VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{F39CBE5C-2D1B-11D6-8D4A-0050043A2E60}#2.0#0"; "ProgBarProzent.ocx"
Begin VB.Form FormNumMAt 
   BorderStyle     =   3  'Fixed Dialog
   Caption         =   "Numeros de matrículas"
   ClientHeight    =   4755
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   9870
   Icon            =   "FormNumMAt.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   4755
   ScaleWidth      =   9870
   ShowInTaskbar   =   0   'False
   Begin VB.Frame ventana 
      BorderStyle     =   0  'None
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   2280
      TabIndex        =   12
      Top             =   1680
      Visible         =   0   'False
      Width           =   3135
      Begin VB.Label Mensaje 
         Alignment       =   2  'Center
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   12
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   615
         Left            =   360
         TabIndex        =   13
         Top             =   120
         Width           =   2415
      End
   End
   Begin VB.CommandButton btnComenzar 
      Caption         =   "Comenzar"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   8040
      TabIndex        =   11
      Top             =   4200
      Width           =   1575
   End
   Begin VB.PictureBox Picture1 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   3975
      Left            =   7920
      ScaleHeight     =   3915
      ScaleWidth      =   1755
      TabIndex        =   2
      Top             =   120
      Width           =   1815
      Begin VB.Label lblTotMat 
         Height          =   255
         Left            =   720
         TabIndex        =   6
         Top             =   3480
         Width           =   975
      End
      Begin VB.Label Label1 
         Caption         =   "Total"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   120
         TabIndex        =   5
         Top             =   3480
         Width           =   495
      End
      Begin VB.Label lblMod 
         Alignment       =   2  'Center
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   24
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   735
         Left            =   120
         TabIndex        =   4
         Top             =   240
         Width           =   1575
      End
      Begin VB.Label lblNumMat 
         Alignment       =   2  'Center
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   24
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   435
         Left            =   120
         TabIndex        =   3
         Top             =   2640
         Width           =   1530
      End
   End
   Begin ProgBarProzent.ProgBar ProgBar1 
      Height          =   375
      Left            =   120
      TabIndex        =   1
      Top             =   3720
      Width           =   7695
      _ExtentX        =   13573
      _ExtentY        =   661
      BorderStyle     =   0
   End
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   3615
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   7695
      _ExtentX        =   13573
      _ExtentY        =   6376
      _Version        =   393216
      Cols            =   4
      FixedCols       =   0
      BorderStyle     =   0
      FormatString    =   $"FormNumMAt.frx":2A8B2
      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
   End
   Begin MSComCtl2.DTPicker DTPFecCre 
      Height          =   375
      Left            =   2040
      TabIndex        =   7
      Top             =   4200
      Width           =   1575
      _ExtentX        =   2778
      _ExtentY        =   661
      _Version        =   393216
      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
      Format          =   50528257
      CurrentDate     =   38037
   End
   Begin MSComCtl2.DTPicker DTPFecMatIni 
      Height          =   375
      Left            =   6240
      TabIndex        =   9
      Top             =   4200
      Width           =   1575
      _ExtentX        =   2778
      _ExtentY        =   661
      _Version        =   393216
      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
      Format          =   50528257
      CurrentDate     =   38037
   End
   Begin VB.Label Label3 
      Caption         =   "Fecha de Matricula 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            =   4080
      TabIndex        =   10
      Top             =   4200
      Width           =   2055
   End
   Begin VB.Label Label2 
      Caption         =   "Fecha de Selección de Registros"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   120
      TabIndex        =   8
      Top             =   4200
      Width           =   1695
   End
End
Attribute VB_Name = "FormNumMAt"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub btnComenzar_Click()
On Error GoTo TRAPError:
Me.MSFlexGrid1.Clear
Me.MSFlexGrid1.Rows = 2
Me.MSFlexGrid1.FormatString = "Num. Mat.|Curso     |RUN            | Nombre                                                                                                   "
Dim NumMod As Integer
Dim MatBase As Long
Dim rsNumMat As ADODB.Recordset
Dim rsBaseBus As ADODB.Recordset
Set rsNumMat = New ADODB.Recordset
rsNumMat.CursorLocation = adUseClient

