VERSION 5.00
Object = "{00025600-0000-0000-C000-000000000046}#4.6#0"; "crystl32.ocx"
Begin VB.Form Form_contrato_antiguos 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Generar Contrato"
   ClientHeight    =   1260
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   5085
   Icon            =   "Form_contrato_antiguos.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   1260
   ScaleWidth      =   5085
   Begin VB.CommandButton Command2 
      Caption         =   "Salir"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   3720
      TabIndex        =   3
      Top             =   240
      Width           =   1095
   End
   Begin VB.Frame Frame1 
      Caption         =   "Ingrese Rut de Apoderado"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   855
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   3255
      Begin VB.CommandButton Command1 
         Caption         =   "Generar"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   1920
         TabIndex        =   2
         Top             =   240
         Width           =   1095
      End
      Begin VB.TextBox txt_rut 
         Height          =   285
         Left            =   240
         TabIndex        =   1
         Top             =   330
         Width           =   1335
      End
   End
   Begin Crystal.CrystalReport cr_circular 
      Left            =   720
      Top             =   1680
      _ExtentX        =   741
      _ExtentY        =   741
      _Version        =   262150
   End
End
Attribute VB_Name = "Form_contrato_antiguos"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()
    Dim conecta As Boolean
    Dim cursos As String
    Dim descu As Boolean
    Dim rut_apo As String
    Dim apoderado As String
    Dim var_sql As String
    Dim Cod_Ens As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    
'    Select Case IC_Ensenanza.Text
'        Case Is = "Educación Parvularia"
'            Cod_Ens = "010"
'        Case Is = "Educación General Básica"
'            Cod_Ens = "110"
'        Case Is = "Educación Media H/C niños y jóvenes"
'            Cod_Ens = "310"
'        Case Is = "Otros Tipos (Pre Kinder)"
'            Cod_Ens = "996"
'        Case Is = "Play Group Media Mayor"
'            Cod_Ens = "222"
'        Case Is = "Play Group"
'            Cod_Ens = "111"
'    End Select
'
'    If Cod_Ens = "310" And IC_Nivel.SelectedItem.Text = 4 Then
'        MsgBox "Cuartos Medios NO son validos", vbInformation
'        Exit Sub
'    End If
        
    var_sql = "SELECT run_apo FROM fnza_mensualidad WHERE agno = 2021 AND run_apo = '" & txt_rut.Text & "'"
    objRS.Open var_sql, conn

    If objRS.RecordCount = 0 Then
        MsgBox "Rut No es valido o No Apoderado el 2021"
        objRS.Close
        Exit Sub
    End If
    objRS.Close
        
        
    'Rescato deudores de base d edatos comunes
    'borro tabla temporales
    var_sql = "DROP TABLE IF EXISTS temp_lista_negra_apoderados"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_lista_negra_alumnos"
    conn.Execute var_sql
    
    'conecto a otras coneciones
    conecta = ConectarBDSiempre(connLN, "192.168.7.91", "comunes_gescol", "groot", "kripplefight", True)
    
    'creo temporales
    var_sql = "create table temp_lista_negra_alumnos(" _
            & "ci_alu varchar (30));"
    conn.Execute var_sql
    var_sql = "create table `temp_lista_negra_apoderados` (" _
            & "`ci_apo` varchar (30));"
    conn.Execute var_sql

    'inserto alumnos con deuda
    var_sql = "SELECT ci_alu FROM fnza_lista_negra_alumnos where bloqueado = 1"
    objRS.Open var_sql, connLN
    While Not objRS.EOF
        var_sql = "insert into `temp_lista_negra_alumnos` (`ci_alu`) values('" & objRS("ci_alu") & "');"
        conn.Execute var_sql
        objRS.MoveNext
    Wend
    objRS.Close
    
