Attribute VB_Name = "modLocalDBData"
Option Explicit

Public Function GetPatientDoctorIds(ByVal idNum As String) As String()
    Dim rec As New ADODB.Recordset
    Dim query As String
    Dim result() As String
On Error GoTo ErrHandler
    query = query & "SELECT A.DocCode [value] "
    query = query & "FROM tbMedDoctors A "
    query = query & "LEFT JOIN BUILD_FILE..tbCoDoctor B "
    query = query & "ON A.DocCode = B.DoctorID "
    query = query & "LEFT JOIN tbMedPatient C "
    query = query & "ON a.idnum = c.idnum "
    query = query & "WHERE a.IdNum = '" & idNum & "' "
    ' query = query & "AND ISNULL(B.PhilHealthNum,'') <> ''"
    query = query & "AND (B.PhilHealthNum <> '' AND B.PhilHealthNum IS NOT NULL)"
        
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount)
            Dim counter As Integer
            counter = 0
            Do While Not .EOF
                result(counter) = !value
                counter = counter + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetPatientDoctorIds = result
    Exit Function
ErrHandler:
    Set rec = Nothing
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function UpdatePatientMNameUseNa(ByVal idNum As String, ByVal value As Boolean) As Boolean
UpdatePatientMNameUseNa = False
On Error GoTo ErrHandler
    Dim query As String
    query = "UPDATE Medicare..tbMedMemberEligibility "
    query = query & "SET PatientMNameUseNA = '" & value & "' "
    query = query & "WHERE IdNum = '" & idNum & "'"

    MedsysUser.SQLConnection.Execute query
    
    UpdatePatientMNameUseNa = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetCf2Amount(ByVal idNum As String, ByVal item As Integer) As Double
    Dim rec As New ADODB.Recordset
On Error GoTo ErrHandler
    Dim result As Double
    result = 0
    
    Dim query As String
    query = "SELECT MEDICARE.dbo.fn_CF2Fees('" & idNum & "','" & item & "') [value]"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = !value
        End If
    End With
    Set rec = Nothing
    
    GetCf2Amount = result
    Exit Function
ErrHandler:
    Set rec = Nothing
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetImportedData(pClaimNumber As String) As ExternalClaimInfo
    Dim rec As New ADODB.Recordset
    Dim query As String
    query = "SELECT ClaimNumber, XMLFileName FROM MEDICARE..tbMedEClaimsImportXML WHERE ClaimNumber = '" & pClaimNumber & "'"
    Dim result As ExternalClaimInfo
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result.ClaimNumber = ValidateNullValue(!ClaimNumber)
            result.XmlFileName = ValidateNullValue(!XmlFileName)
        End If
    End With
    Set rec = Nothing
    
    GetImportedData = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function SaveExternalXml(ByVal pClaimNumber As String, ByVal pXmlFileName As String, _
                                ByVal pUploadedBy As String) As Boolean
On Error GoTo ErrHandler
    Dim result As Boolean
    result = False
    
    Dim query As String
    query = "INSERT INTO MEDICARE..tbMedEClaimsImportXML "
    query = query & "(ClaimNumber, XMLFileName, UploadedBy, TransDate) "
    query = query & "VALUES "
    query = query & "('" & pClaimNumber & "', '" & pXmlFileName & "', '" & pUploadedBy & "', '" & DateTime.Now & "')"
    MedsysUser.SQLConnection.Execute query
    
    result = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetServerList() As ServerList()
    Dim rec As New ADODB.Recordset
    Dim result() As ServerList
    Dim query As String
On Error GoTo ErrHandler
    query = "SELECT ServerName, ServerUrl, ServerType FROM MEDICARE..tbMedEclaimsServerList "
    query = query & "WHERE ServerType = 'STORAGE'"
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).ServerName = !ServerName
                result(intRow).ServerUrl = !ServerUrl
                result(intRow).ServerType = !ServerType
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetServerList = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetDocDbName() As String
GetDocDbName = Empty
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result: result = "Patient_Files"
    
    Dim query As String
    query = "SELECT name FROM master.dbo.sysdatabases WHERE name = N'Patient_Files_' + CAST(YEAR(GETDATE()) AS VARCHAR)"
'    query = "SELECT name FROM master.dbo.sysdatabases WHERE name = N'eClaims_Files'"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!Name)
        End If
    End With
    Set rec = Nothing
    
    GetDocDbName = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetDocTableName() As String
GetDocTableName = Empty
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result: result = "tbPatientDocs"
    
    Dim query As String
    query = "SELECT TABLE_NAME FROM " & DocumentDbName & ".INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbPatientEClaimsDocs'"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!TABLE_NAME, False)
        End If
    End With
    Set rec = Nothing
    
    GetDocTableName = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

' Get ServerDate
Public Function GetServerDate() As String
    Dim rec As New ADODB.Recordset
    Dim query As String
    query = "SELECT GETDATE() [value]"
    Dim result As String
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!value)
        End If
    End With
    Set rec = Nothing
    
    GetServerDate = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

' Get User Category
Public Function GetUserCategories() As UserCategoryType()
    Dim rec As New ADODB.Recordset
    Dim result() As UserCategoryType
    Dim query As String
On Error GoTo ErrHandler
    query = "SELECT DepartmentCode, UserCategory FROM MEDICARE..tbMedEclaimsSettings"
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).DepartmentCode = !DepartmentCode
                result(intRow).Category = !UserCategory
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetUserCategories = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

' Get Problem List
Public Function GetPatientProblems(pIdNum As String) As PatientProblems()
    Dim rec As New ADODB.Recordset
    Dim result() As PatientProblems
    Dim query As String
    
On Error GoTo ErrHandler
    query = "SELECT A.IDNum, A.PPCode, B.Description [ProblemDescription], A.StartDate, A.EndDate "
    query = query & "FROM STATION..tbPatientProblem A "
    query = query & "LEFT JOIN Build_File..tbPatientProblem B "
    query = query & "ON A.PPCode = B.PPCode "
    query = query & "WHERE IDNum = '" & pIdNum & "' "
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).idNum = !idNum
                result(intRow).Code = !PPCode
                result(intRow).Description = !ProblemDescription
                result(intRow).StartDate = !StartDate
                result(intRow).EndDate = ValidateNullValue(!EndDate)
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetPatientProblems = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function


' Set AppKey ' Expiration Date
Public Sub SetAppKey(ByVal Key As String, ByVal HospCode As String)
On Error GoTo ErrHandler
    Dim query As String
    query = "UPDATE Medicare..tbMedEClaimsSettings SET AppKey = '" & Key & "'"
    query = query & " WHERE HospitalCode = '" & HospCode & "'"
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

' Get AppKey ' Expiration Date
Public Function GetAppKey(HospCode As String) As String
    Dim rec As New ADODB.Recordset
    Dim query As String
    query = "SELECT ISNULL(AppKey,'') [value] FROM Medicare..tbMedEClaimsSettings WHERE HospitalCode = '" & HospCode & "'"
    Dim result As String
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!value)
        End If
    End With
    Set rec = Nothing
    
    GetAppKey = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetSupportingDocumentSeriesNoByDocumentType(pIdNum As String, pDocumentType As String) As String
On Error GoTo ErrHandler
    Dim query As String
    query = "SELECT ISNULL(MAX(CAST(SeriesNo AS INT)), 0) [value] "
    query = query & "FROM Medicare..tbMedSupportingDocumentSeries "
    query = query & "WHERE IdNum = '" & pIdNum & "' AND DocumentType = '" & pDocumentType & "' "
    GetSupportingDocumentSeriesNoByDocumentType = GetQueryResult(query, False)
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Private Function GetSupportingDocumentSeriesNo(pIdNum As String) As String
On Error GoTo ErrHandler
    Dim query As String
    query = "SELECT ISNULL(MAX(CAST(SeriesNo AS INT)), 0) [value] "
    query = query & "FROM Medicare..tbMedSupportingDocumentSeries "
    query = query & "WHERE IdNum = '" & pIdNum & "' "
    GetSupportingDocumentSeriesNo = GetQueryResult(query, True)
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function SaveSupportingDocumentSeries(ByVal pIdNum As String, ByVal pDocumentType As String) As Boolean
On Error GoTo ErrHandler
    Dim currDate As String
    currDate = DateTime.Now

    Dim result As Boolean
    result = False
    
    Dim query As String
    query = "INSERT INTO Medicare..tbMedSupportingDocumentSeries "
    query = query & "(IdNum, Trans_Year, Trans_Month, SeriesNo, DocumentType, TransDate) "
    query = query & "VALUES "
    query = query & "('" & pIdNum & "', YEAR('" & currDate & "'), MONTH('" & currDate & "'), '" & GetSupportingDocumentSeriesNo(pIdNum) & "', '" & pDocumentType & "', '" & currDate & "')"
    MedsysUser.SQLConnection.Execute query
    
    result = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function IsClaimCountUploaded() As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As Boolean
    result = False
    Dim query As String
    query = "SELECT CONVERT(date, LastClaimCountUploadDate, 102) [value] FROM MEDICARE..tbMedEClaimsSettings "
    query = query & "WHERE HospitalCode = '" & EcSettings.HospCode & "' "
    query = query & "AND CONVERT(date, LastClaimCountUploadDate, 102) = CONVERT(date, GETDATE(), 102) "
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .EOF = False Then
            result = True
        End If
    End With
    Set rec = Nothing
    
    IsClaimCountUploaded = result
    Exit Function
ErrHandler:
    IsClaimCountUploaded = False
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

' Set Date Refiled
Public Sub SetDateRefiled(ByVal idNum As String)
On Error GoTo ErrHandler
    Dim query As String
    query = "UPDATE Medicare..tbMedElectronicClaims SET DateRefiled = GETDATE()"
    query = query & " WHERE IdNum = '" & idNum & "'"
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SavePayment(pClaimSeriesLhio As String, _
                        pClaimPayeeName As String, _
                        pTotalClaimAmountPaid As String, _
                        pVoucherNo As String, _
                        pVoucherDate As String, _
                        pCheckNo As String, _
                        pCheckDate As String, _
                        pCheckAmount As String, _
                        pClaimAmount As String)
On Error GoTo ErrHandler
    Dim rec As New ADODB.Record
    Dim query As String
    query = "INSERT INTO MEDICARE..tbMedElectronicClaimStatus_Payment "
    query = query & "(ClaimSeriesLhio, ClaimPayeeName, TotalClaimAmountPaid, VoucherNo, VoucherDate, CheckNo, CheckDate, CheckAmount, ClaimAmount) "
    query = query & "VALUES ('" & pClaimSeriesLhio & "', '" & pClaimPayeeName & "', '" & pTotalClaimAmountPaid & "', '" & pVoucherNo & "', '" & pVoucherDate & "', "
    query = query & "'" & pCheckNo & "', '" & pCheckDate & "', '" & pCheckAmount & "', '" & pClaimAmount & "')"
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SaveDenied(pClaimSeriesLhio As String, pReason As String)
On Error GoTo ErrHandler
    Dim rec As New ADODB.Record
    Dim query As String
    query = "IF NOT EXISTS (SELECT TOP 1 ClaimSeriesLhio FROM MEDICARE..tbMedElectronicClaimStatus_Denied WHERE ClaimSeriesLhio = '" & pClaimSeriesLhio & "' AND Reason = '" & pReason & "') "
    query = query & "BEGIN "
    query = query & "INSERT INTO MEDICARE..tbMedElectronicClaimStatus_Denied "
    query = query & "(ClaimSeriesLhio, Reason) "
    query = query & "VALUES ('" & pClaimSeriesLhio & "', '" & pReason & "') "
    query = query & "END"
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SaveReturn(pClaimSeriesLhio As String, pDeficiency As String, pRequirement As String)
On Error GoTo ErrHandler
    Dim rec As New ADODB.Record
    Dim query As String
    query = "IF NOT EXISTS (SELECT TOP 1 ClaimSeriesLhio FROM MEDICARE..tbMedElectronicClaimStatus_Return "
    query = query & "WHERE ClaimSeriesLhio = '" & pClaimSeriesLhio & "' AND Deficiency = '" & pDeficiency & "' AND Requirement = '" & pRequirement & "') "
    query = query & "BEGIN "
    query = query & "INSERT INTO MEDICARE..tbMedElectronicClaimStatus_Return "
    query = query & "(ClaimSeriesLhio, Deficiency, Requirement) "
    query = query & "VALUES ('" & pClaimSeriesLhio & "', '" & pDeficiency & "', '" & pRequirement & "') "
    query = query & "END "
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SaveProcess(pClaimSeriesLhio As String, pProcessStage As String, pProcessDate As String)
On Error GoTo ErrHandler
    Dim rec As New ADODB.Record
    Dim query As String
    query = "IF NOT EXISTS (SELECT TOP 1 ClaimSeriesLhio FROM MEDICARE..tbMedElectronicClaimStatus_Process "
    query = query & "WHERE ClaimSeriesLhio = '" & pClaimSeriesLhio & "' AND ProcessStage = '" & pProcessStage & "' AND ProcessDate = '" & pProcessDate & "' )"
    query = query & "BEGIN "
    query = query & "INSERT INTO MEDICARE..tbMedElectronicClaimStatus_Process "
    query = query & "(ClaimSeriesLhio, ProcessStage, ProcessDate) "
    query = query & "VALUES ('" & pClaimSeriesLhio & "', '" & pProcessStage & "', '" & pProcessDate & "') "
    query = query & "END "
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SaveClaimStatus(pClaimSeriesLhio As String, _
                            pStatus As String, _
                            pAsOf As String, _
                            pAsOfTime As String, _
                            pClaimDateReceived As String, _
                            pClaimDateRefile As String)
On Error GoTo ErrHandler
    Dim rec As New ADODB.Record
    Dim query As String
    query = "IF NOT EXISTS (SELECT TOP 1 ClaimSeriesLhio FROM MEDICARE..tbMedElectronicClaimStatus WHERE ClaimSeriesLhio = '" & pClaimSeriesLhio & "') "
    query = query & "BEGIN "
    query = query & "INSERT INTO MEDICARE..tbMedElectronicClaimStatus "
    query = query & "(ClaimSeriesLhio, Status, AsOf, AsOfTime, UserId, ClaimDateReceived, ClaimDateRefile) "
    query = query & "VALUES "
    query = query & "('" & pClaimSeriesLhio & "', '" & pStatus & "', '" & pAsOf & "', '" & pAsOfTime & "', '" & MedsysUser.EmployeeCode & "', '" & pClaimDateReceived & "', '" & pClaimDateRefile & "') "
    query = query & "END "
    query = query & "ELSE "
    query = query & "BEGIN "
    query = query & "UPDATE MEDICARE..tbMedElectronicClaimStatus "
    query = query & "SET Status = '" & pStatus & "', AsOf = '" & pAsOf & "', "
    query = query & "AsOfTime = '" & pAsOfTime & "', UserId = '" & MedsysUser.EmployeeCode & "', ClaimDateReceived = '" & pClaimDateReceived & "', ClaimDateRefile = '" & pClaimDateRefile & "' "
    query = query & "WHERE ClaimSeriesLhio = '" & pClaimSeriesLhio & "' "
    query = query & "END "
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function IsCf4Verified(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As Boolean
    result = False
    Dim query As String
'    query = "SELECT ISNULL(CF4VerifiedOn,0) [value] FROM Station..tbNurseProfile WHERE IDNum = '" & pIdNum & "'"
    query = "SELECT MEDICARE.dbo.[fn_isCF4Verified]('" & pIdNum & "') [value]"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .EOF = False Then
            result = !value
        End If
    End With
    Set rec = Nothing
    
    IsCf4Verified = result
    Exit Function
ErrHandler:
    IsCf4Verified = False
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub SavePbefData(ByVal pId As String, _
                    ByVal pIdNum As String, _
                    ByVal pTransDate As String, _
                    ByVal pTrackingNumber As String, _
                    ByVal pHospitalName As String, _
                    ByVal pAccreditationNumber As String, _
                    ByVal pMemberPIN As String, _
                    ByVal pMemberName As String, _
                    ByVal pMemberGender As String, _
                    ByVal pMemberBirthDate As String, _
                    ByVal pMemberCategoryDescription As String, _
                    ByVal pPatientName As String, _
                    ByVal pPatientAdmissionDate As String, _
                    ByVal pPatientDischargeDate As String, _
                    ByVal pPatientGender As String, _
                    ByVal pPatientBirthDate As String, _
                    ByVal pIsOk As String, _
                    ByVal pRemarks As String, _
                    ByVal pReason As String, _
                    ByVal pWith3over6 As String, _
                    ByVal pWith9Over12 As String, _
                    ByVal pRemainingDays As String)
On Error GoTo ErrHandler
    Dim currDate As String
    currDate = DateTime.Now

    Dim result As Boolean
    result = False
    
    Dim query As String
    query = "INSERT INTO Medicare..tbMedEclaimsPrintedPbef "
    query = query & "VALUES "
    query = query & "('" & pId & "', '" _
                        & pIdNum & "', '" _
                        & pTransDate & "', '" _
                        & pTrackingNumber & "', '" _
                        & pHospitalName & "', '" _
                        & pAccreditationNumber & "', '" _
                        & pMemberPIN & "', '" _
                        & pMemberName & "', '" _
                        & pMemberGender & "', '" _
                        & pMemberBirthDate & "', '" _
                        & pMemberCategoryDescription & "', '" _
                        & pPatientName & "', '" _
                        & pPatientAdmissionDate & "', '" _
                        & pPatientDischargeDate & "', '" _
                        & pPatientGender & "', '" _
                        & pPatientBirthDate & "', '" _
                        & pIsOk & "', '" _
                        & pRemarks & "', '" _
                        & pReason & "', '" _
                        & pWith3over6 & "', '" _
                        & pWith9Over12 & "', '" _
                        & pRemainingDays & "', '" _
                        & MedsysUser.EmployeeCode & "')"
    MedsysUser.SQLConnection.Execute query
    
    result = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function GetForValidationDocument(Optional ForRth As Boolean = False) As String()
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result() As String
    Dim query As String
    
    If ForRth Then
        query = "SELECT DocumentCode FROM MEDICARE..tbMedDocumentTypes WHERE ValidateUponRefile = 1"
    Else
        query = "SELECT DocumentCode FROM MEDICARE..tbMedDocumentTypes WHERE ValidateUponSubmit = 1"
    End If
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow) = !DocumentCode
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetForValidationDocument = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKCancel & vbCritical
End Function

