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 = "{0ECD9B60-23AA-11D0-B351-00A0C9055D8E}#6.0#0"; "MSHFLXGD.OCX"
Begin VB.Form Form_licencias 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Registro de Licencias"
   ClientHeight    =   6540
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   7590
   Icon            =   "Form_licencias.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   6540
   ScaleWidth      =   7590
   Begin VB.Frame Frame1 
      Caption         =   "Listado de Licencias"
      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           =   7335
      Begin MSComctlLib.ProgressBar pgbar 
         Height          =   255
         Left            =   120
         TabIndex        =   22
         Top             =   5880
         Width           =   6975
         _ExtentX        =   12303
         _ExtentY        =   450
         _Version        =   393216
         Appearance      =   1
      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            =   4440
         TabIndex        =   21
         Top             =   5280
         Width           =   1335
      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            =   5880
         TabIndex        =   18
         Top             =   5280
         Width           =   1335
      End
      Begin VB.CommandButton cmd_eliminar 
         Caption         =   "Eliminar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   3000
         TabIndex        =   17
         Top             =   5280
         Width           =   1335
      End
      Begin VB.CommandButton cmd_modificar 
         Caption         =   "Modificar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   1560
         TabIndex        =   16
         Top             =   5280
         Width           =   1335
      End
      Begin VB.CommandButton cmd_agregar 
         Caption         =   "Agregar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   120
         TabIndex        =   15
         Top             =   5280
         Width           =   1335
      End
      Begin VB.Frame frame_licencias 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00C00000&
         Height          =   4335
         Left            =   720
         TabIndex        =   2
         Top             =   480
         Visible         =   0   'False
         Width           =   5775
         Begin VB.CommandButton cmd_cancelar 
            Caption         =   "Cancelar"
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   8.25
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   375
            Left            =   3120
            TabIndex        =   20
            Top             =   3600
            Width           =   1335
         End
         Begin VB.CommandButton cmd_aceptar 
            Caption         =   "Aceptar"
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   8.25
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   375
            Left            =   1080
            TabIndex        =   19
            Top             =   3600
            Width           =   1215
         End
         Begin VB.TextBox txt_motivo 
            Height          =   285
            Left            =   1440
            TabIndex        =   14
            Top             =   3000
            Width           =   4095
         End
         Begin VB.TextBox txt_folio 
            Height          =   285
            Left            =   1440
            TabIndex        =   13
            Top             =   2520
            Width           =   1575
         End
         Begin VB.TextBox txt_ci_alu 
            Height          =   285
            Left            =   1440
            TabIndex        =   10
            Top             =   840
            Width           =   1575
         End
         Begin VB.ComboBox cb_agno 
            Height          =   315
            Left            =   1440
            TabIndex        =   9
            Top             =   360
            Width           =   1575
         End
         Begin MSComCtl2.DTPicker dtp_desde 
            Height          =   375
            Left            =   1440
            TabIndex        =   11
            Top             =   1320
            Width           =   1575
            _ExtentX        =   2778
            _ExtentY        =   661
            _Version        =   393216
            Format          =   51511297
            CurrentDate     =   45366
         End
         Begin MSComCtl2.DTPicker dtp_hasta 
            Height          =   375
            Left            =   1440
            TabIndex        =   12
            Top             =   1920
            Width           =   1575
            _ExtentX        =   2778
            _ExtentY        =   661
            _Version        =   393216
            Format          =   51511297
            CurrentDate     =   45366
         End
         Begin VB.Label Label11 
            Caption         =   "Motivo"
            Height          =   255
            Left            =   360
            TabIndex        =   8
            Top             =   3000
            Width           =   975
         End
         Begin VB.Label Label10 
            Caption         =   "Folio"
            Height          =   255
            Left            =   360
            TabIndex        =   7
            Top             =   2520
            Width           =   735
         End
         Begin VB.Label Label9 
            Caption         =   "Fecha hasta"
            Height          =   255
            Left            =   360
            TabIndex        =   6
            Top             =   1920
            Width           =   1095
         End
         Begin VB.Label Label8 
            Caption         =   "Fecha desde"
            Height          =   255
            Left            =   360
            TabIndex        =   5
            Top             =   1320
            Width           =   1215
         End
         Begin VB.Label Label7 
            Caption         =   "Aņo"
            Height          =   255
            Left            =   360
            TabIndex        =   4
            Top             =   360
            Width           =   975
         End
         Begin VB.Label Label3 
            Caption         =   "Rut. Alumno"
            Height          =   255
            Left            =   360
            TabIndex        =   3
            Top             =   840
            Width           =   1215
         End
      End
      Begin MSHierarchicalFlexGridLib.MSHFlexGrid grid_licencias 
         Height          =   4815
         Left            =   120
         TabIndex        =   1
         Top             =   240
         Width           =   7095
         _ExtentX        =   12515
         _ExtentY        =   8493
         _Version        =   393216
         FocusRect       =   0
         SelectionMode   =   1
         RowSizingMode   =   1
         _NumberOfBands  =   1
         _Band(0).Cols   =   2
      End
   End
