VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.1#0"; "mscomctl.ocx"
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
Begin VB.Form Form_Begin_Proc_Mat_Mas 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Iniciar Proceso de Matriculas Masivas para el Proximo Año"
   ClientHeight    =   6285
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   7035
   Icon            =   "Form_Begin_Proc_Mat_Mas.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   6285
   ScaleWidth      =   7035
   Begin VB.Frame Frame3 
      Caption         =   "Progreso"
      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            =   120
      TabIndex        =   6
      Top             =   4080
      Width           =   6735
      Begin MSComctlLib.ProgressBar PBar 
         Height          =   375
         Left            =   120
         TabIndex        =   7
         Top             =   720
         Width           =   6495
         _ExtentX        =   11456
         _ExtentY        =   661
         _Version        =   393216
         Appearance      =   1
         Min             =   1e-4
         Scrolling       =   1
      End
      Begin VB.Label Label2 
         Alignment       =   2  'Center
         Caption         =   "Label2"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H000000FF&
         Height          =   375
         Left            =   120
         TabIndex        =   8
         Top             =   240
         Width           =   6495
      End
   End
   Begin VB.Frame Frame2 
      Caption         =   "Selección de masivos"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   975
      Left            =   120
      TabIndex        =   3
      Top             =   3000
      Width           =   6735
      Begin VB.OptionButton Option1 
         Caption         =   "Todos los alumnos excepto los con renuncia"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Index           =   1
         Left            =   2760
         TabIndex        =   5
         Top             =   360
         Width           =   3735
      End
      Begin VB.OptionButton Option1 
         Caption         =   "Alumnos con reservas"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Index           =   0
         Left            =   360
         TabIndex        =   4
         Top             =   360
         Value           =   -1  'True
         Width           =   2055
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "Observación"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   2775
      Left            =   120
      TabIndex        =   1
      Top             =   120
      Width           =   6735
      Begin VB.Label Label1 
         Caption         =   "Label1"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H000000FF&
         Height          =   2415
         Left            =   120
         TabIndex        =   2
         Top             =   240
         Width           =   6495
      End
   End
   Begin MSComDlg.CommonDialog CDBox 
      Left            =   6120
      Top             =   5640
      _ExtentX        =   847
      _ExtentY        =   847
      _Version        =   393216
   End
   Begin VB.CommandButton Command1 
      Caption         =   "Generar Matrículas Masivas"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   615
      Left            =   1560
      TabIndex        =   0
      Top             =   5520
      Width           =   4215
   End
End
Attribute VB_Name = "Form_Begin_Proc_Mat_Mas"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
' var. Globales
Dim EnProc As Boolean

