Attribute VB_Name = "modConfig"
Option Explicit

Public sCONFIG(23000, 3) As String

Public Function CONFIG(pKey As Integer) As String
    CONFIG = sCONFIG(pKey, 2)
    
   ' MsgBox sCONFIG(5, 2)
     
End Function

Public Sub LoadConfig()
    On Error GoTo ErrTap
    Dim RecS As New ADODB.Recordset
    Dim Section As String
    
    'ReDim sCONFIG(22000, 3)
    Erase sCONFIG
    
    With RecS
        .Open "SELECT * FROM Reports..tbHospitalConfig WITH (NOLOCK)  ", pclsUser.SQLCONNECTION, adOpenDynamic, adLockReadOnly
        Do While Not .EOF
            Section = .Fields("Section").Value & ""
            sCONFIG(Section, 1) = .Fields("Config").Value & ""
            sCONFIG(Section, 2) = .Fields("Data").Value & ""
            .MoveNext
        Loop
        .Close
    End With
    Exit Sub
ErrTap:
    MsgBox Err.Description & " Open Hospital Info (F2), DOH Settings tab, then click Reset DB."
    
End Sub

Public Sub DefaultConfig()

    pclsUser.SQLCONNECTION.Execute "DELETE FROM Reports..tbHospitalConfig"
     
    ' Chapter 5
    
        AddKey "5001", "5. ServiceType 1. Medicine", "|10|", "5 Service type for Medicine ex: |10|11| "
        AddKey "5002", "5. ServiceType 2. Obstetrics", "|16|", "5 Service type for Obstetrics"
        AddKey "5003", "5. ServiceType 3. Gynecology", "|9|28|", "5 Service type for Gynecology"
        AddKey "5004", "5. ServiceType 4. Pediatrics", "|14|", "5 Service type for Pediatrics"
        AddKey "5005", "5. ServiceType 56.SurgeryPedia", "|23|", "5 Service type for Surgery"
        AddKey "5008", "5. ServiceType 89.Newborn", "|13|", "5 Service type for Newborn"
        'AddKey "511", "V. ServiceType Surgery", "|23|"
    
    ' Chapter 8
    
        'AddKey "8001", "8. InFacilityDeliveries Include", "AND SUBSTRING(C.DiagnosisID,1,3) in('O82','O80')", "8. InFacilityDeliveries ICD Codes"
    
    
    
    ' Chapter 12
        '1,X-Ray,1
        AddKey "12011", "X-Ray Request Table", "Radiology..tbXRRequest", ""
        AddKey "12012", "X-Ray Revenue Code", "XR"
        AddKey "12013", "X-Ray Include Clause", "swfin  <> 'C' AND Code NOT IN ('112','155')"  ' Exclude mammo, dental
        
        '2,Ultrasound,1
        AddKey "12021", "Ultrasound Request Table", "Radiology..tbULRequest"
        AddKey "12022", "Ultrasound Revenue Code", "US"
        AddKey "12023", "UltrasoundInclude Clause", "swfin  <> 'C'"
        
        '3,CT-Scan,1
        AddKey "12031", "CT-Scan Request Table", "Radiology..tbCTRequest"
        AddKey "12032", "CT-Scan Revenu Codee", "CT"
        AddKey "12033", "CT-Scan Include Clause", "swfin  <> 'C'"
             
        '4,MRI,1
        AddKey "12041", "MRI Request Table", "Radiology..tbMRIRequest"
        AddKey "12042", "MRI Revenue Code", "MI"
        AddKey "12043", "MRI Include Clause", "swfin  <> 'C'"
             
        '5,Mammography,1
        AddKey "12051", "Mammography Request Table", "Radiology..tbXRRequest"
        AddKey "12052", "Mammography Revenue Code", "XR"
        AddKey "12053", "Mammography Include Clause", "Code IN ('112')"
             
        '6,Angiography,1
        AddKey "12061", "Cathlab Request Table", "Clinical_Area..tbCathLabMaster"
        AddKey "12062", "Cathlab Revenue Code", "CQ"
        AddKey "12063", "Cathlab Include Clause", "RequestStatus = 'W'"
        
        '7,"Linear Accelerator",1
        AddKey "12071", "Linear Accelerator Request Table", ""
        AddKey "12072", "Linear Accelerator Revenue Code", ""
        AddKey "12073", "Linear AcceleratorInclude Clause", ""
            
        '8,"Dental X-Ray",1
        AddKey "12081", "Dental Request Table", "Radiology..tbXRRequest", ""
        AddKey "12082", "Dental Revenue Code", "XR"
        AddKey "12083", "Dental Include Clause", "Code IN ('155') AND swfin  <> 'C'"
         
    
        '10,Urinalysis,2
        AddKey "12101", "Urinalysis Request Table", "LABORATORY..tbLABMaster", ""
        AddKey "12102", "Urinalysis Revenue Code", "LB", ""
        AddKey "12103", "Urinalysis Include Clause", "SectionID NOT IN ('A','C','H') AND RequestStatus = 'W' AND ItemID='317'"
        
        ''11' /* Fecalysis */
        AddKey "12111", "Fecalysis Request Table", "LABORATORY..tbLABMaster"
        AddKey "12112", "Fecalysis Revenue Code", "LB"
        AddKey "12113", "Fecalysis Include Clause", "SectionID IN ('C') AND RequestStatus = 'W'"
    
        '12,Hematology,2
        AddKey "12121", "Hematology Request Table", "LABORATORY..tbLABMaster"
        AddKey "12122", "Hematology Revenue Code", "LB"
        AddKey "12123", "Hematology Include Clause", "SectionID IN ('A') AND RequestStatus = 'W'"
        
        '13,"Clinical Chemistry",2
        AddKey "12131", "Clinical Chemistry Request Table", "LABORATORY..tbLABMaster"
        AddKey "12132", "Clinical Chemistry Revenue Code", "LB"
        AddKey "12133", "Clinical Chemistry Include Clause", "SectionID IN ('H') AND RequestStatus = 'W'"
        
        '14,Immunology/Serology/HIV,2
        AddKey "12141", "Immunology Request Table", "LABORATORY..tbLABMaster"
        AddKey "12142", "Immunology Revenue Code", "LB"
        AddKey "12143", "Immunology Include Clause", "SectionID IN ('E') AND RequestStatus = 'W' AND ItemId NOT IN ('262')"
        
        '15,"Microbiology (Smears/Culture & Sensitivity)",2
        AddKey "12151", "Microbiology Request Table", "LABORATORY..tbLABMaster"
        AddKey "12152", "Microbiology Revenue Code", "LB"
        AddKey "12153", "Microbiology Include Clause", "SectionID IN ('G') AND RequestStatus = 'W'"
        
        '16,"Surgical Pathology",2
        AddKey "12161", "Surgical Pathology Request Table", "LABORATORY..tbLABMaster"
        AddKey "12162", "Surgical Pathology Revenue Code", "LB"
        AddKey "12163", "Surgical Pathology Include Clause", "SectionID IN ('F') AND ItemId IN ('287')"
        
        '17,Autopsy,2
        AddKey "12171", "Autopsy Table", "LABORATORY..tbLABMaster"
        AddKey "12172", "Autopsy Revenue Code", "LB"
        AddKey "12173", "Autopsy Include Clause", "SectionID IN ('F') AND ItemId IN ('238')"
        
        '18,Cytology,2
        AddKey "12181", "Cytology Table", "LABORATORY..tbLABMaster"
        AddKey "12182", "Cytology Revenue Code", "LB"
        AddKey "12183", "Cytology Include Clause", "SectionID IN ('F') AND ItemId IN ('253')"
        
        '19,"Number of Blood units Transfused",3
        AddKey "12191", "Number of Blood units Transfused Request Table", "LABORATORY..tbLABMaster"
        AddKey "12192", "Number of Blood units Transfused Revenue Code", "LB"
        AddKey "12193", "Number of Blood units Include Clause", "SectionID IN ('E') AND ItemId IN ('262')"
        
        
        AddKey "8001", "8. InFacilityDeliveries Include", "AND SUBSTRING(C.DiagnosisID,1,3) in('O82','O80')", "8. InFacilityDeliveries ICD Codes"
        
