VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.2#0"; "MSCOMCTL.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Object = "{0ECD9B60-23AA-11D0-B351-00A0C9055D8E}#6.0#0"; "MSHFLXGD.OCX"
Object = "{00025600-0000-0000-C000-000000000046}#4.6#0"; "crystl32.ocx"
Begin VB.Form Form_GestionCuponeras 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Gestion Cuponeras"
   ClientHeight    =   6675
   ClientLeft      =   255
   ClientTop       =   630
   ClientWidth     =   14355
   Icon            =   "Form_GestionCuponeras.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   MouseIcon       =   "Form_GestionCuponeras.frx":2A8B2
   ScaleHeight     =   6675
   ScaleWidth      =   14355
   Begin VB.Frame Frame_CrearCuponera 
      Caption         =   "Crear Cuponera"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   2055
      Left            =   3240
      TabIndex        =   29
      Top             =   2280
      Visible         =   0   'False
      Width           =   3375
      Begin VB.ComboBox Cb_Empresa 
         Height          =   315
         Left            =   1680
         TabIndex        =   43
         Top             =   960
         Width           =   1335
      End
      Begin VB.CommandButton Btn_CancelarCuponera 
         Caption         =   "Cancelar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   1800
         TabIndex        =   41
         Top             =   1440
         Width           =   1455
      End
      Begin VB.ComboBox cb_agnos 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   1680
         TabIndex        =   31
         Top             =   360
         Width           =   1335
      End
      Begin VB.CommandButton cmdGenerarCuponera 
         Caption         =   "Generar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   120
         TabIndex        =   30
         Top             =   1440
         Width           =   1455
      End
      Begin VB.Label Label11 
         Caption         =   "Empresa:"
         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        =   44
         Top             =   960
         Width           =   855
      End
      Begin VB.Label Label10 
         Caption         =   "Año"
         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        =   42
         Top             =   360
         Width           =   975
      End
   End
   Begin VB.ListBox List4 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   1320
      Left            =   11760
      TabIndex        =   2
      Top             =   720
      Visible         =   0   'False
      Width           =   1335
   End
   Begin VB.ListBox List2 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   1110
      Left            =   11760
      TabIndex        =   3
      Top             =   840
      Visible         =   0   'False
      Width           =   1335
   End
   Begin VB.Frame Frame7 
      Height          =   1095
      Left            =   3480
      TabIndex        =   63
      Top             =   120
      Width           =   2655
      Begin Crystal.CrystalReport cr_cupo 
         Left            =   120
         Top             =   600
         _ExtentX        =   741
         _ExtentY        =   741
         _Version        =   262150
      End
      Begin VB.CommandButton Command2 
         Caption         =   "Cuponeras Erroneas"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   360
         TabIndex        =   83
         Top             =   600
         Width           =   1935
      End
      Begin VB.CommandButton cmb_lista 
         Caption         =   "Listar Tareas"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   360
         TabIndex        =   64
         Top             =   240
         Width           =   1935
      End
   End
   Begin VB.Frame Frame_editar_cupon 
      Caption         =   "Editar Cupó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          =   1695
      Left            =   9360
      TabIndex        =   32
      Top             =   1680
      Visible         =   0   'False
      Width           =   4815
      Begin VB.CommandButton btn_eliminar 
         Caption         =   "Eliminar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   1740
         TabIndex        =   65
         Top             =   1080
         Width           =   1335
      End
      Begin VB.TextBox txt_NuevoValor 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   1320
         TabIndex        =   37
         Top             =   600
         Width           =   1575
      End
      Begin VB.CommandButton Btn_CancelarEdicion 
         Caption         =   "Cancelar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   3240
         TabIndex        =   34
         Top             =   1080
         Width           =   1335
      End
      Begin VB.CommandButton Btn_editar 
         Caption         =   "Procesar"
         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        =   33
         Top             =   1080
         Width           =   1335
      End
      Begin VB.Label Lbl_Venc_Cupon 
         Caption         =   "0000-00-00"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   3720
         TabIndex        =   40
         Top             =   360
         Width           =   975
      End
      Begin VB.Label Label9 
         Caption         =   "Vencimiento:"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   2640
         TabIndex        =   39
         Top             =   360
         Width           =   975
      End
      Begin VB.Label Lbl_valor_cupon 
         Caption         =   "0000"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   840
         TabIndex        =   38
         Top             =   360
         Width           =   735
      End
      Begin VB.Label Label3 
         Caption         =   "Nuevo Valor:"
         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        =   36
         Top             =   600
         Width           =   1215
      End
      Begin VB.Label Label2 
         Caption         =   "Valor :"
         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        =   35
         Top             =   360
         Width           =   495
      End
   End
   Begin MSComctlLib.ProgressBar PgBar 
      Height          =   375
      Left            =   120
      TabIndex        =   47
      Top             =   6120
      Width           =   14055
      _ExtentX        =   24791
      _ExtentY        =   661
      _Version        =   393216
      Appearance      =   1
      Min             =   1e-4
      Scrolling       =   1
   End
   Begin VB.Frame Frame2 
      Caption         =   "Seleccionar 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          =   1095
      Left            =   120
      TabIndex        =   4
      Top             =   120
      Width           =   3255
      Begin VB.OptionButton Option1 
         Caption         =   "Cobranza Masivo"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Index           =   3
         Left            =   120
         TabIndex        =   70
         Top             =   360
         Width           =   1695
      End
      Begin VB.OptionButton Option1 
         Caption         =   "Alumno"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   195
         Index           =   2
         Left            =   1920
         TabIndex        =   7
         Top             =   720
         Width           =   975
      End
      Begin VB.OptionButton Option1 
         Caption         =   "Cuponeras Masivo"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   195
         Index           =   0
         Left            =   120
         TabIndex        =   6
         Top             =   720
         Width           =   1695
      End
      Begin VB.OptionButton Option1 
         Caption         =   "Apoderado"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Index           =   1
         Left            =   1920
         TabIndex        =   5
         Top             =   360
         Value           =   -1  'True
         Width           =   1215
      End
   End
   Begin VB.ListBox List3 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   1320
      Left            =   6960
      TabIndex        =   0
      Top             =   720
      Visible         =   0   'False
      Width           =   4335
   End
   Begin VB.ListBox List1 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   1110
      Left            =   6960
      TabIndex        =   1
      Top             =   840
      Visible         =   0   'False
      Width           =   4335
   End
   Begin VB.Frame Frame_Actualizacion_Masivo 
      Caption         =   "Actualizar Masivo"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00FF0000&
      Height          =   1695
      Left            =   720
      TabIndex        =   73
      Top             =   2520
      Visible         =   0   'False
      Width           =   8055
      Begin ComctlLib.ProgressBar progreso 
         Height          =   255
         Left            =   240
         TabIndex        =   75
         Top             =   1320
         Width           =   7575
         _ExtentX        =   13361
         _ExtentY        =   450
         _Version        =   327682
         Appearance      =   1
      End
      Begin VB.Label Label4 
         Caption         =   "Espere Mientras el Sistema Actualiza los Datos"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   14.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   720
         TabIndex        =   74
         Top             =   480
         Width           =   6735
      End
   End
   Begin VB.Frame frm_mensaje 
      Caption         =   "Mensaje"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1695
      Left            =   720
      TabIndex        =   50
      Top             =   2520
      Visible         =   0   'False
      Width           =   8055
      Begin MSComctlLib.ProgressBar barra 
         Height          =   255
         Left            =   360
         TabIndex        =   52
         Top             =   1080
         Width           =   7575
         _ExtentX        =   13361
         _ExtentY        =   450
         _Version        =   393216
         Appearance      =   1
      End
      Begin VB.Label Label15 
         Caption         =   "Espere Mientras el Sistema Actualiza los Datos"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   14.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   615
         Left            =   720
         TabIndex        =   51
         Top             =   360
         Width           =   6975
      End
   End
   Begin VB.Frame Frame_Cupones 
      Caption         =   "Cupones de la Cuponera"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   4695
      Left            =   9360
      TabIndex        =   27
      Top             =   1320
      Width           =   4815
      Begin MSHierarchicalFlexGridLib.MSHFlexGrid mshflex_detacuponera 
         Height          =   4335
         Left            =   120
         TabIndex        =   28
         Top             =   240
         Width           =   4575
         _ExtentX        =   8070
         _ExtentY        =   7646
         _Version        =   393216
         FocusRect       =   0
         FillStyle       =   1
         SelectionMode   =   1
         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
         _NumberOfBands  =   1
         _Band(0).Cols   =   2
      End
   End
   Begin VB.Frame Frame3 
      Caption         =   "Acciones"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   735
      Left            =   120
      TabIndex        =   53
      Top             =   3600
      Width           =   9135
      Begin VB.CommandButton btn_crear 
         Caption         =   "Crear archivo"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   6360
         TabIndex        =   62
         Top             =   240
         Width           =   1455
      End
      Begin VB.CommandButton Btn_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          =   375
         Left            =   7920
         TabIndex        =   59
         Top             =   240
         Width           =   1095
      End
      Begin VB.CommandButton Btn_Limpiar 
         Caption         =   "Limpiar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   5080
         TabIndex        =   58
         Top             =   240
         Width           =   1095
      End
      Begin VB.CommandButton Cancelar 
         Caption         =   "Cancelar"
         Enabled         =   0   'False
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   3840
         TabIndex        =   57
         Top             =   240
         Width           =   1095
      End
      Begin VB.CommandButton cmd_EliminarCuponera 
         Caption         =   "Eliminar"
         Enabled         =   0   'False
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   2600
         TabIndex        =   56
         Top             =   240
         Width           =   1095
      End
      Begin VB.CommandButton cmg_Reimprimir 
         Caption         =   "Reimprimir"
         Enabled         =   0   'False
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   1360
         TabIndex        =   55
         Top             =   240
         Width           =   1095
      End
      Begin VB.CommandButton Btn_Generar 
         Caption         =   "Generar"
         Enabled         =   0   'False
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   120
         TabIndex        =   54
         Top             =   240
         Width           =   1095
      End
   End
   Begin VB.Frame Frame_Cuponeras 
      Caption         =   "Cuponeras"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   2295
      Left            =   120
      TabIndex        =   25
      Top             =   1320
      Width           =   9135
      Begin MSHierarchicalFlexGridLib.MSHFlexGrid mshflex_cuponeras 
         Height          =   1935
         Left            =   120
         TabIndex        =   26
         Top             =   240
         Width           =   8895
         _ExtentX        =   15690
         _ExtentY        =   3413
         _Version        =   393216
         FocusRect       =   0
         FillStyle       =   1
         SelectionMode   =   1
         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
         _NumberOfBands  =   1
         _Band(0).Cols   =   2
      End
   End
   Begin VB.Frame Frame6 
      Caption         =   "Alumno(s)"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1575
      Left            =   120
      TabIndex        =   60
      Top             =   4440
      Width           =   9135
      Begin MSHierarchicalFlexGridLib.MSHFlexGrid Grilla 
         Height          =   1215
         Left            =   120
         TabIndex        =   61
         Top             =   240
         Width           =   8895
         _ExtentX        =   15690
         _ExtentY        =   2143
         _Version        =   393216
         FixedCols       =   0
         BackColorSel    =   14737632
         ForeColorSel    =   16711680
         FocusRect       =   0
         FillStyle       =   1
         GridLines       =   0
         GridLinesFixed  =   1
         GridLinesUnpopulated=   1
         ScrollBars      =   2
         SelectionMode   =   1
         RowSizingMode   =   1
         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
         _NumberOfBands  =   1
         _Band(0).Cols   =   2
         _Band(0).GridLineWidthBand=   1
         _Band(0).TextStyleBand=   0
      End
   End
   Begin VB.Frame Frame_EliminacionMasivos 
      Caption         =   "Generación de Cobranza"
      Enabled         =   0   'False
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1095
      Left            =   6240
      TabIndex        =   71
      Top             =   120
      Width           =   7935
      Begin VB.ComboBox cb_mes_masivos 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   2160
         TabIndex        =   84
         Top             =   480
         Width           =   615
      End
      Begin VB.OptionButton Option2 
         Caption         =   "Eliminación "
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Index           =   1
         Left            =   120
         TabIndex        =   82
         Top             =   600
         Width           =   1095
      End
      Begin VB.OptionButton Option2 
         Caption         =   "Creación"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   195
         Index           =   0
         Left            =   120
         TabIndex        =   81
         Top             =   360
         Value           =   -1  'True
         Width           =   1095
      End
      Begin VB.TextBox txt_password 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   315
         Left            =   2880
         TabIndex        =   79
         Top             =   480
         Width           =   2295
      End
      Begin VB.ComboBox cb_agno_masivos 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   1320
         TabIndex        =   77
         Text            =   "2010"
         Top             =   480
         Width           =   735
      End
      Begin VB.CommandButton cmd_ 
         Caption         =   "Eliminación masivo"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   6120
         TabIndex        =   72
         Top             =   360
         Visible         =   0   'False
         Width           =   1575
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Generación de Cobranza"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   5280
         TabIndex        =   76
         Top             =   360
         Width           =   2295
      End
      Begin VB.Label Label18 
         Caption         =   "Mes"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Left            =   2160
         TabIndex        =   85
         Top             =   240
         Width           =   495
      End
      Begin VB.Label Label16 
         Caption         =   "Contraseña"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Left            =   2880
         TabIndex        =   80
         Top             =   240
         Width           =   975
      End
      Begin VB.Label Label17 
         Caption         =   "Año"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Left            =   1320
         TabIndex        =   78
         Top             =   240
         Width           =   495
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "Masivos"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1095
      Left            =   6240
      TabIndex        =   14
      Top             =   120
      Width           =   7935
      Begin VB.OptionButton opt_tipo 
         Caption         =   "Cuponeras"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Index           =   1
         Left            =   240
         TabIndex        =   69
         Top             =   600
         Width           =   1215
      End
      Begin VB.OptionButton opt_tipo 
         Caption         =   "Cuota00"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Index           =   0
         Left            =   240
         TabIndex        =   68
         Top             =   240
         Value           =   -1  'True
         Width           =   1215
      End
      Begin VB.ComboBox cb_institucion 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   1680
         TabIndex        =   66
         Text            =   "Combo1"
         Top             =   480
         Width           =   1695
      End
      Begin VB.TextBox text_passwd 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         IMEMode         =   3  'DISABLE
         Left            =   4680
         PasswordChar    =   "*"
         TabIndex        =   48
         Top             =   480
         Width           =   1935
      End
      Begin VB.ComboBox Combo_agno_general 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   3600
         TabIndex        =   45
         Top             =   480
         Width           =   855
      End
      Begin VB.CommandButton Btn_General 
         Caption         =   "Procesar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   615
         Left            =   6720
         TabIndex        =   15
         Top             =   240
         Width           =   975
      End
      Begin VB.Label Label12 
         Caption         =   "Institución"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   1680
         TabIndex        =   67
         Top             =   240
         Width           =   1095
      End
      Begin VB.Label Label14 
         Caption         =   "Contraseña"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   4680
         TabIndex        =   49
         Top             =   240
         Width           =   855
      End
      Begin VB.Label Label13 
         Caption         =   "Año"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   3600
         TabIndex        =   46
         Top             =   240
         Width           =   615
      End
   End
   Begin VB.Frame Frame5 
      Caption         =   "Selección por Apoderado"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1095
      Left            =   6240
      TabIndex        =   8
      Top             =   120
      Width           =   7935
      Begin VB.TextBox Text3 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   720
         TabIndex        =   11
         Top             =   360
         Width           =   4335
      End
      Begin VB.TextBox Text4 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   5520
         TabIndex        =   10
         Top             =   360
         Width           =   1335
      End
      Begin VB.CommandButton Btn_Ver_Apoderado 
         Caption         =   "Ver"
         Enabled         =   0   'False
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   615
         Left            =   6960
         Picture         =   "Form_GestionCuponeras.frx":55164
         Style           =   1  'Graphical
         TabIndex        =   9
         Top             =   240
         Width           =   735
      End
      Begin VB.Label Label7 
         Caption         =   "Nombre"
         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
      Begin VB.Label Label8 
         Caption         =   "Run"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   5160
         TabIndex        =   12
         Top             =   360
         Width           =   495
      End
   End
   Begin VB.Frame Frame4 
      Caption         =   "Selección por Alumno"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1095
      Left            =   6240
      TabIndex        =   16
      Top             =   120
      Width           =   7935
      Begin VB.TextBox Text1 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   720
         TabIndex        =   19
         Text            =   "Text1"
         Top             =   480
         Width           =   4335
      End
      Begin VB.TextBox Text2 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   5520
         TabIndex        =   18
         Text            =   "Text2"
         Top             =   480
         Width           =   1335
      End
      Begin VB.CommandButton Btn_Ver_Alumno 
         Caption         =   "Ver"
         Enabled         =   0   'False
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   615
         Left            =   6960
         Picture         =   "Form_GestionCuponeras.frx":556DB
         Style           =   1  'Graphical
         TabIndex        =   17
         Top             =   240
         Width           =   735
      End
      Begin VB.Label Label1 
         Caption         =   "Label1"
         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        =   22
         Top             =   720
         Width           =   3615
      End
      Begin VB.Label Label5 
         Caption         =   "Nombre"
         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        =   21
         Top             =   480
         Width           =   615
      End
      Begin VB.Label Label6 
         Caption         =   "Run"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   5160
         TabIndex        =   20
         Top             =   480
         Width           =   495
      End
   End
   Begin VB.Label lbl_Run 
      Caption         =   "Label9"
      Height          =   255
      Left            =   3480
      TabIndex        =   24
      Top             =   480
      Visible         =   0   'False
      Width           =   1215
   End
   Begin VB.Label lbl_Apoderado 
      Caption         =   "Label3"
      Height          =   255
      Left            =   3480
      TabIndex        =   23
      Top             =   120
      Visible         =   0   'False
      Width           =   2655
   End
End
Attribute VB_Name = "Form_GestionCuponeras"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim ARCH_GENBC As String
Dim ARCH_ELIBC As String
Dim ARCH_REIBC As String
Dim ARCH_GENPK As String
Dim ARCH_ELIPK As String
Dim ARCH_REIPK As String
Dim Cambio As Boolean
Dim Lista As Boolean

Private Sub MuestraFrame(ByVal frm As String)
    ' Muestra seleccion por curso
    Btn_Generar.Enabled = True
    cmg_Reimprimir.Enabled = True
    cmd_EliminarCuponera.Enabled = True
    Cancelar.Enabled = True
    If frm = "curso" Then
        Frame1.Visible = True
        'SeteaVistaCurso
        Btn_Generar.Enabled = False
        cmg_Reimprimir.Enabled = False
        cmd_EliminarCuponera.Enabled = False
        Cancelar.Enabled = False
    Else
        Frame1.Visible = False
    End If
    ' Muestra seleccion por apoderado
    If frm = "apoderado" Then
        Frame5.Visible = True
        'SeteaVistaApoderado
        Btn_Ver_Alumno.Enabled = False
    Else
        Frame5.Visible = False
    End If
    ' Muestra seleccion por alumno
    If frm = "alumno" Then
        Frame4.Visible = True
        Btn_Ver_Apoderado.Enabled = False
        'SeteaVistaAlumno
    Else
        Frame4.Visible = False
    End If
    ' Muestra seleccion de eliminar masivo
    If frm = "masivo_eliminacion" Then
        Frame_EliminacionMasivos.Visible = True
        Frame_EliminacionMasivos.Enabled = True
        'Btn_Ver_Apoderado.Enabled = False
        'SeteaVistaAlumno
    Else
        Frame_EliminacionMasivos.Visible = False
    End If
End Sub

Private Sub Btn_CancelarCuponera_Click()
    Frame_Cuponeras.Enabled = True
    Frame_Cupones.Enabled = True
    Btn_Generar.Enabled = True
    cmd_EliminarCuponera.Enabled = True
    Btn_Limpiar.Enabled = True
    Btn_Salir.Enabled = True
    cmg_Reimprimir.Enabled = True
    Frame_CrearCuponera.Visible = False
    Cancelar.Enabled = True
    btn_crear.Enabled = True
End Sub

Private Sub Btn_CancelarEdicion_Click()
    Frame_Cuponeras.Enabled = True
    Frame_Cupones.Enabled = True
    Btn_Generar.Enabled = True
    cmd_EliminarCuponera.Enabled = True
    cmg_Reimprimir.Enabled = True
    Btn_Limpiar.Enabled = True
    Btn_Salir.Enabled = True
    btn_crear.Enabled = True
    Cancelar.Enabled = True
    Frame_editar_cupon.Visible = False
    llena_grilla_fnzafoliodetacupones (mshflex_cuponeras.TextMatrix(mshflex_cuponeras.RowSel, 1))
    Call marca_grilla(mshflex_detacuponera, 7)
    mshflex_detacuponera.Row = 1
    mshflex_detacuponera.ColSel = 1
    Call SeteaGrilla
    Call LlenaGrilla
End Sub

