VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_Alumnos_Matriculados_Forma_Pago 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Alumnos Matriculados por Forma de Pago"
   ClientHeight    =   8595
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   13650
   Icon            =   "Form_Alumnos_Matriculados_Forma_Pago.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   8595
   ScaleWidth      =   13650
   Begin VB.CommandButton Command2 
      Caption         =   "Exportar Excel"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   240
      Picture         =   "Form_Alumnos_Matriculados_Forma_Pago.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   12
      Top             =   7560
      Width           =   1575
   End
   Begin VB.CommandButton cmd_salir 
      Caption         =   "Salir"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   11760
      Picture         =   "Form_Alumnos_Matriculados_Forma_Pago.frx":2AE98
      Style           =   1  'Graphical
      TabIndex        =   11
      Top             =   7560
      Width           =   1575
   End
   Begin VB.Frame Frame1 
      Caption         =   "Exportar Alumnos Matriculados"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   6855
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   13335
      Begin VB.ComboBox fp_mensualidad 
         Height          =   315
         Left            =   6120
         TabIndex        =   14
         Top             =   480
         Width           =   3135
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Listar apoderados"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   10560
         TabIndex        =   4
         Top             =   600
         Width           =   1815
      End
      Begin VB.Frame Frame2 
         Caption         =   "Frame2"
         Height          =   2175
         Left            =   1920
         TabIndex        =   2
         Top             =   3000
         Visible         =   0   'False
         Width           =   9375
         Begin VB.Label Label2 
            Caption         =   "En proceso"
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   48
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   975
            Left            =   2280
            TabIndex        =   3
            Top             =   600
            Width           =   5415
         End
      End
      Begin VB.ComboBox cb_encargado 
         Height          =   315
         Left            =   6120
         TabIndex        =   1
         Top             =   960
         Width           =   3135
      End
      Begin MSComCtl2.DTPicker DTP_inicio 
         Height          =   315
         Left            =   1080
         TabIndex        =   5
         Top             =   480
         Width           =   1455
         _ExtentX        =   2566
         _ExtentY        =   556
         _Version        =   393216
         Format          =   51773441
         CurrentDate     =   42103
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   5055
         Left            =   120
         TabIndex        =   6
         Top             =   1680
         Width           =   13095
         _ExtentX        =   23098
         _ExtentY        =   8916
         _Version        =   393216
      End
      Begin MSComCtl2.DTPicker DTP_hasta 
         Height          =   315
         Left            =   1080
         TabIndex        =   7
         Top             =   960
         Width           =   1455
         _ExtentX        =   2566
         _ExtentY        =   556
         _Version        =   393216
         Format          =   51773441
         CurrentDate     =   42103
      End
      Begin VB.Label Label7 
         Caption         =   "Forma de Pago"
         Height          =   255
         Left            =   4560
         TabIndex        =   15
         Top             =   480
         Width           =   1335
      End
      Begin VB.Label Label1 
         Caption         =   "Desde"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   240
         TabIndex        =   10
         Top             =   600
         Width           =   615
      End
      Begin VB.Label Label3 
         Caption         =   "Hasta"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   240
         TabIndex        =   9
         Top             =   1080
         Width           =   615
      End
      Begin VB.Label Label5 
         Caption         =   "Usuario Ingreso"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   4560
         TabIndex        =   8
         Top             =   1080
         Width           =   855
      End
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   0
      TabIndex        =   13
      Top             =   7200
      Width           =   13335
      _ExtentX        =   23521
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
End
Attribute VB_Name = "Form_Alumnos_Matriculados_Forma_Pago"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Rec2Grid(ByRef rec As ADODB.Recordset, ByRef grid As Object, Optional ByVal Numeracion As Boolean = True)
    grid.Clear
    If rec.RecordCount > 0 Then
        grid.Cols = rec.Fields.count + 1
        grid.Rows = rec.RecordCount + 1
        pgbar.Min = 1
        pgbar.Max = IIf(rec.RecordCount = 1, 2, rec.RecordCount)
        pgbar.Value = pgbar.Min
        
        grid.FixedCols = 1
        grid.FixedRows = 1
        grid.TextMatrix(0, 0) = "Nº"
        grid.ColWidth(0) = IIf(Numeracion, TextWidth(grid.TextMatrix(0, 0)) + 100, 0)
        For i = 1 To rec.Fields.count
            grid.TextMatrix(0, i) = UCase(rec.Fields(i - 1).Name)
            grid.ColWidth(i) = TextWidth(grid.TextMatrix(0, i)) + 100
        Next i
        rec.MoveFirst
        For i = 1 To rec.RecordCount
            pgbar.Value = i
            grid.TextMatrix(i, 0) = i
            grid.ColWidth(0) = IIf(Numeracion, IIf(TextWidth(grid.TextMatrix(i, 0)) + 100 > grid.ColWidth(0), TextWidth(grid.TextMatrix(i, 0)) + 100, grid.ColWidth(0)), 0)
            For j = 1 To rec.Fields.count
                grid.TextMatrix(i, j) = IIf(IsNull(rec(j - 1)), "Null", rec(j - 1))
                grid.ColWidth(j) = IIf(TextWidth(grid.TextMatrix(i, j)) + 100 > grid.ColWidth(j), TextWidth(grid.TextMatrix(i, j)) + 100, grid.ColWidth(j))
            Next j
            rec.MoveNext
        Next i
    Else
        grid.Cols = 2
        grid.Rows = 2
    End If
