VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form Form_promedios_cuartos_medios 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Promedios cuartos medios"
   ClientHeight    =   6000
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   10935
   Icon            =   "Form_promedios_cuartos_medios.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   6000
   ScaleWidth      =   10935
   Begin VB.CommandButton cmd_salir 
      Caption         =   "Salir"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   7320
      Picture         =   "Form_promedios_cuartos_medios.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   14
      Top             =   4920
      Width           =   1575
   End
   Begin VB.CommandButton Command2 
      Caption         =   "Exportar Excel"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   2160
      Picture         =   "Form_promedios_cuartos_medios.frx":2AED8
      Style           =   1  'Graphical
      TabIndex        =   13
      Top             =   4920
      Width           =   1575
   End
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   2655
      Left            =   240
      TabIndex        =   12
      Top             =   2040
      Width           =   10455
      _ExtentX        =   18441
      _ExtentY        =   4683
      _Version        =   393216
   End
   Begin VB.CommandButton BtnGenRep 
      Caption         =   "Cargar Promedios"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   8280
      TabIndex        =   9
      Top             =   600
      Width           =   2295
   End
   Begin VB.ComboBox Cmd_Per 
      Enabled         =   0   'False
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   330
      Left            =   5760
      Style           =   2  'Dropdown List
      TabIndex        =   8
      Top             =   960
      Width           =   2055
   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            =   5760
      TabIndex        =   7
      Text            =   "Combo1"
      Top             =   480
      Width           =   2055
   End
   Begin VB.Frame Frame1 
      Caption         =   "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          =   1575
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   4455
      Begin VB.ComboBox Cmb_Let_Cur 
         Enabled         =   0   'False
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   1920
         Style           =   2  'Dropdown List
         TabIndex        =   3
         Top             =   1080
         Width           =   615
      End
      Begin VB.ComboBox Cmb_Grado 
         Enabled         =   0   'False
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   1920
         Style           =   2  'Dropdown List
         TabIndex        =   2
         Top             =   720
         Width           =   615
      End
      Begin VB.ComboBox Cmb_Cod_ens 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   330
         Left            =   1920
         Style           =   2  'Dropdown List
         TabIndex        =   1
         Top             =   360
         Width           =   2475
      End
      Begin VB.Label Tipo 
         Caption         =   "Tipo de enseñanza"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   195
         Left            =   360
         TabIndex        =   6
         Top             =   360
         Width           =   1455
      End
      Begin VB.Label Label1 
         Caption         =   "Grado"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   195
         Left            =   360
         TabIndex        =   5
         Top             =   720
         Width           =   495
      End
      Begin VB.Label Label2 
         Caption         =   "Letra Curso"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   195
         Left            =   360
         TabIndex        =   4
         Top             =   1080
         Width           =   1095
      End
   End
   Begin VB.Label Label3 
      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            =   4920
      TabIndex        =   11
      Top             =   480
      Width           =   735
   End
   Begin VB.Label Label4 
      Caption         =   "Periodo"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   4920
      TabIndex        =   10
      Top             =   960
      Width           =   735
   End
End
Attribute VB_Name = "Form_promedios_cuartos_medios"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
''''Private Sub Cmb_Cod_ens_Change()
''''If Len(Me.Cmb_Cod_ens) <> 0 Then
'''' CodModalidad = DevuelveCampo("codigos_ensenanza", "", "cod_ens", "des_ens = '" & Me.Cmb_Cod_ens.Text & "'", conn)
'''' Cmb_Grado.Enabled = True
'''' Cmb_Grado.Clear
''''
''''rut = val(DevuelveCampo("usuarios", "", "rut_usu", "aut_nombre_usuario = '" & Nom_usu_ses & "'", conn))
''''
''''If CodPerfil = "#UTP#" Or CodPerfil = "#ADM#" Or CodPerfil = "#GES03#" Or CodPerfil = "#PJE#" Then
''''    If Len(Me.Cmb_Cod_ens.Text) <> 0 Then
''''        nivel = DevuelveCampo("codigos_ensenanza", "des_ens", "num_gra_max", "des_ens = " & "'" & Me.Cmb_Cod_ens.Text & "'", conn)
''''        Me.Cmb_Grado.Enabled = True
''''        Me.Cmb_Grado.Clear
''''    For t = 1 To val(nivel)
''''        Me.Cmb_Grado.AddItem t
''''    Next t
'''' End If
''''
''''Else
''''    Call LlenaCombo(Form_promedios_cuartos_medios.Cmb_Grado, "asi_x_cur_prof AP", "DISTINCT AP.NUM_GRA", "where AP.cod_ens = '" & CodModalidad & "' and MID(rut_prof,1,(LENGTH(ap.rut_prof)-2)) ='" & rut & "'", conn)
''''End If
''''
''''
'''' Cmb_Let_Cur.Clear
''''
''''End If
''''Me.MSFlexGrid1.Clear
''''End Sub