Public Sub UserLogs(ByVal pAction As String, ByVal pDescription As String)
On Error GoTo ErrHandler
    Dim currDate As String
    currDate = DateTime.Now

    Dim result As Boolean
    result = False
    
    Dim query As String
    query = "INSERT INTO Medicare..tbMedEClaimsUserLogs "
    query = query & "(UserId, Action, Description, TransDate, Version) "
    query = query & "VALUES "
    query = query & "('" & MedsysUser.EmployeeCode & "', '" & pAction & "', '" & pDescription & "', '" & currDate & "', 'v." & App.Major & "." & App.Minor & "." & App.Revision & "')"
    MedsysUser.SQLConnection.Execute query
    
    result = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function GetDashboardSyncDateTime() As String
On Error GoTo ErrHandler
    Dim query As String
    query = "SELECT TOP 1 A.DateTime [value] "
    query = query & "FROM (SELECT CONVERT(DATETIME, CONVERT(CHAR(8), AsOf, 112) + ' ' + CONVERT(CHAR(8), AsOfTime, 108)) [DateTime] FROM MEDICARE..tbMedElectronicClaimStatus) A "
    query = query & "ORDER BY A.DateTime DESC "
    
    Dim ret: ret = GetQueryResult(query, False, True)
    GetDashboardSyncDateTime = IIf(ret = Empty, "01/01/1900 00:00:00.00", ret)
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetDashboardStatus(ByVal pDisplayBy As String, _
                                    ByVal pPatientType As String, _
                                    ByVal pDate As String, _
                                    ByRef pCaseType() As String, _
                                    Optional ByVal pIsInOutDataOnly As Boolean = False, _
                                    Optional ByVal pSortType As String = "TRD") As DashboardStatus
    Dim rec As New ADODB.Recordset
    Dim result As DashboardStatus
    result.dsInProcess = 0
    result.dsReturn = 0
    result.dsDenied = 0
    result.dsVouchering = 0
    result.dsWithVoucher = 0
    result.dsWithCheque = 0
    
On Error GoTo ErrHandler
    Dim query As String
    query = "SELECT B.Status, Count(B.Status) [Value] "
    If pIsInOutDataOnly = True Then
        query = "SELECT Count(A.ClaimSeriesLhio) [Value] "
    End If
    
    query = query & "FROM MEDICARE..tbMedElectronicClaims A "
    query = query & "LEFT JOIN MEDICARE..tbMedElectronicClaimStatus B "
    query = query & "ON A.ClaimSeriesLhio = B.ClaimSeriesLhio "
    query = query & "LEFT JOIN MEDICARE..tbMedConfinementInfo C "
    query = query & "ON A.IdNum = C.IdNum "
    
    ' Additional for Discharge Date
    query = query & "LEFT JOIN MEDICARE..tbMedPatient D "
    query = query & "ON A.IdNum = D.IdNum "
    
    query = query & "WHERE 1=1 "
    query = query & "AND LEFT(REPLACE(TransmissionControlNumber,'-',''), 6) = '" & IIf(Len(EcSettings.HospCode) = 5, 0 & EcSettings.HospCode, EcSettings.HospCode) & "' "
    query = query & "AND B.Status IS NOT NULL "
    query = query & "AND B.Status <> 'CLAIM SERIES NOT FOUND' "
    
    If pCaseType(0) = "1" Then query = query & "AND C.isHemoProcedure = 1 "
    If pCaseType(1) = "1" Then query = query & "AND C.isChemoProcedure = 1 "
    
    ' TransmissionDate
    If pSortType = "TRD" Then
        If pDisplayBy = "D" Then query = query & "AND A.TransmissionDate = CONVERT(DATE, '" & pDate & "') "
        If pDisplayBy = "M" Then query = query & "AND (MONTH(A.TransmissionDate) = MONTH('" & pDate & "') AND YEAR(A.TransmissionDate) = YEAR('" & pDate & "')) "
        If pDisplayBy = "Y" Then query = query & "AND YEAR(A.TransmissionDate) = YEAR('" & pDate & "') "
    End If
    
    ' DischargeDate
    If pSortType = "DCR" Then
        If pDisplayBy = "D" Then query = query & "AND D.DcrDate = CONVERT(DATE, '" & pDate & "') "
        If pDisplayBy = "M" Then query = query & "AND (MONTH(D.DcrDate) = MONTH('" & pDate & "') AND YEAR(D.DcrDate) = YEAR('" & pDate & "')) "
        If pDisplayBy = "Y" Then query = query & "AND YEAR(D.DcrDate) = YEAR('" & pDate & "') "
    End If
    
    If pPatientType = "I" Then query = query & "AND ISNUMERIC(A.IdNum) = 1 "
    If pPatientType = "O" Then query = query & "AND ISNUMERIC(A.IdNum) = 0 "
    
    If pIsInOutDataOnly = False Then
        query = query & "GROUP BY B.Status "
    End If
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
        
            If pIsInOutDataOnly = True Then
                result.dsInProcess = !value
            Else
                Do While Not .EOF
                    If !Status = "IN PROCESS" Then
                        result.dsInProcess = !value
                    End If
                    
                    If !Status = "RETURN" Then
                        result.dsReturn = !value
                    End If
                    
                    If !Status = "DENIED" Then
                        result.dsDenied = !value
                    End If
                    
                    If !Status = "VOUCHERING" Then
                        result.dsVouchering = !value
                    End If
                    
                    If !Status = "WITH VOUCHER" Then
                        result.dsWithVoucher = !value
                    End If
                    
                    If !Status = "WITH CHEQUE" Then
                        result.dsWithCheque = !value
                    End If
                    .MoveNext
                Loop
            End If
        End If
    End With
    Set rec = Nothing
    
    GetDashboardStatus = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function
Public Function GetPatientDocuments(ByVal pHospNum As String, ByVal admNum As String) As PatientDocs()
    Dim rec As New ADODB.Recordset
    Dim result() As PatientDocs
    Dim query As String
    
    Dim admDate: admDate = Format(GetAdmDate(admNum), "mm/dd/yyyy")
    Dim dcrDate: dcrDate = Format(GetDcrDate(admNum), "mm/dd/yyyy")
    
On Error GoTo ErrHandler
    query = "SELECT DocType [FileType], LabExam [FileContent], FileName [FileName] "
    query = query & "FROM Patient_Files..tbPatientDocs "
    
    If ProgSettingsGbl.IsProgramSettingExists("99ECA54B-2F00-4D24-A896-B29F0FC4ABD3") Then ' 99ECA54B-2F00-4D24-A896-B29F0FC4ABD3','PATIENT FILES : FILTERED BY HOSPNUM
        query = query & " WHERE HospNum = '" & pHospNum & "' "
        query = query & " AND ConversionTime BETWEEN '" & admDate & "' AND '" & dcrDate & "' + ' 23:59:59.99' "
    Else
        query = query & " WHERE IdNum = '" & admNum & "' "
    End If
    query = query & " AND DocType NOT IN ('PDF', 'XML', 'PHIC Docs') "
    
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).fileType = !fileType
                result(intRow).FileContent = !FileContent
                result(intRow).fileName = !fileName
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetPatientDocuments = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetCf4ImplementationStartDate() As String
GetCf4ImplementationStartDate = ""
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As String
    result = "03/01/2019"
    Dim query As String
    query = "SELECT value FROM Station..tbNurseSetup WHERE Field = 'CF4RequireStart'"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .EOF = False Then
            result = !value
        End If
    End With
    Set rec = Nothing
    
    GetCf4ImplementationStartDate = Format(result, "MM/DD/YYYY")
    Exit Function
ErrHandler:
    GetCf4ImplementationStartDate = "03/01/2019"
End Function

