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"
Object = "{00025600-0000-0000-C000-000000000046}#4.6#0"; "crystl32.ocx"
Begin VB.Form Form_exportar_cheques 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Exportar cheques por fecha de ingreso"
   ClientHeight    =   7860
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   13620
   Icon            =   "Form_exportar_cheques.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   7860
   ScaleWidth      =   13620
   Begin VB.CommandButton Command4 
      Caption         =   "Command4"
      Height          =   855
      Left            =   3720
      TabIndex        =   15
      Top             =   6840
      Visible         =   0   'False
      Width           =   1575
   End
   Begin Crystal.CrystalReport c_r 
      Left            =   6480
      Top             =   7080
      _ExtentX        =   741
      _ExtentY        =   741
      _Version        =   262150
   End
   Begin VB.CommandButton Command3 
      Caption         =   "Reporte de Ingreso de Cheques"
      Height          =   855
      Left            =   1920
      TabIndex        =   14
      Top             =   6840
      Width           =   1575
   End
   Begin VB.Frame Frame1 
      Caption         =   "Exportar cheques a Excel"
      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        =   2
      Top             =   120
      Width           =   13335
      Begin VB.ComboBox cb_encargado 
         Height          =   315
         Left            =   7080
         TabIndex        =   17
         Top             =   480
         Width           =   3735
      End
      Begin VB.ComboBox cb_factoring 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   10200
         TabIndex        =   12
         Top             =   840
         Visible         =   0   'False
         Width           =   2895
      End
      Begin VB.Frame Frame2 
         Caption         =   "Frame2"
         Height          =   2175
         Left            =   1920
         TabIndex        =   4
         Top             =   2520
         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        =   5
            Top             =   600
            Width           =   5415
         End
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Listar cheques"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   11400
         TabIndex        =   3
         Top             =   360
         Width           =   1695
      End
      Begin MSComCtl2.DTPicker DTP_inicio 
         Height          =   375
         Left            =   840
         TabIndex        =   6
         Top             =   480
         Width           =   1455
         _ExtentX        =   2566
         _ExtentY        =   661
         _Version        =   393216
         Format          =   51576833
         CurrentDate     =   42103
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   5055
         Left            =   120
         TabIndex        =   7
         Top             =   1080
         Width           =   13095
         _ExtentX        =   23098
         _ExtentY        =   8916
         _Version        =   393216
      End
      Begin MSComCtl2.DTPicker DTP_hasta 
         Height          =   375
         Left            =   3120
         TabIndex        =   10
         Top             =   480
         Width           =   1455
         _ExtentX        =   2566
         _ExtentY        =   661
         _Version        =   393216
         Format          =   51576833
         CurrentDate     =   42103
      End
      Begin VB.Label Label5 
         Caption         =   "Encargado"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   6000
         TabIndex        =   16
         Top             =   600
         Width           =   855
      End
      Begin VB.Label Label4 
         Caption         =   "Factoring"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   9480
         TabIndex        =   13
         Top             =   1080
         Width           =   855
      End
      Begin VB.Label Label3 
         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            =   2520
         TabIndex        =   11
         Top             =   600
         Width           =   615
      End
      Begin VB.Label Label1 
         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            =   240
         TabIndex        =   8
         Top             =   600
         Width           =   615
      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            =   11880
      Picture         =   "Form_exportar_cheques.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   1
      Top             =   6840
      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            =   120
      Picture         =   "Form_exportar_cheques.frx":2AED8
      Style           =   1  'Graphical
      TabIndex        =   0
      Top             =   6840
      Width           =   1575
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   9
      Top             =   6480
      Width           =   13335
      _ExtentX        =   23521
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
End
Attribute VB_Name = "Form_exportar_cheques"
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 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"
                
