use patient_data
go

Alter Table Patient_data..tboutpatient
add OPClassification varchar(1) null
go
Alter table patient_data..tboutPatient add
ReasonOfReferral varchar(200) null, 
ReferredFrom varchar(150) null
go
Insert into tbOPDmenu
values('78','Discharge Entry','1','E','2')

go
Insert into tbOPDmenu
values('79','Discharge Entry','1','F','3')
go

Delete from tbOPDmenu
where menuid in(12,11)

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


USE [Patient_DATA]
GO


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


ALTER Procedure [OPD_GetPatientInfo] 

@Idnum as varchar(11),
@HospNum as varchar(10)

as

	Select isnull(A.LastName,'') LastName,
		   isnull(A.FirstName,'') FirstName,
		   isnull(A.MiddleName,'') MiddleName,
		   isnull(A.birthDate,'')BirthDate,
           isnull(A.Sex,'') Sex,
		   dbo.fn_ComputeAge(A.BirthDate, getdate()) Age,
		   isnull(A.Barangay,'')Barangay,
		   Case A.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,
	       B.Idnum,
           B.HospNum,
           isnull(B.DoctorID1,'')DoctorID1,
		   isnull(D1.FirstName,'') + ' ' + isnull(D1.LastName,'') as Doctor1,
           isnull(B.DoctorID2,'')DoctorID2,
		   isnull(D2.FirstName,'') + ' ' + isnull(D2.LastName,'') as Doctor2,
           isnull(B.DoctorID3,'')DoctorID3,
		   isnull(D3.FirstName,'') + ' ' + isnull(D3.LastName,'') as Doctor3,
  		   isnull(B.DoctorID4,'')DoctorID4,
		   isnull(D4.FirstName,'') + ' ' + isnull(D4.LastName,'') as Doctor4,
		   isnull(B.DoctorID5,'')DoctorID5,
		   isnull(D5.firstname,'') + ' ' + isnull(D5.LastName,'') as Doctor5,	
           B.AdmDate,
           case when (B.AccountNum = B.Hospnum or isnull(B.AccountNum,'') = '') then 'PERSONAL'
					else C.Company end as Account,
		   B.AccountNum,
		   isnull(B.ServiceID1,'')ServiceID1,
		   isnull(S1.Service,'') as Service1,
		   isnull(B.ServiceID2,'')ServiceID2,
		   isnull(S2.Service,'') as Service2,
		   isnull(B.ServiceID3,'') as ServiceID3,
		   isnull(S3.Service,'') as Service3,
		   isnull(B.ServiceID4,'')ServiceID4,
		   isnull(S4.Service,'') as Service4,
		   isnull(B.ServiceID5,'')ServiceID5,
		   isnull(S5.Service,'') as Service5,
           isnull(H.chiefcomplaints,'')ChiefComplaints

		   
	from patient_Data..tboutpatient B
        left outer join patient_data..tbmaster A on a.hospnum = b.hospnum
		left outer join build_file..tbcocompany C on b.accountnum = c.accountnum
        left outer join build_file..tbcodoctor D1 on b.doctorid1 = d1.doctorid
		left outer join build_file..tbcodoctor D2 on b.doctorid2 = d2.doctorid
		left outer join build_file..tbcodoctor D3 on b.doctorid3 = d3.doctorid
		left outer join build_file..tbcodoctor D4 on b.doctorid4 = d4.doctorid
		left outer join build_file..tbcodoctor D5 on b.doctorid5 = d5.doctorid
		left outer join build_file..tbcoService S1 on S1.ServiceID = B.serviceid1
		left outer join build_file..tbcoService S2 on S2.ServiceID = B.serviceid2
		left outer join build_file..tbcoService S3 on S3.ServiceID = B.serviceid3
		left outer join build_file..tbcoService S4 on S4.ServiceID = B.serviceid4
		left outer join build_file..tbcoService S5 on S5.ServiceID = B.serviceid5
		left outer join patient_data..tboutpatienthistory H on B.idnum = H.Idnum
		

where B.Hospnum = @hospnum and b.idnum = @idnum




go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


USE [Patient_DATA]
GO



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


ALTER PROCEDURE [OPD_UpdateOPDHistory]
@HospNum As Varchar(10),
@IDNum As Varchar(10),
@OPDStatus As Varchar(1),
@Service1 As Varchar(4),
@Service2 As Varchar(4),
@Service3 As Varchar(4),
@Service4 As Varchar(4),
@Service5 As Varchar(4),
@Doctor1 As Varchar(4),
@Doctor2 As Varchar(4),
@Doctor3 As Varchar(4),
@Doctor4 As Varchar(4),
@Doctor5 As Varchar(4),
@Findings As Text,
@Medications As Text,
@Chief As Text,
@AdmDate As Datetime,
@DcrDate As Datetime,
@ERClassification As Varchar,
@Reason As Varchar(500),
@ReferredFrom As Varchar(500),
@UserID As Varchar(10)=NULL

