VERSION 5.00
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_informe_asistencia 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Informe de asistencia"
   ClientHeight    =   1965
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   6165
   Icon            =   "form_informe_asistencia.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   1965
   ScaleWidth      =   6165
   Begin VB.Frame Frame1 
      Caption         =   "Informe de asistencia"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1455
      Left            =   240
      TabIndex        =   0
      Top             =   240
      Width           =   5775
      Begin VB.ComboBox cb_mes 
         Height          =   315
         Left            =   1680
         TabIndex        =   4
         Text            =   "Combo1"
         Top             =   600
         Width           =   1455
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Exportar Excel"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   3480
         TabIndex        =   2
         Top             =   600
         Width           =   1935
      End
      Begin VB.ComboBox cb_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            =   120
         TabIndex        =   1
         Top             =   600
         Width           =   1335
      End
      Begin ComctlLib.ProgressBar pgBar 
         Height          =   255
         Left            =   120
         TabIndex        =   3
         Top             =   1080
         Width           =   5415
         _ExtentX        =   9551
         _ExtentY        =   450
         _Version        =   327682
         Appearance      =   1
      End
      Begin VB.Label Label2 
         Caption         =   "Agno"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   120
         TabIndex        =   6
         Top             =   360
         Width           =   975
      End
      Begin VB.Label Label1 
         Caption         =   "Mes"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   1680
         TabIndex        =   5
         Top             =   360
         Width           =   1095
      End
   End
End
Attribute VB_Name = "Form_informe_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 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
        
    If cb_mes.Text = "TODOS" Then
            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 & " GROUP BY ci_alu) AS a" _
                    & " ON c.ci_alu = a.ci_alu" _
                    & " ORDER BY cod_ens,num_gra,let_cur,alumno"
    Else
  
            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 mes_asis = '" & Mid(cb_mes.Text, 1, 3) & "'" _
                    & " GROUP BY ci_alu) AS a" _
                    & " ON c.ci_alu = a.ci_alu" _
                    & " ORDER BY cod_ens,num_gra,let_cur,alumno"
        
        
    End If
            
    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, 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, 1).Formula = "Nivel"
    ApExcel.Cells(1, 2).Formula = "Curso"
    ApExcel.Cells(1, 3).Formula = "Rut. Alu."
    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 = "Porcentaje"
        
    pgbar.Min = 0
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    total_dt = 0
    total_da = 0
    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("porcentaje")
        total_dt = total_dt + IIf(IsNull(objRS("dias_trabajados")), 0, objRS("dias_trabajados"))
        total_da = total_da + IIf(IsNull(objRS("dias_asistidos")), 0, objRS("dias_asistidos"))
      '' IIf(IsNull(rec(j - 1)), "Null", rec(j - 1))
        
        pgbar.Value = i - 1
        'pgBar.Refresh
        objRS.MoveNext
    Wend
    i = i + 1
    ApExcel.Cells(i, 4).Formula = "Total"
    ApExcel.Cells(i, 5).Formula = total_dt
    ApExcel.Cells(i, 6).Formula = total_da
    ApExcel.Cells(i, 7).Formula = ((total_da * 100) / total_dt)
    
   
    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.Visible = True
    ApExcel.Range("a1:g1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
    
    objRS.Close

End Sub

Private Sub Form_Load()
    Dim i As Integer
    For i = (Format(Date, "yyyy") - 2) To Format(Date, "yyyy")
        cb_agno.AddItem i
    Next i
    cb_agno.Text = Format(Date, "yyyy")
    
    cb_mes.AddItem "FEBRERO"
    cb_mes.AddItem "MARZO"
    cb_mes.AddItem "ABRIL"
    cb_mes.AddItem "MAYO"
    cb_mes.AddItem "JUNIO"
    cb_mes.AddItem "JULIO"
    cb_mes.AddItem "AGOSTO"
    cb_mes.AddItem "SEPTIEMBRE"
    cb_mes.AddItem "OCTUBRE"
    cb_mes.AddItem "NOVIEMBRE"
    cb_mes.AddItem "DICIEMBRE"
    cb_mes.AddItem "TODOS"
    cb_mes.Text = ""
    
End Sub
