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 = "{00025600-0000-0000-C000-000000000046}#4.6#0"; "crystl32.ocx"
Begin VB.Form Form_Ingresos_Consulta 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Ingresos"
   ClientHeight    =   5055
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   7125
   BeginProperty Font 
      Name            =   "Arial"
      Size            =   8.25
      Charset         =   0
      Weight          =   400
      Underline       =   0   'False
      Italic          =   0   'False
      Strikethrough   =   0   'False
   EndProperty
   Icon            =   "Form_Ingresos_Consulta.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   5055
   ScaleWidth      =   7125
   Begin VB.CommandButton cmd_consultar 
      Caption         =   "Consultar"
      Height          =   855
      Left            =   5640
      Picture         =   "Form_Ingresos_Consulta.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   9
      Top             =   240
      Width           =   1335
   End
   Begin VB.CommandButton cmd_imprimir 
      Caption         =   "Imprimir"
      Height          =   855
      Left            =   5640
      Picture         =   "Form_Ingresos_Consulta.frx":2AF3B
      Style           =   1  'Graphical
      TabIndex        =   8
      Top             =   1200
      Width           =   1335
   End
   Begin VB.CommandButton cmd_salir 
      Caption         =   "Salir"
      Height          =   855
      Left            =   5640
      Picture         =   "Form_Ingresos_Consulta.frx":2B498
      Style           =   1  'Graphical
      TabIndex        =   7
      Top             =   2160
      Width           =   1335
   End
   Begin VB.Frame Frame2 
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   3495
      Left            =   120
      TabIndex        =   1
      Top             =   1440
      Width           =   5415
      Begin MSFlexGridLib.MSFlexGrid grid 
         Height          =   3135
         Left            =   120
         TabIndex        =   2
         Top             =   240
         Width           =   5175
         _ExtentX        =   9128
         _ExtentY        =   5530
         _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
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "Seleción de periodo"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1215
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   5415
      Begin Crystal.CrystalReport cr_ingresos 
         Left            =   3360
         Top             =   120
         _ExtentX        =   741
         _ExtentY        =   741
         _Version        =   262150
      End
      Begin MSComCtl2.DTPicker dtp_termino 
         Height          =   375
         Left            =   3840
         TabIndex        =   6
         Top             =   480
         Width           =   1335
         _ExtentX        =   2355
         _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          =   21364737
         CurrentDate     =   40652
      End
      Begin MSComCtl2.DTPicker dtp_inicio 
         Height          =   375
         Left            =   1080
         TabIndex        =   5
         Top             =   480
         Width           =   1335
         _ExtentX        =   2355
         _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          =   21364737
         CurrentDate     =   40652
      End
      Begin VB.Label Label2 
         Caption         =   "Fecha Termino"
         ForeColor       =   &H00000000&
         Height          =   255
         Left            =   2640
         TabIndex        =   4
         Top             =   600
         Width           =   1095
      End
      Begin VB.Label Label1 
         Caption         =   "Fecha Inicio"
         ForeColor       =   &H00000000&
         Height          =   255
         Left            =   120
         TabIndex        =   3
         Top             =   480
         Width           =   975
      End
   End
End
Attribute VB_Name = "Form_Ingresos_Consulta"
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_consultar_Click()
    Dim REC As ADODB.Recordset
    Set REC = New ADODB.Recordset
    Dim var_sql As String
    var_sql = "drop table if exists temp_convenios"
    conn.Execute var_sql
    var_sql = "create table temp_convenios type = MyISAM" _
            & " select date_format(fecha_blta,'%m') as mes_boleta,if(date_format(fecha_blta,'%m')=1,'Enero'," _
            & "if(date_format(fecha_blta,'%m')=2,'Febrero',if(date_format(fecha_blta,'%m')=3,'Marzo',if(date_format(fecha_blta,'%m')=4,'Abril',if(date_format(fecha_blta,'%m')= 5,'Mayo'," _
            & "if(date_format(fecha_blta,'%m')=6,'Junio',if(date_format(fecha_blta,'%m') = 7 ,'Julio',if(date_format(fecha_blta,'%m')=8,'Agosto',if(date_format(fecha_blta,'%m')=9,'Septiembre'," _
            & "if(date_format(fecha_blta,'%m')=10,'Octubre',if(date_format(fecha_blta,'%m')=11,'Noviembre','Diciembre'))))))))))) as mm_boleta" _
            & ",date_format(fecha_blta,'%Y') as anno_boleta," _
            & "folio_blta_fisico,monto_blta,monto_pago,fdm.mes,monto_abono," _
            & "if(mes=1,'Enero',if(mes=2,'Febrero',if(mes=3,'Marzo',if(mes=4,'Abril',if(mes = 5,'Mayo'," _
            & "if(mes=6,'Junio',if(mes = 7 ,'Julio',if(mes=8,'Agosto',if(mes=9,'Septiembre'," _
            & "if(mes=10,'Octubre',if(mes=11,'Noviembre','Diciembre'))))))))))) as mes_palabra,concepto,agno" _
            & " from fnza_boletas fb,fnza_dtll_mvto fv,fnza_movimientos_mensualidad fmm,fnza_detalle_mensualidad fdm" _
            & " Where fb.folio_blta = FV.folio_blta And FV.folio_mvto = fmm.folio_mvto And fmm.folio_dtll = fdm.folio_dtll" _
            & " and fecha_blta >= '" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' and fecha_blta <= '" _
            & Format(dtp_termino.Value, "yyyy-mm-dd") & "' and estado_blta = 'aprb'"
    conn.Execute var_sql
    var_sql = "select anno_boleta as 'Año',mm_boleta as 'Mes',format(sum(monto_pago),0) as 'Monto' from temp_convenios" _
            & " group by anno_boleta,mes_boleta"
    REC.Open var_sql, conn
    If Not REC.EOF Then
        Call Rec2Grid(REC, grid, True)
    End If