'    var_sql = "SELECT num_gra AS grado,let_cur AS letra,des_ens AS nivel,inasistencias,asistencias,primera_validacion,ultima_validacion,nombre_usuario" _
'            & " From (SELECT num_gra,let_cur,des_ens FROM cursos c,codigos_ensenanza ce WHERE c.cod_ens = ce.cod_ens AND agno = " & agno & ") cur" _
'            & " Left Join" _
'            & " (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,MIN(hora) AS primera_validacion," _
'            & " MAX(hora) 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) AS asi" _
'            & " ON CONCAT(cur.num_gra,cur.let_cur,cur.des_ens) = CONCAT(asi.grado,asi.letra,asi.nivel)"
                
    If cb_encargado.Text = "" Then
    var_sql = "SELECT run_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,run_titular,nom_titular,tel_titular," _
            & "b.descripcion , cta_cte as Cuenta_Corriente, chq_serie, chq_numero, monto_chq, verificacion, banco_plaza," _
            & "DATE_FORMAT(chq_fecha,'%d-%M-%Y') as Fec_Venc, DATE_FORMAT(fec_ingreso,'%d-%M-%Y') as Fec_Ing,usuario_ingreso" _
            & " FROM fnza_cheques_recibidos fcr,apoderados a,fnza_cod_banco b" _
            & " Where fcr.run_apo = a.rut_apo And fcr.banco = b.banco" _
            & " AND fec_ingreso >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & " 00:00:00' AND fec_ingreso <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & " 23:59:59' order by fec_ingreso"
            'AND fcr.folio_chq NOT IN (SELECT folio_chq FROM fnza_chq_mvto)
            
    Else
    var_sql = "SELECT run_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,run_titular,nom_titular,tel_titular," _
            & "b.descripcion , cta_cte as Cuenta_Corriente, chq_serie, chq_numero, monto_chq, verificacion, banco_plaza," _
            & "DATE_FORMAT(chq_fecha,'%d-%M-%Y') as Fec_Venc, DATE_FORMAT(fec_ingreso,'%d-%M-%Y') as Fec_Ing,usuario_ingreso" _
            & " FROM fnza_cheques_recibidos fcr,apoderados a,fnza_cod_banco b" _
            & " Where fcr.run_apo = a.rut_apo And fcr.banco = b.banco" _
            & " AND fec_ingreso >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & " 00:00:00' AND fec_ingreso <= '" _
            & Format(DTP_hasta.Value, "yyyy-mm-dd") & " 23:59:59' and usuario_ingreso = '" & cb_encargado.Text & "'  order by fec_ingreso"
            ' AND fcr.folio_chq NOT IN (SELECT folio_chq FROM fnza_chq_mvto)
    End If
                
            '' " AND fec_ingreso >= '2015-05-05 00:00:00' AND fec_ingreso <= '2015-06-05 23:59:59'"
                
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
       ' msflex_alumnos.ColSel = 5
        var_sql = "DROP TABLE IF EXISTS temp_cheques_reporte;"
        conn.Execute var_sql
        If cb_encargado.Text = "" Then
        var_sql = "CREATE TABLE temp_cheques_reporte TYPE = MYISAM SELECT run_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,run_titular,nom_titular,tel_titular," _
            & "b.descripcion , cta_cte as Cuenta_Corriente, chq_serie, chq_numero, monto_chq, verificacion, banco_plaza," _
            & "DATE_FORMAT(chq_fecha,'%d-%M-%Y') as Fec_Venc,date_format(fec_ingreso,'%d-%m-%Y') as Fec_Ing,usuario_ingreso" _
            & " FROM fnza_cheques_recibidos fcr,apoderados a,fnza_cod_banco b" _
            & " Where fcr.run_apo = a.rut_apo And fcr.banco = b.banco" _
            & " AND fec_ingreso >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & " 00:00:00' AND fec_ingreso <= '" _
            & Format(DTP_hasta.Value, "yyyy-mm-dd") & " 23:59:59' order by fec_ingreso,apoderado,chq_numero"
            ' AND fcr.folio_chq NOT IN (SELECT folio_chq FROM fnza_chq_mvto)
        conn.Execute var_sql
        Else
        var_sql = "CREATE TABLE temp_cheques_reporte TYPE = MYISAM SELECT run_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS apoderado,run_titular,nom_titular,tel_titular," _
            & "b.descripcion , cta_cte as Cuenta_Corriente, chq_serie, chq_numero, monto_chq, verificacion, banco_plaza," _
            & "DATE_FORMAT(chq_fecha,'%d-%M-%Y') as Fec_Venc,date_format(fec_ingreso,'%d-%m-%Y') as Fec_Ing,usuario_ingreso" _
            & " FROM fnza_cheques_recibidos fcr,apoderados a,fnza_cod_banco b" _
            & " Where fcr.run_apo = a.rut_apo And fcr.banco = b.banco" _
            & " AND fec_ingreso >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & " 00:00:00' AND fec_ingreso <= '" _
            & Format(DTP_hasta.Value, "yyyy-mm-dd") & " 23:59:59' and usuario_ingreso = '" & cb_encargado.Text & "' order by fec_ingreso,apoderado,chq_numero"
            ' AND fcr.folio_chq NOT IN (SELECT folio_chq FROM fnza_chq_mvto)
        conn.Execute var_sql
        End If
    Else
       MsgBox "No se han ingresado cheques en el rango 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
            ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", MSFlexGrid1.TextMatrix(i, j))
        
            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 Command3_Click()
    Call LimpiaFormulas(c_r, 30)
    c_r.ReportFileName = App.Path & "\Rpt\rendicion_cheques.rpt"
'    cr_dj.Formulas(0) = "ci_alu = '" & txt_RutBusqueda & "'"
'    cr_dj.Formulas(1) = "establecimiento = '" & "SOC. EDUC. " & DevuelveCampo("establecimientos", "", "nom_est", "1 = 1", conn) & " LTDA.'"
'    cr_dj.Formulas(2) = "rut_establecimiento = '" & DevuelveCampo("establecimientos", "", "rut_est", "1 = 1", conn) & "'"
'    cr_dj.Formulas(3) = "direccioncolegio = '" & DevuelveCampo("establecimientos", "", "dir_est", "1 = 1", conn) & "'"
'    cr_dj.Formulas(4) = "numerocertificado= " & DevuelveCampo("fnza_dj", "", "round(max(nro_certificado) + 1) AS Numero", "1 = 1", conn) & ""
'    cr_dj.Formulas(5) = "ciudad = '" & Ciudad & "'"
'    cr_dj.Formulas(6) = "alumno = '" & DevuelveCampo("postulantes", "", "CONCAT(nom_alu, ' ',ape_pat_alu, ' ',ape_mat_alu)", "ci_alu = '" & txt_RutBusqueda & "'", conn) & "'"
'    cr_dj.Formulas(7) = "anomatricula = '" & DevuelveCampo("matriculas", "", "MAX(anno_mat)", "ci_alu = '" & txt_RutBusqueda & "'", conn) & "'"
'    cr_dj.Formulas(8) = "porcentaje = '" & DevuelveCampo("temp_rend", "", "IF(porcentaje = 's', 'SI', 'NO')", "ci_alu = '" & txt_RutBusqueda & "'", conn) & "'"
    
'    cr_dj.Formulas(3) = "anno = '" & Anno & "'"
'    cr_dj.Formulas(4) = "alumno = '" & devuelvecampo("postulantes", "", "CONCAT(nom_alu, ' ',ape_pat_alu, ' ',ape_mat_alu)", "ci_alu = '" & txt_RutBusqueda & "'", conn) & "'"
'    cr_dj.Formulas(5) = "curso = '" & devuelvecampo("alu_x_cur", "", "CONCAT(num_gra, ' ', let_cur)", "ci_alu = '" & txt_RutBusqueda & "' AND agno = '" & Anno & "'", conn) & "'"
'    cr_dj.Formulas(6) = "ensenanza = '" & devuelvecampo("alu_x_cur", "", "IF(cod_ens = 996, 'Otros Tipos (Pre Kinder)', IF(cod_ens = 010, 'Educación Parvularia', IF(cod_ens = 110, 'Educación General Básica','Educación Media H/C niños y jóvenes')))", "ci_alu = '" & txt_RutBusqueda & "' AND agno = '" & Anno & "'", conn) & "'"
'    cr_dj.Formulas(7) = "nombrecolegio = '" & devuelvecampo("establecimientos", "", "nom_est", "1 = 1", conn) & "'"
'    cr_dj.Formulas(9) = "comunacolegio = '" & devuelvecampo("establecimientos", "", "localidad_est", "1 = 1", conn) & "'"
'    cr_dj.Formulas(10) = "hora = '" & Time & "'"
    
    c_r.Destination = crptToWindow
    c_r.Action = 1
    
End Sub

