USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_Cancel_GetRequestInfo]    Script Date: 11/21/2017 14:49:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_Cancel_GetRequestInfo]
	@RefNum varchar(10)
As
Declare @IdNum varchar(10),
	@HospNum varchar(8),
	@CashPatient varchar(1)

If Exists(Select distinct RefNum from Billing..tbCashAssessment where RevenueID  in('LB') and RefNum = @RefNum)
   Begin
      Select top 1 @IdNum=IdNum, @HospNum=HospNum From Billing..tbCashAssessment 
      where RefNum = @RefNum and Revenueid in('LB') ;
      Set @CashPatient = 'Y'   
   End;
Else
   Begin
      Select top 1 @IdNum=IdNum, @HospNum=HospNum From tbLabMaster 
      where RefNum = @RefNum;
      Set @CashPatient = 'N'
   End;

If IsNumeric(RIGHT(@IdNum,2)) = 1 

   Begin
     If  @CashPatient = 'N' 
		Begin
			Select Top 1 
				IsNull(A.HospNum,'') as HospNum, 
				IsNull(A.IdNum,'') as IdNum, 
				'In Patient' as PatientType,
				IsNull(B.LastName + ', ' + B.FirstName + ' ' + B.MiddleName,'') as PatientName,
				IsNull(A.TransDate,'') as TransDate,
				Laboratory.dbo.fn_LabComputeAge(B.BirthDate, getdate()) as Age,
				Isnull(B.Sex,'') as Sex,
				IsNull(A.NurseRemarks,'') as Remarks,
				IsNull(D.RoomId,'') + ' - ' + IsNull(D.RoomDescription,'') as Room,
				Case When A.DoctorId = '0' Then A.OutsideDoctor 
					 Else IsNull(E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName,'') 
				End as Doctor,
				Isnull(A.ORNum,'') as ORNum,
				Status =(Case IsNull(A.RequestStatus,'') 
						 When 'W' Then  'With Result' 
							 When 'X' Then 'Pending Request'
							 Else 'Cancelled Request'
					End) ,
				IsNull(B.AccountNum,'') as AccountNum,
				laboratory.dbo.fn_LabGetCivilStatus(IsNull(B.CivilStatus,'')) as CivilStatus,
				IsNull(A.OPService,0) as OPService,
				isnull(A.Doctorid, '0' ) as [DoctorId]
			From tbLabMaster A
				Left Outer Join Patient_Data..tbMaster B On A.HospNum = B.HospNum
				Left Outer Join Patient_Data..tbPatient C On A.IdNum = C.IdNum
				Left Outer Join Build_File..tbCoRoom D On C.RoomId = D.RoomId
				Left Outer Join Build_File..tbCoDoctor E On A.DoctorId = E.DoctorId
			Where RefNum = @RefNum
 		End
      Else
   		Begin
			Select Top 1 
				IsNull(A.HospNum,'') as HospNum, 
				IsNull(A.IdNum,'') as IdNum, 
				'In Patient - Cash' as PatientType,
				IsNull(B.LastName + ', ' + B.FirstName + ' ' + B.MiddleName,'') as PatientName,
				IsNull(A.TransDate,'') as TransDate,
				Laboratory.dbo.fn_LabComputeAge(B.BirthDate, getdate()) as Age,
				Isnull(B.Sex,'') as Sex,
				'' as Remarks,
				IsNull(D.RoomId,'') + ' - ' + IsNull(D.RoomDescription,'') as Room,
				Case When A.RequestDocId = '0' Then A.DoctorName 
					 Else IsNull(E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName,'') 
				End as Doctor,
				Isnull(A.ORNumber,'') as ORNum,
				Status =(Case IsNull(A.RecordStatus,'') 
						 When '' Then  'Not yet paid' 
							 When '1' Then 'Paid'
							 Else 'Cancelled Request'
					End) ,
				'' as AccountNum,
				laboratory.dbo.fn_LabGetCivilStatus(IsNull(B.CivilStatus,'')) as CivilStatus,
				IsNull(A.OPService,0) as OPService,
				isnull(A.RequestDocId, '0' ) as [DoctorId]
			From Billing..tbCashAssessment A
				Left Outer Join Patient_Data..tbMaster B On A.HospNum = B.HospNum
				Left Outer Join Patient_Data..tbPatient C On A.IdNum = C.IdNum
				Left Outer Join Build_File..tbCoRoom D On C.RoomId = D.RoomId
				Left Outer Join Build_File..tbCoDoctor E On A.RequestDocID = E.DoctorId
			Where A.RefNum = @RefNum	
		End		
    End
