USE [Patient_Data]
GO
/****** Object:  StoredProcedure [dbo].[sp_med_Certificate2]    Script Date: 08/29/2012 17:35:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_med_Certificate2]

	@IDNum As VarChar(10),
	@RecordsID as Varchar(10)

AS

--DELETE tbMedrecCertificate;
truncate table tbMedrecCertificate;


--Delete tbMedRecCertificateDx;
truncate table tbMedRecCertificateDx;

INSERT tbMedrecCertificate(idnum,lastname,firstname,middlename,age,sex,civilstatus,nationality,housestreet,
barangay,town,province,telnum,employer,admdate,dcrdate,drname,finaldiagnosis,procedures,occupation,
remarks,PTR,License,[Service],RecordsOfficer,Disposition,DoctorTitle)
Select 	Top 1
	Patient.IDNum,
	Upper(isnull(Master.LastName,'')), 
	Upper(isnull(Master.FirstName,'')), 
	Upper(isnull(Master.MiddleName,'')), 
	isnull(Patient.Age,0),
	Upper(Case isnull(Master.Sex,'') When 'F' Then 'female' 
		   Else 'male' End) AS Sex,
	Upper(Case isnull(Master.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,
	Upper(isnull(Nation.Nationality,'')),
	Upper(isnull(Master.HouseStreet,'')),
	Upper(isnull(Master.Barangay,'')),
	Upper(isnull(Town.Town,'')),
	Upper(isnull(Town.Province,'')),
	Upper(isnull(Master.TelNum,'')),
	Upper(isnull(Master2.Employer,'')),
	convert(varchar(25),Patient.AdmDate,107),
	Convert(varchar(25),Patient.DcrDate,107),
	Upper(RTrim(IsNull(Doctor.FirstName,'')) + ' ' + 
                           Case When RTrim(IsNull(Doctor.MiddleName,'')) = '' then ''
                                     Else   Left(RTrim(IsNull(Doctor.MiddleName,'')),1) + '.' End +  
		' ' + RTrim(IsNull(Doctor.LastName,''))  + 
                Replace(isnull(Doctor.Title,''), 'MD', '')
              ) As DrName,/*
	Convert(Varchar(8000),isnull(History.FinalDiagnosis,'')), 
	Convert(Varchar(8000),isnull(History.Procedures,'')),*/
	isnull(History.FinalDiagnosis,''), 
	isnull(History.Procedures,''),
	Upper(isnull(Master.Occupation,'')),
	Upper(isnull(P2.Remarks,'')),
	Upper(isnull(Doctor.PTR,'')),
	Upper(isnull(Doctor.License,'')),
	Upper(isnull(Service.Service,'')),
	Upper(isnull(pass.LastName,'') + ', ' + isnull(pass.FirstName,'')) as recordOfficer,
    --Upper(Disposition.Disposition),
	Upper(Case Isnull(Patient.ResultID,'')
		When '1' then 'Recovered'
		When '2' then 'Improved'
		When '3' then 'Unimproved'
		When '4' then 'Died'
		When '5' then 'Check-up'
	End) Disposition,

	Upper(RTrim(IsNull(Doctor.FirstName,'')) + ' ' + 
                           Case When RTrim(IsNull(Doctor.MiddleName,'')) = '' then ''
                                     Else   Left(RTrim(IsNull(Doctor.MiddleName,'')),1) + '.' End +  
		' ' + RTrim(IsNull(Doctor.LastName,''))  + 
                Case when isnull(Doctor.Title,'') = '' then ', M.D.'
                     Else ', ' + isnull(Doctor.Title,'')
   		End
              ) As DrName
FROM Patient_Data..tbPatient Patient 
	Left Outer Join Patient_Data..tbMaster Master On Patient.HospNum = Master.HospNum
	Left Outer Join Patient_Data..tbPatient2 P2 On Patient.IdNum=P2.IdNum
	Left Outer Join Patient_Data..tbMaster2 Master2 on Patient.HospNum = Master2.HospNum
	Left Outer Join Build_File..tbCoNationality Nation On Master2.NationalityID = Nation.NationalityID
	Left Outer Join Build_File..tbCoAddress Town On Master.ZipCode = Town.ZipCode
	Left Outer Join Build_File..tbCoDoctor Doctor On Patient.AttendingDr1 = Doctor.DoctorID
	Left Outer Join Patient_Data..tbPatientHistory History On Patient.IDNum = History.IDNum
	Left Outer Join Build_File..tbCoService Service on Patient.ServiceID= Service.ServiceID
	Left Outer Join Password..tbPasswordMaster Pass on Pass.EmployeeID = @RecordsID 
	Left Outer Join Build_File..tbCoDisposition Disposition On Patient.DispositionID = Disposition.DispositionID
Where Patient.IDNum = @IDNum;

If Exists(Select IdNum from tbPatientHistory Where IdNum = @IDNum and not Procedures is null)
  Begin
     Insert into tbMedRecCertificateDx
        Select @IDNum, 'B', Procedures from tbPatientHistory Where IdNum = @IDNum;
  End
Else
  Begin
     Insert into tbMedRecCertificateDx
        Values(@IDNum, 'B', 'No operation done.');
  End;

If Exists(Select IdNum from tbPatientHistory Where IdNum = @IDNum and not FinalDiagnosis is null)
  Begin
     Insert into tbMedRecCertificateDx
        Select @IDNum, 'A', FinalDiagnosis from tbPatientHistory Where IdNum = @IDNum;
  End
Else
  Begin
     Insert into tbMedRecCertificateDx
        Values(@IDNum, 'A', 'No Final Diagnosis');
  End;

If Exists(Select IdNum from tbPatientHistory Where IdNum = @IDNum and not Remarks is null)
  Begin
     Insert into tbMedRecCertificateDx
        Select @IDNum, 'C', Remarks from tbPatientHistory Where IdNum = @IDNum;
  End
Else
  Begin
     Insert into tbMedRecCertificateDx
        Values(@IDNum, 'C', 'No Remarks');
  End;