VERSION 5.00
Object = "{00025600-0000-0000-C000-000000000046}#4.6#0"; "crystl32.ocx"
Begin VB.Form Form_inscritos_y_matriculados 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Control de inscritos y matriculados"
   ClientHeight    =   2550
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   4200
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   2550
   ScaleWidth      =   4200
   Begin Crystal.CrystalReport CrystalReport1 
      Left            =   3360
      Top             =   1560
      _ExtentX        =   741
      _ExtentY        =   741
      _Version        =   262150
   End
   Begin VB.ComboBox cb_agno 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   330
      Left            =   1080
      TabIndex        =   1
      Text            =   "Combo1"
      Top             =   480
      Width           =   2535
   End
   Begin VB.CommandButton Command1 
      Caption         =   "Generar Reporte"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   1200
      Picture         =   "Form_inscritos_y_matriculados.frx":0000
      Style           =   1  'Graphical
      TabIndex        =   0
      Top             =   1440
      Width           =   1695
   End
   Begin VB.Label Label1 
      Caption         =   "Aņo"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   480
      TabIndex        =   2
      Top             =   480
      Width           =   495
   End
End
Attribute VB_Name = "Form_inscritos_y_matriculados"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()
    Dim var_sql As String
      
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim colegio As String
    
    
    
    var_sql = "DROP TABLE IF EXISTS temp_inscritos;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_f;"
    conn.Execute var_sql
    
    var_sql = "create table temp_inscritos type = MyISAM" _
            & " select if(mat.cod_ens='996','PreKinder',if(mat.cod_ens='010','Kinder',if(mat.cod_ens='110','Basica',if(mat.cod_ens='310','Media','PlayGroup')))) as nivel,b.run_apo,b.run_alu," _
            & " concat(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) nombre_alumno," _
            & "folio_blta_fisico as folio,fec_mat,fecha_pago,sum(mm.monto_pago) as monto_pago,mat.num_gra,m.concepto,99999999 as cuota," _
            & "'1 A Basica' as curso,m.agno,9999999 as saldo" _
            & " from fnza_boletas b,fnza_dtll_mvto n,fnza_movimientos_mensualidad mm," _
            & "fnza_detalle_mensualidad m,matriculas mat,postulantes p" _
            & " Where b.folio_blta = n.folio_blta And n.folio_mvto = mm.folio_mvto And n.folio_dtll = m.folio_dtll" _
            & " and estado_blta = 'aprb' and agno = " & cb_agno.Text & " and anno_mat = " & cb_agno.Text _
            & " and m.run_alu = mat.ci_alu and mat.activo_alu = 'SI'" _
            & " and mat.ci_alu = p.ci_alu" _
            & " group by folio_blta_fisico;"
    conn.Execute var_sql
    
    var_sql = "ALTER TABLE temp_inscritos ADD correlativo BIGINT AUTO_INCREMENT, ADD INDEX(correlativo);"
    conn.Execute var_sql
    
    var_sql = "update temp_inscritos set curso = concat(num_gra,' ',nivel),cuota=0,saldo=0;"
    conn.Execute var_sql
    
    var_sql = "update temp_inscritos i,alu_x_cur c set curso = concat(i.num_gra,' ',c.let_cur,' ',i.nivel) where i.run_alu = c.ci_alu and i.agno = c.agno;"
    conn.Execute var_sql
    
    var_sql = "create table temp_f type = MyISAM" _
            & " select run_alu,sum(monto_tot_cuota) as cuo,agno,concepto from fnza_detalle_mensualidad" _
            & " Where agno = " & cb_agno.Text _
            & " group by run_alu,concepto;"
    conn.Execute var_sql
    
    var_sql = "ALTER TABLE temp_f ADD PRIMARY KEY(run_alu,agno,concepto);"
    conn.Execute var_sql
    
    var_sql = "update temp_inscritos i,temp_f f set cuota = (cuo-monto_pago)" _
            & " where  i.run_alu = f.run_alu and i.agno = f.agno and i.concepto = f.concepto;"
    conn.Execute var_sql
    
    var_sql = "SELECT if(nombre='Boston College Huechuraba','Huechuraba',nombre) as nombre,colegio" _
            & " From hist_cod_colegio" _
            & " where colegio = (select valor from cfg where param = 'codbc')"
    objRS.Open var_sql, conn
    colegio = objRS("colegio")
    nombre = objRS("nombre")
    objRS.Close
    
    
    CrystalReport1.ReportFileName = App.Path & "\rpt\inscritos_y_matriculados.rpt"
    
    CrystalReport1.WindowState = crptMaximized
   'cr_perso.WindowTitle = "Informe De Desarrollo Personal y Social"
'    cr_pre.ReplaceSelectionFormula ("")
    CrystalReport1.Formulas(1) = "establo = '" & nombre & "'"
'    cr_perso.Formulas(1) = "fecha = '" & Format(Now, "dd/mm/yyyy") & "'"
'    cr_perso.Formulas(2) = "hora = '" & Format(Now, "hh:mm AM/PM") & "'"
    'cr_perso.ReplaceSelectionFormula ("{pre_eval_cab.cod_ens}='" & xcod & "' and {pre_eval_cab.num_gra}='" & CInt(xnum_gra) & "' and {pre_eval_cab.let_cur}='" & xletra & "' and {pre_eval_cab.agno}=" & str(xagno)) & Per
    CrystalReport1.Destination = crptToWindow
    CrystalReport1.Action = 1
   
End Sub

Private Sub Form_Load()
    cb_agno.AddItem CInt(Format(Date, "yyyy"))
    cb_agno.AddItem (CInt(Format(Date, "yyyy")) + 1)
    cb_agno.Text = 2012
End Sub