var_sql = "update alu_x_cur set ens = '111' where cod_ens = '111' and agno = " & Anno & ";"
conn.Execute var_sql
var_sql = "update matriculas set ens = '111' where cod_ens = '111' and anno_mat = " & Anno & ";"
conn.Execute var_sql

var_sql = "update alu_x_cur set ens = '010' where cod_ens = '996' and agno = " & Anno & ";"
conn.Execute var_sql
var_sql = "update matriculas set ens = '010' where cod_ens = '996' and anno_mat = " & Anno & ";"
conn.Execute var_sql
var_sql = "update alu_x_cur set ens = cod_ens,gra = num_gra,pre = num_gra where (cod_ens = '010' or cod_ens = '110' or cod_ens = '310') and agno = " & Anno & ";"
conn.Execute var_sql
var_sql = "update matriculas set ens = cod_ens,gra = num_gra,pre = num_gra where (cod_ens = '010' or cod_ens = '110' or cod_ens = '310') and anno_mat = " & Anno & ";"
conn.Execute var_sql



var_sql = "update matriculas set gra = 3,pre = 'PG' where cod_ens = '111' and anno_mat = " & Anno & ";"
conn.Execute var_sql
var_sql = "update matriculas set gra = 4,pre = 'PK' where cod_ens = '996' and anno_mat = " & Anno & ";"
conn.Execute var_sql
var_sql = "update matriculas set gra = 5,pre = 'K' where cod_ens = '010' and anno_mat = " & Anno & ";"
conn.Execute var_sql
var_sql = "update alu_x_cur set gra = 3,pre = 'PG' where cod_ens = '111' and agno = " & Anno & ";"
conn.Execute var_sql
var_sql = "update alu_x_cur set gra = 4,pre = 'PK' where cod_ens = '996' and agno = " & Anno & ";"
conn.Execute var_sql
var_sql = "update alu_x_cur set gra = 5,pre = 'K' where cod_ens = '010' and agno = " & Anno & ";"
conn.Execute var_sql

var_sql = "UPDATE postulantes SET ape_pat_alu_ord = ape_pat_alu,ape_mat_alu_ORD = ape_mat_alu"
conn.Execute var_sql
var_sql = "UPDATE postulantes SET ape_pat_alu_ord = REPLACE(ape_pat_alu_ord,' ','')"
conn.Execute var_sql
var_sql = "UPDATE postulantes SET ape_mat_alu_ord = REPLACE(ape_mat_alu_ord,' ','')"
conn.Execute var_sql

DTPFecMatIni.Enabled = False
DTPFecCre.Enabled = False

NumMod = devuelvecampo("alu_x_cur", "", "count(distinct ens)", "agno='" & Year(Me.DTPFecCre.Value) & "'", conn)
Dim Modalidades() As String
ReDim Modalidades(NumMod)
Set rsBaseBus = New ADODB.Recordset
a = "SELECT distinct ens from alu_x_cur WHERE agno='" & Year(Me.DTPFecCre.Value) & "' order by ens asc"
rsBaseBus.Open a, conn
If Not rsBaseBus.EOF Then
    'Modalidades(0) = "996"
    For i = 0 To NumMod - 1
        Modalidades(i) = rsBaseBus(0)
        rsBaseBus.MoveNext
    Next i
    rsBaseBus.Close
Else
    rsBaseBus.Close
End If

