USE Patient_Data Go ALTER TABLE Patient_data.dbo.tbmaster ADD PortalAccess bit Null; GO USE Patient_Data Go ALTER TABLE Patient_data.dbo.tbOutPatient ADD MobileOnsite bit Null; GO USE [BUILD_FILE] GO /****** Object: StoredProcedure [dbo].[global_SearchCompany] Script Date: 11/24/2014 10:05:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[global_SearchCompany] @SearchCriteria [varchar](15), @Type [int] AS IF (@Type=0) BEGIN Select * From tbcoCompany Where AccountNum=@SearchCriteria and Status = 'A' END ELSE IF (@Type=1) BEGIN Select * From tbcoCompany Where Company=@SearchCriteria and Status = 'A' END ELSE IF (@Type=2) BEGIN Select * From tbcoCompany Where AccountNum Like @SearchCriteria + '%' and Status = 'A' END ELSE IF (@Type=3) BEGIN Select * From tbcoCompany Where Company Like @SearchCriteria + '%' and Status = 'A' END ELSE IF (@Type=4) BEGIN Select Company as [Company Name], AccountNum as Code From tbcoCompany Where (AccountNum=@SearchCriteria OR Company=@SearchCriteria) and Status = 'A' END ELSE IF (@Type=5) BEGIN Select Company as [Company Name], AccountNum as Code From tbcoCompany Where Company=@SearchCriteria and Status = 'A' END ELSE IF (@Type=6) BEGIN Select AccountNum as Code, Company as [Company Name] From tbcoCompany Where AccountNum Like @SearchCriteria + '%' and Status = 'A' END ELSE IF (@Type=7) BEGIN Select Company as [Company Name],AccountNum as Code From tbcoCompany Where Company Like @SearchCriteria + '%' and Status = 'A' END ELSE IF (@Type = 8) BEGIN Select CompanyID, AccountNum, Company, ISNULL( ContactPerson,'') [ContactPerson], ISNULL( Position,'') [Position], LTRIM(RTRIM(ISNULL( HouseStreet,'') + '' + ISNULL( Barangay,'') + '' + ISNULL( ZipCode,''))) [Address] From tbcoCompany Where AccountNum=@SearchCriteria END --Added By Angelo ELSE IF (@Type = 54) BEGIN Select Company as Description, AccountNum as Code, Isnull(Status,'A') AS Status From Build_File..tbcoCompany Where AccountNum = @SearchCriteria and Status = 'A' END GO USE PATIENT_DATA GO Alter Table Patient_Data..tbAdmWatcherMaster Add Relation varchar(30) GO GO Alter Table Patient_Data..tbPatientHistory Add Precautions [text] NULL GO Alter table PATIENT_DATA..tbAdmPatientInfoUpdate Alter column Remarks text Alter Table Patient_Data..tbpatient2 alter column vip bit Alter Table Patient_Data..tbpatient2 alter column WithDrOrder bit Alter Table Patient_Data..tbpatient2 alter column DTR bit Alter Table Patient_Data..tbpatient2 alter column OkeyPH bit Alter Table Patient_Data..tbpatient2 alter column Transient bit Alter Table Patient_Data..tbpatient2 alter column Confidential bit Alter Table Patient_Data..tbpatient alter column WithAdmissionKit bit Alter Table Patient_Data..tbpatient alter column WithTransferFee bit Alter Table Patient_Data..tbMaster2 Add PatientFindUs varchar(1) Alter Table Patient_Data..tbMaster2 Add PatientFindUsOtherRemarks varchar(50) Alter Table Patient_Data..tbPatient2 Add PatientType varchar(1) null Alter Table tbMaster Add ForeignAddress varchar(100) Alter Table tbMaster Add Country varchar(30) GO Alter table Patient_Data..tbPastMaster Add EmailAddress varchar(50) GO ALTER TABLE [dbo].[tbmaster2] ADD [FatherEmployer] [varchar](30) NULL ALTER TABLE [dbo].[tbmaster2] ADD [MotherEmployer] [varchar](30) NULL Alter TABLE [dbo].[tbAdmDischarge]Alter column [Remarks] [varchar](100) NULL Alter TABLE [dbo].[tbAdmittingSetUp]Add isAllowSupervisorConfirmation bit not null default (0) Alter TABLE [dbo].[tbAdmDischarge]add DeathDate datetime null Alter Table Patient_Data..tbMaster Alter Column HouseStreet varchar(200); Alter Table Patient_Data..tbMaster2 Alter Column SpouseAddress varchar(200); Alter Table Patient_Data..tbMaster2 Alter Column FatherAddress varchar(200); Alter Table Patient_Data..tbMaster2 Alter Column MotherAddress varchar(200); Alter Table Patient_Data..tbMaster2 Alter Column EmployerAddress varchar(200); Alter Table Patient_Data..tbMaster2 Alter Column ContactAddress varchar(200); Alter Table Patient_Data..tbPatientInformant Alter Column InformantAddress varchar(200); Alter Table Patient_Data..tbPatientGuarantor Alter Column GuarantorAddress varchar(200); Alter Table PATIENT_DATA..tbadmittingsetup Add isAllowCheckOPDConsultation bit not null default(1) Alter Table Patient_Data.dbo.tbOutPatientHistory Add Precautions [text] NULL Alter Table Patient_Data..tbOutPatient Add PAD bit not null default(0) Alter Table Patient_Data..tbPatient2 Add PAD bit not null default(0) Alter table Patient_Data..tbAdmittingSetup Add isAllowHospitalizationGuide bit not null default (0) Alter Table Patient_Data..tbMaster Alter column [Barangay] [varchar](100) NULL Alter Table Patient_Data..tbMaster2 Alter Column [Employer] [varchar](100) NULL Alter Table Patient_Data..tbMaster Alter Column FirstName varchar(50) Alter Table Patient_Data..tbmaster_Merged Alter Column FirstName varchar(50) GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_User_Department_Access] Script Date: 09/16/2014 16:29:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[sp_Adm_SupervisorConfirmation] @UserCode As Varchar(10), @Password As Varchar(32), @DepartmentID As Varchar(4)=NULL AS BEGIN Select * From Password..tbPasswordMain Where EmployeeID = @UserCode and Password= @Password and right_Code2 ='S' and ((DepartmentID='00' and OriginalDepartment='00') or (DepartmentID='1' and OriginalDepartment='1') or (DepartmentID='20' or OriginalDepartment='20') or (DepartmentID='1' and OriginalDepartment=@DepartmentID) or (DepartmentID=@DepartmentID and OriginalDepartment=@DepartmentID) ) END GO GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[OPD_DailyCensusNewPatient] @reportDate as varchar(10) as Select B.Hospnum, A.IDNum, B.LastName, B.FirstName, B.MiddleName, A.AdmDate, C.Company as Account, A.AccountNum,D.LastName+', '+D.FirstName+' '+D.MiddleName as Doctor from Patient_Data..tbOutpatient A Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum Left Outer Join Build_File..tbcoCompany C on A.AccountNum = C.AccountNum Left outer Join Build_File..tbCoDoctor D on A.DoctorID1 = D.DoctorID where A.AdmDate between @reportDate and @reportDate + ' 23:59:59.99' And A.PatientStatus ='N' order by a.AdmDate GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateWatcher] Script Date: 07/10/2014 15:34:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateWatcher] @HospNum varchar(8), @IDNum as varchar(10), @LastName as varchar(30), @FirstName as varchar(30), @MiddleName as varchar(30), @Sex as Varchar(1), @RoomID as Varchar(8), @RoomRate as float(8), @AdmDate as varchar(25), @WatcherID as varchar(8), @AdditionalBed as varchar(1), @Relation as varchar(30) = null AS Declare @OldRate as float(8) select watcherID from tbadmwatchermaster where watcherID=@WatcherID; if @@rowcount = 0 begin Insert into tbAdmWatcherMaster(HospNum, IDNum, LastName, FirstName, MiddleName,Sex,RoomID, RoomRate,AdmDate,WatcherID,AdditionalBed,Relation) Values(@HospNum, @IDNum, @LastName, @FirstName, @MiddleName, @Sex, @RoomID, @RoomRate,@AdmDate,@WatcherID,@AdditionalBed,@Relation); end; else begin update tbAdmWatcherMaster set HospNum = @HospNum, IDNum = @IDNum, LastName = @LastName, FirstName = @FirstName, MiddleName = @MiddleName, Sex = @Sex, RoomID = @RoomID, RoomRate = @RoomRate, AdmDate = @AdmDate, AdditionalBed=@AdditionalBed, Relation = @Relation where WatcherID = @WatcherID end; select @OldRate = isnull(WatcherRate,0) from tbpatient where IDNum=@IDNum; Update tbPatient Set WithWatcher=1, WatcherRate = @OldRate + @RoomRate Where IDNum=@IDNum; IF Len(@RoomID) > 0 begin if @AdditionalBed = '0' Begin Update Build_File..tbCoRoom Set BedsLeft = BedsLeft - 1 Where RoomID = @RoomID ; End ; else begin Update Build_File..tbCoRoom Set AdditionalBed = AdditionalBed + 1 Where RoomID = @RoomID ; end; end; GO GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadPatient] Script Date: 07/21/2014 12:36:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_LoadPatient] --->Dated 02/08/2012 by sensei @HospNum AS VarChar(10) AS if exists(SELECT * FROM tbMaster M WHERE M.HospNum = @HospNum) begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, ISNULL(M.HouseStreet,'') HouseStreet, ISNULL(M.Barangay,'') Barangay, ISNULL(M.ZipCode,'') ZipCode, ISNULL(M.Sex,'') Sex, M.BirthDate, M.CivilStatus, ISNULL(M.Occupation,'') Occupation, ISNULL(M.Age,'') Age, ISNULL(M.TelNum,'') TelNum, ISNULL(M.PatientStatus,'') PatientStatus, ISNULL(M.OPDNum,'') OPDNum, ISNULL(M.XrayNum,'') XrayNum, ISNULL(M.UltraNum,'') UltraNum, ISNULL(M.SSSGSISNum,'') SSSGSISNum, ISNULL(M.CTNum,'') CTNum, ISNULL(M2.BirthPlace,'') BirthPlace, ISNULL(M2.NationalityID,'') NationalityID, ISNULL(M2.ReligionID,'') ReligionID, ISNULL(M2.Spouse,'') Spouse, ISNULL(M2.Father,'') Father, ISNULL(M2.Mother,'') Mother, ISNULL(M2.Employer,'') Employer, ISNULL(M2.EmployerAddress, '') EmployerAddress, ISNULL(M2.EmployerTelNum,'') EmployerTelNum, ISNULL(M2.ContactName,'') ContactName, ISNULL(M2.ContactAddress,'') ContactAddress, ISNULL(M2.ContactRelation,'') ContactRelation, ISNULL(M2.ContactTelNum,'') ContactTelNum, ISNULL(M.BloodType,'') BloodType, ISNULL(M2.MotherAddress,'') MotherAddress, ISNULL(M2.MotherTelNum, '') MotherTelNum, ISNULL(M2.FatherAddress,'') FatherAddress, ISNULL(M2.FatherTelNum,'') FatherTelNum, ISNULL(M2.SpouseAddress,'') SpouseAddress, ISNULL(M2.SpouseTelNum,'') SpouseTelNum, ISNULL(M2.barangaycaptain,'') barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] , ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum] FROM tbMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum WHERE M.HospNum = @HospNum end else begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, M.HouseStreet, M.Barangay, M.ZipCode, M.Sex, M.BirthDate, M.CivilStatus, M.Occupation, M.Age, M.TelNum, M.PatientStatus, M.OPDNum, M.XrayNum, M.UltraNum, M.SSSGSISNum, M.CTNum, M2.BirthPlace, M2.NationalityID, M2.ReligionID, M2.Spouse, M2.Father, M2.Mother, M2.Employer, M2.EmployerAddress, M2.EmployerTelNum, M2.ContactName, M2.ContactAddress, M2.ContactRelation, M2.ContactTelNum, M.BloodType, M2.MotherAddress, M2.MotherTelNum, M2.FatherAddress, M2.FatherTelNum, M2.SpouseAddress, M2.SpouseTelNum, M2.barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] ,ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum] FROM tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum WHERE M.HospNum = @HospNum end GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateLogfile] Script Date: 07/21/2014 10:11:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateLogfile] @Hospnum as varchar(10), @IDNum As VarChar(10), @UserID As VarChar(8), @Remarks As text AS Insert Into tbAdmPatientInfoUpdate (Hospnum, IDNum, TransDate, UserID, Remarks) Values (@Hospnum, @IDNum, GetDate(), @UserID, @Remarks ) GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadAddedInfo] Script Date: 07/24/2014 13:21:00 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_LoadAddedInfo] @IDNum as varchar(10) AS Select P2.PackageID, P2.AdmissionTypeID, P.Package, A.AdmissionType, P2.SeniorCitizen, M.Title, N.NoticeOfAdmID, N.NoticeOfAdm, M.Cellnum, M.EmailAddress, --M.SpouseEmployer, G.GuarantorRelation, M.GSISNum, M.SSSNum, M.HospPlan, M.SeniorCitizenID, M2.SpousePosition, M2.SpouseAddress, M2.SpouseTelNum, M2.FatherOccupation, M2.MotherOccupation, M2.EmployerPosition, G.GuarantorOccupation, I.InformantOccupation, C.AccountNum, C.Company, P2.Transient, P1.Cardnum, M.BirthDate, P2.DTR, --isnull(P1.EMBARKATIONDATE,0)as Embarkationdate, --isnull(P1.DISEMBARKATIONDATE,0)as Disembarkationdate, --P1.Category AS CategoryDesc, --P1.VesID AS VesID, --V.VesselName AS VesselName, M2.SpouseEmpName as SpouseEmployer, P2.OkeyPH From Patient_Data..tbPatient2 P2 Left Outer Join Build_File..tbCoAdmPackage P on P2.PackageID = P.PackageID Left Outer Join Build_File..tbCoAdmissionType A on A.AdmissionTypeID = p2.AdmissionTypeID Left Outer Join Patient_Data..tbPatient P1 on P2.IdNum = P1.IDNum left Outer Join Patient_Data..tbMaster M on P1.HospNum = M.HospNum left Outer Join Build_File..tbCoNoticeOfAdm N on P2.NoticeOfAdmID = N.NoticeOfAdmID left Outer Join Patient_Data..tbPatientGuarantor G on P1.IdNum = G.IdNum left Outer Join Patient_Data..tbMaster2 M2 on P1.HospNum = M2.HospNum Left Outer Join Patient_Data..tbPatientInformant I on P1.Idnum = I.IdNum Left Outer Join Build_file..tbcoCompany C on P1.AccountNumII = C.AccountNum --LEFT OUTER JOIN Build_File..tbCoVessel V ON P1.VesID = V.VesselCode Where P2.IDNum = @IDNum; GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateAddedInfo] Script Date: 07/24/2014 13:20:20 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateAddedInfo] @IDNum as varchar(12), @PackageID as varchar(5), @AdmissionTypeID as varchar(2), @UserID as varchar(10) , @SeniorCitizen as bit = 0, @WithAdmissionKit as bit = 0, @SeniorCitizenID as varchar(20)= Null, @isOutborn as varchar(1) = null, @isWellBaby as varchar(1) = null, @MotherIDNum as varchar(15) = Null, @WithTransferFee as bit = 0, @isCaesarianDelivery as varchar(1) = null, @NoticeOfAdmID as varchar(10), @CellNumber as varchar(12)=null, @EmailAdd as varchar(40), @SpouseEmp as varchar(30), @GuarantorRelation as varchar(30), @GSISNum as varchar(20), @SSSNum as varchar(20), @HospPlan as varchar(1), @SpousePosition as varchar(40) = null, @SpouseAddress as varchar(80)= null, @SpouseTelNum as varchar(15)= null, @FatherOccupation as varchar(40) = null, @MotherOccupation as varchar(40) = null, @Employerposition as varchar(40)= null, @InformantOccupation as varchar(40)= null, @GuarantorPosition as varchar(40)= null, @AccountNumII as varchar(15)= null, @Transient as bit = 0, @DTR as varchar(20)=null--, --@VesselID as varchar(30) =null, --@Category as varchar(10)=null, --@Embarkation datetime = null, --@Disembarkation datetime = null AS Declare @Hospnum as varchar(15) Update tbPatient2 Set PackageID = @PackageID, AdmissionTypeID = @AdmissionTypeID, SeniorCitizen = @SeniorCitizen, Transient = @Transient, DTR=@DTR, NoticeOfAdmID = @NoticeOfAdmID Where IDNum = @IDNum; Update tbPatient Set UserID = @UserID, WithAdmissionKit = @WithAdmissionKit, isOutborn = case when @isOutborn = '' then null else @isOutborn end, isWellBaby = case when @isWellBaby = '' then null else @isWellBaby end, MotherIDNum = @MotherIDNum, WithTransferFee = @WithTransferFee, isCaesarianDelivery = case when @isCaesarianDelivery = '' then null else @isCaesarianDelivery end, AccountNumII = @AccountNumII--, --Cardnum=@Cardnum --VesID = @VesselID, --Category = @Category, --EmbarkationDate = @Embarkation, --DisembarkationDate = @Disembarkation Where IDNum = @IDNum; set @Hospnum = (Select Hospnum from tbPatient where IDNum = @IDNum); Update tbMaster Set SeniorCitizen = @SeniorCitizen, SeniorCitizenID = @SeniorCitizenID, Cellnum = @CellNumber, EmailAddress = @EmailAdd, --SpouseEmployer = @SpouseEmp, GSISNum = @GSISNum, SSSNum = @SSSNum, HospPlan = @HospPlan --AccountNumII = @AccountNumII, --BirthDate=@DateOfBirth Where Hospnum = @Hospnum; Update tbPatientGuarantor set GuarantorRelation = @GuarantorRelation, GuarantorOccupation = @GuarantorPosition where IDNum = @IDNum; Update tbMaster2 set SpousePosition = @SpousePosition, SpouseAddress = @SpouseAddress, SpouseTelNum = @SpouseTelNum, FatherOccupation = @FatherOccupation, MotherOccupation = @MotherOccupation, Employerposition = @EmployerPosition, SpouseEmpName = @SpouseEmp Where Hospnum = @Hospnum; Update tbPatientInformant set InformantOccupation = @InformantOccupation where IDNum = @IDNum; GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadInPatient] Script Date: 07/24/2014 13:19:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_LoadInPatient] @IDNum as VarChar(10), @HospNum as varchar(10) AS --Optimized by Angelo 04/15/2006 IF LEN(@Idnum) > 0 BEGIN Select tbPatient2.*, tbPatient.*,tbpatientInformant.*,tbpatientGuarantor.*, tbpatientHistory.AdmDiagnosis,tbpatientHistory.ChiefComplaint,tbpatientHistory.Precautions,tbpatientHistory.AdmImpression from tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Left Outer Join tbpatientInformant on tbpatient.IdNum =tbpatientInformant.IdNum Left Outer Join tbpatientGuarantor on tbpatient.idnum = tbpatientGuarantor.idnum Left Outer Join tbpatientHistory on tbpatient.IdNum = tbpatientHistory.IDNum Where tbPatient.Idnum = @IdNum END ELSE --Default: Search By IdNum and HospNum BEGIN Select tbPatient2.*, tbPatient.*,tbpatientInformant.*,tbpatientGuarantor.*, tbpatientHistory.AdmDiagnosis,tbpatientHistory.ChiefComplaint,tbpatientHistory.Precautions,tbpatientHistory.AdmImpression From tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Left Outer Join tbpatientInformant on tbpatient.IdNum =tbpatientInformant.IdNum Left Outer Join tbpatientGuarantor on tbpatient.idnum = tbpatientGuarantor.idnum Left Outer Join tbpatientHistory on tbpatient.IdNum = tbpatientHistory.IDNum Where tbPatient.IDNum = @IDNum or (Len(@IDNum) = 0 and tbPatient.HospNum = @HospNum and tbPatient.DcrDate is Null ) END Select tbPatient2.*, tbPatient.*,tbpatientInformant.*,tbpatientGuarantor.*, tbpatientHistory.AdmDiagnosis,tbpatientHistory.ChiefComplaint,tbpatientHistory.Precautions,tbpatientHistory.AdmImpression From tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Left Outer Join tbpatientInformant on tbpatient.IdNum =tbpatientInformant.IdNum Left Outer Join tbpatientGuarantor on tbpatient.idnum = tbpatientGuarantor.idnum Left Outer Join tbpatientHistory on tbpatient.IdNum = tbpatientHistory.IDNum Where tbPatient.IDNum = @IDNum or (Len(@IDNum) = 0 and tbPatient.HospNum = @HospNum and tbPatient.DcrDate is Null ) --Angelo --Commented Orig 04/15/2006 /*CREATE PROCEDURE sp_Adm_LoadInPatient @IDNum as VarChar(8), @HospNum as varchar(8) AS --Optimized by Angelo 04/15/2006 IF LEN(@HospNum) > 0 BEGIN Select tbPatient2.*, tbPatient.* from tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Where (tbPatient.HospNum = @HospNum and tbPatient.DcrDate is Null ) END ELSE --Default: Search By IdNum BEGIN Select tbPatient2.*, tbPatient.* from tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Where tbPatient.IDNum = @IDNum END*/ GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadPatient] Script Date: 07/24/2014 13:19:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_LoadPatient] --->Dated 02/08/2012 by sensei @HospNum AS VarChar(10) AS if exists(SELECT * FROM tbMaster M WHERE M.HospNum = @HospNum) begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, ISNULL(M.HouseStreet,'') HouseStreet, ISNULL(M.Barangay,'') Barangay, ISNULL(M.ZipCode,'') ZipCode, ISNULL(M.Sex,'') Sex, M.BirthDate, M.CivilStatus, ISNULL(M.Occupation,'') Occupation, ISNULL(M.Age,'') Age, ISNULL(M.TelNum,'') TelNum, ISNULL(M.PatientStatus,'') PatientStatus, ISNULL(M.OPDNum,'') OPDNum, ISNULL(M.XrayNum,'') XrayNum, ISNULL(M.UltraNum,'') UltraNum, ISNULL(M.SSSGSISNum,'') SSSGSISNum, ISNULL(M.CTNum,'') CTNum, ISNULL(M2.BirthPlace,'') BirthPlace, ISNULL(M2.NationalityID,'') NationalityID, ISNULL(M2.ReligionID,'') ReligionID, ISNULL(M2.Spouse,'') Spouse, ISNULL(M2.Father,'') Father, ISNULL(M2.Mother,'') Mother, ISNULL(M2.Employer,'') Employer, ISNULL(M2.EmployerAddress, '') EmployerAddress, ISNULL(M2.EmployerTelNum,'') EmployerTelNum, ISNULL(M2.ContactName,'') ContactName, ISNULL(M2.ContactAddress,'') ContactAddress, ISNULL(M2.ContactRelation,'') ContactRelation, ISNULL(M2.ContactTelNum,'') ContactTelNum, ISNULL(M.BloodType,'') BloodType, ISNULL(M2.MotherAddress,'') MotherAddress, ISNULL(M2.MotherTelNum, '') MotherTelNum, ISNULL(M2.FatherAddress,'') FatherAddress, ISNULL(M2.FatherTelNum,'') FatherTelNum, ISNULL(M2.SpouseAddress,'') SpouseAddress, ISNULL(M2.SpouseTelNum,'') SpouseTelNum, ISNULL(M2.barangaycaptain,'') barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] , ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum], ISNULL(M.EmailAddress,'') [EmailAddress], ISNULL(M2.SpousePosition,'')[SpousePosition], ISNULL(M2.FatherOccupation,'') [FatherOccupation], ISNULL(M2.FatherTelNum,'') [FatherTelNum], ISNULL(M2.Employerposition,'')[EmployerPosition], ISNULL(M2.MotherOccupation,'')[MotherOccupation], ISNULL(M.SSSGSISNum,'')[SSSGSIS], ISNULL(M.GSISNum,'')[GSISNum], ISNULL(M.SSSNum,'')[SSSNUm], ISNULL(M.PatientType,'')[PatientClass], ISNULL(M.SeniorCitizenID,'')[SeniorCitizenID], ISNULL(M3.AllergyID1,'')[Allergy1], ISNULL(M3.AllergyID2,'')[Allergy2], ISNULL(M3.AllergyID3,'')[Allergy3], ISNULL(M3.AllergyID4,'')[Allergy4], ISNULL(M3.AllergyID5,'')[Allergy5], ISNULL(M2.PatientFindUs,'')[PatientFindUs], ISNULL(M2.PatientFindUsOtherRemarks,'')[PatientFindUsOtherRemarks] FROM tbMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum WHERE M.HospNum = @HospNum end else begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, M.HouseStreet, M.Barangay, M.ZipCode, M.Sex, M.BirthDate, M.CivilStatus, M.Occupation, M.Age, M.TelNum, M.PatientStatus, M.OPDNum, M.XrayNum, M.UltraNum, M.SSSGSISNum, M.CTNum, M2.BirthPlace, M2.NationalityID, M2.ReligionID, M2.Spouse, M2.Father, M2.Mother, M2.Employer, M2.EmployerAddress, M2.EmployerTelNum, M2.ContactName, M2.ContactAddress, M2.ContactRelation, M2.ContactTelNum, M.BloodType, M2.MotherAddress, M2.MotherTelNum, M2.FatherAddress, M2.FatherTelNum, M2.SpouseAddress, M2.SpouseTelNum, M2.barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] ,ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum], ISNULL(M.EmailAddress,'') [EmailAddress], ISNULL(M2.SpousePosition,'')[SpousePosition], ISNULL(M2.FatherOccupation,'') [FatherOccupation], ISNULL(M2.FatherTelNum,'') [FatherTelNum], ISNULL(M2.Employerposition,'')[EmployerPosition], ISNULL(M2.MotherOccupation,'')[MotherOccupation] FROM tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum WHERE M.HospNum = @HospNum end GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[Sp_Adm_SearchUserUpdates] Script Date: 09/11/2014 13:06:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Sp_Adm_SearchUserUpdates] @StartDate as varchar(10), @EndDate as varchar(10), @UserID as varchar(15), @Hospnum as varchar(15), @Type as varchar(1), @IDNum as varchar(10) as if @Type = '1' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where Hospnum = @Hospnum end else if @Type = '2' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where UserID = @UserID end else if @Type = '3' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where Transdate between @StartDate and @EndDate + ' 23:59:59.99' end else if @Type = '4' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where IDNum = @IDNum end else if @Type = '5' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where Hospnum = @Hospnum and IDNum = @IDNum end else if @Type = '6' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where Hospnum = @Hospnum and UserID = UserID end else if @Type = '7' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where IDNum = @IDNum and UserID = UserID end GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_Search_User] Script Date: 09/11/2014 15:01:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_Search_User] (@Type as varchar(1), @UserID as varchar(5), @StartDate as varchar(10),@EndDate as varchar(10) = null) AS if @Type = '1' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID where A.EmployeeID like @UserID + '%' order by A.logsequence desc else if @Type = '2' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID where A.LogSequence = @UserID order by A.logsequence desc else if @Type = '3' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID order by A.logsequence desc else if @Type = '4' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID where A.LogInDate between @StartDate and @EndDate+' 23:59:59:99' order by A.logsequence desc if @Type = '5' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID where A.LogInDate between @StartDate and @EndDate+' 23:59:59:99' and A.EmployeeID = @UserID order by A.logsequence desc GO USE [PATIENT_DATA] GO ALTER TABLE [tbAdmWaitingList] ADD [EndDateReserved] [datetime] NULL, [ServiceID] [varchar](10) NULL, [AdmissionType] [varchar](10) NULL, [JoneltaID] [varchar](10) NULL GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadReservation] Script Date: 09/15/2014 15:04:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_LoadReservation] @ReservationNum as varchar(10) AS select --* A.LastName, A.FirstName, A.MiddleName, A.RoomID, A.Accomodation, A.Remarks, B.AdmissionTypeID, B.AdmissionType, C.ServiceID, C.Service, A.DateReserved, A.EnddateReserved from tbAdmWaitingList A Left Outer Join BUILD_FILE..tbCoAdmissionType B on A.AdmissionType = B.AdmissionTypeID Left Outer JOin BUILD_FILE..tbCoService C on A.ServiceID = C.ServiceID where ReservationNum = @ReservationNum GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadPatient] Script Date: 09/16/2014 13:07:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_LoadPatient] --->Dated 02/08/2012 by sensei @HospNum AS VarChar(10) AS if exists(SELECT * FROM tbMaster M WHERE M.HospNum = @HospNum) begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, ISNULL(M.HouseStreet,'') HouseStreet, ISNULL(M.Barangay,'') Barangay, ISNULL(M.ZipCode,'') ZipCode, ISNULL(M.Sex,'') Sex, M.BirthDate, M.CivilStatus, ISNULL(M.Occupation,'') Occupation, ISNULL(M.Age,'') Age, ISNULL(M.TelNum,'') TelNum, ISNULL(M.PatientStatus,'') PatientStatus, ISNULL(M.OPDNum,'') OPDNum, ISNULL(M.XrayNum,'') XrayNum, ISNULL(M.UltraNum,'') UltraNum, ISNULL(M.SSSGSISNum,'') SSSGSISNum, ISNULL(M.CTNum,'') CTNum, ISNULL(M2.BirthPlace,'') BirthPlace, ISNULL(M2.NationalityID,'') NationalityID, ISNULL(M2.ReligionID,'') ReligionID, ISNULL(M2.Spouse,'') Spouse, ISNULL(M2.Father,'') Father, ISNULL(M2.Mother,'') Mother, ISNULL(M2.Employer,'') Employer, ISNULL(M2.EmployerAddress, '') EmployerAddress, ISNULL(M2.EmployerTelNum,'') EmployerTelNum, ISNULL(M2.ContactName,'') ContactName, ISNULL(M2.ContactAddress,'') ContactAddress, ISNULL(M2.ContactRelation,'') ContactRelation, ISNULL(M2.ContactTelNum,'') ContactTelNum, ISNULL(M.BloodType,'') BloodType, ISNULL(M2.MotherAddress,'') MotherAddress, ISNULL(M2.MotherTelNum, '') MotherTelNum, ISNULL(M2.FatherAddress,'') FatherAddress, ISNULL(M2.FatherTelNum,'') FatherTelNum, ISNULL(M2.SpouseAddress,'') SpouseAddress, ISNULL(M2.SpouseTelNum,'') SpouseTelNum, ISNULL(M2.barangaycaptain,'') barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] , ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum], ISNULL(M.EmailAddress,'') [EmailAddress], ISNULL(M2.SpousePosition,'')[SpousePosition], ISNULL(M2.FatherOccupation,'') [FatherOccupation], ISNULL(M2.FatherTelNum,'') [FatherTelNum], ISNULL(M2.Employerposition,'')[EmployerPosition], ISNULL(M2.MotherOccupation,'')[MotherOccupation], ISNULL(M.SSSGSISNum,'')[SSSGSIS], ISNULL(M.GSISNum,'')[GSISNum], ISNULL(M.SSSNum,'')[SSSNUm], ISNULL(M.PatientType,'')[PatientClass], ISNULL(M.SeniorCitizenID,'')[SeniorCitizenID], ISNULL(M3.AllergyID1,'')[Allergy1], ISNULL(M3.AllergyID2,'')[Allergy2], ISNULL(M3.AllergyID3,'')[Allergy3], ISNULL(M3.AllergyID4,'')[Allergy4], ISNULL(M3.AllergyID5,'')[Allergy5], ISNULL(M2.PatientFindUs,'')[PatientFindUs], ISNULL(M2.PatientFindUsOtherRemarks,'')[PatientFindUsOtherRemarks], ISNULL(M2.FatherEmployer,'')[FatherEmployer], ISNULL(M2.MotherEmployer,'')[MotherEmployer] FROM tbMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum WHERE M.HospNum = @HospNum end else begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, M.HouseStreet, M.Barangay, M.ZipCode, M.Sex, M.BirthDate, M.CivilStatus, M.Occupation, M.Age, M.TelNum, M.PatientStatus, M.OPDNum, M.XrayNum, M.UltraNum, M.SSSGSISNum, M.CTNum, M2.BirthPlace, M2.NationalityID, M2.ReligionID, M2.Spouse, M2.Father, M2.Mother, M2.Employer, M2.EmployerAddress, M2.EmployerTelNum, M2.ContactName, M2.ContactAddress, M2.ContactRelation, M2.ContactTelNum, M.BloodType, M2.MotherAddress, M2.MotherTelNum, M2.FatherAddress, M2.FatherTelNum, M2.SpouseAddress, M2.SpouseTelNum, M2.barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] ,ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum], ISNULL(M.EmailAddress,'') [EmailAddress], ISNULL(M2.SpousePosition,'')[SpousePosition], ISNULL(M2.FatherOccupation,'') [FatherOccupation], ISNULL(M2.FatherTelNum,'') [FatherTelNum], ISNULL(M2.Employerposition,'')[EmployerPosition], ISNULL(M2.MotherOccupation,'')[MotherOccupation] FROM tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum WHERE M.HospNum = @HospNum end GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_Search_RevokedOutPatients] Script Date: 09/18/2014 18:29:32 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_AOPD_Search_RevokedOutPatients] @LastName as varchar(30), @hospnum as varchar(10), @idnum as varchar(10) as Declare @DateToDay as varchar(10); Set @DateToDay = Convert(varchar(10),getdate(),101); SELECT tbOutPatient.HospNum AS [Hospital #],tbOutPatient.IDNum AS [Admission #],RTRIM(isnull(tbMaster.LastName,'')) + ' ,' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name],convert(varchar(10),tbOutPatient.AdmDate,101) + substring(convert(varchar(19),tbOutPatient.AdmDate,100),12,8) AS [Admission Date], tbmaster.accountnum as account, tboutpatient.billingdate as billingdate,tboutpatient.ExpirationDate as ExpirationDate, tbMaster.FileNum FROM PATIENT_DATA..tbOutPatient AS tbOutPatient LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON tbOutPatient.Hospnum = tbMaster.HospNum where ((tbOutPatient.AdmDate Between @DateToDay and @DateToDay + ' 23:59:59') and (tbMaster.LastName like rtrim(@Lastname) + '%' ) and tboutpatient.HospNum like @Hospnum +'%' and tbOutPatient.IDNum like @IDNum +'%') and tbOutPatient.opdstatus = 'R' order by tbmaster.lastname, tbmaster.firstname, tbmaster.middlename GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[Revoke_ER_Admitting_Communication] Script Date: 09/19/2014 15:50:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Revoke_ER_Admitting_Communication] @idnum as varchar (20) AS SET NOCOUNT ON BEGIN update tboutpatient set billingdate = null where idnum = @idnum update tbermaster set dcrdate= null where idnum = @idnum delete from tbEr_Admitting_Communication where opdidnum = @idnum update tboutpatient set dcrdate = null where idnum = @idnum --091914 END GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateMaster] Script Date: 11/06/2014 22:33:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster] @InPatient as VarChar(1), @HospNum As VarChar(8), @LastName As VarChar(30), @FirstName As VarChar(30), @MiddleName As VarChar(30), @AccountNum As VarChar(8), @HouseStreet As VarChar(100), @Barangay As VarChar(50), @ZipCode As VarChar(4), @Sex As VarChar(1), @BirthDate As VarChar(10), @CivilStatus As VarChar(1), @Occupation As VarChar(30), @Age As VarChar(3), @TelNum As VarChar(15), @SSSGSISNum As VarChar(15), @BloodType As VarChar(3), @OPDNum As VarChar(8), @XrayNum As VarChar(8), @UltraNum As VarChar(8), @CTNum As VarChar(8), @BirthPlace As VarChar(50), @NationalityID As VarChar(2), @ReligionID As VarChar(2), @Spouse As VarChar(50), @Mother As VarChar(50), @Father As VarChar(50), @Employer As VarChar(50), @EmployerAddress As VarChar(200), @EmployerTelNum As VarChar(15), @ContactName As VarChar(30), @ContactAddress As VarChar(200), @ContactRelation As VarChar(30), @ContactTelNum As VarChar(15), @MotherAddress as VarChar(200), @MotherTelNum as VarChar(15), @FatherAddress as VarChar(200), @FatherTelNum as VarChar(15), @SpouseAddress As VarChar(50), @SpouseTelNum as VarChar(15), @AllergyID1 as VarChar(8), @AllergyID2 as VarChar(8), @AllergyID3 as VarChar(8), @AllergyID4 as VarChar(8), @AllergyID5 as VarChar(8), @BarangayCaptain As VarChar(50) AS Declare @NewHospNum VarChar(8) Declare @NewAccountNum varchar(8) if @AccountNum = 'New' or @AccountNum = 'NEW' begin set @NewAccountNum = @HospNum End Else Begin set @NewAccountNum = @AccountNum End Select HospNum From tbMaster Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMaster Set LastName = ltrim(rtrim(@LastName)), FirstName = ltrim(rtrim(@FirstName)), MiddleName = ltrim(rtrim(@MiddleName)), AccountNum = @NewAccountNum, HouseStreet = @HouseStreet, Barangay = @Barangay, ZipCode = @ZipCode, Sex = @Sex, BirthDate = Case When Len(@BirthDate) = 0 Then Null Else @BirthDate End, CivilStatus = @CivilStatus, Occupation = @Occupation, Age = @Age, TelNum = @TelNum, SSSGSISNum = @SSSGSISNum, BloodType = @BloodType, OPDNum = @OPDNum, XrayNum = @XrayNum, UltraNum = @UltraNum, CTNum = @UltraNum from tbMaster Where HospNum = @HospNum ; End ; Else Begin Insert tbMaster ( HospNum,LastName,FirstName,MiddleName,AccountNum, HouseStreet,Barangay,ZipCode,Sex,BirthDate,CivilStatus, Occupation,Age,TelNum,SSSGSISNum,BloodType,OPDNum,XrayNum, UltraNum,CTNum) Values ( @HospNum,ltrim(rtrim(@LastName)),ltrim(rtrim(@FirstName)),ltrim(rtrim(@MiddleName)),@NewAccountNum, @HouseStreet,@Barangay,@ZipCode,@Sex,@BirthDate,@CivilStatus, @Occupation,@Age,@TelNum,@SSSGSISNum,@BloodType,@OPDNum,@XrayNum, @UltraNum,@CTNum); end ; If @InPatient = 'Y' Begin Select HospNum From tbMaster2 Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMaster2 Set BirthPlace = @BirthPlace, NationalityID = @NationalityID, ReligionID = @ReligionID, Spouse = @Spouse, Mother = @Mother, Father = @Father, Employer = @Employer, EmployerAddress = @EmployerAddress, EmployerTelNum = @EmployerTelNum, ContactName = @ContactName, ContactAddress = @ContactAddress, ContactRelation = @ContactRelation, ContactTelNum = @ContactTelNum, MotherAddress = @MotherAddress, MotherTelNum = @MotherTelNum, FatherAddress = @FatherAddress, FatherTelNum = @FatherTelNum, SpouseAddress = @SpouseAddress, SpouseTelNum = @SpouseTelNum, BarangayCaptain = @BarangayCaptain Where HospNum = @HospNum ; End ; Else Begin Insert Into tbMaster2 (HospNum,BirthPlace, NationalityID,ReligionID,Spouse, Mother, Father, Employer, EmployerAddress, EmployerTelNum, ContactName, ContactAddress, ContactRelation, ContactTelNum, MotherAddress, MotherTelNum, FatherAddress, FatherTelNum, SpouseAddress, SpouseTelNum, BarangayCaptain ) Values (@HospNum, @BirthPlace, @NationalityID, @ReligionID, @Spouse, @Mother, @Father, @Employer, @EmployerAddress, @EmployerTelNum, @ContactName, @ContactAddress, @ContactRelation, @ContactTelNum, @MotherAddress, @MotherTelNum, @FatherAddress, @FatherTelNum, @SpouseAddress, @SpouseTelNum, @BarangayCaptain); End ; Select HospNum From tbMasterAllergies Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMasterAllergies Set AllergyID1 = @AllergyID1, AllergyID2 = @AllergyID2, AllergyID3 = @AllergyID3, AllergyID4 = @AllergyID4, AllergyID5 = @AllergyID5 Where HospNum = @HospNum ; End ; Else BEGIN Insert tbMasterAllergies (HospNum, AllergyID1,AllergyID2, AllergyID3,AllergyID4,AllergyID5) Values (@HospNum, @AllergyID1, @AllergyID2,@AllergyID3,@AllergyID4,@AllergyID5 ) ; END ; end ; GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateMaster2] Script Date: 11/06/2014 22:40:48 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster2] --->Dated 02/08/2012 by sensei @HospNum As VarChar(8), @BirthPlace As VarChar(50), @NationalityID As VarChar(2), @ReligionID As VarChar(2), @Spouse As VarChar(50), @Mother As VarChar(50), @Father As VarChar(50), @Employer As VarChar(50), @EmployerAddress As VarChar(200), @EmployerTelNum As VarChar(15), @ContactName As VarChar(50), @ContactAddress As VarChar(200), @ContactRelation As VarChar(50), @ContactTelNum As VarChar(15) , @SpouseAddress varchar(200) , @SpouseTelNum varchar(15) , @FatherAddress varchar(200) , @FatherTelNum varchar(15) , @MotherAddress varchar(200) , @MotherTelNum varchar(15) , @BarangayCaptain varchar(50) , @Position varchar (50) , @EmploymentType varchar(1), @EmploymentLocation varchar(1) , @EmployerFax varchar(15) , @EmployerEmail varchar(50) , @SpouseEmpName varchar(50) , @SpouseEmpAddress varchar(200) , @SpouseEmpTelNum varchar(15) , @SpouseEmpFax varchar(15) , @SpouseEmpEmail varchar(50) , @PHIC varchar(1) , @Insurance varchar(50) , @HouseHold varchar(10) , @Email varchar(50) , @CellNum varchar(15), @OfficeNum varchar(50) = null AS Declare @NewHospNum VarChar(8) Select @NewHospNum = Hospnum From tbMaster2 Where HospNum = @HospNum; Update tbMaster set Email = @Email, CellNum = @CellNum Where HospNum = @HospNum; If Len(@NewHospNum) > 0 Begin Update tbMaster2 Set BirthPlace = @BirthPlace, NationalityID = @NationalityID, ReligionID = @ReligionID, Spouse = @Spouse, Mother = @Mother, Father = @Father, Employer = @Employer, EmployerAddress = @EmployerAddress, EmployerTelNum = @EmployerTelNum, ContactName = @ContactName, ContactAddress = @ContactAddress, ContactRelation = @ContactRelation, ContactTelNum = @ContactTelNum , SpouseAddress = @SpouseAddress , SpouseTelNum = @SpouseTelNum , FatherAddress = @FatherAddress , FatherTelNum = @FatherTelNum , MotherAddress = @MotherAddress , MotherTelNum = @MotherTelNum , BarangayCaptain = @BarangayCaptain , Position = @Position , EmploymentType = @EmploymentType , EmploymentLocation = @EmploymentLocation , EmployerFax = @EmployerFax , EmployerEmail = @EmployerEmail , SpouseEmpName = @SpouseEmpName , SpouseEmpAddress = @SpouseEmpAddress , SpouseEmpTelNum = @SpouseEmpTelNum , SpouseEmpFax = @SpouseEmpFax , SpouseEmpEmail = @SpouseEmpEmail , PhilHealth = @PHIC , Insurance = @Insurance , HouseHold = @HouseHold, OfficeNum = @OfficeNum Where HospNum = @HospNum ; End Else Begin Insert Into tbMaster2 (HospNum,BirthPlace, NationalityID,ReligionID,Spouse, Mother, Father, Employer, EmployerAddress, EmployerTelNum, ContactName, ContactAddress, ContactRelation, ContactTelNum , SpouseAddress , SpouseTelNum , FatherAddress , FatherTelNum , MotherAddress , MotherTelNum , BarangayCaptain ,Position , EmploymentType,EmploymentLocation, EmployerFax ,EmployerEmail , SpouseEmpName , SpouseEmpAddress , SpouseEmpTelNum , SpouseEmpFax , SpouseEmpEmail, PhilHealth, Insurance, HouseHold, OfficeNum ) Values (@HospNum, @BirthPlace, @NationalityID, @ReligionID, @Spouse, @Mother, @Father, @Employer, @EmployerAddress, @EmployerTelNum, @ContactName, @ContactAddress, @ContactRelation, @ContactTelNum, @SpouseAddress , @SpouseTelNum , @FatherAddress , @FatherTelNum , @MotherAddress , @MotherTelNum , @BarangayCaptain , @Position , @EmploymentType, @EmploymentLocation, @EmployerFax , @EmployerEmail , @SpouseEmpName , @SpouseEmpAddress , @SpouseEmpTelNum , @SpouseEmpFax , @SpouseEmpEmail, @PHIC, @Insurance, @HouseHold, @OfficeNum); End GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdatePatient] Script Date: 11/06/2014 22:31:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdatePatient] @IDNum AS VarChar(10), @HospNum AS VarChar(8), @AccountNum AS VarChar(8), @HospPlan AS VarChar(1), @MedicareType AS VarChar(3), @AdmType AS VarChar(1), @AdmDate AS VarChar(25), @ServiceID AS VarChar(3), @AttendingDr1 AS VarChar(4), @AttendingDr2 AS VarChar(4), @AdmittingDr AS VarChar(4), @ReferringDr AS VarChar(4), @AdmittingClerk AS VarChar(8), @ReferredFrom AS VarChar(2), @Age AS VarChar(3), @RoomID AS VarChar(8), @VIP AS Bit, @Confidential AS Bit, @HowAdmitted AS VarChar(1), @PatientClass AS VarChar(1), @GuarantorName AS VarChar(30), @GuarantorAddress AS VarChar(200), @GuarantorEmployer AS VarChar(30), @GuarantorTelNum AS VarChar(15), @AdmDiagnosis AS Text, @ChiefComplaint AS Text, @AdmImpression AS Text, @InformantName AS VarChar(30), @InformantAddress AS VarChar(200), @InformantRelation AS VarChar(30), @InformantTelNum AS VarChar(15), @RoomRate as Float, @WithDrOrder As Bit, @Remarks As VarChar(60), @AdditionalBed as varchar(1) AS Declare @NewAccountNum VarChar(8) Declare @OldRoom VarChar(8) ; if @HospPlan= 'P' or @HospPlan = 'p' begin Set @NewAccountNum = @HospNum end else begin Set @NewAccountNum = @AccountNum end /* Update Table Patient */ Select IDNum from tbPatient Where IDNum = @IDNum ; If @@RowCount = 0 Begin Set @OldRoom = '' ; Insert tbPatient (IDNum, HospNum, AccountNum, HospPlan, MedicareType, AdmType, AdmDate, ServiceID, AttendingDr1, AttendingDr2, AdmittingDr, ReferringDr, AdmittingClerk, ReferredFrom, Age, RoomID, RoomRate,AdditionalBed ) Select @IDNum, @HospNum, @NewAccountNum, @HospPlan, @MedicareType, @AdmType, @AdmDate, @ServiceID, @AttendingDr1, @AttendingDr2, @AdmittingDr, @ReferringDr, @AdmittingClerk, @ReferredFrom, @Age, upper(@RoomID), @RoomRate,@AdditionalBed ; End; Else Begin Select @OldRoom = isnull(RoomID,'') From tbPatient Where IDNum = @IDNum ; Update tbPatient Set AccountNum = @NewAccountNum, HospPlan = @HospPlan, MedicareType = @MedicareType, AdmType = @AdmType, AdmDate = @AdmDate, ServiceID = @ServiceID, AttendingDr1 = @AttendingDr1, AttendingDr2 = @AttendingDr2, AdmittingDr = @AdmittingDr, ReferringDr = @ReferringDr,ReferredFrom = @ReferredFrom, Age = @Age, RoomID = upper(@RoomID), RoomRate = @RoomRate, AdditionalBed = @AdditionalBed Where tbPatient.IDNum = @IDNum End ; /* Update Table Patient2 */ Select IDNum From tbPatient2 Where IDNum = @IDNum ; If @@RowCount = 0 Insert tbPatient2 (IDNum, VIP, Confidential, HowAdmitted, PatientClass, OkeyLAb, OkeyRTS, OkeyXray, OkeyUltra, OkeyCT, OkeyPT, OkeyNU, OkeyAU, OkeyHs,WithDrOrder,Remarks) Values (@IDNum, @VIP, @Confidential, @HowAdmitted, @PatientClass, 1, 1, 1, 1, 1, 1, 1, 1, 1, @WithDrOrder,@Remarks) Else Update tbPatient2 Set VIP = @VIP, Confidential = @Confidential, HowAdmitted = @HowAdmitted, PatientClass = @PatientClass, WithDrOrder = @WithDrOrder, Remarks = @Remarks Where IDNum = @IDNum /* Update RoomAvailability */ if @AdditionalBed = '0' Begin IF Len(@OldRoom) > 0 Begin Update Build_File..tbCoRoom Set BedsLeft = BedsLeft + 1 Where RoomID = @OldRoom ; End ; IF Len(@RoomID) > 0 Begin Update Build_File..tbCoRoom Set BedsLeft = BedsLeft - 1 Where RoomID = @RoomID ; End ; end; else Begin Update Build_File..tbCoRoom Set AdditionalBed = AdditionalBed + 1 Where RoomID=@RoomID; End; /* Update Guarantor */ Select IDNum From tbPatientGuarantor Where IDNum = @IDNum ; IF @@RowCount = 0 Insert tbPatientGuarantor (IDNum, GuarantorName, GuarantorAddress, GuarantorEmployer, GuarantorTelNum ) Values (@IDNum, @GuarantorName, @GuarantorAddress, @GuarantorEmployer, @GuarantorTelNum ) Else Update tbPatientGuarantor Set GuarantorName = @GuarantorName, GuarantorAddress = @GuarantorAddress, GuarantorEmployer = @GuarantorEmployer, GuarantorTelNum = @GuarantorTelNum Where IDNum = @IDNum ; /* Update History file */ Select IDNum From tbPatientHistory Where IDNum = @IDNum If @@RowCount = 0 Insert tbPatientHistory (IDNum, AdmDiagnosis, ChiefComplaint, AdmImpression ) Values (@IDNum, @AdmDiagnosis, @ChiefComplaint, @AdmImpression ) ; Else Update tbPatientHistory Set AdmDiagnosis = @AdmDiagnosis, ChiefComplaint = @ChiefComplaint, AdmImpression = @AdmImpression where IDNum = @IDNum ; /* Update Informant Table */ Select IDNum From tbPatientInformant Where IDNum = @IDNum If @@RowCount = 0 Insert tbPatientInformant (IDNum, InformantName, InformantAddress, InformantRelation, InformantTelNum ) Values (@IDNum, @InformantName, @InformantAddress, @InformantRelation, @InformantTelNum ) ; Else Update tbPatientInformant Set InformantName = @InformantName, InformantAddress = @InformantAddress, InformantRelation = @InformantRelation, InformantTelNum = @InformantTelNum Where IDNum = @IDNum ; GO ------------------------------------------------------------------------------------------------ GO Alter PROCEDURE [dbo].[spOPD_AssessmentStatus]-- 0 @Type as varchar(1), @SearchCriteria varchar(50) = '' AS if @Type = '0' --loading begin select a.assessnum [TransNum], a.[Name] [Patient Name], a.ItemID [Code], case when A.RevenueID = 'MD' then 'Dr. ' + isnull(D.LastName,'') + ', ' + isnull(D.FirstName,'') else Exam.Description end as Description, a.RefNum [Assess #], cast(a.Amount as numeric(12,2)) [Amount], case isnull(a.RecordStatus, '') when '' then 'PENDING'-- CONVERT(varchar(10), a.TransDate, 101) when '1' then a.ORNumber when 'R' then 'REVOKED' end as Status, CONVERT(varchar(10), a.TransDate, 101) Transdate, a.RevenueID, a.ItemID from BILLING..tbcashassessment a left join Billing..tbbillexamlisting Exam on A.RevenueID = Exam.RevenueID and A.Itemid = Exam.ItemID left join BUILD_FILE..tbCoDoctor d on a.ItemID = d.DoctorID where a.DepartmentID = 'OP' --and a.name like @SearchCriteria + '%' order by cast(a.TransDate as float) desc end if @type = '1' --search by name begin select a.assessnum [TransNum], a.[Name] [Patient Name], a.ItemID [Code], case when A.RevenueID = 'MD' then 'Dr. ' + isnull(D.LastName,'') + ', ' + isnull(D.FirstName,'') else Exam.Description end as Description, a.RefNum [Assess #], cast(a.Amount as numeric(12,2)) [Amount], case isnull(a.RecordStatus, '') when '' then 'PENDING'-- CONVERT(varchar(10), a.TransDate, 101) when '1' then a.ORNumber when 'R' then 'REVOKED' end as Status, CONVERT(varchar(10), a.TransDate, 101) Transdate, a.RevenueID, a.ItemID from BILLING..tbcashassessment a left join Billing..tbbillexamlisting Exam on A.RevenueID = Exam.RevenueID and A.Itemid = Exam.ItemID left join BUILD_FILE..tbCoDoctor d on a.ItemID = d.DoctorID where a.DepartmentID = 'OP' and a.name like '%'+ @SearchCriteria + '%' order by a.TransDate end if @type = '2' --search by Refnum begin select a.assessnum [TransNum], a.[Name] [Patient Name], a.ItemID [Code], case when A.RevenueID = 'MD' then 'Dr. ' + isnull(D.LastName,'') + ', ' + isnull(D.FirstName,'') else Exam.Description end as Description, a.RefNum [Assess #], cast(a.Amount as numeric(12,2)) [Amount], case isnull(a.RecordStatus, '') when '' then 'PENDING'-- CONVERT(varchar(10), a.TransDate, 101) when '1' then a.ORNumber when 'R' then 'REVOKED' end as Status, CONVERT(varchar(10), a.TransDate, 101) Transdate, a.RevenueID, a.ItemID from BILLING..tbcashassessment a left join Billing..tbbillexamlisting Exam on A.RevenueID = Exam.RevenueID and A.Itemid = Exam.ItemID left join BUILD_FILE..tbCoDoctor d on a.ItemID = d.DoctorID where a.DepartmentID = 'OP' and a.Refnum like @SearchCriteria + '%' order by a.TransDate end if @type = '3' --search by Refnum begin select a.assessnum [TransNum], a.[Name] [Patient Name], a.ItemID [Code], case when A.RevenueID = 'MD' then 'Dr. ' + isnull(D.LastName,'') + ', ' + isnull(D.FirstName,'') else Exam.Description end as Description, a.RefNum [Assess #], cast(a.Amount as numeric(12,2)) [Amount], case isnull(a.RecordStatus, '') when '' then 'PENDING'-- CONVERT(varchar(10), a.TransDate, 101) when '1' then a.ORNumber when 'R' then 'REVOKED' end as Status, CONVERT(varchar(10), a.TransDate, 101) Transdate, a.RevenueID, a.ItemID from BILLING..tbcashassessment a left join Billing..tbbillexamlisting Exam on A.RevenueID = Exam.RevenueID and A.Itemid = Exam.ItemID left join BUILD_FILE..tbCoDoctor d on a.ItemID = d.DoctorID where a.DepartmentID = 'OP' and a.Idnum like @SearchCriteria + '%' order by a.RefNum desc end GO ------------------------------------------------------------------------------------------------ USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadAddedInfo] Script Date: 12/03/2014 10:35:46 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_LoadAddedInfo] @IDNum as varchar(10) AS Select P2.PackageID, P2.AdmissionTypeID, P.Package, A.AdmissionType, P2.SeniorCitizen, M.Title, N.NoticeOfAdmID, N.NoticeOfAdm, M.Cellnum, M.EmailAddress, --M.SpouseEmployer, G.GuarantorRelation, M.GSISNum, M.SSSNum, M.HospPlan, M.SeniorCitizenID, M2.SpousePosition, M2.SpouseAddress, M2.SpouseTelNum, M2.FatherOccupation, M2.MotherOccupation, M2.EmployerPosition, G.GuarantorOccupation, I.InformantOccupation, --C.AccountNum, P1.AccountNumII as AccountNum, C.Company, P2.Transient, P1.Cardnum, M.BirthDate, P2.DTR, --isnull(P1.EMBARKATIONDATE,0)as Embarkationdate, --isnull(P1.DISEMBARKATIONDATE,0)as Disembarkationdate, --P1.Category AS CategoryDesc, --P1.VesID AS VesID, --V.VesselName AS VesselName, M2.SpouseEmpName as SpouseEmployer, P2.OkeyPH From Patient_Data..tbPatient2 P2 Left Outer Join Build_File..tbCoAdmPackage P on P2.PackageID = P.PackageID Left Outer Join Build_File..tbCoAdmissionType A on A.AdmissionTypeID = p2.AdmissionTypeID Left Outer Join Patient_Data..tbPatient P1 on P2.IdNum = P1.IDNum left Outer Join Patient_Data..tbMaster M on P1.HospNum = M.HospNum left Outer Join Build_File..tbCoNoticeOfAdm N on P2.NoticeOfAdmID = N.NoticeOfAdmID left Outer Join Patient_Data..tbPatientGuarantor G on P1.IdNum = G.IdNum left Outer Join Patient_Data..tbMaster2 M2 on P1.HospNum = M2.HospNum Left Outer Join Patient_Data..tbPatientInformant I on P1.Idnum = I.IdNum Left Outer Join Build_file..tbcoCompany C on P1.AccountNumII = C.AccountNum --LEFT OUTER JOIN Build_File..tbCoVessel V ON P1.VesID = V.VesselCode Where P2.IDNum = @IDNum; GO ------------------------------------------------------------------------------------------------------------------------ GO ALTER PROCEDURE [dbo].[sp_Med_RepLeadingCausesOfMorbidity] @StartDate As VarChar(10), @EndDate As VarChar(10) AS Declare @TotalPatients as Integer; Set @TotalPatients = (Select Count(*) FROM tbPatient Patient WHERE DcrDate between @StartDate and @EndDate + ' 23:59:59.99' and Patient.ResultID <> '4') SELECT DiagnosisID, Max(Isnull(Diagnosis, 'No Diagnosis')) as Diagnosis, Sum(Case When Age = 0 and Sex = 'M' then 1 Else 0 End) as Age0_Male, Sum(Case When Age = 0 and Sex = 'F' then 1 Else 0 End) as Age0_Female, Sum(Case When Age between 1 and 4 and Sex = 'M' then 1 Else 0 End) as Age1to4_Male, Sum(Case When Age between 1 and 4 and Sex = 'F' then 1 Else 0 End) as Age1to4_female, Sum(Case When Age between 5 and 9 and Sex = 'M' then 1 Else 0 End) as Age5to9_Male, Sum(Case When Age between 5 and 9 and Sex = 'F' then 1 Else 0 End) as Age5to9_female, Sum(Case When Age between 10 and 14 and Sex = 'M' then 1 Else 0 End) as Age10to14_Male, Sum(Case When Age between 10 and 14 and Sex = 'F' then 1 Else 0 End) as Age10to14_female, Sum(Case When Age between 15 and 19 and Sex = 'M' then 1 Else 0 End) as Age15to19_Male, Sum(Case When Age between 15 and 19 and Sex = 'F' then 1 Else 0 End) as Age15to19_female, Sum(Case When Age between 20 and 24 and Sex = 'M' then 1 Else 0 End) as Age20to24_Male, Sum(Case When Age between 20 and 24 and Sex = 'F' then 1 Else 0 End) as Age20to24_female, Sum(Case When Age between 25 and 29 and Sex = 'M' then 1 Else 0 End) as Age25to29_Male, Sum(Case When Age between 25 and 29 and Sex = 'F' then 1 Else 0 End) as Age25to29_female, Sum(Case When Age between 30 and 34 and Sex = 'M' then 1 Else 0 End) as Age30to34_Male, Sum(Case When Age between 30 and 34 and Sex = 'F' then 1 Else 0 End) as Age30to34_female, Sum(Case When Age between 35 and 39 and Sex = 'M' then 1 Else 0 End) as Age35to39_Male, Sum(Case When Age between 35 and 39 and Sex = 'F' then 1 Else 0 End) as Age35to39_female, Sum(Case When Age between 40 and 44 and Sex = 'M' then 1 Else 0 End) as Age40to44_Male, Sum(Case When Age between 40 and 44 and Sex = 'F' then 1 Else 0 End) as Age40to44_female, Sum(Case When Age between 45 and 49 and Sex = 'M' then 1 Else 0 End) as Age45to49_Male, Sum(Case When Age between 45 and 49 and Sex = 'F' then 1 Else 0 End) as Age45to49_female, Sum(Case When Age between 50 and 54 and Sex = 'M' then 1 Else 0 End) as Age50to54_Male, Sum(Case When Age between 50 and 54 and Sex = 'F' then 1 Else 0 End) as Age50to54_female, Sum(Case When Age between 55 and 59 and Sex = 'M' then 1 Else 0 End) as Age55to59_Male, Sum(Case When Age between 55 and 59 and Sex = 'F' then 1 Else 0 End) as Age55to59_female, Sum(Case When Age between 60 and 64 and Sex = 'M' then 1 Else 0 End) as Age60to64_Male, Sum(Case When Age between 60 and 64 and Sex = 'F' then 1 Else 0 End) as Age60to64_female, Sum(Case When Age between 65 and 69 and Sex = 'M' then 1 Else 0 End) as Age65to69_Male, Sum(Case When Age between 65 and 69 and Sex = 'F' then 1 Else 0 End) as Age65to69_female, --Sum(Case When Age between 20 and 44 and Sex = 'M' then 1 Else 0 End) as Age20to44_Male, --Sum(Case When Age between 20 and 44 and Sex = 'F' then 1 Else 0 End) as Age20to44_female, --Sum(Case When Age between 45 and 64 and Sex = 'M' then 1 Else 0 End) as Age45to64_Male, --Sum(Case When Age between 45 and 64 and Sex = 'F' then 1 Else 0 End) as Age45to64_female, Sum(Case When Age > 70 and Sex = 'M' then 1 Else 0 End) as Age70Above_Male, Sum(Case When Age > 70 and Sex = 'F' then 1 Else 0 End) as Age70Above_female, Sum(Case When Sex = 'M' then 1 Else 0 End) as Total_Male, Sum(Case When Sex = 'F' then 1 Else 0 End) as Total_female, Count(*) as Total, @TotalPatients as TotalPx FROM (SELECT --Case When not IsNumeric(IsNull(Patient.Age,0)) = 1 then 0 Case When dbo.fn_ComputeAgeInYear(Master.BirthDate,patient.AdmDate) = 1 then 0 Else Cast( isNull(Patient.Age,0) as Decimal(3,0) ) End as Age, isnull(Master.Sex, 'M') as Sex, --SubString(Diag.DiagnosisID,1,3) As DiagnosisID, Diag.DiagnosisID As DiagnosisID, --Diag3Code.Description as Diagnosis D.Description as Diagnosis FROM tbPatient Patient INNER JOIN tbMaster Master ON Patient.HospNum = Master.HospNum INNER JOIN tbPatientDiagnosis Diag ON Patient.IDNum = Diag.IDNum and Diag.PrimaryDiagnosis = 'Y' Left Outer Join tbPatientProcedure Proced ON Patient.IDNum = Proced.IDNum and Proced.PrimaryProcedure = 'Y' inner Join Build_File..tbICDDiagMain D on Diag.DiagnosisID = D.Code --Left Outer Join Build_File..tbICDDiag3Code Diag3Code --ON SubString(Diag.DiagnosisID,1,3) = Diag3Code.Code WHERE DcrDate between @StartDate and @EndDate + ' 23:59:59.99' and Patient.ResultID <> '4' ) Patient Group by DiagnosisID GO ------------------------------------------------------------------------------------------------------------------------ GO ALTER PROCEDURE [dbo].[sp_Med_RepLeadingCausesOfMortality] @StartDate As VarChar(10), @EndDate As VarChar(10) AS Declare @TotalPatients as Integer; Set @TotalPatients = (Select Count(*) FROM tbPatient Patient WHERE DcrDate between @StartDate and @EndDate + ' 23:59:59.99' and Patient.ResultID = '4') SELECT DiagnosisID, Max(Isnull(Diagnosis, 'No Diagnosis')) as Diagnosis, Sum(Case When Age = 0 and Sex = 'M' then 1 Else 0 End) as Age0_Male, Sum(Case When Age = 0 and Sex = 'F' then 1 Else 0 End) as Age0_Female, Sum(Case When Age between 1 and 4 and Sex = 'M' then 1 Else 0 End) as Age1to4_Male, Sum(Case When Age between 1 and 4 and Sex = 'F' then 1 Else 0 End) as Age1to4_female, Sum(Case When Age between 5 and 9 and Sex = 'M' then 1 Else 0 End) as Age5to9_Male, Sum(Case When Age between 5 and 9 and Sex = 'F' then 1 Else 0 End) as Age5to9_female, Sum(Case When Age between 10 and 14 and Sex = 'M' then 1 Else 0 End) as Age10to14_Male, Sum(Case When Age between 10 and 14 and Sex = 'F' then 1 Else 0 End) as Age10to14_female, Sum(Case When Age between 15 and 19 and Sex = 'M' then 1 Else 0 End) as Age15to19_Male, Sum(Case When Age between 15 and 19 and Sex = 'F' then 1 Else 0 End) as Age15to19_female, Sum(Case When Age between 20 and 24 and Sex = 'M' then 1 Else 0 End) as Age20to24_Male, Sum(Case When Age between 20 and 24 and Sex = 'F' then 1 Else 0 End) as Age20to24_female, Sum(Case When Age between 25 and 29 and Sex = 'M' then 1 Else 0 End) as Age25to29_Male, Sum(Case When Age between 25 and 29 and Sex = 'F' then 1 Else 0 End) as Age25to29_female, Sum(Case When Age between 30 and 34 and Sex = 'M' then 1 Else 0 End) as Age30to34_Male, Sum(Case When Age between 30 and 34 and Sex = 'F' then 1 Else 0 End) as Age30to34_female, Sum(Case When Age between 35 and 39 and Sex = 'M' then 1 Else 0 End) as Age35to39_Male, Sum(Case When Age between 35 and 39 and Sex = 'F' then 1 Else 0 End) as Age35to39_female, Sum(Case When Age between 40 and 44 and Sex = 'M' then 1 Else 0 End) as Age40to44_Male, Sum(Case When Age between 40 and 44 and Sex = 'F' then 1 Else 0 End) as Age40to44_female, Sum(Case When Age between 45 and 49 and Sex = 'M' then 1 Else 0 End) as Age45to49_Male, Sum(Case When Age between 45 and 49 and Sex = 'F' then 1 Else 0 End) as Age45to49_female, Sum(Case When Age between 50 and 54 and Sex = 'M' then 1 Else 0 End) as Age50to54_Male, Sum(Case When Age between 50 and 54 and Sex = 'F' then 1 Else 0 End) as Age50to54_female, Sum(Case When Age between 55 and 59 and Sex = 'M' then 1 Else 0 End) as Age55to59_Male, Sum(Case When Age between 55 and 59 and Sex = 'F' then 1 Else 0 End) as Age55to59_female, Sum(Case When Age between 60 and 64 and Sex = 'M' then 1 Else 0 End) as Age60to64_Male, Sum(Case When Age between 60 and 64 and Sex = 'F' then 1 Else 0 End) as Age60to64_female, Sum(Case When Age between 65 and 69 and Sex = 'M' then 1 Else 0 End) as Age65to69_Male, Sum(Case When Age between 65 and 69 and Sex = 'F' then 1 Else 0 End) as Age65to69_female, --Sum(Case When Age between 20 and 44 and Sex = 'M' then 1 Else 0 End) as Age20to44_Male, --Sum(Case When Age between 20 and 44 and Sex = 'F' then 1 Else 0 End) as Age20to44_female, --Sum(Case When Age between 45 and 64 and Sex = 'M' then 1 Else 0 End) as Age45to64_Male, --Sum(Case When Age between 45 and 64 and Sex = 'F' then 1 Else 0 End) as Age45to64_female, Sum(Case When Age > 64 and Sex = 'M' then 1 Else 0 End) as Age70Above_Male, Sum(Case When Age > 64 and Sex = 'F' then 1 Else 0 End) as Age70Above_female, Sum(Case When Sex = 'M' then 1 Else 0 End) as Total_Male, Sum(Case When Sex = 'F' then 1 Else 0 End) as Total_female, Count(*) as Total, @TotalPatients as TotalPx FROM (SELECT --Case When not IsNumeric(IsNull(Patient.Age,0)) = 1 then 0 Case When dbo.fn_ComputeAgeInYear(Master.BirthDate,patient.AdmDate) = 1 then 0 Else Cast( isNull(Patient.Age,0) as Decimal(3,0) ) End as Age, isnull(Master.Sex, 'M') as Sex, --SubString(Diag.DiagnosisID,1,3) As DiagnosisID, Diag.DiagnosisID As DiagnosisID, --Diag3Code.Description as Diagnosis D.Description as Diagnosis FROM tbPatient Patient INNER JOIN tbMaster Master ON Patient.HospNum = Master.HospNum INNER JOIN tbPatientDiagnosis Diag ON Patient.IDNum = Diag.IDNum and Diag.PrimaryDiagnosis = 'Y' Left Outer Join tbPatientProcedure Proced ON Patient.IDNum = Proced.IDNum and Proced.PrimaryProcedure = 'Y' Inner Join Build_File..tbICDDiagMain D on Diag.DiagnosisID = D.Code --Left Outer Join Build_File..tbICDDiag3Code Diag3Code -- ON SubString(Diag.DiagnosisID,1,3) = Diag3Code.Code WHERE DcrDate between @StartDate and @EndDate + ' 23:59:59.99' and Patient.ResultID = '4' ) Patient Group by DiagnosisID GO ----------------------------------------------------------------------------------------------------------------- GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster] @InPatient as VarChar(1), @HospNum As VarChar(8), @LastName As VarChar(30), @FirstName As VarChar(50), @MiddleName As VarChar(30), @AccountNum As VarChar(8), @HouseStreet As VarChar(100), @Barangay As VarChar(50), @ZipCode As VarChar(4), @Sex As VarChar(1), @BirthDate As VarChar(10), @CivilStatus As VarChar(1), @Occupation As VarChar(30), @Age As VarChar(3), @TelNum As VarChar(15), @SSSGSISNum As VarChar(15), @BloodType As VarChar(3), @OPDNum As VarChar(8), @XrayNum As VarChar(8), @UltraNum As VarChar(8), @CTNum As VarChar(8), @BirthPlace As VarChar(50), @NationalityID As VarChar(2), @ReligionID As VarChar(2), @Spouse As VarChar(50), @Mother As VarChar(50), @Father As VarChar(50), @Employer As VarChar(50), @EmployerAddress As VarChar(200), @EmployerTelNum As VarChar(15), @ContactName As VarChar(30), @ContactAddress As VarChar(200), @ContactRelation As VarChar(30), @ContactTelNum As VarChar(15), @MotherAddress as VarChar(200), @MotherTelNum as VarChar(15), @FatherAddress as VarChar(200), @FatherTelNum as VarChar(15), @SpouseAddress As VarChar(50), @SpouseTelNum as VarChar(15), @AllergyID1 as VarChar(8), @AllergyID2 as VarChar(8), @AllergyID3 as VarChar(8), @AllergyID4 as VarChar(8), @AllergyID5 as VarChar(8), @BarangayCaptain As VarChar(50) AS Declare @NewHospNum VarChar(8) Declare @NewAccountNum varchar(8) if @AccountNum = 'New' or @AccountNum = 'NEW' begin set @NewAccountNum = @HospNum End Else Begin set @NewAccountNum = @AccountNum End Select HospNum From tbMaster Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMaster Set LastName = ltrim(rtrim(@LastName)), FirstName = ltrim(rtrim(@FirstName)), MiddleName = ltrim(rtrim(@MiddleName)), AccountNum = @NewAccountNum, HouseStreet = @HouseStreet, Barangay = @Barangay, ZipCode = @ZipCode, Sex = @Sex, BirthDate = Case When Len(@BirthDate) = 0 Then Null Else @BirthDate End, CivilStatus = @CivilStatus, Occupation = @Occupation, Age = @Age, TelNum = @TelNum, SSSGSISNum = @SSSGSISNum, BloodType = @BloodType, OPDNum = @OPDNum, XrayNum = @XrayNum, UltraNum = @UltraNum, CTNum = @UltraNum from tbMaster Where HospNum = @HospNum ; End ; Else Begin Insert tbMaster ( HospNum,LastName,FirstName,MiddleName,AccountNum, HouseStreet,Barangay,ZipCode,Sex,BirthDate,CivilStatus, Occupation,Age,TelNum,SSSGSISNum,BloodType,OPDNum,XrayNum, UltraNum,CTNum) Values ( @HospNum,ltrim(rtrim(@LastName)),ltrim(rtrim(@FirstName)),ltrim(rtrim(@MiddleName)),@NewAccountNum, @HouseStreet,@Barangay,@ZipCode,@Sex,@BirthDate,@CivilStatus, @Occupation,@Age,@TelNum,@SSSGSISNum,@BloodType,@OPDNum,@XrayNum, @UltraNum,@CTNum); end ; If @InPatient = 'Y' Begin Select HospNum From tbMaster2 Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMaster2 Set BirthPlace = @BirthPlace, NationalityID = @NationalityID, ReligionID = @ReligionID, Spouse = @Spouse, Mother = @Mother, Father = @Father, Employer = @Employer, EmployerAddress = @EmployerAddress, EmployerTelNum = @EmployerTelNum, ContactName = @ContactName, ContactAddress = @ContactAddress, ContactRelation = @ContactRelation, ContactTelNum = @ContactTelNum, MotherAddress = @MotherAddress, MotherTelNum = @MotherTelNum, FatherAddress = @FatherAddress, FatherTelNum = @FatherTelNum, SpouseAddress = @SpouseAddress, SpouseTelNum = @SpouseTelNum, BarangayCaptain = @BarangayCaptain Where HospNum = @HospNum ; End ; Else Begin Insert Into tbMaster2 (HospNum,BirthPlace, NationalityID,ReligionID,Spouse, Mother, Father, Employer, EmployerAddress, EmployerTelNum, ContactName, ContactAddress, ContactRelation, ContactTelNum, MotherAddress, MotherTelNum, FatherAddress, FatherTelNum, SpouseAddress, SpouseTelNum, BarangayCaptain ) Values (@HospNum, @BirthPlace, @NationalityID, @ReligionID, @Spouse, @Mother, @Father, @Employer, @EmployerAddress, @EmployerTelNum, @ContactName, @ContactAddress, @ContactRelation, @ContactTelNum, @MotherAddress, @MotherTelNum, @FatherAddress, @FatherTelNum, @SpouseAddress, @SpouseTelNum, @BarangayCaptain); End ; Select HospNum From tbMasterAllergies Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMasterAllergies Set AllergyID1 = @AllergyID1, AllergyID2 = @AllergyID2, AllergyID3 = @AllergyID3, AllergyID4 = @AllergyID4, AllergyID5 = @AllergyID5 Where HospNum = @HospNum ; End ; Else BEGIN Insert tbMasterAllergies (HospNum, AllergyID1,AllergyID2, AllergyID3,AllergyID4,AllergyID5) Values (@HospNum, @AllergyID1, @AllergyID2,@AllergyID3,@AllergyID4,@AllergyID5 ) ; END ; end ; GO ----------------------------------------------------------------------------------------------------------------- CREATE INDEX [by_RequestDateRefNum] ON Radiology..tbRISPatientQueueNo([RequestDate],[RefNum]) GO ----------------------------------------------------------------------------------------------------------------- GO USE [REGISTER] GO /****** Object: StoredProcedure [dbo].[spReg_SearchMedicalHistory] Script Date: 03/05/2015 18:17:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[spReg_SearchMedicalHistory] @Hospnum AS varChar(8), @IDNum as varchar(15)= null AS --Declare -- @Hospnum AS varChar(8), -- @IDNum as varchar(15)= null --Set @Hospnum ='340762' --Set @IDNum ='1785555B' If (Select COUNT(*) from REGISTER..tbMedicalHistory where IDNum =@IDNum) = 1 SELECT A.*,B.AdmDate FROM REGISTER..tbMedicalHistory A Left Outer Join PATIENT_DATA..TBOUTPATIENT B on A.IDNum = B.IDNum WHERE A.hospnum = @Hospnum and A.IDNum = @IDNum Order by B.AdmDate desc Else --Select 'Previous' SELECT Top 1 A.*,B.AdmDate FROM REGISTER..tbMedicalHistory A Left Outer Join PATIENT_DATA..TBOUTPATIENT B on A.IDNum = B.IDNum WHERE A.hospnum = @Hospnum --and A.IDNum = @IDNum Order by B.AdmDate desc GO ----------------------------------------------------------------------------------------------------------------- USE [REGISTER] GO Alter TABLE [dbo].[tbMedicalHistory]Add [Eye] [varchar](1) NULL, [Veins] [varchar](1) NULL, [Blood] [varchar](1) NULL, [Thyroid] [varchar](1) NULL, --[Skin] [varchar](1) NULL, [Allergies] [varchar](1) NULL, [Infectious] [varchar](1) NULL, [Genital] [varchar](1) NULL, [Pregnancy] [varchar](1) NULL, [Sleep] [varchar](1) NULL, [Depression] [varchar](1) NULL, [Memory] [varchar](1) NULL, [Balance] [varchar](1) NULL, [mobility] [varchar](1) NULL, [Amputation] [varchar](1) NULL, [GeneralAnswer] [varchar](100) NULL, [Repatriated] [varchar](1) NULL, [Hospitalized] [varchar](1) NULL, [Declared] [varchar](1) NULL, [Revoked] [varchar](1) NULL, [Aware] [varchar](1) NULL, [Healthy] [varchar](1) NULL, [Allergic] [varchar](1) NULL, [Prescription] [varchar](1) NULL, [Comments] [varchar](100) NULL GO ----------------------------------------------------------------------------------------------------------------- GO USE [REGISTER] GO Alter TABLE [dbo].[tbMedicalHistory]Add [Gynaecological] [varchar](1) NULL, [LastMenstrualPeriod] [datetime] NULL, [LastTropicalDisease] [datetime] NULL, [Schistosomiasis] [varchar](1) NULL, [LastSchistosomiasis] [datetime] NULL, [LastAllergy] [varchar](50) NULL, [AllegicMedication] [varchar](250) NULL GO ----------------------------------------------------------------------------------------------------------------- GO USE [REGISTER] GO /****** Object: StoredProcedure [dbo].[spReg_SaveMedicalHistory] Script Date: 03/05/2015 18:16:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[spReg_SaveMedicalHistory] @Hospnum AS varchar(8), @Lastname AS varchar(30), @Firstname AS varchar(30), @Middlename AS varchar(30), @Nose AS varchar(1), @Ear AS varchar(1), @Asthma AS varchar(1), @Tuberculosis AS varchar(1), @Lung AS varchar(1), @HighBlood AS varchar(1), @Heart AS varchar(1), @Rheumatic AS varchar(1), @Diabetes AS varchar(1), @Endocrine AS varchar(1), @Cancer AS varchar(1), @Mental AS varchar(1), @Head AS varchar(1), @Hernia AS varchar(1), @Rheumatism AS varchar(1), @Typhoid AS varchar(1), @Trachoma AS varchar(1), @Stomach AS varchar(1), @Abdominal AS varchar(1), @Kidney AS varchar(1), @STD AS varchar(1), @Genetic AS varchar(1), @Malaria AS varchar(1), @Operations AS varchar(1), @Tropical AS varchar(1), @Chronic AS varchar(1), @Fainting AS varchar(1), @Headaches AS varchar(1), @Dizziness AS varchar(1), --@WhatOperation AS varchar(30), @Eye AS varchar(1), @Veins AS varchar(1), @Blood AS varchar(1), @Thyroid AS varchar(1), @Skin AS varchar(1), @Allergies AS varchar(1), @Infectious AS varchar(1), @Genital AS varchar(1), @Pregnancy AS varchar(1), @Sleep AS varchar(1), --@Epilepsy AS varchar(1), @Depression AS varchar(1), @Memory AS varchar(1), @Balance AS varchar(1), @mobility AS varchar(1), @Amputation AS varchar(1), @Repatriated AS varchar(1), @Hospitalized AS varchar(1), @Declared AS varchar(1), @Revoked AS varchar(1), @Aware AS varchar(1), @Healthy AS varchar(1), @Allergic AS varchar(1), @Prescription AS varchar(1), @Gynaecological as varchar(1), @LastMenstrualPeriod as datetime, @LastTropicalDisease as datetime, @Schistosomiasis as varchar(1), @LastSchistosomiasis as datetime, @LastAllergy as varchar(50), @AllegicMedication as varchar(250), @GeneralAnswer AS varchar(50), @Comments AS varchar(500), @IDnum as varchar(15)= null AS SELECT * FROM REGISTER..tbMedicalHistory WHERE Hospnum = @Hospnum and IDNum = @IDnum ; IF @@RowCount > 0 BEGIN UPDATE REGISTER..tbMedicalHistory SET Nose = @Nose, Ear = @Ear, Asthma = @Asthma, Tuberculosis = @Tuberculosis, Lung = @Lung, HighBlood = @HighBlood, Heart = @Heart, Rheumatic = @Rheumatic, Diabetes = @Diabetes, Endocrine = @Endocrine, Cancer = @Cancer, Mental = @Mental, Head = @Head, Hernia = @Hernia, Rheumatism = @Rheumatism, Typhoid = @Typhoid, Trachoma = @Trachoma, Stomach = @Stomach, Abdominal = @Abdominal, Kidney = @Kidney, STD = @STD, Genetic = @Genetic, Malaria = @Malaria, Operations = @Operations, Tropical = @Tropical, Chronic = @Chronic, Fainting = @Fainting, Headaches = @Headaches, Dizziness = @Dizziness, -- WhatOperation = @WhatOperation, Eye = @Eye, Veins = @Veins, Blood = @Blood, Thyroid = @Thyroid , Skin = @Skin, Allergies = @Allergies , Infectious = @Infectious, Genital =@Genital , Pregnancy =@Pregnancy , Sleep = @Sleep, --Epilepsy= @Epilepsy, Depression =@Depression , Memory = @Memory, Balance = @Balance, mobility = @mobility, Amputation = @Amputation, Repatriated = @Repatriated, Hospitalized = @Hospitalized, Declared = @Declared, Revoked = @Revoked, Aware = @Aware, Healthy = @Healthy, Allergic = @Allergic, Prescription = @Prescription, Gynaecological = @Gynaecological, LastMenstrualPeriod = @LastMenstrualPeriod, LastTropicalDisease = @LastTropicalDisease, Schistosomiasis = @Schistosomiasis, LastSchistosomiasis = @LastSchistosomiasis, LastAllergy = @LastAllergy, AllegicMedication = @AllegicMedication, GeneralAnswer = @GeneralAnswer, Comments = @Comments WHERE Hospnum = @Hospnum And IDNum = @IDnum; END ELSE BEGIN INSERT INTO REGISTER..tbMedicalHistory ( Hospnum, Lastname, Firstname, Middlename, Nose, Ear, Asthma, Tuberculosis, Lung, HighBlood, Heart, Rheumatic, Diabetes, Endocrine, Cancer, Mental, Head, Hernia, Rheumatism, Typhoid, Trachoma, Stomach, Abdominal, Kidney, STD, Genetic, Malaria, Operations, Tropical, Chronic, Fainting, Headaches, Dizziness,--WhatOperation, Eye, Veins , Blood, Thyroid , Skin , Allergies , Infectious, Genital , Pregnancy , Sleep , --Epilepsy, Depression , Memory , Balance , mobility, Amputation, Repatriated, Hospitalized, Declared, Revoked, Aware, Healthy, Allergic, Prescription, Gynaecological, LastMenstrualPeriod, LastTropicalDisease, Schistosomiasis, LastSchistosomiasis, LastAllergy, AllegicMedication, GeneralAnswer, Comments,IDNum) VALUES ( @Hospnum, @Lastname, @Firstname, @Middlename, @Nose, @Ear, @Asthma, @Tuberculosis, @Lung, @HighBlood, @Heart, @Rheumatic, @Diabetes, @Endocrine, @Cancer, @Mental, @Head, @Hernia, @Rheumatism, @Typhoid, @Trachoma, @Stomach, @Abdominal, @Kidney, @STD, @Genetic, @Malaria, @Operations, @Tropical, @Chronic, @Fainting, @Headaches, @Dizziness,--@WhatOperation , @Eye, @Veins , @Blood, @Thyroid , @Skin , @Allergies , @Infectious, @Genital , @Pregnancy , @Sleep , --@Epilepsy, @Depression , @Memory , @Balance , @mobility, @Amputation, @Repatriated, @Hospitalized, @Declared, @Revoked, @Aware, @Healthy, @Allergic, @Prescription, @Gynaecological, @LastMenstrualPeriod, @LastTropicalDisease, @Schistosomiasis, @LastSchistosomiasis, @LastAllergy, @AllegicMedication, @GeneralAnswer, @Comments,@IDnum) ; END GO ----------------------------------------------------------------------------------------------------------------- Use Station go ALTER PROCEDURE [dbo].[Nurse_VitalSigns] @IDNum As Varchar(10) AS declare @Enddate as datetime; set @Enddate = convert(varchar(10),getdate(),101) Select Distinct TransDate [Date / Time], ISNULL(bp,'') [Blood Pressure], ISNULL(temperature,'') [Temperature], ISNULL(resprate,'') [Respiratory Rate], ISNULL(pulserate,'') [Pulse Rate], ISNULL(o2sat,'') [Oxygen Saturation], ISNULL(PM.LastName+','+PM.FirstName,'') [Encoder], ProcItemID as ItemID, ISNULL(Protocol,'') [Protocol], ISNULL(Remarks,'') [Remarks], ISNULL(Remarks2,'') [Remarks2], case when procitemid = '1' then DATEADD(hh,8,VitalSigns.Transdate) when procitemid = '2' then DATEADD(hh,1,VitalSigns.Transdate) when procitemid = '3' then DATEADD(hh,2,VitalSigns.Transdate) when procitemid = '4' then DATEADD(hh,4,VitalSigns.Transdate) when procitemid = '5' then DATEADD(mi,15,VitalSigns.Transdate) when procitemid = '6' then DATEADD(mi,30,VitalSigns.Transdate) end as ScheduleTime, TransDate From STATION..tbNurseVitalSigns VitalSigns Left Outer Join Password..tbPasswordMaster PM On VitalSigns.NInitial = PM.EmployeeID Where idnum=@IDNum --and Isnull(Remarks,'') = 'DONE' order by Transdate desc ----------------------------------------------------------------------------------------------------------------- use STATION GO ALTER PROCEDURE [dbo].[Nurse_UpdateVitalSigns] @IDNum As Varchar(10), @Transdate As Datetime, @BP As Varchar(7), @Temp As Varchar(8), @Pulse As Varchar(8), @Resp As Varchar(8), @Nurse As Varchar(20), @O2Sat As Varchar(20) = '', @Protocol As Varchar(50) = '', @ProcItemID as varchar(2) = '', @Remarks As Varchar(10) = 'DONE', @BeginningTime As Varchar(12) = '' --@Refnum As Varchar(50) = NULL AS Insert tbNurseVitalSigns (IDnum,Transdate,BP,Temperature,PulseRate,RespRate,Ninitial,O2Sat,Protocol,ProcItemID,Remarks,ScheduleTime) Values (@IDNum,@transdate,@BP,@Temp,@Pulse,@Resp,@Nurse,@O2Sat,@Protocol,@ProcItemID,@Remarks,@BeginningTime) INSERT INTO STATION..TBSTATIONLOGS (Idnum,Transdate,Remarks,UserID,Hostname,StationID) VALUES (@IDNum,GETDATE(),'ADD VITAL SIGNS: ' + @BP + ';' + @Temp + ';' + @Pulse + ';' + @Resp + ';' + @O2Sat + ';' + @Protocol + ';' + @ProcItemID + ';' + @Remarks + ';' + CONVERT(VARCHAR(20),@Transdate),@Nurse,HOST_NAME(),'') ----------------------------------------------------------------------------------------------------------------- USE [BUILD_FILE] GO /****** Object: StoredProcedure [dbo].[global_SearchCompany] Script Date: 11/24/2014 10:05:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[global_SearchCompany] @SearchCriteria [varchar](15), @Type [int] AS IF (@Type=0) BEGIN Select * From tbcoCompany Where AccountNum=@SearchCriteria and Status = 'A' END ELSE IF (@Type=1) BEGIN Select * From tbcoCompany Where Company=@SearchCriteria and Status = 'A' END ELSE IF (@Type=2) BEGIN Select * From tbcoCompany Where AccountNum Like @SearchCriteria + '%' and Status = 'A' END ELSE IF (@Type=3) BEGIN Select * From tbcoCompany Where Company Like @SearchCriteria + '%' and Status = 'A' END ELSE IF (@Type=4) BEGIN Select Company as [Company Name], AccountNum as Code From tbcoCompany Where (AccountNum=@SearchCriteria OR Company=@SearchCriteria) and Status = 'A' END ELSE IF (@Type=5) BEGIN Select Company as [Company Name], AccountNum as Code From tbcoCompany Where Company=@SearchCriteria and Status = 'A' END ELSE IF (@Type=6) BEGIN Select AccountNum as Code, Company as [Company Name] From tbcoCompany Where AccountNum Like @SearchCriteria + '%' and Status = 'A' END ELSE IF (@Type=7) BEGIN Select Company as [Company Name],AccountNum as Code From tbcoCompany Where Company Like @SearchCriteria + '%' and Status = 'A' END ELSE IF (@Type = 8) BEGIN Select CompanyID, AccountNum, Company, ISNULL( ContactPerson,'') [ContactPerson], ISNULL( Position,'') [Position], LTRIM(RTRIM(ISNULL( HouseStreet,'') + '' + ISNULL( Barangay,'') + '' + ISNULL( ZipCode,''))) [Address] From tbcoCompany Where AccountNum=@SearchCriteria END --Added By Angelo ELSE IF (@Type = 54) BEGIN Select Company as Description, AccountNum as Code, Isnull(Status,'A') AS Status From Build_File..tbcoCompany Where AccountNum = @SearchCriteria and Status = 'A' END GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateWatcher] Script Date: 07/10/2014 15:34:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateWatcher] @HospNum varchar(8), @IDNum as varchar(10), @LastName as varchar(30), @FirstName as varchar(30), @MiddleName as varchar(30), @Sex as Varchar(1), @RoomID as Varchar(8), @RoomRate as float(8), @AdmDate as varchar(25), @WatcherID as varchar(8), @AdditionalBed as varchar(1), @Relation as varchar(30) = null AS Declare @OldRate as float(8) select watcherID from tbadmwatchermaster where watcherID=@WatcherID; if @@rowcount = 0 begin Insert into tbAdmWatcherMaster(HospNum, IDNum, LastName, FirstName, MiddleName,Sex,RoomID, RoomRate,AdmDate,WatcherID,AdditionalBed,Relation) Values(@HospNum, @IDNum, @LastName, @FirstName, @MiddleName, @Sex, @RoomID, @RoomRate,@AdmDate,@WatcherID,@AdditionalBed,@Relation); end; else begin update tbAdmWatcherMaster set HospNum = @HospNum, IDNum = @IDNum, LastName = @LastName, FirstName = @FirstName, MiddleName = @MiddleName, Sex = @Sex, RoomID = @RoomID, RoomRate = @RoomRate, AdmDate = @AdmDate, AdditionalBed=@AdditionalBed, Relation = @Relation where WatcherID = @WatcherID end; select @OldRate = isnull(WatcherRate,0) from tbpatient where IDNum=@IDNum; Update tbPatient Set WithWatcher=1, WatcherRate = @OldRate + @RoomRate Where IDNum=@IDNum; IF Len(@RoomID) > 0 begin if @AdditionalBed = '0' Begin Update Build_File..tbCoRoom Set BedsLeft = BedsLeft - 1 Where RoomID = @RoomID ; End ; else begin Update Build_File..tbCoRoom Set AdditionalBed = AdditionalBed + 1 Where RoomID = @RoomID ; end; end; GO GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadPatient] Script Date: 07/21/2014 12:36:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_LoadPatient] --->Dated 02/08/2012 by sensei @HospNum AS VarChar(10) AS if exists(SELECT * FROM tbMaster M WHERE M.HospNum = @HospNum) begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, ISNULL(M.HouseStreet,'') HouseStreet, ISNULL(M.Barangay,'') Barangay, ISNULL(M.ZipCode,'') ZipCode, ISNULL(M.Sex,'') Sex, M.BirthDate, M.CivilStatus, ISNULL(M.Occupation,'') Occupation, ISNULL(M.Age,'') Age, ISNULL(M.TelNum,'') TelNum, ISNULL(M.PatientStatus,'') PatientStatus, ISNULL(M.OPDNum,'') OPDNum, ISNULL(M.XrayNum,'') XrayNum, ISNULL(M.UltraNum,'') UltraNum, ISNULL(M.SSSGSISNum,'') SSSGSISNum, ISNULL(M.CTNum,'') CTNum, ISNULL(M2.BirthPlace,'') BirthPlace, ISNULL(M2.NationalityID,'') NationalityID, ISNULL(M2.ReligionID,'') ReligionID, ISNULL(M2.Spouse,'') Spouse, ISNULL(M2.Father,'') Father, ISNULL(M2.Mother,'') Mother, ISNULL(M2.Employer,'') Employer, ISNULL(M2.EmployerAddress, '') EmployerAddress, ISNULL(M2.EmployerTelNum,'') EmployerTelNum, ISNULL(M2.ContactName,'') ContactName, ISNULL(M2.ContactAddress,'') ContactAddress, ISNULL(M2.ContactRelation,'') ContactRelation, ISNULL(M2.ContactTelNum,'') ContactTelNum, ISNULL(M.BloodType,'') BloodType, ISNULL(M2.MotherAddress,'') MotherAddress, ISNULL(M2.MotherTelNum, '') MotherTelNum, ISNULL(M2.FatherAddress,'') FatherAddress, ISNULL(M2.FatherTelNum,'') FatherTelNum, ISNULL(M2.SpouseAddress,'') SpouseAddress, ISNULL(M2.SpouseTelNum,'') SpouseTelNum, ISNULL(M2.barangaycaptain,'') barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] , ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum] FROM tbMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum WHERE M.HospNum = @HospNum end else begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, M.HouseStreet, M.Barangay, M.ZipCode, M.Sex, M.BirthDate, M.CivilStatus, M.Occupation, M.Age, M.TelNum, M.PatientStatus, M.OPDNum, M.XrayNum, M.UltraNum, M.SSSGSISNum, M.CTNum, M2.BirthPlace, M2.NationalityID, M2.ReligionID, M2.Spouse, M2.Father, M2.Mother, M2.Employer, M2.EmployerAddress, M2.EmployerTelNum, M2.ContactName, M2.ContactAddress, M2.ContactRelation, M2.ContactTelNum, M.BloodType, M2.MotherAddress, M2.MotherTelNum, M2.FatherAddress, M2.FatherTelNum, M2.SpouseAddress, M2.SpouseTelNum, M2.barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] ,ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum] FROM tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum WHERE M.HospNum = @HospNum end GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateLogfile] Script Date: 07/21/2014 10:11:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateLogfile] @Hospnum as varchar(10), @IDNum As VarChar(10), @UserID As VarChar(8), @Remarks As text AS Insert Into tbAdmPatientInfoUpdate (Hospnum, IDNum, TransDate, UserID, Remarks) Values (@Hospnum, @IDNum, GetDate(), @UserID, @Remarks ) GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadAddedInfo] Script Date: 07/24/2014 13:21:00 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_LoadAddedInfo] @IDNum as varchar(10) AS Select P2.PackageID, P2.AdmissionTypeID, P.Package, A.AdmissionType, P2.SeniorCitizen, M.Title, N.NoticeOfAdmID, N.NoticeOfAdm, M.Cellnum, M.EmailAddress, --M.SpouseEmployer, G.GuarantorRelation, M.GSISNum, M.SSSNum, M.HospPlan, M.SeniorCitizenID, M2.SpousePosition, M2.SpouseAddress, M2.SpouseTelNum, M2.FatherOccupation, M2.MotherOccupation, M2.EmployerPosition, G.GuarantorOccupation, I.InformantOccupation, C.AccountNum, C.Company, P2.Transient, P1.Cardnum, M.BirthDate, P2.DTR, --isnull(P1.EMBARKATIONDATE,0)as Embarkationdate, --isnull(P1.DISEMBARKATIONDATE,0)as Disembarkationdate, --P1.Category AS CategoryDesc, --P1.VesID AS VesID, --V.VesselName AS VesselName, M2.SpouseEmpName as SpouseEmployer, P2.OkeyPH From Patient_Data..tbPatient2 P2 Left Outer Join Build_File..tbCoAdmPackage P on P2.PackageID = P.PackageID Left Outer Join Build_File..tbCoAdmissionType A on A.AdmissionTypeID = p2.AdmissionTypeID Left Outer Join Patient_Data..tbPatient P1 on P2.IdNum = P1.IDNum left Outer Join Patient_Data..tbMaster M on P1.HospNum = M.HospNum left Outer Join Build_File..tbCoNoticeOfAdm N on P2.NoticeOfAdmID = N.NoticeOfAdmID left Outer Join Patient_Data..tbPatientGuarantor G on P1.IdNum = G.IdNum left Outer Join Patient_Data..tbMaster2 M2 on P1.HospNum = M2.HospNum Left Outer Join Patient_Data..tbPatientInformant I on P1.Idnum = I.IdNum Left Outer Join Build_file..tbcoCompany C on P1.AccountNumII = C.AccountNum --LEFT OUTER JOIN Build_File..tbCoVessel V ON P1.VesID = V.VesselCode Where P2.IDNum = @IDNum; GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateAddedInfo] Script Date: 07/24/2014 13:20:20 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateAddedInfo] @IDNum as varchar(12), @PackageID as varchar(5), @AdmissionTypeID as varchar(2), @UserID as varchar(10) , @SeniorCitizen as bit = 0, @WithAdmissionKit as bit = 0, @SeniorCitizenID as varchar(20)= Null, @isOutborn as varchar(1) = null, @isWellBaby as varchar(1) = null, @MotherIDNum as varchar(15) = Null, @WithTransferFee as bit = 0, @isCaesarianDelivery as varchar(1) = null, @NoticeOfAdmID as varchar(10), @CellNumber as varchar(12)=null, @EmailAdd as varchar(40), @SpouseEmp as varchar(30), @GuarantorRelation as varchar(30), @GSISNum as varchar(20), @SSSNum as varchar(20), @HospPlan as varchar(1), @SpousePosition as varchar(40) = null, @SpouseAddress as varchar(80)= null, @SpouseTelNum as varchar(15)= null, @FatherOccupation as varchar(40) = null, @MotherOccupation as varchar(40) = null, @Employerposition as varchar(40)= null, @InformantOccupation as varchar(40)= null, @GuarantorPosition as varchar(40)= null, @AccountNumII as varchar(15)= null, @Transient as bit = 0, @DTR as varchar(20)=null--, --@VesselID as varchar(30) =null, --@Category as varchar(10)=null, --@Embarkation datetime = null, --@Disembarkation datetime = null AS Declare @Hospnum as varchar(15) Update tbPatient2 Set PackageID = @PackageID, AdmissionTypeID = @AdmissionTypeID, SeniorCitizen = @SeniorCitizen, Transient = @Transient, DTR=@DTR, NoticeOfAdmID = @NoticeOfAdmID Where IDNum = @IDNum; Update tbPatient Set UserID = @UserID, WithAdmissionKit = @WithAdmissionKit, isOutborn = case when @isOutborn = '' then null else @isOutborn end, isWellBaby = case when @isWellBaby = '' then null else @isWellBaby end, MotherIDNum = @MotherIDNum, WithTransferFee = @WithTransferFee, isCaesarianDelivery = case when @isCaesarianDelivery = '' then null else @isCaesarianDelivery end, AccountNumII = @AccountNumII--, --Cardnum=@Cardnum --VesID = @VesselID, --Category = @Category, --EmbarkationDate = @Embarkation, --DisembarkationDate = @Disembarkation Where IDNum = @IDNum; set @Hospnum = (Select Hospnum from tbPatient where IDNum = @IDNum); Update tbMaster Set SeniorCitizen = @SeniorCitizen, SeniorCitizenID = @SeniorCitizenID, Cellnum = @CellNumber, EmailAddress = @EmailAdd, --SpouseEmployer = @SpouseEmp, GSISNum = @GSISNum, SSSNum = @SSSNum, HospPlan = @HospPlan --AccountNumII = @AccountNumII, --BirthDate=@DateOfBirth Where Hospnum = @Hospnum; Update tbPatientGuarantor set GuarantorRelation = @GuarantorRelation, GuarantorOccupation = @GuarantorPosition where IDNum = @IDNum; Update tbMaster2 set SpousePosition = @SpousePosition, SpouseAddress = @SpouseAddress, SpouseTelNum = @SpouseTelNum, FatherOccupation = @FatherOccupation, MotherOccupation = @MotherOccupation, Employerposition = @EmployerPosition, SpouseEmpName = @SpouseEmp Where Hospnum = @Hospnum; Update tbPatientInformant set InformantOccupation = @InformantOccupation where IDNum = @IDNum; GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadInPatient] Script Date: 07/24/2014 13:19:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_LoadInPatient] @IDNum as VarChar(10), @HospNum as varchar(10) AS --Optimized by Angelo 04/15/2006 IF LEN(@Idnum) > 0 BEGIN Select tbPatient2.*, tbPatient.*,tbpatientInformant.*,tbpatientGuarantor.*, tbpatientHistory.AdmDiagnosis,tbpatientHistory.ChiefComplaint,tbpatientHistory.Precautions,tbpatientHistory.AdmImpression from tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Left Outer Join tbpatientInformant on tbpatient.IdNum =tbpatientInformant.IdNum Left Outer Join tbpatientGuarantor on tbpatient.idnum = tbpatientGuarantor.idnum Left Outer Join tbpatientHistory on tbpatient.IdNum = tbpatientHistory.IDNum Where tbPatient.Idnum = @IdNum END ELSE --Default: Search By IdNum and HospNum BEGIN Select tbPatient2.*, tbPatient.*,tbpatientInformant.*,tbpatientGuarantor.*, tbpatientHistory.AdmDiagnosis,tbpatientHistory.ChiefComplaint,tbpatientHistory.Precautions,tbpatientHistory.AdmImpression From tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Left Outer Join tbpatientInformant on tbpatient.IdNum =tbpatientInformant.IdNum Left Outer Join tbpatientGuarantor on tbpatient.idnum = tbpatientGuarantor.idnum Left Outer Join tbpatientHistory on tbpatient.IdNum = tbpatientHistory.IDNum Where tbPatient.IDNum = @IDNum or (Len(@IDNum) = 0 and tbPatient.HospNum = @HospNum and tbPatient.DcrDate is Null ) END Select tbPatient2.*, tbPatient.*,tbpatientInformant.*,tbpatientGuarantor.*, tbpatientHistory.AdmDiagnosis,tbpatientHistory.ChiefComplaint,tbpatientHistory.Precautions,tbpatientHistory.AdmImpression From tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Left Outer Join tbpatientInformant on tbpatient.IdNum =tbpatientInformant.IdNum Left Outer Join tbpatientGuarantor on tbpatient.idnum = tbpatientGuarantor.idnum Left Outer Join tbpatientHistory on tbpatient.IdNum = tbpatientHistory.IDNum Where tbPatient.IDNum = @IDNum or (Len(@IDNum) = 0 and tbPatient.HospNum = @HospNum and tbPatient.DcrDate is Null ) --Angelo --Commented Orig 04/15/2006 /*CREATE PROCEDURE sp_Adm_LoadInPatient @IDNum as VarChar(8), @HospNum as varchar(8) AS --Optimized by Angelo 04/15/2006 IF LEN(@HospNum) > 0 BEGIN Select tbPatient2.*, tbPatient.* from tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Where (tbPatient.HospNum = @HospNum and tbPatient.DcrDate is Null ) END ELSE --Default: Search By IdNum BEGIN Select tbPatient2.*, tbPatient.* from tbPatient Left Outer Join tbPatient2 On tbPatient.IDNum = tbPatient2.IDNum Where tbPatient.IDNum = @IDNum END*/ GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadPatient] Script Date: 07/24/2014 13:19:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_LoadPatient] --->Dated 02/08/2012 by sensei @HospNum AS VarChar(10) AS if exists(SELECT * FROM tbMaster M WHERE M.HospNum = @HospNum) begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, ISNULL(M.HouseStreet,'') HouseStreet, ISNULL(M.Barangay,'') Barangay, ISNULL(M.ZipCode,'') ZipCode, ISNULL(M.Sex,'') Sex, M.BirthDate, M.CivilStatus, ISNULL(M.Occupation,'') Occupation, ISNULL(M.Age,'') Age, ISNULL(M.TelNum,'') TelNum, ISNULL(M.PatientStatus,'') PatientStatus, ISNULL(M.OPDNum,'') OPDNum, ISNULL(M.XrayNum,'') XrayNum, ISNULL(M.UltraNum,'') UltraNum, ISNULL(M.SSSGSISNum,'') SSSGSISNum, ISNULL(M.CTNum,'') CTNum, ISNULL(M2.BirthPlace,'') BirthPlace, ISNULL(M2.NationalityID,'') NationalityID, ISNULL(M2.ReligionID,'') ReligionID, ISNULL(M2.Spouse,'') Spouse, ISNULL(M2.Father,'') Father, ISNULL(M2.Mother,'') Mother, ISNULL(M2.Employer,'') Employer, ISNULL(M2.EmployerAddress, '') EmployerAddress, ISNULL(M2.EmployerTelNum,'') EmployerTelNum, ISNULL(M2.ContactName,'') ContactName, ISNULL(M2.ContactAddress,'') ContactAddress, ISNULL(M2.ContactRelation,'') ContactRelation, ISNULL(M2.ContactTelNum,'') ContactTelNum, ISNULL(M.BloodType,'') BloodType, ISNULL(M2.MotherAddress,'') MotherAddress, ISNULL(M2.MotherTelNum, '') MotherTelNum, ISNULL(M2.FatherAddress,'') FatherAddress, ISNULL(M2.FatherTelNum,'') FatherTelNum, ISNULL(M2.SpouseAddress,'') SpouseAddress, ISNULL(M2.SpouseTelNum,'') SpouseTelNum, ISNULL(M2.barangaycaptain,'') barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] , ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum], ISNULL(M.EmailAddress,'') [EmailAddress], ISNULL(M2.SpousePosition,'')[SpousePosition], ISNULL(M2.FatherOccupation,'') [FatherOccupation], ISNULL(M2.FatherTelNum,'') [FatherTelNum], ISNULL(M2.Employerposition,'')[EmployerPosition], ISNULL(M2.MotherOccupation,'')[MotherOccupation], ISNULL(M.SSSGSISNum,'')[SSSGSIS], ISNULL(M.GSISNum,'')[GSISNum], ISNULL(M.SSSNum,'')[SSSNUm], ISNULL(M.PatientType,'')[PatientClass], ISNULL(M.SeniorCitizenID,'')[SeniorCitizenID], ISNULL(M3.AllergyID1,'')[Allergy1], ISNULL(M3.AllergyID2,'')[Allergy2], ISNULL(M3.AllergyID3,'')[Allergy3], ISNULL(M3.AllergyID4,'')[Allergy4], ISNULL(M3.AllergyID5,'')[Allergy5], ISNULL(M2.PatientFindUs,'')[PatientFindUs], ISNULL(M2.PatientFindUsOtherRemarks,'')[PatientFindUsOtherRemarks] FROM tbMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum WHERE M.HospNum = @HospNum end else begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, M.HouseStreet, M.Barangay, M.ZipCode, M.Sex, M.BirthDate, M.CivilStatus, M.Occupation, M.Age, M.TelNum, M.PatientStatus, M.OPDNum, M.XrayNum, M.UltraNum, M.SSSGSISNum, M.CTNum, M2.BirthPlace, M2.NationalityID, M2.ReligionID, M2.Spouse, M2.Father, M2.Mother, M2.Employer, M2.EmployerAddress, M2.EmployerTelNum, M2.ContactName, M2.ContactAddress, M2.ContactRelation, M2.ContactTelNum, M.BloodType, M2.MotherAddress, M2.MotherTelNum, M2.FatherAddress, M2.FatherTelNum, M2.SpouseAddress, M2.SpouseTelNum, M2.barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] ,ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum], ISNULL(M.EmailAddress,'') [EmailAddress], ISNULL(M2.SpousePosition,'')[SpousePosition], ISNULL(M2.FatherOccupation,'') [FatherOccupation], ISNULL(M2.FatherTelNum,'') [FatherTelNum], ISNULL(M2.Employerposition,'')[EmployerPosition], ISNULL(M2.MotherOccupation,'')[MotherOccupation] FROM tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum WHERE M.HospNum = @HospNum end GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[Sp_Adm_SearchUserUpdates] Script Date: 09/11/2014 13:06:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Sp_Adm_SearchUserUpdates] @StartDate as varchar(10), @EndDate as varchar(10), @UserID as varchar(15), @Hospnum as varchar(15), @Type as varchar(1), @IDNum as varchar(10) as if @Type = '1' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where Hospnum = @Hospnum end else if @Type = '2' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where UserID = @UserID end else if @Type = '3' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where Transdate between @StartDate and @EndDate + ' 23:59:59.99' end else if @Type = '4' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where IDNum = @IDNum end else if @Type = '5' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where Hospnum = @Hospnum and IDNum = @IDNum end else if @Type = '6' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where Hospnum = @Hospnum and UserID = UserID end else if @Type = '7' begin Select Hospnum, IDNum, Patient_data.dbo.Fn_GetCompleteName(A.Hospnum) PatientName, convert(varchar(10),A.Transdate,101) [Update Date], Patient_data.dbo.fn_GetEmployeeName(A.UserID) [UserName], A.Remarks from tbAdmPatientInfoUpdate A where IDNum = @IDNum and UserID = UserID end GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_Search_User] Script Date: 09/11/2014 15:01:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_Search_User] (@Type as varchar(1), @UserID as varchar(5), @StartDate as varchar(10),@EndDate as varchar(10) = null) AS if @Type = '1' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID where A.EmployeeID like @UserID + '%' order by A.logsequence desc else if @Type = '2' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID where A.LogSequence = @UserID order by A.logsequence desc else if @Type = '3' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID order by A.logsequence desc else if @Type = '4' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID where A.LogInDate between @StartDate and @EndDate+' 23:59:59:99' order by A.logsequence desc if @Type = '5' select A.LogSequence, A.EmployeeID, ltrim(rtrim(isnull(B.lastname,''))) + ', ' + ltrim(rtrim(isnull(B.firstname,''))) + ' ' + ltrim(rtrim(isnull(B.middlename,''))) as Name, A.LoginDate, A.LogoutDate, Remarks from tbAdm_Userlogin A left outer join password..tbpasswordmaster B on A.EmployeeID = B.EmployeeID where A.LogInDate between @StartDate and @EndDate+' 23:59:59:99' and A.EmployeeID = @UserID order by A.logsequence desc GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadPatient] Script Date: 09/16/2014 13:07:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Adm_LoadPatient] --->Dated 02/08/2012 by sensei @HospNum AS VarChar(10) AS if exists(SELECT * FROM tbMaster M WHERE M.HospNum = @HospNum) begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, ISNULL(M.HouseStreet,'') HouseStreet, ISNULL(M.Barangay,'') Barangay, ISNULL(M.ZipCode,'') ZipCode, ISNULL(M.Sex,'') Sex, M.BirthDate, M.CivilStatus, ISNULL(M.Occupation,'') Occupation, ISNULL(M.Age,'') Age, ISNULL(M.TelNum,'') TelNum, ISNULL(M.PatientStatus,'') PatientStatus, ISNULL(M.OPDNum,'') OPDNum, ISNULL(M.XrayNum,'') XrayNum, ISNULL(M.UltraNum,'') UltraNum, ISNULL(M.SSSGSISNum,'') SSSGSISNum, ISNULL(M.CTNum,'') CTNum, ISNULL(M2.BirthPlace,'') BirthPlace, ISNULL(M2.NationalityID,'') NationalityID, ISNULL(M2.ReligionID,'') ReligionID, ISNULL(M2.Spouse,'') Spouse, ISNULL(M2.Father,'') Father, ISNULL(M2.Mother,'') Mother, ISNULL(M2.Employer,'') Employer, ISNULL(M2.EmployerAddress, '') EmployerAddress, ISNULL(M2.EmployerTelNum,'') EmployerTelNum, ISNULL(M2.ContactName,'') ContactName, ISNULL(M2.ContactAddress,'') ContactAddress, ISNULL(M2.ContactRelation,'') ContactRelation, ISNULL(M2.ContactTelNum,'') ContactTelNum, ISNULL(M.BloodType,'') BloodType, ISNULL(M2.MotherAddress,'') MotherAddress, ISNULL(M2.MotherTelNum, '') MotherTelNum, ISNULL(M2.FatherAddress,'') FatherAddress, ISNULL(M2.FatherTelNum,'') FatherTelNum, ISNULL(M2.SpouseAddress,'') SpouseAddress, ISNULL(M2.SpouseTelNum,'') SpouseTelNum, ISNULL(M2.barangaycaptain,'') barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] , ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum], ISNULL(M.EmailAddress,'') [EmailAddress], ISNULL(M2.SpousePosition,'')[SpousePosition], ISNULL(M2.FatherOccupation,'') [FatherOccupation], ISNULL(M2.FatherTelNum,'') [FatherTelNum], ISNULL(M2.Employerposition,'')[EmployerPosition], ISNULL(M2.MotherOccupation,'')[MotherOccupation], ISNULL(M.SSSGSISNum,'')[SSSGSIS], ISNULL(M.GSISNum,'')[GSISNum], ISNULL(M.SSSNum,'')[SSSNUm], ISNULL(M.PatientType,'')[PatientClass], ISNULL(M.SeniorCitizenID,'')[SeniorCitizenID], ISNULL(M3.AllergyID1,'')[Allergy1], ISNULL(M3.AllergyID2,'')[Allergy2], ISNULL(M3.AllergyID3,'')[Allergy3], ISNULL(M3.AllergyID4,'')[Allergy4], ISNULL(M3.AllergyID5,'')[Allergy5], ISNULL(M2.PatientFindUs,'')[PatientFindUs], ISNULL(M2.PatientFindUsOtherRemarks,'')[PatientFindUsOtherRemarks], ISNULL(M2.FatherEmployer,'')[FatherEmployer], ISNULL(M2.MotherEmployer,'')[MotherEmployer] FROM tbMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum WHERE M.HospNum = @HospNum end else begin Select M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, M.AccountNum, M.HouseStreet, M.Barangay, M.ZipCode, M.Sex, M.BirthDate, M.CivilStatus, M.Occupation, M.Age, M.TelNum, M.PatientStatus, M.OPDNum, M.XrayNum, M.UltraNum, M.SSSGSISNum, M.CTNum, M2.BirthPlace, M2.NationalityID, M2.ReligionID, M2.Spouse, M2.Father, M2.Mother, M2.Employer, M2.EmployerAddress, M2.EmployerTelNum, M2.ContactName, M2.ContactAddress, M2.ContactRelation, M2.ContactTelNum, M.BloodType, M2.MotherAddress, M2.MotherTelNum, M2.FatherAddress, M2.FatherTelNum, M2.SpouseAddress, M2.SpouseTelNum, M2.barangaycaptain , ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName], ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] ,ISNULL(M2.OfficeNum,'') [OfficeNum], ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email], ISNULL(M.Title,'')[Title], ISNULL(M.CellNum,'')[CellNum], ISNULL(M.EmailAddress,'') [EmailAddress], ISNULL(M2.SpousePosition,'')[SpousePosition], ISNULL(M2.FatherOccupation,'') [FatherOccupation], ISNULL(M2.FatherTelNum,'') [FatherTelNum], ISNULL(M2.Employerposition,'')[EmployerPosition], ISNULL(M2.MotherOccupation,'')[MotherOccupation] FROM tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum WHERE M.HospNum = @HospNum end GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_Search_RevokedOutPatients] Script Date: 09/18/2014 18:29:32 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_AOPD_Search_RevokedOutPatients] @LastName as varchar(30), @hospnum as varchar(10), @idnum as varchar(10) as Declare @DateToDay as varchar(10); Set @DateToDay = Convert(varchar(10),getdate(),101); SELECT tbOutPatient.HospNum AS [Hospital #],tbOutPatient.IDNum AS [Admission #],RTRIM(isnull(tbMaster.LastName,'')) + ' ,' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name],convert(varchar(10),tbOutPatient.AdmDate,101) + substring(convert(varchar(19),tbOutPatient.AdmDate,100),12,8) AS [Admission Date], tbmaster.accountnum as account, tboutpatient.billingdate as billingdate,tboutpatient.ExpirationDate as ExpirationDate, tbMaster.FileNum FROM PATIENT_DATA..tbOutPatient AS tbOutPatient LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON tbOutPatient.Hospnum = tbMaster.HospNum where ((tbOutPatient.AdmDate Between @DateToDay and @DateToDay + ' 23:59:59') and (tbMaster.LastName like rtrim(@Lastname) + '%' ) and tboutpatient.HospNum like @Hospnum +'%' and tbOutPatient.IDNum like @IDNum +'%') and tbOutPatient.opdstatus = 'R' order by tbmaster.lastname, tbmaster.firstname, tbmaster.middlename GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[Revoke_ER_Admitting_Communication] Script Date: 09/19/2014 15:50:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Revoke_ER_Admitting_Communication] @idnum as varchar (20) AS SET NOCOUNT ON BEGIN update tboutpatient set billingdate = null where idnum = @idnum update tbermaster set dcrdate= null where idnum = @idnum delete from tbEr_Admitting_Communication where opdidnum = @idnum update tboutpatient set dcrdate = null where idnum = @idnum --091914 END GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateMaster] Script Date: 11/06/2014 22:33:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster] @InPatient as VarChar(1), @HospNum As VarChar(8), @LastName As VarChar(30), @FirstName As VarChar(30), @MiddleName As VarChar(30), @AccountNum As VarChar(8), @HouseStreet As VarChar(100), @Barangay As VarChar(50), @ZipCode As VarChar(4), @Sex As VarChar(1), @BirthDate As VarChar(10), @CivilStatus As VarChar(1), @Occupation As VarChar(30), @Age As VarChar(3), @TelNum As VarChar(15), @SSSGSISNum As VarChar(15), @BloodType As VarChar(3), @OPDNum As VarChar(8), @XrayNum As VarChar(8), @UltraNum As VarChar(8), @CTNum As VarChar(8), @BirthPlace As VarChar(50), @NationalityID As VarChar(2), @ReligionID As VarChar(2), @Spouse As VarChar(50), @Mother As VarChar(50), @Father As VarChar(50), @Employer As VarChar(50), @EmployerAddress As VarChar(200), @EmployerTelNum As VarChar(15), @ContactName As VarChar(30), @ContactAddress As VarChar(200), @ContactRelation As VarChar(30), @ContactTelNum As VarChar(15), @MotherAddress as VarChar(200), @MotherTelNum as VarChar(15), @FatherAddress as VarChar(200), @FatherTelNum as VarChar(15), @SpouseAddress As VarChar(50), @SpouseTelNum as VarChar(15), @AllergyID1 as VarChar(8), @AllergyID2 as VarChar(8), @AllergyID3 as VarChar(8), @AllergyID4 as VarChar(8), @AllergyID5 as VarChar(8), @BarangayCaptain As VarChar(50) AS Declare @NewHospNum VarChar(8) Declare @NewAccountNum varchar(8) if @AccountNum = 'New' or @AccountNum = 'NEW' begin set @NewAccountNum = @HospNum End Else Begin set @NewAccountNum = @AccountNum End Select HospNum From tbMaster Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMaster Set LastName = ltrim(rtrim(@LastName)), FirstName = ltrim(rtrim(@FirstName)), MiddleName = ltrim(rtrim(@MiddleName)), AccountNum = @NewAccountNum, HouseStreet = @HouseStreet, Barangay = @Barangay, ZipCode = @ZipCode, Sex = @Sex, BirthDate = Case When Len(@BirthDate) = 0 Then Null Else @BirthDate End, CivilStatus = @CivilStatus, Occupation = @Occupation, Age = @Age, TelNum = @TelNum, SSSGSISNum = @SSSGSISNum, BloodType = @BloodType, OPDNum = @OPDNum, XrayNum = @XrayNum, UltraNum = @UltraNum, CTNum = @UltraNum from tbMaster Where HospNum = @HospNum ; End ; Else Begin Insert tbMaster ( HospNum,LastName,FirstName,MiddleName,AccountNum, HouseStreet,Barangay,ZipCode,Sex,BirthDate,CivilStatus, Occupation,Age,TelNum,SSSGSISNum,BloodType,OPDNum,XrayNum, UltraNum,CTNum) Values ( @HospNum,ltrim(rtrim(@LastName)),ltrim(rtrim(@FirstName)),ltrim(rtrim(@MiddleName)),@NewAccountNum, @HouseStreet,@Barangay,@ZipCode,@Sex,@BirthDate,@CivilStatus, @Occupation,@Age,@TelNum,@SSSGSISNum,@BloodType,@OPDNum,@XrayNum, @UltraNum,@CTNum); end ; If @InPatient = 'Y' Begin Select HospNum From tbMaster2 Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMaster2 Set BirthPlace = @BirthPlace, NationalityID = @NationalityID, ReligionID = @ReligionID, Spouse = @Spouse, Mother = @Mother, Father = @Father, Employer = @Employer, EmployerAddress = @EmployerAddress, EmployerTelNum = @EmployerTelNum, ContactName = @ContactName, ContactAddress = @ContactAddress, ContactRelation = @ContactRelation, ContactTelNum = @ContactTelNum, MotherAddress = @MotherAddress, MotherTelNum = @MotherTelNum, FatherAddress = @FatherAddress, FatherTelNum = @FatherTelNum, SpouseAddress = @SpouseAddress, SpouseTelNum = @SpouseTelNum, BarangayCaptain = @BarangayCaptain Where HospNum = @HospNum ; End ; Else Begin Insert Into tbMaster2 (HospNum,BirthPlace, NationalityID,ReligionID,Spouse, Mother, Father, Employer, EmployerAddress, EmployerTelNum, ContactName, ContactAddress, ContactRelation, ContactTelNum, MotherAddress, MotherTelNum, FatherAddress, FatherTelNum, SpouseAddress, SpouseTelNum, BarangayCaptain ) Values (@HospNum, @BirthPlace, @NationalityID, @ReligionID, @Spouse, @Mother, @Father, @Employer, @EmployerAddress, @EmployerTelNum, @ContactName, @ContactAddress, @ContactRelation, @ContactTelNum, @MotherAddress, @MotherTelNum, @FatherAddress, @FatherTelNum, @SpouseAddress, @SpouseTelNum, @BarangayCaptain); End ; Select HospNum From tbMasterAllergies Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMasterAllergies Set AllergyID1 = @AllergyID1, AllergyID2 = @AllergyID2, AllergyID3 = @AllergyID3, AllergyID4 = @AllergyID4, AllergyID5 = @AllergyID5 Where HospNum = @HospNum ; End ; Else BEGIN Insert tbMasterAllergies (HospNum, AllergyID1,AllergyID2, AllergyID3,AllergyID4,AllergyID5) Values (@HospNum, @AllergyID1, @AllergyID2,@AllergyID3,@AllergyID4,@AllergyID5 ) ; END ; end ; GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdateMaster2] Script Date: 11/06/2014 22:40:48 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster2] --->Dated 02/08/2012 by sensei @HospNum As VarChar(8), @BirthPlace As VarChar(50), @NationalityID As VarChar(2), @ReligionID As VarChar(2), @Spouse As VarChar(50), @Mother As VarChar(50), @Father As VarChar(50), @Employer As VarChar(50), @EmployerAddress As VarChar(200), @EmployerTelNum As VarChar(15), @ContactName As VarChar(50), @ContactAddress As VarChar(200), @ContactRelation As VarChar(50), @ContactTelNum As VarChar(15) , @SpouseAddress varchar(200) , @SpouseTelNum varchar(15) , @FatherAddress varchar(200) , @FatherTelNum varchar(15) , @MotherAddress varchar(200) , @MotherTelNum varchar(15) , @BarangayCaptain varchar(50) , @Position varchar (50) , @EmploymentType varchar(1), @EmploymentLocation varchar(1) , @EmployerFax varchar(15) , @EmployerEmail varchar(50) , @SpouseEmpName varchar(50) , @SpouseEmpAddress varchar(200) , @SpouseEmpTelNum varchar(15) , @SpouseEmpFax varchar(15) , @SpouseEmpEmail varchar(50) , @PHIC varchar(1) , @Insurance varchar(50) , @HouseHold varchar(10) , @Email varchar(50) , @CellNum varchar(15), @OfficeNum varchar(50) = null AS Declare @NewHospNum VarChar(8) Select @NewHospNum = Hospnum From tbMaster2 Where HospNum = @HospNum; Update tbMaster set Email = @Email, CellNum = @CellNum Where HospNum = @HospNum; If Len(@NewHospNum) > 0 Begin Update tbMaster2 Set BirthPlace = @BirthPlace, NationalityID = @NationalityID, ReligionID = @ReligionID, Spouse = @Spouse, Mother = @Mother, Father = @Father, Employer = @Employer, EmployerAddress = @EmployerAddress, EmployerTelNum = @EmployerTelNum, ContactName = @ContactName, ContactAddress = @ContactAddress, ContactRelation = @ContactRelation, ContactTelNum = @ContactTelNum , SpouseAddress = @SpouseAddress , SpouseTelNum = @SpouseTelNum , FatherAddress = @FatherAddress , FatherTelNum = @FatherTelNum , MotherAddress = @MotherAddress , MotherTelNum = @MotherTelNum , BarangayCaptain = @BarangayCaptain , Position = @Position , EmploymentType = @EmploymentType , EmploymentLocation = @EmploymentLocation , EmployerFax = @EmployerFax , EmployerEmail = @EmployerEmail , SpouseEmpName = @SpouseEmpName , SpouseEmpAddress = @SpouseEmpAddress , SpouseEmpTelNum = @SpouseEmpTelNum , SpouseEmpFax = @SpouseEmpFax , SpouseEmpEmail = @SpouseEmpEmail , PhilHealth = @PHIC , Insurance = @Insurance , HouseHold = @HouseHold, OfficeNum = @OfficeNum Where HospNum = @HospNum ; End Else Begin Insert Into tbMaster2 (HospNum,BirthPlace, NationalityID,ReligionID,Spouse, Mother, Father, Employer, EmployerAddress, EmployerTelNum, ContactName, ContactAddress, ContactRelation, ContactTelNum , SpouseAddress , SpouseTelNum , FatherAddress , FatherTelNum , MotherAddress , MotherTelNum , BarangayCaptain ,Position , EmploymentType,EmploymentLocation, EmployerFax ,EmployerEmail , SpouseEmpName , SpouseEmpAddress , SpouseEmpTelNum , SpouseEmpFax , SpouseEmpEmail, PhilHealth, Insurance, HouseHold, OfficeNum ) Values (@HospNum, @BirthPlace, @NationalityID, @ReligionID, @Spouse, @Mother, @Father, @Employer, @EmployerAddress, @EmployerTelNum, @ContactName, @ContactAddress, @ContactRelation, @ContactTelNum, @SpouseAddress , @SpouseTelNum , @FatherAddress , @FatherTelNum , @MotherAddress , @MotherTelNum , @BarangayCaptain , @Position , @EmploymentType, @EmploymentLocation, @EmployerFax , @EmployerEmail , @SpouseEmpName , @SpouseEmpAddress , @SpouseEmpTelNum , @SpouseEmpFax , @SpouseEmpEmail, @PHIC, @Insurance, @HouseHold, @OfficeNum); End GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_UpdatePatient] Script Date: 11/06/2014 22:31:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_UpdatePatient] @IDNum AS VarChar(10), @HospNum AS VarChar(8), @AccountNum AS VarChar(8), @HospPlan AS VarChar(1), @MedicareType AS VarChar(3), @AdmType AS VarChar(1), @AdmDate AS VarChar(25), @ServiceID AS VarChar(3), @AttendingDr1 AS VarChar(4), @AttendingDr2 AS VarChar(4), @AdmittingDr AS VarChar(4), @ReferringDr AS VarChar(4), @AdmittingClerk AS VarChar(8), @ReferredFrom AS VarChar(2), @Age AS VarChar(3), @RoomID AS VarChar(8), @VIP AS Bit, @Confidential AS Bit, @HowAdmitted AS VarChar(1), @PatientClass AS VarChar(1), @GuarantorName AS VarChar(30), @GuarantorAddress AS VarChar(200), @GuarantorEmployer AS VarChar(30), @GuarantorTelNum AS VarChar(15), @AdmDiagnosis AS Text, @ChiefComplaint AS Text, @AdmImpression AS Text, @InformantName AS VarChar(30), @InformantAddress AS VarChar(200), @InformantRelation AS VarChar(30), @InformantTelNum AS VarChar(15), @RoomRate as Float, @WithDrOrder As Bit, @Remarks As VarChar(60), @AdditionalBed as varchar(1) AS Declare @NewAccountNum VarChar(8) Declare @OldRoom VarChar(8) ; if @HospPlan= 'P' or @HospPlan = 'p' begin Set @NewAccountNum = @HospNum end else begin Set @NewAccountNum = @AccountNum end /* Update Table Patient */ Select IDNum from tbPatient Where IDNum = @IDNum ; If @@RowCount = 0 Begin Set @OldRoom = '' ; Insert tbPatient (IDNum, HospNum, AccountNum, HospPlan, MedicareType, AdmType, AdmDate, ServiceID, AttendingDr1, AttendingDr2, AdmittingDr, ReferringDr, AdmittingClerk, ReferredFrom, Age, RoomID, RoomRate,AdditionalBed ) Select @IDNum, @HospNum, @NewAccountNum, @HospPlan, @MedicareType, @AdmType, @AdmDate, @ServiceID, @AttendingDr1, @AttendingDr2, @AdmittingDr, @ReferringDr, @AdmittingClerk, @ReferredFrom, @Age, upper(@RoomID), @RoomRate,@AdditionalBed ; End; Else Begin Select @OldRoom = isnull(RoomID,'') From tbPatient Where IDNum = @IDNum ; Update tbPatient Set AccountNum = @NewAccountNum, HospPlan = @HospPlan, MedicareType = @MedicareType, AdmType = @AdmType, AdmDate = @AdmDate, ServiceID = @ServiceID, AttendingDr1 = @AttendingDr1, AttendingDr2 = @AttendingDr2, AdmittingDr = @AdmittingDr, ReferringDr = @ReferringDr,ReferredFrom = @ReferredFrom, Age = @Age, RoomID = upper(@RoomID), RoomRate = @RoomRate, AdditionalBed = @AdditionalBed Where tbPatient.IDNum = @IDNum End ; /* Update Table Patient2 */ Select IDNum From tbPatient2 Where IDNum = @IDNum ; If @@RowCount = 0 Insert tbPatient2 (IDNum, VIP, Confidential, HowAdmitted, PatientClass, OkeyLAb, OkeyRTS, OkeyXray, OkeyUltra, OkeyCT, OkeyPT, OkeyNU, OkeyAU, OkeyHs,WithDrOrder,Remarks) Values (@IDNum, @VIP, @Confidential, @HowAdmitted, @PatientClass, 1, 1, 1, 1, 1, 1, 1, 1, 1, @WithDrOrder,@Remarks) Else Update tbPatient2 Set VIP = @VIP, Confidential = @Confidential, HowAdmitted = @HowAdmitted, PatientClass = @PatientClass, WithDrOrder = @WithDrOrder, Remarks = @Remarks Where IDNum = @IDNum /* Update RoomAvailability */ if @AdditionalBed = '0' Begin IF Len(@OldRoom) > 0 Begin Update Build_File..tbCoRoom Set BedsLeft = BedsLeft + 1 Where RoomID = @OldRoom ; End ; IF Len(@RoomID) > 0 Begin Update Build_File..tbCoRoom Set BedsLeft = BedsLeft - 1 Where RoomID = @RoomID ; End ; end; else Begin Update Build_File..tbCoRoom Set AdditionalBed = AdditionalBed + 1 Where RoomID=@RoomID; End; /* Update Guarantor */ Select IDNum From tbPatientGuarantor Where IDNum = @IDNum ; IF @@RowCount = 0 Insert tbPatientGuarantor (IDNum, GuarantorName, GuarantorAddress, GuarantorEmployer, GuarantorTelNum ) Values (@IDNum, @GuarantorName, @GuarantorAddress, @GuarantorEmployer, @GuarantorTelNum ) Else Update tbPatientGuarantor Set GuarantorName = @GuarantorName, GuarantorAddress = @GuarantorAddress, GuarantorEmployer = @GuarantorEmployer, GuarantorTelNum = @GuarantorTelNum Where IDNum = @IDNum ; /* Update History file */ Select IDNum From tbPatientHistory Where IDNum = @IDNum If @@RowCount = 0 Insert tbPatientHistory (IDNum, AdmDiagnosis, ChiefComplaint, AdmImpression ) Values (@IDNum, @AdmDiagnosis, @ChiefComplaint, @AdmImpression ) ; Else Update tbPatientHistory Set AdmDiagnosis = @AdmDiagnosis, ChiefComplaint = @ChiefComplaint, AdmImpression = @AdmImpression where IDNum = @IDNum ; /* Update Informant Table */ Select IDNum From tbPatientInformant Where IDNum = @IDNum If @@RowCount = 0 Insert tbPatientInformant (IDNum, InformantName, InformantAddress, InformantRelation, InformantTelNum ) Values (@IDNum, @InformantName, @InformantAddress, @InformantRelation, @InformantTelNum ) ; Else Update tbPatientInformant Set InformantName = @InformantName, InformantAddress = @InformantAddress, InformantRelation = @InformantRelation, InformantTelNum = @InformantTelNum Where IDNum = @IDNum ; GO ------------------------------------------------------------------------------------------------ GO Alter PROCEDURE [dbo].[spOPD_AssessmentStatus]-- 0 @Type as varchar(1), @SearchCriteria varchar(50) = '' AS if @Type = '0' --loading begin select a.assessnum [TransNum], a.[Name] [Patient Name], a.ItemID [Code], case when A.RevenueID = 'MD' then 'Dr. ' + isnull(D.LastName,'') + ', ' + isnull(D.FirstName,'') else Exam.Description end as Description, a.RefNum [Assess #], cast(a.Amount as numeric(12,2)) [Amount], case isnull(a.RecordStatus, '') when '' then 'PENDING'-- CONVERT(varchar(10), a.TransDate, 101) when '1' then a.ORNumber when 'R' then 'REVOKED' end as Status, CONVERT(varchar(10), a.TransDate, 101) Transdate, a.RevenueID, a.ItemID from BILLING..tbcashassessment a left join Billing..tbbillexamlisting Exam on A.RevenueID = Exam.RevenueID and A.Itemid = Exam.ItemID left join BUILD_FILE..tbCoDoctor d on a.ItemID = d.DoctorID where a.DepartmentID = 'OP' --and a.name like @SearchCriteria + '%' order by cast(a.TransDate as float) desc end if @type = '1' --search by name begin select a.assessnum [TransNum], a.[Name] [Patient Name], a.ItemID [Code], case when A.RevenueID = 'MD' then 'Dr. ' + isnull(D.LastName,'') + ', ' + isnull(D.FirstName,'') else Exam.Description end as Description, a.RefNum [Assess #], cast(a.Amount as numeric(12,2)) [Amount], case isnull(a.RecordStatus, '') when '' then 'PENDING'-- CONVERT(varchar(10), a.TransDate, 101) when '1' then a.ORNumber when 'R' then 'REVOKED' end as Status, CONVERT(varchar(10), a.TransDate, 101) Transdate, a.RevenueID, a.ItemID from BILLING..tbcashassessment a left join Billing..tbbillexamlisting Exam on A.RevenueID = Exam.RevenueID and A.Itemid = Exam.ItemID left join BUILD_FILE..tbCoDoctor d on a.ItemID = d.DoctorID where a.DepartmentID = 'OP' and a.name like '%'+ @SearchCriteria + '%' order by a.TransDate end if @type = '2' --search by Refnum begin select a.assessnum [TransNum], a.[Name] [Patient Name], a.ItemID [Code], case when A.RevenueID = 'MD' then 'Dr. ' + isnull(D.LastName,'') + ', ' + isnull(D.FirstName,'') else Exam.Description end as Description, a.RefNum [Assess #], cast(a.Amount as numeric(12,2)) [Amount], case isnull(a.RecordStatus, '') when '' then 'PENDING'-- CONVERT(varchar(10), a.TransDate, 101) when '1' then a.ORNumber when 'R' then 'REVOKED' end as Status, CONVERT(varchar(10), a.TransDate, 101) Transdate, a.RevenueID, a.ItemID from BILLING..tbcashassessment a left join Billing..tbbillexamlisting Exam on A.RevenueID = Exam.RevenueID and A.Itemid = Exam.ItemID left join BUILD_FILE..tbCoDoctor d on a.ItemID = d.DoctorID where a.DepartmentID = 'OP' and a.Refnum like @SearchCriteria + '%' order by a.TransDate end if @type = '3' --search by Refnum begin select a.assessnum [TransNum], a.[Name] [Patient Name], a.ItemID [Code], case when A.RevenueID = 'MD' then 'Dr. ' + isnull(D.LastName,'') + ', ' + isnull(D.FirstName,'') else Exam.Description end as Description, a.RefNum [Assess #], cast(a.Amount as numeric(12,2)) [Amount], case isnull(a.RecordStatus, '') when '' then 'PENDING'-- CONVERT(varchar(10), a.TransDate, 101) when '1' then a.ORNumber when 'R' then 'REVOKED' end as Status, CONVERT(varchar(10), a.TransDate, 101) Transdate, a.RevenueID, a.ItemID from BILLING..tbcashassessment a left join Billing..tbbillexamlisting Exam on A.RevenueID = Exam.RevenueID and A.Itemid = Exam.ItemID left join BUILD_FILE..tbCoDoctor d on a.ItemID = d.DoctorID where a.DepartmentID = 'OP' and a.Idnum like @SearchCriteria + '%' order by a.RefNum desc end GO ------------------------------------------------------------------------------------------------ USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadAddedInfo] Script Date: 12/03/2014 10:35:46 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_LoadAddedInfo] @IDNum as varchar(10) AS Select P2.PackageID, P2.AdmissionTypeID, P.Package, A.AdmissionType, P2.SeniorCitizen, M.Title, N.NoticeOfAdmID, N.NoticeOfAdm, M.Cellnum, M.EmailAddress, --M.SpouseEmployer, G.GuarantorRelation, M.GSISNum, M.SSSNum, M.HospPlan, M.SeniorCitizenID, M2.SpousePosition, M2.SpouseAddress, M2.SpouseTelNum, M2.FatherOccupation, M2.MotherOccupation, M2.EmployerPosition, G.GuarantorOccupation, I.InformantOccupation, --C.AccountNum, P1.AccountNumII as AccountNum, C.Company, P2.Transient, P1.Cardnum, M.BirthDate, P2.DTR, --isnull(P1.EMBARKATIONDATE,0)as Embarkationdate, --isnull(P1.DISEMBARKATIONDATE,0)as Disembarkationdate, --P1.Category AS CategoryDesc, --P1.VesID AS VesID, --V.VesselName AS VesselName, M2.SpouseEmpName as SpouseEmployer, P2.OkeyPH From Patient_Data..tbPatient2 P2 Left Outer Join Build_File..tbCoAdmPackage P on P2.PackageID = P.PackageID Left Outer Join Build_File..tbCoAdmissionType A on A.AdmissionTypeID = p2.AdmissionTypeID Left Outer Join Patient_Data..tbPatient P1 on P2.IdNum = P1.IDNum left Outer Join Patient_Data..tbMaster M on P1.HospNum = M.HospNum left Outer Join Build_File..tbCoNoticeOfAdm N on P2.NoticeOfAdmID = N.NoticeOfAdmID left Outer Join Patient_Data..tbPatientGuarantor G on P1.IdNum = G.IdNum left Outer Join Patient_Data..tbMaster2 M2 on P1.HospNum = M2.HospNum Left Outer Join Patient_Data..tbPatientInformant I on P1.Idnum = I.IdNum Left Outer Join Build_file..tbcoCompany C on P1.AccountNumII = C.AccountNum --LEFT OUTER JOIN Build_File..tbCoVessel V ON P1.VesID = V.VesselCode Where P2.IDNum = @IDNum; GO ------------------------------------------------------------------------------------------------------------------------ GO ALTER PROCEDURE [dbo].[sp_Med_RepLeadingCausesOfMorbidity] @StartDate As VarChar(10), @EndDate As VarChar(10) AS Declare @TotalPatients as Integer; Set @TotalPatients = (Select Count(*) FROM tbPatient Patient WHERE DcrDate between @StartDate and @EndDate + ' 23:59:59.99' and Patient.ResultID <> '4') SELECT DiagnosisID, Max(Isnull(Diagnosis, 'No Diagnosis')) as Diagnosis, Sum(Case When Age = 0 and Sex = 'M' then 1 Else 0 End) as Age0_Male, Sum(Case When Age = 0 and Sex = 'F' then 1 Else 0 End) as Age0_Female, Sum(Case When Age between 1 and 4 and Sex = 'M' then 1 Else 0 End) as Age1to4_Male, Sum(Case When Age between 1 and 4 and Sex = 'F' then 1 Else 0 End) as Age1to4_female, Sum(Case When Age between 5 and 9 and Sex = 'M' then 1 Else 0 End) as Age5to9_Male, Sum(Case When Age between 5 and 9 and Sex = 'F' then 1 Else 0 End) as Age5to9_female, Sum(Case When Age between 10 and 14 and Sex = 'M' then 1 Else 0 End) as Age10to14_Male, Sum(Case When Age between 10 and 14 and Sex = 'F' then 1 Else 0 End) as Age10to14_female, Sum(Case When Age between 15 and 19 and Sex = 'M' then 1 Else 0 End) as Age15to19_Male, Sum(Case When Age between 15 and 19 and Sex = 'F' then 1 Else 0 End) as Age15to19_female, Sum(Case When Age between 20 and 24 and Sex = 'M' then 1 Else 0 End) as Age20to24_Male, Sum(Case When Age between 20 and 24 and Sex = 'F' then 1 Else 0 End) as Age20to24_female, Sum(Case When Age between 25 and 29 and Sex = 'M' then 1 Else 0 End) as Age25to29_Male, Sum(Case When Age between 25 and 29 and Sex = 'F' then 1 Else 0 End) as Age25to29_female, Sum(Case When Age between 30 and 34 and Sex = 'M' then 1 Else 0 End) as Age30to34_Male, Sum(Case When Age between 30 and 34 and Sex = 'F' then 1 Else 0 End) as Age30to34_female, Sum(Case When Age between 35 and 39 and Sex = 'M' then 1 Else 0 End) as Age35to39_Male, Sum(Case When Age between 35 and 39 and Sex = 'F' then 1 Else 0 End) as Age35to39_female, Sum(Case When Age between 40 and 44 and Sex = 'M' then 1 Else 0 End) as Age40to44_Male, Sum(Case When Age between 40 and 44 and Sex = 'F' then 1 Else 0 End) as Age40to44_female, Sum(Case When Age between 45 and 49 and Sex = 'M' then 1 Else 0 End) as Age45to49_Male, Sum(Case When Age between 45 and 49 and Sex = 'F' then 1 Else 0 End) as Age45to49_female, Sum(Case When Age between 50 and 54 and Sex = 'M' then 1 Else 0 End) as Age50to54_Male, Sum(Case When Age between 50 and 54 and Sex = 'F' then 1 Else 0 End) as Age50to54_female, Sum(Case When Age between 55 and 59 and Sex = 'M' then 1 Else 0 End) as Age55to59_Male, Sum(Case When Age between 55 and 59 and Sex = 'F' then 1 Else 0 End) as Age55to59_female, Sum(Case When Age between 60 and 64 and Sex = 'M' then 1 Else 0 End) as Age60to64_Male, Sum(Case When Age between 60 and 64 and Sex = 'F' then 1 Else 0 End) as Age60to64_female, Sum(Case When Age between 65 and 69 and Sex = 'M' then 1 Else 0 End) as Age65to69_Male, Sum(Case When Age between 65 and 69 and Sex = 'F' then 1 Else 0 End) as Age65to69_female, --Sum(Case When Age between 20 and 44 and Sex = 'M' then 1 Else 0 End) as Age20to44_Male, --Sum(Case When Age between 20 and 44 and Sex = 'F' then 1 Else 0 End) as Age20to44_female, --Sum(Case When Age between 45 and 64 and Sex = 'M' then 1 Else 0 End) as Age45to64_Male, --Sum(Case When Age between 45 and 64 and Sex = 'F' then 1 Else 0 End) as Age45to64_female, Sum(Case When Age > 70 and Sex = 'M' then 1 Else 0 End) as Age70Above_Male, Sum(Case When Age > 70 and Sex = 'F' then 1 Else 0 End) as Age70Above_female, Sum(Case When Sex = 'M' then 1 Else 0 End) as Total_Male, Sum(Case When Sex = 'F' then 1 Else 0 End) as Total_female, Count(*) as Total, @TotalPatients as TotalPx FROM (SELECT --Case When not IsNumeric(IsNull(Patient.Age,0)) = 1 then 0 Case When dbo.fn_ComputeAgeInYear(Master.BirthDate,patient.AdmDate) = 1 then 0 Else Cast( isNull(Patient.Age,0) as Decimal(3,0) ) End as Age, isnull(Master.Sex, 'M') as Sex, --SubString(Diag.DiagnosisID,1,3) As DiagnosisID, Diag.DiagnosisID As DiagnosisID, --Diag3Code.Description as Diagnosis D.Description as Diagnosis FROM tbPatient Patient INNER JOIN tbMaster Master ON Patient.HospNum = Master.HospNum INNER JOIN tbPatientDiagnosis Diag ON Patient.IDNum = Diag.IDNum and Diag.PrimaryDiagnosis = 'Y' Left Outer Join tbPatientProcedure Proced ON Patient.IDNum = Proced.IDNum and Proced.PrimaryProcedure = 'Y' inner Join Build_File..tbICDDiagMain D on Diag.DiagnosisID = D.Code --Left Outer Join Build_File..tbICDDiag3Code Diag3Code --ON SubString(Diag.DiagnosisID,1,3) = Diag3Code.Code WHERE DcrDate between @StartDate and @EndDate + ' 23:59:59.99' and Patient.ResultID <> '4' ) Patient Group by DiagnosisID GO ------------------------------------------------------------------------------------------------------------------------ GO ALTER PROCEDURE [dbo].[sp_Med_RepLeadingCausesOfMortality] @StartDate As VarChar(10), @EndDate As VarChar(10) AS Declare @TotalPatients as Integer; Set @TotalPatients = (Select Count(*) FROM tbPatient Patient WHERE DcrDate between @StartDate and @EndDate + ' 23:59:59.99' and Patient.ResultID = '4') SELECT DiagnosisID, Max(Isnull(Diagnosis, 'No Diagnosis')) as Diagnosis, Sum(Case When Age = 0 and Sex = 'M' then 1 Else 0 End) as Age0_Male, Sum(Case When Age = 0 and Sex = 'F' then 1 Else 0 End) as Age0_Female, Sum(Case When Age between 1 and 4 and Sex = 'M' then 1 Else 0 End) as Age1to4_Male, Sum(Case When Age between 1 and 4 and Sex = 'F' then 1 Else 0 End) as Age1to4_female, Sum(Case When Age between 5 and 9 and Sex = 'M' then 1 Else 0 End) as Age5to9_Male, Sum(Case When Age between 5 and 9 and Sex = 'F' then 1 Else 0 End) as Age5to9_female, Sum(Case When Age between 10 and 14 and Sex = 'M' then 1 Else 0 End) as Age10to14_Male, Sum(Case When Age between 10 and 14 and Sex = 'F' then 1 Else 0 End) as Age10to14_female, Sum(Case When Age between 15 and 19 and Sex = 'M' then 1 Else 0 End) as Age15to19_Male, Sum(Case When Age between 15 and 19 and Sex = 'F' then 1 Else 0 End) as Age15to19_female, Sum(Case When Age between 20 and 24 and Sex = 'M' then 1 Else 0 End) as Age20to24_Male, Sum(Case When Age between 20 and 24 and Sex = 'F' then 1 Else 0 End) as Age20to24_female, Sum(Case When Age between 25 and 29 and Sex = 'M' then 1 Else 0 End) as Age25to29_Male, Sum(Case When Age between 25 and 29 and Sex = 'F' then 1 Else 0 End) as Age25to29_female, Sum(Case When Age between 30 and 34 and Sex = 'M' then 1 Else 0 End) as Age30to34_Male, Sum(Case When Age between 30 and 34 and Sex = 'F' then 1 Else 0 End) as Age30to34_female, Sum(Case When Age between 35 and 39 and Sex = 'M' then 1 Else 0 End) as Age35to39_Male, Sum(Case When Age between 35 and 39 and Sex = 'F' then 1 Else 0 End) as Age35to39_female, Sum(Case When Age between 40 and 44 and Sex = 'M' then 1 Else 0 End) as Age40to44_Male, Sum(Case When Age between 40 and 44 and Sex = 'F' then 1 Else 0 End) as Age40to44_female, Sum(Case When Age between 45 and 49 and Sex = 'M' then 1 Else 0 End) as Age45to49_Male, Sum(Case When Age between 45 and 49 and Sex = 'F' then 1 Else 0 End) as Age45to49_female, Sum(Case When Age between 50 and 54 and Sex = 'M' then 1 Else 0 End) as Age50to54_Male, Sum(Case When Age between 50 and 54 and Sex = 'F' then 1 Else 0 End) as Age50to54_female, Sum(Case When Age between 55 and 59 and Sex = 'M' then 1 Else 0 End) as Age55to59_Male, Sum(Case When Age between 55 and 59 and Sex = 'F' then 1 Else 0 End) as Age55to59_female, Sum(Case When Age between 60 and 64 and Sex = 'M' then 1 Else 0 End) as Age60to64_Male, Sum(Case When Age between 60 and 64 and Sex = 'F' then 1 Else 0 End) as Age60to64_female, Sum(Case When Age between 65 and 69 and Sex = 'M' then 1 Else 0 End) as Age65to69_Male, Sum(Case When Age between 65 and 69 and Sex = 'F' then 1 Else 0 End) as Age65to69_female, --Sum(Case When Age between 20 and 44 and Sex = 'M' then 1 Else 0 End) as Age20to44_Male, --Sum(Case When Age between 20 and 44 and Sex = 'F' then 1 Else 0 End) as Age20to44_female, --Sum(Case When Age between 45 and 64 and Sex = 'M' then 1 Else 0 End) as Age45to64_Male, --Sum(Case When Age between 45 and 64 and Sex = 'F' then 1 Else 0 End) as Age45to64_female, Sum(Case When Age > 64 and Sex = 'M' then 1 Else 0 End) as Age70Above_Male, Sum(Case When Age > 64 and Sex = 'F' then 1 Else 0 End) as Age70Above_female, Sum(Case When Sex = 'M' then 1 Else 0 End) as Total_Male, Sum(Case When Sex = 'F' then 1 Else 0 End) as Total_female, Count(*) as Total, @TotalPatients as TotalPx FROM (SELECT --Case When not IsNumeric(IsNull(Patient.Age,0)) = 1 then 0 Case When dbo.fn_ComputeAgeInYear(Master.BirthDate,patient.AdmDate) = 1 then 0 Else Cast( isNull(Patient.Age,0) as Decimal(3,0) ) End as Age, isnull(Master.Sex, 'M') as Sex, --SubString(Diag.DiagnosisID,1,3) As DiagnosisID, Diag.DiagnosisID As DiagnosisID, --Diag3Code.Description as Diagnosis D.Description as Diagnosis FROM tbPatient Patient INNER JOIN tbMaster Master ON Patient.HospNum = Master.HospNum INNER JOIN tbPatientDiagnosis Diag ON Patient.IDNum = Diag.IDNum and Diag.PrimaryDiagnosis = 'Y' Left Outer Join tbPatientProcedure Proced ON Patient.IDNum = Proced.IDNum and Proced.PrimaryProcedure = 'Y' Inner Join Build_File..tbICDDiagMain D on Diag.DiagnosisID = D.Code --Left Outer Join Build_File..tbICDDiag3Code Diag3Code -- ON SubString(Diag.DiagnosisID,1,3) = Diag3Code.Code WHERE DcrDate between @StartDate and @EndDate + ' 23:59:59.99' and Patient.ResultID = '4' ) Patient Group by DiagnosisID GO ----------------------------------------------------------------------------------------------------------------- GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster] @InPatient as VarChar(1), @HospNum As VarChar(8), @LastName As VarChar(30), @FirstName As VarChar(50), @MiddleName As VarChar(30), @AccountNum As VarChar(8), @HouseStreet As VarChar(100), @Barangay As VarChar(50), @ZipCode As VarChar(4), @Sex As VarChar(1), @BirthDate As VarChar(10), @CivilStatus As VarChar(1), @Occupation As VarChar(30), @Age As VarChar(3), @TelNum As VarChar(15), @SSSGSISNum As VarChar(15), @BloodType As VarChar(3), @OPDNum As VarChar(8), @XrayNum As VarChar(8), @UltraNum As VarChar(8), @CTNum As VarChar(8), @BirthPlace As VarChar(50), @NationalityID As VarChar(2), @ReligionID As VarChar(2), @Spouse As VarChar(50), @Mother As VarChar(50), @Father As VarChar(50), @Employer As VarChar(50), @EmployerAddress As VarChar(200), @EmployerTelNum As VarChar(15), @ContactName As VarChar(30), @ContactAddress As VarChar(200), @ContactRelation As VarChar(30), @ContactTelNum As VarChar(15), @MotherAddress as VarChar(200), @MotherTelNum as VarChar(15), @FatherAddress as VarChar(200), @FatherTelNum as VarChar(15), @SpouseAddress As VarChar(50), @SpouseTelNum as VarChar(15), @AllergyID1 as VarChar(8), @AllergyID2 as VarChar(8), @AllergyID3 as VarChar(8), @AllergyID4 as VarChar(8), @AllergyID5 as VarChar(8), @BarangayCaptain As VarChar(50) AS Declare @NewHospNum VarChar(8) Declare @NewAccountNum varchar(8) if @AccountNum = 'New' or @AccountNum = 'NEW' begin set @NewAccountNum = @HospNum End Else Begin set @NewAccountNum = @AccountNum End Select HospNum From tbMaster Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMaster Set LastName = ltrim(rtrim(@LastName)), FirstName = ltrim(rtrim(@FirstName)), MiddleName = ltrim(rtrim(@MiddleName)), AccountNum = @NewAccountNum, HouseStreet = @HouseStreet, Barangay = @Barangay, ZipCode = @ZipCode, Sex = @Sex, BirthDate = Case When Len(@BirthDate) = 0 Then Null Else @BirthDate End, CivilStatus = @CivilStatus, Occupation = @Occupation, Age = @Age, TelNum = @TelNum, SSSGSISNum = @SSSGSISNum, BloodType = @BloodType, OPDNum = @OPDNum, XrayNum = @XrayNum, UltraNum = @UltraNum, CTNum = @UltraNum from tbMaster Where HospNum = @HospNum ; End ; Else Begin Insert tbMaster ( HospNum,LastName,FirstName,MiddleName,AccountNum, HouseStreet,Barangay,ZipCode,Sex,BirthDate,CivilStatus, Occupation,Age,TelNum,SSSGSISNum,BloodType,OPDNum,XrayNum, UltraNum,CTNum) Values ( @HospNum,ltrim(rtrim(@LastName)),ltrim(rtrim(@FirstName)),ltrim(rtrim(@MiddleName)),@NewAccountNum, @HouseStreet,@Barangay,@ZipCode,@Sex,@BirthDate,@CivilStatus, @Occupation,@Age,@TelNum,@SSSGSISNum,@BloodType,@OPDNum,@XrayNum, @UltraNum,@CTNum); end ; If @InPatient = 'Y' Begin Select HospNum From tbMaster2 Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMaster2 Set BirthPlace = @BirthPlace, NationalityID = @NationalityID, ReligionID = @ReligionID, Spouse = @Spouse, Mother = @Mother, Father = @Father, Employer = @Employer, EmployerAddress = @EmployerAddress, EmployerTelNum = @EmployerTelNum, ContactName = @ContactName, ContactAddress = @ContactAddress, ContactRelation = @ContactRelation, ContactTelNum = @ContactTelNum, MotherAddress = @MotherAddress, MotherTelNum = @MotherTelNum, FatherAddress = @FatherAddress, FatherTelNum = @FatherTelNum, SpouseAddress = @SpouseAddress, SpouseTelNum = @SpouseTelNum, BarangayCaptain = @BarangayCaptain Where HospNum = @HospNum ; End ; Else Begin Insert Into tbMaster2 (HospNum,BirthPlace, NationalityID,ReligionID,Spouse, Mother, Father, Employer, EmployerAddress, EmployerTelNum, ContactName, ContactAddress, ContactRelation, ContactTelNum, MotherAddress, MotherTelNum, FatherAddress, FatherTelNum, SpouseAddress, SpouseTelNum, BarangayCaptain ) Values (@HospNum, @BirthPlace, @NationalityID, @ReligionID, @Spouse, @Mother, @Father, @Employer, @EmployerAddress, @EmployerTelNum, @ContactName, @ContactAddress, @ContactRelation, @ContactTelNum, @MotherAddress, @MotherTelNum, @FatherAddress, @FatherTelNum, @SpouseAddress, @SpouseTelNum, @BarangayCaptain); End ; Select HospNum From tbMasterAllergies Where HospNum = @HospNum ; If @@RowCount > 0 Begin Update tbMasterAllergies Set AllergyID1 = @AllergyID1, AllergyID2 = @AllergyID2, AllergyID3 = @AllergyID3, AllergyID4 = @AllergyID4, AllergyID5 = @AllergyID5 Where HospNum = @HospNum ; End ; Else BEGIN Insert tbMasterAllergies (HospNum, AllergyID1,AllergyID2, AllergyID3,AllergyID4,AllergyID5) Values (@HospNum, @AllergyID1, @AllergyID2,@AllergyID3,@AllergyID4,@AllergyID5 ) ; END ; end ; GO GO USE [REGISTER] GO /****** Object: StoredProcedure [dbo].[spReg_SearchMedicalHistory] Script Date: 03/05/2015 18:17:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[spReg_SearchMedicalHistory] @Hospnum AS varChar(8), @IDNum as varchar(15)= null AS --Declare -- @Hospnum AS varChar(8), -- @IDNum as varchar(15)= null --Set @Hospnum ='340762' --Set @IDNum ='1785555B' If (Select COUNT(*) from REGISTER..tbMedicalHistory where IDNum =@IDNum) = 1 SELECT A.*,B.AdmDate FROM REGISTER..tbMedicalHistory A Left Outer Join PATIENT_DATA..TBOUTPATIENT B on A.IDNum = B.IDNum WHERE A.hospnum = @Hospnum and A.IDNum = @IDNum Order by B.AdmDate desc Else --Select 'Previous' SELECT Top 1 A.*,B.AdmDate FROM REGISTER..tbMedicalHistory A Left Outer Join PATIENT_DATA..TBOUTPATIENT B on A.IDNum = B.IDNum WHERE A.hospnum = @Hospnum --and A.IDNum = @IDNum Order by B.AdmDate desc GO GO USE [REGISTER] GO /****** Object: StoredProcedure [dbo].[spReg_SaveMedicalHistory] Script Date: 03/05/2015 18:16:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[spReg_SaveMedicalHistory] @Hospnum AS varchar(8), @Lastname AS varchar(30), @Firstname AS varchar(30), @Middlename AS varchar(30), @Nose AS varchar(1), @Ear AS varchar(1), @Asthma AS varchar(1), @Tuberculosis AS varchar(1), @Lung AS varchar(1), @HighBlood AS varchar(1), @Heart AS varchar(1), @Rheumatic AS varchar(1), @Diabetes AS varchar(1), @Endocrine AS varchar(1), @Cancer AS varchar(1), @Mental AS varchar(1), @Head AS varchar(1), @Hernia AS varchar(1), @Rheumatism AS varchar(1), @Typhoid AS varchar(1), @Trachoma AS varchar(1), @Stomach AS varchar(1), @Abdominal AS varchar(1), @Kidney AS varchar(1), @STD AS varchar(1), @Genetic AS varchar(1), @Malaria AS varchar(1), @Operations AS varchar(1), @Tropical AS varchar(1), @Chronic AS varchar(1), @Fainting AS varchar(1), @Headaches AS varchar(1), @Dizziness AS varchar(1), --@WhatOperation AS varchar(30), @Eye AS varchar(1), @Veins AS varchar(1), @Blood AS varchar(1), @Thyroid AS varchar(1), @Skin AS varchar(1), @Allergies AS varchar(1), @Infectious AS varchar(1), @Genital AS varchar(1), @Pregnancy AS varchar(1), @Sleep AS varchar(1), --@Epilepsy AS varchar(1), @Depression AS varchar(1), @Memory AS varchar(1), @Balance AS varchar(1), @mobility AS varchar(1), @Amputation AS varchar(1), @Repatriated AS varchar(1), @Hospitalized AS varchar(1), @Declared AS varchar(1), @Revoked AS varchar(1), @Aware AS varchar(1), @Healthy AS varchar(1), @Allergic AS varchar(1), @Prescription AS varchar(1), @Gynaecological as varchar(1), @LastMenstrualPeriod as datetime, @LastTropicalDisease as datetime, @Schistosomiasis as varchar(1), @LastSchistosomiasis as datetime, @LastAllergy as varchar(50), @AllegicMedication as varchar(250), @GeneralAnswer AS varchar(50), @Comments AS varchar(500), @IDnum as varchar(15)= null AS SELECT * FROM REGISTER..tbMedicalHistory WHERE Hospnum = @Hospnum and IDNum = @IDnum ; IF @@RowCount > 0 BEGIN UPDATE REGISTER..tbMedicalHistory SET Nose = @Nose, Ear = @Ear, Asthma = @Asthma, Tuberculosis = @Tuberculosis, Lung = @Lung, HighBlood = @HighBlood, Heart = @Heart, Rheumatic = @Rheumatic, Diabetes = @Diabetes, Endocrine = @Endocrine, Cancer = @Cancer, Mental = @Mental, Head = @Head, Hernia = @Hernia, Rheumatism = @Rheumatism, Typhoid = @Typhoid, Trachoma = @Trachoma, Stomach = @Stomach, Abdominal = @Abdominal, Kidney = @Kidney, STD = @STD, Genetic = @Genetic, Malaria = @Malaria, Operations = @Operations, Tropical = @Tropical, Chronic = @Chronic, Fainting = @Fainting, Headaches = @Headaches, Dizziness = @Dizziness, -- WhatOperation = @WhatOperation, Eye = @Eye, Veins = @Veins, Blood = @Blood, Thyroid = @Thyroid , Skin = @Skin, Allergies = @Allergies , Infectious = @Infectious, Genital =@Genital , Pregnancy =@Pregnancy , Sleep = @Sleep, --Epilepsy= @Epilepsy, Depression =@Depression , Memory = @Memory, Balance = @Balance, mobility = @mobility, Amputation = @Amputation, Repatriated = @Repatriated, Hospitalized = @Hospitalized, Declared = @Declared, Revoked = @Revoked, Aware = @Aware, Healthy = @Healthy, Allergic = @Allergic, Prescription = @Prescription, Gynaecological = @Gynaecological, LastMenstrualPeriod = @LastMenstrualPeriod, LastTropicalDisease = @LastTropicalDisease, Schistosomiasis = @Schistosomiasis, LastSchistosomiasis = @LastSchistosomiasis, LastAllergy = @LastAllergy, AllegicMedication = @AllegicMedication, GeneralAnswer = @GeneralAnswer, Comments = @Comments WHERE Hospnum = @Hospnum And IDNum = @IDnum; END ELSE BEGIN INSERT INTO REGISTER..tbMedicalHistory ( Hospnum, Lastname, Firstname, Middlename, Nose, Ear, Asthma, Tuberculosis, Lung, HighBlood, Heart, Rheumatic, Diabetes, Endocrine, Cancer, Mental, Head, Hernia, Rheumatism, Typhoid, Trachoma, Stomach, Abdominal, Kidney, STD, Genetic, Malaria, Operations, Tropical, Chronic, Fainting, Headaches, Dizziness,--WhatOperation, Eye, Veins , Blood, Thyroid , Skin , Allergies , Infectious, Genital , Pregnancy , Sleep , --Epilepsy, Depression , Memory , Balance , mobility, Amputation, Repatriated, Hospitalized, Declared, Revoked, Aware, Healthy, Allergic, Prescription, Gynaecological, LastMenstrualPeriod, LastTropicalDisease, Schistosomiasis, LastSchistosomiasis, LastAllergy, AllegicMedication, GeneralAnswer, Comments,IDNum) VALUES ( @Hospnum, @Lastname, @Firstname, @Middlename, @Nose, @Ear, @Asthma, @Tuberculosis, @Lung, @HighBlood, @Heart, @Rheumatic, @Diabetes, @Endocrine, @Cancer, @Mental, @Head, @Hernia, @Rheumatism, @Typhoid, @Trachoma, @Stomach, @Abdominal, @Kidney, @STD, @Genetic, @Malaria, @Operations, @Tropical, @Chronic, @Fainting, @Headaches, @Dizziness,--@WhatOperation , @Eye, @Veins , @Blood, @Thyroid , @Skin , @Allergies , @Infectious, @Genital , @Pregnancy , @Sleep , --@Epilepsy, @Depression , @Memory , @Balance , @mobility, @Amputation, @Repatriated, @Hospitalized, @Declared, @Revoked, @Aware, @Healthy, @Allergic, @Prescription, @Gynaecological, @LastMenstrualPeriod, @LastTropicalDisease, @Schistosomiasis, @LastSchistosomiasis, @LastAllergy, @AllegicMedication, @GeneralAnswer, @Comments,@IDnum) ; END GO GO USE [Station] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Nurse_Save_Patient_AdministeredMedicines_xp] @idnum as varchar(10), @itemid as varchar(10), @quantity as int, @requestnum as varchar(10), @administereddate as datetime, @administeredby as varchar(10), @UserID as varchar(10), @RefNum As varchar(10)=NULL AS BEGIN Insert into STATION..tbNurse_AdministeredMedicines(IDNum, Transdate, ItemID, Quantity, RequestNum ,AdministeredDate, AdministeredBy, UserID, ReferenceNum) Values(@idnum,getdate(),@itemid,@quantity,@requestnum,@administereddate,@administeredby,@UserID,@RefNum); END GO USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_Adm_LoadReservation] Script Date: 09/15/2014 15:04:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Adm_LoadReservation] @ReservationNum as varchar(10) AS select --* A.LastName, A.FirstName, A.MiddleName, A.RoomID, A.Accomodation, A.Remarks, B.AdmissionTypeID, B.AdmissionType, C.ServiceID, C.Service, A.DateReserved, A.EnddateReserved from tbAdmWaitingList A Left Outer Join BUILD_FILE..tbCoAdmissionType B on A.AdmissionType = B.AdmissionTypeID Left Outer JOin BUILD_FILE..tbCoService C on A.ServiceID = C.ServiceID where ReservationNum = @ReservationNum GO USE [Station] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Nurse_Save_Patient_AdministeredMedicines_xp] @idnum as varchar(10), @itemid as varchar(10), @quantity as float, @requestnum as varchar(10), @administereddate as datetime, @administeredby as varchar(10), @UserID as varchar(10), @RefNum As varchar(10)=NULL AS BEGIN Insert into STATION..tbNurse_AdministeredMedicines(IDNum, Transdate, ItemID, Quantity, RequestNum ,AdministeredDate, AdministeredBy, UserID, ReferenceNum) Values(@idnum,getdate(),@itemid,@quantity,@requestnum,@administereddate,@administeredby,@UserID,@RefNum); END GO ============================================================================= USE [Patient_Data] GO /****** Object: StoredProcedure [dbo].[sp_Get_ER_PatientList_New] Script Date: 07/09/2015 16:01:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Get_ER_PatientList_New] @Status As Varchar (1), @Lastname As Varchar(20), @HospNum As Varchar(10), @IDNum As Varchar(10), @isTableApp as varchar(1) = 'N' AS Declare @HideNonERPatients as bit Set @HideNonERPatients = (SELECT ISNULL(HideNonERPatients,0) FROM STATION..tbNursing_settings); if @isTableApp = 'Y' BEGIN SET @HideNonERPatients = 1 END IF @Status='O' IF Len(Ltrim(Rtrim(@HospNum))) > 0 SELECT '', ER.HospNum AS [Hospital #], ER.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),B.AdmDate,101) + substring(convert(varchar(19),B.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),B.DcrDate,101) + substring(convert(varchar(19),B.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),B.BillingDate,101) + substring(convert(varchar(19),B.BillingDate,100),12,8) AS [Billing Date], Case IsNull(ER.PatientClassification,'') When 'W' then 1 When '' then 1 Else 0 End As IsCash, 'All Departments,' As Remarks FROM PATIENT_DATA..tbERMaster AS ER LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON ER.Hospnum = tbMaster.HospNum LEFT OUTER JOIN PATIENT_DATA..tbOutPatient AS B ON ER.IDnum = B.IDNum and ER.Hospnum = B.Hospnum WHERE ER.HospNum = @HospNum and (B.DcrDate is null or B.BillingDate is null) and Isnull(ER.OpdStatus,'') in ('','E','D','A','M') and B.OPDStatus <> 'R' and isnull(B.ERNum,'') <> '' UNION ALL SELECT '', tbOutPatient.HospNum AS [Hospital #], tbOutPatient.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),tbOutPatient.AdmDate,101) + substring(convert(varchar(19),tbOutPatient.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),tbOutPatient.DcrDate,101) + substring(convert(varchar(19),tbOutPatient.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),tbOutPatient.BillingDate,101) + substring(convert(varchar(19),tbOutPatient.BillingDate,100),12,8) AS [Billing Date], Case IsNull(tbOutPatient.PatientType,'') When 'W' then 1 When '' then 1 Else 0 End As IsCash, 'All Departments,' As Remarks FROM PATIENT_DATA..tbOutPatient AS tbOutPatient LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON tbOutPatient.Hospnum = tbMaster.HospNum WHERE tbOutPatient.HospNum = @HospNum and (tbOutPatient.DcrDate is null or tbOutPatient.BillingDate is null) and Isnull(tbOutPatient.OpdStatus,'') <> 'R' and Isnull(tbOutPatient.ERNum,'') = '' AND @HideNonERPatients <> 1 ORDER BY [Patient Name]; ELSE IF Len(Ltrim(Rtrim(@IDNum))) > 0 SELECT '', ER.HospNum AS [Hospital #], ER.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),B.AdmDate,101) + substring(convert(varchar(19),B.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),B.DcrDate,101) + substring(convert(varchar(19),B.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),B.BillingDate,101) + substring(convert(varchar(19),B.BillingDate,100),12,8) AS [Billing Date], Case IsNull(ER.PatientClassification,'') When 'W' then 1 When '' then 1 Else 0 End As IsCash, 'All Departments,' As Remarks FROM PATIENT_DATA..tbERMaster AS ER LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON ER.Hospnum = tbMaster.HospNum LEFT OUTER JOIN PATIENT_DATA..tbOutPatient AS B ON ER.IDnum = B.IDNum and ER.Hospnum = B.Hospnum WHERE ER.IDNum = @IDNum and (B.DcrDate is null or B.BillingDate is null) and Isnull(ER.OpdStatus,'') in ('','E','D','A','M') and B.OPDStatus <> 'R' and isnull(B.ERNum,'') <> '' UNION ALL SELECT '', tbOutPatient.HospNum AS [Hospital #], tbOutPatient.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),tbOutPatient.AdmDate,101) + substring(convert(varchar(19),tbOutPatient.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),tbOutPatient.DcrDate,101) + substring(convert(varchar(19),tbOutPatient.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),tbOutPatient.BillingDate,101) + substring(convert(varchar(19),tbOutPatient.BillingDate,100),12,8) AS [Billing Date], Case IsNull(tbOutPatient.PatientType,'') When 'W' then 1 When '' then 1 Else 0 End As IsCash, 'All Departments,' As Remarks FROM PATIENT_DATA..tbOutPatient AS tbOutPatient LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON tbOutPatient.Hospnum = tbMaster.HospNum WHERE tbOutPatient.IDNum = @IDNum and (tbOutPatient.DcrDate is null or tbOutPatient.BillingDate is null) and Isnull(tbOutPatient.OpdStatus,'') <> 'R' and Isnull(tbOutPatient.ERNum,'') = '' AND @HideNonERPatients <> 1 ORDER BY [Patient Name]; ELSE SELECT '', ER.HospNum AS [Hospital #], ER.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),B.AdmDate,101) + substring(convert(varchar(19),B.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),B.DcrDate,101) + substring(convert(varchar(19),B.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),B.BillingDate,101) + substring(convert(varchar(19),B.BillingDate,100),12,8) AS [Billing Date], Case IsNull(ER.PatientClassification,'') When 'W' then 1 When '' then 1 Else 0 End As IsCash, 'All Departments,' As Remarks FROM PATIENT_DATA..tbERMaster AS ER LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON ER.Hospnum = tbMaster.HospNum LEFT OUTER JOIN PATIENT_DATA..tbOutPatient AS B ON ER.IDnum = B.IDNum and ER.Hospnum = B.Hospnum WHERE isnull(B.OPDStatus,'') <> 'R' and isnull(B.ERNum,'') <> '' and tbMaster.LastName like @Lastname + '%' and (B.DcrDate is null or B.BillingDate is null) and Isnull(ER.OpdStatus,'') in ('','E','D','A','M') UNION ALL SELECT '', tbOutPatient.HospNum AS [Hospital #], tbOutPatient.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),tbOutPatient.AdmDate,101) + substring(convert(varchar(19),tbOutPatient.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),tbOutPatient.DcrDate,101) + substring(convert(varchar(19),tbOutPatient.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),tbOutPatient.BillingDate,101) + substring(convert(varchar(19),tbOutPatient.BillingDate,100),12,8) AS [Billing Date], Case IsNull(tbOutPatient.PatientType,'') When 'W' then 1 When '' then 1 Else 0 End As IsCash, 'All Departments,' As Remarks FROM PATIENT_DATA..tbOutPatient AS tbOutPatient LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON tbOutPatient.Hospnum = tbMaster.HospNum WHERE (tbMaster.LastName like Ltrim(Rtrim(@Lastname)) + '%' ) and (tbOutPatient.DcrDate is null or tbOutPatient.BillingDate is null) and Isnull(tbOutPatient.OpdStatus,'') <> 'R' and Isnull(tbOutPatient.ERNum,'') = '' AND @HideNonERPatients <> 1 ORDER BY [Patient Name]; ELSE IF Len(Ltrim(Rtrim(@HospNum))) > 0 SELECT '', tbPatient.HospNum AS [Hospital #], tbPatient.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),tbPatient.AdmDate,101) + substring(convert(varchar(19),tbPatient.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),tbPatient.Dcrdate,101) + substring(convert(varchar(19),tbPatient.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),tbPatient.Billingdate,101) + substring(convert(varchar(19),tbPatient.BillingDate,100),12,8) AS [Billing Date], Case When (P2.OkeyLab=0 or P2.OkeyXray=0 or P2.OkeyUltra =0 or P2.OkeyPH=0 or P2.OkeyCS=0 or P2.OkeyCT=0 or P2.OkeyMRI=0 or P2.OkeyHS=0 or P2.OkeyAU=0 or P2.OkeyNU=0 or P2.OkeyRTS=0 or P2.OkeyPT=0 ) then 1 Else 0 End As IsCash, ( (Case When P2.OkeyLab=0 then 'Laboratory, ' Else '' End) + (Case When P2.OkeyXray=0 then 'Xray, ' Else '' End) + (Case When P2.OkeyUltra=0 then 'Ultrasound, ' Else '' End) + (Case When P2.OkeyPH=0 then 'Pharmacy, ' Else '' End) + (Case When P2.OkeyCS=0 then 'Central Supply, ' Else '' End) + (Case When P2.OkeyCT=0 then 'CT-Scan, ' Else '' End) + (Case When P2.OkeyMRI=0 then 'MRI, ' Else '' End) + (Case When P2.OkeyHS=0 then 'Heart Station, ' Else '' End) + (Case When P2.OkeyAU=0 then 'Audiometry, ' Else '' End) + (Case When P2.OkeyNU=0 then 'Nuclear, ' Else '' End) + (Case When P2.OkeyRTS=0 then 'Pulmonary, ' Else '' End) + (Case When P2.OkeyPT=0 then 'PT,' Else '' End) ) AS Remarks FROM PATIENT_DATA..tbPatient AS tbPatient LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON tbPatient.Hospnum = tbMaster.HospNum LEFT OUTER JOIN BUILD_FILE..tbcoRoom AS tbCoRoom ON tbPatient.RoomID = tbcoRoom.RoomiD LEFT OUTER JOIN PATIENT_DATA..tbPatient2 P2 ON tbPatient.IDNum = P2.IDNum WHERE tbPatient.HospNum = @HospNum and (tbPatient.DcrDate is null or tbPatient.BillingDate is null) ORDER BY [Patient Name]; ELSE IF Len(Ltrim(Rtrim(@IDNum))) > 0 SELECT '', tbPatient.HospNum AS [Hospital #], tbPatient.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),tbPatient.AdmDate,101) + substring(convert(varchar(19),tbPatient.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),tbPatient.Dcrdate,101) + substring(convert(varchar(19),tbPatient.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),tbPatient.Billingdate,101) + substring(convert(varchar(19),tbPatient.BillingDate,100),12,8) AS [Billing Date], Case When (P2.OkeyLab=0 or P2.OkeyXray=0 or P2.OkeyUltra =0 or P2.OkeyPH=0 or P2.OkeyCS=0 or P2.OkeyCT=0 or P2.OkeyMRI=0 or P2.OkeyHS=0 or P2.OkeyAU=0 or P2.OkeyNU=0 or P2.OkeyRTS=0 or P2.OkeyPT=0 ) then 1 Else 0 End As IsCash, ( (Case When P2.OkeyLab=0 then 'Laboratory, ' Else '' End) + (Case When P2.OkeyXray=0 then 'Xray, ' Else '' End) + (Case When P2.OkeyUltra=0 then 'Ultrasound, ' Else '' End) + (Case When P2.OkeyPH=0 then 'Pharmacy, ' Else '' End) + (Case When P2.OkeyCS=0 then 'Central Supply, ' Else '' End) + (Case When P2.OkeyCT=0 then 'CT-Scan, ' Else '' End) + (Case When P2.OkeyMRI=0 then 'MRI, ' Else '' End) + (Case When P2.OkeyHS=0 then 'Heart Station, ' Else '' End) + (Case When P2.OkeyAU=0 then 'Audiometry, ' Else '' End) + (Case When P2.OkeyNU=0 then 'Nuclear, ' Else '' End) + (Case When P2.OkeyRTS=0 then 'Pulmonary, ' Else '' End) + (Case When P2.OkeyPT=0 then 'PT,' Else '' End) ) AS Remarks FROM PATIENT_DATA..tbPatient AS tbPatient LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON tbPatient.Hospnum = tbMaster.HospNum LEFT OUTER JOIN BUILD_FILE..tbcoRoom AS tbCoRoom ON tbPatient.RoomID = tbcoRoom.RoomiD LEFT OUTER JOIN PATIENT_DATA..tbPatient2 P2 ON tbPatient.IDNum = P2.IDNum WHERE tbPatient.IDNum = @IDNum and (tbPatient.DcrDate is null or tbPatient.BillingDate is null) ORDER BY [Patient Name]; ELSE SELECT '', tbPatient.HospNum AS [Hospital #], tbPatient.IDNum AS [Admission #], RTRIM(isnull(tbMaster.LastName,'')) + ', ' + RTRIM(isnull(tbMaster.FirstName,'')) + ' ' + RTRIM(isnull(tbMaster.MiddleName,'')) AS [Patient Name], convert(varchar(10),tbPatient.AdmDate,101) + substring(convert(varchar(19),tbPatient.AdmDate,100),12,8) AS [Admission Date], convert(varchar(10),tbPatient.Dcrdate,101) + substring(convert(varchar(19),tbPatient.DcrDate,100),12,8) AS [Discharged Date], convert(varchar(10),tbPatient.Billingdate,101) + substring(convert(varchar(19),tbPatient.BillingDate,100),12,8) AS [Billing Date], Case When (P2.OkeyLab=0 or P2.OkeyXray=0 or P2.OkeyUltra =0 or P2.OkeyPH=0 or P2.OkeyCS=0 or P2.OkeyCT=0 or P2.OkeyMRI=0 or P2.OkeyHS=0 or P2.OkeyAU=0 or P2.OkeyNU=0 or P2.OkeyRTS=0 or P2.OkeyPT=0 ) then 1 Else 0 End As IsCash, ( (Case When P2.OkeyLab=0 then 'Laboratory, ' Else '' End) + (Case When P2.OkeyXray=0 then 'Xray, ' Else '' End) + (Case When P2.OkeyUltra=0 then 'Ultrasound, ' Else '' End) + (Case When P2.OkeyPH=0 then 'Pharmacy, ' Else '' End) + (Case When P2.OkeyCS=0 then 'Central Supply, ' Else '' End) + (Case When P2.OkeyCT=0 then 'CT-Scan, ' Else '' End) + (Case When P2.OkeyMRI=0 then 'MRI, ' Else '' End) + (Case When P2.OkeyHS=0 then 'Heart Station, ' Else '' End) + (Case When P2.OkeyAU=0 then 'Audiometry, ' Else '' End) + (Case When P2.OkeyNU=0 then 'Nuclear, ' Else '' End) + (Case When P2.OkeyRTS=0 then 'Pulmonary, ' Else '' End) + (Case When P2.OkeyPT=0 then 'PT,' Else '' End) ) AS Remarks FROM PATIENT_DATA..tbPatient AS tbPatient LEFT OUTER JOIN PATIENT_DATA..tbMaster AS tbMaster ON tbPatient.Hospnum = tbMaster.HospNum LEFT OUTER JOIN BUILD_FILE..tbcoRoom AS tbCoRoom ON tbPatient.RoomID = tbcoRoom.RoomiD LEFT OUTER JOIN PATIENT_DATA..tbPatient2 P2 ON tbPatient.IDNum = P2.IDNum WHERE (tbMaster.LastName like Ltrim(Rtrim(@Lastname)) + '%') and (tbPatient.DcrDate is null or tbPatient.BillingDate is null) ORDER BY [Patient Name]; ----------------------------------------------------------------------------------- USE [Doctors] GO /****** Object: StoredProcedure [dbo].[Save_FamilyHistory] Script Date: 12/10/2015 14:17:22 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[Save_FamilyHistory]( @Pv_HospNum AS Varchar(10), @Pb_Cancer AS Bit, @Pb_Tuberculosis AS Bit, @Pb_KidneyDisease AS Bit, @Pb_HeartDisease AS Bit, @Pb_Diabetes AS Bit, @Pb_Henophilia AS Bit, @Pb_Arthritis AS Bit, @Pb_Nervous AS Bit, @Pb_Hypertension AS Bit, @Pb_Asthma Bit )AS IF EXISTS(SELECT HospNum FROM FamilyHistory WHERE HospNum = @Pv_HospNum) UPDATE FamilyHistory SET Cancer = @Pb_Cancer, Tuberculosis = @Pb_Tuberculosis, KidneyDisease = @Pb_KidneyDisease, HeartDisease = @Pb_HeartDisease, Diabetes = @Pb_Diabetes, Henophilia = @Pb_Henophilia, Arthritis = @Pb_Arthritis, Nervous = @Pb_Nervous, Hypertension = @Pb_Hypertension, Asthma = @Pb_Asthma WHERE HospNum = @Pv_HospNum ELSE INSERT INTO FamilyHistory( HospNum, Cancer, Tuberculosis, KidneyDisease, HeartDisease, Diabetes, Henophilia, Arthritis, Nervous, Hypertension, Asthma ) VALUES( @Pv_HospNum, @Pb_Cancer, @Pb_Tuberculosis, @Pb_KidneyDisease, @Pb_HeartDisease, @Pb_Diabetes, @Pb_Henophilia, @Pb_Arthritis, @Pb_Nervous, @Pb_Hypertension, @Pb_Asthma)