VERSION 5.00
Object = "{00025600-0000-0000-C000-000000000046}#4.6#0"; "crystl32.ocx"
Begin VB.Form Form_planilla_de_recaudacion 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Planilla de recaudación"
   ClientHeight    =   3030
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   3180
   LinkTopic       =   "form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   3030
   ScaleWidth      =   3180
   Begin Crystal.CrystalReport CrystalReport1 
      Left            =   0
      Top             =   0
      _ExtentX        =   741
      _ExtentY        =   741
      _Version        =   262150
   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            =   360
      Picture         =   "Form_planilla_de_recaudacion.frx":0000
      Style           =   1  'Graphical
      TabIndex        =   4
      Top             =   1920
      Width           =   2415
   End
   Begin VB.TextBox txt_folio_final 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   1680
      TabIndex        =   3
      Top             =   1080
      Width           =   1215
   End
   Begin VB.TextBox txt_folio_inicial 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   1680
      TabIndex        =   2
      Top             =   360
      Width           =   1215
   End
   Begin VB.Label Label2 
      Caption         =   "Folio inicial"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   360
      TabIndex        =   1
      Top             =   480
      Width           =   1215
   End
   Begin VB.Label Label1 
      Caption         =   "Folio final"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   360
      TabIndex        =   0
      Top             =   1200
      Width           =   1215
   End
End
Attribute VB_Name = "Form_planilla_de_recaudacion"
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 minimo As Integer
    Dim maximo As Integer
    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,mm.folio_mvto," _
            & "if(tipo_pago = 'chqe','Cheques',if(tipo_pago='cobr','Cobranza',if(tipo_pago='ctdo','Contado',if(tipo_pago='depo','Deposito','')))) as tipo_pago" _
            & " 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 = anno_mat " _
            & " and m.run_alu = mat.ci_alu and mat.activo_alu = 'SI'" _
            & " and mat.ci_alu = p.ci_alu and folio_blta_fisico >= " & txt_folio_inicial.Text _
            & " and folio_blta_fisico <= " & txt_folio_final.Text & " 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 = "select max(agno) as maximo,min(agno) as minimo from temp_inscritos"
    objRS.Open var_sql, conn
    
    If objRS("minimo") <> "" Then
        minimo = objRS("minimo")
    End If
        
    If objRS("maximo") <> "" Then
        maximo = objRS("maximo")
    End If
    
    objRS.Close
    
    
    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 >= " & minimo & " and agno <= " & maximo _
            & " group by agno,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 = "DROP TABLE IF EXISTS temp_cheques;"
    conn.Execute var_sql
    
    var_sql = "create table temp_cheques type = MyISAM" _
            & " select folio_mvto,b.descripcion as banco,concat(chq_serie,'--',chq_numero,'--',cta_cte) as numero_cheque,nom_titular as titular,chq_fecha as fecha_vencimiento,monto_chq as monto_cheque" _
            & " from fnza_chq_mvto cm,fnza_cheques_recibidos cr,fnza_cod_banco b" _
            & " Where cm.folio_chq = cr.folio_chq And cr.banco = b.banco"
    conn.Execute var_sql
    
    var_sql = "ALTER TABLE temp_inscritos ADD INDEX(folio_mvto);"
    conn.Execute var_sql
    var_sql = "ALTER TABLE temp_cheques ADD INDEX(folio_mvto);"
    conn.Execute var_sql

    var_sql = "DROP TABLE IF EXISTS temp_rendicion;"
    conn.Execute var_sql
    
    var_sql = "create table temp_rendicion type = MyISAM" _
            & " select folio,fecha_pago,nombre_alumno,curso,monto_pago,banco,numero_cheque,titular,fecha_vencimiento,monto_cheque," _
            & "if(tipo_pago = 'Cheques',monto_pago,0) as monto_cheques,if(tipo_pago <> 'Cheques',monto_pago,0) as monto_efectivo" _
            & " from temp_inscritos i left join temp_cheques c" _
            & " on i.folio_mvto = c.folio_mvto"
    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\planilla_de_recaudacion.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

