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_carga_upago 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Archivo de carga para Upago"
   ClientHeight    =   8025
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   13125
   Icon            =   "Form_carga_upago.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   8025
   ScaleWidth      =   13125
   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            =   1200
      Picture         =   "Form_carga_upago.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   6
      Top             =   6960
      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            =   10320
      Picture         =   "Form_carga_upago.frx":2AE98
      Style           =   1  'Graphical
      TabIndex        =   5
      Top             =   6960
      Width           =   1575
   End
   Begin VB.Frame Frame1 
      Caption         =   "Apoderados con deuda"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   6495
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   12855
      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            =   9600
         TabIndex        =   3
         Top             =   360
         Width           =   2535
      End
      Begin VB.Frame Frame2 
         Height          =   2415
         Left            =   1920
         TabIndex        =   1
         Top             =   2640
         Visible         =   0   'False
         Width           =   9135
         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        =   2
            Top             =   600
            Width           =   5175
         End
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   5175
         Left            =   120
         TabIndex        =   4
         Top             =   1200
         Width           =   12615
         _ExtentX        =   22251
         _ExtentY        =   9128
         _Version        =   393216
      End
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   7
      Top             =   6720
      Width           =   12855
      _ExtentX        =   22675
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
End
Attribute VB_Name = "Form_carga_upago"
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)
Dim Variable As String
Variable = "(*)"
    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)
            If rec.Fields(I - 1).Name = "Pasaporte" Then
                grid.TextMatrix(0, I) = "Rut o Pasaporte(*)"
            ElseIf rec.Fields(I - 1).Name = "Celular" Or rec.Fields(I - 1).Name = "Concepto" Or rec.Fields(I - 1).Name = "InteresMora" _
                   Or rec.Fields(I - 1).Name = "DiasMora" Or rec.Fields(I - 1).Name = "DetalleMora" Then
                grid.TextMatrix(0, I) = rec.Fields(I - 1).Name
            Else
                grid.TextMatrix(0, I) = rec.Fields(I - 1).Name & Variable
            End If
            
            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
    
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")

    ApExcel.Workbooks.Add
    
 


    

cole = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
If cole = "FLO001" Then correo = "lf@lincolncollege.cl"
If cole = "PUD001" Then correo = "pudahuel@lincolncollege.cl"
If cole = "SNB001" Then correo = "sb@lincolncollege.cl"
If cole = "SNM001" Then correo = "sm@lincolncollege.cl"
If cole = "HUE001" Then correo = "huechuraba@lincolncollege.cl"

If cole <> "HUE001" Then
    crea_temporales_apoderados
End If