Private Sub BrnCanPanCur_Click()
    Unload Me
End Sub

Private Sub BtnGenRep_Click()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    Dim var_sql As String
    Dim p1 As Integer
    Dim p2 As Integer
    Dim nf As Double
    Dim p1_2 As String
    Dim p2_2 As String
    Dim nf_2 As String
    Dim I As Integer
    Dim Cod_Ens As String
    Dim Num_Gra As String
    Dim Let_Cur As String
    
    var_sql = "DROP TABLE IF EXISTS temp_cuartos;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_terceros;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_segundos;"
    conn.Execute var_sql
    var_sql = "DROP TABLE IF EXISTS temp_primeros;"
    conn.Execute var_sql
    
    '' Modalidad
    CodModalidad = DevuelveCampo("codigos_ensenanza", "", "cod_ens", "des_ens = '" & Me.Cmb_Cod_ens.Text & "'", conn)
    
    '' Cuartos medios
    var_sql = "CREATE TABLE temp_cuartos TYPE = MYISAM" _
            & " SELECT c.agno AS agno25,CONCAT(c.num_gra,'° ',let_cur,' ',des_ens_abrev) AS curso25,p.ci_alu AS Rut_Alu,CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) AS Alumno," _
            & "MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3) AS Prom_Prim_Sem_2025," _
            & "MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3) AS Prom_Seg_Sem_2025," _
            & "IF(SUM(p1) > 0 AND SUM(p2) > 0,MID(((MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3) + MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3))/2)+0.05,1,3)," _
            & "IF(SUM(p2) = '(NULL)',MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3),MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3))) AS Final_2025" _
            & ",MID((SUM(IF(nf <> 0.0,nf,0))/SUM(IF(nf <> 0.0,1,0)))+0.05,1,3) as Prom_Final_2025 FROM promedios p,alu_x_cur c,postulantes pos,codigos_ensenanza ce" _
            & " WHERE c.activo_alu = 'SI' AND p.ci_alu = c.ci_alu AND p.agno = c.agno AND p.cod_ens = c.cod_ens AND p.num_gra = c.num_gra AND p.letra_curso = c.let_cur AND c.ci_alu = pos.ci_alu AND c.cod_ens = ce.cod_ens AND" _
            & " p.agno = " & cb_agno.Text & " AND p.cod_ens = '" & CodModalidad & "' AND p.num_gra = " & Me.Cmb_Grado & " AND p.letra_curso = '" & Me.Cmb_Let_Cur & "' AND cod_asi <> 13 AND cod_asi <> 22 AND tipo_eva = 0" _
            & " GROUP BY p.ci_alu;"
    conn.Execute var_sql
    
    '' Terceros medios
    var_sql = "CREATE TABLE temp_terceros TYPE = MYISAM" _
            & " SELECT c.agno AS agno24,CONCAT(c.num_gra,'° ',let_cur,' ',des_ens_abrev) AS curso24,p.ci_alu," _
            & "MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3) AS Prom_Prim_Sem_2024," _
            & "MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3) AS Prom_Seg_Sem_2024," _
            & "IF(SUM(p1) > 0 AND SUM(p2) > 0,MID(((MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3) + MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))+0.05),1,3))/2),1,3)," _
            & "IF(SUM(p2) = '(NULL)',MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3),MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3))) AS Final_2024" _
            & ",MID((SUM(IF(nf <> 0.0,nf,0))/SUM(IF(nf <> 0.0,1,0)))+0.05,1,3) as Prom_Final_2024 FROM promedios p,alu_x_cur c,codigos_ensenanza ce" _
            & " WHERE p.ci_alu = c.ci_alu AND p.agno = c.agno AND p.cod_ens = c.cod_ens AND p.num_gra = c.num_gra AND p.letra_curso = c.let_cur AND c.cod_ens = ce.cod_ens AND" _
            & " p.agno = " & (cb_agno.Text - 1) & " AND c.ci_alu IN (SELECT rut_alu FROM temp_cuartos) AND cod_asi <> 13 AND cod_asi <> 22 AND tipo_eva = 0" _
            & " GROUP BY ci_alu;"
    conn.Execute var_sql
    
    ' Calculo ciencias naturales
    var_sql = "DROP TABLE IF EXISTS temp_promedios_segundos;"
    conn.Execute var_sql
    var_sql = "CREATE TABLE temp_promedios_segundos TYPE = MYISAM SELECT * FROM promedios WHERE agno = " & (cb_agno.Text - 2) & " AND NOT (cod_sec = '0011' AND (cod_asi = 3 OR cod_asi = 7 OR cod_asi = 8 OR cod_asi = 6)) AND cod_asi <> 13 AND cod_asi <> 22 AND cod_asi <> '' AND ci_alu IN (SELECT rut_alu FROM temp_cuartos)"
    conn.Execute var_sql
        
    var_sql = "SELECT * FROM notas WHERE agno = " & (cb_agno.Text - 2) & " AND cod_sec = '0011' AND (cod_asi = 3 OR cod_asi = 7 OR cod_asi = 8) and ci_alu in (SELECT rut_alu FROM temp_cuartos) group by ci_alu"
    '' agno23,curso23,ci_alu,Prom_Prim_Sem_2023,Prom_Seg_Sem_2023,Prom_Final_2023
    '' insert into `temp_segundos` (`agno23`, `curso23`, `ci_alu`, `Prom_Prim_Sem_2023`, `Prom_Seg_Sem_2023`, `Prom_Final_2023`) values('2023','2° C Medio','22056872-5','4.7','4.6','4.7');
    objRS.Open var_sql, conn
    While Not objRS.EOF
        var_sql = "SELECT * FROM notas WHERE agno = " & (cb_agno.Text - 2) & " and cod_sec = '0011' AND (cod_asi = 3 OR cod_asi = 7 OR cod_asi = 8) AND ci_alu = '" & objRS("ci_alu") & "' and ci_alu in (SELECT rut_alu FROM temp_cuartos) ORDER BY num_per"
        rec.Open var_sql, conn
        p1 = 0
        p2 = 0
        C1 = 0
        C2 = 0
        While Not rec.EOF
            Cod_Ens = rec("cod_ens")
            Num_Gra = rec("num_gra")
            Let_Cur = rec("letra_curso")
            
            If rec("num_per") = 0 Then
                For I = 1 To 10
                    If val(Mid(rec("nota"), (I * 2) - 1, 2)) > 0 Then
                        p1 = p1 + Mid(rec("nota"), (I * 2) - 1, 2)
                        C1 = C1 + 1
                    End If
                Next I
            End If
            If rec("num_per") = 1 Then
                For I = 1 To 10
                    If val(Mid(rec("nota"), (I * 2) - 1, 2)) > 0 Then
                        p2 = p2 + Mid(rec("nota"), (I * 2) - 1, 2)
                        C2 = C2 + 1
                    End If
                Next I
            End If
            
            rec.MoveNext
        Wend
        rec.Close
        'p1 = p1 / C1
        'p2 = p2 / C2
        If C1 > 0 Then
            p1 = p1 / C1
        End If
        If C2 > 0 Then
            p2 = p2 / C2
        End If
        If C1 > 0 And C2 > 0 Then
            nf = (p1 + p2) / 2
        Else
            If C1 > 0 And C2 = 0 Then
                nf = p1
            Else
                nf = p2
            End If
        End If
        
        p1_2 = IIf(p1 > 0, (p1 / 10), 0)
        p2_2 = IIf(p2 > 0, (p2 / 10), 0)
        nf_2 = IIf(nf > 0, (nf / 10), 0)
        
        var_sql = "insert into `temp_promedios_segundos` (`ci_alu`, `cod_ens`, `num_gra`, `letra_curso`, `cod_sec`, `cod_asi`, `p1`, `p2`, `p3`, `pg`, `ne`, `nf`, `agno`, `tipo_eva`)" _
                & " values('" & objRS("ci_alu") & "','" & Cod_Ens & "','" & Num_Gra & "','" & Let_Cur & "','0011','06','" & Replace(p1_2, ",", ".") & "','" & Replace(p2_2, ",", ".") & "',NULL,'','0.0','" & Replace(nf_2, ",", ".") & "','" & objRS("agno") & "','0');"
        conn.Execute var_sql
        ' 50,1
        ' 53,85
        objRS.MoveNext
    Wend
    objRS.Close
    
    
     '' Segundos medios
    var_sql = "CREATE TABLE temp_segundos TYPE = MYISAM" _
            & " SELECT c.agno AS agno23,CONCAT(c.num_gra,'° ',let_cur,' ',des_ens_abrev) AS curso23,p.ci_alu," _
            & "MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3) AS Prom_Prim_Sem_2023," _
            & "MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3) AS Prom_Seg_Sem_2023," _
            & "IF(SUM(p1) > 0 AND SUM(p2) > 0,MID(((MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3) + MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3))/2)+0.05,1,3)," _
            & "IF(SUM(p2) = '(NULL)',MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3),MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3))) AS Final_2023" _
            & ",MID((SUM(IF(nf <> 0.0,nf,0))/SUM(IF(nf <> 0.0,1,0)))+0.05,1,3) Prom_Final_2023 FROM temp_promedios_segundos p,alu_x_cur c,codigos_ensenanza ce" _
            & " WHERE p.ci_alu = c.ci_alu AND p.agno = c.agno AND p.cod_ens = c.cod_ens AND p.num_gra = c.num_gra AND p.letra_curso = c.let_cur AND c.cod_ens = ce.cod_ens AND" _
            & " p.agno = " & (cb_agno.Text - 2) & " AND c.ci_alu IN (SELECT rut_alu FROM temp_cuartos) AND cod_asi <> 13 AND cod_asi <> 22 AND tipo_eva = 0" _
            & " GROUP BY ci_alu;"
    conn.Execute var_sql
    
    
    ' Calculo ciencias naturales
    var_sql = "DROP TABLE IF EXISTS temp_promedios_primeros;"
    conn.Execute var_sql
    var_sql = "CREATE TABLE temp_promedios_primeros TYPE = MYISAM SELECT * FROM promedios WHERE agno = " & (cb_agno.Text - 3) & " AND NOT (cod_sec = '0011' AND (cod_asi = 3 OR cod_asi = 7 OR cod_asi = 8 OR cod_asi = 6)) AND cod_asi <> 13 AND cod_asi <> 22 AND cod_asi <> '' AND ci_alu IN (SELECT rut_alu FROM temp_cuartos)"
    conn.Execute var_sql
        
    var_sql = "SELECT * FROM notas WHERE agno = " & (cb_agno.Text - 3) & " AND cod_sec = '0011' AND (cod_asi = 3 OR cod_asi = 7 OR cod_asi = 8) and ci_alu in (SELECT rut_alu FROM temp_cuartos) group by ci_alu"
    '' agno23,curso23,ci_alu,Prom_Prim_Sem_2023,Prom_Seg_Sem_2023,Prom_Final_2023
    '' insert into `temp_segundos` (`agno23`, `curso23`, `ci_alu`, `Prom_Prim_Sem_2023`, `Prom_Seg_Sem_2023`, `Prom_Final_2023`) values('2023','2° C Medio','22056872-5','4.7','4.6','4.7');
    objRS.Open var_sql, conn
    While Not objRS.EOF
        var_sql = "SELECT * FROM notas WHERE agno = " & (cb_agno.Text - 3) & " and cod_sec = '0011' AND (cod_asi = 3 OR cod_asi = 7 OR cod_asi = 8) AND ci_alu = '" & objRS("ci_alu") & "' and ci_alu in (SELECT rut_alu FROM temp_cuartos) ORDER BY num_per"
        rec.Open var_sql, conn
        p1 = 0
        p2 = 0
        C1 = 0
        C2 = 0
        While Not rec.EOF
            Cod_Ens = rec("cod_ens")
            Num_Gra = rec("num_gra")
            Let_Cur = rec("letra_curso")
            
            If rec("num_per") = 0 Then
                For I = 1 To 10
                    If val(Mid(rec("nota"), (I * 2) - 1, 2)) > 0 Then
                        p1 = p1 + Mid(rec("nota"), (I * 2) - 1, 2)
                        C1 = C1 + 1
                    End If
                Next I
            End If
            If rec("num_per") = 1 Then
                For I = 1 To 10
                    If val(Mid(rec("nota"), (I * 2) - 1, 2)) > 0 Then
                        p2 = p2 + Mid(rec("nota"), (I * 2) - 1, 2)
                        C2 = C2 + 1
                    End If
                Next I
            End If
            
            rec.MoveNext
        Wend
        rec.Close
        'p1 = p1 / C1
        'p2 = p2 / C2
        If C1 > 0 Then
            p1 = p1 / C1
        End If
        If C2 > 0 Then
            p2 = p2 / C2
        End If
        If C1 > 0 And C2 > 0 Then
            nf = (p1 + p2) / 2
        Else
            If C1 > 0 And C2 = 0 Then
                nf = p1
            Else
                nf = p2
            End If
        End If
        
        p1_2 = IIf(p1 > 0, (p1 / 10), 0)
        p2_2 = IIf(p2 > 0, (p2 / 10), 0)
        nf_2 = IIf(nf > 0, (nf / 10), 0)
        
        var_sql = "insert into `temp_promedios_primeros` (`ci_alu`, `cod_ens`, `num_gra`, `letra_curso`, `cod_sec`, `cod_asi`, `p1`, `p2`, `p3`, `pg`, `ne`, `nf`, `agno`, `tipo_eva`)" _
                & " values('" & objRS("ci_alu") & "','" & Cod_Ens & "','" & Num_Gra & "','" & Let_Cur & "','0011','06','" & Replace(p1_2, ",", ".") & "','" & Replace(p2_2, ",", ".") & "',NULL,'','0.0','" & Replace(nf_2, ",", ".") & "','" & objRS("agno") & "','0');"
        conn.Execute var_sql
        ' 50,1
        ' 53,85
        objRS.MoveNext
    Wend
    objRS.Close
    
    
    '' Primeros medios
    var_sql = "CREATE TABLE temp_primeros TYPE = MYISAM" _
            & " SELECT c.agno AS agno22,CONCAT(c.num_gra,'° ',let_cur,' ',des_ens_abrev) AS curso22,p.ci_alu," _
            & "MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3) AS Prom_Prim_Sem_2022," _
            & "MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3) AS Prom_Seg_Sem_2022," _
            & "IF(SUM(p1) > 0 AND SUM(p2) > 0,MID(((MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3) + MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3))/2)+0.05,1,3)," _
            & "IF(SUM(p2) = '(NULL)',MID((SUM(IF(p1 <> 0.0,p1,0))/SUM(IF(p1 <> 0.0,1,0))),1,3),MID((SUM(IF(p2 <> 0.0,p2,0))/SUM(IF(p2 <> 0.0,1,0))),1,3))) AS Final_2022" _
            & ",MID((SUM(IF(nf <> 0.0,nf,0))/SUM(IF(nf <> 0.0,1,0)))+0.05,1,3) as Prom_Final_2022 FROM temp_promedios_primeros p,alu_x_cur c,codigos_ensenanza ce" _
            & " WHERE p.ci_alu = c.ci_alu AND p.agno = c.agno AND p.cod_ens = c.cod_ens AND p.num_gra = c.num_gra AND p.letra_curso = c.let_cur AND c.cod_ens = ce.cod_ens AND" _
            & " p.agno = " & (cb_agno.Text - 3) & " AND c.ci_alu IN (SELECT rut_alu FROM temp_cuartos) AND cod_asi <> 13 AND cod_asi <> 22 AND tipo_eva = 0" _
            & " GROUP BY ci_alu;"
    conn.Execute var_sql
    
    If Cmd_Per.Text = "PRIMER SEMESTRE" Then
    var_sql = "SELECT rut_alu AS Rut_Alu,alumno AS Alumno,agno25 AS Año,curso25 AS Curso,prom_prim_sem_2025 AS Prom_Prim_Sem_2025,agno24 AS Año,curso24 AS Curso,prom_prim_sem_2024 AS Prom_Prim_Sem_2024," _
            & "agno23 As agno, curso23 As curso, Prom_Prim_Sem_2023 As Prom_Prim_Sem_2023, agno22 As agno, curso22 As curso, Prom_Prim_Sem_2022 As Prom_Prim_Sem_2022" _
            & " FROM (SELECT * FROM (SELECT * FROM temp_cuartos c LEFT JOIN temp_terceros t ON c.rut_alu = t.ci_alu) t LEFT JOIN temp_segundos s ON t.rut_alu = s.ci_alu) tt LEFT JOIN temp_primeros p ON tt.rut_alu = p.ci_alu"
    End If
    
    If Cmd_Per.Text = "SEGUNDO SEMESTRE" Then
    var_sql = "SELECT rut_alu AS Rut_Alu,alumno AS Alumno,agno25 AS Año,curso25 AS Curso,prom_seg_sem_2025 AS Prom_Seg_Sem_2025,agno24 AS Año,curso24 AS Curso,prom_seg_sem_2024 AS Prom_Seg_Sem_2024," _
            & "agno23 As agno, curso23 As curso, Prom_Seg_Sem_2023 As Prom_Seg_Sem_2023, agno22 As agno, curso22 As curso, Prom_Seg_Sem_2022 As Prom_Seg_Sem_2022" _
            & " FROM (SELECT * FROM (SELECT * FROM temp_cuartos c LEFT JOIN temp_terceros t ON c.rut_alu = t.ci_alu) t LEFT JOIN temp_segundos s ON t.rut_alu = s.ci_alu) tt LEFT JOIN temp_primeros p ON tt.rut_alu = p.ci_alu"
    End If
    
    If Cmd_Per.Text = "ANUAL" Then
    var_sql = "SELECT rut_alu AS Rut_Alu,alumno AS Alumno,agno25 AS Año,curso25 AS Curso,prom_final_2025 AS Prom_Final_2025,agno24 AS Año,curso24 AS Curso,prom_final_2024 AS Prom_Final_2024," _
            & "agno23 As agno, curso23 As curso, Prom_Final_2023 As Prom_Final_2023, agno22 As agno, curso22 As curso, Prom_Final_2022 As Prom_Final_2022" _
            & " FROM (SELECT * FROM (SELECT * FROM temp_cuartos c LEFT JOIN temp_terceros t ON c.rut_alu = t.ci_alu) t LEFT JOIN temp_segundos s ON t.rut_alu = s.ci_alu) tt LEFT JOIN temp_primeros p ON tt.rut_alu = p.ci_alu"
    End If
    
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
       ' msflex_alumnos.ColSel = 5
    Else
        'MsgBox "Esta mensualidad aún no ha vencido", vbInformation
    End If
    objRS.Close
    MSFlexGrid1.Refresh
    
    
End Sub

Private Sub Cmb_Cod_ens_Click()
If Len(Me.Cmb_Cod_ens) <> 0 Then
 CodModalidad = DevuelveCampo("codigos_ensenanza", "", "cod_ens", "des_ens = '" & Me.Cmb_Cod_ens.Text & "'", conn)
 Cmb_Grado.Enabled = True
 Cmb_Grado.Clear

rut = val(DevuelveCampo("usuarios", "", "rut_usu", "aut_nombre_usuario = '" & Nom_usu_ses & "'", conn))

If CodPerfil = "#UTP#" Or CodPerfil = "#ADM#" Or CodPerfil = "#GES03#" Or CodPerfil = "#PJE#" Then
    If Len(Me.Cmb_Cod_ens.Text) <> 0 Then
        nivel = DevuelveCampo("codigos_ensenanza", "des_ens", "num_gra_max", "des_ens = " & "'" & Me.Cmb_Cod_ens.Text & "'", conn)
        Me.Cmb_Grado.Enabled = True
        Me.Cmb_Grado.Clear
    For t = 4 To val(nivel)
        Me.Cmb_Grado.AddItem t
    Next t
 End If

Else
    Call LlenaCombo(Form_promedios_cuartos_medios.Cmb_Grado, "asi_x_cur_prof AP", "DISTINCT AP.NUM_GRA", "where AP.cod_ens = '" & CodModalidad & "' and MID(rut_prof,1,(LENGTH(ap.rut_prof)-2)) ='" & rut & "'", conn)
