VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.2#0"; "MSCOMCTL.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_informe_asis_entre 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Informe Asistencia"
   ClientHeight    =   2820
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   3870
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   2820
   ScaleWidth      =   3870
   Begin VB.CommandButton Command2 
      Caption         =   "Command1"
      Height          =   255
      Left            =   2880
      TabIndex        =   6
      Top             =   840
      Visible         =   0   'False
      Width           =   1095
   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          =   375
      Left            =   2160
      TabIndex        =   5
      Top             =   1440
      Width           =   1335
   End
   Begin VB.CommandButton cmd_generar 
      Caption         =   "Generar"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   360
      TabIndex        =   4
      Top             =   1440
      Width           =   1455
   End
   Begin MSComCtl2.DTPicker DTPicker2 
      Height          =   255
      Left            =   1440
      TabIndex        =   3
      Top             =   840
      Width           =   1335
      _ExtentX        =   2355
      _ExtentY        =   450
      _Version        =   393216
      Format          =   52690945
      CurrentDate     =   46147
   End
   Begin MSComCtl2.DTPicker DTPicker1 
      Height          =   255
      Left            =   1440
      TabIndex        =   2
      Top             =   240
      Width           =   1335
      _ExtentX        =   2355
      _ExtentY        =   450
      _Version        =   393216
      Format          =   52690945
      CurrentDate     =   46147
   End
   Begin ComctlLib.ProgressBar pgbar2 
      Height          =   255
      Left            =   120
      TabIndex        =   7
      Top             =   2040
      Width           =   3615
      _ExtentX        =   6376
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
   Begin MSComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   8
      Top             =   2400
      Width           =   3615
      _ExtentX        =   6376
      _ExtentY        =   450
      _Version        =   393216
      Appearance      =   1
   End
   Begin VB.Label Label2 
      Caption         =   "Hasta"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   240
      TabIndex        =   1
      Top             =   840
      Width           =   1095
   End
   Begin VB.Label Label1 
      Caption         =   "Desde"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   240
      TabIndex        =   0
      Top             =   240
      Width           =   975
   End
End
Attribute VB_Name = "Form_informe_asis_entre"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub cmd_generar_Click()
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    If DTPicker1.Value > DTPicker2.Value Then
        MsgBox "Fecha desde debe ser menor a la fecha hasta", vbInformation
        Exit Sub
    End If
    
    Call Command2_Click
    
'    var_sql = "DROP TABLE IF EXISTS temp_promedios;"
'    conn.Execute var_sql
'    var_sql = "CREATE TABLE temp_promedios TYPE = MYISAM" _
'            & " SELECT ci_alu,ROUND(SUM(nf)/COUNT(nf),1) AS promedio FROM promedios WHERE agno = 2026 AND cod_asi <> 13 AND cod_asi <> 22 AND nf <> 0.0 GROUP BY ci_alu;"
'    conn.Execute var_sql
    
