Attribute VB_Name = "Mod_Verificacion_Pagos"
Public Function verifica_deuda_apoderado(rut As String, agno As Integer, saldo As Long) As Boolean
    Dim objRS As ADODB.Recordset
    Dim rec As ADODB.Recordset
    Dim var_sql As String
    Set objRS = New ADODB.Recordset
    Set rec = New ADODB.Recordset
    
    verifica_deuda_apoderado = True
    
    'Verifico que todos los alumnos del apoderado no tengan deuda
    
    var_sql = "select distinct axa.ci_alu, apo.rut_apo from" _
            & " (select * from apo_x_alu" _
            & " where ci_alu = '" & rut & "' and agno = " & agno & ")  as apo,apo_x_alu axa" _
            & " Where apo.Rut_apo = axa.Rut_apo"
    objRS.Open var_sql, conn
    
    'Verifico que el apoderado no este en la lista negra
    If objRS.RecordCount > 0 Then
        var_sql = "SELECT * FROM fnza_lista_negra_apoderados" _
                & " where rut_apo = '" & objRS("rut_apo") & "'"
        rec.Open var_sql, conn
    Else
        MsgBox "El alumno no esta postulando para el aņo " & agno, vbInformation + vbOKOnly, "ALERTA"
        verifica_deuda_apoderado = False
        Exit Function
    End If
    If Not rec.EOF Then
        'MsgBox "Apoderado esta en la lista negra", vbInformation
        verifica_deuda_apoderado = False
        Exit Function
    End If
    rec.Close
    
    While Not objRS.EOF
        If verifica_pagos(objRS("ci_alu"), saldo) Then
            verifica_deuda_apoderado = True
        Else
            verifica_deuda_apoderado = False
            Exit Function
        End If
        objRS.MoveNext
    Wend
    objRS.Close
End Function

Public Function verifica_pagos(rut As String, saldo As Long) As Boolean
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql As String
    Dim sw As Boolean
    
    'Verifico que el alumno no este en la lista negra
    var_sql = "SELECT * FROM fnza_lista_negra_alumnos" _
            & " where rut_alu = '" & rut & "'"
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        'MsgBox "Alumno esta en la lista negra", vbInformation
        verifica_pagos = False
        Exit Function
    End If
    objRS.Close
    
    'Verifico pagos de aņos anteriores
    var_sql = "select estado_dtll,mes" _
            & " From fnza_detalle_mensualidad" _
            & " where run_alu = '" & rut & "' and monto_tot_cuota > (monto_abono + " _
            & saldo & ") and agno < " & Anno
    objRS.Open var_sql, conn
    
    If Not objRS.EOF Then
        sw = False
    Else
        sw = True
    End If
    
    objRS.Close
    If sw = True Then
        var_sql = "select estado_dtll,mes" _
                & " From fnza_detalle_mensualidad" _
                & " where agno = " & Anno & " and run_alu = '" _
                & rut & "' and date_format(fech_venc,'%Y-%m-28') < date_format(current_date,'%Y-%m-%d')" _
                & " and monto_tot_cuota > (monto_abono + " & saldo & ")" _
                & " order by folio_dtll"

        objRS.Open var_sql, conn
        If Not objRS.EOF Then
            verifica_pagos = False
        Else
            verifica_pagos = True
        End If
        objRS.Close
    Else
        verifica_pagos = False
    End If
End Function

Public Function verifica_cheques_protestados(ci_alu As String) As Boolean
    'Si el alumno tiene cheques protestados devuelve verdadero
    Dim var_sql As String
    Dim rut_apo As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    var_sql = "select rut_apo from apo_x_alu where ci_alu = '" & ci_alu & "' limit 1"
    objRS.Open var_sql, conn
    rut_apo = objRS("rut_apo")
    objRS.Close
    var_sql = "select * from fnza_cheques_recibidos" _
            & " where run_apo = '" & rut_apo & "' and estado_chq = 'prto'"
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_cheques_protestados = True
    Else
        verifica_cheques_protestados = False
    End If
    objRS.Close
End Function

'Devuelve true si el apoderado ha tenido protestos
Public Function valida_protestos(ci_alu As String) As Boolean
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql As String
    valida_protestos = False
    var_sql = "SELECT * " _
            & " FROM fnza_cheques_recibidos fcr,apo_x_alu axa" _
            & " where fcr.run_apo = axa.rut_apo and estado_chq = 'acla' and ci_alu = '" & ci_alu & "' and axa.agno = " & Anno
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        valida_protestos = True
    End If
    objRS.Close
End Function
