VERSION 5.00
Object = "{00025600-0000-0000-C000-000000000046}#4.6#0"; "crystl32.ocx"
Begin VB.Form Form_ContratoCrea 
   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 Crystal.CrystalReport CrystalReport1 
      Left            =   6240
      Top             =   360
      _ExtentX        =   741
      _ExtentY        =   741
      _Version        =   262150
   End
   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 
      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        =   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 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   285
      Left            =   3960
      TabIndex        =   4
      Top             =   360
      Width           =   495
   End
   Begin VB.TextBox tpago 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   285
      Left            =   3000
      TabIndex        =   3
      Top             =   360
      Width           =   495
   End
   Begin VB.TextBox agno 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      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 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   285
      Left            =   240
      TabIndex        =   0
      Top             =   360
      Width           =   1575
   End
End
Attribute VB_Name = "Form_ContratoCrea"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public Primera As Boolean
Public grabar45 As String

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 var_sql As String
    Dim campo As String
    
    Dim masivo As Boolean
    Dim otro As Integer
    Dim suma As Double
    Dim cuota As Double
    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 = ""
    
    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
    
    'Valor cuota
    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) /" _
            & " (select count(distinct mes) as totalagno from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
            & " where fdm.monto_tot_cuota > 0 and fdm.monto_total > 0 and 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 fec_mat_r is not null 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) /" _
            & " (select count(distinct mes) as totalagno from apo_x_alu axa,matriculas m,fnza_detalle_mensualidad fdm" _
            & " where fdm.monto_tot_cuota > 0 and fdm.monto_total > 0 and axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and fec_mat_r is not null 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
    
    ' Total mensualidades
'    If masivo2 = True Then
'    CAMPO2 = """(select count(distinct mes) 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 totalcuotas"""
'    Else
'    CAMPO2 = """(select count(distinct mes) 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 totalcuotas"""
'    End If
'
'    var_sql = "update cont_camposlabel set campo = " & CAMPO2 & " where label = '[TOTALCUOTAS]'"
'    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
                'If IsNull(queponer!ValorCuota) = True Then
                 '   datoreal = Trim(str("0.00000"))
                'Else
                    datoreal = Format(queponer!ValorCuota / 36865.37, "0.00000")
                'End If
                'datoprobar = datoreal
                'datoreal = Format(queponer!ValorCuota / 36865.37, "###,###,###")
                If IsNull(queponer!ValorCuota) = True Then
                    'creandosql = creandosql & "valor_cuota=" & Trim(str("0.00000")) & ","
                    creandosql = creandosql & "valor_cuota='" & "0.00000" & "',"
                Else
                    creandosql = creandosql & "valor_cuota=" & IIf(IsNull(queponer!ValorCuota), 0, Trim(str(Format(queponer!ValorCuota / 36865.37, "0.00000")))) & ","
                End If
            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 fdm.monto_tot_cuota > 0 and fdm.monto_total > 0 and 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 fdm.monto_tot_cuota > 0 and fdm.monto_total > 0 and axa.agno = m.anno_mat and axa.ci_alu = m.ci_alu and fec_mat_r is not null 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 + IIf(IsNull(queponer!totalagno), 0, queponer!totalagno)
                    queponer.MoveNext
                Wend
                '' Esto agregue
                'suma = (datoprobar * 11)
                datoreal = Format(suma / 36865.37, "00.00000")
                creandosql = creandosql & "total_cuotas=" & Trim(str(suma)) & ","
            ElseIf quesacar!Label = "[TOTALCUOTAS]" Then
                datoreal = Format(queponer!totalcuotas, "###,###,###")
                'datoreal = 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
            grabar45 = "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 & "'"
                
                'Sacar forma de pago de matriculas
                
                If Me.tpago.Text <> "B" Then
'                    If Form_Imprimir_Contrato_Apoderado.Combo1.Text = 2016 Then
'                        texto = " en [TOTALCUOTAS] cuotas iguales de aproximadamente UF [VALORCUOTA] con vencimiento los días 10 de cada mes.\nEl valor en pesos de la anualidad 2016 se definirá una vez conocido el valor de la Unidad de Fomento 2016 y de acuerdo a lo que defina la Ley Nº 20.845, reglamentos y modificaciones, emitidas por el Mineduc.\nInasistencias a clases y/o la interrupción de éstas por motivos ajenos al colegio como tomas, paros, huelgas, desastres de la naturaleza u otros de fuerza mayor, no serán motivo de devolución o rebajas de mensualidades."
'                    Else
                        texto = " en  [TOTALCUOTAS]  cuotas  iguales  de  aproximadamente \nUF [VALORCUOTA], con vencimiento los días 10 de cada mes.\nInasistencias a clases y/o la interrupción de éstas por motivos ajenos al colegio como tomas, paros, huelgas, desastres de la naturaleza u los actos de la autoridad emanados por funcionarios públicos, u otros de fuerza mayor o caso fortuito, no serán motivo alguno de devolución o rebajas de mensualidades.\n" _
                              & "De acuerdo a lo estipulado en la Ley de Inclusión Escolar, el valor definitivo de la anualidad 2024 (el que no será superior a lo informado) será determinado en el mes de enero 2024  de acuerdo  a  lo  que  instruya  el  Ministerio  de  Educación, conforme  a  lo  establecido  en  los  artículos  vigésimo segundo  y  siguientes Transitorios de la Ley N° 20.845 y Reglamento N° 478 del 29/01/2016 y/o cualquier normativa legal que se defina al respecto. El valor de la escolaridad 2024 definida en UF se convertirá en moneda nacional en el  mes de marzo 2024.\n" _
                              & "De producirse alguna diferencia entre los valores pagados y los definitivos que se fijen por instrucciones del Ministerio de Educación, se procederá  a la re liquidación de esta diferencia de las siguientes formas:  Abonando dicho monto a las mensualidades por pagar del año 2024 o bien, en caso de haber pagado al contado o documentado el año con cheques, se procederá a la devolución de dicho dinero en efectivo o mediante cambio uno de los cheques del año 2024, respectivamente, para lo cual el apoderado será citado mediante comunicación.\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n"

                   ' End If
                Else
                    'texto = ".\n" & "Inasistencias a clases y/o la interrupción de éstas por motivos ajenos al colegio como tomas, paros, huelgas, desastres de la naturaleza u otros de fuerza mayor, no serán motivo de devolución o rebajas de mensualidades."
                    texto = ".\n" & "Inasistencias a clases y/o la interrupción de éstas por motivos ajenos al colegio como tomas, paros, huelgas, desastres de la naturaleza u los actos de la autoridad emanados por funcionarios públicos, u otros de fuerza mayor o caso fortuito, no serán motivo alguno de devolución o rebajas de mensualidades.\n" _
                              & "De acuerdo a lo estipulado en la Ley de Inclusión Escolar, el valor definitivo de la anualidad 2024 (el que no será superior a lo informado) será determinado en el mes de enero 2024  de acuerdo  a  lo  que  instruya  el  Ministerio  de  Educación, conforme  a  lo  establecido  en  los  artículos  vigésimo segundo  y  siguientes Transitorios de la Ley N° 20.845 y Reglamento N° 478 del 29/01/2016 y/o cualquier normativa legal que se defina al respecto. El valor de la escolaridad 2024 definida en UF se convertirá en moneda nacional en el  mes de marzo 2024.\n" _
                              & "De producirse alguna diferencia entre los valores pagados y los definitivos que se fijen por instrucciones del Ministerio de Educación, se procederá  a la re liquidación de esta diferencia de las siguientes formas:  Abonando dicho monto a las mensualidades por pagar del año 2024 o bien, en caso de haber pagado al contado o documentado el año con cheques, se procederá a la devolución de dicho dinero en efectivo o mediante cambio uno de los cheques del año 2024, respectivamente, para lo cual el apoderado será citado mediante comunicación.\n\n\n\n\n\n\n\n\n\n\n\n\nn\n\n"
                End If
'                 If Form_Imprimir_Contrato_Apoderado.Combo1.Text = 2016 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,156),'" & texto & "'),'" & 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 & "'"
'                 Else
                    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,201),'" & texto & "'),'" & 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
                 
              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
             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 p.ci_alu,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 p.ci_alu,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 fec_mat_r is not null 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 = "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 & " : "
    
            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
'                accion = "Se ha agregado convenio de jardin al alumno con rut :" & txt_rut.Text
'                Call Inserta_Vitacora_Alumno(txt_rut.Text, accion)
                 Call Inserta_Vitacora_Finanza(quesacar!ci_alu, Me.run_apo.Text, "Contrato Apoderado", "Se ha generado el contrato para el año " & Me.agno.Text, 0, 0)
                
            Else
                datos = datos & quesacar!grado & "º  " & ensegrado & vbCrLf
                Call Inserta_Vitacora_Finanza(quesacar!ci_alu, Me.run_apo.Text, "Contrato Apoderado", "Se ha generado el contrato masivo para el año " & Me.agno.Text, 0, 0)
            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
    
    'Call LimpiaFormulas(Form_main.CrystalReport, 30)
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
    var_sql = "SELECT valor FROM cfg where param = 'CODBC'"
    rec.Open var_sql, conn
    If rec("valor") = "LIN001" Then
        'Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratolincoln.rpt"
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratolincoln.rpt"
    Else
        'Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal.rpt"
        Form_main.CrystalReport.ReportFileName = App.Path & "\rpt\contratofinal.rpt"
    End If
    rec.Close
    
'    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 = crptToWindow
'    Form_main.CrystalReport.Action = 1
'    Call LimpiaFormulas(Form_main.CrystalReport, 30)
'
    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 = crptToWindow
    Form_main.CrystalReport.Action = 1
    ' Call LimpiaFormulas(Form_main.CrystalReport, 30)
    Call LimpiaFormulas(Form_main.CrystalReport, 30)
  
    'otro = MsgBox("¿ Desea imprimir el FORMULARIO DE ACEPTACION DE HONORARIOS DE COBRANZA ?", vbYesNo, "Formulario de Honorarios")

        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 fec_mat_r is not null 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

'        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