End Sub

Private Sub cmd_imprimir_Click()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql, est, dir, conv As String
    var_sql = "SELECT nom_est,dir_est FROM establecimientos"
    objRS.Open var_sql, conn
    var_sql = "drop table if exists temp_convenios;"
    conn.Execute var_sql
    If Not objRS.EOF Then
        est = objRS("nom_est")
        dir = objRS("dir_est")
    End If
    objRS.Close
    
    var_sql = "drop table if exists temp_convenios"
    conn.Execute var_sql
    var_sql = "create table temp_convenios type = MyISAM" _
            & " select date_format(fecha_blta,'%m') as mes_boleta,if(date_format(fecha_blta,'%m')=1,'Enero'," _
            & "if(date_format(fecha_blta,'%m')=2,'Febrero',if(date_format(fecha_blta,'%m')=3,'Marzo',if(date_format(fecha_blta,'%m')=4,'Abril',if(date_format(fecha_blta,'%m')= 5,'Mayo'," _
            & "if(date_format(fecha_blta,'%m')=6,'Junio',if(date_format(fecha_blta,'%m') = 7 ,'Julio',if(date_format(fecha_blta,'%m')=8,'Agosto',if(date_format(fecha_blta,'%m')=9,'Septiembre'," _
            & "if(date_format(fecha_blta,'%m')=10,'Octubre',if(date_format(fecha_blta,'%m')=11,'Noviembre','Diciembre'))))))))))) as mm_boleta" _
            & ",date_format(fecha_blta,'%Y') as anno_boleta," _
            & "folio_blta_fisico,monto_blta,monto_pago,fdm.mes,monto_abono," _
            & "if(mes=1,'Enero',if(mes=2,'Febrero',if(mes=3,'Marzo',if(mes=4,'Abril',if(mes = 5,'Mayo'," _
            & "if(mes=6,'Junio',if(mes = 7 ,'Julio',if(mes=8,'Agosto',if(mes=9,'Septiembre'," _
            & "if(mes=10,'Octubre',if(mes=11,'Noviembre','Diciembre'))))))))))) as mes_palabra,concepto,agno" _
            & " from fnza_boletas fb,fnza_dtll_mvto fv,fnza_movimientos_mensualidad fmm,fnza_detalle_mensualidad fdm" _
            & " Where fb.folio_blta = FV.folio_blta And FV.folio_mvto = fmm.folio_mvto And fmm.folio_dtll = fdm.folio_dtll" _
            & " and fecha_blta >= '" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' and fecha_blta <= '" _
            & Format(dtp_termino.Value, "yyyy-mm-dd") & "' and estado_blta = 'aprb'"
    conn.Execute var_sql
         
    Call LimpiaFormulas(cr_ingresos, 30)
    cr_ingresos.ReportFileName = App.Path & "\Rpt\ingresos.rpt"
        
    cr_ingresos.Formulas(3) = "col = '" & est & "'"
    cr_ingresos.Formulas(4) = "dir = '" & dir & "'"
        
    cr_ingresos.Destination = crptToWindow
    
    cr_ingresos.Action = 1
End Sub

Private Sub cmd_Salir_Click()
    Unload Me
End Sub
