USE [MEDICARE]
GO

ALTER PROCEDURE [dbo].[Medic_ZipCodeSearch] 
   @strSearch as varchar(20)
AS
   Select IsNull(ZipCode,'') [ZipCode],
              IsNull(Town,'') [Town],
              IsNull(Province,'') [Province],
              RTrim(IsNull(Town,''))+', '+IsNull(Province,'') [TownProvince],
			  IsNull(Region,'') [Region]
      from Build_File..tbCoAddress
      Where ZipCode = @strSearch
GO


USE [PATIENT_DATA]
GO


ALTER PROCEDURE [sp_Adm_LoadPatient] --VBB 02.17.17
@HospNum AS VarChar(10)
AS

if exists(SELECT * FROM	tbMaster M WHERE M.HospNum = @HospNum)
begin

Select 	M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, 
	M.AccountNum, ISNULL(M.HouseStreet,'') HouseStreet, ISNULL(M.Barangay,'') Barangay, ISNULL(M.ZipCode,'') ZipCode,
	ISNULL(M.Sex,'') Sex, M.BirthDate, M.CivilStatus, ISNULL(M.Occupation,'') Occupation,
	ISNULL(M.Age,'') Age, ISNULL(M.TelNum,'') TelNum, ISNULL(M.PatientStatus,'') PatientStatus, ISNULL(M.OPDNum,'') OPDNum, 
	ISNULL(M.XrayNum,'') XrayNum, ISNULL(M.UltraNum,'') UltraNum, ISNULL(M.SSSGSISNum,'') SSSGSISNum,  ISNULL(M.CTNum,'') CTNum,
	ISNULL(M2.BirthPlace,'') BirthPlace, ISNULL(M2.NationalityID,'') NationalityID, ISNULL(M2.ReligionID,'') ReligionID,
	ISNULL(M2.Spouse,'') Spouse, ISNULL(M2.Father,'') Father, ISNULL(M2.Mother,'') Mother, ISNULL(M2.Employer,'') Employer,
	ISNULL(M2.EmployerAddress, '') EmployerAddress,	ISNULL(M2.EmployerTelNum,'') EmployerTelNum, ISNULL(M2.ContactName,'') ContactName, 
	ISNULL(M2.ContactAddress,'') ContactAddress, ISNULL(M2.ContactRelation,'') ContactRelation,	ISNULL(M2.ContactTelNum,'') ContactTelNum, 
	ISNULL(M.BloodType,'') BloodType, ISNULL(M2.MotherAddress,'') MotherAddress, ISNULL(M2.MotherTelNum, '') MotherTelNum,
	ISNULL(M2.FatherAddress,'') FatherAddress, ISNULL(M2.FatherTelNum,'') FatherTelNum, 
	ISNULL(M2.SpouseAddress,'') SpouseAddress, ISNULL(M2.SpouseTelNum,'') SpouseTelNum,
	ISNULL(M2.barangaycaptain,'') barangaycaptain ,
	ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], 
	ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , 
	ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName],
	ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], 
	ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], 
	ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] , ISNULL(M2.OfficeNum,'') [OfficeNum],
	ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email],
	ISNULL(M.Title,'')[Title],
	ISNULL(M.CellNum,'')[CellNum], 
	ISNULL(M.EmailAddress,'') [EmailAddress],
	ISNULL(M2.SpousePosition,'')[SpousePosition],
	ISNULL(M2.FatherOccupation,'') [FatherOccupation],
	ISNULL(M2.FatherTelNum,'') [FatherTelNum],
	ISNULL(M2.Employerposition,'')[EmployerPosition],
	ISNULL(M2.MotherOccupation,'')[MotherOccupation],
	--ISNULL(M.SSSGSISNum,'')[SSSGSIS], 
	ISNULL(M.SSSGSISNum,M2.PHICNum)as [SSSGSIS],
	ISNULL(M.GSISNum,'')[GSISNum], 
	ISNULL(M.SSSNum,'')[SSSNUm], 
	ISNULL(M.PatientType,'')[PatientClass],
	ISNULL(M.SeniorCitizenID,'')[SeniorCitizenID],
	ISNULL(M3.AllergyID1,'')[Allergy1],
	ISNULL(M3.AllergyID2,'')[Allergy2],
	ISNULL(M3.AllergyID3,'')[Allergy3],
	ISNULL(M3.AllergyID4,'')[Allergy4],
	ISNULL(M3.AllergyID5,'')[Allergy5],
	ISNULL(M2.PatientFindUs,'')[PatientFindUs],
	ISNULL(M2.PatientFindUsOtherRemarks,'')[PatientFindUsOtherRemarks],
	ISNULL(M2.FatherEmployer,'')[FatherEmployer],
	ISNULL(M2.MotherEmployer,'')[MotherEmployer],
	ISNULL(M2.DateMarried,'')[DateMarried],
	ISNULL(M.FileNum,'')[FileNum],
	ISNULL(M.MSSNum,'')[MSSNum]
	
FROM 	tbMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum
					LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum
WHERE 	M.HospNum = @HospNum

end

else

begin

