VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_consolidado_asistencia 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Consolidado Asistencia"
   ClientHeight    =   7890
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   13125
   Icon            =   "Form_consolidado_asistencia.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   7890
   ScaleWidth      =   13125
   Begin VB.CommandButton Command2 
      Caption         =   "Exportar Excel"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   120
      Picture         =   "Form_consolidado_asistencia.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   8
      Top             =   6840
      Width           =   1575
   End
   Begin VB.CommandButton cmd_salir 
      Caption         =   "Salir"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   11400
      Picture         =   "Form_consolidado_asistencia.frx":2AE98
      Style           =   1  'Graphical
      TabIndex        =   7
      Top             =   6840
      Width           =   1575
   End
   Begin VB.Frame Frame1 
      Caption         =   "Alumnos inasistentes"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   6255
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   12855
      Begin VB.CommandButton Command1 
         Caption         =   "Listar alumnos inasistentes"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   10080
         TabIndex        =   4
         Top             =   360
         Width           =   2535
      End
      Begin VB.Frame Frame2 
         Caption         =   "Frame2"
         Height          =   2175
         Left            =   1560
         TabIndex        =   2
         Top             =   2400
         Visible         =   0   'False
         Width           =   9375
         Begin VB.Label Label2 
            Caption         =   "En proceso"
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   48
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   975
            Left            =   2280
            TabIndex        =   3
            Top             =   600
            Width           =   5415
         End
      End
      Begin MSComCtl2.DTPicker DTP_fecha 
         Height          =   375
         Left            =   840
         TabIndex        =   1
         Top             =   480
         Width           =   1455
         _ExtentX        =   2566
         _ExtentY        =   661
         _Version        =   393216
         Format          =   52494337
         CurrentDate     =   42103
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   5055
         Left            =   120
         TabIndex        =   5
         Top             =   1080
         Width           =   12615
         _ExtentX        =   22251
         _ExtentY        =   8916
         _Version        =   393216
      End
      Begin VB.Label Label1 
         Caption         =   "Fecha"
         Height          =   255
         Left            =   240
         TabIndex        =   6
         Top             =   600
         Width           =   615
      End
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   0
      TabIndex        =   9
      Top             =   6480
      Width           =   12975
      _ExtentX        =   22886
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
End
Attribute VB_Name = "Form_consolidado_asistencia"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Private Sub Rec2Grid(ByRef rec As ADODB.Recordset, ByRef grid As Object, Optional ByVal Numeracion As Boolean = True)
    grid.Clear
    If rec.RecordCount > 0 Then
        grid.Cols = rec.Fields.count + 1
        grid.Rows = rec.RecordCount + 1
        grid.FixedCols = 1
        grid.FixedRows = 1
        grid.TextMatrix(0, 0) = "Nº"
        grid.ColWidth(0) = IIf(Numeracion, TextWidth(grid.TextMatrix(0, 0)) + 100, 0)
        For i = 1 To rec.Fields.count
            grid.TextMatrix(0, i) = UCase(rec.Fields(i - 1).Name)
            grid.ColWidth(i) = TextWidth(grid.TextMatrix(0, i)) + 100
        Next i
        rec.MoveFirst
        For i = 1 To rec.RecordCount
            'progreso.Value = i
            grid.TextMatrix(i, 0) = i
            grid.ColWidth(0) = IIf(Numeracion, IIf(TextWidth(grid.TextMatrix(i, 0)) + 100 > grid.ColWidth(0), TextWidth(grid.TextMatrix(i, 0)) + 100, grid.ColWidth(0)), 0)
            For j = 1 To rec.Fields.count
                grid.TextMatrix(i, j) = IIf(IsNull(rec(j - 1)), "Null", rec(j - 1))
                grid.ColWidth(j) = IIf(TextWidth(grid.TextMatrix(i, j)) + 100 > grid.ColWidth(j), TextWidth(grid.TextMatrix(i, j)) + 100, grid.ColWidth(j))
            Next j
            rec.MoveNext
        Next i
    Else
        grid.Cols = 2
        grid.Rows = 2
    End If
End Sub

Private Sub cmd_Salir_Click()
    Unload Me
End Sub

Private Sub Command1_Click()
    Dim var_sql As String
    Dim conecta As Boolean
    Dim maximo_mes As Integer
    Dim dia As Integer, mes As Integer, agno As Integer
    Dim mes_palabra As String
    Dim colegios(7) As String
    Dim colegios2(7) As String
   
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    Frame2.Visible = True
    
    dia = Format(dtp_fecha.Value, "dd")
    mes = Format(dtp_fecha.Value, "mm")
    agno = Format(dtp_fecha.Value, "yyyy")
           
    Select Case mes
        Case 2
            mes_palabra = "FEB"
        Case 3
            mes_palabra = "MAR"
        Case 4
            mes_palabra = "ABR"
        Case 5
            mes_palabra = "MAY"
        Case 6
            mes_palabra = "JUN"
        Case 7
            mes_palabra = "JUL"
        Case 8
            mes_palabra = "AGO"
        Case 9
            mes_palabra = "SEP"
        Case 10
            mes_palabra = "OCT"
        Case 11
            mes_palabra = "NOV"
        Case 12
            mes_palabra = "DIC"
    End Select
    
    var_sql = "SELECT SUM(IF(MID(asis," & dia & ",1) = '·',1,0)) sin_asistencias" _
            & " FROM asistencia a,codigos_ensenanza ce WHERE agno = " & agno & " AND mes_asis = '" & mes_palabra & "' AND activo_alu = 'SI' AND a.cod_ens = ce.cod_ens"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
        If objRS("sin_asistencias") = 0 Then
            MsgBox "Dia No valido", vbInformation
            Frame2.Visible = False
            MSFlexGrid1.Rows = 1
            MSFlexGrid1.Cols = 6
            Exit Sub
        End If
    Else
       MsgBox "No se ha registrado asistencia este dia", vbInformation
       Exit Sub
    End If
    objRS.Close
                
'    var_sql = "SELECT num_gra as grado,let_cur as letra,des_ens as nivel,SUM(IF(MID(asis," & CInt(dia) & ",1) = 'X',1,0)) inasistencias,SUM(IF(MID(asis," & CInt(dia) & ",1) = '·',1,0)) asistencias" _
'            & " FROM asistencia a,codigos_ensenanza ce" _
'            & " WHERE agno = " & agno & " AND mes_asis = '" & mes_palabra & "' AND activo_alu = 'SI' AND a.cod_ens = ce.cod_ens" _
'            & " GROUP BY a.cod_ens,a.num_gra,let_cur" _
'            & " ORDER BY a.cod_ens,a.num_gra,let_cur"

' MsgBox Format(dtp_fecha, "yyyy-mm-dd")

'    var_sql = "SELECT grado,letra,nivel,inasistencias,asistencias,primera_validacion,ultima_validacion,nombre_usuario FROM" _
'            & " (SELECT num_gra as grado,let_cur as letra,des_ens as nivel,a.cod_ens,SUM(IF(MID(asis," & CInt(dia) & ",1) = 'X',1,0)) inasistencias,SUM(IF(MID(asis," & CInt(dia) & ",1) = '·',1,0)) asistencias" _
'            & " FROM asistencia a,codigos_ensenanza ce" _
'            & " WHERE agno = " & agno & " AND mes_asis = '" & mes_palabra & "' AND activo_alu = 'SI' AND a.cod_ens = ce.cod_ens" _
'            & " GROUP BY a.cod_ens,a.num_gra,let_cur" _
'            & " ORDER BY a.cod_ens,a.num_gra,let_cur) AS a" _
'            & " LEFT JOIN (SELECT num_gra,let_cur,cod_ens,nombre_usuario,CONCAT(HOUR(min(hora))-1,':',TIME_FORMAT(min(hora),'%i:%s')) AS primera_validacion,CONCAT(HOUR(max(hora))-1,':',TIME_FORMAT(max(hora),'%i:%s')) AS ultima_validacion" _
'            & " FROM valida_asistencia WHERE fecha = '" & Format(DTP_fecha, "yyyy-mm-dd") & "' GROUP BY fecha,cod_ens,num_gra,let_cur) AS v" _
'            & " ON a.cod_ens = v.cod_ens AND a.grado = v.num_gra AND a.letra = v.let_cur" _
'            & " ORDER BY a.cod_ens,a.grado,letra"
    
    
    colegios(1) = "sanma_01"
    colegios(2) = "laflo_01"
    colegios(3) = "sanbe_01"
    colegios(4) = "pudah_01"
    
    colegios2(1) = "Boston College San Martin"
    colegios2(2) = "Boston College La Florida"
    colegios2(3) = "Boston College San Bernardo"
    colegios2(4) = "Boston College Pudahuel"
    
    conecta = ConectarBDSiempre(conn_deuda, "192.168.7.91", "sanma_01", "groot", "kripplefight", True)
       
