VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_Centralizacion_Softland 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Centralización Softland"
   ClientHeight    =   4590
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   3570
   Icon            =   "Form_Centralizacion_Softland.frx":0000
   LinkTopic       =   "Form2"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   4590
   ScaleWidth      =   3570
   Begin MSComDlg.CommonDialog CommonDialog1 
      Left            =   3480
      Top             =   2160
      _ExtentX        =   847
      _ExtentY        =   847
      _Version        =   393216
   End
   Begin VB.Frame Frame2 
      Caption         =   "Provision"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   3975
      Left            =   120
      TabIndex        =   9
      Top             =   120
      Width           =   3255
      Begin VB.ComboBox cb_archivo 
         Height          =   315
         Left            =   1440
         TabIndex        =   19
         Top             =   2160
         Width           =   1575
      End
      Begin VB.ComboBox cb_agno 
         Height          =   315
         Left            =   1440
         TabIndex        =   16
         Top             =   1560
         Width           =   1575
      End
      Begin VB.CommandButton Command2 
         Caption         =   "Generar CSV"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   855
         Left            =   360
         Picture         =   "Form_Centralizacion_Softland.frx":2A8B2
         Style           =   1  'Graphical
         TabIndex        =   10
         Top             =   2760
         Width           =   2535
      End
      Begin MSComCtl2.DTPicker DTPicker1 
         Height          =   375
         Left            =   1440
         TabIndex        =   11
         Top             =   360
         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     =   43374
      End
      Begin MSComCtl2.DTPicker DTPicker2 
         Height          =   375
         Left            =   1440
         TabIndex        =   12
         Top             =   960
         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     =   43374
      End
      Begin VB.Label Label7 
         Caption         =   "Archivo"
         Height          =   255
         Left            =   240
         TabIndex        =   18
         Top             =   2160
         Width           =   855
      End
      Begin VB.Label Label4 
         Caption         =   "Año"
         Height          =   255
         Left            =   240
         TabIndex        =   17
         Top             =   1560
         Width           =   975
      End
      Begin VB.Label Label6 
         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          =   375
         Left            =   240
         TabIndex        =   14
         Top             =   960
         Width           =   1335
      End
      Begin VB.Label Label5 
         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            =   240
         TabIndex        =   13
         Top             =   360
         Width           =   1095
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "Seleccione rango de fechas"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   3255
      Left            =   4200
      TabIndex        =   0
      Top             =   120
      Width           =   3255
      Begin VB.ComboBox Combo1 
         Height          =   315
         Left            =   1440
         TabIndex        =   8
         Text            =   "Combo1"
         Top             =   1560
         Width           =   1575
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Generar CSV"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   855
         Left            =   360
         Picture         =   "Form_Centralizacion_Softland.frx":2AE98
         Style           =   1  'Graphical
         TabIndex        =   1
         Top             =   2160
         Width           =   2535
      End
      Begin MSComCtl2.DTPicker dtp_inicio 
         Height          =   375
         Left            =   1440
         TabIndex        =   2
         Top             =   360
         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     =   43374
      End
      Begin MSComCtl2.DTPicker dtp_fin 
         Height          =   375
         Left            =   1440
         TabIndex        =   3
         Top             =   960
         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     =   43374
      End
      Begin VB.Label Label3 
         Caption         =   "Forma pago"
         Height          =   255
         Left            =   240
         TabIndex        =   7
         Top             =   1560
         Width           =   975
      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            =   240
         TabIndex        =   5
         Top             =   360
         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          =   375
         Left            =   240
         TabIndex        =   4
         Top             =   960
         Width           =   1335
      End
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   6
      Top             =   4200
      Width           =   3255
      _ExtentX        =   5741
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
   Begin ComctlLib.ProgressBar ProgressBar1 
      Height          =   255
      Left            =   4920
      TabIndex        =   15
      Top             =   3960
      Width           =   3255
      _ExtentX        =   5741
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
End
Attribute VB_Name = "Form_Centralizacion_Softland"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql As String
    Dim Archivo As String
    Dim ruta2 As String
    Dim I As Integer
    
    ruta2 = "C:\Centralizacion.txt"
    Crear_Archivo_Cobranza (ruta2)
    'Call AgregarRegistroCobranza(Registro, ruta2)
    
    CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    If CODBC = "HUE001" Then
    
    ''  Haber Efectivo
    var_sql = "SELECT cta_h,SUM(monto_pago) AS monto,'' AS C,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & "'' AS Q,'' AS R,'' AS S,'' AS T,'' AS U,'' AS V,'' AS W,'' AS X,'' AS Y" _
            & " 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 AND m.tipo_pago = 'ctdo' GROUP BY folio_blta_fisico"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
    Archivo = ""
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    I = 0
    While Not objRS.EOF
        Archivo = Archivo & objRS(0) & "," & objRS(1) & "," & objRS(2) & "," & objRS(3) & "," & objRS(4) & "," & objRS(5) & "," & objRS(6) & "," & objRS(7) & "," & objRS(8) & "," _
        & objRS(9) & "," & objRS(10) & "," & objRS(11) & "," & objRS(12) & "," & objRS(13) & "," & objRS(14) & "," & objRS(15) & "," & objRS(16) & "," _
        & objRS(17) & "," & objRS(18) & "," & objRS(19) & "," & objRS(20) & "," & objRS(21) & "," & objRS(22) & "," & objRS(23) & "," & objRS(24)
        Call AgregarRegistroCobranza(Archivo, ruta2)
        Archivo = ""
        objRS.MoveNext
        I = I + 1
        pgbar.Value = I
    Wend
    End If
    objRS.Close
    
    '' Debe Efectivo
    var_sql = "SELECT cta_d,'' AS B,SUM(monto_pago) AS monto,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & "'' AS Q,'' AS R,MID(b.run_apo,1,LENGTH(b.run_apo)-2) AS codigo_auxiliar,tipo_conciliacion,b.folio_blta_fisico,fecha_blta,fecha_blta,'B6' AS tipo_documento_referencia,b.folio_blta_fisico" _
            & " 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 AND m.tipo_pago = 'ctdo' GROUP BY folio_blta_fisico "
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
    Archivo = ""
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    I = 0
    While Not objRS.EOF
        Archivo = Archivo & objRS(0) & "," & objRS(1) & "," & objRS(2) & "," & objRS(3) & "," & objRS(4) & "," & objRS(5) & "," & objRS(6) & "," & objRS(7) & "," & objRS(8) & "," _
        & objRS(9) & "," & objRS(10) & "," & objRS(11) & "," & objRS(12) & "," & objRS(13) & "," & objRS(14) & "," & objRS(15) & "," & objRS(16) & "," _
        & objRS(17) & "," & objRS(18) & "," & objRS(19) & "," & objRS(20) & "," & Replace(objRS(21), "-", "/") & "," & Replace(objRS(22), "-", "/") & "," & objRS(23) & "," & objRS(24)
        Call AgregarRegistroCobranza(Archivo, ruta2)
        Archivo = ""
        objRS.MoveNext
        I = I + 1
        pgbar.Value = I
    Wend
    End If
    objRS.Close
    
    '' Haber trans,cred,debito
    var_sql = "SELECT cta_h,SUM(monto_pago) AS monto,'' AS C,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & "tipo_conciliacion,nro_conciliacion,'' AS S,'' AS T,'' AS U,'' AS V,'' AS W,'' AS X,'' AS Y" _
            & " 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 AND (m.tipo_pago = 'tran' OR m.tipo_pago = 'tcre' OR m.tipo_pago = 'tdeb')" _
            & " GROUP BY folio_blta_fisico"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
    Archivo = ""
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    I = 0
    While Not objRS.EOF
        Archivo = Archivo & objRS(0) & "," & objRS(1) & "," & objRS(2) & "," & objRS(3) & "," & objRS(4) & "," & objRS(5) & "," & objRS(6) & "," & objRS(7) & "," & objRS(8) & "," _
        & objRS(9) & "," & objRS(10) & "," & objRS(11) & "," & objRS(12) & "," & objRS(13) & "," & objRS(14) & "," & objRS(15) & "," & objRS(16) & "," _
        & objRS(17) & "," & objRS(18) & "," & objRS(19) & "," & objRS(20) & "," & objRS(21) & "," & objRS(22) & "," & objRS(23) & "," & objRS(24)
        Call AgregarRegistroCobranza(Archivo, ruta2)
        Archivo = ""
        objRS.MoveNext
        I = I + 1
        pgbar.Value = I
    Wend
    End If
    objRS.Close
    
   '' Debe trans,cred,debito
    var_sql = "SELECT cta_d,'' AS B,SUM(monto_pago) AS monto,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & "'' AS Q,'' AS R,MID(b.run_apo,1,LENGTH(b.run_apo)-2) AS codigo_auxiliar,tipo_conciliacion,nro_conciliacion,fecha_blta,fecha_blta,'B6' AS tipo_documento_referencia,b.folio_blta_fisico" _
            & " 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 AND (m.tipo_pago = 'tran' OR m.tipo_pago = 'tcre' OR m.tipo_pago = 'tdeb')" _
            & " GROUP BY folio_blta_fisico "
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
    Archivo = ""
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    I = 0
    While Not objRS.EOF
        Archivo = Archivo & objRS(0) & "," & objRS(1) & "," & objRS(2) & "," & objRS(3) & "," & objRS(4) & "," & objRS(5) & "," & objRS(6) & "," & objRS(7) & "," & objRS(8) & "," _
        & objRS(9) & "," & objRS(10) & "," & objRS(11) & "," & objRS(12) & "," & objRS(13) & "," & objRS(14) & "," & objRS(15) & "," & objRS(16) & "," _
        & objRS(17) & "," & objRS(18) & "," & objRS(19) & "," & objRS(20) & "," & Replace(objRS(21), "-", "/") & "," & Replace(objRS(22), "-", "/") & "," & objRS(23) & "," & objRS(24)
        Call AgregarRegistroCobranza(Archivo, ruta2)
        Archivo = ""
        objRS.MoveNext
        I = I + 1
        pgbar.Value = I
    Wend
    End If
    objRS.Close
    
    '' Haber cheque al dia
    var_sql = "SELECT cta_h,IF(monto > monto_chq,monto_chq,monto) AS monto,'' AS C,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & "'' AS Q,'' AS R,'' AS S,'' AS T,'' AS U,'' AS V,'' AS W,'' AS X,'' AS Y From " _
            & " (SELECT cta_h,'' AS B,monto_pago AS monto,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & "'' AS Q,'' AS R,b.run_apo AS codigo_auxiliar,tipo_conciliacion,b.folio_blta_fisico,fecha_blta,fecha_blta,'B6' AS tipo_documento_referencia,b.folio_blta_fisico,n.folio_mvto,b.run_apo,b.run_alu" _
            & " 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 AND (m.tipo_pago = 'chqe')" _
            & ") AS b LEFT JOIN (SELECT fcm.folio_mvto,chq_numero,fcm.run_apo,fcm.run_alu,monto_chq FROM fnza_cheques_recibidos fcr,fnza_chq_mvto fcm WHERE fcr.folio_chq = fcm.folio_chq AND YEAR(chq_fecha) > 2017) ch" _
            & " ON b.folio_mvto = ch.folio_mvto AND b.run_apo = ch.run_apo AND b.run_alu = b.run_alu"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
    Archivo = ""
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    I = 0
    While Not objRS.EOF
        Archivo = Archivo & objRS(0) & "," & objRS(1) & "," & objRS(2) & "," & objRS(3) & "," & objRS(4) & "," & objRS(5) & "," & objRS(6) & "," & objRS(7) & "," & objRS(8) & "," _
        & objRS(9) & "," & objRS(10) & "," & objRS(11) & "," & objRS(12) & "," & objRS(13) & "," & objRS(14) & "," & objRS(15) & "," & objRS(16) & "," _
        & objRS(17) & "," & objRS(18) & "," & objRS(19) & "," & objRS(20) & "," & objRS(21) & "," & objRS(22) & "," & objRS(23) & "," & objRS(24)
        Call AgregarRegistroCobranza(Archivo, ruta2)
        Archivo = ""
        objRS.MoveNext
        I = I + 1
        pgbar.Value = I
    Wend
    End If
    objRS.Close
    
        '' Debe cheque al dia
    var_sql = "SELECT cta_d,B,IF(monto > monto_chq,monto_chq,monto) AS monto,descripcion_movimiento,E,F,G,H,I,J,K,L,M,N,O,P," _
            & "Q,R,MID(codigo_auxiliar,1,LENGTH(codigo_auxiliar)-2),tipo_conciliacion,IF(chq_numero IS NULL,'No Ingresado',chq_numero),fecha_blta,fecha_blta,tipo_documento_referencia,b.folio_blta_fisico,monto_chq" _
            & " From" _
            & " (SELECT cta_d,'' AS B,monto_pago AS monto,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & " '' AS Q,'' AS R,b.run_apo AS codigo_auxiliar,tipo_conciliacion,b.folio_blta_fisico,fecha_blta,fecha_blta,'B6' AS tipo_documento_referencia,b.folio_blta_fisico,n.folio_mvto,b.run_apo,b.run_alu" _
            & " 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 AND (m.tipo_pago = 'chqe')" _
            & " ) AS b LEFT JOIN (SELECT fcm.folio_mvto,chq_numero,fcm.run_apo,fcm.run_alu,monto_chq FROM fnza_cheques_recibidos fcr,fnza_chq_mvto fcm WHERE fcr.folio_chq = fcm.folio_chq AND YEAR(chq_fecha) > 2017) ch" _
            & " ON b.folio_mvto = ch.folio_mvto AND b.run_apo = ch.run_apo AND b.run_alu = b.run_alu"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
    Archivo = ""
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    I = 0
    While Not objRS.EOF
        Archivo = Archivo & objRS(0) & "," & objRS(1) & "," & objRS(2) & "," & objRS(3) & "," & objRS(4) & "," & objRS(5) & "," & objRS(6) & "," & objRS(7) & "," & objRS(8) & "," _
        & objRS(9) & "," & objRS(10) & "," & objRS(11) & "," & objRS(12) & "," & objRS(13) & "," & objRS(14) & "," & objRS(15) & "," & objRS(16) & "," _
        & objRS(17) & "," & objRS(18) & "," & objRS(19) & "," & objRS(20) & "," & Replace(objRS(21), "-", "/") & "," & Replace(objRS(22), "-", "/") & "," & objRS(23) & "," & objRS(24)
        Call AgregarRegistroCobranza(Archivo, ruta2)
        Archivo = ""
        objRS.MoveNext
        I = I + 1
        pgbar.Value = I
    Wend
    End If
    objRS.Close
    
        '' Haber cheque a fecha
    var_sql = "SELECT '1-01-05-01',monto_chq,'' AS C,'CHEQUE A FECHA NOMBRE APODERADO','' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & "'' AS Q,'' AS R,MID(run_apo,1,LENGTH(run_apo)-2),'CF' AS Tipo_Documento,chq_numero,DATE_FORMAT(fec_ingreso,'%d-%m-%Y'),chq_fecha,'CF' AS tipo_docu_refe,chq_numero" _
            & " From fnza_cheques_recibidos" _
            & " WHERE fec_ingreso >= '" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' AND fec_ingreso <= '" & Format(dtp_fin.Value, "yyyy-mm-dd") & "' AND folio_chq NOT IN (SELECT folio_chq FROM fnza_chq_mvto)"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
    Archivo = ""
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    I = 0
    While Not objRS.EOF
        Archivo = Archivo & objRS(0) & "," & objRS(1) & "," & objRS(2) & "," & objRS(3) & "," & objRS(4) & "," & objRS(5) & "," & objRS(6) & "," & objRS(7) & "," & objRS(8) & "," _
        & objRS(9) & "," & objRS(10) & "," & objRS(11) & "," & objRS(12) & "," & objRS(13) & "," & objRS(14) & "," & objRS(15) & "," & objRS(16) & "," _
        & objRS(17) & "," & objRS(18) & "," & objRS(19) & "," & objRS(20) & "," & Replace(objRS(21), "-", "/") & "," & Replace(objRS(22), "-", "/") & "," & objRS(23) & "," & objRS(24)
        Call AgregarRegistroCobranza(Archivo, ruta2)
        Archivo = ""
        objRS.MoveNext
        I = I + 1
        pgbar.Value = I
    Wend
    End If
    objRS.Close
    
    '' Debe cheque a fecha
    var_sql = "SELECT '2-10-13-04','' AS B,monto_chq,'CHEQUE A FECHA NOMBRE APODERADO','' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
            & "'' AS Q,'' AS R,MID(run_apo,1,LENGTH(run_apo)-2),'' AS T,'' AS U,'' AS V,'' AS W,'' AS X,'' AS Y" _
            & " From fnza_cheques_recibidos" _
            & " WHERE fec_ingreso >= '" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' AND fec_ingreso <= '" & Format(dtp_fin.Value, "yyyy-mm-dd") & "' AND folio_chq NOT IN (SELECT folio_chq FROM fnza_chq_mvto)"
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
    Archivo = ""
    pgbar.Min = 1
    pgbar.Max = objRS.RecordCount + 1
    pgbar.Value = pgbar.Min
    I = 0
    While Not objRS.EOF
        Archivo = Archivo & objRS(0) & "," & objRS(1) & "," & objRS(2) & "," & objRS(3) & "," & objRS(4) & "," & objRS(5) & "," & objRS(6) & "," & objRS(7) & "," & objRS(8) & "," _
        & objRS(9) & "," & objRS(10) & "," & objRS(11) & "," & objRS(12) & "," & objRS(13) & "," & objRS(14) & "," & objRS(15) & "," & objRS(16) & "," _
        & objRS(17) & "," & objRS(18) & "," & objRS(19) & "," & objRS(20) & "," & objRS(21) & "," & objRS(22) & "," & objRS(23) & "," & objRS(24)
        Call AgregarRegistroCobranza(Archivo, ruta2)
        Archivo = ""
        objRS.MoveNext
        I = I + 1
        pgbar.Value = I
    Wend
    End If
    objRS.Close
    Else
    ' ********************************************************************************************************************************************************
        'Corporacion
        '' Haber trans,cred,debito
        var_sql = "SELECT cta_h,SUM(monto_pago) AS monto,'' AS C,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
                & "tipo_conciliacion,nro_conciliacion,'' AS S,'' AS T,'' AS U,'' AS V,'' AS W,'' AS X,'' AS Y" _
                & " 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 AND (m.tipo_pago = 'tran' OR m.tipo_pago = 'tcre' OR m.tipo_pago = 'tdeb')" _
                & " GROUP BY folio_blta_fisico"
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
        Archivo = ""
        pgbar.Min = 1
        pgbar.Max = objRS.RecordCount + 1
        pgbar.Value = pgbar.Min
        I = 0
        While Not objRS.EOF
            Archivo = Archivo & objRS(0) & "," & objRS(1) & ";" & objRS(2) & ";" & objRS(3) & ";" & objRS(4) & ";" & objRS(5) & ";" & objRS(6) & ";" & objRS(7) & ";" & objRS(8) & ";" _
            & objRS(9) & ";" & objRS(10) & ";" & objRS(11) & ";" & objRS(12) & ";" & objRS(13) & ";" & objRS(14) & ";" & objRS(15) & ";" & objRS(16) & ";" _
            & objRS(17) & ";" & objRS(18) & ";" & objRS(19) & ";" & objRS(20) & ";" & objRS(21) & ";" & objRS(22) & ";" & objRS(23) & ";" & objRS(24)
            Call AgregarRegistroCobranza(Archivo, ruta2)
            Archivo = ""
            objRS.MoveNext
            I = I + 1
            pgbar.Value = I
        Wend
        End If
        objRS.Close
    
    '' Debe trans,cred,debito
        var_sql = "SELECT cta_d,'' AS B,SUM(monto_pago) AS monto,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
                & "'' AS Q,'' AS R,MID(b.run_apo,1,LENGTH(b.run_apo)-2) AS codigo_auxiliar,tipo_conciliacion,nro_conciliacion,fecha_blta,fecha_blta,'B6' AS tipo_documento_referencia,b.folio_blta_fisico" _
                & " 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 AND (m.tipo_pago = 'tran' OR m.tipo_pago = 'tcre' OR m.tipo_pago = 'tdeb')" _
                & " GROUP BY folio_blta_fisico "
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
        Archivo = ""
        pgbar.Min = 1
        pgbar.Max = objRS.RecordCount + 1
        pgbar.Value = pgbar.Min
        I = 0
        While Not objRS.EOF
            Archivo = Archivo & objRS(0) & ";" & objRS(1) & ";" & objRS(2) & ";" & objRS(3) & ";" & objRS(4) & ";" & objRS(5) & ";" & objRS(6) & ";" & objRS(7) & ";" & objRS(8) & ";" _
            & objRS(9) & ";" & objRS(10) & ";" & objRS(11) & ";" & objRS(12) & ";" & objRS(13) & ";" & objRS(14) & ";" & objRS(15) & ";" & objRS(16) & ";" _
            & objRS(17) & ";" & objRS(18) & ";" & objRS(19) & ";" & objRS(20) & ";" & Replace(objRS(21), "-", "/") & ";" & Replace(objRS(22), "-", "/") & ";" & objRS(23) & ";" & objRS(24)
            Call AgregarRegistroCobranza(Archivo, ruta2)
            Archivo = ""
            objRS.MoveNext
            I = I + 1
            pgbar.Value = I
        Wend
        End If
        objRS.Close
    
    End If
        
    MsgBox "Archivo generado exitosamente, en la ruta " & ruta2, vbInformation
    