AS

	DECLARE 	
		@BillingDate As Datetime,
		@BillingClerk As Varchar(10)

			Set @BillingDate = Null;
			Set @BillingClerk = Null;

Begin

			Select	@BillingDate =	(Case Isnull(BillingDate,'') 
							When '' then							
								(Case 
									When Isnull(@DcrDate,'') ='' then NULL
									When Convert(Varchar(10),@DcrDate,101) = Convert(Varchar(10),Getdate(),101) then GETDATE()
									When @DcrDate < Getdate() then GetDate()
									 --Else  BillingDate
								End)
							Else Billingdate
						End),
 
				@BillingClerk =	(Case Isnull(BillingClerk,'') 
							When '' then							
								(Case 
									When Isnull(@DcrDate,'') ='' then NULL
									When Convert(Varchar(10),@DcrDate,101) = Convert(Varchar(10),Getdate(),101) then @UserID
									When @DcrDate < Getdate() then @UserID
									 --Else  BillingDate
								End)
							Else  BillingClerk
						End)
			From PATIENT_DATA..tbOutPatient
			 Where HospNum=@HospNum and IDNum=@IDNum;


	 Update PATIENT_DATA..tbOutpatient	
			Set	Admdate=@AdmDate,
				DcrDate=@DcrDate,
				ServiceID1=@Service1,
				ServiceID2=@Service2,
				ServiceID3=@Service3,
				ServiceID4=@Service4,
				ServiceID5=@Service5,
				DoctorID1=@Doctor1,
				DoctorID2=@Doctor2,
				DoctorID3=@Doctor3,
				DoctorID4=@Doctor4,
				DoctorID5=@Doctor5,
				OPDStatus=@OPDStatus,
				OPClassification=@ERClassification,
				ReasonOfReferral=@Reason,
				ReferredFrom=@ReferredFrom,
				BillingDate = @Billingdate,
				BillingClerk = @BillingClerk			
	 Where HospNum=@HospNum and IDNum=@IDNum;

	 Update PATIENT_DATA..tbOutPatientHistory	
			Set	Findings=@Findings,
				Medications=@Medications,
				ChiefComplaints=@Chief
	 Where IDNum=@IDNum;

End





go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



USE [Patient_DATA]
GO

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

ALTER Procedure [OPD_PerDepartmentLogbook] 
@StartDate varchar(10),
@EndDate varchar(10),
@RevenueID as varchar(3)

as

if @Revenueid = '' 
begin
	select max(Z.Hospnum)HospNum, max(Z.Idnum)IdNum,  max(Z.PxName)PxName, Z.RevenueID,
	count(Z.Revenueid) Ctr, 'Cash' PType, R.ItemName, Z.AdmDate


	from (

			Select A.Hospnum, A.Idnum, 
			isnull(M.LastName, '') + ', ' + isnull(M.FirstName,'') as PxName,
			C.Revenueid, 
			case when C.Revenueid in ('PH','CS') then 
			Inv.ItemName else L.Description end as Description,
			A.AdmDate

			from Patient_data..tbOutPatient A
			left outer join Patient_data..tbMaster M
					on M.Hospnum = A.Hospnum
			left outer join Billing..tbCashAssessment C
					on C.Hospnum = A.Hospnum
			left outer Join Billing..tbBillExamListing L
					on (L.itemid = C.Itemid and C.Revenueid = L.Revenueid)
			left outer join Inventory..tbInvMaster Inv
					on C.Itemid = Inv.Itemid
			where 
			A.AdmDate between @StartDate and @EndDate + ' 23:59:59.99'
			and C.TransDate between @StartDate and @EndDate + ' 23:59:59.99'
	--		And c.idnum is not null
	)Z
	left outer join Build_file..tbCoRevenueCode R
		on Z.Revenueid = R.RevenueID

	where z.revenueid not in ('MD')
	group by  Z.Idnum, Z.Hospnum , Z.Pxname, Z.Revenueid, R.ItemName, Z.AdmDate
	--order by z.pxname

	Union All

	select max(Z.Hospnum)HospNum, max(Z.Idnum)IdNum,  max(Z.PxName)PxName, Z.RevenueID,
	count(Z.Revenueid) Ctr, 'Post Charges' PType, R.ItemName, Z.AdmDate

	from (

			Select A.Hospnum, A.Idnum, 
			isnull(M.LastName, '') + ', ' + isnull(M.FirstName,'') as PxName,
			C.Revenueid, 
			case when C.Revenueid in ('PH','CS') then 
			Inv.ItemName else L.Description end as Description,
			A.AdmDate

			from Patient_data..tbOutPatient A
			left outer join Patient_data..tbMaster M
					on M.Hospnum = A.Hospnum
			left outer join Billing..tbBillOpdailyout C
					on C.Hospnum = A.Hospnum and C.Idnum = A.IDnum
			left outer Join Billing..tbBillExamListing L
					on L.itemid = C.Itemid and C.Revenueid = L.Revenueid
			left outer join Inventory..tbInvMaster Inv
					on C.Itemid = Inv.Itemid
			where 
			A.AdmDate between @StartDate and @EndDate + ' 23:59:59.99'
			and C.TransDate between @StartDate and @EndDate + ' 23:59:59.99'
			And c.idnum <> 'CASH' 
	)Z
	left outer join Build_file..tbCoRevenueCode R
		on Z.Revenueid = R.RevenueID

	where z.revenueid not in ('MD', 'ER')
	group by  Z.Idnum, Z.Hospnum , Z.Pxname, Z.Revenueid, R.ItemName, Z.AdmDate
	order by z.pxname, z.ptype

	end