Private Sub btn_crear_Click()
    Dim accion, accion_detalle, run_apo As String
    Dim primer_cupon, ultimo_cupon, folio_cuponera, LI As Long
    Dim sw As Boolean
    Dim Sql As String
    Dim Pk As Boolean
    Dim archivo As String
    Dim RC As ADODB.Recordset
    Set RC = New ADODB.Recordset
    Dim RCcupoNera As ADODB.Recordset
    Set RCcupoNera = New ADODB.Recordset
    Dim RCcupon As ADODB.Recordset
    Set RCcupon = New ADODB.Recordset
    Barra.Value = 0
    Barra.Min = 0
    
    Btn_Generar.Enabled = False
    cmg_Reimprimir.Enabled = False
    cmd_EliminarCuponera.Enabled = False
    Cancelar.Enabled = False
    Btn_Limpiar.Enabled = False
    Btn_Salir.Enabled = False
    
    
    Sql = "select count(estado) as cant from fnza_deta_cupones where (estado = 'GE' or estado = 'DE' or estado = 'RE' or estado = 'MC') and loginusuario = '" & Nom_usu_ses & "'"
    RC.Open Sql, conn
    
    If RC!cant > 0 Then
    Barra.Max = RC!cant
    RC.Close
    frm_mensaje.Visible = True
    frm_mensaje.Refresh
    
        MsgBox "el sistema se dispondra a actualizar los datos de cuponeras, por favor espere un momento", vbInformation
        
'        ARCH_ELIBC = "\\192.168.7.10\cuponeras\" & CODBC & "\" & RetornaCampo("CFG", "valor", "param='CODBC'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_ELI.bci"
'        ARCH_REIBC = "\\192.168.7.10\cuponeras\" & CODBC & "\" & RetornaCampo("CFG", "valor", "param='CODBC'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_REI.bci"
'        ARCH_ELIPK = "\\192.168.7.10\cuponeras\" & CODBC & "\" & RetornaCampo("CFG", "valor", "param='CODPK'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_ELI.bci"
'        ARCH_REIPK = "\\192.168.7.10\cuponeras\" & CODBC & "\" & RetornaCampo("CFG", "valor", "param='CODPK'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_REI.bci"
'        ARCH_GENBC = "\\192.168.7.10\cuponeras\" & CODBC & "\" & RetornaCampo("CFG", "valor", "param='CODBC'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_GEN.bci"
'        ARCH_GENPK = "\\192.168.7.10\cuponeras\" & CODBC & "\" & RetornaCampo("CFG", "valor", "param='CODPK'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_GEN.bci"

        ARCH_ELIBC = "C:\" & RetornaCampo("CFG", "valor", "param='CODBC'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_ELI.bci"
        ARCH_REIBC = "C:\" & RetornaCampo("CFG", "valor", "param='CODBC'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_REI.bci"
        ARCH_ELIPK = "C:\" & RetornaCampo("CFG", "valor", "param='CODPK'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_ELI.bci"
        ARCH_REIPK = "C:\" & RetornaCampo("CFG", "valor", "param='CODPK'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_REI.bci"
        ARCH_GENBC = "C:\" & RetornaCampo("CFG", "valor", "param='CODBC'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_GEN.bci"
        ARCH_GENPK = "C:\" & RetornaCampo("CFG", "valor", "param='CODPK'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_GEN.bci"

        'generar y anexar datos archivo de generacion de cuponeras colegio segun año
        '***************************************************************************
        
        Sql = "SELECT distinct agno FROM fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fdc.folio_cuponera = fc.folio_cuponera and estado = 'GE' AND fc.colegio = '" & CODBC & "' and fdc.loginusuario = '" & Nom_usu_ses & "' ORDER BY fc.agno"
        RC.Open Sql, conn
        If RC.RecordCount > 0 Then
            While Not RC.EOF
                archivo = ARCH_GENBC & "_" & CStr(RC!agno) & "_GEN.bci"
                CrearArchBCI False, False, archivo, RC!agno, conn
                Sql = "SELECT * from fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fc.colegio = '" & CODBC & "' and fdc.loginusuario = '" & Nom_usu_ses & "' and fdc.estado = 'GE' and fdc.folio_cuponera = fc.folio_cuponera and fc.agno = " & RC!agno & " ORDER BY fc.folio_cuponera, fdc.vcto "
                RCcupoNera.Open Sql, conn
                sw = False
                primer_cupon = 0
                ultimo_cupon = 0
                While Not RCcupoNera.EOF
                    If sw = False Then
                        primer_cupon = RCcupoNera("folio_cupon")
                        ultimo_cupon = RCcupoNera("folio_cupon")
                        folio_cuponera = RCcupoNera("folio_cuponera")
                        run_apo = RCcupoNera("rut_apo")
                        sw = True
                    End If
                    If primer_cupon > RCcupoNera("folio_cupon") Then
                        primer_cupon = RCcupoNera("folio_cupon")
                    End If
                    If ultimo_cupon < RCcupoNera("folio_cupon") Then
                        ultimo_cupon = RCcupoNera("folio_cupon")
                    End If
                    AgregarRegistroArchivoGeneracion archivo, RCcupoNera("rut_apo"), RCcupoNera("valor"), RCcupoNera("numero"), Format(RCcupoNera("vcto"), "yyyy-mm-dd"), conn
                    Barra.Value = Barra.Value + 1
                    Barra.Refresh
                    Sql = "UPDATE fnza_deta_cupones fdc SET estado = 'PE' WHERE folio_cupon = " & RCcupoNera("folio_cupon")
                    conn.Execute Sql
                    RCcupoNera.MoveNext
                Wend
                MsgBox "El archivo de Generacion de cuponeras colegio se ha creado exitosamente. " & vbCrLf & "Se han creado un total de " & RCcupoNera.RecordCount & " cuponeras." & vbCrLf & "Y su hubicacion es : " & archivo & vbCrLf & "Para el año : " & RC!agno
                RCcupoNera.Close
                Sql = "UPDATE fnza_cuponeras fc SET esta_fin = 'PE' WHERE esta_fin = 'GE' AND colegio = '" & CODBC & "' and loginusuario = '" & Nom_usu_ses & "' and fc.agno = " & RC!agno
                conn.Execute Sql, LI
                'Inserta log
                If LI = 1 Then
                accion_detalle = "Se ha generado la cuponera con folio " & folio_cuponera _
                               & ", para el año " & RC!agno & ", con los cupones del " _
                               & primer_cupon & " al " & ultimo_cupon
                accion = "Generar Cuponera"
                Else
                accion = "Crear Cupon"
                If primer_cupon < ultimo_cupon Then
                    accion_detalle = "Se han creado los cupones del " & primer_cupon _
                                   & " al " & ultimo_cupon & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                Else
                    accion_detalle = "Se ha creado el cupon con folio " & primer_cupon _
                                   & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                End If
                End If
                'Call inserta_vitacora_cuponeras(run_apo, CLng(folio_cuponera), _
                '     CStr(accion), CStr(accion_detalle))
                'Fin de log
                RC.MoveNext
            Wend
         End If
        RC.Close
        'generar y anexar datos archivo de generacion de cuponeras marta carcamo segun año
        '*********************************************************************************
        
        Sql = "SELECT distinct agno FROM fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fdc.folio_cuponera = fc.folio_cuponera and estado = 'GE' AND fc.colegio = '" & CODPK & "' and fdc.loginusuario = '" & Nom_usu_ses & "' ORDER BY fc.agno"
        RC.Open Sql, conn
        If RC.RecordCount > 0 Then
            While Not RC.EOF
                archivo = ARCH_GENPK & "_" & RC!agno & "_GEN.bci"
                CrearArchBCI True, False, archivo, RC!agno, conn
                Sql = "SELECT * from fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fc.colegio = '" & CODPK & "' and fdc.loginusuario = '" & Nom_usu_ses & "' and fdc.estado = 'GE' and fdc.folio_cuponera = fc.folio_cuponera and fc.agno = " & RC!agno & " ORDER BY fc.folio_cuponera, fdc.vcto "
                RCcupoNera.Open Sql, conn
                sw = False
                primer_cupon = 0
                ultimo_cupon = 0
                While Not RCcupoNera.EOF
                    ' falta cabecera en numboleta
                    If sw = False Then
                        primer_cupon = RCcupoNera("folio_cupon")
                        ultimo_cupon = RCcupoNera("folio_cupon")
                        folio_cuponera = RCcupoNera("folio_cuponera")
                        run_apo = RCcupoNera("rut_apo")
                        sw = True
                    End If
                    If primer_cupon > RCcupoNera("folio_cupon") Then
                        primer_cupon = RCcupoNera("folio_cupon")
                    End If
                    If ultimo_cupon < RCcupoNera("folio_cupon") Then
                        ultimo_cupon = RCcupoNera("folio_cupon")
                    End If
                    AgregarRegistroArchivoGeneracion archivo, RCcupoNera("rut_apo"), RCcupoNera("valor"), RCcupoNera("numero"), Format(RCcupoNera("vcto"), "yyyy-mm-dd"), conn
                    Barra.Value = Barra.Value + 1
                    Barra.Refresh
                    Sql = "UPDATE fnza_deta_cupones fdc SET estado = 'PE' WHERE folio_cupon = " & RCcupoNera("folio_cupon")
                    conn.Execute Sql
                    RCcupoNera.MoveNext
                Wend
                MsgBox "El archivo de Generacion de cuponeras Marta Carcamo se ha creado exitosamente. " & vbCrLf & "Se han creado un total de " & RCcupoNera.RecordCount & " cuponeras." & vbCrLf & "Y su hubicacion es : " & archivo & vbCrLf & "Para el año : " & RC!agno
                RCcupoNera.Close
                Sql = "UPDATE fnza_cuponeras fc SET esta_fin = 'PE' WHERE esta_fin = 'GE' AND colegio = '" & CODPK & "' and loginusuario = '" & Nom_usu_ses & "' and fc.agno = " & RC!agno
                conn.Execute Sql, LI
                'Inserta log
                If LI = 1 Then
                accion_detalle = "Se ha generado la cuponera con folio " & folio_cuponera _
                               & ", para el año " & RC!agno & ", con los cupones del " _
                               & primer_cupon & " al " & ultimo_cupon
                accion = "Generar Cuponera"
                Else
                accion = "Crear Cupon"
                If primer_cupon < ultimo_cupon Then
                    accion_detalle = "Se han creado los cupones del " & primer_cupon _
                                   & " al " & ultimo_cupon & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                Else
                    accion_detalle = "Se ha creado el cupon con folio " & primer_cupon _
                                   & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                End If
                End If
                'Call inserta_vitacora_cuponeras(run_apo, CLng(folio_cuponera), _
                '     CStr(accion), CStr(accion_detalle))
                'Fin de log
                RC.MoveNext
            Wend
        End If
        RC.Close
        
        'generar y anexar datos archivo de eliminacion de cuponeras colegio segun año
        '****************************************************************************
        
        Sql = "SELECT distinct agno FROM fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fdc.folio_cuponera = fc.folio_cuponera and estado = 'DE' AND fc.colegio = '" & CODBC & "' and fdc.loginusuario = '" & Nom_usu_ses & "' ORDER BY fc.agno"
        RC.Open Sql, conn
        If RC.RecordCount > 0 Then
            While Not RC.EOF
                archivo = ARCH_ELIBC & "_" & RC!agno & "_ELI.bci"
                CrearArchBCI False, True, archivo, RC!agno, conn
                Sql = "SELECT * from fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fc.colegio = '" & CODBC & "' and fdc.loginusuario = '" & Nom_usu_ses & "' AND fdc.estado = 'DE' AND fdc.folio_cuponera = fc.folio_cuponera AND fc.agno = " & RC!agno & " ORDER BY fc.folio_cuponera, fdc.vcto "
                RCcupoNera.Open Sql, conn
                sw = False
                primer_cupon = 0
                ultimo_cupon = 0
                While Not RCcupoNera.EOF
                    If sw = False Then
                        primer_cupon = RCcupoNera("folio_cupon")
                        ultimo_cupon = RCcupoNera("folio_cupon")
                        folio_cuponera = RCcupoNera("folio_cuponera")
                        run_apo = RCcupoNera("rut_apo")
                        sw = True
                    End If
                    If primer_cupon > RCcupoNera("folio_cupon") Then
                        primer_cupon = RCcupoNera("folio_cupon")
                    End If
                    If ultimo_cupon < RCcupoNera("folio_cupon") Then
                        ultimo_cupon = RCcupoNera("folio_cupon")
                    End If
                    AgregarRegistroArchivoEliminacion archivo, False, RCcupoNera("rut_apo"), RCcupoNera("numero"), conn
                    Barra.Value = Barra.Value + 1
                    Barra.Refresh
                    Sql = "UPDATE fnza_deta_cupones fdc SET estado = 'EL' WHERE folio_cupon = " & RCcupoNera("folio_cupon")
                    conn.Execute Sql
                    RCcupoNera.MoveNext
                Wend
                MsgBox "El archivo de Eliminacion de cuponeras colegio se ha creado exitosamente. " & vbCrLf & "Se han eliminado un total de " & RCcupoNera.RecordCount & " cuponeras." & vbCrLf & "Y su hubicacion es : " & archivo & vbCrLf & "Para el año : " & RC!agno
                RCcupoNera.Close
                Sql = "UPDATE fnza_cuponeras fc SET esta_fin = 'EL' WHERE esta_fin = 'DE' AND colegio = '" & CODBC & "' and loginusuario = '" & Nom_usu_ses & "' and  fc.agno = " & RC!agno
                conn.Execute Sql, LI
                'Inserta log
                If LI = 1 Then
                accion_detalle = "Se ha eliminado la cuponera con folio " & folio_cuponera _
                               & ", para el año " & RC!agno & ", con los cupones del " _
                               & primer_cupon & " al " & ultimo_cupon
                accion = "Eliminar Cuponera"
                Else
                accion = "Eliminar Cupon"
                If primer_cupon < ultimo_cupon Then
                    accion_detalle = "Se han eliminado los cupones del " & primer_cupon _
                                   & " al " & ultimo_cupon & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                Else
                    accion_detalle = "Se ha eliminado el cupon con folio " & primer_cupon _
                                   & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                End If
                End If
                'Call inserta_vitacora_cuponeras(run_apo, CLng(folio_cuponera), _
                '     CStr(accion), CStr(accion_detalle))
                'Fin de log
                RC.MoveNext
            Wend
        End If
        RC.Close
        
        'generar y anexar datos archivo de eliminacion de cuponeras marta carcamo segun año
        '*********************************************************************************
        
        Sql = "SELECT distinct agno FROM fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fdc.folio_cuponera = fc.folio_cuponera and estado = 'DE' AND fc.colegio = '" & CODPK & "' and fdc.loginusuario = '" & Nom_usu_ses & "' ORDER BY fc.agno"
        RC.Open Sql, conn
        If RC.RecordCount > 0 Then
            While Not RC.EOF
                archivo = ARCH_ELIPK & "_" & RC!agno & "_ELI.bci"
                CrearArchBCI True, True, archivo, RC!agno, conn
                Sql = "SELECT * from fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fc.colegio = '" & CODPK & "' and fdc.loginusuario = '" & Nom_usu_ses & "' and fdc.estado = 'DE' and fdc.folio_cuponera = fc.folio_cuponera and fc.agno = " & RC!agno & " ORDER BY fc.folio_cuponera, fdc.vcto "
                RCcupoNera.Open Sql, conn
                sw = False
                primer_cupon = 0
                ultimo_cupon = 0
                While Not RCcupoNera.EOF
                    If sw = False Then
                        primer_cupon = RCcupoNera("folio_cupon")
                        ultimo_cupon = RCcupoNera("folio_cupon")
                        folio_cuponera = RCcupoNera("folio_cuponera")
                        run_apo = RCcupoNera("rut_apo")
                        sw = True
                    End If
                    If primer_cupon > RCcupoNera("folio_cupon") Then
                        primer_cupon = RCcupoNera("folio_cupon")
                    End If
                    If ultimo_cupon < RCcupoNera("folio_cupon") Then
                        ultimo_cupon = RCcupoNera("folio_cupon")
                    End If
                    AgregarRegistroArchivoEliminacion archivo, True, RCcupoNera("rut_apo"), RCcupoNera("numero"), conn
                    Barra.Value = Barra.Value + 1
                    Barra.Refresh
                    Sql = "UPDATE fnza_deta_cupones fdc SET estado = 'EL' WHERE folio_cupon = " & RCcupoNera("folio_cupon")
                    conn.Execute Sql
                    RCcupoNera.MoveNext
                Wend
                MsgBox "El archivo de Eliminacion de cuponeras Marta Carcamo se ha creado exitosamente. " & vbCrLf & "Se han eliminado un total de " & RCcupoNera.RecordCount & " cuponeras." & vbCrLf & "Y su hubicacion es : " & archivo & vbCrLf & "Para el año : " & RC!agno
                RCcupoNera.Close
                Sql = "UPDATE fnza_cuponeras fc SET esta_fin = 'EL' WHERE esta_fin = 'DE' AND colegio = '" & CODPK & "' and loginusuario = '" & Nom_usu_ses & "' and fc.agno = " & RC!agno
                conn.Execute Sql, LI
                'Inserta log
                If LI = 1 Then
                accion_detalle = "Se ha eliminado la cuponera con folio " & folio_cuponera _
                               & ", para el año " & RC!agno & ", con los cupones del " _
                               & primer_cupon & " al " & ultimo_cupon
                accion = "Eliminar Cuponera"
                Else
                accion = "Eliminar Cupon"
                If primer_cupon < ultimo_cupon Then
                    accion_detalle = "Se han eliminado los cupones del " & primer_cupon _
                                   & " al " & ultimo_cupon & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                Else
                    accion_detalle = "Se ha eliminado el cupon con folio " & primer_cupon _
                                   & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                End If
                End If
                'Call inserta_vitacora_cuponeras(run_apo, CLng(folio_cuponera), _
                '     CStr(accion), CStr(accion_detalle))
                'Fin de log
                RC.MoveNext
            Wend
        End If
        RC.Close
        
        
        'generar y anexar datos archivo de eliminacion ARCHIVO DE COBRANZA de cuponeras colegio segun año
        '****************************************************************************
        
        Sql = "SELECT distinct agno FROM fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fdc.folio_cuponera = fc.folio_cuponera and estado = 'MC' AND fc.colegio = '" & CODBC & "' and fdc.loginusuario = '" & Nom_usu_ses & "' ORDER BY fc.agno"
        RC.Open Sql, conn
        If RC.RecordCount > 0 Then
            ARCH_ELIBC = "C:\Reg_Cobranza" & Date & "\" & RetornaCampo("CFG", "valor", "param='CODBC'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_ELI.bci"
            While Not RC.EOF
                archivo = ARCH_ELIBC & "_" & RC!agno & "_ELI.bci"
                CrearArchBCI False, True, archivo, RC!agno, conn
                Sql = "SELECT * from fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fc.colegio = '" & CODBC & "' and fdc.loginusuario = '" & Nom_usu_ses & "' AND fdc.estado = 'MC' AND fdc.folio_cuponera = fc.folio_cuponera AND fc.agno = " & RC!agno & " ORDER BY fc.folio_cuponera, fdc.vcto "
                RCcupoNera.Open Sql, conn
                sw = False
                primer_cupon = 0
                ultimo_cupon = 0
                While Not RCcupoNera.EOF
                    If sw = False Then
                        primer_cupon = RCcupoNera("folio_cupon")
                        ultimo_cupon = RCcupoNera("folio_cupon")
                        folio_cuponera = RCcupoNera("folio_cuponera")
                        run_apo = RCcupoNera("rut_apo")
                        sw = True
                    End If
                    If primer_cupon > RCcupoNera("folio_cupon") Then
                        primer_cupon = RCcupoNera("folio_cupon")
                    End If
                    If ultimo_cupon < RCcupoNera("folio_cupon") Then
                        ultimo_cupon = RCcupoNera("folio_cupon")
                    End If
                    AgregarRegistroArchivoEliminacion archivo, False, RCcupoNera("rut_apo"), RCcupoNera("numero"), conn
                    Barra.Value = Barra.Value + 1
                    Barra.Refresh
                    Sql = "UPDATE fnza_deta_cupones fdc SET estado = 'EC' WHERE folio_cupon = " & RCcupoNera("folio_cupon")
                    conn.Execute Sql
                    RCcupoNera.MoveNext
                Wend
                MsgBox "El archivo de Eliminacion de cuponeras colegio se ha creado exitosamente. " & vbCrLf & "Se han eliminado un total de " & RCcupoNera.RecordCount & " cuponeras." & vbCrLf & "Y su hubicacion es : " & archivo & vbCrLf & "Para el año : " & RC!agno
                RCcupoNera.Close
                Sql = "UPDATE fnza_cuponeras fc SET esta_fin = 'EC' WHERE esta_fin = 'MC' AND colegio = '" & CODBC & "' and loginusuario = '" & Nom_usu_ses & "' and  fc.agno = " & RC!agno
                conn.Execute Sql, LI
                'Inserta log
                If LI = 1 Then
                accion_detalle = "Se ha eliminado la cuponera con folio " & folio_cuponera _
                               & ", para el año " & RC!agno & ", con los cupones del " _
                               & primer_cupon & " al " & ultimo_cupon
                accion = "Eliminar Cuponera"
                Else
                accion = "Eliminar Cupon"
                If primer_cupon < ultimo_cupon Then
                    accion_detalle = "Se han eliminado los cupones del " & primer_cupon _
                                   & " al " & ultimo_cupon & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                Else
                    accion_detalle = "Se ha eliminado el cupon con folio " & primer_cupon _
                                   & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                End If
                End If
                'Call inserta_vitacora_cuponeras(run_apo, CLng(folio_cuponera), _
                '     CStr(accion), CStr(accion_detalle))
                'Fin de log
                RC.MoveNext
            Wend
        End If
        RC.Close
        
        'generar y anexar datos archivo de eliminacion ARCHIVO DE COBRANZA de cuponeras colegio segun año
        'generar y anexar datos archivo de eliminacion ARCHIVO DE COBRANZA de cuponeras marta carcamo segun año
        '*********************************************************************************
        
        Sql = "SELECT distinct agno FROM fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fdc.folio_cuponera = fc.folio_cuponera and estado = 'MC' AND fc.colegio = '" & CODPK & "' and fdc.loginusuario = '" & Nom_usu_ses & "' ORDER BY fc.agno"
        RC.Open Sql, conn
        If RC.RecordCount > 0 Then
            ARCH_ELIPK = "C:\Reg_Cobranza" & Date & "\" & RetornaCampo("CFG", "valor", "param='CODPK'", conn) & "_" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") '& "_ELI.bci"
            While Not RC.EOF
                archivo = ARCH_ELIPK & "_" & RC!agno & "_ELI.bci"
                CrearArchBCI True, True, archivo, RC!agno, conn
                Sql = "SELECT * from fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fc.colegio = '" & CODPK & "' and fdc.loginusuario = '" & Nom_usu_ses & "' and fdc.estado = 'MC' and fdc.folio_cuponera = fc.folio_cuponera and fc.agno = " & RC!agno & " ORDER BY fc.folio_cuponera, fdc.vcto "
                RCcupoNera.Open Sql, conn
                sw = False
                primer_cupon = 0
                ultimo_cupon = 0
                While Not RCcupoNera.EOF
                    If sw = False Then
                        primer_cupon = RCcupoNera("folio_cupon")
                        ultimo_cupon = RCcupoNera("folio_cupon")
                        folio_cuponera = RCcupoNera("folio_cuponera")
                        run_apo = RCcupoNera("rut_apo")
                        sw = True
                    End If
                    If primer_cupon > RCcupoNera("folio_cupon") Then
                        primer_cupon = RCcupoNera("folio_cupon")
                    End If
                    If ultimo_cupon < RCcupoNera("folio_cupon") Then
                        ultimo_cupon = RCcupoNera("folio_cupon")
                    End If
                    AgregarRegistroArchivoEliminacion archivo, True, RCcupoNera("rut_apo"), RCcupoNera("numero"), conn
                    Barra.Value = Barra.Value + 1
                    Barra.Refresh
                    Sql = "UPDATE fnza_deta_cupones fdc SET estado = 'EC' WHERE folio_cupon = " & RCcupoNera("folio_cupon")
                    conn.Execute Sql
                    RCcupoNera.MoveNext
                Wend
                MsgBox "El archivo de Eliminacion de cuponeras Marta Carcamo se ha creado exitosamente. " & vbCrLf & "Se han eliminado un total de " & RCcupoNera.RecordCount & " cuponeras." & vbCrLf & "Y su hubicacion es : " & archivo & vbCrLf & "Para el año : " & RC!agno
                RCcupoNera.Close
                Sql = "UPDATE fnza_cuponeras fc SET esta_fin = 'EC' WHERE esta_fin = 'MC' AND colegio = '" & CODPK & "' and loginusuario = '" & Nom_usu_ses & "' and fc.agno = " & RC!agno
                conn.Execute Sql, LI
                'Inserta log
                If LI = 1 Then
                accion_detalle = "Se ha eliminado la cuponera con folio " & folio_cuponera _
                               & ", para el año " & RC!agno & ", con los cupones del " _
                               & primer_cupon & " al " & ultimo_cupon
                accion = "Eliminar Cuponera"
                Else
                accion = "Eliminar Cupon"
                If primer_cupon < ultimo_cupon Then
                    accion_detalle = "Se han eliminado los cupones del " & primer_cupon _
                                   & " al " & ultimo_cupon & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                Else
                    accion_detalle = "Se ha eliminado el cupon con folio " & primer_cupon _
                                   & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                End If
                End If
                'Call inserta_vitacora_cuponeras(run_apo, CLng(folio_cuponera), _
                '     CStr(accion), CStr(accion_detalle))
                'Fin de log
                RC.MoveNext
            Wend
        End If
        RC.Close
        
        
        'generar y anexar datos archivo de Reimprecion de cuponeras colegio segun año
        '***************************************************************************
        
        Sql = "SELECT distinct agno FROM fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fdc.folio_cuponera = fc.folio_cuponera and estado = 'RE' AND fc.colegio = '" & CODBC & "' and fdc.loginusuario = '" & Nom_usu_ses & "' ORDER BY fc.agno"
        RC.Open Sql, conn
        If RC.RecordCount > 0 Then
            While Not RC.EOF
                archivo = ARCH_REIBC & "_" & CStr(RC!agno) & "_REI.bci"
                CrearArchBCI False, False, archivo, RC!agno, conn
                Sql = "SELECT * from fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fc.colegio = '" & CODBC & "' and fdc.loginusuario = '" & Nom_usu_ses & "' and fdc.estado = 'RE' and fdc.folio_cuponera = fc.folio_cuponera and fc.agno = " & RC!agno & " ORDER BY fc.folio_cuponera, fdc.vcto "
                RCcupoNera.Open Sql, conn
                sw = False
                primer_cupon = 0
                ultimo_cupon = 0
                While Not RCcupoNera.EOF
                    If sw = False Then
                        primer_cupon = RCcupoNera("folio_cupon")
                        ultimo_cupon = RCcupoNera("folio_cupon")
                        folio_cuponera = RCcupoNera("folio_cuponera")
                        run_apo = RCcupoNera("rut_apo")
                        sw = True
                    End If
                    If primer_cupon > RCcupoNera("folio_cupon") Then
                        primer_cupon = RCcupoNera("folio_cupon")
                    End If
                    If ultimo_cupon < RCcupoNera("folio_cupon") Then
                        ultimo_cupon = RCcupoNera("folio_cupon")
                    End If
                    AgregarRegistroArchivoGeneracion archivo, RCcupoNera("rut_apo"), RCcupoNera("valor"), RCcupoNera("numero"), Format(RCcupoNera("vcto"), "yyyy-mm-dd"), conn
                    Barra.Value = Barra.Value + 1
                    Barra.Refresh
                    Sql = "UPDATE fnza_deta_cupones fdc SET estado = 'PE' WHERE folio_cupon = " & RCcupoNera("folio_cupon")
                    conn.Execute Sql
                    RCcupoNera.MoveNext
                Wend
                MsgBox "El archivo de Reimpresion de cuponeras colegio se ha creado exitosamente. " & vbCrLf & "Se han reimpreso un total de " & RCcupoNera.RecordCount & " cuponeras." & vbCrLf & "Y su hubicacion es : " & archivo & vbCrLf & "Para el año : " & RC!agno
                RCcupoNera.Close
                Sql = "UPDATE fnza_cuponeras fc SET esta_fin = 'PE' WHERE esta_fin = 'RE' AND colegio = '" & CODBC & "' and loginusuario = '" & Nom_usu_ses & "' and fc.agno = " & RC!agno
                conn.Execute Sql, LI
                'Inserta log
                If LI = 1 Then
                accion_detalle = "Se ha reimpreso la cuponera con folio " & folio_cuponera _
                               & ", para el año " & RC!agno & ", con los cupones del " _
                               & primer_cupon & " al " & ultimo_cupon
                accion = "Reimpresión de Cuponera"
                Else
                accion = "Reimprimir Cupon"
                If primer_cupon < ultimo_cupon Then
                    accion_detalle = "Se han reimpreso los cupones del " & primer_cupon _
                                   & " al " & ultimo_cupon & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                Else
                    accion_detalle = "Se ha reimpreso el cupon con folio " & primer_cupon _
                                   & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                End If
                End If
                'Call inserta_vitacora_cuponeras(run_apo, CLng(folio_cuponera), _
                '     CStr(accion), CStr(accion_detalle))
                'Fin de log
                RC.MoveNext
            Wend
        End If
        RC.Close
        'generar y anexar datos archivo de Reimprecion de cuponeras marta carcamo segun año
        '*********************************************************************************
        
        Sql = "SELECT distinct agno FROM fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fdc.folio_cuponera = fc.folio_cuponera and estado = 'RE' AND fc.colegio = '" & CODPK & "' and fdc.loginusuario = '" & Nom_usu_ses & "' ORDER BY fc.agno"
        RC.Open Sql, conn
        If RC.RecordCount > 0 Then
            While Not RC.EOF
                archivo = ARCH_REIPK & "_" & RC!agno & "_REI.bci"
                CrearArchBCI True, False, archivo, RC!agno, conn
                Sql = "SELECT * from fnza_cuponeras fc, fnza_deta_cupones fdc WHERE fc.colegio = '" & CODPK & "' and fdc.loginusuario = '" & Nom_usu_ses & "' and fdc.estado = 'RE' and fdc.folio_cuponera = fc.folio_cuponera and fc.agno = " & RC!agno & " ORDER BY fc.folio_cuponera, fdc.vcto "
                RCcupoNera.Open Sql, conn
                sw = False
                primer_cupon = 0
                ultimo_cupon = 0
                While Not RCcupoNera.EOF
                    If sw = False Then
                        primer_cupon = RCcupoNera("folio_cupon")
                        ultimo_cupon = RCcupoNera("folio_cupon")
                        folio_cuponera = RCcupoNera("folio_cuponera")
                        run_apo = RCcupoNera("rut_apo")
                        sw = True
                    End If
                    If primer_cupon > RCcupoNera("folio_cupon") Then
                        primer_cupon = RCcupoNera("folio_cupon")
                    End If
                    If ultimo_cupon < RCcupoNera("folio_cupon") Then
                        ultimo_cupon = RCcupoNera("folio_cupon")
                    End If
                    AgregarRegistroArchivoGeneracion archivo, RCcupoNera("rut_apo"), RCcupoNera("valor"), RCcupoNera("numero"), Format(RCcupoNera("vcto"), "yyyy-mm-dd"), conn
                    Barra.Value = Barra.Value + 1
                    Barra.Refresh
                    Sql = "UPDATE fnza_deta_cupones fdc SET estado = 'PE' WHERE folio_cupon = " & RCcupoNera("folio_cupon")
                    conn.Execute Sql
                    RCcupoNera.MoveNext
                Wend
                MsgBox "El archivo de Reimpresion de cuponeras Marta Carcamo se ha creado exitosamente. " & vbCrLf & "Se han reimpreso un total de " & RCcupoNera.RecordCount & " cuponeras." & vbCrLf & "Y su hubicacion es : " & archivo & vbCrLf & "Para el año : " & RC!agno
                RCcupoNera.Close
                Sql = "UPDATE fnza_cuponeras fc SET esta_fin = 'PE' WHERE esta_fin = 'RE' AND colegio = '" & CODPK & "' and loginusuario = '" & Nom_usu_ses & "' and fc.agno = " & RC!agno
                conn.Execute Sql, LI
                'Inserta log
                If LI = 1 Then
                accion_detalle = "Se ha reimpreso la cuponera con folio " & folio_cuponera _
                               & ", para el año " & RC!agno & ", con los cupones del " _
                               & primer_cupon & " al " & ultimo_cupon
                accion = "Reimpresión de Cuponera"
                Else
                accion = "Reimprimir Cupon"
                If primer_cupon < ultimo_cupon Then
                    accion_detalle = "Se han reimpreso los cupones del " & primer_cupon _
                                   & " al " & ultimo_cupon & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                Else
                    accion_detalle = "Se ha reimpreso el cupon con folio " & primer_cupon _
                                   & ", para la cuponera con folio " _
                                   & folio_cuponera & ", del año " & RC!agno
                End If
                End If
                'Call inserta_vitacora_cuponeras(run_apo, CLng(folio_cuponera), _
                '     CStr(accion), CStr(accion_detalle))
                'Fin de log
                RC.MoveNext
            Wend
        End If
        RC.Close
        frm_mensaje.Visible = False
    Else
        MsgBox "no se a generado ningun archivo, ya que no hay cambios disponibles"
    End If
    
    Btn_Generar.Enabled = True
    cmg_Reimprimir.Enabled = True
    cmd_EliminarCuponera.Enabled = True
    Cancelar.Enabled = True
    Btn_Limpiar.Enabled = True
    Btn_Salir.Enabled = True
    Btn_Ver_Alumno_Click
    mshflex_detacuponera.ClearStructure
    mshflex_detacuponera.Rows = 2