'        AddKey "8001", "8.DeliveryType  1InFacilityDeliveryCS", "Substring(C.DiagnosisID,1,3) in ('O82', 'O80')", ""
'        AddKey "8002", "8.DeliveryType  2LiveBirthVaginalNormal", "Substring(C.DiagnosisID,1,3) in('O80')"
'        AddKey "8003", "8.DeliveryType  3LIveBirthCSection", "Substring(C.DiagnosisID,1,3) in('O82')"
'        AddKey "8004", "8.DeliveryType  4NormalDelivery", "C.DiagnosisID in('Z37.0')"
'
'        AddKey "9001", "9.ServiceType   1Medicine", "ServiceID1 ='10'"
'        AddKey "9002", "9.ServiceType   2Surgical", "ServiceID1 ='23'"
'        AddKey "9003", "9.ServiceType   3NonSurgical", "ServiceID1 not in('23','10','14','13')"
'
'        AddKey "13001", "13.ERClass     1ERTransFromFacility", ""
'
'        AddKey "14001", "14.DeathType    1Neonatal", "Case When IsNull(DeathType, '13') In ('3','4','5','6') Then 1 Else 0 End [Neonatal Death],"
'        AddKey "14002", "14.DeathType    2Infant", ""
'        AddKey "14003", "14.DeathType    3Maternal", ""
'        AddKey "14004", "14.DeathType    4DOA", ""
'        AddKey "14011", "14.ServiceType  1Newborn", ""
        
 

