USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_Chargeslip_GetCharges]    Script Date: 11/21/2017 12:32:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Lab_Chargeslip_GetCharges] 
	@RefNum VARCHAR(10)
AS
SET NOCOUNT ON;

DECLARE @OutsideDoctor VARCHAR(50),
		@OPService VARCHAR(10),
		@RequestDate VARCHAR(30),
		@NurseRemarks VARCHAR(100),
		@Rush AS VARCHAR(1),
		@Station AS VARCHAR(10),
		@RequestNurse AS VARCHAR(50),
		@TotalPackageAmount AS FLOAT
	
SET @OutsideDoctor = (SELECT TOP 1 OutsideDoctor FROM tbLabMaster WHERE Refnum = @RefNum);
SELECT TOP 1 @OPService = CASE WHEN OpService = 1 THEN 'OP-SERVICE' ELSE '' END 
FROM tbLabMaster
WHERE RefNum = @RefNum;

/*
If (Select top 1 IsNull(NurseRequestNum,'') from tbLabMaster where RefNum = @RefNum) <> ''
   Begin
	Select top 1 @Requestdate = B.RequestDate,
		       @NurseRemarks = isnull(B.Remarks,'') ,
		       @Station = isnull(B.StationID,''),
		       @RequestNurse = isnull(PM.LastName,'') + ', ' +  isnull(PM.Firstname,'')
	from tbLabMaster A
		left outer join Station..tbNurseCommunicationFile B on B.RequestNum = A.NurseRequestNum
		left outer join PASSWORD..tbPasswordMaster PM on B.UserID = PM.EmployeeID
	where A.RefNum = @RefNum
   End
Else Set @RequestDate = '';
*/

IF EXISTS(SELECT RefNum FROM Billing..tbBillDailyBill 
	  WHERE RefNum = @RefNum AND RevenueId = 'LB')
   BEGIN
	SET @TotalPackageAmount  = (SELECT SUM(Amount)  FROM billing..tbBillDailyBill WHERE refnum = @RefNum);

	SELECT 	MAX(B.TransDate) AS [Trans. DATE],
		MAX(B.RefNum) AS  [Charge Slip No.],
		MAX(B.ItemId) AS Code, 
	    MAX(L.LabExam) AS Examination, 
		MAX(B.Quantity) AS Quantity,
		MAX(B.Amount) AS Amount,
		'' AS Remarks,
		MAX(P.HospNum) AS HospNum,
		MAX(B.IdNum) AS IdNum,
		MAX(R.RoomID) AS Room,
		CASE WHEN MAX(B.RequestDocID) = '0' THEN @OutsideDoctor
		     ELSE MAX(ISNULL(Doctor.LastName,'')) + ', ' + MAX(ISNULL(Doctor.FirstName,'')) + ' ' + MAX(ISNULL(Doctor.MiddleName,'')) 
		END AS Doctor,
		0 AS NetAmount,
		'' AS DiscountType,
		MAX(ISNULL(L.LabSectionid, '')) AS SectionId,
		MAX(laboratory.dbo.fn_LabGetItemRate (B.IdNum, B.ItemId)) AS Price,
		MAX(ISNULL(NC.RequestDate,LM.TransDate)) AS RequestDate,
		MAX(@OPService) AS OPService,
		MAX(Laboratory.dbo.fn_LabGetUserName(B.UserId)) AS UserName,
		MAX(ISNULL(S.RevenueId, 'LB')) AS RevenueID,
		MAX(ISNULL(NC.Remarks,'')) AS NurseRemarks,
		MAX(ISNULL(S.labSection,'')) AS LabSection,
		ISNULL(MAX(NC.Stat),ISNULL(MAX(LM.Rush),'')) AS RUSH,
		MAX(ISNULL(NC.StationID,'')) AS Station,
		
		CASE WHEN MAX(ISNULL(NC.UserID,'')) <> '' THEN
				MAX(ISNULL(NP.LastName,'')) + ', ' +  MAX(ISNULL(NP.Firstname,''))
			 ELSE
				MAX(Laboratory.dbo.fn_LabGetUserName(B.UserId))
		END AS RequestNurse,
		
		@TotalPackageAmount AS PackageAmount,
		(SELECT TOP 1 ISNULL(Remarks,'') FROM tbLABMaster WHERE RefNum = B.RefNum) AS LabRemarks
	FROM Billing..tbBillDailyBill B
		LEFT OUTER JOIN Build_File..tbCoLabExam L ON B.ItemId = L.LabExamId
		LEFT OUTER JOIN Patient_Data..tbPatient P ON B.IdNum = P.IDNum
		LEFT OUTER JOIN Laboratory..tbLabMaster LM ON B.Refnum = LM.RefNum AND B.ItemId = LM.ItemID --and B.RequestNum = LM.RequestNum
		LEFT OUTER JOIN Build_File..tbCoRoom R ON B.RoomID = R.RoomID
		LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Doctor.DoctorID = B.RequestDocID 
		LEFT OUTER JOIN Build_File..tbCoLabSection S ON S.LabSectionID = L.LabSectionID
		LEFT OUTER JOIN Station..tbNurseCommunicationFile NC ON B.RefNum = NC.ReferenceNum AND B.ItemID = NC.IteMID --and LM.NurseRequestNum = NC.RequestNum
		LEFT OUTER JOIN PASSWORD..tbpasswordmaster NP ON NC.UserID = NP.EmployeeID
	WHERE B.Revenueid = 'LB' AND B.RefNum = @RefNum
	GROUP BY S.LabSectionID, s.LabSection, B.RefNum, B.ItemId