End Sub

Private Sub Btn_editar_Click()
    Dim folio_cupon As Long
    folio_cupon = val(mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 2))
    If txt_NuevoValor = Lbl_valor_cupon Then
        If MsgBox("Desea Reimprimir el cupon?", vbQuestion + vbYesNo) = vbYes Then
            reimprime_cupon folio_cupon
        End If
    Else
        If elimina_cupon(folio_cupon) = True Then
            If agrega_cupon(mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 3), _
            mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 4), _
            mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 5), _
            val(txt_NuevoValor), _
            mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 9)) Then
                MsgBox "La Modificacion del Cupon ha sido Realizado sin problemas"
            Else
                MsgBox "Problemas en la creación del cupon"
            End If
        Else
            MsgBox "Cupon no pudo ser eliminado"
        End If
    End If
    Btn_CancelarEdicion_Click
End Sub

Private Sub btn_eliminar_Click()
    Dim folio_cupon As Long
    folio_cupon = val(mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 2))
    If MsgBox("Esta a punto de eliminar el cupon, desea continuar?", vbQuestion + vbYesNo) = vbYes Then
        If elimina_cupon(folio_cupon) = True Then
            MsgBox "Cupon Eliminado"
        Else
            MsgBox "Cupon no pudo ser eliminado"
        End If
    End If
    Btn_CancelarEdicion_Click
End Sub

Private Sub Btn_General_Click()
    Dim colegio As String
    Dim CheckGen As String
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Cambio = False
    If opt_tipo(0).Value = True Then
       If text_passwd.Text = "cuponeras.cuota00" Then genera_ct00_masivo
    Else
       If text_passwd.Text <> "muerte" And text_passwd.Text <> "suicidate" Then Exit Sub
       If text_passwd.Text = "muerte" Then genera_cup_masivo
       If text_passwd.Text = "suicidate" Then genera_cup_no_generados
    End If
End Sub
Private Sub genera_cup_no_generados()
    Dim institucion As Boolean
    var_sql = ""
    Dim TotalCup  As Double
    '*************************************************************************************
    'proceso para crear las cuponeras de los apoderados que tienen hijos pero no cuponeras
    
    MsgBox "El Proceso a ejecutar tomara tiempo en terminar, por favor espere a que termine", vbInformation, "Apoderados Sin Cuponeras"
    If cb_institucion.Text = "Colegio" Then
        colegio = RetornaCampo("cfg", "valor", "param='CODBC'", conn)
        var_sql = "SELECT d.* FROM (SELECT DISTINCT aa.rut_apo, aa.agno FROM apo_x_alu as aa, matriculas as m WHERE aa.ci_alu=m.ci_alu AND aa.agno=m.anno_mat AND m.anno_mat=2006 AND m.confirmado='SI' AND m.cod_ens<>'996' AND m.ind_cup=1) as d LEFT JOIN fnza_cuponeras as fc ON d.rut_apo=fc.rut_apo AND d.agno=fc.agno AND (fc.esta_fin='PE' OR esta_fin = 'GE') AND LENGTH(fc.colegio)=6 WHERE fc.rut_apo is null;"
    ElseIf cb_institucion.Text = "Pre Kinder" Then
        colegio = RetornaCampo("cfg", "valor", "param='CODPK'", conn)
        var_sql = "SELECT d.* FROM (SELECT DISTINCT aa.rut_apo, aa.agno FROM apo_x_alu as aa, matriculas as m WHERE aa.ci_alu=m.ci_alu AND aa.agno=m.anno_mat AND m.anno_mat=2006 AND m.confirmado='SI' AND m.cod_ens='996' AND m.ind_cup=1) as d LEFT JOIN fnza_cuponeras as fc ON d.rut_apo=fc.rut_apo AND d.agno=fc.agno AND (fc.esta_fin='PE' OR esta_fin = 'GE') AND LENGTH(fc.colegio)=9 WHERE fc.rut_apo is null;"
    Else
        MsgBox "Error no esta seleccionado el tipo de busqueda"
        Exit Sub
    End If
    
    Set objRS = New ADODB.Recordset
    objRS.Open var_sql, conn
    
    If cb_institucion.Text = "Colegio" Then
        institucion = False
    Else
        institucion = True
    End If
    
    If objRS.RecordCount > 0 Then
        pgBar.Min = 0
        pgBar.Max = objRS.RecordCount
        TotalCup = objRS.RecordCount
        pgBar.Value = pgBar.Min
        objRS.MoveFirst
        For i = 1 To objRS.RecordCount
            crea_cuponera objRS!rut_apo, val(Combo_agno_general.Text), institucion, conn
            objRS.MoveNext
            pgBar.Value = i
            pgBar.Refresh
        Next i
        If TotalCup > 0 Then
            MsgBox "Hay " & TotalCup & " creadas en espera de creacion de archivo  ", vbInformation, "Proceso finalizado."
        Else
            MsgBox "no hay apoderados sin cuponeras"
        End If
    End If
    '*******************************************************************************
    'proceso para eliminar las cuponeras de los apoderados que ya no tienen hijos
    
    MsgBox "El Proceso a ejecutar tomara tiempo en terminar, por favor espere a que termine", vbInformation, "Apoderados Con Cuponeras sin Alumnos"
    
    var_sql = "select * from fnza_cuponeras fc left join apo_x_alu axu on axu.rut_apo = fc.rut_apo where ci_alu is null and esta_fin = 'PE' and fc.colegio = '" & colegio & "';"
    
    objRS.Open var_sql, conn
    
    If objRS.RecordCount > 0 Then
        pgBar.Min = 0
        pgBar.Max = objRS.RecordCount
        TotalCup = objRS.RecordCount
        pgBar.Value = pgBar.Min
        objRS.MoveFirst
        For i = 1 To objRS.RecordCount
            elimina_cuponera objRS!folio_cuponera
            objRS.MoveNext
            pgBar.Value = i
            pgBar.Refresh
        Next i
        If TotalCup > 0 Then
            MsgBox "Hay " & TotalCup & " Eliminadas en espera de creacion de archivo  ", vbInformation, "Proceso finalizado."
        Else
            MsgBox "no hay apoderados sin cuponeras"
        End If
    End If
    
    '************************************************************************************************************
    'proceso para crear las cuponeras de los apoderados que tienen cuponeras pero con valores distintos a finanza
    
    MsgBox "El Proceso a ejecutar tomara tiempo en terminar, por favor espere a que termine", vbInformation, "Verificar Cuponeras"
    
    var_sql = "select distinct fc.rut_apo,fc.agno, fc.folio_cuponera from fnza_cuponeras fc, fnza_deta_cupones fdc, ( SELECT fm.run_apo, fdm.fech_venc, fdm.folio_mens, sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' , fdm.mes, fdm.agno, fdm.colegio, fdm.concepto FROM fnza_detalle_mensualidad as fdm, (select distinct m.ci_alu,m.anno_mat  from apo_x_alu axu, matriculas m where m.anno_mat = axu.agno and m.ci_alu = axu.ci_alu and  m.anno_mat = " & Combo_agno_general.Text & " and m.ind_cup = 1 and m.confirmado = 'SI') as d, fnza_mensualidad as fm WHERE fdm.run_alu=d.ci_alu AND fdm.agno=d.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno=" & Combo_agno_general.Text & " and fdm.monto_tot_cuota>0 AND (fdm.concepto<>'ct00') and fdm.colegio = '" & colegio & "'  and fdm.mes = fm.mes  GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno, fdm.colegio ORDER BY fm.run_apo, fdm.folio_mens, fdm.colegio, fdm.mes, fdm.agno) as x " & _
              "where fdc.folio_cuponera = fc.folio_cuponera and x.run_apo = fc.rut_apo and  x.mes = fdc.mes and fc.colegio = x.colegio and  fc.agno = x.agno and fdc.valor <> x.cuota and (fdc.estado = 'PE' or fdc.estado = 'GE') order by rut_apo;"
    objRS.Open var_sql, conn
    
    If objRS.RecordCount > 0 Then
        If MsgBox("Hay un total de " & objRS.RecordCount & " cuponeras en que los precios no corresponden a finanza" & vbCrLf & "Desea eliminar las antiguas y crear las correctas?", vbQuestion + vbYesNo) = vbYes Then
            pgBar.Min = 0
            pgBar.Max = objRS.RecordCount
            TotalCup = objRS.RecordCount
            pgBar.Value = pgBar.Min
            objRS.MoveFirst
            For i = 1 To objRS.RecordCount
                elimina_cuponera objRS!folio_cuponera
                crea_cuponera objRS!rut_apo, val(Combo_agno_general.Text), institucion, conn
                objRS.MoveNext
                pgBar.Value = i
                pgBar.Refresh
            Next i
            If TotalCup > 0 Then
                MsgBox "Hay " & TotalCup & " Eliminadas en espera de creacion de archivo  ", vbInformation, "Proceso finalizado."
            Else
                MsgBox "no hay apoderados sin cuponeras"
            End If
        End If
    End If
