VERSION 5.00
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_informe_boletas_emitidas 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Informe boletas emitidas"
   ClientHeight    =   3015
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   4095
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   3015
   ScaleWidth      =   4095
   Begin ComctlLib.ProgressBar pgBar 
      Height          =   255
      Left            =   240
      TabIndex        =   5
      Top             =   2520
      Width           =   3615
      _ExtentX        =   6376
      _ExtentY        =   450
      _Version        =   327682
      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          =   855
      Left            =   720
      Picture         =   "Form_informe_boletas_emitidas.frx":0000
      Style           =   1  'Graphical
      TabIndex        =   0
      Top             =   1320
      Width           =   2535
   End
   Begin MSComCtl2.DTPicker dtp_inicio 
      Height          =   375
      Left            =   1800
      TabIndex        =   1
      Top             =   240
      Width           =   1575
      _ExtentX        =   2778
      _ExtentY        =   661
      _Version        =   393216
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Format          =   51642369
      CurrentDate     =   41030
   End
   Begin MSComCtl2.DTPicker dtp_fin 
      Height          =   375
      Left            =   1800
      TabIndex        =   2
      Top             =   840
      Width           =   1575
      _ExtentX        =   2778
      _ExtentY        =   661
      _Version        =   393216
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Format          =   51642369
      CurrentDate     =   41060
   End
   Begin VB.Label Label1 
      Caption         =   "Fecha inicial"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   600
      TabIndex        =   4
      Top             =   240
      Width           =   1095
   End
   Begin VB.Label Label2 
      Caption         =   "Fecha final"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   600
      TabIndex        =   3
      Top             =   840
      Width           =   1215
   End