Private Sub Command4_Click()
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim monto_cheques As Double
    Dim total_cheques As Long
    
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")

    ApExcel.Workbooks.Add
    
    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
        
    var_sql = "SELECT run_titular,nom_titular,b.descripcion AS banco,chq_numero,verificacion,banco_plaza,chq_fecha,cta_cte AS Cuenta_Corriente, monto_chq,'' AS FACTORING" _
            & " FROM fnza_cheques_recibidos fcr,apoderados a,fnza_cod_banco b" _
            & " Where fcr.run_apo = a.rut_apo And fcr.banco = b.banco" _
            & " AND fec_ingreso >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & " 00:00:00' AND fec_ingreso <= '" _
            & Format(DTP_hasta.Value, "yyyy-mm-dd") & " 23:59:59' and chq_fecha < '2018-12-31' order by fec_ingreso"
            
    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, 8).Font.Size = 12
    ApExcel.Cells(1, 9).Font.Size = 12
    ApExcel.Cells(1, 10).Font.Size = 12
    ApExcel.Cells(1, 11).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, 8).Font.Bold = True
    ApExcel.Cells(1, 9).Font.Bold = True
    ApExcel.Cells(1, 10).Font.Bold = True
    ApExcel.Cells(1, 11).Font.Bold = True
    
    ApExcel.Cells(1, 1).Formula = "N°"
    ApExcel.Cells(1, 2).Formula = "Rut Deudor"
    ApExcel.Cells(1, 3).Formula = "Nombre Deudor"
    ApExcel.Cells(1, 4).Formula = "Banco"
    ApExcel.Cells(1, 5).Formula = "# Cheque"
    ApExcel.Cells(1, 6).Formula = "Verificación"
    ApExcel.Cells(1, 7).Formula = "Banco y Plaza"
    ApExcel.Cells(1, 8).Formula = "Cuenta Cte."
    ApExcel.Cells(1, 9).Formula = "Vencimiento"
    ApExcel.Cells(1, 10).Formula = "Monto Docto"
    ApExcel.Cells(1, 11).Formula = "FACTORING"
        
    pgbar.Min = 0
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    total_boletas = 0
    Monto_boletas = 0
    While Not objRS.EOF
        'ApExcel.Cells(i, 3).Formula = Format(objRS("fecha_blta"), "yyyy/mm/dd")
        i = i + 1
        ApExcel.Cells(i, 1).Formula = i
        ApExcel.Cells(i, 2).Formula = objRS("run_titular")
        ApExcel.Cells(i, 3).Formula = objRS("nom_titular")
        ApExcel.Cells(i, 4).Formula = objRS("banco")
        ApExcel.Cells(i, 5).Formula = objRS("chq_numero")
        ApExcel.Cells(i, 6).Formula = objRS("verificacion")
        ApExcel.Cells(i, 7).Formula = objRS("banco_plaza")
        ApExcel.Cells(i, 8).Formula = objRS("Cuenta_Corriente")
        ApExcel.Cells(i, 9).Formula = Format(objRS("vencimiento"), "yyyy/mm/dd")
        ApExcel.Cells(i, 10).Formula = objRS("monto_chq")
        ApExcel.Cells(i, 11).Formula = objRS("FACTORING")
                
        monto_cheques = monto_cheques + objRS("monto_chq")
        ApExcel.Cells(i, 10).NumberFormat = "#,##0"
        total_cheques = total_cheques + 1
        
        pgbar.Value = i - 1

        objRS.MoveNext
    Wend
    i = i + 1
    
    ApExcel.Cells(i, 6).Formula = "Tot. cheques"
    ApExcel.Cells(i, 7).Formula = total_cheques
    ApExcel.Cells(i, 7).HorizontalAlignment = xlHAlignLeft
    ApExcel.Cells(i, 9).Formula = "Tot. monto"
    ApExcel.Cells(i, 10).Formula = monto_cheques
    ApExcel.Cells(i, 10).NumberFormat = "#,##0"
    
    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.Cells(i, 8).Font.Bold = True
    ApExcel.Cells(i, 9).Font.Bold = True
    ApExcel.Cells(i, 10).Font.Bold = True
    ApExcel.Cells(i, 11).Font.Bold = True
            
    ApExcel.Columns("A").ColumnWidth = 10
    ApExcel.Columns("B").ColumnWidth = 15
    ApExcel.Columns("C").ColumnWidth = 40
    ApExcel.Columns("D").ColumnWidth = 40
    ApExcel.Columns("E").ColumnWidth = 15
    ApExcel.Columns("F").ColumnWidth = 15
    ApExcel.Columns("G").ColumnWidth = 15
    ApExcel.Columns("H").ColumnWidth = 15
    ApExcel.Columns("I").ColumnWidth = 15
    ApExcel.Columns("J").ColumnWidth = 15
    ApExcel.Columns("K").ColumnWidth = 15
            
    ApExcel.Visible = True
    ApExcel.Range("a1:f1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
    
    objRS.Close

End Sub

Private Sub Form_Load()
    DTP_inicio.Value = Date
    DTP_hasta.Value = Date
    
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    var_sql = "SELECT cod_fac,factoring as factoring FROM fnza_factoring"
    objRS.Open var_sql, conn
    cb_factoring.Clear
    While Not objRS.EOF
        cb_factoring.Tag = objRS("cod_fac")
        cb_factoring.AddItem objRS("factoring")
        objRS.MoveNext
    Wend
    objRS.Close
    
    var_sql = "SELECT DISTINCT usuario_ingreso FROM fnza_cheques_recibidos WHERE usuario_ingreso <> ''"
    objRS.Open var_sql, conn
    cb_encargado.Clear
    cb_encargado.AddItem ""
    While Not objRS.EOF
        cb_encargado.AddItem objRS("usuario_ingreso")
        cb_encargado.Text = objRS("usuario_ingreso")
        objRS.MoveNext
    Wend
    objRS.Close
End Sub