End Sub

Private Sub genera_cup_masivo()
    Dim institucion As Boolean
'    If cb_institucion.Text = "Colegio" Then
        colegio = RetornaCampo("cfg", "valor", "param='CODBC'", conn)
        CheckGen = RetornaCampo("cfg", "valor", "param='GENBC_READY' AND agno=" & Combo_agno_general.Text, conn)
'    ElseIf cb_institucion.Text = "Pre Kinder" Then
'        colegio = RetornaCampo("cfg", "valor", "param='CODPK'", conn)
'        CheckGen = RetornaCampo("cfg", "valor", "param='GENPK_READY' AND agno=" & Combo_agno_general.Text, conn)
'    Else
'        MsgBox "Error no esta seleccionado el tipo de busqueda"
'        Exit Sub
'    End If
    
    If CheckGen = "SI" Then
        MsgBox "Este proceso ya fue generado", vbInformation, "Bloqueado"
        Exit Sub
    End If
    
    Set objRS = New ADODB.Recordset
    
    var_sql = "drop table if exists temp_apos"
    conn.Execute var_sql
    var_sql = "drop table if exists temp_cupo"
    conn.Execute var_sql
            
'    var_sql = "SELECT DISTINCT fm.run_apo FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm" _
'            & " WHERE fdm.run_alu=m.ci_alu AND fdm.agno=m.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno=" & Combo_agno_general.Text & " and m.ind_cup=1 and fdm.monto_tot_cuota>0 AND (fdm.concepto<>'ct00')  and m.confirmado='SI' and fdm.colegio='" & colegio & "'" _
'            & " GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno, fdm.colegio ORDER BY fm.run_apo;"
    
    var_sql = "create table temp_apos type = MyISAM" _
            & " SELECT DISTINCT fm.run_apo" _
            & " FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm" _
            & " Where fdm.run_alu = m.ci_alu And fdm.agno = m.anno_mat And fdm.folio_mens = fm.folio_mens" _
            & " And fdm.agno = " & Combo_agno_general.Text & " And m.ind_cup = 1" _
            & " and fdm.monto_tot_cuota>0 AND (fdm.concepto<>'ct00') and m.confirmado='SI' AND fec_mat_r IS NOT NULL AND fec_mat_r <> '0000-00-00'" _
            & " GROUP BY fm.run_apo, fm.mes, fm.agno ORDER BY fm.run_apo;"
    conn.Execute var_sql

    var_sql = "create table temp_cupo type = MyISAM" _
            & " (SELECT rut_apo FROM fnza_cuponeras" _
            & " WHERE (esta_fin='PE' or esta_fin = 'GE' or esta_fin = 'RE') AND agno = " & Combo_agno_general.Text _
            & " and concepto <> 'ct00')"
    conn.Execute var_sql
                    
    var_sql = "select * from temp_apos where run_apo not in (SELECT rut_apo FROM temp_cupo)"
    objRS.Open var_sql, conn
        
    If objRS.RecordCount = 0 Then
        Exit Sub
    End If
    
    pgBar.Min = 0
    pgBar.Max = objRS.RecordCount
    pgBar.Value = pgBar.Min
    objRS.MoveFirst
    'If cb_institucion.Text = "Colegio" Then
        institucion = False
    'Else
        'institucion = True
    'End If
    For i = 1 To objRS.RecordCount
        crea_cuponera objRS!run_apo, val(Combo_agno_general.Text), institucion, conn
        objRS.MoveNext
        pgBar.Value = i
        pgBar.Refresh
    Next i
    
    'bloqueo de inserciones de cuponeras
    conn.Execute "REPLACE INTO CFG(param,valor,descripcion,agno) VALUES('" & IIf(cb_institucion.Text = "Pre Kinder", "GENPK_READY", "GENBC_READY") & "','SI','Bloquear Generacion masiva de cuponera para " & colegio & ".','" & Trim(Combo_agno_general.Text) & "');"
    
    MsgBox "El archivo de cuponeras:" & vbCrLf & vbCrLf & IIf(cb_institucion.Text = "Pre Kinder", ARCH_GENPK, ARCH_GENBC) & vbCrLf & vbCrLf & "... fue creado exitosamente", vbInformation, "Proceso finalizado."
End Sub
Private Sub Btn_Generar_Click()
    Frame_Cuponeras.Enabled = False
    Frame_Cupones.Enabled = False
    Btn_Generar.Enabled = False
    cmd_EliminarCuponera.Enabled = False
    cmg_Reimprimir.Enabled = False
    Btn_Limpiar.Enabled = False
    Btn_Salir.Enabled = False
    Cancelar.Enabled = False
    btn_crear.Enabled = False
    Frame_CrearCuponera.Visible = True
    Cb_Empresa.Clear
    Cb_Empresa.AddItem "Colegio"
    Cb_Empresa.AddItem "Pre Kinder"
'    cb_agnos.AddItem = Format(Year(), "yyyy")
'    cb_agnos.AddItem = Format(Year(), "yyyy") - 1
End Sub

Private Sub Btn_Limpiar_Click()
    Frame2.Enabled = True
    mshflex_cuponeras.ClearStructure
    mshflex_cuponeras.Rows = 2
    mshflex_detacuponera.ClearStructure
    mshflex_detacuponera.Rows = 2
    Grilla.ClearStructure
    Grilla.Rows = 2
    Btn_Generar.Enabled = True
    cmg_Reimprimir.Enabled = True
    cmd_EliminarCuponera.Enabled = True
    Text1.Text = ""
    text2.Text = ""
    Text3.Text = ""
    Text4.Text = ""
    Text1.Locked = False
    text2.Locked = False
    Text3.Locked = False
    Text4.Locked = False
    
End Sub

Private Sub Btn_Salir_Click()
    Unload Me
End Sub

Private Sub Btn_Ver_Alumno_Click()
    Frame2.Enabled = False
    Text1.Locked = True
    text2.Locked = True
    Cambio = False
    'actualiza_cuponeras_detalle (lbl_Run)
    'actualiza_cuponeras_detalle_cuotas00 (lbl_Run)
    llena_grilla_fnzacuponera (lbl_Run)
    If mshflex_cuponeras.TextMatrix(1, 1) <> "" Then
        Call marca_grilla(mshflex_cuponeras, 8)
        mshflex_cuponeras.Row = 1
        mshflex_cuponeras.ColSel = 1
        Btn_Generar.Enabled = False
        cmg_Reimprimir.Enabled = False
        cmd_EliminarCuponera.Enabled = False
    End If
End Sub

Private Sub Btn_Ver_Apoderado_Click()
    Frame2.Enabled = False
    Text3.Locked = True
    Text4.Locked = True
    'actualiza_cuponeras_detalle (Text4.Text)
    'actualiza_cuponeras_detalle_cuotas00 (Text4.Text)
    Cambio = False
    llena_grilla_fnzacuponera (Text4)
    If mshflex_cuponeras.TextMatrix(1, 1) <> "" Then
        mshflex_cuponeras.ColWidth(1) = 0
        Call marca_grilla(mshflex_cuponeras, 8)
        mshflex_cuponeras.Row = 1
        mshflex_cuponeras.ColSel = 1
        Btn_Generar.Enabled = True
        cmg_Reimprimir.Enabled = True
        cmd_EliminarCuponera.Enabled = True
    End If
End Sub

Private Sub Cancelar_Click()
    Dim Resp As Integer
    Dim RC As ADODB.Recordset
    Set RC = New ADODB.Recordset
    
    If mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1) = "" Then
        MsgBox "Debe Seleccionar una cuponera"
        Exit Sub
    End If
    Resp = MsgBox("Desea cancelar todas las operaciones que se estan efectuando?", vbYesNo + vbQuestion)
    If Resp = 6 Then
        Estado = RetornaCampo("fnza_cod_estadocupon", "estado", "desc_estado='" & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 8) & "'", conn)
        If Estado = "RE" Then
            Sql = "update fnza_cuponeras set esta_fin = 'PE' where esta_fin = 'RE' and rut_apo = '" & lbl_Run.Caption & "' and loginusuario = '" & Nom_usu_ses & "' and folio_cuponera = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)
            conn.Execute Sql
            Sql = "update fnza_deta_cupones set estado = 'PE' where estado = 'RE' and loginusuario = '" & Nom_usu_ses & "' and folio_cuponera = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)
            conn.Execute Sql
        End If
        If Estado = "GE" Then
            Sql = "DELETE FROM fnza_cuponeras where esta_fin = 'GE' and rut_apo = '" & lbl_Run.Caption & "' and loginusuario = '" & Nom_usu_ses & "' and folio_cuponera = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)
            conn.Execute Sql
            Sql = "DELETE FROM fnza_deta_cupones where estado = 'GE' and loginusuario = '" & Nom_usu_ses & "' and folio_cuponera = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)
            conn.Execute Sql
        End If
        If Estado = "DE" Then
            Sql = "SELECT * FROM fnza_cuponeras WHERE (esta_fin = 'GE' or esta_fin = 'RE' or esta_fin = 'PE') and rut_apo = '" & lbl_Run.Caption & "' and agno = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 3)
            RC.Open Sql, conn
            If RC.RecordCount = 0 Then
                Sql = "update fnza_cuponeras set esta_fin = 'PE' where esta_fin = 'DE' and rut_apo = '" & lbl_Run.Caption & "' and loginusuario = '" & Nom_usu_ses & "' and folio_cuponera = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)
                conn.Execute Sql
                Sql = "update fnza_deta_cupones set estado = 'PE' where estado = 'DE' and loginusuario = '" & Nom_usu_ses & "' and folio_cuponera = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)
                conn.Execute Sql
            Else
                MsgBox "No se puede cancelar la eliminacion de esta cuponera, el apoderado cuenta con una orden de generacion o eliminacion activa" & vbCrLf & "Cancele la generacion o reimpresion de otra cuponera antes de repetir esta orden", vbInformation
            End If
        End If
        llena_grilla_fnzacuponera (lbl_Run)
        mshflex_cuponeras.ColWidth(1) = 0
        Call marca_grilla(mshflex_cuponeras, 8)
        mshflex_cuponeras.Row = 1
        mshflex_cuponeras.ColSel = 1
        mshflex_detacuponera.ClearStructure
        mshflex_detacuponera.Rows = 2
    End If
    
End Sub

Private Sub cmb_lista_Click()
    If Lista Then
        cmb_lista.Caption = "Listar Tareas"
        Btn_Limpiar_Click
        Lista = False
        Btn_Generar.Enabled = True
        cmg_Reimprimir.Enabled = True
        cmd_EliminarCuponera.Enabled = True
        Cancelar.Enabled = True
        Btn_Salir.Enabled = True
        Btn_Limpiar.Enabled = True
        btn_crear.Enabled = True
        Option1(0).Enabled = True
        Option1(1).Enabled = True
        Option1(2).Enabled = True
        Text1.Enabled = True
        text2.Enabled = True
        Text3.Enabled = True
        Text4.Enabled = True
        Grilla.ClearStructure
        Grilla.Rows = 2
    Else
        llena_grilla_fnzacuponeracambios
        If mshflex_cuponeras.TextMatrix(1, 0) <> "" Then
            mshflex_cuponeras.Row = 1
            mshflex_cuponeras.ColSel = 1
        End If
        cmb_lista.Caption = "Limpiar"
        Lista = True
        Btn_Generar.Enabled = False
        cmg_Reimprimir.Enabled = False
        cmd_EliminarCuponera.Enabled = False
        Cancelar.Enabled = False
        Btn_Limpiar.Enabled = False
        btn_crear.Enabled = False
        Option1(0).Enabled = False
        Option1(1).Enabled = False
        Option1(2).Enabled = False
        Text1.Enabled = False
        text2.Enabled = False
        Text3.Enabled = False
        Text4.Enabled = False
    
    End If
End Sub

Private Sub cmd__Click()
    Dim var_sql As String
    Dim colegio As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    
    var_sql = "select valor from cfg where param = 'CODBC'"
    rec.Open var_sql, conn
    colegio = rec("valor")
    rec.Close
    
    var_sql = "drop table if exists apos"
    conn.Execute var_sql
    
    var_sql = "CREATE TABLE `apos` (" _
            & "`rut_apo` varchar(10) NOT NULL default ''," _
            & " PRIMARY KEY  (`rut_apo`)" _
            & ") ENGINE=MyISAM DEFAULT CHARSET=latin1;"
    conn.Execute var_sql
    
    var_sql = "insert into apos(rut_apo)" _
            & " select rut_apo from apo_x_alu axa,matriculas m,alu_x_cur axc" _
            & " Where axa.ci_alu = m.ci_alu And axa.agno = m.anno_mat And axa.agno = " & cb_agno_masivos.Text _
            & " and m.ci_alu = axc.ci_alu and m.anno_mat = axc.agno and m.num_gra = axc.num_gra and" _
            & " m.cod_ens = axc.cod_ens and fec_cam = '1111-11-11'" _
            & " group by rut_apo;"
    conn.Execute var_sql
      
'    If colegio = "SNM001" Then
'        var_sql = "update fnza_deta_cupones fdc,fnza_cuponeras fc," _
'                & "apos" _
'                & " set estado = 'MC',fc.loginusuario = '" & Nom_usu_ses & "',fdc.loginusuario = '" & Nom_usu_ses & "'" _
'                & " where fc.folio_cuponera = fdc.folio_cuponera and concepto <> 'ct00'" _
'                & " and estado = 'PE' and adddate(vcto,20) < current_date and mes > 8 " _
'                & " and fc.agno = " & cb_agno_masivos.Text & " and fc.rut_apo = apos.rut_apo"
'        conn.Execute var_sql
'    Else
'        var_sql = "update fnza_deta_cupones fdc,fnza_cuponeras fc," _
'                & "apos" _
'                & " set estado = 'MC',fc.loginusuario = '" & Nom_usu_ses & "',fdc.loginusuario = '" & Nom_usu_ses & "'" _
'                & " where fc.folio_cuponera = fdc.folio_cuponera and concepto <> 'ct00'" _
'                & " and estado = 'PE' and adddate(vcto,20) < current_date " _
'                & " and fc.agno = " & cb_agno_masivos.Text & " and fc.rut_apo = apos.rut_apo"
'        conn.Execute var_sql
'    End If
    
'    If colegio = "SNM001" Then
'        var_sql = "update fnza_deta_cupones fdc,fnza_cuponeras fc," _
'                & "apos" _
'                & " set estado = 'MC',fc.loginusuario = '" & Nom_usu_ses & "',fdc.loginusuario = '" & Nom_usu_ses & "'" _
'                & " where fc.folio_cuponera = fdc.folio_cuponera and concepto <> 'ct00'" _
'                & " and estado = 'PE' and adddate(vcto,20) < current_date and mes > 8 " _
'                & " and fc.agno = " & cb_agno_masivos.Text & " and fc.rut_apo = apos.rut_apo"
'        conn.Execute var_sql
'    Else
        var_sql = "update fnza_deta_cupones fdc,fnza_cuponeras fc," _
                & "apos" _
                & " set estado = 'MC',fc.loginusuario = '" & Nom_usu_ses & "',fdc.loginusuario = '" & Nom_usu_ses & "'" _
                & " where fc.folio_cuponera = fdc.folio_cuponera and concepto <> 'ct00'" _
                & " and estado = 'PE' and adddate(vcto,20) < current_date " _
                & " and fc.agno = " & cb_agno_masivos.Text & " and fc.rut_apo = apos.rut_apo"
        conn.Execute var_sql
'    End If
    
End Sub

'Private Sub cmd_actualizar_masivo_Click()
'    Dim objRS As ADODB.Recordset
'    Set objRS = New ADODB.Recordset
'    Dim var_sql As String
'    'Dim rut As String
'    var_sql = "SELECT fm.run_apo, fdm.folio_mens, sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' , fdm.mes, fdm.agno, fdm.colegio, fdm.concepto FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm" _
'            & " WHERE fdm.run_alu=m.ci_alu AND fdm.agno=m.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno = " & Anno & " and m.ind_cup=1 and fdm.monto_tot_cuota>0 AND (fdm.concepto<>'ct00')" _
'            & " GROUP BY fm.run_apo"
'    objRS.Open var_sql, conn
'    progreso.Value = 0
'    progreso.Min = 0
'    progreso.Max = objRS.RecordCount
'    Frame_Actualizacion_Masivo.Visible = True
'    Frame_Actualizacion_Masivo.Caption = "Actualizar Masivo"
'    While Not objRS.EOF
'        actualiza_cuponeras_detalle (objRS("run_apo"))
'        objRS.MoveNext
'        progreso.Value = progreso.Value + 1
'    Wend
'    objRS.Close
'    Frame_Actualizacion_Masivo.Visible = False
'End Sub

Private Sub cmd_EliminarCuponera_Click()
    If mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1) = "" Then
        MsgBox "Debe Seleccionar una cuponera"
        Exit Sub
    End If
    If elimina_cuponera(mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)) Then
        MsgBox "La cuponera fue eliminada exitosamente", vbInformation
    End If
    llena_grilla_fnzacuponera (lbl_Run)
    mshflex_cuponeras.ColWidth(1) = 0
    Call marca_grilla(mshflex_cuponeras, 8)
    mshflex_cuponeras.Row = 1
    mshflex_cuponeras.ColSel = 1
    mshflex_detacuponera.ClearStructure
    mshflex_detacuponera.Rows = 2
End Sub

Private Sub cmdGenerarCuponera_Click()
    Dim rut_apo As String
    Dim Bol As Boolean
    If Option1(0).Value = True Then
        'nada por ahora
        Exit Sub
    ElseIf Option1(1).Value = True Then
        rut_apo = Text4.Text
    ElseIf Option1(2).Value = True Then
        rut_apo = lbl_Run.Caption
    Else
        MsgBox "Error no esta seleccionado el tipo de busqueda"
        Exit Sub
    End If
    '"13656233-9"
    If Cb_Empresa.Text = "Pre Kinder" Then
        Bol = True
    Else
        Bol = False
    End If
    
    If crea_cuponera(rut_apo, cb_agnos.Text, Bol, conn) = True Then
        MsgBox "La cuponera fue creada con exito", vbInformation
    Else
        MsgBox "La cuponera No fue creada", vbInformation
    End If
    Call llena_grilla_fnzacuponera(rut_apo)
    Call marca_grilla(mshflex_cuponeras, 8)
    mshflex_detacuponera.ClearStructure
    mshflex_detacuponera.Rows = 2
    Grilla.ClearStructure
    Grilla.Rows = 2
    Btn_CancelarCuponera_Click
End Sub

Private Sub Command3_Click()
    Unload Me
End Sub

Private Sub cmg_Reimprimir_Click()
    Dim Sql As String
    Dim affected2 As Integer
    Sql = "UPDATE fnza_cuponeras SET esta_fin = 'RE', loginusuario = '" & Nom_usu_ses & "' where esta_fin = 'PE' AND folio_cuponera = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)
    conn.Execute Sql, Affected
    Sql = "UPDATE fnza_deta_cupones SET estado = 'RE', loginusuario = '" & Nom_usu_ses & "' where estado = 'PE' AND folio_cuponera = " & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 1)
    conn.Execute Sql, affected2
    If affected2 = 0 And Affected = 0 Then
        MsgBox "No se reimprimio la cuponera, porque es una cuponera eliminada o ya se encontraba en la lista de impresion", vbInformation
    Else
        MsgBox "la cuponera se encuentra en la lista de impresion", vbInformation
    End If
    llena_grilla_fnzacuponera (lbl_Run)
    mshflex_cuponeras.ColWidth(1) = 0
    Call marca_grilla(mshflex_cuponeras, 8)
    mshflex_cuponeras.Row = 1
    mshflex_cuponeras.ColSel = 1
    mshflex_detacuponera.ClearStructure
    mshflex_detacuponera.Rows = 2
End Sub

Private Sub Command1_Click()
    Dim ruta As String
    Dim largo As Integer
    Dim rut As String
    Dim i, Pos As Integer
    Dim correo_parte1 As String
    Dim correo_parte2 As String
    Dim cod_colegio As String
    Dim cod_prekinder As String
    Dim Registro As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim vat_sql As String
    'Dim i As Integer
    
    If txt_password.Text <> "el tulipan negro" Then
        MsgBox "Contraseña Incorrecta", vbInformation, "Password"
        Exit Sub
    End If
    Frame_EliminacionMasivos.Enabled = False
    
   

