VERSION 5.00
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_informes_asistencia 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Informes Asistencia"
   ClientHeight    =   3660
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   2835
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   3660
   ScaleWidth      =   2835
   Begin ComctlLib.ProgressBar pgbar2 
      Height          =   255
      Left            =   120
      TabIndex        =   9
      Top             =   3360
      Width           =   2535
      _ExtentX        =   4471
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
   Begin VB.CommandButton Command7 
      Caption         =   "Proceso3"
      Height          =   495
      Left            =   3120
      TabIndex        =   8
      Top             =   1920
      Width           =   1215
   End
   Begin VB.Frame Frame1 
      Caption         =   "Informes por aņo"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   2775
      Left            =   240
      TabIndex        =   3
      Top             =   120
      Width           =   2415
      Begin VB.CommandButton Command6 
         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            =   240
         TabIndex        =   7
         Top             =   2160
         Width           =   1815
      End
      Begin VB.CommandButton Command5 
         Caption         =   "Informe 2024"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   240
         TabIndex        =   6
         Top             =   1560
         Width           =   1815
      End
      Begin VB.CommandButton Command4 
         Caption         =   "Informe 2023"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   240
         TabIndex        =   5
         Top             =   960
         Width           =   1815
      End
      Begin VB.CommandButton Command3 
         Caption         =   "Informe 2022"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   240
         TabIndex        =   4
         Top             =   360
         Width           =   1815
      End
   End
   Begin VB.CommandButton Command2 
      Caption         =   "Proceso2"
      Height          =   495
      Left            =   4560
      TabIndex        =   2
      Top             =   1200
      Width           =   1215
   End
   Begin MSComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   1
      Top             =   3000
      Width           =   2535
      _ExtentX        =   4471
      _ExtentY        =   450
      _Version        =   393216
      Appearance      =   1
   End
   Begin VB.CommandButton Command1 
      Caption         =   "Proceso"
      Height          =   495
      Left            =   3120
      TabIndex        =   0
      Top             =   1200
      Width           =   1215
   End