End
Attribute VB_Name = "Form_licencias"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub cmd_Aceptar_Click()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql As String
    
    If txt_ci_alu.Text = "" Or cb_agno.Text = "" Or dtp_desde.Value = "" Or dtp_hasta.Value = "" Or txt_motivo.Text = "" Or txt_folio.Text = "" Then
        MsgBox "Debe completar todos los datos", vbInformation
        Exit Sub
    End If
    
    If Len(txt_motivo.Text) < 4 Then
        MsgBox "Motivo debe tener un largo de 4 como minimo", vbInformation
        Exit Sub
    End If
    
    If cb_agno.Text <> Year(dtp_desde.Value) Then
        MsgBox "El aņo debe ser igual al de la vigencia de la licencia", vbInformation
        Exit Sub
    End If
    
   '' If Len(txt_ci_alu.Text) = 10 Or Len(txt_ci_alu.Text) = 11 Then
        var_sql = "select ci_alu from alu_x_cur where agno = " & cb_agno.Text & " and ci_alu = '" & txt_ci_alu.Text & "'"
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
        Else
            MsgBox "Rut de alumno invalido, el rut debe ser de un alumno del colegio", vbInformation
            Exit Sub
        End If
        objRS.Close
    '' End If

    If frame_licencias.Caption = "Ingresar licencia" Then
'        var_sql = "insert into fnza_cajas_folios(folio,folio_inicial,folio_final,activo)" _
'                & " values(" & Folio & "," & txt_folio_inicial.Text & "," & txt_folio_final.Text & ",'NO')"
        var_sql = "INSERT INTO fnza_licencias(agno,desde,hasta,ci_alu,motivo,folio)" _
                & " VALUES('" & cb_agno.Text & "','" & Format(dtp_desde.Value, "yyyy-mm-dd") & "','" & Format(dtp_hasta.Value, "yyyy-mm-dd") & "','" & txt_ci_alu.Text & "',ucase('" & txt_motivo.Text & "'),'" & txt_folio.Text & "');"
        Mensaje = "La licencia ha sido ingresada satisfactoriamente"
    Else
'        var_sql = "update fnza_cajas_folios set folio_inicial = " & txt_folio_inicial.Text & ",folio_final = " & txt_folio_final.Text & " where" _
'                & " folio = " & Folio & " and folio_inicial >= " & folio_inicial & " and folio_final <= " & folio_final
        var_sql = "UPDATE fnza_licencias" _
                & " SET ci_alu = '" & txt_ci_alu.Text & "',agno = " & cb_agno.Text & ",desde = '" & Format(dtp_desde.Value, "yyyy-mm-dd") & "',hasta = '" & Format(dtp_hasta.Value, "yyyy-mm-dd") _
                & "',folio = " & txt_folio.Text & ",motivo = ucase('" & txt_motivo.Text & "')" _
                & " WHERE ci_alu = '" & grid_licencias.TextMatrix(grid_licencias.Row, 4) & "' AND agno = " & grid_licencias.TextMatrix(grid_licencias.Row, 1) _
                & " AND desde = '" & Format(grid_licencias.TextMatrix(grid_licencias.Row, 2), "yyyy-mm-dd") & "' AND hasta = '" & Format(grid_licencias.TextMatrix(grid_licencias.Row, 3), "yyyy-mm-dd") _
                & "' AND folio = " & grid_licencias.TextMatrix(grid_licencias.Row, 6) & " AND motivo = '" & grid_licencias.TextMatrix(grid_licencias.Row, 5) & "'"
        Mensaje = "La licencia ha sido modificada satisfactoriamente"
    End If
        
    conn.Execute var_sql
    
    frame_licencias.Visible = False
    
    Form_Load
    MsgBox Mensaje, vbInformation
End Sub

Private Sub cmd_agregar_Click()
    frame_licencias.Caption = "Ingresar licencia"
    frame_licencias.Visible = True
    txt_ci_alu.Text = ""
    cb_agno.Text = Anno
    dtp_desde.Value = Date
    dtp_hasta.Value = Date
    txt_motivo.Text = ""
    txt_folio.Text = ""
End Sub

Private Sub cmd_Cancelar_Click()
    frame_licencias.Visible = False
End Sub

Private Sub cmd_eliminar_Click()
''    MsgBox grid_licencias.TextMatrix(grid_licencias.Row, 1) ' agno
''    MsgBox grid_licencias.TextMatrix(grid_licencias.Row, 2) ' fecha desde
''    MsgBox grid_licencias.TextMatrix(grid_licencias.Row, 3) ' fecha hasta
''    MsgBox grid_licencias.TextMatrix(grid_licencias.Row, 4) ' ci_alu
''    MsgBox grid_licencias.TextMatrix(grid_licencias.Row, 5) ' motivo
''    MsgBox grid_licencias.TextMatrix(grid_licencias.Row, 6) ' folio
    '' Format(dtp_fin.Value, "yyyy-mm-dd")
    
    var_sql = "delete FROM fnza_licencias WHERE ci_alu = '" & grid_licencias.TextMatrix(grid_licencias.Row, 4) & "' AND agno = " _
            & grid_licencias.TextMatrix(grid_licencias.Row, 1) & " AND desde = '" & Format(grid_licencias.TextMatrix(grid_licencias.Row, 2), "yyyy-mm-dd") _
            & "' AND folio = " & grid_licencias.TextMatrix(grid_licencias.Row, 6) & " AND motivo = '" & grid_licencias.TextMatrix(grid_licencias.Row, 5) & "'"
    conn.Execute var_sql
    Form_Load
    MsgBox "La licencia ha sido eliminada satisfactoriamente", vbInformation
