IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'SP_Bui_UpdateItem')
EXEC ('CREATE PROC dbo.SP_Bui_UpdateItem AS SELECT 1')
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SP_Bui_UpdateItem]
(@RevenueID as varchar(2), @ItemID as varchar(5), @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')
    update build_file..tbcoaudexam 
				set 
					AudSectionID=@SectionID,
					AudExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where audexamid = @itemid
else
If (@RevenueID = 'CT')
    update build_file..tbcoctexam 
				set 
					CTSectionID=@SectionID,
					CTExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where ctexamid = @itemid
else
If (@RevenueID = 'HS')
    update build_file..tbcohsexam 
				set 
					HSSectionID=@SectionID,
					HSExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where hsexamid = @itemid
If (@RevenueID = 'LB')
   update build_file..tbcolabexam 
				set 
					LABSectionID=@SectionID,
					LABExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where LABexamid = @itemid
else
If (@RevenueID = 'NU')
       update build_file..tbconucexam 
				set 
					NUCSectionID=@SectionID,
					NUCExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where NUCexamid = @itemid
else
If (@RevenueID = 'PT')
       update build_file..tbcoptexam 
				set 
					PTSectionID=@SectionID, 
					PTExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where PTexamid = @itemid
else
If (@RevenueID = 'PA')
      update build_file..tbcortsexam 
				set 
					RTSSectionID=@SectionID,
					RTSExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where RTSexamid = @itemid
else
If (@RevenueID = 'US')
       update build_file..tbcoultraexam 
				set 
					ULTRASectionID=@SectionID,
					ULTRAExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where ULTRAexamid = @itemid
else
If (@RevenueID = 'XR')
       update build_file..tbcoxrayexam 
				set
					XRAYSectionID=@SectionID,
					XRAYExam=@ExamName, 
					RateA=@RateA,
					RateB=@RateB, 
					RateC=@RateC, 
					RateD=@RateD, 
					RateE=@RateE, 
					RateF=@RateF, 
					RateG=@RateG, 
					RateH=@RateH, 
					Fixrate=@Fixrate, 
					Status=@Status, 
					MedicareType=@MedicareType, 
					common=@common, 
					RateSD=@RateSD, 
					IsSenior=@IsSenior, 
					IsSpecial=@IsSpecial, 
					AccountCode=@AccountCode, 
					rateI=@rateI
				where XRAYexamid = @itemid
else
    Update tbcootherrevenue
			set 
				OtherRevenue=@ExamName, 
				RateA=@RateA, 
				RateB=@RateB, 
				RateC=@RateC, 
				RateD=@RateD, 
				RateE=@RateE, 
				RateF=@RateF, 
				RateG=@RateG, 
				RateH=@RateH, 
				Fixrate=@Fixrate, 
				Status=@Status, 
				MedicareType=@MedicareType, 
				common=@common, 
				RateSD=@RateSD, 
				IsSenior=@IsSenior, 
				IsSpecial=@IsSpecial, 
				AccountCode=@AccountCode,
				KidneyActive=@KidneyActive, 
				DRActive=@DRActive, 
				HSActive=@HSActive, 
				ERActive=@ERActive, 
				ICUActive=@ICUActive, 
				PICUActive=@PICUActive, 
				ORActive=@ORActive, 
				NurseryActive=@NurseryActive, 
				NurseActive=@NurseActive, 
				OPDActive=@OPDActive, 
				PulmoActive=@PulmoActive, 
				PTActive=@PTActive, 
				MedRecActive=@MedRecActive, 
				NuclearActive=@NuclearActive, 
				SICUActive=@SICUActive, 
				CCUActive=@CCUActive, 
				WCActive=@WCActive, 
				EndoActive=@EndoActive, 
				EyeActive=@EyeActive, 
				CathLabActive=@CathLabActive, 
				rateI=@rateI
			     where otherrevenueid = @itemid and othersectionid = @revenueid

alter table build_file..tbcoroomamenities
add Quantity int null
go
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Bui_RoomAmenities')
EXEC ('CREATE PROC dbo.sp_Bui_RoomAmenities AS SELECT 1')
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[sp_Bui_RoomAmenities]
@RoomID as varchar(10),
@AmenityID as varchar(10),
@Status as bit,
@Quantity as int

as
IF (Exists(Select AmenityID from Build_file..tbCoRoomAmenities Where AmenityID=@AmenityID and RoomID=@RoomID))
begin
update build_file..tbcoRoomAmenities
set RoomID=@RoomID,
	AmenityID=@AmenityID,
	Status=@Status,
	Quantity=@Quantity
where AmenityID=@AmenityID and RoomID=@RoomID
end;
else
begin
insert into build_file..tbcoroomAmenities
(RoomID,AmenityID,Status,Quantity)
values
(@RoomID,@AmenityID,@Status,@Quantity)
end;
go
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Bui_BatchUpdate')
EXEC ('CREATE PROC dbo.sp_Bui_BatchUpdate AS SELECT 1')
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[sp_Bui_BatchUpdate]
 	  @RevenueID as varchar(2),
 	  @ItemID as varchar(5), 
	  @RateA as float, 
	  @RateB as Float, 
	  @RateC as float, 
	  @RateD as float, 
	  @RateE as float, 
	  @RateF as float,
 	  @RateG as float, 
	  @RateH as float,
	  @RateI as float,
	  @RateSD as float = 0.00

	AS

	If (@RevenueID = 'AU')

		Update tbcoaudexam

			set	RateA=@RateA,
				RateB=@RateB,
				RateC=@RateC,
				RateD=@RateD,
				RateE=@RateE,
				RateF=@RateF,
				RateG=@RateG,
				RateH=@RateH
			where audexamid = @itemid
	else

	If (@RevenueID = 'CT')

		Update tbcoctexam

			set RateA=@RateA,
				RateB=@RateB,
				RateC=@RateC,
				RateD=@RateD,
				RateE=@RateE,
				RateF=@RateF,
				RateG=@RateG,
				RateH=@RateH,
				RateSD = @RateSD
			where ctexamid = @itemid
	else

	If (@RevenueID = 'HS')

		Update tbcohsexam

			set  RateA=@RateA,
				 RateB=@RateB,
				 RateC=@RateC,
				 RateD=@RateD,
				 RateE=@RateE,
				 RateF=@RateF,
				 RateG=@RateG,
				 RateH=@RateH
			where hsexamid = @itemid
	else

	If (@RevenueID = 'LB')

		Update tbcolabexam
			
			set  RateA=@RateA,
				 RateB=@RateB,
				 RateC=@RateC,
				 RateD=@RateD,
				 RateE=@RateE,
				 RateF=@RateF,
				 RateG=@RateG,
				 RateH=@RateH
			where labexamid = @itemid
	else

	If (@RevenueID = 'NU')

		Update tbconucexam

			set  RateA=@RateA,
				 RateB=@RateB,
				 RateC=@RateC,
				 RateD=@RateD,
				 RateE=@RateE,
				 RateF=@RateF,
				 RateG=@RateG,
				 RateH=@RateH
			where nucexamid = @itemid
	else

	If (@RevenueID = 'PT')

		Update tbcoptexam

			set RateA=@RateA,
				RateB=@RateB,
				RateC=@RateC,
				RateD=@RateD,
				RateE=@RateE,
				RateF=@RateF,
				RateG=@RateG,
				RateH=@RateH,
				RateSD = @RateSD
			where ptexamid = @itemid
	else

	If (@RevenueID = 'PA')

		Update tbcortsexam

			set RateA=@RateA,
				RateB=@RateB,
				RateC=@RateC,
				RateD=@RateD,
				RateE=@RateE,
				RateF=@RateF,
				RateG=@RateG,
				RateH=@RateH,
				RateI=@RateI
			where rtsexamid = @itemid
	else

	If (@RevenueID = 'US')

		Update tbcoultraexam
		
			set RateA=@RateA,
				RateB=@RateB,
				RateC=@RateC,
				RateD=@RateD,
				RateE=@RateE,
				RateF=@RateF,
				RateG=@RateG,
				RateH=@RateH,
				RateI=@RateI,
				RateSD = @RateSD
			where ultraexamid = @itemid
	else

	If (@RevenueID = 'XR')

		Update tbcoxrayexam
		
			set RateA=@RateA,
				RateB=@RateB,
				RateC=@RateC,
				RateD=@RateD,
				RateE=@RateE,
				RateF=@RateF,
				RateG=@RateG,
				RateH=@RateH,
				RateI=@RateI,
				RateSD = @RateSD
			where xrayexamid = @itemid
	else

		Update tbcootherrevenue
			
			set RateA=@RateA,
				RateB=@RateB,
				RateC=@RateC,
				RateD=@RateD,
				RateE=@RateE,
				RateF=@RateF,
				RateG=@RateG,
				RateH=@RateH,
				RateI=@RateI
			where otherrevenueid = @itemid and othersectionid = @revenueid
alter table TbBuildUserSettings
add sShowUserValidation bit null
GO
alter table TbBuildUserSettings
add sRoomStatus bit null
GO
alter table TbBuildUserSettings
add sDeposittype bit null
GO
alter table TbBuildUserSettings
add sHospitalList bit null
GO
alter table TbBuildUserSettings
add sICDCode bit null
GO
alter table TbBuildUserSettings
add sCreditMemo bit null
GO
alter table TbBuildUserSettings
add sCausesofConsultation bit null
GO
alter table TbBuildUserSettings
add sHoliday bit null
GO
alter table TbBuildUserSettings
add sShowUserValidation bit null
go
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'spBuild_UserSettingsNew')
EXEC ('CREATE PROC dbo.spBuild_UserSettingsNew AS SELECT 1')
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER Procedure [dbo].[spBuild_UserSettingsNew]
@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 
	  

  FROM BUILD_FILE.dbo.TbBuildUserSettings

where employeeid = @Employeeid
go

alter table build_file..tbcoroom
add WithAmenities varchar(1)
GO
create table tbCoRoomAmenities
(
RoomID varchar(10),
AmenityID varchar(10),
Status bit ,
Quantity int null
)

GO
create table tbCoAmenities
(
AmenityID varchar(10),
Amenity varchar (50)
)

GO