USE [Build_File]
GO 
SET ANSI_NULLS ON
GO
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 ,
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,
isAllowUploadDoctor [isAllowUploadDoctor]

FROM BUILD_FILE.dbo.TbBuildUserSettings

where employeeid = @Employeeid
GO



USE [Build_File]
GO
/****** Object:  StoredProcedure [dbo].[sp_Build_UserSettings]    Script Date: 4/2/2014 12:51:00 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 varchar(1) = '0',
@ICDcode varchar(1) = '0',
@CreditMemo varchar(1) = '0',
@CausesofConsultation varchar(1) = '0',
@Holiday varchar(1) = '0',
@UserValidation varchar(1) = '0',
@Points varchar(1)= '0',
@InputTax [bit],
@PhicNumber [bit],
@BankAccount [bit],
@DoctorRate [bit],
@DoctorUtilities [bit],
@Amenities [bit],
@LogoandImage [bit],
@sBatchUpdate [bit],
@sClinicInfo [bit],
@sDocAccess [bit],
@sClinicAddinfo [bit],
@isAllowUploadDoctor [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
	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)
		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)
End;
GO
USE [Build_File]
GO
/****** Object:  StoredProcedure [dbo].[sp_Bui_Insert_Revenue_Center_New]    Script Date: 4/10/2014 10:17:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Bui_Insert_Revenue_Center_New] --->Dated 02/08/2012 by 

(	 @RevenueID 		as varchar(3),

	 @Itemname 		as varchar(45),

	 @lgrp 			as varchar(1),

	 @reportrevenue 	as varchar(1),

	 @medicaretype 	as varchar(1),

	 @drcr			as varchar(1),

	 @fix 			as varchar(1),

	 @recordstatus 		as varchar(1),

	 @rwith 		as varchar(1),

	 @billingactive	 	as varchar(1),

	 @cashieractive 	as varchar(1),

	 @nurseactive 		as varchar(1),

	 @parsactive 		as varchar(1),

	 @medicareactive 	as varchar(1),	

	 @kidneyactive 		as varchar(1),

	 @dractive	 	as varchar(1),

	 @eractive 		as varchar(1),

	 @icuactive 		as varchar(1),

	 @nuactive 		as varchar(1),

	 @oractive 		as varchar(1),

	 @opdactive 		as varchar(1),

	 @PAactive 		as varchar(1) = null,

	 @PrepaidActive	as varchar(1) = null,

	@EndoActive		as varchar(1) = null,

	@DebitCode		as varchar(15) = null,

	@CreditCode		as varchar(15) = null,

	@AccountName		as varchar(30) = null,

	@NurseryActive		as varchar(1) = null,

	@PICUActive		as varchar(1) = null,

	@CVUActive		as varchar(1) = null,

	@HemaActive		as varchar(1) = null,

	@HSActive		as varchar(1) = null,

	@StationID		as varchar(4) = null,

	@CCUActive	    as varchar(1) = null,
	
	@IsPatientCard as bit,

	@StockCardRevenueID as varchar(5) = ''
	
	
)

AS

insert into tbcorevenuecode(RevenueID, Itemname, lgrp,

	  reportrevenue, medicaretype, drcr,		

	  fix, recordstatus, rwith, billingactive,

	  cashieractive, nurseactive, parsactive,

	  medicareactive, kidneyactive, dractive,

	  eractive, icuactive, nuactive, oractive,opdactive,PAactive,

	  PrepaidActive, EndoActive, GLInDebitCode, GLInCreditCode, AccountName,

	  NurActive, PICUActive, CVUActive, HemaActive, HSActive, StationID,

CCUActive, IsPatientCard, StockCardRevenueID )

values   (@RevenueID,  @Itemname, @lgrp,

	 @reportrevenue, @medicaretype, @drcr,		

	 @fix, @recordstatus, @rwith, @billingactive,

	 @cashieractive,  @nurseactive, @parsactive,

	 @medicareactive, @kidneyactive, @dractive,

	 @eractive, @icuactive, @nuactive, @oractive,@opdactive,@PAactive,

@PrepaidActive, @EndoActive, @DebitCode, @CreditCode, @AccountName,

	 @NurseryActive, @PICUActive, @CVUActive, @HemaActive, @HSActive,

@StationID, @CCUActive, @IsPatientCard, @StockCardRevenueID)
GO
USE [Build_File]
GO
/****** Object:  StoredProcedure [dbo].[sp_Bui_Update_Revenue_Center_New]    Script Date: 4/10/2014 10:21:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Bui_Update_Revenue_Center_New] --->Dated 02/08/2012 by sensei

(	 @RevenueID 		as varchar(3),

	 @Itemname 		as varchar(45),

	 @lgrp 			as varchar(1),

	 @reportrevenue 	as varchar(1),

	 @medicaretype 	as varchar(1),

	 @drcr			as varchar(1),

	 @fix 			as varchar(1),

	 @recordstatus 		as varchar(1),

	 @rwith 			as varchar(1),

	 @billingactive	 	as varchar(1),

	 @cashieractive 	as varchar(1),

	 @nurseactive 		as varchar(1),

	 @parsactive 		as varchar(1),

	 @medicareactive 	as varchar(1),	

	 @kidneyactive 	as varchar(1),

	 @dractive	 	as varchar(1),

	 @eractive 		as varchar(1),

	 @icuactive 		as varchar(1),

	 @nuactive 		as varchar(1),

	 @oractive 		as varchar(1),

	 @opdactive 		as varchar(1),

	 @PAactive		as varchar(1) = null,

	 @WithProfFee as bit = null,

	 @PrepaidActive	as varchar(1) = null,

	 @EndoActive as varchar(1) = null,

	 @DebitCode		as varchar(15) = null,

	 @CreditCode		as varchar(15) = null,

	 @AccountName 	as varchar(30) = null,

	 @NurseryActive		as varchar(1) = null,

	 @PICUActive		as varchar(1) = null,

	 @CVUActive		as varchar(1)= null,

	 @HemaActive		as varchar(1) = null,

	 @HSActive		as varchar(1) = null,

	 @StationID		as varchar(4) = null,

	 @CCUActive      as varchar(1) = null,
	
@IsPatientCard  as bit,

	 @StockCardRevenueID as varchar(5) = ''

)

AS

Update 	 tbcorevenuecode

set   Itemname = @itemname,

	  lgrp = @lgrp,

	  reportrevenue =  @reportrevenue,

	  medicaretype = @medicaretype,

	  drcr = @drcr,		

	  fix = @fix,

	  recordstatus = @recordstatus,

	  rwith = @rwith,

	  billingactive =  @billingactive,

	  cashieractive =  @cashieractive,

	  nurseactive = @nurseactive,

	  parsactive = @parsactive,

	  medicareactive =  @medicareactive,

	  kidneyactive =  @kidneyactive,

	  dractive = @dractive,

	  eractive = @eractive,

	  icuactive =  @icuactive,

nuactive =  @nuactive,

oractive = @oractive,

	  opdactive = @opdactive,

	  PAactive = @PAactive,

	  WithProfFee = @WithProfFee,

	  PrepaidActive = @PrepaidActive,

	  EndoActive = @EndoActive,

	  GLINDebitCode = @DebitCode,

	  GLINCreditCode = @CreditCode,

	  AccountName = @AccountName,

	  NurActive = @NurseryActive,

	  PICUActive = @PICUActive,

	  CVUActive = @CVUActive,

	  HemaActive = @HemaActive,

	  HSActive = @HSActive,

	  StationID = @StationID,

	  CCUActive = @CCUActive,
	
	  IsPatientCard = @IsPatientCard,

	  StockCardRevenueID = @StockCardRevenueID

where     revenueid = @RevenueID
GO
USE [Build_File]
GO
/****** Object:  StoredProcedure [dbo].[sp_Bui_Show_Revenue_Center_Details]    Script Date: 4/10/2014 10:34:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Bui_Show_Revenue_Center_Details](@revenueid as varchar(3))
AS
select * from tbcorevenuecode where revenueid = @revenueid
GO
 
/****** Object:  StoredProcedure [dbo].[sp_Insert_Update_AdmPackageItems]    Script Date: 02/10/2014 11:55:36 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Insert_Update_AdmPackageItems]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE [dbo].[sp_Insert_Update_AdmPackageItems]
	@PackageID [varchar](5),
	@ExamID [varchar](5),
	@RevenueID [varchar](2),
	@Quantity [int],
	@Amount [float],
	@Gender [varchar](1),
	@Status [varchar](1),
	@TotalGross [float] = null,
	@SpecimenID [varchar](10)
WITH EXECUTE AS CALLER
AS
If Exists(Select * From  tbcoadmpackage_items Where PackageID = @PackageID and ExamID = @ExamID and RevenueID = @RevenueID)
 Begin                                                                                                                                                                                                                                           
	Update tbcoAdmPackage_items
		Set PackageID  =  @PackageID,
			ExamID  =  @ExamID,
			RevenueID  =  @RevenueID,
			Quantity  =  @Quantity,
			Amount  =  @Amount,
			Gender  =  @Gender,
			Status  =  @Status,
			TotalGross = @TotalGross,
			SpecimenID = @SpecimenID
	Where PackageID = @PackageID and ExamID = @ExamID and RevenueID = @RevenueID
 End;

Else

Begin
	Insert Into tbcoAdmPackage_items(PackageID,ExamID,RevenueID,Quantity,Amount,Gender,Status,TotalGross,SpecimenID)
		Values(@PackageID,@ExamID,@RevenueID,@Quantity,@Amount,@Gender,@Status,@TotalGross,@SpecimenID)

End;

' 
END
GO
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Bui_Search_SpecimenID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_Bui_Search_SpecimenID]
	@PackageID [varchar](10),
	@ExamID [varchar](10)
WITH EXECUTE AS CALLER
AS
select isnull(SpecimenID,'''')SpecimenID from build_file..tbcoadmpackage_items
where Packageid = @PackageID and examid = @ExamID


' 
END
GO