'    var_sql = "SELECT cel_apo AS Celular," _
'            & " IF(LOCATE(' ',nom_alu) = 0,nom_alu,MID(nom_alu,1,(LOCATE(' ',nom_alu)-1))) Estudiante,IF(e.nom_est = 'BOSTON COLLEGE','BOSTON COLLEGE LA FLORIDA',e.nom_est) AS Sede," _
'            & " IF(LOCATE(' ',noms_apo) = 0,noms_apo,MID(noms_apo,1,(LOCATE(' ',noms_apo)-1))) AS Apoderado," _
'            & " CONCAT(IF(a.cod_ens = '110' OR a.cod_ens = '310',a.num_gra,''),CAST(IF(a.cod_ens = '110' OR a.cod_ens = '310','º ','') AS CHAR),des_ens_abrev,' ',a.let_cur) AS Curso," _
'            & " (asi_r - asi_t) numero_inasistencias_mes" _
'            & " FROM asistencia a,codigos_ensenanza ce,postulantes p,apo_x_alu axa,apoderados ap,establecimientos e" _
'            & " Where a.ci_alu = axa.ci_alu And a.agno = axa.agno And axa.rut_apo = ap.rut_apo" _
'            & " AND a.ci_alu = p.ci_alu AND a.agno = " & agno & " AND mes_asis = '" & mes_palabra & "' AND activo_alu = 'SI' AND a.cod_ens = ce.cod_ens" _
'            & " AND MID(asis," & CInt(Dia) & ",1) = 'X'" _
'            & " ORDER BY ord_ens,a.num_gra,a.let_cur,Estudiante"
                
    var_sql = "SELECT cel_apo AS Celular," _
            & " concat(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) Estudiante,IF(e.nom_est = 'BOSTON COLLEGE','BOSTON COLLEGE LA FLORIDA',e.nom_est) AS Sede," _
            & " concat(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado," _
            & " CONCAT(IF(a.cod_ens = '110' OR a.cod_ens = '310',a.num_gra,''),CAST(IF(a.cod_ens = '110' OR a.cod_ens = '310','º ','') AS CHAR),des_ens_abrev,' ',a.let_cur) AS Curso," _
            & " (asi_r - asi_t) numero_inasistencias_mes" _
            & " FROM asistencia a,codigos_ensenanza ce,postulantes p,apo_x_alu axa,apoderados ap,establecimientos e" _
            & " Where a.ci_alu = axa.ci_alu And a.agno = axa.agno And axa.rut_apo = ap.rut_apo" _
            & " AND a.ci_alu = p.ci_alu AND a.agno = " & agno & " AND mes_asis = '" & mes_palabra & "' AND activo_alu = 'SI' AND a.cod_ens = ce.cod_ens" _
            & " AND MID(asis," & CInt(dia) & ",1) = 'X'" _
            & " ORDER BY ord_ens,a.num_gra,a.let_cur,Estudiante"
                
    objRS.Open var_sql, conn_deuda
    If objRS.RecordCount > 0 Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
       ' msflex_alumnos.ColSel = 5
    Else
       MsgBox "No se ha registrado asistencia este día en " & colegios2(1), vbInformation
       Exit Sub
    End If
    objRS.Close
    conn_deuda.Close
    
    filas = MSFlexGrid1.Rows
    For j = 2 To 4
        conecta = ConectarBDSiempre(conn_deuda, "192.168.7.91", colegios(j), "groot", "kripplefight", True)
       
        var_sql = "SELECT cel_apo AS Celular,concat(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) Estudiante,IF(e.nom_est = 'BOSTON COLLEGE','BOSTON COLLEGE LA FLORIDA',e.nom_est) AS Sede," _
            & " concat(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado," _
            & " CONCAT(IF(a.cod_ens = '110' OR a.cod_ens = '310',a.num_gra,''),CAST(IF(a.cod_ens = '110' OR a.cod_ens = '310','º ','') AS CHAR),des_ens_abrev,' ',a.let_cur) AS Curso," _
            & " (asi_r - asi_t) Numero_inasistencias_mes" _
            & " FROM asistencia a,codigos_ensenanza ce,postulantes p,apo_x_alu axa,apoderados ap,establecimientos e" _
            & " Where a.ci_alu = axa.ci_alu And a.agno = axa.agno And axa.rut_apo = ap.rut_apo" _
            & " AND a.ci_alu = p.ci_alu AND a.agno = " & agno & " AND mes_asis = '" & mes_palabra & "' AND activo_alu = 'SI' AND a.cod_ens = ce.cod_ens" _
            & " AND MID(asis," & CInt(dia) & ",1) = 'X'" _
            & " ORDER BY ord_ens,a.num_gra,a.let_cur,Estudiante"
                
        objRS.Open var_sql, conn_deuda
        If objRS.RecordCount > 0 Then
        '    Call Rec2Grid(objRS, MSFlexGrid1, True)
        ' msflex_alumnos.ColSel = 5
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        Else
            MsgBox "No se ha registrado asistencia este día en " & colegios2(j), vbInformation
        End If
        objRS.Close
        conn_deuda.Close
                
    Next j
    
    MSFlexGrid1.Refresh
    
    Frame2.Visible = False
   
End Sub

Private Sub Command2_Click()
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")
    ApExcel.Workbooks.Add
          
    For i = 1 To Me.MSFlexGrid1.Cols - 1
        ApExcel.Cells(1, i).Font.Size = 12
        ApExcel.Cells(1, i).Font.Bold = True
        ApExcel.Cells(1, i).Formula = Me.MSFlexGrid1.TextMatrix(0, i)
    Next i
    pgbar.Min = 1
    pgbar.Max = MSFlexGrid1.Rows
    pgbar.Value = pgbar.Min
    For i = 1 To MSFlexGrid1.Rows - 1

         For j = 1 To MSFlexGrid1.Cols - 1
            ApExcel.Cells((i + 1), j).Formula = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", MSFlexGrid1.TextMatrix(i, j))
        
        pgbar.Value = i
        Next j
    Next i
            
    ApExcel.Visible = True
    'ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub

Private Sub Form_Load()
    dtp_fecha.Value = Date
End Sub


