VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_comportamiento_deuda 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Comportamiento deuda"
   ClientHeight    =   7680
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   13125
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   7680
   ScaleWidth      =   13125
   Begin VB.Frame Frame1 
      Caption         =   "Listado de alumnos"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   6135
      Left            =   120
      TabIndex        =   2
      Top             =   120
      Width           =   12855
      Begin VB.CommandButton Command3 
         Caption         =   "Listar por 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            =   6480
         TabIndex        =   9
         Top             =   360
         Width           =   2535
      End
      Begin VB.Frame Frame2 
         Caption         =   "Frame2"
         Height          =   2175
         Left            =   2160
         TabIndex        =   6
         Top             =   2160
         Visible         =   0   'False
         Width           =   9015
         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            =   2040
            TabIndex        =   8
            Top             =   600
            Width           =   5175
         End
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Listar por alumnos"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   9600
         TabIndex        =   5
         Top             =   360
         Width           =   2535
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   5055
         Left            =   120
         TabIndex        =   3
         Top             =   1080
         Width           =   12615
         _ExtentX        =   22251
         _ExtentY        =   8916
         _Version        =   393216
      End
   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            =   9960
      Picture         =   "Form_comportamiento_deuda.frx":0000
      Style           =   1  'Graphical
      TabIndex        =   1
      Top             =   6720
      Width           =   1575
   End
   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            =   1560
      Picture         =   "Form_comportamiento_deuda.frx":0626
      Style           =   1  'Graphical
      TabIndex        =   0
      Top             =   6720
      Width           =   1575
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   4
      Top             =   6360
      Width           =   12855
      _ExtentX        =   22675
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
   Begin VB.Label Label1 
      Caption         =   "Label1"
      Height          =   495
      Left            =   6000
      TabIndex        =   7
      Top             =   3600
      Width           =   1215
   End