Else
	If @CashPatient = 'Y'  
		Begin
			Select Top 1 
				IsNull(A.HospNum,'') as HospNum,
				IsNull(A.IdNum,'') as IdNum,
				'Cash Patient' as PatientType,
				IsNull(B.LastName + ', ' + B.FirstName + + ' ' + B.MiddleName,'') as PatientName,
				IsNull(A.TransDate,'') as TransDate,
				Laboratory.dbo.fn_LabComputeAge(B.BirthDate, getdate()) as Age,
				IsNull(B.Sex,'') as Sex,
				'' as Remarks,
				'OPD' as Room,
				Case When A.RequestDocId = '0' Then A.DoctorName 
					 Else IsNull(E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName,'') 
				End as Doctor,
				Isnull(A.ORNumber,'') as ORNum,
				Status =(Case IsNull(A.RecordStatus,'') 
						 When '' Then  'Not yet paid' 
							 When '1' Then 'Paid'
							 Else 'Cancelled Request'
					End) ,
				'' as AccountNum,
				laboratory.dbo.fn_LabGetCivilStatus(IsNull(B.CivilStatus,'')) as CivilStatus,
				IsNull(A.OPService,0) as OPService,
				isnull(A.RequestDocId, '0' ) as [DoctorId]
			From Billing..tbCashAssessment A
				Left Outer Join Patient_Data..tbMaster B On B.HospNum = A.HospNum	
				Left Outer Join Build_File..tbCoDoctor E On A.RequestDocID = E.DoctorId
			Where RefNum = @RefNum
		End
	else
		Begin
		   If @IdNum not like 'T%'
    			Begin
					Select Top 1 
					IsNull(A.HospNum,'') as HospNum,
					IsNull(A.IdNum,'') as IdNum,
					'Charge Patient' as PatientType,
					IsNull(B.LastName + ', ' + B.FirstName + + ' ' + B.MiddleName,'') as PatientName,
					IsNull(A.TransDate,'') as TransDate,
					Laboratory.dbo.fn_LabComputeAge(B.BirthDate, getdate()) as Age,
					IsNull(B.Sex,'') as Sex,
					IsNull(A.NurseRemarks,'') as Remarks,
					'OPD' as Room,
					Case When A.DoctorID = '0' Then A.OutsideDoctor 
						 Else IsNull(E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName,'') 
					End as Doctor,
					Isnull(A.ORNum,'') as ORNum,
					Status =(Case IsNull(A.RequestStatus,'') 
							 When 'W' Then  'With Result' 
								 When 'X' Then 'Pending Request'
								 Else 'Cancelled Request'
						End),
					IsNull(F.AccountNum,'') as AccountNum,
					laboratory.dbo.fn_LabGetCivilStatus(IsNull(B.CivilStatus,'')) as CivilStatus,
					IsNull(A.OPService,0) as OPService,
					isnull(A.Doctorid, '0' ) as [DoctorId]
				From tbLabMaster A
					Left Outer Join Patient_Data..tbMaster B On B.HospNum = A.HospNum	
					Left Outer Join Build_File..tbCoDoctor E On A.DoctorId = E.DoctorId
					Left Outer Join Patient_Data..tbOutPatient F On A.IdNum = F.IdNum
				Where RefNum = @RefNum	    
			End  
		Else
			If @IdNum like 'T%' and IsNumeric(@HospNum)=1
      			Begin
					Select Top 1 
					IsNull(A.HospNum,'') as HospNum,
					IsNull(A.IdNum,'') as IdNum,
					'Cash Patient' as PatientType,
					IsNull(B.LastName + ', ' + B.FirstName + + ' ' + B.MiddleName,'') as PatientName,
					IsNull(A.TransDate,'') as TransDate,
					Laboratory.dbo.fn_LabComputeAge(B.BirthDate, getdate()) as Age,
					IsNull(B.Sex,'') as Sex,
					'' as Remarks,
					'OPD' as Room,
					Case When A.RequestDOcId = '0' Then A.DoctorName 
						 Else IsNull(E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName,'') 
					End as Doctor,
					Isnull(A.ORNumber,'') as ORNum,
					Status =(Case IsNull(A.RecordStatus,'') 
							 When '' Then  'Not yet paid' 
								 When '1' Then 'Paid'
								 Else 'Cancelled Request'
						End) ,
					'' as AccountNum,
					laboratory.dbo.fn_LabGetCivilStatus(IsNull(B.CivilStatus,'')) as CivilStatus,
					IsNull(A.OPService,0) as OPService,
					isnull(A.RequestDocId, '0' ) as [DoctorId]
				From Billing..tbCashAssessment A
					Left Outer Join Patient_Data..tbMaster B On B.HospNum = A.HospNum	
					Left Outer Join Build_File..tbCoDoctor E On A.RequestDocID = E.DoctorId
				Where RefNum = @RefNum	    
			End
			 Else
     			Begin
					Select Top 1 
					IsNull(A.HospNum,'') as HospNum,
					IsNull(A.IdNum,'') as IdNum,
					'Cash Patient' as PatientType,
					IsNull(B.LastName + ', ' + B.FirstName + + ' ' + B.MiddleName,'') as PatientName,
					IsNull(A.TransDate,'') as TransDate,
					Laboratory.dbo.fn_LabComputeAge(B.BirthDate, getdate()) as Age,
					IsNull(B.Sex,'') as Sex,
					'' as Remarks,
					'OPD' as Room,
					Case When A.RequestDocId = '0' Then A.DoctorName 
						 Else IsNull(E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName,'') 
					End as Doctor,
					Isnull(A.ORNumber,'') as ORNum,
					Status =(Case IsNull(A.RecordStatus,'') 
							 When '' Then  'Not yet paid' 
								 When '1' Then 'Paid'
								 Else 'Cancelled Request'
						End) ,
					'' as AccountNum,
					laboratory.dbo.fn_LabGetCivilStatus(IsNull(B.CivilStatusId,'')) as CivilStatus,
					IsNull(A.OPService,0) as OPService,
					isnull(A.RequestDocId, '0' ) as [DoctorId]
				From Billing..tbCashAssessment A
					Left Outer Join Patient_Data..tbCashPatient B On B.HospNum = A.HospNum	
					Left Outer Join Build_File..tbCoDoctor E On A.RequestDocID = E.DoctorId
				Where RefNum = @RefNum	    
			End
		End