End If

 
 Cmb_Let_Cur.Clear

End If
Me.MSFlexGrid1.Clear
End Sub

Private Sub Cmb_Grado_Change()
Ngrado = Me.Cmb_Grado.Text
Cmb_Let_Cur.Clear

rut = val(DevuelveCampo("usuarios", "", "rut_usu", "aut_nombre_usuario = '" & Nom_usu_ses & "'", conn))

If CodPerfil = "#UTP#" Or CodPerfil = "#ADM#" Or CodPerfil = "#GES03#" Or CodPerfil = "#PJE#" Then
    Call LlenaCombo(Me.Cmb_Let_Cur, "cursos", "distinct let_cur", "where cod_ens = '" & CodModalidad & "' and agno=" & cb_agno.Text & " and num_gra = '" & Ngrado & "'", conn)
Else
    'Call llenacombo(Form_Notas.Cmb_Cod_ens, "codigos_ensenanza ce, asi_x_cur_prof ap", "distinct ce.des_ens", "where ce.cod_ens = ap.cod_ens and ap.rut_prof ='" & Rutglobal & "'")
    Call LlenaCombo(Cmb_Let_Cur, "asi_x_cur_prof ap", "distinct ap.let_cur", "where ap.cod_ens = '" & CodModalidad & "' and ap.num_gra = '" & Ngrado & "' and ap.agno=" & cb_agno.Text & " and MID(rut_prof,1,(LENGTH(ap.rut_prof)-2)) ='" & rut & "'", conn)