End
Attribute VB_Name = "Form_comportamiento_deuda"
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
        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
            'progreso.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 maximo_mes As Integer
   
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    Frame2.Visible = True
    
    var_sql = "DROP TABLE IF EXISTS temp_d_2024;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_d_2023;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_d_2022;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_d_2024_2023;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_d_2024_2022;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_comportamiento_temp;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_comportamiento;"
    conn.Execute var_sql
            
    maximo_mes = Month(Date)

    If maximo_mes < 4 Then
        maximo_mes = 12
    Else
        maximo_mes = (maximo_mes - 1)
    End If

    cole = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    valor_uf = DevuelveCampo("fnza_valor_UF", "", "valor", "fecha = '" & Format(Date, "yyyy-mm-dd") & "'", conn)

    ' deuda 2024
    If cole = "HUE001" Then
        var_sql = "CREATE TABLE temp_d_2024 TYPE = MYISAM" _
                & " SELECT dm.agno AS 'Año',nom_est AS Establecimiento,CONCAT(CAST(c.num_gra AS CHAR),' ',c.let_cur,' ',IF(c.cod_ens = '996','PRE-KINDER',IF(c.cod_ens = '010','KINDER'," _
                & " IF(c.cod_ens = '110','BASICO',IF(c.cod_ens = '310','MEDIA','PLAY GROUP'))))) curso,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,a.rut_apo,dir_apo AS Direccion," _
                & " fon_c_apo AS Telefono,empresa_fon AS Telefono_empresa,cel_apo AS Celular,IF(telefono_adicional_1 IS NULL,'',telefono_adicional_1) AS telefono_adicional_1," _
                & " IF(telefono_adicional_2 IS NULL,'',telefono_adicional_2) AS telefono_adicional_2,email,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
                & " ROUND(SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota_uf * " & Replace(valor_uf, ",", ".") & "),0))) AS deuda_2024," _
                & " cast(SUM(IF((monto_tot_cuota-monto_abono) >= 13500,monto_tot_cuota_UF,0)) as char) AS deuda_2024_UF," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2024" _
                & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
                & " 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.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2024 AND dm.mes >= 3 AND dm.mes <= " & maximo_mes & "" _
                & " AND a.comuna_apo = cg.cod_geo" _
                & " AND DATE_FORMAT(NOW(),'%Y-%m-%d') > fech_venc" _
                & " GROUP BY a.rut_apo,dm.run_alu" _
                & " HAVING SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) > 1" _
                & " ORDER BY c.cod_ens,c.num_gra,c.let_cur,apoderado;"
    Else
        var_sql = "CREATE TABLE temp_d_2024 TYPE = MYISAM" _
                & " SELECT dm.agno AS 'Año',nom_est AS Establecimiento,CONCAT(CAST(c.num_gra AS CHAR),' ',c.let_cur,' ',IF(c.cod_ens = '996','PRE-KINDER',IF(c.cod_ens = '010','KINDER'," _
                & " IF(c.cod_ens = '110','BASICO',IF(c.cod_ens = '310','MEDIA','PLAY GROUP'))))) curso,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,a.rut_apo,dir_apo AS Direccion," _
                & " fon_c_apo AS Telefono,empresa_fon AS Telefono_empresa,cel_apo AS Celular,IF(telefono_adicional_1 IS NULL,'',telefono_adicional_1) AS telefono_adicional_1," _
                & " IF(telefono_adicional_2 IS NULL,'',telefono_adicional_2) AS telefono_adicional_2,email,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota-monto_abono),0)) AS deuda_2024," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2024" _
                & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
                & " 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.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2024 AND dm.mes >= 3 AND dm.mes <= " & maximo_mes & "" _
                & " AND a.comuna_apo = cg.cod_geo" _
                & " AND DATE_FORMAT(NOW(),'%Y-%m-%d') > fech_venc" _
                & " GROUP BY a.rut_apo,dm.run_alu" _
                & " HAVING SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) > 1" _
                & " ORDER BY c.cod_ens,c.num_gra,c.let_cur,apoderado;"
    End If
    conn.Execute var_sql
    
    ' deuda 2023
    If cole = "HUE001" Then
       var_sql = "CREATE TABLE temp_d_2023 TYPE = MYISAM" _
                & " SELECT CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
                & " ROUND(SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota_uf * " & Replace(valor_uf, ",", ".") & "),0))) AS deuda_2023," _
                & " cast(SUM(IF((monto_tot_cuota-monto_abono) >= 13500,monto_tot_cuota_UF,0)) as char) AS deuda_2023_UF," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2023," _
                & " SUM(IF(fech_pago > DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_atrasado_2023," _
                & " SUM(IF(fech_pago < DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_al_dia_2023" _
                & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
                & " 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 = 2023" _
                & " AND dm.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2023" _
                & " AND a.comuna_apo = cg.cod_geo" _
                & " GROUP BY a.rut_apo,dm.run_alu;"
    Else
        var_sql = "CREATE TABLE temp_d_2023 TYPE = MYISAM" _
                & " SELECT CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota-monto_abono),0)) AS deuda_2023," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2023," _
                & " SUM(IF(fech_pago > DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_atrasado_2023," _
                & " SUM(IF(fech_pago < DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_al_dia_2023" _
                & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
                & " 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 = 2023" _
                & " AND dm.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2023" _
                & " AND a.comuna_apo = cg.cod_geo" _
                & " GROUP BY a.rut_apo,dm.run_alu;"
    End If
    conn.Execute var_sql
    
    ' deuda 2022
    var_sql = "CREATE TABLE temp_d_2022 TYPE = MYISAM" _
            & " SELECT CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
            & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota-monto_abono),0)) AS deuda_2022," _
            & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2022," _
            & " SUM(IF(fech_pago > DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_atrasado_2022," _
            & " SUM(IF(fech_pago < DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_al_dia_2022" _
            & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
            & " 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 = 2022" _
            & " AND dm.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2022" _
            & " AND a.comuna_apo = cg.cod_geo" _
            & " GROUP BY a.rut_apo,dm.run_alu;"
    conn.Execute var_sql
    
    If cole = "HUE001" Then
        var_sql = "CREATE TABLE temp_d_2024_2023 TYPE = MYISAM" _
                & " SELECT d14.*,deuda_2023,deuda_2023_UF,cantidad_cuotas_adeudadas_2023,pagado_atrasado_2023,pagado_al_dia_2023" _
                & " FROM temp_d_2024 AS d14 LEFT JOIN temp_d_2023 AS d13 ON d14.run_alu = d13.run_alu;"
        conn.Execute var_sql
    Else
        var_sql = "CREATE TABLE temp_d_2024_2023 TYPE = MYISAM" _
                & " SELECT d14.*,deuda_2023,cantidad_cuotas_adeudadas_2023,pagado_atrasado_2023,pagado_al_dia_2023" _
                & " FROM temp_d_2024 AS d14 LEFT JOIN temp_d_2023 AS d13 ON d14.run_alu = d13.run_alu;"
        conn.Execute var_sql
    End If

    var_sql = "CREATE TABLE temp_d_2024_2022 TYPE = MYISAM" _
            & " SELECT d14.*,deuda_2022,cantidad_cuotas_adeudadas_2022,pagado_atrasado_2022,pagado_al_dia_2022" _
            & " FROM temp_d_2024_2023 AS d14 LEFT JOIN temp_d_2022 AS d12 ON d14.run_alu = d12.run_alu;"
    conn.Execute var_sql

    var_sql = "CREATE TABLE temp_comportamiento_temp TYPE = MYISAM SELECT d.*,IF(c.ci_alu IS NULL,'NUEVO','ANTIGUO') AS tipo_alumno FROM temp_d_2024_2022 d" _
            & " Left Join" _
            & " (SELECT * FROM alu_x_cur WHERE agno = 2023 AND activo_alu = 'SI') c" _
            & " ON d.run_alu = c.ci_alu;"
    conn.Execute var_sql

    var_sql = "CREATE TABLE temp_comportamiento TYPE = MYISAM SELECT c.*,cantidad_protestos_2024,monto_protestos_2024 FROM temp_comportamiento_temp AS c" _
            & " LEFT JOIN (SELECT ci_apo,COUNT(numero_cheque) cantidad_protestos_2024,SUM(monto_cheque) monto_protestos_2024" _
            & " FROM fnza_lista_negra_apoderados WHERE agno = 2024 AND bloqueado = 1" _
            & " GROUP BY ci_apo) AS p" _
            & " ON c.rut_apo = p.ci_apo"
    conn.Execute var_sql
    
    var_sql = "SELECT t.*,IF(m.ci_alu IS NULL,'NO','SI') matriculado_2025 FROM temp_comportamiento t" _
            & " Left Join (SELECT * FROM matriculas WHERE anno_mat = 2025 AND fec_mat_r IS NOT NULL AND confirmado = 'SI') AS m" _
            & " ON t.run_alu = m.ci_alu"
    
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
       ' msflex_alumnos.ColSel = 5
    Else
        'MsgBox "Esta mensualidad aún no ha vencido", vbInformation
    End If
    objRS.Close
    MSFlexGrid1.Refresh
    
    Frame2.Visible = False
    