Public Function IsWithMemberEligibilityData(pIdNum As String) As Boolean
    Dim result As Boolean
    result = False
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    SQL = "SELECT IdNum FROM Medicare..tbMedMemberEligibility where IdNum = '" & pIdNum & "'"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result = True
        End If
        .Close
    End With
    Set rec = Nothing
    
    IsWithMemberEligibilityData = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function HasAttachedSoa(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As Boolean
    result = False
    Dim query As String
    query = "SELECT IdNum FROM MEDICARE..tbMedSupportingDocuments WHERE IdNum = '" & pIdNum & "' AND DocumentCode = 'SOA'"
    With rec
        .Open query, MedsysUser.SQLConnection, adOpenDynamic, adLockReadOnly
        If .EOF = False Then
            result = True
        End If
    End With
    Set rec = Nothing
    
    HasAttachedSoa = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetAdmDate(pIdNum) As Date
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As Date
    Dim query As String
    query = "SELECT AdmDate FROM PATIENT_DATA..tbpatient WHERE IdNum = '" & pIdNum & "'  "
    query = query & "UNION ALL "
    query = query & "SELECT AdmDate FROM PATIENT_DATA..tbOutPatient WHERE IdNum = '" & pIdNum & "' "
    With rec
        .Open query, MedsysUser.SQLConnection, adOpenDynamic, adLockReadOnly
        If .EOF = False Then
            result = !admDate
        End If
    End With
    Set rec = Nothing
    
    GetAdmDate = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetDcrDate(pIdNum) As Date
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As Date
    Dim query As String
    query = "SELECT ISNULL(DcrDate, GETDATE()) value FROM PATIENT_DATA..tbpatient WHERE IdNum = '" & pIdNum & "'  "
    query = query & "UNION ALL "
    query = query & "SELECT ISNULL(DcrDate, GETDATE()) value FROM PATIENT_DATA..tbOutPatient WHERE IdNum = '" & pIdNum & "' "
    With rec
        .Open query, MedsysUser.SQLConnection, adOpenDynamic, adLockReadOnly
        If .EOF = False Then
            result = !value
        End If
    End With
    Set rec = Nothing
    
    GetDcrDate = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function SaveCf4XmlLog(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim currDate As String
    currDate = DateTime.Now

    Dim result As Boolean
    result = False
    
    Dim query As String
    query = "INSERT INTO Medicare..tbMedEClaimsCF4XmlSeries "
    query = query & "(IdNum, Trans_Year, Trans_Month, SeriesNo, TransDate) "
    query = query & "VALUES "
    query = query & "('" & pIdNum & "', YEAR('" & currDate & "'), MONTH('" & currDate & "'), '" & GetCf4SeriesNo(currDate) & "', '" & currDate & "')"
    MedsysUser.SQLConnection.Execute query
    
    result = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Private Function GetCf4SeriesNo(pCurrDate As String) As String
On Error GoTo ErrHandler
    Dim query As String
    query = "SELECT ISNULL(MAX(CAST(SeriesNo AS INT)), 0) [value] "
    query = query & "FROM Medicare..tbMedEClaimsCF4XmlSeries "
    query = query & "WHERE Trans_Year = YEAR('" & pCurrDate & "') AND Trans_Month = MONTH('" & pCurrDate & "') "
    GetCf4SeriesNo = GetQueryResult(query, True)
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

'-----------------------------------------------<ESOA PART>------------------------------------------------------------------------------------------------------------
Public Function HasCf4TransNo(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result: result = False
    Dim query As String
    query = "IF EXISTS(SELECT SeriesNo FROM Medicare..tbMedEClaimsCF4Xml WHERE IdNum = '" & pIdNum & "') "
    query = query & "BEGIN "
    query = query & "SELECT SeriesNo FROM Medicare..tbMedEClaimsCF4Xml WHERE IdNum = '" & pIdNum & "' "
    query = query & "END "
    query = query & "ELSE "
    query = query & "BEGIN "
    query = query & "SELECT SeriesNo FROM Medicare..tbMedEClaimsCF4XmlSeries WHERE IdNum = '" & pIdNum & "' "
    query = query & "END "
    With rec
        .Open query, MedsysUser.SQLConnection, adOpenDynamic, adLockReadOnly
        If .EOF = False Then
            result = True
        End If
    End With
    Set rec = Nothing
    
    HasCf4TransNo = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

'arnold 1.12.24
Public Function SaveESOAXmlLog(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim currDate As String
    currDate = DateTime.Now

    Dim result As Boolean
    result = False
    
    Dim query As String
    query = "INSERT INTO Medicare..tbMedEClaimsESOAXmlSeries "
    query = query & "(IdNum, Trans_Year, Trans_Month, SeriesNo, TransDate) "
    query = query & "VALUES "
    query = query & "('" & pIdNum & "', YEAR('" & currDate & "'), MONTH('" & currDate & "'), '" & GetESOASeriesNo(currDate) & "', '" & currDate & "')"
    MedsysUser.SQLConnection.Execute query
    
    result = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Private Function GetESOASeriesNo(pCurrDate As String) As String
On Error GoTo ErrHandler
    Dim query As String
    query = "SELECT ISNULL(MAX(CAST(SeriesNo AS INT)), 0) [value] "
    query = query & "FROM Medicare..tbMedEClaimsESOAXmlSeries "
    query = query & "WHERE Trans_Year = YEAR('" & pCurrDate & "') AND Trans_Month = MONTH('" & pCurrDate & "') "
    GetESOASeriesNo = GetQueryResult(query, True)
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function HasESOATransNo(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result: result = False
    Dim query As String
    query = "IF EXISTS(SELECT SeriesNo FROM Medicare..tbMedEClaimsESOAXml WHERE IdNum = '" & pIdNum & "') "
    query = query & "BEGIN "
    query = query & "SELECT SeriesNo FROM Medicare..tbMedEClaimsESOAXml WHERE IdNum = '" & pIdNum & "' "
    query = query & "END "
    query = query & "ELSE "
    query = query & "BEGIN "
    query = query & "SELECT SeriesNo FROM Medicare..tbMedEClaimsESOAXmlSeries WHERE IdNum = '" & pIdNum & "' "
    query = query & "END "
    With rec
        .Open query, MedsysUser.SQLConnection, adOpenDynamic, adLockReadOnly
        If .EOF = False Then
            result = True
        End If
    End With
    Set rec = Nothing
    
    HasESOATransNo = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function IsEsoaVerified(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As Boolean
    result = False
    Dim query As String
    query = "SELECT MEDICARE.dbo.[fn_isEsoaVerified]('" & pIdNum & "') [value]"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .EOF = False Then
            result = !value
        End If
    End With
    Set rec = Nothing
    
    IsEsoaVerified = result
    Exit Function
ErrHandler:
    IsEsoaVerified = False
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetESOAImplementationStartDate() As String
'soon to add  tbnursesetup
GetESOAImplementationStartDate = ""
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As String
    result = "02/01/2023"
    Dim query As String
    query = "SELECT value FROM Station..tbNurseSetup WHERE Field = 'ESOARequireStart'"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .EOF = False Then
            result = !value
        End If
    End With
    Set rec = Nothing
    
    GetESOAImplementationStartDate = Format(result, "MM/DD/YYYY")
    Exit Function
ErrHandler:
    GetESOAImplementationStartDate = "02/01/2023"
End Function
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------<CF5 PART>-----------------------------------------------------------------------------------------------------------------------------------------------------------
'arnold 6.28.2024
Public Function HasCF5TransNo(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result: result = False
    Dim query As String
    query = "IF EXISTS(SELECT SeriesNo FROM Medicare..tbMedEClaimsCF5Xml WHERE IdNum = '" & pIdNum & "') "
    query = query & "BEGIN "
    query = query & "SELECT SeriesNo FROM Medicare..tbMedEClaimsCF5Xml WHERE IdNum = '" & pIdNum & "' "
    query = query & "END "
    query = query & "ELSE "
    query = query & "BEGIN "
    query = query & "SELECT SeriesNo FROM Medicare..tbMedEClaimsCF5XmlSeries WHERE IdNum = '" & pIdNum & "' "
    query = query & "END "
    With rec
        .Open query, MedsysUser.SQLConnection, adOpenDynamic, adLockReadOnly
        If .EOF = False Then
            result = True
        End If
    End With
    Set rec = Nothing
    
    HasCF5TransNo = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function


Public Function SaveCF5XmlLog(ByVal pIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim currDate As String
    currDate = DateTime.Now

    Dim result As Boolean
    result = False
    
    Dim query As String
    query = "INSERT INTO Medicare..tbMedEClaimsCF5XmlSeries "
    query = query & "(IdNum, Trans_Year, Trans_Month, SeriesNo, TransDate) "
    query = query & "VALUES "
    query = query & "('" & pIdNum & "', YEAR('" & currDate & "'), MONTH('" & currDate & "'), '" & GetCF5SeriesNo(currDate) & "', '" & currDate & "')"
    MedsysUser.SQLConnection.Execute query
    
    result = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Private Function GetCF5SeriesNo(pCurrDate As String) As String
On Error GoTo ErrHandler
    Dim query As String
    query = "SELECT ISNULL(MAX(CAST(SeriesNo AS INT)), 0) [value] "
    query = query & "FROM Medicare..tbMedEClaimsCF5XmlSeries "
    query = query & "WHERE Trans_Year = YEAR('" & pCurrDate & "') AND Trans_Month = MONTH('" & pCurrDate & "') "
    GetCF5SeriesNo = GetQueryResult(query, True)
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function



'soon to add (soon to add and create fn and tbnursesetup)
'Public Function IsCF5Verified(ByVal pIdNum As String) As Boolean
'On Error GoTo ErrHandler
'    Dim rec As New ADODB.Recordset
'    Dim result As Boolean
'    result = False
'    Dim query As String
'    query = "SELECT MEDICARE.dbo.[fn_isCF5Verified]('" & pIdNum & "') [value]"
'    With rec
'        If .State > 0 Then .Close
'        .CursorLocation = adUseClient
'        .CursorType = adOpenDynamic
'        .LockType = adLockReadOnly
'        .Open query, MedsysUser.SQLConnection
'        If .EOF = False Then
'            result = !value
'        End If
'    End With
'    Set rec = Nothing
'
'    IsCF5Verified = result
'    Exit Function
'ErrHandler:
'    IsEsoaVerified = False
'    MsgBox Err.Description, vbOKOnly + vbCritical
'End Function

'soon to add
'Public Function GetESOAImplementationStartDate() As String
'GetESOAImplementationStartDate = ""
'On Error GoTo ErrHandler
'    Dim rec As New ADODB.Recordset
'    Dim result As String
'    result = "02/01/2023"
'    Dim query As String
'    query = "SELECT value FROM Station..tbNurseSetup WHERE Field = 'ESOARequireStart'"
'    With rec
'        If .State > 0 Then .Close
'        .CursorLocation = adUseClient
'        .CursorType = adOpenDynamic
'        .LockType = adLockReadOnly
'        .Open query, MedsysUser.SQLConnection
'        If .EOF = False Then
'            result = !value
'        End If
'    End With
'    Set rec = Nothing
'
'    GetESOAImplementationStartDate = Format(result, "MM/DD/YYYY")
'    Exit Function
'ErrHandler:
'    GetESOAImplementationStartDate = "02/01/2023"
'End Function

'-----------------------------------------------------------------------------------------------------------------------------------------------------------

Public Sub SaveUserLogs(ByVal Action As String, ByVal Description As String)
On Error GoTo ErrHandler
    
    Dim query As String
    query = "INSERT INTO Medicare..tbMedEClaimsUserLogs "
    query = query & "(UserId, Action, Description, TransDate, Version) "
    query = query & "VALUES "
    query = query & "('" & MedsysUser.EmployeeCode & "','" & Action & "','" & Description & "','" & DateTime.Now & "', 'v." & App.Major & "." & App.Minor & "." & App.Revision & "')"
    
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SaveEReceipt(ByVal idNum As String, ByVal response As String)
On Error GoTo ErrHandler

    Dim query As String
    query = "INSERT INTO Medicare..tbMedeClaimsReceipt "
    query = query & "(IdNum, UserId, Response) "
    query = query & "VALUES "
    query = query & "('" & idNum & "','" & MedsysUser.EmployeeCode & "','" & response & "') "
    
    MedsysUser.SQLConnection.Execute query
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

'Update Medicare..tbMedPatient set TransmittalNum =
Public Sub AssignTransmittalNum(ByVal idNum As String, ByVal transNum As String, ByVal batchDate As String)
On Error GoTo ErrHandler
    Dim query As String
    query = "UPDATE Medicare..tbMedPatient SET TransmittalNum = '" & transNum & "', TransDate = '" & batchDate & "'"
    query = query & " WHERE IdNum = '" & idNum & "'"
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub UpdateTransmittalNum(ByVal transNum As String)
On Error GoTo ErrHandler
    Dim query As String
    query = "Update Medicare..tbMedIndexNumber set TransNum = '" & Val(transNum) + 1 & "'"
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function GetTransmittalNum() As String
GetTransmittalNum = ""
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As String
    Dim query As String
    query = "Select TransNum from Medicare..tbMedIndexNumber"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = !transNum
        End If
    End With
    Set rec = Nothing
    
    GetTransmittalNum = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetBatchNum(ByVal idNum As String) As String
GetBatchNum = Empty
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As String
    Dim query As String
    query = "SELECT ISNULL(TransmittalNum,'') [value] FROM Medicare..tbMedPatient WHERE IdNum = '" & idNum & "'"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!value)
        End If
    End With
    Set rec = Nothing
    
    GetBatchNum = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetBatchDate(ByVal idNum As String) As String
GetBatchDate = Empty
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim result As String
    Dim query As String
    query = "SELECT TransDate [value] FROM Medicare..tbMedPatient WHERE IdNum = '" & idNum & "'"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!value)
        End If
    End With
    Set rec = Nothing
    
    GetBatchDate = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub DeleteRawPdfData(ByVal rawFileName As String, ByVal pHosNum As String, ByVal pDatabaseName As String, ByVal pTableName As String)
On Error GoTo ErrHandler
    Dim query As String
    ' DELETE QUERY
'    query = "DELETE FROM Patient_Files..tbPatientDocs WHERE HospNum = '" & pHosNum & "' AND FileName = '" & rawFileName & "' AND DocType = 'PHIC Docs' "
    query = "DELETE FROM " & pDatabaseName & ".." & pTableName & " WHERE HospNum = '" & pHosNum & "' AND FileName = '" & rawFileName & "' AND DocType = 'PHIC Docs' "
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub LoadRawPdfData(ByVal rawFileName As String, ByVal pHospNum As String, ByVal pDatabaseName As String, ByVal pTableName As String)
On Error GoTo ErrHandler:
    Dim counter As Integer
    Dim strStream: Set strStream = New ADODB.Stream
    
    Dim strSql As String
'    strSql = "SELECT FileData FROM Patient_Files..tbPatientDocs "
    strSql = "SELECT FileData FROM " & pDatabaseName & ".." & pTableName & " "
    strSql = strSql & "WHERE HospNum = '" & pHospNum & "' "
    strSql = strSql & "AND FileName = '" & rawFileName & "' "
    
    counter = 1
    Dim rec: Set rec = New ADODB.Recordset
    rec.Open strSql, MedsysUser.SQLConnection, adOpenDynamic, adLockOptimistic
    If rec.EOF = False Then
        strStream.Type = adTypeBinary
        strStream.Open
        strStream.Write rec!FileData
        Dim strfilename: strfilename = App.path & "\Dump\" & rawFileName
        strStream.SaveToFile strfilename, adSaveCreateOverWrite
        strStream.Close
    counter = counter + 1
    rec.MoveNext
    End If
    'Set strstream = Nothing
    Set rec = Nothing
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SaveRawPdfData(ByVal rawFileName As String, ByVal fileExtension As String, ByVal pHospNum As String, ByVal pIdNum As String)
On Error GoTo ErrHandler:
    Dim completeFileName: completeFileName = rawFileName & "." & fileExtension
    Dim strStream: Set strStream = New ADODB.Stream
    strStream.Type = adTypeBinary
    strStream.Open
    strStream.LoadFromFile App.path & "\Dump\" & completeFileName
    
    Dim rec: Set rec = New ADODB.Recordset
    With rec
        If .State = adStateOpen Then .Close
        .CursorLocation = adUseServer
        
        Dim strSql As String
                
'        strSql = "SELECT ConversionTime, HospNum, DocType, FileName, FileType, FileData, IDNum FROM Patient_Files..tbPatientDocs "
        strSql = "SELECT ConversionTime, HospNum, DocType, FileName, FileType, FileData, IDNum FROM " & DocumentDbName & ".." & DocumentTableName & " "
        strSql = strSql & "WHERE HospNum = '" & pHospNum & "' "
        strSql = strSql & "AND FileName = '" & completeFileName & "' "
        
        .Open strSql, MedsysUser.SQLConnection, adOpenKeyset, adLockOptimistic
        If .EOF Then
            .AddNew
            .Fields("ConversionTime").value = DateTime.Now
            .Fields("HospNum").value = pHospNum
            .Fields("DocType").value = "PHIC Docs"
            .Fields("FileName").value = completeFileName
            .Fields("FileType").value = "." & fileExtension
            .Fields("FileData").value = strStream.Read
            .Fields("IDNum").value = pIdNum
            .Update
        End If
        .Close
    End With
    strStream.Close
    Set strStream = Nothing
    Set rec = Nothing
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function GetDocumentFileNames(Id As String) As String()
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim query As String
    Dim result() As String
    
    query = "SELECT STUFF( "
    query = query & "(SELECT DISTINCT CAST(';' + ISNULL(RawFileName,'') AS VARCHAR) "
    query = query & "FROM MEDICARE..tbMedSupportingDocuments "
    query = query & "WHERE IdNum = '" & Id & "' "
    query = query & "FOR XML PATH('')) "
    query = query & ", 1, 1, '') AS [value]"
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = Split(!value, ";")
        End If
    End With
    Set rec = Nothing
    
    GetDocumentFileNames = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub CleanLoggedRthData(ByVal ClaimSeriesLhio As String)
On Error GoTo ErrHandler
    Dim query As String
    ' DELETE QUERY
    query = "DELETE FROM MEDICARE..tbMedEClaimsRthReasons WHERE ClaimSeriesLhio = '" & ClaimSeriesLhio & "'"
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub LogRthData(ByVal ClaimSeriesLhio As String, ByVal Deficiency As String, ByVal Remarks As String)
On Error GoTo ErrHandler
    Dim query As String
    ' INSERT QUERY
    query = "INSERT INTO MEDICARE..tbMedEClaimsRthReasons "
    query = query & " (ClaimSeriesLhio,Deficiency,Remarks) "
    query = query & " VALUES "
    query = query & " ('" & ClaimSeriesLhio & "', '" & ReplaceApostrophe(Deficiency) & "', '" & ReplaceApostrophe(Remarks) & "') "
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function HasSuffix(Id As String, isPatient As Boolean) As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim query As String
    Dim result As Boolean
    result = False
    
    If isPatient Then
        query = "SELECT TOP 1 ISNULL(PatientHasSuffix,0) AS VALUE FROM MEDICARE..tbMedMemberEligibility WHERE IdNum = '" & Id & "'"
    Else
        query = "SELECT TOP 1 ISNULL(MemberHasSuffix,0) AS VALUE FROM MEDICARE..tbMedMemberEligibility WHERE IdNum = '" & Id & "'"
    End If
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = !value
        End If
    End With
    Set rec = Nothing
    
    HasSuffix = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function MiddleNameUseNA(Id As String, isPatient As Boolean) As Boolean
On Error GoTo ErrHandler
    Dim rec As New ADODB.Recordset
    Dim query As String
    Dim result As Boolean
    result = False
    
    If isPatient Then
        query = "SELECT TOP 1 ISNULL(PatientMNameUseNA,0) AS VALUE FROM MEDICARE..tbMedMemberEligibility WHERE IdNum = '" & Id & "'"
    Else
        query = "SELECT TOP 1 ISNULL(MemberMNameUseNA,0) AS VALUE FROM MEDICARE..tbMedMemberEligibility WHERE IdNum = '" & Id & "'"
    End If
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = !value
        End If
    End With
    Set rec = Nothing
    
    MiddleNameUseNA = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub SaveSoaBenefitCharges(HospNum As String, idNum As String, soaBenefitChargesUdt As SoaBenefitCharges)
    Dim query As String
On Error GoTo ErrHandler
    If soaBenefitChargesUdt.RecordFound Then
        query = "UPDATE MEDICARE..tbMedActual "
        query = query & "SET ActualRoomBoard = '" & soaBenefitChargesUdt.TotalActualHCIFee & "', "
        query = query & "MedRoomBoard = '" & soaBenefitChargesUdt.TotalBenefitHCIFee & "', "
        query = query & "HciDiscount = '" & soaBenefitChargesUdt.TotalHCIDiscount & "', "
        query = query & "ActualPfFee = '" & soaBenefitChargesUdt.TotalActualPfFee & "', "
        query = query & "BenefitPfFee = '" & soaBenefitChargesUdt.TotalBenefitPfFee & "', "
        query = query & "PfDiscount = '" & soaBenefitChargesUdt.TotalPfDiscount & "', "
        query = query & "DrugsOutside = '" & soaBenefitChargesUdt.TotalDrug & "', "
        query = query & "LabOutside = '" & soaBenefitChargesUdt.TotalLab & "', "
        query = query & "FirstICDRvs = '" & soaBenefitChargesUdt.FirstCaseRate & "', "
        query = query & "SecondIcdRvs = '" & soaBenefitChargesUdt.SecondCaseRate & "', "
        query = query & "PaidBy = '" & soaBenefitChargesUdt.HciPaidBy & "', "
        query = query & "DocPaidBy = '" & soaBenefitChargesUdt.PfPaidBy & "', "
        query = query & "PackageId = '', "
        query = query & "PackageIdSecondary = '', "
        query = query & "ComputedDate = '" & DateTime.Now & "', "
        query = query & "UserID = '" & MedsysUser.EmployeeCode & "' "
        query = query & "WHERE IdNum = '" & idNum & "'"
    Else
        query = "INSERT INTO MEDICARE..tbMedActual "
        query = query & "(IdNum, HospNum, ActualRoomBoard, MedRoomBoard, HciDiscount, ActualPfFee, BenefitPfFee, PfDiscount, "
        query = query & "DrugsOutside, LabOutside, FirstICDRvs, SecondIcdRvs, PaidBy, DocPaidBy, PackageId, PackageIdSecondary, ComputedDate, UserID) "
        query = query & "VALUES "
        query = query & "('" & idNum & "', '" & HospNum & "', '" & soaBenefitChargesUdt.TotalActualHCIFee & "', '" & soaBenefitChargesUdt.TotalBenefitHCIFee _
                            & "', '" & soaBenefitChargesUdt.TotalHCIDiscount & "', '" & soaBenefitChargesUdt.TotalActualPfFee & "', '" & soaBenefitChargesUdt.TotalBenefitPfFee _
                            & "', '" & soaBenefitChargesUdt.TotalPfDiscount & "', '" & soaBenefitChargesUdt.TotalDrug & "','" & soaBenefitChargesUdt.TotalLab _
                            & "', '" & soaBenefitChargesUdt.FirstCaseRate & "', '" & soaBenefitChargesUdt.SecondCaseRate & "', '" & soaBenefitChargesUdt.HciPaidBy _
                            & "', '" & soaBenefitChargesUdt.PfPaidBy & "', '', '', '" & DateTime.Now & "', '" & MedsysUser.EmployeeCode & "')"
    End If
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function LoadSoaBenefitCharges(idNum As String) As SoaBenefitCharges
    Dim recSet As New ADODB.Recordset
    Dim query As String
    Dim result As SoaBenefitCharges
    result.RecordFound = False
On Error GoTo ErrHandler
    query = "SELECT ISNULL(ActualRoomBoard,0) [ActualRoomBoard], ISNULL(MedRoomBoard,0) [MedRoomBoard], ISNULL(HciDiscount,0) [HciDiscount], "
    query = query + "ISNULL(ActualPfFee,0) [ActualPfFee], ISNULL(BenefitPfFee,0) [BenefitPfFee], ISNULL(PfDiscount,0) [PfDiscount], ISNULL(DrugsOutside,0) [DrugsOutside], "
    query = query + "ISNULL(LabOutside,0) [LabOutside], FirstICDRvs, SecondIcdRvs, PaidBy, DocPaidBy "
    query = query + "FROM Medicare..tbMedActual "
    query = query + "WHERE IdNum = '" & idNum & "' "
    With recSet
        If .State > 0 Then .Close
           .CursorLocation = adUseClient
           .Open query, MedsysUser.SQLConnection, adOpenDynamic
           If .RecordCount > 0 Then
                result.RecordFound = True
                result.TotalActualHCIFee = !ActualRoomBoard
                result.TotalHCIDiscount = !HCIDiscount
                result.TotalBenefitHCIFee = !MedRoomBoard
                result.TotalActualPfFee = !ActualPfFee
                result.TotalPfDiscount = !PFDiscount
                result.TotalBenefitPfFee = !BenefitPfFee
                result.TotalDrug = !DrugsOutside
                result.TotalLab = !LabOutSide
                result.FirstCaseRate = ValidateNullValue(!FirstIcdRvs)
                result.SecondCaseRate = ValidateNullValue(!SecondIcdRvs)
                result.HciPaidBy = ValidateNullValue(!paidBy)
                result.PfPaidBy = ValidateNullValue(!DocPaidBy)
           End If
        If .State > 0 Then .Close
    End With

    LoadSoaBenefitCharges = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadPatientList(keyLastName As String, keyFirstName As String, keyMiddleName As String) As PatientInformation()
    Dim recSet As New ADODB.Recordset
    Dim query As String
    Dim result() As PatientInformation
On Error GoTo ErrHandler
    query = "SELECT HospNum, LastName, FirstName, MiddleName, BirthDate, Sex, HouseStreet, Barangay, ZipCode "
    query = query + "FROM PATIENT_DATA..tbmaster "
    query = query + "WHERE 1=1 AND BirthDate IS NOT NULL "
    If keyLastName <> Empty Then
        query = query + "AND LastName like '" & keyLastName & "%' "
    End If
    If keyFirstName <> Empty Then
        query = query + "AND FirstName like '" & keyFirstName & "%' "
    End If
    If keyMiddleName <> Empty Then
        query = query + "AND MiddleName like '" & keyMiddleName & "%' "
    End If
    
    With recSet
        If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Open query, MedsysUser.SQLConnection
            If .RecordCount > 0 Then
                ReDim result(.RecordCount - 1)
                Dim intRow As Integer
                intRow = 0
                Do While Not .EOF
                    result(intRow).HospNumber = !HospNum
                    result(intRow).info.LastName = RemoveSuffix(ReplaceBackApostrophe(ValidateNullValue(!LastName)))
                    result(intRow).info.FirstName = RemoveSuffix(ReplaceBackApostrophe(ValidateNullValue(!FirstName)))
                    result(intRow).info.MiddleName = ValidateNullValue(!MiddleName)
                    Dim Suffix As String
                    Suffix = GetSuffix(ReplaceBackApostrophe(ValidateNullValue(!FirstName)))
                    If Suffix = Empty Then
                        Suffix = GetSuffix(ReplaceBackApostrophe(ValidateNullValue(!LastName)))
                    End If
                    result(intRow).info.Suffix = Suffix
                    result(intRow).info.BirthDate = !BirthDate
                    result(intRow).info.Gender = !Sex
                    result(intRow).info.Address.Street = ValidateNullValue(!HouseStreet)
                    result(intRow).info.Address.Barangay = ValidateNullValue(!Barangay)
                    result(intRow).info.Address.ZipCode = ValidateNullValue(!ZipCode)
    
                    intRow = intRow + 1
                    .MoveNext
                Loop
            End If
        .Close
    End With
    Set recSet = Nothing
    
    LoadPatientList = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadAPR(idNum As String) As AprInfo
    Dim recSet As New ADODB.Recordset
    Dim query As String
    Dim result As AprInfo
    With result
        .AprDateSigned = DateTime.Now
        .AprRelCode = Empty
        .AprRelDesc = Empty
        .AprReasonCode = Empty
        .AprReasonDesc = Empty
        .AprThumbmarkedBy = Empty
    End With
On Error GoTo ErrHandler
    query = "SELECT ISNULL(AprDateSigned, GETDATE()) AprDateSigned, "
    query = query + "ISNULL(AprRelCode, '') AprRelCode, "
    query = query + "ISNULL(AprRelDesc, '') AprRelDesc, "
    query = query + "ISNULL(AprReasonCode, '') AprReasonCode, "
    query = query + "ISNULL(AprReasonDesc, '') AprReasonDesc, "
    query = query + "ISNULL(AprThumbmarkedBy, '') AprThumbmarkedBy, "
    query = query + "ISNULL(IsEmergency, 'N') IsEmergency "
    query = query + "FROM MEDICARE..tbMedPatient "
    query = query + "WHERE IdNum = '" & idNum & "' "
    With recSet
        If .State > 0 Then .Close
           .CursorLocation = adUseClient
           .Open query, MedsysUser.SQLConnection, adOpenDynamic
            If .RecordCount > 0 Then
                result.AprDateSigned = !AprDateSigned
                result.AprRelCode = !AprRelCode
                result.AprRelDesc = !AprRelDesc
                result.AprReasonCode = !AprReasonCode
                result.AprReasonDesc = !AprReasonDesc
                result.AprThumbmarkedBy = !AprThumbmarkedBy
                result.IsEmergency = !IsEmergency
            End If
        If .State > 0 Then .Close
    End With
    
    LoadAPR = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub SaveAPR(idNum As String, aprInfoUdt As AprInfo)
    Dim query As String
On Error GoTo ErrHandler
    query = "UPDATE Medicare..tbMedPatient "
    query = query & "SET AprDateSigned = '" & aprInfoUdt.AprDateSigned & "', "
    query = query & "AprRelCode = '" & aprInfoUdt.AprRelCode & "', "
    query = query & "AprRelDesc = '" & aprInfoUdt.AprRelDesc & "', "
    query = query & "AprReasonCode = '" & aprInfoUdt.AprReasonCode & "', "
    query = query & "AprReasonDesc = '" & aprInfoUdt.AprReasonDesc & "', "
    query = query & "AprThumbmarkedBy = '" & aprInfoUdt.AprThumbmarkedBy & "', "
    query = query & "IsEmergency = '" & aprInfoUdt.IsEmergency & "' "
    query = query & "WHERE IdNum = '" & idNum & "'"
        
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function LoadDiagInfo(idNum As String) As DiagnosisInfo()
    Dim rec As New ADODB.Recordset
    Dim SQL As String
    Dim result() As DiagnosisInfo
On Error GoTo ErrHandler
    SQL = "SELECT DiagID, ICDCode, Diagnosis, RVSCode, RelatedOperation, DateOfOperation, Laterality "
    SQL = SQL & " FROM MEDICARE..tbMedDiagnosis "
    SQL = SQL & " WHERE 1=1 "
    SQL = SQL & " AND idNum = '" & idNum & "' "
    SQL = SQL & " ORDER BY DiagID "
    With rec
        If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Open SQL, MedsysUser.SQLConnection
            If .RecordCount > 0 Then
                ReDim result(.RecordCount - 1)
                Dim intRow As Integer
                intRow = 0
                Do While Not .EOF
                    result(intRow).DiagId = !DiagId
                    result(intRow).IcdCode = ValidateNullValue(!IcdCode)
                    result(intRow).Diagnosis = ValidateNullValue(!Diagnosis)
                    result(intRow).RvsCode = ValidateNullValue(!RvsCode)
                    result(intRow).RelatedOperation = ValidateNullValue(!RelatedOperation)
                    result(intRow).DateOfOperation = !DateOfOperation
                    result(intRow).Laterality = ValidateNullValue(!Laterality)
    
                    intRow = intRow + 1
                    .MoveNext
                Loop
            End If
        .Close
    End With
    Set rec = Nothing
    
    LoadDiagInfo = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub CleanUpDiagnosis(idNum As String)
    Dim queryCommand As String
On Error GoTo ErrHandler
    queryCommand = "DELETE FROM MEDICARE..tbMedDiagnosis "
    queryCommand = queryCommand & "WHERE 1=1 "
    queryCommand = queryCommand & "AND IdNum = '" & idNum & "'"
    
    MedsysUser.SQLConnection.Execute queryCommand
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SaveDiagnosis(IdNumber As String, _
                        DiagId As Integer, _
                        Diagnosis As String, _
                        IcdCode As String, _
                        RelatedOperation As String, _
                        RvsCode As String, _
                        DateOfOperation As String, _
                        Laterality As String)
    Dim queryCommand As String
On Error GoTo ErrHandler
    queryCommand = "INSERT INTO MEDICARE..tbMedDiagnosis "
    queryCommand = queryCommand & " (IdNum, DiagID, Diagnosis, ICDCode, RelatedOperation, RVSCode, DateOfOperation, Laterality) "
    queryCommand = queryCommand & " VALUES "
    queryCommand = queryCommand & "("
    queryCommand = queryCommand & "'" & IdNumber & "',"
    queryCommand = queryCommand & "'" & DiagId & "',"
    queryCommand = queryCommand & "'" & Diagnosis & "',"
    queryCommand = queryCommand & "'" & IcdCode & "',"
    queryCommand = queryCommand & "'" & RelatedOperation & "',"
    queryCommand = queryCommand & "'" & RvsCode & "',"
    queryCommand = queryCommand & "'" & DateOfOperation & "',"
    queryCommand = queryCommand & "'" & Laterality & "'"
    queryCommand = queryCommand & ")"
    
    MedsysUser.SQLConnection.Execute queryCommand
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function LoadDoctorCharges(idNum As String, doctType As String) As DoctorCharges()
    Dim rec As New ADODB.Recordset
    Dim SQL As String
    Dim result() As DoctorCharges
On Error GoTo ErrHandler

    SQL = "Medicare..Medic_DoctorInfo '" & idNum & "', '" & doctType & "'"
    With rec
        If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Open SQL, MedsysUser.SQLConnection
            If .RecordCount > 0 Then
                ReDim result(.RecordCount - 1)
                Dim intRow As Integer
                intRow = 0
                Do While Not .EOF
                    result(intRow).DocCode = ValidateNullValue(![DocCode])
                    result(intRow).DoctorName = ValidateNullValue(![DoctorName])
                    result(intRow).ServicePerformed = ValidateNullValue(![ServicePerformed])
                    result(intRow).DatePerformed = ValidateNullValue(![DatePerformed])
                    result(intRow).ActualPf = ![ActualPf]
                    result(intRow).DiscountPf = 0
                    result(intRow).PhilHealthPf = ![MedicarePF]
                    result(intRow).PatientPf = ![PatientPf]
                    result(intRow).TinNumber = ValidateNullValue(![TinNumber])
                    result(intRow).PHICNumber = ValidateNullValue(![PHICNumber])
                    result(intRow).DocCount = !DocCount
    
                    intRow = intRow + 1
                    .MoveNext
                Loop
            End If
        .Close
    End With
    Set rec = Nothing
    
    LoadDoctorCharges = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub DeleteDoctors(IdNumber As String)
Dim queryCommand As String
On Error GoTo ErrHandler
    queryCommand = "DELETE FROM MEDICARE..tbMedDoctors "
    queryCommand = queryCommand & "WHERE 1=1 "
    queryCommand = queryCommand & "AND IdNum = '" & IdNumber & "'"
    
    MedsysUser.SQLConnection.Execute queryCommand
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

' docType
' 1 : Physician
' 2 : Surgeon
' 3 : Cardiologist
' 4 : Anesthesiologist
' 5 : Obstetrician
' 6 : Pediatrician
Public Sub SaveDoctor(IdNumber As String, _
                        DocCode As String, _
                        ServicePerformed As String, _
                        serviceDate As String, _
                        ActualPf As String, _
                        DiscountPf As String, _
                        PhilHealthPf As String, _
                        PatientPf As String, _
                        DocCount As String, _
                        docType As Integer)
    Dim queryCommand As String
On Error GoTo ErrHandler
    Select Case docType
        Case 1
            queryCommand = "Medicare..Medic_SavePhysicianInfo "
        Case 2
            queryCommand = "Medicare..Medic_SaveSurgeonInfo "
        Case 3
            queryCommand = "Medicare..Medic_SaveCardiologistInfo "
        Case 4
            queryCommand = "Medicare..Medic_SaveAnesthesiologistInfo "
        Case 5
            queryCommand = "Medicare..Medic_SaveObstetricianInfo "
        Case 6
            queryCommand = "Medicare..Medic_SavePediatricianInfo "
    End Select
    queryCommand = queryCommand & "'" & IdNumber & "',"
    queryCommand = queryCommand & "'" & DocCode & "',"
    queryCommand = queryCommand & "'" & ServicePerformed & "',"
    queryCommand = queryCommand & "'" & serviceDate & "',"
    queryCommand = queryCommand & "'" & ActualPf & "',"
    queryCommand = queryCommand & "'" & PhilHealthPf & "',"
    queryCommand = queryCommand & "'" & PatientPf & "',"
    queryCommand = queryCommand & "'" & DocCount & "'"
    
    MedsysUser.SQLConnection.Execute queryCommand
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub SaveConfinementInfo(confinementInfoUdt As ConfinementInfo)
    Dim queryCommand As String
On Error GoTo ErrHandler
    queryCommand = "Medicare..Medic_SaveConfinementInfo "
    queryCommand = queryCommand & "'" & confinementInfoUdt.IdNumber & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.isPatientReferred & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.NameOfReferralHCI & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ReferralAddress & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ReferralReason & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.patientDisposition & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.accomodationType & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsHemoProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsPDProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsRLINACProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsRCOBALTProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsBTProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsBrachyProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsChemoProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsSDProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.HemoProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.PDProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.RLINACProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.RCOBALTProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.BTProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.BrachyProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ChemoProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.SDProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ZBPCode & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ABPackageDayZero & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ABPackageDayThree & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ABPackageDaySeven & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ABPackageRIG & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ABPackageOthers & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.MCPPackageFirst & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.MCPPackageSecond & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.MCPPackageThird & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.MCPPackageFourth & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareIDON & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareEStSC & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareTCC & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareEP & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareWotN & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareVkA & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareBCGV & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareNsoMBfEBFI & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.ENewbornCareHepaB & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.tbDotsPackge & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.NewbornCarePackge & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.LaboratoryNumber & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.LMPPackage & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.CataractPreAuth & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.LeftEyeIOLStickerNumber & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.LeftEyeIOLExpiryDate & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.RightEyeIOLStickerNumber & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.RightEyeIOLExpiryDate & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.NCPFilterCardNo & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IMRTProcedureDates & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.IsImrtProcedure & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.RefferedHciAccreCode & "',"
    queryCommand = queryCommand & "'" & confinementInfoUdt.NTPCardNumber & "'"
    
    MedsysUser.SQLConnection.Execute queryCommand
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub CleanUpClaimsStatusDump()
    Dim queryCommand As String
On Error GoTo ErrHandler
    queryCommand = "DELETE FROM MEDICARE..tbMedEClaimsStatusReport_Dump "
    queryCommand = queryCommand & "WHERE 1=1 "
    queryCommand = queryCommand & "AND UserId = '" & MedsysUser.EmployeeCode & "'"
    
    MedsysUser.SQLConnection.Execute queryCommand
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function AddClaimStatusReport(ByVal ClaimSeriesLhio As String) As Boolean
    AddClaimStatusReport = False
    Dim query As String
On Error GoTo ErrHandler
    query = "INSERT INTO MEDICARE..tbMedEClaimsStatusReport_Dump "
    query = query & "(ClaimSeriesLhio, UserId)"
    query = query & "VALUES "
    query = query & "('" & ClaimSeriesLhio & "', '" & MedsysUser.EmployeeCode & "')"
    
    MedsysUser.SQLConnection.Execute query
    AddClaimStatusReport = True
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function DumpClaimsStatusReport(ByVal ClaimSeriesLhio As String, _
                                        ByVal memberPin As String, _
                                        ByVal PatientName As String, _
                                        ByVal ConfinementPeriod As String, _
                                        ByVal ClaimDateReceived As String, _
                                        ByVal ClaimStatus As String, _
                                        ByVal voucherNo As String, _
                                        ByVal VoucherDate As String, _
                                        ByVal CheckNo As String, _
                                        ByVal CheckDate As String, _
                                        ByVal CheckAmount As String, _
                                        ByVal ClaimAmount As String, _
                                        ByVal TotalClaimAmountPaid As String, _
                                        ByVal Remarks As String) As Boolean
    DumpClaimsStatusReport = False
    Dim query As String
On Error GoTo ErrHandler

    If ProgSettingsGbl.IsProgramSettingExists("911125DC-D240-453D-8097-63AEC5DB6D9F") Then ' CLAIMS INQUIRY - DISPLAY NOT FETCH DATA
        query = "UPDATE MEDICARE..tbMedEClaimsStatusReport_Dump "
        query = query & "SET MemberPin = '" & memberPin & "', " _
                        & "PatientName = '" & PatientName & "', " _
                        & "ConfinementPeriod = '" & ConfinementPeriod & "', " _
                        & "ClaimDateReceived = '" & ClaimDateReceived & "', " _
                        & "ClaimStatus = '" & ClaimStatus & "', " _
                        & "VoucherNo = '" & voucherNo & "', " _
                        & "VoucherDate = '" & VoucherDate & "', " _
                        & "CheckNo = '" & CheckNo & "', " _
                        & "CheckDate = '" & CheckDate & "', " _
                        & "CheckAmount = '" & CheckAmount & "', " _
                        & "ClaimAmount = '" & ClaimAmount & "', " _
                        & "TotalClaimAmountPaid = '" & TotalClaimAmountPaid & "', " _
                        & "Remarks = '" & Remarks & "' " _
                        & "WHERE ClaimSeriesLhio = '" & ClaimSeriesLhio & "' " _
                        & "AND UserId = '" & MedsysUser.EmployeeCode & "' "
    Else
        query = "INSERT INTO MEDICARE..tbMedEClaimsStatusReport_Dump "
        query = query & "VALUES "
        query = query & "('" & ClaimSeriesLhio & "','" _
                                & memberPin & "','" _
                                & PatientName & "','" _
                                & ConfinementPeriod & "','" _
                                & ClaimDateReceived & "', '" _
                                & ClaimStatus & "', '" _
                                & MedsysUser.EmployeeCode & "', '" _
                                & voucherNo & "', '" _
                                & VoucherDate & "', '" _
                                & CheckNo & "', '" _
                                & CheckDate & "', '" _
                                & CheckAmount & "', '" _
                                & ClaimAmount & "', '" _
                                & TotalClaimAmountPaid & "', '" _
                                & Remarks & "')"
    End If
    
    MedsysUser.SQLConnection.Execute query
    DumpClaimsStatusReport = True
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

'Public Function IsProgramSettingExists(Id As String) As Boolean
'    Dim query As String
'    query = "SELECT TOP 1 Id AS VALUE FROM tbMedEClaimsProgramSettings WHERE Id = '" & Id & "'"
'    IsProgramSettingExists = IIf(GetQueryResult(query, False) <> Empty, True, False)
'End Function

Public Function GetUtilities() As Utilities()
    Dim rec As New ADODB.Recordset
    Dim result() As Utilities
    Dim query As String
On Error GoTo ErrHandler
    query = "SELECT UtilityType, UtilityValue"
    query = query & " FROM MEDICARE..tbMedEclaimsUtility"
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).Type = !UtilityType
                result(intRow).value = !UtilityValue
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetUtilities = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetProgramSettings() As ProgramSettings()
    Dim rec As New ADODB.Recordset
    Dim result() As ProgramSettings
    Dim query As String
On Error GoTo ErrHandler
    query = "SELECT Id, Description"
    query = query & " FROM MEDICARE..tbMedEClaimsProgramSettings"
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).Id = !Id
                result(intRow).Description = !Description
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetProgramSettings = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadConfinementInfo(idNum As String) As ConfinementInfo
    Dim rec As New ADODB.Recordset
    Dim SQL As String
    Dim result As ConfinementInfo
On Error GoTo ErrHandler

    SQL = "Medicare..Medic_GetConfinementInfo '" & idNum & "' "
    With rec
        If .State > 0 Then .Close
           .CursorLocation = adUseClient
           .Open SQL, MedsysUser.SQLConnection, adOpenDynamic
           If .RecordCount > 0 Then
                result.isPatientReferred = !isPatientReferred
                result.RefferedHciAccreCode = !pReferredIHCPAccreCode
                result.patientDisposition = !patientDisposition
                result.accomodationType = !accomodationType
                result.IsHemoProcedure = !IsHemoProcedure
                result.IsPDProcedure = !IsPDProcedure
                result.IsRLINACProcedure = !IsRLINACProcedure
                result.IsRCOBALTProcedure = !IsRCOBALTProcedure
                result.IsBTProcedure = !IsBTProcedure
                result.IsBrachyProcedure = !IsBrachyProcedure
                result.IsChemoProcedure = !IsChemoProcedure
                result.IsSDProcedure = !IsSDProcedure
                result.HemoProcedureDates = !HemoProcedureDates
                result.PDProcedureDates = !PDProcedureDates
                result.RLINACProcedureDates = !RLINACProcedureDates
                result.RCOBALTProcedureDates = !RCOBALTProcedureDates
                result.BTProcedureDates = !BTProcedureDates
                result.BrachyProcedureDates = !BrachyProcedureDates
                result.ChemoProcedureDates = !ChemoProcedureDates
                result.SDProcedureDates = !SDProcedureDates
                result.MCPPackageFirst = !MCPPackageFirst
                result.MCPPackageSecond = !MCPPackageSecond
                result.MCPPackageThird = !MCPPackageThird
                result.MCPPackageFourth = !MCPPackageFourth
                result.tbDotsPackge = !tbDotsPackge
                result.NewbornCarePackge = !NewbornCarePackge
                result.ENewbornCareIDON = !ENewbornCareIDON
                result.ENewbornCareEStSC = !ENewbornCareEStSC
                result.ENewbornCareTCC = !ENewbornCareTCC
                result.ENewbornCareEP = !ENewbornCareEP
                result.ENewbornCareWotN = !ENewbornCareWotN
                result.ENewbornCareVkA = !ENewbornCareVkA
                result.ENewbornCareBCGV = !ENewbornCareBCGV
                result.ENewbornCareNsoMBfEBFI = !ENewbornCareNsoMBfEBFI
                result.ENewbornCareHepaB = !ENewbornCareHepaB
                result.NameOfReferralHCI = !NameOfReferralHCI
                result.ReferralAddress = !ReferralAddress
                result.ReferralReason = !ReferralReason
                result.ZBPCode = !ZBPCode
                result.ABPackageDayZero = !ABPackageDayZero
                result.ABPackageDayThree = !ABPackageDayThree
                result.ABPackageDaySeven = !ABPackageDaySeven
                result.ABPackageRIG = !ABPackageRIG
                result.ABPackageOthers = !ABPackageOthers
                result.LMPPackage = !LMPPackage
                result.LaboratoryNumber = !LaboratoryNumber
                result.NCPFilterCardNo = !NCPFilterCardNo
                result.CataractPreAuth = !CataractPreAuth
                result.LeftEyeIOLStickerNumber = !LeftEyeIOLStickerNumber
                result.LeftEyeIOLExpiryDate = !LeftEyeIOLExpiryDate
                result.RightEyeIOLStickerNumber = !RightEyeIOLStickerNumber
                result.RightEyeIOLExpiryDate = !RightEyeIOLExpiryDate
                result.NTPCardNumber = !NTPCardNo
           End If
        If .State > 0 Then .Close
    End With
    
    LoadConfinementInfo = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function CheckIfClaimSeriesLhioExists(ByVal idNum As String) As String
    Dim rec As New ADODB.Recordset
    Dim SQL As String
    Dim result As String
    result = ""
On Error GoTo ErrHandler
    
    SQL = "SELECT ClaimSeriesLhio FROM Medicare..tbMedElectronicClaims WHERE IdNum = '" & idNum & "'"
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!ClaimSeriesLhio)
        End If
    End With
    Set rec = Nothing
    
    CheckIfClaimSeriesLhioExists = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetDepartmentCode(employeeId As String, deptId As String) As Boolean
    Dim rec As New ADODB.Recordset
    Dim query As String
    query = "SELECT DepartmentID [value] "
    query = query & "FROM PASSWORD..tbPasswordMain "
    query = query & "WHERE 1=1 "
    query = query & "AND EmployeeID = '" & employeeId & "' "
    query = query & "AND DepartmentID = '" & deptId & "' "
    Dim result As Boolean
    result = False
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
'            result = ValidateNullValue(!value)
            result = True
        End If
    End With
    Set rec = Nothing
    
    GetDepartmentCode = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub UpdateDoctorAccreditationCode(DoctorId As String, DocCode As String)
    Dim queryCommand As String
On Error GoTo ErrHandler
    queryCommand = "UPDATE Build_File..tbCoDoctor "
    queryCommand = queryCommand & "SET PhilHealthNum = '" & DocCode & "' "
    queryCommand = queryCommand & "WHERE 1=1 "
    queryCommand = queryCommand & "AND DoctorID = '" & DoctorId & "' "
    
    MedsysUser.SQLConnection.Execute queryCommand
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function GetDoctorList(Optional searchKey As String = "") As DoctorInformation()
    Dim rec As New ADODB.Recordset
    Dim result() As DoctorInformation
    Dim query As String
On Error GoTo ErrHandler
    query = "SELECT TOP 100 DoctorID, LastName, FirstName, MiddleName, TaxNumber, PhilHealthNum, Birthday "
    query = query & "FROM Build_File..tbCoDoctor "
    query = query & "WHERE 1 = 1 "
    query = query & "AND Status = 'A' "
    
    If searchKey <> Empty Then
        query = query & "AND (LastName like '" & searchKey & "%') "
    End If

    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)

            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).DoctorId = ValidateNullValue(!DoctorId)
                result(intRow).DoctorAccreCode = Replace(ValidateNullValue(!PhilHealthNum), "-", "")
                result(intRow).TIN = Replace(ValidateNullValue(!TaxNumber), "-", "")
                result(intRow).info.LastName = ValidateNullValue(!LastName)
                result(intRow).info.FirstName = ValidateNullValue(!FirstName)
                result(intRow).info.MiddleName = ValidateNullValue(!MiddleName)
                result(intRow).info.BirthDate = ValidateNullValue(!Birthday)

                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing

    GetDoctorList = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetClaimNumber(AdmNumber As String) As String
    Dim rec As New ADODB.Recordset
    Dim query As String
    Dim result As String
On Error GoTo ErrHandler
    Dim batchDate As String
    batchDate = GetBatchDate(AdmNumber)

    query = "SELECT CAST((YEAR('" & batchDate & "') % 100) AS NVARCHAR(2)) + RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MONTH, '" & batchDate & "')), 2) [Code] "
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If Not .EOF Then
            result = !Code
        Else
            result = ""
        End If
    End With
    Set rec = Nothing
    
    GetClaimNumber = "" & result & AdmNumber
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub RemoveUploadedDocument(IdNumber As String, DocCode As String, docUrl As String)
    Dim queryCommand As String
On Error GoTo ErrHandler
    queryCommand = "DELETE FROM MEDICARE..tbMedSupportingDocuments "
    queryCommand = queryCommand & "WHERE 1=1 "
    queryCommand = queryCommand & "AND IdNum = '" & IdNumber & "' "
    queryCommand = queryCommand & "AND DocumentCode = '" & DocCode & "' "
    queryCommand = queryCommand & "AND DocumentUrl = '" & docUrl & "' "
    queryCommand = queryCommand & "AND IsSubmitted IS NULL"
    
    MedsysUser.SQLConnection.Execute queryCommand
    
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function IsTransmitted(idNum As String) As Boolean
    Dim rec As New ADODB.Recordset
    Dim query As String
    Dim result As Boolean
On Error GoTo ErrHandler
    query = "SELECT TOP 1 TransmittalNum "
    query = query & " FROM Medicare..tbMedPatient "
    query = query & " WHERE IdNum = '" & idNum & "' "
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        
        result = False
        If Not .EOF Then
            If ValidateNullValue(!transmittalNum) = Empty Then
                result = False
            Else
                result = True
            End If
        End If
    End With
    Set rec = Nothing
    
    IsTransmitted = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetUserName(userID As String) As String
    Dim rec As New ADODB.Recordset
    Dim query As String
    Dim result As String
On Error GoTo ErrHandler
    query = "SELECT TOP 1 (LastName + ', ' + FirstName + ' ' + MiddleName) [FullName] "
    query = query & " FROM PASSWORD..tbPasswordMaster "
    query = query & " WHERE EmployeeID = '" & userID & "' "
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If Not .EOF Then
            result = !FullName
        Else
            result = ""
        End If
    End With
    Set rec = Nothing
    
    GetUserName = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub SaveUserAccessSettings(userID As String, AccessID As String, ByVal CanAccess As Boolean)
    Dim rec As New ADODB.Recordset
    Dim query As String
    Dim queryCommand As String
On Error GoTo ErrHandler
    query = "SELECT TOP 1 * "
    query = query & " FROM MEDICARE..tbMedEClaimsUserAccess "
    query = query & " WHERE UserId = '" & userID & "' AND Code = '" & AccessID & "' "
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        
        If Not .EOF Then
            ' UPDATE
            queryCommand = "UPDATE MEDICARE..tbMedEClaimsUserAccess SET "
            queryCommand = queryCommand & " Status = " & IIf(CanAccess, 1, 0) & " "
            queryCommand = queryCommand & " WHERE UserId = '" & userID & "' "
            queryCommand = queryCommand & " AND Code = '" & AccessID & "' "
        Else
            ' INSERT
            queryCommand = "INSERT INTO MEDICARE..tbMedEClaimsUserAccess (UserId, Code, Status) "
            queryCommand = queryCommand & " VALUES ('" & userID & "',"
            queryCommand = queryCommand & "'" & AccessID & "',"
            queryCommand = queryCommand & IIf(CanAccess, 1, 0) & ")"
        End If
        MedsysUser.SQLConnection.Execute queryCommand
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function GetAccessTypes(userID As String) As accessTypes()
    Dim rec As New ADODB.Recordset
    Dim result() As accessTypes
    Dim query As String
On Error GoTo ErrHandler
    query = "SELECT A.Code [AccessId], A.Description, ISNULL(B.Status,0) [CanAccess] "
    query = query & " FROM Medicare..tbMedEClaimsAccess A "
    query = query & " LEFT JOIN Medicare..tbMedEClaimsUserAccess B "
    query = query & " ON A.Code = B.Code AND B.UserID = '" & userID & "' "
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).AccessID = !AccessID
                result(intRow).CanAccess = !CanAccess
                result(intRow).Description = !Description
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetAccessTypes = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetUploadedSCFDocs()
    
End Function

Public Function GetPatientList(ByVal isByAdmNumber As Boolean, ByVal PatientType As Integer, ByVal admDate As String, ByVal AdmNumber As String, _
                                ByVal orderBy As OrderByEnum, ByVal sortBy As SortByEnum, _
                                ByVal FirstName As String, ByVal MiddleName As String) As PatientInformation()
    Dim rec As New ADODB.Recordset
    Dim result() As PatientInformation
    Dim query As String
On Error GoTo ErrHandler
    query = "SELECT TOP 100 pat.HospNum, pat.IdNum, patMaster.LastName, patMaster.FirstName, patMaster.MiddleName, pat.AdmDate, pat.DcrDate "
    
    If PatientType = 0 Then
        query = query & "FROM PATIENT_DATA..tbpatient pat "
    Else
        query = query & "FROM PATIENT_DATA..tbOutPatient pat "
    End If
    
    query = query & "LEFT JOIN PATIENT_DATA..tbmaster patMaster "
    query = query & "ON pat.HospNum = patMaster.HospNum "
    query = query & "WHERE 1 = 1 "
    
    If isByAdmNumber <> True Then
        query = query & "AND CONVERT(VARCHAR(10), pat.AdmDate, 101) = '" & Format(admDate, "mm/dd/yyyy") & "' "
    Else
        If IsNumeric(Left(AdmNumber, 1)) Then
            query = query & "AND pat.IdNum = '" & AdmNumber & "' "
        Else
            query = query & "AND patMaster.LastName LIKE '" & AdmNumber & "%' "
            query = query & "AND patMaster.FirstName LIKE '%" & FirstName & "%' "
            query = query & "AND patMaster.MiddleName LIKE '" & MiddleName & "%' "
        End If
        
        ' extensive query
        ' query = query & "AND (pat.IdNum = '" & AdmNumber & "' "
        ' query = query & "OR patMaster.LastName like '" & AdmNumber & "%') "
        ' query = query & "OR REPLACE((patMaster.LastName + ',' + patMaster.FirstName + patMaster.MiddleName),' ','') LIKE REPLACE('" & AdmNumber & "%',' ','')) "
    End If
    
    Select Case orderBy
        Case OrderByEnum.enLastName
            query = query & "ORDER BY patMaster.LastName " & IIf(sortBy = enAsc, "ASC", "DESC")
        Case OrderByEnum.enFirstName
            query = query & "ORDER BY patMaster.FirstName " & IIf(sortBy = enAsc, "ASC", "DESC")
        Case OrderByEnum.enAdmissionDate
            query = query & "ORDER BY pat.AdmDate " & IIf(sortBy = enAsc, "ASC", "DESC")
    End Select
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).HospNumber = ValidateNullValue(!HospNum)
                result(intRow).AdmNumber = ValidateNullValue(!idNum)
                result(intRow).info.LastName = ValidateNullValue(!LastName)
                result(intRow).info.FirstName = ValidateNullValue(!FirstName)
                result(intRow).info.MiddleName = ValidateNullValue(!MiddleName)
                result(intRow).Confinement.AdmissionDate = ValidateNullValue(!admDate)
                result(intRow).Confinement.DischargeDate = ValidateNullValue(!dcrDate)
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetPatientList = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub UpdateSubmittedSupportingDocument(admNum As String, isRequired As Boolean)
    Dim query As String
On Error GoTo ErrHandler
    query = "UPDATE MEDICARE..tbMedSupportingDocuments "
    query = query & "SET IsSubmitted = 1 "
    query = query & "WHERE IdNum = '" & admNum & "' AND IsRequired = " & IIf(isRequired, 1, 0) & ""
    
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function GetDoctorAccreditationCode(searchKey As String) As String
    Dim rec As New ADODB.Recordset
    Dim query As String
    query = "SELECT REPLACE(PhilHealthNum, '-','') [Value] FROM Build_File..tbCoDoctor WHERE DoctorID = '" & searchKey & "'"
    Dim result As String
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!value)
        End If
    End With
    Set rec = Nothing
    
    GetDoctorAccreditationCode = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetIdNumByClaimSeriesLhio(searchKey As String) As String
    Dim rec As New ADODB.Recordset
    Dim query As String
    query = "SELECT TOP 1 IdNum [Value] FROM MEDICARE..tbMedElectronicClaims WHERE ClaimSeriesLhio = '" & searchKey & "'"
    Dim result As String
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = ValidateNullValue(!value)
        End If
    End With
    Set rec = Nothing
    
    GetIdNumByClaimSeriesLhio = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetAge(strBirthDate As String) As String
    Dim query As String
    query = "SELECT ISNULL(PATIENT_DATA.dbo.Fn_GetAge('" & strBirthDate & "','" & Format(Now, "mm/dd/yyyy") & "'),'') AS value"
    GetAge = GetQueryResult(query, False)