End Sub

Private Sub cmd_modificar_Click()

    'txt_folio_inicial = grid_cajas.TextMatrix(grid_cajas.Row, 1)
    'txt_folio_final = grid_cajas.TextMatrix(grid_cajas.Row, 2)
    txt_ci_alu.Text = grid_licencias.TextMatrix(grid_licencias.Row, 4)
    cb_agno.Text = grid_licencias.TextMatrix(grid_licencias.Row, 1)
    dtp_desde.Value = grid_licencias.TextMatrix(grid_licencias.Row, 2)
    dtp_hasta.Value = grid_licencias.TextMatrix(grid_licencias.Row, 3)
    txt_motivo.Text = grid_licencias.TextMatrix(grid_licencias.Row, 5)
    txt_folio.Text = grid_licencias.TextMatrix(grid_licencias.Row, 6)

''    var_sql = "update fnza_licencias set agno = '" & cb_agno.Text & "',desde = '" & Format(dtp_desde.Value, "yyyy-mm-dd") & "',hasta = '" & Format(dtp_hasta.Value, "yyyy-mm-dd") & "'," _
''            & "ci_alu='" & txt_ci_alu.Text & "',motivo = '" & txt_motivo.Text & "',folio = '" & txt_folio.Text & "'" _
''            & " WHERE ci_alu = '" & grid_licencias.TextMatrix(grid_licencias.Row, 4) & "' AND agno = " _
''            & grid_licencias.TextMatrix(grid_licencias.Row, 1) & " AND desde = '" & Format(grid_licencias.TextMatrix(grid_licencias.Row, 2), "yyyy-mm-dd") _
''            & "' AND folio = " & grid_licencias.TextMatrix(grid_licencias.Row, 6) & " AND motivo = '" & grid_licencias.TextMatrix(grid_licencias.Row, 5) & "'"
''    conn.Execute var_sql
    frame_licencias.Caption = "Modificar Licencia"
    frame_licencias.Visible = True
End Sub

Private Sub cmd_Salir_Click()
    Unload Me
End Sub

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

        For j = 1 To grid_licencias.Cols - 1
            If j = 2 Or j = 3 Then
                '' ApExcel.Cells(I, 3).Formula = Format(objRS("fecha_blta"), "yyyy/mm/dd")
                ApExcel.Cells((i + 1), j).Formula = Format(IIf(grid_licencias.TextMatrix(i, j) = "Null", "", grid_licencias.TextMatrix(i, j)), "yyyy/mm/dd")
            Else
                ApExcel.Cells((i + 1), j).Formula = IIf(grid_licencias.TextMatrix(i, j) = "Null", "", grid_licencias.TextMatrix(i, j))
            End If
            pgbar.Value = i
        Next j
        ApExcel.Cells((i + 1), j).Formula = DevuelveCampo("postulantes", "", "concat(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu)", "ci_alu = '" & grid_licencias.TextMatrix(i, 4) & "'", conn)
        ApExcel.Cells((i + 1), j + 1).Formula = DevuelveCampo("alu_x_cur", "", "concat(num_gra,'° ',let_cur,' ',IF(cod_ens = 111,'PlayGroup',IF(cod_ens = '996','PreKinder',IF(cod_ens = '010','Kinder',IF(cod_ens = '110','Basico','Media')))))", "activo_alu = 'SI' and ci_alu = '" & grid_licencias.TextMatrix(i, 4) & "' and agno = " & grid_licencias.TextMatrix(i, 1), conn)
    Next i
            
    ApExcel.Visible = True
    ApExcel.Range("a1:h1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub

Private Sub Form_Load()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql As String
    var_sql = "SELECT * FROM fnza_licencias where agno = 2026 ORDER BY agno,desde"
    objRS.Open var_sql, conn
    grid_licencias.Clear
    grid_licencias.Rows = 2
    If objRS.RecordCount > 0 Then
        Call Rec2Grid(objRS, grid_licencias, True)
    End If
    objRS.Close
    For i = 2025 To (Anno + 1)
        cb_agno.AddItem i
    Next i
    dtp_desde.Value = Date
    dtp_hasta.Value = Date
End Sub

Private Sub Rec2Grid(ByRef rec As ADODB.Recordset, ByRef grid As Object, Optional ByVal Numeracion As Boolean = True)
    grid.ClearStructure
    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
            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 Function validar_datos() As String
    If txt_ci_alu.Text = "" Or cb_agno.Text = "" Or dtp_desde.Value = "" Or dtp_hasta.Value = "" Or txt_motivo.Text = "" Or txt_folio.Text = "" Then Mensaje = "Debe completar todos los datos"

End Function