End Sub

Private Sub cmd_Salir_Click()
    Unload Me
End Sub

Private Sub Command1_Click()
    Dim var_sql As String
    Dim var_sql1 As String
    Dim conecta As Boolean
    Dim maximo_mes As Integer
    Dim dia As Integer, mes As Integer, agno As Integer
    Dim mes_palabra As String
    Dim Filtro As String
   
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    Frame2.Visible = True
    
    Filtro = ""
       
    ' Usuario
    If cb_encargado.Text <> "Todos" Then
        If fp_mensualidad.Text = "Cheques" Then
            Filtro = Filtro & " where usuario_ingreso = '" & cb_encargado.Text & "'"
        Else
            Filtro = Filtro & " and usuario_ingreso = '" & cb_encargado.Text & "'"
        End If
    End If
    
    var_sql = "drop table if exists temp_cheques_pagos;"
    conn.Execute var_sql
                  
    cole = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    
    If cole = "HUE001" Then
    '''''  Huechuraba rendición
    If fp_mensualidad.Text = "Cheques" Then
        '''''' Año 2025
        var_sql = "CREATE TABLE temp_cheques_pagos SELECT * FROM" _
            & " (SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE(fp_incorporacion) AS FORMA_PAGO,IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA,UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'inco' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2025 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_incorporacion = '" & fp_mensualidad.Text & "' GROUP BY A.RUT_APO,fdm.MES" _
            & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE(fp_matricula) AS FORMA_PAGO," _
            & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA," _
            & "UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'ct00' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2025 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_matricula = '" & fp_mensualidad.Text & "' GROUP BY A.RUT_APO,fdm.MES" _
            & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE(fp_mensualidad) AS FORMA_PAGO," _
            & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA," _
            & "UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'esco' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2025 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_mensualidad = '" & fp_mensualidad.Text & "' GROUP BY A.RUT_APO,fdm.MES) AS mat ORDER BY fec_ing,rut_apo,mes"
        conn.Execute var_sql
    
        '''''' Año 2025
        var_sql = "drop table if exists temp_cheques_recibidos"
        conn.Execute var_sql
    
        '''''' Año 2025
        var_sql = "CREATE TABLE temp_cheques_recibidos" _
                & " SELECT c.*,descripcion FROM fnza_cheques_recibidos c,fnza_cod_banco b" _
                & " WHERE agno = 2025 AND c.banco = b.banco"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "ALTER TABLE temp_cheques_recibidos ADD fecha_cheque DATE"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "UPDATE temp_cheques_recibidos SET fecha_cheque = IF(chq_fecha < '2025-01-01','2025-02-25',chq_fecha)"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "UPDATE temp_cheques_recibidos SET fecha_cheque = '2025-03-25' WHERE agno = 2025 AND monto_chq > 1000000 AND chq_fecha < '2025-01-01'"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "DROP TABLE temp_mes_pago"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "CREATE TABLE temp_mes_pago SELECT rut_apo,MIN(mes) AS mes FROM temp_cheques_pagos GROUP BY rut_apo"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "DROP TABLE temp_cheques_no_realcionados"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "CREATE TABLE temp_cheques_no_realcionados SELECT folio_chq FROM temp_cheques_recibidos r" _
                & " LEFT JOIN  temp_cheques_pagos p ON r.run_apo = p.rut_apo AND DATE_FORMAT(r.fecha_CHEQUE,'%m') = mes" _
                & " Where p.rut_apo Is Null"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "UPDATE temp_cheques_recibidos r," _
                & "temp_mes_pago p SET fecha_cheque = CONCAT('2025-',IF(p.mes < 10,CONCAT('0',p.mes),p.mes),'-23')" _
                & " WHERE r.run_apo = p.rut_apo AND folio_chq IN (SELECT * FROM temp_cheques_no_realcionados)"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "drop table if exists temp_carlos_cheques"
        conn.Execute var_sql
        
        '''''' Año 2025
        var_sql = "CREATE TABLE temp_carlos_cheques" _
                & " SELECT p.colegio,RUT_APO,APODERADO,R.RUN_TITULAR,R.NOM_TITULAR,R.TEL_TITULAR,FORMA_PAGO,CONCEPTO," _
                & "r.descripcion AS BANCO,R.CTA_CTE AS CUENTA_CORRIENTE,R.CHQ_SERIE,R.CHQ_NUMERO,R.MONTO_CHQ,R.VERIFICACION,R.BANCO_PLAZA," _
                & "MES_DE,AÑO_DE,chq_fecha AS FEC_VENC,FEC_ING,R.USUARIO_INGRESO,p.mes" _
                & " FROM temp_cheques_pagos p LEFT JOIN temp_cheques_recibidos r" _
                & " ON p.rut_apo = r.run_apo AND p.mes = DATE_FORMAT(r.fecha_CHEQUE,'%m')" _
                & " ORDER BY FEC_ING,RUN_APO,p.mes"
        conn.Execute var_sql
        
                '''''''' Año 2026
        var_sql = "drop table if exists temp_cheques_pagos;"
        conn.Execute var_sql
        
                '''''''' Año 2026
        var_sql = "CREATE TABLE temp_cheques_pagos SELECT * FROM" _
            & " (SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE(fp_incorporacion) AS FORMA_PAGO,IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA,UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'inco' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2026 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_incorporacion = '" & fp_mensualidad.Text & "' GROUP BY A.RUT_APO,fdm.MES" _
            & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE(fp_matricula) AS FORMA_PAGO," _
            & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA," _
            & "UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'ct00' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2026 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_matricula = '" & fp_mensualidad.Text & "' GROUP BY A.RUT_APO,fdm.MES" _
            & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE(fp_mensualidad) AS FORMA_PAGO," _
            & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA," _
            & "UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'esco' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2026 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_mensualidad = '" & fp_mensualidad.Text & "' GROUP BY A.RUT_APO,fdm.MES" _
            & ") AS mat ORDER BY fec_ing,rut_apo,mes"
        conn.Execute var_sql
    
            '''''' Año 2026
        var_sql = "drop table if exists temp_cheques_recibidos"
        conn.Execute var_sql
    
            '''''' Año 2026
        var_sql = "CREATE TABLE temp_cheques_recibidos" _
                & " SELECT c.*,descripcion FROM fnza_cheques_recibidos c,fnza_cod_banco b" _
                & " WHERE agno = 2026 AND c.banco = b.banco"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "ALTER TABLE temp_cheques_recibidos ADD fecha_cheque DATE"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "UPDATE temp_cheques_recibidos SET fecha_cheque = IF(chq_fecha < '2026-01-01','2026-02-25',chq_fecha)"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "UPDATE temp_cheques_recibidos SET fecha_cheque = '2026-03-25' WHERE agno = 2026 AND monto_chq > 1000000 AND chq_fecha < '2026-01-01'"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "DROP TABLE temp_mes_pago"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "CREATE TABLE temp_mes_pago SELECT rut_apo,MIN(mes) AS mes FROM temp_cheques_pagos GROUP BY rut_apo"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "DROP TABLE temp_cheques_no_realcionados"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "CREATE TABLE temp_cheques_no_realcionados SELECT folio_chq FROM temp_cheques_recibidos r" _
                & " LEFT JOIN  temp_cheques_pagos p ON r.run_apo = p.rut_apo AND DATE_FORMAT(r.fecha_CHEQUE,'%m') = mes" _
                & " Where p.rut_apo Is Null"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "UPDATE temp_cheques_recibidos r," _
                & "temp_mes_pago p SET fecha_cheque = CONCAT('2026-',IF(p.mes < 10,CONCAT('0',p.mes),p.mes),'-23')" _
                & " WHERE r.run_apo = p.rut_apo AND folio_chq IN (SELECT * FROM temp_cheques_no_realcionados)"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "drop table if exists temp_carlos_cheques2"
        conn.Execute var_sql
        
            '''''' Año 2026
        var_sql = "CREATE TABLE temp_carlos_cheques2" _
                & " SELECT p.colegio,RUT_APO,APODERADO,R.RUN_TITULAR,R.NOM_TITULAR,R.TEL_TITULAR,FORMA_PAGO,CONCEPTO," _
                & "r.descripcion AS BANCO,R.CTA_CTE AS CUENTA_CORRIENTE,R.CHQ_SERIE,R.CHQ_NUMERO,R.MONTO_CHQ,R.VERIFICACION,R.BANCO_PLAZA," _
                & "MES_DE,AÑO_DE,chq_fecha AS FEC_VENC,FEC_ING,R.USUARIO_INGRESO,p.mes" _
                & " FROM temp_cheques_pagos p LEFT JOIN temp_cheques_recibidos r" _
                & " ON p.rut_apo = r.run_apo AND p.mes = DATE_FORMAT(r.fecha_CHEQUE,'%m')" _
                & " ORDER BY FEC_ING,RUN_APO,p.mes"
        conn.Execute var_sql
        
        
                
        var_sql = "SELECT * FROM temp_carlos_cheques " & Filtro & " union SELECT * FROM temp_carlos_cheques2 " & Filtro & ""
    Else
        If fp_mensualidad.Text = "PAT" Or fp_mensualidad.Text = "Pagare" Then
            var_sql = "CREATE TABLE temp_cheques_pagos SELECT * FROM" _
                & " (SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,m.ci_alu AS RUT_ALU,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS ALUMNO,UCASE(fp_incorporacion) AS FORMA_PAGO,IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,SUM(MONTO_TOT_CUOTA) AS MONTO,UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
                & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'inco' AND fdm.mes = me.num_mes" _
                & " AND anno_mat = 2021 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_incorporacion = '" & fp_mensualidad.Text & "' " & Filtro & " GROUP BY A.RUT_APO,fdm.run_alu,fdm.MES" _
                & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,m.ci_alu AS RUT_ALU,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS ALUMNO,UCASE(fp_matricula) AS FORMA_PAGO," _
                & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,SUM(MONTO_TOT_CUOTA) AS MONTO," _
                & "UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
                & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'ct00' AND fdm.mes = me.num_mes" _
                & " AND anno_mat = 2021 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_matricula = '" & fp_mensualidad.Text & "' " & Filtro & " GROUP BY A.RUT_APO,fdm.run_alu,fdm.MES" _
                & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,m.ci_alu AS RUT_ALU,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS ALUMNO,UCASE(fp_mensualidad) AS FORMA_PAGO," _
                & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,SUM(MONTO_TOT_CUOTA) AS MONTO," _
                & "UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
                & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'esco' AND fdm.mes = me.num_mes" _
                & " AND anno_mat = 2021 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_mensualidad = '" & fp_mensualidad.Text & "' " & Filtro & " GROUP BY A.RUT_APO,fdm.run_alu,fdm.MES) AS mat ORDER BY fec_ing,rut_apo,mes,rut_alu"
            conn.Execute var_sql
        
            var_sql = "select * from temp_cheques_pagos ORDER BY fec_ing,rut_apo,mes"
        Else
            '29-04-2025 SE AGREGA LA NOTA DE CREDITO AL SELECT
            var_sql = "SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO," _
                    & "run_alu AS RUT_ALU,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS ALUMNO,folio_blta_fisico AS FOLIO,cast(monto_blta as signed) as MONTO_TOTAL_BLTA,fecha_blta AS FECHA_BLTA,UCASE(fp.descripcion) AS FORMA_PAGO," _
                    & "IF(cod_concepto = 'inco','C_INCORP.',IF(cod_concepto = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO," _
                    & "SUM(MONTO_pago) AS MONTO,UCASE(ME.MES) AS MES_DE,DATE_FORMAT(fech_venc_cptos,'%d-%M-%Y') AS FEC_VENC," _
                    & "fecha_ingreso As FEC_ING, usuario_ingreso" _
                    & " FROM fnza_boletas b,fnza_dtll_mvto n,fnza_movimientos_mensualidad m,fnza_cod_forma_pago fp," _
                    & "apoderados a,meses me,establecimientos e,postulantes p" _
                    & " WHERE a.rut_apo = b.run_apo AND b.folio_blta = n.folio_blta AND n.folio_mvto = m.folio_mvto AND b.run_alu = p.ci_alu AND" _
                    & " DATE_FORMAT(fecha_ingreso,'%Y-%m-%d') >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "'" _
                    & " AND DATE_FORMAT(fecha_ingreso,'%Y-%m-%d') <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "'" _
                    & " AND m.tipo_pago = fp.forma_pago AND DATE_FORMAT(fech_venc_cptos,'%m') = me.num_mes" _
                    & " AND fp.descripcion = '" & fp_mensualidad.Text & "' " & Filtro & "" _
                    & " GROUP BY fech_venc_cptos,run_apo,run_alu,me.mes,folio_blta_fisico"
                    
               var_sql1 = " UNION" _
                    & " SELECT E.nom_est  AS colegio, B.RUN_APO AS RUT_APO, CONCAT(A.ape1_apo,' ',A.ape2_apo,' ',A.noms_apo) AS APODERADO," _
                    & " P.CI_ALU AS RUT_ALU, CONCAT(P.ape_pat_alu,' ',P.ape_mat_alu,' ',P.nom_alu) AS ALUMNO, B.folio_fisico_ntcr AS FOLIO," _
                    & " CAST((B.monto_NTCR * -1) AS SIGNED) AS monto, B.fecha_NTCR AS FECHA_BLTA, B.TIPO_PAGO AS FORMA_PAGO, 'NOTA DE CREDITO' AS CONCEPTO," _
                    & " CAST((B.monto_NTCR * -1) AS SIGNED) As monto, folio_blta_fisico AS MES_DE,   '' AS FEC_VENC, '' AS FEC_ING, ''" _
                    & " FROM fnza_NTCR b," _
                    & " fnza_detalle_ntcr n," _
                    & " APODERADOS A," _
                    & " ESTABLECIMIENTOS e," _
                    & " FNZA_BOLETAS FB," _
                    & " POSTULANTES P,fnza_dtll_mvto fdm,fnza_movimientos_mensualidad fmm,fnza_cod_forma_pago fp" _
                    & " Where b.folio_NTCR = n.folio_NTCR" _
                    & " AND N.BLTA_REL = FB.FOLIO_BLTA" _
                    & " AND FB.RUN_ALU = P.CI_ALU" _
                    & " AND A.RUT_APO = B.RUN_APO" _
                    & " and  fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fmm.tipo_pago = fp.forma_pago AND fp.descripcion = '" & fp_mensualidad.Text & "'" _
                    & " AND  DATE_FORMAT(fecha_NTCR,'%Y-%m-%d') >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "'" _
                    & " AND DATE_FORMAT(fecha_NTCR,'%Y-%m-%d') <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "'" _
                    & " GROUP BY folio_fisico_ntcr"
                    
                    ' & " ORDER BY fec_ing,run_apo,run_alu,me.num_mes,folio_blta_fisico"
                    var_sql = var_sql & var_sql1
        
        End If
    End If
                
    Else
    ''''' Rendicion corporación
    If fp_mensualidad.Text = "Cheques" Then
        ''''' Año 2021
        var_sql = "CREATE TABLE temp_cheques_pagos SELECT * FROM" _
            & " (SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE('Cheques') AS FORMA_PAGO," _
            & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA," _
            & "UCASE(ME.MES) AS MES_DE,fdm.mes as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'ct00' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2021 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND ind_cheq = 1 GROUP BY A.RUT_APO,fdm.MES" _
            & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE('Cheques') AS FORMA_PAGO," _
            & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA," _
            & "UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'esco' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2021 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND ind_cheq = 1 GROUP BY A.RUT_APO,fdm.MES) AS mat ORDER BY fec_ing,rut_apo,mes"
        conn.Execute var_sql
    
        ''''' Año 2021
        var_sql = "drop table if exists temp_cheques_recibidos"
        conn.Execute var_sql
    
        ''''' Año 2021
        var_sql = "CREATE TABLE temp_cheques_recibidos" _
                & " SELECT c.*,descripcion FROM fnza_cheques_recibidos c,fnza_cod_banco b" _
                & " WHERE agno = 2021 AND c.banco = b.banco"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "ALTER TABLE temp_cheques_recibidos ADD fecha_cheque DATE"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "UPDATE temp_cheques_recibidos SET fecha_cheque = IF(chq_fecha < '2021-01-01','2021-02-25',chq_fecha)"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "UPDATE temp_cheques_recibidos SET fecha_cheque = '2021-03-25' WHERE agno = 2021 AND monto_chq > 1000000 AND chq_fecha < '2021-01-01'"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "drop table if exists temp_mes_pago"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "CREATE TABLE temp_mes_pago SELECT rut_apo,MIN(mes) AS mes FROM temp_cheques_pagos GROUP BY rut_apo"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "drop table if exists temp_cheques_no_realcionados"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "CREATE TABLE temp_cheques_no_realcionados SELECT folio_chq FROM temp_cheques_recibidos r" _
                & " LEFT JOIN  temp_cheques_pagos p ON r.run_apo = p.rut_apo AND DATE_FORMAT(r.fecha_CHEQUE,'%m') = mes" _
                & " Where p.rut_apo Is Null"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "UPDATE temp_cheques_recibidos r," _
                & "temp_mes_pago p SET fecha_cheque = CONCAT('2021-',IF(p.mes < 10,CONCAT('0',p.mes),p.mes),'-23')" _
                & " WHERE r.run_apo = p.rut_apo AND folio_chq IN (SELECT * FROM temp_cheques_no_realcionados)"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "drop table if exists temp_carlos_cheques"
        conn.Execute var_sql
        
        ''''' Año 2021
        var_sql = "CREATE TABLE temp_carlos_cheques" _
                & " SELECT p.colegio,RUT_APO,APODERADO,R.RUN_TITULAR,R.NOM_TITULAR,R.TEL_TITULAR,FORMA_PAGO,CONCEPTO," _
                & "r.descripcion AS BANCO,R.CTA_CTE AS CUENTA_CORRIENTE,R.CHQ_SERIE,R.CHQ_NUMERO,R.MONTO_CHQ,R.VERIFICACION,R.BANCO_PLAZA," _
                & "MES_DE,AÑO_DE,chq_fecha AS FEC_VENC,FEC_ING,R.USUARIO_INGRESO,p.mes" _
                & " FROM temp_cheques_pagos p LEFT JOIN temp_cheques_recibidos r" _
                & " ON p.rut_apo = r.run_apo AND p.mes = DATE_FORMAT(r.fecha_CHEQUE,'%m')" _
                & " ORDER BY FEC_ING,RUN_APO,p.mes"
        conn.Execute var_sql
        
        
                  '''''''' Año 2025
        var_sql = "drop table if exists temp_cheques_pagos;"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "CREATE TABLE temp_cheques_pagos SELECT * FROM" _
            & " (SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE('Cheques') AS FORMA_PAGO," _
            & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA," _
            & "UCASE(ME.MES) AS MES_DE,fdm.mes as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'ct00' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2025 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND (ind_cheq = 1 OR (ind_cont = 1 AND ind_cont_cheq = 1)) GROUP BY A.RUT_APO,fdm.MES" _
            & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,'' AS RUN_TITULAR,'' AS NOM_TITULAR,'' AS TEL_TITULAR,UCASE('Cheques') AS FORMA_PAGO," _
            & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,'' AS DESCRIPCION,'' AS CUENTA_CORRIENTE,'' AS CHQ_SERIE,'' AS CHQ_NUMERO,SUM(MONTO_TOT_CUOTA) AS MONTO_CHQ,'' AS VERIFICACION,'' AS BANCO_PLAZA," _
            & "UCASE(ME.MES) AS MES_DE,fdm.agno as AÑO_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'esco' AND fdm.mes = me.num_mes" _
            & " AND anno_mat = 2025 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND (ind_cheq = 1 OR (ind_cont = 1 AND ind_cont_cheq = 1)) GROUP BY A.RUT_APO,fdm.MES) AS mat ORDER BY fec_ing,rut_apo,mes"
        conn.Execute var_sql
    
        ''''' Año 2025
        var_sql = "drop table if exists temp_cheques_recibidos"
        conn.Execute var_sql
    
        ''''' Año 2025
        var_sql = "CREATE TABLE temp_cheques_recibidos" _
                & " SELECT c.*,descripcion FROM fnza_cheques_recibidos c,fnza_cod_banco b" _
                & " WHERE agno = 2025 AND c.banco = b.banco"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "ALTER TABLE temp_cheques_recibidos ADD fecha_cheque DATE"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "UPDATE temp_cheques_recibidos SET fecha_cheque = IF(chq_fecha < '2025-01-01','2025-02-25',chq_fecha)"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "UPDATE temp_cheques_recibidos SET fecha_cheque = '2025-03-25' WHERE agno = 2025 AND monto_chq > 1000000 AND chq_fecha < '2025-01-01'"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "drop table if exists temp_mes_pago"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "CREATE TABLE temp_mes_pago SELECT rut_apo,MIN(mes) AS mes FROM temp_cheques_pagos GROUP BY rut_apo"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "drop table if exists temp_cheques_no_realcionados"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "CREATE TABLE temp_cheques_no_realcionados SELECT folio_chq FROM temp_cheques_recibidos r" _
                & " LEFT JOIN  temp_cheques_pagos p ON r.run_apo = p.rut_apo AND DATE_FORMAT(r.fecha_CHEQUE,'%m') = mes" _
                & " Where p.rut_apo Is Null"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "UPDATE temp_cheques_recibidos r," _
                & "temp_mes_pago p SET fecha_cheque = CONCAT('2025-',IF(p.mes < 10,CONCAT('0',p.mes),p.mes),'-23')" _
                & " WHERE r.run_apo = p.rut_apo AND folio_chq IN (SELECT * FROM temp_cheques_no_realcionados)"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "drop table if exists temp_carlos_cheques2"
        conn.Execute var_sql
        
        ''''' Año 2025
        var_sql = "CREATE TABLE temp_carlos_cheques2" _
                & " SELECT p.colegio,RUT_APO,APODERADO,R.RUN_TITULAR,R.NOM_TITULAR,R.TEL_TITULAR,FORMA_PAGO,CONCEPTO," _
                & "r.descripcion AS BANCO,R.CTA_CTE AS CUENTA_CORRIENTE,R.CHQ_SERIE,R.CHQ_NUMERO,R.MONTO_CHQ,R.VERIFICACION,R.BANCO_PLAZA," _
                & "MES_DE,AÑO_DE,chq_fecha AS FEC_VENC,FEC_ING,R.USUARIO_INGRESO,p.mes" _
                & " FROM temp_cheques_pagos p LEFT JOIN temp_cheques_recibidos r" _
                & " ON p.rut_apo = r.run_apo AND p.mes = DATE_FORMAT(r.fecha_CHEQUE,'%m')" _
                & " ORDER BY FEC_ING,RUN_APO,p.mes"
        conn.Execute var_sql
                
        'var_sql = "SELECT * FROM temp_carlos_cheques " & Filtro & " ORDER BY FEC_ING,RUt_APO,mes"
        var_sql = "SELECT * FROM temp_carlos_cheques " & Filtro & " union SELECT * FROM temp_carlos_cheques2 " & Filtro & ""
    Else
        If fp_mensualidad.Text = "PAT" Or fp_mensualidad.Text = "Pagare" Then
'            var_sql = "CREATE TABLE temp_cheques_pagos SELECT * FROM" _
'                & " (SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,m.ci_alu AS RUT_ALU,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS ALUMNO,UCASE(fp_matricula) AS FORMA_PAGO," _
'                & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,SUM(MONTO_TOT_CUOTA) AS MONTO," _
'                & "UCASE(ME.MES) AS MES_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
'                & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'ct00' AND fdm.mes = me.num_mes" _
'                & " AND activo_alu = 'SI' AND anno_mat = 2021 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_matricula = '" & fp_mensualidad.Text & "' " & Filtro & " GROUP BY A.RUT_APO,fdm.run_alu,fdm.MES" _
'                & " Union SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,m.ci_alu AS RUT_ALU,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS ALUMNO,UCASE(fp_mensualidad) AS FORMA_PAGO," _
'                & "IF(CONCEPTO = 'inco','C_INCORP.',IF(CONCEPTO = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO,SUM(MONTO_TOT_CUOTA) AS MONTO," _
'                & "UCASE(ME.MES) AS MES_DE,DATE_FORMAT(FECH_VENC,'%d-%M-%Y') AS FEC_VENC,fec_mat_r AS FEC_ING,usuario_ingreso,fdm.mes FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p,fnza_detalle_mensualidad fdm,meses me,establecimientos e" _
'                & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'esco' AND fdm.mes = me.num_mes" _
'                & " AND activo_alu = 'SI' AND anno_mat = 2021 AND fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND fp_mensualidad = '" & fp_mensualidad.Text & "' " & Filtro & " GROUP BY A.RUT_APO,fdm.run_alu,fdm.MES) AS mat ORDER BY fec_ing,rut_apo,mes,rut_alu"
'            conn.Execute var_sql
'
'            var_sql = "select * from temp_cheques_pagos ORDER BY fec_ing,rut_apo,mes"
            MsgBox "No hay alumnos matriculados en el filtro seleccionado", vbInformation
            Exit Sub

        Else
            '02-05-2025 se agrega la nota de credito
            var_sql = "SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO," _
                    & "run_alu AS RUT_ALU,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS ALUMNO,folio_blta_fisico AS FOLIO,cast(monto_blta as signed) as MONTO_TOTAL_BLTA,fecha_blta AS FECHA_BLTA,UCASE(fp.descripcion) AS FORMA_PAGO," _
                    & "IF(cod_concepto = 'inco','C_INCORP.',IF(cod_concepto = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO," _
                    & "SUM(MONTO_pago) AS MONTO,UCASE(ME.MES) AS MES_DE,DATE_FORMAT(fech_venc_cptos,'%d-%M-%Y') AS FEC_VENC," _
                    & "fecha_ingreso As FEC_ING, usuario_ingreso" _
                    & " FROM fnza_boletas b,fnza_dtll_mvto n,fnza_movimientos_mensualidad m,fnza_cod_forma_pago fp," _
                    & "apoderados a,meses me,establecimientos e,postulantes p" _
                    & " WHERE a.rut_apo = b.run_apo AND b.folio_blta = n.folio_blta AND n.folio_mvto = m.folio_mvto AND b.run_alu = p.ci_alu AND" _
                    & " DATE_FORMAT(fecha_ingreso,'%Y-%m-%d') >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "'" _
                    & " AND DATE_FORMAT(fecha_ingreso,'%Y-%m-%d') <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "'" _
                    & " AND m.tipo_pago = fp.forma_pago AND DATE_FORMAT(fech_venc_cptos,'%m') = me.num_mes" _
                    & " AND fp.descripcion = '" & fp_mensualidad.Text & "' " & Filtro & " GROUP BY fech_venc_cptos,run_apo,run_alu,me.mes,folio_blta_fisico" _
                    
                    
            var_sql1 = " UNION" _
                    & " SELECT E.nom_est  AS colegio, B.RUN_APO AS RUT_APO, CONCAT(A.ape1_apo,' ',A.ape2_apo,' ',A.noms_apo) AS APODERADO," _
                    & " P.CI_ALU AS RUT_ALU, CONCAT(P.ape_pat_alu,' ',P.ape_mat_alu,' ',P.nom_alu) AS ALUMNO, B.folio_fisico_ntcr AS FOLIO," _
                    & " CAST((B.monto_NTCR * -1) AS SIGNED) AS monto, B.fecha_NTCR AS FECHA_BLTA, B.TIPO_PAGO AS FORMA_PAGO, 'NOTA DE CREDITO' AS CONCEPTO," _
                    & " CAST((B.monto_NTCR * -1) AS SIGNED) As monto, folio_blta_fisico AS MES_DE,   '' AS FEC_VENC, '' AS FEC_ING, ''" _
                    & " FROM fnza_NTCR b," _
                    & " fnza_detalle_ntcr n," _
                    & " APODERADOS A," _
                    & " ESTABLECIMIENTOS e," _
                    & " FNZA_BOLETAS FB," _
                    & " POSTULANTES P,fnza_dtll_mvto fdm,fnza_movimientos_mensualidad fmm,fnza_cod_forma_pago fp" _
                    & " Where b.folio_NTCR = n.folio_NTCR" _
                    & " AND N.BLTA_REL = FB.FOLIO_BLTA" _
                    & " AND FB.RUN_ALU = P.CI_ALU" _
                    & " AND A.RUT_APO = B.RUN_APO" _
                    & " and  fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fmm.tipo_pago = fp.forma_pago AND fp.descripcion = '" & fp_mensualidad.Text & "'" _
                    & " AND  DATE_FORMAT(fecha_NTCR,'%Y-%m-%d') >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "'" _
                    & " AND DATE_FORMAT(fecha_NTCR,'%Y-%m-%d') <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "'" _
                    & " GROUP BY folio_fisico_ntcr"
                    ' & " ORDER BY fec_ing,run_apo,run_alu,me.num_mes,folio_blta_fisico"
                    
 
                    var_sql = var_sql & var_sql1
                    
        
        End If
    End If
    
    
    End If
    
                
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
    Else
       MSFlexGrid1.Clear
       MSFlexGrid1.Rows = 2
       MsgBox "No hay alumnos matriculados en el filtro seleccionado", vbInformation
    End If
    objRS.Close
    MSFlexGrid1.Refresh
    
    Frame2.Visible = False
   
End Sub

Private Sub Command2_Click()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")
    ApExcel.Workbooks.Add
          
    For i = 1 To Me.MSFlexGrid1.Cols - 1
        ApExcel.Cells(1, i).Font.Size = 12
        ApExcel.Cells(1, i).Font.Bold = True
        ApExcel.Cells(1, i).Formula = Me.MSFlexGrid1.TextMatrix(0, i)
    Next i
    ApExcel.Cells(1, 16).Font.Size = 12
    ApExcel.Cells(1, 16).Font.Bold = True
    
    pgbar.Min = 1
    pgbar.Max = MSFlexGrid1.Rows
    pgbar.Value = pgbar.Min
    'ApExcel.Columns("G"). = 300
    ApExcel.Range("G:G").ColumnWidth = 20
    ApExcel.Columns(7).Select
    'ApExcel.Selection.NumberFormat = "#"
    ApExcel.Selection.NumberFormat = "@"
    
'    If cb_factoring.Text <> "" Then
'        ApExcel.Cells(1, 16) = "FACTORING"
'    End If
    
    For i = 1 To MSFlexGrid1.Rows - 1
         For j = 1 To MSFlexGrid1.Cols - 1
            'ApExcel.Cells((i + 1), j).WrapText = True
            If (j = 18 Or j = 19) And fp_mensualidad.Text = "Cheques" Then
                ApExcel.Cells((i + 1), j).NumberFormat = "dd/mm/yyyy"
                ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", Format(MSFlexGrid1.TextMatrix(i, j), "yyyy/mm/dd"))
            Else
                If (j = 8 Or j = 14) And (fp_mensualidad.Text = "Efectivo" Or fp_mensualidad.Text = "Credito" _
                Or fp_mensualidad.Text = "Debito" Or fp_mensualidad.Text = "Transferencia" Or fp_mensualidad.Text = "Upago" _
                Or fp_mensualidad.Text = "Deposito" Or fp_mensualidad.Text = "Cobranza") Then
                    ApExcel.Cells((i + 1), j).NumberFormat = "dd/mm/yyyy"
                    ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", Format(MSFlexGrid1.TextMatrix(i, j), "yyyy/mm/dd"))
                Else
                    If (j = 12) And (fp_mensualidad.Text = "Pagare" Or fp_mensualidad.Text = "PAT") Then
                        ApExcel.Cells((i + 1), j).NumberFormat = "dd/mm/yyyy"
                        ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", Format(MSFlexGrid1.TextMatrix(i, j), "yyyy/mm/dd"))
                    Else
                        ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", MSFlexGrid1.TextMatrix(i, j))
                    End If
                End If
            End If
            
            pgbar.Value = i
         Next j
'         If cb_factoring.Text <> "" Then
'            ApExcel.Cells((i + 1), j) = cb_factoring.Text
'         End If
    Next i
            
    ApExcel.Visible = True
    'ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub




Private Sub Form_Load()
    DTP_inicio.Value = Date
    DTP_hasta.Value = Date
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
        
    var_sql = "SELECT DISTINCT usuario_ingreso FROM matriculas where anno_mat = 2021 and usuario_ingreso <> ''"
    objRS.Open var_sql, conn
    cb_encargado.Clear
    cb_encargado.AddItem "Todos"
    While Not objRS.EOF
        cb_encargado.AddItem objRS("usuario_ingreso")
        cb_encargado.Text = objRS("usuario_ingreso")
        objRS.MoveNext
    Wend
    objRS.Close
    cb_encargado.Text = "Todos"
    
'        ' Matricula
'    Me.fp_matricula.AddItem "Cheques"
'    Me.fp_matricula.AddItem "Efectivo"
'    Me.fp_matricula.AddItem "Credito"
'    Me.fp_matricula.AddItem "Debito"
'    Me.fp_matricula.AddItem "Transferencia"
'    Me.fp_matricula.AddItem "Todos"
'    Me.fp_matricula.Text = "Todos"
'
'    ' Incorporacion
'    Me.fp_incorporacion.AddItem "Cheques"
'    Me.fp_incorporacion.AddItem "Efectivo"
'    Me.fp_incorporacion.AddItem "Credito"
'    Me.fp_incorporacion.AddItem "Debito"
'    Me.fp_incorporacion.AddItem "Transferencia"
'    Me.fp_incorporacion.AddItem "Todos"
'    Me.fp_incorporacion.Text = "Todos"
    
    cole = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    
    If cole = "HUE001" Then
        ' Mensualidad
        Me.fp_mensualidad.AddItem "Cheques"
        Me.fp_mensualidad.AddItem "Efectivo"
        Me.fp_mensualidad.AddItem "Credito"
        Me.fp_mensualidad.AddItem "Debito"
        Me.fp_mensualidad.AddItem "Transferencia"
        Me.fp_mensualidad.AddItem "Pagare"
        Me.fp_mensualidad.AddItem "PAT"
        Me.fp_mensualidad.AddItem "UPago"
        Me.fp_mensualidad.Text = "Efectivo"
    Else
        ' Mensualidad
        Me.fp_mensualidad.AddItem "Cheques"
        Me.fp_mensualidad.AddItem "Efectivo"
        Me.fp_mensualidad.AddItem "Credito"
        Me.fp_mensualidad.AddItem "Debito"
        Me.fp_mensualidad.AddItem "Transferencia"
        Me.fp_mensualidad.AddItem "Upago"
        Me.fp_mensualidad.AddItem "Deposito"
        Me.fp_mensualidad.AddItem "Cobranza"
        Me.fp_mensualidad.Text = "Efectivo"
    End If
    
End Sub






