Attribute VB_Name = "modLocalDBNewFields"
Option Explicit

Public Sub eClaimsUtility()
    Dim query As String
    query = "USE MEDICARE; "
    query = query & "IF NOT EXISTS (SELECT TABLE_NAME "
    query = query & "FROM INFORMATION_SCHEMA.COLUMNS "
    query = query & "WHERE TABLE_CATALOG = 'MEDICARE' AND TABLE_NAME = 'tbMedEclaimsUtility') "
    query = query & "BEGIN "
    query = query & "CREATE TABLE tbMedEclaimsUtility "
    query = query & "( "
    query = query & "Id INT IDENTITY(1,1), UtilityType VARCHAR(25), UtilityValue VARCHAR(50) "
    query = query & ") "
    query = query & "END "
    ExecuteAlter query
    
    ' [SUFFIXES] = "JRA;JR.;SR.;JR;SR;VII;VI;V;IV;III;II;I"
    query = "IF NOT EXISTS (SELECT Id "
    query = query & "FROM MEDICARE..tbMedEclaimsUtility "
    query = query & "WHERE UtilityType = 'SUFFIXES') "
    query = query & "BEGIN "
    query = query & "INSERT INTO MEDICARE..tbMedEclaimsUtility "
    query = query & "(UtilityType, UtilityValue) "
    query = query & "VALUES "
    query = query & "('SUFFIXES', 'JRA;JR.;SR.;JR;SR;VII;VI;V;IV;III;II;I') "
    query = query & "END "
    ExecuteAlter query
    
    ' [CF42020-057] = "03/01/2021" (Extension Advisory [CF42021-011] "10/01/2021")
    query = "IF NOT EXISTS (SELECT Id "
    query = query & "FROM MEDICARE..tbMedEclaimsUtility "
    query = query & "WHERE UtilityType = 'CF42020-057') "
    query = query & "BEGIN "
    query = query & "INSERT INTO MEDICARE..tbMedEclaimsUtility "
    query = query & "(UtilityType, UtilityValue) "
    query = query & "VALUES "
    query = query & "('CF42020-057', '10/01/2021') "
    query = query & "END "
    ExecuteAlter query
    
    ' [DOCUMENTSIZELIMIT] = "2"
    query = "IF NOT EXISTS (SELECT Id "
    query = query & "FROM MEDICARE..tbMedEclaimsUtility "
    query = query & "WHERE UtilityType = 'DOCUMENTSIZELIMIT') "
    query = query & "BEGIN "
    query = query & "INSERT INTO MEDICARE..tbMedEclaimsUtility "
    query = query & "(UtilityType, UtilityValue) "
    query = query & "VALUES "
    query = query & "('DOCUMENTSIZELIMIT', '2') "
    query = query & "END "
    ExecuteAlter query
    
    ' [VERSION] = "85" baseline version value
    query = "IF NOT EXISTS (SELECT Id "
    query = query & "FROM MEDICARE..tbMedEclaimsUtility "
    query = query & "WHERE UtilityType = 'VERSION') "
    query = query & "BEGIN "
    query = query & "INSERT INTO MEDICARE..tbMedEclaimsUtility "
    query = query & "(UtilityType, UtilityValue) "
    query = query & "VALUES "
    query = query & "('VERSION', '85') "
    query = query & "END "
    ExecuteAlter query
    
    ' [VERSION] = Update version if older
    query = "IF (SELECT Id "
    query = query & "FROM MEDICARE..tbMedEclaimsUtility "
    query = query & "WHERE UtilityType = 'VERSION' "
    query = query & "AND CAST(UtilityValue AS INT) < CAST('" & App.Major & App.Minor & App.Revision & "' AS INT)) > 0 "
    query = query & "BEGIN "
    query = query & "UPDATE MEDICARE..tbMedEclaimsUtility "
    query = query & "SET UtilityValue = '" & App.Major & App.Minor & App.Revision & "' "
    query = query & "WHERE UtilityType = 'VERSION' "
    query = query & "END "
    ExecuteAlter query
    
End Sub

Public Sub ExecuteNewFields()
    Dim query As String
    query = query & "IF NOT EXISTS (SELECT COLUMN_NAME "
    query = query & "FROM MEDICARE.INFORMATION_SCHEMA.COLUMNS "
    query = query & "WHERE TABLE_NAME = 'tbMedSupportingDocuments' and COLUMN_NAME = 'TableName') "
    query = query & "BEGIN "
    query = query & "ALTER TABLE tbMedSupportingDocuments ADD TableName VARCHAR(25) "
    query = query & "END "
    ExecuteAlter query
End Sub

Private Sub ExecuteAlter(ByVal query As String)
On Error GoTo ErrHandler
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub