Attribute VB_Name = "mdlMembers"
Option Explicit
Const YELLOW = &H80000018
Const WHITE = &H80000005
Const BLACK = &H80000008

Public conMember As New ADODB.Connection
Public Rec As New ADODB.Recordset
Public recMember As New ADODB.Recordset
Public RecNew As New ADODB.Recordset


'Public Sub Emphasize(msflexVar As MSHFlexGrid, lHigh As Boolean)
'   If msflexVar.Rows > 0 Then
'      If lHigh Then
'         msflexVar.CellBackColor = YELLOW
'         msflexVar.CellForeColor = BLACK
'      Else
'         msflexVar.CellBackColor = WHITE
'         msflexVar.CellForeColor = BLACK
'      End If
'   End If
'End Sub

Public Function GetMemberType(strIdnum As String) As Integer
    Dim SQL As String
    
    SQL = "Medicare..Medic_GetMemberType '" & strIdnum & "'"
    
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetMemberType = CInt(!MemberType)
        Else
            GetMemberType = 1
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
    GetMemberType = IIf(GetMemberType = 0, 1, GetMemberType)
End Function

Public Function CheckEntryForm1(strIdnum As String) As Boolean
    Dim SQL As String
    
    SQL = "Select * from tbQualityObjective  "
    SQL = SQL + "where IDNum = '" & strIdnum & "' and (Form1User is not null or "
    SQL = SQL + "Form1Date is not null)"
    
    CheckEntryForm1 = False
    
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            CheckEntryForm1 = True
        End If
        .Close
    End With
    Set recMember = Nothing
End Function

Public Function Get_DcrDate(strIdnum As String) As String
    Dim SQL As String

    SQL = "Select AdmDate, isnull(DcrDate,'') DcrDate from Patient_Data..tbPatient where IDNum = '" & strIdnum & "'"

    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly

        If .RecordCount > 0 Then
            Get_DcrDate = IIf(ValidateDate(!DCRDate, !AdmDate), !DCRDate & "", "")
        End If
        .Close
    End With
    Set recMember = Nothing
End Function

Public Function ValidateDate(ByVal strValue As Date, ByVal strAdmDate As Date) As Boolean
   ValidateDate = False
    If IsDate(strValue) Then
        If strValue > strAdmDate Then
            ValidateDate = True
        Else
            ValidateDate = False
        End If
    Else
        ValidateDate = False
    End If
End Function

Public Function GetServerDate() As String
    Dim SQL As String
    Dim recMember As New ADODB.Recordset
    SQL = "Select getdate() as ServerDate"
    
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            ServerDate = Format(!ServerDate, "mm/dd/yyyy hh:mm:ss")
        End If
        .Close
    End With
    Set recMember = Nothing
End Function

Public Function GetClientName() As String
    Dim SQL As String
     Dim Rec As New ADODB.Recordset
    SQL = "Select top 1 isnull(ClientName,'') as ClientName from Patient_Data..tbHospitalInfo"
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        If .RecordCount > 0 Then
            ClientName = !ClientName & ""
        End If
        .Close
    End With
End Function

Public Function isPaidtoBill(strIdnum As String) As Boolean
    Dim SQL As String
    isPaidtoBill = False
    
    SQL = "Select isnull(isPTB, '') as PTB  from Medicare..tbMedActual where IDNum = '" & strIdnum & "'"
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        If .RecordCount > 0 Then
            If !PTB = "N" Then
             strOptionType = "N"
             isPaidtoBill = False
            ElseIf !PTB = "" Then
             isPaidtoBill = True
            Else
             strOptionType = "Y"
             isPaidtoBill = False
            End If
        Else
            isPaidtoBill = True
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function GetDcrDate(strIdnum As String) As String
    Dim SQL As String
    If CheckIfExistAdmDate(strIdnum) Then
        SQL = "Select max(DateDischarges) Discharge from Medicare..tbMedicOutDischarges where IDNum = '" & strIdnum & "'"
        
        With Rec
            If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
            
            If .RecordCount > 0 Then
                GetDcrDate = !Discharge
            End If
            .Close
        End With
        Set Rec = Nothing
    End If
End Function

Public Function GetAdmDate(strIdnum As String) As String
    Dim SQL As String
    If CheckIfExistAdmDate(strIdnum) Then
        SQL = "Select min(isnull(DateDischarges,getdate())) Admitted from Medicare..tbMedicOutDischarges where IDNum = '" & strIdnum & "'"
        
        With Rec
            If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
            
            If .RecordCount > 0 Then
                GetAdmDate = !Admitted
            End If
            .Close
        End With
        Set Rec = Nothing
    End If
End Function

Public Function GetDoctorPF(strDocID As String, DocType As String) As String
    Dim SQL As String
    
    SQL = "select isnull(MedicarePF,0) MedicarPF from Medicare..tbMedDoctors where IDnum = '" & strIdnum & "' and DocCode = '" & strDocID & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            If DocType = "P" Then
            PhysicianPF = Val(!MedicarPF)
            ElseIf DocType = "S" Then
            SurgeonPF = Val(!MedicarPF)
            ElseIf DocType = "A" Then
            AnesPF = Val(!MedicarPF)
            End If
        End If
        .Close
    End With
    DoctorPF = PhysicianPF + SurgeonPF + AnesPF
    
    Set Rec = Nothing
End Function

Public Function CheckIfExistAdmDate(strIdnum As String) As Boolean
    Dim SQL As String
    
    SQL = "Select * from Medicare..tbMedicOutDischarges where IDNum = '" & strIdnum & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            CheckIfExistAdmDate = True
        Else
            CheckIfExistAdmDate = False
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function GetDocSpecialization(DocCode As String) As String
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    SQL = "Select Specialization from Build_File..tbcoDoctor D left Outer Join Build_File..tbcoSpecialization S " & _
          " on D.SpecializationID = S.SpecializationID where DoctorID = '" & DocCode & "'"
    
    GetDocSpecialization = ""
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetDocSpecialization = !Specialization
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function GetAuthorizedBeds() As String
    Dim SQL As String
    
        SQL = "Select top 1 isnull(AuthorizedBeds,'300') AuthorizedBeds from Medicare..tbMedHospital"
        
        With Rec
            If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
            
            If .RecordCount > 0 Then
                GetAuthorizedBeds = !AuthorizedBeds
            End If
            .Close
        End With
        Set Rec = Nothing
End Function

Public Function GetOperationDate(strIdnum As String, strDocCode As String) As String
    Dim SQL As String
    
        SQL = "Select OperationDate from Station..tbNurseDischargeNotice where IDNum = '" & strIdnum & "'"
        
        With Rec
            If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
            
            If .RecordCount > 0 Then
                If IsNull(!OperationDate) Then
                    GetOperationDate = ""
                Else
                    GetOperationDate = Format(!OperationDate, "MM/DD/YYYY")
                End If
            End If
            .Close
        End With
        Set Rec = Nothing
End Function

Public Function GetActualUserID(strIdnum As String) As String
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    GetActualUserID = ""
    
    SQL = "Select UserID from Medicare..tbMedActual where IDNum  = '" & strIdnum & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetActualUserID = !UserID & ""
        End If
        .Close
    End With
    ActualUserID = GetActualUserID
    
    Set Rec = Nothing
End Function

Public Function CheckConfinementinOtherHospital(strIdnum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    SQL = "Medic_ConfinementinOtherHospital '" & strIdnum & "'"
    CheckConfinementinOtherHospital = False
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If Not .EOF Then
            CheckConfinementinOtherHospital = True
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function CheckHemoChemoDischarges(strIdnum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    SQL = "Select * from Medicare..tbMedicOutDischarges where IDNum = '" & strIdnum & "'"
    CheckHemoChemoDischarges = False
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            CheckHemoChemoDischarges = True
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function CheckDischargeDate(strIdnum As String) As String
Dim recSQL As New ADODB.Recordset
Dim strSQL As String

pubDcrDate = ""

strSQL = "Select DcrDate From Patient_Data..tbPatient Where IDNum = '" & strIdnum & "'"

With recSQL
    If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open strSQL, user.SQLConnection, adOpenDynamic, adLockOptimistic
        If .RecordCount > 0 Then
            If Not IsNull(!DCRDate) Then
                CheckDischargeDate = !DCRDate
            End If
        End If
    If .State > 0 Then .Close
End With
pubDcrDate = CheckDischargeDate
End Function

Public Function GetRUVAmount(strRUV As String, strCaseType As String) As String
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
  
  SQL = "Select RUVRate [RUVRate] From Medicare..tbMedORRates Where Type = '" & strCaseType & "' and '" & _
         strRUV & "' between FromRUV and ToRUV"
         
  With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetRUVAmount = ![RUVRate] & ""
        End If
        .Close
  End With
  Set Rec = Nothing
End Function

Public Function GetNewRUVAmount(strRUV As String, strCaseType As String) As String
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
  
  SQL = "Select RUVRate [RUVRate] From Medicare..tbMedNewORRates Where Type = '" & strCaseType & "' and '" & _
         strRUV & "' between FromRUV and ToRUV"
         
  With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetNewRUVAmount = ![RUVRate] & ""
        End If
        .Close
  End With
  Set Rec = Nothing
End Function

Public Sub GetAdministrator()
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
  
    SQL = "Medicare..Medic_ValidateHospitalInfo"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            pstrAdministrator = !Administrator
            pstrAdminTitle = !AdminTitle
            blnCashMeds = !isAllowCashMeds
            blnWithOperationDate = !isAllowOperationDate
            blnAllowUpdateSlashingCharges = !isAllowUpdateSlashingCharges
            blnAllowDistributeSlashing = !isAllowDistributeSlashing
            blnAllowPrintOutDiagnosis = !isAllowPrintOutDiagnosis
            intAllowedPrintOutDiagnosis = !AllowedPrintOutDiagnosis
            blnAllowTentativeTransmittal = !isAllowTentativeTransmittal
            blnAllowShowElapsedClaims = !isAllowShowElapsedClaims
            blnAllowShowRTHClaims = !isAllowShowRTHClaims
            blnAllowMaximize = !isAllowMaximize
            blnAllowPrintPartV = !isAllowPrintPartV
            blnAllowUpdateDoctorPF = !isAllowUpdateDoctorPF
            blnAllowLateSubmission = !isAllowLateSubmission
            blnAllowTransmittalRefiled = !isAllowTransmittalRefiled
            PubSlashingForm = !SlashingForm & ""
            blnAllowSlashing = !IsAllowSlashing
            blnAllowSignatoryUserAccount = !isAllowSignatoryUserAccount
            blnAllowPatientAutomaticCompute = !isAllowPatientAutomaticCompute
            blnAllowEntryAutomaticCompute = !isAllowEntryAutomaticCompute
            blnAllowBuildFileNew = !isAllowBuildFileNew
            blnAllowIndexNumber = !IsAllowIndexNumber
            blnAllowDirectAutomaticCompute = !isAllowDirectAutomaticCompute
            blnAllowDeleteTransmittal = !IsAllowDeleteTransmittal
            blnAllowShowPerformedDate = !IsAllowShowPerformedDate
            blnAllowLockedFormsPrinting = !IsAllowLockedFormsPrinting
            PubFormsImplementationDate = !FormsImplementationDate
            blnAllowSeparatePart45 = !IsAllowSeparatePart45
            blnAllowEditPatientDays = !IsAllowEditPatientDays
            blnAllowPart23Signatory = !IsAllowPart23Signatory
            pstrPHICAccountNum = !AccountNum
'            PubBenefitsImplementationDate = !BenefitsImplementationDate
            blnPart34onEntry = !isPart34onEntry
            PubisPBEFImplementationDate = !isPBEFImplementationDate
            blnEnablePBEF = !EnablePBEF
            blnisAllowCaseRateTerms = !isAllowCaseRateTerms
            PubAllowCaseRateTermsImplementationDate = !AllowCaseRateTermsImplementationDate
            blnisAllowFieldsCF2 = !isAllowFieldsCF2
            blnEnablePFPHICValidCF1 = !EnablePFPHICValidCF1
            blnisValidate90daysCaserate = !isValidate90daysCaserate
            blnAllowShowAdmittedPHICPx = !isAllowShowAdmittedPHICPx
            blnAllowShowAdmittedPHICPxImplementationDate = !isAllowShowAdmittedPHICPx_ImplementationDate
            blnPBEFTransmittal = !PBEFTransmittal
            blnPBEFTransmittalImplementationDate = !PBEFTransmittalImplementationDate
            blnAutoPFSharing = !AutoComputePFSharing
            blnCF2Preprint = !EnablePrePrintCF2
            bln90daysConfinement = !EnablePrompt90DaysCF1
            blnisTransmittalCaseType = !isTransmittalCaseType
            blnisallowSplitCaserate = !isallowSplitCaserate
          '  pstrPHICAccountNum = !AccountNum
        End If
        .Close
    End With
    Set Rec = Nothing
End Sub

Public Function CheckRelationship(strIdnum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    CheckRelationship = False
    
    SQL = "Select isnull(Relationship,'') Relationship from Medicare..tbMedRelationship where IDNum = '" & Trim$(strIdnum) & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If Not .EOF Then
            pubMemberRelationship = !Relationship
            CheckRelationship = True
        End If
        .Close
    End With
    Set Rec = Nothing
    
End Function

Public Function GetPackage(strIdnum As String) As String
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
    
    GetPackage = ""
    If IsNumeric(strIdnum) = True Then
        SQL = "Select isnull(Patient_data.dbo.fn_GetPackage(PackageID),'') as Package from Patient_Data..tbPatient2 where IDNum = '" & Trim$(strIdnum) & "'"
    Else
        SQL = "Select isnull(Patient_data.dbo.fn_GetPackage(PackageID),'') as Package from Patient_Data..tbOutPatient where IDNum = '" & Trim$(strIdnum) & "'"
    End If
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetPackage = !Package
        End If
        .Close
    End With
    Set Rec = Nothing
    
    PublicPackage = GetPackage
    isPublicPackage = IIf(GetPackage = "", False, True)
End Function

Public Function IsCashMeds(strIdnum As String) As Boolean
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
    
    IsCashMeds = False
    
    SQL = "Select isnull(isCashMeds,0) isCashMeds from Medicare..tbMedPatient where IDNum = '" & Trim$(strIdnum) & "'"
        
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            IsCashMeds = !IsCashMeds
        End If
        .Close
    End With
    Set Rec = Nothing
   
End Function

Public Function CheckORNumber(strORNumber As String) As Boolean
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
    
    CheckORNumber = False
    
    If Left(Trim$(strORNumber), 2) <> "OR" Then
        strORNumber = "OR" + strORNumber
    End If
    
    SQL = "Select * from Billing..tbCashORMaster where RefNum = '" & Trim$(strORNumber) & "' And RevenueID = 'CP' " & _
          "And IDNum In ('PHIC', 'PHGS', 'PHOW', 'PHIC-SSS', 'PHIC-GSI', 'PHIC-OWW','" & pstrPHICAccountNum & "')"
        
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            CheckORNumber = True
            PublicORAmount = Format(!Amount, "########0.00")
        End If
        .Close
    End With
    Set Rec = Nothing
   
End Function

Public Function GetORAmount(strORNumber As String, strIdnum As String) As Double
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
       
    If strIdnum = "" Then
        SQL = "Select isnull(sum(Amount),0) Amount from Medicare..tbMedPayment where ORNumber = '" & Trim$(strORNumber) & "'"
    Else
        SQL = "Select isnull((Amount),0) Amount from Medicare..tbMedPayment where ORNumber = '" & Trim$(strORNumber) & "' and not IDNum = '" & strIdnum & "'"
    End If
    GetORAmount = 0
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetORAmount = !Amount
        End If
        .Close
    End With
    Set Rec = Nothing
   
End Function

Public Function GetAdmDiagnosis(strIdnum As String) As String
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    SQL = "Select isnull(AdmDiagnosis,'') Diagnosis from Patient_Data..tbPatientHistory where IDNum = '" & Trim$(strIdnum) & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If Not .EOF Then
            GetAdmDiagnosis = !Diagnosis
        End If
        .Close
    End With
    Set Rec = Nothing
    
End Function

Public Function GetPatientName(strIdnum As String) As String
    Dim SQL As String
    SQL = "Select Medicare.dbo.funcGetFullName('" & strIdnum & "') as PatientName"
        
        With Rec
            If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
            
            If .RecordCount > 0 Then
                GetPatientName = !PatientName
            End If
            .Close
        End With
        Set Rec = Nothing
End Function

Public Function CheckisDistribute(strIdnum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    CheckisDistribute = False
    SQL = "Select top 1 isnull(isDistribute,0) isDistribute from Medicare..Medic_Compensable where isCompensable = 0 and Position in ('2','3') and IDNum = '" & Trim$(strIdnum) & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If Not .EOF Then
            If !isDistribute = "True" Then
                CheckisDistribute = True
            End If
        End If
        .Close
    End With
    Set Rec = Nothing
    
End Function

Public Function isHMO(strAccountNum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    SQL = "Patient_Data..SP_AOPD_CheckCompanyAccount '" & Trim$(strAccountNum) & "'"
    isHMO = False
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            isHMO = True
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function CheckIfWithTransDate(strIdnum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    CheckIfWithTransDate = False
    SQL = "Select Case isnull(TransDate,'')  When '' then '0' Else '1' End TransDate from Medicare..tbMedPatient where IDNum = '" & Trim$(strIdnum) & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If Not .EOF Then
            If !TransDate = "1" Then
                CheckIfWithTransDate = True
            End If
        End If
        .Close
    End With
    Set Rec = Nothing
    
End Function

Public Function EmployeeInitial() As String
    Dim SQL As String
    Dim Rec As New ADODB.Recordset
    
    SQL = "Select isnull(Initial,'') Initial from Password..tbPasswordMaster where EmployeeID = '" & Trim$(user.EmployeeCode) & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            EmployeeInitial = !Initial & ""
        End If
        .Close
    End With
    Set Rec = Nothing
End Function



Public Function GetPatientAdmTime(strIdnum As String) As String
    Dim SQL As String
    
        SQL = "Select AdmDate from Patient_Data..tbPatient where IDNum = '" & strIdnum & "' " & _
              "UNION ALL " & _
              "Select AdmDate from Patient_Data..tbOutPatient where IDNum = '" & strIdnum & "'"

        With Rec
            If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
            
            If .RecordCount > 0 Then
                GetPatientAdmTime = Format(!AdmDate, "HH:MM:SS")
            End If
            .Close
        End With
        Set Rec = Nothing
End Function

Public Function GetAppliedDate(strAdmDate As String) As String
    Dim SQL As String
    
    SQL = "Select top 1 ApplieDDate From Medicare..tbMedNewRates Where AppliedDate <= '" & Trim$(strAdmDate) & "'" & _
          "Group By AppliedDate Order By AppliedDate Desc"
        
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            GetAppliedDate = Format(!AppliedDate & "", "MM/DD/YYYY")
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function HMOAmount(strIdnum As String, strPosition As String) As Double
    Dim SQL As String
    
    SQL = "Medicare..Medic_GetHMOAmount '" & Trim$(strIdnum) & "', '" & Trim$(strPosition) & "'"
    HMOAmount = 0
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            HMOAmount = !HMO
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function DiscountAmount(strIdnum As String, strPosition As String) As Double
    Dim SQL As String
    
    SQL = "Medicare..Medic_GetDiscountAmount '" & Trim$(strIdnum) & "', '" & Trim$(strPosition) & "'"
    DiscountAmount = 0
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            DiscountAmount = !Discount
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function HMOPFAmount(strIdnum As String, strDocID As String) As Double
    Dim SQL As String
    
    SQL = "Select IsNull(HMO,0) HMO From Billing..tbBillHMOChargesDetailed Where IDNUm = '" & Trim$(strIdnum) & _
          "' And ItemID = '" & Trim$(strDocID) & "' And RevenueID = 'MD'"
    HMOPFAmount = 0
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            HMOPFAmount = !HMO
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function DiscountPFAmount(strIdnum As String, strDocID As String) As Double
    Dim SQL As String
    
    SQL = "Select IsNull(Amount,0) Amount From Billing..tbBillDailyBill Where IDNUm = '" & Trim$(strIdnum) & _
          "' And ItemID = '" & Trim$(strDocID) & "' And RevenueID = 'FD' " & _
          "UNION ALL " & _
          "Select IsNull(Amount,0) Amount From Billing..tbBillOPDailyOut Where IDNUm = '" & Trim$(strIdnum) & _
          "' And ItemID = '" & Trim$(strDocID) & "' And RevenueID = 'FD' "
          
    DiscountPFAmount = 0
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            DiscountPFAmount = !Amount
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function PHICClaimGroup(strDocID As String) As String
    Dim SQL As String
    
    PHICClaimGroup = ""
    SQL = "Select Isnull(PHICClaimGroup,'') PHICClaimGroup From Build_File..tbCoDoctor Where DoctorID = '" & Trim$(strDocID) & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            PHICClaimGroup = !PHICClaimGroup & ""
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function PHICDoctorRates(strClaimGroup As String, strCaseType As String) As Boolean
    Dim SQL As String
    
    PubMaxDailyPF = 0
    PubDailyPF = 0
    PubSurgeonPFBelow500 = 0
    PubSurgeonPFUp500 = 0
    PubAnesPFBelow500 = 0
    PubAnesPFUp500 = 0
    PHICDoctorRates = False
    
    SQL = "Select * From Medicare..tbMedDoctorRates Where GroupCode = '" & Trim$(strClaimGroup) & "' And CaseType = '" & Trim$(strCaseType) & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            PubMaxDailyPF = !MaxDailyVisit
            PubDailyPF = !DailyVisit
            PubSurgeonPFBelow500 = !SurgeonBelow500
            PubSurgeonPFUp500 = !SurgeonUp500
            PubAnesPFBelow500 = !AnesBelow500
            PubAnesPFUp500 = !AnesUp500
            PHICDoctorRates = True
        End If
        .Close
    End With
    Set Rec = Nothing
End Function

Public Function GetPatientAdmDate(strIdnum As String) As String
    Dim SQL As String
    Dim Rec As New ADODB.Recordset

        SQL = "Select isnull(Convert(Varchar(10), AdmDate, 101),'09/01/2011') AdmDate from Patient_Data..tbPatient where IDNum = '" & strIdnum & _
        "' UNION ALL Select isnull(Convert(Varchar(10), AdmDate, 101),'09/01/2011') AdmDate from Patient_Data..tbOutPatient where IDNum = '" & strIdnum & "'"

     
        With Rec
            If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
            
            If .RecordCount > 0 Then
                GetPatientAdmDate = !AdmDate & ""
            End If
            .Close
        End With
        Set Rec = Nothing

End Function

Public Function GetIsBenefits(strIdnum As String) As Boolean
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
  Dim isPackage As String
  
  GetIsBenefits = False
  isPackage = "0"
  
  SQL = "Select Isnull(PackageID,1) PackageID From Medicare..tbMedActual where IDNum = '" & strIdnum & "'"
  
  With Rec
    If .State > 0 Then .Close
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Open SQL, user.SQLConnection
    
    If .RecordCount > 0 Then
        If !PackageID > 1 Then
            GetIsBenefits = True
            isPackage = "1"
        End If
    End If
    .Close
  End With
  Set Rec = Nothing
End Function


Public Function GetTrackingNumber(strIdnum As String) As String
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
  
  GetTrackingNumber = ""
  SQL = "Select Isnull(TrackingNumber,'') TrackingNumber From Medicare..tbMedPatient where IDNum = '" & strIdnum & "'"
  
  With Rec
    If .State > 0 Then .Close
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Open SQL, user.SQLConnection
    
    If .RecordCount > 0 Then
            GetTrackingNumber = !TrackingNumber & ""
    End If
    .Close
  End With
  Set Rec = Nothing
End Function






Public Function GetPackageType(strIdnum As String) As Integer
    Dim SQL As String
    
    SQL = "Select IsNull(PackageID,'1') PackageID From Medicare..tbMedActual Where IDNum = '" & strIdnum & "'"
    
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetPackageType = CInt(!PackageID)
        Else
            GetPackageType = 1
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
    GetPackageType = IIf(GetPackageType = 0, 1, GetPackageType)
End Function

Public Function GetForm2Printed(strIdnum As String) As Boolean
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
  
  GetForm2Printed = False
  SQL = "Select Isnull(isForm2Printed,0) isForm2Printed From Medicare..tbMedpatient where IDNum = '" & strIdnum & "'"
  
  With Rec
    If .State > 0 Then .Close
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Open SQL, user.SQLConnection
    
    If .RecordCount > 0 Then
        GetForm2Printed = !isForm2Printed
    End If
    .Close
  End With
  Set Rec = Nothing
End Function

Public Function GetFirstICDRVSAmount(strIdnum As String) As Long
    Dim SQL As String
    
'    SQL = "Select IsNull(PackageID,'1') PackageID From Medicare..tbMedActual Where IDNum = '" & strIdNum & "'"
    SQL = "Select IsNull(FirstICDRVSAmount,'1') FirstICDRVSAmount From Medicare..tbMedActual Where IDNum = '" & strIdnum & "'"
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            If !FirstICDRVSAmount = "" Then
                GetFirstICDRVSAmount = 0
            Else
                GetFirstICDRVSAmount = !FirstICDRVSAmount
            End If
        Else
            GetFirstICDRVSAmount = 0
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
End Function

Public Function GetSecondICDRVSAmount(strIdnum As String) As Long
    Dim SQL As String
    
'    SQL = "Select IsNull(PackageIDSecondary,'1') PackageIDSecondary From Medicare..tbMedActual Where IDNum = '" & strIdNum & "'"
SQL = "Select IsNull(SecondICDRVSAmount,'1') SecondICDRVSAmount From Medicare..tbMedActual Where IDNum = '" & strIdnum & "'"
    
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            If !SecondICDRVSAmount = "" Then
                GetSecondICDRVSAmount = 0
            Else
                GetSecondICDRVSAmount = !SecondICDRVSAmount
            End If
        Else
            GetSecondICDRVSAmount = 0
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
End Function