'    RmDir "C:\Reg_Cobranza_" & Date
 
    
    
    MkDir "C:\Reg_Cobranza_" & Date
    
    var_sql = "select valor from cfg where param = 'CCBC'"
    objRS.Open var_sql, conn
    cod_colegio = objRS("valor")
    objRS.Close
    var_sql = "select valor from cfg where param = 'CCPK'"
    objRS.Open var_sql, conn
    cod_prekinder = objRS("valor")
    objRS.Close
    ruta = "C:\Reg_Cobranza_" & Date & "\deuda_" & cod_colegio & "_" & Date & ".txt"
    'ruta = "C:\Reg_Cobranza" & "\deuda_" & cod_colegio & ".txt"
        
    var_sql = "CREATE TABLE IF NOT EXISTS `fnza_cobranza` (" _
            & "`folio` int(11) NOT NULL auto_increment," _
            & "rut_cli varchar(10)," _
            & "`monto_total` double default NULL," _
            & "`moneda` bigint(20) NOT NULL default '0'," _
            & "`producto` char(3) NOT NULL default ''," _
            & "`num_cuota` bigint(20)," _
            & "`fech_venc` date," _
            & "`colegio` varchar(15)," _
            & "`nro_cli` varbinary(44) default NULL," _
            & "`estado` char(2) NOT NULL default ''," _
            & "`fecha_cobranza` datetime NOT NULL default '0000-00-00 00:00:00'," _
            & "`fecha_eliminacion` datetime NOT NULL default '0000-00-00 00:00:00'," _
            & "`agno` bigint(20) unsigned default NULL," _
            & "`mes` bigint(20) unsigned default NULL," _
            & "PRIMARY KEY  (`folio`)" _
            & ") ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;"
    conn.Execute var_sql
    var_sql = "update apoderados set comuna_apo = 13119 where comuna_apo is null"
    conn.Execute var_sql
    
    If Option2(0).Value = True Then
    
    'Actualizar cuponeras
   ' actualiza_cuponeras_masivo

    'Eliminar masivo
    'cmd__Click

    'Crear Archivo BCI
    'btn_crear_Click

     'Generar cuponeras a alumnos que no tienen
    'crear_cuponeras_alu_sin_cupo
    
    var_sql = "insert into fnza_cobranza(rut_cli,monto_total,moneda,producto,num_cuota," _
            & "fech_venc,colegio,nro_cli,estado,fecha_cobranza,fecha_eliminacion,agno,mes)" _
            & " select rut_cli,monto_total,moneda,producto,num_cuota,fech_venc,t.colegio,nro_cli," _
            & "estado,fecha_cobranza,fecha_eliminacion,agno,mes" _
            & " From" _
            & " (select rut_apo as rut_cli,sum(monto_tot_cuota-monto_abono) as monto_total," _
            & "1 as moneda,'CUP' as producto,(mes-2) as num_cuota," _
            & "fech_venc,dm.colegio,concat(dm.agno," _
            & "if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado,now() as fecha_cobranza," _
            & "'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes" _
            & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc" _
            & " Where c.activo_alu = 'SI' and dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu" _
            & " and dm.agno = c.agno and c.agno = axc.agno" _
            & " and dm.agno = " & cb_agno_masivos.Text & " and mes = " & cb_mes_masivos.Text _
            & " group by rut_apo" _
            & " having sum(monto_tot_cuota-monto_abono) >= 13500) as t" _
            & " where concat(rut_cli,fech_venc,nro_cli) not in (select concat(rut_cli,fech_venc,nro_cli) from fnza_cobranza)"
    conn.Execute var_sql
    
   
    Call Crear_Archivo_Cobranza(ruta)
'    var_sql = "SELECT concat(ape1_apo,' ',ape2_apo,' ',noms_apo) as nombre,folio_cupon,fc.rut_apo as rut_cli,'CUP' as producto,numero as nro_cli,valor as monto_total,'1' as cod_moneda," _
'            & "if(mes<>12,concat(0,(mes-2)),(mes-2)) as num_cuota,vcto as fech_venc,colegio,dir_apo," _
'            & "comuna,fon_c_apo as tel_parti,cel_apo as tel_parti2,empresa_fon,if(email is null,'',email) as texto_adi " _
'            & " FROM fnza_cuponeras fc,fnza_deta_cupones fdc,apoderados a,codigos__geograficos cg" _
'            & " where fc.folio_cuponera = fdc.folio_cuponera and agno = " & cb_agno_masivos.Text & " and" _
'            & " estado = 'EC' and fc.rut_apo = a.rut_apo and a.comuna_apo = cg.cod_geo and mes = 12"
'      cast(c.rut_cli as char) = cast(a.rut_apo as char)
            
    var_sql = "SELECT concat(ape1_apo,' ',ape2_apo,' ',noms_apo) as nombre,folio,rut_cli,producto," _
            & "nro_cli,monto_total,moneda as cod_moneda,if(mes<>12,concat(0,(mes-2)),(mes-2)) as num_cuota,fech_venc," _
            & "c.colegio,dir_apo,comuna,fon_c_apo as tel_parti,cel_apo as tel_parti2,empresa_fon,if(email is null,'',email) as texto_adi" _
            & ",telefono_adicional_1,telefono_adicional_2" _
            & " FROM fnza_cobranza c,apoderados a,codigos__geograficos cg" _
            & " where agno = " & cb_agno_masivos.Text & " and estado = 'EC'" _
            & " and c.rut_cli = a.rut_apo and a.comuna_apo = cg.cod_geo and mes = " & cb_mes_masivos.Text
    objRS.Open var_sql, conn
    
    If objRS.RecordCount = 0 Then
        MsgBox "No hay registros para generar archivo de Cobranza", vbInformation
        Exit Sub
    End If
    
    progreso.Value = 0
    progreso.Min = 0
    progreso.Max = objRS.RecordCount
    Frame_Actualizacion_Masivo.Visible = True
    Frame_Actualizacion_Masivo.Caption = "Generación de Masivo"
    
    While Not objRS.EOF
'        If Len(objRS("colegio")) = 6 Then
'            producto = cod_colegio
'        Else
'            producto = cod_prekinder
'        End If
        Pos = 0
        producto = cod_colegio
        For i = 1 To Len(objRS("texto_adi"))
            If Mid(objRS("texto_adi"), i, 1) = "@" Then Pos = i
        Next i
        If Len(objRS("texto_adi")) > 2 And Pos > 0 Then
            correo_parte1 = Mid(objRS("texto_adi"), 1, Pos - 1)
            correo_parte2 = Mid(objRS("texto_adi"), Pos + 1)
        Else
            correo_parte1 = ""
            correo_parte2 = ""
        End If
        rut = "000000000"
        largo = 9 - (Len(objRS("rut_cli")) - 2)
        Registro = Mid(rut, 1, largo) & objRS("rut_cli") & Chr(9) & Left(objRS("nombre"), 40) & Chr(9) & producto _
                 & Chr(9) & objRS("nro_cli") & Chr(9) & objRS("monto_total") & Chr(9) _
                 & objRS("cod_moneda") & Chr(9) & objRS("num_cuota") & Chr(9) _
                 & Format(objRS("fech_venc"), "dd") & "/" & Format(objRS("fech_venc"), "mm") & "/" & Format(objRS("fech_venc"), "yyyy") & Chr(9) _
                 & objRS("colegio") & Chr(9) & Mid(Left(IIf(IsNull(objRS("dir_apo")), "", objRS("dir_apo")), 60), 1, 60) & Chr(9) _
                 & Mid(Left(objRS("comuna"), 20), 1, 20) & Chr(9) & Mid(valida_telefonos(IIf(IsNull(objRS("tel_parti")), "", objRS("tel_parti"))), 1, 15) & Chr(9) _
                 & Mid(valida_telefonos(IIf(IsNull(objRS("tel_parti2")), "", objRS("tel_parti2"))), 1, 15) & Chr(9) & "" & Chr(9) & "" & Chr(9) _
                 & Mid(valida_telefonos(IIf(IsNull(objRS("empresa_fon")), "", objRS("empresa_fon"))), 1, 15) & Chr(9) _
                 & Mid(valida_telefonos(IIf(IsNull(objRS("telefono_adicional_1")), "", objRS("telefono_adicional_1"))), 1, 15) & Chr(9) _
                 & Mid(Left(correo_parte1, 20), 1, 20) _
                 & Chr(9) & Mid(Left(correo_parte2, 20), 1, 20) & Chr(9) _
                 & Mid(valida_telefonos(IIf(IsNull(objRS("telefono_adicional_2")), "", objRS("telefono_adicional_2"))), 1, 15) & Chr(9) & "C"
                 'Call AgregarRegistroCobranza(Registro)
                 Call AgregarRegistroCobranza(Registro, ruta)
                 
        var_sql = "update fnza_cobranza set estado = 'GC' where folio = " & objRS("folio")
        conn.Execute var_sql
                 
        objRS.MoveNext
        progreso.Value = progreso.Value + 1
        
    Wend
    objRS.Close
    Else
        ruta = "C:\Reg_Cobranza_" & Date & "\eliminacion_cobranza_" & cod_colegio & ".txt"
'        var_sql = "SELECT folio_cupon,fc.rut_apo as rut_cli,numero as nro_cli,valor as monto_total,'1' as cod_moneda,if(mes<>12,concat(0,(mes-2)),(mes-2)) as num_cuota,vcto as fech_venc,colegio" _
'                & " FROM fnza_cuponeras fc,fnza_deta_cupones fdc,fnza_elimina_cobranza fec" _
'                & " where fc.folio_cuponera = fdc.folio_cuponera and agno = 2009 and" _
'                & " (fdc.estado = 'GC' or fdc.estado = 'EL' or fdc.estado = 'PA') and (fdc.mes-2) = fec.num_cup and" _
'                & " fc.rut_apo = fec.rut_apo and vcto = date_format(fech_ven,'%Y-%m-10') and numero = cab_bol and fec.estado = 'eliminar'"
        var_sql = "drop table if exists temp_cobranza"
        conn.Execute var_sql
        
        var_sql = "create table temp_cobranza type = MyISAM" _
                & " select rut_cli,monto_total,moneda as cod_moneda,producto,num_cuota,fech_venc,t.colegio," _
                & "nro_cli,estado,fecha_cobranza,fecha_eliminacion,agno,mes" _
                & " From (select rut_apo as rut_cli,sum(monto_tot_cuota-monto_abono) as monto_total," _
                & "1 as moneda,'CUP' as producto,(mes-2) as num_cuota,fech_venc,dm.colegio," _
                & "concat(dm.agno,if(mes<12,concat(0,(mes-2)),(mes-2))) as nro_cli,'EC' as estado" _
                & ",now() as fecha_cobranza,'1111-11-11' as fecha_eliminacion,dm.agno,dm.mes" _
                & " from fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc" _
                & " Where dm.run_alu = c.ci_alu And c.ci_alu = axc.ci_alu and dm.agno = c.agno" _
                & " and c.agno = axc.agno and dm.agno = " & cb_agno_masivos.Text & " and mes = " & cb_mes_masivos.Text & " group by rut_apo" _
                & " having sum(monto_tot_cuota-monto_abono) >= 0) as t"
        conn.Execute var_sql
        
        var_sql = "update temp_cobranza t,fnza_cobranza f set f.estado = 'DE',f.fecha_eliminacion = now()" _
                & " Where t.rut_cli = f.rut_cli and t.nro_cli = f.nro_cli And t.Monto_Total = 0 and f.estado = 'GC' and f.estado <> 'EL'"
        conn.Execute var_sql
        
        var_sql = "select folio as folio_cupon,rut_cli,nro_cli,monto_total,moneda as cod_moneda,num_cuota,fech_venc,colegio from fnza_cobranza where estado = 'DE'"
        objRS.Open var_sql, conn
        
        Call Crear_Archivo_Cobranza(ruta)
        If objRS.RecordCount = 0 Then
            MsgBox "No hay registros para generar archivo de Eliminación de Cobranza", vbInformation
            Exit Sub
        End If
        progreso.Value = 0
        progreso.Min = 0
        progreso.Max = objRS.RecordCount
        Frame_Actualizacion_Masivo.Visible = True
        Frame_Actualizacion_Masivo.Caption = "Generación de Masivo"
    
        While Not objRS.EOF
'            If Len(objRS("colegio")) = 6 Then
'                producto = cod_colegio
'            Else
'                producto = cod_prekinder
'            End If
            Pos = 0
            producto = cod_colegio
            rut = "000000000"
            largo = 9 - (Len(objRS("rut_cli")) - 2)
            Registro = Mid(rut, 1, largo) & objRS("rut_cli") & Chr(9) & "" & Chr(9) & producto _
                     & Chr(9) & objRS("nro_cli") & Chr(9) & objRS("monto_total") & Chr(9) _
                     & objRS("cod_moneda") & Chr(9) & objRS("num_cuota") & Chr(9) _
                     & Format(objRS("fech_venc"), "dd") & "/" & Format(objRS("fech_venc"), "mm") & "/" & Format(objRS("fech_venc"), "yyyy") & Chr(9) _
                     & objRS("colegio") & Chr(9) & "" & Chr(9) _
                     & "" & Chr(9) & "" & Chr(9) _
                     & "" & Chr(9) & "" & Chr(9) & "" & Chr(9) _
                     & "" & Chr(9) & "" & Chr(9) & "" & Chr(9) & "" & Chr(9) & "" & Chr(9) & "E"
                     'Call AgregarRegistroCobranza(Registro)
            Call AgregarRegistroCobranza(Registro, ruta)
            
'            var_sql = "update fnza_elimina_cobranza" _
'                    & " set estado = 'eliminado'" _
'                    & " where rut_apo = '" & objRS("rut_cli") _
'                    & "' and cab_bol = '" & objRS("nro_cli") _
'                    & "' and num_cup = " & objRS("num_cuota") _
'                    & " and fech_ven = '" & Format(objRS("fech_venc"), "yyyy-mm-dd") & "' and estado = 'eliminar'"
'            conn.Execute var_sql
            
'            var_sql = "update fnza_cuponeras fc,fnza_deta_cupones fdc" _
'                    & " set estado = 'PA'" _
'                    & " where fc.folio_cuponera = fdc.folio_cuponera and" _
'                    & " fc.rut_apo = '" & objRS("rut_cli") _
'                    & "' and numero = '" & objRS("nro_cli") _
'                    & "' and (mes-2) = " & objRS("num_cuota") _
'                    & " and vcto = '" & Format(objRS("fech_venc"), "yyyy-mm-10") & "'"
'            conn.Execute var_sql
                             
            var_sql = "update fnza_cobranza set estado = 'EL' where rut_cli = '" & objRS("rut_cli") & "' and num_cuota = '" & objRS("num_cuota") & "'"
            conn.Execute var_sql
            objRS.MoveNext
            progreso.Value = progreso.Value + 1
        Wend
        objRS.Close
    End If
    Frame_Actualizacion_Masivo.Visible = False
End Sub

Private Sub Command2_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 = "create table temp_convenios type = MyISAM" _
            & " SELECT fc.rut_apo,concat(ape1_apo,' ',ape2_apo,' ',noms_apo) as nombre,if(length(colegio)=6,'Colegio','Pre Kinder') as colegio,numero," _
            & "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,valor,vcto" _
            & " FROM fnza_cuponeras fc,fnza_deta_cupones fdc,apoderados a" _
            & " Where fc.Rut_apo = a.Rut_apo And fc.folio_cuponera = fdc.folio_cuponera" _
            & " and concepto <> 'ct00' and estado = 'ME'" _
            & " order by agno,rut_apo,numero"
    conn.Execute var_sql
             
    Call LimpiaFormulas(cr_cupo, 30)
    cr_cupo.ReportFileName = App.Path & "\Rpt\cuponeras_erroneas.rpt"
    cr_cupo.Formulas(3) = "est = '" & est & "'"
    cr_cupo.Formulas(4) = "dir = '" & dir & "'"
    cr_cupo.Destination = crptToWindow
    cr_cupo.Action = 1
End Sub

Private Sub Form_Load()
    Dim var_sql As String
    MuestraFrame "apoderado"
    llena_combo_agnos
    llena_combo_general
    llena_combo_mes
    var_sql = "UPDATE matriculas as m, fnza_detalle_mensualidad as fdm SET confirmado='SI'  WHERE fdm.run_alu=m.ci_alu AND fdm.agno=m.anno_mat AND fdm.concepto='ct00' AND (fdm.estado_dtll='clda' OR fdm.estado_dtll='abon' OR fdm.estado_dtll='cred');"
    conn.Execute var_sql
    'cb_colegio.AddItem "Colegio"
    'cb_colegio.AddItem "Pre Kinder"
    cb_institucion.AddItem "Colegio"
    cb_institucion.AddItem "Pre Kinder"
    cb_institucion.Text = "Colegio"
    'Actualizar cuponeras
    'actualiza_cuponeras_masivo
    Lista = False
    Me.Left = 10
    Me.Top = 100
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Dim RC As ADODB.Recordset
    Set RC = New ADODB.Recordset

    Sql = "select count(estado) as cant from fnza_deta_cupones where (estado = 'GE' or estado = 'DE' or estado = 'RE') and loginusuario = '" & Nom_usu_ses & "'"
    RC.Open Sql, conn
    
    If RC!cant > 0 Then
        If MsgBox("Hay Cambios en las cuponeras sin traspasar a los archivos, Generar los archivos ahora?", vbQuestion + vbYesNo) = vbYes Then
            btn_crear_Click
        End If
    End If
    RC.Close
End Sub

Private Sub List1_Click()
    Dim RC As ADODB.Recordset
    Set RC = New ADODB.Recordset
    
    Text1.Text = List1.Text
    List1.Visible = False
    List2.Visible = False
    RC.Open "SELECT ci_alu FROM postulantes WHERE left(CONCAT(ape_Pat_alu,' ',ape_Mat_alu,' ',nom_alu)," & Len(Text1.Text) & ")='" & Text1.Text & "' ORDER BY ape_pat_alu, ape_mat_alu, nom_alu;", conn
    RC.MoveFirst
    Flag_sub = True
    text2.Text = RC("ci_alu")
    RC.Close
    Flag_sub = False
    Btn_Ver_Alumno.Enabled = True
    busca_apoderado
End Sub

Private Sub List2_Click()
    Dim RC As ADODB.Recordset
    Set RC = New ADODB.Recordset
    text2.Text = List2.Text
    List2.Visible = False
    List1.Visible = False
    RC.Open "SELECT CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) as nombre_alu FROM postulantes WHERE ci_alu='" & text2.Text & "' ORDER BY ci_alu;", conn
    RC.MoveFirst
    Flag_sub = True
    Text1.Text = RC("nombre_alu")
    RC.Close
    Flag_sub = False
    Btn_Ver_Alumno.Enabled = True
    busca_apoderado
End Sub

Private Sub List3_Click()
    Dim RC As ADODB.Recordset
    Text3.Text = List3.Text
    List3.Visible = False
    List4.Visible = False
    Set RC = New ADODB.Recordset
    RC.Open "SELECT rut_apo FROM `apoderados` where left(CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo)," & Len(Text3.Text) & ")='" & Text3.Text & "' ORDER BY ape1_apo, ape2_apo, noms_apo;", conn
    RC.MoveFirst
    Flag_sub = True
    Text4.Text = RC("rut_apo")
    lbl_Run.Caption = RC("rut_apo")
    Flag_sub = False
    Btn_Ver_Apoderado.Enabled = True
    lbl_Run = UCase(RC("rut_apo"))
    RC.Close
End Sub

Private Sub List4_Click()
    Dim RC As ADODB.Recordset
    Text4.Text = List4.Text
    List4.Visible = False
    List3.Visible = False
    Set RC = New ADODB.Recordset
    RC.Open "SELECT CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) as nombre_apo FROM apoderados where rut_apo='" & Text4.Text & "' ORDER BY rut_apo;", conn
    RC.MoveFirst
    Flag_sub = True
    Text3.Text = RC("nombre_apo")
    lbl_Run.Caption = Text4.Text
    Flag_sub = False
    Btn_Ver_Apoderado.Enabled = True
    lbl_Apoderado.Caption = RC("nombre_apo")
    RC.Close
End Sub

Private Sub mshflex_cuponeras_Click()
If mshflex_cuponeras.TextMatrix(1, 1) <> "" Then
    'mshflex_cuponeras.Col = 1
    llena_grilla_fnzafoliodetacupones (mshflex_cuponeras.TextMatrix(mshflex_cuponeras.RowSel, 1))
    Call marca_grilla(mshflex_detacuponera, 7)
    mshflex_detacuponera.Row = 1
    mshflex_detacuponera.ColSel = 1
    Call SeteaGrilla
    Call LlenaGrilla
End If
End Sub

Private Sub mshflex_detacuponera_DblClick()
    If Not Lista Then
        'If mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 7) = "Pendiente" Then
            Lbl_valor_cupon.Caption = mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 6)
            Lbl_Venc_Cupon.Caption = mshflex_detacuponera.TextMatrix(mshflex_detacuponera.Row, 5)
            txt_NuevoValor.Text = Lbl_valor_cupon.Caption
            Frame_Cuponeras.Enabled = False
            Frame_Cupones.Enabled = False
            Btn_Generar.Enabled = False
            Cancelar.Enabled = False
            btn_crear.Enabled = False
            cmd_EliminarCuponera.Enabled = False
            cmg_Reimprimir.Enabled = False
            Btn_Limpiar.Enabled = False
            Btn_Salir.Enabled = False
            Frame_editar_cupon.Visible = True
       ' End If
    End If