End If
Me.Cmb_Let_Cur.Enabled = True

'btn_carga_asignaturas_Click

'Cmb_Asi.Clear
'If Me.Cmb_Sector <> "" Then
'    Call llenacombo(Form_InfoNotas.Cmb_Asi, "subsectores ss, sectores s", "NOM_ASI", "where ss.cod_sec = s.cod_sec and ss.cod_ens ='" & CodModalidad & "' and num_gra ='" & Ngrado & "' and ss.cod_sec ='" & CodSec & "'")
'End If
Me.MSFlexGrid1.Clear
End Sub

Private Sub Cmb_Grado_Click()
Ngrado = Me.Cmb_Grado.Text
Cmb_Let_Cur.Clear
CodModalidad = DevuelveCampo("codigos_ensenanza", "", "cod_ens", "des_ens = '" & Me.Cmb_Cod_ens.Text & "'", conn)
rut = val(DevuelveCampo("usuarios", "", "rut_usu", "aut_nombre_usuario = '" & Nom_usu_ses & "'", conn))

If CodPerfil = "#UTP#" Or CodPerfil = "#ADM#" Or CodPerfil = "#GES03#" Or CodPerfil = "#PJE#" Then
    Call LlenaCombo(Me.Cmb_Let_Cur, "cursos", "distinct let_cur", "where cod_ens = '" & CodModalidad & "' and agno=" & cb_agno.Text & " and num_gra = '" & Ngrado & "'", conn)