'''''''    'inserto apoderados con deuda
'''''''    var_sql = "SELECT ci_apo FROM fnza_lista_negra_apoderados where bloqueado = 1"
'''''''    objRS.Open var_sql, connLN
'''''''    While Not objRS.EOF
'''''''        var_sql = "insert into `temp_lista_negra_apoderados` (`ci_apo`) values('" & objRS("ci_apo") & "');"
'''''''        conn.Execute var_sql
'''''''        objRS.MoveNext
'''''''    Wend
'''''''    objRS.Close
'''''''    connLN.Close
    
    ' Creo tabla de deudores de cuotas actuales
    var_sql = "DROP TABLE IF EXISTS temp_deuda_part_actual"
    conn.Execute var_sql
    var_sql = "CREATE TABLE temp_deuda_part_actual TYPE = MYISAM" _
            & " SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,dm.run_alu,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS Alumno," _
            & "SUM(monto_tot_cuota-monto_abono) AS monto_deuda,fech_venc," _
            & "LEFT(dm.colegio, 6) As colegio" _
            & " FROM fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.agno AND c.agno = axc.agno AND dm.agno > 2020" _
            & " AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno < 2022 AND dm.mes >= 1 AND dm.mes < 9" _
            & " GROUP BY a.rut_apo,dm.run_alu" _
            & " HAVING SUM(monto_tot_cuota - monto_abono) >= 100000;"
    conn.Execute var_sql
    
    ' Creo tabla de deudores de cuotas de años anteriores
    var_sql = "DROP TABLE IF EXISTS temp_deuda_part_anterior"
    conn.Execute var_sql
    var_sql = "CREATE TABLE temp_deuda_part_anterior TYPE = MYISAM" _
            & " SELECT a.rut_apo,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,dm.run_alu,CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS Alumno," _
            & "SUM(monto_tot_cuota-monto_abono) AS monto_deuda,fech_venc," _
            & "LEFT(dm.colegio, 6) As colegio" _
            & " FROM fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a" _
            & " WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu AND dm.agno = c.agno AND c.agno = axc.agno AND dm.agno > 2011" _
            & " AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno < 2021 AND dm.mes >= 1" _
            & " GROUP BY a.rut_apo,dm.run_alu" _
            & " HAVING SUM(monto_tot_cuota - monto_abono) >= 90000;"
    conn.Execute var_sql
    
    var_sql = "DROP TABLE IF EXISTS temp_mayor"
    conn.Execute var_sql
    
    var_sql = "CREATE TABLE temp_mayor TYPE = MYISAM" _
            & " SELECT b.rut_apo, MAX(b.orden),agno,curso,ci_alu,cod_ens,num_gra" _
            & " From" _
            & " (" _
            & " SELECT rut_apo,m.ci_alu,m.agno,CONCAT(m.num_gra,' ',let_cur,' ',IF(m.cod_ens='996','PK',IF(m.cod_ens='010','K',IF(m.cod_ens='110','EB','M')))) AS curso, CONCAT(ord_ens,m.num_gra,fec_nacalu,m.ci_alu) AS orden,m.cod_ens,m.num_gra" _
            & " FROM apo_x_alu a,alu_x_cur m,codigos_ensenanza e,postulantes p" _
            & " WHERE a.ci_alu = m.ci_alu AND a.agno = m.agno AND m.agno = '2021' AND m.cod_ens = e.cod_ens AND m.ci_alu = p.ci_alu AND m.activo_alu = 'SI' AND NOT (m.cod_ens = '310' AND m.num_gra = 4)" _
            & " AND NOT (m.ci_alu IN (SELECT ci_alu FROM temp_lista_negra_alumnos) or m.ci_alu IN (SELECT run_alu FROM temp_deuda_part_actual) or m.ci_alu IN (SELECT run_alu FROM temp_deuda_part_anterior))" _
            & " AND m.ci_alu NOT IN (SELECT rut FROM fnza_bloqueados_condicionales where agno = 2022)" _
            & " and rut_apo NOT IN (SELECT rut FROM fnza_enmendados) AND rut_apo NOT IN (SELECT rut_apo FROM fnza_cheques_rescatados)" _
            & " AND rut_apo NOT IN (SELECT ci_apo FROM fnza_lista_negra_apoderados where bloqueado = 1) ORDER BY rut_apo,ord_ens ASC,m.num_gra ASC,fec_nacalu DESC,m.ci_alu DESC" _
            & " ) AS b GROUP BY b.rut_apo ORDER BY curso;"
    conn.Execute var_sql
        
'    var_sql = "select comuna,dir_apo,concat(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) as alumno,a.rut_apo,concat(noms_apo,' ',ape1_apo,' ',ape2_apo) as apoderado,c.ci_alu, 0 as matricula,0 as mensualidad,0 as cheques,'888' as ens,'9' as gra,'aaaaaa' as ens2,0 as hermanos" _
'            & " from alu_x_cur c,apo_x_alu a,apoderados as apo,postulantes p,codigos__geograficos cg,matriculas m" _
'            & " where apo.comuna_apo = cg.cod_geo AND a.agno = '" & IC_Agno.SelectedItem.Text & "' and c.cod_ens = '" & Cod_Ens & "'" _
'            & " and c.num_gra = " & IC_Nivel.SelectedItem.Text & " and let_cur = '" & IC_Letra.SelectedItem.Text & "' and c.activo_alu = 'SI'" _
'            & " and c.ci_alu = a.ci_alu and a.rut_apo = apo.rut_apo and c.agno = a.agno and c.ci_alu = p.ci_alu" _
'            & " AND CONCAT(c.ci_alu,c.num_gra,c.cod_ens) IN (SELECT CONCAT(ci_alu,num_gra,cod_ens) FROM temp_mayor)" _
'            & " AND NOT (c.ci_alu IN (SELECT ci_alu FROM temp_lista_negra_alumnos) OR a.rut_apo IN (SELECT ci_apo FROM temp_lista_negra_apoderados) OR a.rut_apo IN (SELECT rut_apo FROM temp_deuda_part_actual) OR a.rut_apo IN (SELECT rut_apo FROM temp_deuda_part_anterior))" _
'            & " group by rut_apo"
            