End Sub

Private Sub Option1_Click(Index As Integer)
    Text1.Text = ""
    text2.Text = ""
    Text3.Text = ""
    Text4.Text = ""
    List1.Visible = False
    List2.Visible = False
    List3.Visible = False
    List4.Visible = False
    'Label2.Visible = False
    'Label3.Visible = False
    lbl_Apoderado.Visible = False
    lbl_Run.Visible = False
    mshflex_cuponeras.ClearStructure
    mshflex_cuponeras.Rows = 2
    mshflex_detacuponera.ClearStructure
    mshflex_detacuponera.Rows = 2
    If Option1(Index).Value = True Then
        If Index = 0 Then
            MuestraFrame "curso"
        ElseIf Index = 1 Then
            MuestraFrame "apoderado"
        ElseIf Index = 2 Then
            MuestraFrame "alumno"
        ElseIf Index = 3 Then
            MuestraFrame "masivo_eliminacion"
        End If
    End If
End Sub

Private Sub Text1_Change()
    If text2.Text = "" Then
    Dim RC As ADODB.Recordset
    ' Si el cambio lo hace un Sub no hacer nada
    If Flag_sub Then Exit Sub
    If Text1.Text = "" Then Exit Sub
    Set RC = New ADODB.Recordset
    
    RC.Open "SELECT DISTINCT a.ape_pat_alu, a.ape_mat_alu, a.nom_alu FROM postulantes a, matriculas m WHERE left(CONCAT(a.ape_pat_alu,' ',a.ape_mat_alu,' ',a.nom_alu)," & Len(Text1.Text) & ")='" & Text1.Text & "' AND a.ci_alu=m.ci_alu ORDER BY a.ape_pat_alu, a.ape_mat_alu, a.nom_alu;", conn
    List1.Clear
    If RC.RecordCount > 0 Then
        While Not (RC.EOF)
            List1.AddItem RC("ape_pat_alu") & " " & RC("ape_mat_alu") & " " & RC("nom_alu")
            RC.MoveNext
        Wend
        List1.Visible = True
        List2.Visible = False
    End If
    RC.Close
    Btn_Ver_Alumno.Enabled = False
    End If
End Sub

Private Sub Text2_Change()
    If Text1.Text = "" Then
    Dim RC As ADODB.Recordset
    ' Si el cambio lo hace un Sub no hacer nada
    If Flag_sub Then Exit Sub
    If text2.Text = "" Then Exit Sub
    Set RC = New ADODB.Recordset
    
    RC.Open "SELECT DISTINCT a.ci_alu FROM postulantes a, matriculas m WHERE left(a.ci_alu," & Len(text2.Text) & ")='" & text2.Text & "' AND a.ci_alu=m.ci_alu ORDER BY a.ci_alu;", conn
    List2.Clear
    If RC.RecordCount > 0 Then
        While Not (RC.EOF)
            List2.AddItem RC("ci_alu")
            RC.MoveNext
        Wend
        List1.Visible = False
        List2.Visible = True
    End If
    RC.Close
    Btn_Ver_Alumno.Enabled = False
    End If
End Sub

Private Sub Text3_Change()
    If Text4.Text = "" Then
    Dim RC As ADODB.Recordset
    ' Si el cambio lo hace un Sub no hacer nada
    If Flag_sub Then Exit Sub
    If Text3.Text = "" Then Exit Sub
    Set RC = New ADODB.Recordset
        
    RC.Open "SELECT DISTINCT a.ape1_apo, a.ape2_apo, a.noms_apo FROM apoderados a, fnza_mensualidad fm where left(CONCAT(a.ape1_apo,' ',a.ape2_apo,' ',a.noms_apo)," & Len(Text3.Text) & ")='" & Text3.Text & "' and a.rut_apo=fm.run_apo ORDER BY a.ape1_apo, a.ape2_apo, a.noms_apo;", conn
    List3.Clear
    While Not (RC.EOF)
        List3.AddItem RC("ape1_apo") & " " & RC("ape2_apo") & " " & RC("noms_apo")
        RC.MoveNext
    Wend
    RC.Close
    Btn_Ver_Apoderado.Enabled = False
    List3.Visible = True
    List4.Visible = False
    End If
End Sub

Private Sub Text4_Change()
' Si el cambio lo hace un Sub no hacer nada
    If Text3.Text = "" Then
    If Flag_sub Then Exit Sub
    If Text4.Text = "" Then Exit Sub
    Dim Str_Query As String
    Dim RC As ADODB.Recordset
    
    Set RC = New ADODB.Recordset
    Str_Query = "SELECT DISTINCT fm.run_apo FROM apoderados a, fnza_mensualidad fm WHERE left(a.rut_apo," & Len(Text4.Text) & ")='" & Text4.Text & "' AND a.rut_apo=fm.run_apo ORDER BY a.rut_apo;"
    RC.Open Str_Query, conn
    List4.Clear
    While Not (RC.EOF)
        List4.AddItem RC("run_apo")
        RC.MoveNext
    Wend
    RC.Close
    Btn_Ver_Apoderado.Enabled = False
    List4.Visible = True
    List3.Visible = False
    End If
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Private Sub Text2_KeyPress(KeyAscii As Integer)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Private Sub Text3_KeyPress(KeyAscii As Integer)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Private Sub Text4_KeyPress(KeyAscii As Integer)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Private Sub busca_apoderado()
Dim objRS As ADODB.Recordset
Set objRS = New ADODB.Recordset
Dim var_sql
var_sql = "SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) as nombre_apo" _
        & " FROM apoderados a,apo_x_alu axa" _
        & " Where a.Rut_apo = axa.Rut_apo and ci_alu = '" & text2 _
        & "' group by rut_apo" _
        & " ORDER BY rut_apo"
objRS.Open var_sql, conn
If Not objRS.EOF Then
    lbl_Apoderado.Caption = objRS("nombre_apo")
    lbl_Run = UCase(objRS("rut_apo"))
End If
objRS.Close
'Label2.Visible = True
'Label3.Visible = True
lbl_Apoderado.Visible = True
lbl_Run.Visible = True
End Sub

Private Sub llena_grilla_fnzacuponera(rut_apo As String)
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    var_sql = "select folio_cuponera as 'Cuponera',nombre as 'Colegio',agno 'Año',mes_1 as 'Cupon Ini'," _
            & "descripcion as 'banco',fecha_gen 'Fecha',can_cupon 'Nº Cup.'," _
            & "desc_estado 'Estado',fc.colegio" _
            & " from fnza_cuponeras fc,hist_cod_colegio hcc,fnza_cod_estadocupon fcec," _
            & " fnza_cod_banco fcb" _
            & " where fc.esta_fin = fcec.estado and fc.colegio = hcc.colegio and" _
            & " fc.banco = fcb.banco and rut_apo = '" & rut_apo & "'" _
            & " order by folio_cuponera asc"
    objRS.Open var_sql, conn
    Call Rec2Grid(objRS, mshflex_cuponeras, True)
    objRS.Close
    mshflex_cuponeras.ColWidth(1) = 0
    mshflex_cuponeras.ColWidth(9) = 0
    
End Sub

Private Sub llena_grilla_fnzacuponeracambios()
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    var_sql = "select folio_cuponera as 'Cuponera',rut_apo as 'Rut Apoderado', nombre as 'Colegio',agno 'Año',mes_1 as 'Cupon Ini'," _
            & "descripcion as 'banco',fecha_gen 'Fecha',can_cupon 'Nº Cup.'," _
            & "desc_estado 'Estado',fc.colegio" _
            & " from fnza_cuponeras fc,hist_cod_colegio hcc,fnza_cod_estadocupon fcec," _
            & " fnza_cod_banco fcb" _
            & " where fc.esta_fin = fcec.estado and fc.colegio = hcc.colegio and" _
            & " fc.banco = fcb.banco and (esta_fin = 'GE' or esta_fin = 'DE' or esta_fin = 'RE'  ) and loginusuario = '" & Nom_usu_ses & "'" _
            & " order by folio_cuponera asc"
    objRS.Open var_sql, conn
    Call Rec2Grid(objRS, mshflex_cuponeras, True)
    objRS.Close
    mshflex_cuponeras.ColWidth(1) = 0
    mshflex_cuponeras.ColWidth(10) = 0
    
End Sub

Private Sub Rec2Grid(ByRef rec As ADODB.Recordset, ByRef grid As Object, Optional ByVal Numeracion As Boolean = True)
    grid.ClearStructure
    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
            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

Public Sub llena_grilla_fnzafoliodetacupones(folio_cuponera As Long)
    Dim var_sql As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    var_sql = "select fc.colegio,folio_cupon,numero as 'nº cupon',mes,vcto vencimiento,valor," _
            & "desc_estado estado,ubicacion,fdc.folio_cuponera" _
            & " from fnza_deta_cupones fdc,fnza_cod_estadocupon fcec," _
            & " fnza_cuponeras fc" _
            & " where fdc.estado = fcec.estado and fdc.folio_cuponera = " & folio_cuponera _
            & " and fc.folio_cuponera = fdc.folio_cuponera" _
            & " order by folio_cupon asc"
    objRS.Open var_sql, conn
    Call Rec2Grid(objRS, mshflex_detacuponera, True)
    objRS.Close
    mshflex_detacuponera.ColWidth(1) = 0
    mshflex_detacuponera.ColWidth(2) = 0
    mshflex_detacuponera.ColWidth(8) = 0
    mshflex_detacuponera.ColWidth(9) = 0
End Sub

Public Sub marca_grilla(ByRef Grilla As Object, Columna As Integer)
Dim i, j As Integer

For i = 1 To Grilla.Rows - 1
      Grilla.Row = i
      Grilla.Col = Columna
      If Grilla.Text = "Pagado" Then
        For j = 1 To Grilla.Cols - 1
            Grilla.Col = j
            Grilla.CellBackColor = &HFF00&
        Next j
      End If
      
      If Grilla.Text = "Eliminado" Then
        For j = 1 To Grilla.Cols - 1
            Grilla.Col = j
            Grilla.CellBackColor = &HC0C0FF
        Next j
      End If
                
      If Grilla.Text = "Pagado con Diferencia" Then
        For j = 1 To Grilla.Cols - 1
            Grilla.Col = j
            Grilla.CellBackColor = &H80FFFF
        Next j
      End If
Next i
End Sub

Public Sub actualiza_cuponeras_detalle(rut As String)
    Dim var_sql_cupo, var_sql_fnza, var_sql As String
    Dim agno, i As Long
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    agno = Anno

    For i = 2005 To agno
    var_sql_fnza = "SELECT fm.run_apo, fdm.folio_mens, sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' , fdm.mes, fdm.agno, fdm.colegio, fdm.concepto" _
                 & " FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm" _
                 & " WHERE fdm.run_alu=m.ci_alu AND fdm.agno=m.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno = " & i & " and m.ind_cup=1 and fdm.monto_tot_cuota>0 AND (fdm.concepto<>'ct00')" _
                 & " and fm.run_apo = '" & rut & "'" _
                 & " GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno, fdm.colegio" _
                 & " ORDER BY fm.run_apo, fdm.folio_mens, fdm.colegio, fdm.mes, fdm.agno"
    
    objRS.Open var_sql_fnza, conn
    'MsgBox objRS(run_apo)
    While Not objRS.EOF
        var_sql_cupo = "select folio_cupon" _
                 & " from fnza_deta_cupones fdc,fnza_cuponeras fc" _
                 & " Where fc.folio_cuponera = fdc.folio_cuponera And" _
                 & " Rut_apo = '" & objRS("run_apo") & "' And agno = " & objRS("agno") _
                 & " and mes = " & objRS("mes") & " and fc.colegio='" & objRS!colegio & "'" _
                 & " and estado <> 'EL' and estado <> 'GE' and estado <> 'DE' and estado <> 'RE'" _
                 & " and estado <> 'MC' and estado <> 'EC' and estado <> 'GC' and estado <> 'CC'" _
                 & " Order by folio_cupon desc"
        rec.Open var_sql_cupo, conn
        
        If Not rec.EOF Then
        If objRS("abono") = 0 Then
            'var_sql = "update fnza_deta_cupones set" _
                    & " valor = " & objRS("cuota") _
                    & ", estado = 'PE' where folio_cupon = " & REC("folio_cupon")
            var_sql = "update fnza_deta_cupones set" _
                    & " estado = 'PE' where folio_cupon = " & rec("folio_cupon")
            conn.Execute var_sql
        Else
            If objRS("cuota") = objRS("abono") Then
                'var_sql = "update fnza_deta_cupones set" _
                        & " valor = " & objRS("cuota") _
                        & ", estado = 'PA' where folio_cupon = " & REC("folio_cupon")
                var_sql = "update fnza_deta_cupones set" _
                        & " estado = 'PA' where folio_cupon = " & rec("folio_cupon")
                conn.Execute var_sql
            End If
            If objRS("cuota") > objRS("abono") Then
                'var_sql = "update fnza_deta_cupones set" _
                        & " valor = " & objRS("cuota") _
                        & ", estado = 'PD' where folio_cupon = " & REC("folio_cupon")
                var_sql = "update fnza_deta_cupones set" _
                        & " estado = 'PD' where folio_cupon = " & rec("folio_cupon")
                conn.Execute var_sql
            End If
        End If
        End If
        rec.Close
        objRS.MoveNext
    Wend
    objRS.Close
    Next i
    
    var_sql_fnza = "SELECT folio_cuponera FROM fnza_cuponeras WHERE rut_apo='" & rut & "' and concepto <> 'ct00';"
    objRS.Open var_sql_fnza, conn
    While Not objRS.EOF
        var_sql = "select sum(if(estado='PE',1,0)) as N_PE, sum(if(estado='EL',1,0)) as N_EL, sum(if(estado='PA',1,0)) as N_PA from fnza_deta_cupones where folio_cuponera=" & objRS!folio_cuponera & " group by folio_cuponera;"
        rec.Open var_sql, conn
        If rec.RecordCount > 0 Then
            If rec!N_PE > 0 Then
                conn.Execute "UPDATE fnza_cuponeras SET esta_fin='PE' WHERE folio_cuponera=" & objRS!folio_cuponera & ";"
            Else
                If rec!N_PA > 0 Then
                    conn.Execute "UPDATE fnza_cuponeras SET esta_fin='PA' WHERE folio_cuponera=" & objRS!folio_cuponera & ";"
                Else
                    conn.Execute "UPDATE fnza_cuponeras SET esta_fin='EL' WHERE folio_cuponera=" & objRS!folio_cuponera & ";"
                End If
            End If
        End If
        rec.Close
        objRS.MoveNext
    Wend
End Sub

Public Sub actualiza_cuponeras_detalle_cuotas00(rut As String)
    Dim var_sql_cupo, var_sql_fnza, var_sql As String
    Dim total As Long
    Dim agno, i As Long
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    agno = Anno + 1

    For i = 2003 To agno
    var_sql_fnza = "SELECT fm.run_apo, fdm.folio_mens, sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' , fdm.mes, fdm.agno, fdm.colegio, fdm.concepto" _
                 & " FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm" _
                 & " WHERE fdm.run_alu=m.ci_alu AND fdm.agno=m.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno = " & i & " and m.ind_cup=1 and fdm.monto_tot_cuota>0 AND (fdm.concepto='ct00')" _
                 & " and fm.run_apo = '" & rut & "'" _
                 & " GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno, fdm.colegio" _
                 & " ORDER BY fm.run_apo, fdm.folio_mens, fdm.colegio, fdm.mes, fdm.agno"
    
    objRS.Open var_sql_fnza, conn
    'MsgBox objRS(run_apo)
    While Not objRS.EOF
        var_sql_cupo = "select folio_cupon,valor" _
                 & " from fnza_deta_cupones fdc,fnza_cuponeras fc" _
                 & " Where fc.folio_cuponera = fdc.folio_cuponera And" _
                 & " Rut_apo = '" & objRS("run_apo") & "' And agno = " & objRS("agno") _
                 & " and mes = " & objRS("mes") & " and fc.colegio='" & objRS!colegio & "'" _
                 & " and estado <> 'EL' and estado <> 'GE' and estado <> 'DE' and estado <> 'RE'" _
                 & " and concepto = 'ct00' Order by folio_cupon"
        rec.Open var_sql_cupo, conn
        
        If Not rec.EOF Then
        If objRS("abono") = 0 Then
            'var_sql = "update fnza_deta_cupones set" _
                    & " valor = " & objRS("cuota") _
                    & ", estado = 'PE' where folio_cupon = " & REC("folio_cupon")
            While Not rec.EOF
                var_sql = "update fnza_deta_cupones set" _
                        & " estado = 'PE' where folio_cupon = " & rec("folio_cupon")
                conn.Execute var_sql
                rec.MoveNext
            Wend
        Else
            If objRS("cuota") = objRS("abono") Then
                'var_sql = "update fnza_deta_cupones set" _
                        & " valor = " & objRS("cuota") _
                        & ", estado = 'PA' where folio_cupon = " & REC("folio_cupon")
                While Not rec.EOF
                    var_sql = "update fnza_deta_cupones set" _
                            & " estado = 'PA' where folio_cupon = " & rec("folio_cupon")
                    conn.Execute var_sql
                    rec.MoveNext
                Wend
            End If
            If objRS("cuota") > objRS("abono") Then
                'var_sql = "update fnza_deta_cupones set" _
                        & " valor = " & objRS("cuota") _
                        & ", estado = 'PD' where folio_cupon = " & REC("folio_cupon")
                total = 0
                While Not rec.EOF
                    total = total + rec("valor")
                    If objRS("abono") >= total Then
                        var_sql = "update fnza_deta_cupones set" _
                                & " estado = 'PA' where folio_cupon = " & rec("folio_cupon")
                        conn.Execute var_sql
                    Else
                        var_sql = "update fnza_deta_cupones set" _
                                & " estado = 'PE' where folio_cupon = " & rec("folio_cupon")
                        conn.Execute var_sql
                    End If
                    rec.MoveNext
                Wend
            End If
        End If
        End If
        rec.Close
        objRS.MoveNext
    Wend
    objRS.Close
    Next i
    
    var_sql_fnza = "SELECT folio_cuponera FROM fnza_cuponeras WHERE rut_apo='" & rut & "' and concepto = 'ct00';"
    objRS.Open var_sql_fnza, conn
    While Not objRS.EOF
        var_sql = "select sum(if(estado='PE',1,0)) as N_PE, sum(if(estado='EL',1,0)) as N_EL, sum(if(estado='PA',1,0)) as N_PA from fnza_deta_cupones where folio_cuponera=" & objRS!folio_cuponera & " group by folio_cuponera;"
        rec.Open var_sql, conn
        If rec.RecordCount > 0 Then
            If rec!N_PE > 0 Then
                conn.Execute "UPDATE fnza_cuponeras SET esta_fin='PE' WHERE folio_cuponera=" & objRS!folio_cuponera & ";"
            Else
                If rec!N_PA > 0 Then
                    conn.Execute "UPDATE fnza_cuponeras SET esta_fin='PA' WHERE folio_cuponera=" & objRS!folio_cuponera & ";"
                Else
                    conn.Execute "UPDATE fnza_cuponeras SET esta_fin='EL' WHERE folio_cuponera=" & objRS!folio_cuponera & ";"
                End If
            End If
        End If
        rec.Close
        objRS.MoveNext
    Wend
End Sub