End Function

Public Function GetPatientHospNum() As String
    Dim query As String
    query = "SELECT top 1 HospNum [Value] "
    query = query + " FROM PATIENT_DATA..tbmaster "
    query = query + " ORDER BY CAST(HospNum AS bigint) DESC "
    
    Dim result: result = GetQueryResult(query, True)
    GetPatientHospNum = IIf(result = Empty, "1", result)
End Function

Public Function GetInPatientId() As String
    Dim query As String
    query = "SELECT top 1 IdNum [Value] "
    query = query + " FROM PATIENT_DATA..tbpatient "
    query = query + " ORDER BY CAST(IdNum AS bigint) DESC "
    
    Dim result: result = GetQueryResult(query, True)
    GetInPatientId = IIf(result = Empty, "1", result)
End Function

Public Function GetOutPatientId() As String
    Dim query As String
    query = "SELECT top 1 REPLACE(IdNum, 'B', '') [Value] "
    query = query + " FROM PATIENT_DATA..tbOutPatient "
    query = query + " ORDER BY CAST(REPLACE(IdNum, 'B', '') AS bigint) DESC "
    
    Dim result: result = GetQueryResult(query, True)
    GetOutPatientId = IIf(result = Empty, "1B", result & "B")
End Function

Public Function GetSupportingDocuments(ByVal admNum As String, isRequired As Boolean, Optional forAdditionalDocument As Boolean = False, Optional PPCode As String = "") As SupportingDocuments()
    Dim rec As New ADODB.Recordset
    Dim result() As SupportingDocuments
    Dim query As String
On Error GoTo ErrHandler
    query = "SELECT DocumentCode, DocumentUrl, ISNULL(IsSubmitted,0) [IsSubmitted], ISNULL(RawFileName, '') [RawFileName], ISNULL(FileType, '') [FileType], ISNULL(DatabaseName, 'PATIENT_FILES') [DatabaseName], ISNULL(TableName, '" & DocumentTableName & "') [TableName] "
    query = query & "FROM MEDICARE..tbMedSupportingDocuments "
    query = query & "WHERE IdNum = '" & admNum & "' AND IsRequired = " & IIf(isRequired, 1, 0) & " "
    ' 52244242-D629-41F8-A42D-0A95E33CDFFC - SHOW PATIENT BASED ON PROBLEM ID

    If forAdditionalDocument Then
        query = query & " AND ISNULL(IsSubmitted,0) = 0"
    End If
    
    If ProgSettingsGbl.IsProgramSettingExists("52244242-D629-41F8-A42D-0A95E33CDFFC") Then
        query = query & " AND PPCode = '" & PPCode & "' "
    End If
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).DocumentCode = !DocumentCode
                result(intRow).DocumentUrl = !DocumentUrl
                result(intRow).Submitted = IIf(!IsSubmitted, "YES", "NO")
                result(intRow).rawFileName = !rawFileName
                result(intRow).fileType = !fileType
                result(intRow).DatabaseName = !DatabaseName
                result(intRow).TableName = !TableName
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetSupportingDocuments = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetDocumentTypes() As DocumentTypes()
    Dim rec As New ADODB.Recordset
    Dim result() As DocumentTypes
    Dim query As String
    query = "SELECT Id, DocumentCode, Description FROM Medicare..tbMedDocumentTypes"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open query, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount)
            result(0).Id = "0"
            result(0).DocumentCode = "NONE"
            result(0).Description = "- SELECT -"
            
            Dim intRow As Integer
            intRow = 1
            Do While Not .EOF
                result(intRow).Id = ValidateNullValue(!Id)
                result(intRow).DocumentCode = ValidateNullValue(!DocumentCode)
                result(intRow).Description = ValidateNullValue(!Description)
                
                intRow = intRow + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetDocumentTypes = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Private Function GetQueryResult(ByVal SQL As String, ByVal increment As Boolean, Optional ByVal stringOnly As Boolean = False) As String
    Dim rec As New ADODB.Recordset
    Dim result As String
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            If stringOnly = True Then
                result = ValidateNullValue(!value)
            Else
                result = Val(ValidateNullValue(!value)) + IIf(increment, 1, 0)
            End If
        End If
    End With
    Set rec = Nothing
    
    GetQueryResult = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function SaveSupportingDocuments(ByVal admNum As String, ByVal doCode As String, ByVal docUrl As String, _
                                        ByVal uploadedBy As String, ByVal uploadedDate As Date, ByVal blnRequired As Boolean, ByVal encryptedData As String, _
                                        ByVal rawFileName As String, ByVal fileType As String, Optional PPCode As String = "") As Boolean
    SaveSupportingDocuments = False
    Dim query As String
On Error GoTo ErrHandler
    query = "INSERT INTO MEDICARE..tbMedSupportingDocuments "
    ' 52244242-D629-41F8-A42D-0A95E33CDFFC - SHOW PATIENT BASED ON PROBLEM ID
    If ProgSettingsGbl.IsProgramSettingExists("52244242-D629-41F8-A42D-0A95E33CDFFC") Then
        query = query & "(IdNum, DocumentCode, DocumentUrl, UploadedBy, UploadedDate, IsRequired, EncryptedData, rawFileName, fileType, PPCode, DatabaseName, TableName) "
        query = query & "VALUES "
        query = query & "('" & admNum & "','" & doCode & "','" & docUrl & "','" & uploadedBy & "','" & uploadedDate & "', '" & blnRequired & "', '" & encryptedData & "', '" & rawFileName & "', '" & fileType & "', '" & PPCode & "', '" & DocumentDbName & "', '" & DocumentTableName & "')"
    Else
        query = query & "(IdNum, DocumentCode, DocumentUrl, UploadedBy, UploadedDate, IsRequired, EncryptedData, rawFileName, fileType, DatabaseName, TableName) "
        query = query & "VALUES "
        query = query & "('" & admNum & "','" & doCode & "','" & docUrl & "','" & uploadedBy & "','" & uploadedDate & "', '" & blnRequired & "', '" & encryptedData & "', '" & rawFileName & "', '" & fileType & "', '" & DocumentDbName & "', '" & DocumentTableName & "')"
    End If
    
    MedsysUser.SQLConnection.Execute query
    SaveSupportingDocuments = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Private Function ReadFile(sFile As String) As Byte()
    Dim nFile       As Integer

    nFile = FreeFile
    Open sFile For Binary Access Read As #nFile
    If LOF(nFile) > 0 Then
        ReDim ReadFile(0 To LOF(nFile) - 1)
        Get nFile, , ReadFile
    End If
    Close #nFile
End Function

Public Function SaveElectronicClaim(ByVal idNum As String, ByVal transNum As String, ByVal TransmissionControlNumber As String, _
                                ByVal TransmissionDate As String, ByVal TransmissionTime As String, ByVal ReceiptTicketNumber As String, _
                                ByVal ClaimSeriesLhio As String, ByVal response As String) As Boolean
    SaveElectronicClaim = False
On Error GoTo ErrHandler

    Dim SQL As String
    SQL = "INSERT INTO Medicare..tbMedElectronicClaims "
    SQL = SQL & " (IdNum, TransmittalNo, TransmissionControlNumber, TransmissionDate, TransmissionTime, ReceiptTicketNumber, ClaimSeriesLhio, TransmittedBy, Response) "
    SQL = SQL & " VALUES "
    SQL = SQL & " ('" & idNum & "', '" & transNum & "', '" & TransmissionControlNumber & "', "
    SQL = SQL & " '" & TransmissionDate & "', '" & TransmissionTime & "', '" & ReceiptTicketNumber & "', '" & ClaimSeriesLhio & "', '" & MedsysUser.EmployeeCode & "', '" & response & "') "
    
    MedsysUser.SQLConnection.Execute SQL
    SaveElectronicClaim = True
    
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Sub SaveClaimSeriesLhio(ByVal idNum As String, ClaimSeriesLhio As String)
On Error GoTo ErrHandler

    Dim SQL As String
    SQL = "UPDATE Medicare..tbMedElectronicClaims "
    SQL = SQL & " SET ClaimSeriesLhio = '" & ClaimSeriesLhio & "' "
    SQL = SQL & " WHERE IdNum = '" & idNum & "' "
    
    MedsysUser.SQLConnection.Execute SQL
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

' patientType value:
' 0 : ALL (IN AND OUT-PATIENT)
' 1 : IN-Patient Only
' 2 : OUT-Patient Only
Public Function GetClaimSeriesLhioByDate(ByVal dateStart As String, _
                                            ByVal dateEnd As String, _
                                            ByVal admNo As String, _
                                            ByVal bySubmitDate As Boolean, _
                                            ByVal submitDate As String, _
                                            ByVal PatientType As String, _
                                            ByVal byDcrDate As Boolean) As String()
    Dim rec As New ADODB.Recordset
    Dim SQL As String
    Dim result() As String
On Error GoTo ErrHandler
    Dim blnIndividual As Boolean
    blnIndividual = True
'    If blnIndividual Then
        SQL = "SELECT A.ClaimSeriesLhio "
        SQL = SQL + " FROM MEDICARE..tbMedElectronicClaims A "
        SQL = SQL + " LEFT JOIN MEDICARE..tbMedPatient B "
        SQL = SQL + " On A.IdNum = B.IdNum "
        SQL = SQL + " WHERE 1 = 1 "
        SQL = SQL + " AND LEFT(REPLACE(A.TransmissionControlNumber,'-',''), 6) = '" & IIf(Len(ServiceKey.HospitalCode) = 6, ServiceKey.HospitalCode, "0" & ServiceKey.HospitalCode) & "'"
        
        If PatientType = PatientType_In Then
            SQL = SQL + " AND ISNUMERIC(A.IdNum) = 1"
        End If
        
        If PatientType = PatientType_Out Then
            SQL = SQL + " AND ISNUMERIC(A.IdNum) = 0"
        End If
        
        If bySubmitDate = True Then
            If submitDate = Empty Then
                SQL = SQL + " AND CAST(A.TransmissionDate AS DATE) >= CAST('" & dateStart & "' AS DATE) "
                SQL = SQL + " AND CAST(A.TransmissionDate AS DATE) <= CAST('" & dateEnd & "' AS DATE) "
            Else
                SQL = SQL + " AND A.TransmissionDate = '" & submitDate & "'"
            End If
        Else
            If admNo = Empty Then
                If byDcrDate Then
