USE PATIENT_DATA GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbOutPatientHistory]') AND name = 'ICP1') BEGIN ALTER TABLE tbOutPatientHistory ADD ICP1 varchar(15) END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbOutPatientHistory]') AND name = 'ICP2') BEGIN ALTER TABLE tbOutPatientHistory ADD ICP2 varchar(15) END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbOutPatientHistory]') AND name = 'ICP3') BEGIN ALTER TABLE tbOutPatientHistory ADD ICP3 varchar(15) END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tboutpatient]') AND name = 'Purpose') BEGIN ALTER TABLE tboutpatient ADD Purpose varchar (1) END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tboutpatient]') AND name = 'LOANum') BEGIN ALTER TABLE tboutpatient ADD LOANum varchar(20) END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbmaster2]') AND name = 'OfficeNum') BEGIN ALTER TABLE tbmaster2 ADD OfficeNum varchar(50) END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbHospitalInfo]') AND name = 'OtherInfo') BEGIN ALTER TABLE tbHospitalInfo ADD OtherInfo varchar(50) END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbmaster2]') AND name = 'PHICNum') BEGIN ALTER TABLE tbmaster2 ADD PHICNum varchar(50) null END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbOutPatient]') AND name = 'isHomeCare') BEGIN ALTER TABLE tbOutPatient ADD isHomeCare bit null END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbOutPatient]') AND name = 'isDiabet') BEGIN ALTER TABLE tbOutPatient ADD isDiabet bit null END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbopd_settings]') AND name = 'MarkUp') BEGIN ALTER TABLE tbopd_settings ADD MarkUp bit null END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbopd_settings]') AND name = 'isAllowVitalSigns') BEGIN ALTER TABLE tbopd_settings ADD isAllowVitalSigns bit null END GO ---------------- IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tbopd_settings]') AND name = 'isAllowLOANumberOnly') BEGIN ALTER TABLE tbopd_settings ADD isAllowLOANumberOnly bit null END GO ---------------- USE PATIENT_DATA GO ---------------- IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Adm_UpdateMaster2') EXEC ('CREATE PROC dbo.sp_Adm_UpdateMaster2 AS SELECT 1') GO ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster2] --->Dated 02/08/2012 by LARIE @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(50), @EmployerTelNum As VarChar(15), @ContactName As VarChar(50), @ContactAddress As VarChar(50), @ContactRelation As VarChar(50), @ContactTelNum As VarChar(15) , @SpouseAddress varchar(50) , @SpouseTelNum varchar(15) , @FatherAddress varchar(50) , @FatherTelNum varchar(15) , @MotherAddress varchar(50) , @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(50) , @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) 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 ---------------- IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_AOPD_Load_TbMaster') EXEC ('CREATE PROC dbo.sp_sp_AOPD_Load_TbMaster AS SELECT 1') GO ALTER PROCEDURE [dbo].[sp_AOPD_Load_TbMaster] --->Dated 02/08/2012 by LARIE @Hospnum as varchar(10) AS Select m.hospnum, isnull(m.lastname,'') as Lastname, isnull(m.firstname,'') as FirstName, isnull(m.middlename,'') as MiddleName, isnull(m.housestreet,'')as HouseStreet, isnull(m.barangay,'') as Barangay, m.Zipcode, (case when isnull(c.town,'') + ', ' + isnull(c.province,'') = ', ' then '' else isnull(c.town,'') + ', ' + isnull(c.province,'') End) as province, isnull(m.telnum,'') as TelNum, m.birthdate as BirthDate, isnull(m.sex,'') as Sex, isnull(m.age,0) as Age, isnull(m2.Employer,'') as Employer, isnull(m2.EmployerAddress,'') as EmployerAddress, isNull(m2.EmployerTelNum,'') as EmployerTelNum, M.CivilStatus , isnull(convert(varchar(10),m.GovDiscountExpiry,101),'') as GovDiscountExpiry, isNull(M.BloodType,'') [BloodType] , isNull(M2.BirthPlace,'') [BirthPlace], isNull(M.Occupation,'') [Occupation] , isNull(N.Nationality,'') [Nationality] , isNull(R.Religion,'') [Religion], isnull(M2.NationalityID,'') AS NationalityID, isnull(M2.ReligionID,'') AS ReligionID, isnull(M.Title,'') AS Title, SSSGSISNum, m2.phicnum from tbmaster m left outer join Build_File..tbcoaddress c on m.zipcode = c.zipcode left outer join tbMaster2 m2 on m.hospNum=m2.HospNum left outer join tbPatient P on m.HospNum=p.HospNum left outer join build_file..tbcoNationality N on N.NationalityID = M2.NationalityID left outer join build_file..tbcoReligion R on R.ReligionID = M2.ReligionID where m.hospnum = @HospNum set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ---------------- IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'OPD_SaveOPDSettings') EXEC ('CREATE PROC dbo.OPD_SaveOPDSettings AS SELECT 1') GO ALTER Procedure [dbo].[OPD_SaveOPDSettings] --->Dated 02/08/2012 by LARIE @isAllowNewOPSTatRate varchar(1), @isAllowHolidayRate varchar(1), @isAllowAfterFivePMRate varchar(1), @isAllowWeekendRate varchar(1), @isAllowAfterFiveLabChemOnly varchar(1), @isAllowAfterFiveAllRevenues varchar(1), @OPDStatRate float, @HolidayRate float, @AfterFivePmRate float, @WeekendRate float, @MaxMarkUpRate float, @StatAndAfterFivePMRate float, @StatAndWeekendRate float, @HolidayAndWeekend float, @HolidayAndAfterFivePMRate float, @WeekendAfterFive float, @dtWeekendStartMarkUp smalldatetime, @dtWeekendEndMarkUp smalldatetime, @isAssessmentMarkUp as varchar(1), @isPostChargeMarkup as varchar(1), @dtWeekendStartMarkUp2 smalldatetime, @dtWeekendEndMarkUp2 smalldatetime, @MarkUp as bit as Select top 1 * from Patient_data..tbopd_settings; if @@rowcount > 0 begin Update Patient_data..tbOPd_settings set isAllowNewOPSTatRate = @isAllowNewOPSTatRate, isAllowHolidayRate = @isAllowHolidayRate, isAllowAfterFivePMRate = @isAllowAfterFivePMRate, isAllowWeekendRate = @isAllowWeekendRate, isAllowAfterFiveLabChemOnly = @isAllowAfterFiveLabChemOnly, isAllowAfterFiveAllRevenues = @isAllowAfterFiveAllRevenues, OPDStatRate = @OPDStatRate, HolidayRate = @HolidayRate, AfterFivePmRate = @AfterFivePmRate, WeekendRate = @WeekendRate, MaxMarkUpRate = @MaxMarkUpRate, StatAndAfterFivePMRate = @StatAndAfterFivePMRate, StatAndWeekendRate = @StatAndWeekendRate, HolidayAndWeekend = @HolidayAndWeekend, HolidayAndAfterFivePMRate = @HolidayAndAfterFivePMRate, WeekendAfterFive = @WeekendAfterFive, dtWeekendStartMarkUp = @dtWeekendStartMarkUp, dtWeekendEndMarkUp = @dtWeekendEndMarkUp, isAssesmentMarkup = @isAssessmentMarkUp, isPostChargeMarkUp = @isPostChargeMarkup, dtWeekendStartMarkUp2 = @dtWeekendStartMarkUp2, dtWeekendEndMarkUp2 = @dtWeekendEndMarkUp2, MarkUp = @MarkUp end else begin insert into patient_data..tbopd_settings( isAllowNewOPSTatRate, isAllowHolidayRate, isAllowAfterFivePMRate, isAllowWeekendRate, isAllowAfterFiveLabChemOnly, isAllowAfterFiveAllRevenues,OPDStatRate, HolidayRate, AfterFivePmRate, WeekendRate, MaxMarkUpRate, StatAndAfterFivePMRate, StatAndWeekendRate, HolidayAndWeekend, HolidayAndAfterFivePMRate, WeekendAfterFive, dtWeekendStartMarkUp, dtWeekendEndMarkUp, isAssesmentMarkup, isPostChargeMarkUp, dtWeekendStartMarkUp2,dtWeekendEndMarkUp2,MarkUp) values (@isAllowNewOPSTatRate, @isAllowHolidayRate, @isAllowAfterFivePMRate, @isAllowWeekendRate, @isAllowAfterFiveLabChemOnly, @isAllowAfterFiveAllRevenues,@OPDStatRate, @HolidayRate, @AfterFivePmRate, @WeekendRate, @MaxMarkUpRate, @StatAndAfterFivePMRate, @StatAndWeekendRate, @HolidayAndWeekend, @HolidayAndAfterFivePMRate, @WeekendAfterFive, @dtWeekendStartMarkUp, @dtWeekendEndMarkUp, @isAssessmentMarkUp,@isPostChargeMarkup,@dtWeekendStartMarkUp2,@dtWeekendEndMarkUp2,@MarkUp) end GO ---------------- IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Adm_UpdateMaster2') EXEC ('CREATE PROC dbo.sp_Adm_UpdateMaster2 AS SELECT 1') GO set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_Adm_UpdateMaster2] --->Dated 02/08/2012 by LARIE @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(50), @EmployerTelNum As VarChar(15), @ContactName As VarChar(50), @ContactAddress As VarChar(50), @ContactRelation As VarChar(50), @ContactTelNum As VarChar(15) , @SpouseAddress varchar(50) , @SpouseTelNum varchar(15) , @FatherAddress varchar(50) , @FatherTelNum varchar(15) , @MotherAddress varchar(50) , @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(50) , @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) 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 ---------------- IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Adm_LoadPatient') EXEC ('CREATE PROC dbo.sp_Adm_LoadPatient AS SELECT 1') GO set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_Adm_LoadPatient] --->Dated 02/08/2012 by LARIE @HospNum AS VarChar(8) 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] 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] FROM tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum WHERE M.HospNum = @HospNum end go set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ---------------- IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_AOPD_SearchRevenue') EXEC ('CREATE PROC dbo.sp_AOPD_SearchRevenue AS SELECT 1') GO ALTER PROCEDURE [dbo].[sp_AOPD_SearchRevenue] --->Dated 02/08/2012 by LARIE @SearchType varchar(1), @SearchCriteria varchar(50) AS if @SearchType = '0' begin select r.ItemName [Description], r.RevenueID [Code] from BUILD_FILE..tbCoRevenueCode r where ISNULL(r.OPDActive, '') = 'Y' and r.RecordStatus = 'A' and r.ItemName like @SearchCriteria + '%' order by r.ItemName end if @SearchType = '1' begin select r.ItemName [Revenue], r.RevenueID, r.RWith [WithItems], r.SearchInventory, r.LocationID,b.Revenueid [InvCode] from BUILD_FILE..tbCoRevenueCode r left join BILLING..tbBillInvCode b on r.Revenueid = b.ServiceID where r.RecordStatus = 'A' AND r.RevenueID = @SearchCriteria end if @SearchType = '2' begin select Count(*) [RecCount] from BUILD_FILE..tbCoRevenueCode r where ISNULL(r.OPDActive, '') = 'Y' and r.RecordStatus = 'A' and r.ItemName like @SearchCriteria + '%' end if @SearchType = '3' --with inventory code begin select r.ItemName [Revenue], r.RevenueID, r.RWith [WithItems], r.SearchInventory, r.LocationID, r.StockCardRevenueID [InventoryCode] from BUILD_FILE..tbCoRevenueCode r where ISNULL(r.OPDActive, '') = 'Y' and r.RecordStatus = 'A' --AND r.revenueid = 'CT' OR REVENUEID = 'LB' OR REVENUEID = 'EM' OR REVENUEID = 'OX'--AND r.revenueid = 'EL' OR REVENUEID = 'ER' OR REVENUEID = 'EM' OR REVENUEID = 'OX' or r.REVENUEID = 'PT' OR REVENUEID = 'MD' OR REVENUEID = 'PA' and r.RevenueID = @SearchCriteria end if @SearchType = '4' begin select r.ItemName [Description], r.RevenueID [Code] from BUILD_FILE..tbCoRevenueCode r where ISNULL(r.OPDActive, '') = 'Y' and r.RecordStatus = 'A' and r.IsPatientCard ='1' and r.ItemName like @SearchCriteria + '%' order by r.ItemName end GO ---------------- USE PATIENT_DATA GO ---------------- '--additional reports for revoked patient 12/21/2012 IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_ListOfRevokedPatient') EXEC ('CREATE PROC dbo.sp_ListOfRevokedPatient AS SELECT 1') GO ALTER PROCEDURE sp_ListOfRevokedPatient AS select A.IDNum,B.HospNum,A.TransDate,LTRIM(RTRIM(Isnull(C.LastName,''))) + ', ' + LTRIM(RTRIM(Isnull(C.FirstName,''))) + ' ' + LTRIM(RTRIM(Isnull(C.MiddleName, ''))) AS PatientName, LTRIM(RTRIM(Isnull(D.LastName,''))) + ', ' + LTRIM(RTRIM(Isnull(D.FirstName,''))) + ' ' + LTRIM(RTRIM(Isnull(D.MiddleName, ''))) AS RevokedBy from patient_data..tbOPD_RevokedLog A left outer join patient_data..tbOutPatient B on A.IDnum = B.IDnum left outer join patient_data..tbMaster C on B.Hospnum = C.Hospnum left outer join password..tbpasswordmain D on A.UserID = D.EmployeeID GO ---------------- USE PATIENT_DATA GO ---------------- '--additional reports for monthly diagnosis 12/26/2012 IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_AOPD_Diagnosis') EXEC ('CREATE PROC dbo.sp_AOPD_Diagnosis AS SELECT 1') GO ALTER PROCEDURE sp_AOPD_Diagnosis @StartDate as varchar(10), @EndDate as varchar(10) /* set @StartDate = 12/01/2003 set @EndDate = '12/31/2003' */ as declare @YearStart as varchar(10) declare @YearEnd as varchar(10) declare @LastYearStart as varchar(10) declare @LastYearEnd as varchar(10) declare @LastMonthStart as varchar(10) declare @LastMonthEnd as varchar(10) set @YearStart = '01/01/' + cast(year(@StartDate) as varchar(4)) set @YearEnd = '12/31/' + cast(year(@StartDate) as varchar(4)) set @LastMonthStart = cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastMonthEnd = cast(month(@EndDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearStart = '01/01/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearEnd = '12/31/' + cast(year(@StartDate) - 1 as varchar(4)) select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(max(C.Code),'') as Code, isnull(max(C.[Description]),'') as [Description] from PATIENT_DATA..tbOutPatient P left outer join patient_data..tboutpatientHistory H on P.IDnum = H.IdNum left outer join BUILD_FILE..tbICDDiagMain C on H.DiagnosisID1 = C.Code where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and isnull(P.OPDStatus,'') not in ('R','E') and isnull(P.userid,'') <> '' group by isnull(H.DiagnosisID1,'') union all select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(max(C.Code),'') as Code, isnull(max(C.[Description]),'') as [Description] from PATIENT_DATA..tbOutPatient P left outer join patient_data..tboutpatientHistory H on P.IDnum = H.IdNum left outer join BUILD_FILE..tbICDDiagMain C on H.DiagnosisID2 = C.Code where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and isnull(P.OPDStatus,'') not in ('R','E') and isnull(P.userid,'') <> '' group by isnull(H.DiagnosisID2,'') union all select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(max(C.Code),'') as Code, isnull(max(C.[Description]),'') as [Description] from PATIENT_DATA..tbOutPatient P left outer join patient_data..tboutpatientHistory H on P.IDnum = H.IdNum left outer join BUILD_FILE..tbICDDiagMain C on H.DiagnosisID3 = C.Code where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and isnull(P.OPDStatus,'') not in ('R','E') and isnull(P.userid,'') <> '' group by isnull(H.DiagnosisID3,'') GO ----------------