VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.2#0"; "MSCOMCTL.OCX"
Begin VB.Form Form_Listado_Pagos_PresencialesV2 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Listado Pagos Presenciales"
   ClientHeight    =   4470
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   7140
   Icon            =   "Form_Listado_Pagos_Presenciales.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   4470
   ScaleWidth      =   7140
   Begin VB.Frame frame_rango 
      Caption         =   "Rango de meses"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1335
      Left            =   3720
      TabIndex        =   12
      Top             =   1440
      Width           =   2895
      Begin VB.ComboBox cb_hasta 
         Height          =   315
         Left            =   1440
         TabIndex        =   16
         Top             =   840
         Width           =   1335
      End
      Begin VB.ComboBox cb_desde 
         Height          =   315
         ItemData        =   "Form_Listado_Pagos_Presenciales.frx":2A8B2
         Left            =   1440
         List            =   "Form_Listado_Pagos_Presenciales.frx":2A8B4
         TabIndex        =   15
         Top             =   360
         Width           =   1335
      End
      Begin VB.Label Label5 
         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            =   120
         TabIndex        =   14
         Top             =   840
         Width           =   615
      End
      Begin VB.Label Label4 
         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        =   13
         Top             =   360
         Width           =   615
      End
   End
   Begin VB.Frame frame_acumulado 
      Caption         =   "Acumulado al"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   975
      Left            =   3720
      TabIndex        =   9
      Top             =   1440
      Width           =   2895
      Begin MSComCtl2.DTPicker dtp_fecha 
         Height          =   375
         Left            =   1440
         TabIndex        =   10
         Top             =   360
         Width           =   1335
         _ExtentX        =   2355
         _ExtentY        =   661
         _Version        =   393216
         Format          =   51249153
         CurrentDate     =   45428
      End
      Begin VB.Label Label2 
         Caption         =   "Fecha corte"
         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        =   11
         Top             =   480
         Width           =   1215
      End
   End
   Begin VB.Frame Frame2 
      Caption         =   "Seleccione anualidad"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   975
      Left            =   1800
      TabIndex        =   6
      Top             =   120
      Width           =   3255
      Begin VB.ComboBox cb_agno 
         Height          =   315
         Left            =   1440
         TabIndex        =   7
         Top             =   360
         Width           =   1335
      End
      Begin VB.Label Label1 
         Caption         =   "Anualidad"
         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        =   8
         Top             =   360
         Width           =   1215
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "Seleccione una opción"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1335
      Left            =   240
      TabIndex        =   3
      Top             =   1440
      Width           =   2895
      Begin VB.OptionButton opt_rango 
         Caption         =   "Rango de meses"
         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        =   5
         Top             =   720
         Width           =   1815
      End
      Begin VB.OptionButton opt_acumulado 
         Caption         =   "Acumulado"
         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        =   4
         Top             =   360
         Width           =   1935
      End
   End
   Begin MSComctlLib.ProgressBar pgbar2 
      Height          =   255
      Left            =   240
      TabIndex        =   2
      Top             =   4080
      Width           =   6495
      _ExtentX        =   11456
      _ExtentY        =   450
      _Version        =   393216
      Appearance      =   1
   End
   Begin MSComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   240
      TabIndex        =   1
      Top             =   3720
      Width           =   6495
      _ExtentX        =   11456
      _ExtentY        =   450
      _Version        =   393216
      Appearance      =   1
   End
   Begin VB.CommandButton cmd_generar 
      Caption         =   "Generar"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   2520
      TabIndex        =   0
      Top             =   3120
      Width           =   1815
   End
