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

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


Public Function GetPatientAdmDate(strIdnum As String) As String
    Dim SQL As String
    
        If IsNumeric(strIdnum) = True Then
            SQL = "Select isnull(Convert(Varchar(10), AdmDate, 101),'') AdmDate from patient_data..tbpatient where IDNum = '" & strIdnum & "'"
        ElseIf Right(strIdnum, 1) = "B" Then
            SQL = "Select isnull(Convert(Varchar(10), AdmDate, 101),'') AdmDate from patient_data..tboutpatient where IDNum = '" & strIdnum & "'"
        ElseIf Right(strIdnum, 1) = "D" Then
            SQL = "Select isnull(Convert(Varchar(10), AdmDate, 101),'') AdmDate from medicare..tbmedpatient where IDNum = '" & strIdnum & "'"
        ElseIf Left(strIdnum, 1) = "P" Then
            SQL = "Select isnull(Convert(Varchar(10), AdmDate, 101),'') AdmDate from patient_data..tboutpatient where IDNum = '" & strIdnum & "'"
        ElseIf strIdnum = "NEW" Then
            SQL = "Select Getdate() as AdmDate from Medicare..tbMedHospital"
        End If
        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
'added by angelika 9/14/2015
Public Function CheckIfHemoPatient(strIdnum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    CheckIfHemoPatient = False
    SQL = "select hemonum from patient_data..tboutpatient where idnum = '" & strIdnum & "' and ishemodialysis = '1'"

    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
            If .RecordCount > 0 Then
                CheckIfHemoPatient = True
            End If
      
        .Close
    End With
    Set Rec = Nothing
    
End Function
'added by ange 6.1.2016
Public Function CheckIfExpiredPatient(strCcode As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    CheckIfExpiredPatient = False
    SQL = "Medicare..Medic_GetCaseRateWithoutRestriction '" & strCcode & "'"

    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
            If .RecordCount > 0 Then
                CheckIfExpiredPatient = True
            End If
      
        .Close
    End With
    Set Rec = Nothing
    
End Function
'added by ange 6.1.2016
Public Function CheckisHEMO_CHEMO(strIdnum As String) As Boolean
    Dim SQL As String
    
    SQL = "Medicare..Medic_CheckIfHemoChemo '" & strIdnum & "'"
    
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            CheckisHEMO_CHEMO = True
        Else
            CheckisHEMO_CHEMO = False
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
    
End Function
'added by ange 6.1.2016
Public Function GetCountConfinementHours(strIdnum As String) As Long 'Integer
    Dim SQL As String
    
    SQL = "Medicare..Medic_GetConfinementHours '" & strIdnum & "'"
    
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetCountConfinementHours = !hours
        Else
            GetCountConfinementHours = 0
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
    
End Function
'added by ange 6.1.2016
Public Function GetCaseRateHours(strCcode As String, intAge As Integer) As Integer
    Dim SQL As String
    
    SQL = "Medicare..Medic_CaseRateCondition '" & strCcode & "', '" & intAge & "'"
    
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetCaseRateHours = !Chours
        Else
            'set as 24 hours by default
            GetCaseRateHours = 24
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
    
End Function

Public Function GetMemberType(strIdnum As String) As Integer
    Dim SQL As String
    
    SQL = "Medicare..Medic_GetMemberType '" & Trim$(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
    
If IsNumeric(strIdnum) = True Then
    SQL = "Select isnull(AdmDate,'') AdmDate, isnull(DcrDate,'') DcrDate from Patient_Data..tbPatient where IDNum = '" & strIdnum & "'"
Else
    SQL = "Select isnull(AdmDate,'') AdmDate, isnull(DcrDate,'') DcrDate from Patient_Data..tboutPatient where IDNum = '" & strIdnum & "'"
End If
    With recMember
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            If !AdmDate <> "" Then
                Get_DcrDate = IIf(ValidateDate(!DCRDate, !AdmDate), !DCRDate & "", "")
            End If
        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 IsNumeric(strIdnum) = True Then
            If strValue > strAdmDate Then
                ValidateDate = True
            Else
                ValidateDate = False
            End If
        Else
            If strValue >= Format(strAdmDate, "mm/dd/yyyy") Then
                ValidateDate = True
            Else
                ValidateDate = False
            End If
        End If
    Else
        ValidateDate = False
    End If
End Function

Public Function GetServerDate() As String
    Dim SQL As String
    
    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 GetHospitalCode() As String
    Dim SQL As String
    SQL = "Select top 1 isnull(HospitalCode,'') as HospitalCode,ACRFormsImplementationDate,isPart34onEntry,isAllowNewTransmital,isAlloSeniorDiscountPhic,isAlloSeniorDiscountPhicOut,Isnull(HemodialysisDaysValidation,0)HemodialysisDaysValidation, isAllowPWDDiscountPhic, isAllowPWDDiscountPhicOut from Medicare..tbmedhospital"
'    SQL = "Select top 1 isnull(HospitalCode,'') as HospitalCode,ACRFormsImplementationDate,isPart34onEntry,'1' isAllowNewTransmital, '1' isAlloSeniorDiscountPhic, '1' isAlloSeniorDiscountPhicOut, '1' HemodialysisDaysValidation, '1' isAllowPWDDiscountPhic, '1' isAllowPWDDiscountPhicOut from Medicare..tbmedhospital"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        If .RecordCount > 0 Then
            pstrHospitalCode = !HospitalCode & ""
            PubACRFormsImplementationDate = !ACRFormsImplementationDate
            PubisPart34onEntry = !isPart34onEntry
            PubNewTransmital = !isAllowNewTransmital
            PubAllowSeniorDiscountPhic = !isAlloSeniorDiscountPhic
            PubAllowSeniorDiscountPhicOut = !isAlloSeniorDiscountPhicOut
            PubHemodialysisDaysValidation = !HemodialysisDaysValidation
            PubAllowPWDDiscountPhic = !isAllowPWDDiscountPhic
            PubAllowPWDDiscountPhicOut = !isAllowPWDDiscountPhicOut
        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 GetDocSpecialization(DocCode As String) As String
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    SQL = "Select isnull(PMCC,'') PMCC from Build_File..tbcoDoctor D where DoctorID = '" & DocCode & "'"
    
    GetDocSpecialization = ""
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetDocSpecialization = !PMCC & ""
        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 GetMSSD() As String
    Dim SQL As String
    
        SQL = "Select top 1 isnull(MSSD,'') MSSD from Medicare..tbMedHospital"
        
        With Rec
            If .State > 0 Then .Close
            .CursorLocation = adUseClient
            .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
            
            If .RecordCount > 0 Then
                GetMSSD = !MSSD
            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 = ""
    If Right(strIdnum, 1) = "D" Then
            SQL = "Select ClaimUserID as UserID from Medicare..tbMedPatient where IDNum  = '" & strIdnum & "'"
    Else
            SQL = "Select UserID from Medicare..tbMedActual where IDNum  = '" & strIdnum & "'"
    End If
    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 = ""

If IsNumeric(strIdnum) = True Then
    strSQL = "Select DcrDate From Patient_Data..tbPatient Where IDNum = '" & strIdnum & "'"
Else
    strSQL = "Select DcrDate From Patient_Data..tboutPatient Where IDNum = '" & strIdnum & "'"
End If
With RecSQL
    If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open strSQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        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
            blnisAllowNewSearchingCaseRate = !isAllowNewSearchingCaseRate
            blnisAllowNewSearchingCaseRate = 1
            blnisAllowCaseRateTerms = !isAllowCaseRateTerms
            PubAllowCaseRateTermsImplementationDate = !AllowCaseRateTermsImplementationDate
            blnisAllowFieldsCF2 = !isAllowFieldsCF2
            blnEnablePFPHICValidCF1 = !EnablePFPHICValidCF1
            blnisValidate90daysCaserate = !isValidate90daysCaserate
            blnisTransmittalCaseType = !isTransmittalCaseType
            blnisallowSplitCaserate = !isallowSplitCaserate
            blnisDistributeWholeCaserate = !isDistributeWholeCaserate
        End If
        .Close
    End With
    Set Rec = Nothing
End Sub

Public Function CheckRelationship(strIdnum As String, Optional strHospNum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    CheckRelationship = False
    
    SQL = "if exists(Select isnull(Relationship,'') Relationship from Medicare..tbMedRelationship where IDNum = '" & Trim$(strIdnum) & "') begin " & _
          "Select isnull(Relationship,'') Relationship from Medicare..tbMedRelationship where IDNum = '" & Trim$(strIdnum) & "' end else begin Select isnull(F.Relationship,'') " & _
          "Relationship from medicare..tbMedMember D left outer join Medicare..tbMedRelationship F on F.Membernumber = D.Membernumber " & _
          "left outer join Patient_Data..tbpatient A on A.IdNum = F.idnum " & _
          "left outer join Patient_Data..tbmaster G on G.hospnum = A.hospnum " & _
          "where A.HospNum = '" & Trim$(strHospNum) & "' end"
    
    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
    PublicORAmount = 0
    If Left(Trim$(strOrnumber), 2) <> "OR" Then
        If ClientName = "MJSH" Then
             strOrnumber = strOrnumber
        Else
            strOrnumber = "OR" + strOrnumber
        End If
    End If
    If ClientName = "NKTI" Then
        If Right(Trim$(strOrnumber), 1) <> "N" Then
            strOrnumber = strOrnumber + "N"
        End If
    End If
    If ClientName = "BIHMI" Or ClientName = "WCI" Then
        SQL = "Select * from billing..tbCashLumpSum where RefNum = '" & Trim$(strOrnumber) & "'"
    ElseIf ClientName = "PSH" Then
        SQL = "Select * from Billing..tbCashORMaster where RefNum = '" & Trim$(strOrnumber) & "' And RevenueID in ('MU','PM') " & _
              "And IDNum = 'CASH'"
    ElseIf ClientName = "UPHDMC" Then
        SQL = "Select * from Billing..tbCashORMaster where RefNum = '" & Trim$(strOrnumber) & "' And RevenueID = 'CP' " & _
              "And IDNum = 'P12'"
    ElseIf ClientName = "NKTI" Then
        SQL = "select Refnum, isnull(CheckAmount,0) Amount from billing..tbcashormaster where refnum = '" & Trim$(strOrnumber) & "'"
    Else
        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 & "')"
    End If
        
    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) - isnull(sum(Tax),0)) + (Select isnull(sum(Payment),0) From tbmeddoctors where ornumber = '" & _
             Trim$(strOrnumber) & "' ) [Amount] from Medicare..tbMedPayment where ORNumber = '" & Trim$(strOrnumber) & "')"
    Else
'        If ClientName = "MGH" Then
            SQL = "(Select (isnull(sum(Amount),0)  - isnull(sum(Tax),0)) + (Select isnull(sum(Payment),0) From tbmeddoctors where ornumber = '" & _
                 Trim$(strOrnumber) & "' and not IDNum = '" & Trim$(strIdnum) & "' ) [Amount] from Medicare..tbMedPayment where ORNumber = '" & _
                 Trim$(strOrnumber) & "' and not IDNum = '" & Trim$(strIdnum) & "')"
'        Else
'            SQL = "(Select (isnull(sum(Amount),0)) + (Select isnull(sum(Payment),0) From tbmeddoctors where ornumber = '" & _
'                 Trim$(strORNumber) & "' and not IDNum = '" & Trim$(strIdNum) & "' ) [Amount] from Medicare..tbMedPayment where ORNumber = '" & _
'                 Trim$(strORNumber) & "' and not IDNum = '" & Trim$(strIdNum) & "')"
'        End If
    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 GetHemoDiagnosis(strHospNum As String) As String
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    SQL = "Select isnull(Diagnosis,'') Diagnosis from Medicare..tbMed_HemoDiagnosis where Hospnum = '" & Trim$(strHospNum) & "'"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If Not .EOF Then
            GetHemoDiagnosis = !Diagnosis
        End If
        .Close
    End With
    Set Rec = Nothing
    
End Function

Public Function Check_IsHemoPatient(strIdnum As String) As Boolean
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    Check_IsHemoPatient = False
    SQL = "Select isnull(isHemoDialysis, 0) isHemoDialysis from Patient_Data..tbOutPatient where IDNum = '" & Trim$(strIdnum) & "'"

    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If Not .EOF Then
            If !IsHemodialysis = True Then
                Check_IsHemoPatient = True
            End If
            
        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 = adLockReadOnly
        .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 = adLockReadOnly
        .Open SQL, user.SQLConnection
        
        If .RecordCount > 0 Then
            EmployeeInitial = !Initial & ""
        End If
        .Close
    End With
    Set Rec = Nothing
End Function
Public Function GetDocExpiryDate(DocCode As String) As String
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    'edited by ange 4/7/2016
    'SQL = "Select isnull(PHICExpireDate,'') PHICExpireDate from Build_File..tbcoDoctor D where DoctorID = '" & DocCode & "'"
    SQL = "Select convert(varchar(10),PHICExpireDate, 101) PHICExpireDate from Build_File..tbcoDoctor D where DoctorID = '" & DocCode & "' and PhilHealthNum <> '' and (PHICExpiredate <> '  /  /    ' and PHICExpiredate is not null and PHICExpiredate <> 'NULL') "
    
    GetDocExpiryDate = ""
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetDocExpiryDate = !PHICExpireDate
        End If
        .Close
    End With
    Set Rec = Nothing
End Function
'added by ange 6.8.2016
Public Function GetDocValidStart(DocCode As String) As String
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    'edited by ange 4/7/2016
    'SQL = "Select isnull(PHICExpireDate,'') PHICExpireDate from Build_File..tbcoDoctor D where DoctorID = '" & DocCode & "'"
    SQL = "Select isnull(PHICValidFrom,'') PHICValidFrom from Build_File..tbcoDoctor D where DoctorID = '" & DocCode & "' and PhilHealthNum <> ''"
    
    GetDocValidStart = ""
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetDocValidStart = !PHICValidFrom
        End If
        .Close
    End With
    Set Rec = Nothing
End Function
Public Function GetDaysToPromptExpiry() As String
    Dim Rec As New ADODB.Recordset
    Dim SQL As String
    
    SQL = "select isnull(DaysToPromptPFExpiry,'30') [DaysToPromptPFExpiry] from tbMedHospital"
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open SQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        
        If .RecordCount > 0 Then
            GetDaysToPromptExpiry = !DaysToPromptPFExpiry
        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 = adLockReadOnly
        .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 = adLockReadOnly
        .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 = adLockReadOnly
        .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 = adLockReadOnly
        .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 = adLockReadOnly
        .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 = adLockReadOnly
        .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 = adLockReadOnly
        .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 GetPackageType(strIdnum As String) As String
    Dim SQL As String
    
    SQL = "Select IsNull(PackageID,'1') PackageID,isnull(FirstICDRVS,'') FirstICDRVS 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 = IIf((!FirstICDRVS) = "", 1, (!FirstICDRVS))
            
        Else
            GetPackageType = 1
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
    GetPackageType = 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 GetIsBenefits(strIdnum As String) As Boolean
  Dim Rec As New ADODB.Recordset
  Dim SQL 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 GetPackageName(strIdnum As String) As String
  Dim Rec As New ADODB.Recordset
  Dim SQL As String
  
  GetPackageName = ""

  SQL = "Select Isnull(b.Package,'NONE') Package From Medicare..tbMedActual a " & _
        "left outer join medicare..tbmedpackage b on a.packageid = b.packageid where a.IDNum = '" & strIdnum & "'"
  
  With Rec
    If .State > 0 Then .Close
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Open SQL, user.SQLConnection
    
    If .RecordCount > 0 Then
        GetPackageName = !Package
    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 GetSecondaryPackageType(strIdnum As String) As String
    Dim SQL As String
    
    SQL = "Select IsNull(SecondICDRVS,'1') SecondICDRVS 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 !SecondICDRVS = "" Then
                GetSecondaryPackageType = 1
            Else
                GetSecondaryPackageType = (!SecondICDRVS)
            End If
        Else
            GetSecondaryPackageType = 1
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
    GetSecondaryPackageType = GetSecondaryPackageType
End Function

Public Function GetFirstPackageType(strIdnum As String) As String
    Dim SQL As String
    
    SQL = "Select IsNull(FirstICDRVS,'1') FirstICDRVS 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 !FirstICDRVS = "" Then
                GetFirstPackageType = 1
            Else
                GetFirstPackageType = CStr(!FirstICDRVS) & ""
            End If
        Else
            GetFirstPackageType = 1
        End If
        .Close
        
    End With
    Set recMember = Nothing
    
    GetFirstPackageType = IIf(GetFirstPackageType = 0, 1, GetFirstPackageType)
End Function


'added by ange 8.12.2016 allow caserate as first and Second
Public Function isAllowSecondCase(strPackageID As String) As Boolean
Dim RecSQL As New ADODB.Recordset
Dim strSQL As String

'strSQL = "Select isnull(isAllowSecondCR, '') [isAllowSecondCR]  from Medicare..tbMedPackage where packageid = '" & strPackageID & "'" --Luzon Ver.
strSQL = "Select isnull(isAllowSecondCR, '') [isAllowSecondCR]  from Medicare..tbMedPackage_NEW where code = '" & strPackageID & "'" 'Mindanao Ver.

isAllowSecondCase = False
With RecSQL
    If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .Open strSQL, user.SQLConnection, adOpenDynamic, adLockReadOnly
        If .RecordCount > 0 Then
                isAllowSecondCase = !isAllowSecondCR
        End If
    If .State > 0 Then .Close
End With
End Function

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


Public Function DiscountAmt(strIdnum As String, intItem As Integer) As Double
    Dim SQL As String
    
    SQL = "Select Medicare.dbo.fn_DiscountAmount('" & Trim$(strIdnum) & "', " & Trim$(intItem) & ") as Discount"
    DiscountAmt = 0
    
    With Rec
        If .State > 0 Then .Close
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Open SQL, user.SQLConnection
        
        If Not .EOF Then
            DiscountAmt = !Discount
        End If
        .Close
    End With
    Set Rec = Nothing
End Function