VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_nomina_matriculados 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Nomina Alumnos Matriculados"
   ClientHeight    =   8640
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   13590
   Icon            =   "Form_nomina_matriculados.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   8640
   ScaleWidth      =   13590
   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            =   11760
      Picture         =   "Form_nomina_matriculados.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   7
      Top             =   7560
      Width           =   1575
   End
   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            =   240
      Picture         =   "Form_nomina_matriculados.frx":2AED8
      Style           =   1  'Graphical
      TabIndex        =   6
      Top             =   7560
      Width           =   1575
   End
   Begin VB.Frame Frame1 
      Caption         =   "Exportar Alumnos Matriculados"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   6855
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   13335
      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            =   840
         TabIndex        =   9
         Text            =   "Combo1"
         Top             =   600
         Width           =   1335
      End
      Begin VB.Frame Frame2 
         Caption         =   "Frame2"
         Height          =   2175
         Left            =   1920
         TabIndex        =   2
         Top             =   3000
         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 VB.CommandButton Command1 
         Caption         =   "Listar Alumnos"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   2520
         TabIndex        =   1
         Top             =   480
         Width           =   1815
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   5055
         Left            =   120
         TabIndex        =   4
         Top             =   1680
         Width           =   13095
         _ExtentX        =   23098
         _ExtentY        =   8916
         _Version        =   393216
      End
      Begin VB.Label Label1 
         Caption         =   "Aņo:"
         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        =   5
         Top             =   600
         Width           =   615
      End
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   8
      Top             =   7200
      Width           =   13335
      _ExtentX        =   23521
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
End
Attribute VB_Name = "Form_nomina_matriculados"
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
        pgbar.Min = 1
        pgbar.Max = IIf(rec.RecordCount = 1, 2, rec.RecordCount)
        pgbar.Value = pgbar.Min
        
        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
            pgbar.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 objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    var_sql = "SELECT rut_apo AS RUT_APO,Apoderado AS NOMBRE_APOD,ci_alu AS RUT_ALU,IF(run_titular IS NULL,rut_apo,run_titular) AS RUT_FINANCIERO," _
            & "IF(activo_alu = 'SI','ACTIVO','RETIRADO') AS ESTADO,monto_tot_cuota AS MONTO,mes AS MENSUALIDAD,fec_mat_r AS FECH_ING,usuario_ingreso AS USUARIO_INGRESO" _
            & " From" _
            & " (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,monto_tot_cuota,me.mes,f.mes AS orden," _
            & "activo_alu , fec_mat_r, usuario_ingreso" _
            & " FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
            & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes" _
            & " AND m.ci_alu = f.run_alu AND m.anno_mat = f.agno" _
            & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r IS NOT NULL) AS f" _
            & " Left Join" _
            & " (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
            & " ON f.rut_apo = c.run_apo" _
            & " ORDER BY fech_ing,nombre_apod,rut_alu,f.orden"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
    Else
       MSFlexGrid1.Clear
       MSFlexGrid1.Rows = 2
       MsgBox "No hay alumnos matriculados en el filtro seleccionado", vbInformation
    End If
    objRS.Close
    MSFlexGrid1.Refresh
    
    Frame2.Visible = False
    
End Sub

Private Sub Command2_Click()
 Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    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
    ApExcel.Cells(1, 16).Font.Size = 12
    ApExcel.Cells(1, 16).Font.Bold = True
    
    pgbar.Min = 1
    pgbar.Max = MSFlexGrid1.Rows
    pgbar.Value = pgbar.Min
    'ApExcel.Columns("G"). = 300
    ApExcel.Range("G:G").ColumnWidth = 20
    ApExcel.Columns(7).Select
    'ApExcel.Selection.NumberFormat = "#"
    ApExcel.Selection.NumberFormat = "@"
    
'    If cb_factoring.Text <> "" Then
'        ApExcel.Cells(1, 16) = "FACTORING"
'    End If
    
    For i = 1 To MSFlexGrid1.Rows - 1
         For j = 1 To MSFlexGrid1.Cols - 1
            'ApExcel.Cells((i + 1), j).WrapText = True
'            If (j = 17 Or j = 18) And fp_mensualidad.Text = "Cheques" Then
'                ApExcel.Cells((i + 1), j).NumberFormat = "dd/mm/yyyy"
'                ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", Format(MSFlexGrid1.TextMatrix(i, j), "yyyy/mm/dd"))
'            Else
'                If (j = 8 Or j = 14) And (fp_mensualidad.Text = "Efectivo" Or fp_mensualidad.Text = "Credito" _
'                Or fp_mensualidad.Text = "Debito" Or fp_mensualidad.Text = "Transferencia" Or fp_mensualidad.Text = "Upago" _
'                Or fp_mensualidad.Text = "Deposito" Or fp_mensualidad.Text = "Cobranza") Then
'                    ApExcel.Cells((i + 1), j).NumberFormat = "dd/mm/yyyy"
'                    ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", Format(MSFlexGrid1.TextMatrix(i, j), "yyyy/mm/dd"))
'                Else
                    If (j = 8) Then
                        ApExcel.Cells((i + 1), j).NumberFormat = "dd/mm/yyyy"
                        ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", Format(MSFlexGrid1.TextMatrix(i, j), "yyyy/mm/dd"))
                    Else
                        ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", MSFlexGrid1.TextMatrix(i, j))
                    End If
'                End If
'            End If
            
            pgbar.Value = i
         Next j
'         If cb_factoring.Text <> "" Then
'            ApExcel.Cells((i + 1), j) = cb_factoring.Text
'         End If
    Next i
            
    ApExcel.Visible = True
    'ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub

Private Sub Form_Load()
    Dim i As Integer
    For i = 2021 To Year(Date) + 1
        cb_agno.AddItem i
    Next i
    cb_agno.Text = Year(Date)
End Sub