End
Attribute VB_Name = "Form_informe_boletas_emitidas"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim Monto_boletas As Double
    Dim total_boletas As Long
    
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")
    ApExcel.Workbooks.Add
    'Poner Titulos
    'ApExcel.cells(1, 1).Formula = "INFORME DE BOLETAS EMITIDAS"
    
            
    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 * FROM (SELECT fecha_blta,'boleta' AS tipo_documento,folio_blta_fisico,SUM(monto_pago) AS monto,IF(estado_blta = 'aprb','vigente','nula') AS estado,descripcion AS forma_pago" _
            & " FROM fnza_boletas b,fnza_dtll_mvto n,fnza_movimientos_mensualidad m,fnza_cod_forma_pago fp" _
            & " Where b.folio_blta = n.folio_blta And n.folio_mvto = m.folio_mvto" _
            & " AND fecha_blta >= '" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' AND fecha_blta <= '" & Format(dtp_fin.Value, "yyyy-mm-dd") & "'" _
            & " AND m.tipo_pago = fp.forma_pago GROUP BY folio_blta_fisico" _
            & " Union" _
            & " SELECT fecha_ntcr,'nota credito' AS tipo_documento,folio_fisico_ntcr,(SUM(monto_pago) * - 1) AS monto,IF(estado_ntcr = 'aprb','vigente','nula') AS estado,'' AS forma_pago" _
            & " FROM fnza_ntcr nc,fnza_detalle_ntcr dnc" _
            & " Where nc.folio_ntcr = dnc.folio_ntcr And folio_fisico_ntcr > 0 And fecha_ntcr Is Not Null" _
            & " AND fecha_ntcr >= '" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' AND fecha_ntcr <= '" & Format(dtp_fin.Value, "yyyy-mm-dd") & "'" _
            & " GROUP BY folio_fisico_ntcr UNION" _
            & " SELECT fecha_blta,'boleta' AS tipo_documento,folio_blta_fisico,0 AS monto,IF(estado_blta = 'aprb','aprobada','nula') AS estado,'' AS forma_pago FROM fnza_boletas WHERE estado_blta = 'nula'" _
            & " AND run_apo IS NULL AND folio_blta NOT IN (SELECT folio_blta FROM fnza_dtll_mvto)" _
            & " AND folio_blta NOT IN (SELECT folio_blta FROM fnza_boletas WHERE estado_blta = 'aprb') AND fecha_blta >= '" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' AND fecha_blta <= '" _
            & Format(dtp_fin.Value, "yyyy-mm-dd") & "') as t order by fecha_blta,folio_blta_fisico"
            
    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, 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, 1).Formula = "Folio"
    ApExcel.Cells(1, 2).Formula = "Tipo Documento"
    ApExcel.Cells(1, 3).Formula = "Fecha"
    ApExcel.Cells(1, 4).Formula = "Monto"
    ApExcel.Cells(1, 5).Formula = "Estado"
    ApExcel.Cells(1, 6).Formula = "Forma Pago"
        
    pgbar.Min = 0
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    total_boletas = 0
    Monto_boletas = 0
    While Not objRS.EOF
        
        I = I + 1
        ApExcel.Cells(I, 1).Formula = objRS("folio_blta_fisico")
        ApExcel.Cells(I, 2).Formula = objRS("tipo_documento")
        ApExcel.Cells(I, 3).Formula = Format(objRS("fecha_blta"), "yyyy/mm/dd")
        If objRS("estado") = "nula" Then
            ApExcel.Cells(I, 4).Formula = 0
        Else
            ApExcel.Cells(I, 4).Formula = objRS("monto")
            Monto_boletas = Monto_boletas + objRS("monto")
            
        End If
        
        ApExcel.Cells(I, 4).NumberFormat = "#,##0"
        
        If objRS("tipo_documento") = "boleta" Then
            total_boletas = total_boletas + 1
        End If
        
        ApExcel.Cells(I, 5).Formula = objRS("estado")
        ApExcel.Cells(I, 6).Formula = objRS("forma_pago")
        
        ' Aplica Formula
        'ApExcel.cells(3, 4).Formula = "=B3-C3"
        ' Hace una Seleccion de celdas y pone bordes de Color
        
        pgbar.Value = I - 1
        'pgBar.Refresh
        objRS.MoveNext
    Wend
    I = I + 1
    
    ApExcel.Cells(I, 1).Formula = "Tot. boletas"
    ApExcel.Cells(I, 2).Formula = total_boletas
    ApExcel.Cells(I, 2).HorizontalAlignment = xlHAlignLeft
    ApExcel.Cells(I, 3).Formula = "Tot. monto"
    ApExcel.Cells(I, 4).Formula = Monto_boletas
    ApExcel.Cells(I, 4).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.Visible = True
    ApExcel.Range("a1:f1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
    
    objRS.Close

'    CrystalReport1.ReportFileName = App.Path & "\rpt\libro_ventas.rpt"
'
'    CrystalReport1.WindowState = crptMaximized
'    'CrystalReport1.Formulas(1) = "a = '" & menor & "'"
'    'CrystalReport1.Formulas(2) = "fecha = '" & mayor & "'"
'    CrystalReport1.Formulas(3) = "menor_ntcr = '" & menor & "'"
'    CrystalReport1.Formulas(4) = "mayor_ntcr = '" & Mayor & "'"
'    CrystalReport1.Formulas(5) = "monto_ntcr = '" & Monto & "'"
'    CrystalReport1.Formulas(6) = "nobecado_ntcr = '" & nobecado & "'"
'    CrystalReport1.Formulas(7) = "becado_ntcr = '" & becado & "'"
'    CrystalReport1.Formulas(8) = "excencion = '" & excencion & "'"
'
'   'cr_perso.WindowTitle = "Informe De Desarrollo Personal y Social"
''    cr_pre.ReplaceSelectionFormula ("")
'    'CrystalReport1.Formulas(1) = "establo = '" & nombre & "'"
''    cr_perso.Formulas(1) = "fecha = '" & Format(Now, "dd/mm/yyyy") & "'"
''    cr_perso.Formulas(2) = "hora = '" & Format(Now, "hh:mm AM/PM") & "'"
'    'cr_perso.ReplaceSelectionFormula ("{pre_eval_cab.cod_ens}='" & xcod & "' and {pre_eval_cab.num_gra}='" & CInt(xnum_gra) & "' and {pre_eval_cab.let_cur}='" & xletra & "' and {pre_eval_cab.agno}=" & str(xagno)) & Per
'    CrystalReport1.Destination = crptToWindow
'    CrystalReport1.Action = 1
End Sub

Private Sub Command2_Click()
Dim ApExcel As Variant
Set ApExcel = CreateObject("Excel.application")
' Hace que Excel se vea
ApExcel.Visible = True
'Agrega un nuevo Libro
ApExcel.Workbooks.Add
'Poner Titulos
ApExcel.Cells(1, 1).Formula = "INFORME DE BOLETAS EMITIDAS"
ApExcel.Cells(1, 1).Font.Size = 18
ApExcel.Cells(2, 2).Formula = "Debe"
ApExcel.Cells(2, 3).Formula = "Haber"
ApExcel.Cells(2, 4).Formula = "Saldo"
ApExcel.Cells(3, 2).Formula = 200
ApExcel.Cells(3, 3).Formula = 100
' Aplica Formula
ApExcel.Cells(3, 4).Formula = "=B3-C3"
' Hace una Seleccion de celdas y pone bordes de Color
ApExcel.Range("B3:D3").Borders.color = RGB(255, 0, 0)
Set ApExcel = Nothing
End Sub

Private Sub Form_Load()

dtp_inicio = Date
dtp_fin = Date

End Sub