End Sub

Private Sub Command2_Click()
    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
    pgbar.Min = 1
    pgbar.Max = MSFlexGrid1.Rows
    pgbar.Value = pgbar.Min
    For I = 1 To MSFlexGrid1.Rows - 1

         For j = 1 To MSFlexGrid1.Cols - 1
            ApExcel.Cells((I + 1), j).Formula = IIf(MSFlexGrid1.TextMatrix(I, j) = "Null", "", MSFlexGrid1.TextMatrix(I, j))
        
        pgbar.Value = I
        Next j
    Next I
            
    ApExcel.Visible = True
    ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub



Private Sub Command3_Click()
    Dim var_sql As String
    Dim maximo_mes As Integer
   
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    Frame2.Visible = True
    
    var_sql = "DROP TABLE IF EXISTS temp_d_2024;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_d_2023;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_d_2022;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_d_2024_2023;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_d_2024_2022;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_comportamiento_temp;"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_comportamiento;"
    conn.Execute var_sql
    
    
    
    maximo_mes = Month(Date)

    If maximo_mes < 4 Then
        maximo_mes = 12
    Else
        maximo_mes = (maximo_mes - 1)
    End If
    cole = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    valor_uf = DevuelveCampo("fnza_valor_UF", "", "valor", "fecha = '" & Format(Date, "yyyy-mm-dd") & "'", conn)

    ' deuda 2024
    If cole <> "HUE001" Then
        var_sql = "CREATE TABLE temp_d_2024 TYPE = MYISAM" _
                & " SELECT dm.agno AS 'Año',nom_est AS Establecimiento,CONCAT(CAST(c.num_gra AS CHAR),' ',c.let_cur,' ',IF(c.cod_ens = '996','PRE-KINDER',IF(c.cod_ens = '010','KINDER'," _
                & " IF(c.cod_ens = '110','BASICO',IF(c.cod_ens = '310','MEDIA','PLAY GROUP'))))) curso,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,a.rut_apo,dir_apo AS Direccion," _
                & " fon_c_apo AS Telefono,empresa_fon AS Telefono_empresa,cel_apo AS Celular,IF(telefono_adicional_1 IS NULL,'',telefono_adicional_1) AS telefono_adicional_1," _
                & " IF(telefono_adicional_2 IS NULL,'',telefono_adicional_2) AS telefono_adicional_2,email,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota-monto_abono),0)) AS deuda_2024," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2024" _
                & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
                & " 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.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2024 AND dm.mes >= 3 AND dm.mes <= " & maximo_mes & "" _
                & " AND a.comuna_apo = cg.cod_geo" _
                & " AND DATE_FORMAT(NOW(),'%Y-%m-%d') > fech_venc" _
                & " GROUP BY a.rut_apo,dm.run_alu" _
                & " HAVING SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) > 1" _
                & " ORDER BY c.cod_ens,c.num_gra,c.let_cur,apoderado;"
    Else
        var_sql = "CREATE TABLE temp_d_2024 TYPE = MYISAM" _
                & " SELECT dm.agno AS 'Año',nom_est AS Establecimiento,CONCAT(CAST(c.num_gra AS CHAR),' ',c.let_cur,' ',IF(c.cod_ens = '996','PRE-KINDER',IF(c.cod_ens = '010','KINDER'," _
                & " IF(c.cod_ens = '110','BASICO',IF(c.cod_ens = '310','MEDIA','PLAY GROUP'))))) curso,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,a.rut_apo,dir_apo AS Direccion," _
                & " fon_c_apo AS Telefono,empresa_fon AS Telefono_empresa,cel_apo AS Celular,IF(telefono_adicional_1 IS NULL,'',telefono_adicional_1) AS telefono_adicional_1," _
                & " IF(telefono_adicional_2 IS NULL,'',telefono_adicional_2) AS telefono_adicional_2,email,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
                & " ROUND(SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota_uf * " & Replace(valor_uf, ",", ".") & "),0))) AS deuda_2024," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,monto_tot_cuota_UF,0)) AS deuda_2024_UF," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2024" _
                & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
                & " 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.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2024 AND dm.mes >= 3 AND dm.mes <= " & maximo_mes & "" _
                & " AND a.comuna_apo = cg.cod_geo" _
                & " AND DATE_FORMAT(NOW(),'%Y-%m-%d') > fech_venc" _
                & " GROUP BY a.rut_apo,dm.run_alu" _
                & " HAVING SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) > 1" _
                & " ORDER BY c.cod_ens,c.num_gra,c.let_cur,apoderado;"
    
    End If
    conn.Execute var_sql
    
    ' deuda 2023
    If cole = "HUE001" Then
        var_sql = "CREATE TABLE temp_d_2023 TYPE = MYISAM" _
                & " SELECT CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
                & " ROUND(SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota_uf * " & Replace(valor_uf, ",", ".") & "),0))) AS deuda_2023," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,monto_tot_cuota_UF,0)) AS deuda_2023_UF," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2023," _
                & " SUM(IF(fech_pago > DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_atrasado_2023," _
                & " SUM(IF(fech_pago < DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_al_dia_2023" _
                & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
                & " 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 = 2023" _
                & " AND dm.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2023" _
                & " AND a.comuna_apo = cg.cod_geo" _
                & " GROUP BY a.rut_apo,dm.run_alu;"
    Else
        var_sql = "CREATE TABLE temp_d_2023 TYPE = MYISAM" _
                & " SELECT CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota-monto_abono),0)) AS deuda_2023," _
                & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2023," _
                & " SUM(IF(fech_pago > DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_atrasado_2023," _
                & " SUM(IF(fech_pago < DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_al_dia_2023" _
                & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
                & " 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 = 2023" _
                & " AND dm.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2023" _
                & " AND a.comuna_apo = cg.cod_geo" _
                & " GROUP BY a.rut_apo,dm.run_alu;"
    End If
    conn.Execute var_sql
    
    ' deuda 2022
    var_sql = "CREATE TABLE temp_d_2022 TYPE = MYISAM" _
            & " SELECT CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,dm.run_alu," _
            & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(monto_tot_cuota-monto_abono),0)) AS deuda_2022," _
            & " SUM(IF((monto_tot_cuota-monto_abono) >= 13500,(1),0)) AS cantidad_cuotas_adeudadas_2022," _
            & " SUM(IF(fech_pago > DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_atrasado_2022," _
            & " SUM(IF(fech_pago < DATE_FORMAT(DATE_ADD(fech_venc,INTERVAL 1 MONTH),'%Y-%m-03'),monto_abono,0)) AS pagado_al_dia_2022" _
            & " FROM establecimientos,fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a,codigos__geograficos cg" _
            & " 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 = 2022" _
            & " AND dm.mes <> 2 AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno = 2022" _
            & " AND a.comuna_apo = cg.cod_geo" _
            & " GROUP BY a.rut_apo,dm.run_alu;"
    conn.Execute var_sql
    
    If cole = "HUE001" Then
        var_sql = "CREATE TABLE temp_d_2024_2023 TYPE = MYISAM" _
                & " SELECT d14.*,deuda_2023,deuda_2023_UF,cantidad_cuotas_adeudadas_2023,pagado_atrasado_2023,pagado_al_dia_2023" _
                & " FROM temp_d_2024 AS d14 LEFT JOIN temp_d_2023 AS d13 ON d14.run_alu = d13.run_alu;"
        conn.Execute var_sql
    Else
         var_sql = "CREATE TABLE temp_d_2024_2023 TYPE = MYISAM" _
                & " SELECT d14.*,deuda_2023,cantidad_cuotas_adeudadas_2023,pagado_atrasado_2023,pagado_al_dia_2023" _
                & " FROM temp_d_2024 AS d14 LEFT JOIN temp_d_2023 AS d13 ON d14.run_alu = d13.run_alu;"
        conn.Execute var_sql
    End If

    var_sql = "CREATE TABLE temp_d_2024_2022 TYPE = MYISAM" _
            & " SELECT d14.*,deuda_2022,cantidad_cuotas_adeudadas_2022,pagado_atrasado_2022,pagado_al_dia_2022" _
            & " FROM temp_d_2024_2023 AS d14 LEFT JOIN temp_d_2022 AS d12 ON d14.run_alu = d12.run_alu;"
    conn.Execute var_sql

    var_sql = "CREATE TABLE temp_comportamiento_temp TYPE = MYISAM SELECT d.*,IF(c.ci_alu IS NULL,'NUEVO','ANTIGUO') AS tipo_alumno FROM temp_d_2024_2022 d" _
            & " Left Join" _
            & " (SELECT * FROM alu_x_cur WHERE agno = 2023 AND activo_alu = 'SI') c" _
            & " ON d.run_alu = c.ci_alu;"
    conn.Execute var_sql

    var_sql = "CREATE TABLE temp_comportamiento TYPE = MYISAM SELECT c.*,cantidad_protestos_2024,monto_protestos_2024 FROM temp_comportamiento_temp AS c" _
            & " LEFT JOIN (SELECT ci_apo,COUNT(numero_cheque) cantidad_protestos_2024,SUM(monto_cheque) monto_protestos_2024" _
            & " FROM fnza_lista_negra_apoderados WHERE agno = 2024 AND bloqueado = 1" _
            & " GROUP BY ci_apo) AS p" _
            & " ON c.rut_apo = p.ci_apo"
    conn.Execute var_sql
    
    var_sql = "SELECT t.*,IF(m.ci_alu IS NULL,'NO','SI') matriculado_2025 FROM temp_comportamiento t" _
            & " Left Join (SELECT * FROM matriculas WHERE anno_mat = 2025 AND fec_mat_r IS NOT NULL AND confirmado = 'SI') AS m" _
            & " ON t.run_alu = m.ci_alu"
            
    If cole = "HUE001" Then
        var_sql = "SELECT Año,Establecimiento,apoderado,rut_apo,Direccion,Telefono,Telefono_empresa,Celular,telefono_adicional_1,telefono_adicional_2,email,SUM(deuda_2024) AS deuda_2024,cast(SUM(deuda_2024_UF) as char) AS deuda_2024_UF,cantidad_cuotas_adeudadas_2024," _
                & "SUM(deuda_2023) AS deuda_2023,cast(SUM(deuda_2023_UF) as char) AS deuda_2023_UF,cantidad_cuotas_adeudadas_2023,SUM(pagado_atrasado_2023) AS pagado_atrasado_2023,SUM(pagado_al_dia_2023) AS pagado_al_dia_2023," _
                & "SUM(deuda_2022) AS deuda_2022,cantidad_cuotas_adeudadas_2022,SUM(pagado_atrasado_2022) AS pagado_atrasado_2022,SUM(pagado_al_dia_2022) AS pagado_al_dia_2022," _
                & "cantidad_protestos_2024,monto_protestos_2024,IF(m.ci_alu IS NULL,'NO','SI') matriculado_2025" _
                & " FROM temp_comportamiento t LEFT JOIN (SELECT * FROM matriculas WHERE anno_mat = 2025 AND fec_mat_r IS NOT NULL AND confirmado = 'SI') AS m ON t.run_alu = m.ci_alu" _
                & " GROUP BY rut_apo"
    Else
        var_sql = "SELECT Año,Establecimiento,apoderado,rut_apo,Direccion,Telefono,Telefono_empresa,Celular,telefono_adicional_1,telefono_adicional_2,email,SUM(deuda_2024) AS deuda_2024,cantidad_cuotas_adeudadas_2024," _
                & "SUM(deuda_2023) AS deuda_2023,cantidad_cuotas_adeudadas_2023,SUM(pagado_atrasado_2023) AS pagado_atrasado_2023,SUM(pagado_al_dia_2023) AS pagado_al_dia_2023," _
                & "SUM(deuda_2022) AS deuda_2022,cantidad_cuotas_adeudadas_2022,SUM(pagado_atrasado_2022) AS pagado_atrasado_2022,SUM(pagado_al_dia_2022) AS pagado_al_dia_2022," _
                & "cantidad_protestos_2024,monto_protestos_2024,IF(m.ci_alu IS NULL,'NO','SI') matriculado_2025" _
                & " FROM temp_comportamiento t LEFT JOIN (SELECT * FROM matriculas WHERE anno_mat = 2025 AND fec_mat_r IS NOT NULL AND confirmado = 'SI') AS m ON t.run_alu = m.ci_alu" _
                & " GROUP BY rut_apo"
    End If
    
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
       ' msflex_alumnos.ColSel = 5
    Else
        'MsgBox "Esta mensualidad aún no ha vencido", vbInformation
    End If
    objRS.Close
    MSFlexGrid1.Refresh
    
    Frame2.Visible = False
    
End Sub