End Sub

Function Crear_Archivo_Cobranza(ruta As String) As Boolean
On Error GoTo Controlerror:
    'ArchBCI = "\\" & Form_main.Winsock1.LocalIP & "\Reg_Cobranza\" & "deuda.txt"
    ArchBCI = ruta
    Crear_Archivo_Cobranza = False
    Canc = FreeFile
    Open ArchBCI For Output As #Canc
    Close #Canc

    Crear_Archivo_Cobranza = True
    Exit Function
Controlerror:
    Err.Raise Err.Number, "CrearArchBCI->" & Err.Source, Err.Description
    Resume
End Function

Function AgregarRegistroCobranza(Registro As String, ruta As String) As Boolean
On Error GoTo Controlerror:
    Dim num_arch As Integer
    
    AgregarRegistroCobranza = False
    
    num_arch = FreeFile
    Arch = ruta
    Open Arch For Append As #num_arch
        Print #num_arch, Registro
    Close #num_arch
    AgregarRegistroCobranza = True
    Exit Function
Controlerror:
    Err.Raise Err.Number, "AgregarRegistroArchivoGeneracion->" & Err.Source, Err.Description
End Function

Private Sub Command2_Click()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim var_sql As String
    Dim Archivo As String
    Dim ruta2 As String
    Dim I As Integer
    
    CommonDialog1.DialogTitle = "Seleccione el archivo de texto"
    CommonDialog1.Filter = "Archivos de texto*.txt"
    CommonDialog1.ShowSave

    If CommonDialog1.FileName = "" Then
        MsgBox "Debe Seleccionar una ruta para guardar archivo"
        Exit Sub
    End If
       
    ruta2 = CommonDialog1.FileName & ".csv"
    'ruta2 = "C:\provision.csv"
    
    Crear_Archivo_Cobranza (ruta2)
    'Call AgregarRegistroCobranza(Registro, ruta2)
    
    CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    
    If cb_archivo.Text = "Auxiliares" Then
        If cb_agno.Text >= 2024 Then
             var_sql = "SELECT a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) apoderado,'' a3,'' a4,'S' a5,'' a6,'' a7,'' a8,'01' a9,'13119' a10," _
                    & "dir_apo a11,'' a12, '' a13,'' a14,'' a15,'' a16,'' a17,'S' a18,'' a19,'' a20," _
                    & "'' a21,'' a22,'' a23,'' a24,'' a25,'' a26,'' a27,'' a28,'' a29,'' a30," _
                    & "'' a31,'' a32,'' a33,'' a34,'' a35,'' a36,'' a37,'' a38,'' a39,'' a40," _
                    & "'' a41,'' a42,'' a43,'' a44,'' a45,'' a46,'' a47,'' a48,'' a49,'' a50," _
                    & "'' a51,'' a52,'' a53,'' a54,'' a55,'' a56,'' a57,'' a58,'' a59,'' a60," _
                    & "'' a61,'' a62,'' a63" _
                    & " FROM matriculas m,apo_x_alu_contabilidad axa,apoderados a" _
                    & " WHERE m.anno_mat = axa.agno AND m.ci_alu = axa.ci_alu AND axa.rut_apo = a.rut_apo AND" _
                    & " fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "'" _
                    & " AND anno_mat = " & cb_agno.Text & " GROUP BY rut_apo"
        Else
            var_sql = "SELECT a.rut_apo,CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) apoderado,'' a3,'' a4,'S' a5,'' a6,'' a7,'' a8,'01' a9,'13119' a10," _
                    & "dir_apo a11,'' a12, '' a13,'' a14,'' a15,'' a16,'' a17,'S' a18,'' a19,'' a20," _
                    & "'' a21,'' a22,'' a23,'' a24,'' a25,'' a26,'' a27,'' a28,'' a29,'' a30," _
                    & "'' a31,'' a32,'' a33,'' a34,'' a35,'' a36,'' a37,'' a38,'' a39,'' a40," _
                    & "'' a41,'' a42,'' a43,'' a44,'' a45,'' a46,'' a47,'' a48,'' a49,'' a50," _
                    & "'' a51,'' a52,'' a53,'' a54,'' a55,'' a56,'' a57,'' a58,'' a59,'' a60," _
                    & "'' a61,'' a62,'' a63" _
                    & " FROM matriculas m,apo_x_alu axa,apoderados a" _
                    & " WHERE m.anno_mat = axa.agno AND m.ci_alu = axa.ci_alu AND axa.rut_apo = a.rut_apo AND" _
                    & " fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' AND fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "'" _
                    & " AND anno_mat = " & cb_agno.Text & " GROUP BY rut_apo"
        End If
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
        Archivo = ""
        pgbar.Min = 1
        pgbar.Max = objRS.RecordCount + 1
        pgbar.Value = pgbar.Min
        I = 0
        While Not objRS.EOF
            Archivo = Archivo & objRS(0) & ";" & objRS(1) & ";" & objRS(2) & ";" & objRS(3) & ";" & objRS(4) & ";" & objRS(5) & ";" & objRS(6) & ";" & objRS(7) & ";" & objRS(8) & ";" _
            & objRS(9) & ";" & objRS(10) & ";" & objRS(11) & ";" & objRS(12) & ";" & objRS(13) & ";" & objRS(14) & ";" & objRS(15) & ";" & objRS(16) & ";" _
            & objRS(17) & ";" & objRS(18) & ";" & objRS(19) & ";" & objRS(20) & ";" & objRS(21) & ";" & objRS(22) & ";" & objRS(23) & ";" & objRS(24) & ";" _
            & objRS(25) & ";" & objRS(26) & ";" & objRS(27) & ";" & objRS(28) & ";" & objRS(29) & ";" & objRS(30) & ";" & objRS(31) & ";" & objRS(32) & ";" & objRS(33) & ";" & objRS(34) & ";" _
            & objRS(35) & ";" & objRS(36) & ";" & objRS(37) & ";" & objRS(38) & ";" & objRS(39) & ";" & objRS(40) & ";" & objRS(41) & ";" & objRS(42) & ";" & objRS(43) & ";" & objRS(44) & ";" _
            & objRS(45) & ";" & objRS(46) & ";" & objRS(47) & ";" & objRS(48) & ";" & objRS(49) & ";" & objRS(50) & ";" & objRS(51) & ";" & objRS(52) & ";" & objRS(53) & ";" & objRS(54) & ";" _
            & objRS(55) & ";" & objRS(56) & ";" & objRS(57) & ";" & objRS(58) & ";" & objRS(59) & ";" & objRS(60) & ";" & objRS(61) & ";" & objRS(62)
            
            Call AgregarRegistroCobranza(Archivo, ruta2)
            Archivo = ""
            objRS.MoveNext
            I = I + 1
            pgbar.Value = I
        Wend
        End If
        objRS.Close
        MsgBox "Archivo CSV generado exitosamente en la siguiente ruta, " & ruta2
                
        Exit Sub
    End If
       
    
    If CODBC = "HUE001" Then
        valor = DevuelveCampo("fnza_valor_uf", "", "valor", "fecha IN (SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS fechas)", conn)