Private Sub Command1_Click()
    Dim fec1 As String
    Dim fec2 As String
    Dim var_sql As String
    Dim protestados As Boolean
    Dim RCL As ADODB.Recordset
    Dim RCR As ADODB.Recordset
    Dim REC As ADODB.Recordset
    Dim QRY As String
    Dim CMD As ADODB.Command
    Dim Affected As Long
    Dim Clave As String
    Dim SignI As String
    Dim MiClave As String
    Dim MiClaveAll As String
    Dim curso, grado, accion_realizada As String
    Dim log As Boolean
    Dim deuda As Boolean
    
    Set REC = New ADODB.Recordset
    'Verifico la fecha de corte para el proceso de matriculas masivas
    var_sql = "select * from cfg" _
            & " where param = 'GEN_RES_MAT_READY' and agno = " & Anno
    REC.Open var_sql, conn
    fec1 = CLng(Format(Now, "yyyymmdd"))
    fec2 = CLng(Format(REC("valor"), "yyyymmdd"))
    If fec1 > fec2 Then
        MsgBox "No se puede ejecutar el proceso de matriculas masivas, el plazo termino el " & REC("valor"), vbInformation, "Matriculas masivas"
        Exit Sub
    End If
    REC.Close
    
    Clave = InputBox("Ingrese la clave para iniciar del proceso", "Ingreso de Clave", "")
    MiClave = (Anno + 1) & "." & DevuelveCampo("establecimientos", "", "RDB", "1=1", conn)
    If Clave <> MiClave Then
        MsgBox "Clave Errónea", vbCritical, "Error en Clave"
        Exit Sub
    End If
    
    EnProc = True
    Command1.Enabled = False
    If Option1(0).Value = True Then
        QRY = "SELECT m.ci_alu, 'SI' as 'activo_alu', '00000' as 'num_mat', '" & Anno & "-12-31' as 'fec_mat', '1111-11-11' as 'fec_retiro', '0' as 'reserva', if(m.cod_ens='996','010',if(m.cod_ens='010','110',if(m.cod_ens='110' and m.num_gra=8,'310',m.cod_ens))) as 'cod_ens', if(m.cod_ens='996' or m.cod_ens='010',1,if(m.cod_ens='110' and m.num_gra=8,1,if(m.cod_ens='310' and m.num_gra=4,4,m.num_gra+1))) as 'num_gra', m.cod_jor, (m.anno_mat +1) as 'anno_mat', if(bf.asignada=100,0,1) as 'ind_cup', 0 as 'ind_cheq', 0 as 'ind_pac', if(bf.asignada=100,1,0) as 'ind_beca', if(bf.asignada=50,1,0) as 'ind_mbeca', 0 as 'ind_cont' FROM (SELECT ma.* FROM (SELECT * FROM matriculas  WHERE anno_mat=" & Anno & " and activo_alu='SI' and reserva=1) as ma LEFT JOIN (SELECT ci_alu, reserva FROM matriculas WHERE anno_mat=" & (Anno + 1) & " and activo_alu='SI') as mn ON ma.ci_alu=mn.ci_alu WHERE mn.ci_alu is null) as m LEFT JOIN becas_fichas as bf " & _
              "ON m.ci_alu=bf.ci_alumno AND bf.agno=(m.anno_mat+1) WHERE m.reserva=1 AND m.anno_mat=" & Anno & " " & _
              "ORDER BY m.cod_ens, m.num_gra;"
    Else
        QRY = "SELECT m.ci_alu, 'SI' as 'activo_alu', '00000' as 'num_mat', '" & Anno & "-12-31' as 'fec_mat', '1111-11-11' as 'fec_retiro', '0' as 'reserva', if(m.cod_ens='996','010',if(m.cod_ens='010','110',if(m.cod_ens='110' and m.num_gra=8,'310',m.cod_ens))) as 'cod_ens', if(m.cod_ens='996' or m.cod_ens='010',1,if(m.cod_ens='110' and m.num_gra=8,1,if(m.cod_ens='310' and m.num_gra=4,4,m.num_gra+1))) as 'num_gra', m.cod_jor, (m.anno_mat +1) as 'anno_mat'," _
            & "if(bf.ind_beca = 1 OR bf.ind_mbeca = 1,0,1) as 'ind_cup', 0 as 'ind_cheq', 0 as 'ind_pac'," _
            & "IF(bf.ind_beca=1,1,0) AS 'ind_beca', IF(bf.ind_mbeca=1,1,0) AS 'ind_mbeca'," _
            & "0 as 'ind_cont' FROM (SELECT ma.* FROM (SELECT * FROM matriculas m,alu_x_cur axc WHERE anno_mat=" & Anno _
            & " and axc.activo_alu='SI' and m.activo_alu = 'SI' and reserva<>2 and m.ci_alu = axc.ci_alu and m.cod_ens = axc.cod_ens and m.anno_mat = axc.agno and m.num_gra = axc.num_gra and axc.activo_alu = 'SI') as ma LEFT JOIN (SELECT ci_alu, reserva FROM matriculas WHERE anno_mat=" & (Anno + 1) & " and activo_alu='SI') as mn ON ma.ci_alu=mn.ci_alu WHERE mn.ci_alu is null) as m LEFT JOIN (SELECT * FROM matriculas WHERE anno_mat = 2015 AND activo_alu = 'SI' AND (ind_beca = 1 OR ind_mbeca = 1)) as bf " & _
              "ON m.ci_alu=bf.ci_alu AND (bf.anno_mat+1)=(m.anno_mat+1) WHERE m.reserva<>2 AND m.anno_mat=" & Anno & " AND not(m.cod_ens='310' AND m.num_gra=4 AND m.reserva=0) " & _
              "ORDER BY m.cod_ens, m.num_gra;"
    End If
    Set RCL = New ADODB.Recordset
    Set CMD = New ADODB.Command
    
    CMD.ActiveConnection = conn
    CMD.CommandType = adCmdText
    PBar.Min = 0
    PBar.Value = PBar.Min
    PBar.Max = 1
    
    RCL.Open QRY, conn
    
    If RCL.RecordCount > 0 Then
        PBar.Max = RCL.RecordCount
        RCL.MoveFirst
        For i = 1 To RCL.RecordCount
        
            'Verifico que el alumno tenga todo cancelado hasta el mes actual
            'deuda = verifica_deuda_apoderado(RCL("ci_alu"), CInt(Anno), CLng(5000))
            'If deuda Then MsgBox "TRue"
            'protestados = verifica_cheques_protestados(RCL("ci_alu"))
            'If deuda And protestados = False Then
                               
                ' Armo la consulta de insercion de registro de matricula
                QRY = "REPLACE matriculas(ci_alu, activo_alu, num_mat, fec_mat, fec_retiro, reserva,cod_ens,num_gra,cod_jor,anno_mat,ind_cup,ind_cheq, ind_pac,ind_beca,ind_mbeca,ind_cont) " & _
                      "VALUES (" & _
                        "'" & RCL!ci_alu & "'," & _
                        "'" & RCL!activo_alu & "'," & _
                        "'" & RCL!Num_MAT & "'," & _
                        "'" & RCL!fec_mat & "'," & _
                        "'" & RCL!fec_retiro & "'," & _
                        "'" & RCL!Reserva & "'," & _
                        "'" & RCL!Cod_Ens & "'," & _
                        "'" & RCL!Num_Gra & "'," & _
                        "'" & RCL!cod_jor & "'," & _
                        "'" & RCL!anno_mat & "'," & _
                        "'" & RCL!ind_cup & "'," & _
                        "'" & RCL!Ind_Cheq & "'," & _
                        "'" & RCL!ind_pac & "'," & _
                        "'" & RCL!Ind_Beca & "'," & _
                        "'" & RCL!Ind_MBeca & "'," & _
                        "'" & RCL!Ind_Cont & "');"
                CMD.CommandText = QRY
                ' Aquí lo matriculo
                CMD.Execute Affected
            
                ' Aquí le asigno el apoderado al alumno para el año siguiente            '
                QRY = "SELECT * FROM apo_x_alu" _
                    & " where agno = " & (RCL!anno_mat - 1) _
                    & " and ci_alu = '" & RCL!ci_alu & "'"
                REC.Open QRY, conn
                QRY = "replace into apo_x_alu(rut_apo,ci_alu,parent_apo,agno)" _
                & "values('" & REC("rut_apo") & "','" & REC("ci_alu") & "','" _
                & REC("parent_apo") & "'," & (REC("agno") + 1) & ")"
                conn.Execute QRY
                REC.Close
            
                'Log hist_vitacora_alumno
                    Select Case RCL!Cod_Ens
                        Case "996"
                            grado = "PK"
                        Case "010"
                            grado = "K"
                        Case "110"
                            grado = "EB"
                        Case "310"
                            grado = "EM"
                    End Select
                    curso = CStr(RCL!Num_Gra) & "º " & grado
                    accion_realizada = "El alumno fue procesado en " & curso _
                                    & ", para el período " & RCL!anno_mat
                    log = Inserta_Vitacora_Alumno(RCL!ci_alu, accion_realizada)
                'Fin de log
            
                ' Aquí le calculo la mensualidad
                RecalculaCuota_Alu RCL!ci_alu, (Anno + 1), conn
                ' Avanzo la barra
                PBar.Value = i
                Label2.Caption = "Van " & i & " de " & RCL.RecordCount & " registros a procesar."
                DoEvents
                           
            'End If
            ' Siguiente
            RCL.MoveNext
        Next i
    Else
        Label2.Caption = "No existen registros nuevos que procesar."
    End If
    
    QRY = "CREATE TABLE IF NOT EXISTS `fnza_log_proceso_matricula_masiva` (  `FOLIO_REG` bigint(20) unsigned NOT NULL auto_increment,  `LOGIN_USUARIO` varchar(100) NOT NULL default '',  `NOMBRE_USUARIO` varchar(100) NOT NULL default '',  `FEC_PROCESO` datetime NOT NULL default '0000-00-00 00:00:00',  `AGNO_PROCESADO` bigint(20) unsigned NOT NULL default '0',  `NUM_REG_PROCESADOS` bigint(20) unsigned NOT NULL default '0',  PRIMARY KEY  (`FOLIO_REG`)) ENGINE=MyISAM;"
    CMD.CommandText = QRY
    CMD.Execute Affected
    QRY = "INSERT INTO fnza_log_proceso_matricula_masiva(login_usuario,nombre_usuario,fec_proceso,agno_procesado,num_reg_procesados) VALUES('" & Nom_usu_ses & "','" & Nom_com_usu_ses & "',concat(current_date,' ',current_time),'" & (Anno + 1) & "','" & RCL.RecordCount & "')"
    CMD.CommandText = QRY
    CMD.Execute Affected
    RCL.Close
    
    Label2.Caption = Label2.Caption & vbCrLf & "Verificación de cambios criticos"
    QRY = "SELECT * from fnza_detalle_mensualidad WHERE agno=" & (Anno + 1) & " and concepto='ct00' and estado_dtll='cred';"
    RCL.Open QRY, conn
    If RCL.RecordCount > 0 Then
        Set RCR = New ADODB.Recordset
        RCL.MoveFirst
        PBar.Value = PBar.Min
        PBar.Max = RCL.RecordCount
        For i = 1 To RCL.RecordCount
            QRY = "SELECT m.folio_dtll, m.monto_total, m.monto_exen_hno, m.monto_exen_bca, m.monto_exen_desc, m.monto_exen_conv, m.monto_exen_tipo_pago, m.cod_convenio, m.run_alu, m.colegio, m.concepto, m.estado_dtll FROM (SELECT rut_apo FROM (SELECT distinct run_alu from fnza_detalle_mensualidad WHERE agno=" & (Anno + 1) & " and concepto='ct00' and estado_dtll='cred' and run_alu='" & RCL!run_alu & "') as d, apo_x_alu as axa WHERE ci_alu=run_alu) as e, apo_x_alu as aa, fnza_detalle_mensualidad as m WHERE aa.rut_apo=e.rut_apo AND m.run_alu=aa.ci_alu AND m.agno=" & (Anno + 1) & " AND m.concepto='ct00' AND m.monto_exen_hno=0 AND m.monto_tot_cuota>0;"
            RCR.Open QRY, conn
            If RCR.RecordCount > 0 Then
                fijar_montos_detalle conn, RCL!folio_dtll, RCL!Monto_Total, RCL!monto_exen_hno, RCL!monto_exen_bca, (RCL!monto_exen_desc + (RCL!monto_tot_cuota - RCL!monto_abono)), RCL!Monto_Exen_Conv, RCL!cod_convenio, RCL!monto_exen_tipo_pago
                fijar_montos_detalle conn, RCR!folio_dtll, RCR!Monto_Total, RCR!monto_exen_hno, RCR!monto_exen_bca, (RCR!monto_exen_desc + (RCL!monto_abono - RCL!monto_tot_cuota)), RCR!Monto_Exen_Conv, RCR!cod_convenio, RCR!monto_exen_tipo_pago
            End If
            RCR.Close
            RCL.MoveNext
            PBar.Value = i
            PBar.Refresh
        Next i
    End If
    RCL.Close
    Label2.Caption = Label2.Caption & " finalizada."
    Command1.Enabled = True
    EnProc = False
End Sub

Private Sub Form_Load()
    EnProc = False
    Label1.Caption = "Este Proceso genera la información necesaria en el sistema para dar inicio al proceso de matriculas masivas para el próximo año académico." & vbCrLf & vbCrLf & _
                     "El programa requiere hayan sido ingresadas la totalidad de las reservas y las becas de los distintos alumnos a matricular para el año 2006, por lo anterior sugerimos su ejecución solo una vez se encuentren estos datos ingresados al sistema." & vbCrLf & vbCrLf & _
                     "Se solicita completa seguridad y cuidado al activar este programa, debido a que cada vez que se ejecute la información ya generada podría perderse produciendo inconsistencia entre lo impreso previamente y los nuevos datos."
    Label2.Caption = ""
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If (EnProc = True) Then
        Cancel = -1
    End If
End Sub