If cole = "HUE001" Then
'''''    var_sql = "SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8," _
'''''            & "'" & correo & "',IF(email IS NULL,'" & correo & "',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2021-02-10','1 de 11','Mensualidad') AS Concepto," _
'''''            & "IF(fech_venc = '2020-02-10','1 de 11','Mensualidad') AS Descripcion,'UF' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota '," _
'''''            & "IF(fech_venc = '2020-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion,SUM(monto_tot_cuota_UF) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora," _
'''''            & "IF(fech_venc = '2020-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento" _
'''''            & " FROM fnza_detalle_mensualidad dm,(SELECT * FROM matriculas WHERE confirmado = 'SI' AND fec_mat_r IS NOT NULL AND anno_mat = 2022 GROUP BY ci_alu) c,apo_x_alu axc,apoderados a,meses m" _
'''''            & " WHERE dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu" _
'''''            & " AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2022" _
'''''            & " AND monto_tot_cuota > 0 AND monto_abono = 0 AND dm.mes > 2 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL AND fp_mensualidad = 'Cuponera'" _
'''''            & " AND a.rut_apo NOT IN (SELECT rut_apo FROM fnza_bloqueados_x_pagare) GROUP BY a.rut_apo,dm.mes" _
'''''            & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Rut,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre," _
'''''            & "IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email," _
'''''            & "IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2023-02-10','1 de 11','Mensualidad') AS Concepto," _
'''''            & "IF(fech_venc = '2023-02-10','1 de 11','Mensualidad') AS Descripcion,'UF' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota '," _
'''''            & "IF(fech_venc = '2023-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion2,SUM(monto_tot_cuota_UF) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora," _
'''''            & "IF(fech_venc = '2023-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento" _
'''''            & " FROM fnza_detalle_mensualidad dm,(SELECT * FROM matriculas WHERE confirmado = 'SI' AND fec_mat_r IS NOT NULL AND anno_mat = 2023 GROUP BY ci_alu) c,apo_x_alu axc,apoderados a,meses m" _
'''''            & " Where dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu And dm.agno = c.anno_mat And c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2023 AND monto_tot_cuota > 0 AND monto_abono = 0 AND dm.mes > 2" _
'''''            & " AND confirmado = 'SI' AND fec_mat_r IS NOT NULL AND fp_mensualidad = 'Cuponera' AND a.rut_apo NOT IN (SELECT rut_apo FROM fnza_bloqueados_x_pagare) GROUP BY a.rut_apo,dm.mes"
            
    ''' procedimiento que crea tabla con bloqueos
    ''' Call bloqueo_deuda_cupon
          
    var_sql = "SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2021-02-10','1 de 11','Mensualidad') AS Concepto," _
            & "IF(fech_venc = '2020-02-10','1 de 11','Mensualidad') AS Descripcion,'UF' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2020-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion,SUM(monto_tot_cuota_UF) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,IF(fech_venc = '2020-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento FROM fnza_detalle_mensualidad dm," _
            & "(SELECT * FROM matriculas WHERE confirmado = 'SI' AND fec_mat_r IS NOT NULL AND anno_mat = 2022 GROUP BY ci_alu) c,apo_x_alu axc,apoderados a,meses m Where dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu And dm.agno = c.anno_mat And c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2022 AND monto_tot_cuota > 0 AND monto_abono = 0 AND dm.mes > 2 AND confirmado = 'SI'" _
            & " AND fec_mat_r IS NOT NULL AND fp_mensualidad = 'Cuponera' AND a.rut_apo NOT IN (SELECT rut_apo FROM fnza_bloqueados_x_pagare) AND a.rut_apo NOT IN (SELECT run_apo FROM fnza_mensualidad_pagare WHERE monto_pagare > 0 AND pagare_pagado = 'NO' AND run_apo <> '18738296-3') AND a.rut_apo NOT IN (SELECT rut FROM fnza_bloqueados_pagare_2024) AND a.rut_apo NOT IN (SELECT run_apo FROM fnza_mensualidad_pagare_2022 WHERE monto_pagare > 0) GROUP BY a.rut_apo,dm.mes" _
            & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl'," _
            & "IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular," _
            & "IF(fech_venc = '2021-02-10','1 de 11','Repactacion') AS Concepto,IF(fech_venc = '2020-02-10','1 de 11','Repactacion') AS Descripcion,'CLP' AS Moneda," _
            & "CONCAT('REPAC ',MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2020-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion,SUM(monto_pagare) AS MontoCapital,0 AS InteresMora," _
            & "'' AS DiasMora,'' AS DetalleMora,DATE_FORMAT(DATE(DATE_ADD(fech_venc, INTERVAL 1 YEAR)),'%d-%m-%Y') AS Vencimiento" _
            & " FROM fnza_mensualidad_pagare dm,apoderados a,meses m" _
            & " Where dm.run_apo = a.rut_apo And dm.mes = m.num_mes And dm.agno = 2022 And monto_pagare > 0 And dm.mes > 2" _
            & " AND a.rut_apo NOT IN (SELECT rut_apo FROM fnza_bloqueados_x_pagare) AND pagare_pagado = 'NO'" _
            & " GROUP BY a.rut_apo,dm.mes" _
            & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Rut,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre," _
            & "IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email," _
            & "IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2023-02-10','1 de 11','Mensualidad') AS Concepto," _
            & "IF(fech_venc = '2023-02-10','1 de 11','Mensualidad') AS Descripcion,'UF' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota '," _
            & "IF(fech_venc = '2023-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion2,SUM(monto_tot_cuota_UF) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora," _
            & "IF(fech_venc = '2023-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento" _
            & " FROM fnza_detalle_mensualidad dm,(SELECT * FROM matriculas WHERE confirmado = 'SI' AND fec_mat_r IS NOT NULL AND anno_mat = 2023 GROUP BY ci_alu) c,apo_x_alu axc,apoderados a,meses m" _
            & " Where dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu And dm.agno = c.anno_mat And c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2023 AND monto_tot_cuota > 0 AND monto_abono = 0 AND dm.mes > 2" _
            & " AND confirmado = 'SI' AND fec_mat_r IS NOT NULL AND fp_mensualidad = 'Cuponera' AND concepto = 'esco' AND cuota_pagare IS NULL AND a.rut_apo NOT IN (SELECT rut FROM fnza_bloqueados_pagare_2024) AND a.rut_apo NOT IN (SELECT run_apo FROM fnza_mensualidad_pagare_2023 WHERE monto_pagare > 0 AND pagare_pagado = 'NO') /* AND a.rut_apo IN (SELECT rut_apo FROM temp_deuda_motivo_bloqueo) AND c.ci_alu IN (SELECT rut FROM temp_deuda_motivo_bloqueo) */" _
            & " GROUP BY a.rut_apo,dm.mes"
            
    var_sql = var_sql & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2021-02-10','1 de 11','Repactacion') AS Concepto,IF(fech_venc = '2020-02-10','1 de 11','Repactacion') AS Descripcion,'UF' AS Moneda,CONCAT('REPAC ',MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2020-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion,SUM(monto_pagare) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,DATE_FORMAT(vencimiento,'%d-%m-%Y') AS Vencimiento FROM fnza_mensualidad_pagare_2023 dm,apoderados a,meses m WHERE dm.run_apo = a.rut_apo" _
            & " AND dm.mes = m.num_mes AND dm.agno = 2023 AND monto_pagare > 0 AND dm.mes >= 1 AND a.rut_apo NOT IN (SELECT rut_apo FROM fnza_bloqueados_x_pagare) AND pagare_pagado = 'NO' GROUP BY a.rut_apo,dm.mes"
    
    var_sql = var_sql & " UNION SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2021-02-10','1 de 11','Repactacion') AS Concepto,IF(fech_venc = '2020-02-10','1 de 11','Repactacion') AS Descripcion,'CLP' AS Moneda,CONCAT('REPAC ',MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2020-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion,SUM(monto_pagare) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,DATE_FORMAT(vencimiento,'%d-%m-%Y') AS Vencimiento FROM fnza_mensualidad_pagare_2022 dm,apoderados a,meses m WHERE dm.run_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2022 AND monto_pagare > 0 AND dm.mes >= 1" _
            & " AND pagare_pagado = 'NO' GROUP BY a.rut_apo,dm.mes"
            
''    var_sql = var_sql & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Rut,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular," _
''            & "'Matricula 2024' AS Concepto,'Matricula_2024' AS Descripcion,'CLP' AS Moneda,'HUE Cuota Matricula 2024' AS Descripcion2,SUM(arancel_base) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,'2023-11-30' AS Vencimiento FROM (SELECT c.*,CONCAT(cod_ens_sig,num_gra_sig) AS curso_sig FROM alu_x_cur c,curso_siguiente cs WHERE (c.cod_ens = cs.cod_ens) AND (c.num_gra = cs.num_gra) AND agno = 2023 ) c," _
''            & " fnza_descuento d,apo_x_alu a,apoderados ap WHERE (c.agno + 1) = d.agno AND c.curso_sig = d.curso AND c.ci_alu = a.ci_alu AND c.agno = a.agno AND a.rut_apo = ap.rut_apo AND c.agno = 2023 AND activo_alu = 'SI' AND NOT (cod_ens = '310' AND num_gra = 4) AND a.rut_apo NOT IN (SELECT rut_apo FROM temp_deuda_motivo_bloqueo) AND a.rut_apo NOT IN (SELECT rut_apo FROM temp_funcionarios_2023)" _
''            & " AND c.ci_alu NOT IN (SELECT rut FROM temp_deuda_motivo_bloqueo) AND c.ci_alu NOT IN (SELECT rut FROM fnza_bloqueados_condicionales WHERE agno = 2024) AND c.ci_alu NOT IN (SELECT ci_alu FROM matriculas WHERE anno_mat = 2024 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL) GROUP BY a.rut_apo" _
''            & " Union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Rut,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,'Matricula 2024' AS Concepto,'Matricula_2024' AS Descripcion,'CLP' AS Moneda,'HUE Cuota Matricula 2024' AS Descripcion2,ROUND((SUM(arancel_base) * 0.6)) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,'2023-11-30' AS Vencimiento" _
''            & " FROM (SELECT c.*,CONCAT(cod_ens_sig,num_gra_sig) AS curso_sig FROM alu_x_cur c,curso_siguiente cs WHERE (c.cod_ens = cs.cod_ens) AND (c.num_gra = cs.num_gra) AND agno = 2023 ) c,fnza_descuento d,apo_x_alu a,apoderados ap WHERE (c.agno + 1) = d.agno AND c.curso_sig = d.curso AND c.ci_alu = a.ci_alu AND c.agno = a.agno AND a.rut_apo = ap.rut_apo AND c.agno = 2023 AND activo_alu = 'SI' AND NOT (cod_ens = '310' AND num_gra = 4) AND a.rut_apo NOT IN (SELECT rut_apo FROM temp_deuda_motivo_bloqueo) AND a.rut_apo IN (SELECT rut_apo FROM temp_funcionarios_2023)" _
''            & " AND c.ci_alu NOT IN (SELECT rut FROM temp_deuda_motivo_bloqueo) AND c.ci_alu NOT IN (SELECT rut FROM fnza_bloqueados_condicionales WHERE agno = 2024) AND c.ci_alu NOT IN (SELECT ci_alu FROM matriculas WHERE anno_mat = 2024 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL) GROUP BY a.rut_apo"
            
   '' var_sql = var_sql & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Rut,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2023-02-10','1 de 11','Mensualidad') AS Concepto,IF(fech_venc = '2023-02-10','1 de 11','Mensualidad') AS Descripcion,'UF' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2023-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion2,SUM(monto_tot_cuota_UF) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora," _
   ''         & "IF(fech_venc = '2023-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento FROM fnza_detalle_mensualidad dm,(SELECT * FROM matriculas WHERE confirmado = 'SI' AND fec_mat_r IS NOT NULL AND anno_mat = 2023 GROUP BY ci_alu) c,apo_x_alu axc,apoderados a,meses m Where dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu And dm.agno = c.anno_mat And c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2023 AND monto_tot_cuota > 0 AND monto_abono = 0 AND dm.mes > 11 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL AND fp_mensualidad = 'Cuponera' AND concepto = 'esco' AND cuota_pagare IS NULL AND a.rut_apo NOT IN (SELECT rut FROM fnza_bloqueados_pagare_2024)" _
   ''         & " AND a.rut_apo NOT IN (SELECT rut_apo FROM temp_deuda_motivo_bloqueo) AND ((c.ci_alu IN (SELECT ci_alu FROM matriculas WHERE anno_mat = 2024 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL)) OR (c.cod_ens = '310' AND c.num_gra = 4)) GROUP BY a.rut_apo,dm.mes"
    
    var_sql = var_sql & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Rut,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'huechuraba@lincolncollege.cl',IF(email IS NULL,'huechuraba@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2023-02-10','1 de 11','Mensualidad') AS Concepto,IF(fech_venc = '2023-02-10','1 de 11','Mensualidad') AS Descripcion,'UF' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2023-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion2,SUM(monto_tot_cuota_UF) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora," _
            & "IF(fech_venc = '2023-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento FROM fnza_detalle_mensualidad dm,(SELECT * FROM matriculas WHERE confirmado = 'SI' AND fec_mat_r IS NOT NULL AND anno_mat = 2024 GROUP BY ci_alu) c,apo_x_alu axc,apoderados a,meses m Where dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu And dm.agno = c.anno_mat And c.anno_mat = axc.agno And axc.rut_apo = a.rut_apo And dm.mes = m.num_mes AND dm.agno = 2024 AND monto_tot_cuota > 0 AND monto_abono = 0 AND dm.mes > 2 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL AND fp_mensualidad = 'Cuponera' AND concepto = 'esco'GROUP BY a.rut_apo,dm.mes"
            
Else
    var_sql = "DROP TABLE IF EXISTS temp_apo_activo;"
    conn.Execute var_sql
    var_sql = "CREATE TABLE temp_apo_activo TYPE = MYISAM SELECT rut_apo FROM alu_x_cur c,apo_x_alu a WHERE c.ci_alu = a.ci_alu AND c.agno = a.agno AND c.agno = 2023 AND activo_alu = 'SI';"
    conn.Execute var_sql
    
    var_sql = "SELECT * FROM (/*SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'" & correo & "',IF(email IS NULL,'" & correo & "',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(concepto = 'esco','Mensualidad','1 de 11') AS Concepto,IF(concepto = 'esco','Mensualidad','1 de 11') AS Descripcion,'CLP' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota 1 de 11 ',dm.agno) AS Descripcion," _
            & "SUM(monto_tot_cuota-monto_abono) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,DATE_FORMAT(fech_venc,'%05-03-%2024') AS Vencimiento FROM fnza_detalle_mensualidad dm,matriculas c,apo_x_alu axc,apoderados a,meses m WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2024 AND monto_tot_cuota > 0 AND (monto_tot_cuota - monto_abono) > 20000" _
            & " AND dm.mes = 2 AND ind_cup = 1 AND fec_mat_r IS NULL AND dm.run_alu NOT IN (SELECT rut FROM fnza_desertores) GROUP BY a.rut_apo,dm.mes Union*/ " _
            & " SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'" & correo & "',IF(email IS NULL,'" & correo & "',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2021-02-10','1 de 11','Mensualidad') AS Concepto,IF(fech_venc = '2021-02-10','1 de 11','Mensualidad') AS Descripcion,'CLP' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2020-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion," _
            & "SUM(monto_tot_cuota-monto_abono) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,IF(fech_venc = '2020-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento FROM fnza_detalle_mensualidad dm,matriculas c,apo_x_alu axc,apoderados a,meses m WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno" _
            & " AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2020 AND monto_tot_cuota > 0 AND (monto_tot_cuota - monto_abono) > 20000 and dm.mes > 2" _
            & " and confirmado = 'SI' and fec_mat_r is not null AND ind_cup = 1 AND (c.ci_alu IN (SELECT ci_alu FROM alu_x_cur WHERE agno = 2024 AND activo_alu = 'SI') OR a.rut_apo IN (SELECT rut_apo FROM temp_apo_activo)) GROUP BY a.rut_apo,dm.mes" _
            & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'" & correo & "',IF(email IS NULL,'" & correo & "',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2021-02-10','1 de 11','Mensualidad') AS Concepto,IF(fech_venc = '2021-02-10','1 de 11','Mensualidad') AS Descripcion,'CLP' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2020-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion," _
            & "SUM(monto_tot_cuota-monto_abono) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,IF(fech_venc = '2020-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento FROM fnza_detalle_mensualidad dm,matriculas c,apo_x_alu axc,apoderados a,meses m WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno" _
            & " AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2021 AND monto_tot_cuota > 0 AND (monto_tot_cuota - monto_abono) > 20000 and dm.mes > 2" _
            & " and confirmado = 'SI' and fec_mat_r is not null AND ind_cup = 1 AND (c.ci_alu IN (SELECT ci_alu FROM alu_x_cur WHERE agno = 2024 AND activo_alu = 'SI') OR a.rut_apo IN (SELECT rut_apo FROM temp_apo_activo)) GROUP BY a.rut_apo,dm.mes" _
            & " union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'" & correo & "',IF(email IS NULL,'" & correo & "',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2022-02-10','1 de 11','Mensualidad') AS Concepto,IF(fech_venc = '2022-02-10','1 de 11','Mensualidad') AS Descripcion,'CLP' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2022-02-10','1 de 11',m.mes),' ',dm.agno) AS Descripcion," _
            & "SUM(monto_tot_cuota-monto_abono) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,IF(fech_venc = '2022-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento FROM fnza_detalle_mensualidad dm,matriculas c,apo_x_alu axc,apoderados a,meses m WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno" _
            & " AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2022 AND monto_tot_cuota > 0 AND (monto_tot_cuota - monto_abono) > 20000 and dm.mes > 2" _
            & " and confirmado = 'SI' and fec_mat_r is not null AND ind_cup = 1 AND (c.ci_alu IN (SELECT ci_alu FROM alu_x_cur WHERE agno = 2024 AND activo_alu = 'SI') OR a.rut_apo IN (SELECT rut_apo FROM temp_apo_activo)) GROUP BY a.rut_apo,dm.mes union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'lf@lincolncollege.cl',IF(email IS NULL,'lf@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2022-02-10','1 de 11','Mensualidad') AS Concepto," _
            & "IF(fech_venc = '2022-02-10','1 de 11','Mensualidad') AS Descripcion,'CLP' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2022-02-10','1 de 11',m.mes),' ',year(fech_venc)) AS Descripcion,SUM(monto_tot_cuota-monto_abono) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,IF(fech_venc = '2022-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento FROM fnza_detalle_mensualidad dm,matriculas c,apo_x_alu axc,apoderados a,meses m WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo" _
            & " AND dm.mes = m.num_mes AND dm.agno = 2023 AND monto_tot_cuota > 0 AND (monto_tot_cuota - monto_abono) > 20000 AND dm.mes > 2 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL AND ind_cup = 1 GROUP BY a.rut_apo,dm.mes union SELECT IF(MID(a.rut_apo,1,1) = '0',MID(a.rut_apo,2),a.rut_apo) AS Pasaporte,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS Nombre,IF(LENGTH(email) < 8,'lf@lincolncollege.cl',IF(email IS NULL,'lf@lincolncollege.cl',email)) AS Email,IF(LENGTH(cel_apo) < 8,'56985297070',CONCAT('569',MID(cel_apo,LENGTH(cel_apo) - 7))) AS Celular,IF(fech_venc = '2022-02-10','1 de 11','Mensualidad') AS Concepto," _
            & "IF(fech_venc = '2022-02-10','1 de 11','Mensualidad') AS Descripcion,'CLP' AS Moneda,CONCAT(MID(dm.colegio,1,3),' Cuota ',IF(fech_venc = '2022-02-10','1 de 11',m.mes),' ',year(fech_venc)) AS Descripcion,SUM(monto_tot_cuota-monto_abono) AS MontoCapital,0 AS InteresMora,'' AS DiasMora,'' AS DetalleMora,IF(fech_venc = '2022-02-10',DATE_FORMAT(fech_venc,'%05-03-%Y'),DATE_FORMAT(fech_venc,'%d-%m-%Y')) AS Vencimiento FROM fnza_detalle_mensualidad dm,matriculas c,apo_x_alu axc,apoderados a,meses m WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo" _
            & " AND dm.mes = m.num_mes AND dm.agno = 2024 AND monto_tot_cuota > 0 AND (monto_tot_cuota - monto_abono) > 20000 AND dm.mes > 2 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL AND ind_cup = 1 GROUP BY a.rut_apo,dm.mes) AS t ORDER BY Pasaporte,Vencimiento"
    
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

Private Sub Command2_Click()
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")
    ApExcel.Workbooks.Add
    cole = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql As String
    
    ApExcel.Cells(1, 1).Formula = "Cliente"
    ApExcel.Cells(1, 1).Font.Size = 12
    ApExcel.Cells(1, 1).Font.Bold = True
    ApExcel.Range("A1:D1").Merge
    ApExcel.Range("A1:D1").HorizontalAlignment = xlCenter
    'ApExcel.Range("A1:D1").Interior.color = RGB(200, 160, 27)
    
    ApExcel.Cells(1, 5).Formula = "Consumo"
    ApExcel.Cells(1, 5).Font.Size = 12
    ApExcel.Cells(1, 5).Font.Bold = True
    ApExcel.Range("E1:G1").Merge
    ApExcel.Range("E1:G1").HorizontalAlignment = xlCenter
    
    ApExcel.Cells(1, 8).Formula = "Deuda"
    ApExcel.Cells(1, 8).Font.Size = 12
    ApExcel.Cells(1, 8).Font.Bold = True
    ApExcel.Range("H1:M1").Merge
    ApExcel.Range("H1:M1").HorizontalAlignment = xlCenter
    
    For I = 1 To Me.MSFlexGrid1.Cols - 1
        ApExcel.Cells(2, I).Font.Size = 12
        ApExcel.Cells(2, I).Font.Bold = True
        ApExcel.Cells(2, 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
    
    '  cole = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    If cole <> "HUE001" Then
        '' crea_temporales_apoderados
        fecha_hoy = DevuelveCampo("cfg", "", "DATE_FORMAT(NOW(),'%Y-%m')", "param = 'CODBC' limit 1", conn)
        If cole = "FLO001" Then colegio = "F"
        If cole = "PUD001" Then colegio = "P"
        If cole = "SNB001" Then colegio = "B"
        If cole = "SNM001" Then colegio = "M"
    End If
    
    For I = 1 To MSFlexGrid1.Rows - 1
        For j = 1 To MSFlexGrid1.Cols - 1
            If j = 13 Then
                ApExcel.Cells((I + 2), j).NumberFormat = "dd/mm/yyyy"
                ApExcel.Cells((I + 2), j) = IIf(MSFlexGrid1.TextMatrix(I, j) = "Null", "", Format(MSFlexGrid1.TextMatrix(I, j), "yyyy/mm/dd"))
            Else
                fecha_cuota = Format(MSFlexGrid1.TextMatrix(I, 13), "yyyy/mm")
                agno_cuota = Format(MSFlexGrid1.TextMatrix(I, 13), "yyyy")
                If cole <> "HUE001" And j = 8 And fecha_cuota < fecha_hoy Then
                    ' Observacion para repactacion
                    ' limpio variables
                    tipo_apo = ""
                    tercero_medio = ""
                    deuda_2022 = 0
                    deuda_2021 = 0
                    deuda_2020 = 0
                    apo_2020 = ""
                    apo_2021 = ""
                    cod_repac = ""
                    
                    rut = MSFlexGrid1.TextMatrix(I, 1)
                    rut = IIf(Len(rut) = 9, ("0" & rut), rut)
                    ' busco apoderado
                    var_sql = "SELECT * FROM temp_upago_tipo_apo WHERE rut_apo = '" & rut & "'"
                    objRS.Open var_sql, conn
                    If Not objRS.EOF Then
                        tipo_apo = objRS("tipo_apo")
                        tercero_medio = objRS("tercero_medio")
                        deuda_2022 = objRS("deuda_2022")
                        deuda_2021 = objRS("deuda_2021")
                        deuda_2020 = objRS("deuda_2020")
                        apo_2020 = objRS("apo_2020")
                        apo_2020 = objRS("apo_2021")
                    End If
                    objRS.Close
                    
                    ' busco por si hicieron cambio 2020
                    If tipo_apo = "" Then
                        var_sql = "SELECT * FROM temp_upago_tipo_apo WHERE apo_2020 = '" & rut & "'"
                        objRS.Open var_sql, conn
                        If Not objRS.EOF Then
                            tipo_apo = objRS("tipo_apo")
                            tercero_medio = objRS("tercero_medio")
                            deuda_2022 = objRS("deuda_2022")
                            deuda_2021 = objRS("deuda_2021")
                            deuda_2020 = objRS("deuda_2020")
                            apo_2020 = objRS("apo_2020")
                            apo_2020 = objRS("apo_2021")
                        End If
                        objRS.Close
                    End If
                    
                      ' busco por si hicieron cambio 2021
                    If tipo_apo = "" Then
                        var_sql = "SELECT * FROM temp_upago_tipo_apo WHERE apo_2021 = '" & rut & "'"
                        objRS.Open var_sql, conn
                        If Not objRS.EOF Then
                            tipo_apo = objRS("tipo_apo")
                            tercero_medio = objRS("tercero_medio")
                            deuda_2022 = objRS("deuda_2022")
                            deuda_2021 = objRS("deuda_2021")
                            deuda_2020 = objRS("deuda_2020")
                            apo_2020 = objRS("apo_2020")
                            apo_2020 = objRS("apo_2021")
                        End If
                        objRS.Close
                    End If
                    If tipo_apo = "N" Then
                        ' si es nuevo
                        If agno_cuota = 2022 Then
                            cod_repac = "N" & colegio & "R80"
                        Else
                            cod_repac = "A" & colegio & "R100"
                        End If
                    Else
                        If tercero_medio = "S" Then
                            ' tercero medio
                            If agno_cuota = 2022 Then
                                ' deuda 2022
                                cod_repac = "A" & colegio & "R380"
                            Else
                                ' deuda anteriores
                                cod_repac = "A" & colegio & "R3100"
                            End If
                        Else
                            ' solo deuda 2022
                            If agno_cuota = 2022 And deuda_2021 = 0 And deuda_2020 = 0 Then
                                cod_repac = "A" & colegio & "R60"
                            Else
                                If deuda_2021 > 0 Or deuda_2020 > 0 Then
                                    If agno_cuota = 2022 Then
                                        ' deuda 2022 y anteriores cuota 2022
                                        cod_repac = "A" & colegio & "R70"
                                    Else
                                        '
                                        cod_repac = "A" & colegio & "R100"
                                    End If
                                End If
                            End If
                        End If
                    End If
                    ApExcel.Cells((I + 2), j).Formula = IIf(MSFlexGrid1.TextMatrix(I, j) = "Null", "", MSFlexGrid1.TextMatrix(I, j)) '' & " " & cod_repac
                Else
                    If cole = "HUE001" And j = 9 Then
                        ApExcel.Cells((I + 2), j).Formula = CDbl(IIf(MSFlexGrid1.TextMatrix(I, j) = "Null", "", MSFlexGrid1.TextMatrix(I, j)))
                    Else
                        ApExcel.Cells((I + 2), j).Formula = IIf(MSFlexGrid1.TextMatrix(I, j) = "Null", "", MSFlexGrid1.TextMatrix(I, j))
                    End If
                End If
            End If
        
        pgbar.Value = I
        Next j
    Next I
            
    ApExcel.Visible = True
    ApExcel.Range("a1:m1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub

Private Sub Form_Load()
    Dim var_sql As String
    
    var_sql = "UPDATE apoderados SET email = REPLACE(email,'ñ','n');"
    conn.Execute var_sql
    
    var_sql = "UPDATE apoderados SET email = REPLACE(email,' ','');"
    conn.Execute var_sql
End Sub

Private Sub crea_temporales_apoderados()
    var_sql = "DROP TABLE IF EXISTS temp_upago_tipo_apo;"
    conn.Execute var_sql
    
    var_sql = "CREATE TABLE `temp_upago_tipo_apo`(" _
            & "`rut_apo` VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL DEFAULT ''," _
            & "`tipo_apo` CHAR(1) COLLATE latin1_spanish_ci DEFAULT NULL," _
            & "`tercero_medio` CHAR(1) COLLATE latin1_spanish_ci DEFAULT NULL," _
            & "`deuda_2022` INT(8) DEFAULT NULL," _
            & "`deuda_2021` INT(8) DEFAULT NULL," _
            & "`deuda_2020` INT(8) DEFAULT NULL," _
            & "`apo_2020` VARCHAR(10) COLLATE latin1_spanish_ci DEFAULT NULL," _
            & "`apo_2021` VARCHAR(10) COLLATE latin1_spanish_ci DEFAULT NULL," _
            & " PRIMARY KEY  (`rut_apo`)" _
            & " ) ENGINE=MYISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;"
    conn.Execute var_sql
    
    var_sql = "DROP TABLE IF EXISTS temp_deuda_2020_cam_apo;"
    conn.Execute var_sql
    
    var_sql = "DROP TABLE IF EXISTS temp_deuda_2021_cam_apo;"
    conn.Execute var_sql
    
    ' Alumnos antuguos
    var_sql = "INSERT INTO temp_upago_tipo_apo(rut_apo,tipo_apo,tercero_medio,deuda_2022,deuda_2021,deuda_2020,apo_2020,apo_2021)" _
            & " SELECT a.rut_apo,'A','N',0,0,0,0,0 FROM matriculas m,apo_x_alu axa,apoderados a" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " m.anno_mat = 2022 AND fec_mat_r IS NOT NULL AND m.ci_alu IN (SELECT ci_alu FROM alu_x_cur WHERE agno = 2021 AND activo_alu = 'SI')" _
            & " GROUP BY a.rut_apo;"
    conn.Execute var_sql
    
    ' Alumnos nuevos
    var_sql = "REPLACE INTO temp_upago_tipo_apo(rut_apo,tipo_apo,tercero_medio,deuda_2022,deuda_2021,deuda_2020,apo_2020,apo_2021)" _
            & " SELECT a.rut_apo,'N','N',0,0,0,0,0 FROM matriculas m,apo_x_alu axa,apoderados a" _
            & " WHERE m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " m.anno_mat = 2022 AND fec_mat_r IS NOT NULL AND m.ci_alu NOT IN (SELECT ci_alu FROM alu_x_cur WHERE agno = 2021 AND activo_alu = 'SI')" _
            & " GROUP BY a.rut_apo;"
    conn.Execute var_sql
    
    ' Tercero medio
    var_sql = "REPLACE INTO temp_upago_tipo_apo(rut_apo,tipo_apo,tercero_medio,deuda_2022,deuda_2021,deuda_2020,apo_2020,apo_2021)" _
            & " SELECT t.rut_apo,'A','S',0,0,0,0,0 FROM alu_x_cur c,apo_x_alu axa,temp_upago_tipo_apo t" _
            & " WHERE c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = t.rut_apo AND" _
            & " c.agno = 2022 AND c.cod_ens = '310' AND c.num_gra = 3 AND tipo_apo = 'A'" _
            & " GROUP BY t.rut_apo;"
    conn.Execute var_sql
    
    ' Deuda 2022
    var_sql = "REPLACE INTO temp_upago_tipo_apo(rut_apo,tipo_apo,tercero_medio,deuda_2022,deuda_2021,deuda_2020,apo_2020,apo_2021)" _
            & " SELECT t.rut_apo,tipo_apo,tercero_medio,SUM(monto_tot_cuota - monto_abono) d2022,0,0,0,0" _
            & " FROM matriculas c,apo_x_alu axa,temp_upago_tipo_apo t,fnza_detalle_mensualidad f" _
            & " WHERE c.ci_alu = axa.ci_alu AND c.anno_mat = axa.agno AND axa.rut_apo = t.rut_apo AND" _
            & " c.ci_alu = f.run_alu And c.anno_mat = f.agno And mes > 2 And mes < 11 And c.anno_mat = 2022" _
            & " AND fec_mat_r IS NOT NULL" _
            & " GROUP BY t.rut_apo;"
    conn.Execute var_sql
    
    ' Deuda 2021
    var_sql = "REPLACE INTO temp_upago_tipo_apo(rut_apo,tipo_apo,tercero_medio,deuda_2022,deuda_2021,deuda_2020,apo_2020,apo_2021)" _
            & " SELECT t.rut_apo,tipo_apo,tercero_medio,deuda_2022,SUM(monto_tot_cuota - monto_abono) d2021,0,0,0" _
            & " FROM matriculas c,apo_x_alu axa,temp_upago_tipo_apo t,fnza_detalle_mensualidad f" _
            & " WHERE c.ci_alu = axa.ci_alu AND c.anno_mat = axa.agno AND axa.rut_apo = t.rut_apo AND" _
            & " c.ci_alu = f.run_alu And c.anno_mat = (f.agno + 1) And mes > 2 And c.anno_mat = 2022" _
            & " AND fec_mat_r IS NOT NULL AND (monto_tot_cuota - monto_abono) > 20000" _
            & " GROUP BY t.rut_apo;"
    conn.Execute var_sql
    
    ' Deuda 2020
    var_sql = "REPLACE INTO temp_upago_tipo_apo(rut_apo,tipo_apo,tercero_medio,deuda_2022,deuda_2021,deuda_2020,apo_2020,apo_2021)" _
            & " SELECT t.rut_apo,tipo_apo,tercero_medio,deuda_2022,deuda_2021,SUM(monto_tot_cuota - monto_abono) d2020,0,0" _
            & " FROM matriculas c,apo_x_alu axa,temp_upago_tipo_apo t,fnza_detalle_mensualidad f" _
            & " WHERE c.ci_alu = axa.ci_alu AND c.anno_mat = axa.agno AND axa.rut_apo = t.rut_apo AND" _
            & " c.ci_alu = f.run_alu And c.anno_mat = (f.agno + 2) And mes > 2 And c.anno_mat = 2022" _
            & " AND fec_mat_r IS NOT NULL AND (monto_tot_cuota - monto_abono) > 20000" _
            & " GROUP BY t.rut_apo;"
    conn.Execute var_sql
    
    ' Deuda apoderados trukeros 2020
    var_sql = "CREATE TABLE temp_deuda_2020_cam_apo TYPE = MYISAM" _
            & " SELECT a.rut_apo,c.ci_alu,SUM(monto_tot_cuota-monto_abono) AS MontoCapital" _
            & " FROM fnza_detalle_mensualidad dm,matriculas c,apo_x_alu axc,apoderados a,meses m WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno" _
            & " AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2020 AND monto_tot_cuota > 0 AND (monto_tot_cuota - monto_abono) > 15000 AND dm.mes > 2 AND confirmado = 'SI'" _
            & " AND fec_mat_r IS NOT NULL AND ind_cup = 1 AND c.ci_alu IN (SELECT ci_alu FROM alu_x_cur WHERE agno = 2022 AND activo_alu = 'SI')" _
            & " AND a.rut_apo NOT IN (SELECT rut_apo FROM temp_upago_tipo_apo)" _
            & " GROUP BY a.rut_apo,c.ci_alu;"
    conn.Execute var_sql
    
    ' Deuda apoderados trukeros 2021
    var_sql = "CREATE TABLE temp_deuda_2021_cam_apo TYPE = MYISAM" _
            & " SELECT a.rut_apo,c.ci_alu,SUM(monto_tot_cuota-monto_abono) AS MontoCapital" _
            & " FROM fnza_detalle_mensualidad dm,matriculas c,apo_x_alu axc,apoderados a,meses m WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu" _
            & " AND dm.agno = c.anno_mat AND c.anno_mat = axc.agno AND axc.rut_apo = a.rut_apo AND dm.mes = m.num_mes AND dm.agno = 2021 AND monto_tot_cuota > 0" _
            & " AND (monto_tot_cuota - monto_abono) > 20000 AND dm.mes > 2 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL AND ind_cup = 1 AND c.ci_alu IN (SELECT ci_alu" _
            & " FROM alu_x_cur WHERE agno = 2022 AND activo_alu = 'SI')" _
            & " AND a.rut_apo NOT IN (SELECT rut_apo FROM temp_upago_tipo_apo)" _
            & " GROUP BY a.rut_apo,c.ci_alu;"
    conn.Execute var_sql
    
    ' Relaciono apoderado antiguo 2020 con apoderado 2022
    var_sql = "UPDATE temp_deuda_2020_cam_apo t,apo_x_alu axa,temp_upago_tipo_apo tu" _
            & " Set tu.apo_2020 = t.rut_apo" _
            & " WHERE t.ci_alu = axa.ci_alu AND axa.agno = 2022 AND axa.rut_apo = tu.rut_apo;"
    conn.Execute var_sql
    
    ' Relaciono apoderado antiguo 2021 con apoderado 2022
    var_sql = "UPDATE temp_deuda_2021_cam_apo t,apo_x_alu axa,temp_upago_tipo_apo tu" _
            & " Set tu.apo_2021 = t.rut_apo" _
            & " WHERE t.ci_alu = axa.ci_alu AND axa.agno = 2022 AND axa.rut_apo = tu.rut_apo;"
    conn.Execute var_sql
   
End Sub

Public Sub bloqueo_deuda_cupon()
    Dim var_sql As String
    
    var_sql = "DROP TABLE IF EXISTS temp_deuda_actual;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_deuda_anterior;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_deuda_motivo_bloqueo;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_alu_prot;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_prot_cam;"
    conn.Execute var_sql
  
    ' deuda actual
    var_sql = "CREATE TABLE temp_deuda_actual TYPE = MYISAM" _
            & " SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,dm.run_alu," _
            & "CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS Alumno,dm.agno,dm.mes,SUM(monto_tot_cuota-monto_abono) AS monto_deuda,fech_venc,LEFT(dm.colegio, 6) AS colegio" _
            & " 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 > 2022 AND dm.run_alu = p.ci_alu" _
            & " AND axc.rut_apo = a.rut_apo AND dm.agno < 2024 AND dm.mes >= 1 AND dm.mes <= 12 GROUP BY a.rut_apo,dm.run_alu,dm.mes HAVING SUM(monto_tot_cuota - monto_abono) >= 100000;"
    conn.Execute var_sql
    
    ' deuda años anteriores
    var_sql = "CREATE TABLE temp_deuda_anterior TYPE = MYISAM " _
            & "SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,dm.run_alu," _
            & "CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS Alumno,dm.agno,dm.mes,SUM(monto_tot_cuota-monto_abono) AS monto_deuda,fech_venc,LEFT(dm.colegio, 6) AS colegio" _
            & " 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 dm.agno = c.agno" _
            & " AND c.ci_alu = axc.ci_alu AND c.agno = axc.agno AND c.ci_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno > 2011 AND dm.agno < 2023 AND dm.mes >= 1" _
            & " GROUP BY a.rut_apo,dm.run_alu,dm.mes HAVING SUM(monto_tot_cuota - monto_abono) >= 90000;"
    conn.Execute var_sql
    
    ' actual
    var_sql = "CREATE TABLE temp_deuda_motivo_bloqueo TYPE = MYISAM " _
            & "SELECT c.ci_alu AS Rut,CONCAT(ape_pat_alu,' ',ape_mat_alu) AS Apellidos," _
            & "nom_alu AS Nombres,des_ens_abrev AS Nivel,CONCAT(CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310',c.num_gra,'') AS CHAR)," _
            & "CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310','º ','') AS CHAR),c.let_cur) AS Curso,c.fec_mat AS 'Fec. Mat.'," _
            & "a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS apoderado," _
            & "cel_apo AS celular,fon_c_apo AS telefono,empresa_fon AS empresa_telefono," _
            & "telefono_adicional_1,telefono_adicional_2,email,'Deuda mensualidades actuales' AS motivo" _
            & " FROM alu_x_cur c,codigos_ensenanza ce,matriculas m,postulantes p,apo_x_alu axa,apoderados a" _
            & " WHERE c.ci_alu = m.ci_alu AND c.agno = m.anno_mat AND c.cod_ens = ce.cod_ens AND c.ci_alu = p.ci_alu AND" _
            & " c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " c.agno = 2023 AND c.activo_alu = 'SI' AND" _
            & " c.ci_alu IN (SELECT run_alu FROM temp_deuda_actual)" _
            & " GROUP BY c.ci_alu;"
    conn.Execute var_sql
    
    ' anterior
    var_sql = "INSERT INTO `temp_deuda_motivo_bloqueo` (`Rut`, `Apellidos`, `Nombres`, `Nivel`, `Curso`, `Fec. Mat.`, `rut_apo`, `apoderado`, `celular`, `telefono`, `empresa_telefono`, `telefono_adicional_1`, `telefono_adicional_2`, `email`, `motivo`)" _
            & " SELECT c.ci_alu AS Rut,CONCAT(ape_pat_alu,' ',ape_mat_alu) AS Apellidos," _
            & "nom_alu AS Nombres,des_ens_abrev AS Nivel,CONCAT(CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310',c.num_gra,'') AS CHAR)," _
            & "CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310','º ','') AS CHAR),c.let_cur) AS Curso,c.fec_mat AS 'Fec. Mat.'," _
            & "a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS apoderado," _
            & "cel_apo AS celular,fon_c_apo AS telefono,empresa_fon AS empresa_telefono," _
            & "telefono_adicional_1,telefono_adicional_2,email,'Deuda mensualidades antiguas' AS motivo" _
            & " FROM alu_x_cur c,codigos_ensenanza ce,matriculas m,postulantes p,apo_x_alu axa,apoderados a" _
            & " WHERE c.ci_alu = m.ci_alu AND c.agno = m.anno_mat AND c.cod_ens = ce.cod_ens AND c.ci_alu = p.ci_alu AND" _
            & " c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " c.agno = 2023 AND c.activo_alu = 'SI' AND" _
            & " c.ci_alu IN (SELECT run_alu FROM temp_deuda_anterior)" _
            & " GROUP BY c.ci_alu;"
    conn.Execute var_sql
    
    'protestos
    var_sql = "INSERT INTO `temp_deuda_motivo_bloqueo` (`Rut`, `Apellidos`, `Nombres`, `Nivel`, `Curso`, `Fec. Mat.`, `rut_apo`, `apoderado`, `celular`, `telefono`, `empresa_telefono`, `telefono_adicional_1`, `telefono_adicional_2`, `email`, `motivo`)" _
            & " SELECT c.ci_alu AS Rut,CONCAT(ape_pat_alu,' ',ape_mat_alu) AS Apellidos," _
            & "nom_alu AS Nombres,des_ens_abrev AS Nivel,CONCAT(CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310',c.num_gra,'') AS CHAR)," _
            & "CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310','º ','') AS CHAR),c.let_cur) AS Curso,c.fec_mat AS 'Fec. Mat.'," _
            & "a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS apoderado," _
            & "cel_apo AS celular,fon_c_apo AS telefono,empresa_fon AS empresa_telefono," _
            & "telefono_adicional_1,telefono_adicional_2,email,'Cheques protestados' AS motivo" _
            & " FROM alu_x_cur c,codigos_ensenanza ce,matriculas m,postulantes p,apo_x_alu axa,apoderados a" _
            & " WHERE c.ci_alu = m.ci_alu AND c.agno = m.anno_mat AND c.cod_ens = ce.cod_ens AND c.ci_alu = p.ci_alu AND" _
            & " c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " c.agno = 2023 AND c.activo_alu = 'SI' AND" _
            & " axa.rut_apo IN (SELECT ci_apo FROM fnza_lista_negra_apoderados WHERE bloqueado = 1)" _
            & " GROUP BY c.ci_alu;"
    conn.Execute var_sql
    
    'enmendado
    var_sql = "INSERT INTO `temp_deuda_motivo_bloqueo` (`Rut`, `Apellidos`, `Nombres`, `Nivel`, `Curso`, `Fec. Mat.`, `rut_apo`, `apoderado`, `celular`, `telefono`, `empresa_telefono`, `telefono_adicional_1`, `telefono_adicional_2`, `email`, `motivo`)" _
            & " SELECT c.ci_alu AS Rut,CONCAT(ape_pat_alu,' ',ape_mat_alu) AS Apellidos," _
            & "nom_alu AS Nombres,des_ens_abrev AS Nivel,CONCAT(CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310',c.num_gra,'') AS CHAR)," _
            & "CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310','º ','') AS CHAR),c.let_cur) AS Curso,c.fec_mat AS 'Fec. Mat.'," _
            & "a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS apoderado," _
            & "cel_apo AS celular,fon_c_apo AS telefono,empresa_fon AS empresa_telefono," _
            & "telefono_adicional_1,telefono_adicional_2,email,'Cheques enmendados' AS motivo" _
            & " FROM alu_x_cur c,codigos_ensenanza ce,matriculas m,postulantes p,apo_x_alu axa,apoderados a" _
            & " WHERE c.ci_alu = m.ci_alu AND c.agno = m.anno_mat AND c.cod_ens = ce.cod_ens AND c.ci_alu = p.ci_alu AND" _
            & " c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " c.agno = 2023 AND c.activo_alu = 'SI' AND" _
            & " axa.rut_apo IN (SELECT rut FROM fnza_enmendados)" _
            & " GROUP BY c.ci_alu;"
    conn.Execute var_sql
    
    'rescatados
    var_sql = "INSERT INTO `temp_deuda_motivo_bloqueo` (`Rut`, `Apellidos`, `Nombres`, `Nivel`, `Curso`, `Fec. Mat.`, `rut_apo`, `apoderado`, `celular`, `telefono`, `empresa_telefono`, `telefono_adicional_1`, `telefono_adicional_2`, `email`, `motivo`)" _
            & " SELECT c.ci_alu AS Rut,CONCAT(ape_pat_alu,' ',ape_mat_alu) AS Apellidos," _
            & "nom_alu AS Nombres,des_ens_abrev AS Nivel,CONCAT(CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310',c.num_gra,'') AS CHAR)," _
            & "CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310','º ','') AS CHAR),c.let_cur) AS Curso,c.fec_mat AS 'Fec. Mat.'," _
            & "a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS apoderado," _
            & "cel_apo AS celular,fon_c_apo AS telefono,empresa_fon AS empresa_telefono," _
            & "telefono_adicional_1,telefono_adicional_2,email,'Cheques rescatados' AS motivo" _
            & " FROM alu_x_cur c,codigos_ensenanza ce,matriculas m,postulantes p,apo_x_alu axa,apoderados a" _
            & " WHERE c.ci_alu = m.ci_alu AND c.agno = m.anno_mat AND c.cod_ens = ce.cod_ens AND c.ci_alu = p.ci_alu AND" _
            & " c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " c.agno = 2023 AND c.activo_alu = 'SI' AND" _
            & " axa.rut_apo IN (SELECT rut_apo FROM fnza_cheques_rescatados)" _
            & " GROUP BY c.ci_alu;"
    conn.Execute var_sql
    
    'protestos alumnos
    var_sql = "INSERT INTO `temp_deuda_motivo_bloqueo` (`Rut`, `Apellidos`, `Nombres`, `Nivel`, `Curso`, `Fec. Mat.`, `rut_apo`, `apoderado`, `celular`, `telefono`, `empresa_telefono`, `telefono_adicional_1`, `telefono_adicional_2`, `email`, `motivo`)" _
            & " SELECT c.ci_alu AS Rut,CONCAT(ape_pat_alu,' ',ape_mat_alu) AS Apellidos," _
            & "nom_alu AS Nombres,des_ens_abrev AS Nivel,CONCAT(CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310',c.num_gra,'') AS CHAR)," _
            & "CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310','º ','') AS CHAR),c.let_cur) AS Curso,c.fec_mat AS 'Fec. Mat.'," _
            & "a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS apoderado," _
            & "cel_apo AS celular,fon_c_apo AS telefono,empresa_fon AS empresa_telefono," _
            & "telefono_adicional_1,telefono_adicional_2,email,'Protestos alumnos' AS motivo" _
            & " FROM alu_x_cur c,codigos_ensenanza ce,matriculas m,postulantes p,apo_x_alu axa,apoderados a" _
            & " WHERE c.ci_alu = m.ci_alu AND c.agno = m.anno_mat AND c.cod_ens = ce.cod_ens AND c.ci_alu = p.ci_alu AND" _
            & " c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " c.agno = 2023 AND c.activo_alu = 'SI' AND" _
            & " c.ci_alu IN (SELECT ci_alu FROM fnza_lista_negra_alumnos)" _
            & " GROUP BY c.ci_alu;"
    conn.Execute var_sql
    
    'condicionales
    var_sql = "INSERT INTO `temp_deuda_motivo_bloqueo` (`Rut`, `Apellidos`, `Nombres`, `Nivel`, `Curso`, `Fec. Mat.`, `rut_apo`, `apoderado`, `celular`, `telefono`, `empresa_telefono`, `telefono_adicional_1`, `telefono_adicional_2`, `email`, `motivo`)" _
            & " SELECT c.ci_alu AS Rut,CONCAT(ape_pat_alu,' ',ape_mat_alu) AS Apellidos," _
            & "nom_alu AS Nombres,des_ens_abrev AS Nivel,CONCAT(CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310',c.num_gra,'') AS CHAR)," _
            & "CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310','º ','') AS CHAR),c.let_cur) AS Curso,c.fec_mat AS 'Fec. Mat.'," _
            & "a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS apoderado," _
            & "cel_apo AS celular,fon_c_apo AS telefono,empresa_fon AS empresa_telefono," _
            & "telefono_adicional_1,telefono_adicional_2,email,'Bloqueos conducta' AS motivo" _
            & " FROM alu_x_cur c,codigos_ensenanza ce,matriculas m,postulantes p,apo_x_alu axa,apoderados a" _
            & " WHERE c.ci_alu = m.ci_alu AND c.agno = m.anno_mat AND c.cod_ens = ce.cod_ens AND c.ci_alu = p.ci_alu AND" _
            & " c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " c.agno = 2023 AND c.activo_alu = 'SI' AND" _
            & " c.ci_alu IN (SELECT rut FROM fnza_bloqueados_condicionales WHERE agno = 2024)" _
            & " GROUP BY c.ci_alu;"
    conn.Execute var_sql
    
    ' temporales cambio apoderado protestos
    var_sql = "CREATE TABLE temp_alu_prot TYPE = MYISAM" _
            & " SELECT ci_alu FROM fnza_lista_negra_apoderados b,apo_x_alu a WHERE ci_apo = rut_apo AND bloqueado = 1 GROUP BY ci_alu;"
    conn.Execute var_sql
    var_sql = "CREATE TABLE temp_prot_cam TYPE = MYISAM" _
            & " SELECT * FROM apo_x_alu WHERE ci_alu IN (SELECT ci_alu FROM temp_alu_prot) GROUP BY rut_apo"
    conn.Execute var_sql
    
    'Bloqueo ce cambios de apoderado protestos
    var_sql = "INSERT INTO `temp_deuda_motivo_bloqueo` (`Rut`, `Apellidos`, `Nombres`, `Nivel`, `Curso`, `Fec. Mat.`, `rut_apo`, `apoderado`, `celular`, `telefono`, `empresa_telefono`, `telefono_adicional_1`, `telefono_adicional_2`, `email`, `motivo`) SELECT c.ci_alu AS Rut,CONCAT(ape_pat_alu,' ',ape_mat_alu) AS Apellidos," _
            & "nom_alu AS Nombres,des_ens_abrev AS Nivel,CONCAT(CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310',c.num_gra,'') AS CHAR)," _
            & "CAST(IF(c.cod_ens = '110' OR c.cod_ens = '310','º ','') AS CHAR),c.let_cur) AS Curso,c.fec_mat AS 'Fec. Mat.'," _
            & "a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS apoderado," _
            & "cel_apo AS celular,fon_c_apo AS telefono,empresa_fon AS empresa_telefono," _
            & "telefono_adicional_1,telefono_adicional_2,email,'Protestados Cambio Apoderado' AS motivo" _
            & " FROM alu_x_cur c,codigos_ensenanza ce,matriculas m,postulantes p,apo_x_alu axa,apoderados a" _
            & " WHERE c.ci_alu = m.ci_alu AND c.agno = m.anno_mat AND c.cod_ens = ce.cod_ens AND c.ci_alu = p.ci_alu AND" _
            & " c.ci_alu = axa.ci_alu AND c.agno = axa.agno AND axa.rut_apo = a.rut_apo AND" _
            & " c.agno = 2023 AND c.activo_alu = 'SI' AND axa.rut_apo IN (SELECT rut_apo FROM temp_prot_cam)" _
            & " GROUP BY c.ci_alu;"
    conn.Execute var_sql
    
End Sub