'''        var_sql = "SELECT cta_h,SUM(monto_pago) AS monto,'' AS C,descripcion_movimiento,'' AS E,'' AS F,'' AS G,'' AS H,'' AS I,'' AS J,'' AS K,'' AS L,'' AS M,'' AS N,'' AS O,'' AS P," _
'''                & "'' AS Q,'' AS R,'' AS S,'' AS T,'' AS U,'' AS V,'' AS W,'' AS X,'' AS Y" _
'''                & " 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 AND m.tipo_pago = 'ctdo' GROUP BY folio_blta_fisico"
        If cb_agno.Text > 2024 Then
            var_sql = "SELECT '1-01-04-24' AS a1,monto_tot_cuota AS b2,'' AS c3,'PROVISION APODERADOS' AS d4,'' AS e5,'' AS f6,'' AS g7,'' AS h8,'' AS i9,'' AS j10,'' AS k11,'' AS l12," _
                    & "'' AS m13,'' AS n14,'' AS o15,'' AS p16,'' AS q17,'' AS r18,IF(run_titular IS NULL,rut_apo,rut_apo) s19," _
                    & "'20' AS t20,f.orden u21,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS v22,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS w23,'20' AS x24,f.orden y25,'' z26,'' AS aa27,'' AS ab28,'' AS ac29,'' AS ad30,'' ae31,'' af32,'' ag33,'' ah34,'' ai35" _
                    & " FROM (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,IF(concepto = 'esco',ROUND(monto_tot_cuota_uf * " & Replace(valor, ",", ".") & "),monto_tot_cuota) AS monto_tot_cuota," _
                    & "me.mes,f.mes AS orden,activo_alu , fec_mat_r, usuario_ingreso FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
                    & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes And m.ci_alu = f.run_alu And m.anno_mat = f.agno" _
                    & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "') AS f LEFT JOIN (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
                    & " ON f.rut_apo = c.run_apo" _
                    & " Union All" _
                    & " SELECT '2-10-22-06' AS a1,'' AS b2,monto_tot_cuota AS c3,'PROVISION APODERADOS' AS d4,'' AS e5,'' AS f6,'' AS g7,'' AS h8,'' AS i9,'' AS j10,'' AS k11,'' AS l12," _
                    & "'' AS m13,'' AS n14,'' AS o15,'' AS p16,'' AS q17,'' AS r18,IF(run_titular IS NULL,rut_apo,rut_apo) s19," _
                    & "'13' AS t20,f.orden u21,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS v22,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS w23,'13' AS x24,f.orden y25,'' z26,'' AS aa27,'' AS ab28,'' AS ac29,'' AS ad30,'' ae31,'' af32,'' ag33,'' ah34,'' ai35" _
                    & " FROM (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,IF(concepto = 'esco',ROUND(monto_tot_cuota_uf * " & Replace(valor, ",", ".") & "),monto_tot_cuota) AS monto_tot_cuota," _
                    & "me.mes,f.mes AS orden,activo_alu , fec_mat_r, usuario_ingreso FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
                    & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes And m.ci_alu = f.run_alu And m.anno_mat = f.agno" _
                    & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "') AS f LEFT JOIN (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
                    & " ON f.rut_apo = c.run_apo "
        Else
            var_sql = "SELECT '1-01-04-24' AS a1,monto_tot_cuota AS b2,'' AS c3,'PROVISION APODERADOS' AS d4,'' AS e5,'' AS f6,'' AS g7,'' AS h8,'' AS i9,'' AS j10,'' AS k11,'' AS l12," _
                    & "'' AS m13,'' AS n14,'' AS o15,'' AS p16,'' AS q17,'' AS r18,IF(run_titular IS NULL,rut_apo,rut_apo) s19," _
                    & "'20' AS t20,f.orden u21,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS v22,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS w23,'20' AS x24,f.orden y25,'' z26,'' AS aa27,'' AS ab28,'' AS ac29,'' AS ad30,'' ae31,'' af32,'' ag33,'' ah34,'' ai35" _
                    & " FROM (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,IF(concepto = 'esco',ROUND(monto_tot_cuota_uf * " & Replace(valor, ",", ".") & "),monto_tot_cuota) AS monto_tot_cuota," _
                    & "me.mes,f.mes AS orden,activo_alu , fec_mat_r, usuario_ingreso FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
                    & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes And m.ci_alu = f.run_alu And m.anno_mat = f.agno" _
                    & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "') AS f LEFT JOIN (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
                    & " ON f.rut_apo = c.run_apo" _
                    & " Union All" _
                    & " SELECT '2-10-22-06' AS a1,'' AS b2,monto_tot_cuota AS c3,'PROVISION APODERADOS' AS d4,'' AS e5,'' AS f6,'' AS g7,'' AS h8,'' AS i9,'' AS j10,'' AS k11,'' AS l12," _
                    & "'' AS m13,'' AS n14,'' AS o15,'' AS p16,'' AS q17,'' AS r18,IF(run_titular IS NULL,rut_apo,rut_apo) s19," _
                    & "'13' AS t20,f.orden u21,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS v22,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS w23,'13' AS x24,f.orden y25,'' z26,'' AS aa27,'' AS ab28,'' AS ac29,'' AS ad30,'' ae31,'' af32,'' ag33,'' ah34,'' ai35" _
                    & " FROM (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,IF(concepto = 'esco',ROUND(monto_tot_cuota_uf * " & Replace(valor, ",", ".") & "),monto_tot_cuota) AS monto_tot_cuota," _
                    & "me.mes,f.mes AS orden,activo_alu , fec_mat_r, usuario_ingreso FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
                    & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes And m.ci_alu = f.run_alu And m.anno_mat = f.agno" _
                    & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "') AS f LEFT JOIN (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
                    & " ON f.rut_apo = c.run_apo "
        End If
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
        Archivo = ""
        pgbar.Min = 1
        pgbar.Max = objRS.RecordCount + 1
        pgbar.Value = pgbar.Min
        I = 0
        While Not objRS.EOF
            Archivo = Archivo & objRS(0) & ";" & objRS(1) & ";" & objRS(2) & ";" & objRS(3) & ";" & objRS(4) & ";" & objRS(5) & ";" & objRS(6) & ";" & objRS(7) & ";" & objRS(8) & ";" _
            & objRS(9) & ";" & objRS(10) & ";" & objRS(11) & ";" & objRS(12) & ";" & objRS(13) & ";" & objRS(14) & ";" & objRS(15) & ";" & objRS(16) & ";" _
            & objRS(17) & ";" & objRS(18) & ";" & objRS(19) & ";" & objRS(20) & ";" & objRS(21) & ";" & objRS(22) & ";" & objRS(23) & ";" & objRS(24) & ";" _
            & objRS(25) & ";" & objRS(26) & ";" & objRS(27) & ";" & objRS(28) & ";" & objRS(29) & ";" & objRS(30) & ";" & objRS(31) & ";" & objRS(32) & ";" & objRS(33) & ";" & objRS(34)
            Call AgregarRegistroCobranza(Archivo, ruta2)
            Archivo = ""
            objRS.MoveNext
            I = I + 1
            pgbar.Value = I
        Wend
        End If
        objRS.Close
        MsgBox "Archivo CSV generado exitosamente en la siguiente ruta, " & ruta2
    Else
        If cb_agno.Text > 2023 Then
            var_sql = "SELECT '1-01-04-24' AS a1,monto_tot_cuota AS b2,'' AS c3,'PROVISION APODERADOS' AS d4,'' AS e5,'' AS f6,'' AS g7,'' AS h8,'' AS i9,'' AS j10,'' AS k11,'' AS l12," _
                    & "'' AS m13,'' AS n14,'' AS o15,'' AS p16,'' AS q17,'' AS r18,IF(run_titular IS NULL,rut_apo,rut_apo) s19," _
                    & "'20' AS t20,f.orden u21,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS v22,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS w23,'20' AS x24,f.orden y25,'' z26,'' AS aa27,'' AS ab28,'' AS ac29,'' AS ad30,'' ae31,'' af32,'' ag33,'' ah34,'' ai35" _
                    & " FROM (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,monto_tot_cuota," _
                    & "me.mes,f.mes AS orden,activo_alu , fec_mat_r, usuario_ingreso FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
                    & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes And m.ci_alu = f.run_alu And m.anno_mat = f.agno" _
                    & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "') AS f LEFT JOIN (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
                    & " ON f.rut_apo = c.run_apo" _
                    & " Union All" _
                    & " SELECT '2-10-22-06' AS a1,'' AS b2,monto_tot_cuota AS c3,'PROVISION APODERADOS' AS d4,'' AS e5,'' AS f6,'' AS g7,'' AS h8,'' AS i9,'' AS j10,'' AS k11,'' AS l12," _
                    & "'' AS m13,'' AS n14,'' AS o15,'' AS p16,'' AS q17,'' AS r18,IF(run_titular IS NULL,rut_apo,rut_apo) s19," _
                    & "'13' AS t20,f.orden u21,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS v22,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS w23,'13' AS x24,f.orden y25,'' z26,'' AS aa27,'' AS ab28,'' AS ac29,'' AS ad30,'' ae31,'' af32,'' ag33,'' ah34,'' ai35" _
                    & " FROM (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,monto_tot_cuota," _
                    & "me.mes,f.mes AS orden,activo_alu , fec_mat_r, usuario_ingreso FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
                    & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes And m.ci_alu = f.run_alu And m.anno_mat = f.agno" _
                    & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "') AS f LEFT JOIN (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
                    & " ON f.rut_apo = c.run_apo "
        Else
            var_sql = "SELECT '1-01-04-22' AS a1,monto_tot_cuota AS b2,'' AS c3,'PROVISION APODERADOS' AS d4,'' AS e5,'' AS f6,'' AS g7,'' AS h8,'' AS i9,'' AS j10,'' AS k11,'' AS l12," _
                    & "'' AS m13,'' AS n14,'' AS o15,'' AS p16,'' AS q17,'' AS r18,IF(run_titular IS NULL,rut_apo,rut_apo) s19," _
                    & "'20' AS t20,f.orden u21,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS v22,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS w23,'20' AS x24,f.orden y25,'' z26,'' AS aa27,'' AS ab28,'' AS ac29,'' AS ad30,'' ae31,'' af32,'' ag33,'' ah34,'' ai35" _
                    & " FROM (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,monto_tot_cuota," _
                    & "me.mes,f.mes AS orden,activo_alu , fec_mat_r, usuario_ingreso FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
                    & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes And m.ci_alu = f.run_alu And m.anno_mat = f.agno" _
                    & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "') AS f LEFT JOIN (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
                    & " ON f.rut_apo = c.run_apo" _
                    & " Union All" _
                    & " SELECT '2-10-22-05' AS a1,'' AS b2,monto_tot_cuota AS c3,'PROVISION APODERADOS' AS d4,'' AS e5,'' AS f6,'' AS g7,'' AS h8,'' AS i9,'' AS j10,'' AS k11,'' AS l12," _
                    & "'' AS m13,'' AS n14,'' AS o15,'' AS p16,'' AS q17,'' AS r18,IF(run_titular IS NULL,rut_apo,rut_apo) s19," _
                    & "'13' AS t20,f.orden u21,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS v22,DATE_FORMAT(fec_mat_r,'%d-%m-%Y') AS w23,'13' AS x24,f.orden y25,'' z26,'' AS aa27,'' AS ab28,'' AS ac29,'' AS ad30,'' ae31,'' af32,'' ag33,'' ah34,'' ai35" _
                    & " FROM (SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,m.ci_alu,monto_tot_cuota," _
                    & "me.mes,f.mes AS orden,activo_alu , fec_mat_r, usuario_ingreso FROM matriculas m,apo_x_alu axa,apoderados a,fnza_detalle_mensualidad f,meses me" _
                    & " Where m.anno_mat = axa.agno And m.ci_alu = axa.ci_alu And axa.rut_apo = a.rut_apo And f.mes = Me.num_mes And m.ci_alu = f.run_alu And m.anno_mat = f.agno" _
                    & " AND anno_mat = " & cb_agno.Text & " AND fec_mat_r >= '" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTPicker2.Value, "yyyy-mm-dd") & "') AS f LEFT JOIN (SELECT run_apo,run_titular FROM fnza_cheques_recibidos WHERE agno = " & cb_agno.Text & " GROUP BY run_apo) AS c" _
                    & " ON f.rut_apo = c.run_apo "
        End If
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
        Archivo = ""
        pgbar.Min = 1
        pgbar.Max = objRS.RecordCount + 1
        pgbar.Value = pgbar.Min
        I = 0
        While Not objRS.EOF
            Archivo = Archivo & objRS(0) & ";" & objRS(1) & ";" & objRS(2) & ";" & objRS(3) & ";" & objRS(4) & ";" & objRS(5) & ";" & objRS(6) & ";" & objRS(7) & ";" & objRS(8) & ";" _
            & objRS(9) & ";" & objRS(10) & ";" & objRS(11) & ";" & objRS(12) & ";" & objRS(13) & ";" & objRS(14) & ";" & objRS(15) & ";" & objRS(16) & ";" _
            & objRS(17) & ";" & objRS(18) & ";" & objRS(19) & ";" & objRS(20) & ";" & objRS(21) & ";" & objRS(22) & ";" & objRS(23) & ";" & objRS(24) & ";" _
            & objRS(25) & ";" & objRS(26) & ";" & objRS(27) & ";" & objRS(28) & ";" & objRS(29) & ";" & objRS(30) & ";" & objRS(31) & ";" & objRS(32) & ";" & objRS(33) & ";" & objRS(34)
            Call AgregarRegistroCobranza(Archivo, ruta2)
            Archivo = ""
            objRS.MoveNext
            I = I + 1
            pgbar.Value = I
        Wend
        End If
        objRS.Close
        MsgBox "Archivo CSV generado exitosamente en la siguiente ruta, " & ruta2
            
    End If
End Sub

Private Sub Form_Load()
    Dim I As Integer
    dtp_inicio.Value = Date
    dtp_fin.Value = Date
    DTPicker1.Value = Date
    DTPicker2.Value = Date
    For I = 2022 To (Year(Date) + 1)
        cb_agno.AddItem I
    Next I
    cb_agno.Text = Year(Date) + 1
    cb_archivo.AddItem "Provisión"
    cb_archivo.AddItem "Auxiliares"
    cb_archivo.Text = "Provisión"
    
End Sub