'                    SQL = SQL + " AND CONVERT(VARCHAR(10), b.DcrDate, 101) >= '" & dateStart & "' "
'                    SQL = SQL + " AND CONVERT(VARCHAR(10), b.DcrDate, 101) <= '" & dateEnd & "' "
                    SQL = SQL + " AND CAST(b.DcrDate AS DATE) >= CAST('" & dateStart & "' AS DATE) "
                    SQL = SQL + " AND CAST(b.DcrDate AS DATE) <= CAST('" & dateEnd & "' AS DATE) "
                Else
'                    SQL = SQL + " AND CONVERT(VARCHAR(10), b.AdmDate, 101) >= '" & dateStart & "' "
'                    SQL = SQL + " AND CONVERT(VARCHAR(10), b.DcrDate, 101) <= '" & dateEnd & "' "
                    SQL = SQL + " AND CAST(b.AdmDate AS DATE) >= CAST('" & dateStart & "' AS DATE) "
                    SQL = SQL + " AND CAST(b.DcrDate AS DATE) <= CAST('" & dateEnd & "' AS DATE) "
                End If
            Else
                SQL = SQL + " AND (A.IdNum = '" & admNo & "'"
                SQL = SQL + " OR A.ClaimSeriesLhio = '" & admNo & "')"
            End If
        End If
'    Else
'        SQL = "SELECT SUBSTRING((SELECT DISTINCT ',' + CAST(A.ClaimSeriesLhio AS VARCHAR) "
'        SQL = SQL + " FROM MEDICARE..tbMedElectronicClaims A "
'        SQL = SQL + " LEFT JOIN MEDICARE..tbMedPatient B "
'        SQL = SQL + " On A.IdNum = B.IdNum "
'        SQL = SQL + " WHERE 1 = 1 "
'        SQL = SQL + " AND LEFT(REPLACE(A.TransmissionControlNumber,'-',''), 6) = '" & IIf(Len(ServiceKey.HospitalCode) = 6, ServiceKey.HospitalCode, "0" & ServiceKey.HospitalCode) & "'"
'
'        If PatientType = PatientType_In Then
'            SQL = SQL + " AND ISNUMERIC(A.IdNum) = 1"
'        End If
'
'        If PatientType = PatientType_Out Then
'            SQL = SQL + " AND ISNUMERIC(A.IdNum) = 0"
'        End If
'
'        If bySubmitDate = True Then
'            SQL = SQL + " AND A.TransmissionDate = '" & submitDate & "'"
'        Else
'            If admNo = Empty Then
'                If byDcrDate Then
'                    SQL = SQL + " AND CONVERT(VARCHAR(10), b.DcrDate, 101) >= '" & dateStart & "' "
'                    SQL = SQL + " AND CONVERT(VARCHAR(10), b.DcrDate, 101) <= '" & dateEnd & "' "
'                Else
'                    SQL = SQL + " AND CONVERT(VARCHAR(10), b.AdmDate, 101) >= '" & dateStart & "' "
'                    SQL = SQL + " AND CONVERT(VARCHAR(10), b.DcrDate, 101) <= '" & dateEnd & "' "
'                End If
'            Else
'                SQL = SQL + " AND (A.IdNum = '" & admNo & "'"
'                SQL = SQL + " OR A.ClaimSeriesLhio = '" & admNo & "')"
'            End If
'        End If
'
'        SQL = SQL + " FOR XML PATH('')), 2,10000) AS [ClaimSeriesLhio] "
'    End If
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount)
            Dim counter As Integer
            counter = 0
            Do While Not .EOF
                result(counter) = ValidateNullValue(!ClaimSeriesLhio)
                counter = counter + 1
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    GetClaimSeriesLhioByDate = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadBatchList(ByVal memberType As String, ByVal transDate As String) As String()
    Dim rec As New ADODB.Recordset
    Dim SQL As String
    Dim result() As String
    ReDim result(0)
On Error GoTo ErrHandler
    SQL = "SELECT A.TransmittalNum [BatchNumber] "
    SQL = SQL + " FROM Medicare..tbMedPatient A "
    SQL = SQL + " LEFT JOIN Medicare..tbMedMemberEligibility B "
    SQL = SQL + " ON A.IdNum = B.IdNum "
    SQL = SQL + " WHERE 1=1 "
    SQL = SQL + " AND MemberType = '" + memberType + "'"
    SQL = SQL + " AND Convert(varchar(10), TransDate, 101) = '" + Format(transDate, "mm/dd/yyyy") + "'"
    
    'SQL = SQL + " AND B.IsFinal = 1 "
    
    SQL = SQL + " GROUP BY TransmittalNum "

    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                If ValidateNullValue(!BatchNumber) <> Empty Then
                    result(intRow) = ValidateNullValue(!BatchNumber)
                    intRow = intRow + 1
                End If
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    LoadBatchList = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadClaimList(ByVal transNum As String, ByVal memType As String, ByVal transDate As String) As ClaimListData()
    Dim rec As New ADODB.Recordset
    Dim SQL As String
    Dim result() As ClaimListData
    ReDim result(0)
On Error GoTo ErrHandler
    
    SQL = "SELECT A.IdNum [AdmissionNum], (A.LastName + ', ' + A.FirstName + ' ' + A.MiddleName) [PatientName], (Convert(varchar(10), A.AdmDate, 101) + '-' + CONVERT(varchar(10), A.DcrDate, 101)) [ConfinementPeriod], "
    SQL = SQL + " B.MemberNumber, (SELECT TOP 1 LastName + ', ' + FirstName + ' ' + MiddleName from Medicare..tbMedMember where MemberNumber = B.MemberNumber) [MemberName],"
    SQL = SQL + " (SELECT TOP 1 ClaimSeriesLhio FROM Medicare..tbMedElectronicClaims WHERE IdNum = A.IdNum) [ClaimSeriesLhio], "
    SQL = SQL + " (SELECT COUNT(IdNum) FROM Medicare..tbMedSupportingDocuments WHERE IdNum = A.IdNum) [WithDocuments], "
    SQL = SQL + " B.TrackingNumber [TrackingNumber], "
    SQL = SQL + " B.IsFinal [IsFinal] "
    SQL = SQL + " FROM Medicare..tbMedPatient A "
    SQL = SQL + " LEFT JOIN Medicare..tbMedMemberEligibility B "
    SQL = SQL + " ON A.Idnum = B.IdNum "
    SQL = SQL + " WHERE 1=1 "
    SQL = SQL + " AND A.TransmittalNum = '" + transNum + "' "
    SQL = SQL + " AND A.MemberType = '" + memType + "' "
    'SQL = SQL + " AND ISNULL(A.TransmittalNum, '') <> ''"
    SQL = SQL + " AND (A.TransmittalNum <> '' AND A.TransmittalNum IS NOT NULL) "
    SQL = SQL + " AND Convert(varchar(10), A.TransDate, 101) = '" & Format(transDate, "mm/dd/yyyy") & "'"
    SQL = SQL & " ORDER BY [PatientName]"
'    SQL = SQL + " AND B.IsFinal = 1"
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            ReDim result(.RecordCount - 1)
            Dim intRow As Integer
            intRow = 0
            Do While Not .EOF
                result(intRow).admissionNumber = ValidateNullValue(!admissionNum)
                result(intRow).PatientName = ValidateNullValue(!PatientName)
                result(intRow).ConfinementPeriod = ValidateNullValue(!ConfinementPeriod)
                result(intRow).MemberNumber = ValidateNullValue(!MemberNumber)
                result(intRow).MemberName = ValidateNullValue(!MemberName)
                result(intRow).ClaimSeriesLhio = ValidateNullValue(!ClaimSeriesLhio)
                result(intRow).WithDocuments = IIf(ValidateNullValue(!WithDocuments) = "0", "NO", "YES")
                result(intRow).WithTrackingNumber = IIf(Len(ValidateNullValue(!trackingNumber)) = 0, "NO", "YES")
                
                If Len(ValidateNullValue(!IsFinal)) = 0 Then
                    result(intRow).IsFinal = "NO"
                Else
                    result(intRow).IsFinal = IIf(ValidateNullValue(!IsFinal) = 0, "NO", "YES")
                End If
                intRow = intRow + 1
                
                .MoveNext
            Loop
        End If
    End With
    Set rec = Nothing
    
    LoadClaimList = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadClaimReceiptInformation(ByVal idNum As String, ByVal isWithClaimSeriesLhio As Boolean) As eReceipt
    Dim rec As New ADODB.Recordset
    Dim SQL As String
    Dim result As eReceipt
On Error GoTo ErrHandler

    If isWithClaimSeriesLhio Then
        SQL = "SELECT Response FROM Medicare..tbMedElectronicClaims WHERE IdNum = '" & idNum & "'"
    Else
        SQL = "SELECT Response FROM Medicare..tbMedeClaimsReceipt WHERE IdNum = '" & idNum & "'"
    End If
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = Translate_eReceipt(!response)
        End If
    End With
    Set rec = Nothing
    
    LoadClaimReceiptInformation = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function


Public Function GetCallType(idNum As String, memNumber As String) As String
    Dim result As String
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    ' 7F831F4B-8C93-4CB7-8009-66A942D50342    LOG LAST FINAL CALL DATA
    If ProgSettingsGbl.IsProgramSettingExists("7F831F4B-8C93-4CB7-8009-66A942D50342") Then
        SQL = "SELECT TOP 1 1 [IsFinal] FROM MEDICARE..tbMedEClaimsFinalCall WHERE IdNum = '" & idNum & "' AND MemPin = '" & memNumber & "'"
    Else
        SQL = "SELECT IsFinal FROM Medicare..tbMedMemberEligibility where IdNum = '" & idNum & "' AND MemberNumber = '" & memNumber & "'"
    End If
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If .RecordCount > 0 Then
            result = !IsFinal
        End If
        .Close
    End With
    Set rec = Nothing
    GetCallType = IIf(result = Empty, "0", result)
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadMemberEligibility(idNum As String, memNumber As String, Optional PPCode As String = "") As EligibilityResponse
    Dim result As EligibilityResponse
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    ' SQL = "SELECT Response FROM Medicare..tbMedMemberEligibility where IdNum = '" & idNum & "' AND MemberNumber = '" & memNumber & "'"
    ' 52244242-D629-41F8-A42D-0A95E33CDFFC - SHOW PATIENT BASED ON PROBLEM ID
    SQL = "SELECT TOP 1 Z.RESPONSE FROM "
    SQL = SQL & "("
    SQL = SQL & " SELECT Response FROM Medicare..tbMedMemberEligibility where IdNum = '" & idNum & "' AND MemberNumber = '" & memNumber & "' "
    
    ' 7F831F4B-8C93-4CB7-8009-66A942D50342    LOG LAST FINAL CALL DATA
    If ProgSettingsGbl.IsProgramSettingExists("7F831F4B-8C93-4CB7-8009-66A942D50342") Then
        SQL = SQL & "UNION ALL "
        SQL = SQL & "SELECT Response FROM Medicare..tbMedEClaimsFinalCall where IdNum = '" & idNum & "' AND MemPin = '" & memNumber & "'"
    End If
    
    SQL = SQL & ") Z "
    
    If ProgSettingsGbl.IsProgramSettingExists("52244242-D629-41F8-A42D-0A95E33CDFFC") Then
        SQL = SQL & " AND PPCode = '" & PPCode & "'"
    End If
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result = Translate_EligibilityResponse(ReplaceBackApostrophe(!response))
        End If
        .Close
    End With
    Set rec = Nothing
    LoadMemberEligibility = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadPatientInfo(Optional idNum As String = "") As PatientInformation
    Dim result As PatientInformation
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    SQL = "Medicare..Medic_GetForm1PatientInfo '" & idNum & "'"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result.HospNumber = ValidateNullValue(!HospNum)
            result.AdmNumber = ValidateNullValue(!idNum)
            result.info.PatientPin = ValidateNullValue(!PxPIN)
            result.info.LastName = ReplaceBackApostrophe(ValidateNullValue(RemoveSuffix(!LastName)))
            result.info.FirstName = ReplaceBackApostrophe(ValidateNullValue(RemoveSuffix(!FirstName)))
            result.info.MiddleName = ReplaceBackApostrophe(ValidateNullValue(!MiddleName))
            Dim Suffix As String
            Suffix = GetSuffix(ValidateNullValue(!FirstName))
            If Suffix = Empty Then
                Suffix = GetSuffix(ValidateNullValue(!LastName))
            End If
            result.info.Suffix = Suffix
            result.info.Gender = ValidateNullValue(!Gender)
            result.Confinement.AdmissionDate = !admDate
            result.Confinement.DischargeDate = !dcrDate
            result.info.BirthDate = !BirthDate
            result.info.Address.Street = ValidateNullValue(!HouseStreet)
            result.info.Address.Barangay = ValidateNullValue(!Barangay)
            result.info.Address.ZipCode = ValidateNullValue(!ZipCode)
        End If
        .Close
    End With
    Set rec = Nothing
    LoadPatientInfo = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadMemberInfo(memNumber As String) As MemberInformation
    Dim result As MemberInformation
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    SQL = SQL + "SELECT MemberNumber, FirstName, MiddleName, LastName, MemberType, HouseStreet, Barangay, ZipCode, Region, BirthDate, Sex, EmployerNum "
    SQL = SQL + "FROM MEDICARE..tbMedMember "
    SQL = SQL + "WHERE MemberNumber = '" & memNumber & "'"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result.Pin = ValidateNullValue(!MemberNumber)
            result.MemberShipType = ValidateNullValue(!memberType)
            result.info.LastName = ReplaceBackApostrophe(RemoveSuffix(ValidateNullValue(!LastName)))
            result.info.FirstName = ReplaceBackApostrophe(RemoveSuffix(ValidateNullValue(!FirstName)))
            result.info.MiddleName = ReplaceBackApostrophe(ValidateNullValue(!MiddleName))
            result.info.Address.Region = ValidateNullValue(!Region)
            Dim Suffix As String
            Suffix = GetSuffix(ValidateNullValue(!FirstName))
            If Suffix = Empty Then
                Suffix = GetSuffix(ValidateNullValue(!LastName))
            End If
            result.info.Suffix = Suffix
            result.info.Gender = ValidateNullValue(!Sex)
            result.info.BirthDate = !BirthDate
            result.info.Address.Street = ValidateNullValue(!HouseStreet)
            result.info.Address.Barangay = ValidateNullValue(!Barangay)
            result.info.Address.ZipCode = ValidateNullValue(!ZipCode)
            result.Employer.PEN = ValidateNullValue(!EmployerNum)
        End If
        .Close
    End With
    Set rec = Nothing
    LoadMemberInfo = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function LoadEmployerInfo(empNumber As String) As EmployerInformation
    Dim result As EmployerInformation
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    SQL = SQL + "SELECT EmployerNum, Name, Address "
    SQL = SQL + "FROM MEDICARE..tbMedEmployer "
    SQL = SQL + "WHERE EmployerNum = '" & empNumber & "'"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result.PEN = !EmployerNum
            result.Name = ReplaceBackApostrophe(!Name)
            result.Address = ReplaceBackApostrophe(!Address)
        End If
        .Close
    End With
    Set rec = Nothing
    LoadEmployerInfo = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetMemberNumber(idNum As String) As String
    Dim result As String
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    result = ""
    SQL = "SELECT ISNULL(MemberNumber, '') [MemberNumber] FROM MEDICARE..tbMedRelationship WHERE IDNum = '" & idNum & "'"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result = !MemberNumber
        End If
        .Close
    End With
    Set rec = Nothing
    GetMemberNumber = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetRelationship(idNum As String, memNumber As String) As String
    Dim result As String
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    result = ""
    SQL = "SELECT ISNULL(Relationship, '') [Relationship] FROM MEDICARE..tbMedRelationship WHERE IDNum = '" & idNum & "' AND MemberNumber = '" & memNumber & "'"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result = IIf(!relationShip = "", "M", !relationShip)
        End If
        .Close
    End With
    Set rec = Nothing
    GetRelationship = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetMemberTypeCode(Code As String) As String
    Dim result As String
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    result = ""
    SQL = "SELECT ISNULL(TypeCode, '') [TypeCode] FROM MEDICARE..tbMedType WHERE Code = '" & Code & "'"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result = ValidateNullValue(!TypeCode)
        End If
        .Close
    End With
    Set rec = Nothing
    GetMemberTypeCode = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetMemberTypeDescription(Code As String) As String
    Dim result As String
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    
    result = ""
    SQL = "SELECT ISNULL(Description, '') [Description] FROM MEDICARE..tbMedType WHERE TypeCode = '" & Code & "'"