--	having sum(B.Amount) <> 0 --and Sum(B.Amount) > 0
	
	UNION ALL
	
		SELECT 	MAX(B.TransDate) AS [Trans. DATE],
		MAX(B.RefNum) AS  [Charge Slip No.],
		MAX(B.ItemId) AS Code, 
	       	MAX(L.LastName + ', ' + L.FirstName) AS Examination, 
		'1' AS Quantity,
		SUM(B.Amount) AS Amount,
		'Professional Fee' AS Remarks,
		MAX(P.HospNum) AS HospNum,
		MAX(B.IdNum) AS IdNum,
		MAX(R.RoomID) AS Room,
		CASE WHEN MAX(B.RequestDocID) = '0' THEN @OutsideDoctor
		     ELSE MAX(ISNULL(Doctor.LastName,'')) + ', ' + MAX(ISNULL(Doctor.FirstName,'')) + ' ' + MAX(ISNULL(Doctor.MiddleName,'')) 
		END AS Doctor,
		0 AS NetAmount,
		'' AS DiscountType,
		'PF' SectionId,
		SUM(B.Amount) AS Price,
		'' AS RequestDate,
		'' AS OPService,
		MAX(Laboratory.dbo.fn_LabGetUserName(B.UserId)) AS UserName,
		'LB' AS RevenueID,
		'' AS NurseRemarks,
		'PROFESSIONAL FEE' AS LabSection,
		'' AS RUSH,
		'' AS Station,
		'' AS RequestNurse,
		0 AS PackageAmount,
		'' AS LabRemarks
	FROM Billing..tbBillDailyBill B
		LEFT OUTER JOIN Build_File..tbCoDoctor L ON B.ItemId = L.doctorid
		LEFT OUTER JOIN Patient_Data..tbPatient P ON B.IdNum = P.IDNum
		LEFT OUTER JOIN Build_File..tbCoRoom R ON B.RoomID = R.RoomID
		LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Doctor.DoctorID = B.RequestDocID 
		LEFT OUTER JOIN Build_File..tbCoDoctor ReqDoctor ON ReqDoctor.DoctorID = B.ItemId
	WHERE (B.SummaryCode = 'LB' AND (B.Revenueid = 'PL' OR   B.Revenueid = 'MD')) AND B.RefNum = @RefNum
	GROUP BY B.RefNum, B.ItemId
--	having sum(B.Amount) <> 0 --and Sum(B.Amount) > 0

   END
