USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_Get_PatientInfo]    Script Date: 11/21/2017 12:30:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_Get_PatientInfo]
	@IdNum varchar(10),
	@RefNum Varchar(15) = ''
AS

Declare @HospNum varchar(10)

If isnumeric(RIGHT(@IdNum,2)) = 1
  Begin
    Select top 1 A.HospNum, 
		A.IdNum, 
		isnull(B.LastName,'') + ', ' + isnull(B.FirstName,'') + ' ' + isnull(B.MiddleName,'') as PatientName, 
		B.Sex,
		Age =Laboratory.dbo.fn_LabComputeAge(B.BirthDate, getdate()),
		IsNull(S.CivilStatus,'') as CivilStatus,
		IsNull(convert(varchar,B.birthdate,101),'') as [BirthDate], 
		A.RoomId, C.RoomDescription as RoomClass,
		isnull(B.HouseStreet,'')as HouseStreet, 
		isnull(B.Barangay,'')as Barangay, 
		isnull(B.ZipCode,'') as ZipCode,
		isnull(D.Town + ', '+ D.Province,'') as Province,
		isnull(E.FirstName + ' ' + E.MiddleName + ' ' + E.LastName +', M.D.','') as Physician,
		isnull(F.RefNum,'') as RefNum,
		isnull(F.OrNum,'') as ORNum,
		IsNull(C.StationID,'') as StationID,
		Case When ltrim(rtrim(isnull(A.AccountNum,''))) = ltrim(rtrim(A.HospNum)) 
			Then 'PERSONAL' 
			Else IsNull(G.AccountNum,'') + '-' + IsNull(G.Company,'') 
		End as Company,
			IsNull(cast(A.BillingDate as varchar),'') as BillingDate, 
		IsNull(A.AttendingDr1,'') as PhysicianID,
		IsNull(P2.PackageID,'') as PackageID,
		IsNull(P.Package,'') as Package,
	    ISNULL(M.PHICDescription,'') as  PHICDescription,
	    isnull(cast(H.AdmDiagnosis as varchar),'') as AdmDiagnosis,
	    isnull(B.Birthdate,'') as Birthday,
		isnull(a.admDate,'') as AdmDate
		From Patient_Data..tbPatient A
		    Left Outer Join Patient_Data..tbMaster B On A.HospNum = B.HospNum
			Left Outer Join Patient_Data..tbPatient2 P2 on P2.IdNum = A.IdNum
		    Left outer join build_file..tbCoRoom C on A.RoomID = C.RoomID
			Left Outer Join Build_File..tbCoAddress D on B.ZipCode = D.ZipCode
			Left Outer Join Build_File..tbCoDoctor E on A.AttendingDr1 = E.DoctorID
			Left Outer Join Laboratory..tbLabMaster F on A.IDNum = F.IdNum
			Left Outer Join Build_File..tbCoCivilStatus S on S.CivilStatusID = B.CivilStatus
			Left Outer Join Build_File..tbCoCompany G on G.AccountNum = A.AccountNum
			Left Outer Join Build_File..tbCoAdmPackage P on P.PackageID = P2.PackageID
		    Left Outer Join patient_data..tbAdmPHICTable M ON A.MedicareType = M.PHICCode 
			Left Outer Join patient_data..tbPatientHistory H on A.IdNum = H.IdNum
	    where A.IdNum = @IdNum
  End
Else
  If @IdNum not like 'T%'
     Begin
		Select top 1 A.HospNum, 
			A.IdNum, 
			isnull(B.LastName,'') + ', ' + isnull(B.FirstName,'') + ' ' + isnull(B.MiddleName,'') as PatientName, 
			B.Sex,
			case 
			   	when B.birthdate is null then convert(varchar(3), IsNull(B.Age,''))
			   	when B.birthdate <= '01/01/1900' then convert(varchar(3), IsNull(B.Age,''))
			   	else Laboratory.dbo.fn_LabComputeAge(B.Birthdate,getdate()) 
			 end as Age,
			IsNull(C.CivilStatus,'') as CivilStatus,
--			case when convert(varchar(10),IsNull(B.BirthDate,'01/01/1900'),101) = '01/01/1900' then 'N/A'
--				      else cast(convert(varchar(10),B.BirthDate,101) as varchar(10))
--			end as [BirthDate], 
			isnull(convert(varchar,B.BirthDate,101),'') as [BirthDate], 
			Case Isnull(A.ERNum,'')
				when '' then 'OPD'
				ELSE 'OPD/ER'
			END AS RoomId, '' as RoomClass,
			isnull(B.HouseStreet,'')as HouseStreet, 
			isnull(B.Barangay,'') as Barangay, 
			isnull(B.ZipCode,'') as ZipCode,
			isnull(D.Town + ', '+ D.Province,'') as Province,
			isnull(E.FirstName + ' ' + E.MiddleName + ' ' + E.LastName +', M.D.','') as Physician,
			isnull(F.RefNum,'') as RefNum,
			isnull(F.OrNum,'') as ORNum,
			'' as StationID,
			Case When ltrim(rtrim(isnull(A.AccountNum,''))) = ltrim(rtrim(A.HospNum)) 
				Then 'PERSONAL' 
				Else IsNull(G.AccountNum,'') + '-' + IsNull(G.Company,'') 
			End as Company,
			IsNull(A.BillingDate,'') as BillingDate,
			IsNull(A.DoctorId1,'') as PhysicianID,
			IsNull(A.PackageID,'') as PackageID,
			IsNull(P.Package,'') as Package,
	        '' as  PHICDescription,
	        isnull(cast(H.AdmDiagnosis as varchar),'') as AdmDiagnosis,
			isnull(B.Birthdate,'') as Birthday,
			isnull(a.admDate,'') as AdmDate
	    	From Patient_Data..tbOutPatient A
		     	Left Outer Join Patient_Data..tbMaster B On A.HospNum = B.HospNum
			    Left outer Join Build_File..tbCoAddress D on B.ZipCode = D.ZipCode
				Left Outer Join Laboratory..tbLabMaster F on A.IDNum = F.IdNum
				Left Outer Join Build_File..tbCoDoctor E on F.DoctorID = E.DoctorID
			   	Left Outer Join Build_File..tbCoCivilStatus C on C.CivilStatusID = B.CivilStatus
				Left Outer Join Build_File..tbCoCompany G on G.AccountNum = A.AccountNum
				Left Outer Join Build_File..tbCoAdmPackage P on P.PackageID = A.PackageID
				Left Outer Join patient_data..tbPatientHistory H on A.IdNum = H.IdNum
	    	where A.IdNum = @IdNum
		order by F.Transdate desc
     End

   Else
     Begin
	if exists(Select distinct(IsNull(HospNum,'')) From tbLabMaster where IdNum = @IdNum) 
	   begin
		Set @HospNum = (Select distinct(IsNull(HospNum,'')) From tbLabMaster where IdNum = @IdNum)
	   end
	else
	   begin
		Set @HospNum = (Select top 1 HospNum From BILLING..tbCashAssessment Where IdNum = @IdNum)
	   end;

	If IsNull(@HospNum,'') = '' Set @HospNum = @IdNum;

		IF IsNumeric(@HospNum)=1
		    Begin
		        Select top 1 B.HospNum, 
		    		@IdNum as IdNum, 
		    		isnull(B.LastName,'') + ', ' + isnull(B.FirstName,'') + ' ' + isnull(B.MiddleName,'') as PatientName, 
		    		B.Sex,
		    		case 
		      	   	     when B.birthdate is null then convert(varchar(3), IsNull(B.Age,''))
		      	   	     when B.birthdate <= '01/01/1900' then convert(varchar(3), IsNull(B.Age,''))
		      	   	     else Laboratory.dbo.fn_LabComputeAge(B.Birthdate,getdate()) 
		    	    end as Age,
		    		IsNull(C.CivilStatus,'') as CivilStatus,
