USE PATIENT_DATA
go

IF NOT EXISTS (SELECT top 1 1 FROM PATIENT_DATA..tbDOH_HospitalInfo)
BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_HospitalInfo (HospitalID)
		SELECT 1
END


IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOHSetup' and COLUMN_NAME='IsAllowDOHVer9')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOHSetup
	ADD IsAllowDOHVer9 bit not null default(0)
END


/* general info classification*/
IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='HospitalID')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD HospitalID varchar(20)
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHhfhudcode')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHhfhudcode varchar(19)
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHServiceCapability')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHServiceCapability int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHGeneral')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHGeneral int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHSpecialty')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHSpecialty int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHSpecialtySpecify')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHSpecialtySpecify varchar(250)
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHTraumaCapability')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHTraumaCapability int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHNatureOfOwnerShip')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHNatureOfOwnerShip int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHGovernment')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHGovernment int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHNational')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHNational int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHLocal')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHLocal int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHPrivate')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHPrivate int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='DOHOwnershipOthers')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD DOHOwnershipOthers varchar(250)
END
GO

/*quality management*/

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='QMQualityMgmtType')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD QMQualityMgmtType int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='QMDescription')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD QMDescription VARCHAR(MAX)
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='QMCertifyingBody')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD QMCertifyingBody varchar(250)
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='QMPhilHealthAccreditation')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD QMPhilHealthAccreditation int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='QMValidityFrom')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD QMValidityFrom datetime
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='QMValidityTo')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD QMValidityTo datetime
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='AuthorizedBeds')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD AuthorizedBeds int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='ImplementingBeds')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD ImplementingBeds int
END
GO

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbDOH_HospitalInfo' and COLUMN_NAME='AdditionalBeds')
BEGIN
	ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
	ADD AdditionalBeds int
END
GO



USE PATIENT_DATA
GO
 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_HospitalInfo') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_HospitalInfo
	(
		HospitalID varchar(20)	
	)