Select 	M.HospNum, isnull(M.LastName,'') as LastName, isnull(M.FirstName,'') as FirstName, isnull(M.MiddleName,'') as MiddleName, 
	M.AccountNum, M.HouseStreet, M.Barangay, M.ZipCode,
	M.Sex, M.BirthDate, M.CivilStatus, M.Occupation,
	M.Age, M.TelNum, M.PatientStatus, M.OPDNum, 
	M.XrayNum, M.UltraNum, M.SSSGSISNum,  M.CTNum,
	M2.BirthPlace, M2.NationalityID, M2.ReligionID,
	M2.Spouse, M2.Father, M2.Mother, M2.Employer, M2.EmployerAddress,
	M2.EmployerTelNum, M2.ContactName, M2.ContactAddress, M2.ContactRelation,
	M2.ContactTelNum, M.BloodType, M2.MotherAddress, M2.MotherTelNum,
	M2.FatherAddress, M2.FatherTelNum, M2.SpouseAddress, M2.SpouseTelNum,
	M2.barangaycaptain ,
	ISNULL(M2.Position,'') [Position], ISNULL(M2.EmploymentType,0) [EmploymentType], 
	ISNULL(M2.EmploymentLocation,0) [EmploymentLocation], ISNULL(M2.EmployerFax,'') [EmployerFax] , 
	ISNULL(M2.EmployerEmail,'') [EmployerEmail], ISNULL(M2.SpouseEmpName,'') [SpouseEmpName],
	ISNULL(M2.SpouseEmpAddress,'') [SpouseEmpAddress], ISNULL(M2.SpouseEmpTelNum,'') [SpouseEmpTelNum], 
	ISNULL(M2.SpouseEmpFax,'') [SpouseEmpFax] , ISNULL(M2.SpouseEmpEmail,'') [SpouseEmpEmail], 
	ISNULL(M2.PhilHealth,0) [PhilHealth], ISNULL(M2.Insurance,'')[Insurance], ISNULL(M2.HouseHold,'') [HouseHold] ,ISNULL(M2.OfficeNum,'') [OfficeNum],
	ISNULL(M.CellNum,'') [CellNum], ISNULL(M.Email,'') [Email],
	ISNULL(M.Title,'')[Title],
	ISNULL(M.CellNum,'')[CellNum], 
	ISNULL(M.EmailAddress,'') [EmailAddress],
	ISNULL(M2.SpousePosition,'')[SpousePosition],
	ISNULL(M2.FatherOccupation,'') [FatherOccupation],
	ISNULL(M2.FatherTelNum,'') [FatherTelNum],
	ISNULL(M2.Employerposition,'')[EmployerPosition],
	ISNULL(M2.MotherOccupation,'')[MotherOccupation],
	''[SSSGSIS], 
	''[GSISNum], 
	''[SSSNUm], 
	''[PatientClass],
	''[SeniorCitizenID],
	''[Allergy1],
	''[Allergy2],
	''[Allergy3],
	''[Allergy4],
	''[Allergy5],
	''[PatientFindUs],
	''[PatientFindUsOtherRemarks],
	''[FatherEmployer],
	''[MotherEmployer],
	''[DateMarried],
	''[FileNum],
	''[MSSNum]
FROM 	tbPastMaster M LEFT OUTER JOIN tbMaster2 M2 ON M.HospNum = M2.HospNum
					LEFT OUTER JOIN tbmasterAllergies M3 on M.HospNum = M3.HospNum
WHERE 	M.HospNum = @HospNum

end



GO

GO
---------------------------------------------------------------------------
USE [PATIENT_DATA]
GO



ALTER PROCEDURE [sp_AOPD_Load_TbMaster] --'1326' --Revised 02.17.17 VBB
   @Hospnum as varchar(10)    
AS

  Select m.hospnum, 
	isnull(m.lastname,'') as Lastname, 
	isnull(m.firstname,'') as FirstName,  
	isnull(m.middlename,'') as MiddleName, 
	isnull(m.housestreet,'')as HouseStreet, 
	isnull(m.barangay,'') as Barangay, 
	m.Zipcode,
	(case when isnull(c.town,'') + ',  ' + isnull(c.province,'') = ', ' then  '' 
	         else isnull(c.town,'') + ',  ' + isnull(c.province,'') End) as province, 
	isnull(m.telnum,'') as TelNum, 
	m.birthdate as BirthDate, 
	isnull(m.sex,'') as Sex, 
	isnull(m.age,0) as Age,
	isnull(m2.Employer,'') as Employer,
	isnull(m2.EmployerAddress,'') as EmployerAddress, 
	isNull(m2.EmployerTelNum,'') as EmployerTelNum,
    M.CivilStatus , 
	isnull(convert(varchar(10),m.GovDiscountExpiry,101),'') as GovDiscountExpiry,	
	isNull(M.BloodType,'') [BloodType] ,
	isNull(M2.BirthPlace,'') [BirthPlace], 
	isNull(M.Occupation,'') [Occupation] ,
	isNull(N.Nationality,'') [Nationality] ,
	isNull(R.Religion,'') [Religion], 	
	isnull(M2.NationalityID,'') AS NationalityID,
    isnull(M2.ReligionID,'') AS ReligionID,
    isnull(M.Title,'') AS Title, 
	m.SSSGSISNum, 
	m2.phicnum,
    ISNULL(M2.DateMarried,'')as DateMarried
	--ISNULL(M.SSSGSISNum,M2.PHICNum)as PhicNum,
	--ISNULL(M.GSISNum,'')as GSISNum,
	--ISNULL(M.SSSNum,'')as SSSNum
	

    From Patient_Data..tbMaster m 
	Left Outer Join Build_File..tbcoAddress c on m.Zipcode = c.Zipcode
	Left Outer Join Patient_Data..tbMaster2 m2 on m.HospNum=m2.HospNum
	--left outer join tbPatient P on m.HospNum=p.HospNum
	Left Outer Join BUILD_FILE..tbcoNationality N on N.NationalityID = M2.NationalityID
	Left Outer Join BUILD_FILE..tbcoReligion R on R.ReligionID = M2.ReligionID
    where m.hospnum = @HospNum



GO