else

begin

	select max(Z.Hospnum)HospNum, max(Z.Idnum)IdNum,  max(Z.PxName)PxName, Z.RevenueID,
	count(Z.Revenueid) Ctr, 'Cash' PType, R.ItemName, Z.AdmDate


	from (

			Select A.Hospnum, A.Idnum, 
			isnull(M.LastName, '') + ', ' + isnull(M.FirstName,'') as PxName,
			C.Revenueid, 
			case when C.Revenueid in ('PH','CS') then 
			Inv.ItemName else L.Description end as Description,
			A.AdmDate

			from Patient_data..tbOutPatient A
			left outer join Patient_data..tbMaster M
					on M.Hospnum = A.Hospnum
			left outer join Billing..tbCashAssessment C
					on C.Hospnum = A.Hospnum
			left outer Join Billing..tbBillExamListing L
					on (L.itemid = C.Itemid and C.Revenueid = L.Revenueid)
			left outer join Inventory..tbInvMaster Inv
					on C.Itemid = Inv.Itemid
			where 
			A.AdmDate between @StartDate and @EndDate + ' 23:59:59.99'
			and C.TransDate between @StartDate and @EndDate + ' 23:59:59.99'
			And c.Revenueid = @RevenueID
	)Z
	left outer join Build_file..tbCoRevenueCode R
		on Z.Revenueid = R.RevenueID

	where z.revenueid not in ('MD') and z.revenueid = @Revenueid
	group by  Z.Idnum, Z.Hospnum , Z.Pxname, Z.Revenueid, R.ItemName, Z.AdmDate
	--order by z.pxname

	Union All

	select max(Z.Hospnum)HospNum, max(Z.Idnum)IdNum,  max(Z.PxName)PxName, Z.RevenueID,
	count(Z.Revenueid) Ctr, 'Post Charges' PType, R.ItemName, z.AdmDate

	from (

			Select A.Hospnum, A.Idnum, 
			isnull(M.LastName, '') + ', ' + isnull(M.FirstName,'') as PxName,
			C.Revenueid, 
			case when C.Revenueid in ('PH','CS') then 
			Inv.ItemName else L.Description end as Description,
			A.AdmDate

			from Patient_data..tbOutPatient A
			left outer join Patient_data..tbMaster M
					on M.Hospnum = A.Hospnum
			left outer join Billing..tbBillOpdailyout C
					on C.Hospnum = A.Hospnum and C.Idnum = A.IDnum
			left outer Join Billing..tbBillExamListing L
					on (L.itemid = C.Itemid and C.Revenueid = L.Revenueid)
			left outer join Inventory..tbInvMaster Inv
					on C.Itemid = Inv.Itemid
			where 
			A.AdmDate between @StartDate and @EndDate + ' 23:59:59.99'
			and C.TransDate between @StartDate and @EndDate + ' 23:59:59.99'
			And c.idnum <> 'CASH' and c.revenueid = @revenueid
	)Z
	left outer join Build_file..tbCoRevenueCode R
		on Z.Revenueid = R.RevenueID

	where z.revenueid not in ('MD', 'ER') and z.revenueid = @revenueid
	group by  Z.Idnum, Z.Hospnum , Z.Pxname, Z.Revenueid, R.ItemName, Z.AdmDate
	order by z.pxname, z.ptype

	end

go