AddKey "1", "General Medicine Service", "('10')"
AddKey "2", "Newborn Service", "('13')"
AddKey "3", "Pediatrics Service", "('14')"
AddKey "4", "Surgical Service", "('23')"
AddKey "5", "Pedia Age", "14"
AddKey "6", "OR Services Revenue", " IN ('OR')"
  
 

AddKey "9001", "9.ServiceType   1NonSurgical", "ServiceID1 not in('23','10','14','13')"

AddKey "13001", "13.ERClassification 1.ER to Inpatient", "ErClassification in ('7') AND OpdStatus NOT IN ('R')"


AddKey "14001", "14.DeathType 1Neonatal", "Case When IsNull(DeathType, '13') In ('3','4','5','6') Then 1 Else 0 End [Neonatal Death],"
AddKey "14002", "14.DeathType 2Infant", "Case When IsNull(DeathType, '13') IN ('7') Then 1 Else 0 End [Infant Death],"
AddKey "14003", "14.DeathType 3Maternal", "Case When IsNull(DeathType, '13') IN ('8') Then 1 Else 0 End [Maternal Death],"
AddKey "14004", "14.DeathType 4DOA", "Case When IsNull(DeathType, '13') in ('11') Then 1 Else 0 End [DOA],"
AddKey "14011", "14.ServiceType 1Newborn", "Case when ServiceID In ('13') then 1 else 0 end as [NewBornDeaths]"

AddKey "17001", "17.ORCategory 1MajorOperation  ", "(tbORMasterFile.Category IN ('3') OR tbORMasterFile.Category  IN('4'))"


AddKey "18001", "18.ORCategory 1MinorOperation", "IN ('1','2')"

AddKey "21001", "21.GLGroup 1SalaryWages", "IN ('EXP10','EXP07')"
AddKey "21002", "21.GLGroup 2EmployeeBenefit", "IN ('EXP01','EXP19')"
AddKey "21003", "21.GLGroup 3AmountMeds", "IN ('EXP10','EXP07')"
AddKey "21004", "21.GLGroup 4AmountMedSupplies", "IN ('EXP03')"
AddKey "21005", "21.GLGroup 5AmountUtilities", "IN ('1','2')"
AddKey "21006", "21.GLGroup 6AmountNonMedService", "NOT IN ('EXP01', 'EXP19', 'EXP10', 'EXP07', 'EXP03','EXP13')"
AddKey "21007", "21.GLGroup 7AmountInfrastracture", "IN ('ASF10','ASF10-01')"
AddKey "21008", "21.GLGroup 8AmountEquiptment", "IN ('ASF12')"

AddKey "22001", "22.Class 1PHICClass", "A.CLASS IN('H')"
AddKey "22002", "22.Class 2Private", "A.CLASS IN('I')"
AddKey "22003", "22.Class 3OthersExempt", "A.CLASS NOT IN ('I','R','H')"
AddKey "22004", "22.Class 4DOH", "A.CLASS IN('DOH')"
AddKey "22005", "22.Class 5LG", "A.CLASS IN('LG')"
AddKey "22006", "22.Class 6PrivateDonation", "A.CLASS IN('PRIVATEDONATION')"
AddKey "22007", "22.Class 7Donor", "A.CLASS IN('DONOR')"
AddKey "22011", "22.RevenueID 1Other", "B.RevenueID in ('SD','SF')"



    
        
End Sub