For j = 0 To NumMod - 1
    Me.lblMod = Modalidades(j)
    a = "SELECT m.num_mat, m.ens, concat(ac.ens,' - ', ac.gra, ac.let_cur) curso, m.ci_alu, p.ape_pat_alu,  p.ape_mat_alu, p.nom_alu  FROM matriculas m, alu_x_cur ac, postulantes p WHERE p.ci_alu = m.ci_alu AND  m.ci_alu = ac.ci_alu AND m.ens = ac.ens AND m.anno_mat=ac.agno AND ac.ens ='" & Modalidades(j) & "' AND m.anno_mat='" & Year(Me.DTPFecCre.Value) & "' AND m.fec_mat<='" & Format(Me.DTPFecCre.Value, "yyyy-mm-dd") & "' order by ac.ens, ac.gra, ac.let_cur, ape_pat_alu_ord asc, ape_mat_alu_ord asc, nom_alu asc"
    rsNumMat.Open a, conn
    If rsNumMat.RecordCount > 0 Then
        With ProgBar1
            .MaxValue = rsNumMat.RecordCount
            .Richtung = mDir
            .ProgressStyle = mStyle
            .ShowProzent = 1
            .Zusatztext = "%"
            .ShowProgress 0
        End With
    
        For i = 1 To rsNumMat.RecordCount
            Me.MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 0) = i
            Me.lblNumMat = i
            Me.lblNumMat.Refresh
            Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Rows - 1, 1) = rsNumMat(2)
            Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Rows - 1, 2) = rsNumMat(3)
            Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Rows - 1, 3) = rsNumMat(4) & " " & rsNumMat(5) & " " & rsNumMat(6)
            rsNumMat.MoveNext
            Me.ProgBar1.ShowProgress i
            DoEvents
            Me.MSFlexGrid1.AddItem ""
        Next i
        Me.ProgBar1.ShowProgress Me.ProgBar1.MaxValue
    End If
    rsNumMat.Close
    Me.lblTotMat = Val(Me.lblTotMat) + Val(Me.lblNumMat.Caption)
Next j
Me.MSFlexGrid1.Rows = MSFlexGrid1.Rows - 1

Me.lblMod = "-"
Me.lblNumMat = "-"

'### ACTUALIZA NUM. MAT. EN LA TABL MATRICULAS ####

With ProgBar1
    .MaxValue = Me.MSFlexGrid1.Rows
    .Richtung = mDir
    .ProgressStyle = mStyle
    .ShowProzent = 1
    .Zusatztext = "%"
    .ShowProgress 0
End With
conn.Execute "UPDATE matriculas SET num_mat ='00000' WHERE anno_mat='" & Year(Me.DTPFecCre.Value) & "' AND fec_mat<='" & Format(Me.DTPFecCre.Value, "yyyy-mm-dd") & "'"
conn.Execute "UPDATE matriculas SET fec_mat=date_format(current_date,'%Y-%m-%d') WHERE anno_mat='" & Year(Me.DTPFecCre.Value) & "' AND confirmado='NO';"

For i = 1 To Me.MSFlexGrid1.Rows - 1
    conn.Execute "UPDATE matriculas Set num_mat ='" & Format(Val(Me.MSFlexGrid1.TextMatrix(i, 0)), "00000") & "', fec_mat ='" & Format(Me.DTPFecMatIni.Value, "yyyy-mm-dd") & "'  WHERE ci_alu ='" & Me.MSFlexGrid1.TextMatrix(i, 2) & "' AND anno_mat='" & Year(Me.DTPFecCre.Value) & "' AND fec_mat<='" & Format(Me.DTPFecCre.Value, "yyyy-mm-dd") & "'"
    conn.Execute "UPDATE ALU_X_CUR Set FEC_ING_CURSO ='" & Format(Me.DTPFecMatIni.Value, "yyyy-mm-dd") & "', fec_mat ='" & Format(Me.DTPFecMatIni, "yyyy-mm-dd") & "'  WHERE ci_alu ='" & Me.MSFlexGrid1.TextMatrix(i, 2) & "' AND agno='" & Year(Me.DTPFecCre.Value) & "' and fec_mat<='" & Format(Me.DTPFecCre.Value, "yyyy-mm-dd") & "'"
    Me.ProgBar1.ShowProgress i
    DoEvents
Next i
Me.ProgBar1.ShowProgress Me.ProgBar1.MaxValue

