Attribute VB_Name = "Mod_Funciones_Fnzas"
' Variables Globales
' Private cnx As ADODB.Connection

Function num_folio_mens(ByVal RunApo As String, ByVal colegio As String, ByVal mes As Integer, ByVal agno As Integer, CNX As ADODB.Connection) As Long
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    
    num_folio_mens = 0
    Set RC = New ADODB.Recordset
    
    QR = "SELECT folio_mens FROM fnza_mensualidad WHERE run_apo='" & RunApo & "' And Left(colegio, 6)= '" & Left(colegio, 6) & "' And mes=" & mes & " And agno=" & agno
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        RC.Close
        Exit Function
    End If
    If RC.RecordCount <> 1 Then
        MsgBox "Folios erróneos"
        RC.Close
        Exit Function
    Else
        RC.MoveFirst
        num_folio_mens = RC("folio_mens")
    End If
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.num_folio_mens", Err.Description
End Function

Function crear_folio_mens(ByVal RunApo As String, ByVal colegio As String, ByVal mes As Integer, ByVal agno As Integer, CNX As ADODB.Connection) As Long
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim Res As Long
    
    crear_folio_mens = 0
    Set CMD = New ADODB.Command
    
    QR = "INSERT fnza_mensualidad(run_apo,colegio,fech_venc,estado_mens,mes,agno) VALUES ('" & RunApo & "','" & Left(colegio, 6) & "','" & agno & "-" & Format(mes, "00") & "-10" & "','clda','" & mes & "','" & agno & "')"
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute
    Set CMD = Nothing
    
    crear_folio_mens = num_folio_mens(RunApo, Left(colegio, 6), mes, agno, CNX)
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.crear_folio_mens", Err.Description
End Function

Function actualiza_estado_mens(ByVal FolioMens As Long, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Res As Long
    
    actualiza_estado_mens = False
    Set RC = New ADODB.Recordset
    Set CMD = New ADODB.Command
    
    QR = "SELECT folio_mens, estado_mens, monto_mens, abono_mens As 'monto_abonado' FROM fnza_mensualidad WHERE folio_mens=" & FolioMens & " LIMIT 1;"
    
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        MsgBox "Folio " & FolioMens & " no existe"
        RC.Close
        Set RC = Nothing
        Set CMD = Nothing
        Exit Function
    End If
    RC.MoveFirst
    
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    ' Si la mensualidad esta nula o esta repactada o los alumnos fueron retirados no hacer nada.
    If (RC("estado_mens") = "nula" Or RC("estado_mens") = "reti" Or RC("estado_mens") = "rpto") Then
        RC.Close
        Set CMD = Nothing
        Set RC = Nothing
        actualiza_estado_mens = True
        Exit Function
    End If
    ' Si la deuda es igual a lo pagado el estado es 'cldo' (Cancelado).
    If (RC("monto_mens") = RC("monto_abonado")) Then
        CMD.CommandText = "UPDATE fnza_mensualidad SET colegio = left(colegio,6),estado_mens='clda' WHERE folio_mens=" & FolioMens
        CMD.Execute
        actualiza_estado_mens = True
    ' Si lo pagado es menor a lo adeudado entonces el estado es 'abon' (Abonado).
    ElseIf (RC("monto_mens") > RC("monto_abonado") And RC("monto_abonado") > 0) Then
        CMD.CommandText = "UPDATE fnza_mensualidad SET colegio = left(colegio,6),estado_mens='abon' WHERE folio_mens=" & FolioMens
        CMD.Execute
        actualiza_estado_mens = True
    ' Si lo pagado es mayor a lo adeudado entonces, existe un saldo a favor del cliente, 'cred (Credito)'
    ElseIf (RC("monto_mens") < RC("monto_abonado") And RC("monto_abonado") > 0) Then
        CMD.CommandText = "UPDATE fnza_mensualidad SET colegio = left(colegio,6),estado_mens='cred' WHERE folio_mens=" & FolioMens
        CMD.Execute
        actualiza_estado_mens = True
    ' si no a pagado una deuda entonces lka deuda esta impaga, 'impg'
    ElseIf (RC("monto_abonado") = 0 And RC("monto_mens") > 0) Then
        CMD.CommandText = "UPDATE fnza_mensualidad SET colegio = left(colegio,6),estado_mens='impg' WHERE folio_mens=" & FolioMens
        CMD.Execute
        actualiza_estado_mens = True
    Else
        Err.Raise vbObjectError + 518, "Source: Mod_funcionesFnzas.actualiza_estado_mens", "Error desconocido en comparación de montos"
    End If
    
    RC.Close
    Set CMD = Nothing
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.actualiza_estado_mens", Err.Description
End Function

Function fijar_mens(ByVal FolioMens As Long, ByVal monto As Long, ByVal Monto_d As Double, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim Res As Long
    
    fijar_mens = False
    Set CMD = New ADODB.Command
    
    QR = "UPDATE fnza_mensualidad SET monto_mens=" & monto & ",monto_mens_d = " & Trim(str(Monto_d)) & " WHERE folio_mens=" & FolioMens
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute
    
    fijar_mens = actualiza_estado_mens(FolioMens, CNX)
    
    Set CMD = Nothing
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.fijar_mens", Err.Description
End Function

Function abonar_mens(ByVal FolioMens As Long, ByVal monto As Long, ByVal FechaAbono As Date, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim Res As Long
    
    abonar_mens = False
    Set CMD = New ADODB.Command
    
    QR = "UPDATE fnza_mensualidad SET abono_mens=(abono_mens + " & monto & "), fech_pago='" & Format(FechaAbono, "yyyy-mm-dd") & "' WHERE folio_mens=" & FolioMens
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute
    
    abonar_mens = actualiza_estado_mens(FolioMens, CNX)
    
    Set CMD = Nothing
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.abonar_mens", Err.Description
End Function

Function num_folio_dtll(ByVal RunAlu As String, ByVal Concepto As String, ByVal colegio As String, ByVal mes As Integer, ByVal agno As Integer, CNX As ADODB.Connection) As Long
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    
    num_folio_dtll = 0
    Set RC = New ADODB.Recordset
    
    'QR = "SELECT folio_dtll FROM fnza_detalle_mensualidad WHERE run_alu='" & RunAlu & "' And concepto='" & Concepto & "' And colegio= '" & Left(colegio, 6) & "' And mes=" & Mes & " And agno=" & agno
    QR = "SELECT folio_dtll FROM fnza_detalle_mensualidad WHERE run_alu='" & RunAlu & "' And concepto='" & Concepto & "' And mes=" & mes & " And agno=" & agno
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        'caso sin folio
        RC.Close
        Exit Function
    End If
    If RC.RecordCount <> 1 Then
        MsgBox "Folios erróneos"
        RC.Close
        Exit Function
    Else
        RC.MoveFirst
        num_folio_dtll = RC("folio_dtll")
    End If
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    'RC.Close
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.num_folio_dtll", Err.Description
End Function

Function crear_folio_dtll(ByVal RunAlu As String, ByVal RunApo As String, ByVal Concepto As String, ByVal colegio As String, ByVal mes As Integer, ByVal agno As Integer, CNX As ADODB.Connection) As Long
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim Res, FolioMens As Long
    
    crear_folio_dtll = 0
    Set CMD = New ADODB.Command
    
    FolioMens = num_folio_mens(RunApo, Left(colegio, 6), mes, agno, CNX)
    If FolioMens = 0 Then
        FolioMens = crear_folio_mens(RunApo, Left(colegio, 6), mes, agno, CNX)
    End If
    
    QR = "REPLACE fnza_detalle_mensualidad(run_alu,folio_mens,colegio,concepto,fech_venc,estado_dtll,mes,agno) VALUES ('" & RunAlu & "','" & FolioMens & "','" & Left(colegio, 6) & "','" & Concepto & "','" & agno & "-" & Format(mes, "00") & "-10" & "','clda','" & mes & "','" & agno & "')"
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute
    Set CMD = Nothing
    
    crear_folio_dtll = num_folio_dtll(RunAlu, Concepto, Left(colegio, 6), mes, agno, CNX)
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    'Resume
    Err.Raise Err.Number, "Mod_funcionesFnzas.crear_folio_dtll", Err.Description
End Function

Function actualiza_estado_dtll(ByVal FolioDtll As Long, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Res As Long
    
    actualiza_estado_dtll = False
    Set RC = New ADODB.Recordset
    Set CMD = New ADODB.Command
    
    QR = "SELECT folio_dtll, estado_dtll, monto_tot_cuota as 'monto_mens', monto_abono As 'monto_abonado' FROM fnza_detalle_mensualidad WHERE folio_dtll=" & FolioDtll & " LIMIT 1;"
    
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        MsgBox "Folio " & FolioMens & " no existe"
        RC.Close
        Set RC = Nothing
        Set CMD = Nothing
        Exit Function
    End If
    RC.MoveFirst
    
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    ' Si el detalle esta nula o esta repactada o el alumno fue retirado no hacer nada.
    If (RC("estado_dtll") = "nula" Or RC("estado_dtll") = "reti" Or RC("estado_dtll") = "rpto") Then
        RC.Close
        Set CMD = Nothing
        Set RC = Nothing
        actualiza_estado_dtll = True
        Exit Function
    End If
    ' Si la deuda es igual a lo pagado el estado es 'cldo' (Cancelado).
    If (RC("monto_mens") = RC("monto_abonado")) Then
        CMD.CommandText = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6),estado_dtll='clda' WHERE folio_dtll=" & FolioDtll
        CMD.Execute
        actualiza_estado_dtll = True
    ' Si lo pagado es menor a lo adeudado entonces el estado es 'abon' (Abonado).
    ElseIf (RC("monto_mens") > RC("monto_abonado") And RC("monto_abonado") > 0) Then
        CMD.CommandText = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6),estado_dtll='abon' WHERE folio_dtll=" & FolioDtll
        CMD.Execute
        actualiza_estado_dtll = True
    ' Si lo pagado es mayor a lo adeudado entonces, existe un saldo a favor del cliente, 'cred (Credito)'
    ElseIf (RC("monto_mens") < RC("monto_abonado") And RC("monto_abonado") > 0) Then
        CMD.CommandText = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6) , estado_dtll='cred' WHERE folio_dtll=" & FolioDtll
        CMD.Execute
        actualiza_estado_dtll = True
    ' si no a pagado una deuda entonces la deuda esta impaga, 'impg'
    ElseIf (RC("monto_abonado") = 0 And RC("monto_mens") > 0) Then
        CMD.CommandText = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6),estado_dtll='impg' WHERE folio_dtll=" & FolioDtll
        CMD.Execute
        actualiza_estado_dtll = True
    Else
        Err.Raise vbObjectError + 514, "Mod_funcionesFnzas.actualiza_estado_dtll", "Error desconocido en comparación de montos"
    End If
    
    RC.Close
    Set CMD = Nothing
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Set RC = Nothing
    MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & "Mod_funcionesFnzas.actualiza_estado_dtll"
End Function

Function num_mens_from_dtll(ByVal FolioDtll As Long, CNX As ADODB.Connection) As Long
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    
    num_mens_from_dtll = 0
    Set RC = New ADODB.Recordset
    
    QR = "SELECT folio_mens FROM fnza_detalle_mensualidad WHERE folio_dtll=" & FolioDtll & " LIMIT 1;"
    RC.Open QR, CNX
    If RC.RecordCount <> 0 Then
        RC.MoveFirst
        num_mens_from_dtll = RC("folio_mens")
    End If
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.num_mens_from_dtll", Err.Description
End Function

Function fijar_montos_detalle(ByRef CNX As ADODB.Connection, ByVal FolioDtll As Long, ByVal MtoTotal As Long, ByVal MtoTotal_d As Double, Optional ByVal MtoExenHermano As Long = 0, Optional ByVal MtoExenBeca As Long = 0, Optional ByVal MtoExenDescuento As Long = 0, Optional ByVal MtoExenConvenio As Long = 0, Optional ByVal CodConvenio As String = "none", Optional ByVal MtoExenTipoPago As Long = 0) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Res, total_mens, FolioMens As Long
    
    fijar_montos_detalle = False
    Set CMD = New ADODB.Command
    Set RC = New ADODB.Recordset
    
    FolioMens = num_mens_from_dtll(FolioDtll, CNX)
    If FolioMens = 0 Then
        Set CMD = Nothing
        Set RC = Nothing
        Err.Raise vbObjectError + 515, "Mod_funcionesFnzas.fijar_montos_detalle", "Relación de Folios Detalle-Mensualidad No Existe." & vbCrLf & "El folio del detalle dado no existe o no existe el folio de la mensualidad correspondiente al detalle."
        Exit Function
    End If
    
    Res = MtoTotal - MtoExenHermano - MtoExenBeca - MtoExenDescuento - MtoExenConvenio - MtoExenTipoPago
    If Res < 0 Then
        'Set CMD = Nothing
        'Set RC = Nothing
        Log_AddAnotacion "Saldo Negativo. El folio interno nº" & FolioDtll & " tiene excenciones que exceden el total de la cuota."
        'Form_Principal.MensajeTxtBox "Saldo Negativo. El folio interno nº" & FolioDtll & " tiene excenciones que exceden el total de la cuota.", Form_Principal.Text1
        'MsgBox "Saldo Negativo. El folio interno nº" & FolioDtll & " tiene excenciones que exceden el total de la cuota.", vbCritical, "ALERTA!!"
        Res = 0
        'Err.Raise vbObjectError + 516, "Mod_funcionesFnzas.fijar_montos_detalle", "Saldo Negativo." & vbCrLf & "El total de exenciones excede el monto total del concepto."
        'Exit Function
    End If
    
    QR = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6)," & _
         "monto_total=" & MtoTotal & ", " & _
         "monto_exen_hno=" & MtoExenHermano & ", " & _
         "monto_exen_bca=" & MtoExenBeca & ", " & _
         "monto_exen_desc=" & MtoExenDescuento & ", " & _
         "monto_exen_conv=" & MtoExenConvenio & ", " & _
         "monto_exen_tipo_pago=" & MtoExenTipoPago & ", " & _
         "monto_tot_cuota=" & Res & ", " & _
         "monto_tot_cuota_d=" & Trim(str(MtoTotal_d)) & ", " & _
         "cod_convenio='" & CodConvenio & "' " & _
         "WHERE folio_dtll=" & FolioDtll & " LIMIT 1;"
    ' MtoExenTipoPago
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute
       
    fijar_montos_detalle = actualiza_estado_dtll(FolioDtll, CNX)
    Set CMD = Nothing
    
    'Qr = "SELECT sum(monto_tot_cuota) as monto_mens FROM fnza_detalle_mensualidad WHERE folio_mens =" & FolioMens & "  AND estado_dtll<>'nula'  AND estado_dtll<>'reti' AND estado_dtll<>'rpto' GROUP BY folio_mens"
    QR = "SELECT sum(monto_tot_cuota) as monto_mens,sum(monto_tot_cuota_d) as monto_mens_d FROM fnza_detalle_mensualidad WHERE folio_mens =" & FolioMens & "  GROUP BY folio_mens"
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        Err.Raise vbObjectError + 517, "Mod_funcionesFnzas.fijar_montos_detalle", "No se encontraron registros para este detalle"
    End If
    RC.MoveFirst
    fijar_mens FolioMens, RC("monto_mens"), RC("monto_mens_d"), CNX
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    
    Set CMD = Nothing
    Set RC = Nothing
    
    Err.Raise Err.Number, "Mod_funcionesFnzas.fijar_montos_detalle", Err.Description
    'Resume
End Function

Function abonar_detalle(ByVal FolioDtll As Long, ByVal monto As Long, ByVal FechaAbono As Date, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    
    abonar_detalle = False
    Set CMD = New ADODB.Command
    
    QR = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6),monto_abono=(monto_abono + " & monto & "), fech_pago='" & Format(FechaAbono, "yyyy-mm-dd") & "' WHERE folio_dtll=" & FolioDtll
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute
        
    abonar_detalle = actualiza_estado_dtll(FolioDtll, CNX)
    
    abonar_mens num_mens_from_dtll(FolioDtll, CNX), monto, FechaAbono, CNX
        
    Set CMD = Nothing
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.abonar_detalle", Err.Description
End Function

Function concepto_de_dtll(ByVal FolioDtll As Long, CNX As ADODB.Connection) As Variant
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Resp(1 To 3) As Variant
    
    concepto_de_dtll = Null
    Set RC = New ADODB.Recordset
    QR = "SELECT concepto, fech_venc, (monto_tot_cuota - monto_abono) as monto FROM fnza_detalle_mensualidad WHERE folio_dtll=" & FolioDtll & ";"
    
    RC.Open QR, CNX
    If Not (RC.RecordCount = 0) Then
        RC.MoveFirst
        Resp(1) = RC("concepto")
        Resp(2) = RC("fech_venc")
        Resp(3) = RC("monto")
        concepto_de_dtll = Resp
    End If
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.concepto_de_detalle", Err.Description
End Function

'se agrega el tipo de pago al where 06-06-2022
Function num_folio_mvto(ByVal FolioDtll As Long, ByVal FechPago As Date, ByVal CodConcepto As String, ByVal FechVencCptos As Date, CNX As ADODB.Connection, TipoPago As String) As Long
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    
    num_folio_mvto = 0
    Set RC = New ADODB.Recordset
    QR = "SELECT folio_mvto FROM fnza_movimientos_mensualidad WHERE folio_dtll=" & FolioDtll & " AND fech_pago='" & Format(FechPago, "yyyy-mm-dd") & "' "
    QR = QR & " AND cod_concepto='" & CodConcepto & "' AND fech_venc_cptos='" & Format(FechVencCptos, "yyyy-mm-dd") & "' AND TIPO_PAGO = '" & TipoPago & "'  LIMIT 1;"
    RC.Open QR, CNX
    If Not (RC.RecordCount = 0) Then
        num_folio_mvto = RC("folio_mvto")
    End If
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.num_folio_mvto", Err.Description
End Function

Function num_folio_blta(ByVal FolioFisico As Long, ByVal colegio As String, CNX As ADODB.Connection) As Long
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    
    num_folio_blta = 0
    Set RC = New ADODB.Recordset
        
    'QR = "SELECT folio_blta FROM fnza_boletas WHERE folio_blta_fisico=" & FolioFisico & " AND colegio='" & colegio & "' AND tipo_doc='blta';"
    'QR = "SELECT folio_blta FROM fnza_boletas WHERE folio_blta_fisico=" & FolioFisico & " AND mid(colegio,1,6) = '" & Mid(colegio, 1, 6) & "' AND tipo_doc='blta';"
    QR = "SELECT folio_blta FROM fnza_boletas WHERE folio_blta_fisico=" & FolioFisico & " AND mid(colegio,1,6) = '" & Mid(colegio, 1, 6) & "' AND tipo_doc='blta' and tipo_blta = 3;"
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        RC.Close
        Set RC = Nothing
        Exit Function
    End If
    RC.MoveFirst
    
    num_folio_blta = RC("folio_blta")
    
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.num_folio_blta", Err.Description
    Resume
End Function