ELSE IF EXISTS(SELECT RefNum FROM Billing..tbBillOPDailyOut 
	  WHERE RefNum = @RefNum AND RevenueId = 'LB')
   BEGIN
	SET @TotalPackageAmount  = (SELECT SUM(Amount)  FROM billing..tbbillopdailyout WHERE refnum = @RefNum);

	SELECT 	MAX(B.TransDate) AS [Trans. DATE],
		MAX(B.RefNum) AS  [Charge Slip No.],
		MAX(B.ItemId) AS Code, 
	    MAX(L.LabExam) AS Examination, 
		SUM(B.Quantity) AS Quantity,
		SUM(B.Amount) AS Amount,
		'' AS Remarks,
		MAX(B.HospNum) AS HospNum,
		MAX(B.IdNum) AS IdNum,
		MAX(B.RoomId) AS Room,
		CASE WHEN MAX(B.RequestDocId) = '0' THEN @OutsideDoctor 
		     ELSE MAX(ISNULL(Doctor.LastName,'')) + ', ' + MAX(ISNULL(Doctor.FirstName,'')) + ' ' + MAX(ISNULL(Doctor.MiddleName,'')) 
		END AS Doctor,
		MAX(ISNULL(B.NetAmount,0)) AS NetAmount,
		MAX(ISNULL(B.DiscountType,'')) AS DiscountType,
		MAX(ISNULL(L.LabSectionid, '')) AS SectionId,
		MAX(laboratory.dbo.fn_LabGetItemRate (B.IdNum, B.ItemId)) AS Price,
		MAX(ISNULL(NC.RequestDate,LM.TransDate)) AS RequestDate,
		MAX(@OPService) AS OPService,
		MAX(Laboratory.dbo.fn_LabGetUserName(B.UserId)) AS UserName,
		MAX(ISNULL(S.RevenueId, 'LB')) AS RevenueID,
		MAX(ISNULL(NC.Remarks,'')) AS NurseRemarks,
		MAX(ISNULL(S.labSection,'')) AS LabSection,
		ISNULL(MAX(NC.Stat),ISNULL(MAX(LM.Rush),'')) AS RUSH,
		MAX(ISNULL(NC.StationID,'')) AS Station,
		
		CASE WHEN MAX(ISNULL(NC.UserID,'')) <> '' THEN
				MAX(ISNULL(NP.LastName,'')) + ', ' +  MAX(ISNULL(NP.Firstname,''))
			 ELSE
				MAX(Laboratory.dbo.fn_LabGetUserName(B.UserId))
		END AS RequestNurse,
		
		@TotalPackageAmount AS PackageAmount,
		(SELECT TOP 1 ISNULL(Remarks,'') FROM tbLABMaster WHERE RefNum = B.RefNum) AS LabRemarks
	FROM Billing..tbBillOPDailyOut B
		LEFT OUTER JOIN Build_File..tbCoLabExam L ON B.ItemId = L.LabExamId
		LEFT OUTER JOIN Patient_Data..tbOutPatient P ON B.IdNum = P.IDNum
		LEFT OUTER JOIN Laboratory..tbLabMaster LM ON B.Refnum = LM.refNum AND B.ItemId = LM.ItemID --and B.RequestNum = LM.RequestNum
		LEFT OUTER JOIN Build_File..tbCoRoom R ON B.RoomID = R.RoomID
		LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Doctor.DoctorID = B.RequestDocID 
		LEFT OUTER JOIN Build_File..tbCoLabSection S ON S.LabSectionID = L.LabSectionID
		LEFT OUTER JOIN Station..tbNurseCommunicationFile NC ON B.RefNum = NC.ReferenceNum AND B.ItemID = NC.IteMID --and LM.NurseRequestNum = NC.RequestNum
		LEFT OUTER JOIN PASSWORD..tbpasswordmaster NP ON NC.UserID = NP.EmployeeID
	WHERE B.Revenueid = 'LB' AND B.RefNum = @RefNum
	GROUP BY S.LabSectionID,LabSection, B.RefNum, B.ItemId
--	having sum(B.Amount) <> 0 --and Sum(B.Amount) > 0	
	UNION ALL

	SELECT 	MAX(B.TransDate) AS [Trans. DATE],
		MAX(B.RefNum) AS  [Charge Slip No.],
		MAX(B.ItemId) AS Code, 
	       	MAX(L.LastName + ', ' + L.FirstName) AS Examination, 
		SUM(B.Quantity) AS Quantity,
		SUM(B.Amount) AS Amount,
		'Professional Fee' AS Remarks,
		MAX(B.HospNum) AS HospNum,
		MAX(B.IdNum) AS IdNum,
		MAX(B.RoomId) AS Room,
		CASE WHEN MAX(B.RequestDocId) = '0' THEN @OutsideDoctor 
		     ELSE MAX(ISNULL(Doctor.LastName,'')) + ', ' + MAX(ISNULL(Doctor.FirstName,'')) + ' ' + MAX(ISNULL(Doctor.MiddleName,'')) 
		END AS Doctor,
		0 AS NetAmount,
		'' AS DiscountType,
		'PF' SectionId,
		SUM(B.Amount) AS Price,
		MAX(@Requestdate) AS RequestDate,
		MAX(@OPService) AS OPService,
		MAX(Laboratory.dbo.fn_LabGetUserName(B.UserId)) AS UserName,
		'LB' AS RevenueID,
		'' AS NurseRemarks,
		'PROFESSIONAL FEE' AS LabSection,
		'' AS RUSH,
		'' AS Station,
		'' AS RequestNurse,
		0 AS PackageAmount,
		'' AS LabRemarks
	FROM Billing..tbBillOPDailyOut B
		LEFT OUTER JOIN Build_File..tbCoDoctor L ON B.ItemId = L.DoctorID
		LEFT OUTER JOIN Patient_Data..tbOutPatient P ON B.IdNum = P.IDNum
		LEFT OUTER JOIN Laboratory..tbLabMaster LM ON B.Refnum = LM.refNum AND B.ItemId = LM.ItemID
		LEFT OUTER JOIN Build_File..tbCoRoom R ON B.RoomID = R.RoomID
		LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Doctor.DoctorID = B.RequestDocID 
		LEFT OUTER JOIN Build_File..tbCoDoctor ReqDoctor ON ReqDoctor.DoctorID = B.ItemId
	WHERE  (B.SummaryCode = 'LB' AND (B.Revenueid = 'PL' OR   B.Revenueid = 'MD')) AND B.RefNum = @RefNum
	GROUP BY B.RefNum, B.ItemId