Else
    'Call llenacombo(Form_Notas.Cmb_Cod_ens, "codigos_ensenanza ce, asi_x_cur_prof ap", "distinct ce.des_ens", "where ce.cod_ens = ap.cod_ens and ap.rut_prof ='" & Rutglobal & "'")
    Call LlenaCombo(Cmb_Let_Cur, "asi_x_cur_prof ap", "distinct ap.let_cur", "where ap.cod_ens = '" & CodModalidad & "' and ap.num_gra = '" & Ngrado & "' and ap.agno=" & cb_agno.Text & " and MID(rut_prof,1,(LENGTH(ap.rut_prof)-2)) ='" & rut & "'", conn)
End If
Me.Cmb_Let_Cur.Enabled = True

'btn_carga_asignaturas_Click

'Cmb_Asi.Clear
'If Me.Cmb_Sector <> "" Then
'    Call llenacombo(Form_InfoNotas.Cmb_Asi, "subsectores ss, sectores s", "NOM_ASI", "where ss.cod_sec = s.cod_sec and ss.cod_ens ='" & CodModalidad & "' and num_gra ='" & Ngrado & "' and ss.cod_sec ='" & CodSec & "'")
'End If
Me.MSFlexGrid1.Clear
End Sub



Private Sub Cmb_Grado_DblClick()
Ngrado = Me.Cmb_Grado.Text
Cmb_Let_Cur.Clear