END
GO
	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_HospitalInfo where HospitalID ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_HospitalInfo (HospitalID) SELECT '1'
	END
	GO

 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_ServiceCapability') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_ServiceCapability
	(
		scCode int NULL,
		scDesc varchar(100) NULL,		
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability where scCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability (scCode,scDesc) SELECT 1,'General'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability where scCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability (scCode,scDesc) SELECT 2,'Specialty'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability where scCode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability (scCode,scDesc) SELECT 3,'Infirmary'
	END
	GO
------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_ServiceCapability_General') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_ServiceCapability_General
	(
		scgCode int NULL,
		scgDesc varchar(100) NULL,
		scCode int NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability_General where scgCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability_General (scgCode,scgDesc,scCode) SELECT 1,'Level 1 Hospital',1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability_General where scgCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability_General (scgCode,scgDesc,scCode) SELECT 2,'Level 2 Hospital',1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability_General where scgCode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability_General (scgCode,scgDesc,scCode) SELECT 3,'Level 3 Hospital',1
	END
	GO
------------------------------------------------------------------------------

 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_ServiceCapability_Specialty') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_ServiceCapability_Specialty
	(
		scsCode int NULL,
		scsDesc varchar(100) NULL,		
		scCode int NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability_Specialty where scsCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability_Specialty (scsCode,scsDesc,scCode) SELECT 1,'Treats a particular disease',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability_Specialty where scsCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability_Specialty (scsCode,scsDesc,scCode) SELECT 2,'Treats a particular organ',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability_Specialty where scsCode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability_Specialty (scsCode,scsDesc,scCode) SELECT 3,'Treats a particular class of patients',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_ServiceCapability_Specialty where scsCode ='4')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_ServiceCapability_Specialty (scsCode,scsDesc,scCode) SELECT 4,'Others',2
	END
	GO
------------------------------------------------------------------------------

 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_TraumaCapability') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_TraumaCapability
	(
		tcCode int NULL,
		tcDesc varchar(100) NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_TraumaCapability where tcCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_TraumaCapability (tcCode,tcDesc) SELECT 1,'Trauma Capable'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_TraumaCapability where tcCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_TraumaCapability (tcCode,tcDesc) SELECT 2,'Trauma Receiving'
	END
	GO

------------------------------------------------------------------------------

 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_NatureOfOwnership') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_NatureOfOwnership
	(
		noCode int NULL,
		noDesc varchar(100) NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership where noCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership (noCode,noDesc) SELECT 1,'Government'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership where noCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership (noCode,noDesc) SELECT 2,'Private'
	END
	GO

------------------------------------------------------------------------------


 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_NatureOfOwnership_Government') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_NatureOfOwnership_Government
	(
		nogCode int NULL,
		nogDesc varchar(100) NULL,
		noCode int NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Government where nogCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Government (nogCode,nogDesc,noCode) SELECT 1,'National',1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Government where nogCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Government (nogCode,nogDesc,noCode) SELECT 2,'Local',1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Government where nogCode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Government (nogCode,nogDesc,noCode) SELECT 3,'State Universities and Colleges (SUCs)',1
	END
	GO

------------------------------------------------------------------------------

 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_NOO_Government_National') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_NOO_Government_National
	(

		nognCode int NULL,
		nognDesc varchar(100) NULL,
		nogCode int NULL,
		noCode int NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NOO_Government_National where nognCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NOO_Government_National (nognCode,nognDesc,nogCode,noCode) SELECT 1,'DOH Retained / Renationalized',1,1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NOO_Government_National where nognCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NOO_Government_National (nognCode,nognDesc,nogCode,noCode) SELECT 2,'DILG - PNP',1,1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NOO_Government_National where nognCode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NOO_Government_National (nognCode,nognDesc,nogCode,noCode) SELECT 3,'DND - AFP',1,1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NOO_Government_National where nognCode ='4')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NOO_Government_National (nognCode,nognDesc,nogCode,noCode) SELECT 4,'DOJ',1,1
	END
	GO

------------------------------------------------------------------------------


 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_NOO_Government_Local') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_NOO_Government_Local
	(
		noglCode int NULL,
		noglDesc varchar(100) NULL,
		nogCode int NULL,
		noCode int NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NOO_Government_Local where noglCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NOO_Government_Local (noglCode,noglDesc,nogCode,noCode) SELECT 1,'Province',2,1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NOO_Government_Local where noglCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NOO_Government_Local (noglCode,noglDesc,nogCode,noCode) SELECT 2,'City',2,1
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NOO_Government_Local where noglCode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NOO_Government_Local (noglCode,noglDesc,nogCode,noCode) SELECT 3,'Municipality',2,1
	END
	GO

------------------------------------------------------------------------------


 IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_NatureOfOwnership_Private') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_NatureOfOwnership_Private
	(
		nopCode int NULL,
		nopDesc varchar(100) NULL,
		noCode int NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Private where nopCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Private (nopCode,nopDesc,noCode) SELECT 1,'Single Proprietorship',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Private where nopCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Private (nopCode,nopDesc,noCode) SELECT 2,'Partnership',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Private where nopCode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Private (nopCode,nopDesc,noCode) SELECT 3,'Corporation',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Private where nopCode ='4')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Private (nopCode,nopDesc,noCode) SELECT 4,'Religious',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Private where nopCode ='5')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Private (nopCode,nopDesc,noCode) SELECT 5,'Civic Organization',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Private where nopCode ='6')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Private (nopCode,nopDesc,noCode) SELECT 6,'Foundation',2
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_NatureOfOwnership_Private where nopCode ='7')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_NatureOfOwnership_Private (nopCode,nopDesc,noCode) SELECT 7,'Cooperative',2
	END
	GO
------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_QualityMgmtType') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_QualityMgmtType
	(
		qmCode int NULL,
		qmDesc varchar(250) NULL
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_QualityMgmtType where qmCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_QualityMgmtType (qmCode,qmDesc) SELECT 1,'ISO Certified'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_QualityMgmtType where qmCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_QualityMgmtType (qmCode,qmDesc) SELECT 2,'International Accreditation'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_QualityMgmtType where qmCode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_QualityMgmtType (qmCode,qmDesc) SELECT 3,'PhilHealth Accreditation'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_QualityMgmtType where qmCode ='4')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_QualityMgmtType (qmCode,qmDesc) SELECT 4,'PCAHO'
	END
	GO

------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_PhilHealthAccreditation') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_PhilHealthAccreditation
	(
		qmpCode int NULL,
		qmpDesc varchar(250) NULL,
		qmCode int NULL,
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_PhilHealthAccreditation where qmpCode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_PhilHealthAccreditation (qmpCode,qmpDesc,qmCode) SELECT 1,'Basic Participation','3'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_PhilHealthAccreditation where qmpCode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_PhilHealthAccreditation (qmpCode,qmpDesc,qmCode) SELECT 2,'Advanced Participation','3'
	END
	GO



------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_TestingGroup') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_TestingGroup
	(
		gtestcode int NULL,
		gtestdesc varchar(500) NULL
		
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_TestingGroup where gtestcode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_TestingGroup (gtestcode,gtestdesc) SELECT 1,'Total number of medical imaging tests (all types including x-rays, ultrasound, CT scans, etc.)'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_TestingGroup where gtestcode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_TestingGroup (gtestcode,gtestdesc) SELECT 2,'Total number of laboratory and diagnostic tests (all types, excluding medical imaging)'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_TestingGroup where gtestcode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_TestingGroup (gtestcode,gtestdesc) SELECT 3,'Blood Service Facilities'
	END
	GO

------------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA.sys.objects WHERE object_id = OBJECT_ID(N'tbDOH_Test') AND type in (N'U'))
BEGIN
	CREATE TABLE PATIENT_DATA..tbDOH_Test
	(
		testcode int NULL,
		testdesc varchar(500) NULL,
		gtestcode int NULL
		
	)
END
GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='1')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 1,'X-Ray','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='2')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 2,'Ultrasound','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='3')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 3,'CT-Scan','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='4')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 4,'MRI','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='5')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 5,'Mammography','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='6')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 5,'Angiography','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='7')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 7,'Linear Accelerator','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='8')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 8,'Dental X-Ray','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='9')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 9,'Others','1'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='10')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 10,'Urinalysis','2'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='11')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 11,'Fecalysis','2'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='12')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 12,'Hematology','2'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='13')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 13,'Clinical chemistry','2'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='14')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 14,'Immunology/Serology/HIV','2'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='15')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 15,'Microbiology (Smears/Culture & Sensitivity)','2'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='16')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 16,'Surgical Pathology','2'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='17')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 17,'Autopsy','2'
	END
	GO
	
	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='18')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 18,'Cytology','2'
	END
	GO

	IF NOT EXISTS (SELECT 1 FROM PATIENT_DATA..tbDOH_Test where testcode ='19')
	BEGIN
		INSERT INTO PATIENT_DATA..tbDOH_Test (testcode,testdesc,gtestcode) SELECT 19,'Number of Blood units Transfused','3'
	END
	GO
