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_rendicion_excel 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Rendición Excel"
   ClientHeight    =   8790
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   13110
   Icon            =   "Form_rendicion_excel.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   8790
   ScaleWidth      =   13110
   Begin VB.Frame Frame1 
      Caption         =   "Listado de boletas"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   7095
      Left            =   120
      TabIndex        =   2
      Top             =   120
      Width           =   12855
      Begin VB.Frame Frame2 
         Caption         =   "Frame2"
         Height          =   2175
         Left            =   2160
         TabIndex        =   5
         Top             =   3360
         Visible         =   0   'False
         Width           =   9015
         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            =   2040
            TabIndex        =   6
            Top             =   600
            Width           =   5175
         End
      End
      Begin VB.Frame Frame3 
         Caption         =   "Seleccione rango de folios"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00C00000&
         Height          =   1095
         Left            =   120
         TabIndex        =   3
         Top             =   480
         Width           =   12615
         Begin VB.TextBox txt_folio_final 
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   8.25
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   285
            Left            =   3240
            TabIndex        =   11
            Top             =   600
            Width           =   1095
         End
         Begin VB.TextBox txt_folio_inicial 
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   8.25
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   285
            Left            =   720
            TabIndex        =   10
            Top             =   600
            Width           =   1095
         End
         Begin VB.CommandButton Command1 
            Caption         =   "Listar boletas"
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   8.25
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   495
            Left            =   9960
            TabIndex        =   4
            Top             =   360
            Width           =   2535
         End
         Begin VB.Label Label4 
            Caption         =   "Hasta:"
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   8.25
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   255
            Left            =   2640
            TabIndex        =   13
            Top             =   600
            Width           =   615
         End
         Begin VB.Label Label3 
            Caption         =   "Desde:"
            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        =   12
            Top             =   600
            Width           =   615
         End
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   5055
         Left            =   120
         TabIndex        =   7
         Top             =   1920
         Width           =   12615
         _ExtentX        =   22251
         _ExtentY        =   8916
         _Version        =   393216
      End
   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            =   9960
      Picture         =   "Form_rendicion_excel.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   1
      Top             =   7800
      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            =   1560
      Picture         =   "Form_rendicion_excel.frx":2AED8
      Style           =   1  'Graphical
      TabIndex        =   0
      Top             =   7800
      Width           =   1575
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   8
      Top             =   7440
      Width           =   12855
      _ExtentX        =   22675
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
   Begin VB.Label Label1 
      Caption         =   "Label1"
      Height          =   495
      Left            =   6000
      TabIndex        =   9
      Top             =   3600
      Width           =   1215
   End