rut = val(DevuelveCampo("usuarios", "", "rut_usu", "aut_nombre_usuario = '" & Nom_usu_ses & "'", conn))

If CodPerfil = "#UTP#" Or CodPerfil = "#ADM#" Or CodPerfil = "#GES03#" Or CodPerfil = "#PJE#" Then
    Call LlenaCombo(Me.Cmb_Let_Cur, "cursos", "distinct let_cur", "where cod_ens = '" & CodModalidad & "' and agno=" & cb_agno.Text & " and num_gra = '" & Ngrado & "'", conn)
Else
    'Call llenacombo(Form_Notas.Cmb_Cod_ens, "codigos_ensenanza ce, asi_x_cur_prof ap", "distinct ce.des_ens", "where ce.cod_ens = ap.cod_ens and ap.rut_prof ='" & Rutglobal & "'")
    Call LlenaCombo(Cmb_Let_Cur, "asi_x_cur_prof ap", "distinct ap.let_cur", "where ap.cod_ens = '" & CodModalidad & "' and ap.num_gra = '" & Ngrado & "' and ap.agno=" & cb_agno.Text & " and MID(rut_prof,1,(LENGTH(ap.rut_prof)-2)) ='" & rut & "'", conn)
End If
Me.Cmb_Let_Cur.Enabled = True

'btn_carga_asignaturas_Click

'Cmb_Asi.Clear
'If Me.Cmb_Sector <> "" Then
'    Call llenacombo(Form_InfoNotas.Cmb_Asi, "subsectores ss, sectores s", "NOM_ASI", "where ss.cod_sec = s.cod_sec and ss.cod_ens ='" & CodModalidad & "' and num_gra ='" & Ngrado & "' and ss.cod_sec ='" & CodSec & "'")
'End If
Me.MSFlexGrid1.Clear
End Sub

Private Sub Cmb_let_cur_Click()
Cmd_Per.Enabled = True
Cmd_Per.ListIndex = 0
MSFlexGrid1.Clear
'Cmd_Per_Click
End Sub

Private Sub cmd_Salir_Click()
 Unload Me
End Sub