'    mes = DevuelveCampo("establecimientos", "", "month(now()) as mes", "1 = 1", conn)
'    agno = DevuelveCampo("establecimientos", "", "year(now()) as agno", "1 = 1", conn)
'    dia = DevuelveCampo("establecimientos", "", "day(now()) as dia", "1 = 1", conn)
    
    dia_d = Day(DTPicker1.Value)
    mes_d = Month(DTPicker1.Value)
    agno_d = Year(DTPicker1.Value)
    
    dia_h = Day(DTPicker2.Value)
    mes_h = Month(DTPicker2.Value)
    agno_h = Year(DTPicker2.Value)
    
    
    '''   Meses
    var_sql = "SELECT UCASE(MID(mes,1,3)) as me FROM meses WHERE num_mes >= " & mes_d & " AND num_mes <= " & mes_h
    
    objRS.Open var_sql, conn
    Meses = "("
    If objRS.RecordCount > 0 Then
        While Not objRS.EOF
            Meses = Meses & "mes_asis = '" & objRS("me") & "' or "
            objRS.MoveNext
        Wend
    Else
        MsgBox "No hay asistencia definida en el mes en curso", vbInformation
        objRS.Close
        Exit Sub
    End If
    objRS.Close
    Meses = Mid(Meses, 1, (Len(Meses) - 3)) & ")"
    
    mes_asis_d = IIf(mes_d = 2, "FEB", IIf(mes_d = 3, "MAR", IIf(mes_d = 4, "ABR", IIf(mes_d = 5, "MAY", IIf(mes_d = 6, "JUN", IIf(mes_d = 7, "JUL", IIf(mes_d = 8, "AGO", IIf(mes_d = 9, "SEP", IIf(mes_d = 10, "OCT", IIf(mes_d = 11, "NOV", IIf(mes_d = 12, "DIC", "ENE")))))))))))
    mes_asis_h = IIf(mes_h = 2, "FEB", IIf(mes_h = 3, "MAR", IIf(mes_h = 4, "ABR", IIf(mes_h = 5, "MAY", IIf(mes_h = 6, "JUN", IIf(mes_h = 7, "JUL", IIf(mes_h = 8, "AGO", IIf(mes_h = 9, "SEP", IIf(mes_h = 10, "OCT", IIf(mes_h = 11, "NOV", IIf(mes_h = 12, "DIC", "ENE")))))))))))
    
    '''' Desde
    var_sql = "SELECT asis,asi_r FROM asistencia WHERE activo_alu = 'SI' AND agno = " & agno_d & " AND mes_asis = '" & mes_asis_d & "' AND ci_alu IN " _
            & "(SELECT ci_alu FROM alu_x_cur WHERE agno = " & agno_d & " AND activo_alu = 'SI'" _
            & " AND fec_ing_curso IN (SELECT valor FROM cfg WHERE agno = " & agno_d & " AND param = 'PerEscIni')) LIMIT 1"
            
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
        Asis = objRS("asis")
        total = objRS("asi_r")
    Else
        MsgBox "No hay asistencia definida en el mes en curso", vbInformation
        objRS.Close
        Exit Sub
    End If
            
    transcurridos = 0
    For I = dia_d To 31
        If Mid(Asis, I, 1) = "·" Or Mid(Asis, I, 1) = "X" Then transcurridos = transcurridos + 1
    Next I
    
    transcu = total - transcurridos
    objRS.Close
    
    
    '''' Hasta
    var_sql = "SELECT asis,asi_r FROM asistencia WHERE activo_alu = 'SI' AND agno = " & agno_h & " AND mes_asis = '" & mes_asis_h & "' AND ci_alu IN " _
            & "(SELECT ci_alu FROM alu_x_cur WHERE agno = " & agno_h & " AND activo_alu = 'SI'" _
            & " AND fec_ing_curso IN (SELECT valor FROM cfg WHERE agno = " & agno_h & " AND param = 'PerEscIni')) LIMIT 1"
            
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
        Asis = objRS("asis")
        total = objRS("asi_r")
    Else
        MsgBox "No hay asistencia definida en el mes en curso", vbInformation
        objRS.Close
        Exit Sub
    End If
            
    transcurridos = 0
    For I = 1 To dia_h
        If Mid(Asis, I, 1) = "·" Or Mid(Asis, I, 1) = "X" Then transcurridos = transcurridos + 1
    Next I
    
    dife = total - transcurridos
    objRS.Close
    
    var_sql = "SELECT nivel,curso,a.ci_alu,alumno,Escolares,Asistidos,Inasistidos,porcentaje,situacion,IF(c.ci_alu IS NULL,'Nuevo','Antiguo') AS tipo FROM" _
            & " (SELECT cu.ci_alu,IF(cu.cod_ens = '111','PlayGroup',IF(cu.cod_ens = '996','PreKinder',IF(cu.cod_ens = '010','Kinder',IF(cu.cod_ens = '110','Basico',IF(cu.cod_ens = '310','Media','SinCurso'))))) AS nivel," _
            & "CONCAT(CAST(IF(cu.cod_ens = '110' OR cu.cod_ens = '310',cu.num_gra,'') AS CHAR),CAST(IF(cu.cod_ens = '110' OR cu.cod_ens = '310',' ','') AS CHAR),cu.let_cur) AS curso,cu.num_gra,cu.let_cur,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno," _
            & "COUNT(estado) Escolares,SUM(IF(estado = 'A',1,0)) Asistidos,SUM(IF(estado = 'I',1,0)) Inasistidos," _
            & "(SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) AS porcentaje," _
            & "IF((SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) <= 50,'Crítico',IF((SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) > 50 AND (SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) <= 69,'Riesgo'," _
            & "IF((SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) >= 70 AND (SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) <= 85,'Medio'," _
            & "IF((SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) >= 86 AND (SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) <= 89,'Adecuado','Óptimo')))) AS Situacion,IF((SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) <= 50,'1',IF((SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) > 50 AND (SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) <= 69,'2'," _
            & "IF((SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) >= 70 AND (SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) <= 85,'3',IF((SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) >= 86" _
            & " AND (SUM(IF(estado = 'A',1,0)) * 100) / COUNT(estado) <= 89,'4','5')))) AS orden" _
            & " FROM alu_x_cur cu,postulantes p,asistencia_informes ai" _
            & " WHERE cu.agno = " & Format(DTPicker2.Value, "yyyy") & " AND activo_alu = 'SI' AND cu.ci_alu = p.ci_alu AND cu.ci_alu = ai.ci_alu AND cu.agno = ai.agno" _
            & " AND fecha >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' AND fecha <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "' GROUP BY ci_alu) a Left Join" _
            & "(SELECT ci_alu FROM alu_x_cur WHERE agno = " & (Format(DTPicker2.Value, "yyyy") - 1) & " GROUP BY ci_alu) c ON a.ci_alu = c.ci_alu ORDER BY orden"
    
    ' " & Format(DTPicker1.Value, "yyyy-mm-dd") & "
    ' " & Format(DTPicker2.Value, "yyyy-mm-dd") & "
            
'''''    var_sql = "SELECT a.*,IF(l.ci_alu IS NOT NULL,'licencia medica','') AS licencia_medica FROM (SELECT a.*,i.fecha,IF(DAYOFWEEK(fecha) = 1,'Domingo',IF(DAYOFWEEK(fecha) = 2,'Lunes',IF(DAYOFWEEK(fecha) = 3,'Martes',IF(DAYOFWEEK(fecha) = 4,'Miercoles',IF(DAYOFWEEK(fecha) = 5,'Jueves',IF(DAYOFWEEK(fecha) = 6,'Viernes','Sabado')))))) AS dia,promedio" _
'''''            & " From (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,dias_faltados,porcentaje,promedio" _
'''''            & " 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,promedio FROM alu_x_cur cu,postulantes p LEFT JOIN temp_promedios pr ON p.ci_alu = pr.ci_alu WHERE agno = 2026 AND activo_alu = 'SI' AND cu.ci_alu = p.ci_alu) AS c" _
'''''            & " LEFT JOIN (select ci_alu,SUM(IF(mes_asis = '" & mes_asis & "',(asi_r - " & dife & "),asi_r)) AS dias_trabajados,SUM(IF(mes_asis = '" & mes_asis & "',(asi_t - " & dife & "),asi_t)) AS dias_asistidos" _
'''''            & ",(SUM(IF(mes_asis = '" & mes_asis & "',(asi_r - " & dife & "),asi_r)) - SUM(IF(mes_asis = '" & mes_asis & "',(asi_t - " & dife & "),asi_t))) AS dias_faltados" _
'''''            & ",((SUM(IF(mes_asis = '" & mes_asis & "',(asi_t - " & dife & "),asi_t)) * 100) / SUM(IF(mes_asis = '" & mes_asis & "',(asi_r - " & dife & "),asi_r))) porcentaje FROM asistencia a WHERE agno = 2026 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) a,asistencia_informes i" _
'''''            & " Where a.ci_alu = I.ci_alu And I.agno = 2026 ORDER BY nivel,curso,alumno,fecha) as a LEFT JOIN temp_licencias l ON a.ci_alu = l.ci_alu AND a.fecha = l.fecha"
     Call Exportar_Excel(var_sql)
End Sub

Private Sub cmd_Salir_Click()
    Unload Me
End Sub

Private Sub Command2_Click()
Dim var_sql As String
    Dim dia As String
    Dim mes As String
    Dim Fecha As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    '' Borro para actualizar
    var_sql = "delete FROM asistencia_informes WHERE fecha >= '2026-03-01' AND fecha <= '2026-11-30'"
    conn.Execute var_sql
    
    var_sql = "SELECT * FROM asistencia WHERE agno = 2026 and (mes_asis = 'MAR' or mes_asis = 'ABR' or mes_asis = 'MAY' or mes_asis = 'JUN' or mes_asis = 'JUL' or mes_asis = 'AGO' or mes_asis = 'SEP' or mes_asis = 'OCT' or mes_asis = 'NOV') and activo_alu = 'SI'"
    objRS.Open var_sql, conn
    pgbar2.Min = 1
    pgbar2.Max = objRS.RecordCount
    pgbar2.Value = pgbar2.Min
    j = 0
    While Not objRS.EOF
        j = j + 1
        pgbar2.Value = j
        For I = 1 To 31
            
            '' Dias faltado
            If Mid(objRS("asis"), I, 1) = "X" Then
                dia = IIf(I < 10, "0", "") & I
                '' mes = IIf(objRS("mes_asis") = "FEB", "02", IIf(objRS("mes_asis") = "MAR","03"),iif(objRS("mes_asis") = "ABR" = "04",iif(objRS("mes_asis") = "MAY"))
                If objRS("mes_asis") = "FEB" Then mes = "02"
                If objRS("mes_asis") = "MAR" Then mes = "03"
                If objRS("mes_asis") = "ABR" Then mes = "04"
                If objRS("mes_asis") = "MAY" Then mes = "05"
                If objRS("mes_asis") = "JUN" Then mes = "06"
                If objRS("mes_asis") = "JUL" Then mes = "07"
                If objRS("mes_asis") = "AGO" Then mes = "08"
                If objRS("mes_asis") = "SEP" Then mes = "09"
                If objRS("mes_asis") = "OCT" Then mes = "10"
                If objRS("mes_asis") = "NOV" Then mes = "11"
                If objRS("mes_asis") = "DIC" Then mes = "12"
                Fecha = objRS("agno") & "-" & mes & "-" & dia
                
                var_sql = "insert into asistencia_informes(agno,ci_alu,fecha,cod_ens,num_gra,let_cur,estado) values('" _
                        & objRS("agno") & "','" & objRS("ci_alu") & "','" & Fecha & "','" & objRS("cod_ens") & "','" _
                        & objRS("num_gra") & "','" & objRS("let_cur") & "','I');"
                conn.Execute var_sql
            End If
            
            '''  Dias Asistidos
            If Mid(objRS("asis"), I, 1) = "·" Then
                dia = IIf(I < 10, "0", "") & I
                '' mes = IIf(objRS("mes_asis") = "FEB", "02", IIf(objRS("mes_asis") = "MAR","03"),iif(objRS("mes_asis") = "ABR" = "04",iif(objRS("mes_asis") = "MAY"))
                If objRS("mes_asis") = "FEB" Then mes = "02"
                If objRS("mes_asis") = "MAR" Then mes = "03"
                If objRS("mes_asis") = "ABR" Then mes = "04"
                If objRS("mes_asis") = "MAY" Then mes = "05"
                If objRS("mes_asis") = "JUN" Then mes = "06"
                If objRS("mes_asis") = "JUL" Then mes = "07"
                If objRS("mes_asis") = "AGO" Then mes = "08"
                If objRS("mes_asis") = "SEP" Then mes = "09"
                If objRS("mes_asis") = "OCT" Then mes = "10"
                If objRS("mes_asis") = "NOV" Then mes = "11"
                If objRS("mes_asis") = "DIC" Then mes = "12"
                Fecha = objRS("agno") & "-" & mes & "-" & dia
                
                var_sql = "insert into asistencia_informes(agno,ci_alu,fecha,cod_ens,num_gra,let_cur,estado) values('" _
                        & objRS("agno") & "','" & objRS("ci_alu") & "','" & Fecha & "','" & objRS("cod_ens") & "','" _
                        & objRS("num_gra") & "','" & objRS("let_cur") & "','A');"
                conn.Execute var_sql
            End If
            
        Next I
        objRS.MoveNext
    Wend
    objRS.Close
    
    '' Borro para actualizar
    var_sql = "delete FROM temp_licencias"
    conn.Execute var_sql
    
    var_sql = "SELECT agno,desde,hasta,ci_alu,motivo,folio,MONTH(desde) AS mes_ini,DAY(desde) AS dia_ini,MONTH(hasta) AS mes_fin,DAY(hasta) AS dia_fin  FROM fnza_licencias where agno = " & Anno & " GROUP BY ci_alu,motivo"
    objRS.Open var_sql, conn
    
    While Not objRS.EOF
        sw = True
        salida = True
        For mess = objRS("mes_ini") To objRS("mes_fin")
            dia = 0
            While dia < 32 And salida = True
                If sw = True Then
                    dia = objRS("dia_ini")
                    sw = False
                Else
                    dia = dia + 1
                End If
                Fecha = "2026" & "-" & IIf(Len(mess) = 1, "0", "") & mess & "-" & IIf(Len(dia) = 1, "0", "") & dia
                fecha_fin = "2026" & "-" & IIf(Len(objRS("Mes_Fin")) = 1, "0", "") & objRS("mes_fin") & "-" & IIf(Len(objRS("dia_fin")) = 1, "0", "") & objRS("dia_fin")
                If Fecha <= fecha_fin Then
                     ' Format(objRS("fecha"), "yyyy/mm/dd")
                    If (mess = 4 And dia = 31) Or (mess = 6 And dia = 31) Or (mess = 9 And dia = 31) Or (mess = 11 And dia = 31) Then
                    Else
                        
                        If dia <> 32 Then
                            var_sql = "insert into `temp_licencias` (`agno`, `desde`, `hasta`, `ci_alu`, `motivo`, `folio`, `fecha`)" _
                                    & " values('2026','" & Format(objRS("desde"), "yyyy/mm/dd") & "','" & Format(objRS("hasta"), "yyyy/mm/dd") & "','" & objRS("ci_alu") & "','" & objRS("motivo") _
                                    & "','" & objRS("folio") & "','" & Fecha & "');"
                            conn.Execute var_sql
                        End If
                    End If
                End If
                If Fecha = fecha_fin Then
                    salida = False
                   '' objRS.Close
                   '' Exit Sub
                End If
            Wend
        Next mess
        objRS.MoveNext
    Wend
    objRS.Close

    '' MsgBox "Proceso terminado"
End Sub

Private Sub Exportar_Excel(Consulta As String)
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")
    ApExcel.Workbooks.Add
    
    var_sql = Consulta
    objRS.Open var_sql, conn
    I = 3
    
    ApExcel.Cells(1, 1).Formula = "Año " & Year(DTPicker1.Value)
    ApExcel.Cells(1, 1).Font.Size = 12
    ApExcel.Cells(1, 1).Font.Bold = True
    ApExcel.Range("A1:k1").Merge
    ApExcel.Range("A1:k1").HorizontalAlignment = xlCenter
    ApExcel.Range("A1:k1").Borders.color = RGB(0, 0, 0)
    'ApExcel.Range("A1:D1").Interior.color = RGB(200, 160, 27)
    
    ApExcel.Cells(2, 6).Formula = "Dias"
    ApExcel.Cells(2, 6).Font.Size = 12
    ApExcel.Cells(2, 6).Font.Bold = True
    ApExcel.Range("F2:H2").Merge
    ApExcel.Range("F2:H2").HorizontalAlignment = xlCenter
    ApExcel.Range("F2:H2").Borders.color = RGB(0, 0, 0)
        
    ApExcel.Cells(3, 1).Font.Size = 12
    ApExcel.Cells(3, 2).Font.Size = 12
    ApExcel.Cells(3, 3).Font.Size = 12
    ApExcel.Cells(3, 4).Font.Size = 12
    ApExcel.Cells(3, 5).Font.Size = 12
    ApExcel.Cells(3, 6).Font.Size = 12
    ApExcel.Cells(3, 7).Font.Size = 12
    ApExcel.Cells(3, 8).Font.Size = 12
    ApExcel.Cells(3, 9).Font.Size = 12
    ApExcel.Cells(3, 10).Font.Size = 12
    ApExcel.Cells(3, 11).Font.Size = 12
      
    ApExcel.Cells(3, 1).Font.Bold = True
    ApExcel.Cells(3, 2).Font.Bold = True
    ApExcel.Cells(3, 3).Font.Bold = True
    ApExcel.Cells(3, 4).Font.Bold = True
    ApExcel.Cells(3, 5).Font.Bold = True
    ApExcel.Cells(3, 6).Font.Bold = True
    ApExcel.Cells(3, 7).Font.Bold = True
    ApExcel.Cells(3, 8).Font.Bold = True
    ApExcel.Cells(3, 9).Font.Bold = True
    ApExcel.Cells(3, 10).Font.Bold = True
    ApExcel.Cells(3, 11).Font.Bold = True
     
    ApExcel.Cells(3, 1).Formula = "Nº"
    ApExcel.Cells(3, 2).Formula = "Nivel"
    ApExcel.Cells(3, 3).Formula = "Curso"
    ApExcel.Cells(3, 4).Formula = "C.I."
    ApExcel.Cells(3, 5).Formula = "Alumno"
    ApExcel.Cells(3, 6).Formula = "Escolares"
    ApExcel.Cells(3, 7).Formula = "Asistidos"
    ApExcel.Cells(3, 8).Formula = "Faltados"
    ApExcel.Cells(3, 9).Formula = "% Asistencia"
    ApExcel.Cells(3, 10).Formula = "Situación"
    ApExcel.Cells(3, 11).Formula = "Alumno"
    
    ApExcel.Columns("A").ColumnWidth = 5
    ApExcel.Columns("B").ColumnWidth = 10
    ApExcel.Columns("C").ColumnWidth = 10
    ApExcel.Columns("E").ColumnWidth = 40
    ApExcel.Columns("M").ColumnWidth = 20
    
        
    pgbar.Min = 0
    pgbar.Max = objRS.RecordCount + 10
    pgbar.Value = pgbar.Min
  
    critico = 0
    riesgo = 0
    Medio = 0
    adecuado = 0
    optimo = 0
    total = 0
    
    While Not objRS.EOF
        total = total + 1
        I = I + 1
        ApExcel.Cells(I, 1).Formula = I - 3
        ApExcel.Cells(I, 2).Formula = objRS("nivel")
        ApExcel.Cells(I, 3).Formula = objRS("curso")
        ApExcel.Cells(I, 4).Formula = objRS("ci_alu")
        ApExcel.Cells(I, 5).Formula = objRS("alumno")
        ApExcel.Cells(I, 6).Formula = objRS("escolares")
        ApExcel.Cells(I, 7).Formula = objRS("asistidos")
        ApExcel.Cells(I, 8).Formula = objRS("inasistidos")
        ApExcel.Cells(I, 9).Formula = objRS("porcentaje")
        ApExcel.Cells(I, 10).Formula = objRS("situacion")
        ApExcel.Cells(I, 11).Formula = objRS("tipo")
        
        If objRS("situacion") = "Crítico" Then
            critico = critico + 1
            ApExcel.Range("J" & I).Interior.color = RGB(255, 0, 0)
        End If
        If objRS("situacion") = "Riesgo" Then
            riesgo = riesgo + 1
            ApExcel.Range("J" & I).Interior.color = RGB(255, 165, 0)
        End If
        If objRS("situacion") = "Medio" Then
            Medio = Medio + 1
            ApExcel.Range("J" & I).Interior.color = RGB(255, 255, 0)
        End If
        If objRS("situacion") = "Adecuado" Then
            adecuado = adecuado + 1
            ApExcel.Range("J" & I).Interior.color = RGB(144, 213, 255)
        End If
        If objRS("situacion") = "Óptimo" Then
            optimo = optimo + 1
            ApExcel.Range("J" & I).Interior.color = RGB(144, 238, 144)
        End If
      
        pgbar.Value = I - 1
        objRS.MoveNext
    Wend
    
    ApExcel.Cells(4, 13).Font.Bold = True
    ApExcel.Cells(4, 13).Formula = "Categoria"
    ApExcel.Cells(5, 13).Formula = "<= 50%    --- Crítico"
    ApExcel.Cells(6, 13).Formula = "51% – 69% --- Riesgo"""
    ApExcel.Cells(7, 13).Formula = "70% – 85% --- Medio"
    ApExcel.Cells(8, 13).Formula = "86% – 89% --- Adecuado"""
    ApExcel.Cells(9, 13).Formula = ">= 90%    --- Óptimo"
    
    ApExcel.Cells(11, 13).Font.Bold = True
    ApExcel.Cells(11, 13).Formula = "Resumen"
    ApExcel.Cells(12, 13).Font.Bold = True
    ApExcel.Cells(12, 13).Formula = "Cantidad Alumnos"
    ApExcel.Cells(13, 13).Formula = critico
    ApExcel.Cells(14, 13).Formula = riesgo
    ApExcel.Cells(15, 13).Formula = Medio
    ApExcel.Cells(16, 13).Formula = adecuado
    ApExcel.Cells(17, 13).Formula = optimo
    ApExcel.Cells(18, 13).Formula = total
    ApExcel.Cells(12, 14).Formula = "%"
    ApExcel.Cells(13, 14).Formula = Round((critico * 100) / total, 2) & " %"
    ApExcel.Cells(14, 14).Formula = Round((riesgo * 100) / total, 2) & " %"
    ApExcel.Cells(15, 14).Formula = Round((Medio * 100) / total, 2) & " %"
    ApExcel.Cells(16, 14).Formula = Round((adecuado * 100) / total, 2) & " %"
    ApExcel.Cells(17, 14).Formula = Round((optimo * 100) / total, 2) & " %"
    ' ApExcel.Cells(18, 14).Formula = 100 & " %"
    ApExcel.Cells(12, 15).Formula = "Categoria"
    ApExcel.Cells(13, 15).Formula = "Crítico"
    ApExcel.Cells(14, 15).Formula = "Riesgo"
    ApExcel.Cells(15, 15).Formula = "Medio"
    ApExcel.Cells(16, 15).Formula = "Adecuado"
    ApExcel.Cells(17, 15).Formula = "Óptimo"
    ApExcel.Cells(18, 15).Formula = "Total Alumnos"
    ApExcel.Cells(18, 15).Font.Bold = True
        
    ApExcel.Range("m11:o11").Borders.color = RGB(0, 0, 0)
    ApExcel.Range("m12:o12").Borders.color = RGB(0, 0, 0)
    ApExcel.Range("m13:o13").Borders.color = RGB(0, 0, 0)
    ApExcel.Range("m14:o14").Borders.color = RGB(0, 0, 0)
    ApExcel.Range("m15:o15").Borders.color = RGB(0, 0, 0)
    ApExcel.Range("m16:o16").Borders.color = RGB(0, 0, 0)
    ApExcel.Range("m17:o17").Borders.color = RGB(0, 0, 0)
    ApExcel.Range("m18:o18").Borders.color = RGB(0, 0, 0)
        
    I = I + 1
    ApExcel.Visible = True
    ApExcel.Range("a3:k3").Borders.color = RGB(0, 0, 0)
    Set ApExcel = Nothing
    objRS.Close
        
End Sub

Private Sub Form_Load()
    DTPicker1.Value = DevuelveCampo("cfg", "", "valor", "agno = " & Year(Date) & " AND param = 'perescini'", conn)
    DTPicker2.Value = Date
       
End Sub
