if exists (select * from information_schema.tables where table_name = 'LU_QBP_Flag')
    drop table LU_QBP_Flag
GO
CREATE TABLE [dbo].[LU_QBP_Flag](
	[Code] [int] NOT NULL,
	[DisplayCode] [varchar](50) NULL,
	[UserDescription] [varchar](255) NULL,
	[DefaultDescription] [varchar](255) NULL,
	[CIHIValue] [varchar](50) NULL,
	[ValidFrom] [datetime] NULL,
	[ValidTo] [datetime] NULL,
	[InterfaceIn] [varchar](50) NULL,
	[HospitalID] [int] NULL,
	[LUText] [varchar](255) NULL,
    CONSTRAINT [PK_LU_QBP_Flag] PRIMARY KEY CLUSTERED (	[Code] ASC)
)
GO
Insert into LU_QBP_Flag(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, HospitalID, LUText)
values(1, 'Y', 'Yes - Meet one of the Quality Based Procedures', 'Yes - Meet one of the Quality Based Procedures', 'Y', '2001/04/01', '2099/03/31', 'Y', 0, '')
GO
Insert into LU_QBP_Flag(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, HospitalID, LUText)
values(2, 'P', 'Possible QBP - Other criteria such as Diagnosis and/or Intervention could meet criteria to qualify', 'Possible QBP - Other criteria such as Diagnosis and/or Intervention could meet criteria to qualify', 'P', '2001/04/01', '2099/03/31', 'P', 0, '')
GO
delete from u_fields where fieldid between 80000 and 80050
GO
Insert into U_Fields(FieldID, InHouseDesc, SystemName, PropertyName, FieldAccess, IsGroup)
select 80000, 'QBP Flag section', 'QBP Flag section', 'QBP Flag section', 1, 1 union
select 80001, 'Bilateral Primary Joint Replacement', 'QBP_BilateralJointReplacement', 'QBP_BilateralJointReplacement', 1, 0 union
select 80002, 'Chronic Obstructive Pulmonary Disease', 'QBP_ChronicObstructivePulmonary', 'QBP_ChronicObstructivePulmonary', 1, 0 union
select 80003, 'Congestive Heart Failure (CHF)', 'QBP_CongestiveHeartFailure', 'QBP_CongestiveHeartFailure', 1, 0 union
select 80004, 'CS Abdominal', 'QBP_CS_Abdominal', 'QBP_CS_Abdominal', 1, 0 union
select 80005, 'CS Bone', 'QBP_CSBone', 'QBP_CSBone', 1, 0 union
select 80006, 'CS Breast Delayed Reconstruction', 'QBP_CSBreastDelayedReconstruction', 'QBP_CSBreastDelayedReconstruction', 1, 0 union
select 80007, 'CS Breast Immediate Reconstruction', 'QBP_CSBreastImmediateReconstruction', 'QBP_CSBreastImmediateReconstruction', 1, 0 union
select 80008, 'CS Breast No Reconstruction', 'QBP_CSBreastNoReconstruction', 'QBP_CSBreastNoReconstruction', 1, 0 union
select 80009, 'CS Colorectal', 'QBP_CSColorectal', 'QBP_CSColorectal', 1, 0 union
select 80010, 'CS Endocrine-Other', 'QBP_CSEndocrineOther', 'QBP_CSEndocrineOther', 1, 0 union
select 80011, 'CS Esophagus', 'QBP_CSEsophagus', 'QBP_CSEsophagus', 1, 0 union
select 80012, 'CS Gastric', 'QBP_CSGastric', 'QBP_CSGastric', 1, 0 union
select 80013, 'CS Gentourinary', 'QBP_CSGentourinary', 'QBP_CSGentourinary', 1, 0 union
select 80014, 'CS Gynecology', 'QBP_CSGynecology', 'QBP_CSGynecology', 1, 0 union
select 80015, 'CS HNK', 'QBP_CSHNK', 'QBP_CSHNK', 1, 0 union
select 80016, 'CS Liver', 'QBP_CSLiver', 'QBP_CSLiver', 1, 0 union
select 80017, 'CS Lung', 'QBP_CSLung', 'QBP_CSLung', 1, 0 union
select 80018, 'CS Neuro Brain', 'QBP_CSNeuroBrain', 'QBP_CSNeuroBrain', 1, 0 union
select 80019, 'CS Neuro Spinal', 'QBP_CSNeuroSpinal', 'QBP_CSNeuroSpinal', 1, 0 union
select 80020, 'CS Non-Site Specific', 'QBP_CSNonSiteSpecific', 'QBP_CSNonSiteSpecific', 1, 0 union
select 80021, 'CS Pancreas', 'QBP_CSPancreas', 'QBP_CSPancreas', 1, 0 union
select 80022, 'CS Prostate', 'QBP_CSProstate', 'QBP_CSProstate', 1, 0 union
select 80023, 'CS Skin Lymph Node', 'QBP_CSSkinLymphNode', 'QBP_CSSkinLymphNode', 1, 0 union
select 80024, 'CS Skin Soft Tissue', 'QBP_CSSkinSoftTissue', 'QBP_CSSkinSoftTissue', 1, 0 union
select 80025, 'CS Soft Tissue', 'QBP_CSSoftTissue', 'QBP_CSSoftTissue', 1, 0 union
select 80026, 'CS Thorax', 'QBP_CSThorax', 'QBP_CSThorax', 1, 0 union
select 80027, 'CS Thyroid', 'QBP_CSThyroid', 'QBP_CSThyroid', 1, 0 union
select 80028, 'Hip Fracture', 'QBP_HipFracture', 'QBP_HipFracture', 1, 0 union
select 80029, 'Integrated Corneal Transplant (Day Surgery)', 'QBP_CornealTransplant', 'QBP_CornealTransplant', 1, 0 union
select 80030, 'Knee Athroscopy', 'QBP_KneeAthroscopy', 'QBP_KneeAthroscopy', 1, 0 union
select 80031, 'Non-Cardia Vascular Lower Extremity Occlusive', 'QBP_VascularLower', 'QBP_VascularLower', 1, 0 union
select 80032, 'Non-Cardiac Vascular Aortic Aneurysm (AA)', 'QBP_VascularAortic', 'QBP_VascularAortic', 1, 0 union
select 80033, 'Non-Emergent Spine (Instru-IP Surgery)', 'QBP_SpineInstruIP', 'QBP_SpineInstruIP', 1, 0 union
select 80034, 'Non-Emergent Spine (Non-Instru-Day Surgery)', 'QBP_SpineNonInstruDay', 'QBP_SpineNonInstruDay', 1, 0 union
select 80035, 'Non-Emergent Spine (Non-Instru-IP Surgery)', 'QBP_SpineNonInstruIP', 'QBP_SpineNonInstruIP', 1, 0 union
select 80036, 'Non-Routine and Bilateral Cataracts', 'QBP_BilateralCataracts', 'QBP_BilateralCataracts', 1, 0 union
select 80037, 'Pneumonia', 'QBP_Pneumonia', 'QBP_Pneumonia', 1, 0 union
select 80038, 'Shoulder (Arthroplasties)', 'QBP_ShoulderArthroplasties', 'QBP_ShoulderArthroplasties', 1, 0 union
select 80039, 'Shoulder (Other)', 'QBP_ShoulderOther', 'QBP_ShoulderOther', 1, 0 union
select 80040, 'Shoulder (Repairs)', 'QBP_ShoulderRepairs', 'QBP_ShoulderRepairs', 1, 0 union
select 80041, 'Shoulder (Reverse Arthroplaties)', 'QBP_ShoulderReverseArthroplaties', 'QBP_ShoulderReverseArthroplaties', 1, 0 union
select 80042, 'Stroke Acute Hemorrhage', 'QBP_StrokeAcuteHemorrhage', 'QBP_StrokeAcuteHemorrhage', 1, 0 union
select 80043, 'Stroke Acute Ischemic or Unspecified', 'QBP_StrokeAcuteIschemic', 'QBP_StrokeAcuteIschemic', 1, 0 union
select 80044, 'Stroke Acute Transient Ischemic Attach (TIA)', 'QBP_StrokeAcuteTransient', 'QBP_StrokeAcuteTransient', 1, 0 union
select 80045, 'Stroke Endovascular Therapy (EVT)', 'QBP_StrokeEndovascularTherapy', 'QBP_StrokeEndovascularTherapy', 1, 0 union
select 80046, 'Unilateral Primary Hip Replacement', 'QBP_UnilateralHipReplacement', 'QBP_UnilateralHipReplacement', 1, 0 union
select 80047, 'Unilateral Primary Knee Replacement', 'QBP_UnilateralKneeReplacement', 'QBP_UnilateralKneeReplacement', 1, 0 union
select 80048, 'Tonsillectomy', 'QBP_Tonsillectomy', 'QBP_Tonsillectomy', 1, 0 union
select 80049, 'Non Cancer Hysterectomy', 'QBP_NonCancerHysterectomy', 'QBP_NonCancerHysterectomy', 1, 0 union
select 80050, 'Retinal', 'QBP_Retinal', 'QBP_Retinal', 1, 0 