On Error GoTo ErrHandler
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, MedsysUser.SQLConnection
        If Not .EOF Then
            result = ValidateNullValue(!Description)
        End If
        .Close
    End With
    Set rec = Nothing
    GetMemberTypeDescription = result
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function SavePatientInfo(patientInfo As PatientInformation, ByVal memNumber As String, ByRef patientIdNum As String) As Boolean
On Error GoTo ErrHandler
    Dim strtbMasterQuery As String
    If patientInfo.HospNumber = Empty Then
        patientInfo.HospNumber = GetPatientHospNum
        ' Insert into tbMaster
        strtbMasterQuery = "INSERT INTO PATIENT_DATA..tbmaster "
        strtbMasterQuery = strtbMasterQuery & " (HospNum, LastName, FirstName, MiddleName, BirthDate, Sex, Age, HouseStreet, Barangay, Zipcode, Own_PHICMemberNumber) "
        strtbMasterQuery = strtbMasterQuery & " VALUES "
        strtbMasterQuery = strtbMasterQuery & " ('" & patientInfo.HospNumber & "', '" & ReplaceApostrophe(patientInfo.info.LastName) & " " & patientInfo.info.Suffix & "', '" & _
                                                    ReplaceApostrophe(patientInfo.info.FirstName) & "', '" & ReplaceApostrophe(patientInfo.info.MiddleName) & "', '" & _
                                                    patientInfo.info.BirthDate & "', '" & patientInfo.info.Gender & "', '" & GetAge(patientInfo.info.BirthDate) & "', '" & _
                                                    patientInfo.info.Address.Street & "', '" & patientInfo.info.Address.Barangay & "', '" & patientInfo.info.Address.ZipCode & "', '" & _
                                                    patientInfo.info.PatientPin & "')"
        MedsysUser.SQLConnection.Execute strtbMasterQuery
    Else
        ' Update tbMaster
        strtbMasterQuery = "UPDATE PATIENT_DATA..tbmaster "
        strtbMasterQuery = strtbMasterQuery & "SET HouseStreet = '" & patientInfo.info.Address.Street & "', "
        strtbMasterQuery = strtbMasterQuery & "Barangay = '" & patientInfo.info.Address.Barangay & "', "
        strtbMasterQuery = strtbMasterQuery & "Zipcode = '" & patientInfo.info.Address.ZipCode & "', "
        strtbMasterQuery = strtbMasterQuery & "Own_PHICMemberNumber = '" & patientInfo.info.PatientPin & "' "
        strtbMasterQuery = strtbMasterQuery & "WHERE HospNum = '" & patientInfo.HospNumber & "' "
        MedsysUser.SQLConnection.Execute strtbMasterQuery
    End If
    
    Dim strtbPatientQuery As String
    If patientInfo.PatientType = "0" Then
        ' tbPatient
        patientIdNum = GetInPatientId
        strtbPatientQuery = "INSERT INTO PATIENT_DATA..tbpatient "
    Else
        ' tbOutPatient
        patientIdNum = GetOutPatientId
        strtbPatientQuery = "INSERT INTO PATIENT_DATA..tbOutPatient "
    End If
    strtbPatientQuery = strtbPatientQuery & " (HospNum, IdNum, AdmDate, DcrDate) "
    strtbPatientQuery = strtbPatientQuery & " VALUES "
    strtbPatientQuery = strtbPatientQuery & " ('" & patientInfo.HospNumber & "', '" & patientIdNum & "', '" & patientInfo.Confinement.AdmissionDate & "', '" & patientInfo.Confinement.DischargeDate & "') "
    MedsysUser.SQLConnection.Execute strtbPatientQuery

    ' tbPatient2
    Dim strtbPatient2Query As String
    strtbPatient2Query = "INSERT INTO PATIENT_DATA..tbPatient2 "
    strtbPatient2Query = strtbPatient2Query & " (IdNum) "
    strtbPatient2Query = strtbPatient2Query & " VALUES "
    strtbPatient2Query = strtbPatient2Query & " ('" & patientIdNum & "') "
    MedsysUser.SQLConnection.Execute strtbPatient2Query

    ' tbpatientInformant
    Dim strtbPatientInformant As String
    strtbPatientInformant = "INSERT INTO PATIENT_DATA..tbpatientInformant "
    strtbPatientInformant = strtbPatientInformant & " (IdNum) "
    strtbPatientInformant = strtbPatientInformant & " VALUES "
    strtbPatientInformant = strtbPatientInformant & " ('" & patientIdNum & "') "
    MedsysUser.SQLConnection.Execute strtbPatientInformant

    ' tbpatientGuarantor
    Dim strtbPatientGuarantor As String
    strtbPatientGuarantor = "INSERT INTO PATIENT_DATA..tbpatientGuarantor "
    strtbPatientGuarantor = strtbPatientGuarantor & " (IdNum) "
    strtbPatientGuarantor = strtbPatientGuarantor & " VALUES "
    strtbPatientGuarantor = strtbPatientGuarantor & " ('" & patientIdNum & "') "
    MedsysUser.SQLConnection.Execute strtbPatientGuarantor

    If patientInfo.PatientType = "0" Then
        'tbpatientHistory
        Dim strtbPatientHistory As String
        strtbPatientHistory = "INSERT INTO PATIENT_DATA..tbpatientHistory "
        strtbPatientHistory = strtbPatientHistory & " (IdNum) "
        strtbPatientHistory = strtbPatientHistory & " VALUES "
        strtbPatientHistory = strtbPatientHistory & " ('" & patientIdNum & "') "
        MedsysUser.SQLConnection.Execute strtbPatientHistory
    End If
    
    SavePatientInfo = True
    Exit Function
ErrHandler:
    SavePatientInfo = False
    MsgBox Err.Description, vbOKOnly + vbCritical, "WARNING"
End Function

Public Function SaveMemberInformation(member As MemberInformation) As Boolean
SaveMemberInformation = False
On Error GoTo ErrHandler
    If member.Pin <> Empty Then
'        Dim LastName As String
'        LastName = ReplaceApostrophe(member.info.LastName)
'        If member.info.Suffix <> Empty Then
'            LastName = LastName & " " & member.info.Suffix
'        End If
        
        Dim strFirstName As String
        strFirstName = ReplaceApostrophe(member.info.FirstName)
        If member.info.Suffix <> Empty Then
            strFirstName = strFirstName & " " & member.info.Suffix
        End If
        
        MedsysUser.SQLConnection.Execute "Medicare..Medic_SaveMemberInfo '" _
                                            & member.Pin & "','" _
                                            & member.MemberShipType & "','" _
                                            & ReplaceApostrophe(member.info.LastName) & "','" _
                                            & strFirstName & "','" _
                                            & ReplaceApostrophe(member.info.MiddleName) & "','" _
                                            & ReplaceApostrophe(member.info.Address.Street) & "','" _
                                            & ReplaceApostrophe(member.info.Address.Barangay) & "','" _
                                            & member.info.Address.ZipCode & "','" _
                                            & member.Employer.PEN & "','" _
                                            & member.info.BirthDate & "','" _
                                            & ReplaceApostrophe(member.Employer.Name) & "','" _
                                            & ReplaceApostrophe(member.Employer.Address) & "','','" _
                                            & member.info.Gender & "','" _
                                            & member.info.Address.Region & "', '" _
                                            & MedsysUser.EmployeeCode & "'"
        
        SaveMemberInformation = True
    End If
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function SaveRelationShip(idNum As String, memNumber As String, relationShip As String) As Boolean
If relationShip = "M" Then
    relationShip = ""
End If
SaveRelationShip = False
On Error GoTo ErrHandler
    MedsysUser.SQLConnection.Execute "Medicare..Medic_SaveMemberRelationship '" _
                                        & idNum & "','" _
                                        & memNumber & "','" _
                                        & relationShip & "'"
    
    SaveRelationShip = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function SaveFinalCallData(pIdNum As String, pPatPin As String, pPatLastName As String, pPatFirstName As String, _
                                    pPatMiddleName As String, pPatNoMiddleName As Boolean, pPatSex As String, pPatDateOfBirth As String, _
                                    pPatientIs As String, pMemPin As String, pMemLastName As String, pMemFirstName As String, _
                                    pMemMiddleName As String, pMemNoMiddleName As Boolean, pMemSex As String, pMemDateOfBirth As String, _
                                    pMemStreet As String, pMemBarangay As String, pMemZipCode As String, pMemType As String, _
                                    pEmpPEN As String, pEmpName As String, pEmpAddress As String, pResponse As String) As Boolean
SaveFinalCallData = False
On Error GoTo ErrHandler
    Dim query As String
    query = query & "Medicare..Medic_SaveFinalCallData " _
                    & "'" & pIdNum & "', '" & pPatPin & "', '" & pPatLastName & "', '" & pPatFirstName & "', " _
                    & "'" & pPatMiddleName & "', " & pPatNoMiddleName & ", '" & pPatSex & "', '" & pPatDateOfBirth & "', " _
                    & "'" & pPatientIs & "', '" & pMemPin & "', '" & pMemLastName & "', '" & pMemFirstName & "', " _
                    & "'" & pMemMiddleName & "', " & pMemNoMiddleName & ", '" & pMemSex & "', '" & pMemDateOfBirth & "', " _
                    & "'" & pMemStreet & "', '" & pMemBarangay & "', '" & pMemZipCode & "', '" & pMemType & "', " _
                    & "'" & pEmpPEN & "', '" & pEmpName & "', '" & pEmpAddress & "', '" & pResponse & "'"
                                        
    MedsysUser.SQLConnection.Execute query
    SaveFinalCallData = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function SaveMemberEligibility(idNum As String, memNumber As String, isEligible As String, trackingNumber As String, callType As String, _
                                        response As String, hasPatientSuffix As Boolean, hasMemberSuffix As Boolean, _
                                        patientMNameUseNA As Integer, memberMNameUseNA As Integer, Optional PPCode As String = Empty) As Boolean
SaveMemberEligibility = False
On Error GoTo ErrHandler
    Dim query As String
    query = query & "Medicare..Medic_SaveMemberEligibility '" _
                                        & idNum & "','" _
                                        & memNumber & "','" _
                                        & isEligible & "','" _
                                        & trackingNumber & "','" _
                                        & callType & "','" _
                                        & MedsysUser.EmployeeCode & "','" _
                                        & DateTime.Now & "','" _
                                        & ReplaceApostrophe(response) & "','" _
                                        & hasPatientSuffix & "','" _
                                        & hasMemberSuffix & "'," _
                                        & patientMNameUseNA & "," _
                                        & memberMNameUseNA & ""
                                        
    ' 52244242-D629-41F8-A42D-0A95E33CDFFC - SHOW PATIENT BASED ON PROBLEM ID
    If ProgSettingsGbl.IsProgramSettingExists("52244242-D629-41F8-A42D-0A95E33CDFFC") Then
        query = query & ", '" & PPCode & "'"
    End If
    
    MedsysUser.SQLConnection.Execute query
    SaveMemberEligibility = True
    Exit Function
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Function

Public Function GetServiceKey() As PassKey
    Dim result As PassKey
    With result
        .HospitalCode = EcSettings.HospCode
        .UserName = EcSettings.SoapUserId
        .UserPassword = EcSettings.SoapUserPw
    End With
    
    GetServiceKey = result
End Function

Public Function GetHospitalCode() As String
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    SQL = "Select top 1 isnull(HospitalCode,'') as HospitalCode from Medicare..tbmedhospital"
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, MedsysUser.SQLConnection, adOpenDynamic, adLockReadOnly
        If .RecordCount > 0 Then
            GetHospitalCode = !HospitalCode & ""
        End If
        .Close
    End With
End Function

Public Sub UpdateEClaimsSettings(settings As EClaimsSettings)
    Dim query As String
On Error GoTo ErrHandler
    query = "UPDATE MEDICARE..tbMedEClaimsSettings "
    query = query & "SET HospitalEmail = '" & settings.HospEmail & "', "
    query = query & " HospitalName = '" & settings.HospitalName & "', "
    query = query & " AccreditationNumber = '" & settings.AccreditationNumber & "', "
    query = query & " HospitalCode = '" & settings.HospCode & "', "
    query = query & " SoapUserId = '" & settings.SoapUserId & "', "
    query = query & " SoapUserPw = '" & settings.SoapUserPw & "', "
    query = query & " PdfCloudStorageUri = '" & settings.PdfCloudStorageUri & "', "
    query = query & " PdfCloudStorageUserId = '" & settings.PdfCloudStorageUserId & "', "
    query = query & " PdfCloudStorageUserPw = '" & settings.PdfCloudStorageUserPw & "', "
    query = query & " UserCategory = '" & settings.UserCategory & "', "
    query = query & " XmlEncryptDecryptPw = '" & settings.XmlEncryptDecryptPw & "', "
    query = query & " IsEncryptXmlPayLoadData = " & IIf(settings.IsEncryptXmlPayLoadData, 1, 0) & " "
    query = query & " WHERE UserCategory = '" & settings.UserCategory & "' "
    
    MedsysUser.SQLConnection.Execute query
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbOKOnly + vbCritical
End Sub

Public Function GetEClaimsSettings(uCategory As String) As EClaimsSettings
    Dim result As EClaimsSettings
    
    Dim SQL As String
    Dim rec As New ADODB.Recordset
    SQL = "SELECT HospitalEmail, HospitalCode, SoapUserId, SoapUserPw, PdfCloudStorageUri, PdfCloudStorageUserId, PdfCloudStorageUserPw, " _
            & " XmlEncryptDecryptPw, UserCategory, AccreditationNumber, CertificateId, Isnull(IsDemoMode, 0) [IsDemoMode], isnull(IsEncryptXmlPayLoadData,0) [IsEncryptXmlPayLoadData], HospitalName, " _
            & " CloudDescription, ClaimStatusCountStorageUri, AppKey, AppServerUrl, AppServerId, AppServerPw "
    SQL = SQL & " FROM Medicare..tbMedEClaimsSettings "
    SQL = SQL & " WHERE UserCategory = '" & uCategory & "' "
    
    With rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, MedsysUser.SQLConnection, adOpenDynamic, adLockReadOnly
        If .RecordCount > 0 Then
            result.HospEmail = ValidateNullValue(!HospitalEmail, False)
            result.HospCode = ValidateNullValue(!HospitalCode, False)
            result.SoapUserId = ValidateNullValue(!SoapUserId, False)
            result.SoapUserPw = ValidateNullValue(!SoapUserPw, False)
            result.PdfCloudStorageUri = ValidateNullValue(!PdfCloudStorageUri, False)
            result.PdfCloudStorageUserId = ValidateNullValue(!PdfCloudStorageUserId, False)
            result.PdfCloudStorageUserPw = ValidateNullValue(!PdfCloudStorageUserPw, False)
            result.XmlEncryptDecryptPw = ValidateNullValue(!XmlEncryptDecryptPw, False)
            result.UserCategory = ValidateNullValue(!UserCategory, False)
            result.AccreditationNumber = ValidateNullValue(!AccreditationNumber, False)
            result.CertifiateId = ValidateNullValue(!CertificateId, False)
            result.IsDemoMode = !IsDemoMode
            result.IsEncryptXmlPayLoadData = !IsEncryptXmlPayLoadData
            result.HospitalName = ValidateNullValue(!HospitalName, True)
            result.CloudDescription = ValidateNullValue(!CloudDescription, False)
            result.ClaimStatusCountStorageUri = ValidateNullValue(!ClaimStatusCountStorageUri, False)
            result.AppKey = Crypt("D", "kcci@medsys", ValidateNullValue(!AppKey, False))
            result.AppServerUrl = ValidateNullValue(!AppServerUrl, False)
            result.AppServerId = ValidateNullValue(!AppServerId, False)
            result.AppServerPw = ValidateNullValue(!AppServerPw, False)
        End If
        .Close
    End With
    
    GetEClaimsSettings = result
End Function