VERSION 5.00
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_Informes_mat_prox_agno 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Informes de Matriculas"
   ClientHeight    =   3645
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   4965
   Icon            =   "Form_Informes_mat_prox_agno.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   3645
   ScaleWidth      =   4965
   Begin VB.CommandButton cmd_exportar_excel 
      Caption         =   "Exportar Excel"
      Height          =   735
      Left            =   3360
      TabIndex        =   15
      Top             =   2280
      Width           =   1455
   End
   Begin VB.CommandButton Command3 
      Caption         =   "Cerrar"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   3360
      Picture         =   "Form_Informes_mat_prox_agno.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   11
      Top             =   1200
      Width           =   1455
   End
   Begin VB.CommandButton Command4 
      Caption         =   "Detalle de Matrícula"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   3360
      TabIndex        =   12
      Top             =   2040
      Visible         =   0   'False
      Width           =   1455
   End
   Begin VB.CommandButton Command2 
      Caption         =   "Generar Informe"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   3360
      Picture         =   "Form_Informes_mat_prox_agno.frx":2AC1A
      Style           =   1  'Graphical
      TabIndex        =   4
      Top             =   120
      Width           =   1455
   End
   Begin VB.CommandButton Command1 
      Caption         =   "Estadística"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   3360
      TabIndex        =   3
      Top             =   240
      Visible         =   0   'False
      Width           =   1455
   End
   Begin VB.Frame Frame1 
      Caption         =   "Informes"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   3375
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   3135
      Begin VB.TextBox txt_monto 
         Height          =   315
         Left            =   1200
         TabIndex        =   14
         Text            =   "0"
         Top             =   2400
         Width           =   1455
      End
      Begin VB.ComboBox Cmb_Reserva 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   960
         Style           =   2  'Dropdown List
         TabIndex        =   7
         Top             =   3720
         Visible         =   0   'False
         Width           =   1695
      End
      Begin VB.ComboBox Cmb_Matr 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   960
         Style           =   2  'Dropdown List
         TabIndex        =   6
         Top             =   1800
         Width           =   1695
      End
      Begin VB.ComboBox Cmb_who 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   960
         Style           =   2  'Dropdown List
         TabIndex        =   5
         Top             =   1080
         Width           =   1695
      End
      Begin VB.ComboBox Cmb_Agno 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   960
         Style           =   2  'Dropdown List
         TabIndex        =   1
         Top             =   360
         Width           =   1215
      End
      Begin ComctlLib.ProgressBar pgBar 
         Height          =   255
         Left            =   120
         TabIndex        =   17
         Top             =   2880
         Width           =   2895
         _ExtentX        =   5106
         _ExtentY        =   450
         _Version        =   327682
         Appearance      =   1
      End
      Begin VB.Label lbl_mayor 
         Caption         =   ">="
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   12
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   840
         TabIndex        =   16
         Top             =   2400
         Width           =   255
      End
      Begin VB.Label lbl_monto 
         Caption         =   "Monto"
         Height          =   255
         Left            =   240
         TabIndex        =   13
         Top             =   2450
         Width           =   495
      End
      Begin VB.Label Label1 
         Caption         =   "Reserva"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Index           =   3
         Left            =   240
         TabIndex        =   10
         Top             =   3600
         Visible         =   0   'False
         Width           =   855
      End
      Begin VB.Label Label1 
         Caption         =   "Estado"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Index           =   2
         Left            =   240
         TabIndex        =   9
         Top             =   1800
         Width           =   855
      End
      Begin VB.Label Label1 
         Caption         =   "Alumnos"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Index           =   1
         Left            =   240
         TabIndex        =   8
         Top             =   1080
         Width           =   855
      End
      Begin VB.Label Label1 
         Caption         =   "Año"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Index           =   0
         Left            =   240
         TabIndex        =   2
         Top             =   360
         Width           =   855
      End
   End
End
Attribute VB_Name = "Form_Informes_mat_prox_agno"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub cmb_agno_Click()
    Call Cmb_who_Click
    oculta_excel
End Sub





Private Sub Cmb_Matr_Change()
    oculta_excel
End Sub

Private Sub Cmb_Matr_Click()
    oculta_excel
End Sub

Private Sub Cmb_who_Click()
oculta_excel
Cmb_Matr.Clear

    If Cmb_who.Text = "Todos" Then
        Cmb_Matr.AddItem "Matriculados"
        Cmb_Matr.Text = "Matriculados"
        Cmb_Matr.Enabled = False
    Else
    
        If Cmb_who.Text = "Nuevos" Then
            Cmb_Matr.AddItem "Matriculados"
            Cmb_Matr.Text = "Matriculados"
            
            If cmb_agno.Text <> "2011" Then
            Cmb_Matr.AddItem "Postulados"
            End If
            
        End If
        
        If Cmb_who.Text = "Antiguos" Then
            Cmb_Matr.AddItem "Matriculados"
            Cmb_Matr.Text = "Matriculados"
            Cmb_Matr.AddItem "No Matriculados"
            Cmb_Matr.AddItem "Todos"
        End If
        
        Cmb_Matr.Enabled = True
    End If

    'Cmb_Reserva.Text = "Todos"
    'Cmb_Reserva.Enabled = False

End Sub

Private Sub cmd_exportar_excel_Click()
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim Monto_boletas As Long
    Dim total_boletas As Long
    
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")
    ' Hace que Excel se vea
    'ApExcel.Visible = True
    'Agrega un nuevo Libro
    ApExcel.Workbooks.Add
    'Poner Titulos
    'ApExcel.cells(1, 1).Formula = "INFORME DE BOLETAS EMITIDAS"
    
            
    var_sql = "DROP TABLE IF EXISTS temp_caja;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_caja_reporte;"
    conn.Execute var_sql
        