Private Function crea_cuponera(rut As String, agno As Long, Pk As Boolean, conn As ADODB.Connection) As Boolean
    Dim i As Integer
    Dim cant_cupones
    Dim folio_cuponera As Long
    Dim Flag As Boolean
    Dim Fecha As Date
    Dim objRS As ADODB.Recordset
    Dim rec As ADODB.Recordset
    Dim var_sql As String
    Dim colegio As String
    Dim CBC As String
    
    colegio = RetornaCampo("CFG", "valor", "param='" & IIf(Pk, "CODPK", "CODBC") & "'", conn)
    CBC = RetornaCampo("CFG", "valor", "param='CodConvBCIBC'", conn)
    crea_cuponera = False
    Flag = False
    i = 0
    cant_cupones = 0
    Set objRS = New ADODB.Recordset
    Set rec = New ADODB.Recordset
    
    var_sql = "SELECT fm.run_apo, fdm.fech_venc, fdm.folio_mens, sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' , fdm.mes, fdm.agno, fdm.colegio, fdm.concepto" _
            & " FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm" _
            & " WHERE fdm.run_alu=m.ci_alu AND fdm.agno=m.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno=" & agno & " and m.ind_cup=1 and fdm.monto_tot_cuota>0 AND (fdm.concepto<>'ct00') and fm.run_apo = '" & rut & "' and m.confirmado='SI' and m.activo_alu = 'SI' and m.fec_retiro = '1111-11-11' AND m.fec_mat_r IS NOT NULL AND m.fec_mat_r <> '0000-00-00'" _
            & " GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno" _
            & " ORDER BY fm.run_apo, fdm.folio_mens, fdm.mes, fdm.agno"
            
    var_sql = "select distinct m.ci_alu  from apo_x_alu axu, matriculas m where m.anno_mat = axu.agno and m.ci_alu = axu.ci_alu and axu.rut_apo = '" & rut & "' and m.anno_mat = " & agno & " and m.ind_cup = 1 and m.confirmado = 'SI' AND m.fec_mat_r IS NOT NULL AND m.fec_mat_r <> '0000-00-00';"
            
    objRS.Open var_sql, conn
    
    If Pk Then
        Col = CODPK
    Else
        Col = CODBC
    End If
    
    'Si no tiene cupones para el año salir
    If objRS.RecordCount = 0 Then
        objRS.Close
        var_sql = "select m.confirmado from matriculas m, apo_x_alu axu, fnza_mensualidad fm, fnza_detalle_mensualidad fdm where axu.ci_alu = m.ci_alu and m.ind_cup = 1 and m.anno_mat = " & agno & " and axu.rut_apo = '" & rut & "' and confirmado <> 'SI' AND fec_mat_r IS NOT NULL AND fec_mat_r <> '0000-00-00' and fm.agno = m.anno_mat and fm.run_apo = '" & rut & "'and fdm.concepto = 'ct00' and fm.folio_mens = fdm.folio_mens" & ";"
        objRS.Open var_sql, conn
        If objRS.RecordCount = 0 Then
            MsgBox "El Apoderado no cuenta con tipo de pago cuponera" & vbCrLf & "Rut : " & rut, vbInformation
        Else
            If objRS("confirmado") = "NO" Then
                MsgBox "El apoderado cuenta con alumnos con matricula NO confirmada" & vbCrLf & "Rut : " & rut, vbInformation
            Else
                objRS.Close
                MsgBox "El Apoderado tiene irregularidades en su registro " & vbCrLf & "Rut : " & rut, vbInformation
                MsgBox "El Apoderado tiene errores en su registro " & vbCrLf & "Rut : " & rut, vbInformation
            End If
        End If
        Exit Function
    End If

    var_sql = "SELECT * FROM fnza_cuponeras WHERE (esta_fin='PE' or esta_fin = 'GE' or esta_fin = 'RE') AND agno = " & agno & " and rut_apo = '" & rut & "' and concepto <> 'ct00'"
            
    rec.Open var_sql, conn
    
    If rec.RecordCount > 0 Then
       ' MsgBox "El Apoderado cuenta con cuponera valida, si desea generar una nueva primero elimine la activa", vbInformation
        rec.Close
        Exit Function
    End If
    rec.Close
    
    For i = 2 To 12
        objRS.Close
        var_sql = " SELECT fm.run_apo, fdm.fech_venc, fdm.folio_mens, sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' , fdm.mes, fdm.agno, mid(fdm.colegio,1,6) as colegio, fdm.concepto" _
                & " FROM fnza_detalle_mensualidad as fdm, (select distinct m.ci_alu,m.anno_mat  from apo_x_alu axu, matriculas m where m.anno_mat = axu.agno and m.ci_alu = axu.ci_alu and axu.rut_apo = '" & rut & "' and m.anno_mat = " & agno & " and m.ind_cup = 1 and m.confirmado = 'SI' AND m.fec_mat_r IS NOT NULL AND m.fec_mat_r <> '0000-00-00') as d, fnza_mensualidad as fm" _
                & " WHERE fdm.run_alu=d.ci_alu AND fdm.agno=d.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno=" & agno & " and fdm.monto_tot_cuota>0 AND (fdm.concepto<>'ct00') and fm.run_apo = '" & rut & "' and fdm.mes = " & i & " and fdm.mes = fm.mes " _
                & " GROUP BY fm.run_apo, fm.mes, fm.agno" _
                & " ORDER BY fm.run_apo, fdm.mes, fdm.agno"
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
            If val(objRS!abono) = 0 And Not (Flag) Then
                var_sql = "insert into fnza_cuponeras(colegio,rut_apo,agno,mes_1,banco,fecha_gen,can_cupon,esta_fin,loginusuario,concepto)" _
                    & " values('" & objRS("colegio") & "','" & objRS("run_apo") & "'," & objRS("agno") & "," _
                    & objRS!mes & ",'" & "16" & "','" & Format(Date, "yyyy-mm-dd") & "'" _
                    & ",'1','GE','" & Nom_usu_ses & "','esco')"
                conn.Execute var_sql
                Flag = True
                var_sql = "select folio_cuponera from fnza_cuponeras where " _
                        & " rut_apo = '" & rut & "' and agno = " & agno & " ORDER BY folio_cuponera DESC LIMIT 1;"
                rec.Open var_sql, conn
                If rec.RecordCount > 0 Then
                    folio_cuponera = rec("folio_cuponera")
                Else
                    MsgBox "Error en  generacion de cuponera"
                    Exit Function
                End If
                rec.Close
            End If
            If val(objRS!abono) = 0 Then
                cant_cupones = cant_cupones + 1
                var_sql = "insert into fnza_deta_cupones(numero,mes,vcto,valor,estado," _
                        & "ubicacion,folio_cuponera,loginusuario) values(" & IIf(Pk, CBC, "") & Format(agno, "0000") & Format(val(objRS!mes) - 2, "00") & "," & objRS("mes") & ",'" _
                        & Format(objRS!fech_venc, "yyyy-mm-dd") & "'," & objRS("cuota") & ",'GE'," _
                        & cant_cupones & "," & folio_cuponera & ",'" & Nom_usu_ses & "')"
                conn.Execute var_sql
            End If
        End If
    Next i
    var_sql = "update fnza_cuponeras set can_cupon = " & cant_cupones _
            & " where folio_cuponera = " & folio_cuponera
    conn.Execute var_sql
    objRS.Close
    crea_cuponera = True
End Function

Private Sub llena_combo_agnos()
    Dim i As Integer
    Dim agno As Long
    cb_agnos.Clear
    agno = Format(Date, "yyyy")
    For i = 2005 To agno
        cb_agnos.AddItem i
        cb_agno_masivos.AddItem i
    Next i
    cb_agno_masivos.Text = Anno
End Sub

Private Function elimina_cuponera(folio_cuponera As Long) As Boolean
    Dim objRS, RC As ADODB.Recordset
    Dim total_cupones, cupones_modificados
    Dim var_sql As String
    Dim Pk, GE, RE As Boolean
    
    elimina_cuponera = False
    
    Set objRS = New ADODB.Recordset
    Set RC = New ADODB.Recordset
    
    var_sql = "select * from fnza_deta_cupones fdc,fnza_cuponeras fc" _
            & " where fc.folio_cuponera = fdc.folio_cuponera and fdc.folio_cuponera = " & folio_cuponera
    CBC = RetornaCampo("CFG", "valor", "param='CodConvBCIBC'", conn)
    total_cupones = 0
    cupones_modificados = 0
    GE = False
    RE = False
    objRS.Open var_sql, conn
    
    'Si no tiene cupones para el año salir
    If objRS.RecordCount = 0 Then
        Exit Function
    End If
    
    'Recorro el Recorset y busco los cupones que esten en Estado Pendiente y los cambio a Eliminado
    objRS.MoveFirst
    For i = 1 To objRS.RecordCount
        total_cupones = total_cupones + 1
        If objRS!Estado = "PE" Or objRS!Estado = "ME" Then
            var_sql = "update fnza_deta_cupones set estado = 'DE', loginusuario = '" & Nom_usu_ses & "'" _
                    & " where folio_cupon = " & objRS!folio_cupon
            conn.Execute var_sql
            Pk = IIf(objRS("colegio") = CODPK, True, False)
            cupones_modificados = cupones_modificados + 1
        End If
        If objRS!Estado = "GE" Then
            GE = True
            var_sql = "DELETE FROM fnza_deta_cupones " _
                    & " where folio_cupon = " & objRS!folio_cupon
            conn.Execute var_sql
        End If
        If objRS!Estado = "RE" Then
            RE = True
        End If
        objRS.MoveNext
    Next i
    objRS.Close
    
    If cupones_modificados > 0 And total_cupones > 0 Then
        var_sql = "update fnza_cuponeras set esta_fin = 'DE', loginusuario = '" & Nom_usu_ses & "'" _
                    & " where folio_cuponera = " & folio_cuponera
        conn.Execute var_sql
    End If
    If GE And cupones_modificados = 0 Then
        var_sql = "DELETE FROM fnza_cuponeras " _
                    & " where folio_cuponera = " & folio_cuponera
        conn.Execute var_sql
    End If
    
    If RE And cupones_modificados = 0 Then
        MsgBox "Esta Tratando de eliminar una cuponera que esta preparada para la reimpresion" & vbCrLf & "Para eliminar cancele la reimpresion"
        elimina_cuponera = False
        Exit Function
    End If
        
    elimina_cuponera = True
End Function
Private Function agrega_cupon(Numero As String, mes As Integer, _
                              Vcto As Date, valor As Long, _
                              folio_cuponera As Long) As Boolean
    agrega_cupon = False
    Dim var_sql As String
    Dim afectado As Long
    
    var_sql = "Insert into fnza_deta_cupones(numero,mes,vcto,valor,estado," _
                    & "folio_cuponera,loginusuario) values('" & Numero & "'," & mes & ",'" _
                    & Format(Vcto, "yyyy-mm-dd") & "'," & valor & ",'GE'," _
                    & folio_cuponera & ",'" & Nom_usu_ses & "' )"
    conn.Execute var_sql, afectado
    
    If afectado > 0 Then
        agrega_cupon = True
    Else
        MsgBox "No fue posible realizar el cambio"
    End If
    
End Function

Private Function elimina_cupon(folio_cupon As Long) As Boolean
    Dim var_sql As String
    elimina_cupon = False
    var_sql = "update fnza_deta_cupones set estado = 'DE', loginusuario = '" & Nom_usu_ses & "' " _
            & " where folio_cupon = " & folio_cupon
    conn.Execute var_sql
    elimina_cupon = True
End Function

Private Sub reimprime_cupon(folio_cupon As Long)
    Dim var_sql As String
    var_sql = "update fnza_deta_cupones set estado = 'GE', loginusuario = '" & Nom_usu_ses & "' " _
            & " where folio_cupon = " & folio_cupon
    conn.Execute var_sql
End Sub

Private Sub llena_combo_general()
    Dim i As Long
    For i = 2005 To (val(Format(Date, "yyyy")) + 1)
        Combo_agno_general.AddItem i
    Next i
End Sub

Private Sub SeteaGrilla()
    Grilla.ClearStructure
    Grilla.Rows = 2
    Grilla.FormatString = "<RUN|<Nombre|<Curso|<Monto Total"
    Grilla.ColWidth(0) = 1200
    Grilla.ColWidth(1) = 4100
    Grilla.ColWidth(2) = 1000
    Grilla.ColWidth(3) = 1000
End Sub

Private Sub LlenaGrilla()
    Dim QR As String
    Dim RC As ADODB.Recordset
    Set RC = New ADODB.Recordset
    If Not Lista Then GoTo listado
    QR = "SELECT DISTINCT fdm.run_alu, CONCAT(p.ape_pat_alu,' ',p.ape_mat_alu,' ',p.nom_alu) as nombre, sum(fdm.monto_tot_cuota) total FROM fnza_mensualidad fm, fnza_detalle_mensualidad fdm, postulantes p WHERE fm.folio_mens=fdm.folio_mens AND fm.run_apo='" & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 2) & "' AND p.ci_alu=fdm.run_alu AND fdm.colegio='" & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 10) & "' AND fm.agno=fdm.agno And fdm.agno=" & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 4) & " group by fdm.run_alu order by fdm.run_alu"
    RC.Open QR, conn
    If RC.RecordCount = 0 Then
        RC.Close
        Exit Sub
    End If
    For i = 1 To RC.RecordCount
        Grilla.AddItem RC("run_alu") & Chr(9) & RC("nombre") & Chr(9) & CursoAlumno(RC("run_alu"), val(mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 4)), conn) & Chr(9) & RC("total"), i
        RC.MoveNext
    Next i
    RC.Close
    Frame2.Enabled = True
    Grilla.SetFocus
    Exit Sub
listado:
    QR = "SELECT DISTINCT fdm.run_alu, CONCAT(p.ape_pat_alu,' ',p.ape_mat_alu,' ',p.nom_alu) as nombre, sum(fdm.monto_tot_cuota) total FROM fnza_mensualidad fm, fnza_detalle_mensualidad fdm, postulantes p WHERE fm.folio_mens=fdm.folio_mens AND fm.run_apo='" & lbl_Run & "' AND p.ci_alu=fdm.run_alu AND fdm.colegio='" & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 9) & "' AND fm.agno=fdm.agno And fdm.agno=" & mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 3) & " group by fdm.run_alu order by fdm.run_alu"
    RC.Open QR, conn
    If RC.RecordCount = 0 Then
        RC.Close
        Exit Sub
    End If
    For i = 1 To RC.RecordCount
        Grilla.AddItem RC("run_alu") & Chr(9) & RC("nombre") & Chr(9) & CursoAlumno(RC("run_alu"), val(mshflex_cuponeras.TextMatrix(mshflex_cuponeras.Row, 3)), conn) & Chr(9) & RC("total"), i
        RC.MoveNext
    Next i
    RC.Close
    Frame2.Enabled = True
    Grilla.SetFocus
    
End Sub

Function CursoAlumno(ByVal RunAlu As String, ByVal agno As Integer, CNX As ADODB.Connection) As String
On Error GoTo Controlerror:
    Dim RC As ADODB.Recordset
    Dim QR As String
    Dim Ens As String
    Dim gra As String
    Dim Codens As Integer
    
    CursoAlumno = ""
    QR = "SELECT ce.cod_des,m.cod_ens,m.num_gra FROM matriculas m, codigos_ensenanza ce WHERE m.cod_ens=ce.cod_ens AND m.ci_alu='" & RunAlu & "' AND m.anno_mat='" & agno & "' ORDER BY m.fec_mat DESC LIMIT 1;"
    Set RC = New ADODB.Recordset
    RC.Open QR, CNX
    If RC.RecordCount = 0 Then
        CursoAlumno = "" '"No encontrado"
    Else
        RC.MoveFirst
        Codens = val(RC!Cod_Ens)
        Ens = RC!cod_des
        gra = RC!Num_Gra
        If Not (Codens = 996) Then
            CursoAlumno = gra & " " & Ens
        Else
            CursoAlumno = Ens
        End If
        RC.Close
        QR = "SELECT let_cur FROM alu_x_cur WHERE ci_alu='" & RunAlu & "' AND DATE_FORMAT(fec_mat,'%Y')='" & agno & "' AND fec_cam='1111-11-11' LIMIT 1;"
        RC.Open QR, CNX
        If RC.RecordCount = 0 Then
            RC.Close
            Set RC = Nothing
            Exit Function
        End If
        If (Codens = 996) Or (Codens = 10) Then
            CursoAlumno = Ens & " " & RC!Let_Cur
        Else
            CursoAlumno = gra & "º " & RC!Let_Cur & " " & Ens
        End If
    End If
    RC.Close
    Set RC = Nothing
    
    Exit Function
Controlerror:
    Set RC = Nothing
    Err.Raise Err.Number, Err.Source, Err.Description
End Function

Private Sub genera_ct00_masivo()
    Dim institucion As Boolean
    If cb_institucion.Text = "Colegio" Then
        colegio = RetornaCampo("cfg", "valor", "param='CODBC'", conn)
        CheckGen = RetornaCampo("cfg", "valor", "param='GENBC_READY' AND agno=" & Combo_agno_general.Text, conn)
    ElseIf cb_institucion.Text = "Pre Kinder" Then
        colegio = RetornaCampo("cfg", "valor", "param='CODPK'", conn)
        CheckGen = RetornaCampo("cfg", "valor", "param='GENPK_READY' AND agno=" & Combo_agno_general.Text, conn)
    Else
        MsgBox "Error no esta seleccionado el tipo de busqueda"
        Exit Sub
    End If
    
'    If CheckGen = "SI" Then
'        MsgBox "Este proceso ya fue generado", vbInformation, "Bloqueado"
'        Exit Sub
'    End If
    
    Set objRS = New ADODB.Recordset
        
    var_sql = "SELECT DISTINCT fm.run_apo FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm" _
            & " WHERE fdm.run_alu=m.ci_alu AND fdm.agno=m.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno=" & Combo_agno_general.Text & " and m.ind_cup=1 and fdm.monto_tot_cuota>0 AND (fdm.concepto='ct00') and fdm.colegio='" & colegio & "' and confirmado <> 'SI'" _
            & " GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno, fdm.colegio ORDER BY fm.run_apo;"
    objRS.Open var_sql, conn
    
    If objRS.RecordCount = 0 Then
        Exit Sub
    End If
    
    pgBar.Min = 0
    pgBar.Max = objRS.RecordCount
    pgBar.Value = pgBar.Min
    objRS.MoveFirst
    If cb_institucion.Text = "Colegio" Then
        institucion = False
    Else
        institucion = True
    End If
    For i = 1 To objRS.RecordCount
        crea_cuponera_cuota00 objRS!run_apo, val(Combo_agno_general.Text), institucion, conn
        objRS.MoveNext
        pgBar.Value = i
        pgBar.Refresh
    Next i
    'bloqueo de inserciones de cuponeras
    'conn.Execute "REPLACE INTO CFG(param,valor,descripcion,agno) VALUES('" & IIf(cb_institucion.Text = "Pre Kinder", "GENPK_READY", "GENBC_READY") & "','SI','Bloquear Generacion masiva de cuponera para " & Colegio & ".','" & Trim(Combo_agno_general.Text) & "');"
    'MsgBox "El archivo de cuponeras:" & vbCrLf & vbCrLf & IIf(cb_institucion.Text = "Pre Kinder", ARCH_GENPK, ARCH_GENBC) & vbCrLf & vbCrLf & "... fue creado exitosamente", vbInformation, "Proceso finalizado."
    MsgBox "Este proceso fue realizado exitosamente", vbInformation
End Sub