Public Sub ShowConfig()

    'Me.MousePointer = 11

    With pclsUser.MEDSYSClasses
        '.CreateMSYS
        .useclasses = True
        .InitWithDB
        .Msys.Program.StaffAllowEdit = True
        .msysMain.Browse.Program.StaffAllowadd = True
        .msysMain.Browse.Program.StaffAllowDelete = True
    End With
    
    With pclsUser.MEDSYSClasses.msysMain.Browse
        .INIT
        .PropTable = "tbHospitalConfig"
        .PropDatabaseName = "Reports"
        .PropOperation = "AED"
        .PropWindowTitle = "DOH Hospital Config"
        .PropFieldNameID = "Section"
        .PropFieldList = "Section,Config,Data"
        '.PropFieldNameDesc = "ItemName"
        
        Set .PropParentWindow = frmHospitalInfo
        .propfindbeforelisting = False
        .PropFormUseOnlyBrowseFields = False
        .propfindfield1 = "Section"
        .propfindfield2 = "Config"
        'Set .PropParentWindow = Me
        .PropFieldSizes = "800,3000,5500"
        '.PropControlNameToUpdate = "txtCode"

    End With

    pclsUser.MEDSYSClasses.msysMain.newbrowse

    'Me.MousePointer = 0
    
End Sub

Public Sub AddKey(pSectionID As String, pConfigName As String, pData As String, Optional pName As String)
    
    On Error GoTo ErrTrap
    Dim RecS As New ADODB.Recordset
    
    With RecS
        .Open "Reports..tbHospitalConfig", pclsUser.SQLCONNECTION, adOpenDynamic, adLockOptimistic
        .AddNew
        If pName = "" Then
            .Fields("Name") = pConfigName
        Else
            .Fields("Name") = pName
        End If
        .Fields("Section") = pSectionID
        .Fields("Config") = pConfigName
        .Fields("Data") = pData
        .Update
        .Close
    End With
    Exit Sub
ErrTrap:
    
    MsgBox Err.Description
End Sub

Public Function GetKey(Key As String) As String
    
    Dim RecS As New ADODB.Recordset
    
    With RecS
        .Open "SELECT DATA FROM Reports..tbHospitalConfig WITH (NOLOCK) WHERE [Config]='" & Key & "'", pclsUser.SQLCONNECTION, adOpenDynamic, adLockReadOnly
        If .EOF = False Then
            GetKey = .Fields(0).Value & ""
        End If
        .Close
    End With

End Function

Public Sub ResetDB()
    On Error GoTo ErrTrap
    
    Dim SQLStr As String
    
    pclsUser.SQLCONNECTION.Execute "DROP TABLE Reports.[dbo].[tbHospitalConfig] "
    
    SQLStr = ""
    SQLStr = SQLStr & "    CREATE TABLE Reports.[dbo].[tbHospitalConfig]("
    SQLStr = SQLStr & "        [ID] [bigint] IDENTITY(1,1) NOT NULL,"
    SQLStr = SQLStr & "        [Name] [varchar](250) NULL,"
    SQLStr = SQLStr & "        [Section] [varchar](50) NULL,"
    SQLStr = SQLStr & "        [Config] [varchar](250) NULL,"
    SQLStr = SQLStr & "        [Data] [varchar](255) NULL"
    SQLStr = SQLStr & "    ) ON [PRIMARY]"
    pclsUser.SQLCONNECTION.Execute SQLStr
    
    SQLStr = ""
    SQLStr = SQLStr & "    CREATE UNIQUE NONCLUSTERED INDEX [by_ID] ON  Reports.[dbo].[tbHospitalConfig]"
    SQLStr = SQLStr & "    ("
    SQLStr = SQLStr & "        [ID] Asc"
    SQLStr = SQLStr & "    )"
    pclsUser.SQLCONNECTION.Execute SQLStr
    
    SQLStr = ""
    SQLStr = SQLStr & "    CREATE UNIQUE NONCLUSTERED INDEX [by_Config] ON  Reports.[dbo].[tbHospitalConfig]"
    SQLStr = SQLStr & "    ("
    SQLStr = SQLStr & "        [SECTION]"
    SQLStr = SQLStr & "    )"
    pclsUser.SQLCONNECTION.Execute SQLStr

    MsgBox "Done."
'
'    CREATE TABLE Reports.[dbo].[tbHospitalConfig](
'        [ID] [bigint] IDENTITY(1,1) NOT NULL,
'        [Name] [varchar](50) NULL,
'        [Section] [varchar](50) NULL,
'        [Key] [varchar](50) NULL,
'        [Data] [varchar](60) NULL
'    ) ON [PRIMARY]
'    GO
'
'    CREATE UNIQUE NONCLUSTERED INDEX [by_ID] ON  Reports.[dbo].[tbHospitalConfig]
'    (
'        [ID] Asc
'    )
'    GO
'
'    CREATE UNIQUE NONCLUSTERED INDEX [by_Section_Key] ON  Reports.[dbo].[tbHospitalConfig]
'    (
'        [Section],
'        [Key]
'    )
'    GO
    Exit Sub
ErrTrap:
    pclsUser.addlog Err.Description
    Resume Next

End Sub