--	having sum(B.Amount) <> 0 --and Sum(B.Amount) > 0
   END
ELSE IF EXISTS(SELECT RefNum FROM Billing..tbCashAssessment 
	  WHERE RefNum = @RefNum AND RevenueId = 'LB')
  BEGIN
	SELECT 	B.TransDate AS [Trans. DATE],
		B.RefNum AS  [Charge Slip No.],
		B.ItemId AS Code, 
	       	L.LabExam AS Examination, 
		B.Quantity AS Quantity,
		B.Amount AS Amount,
		'' AS Remarks,
		B.HospNum AS HospNum,
		B.IdNum AS IdNum,
		'OPD' AS Room,
		CASE WHEN B.RequestDocID = '0' THEN B.DoctorName
		     ELSE ISNULL(Doctor.LastName,'') + ', ' + ISNULL(Doctor.FirstName,'') + ' ' + ISNULL(Doctor.MiddleName,'') 
		END AS Doctor,
		0 AS NetAmount,
		'' AS DiscountType,
		ISNULL(L.LabSectionid, '') AS SectionId,
		laboratory.dbo.fn_LabGetItemRate (B.IdNum, B.ItemId) AS Price,
		B.Transdate AS RequestDate,
		@OPService AS OPService,
		Laboratory.dbo.fn_LabGetUserName(B.UserId) AS UserName,
		ISNULL(S.RevenueId, 'LB') AS RevenueID,
		'' AS NurseRemarks,
		ISNULL(S.labSection,'') AS LabSection,
		ISNULL(B.RUSH,'') AS RUSH,
		'' AS Station,
		'' AS RequestNurse,
		0 AS PackageAmount,
		B.Remarks AS LabRemarks		
	FROM Billing..tbCashAssessment B
		LEFT OUTER JOIN Build_File..tbCoLabExam L ON B.ItemId = L.LabExamId
		LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Doctor.DoctorID = B.RequestDocID 
		LEFT OUTER JOIN Build_File..tbCoLabSection S ON S.LabSectionID = L.LabSectionID
	WHERE B.Revenueid = 'LB' AND B.RefNum = @RefNum AND ISNULL(RecordStatus,'') <> 'R'

	UNION ALL

	SELECT 	B.TransDate AS [Trans. DATE],
		B.RefNum AS  [Charge Slip No.],
		B.ItemId AS Code, 
	       	L.LastName + ', ' + L.FirstName AS Examination, 
		'1' AS Quantity,
		B.Amount AS Amount,
		'Professional Fee' AS Remarks,
		B.HospNum AS HospNum,
		B.IdNum AS IdNum,
		'OPD' AS Room,
		CASE WHEN B.RequestDocID = '0' THEN B.DoctorName
		     ELSE ISNULL(Doctor.LastName,'') + ', ' + ISNULL(Doctor.FirstName,'') + ' ' + ISNULL(Doctor.MiddleName,'') 
		END AS Doctor,
		0 AS NetAmount,
		'' AS DiscountType,
		'PF' AS SectionId,
		B.Amount AS Price,
		@Requestdate AS RequestDate,
		@OPService AS OPService,
		Laboratory.dbo.fn_LabGetUserName(B.UserId) AS UserName,
		'PF' AS RevenueID,
		'' AS NurseRemarks,
--		'PROFESSIONAL FEE' as LabSection,
		(SELECT TOP 1 LabSection FROM billing..tbcashassessment Bill
				LEFT OUTER JOIN Build_File..tbcoLabExam LabExam ON Bill.ItemId = LabExam.LabExamId
				LEFT OUTER JOIN Build_File..tbcoLabSection SECTION ON Labexam.LabsectionId = Section.LabSectionId
			WHERE refnum = B.Refnum AND Bill.Profid1 = B.ItemId) AS LabSection,
		'' AS RUSH,
		'' AS Station,
		'' AS RequestNurse,
		0 AS PackageAmount,
		B.Remarks AS LabRemarks		
	FROM Billing..tbCashAssessment B
		LEFT OUTER JOIN Build_File..tbCoDoctor L ON B.ItemId = L.DoctorId
		LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Doctor.DoctorID = B.RequestDocID 
		LEFT OUTER JOIN Build_File..tbCoDoctor ReqDoctor ON ReqDoctor.DoctorID = B.ItemID 
	WHERE ((B.Revenueid = 'MD' OR B.Revenueid = 'PL') AND B.DepartmentId = 'LB') AND B.RefNum = @RefNum AND ISNULL(RecordStatus,'') <> 'R'
   END