GO
if not exists (select * from u_usersfields where userid = 1 and fieldid = 80000)
insert into [dbo].[U_UsersFields](AppID, RoleID, UserID, FieldID, SortOrder, GroupID, MultipleIndex, MandatoryStatus, Needed, FieldSize, DataType, SubAppID, LookUpTable, CIHI_ID, Header_CIHI_English, Header_User, FieldEnabled, FieldVisible, DefaultValue, ParamProcID, ParamOrder, ParamName, ParamDataType, ParamSize, NeedForUserDef, MainDataType, UNoteCode)
select AppID, RoleID = 0, UserID, FieldID, 
    SortOrder = maxorder + fieldid - 80000 + 10, 
    GroupID = 70, 
    MultipleIndex = 0, 
    MandatoryStatus = 1000, 
    Needed = 0, 
    FieldSize = (case when fieldid = 80000 then 0 else 1 end), 
    DataType = (case when fieldid = 80000 then 0 else 50 end), 
    SubAppID = 0, 
    LookUpTable = (case when fieldid = 80000 then '' else 'LU_QBP_Flag' end), 
    CIHI_ID = '', 
    Header_CIHI_English = InHouseDesc, Header_User = InHouseDesc, 
    FieldEnabled = 2, FieldVisible = 2, 
    DefaultValue = null, 
    ParamProcID = (case when fieldid = 80000 then 0 else 48 end), 
    ParamOrder = fieldid - 80000, 
    ParamName = (case when fieldid = 80000 then '' else 'p' + SystemName end), 
    ParamDataType = (case when fieldid = 80000 then 0 else 3 end), 
    ParamSize = 0, 
    NeedForUserDef = 0, MainDataType = null, UNoteCode = null