--------------------------------------------------------------------------------



/* ADDITIONAL FIELDS : QUALITY MANAGEMENT - INTERNATIONAL,PHIC,PCAHO VALIDITY DATE */
		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMISOCertified')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMISOCertified int
		END
		GO

		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMInternationalAccreditation')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMInternationalAccreditation int
		END
		GO

		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMPCAHO')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMPCAHO int
		END
		GO

		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMInternationalValidityFrom')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMInternationalValidityFrom datetime NULL
		END
		GO
		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMInternationalValidityTo')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMInternationalValidityTo datetime NULL
		END
		GO
		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMPHICValidityFrom')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMPHICValidityFrom datetime NULL
		END
		GO
		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMPHICValidityTo')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMPHICValidityTo datetime NULL
		END
		GO
		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMPCAHOValidityFrom')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMPCAHOValidityFrom datetime NULL
		END
		GO
		IF NOT EXISTS (select 1 from PATIENT_DATA.INFORMATION_SCHEMA.columns where TABLE_NAME='tbDOH_HospitalInfo' AND COLUMN_NAME='QMPCAHOValidityTo')
		BEGIN
			ALTER TABLE PATIENT_DATA..tbDOH_HospitalInfo
			ADD QMPCAHOValidityTo datetime NULL
		END
		GO















ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[StreetAddress] [varchar](100) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[Municipality] [varchar](100) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[Province] [varchar](100) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[Specialty] [int] NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[TraumaCapability] [int] NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[LocalParticular] [varchar](100) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[OwnershipGovOthers] [varchar](100) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[OwnershipPrivateOthers] [varchar](100) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[QMISOCertified] [bit] NOT NULL DEFAULT(0)
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[QMISOCertifiedValPeriod] [varchar](50) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[QMIntlAccreditation] [bit] NOT NULL DEFAULT(0)
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[QMIntlAccreditationValPeriod] [varchar](50) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[QMPhicAccreditation] [bit] NOT NULL DEFAULT(0)
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[QMPhicAccreditationValPeriod] [varchar](50) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[QMPCAHO] [bit] NOT NULL DEFAULT(0)
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[QMPCAHOValPeriod] [varchar](50) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[PhilhealthAccreditationParticular] [varchar](50) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[HospitalName] [varchar](100) NULL
ALTER TABLE [Password].[dbo].[tbHospitalInfo2] ADD	[HospitalAddress] [varchar](100) NULL




USE [Patient_Data]
GO