Function crear_boleta(ByVal FolioFisico As Long, ByVal colegio As String, ByVal RunApo As String, ByVal RunAlu As String, ByVal FechaBoleta As Date, _
                      ByVal FechaPago As Date, ByVal monto As Long, ByVal ProcPago As String, ByVal TipoPago As String, ByRef Detalle() As Long, _
                      FormaPago1 As String, FormaPago2 As String, Valor1 As Long, Valor2 As Long, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    'Dim RC As ADODB.Recordset
    Dim CMD As ADODB.Command
    Dim Num_Dtlls As Integer
    Dim Suma_Dtlls As Long
    Dim Concepto As Variant
    Dim CodConcepto As String
    Dim FecConcepto As Date
    Dim FolioBlta As Long
    Dim FolioDtll As Long
    Dim FolioMvto As Long
    Dim resto_monto As Long
    Dim valor As Long
    Dim Resto_MontoAux As Long  'se agrega 24-05-2022
    Dim MontoPagoAux As Long   'se agrega 26-05-2022
    Dim TotalDetalle As Long   'se agrega 26-05-2022
    Dim Valor1Aux As Long
    Dim Valor2Aux As Long
    crear_boleta = False
    
    Num_Dtlls = UBound(Detalle, 2)
    
    
    Suma_Dtlls = 0
    
    ' verifico que la boleta tenga detalle
    If Num_Dtlls = 0 Then
        Err.Raise vbObjectError + 522, "Mod_funcionesFnzas.crear_boleta", "La boleta no tiene detalle(s)"
        Exit Function
    End If
    ' verifico que la boleta no exista
    If Not (num_folio_blta(FolioFisico, Left(colegio, 6), CNX) = 0) Then
        Err.Raise vbObjectError + 521, "Mod_funcionesFnzas.crear_boleta", "La Boleta nº" & FolioFisico & " ya esta ingresada al sistema."
        Exit Function
    End If
    ' varifico el monto de la boleta con el monto del detalle
    For i = 1 To Num_Dtlls
        Suma_Dtlls = Suma_Dtlls + Detalle(2, i)
    Next i
    If Not (Suma_Dtlls = monto) Then
        'Err.Raise vbObjectError + 523, "Mod_funcionesFnzas.crear_boleta", "La suma de los detalles y el total la boleta no coinciden"
        'Exit Function
    End If
    
    QR = "INSERT INTO fnza_boletas(fecha_ingreso,usuario_ingreso,folio_blta_fisico, colegio, run_apo, run_alu, fecha_blta,fecha_pago, monto_blta, estado_blta, cod_anulacion_blta, tipo_doc,tipo_blta) " & _
         "VALUES (now(),'" & Nom_com_usu_ses & "','" & FolioFisico & "','" & Left(colegio, 6) & "','" & RunApo & "','" & RunAlu & "','" & Format(FechaBoleta, "yyyy-mm-dd") & "','" & Format(FechaPago, "yyyy-mm-dd") & "','" & monto & "','aprb','none','blta',3);"
    Set CMD = New ADODB.Command
    CMD.CommandType = adCmdText
    CMD.ActiveConnection = CNX
    CMD.CommandText = QR
    CMD.Execute
    
    FolioBlta = num_folio_blta(FolioFisico, Left(colegio, 6), CNX)
    If FolioBlta = 0 Then
        Err.Raise vbObjectError + 524, "", "Se produjo un error al registrar la boleta nº" & FolioFisico & " " & colegio
        Exit Function
    End If
    resto_monto = monto
    TotalDetalle = UBound(Detalle, 2)
    For i = 1 To Num_Dtlls
        If i <= TotalDetalle Then
           FolioDtll = Detalle(1, i)
        End If
        Concepto = concepto_de_dtll(FolioDtll, CNX)
        If FormaPago1 <> "" And FormaPago2 <> "" Then
            If Detalle(3, i) = 1 Then
                TipoPago = FormaPago1
            Else
                TipoPago = FormaPago2
            End If
            
            QR = "INSERT INTO fnza_movimientos_mensualidad(folio_dtll, fech_pago,proc_pago,tipo_pago,monto_pago,cod_concepto,fech_venc_cptos,estado_pago) "
            QR = QR & " Values ('" & FolioDtll & "','" & Format(FechaBoleta, "yyyy-mm-dd") & "','" & ProcPago & "','" & TipoPago & "', "
            QR = QR & "  '" & Detalle(2, i) & "','" & Concepto(1) & "','" & Format(Concepto(2), "yyyy-mm-dd") & "','aprb')"
            CMD.CommandText = QR
            CMD.Execute
            'se agrega el tipo de pago 06-06-2022
            FolioMvto = num_folio_mvto(FolioDtll, FechaBoleta, Concepto(1), Concepto(2), CNX, TipoPago)
            If FolioMvto = 0 Then
                Err.Raise vbObjectError + 525, "", "Se a producido un error al registrar el movimiento asociado a la boleta " & FolioBlta & " " & FolioDtll
                Exit Function
            End If
            ' funcion que inserta la relacion en fnza_dtll_mvto
            crear_relacion_blta_dtll_mvto FolioBlta, FolioDtll, FolioMvto, CNX
            abonar_detalle FolioDtll, Detalle(2, i), FechaBoleta, CNX

        Else
            If resto_monto <= Detalle(2, i) Then
                valor = resto_monto
                resto_monto = resto_monto - valor
            Else
                valor = Detalle(2, i)
                resto_monto = resto_monto - valor
            End If
            
            If (resto_monto > 0) And (i = Num_Dtlls) Then
                QR = "INSERT INTO fnza_movimientos_mensualidad(folio_dtll,fech_pago,proc_pago,tipo_pago,monto_pago,cod_concepto,fech_venc_cptos,estado_pago) Values ('" & FolioDtll & "','" & Format(FechaBoleta, "yyyy-mm-dd") & "','" & ProcPago & "','" & TipoPago & "','" & (valor + resto_monto) & "','" & Concepto(1) & "','" & Format(Concepto(2), "yyyy-mm-dd") & "','aprb')"
            Else
                QR = "INSERT INTO fnza_movimientos_mensualidad(folio_dtll, fech_pago,proc_pago,tipo_pago,monto_pago,cod_concepto,fech_venc_cptos,estado_pago) Values ('" & FolioDtll & "','" & Format(FechaBoleta, "yyyy-mm-dd") & "','" & ProcPago & "','" & TipoPago & "','" & valor & "','" & Concepto(1) & "','" & Format(Concepto(2), "yyyy-mm-dd") & "','aprb')"
            End If
            CMD.CommandText = QR
            CMD.Execute
                       
            FolioMvto = num_folio_mvto(FolioDtll, FechaBoleta, Concepto(1), Concepto(2), CNX, TipoPago)
            If FolioMvto = 0 Then
                Err.Raise vbObjectError + 525, "", "Se a producido un error al registrar el movimiento asociado a la boleta " & FolioBlta & " " & FolioDtll
                Exit Function
            End If
            ' funcion que inserta la relacion en fnza_dtll_mvto
            crear_relacion_blta_dtll_mvto FolioBlta, FolioDtll, FolioMvto, CNX
            ' ##########
            ' Luego abonar el monto... en el detalle
                If (resto_monto > 0) And (i = Num_Dtlls) Then
                    abonar_detalle FolioDtll, (resto_monto + valor), FechaBoleta, CNX
                Else
                    abonar_detalle FolioDtll, valor, FechaBoleta, CNX
                End If
         End If
         
    Next i
    
    
    Set CMD = Nothing
    crear_boleta = True
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.crear_boleta", Err.Description
End Function

Function crear_relacion_blta_dtll_mvto(ByVal FolioBlta As Long, ByVal FolioDtll As Long, ByVal FolioMvto As Long, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim Hecho As Long
    
    crear_relacion_blta_dtll_mvto = False
    Set CMD = New ADODB.Command
    QR = "INSERT INTO fnza_dtll_mvto(folio_blta,folio_dtll,folio_mvto) " & _
         "VALUES ('" & FolioBlta & "','" & FolioDtll & "','" & FolioMvto & "');"
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute Hecho
    Set CMD = Nothing
    If Hecho = 1 Then
        crear_relacion_blta_dtll_mvto = True
    End If
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_funcionesFnzas.crear_relacion_blta_dtll_mvto", Err.Description
End Function

Function TotalMesAlu(ByVal RunAlu As String, ByVal colegio As String, ByVal mes As Integer, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Variant
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Resp(1 To 7) As Variant
    
    Resp(1) = Null
    QR = "SELECT sum(monto_total) As 'total', sum(monto_exen_hno) As 'hno',sum(monto_exen_bca) As 'bca',sum(monto_exen_desc + monto_exen_tipo_pago) As 'desc',sum(monto_exen_conv) As 'conv',sum(monto_tot_cuota) As 'cuota',sum(monto_abono) As 'abono' " & _
         "FROM fnza_detalle_mensualidad " & _
         "WHERE run_alu='" & RunAlu & "' AND colegio='" & Left(colegio, 6) & "' AND mes=" & mes & " AND agno=" & agno & " AND estado_dtll<>'nula' AND estado_dtll<>'reti' AND estado_dtll<>'rpto' " & _
         "GROUP BY run_alu;"
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        TotalMesAlu = Resp
        Exit Function
    End If
    RC.MoveFirst
    Resp(1) = RC("total")
    Resp(2) = RC("hno")
    Resp(3) = RC("bca")
    Resp(4) = RC("desc") 'Descuentos de Ajustes + descuentos por tipo de pago
    Resp(5) = RC("conv")
    Resp(6) = RC("cuota")
    Resp(7) = RC("abono")
    TotalMesAlu = Resp
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Err.Raise Err.Number, "Mod_funcionesFnzas.TotalMesAlu", Err.Description
End Function

Function DtllMesAlu(ByVal RunAlu As String, ByVal colegio As String, ByVal mes As Integer, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Variant
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Resp() As Variant
    
    ReDim Preserve Resp(1 To 13, 1 To 1)
    Resp(1, 1) = Null
    '######################
    '######################
'''    QR = "SELECT folio_dtll, concepto, fech_venc,monto_total As 'total', monto_exen_hno As 'hno',monto_exen_bca As 'bca',monto_exen_desc As 'desc',monto_exen_tipo_pago As 'descTP',monto_exen_conv As 'conv',monto_tot_cuota As 'cuota',monto_abono As 'abono'," & _
'''         "if(fech_pago='0000-00-00','NULL',CONCAT(fech_pago,'')) as fecha_pago,estado_dtll,cod_convenio " & _
'''         "FROM fnza_detalle_mensualidad " & _
'''         "WHERE run_alu='" & RunAlu & "' AND colegio='" & Left(colegio, 6) & "' AND mes=" & mes & " AND agno=" & agno & " AND estado_dtll<>'nula' AND estado_dtll<>'reti' AND estado_dtll<>'rpto';"
         
    QR = "SELECT folio_dtll, concepto, fech_venc,monto_total As 'total', monto_exen_hno As 'hno',monto_exen_bca As 'bca',monto_exen_desc As 'desc',monto_exen_tipo_pago As 'descTP',monto_exen_conv As 'conv',monto_tot_cuota As 'cuota',monto_abono As 'abono'," & _
         "if(fech_pago='0000-00-00','NULL',CONCAT(fech_pago,'')) as fecha_pago,estado_dtll,cod_convenio " & _
         "FROM fnza_detalle_mensualidad " & _
         "WHERE run_alu='" & RunAlu & "' AND mes=" & mes & " AND agno=" & agno & " AND estado_dtll<>'nula' AND estado_dtll<>'reti' AND estado_dtll<>'rpto';"
    
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        DtllMesAlu = Resp
        Exit Function
    End If
    RC.MoveFirst
    For i = 1 To RC.RecordCount
        ReDim Preserve Resp(1 To 13, 1 To i)
        Resp(1, i) = RC("folio_dtll")
        Resp(2, i) = RC("concepto")
        Resp(3, i) = RC("fech_venc")
        Resp(4, i) = RC("total")
        Resp(5, i) = RC("hno")                  'Descuento de Hermano
        Resp(6, i) = RC("bca")                  'Descuento de Beca
        Resp(7, i) = RC("desc") + RC("descTP")  'Descuento de Ajustes Especiales + Descuento por Tipo de Pago
        Resp(8, i) = RC("conv")                 'Descuento de Convenios
        Resp(9, i) = RC("cuota")
        Resp(10, i) = RC("abono")
        Resp(11, i) = RC("fecha_pago")
        Resp(12, i) = RC("estado_dtll")
        Resp(13, i) = RC("cod_convenio")
        RC.MoveNext
    Next i
    DtllMesAlu = Resp
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Err.Raise Err.Number, "Mod_funcionesFnzas.DtllMesAlu", Err.Description
End Function

Function DtllPagoApo(ByVal RunApo As String, ByVal colegio As String, ByVal mes As Integer, ByVal agno As Integer, ByRef CNX As ADODB.Connection, Optional ByVal RunAlu As String = "", Optional ByVal Nulos As Boolean = False) As Variant
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Resp() As Variant
    Dim ConNulo As String
    Dim ConAlu As String
    
    If Not (ConAlu = "") Then
        ConAlu = " AND run_alu='" & RunAlu & "'"
    Else
        ConAlu = ""
    End If
    If Nulos Then
        ConNulo = ""
    Else
        ConNulo = " AND estado_blta<>'nula' and estado_blta<>'devo' and estado_blta<>'reti' and estado_blta<>'rpto' "
    End If
    QR = "SELECT fb.folio_blta, fb.fecha_blta,fb.fecha_pago,fb.run_alu,fb.folio_blta_fisico, fb.estado_blta, fb.cod_anulacion_blta, fb.monto_blta, fdm.fech_venc, fdm.concepto, fmm.proc_pago, fmm.tipo_pago, fmm.monto_pago FROM fnza_boletas fb, fnza_movimientos_mensualidad fmm, fnza_detalle_mensualidad fdm, fnza_dtll_mvto rel WHERE fb.tipo_doc='blta' AND rel.folio_blta=fb.folio_blta AND rel.folio_mvto=fmm.folio_mvto AND rel.folio_dtll=fdm.folio_dtll AND fb.run_apo='" & "' " & ConAlu & ConNulo
    Set RC = New ADODB.Recordset
    
    RC.Open QR, CNX
    
    If RC.RecordCount = 0 Then
        ReDim Preserve Resp(1 To 13, 1 To 1)
        Resp(1, 1) = Null
        DtllPagoApo = Resp
        Exit Function
    End If
    RC.MoveFirst
    For i = 1 To RC.RecordCount
        ReDim Preserve Resp(1 To 13, 1 To i)
        Resp(1, i) = RC("folio_blta")
        Resp(2, i) = RC("fecha_blta")
        Resp(3, i) = RC("fecha_pago")
        Resp(4, i) = RC("run_alu")
        Resp(5, i) = RC("folio_blta_fisico")
        Resp(6, i) = RC("estado_blta")
        Resp(7, i) = RC("cod_anulacion_blta")
        Resp(8, i) = RC("monto_blta")
        Resp(9, i) = RC("fech_venc")
        Resp(10, i) = RC("concepto")
        Resp(11, i) = RC("proc_pago")
        Resp(12, i) = RC("tipo_pago")
        Resp(13, i) = RC("monto_pago")
        RC.MoveNext
    Next i
    RC.Close
    Set RC = Nothing
    DtllPagoApo = Resp
    
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.DtllPagoApo", Err.Source
End Function

Function AnularBoleta(ByVal FolioFisico As Long, ByVal FechaAnul As Date, ByRef CNX As ADODB.Connection, Optional CodAnul As String = "none") As Boolean
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim RC2 As ADODB.Recordset
    Dim CMD As ADODB.Command
    Dim FolioBlta As Long
    Dim FolioDtll As Long
    Dim FolioMvto As Long
    Dim MontoMvto As Long
    Dim RegAffected As Long
    Dim QR As String
    
    AnularBoleta = False
    Set RC = New ADODB.Recordset
    QR = "SELECT folio_blta, estado_blta FROM fnza_boletas WHERE tipo_blta = 3 and folio_blta_fisico=" & FolioFisico & " AND estado_blta<>'nula' AND estado_blta<>'devo' AND tipo_doc='blta';"
    RC.Open QR, CNX
    If Not (RC.RecordCount = 0) Then
        RC.MoveFirst
        If Not (RC!estado_blta = "2004") Then
            FolioBlta = RC("folio_blta")
            RC.Close
        Else
            RC.Close
            Err.Raise vbObjectError + 602, "", "No se existen movimientos para la Boletas 2004, Bol nº" & FolioFisico
        End If
    Else
        RC.Close
        Err.Raise vbObjectError + 601, "", "No se encontro folio interno no nulo para la Boleta nº" & FolioFisico
        GoTo Controlerror:
    End If
    
    QR = "SELECT folio_mvto, folio_dtll FROM fnza_dtll_mvto WHERE folio_blta=" & FolioBlta
    
    RC.Open QR, CNX
    If Not (RC.RecordCount = 0) Then
        RC.MoveFirst
        
        Set RC2 = New ADODB.Recordset
        Set CMD = New ADODB.Command
        
        For i = 1 To RC.RecordCount
            FolioDtll = RC("folio_dtll")
            FolioMvto = RC("folio_mvto")
            QR = "SELECT monto_pago FROM fnza_movimientos_mensualidad WHERE folio_mvto=" & FolioMvto
            RC2.Open QR, CNX
            If RC2.RecordCount = 0 Then
                RC2.Close
                Err.Raise vbObjectError + 603, "", "Fallo al rescatar el monto del movimiento nº" & FolioMvto
                GoTo Controlerror:
            Else
                MontoMvto = RC2("monto_pago")
                RC2.Close
            End If
            ' Actualizar el movimiento, anulandolo
            QR = "UPDATE fnza_movimientos_mensualidad SET estado_pago='nula', fech_pago='" & Format(FechaAnul, "yyyy-mm-dd") & "' WHERE folio_mvto=" & FolioMvto
            CMD.ActiveConnection = CNX
            CMD.CommandType = adCmdText
            CMD.CommandText = QR
            CMD.Execute RegAffected
            If Not (RegAffected = 1) Then
                Err.Raise vbObjectError + 604, "", "Fallo al actualizar estado de 'fnza_movimientos_mensualidad', para el folio nº" & FolioMvto
                GoTo Controlerror:
            End If
            ' Limpiar cheques asociados si los hubiera
            QR = "DELETE FROM fnza_chq_mvto WHERE folio_mvto='" & FolioMvto & "';"
            CMD.CommandText = QR
            CMD.Execute RegAffected
            ' Actualizar el detalle, anulando el movimiento
            If Not (abonar_detalle(FolioDtll, (0 - MontoMvto), Date, CNX)) Then
                Err.Raise vbObjectError + 605, "", "Fallo al actualizar monto de 'fnza_detalle_mensualidad', para el folio nº" & FolioDtll
                GoTo Controlerror:
            End If
            RC.MoveNext
        Next i
        ' una vez anulados y descontados los movimientos, actualiza la boleta anulandola
        QR = "UPDATE fnza_boletas SET estado_blta='nula', cod_anulacion_blta='" & CodAnul & "', fecha_pago='" & Format(FechaAnul, "yyyy-mm-dd") & "' WHERE tipo_blta = 3 and tipo_doc='blta' AND folio_blta=" & FolioBlta
        CMD.CommandType = adCmdText
        CMD.CommandText = QR
        CMD.Execute RegAffected
        If Not (RegAffected = 1) Then
            Err.Raise vbObjectError + 604, "", "Fallo al actualizar estado de 'fnza_boleta', en folio nº" & FolioMvto
            GoTo Controlerror:
        End If
        Set RC2 = Nothing
        Set CMD = Nothing
        Set RC = Nothing
        AnularBoleta = True
    Else
        RC.Close
        Err.Raise vbObjectError + 602, "", "No se encontraron movimientos asociados para la boleta nº" & FolioFisico
        GoTo Controlerror:
    End If
    
    Exit Function
Controlerror:
    'MsgBox "error"
    'Resume
    Set RC = Nothing
    Set RC2 = Nothing
    Set CMD = Nothing
    Err.Raise Err.Number, Err.Description & vbCrLf & vbCrLf & "Mod_Funciones_Fnzas.AnularBoleta", Err.Source
End Function

Function CrearBoletaNula(ByVal FolioFisico As Long, ByVal colegio As String, ByVal CodAnulacion As String, ByRef CNX As ADODB.Connection, Optional FechAnul As Date) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim RegAffected As Long
    
    FechAnul = IIf(FechAnul = Null, Date, FechAnul)
    CrearBoletaNula = False
    Set CMD = New ADODB.Command
    QR = "INSERT INTO fnza_boletas(folio_blta_fisico,colegio,estado_blta,cod_anulacion_blta, fecha_pago,fecha_blta,tipo_doc) VALUES ('" & FolioFisico & "','" & Left(colegio, 6) & "','nula','" & CodAnulacion & "','" & Format(FechAnul, "yyyy-mm-dd") & "','" & Format(FechAnul, "yyyy-mm-dd") & "','blta')"
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute RegAffected
    If Not (RegAffected > 0) Then
        Err.Raise vbObjectError + 620, "Mod_Funciones_Fnzas.CrearBoletaNula", "La boleta de folio fisico nº" & FolioFisico & " no pudo ser insertada." & vbCrLf & "Posiblemente el folio fisico ya esta registrado, revise."
        GoTo Controlerror:
    Else
        CrearBoletaNula = True
    End If
    Set CMD = Nothing
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.CrearBoletaNula", Err.Source
End Function

Function dtll_impago(ByVal FolioDtll As String, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    
    QR = "SELECT estado_dtll FROM fnza_detalle_mensualidad WHERE folio_dtll=" & FolioDtll
    Set RC = New ADODB.Recordset
    
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        Err.Raise vbObjectError + 622, "", "No se encontro estado para el detalle Folio nº" & FolioDtll
        Exit Function
    End If
    RC.MoveFirst
    If RC("estado_dtll") = "impg" Then
        dtll_impago = True
    Else
        dtll_impago = False
    End If
    RC.Close
    
    Exit Function
Controlerror:
Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.dtll_impago", Err.Description
    'Resume
End Function

Public Function TraeCupon(ByVal RunApo As String, ByVal colegio As String, ByVal agno As Long, ByVal mes As Integer, ByVal Cod_Banco As String, CNX As ADODB.Connection) As Variant()
On Error GoTo Controlerror:
    Dim Cupon(9) As Variant
    Dim RC As ADODB.Recordset
    Dim QR As String
    QR = "select * from fnza_cuponeras c, fnza_deta_cupones d where c.folio_cuponera=d.folio_cuponera and c.rut_apo='" & RunApo & "' and d.mes =" & mes & " and c.colegio ='" & Left(colegio, 6) & "' and (ucase(d.estado)='PE' or ucase(d.estado)='PA' or ucase(d.estado)='GC') and c.agno =" & agno & " and c.banco='" & Cod_Banco & "'"
    Set RC = New ADODB.Recordset: RC.CursorLocation = adUseClient
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        Cupon(1) = RC!folio_cuponera
        Cupon(2) = RC!esta_fin
        Cupon(3) = RC!folio_cupon
        Cupon(4) = RC!Numero
        Cupon(5) = RC!mes
        Cupon(6) = RC!Vcto
        Cupon(7) = RC!valor
        Cupon(8) = RC!Estado
        Cupon(9) = RC!Ubicacion
    Else
        Cupon(1) = Null
    End If
    RC.Close
    Set RC = Nothing
    TraeCupon = Cupon
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraeCupon", Err.Description
End Function

Public Function Act_Estado_Cuponera(ByVal FolioCuponera As Long, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim CMD As ADODB.Command
    Dim Exito As Long
    Dim Cancela As Boolean
    Dim QR As String
    Cancela = True
    Act_Estado_Cuponera = False
    
    QR = "select dc.estado from fnza_cuponeras c, fnza_deta_cupones dc where c.folio_cuponera = dc.folio_cuponera and ucase(dc.estado) <> 'NU' and c.folio_cuponera =" & FolioCuponera
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        While Not RC.EOF
            If RC!Estado = "PE" Then
                Cancela = False
            End If
            RC.MoveNext
        Wend
    Else
       'No se encuentra o todos los cupones nulos.
        Cancela = False
    End If
    If Cancela Then
        QR = "update fnza_cuponeras set esta_fin='PA' where folio_cuponera = " & FolioCuponera
        Set CMD = New ADODB.Command
        CMD.CommandType = adCmdText
        CMD.ActiveConnection = CNX
        CMD.CommandText = QR
        CMD.Execute Exito
        If Exito = 0 Then
            Err.Raise vbObjectError + 640, "", "El estado del cupon de folio " & FolioCuponera & " no fue actualizado."
        End If
    End If
    Act_Estado_Cuponera = True
    RC.Close
    Set RC = Nothing
    Exit Function
Controlerror:
    Set RC = Nothing
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.Act_Estado_Cuponera", Err.Description
End Function

Public Function Ult_Folio_Fisico(colegio As String, CNX As ADODB.Connection) As Long
On Error GoTo Controlerror:
    Dim Folio As Long
    Dim RC As ADODB.Recordset
    Dim QR As String
    Cancela = True
    'QR = "SELECT max(folio_blta_fisico) AS UltFolio FROM fnza_boletas WHERE colegio ='" & colegio & "' and tipo_doc='blta'"
    QR = "SELECT max(folio_blta_fisico) AS UltFolio FROM fnza_boletas WHERE tipo_doc='blta'"
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    RC.MoveFirst
    If Not IsNull(RC!ultfolio) And RC!ultfolio <> "" Then
        Ult_Folio_Fisico = RC!ultfolio
    Else
        Ult_Folio_Fisico = 0
    End If
    RC.Close
    Set RC = Nothing
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.Ult_Folio_Fisico", Err.Description
End Function

Function TraduceConceptoDtll(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceConceptoDtll = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_cod_concepto_dtll WHERE concepto='" & cod & "';"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceConceptoDtll = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceConceptoDtll", Err.Description
End Function

Function TraduceEstadoDtll(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceEstadoDtll = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_cod_estado_dtll WHERE estado_dtll='" & cod & "';"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceEstadoDtll = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceEstadoDtll", Err.Description
End Function

Function TraduceEstadoMens(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceEstadoMens = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_cod_estado_mens WHERE estado_mens='" & cod & "';"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceEstadoMens = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceEstadoMens", Err.Description
End Function

Function TraduceEstadoBlta(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceEstadoBlta = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_cod_estado_blta WHERE estado_blta='" & cod & "';"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceEstadoBlta = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceEstadoBlta", Err.Description
End Function

Function TraduceEstadoPago(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceEstadoPago = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_cod_estado_pago WHERE estado_pago='" & cod & "';"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceEstadoPago = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceEstadoPago", Err.Description
End Function

Function TraduceProcPago(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceProcPago = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_cod_proc_pago WHERE proc_pago='" & cod & "';"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceProcPago = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceProcPago", Err.Description
End Function

Function TraduceTipoPago(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceTipoPago = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_cod_tipo_pago WHERE tipo_pago='" & cod & "';"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceTipoPago = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceTipoPago", Err.Description
End Function

Function TraduceConceptoConv(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceConceptoConv = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_convenios WHERE cod_convenio='" & cod & "' LIMIT 1;"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceConceptoConv = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceConceptoConv", Err.Description
End Function

Function TraduceEstadoChq(ByVal cod As String, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    ' #############
    TraduceEstadoChq = cod
    Set RC = New ADODB.Recordset
    QR = "SELECT descripcion FROM fnza_cod_estado_chq WHERE estado_chq='" & cod & "';"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        TraduceEstadoChq = RC("descripcion")
    End If
    RC.Close
    Set RC = Nothing
    ' #############
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceEstadoChq", Err.Description
End Function

Function CrearBoleta2004(ByVal FolioFisico As Long, ByVal colegio As String, ByVal BDname As String, ByVal FechaPago As Date, ByVal FechaEmision As Date, ByVal MontoBlta As Long, ByVal RunApo As String, ByVal RunAlu As String, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim RegAffected As Long
    
    CrearBoleta2004 = False
    Set CMD = New ADODB.Command
    QR = "INSERT INTO " & BDname & ".fnza_boletas(folio_blta_fisico,colegio,estado_blta,fecha_blta, fecha_pago,monto_blta,run_apo,run_alu,tipo_doc) VALUES ('" & FolioFisico & "','" & Left(colegio, 6) & "','2004','" & Format(FechaEmision, "yyyy-mm-dd") & "','" & Format(FechaPago, "yyyy-mm-dd") & "','" & MontoBlta & "','" & RunApo & "','" & RunAlu & "','blta');"
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    CMD.CommandText = QR
    CMD.Execute RegAffected
    If Not (RegAffected > 0) Then
        Err.Raise vbObjectError + 620, "Mod_Funciones_Fnzas.CrearBoletaNula", "La boleta de folio fisico nº" & FolioFisico & " no pudo ser insertada como boleta 2004." & vbCrLf & "Posiblemente el folio fisico ya esta registrado, revise."
        GoTo Controlerror:
    Else
        CrearBoleta2004 = True
    End If
    Set CMD = Nothing
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.CrearBoleta2004", Err.Source
End Function

Function AnularDtll(ByVal FolioDtll As Long, ByVal CodAnul As String, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim RegAffected As Long
    
    AnularDtll = False
    
    QR = "UPDATE fnza_detalle_mensualidad SET estado_dtll='" & CodAnul & "' WHERE folio_dtll='" & FolioDtll & "' LIMIT 1;"
    Set CMD = New ADODB.Command
    CMD.CommandType = adCmdText
    CMD.ActiveConnection = CNX
    CMD.CommandText = QR
    CMD.Execute RegAffected
    
    If RegAffected = 1 Then
        AnularDtll = True
    End If
    
    Set CMD = Nothing
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.AnularDtll", Err.Source
End Function

Function AnularMens(ByVal FolioMens As Long, ByVal CodAnul As String, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim RegAffected As Long
    
    AnularMens = False
    
    QR = "UPDATE fnza_mensualidad SET estado_mens='" & CodAnul & "' WHERE folio_mens='" & FolioMens & "' LIMIT 1;"
    Set CMD = New ADODB.Command
    CMD.CommandType = adCmdText
    CMD.ActiveConnection = CNX
    CMD.CommandText = QR
    CMD.Execute RegAffected
    
    If RegAffected = 1 Then
        AnularMens = True
    End If
    
    Set CMD = Nothing
    
    Exit Function
Controlerror:
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.AnularMens", Err.Source
End Function


Function RetirarAlumno(ByVal RunAlu As String, ByVal FechReti As Date, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim MesINI As Integer
    Dim agno As Integer
        
    RetirarAlumno = False
    agno = Year(FechReti)
    
    If Day(FechReti) > 15 Then
        MesINI = Month(FechReti) + 1
    Else
        MesINI = Month(FechReti)
    End If
    
    QR = "SELECT folio_dtll FROM fnza_detalle_mensualidad WHERE run_alu='" & RunAlu & "' AND mes>='" & MesINI & "' AND agno='" & agno & "' AND estado_dtll<>'nula';"
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    
    If Not (RC.RecordCount > 0) Then
        Exit Function
    End If
    
    RC.MoveFirst
    While Not (RC.EOF)
        AnularDtll RC!folio_dtll, "reti", CNX
        ' rebajar detalle a 0,0,0,0,0,0
        RC.MoveNext
    Wend
    RC.Close
    Set RC = Nothing
    RetirarAlumno = True
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.RetirarAlumno", Err.Source
End Function

Function RetirarApoderado(ByVal RunApo As String, ByVal FechReti As Date, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim CMD As ADODB.Command
    Dim QR As String
    Dim MesINI As Integer
    Dim agno As Integer
    
        
    RetirarApoderado = False
    agno = Year(FechReti)
    
    If Day(FechReti) > 15 Then
        MesINI = Month(FechReti) + 1
    Else
        MesINI = Month(FechReti)
    End If
    
    QR = "SELECT folio_mens FROM fnza_mensualidad WHERE run_apo='" & RunApo & "' AND mes>='" & MesINI & "' AND agno='" & agno & "' AND estado_mens<>'nula';"
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    If Not (RC.RecordCount > 0) Then
        Exit Function
    End If
    
    RC.MoveFirst
    Set CMD = New ADODB.Command
    While Not (RC.EOF)
        AnularMens RC!folio_mens, "reti", CNX
        QR = "REPLACE fnza_apoderado_revisar(run_apo,folio_mens,forma_pago,fech_modificacion) VALUES ('" & RunApo & "','" & RC!folio_mens & "','sinalumno','" & Format(FechReti, "yyyy-mm-dd") & "') "
        CMD.CommandText = QR
        CMD.CommandType = adCmdText
        CMD.ActiveConnection = CNX
        CMD.Execute
        RC.MoveNext
    Wend
    Set CMD = Nothing
    RC.Close
    Set RC = Nothing
    RetirarApoderado = True
    Exit Function
Controlerror:
    Set RC = Nothing
    Set CMD = Nothing
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.RetirarApoderado", Err.Source
End Function

Function RegistrarOpeCupon(CodOpe As String, FCuponera As Long, Fcupon As Long, Conexion As ADODB.Connection)
    ' Registra Operaciones con cuponeras
    Conexion.Execute "Insert into fnza_log_cuponeras (folio_cuponera, folio_cupon, fecha_hora, usuario, ip, cod_operacion) values (" & FCuponera & ", " & Fcupon & ",'" & InvierteFecha(Date, "R") & " " & Time & "' , '" & Nom_com_usu_ses & "' , '" & PC_IP & "' ,'" & CodOpe & "')"
End Function

Function InsCupones(FolioCuponera As Long, NCupones As Integer, NCuponInicial As String, MesInicial As Integer, fecha_venc As Date, ValorCupon As Long) As Boolean
Dim NumeroCupon As String, NumeroCuponBase As String, MesCupon As Integer, VctoCupon
Dim UbicCupon As Integer, Ubicacion As String, Incremento As String, Fcupon As String
InsCupones = False
NumeroCuponBase = Left(NCuponInicial, Len(NCuponInicial) - 2)
Incremento = Right(NCuponInicial, 2)
MesCupon = MesInicial
VctoCupon = InvierteFecha(fecha_venc, "R")
' Trae la ubicación del cupon segun folio cuponera y mes
Ubicacion = DevuelveCampo("fnza_deta_cupones", "", "max(ubicacion)", "folio_cuponera = " & FolioCuponera & " and mes <= " & MesCupon & " and (ucase(estado) <>'NU' and ucase(estado) <> 'EL')", conn)
UbicCupon = IIf(Ubicacion = "", 1, val(Ubicacion) + 1)
For t = 1 To NCupones
    NumeroCupon = NumeroCuponBase & Incremento
    VctoCupon = InvierteFecha(CDate(Year(fecha_venc) & "-" & CStr(MesCupon) & "-" & Day(fecha_venc)), "R")
    If Not ExistReg("fnza_deta_cupones", "folio_cuponera = " & FolioCuponera & " and mes = " & MesCupon & " and estado ='PE'", conn) Then
        conn.Execute "insert into fnza_deta_cupones (numero, mes, vcto, valor, ubicacion, folio_cuponera) values ('" & NumeroCupon & "' , " & MesCupon & " , '" & VctoCupon & "' , " & ValorCupon & " , " & UbicCupon & " , " & FolioCuponera & ")"
        Fcupon = DevuelveCampo("fnza_deta_cupones", "", "folio_cupon", "folio_cuponera =" & FolioCuponera & " and numero ='" & NumeroCupon & "' and mes =" & MesCupon & " and vcto = '" & VctoCupon & "' and valor = " & ValorCupon & " and ubicacion = " & UbicCupon, conn)
        If Fcupon <> "" Then
            Call RegistrarOpeCupon("GEN", FolioCuponera, CLng(Fcupon), conn)
        End If
    Else
        'Comente la modificacion del cupón porque de se debe marcar como 'EL' y crear otro
        ' Modifica el valor del cupon
        'conn.Execute "update fnza_deta_cupones set valor = " & ValorCupon & " where folio_cuponera = " & FolioCuponera & " and mes = " & MesCupon & " and ucase(estado) = 'PE'"
        'Fcupon = devuelvecampo("fnza_deta_cupones", "", "folio_cupon", "folio_cuponera =" & FolioCuponera & " and mes =" & MesCupon & " and vcto ='" & VctoCupon & "' and valor =" & ValorCupon, conn)
        '    'MsgBox ("No pudo registrarse Creación de Cupón")
        'If Fcupon <> "" Then
        '    Call RegistrarOpeCupon("MOD", FolioCuponera, CLng(Fcupon), conn)
        'End If
    End If
    Incremento = largo(Trim(CStr(val(Incremento) + 1)), 2)
    MesCupon = MesCupon + 1
    UbicCupon = UbicCupon + 1
Next t
InsCupones = True
End Function

Function CuotaAlumnosApoFecha(ByVal RunApo As String, ByVal agno As Integer, ByVal mes As Integer, ByRef CNX As ADODB.Connection) As Variant
Dim errnum As Double
Dim RDB As String
Dim errdes As String
Dim errsou As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim ValAlumnos() As Variant
    Dim ValAlumnos_d() As Variant
    Dim swBeca As Boolean
    Dim PorcentajeTipoPago As Double
    Dim NumMesesDePago As Integer
    Dim Remanente As Double
    
    swBeca = False
    PorcentajeTipoPago = 0
    ReDim Preserve ValAlumnos(1 To 11, 1 To 1)
    ValAlumnos(1, 1) = ""   'Run Alumno
    ValAlumnos(2, 1) = 0    'Arancel Base
    ValAlumnos(3, 1) = 0    'Descuento Beca
    ValAlumnos(4, 1) = 0    'Descuento Hermano
    ValAlumnos(5, 1) = 0    'Descuento Ajuste Especial
    ValAlumnos(6, 1) = 0    'Descuento Convenio
    ValAlumnos(10, 1) = 0   'Descuento Tipo Pago
    ValAlumnos(7, 1) = 0    'Total a Pagar
    ValAlumnos(8, 1) = ""   'Codigo Convenio
    ValAlumnos(9, 1) = ""   'Codigo Colegio
    ValAlumnos(11, 1) = 0   'Total a Pagar decimal
    
    ReDim Preserve ValAlumnos_d(1 To 10, 1 To 1)
    ValAlumnos_d(1, 1) = ""   'Run Alumno
    ValAlumnos_d(2, 1) = 0    'Arancel Base
    ValAlumnos_d(3, 1) = 0    'Descuento Beca
    ValAlumnos_d(4, 1) = 0    'Descuento Hermano
    ValAlumnos_d(5, 1) = 0    'Descuento Ajuste Especial
    ValAlumnos_d(6, 1) = 0    'Descuento Convenio
    ValAlumnos_d(10, 1) = 0   'Descuento Tipo Pago
    ValAlumnos_d(7, 1) = 0    'Total a Pagar
    ValAlumnos_d(8, 1) = ""   'Codigo Convenio
    ValAlumnos_d(9, 1) = ""   'Codigo Colegio
    
    Set RC = New ADODB.Recordset
    RC.Open "SELECT RDB FROM establecimientos;", CNX
    RDB = Trim(RC!RDB)
    If Trim(RC!RDB) <> "9782-9" Then 'Sino es niño Lincoln College
        If Trim(RC!RDB) = "26476-8" Then
           ' QR = "SELECT alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format((Mes + 1), "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(Mes, "00") & _
           ' IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY alu.cole, alu.curso DESC, alu.fec_nacalu, alu.ci_alu"
            
            QR = "SELECT orden,num_gra,alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT m.num_gra,axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, IF(m.cod_ens = '996',1,IF(m.cod_ens = '010',2,IF(m.cod_ens = '110',3,4))) AS orden, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format((mes + 1), "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & _
            IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY orden DESC,num_gra DESC, alu.fec_nacalu, alu.ci_alu DESC"
        Else
            'QR = "SELECT alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(Mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(Mes, "00") & _
            'IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY alu.cole, alu.curso DESC, alu.fec_nacalu, alu.ci_alu"
            
            QR = "SELECT orden,num_gra,alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT m.num_gra,axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, IF(m.cod_ens = '996',1,IF(m.cod_ens = '010',2,IF(m.cod_ens = '110',3,4))) AS orden, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & _
            IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY orden desc,num_gra desc, alu.fec_nacalu asc, alu.ci_alu desc"
        End If
    Else
            'QR = "SELECT alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(Mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(Mes, "00") & _
            'IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY alu.cole, alu.curso DESC, alu.fec_nacalu, alu.ci_alu"
            
            QR = "SELECT orden,num_gra,alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT m.num_gra,axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, IF(m.cod_ens = '996',1,IF(m.cod_ens = '010',2,IF(m.cod_ens = '110',3,4))) AS orden, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & _
            IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY orden DESC,num_gra DESC, alu.fec_nacalu, alu.ci_alu DESC"
    End If
    RC.Close
    
    RC.Open QR, CNX
        If RC.RecordCount > 0 Then
            RC.MoveFirst
            For i = 1 To RC.RecordCount
                ReDim Preserve ValAlumnos(1 To 11, 1 To i)
                'Run Alumno
                ValAlumnos(1, i) = RC!ci_alu
                'Arancel Base
                ValAlumnos(2, i) = RC!arancel_base
                'Descuento Beca
                If RC!Ind_Beca = 1 Then
                    ValAlumnos(3, i) = RC!arancel_base
                    swBeca = True
                ElseIf RC!Ind_MBeca = 1 Then
                    ValAlumnos(3, i) = (RC!arancel_base / 2)
                    swBeca = True
                Else
                    ValAlumnos(3, i) = 0
                End If
            
                
                'Descuento Hermano
'                If I > 1 And Not (swBeca) And Not (RC!cole = "LIN001AJM") Then
'                    ValAlumnos(4, I) = Int((RC!arancel_base * (RC!hermanos / 100))) ' + 0.5)
'                    'Validacion de Año
'                    If agno = 2005 Then
'                        ValAlumnos(4, I) = ValAlumnos(4, I) - (ValAlumnos(4, I) Mod 100)
'                    Else
'                        If ValidaHermanos(RunApo, RC!ci_alu, agno, mes, CNX) = True Then
'                            ValAlumnos(4, I) = ValAlumnos(4, I) ' - (ValAlumnos(4, i) Mod 50)
'                        Else
'                            ValAlumnos(4, I) = 0
'                        End If
'                    End If
'                Else
                    If i > 1 And Not (swBeca) And Not (RC!cole = "LIN001AJM") Then
                        If i = 2 Then
                            ValAlumnos(4, i) = Int(((RC!arancel_base * (RC!hermanos / 100))) + 0.5)
                        Else
                            If RC!cole = "HUE001" Then
                                ValAlumnos(4, i) = Int(((RC!arancel_base * (RC!hermanos / 100))) + 0.5)
                            Else
                                ValAlumnos(4, i) = Int(((RC!arancel_base * (7 / 100))) + 0.5)
                            End If
                        End If
                        'Validacion de Año
                        If agno = 2005 Then
                            ValAlumnos(4, i) = ValAlumnos(4, i) - (ValAlumnos(4, i) Mod 100)
                        Else
                            If ValidaHermanos(RunApo, RC!ci_alu, agno, mes, CNX) = True Then
                                ValAlumnos(4, i) = ValAlumnos(4, i) ' - (ValAlumnos(4, i) Mod 50)
                            Else
                                ValAlumnos(4, i) = 0
                            End If
                        End If
                    Else
                        ValAlumnos(4, i) = 0
                    End If
                'End If
                'Descuento Ajuste Especial
                ValAlumnos(5, i) = 0
                'Descuento Convenio
                If Not (UCase(RC!cod_conv) = "N") And Not (swBeca) And Not (val(UCase(RC!cod_conv)) = 4) Then
                    
                    ValAlumnos(6, i) = Int(((RC!arancel_base - ValAlumnos(4, i)) * (RC!Convenio1 / 100))) ' + 0.5)
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos(6, i) = ValAlumnos(6, i) ' - (ValAlumnos(6, i) Mod 100)
                    Else
                        ValAlumnos(6, i) = ValAlumnos(6, i) ' - (ValAlumnos(6, i) Mod 50)
                    End If
                Else
                    If Not (val(UCase(RC!cod_conv)) = 4) Then
                        ValAlumnos(6, i) = 0
                    End If
                End If
                'Descuento Tipo Pago
                If Not (swBeca) Then
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos(10, i) = 0
                    Else
                        If (Year(RC!fec_mat) < agno) Or (Year(RC!fec_mat) = agno And Month(RC!fec_mat) <= 2) Then
                            NumMesesDePago = 10
                        Else
                            If Day(RC!fec_mat) > 15 Then
                                NumMesesDePago = 12 - IIf((Month(RC!fec_mat) < 2), 2, Month(RC!fec_mat))
                            Else
                                NumMesesDePago = 13 - IIf((Month(RC!fec_mat) < 2), 2, Month(RC!fec_mat))
                            End If
                        End If
                        Remanente = 0
                        If RC!Ind_Cont = 1 Then
                            'Porcentaje de Descuento contado
                            If RDB <> "9782-9" Then
                                PorcentajeTipoPago = 60 + (60 / NumMesesDePago) '6% anual
                            Else
                                PorcentajeTipoPago = 60 '+ (120 / NumMesesDePago) '6% anual
                            End If
                        ElseIf RC!Ind_Cheq = 1 Then
                            'Porcentaje de Descuento cheque
                            If RDB <> "9782-9" Then
                                PorcentajeTipoPago = 0 ' 50 + (50 / NumMesesDePago) '0% anual
                            Else
                                PorcentajeTipoPago = 0 ' 50 '+ (50 / NumMesesDePago) '0% anual
                            End If
                        Else
                            'Sin descuento.
                            PorcentajeTipoPago = 0
                        End If
                        ValAlumnos(10, i) = ((RC!arancel_base - ValAlumnos(4, i) - ValAlumnos(6, i)) * (PorcentajeTipoPago / 1000))
                        Remanente = ValAlumnos(10, i) - Int(ValAlumnos(10, i))
                        ValAlumnos(10, i) = ValAlumnos(10, i) - Remanente
                        If mes = 12 Then
                            ValAlumnos(10, i) = Round(ValAlumnos(10, i) + ((Remanente * NumMesesDePago) \ 1), 0)
                        End If
                    End If
                Else
                    ValAlumnos(10, i) = 0
                End If
                'Descuento caja
                If val(UCase(RC!cod_conv)) = 4 And Not (swBeca) Then
                    ValAlumnos(6, i) = 0 'Int(((RC!arancel_base) * (20 / 100))) ' + 0.5)
                End If
                'Total a Pagar
                ValAlumnos(7, i) = ValAlumnos(2, i) - ValAlumnos(3, i) - ValAlumnos(4, i) - ValAlumnos(5, i) - ValAlumnos(6, i) - ValAlumnos(10, i)
                'Codigo del convenio
                ValAlumnos(8, i) = RC!cod_conv
                'Colegio
                ValAlumnos(9, i) = RC!cole
                RC.MoveNext
                swBeca = False
            Next i
        End If
    RC.Close
    
    
    QR = "SELECT orden,num_gra,alu.*, fd.convenio1, fd.hermanos, fd.arancel_base_d FROM fnza_descuento as fd, (SELECT m.num_gra,axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, IF(m.cod_ens = '996',1,IF(m.cod_ens = '010',2,IF(m.cod_ens = '110',3,4))) AS orden, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & _
            IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY orden DESC,num_gra DESC, alu.fec_nacalu, alu.ci_alu DESC"
    
    '''' Decimal valores
    RC.Open QR, CNX
        If RC.RecordCount > 0 Then
            RC.MoveFirst
            For i = 1 To RC.RecordCount
                ReDim Preserve ValAlumnos_d(1 To 10, 1 To i)
                'Run Alumno
                ValAlumnos_d(1, i) = RC!ci_alu
                'Arancel Base
                ValAlumnos_d(2, i) = RC!arancel_base_d
                'Descuento Beca
                If RC!Ind_Beca = 1 Then
                    ValAlumnos_d(3, i) = RC!arancel_base_d
                    swBeca = True
                ElseIf RC!Ind_MBeca = 1 Then
                    ValAlumnos_d(3, i) = (RC!arancel_base_d / 2)
                    swBeca = True
                Else
                    ValAlumnos_d(3, i) = 0
                End If
            
                
                'Descuento Hermano
'                If I = 1 And Not (swBeca) And Not (RC!cole = "LIN001AJM") And Not (val(UCase(RC!cod_conv)) = 4) Then
'                    ValAlumnos_d(4, I) = (RC!arancel_base_d * (RC!hermanos / 100)) ' + 0.5)
'                    'Validacion de Año
'                    If agno = 2005 Then
'                        ValAlumnos_d(4, I) = ValAlumnos_d(4, I) - (ValAlumnos_d(4, I) Mod 100)
'                    Else
'                        If ValidaHermanos(RunApo, RC!ci_alu, agno, mes, CNX) = True Then
'                            ValAlumnos_d(4, I) = ValAlumnos_d(4, I) ' - (ValAlumnos(4, i) Mod 50)
'                        Else
'                            ValAlumnos_d(4, I) = 0
'                        End If
'                    End If
'                Else
                    If i > 2 And Not (swBeca) And Not (RC!cole = "LIN001AJM") And Not (val(UCase(RC!cod_conv)) = 4) Then
                        If i = 2 Then
                            ValAlumnos_d(4, i) = ((RC!arancel_base_d * (RC!hermanos / 100)) + 0.5)
                        Else
                            ValAlumnos_d(4, i) = ((RC!arancel_base_d * (7 / 100)) + 0.5)
                        End If
                        'Validacion de Año
                        If agno = 2005 Then
                            ValAlumnos_d(4, i) = ValAlumnos_d(4, i) - (ValAlumnos_d(4, i) Mod 100)
                        Else
                            If ValidaHermanos(RunApo, RC!ci_alu, agno, mes, CNX) = True Then
                                ValAlumnos_d(4, i) = ValAlumnos_d(4, i) ' - (ValAlumnos(4, i) Mod 50)
                            Else
                                ValAlumnos_d(4, i) = 0
                            End If
                        End If
                    
                    Else
                        ValAlumnos_d(4, i) = 0
                    End If
                ' End If
                'Descuento Ajuste Especial
                ValAlumnos_d(5, i) = 0
                'Descuento Convenio
                If Not (UCase(RC!cod_conv) = "N") And Not (swBeca) And Not (val(UCase(RC!cod_conv)) = 4) Then
                    
                    ValAlumnos_d(6, i) = ((RC!arancel_base_d - ValAlumnos_d(4, i)) * (RC!Convenio1 / 100)) ' + 0.5)
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos_d(6, i) = ValAlumnos_d(6, i) ' - (ValAlumnos(6, i) Mod 100)
                    Else
                        ValAlumnos_d(6, i) = ValAlumnos_d(6, i) ' - (ValAlumnos(6, i) Mod 50)
                    End If
                Else
                    If Not (val(UCase(RC!cod_conv)) = 4) Then
                        ValAlumnos_d(6, i) = 0
                    End If
                End If
                'Descuento Tipo Pago
                If Not (swBeca) And Not (val(UCase(RC!cod_conv)) = 4) Then
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos_d(10, i) = 0
                    Else
                        If (Year(RC!fec_mat) < agno) Or (Year(RC!fec_mat) = agno And Month(RC!fec_mat) <= 2) Then
                            NumMesesDePago = 10
                        Else
                            If Day(RC!fec_mat) > 15 Then
                                NumMesesDePago = 12 - IIf((Month(RC!fec_mat) < 2), 2, Month(RC!fec_mat))
                            Else
                                NumMesesDePago = 13 - IIf((Month(RC!fec_mat) < 2), 2, Month(RC!fec_mat))
                            End If
                        End If
                        Remanente = 0
                        If RC!Ind_Cont = 1 Then
                            'Porcentaje de Descuento contado
                            If RDB <> "9782-9" Then
                                PorcentajeTipoPago = 60 + (60 / NumMesesDePago) '6% anual
                            Else
                                PorcentajeTipoPago = 60 '+ (120 / NumMesesDePago) '6% anual
                            End If
                        ElseIf RC!Ind_Cheq = 1 Then
                            'Porcentaje de Descuento cheque
                            If RDB <> "9782-9" Then
                                PorcentajeTipoPago = 0 '50 + (50 / NumMesesDePago) '0% anual
                            Else
                                PorcentajeTipoPago = 0 '50 '+ (50 / NumMesesDePago) '0% anual
                            End If
                        Else
                            'Sin descuento.
                            PorcentajeTipoPago = 0
                        End If
                        ValAlumnos_d(10, i) = ((RC!arancel_base_d - ValAlumnos_d(4, i) - ValAlumnos_d(6, i)) * (PorcentajeTipoPago / 1000))
                        Remanente = ValAlumnos_d(10, i) - Int(ValAlumnos_d(10, i))
                        ValAlumnos_d(10, i) = ValAlumnos_d(10, i) - Remanente
                        If mes = 12 Then
                            ValAlumnos_d(10, i) = Round(ValAlumnos_d(10, i) + ((Remanente * NumMesesDePago) \ 1), 0)
                        End If
                    End If
                Else
                    ValAlumnos_d(10, i) = 0
                End If
                'Descuento caja
                If val(UCase(RC!cod_conv)) = 4 And Not (swBeca) Then
                    ValAlumnos_d(6, i) = ((RC!arancel_base_d) * (20 / 100)) ' + 0.5)
                End If
                'Total a Pagar
                ValAlumnos(11, i) = ValAlumnos_d(2, i) - ValAlumnos_d(3, i) - ValAlumnos_d(4, i) - ValAlumnos_d(5, i) - ValAlumnos_d(6, i) - ValAlumnos_d(10, i)
                'Codigo del convenio
                ValAlumnos_d(8, i) = RC!cod_conv
                'Colegio
                ValAlumnos_d(9, i) = RC!cole
                RC.MoveNext
                swBeca = False
            Next i
        End If
    RC.Close
    
    
    CuotaAlumnosApoFecha = ValAlumnos
    Exit Function
Controlerror:
    errnum = Err.Number
    errdes = Err.Description
    errsou = Err.Source
    Err.Raise errnum, errsou & vbCrLf & "Mod_Funciones_Fnzas.CuotaAlumnosFecha", errdes
    Resume
End Function


Function CuotaApoFechaCuponeras(ByVal RunApo As String, ByVal agno As Integer, ByVal mes As Integer, ByRef CNX As ADODB.Connection) As Variant
Dim errnum As Double
Dim errdes As String
Dim errsou As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim ValAlumnos() As Variant
    Dim swBeca As Boolean
    Dim PorcentajeTipoPago As Long
    
    swBeca = False
    ReDim Preserve ValAlumnos(1 To 11, 1 To 1)
    ValAlumnos(1, 1) = ""
    ValAlumnos(2, 1) = 0
    ValAlumnos(3, 1) = 0
    ValAlumnos(4, 1) = 0
    ValAlumnos(5, 1) = 0
    ValAlumnos(6, 1) = 0
    ValAlumnos(7, 1) = 0
    ValAlumnos(8, 1) = ""
    ValAlumnos(9, 1) = ""
    ValAlumnos(10, 1) = 0
    ValAlumnos(11, 1) = 0
    
    QR = "SELECT DISTINCT reco.* FROM alu_x_cur axc, (" & _
         "SELECT alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cup, m.ind_cheq, m.ind_cont, m.ind_pac, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND m.anno_mat=axa.agno AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & "-" & IIf((agno > 2006), "01", "15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat " & _
         ") as reco " & _
         "WHERE axc.ci_alu=reco.ci_alu AND axc.cod_ens=reco.cod_ens AND axc.fec_ing_curso<='" & agno & "-" & Format(mes, "00") & "-15' " & _
         "ORDER BY reco.rut_apo, reco.cole, reco.curso DESC, reco.fec_nacalu, reco.ci_alu;"
    
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
        If RC.RecordCount > 0 Then
            RC.MoveFirst
            For i = 1 To RC.RecordCount
                ReDim Preserve ValAlumnos(1 To 11, 1 To i)
                ValAlumnos(1, i) = RC!ci_alu
                ValAlumnos(2, i) = RC!arancel_base
                If RC!Ind_Beca = 1 Then
                    ValAlumnos(3, i) = RC!arancel_base
                    swBeca = True
                ElseIf RC!Ind_MBeca = 1 Then
                    ValAlumnos(3, i) = (RC!arancel_base / 2)
                    swBeca = True
                Else
                    ValAlumnos(3, i) = 0
                End If
                If i > 1 And Not (swBeca) And Not (RC!cole = "LIN001AJM") Then
                    ValAlumnos(4, i) = Int((RC!arancel_base * (RC!hermanos / 100)) + 0.5)
                    If agno = 2005 Then
                        ValAlumnos(4, i) = ValAlumnos(4, i) - (ValAlumnos(4, i) Mod 100)
                    Else
                        If ValidaHermanos(RunApo, RC!ci_alu, agno, mes, CNX) = True Then
                            ValAlumnos(4, i) = ValAlumnos(4, i) - (ValAlumnos(4, i) Mod 50)
                        Else
                            ValAlumnos(4, i) = 0
                        End If
                    End If
                Else
                    ValAlumnos(4, i) = 0
                End If
                ValAlumnos(5, i) = 0
                If Not (UCase(RC!cod_conv) = "N") And Not (swBeca) Then
                    ValAlumnos(6, i) = Int(((RC!arancel_base - ValAlumnos(4, i)) * (RC!Convenio1 / 100)) + 0.5)
                    If agno = 2005 Then
                        ValAlumnos(6, i) = ValAlumnos(6, i) - (ValAlumnos(6, i) Mod 100)
                    Else
                        ValAlumnos(6, i) = ValAlumnos(6, i) - (ValAlumnos(6, i) Mod 50)
                    End If
                Else
                    ValAlumnos(6, i) = 0
                End If
                'Descuento Tipo Pago
                If Not (swBeca) Then
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos(10, i) = 0
                    Else
                        If RC!Ind_Cont = 1 Then
                            'Porcentaje de Descuento contado
                            PorcentajeTipoPago = 132
                        ElseIf RC!Ind_Cheq = 1 Then
                            'Porcentaje de Descuento cheque
                            PorcentajeTipoPago = 55
                        Else
                            'Sin descuento.
                            PorcentajeTipoPago = 0
                        End If
                        ValAlumnos(11, i) = ((RC!arancel_base - ValAlumnos(4, i) - ValAlumnos(6, i)) * (PorcentajeTipoPago / 1000)) \ 1
                    End If
                Else
                    ValAlumnos(11, i) = 0
                End If
                ValAlumnos(7, i) = ValAlumnos(2, i) - ValAlumnos(3, i) - ValAlumnos(4, i) - ValAlumnos(5, i) - ValAlumnos(6, i) - ValAlumnos(11, i)
                ValAlumnos(8, i) = RC!cod_conv
                ValAlumnos(9, i) = RC!cole
                ValAlumnos(10, i) = RC!ind_cup
                RC.MoveNext
                swBeca = False
            Next i
        End If
    RC.Close
    CuotaApoFechaCuponeras = ValAlumnos
    Exit Function
Controlerror:
    errnum = Err.Number
    errdes = Err.Description
    errsou = Err.Source
    Err.Raise errnum, errsou & vbCrLf & "Mod_Funciones_Fnzas.CuotaAlumnosFecha", errdes
    'Resume
End Function

Function CuotaAlumnoFecha(ByVal RunAlu As String, ByVal agno As Integer, ByVal mes As Integer, ByRef CNX As ADODB.Connection) As Variant
Dim errnum As Double
Dim errdes As String
Dim errsou As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Res As Variant
    Dim RunApo As String
    Dim Repositorio As Variant
    Dim Resp(1 To 11) As Variant
    
    Resp(1) = ""
    Resp(2) = 0
    Resp(3) = 0
    Resp(4) = 0
    Resp(5) = 0
    Resp(6) = 0
    Resp(7) = 0
    Resp(8) = ""
    Resp(9) = ""
    Resp(10) = 0
    Resp(11) = 0
    '####################
    'MODIFICADO SEGUN AÑO OK
    QR = "SELECT rut_apo FROM apo_x_alu WHERE ci_alu='" & RunAlu & "' AND agno=" & agno & ";"
    '####################
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        RunApo = RC!rut_apo
        RC.Close
        Repositorio = CuotaAlumnosApoFecha(RunApo, agno, mes, CNX)
        For i = 1 To UBound(Repositorio, 2)
            If Repositorio(1, i) = RunAlu Then
                Resp(1) = Repositorio(1, i)
                Resp(2) = Repositorio(2, i)
                Resp(3) = Repositorio(3, i)
                Resp(4) = Repositorio(4, i)
                Resp(5) = Repositorio(5, i)
                Resp(6) = Repositorio(6, i)
                Resp(7) = Repositorio(7, i)
                Resp(8) = Repositorio(8, i)
                Resp(9) = Repositorio(9, i)
                Resp(10) = Repositorio(10, i)
                Resp(11) = Repositorio(11, i)
            End If
        Next i
        CuotaAlumnoFecha = Resp
    Else
        RC.Close
        Err.Raise vbObjectError + 750, "", "No se ha podido encontrar un apoderado para este alumno."
    End If
    Exit Function
Controlerror:
    errnum = Err.Number
    errdes = Err.Description
    errsou = Err.Source
    Err.Raise errnum, errsou & vbCrLf & "Mod_Funciones_Fnzas.CuotaAlumnosFecha", errdes
    Resume
End Function

Function MesCuotaCero(ByVal RunAlu As String, agno, ByRef CNX As ADODB.Connection) As Integer
Dim cole As String
On Error GoTo Controlerror:
    ' Funcion que señala si el alumno tiene cuotacero , para este año.
    Dim RC As ADODB.Recordset
    Dim QR As String
    'Dim Resp(1 To 2) As Variant
        
    cole = DevuelveCampo("cfg", "", "valor", "param = 'codbc'", conn)
        
    MesCuotaCero = 0
    Set RC = New ADODB.Recordset
    QR = "SELECT fec_mat FROM matriculas WHERE ci_alu='" & RunAlu & "' and anno_mat='" & agno & "' ORDER BY fec_mat LIMIT 1;"
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        If CDate(RC!fec_mat) < CDate(agno & "-03-01") Then
            MesCuotaCero = 2
        Else
            If Day(RC!fec_mat) < 16 Then
                MesCuotaCero = Month(RC!fec_mat) - 1
            Else
                If cole <> "HUE001" Then
                    MesCuotaCero = Month(RC!fec_mat)
                Else
                    MesCuotaCero = Month(RC!fec_mat) - 1
                End If
            End If
        End If
    End If
    RC.Close
    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.MesCuotaCero", Err.Description
    Resume
End Function

Function RecalculaCuota_Alu(ByVal RunAlu As String, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim rec As ADODB.Recordset
    Dim QRY As String
    Dim RunApo As String
    
    RecalculaCuota_Alu = False
    Set rec = New ADODB.Recordset
    QRY = "SELECT rut_apo FROM apo_x_alu WHERE ci_alu='" & RunAlu & "' AND agno=" & agno & ";"
    rec.Open QRY, CNX
    If Not (rec.RecordCount > 0) Then
        rec.Close
        Err.Raise vbObjectError + 751, "", "Alumno sin Apoderado"
    Else
        rec.MoveFirst
        RunApo = rec!rut_apo
        rec.Close
    End If
    
    '########################|
    '#
    RecalculaCuota_Alu = RecalculaCuota_Apo(RunApo, agno, CNX)
    '#
    '########################
    
    Exit Function
Controlerror:
    'Set RC = Nothing
    Err.Raise Err.Number, Err.Source & vbCrLf & "Form_proc_Cursos.RecalculaCuota_Alu", Err.Description
    Resume
End Function

Function RecalculaCuota_Apo(ByVal RunApo As String, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim rec As ADODB.Recordset
    Dim QRY As String
    Dim Mensu As Variant
    Dim Mensu_cuota00 As Variant
    Dim MensuAlu As Variant
    Dim FolioDtll As Double
    Dim CodConv As String
    Dim MesCC As Integer
    Dim fecha_matricula As Date
    Dim ci_nuevo As String
    
    RecalculaCuota_Apo = False
    Set rec = New ADODB.Recordset
    Set CMD = New ADODB.Command
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    
    '#############################
    '# Inicio Recalculo Cuota Cero
    QRY = "SELECT axa.ci_alu,fec_mat FROM apo_x_alu as axa, matriculas as m WHERE axa.rut_apo='" & RunApo & "' AND m.anno_mat='" & agno & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat;"
    
    rec.Open QRY, CNX
    
    If Not (rec.RecordCount > 0) Then
        
        rec.Close
        MsgBox "Apoderado " & RunApo & " sin Alumno(s)", vbOKOnly + vbCritical, "ALERTA DE CONSISTENCIA."
        Exit Function
    End If
    '' Para masivo cuponeras apoderados que conservan valor cuota 00
    fecha_matricula = rec("fec_mat")
    rec.MoveFirst
    'fecha_matricula = REC(fec_mat_r)
    'Cuota Cero
    
    For i = 1 To rec.RecordCount
        'Mes Cuota Cero
        MesCC = MesCuotaCero(rec!ci_alu, agno, CNX)
        'Mensualidad Cero
        MensuAlu = CuotaAlumnoFecha(rec!ci_alu, agno, (MesCC + 1), CNX)
        'Si no es vacia
        If MensuAlu(1) = "" Then Exit Function
        'Si no es alumno Lincoln
        If Not (MensuAlu(9) = "LIN001" Or MensuAlu(9) = "LIN001AJM") Then
                'Extraer folio de la cuota cero.
                FolioDtll = num_folio_dtll(rec!ci_alu, "ct00", MensuAlu(9), MesCC, agno, CNX)
                If FolioDtll = 0 Then
                    FolioDtll = crear_folio_dtll(rec!ci_alu, RunApo, "ct00", Left(MensuAlu(9), 6), MesCC, agno, CNX)
                End If
                Select Case MensuAlu(8)
                        Case "1": CodConv = "cara"
                        Case "2": CodConv = "inve"
                        Case "3": CodConv = "muni"
                        Case "4" Or "04": CodConv = "caja"
                        Case Else: CodConv = "none"
                End Select
                
                ' Cuota00 UF dejando matricula en valor del año anterior
                If Not (MensuAlu(9) = "ALT001" Or MensuAlu(9) = "HUE001" Or MensuAlu(9) = "ALT001MMC" Or MensuAlu(9) = "HUE001MMC") Then
                ' Cambio de pagos a quienes matricularon despues del 15 de marzo.
                  If Format(fecha_matricula, "YYYY-MM-DD") < "2021-02-10" Then
                        MensuAlu_cuota00 = CuotaAlumnoFecha_cuota00(rec!ci_alu, agno, (MesCC + 1), CNX)
                        'Fijar los montos de la cuota cero
                        If Not (ConvenioJardin(rec!ci_alu, agno, CNX)) Then
                            If Not fijar_montos_detalle(CNX, FolioDtll, MensuAlu_cuota00(2), MensuAlu_cuota00(11), MensuAlu_cuota00(4), MensuAlu_cuota00(3), MontoDescuentoDtll(FolioDtll, CNX), MensuAlu_cuota00(6), CodConv, 0) Then
                                ' UPS!
                                MsgBox "Error en ct00 " & rec!ci_alu, vbCritical, "ERROR"
                            End If
                        Else
                        If Not fijar_montos_detalle(CNX, FolioDtll, MensuAlu_cuota00(2), MensuAlu_cuota00(11), 0, 0, MontoDescuentoDtll(FolioDtll, CNX), MensuAlu_cuota00(2), "jard", 0) Then
                            ' UPS!
                            MsgBox "Error en ct00 convenio jardin " & rec!ci_alu, vbCritical, "ERROR"
                        End If
                        End If
                  Else
                         'Fijar los montos de la cuota cero
                        If Not (ConvenioJardin(rec!ci_alu, agno, CNX)) Then
                            If Not fijar_montos_detalle(CNX, FolioDtll, MensuAlu(2), MensuAlu(11), MensuAlu(4), MensuAlu(3), MontoDescuentoDtll(FolioDtll, CNX), MensuAlu(6), CodConv, 0) Then
                                ' UPS!
                                MsgBox "Error en ct00 " & rec!ci_alu, vbCritical, "ERROR"
                            End If
                        Else
                        If Not fijar_montos_detalle(CNX, FolioDtll, MensuAlu(2), MensuAlu(11), 0, 0, MontoDescuentoDtll(FolioDtll, CNX), MensuAlu(2), "jard", 0) Then
                            ' UPS!
                            MsgBox "Error en ct00 convenio jardin " & rec!ci_alu, vbCritical, "ERROR"
                        End If
                        End If
                  
                  End If
                Else
                    ci_nuevo = ""
                    ci_nuevo = DevuelveCampo("alu_x_cur", "", "ci_alu", "agno = " & (agno - 1) & " AND activo_alu = 'SI' AND ci_alu = '" & rec!ci_alu & "'", conn)
                    If ci_nuevo = "" Then
                    'Fijar los montos de la cuota cero
                    If Not (ConvenioJardin(rec!ci_alu, agno, CNX)) Then
                        If Not fijar_montos_detalle(CNX, FolioDtll, MensuAlu(2), MensuAlu(11), MensuAlu(4), MensuAlu(3), MontoDescuentoDtll(FolioDtll, CNX), MensuAlu(6), CodConv, 0) Then
                            ' UPS!
                            MsgBox "Error en ct00 " & rec!ci_alu, vbCritical, "ERROR"
                        End If
                    Else
                    If Not fijar_montos_detalle(CNX, FolioDtll, MensuAlu(2), MensuAlu(11), 0, 0, MontoDescuentoDtll(FolioDtll, CNX), MensuAlu(2), "jard", 0) Then
                        ' UPS!
                        MsgBox "Error en ct00 convenio jardin " & rec!ci_alu, vbCritical, "ERROR"
                    End If
                    End If
                    Else
                        'Fijar los montos de la cuota cero
                        MensuAlu_cuota00 = CuotaAlumnoFecha_cuota00(rec!ci_alu, agno, (MesCC + 1), CNX)
                        'Fijar los montos de la cuota cero
                        If Not (ConvenioJardin(rec!ci_alu, agno, CNX)) Then
                            If Not fijar_montos_detalle(CNX, FolioDtll, MensuAlu_cuota00(2), MensuAlu_cuota00(11), MensuAlu_cuota00(4), MensuAlu_cuota00(3), MontoDescuentoDtll(FolioDtll, CNX), MensuAlu_cuota00(6), CodConv, 0) Then
                                ' UPS!
                                MsgBox "Error en ct00 " & rec!ci_alu, vbCritical, "ERROR"
                            End If
                        Else
                        If Not fijar_montos_detalle(CNX, FolioDtll, MensuAlu_cuota00(2), MensuAlu_cuota00(11), 0, 0, MontoDescuentoDtll(FolioDtll, CNX), MensuAlu_cuota00(2), "jard", 0) Then
                            ' UPS!
                            MsgBox "Error en ct00 convenio jardin " & rec!ci_alu, vbCritical, "ERROR"
                        End If
                        End If
                    End If
                End If
                
                'If Not (fijar_montos_detalle(CNX, FolioDtll, MensuAlu(2), MensuAlu(4), MensuAlu(3), MontoDescuentoDtll(FolioDtll, CNX), MensuAlu(6), CodConv, 0)) Then
                    ' UPS!
                '    MsgBox "No se pudo crear detalle cuota 00."
                'End If
        End If
        rec.MoveNext
        
    Next i
    '# Fin Recalculo cuota Cero
    '##########################
    '################################
    '# Inicio Recalculo Mensualidades
    For mes = 3 To 12
        'Mensualidad
        Mensu = CuotaAlumnosApoFecha(RunApo, agno, mes, CNX)
        'Si no es Vacia
        If Not (Mensu(1, 1) = "") Then
            For i = 1 To UBound(Mensu, 2)
                FolioDtll = num_folio_dtll(Mensu(1, i), "esco", Mensu(9, i), mes, agno, CNX)
                If FolioDtll = 0 Then
                        FolioDtll = crear_folio_dtll(Mensu(1, i), RunApo, "esco", Left(Mensu(9, i), 6), mes, agno, CNX)
                End If
                'codigo de convenio
                Select Case Mensu(8, i)
                    Case "1": CodConv = "cara"
                    Case "2": CodConv = "inve"
                    Case "3": CodConv = "muni"
                    Case "4" Or "04": CodConv = "caja"
                    Case Else: CodConv = "none"
                End Select
                'Fijo los montos de la mensualidad
                If Not (fijar_montos_detalle(CNX, FolioDtll, Mensu(2, i), Mensu(11, i), Mensu(4, i), Mensu(3, i), MontoDescuentoDtll(FolioDtll, CNX), Mensu(6, i), CodConv, Mensu(10, i))) Then
                    ' UPS!
                    MsgBox "No se pudo crear detalle del mes " & Mesw
                End If
            Next i
        End If
    Next mes
    '# Fin Recalculo de Mensualidades
    '################################
    '##########################
    '# Inicio de ajustes varios
    
    rec.MoveFirst
    
    For i = 1 To rec.RecordCount
        QRY = "UPDATE fnza_detalle_mensualidad SET estado_dtll='erro' WHERE run_alu='" & rec!ci_alu & "' AND monto_total=0 AND agno=" & agno & ";"
        CMD.CommandText = QRY
        CMD.Execute
        rec.MoveNext
    Next i
    'Actualiza los montos mensualidad
    QRY = "UPDATE fnza_mensualidad as fm, (SELECT sum(monto_Abono) as abono, folio_mens FROM fnza_detalle_mensualidad GROUP BY folio_mens) as up SET fm.abono_mens=up.abono WHERE fm.folio_mens=up.folio_mens;"
    CMD.CommandText = QRY
    CMD.Execute
    QRY = "UPDATE fnza_mensualidad SET estado_mens='impg' WHERE abono_mens=0 AND monto_mens>0;"
    CMD.CommandText = QRY
    CMD.Execute
    QRY = "UPDATE fnza_mensualidad SET estado_mens='clda' WHERE monto_mens=abono_mens;"
    CMD.CommandText = QRY
    CMD.Execute
    QRY = "UPDATE fnza_mensualidad SET estado_mens='abon' WHERE monto_mens>abono_mens AND abono_mens>0;"
    CMD.CommandText = QRY
    CMD.Execute
    QRY = "UPDATE fnza_mensualidad SET estado_mens='cred' WHERE monto_mens<abono_mens;"
    CMD.CommandText = QRY
    CMD.Execute
    'fin actualiza montos mensualidad
    QRY = "DELETE FROM fnza_detalle_mensualidad WHERE estado_dtll='erro' AND monto_abono=0 AND monto_total=0;"
    CMD.CommandText = QRY
    CMD.Execute
    QRY = "DELETE FROM fnza_detalle_mensualidad WHERE concepto='ct00' AND colegio='LIN001' AND agno=" & agno & ";"
    CMD.CommandText = QRY
    CMD.Execute
    QRY = "DELETE fm.* FROM fnza_mensualidad as fm, (SELECT distinct fm.folio_mens FROM fnza_mensualidad as fm left join fnza_detalle_mensualidad fdm on fm.folio_mens=fdm.folio_mens WHERE fdm.folio_mens is null) as fd WHERE fm.folio_mens=fd.folio_mens;"
    CMD.CommandText = QRY
    CMD.Execute
    rec.Close
    RecalculaCuota_Apo = True
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, Err.Source & vbCrLf & "Form_proc_Cursos.RecalculaCuota_Apo", Err.Description
    Resume
End Function
Function RepararApoderadoFnza(ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    ' #############################################
    ' Reparación de relación de apo_x_alu en fnzas.
    ' #############################################
    Dim QR As String
    Dim RC_LISTA As ADODB.Recordset
    Dim RC_apoOK As ADODB.Recordset
    Dim RC_apoNO As ADODB.Recordset
    Dim RC_aluOK As ADODB.Recordset
    Dim RC_aluNO As ADODB.Recordset
    Dim RC_aluDtll As ADODB.Recordset
    Dim CMD_REPA As ADODB.Command
    Dim FolioMensOK As Long
    Dim FolioDtllOK As Long
    Dim RecalcApoNO As Boolean
    
    ' AQUI mato este proceso
    'MsgBox "Deshabilitado", vbInformation, "Proceso"
    Exit Function
    
    Set RC_LISTA = New ADODB.Recordset
    Set CMD_REPA = New ADODB.Command
    CMD_REPA.ActiveConnection = CNX
    CMD_REPA.CommandType = adCmdText
    RecalcApoNO = False
    
    QR = "SELECT DISTINCT fdm.run_alu, left(fdm.colegio,6) as colegio, axa.rut_apo, fm.run_apo, fm.agno FROM fnza_detalle_mensualidad as fdm, fnza_mensualidad as fm, apo_x_alu as axa WHERE fdm.folio_mens=fm.folio_mens AND fdm.run_alu=axa.ci_alu AND fm.run_apo<>axa.rut_apo AND fdm.agno=fm.agno AND axa.agno=fdm.agno;"
    
    RC_LISTA.Open QR, CNX
    If RC_LISTA.RecordCount > 0 Then
        RC_LISTA.MoveFirst
        'Set Cmd_Repa = New ADODB.Command
        Set RC_apoOK = New ADODB.Recordset
        Set RC_apoNO = New ADODB.Recordset
        Set RC_aluOK = New ADODB.Recordset
        Set RC_aluNO = New ADODB.Recordset
        Set RC_aluDtll = New ADODB.Recordset
        For i = 1 To RC_LISTA.RecordCount
            agno = RC_LISTA!agno
            RecalcApoNO = False
            QR = "SELECT * FROM apo_x_alu WHERE rut_apo='" & RC_LISTA!rut_apo & "' AND agno=" & RC_LISTA!agno & ";"
            
            RC_aluOK.Open QR, CNX
            QR = "SELECT * FROM apo_x_alu WHERE rut_apo='" & RC_LISTA!run_apo & "' AND agno=" & RC_LISTA!agno & ";"
            
            RC_aluNO.Open QR, CNX
            For mes = 2 To 12
                QR = "SELECT folio_mens,left(colegio,6) as colegio,run_apo,monto_mens,abono_mens,mes,agno FROM fnza_mensualidad WHERE run_apo='" & RC_LISTA!rut_apo & "' AND colegio='" & Left(RC_LISTA!colegio, 6) & "' AND mes=" & mes & " AND agno=" & agno & ";"
                RC_apoOK.Open QR, CNX
                QR = "SELECT folio_mens,left(colegio,6) as colegio,run_apo,monto_mens,abono_mens,mes,agno FROM fnza_mensualidad WHERE run_apo='" & RC_LISTA!run_apo & "' AND colegio='" & Left(RC_LISTA!colegio, 6) & "' AND mes=" & mes & " AND agno=" & agno & ";"
                RC_apoNO.Open QR, CNX
                
                ' ApoOk sin alumnos en el detalle, ApoNo sin alumnos en el sistema
                If (RC_apoOK.RecordCount = 0 And RC_aluNO.RecordCount = 0) Then
                    ' Reeemplazar el RUN del apoNO por el del apoOK.
                    QR = "UPDATE fnza_mensualidad SET run_apo='" & RC_LISTA!rut_apo & "' WHERE run_apo='" & RC_LISTA!run_apo & "' AND mes=" & mes & " AND agno=" & agno & ";"
                    CMD_REPA.CommandText = QR
                    CMD_REPA.Execute
                    ' Borrar el apoNO.
                    QR = "DELETE FROM fnza_mensualidad WHERE run_apo='" & RC_LISTA!run_apo & "' AND mes=" & mes & " AND agno=" & agno & ";"
                    CMD_REPA.CommandText = QR
                    CMD_REPA.Execute
                End If
                
                ' ApoOk con alumnos en el detalle, ApoNo sin alumnos en el sistema
                If (RC_apoOK.RecordCount > 0 And RC_aluNO.RecordCount = 0) And RC_apoNO.RecordCount > 0 Then
                    ' Mover los niños del apoNo al apoOK.
                    QR = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6),folio_mens=" & RC_apoOK!folio_mens & " WHERE folio_mens=" & RC_apoNO!folio_mens & ";"
                    CMD_REPA.CommandText = QR
                    CMD_REPA.Execute
                    ' Mover los abonos del Mes.
                    QR = "UPDATE fnza_mensualidad as fm , (SELECT folio_mens, sum(monto_tot_cuota) as monto_tot, sum(monto_abono) as monto_abo FROM fnza_detalle_mensualidad WHERE folio_mens=" & RC_apoOK!folio_mens & " GROUP BY folio_mens) as suma SET fm.monto_mens=suma.monto_tot, fm.abono_mens=suma.monto_abo WHERE fm.folio_mens=suma.folio_mens AND fm.folio_mens=" & RC_apoOK!folio_mens & ";"
                    CMD_REPA.CommandText = QR
                    CMD_REPA.Execute
                    ' Borrar el apoNO.
                    QR = "DELETE from fnza_mensualidad WHERE folio_mens=" & RC_apoNO!folio_mens & ";"
                    CMD_REPA.CommandText = QR
                    CMD_REPA.Execute
                End If
                
                ' ApoOk sin alumnos en el detalle, ApoNo con alumnos en el sistema
                If (RC_apoOK.RecordCount = 0 And RC_aluNO.RecordCount > 0) Then
                    RecalcApoNO = True
                    ' Revisar si corresponde mover algo para el mes.
                    QR = "SELECT folio_dtll,folio_mens,left(colegio,6) as colegio,mes,agno FROM fnza_detalle_mensualidad WHERE run_alu='" & RC_LISTA!run_alu & "' AND mes=" & mes & " AND agno=" & agno & ";"
                    RC_aluDtll.Open QR, CNX
                    If RC_aluDtll.RecordCount > 0 Then
                        RC_aluDtll.MoveFirst
                        ' Generar los detalles en blanco para el apoOK.
                        FolioMensOK = crear_folio_mens(RC_LISTA!rut_apo, Left(RC_aluDtll!colegio, 6), RC_aluDtll!mes, RC_aluDtll!agno, CNX)
                        While Not (RC_aluDtll.EOF)
                            ' Mover el niño que corresponde del apoNo al apoOK.
                            QR = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6),folio_mens=" & FolioMensOK & " WHERE folio_dtll=" & RC_aluDtll!folio_dtll & ";"
                            CMD_REPA.CommandText = QR
                            CMD_REPA.Execute
                            ' siguiente detalle
                            RC_aluDtll.MoveNext
                        Wend
                        ' Mover el total y los abonos del detalle a la mensualidad
                        QR = "UPDATE fnza_mensualidad as fm , (SELECT folio_mens, sum(monto_tot_cuota) as monto_tot, sum(monto_abono) as monto_abo FROM fnza_detalle_mensualidad WHERE folio_mens=" & FolioMensOK & " GROUP BY folio_mens) as suma SET fm.monto_mens=suma.monto_tot, fm.abono_mens=suma.monto_abo WHERE fm.folio_mens=suma.folio_mens AND fm.folio_mens=" & FolioMensOK & ";"
                        CMD_REPA.CommandText = QR
                        CMD_REPA.Execute
                        QR = "UPDATE fnza_mensualidad as fm , (SELECT folio_mens, sum(monto_tot_cuota) as monto_tot, sum(monto_abono) as monto_abo FROM fnza_detalle_mensualidad WHERE folio_mens=" & RC_apoNO!folio_mens & " GROUP BY folio_mens) as suma SET fm.monto_mens=suma.monto_tot, fm.abono_mens=suma.monto_abo WHERE fm.folio_mens=suma.folio_mens AND fm.folio_mens=" & RC_apoNO!folio_mens & ";"
                        CMD_REPA.CommandText = QR
                        CMD_REPA.Execute
                    End If
                    RC_aluDtll.Close
                End If
                
                ' ApoOk con alumnos en el detalle, ApoNo con alumnos en el sistema
                If (RC_apoOK.RecordCount > 0 And RC_aluNO.RecordCount > 0) Then
                    RecalcApoNO = True
                    ' Revisar si corresponde mover algo para el mes.
                    QR = "SELECT folio_dtll,folio_mens,left(colegio,6) as colegio,mes,agno FROM fnza_detalle_mensualidad WHERE run_alu='" & RC_LISTA!run_alu & "' AND mes=" & mes & " AND agno=" & agno & ";"
                    RC_aluDtll.Open QR, CNX
                    If RC_aluDtll.RecordCount > 0 Then
                        RC_aluDtll.MoveFirst
                        ' Generar los detalles en blanco para el apoOK.
                        FolioMensOK = num_folio_mens(RC_LISTA!rut_apo, Left(RC_aluDtll!colegio, 6), RC_aluDtll!mes, RC_aluDtll!agno, CNX)
                        While Not (RC_aluDtll.EOF)
                            ' Mover el niño que corresponde del apoNo al apoOK.
                            QR = "UPDATE fnza_detalle_mensualidad SET colegio = left(colegio,6),folio_mens=" & FolioMensOK & " WHERE folio_dtll=" & RC_aluDtll!folio_dtll & ";"
                            CMD_REPA.CommandText = QR
                            CMD_REPA.Execute
                            ' siguiente detalle
                            RC_aluDtll.MoveNext
                        Wend
                        ' Mover el total y los abonos del detalle a la mensualidad
                        QR = "UPDATE fnza_mensualidad as fm , (SELECT folio_mens, sum(monto_tot_cuota) as monto_tot, sum(monto_abono) as monto_abo FROM fnza_detalle_mensualidad WHERE folio_mens=" & FolioMensOK & " GROUP BY folio_mens) as suma SET fm.monto_mens=suma.monto_tot, fm.abono_mens=suma.monto_abo WHERE fm.folio_mens=suma.folio_mens AND fm.folio_mens=" & FolioMensOK & ";"
                        CMD_REPA.CommandText = QR
                        CMD_REPA.Execute
                        QR = "UPDATE fnza_mensualidad as fm , (SELECT folio_mens, sum(monto_tot_cuota) as monto_tot, sum(monto_abono) as monto_abo FROM fnza_detalle_mensualidad WHERE folio_mens=" & RC_apoNO!folio_mens & " GROUP BY folio_mens) as suma SET fm.monto_mens=suma.monto_tot, fm.abono_mens=suma.monto_abo WHERE fm.folio_mens=suma.folio_mens AND fm.folio_mens=" & RC_apoNO!folio_mens & ";"
                        CMD_REPA.CommandText = QR
                        CMD_REPA.Execute
                    End If
                    RC_aluDtll.Close
                End If
                
                RC_apoOK.Close
                RC_apoNO.Close
            Next mes
            RecalculaCuota_Apo RC_LISTA!rut_apo, agno, CNX
            If RecalcApoNO Then
                RecalculaCuota_Apo RC_LISTA!run_apo, agno, CNX
            End If
            
            RC_aluOK.Close
            RC_aluNO.Close
            RC_LISTA.MoveNext
        Next i
    End If
    RC_LISTA.Close
    ' fin de reproseso y reparacion de apoderados
    ' ###########################################
    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.RepararApoderadoFnza", Err.Description
    Resume
End Function

Function RecalculaCuota_Alu_Retiro(ByVal RunAlu As String, ByVal Fech_Ret As Date, CNX As ADODB.Connection, Optional ByVal AgnoProx As Integer = 0) As Boolean
On Error GoTo Controlerror:
    Dim CMD As ADODB.Command
    Dim rec As ADODB.Recordset
    Dim QRY As String
    Dim agno As Integer
    Dim MesINI As Integer
    
    RecalculaCuota_Alu_Retiro = False
    If AgnoProx = 0 Then
        agno = Year(Fech_Ret)
    Else
        agno = AgnoProx
    End If
    
    If AgnoProx = 0 Then
        If Day(Fech_Ret) < 16 Then
            MesINI = Month(Fech_Ret)
        Else
            MesINI = Month(Fech_Ret) + 1
        End If
    Else
        MesINI = 2
    End If
    
    Set rec = New ADODB.Recordset
    QRY = "SELECT folio_dtll,left(colegio,6) as colegio,mes,agno FROM fnza_detalle_mensualidad WHERE run_alu='" & RunAlu & "' AND mes>='" & MesINI & "' AND agno='" & agno & "';"
    rec.Open QRY, CNX
    'Si no hay tonz para que?
    If Not (rec.RecordCount > 0) Then Exit Function
    
    For i = 1 To rec.RecordCount
        If Not (fijar_montos_detalle(CNX, rec!folio_dtll, 0, 0, 0, 0, 0, 0, "none", 0)) Then
            MsgBox "No se pudo eliminar el detalle folio :" & rec!Folio & vbCrLf & vbCrLf & "Anotar el Folio e Informar.", vbOKOnly + vbCritical, "ALERTA DE ANOMALÍA"
        End If
        rec.MoveNext
    Next i
    
    Set CMD = New ADODB.Command
    CMD.ActiveConnection = CNX
    CMD.CommandType = adCmdText
    
    QRY = "UPDATE fnza_detalle_mensualidad SET estado_dtll='erro' WHERE run_alu='" & RunAlu & "' AND monto_total=0 AND agno=" & agno & ";"
    CMD.CommandText = QRY
    CMD.Execute
    
    QRY = "DELETE FROM fnza_detalle_mensualidad WHERE estado_dtll='erro' AND monto_abono=0 AND monto_total=0;"
    CMD.CommandText = QRY
    CMD.Execute
    
    QRY = "DELETE fm.* FROM fnza_mensualidad as fm, (SELECT distinct fm.folio_mens FROM fnza_mensualidad as fm left join fnza_detalle_mensualidad fdm on fm.folio_mens=fdm.folio_mens WHERE fdm.folio_mens is null) as fd WHERE fm.folio_mens=fd.folio_mens;"
    CMD.CommandText = QRY
    CMD.Execute
    
    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.RecalculaCuota_Alu_Retiro", Err.Description
    Resume
End Function

Function MontoDescuentoDtll(ByVal FolioDtll As Long, ByRef CNX As ADODB.Connection) As Long
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    
    MontoDescuentoDtll = 0
    
    QR = "SELECT monto_exen_desc FROM fnza_detalle_mensualidad WHERE folio_dtll=" & FolioDtll & ";"
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        MontoDescuentoDtll = RC!monto_exen_desc
    End If
    RC.Close
    Exit Function
Controlerror:
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.TraduceConcepto", Err.Description
End Function

Function CompruebaValoresParaMatricula(ByVal Codens As String, ByVal NumGra As String, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim rec As ADODB.Recordset
    Dim QRY As String
    Dim colegio As String
    
    CompruebaValoresParaMatricula = False
    Set rec = New ADODB.Recordset
    
    QRY = "SELECT left(cc.colegio,6) as colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd"
    rec.Open QRY, CNX
    If rec.RecordCount > 0 Then
        colegio = rec("colegio")
        rec.Close
        QRY = "SELECT * FROM fnza_descuento WHERE agno='" & agno & "' AND curso='" & Codens & NumGra & "' AND colegio='" & Left(colegio, 6) & "';"
        rec.Open QRY, CNX
        If rec.RecordCount > 0 Then
            CompruebaValoresParaMatricula = True
        End If
    End If
    rec.Close
    
    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.CompruebaValoresParaMatricula", Err.Description
End Function

Function ValidaRut(ByRef Run As String) As Boolean
On Error GoTo Controlerror:
    Dim largo As Integer
    Dim DigVer As String
    Dim ParEnt As String
    Dim suma As Long
    Dim n As Integer
    Dim digito As Integer
    
    ValidaRut = False
    Run = Replace(Trim(Run), ".", "")
    largo = Len(Run)
    DigVer = UCase(Right(Run, 1))
    ParEnt = Left(Run, largo - 2)
    n = Len(ParEnt)
    suma = 0
    For i = 0 To n - 1
        digito = val(Mid(ParEnt, n - i, 1))
        suma = suma + (digito * ((i Mod 6) + 2))
    Next i
    suma = suma Mod 11
    digito = 11 - suma
    If (digito = 11 And DigVer = "0") Or (digito = 10 And DigVer = "K") Or (digito = val(DigVer)) Then
        ValidaRut = True
    Else
        ValidaRut = False
    End If
    Run = Format(ParEnt, "00000000") & "-" & DigVer
    
    Exit Function
Controlerror:
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.ValidaRut", Err.Description
End Function

Function ValidaHermanos(ByVal RunApo As String, ByVal RunAlu As String, ByVal agno As Integer, ByVal mes As Integer, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim RCH As ADODB.Recordset
    Dim RCA As ADODB.Recordset
    Dim QRY As String
    
    ValidaHermanos = False
    Set RCA = New ADODB.Recordset
    
    QRY = "SELECT DISTINCT a.rut_apo, p.ci_alu, p.ape_pat_alu, p.ape_mat_alu, m.anno_mat, m.fec_mat, m.fec_retiro  FROM apoderados as a, apo_x_alu as axa, postulantes as p, matriculas as m WHERE axa.rut_apo=a.rut_apo AND axa.ci_alu=p.ci_alu AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat=" & agno & " and m.fec_mat<='" & agno & "-" & mes & "-15' and (m.fec_retiro='1111-11-11' or m.fec_retiro>'" & agno & "-" & mes & "-01') and axa.rut_apo='" & RunApo & "' and axa.ci_alu='" & RunAlu & "' ORDER BY a.rut_apo, p.ci_alu;"
    
    RCA.Open QRY, CNX
    If RCA.RecordCount > 0 Then
        RCA.MoveFirst
        Set RCH = New ADODB.Recordset
        QRY = "SELECT DISTINCT a.rut_apo, p.ci_alu, p.ape_pat_alu, p.ape_mat_alu, m.anno_mat, m.fec_mat, m.fec_retiro  FROM apoderados as a, apo_x_alu as axa, postulantes as p, matriculas as m WHERE axa.rut_apo=a.rut_apo AND axa.ci_alu=p.ci_alu AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat=" & agno & " and m.fec_mat<='" & agno & "-" & mes & "-15' and (m.fec_retiro='1111-11-11' or m.fec_retiro>'" & agno & "-" & mes & "-01') and axa.rut_apo='" & RunApo & "' and axa.ci_alu<>'" & RunAlu & "' ORDER BY a.rut_apo, p.ci_alu;"
        RCH.Open QRY, CNX
        If RCH.RecordCount > 0 Then
            RCH.MoveFirst
            For i = 1 To RCH.RecordCount
                If (RCA!ape_pat_alu = RCH!ape_pat_alu Or RCA!ape_mat_alu = RCH!ape_mat_alu) Then
                    ValidaHermanos = True
                End If
                RCH.MoveNext
            Next i
            'validamos si es hermano a la fuerza.
            If ValidaHermanos = False Then
                '#######################################
                RCH.Close
                QRY = "SELECT ind_hermano FROM postulantes WHERE ci_alu='" & RunAlu & "' AND ind_hermano='SI';"
                RCH.Open QRY, CNX
                If RCH.RecordCount = 1 Then
                    ValidaHermanos = True
                End If
                '#######################################
            End If
        End If
        RCH.Close
    End If
    RCA.Close
    Exit Function
Controlerror:
    Err.Raise Err.Number, "Mod_Funciones_Fnzas.ValidaHermanos->" & Err.Source, Err.Description
    'Resume
End Function

Function CrearArchBCI(ByVal Pk As Boolean, ByVal ELI As Boolean, ByVal ArchBCI As String, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim rec As ADODB.Recordset
    Dim QRY As String
    Dim Registro As String
    Dim Canc As Integer
    Dim RCV As ADODB.Recordset
    Dim colegio As String
    Dim nombre As String
    Dim CodConv As String
    Dim CtaCte As String
    Dim Base As String
    
    CrearArchBCI = False
    'datos de CFG
    colegio = RetornaCampo("CFG", "valor", "param='" & IIf(Pk, "CODPK", "CODBC") & "'", CNX)
    nombre = RetornaCampo("hist_cod_colegio", "nombre", "colegio='" & Left(colegio, 6) & "'", CNX)
    CodConv = RetornaCampo("CFG", "valor", "param='" & IIf(Pk, "CodConvBCIPK", "CodConvBCIBC") & "'", CNX)
    CtaCte = RetornaCampo("CFG", "valor", "param='" & IIf(Pk, "CtaCtePK", "CtaCteBC") & "'", CNX)
    Base = RetornaCampo("fnza_descuento", "avg(arancel_base) as base", "colegio='" & Left(colegio, 6) & "' AND agno=" & agno, CNX)
    
    If Not (ELI) Then
        If Base <> "" Then
            Registro = "1" & Format(Left("CONV BCI " & CodConv & " " & UCase(nombre), 37), "!@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@") & Format(CtaCte, "00000000") & "10" & "0" & Format(Base, "000000000") & "000" & Format(Date, "mmyy") & Format(1, "00") & "10" & Format(Year(Date) & "00", "00000000000")
        Else
            MsgBox "No exiten valorizaciones para el año " & agno
        End If
        Canc = FreeFile
        Open ArchBCI For Output As #Canc
            Print #Canc, Registro
        Close #Canc
    Else
        Canc = FreeFile
        Open ArchBCI For Output As #Canc
        Close #Canc
    End If
    CrearArchBCI = True
    Exit Function
Controlerror:
    Err.Raise Err.Number, "CrearArchBCI->" & Err.Source, Err.Description
End Function

Function AgregarRegistroArchivoGeneracion(ByVal Arch As String, ByVal RutApo As String, ByVal MontoCupon As Long, ByVal NumBoleta As String, ByVal FechaPago As Date, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim num_arch As Integer
    Dim Registro As String
    Dim RC As ADODB.Recordset
    Dim QR As String
    
    AgregarRegistroArchivoGeneracion = False
    If Len(RutApo) < 2 Then Exit Function
    
    num_arch = FreeFile
    ' Armar el registro de generacion.
    QR = "SELECT concat(ape1_apo,' ',ape2_apo,' ',noms_apo) as 'nombre' FROM apoderados WHERE rut_apo='" & RutApo & "';"
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    ' Formato: Cab.  Rut del Apoderado                                          Nombre                                                Monto del Cupon (Entero)  &  (decimales) Cant.Cup. Numero de Cupon                   Fecha de Venc.               Meses   Filler(14 espacios)
    Registro = "2" & Format(Left(RutApo, Len(RutApo) - 2), "0000000000") & Right(RutApo, 1) & Format(Left(RC!nombre, 20), "!@@@@@@@@@@@@@@@@@@@@") & Format(MontoCupon, "000000000") & "000" & "001" & Format(NumBoleta, "00000000000") & Format(FechaPago, "ddmmyy") & "00" & Format(" ", "!@@@@@@@@@@@@@@")
    RC.Close
    ' Escribo registro
    Open Arch For Append As #num_arch
        Print #num_arch, Registro
    Close #num_arch
    AgregarRegistroArchivoGeneracion = True
    Exit Function
Controlerror:
    Err.Raise Err.Number, "AgregarRegistroArchivoGeneracion->" & Err.Source, Err.Description
    Resume
End Function

Function AgregarRegistroArchivoEliminacion(ByVal Arch As String, ByVal Pk As Boolean, ByVal RutApo As String, ByVal NumBoleta, CNX As ADODB.Connection) As Boolean
    Dim num_arch As Integer
    Dim Registro As String
    Dim CtaCte As String
    
    AgregarRegistroArchivoEliminacion = False
    If Len(RutApo) < 2 Then Exit Function
    CtaCte = RetornaCampo("CFG", "valor", "param='" & IIf(Pk, "CtaCtePK", "CtaCteBC") & "'", CNX)
    ' Armado del Registro de Eliminacion.
    Registro = Format(CtaCte, "000000000000") & Format(" ", "!@") & Format(Left(RutApo, Len(RutApo) - 2), "0000000000") & Format(" ", "!@") & Right(RutApo, 1) & Format(" ", "!@") & Format(NumBoleta, "0000000000") & Format(" ", "!@") & Format(NumBoleta, "0000000000") & Format(" ", "!@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
    num_arch = FreeFile
    Open Arch For Append As #num_arch
        Print #num_arch, Registro
    Close #num_arch
    AgregarRegistroArchivoEliminacion = True
    Exit Function
Controlerror:
    Err.Raise Err.Number, "AgregarRegistroArchivoEliminacion->" & Err.Source, Err.Description
End Function

Function ConvenioJardin(ByRef RunAlu As String, ByVal agno As Integer, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim rec As ADODB.Recordset
    Dim QRY As String

    ConvenioJardin = False
    QRY = "SELECT run_alu FROM fnza_convenio_jardin WHERE run_alu='" & RunAlu & "' AND agno='" & agno & "';"
    Set rec = New ADODB.Recordset
    rec.Open QRY, CNX
    If rec.RecordCount > 0 Then
        ConvenioJardin = True
    End If

    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.ConvenioJardin", Err.Description
End Function

Function CorrigeValorNuevo(ByVal RunAlu As String, ByVal AgnoPost As Integer, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim rec As ADODB.Recordset
    Dim QRY As String
    Dim RCL As ADODB.Recordset
    Dim RCA As ADODB.Recordset
    Dim RunApo As String
    
    ' Día de caducidad del parche
    If Date > CDate("12-12-2005") Then Exit Function
    
    CorrigeValorNuevo = False
    ' solo si es matricula para el prox. año
    If (AgnoPost - agno) <> 1 Then Exit Function
    'If Date > CDate("2005-12-11") Then Exit Function
    
    Set rec = New ADODB.Recordset
    Set RCL = New ADODB.Recordset
    Set RCA = New ADODB.Recordset
    '
    rec.Open "SELECT RDB FROM establecimientos;", CNX
    If Trim(rec!RDB) = "9782-9" Then Exit Function 'Si es niño Lincoln College ¡FUERA!!
    rec.Close
    ' SELECT para saber el mayor de los hermanos nuevos del colegio
    QRY = "SELECT list.rut_apo, matr.*, p.fec_nacalu, ce.ord_ens FROM (SELECT axa.rut_apo, axa.ci_alu FROM  apo_x_alu as axa, (SELECT rut_apo FROM apo_x_alu WHERE ci_alu='" & RunAlu & "' AND agno=" & agno & ") as apo where axa.rut_apo=apo.rut_Apo and axa.agno=" & agno & ") as list, (SELECT new.ci_alu, new.cod_ens, new.num_gra FROM (SELECT * FROM matriculas WHERE anno_mat=" & AgnoPost & ") as new LEFT JOIN (SELECT * FROM matriculas WHERE anno_mat=" & agno & ") as old ON new.ci_alu=old.ci_alu  AND new.activo_alu=old.activo_alu WHERE old.ci_alu is null) as matr, postulantes as p, codigos_ensenanza as ce WHERE list.ci_alu=matr.ci_alu AND list.ci_alu=p.ci_alu AND matr.cod_ens=ce.cod_ens ORDER BY ce.ord_ens DESC, matr.num_gra DESC, p.fec_nacalu, matr.ci_alu"
    rec.Open QRY, CNX
    If rec.RecordCount > 0 Then
        RunApo = rec!rut_apo
        'Dejar en cero el descuento por hermanos al mayor de los "nuevos" si corresponde
        rec.MoveFirst
        If RunAlu = rec!ci_alu And rec.RecordCount = 1 Then
            QRY = "SELECT folio_dtll, run_alu, agno,mes, concepto, estado_dtll, monto_total, monto_exen_hno, monto_exen_bca, monto_exen_desc, monto_exen_conv, monto_exen_tipo_pago, cod_convenio FROM fnza_detalle_mensualidad WHERE run_alu='" & RunAlu & "' AND concepto='ct00' AND agno=" & AgnoPost & ";"
            RCA.Open QRY, CNX
            'If RCA.RecordCount = 0 Then Exit Function
            If RCA!monto_exen_hno > 0 Then
                QRY = "SELECT list.rut_apo, matr.*, p.fec_nacalu, ce.ord_ens FROM (SELECT axa.rut_apo, axa.ci_alu FROM  apo_x_alu as axa, (SELECT rut_apo FROM apo_x_alu WHERE ci_alu='" & RunAlu & "' AND agno=" & agno & ") as apo where axa.rut_apo=apo.rut_Apo AND axa.agno=" & agno & ") as list, (SELECT new.ci_alu, new.cod_ens, new.num_gra FROM (SELECT * FROM matriculas WHERE anno_mat=" & AgnoPost & ") as new LEFT JOIN (SELECT * FROM matriculas WHERE anno_mat=" & agno & ") as old ON new.ci_alu=old.ci_alu  AND new.activo_alu=old.activo_alu WHERE old.ci_alu is not null) as matr, postulantes as p, codigos_ensenanza as ce WHERE list.ci_alu=matr.ci_alu AND list.ci_alu=p.ci_alu AND matr.cod_ens=ce.cod_ens ORDER BY ce.ord_ens DESC, matr.num_gra DESC, p.fec_nacalu, matr.ci_alu"
                RCL.Open QRY, CNX
                QRY = "SELECT folio_dtll, run_alu, agno,mes, concepto, estado_dtll, monto_total, monto_exen_hno, monto_exen_bca, monto_exen_desc, monto_exen_conv, monto_exen_tipo_pago, cod_convenio FROM fnza_detalle_mensualidad WHERE run_alu='" & RCL!ci_alu & "' AND concepto='ct00' AND agno=" & AgnoPost & ";"
                RCL.Close
                RCL.Open QRY, CNX
                ' Le asigno el descuento al hermano mayor antiguo
                fijar_montos_detalle CNX, RCA!folio_dtll, RCA!Monto_Total, RCA!monto_exen_hno, RCA!monto_exen_bca, (RCA!monto_exen_desc - RCA!monto_exen_hno), RCA!Monto_Exen_Conv, RCA!cod_convenio, RCA!monto_exen_tipo_pago
                fijar_montos_detalle CNX, RCL!folio_dtll, RCL!Monto_Total, RCL!monto_exen_hno, RCL!monto_exen_bca, (RCL!monto_exen_desc + RCA!monto_exen_hno), RCL!Monto_Exen_Conv, RCL!cod_convenio, RCL!monto_exen_tipo_pago
                CorrigeValorNuevo = True
                RCL.Close
            End If
            RCA.Close
        ElseIf run_alu = rec!run_alu And rec.RecordCount > 1 Then
            'QRY = "SELECT fdm.* FROM apo_x_alu as axa, fnza_detalle_mensualidad as fdm, postulantes as p, matriculas as m WHERE axa.rut_apo='" & RunApo & "' AND fdm.agno=" & AgnoPost & " AND axa.ci_alu<>'" & RunAlu & "' AND fdm.run_alu=axa.ci_alu AND p.ci_alu=axa.ci_alu AND m.ci_alu=axa.ci_alu AND fdm.agno=m.anno_mat AND fdm.concepto='ct00' AND fdm.estado_dtll<>'clda' ORDER BY fdm.colegio, m.cod_ens DESC, m.num_gra DESC, p.fec_nacalu, axa.ci_alu;"
            QRY = "SELECT folio_dtll, run_alu, agno,mes, concepto, estado_dtll, monto_total, monto_exen_hno, monto_exen_bca, monto_exen_desc, monto_exen_conv, monto_exen_tipo_pago, cod_convenio FROM fnza_detalle_mensualidad WHERE run_alu='" & RunAlu & "' AND concepto='ct00' AND agno=" & AgnoPost & ";"
            RCA.Open QRY, CNX
            If RCA!monto_exen_hno = 0 Then
                For i = 1 To rec.RecordCount - 1
                    rec.MoveNext
                    QRY = "SELECT folio_dtll, run_alu, agno,mes, concepto, estado_dtll, monto_total, monto_exen_hno, monto_exen_bca, monto_exen_desc, monto_exen_conv, monto_exen_tipo_pago, cod_convenio FROM fnza_detalle_mensualidad WHERE run_alu='" & rec!ci_alu & "' AND concepto='ct00' AND agno=" & AgnoPost & ";"
                    RCL.Open QRY, CNX
                    If RCL!estado_dtll = "cred" Then
                        fijar_montos_detalle CNX, RCA!folio_dtll, RCA!Monto_Total, RCA!monto_exen_hno, RCA!monto_exen_bca, (RCA!monto_exen_desc + RCA!monto_exen_hno), RCA!Monto_Exen_Conv, RCA!cod_convenio, RCA!monto_exen_tipo_pago
                        fijar_montos_detalle CNX, RCL!folio_dtll, RCL!Monto_Total, RCL!monto_exen_hno, RCL!monto_exen_bca, (RCL!monto_exen_desc - RCA!monto_exen_hno), RCL!Monto_Exen_Conv, RCL!cod_convenio, RCL!monto_exen_tipo_pago
                        CorrigeValorNuevo = True
                    End If
                Next i
            End If
            RCA.Close
        ElseIf run_alu <> rec!run_alu And rec.RecordCount > 1 Then
            ' En este caso otro alumno nuevo ya pago completo...
            ' no debería hacer nada.
        Else
            ' Este caso se da si y solo si
            ' hay un error en el sistema.
        End If
    End If
    rec.Close
    
    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.CorrigeValorNuevo", Err.Description
    Resume
End Function

Public Function RetornaCampo(tabla As String, campos As String, Criterio As String, CNX As ADODB.Connection) As String
Dim QRY As String
On Error GoTo controlerr
' Esta funcion devuelve un campo por otro acompañado de un criterio de busqueda
Set RsFuncion = New ADODB.Recordset

QRY = "SELECT " & campos & " FROM " & tabla & " WHERE " & Criterio & " LIMIT 1;"
RsFuncion.Open QRY, CNX
If RsFuncion.RecordCount > 0 Then
    RsFuncion.MoveFirst
    RetornaCampo = IIf(Not IsNull(RsFuncion(0)), RsFuncion(0), "")
Else
    RetornaCampo = ""
End If
RsFuncion.Close
Set RsFuncion = Nothing
Exit Function

controlerr:
If Err.Number = -2147467259# Then
    RetornaCampo = ""
    MsgBox "E-FAIL en:" & vbCrLf & vbCrLf & QRY, vbExclamation, "Estado E-Fail"
Else
    MsgBox Err.Description & vbCrLf & vbCrLf & Err.Source, vbCritical, Err.Number
End If
Resume
End Function

Function CalculaTercio(ByVal agno As Long, ByRef CNX As ADODB.Connection) As Boolean
On Error GoTo CapErro:
    Dim Tercio As Long
    Dim Consumido As Long
    Dim Disponible As Long
    Dim QRY As String
    Dim rec As ADODB.Recordset
    Dim colegio As String
    
    CalculaTercio = False
    Consumido = 0
    Disponible = val(RetornaCampo("CFG", "valor", "param='Cola_Disp' AND agno='" & agno & "'", CNX))
    ' Si ya se asigno todo una vez, entonces, no volver a asignar
    If Disponible <= 0 Then Exit Function
    
    'borrar asignaciones anteriores.
    QRY = "UPDATE Matriculas SET tercio='NO', tercio_por=0 WHERE anno_mat='" & agno & "' and ind_beca=0 AND ind_mbeca=0;"
    CNX.Execute QRY
    QRY = "UPDATE Fnza_Detalle_Mensualidad SET tercio='NO' WHERE agno='" & agno & "' and monto_exen_bca=0;"
    CNX.Execute QRY


    Tercio = CLng("&H" & RetornaCampo("CFG", "valor", "param='CodLicencia' AND agno='" & agno & "'", CNX))
    QRY = "SELECT sum(monto_exen_hno + monto_exen_conv + monto_exen_bca) as asignado FROM fnza_detalle_mensualidad WHERE tercio='SI' AND agno=" & agno & " GROUP BY agno;"
    Set rec = New ADODB.Recordset
    rec.Open QRY, CNX
    If rec.RecordCount > 0 Then
        Consumido = rec!asignado
    End If
    rec.Close
    
    If (Tercio - Consumido) > 0 Then
        Disponible = Tercio - Consumido
        colegio = RetornaCampo("CFG", "valor", "param='CODBC'", CNX)
        If colegio = "LIN001" Then
            QRY = "SELECT fdm.run_alu, left(fdm.colegio,6) as colegio, fdm.agno, sum(fdm.monto_exen_hno + fdm.monto_exen_conv) as disponible, sum(fdm.monto_exen_desc) as ajustes, sum(fdm.monto_total ) as anualidad FROM fnza_detalle_mensualidad as fdm, alu_x_cur as ac, (SELECT DISTINCT D.RUN_ALU FROM (SELECT * FROM FNZA_DETALLE_MENSUALIDAD WHERE AGNO=" & agno & " AND CONCEPTO='esco' AND MES=3) AS D, (SELECT * FROM FNZA_DETALLE_MENSUALIDAD WHERE AGNO=" & agno & " AND CONCEPTO='esco' AND MES=12) AS F WHERE D.RUN_ALU=F.RUN_ALU AND D.AGNO=F.AGNO AND left(D.COLEGIO,6)=left(F.COLEGIO,6) AND D.MONTO_TOT_CUOTA=F.MONTO_TOT_CUOTA AND left(D.COLEGIO,6)='" & Left(colegio, 6) & "') AS d WHERE d.run_alu=fdm.run_alu AND ac.ci_alu=fdm.run_alu AND ac.agno=fdm.agno AND fdm.tercio='NO' AND fdm.agno=" & agno & " AND left(fdm.colegio,6)='" & Left(colegio, 6) & "' AND fdm.monto_exen_tipo_pago=0 AND ac.activo_alu='SI' AND ac.fec_mat<='" & agno & "-03-15' GROUP BY fdm.run_alu;"
        Else
            QRY = "SELECT fdm.run_alu, left(fdm.colegio,6) as colegio, fdm.agno, sum(fdm.monto_exen_hno + fdm.monto_exen_conv) as disponible, sum(fdm.monto_exen_desc) as ajustes, sum(fdm.monto_total ) as anualidad FROM fnza_detalle_mensualidad as fdm, alu_x_cur as ac, (SELECT DISTINCT D.RUN_ALU FROM (SELECT * FROM FNZA_DETALLE_MENSUALIDAD WHERE AGNO=" & agno & " AND CONCEPTO='ct00') AS D, (SELECT * FROM FNZA_DETALLE_MENSUALIDAD WHERE AGNO=" & agno & " AND CONCEPTO='esco' AND MES=12) AS F WHERE D.RUN_ALU=F.RUN_ALU AND D.AGNO=F.AGNO AND left(D.COLEGIO,6)=left(F.COLEGIO,6) AND D.MONTO_TOT_CUOTA=F.MONTO_TOT_CUOTA AND left(D.COLEGIO,6)='" & Left(colegio, 6) & "') AS d WHERE d.run_alu=fdm.run_alu AND ac.ci_alu=fdm.run_alu AND ac.agno=fdm.agno AND fdm.tercio='NO' AND fdm.agno=" & agno & " AND left(fdm.colegio,6)='" & Left(colegio, 6) & "' AND fdm.monto_exen_tipo_pago=0 AND ac.activo_alu='SI' AND ac.fec_mat<='" & agno & "-03-15' GROUP BY fdm.run_alu;"
        End If
        'QRY = "SELECT fdm.run_alu, fdm.colegio, fdm.agno, sum(fdm.monto_exen_hno + fdm.monto_exen_conv) as disponible, sum(fdm.monto_total ) as anualidad FROM fnza_detalle_mensualidad as fdm, alu_x_cur as ac WHERE ac.ci_alu=fdm.run_alu AND ac.agno=fdm.agno AND fdm.tercio='NO' AND fdm.agno=" & agno & " AND fdm.colegio='" & Colegio & "' AND fdm.monto_exen_tipo_pago=0 AND fdm.concepto<>'ct00' GROUP BY fdm.run_alu"
        'QRY = "SELECT run_alu, colegio, agno, sum(monto_exen_hno + monto_exen_conv) as disponible, sum(monto_total - monto_exen_desc) as anualidad FROM fnza_detalle_mensualidad as fdm, alu_x_Cur as ac WHERE tercio='NO' AND agno=" & agno & " AND colegio='" & Colegio & "' AND monto_exen_tipo_pago=0 GROUP BY run_alu"
        rec.Open QRY, CNX
        If rec.RecordCount > 0 Then
            rec.MoveFirst
            While (Not rec.EOF) And Disponible > 0
                If rec!Disponible > 0 And rec!ajustes = 0 Then
                    QRY = "UPDATE fnza_detalle_mensualidad SET TERCIO='SI' WHERE run_alu='" & rec!run_alu & "' AND agno=" & rec!agno & ";"
                    conn.Execute QRY
                    'QRY = "UPDATE matriculas SET TERCIO='SI', TERCIO_POR='" & Round(((Val(REC!Disponible) / Val(REC!anualidad)) - 0.005) * 100, 0) & "' WHERE ci_alu='" & REC!run_alu & "' AND anno_mat='" & REC!agno & "';"
                    QRY = "UPDATE matriculas SET TERCIO='SI', TERCIO_POR='" & Round(((val(rec!Disponible) / val(rec!anualidad))) * 100, 1) & "' WHERE ci_alu='" & rec!run_alu & "' AND anno_mat='" & rec!agno & "';"
                    conn.Execute QRY
                    Disponible = Disponible - rec!Disponible
                End If
                rec.MoveNext
            Wend
        End If
    End If
    QRY = "REPLACE into CFG(param,valor,descripcion,agno) Values('Cola_Disp','" & Disponible & "','Nº Disponibilidad de Cola','" & agno & "')"
    conn.Execute QRY
    
    CalculaTercio = True
    
    Exit Function
CapErro:
    MsgBox Err.Description & vbCrLf & vbCrLf & Err.Source, vbCritical, Err.Number
End Function

'################ EN PRUEBA ######################
Function CreaNTCR(ByVal FolioBlta As Long, ByVal FolioMvto As Long, ByVal monto As Long, ByVal RunApo As String, ByVal Glosa As Long, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim QRY As String
    Dim FolioNTCR As Long
    Dim FolioDtll As Long
    'Dim FolioMvto As String
    Dim NumMvto As Integer
    Dim NewNTCR As Boolean
    Dim RCB As ADODB.Recordset
    Dim RCM As ADODB.Recordset
    
    CreaNTCR = False
    Set RCB = New ADODB.Recordset
    Set RCM = New ADODB.Recordset
    
    'Reviso si la BLTA es valida para la NTCR
    If RetornaCampo("fnza_boletas", "estado_blta", "folio_blta=" & FolioBlta, CNX) <> "nula" Then
        'Reviso si el MVTO pertence a la BLTA
        If val(RetornaCampo("fnza_dtll_mvto", "folio_blta", "folio_mvto=" & FolioMvto, CNX)) = FolioBlta Then
            'Recupero la informacion de la BLTA
            QRY = "SELECT * FROM fnza_boletas WHERE folio_blta=" & FolioBlta
            RCB.Open QRY, CNX
            'Recupero Folio de NTCR no emitida para la BLTA
            NewNTCR = False
             FolioNTCR = val(RetornaCampo("fnza_ntcr", "folio_ntcr", "folio_fisico_ntcr=0 AND estado_ntcr='pend' AND run_apo = '" & RunApo & "' AND colegio = '" & Left(RCB!colegio, 6) & "'", CNX))
            'Si No Hay NTCR pendiente, creo una nuevo.
            If FolioNTCR = 0 Then
                'Mando a hacer NTCR
                NewNTCR = True
            Else
                'Contar numero de detalles para saber si se debe hacer otra NTCR
                NumMvto = val(RetornaCampo("fnza_detalle_ntcr", "count(folio_ntcr) as num_deta", "folio_ntcr=" & FolioNTCR, CNX))
                'Si # de MVTOs es mayor a 19 (20 MVTOs) entonces creo nueva NCTR
                If NumMvto > 19 Then
                    QRY = "UPDATE fnza_ntcr SET estado_ntcr='penf' WHERE folio_ntcr=" & FolioNTCR
                    CNX.Execute QRY
                    NewNTCR = True
                End If
            End If
            If NewNTCR Then
                'creo la nueva NTCR
                QRY = "INSERT fnza_ntcr(fecha_ingreso,usuario_ingreso,FECHA_ntcr,RUN_APO,FOLIO_FISICO_NTCR,COLEGIO,ESTADO_NTCR,MONTO_NTCR) " & _
                      "VALUES(NOW(),'" & Nom_com_usu_ses & "','1111-11-11','" & RunApo & "',0,'" & Left(RCB!colegio, 6) & "','pend',0);"
                CNX.Execute QRY
                'rescato el folio de la NTCR creada
                FolioNTCR = val(RetornaCampo("fnza_ntcr", "folio_ntcr", "folio_fisico_ntcr=0 AND run_apo='" & RunApo & "' AND estado_ntcr='pend'", CNX))
            End If
            FolioDtll = val(RetornaCampo("fnza_dtll_mvto", "folio_dtll", "folio_mvto=" & FolioMvto, CNX))
            QRY = "INSERT fnza_detalle_ntcr(folio_ntcr,blta_rel,cod_glosa,monto_pago,folio_dtll_rel) VALUES('" & FolioNTCR & "','" & FolioBlta & "','" & Glosa & "','" & monto & "','" & FolioDtll & "')"
            CNX.Execute QRY
            'actualizo los montos de la NTCR y el DTLL
            'NTCR
            QRY = "UPDATE fnza_ntcr SET monto_ntcr=monto_ntcr + " & monto & " WHERE folio_ntcr=" & FolioNTCR
            CNX.Execute QRY
            'DTLL
            abonar_detalle FolioDtll, (-1 * monto), Date, CNX
            'fin
            CreaNTCR = True
        Else
            'El movimiento no corresponde a la boleta.
            MsgBox "El movimiento [" & FolioMvto & "] no corresponde a la Boleta [" & FolioBlta & "].", vbCritical, "ALERTA DE ANÓMALIA"
        End If
    Else
        ' El folio no es boleta valida.
        MsgBox "El Folio [" & FolioBlta & "] no corresponde a una boleta válida.", vbCritical, "ALERTA DE ANOMALÍA"
    End If
    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.CreaNTCR", Err.Description
    Resume
End Function

'################ INCOMPLETA ######################
Function AgregarNTCRalLibro(ByVal FolioNTCR As Long, ByVal FolioFisico As Long, CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Dim VERIF As Boolean

    AgregarNTCRalLibro = False
    VERIF = IIf((val(RetornaCampo("fnza_boletas", "count(folio_blta) as nfolio", "folio_blta_fisico=" & FolioFisico & " AND tipo_doc='ntcr'", CNX)) > 0), False, True)
    If VERIF Then
        
    End If
    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.AgregarNTCRalLibro", Err.Description
End Function

'################ INCOMPLETA ######################
Function EsqueletoFuncion(CNX As ADODB.Connection) As Boolean
On Error GoTo Controlerror:
    Exit Function
Controlerror:
    Err.Raise Err.Number, Err.Source & vbCrLf & "Mod_Funciones_Fnzas.EsqueletoFuncion", Err.Description
End Function

Public Function verifica_deuda(ci_alu As String, ByVal college As String) As Boolean
    Dim var_sql As String
    Dim rut_apo As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim exento As Long
    Dim boston As String
    Dim lincoln As String
    Dim conecta As Boolean
    
    arr_deuda(1) = ""
    arr_deuda(2) = ""
    arr_deuda(3) = ""
    arr_deuda(4) = ""
    
    If (DevuelveCampo("establecimientos", "", "RDB", "1=1", conn) <> "9782-9") Then
        exento = 15000
    Else
        exento = 8756
    End If
    
    arr_deuda(2) = DevuelveCampo("establecimientos", "", "NOM_EST", "1=1", conn)
       
'    boston = 13500
'    lincoln = 8756

    verifica_deuda = False
    rut_apo = ""
    
    'Verifica deuda alumno años anteriores
    var_sql = "select a.rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,left(dm.colegio,6) as colegio,concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno and c.agno = axc.agno and dm.agno > 2017" _
            & " and dm.run_alu = '" & ci_alu & "' and dm.run_alu = p.ci_alu and axc.rut_apo = a.rut_apo" _
            & " and dm.agno < 2025 and dm.mes > 1 and (monto_tot_cuota - monto_abono) > 10000 " _
            & " group by a.rut_apo" _
            & " Having Sum(monto_tot_cuota - monto_abono) >= " & exento
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_deuda = True
        arr_deuda(3) = "alumno"
    End If
    objRS.Close
    
''''''    'Verifica deuda año actual
    var_sql = "select a.rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,left(dm.colegio,6) as colegio,concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno and c.agno = axc.agno" _
            & " and dm.run_alu = '" & ci_alu & "' and dm.run_alu = p.ci_alu and axc.rut_apo = a.rut_apo" _
            & " and dm.agno = 2025 and dm.mes > 1 and dm.mes <= 11 and (monto_tot_cuota - monto_abono) > 10000 " _
            & " group by a.rut_apo" _
            & " Having Sum(monto_tot_cuota - monto_abono) >= " & exento
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_deuda = True
        arr_deuda(3) = "alumno"
    End If
    objRS.Close
        
    'Obtengo apoderado
    var_sql = "select rut_apo from apo_x_alu where ci_alu = '" & ci_alu & "' order by agno desc limit 1"
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        rut_apo = objRS("rut_apo")
    End If
    objRS.Close
    
    If arr_deuda(3) <> "" Then
        Exit Function
    End If
    
    If rut_apo <> "" Then
    'Obtengo deuda del alumno
'    var_sql = "select rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,colegio,concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes" _
'            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc" _
'            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno and c.agno = axc.agno and dm.agno > 2006" _
'            & " and dm.agno < 2011 and rut_apo = '" & Rut_apo & "'" _
'            & " group by rut_apo" _
'            & " Having Sum(monto_tot_cuota - monto_abono) >= 13500"
' Obtengo deuda años anteriores
    var_sql = "select a.rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,left(dm.colegio,6) as colegio," _
            & "concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes," _
            & "concat(ape_pat_alu,' ',ape_pat_alu,' ',nom_alu) as alumno,concat(ape1_apo,' ',ape2_apo,' ',noms_apo) as apoderado" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno" _
            & " and c.agno = axc.agno and dm.agno > 2017 and a.rut_apo = '" & rut_apo & "' and dm.run_alu = p.ci_alu" _
            & " and dm.agno < 2025 and dm.mes > 1" _
            & " and axc.rut_apo = a.rut_apo AND (monto_tot_cuota - monto_abono) > 10000 " _
            & " group by a.rut_apo" _
            & " Having Sum(monto_tot_cuota - monto_abono) >= " & exento
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_deuda = True
        arr_deuda(3) = "apoderado"
    End If
    objRS.Close
''''''    ' Obtengo deuda del apoderado Actual
    var_sql = "select a.rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,left(dm.colegio,6) as colegio," _
            & "concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes," _
            & "concat(ape_pat_alu,' ',ape_pat_alu,' ',nom_alu) as alumno,concat(ape1_apo,' ',ape2_apo,' ',noms_apo) as apoderado" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno" _
            & " and c.agno = axc.agno and a.rut_apo = '" & rut_apo & "' and dm.run_alu = p.ci_alu" _
            & " and dm.agno = 2025 and dm.mes > 1 and dm.mes <= 11" _
            & " and axc.rut_apo = a.rut_apo AND (monto_tot_cuota - monto_abono) > 10000 " _
            & " group by a.rut_apo" _
            & " Having Sum(monto_tot_cuota - monto_abono) >= " & exento
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_deuda = True
        arr_deuda(3) = "apoderado"
    End If
    objRS.Close
    End If
        
    If arr_deuda(3) <> "" Then
        Exit Function
    End If
    'arr_deuda(2) = "LA FLO"
    
    If verifica_deuda = False Then
        conecta = ConectarBDSiempre(conn_deuda, "192.168.7.91", "comunes_gescol", "groot", "kripplefight", True)
        
        'Alumno
        var_sql = "SELECT * FROM `alumnos_deudores` where ci_alu = '" & ci_alu & "'"
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
            verifica_deuda = True
            arr_deuda(2) = objRS("colegio")
            arr_deuda(3) = "alumno"
        End If
        objRS.Close
        
        If arr_deuda(3) <> "" Then
            conn_deuda.Close
            Exit Function
        End If
        
        'Apoderado
        var_sql = "SELECT * FROM `apoderados_deudores` where ci_apo = '" & rut_apo & "'"
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
            verifica_deuda = True
            arr_deuda(2) = objRS("colegio")
            arr_deuda(3) = "apoderado"
        End If
        objRS.Close
        
        If arr_deuda(3) <> "" Then
            conn_deuda.Close
            Exit Function
        End If
        
        'Cheques
        var_sql = "SELECT * FROM apoderados_cheques_protestados where ci_apo = '" & rut_apo & "'"
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
            verifica_deuda = True
            arr_deuda(2) = objRS("colegio")
            arr_deuda(3) = "apoderado"
            arr_deuda(4) = "cheques protestados"
        End If
        objRS.Close
        
        'Titulares
        var_sql = "SELECT * FROM apoderados_cheques_protestados where ci_tit = '" & rut_apo & "'"
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
            verifica_deuda = True
            arr_deuda(2) = objRS("colegio")
            arr_deuda(3) = "titular"
            arr_deuda(4) = "cheques protestados"
        End If
        objRS.Close
        
        If arr_deuda(3) <> "" Then
            conn_deuda.Close
            Exit Function
        End If
        
        conn_deuda.Close
    End If
   
End Function

Public Function deuda_exage(ci_alu As String, colegio As String) As Boolean

    Dim rut_apo As String
    Dim i As Integer
    'Dim j As Integer
    'Dim colegios(3) As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql As String
    Dim mat_con_deuda As Integer
    deuda_exage = False
    
   
'    If colegio = "otro" Then Exit Function
'
'    'For j = 1 To 3
'    aaa = ConectarSQL(connSQL, "192.168.7.31", colegio, "sa", "Password1", True)
'
'
'    'var_sql = "select top 1 * from col_alu_x_curso"
'
'    If colegio = "pudahuel_2005" Then
'        var_sql = "select rut_apo from apo_x_alu where (agno = 2011 or agno = 2012) and ci_alu = '" & ci_alu & "' order by agno desc limit 1"
'        objRS.Open var_sql, conn
'        If Not objRS.EOF Then
'            rut_apo = objRS("rut_apo")
'        Else
'            Exit Function
'        End If
'        objRS.Close
'
'
'
'        var_sql = "select MIN(c.idcargo) as cargo,cc.IdCuentaCorriente,t.IdTitular,ROUND(sum(m.Importe),0) as Total," _
'                & "isnull((select ROUND(sum(valor),0) from fb_bonificacion bb,fb_movimientos mm,fb_cargos ccc" _
'                & " Where bb.idcargo = mm.idMovimiento And mm.idMovimiento = ccc.idcargo" _
'                & " and datepart(year,mm.Fecha)= 2009 and not concepto like '%UOTA%'" _
'                & " and mm.idcuentacorriente = cc.idcuentacorriente and ccc.Estado<>1 and ccc.idpadre is null" _
'                & " and (mm.Anulado is null or mm.Anulado=0)),0) as Bonificacion,ROUND(Sum(pc.importe), 0) As Abonado" _
'                & " From FB_Movimientos m,FB_CuentaCorriente cc, FB_Titulares t, DatosAdministrativos da, FB_Cargos c,FB_PagoCargo pc," _
'                & "FB_CargosTemplateXAlumno ctxa where da.nrodoc + '-' + da.DigVerDoc = '" & rut_apo & "' and m.Tipo='CA' and c.idCargo=pc.IdCargo and datepart(year,m.Fecha)= 2009" _
'                & " and m.idCuentaCorriente=cc.IdCuentaCorriente and cc.IdTitular=t.IdTitular and da.IdPersona=t.IdPersona" _
'                & " and m.idMovimiento=c.idCargo and c.idcargotemplatealumno = ctxa.idcargotemplatealumno and" _
'                & "(m.Anulado is null or m.Anulado=0) and c.Estado<>1 and c.idpadre is null and not concepto like '%UOTA%'" _
'                & " and da.NroDoc in (select ISNULL((select top 1 nrodoc from ApoderadosxAlumnos a,datosadministrativos da" _
'                & " where a.idapoderado = DA.idpersona and idalumno = mc.idalumno),'') as rut_apo" _
'                & " from datosadministrativos da,matriculasxcurso mc , dbo.TipoEnsenanzas te," _
'                & " dbo.TipoGrados tg, cursos c Where mc.idciclolectivo = 2009 and da.idpersona = mc.idalumno and mc.idcurso = c.idcurso" _
'                & " and c.idtipoens = te.idtipoens and c.idtipogrado = tg.idtipogrado and c.idcurso not in (select sf.idcurso" _
'                & " from situacionfinal sf Where sf.ididentificador = 4 And sf.IdAlumno = mc.IdAlumno" _
'                & ")) and ctxa.idalumnoctacte IN ((select idpersona from datosadministrativos da,matriculasxcurso mc , dbo.TipoEnsenanzas te," _
'                & "dbo.TipoGrados tg, cursos c Where mc.idciclolectivo = 2009 and da.idpersona = mc.idalumno and mc.idcurso = c.idcurso" _
'                & " and c.idtipoens = te.idtipoens and c.idtipogrado = tg.idtipogrado and c.idcurso not in (select sf.idcurso" _
'                & " from situacionfinal sf Where sf.ididentificador = 4 And sf.IdAlumno = mc.IdAlumno)))" _
'                & " and c.idcargo not in (select idcargo from fb_bonificacion where descripcion like '%contad%' or descripcion like '%chequ%')" _
'                & " Group By cc.IdCuentaCorriente,t.IdTitular,da.IdPersona,da.NroDoc,da.DigVerDoc,da.Nombres,da.ApellidoPaterno," _
'                & " da.ApellidoMaterno"
'        objRS.Open var_sql, connSQL
'        If Not objRS.EOF Then
'            If (objRS("abonado") + 5000) < (objRS("total") - objRS("bonificacion")) Then
'                deuda_exage = True
'            End If
'        End If
'        objRS.Close
'
'    Else
'    For i = 2007 To 2009
'        var_sql = "select d from" _
'                & " (select ob.ci_alu,(o-p) as d from" _
'                & " (select ci_alu,sum(monto_cargo - monto_abono) as o from fnza_obligaciones where agno = " & i _
'                & " and ci_alu = '" & ci_alu & "' group by ci_alu) as ob," _
'                & " (select ci_alu,sum(monto_abono - monto_cargo) as p from fnza_pago_obligaciones where agno = " & i _
'                & " and ci_alu = '" & ci_alu & "' group by ci_alu ) as po) as dd,col_alu_x_curso c" _
'                & " where dd.ci_alu = c.ci_alu and c.ci_alu = '" & ci_alu & "' and c.agno = " & i
'        objRS.Open var_sql, connSQL
'        If Not objRS.EOF Then
'            If objRS("d") > 0 Then
'                deuda_exage = True
'            End If
'        End If
'        objRS.Close
'    Next i
'    End If
'
'    connSQL.Close
    'Next j
End Function

Public Function verifica_deuda_particulares(ci_alu As String, ByVal college As String) As Boolean
    Dim var_sql As String
    Dim rut_apo As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim exento As Long
    Dim boston As String
    Dim lincoln As String
    Dim conecta As Boolean
    
    arr_deuda(1) = ""
    arr_deuda(2) = ""
    arr_deuda(3) = ""
    arr_deuda(4) = ""
    
'    If (DevuelveCampo("establecimientos", "", "RDB", "1=1", conn) <> "9782-9") Then
'        exento = 13500
'    Else
'        exento = 8756
'    End If
    
    exento = 90000
    
    arr_deuda(2) = DevuelveCampo("establecimientos", "", "NOM_EST", "1=1", conn)
       
'    boston = 13500
'    lincoln = 8756

    verifica_deuda_particulares = False
    rut_apo = ""
    
    'Verifica deuda alumno años anteriores
    var_sql = "select a.rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,left(dm.colegio,6) as colegio,concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno and c.agno = axc.agno and dm.agno > 2011" _
            & " and dm.run_alu = '" & ci_alu & "' and dm.run_alu = p.ci_alu and axc.rut_apo = a.rut_apo" _
            & " and dm.agno < 2025 and dm.mes >= 1" _
            & " group by a.rut_apo" _
            & " Having Sum(monto_tot_cuota - monto_abono) >= " & exento
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_deuda_particulares = True
        arr_deuda(3) = "alumno"
    End If
    objRS.Close
    
    'Verifica deuda año actual
    var_sql = "select a.rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,left(dm.colegio,6) as colegio,concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno and c.agno = axc.agno and dm.agno > 2024" _
            & " and dm.run_alu = '" & ci_alu & "' and dm.run_alu = p.ci_alu and axc.rut_apo = a.rut_apo" _
            & " and dm.agno < 2026 and dm.mes >=1 and dm.mes <= 10" _
            & " group by a.rut_apo" _
            & " Having Sum(monto_tot_cuota - monto_abono) >= 100000"
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_deuda_particulares = True
        arr_deuda(3) = "alumno"
    End If
    objRS.Close
           
    'Obtengo apoderado
    var_sql = "select rut_apo from apo_x_alu where ci_alu = '" & ci_alu & "' order by agno desc limit 1"
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        rut_apo = objRS("rut_apo")
    End If
    objRS.Close
    
    If arr_deuda(3) <> "" Then
        Exit Function
    End If
    
    If rut_apo <> "" Then
    'Obtengo deuda del alumno
'    var_sql = "select rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,colegio,concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes" _
'            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc" _
'            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno and c.agno = axc.agno and dm.agno > 2006" _
'            & " and dm.agno < 2011 and rut_apo = '" & Rut_apo & "'" _
'            & " group by rut_apo" _
'            & " Having Sum(monto_tot_cuota - monto_abono) >= 13500"

    ' Obtengo deuda del apoderado de años anteriores
    var_sql = "select a.rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,left(dm.colegio,6) as colegio," _
            & "concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes," _
            & "concat(ape_pat_alu,' ',ape_pat_alu,' ',nom_alu) as alumno,concat(ape1_apo,' ',ape2_apo,' ',noms_apo) as apoderado" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno" _
            & " and c.agno = axc.agno and dm.agno > 2011 and a.rut_apo = '" & rut_apo & "' and dm.run_alu = p.ci_alu" _
            & " and dm.agno < 2025 and dm.mes >=1" _
            & " and axc.rut_apo = a.rut_apo " _
            & " group by a.rut_apo" _
            & " Having Sum(monto_tot_cuota - monto_abono) >= " & exento
    
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_deuda_particulares = True
        arr_deuda(3) = "apoderado"
    End If
    objRS.Close
    
    ' Obtengo deuda del apoderado Actual
    var_sql = "select a.rut_apo,sum(monto_tot_cuota-monto_abono) as monto_total,1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,left(dm.colegio,6) as colegio," _
            & "concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes," _
            & "concat(ape_pat_alu,' ',ape_pat_alu,' ',nom_alu) as alumno,concat(ape1_apo,' ',ape2_apo,' ',noms_apo) as apoderado" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno" _
            & " and c.agno = axc.agno and dm.agno > 2024 and a.rut_apo = '" & rut_apo & "' and dm.run_alu = p.ci_alu" _
            & " and dm.agno < 2026 and dm.mes >= 1 and dm.mes <= 10" _
            & " and axc.rut_apo = a.rut_apo " _
            & " group by a.rut_apo" _
            & " Having Sum(monto_tot_cuota - monto_abono) >= " & exento

    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        verifica_deuda_particulares = True
        arr_deuda(3) = "apoderado"
    End If
    objRS.Close
      
    
    End If
    
    If arr_deuda(3) <> "" Then
        Exit Function
    End If
    'arr_deuda(2) = "LA FLO"
    
    If verifica_deuda_particulares = False Then
        conecta = ConectarBDSiempre(conn_deuda, "192.168.7.91", "comunes_gescol", "groot", "kripplefight", True)
        
        'Alumno
        var_sql = "SELECT * FROM `alumnos_deudores` where ci_alu = '" & ci_alu & "'"
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
            verifica_deuda_particulares = True
            arr_deuda(2) = objRS("colegio")
            arr_deuda(3) = "alumno"
        End If
        objRS.Close
        
        If arr_deuda(3) <> "" Then
            conn_deuda.Close
            Exit Function
        End If
        
        'Apoderado
        var_sql = "SELECT * FROM `apoderados_deudores` where ci_apo = '" & rut_apo & "'"
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
            verifica_deuda_particulares = True
            arr_deuda(2) = objRS("colegio")
            arr_deuda(3) = "apoderado"
        End If
        objRS.Close
        
        If arr_deuda(3) <> "" Then
            conn_deuda.Close
            Exit Function
        End If
        
        'Cheques
        var_sql = "SELECT * FROM apoderados_cheques_protestados where ci_apo = '" & rut_apo & "'"
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
            verifica_deuda_particulares = True
            arr_deuda(2) = objRS("colegio")
            arr_deuda(3) = "apoderado"
            arr_deuda(4) = "cheques protestados"
        End If
        objRS.Close
        
        'Titulares
        var_sql = "SELECT * FROM apoderados_cheques_protestados where ci_tit = '" & rut_apo & "'"
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
            verifica_deuda_particulares = True
            arr_deuda(2) = objRS("colegio")
            arr_deuda(3) = "titular"
            arr_deuda(4) = "cheques protestados"
        End If
        objRS.Close
        
        If arr_deuda(3) <> "" Then
            conn_deuda.Close
            Exit Function
        End If
        
        conn_deuda.Close
    End If
   
End Function



Function CuotaAlumnosApoFecha_cuota00(ByVal RunApo As String, ByVal agno As Integer, ByVal mes As Integer, ByRef CNX As ADODB.Connection) As Variant
Dim errnum As Double
Dim RDB As String
Dim errdes As String
Dim errsou As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim ValAlumnos() As Variant
    Dim ValAlumnos_d() As Variant
    Dim swBeca As Boolean
    Dim PorcentajeTipoPago As Integer
    Dim NumMesesDePago As Integer
    Dim Remanente As Double
    
    swBeca = False
    PorcentajeTipoPago = 0
    ReDim Preserve ValAlumnos(1 To 11, 1 To 1)
    ValAlumnos(1, 1) = ""   'Run Alumno
    ValAlumnos(2, 1) = 0    'Arancel Base
    ValAlumnos(3, 1) = 0    'Descuento Beca
    ValAlumnos(4, 1) = 0    'Descuento Hermano
    ValAlumnos(5, 1) = 0    'Descuento Ajuste Especial
    ValAlumnos(6, 1) = 0    'Descuento Convenio
    ValAlumnos(10, 1) = 0   'Descuento Tipo Pago
    ValAlumnos(7, 1) = 0    'Total a Pagar
    ValAlumnos(8, 1) = ""   'Codigo Convenio
    ValAlumnos(9, 1) = ""   'Codigo Colegio
    ValAlumnos(11, 1) = 0   'Total a Pagar Decimal
    
    ReDim Preserve ValAlumnos_d(1 To 11, 1 To 1)
    ValAlumnos_d(1, 1) = ""   'Run Alumno
    ValAlumnos_d(2, 1) = 0    'Arancel Base
    ValAlumnos_d(3, 1) = 0    'Descuento Beca
    ValAlumnos_d(4, 1) = 0    'Descuento Hermano
    ValAlumnos_d(5, 1) = 0    'Descuento Ajuste Especial
    ValAlumnos_d(6, 1) = 0    'Descuento Convenio
    ValAlumnos_d(10, 1) = 0   'Descuento Tipo Pago
    ValAlumnos_d(7, 1) = 0    'Total a Pagar
    ValAlumnos_d(8, 1) = ""   'Codigo Convenio
    ValAlumnos_d(9, 1) = ""   'Codigo Colegio
        
    Set RC = New ADODB.Recordset
    RC.Open "SELECT RDB FROM establecimientos;", CNX
    RDB = Trim(RC!RDB)
    If Trim(RC!RDB) <> "9782-9" Then 'Sino es niño Lincoln College
        If Trim(RC!RDB) = "26476-8" Then
           ' QR = "SELECT alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format((Mes + 1), "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(Mes, "00") & _
           ' IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY alu.cole, alu.curso DESC, alu.fec_nacalu, alu.ci_alu"
            
            QR = "SELECT orden,num_gra,alu.*, fd.convenio1, fd.hermanos, fd.cuota_cero as arancel_base FROM fnza_descuento as fd, (SELECT m.num_gra,axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, IF(m.cod_ens = '996',1,IF(m.cod_ens = '010',2,IF(m.cod_ens = '110',3,4))) AS orden, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format((mes + 1), "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & _
            IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY orden DESC,num_gra DESC, alu.fec_nacalu, alu.ci_alu DESC"
        Else
            'QR = "SELECT alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(Mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(Mes, "00") & _
            'IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY alu.cole, alu.curso DESC, alu.fec_nacalu, alu.ci_alu"
            
            QR = "SELECT orden,num_gra,alu.*, fd.convenio1, fd.hermanos, fd.cuota_cero as arancel_base FROM fnza_descuento as fd, (SELECT m.num_gra,axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, IF(m.cod_ens = '996',1,IF(m.cod_ens = '010',2,IF(m.cod_ens = '110',3,4))) AS orden, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & _
            IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY orden DESC,num_gra DESC, alu.fec_nacalu, alu.ci_alu DESC"
        End If
    Else
            'QR = "SELECT alu.*, fd.convenio1, fd.hermanos, fd.arancel_base FROM fnza_descuento as fd, (SELECT axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(Mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(Mes, "00") & _
            'IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY alu.cole, alu.curso DESC, alu.fec_nacalu, alu.ci_alu"
            
            QR = "SELECT orden,num_gra,alu.*, fd.convenio1, fd.hermanos, fd.cuota_cero as arancel_base FROM fnza_descuento as fd, (SELECT m.num_gra,axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, IF(m.cod_ens = '996',1,IF(m.cod_ens = '010',2,IF(m.cod_ens = '110',3,4))) AS orden, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & _
            IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY orden DESC,num_gra DESC, alu.fec_nacalu, alu.ci_alu DESC"
    End If
    RC.Close
    
    RC.Open QR, CNX
        If RC.RecordCount > 0 Then
            RC.MoveFirst
            For i = 1 To RC.RecordCount
                ReDim Preserve ValAlumnos(1 To 11, 1 To i)
                'Run Alumno
                ValAlumnos(1, i) = RC!ci_alu
                'Arancel Base
                ValAlumnos(2, i) = RC!arancel_base
                'Descuento Beca
                If RC!Ind_Beca = 1 Then
                    ValAlumnos(3, i) = RC!arancel_base
                    swBeca = True
                ElseIf RC!Ind_MBeca = 1 Then
                    ValAlumnos(3, i) = (RC!arancel_base / 2)
                    swBeca = True
                Else
                    ValAlumnos(3, i) = 0
                End If
            
                
                'Descuento Hermano
                If i > 1 And Not (swBeca) And Not (RC!cole = "LIN001AJM") And Not (val(UCase(RC!cod_conv)) = 4) Then
                    ValAlumnos(4, i) = Int((RC!arancel_base * (RC!hermanos / 100))) ' + 0.5)
                    'Validacion de Año
                    If agno = 2005 Then
                        ValAlumnos(4, i) = ValAlumnos(4, i) - (ValAlumnos(4, i) Mod 100)
                    Else
                        If ValidaHermanos(RunApo, RC!ci_alu, agno, mes, CNX) = True Then
                            ValAlumnos(4, i) = ValAlumnos(4, i) ' - (ValAlumnos(4, i) Mod 50)
                        Else
                            ValAlumnos(4, i) = 0
                        End If
                    End If
                Else
                    ValAlumnos(4, i) = 0
                End If
                'Descuento Ajuste Especial
                ValAlumnos(5, i) = 0
                'Descuento Convenio
                If Not (UCase(RC!cod_conv) = "N") And Not (swBeca) And Not (val(UCase(RC!cod_conv)) = 4) Then
                    
                    ValAlumnos(6, i) = Int(((RC!arancel_base - ValAlumnos(4, i)) * (RC!Convenio1 / 100))) ' + 0.5)
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos(6, i) = ValAlumnos(6, i) ' - (ValAlumnos(6, i) Mod 100)
                    Else
                        ValAlumnos(6, i) = ValAlumnos(6, i) ' - (ValAlumnos(6, i) Mod 50)
                    End If
                Else
                    If Not (val(UCase(RC!cod_conv)) = 4) Then
                        ValAlumnos(6, i) = 0
                    End If
                End If
                'Descuento Tipo Pago
                If Not (swBeca) And Not (val(UCase(RC!cod_conv)) = 4) Then
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos(10, i) = 0
                    Else
                        If (Year(RC!fec_mat) < agno) Or (Year(RC!fec_mat) = agno And Month(RC!fec_mat) <= 2) Then
                            NumMesesDePago = 10
                        Else
                            If Day(RC!fec_mat) > 15 Then
                                NumMesesDePago = 12 - IIf((Month(RC!fec_mat) < 2), 2, Month(RC!fec_mat))
                            Else
                                NumMesesDePago = 13 - IIf((Month(RC!fec_mat) < 2), 2, Month(RC!fec_mat))
                            End If
                        End If
                        Remanente = 0
                        If RC!Ind_Cont = 1 Then
                            'Porcentaje de Descuento contado
                            If RDB <> "9782-9" Then
                                PorcentajeTipoPago = 120 + (120 / NumMesesDePago) '12% anual
                            Else
                                PorcentajeTipoPago = 120 '+ (120 / NumMesesDePago) '12% anual
                            End If
                        ElseIf RC!Ind_Cheq = 1 Then
                            'Porcentaje de Descuento cheque
                            If RDB <> "9782-9" Then
                                PorcentajeTipoPago = 50 + (50 / NumMesesDePago) '5% anual
                            Else
                                PorcentajeTipoPago = 50 '+ (50 / NumMesesDePago) '5% anual
                            End If
                        Else
                            'Sin descuento.
                            PorcentajeTipoPago = 0
                        End If
                        ValAlumnos(10, i) = ((RC!arancel_base - ValAlumnos(4, i) - ValAlumnos(6, i)) * (PorcentajeTipoPago / 1000))
                        Remanente = ValAlumnos(10, i) - Int(ValAlumnos(10, i))
                        ValAlumnos(10, i) = ValAlumnos(10, i) - Remanente
                        If mes = 12 Then
                            ValAlumnos(10, i) = Round(ValAlumnos(10, i) + ((Remanente * NumMesesDePago) \ 1), 0)
                        End If
                    End If
                Else
                    ValAlumnos(10, i) = 0
                End If
                'Descuento caja
                If val(UCase(RC!cod_conv)) = 4 And Not (swBeca) Then
                    ValAlumnos(6, i) = Int(((RC!arancel_base) * (20 / 100))) ' + 0.5)
                End If
                'Total a Pagar
                ValAlumnos(7, i) = ValAlumnos(2, i) - ValAlumnos(3, i) - ValAlumnos(4, i) - ValAlumnos(5, i) - ValAlumnos(6, i) - ValAlumnos(10, i)
                'Codigo del convenio
                ValAlumnos(8, i) = RC!cod_conv
                'Colegio
                ValAlumnos(9, i) = RC!cole
                RC.MoveNext
                swBeca = False
            Next i
        End If
    RC.Close
    
    QR = "SELECT orden,num_gra,alu.*, fd.convenio1, fd.hermanos, fd.cuota_cero_d as arancel_base FROM fnza_descuento as fd, (SELECT m.num_gra,axa.rut_apo, axa.ci_alu, m.fec_mat, m.fec_retiro, if(m.cod_ens='996',concat(c.colegio,''),c.colegio) as cole, IF(m.cod_ens = '996',1,IF(m.cod_ens = '010',2,IF(m.cod_ens = '110',3,4))) AS orden, concat(m.cod_ens, m.num_gra) as curso, m.cod_ens, m.anno_mat, m.ind_beca, m.ind_mbeca, m.ind_cheq, m.ind_cont, p.fec_nacalu, a.cod_conv FROM apo_x_alu as axa, matriculas as m, postulantes as p, apoderados as a, (SELECT cc.colegio FROM hist_cod_colegio as cc, establecimientos as e WHERE e.rdb=cc.cod_rbd) as c WHERE axa.rut_apo='" & RunApo & "' AND axa.ci_alu=m.ci_alu AND axa.agno=m.anno_mat AND m.anno_mat='" & agno & "' AND m.fec_mat<='" & agno & "-" & Format(mes, "00") & "-15' AND (m.fec_retiro='1111-11-11' OR m.fec_retiro>'" & agno & "-" & Format(mes, "00") & _
            IIf((agno > 2006), "-01", "-15") & "') AND p.ci_alu=axa.ci_alu AND axa.rut_apo=a.rut_apo ) as alu WHERE left(fd.colegio,6)=left(alu.cole,6) AND fd.curso=alu.curso AND fd.agno=alu.anno_mat ORDER BY orden DESC,num_gra DESC, alu.fec_nacalu, alu.ci_alu DESC"
    
    '''' Decimal
    RC.Open QR, CNX
        If RC.RecordCount > 0 Then
            RC.MoveFirst
            For i = 1 To RC.RecordCount
                ReDim Preserve ValAlumnos_d(1 To 11, 1 To i)
                'Run Alumno
                ValAlumnos_d(1, i) = RC!ci_alu
                'Arancel Base
                ValAlumnos_d(2, i) = RC!arancel_base
                'Descuento Beca
                If RC!Ind_Beca = 1 Then
                    ValAlumnos_d(3, i) = RC!arancel_base
                    swBeca = True
                ElseIf RC!Ind_MBeca = 1 Then
                    ValAlumnos_d(3, i) = (RC!arancel_base / 2)
                    swBeca = True
                Else
                    ValAlumnos_d(3, i) = 0
                End If
            
                
                'Descuento Hermano
                If i > 1 And Not (swBeca) And Not (RC!cole = "LIN001AJM") And Not (val(UCase(RC!cod_conv)) = 4) Then
                    ValAlumnos_d(4, i) = (RC!arancel_base * (RC!hermanos / 100)) ' + 0.5)
                    'Validacion de Año
                    If agno = 2005 Then
                        ValAlumnos_d(4, i) = ValAlumnos_d(4, i) - (ValAlumnos_d(4, i) Mod 100)
                    Else
                        If ValidaHermanos(RunApo, RC!ci_alu, agno, mes, CNX) = True Then
                            ValAlumnos_d(4, i) = ValAlumnos_d(4, i) ' - (ValAlumnos_d(4, i) Mod 50)
                        Else
                            ValAlumnos_d(4, i) = 0
                        End If
                    End If
                Else
                    ValAlumnos_d(4, i) = 0
                End If
                'Descuento Ajuste Especial
                ValAlumnos(5, i) = 0
                'Descuento Convenio
                If Not (UCase(RC!cod_conv) = "N") And Not (swBeca) And Not (val(UCase(RC!cod_conv)) = 4) Then
                    
                    ValAlumnos_d(6, i) = ((RC!arancel_base - ValAlumnos_d(4, i)) * (RC!Convenio1 / 100)) ' + 0.5)
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos_d(6, i) = ValAlumnos_d(6, i) ' - (ValAlumnos(6, i) Mod 100)
                    Else
                        ValAlumnos_d(6, i) = ValAlumnos_d(6, i) ' - (ValAlumnos(6, i) Mod 50)
                    End If
                Else
                    If Not (val(UCase(RC!cod_conv)) = 4) Then
                        ValAlumnos_d(6, i) = 0
                    End If
                End If
                'Descuento Tipo Pago
                If Not (swBeca) And Not (val(UCase(RC!cod_conv)) = 4) Then
                    'Validacion de año.
                    If agno = 2005 Then
                        ValAlumnos_d(10, i) = 0
                    Else
                        If (Year(RC!fec_mat) < agno) Or (Year(RC!fec_mat) = agno And Month(RC!fec_mat) <= 2) Then
                            NumMesesDePago = 10
                        Else
                            If Day(RC!fec_mat) > 15 Then
                                NumMesesDePago = 12 - IIf((Month(RC!fec_mat) < 2), 2, Month(RC!fec_mat))
                            Else
                                NumMesesDePago = 13 - IIf((Month(RC!fec_mat) < 2), 2, Month(RC!fec_mat))
                            End If
                        End If
                        Remanente = 0
                        If RC!Ind_Cont = 1 Then
                            'Porcentaje de Descuento contado
                            If RDB <> "9782-9" Then
                                PorcentajeTipoPago = 120 + (120 / NumMesesDePago) '12% anual
                            Else
                                PorcentajeTipoPago = 120 '+ (120 / NumMesesDePago) '12% anual
                            End If
                        ElseIf RC!Ind_Cheq = 1 Then
                            'Porcentaje de Descuento cheque
                            If RDB <> "9782-9" Then
                                PorcentajeTipoPago = 50 + (50 / NumMesesDePago) '5% anual
                            Else
                                PorcentajeTipoPago = 50 '+ (50 / NumMesesDePago) '5% anual
                            End If
                        Else
                            'Sin descuento.
                            PorcentajeTipoPago = 0
                        End If
                        ValAlumnos_d(10, i) = ((RC!arancel_base - ValAlumnos_d(4, i) - ValAlumnos_d(6, i)) * (PorcentajeTipoPago / 1000))
                        Remanente = ValAlumnos_d(10, i) - Int(ValAlumnos_d(10, i))
                        ValAlumnos_d(10, i) = ValAlumnos_d(10, i) - Remanente
                        If mes = 12 Then
                            ValAlumnos_d(10, i) = Round(ValAlumnos_d(10, i) + ((Remanente * NumMesesDePago) \ 1), 0)
                        End If
                    End If
                Else
                    ValAlumnos(10, i) = 0
                End If
                'Descuento caja
                If val(UCase(RC!cod_conv)) = 4 And Not (swBeca) Then
                    ValAlumnos_d(6, i) = ((RC!arancel_base) * (20 / 100)) ' + 0.5)
                End If
                'Total a Pagar
                ValAlumnos(11, i) = ValAlumnos_d(2, i) - ValAlumnos_d(3, i) - ValAlumnos_d(4, i) - ValAlumnos_d(5, i) - ValAlumnos_d(6, i) - ValAlumnos_d(10, i)
                'Codigo del convenio
                ValAlumnos_d(8, i) = RC!cod_conv
                'Colegio
                ValAlumnos_d(9, i) = RC!cole
                RC.MoveNext
                swBeca = False
            Next i
        End If
    RC.Close
      
    CuotaAlumnosApoFecha_cuota00 = ValAlumnos
    Exit Function
Controlerror:
    errnum = Err.Number
    errdes = Err.Description
    errsou = Err.Source
    Err.Raise errnum, errsou & vbCrLf & "Mod_Funciones_Fnzas.CuotaAlumnosFecha", errdes
    Resume
End Function

Function CuotaAlumnoFecha_cuota00(ByVal RunAlu As String, ByVal agno As Integer, ByVal mes As Integer, ByRef CNX As ADODB.Connection) As Variant
Dim errnum As Double
Dim errdes As String
Dim errsou As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Res As Variant
    Dim RunApo As String
    Dim Repositorio As Variant
    Dim Resp(1 To 11) As Variant
    
    Resp(1) = ""
    Resp(2) = 0
    Resp(3) = 0
    Resp(4) = 0
    Resp(5) = 0
    Resp(6) = 0
    Resp(7) = 0
    Resp(8) = ""
    Resp(9) = ""
    Resp(10) = 0
    Resp(11) = 0
    '####################
    'MODIFICADO SEGUN AÑO OK
    QR = "SELECT rut_apo FROM apo_x_alu WHERE ci_alu='" & RunAlu & "' AND agno=" & agno & ";"
    '####################
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    If RC.RecordCount > 0 Then
        RC.MoveFirst
        RunApo = RC!rut_apo
        RC.Close
        Repositorio = CuotaAlumnosApoFecha_cuota00(RunApo, agno, mes, CNX)
        For i = 1 To UBound(Repositorio, 2)
            If Repositorio(1, i) = RunAlu Then
                Resp(1) = Repositorio(1, i)
                Resp(2) = Repositorio(2, i)
                Resp(3) = Repositorio(3, i)
                Resp(4) = Repositorio(4, i)
                Resp(5) = Repositorio(5, i)
                Resp(6) = Repositorio(6, i)
                Resp(7) = Repositorio(7, i)
                Resp(8) = Repositorio(8, i)
                Resp(9) = Repositorio(9, i)
                Resp(10) = Repositorio(10, i)
                Resp(11) = Repositorio(11, i)
            End If
        Next i
        CuotaAlumnoFecha_cuota00 = Resp
    Else
        RC.Close
        Err.Raise vbObjectError + 750, "", "No se ha podido encontrar un apoderado para este alumno."
    End If
    Exit Function
Controlerror:
    errnum = Err.Number
    errdes = Err.Description
    errsou = Err.Source
    Err.Raise errnum, errsou & vbCrLf & "Mod_Funciones_Fnzas.CuotaAlumnosFecha", errdes
    Resume
End Function