from (select userid, appid, max(sortorder) maxorder from u_usersfields where appid in (200, 201, 202, 401) group by userid, appid) m,
(select * from u_fields where fieldid between 80000 and 80050) f
GO
if not exists (select * from information_schema.tables where table_name = 'AB_QBP_Flag')
CREATE TABLE [dbo].[AB_QBP_Flag](
	[AbstractID] [int] NOT NULL,
    QBP_BilateralJointReplacement int, 
    QBP_ChronicObstructivePulmonary int, 
    QBP_CongestiveHeartFailure int, 
    QBP_CS_Abdominal int, 
    QBP_CSBone int, 
    QBP_CSBreastDelayedReconstruction int, 
    QBP_CSBreastImmediateReconstruction int, 
    QBP_CSBreastNoReconstruction int, 
    QBP_CSColorectal int, 
    QBP_CSEndocrineOther int, 
    QBP_CSEsophagus int, 
    QBP_CSGastric int, 
    QBP_CSGentourinary int, 
    QBP_CSGynecology int, 
    QBP_CSHNK int, 
    QBP_CSLiver int, 
    QBP_CSLung int, 
    QBP_CSNeuroBrain int, 
    QBP_CSNeuroSpinal int, 
    QBP_CSNonSiteSpecific int, 
    QBP_CSPancreas int, 
    QBP_CSProstate int, 
    QBP_CSSkinLymphNode int, 
    QBP_CSSkinSoftTissue int, 
    QBP_CSSoftTissue int, 
    QBP_CSThorax int, 
    QBP_CSThyroid int, 
    QBP_HipFracture int, 
    QBP_CornealTransplant int, 
    QBP_KneeAthroscopy int, 
    QBP_VascularLower int, 
    QBP_VascularAortic int, 
    QBP_SpineInstruIP int, 
    QBP_SpineNonInstruDay int, 
    QBP_SpineNonInstruIP int, 
    QBP_BilateralCataracts int, 
    QBP_Pneumonia int, 
    QBP_ShoulderArthroplasties int, 
    QBP_ShoulderOther int, 
    QBP_ShoulderRepairs int, 
    QBP_ShoulderReverseArthroplaties int, 
    QBP_StrokeAcuteHemorrhage int, 
    QBP_StrokeAcuteIschemic int, 
    QBP_StrokeAcuteTransient int, 
    QBP_StrokeEndovascularTherapy int, 
    QBP_UnilateralHipReplacement int, 
    QBP_UnilateralKneeReplacement int, 
    QBP_Tonsillectomy int,
    QBP_NonCancerHysterectomy int,
    QBP_Retinal int,
    CONSTRAINT [PK_AB_QBP_Flag] PRIMARY KEY CLUSTERED (	AbstractID ASC)
)
GO
if not exists (select * from information_schema.columns where table_name = 'H_HospitalProfile' and column_name = 'EnableQBPFlag')
    alter table H_HospitalProfile add EnableQBPFlag int
GO