End
Attribute VB_Name = "Form_Listado_Pagos_PresencialesV2"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub cmd_generar_Click()
    Dim QRY As String
    Dim RC As ADODB.Recordset
    Dim xlApp As Object, xlBook As Object, xlSheet As Object
    Dim i As Integer
    Dim mes_desde As Integer, mes_hasta As Integer
    
    ' Verificar que se haya seleccionado una opción
    If Not (opt_rango.Value Or opt_acumulado.Value) Then
        MsgBox "Debe seleccionar un tipo de generación (Rango o Acumulado)", vbExclamation, "Error"
        Exit Sub
    End If
    
    If opt_rango.Value = True Then
        ' Formatea fecha por rango
        Select Case cb_desde.Text
            Case "ENERO"
                mes_desde = "01"
            Case "FEBRERO"
                mes_desde = "02"
            Case "MARZO"
                mes_desde = "03"
            Case "ABRIL"
                mes_desde = "04"
            Case "MAYO"
                mes_desde = "05"
            Case "JUNIO"
                mes_desde = "06"
            Case "JULIO"
                mes_desde = "07"
            Case "AGOSTO"
                mes_desde = "08"
            Case "SEPTIEMBRE"
                mes_desde = "09"
            Case "OCTUBRE"
                mes_desde = "10"
            Case "NOVIEMBRE"
                mes_desde = "11"
            Case "DICIEMBRE"
                mes_desde = "12"
        End Select
    
        Select Case cb_hasta.Text
            Case "ENERO"
                mes_hasta = "01"
            Case "FEBRERO"
                mes_hasta = "02"
            Case "MARZO"
                mes_hasta = "03"
            Case "ABRIL"
                mes_hasta = "04"
            Case "MAYO"
                mes_hasta = "05"
            Case "JUNIO"
                mes_hasta = "06"
            Case "JULIO"
                mes_hasta = "07"
            Case "AGOSTO"
                mes_hasta = "08"
            Case "SEPTIEMBRE"
                mes_hasta = "09"
            Case "OCTUBRE"
                mes_hasta = "10"
            Case "NOVIEMBRE"
                mes_hasta = "11"
            Case "DICIEMBRE"
                mes_hasta = "12"
        End Select
    
        dd_desde = "01"
        
        If (mes_hasta = "01" Or mes_hasta = "03" Or mes_hasta = "05" Or mes_hasta = "07" Or mes_hasta = "08" Or mes_hasta = 10 Or mes_hasta = 12) Then
            dd_hasta = 31
        Else
            If mes_hasta = "02" Then
                dd_hasta = 28
            Else
                dd_hasta = 30
            End If
        End If
       
        ' fecha_desde = dd_desde & IIf(Len(cb_desde.Text) < 10, "0" & cb_desde.Text, cb_desde.Text) & cb_agno_rango.Text
        ' fecha_hasta = dd_hasta & IIf(Len(cb_hasta.Text) < 10, "0" & cb_hasta.Text, cb_hasta.Text) & cb_agno_rango.Text
        fecha_desde = dd_desde & "-" & mes_desde & "-" & cb_agno.Text
        fecha_hasta = dd_hasta & "-" & mes_hasta & "-" & cb_agno.Text
    
    End If

    
    ' Conectar con la base de datos
    Set RC = New ADODB.Recordset
    
    ' Construcción de la consulta SQL según la opción seleccionada
    If opt_rango.Value Then
        ' Validar que los combobox tengan valores
        If cb_desde.ListIndex = -1 Or cb_hasta.ListIndex = -1 Then
            MsgBox "Debe seleccionar un rango de meses", vbExclamation, "Error"
            Exit Sub
        End If
        
        ' Convertir nombres de meses a números
        mes_desde = MesANumero(cb_desde.Text)
        mes_hasta = MesANumero(cb_hasta.Text)
        
        QRY = "SELECT * FROM fnza_pagos_manuales WHERE MONTH(fecha_pago) BETWEEN " & mes_desde & " AND " & mes_hasta & " AND agno = 2025"
    ElseIf opt_acumulado.Value Then
        ' Validar que la fecha de corte no esté vacía
        If Trim(dtp_fecha.Value) = "" Then
            MsgBox "Debe seleccionar una fecha de corte", vbExclamation, "Error"
            Exit Sub
        End If
        
        QRY = "SELECT * FROM fnza_pagos_manuales WHERE fecha_pago <= '" & Format(dtp_fecha.Value, "yyyy-mm-dd") & "'"
    End If
    
    ' Ejecutar la consulta
    On Error GoTo ErrorHandler
    RC.Open QRY, conn, adOpenStatic, adLockReadOnly
    If RC.EOF Then
        MsgBox "No hay datos para exportar", vbInformation, "Sin Datos"
        RC.Close
        Exit Sub
    End If
    
    ' Crear Excel y hoja de trabajo
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    
    ' Agregar encabezados
    For i = 0 To RC.Fields.count - 1
        xlSheet.Cells(1, i + 1).Value = RC.Fields(i).Name
        xlSheet.Cells(1, i + 1).Font.Bold = True
    Next i
    
    ' Agregar datos
    xlSheet.Range("A2").CopyFromRecordset RC
    
    ' Formatear hoja
    xlSheet.Cells.EntireColumn.AutoFit
    xlApp.Visible = True
    
    ' Cerrar conexión
    RC.Close
    Exit Sub
    
ErrorHandler:
    MsgBox "Error al generar el archivo Excel: " & Err.Description, vbCritical, "Error"
    If Not RC Is Nothing Then RC.Close
    
End Sub

Private Sub Form_Load()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    For i = 2023 To Year(Date) + 1
        cb_agno.AddItem i
        ' cb_agno_rango.AddItem I
    Next i
    cb_agno.Text = Year(Date)
    dtp_fecha.Value = Date
        
    var_sql = "SELECT num_mes,ucase(mes) as mes FROM meses ORDER BY num_mes"
    objRS.Open var_sql, conn
    While Not objRS.EOF
        cb_desde.AddItem objRS("mes")
        cb_hasta.AddItem objRS("mes")
        objRS.MoveNext
    Wend
    objRS.Close
    
    frame_acumulado.Visible = False
    frame_rango.Visible = False
End Sub


Private Sub opt_acumulado_Click()
    frame_acumulado.Visible = True
    frame_rango.Visible = False
End Sub

Private Sub opt_rango_Click()
    frame_rango.Visible = True
    frame_acumulado.Visible = False
End Sub

' Función para convertir nombre de mes a número
Private Function MesANumero(ByVal nombreMes As String) As Integer
    Select Case LCase(nombreMes)
        Case "enero": MesANumero = 1
        Case "febrero": MesANumero = 2
        Case "marzo": MesANumero = 3
        Case "abril": MesANumero = 4
        Case "mayo": MesANumero = 5
        Case "junio": MesANumero = 6
        Case "julio": MesANumero = 7
        Case "agosto": MesANumero = 8
        Case "septiembre": MesANumero = 9
        Case "octubre": MesANumero = 10
        Case "noviembre": MesANumero = 11
        Case "diciembre": MesANumero = 12
        Case Else: MesANumero = 0
    End Select
End Function