Private Sub Command2_Click()
    Dim ApExcel As Variant
    Set ApExcel = CreateObject("Excel.application")
    ApExcel.Workbooks.Add
          
    For I = 1 To Me.MSFlexGrid1.Cols - 1
        ApExcel.Cells(1, I).Font.Size = 12
        ApExcel.Cells(1, I).Font.Bold = True
        ApExcel.Cells(1, I).Formula = Me.MSFlexGrid1.TextMatrix(0, I)
    Next I
    'pgBar.Min = 1
    'pgBar.Max = MSFlexGrid1.Rows
    'pgBar.Value = pgBar.Min
    For I = 1 To MSFlexGrid1.Rows - 1

         For j = 1 To MSFlexGrid1.Cols - 1
            ApExcel.Cells((I + 1), j).Formula = CStr(IIf(MSFlexGrid1.TextMatrix(I, j) = "Null", "", MSFlexGrid1.TextMatrix(I, j)))
        
        ' pgBar.Value = I
        Next j
    Next I
            
    ApExcel.Visible = True
    ApExcel.Range("a1:n1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub

Private Sub Form_Load()
CentrarForma Me
llena_combo_anno
'''''Me.OptOptUno.Value = True
'''''Me.OptDesPan.Value = True
'###################### GRILLA #########################
MSFlexGrid1.ColWidth(0) = 4000
'###################### GRILLA #########################

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient

'********* Llena combos ****************
Me.Cmd_Per.Clear
Me.Cmd_Per.AddItem "Seleccione Período"
'Call LlenaCombo(Me.Cmd_Per, "periodos p", "distinct p.des_per, p.num_per", "order by p.num_per", conn)
cole = DevuelveCampo("cfg", "", "valor", "param = 'codbc'", conn)
If cole = "FLO001" And cb_agno = 2021 Then
    Me.Cmd_Per.AddItem "PRIMER TRIMESTRE"
    Me.Cmd_Per.AddItem "SEGUNDO TRIMESTRE"
    Me.Cmd_Per.AddItem "TERCER TRIMESTRE"
Else
    Me.Cmd_Per.AddItem "PRIMER SEMESTRE"
    Me.Cmd_Per.AddItem "SEGUNDO SEMESTRE"
End If
Me.Cmd_Per.AddItem "ANUAL"

rut = val(DevuelveCampo("usuarios", "", "rut_usu", "aut_nombre_usuario = '" & Nom_usu_ses & "'", conn))

If CodPerfil = "#UTP#" Or CodPerfil = "#ADM#" Or CodPerfil = "#GES#" Or CodPerfil = "#PJE#" Then
    Call LlenaCombo(Form_promedios_cuartos_medios.Cmb_Cod_ens, "codigos_ensenanza ce", "distinct ce.des_ens", "where cod_ens = '310' order by ce.ord_ens", conn)
Else
    Call LlenaCombo(Form_promedios_cuartos_medios.Cmb_Cod_ens, "codigos_ensenanza ce, asi_x_cur_prof ap", "distinct ce.des_ens", "where ce.cod_ens = ap.cod_ens and MID(rut_prof,1,(LENGTH(ap.rut_prof)-2)) ='" & rut & "' order by ce.ord_ens", conn)
End If
End Sub

Public Sub llena_combo_anno()
    Dim I As Integer
    For I = 2004 To Year(Date)
        cb_agno.AddItem I
    Next I
    cb_agno.Text = Year(Date)
End Sub

Private Sub Rec2Grid(ByRef rec As ADODB.Recordset, ByRef grid As Object, Optional ByVal Numeracion As Boolean = True)
    grid.Clear
    If rec.RecordCount > 0 Then
        grid.Cols = rec.Fields.count + 1
        grid.Rows = rec.RecordCount + 1
        grid.FixedCols = 1
        grid.FixedRows = 1
        grid.TextMatrix(0, 0) = "Nº"
        grid.ColWidth(0) = IIf(Numeracion, TextWidth(grid.TextMatrix(0, 0)) + 100, 0)
        For I = 1 To rec.Fields.count
            grid.TextMatrix(0, I) = UCase(rec.Fields(I - 1).Name)
            grid.ColWidth(I) = TextWidth(grid.TextMatrix(0, I)) + 100
        Next I
        rec.MoveFirst
        For I = 1 To rec.RecordCount
            'progreso.Value = i
            grid.TextMatrix(I, 0) = I
            grid.ColWidth(0) = IIf(Numeracion, IIf(TextWidth(grid.TextMatrix(I, 0)) + 100 > grid.ColWidth(0), TextWidth(grid.TextMatrix(I, 0)) + 100, grid.ColWidth(0)), 0)
            For j = 1 To rec.Fields.count
                grid.TextMatrix(I, j) = IIf(IsNull(rec(j - 1)), "Null", rec(j - 1))
                grid.ColWidth(j) = IIf(TextWidth(grid.TextMatrix(I, j)) + 100 > grid.ColWidth(j), TextWidth(grid.TextMatrix(I, j)) + 100, grid.ColWidth(j))
            Next j
            rec.MoveNext
        Next I
    Else
        grid.Cols = 2
        grid.Rows = 2
    End If
End Sub