rsBaseBus.Open "SELECT distinct ens from matriculas WHERE anno_mat='" & Year(Me.DTPFecCre.Value) & "'", conn
If rsBaseBus.RecordCount > 0 Then
    rsBaseBus.MoveFirst
    For cod = 1 To rsBaseBus.RecordCount
        rsNumMat.Open "SELECT * FROM matriculas WHERE ens='" & rsBaseBus!Ens & "' and anno_mat='" & Year(Me.DTPFecCre.Value) & "' and fec_mat>'" & Format(Me.DTPFecCre.Value, "yyyy-mm-dd") & "' AND confirmado='SI' ORDER BY fec_mat,num_mat;", conn
        If rsNumMat.RecordCount > 0 Then
            MatBase = Val(RetornaCampo("matriculas", "max(num_mat+0) as mat", "anno_mat='" & Year(Me.DTPFecCre.Value) & "' AND ens='" & rsBaseBus!Ens & "' AND fec_mat='" & Format(Me.DTPFecMatIni.Value, "yyyy-mm-dd") & "'", conn))
            With ProgBar1
                .MaxValue = rsNumMat.RecordCount
                .Richtung = mDir
                .ProgressStyle = mStyle
                .ShowProzent = 1
                .Zusatztext = "%"
                .ShowProgress 0
            End With
            For i = 1 To rsNumMat.RecordCount
                conn.Execute "UPDATE matriculas set num_mat='" & Format((MatBase + i), "00000") & "' WHERE ci_alu='" & rsNumMat!ci_alu & "' AND fec_mat='" & rsNumMat!fec_mat & "' AND anno_mat='" & rsNumMat!anno_mat & "' and activo_alu='" & rsNumMat!activo_alu & "' LIMIT 1;"
                'conn.Execute "UPDATE alu_x_cur set fec_alta='" & Format((MatBase + i), "00000") & "' WHERE ci_alu='" & rsNumMat!ci_alu & "' AND fec_mat='" & rsNumMat!fec_mat & "' AND agno='" & rsNumMat!anno_mat & "' and activo_alu='" & rsNumMat!activo_alu & "' LIMIT 1;"
                rsNumMat.MoveNext
                Me.ProgBar1.ShowProgress i
            Next i
        End If
        rsNumMat.Close
        rsBaseBus.MoveNext
    Next cod
End If

MsgBox "Existen " & RetornaCampo("matriculas", "count(ci_alu) as tot", "num_mat='00000' and anno_mat='" & Year(Me.DTPFecCre.Value) & "' and confirmado='SI'", conn) & " alumnos matriculados sin curso," & vbCrLf & "quienes no tienen número asignado de matricula."

MsgBox "Los números de matrículas han sido actualizados satisfactoriamente"
DTPFecMatIni.Enabled = True
DTPFecCre.Enabled = True
Exit Sub
TRAPError:
    MsgBox Err.Description & vbCrLf & vbCrLf & Err.Source, vbCritical, Err.Number
    DTPFecMatIni.Enabled = True
    DTPFecCre.Enabled = True
    Resume
End Sub

Private Sub Form_Load()
    Me.DTPFecCre.MinDate = CDate("01-02-" & Year(Date))
    Me.DTPFecCre.MaxDate = CDate("31-03-" & Year(Date))
    Me.DTPFecCre.Value = IIf((Date >= CDate("01-02-" & Year(Date))), IIf((Date <= CDate("31-03-" & Year(Date))), Date, CDate("31-03-" & Year(Date))), CDate("01-02-" & Year(Date)))
    Me.DTPFecMatIni.MinDate = CDate("01-02-" & Year(Date))
    Me.DTPFecMatIni.MaxDate = CDate("31-03-" & Year(Date))
    Me.DTPFecMatIni.Value = IIf((Date >= CDate("01-02-" & Year(Date))), IIf((Date <= CDate("31-03-" & Year(Date))), Date, CDate("31-03-" & Year(Date))), CDate("01-02-" & Year(Date)))
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Call menuhabilitado(True)
End Sub


