USE [Medicare]
GO
/****** Object:  StoredProcedure [dbo].[sp_Medic_GetForm3]    Script Date: 10/11/2016 17:38:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









ALTER PROCEDURE [dbo].[sp_Medic_GetForm3]
@IDNum As Varchar(10) 
 AS

BEGIN

			DECLARE	@HospitalName As Varchar(50),
					@Accreditation As Varchar(20),
					@Street As Varchar(30),
					@Barangay As Varchar(30),
					@Municipality As Varchar(30),
					@Province As Varchar(30),
					@ZipCode As Varchar(4)
			
			
			Select	@HospitalName=HospitalName,
				@Accreditation=AccrNum,
				@Street=Street,
				@Barangay=Barangay,
				@Municipality=Municipality,
				@Province=Province,
				@ZipCode=ZipCode
			
			From MEDICARE..tbMedHospital;

	If IsNumeric(@IDNum) = 1
	  Begin
		Select Rtrim(Isnull(M.Lastname,'')) LastName,
			Rtrim(Isnull(M.Firstname,'')) Firstname,
			Rtrim(Isnull(M.Middlename,'')) Middlename,
			P.ADMDate,
			P.DCRDate,
			Patient_Data.dbo.Compute_Age(M.BirthDate,P.ADMDate) Age,
			M.Sex,
			Ph.ADMdiagnosis,
			Ph.ChiefComplaint ,
			Ph.ADMImpression,
			Ph.FinalDiagnosis,
			Ph.Procedures,
			Ph.CourseWard,
			NP.BriefHistory,
			NP.PEskin,
			NP.PEheadneck,
			NP.PEthorax,
			NP.PEabdomen,
			NP.PEextremeties,
			NP.PElympnodes,
			NP.PEgenitalia,
			NP.PErectal,
			NP.PEneurological,
			NV.BP,
			NV.Temperature,
			NV.PulseRate,
			NV.RespRate,
			Case Isnull(P.ResultID,'')
				When '1' then 'Recovered'
				When '2' then 'Improved'
				When '3' then 'Unimproved'
				When '4' then 'Died'
				When '5' then 'Check-Up'
			End Condition,
		         Case Isnull(P.AttendingDr1,'')--IsNull(D.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = P.AttendingDr1--D.DocCode
			           Group by DoctorID ), '' )
		         End Physician,	
		         Case IsNull(S.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = S.DocCode
			           Group by DoctorID ), '' )
		         End Surgeon,
			Case IsNull(S.DatePerformed,'')
				When '' Then ''
				Else
				    S.DatePerformed
			End Operationdate,
		         Case IsNull(A.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = A.DocCode
			           Group by DoctorID ), '' )
		         End Anesthesiologist,
			Case IsNull(A.DatePerformed,'')
				When '' Then ''
				Else
				    A.DatePerformed
			End Anesthedate,
			IsNull(A.ServicePerformed,'') Anesthesia,
			@HospitalName As Hospital,
			@Accreditation As Accreditation,
			@Street As Street,
			@Barangay As Barangay,
			@Municipality As Municipality,
			@Province As Province,
			@ZipCode As Zipcode,
			P.RoomID As Room,
			Patient_Data.dbo.Fn_GetCompleteName(P.HospNum) As PName,
			 Case IsNull(P.AdmittingDr,'')
			    When '' then ''
			    Else
			      IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
			                      from Build_File..tbCoDoctor
			                      Where DoctorID = P.AdmittingDr
			           Group by DoctorID ), '' )
			 End AdmittingDr,			
			 Case IsNull(P.AdmittingClerk,'')
			    When '' then ''
			    Else
			      IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') ) 
			                      from Password..tbPasswordMaster
			                      Where EmployeeID = P.AdmittingClerk
			           Group by EmployeeID ), '' )
			 End AdmittingClerk,
			P.HospNum As HospNum,
			Lower(Ltrim(Rtrim(Cast(Ph.AdmDiagnosis  As Varchar(5000))))) ADMDiagnosis,
			Ph.CourseWard,
			NP.Others As Remarks,
			NV.O2Sat,
			case 
				when Len(Ltrim(Rtrim(Cast(Ph.CourseWard  As Varchar(5000))))) <=0 then 0
				else 1
			End As CourseCount,
			case 
				when Len(Ltrim(Rtrim(Cast(Ph.Procedures  As Varchar(5000))))) <=0 then 0
				else 1
			End As ProceduresCount,
			Lower(Ltrim(Rtrim(Cast(Ph.ChiefComplaint  As Varchar(5000))))) As ChiefComplaints,
			P.ResultID,
			P.DispositionID,
			NP.GeneralSurvey as GeneralSurvey
		
		From PATIENT_DATA..tbPatientHistory Ph
			Inner Join PATIENT_DATA..tbPatient P
				On Ph.IDNum = P.IDNum
			Inner Join PATIENT_DATA..tbMaster M
				On P.Hospnum = M.Hospnum
			Left Outer Join Station..tbNurseProfile NP
				On Ph.IDNum = NP.IDNum
			Left Outer Join 
				(Select top 1 * From Station..tbNurseVitalSigns 
				 Where IDNUm = @IDNum
				 Order By Cast(Marker As Int) Desc
				 ) As	NV
				On Ph.IDNum = NV.IDNum
			Left Outer Join Medicare..tbMedDoctors D
				On Ph.IDNum = D.IDNum and D.DocType ='P'
			Left Outer Join Medicare..tbMedDoctors S
				On Ph.IDNum = S.IDNum and S.DocType ='S'
			Left Outer Join Medicare..tbMedDoctors A
				On Ph.IDNum = A.IDNum and A.DocType ='A'
		Where Ph.IDNum=@IDNum;--'503886';
	  END
	Else
      Begin
		Select	Rtrim(Isnull(M.Lastname,'')) LastName,
			Rtrim(Isnull(M.Firstname,'')) Firstname,
			Rtrim(Isnull(M.Middlename,'')) Middlename,
			P.ADMDate,
			P.DCRDate,
			Patient_Data.dbo.Compute_Age(M.BirthDate,P.ADMDate) Age,
			M.Sex,
			'' ADMdiagnosis,
			'' ChiefComplaint ,
			'' ADMImpression,
			'' FinalDiagnosis,
			'' Procedures,
			'' CourseWard,
			NP.BriefHistory,
			NP.PEskin,
			NP.PEheadneck,
			NP.PEthorax,
			NP.PEabdomen,
			NP.PEextremeties,
			NP.PElympnodes,
			NP.PEgenitalia,
			NP.PErectal,
			NP.PEneurological,
			NV.BP,
			NV.Temperature,
			NV.PulseRate,
			NV.RespRate,
			'' Condition,
		         Case Isnull(P.DoctorID1,'')--IsNull(D.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = P.DoctorID1--D.DocCode
			           Group by DoctorID ), '' )
		         End Physician,	
		         Case IsNull(S.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = S.DocCode
			           Group by DoctorID ), '' )
		         End Surgeon,
			Case IsNull(S.DatePerformed,'')
				When '' Then ''
				Else
				    S.DatePerformed
			End Operationdate,
		         Case IsNull(A.DocCode,'')
		            When '' then ''
		            Else
		              IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
		                              from Build_File..tbCoDoctor
		                              Where DoctorID = A.DocCode
			           Group by DoctorID ), '' )
		         End Anesthesiologist,
			Case IsNull(A.DatePerformed,'')
				When '' Then ''
				Else
				    A.DatePerformed
			End Anesthedate,
			IsNull(A.ServicePerformed,'') Anesthesia,
			@HospitalName As Hospital,
			@Accreditation As Accreditation,
			@Street As Street,
			@Barangay As Barangay,
			@Municipality As Municipality,
			@Province As Province,
			@ZipCode As Zipcode,
			'OPD' As Room,
			Patient_Data.dbo.Fn_GetCompleteName(P.HospNum) As PName,
			 Case IsNull(P.DoctorID1,'')
			    When '' then ''
			    Else
			      IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ',MD') 
			                      from Build_File..tbCoDoctor
			                      Where DoctorID = P.DoctorID1
			           Group by DoctorID ), '' )
			 End AdmittingDr,			
			 Case IsNull(P.UserID,'')
			    When '' then ''
			    Else
			      IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') ) 
			                      from Password..tbPasswordMaster
			                      Where EmployeeID = P.UserID
			           Group by EmployeeID ), '' )
			 End AdmittingClerk,
			P.HospNum As HospNum,
			'' ADMDiagnosis,
			'' CourseWard,
			NP.Others As Remarks,
			NV.O2Sat,
			0 As CourseCount,
			0 As ProceduresCount,
			'' As ChiefComplaints,
			'' ResultID,
			'' DispositionID,
			 NP.GeneralSurvey as GeneralSurvey
		
		From PATIENT_DATA..tbOutPatientHistory Ph
			Inner Join PATIENT_DATA..tbOutPatient P
				On Ph.IDNum = P.IDNum
			Inner Join PATIENT_DATA..tbMaster M
				On P.Hospnum = M.Hospnum
			Left Outer Join Station..tbNurseProfile NP
				On Ph.IDNum = NP.IDNum
			Left Outer Join  
				(Select top 1 * From Station..tbNurseVitalSigns 
				 Where IDNUm = @IDNum
				 Order By Cast(Marker As Int) Desc
				 ) As	NV
				On Ph.IDNum = NV.IDNum
			Left Outer Join Medicare..tbMedDoctors D
				On Ph.IDNum = D.IDNum and D.DocType ='P'
			Left Outer Join Medicare..tbMedDoctors S
				On Ph.IDNum = S.IDNum and S.DocType ='S'
			Left Outer Join Medicare..tbMedDoctors A
				On Ph.IDNum = A.IDNum and A.DocType ='A'
		Where Ph.IDNum=@IDNum;--'503886';
	  End
END