/****** Object:  StoredProcedure [dbo].[sp_DOH_Report_Section1]    Script Date: 10/21/2022 4:00:32 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[sp_DOH_Report_Section1]--'01/01/2013','01/05/2013'
--@StartDate varchar(10),
--@EndDate varchar(10)
AS

SELECT     tbHospitalInfo.Title AS HealthFacilityName,
       	   tbHospitalInfo.Address1 AS CompleteAddress,
	   Region, ContactNo, FaxNo, EmailAddress, 
	   CASE WHEN Isnull(IsBirthingHome,0) = 1 THEN 'X' ELSE '_' END AS IsBirthingHome, 
	   CASE WHEN Isnull(IsInfirmary,0) = 1 THEN 'X' ELSE '' END AS IsInfirmary,
           CASE WHEN Isnull(IsAcuteChronic,0) = 1 THEN 'X' ELSE '_' END AS IsAcuteChronic, 
           CASE WHEN Isnull(IsCustodialPsychiatric,0) = 1 THEN 'X' ELSE '_' END AS IsCustodialPsychiatric, 
	   CASE WHEN Isnull(IsFirstLevel,0) = 1 THEN 'X' ELSE '_' END AS IsFirstLevel,
	   CASE WHEN Isnull(IsSecondLevel,0) = 1 THEN 'X' ELSE '_' END AS IsSecondLevel, 
           CASE WHEN Isnull(IsThirdLevel,0) = 1 THEN 'X' ELSE '_' END AS IsThirdLevel, 
           CASE WHEN Isnull(IsSpecial,0) = 1 THEN 'X' ELSE '_' END AS IsSpecial,
	   SpecialSpecify, 
           NatureOfOwnership, 
           OtherAgencySpecify, 
           CASE WHEN Isnull(IsCertifiedISO,0) = 1 THEN 'X' ELSE '_' END AS IsCertifiedISO,
	   CertifiedISOSpecify, 
           CASE WHEN Isnull(IsPCAHO,0) = 1 THEN 'X' ELSE '_' END AS IsPCAHO,
           CASE WHEN Isnull(IsOtherCertifying,0) = 1 THEN 'X' ELSE '_' END AS IsOtherCertifying,
           OtherCertifyingSpecify, 
           CertifiedISOValidity, 
           PCAHOValidity, 
           OtherCertifyingValidity, AuthorizedBed, ActualBed, AdditionalBed,
	   CASE WHEN Isnull(IsSpecial,0) = 0 THEN 'X' ELSE '_' END AS IsGeneral,
           CASE WHEN Isnull(NatureOfOwnership,1) = 1 THEN 'X' ELSE '_' END AS IsNational, 
	   CASE WHEN Isnull(NatureOfOwnership,1) = 2 THEN 'X' ELSE '_' END AS IsLocal, 
           CASE WHEN Isnull(NatureOfOwnership,1) = 3 THEN 'X' ELSE '_' END AS IsOtherAgency,
           CASE WHEN Isnull(NatureOfOwnership,1) = 4 THEN 'X' ELSE '_' END AS IsSingleProprietorship,
           CASE WHEN Isnull(NatureOfOwnership,1) = 5 THEN 'X' ELSE '_' END AS IsReligious,
           CASE WHEN Isnull(NatureOfOwnership,1) = 6 THEN 'X' ELSE '_' END AS IsCivicOrganization,
           CASE WHEN Isnull(NatureOfOwnership,1) = 7 THEN 'X' ELSE '_' END AS IsFoundation,
        tbHospitalInfo2.StreetAddress,
        tbHospitalInfo2.Municipality,
        tbHospitalinfo2.Province,
        tbHospitalInfo2.Specialty,
        tbHospitalInfo2.SpecialSpecify,
        tbhospitalInfo2.TraumaCapability,
        tbHospitalInfo2.LocalParticular,
        tbHospitalInfo2.OwnershipGovOthers,
        tbHospitalInfo2.OwnershipPrivateOthers,
 
 		tbHospitalInfo2.QMISOCertified,
		tbHospitalInfo2.QMISOCertifiedValPeriod,
		tbHospitalInfo2.QMIntlAccreditation,
		tbHospitalInfo2.QMIntlAccreditationValPeriod,
		tbHospitalInfo2.QMPhicAccreditation,
		tbHospitalInfo2.QMPhicAccreditationValPeriod,
		tbHospitalInfo2.QMPCAHO,
		tbHospitalInfo2.QMPCAHOValPeriod,
		tbhospitalinfo2.PhilhealthAccreditationParticular
		--,
		--dbo.Fn_DOH_GEtBOR(@StartDate,@EndDate,tbHospitalInfo2.AuthorizedBed) as BOR      


FROM Password..tbHospitalInfo tbHospitalInfo
LEFT OUTER JOIN Password..tbHospitalInfo2 tbHospitalInfo2
ON tbHospitalInfo.HospitalID = tbHospitalInfo2.HospitalID
WHERE tbHospitalInfo.HospitalID = 1

GO