--					cast(convert(varchar(10),IsNull(b.birthdate,''),101) as varchar(10)) as [BirthDate], 
					isnull(convert(varchar,b.birthdate,101),'') as [BirthDate],
		    		'OP' as RoomId, '' as RoomClass,
		    		isnull(B.HouseStreet,'')as HouseStreet, 
			    	isnull(B.Barangay,'') as Barangay, 
			    	isnull(B.ZipCode,'') as ZipCode,
			    	isnull(D.Town + ', '+ D.Province,'') as Province,
			    	Case When F.DoctorId = '0' Then F.OutsideDoctor 
					     Else IsNull(E.LastName,'') + ', ' + IsNull(E.FirstName,'') + ', M.D.' 
					End as Physician,
			    		isnull(F.RefNum,'') as RefNum,
			    		isnull(F.OrNum,'') as ORNum,
					'' as StationID,
					'' as Company,
					'' as BillingDate,
					'' as PhysicianID,
					'' as PackageID,
					'' as Package,
			        '' as  PHICDescription,
			        '' as AdmDiagnosis,
					isnull(B.Birthdate,'') as Birthday,
					getdate() as Admdate
	    	   	From Patient_Data..tbMaster B 
			    	Left outer Join Build_File..tbCoAddress D on B.ZipCode = D.ZipCode
					Left Outer Join Laboratory..tbLabMaster F on B.HospNum = F.HospNum
			    	Left Outer Join Build_File..tbCoDoctor E on F.DoctorID = E.DoctorID
					Left Outer Join Build_File..tbCoCivilStatus C on C.CivilStatusID = B.CivilStatus
	    	 	where B.HospNum = @HospNum
			  order by F.Transdate desc			
		    End
		Else
		    Begin

		        Select top 1 A.HospNum, 
	    		A.HospNum as IdNum, 
	    		isnull(A.LastName,'') + ', ' + isnull(A.FirstName,'') + ' ' + isnull(A.MiddleName,'') as PatientName, 
	    		A.Sex,
	    		case 
	      	   	   when A.birthdate is null then convert(varchar(3), IsNull(A.Age,''))
	      	   	   when A.birthdate <= '01/01/1900' then convert(varchar(3), IsNull(A.Age,''))
	      	   	   else Laboratory.dbo.fn_LabComputeAge(A.Birthdate,getdate()) 
	    	    	end as Age,
	    		IsNull(C.CivilStatus,'') as CivilStatus, 
--				case 
--			     	when convert(varchar(10),IsNull(A.BirthDate,'01/01/1900'),101) = '01/01/1900' then 'N/A'
--			     	else cast(convert(varchar(10),A.BirthDate,101) as varchar(10))
--				end as [BirthDate], 
				isnull(convert(varchar,A.BirthDate,101),'') as [BirthDate],
	    		'OP' as RoomId, '' as RoomClass,
	    		isnull(A.HouseStreet,'')as HouseStreet, 
	    		isnull(A.Barangay,'') as Barangay, 
	    		isnull(A.ZipCode,'') as ZipCode,
	    		isnull(D.Town + ', '+ D.Province,'') as Province,
				Case When IsNull(x.requestdocid,'') = '0' Then x.requestdocid
				     Else isnull(E.FirstName + ' ' + E.MiddleName + ' ' + E.LastName +', M.D.','') 
				End as Physician,
	    		isnull(F.RefNum,'') as RefNum,
	    		isnull(F.ORNum,'') as ORNum,
				'' as StationID,
				'' as AccountNum,
				'' as BillingDate,
				'' as PhysicianID,
				'' as Company,
				'' as PackageID,
				'' as Package,
			    '' as  PHICDescription,
			    '' as AdmDiagnosis,
				isnull(A.Birthdate,'') as Birthday,
				getdate() as AdmDate
	    	    From Patient_Data..tbCashPatient A
		    		left outer Join Build_File..tbCoAddress D on A.ZipCode = D.ZipCode
	            	Left Outer Join Laboratory..tbLabMaster F on A.HospNum = F.HospNum
				 	Left Outer Join billing..tbcashassessment x on A.HospNum = x.HospNum And x.RefNum = @RefNum 
					Left Outer Join Build_File..tbCoDoctor E on  x.requestdocid=E.DoctorID
					Left Outer Join Build_File..tbCoCivilStatus C on C.CivilStatusID = A.CivilStatusID
	    	   	where A.HospNum = @HospNum
				  	and x.revenueid='lb'
		      	order by F.Transdate desc
		End
	End