'    var_sql = "SELECT IF(cod_ens = '111','PlayGroup',IF(cod_ens = '996','PreKinder',IF(cod_ens = '010','Kinder',IF(cod_ens = '110','Basico',IF(cod_ens = '310','Media','SinCurso'))))) AS nivel," _
'            & "CONCAT(CAST(IF(cod_ens = '110' OR cod_ens = '310',num_gra,'') AS CHAR),CAST(IF(cod_ens = '110' OR cod_ens = '310',' ','') AS CHAR),let_cur) AS curso,c.ci_alu,alumno,dias_trabajados,dias_asistidos,porcentaje" _
'            & " FROM (SELECT cu.ci_alu,cu.cod_ens,cu.num_gra,cu.let_cur,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno" _
'            & " FROM alu_x_cur cu,postulantes p WHERE agno = " & cb_agno.Text & " AND activo_alu = 'SI' AND cu.ci_alu = p.ci_alu) AS c" _
'            & " Left Join" _
'            & " (SELECT ci_alu,SUM(asi_r) AS dias_trabajados,SUM(asi_t) AS dias_asistidos,((SUM(asi_t) * 100) / SUM(asi_r)) porcentaje" _
'            & " FROM asistencia a" _
'            & " WHERE agno = " & cb_agno.Text & " AND activo_alu = 'SI'" _
'            & " GROUP BY ci_alu) AS a" _
'            & " ON c.ci_alu = a.ci_alu" _
'            & " ORDER BY cod_ens,num_gra,let_cur,alumno"
        
    var_sql = "SELECT f.*, c.monto_cheques,c.cantidad_cheques" _
            & " FROM tmp_reporte_matriculas f LEFT JOIN (SELECT ci_apo,SUM(monto_cheque) AS monto_cheques,COUNT(ci_apo) cantidad_cheques" _
            & " FROM fnza_lista_negra_apoderados WHERE bloqueado = 1 GROUP BY ci_apo) c" _
            & " ON f.rut_apo = c.ci_apo" _
            & " WHERE f.deuda >= " & txt_monto.Text
     
    objRS.Open var_sql, conn
    I = 1
    If Cmb_Matr.Text = "Todos" Then
        ApExcel.Cells(1, 1).Font.Size = 12
        ApExcel.Cells(1, 2).Font.Size = 12
        ApExcel.Cells(1, 3).Font.Size = 12
        ApExcel.Cells(1, 4).Font.Size = 12
        ApExcel.Cells(1, 5).Font.Size = 12
        ApExcel.Cells(1, 6).Font.Size = 12
        ApExcel.Cells(1, 7).Font.Size = 12
        ApExcel.Cells(1, 8).Font.Size = 12
        ApExcel.Cells(1, 9).Font.Size = 12
        ApExcel.Cells(1, 10).Font.Size = 12
        ApExcel.Cells(1, 11).Font.Size = 12
        ApExcel.Cells(1, 12).Font.Size = 12
        ApExcel.Cells(1, 13).Font.Size = 12
        ApExcel.Cells(1, 14).Font.Size = 12
        ApExcel.Cells(1, 15).Font.Size = 12
        ApExcel.Cells(1, 16).Font.Size = 12
        ApExcel.Cells(1, 17).Font.Size = 12
            
        ApExcel.Cells(1, 1).Font.Bold = True
        ApExcel.Cells(1, 2).Font.Bold = True
        ApExcel.Cells(1, 3).Font.Bold = True
        ApExcel.Cells(1, 4).Font.Bold = True
        ApExcel.Cells(1, 5).Font.Bold = True
        ApExcel.Cells(1, 6).Font.Bold = True
        ApExcel.Cells(1, 7).Font.Bold = True
        ApExcel.Cells(1, 8).Font.Bold = True
        ApExcel.Cells(1, 9).Font.Bold = True
        ApExcel.Cells(1, 10).Font.Bold = True
        ApExcel.Cells(1, 11).Font.Bold = True
        ApExcel.Cells(1, 12).Font.Bold = True
        ApExcel.Cells(1, 13).Font.Bold = True
        ApExcel.Cells(1, 14).Font.Bold = True
        ApExcel.Cells(1, 15).Font.Bold = True
        ApExcel.Cells(1, 16).Font.Bold = True
        ApExcel.Cells(1, 17).Font.Bold = True
    
        ApExcel.Cells(1, 1).Formula = "Rut. Alu."
        ApExcel.Cells(1, 2).Formula = "Alumno"
        ApExcel.Cells(1, 3).Formula = "Grado"
        ApExcel.Cells(1, 4).Formula = "Nivel"
        ApExcel.Cells(1, 5).Formula = "Letra"
        ApExcel.Cells(1, 6).Formula = "Rut. Apo."
        ApExcel.Cells(1, 7).Formula = "Apoderado"
        ApExcel.Cells(1, 8).Formula = "Telefono"
        ApExcel.Cells(1, 9).Formula = "Celular"
        ApExcel.Cells(1, 10).Formula = "Becado"
        ApExcel.Cells(1, 11).Formula = "Correo Alumno"
        ApExcel.Cells(1, 12).Formula = "Correo Apoderado"
        ApExcel.Cells(1, 13).Formula = "Promedio Notas"
        ApExcel.Cells(1, 14).Formula = "Deuda"
        ApExcel.Cells(1, 15).Formula = "Matriculado"
        ApExcel.Cells(1, 16).Formula = "Monto cheques"
        ApExcel.Cells(1, 17).Formula = "Cantidad cheques"
        
        pgbar.Min = 0
        pgbar.Max = objRS.RecordCount + 1
        pgbar.Value = pgbar.Min
        total_boletas = 0
        Monto_boletas = 0
        While Not objRS.EOF
        
            I = I + 1
            ApExcel.Cells(I, 1).Formula = objRS("ci_alu")
            ApExcel.Cells(I, 2).Formula = objRS("nombre_alumno")
            ApExcel.Cells(I, 3).Formula = objRS("num_gra")
            ApExcel.Cells(I, 4).Formula = objRS("nivel")
            ApExcel.Cells(I, 5).Formula = objRS("letra")
            ApExcel.Cells(I, 6).Formula = objRS("rut_apo")
            ApExcel.Cells(I, 7).Formula = objRS("nombre_apoderado")
            ApExcel.Cells(I, 8).Formula = objRS("fon_c_apo")
            ApExcel.Cells(I, 9).Formula = objRS("cel_apo")
            ApExcel.Cells(I, 10).Formula = objRS("becado")
            ApExcel.Cells(I, 11).Formula = objRS("correo_alumno")
            ApExcel.Cells(I, 12).Formula = objRS("correo_apoderado")
            ApExcel.Cells(I, 13).Formula = objRS("primer_semestre")
            ApExcel.Cells(I, 14).Formula = objRS("deuda")
            ApExcel.Cells(I, 14).NumberFormat = "#,##0"
            ApExcel.Cells(I, 15).Formula = objRS("matriculado")
            ApExcel.Cells(I, 16).Formula = objRS("monto_cheques")
            ApExcel.Cells(I, 17).Formula = objRS("cantidad_cheques")
        ' Aplica Formula
        'ApExcel.cells(3, 4).Formula = "=B3-C3"
        ' Hace una Seleccion de celdas y pone bordes de Color
        
            pgbar.Value = I - 1
        'pgBar.Refresh
            objRS.MoveNext
        Wend
        I = I + 1
    
   
        ApExcel.Cells(I, 1).Font.Bold = True
        ApExcel.Cells(I, 2).Font.Bold = True
        ApExcel.Cells(I, 3).Font.Bold = True
        ApExcel.Cells(I, 4).Font.Bold = True
        ApExcel.Cells(I, 5).Font.Bold = True
        ApExcel.Cells(I, 6).Font.Bold = True
        ApExcel.Cells(I, 7).Font.Bold = True
        ApExcel.Cells(I, 8).Font.Bold = True
        ApExcel.Cells(I, 9).Font.Bold = True
        ApExcel.Cells(I, 10).Font.Bold = True
        ApExcel.Cells(I, 11).Font.Bold = True
        ApExcel.Cells(I, 12).Font.Bold = True
        ApExcel.Cells(I, 13).Font.Bold = True
        ApExcel.Cells(I, 14).Font.Bold = True
        ApExcel.Cells(I, 15).Font.Bold = True
        ApExcel.Cells(I, 14).Font.Bold = True
        ApExcel.Cells(I, 15).Font.Bold = True
            
        ApExcel.Visible = True
        ApExcel.Range("a1:m1").Borders.color = RGB(255, 0, 0)
        Set ApExcel = Nothing
    
        objRS.Close
    Else
    
    ApExcel.Cells(1, 1).Font.Size = 12
    ApExcel.Cells(1, 2).Font.Size = 12
    ApExcel.Cells(1, 3).Font.Size = 12
    ApExcel.Cells(1, 4).Font.Size = 12
    ApExcel.Cells(1, 5).Font.Size = 12
    ApExcel.Cells(1, 6).Font.Size = 12
    ApExcel.Cells(1, 7).Font.Size = 12
    ApExcel.Cells(1, 8).Font.Size = 12
    ApExcel.Cells(1, 9).Font.Size = 12
    ApExcel.Cells(1, 10).Font.Size = 12
    ApExcel.Cells(1, 11).Font.Size = 12
    ApExcel.Cells(1, 12).Font.Size = 12
    ApExcel.Cells(1, 13).Font.Size = 12
    ApExcel.Cells(1, 14).Font.Size = 12
    ApExcel.Cells(1, 15).Font.Size = 12
            
    ApExcel.Cells(1, 1).Font.Bold = True
    ApExcel.Cells(1, 2).Font.Bold = True
    ApExcel.Cells(1, 3).Font.Bold = True
    ApExcel.Cells(1, 4).Font.Bold = True
    ApExcel.Cells(1, 5).Font.Bold = True
    ApExcel.Cells(1, 6).Font.Bold = True
    ApExcel.Cells(1, 7).Font.Bold = True
    ApExcel.Cells(1, 8).Font.Bold = True
    ApExcel.Cells(1, 9).Font.Bold = True
    ApExcel.Cells(1, 10).Font.Bold = True
    ApExcel.Cells(1, 11).Font.Bold = True
    ApExcel.Cells(1, 12).Font.Bold = True
    ApExcel.Cells(1, 13).Font.Bold = True
    ApExcel.Cells(1, 14).Font.Bold = True
    ApExcel.Cells(1, 15).Font.Bold = True
    
    ApExcel.Cells(1, 1).Formula = "Rut. Alu."
    ApExcel.Cells(1, 2).Formula = "Alumno"
    ApExcel.Cells(1, 3).Formula = "Grado"
    ApExcel.Cells(1, 4).Formula = "Nivel"
    ApExcel.Cells(1, 5).Formula = "Rut. Apo."
    ApExcel.Cells(1, 6).Formula = "Apoderado"
    ApExcel.Cells(1, 7).Formula = "Telefono"
    ApExcel.Cells(1, 8).Formula = "Celular"
    ApExcel.Cells(1, 9).Formula = "Becado"
    ApExcel.Cells(1, 10).Formula = "Correo Alumno"
    ApExcel.Cells(1, 11).Formula = "Correo Apoderado"
    ApExcel.Cells(1, 12).Formula = "Promedio Notas"
    ApExcel.Cells(1, 13).Formula = "Deuda"
    ApExcel.Cells(1, 14).Formula = "Monto cheques"
    ApExcel.Cells(1, 15).Formula = "Cantidad cheques"
        
    pgbar.Min = 0
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    total_boletas = 0
    Monto_boletas = 0
    While Not objRS.EOF
        
        I = I + 1
        ApExcel.Cells(I, 1).Formula = objRS("ci_alu")
        ApExcel.Cells(I, 2).Formula = objRS("nombre_alumno")
        ApExcel.Cells(I, 3).Formula = objRS("num_gra")
        ApExcel.Cells(I, 4).Formula = objRS("nivel")
        ApExcel.Cells(I, 5).Formula = objRS("rut_apo")
        ApExcel.Cells(I, 6).Formula = objRS("nombre_apoderado")
        ApExcel.Cells(I, 7).Formula = objRS("fon_c_apo")
        ApExcel.Cells(I, 8).Formula = objRS("cel_apo")
        ApExcel.Cells(I, 9).Formula = objRS("becado")
        ApExcel.Cells(I, 10).Formula = objRS("correo_alumno")
        ApExcel.Cells(I, 11).Formula = objRS("correo_apoderado")
        ApExcel.Cells(I, 12).Formula = objRS("primer_semestre")
        ApExcel.Cells(I, 13).Formula = objRS("deuda")
        ApExcel.Cells(I, 13).NumberFormat = "#,##0"
        ApExcel.Cells(I, 14).Formula = objRS("monto_cheques")
        ApExcel.Cells(I, 15).Formula = objRS("cantidad_cheques")
        ' Aplica Formula
        'ApExcel.cells(3, 4).Formula = "=B3-C3"
        ' Hace una Seleccion de celdas y pone bordes de Color
        
        pgbar.Value = I - 1
        'pgBar.Refresh
        objRS.MoveNext
    Wend
    I = I + 1
    
   
    ApExcel.Cells(I, 1).Font.Bold = True
    ApExcel.Cells(I, 2).Font.Bold = True
    ApExcel.Cells(I, 3).Font.Bold = True
    ApExcel.Cells(I, 4).Font.Bold = True
    ApExcel.Cells(I, 5).Font.Bold = True
    ApExcel.Cells(I, 6).Font.Bold = True
    ApExcel.Cells(I, 7).Font.Bold = True
    ApExcel.Cells(I, 8).Font.Bold = True
    ApExcel.Cells(I, 9).Font.Bold = True
    ApExcel.Cells(I, 10).Font.Bold = True
    ApExcel.Cells(I, 11).Font.Bold = True
    ApExcel.Cells(I, 12).Font.Bold = True
    ApExcel.Cells(I, 13).Font.Bold = True
    ApExcel.Cells(I, 14).Font.Bold = True
    ApExcel.Cells(I, 15).Font.Bold = True
    
        
    ApExcel.Visible = True
    ApExcel.Range("a1:m1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
    
    objRS.Close
    
    End If

'    CrystalReport1.ReportFileName = App.Path & "\rpt\libro_ventas.rpt"
'
'    CrystalReport1.WindowState = crptMaximized
'    'CrystalReport1.Formulas(1) = "a = '" & menor & "'"
'    'CrystalReport1.Formulas(2) = "fecha = '" & mayor & "'"
'    CrystalReport1.Formulas(3) = "menor_ntcr = '" & menor & "'"
'    CrystalReport1.Formulas(4) = "mayor_ntcr = '" & Mayor & "'"
'    CrystalReport1.Formulas(5) = "monto_ntcr = '" & Monto & "'"
'    CrystalReport1.Formulas(6) = "nobecado_ntcr = '" & nobecado & "'"
'    CrystalReport1.Formulas(7) = "becado_ntcr = '" & becado & "'"
'    CrystalReport1.Formulas(8) = "excencion = '" & excencion & "'"
'
'   'cr_perso.WindowTitle = "Informe De Desarrollo Personal y Social"
''    cr_pre.ReplaceSelectionFormula ("")
'    'CrystalReport1.Formulas(1) = "establo = '" & nombre & "'"
''    cr_perso.Formulas(1) = "fecha = '" & Format(Now, "dd/mm/yyyy") & "'"
''    cr_perso.Formulas(2) = "hora = '" & Format(Now, "hh:mm AM/PM") & "'"
'    'cr_perso.ReplaceSelectionFormula ("{pre_eval_cab.cod_ens}='" & xcod & "' and {pre_eval_cab.num_gra}='" & CInt(xnum_gra) & "' and {pre_eval_cab.let_cur}='" & xletra & "' and {pre_eval_cab.agno}=" & str(xagno)) & Per
'    CrystalReport1.Destination = crptToWindow
'    CrystalReport1.Action = 1
End Sub

Private Sub Command1_Click()
On Error GoTo errores:
        Dim Titulo_info As String
        Dim Colegio_inf As String
        
        Colegio_inf = DevuelveCampo("establecimientos", "", "nom_est", "1=1", conn)
        Titulo_info = "Estadística de Alumnos" & IIf((Cmb_who.Text = "Todos"), " ", " " & Cmb_who.Text & " ") & Cmb_Matr.Text & IIf((Cmb_Reserva.Text = "Todos"), "", " " & Cmb_Reserva.Text)
        Titulo_info = UCase(Titulo_info)
        Command1.Enabled = False
        Command2.Enabled = False
        Command4.Enabled = False
        ' tabla de informacion estad_matr_prox_agno.rpt
        CreaTablaInformeMatrProxAgno
        ' Imprimo el reporte
        Call LimpiaFormulas(Form_main.CrystalReport, 30)
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\estad_matr.rpt"
        Form_main.CrystalReport.WindowTitle = "Estadística de Alumnos"
        Form_main.CrystalReport.WindowState = crptMaximized
        Form_main.CrystalReport.Formulas(1) = "colegio_info='" & Colegio_inf & "'"
        Form_main.CrystalReport.Formulas(2) = "titulo_info='" & Titulo_info & "'"
        Form_main.CrystalReport.Formulas(3) = "agno_informe='PARA EL AÑO " & cmb_agno.Text & "'"
        Form_main.CrystalReport.ReplaceSelectionFormula ("")
        Form_main.CrystalReport.Destination = crptToWindow
        Form_main.CrystalReport.Action = 1
        Command1.Enabled = True
        Command2.Enabled = True
        Command4.Enabled = True
        Exit Sub
errores:
        Command1.Enabled = True
        Command2.Enabled = True
        Command4.Enabled = True
End Sub

Private Sub Command2_Click()
'On Error GoTo errores:
        Dim Titulo_info As String
        Dim Colegio_inf As String
        
        Colegio_inf = DevuelveCampo("establecimientos", "", "nom_est", "1=1", conn)
        Titulo_info = "Listado de Alumnos" & IIf((Cmb_who.Text = "Todos"), " ", " " & Cmb_who.Text & " ") & Cmb_Matr.Text & IIf((Cmb_Reserva.Text = "Todos"), "", " " & Cmb_Reserva.Text)
        Titulo_info = UCase(Titulo_info)
      
        ' tabla de la info
        
    If Cmb_who.Text = "Nuevos" And Cmb_Matr.Text = "Matriculados" Then
    var_sql = "DROP TABLE IF EXISTS temp_mat_nuevos;"
    conn.Execute var_sql
    
    If Colegio_inf = "CENTRO EDUCACIONAL LINCOLN COLLEGE" Then
        var_sql = "CREATE TABLE temp_mat_nuevos SELECT @ROW_NUMBER:=@ROW_NUMBER+1 AS ROW_NUMBER , tmp_reporte_matriculas_old.* FROM (" _
            & " select * from (SELECT f.*,folio_boleta_fisico,fecha_blta,monto_pago FROM" _
            & " (SELECT m.cod_ens,m.num_gra,fdm.folio_dtll,p.ci_alu,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,fec_mat,CONCAT(CAST(IF(m.cod_ens = '310' OR m.cod_ens = '110',CONCAT(m.num_gra,' '),'') AS CHAR),IF(m.cod_ens = '111','PlayGroup'," _
            & " IF(m.cod_ens = '996','PreKinder',IF(m.cod_ens = '010','Kinder',IF(m.cod_ens = '110','Basico','Media'))))) AS curso," _
            & " a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado FROM matriculas m,postulantes p,apo_x_alu AS axa,apoderados AS a,fnza_detalle_mensualidad fdm WHERE fec_mat_r IS NOT NULL AND confirmado = 'SI' and anno_mat = " & cmb_agno.Text & " AND activo_alu = 'SI' AND m.ci_alu = p.ci_alu AND" _
            & " m.ci_alu = axa.ci_alu And m.anno_mat = axa.agno And axa.rut_apo = a.rut_apo" _
            & " AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND fdm.mes = 3 AND p.ci_alu NOT IN (SELECT DISTINCT ci_alu FROM alu_x_cur WHERE activo_alu = 'SI' and agno = " & (cmb_agno - 1) & ")) AS f" _
            & " Left Join (SELECT fdm.folio_dtll, fb.folio_blta_fisico AS folio_boleta_fisico, fb.fecha_blta, fmm.monto_pago AS monto_pago FROM fnza_boletas fb, fnza_dtll_mvto fdm, fnza_movimientos_mensualidad fmm WHERE fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fb.estado_blta <> 'nula'" _
            & " AND fmm.cod_concepto = 'ct00' GROUP BY fb.folio_blta_fisico) AS b ON f.folio_dtll = b.folio_dtll ORDER BY f.cod_ens,f.num_gra,fec_mat) as d group by ci_alu ORDER BY d.cod_ens,d.num_gra,d.fec_mat) AS tmp_reporte_matriculas_old, (SELECT @ROW_NUMBER:=0) r;"
        conn.Execute var_sql
    
    Else
        var_sql = "CREATE TABLE temp_mat_nuevos SELECT @ROW_NUMBER:=@ROW_NUMBER+1 AS ROW_NUMBER , tmp_reporte_matriculas_old.* FROM (" _
            & " select * from (SELECT f.*,folio_boleta_fisico,fecha_blta,monto_pago FROM" _
            & " (SELECT m.cod_ens,m.num_gra,fdm.folio_dtll,p.ci_alu,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,fec_mat,CONCAT(CAST(IF(m.cod_ens = '310' OR m.cod_ens = '110',CONCAT(m.num_gra,' '),'') AS CHAR),IF(m.cod_ens = '111','PlayGroup'," _
            & " IF(m.cod_ens = '996','PreKinder',IF(m.cod_ens = '010','Kinder',IF(m.cod_ens = '110','Basico','Media'))))) AS curso," _
            & " a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado FROM matriculas m,postulantes p,apo_x_alu AS axa,apoderados AS a,fnza_detalle_mensualidad fdm WHERE fec_mat_r IS NOT NULL AND confirmado = 'SI' and anno_mat = " & cmb_agno.Text & " AND activo_alu = 'SI' AND m.ci_alu = p.ci_alu AND" _
            & " m.ci_alu = axa.ci_alu And m.anno_mat = axa.agno And axa.rut_apo = a.rut_apo" _
            & " AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno AND concepto = 'ct00' AND p.ci_alu NOT IN (SELECT DISTINCT ci_alu FROM alu_x_cur WHERE activo_alu = 'SI' and agno = " & (cmb_agno - 1) & ")) AS f" _
            & " Left Join (SELECT fdm.folio_dtll, fb.folio_blta_fisico AS folio_boleta_fisico, fb.fecha_blta, fmm.monto_pago AS monto_pago FROM fnza_boletas fb, fnza_dtll_mvto fdm, fnza_movimientos_mensualidad fmm WHERE fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fb.estado_blta <> 'nula'" _
            & " AND fmm.cod_concepto = 'ct00' GROUP BY fb.folio_blta_fisico) AS b ON f.folio_dtll = b.folio_dtll ORDER BY f.cod_ens,f.num_gra,fec_mat) as d group by ci_alu ORDER BY d.cod_ens,d.num_gra,d.fec_mat) AS tmp_reporte_matriculas_old, (SELECT @ROW_NUMBER:=0) r;"
        conn.Execute var_sql
    End If
        Call LimpiaFormulas(Form_main.CrystalReport, 30)
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\alumnos_nuevos_matriculados.rpt"
        Form_main.CrystalReport.WindowTitle = "Proceso de Alumnos Nuevos Postulados"
        
        Form_main.CrystalReport.ReplaceSelectionFormula ("")
        
        Form_main.CrystalReport.Formulas(2) = "titulo='ALUMNOS NUEVOS MATRICULADOS " & cmb_agno & " " & Colegio_inf & "'"
        Form_main.CrystalReport.WindowState = crptMaximized
       ' Form_main.CrystalReport.Formulas(1) = "colegio_info='" & Colegio_inf & "'"
        Form_main.CrystalReport.Destination = crptToWindow
        Form_main.CrystalReport.Action = 1
            
    Exit Sub
    Else
        Command1.Enabled = False
        Command2.Enabled = False
        Command4.Enabled = False
        CreaTablaInformeMatrProxAgno
    End If
        
    If Cmb_who = "Nuevos" And Cmb_Matr = "Postulados" Then
        'Actualizo seguimiento
        'Creo tabla con fecha en orden correcto y con ultimo comentario
        var_sql = "DROP TABLE IF EXISTS temp_postulante_web_seguimiento;"
        conn.Execute var_sql

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

        var_sql = "CREATE TABLE temp_postulante_web_seguimiento TYPE = MYISAM" _
            & " SELECT rut_pos,comentario,usuario,CONCAT(MID(fecha,7,4),'-',MID(fecha,4,2),'-',MID(fecha,1,2),' ',hora) AS fecha_hora,CONCAT(MID(fecha,7,4),'-',MID(fecha,4,2),'-',MID(fecha,1,2)) AS fecha,hora,renuncia" _
            & " From postulante_web_seguimiento" _
            & " WHERE MID(fecha,7,4) >= 2015;"
        conn.Execute var_sql

        var_sql = "CREATE TABLE temp_postulante_web_seguimiento_2 TYPE = MYISAM" _
            & " SELECT a.* FROM temp_postulante_web_seguimiento AS a," _
            & " (SELECT rut_pos,MAX(fecha_hora) AS fecha_hora" _
            & " From temp_postulante_web_seguimiento" _
            & " GROUP BY rut_pos) AS b" _
            & " WHERE a.rut_pos = b.rut_pos AND a.fecha_hora = b.fecha_hora;"
        conn.Execute var_sql

        var_sql = "UPDATE tmp_reporte_matriculas m,temp_postulante_web_seguimiento_2 pw " _
                & " SET m.vigente = IF(renuncia = 1,'SI',IF(renuncia = 0,'NO','SI'))" _
                & " WHERE m.run = pw.rut_pos and MID(fecha,1,4) = " & (cmb_agno - 1) & " ;"
        conn.Execute var_sql
        
        var_sql = "UPDATE tmp_reporte_matriculas m,temp_postulante_web_seguimiento_2 pw" _
                & " SET m.vigente = IF(renuncia = 1,'SI',IF(renuncia = 0,'NO','SI'))" _
                & " WHERE m.run = pw.rut_pos and MID(fecha,1,4) = " & (cmb_agno - 1) & " ;"
        conn.Execute var_sql
        
        ' Modifico largo de oficina
        'var_sql = "ALTER TABLE tmp_reporte_matriculas MODIFY oficina VARCHAR(10);"
        'conn.Execute var_sql
        
        'Modifico origen postulacion
        var_sql = "UPDATE tmp_reporte_matriculas r,postulante_web p SET r.oficina = UCASE(origen),nombre = ucase(nombre),r.curso_pos = ucase(r.curso_pos)" _
                & " WHERE r.run = p.rut_pos AND p.anio = " & cmb_agno.Text
        conn.Execute var_sql
                
        ' reporte
        Call LimpiaFormulas(Form_main.CrystalReport, 30)
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\reporte_matriculas_postulados.rpt"
        Form_main.CrystalReport.WindowTitle = "Proceso de Alumnos Nuevos Postulados"
        
        Form_main.CrystalReport.ReplaceSelectionFormula ("")
        
        Form_main.CrystalReport.Formulas(2) = "titulo='Alumnos Nuevos Postulados para el " & cmb_agno & "'"
        Form_main.CrystalReport.WindowState = crptMaximized
        Form_main.CrystalReport.Formulas(1) = "colegio_info='" & Colegio_inf & "'"
        Form_main.CrystalReport.Destination = crptToWindow
        Form_main.CrystalReport.Action = 1
        
    Else
    
        If Cmb_who = "Antiguos" And (Cmb_Matr = "No Matriculados" Or Cmb_Matr = "Todos") Then
         ' reporte
        var_sql = "UPDATE `tmp_reporte_matriculas` SET deuda = 0 WHERE deuda < 0"
        conn.Execute var_sql
        Call LimpiaFormulas(Form_main.CrystalReport, 30)
        
        If Cmb_Matr = "Todos" Then
            Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\reporte_matriculados_todos_alumnos.rpt"
        Else
            Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\reporte_matriculas_nomatriculados.rpt"
        End If
        
        Form_main.CrystalReport.WindowTitle = "Proceso de Alumnos Nuevos Postulados"
        
        Form_main.CrystalReport.ReplaceSelectionFormula ("")
        
        'Form_main.CrystalReport.Formulas(2) = "titulo='Proceso de Alumnos Nuevos Postulados para el " & Cmb_Agno & "'"
        Form_main.CrystalReport.Formulas(2) = "titulo='Todos los Alumnos Antiguos para el " & cmb_agno & "'"
        Form_main.CrystalReport.WindowState = crptMaximized
        Form_main.CrystalReport.Formulas(1) = "colegio_info='" & Colegio_inf & "'"
        Form_main.CrystalReport.Destination = crptToWindow
        Form_main.CrystalReport.Action = 1
        
        muestra_excel
        
        Else
        
        ' reporte
        Call LimpiaFormulas(Form_main.CrystalReport, 30)
        'Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\info_matr_prox_agno.rpt"
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\reporte_matriculas.rpt"
        Form_main.CrystalReport.WindowTitle = "Listado de Alumnos Matriculados"
        
        Form_main.CrystalReport.ReplaceSelectionFormula ("")
        
        If Cmb_who = "Todos" Then
            Form_main.CrystalReport.Formulas(2) = "titulo='" & Cmb_who.Text & " los Alumnos Matriculados para el " & cmb_agno & "'"
        End If
        
        If Cmb_who = "Nuevos" Then
            If Cmb_Matr = "Todos" Then
                Form_main.CrystalReport.Formulas(2) = "titulo='Todos los Alumnos Nuevos Matriculados para el " & cmb_agno & "'"
            Else
                Form_main.CrystalReport.Formulas(2) = "titulo='Todos los Alumnos Nuevos Postulados para el " & cmb_agno & "'"
            End If
        End If
        
        If Cmb_who = "Antiguos" Then
            If Cmb_Matr = "Matriculados" Then
                Form_main.CrystalReport.Formulas(2) = "titulo='Todos los Alumnos Antiguos Matriculados para el " & cmb_agno & "'"
            'Else
                'Form_main.CrystalReport.Formulas(2) = "titulo='Todos los Alumnos Antiguos No Matriculados para el " & Cmb_Agno & "'"
            End If
        End If
        
        'Form_main.CrystalReport.Formulas(2) = "titulo='" & Cmb_who.Text & " " & Cmb_Matr & " " & Cmb_Agno & "'"
        
        Form_main.CrystalReport.WindowState = crptMaximized
        Form_main.CrystalReport.Formulas(1) = "colegio_info='" & Colegio_inf & "'"
        'Form_main.CrystalReport.Formulas(2) = "titulo_info='" & Titulo_info & "'"
        

        
        'Form_main.CrystalReport.ReplaceSelectionFormula ("")
        Form_main.CrystalReport.Destination = crptToWindow
        Form_main.CrystalReport.Action = 1
        
        End If
        
    End If
        
        Command1.Enabled = True
        Command2.Enabled = True
        Command4.Enabled = True
        Exit Sub
        
'errores:
 '       Command1.Enabled = True
'        Command2.Enabled = True
'        Command4.Enabled = True
End Sub

Private Sub Command3_Click()
    Me.Visible = False
    Unload Me
End Sub

Private Sub Command4_Click()
On Error GoTo errores:
        Dim Titulo_info As String
        Dim Colegio_inf As String
        Dim Prefix As String
        Dim Infix As String
        
        Colegio_inf = DevuelveCampo("establecimientos", "", "nom_est", "1=1", conn)
        Titulo_info = "Detalle Alumnos" & IIf((Cmb_who.Text = "Todos"), " ", " " & Cmb_who.Text & " ") & Cmb_Matr.Text & IIf((Cmb_Reserva.Text = "Todos"), "", " " & Cmb_Reserva.Text) & " Año " & cmb_agno.Text
        Prefix = IIf(Cmb_who.Text = "Antiguos", "De", "Para")
        Infix = IIf(Cmb_who.Text = "Antiguos", "grado", "grado")
        Command1.Enabled = False
        Command2.Enabled = False
        Command4.Enabled = False
        ' tabla de informacion estad_matr_prox_agno.rpt
        CreaTablaInformeMatrProxAgno
        ' Imprimo el reporte
        Call LimpiaFormulas(Form_main.CrystalReport, 30)
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\est_det_matantproxagno.rpt"
        Form_main.CrystalReport.WindowTitle = "Estadística de Alumnos"
        Form_main.CrystalReport.WindowState = crptMaximized
        Form_main.CrystalReport.Formulas(1) = "colegio='" & Colegio_inf & "'"
        Form_main.CrystalReport.Formulas(2) = "titulo_info='" & Cmb_who.Text & "'"
        Form_main.CrystalReport.Formulas(3) = "Prefix1='" & Prefix & "'"
        Form_main.CrystalReport.Formulas(4) = "Infix2='" & Infix & "'"
        Form_main.CrystalReport.ReplaceSelectionFormula ("")
        Form_main.CrystalReport.Destination = crptToWindow
        Form_main.CrystalReport.Action = 1
        Command1.Enabled = True
        Command2.Enabled = True
        Command4.Enabled = True
        Exit Sub
        
errores:
        Command1.Enabled = True
        Command2.Enabled = True
        Command4.Enabled = True
End Sub

Private Sub Form_Load()
Dim var As Integer
    
    var = Year(Date) + 1
    
    While var >= 2011
        cmb_agno.AddItem var
        var = var - 1
    Wend
    
    cmb_agno.Text = Year(Date)
    
    'Combo estado reserva
    Cmb_Reserva.AddItem "Todos"
    'Cmb_Reserva.AddItem "Reservados"
    'Cmb_Reserva.AddItem "NO Reservados"
    'Cmb_Reserva.AddItem "Renunciados"
    'Cmb_Reserva.AddItem "NO Renunciados"
    Cmb_Reserva.Text = "Todos"
    
    'Combo Alumnos
    'Cmb_who.AddItem "Todos"
    Cmb_who.AddItem "Nuevos"
    Cmb_who.AddItem "Antiguos"
    'Cmb_who.AddItem "Todos"
    'Cmb_who.Text = "Todos"
    oculta_excel
    
    'Con estos update se dejar igual CRM-interesados que la WEB
    var_sql = "UPDATE postulante_web SET curso_pos = 'PLAYGROUP' WHERE curso_pos = ' Play Group';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = 'PREKINDER' WHERE curso_pos = ' Pre Kinder';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = 'KINDER' WHERE curso_pos = ' Parvularia';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '1 BASICO' WHERE curso_pos = '1º Básico';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '2 BASICO' WHERE curso_pos = '2º Básico';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '3 BASICO' WHERE curso_pos = '3º Básico';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '4 BASICO' WHERE curso_pos = '4º Básico';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '5 BASICO' WHERE curso_pos = '5º Básico';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '6 BASICO' WHERE curso_pos = '6º Básico';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '7 BASICO' WHERE curso_pos = '7º Básico';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '8 BASICO' WHERE curso_pos = '8º Básico';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '1 MEDIO' WHERE curso_pos = '1º Medio';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '2 MEDIO' WHERE curso_pos = '2º Medio';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '3 MEDIO' WHERE curso_pos = '3º Medio';"
    conn.Execute var_sql
    var_sql = "UPDATE postulante_web SET curso_pos = '4 MEDIO' WHERE curso_pos = '4º Medio';"
    conn.Execute var_sql
    
End Sub

Sub CreaTablaInformeMatrProxAgno()
On Error GoTo controlerr:
    Dim QRY As String
    Dim Sql As String
    Dim CMD As ADODB.Command
    Dim QRY_P1 As String
    Dim QRY_P2 As String
    Dim QRY_TO As String
    Dim EsColegio As String
    Dim cole As String
    
    cole = DevuelveCampo("cfg", "valor", "valor", "param = 'CODBC'", conn)
    If cole = "LIN001" Then
        ttabla = "FNZA_DETALLE_MENSUALIDAD.mes"
        mmes = DevuelveCampo("cfg", "date_format(now(),'%m')", "date_format(now(),'%m')", "param = 'CODBC'", conn)
        mmes = IIf(mmes = 1 Or mmes = 2, 3, mmes)
    Else
        ttabla = "FNZA_DETALLE_MENSUALIDAD.concepto"
        mmes = "ct00"
    End If
        
    Set CMD = New ADODB.Command
    CMD.ActiveConnection = conn
    CMD.CommandType = adCmdText
    
    QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas;"
    CMD.CommandText = QRY
    CMD.Execute
    
    QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas_old;"
    CMD.CommandText = QRY
    CMD.Execute
    
    QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas_old2;"
    CMD.CommandText = QRY
    CMD.Execute
    
    ' para quienes?
    If Cmb_who.Text = "Todos" Then

        QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT * FROM (SELECT * FROM (" _
        & "SELECT DISTINCT MATRICULAS.CI_ALU, CONCAT(POSTULANTES.APE_PAT_ALU, POSTULANTES.APE_MAT_ALU, MID(POSTULANTES.NOM_ALU, 1, 3)) AS cadena," _
        & "CONCAT(POSTULANTES.APE_PAT_ALU, ' ', POSTULANTES.APE_MAT_ALU, ' ', POSTULANTES.NOM_ALU) AS NOMBRE_ALUMNO, MATRICULAS.NUM_GRA," _
        & "IF(MATRICULAS.COD_ENS = '111', 'PG', IF(MATRICULAS.COD_ENS = '222', 'PGMM', IF(MATRICULAS.COD_ENS = '996', 'PK', IF(MATRICULAS.COD_ENS = '010', 'K', IF(MATRICULAS.COD_ENS = '110', 'B', 'M'))))) AS nivel," _
        & "APO_X_ALU.RUT_APO, CONCAT(APODERADOS.APE1_APO,' ', APODERADOS.APE2_APO, ' ', APODERADOS.NOMS_APO) AS NOMBRE_APODERADO, APODERADOS.FON_C_APO, FNZA_DETALLE_MENSUALIDAD.folio_dtll AS folio_matricula" _
        & " From MATRICULAS, FNZA_DETALLE_MENSUALIDAD, APO_X_ALU, APODERADOS, POSTULANTES" _
        & " Where FNZA_DETALLE_MENSUALIDAD.run_alu = MATRICULAS.ci_alu" _
        & " AND FNZA_DETALLE_MENSUALIDAD.AGNO = MATRICULAS.ANNO_MAT AND MATRICULAS.ANNO_MAT = '" & cmb_agno & "'" _
        & " AND matriculas.fec_mat_r IS NOT NULL AND MATRICULAS.CONFIRMADO = 'SI' AND MATRICULAS.ACTIVO_ALU = 'SI'" _
        & " AND APO_X_ALU.CI_ALU = MATRICULAS.CI_ALU AND APO_X_ALU.RUT_APO = APODERADOS.RUT_APO AND APO_X_ALU.AGNO = '" & cmb_agno & "'" _
        & " AND " & ttabla & " = '" & mmes & "' AND POSTULANTES.CI_ALU = MATRICULAS.CI_ALU" _
        & ") AS matriculas_validas LEFT JOIN(" _
        & "SELECT fdm.folio_dtll AS folio_boleta, fb.folio_blta_fisico AS folio_boleta_fisico, fb.fecha_blta, fmm.monto_pago AS monto_pago" _
        & " FROM fnza_boletas fb, fnza_dtll_mvto fdm, fnza_movimientos_mensualidad fmm" _
        & " Where fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fb.estado_blta <> 'nula'" _
        & " AND cod_concepto = 'ct00' GROUP BY fb.folio_blta_fisico) AS boletas" _
        & " ON matriculas_validas.folio_matricula = boletas.folio_boleta) AS a"
        
    a = 1
    
    ElseIf Cmb_who.Text = "Nuevos" Then
    
    If Cmb_Matr.Text = "Todos" Then
        QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT * FROM (SELECT * FROM (" _
        & "SELECT DISTINCT MATRICULAS.CI_ALU, CONCAT(POSTULANTES.APE_PAT_ALU, POSTULANTES.APE_MAT_ALU, MID(POSTULANTES.NOM_ALU, 1, 3)) AS cadena," _
        & "CONCAT(POSTULANTES.APE_PAT_ALU, ' ', POSTULANTES.APE_MAT_ALU, ' ', POSTULANTES.NOM_ALU) AS NOMBRE_ALUMNO, MATRICULAS.NUM_GRA," _
        & "IF(MATRICULAS.COD_ENS = '111', 'PG', IF(MATRICULAS.COD_ENS = '222', 'PGMM', IF(MATRICULAS.COD_ENS = '996', 'PK', IF(MATRICULAS.COD_ENS = '010', 'K', IF(MATRICULAS.COD_ENS = '110', 'B', 'M'))))) AS nivel," _
        & "APO_X_ALU.RUT_APO, CONCAT(APODERADOS.APE1_APO,' ', APODERADOS.APE2_APO, ' ', APODERADOS.NOMS_APO) AS NOMBRE_APODERADO, APODERADOS.FON_C_APO, FNZA_DETALLE_MENSUALIDAD.folio_dtll AS folio_matricula" _
        & " From MATRICULAS, FNZA_DETALLE_MENSUALIDAD, APO_X_ALU, APODERADOS, POSTULANTES" _
        & " Where FNZA_DETALLE_MENSUALIDAD.run_alu = MATRICULAS.ci_alu" _
        & " AND FNZA_DETALLE_MENSUALIDAD.AGNO = MATRICULAS.ANNO_MAT AND MATRICULAS.ANNO_MAT = '" & cmb_agno & "'" _
        & " AND MATRICULAS.CONFIRMADO = 'SI' and fec_mat_r IS NOT NULL AND MATRICULAS.ACTIVO_ALU = 'SI'" _
        & " AND APO_X_ALU.CI_ALU = MATRICULAS.CI_ALU AND APO_X_ALU.RUT_APO = APODERADOS.RUT_APO AND APO_X_ALU.AGNO = '" & cmb_agno & "'" _
        & " AND " & ttabla & " = '" & mmes & "' AND POSTULANTES.CI_ALU = MATRICULAS.CI_ALU" _
        & ") AS matriculas_validas LEFT JOIN(" _
        & "SELECT fdm.folio_dtll AS folio_boleta, fb.folio_blta_fisico AS folio_boleta_fisico, fb.fecha_blta, fmm.monto_pago AS monto_pago" _
        & " FROM fnza_boletas fb, fnza_dtll_mvto fdm, fnza_movimientos_mensualidad fmm" _
        & " Where fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fb.estado_blta <> 'nula'" _
        & " AND fmm.cod_concepto = 'ct00' GROUP BY fb.folio_blta_fisico) AS boletas" _
        & " ON matriculas_validas.folio_matricula = boletas.folio_boleta) AS a" _
        & " WHERE a.ci_alu NOT IN (SELECT CI_ALU FROM MATRICULAS WHERE activo_alu = 'SI' and ANNO_MAT = '" & cmb_agno - 1 & "')"
    Else
               
        'aqui modificar
        QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT IF(run IS NULL, 'SIN RUN', run) AS run, nombre, curso_pos, IF(postulado IS NULL, 'NO', 'SI') AS postulado," _
        & "IF(inscrito IS NULL, 'NO', 'SI') AS inscrito, IF(prueba_asignada IS NULL, 'NO', 'SI') AS prueba_asignada, IF(evaluado IS NULL, 'NO', 'SI') AS evaluado," _
        & "IF(ci_alu IS NULL, 'NO','SI') AS matriculado FROM (" _
        & "SELECT nombre, run, postulado, inscrito, prueba_asignada, VIII_RES_EVAL AS evaluado, curso_pos FROM (" _
        & "SELECT nombre, run, postulado, inscrito, ci_alu AS prueba_asignada, curso_pos FROM (" _
        & "SELECT CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', nombres_pos) AS nombre, ci_alu AS run, ape_pat_pos AS postulado, ape_pat_alu AS inscrito, curso_pos" _
        & " FROM postulante_web LEFT JOIN postulantes ON CAST(CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', MID(nombres_pos,1,3)) AS CHAR) = CAST(CONCAT(ape_pat_alu, ' ', ape_mat_alu, ' ', MID(nom_alu,1,3)) AS CHAR)" _
        & ") AS tabla LEFT JOIN postulaciones ON run = ci_alu) AS postulados_evaluados" _
        & " LEFT JOIN postulaciones p ON run = p.ci_alu) AS postulados_evaluados LEFT JOIN matriculas ON run = ci_alu" _
        & " GROUP BY nombre ORDER BY curso_pos, nombre, postulado, inscrito, prueba_asignada, evaluado, matriculado"

        If cmb_agno.Text = cmb_agno.Text Then
        ' aqui modificar postulados
            QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT rut_apo,telefono,fec_pos as fec_ins,IF(anio < 2010,CAST(CONCAT(MID(fecha,7,4),'/',MID(fecha,4,2),'/',MID(fecha,1,2)) AS DATE),fecha) AS fec_pos,numero,if(renuncia = 1,'NO','SI') vigente,IF(run IS NULL, 'SIN RUN', run) AS run, nombre, curso_pos, IF(postulado IS NULL, 'NO', 'SI') AS postulado,IF(inscrito IS NULL, 'NO', 'SI') AS inscrito," _
                & "IF(prueba_asignada IS NULL, 'NO', 'SI') AS prueba_asignada, IF(evaluado IS NULL,'',evaluado) AS nota,IF(ci_alu IS NULL, 'NO','SI') AS matriculado,IF(oficina = 1,'OFICINA','WEB') AS oficina" _
                & " FROM (SELECT rut_apo,anio,fec_pos,telefono,fecha,numero,renuncia,oficina,nombre, run, postulado, inscrito, prueba_asignada, VIII_RES_EVAL AS evaluado, curso_pos" _
                & " FROM (SELECT rut_apo,anio,telefono,fecha,numero,renuncia,oficina,nombre, run, postulado, inscrito, ci_alu AS prueba_asignada, curso_pos" _
                & " FROM (SELECT rut_apo,anio,telefono,fecha,numero,renuncia,oficina,CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', nombres_pos) AS nombre, rut_pos AS run, ape_pat_pos AS postulado, ape_pat_alu AS inscrito, curso_pos" _
                & " FROM (select * from postulante_web where anio = " & cmb_agno.Text & ") as postulante_web LEFT JOIN postulantes ON rut_pos =" _
                & " ci_alu) AS tabla LEFT JOIN (select * from postulaciones where anno_pos_alu = " & cmb_agno.Text & ") as postulaciones" _
                & " ON run = ci_alu) AS postulados_evaluados LEFT JOIN (select * from postulaciones where anno_pos_alu = " & cmb_agno.Text & ") as p ON run = p.ci_alu)" _
                & " AS postulados_evaluados LEFT JOIN (select * from matriculas where anno_mat = " & cmb_agno.Text & ") as matriculas ON run = ci_alu" _
                & " GROUP BY nombre" _
                & " ORDER BY numero"
        
'            QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT nombre, run, postulado, inscrito, ci_alu AS prueba_asignada, curso_pos" _
'                    & " FROM (SELECT CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', nombres_pos) AS nombre, ci_alu AS run, ape_pat_pos" _
'                    & " AS postulado, ape_pat_alu AS inscrito, curso_pos FROM (select * from postulante_web where anio is null)as postulante_web" _
'                    & " LEFT JOIN postulantes ON CAST(CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', MID(nombres_pos,1,3))" _
'                    & " AS CHAR) = CAST(CONCAT(ape_pat_alu, ' ', ape_mat_alu, ' ', MID(nom_alu,1,3)) AS CHAR)) AS tabla" _
'                    & " LEFT JOIN (select * from postulaciones where anno_pos_alu = " & Cmb_Agno.Text & ") as postulaciones ON run = ci_alu"
        Else
            QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT IF(run IS NULL, 'SIN RUN', run) AS run, nombre, curso_pos, IF(postulado IS NULL, 'NO', 'SI') AS postulado,IF(inscrito IS NULL, 'NO', 'SI') AS inscrito," _
                & "IF(prueba_asignada IS NULL, 'NO', 'SI') AS prueba_asignada, IF(evaluado IS NULL, 'NO', 'SI') AS evaluado,IF(ci_alu IS NULL, 'NO','SI') AS matriculado" _
                & " FROM (SELECT nombre, run, postulado, inscrito, prueba_asignada, VIII_RES_EVAL AS evaluado, curso_pos" _
                & " FROM (SELECT nombre, run, postulado, inscrito, ci_alu AS prueba_asignada, curso_pos" _
                & " FROM (SELECT CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', nombres_pos) AS nombre, ci_alu AS run, ape_pat_pos AS postulado, ape_pat_alu AS inscrito, curso_pos" _
                & " FROM (select * from postulante_web where anio = " & cmb_agno.Text & ") as postulante_web LEFT JOIN postulantes ON CAST(CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', MID(nombres_pos,1,3)) AS CHAR) =" _
                & " CAST(CONCAT(ape_pat_alu, ' ', ape_mat_alu, ' ', MID(nom_alu,1,3)) AS CHAR)) AS tabla LEFT JOIN (select * from postulaciones where anno_pos_alu = " & cmb_agno.Text & ") as postulaciones" _
                & " ON run = ci_alu) AS postulados_evaluados LEFT JOIN (select * from postulaciones where anno_pos_alu = " & cmb_agno.Text & ") as p ON run = p.ci_alu)" _
                & " AS postulados_evaluados LEFT JOIN (select * from matriculas where anno_mat = " & cmb_agno.Text & ") as matriculas ON run = ci_alu" _
                & " GROUP BY nombre" _
                & " ORDER BY curso_pos, nombre, postulado, inscrito, prueba_asignada, evaluado, matriculado"
        
'            QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT nombre, run, postulado, inscrito, ci_alu AS prueba_asignada, curso_pos" _
'                    & " FROM (SELECT CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', nombres_pos) AS nombre, ci_alu AS run, ape_pat_pos" _
'                    & " AS postulado, ape_pat_alu AS inscrito, curso_pos FROM (select * from postulante_web where anio = " & Cmb_Agno.Text & ")as postulante_web" _
'                    & " LEFT JOIN postulantes ON CAST(CONCAT(ape_pat_pos, ' ', ape_mat_pos, ' ', MID(nombres_pos,1,3))" _
'                    & " AS CHAR) = CAST(CONCAT(ape_pat_alu, ' ', ape_mat_alu, ' ', MID(nom_alu,1,3)) AS CHAR)) AS tabla" _
'                    & " LEFT JOIN (select * from postulaciones where anno_pos_alu = " & Cmb_Agno.Text & ") as postulaciones ON run = ci_alu"
        End If

        a = "Postulados"
    End If
        
   a = 1

    ElseIf Cmb_who.Text = "Antiguos" Then
        ' matriculados?
        
        If Cmb_Matr.Text = "Todos" Then
            QRY = "CREATE TABLE tmp_reporte_matriculas_old2 TYPE=MYISAM SELECT * FROM (SELECT * FROM (" _
            & "SELECT DISTINCT MATRICULAS.CI_ALU,fec_mat_r," _
            & "CONCAT(POSTULANTES.APE_PAT_ALU, ' ', POSTULANTES.APE_MAT_ALU, ' ', POSTULANTES.NOM_ALU) AS NOMBRE_ALUMNO, MATRICULAS.NUM_GRA," _
            & "IF(MATRICULAS.COD_ENS = '111', 'PG', IF(MATRICULAS.COD_ENS = '222', 'PGMM', IF(MATRICULAS.COD_ENS = '996', 'PK', IF(MATRICULAS.COD_ENS = '010', 'K', IF(MATRICULAS.COD_ENS = '110', 'B', 'M'))))) AS nivel,let_cur as letra," _
            & "APO_X_ALU.RUT_APO, CONCAT(APODERADOS.APE1_APO,' ', APODERADOS.APE2_APO, ' ', APODERADOS.NOMS_APO) AS NOMBRE_APODERADO, APODERADOS.FON_C_APO,APODERADOS.CEL_APO ,IF (MATRICULAS.ind_beca <> '1', 'Sin Beca', 'Con Beca') AS Becado, FNZA_DETALLE_MENSUALIDAD.folio_dtll AS folio_matricula, email AS Correo_Apoderado, email_alu AS Correo_Alumno,'NO' as matriculado" _
            & " From MATRICULAS, FNZA_DETALLE_MENSUALIDAD, APO_X_ALU, APODERADOS, POSTULANTES, ALU_X_CUR" _
            & " Where FNZA_DETALLE_MENSUALIDAD.run_alu = MATRICULAS.ci_alu" _
            & " AND FNZA_DETALLE_MENSUALIDAD.AGNO = MATRICULAS.ANNO_MAT AND MATRICULAS.ANNO_MAT = '" & cmb_agno - 1 & "'" _
            & " AND MATRICULAS.CONFIRMADO = 'SI' and fec_mat_r IS NOT NULL AND MATRICULAS.ACTIVO_ALU = 'SI'" _
            & " AND APO_X_ALU.CI_ALU = MATRICULAS.CI_ALU AND APO_X_ALU.RUT_APO = APODERADOS.RUT_APO AND APO_X_ALU.AGNO = '" & cmb_agno - 1 & "'" _
            & " AND " & ttabla & " = '" & mmes & "' And POSTULANTES.ci_alu = MATRICULAS.ci_alu " _
            & " And ALU_X_CUR.ci_alu = MATRICULAS.ci_alu AND ALU_X_CUR.AGNO = MATRICULAS.ANNO_MAT AND ALU_X_CUR.COD_ENS = MATRICULAS.COD_ENS" _
            & " AND ALU_X_CUR.NUM_GRA = MATRICULAS.NUM_GRA AND ALU_X_CUR.ACTIVO_ALU = 'SI'" _
            & ") AS matriculas_validas LEFT JOIN(" _
            & "SELECT fb.run_alu, fdm.folio_dtll AS folio_boleta, fb.folio_blta_fisico AS folio_boleta_fisico, fb.fecha_blta, fmm.monto_pago AS monto_pago" _
            & " FROM fnza_boletas fb, fnza_dtll_mvto fdm, fnza_movimientos_mensualidad fmm" _
            & " Where fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fb.estado_blta <> 'nula' AND fmm.cod_concepto = 'ct00'" _
            & " AND fech_venc_cptos > '" & cmb_agno - 1 & "-01-01' AND fech_venc_cptos <= '" & cmb_agno - 1 & "-12-31' GROUP BY fb.folio_blta_fisico) AS boletas" _
            & " ON matriculas_validas.folio_matricula = boletas.folio_boleta AND matriculas_validas.ci_alu = boletas.run_alu) AS a" '_
            ' & " WHERE a.ci_alu NOT IN (SELECT CI_ALU FROM MATRICULAS WHERE ANNO_MAT = '" & cmb_agno & "' AND activo_alu = 'SI' AND confirmado = 'SI' and fec_mat_r IS NOT NULL)"
            muestra_excel
          a = 2
        
        
        
        
        Else
        If Cmb_Matr.Text = "Matriculados" Then
            QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT * FROM (SELECT * FROM (" _
            & "SELECT DISTINCT MATRICULAS.CI_ALU, CONCAT(POSTULANTES.APE_PAT_ALU, POSTULANTES.APE_MAT_ALU, MID(POSTULANTES.NOM_ALU, 1, 3)) AS cadena," _
            & "CONCAT(POSTULANTES.APE_PAT_ALU, ' ', POSTULANTES.APE_MAT_ALU, ' ', POSTULANTES.NOM_ALU) AS NOMBRE_ALUMNO, MATRICULAS.NUM_GRA," _
            & "IF(MATRICULAS.COD_ENS = '111', 'PG', IF(MATRICULAS.COD_ENS = '222', 'PGMM', IF(MATRICULAS.COD_ENS = '996', 'PK', IF(MATRICULAS.COD_ENS = '010', 'K', IF(MATRICULAS.COD_ENS = '110', 'B', 'M'))))) AS nivel," _
            & "APO_X_ALU.RUT_APO, CONCAT(APODERADOS.APE1_APO,' ', APODERADOS.APE2_APO, ' ', APODERADOS.NOMS_APO) AS NOMBRE_APODERADO, APODERADOS.FON_C_APO, FNZA_DETALLE_MENSUALIDAD.folio_dtll AS folio_matricula,fec_mat_r,fec_mat" _
            & " From MATRICULAS, FNZA_DETALLE_MENSUALIDAD, APO_X_ALU, APODERADOS, POSTULANTES" _
            & " Where FNZA_DETALLE_MENSUALIDAD.run_alu = MATRICULAS.ci_alu" _
            & " AND FNZA_DETALLE_MENSUALIDAD.AGNO = MATRICULAS.ANNO_MAT AND MATRICULAS.ANNO_MAT = '" & cmb_agno & "'" _
            & " AND fec_mat_r IS NOT NULL AND MATRICULAS.CONFIRMADO = 'SI' AND MATRICULAS.ACTIVO_ALU = 'SI'" _
            & " AND APO_X_ALU.CI_ALU = MATRICULAS.CI_ALU AND APO_X_ALU.RUT_APO = APODERADOS.RUT_APO AND fec_mat_r IS NOT NULL AND APO_X_ALU.AGNO = '" & cmb_agno & "'" _
            & " AND " & ttabla & " = '" & mmes & "' AND POSTULANTES.CI_ALU = MATRICULAS.CI_ALU" _
            & ") AS matriculas_validas LEFT JOIN(" _
            & "SELECT fdm.folio_dtll AS folio_boleta, fb.folio_blta_fisico AS folio_boleta_fisico, fb.fecha_blta, fmm.monto_pago AS monto_pago" _
            & " FROM fnza_boletas fb, fnza_dtll_mvto fdm, fnza_movimientos_mensualidad fmm" _
            & " Where fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fb.estado_blta <> 'nula'" _
            & " AND fmm.cod_concepto = 'ct00' GROUP BY fb.folio_blta_fisico) AS boletas" _
            & " ON matriculas_validas.folio_matricula = boletas.folio_boleta) AS a" _
            & " WHERE a.ci_alu IN (SELECT CI_ALU FROM MATRICULAS WHERE ANNO_MAT = '" & cmb_agno - 1 & "')"
        
   a = 1
        Else
            QRY = "CREATE TABLE tmp_reporte_matriculas_old2 TYPE=MYISAM SELECT * FROM (SELECT * FROM (" _
            & "SELECT DISTINCT MATRICULAS.CI_ALU,fec_mat_r," _
            & "CONCAT(POSTULANTES.APE_PAT_ALU, ' ', POSTULANTES.APE_MAT_ALU, ' ', POSTULANTES.NOM_ALU) AS NOMBRE_ALUMNO, MATRICULAS.NUM_GRA," _
            & "IF(MATRICULAS.COD_ENS = '111', 'PG', IF(MATRICULAS.COD_ENS = '222', 'PGMM', IF(MATRICULAS.COD_ENS = '996', 'PK', IF(MATRICULAS.COD_ENS = '010', 'K', IF(MATRICULAS.COD_ENS = '110', 'B', 'M'))))) AS nivel," _
            & "APO_X_ALU.RUT_APO, CONCAT(APODERADOS.APE1_APO,' ', APODERADOS.APE2_APO, ' ', APODERADOS.NOMS_APO) AS NOMBRE_APODERADO, APODERADOS.FON_C_APO,APODERADOS.CEL_APO ,IF (MATRICULAS.ind_beca <> '1', 'Sin Beca', 'Con Beca') AS Becado, FNZA_DETALLE_MENSUALIDAD.folio_dtll AS folio_matricula, email AS Correo_Apoderado, email_alu AS Correo_Alumno" _
            & " From MATRICULAS, FNZA_DETALLE_MENSUALIDAD, APO_X_ALU, APODERADOS, POSTULANTES, ALU_X_CUR" _
            & " Where FNZA_DETALLE_MENSUALIDAD.run_alu = MATRICULAS.ci_alu" _
            & " AND FNZA_DETALLE_MENSUALIDAD.AGNO = MATRICULAS.ANNO_MAT AND MATRICULAS.ANNO_MAT = '" & cmb_agno - 1 & "'" _
            & " AND MATRICULAS.CONFIRMADO = 'SI' and fec_mat_r IS NOT NULL AND MATRICULAS.ACTIVO_ALU = 'SI' AND NOT (MATRICULAS.cod_ens = '310' AND MATRICULAS.num_gra = 4)" _
            & " AND APO_X_ALU.CI_ALU = MATRICULAS.CI_ALU AND APO_X_ALU.RUT_APO = APODERADOS.RUT_APO AND APO_X_ALU.AGNO = '" & cmb_agno - 1 & "'" _
            & " AND " & ttabla & " = '" & mmes & "' And POSTULANTES.ci_alu = MATRICULAS.ci_alu " _
            & " And ALU_X_CUR.ci_alu = MATRICULAS.ci_alu AND ALU_X_CUR.AGNO = MATRICULAS.ANNO_MAT AND ALU_X_CUR.COD_ENS = MATRICULAS.COD_ENS" _
            & " AND ALU_X_CUR.NUM_GRA = MATRICULAS.NUM_GRA AND ALU_X_CUR.ACTIVO_ALU = 'SI'" _
            & ") AS matriculas_validas LEFT JOIN(" _
            & "SELECT fb.run_alu, fdm.folio_dtll AS folio_boleta, fb.folio_blta_fisico AS folio_boleta_fisico, fb.fecha_blta, fmm.monto_pago AS monto_pago" _
            & " FROM fnza_boletas fb, fnza_dtll_mvto fdm, fnza_movimientos_mensualidad fmm" _
            & " Where fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fb.estado_blta <> 'nula' AND fmm.cod_concepto = 'ct00'" _
            & " AND fech_venc_cptos > '" & cmb_agno - 1 & "-01-01' AND fech_venc_cptos <= '" & cmb_agno - 1 & "-12-31' GROUP BY fb.folio_blta_fisico) AS boletas" _
            & " ON matriculas_validas.folio_matricula = boletas.folio_boleta AND matriculas_validas.ci_alu = boletas.run_alu) AS a" _
            & " WHERE a.ci_alu NOT IN (SELECT CI_ALU FROM MATRICULAS WHERE ANNO_MAT = '" & cmb_agno & "' AND activo_alu = 'SI' AND confirmado = 'SI' and fec_mat_r IS NOT NULL)"
            muestra_excel
          a = 2
          
        End If
        End If
        
   End If
    
    CMD.CommandText = QRY
    CMD.Execute
    
    If (a = 2) Then
        QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas_old_111;"
        CMD.CommandText = QRY
        CMD.Execute
    
        QRY = "CREATE TABLE tmp_reporte_matriculas_old_111 TYPE=MYISAM SELECT * FROM tmp_reporte_matriculas_old2 GROUP BY ci_alu;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas_old2;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "CREATE TABLE tmp_reporte_matriculas_old2 TYPE=MYISAM SELECT * FROM tmp_reporte_matriculas_old_111 GROUP BY ci_alu;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT a.*, b.deuda FROM (SELECT * FROM tmp_reporte_matriculas_old2 " _
        & " LEFT JOIN (SELECT ci_alu AS ci_alu2, SUM(nf)/COUNT(nf)  AS primer_semestre FROM promedios WHERE nf <> '0.0' AND agno = '" & (cmb_agno - 1) & "'" _
        & " GROUP BY ci_alu) AS promedios ON tmp_reporte_matriculas_old2.ci_alu = promedios.ci_alu2) AS a LEFT JOIN" _
        & " (SELECT *, (SUM(monto_tot_cuota) - SUM(monto_abono)) AS deuda FROM fnza_detalle_mensualidad WHERE agno > 2017 AND agno < " & cmb_agno & "" _
        & " GROUP BY run_alu) AS b ON a.ci_alu = b.run_alu"
        
'        ' Consulta Antigua 2015-08-14
'             QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT a.*, b.deuda FROM (SELECT * FROM tmp_reporte_matriculas_old2 " _
'        & " LEFT JOIN (SELECT ci_alu AS ci_alu2, SUM(nf)/COUNT(nf)  AS primer_semestre FROM promedios WHERE nf <> '0.0' AND agno = '" & (cmb_agno - 1) & "'" _
'        & " GROUP BY ci_alu) AS promedios ON tmp_reporte_matriculas_old2.ci_alu = promedios.ci_alu2) AS a LEFT JOIN" _
'        & " (SELECT *, (SUM(monto_tot_cuota) - SUM(monto_abono)) AS deuda FROM fnza_detalle_mensualidad WHERE agno > '2011' AND agno < '" & (cmb_agno - 1) & "'" _
'        & " AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(fech_venc,'%Y-%m-%d')) > 20 GROUP BY run_alu) AS b ON a.ci_alu = b.run_alu"
        
        CMD.CommandText = QRY
        CMD.Execute
    End If
    
    
    If Cmb_who.Text = "Nuevos" And Cmb_Matr.Text = "Postulados" Then
        QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas_old_111;"
        CMD.CommandText = QRY
        CMD.Execute
    
        QRY = "CREATE TABLE tmp_reporte_matriculas_old_111 TYPE=MYISAM SELECT * FROM tmp_reporte_matriculas_old GROUP BY nombre;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas_old;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT * FROM tmp_reporte_matriculas_old_111 GROUP BY nombre;"
        CMD.CommandText = QRY
        CMD.Execute
    
        QRY = "ALTER TABLE tmp_reporte_matriculas_old MODIFY oficina VARCHAR(10);"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "INSERT INTO tmp_reporte_matriculas_old(rut_apo,telefono,fec_ins,vigente,run,nombre,curso_pos,inscrito,prueba_asignada,nota,matriculado,oficina)" _
            & " SELECT apoderados1.rut_apo,fon_c_apo,fec_pos AS fec_ins,'SI' AS vigente,postulaciones1.ci_alu AS run," _
            & " CONCAT(postulantes1.`APE_PAT_ALU`,' ', postulantes1.`APE_MAT_ALU`,' ', postulantes1.`NOM_ALU`) AS nombre," _
            & " CONCAT(CAST(IF(matriculas1.cod_ens = '996' OR matriculas1.cod_ens = '010' OR matriculas1.cod_ens = '111','',concat(matriculas1.num_gra,' ')) AS CHAR),IF(matriculas1.cod_ens = '111','PLAYGROUP', IF(matriculas1.cod_ens = '996','PREKINDER',IF(matriculas1.cod_ens = '010','KINDER'," _
            & " IF(matriculas1.cod_ens = '110','BASICO','MEDIO'))))) AS curso_pos,'SI' AS inscrito," _
            & " IF(postulaciones1.`VII_HOR_EVAL` IS NULL,'NO','SI') AS prueba_asignada,VIII_res_eval,IF(matriculas1.cod_ens IS NULL,'NO','SI') AS matriculado,'OFICINA' AS oficina" _
            & " From `matriculas` matriculas1,`apoderados` apoderados1 INNER JOIN `apo_x_alu` apo_x_alu1 ON apoderados1.`RUT_APO` = apo_x_alu1.`RUT_APO`" _
            & " INNER JOIN `postulantes` postulantes1 ON apo_x_alu1.`CI_ALU` = postulantes1.`CI_ALU` INNER JOIN `postulaciones` postulaciones1 ON" _
            & " postulantes1.`CI_ALU` = postulaciones1.`CI_ALU` INNER JOIN `jornadas` jornadas1 ON postulaciones1.`COD_JOR` = jornadas1.`COD_JOR`" _
            & " Where matriculas1.`CI_ALU` = postulantes1.`CI_ALU`  AND matriculas1.`CI_ALU` = apo_x_alu1.`CI_ALU`  AND matriculas1.`ANNO_MAT` = apo_x_alu1.`AGNO`  AND" _
            & " matriculas1.`COD_ENS` = postulantes1.`COD_ENS` AND matriculas1.`ACTIVO_ALU` = 'SI' AND anno_mat = " & cmb_agno.Text & " AND" _
            & " postulaciones1.ci_alu NOT IN (SELECT run FROM tmp_reporte_matriculas_old) AND postulaciones1.`ANNO_POS_ALU` = " & cmb_agno.Text & " Order By" _
            & " postulantes1.`NUM_GRA` ASC,postulantes1.`COD_JOR` ASC,postulantes1.`APE_PAT_ALU` ASC,postulantes1.`APE_MAT_ALU` ASC,postulantes1.`NOM_ALU` ASC"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "INSERT INTO tmp_reporte_matriculas_old(rut_apo,telefono,fec_ins,vigente,run,nombre,curso_pos,inscrito,prueba_asignada,nota,matriculado,oficina) " _
            & " SELECT apoderados1.rut_apo,fon_c_apo,fec_pos AS fec_ins,'SI' AS vigente,postulaciones1.ci_alu AS run,CONCAT(postulantes1.`APE_PAT_ALU`,' ', postulantes1.`APE_MAT_ALU`,' ', postulantes1.`NOM_ALU`) AS nombre," _
            & " CONCAT(CAST(IF(cod_ens = '996' OR cod_ens = '010' OR cod_ens = '111','',concat(num_gra,' ')) AS CHAR),IF(cod_ens = '111','PLAYGROUP', IF(cod_ens = '996','PREKINDER',IF(cod_ens = '010','KINDER',IF(cod_ens = '110','BASICO','MEDIO'))))) AS curso_pos,'SI' AS inscrito," _
            & " IF(postulaciones1.`VII_HOR_EVAL` IS NULL,'NO','SI') AS prueba_asignada,VIII_res_eval AS nota,'NO' AS matriculado,'OFICINA' AS oficina" _
            & " From ((`apoderados` apoderados1 INNER JOIN `apo_x_ins` apo_x_ins1 ON apoderados1.`RUT_APO` = apo_x_ins1.`RUT_APO`) INNER JOIN `postulantes` postulantes1 ON" _
            & " apo_x_ins1.`CI_ALU` = postulantes1.`CI_ALU`) INNER JOIN `postulaciones` postulaciones1 ON postulantes1.`CI_ALU` = postulaciones1.`CI_ALU`" _
            & " INNER JOIN `jornadas` jornadas1 ON postulaciones1.`COD_JOR` = jornadas1.`COD_JOR`" _
            & " Where apoderados1.`RUT_APO` = apo_x_ins1.`RUT_APO` AND apo_x_ins1.`CI_ALU` = postulantes1.`CI_ALU` AND postulantes1.`CI_ALU` = postulaciones1.`CI_ALU` AND" _
            & " postulaciones1.`COD_JOR` = jornadas1.`COD_JOR` AND postulaciones1.`ANNO_POS_ALU` = " & cmb_agno.Text & " AND" _
            & " postulaciones1.ci_alu NOT IN (SELECT run FROM tmp_reporte_matriculas_old)" _
            & " Order By postulantes1.`NUM_GRA` ASC,postulantes1.`COD_JOR` ASC,postulantes1.`APE_PAT_ALU` ASC,postulantes1.`APE_MAT_ALU` ASC,postulantes1.`NOM_ALU` ASC"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "DROP TABLE IF EXISTS temp_nuevos;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "CREATE TABLE temp_nuevos SELECT @ROW_NUMBER:=@ROW_NUMBER+1 AS ROW_NUMBER , tmp_reporte_matriculas_old.* FROM ( SELECT * FROM (SELECT f.*,folio_boleta_fisico,fecha_blta,monto_pago FROM (SELECT m.cod_ens,m.num_gra,fdm.folio_dtll,p.ci_alu,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,fec_mat,CONCAT(CAST(IF(m.cod_ens = '310' OR m.cod_ens = '110',CONCAT(m.num_gra,' '),'') AS CHAR),IF(m.cod_ens = '111','PLAYGROUP', IF(m.cod_ens = '996','PREKINDER',IF(m.cod_ens = '010','KINDER',IF(m.cod_ens = '110','BASICO','MEDIO'))))) AS curso,a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado FROM matriculas m,postulantes p,apo_x_alu AS axa,apoderados AS a,fnza_detalle_mensualidad fdm WHERE anno_mat = " & cmb_agno.Text & " AND activo_alu = 'SI' AND m.ci_alu = p.ci_alu AND m.ci_alu = axa.ci_alu AND m.anno_mat = axa.agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = fdm.run_alu AND m.anno_mat = fdm.agno" _
            & " AND concepto = 'ct00' AND p.ci_alu NOT IN (SELECT DISTINCT ci_alu FROM alu_x_cur WHERE agno = " & (cmb_agno.Text - 1) & " AND activo_alu = 'SI')) AS f LEFT JOIN (SELECT fdm.folio_dtll, fb.folio_blta_fisico AS folio_boleta_fisico, fb.fecha_blta, fmm.monto_pago AS monto_pago FROM fnza_boletas fb, fnza_dtll_mvto fdm, fnza_movimientos_mensualidad fmm WHERE fb.folio_blta = fdm.folio_blta AND fdm.folio_mvto = fmm.folio_mvto AND fb.estado_blta <> 'nula' AND fmm.cod_concepto = 'ct00' GROUP BY fb.folio_blta_fisico) AS b ON f.folio_dtll = b.folio_dtll ORDER BY f.cod_ens,f.num_gra,fec_mat) AS d GROUP BY ci_alu ORDER BY d.cod_ens,d.num_gra,d.fec_mat) AS tmp_reporte_matriculas_old, (SELECT @ROW_NUMBER:=0) r;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "UPDATE tmp_reporte_matriculas_old a,temp_nuevos n SET curso_pos = curso" _
            & " Where a.Run = n.ci_alu And curso Is Not Null"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "UPDATE tmp_reporte_matriculas_old m,postulante_web_seguimiento s SET vigente = 'NO' WHERE m.run = s.rut_pos AND s.renuncia = 1 and MID(fecha,7) = " & (cmb_agno.Text - 1)
        CMD.CommandText = QRY
        CMD.Execute
    
        QRY = "UPDATE tmp_reporte_matriculas_old SET fec_ins = NULL WHERE fec_pos IS NOT NULL"
        CMD.CommandText = QRY
        CMD.Execute
    
        CMD.CommandText = "CREATE TABLE tmp_reporte_matriculas TYPE = MYISAM SELECT @ROW_NUMBER:=@ROW_NUMBER+1 AS ROW_NUMBER , tmp_reporte_matriculas_old.* FROM" _
        & " (SELECT *,IF(curso_pos = 'PLAYGROUP',1,IF(curso_pos = 'PREKINDER',2,IF(curso_pos = 'KINDER',3,IF(curso_pos = '1 BASICO',4,IF(curso_pos = '2 BASICO',5,IF(curso_pos = '3 BASICO',6,IF(curso_pos = '4 BASICO',7,IF(curso_pos = '5 BASICO',8,IF(curso_pos = '6 BASICO',9,IF(curso_pos = '7 BASICO',10,IF(curso_pos = '8 BASICO',11,IF(curso_pos = '1 MEDIO',12,IF(curso_pos = '2 MEDIO',13,IF(curso_pos = '3 MEDIO',14,IF(curso_pos = '4 MEDIO',15,16))))))))))))))) AS ordenar, IF (MID(curso_pos, 1, 1) <> 'K' AND MID(curso_pos, 1, 1) <> 'P', CONCAT(MID(curso_pos, 3, 1), MID(curso_pos, 1, 1)), CONCAT(1,MID(curso_pos, 1, 1))) AS nivel FROM tmp_reporte_matriculas_old ORDER BY fec_pos,ORDENAR) as tmp_reporte_matriculas_old, (SELECT @ROW_NUMBER:=0) r"
    Else
        If Cmb_Matr.Text <> "No Matriculados" Then
            Sql = "DELETE FROM tmp_reporte_matriculas_old WHERE ci_alu NOT IN (SELECT DISTINCT ci_alu FROM alu_x_cur WHERE activo_alu = 'SI' and agno = " & (cmb_agno.Text - 1) & ")"
            conn.Execute Sql
        End If
    
        QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas_old_111;"
        CMD.CommandText = QRY
        CMD.Execute
    
        QRY = "CREATE TABLE tmp_reporte_matriculas_old_111 TYPE=MYISAM SELECT * FROM tmp_reporte_matriculas_old GROUP BY ci_alu;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "DROP TABLE IF EXISTS tmp_reporte_matriculas_old;"
        CMD.CommandText = QRY
        CMD.Execute
        
        QRY = "CREATE TABLE tmp_reporte_matriculas_old TYPE=MYISAM SELECT * FROM tmp_reporte_matriculas_old_111 GROUP BY ci_alu;"
        CMD.CommandText = QRY
        CMD.Execute
           
        If Cmb_Matr.Text = "Todos" Then
            CMD.CommandText = "CREATE TABLE tmp_reporte_matriculas TYPE = MYISAM SELECT @ROW_NUMBER:=@ROW_NUMBER+1 AS ROW_NUMBER , tmp_reporte_matriculas_old.* FROM" _
            & "(SELECT f.*, monto_cheques,cantidad_cheques FROM tmp_reporte_matriculas_old f LEFT JOIN (SELECT ci_apo,SUM(monto_cheque) AS monto_cheques,COUNT(ci_apo) cantidad_cheques " _
            & " FROM fnza_lista_negra_apoderados WHERE bloqueado = 1 GROUP BY ci_apo) c ON f.rut_apo = c.ci_apo ORDER BY nivel, num_gra) as tmp_reporte_matriculas_old, (SELECT @ROW_NUMBER:=0) r ORDER BY nivel,num_gra,letra,nombre_alumno"
        Else
            CMD.CommandText = "CREATE TABLE tmp_reporte_matriculas TYPE = MYISAM SELECT @ROW_NUMBER:=@ROW_NUMBER+1 AS ROW_NUMBER , tmp_reporte_matriculas_old.* FROM" _
            & "(SELECT f.*, monto_cheques,cantidad_cheques FROM tmp_reporte_matriculas_old f LEFT JOIN (SELECT ci_apo,SUM(monto_cheque) AS monto_cheques,COUNT(ci_apo) cantidad_cheques " _
            & " FROM fnza_lista_negra_apoderados WHERE bloqueado = 1 GROUP BY ci_apo) c ON f.rut_apo = c.ci_apo ORDER BY nivel, num_gra) as tmp_reporte_matriculas_old, (SELECT @ROW_NUMBER:=0) r ORDER BY fec_mat_r"
        End If
    End If
    
    CMD.Execute
    
    If Cmb_Matr.Text = "Todos" Then
       '  tmp_reporte_matriculas
       var_sql = "UPDATE tmp_reporte_matriculas SET matriculado = 'SI' WHERE ci_alu IN (SELECT ci_alu FROM matriculas WHERE anno_mat = 2024 AND confirmado = 'SI' AND fec_mat_r IS NOT NULL)"
       conn.Execute var_sql
       
    End If
    Exit Sub
    
controlerr:
    MsgBox Err.Description & vbCrLf & vbCrLf & Err.Source, vbExclamation, "Error " & Err.Number
    Err.Raise 0, "CreaTablaInformeMatrProxAgno", ""
    Resume

End Sub

Private Sub oculta_excel()
     lbl_monto.Visible = False
    lbl_mayor.Visible = False
    txt_monto.Visible = False
    cmd_exportar_excel.Visible = False
    pgbar.Visible = False

  
End Sub

Private Sub muestra_excel()
     lbl_monto.Visible = True
    lbl_mayor.Visible = True
    txt_monto.Visible = True
    cmd_exportar_excel.Visible = True
    pgbar.Visible = True
End Sub