End
Attribute VB_Name = "Form_rendicion_excel"
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 maximo_mes As Integer
   
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    Frame2.Visible = True
    MSFlexGrid1.Cols = 2
    MSFlexGrid1.Rows = 2
    MSFlexGrid1.Clear
    
    var_sql = "DROP TABLE IF EXISTS temp_rend"
    conn.Execute var_sql
    
    var_sql = "DROP TABLE IF EXISTS temp_cheque;"
    conn.Execute var_sql
    
    var_sql = "CREATE TABLE temp_rend TYPE = MYISAM" _
            & " SELECT monto_pago,monto_pago as monto_blta,mes,agno,IF(tipo_pago = 'ctdo',monto_pago,0) AS monto_efectivo,IF(tipo_pago = 'chqe',monto_pago,0) AS monto_cheques," _
            & "IF(tipo_pago = 'cobr',monto_pago,0) AS monto_cobranza,IF(tipo_pago = 'depo',monto_pago,0) AS monto_deposito,IF(tipo_pago = 'pat',monto_pago,0) AS monto_pat," _
            & "IF(tipo_pago = 'tcre',monto_pago,0) AS monto_t_credito,IF(tipo_pago = 'tdeb',monto_pago,0) AS monto_t_debito,IF(tipo_pago = 'tran',monto_pago,0) AS monto_tran,fecha_pago," _
            & "folio_blta_fisico,SUM(mm.monto_pago) AS monto,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,run_apo," _
            & "CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS alumno,b.run_alu,fech_venc_cptos,tipo_pago,n.folio_mvto,cod_concepto," _
            & "IF(concepto = 'ct00','1 de 11',IF(concepto='esco',concat((m.mes - 1),' de 11'),'incorporacion')) AS concepto" _
            & " FROM fnza_boletas b,fnza_dtll_mvto n,fnza_movimientos_mensualidad mm,fnza_detalle_mensualidad m,apoderados a,postulantes p" _
            & " Where b.folio_blta = n.folio_blta And n.folio_mvto = mm.folio_mvto And n.folio_dtll = m.folio_dtll" _
            & " AND tipo_blta = 3 AND folio_blta_fisico >= " & txt_folio_inicial.Text & " AND folio_blta_fisico <= " & txt_folio_final.Text & " AND estado_blta = 'aprb' AND b.run_apo = a.rut_apo AND b.run_alu = p.ci_alu" _
            & " GROUP BY folio_blta_fisico,fech_venc_cptos ORDER BY fecha_pago,folio_blta_fisico,fech_venc_cptos;"
    conn.Execute var_sql
    
    var_sql = "CREATE TABLE temp_cheque TYPE = MYISAM" _
            & " SELECT folio_mvto,c.folio_chq,cta_cte,chq_serie,chq_numero,monto AS monto_asignado," _
            & "monto_chq,nom_titular AS titular,chq_fecha AS fecha_vencimiento,descripcion AS banco,run_titular" _
            & " FROM fnza_chq_mvto m,fnza_cheques_recibidos c,fnza_cod_banco b" _
            & " WHERE m.folio_chq = c.folio_chq AND c.banco = b.banco group by folio_mvto;"
    conn.Execute var_sql
    
    var_sql = "DROP TABLE IF EXISTS t_rend;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS t_cheque;"
    conn.Execute var_sql
    var_sql = "CREATE INDEX t_rend ON temp_rend(folio_mvto);"
    conn.Execute var_sql
    var_sql = "CREATE INDEX t_cheque ON temp_cheque(folio_mvto);"
    conn.Execute var_sql
        
    var_sql = "select nom_est as colegio,r.* from (SELECT DATE_FORMAT(fecha_pago,'%d-%M-%Y') AS Fecha,folio_blta_fisico AS Folio,monto_blta AS Monto,apoderado AS Apoderado,run_apo AS Rut,alumno AS Alumno,concepto AS Cuota," _
            & "monto_efectivo AS Efectivo,monto_pat AS Pat,monto_t_credito AS 'T/C',monto_deposito AS BCI,monto_cobranza as IBR,monto_t_debito AS 'T/D',monto_tran as Transferencia,monto_cheques AS Cheque," _
            & "monto_chq AS MontoCheque,chq_serie AS SerieCheque,chq_numero AS NumeroCheque,banco AS BancoCheque,fecha_vencimiento AS FechaCheque," _
            & "Titular As Titular, run_titular As RunTitular, cta_cte As CtaCte" _
            & " FROM temp_rend r LEFT JOIN temp_cheque c ON r.folio_mvto = c.folio_mvto) as r,establecimientos;"
    conn.Execute var_sql
    
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
       ' msflex_alumnos.ColSel = 5
    Else
        'MsgBox "Esta mensualidad aún no ha vencido", 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 var_sql As String
    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))
            If j = 4 Or j = 9 Or j = 10 Or j = 11 Or j = 12 Or j = 13 Or j = 14 Or j = 15 Or j = 16 Then
                ApExcel.Cells((i + 1), j).NumberFormat = "#,##0"
            End If
            pgbar.Value = i
        Next j
    Next i
            
    var_sql = "SELECT SUM(monto_pago) AS total_boletas,SUM(monto_efectivo) AS Efectivo,SUM(monto_pat) AS Pat,SUM(monto_t_credito) AS TC," _
            & "SUM(monto_tran) AS Transferencia,sum(monto_cobranza) as cobranza,SUM(monto_t_debito) AS TD,SUM(monto_cheques) AS Cheque,SUM(monto_deposito) AS BCI" _
            & " FROM temp_rend r LEFT JOIN temp_cheque c ON r.folio_mvto = c.folio_mvto;"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
        ApExcel.Cells((i + 1), 3).Formula = "Total boletas:"
        ApExcel.Cells((i + 1), 4).Formula = objRS("total_boletas")
        ApExcel.Cells((i + 1), 8).Formula = "Totales:"
        ApExcel.Cells((i + 1), 9).Formula = objRS("Efectivo")
        ApExcel.Cells((i + 1), 10).Formula = objRS("Pat")
        ApExcel.Cells((i + 1), 11).Formula = objRS("TC")
        ApExcel.Cells((i + 1), 12).Formula = objRS("BCI")
        ApExcel.Cells((i + 1), 13).Formula = objRS("cobranza")
        ApExcel.Cells((i + 1), 14).Formula = objRS("TD")
        ApExcel.Cells((i + 1), 15).Formula = objRS("Transferencia")
        ApExcel.Cells((i + 1), 16).Formula = objRS("Cheque")
        
        'ApExcel.Cells(i, 4).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 3).Font.Bold = True
        ApExcel.Cells((i + 1), 4).Font.Bold = True
        ApExcel.Cells((i + 1), 8).Font.Bold = True
        ApExcel.Cells((i + 1), 9).Font.Bold = True
        ApExcel.Cells((i + 1), 10).Font.Bold = True
        ApExcel.Cells((i + 1), 11).Font.Bold = True
        ApExcel.Cells((i + 1), 12).Font.Bold = True
        ApExcel.Cells((i + 1), 13).Font.Bold = True
        ApExcel.Cells((i + 1), 14).Font.Bold = True
        ApExcel.Cells((i + 1), 15).Font.Bold = True
        ApExcel.Cells((i + 1), 16).Font.Bold = True
        
        ApExcel.Cells((i + 1), 4).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 9).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 10).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 11).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 12).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 13).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 14).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 15).NumberFormat = "#,##0"
        ApExcel.Cells((i + 1), 16).NumberFormat = "#,##0"
    End If
    objRS.Close
                
    ApExcel.Visible = True
    'ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub
