VERSION 5.00
Begin VB.Form Form_ContratoCrea_hue 
   BorderStyle     =   4  'Fixed ToolWindow
   Caption         =   "Procesando Espere"
   ClientHeight    =   2100
   ClientLeft      =   45
   ClientTop       =   285
   ClientWidth     =   6765
   LinkTopic       =   "Form4"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   2100
   ScaleWidth      =   6765
   ShowInTaskbar   =   0   'False
   Visible         =   0   'False
   WindowState     =   1  'Minimized
   Begin VB.CommandButton Command8 
      Caption         =   "Imprimir Aviso SMC00 Curso"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   5040
      TabIndex        =   12
      Top             =   1440
      Width           =   1455
   End
   Begin VB.CommandButton Command7 
      Caption         =   "Carta Aviso SMC00"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   3360
      TabIndex        =   11
      Top             =   1440
      Width           =   1575
   End
   Begin VB.CommandButton Command6 
      Caption         =   "Aviso Honorarios"
      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        =   10
      Top             =   1440
      Width           =   1455
   End
   Begin VB.CommandButton Command5 
      Caption         =   "Imprimir aviso curso"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   240
      TabIndex        =   9
      Top             =   1440
      Width           =   1455
   End
   Begin VB.CommandButton Command4 
      Caption         =   "Imprimir CURSO"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   5040
      TabIndex        =   8
      Top             =   840
      Width           =   1455
   End
   Begin VB.CommandButton Command3 
      Caption         =   "Carta Aviso"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   3360
      TabIndex        =   7
      Top             =   840
      Width           =   1575
   End
   Begin VB.TextBox codnumlet 
      Height          =   285
      Left            =   5520
      TabIndex        =   6
      Top             =   360
      Width           =   495
   End
   Begin VB.CommandButton Command2 
      Caption         =   "Imprimir Uno"
      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        =   5
      Top             =   840
      Width           =   1455
   End
   Begin VB.TextBox opm 
      Height          =   285
      Left            =   3960
      TabIndex        =   4
      Top             =   360
      Width           =   495
   End
   Begin VB.TextBox tpago 
      Height          =   285
      Left            =   3000
      TabIndex        =   3
      Top             =   360
      Width           =   495
   End
   Begin VB.TextBox agno 
      Height          =   285
      Left            =   2040
      TabIndex        =   2
      Top             =   360
      Width           =   735
   End
   Begin VB.CommandButton Command1 
      Caption         =   "Ver"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   240
      TabIndex        =   1
      Top             =   840
      Width           =   1455
   End
   Begin VB.TextBox run_apo 
      Height          =   285
      Left            =   240
      TabIndex        =   0
      Top             =   360
      Width           =   1575
   End
End
Attribute VB_Name = "Form_ContratoCrea_hue"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public Primera As Boolean

Public Sub Command1_Click()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    Dim obligacion_numero As String
    Dim obligacion_texto As String
    Dim texto As String
    
    Dim var_sql As String
    Dim campo As String
    
    Dim masivo As Boolean
    Dim otro As Integer
    Dim suma As Long
    Dim quesacar As ADODB.Recordset
    Dim queponer As ADODB.Recordset
    Dim lineasec As ADODB.Recordset
    Dim grabar As ADODB.Command
    Dim cuerpo As ADODB.Command
    Dim Cabecera As ADODB.Command
    Primera = True
    Dim Tablas, datos, ensegrado, datoreal As String
    Dim creandosql, labelc, Establo As String
    datos = ""
    
    Dim EnPalabras As clsNum2Let
    Set EnPalabras = New clsNum2Let
    Dim monto_cuota_incorporacion As Long
    Dim monto_matricula As Long
    Dim monto_total_uf As Double
    Dim Desde As Integer
    Dim Hasta As Integer
    Dim cuotas As Integer
    Dim valor As Double
    
    
    var_sql = "select * from cfg where param = 'LETRAS'"
    objRS.Open var_sql, conn
    If objRS("valor") = "SI" Then
        masivo = True
    Else
        masivo = False
    End If
    objRS.Close
    
    If masivo = True Then
    campo = """(select sum(monto_tot_cuota) as totalagno from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
            & " where axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and axa.agno = {AGNO}" _
            & " and rut_apo = '{RUT_APO}' and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno) div" _
            & " (select count(monto_tot_cuota) as totalagno from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
            & " where axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and axa.agno = {AGNO}" _
            & " and rut_apo = '{RUT_APO}' and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno) as valorcuota"""
    Else
    campo = """(select sum(monto_tot_cuota) as totalagno from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
            & " where axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = {AGNO}" _
            & " and rut_apo = '{RUT_APO}' and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno) div" _
            & " (select count(monto_tot_cuota) as totalagno from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
            & " where axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = {AGNO}" _
            & " and rut_apo = '{RUT_APO}' and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno) as valorcuota"""
    End If
    
    var_sql = "update cont_camposlabel set campo = " & campo & " where label = '[VALORCUOTA]'"
    conn.Execute var_sql
        
    creandosql = "Replace into cont_datos set ci_apo='" & Me.run_apo.Text & "',opcion_pago='" & Me.tpago.Text & "',"
    
    Tablas = "CREATE TABLE if not EXISTS `tmp_contrato`  (`CI_APO` varchar(10) NOT NULL default '',`ORDEN` tinyint(4) NOT NULL default '0',`TXT` text NOT NULL,`AGNO` varchar(4) NOT NULL default '',PRIMARY KEY  (`CI_APO`,`AGNO`)) ENGINE=MyISAM"
    
    Set grabar = New ADODB.Command
    grabar.CommandType = adCmdText
    grabar.ActiveConnection = conn
    grabar.CommandText = Tablas
    grabar.Execute
              
    Tablas = "CREATE TABLE if not EXISTS `tmp_contratocab` (`CI_APO` varchar(10) NOT NULL default '',`TXT` text,`ENCARGADO` varchar(50) NOT NULL default '',`CODNUMLET` varchar(5) default NULL,`AGNO` varchar(4) NOT NULL default '', PRIMARY KEY  (`CI_APO`)) ENGINE=MyISAM"
    Set grabar = New ADODB.Command
    grabar.CommandType = adCmdText
    grabar.ActiveConnection = conn
    grabar.CommandText = Tablas
    grabar.Execute
    
    Set grabar = New ADODB.Command
    grabar.CommandType = adCmdText
    grabar.ActiveConnection = conn
    grabar.CommandText = "delete from tmp_contratocab where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
    grabar.Execute
    
    Set grabar = New ADODB.Command
    grabar.CommandType = adCmdText
    grabar.ActiveConnection = conn
    grabar.CommandText = "delete from tmp_contrato where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
    grabar.Execute
    
    Set quesacar = New ADODB.Recordset
    quesacar.Open "select * from `cont_camposlabel` order by tabla,label,descripcion", conn
    
    Set lineasec = New ADODB.Recordset
    lineasec.Open "Select orden from cont_formato_linea where agno=" & Me.agno.Text, conn  ' Por mientras"
     
    While Not quesacar.EOF
        
        If quesacar!tabla = "apoderados" Then
            
            datos = "Select " & Trim(quesacar!campo) & " from " & quesacar!tabla & " where " & quesacar!Where
            datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
            datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)
            
            Set queponer = New ADODB.Recordset
            queponer.Open datos, conn
            
            If quesacar!Label = "[NOM_APODERADO]" Then
                datoreal = queponer!nom_apoderado
            ElseIf quesacar!Label = "[CI_APO]" Then
                datoreal = queponer!ci_apo
            End If
            
        ElseIf quesacar!tabla = "buscado" Then
        
            If Trim(quesacar!Where) <> "" Then
                datos = quesacar!campo & " where " & quesacar!Where
            Else
                datos = quesacar!campo
            End If
            
            datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
            datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)
            
                
            Set queponer = New ADODB.Recordset
            queponer.Open datos, conn
            
            If quesacar!Label = "[DIRCOM_APO]" Then
                datoreal = queponer!dircom_apo
            ElseIf quesacar!Label = "[NOM_COL]" Then
                datoreal = queponer!NOM_COL
                Establo = datoreal
            End If
            
        ElseIf quesacar!tabla = "establecimientos" Then
        
            If Trim(quesacar!Where) <> "" Then
                datos = "Select " & Trim(quesacar!campo) & " from " & quesacar!tabla & " where " & quesacar!Where
            Else
                datos = "Select " & Trim(quesacar!campo) & " from " & quesacar!tabla
            End If
                
            datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
            datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)

            Set queponer = New ADODB.Recordset
            queponer.Open datos, conn
            
            If quesacar!Label = "[DIRCOM_COL]" Then
                datoreal = queponer!dircom_col
            End If
       ElseIf quesacar!tabla = "generado" Then
             datos = "Select " & Trim(quesacar!campo)
             datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
             datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)
             
             Set queponer = New ADODB.Recordset
             queponer.Open datos, conn
             
            If quesacar!Label = "[FEC_ACTUAL]" Then
                datoreal = Format(queponer!fec_actual, "dd \de Mmmm \de yyyy")
                creandosql = creandosql & "fecha_contrato='" & Format(queponer!fec_actual, "yyyy-mm-dd") & "',"
            ElseIf quesacar!Label = "[VALORCUOTA]" Then
                datoreal = Format(queponer!ValorCuota, "###,###,###")
                creandosql = creandosql & "valor_cuota=" & queponer!ValorCuota & ","
            End If
       ElseIf quesacar!tabla = "fnza_mensualidad" Then
            datos = "Select " & quesacar!campo & " from " & quesacar!tabla & " where " & quesacar!Where
            datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
            datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)
            'datos = Replace(datos, "{CODCOL}", Me.colegio.Text, 1)
                                  
            If masivo = True Then
                datos = "select " & quesacar!campo & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
                  & " where axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and axa.agno = " & Me.agno.Text _
                  & " and rut_apo = '" & Me.run_apo.Text & "' and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno"
            Else
                datos = "select " & quesacar!campo & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
                  & " where axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text _
                  & " and rut_apo = '" & Me.run_apo.Text & "' and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno"
            End If
              
                       
            suma = 0
            Set queponer = New ADODB.Recordset
            queponer.Open datos, conn
            
            If quesacar!Label = "[TOTALAGNO]" Then
                While Not queponer.EOF
                    suma = suma + queponer!totalagno
                    queponer.MoveNext
                Wend
                datoreal = Format(suma, "###,###,###")
                creandosql = creandosql & "total_cuotas=" & Trim(str(suma)) & ","
            ElseIf quesacar!Label = "[TOTALCUOTAS]" Then
                datoreal = Format(queponer!totalcuotas, "###,###,###")
                creandosql = creandosql & "num_cuotas=" & queponer!totalcuotas & ","
            End If

       ElseIf quesacar!tabla = "traspasadoprg" Then
            If quesacar!Label = "[OPCIONPAGO]" Then
                datoreal = Me.tpago.Text
            End If
       End If
        If datoreal = "" Then
            datoreal = "0"
        End If
       Set grabar = New ADODB.Command
       grabar.ActiveConnection = conn
       grabar.CommandType = adCmdText
       
       Set cuerpo = New ADODB.Command
       cuerpo.ActiveConnection = conn
       cuerpo.CommandType = adCmdText
       
       
        If Primera Then
            grabar.CommandText = "INSERT INTO tmp_contratocab (txt,ci_apo,encargado,agno,codnumlet) Select replace(cont_formato_cab.txt,'" & quesacar!Label & "','" & datoreal & "') as texto, '" & Me.run_apo.Text & "' as rut_apo,  '" & Nom_com_usu_ses & "' as nomusu,'" & Me.agno.Text & "' as agno, '" & Me.codnumlet.Text & "' as codnumlet  from cont_formato_cab where agno=" & Me.agno.Text
          While Not lineasec.EOF
             If Trim(lineasec!Orden) = "3" Then
                        
                'cuerpo.CommandText = "INSERT INTO tmp_contrato (ci_apo,txt,orden,agno) select '" & Me.run_apo.Text & "' as ci_apo, replace(concat(Left(cont_formato_linea.txt,155),'segun tabla de pago adjunta.\n'),'" & quesacar!Label & "','" & datoreal & "') as txt, cont_formato_linea.orden,'" & Me.agno.Text & "' as agno from cont_formato_linea where cont_formato_linea.orden=" & lineasec!Orden & " and agno='" & Me.agno.Text & "'"
                cuerpo.CommandText = "INSERT INTO tmp_contrato (ci_apo,txt,orden,agno) select '" & Me.run_apo.Text & "' as ci_apo, replace(cont_formato_linea.txt,'" & quesacar!Label & "','" & datoreal & "') as txt, cont_formato_linea.orden,'" & Me.agno.Text & "' as agno from cont_formato_linea  where cont_formato_linea.orden=" & lineasec!Orden & " and agno='" & Me.agno.Text & "'"
                 var_sql = "select sum(fdm.monto_tot_cuota) as matricula" _
                         & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
                         & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
                         & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'ct00'"
                 objRS.Open var_sql, conn
                 If objRS.RecordCount > 0 Then
                 
                    var_sql = "update tmp_contrato set txt = REPLACE(txt,'Matricula:','" & "Matricula:" & objRS("matricula") & "') where ci_apo = '" & Me.run_apo.Text & "' and agno = " & Me.agno.Text & " and orden = 3"
                    conn.Execute var_sql
                 End If
                 objRS.Close
              Else
                cuerpo.CommandText = "INSERT INTO tmp_contrato (ci_apo,txt,orden,agno) select '" & Me.run_apo.Text & "' as ci_apo, replace(cont_formato_linea.txt,'" & quesacar!Label & "','" & datoreal & "') as txt, cont_formato_linea.orden,'" & Me.agno.Text & "' as agno from cont_formato_linea  where cont_formato_linea.orden=" & lineasec!Orden & " and agno='" & Me.agno.Text & "'"
              End If
             cuerpo.Execute
             If Trim(lineasec!Orden) = "3" Then
                 var_sql = "select sum(fdm.monto_tot_cuota) as matricula" _
                         & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
                         & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
                         & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'ct00'"
        
                 objRS.Open var_sql, conn
                 If objRS.RecordCount > 0 Then
                    monto_matricula = objRS("matricula")
                    var_sql = "update tmp_contrato set txt = REPLACE(txt,'Matrícula:','" & "Matrícula:$" & FormatNumber(objRS("matricula"), 0) & "') where ci_apo = '" & Me.run_apo.Text & "' and agno = " & Me.agno.Text & " and orden = 3"
                    conn.Execute var_sql
                 End If
                 objRS.Close
             End If
             If Trim(lineasec!Orden) = "3" Then
                 var_sql = "select if(sum(fdm.monto_tot_cuota) is null,0,sum(fdm.monto_tot_cuota)) as incorporacion" _
                         & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
                         & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
                         & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'inco'"
                 objRS.Open var_sql, conn
                 If objRS.RecordCount > 0 Then
                    If objRS("incorporacion") > 0 Then
                        monto_cuota_incorporacion = objRS("incorporacion")
                        var_sql = "update tmp_contrato set txt = REPLACE(txt,'Cuota de Incorporación:','" & "Cuota de Incorporación:$" & FormatNumber(objRS("incorporacion"), 0) & "') where ci_apo = '" & Me.run_apo.Text & "' and agno = " & Me.agno.Text & " and orden = 3"
                        conn.Execute var_sql
                    End If
                 End If
                 objRS.Close
             End If
             If Trim(lineasec!Orden) = "3" Then
                If Me.agno.Text < 2022 Then
                    var_sql = "select if(sum(fdm.monto_tot_cuota) is null,0,sum(fdm.monto_tot_cuota)/count(distinct fdm.mes)) as valor,if(count(fdm.monto_tot_cuota) is null,0,count(distinct fdm.mes)) as cuotas" _
                             & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
                             & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
                            & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'esco'"
                    objRS.Open var_sql, conn
                    If objRS.RecordCount > 0 Then
                        var_sql = "update tmp_contrato set txt = REPLACE(txt,'Mensualidades:','" & "Mensualidades:" & objRS("cuotas") & " cuotas iguales de $" & FormatNumber(objRS("valor"), 0) & " con vencimiento los días 10 de cada mes.') where ci_apo = '" & Me.run_apo.Text & "' and agno = " & Me.agno.Text & " and orden = 3"
                        conn.Execute var_sql
                    End If
                    objRS.Close
                Else
'                    If Me.agno.Text = 2022 Then
                        var_sql = "select if(sum(fdm.monto_tot_cuota_UF) is null,0,sum(fdm.monto_tot_cuota_UF)/count(distinct fdm.mes)) as valor,if(count(fdm.monto_tot_cuota_UF) is null,0,count(distinct fdm.mes)) as cuotas," _
                                 & "if(sum(fdm.monto_tot_cuota_UF) is null,0,sum(fdm.monto_tot_cuota_UF)) as monto_total_uf,min(mes) as desde,max(mes) as hasta from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
                                 & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
                                & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'esco'"
                        objRS.Open var_sql, conn
                        If objRS.RecordCount > 0 Then
                            monto_total_uf = objRS("monto_total_uf")
                            Desde = objRS("desde")
                            Hasta = objRS("hasta")
                            cuotas = objRS("cuotas")
                            valor = objRS("valor")
                                                        
                            var_sql = "update tmp_contrato set txt = REPLACE(txt,'Mensualidades:','" & "Mensualidades:" & objRS("cuotas") & " cuotas iguales de UF " & Round(objRS("valor"), 2) & " con vencimiento los días 10 de cada mes.') where ci_apo = '" & Me.run_apo.Text & "' and agno = " & Me.agno.Text & " and orden = 3"
                            conn.Execute var_sql
                        End If
                        objRS.Close
'                    Else
'                        var_sql = "select if(sum(fdm.monto_tot_cuota_UF) is null,0,sum(fdm.monto_tot_cuota_UF)/count(distinct fdm.mes)) as valor,if(count(fdm.monto_tot_cuota_UF) is null,0,count(distinct fdm.mes)) as cuotas" _
'                                 & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
'                                 & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
'                                & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'esco'"
'                           objRS.Open var_sql, conn
'                        If objRS.RecordCount > 0 Then
'
'                        End If
'                        objRS.Close
'
'                    End If
                End If
             End If
             lineasec.MoveNext
          Wend
          Primera = False
        Else
           grabar.CommandText = "update tmp_contratocab set txt=replace(txt,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
           
           While Not lineasec.EOF
              cuerpo.CommandText = "update tmp_contrato set txt=replace(txt,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "' and orden=" & lineasec!Orden
              cuerpo.Execute
              lineasec.MoveNext
           Wend
        End If
        lineasec.MoveFirst
        grabar.Execute
'       queponer.Close
        quesacar.MoveNext
    Wend
    
    quesacar.Close
    
    '*****************************************************Alumnos del Apoderado
    
    Set quesacar = New ADODB.Recordset
    If masivo = True Then
        sqlpor = "Select rpad(concat(p.nom_alu,' ',p.ape_pat_alu,' ',p.ape_mat_alu),60,' ') AS nom_alu, m.cod_ens as ense, m.num_gra as grado,a.rut_apo as rut_apo FROM postulantes as p, apoderados as a, matriculas as m, apo_x_alu as t WHERE a.rut_apo='" & Me.run_apo.Text & "' and a.rut_apo=t.rut_apo AND t.ci_alu=m.ci_alu AND m.anno_mat=t.agno AND m.ci_alu=p.ci_alu AND m.activo_alu='SI' and date_format(m.fec_retiro,'%d-%m-%Y')='11-11-1111' AND m.anno_mat=" & Me.agno.Text & ";"
    Else
        sqlpor = "Select rpad(concat(p.nom_alu,' ',p.ape_pat_alu,' ',p.ape_mat_alu),60,' ') AS nom_alu, m.cod_ens as ense, m.num_gra as grado,a.rut_apo as rut_apo FROM postulantes as p, apoderados as a, matriculas as m, apo_x_alu as t WHERE a.rut_apo='" & Me.run_apo.Text & "' and a.rut_apo=t.rut_apo and m.confirmado='SI' AND t.ci_alu=m.ci_alu AND m.anno_mat=t.agno AND m.ci_alu=p.ci_alu AND m.activo_alu='SI' and date_format(m.fec_retiro,'%d-%m-%Y')='11-11-1111' AND m.anno_mat=" & Me.agno.Text & ";"
    End If
              
    quesacar.Open sqlpor, conn
    datos = "" & vbCrLf & vbCrLf
    If quesacar.RecordCount < 1 Then
        Exit Sub
    End If
    
    While Not quesacar.EOF
            If quesacar!ense = "310" Then
                ensegrado = "Medio"
            ElseIf quesacar!ense = "010" Then
                ensegrado = "Párvulo"
            ElseIf quesacar!ense = "110" Then
                ensegrado = "Básico"
            ElseIf quesacar!ense = "996" Then
                ensegrado = "PreKinder"
            ElseIf quesacar!ense = "222" Then
                ensegrado = "Play Group Media Mayor"
            ElseIf quesacar!ense = "111" Then
                ensegrado = "Play Group"
            End If
        
        
            datos = datos & "Nombre: " & quesacar!Nom_Alu & " Curso " & Me.agno.Text & " : "
    
            If Me.opm.Text = "S" Then
                cursoalu = quesacar!grado
                cursoalu = InputBox("Para el Alumno : " & quesacar!Nom_Alu & " el Curso es ", "Definicion De Cursos", quesacar!grado & "º  " & ensegrado)
                datos = datos & cursoalu & vbCrLf
            Else
                datos = datos & quesacar!grado & "º  " & ensegrado & vbCrLf
            End If
         
    quesacar.MoveNext
    Wend
    
    
    Set grabar = New ADODB.Command
    grabar.ActiveConnection = conn
    grabar.CommandType = adCmdText
    grabar.CommandText = "update tmp_contratocab set txt=concat(txt,'" & datos & "') where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
    grabar.Execute
    quesacar.Close
    
    Set grabar = New ADODB.Command
    grabar.ActiveConnection = conn
    grabar.CommandType = adCmdText
    grabar.CommandText = creandosql & "encargado='" & Nom_com_usu_ses & "'"
    grabar.Execute
    
    If Me.opm.Text = "N" Then
        Exit Sub
    End If
    
    '''''' Obtengo monto 2023
    If Me.agno.Text >= 2023 Then
        var_sql = "select sum(fdm.monto_tot_cuota) as matricula" _
                         & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
                         & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
                         & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'ct00'"
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
             monto_matricula = objRS("matricula")
'             var_sql = "update tmp_contrato set txt = REPLACE(txt,'Matrícula:','" & "Matrícula:$" & FormatNumber(objRS("matricula"), 0) & "') where ci_apo = '" & Me.run_apo.Text & "' and agno = " & Me.agno.Text & " and orden = 3"
'             conn.Execute var_sql
        End If
        objRS.Close
        
        var_sql = "select if(sum(fdm.monto_tot_cuota) is null,0,sum(fdm.monto_tot_cuota)) as incorporacion" _
                         & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
                         & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
                         & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'inco'"
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
            If objRS("incorporacion") > 0 Then
                   monto_cuota_incorporacion = objRS("incorporacion")
'                   var_sql = "update tmp_contrato set txt = REPLACE(txt,'Cuota de Incorporación:','" & "Cuota de Incorporación:$" & FormatNumber(objRS("incorporacion"), 0) & "') where ci_apo = '" & Me.run_apo.Text & "' and agno = " & Me.agno.Text & " and orden = 3"
'                   conn.Execute var_sql
            End If
        End If
        objRS.Close
    
        var_sql = "select if(sum(fdm.monto_tot_cuota_UF) is null,0,sum(fdm.monto_tot_cuota_UF)/count(distinct fdm.mes)) as valor,if(count(fdm.monto_tot_cuota_UF) is null,0,count(distinct fdm.mes)) as cuotas," _
                & "if(sum(fdm.monto_tot_cuota_UF) is null,0,sum(fdm.monto_tot_cuota_UF)) as monto_total_uf,min(mes) as desde,max(mes) as hasta from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm where axa.agno = m.anno_mat" _
                & " and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text & " and rut_apo = '" & Me.run_apo.Text & "'" _
                & " and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto = 'esco'"
        objRS.Open var_sql, conn
        If objRS.RecordCount > 0 Then
                            monto_total_uf = objRS("monto_total_uf")
                            monto_total_uf = Round(monto_total_uf, 2)
                            Desde = objRS("desde")
                            Hasta = objRS("hasta")
                            cuotas = objRS("cuotas")
                            valor = objRS("valor")
                            valor = Round(valor, 2)
                                                        
'                            var_sql = "update tmp_contrato set txt = REPLACE(txt,'Mensualidades:','" & "Mensualidades:" & objRS("cuotas") & " cuotas iguales de UF " & Round(objRS("valor"), 2) & " con vencimiento los días 10 de cada mes.') where ci_apo = '" & Me.run_apo.Text & "' and agno = " & Me.agno.Text & " and orden = 3"
'                            conn.Execute var_sql
        End If
        objRS.Close
    
    End If
   
    
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
    var_sql = "SELECT valor FROM cfg where param = 'CODBC'"
    rec.Open var_sql, conn
    If rec("valor") = "HUE001" Then
        colegios = "HUE001"
        If Me.agno.Text = 2026 Then
            Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal_huechuraba_uf_2026.rpt"
        Else
            Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal_huechuraba_uf_2027.rpt"
''            If Me.agno.Text = 2022 Then
''                Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal_huechuraba_uf.rpt"
''            Else
''                If Me.agno.Text = 2023 Then
''                    Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal_huechuraba_uf_2023_2.rpt"
''                Else
''                    Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal_huechuraba_uf_2024.rpt"
''                End If
''            End If
        End If
    Else
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal_macul.rpt"
    End If
    rec.Close
    
    ' Obtengo obligacion
    obligacion = 0
    var_sql = "SELECT folio FROM fnza_contrato WHERE agno = " & Me.agno.Text & " AND rut_apo = '" & Me.run_apo.Text & "'"
    rec.Open var_sql, conn
    If rec.RecordCount > 0 Then obligacion = rec("folio")
    rec.Close
    If obligacion = 0 Then
        var_sql = "SELECT MAX(folio) as folio FROM fnza_contrato WHERE agno = " & Me.agno.Text
        rec.Open var_sql, conn
        If IsNull(rec("folio")) Then
            obligacion = 1
        Else
            obligacion = rec("folio") + 1
        End If
        rec.Close
        var_sql = "INSERT INTO fnza_contrato(folio,rut_apo,agno) VALUES(" & obligacion & ",'" & Me.run_apo.Text & "'," & Me.agno.Text & ")"
        conn.Execute var_sql
    End If
    If Len(obligacion) = 1 Then texto = "000" & obligacion
    If Len(obligacion) = 2 Then texto = "00" & obligacion
    If Len(obligacion) = 3 Then texto = "0" & obligacion
    If Len(obligacion) = 4 Then texto = "" & obligacion
    obligacion_texto = "N° DE OBLIGACIÓN:  " & texto & " - " & Me.agno.Text
    obligacion_numero = texto & " - " & Me.agno.Text & "."
        
    Form_main.CrystalReport.WindowState = crptMaximized
    Form_main.CrystalReport.WindowTitle = "Contrato de Pago"
    Form_main.CrystalReport.Formulas(1) = "col = '" & Establo & "'"
    If Me.agno.Text > 2021 Then Form_main.CrystalReport.Formulas(2) = "obligacion = '" & obligacion_texto & "'"
    If Me.agno.Text > 2022 Then
        EnPalabras.Numero = monto_cuota_incorporacion
        If monto_cuota_incorporacion = 0 Then
            incor = "0"
        Else
            incor = "$" & FormatNumber(monto_cuota_incorporacion, 0) & ".-(" & LCase(EnPalabras.ALetra) & ")."
        End If
        Form_main.CrystalReport.Formulas(3) = "cuota_incorporacion = '" & incor & "'"
        
        EnPalabras.Numero = monto_matricula
        Form_main.CrystalReport.Formulas(4) = "matricula = '" & "$" & FormatNumber(monto_matricula, 0) & ".-(" & LCase(EnPalabras.ALetra) & ")." & "'"
        
        EnPalabras.Numero = monto_total_uf
        Dim valor_decimales As Double
        valor_decimales = monto_total_uf - Int(monto_total_uf)
        valor_decimales = Round(valor_decimales, 2)
        largo_valor = Len(CStr(valor_decimales))
        If largo_valor = 3 Then decimales = valor_decimales * 10
        If largo_valor = 4 Then decimales = valor_decimales * 100
        If largo_valor = 5 Then decimales = valor_decimales * 1000
        If largo_valor = 6 Then decimales = valor_decimales * 10000
        
        If monto_total_uf <> 0 Then
            texto_cuota = Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6) ' & " coma "
        End If
        If decimales <> "" Then
            EnPalabras.Numero = decimales
            texto_cuota = texto_cuota & " coma " & IIf(largo_valor = 4 And Mid(valor_decimales, 3, 1) = 0, "cero ", "") & Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6)
        End If
        texto_cuota = "UF " & monto_total_uf & ".-(" & LCase(texto_cuota) & " Unidades de Fomento),"
                    
        'Form_main.CrystalReport.Formulas(5) = "mensualidades = '" & "UF " & monto_total_uf & ".-(" & LCase(Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6)) & " Unidades de Fomento)," & "'"
        Form_main.CrystalReport.Formulas(5) = "mensualidades = '" & texto_cuota & "'"
        If Me.agno.Text = 2026 Then
            ' total_mensu = "3.- Colegiatura anual año 2023: " & texto_cuota & " las cuales se podrán pagar de la siguiente manera:"
            total_mensu = "3.- Colegiatura anual año 2026: " & texto_cuota & " las cuales se podrán pagar de la siguiente manera:"
        Else
            total_mensu = "3.- Colegiatura anual año 2027: " & texto_cuota & " las cuales se podrán pagar de la siguiente manera:"
        End If
        Form_main.CrystalReport.Formulas(6) = "cantidad_cuotas = '" & cuotas & "'"
        
        'Dim valor_decimales As Double
        valor_decimales = valor - Int(valor)
        valor_decimales = Round(valor_decimales, 2)
        largo_valor = Len(CStr(valor_decimales))
        If largo_valor = 3 Then decimales = valor_decimales * 10
        If largo_valor = 4 Then decimales = valor_decimales * 100
        If largo_valor = 5 Then decimales = valor_decimales * 1000
        If largo_valor = 6 Then decimales = valor_decimales * 10000
        EnPalabras.Numero = valor
        
        If valor <> 0 Then
            texto_cuota = Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6) ' & " coma "
        End If
        If decimales <> "" Then
            EnPalabras.Numero = decimales
            texto_cuota = texto_cuota & " coma " & IIf(largo_valor = 4 And Mid(valor_decimales, 3, 1) = 0, "cero ", "") & Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6)
        End If
        texto_cuota = "UF " & valor & ".-(" & LCase(texto_cuota) & " Unidades de Fomento),"
      
        
        Form_main.CrystalReport.Formulas(7) = "valor_cuota = '" & texto_cuota & "'"
        If Me.agno.Text = 2023 Then
''            linea_2 = "A.- Se podrá pagar a través de " & cuotas & " cuotas mensuales, iguales y sucesivas por el monto de " & texto_cuota _
''                & " con vencimiento los días 10 de cada mes, pagaderas comenzando la primera cuota el mes de " & LCase(DevuelveCampo("meses", "", "mes", "num_mes = '" & Desde & "'", conn)) & " de 2023"
            linea_2 = "A.- Se podrá pagar a través de " & cuotas & " cuotas mensuales, iguales y sucesivas por el monto de " & texto_cuota _
                & " con vencimiento los días 10 de cada mes, pagaderas comenzando la primera cuota el mes de " & LCase(DevuelveCampo("meses", "", "mes", "num_mes = '" & Desde & "'", conn)) ''' & " de 2024"
        Else
            linea_2 = "A.- Se podrá pagar a través de " & cuotas & " cuotas mensuales, iguales y sucesivas por el monto de " & texto_cuota _
                & " con vencimiento los días 10 de cada mes, pagaderas comenzando la primera cuota el mes de " & LCase(DevuelveCampo("meses", "", "mes", "num_mes = '" & Desde & "'", conn)) ''' & " de 2024"
        End If
           
        
        'Joralu = DevuelveCampo("meses", "", "mes", "num_mes = '" & desde & "'", conn)
        Form_main.CrystalReport.Formulas(8) = "desde = '" & LCase(DevuelveCampo("meses", "", "mes", "num_mes = '" & Desde & "'", conn)) & "'"
        Form_main.CrystalReport.Formulas(9) = "hasta = '" & LCase(DevuelveCampo("meses", "", "mes", "num_mes = '" & Hasta & "'", conn)) & "'"
        Form_main.CrystalReport.Formulas(10) = "fp = '" & tpago.Text & "'"
        Form_main.CrystalReport.Formulas(11) = "linea_1 = '" & total_mensu & "'"
        Form_main.CrystalReport.Formulas(12) = "linea_2 = '" & linea_2 & "'"
    End If
    Form_main.CrystalReport.ReplaceSelectionFormula ("")
    Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_contratocab.ci_apo}='" & Me.run_apo.Text & "' and {tmp_contratocab.agno}='" & Me.agno.Text & "'")
    Form_main.CrystalReport.Destination = crptToWindow
    Form_main.CrystalReport.Action = 1
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
  
    'otro = MsgBox("¿ Desea imprimir el FORMULARIO DE ACEPTACION DE HONORARIOS DE COBRANZA ?", vbYesNo, "Formulario de Honorarios")
    
    If colegios <> "HUE001" Then Exit Sub
    If Me.agno.Text < 2022 Then Exit Sub
    
    'creo pagare UF
        'Alumnos
    var_sql = "SELECT m.ci_alu,CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) AS alumno,forma_pago" _
            & " FROM apo_x_alu axa,matriculas m,postulantes p" _
            & " Where axa.ci_alu = m.ci_alu And axa.agno = m.anno_mat And axa.ci_alu = p.ci_alu" _
            & " AND axa.agno = " & Me.agno.Text & " AND axa.rut_apo = '" & Me.run_apo.Text & "'"
    rec.Open var_sql, conn
    alumnos = ""
    cont = 0
    If rec.RecordCount > 0 Then
        While Not rec.EOF
            cont = cont + 1
            If cont = 1 Then
                alumnos = alumnos & rec("alumno")
            Else
                alumnos = alumnos & ", " & rec("alumno")
            End If
            rec.MoveNext
            'forma_pago = "PAT"   'objRS("forma_pago")
        Wend
    End If
    rec.Close
    
    var_sql = "SELECT concat(noms_apo,' ',ape1_apo,' ',ape2_apo) as apoderado,rut_apo,'Chileno' AS nacionalidad,dir_apo,comuna,cel_apo" _
            & " FROM apoderados a,codigos__geograficos cg" _
            & " WHERE a.comuna_apo = cg.cod_geo AND rut_apo = '" & Me.run_apo.Text & "'"
    rec.Open var_sql, conn
    If rec.RecordCount > 0 Then
        apoderado = rec("apoderado")
        rut = rec("rut_apo")
        nacionalidad = rec("nacionalidad")
        direccion = rec("dir_apo")
        Comuna = rec("comuna")
        Telefono = rec("cel_apo")
    End If
    rec.Close
    
    var_sql = "SELECT DAY(NOW()) AS dia,MONTH(NOW()) as mes,YEAR(NOW()) AS agno"
    rec.Open var_sql, conn
    If rec.RecordCount > 0 Then
        dia = rec("dia")
        mes = rec("mes")
        agno1 = rec("agno")
    End If
    rec.Close
    
    var_sql = "SELECT mes FROM meses WHERE num_mes = " & mes
    rec.Open var_sql, conn
    If rec.RecordCount > 0 Then
        mes_palabra = rec("mes")
        
    End If
    rec.Close
        
    var_sql = "delete from temp_pagare_UF"
    conn.Execute var_sql
        
    var_sql = "insert into `temp_pagare_UF` (`rut`, `apoderado`, `alumnos`, `fecha`, `dia`, `mes`," _
            & "`mes_palabra`, `agno`, `direccion`, `comuna`, `ciudad`, `celular`)" _
            & " values('" & rut & "','" & apoderado & "','" & alumnos & "',now(),'" & dia & "','" & mes _
            & "','" & mes_palabra & "','" & agno1 & "','" & direccion & "','" & Comuna & "','SANTIAGO','" & Telefono & "');"
        
    conn.Execute var_sql
    'MsgBox "oka"
    

        var_sql = "drop table if exists temp_convenios;"
        conn.Execute var_sql
        
        var_sql = "create table temp_convenios type = MyISAM" _
                & " select fech_venc,sum(monto_tot_cuota) as totalagno" _
                & " from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
                & " where axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and confirmado = 'SI' and axa.agno = " & Me.agno.Text _
                & " and rut_apo = '" & Me.run_apo.Text & "' and m.ci_alu = fdm.run_alu and m.anno_mat = fdm.agno and concepto <> 'ct00'" _
                & " group by fdm.mes" _
                & " order by fdm.mes"
        conn.Execute var_sql
    
    ''''''' Pagare
        
    Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\pagare_hue_uf.rpt"
    Form_main.CrystalReport.WindowState = crptMaximized
    Form_main.CrystalReport.WindowTitle = "Pagare"
    'Form_main.CrystalReport.Formulas(1) = "col = '" & Establo & "'"
    If Me.agno.Text > 2021 Then Form_main.CrystalReport.Formulas(2) = "obligacion = '" & obligacion_texto & "'"
    Form_main.CrystalReport.ReplaceSelectionFormula ("")
    'Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_contratocab.ci_apo}='" & Me.run_apo.Text & "' and {tmp_contratocab.agno}='" & Me.agno.Text & "'")
    Form_main.CrystalReport.Destination = crptToWindow
    Form_main.CrystalReport.Action = 1
    Call LimpiaFormulas(Form_main.CrystalReport, 30)

    If Me.agno.Text >= 2023 Then
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\mandato_especial_huechuraba_2023.rpt"
    Else
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\mandato_especial_huechuraba.rpt"
    End If
    Form_main.CrystalReport.WindowState = crptMaximized
    Form_main.CrystalReport.WindowTitle = "Mandato Especial"
    'Form_main.CrystalReport.Formulas(1) = "col = '" & Establo & "'"
    If Me.agno.Text > 2021 Then Form_main.CrystalReport.Formulas(2) = "obligacion = '" & obligacion_texto & "'"
    If Me.agno.Text > 2021 Then Form_main.CrystalReport.Formulas(3) = "obligacion_numero = '" & obligacion_numero & "'"
    If Me.agno.Text > 2021 Then Form_main.CrystalReport.Formulas(4) = "nro_obligacion = '" & Mid(obligacion_numero, 1, (Len(obligacion_numero) - 1)) & "'"
    Form_main.CrystalReport.ReplaceSelectionFormula ("")
    'Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_contratocab.ci_apo}='" & Me.run_apo.Text & "' and {tmp_contratocab.agno}='" & Me.agno.Text & "'")
    Form_main.CrystalReport.Destination = crptToWindow
    Form_main.CrystalReport.Action = 1
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
    
    If Me.agno.Text = 2027 Then
        'Exit Sub
        'anexo_huechuraba_2023.rpt
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\anexo_huechuraba_2027.rpt"
    Else
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\anexo_huechuraba_2026.rpt"
        'Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\anexo_huechuraba.rpt"
    End If
    Form_main.CrystalReport.WindowState = crptMaximized
    Form_main.CrystalReport.WindowTitle = "Anexo Contrato"
    'Form_main.CrystalReport.Formulas(1) = "col = '" & Establo & "'"
    If Me.agno.Text > 2021 Then Form_main.CrystalReport.Formulas(2) = "obligacion = '" & obligacion_texto & "'"
    'If Me.agno.Text > 2021 Then Form_main.CrystalReport.Formulas(3) = "obligacion_numero = '" & obligacion_numero & "'"
    'If Me.agno.Text > 2021 Then Form_main.CrystalReport.Formulas(4) = "nro_obligacion = '" & Mid(obligacion_numero, 1, (Len(obligacion_numero) - 1)) & "'"
    Form_main.CrystalReport.ReplaceSelectionFormula ("")
    'Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_contratocab.ci_apo}='" & Me.run_apo.Text & "' and {tmp_contratocab.agno}='" & Me.agno.Text & "'")
    Form_main.CrystalReport.Destination = crptToWindow
    Form_main.CrystalReport.Action = 1
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
        
    '        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\tabladepagoscheque.rpt"
'        Form_main.CrystalReport.WindowState = crptMaximized
'        Form_main.CrystalReport.WindowTitle = "Tabla de Cheques"
'        Form_main.CrystalReport.ReplaceSelectionFormula ("")
'        'Form_main.CrystalReport.ReplaceSelectionFormula ("{fnza_mensualidad.run_apo}='" & Me.run_apo.Text & "' and {fnza_mensualidad.agno}=" & Me.agno.Text)
'        Form_main.CrystalReport.Destination = crptToWindow
'        Form_main.CrystalReport.Action = 1
'        Call LimpiaFormulas(Form_main.CrystalReport, 30)
         
'    If otro = 6 Then
'        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratohonorarios.rpt"
'        Form_main.CrystalReport.WindowState = crptMaximized
'        Form_main.CrystalReport.WindowTitle = "Contrato de Pago Honorarios"
'        Form_main.CrystalReport.Formulas(2) = "fecha = '" & Format(Date, "dd \de MMMM \de YYYY") & "'"
'        Form_main.CrystalReport.ReplaceSelectionFormula ("")
'        Form_main.CrystalReport.ReplaceSelectionFormula ("{apoderados.rut_apo}='" & Me.run_apo.Text & "'")
'        Form_main.CrystalReport.Destination = crptToWindow
'        Form_main.CrystalReport.Action = 1
'        Call LimpiaFormulas(Form_main.CrystalReport, 30)
'    End If
  
End Sub

Private Sub Command2_Click()

    Dim otro As Integer
    Dim suma As Long
    Dim quesacar As ADODB.Recordset
    Dim queponer As ADODB.Recordset
    Dim lineasec As ADODB.Recordset
    Dim grabar As ADODB.Command
    Dim cuerpo As ADODB.Command
    Dim Cabecera As ADODB.Command
    Primera = True
    Dim Tablas, datos, ensegrado, datoreal As String
    Dim creandosql, labelc, Establo As String
    
    If Me.opm = "S" Then
    
        creandosql = "Replace into cont_datos set ci_apo='" & Me.run_apo.Text & "',opcion_pago='" & Me.tpago.Text & "',"
    
        Set grabar = New ADODB.Command
        grabar.CommandType = adCmdText
        grabar.ActiveConnection = conn
        grabar.CommandText = "delete from tmp_contratocab where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
        grabar.Execute
    
        Set grabar = New ADODB.Command
        grabar.CommandType = adCmdText
        grabar.ActiveConnection = conn
        grabar.CommandText = "delete from tmp_contrato where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
        grabar.Execute
    
        Set quesacar = New ADODB.Recordset
        quesacar.Open "select * from `cont_camposlabel` order by tabla,label,descripcion", conn
    
        Set lineasec = New ADODB.Recordset
        lineasec.Open "Select orden from cont_formato_linea where agno=" & Me.agno.Text, conn  ' Por mientras"
     
        While Not quesacar.EOF
        
        If quesacar!tabla = "apoderados" Then
            
            datos = "Select " & Trim(quesacar!campo) & " from " & quesacar!tabla & " where " & quesacar!Where
            datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
            datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)
            
            Set queponer = New ADODB.Recordset
            queponer.Open datos, conn
            
            If quesacar!Label = "[NOM_APODERADO]" Then
                datoreal = queponer!nom_apoderado
            ElseIf quesacar!Label = "[CI_APO]" Then
                datoreal = queponer!ci_apo
            End If
            
        ElseIf quesacar!tabla = "buscado" Then
        
            If Trim(quesacar!Where) <> "" Then
                datos = quesacar!campo & " where " & quesacar!Where
            Else
                datos = quesacar!campo
            End If
            
            datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
            datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)
            
                
            Set queponer = New ADODB.Recordset
            queponer.Open datos, conn
            
            If quesacar!Label = "[DIRCOM_APO]" Then
                datoreal = queponer!dircom_apo
            ElseIf quesacar!Label = "[NOM_COL]" Then
                datoreal = queponer!NOM_COL
                Establo = datoreal
            End If
            
        ElseIf quesacar!tabla = "establecimientos" Then
        
            If Trim(quesacar!Where) <> "" Then
                datos = "Select " & Trim(quesacar!campo) & " from " & quesacar!tabla & " where " & quesacar!Where
            Else
                datos = "Select " & Trim(quesacar!campo) & " from " & quesacar!tabla
            End If
                
            datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
            datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)

            Set queponer = New ADODB.Recordset
            queponer.Open datos, conn
            
            If quesacar!Label = "[DIRCOM_COL]" Then
                datoreal = queponer!dircom_col
            End If
       ElseIf quesacar!tabla = "generado" Then
             datos = "Select " & Trim(quesacar!campo)
             datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
             datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)
             
             Set queponer = New ADODB.Recordset
             queponer.Open datos, conn
             
            If quesacar!Label = "[FEC_ACTUAL]" Then
                datoreal = Format(queponer!fec_actual, "dd \de Mmmm \de yyyy")
                creandosql = creandosql & "fecha_contrato='" & Format(queponer!fec_actual, "yyyy-mm-dd") & "',"
            ElseIf quesacar!Label = "[VALORCUOTA]" Then
                datoreal = Format(queponer!ValorCuota, "###,###,###")
                creandosql = creandosql & "valor_cuota=" & queponer!ValorCuota & ","
            End If
       ElseIf quesacar!tabla = "fnza_mensualidad" Then
            datos = "Select " & quesacar!campo & " from " & quesacar!tabla & " where " & quesacar!Where
            datos = Replace(datos, "{RUT_APO}", Me.run_apo.Text, 1)
            datos = Replace(datos, "{AGNO}", Me.agno.Text, 1)
            'datos = Replace(datos, "{CODCOL}", Me.colegio.Text, 1)
            suma = 0
            Set queponer = New ADODB.Recordset
            queponer.Open datos, conn
            
            If quesacar!Label = "[TOTALAGNO]" Then
                While Not queponer.EOF
                    suma = suma + queponer!totalagno
                    queponer.MoveNext
                Wend
                datoreal = Format(suma, "###,###,###")
                creandosql = creandosql & "total_cuotas=" & Trim(str(suma)) & ","
            ElseIf quesacar!Label = "[TOTALCUOTAS]" Then
                datoreal = Format(queponer!totalcuotas, "###,###,###")
                creandosql = creandosql & "num_cuotas=" & queponer!totalcuotas & ","
            End If

       ElseIf quesacar!tabla = "traspasadoprg" Then
            If quesacar!Label = "[OPCIONPAGO]" Then
                datoreal = Me.tpago.Text
            End If
       End If
       If datoreal = "" Then
            datoreal = "0"
        End If
       Set grabar = New ADODB.Command
       grabar.ActiveConnection = conn
       grabar.CommandType = adCmdText
       
       Set cuerpo = New ADODB.Command
       cuerpo.ActiveConnection = conn
       cuerpo.CommandType = adCmdText
       
       
        If Primera Then
            grabar.CommandText = "INSERT INTO tmp_contratocab (txt,ci_apo,encargado,agno) Select replace(cont_formato_cab.txt,'" & quesacar!Label & "','" & datoreal & "') as texto, '" & Me.run_apo.Text & "' as rut_apo,  '" & Nom_com_usu_ses & "' as nomusu,'" & Me.agno.Text & "' as agno  from cont_formato_cab where agno=" & Me.agno.Text
          While Not lineasec.EOF
             If Trim(tpago) = "C" And Trim(lineasec!Orden) = "3" Then
                 cuerpo.CommandText = "INSERT INTO tmp_contrato (ci_apo,txt,orden,agno) select '" & Me.run_apo.Text & "' as ci_apo, replace(concat(Left(cont_formato_linea.txt,155),'segun tabla de pago adjunta.'),'" & quesacar!Label & "','" & datoreal & "') as txt, cont_formato_linea.orden,'" & Me.agno.Text & "' as agno from cont_formato_linea where cont_formato_linea.orden=" & lineasec!Orden
              Else
                cuerpo.CommandText = "INSERT INTO tmp_contrato (ci_apo,txt,orden,agno) select '" & Me.run_apo.Text & "' as ci_apo, replace(cont_formato_linea.txt,'" & quesacar!Label & "','" & datoreal & "') as txt, cont_formato_linea.orden,'" & Me.agno.Text & "' as agno from cont_formato_linea  where cont_formato_linea.orden=" & lineasec!Orden
              End If
             cuerpo.Execute
             lineasec.MoveNext
          Wend
          Primera = False
        Else
           grabar.CommandText = "update tmp_contratocab set txt=replace(txt,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
           
           While Not lineasec.EOF
              cuerpo.CommandText = "update tmp_contrato set txt=replace(txt,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "' and orden=" & lineasec!Orden
              cuerpo.Execute
              lineasec.MoveNext
           Wend
        End If
        lineasec.MoveFirst
        grabar.Execute
'       queponer.Close
        quesacar.MoveNext
    Wend
    
    quesacar.Close
    
    '*****************************************************Alumnos del Apoderado
    
    Set quesacar = New ADODB.Recordset
    quesacar.Open "Select rpad(concat(p.nom_alu,' ',p.ape_pat_alu,' ',p.ape_mat_alu),60,' ') as nom_alu, m.cod_ens as ense, m.num_gra as grado,a.rut_apo as rut_apo from postulantes as p, apoderados as a, matriculas as m, apo_x_alu as t where a.rut_apo='" & Me.run_apo.Text & "' AND a.rut_apo=t.rut_apo AND t.agno=m.anno_mat AND t.ci_alu=m.ci_alu AND m.ci_alu=p.ci_alu and m.activo_alu='SI' and date_format(m.fec_retiro,'%d-%m-%Y')='11-11-1111' and m.anno_mat=" & Me.agno.Text & ";", conn
                    
    datos = "" & vbCrLf & vbCrLf
    If quesacar.RecordCount < 1 Then
        Exit Sub
    End If
    
    While Not quesacar.EOF
            If quesacar!ense = "310" Then
                ensegrado = "Medio"
            ElseIf quesacar!ense = "010" Then
                ensegrado = "Parvulo"
            ElseIf quesacar!ense = "110" Then
                ensegrado = "Básico"
            ElseIf quesacar!ense = "996" Then
                ensegrado = "PreKinder"
            End If
        
        
            datos = datos & "Nombre: " & quesacar!Nom_Alu & " Curso " & Me.agno.Text & " : "
    
        
            datos = datos & quesacar!grado & "º  " & ensegrado & vbCrLf
         
    quesacar.MoveNext
    Wend
    
    
    Set grabar = New ADODB.Command
    grabar.ActiveConnection = conn
    grabar.CommandType = adCmdText
    grabar.CommandText = "update tmp_contratocab set txt=concat(txt,'" & datos & "') where ci_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
    grabar.Execute
    quesacar.Close
    
    Set grabar = New ADODB.Command
    grabar.ActiveConnection = conn
    grabar.CommandType = adCmdText
    grabar.CommandText = creandosql & "encargado='" & Nom_com_usu_ses & "'"
    grabar.Execute
    
 
    End If
     
    
    
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
    Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal.rpt"
    Form_main.CrystalReport.WindowState = crptMaximized
    Form_main.CrystalReport.WindowTitle = "Contrato de Pago"
    Form_main.CrystalReport.Formulas(1) = "col = '" & Establo & "'"
    Form_main.CrystalReport.ReplaceSelectionFormula ("")
    Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_contratocab.ci_apo}='" & Me.run_apo.Text & "' and {tmp_contratocab.agno}='" & Me.agno.Text & "'")
    Form_main.CrystalReport.Destination = crptToPrinter
    Form_main.CrystalReport.Action = 1
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
  
    
    If Trim(Me.tpago.Text) = "C" Then
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\tabladepagoscheque.rpt"
        Form_main.CrystalReport.WindowState = crptMaximized
        Form_main.CrystalReport.WindowTitle = "Tabla de Cheques"
        Form_main.CrystalReport.ReplaceSelectionFormula ("")
        Form_main.CrystalReport.ReplaceSelectionFormula ("{fnza_mensualidad.run_apo}='" & Me.run_apo.Text & "' and {fnza_mensualidad.agno}=" & Me.agno.Text)
        Form_main.CrystalReport.Destination = crptToPrinter
        Form_main.CrystalReport.Action = 1
        Call LimpiaFormulas(Form_main.CrystalReport, 30)
    End If
        

  
End Sub

Public Sub Command4_Click()
    Dim cole As ADODB.Recordset
    Dim Establo As String
    Dim RDB As String
    Set cole = New ADODB.Recordset
    cole.Open "select est.rdb,est.NOM_SOSTENEDOR as NOM_COL from establecimientos as est", conn
    Establo = cole!NOM_COL
    RDB = cole!RDB
    cole.Close
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
    If RDB = "9782-9" Then
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratolincoln.rpt"
    Else
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal.rpt"
    End If
    Form_main.CrystalReport.WindowState = crptMaximized
    Form_main.CrystalReport.WindowTitle = "Contrato de Pago"
    Form_main.CrystalReport.Formulas(1) = "col = '" & Establo & "'"
    Form_main.CrystalReport.ReplaceSelectionFormula ("")
    Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_contratocab.codnumlet}='" & Me.codnumlet.Text & "' and {tmp_contratocab.agno}='" & Me.agno.Text & "'")
    Form_main.CrystalReport.Destination = crptToWindow
    Form_main.CrystalReport.Action = 1
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
  
'    If Trim(Me.tpago.Text) = "C" Then
'        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\tabladepagoscheque.rpt"
'        Form_main.CrystalReport.WindowState = crptMaximized
'        Form_main.CrystalReport.WindowTitle = "Tabla de Cheques"
'        Form_main.CrystalReport.ReplaceSelectionFormula ("")
'        Form_main.CrystalReport.ReplaceSelectionFormula ("{fnza_mensualidad.run_apo}='" & Me.run_apo.Text & "' and {fnza_mensualidad.agno}=" & Me.agno.Text)
'        Form_main.CrystalReport.Destination = crptToWindow
'        Form_main.CrystalReport.Action = 1
'        Call LimpiaFormulas(Form_main.CrystalReport, 30)
'    End If
        
'    Command6_Click
  

End Sub

Public Sub Command3_Click()

    Dim otro, cant As Integer
    Dim suma As Long
    Dim quesacar As ADODB.Recordset
    Dim queponer As ADODB.Recordset
    Dim lineasec As ADODB.Recordset
    Dim grabar As ADODB.Command
    Dim cuerpo As ADODB.Command
    Dim Cabecera As ADODB.Recordset
    Primera = True
    Dim Tablas, datos, ensegrado, datoreal, Detalle As String
    Dim creandosql, labelc, Establo As String
    Dim Ncuotas As Integer




        Set grabar = New ADODB.Command
        grabar.CommandType = adCmdText
        grabar.ActiveConnection = conn
        grabar.CommandText = "delete from tmp_aviso where ci_apo='" & Me.run_apo.Text & "'"
        grabar.Execute
    
        Set quesacar = New ADODB.Recordset
        quesacar.Open "select * from aviso_labels order by tabla,label,descripcion", conn
        
        Set lineasec = New ADODB.Recordset
        'lineasec.Open "select ap", conn
        
        sqlalum = "SELECT concat(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) as nombre, count(p.ci_alu) as cuantos from postulantes as p,apo_x_alu as axa,matriculas as m " & _
                  "WHERE axa.ci_alu=p.ci_alu AND axa.rut_apo='" & Me.run_apo.Text & "' AND m.ci_alu=p.ci_alu and " & _
                  "m.activo_alu='SI' and m.fec_retiro='1111-11-11' AND m.anno_mat=axa.agno AND m.anno_mat='" & Me.agno.Text & "' group by p.ci_alu "
        
        'sqlcini = "select monto_mens from fnza_mensualidad where run_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "' and mes = 2"
         sqlcrest = "select sum(monto_mens) as monto_mens from fnza_mensualidad where run_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
        
        Set lineasec = New ADODB.Recordset
        lineasec.Open sqlalum, conn
        
        If lineasec.RecordCount > 0 Then
            Detalle = "      Cantidad de Alumnos:  " & Trim(str(lineasec.RecordCount)) & ",  (informado a la fecha)" & vbCrLf & vbCrLf
        End If
        cant = 0
        While Not lineasec.EOF
            cant = cant + 1
            Detalle = Detalle & "      Nombre Alumno " & Trim(str(cant)) & ":  " & lineasec!nombre & vbCrLf
            lineasec.MoveNext
        Wend
        lineasec.Close
        
        
        'lineasec.Open sqlcini, conn
        'If lineasec.RecordCount > 0 Then
        '    detalle = detalle & vbCrLf & "       Valor Contrato: $" & Format(lineasec!Monto_Mens, "###,###,###")
       ' End If
        'lineasec.Close
        
        lineasec.Open sqlcrest, conn
        pasando = Format(lineasec!Monto_Mens, "###,###,###")
        
          
        Set Cabecera = New ADODB.Recordset
        Cabecera.Open "Select count(*) as numcuotas from fnza_mensualidad where agno=" & Trim(Me.agno.Text) & " and run_apo='" & Me.run_apo.Text & "' group by run_apo ", conn
        If Cabecera.RecordCount < 1 Then
            Ncuotas = 0
        Else
            Ncuotas = Cabecera!numcuotas
        End If
        
        Cabecera.Close
        
        If pasando = "" Then
            Detalle = Detalle & vbCrLf & "      Valor Contrato: $0, A pagar hasta en " & Trim(str(Ncuotas)) & " cuotas." & vbCrLf
        Else
            'Detalle = Detalle & vbCrLf & "      Valor Contrato: $" & Format(lineasec!Monto_Mens, "###,###,###") & ", A pagar hasta en " & Trim(str(Ncuotas)) & " cuotas de $" & Format((lineasec!Monto_Mens / Ncuotas), "###,###,###") & vbCrLf
            Detalle = Detalle & vbCrLf & "      Valor Contrato: $" & Format(lineasec!Monto_Mens, "###,###,###") & ", A pagar hasta en " & Trim(str(Ncuotas)) & " cuotas." & vbCrLf
        End If
        
        'While Not lineasec.EOF
        '   detalle = detalle & "                   " & lineasec!Meses & "      $" & Format(lineasec!monto_mens, "###,###,###") & vbCrLf
        '   lineasec.MoveNext
        'Wend
        
        Set cuerpo = New ADODB.Command
        cuerpo.CommandType = adCmdText
        cuerpo.ActiveConnection = conn
        cuerpo.CommandText = "Insert into tmp_aviso set texto='" & Detalle & "',orden=2,tipo='DETA',ci_apo='" & Me.run_apo.Text & "',codnumlet='" & Me.codnumlet.Text & "'"
        cuerpo.Execute
        
        While Not quesacar.EOF
        
          If quesacar!tabla = "apoderados" Then
               datos = "Select " & quesacar!campo & " from " & quesacar!tabla & " where rut_apo='" & Me.run_apo.Text & "'"
            
                Set queponer = New ADODB.Recordset
                queponer.Open datos, conn
            
                If quesacar!Label = "[NOMAPO]" Then
                    datoreal = queponer!Noms_Apo
                ElseIf quesacar!Label = "[PATAPO]" Then
                    datoreal = queponer!ape1_apo
                ElseIf quesacar!Label = "[MATAPO]" Then
                    datoreal = queponer!ape2_apo
                ElseIf quesacar!Label = "[DIRECCION]" Then
                    datoreal = queponer!dir_apo
                ElseIf quesacar!Label = "[RUTAPO]" Then
                    datoreal = Me.run_apo.Text
                End If
                
         ElseIf quesacar!tabla = "establecimientos" Then
                datos = "Select " & quesacar!campo & " from " & quesacar!tabla
                
                Set queponer = New ADODB.Recordset
                queponer.Open datos, conn
                
                If quesacar!Label = "[COLEGIO]" Then
                    datoreal = queponer!nom_est
                End If
                
        ElseIf quesacar!tabla = "fechagen" Then
                datos = "Select " & quesacar!campo
                
                Set queponer = New ADODB.Recordset
                queponer.Open datos, conn
                
                datoreal = queponer!fec
        End If
        ' MsgBox datoreal
        If datoreal = "" Then
            datoreal = "0"
        End If
       Set grabar = New ADODB.Command
       grabar.ActiveConnection = conn
       grabar.CommandType = adCmdText
       
       Set cuerpo = New ADODB.Command
       cuerpo.ActiveConnection = conn
       cuerpo.CommandType = adCmdText
       
       
        If Primera Then
            grabar.CommandText = "INSERT INTO tmp_aviso (texto,ci_apo,tipo,orden,codnumlet) Select replace(texto,'" & quesacar!Label & "','" & datoreal & "') as texto, '" & Me.run_apo.Text & "' as ci_apo,tipo,orden,'" & Me.codnumlet.Text & "' as codnumlet from aviso_formato where orden=0"
            grabar.Execute
            grabar.CommandText = "INSERT INTO tmp_aviso (texto,ci_apo,tipo,orden,codnumlet) Select replace(texto,'" & quesacar!Label & "','" & datoreal & "') as texto, '" & Me.run_apo.Text & "' as ci_apo,tipo,orden,'" & Me.codnumlet.Text & "' as codnumlet from aviso_formato where orden=1"
            grabar.Execute
            grabar.CommandText = "INSERT INTO tmp_aviso (texto,ci_apo,tipo,orden,codnumlet) Select replace(texto,'" & quesacar!Label & "','" & datoreal & "') as texto, '" & Me.run_apo.Text & "' as ci_apo,tipo,orden,'" & Me.codnumlet.Text & "' as codnumlet from aviso_formato where orden=3"
            grabar.Execute
            Primera = False
        Else
           grabar.CommandText = "update tmp_aviso set texto=replace(texto,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and tipo='ENCAB'"
           grabar.Execute
           grabar.CommandText = "update tmp_aviso set texto=replace(texto,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and tipo='TEXTO'"
           grabar.Execute
           grabar.CommandText = "update tmp_aviso set texto=replace(texto,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and tipo='PIE'"
           grabar.Execute
        End If
       
       quesacar.MoveNext
       queponer.Close
    Wend
    quesacar.Close
         
End Sub


Public Sub Command5_Click()
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
    Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\cartaaviso.rpt"
    Form_main.CrystalReport.WindowState = crptMaximized
    Form_main.CrystalReport.WindowTitle = "Carta Aviso"
    Form_main.CrystalReport.ReplaceSelectionFormula ("")
    Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_aviso.codnumlet}='" & Me.codnumlet.Text & "'")
    Form_main.CrystalReport.Destination = crptToWindow
    Form_main.CrystalReport.Action = 1
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
End Sub

Private Sub Command6_Click()
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratohonorarios.rpt"
        Form_main.CrystalReport.WindowState = crptMaximized
        Form_main.CrystalReport.WindowTitle = "Contrato de Pago Honorarios"
        Form_main.CrystalReport.Formulas(2) = "fecha = '" & Format(Date, "dd \de MMMM \de YYYY") & "'"
        Form_main.CrystalReport.ReplaceSelectionFormula ("")
        Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_contratocab.codnumlet}='" & Me.codnumlet.Text & "' and {tmp_contratocab.agno}='" & Me.agno.Text & "'")
        Form_main.CrystalReport.Destination = crptToWindow
        Form_main.CrystalReport.Action = 1
        Call LimpiaFormulas(Form_main.CrystalReport, 30)
End Sub

Public Sub Command7_Click()
    Dim otro, cant As Integer
    Dim suma As Long
    Dim quesacar As ADODB.Recordset
    Dim queponer As ADODB.Recordset
    Dim lineasec As ADODB.Recordset
    Dim grabar As ADODB.Command
    Dim cuerpo As ADODB.Command
    Dim Cabecera As ADODB.Recordset
    Primera = True
    Dim Tablas, datos, ensegrado, datoreal, Detalle As String
    Dim creandosql, labelc, Establo As String
    Dim Ncuotas As Integer




        Set grabar = New ADODB.Command
        grabar.CommandType = adCmdText
        grabar.ActiveConnection = conn
        grabar.CommandText = "delete from tmp_aviso2 where ci_apo='" & Me.run_apo.Text & "'"
        grabar.Execute
    
        Set quesacar = New ADODB.Recordset
        quesacar.Open "select * from aviso_labels order by tabla,label,descripcion", conn
        
        Set lineasec = New ADODB.Recordset
        'lineasec.Open "select ap", conn
        
        sqlalum = "SELECT concat(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) as nombre, count(p.ci_alu) as cuantos FROM postulantes as p,apo_x_alu as axa, matriculas as m " & _
                  "WHERE axa.ci_alu=p.ci_alu AND axa.rut_apo='" & Me.run_apo.Text & "' AND m.ci_alu=p.ci_alu and " & _
                  "m.activo_alu='SI' AND m.fec_retiro='1111-11-11' AND m.anno_mat='" & Me.agno.Text & "' AND axa.agno=m.anno_mat GROUP BY p.ci_alu;"
        
        'sqlcini = "select monto_mens from fnza_mensualidad where run_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "' and mes = 2"
         sqlcrest = "select sum(monto_mens) as monto_mens from fnza_mensualidad where run_apo='" & Me.run_apo.Text & "' and agno='" & Me.agno.Text & "'"
        
        Set lineasec = New ADODB.Recordset
        lineasec.Open sqlalum, conn
        
        If lineasec.RecordCount > 0 Then
          '  Detalle = "      Cantidad de Alumnos:  " & Trim(str(lineasec.RecordCount)) & ",  (informado a la fecha)" & vbCrLf & vbCrLf
        End If
        cant = 0
        While Not lineasec.EOF
            cant = cant + 1
            Detalle = Detalle & "      Nombre Alumno " & Trim(str(cant)) & ":  " & lineasec!nombre & vbCrLf
            lineasec.MoveNext
        Wend
        lineasec.Close
        
        
        'lineasec.Open sqlcini, conn
        'If lineasec.RecordCount > 0 Then
        '    detalle = detalle & vbCrLf & "       Valor Contrato: $" & Format(lineasec!Monto_Mens, "###,###,###")
       ' End If
        'lineasec.Close
        
        lineasec.Open sqlcrest, conn
        pasando = Format(lineasec!Monto_Mens, "###,###,###")
        
          
        Set Cabecera = New ADODB.Recordset
        Cabecera.Open "Select count(*) as numcuotas from fnza_mensualidad where agno=" & Trim(Me.agno.Text) & " and run_apo='" & Me.run_apo.Text & "' group by run_apo,colegio ", conn
        If Cabecera.RecordCount < 1 Then
            Ncuotas = 0
        Else
            Ncuotas = Cabecera!numcuotas
        End If
        
        Cabecera.Close
        
        If pasando = "" Then
           ' Detalle = Detalle & vbCrLf & "      Valor Contrato: $0, A pagar hasta en " & Trim(str(Ncuotas)) & " cuotas de $0" & vbCrLf
        Else
            'Detalle = Detalle & vbCrLf & "      Valor Contrato: $" & Format(lineasec!Monto_Mens, "###,###,###") & ", A pagar hasta en " & Trim(str(Ncuotas)) & " cuotas de $" & Format((lineasec!Monto_Mens / Ncuotas), "###,###,###") & vbCrLf
        End If
        
       ' While Not lineasec.EOF
        '   detalle = detalle & "                   " & lineasec!Meses & "      $" & Format(lineasec!monto_mens, "###,###,###") & vbCrLf
         '  lineasec.MoveNext
        'Wend
        
        Set cuerpo = New ADODB.Command
        cuerpo.CommandType = adCmdText
        cuerpo.ActiveConnection = conn
        cuerpo.CommandText = "Insert into tmp_aviso2 set texto='" & Detalle & "',orden=2,tipo='DETA',ci_apo='" & Me.run_apo.Text & "',codnumlet='" & Me.codnumlet.Text & "'"
        cuerpo.Execute
        
        While Not quesacar.EOF
        
          If quesacar!tabla = "apoderados" Then
               datos = "Select " & quesacar!campo & " from " & quesacar!tabla & " where rut_apo='" & Me.run_apo.Text & "'"
            
                Set queponer = New ADODB.Recordset
                queponer.Open datos, conn
            
                If quesacar!Label = "[NOMAPO]" Then
                    datoreal = queponer!Noms_Apo
                ElseIf quesacar!Label = "[PATAPO]" Then
                    datoreal = queponer!ape1_apo
                ElseIf quesacar!Label = "[MATAPO]" Then
                    datoreal = queponer!ape2_apo
                ElseIf quesacar!Label = "[DIRECCION]" Then
                    datoreal = queponer!dir_apo
                ElseIf quesacar!Label = "[RUTAPO]" Then
                    datoreal = Me.run_apo.Text
                End If
                
         ElseIf quesacar!tabla = "establecimientos" Then
                datos = "Select " & quesacar!campo & " from " & quesacar!tabla
                
                Set queponer = New ADODB.Recordset
                queponer.Open datos, conn
                
                If quesacar!Label = "[COLEGIO]" Then
                    datoreal = queponer!nom_est
                End If
                
        ElseIf quesacar!tabla = "fechagen" Then
                datos = "Select " & quesacar!campo
                
                Set queponer = New ADODB.Recordset
                queponer.Open datos, conn
                
                datoreal = queponer!fec
        End If
        ' MsgBox datoreal
        If datoreal = "" Then
            datoreal = "0"
        End If
       Set grabar = New ADODB.Command
       grabar.ActiveConnection = conn
       grabar.CommandType = adCmdText
       
       Set cuerpo = New ADODB.Command
       cuerpo.ActiveConnection = conn
       cuerpo.CommandType = adCmdText
       
       
        If Primera Then
            grabar.CommandText = "INSERT INTO tmp_aviso2 (texto,ci_apo,tipo,orden,codnumlet) Select replace(texto,'" & quesacar!Label & "','" & datoreal & "') as texto, '" & Me.run_apo.Text & "' as ci_apo,tipo,orden,'" & Me.codnumlet.Text & "' as codnumlet from avisotros_formato where orden=0"
            grabar.Execute
            grabar.CommandText = "INSERT INTO tmp_aviso2 (texto,ci_apo,tipo,orden,codnumlet) Select replace(texto,'" & quesacar!Label & "','" & datoreal & "') as texto, '" & Me.run_apo.Text & "' as ci_apo,tipo,orden,'" & Me.codnumlet.Text & "' as codnumlet from avisotros_formato where orden=1"
            grabar.Execute
            grabar.CommandText = "INSERT INTO tmp_aviso2 (texto,ci_apo,tipo,orden,codnumlet) Select replace(texto,'" & quesacar!Label & "','" & datoreal & "') as texto, '" & Me.run_apo.Text & "' as ci_apo,tipo,orden,'" & Me.codnumlet.Text & "' as codnumlet from avisotros_formato where orden=3"
            grabar.Execute
            Primera = False
        Else
           grabar.CommandText = "update tmp_aviso2 set texto=replace(texto,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and tipo='ENCAB'"
           grabar.Execute
           grabar.CommandText = "update tmp_aviso2 set texto=replace(texto,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and tipo='TEXTO'"
           grabar.Execute
           grabar.CommandText = "update tmp_aviso2 set texto=replace(texto,'" & quesacar!Label & "','" & datoreal & "') where ci_apo='" & Me.run_apo.Text & "' and tipo='PIE'"
           grabar.Execute
        End If
       
       quesacar.MoveNext
       queponer.Close
    Wend
    quesacar.Close
End Sub

Public Sub Command8_Click()
  Call LimpiaFormulas(Form_main.CrystalReport, 30)
    Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\cartaaviso2.rpt"
    Form_main.CrystalReport.WindowState = crptMaximized
    Form_main.CrystalReport.WindowTitle = "Carta Aviso"
    Form_main.CrystalReport.ReplaceSelectionFormula ("")
    Form_main.CrystalReport.ReplaceSelectionFormula ("{tmp_aviso2.codnumlet}='" & Me.codnumlet.Text & "'")
    Form_main.CrystalReport.Destination = crptToWindow
    Form_main.CrystalReport.Action = 1
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
End Sub

