USE Build_FIle GO IF OBJECT_ID('sp_Bui_RepDoctorLists_PerInsurance', 'P') IS NOT NULL DROP PROC sp_Bui_RepDoctorLists_PerInsurance GO CREATE PROCEDURE sp_Bui_RepDoctorLists_PerInsurance @doctor_id as varchar(50) = '' AS BEGIN SET NOCOUNT ON; SELECT dh.*, doc.Lastname + ', ' + doc.Firstname + CASE WHEN doc.Middlename IN(NULL,'') THEN '' ELSE ' ' + LEFT(doc.Middlename,1) + '.' END AS NAME, co.Company FROM tbCoDoctorInsurance dh LEFT JOIN tbCoDoctor doc ON dh.doctor_id = doc.DoctorID LEFT JOIN tbCoCompany co ON dh.account_num = co.AccountNum WHERE (@doctor_id = '' OR doc.DoctorID = @doctor_id) END GO --#----STORED PROCEDURE ALTERATIONS----#-- USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[Help_SearchCompany] Script Date: 12/14/2015 10:56:29 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Help_SearchCompany] @strType as varchar(1), @strSearch as varchar(35) AS If @strType = '1' Begin Select AccountNum [Code], Company [Description] From tbCoCompany Where AccountNum = @strSearch and Status = 'A' End; Else If @strType = '2' Begin Select AccountNum [Code], Company [Description] From tbCoCompany Where Status = 'A' Order By Company End; Else If @strType = '3' --Insurance Begin Select AccountNum [Code], Company [Description] From tbCoCompany Where Status = 'A' AND CLASS = 'I' AND Company LIKE '%'+@strSearch+'%' Order By Company End; If @strType = '4' --Insurance Begin Select AccountNum [Code], Company [Description] From tbCoCompany Where Status = 'A' AND CLASS = 'I' AND AccountNum = @strSearch Order By Company End; --Else --If @strType = '7' -- Begin -- Select AccountNum [Code], Company [Description] -- From tbCoCompany -- Where Status = 'A' -- Order By Company -- End; GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_RepDoctorLists_PerInsurance] Script Date: 12/16/2015 5:34:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: NEBALASCA, ARJELYN P. -- Create date: 12/16/2015 -- Description: Get all Doctor's Insurance -- ============================================= ALTER PROCEDURE [dbo].[sp_Bui_RepDoctorLists_PerInsurance] @doctor_id AS varchar(50) = '', @status AS varchar(1) = '' AS BEGIN SET NOCOUNT ON; SELECT dh.*, doc.Lastname + ', ' + doc.Firstname + CASE WHEN doc.Middlename IN(NULL,'') THEN '' ELSE ' ' + LEFT(doc.Middlename,1) + '.' END AS name, co.CompanyID, co.Company, CASE WHEN dh.date_expiry >= CAST(GETDATE() AS date) THEN 'A' WHEN dh.date_expiry < CAST(GETDATE() AS date) THEN 'I' END AS status FROM tbCoDoctorInsurance dh LEFT JOIN tbCoDoctor doc ON dh.doctor_id = doc.DoctorID LEFT JOIN tbCoCompany co ON dh.account_num = co.AccountNum WHERE (@doctor_id = '' OR doc.DoctorID = @doctor_id) AND (@status = '' OR ( (@status = 'A' AND dh.date_expiry >= CAST(GETDATE() AS date)) OR (@status = 'I' AND dh.date_expiry < CAST(GETDATE() AS date)) ) ) ORDER BY doc.Lastname ASC, CO.Company ASC END GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Build_UserSettings] Script Date: 12/17/2015 10:47:06 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Build_UserSettings] --->Dated 02/08/2012 by sensei @EmployeeID as varchar(30), @sCompany as varchar(1), @sBussinessClass as varchar(1), @sDepartment as varchar(1), @sDoctor as varchar(1), @sNationality as varchar(1), @sReligion as varchar(1), @sAddress as varchar(1), @sBarangay as varchar(1), @sRoomClass as varchar(1), @sRoom as varchar(1), @sStation as varchar(1), @sAccomodation as varchar(1), @sDosage as varchar(1), @sItemPackage as varchar(1), @sRevenue as varchar(1), @sCharging as varchar(1), @sAccounting as varchar(1), @sLabCode as varchar(1), @sLabSpecimen as varchar(1), @sSection as varchar(1), @sService as varchar(1), @sAddressRPT as varchar(1)='0', @sDoctorsRPT as varchar(1)='0', @sCompanyRPT as varchar(1)='0', @sRoomCodesRPT as varchar(1)='0', @sDepartmentsRPT as varchar(1)='0', @sBarangaysRPT as varchar(1)='0', @sRvcRPT as varchar(1)='0', @sRevExamRPT as varchar(1)='0', @sARCodesRPT as varchar(1)='0', @sGLChartCodesRPT as varchar(1)='0', @sAccountingRPT as varchar(1)='0', @sDeptARCodeRPT as varchar(1)='0', @sInterpretRPT as varchar(1)='0', @sRCCodesRPT as varchar(1)='0', @sTelephoneRPT as varchar(1) ='0', @sMedicalSpecializationRPT as varchar(1) = '0', @sSurgicalCodeRPT as varchar(1) = '0', @sDocProfFee as varchar(1)='0', @sDiscountRate as varchar(1)='0', @sCompanyRate as varchar(1)='0', @sDocSpec as varchar(1) ='0', @sSOP as varchar(1) = '0', @sExePackage as varchar(1) = '0', @UserLog as varchar(1)= '0', @UserSetting as varchar(1) = '1', @ForInfo as varchar(1) = '0', @RoomStatus as varchar(1) = '0', @DepositType as varchar(1) = '0', @HospitalList as varchar(1) = '0', @ICDcode as varchar(1) = '0', @CreditMemo as varchar(1) = '0', @CausesofConsultation as varchar(1) = '0', @Holiday as varchar(1) = '0', @UserValidation as varchar(1) = '0', @Points as varchar(1)= '0', @InputTax as [bit], @PhicNumber as [bit], @BankAccount as [bit], @DoctorRate as [bit], @DoctorUtilities as [bit], @Amenities as [bit], @LogoandImage as [bit], @sBatchUpdate as [bit], @sClinicInfo as [bit], @sDocAccess as [bit], @sClinicAddinfo as [bit], @isAllowUploadDoctor as [bit], @inputInsurance as [bit] AS If exists (Select * from tbBuildUserSettings where employeeId= @EmployeeId) Begin Update TbBuildUserSettings set EmployeeId = @Employeeid, sCompany = @sCompany, sBussinessClass = @sBussinessClass, sDepartment = @sDepartment, sDoctor = @sDoctor, sNationality = @sNationality, sReligion = @sReligion, sAddress = @sAddress, sBarangay = @sBarangay, sRoomClass = @sRoomClass, sRoom = @sRoom, sStation = @sStation, sAccomodation = @sAccomodation, sDosage = @sDosage, sItemPackage = @sItemPackage, sCharging = @sCharging, sRevenue = @sRevenue, sAccounting = @sAccounting, sLabCode = @SLabCode, sLabSpecimen = @sLabSpecimen, sSection = @sSection, sService = @sService, sAddressRPT = @sAddressRPT, sDoctorsRPT = @sDoctorsRPT , sCompanyRPT = @sCompanyRPT, sRoomCodesRPT = @sRoomCodesRPT, sDepartmentsRPT = @sDepartmentsRPT , sBarangaysRPT = @sBarangaysRPT, sRvcRPT = @sRvcRPT, sRevExamRPT = @sRevExamRPT, sARCodesRPT = @sARCodesRPT, sGLChartCodesRPT = @sGLChartCodesRPT, sAccountingRPT = @sAccountingRPT, sDeptARCodeRPT = @sDeptARCodeRPT, sInterpretRPT = @sInterpretRPT, sRCCodesRPT = @sRCCodesRPT, sTelephoneRPT = @sTelephoneRPT, sMedicalSpecializationRPT = @sMedicalSpecializationRPT, sSurgicalCodeRPT = @sSurgicalCodeRPT, sDoctorProfFee = @sDocProfFee, sDiscountRate = @sDiscountRate, sCompanyRate = @sCompanyRate, sDocSpecialization = @sDocSpec, sSOP = @sSOP, sExePackage = @sExePackage, sUserlog = @UserLog, sUserSetting = @UserSetting, sForInfo = @ForInfo, sRoomStatus = @roomstatus, sDepositType = @DepositType, sHospitalList = @HospitalList, sICDcode = @ICDcode, sCreditMemo = @CreditMemo, sCausesofConsultation = @CausesofConsultation, sHoliday =@Holiday, sShowUserValidation = @UserValidation, sEarningPoints = @Points, sInputTax = @InputTax, sPHICnumber = @PhicNumber, sBankAccount = @BankAccount, sDoctorRate = @DoctorRate, sDoctorUtilities = @DoctorUtilities, sAmenities = @Amenities, sLogoandImage = @LogoandImage, sBatchUpdate = @sBatchUpdate, sClinicInfo = @sClinicInfo, sDocAccess = @sDocAccess, sClinicAddinfo = @sClinicAddinfo, isAllowUploadDoctor = @isAllowUploadDoctor, sInputInsurance = @inputInsurance Where EmployeeId = @EmployeeId; End Else Begin Insert into TbBuildUserSettings(EmployeeID,sCompany,sBussinessClass,sDepartment,sDoctor,sNationality, sReligion, sAddress, sBarangay,sRoomClass, sRoom,sStation,sAccomodation, sDosage, sItemPackage, sCharging, sRevenue, sAccounting, sLabCode, sLabSpecimen, sSection, sService, sAddressRPT, sDoctorsRPT, sCompanyRPT, sRoomCodesRPT, sDepartmentsRPT, sBarangaysRPT, sRvcRPT, sRevExamRPT, sARCodesRPT, sGLChartCodesRPT, sAccountingRPT, sDeptARCodeRPT, sInterpretRPT, sRCCodesRPT, sTelephoneRPT, sMedicalSpecializationRPT, sSurgicalCodeRPT, sDoctorProfFee, sDiscountRate, sCompanyRate, sDocSpecialization, sSOP, sExePackage, sUSerLog, sUserSetting, sForInfo,sRoomStatus,sDepositType,sHospitalList,sICDcode, sCreditMemo,sCausesofConsultation,sHoliday,sShowUserValidation,sEarningPoints,sInputTax,sPHICnumber,sBankAccount,sDoctorRate, sDoctorUtilities,sAmenities,sLogoandImage,sBatchUpdate,sClinicInfo,sDocAccess,sClinicAddinfo,isAllowUploadDoctor,sInputInsurance) values(@EmployeeID,@sCompany,@sBussinessClass, @sDepartment,@sDoctor,@sNationality, @sReligion, @sAddress, @sBarangay,@sRoomClass, @sRoom,@sStation,@sAccomodation, @sDosage, @sItemPackage, @sCharging, @sRevenue, @sAccounting, @sLabCode, @sLabSpecimen, @sSection, @sService, @sAddressRPT, @sDoctorsRPT , @sCompanyRPT, @sRoomCodesRPT, @sDepartmentsRPT , @sBarangaysRPT, @sRvcRPT, @sRevExamRPT, @sARCodesRPT, @sGLChartCodesRPT, @sAccountingRPT, @sDeptARCodeRPT, @sInterpretRPT, @sRCCodesRPT, @sTelephoneRPT, @sMedicalSpecializationRPT, @sSurgicalCodeRPT, @sDocProfFee, @sDiscountRate, @sCompanyRate, @sDocSpec, @sSOP, @sExePackage, @UserLog, @UserSetting, @ForInfo, @RoomStatus,@DepositType,@HospitalList,@ICDcode, @CreditMemo,@CausesofConsultation,@Holiday,@UserValidation,@Points,@InputTax,@PhicNumber,@BankAccount, @DoctorRate,@DoctorUtilities,@Amenities,@LogoandImage,@sBatchUpdate,@sClinicInfo,@sDocAccess,@sClinicAddinfo,@isAllowUploadDoctor,@inputInsurance) End; GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[spBuild_UserSettingsNew] Script Date: 12/17/2015 11:27:11 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[spBuild_UserSettingsNew] --->Dated 02/08/2012 by sensei @Employeeid as varchar(10) as SELECT isnull(EmployeeID, 0)EmployeeIDisnull, isnull(sCompany, 0)sCompany, isnull(sBussinessClass, 0) sBussinessClass, isnull(sDepartment, 0) sDepartment, isnull(sNationality, 0) sNationality, isnull(sAddress, 0) sAddress, isnull(sBarangay, 0) sBarangay, isnull(sReligion, 0)sReligion, isnull(sDoctor, 0)sDoctor, isnull(sRoom, 0)sRoom, isnull(sStation, 0)sStation, isnull(sRoomClass, 0)sRoomClass, isnull(sAccomodation, 0)sAccomodation, isnull(sDosage, 0)sDosage, isnull(sItemPackage, 0)sItemPackage, isnull(sRevenue, 0)sRevenue, isnull(sCharging, 0)sCharging, isnull(sAccounting, 0)sAccounting, isnull(sLabCode, 0)sLabCode, isnull(sLabSpecimen, 0)sLabSpecimen, isnull(sSection, 0)sSection, isnull(sService, 0)sService, isnull(sAddressRPT, 0)sAddressRPT, isnull(sDoctorsRPT, 0)sDoctorsRPT, isnull(sCompanyRPT, 0)sCompanyRPT, isnull(sRoomCodesRPT, 0)sRoomCodesRPT, isnull(sDepartmentsRPT, 0)sDepartmentsRPT, isnull(sBarangaysRPT, 0)sBarangaysRPT, isnull(sRvcRPT, 0)sRvcRPT, isnull(sRevExamRPT, 0)sRevExamRPT, isnull(sARCodesRPT, 0)sARCodesRPT, isnull(sGLChartCodesRPT, 0)sGLChartCodesRPT, isnull(sAccountingRPT, 0)sAccountingRPT, isnull(sDeptARCodeRPT, 0)sDeptARCodeRPT, isnull(sInterpretRPT, 0)sInterpretRPT, isnull(sRCCodesRPT, 0)sRCCodesRPT, isnull(sTelephoneRPT, 0)sTelephoneRPT, isnull(sMedicalSpecializationRPT, 0)sMedicalSpecializationRPT, isnull(sSurgicalCodeRPT, 0)sSurgicalCodeRPT, isnull(sDoctorProfFee, 0)sDoctorProfFee, isnull(sDiscountRate, 0)sDiscountRate, isnull(sCompanyRate, 0)sCompanyRate, isnull(sDocSpecialization, 0)sDocSpecialization, isnull(sSOP, 0)sSOP, isnull(sExePackage, 0)sExePackage, isnull(sUserLog, 0)sUserLog, isnull(sUserSetting, 0)sUserSetting, isnull(sForInfo, 0)sForInfo, isnull(sRoomStatus,0)sRoomStatus, isnull(sDeposittype, 0)sDepositType, isnull(sHospitalList,0)sHospitalList, isnull(sICDCode,0)sICDcode, isnull(sCreditMemo,0)sCreditMemo, isnull(sCausesofConsultation,0)sCausesofConsultation, isnull(sHoliday,0)sHoliday, isnull(sShowUserValidation , 0) sShowUserValidation , isnull(sEarningPoints, 0)sEarningPoints, isnull(sInputTax,0)sInputTax, isnull(sPHICnumber,0)sPHICnumber, isnull(sBankAccount,0)sBankAccount, isnull(sDoctorRate,0) sDoctorRate, isnull(sDoctorUtilities,0) sDoctorUtilities, isnull(sAmenities,0) sAmenities, isnull(sLogoandImage,0)sLogoandImage, isnull(sBatchUpdate,0) sBatchUpdate, isnull(sClinicInfo,0) sClinicInfo, isnull(sDocAccess,0)sDocAccess, isnull(sClinicAddinfo,0)sClinicAddinfo, ISNULL(isAllowUploadDoctor,0) [isAllowUploadDoctor], ISNULL(sInputInsurance,0) AS sInputInsurance FROM BUILD_FILE.dbo.TbBuildUserSettings where employeeid = @Employeeid GO USE [Build_File] GO /****** Object: StoredProcedure [dbo].[sp_Bui_ReportExecutivePackageitems] Script Date: 9/30/2015 6:25:52 PM ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Bui_ReportExecutivePackageitems] @PackageID as varchar(5) AS SELECT C.Package, A.RevenueID, A.ExamID, CASE WHEN B.Description IS NULL THEN E.ItemName ELSE B.Description END AS Description, A.Quantity, A.Amount, CASE WHEN A.Gender = 'M' THEN 'Male' WHEN A.Gender = 'F' THEN 'Female' ELSE 'Both' END AS Gender, CASE WHEN A.Status = 'I' THEN 'Inactive' ELSE 'Active' END AS Status, C.Package, CASE WHEN RV.RateDDate IS NULL THEN 0 ELSE RV.RateD END RateIncrease, CASE WHEN RV.RateDDate IS NULL THEN NULL ELSE convert(varchar(10), RV.RateDDate,101) END DateIncrease FROM tbcoadmpackage_items A LEFT OUTER JOIN Billing..tbBillExamListing B ON A.RevenueID = B.RevenueID AND A.ExamID = B.ItemID LEFT OUTER JOIN tbcoAdmPackage C ON A.PackageID = C.PackageID LEFT OUTER JOIN tbcootherrevenue RV ON RV.OtherSectionID = A.RevenueID AND OtherRevenueID = A.ExamID LEFT OUTER JOIN Inventory..tbInvMaster E ON A.ExamID = E.ItemID WHERE A.PackageID = @PackageID GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_SearchExecutivePackageitems] Script Date: 1/12/2016 2:15:02 PM ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Bui_SearchExecutivePackageitems] @PackageID as varchar(5) AS /*Declare @PackageID as varchar(5) set @PackageID = '9' */ Select A.RevenueID, A.ExamID, CASE WHEN B.Description IS NULL THEN E.ItemName ELSE B.Description END AS Description, A.Quantity, A.Amount, Case When A.Gender = 'M' then 'Male' When A.Gender = 'F' then 'Female' Else 'Both' End as Gender, Case When A.Status = 'I' then 'Inactive' Else 'Active' End as Status From tbcoadmpackage_items A Left Outer Join Billing..tbBillExamListing B On A.RevenueID = B.RevenueID and A.ExamID = B.ItemID LEFT OUTER JOIN Inventory..tbInvMaster E ON A.ExamID = E.ItemID Where A.PackageID = @PackageID GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_bui_InsertUpdateHolidays] Script Date: 1/13/2016 2:34:26 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[sp_bui_InsertUpdateHolidays] @HolidayCode as varchar(5), @HolidayName as varchar(40), @Greetings as varchar(50), @HolidayDate as varchar(20), @isFixed as bit, @HolidayType as bit, @isActive as bit as declare @NewHolidayCode as varchar(5), @NewHolidayDate as datetime set @NewHolidayDate = @Holidaydate + '/' + convert(varchar(4),year(getdate()), 101 ); Select top 1 * from tbcoholidays where HolidayID = @HolidayCode; if @@rowcount > 0 Begin Update Build_file..TbcoHolidays set HolidayName = @HolidayName, HolidayGreeting = @Greetings, HolidayDate = @NewHolidayDate, HolidayMonth = Month(@NewHolidayDate), HolidayDay = Day(@NewHolidayDate), HolidayYear = Year(@NewHolidayDate), isFixed = @isFixed, HolidayType = @HolidayType, Status = @isActive where HolidayId = @HolidayCode end; else Begin set @NewHolidayCode = (SELECT ISNULL(HolidayCode,(SELECT MAX(CAST(HolidayID AS int)) FROM tbCoHolidays)) + 1 FROM TbcoLastNumber); Insert Into TbcoHolidays(HolidayID, HolidayName, HolidayGreeting, Holidaydate, HolidayMonth, HolidayDay, HolidayYear, isFixed, Holidaytype, Status ) values (@NewHolidayCode, @HolidayName, @Greetings, @NewHolidayDate, Month(@NewHolidayDate), Day(@NewHolidayDate), Year(@NewHolidayDate), @isFixed, @HolidayType, @isActive) Update TbcoLastNumber set Holidaycode = @NewHolidayCode end; GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Insert_Item] Script Date: 1/19/2016 5:32:41 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Bui_Insert_Item] (@RevenueID as varchar(2), @ItemID as varchar(7), @SectionID as varchar(3), @ExamName as varchar(45), @RateA as float, @RateB as Float, @RateC as float, @RateD as float, @RateE as float, @RateF as Float, @RateG as Float, @RateH as Float, @Fixrate as varchar(1), @Status as varchar(1), @MedicareType as varchar(1), @common as varchar(1), @RateSD as float=0.00, @IsSenior as bit, @IsSpecial as bit, @AccountCode as varchar(12), @KidneyActive as varchar(1), @DRActive as varchar(1), @HSActive as varchar(1), @ERActive as varchar(1), @ICUActive as varchar(1), @PICUActive as varchar(1), @ORActive as varchar(1), @NurseryActive as varchar(1), @NurseActive as varchar(1), @OPDActive as varchar(1), @PulmoActive as varchar(1), @PTActive as varchar(1), @MedRecActive as varchar(1), @NuclearActive as varchar(1), @SICUActive as varchar(1), @CCUActive as varchar(1), @WCActive as varchar(1), @EndoActive as varchar(1), @EyeActive as varchar(1), @CathLabActive as varchar(1), @rateI as float = 0) AS If (@RevenueID = 'AU') Insert into tbcoaudexam(AudExamID, AudSectionID, AudExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'CT') Insert into tbcoctexam(CtExamID, CtSectionID, CtExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'HS') Insert into tbcohsexam(HsExamID, HsSectionID, HsExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH,@Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'LB') Insert into tbcolabexam(LabExamID, LabSectionID, LabExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH,@Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'NU') Insert into tbconucexam(NucExamID, NucSectionID, NucExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'PT') Insert into tbcoptexam(PtExamID, PtSectionID, PtExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH,Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'PA') Insert into tbcortsexam(RtsExamID, RtsSectionID, RtsExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'US') Insert into tbcoultraexam(UltraExamID, UltraSectionID, UltraExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'XR') Insert into tbcoxrayexam(XrayExamID, XraySectionID, XrayExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else Insert into tbcootherrevenue(OtherRevenueID, SectionID, OtherRevenue, OtherSectionID, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, KidneyActive, DRActive, HSActive, ERActive, ICUActive, PICUActive, ORActive, NurseryActive, NurseActive, OPDActive, PulmoActive, PTActive, MedRecActive, NuclearActive, SICUActive, CCUActive, WCActive, EndoActive, EyeActive, CathLabActive , rateI) values (@ItemId, @SectionID, @ExamName, @RevenueID, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode, @KidneyActive, @DRActive, @HSActive, @ERActive, @ICUActive, @PICUActive,@ORActive, @NurseryActive, @NurseActive, @OPDActive, @PulmoActive, @PTActive, @MedRecActive, @NuclearActive, @SICUActive, @CCUActive, @WCActive, @EndoActive, @EyeActive, @CathLabActive,@rateI) set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Update_Item_Date] Script Date: 1/19/2016 5:33:18 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Bui_Update_Item_Date] @RevenueID as varchar(2), @ItemID as varchar(7), @RateADate as varchar(1), @RateBDate as varchar(1), @RateCDate as varchar(1), @RateDDate as varchar(1), @RateEDate as varchar(1), @RateFDate as varchar(1), @RateGDate as varchar(1) AS Declare @DateA as datetime Declare @DateB as datetime Declare @DateC as datetime Declare @DateD as datetime Declare @DateE as datetime Declare @DateF as datetime Declare @DateG as datetime if @RateADate = '1' begin set @DateA = getdate() end else begin set @DateA = null end if @RateBDate = '1' begin set @DateB = getdate() end else begin set @DateB = null end if @RateCDate = '1' begin set @DateC = getdate() end else begin set @DateC = null end if @RateDDate = '1' begin set @DateD = getdate() end else begin set @DateD = null end if @RateEDate = '1' begin set @DateE = getdate() end else begin set @DateE = null end if @RateFDate = '1' begin set @DateF = getdate() end else begin set @DateF = null end if @RateGDate = '1' begin set @DateG = getdate() end else begin set @DateG = null end If (@RevenueID = 'AU') Update tbcoaudexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where audexamid = @itemid else If (@RevenueID = 'CT') Update tbcoctexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where ctexamid = @itemid else If (@RevenueID = 'HS') Update tbcohsexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where hsexamid = @itemid else If (@RevenueID = 'LB') Update tbcolabexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where labexamid = @itemid else If (@RevenueID = 'NU') Update tbconucexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where nucexamid = @itemid else If (@RevenueID = 'PT') Update tbcoptexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where ptexamid = @itemid else If (@RevenueID = 'PA') Update tbcortsexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where rtsexamid = @itemid else If (@RevenueID = 'US') Update tbcoultraexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where ultraexamid = @itemid If (@RevenueID = 'XR') Update tbcoxrayexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where xrayexamid = @itemid else Update tbcootherrevenue set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where otherrevenueid = @itemid and othersectionid = @revenueid GO USE [Build_FIle] GO IF OBJECT_ID('SP_bui_Doctor_License_ExpiryDate','P') IS NOT NULL DROP PROCEDURE SP_bui_Doctor_License_ExpiryDate /****** Object: StoredProcedure [dbo].[SP_bui_Doctor_License_ExpiryDate] Script Date: 1/4/2016 5:41:57 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --declare CREATE PROCEDURE [dbo].[SP_bui_Doctor_License_ExpiryDate] @StartDate varchar(25), @EndDate varchar(25), @Type varchar(1) = '' AS SELECT DoctorID, ltrim(rtrim(isnull(A.lastname,''))) + ', ' + ltrim(rtrim(isnull(A.firstname,''))) + ' ' + ltrim(rtrim(isnull(A.middlename,''))) as DoctorName, License, LicenseExpireDate, PhilHealthNum, PHICExpireDate, S2Number, S2Expiry, ExpiryDate FROM ( SELECT CONVERT(Varchar(30),(CASE @Type WHEN '' THEN LicenseExpireDate WHEN '1' THEN PHICExpireDate WHEN '2' THEN S2Expiry END),101) AS ExpiryDate, * FROM tbCoDoctor )A WHERE ISDATE(A.ExpiryDate) = 1 AND A.Status = 'A' AND CONVERT(datetime , CASE WHEN ISDATE(A.ExpiryDate) = 1 THEN A.ExpiryDate END) > @startdate AND CONVERT(datetime , CASE WHEN ISDATE(A.ExpiryDate) = 1 THEN A.ExpiryDate END) < @Enddate GO USE [Build_File] GO IF OBJECT_ID('SP_bui_Doctor_Birthday','P') IS NOT NULL DROP PROCEDURE SP_bui_Doctor_Birthday /****** Object: StoredProcedure [dbo].[SP_bui_Doctor_Birthday] Script Date: 9/30/2015 6:13:40 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SP_bui_Doctor_Birthday] ( @StartDate varchar(25), @EndDate Varchar(25) ) AS DECLARE @StartMonth varchar(10) DECLARE @EndMonth varchar(10) SET @StartMonth = SUBSTRING(@StartDate,1,2) SET @EndMonth = SUBSTRING(@EndDate,1,2) SELECT * FROM ( SELECT birthday [Bday],* FROM tbCoDoctor WHERE ISDATE(birthDay) = 1 ) A WHERE bday BETWEEN (@StartMonth) AND (@EndMonth) ORDER BY bday GO USE [Build_File] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Find_Revenue_Center_Items_For_Report_Purposes] Script Date: 9/30/2015 6:17:07 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Bui_Find_Revenue_Center_Items_For_Report_Purposes] ( @RevenueID as varchar(2), @Type as varchar(1) ) AS If (@RevenueID = 'AU') If (@type = '1') Select '' as dumm, audexam as examname, audexamid as examid, tbcosection.sectionname, MedicareType, ratea, rateb, ratec, rated, ratee, rateG,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoaudexam left outer join tbcosection on revenueid = @revenueid and tbcoaudexam.audsectionid = tbcosection.sectionid order by examname else -- if (@type = '2') Select '' as dumm, audexam as examname, audexamid as examid, tbcosection.sectionname, MedicareType, ratea, rateb, ratec, rated, ratee, rateG,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoaudexam left outer join tbcosection on revenueid = @revenueid and tbcoaudexam.audsectionid = tbcosection.sectionid where status = 'A' order by examname else If (@RevenueID = 'CT') if (@type = '1') Select '' as dumm, ctexam as examname, ctexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoctexam left outer join tbcosection on tbcosection.revenueid = @revenueid and tbcoctexam.ctsectionid = tbcosection.sectionid order by examname else -- if (@type = '2') Select '' as dumm, ctexam as examname, ctexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoctexam left outer join tbcosection on tbcosection.revenueid = @revenueid and tbcoctexam.ctsectionid = tbcosection.sectionid where status = 'A' order by examname else If (@RevenueID = 'HS') if (@type = '1') Select '' as dumm, hsexam as examname, hsexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcohsexam left outer join tbcosection on revenueid = @revenueid and tbcohsexam.hssectionid = tbcosection.sectionid order by examname else -- if (@type = '2') Select '' as dumm, hsexam as examname, hsexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcohsexam left outer join tbcosection on revenueid = @revenueid and tbcohsexam.hssectionid = tbcosection.sectionid where status = 'A' order by examname else If (@RevenueID = 'LB') if (@Type = '1') Select '' as dumm, labexam as examname, labexamid as examid, tbcosection.Labsection as SectionName, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcolabexam left outer join tbcoLabsection tbCoSection on tbcolabexam.labsectionid = tbcosection.Labsectionid order by examname else -- if ( @type = '2') Select '' as dumm, labexam as examname, labexamid as examid, tbcosection.Labsection as SectionName, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcolabexam left outer join tbcoLabsection tbCoSection on tbcolabexam.labsectionid = tbcosection.Labsectionid where status = 'A' order by examname else If (@RevenueID = 'NU') if @type = '1' Select '' as dumm, nucexam as examname, nucexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbconucexam left outer join tbcosection on revenueid = @revenueid and tbconucexam.nucsectionid = tbcosection.sectionid order by examname else -- if @type = '2' Select '' as dumm, nucexam as examname, nucexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbconucexam left outer join tbcosection on revenueid = @revenueid and tbconucexam.nucsectionid = tbcosection.sectionid where status = 'A' order by examname else If (@RevenueID = 'PT') if @type = '1' Select '' as dumm, ptexam as examname, ptexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoptexam left outer join tbcosection on revenueid = 'PT' and tbcoptexam.ptsectionid = tbcosection.sectionid order by examname else -- if @type = '2' Select '' as dumm, ptexam as examname, ptexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoptexam left outer join tbcosection on revenueid = 'PT' and tbcoptexam.ptsectionid = tbcosection.sectionid where status = 'A' or status is null order by examname else If (@RevenueID = 'PA') if @type = '1' Select '' as dumm, rtsexam as examname, rtsexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcortsexam left outer join tbcosection on revenueid = @revenueid and tbcortsexam.rtssectionid = tbcosection.sectionid order by examname else -- if @type = '2' Select '' as dumm, rtsexam as examname, rtsexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcortsexam left outer join tbcosection on revenueid = @revenueid and tbcortsexam.rtssectionid = tbcosection.sectionid where status = 'A' order by examname else If (@RevenueID = 'US') if @type = '1' Select '' as dumm, ultraexam as examname, ultraexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoultraexam left outer join tbcosection on tbcosection.revenueid = @revenueid and tbcoultraexam.ultrasectionid = tbcosection.sectionid order by examname else -- if @type = '2' Select '' as dumm, ultraexam as examname, ultraexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoultraexam left outer join tbcosection on tbcosection.revenueid = @revenueid and tbcoultraexam.ultrasectionid = tbcosection.sectionid where status = 'A' order by examname else If (@RevenueID = 'XR') if @type = '1' Select '' as dumm, xrayexam as examname, xrayexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoxrayexam left outer join tbcosection on tbcosection.revenueid = @revenueid and tbcoxrayexam.xraysectionid = tbcosection.sectionid order by examname else -- if @type = '2' Select '' as dumm, xrayexam as examname, xrayexamid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcoxrayexam left outer join tbcosection on tbcosection.revenueid = @revenueid and tbcoxrayexam.xraysectionid = tbcosection.sectionid where Status = 'A' order by examname else if @type = '1' Select '' as dumm, otherrevenue as examname, otherrevenueid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcootherrevenue left outer join tbcosection on revenueid = @revenueid and tbcootherrevenue.othersectionid = tbcosection.sectionid where othersectionid = @revenueid order by examname else Select '' as dumm, otherrevenue as examname, otherrevenueid as examid, tbcosection.sectionname, ratea, rateb, ratec, rated, ratee, rateG,MedicareType,rateI, (case when fixrate = 'N' then 'NO' else 'YES' End) as fixrate, (case when status = 'I' then 'Inactive' else 'Active' End) as status from tbcootherrevenue left outer join tbcosection on revenueid = @revenueid and tbcootherrevenue.othersectionid = tbcosection.sectionid where othersectionid = @revenueid and status = 'A' order by examname GO USE [Build_File] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Search_Company] Script Date: 9/3/2015 3:08:19 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Bui_Search_Company] ( @SearchCriteria as varchar(60), @type as varchar(1) ) --->Dated 03/08/2012 by sensei AS If (@Type = '1') Select '' as dumm, isnull(a.company,'') as Company, isnull(a.accountnum,'')as AccountNum, isnull(b. description,'') as Description, (case when a.status = 'A' then 'Active' else 'Inactive' End) as Status, isnull(a.cardcode,'') as CardCode from tbcocompany a left outer join tbcobusinessclass b on a.class = b.code order by a.company else If (@Type = '2') Select isnull(a.accountnum,'') as AccountNum, isnull(a.company,'') as Company, isnull(a.housestreet,'') as HouseStreet, isnull(a.barangay,'') as Barangay, isnull(a.zipcode,'') as Zipcode, isnull(a.status,'') as Status,isnull(a.cardcode,'') as CardCode, isnull(a.tin,'') as TIN, isnull(a.vat,'') as VAT, isnull(a.fax,'') as FAX, isnull(a.status,'') as Status, isnull(a.phone,'') as Phone, (case when ltrim(rtrim(isnull(b.description,''))) = '' then '(NONE)' else b.description End) as description, (case when ltrim(rtrim(isnull(c.town,'') + ', ' + isnull(c.province,''))) = ',' then '(NONE)' else isnull(c.town,'') + ', ' + isnull(c.province,'') End) as province, IsNull( a.Class, '' ) as Class , IsNull( a.ContactPerson, '' ) as ContactPerson, IsNull( a.Position, '' ) as Position, IsNull( a.Officer, '' ) as Officer, isnull(CardCode, '') as CardCode, isnull(EmployeeID,'') as EmployeeID, isnull(a.CreditLimit, 0) as CreditLimit, CompanyDiscount, moaStartDate, moaEndDate , moaNumber, comppassword, isnull(a.isEmployee,0) as isEmployee from tbcocompany a left outer join tbcobusinessclass b on a.class = b.code left outer join tbcoaddress c on a.zipcode = c.zipcode where a.accountnum = @searchcriteria else If (@type = '3') Select * from tbcoCompany where ltriM(rtrim(isnull(company,''))) = @SearchCriteria else If (@Type = '4') Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, b.code,a.cardcode, (case when a.status = 'A' then 'Active' else 'Inactive' End) as status from tbcocompany a left outer join tbcobusinessclass b on a.class = b.code order by b.description,a.company else If (@Type = '5') Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code, isnull(a.cardcode,'') as Cardcode from tbcocompany a left outer join tbcobusinessclass b on a.class = b.code order by a.accountnum else If (@Type = '6') Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code,isnull(a.cardcode,'') as Cardcode, (case when a.status = 'A' then 'Active' else 'Inactive' End) as status from tbcocompany a left outer join tbcobusinessclass b on a.class = b.code order by a.company else If (@Type = '7') Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as Accountnum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code, isnull(a.cardcode,'') as CardCode, (case when a.status = 'A' then 'Active' else 'Inactive' End) as status from tbcocompany a left outer join tbcobusinessclass b on a.class = b.code where b.description not like '%advance%' order by a.company ELSE If (@Type = '8') Select '' as dumm ,isnull(a.companyid,'') as CompanyID ,isnull(a.company,'') as Company ,isnull(a.accountnum,'') as Accountnum ,isnull(b.code,'') + ' - ' + isnull(b.description,'') as description ,isnull(b.code,'') as Code ,(case when a.status = 'A' then 'Active' else 'Inactive' End) as status ,isnull(a.cardcode,'') as CardCode FROM tbcocompany a left outer join tbcobusinessclass b ON a.class = b.code WHERE b.description not like '%advance%' AND a.Status = 'A' ORDER BY a.company ELSE If (@Type = '9') Select '' as dumm ,isnull(a.companyid,'') as CompanyID ,isnull(a.company,'') as Company ,isnull(a.accountnum,'') as Accountnum ,isnull(b.code,'') + ' - ' + isnull(b.description,'') as description ,isnull(b.code,'') as Code ,(case when a.status = 'A' then 'Active' else 'Inactive' End) as status ,isnull(a.cardcode,'') as CardCode FROM tbcocompany a left outer join tbcobusinessclass b ON a.class = b.code WHERE b.description not like '%advance%' AND a.Status = 'I' ORDER BY a.company set ANSI_NULLS ON set QUOTED_IDENTIFIER ON ---------------------------- GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Search_Doctor] Script Date: 1/4/2016 2:38:50 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Bui_Search_Doctor] ( @SearchCriterion1 as varchar(30), @SearchCriterion2 as varchar(30), @SearchCriterion3 as varchar(30), @Type as varchar(1) ) AS If (@Type = '1') Select A.*, B.Specialization, B2.Specialization as Specialization2, B2.Specialization as Specialization3, C.Category from tbcoDoctor A Left Outer Join tbcoSpecialization B On A.SpecializationID = B.SpecializationID Left Outer Join tbcoSpecialization B2 On A.SpecializationID2 = B2.SpecializationID Left Outer Join tbCoDocCategory C On A.CategoryID = C.CategoryID where ltrim(rtrim(doctorid)) = @SearchCriterion1 else If (@Type = '2') Select '' as dumm, ltrim(rtrim(isnull(lastname,''))) + ', ' + ltrim(rtrim(isnull(firstname,''))) + ' ' + ltrim(rtrim(isnull(middlename,''))) AS DoctorName, DoctorID FROM tbcoDoctor WHERE ltrim(rtrim(isnull(LastName,''))) = @SearchCriterion1 and ltrim(rtrim(isnull(FirstName,''))) = @SearchCriterion2 and ltrim(rtrim(isnull(MiddleName,''))) = @SearchCriterion3 else If (@Type = '3') Select '' as dumm, ltrim(rtrim(isnull(lastname,''))) + ', ' + ltrim(rtrim(isnull(firstname,''))) + ' ' + ltrim(rtrim(isnull(middlename,''))) as DoctorName, DoctorID, License, taxnumber, PMCC from tbcoDoctor order by Doctorname ELSE IF (@Type = '4') BEGIN IF (@SearchCriterion3 = '') IF (@SearchCriterion1 = '1') --**Active/Inactive** SELECT ltrim(rtrim(isnull(A.lastname,''))) + ', ' + ltrim(rtrim(isnull(A.firstname,''))) + ' ' + ltrim(rtrim(isnull(A.middlename,''))) as DoctorName, A.DoctorID, ISNULL(A.PhilHealthNum,'') PHIC, A.License, A.LicenseExpireDate, A.Taxnumber, Status = (case when A.status = 'I' then 'Inactive' else 'Active' End), A.PHICExpireDate, A.PRCDateExpired, A.PRC, A.S2Expiry, A.S2Number FROM tbCoDoctor A LEFT OUTER JOIN tbCoSpecialization B ON A.SpecializationID = B.SpecializationID LEFT OUTER JOIN tbCoDocCategory C ON A.CategoryID = C.CategoryID WHERE A.Status = @SearchCriterion2 ORDER BY DoctorName ELSE --**All** SELECT ltrim(rtrim(isnull(A.lastname,''))) + ', ' + ltrim(rtrim(isnull(A.firstname,''))) + ' ' + ltrim(rtrim(isnull(A.middlename,''))) as DoctorName, A.DoctorID, ISNULL(A.PhilHealthNum,'') PHIC, A.License, A.LicenseExpireDate, A.Taxnumber, Status = (case when A.status = 'I' then 'Inactive' else 'Active' End), A.PHICExpireDate, A.PRCDateExpired, A.PRC, A.S2Expiry, A.S2Number FROM tbCoDoctor A LEFT OUTER JOIN tbCoSpecialization B ON A.SpecializationID = B.SpecializationID LEFT OUTER JOIN tbCoDocCategory C ON A.CategoryID = C.CategoryID ORDER BY DoctorName ELSE --CATEGORY IF (@SearchCriterion1 = '1') SELECT ltrim(rtrim(isnull(A.lastname,''))) + ', ' + ltrim(rtrim(isnull(A.firstname,''))) + ' ' + ltrim(rtrim(isnull(A.middlename,''))) as DoctorName, A.DoctorID, ISNULL(A.PhilHealthNum,'') PHIC, A.License, A.LicenseExpireDate, A.Taxnumber, Status = (case when A.status = 'I' then 'Inactive' else 'Active' End), A.PHICExpireDate, A.PRCDateExpired, A.PRC, A.S2Expiry, A.S2Number FROM tbCoDoctor A LEFT OUTER JOIN tbCoSpecialization B ON A.SpecializationID = B.SpecializationID LEFT OUTER JOIN tbCoDocCategory C ON A.CategoryID = C.CategoryID WHERE (@SearchCriterion2 = '' OR A.Status = @SearchCriterion2) AND A.CategoryID = @SearchCriterion3 ORDER BY DoctorName ELSE --**All** SELECT ltrim(rtrim(isnull(A.lastname,''))) + ', ' + ltrim(rtrim(isnull(A.firstname,''))) + ' ' + ltrim(rtrim(isnull(A.middlename,''))) as DoctorName, A.DoctorID, ISNULL(A.PhilHealthNum,'') PHIC, A.License, A.LicenseExpireDate, A.Taxnumber, Status = (case when A.status = 'I' then 'Inactive' else 'Active' End), A.PHICExpireDate, A.PRCDateExpired, A.PRC, A.S2Expiry, A.S2Number FROM tbCoDoctor A LEFT OUTER JOIN tbCoSpecialization B ON A.SpecializationID = B.SpecializationID LEFT OUTER JOIN tbCoDocCategory C ON A.CategoryID = C.CategoryID WHERE A.CategoryID = @SearchCriterion3 ORDER BY DoctorName END GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Insert_Item] Script Date: 1/21/2016 5:05:50 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Bui_Insert_Item] (@RevenueID as varchar(2), @ItemID as varchar(7), @SectionID as varchar(3), @ExamName as varchar(45), @RateA as float, @RateB as Float, @RateC as float, @RateD as float, @RateE as float, @RateF as Float, @RateG as Float, @RateH as Float, @Fixrate as varchar(1), @Status as varchar(1), @MedicareType as varchar(1), @common as varchar(1), @RateSD as float=0.00, @IsSenior as bit, @IsSpecial as bit, @AccountCode as varchar(12), @KidneyActive as varchar(1), @DRActive as varchar(1), @HSActive as varchar(1), @ERActive as varchar(1), @ICUActive as varchar(1), @PICUActive as varchar(1), @ORActive as varchar(1), @NurseryActive as varchar(1), @NurseActive as varchar(1), @OPDActive as varchar(1), @PulmoActive as varchar(1), @PTActive as varchar(1), @MedRecActive as varchar(1), @NuclearActive as varchar(1), @SICUActive as varchar(1), @CCUActive as varchar(1), @WCActive as varchar(1), @EndoActive as varchar(1), @EyeActive as varchar(1), @CathLabActive as varchar(1), @rateI as float = 0, @phicCode as varchar(25) = '' ) AS If (@RevenueID = 'AU') Insert into tbcoaudexam(AudExamID, AudSectionID, AudExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'CT') Insert into tbcoctexam(CtExamID, CtSectionID, CtExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'HS') Insert into tbcohsexam(HsExamID, HsSectionID, HsExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH,@Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'LB') Insert into tbcolabexam(LabExamID, LabSectionID, LabExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH,@Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'NU') Insert into tbconucexam(NucExamID, NucSectionID, NucExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'PT') Insert into tbcoptexam(PtExamID, PtSectionID, PtExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH,Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'PA') Insert into tbcortsexam(RtsExamID, RtsSectionID, RtsExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'US') Insert into tbcoultraexam(UltraExamID, UltraSectionID, UltraExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'XR') Insert into tbcoxrayexam(XrayExamID, XraySectionID, XrayExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else Insert into tbCoOtherRevenue(OtherRevenueID, SectionID, OtherRevenue, OtherSectionID, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, KidneyActive, DRActive, HSActive, ERActive, ICUActive, PICUActive, ORActive, NurseryActive, NurseActive, OPDActive, PulmoActive, PTActive, MedRecActive, NuclearActive, SICUActive, CCUActive, WCActive, EndoActive, EyeActive, CathLabActive , rateI, PhicCode) values (@ItemId, @SectionID, @ExamName, @RevenueID, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode, @KidneyActive, @DRActive, @HSActive, @ERActive, @ICUActive, @PICUActive,@ORActive, @NurseryActive, @NurseActive, @OPDActive, @PulmoActive, @PTActive, @MedRecActive, @NuclearActive, @SICUActive, @CCUActive, @WCActive, @EndoActive, @EyeActive, @CathLabActive,@rateI, @phicCode) set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Show_Item_Details] Script Date: 1/22/2016 9:46:08 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_Bui_Show_Item_Details](@RevenueID as varchar(2), @itemID as varchar(5)) AS If (@RevenueID = 'AU') Select * from tbcoaudexam where audexamid = @itemid else If (@RevenueID = 'CT') Select * from tbcoctexam where ctexamid = @itemid else If (@RevenueID = 'HS') Select * from tbcohsexam where hsexamid = @itemid else If (@RevenueID = 'LB') Select * from tbcolabexam where labexamid = @itemid else If (@RevenueID = 'NU') Select * from tbconucexam where nucexamid = @itemid else If (@RevenueID = 'PT') Select * from tbcoptexam where ptexamid = @itemid else If (@RevenueID = 'PA') Select * from tbcortsexam where rtsexamid = @itemid else If (@RevenueID = 'US') Select * from tbcoultraexam where ultraexamid = @itemid else If (@RevenueID = 'XR') Select * from tbcoxrayexam where xrayexamid = @itemid else If (@RevenueID = 'NS') SELECT A.ItemID [ItemID], IsNull(A.ItemName,'') [Description], cast(isnull(B.RateA,0) as money) as RateA, cast(isnull(B.RateB,0) as money) as RateB, cast(isnull(B.RateC,0) as money) as RateC, cast(isnull(B.RateD,0) as money) as RateD, cast(isnull(B.RateE,0) as money) as RateE, cast(isnull(B.opRate,0) as money) as OpRate, a.itemdesc as Itemdesc FROM Inventory..tbInvMaster A INNER JOIN Inventory..tbInvRentalItemsPrice B ON A.ItemID = B.ItemID WHERE A.ItemclassificationID = 'N' and a.itemid = @itemid else Select * from tbcootherrevenue where othersectionid = @revenueid and otherrevenueid = @itemid order by otherrevenue GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_ICD_InsertUpdateICDCode] Script Date: 1/20/2016 10:40:03 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_ICD_InsertUpdateICDCode] @strCode as varchar(20), @Description as varchar(2000), @isCauseOfDeath as varchar(1), @isPrimary as varchar(1), @isNotifiable as varchar(1), @status as bit as if exists(Select * from Build_File..tbICDDiagMain where Code = @strCode) begin Update Build_File..tbICDDiagMain set Description = @Description, AllowCauseOfDeath = @isCauseOfDeath, AllowAsPrimary = @isPrimary, IsNotifiable = @isNotifiable, status = @status where Code = @strCode end else begin Insert into Build_File..tbICDDiagMain (Code, Description, AllowCauseOfDeath, AllowAsPrimary, IsNotifiable, Active) values (@strCode, @Description, @isCauseOfDeath, @isPrimary, @isNotifiable, @status) end GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_ICD_SearchICDCode] Script Date: 1/20/2016 11:32:45 AM ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_ICD_SearchICDCode] @strCode as varchar(20) as Select code, ltrim(rtrim(Description)) Description, isnull(AllowCauseOfDeath,'Y') CauseOfDeath, isnull(AllowAsPrimary, 'Y') isPrimary, isnull(IsNotifiable, 'N') isNotifiable, ISNULL(Status, 1) AS Status From Build_File..tbICDDiagMain where Code = @strCode GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Update_Item_Date] Script Date: 1/19/2016 5:33:18 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Bui_Update_Item_Date] @RevenueID as varchar(2), @ItemID as varchar(7), @RateADate as varchar(1), @RateBDate as varchar(1), @RateCDate as varchar(1), @RateDDate as varchar(1), @RateEDate as varchar(1), @RateFDate as varchar(1), @RateGDate as varchar(1) AS Declare @DateA as datetime Declare @DateB as datetime Declare @DateC as datetime Declare @DateD as datetime Declare @DateE as datetime Declare @DateF as datetime Declare @DateG as datetime if @RateADate = '1' begin set @DateA = getdate() end else begin set @DateA = null end if @RateBDate = '1' begin set @DateB = getdate() end else begin set @DateB = null end if @RateCDate = '1' begin set @DateC = getdate() end else begin set @DateC = null end if @RateDDate = '1' begin set @DateD = getdate() end else begin set @DateD = null end if @RateEDate = '1' begin set @DateE = getdate() end else begin set @DateE = null end if @RateFDate = '1' begin set @DateF = getdate() end else begin set @DateF = null end if @RateGDate = '1' begin set @DateG = getdate() end else begin set @DateG = null end If (@RevenueID = 'AU') Update tbcoaudexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where audexamid = @itemid else If (@RevenueID = 'CT') Update tbcoctexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where ctexamid = @itemid else If (@RevenueID = 'HS') Update tbcohsexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where hsexamid = @itemid else If (@RevenueID = 'LB') Update tbcolabexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where labexamid = @itemid else If (@RevenueID = 'NU') Update tbconucexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where nucexamid = @itemid else If (@RevenueID = 'PT') Update tbcoptexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where ptexamid = @itemid else If (@RevenueID = 'PA') Update tbcortsexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where rtsexamid = @itemid else If (@RevenueID = 'US') Update tbcoultraexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where ultraexamid = @itemid If (@RevenueID = 'XR') Update tbcoxrayexam set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where xrayexamid = @itemid else Update tbcootherrevenue set RateADate = @DateA, RateBDate = @DateB, RateCDate = @DateC, RateDDate = @DateD, RateEDate = @DateE, RateFDate = @DateF, RateGDate = @DateG where otherrevenueid = @itemid and othersectionid = @revenueid GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Search_Doctor] Script Date: 1/22/2016 11:30:07 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Bui_Search_Doctor] ( @SearchCriterion1 as varchar(30), @SearchCriterion2 as varchar(30), @SearchCriterion3 as varchar(30), @Type as varchar(1) ) AS If (@Type = '1') Select A.*, B.Specialization, B2.Specialization as Specialization2, B2.Specialization as Specialization3, C.Category from tbcoDoctor A Left Outer Join tbcoSpecialization B On A.SpecializationID = B.SpecializationID Left Outer Join tbcoSpecialization B2 On A.SpecializationID2 = B2.SpecializationID Left Outer Join tbCoDocCategory C On A.CategoryID = C.CategoryID where ltrim(rtrim(doctorid)) = @SearchCriterion1 else If (@Type = '2') Select '' as dumm, ltrim(rtrim(isnull(lastname,''))) + ', ' + ltrim(rtrim(isnull(firstname,''))) + ' ' + ltrim(rtrim(isnull(middlename,''))) AS DoctorName, DoctorID FROM tbcoDoctor WHERE ltrim(rtrim(isnull(LastName,''))) = @SearchCriterion1 and ltrim(rtrim(isnull(FirstName,''))) = @SearchCriterion2 and ltrim(rtrim(isnull(MiddleName,''))) = @SearchCriterion3 else If (@Type = '3') Select '' as dumm, ltrim(rtrim(isnull(lastname,''))) + ', ' + ltrim(rtrim(isnull(firstname,''))) + ' ' + ltrim(rtrim(isnull(middlename,''))) as DoctorName, DoctorID, License, taxnumber, PMCC from tbcoDoctor order by Doctorname ELSE IF (@Type = '4') BEGIN SELECT ltrim(rtrim(isnull(A.lastname,''))) + ', ' + ltrim(rtrim(isnull(A.firstname,''))) + ' ' + ltrim(rtrim(isnull(A.middlename,''))) as DoctorName, A.DoctorID, ISNULL(A.PhilHealthNum,'') PHIC, A.License, A.LicenseExpireDate, A.Taxnumber, Status = (case when A.status = 'I' then 'Inactive' else 'Active' End), A.PHICExpireDate, A.PRCDateExpired, A.PRC, A.S2Expiry, A.S2Number, C.Category, B.Specialization, A.Department FROM tbCoDoctor A LEFT OUTER JOIN tbCoSpecialization B ON A.SpecializationID = B.SpecializationID LEFT OUTER JOIN tbCoDocCategory C ON A.CategoryID = C.CategoryID WHERE (@SearchCriterion2 = '' OR A.Status = @SearchCriterion2) AND (@SearchCriterion3 = '' OR A.CategoryID = @SearchCriterion3) ORDER BY DoctorName END GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Build_SearchDiscountRates] Script Date: 1/25/2016 5:27:15 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Build_SearchDiscountRates] @DiscountType as varchar(2) AS Select isnull(A.DiscountType,'') as DiscountType, isnull(B.ItemName,'') as Department, isnull(A.Rate,0) as Rate, isnull(A.NetPHIC,0) as NetPHIC, isnull(A.DiscountCode,'') as DiscCode, isnull(A.AccountNum,'') as AccountNum, isnull(A.RevenueID,'') as DepartmentID, isnull(D.ItemName,'') as Discount, isnull(A.SubType, '') as SubType, isnull(C.Company,'') as Company From tbCoDiscountRates A Left Outer Join Build_File..tbCoRevenueCode B On A.RevenueID = B.RevenueID Left Outer Join Build_File..tbCoCompany C On A.AccountNum = C.AccountNum Left Outer Join tbCoRevenueCode D On A.DiscountCode = D.RevenueID Where DiscountType = @DiscountType Order By Department GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_ICD_InsertUpdateICDCode] Script Date: 2/2/2016 4:47:43 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_ICD_InsertUpdateICDCode] @strCode as varchar(20), @Description as varchar(2000), @isCauseOfDeath as varchar(1), @isPrimary as varchar(1), @isNotifiable as varchar(1), @status as bit as if exists(Select * from Build_File..tbICDDiagMain where Code = @strCode) begin Update Build_File..tbICDDiagMain set Description = @Description, AllowCauseOfDeath = @isCauseOfDeath, AllowAsPrimary = @isPrimary, IsNotifiable = @isNotifiable, Status = @status where Code = @strCode end else begin Insert into Build_File..tbICDDiagMain (Code, Description, AllowCauseOfDeath, AllowAsPrimary, IsNotifiable, Status) values (@strCode, @Description, @isCauseOfDeath, @isPrimary, @isNotifiable, @status) end GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_ICD_SearchICDCode] Script Date: 2/2/2016 4:36:18 PM ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_ICD_SearchICDCode] @strCode as varchar(20) as Select code, ltrim(rtrim(Description)) Description, isnull(AllowCauseOfDeath,'Y') CauseOfDeath, isnull(AllowAsPrimary, 'Y') isPrimary, isnull(IsNotifiable, 'N') isNotifiable, ISNULL(Status, 1) AS Status From Build_File..tbICDDiagMain where Code = @strCode GO USE [Patient_Data] GO /****** Object: StoredProcedure [dbo].[sp_med_MedicalCert] Script Date: 2/4/2016 6:25:09 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[sp_med_MedicalCert] @IDNum As VarChar(10), @RecordsID as varchar(10) AS --Delete tbMedRecCertificateDx; if isnumeric(@IDNum) = 1 Begin Select Top 1 Patient.IDNum, isnull(Master.LastName,'') as LastName, isnull(Master.FirstName,'') as FirstName, isnull(Master.MiddleName,'') as MiddleName, Case when master.birthdate is not null then isnull(datediff(year, Master.BirthDate, getdate()),0) When isnumeric(Master.Age) = 1 then Master.Age Else 0 End as Age, Case isnull(Master.Sex,'') When 'F' Then 'female' Else 'male' End AS Sex, Case isnull(Master.CivilStatus,'') When '0' Then 'child' When '1' Then 'single' When '2' Then 'married' When '3' Then 'widow' When '4' Then 'separated' When '5' Then 'divorced' Else '' End AS CivilStatus, isnull(Nation.Nationality,'') as Nationality, isnull(Master.HouseStreet,'') as HouseStreet, isnull(Master.Barangay,'') as Barangay, isnull(Town.Town,'') as Town, isnull(Town.Province,'') as Province, isnull(Master.TelNum,'') as TelNum, isnull(Master2.Employer,'') as Employer, convert(varchar(25),Patient.AdmDate,107) as AdmDate, Convert(varchar(25),Patient.DcrDate,107) as DcrDate, Case When Patient.HospPlan = 'S' then RTrim(IsNull(Resident.FirstName,'')) + ' ' + Left(RTrim(IsNull(Resident.MiddleName,'')),1) + Case When Left(RTrim(IsNull(Resident.MiddleName,'')),1) = '' then '' Else '. ' End + RTrim(IsNull(Resident.LastName,'')) + ' ' + isnull(Resident.Title,'') Else RTrim(IsNull(Doctor.FirstName,'')) + ' ' + Left(RTrim(IsNull(Doctor.MiddleName,'')),1) + Case When Left(RTrim(IsNull(Doctor.MiddleName,'')),1) = '' then '' Else '. ' End + RTrim(IsNull(Doctor.LastName,'')) + ' ' + isnull(Doctor.Title,'') End As DrName, isnull(History.FinalDiagnosis,'') as FinalDiagnosis, isnull(History.Procedures,'') as [Procedure], isnull(Master.Occupation,'') as Occupation, isnull(P2.Remarks,'') as Remarks, isnull(Doctor.PTR,'') as PTR, isnull(Doctor.License,'') as License, isnull(Service.Service,'') as Service, isnull((select top 1 isnull(pass.Firstname,'') + ' ' + isnull(pass.Middlename,'') + ' ' + isnull(Pass.LastName,'') from Password..tbPasswordMain Pass where pass.employeeID = @RecordsID),'') as recordOfficer, Case Patient.ResultID When '1' then ': RECOVERED' When '2' then ': IMPROVED' When '3' then ': UNINPROVED' When '4' then ': DIED' When '5' then ': CHECK_UP' Else '' End as Result, Case When Patient.HospPlan = 'S' then 'Resident/Fellow' Else 'Attending Physician' End as DrTitle FROM Patient_Data..tbPatient Patient Left Outer Join Patient_Data..tbMaster Master On Patient.HospNum = Master.HospNum Left Outer Join Patient_Data..tbPatient2 P2 On Patient.IdNum=P2.IdNum Left Outer Join Patient_Data..tbMaster2 Master2 on Patient.HospNum = Master2.HospNum Left Outer Join Build_File..tbCoNationality Nation On Master2.NationalityID = Nation.NationalityID Left Outer Join Build_File..tbCoAddress Town On Master.ZipCode = Town.ZipCode Left Outer Join Build_File..tbCoDoctor Doctor On Patient.AttendingDr1 = Doctor.DoctorID Left Outer Join Build_File..tbCoDoctor Resident On Patient.ResidentDr1 = Resident.DoctorID Left Outer Join Patient_Data..tbPatientHistory History On Patient.IDNum = History.IDNum Left Outer Join Build_File..tbCoService Service on Patient.ServiceID= Service.ServiceID Where Patient.IDNum = @IDNum; END ELSE BEGIN Select Top 1 Patient.IDNum, isnull(Master.LastName,'') as LastName, isnull(Master.FirstName,'') as FirstName, isnull(Master.MiddleName,'') as MiddleName, Case when master.birthdate is not null then isnull(datediff(year, Master.BirthDate, getdate()),0) When isnumeric(Master.Age) = 1 then Master.Age Else 0 End as Age, Case isnull(Master.Sex,'') When 'F' Then 'female' Else 'male' End AS Sex, Case isnull(Master.CivilStatus,'') When '0' Then 'child' When '1' Then 'single' When '2' Then 'married' When '3' Then 'widow' When '4' Then 'separated' When '5' Then 'divorced' Else '' End AS CivilStatus, isnull(Nation.Nationality,'') as Nationality, isnull(Master.HouseStreet,'') as HouseStreet, isnull(Master.Barangay,'') as Barangay, isnull(Town.Town,'') as Town, isnull(Town.Province,'') as Province, isnull(Master.TelNum,'') as TelNum, isnull(Master2.Employer,'') as Employer, convert(varchar(25),Patient.AdmDate,107) as AdmDate, Convert(varchar(25),Patient.DcrDate,107) as DcrDate, RTrim(IsNull(Doctor.FirstName,'')) + ' ' + RTrim(IsNull(Doctor.MiddleName,'')) + ' ' + RTrim(IsNull(Doctor.LastName,'')) As DrName, isnull(History.Findings,'') as FinalDiagnosis, isnull(History.Medications,'') as [Procedure], isnull(Master.Occupation,'') as Occupation, isnull(Patient.Remarks,'') as Remarks, isnull(Doctor.PTR,'') as PTR, isnull(Doctor.License,'') as License, isnull(Service.Service,'') as Service, isnull((select top 1 isnull(pass.Firstname,'') + ' ' + isnull(pass.Middlename,'') + ' ' + isnull(Pass.LastName,'') from Password..tbPasswordMain Pass where pass.employeeID = @RecordsID),'') as recordOfficer, '' as Result, 'Resident/Fellow' as DrTitle FROM Patient_Data..tbOutPatient Patient Left Outer Join Patient_Data..tbMaster Master On Patient.HospNum = Master.HospNum Left Outer Join Patient_Data..tbMaster2 Master2 on Patient.HospNum = Master2.HospNum Left Outer Join Build_File..tbCoNationality Nation On Master2.NationalityID = Nation.NationalityID Left Outer Join Build_File..tbCoAddress Town On Master.ZipCode = Town.ZipCode Left Outer Join Build_File..tbCoDoctor Doctor On Patient.DoctorID1 = Doctor.DoctorID Left Outer Join Patient_Data..tbOutPatientHistory History On Patient.IDNum = History.IDNum Left Outer Join Build_File..tbCoService Service on Patient.ServiceID1 = Service.ServiceID Where Patient.IDNum = @IDNum; END GO USE [Build_FIle] GO /****** Object: StoredProcedure [dbo].[sp_Bui_Insert_Item] Script Date: 2/4/2016 11:32:00 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Bui_Insert_Item] (@RevenueID as varchar(2), @ItemID as varchar(7), @SectionID as varchar(3), @ExamName as varchar(45), @RateA as float, @RateB as Float, @RateC as float, @RateD as float, @RateE as float, @RateF as Float, @RateG as Float, @RateH as Float, @Fixrate as varchar(1), @Status as varchar(1), @MedicareType as varchar(1), @common as varchar(1), @RateSD as float=0.00, @IsSenior as bit, @IsSpecial as bit, @AccountCode as varchar(12), @KidneyActive as varchar(1), @DRActive as varchar(1), @HSActive as varchar(1), @ERActive as varchar(1), @ICUActive as varchar(1), @PICUActive as varchar(1), @ORActive as varchar(1), @NurseryActive as varchar(1), @NurseActive as varchar(1), @OPDActive as varchar(1), @PulmoActive as varchar(1), @PTActive as varchar(1), @MedRecActive as varchar(1), @NuclearActive as varchar(1), @SICUActive as varchar(1), @CCUActive as varchar(1), @WCActive as varchar(1), @EndoActive as varchar(1), @EyeActive as varchar(1), @CathLabActive as varchar(1), @rateI as float = 0, @phicCode as varchar(25) = '' ) AS If (@RevenueID = 'AU') Insert into tbcoaudexam(AudExamID, AudSectionID, AudExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'CT') Insert into tbcoctexam(CtExamID, CtSectionID, CtExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'HS') Insert into tbcohsexam(HsExamID, HsSectionID, HsExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH,@Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'LB') Insert into tbcolabexam(LabExamID, LabSectionID, LabExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH,@Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'NU') Insert into tbconucexam(NucExamID, NucSectionID, NucExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'PT') Insert into tbcoptexam(PtExamID, PtSectionID, PtExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH,Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'PA') Insert into tbcortsexam(RtsExamID, RtsSectionID, RtsExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'US') Insert into tbcoultraexam(UltraExamID, UltraSectionID, UltraExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else If (@RevenueID = 'XR') Insert into tbcoxrayexam(XrayExamID, XraySectionID, XrayExam, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, rateI) values (@ItemId, @SectionID, @ExamName, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode,@rateI) else Insert into tbCoOtherRevenue(OtherRevenueID, SectionID, OtherRevenue, OtherSectionID, RateA, RateB, RateC, RateD, RateE, RateF, RateG, RateH, Fixrate, Status, MedicareType, common, RateSD, IsSenior, IsSpecial, AccountCode, KidneyActive, DRActive, HSActive, ERActive, ICUActive, PICUActive, ORActive, NurseryActive, NurseActive, OPDActive, PulmoActive, PTActive, MedRecActive, NuclearActive, SICUActive, CCUActive, WCActive, EndoActive, EyeActive, CathLabActive , rateI, PhicCode) values (@ItemId, @SectionID, @ExamName, @RevenueID, @RateA, @RateB, @RateC, @RateD, @RateE, @RateF, @RateG, @RateH, @Fixrate, @Status, @MedicareType, @common, @RateSD, @IsSenior, @IsSpecial, @AccountCode, @KidneyActive, @DRActive, @HSActive, @ERActive, @ICUActive, @PICUActive,@ORActive, @NurseryActive, @NurseActive, @OPDActive, @PulmoActive, @PTActive, @MedRecActive, @NuclearActive, @SICUActive, @CCUActive, @WCActive, @EndoActive, @EyeActive, @CathLabActive,@rateI, @phicCode) set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO