VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.2#0"; "MSCOMCTL.OCX"
Object = "{00025600-0000-0000-C000-000000000046}#4.6#0"; "crystl32.ocx"
Begin VB.Form Form_circular_matricula 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Contratos Masivos Particulares"
   ClientHeight    =   1620
   ClientLeft      =   105
   ClientTop       =   435
   ClientWidth     =   9120
   Icon            =   "Form_circular_matricula.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   1620
   ScaleWidth      =   9120
   Begin Crystal.CrystalReport Cr_circular 
      Left            =   8880
      Top             =   360
      _ExtentX        =   741
      _ExtentY        =   741
      _Version        =   262150
   End
   Begin VB.Frame Frame1 
      Caption         =   "Selección por Curso"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   1215
      Left            =   240
      TabIndex        =   0
      Top             =   240
      Width           =   8655
      Begin VB.CheckBox Check_Letra 
         Caption         =   "Letra"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   195
         Left            =   5760
         TabIndex        =   2
         Top             =   360
         Value           =   1  'Checked
         Visible         =   0   'False
         Width           =   1095
      End
      Begin VB.CommandButton Btn_Ver_Lista 
         Caption         =   "Imprimir"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   6960
         TabIndex        =   1
         Top             =   480
         Width           =   1455
      End
      Begin MSComctlLib.ImageCombo IC_Ensenanza 
         Height          =   345
         Left            =   1320
         TabIndex        =   3
         Top             =   600
         Width           =   3135
         _ExtentX        =   5530
         _ExtentY        =   609
         _Version        =   393216
         ForeColor       =   -2147483640
         BackColor       =   -2147483643
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Locked          =   -1  'True
         Text            =   "IC_Ensenanza"
      End
      Begin MSComctlLib.ImageCombo IC_Nivel 
         Height          =   345
         Left            =   4560
         TabIndex        =   4
         Top             =   600
         Width           =   1095
         _ExtentX        =   1931
         _ExtentY        =   609
         _Version        =   393216
         ForeColor       =   0
         BackColor       =   -2147483643
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Locked          =   -1  'True
         Text            =   "IC_Nivel"
      End
      Begin MSComctlLib.ImageCombo IC_Letra 
         Height          =   345
         Left            =   5760
         TabIndex        =   5
         Top             =   600
         Width           =   1095
         _ExtentX        =   1931
         _ExtentY        =   609
         _Version        =   393216
         ForeColor       =   -2147483640
         BackColor       =   -2147483643
         Enabled         =   0   'False
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Locked          =   -1  'True
         Text            =   "IC_Letra"
      End
      Begin MSComctlLib.ImageCombo IC_Agno 
         Height          =   345
         Left            =   120
         TabIndex        =   6
         Top             =   600
         Width           =   1095
         _ExtentX        =   1931
         _ExtentY        =   609
         _Version        =   393216
         ForeColor       =   -2147483640
         BackColor       =   -2147483643
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Locked          =   -1  'True
         Text            =   "IC_Agno"
      End
      Begin VB.Label Label1 
         Alignment       =   2  'Center
         Caption         =   "Año"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Left            =   120
         TabIndex        =   9
         Top             =   360
         Width           =   855
      End
      Begin VB.Label Label2 
         Alignment       =   2  'Center
         Caption         =   "Enseñanza"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Left            =   1320
         TabIndex        =   8
         Top             =   360
         Width           =   2895
      End
      Begin VB.Label Label3 
         Alignment       =   2  'Center
         Caption         =   "Nivel"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   255
         Left            =   4560
         TabIndex        =   7
         Top             =   360
         Width           =   855
      End
   End
End
Attribute VB_Name = "Form_circular_matricula"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Set_IC_Agno()
    Dim agno As Integer
    
    agno = IIf(Year(Date) < 2005, 2005, Year(Date))
    IC_Agno.ComboItems.Clear
    For I = 1 To (agno - 2004) + 1
        IC_Agno.ComboItems.Add I, , str(I + 2004)
    Next I
    Set IC_Agno.SelectedItem = IC_Agno.ComboItems.Item(I - 2)
    ErrorStatus = False
    ' Llenar combo enseñanza
    IC_Ensenanza.Enabled = True
    IC_Nivel.Enabled = False
    'IC_Letra.Enabled = False
    Call Set_IC_Ensenanza
    
End Sub

Private Sub Set_IC_Ensenanza()
    Dim rec As ADODB.Recordset
    Dim QRY As String
    
    QRY = "SELECT DISTINCT m.cod_ens, ce.des_ens FROM matriculas as m, codigos_ensenanza as ce WHERE m.cod_ens=ce.cod_ens AND m.anno_mat=" & IC_Agno.SelectedItem.Text & " ORDER BY ce.ord_ens;"
    Set rec = New ADODB.Recordset
    rec.Open QRY, conn
    
    IC_Ensenanza.ComboItems.Clear
    If rec.RecordCount > 0 Then
        rec.MoveFirst
        For I = 1 To rec.RecordCount
            IC_Ensenanza.ComboItems.Add I, "K" & Format(rec!Cod_Ens, "000"), rec!des_ens
            rec.MoveNext
        Next I
        Set IC_Ensenanza.SelectedItem = IC_Ensenanza.ComboItems.Item(1)
        ErrorStatus = False
        ' Llenar Siguiente Combo.
        IC_Nivel.Enabled = True
        'IC_Letra.Enabled = False
        Call Set_IC_Nivel
    Else
        ErrorStatus = True
        IC_Ensenanza.Enabled = False
        IC_Nivel.Enabled = False
        'IC_Letra.Enabled = False
    End If
    rec.Close
End Sub

Private Sub Set_IC_Nivel()
    Dim rec As ADODB.Recordset
    Dim QRY As String

    QRY = "SELECT DISTINCT m.num_gra FROM matriculas as m WHERE m.cod_ens='" & Mid(IC_Ensenanza.SelectedItem.Key, 2) & "' AND m.anno_mat=" & IC_Agno.SelectedItem.Text & " ORDER BY m.num_gra;"
    Set rec = New ADODB.Recordset
    rec.Open QRY, conn
    
    IC_Nivel.ComboItems.Clear
    If rec.RecordCount > 0 Then
        rec.MoveFirst
        For I = 1 To rec.RecordCount
            IC_Nivel.ComboItems.Add I, , str(rec!Num_Gra)
            rec.MoveNext
        Next I
        Set IC_Nivel.SelectedItem = IC_Nivel.ComboItems.Item(1)
        ErrorStatus = False
        ' Llenar Siguiente Combo.
        If Check_Letra.Value = 1 Then
            IC_Letra.Enabled = True
            Call Set_IC_Letra
        End If
    Else
        ErrorStatus = True
        IC_Nivel.Enabled = False
        'IC_Letra.Enabled = False
    End If
    rec.Close
End Sub

Private Sub Set_IC_Letra()
    Dim rec As ADODB.Recordset
    Dim QRY As String
    
    If Check_Letra.Value = 1 Then
        IC_Letra.ComboItems.Clear
        IC_Letra.Text = ""
        QRY = "SELECT DISTINCT m.let_cur FROM alu_x_cur as m WHERE m.cod_ens='" & Mid(IC_Ensenanza.SelectedItem.Key, 2) & "' AND m.agno=" & Trim(IC_Agno.SelectedItem.Text) & " AND m.num_gra='" & Trim(IC_Nivel.SelectedItem.Text) & "' ORDER BY m.num_gra;"
        Set rec = New ADODB.Recordset
        rec.Open QRY, conn
        If rec.RecordCount > 0 Then
            rec.MoveFirst
            For I = 1 To rec.RecordCount
                IC_Letra.ComboItems.Add I, , rec!Let_Cur
                rec.MoveNext
            Next I
            Set IC_Letra.SelectedItem = IC_Letra.ComboItems.Item(1)
            ErrorStatus = False
            ' Llenar Siguiente Combo.
        Else
            ErrorStatus = True
            'Check_Letra.Value = 0
            'IC_Letra.Enabled = False
        End If
        rec.Close
    Else
        ErrorStatus = False
        'IC_Letra.Enabled = False
    End If
End Sub

Private Sub Btn_Ver_Lista_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
    
    Dim EnPalabras As clsNum2Let
    Set EnPalabras = New clsNum2Let
    
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")
    ApExcel.Workbooks.Add
    
    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
        
'''    '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
'''    var_sql = "DROP TABLE IF EXISTS temp_alu_prot"
'''    conn.Execute var_sql
'''    var_sql = "DROP TABLE IF EXISTS temp_prot_cam"
'''    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
''''''
''''''    ' cheques cambio apoderado
''''''    var_sql = "CREATE TABLE temp_alu_prot TYPE = MYISAM" _
''''''            & " SELECT ci_alu FROM fnza_lista_negra_apoderados b,apo_x_alu a WHERE ci_apo = rut_apo AND bloqueado = 1 GROUP BY ci_alu;"
''''''    conn.Execute var_sql
''''''    var_sql = "CREATE TABLE temp_prot_cam TYPE = MYISAM" _
''''''            & " SELECT * FROM apo_x_alu WHERE ci_alu IN (SELECT ci_alu FROM temp_alu_prot) GROUP BY rut_apo"
''''''    conn.Execute var_sql
    
'''''''    '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 > " & (IC_Agno.SelectedItem.Text - 1) _
'''''''''''            & " AND dm.run_alu = p.ci_alu AND axc.rut_apo = a.rut_apo AND dm.agno < " & (IC_Agno.SelectedItem.Text + 1) & " AND dm.mes >= 1 AND dm.mes < 10" _
'''''''''''            & " 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 < " & IC_Agno.SelectedItem.Text & " 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
    
    ''' alumno mayor con bloquoes de deuda
'''''    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 = '" & IC_Agno.SelectedItem.Text & "' 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 = " & (IC_Agno.SelectedItem.Text + 1) & ")" _
'''''            & " 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)" _
'''''            & " AND rut_apo NOT IN (SELECT rut_apo FROM temp_prot_cam)" _
'''''            & " 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
    
    ' alumno mayor sin deuda
    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 = '" & IC_Agno.SelectedItem.Text & "' 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 m.ci_alu NOT IN (SELECT rut FROM fnza_bloqueados_condicionales where agno = " & (IC_Agno.SelectedItem.Text + 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
        
     ''' seelccion de curso con bloqueo
'''    var_sql = "select cel_apo,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,' ' as alumnos,' ' as alumno1,' ' as alumno2,' ' as alumno3,' ' as alumno4,' ' as curso1,' ' as curso2,' ' as curso3,' ' as curso4" _
'''            & " 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 rut FROM fnza_bloqueados_condicionales where agno = 2025) 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)" _
'''            & " AND a.rut_apo NOT IN (SELECT rut_apo FROM temp_prot_cam) group by rut_apo"
'''    objRS.Open var_sql, conn
        
    '' seelccion de curso sin bloqueo
    var_sql = "select cel_apo,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,' ' as alumnos,' ' as alumno1,' ' as alumno2,' ' as alumno3,' ' as alumno4,' ' as curso1,' ' as curso2,' ' as curso3,' ' as curso4" _
            & " 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 rut FROM fnza_bloqueados_condicionales where agno = 2025))" _
            & " group by rut_apo"
            
    '' Funcionarios despues comentar
'''''''''    var_sql = "select cel_apo,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,' ' as alumnos,' ' as alumno1,' ' as alumno2,' ' as alumno3,' ' as alumno4,' ' as curso1,' ' as curso2,' ' as curso3,' ' as curso4" _
'''''''''            & " 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.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 = 2025))" _
'''''''''            & " AND a.rut_apo IN (SELECT rut_apo FROM temp_funcionarios_2024) group by rut_apo"
            
    objRS.Open var_sql, conn
        
    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 alumnos VARCHAR(200)"
        conn.Execute "ALTER TABLE temp_circular MODIFY alumno1 VARCHAR(50)"
        conn.Execute "ALTER TABLE temp_circular MODIFY alumno2 VARCHAR(50)"
        conn.Execute "ALTER TABLE temp_circular MODIFY alumno3 VARCHAR(50)"
        conn.Execute "ALTER TABLE temp_circular MODIFY alumno4 VARCHAR(50)"
        conn.Execute "ALTER TABLE temp_circular MODIFY curso1 VARCHAR(30)"
        conn.Execute "ALTER TABLE temp_circular MODIFY curso2 VARCHAR(30)"
        conn.Execute "ALTER TABLE temp_circular MODIFY curso3 VARCHAR(30)"
        conn.Execute "ALTER TABLE temp_circular MODIFY curso4 VARCHAR(30)"
        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")
        '''' alumnos apoderado con bloqueos
''''        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 = " & IC_Agno.SelectedItem.Text _
''''                & " 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 = 2025) 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)" _
''''                & " AND rut_apo NOT IN (SELECT rut_apo FROM temp_prot_cam) ORDER BY ce.ord_ens DESC,c.num_gra DESC,fec_nacalu,c.ci_alu"
        '''' Alumnos apoderado
        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 = " & IC_Agno.SelectedItem.Text _
                & " 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 = 2025))" _
                & " 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 = ""
        alumnos = ""
        
        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 & "Nombre: " & Mid((rec("alumno") & "                                                "), 1, 50) & " Curso 2025: " & arr_descuentos(5) & "º " & arr_descuentos(6) & vbCrLf
            alumnos = alumnos & rec("alumno") & ", "
            
            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 & "'" _
                    & ",alumnos = '" & alumnos & "'" _
                    & ",alumno" & hermano & " = '" & rec("alumno") & "'" _
                    & ",curso" & hermano & " = '" & arr_descuentos(5) & "º " & arr_descuentos(6) & "'" _
                    & ",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,1)"
    conn.Execute var_sql
    var_sql = "update temp_circular set alumnos = mid(alumnos,1,(length(alumnos)-2))"
    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'," _
            & "`cuota_incorporacion` varchar(80) collate latin1_spanish_ci NOT NULL default '',`matricula` varchar(150) collate latin1_spanish_ci NOT NULL default ''," _
            & "`linea_1` varchar(255) collate latin1_spanish_ci NOT NULL default '',`linea_2` varchar(255) collate latin1_spanish_ci NOT NULL default ''," _
            & "`nombre_apoderado` varchar(50) collate latin1_spanish_ci NOT NULL default '',`direccion` varchar(40) collate latin1_spanish_ci NOT NULL default ''," _
            & "`comuna` varchar(30) collate latin1_spanish_ci NOT NULL default '',`celular` varchar(14) collate latin1_spanish_ci NOT NULL default ''," _
            & "`alumnos` varchar(100) collate latin1_spanish_ci NOT NULL default '',`ens2` text collate latin1_spanish_ci NOT NULL default ''," _
            & "`Fecha_cont` varchar(40) collate latin1_spanish_ci NOT NULL default '',`alumno1` varchar(50) collate latin1_spanish_ci NOT NULL default ''," _
            & "`alumno2` varchar(50) collate latin1_spanish_ci NOT NULL default '',`alumno3` varchar(50) collate latin1_spanish_ci NOT NULL default ''," _
            & "`alumno4` varchar(50) collate latin1_spanish_ci NOT NULL default '',`curso1` varchar(30) collate latin1_spanish_ci NOT NULL default ''," _
            & "`curso2` varchar(30) collate latin1_spanish_ci NOT NULL default '',`curso3` varchar(30) collate latin1_spanish_ci NOT NULL default ''," _
            & "`curso4` varchar(30) collate latin1_spanish_ci NOT NULL default '',`valor_cuota` varchar(100) collate latin1_spanish_ci NOT NULL default '',valor varchar(100) collate latin1_spanish_ci NOT NULL default ''" _
            & ") 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,nombre_apoderado,direccion,comuna,alumnos,celular,ens2,Fecha_cont)" _
                    & " values('" & objRS("rut_apo") & "','" & rec("txt") & "','" & rec("encargado") & "','" & objRS("rut_apo") & "','" & rec("txt_detalle") & "','" & rec("cardinal") & "','" & rec("sostenedor") & "','" & objRS("apoderado") & "','" & objRS("dir_apo") & "','" & objRS("comuna") & "','" & objRS("alumnos") & "','" & objRS("cel_apo") & "','" & objRS("ens2") & "','" & Fecha_cont & "')"
            conn.Execute var_sql
            rec.MoveNext
        Wend
        rec.Close
        '' folio inserto
        var_sql = "SELECT * FROM fnza_contrato WHERE agno = " & (IC_Agno.SelectedItem.Text + 1) & " 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 = " & (IC_Agno.SelectedItem.Text + 1), conn) + 1
            var_sql = "INSERT INTO fnza_contrato(folio,rut_apo,agno) VALUES(" & obligacion & ",'" & objRS("rut_apo") & "'," & (IC_Agno.SelectedItem.Text + 1) & ")"
            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 & " - " & (IC_Agno.SelectedItem.Text + 1)
        var_sql = "UPDATE temp_contratos_masivos SET folio = '" & obligacion_texto & "' WHERE ci_apo = '" & objRS("rut_apo") & "'"
        conn.Execute var_sql
        ' fin folio
        
        ''''''''' Inserto formulas parrafo 3
'        EnPalabras.Numero = monto_cuota_incorporacion
'        If monto_cuota_incorporacion = 0 Then
'            incor = ""
'        Else
'            incor = "$" & FormatNumber(monto_cuota_incorporacion, 0) & ".-(" & LCase(EnPalabras.ALetra) & ")."
'        End If
'        Form_main.CrystalReport.Formulas(3) = "cuota_incorporacion = '" & incor & "'"
        
        EnPalabras.Numero = objRS("matricula")
        Form_main.CrystalReport.Formulas(4) = "matricula = '" & "$" & FormatNumber(objRS("matricula"), 0) & ".-(" & LCase(EnPalabras.ALetra) & ")." & "'"
        linea_0 = "$" & FormatNumber(objRS("matricula"), 0) & ".-(" & LCase(EnPalabras.ALetra) & ")."
        
        If objRS("rut_apo") = "18949057-7" Then
            a = "SI"
        End If
        EnPalabras.Numero = (objRS("cheques") * 10)
        Dim valor_decimales As Double
        valor_decimales = (objRS("cheques") * 10) - Int((objRS("cheques") * 10))
        valor_decimales = Round(valor_decimales, 2)
        largo_valor = Len(CStr(valor_decimales))
        If largo_valor = 3 Then decimales = valor_decimales * 10
        If largo_valor = 4 Then decimales = valor_decimales * 100
        If largo_valor = 5 Then decimales = valor_decimales * 1000
        If largo_valor = 6 Then decimales = valor_decimales * 10000
        
        texto_cuota = Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6) ' & " coma "
        If decimales <> "" Then
            EnPalabras.Numero = decimales
            texto_cuota = texto_cuota & " coma " & IIf(largo_valor = 4 And Mid(valor_decimales, 3, 1) = 0, "cero ", "") & Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6)
        End If
        texto_cuota = "UF " & Round((objRS("cheques") * 10), 2) & ".-(" & LCase(texto_cuota) & " Unidades de Fomento),"
                    
        'Form_main.CrystalReport.Formulas(5) = "mensualidades = '" & "UF " & monto_total_uf & ".-(" & LCase(Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6)) & " Unidades de Fomento)," & "'"
        Form_main.CrystalReport.Formulas(5) = "mensualidades = '" & texto_cuota & "'"
        total_mensu = "3.- Colegiatura anual año 2025: " & texto_cuota & " las cuales se podrán pagar de la siguiente manera:"
        Form_main.CrystalReport.Formulas(6) = "cantidad_cuotas = '" & 10 & "'"
        
        'Dim valor_decimales As Double
        valor_decimales = objRS("cheques") - Int(objRS("cheques"))
        valor_decimales = Round(valor_decimales, 2)
        largo_valor = Len(CStr(valor_decimales))
        If largo_valor = 3 Then decimales = valor_decimales * 10
        If largo_valor = 4 Then decimales = valor_decimales * 100
        If largo_valor = 5 Then decimales = valor_decimales * 1000
        If largo_valor = 6 Then decimales = valor_decimales * 10000
        EnPalabras.Numero = Round(objRS("cheques"), 2)
        
        texto_cuota = Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6) ' & " coma "
        If decimales <> "" Then
            EnPalabras.Numero = decimales
            texto_cuota = texto_cuota & " coma " & IIf(largo_valor = 4 And Mid(valor_decimales, 3, 1) = 0, "cero ", "") & Mid(EnPalabras.ALetra, 1, Len(EnPalabras.ALetra) - 6)
        End If
        texto_cuota = "UF " & Round(objRS("cheques"), 2) & ".-(" & LCase(texto_cuota) & " Unidades de Fomento),"
      
        
        Form_main.CrystalReport.Formulas(7) = "valor_cuota = '" & texto_cuota & "'"
'        linea_2 = "A.- Se podrá pagar a través de " & cuotas & " cuotas mensuales, iguales y sucesivas por el monto de " & texto_cuota _
'                & " con vencimiento los días 10 de cada mes, pagaderas comenzando la primera cuota el mes de " & LCase(DevuelveCampo("meses", "", "mes", "num_mes = '" & Desde & "'", conn)) & " de 2025"
        linea_2 = "A.- Con el objeto da facilitar el pago de dinero correspondiente a la colegiatura anual, se podrá pagar a través de 10 cuotas mensuales, iguales y sucesivas por el monto de " & texto_cuota _
             '   & " con vencimiento los días 10 de cada mes, pagaderas comenzando la primera cuota el mes de marzo de 2025, así sucesivamente,"
        
        
        'Joralu = DevuelveCampo("meses", "", "mes", "num_mes = '" & desde & "'", conn)
        Form_main.CrystalReport.Formulas(8) = "desde = '" & LCase(DevuelveCampo("meses", "", "mes", "num_mes = '" & Desde & "'", conn)) & "'"
        Form_main.CrystalReport.Formulas(9) = "hasta = '" & LCase(DevuelveCampo("meses", "", "mes", "num_mes = '" & Hasta & "'", conn)) & "'"
        'Form_main.CrystalReport.Formulas(10) = "fp = '" & tpago.Text & "'"
        Form_main.CrystalReport.Formulas(11) = "linea_1 = '" & total_mensu & "'"
        Form_main.CrystalReport.Formulas(12) = "linea_2 = '" & linea_2 & "'"
                
                
        ''''''''' Fin formulas
        
        
        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
        
        var_sql = "UPDATE temp_contratos_masivos SET matricula = '" & linea_0 & "' WHERE ci_apo = '" & objRS("rut_apo") & "' AND cardinal = 'TERCERO:'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET linea_1 = '" & total_mensu & "' WHERE ci_apo = '" & objRS("rut_apo") & "' AND cardinal = 'TERCERO:'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET linea_2 = '" & linea_2 & "' WHERE ci_apo = '" & objRS("rut_apo") & "' AND cardinal = 'TERCERO:'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET curso1 = '" & objRS("curso1") & "',curso2 = '" & objRS("curso2") & "',curso3 = '" & objRS("curso3") & "',curso4 = '" & objRS("curso4") & "' WHERE ci_apo = '" & objRS("rut_apo") & "' AND cardinal = 'TERCERO:'"
        conn.Execute var_sql
        var_sql = "UPDATE temp_contratos_masivos SET alumno1 = '" & objRS("alumno1") & "',alumno2 = '" & objRS("alumno2") & "',alumno3 = '" & objRS("alumno3") & "',alumno4 = '" & objRS("alumno4") & "',valor_cuota = '" & texto_cuota & "',valor = '" & Round(objRS("cheques"), 2) & "' 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,'[matricula]','" & linea_0 & "') 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,'[linea_1]','" & total_mensu & "') 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,'[linea_2]','" & linea_2 & "') 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
    
    var_sql = "UPDATE temp_contratos_masivos SET linea_1 = MID(linea_1,5),linea_2 = MID(linea_2,5)"
    conn.Execute var_sql
    
    var_sql = "SELECT ci_apo,folio,matricula,linea_1 AS mensualidad,linea_2 AS parrafo_a,nombre_apoderado AS apoderado,direccion,comuna,celular,alumnos," _
            & "ens2,fecha_cont,alumno1,alumno2,alumno3,alumno4,curso1,curso2,curso3,curso4,valor_cuota,valor FROM temp_contratos_masivos WHERE cardinal = 'TERCERO:'"
    objRS.Open var_sql, conn
    
    ApExcel.Cells(1, 1).Formula = "rut"
    ApExcel.Cells(1, 2).Formula = "folio"
    ApExcel.Cells(1, 3).Formula = "matricula"
    ApExcel.Cells(1, 4).Formula = "mensualidad"
    ApExcel.Cells(1, 5).Formula = "parrafo_a"
    ApExcel.Cells(1, 6).Formula = "apoderado"
    ApExcel.Cells(1, 7).Formula = "direccion"
    ApExcel.Cells(1, 8).Formula = "comuna"
    ApExcel.Cells(1, 9).Formula = "celular"
    ApExcel.Cells(1, 10).Formula = "alumnos"
    ApExcel.Cells(1, 11).Formula = "ens2"
    ApExcel.Cells(1, 12).Formula = "fecha_cont"
    ApExcel.Cells(1, 13).Formula = "alumno1"
    ApExcel.Cells(1, 14).Formula = "alumno2"
    ApExcel.Cells(1, 15).Formula = "alumno3"
    ApExcel.Cells(1, 16).Formula = "alumno4"
    ApExcel.Cells(1, 17).Formula = "curso1"
    ApExcel.Cells(1, 18).Formula = "curso2"
    ApExcel.Cells(1, 19).Formula = "curso3"
    ApExcel.Cells(1, 20).Formula = "curso4"
    ApExcel.Cells(1, 21).Formula = "valor_cuota"
    ApExcel.Cells(1, 22).Formula = "valor"
    
    I = 1
    While Not objRS.EOF
        I = I + 1
        For j = 0 To 21
        ApExcel.Cells(I, (j + 1)).Formula = objRS(j)
        Next j
        objRS.MoveNext
    Wend
    objRS.Close
    
    ApExcel.Visible = True
    '' ApExcel.Range("a1:f1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing

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

Private Sub Form_Load()
    Call Set_IC_Agno
End Sub

Private Sub IC_Agno_Click()
    IC_Ensenanza.Enabled = True
    Set_IC_Ensenanza
End Sub

Private Sub IC_Ensenanza_Click()
    IC_Nivel.Enabled = True
    Call Set_IC_Nivel
End Sub



Private Sub IC_Nivel_Click()
    If Check_Letra.Value = 1 Then
        IC_Letra.Enabled = True
        'Check_Letra.Value = 0
        Call Set_IC_Letra
    End If
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 = " & IC_Agno.SelectedItem.Text & " 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 = " & IC_Agno.SelectedItem.Text & " and n.ci_alu = fm.run_alu and fm.agno = " & IC_Agno.SelectedItem.Text & " and concepto = 'ct00';"
    conn.Execute var_sql

    ' arregle que mensualidades sean mayores a cero
    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 = " & IC_Agno.SelectedItem.Text & " and n.ci_alu = fm.run_alu and fm.agno = " & IC_Agno.SelectedItem.Text & " and concepto <> 'ct00' AND monto_tot_cuota <> 0" _
            & " 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 = " & (IC_Agno.SelectedItem.Text + 1) & ";"
    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")
''''''''''        If hermanos = 1 Then
'''''''''            arr_descuentos(3) = "4,884"
'''''''''            arr_descuentos(1) = 178800
''''''''''        End If
        
    End If
    
    objRS.Close

    calcula_descuentos_circular = True
End Function