End
Attribute VB_Name = "Form_informes_asistencia"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_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 >= '2023-02-01' AND fecha <= '2023-12-31'"
    conn.Execute var_sql
    
    var_sql = "SELECT * FROM asistencia WHERE agno = 2023"
    objRS.Open var_sql, conn
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount
    pgbar.Value = pgbar.Min
    j = 0
    While Not objRS.EOF
        j = j + 1
        pgbar.Value = j
        For I = 1 To 31
            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) values('" _
                        & objRS("agno") & "','" & objRS("ci_alu") & "','" & Fecha & "','" & objRS("cod_ens") & "','" _
                        & objRS("num_gra") & "','" & objRS("let_cur") & "');"
                conn.Execute var_sql
            End If
        Next I
        objRS.MoveNext
    Wend
    objRS.Close
    MsgBox "Proceso terminado"
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 >= '2024-03-01' AND fecha <= '2024-10-31'"
    conn.Execute var_sql
    
    var_sql = "SELECT * FROM asistencia WHERE agno = 2024 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') 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
            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) values('" _
                        & objRS("agno") & "','" & objRS("ci_alu") & "','" & Fecha & "','" & objRS("cod_ens") & "','" _
                        & objRS("num_gra") & "','" & objRS("let_cur") & "');"
                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 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 = "2024" & "-" & IIf(Len(mess) = 1, "0", "") & mess & "-" & IIf(Len(dia) = 1, "0", "") & dia
                fecha_fin = "2024" & "-" & 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('2024','" & 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 Command3_Click()
    Dim var_sql As String
    
    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 = 2022 AND cod_asi <> 13 AND cod_asi <> 22 AND nf <> 0.0 GROUP BY ci_alu;"
    conn.Execute var_sql
    
    var_sql = "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 = 2022 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_r) - SUM(asi_t)) AS dias_faltados ,((SUM(asi_t) * 100) / SUM(asi_r)) porcentaje FROM asistencia a WHERE agno = 2022 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 = 2022 ORDER BY nivel,curso,alumno,fecha"
    Call Exportar_Excel(var_sql)
End Sub

Private Sub Command4_Click()
    Dim var_sql As String
    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 = 2023 AND cod_asi <> 13 AND cod_asi <> 22 AND nf <> 0.0 GROUP BY ci_alu;"
    conn.Execute var_sql
    
    var_sql = "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 = 2023 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_r) - SUM(asi_t)) AS dias_faltados ,((SUM(asi_t) * 100) / SUM(asi_r)) porcentaje FROM asistencia a WHERE agno = 2023 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 = 2023 ORDER BY nivel,curso,alumno,fecha"
    Call Exportar_Excel(var_sql)
End Sub

Private Sub Command5_Click()
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    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 = 2024 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)
    
    mes_asis = IIf(mes = 2, "FEB", IIf(mes = 3, "MAR", IIf(mes = 4, "ABR", IIf(mes = 5, "MAY", IIf(mes = 6, "JUN", IIf(mes = 7, "JUL", IIf(mes = 8, "AGO", IIf(mes = 9, "SEP", IIf(mes = 10, "OCT", IIf(mes = 11, "NOV", IIf(mes = 12, "DIC", "ENE")))))))))))
    
    var_sql = "SELECT asis,asi_r FROM asistencia WHERE activo_alu = 'SI' AND agno = " & agno & " AND mes_asis = '" & mes_asis & "' AND ci_alu IN " _
            & "(SELECT ci_alu FROM alu_x_cur WHERE agno = " & agno & " AND activo_alu = 'SI'" _
            & " AND fec_ing_curso IN (SELECT valor FROM cfg WHERE agno = " & agno & " 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
        If Mid(Asis, I, 1) = "·" Or Mid(Asis, I, 1) = "X" Then transcurridos = transcurridos + 1
    Next I
    
    dife = total - transcurridos
            
    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 = 2024 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 = 2024 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 = 2024 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 Command6_Click()
    Unload Me
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 = 1
        
    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, 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, 1).Formula = "Nivel"
    ApExcel.Cells(1, 2).Formula = "Curso"
    ApExcel.Cells(1, 3).Formula = "Rut Alumno"
    ApExcel.Cells(1, 4).Formula = "Alumno"
    ApExcel.Cells(1, 5).Formula = "Dias Trabajados"
    ApExcel.Cells(1, 6).Formula = "Dias Asistidos"
    ApExcel.Cells(1, 7).Formula = "Dias Faltados"
    ApExcel.Cells(1, 8).Formula = "Porcentaje"
    ApExcel.Cells(1, 9).Formula = "Fecha inasistencia"
    ApExcel.Cells(1, 10).Formula = "Dia de la semana"
    ApExcel.Cells(1, 11).Formula = "Promedio"
    ApExcel.Cells(1, 12).Formula = "Licencia medica"
        
    pgbar.Min = 0
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
  
    While Not objRS.EOF
        I = I + 1
        ApExcel.Cells(I, 1).Formula = objRS("nivel")
        ApExcel.Cells(I, 2).Formula = objRS("curso")
        ApExcel.Cells(I, 3).Formula = objRS("ci_alu")
        ApExcel.Cells(I, 4).Formula = objRS("alumno")
        ApExcel.Cells(I, 5).Formula = objRS("dias_trabajados")
        ApExcel.Cells(I, 6).Formula = objRS("dias_asistidos")
        ApExcel.Cells(I, 7).Formula = objRS("dias_faltados")
        ApExcel.Cells(I, 8).Formula = objRS("porcentaje")
        ApExcel.Cells(I, 9).Formula = Format(objRS("fecha"), "yyyy/mm/dd")
        ApExcel.Cells(I, 10).Formula = objRS("dia")
        ApExcel.Cells(I, 11).Formula = objRS("promedio")
        ApExcel.Cells(I, 12).Formula = objRS("licencia_medica")
        pgbar.Value = I - 1
        objRS.MoveNext
    Wend
    I = I + 1
    ApExcel.Visible = True
    ApExcel.Range("a1:k1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
    objRS.Close
        
End Sub

Private Sub Command7_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 >= '2022-02-01' AND fecha <= '2022-12-31'"
    conn.Execute var_sql
    
    var_sql = "SELECT * FROM asistencia WHERE agno = 2022"
    objRS.Open var_sql, conn
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount
    pgbar.Value = pgbar.Min
    j = 0
    While Not objRS.EOF
        j = j + 1
        pgbar.Value = j
        For I = 1 To 31
            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) values('" _
                        & objRS("agno") & "','" & objRS("ci_alu") & "','" & Fecha & "','" & objRS("cod_ens") & "','" _
                        & objRS("num_gra") & "','" & objRS("let_cur") & "');"
                conn.Execute var_sql
            End If
        Next I
        objRS.MoveNext
    Wend
    objRS.Close
    MsgBox "Proceso terminado"
End Sub