Private Function crea_cuponera_cuota00(rut As String, agno As Long, Pk As Boolean, conn As ADODB.Connection) As Boolean
    Dim i As Integer
    Dim cant_cupones
    Dim folio_cuponera As Long
    Dim Flag As Boolean
    Dim Fecha As Date
    Dim objRS As ADODB.Recordset
    Dim rec As ADODB.Recordset
    Dim rec2 As ADODB.Recordset
    Dim var_sql As String
    Dim colegio As String
    Dim CBC As String
        
    colegio = RetornaCampo("CFG", "valor", "param='" & IIf(Pk, "CODPK", "CODBC") & "'", conn)
    CBC = RetornaCampo("CFG", "valor", "param='CodConvBCIBC'", conn)
    crea_cuponera_cuota00 = False
    Flag = False
    i = 0
    cant_cupones = 0
    Set objRS = New ADODB.Recordset
    Set rec = New ADODB.Recordset
    Set rec2 = New ADODB.Recordset
    If Pk Then
        Col = CODPK
    Else
        Col = CODBC
    End If

    var_sql = " SELECT fm.run_apo, fdm.fech_venc, fdm.folio_mens, sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' , fdm.mes, fdm.agno, fdm.colegio, fdm.concepto" _
            & " FROM fnza_detalle_mensualidad as fdm, (select distinct m.ci_alu,m.anno_mat  from apo_x_alu axu, matriculas m where m.anno_mat = axu.agno and m.ci_alu = axu.ci_alu and axu.rut_apo = '" & rut & "' and m.anno_mat = " & agno & " and m.ind_cup = 1) as d, fnza_mensualidad as fm" _
            & " WHERE fdm.run_alu=d.ci_alu AND fdm.agno=d.anno_mat AND fdm.folio_mens=fm.folio_mens" _
            & " AND fdm.agno=" & agno & " and fdm.monto_tot_cuota>0 AND" _
            & " (fdm.concepto='ct00') and fm.run_apo = '" & rut & "' and fdm.colegio = '" & colegio & "' and fdm.mes = fm.mes " _
            & " GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno, fdm.colegio" _
            & " ORDER BY fm.run_apo, fdm.folio_mens, fdm.colegio, fdm.mes, fdm.agno"
    objRS.Open var_sql, conn
    
    'Si el valor del abono es mayor significa que la cuponera esta pagada
    If objRS("cuota") <= objRS("abono") Then
        Exit Function
    End If
    
    var_sql = "select sum(valor) as valor,fc.folio_cuponera from fnza_cuponeras fc,fnza_deta_cupones fdc" _
            & " where fc.folio_cuponera = fdc.folio_cuponera and" _
            & " rut_apo = '" & rut & "' and agno = " & agno & " and" _
            & " (esta_fin = 'PE' or esta_fin = 'GE' or esta_fin ='RE')" _
            & " and fc.colegio = '" & objRS("colegio") & "' and concepto = 'ct00'" _
            & " group by folio_cuponera"
    rec.Open var_sql, conn
    
    If rec.RecordCount = 0 Then
       'Creo cuponera si no esta creada
       var_sql = "insert into fnza_cuponeras(colegio,rut_apo,agno,mes_1,banco,fecha_gen,can_cupon,esta_fin,loginusuario,concepto)" _
               & " values('" & objRS("colegio") & "','" & objRS("run_apo") & "'," & objRS("agno") & "," _
               & objRS!mes & ",'" & "16" & "','" & Format(Date, "yyyy-mm-dd") & "'" _
               & ",'1','GE','" & Nom_usu_ses & "','ct00')"
       conn.Execute var_sql
       'Obtengo folio cuponera
        var_sql = "select folio_cuponera from fnza_cuponeras where " _
                & " rut_apo = '" & rut & "' and agno = " & agno & " ORDER BY folio_cuponera DESC LIMIT 1;"
        'REC2.Open var_sql, conn
        
        rec2.Open var_sql, conn
        If rec2.RecordCount > 0 Then
            folio_cuponera = rec2("folio_cuponera")
        Else
            MsgBox "Error en  generacion de cuponera"
            Exit Function
        End If
        rec2.Close
        'Creo cupon
        var_sql = "insert into fnza_deta_cupones(numero,mes,vcto,valor,estado," _
                & "ubicacion,folio_cuponera,loginusuario) values(" & IIf(Pk, CBC, "") & Format(agno, "0000") & Format(val(objRS!mes) - 2, "00") & "," & 2 & ",'" _
                & Format("2009-03-05", "yyyy-mm-dd") & "'," & (objRS("cuota") - objRS("abono")) & ",'GE'," _
                & cant_cupones & "," & folio_cuponera & ",'" & Nom_usu_ses & "')"
        conn.Execute var_sql
    Else
       If (objRS("cuota") - objRS("abono")) = rec("valor") Then
            'If el valor de la cuponera ya existe entonces la cuponera ya esta creada
            Exit Function
       Else
            'If el monto es mayor creo un nuevo cupon con la diferencia
            If (objRS("cuota") - objRS("abono")) > rec("valor") Then
                folio_cuponera = rec("folio_cuponera")
                var_sql = "insert into fnza_deta_cupones(numero,mes,vcto,valor,estado," _
                        & "ubicacion,folio_cuponera,loginusuario) values(" & IIf(Pk, CBC, "") & Format(agno, "0000") & Format(val(objRS!mes) - 2, "00") & "," & 2 & ",'" _
                        & Format("2008-03-05", "yyyy-mm-dd") & "'," _
                        & ((objRS("cuota") - objRS("abono")) - rec("valor")) & ",'GE'," _
                        & cant_cupones & "," & folio_cuponera & ",'" & Nom_usu_ses & "')"
                conn.Execute var_sql
            End If
            'If el monto es menor entonces elimino la cuponera y creo una nueva
            If (objRS("cuota") - objRS("abono")) < rec("valor") Then
                'Elimino cuponera
                If elimina_cuponera(rec("folio_cuponera")) Then
                    MsgBox "La cuponera fue creada con exito", vbInformation
                End If
                'Creo cuponera nueva
                var_sql = "insert into fnza_cuponeras(colegio,rut_apo,agno,mes_1,banco,fecha_gen,can_cupon,esta_fin,loginusuario,concepto)" _
                        & " values('" & objRS("colegio") & "','" & objRS("run_apo") & "'," & objRS("agno") & "," _
                        & objRS!mes & ",'" & "16" & "','" & Format(Date, "yyyy-mm-dd") & "'" _
                        & ",'1','GE','" & Nom_usu_ses & "','ct00')"
                conn.Execute var_sql
                'Obtengo folio cuponera
                var_sql = "select folio_cuponera from fnza_cuponeras where " _
                        & " rut_apo = '" & rut & "' and agno = " & agno & " ORDER BY folio_cuponera DESC LIMIT 1;"
                rec2.Open var_sql, conn
                If rec2.RecordCount > 0 Then
                    folio_cuponera = rec2("folio_cuponera")
                Else
                    MsgBox "Error en  generacion de cuponera"
                    Exit Function
                End If
                'REC.Close
                'Creo cupon
                var_sql = "insert into fnza_deta_cupones(numero,mes,vcto,valor,estado," _
                        & "ubicacion,folio_cuponera,loginusuario) values(" & IIf(Pk, CBC, "") & Format(agno, "0000") & Format(val(objRS!mes) - 2, "00") & "," & 2 & ",'" _
                        & Format("2008-03-05", "yyyy-mm-dd") & "'," _
                        & (objRS("cuota") - objRS("abono")) & ",'GE'," _
                        & cant_cupones & "," & folio_cuponera & ",'" & Nom_usu_ses & "')"
                conn.Execute var_sql
            End If
       End If
    End If
    rec.Close
    'Obtengo la cantidad de cupones de la cuponera
    var_sql = "select * from fnza_deta_cupones" _
            & " where folio_cuponera = " & folio_cuponera & " and estado <> 'DE' and estado <> 'EL'"
    rec.Open var_sql, conn
    'Ingreso la cantidad de cupones de la cuponera
    var_sql = "update fnza_cuponeras set can_cupon = " & rec.RecordCount _
            & " where folio_cuponera = " & folio_cuponera
    conn.Execute var_sql
    rec.Close
    objRS.Close
    crea_cuponera_cuota00 = True
End Function

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 Function valida_telefonos(Numero As String) As String
    Dim fono As String
    Dim i As Integer
    fono = ""
    For i = 1 To Len(Numero)
        If Asc(Mid(Numero, i, 1)) > 47 And Asc(Mid(Numero, i, 1)) < 58 Then
            fono = fono & Mid(Numero, i, 1)
        End If
    Next i
    valida_telefonos = fono
End Function

Public Sub actualiza_cuponeras_masivo()
    Dim i As Integer
    Dim var_sql As String
    var_sql = "drop table if exists temp_fnza_actualizacion"
    conn.Execute var_sql
    var_sql = "CREATE TABLE `temp_fnza_actualizacion`(" _
            & "`run_apo` varchar(10) NOT NULL default ''," _
            & "`folio_mens` bigint(20) unsigned NOT NULL default '0'," _
            & "`cuota` double(17,0) NOT NULL default '0'," _
            & "`abono` double(17,0) NOT NULL default '0'," _
            & "`mes` bigint(20) unsigned NOT NULL default '0'," _
            & "`agno` bigint(20) unsigned NOT NULL default '0'," _
            & "`colegio` varchar(15) NOT NULL default ''," _
            & "`concepto` varchar(4) NOT NULL default ''," _
            & " PRIMARY KEY  (`run_apo`,`cuota`,`abono`,`mes`,`agno`,`colegio`)" _
            & ") ENGINE=MyISAM DEFAULT CHARSET=latin1;"
    conn.Execute var_sql
    For i = 2008 To Anno
    var_sql = "insert into temp_fnza_actualizacion (run_apo,folio_mens,cuota,abono,mes,agno,colegio,concepto) SELECT fm.run_apo, fdm.folio_mens, sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' , fdm.mes, fdm.agno, fdm.colegio, fdm.concepto FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm WHERE fdm.run_alu=m.ci_alu AND fdm.agno=m.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno = " & i & " and m.ind_cup=1 and fdm.monto_tot_cuota>0 AND (fdm.concepto<>'ct00')" _
            & " GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno, fdm.colegio" _
            & " ORDER BY fm.run_apo, fdm.folio_mens, fdm.colegio, fdm.mes, fdm.agno"
    conn.Execute var_sql
                
    var_sql = "update temp_fnza_actualizacion f,fnza_deta_cupones as fdc,fnza_cuponeras as fc" _
            & " set fdc.estado = 'PA'" _
            & " where fc.folio_cuponera = fdc.folio_cuponera And fc.agno = " & i & " and fc.concepto <> 'ct00' and" _
            & " estado <> 'EL' and estado <> 'GE' and estado <> 'DE' and estado <> 'RE' and estado <> 'MC'" _
            & " and estado <> 'ME' and estado <> 'EC' and estado <> 'GC' and estado <> 'CC' and f.run_apo = fc.rut_apo and f.mes = fdc.mes" _
            & " and f.agno = fc.agno and f.cuota = fdc.valor and f.colegio = fc.colegio and f.cuota = f.abono"
    conn.Execute var_sql
    
    var_sql = "update temp_fnza_actualizacion f,fnza_deta_cupones as fdc,fnza_cuponeras as fc" _
            & " set fdc.estado = 'PD'" _
            & " where fc.folio_cuponera = fdc.folio_cuponera And fc.agno = " & i & " and fc.concepto <> 'ct00' and" _
            & " estado <> 'EL' and estado <> 'GE' and estado <> 'DE' and estado <> 'RE' and estado <> 'MC'" _
            & " and estado <> 'ME' and estado <> 'EC' and estado <> 'GC' and estado <> 'CC' and f.run_apo = fc.rut_apo and f.mes = fdc.mes" _
            & " and f.agno = fc.agno and f.cuota = fdc.valor and f.colegio = fc.colegio and f.cuota <> f.abono and f.abono > 0"
    conn.Execute var_sql
    
    var_sql = "update temp_fnza_actualizacion f,fnza_deta_cupones as fdc,fnza_cuponeras as fc" _
            & " set fdc.estado = 'PE'" _
            & " where fc.folio_cuponera = fdc.folio_cuponera And fc.agno = " & i & " and fc.concepto <> 'ct00' and" _
            & " estado <> 'EL' and estado <> 'GE' and estado <> 'DE' and estado <> 'RE' and estado <> 'MC'" _
            & " and estado <> 'ME' and estado <> 'EC' and estado <> 'GC' and estado <> 'CC' and f.run_apo = fc.rut_apo and f.mes = fdc.mes" _
            & " and f.agno = fc.agno and f.cuota = fdc.valor and f.colegio = fc.colegio and f.abono = 0"
    conn.Execute var_sql
    
    var_sql = "update temp_fnza_actualizacion f,fnza_deta_cupones as fdc,fnza_cuponeras as fc" _
            & " set fdc.estado = 'ME'" _
            & " where fc.folio_cuponera = fdc.folio_cuponera And fc.agno = " & i & " and fc.concepto <> 'ct00' and" _
            & " estado <> 'EL' and estado <> 'GE' and estado <> 'DE' and estado <> 'RE' and estado <> 'MC'" _
            & " and estado <> 'ME' and estado <> 'EC' and estado <> 'GC' and estado <> 'CC' and f.run_apo = fc.rut_apo and f.mes = fdc.mes" _
            & " and f.agno = fc.agno and f.cuota <> fdc.valor and f.colegio = fc.colegio"
    conn.Execute var_sql
    
    var_sql = "replace into fnza_elimina_cobranza(rut_apo,cab_bol,num_cup,fech_ven,monto,estado)" _
            & " select c.rut_apo,(numero),(c.mes-2),vcto,valor,'eliminar' from" _
            & " (select *" _
            & " from fnza_deta_cupones as fdc,fnza_cuponeras as fc" _
            & " where fc.folio_cuponera = fdc.folio_cuponera And fc.agno = " & i & " and fc.concepto <> 'ct00'" _
            & " and estado = 'GC') as c" _
            & " left join temp_fnza_actualizacion f" _
            & " on f.run_apo = c.rut_apo and f.mes = c.mes and f.agno = c.agno and f.colegio = c.colegio" _
            & " Where f.colegio Is Null"
    conn.Execute var_sql
    
    var_sql = "Update" _
            & " (select * from" _
            & " (select *" _
            & " from fnza_deta_cupones as fdc,fnza_cuponeras as fc" _
            & " where fc.folio_cuponera = fdc.folio_cuponera And fc.agno = " & i & " and fc.concepto <> 'ct00'" _
            & " and (estado = 'GC' or estado = 'PE')) as c" _
            & " left join temp_fnza_actualizacion f" _
            & " on f.run_apo = c.rut_apo and f.mes = c.mes and f.agno = c.agno and f.colegio = c.colegio" _
            & " where f.colegio is null) as cupo,fnza_deta_cupones fdc" _
            & " set fdc.estado = 'DE'" _
            & " Where cupo.folio_cuponera = fdc.folio_cuponera And cupo.Mes = fdc.Mes"
    conn.Execute var_sql
    
    Next i
End Sub

Private Sub crear_cuponeras_alu_sin_cupo()
    Dim var_sql As String
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim R As ADODB.Recordset
    Set R = New ADODB.Recordset
    
    var_sql = "drop table if exists temp_fnza_actualizacion"
    conn.Execute var_sql
    
    var_sql = "drop table if exists temp_cuponeras"
    conn.Execute var_sql
    
    var_sql = "CREATE TABLE `temp_fnza_actualizacion`(" _
            & "`run_apo` varchar(10) NOT NULL default ''," _
            & "`folio_mens` bigint(20) unsigned NOT NULL default '0'," _
            & "`cuota` double(17,0) NOT NULL default '0'," _
            & "`abono` double(17,0) NOT NULL default '0'," _
            & "`mes` bigint(20) unsigned NOT NULL default '0'," _
            & "`agno` bigint(20) unsigned NOT NULL default '0'," _
            & "`colegio` varchar(15) NOT NULL default ''," _
            & "`vcto` date NOT NULL default '0000-00-00'," _
            & "`concepto` varchar(4) NOT NULL default ''," _
            & "PRIMARY KEY  (`run_apo`,`cuota`,`abono`,`mes`,`agno`,`colegio`)" _
            & ") ENGINE=MyISAM DEFAULT CHARSET=latin1;"
    conn.Execute var_sql
    
    var_sql = "CREATE TABLE `temp_cuponeras` (" _
            & "`run_apo` varchar(10) NOT NULL default ''," _
            & "`valor` int(11) NOT NULL default '0'," _
            & "`mes` int(11) NOT NULL default '0'," _
            & "`agno` int(11) NOT NULL default '0'," _
            & "`colegio` varchar(20) NOT NULL default ''," _
            & "`concepto` varchar(4) NOT NULL default ''," _
            & "`estado` char(2) NOT NULL default ''," _
            & "PRIMARY KEY  (`run_apo`,`mes`,`agno`,`colegio`,`estado`)" _
            & ") ENGINE=MyISAM;"
    conn.Execute var_sql
    
    var_sql = "insert into temp_fnza_actualizacion(run_apo,folio_mens,cuota,abono,mes,agno,colegio,vcto,concepto)" _
            & " select * from" _
            & " (SELECT fm.run_apo,fdm.folio_mens,sum(fdm.monto_tot_cuota) as 'cuota', sum(fdm.monto_abono) as 'abono' ,fdm.mes,fdm.agno,fdm.colegio,fdm.fech_venc,fdm.concepto" _
            & " FROM fnza_detalle_mensualidad as fdm, matriculas as m, fnza_mensualidad as fm,alu_x_cur as axc" _
            & " Where fdm.run_alu = m.ci_alu" _
            & " AND fdm.agno=m.anno_mat AND fdm.folio_mens=fm.folio_mens AND fdm.agno = 2009 and m.ind_cup=1 and fdm.monto_tot_cuota>0" _
            & " AND (fdm.concepto<>'ct00') and m.activo_alu = 'SI'" _
            & " and m.ci_alu = axc.ci_alu and m.cod_ens = axc.cod_ens and m.num_gra = axc.num_gra and m.anno_mat = axc.agno and m.activo_alu = axc.activo_alu" _
            & " GROUP BY fm.run_apo, fm.folio_mens, fm.mes, fm.agno, fdm.colegio" _
            & " ORDER BY fm.run_apo, fdm.folio_mens, fdm.colegio, fdm.mes, fdm.agno) as f" _
            & " Where f.cuota > f.abono And f.abono = 0"
    conn.Execute var_sql
    
    var_sql = "insert into temp_cuponeras(run_apo,valor,mes,agno,colegio,concepto,estado)" _
            & " select distinct rut_apo,valor,mes,agno,fc.colegio,concepto,estado from fnza_deta_cupones as fdc,fnza_cuponeras as fc" _
            & " where fc.folio_cuponera = fdc.folio_cuponera And fc.agno = 2009 and fc.concepto <> 'ct00'" _
            & " and estado <> 'EL' and estado <> 'GE' and estado <> 'DE' and estado <> 'RE'" _
            & " and estado <> 'MC' and estado <> 'CC' and esta_fin <> 'EL'  and esta_fin <> 'PA'"
    'MsgBox var_sql, vbCritical + vbOKOnly
    conn.Execute var_sql
    
    var_sql = "select f.* from temp_fnza_actualizacion as f left join" _
            & " temp_cuponeras as c on f.run_apo = c.run_apo and f.colegio = c.colegio and f.agno = c.agno and f.mes = c.mes" _
            & " Where adddate(Vcto, 22) < current_date And c.run_apo Is Null"
    objRS.Open var_sql, conn
    While Not objRS.EOF
        var_sql = "select * from fnza_cuponeras" _
                & " where rut_apo = '" & objRS("run_apo") _
                & "' and agno = " & objRS("agno") & " and colegio = '" & objRS("colegio") _
                & "' and" _
                & " (esta_fin = 'PE' or esta_fin = 'PA') and concepto = 'esco'"
        rec.Open var_sql, conn
        If rec.EOF Then
            var_sql = "insert into fnza_cuponeras(colegio,rut_apo,agno,mes_1,banco," _
                    & "fecha_gen,can_cupon,esta_fin,loginusuario,concepto)" _
                    & " values('" & objRS("colegio") & "','" & objRS("run_apo") & "'," & objRS("agno") _
                    & "," _
                    & objRS!mes & ",'" & "16" & "','" & Format(Date, "yyyy-mm-dd") & "'" _
                    & ",'1','PE','" & Nom_usu_ses & "','esco')"
            conn.Execute var_sql
            
            var_sql = "select folio_cuponera from fnza_cuponeras where " _
                    & " rut_apo = '" & objRS("run_apo") & "' and agno = " & objRS("agno") & " ORDER BY folio_cuponera DESC LIMIT 1;"
            R.Open var_sql, conn
            If R.RecordCount > 0 Then
                folio_cuponera = R("folio_cuponera")
            Else
                MsgBox "Error en  generacion de cuponera"
                Exit Sub
            End If
            R.Close
            'End If
            'If Val(objRS!abono) = 0 Then
             '   cant_cupones = cant_cupones + 1
             
            If Len(objRS("colegio")) > 6 Then
                Pk = True
            Else
                Pk = False
            End If
            
            'colegio = RetornaCampo("CFG", "valor", "param='" & IIf(Pk, "CODPK", "CODBC") & "'", conn)
            CBC = RetornaCampo("CFG", "valor", "param='CodConvBCIBC'", conn)
             
            var_sql = "insert into fnza_deta_cupones(numero,mes,vcto,valor,estado," _
                    & "ubicacion,folio_cuponera,loginusuario) values(" & IIf(Pk, CBC, "") & Format(objRS("agno"), "0000") & Format(val(objRS!mes) - 2, "00") & "," & objRS("mes") & ",'" _
                    & Format(objRS!Vcto, "yyyy-mm-dd") & "'," & objRS("cuota") & ",'EC'," _
                    & "1," & folio_cuponera & ",'" & Nom_usu_ses & "')"
            conn.Execute var_sql
            'End If
        Else
            If Len(objRS("colegio")) > 6 Then
                Pk = True
            Else
                Pk = False
            End If
            
            'colegio = RetornaCampo("CFG", "valor", "param='" & IIf(Pk, "CODPK", "CODBC") & "'", conn)
            CBC = RetornaCampo("CFG", "valor", "param='CodConvBCIBC'", conn)
            folio_cuponera = rec("folio_cuponera")
            var_sql = "insert into fnza_deta_cupones(numero,mes,vcto,valor,estado," _
                    & "ubicacion,folio_cuponera,loginusuario) values(" & IIf(Pk, CBC, "") & Format(objRS("agno"), "0000") & Format(val(objRS!mes) - 2, "00") & "," & objRS("mes") & ",'" _
                    & Format(objRS!Vcto, "yyyy-mm-dd") & "'," & objRS("cuota") & ",'EC'," _
                    & "1," & folio_cuponera & ",'" & Nom_usu_ses & "')"
            conn.Execute var_sql
        End If
        rec.Close
        objRS.MoveNext
    Wend
    objRS.Close
 
    var_sql = "select f.* from temp_fnza_actualizacion as f  left join" _
            & " temp_cuponeras as c on f.run_apo = c.run_apo and f.colegio = c.colegio and f.agno = c.agno and f.mes = c.mes" _
            & " where adddate(vcto,22) < current_date and estado = 'ME'"
    objRS.Open var_sql, conn
    While Not objRS.EOF
        var_sql = "select folio_cupon from fnza_deta_cupones as fdc,fnza_cuponeras as fc" _
                & " where fc.folio_cuponera = fdc.folio_cuponera And fc.agno = " & objRS("agno") _
                & " and fc.concepto <> 'ct00'" _
                & " and rut_apo = '" & objRS("run_apo") & "' and estado = 'ME'" _
                & " and mes = " & objRS("mes") & " and fc.colegio = '" & objRS("colegio") & "'"
        rec.Open var_sql, conn
        var_sql = "update fnza_deta_cupones set estado = 'EC',valor = " _
        & objRS("cuota") & " where folio_cupon = " & rec("folio_cupon")
        conn.Execute var_sql
        rec.Close
        objRS.MoveNext
    Wend
    objRS.Close
   
End Sub

Private Sub llena_combo_mes()
    Dim i As Integer
    For i = 3 To 12
        cb_mes_masivos.AddItem IIf(i < 10, (0 & i), i)
    Next i
    cb_mes_masivos.Text = "03"
End Sub