''''''    var_sql = "select comuna,dir_apo,concat(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) as alumno,a.rut_apo,concat(noms_apo,' ',ape1_apo,' ',ape2_apo) as apoderado,c.ci_alu, 0 as matricula,0 as mensualidad,0 as cheques,'888' as ens,'9' as gra,'aaaaaa' as ens2,0 as hermanos" _
''''''            & " from alu_x_cur c,apo_x_alu a,apoderados as apo,postulantes p,codigos__geograficos cg,matriculas m" _
''''''            & " where apo.comuna_apo = cg.cod_geo AND a.agno = '2021' and c.cod_ens = '" & Cod_Ens & "'" _
''''''            & " and c.num_gra = " & IC_Nivel.SelectedItem.Text & " and let_cur = '2021' and c.activo_alu = 'SI'" _
''''''            & " and c.ci_alu = a.ci_alu and a.rut_apo = apo.rut_apo and c.agno = a.agno and c.ci_alu = p.ci_alu" _
''''''            & " AND CONCAT(c.ci_alu,c.num_gra,c.cod_ens) IN (SELECT CONCAT(ci_alu,num_gra,cod_ens) FROM temp_mayor)" _
''''''            & " AND NOT (c.ci_alu IN (SELECT rut FROM fnza_bloqueados_condicionales where agno = 2022) or c.ci_alu IN (SELECT ci_alu FROM temp_lista_negra_alumnos) OR a.rut_apo IN (SELECT ci_apo FROM fnza_lista_negra_apoderados where bloqueado = 1) OR a.rut_apo IN (SELECT rut_apo FROM temp_deuda_part_actual) OR a.rut_apo IN (SELECT rut_apo FROM temp_deuda_part_anterior))" _
''''''            & " and a.rut_apo NOT IN (SELECT rut FROM fnza_enmendados) AND a.rut_apo NOT IN (SELECT rut_apo FROM fnza_cheques_rescatados)" _
''''''            & " group by rut_apo"
''''''    objRS.Open var_sql, conn

    var_sql = "select comuna,dir_apo,concat(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) as alumno,a.rut_apo,concat(noms_apo,' ',ape1_apo,' ',ape2_apo) as apoderado,c.ci_alu, 0 as matricula,0 as mensualidad,0 as cheques,'888' as ens,'9' as gra,'aaaaaa' as ens2,0 as hermanos" _
            & " from alu_x_cur c,apo_x_alu a,apoderados as apo,postulantes p,codigos__geograficos cg,matriculas m" _
            & " where apo.comuna_apo = cg.cod_geo AND a.agno = '2021' and c.activo_alu = 'SI' and a.rut_apo = '" & txt_rut.Text & "'" _
            & " and c.ci_alu = a.ci_alu and a.rut_apo = apo.rut_apo and c.agno = a.agno and c.ci_alu = p.ci_alu" _
            & " AND CONCAT(c.ci_alu,c.num_gra,c.cod_ens) IN (SELECT CONCAT(ci_alu,num_gra,cod_ens) FROM temp_mayor)" _
            & " AND NOT (c.ci_alu IN (SELECT rut FROM fnza_bloqueados_condicionales where agno = 2022) or c.ci_alu IN (SELECT ci_alu FROM temp_lista_negra_alumnos) OR a.rut_apo IN (SELECT ci_apo FROM fnza_lista_negra_apoderados where bloqueado = 1) OR a.rut_apo IN (SELECT rut_apo FROM temp_deuda_part_actual) OR a.rut_apo IN (SELECT rut_apo FROM temp_deuda_part_anterior))" _
            & " and a.rut_apo NOT IN (SELECT rut FROM fnza_enmendados) AND a.rut_apo NOT IN (SELECT rut_apo FROM fnza_cheques_rescatados)" _
            & " group by rut_apo"
    objRS.Open var_sql, conn

    If objRS.RecordCount = 0 Then
        MsgBox "Rut No es valido o esta con deuda"
        objRS.Close
        Exit Sub
    End If

        
    If Not objRS.EOF Then
        conn.Execute "drop table if exists temp_circular;"
        conn.Execute "create table temp_circular type = MyISAM " & var_sql
        conn.Execute "ALTER TABLE temp_circular MODIFY ens2 VARCHAR(300)"
        conn.Execute "ALTER TABLE temp_circular MODIFY cheques double"
    End If
    
    While Not objRS.EOF
        rut_apo = objRS("rut_apo")
        apoderado = objRS("apoderado")
        alumno = objRS("alumno")
        var_sql = "select concat(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) as alumno,a.ci_alu from apo_x_alu a,alu_x_cur c,postulantes p,codigos_ensenanza ce" _
                & " where c.ci_alu = p.ci_alu and a.ci_alu = c.ci_alu and a.agno = c.agno" _
                & " and a.agno = 2021" _
                & " and c.activo_alu = 'SI' and a.rut_apo = '" & rut_apo & "' AND NOT (c.cod_ens = '310' AND c.num_gra = 4) and c.cod_ens = ce.cod_ens" _
                & " AND NOT(c.ci_alu IN (SELECT rut FROM fnza_bloqueados_condicionales WHERE agno = 2022) OR c.ci_alu IN (SELECT ci_alu FROM temp_lista_negra_alumnos) OR a.rut_apo IN (SELECT ci_apo FROM fnza_lista_negra_apoderados where bloqueado = 1) OR a.rut_apo IN (SELECT rut_apo FROM temp_deuda_part_actual) OR a.rut_apo IN (SELECT rut_apo FROM temp_deuda_part_anterior))" _
                & " and a.rut_apo NOT IN (SELECT rut FROM fnza_enmendados) AND rut_apo NOT IN (SELECT rut_apo FROM fnza_cheques_rescatados) ORDER BY ce.ord_ens DESC,c.num_gra DESC,fec_nacalu,c.ci_alu"
        If rut_apo = "10443288-3" Then
            aa = 1
        End If
        
        rec.Open var_sql, conn
        cursos = ""
        alus = ""
        
        hermano = 0
        While Not rec.EOF
            hermano = hermano + 1
            descu = calcula_descuentos_circular(rec("ci_alu"), rut_apo, apoderado, hermano)
            If descu = True Then
            
            cursos = cursos & "  -  " & arr_descuentos(5) & " " & arr_descuentos(6)
            'vbCrLf &
            alus = alus & vbCrLf & "Nombre: " & Mid((rec("alumno") & "                                                "), 1, 50) & " Curso 2022: " & arr_descuentos(5) & "º " & arr_descuentos(6) '& vbCrLf
            
            var_sql = "update temp_circular set " _
                    & "matricula = matricula + " & Round(arr_descuentos(1)) _
                    & ",mensualidad = mensualidad + " & arr_descuentos(2) _
                    & ",cheques = cheques + " & Replace(arr_descuentos(3), ",", ".") _
                    & ",ens = '" & arr_descuentos(4) & "'" _
                    & ",gra = '" & arr_descuentos(5) & "'" _
                    & ",ens2 = '" & alus & "'" _
                    & ",hermanos = hermanos + 1" _
                    & " where rut_apo = '" & rut_apo & "'"
                    
            conn.Execute var_sql
            End If
            rec.MoveNext
        Wend
    
        rec.Close
        objRS.MoveNext
    Wend
    objRS.Close
    
    var_sql = "update temp_circular set ens2 = mid(ens2,3)"
    conn.Execute var_sql
    
    sostenedor = DevuelveCampo("establecimientos", "", "nom_sostenedor", "1=1", conn)
    dir_est = DevuelveCampo("establecimientos", "", "dir_est", "1=1", conn)
    localidad_est = DevuelveCampo("establecimientos", "", "localidad_est", "1=1", conn)
    'Fecha_cont = Format(Date, "yyyy-mm-dd")
    Fecha_cont = Format(Date, "dd") & " de " & Mes2Texto(Format(Date, "mm")) & " de " & Format(Date, "yyyy")
    
    conn.Execute "drop table if exists temp_contratos_masivos;"
        
    var_sql = "CREATE TABLE `temp_contratos_masivos` (`CI_APO` varchar(10) collate latin1_spanish_ci NOT NULL default '',`TXT` text collate latin1_spanish_ci,`ENCARGADO` varchar(50) collate latin1_spanish_ci NOT NULL default '',`CI_APO_DOS` varchar(10) collate latin1_spanish_ci NOT NULL default '',`TXT_DETALLE` text collate latin1_spanish_ci NOT NULL,`CARDINAL` varchar(20) collate latin1_spanish_ci NOT NULL default '',`sostenedor` varchar(50) collate latin1_spanish_ci NOT NULL default '',`folio` varchar(30) collate latin1_spanish_ci NOT NULL default '5000') ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;"
    conn.Execute var_sql
        
    var_sql = "SELECT * FROM temp_circular"
    objRS.Open var_sql, conn
    While Not objRS.EOF
        var_sql = "SELECT * FROM temp_contrato_hue"
        rec.Open var_sql, conn
        While Not rec.EOF
            var_sql = "insert into temp_contratos_masivos(ci_apo,txt,encargado,ci_apo_dos,txt_detalle,cardinal,sostenedor)" _
                    & " values('" & objRS("rut_apo") & "','" & rec("txt") & "','" & rec("encargado") & "','" & objRS("rut_apo") & "','" & rec("txt_detalle") & "','" & rec("cardinal") & "','" & rec("sostenedor") & "')"
            conn.Execute var_sql
            rec.MoveNext
        Wend
        rec.Close
        '' folio inserto
        var_sql = "SELECT * FROM fnza_contrato WHERE agno = 2022 and rut_apo = '" & objRS("rut_apo") & "'"
        rec.Open var_sql, conn
        If rec.RecordCount > 0 Then
            obligacion = rec("folio")
        Else
            obligacion = DevuelveCampo("fnza_contrato", "", "max(folio)", "agno = 2022", conn) + 1
            var_sql = "INSERT INTO fnza_contrato(folio,rut_apo,agno) VALUES(" & obligacion & ",'" & objRS("rut_apo") & "',2022)"
            conn.Execute var_sql
        End If
        rec.Close
        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 & " - 2022"
        var_sql = "UPDATE temp_contratos_masivos SET folio = '" & obligacion_texto & "' WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        ' fin folio
        
        
        
        var_sql = "UPDATE temp_contratos_masivos SET txt = REPLACE(txt,'[comunaapoderado]','" & objRS("comuna") & "') WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt = REPLACE(txt,'[alumnos]','" & objRS("ens2") & "') WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt = REPLACE(txt,'[FEC_ACTUAL]','" & Fecha_cont & "') WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt = REPLACE(txt,'[NOM_COL]','" & sostenedor & "') WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt = REPLACE(txt,'[DIRCOM_COL]','" & dir_est & "') WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt = REPLACE(txt,'[NOM_APODERADO]','" & objRS("apoderado") & "') WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt = REPLACE(txt,'[CI_APO]','" & objRS("rut_apo") & "') WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt = REPLACE(txt,'[DIRCOM_APO]','" & objRS("dir_apo") & "') WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt_detalle = REPLACE(txt_detalle,'[matricula]','" & Format(objRS("matricula"), "#,#") & "') WHERE ci_apo = '" & objRS("rut_apo") & "' AND cardinal = 'TERCERO:'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt_detalle = REPLACE(txt_detalle,'[mensualidad]','" & objRS("cheques") & "') WHERE ci_apo = '" & objRS("rut_apo") & "' AND cardinal = 'TERCERO:'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET txt_detalle = REPLACE(txt_detalle,'[arancel]','" & Format((objRS("matricula") + objRS("mensualidad")), "#,#") & "') WHERE ci_apo = '" & objRS("rut_apo") & "' AND cardinal = 'TERCERO:'"
        conn.Execute var_sql
        objRS.MoveNext
    Wend
    objRS.Close
    
    CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
        
    Call LimpiaFormulas(cr_circular, 30)
   ' If Option1(0).Value = True Then
   If CODBC = "HUE001" Then
        cr_circular.ReportFileName = App.Path & "\Rpt\contrato_masivo_particulares_UF.rpt"
   Else
        cr_circular.ReportFileName = App.Path & "\Rpt\masivo_macul.rpt"
   End If
'    Else
'        cr_convenios.ReportFileName = App.Path & "\Rpt\fnza_convenios.rpt"
    'End If

'    cr_convenios.Formulas(3) = "est = '" & est & "'"
'    cr_convenios.Formulas(4) = "dir = '" & dir & "'"

    cr_circular.Destination = crptToWindow
'
    cr_circular.Action = 1
    

'IC_Agno.SelectedItem.Text
'IC_Ensenanza.SelectedItem.Text
'IC_Nivel.SelectedItem.Text
'IC_Letra.SelectedItem.Text
End Sub

Private Function calcula_descuentos_circular(ci_alu As String, rut_apo As String, apoderado As String, hermanos) As Boolean
    Dim var_sql As String
    Dim Cod_Ens As String
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    calcula_descuentos_circular = False
    
    var_sql = "drop table if exists temp_nivel;"
    conn.Execute var_sql
    
    var_sql = "drop table if exists temp_mat;"
    conn.Execute var_sql
    
    var_sql = "drop table if exists temp_men;"
    conn.Execute var_sql
    
    If ci_alu = "45000000-0" Then
        aa = 3
    End If

    var_sql = "create table temp_nivel type = MyISAM" _
            & " select ci_alu,num_gra,cod_ens," _
            & "if(cod_ens = '111','996',if(cod_ens = '111','996',if(cod_ens = '996','010',if(cod_ens = '010','110',if(cod_ens = '110' and num_gra < 8,'110','310'))))) as ens," _
            & "if(cod_ens = '111','1',if(cod_ens = '111','1',if(cod_ens = '996','1',if(cod_ens = '010',1,if(cod_ens = '110' and num_gra = 8,1,if(cod_ens = '110' and num_gra < 8,(num_gra+1),if(cod_ens = '310',(num_gra + 1),(num_gra + 1)))))))) as gra," _
            & "concat(if(cod_ens = '111','996',if(cod_ens = '222','996',if(cod_ens = '996','010',if(cod_ens = '010','110',if(cod_ens = '110' and num_gra < 8,'110','310')))))," _
            & "if(cod_ens = '111','1',if(cod_ens = '111','1',if(cod_ens = '996','1',if(cod_ens = '010',1,if(cod_ens = '110' and num_gra = 8,1,if(cod_ens = '110' and num_gra < 8,(num_gra+1),if(cod_ens = '310',(num_gra + 1),(num_gra + 1))))))))) as ee" _
            & " from alu_x_cur where agno = 2021 and ci_alu = '" & ci_alu & "';"
    conn.Execute var_sql

    'arregle aqui
    var_sql = "create table temp_mat type = MyISAM" _
            & " select ci_alu,ens as ensm,gra as gram,arancel_base,monto_tot_cuota,((monto_total * 100)/monto_total) as desm,ee" _
            & " from temp_nivel n,fnza_descuento f,fnza_detalle_mensualidad fm" _
            & " where n.ee = f.curso and f.agno = 2021 and n.ci_alu = fm.run_alu and fm.agno = 2021 and concepto = 'ct00';"
    conn.Execute var_sql

    var_sql = "create table temp_men type = MyISAM" _
            & " select mes,ci_alu,ens,gra,arancel_base,monto_tot_cuota,((monto_total * 100)/monto_total) as des" _
            & " from temp_nivel n,fnza_descuento f,fnza_detalle_mensualidad fm" _
            & " where n.ee = f.curso and f.agno = 2021 and n.ci_alu = fm.run_alu and fm.agno = 2021 and concepto <> 'ct00'" _
            & " order by mes desc limit 1;"
    conn.Execute var_sql
    
    CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
    
    If CODBC = "HUE001" Then
    'var_sql = "update temp_men set des =  if(des = 91.2,96,if(des = 87.4,92,if(des = 83.6,88,if(des = 72,96,if(des = 69,92,if(des = 66,88,if(round(des,3)=67.2,96,if(round(des,3)=64.4,92,if(round(des,3) = 61.6,88,des)))))))));"
    var_sql = "update temp_men set des =  if(round(des,3) = 91.2,96,if(round(des,3) = 87.4,92,if(round(des,3) = 83.6,88,if(round(des,3) = 72,96,if(round(des,3) = 69,92,if(round(des,3) = 66,88,if(round(des,3)=67.2,96,if(round(des,3)=64.4,92,if(round(des,3) = 61.6,88,des)))))))));"
    conn.Execute var_sql
    'var_sql = "update temp_mat ma,temp_men me set des =  if(desm = 50 and des = 84.48,96,if(desm = 50 and des = 80.96,92,if(desm = 50 and des = 77.44,88,des))) where ma.ci_alu = me.ci_alu;"
    var_sql = "update temp_mat ma,temp_men me set des =  if(desm = 50 and round(des,3) = 84.48,96,if(desm = 50 and round(des,3) = 80.96,92,if(desm = 50 and round(des,3) = 77.44,88,des))) where ma.ci_alu = me.ci_alu;"
    conn.Execute var_sql
    'var_sql = "update temp_mat ma,temp_men me  set des = if(desm = 50 and des = 80.256,96,if(desm = 50 and des = 76.912,92,if(desm = 50 and des = 73.568,88,des))) where ma.ci_alu = me.ci_alu;"
    var_sql = "update temp_mat ma,temp_men me  set des = if(desm = 50 and round(des,3) = 80.256,96,if(desm = 50 and round(des,3) = 76.912,92,if(desm = 50 and round(des,3) = 73.568,88,des))) where ma.ci_alu = me.ci_alu;"
    conn.Execute var_sql
    'var_sql = "update temp_men set des = if(des = 63.84,96,if(des = 61.18,92,if(des = 58.52,88,des)));"
    var_sql = "update temp_men set des = if(round(des,3) = 63.84,96,if(round(des,3) = 61.18,92,if(round(des,3) = 58.52,88,des)));"
    conn.Execute var_sql
    'var_sql = "update temp_men set des =  if(des = 68.4,96,if(des = 65.55,92,if(des = 62.7,88,des)));"
    var_sql = "update temp_men set des =  if(round(des,3) = 68.4,96,if(round(des,3) = 65.55,92,if(round(des,3) = 62.7,88,des)));"
    conn.Execute var_sql
    var_sql = "update temp_mat set desm = 100"
    conn.Execute var_sql
    var_sql = "update temp_men set des = if (des = 95 or des = 75 or des = 70,100,des)"
    conn.Execute var_sql
    End If
    
    If CODBC = "ALT001" Then
    'var_sql = "update temp_men set des =  if(des = 91.2,96,if(des = 87.4,92,if(des = 83.6,88,if(des = 72,96,if(des = 69,92,if(des = 66,88,if(round(des,3)=67.2,96,if(round(des,3)=64.4,92,if(round(des,3) = 61.6,88,des)))))))));"
    var_sql = "update temp_men set des =  if(round(des,3) = 91.2,96,if(round(des,3) = 87.4,92,if(round(des,3) = 83.6,88,if(round(des,3) = 72,96,if(round(des,3) = 69,92,if(round(des,3) = 66,88,if(round(des,3)=67.2,96,if(round(des,3)=64.4,92,if(round(des,3) = 61.6,88,des)))))))));"
    conn.Execute var_sql
    'var_sql = "update temp_mat ma,temp_men me set des =  if(desm = 50 and des = 84.48,96,if(desm = 50 and des = 80.96,92,if(desm = 50 and des = 77.44,88,des))) where ma.ci_alu = me.ci_alu;"
    var_sql = "update temp_mat ma,temp_men me set des =  if(desm = 50 and round(des,3) = 84.48,96,if(desm = 50 and round(des,3) = 80.96,92,if(desm = 50 and round(des,3) = 77.44,88,des))) where ma.ci_alu = me.ci_alu;"
    conn.Execute var_sql
    'var_sql = "update temp_mat ma,temp_men me  set des = if(desm = 50 and des = 80.256,96,if(desm = 50 and des = 76.912,92,if(desm = 50 and des = 73.568,88,des))) where ma.ci_alu = me.ci_alu;"
    var_sql = "update temp_mat ma,temp_men me  set des = if(desm = 50 and round(des,3) = 80.256,96,if(desm = 50 and round(des,3) = 76.912,92,if(desm = 50 and round(des,3) = 73.568,88,des))) where ma.ci_alu = me.ci_alu;"
    conn.Execute var_sql
    'var_sql = "update temp_men set des = if(des = 63.84,96,if(des = 61.18,92,if(des = 58.52,88,des)));"
    var_sql = "update temp_men set des = if(round(des,3) = 63.84,96,if(round(des,3) = 61.18,92,if(round(des,3) = 58.52,88,des)));"
    conn.Execute var_sql
    'var_sql = "update temp_men set des =  if(des = 68.4,96,if(des = 65.55,92,if(des = 62.7,88,des)));"
    var_sql = "update temp_men set des =  if(round(des,3) = 68.4,96,if(round(des,3) = 65.55,92,if(round(des,3) = 62.7,88,des)));"
    conn.Execute var_sql
    var_sql = "update temp_mat set desm = 100"
    conn.Execute var_sql
    var_sql = "update temp_men set des = if (des = 75 or des = 70,100,des)"
    conn.Execute var_sql
    End If
    
        
    If CODBC = "HUE001" Then
        If hermanos > 1 Then
            var_sql = "update temp_men set des = if(des < 88 or des = 0 or des = 100 or des = 96 or des = 92 or des = 88,100-((" & hermanos & " - 1) * 4),des)"
            conn.Execute var_sql
        End If
        If hermanos = 1 Then
            var_sql = "update temp_men set des = if(des = 94.5 or des = 0 or des = 96 or des = 92 or des = 88,100,des)"
            conn.Execute var_sql
        End If
    End If
    
    If CODBC = "ALT001" Then
        If hermanos > 1 Then
            var_sql = "update temp_men set des = 100-((" & hermanos & " - 1) * 5)"
            conn.Execute var_sql
        End If
        If hermanos = 1 Then
            var_sql = "update temp_men set des = 100"
            conn.Execute var_sql
        End If
    End If
    

    var_sql = "select f.arancel_base,ma.ci_alu,if(f.matricula * (desm /100) = 0,f.matricula,f.matricula * (desm /100) ) as matricula,(( f.arancel_base * (des /100)) * 10) as mensualidad,ens,gra,( f.arancel_base_UF * (des /100)) as cheques,if(ens = '111','Play Group',if(ens = '111','Play Group',if(ens = '996','Pre-Kinder',if(ens = '010','Kinder',if(ens = '110','Enseñanza Básica','Enseñanza Media'))))) ens2" _
            & " from temp_mat as ma,temp_men as me,fnza_descuento f" _
            & " where ma.ci_alu = me.ci_alu and ma.ee = f.curso and agno = 2022" & ";"
    objRS.Open var_sql, conn
    
'    If objRS("ci_alu") = "21181765-8" Then
'     aaa = 1
'    End If
    
    
    If Not objRS.EOF Then
        arr_descuentos(0) = objRS("ci_alu")
        arr_descuentos(1) = objRS("matricula")
        arr_descuentos(2) = objRS("mensualidad")
        arr_descuentos(3) = objRS("cheques")
        arr_descuentos(4) = objRS("ens")
        arr_descuentos(5) = objRS("gra")
        arr_descuentos(6) = objRS("ens2")
    End If
    
    objRS.Close

    calcula_descuentos_circular = True
End Function


Private Sub Command2_Click()
    Unload Me
End Sub
