VERSION 5.00
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
Object = "{0ECD9B60-23AA-11D0-B351-00A0C9055D8E}#6.0#0"; "MSHFLXGD.OCX"
Begin VB.Form Form_Lista_Alerta 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Lista de Alertas"
   ClientHeight    =   8535
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   14820
   Icon            =   "Form_Lista_Alerta.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   8535
   ScaleWidth      =   14820
   Begin VB.Frame Frame1 
      Caption         =   "Listado de Alumnos"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00FF0000&
      Height          =   8295
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   14535
      Begin VB.CommandButton Cerrar 
         Caption         =   "Cerrar"
         Height          =   975
         Left            =   7800
         Picture         =   "Form_Lista_Alerta.frx":2A8B2
         Style           =   1  'Graphical
         TabIndex        =   3
         Top             =   7200
         Width           =   1815
      End
      Begin MSComDlg.CommonDialog CommonDialog1 
         Left            =   6840
         Top             =   7200
         _ExtentX        =   847
         _ExtentY        =   847
         _Version        =   393216
      End
      Begin MSHierarchicalFlexGridLib.MSHFlexGrid MSHFlexGrid1 
         Height          =   6615
         Left            =   120
         TabIndex        =   2
         Top             =   480
         Width           =   14295
         _ExtentX        =   25215
         _ExtentY        =   11668
         _Version        =   393216
         _NumberOfBands  =   1
         _Band(0).Cols   =   2
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Exportar a Excel"
         Height          =   975
         Left            =   4440
         Picture         =   "Form_Lista_Alerta.frx":2AE8B
         Style           =   1  'Graphical
         TabIndex        =   1
         Top             =   7200
         Width           =   1815
      End
   End
End
Attribute VB_Name = "Form_Lista_Alerta"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Cerrar_Click()
Unload Me
End Sub

Private Sub Command1_Click()

Dim linea As String

'Titulo del CommonDialog
CommonDialog1.DialogTitle = "Escriba el nombre del archivo de texto"

'Extension del CommonDialog. Archivo de tipo txt
CommonDialog1.Filter = "Archivos de texto Excel|*.xlsx"

'cont = cont + 1
'If alert = 1 Then
'    CommonDialog1.FileName = "Postulados_sin_Contactar" & "_" & cont & ".xlsx"
'End If
'
'If alert = 2 Then
'    CommonDialog1.FileName = "Postulados_no_Inscritos" & "_" & cont & ".xlsx"
'End If
'
'If alert = 3 Then
'    CommonDialog1.FileName = "Inscritos_no_Postulados" & "_" & cont & ".xlsx"
'End If
'
'If alert = 4 Then
'    CommonDialog1.FileName = "Postulados_sin_Nota" & "_" & cont & ".xlsx"
'End If
'
'If alert = 5 Then
'    CommonDialog1.FileName = "Aprobados_sin_Matricular" & "_" & cont & ".xlsx"
'End If






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

         For j = 1 To MSHFlexGrid1.Cols - 1
            If j = 9 Then
            
            ApExcel.Cells((i + 1), j).Formula = Format(MSHFlexGrid1.TextMatrix(i, j), "yyyy-mmm-dd")
           Else
            ApExcel.Cells((i + 1), j).Formula = MSHFlexGrid1.TextMatrix(i, j)
           End If
            
       ' pgbar.Value = i
        ' ApExcel.Columns.AutoFit
        Next j
        'ApExcel.ColumnWidth = 1
        'Worksheets("Sheet1").Range("A1:E1").Columns.AutoFit
'        With Worksheets("Sheet1").Columns("A")
'        .ColumnWidth = 500
'        End With
        
         
       ' ApExcel.Cells(1, 1).ColumnWidth = 500

    Next i
            
    ApExcel.Visible = True
    'ApExcel.Range("a1:o1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing







'''''''CommonDialog1.ShowSave
'''''''
'''''''
'''''''
''''''''if CommonDialog1.vbOkCance
'''''''
'''''''If (Exportar_Excel(CommonDialog1.FileName, MSHFlexGrid1)) Then
'''''''            MsgBox " Datos exportados en " & CommonDialog1.FileName
'''''''        End If


   
End Sub

Public Function Exportar_Excel(sOutputPath As String, itGrid As Object) As Boolean

On Error GoTo Error_Handler

Dim o_Excel As Object
Dim o_Libro As Object
Dim o_Hoja As Object
Dim Fila As Long
Dim Columna As Long

' -- Crea el objeto Excel, el objeto workBook y el objeto sheet
Set o_Excel = CreateObject("Excel.Application")
Set o_Libro = o_Excel.Workbooks.Add
Set o_Hoja = o_Libro.Worksheets.Add

' -- Bucle para Exportar los datos
    With itGrid
        For Fila = 1 To .Rows - 1
            For Columna = 0 To .Cols - 1
           
            o_Hoja.Cells(Fila, Columna + 1).Value = CStr(.TextMatrix(Fila, Columna))
            Next
        Next
    End With
    
o_Libro.Close True, sOutputPath

' -- Cerrar Excel
o_Excel.Quit

' -- Terminar instancias
Call ReleaseObjects(o_Excel, o_Libro, o_Hoja)
Exportar_Excel = True
Exit Function

' -- Controlador de Errores
Error_Handler:
' -- Cierra la hoja y el la aplicación Excel
If Not o_Libro Is Nothing Then: o_Libro.Close False
If Not o_Excel Is Nothing Then: o_Excel.Quit
Call ReleaseObjects(o_Excel, o_Libro, o_Hoja)
If Err.Number <> 1004 Then MsgBox Err.Description, vbCritical
End Function
' -------------------------------------------------------------------
' \ -- Eliminar objetos para liberar recursos
' -------------------------------------------------------------------
Private Sub ReleaseObjects(o_Excel As Object, o_Libro As Object, o_Hoja As Object)
If Not o_Excel Is Nothing Then Set o_Excel = Nothing
If Not o_Libro Is Nothing Then Set o_Libro = Nothing
If Not o_Hoja Is Nothing Then Set o_Hoja = Nothing
End Sub

Private Sub Form_Load()
Dim var_sql, rut
Dim objRS As ADODB.Recordset
Set objRS = New ADODB.Recordset


'Creo tabla con fecha en orden correcto y con ultimo comentario
var_sql = "DROP TABLE IF EXISTS temp_postulante_web_seguimiento;"
conn.Execute var_sql

var_sql = "DROP TABLE IF EXISTS temp_postulante_web_seguimiento_2;"
conn.Execute var_sql

var_sql = "CREATE TABLE temp_postulante_web_seguimiento TYPE = MYISAM" _
        & " SELECT rut_pos,comentario,usuario,CONCAT(MID(fecha,7,4),'-',MID(fecha,4,2),'-',MID(fecha,1,2),' ',hora) AS fecha_hora,CONCAT(MID(fecha,7,4),'-',MID(fecha,4,2),'-',MID(fecha,1,2)) AS fecha,hora,renuncia" _
        & " From postulante_web_seguimiento" _
        & " WHERE MID(fecha,7,4) >= 2018;"
conn.Execute var_sql

var_sql = "CREATE TABLE temp_postulante_web_seguimiento_2 TYPE = MYISAM" _
        & " SELECT a.* FROM temp_postulante_web_seguimiento AS a," _
        & " (SELECT rut_pos,MAX(fecha_hora) AS fecha_hora" _
        & " From temp_postulante_web_seguimiento" _
        & " GROUP BY rut_pos) AS b" _
        & " WHERE a.rut_pos = b.rut_pos AND a.fecha_hora = b.fecha_hora;"
conn.Execute var_sql


' Interesados
If alert = 6 Then
var_sql = " SELECT rut_pos AS RUT_POSTULANTE," _
    & " CONCAT(nombres_pos,' ',ape_pat_pos,' ',ape_mat_pos) AS NOMBRE_POSTULANTE, " _
    & " CONCAT(nombres_apo,ape_pat_apo,ape_mat_apo) AS NOMBRE_APODERADO," _
    & " rut_apo AS rut_apoderado, telefono AS telefono, telefono_celular AS CELULAR, MAIL," _
    & "  IF(oficina=3,'oficina',IF(oficina = '4','telefono',IF(oficina = 1,'web','mail'))) AS MEDIO, fecha AS FECHA_POSTULACION,curso_pos,anio as 'AÑO_POSTULACION'" _
& " From postulante_web" _
& " WHERE anio >= '2019' AND rut_pos NOT IN (SELECT rut_pos FROM postulante_web_seguimiento)" _
& " AND DATEDIFF(NOW(), CONCAT(MID(fecha, 7, 4), '-', MID(fecha, 4, 2), '-', MID(fecha, 1, 2))) <= 0 " _
& " And rut_pos not in (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2018' AND activo_alu = 'si') And rut_pos not in (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2019' AND activo_alu = 'si') and eliminado = 'NO'  " _
& " ORDER BY oficina"

objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close
End If

'Interesados sin contactar
If alert = 1 Then
var_sql = "SELECT rut_pos AS RUT_POSTULANTE," _
& "CONCAT(nombres_pos,' ',ape_pat_pos,' ',ape_mat_pos) AS NOMBRE_POSTULANTE," _
& "CONCAT(nombres_apo,ape_pat_apo,ape_mat_apo) AS NOMBRE_APODERADO," _
& "rut_apo As rut_apoderado, telefono As telefono, telefono_celular As CELULAR, MAIL,IF(oficina=3,'oficina',if(oficina = '4','telefono','web')) AS MEDIO, CAST(CONCAT(MID(fecha, 7, 4), '-', MID(fecha, 4, 2), '-', MID(fecha, 1, 2)) AS DATE) AS FECHA_POSTULACION,curso_pos,anio as 'AÑO_POSTULACION' " _
& " From postulante_web " _
& " WHERE MID(fecha,7,4) > 2017 and anio >= '2019' AND rut_pos NOT IN (SELECT rut_pos FROM postulante_web_seguimiento)" _
& " AND DATEDIFF(NOW(), CONCAT(MID(fecha, 7, 4), '-', MID(fecha, 4, 2), '-', MID(fecha, 1, 2))) >= 1 " _
& " AND rut_pos NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2018' AND activo_alu = 'si') AND rut_pos NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2019' AND activo_alu = 'si') AND rut_pos NOT IN (SELECT DISTINCT CI_ALU FROM postulaciones WHERE VIII_RES_EVAL >= 0 AND ANNO_POS_ALU >= '2018') AND rut_pos NOT IN (SELECT ci_alu FROM postulantes WHERE agno_postulacion >= 2018) AND eliminado = 'NO' group by rut_pos"
objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close
End If


'interesados no Inscritos
If alert = 2 Then
var_sql = "  SELECT a.rut_pos AS RUT_POSTULANTE, CONCAT(b.nombres_pos,' ',b.ape_pat_pos,' ',b.ape_mat_pos)  AS NOMBRE_POSTULANTE, " _
     & " CONCAT(b.nombres_apo,' ',b.ape_pat_apo,' ',b.ape_mat_apo) AS NOMBRE_APODERADO, " _
     & " b.rut_apo AS RUT_APODERADO, b.telefono AS TELEFONO, b.telefono_celular AS CELULAR, " _
     & " b.MAIL,a.comentario AS MOTIVO,b.fecha AS FECHA_POSTULACION,curso_pos AS CURSO_POSTULACION, " _
     & " anio AS 'AÑO_POSTULACION', a.fecha AS FECHA_CONTACTO, a.hora " _
& " FROM ( SELECT *, CONCAT(MID(fecha, 7, 4), '-', MID(fecha, 4, 2), '-', MID(fecha, 1, 2), ' ', hora) AS fecha_hora " _
& " FROM temp_postulante_web_seguimiento_2 AS pw1, " _
& "    (SELECT  rut_pos, MAX(CONCAT(MID(fecha, 7, 4), '-', MID(fecha, 4, 2), '-', MID(fecha, 1, 2), ' ', hora)) AS fecha2 " _
& " From temp_postulante_web_seguimiento_2 " _
   & " GROUP BY rut_pos) AS pw2 " _
 & " Where pw1.rut_pos = pw2.rut_pos " _
     & " AND CONCAT(MID(pw1.fecha, 7, 4), '-', MID(pw1.fecha, 4, 2), '-', MID(pw1.fecha, 1, 2), ' ', pw1.hora)= pw2.fecha2) AS a " _
    & " INNER JOIN postulante_web b ON a.rut_pos = b.rut_pos AND b.anio >= '2019' and a.renuncia = 1" _
 & " WHERE a.rut_pos NOT IN (SELECT CI_ALU FROM postulantes WHERE agno_postulacion >= 2019) AND a.rut_pos NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2019' AND activo_alu = 'si') AND a.rut_pos NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2019' AND activo_alu = 'si') AND a.rut_pos NOT IN (SELECT DISTINCT CI_ALU FROM postulaciones WHERE ANNO_POS_ALU >= '2019') AND eliminado = 'NO' "
objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close
End If


'Inscritos no Postulados
If alert = 3 Then
var_sql = "  SELECT a.CI_ALU AS RUT_POSTULANTE, CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu)  AS NOMBRE_POSTULANTE, CONCAT(ap.noms_apo,' ',ap.ape1_apo,' ',ap.ape2_apo) AS NOMBRE_APODERADO," _
 & " a.rut_apo AS RUT_APODERADO, ap.fon_c_apo AS TELEFONO, ap.cel_apo AS CELULAR, ap.email AS MAIL,IF(p.cod_ens IS NULL,'Sin Curso Asignado', '') AS Curso_Postulacion, p.VII_FEC_INS AS FECHA_POSTULACION," _
 & " agno_postulacion AS 'AÑO_POSTULACION'" _
 & " FROM postulantes AS p, apo_x_ins AS a, apoderados AS ap" _
 & " WHERE p.CI_ALU = a.CI_ALU AND a.rut_apo = ap.rut_apo AND VII_FEC_INS >= '2018-04-27' AND a.CI_ALU NOT IN (SELECT DISTINCT CI_ALU FROM postulaciones WHERE ANNO_POS_ALU >= '2018')" _
 & " AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias) AND a.ci_alu NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2018' AND activo_alu = 'si') AND a.ci_alu NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2019' AND activo_alu = 'si')"
objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close
End If

'Postulados sin nota
If alert = 4 Then
var_sql = "   SELECT a.CI_ALU AS RUT_POSTULANTE, CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu)  AS NOMBRE_POSTULANTE," _
      & " CONCAT(ap.noms_apo,' ',ap.ape1_apo,' ',ap.ape2_apo) AS NOMBRE_APODERADO," _
      & " a.rut_apo AS RUT_APODERADO, ap.fon_c_apo AS TELEFONO, ap.cel_apo AS CELULAR, ap.email AS MAIL, CONCAT(p.num_gra,' ',ce.des_ens_abrev ) AS 'CURSO_POSTULACION' , VII_FEC_INS AS FECHA_POSTULACION, agno_postulacion AS 'AÑO_POSTULACION'" _
  & " FROM postulantes AS p, apo_x_ins AS a, apoderados AS ap, codigos_ensenanza ce " _
    & " WHERE p.CI_ALU = a.CI_ALU AND a.rut_apo = ap.rut_apo AND agno_postulacion >= '2019' AND p.cod_ens = ce.cod_ens " _
  & " AND a.CI_ALU IN (SELECT DISTINCT CI_ALU FROM postulaciones WHERE VIII_RES_EVAL IS  NULL AND ANNO_POS_ALU >= '2018')" _
  & " AND a.ci_alu NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2018' AND activo_alu = 'si') AND a.ci_alu NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT = '2019' AND activo_alu = 'si') AND  p.VII_FEC_INS >= '2018-04-27' AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias)"

objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close
End If

' Aprobados no Matriculados
If alert = 5 Then

CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
If CODBC = "HUE001" Or CODBC = "ALT001" Then
    var_sql = "SELECT DISTINCT a.CI_ALU AS RUT_POSTULANTE, CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu)  AS NOMBRE_POSTULANTE, CONCAT(ap.noms_apo,' ',ap.ape1_apo,' ',ap.ape2_apo) AS NOMBRE_APODERADO, " _
    & " a.rut_apo AS RUT_APODERADO, ap.fon_c_apo AS TELEFONO, ap.cel_apo AS CELULAR, ap.email AS MAIL, CONCAT(p.num_gra,' ',ce.des_ens_abrev ) AS 'CURSO_POSTULACION', VII_FEC_INS AS FECHA_INSCRIPCION," _
    & " agno_postulacion AS 'AÑO_POSTULACION' " _
    & " FROM postulantes AS p, apo_x_ins AS a, apoderados AS ap , codigos_ensenanza ce " _
    & " WHERE p.cod_ens = ce.cod_ens AND p.CI_ALU = a.CI_ALU AND a.rut_apo = ap.rut_apo  AND a.CI_ALU  IN (SELECT DISTINCT CI_ALU FROM postulaciones WHERE  VIII_SIT_FIN_ALU >='A' AND VIII_RES_EVAL >= 70 AND ANNO_POS_ALU >= '2019') AND " _
    & " a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2018') and a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2019') AND  p.VII_FEC_INS >= '2018-04-27' AND a.CI_ALU NOT IN (SELECT rut_pos FROM temp_postulante_web_seguimiento_2 WHERE rut_pos = '23486614-1' AND renuncia = 0) AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias) GROUP BY a.ci_alu "
 Else
    var_sql = "SELECT DISTINCT a.CI_ALU AS RUT_POSTULANTE, CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu)  AS NOMBRE_POSTULANTE, CONCAT(ap.noms_apo,' ',ap.ape1_apo,' ',ap.ape2_apo) AS NOMBRE_APODERADO, " _
    & " a.rut_apo AS RUT_APODERADO, ap.fon_c_apo AS TELEFONO, ap.cel_apo AS CELULAR, ap.email AS MAIL, CONCAT(p.num_gra,' ',ce.des_ens_abrev ) AS 'CURSO_POSTULACION', VII_FEC_INS AS FECHA_INSCRIPCION," _
    & " agno_postulacion AS 'AÑO_POSTULACION' " _
    & " FROM postulantes AS p, apo_x_ins AS a, apoderados AS ap , codigos_ensenanza ce " _
    & " WHERE p.cod_ens = ce.cod_ens AND p.CI_ALU = a.CI_ALU AND a.rut_apo = ap.rut_apo  AND a.CI_ALU  IN (SELECT DISTINCT CI_ALU FROM postulaciones WHERE  VIII_SIT_FIN_ALU >='A' AND ANNO_POS_ALU >= '2019') AND " _
    & " a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2018') and a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2019') AND  p.VII_FEC_INS >= '2018-04-27' AND a.CI_ALU NOT IN (SELECT rut_pos FROM temp_postulante_web_seguimiento_2 WHERE rut_pos = '23486614-1' AND renuncia = 0) AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias) GROUP BY a.ci_alu "
 End If
 
objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close

End If


' Reprobados no Matriculados
If alert = 12 Then

CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
If CODBC = "HUE001" Or CODBC = "ALT001" Then
    var_sql = "SELECT DISTINCT a.CI_ALU AS RUT_POSTULANTE, CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu)  AS NOMBRE_POSTULANTE, CONCAT(ap.noms_apo,' ',ap.ape1_apo,' ',ap.ape2_apo) AS NOMBRE_APODERADO, " _
    & " a.rut_apo AS RUT_APODERADO, ap.fon_c_apo AS TELEFONO, ap.cel_apo AS CELULAR, ap.email AS MAIL, CONCAT(p.num_gra,' ',ce.des_ens_abrev ) AS 'CURSO_POSTULACION', VII_FEC_INS AS FECHA_INSCRIPCION," _
    & " agno_postulacion AS 'AÑO_POSTULACION',if(VIII_SIT_FIN_ALU = 'P','PENDIENTE',if(VIII_SIT_FIN_ALU = 'R','REPROBADO',if(VIII_SIT_FIN_ALU = 'A','ACEPTADO','FALTA A EXAMEN'))) as 'ESTADO ALUMNO',VIII_RES_EVAL as 'NOTA EVALUACION'" _
    & " FROM postulantes AS p, apo_x_ins AS a, apoderados AS ap , codigos_ensenanza ce,postulaciones pos " _
    & " WHERE p.cod_ens = ce.cod_ens AND p.CI_ALU = a.CI_ALU AND a.rut_apo = ap.rut_apo AND p.ci_alu = pos.ci_alu AND pos.ANNO_POS_ALU >= '2018' AND a.CI_ALU  IN (SELECT DISTINCT CI_ALU FROM postulaciones WHERE ANNO_POS_ALU >= '2018' and not(VIII_SIT_FIN_ALU >='A' AND VIII_RES_EVAL >= 70)) AND " _
    & " a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2018') and a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2019') AND  p.VII_FEC_INS >= '2018-04-27' AND a.CI_ALU NOT IN (SELECT rut_pos FROM temp_postulante_web_seguimiento_2 WHERE rut_pos = '23486614-1' AND renuncia = 0) AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias) GROUP BY a.ci_alu "
 Else
    var_sql = "SELECT DISTINCT a.CI_ALU AS RUT_POSTULANTE, CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu)  AS NOMBRE_POSTULANTE, CONCAT(ap.noms_apo,' ',ap.ape1_apo,' ',ap.ape2_apo) AS NOMBRE_APODERADO, " _
    & " a.rut_apo AS RUT_APODERADO, ap.fon_c_apo AS TELEFONO, ap.cel_apo AS CELULAR, ap.email AS MAIL, CONCAT(p.num_gra,' ',ce.des_ens_abrev ) AS 'CURSO_POSTULACION', VII_FEC_INS AS FECHA_INSCRIPCION," _
    & " agno_postulacion AS 'AÑO_POSTULACION',if(VIII_SIT_FIN_ALU = 'P','PENDIENTE',if(VIII_SIT_FIN_ALU = 'R','REPROBADO',if(VIII_SIT_FIN_ALU = 'A','ACEPTADO','FALTA A EXAMEN'))) as 'ESTADO ALUMNO',VIII_RES_EVAL as 'NOTA EVALUACION'" _
    & " FROM postulantes AS p, apo_x_ins AS a, apoderados AS ap , codigos_ensenanza ce,postulaciones pos " _
    & " WHERE p.cod_ens = ce.cod_ens AND p.CI_ALU = a.CI_ALU AND a.rut_apo = ap.rut_apo  AND p.ci_alu = pos.ci_alu AND pos.ANNO_POS_ALU >= '2018' AND a.CI_ALU  IN (SELECT DISTINCT CI_ALU FROM postulaciones WHERE ANNO_POS_ALU >= '2018' and not(VIII_SIT_FIN_ALU >='A') ) AND " _
    & " a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2018') and a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2019') AND  p.VII_FEC_INS >= '2018-04-27' AND a.CI_ALU NOT IN (SELECT rut_pos FROM temp_postulante_web_seguimiento_2 WHERE rut_pos = '23486614-1' AND renuncia = 0) AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias) GROUP BY a.ci_alu "
 End If
 
objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close

End If


' Matriculados 2018
If alert = 7 Then
var_sql = " SELECT c.*,CAST(CONCAT(MID(pw.fecha, 7, 4), '-', MID(pw.fecha, 4, 2), '-', MID(pw.fecha, 1, 2)) AS DATE) AS FECHA_POSTULACION FROM ( " _
& " SELECT   a.ci_alu ," _
& " CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) AS 'NOMBRE_POSTULANTE',axa.rut_apo," _
& " CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS 'NOMBRE_APODERADO'," _
& " CONCAT(p.num_gra,' ',ce.des_ens_abrev ) AS 'CURSO_POSTULACION'," _
& " agno_postulacion AS 'AÑO_POSTULACION',  fon_c_apo AS 'TELEFONO', email,  p.VII_FEC_INS As 'FECHA_INSCRIPCION' " _
& " FROM matriculas a," _
 & " Postulantes p ," _
 & " codigos_ensenanza ce," _
 & " apo_x_alu AS axa," _
 & " apoderados As apo" _
& " WHERE a.activo_alu = 'SI' and a.ci_alu = p.ci_alu AND a.anno_mat = '2018' AND p.cod_ens = ce.cod_ens " _
& " AND a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2019') AND  p.VII_FEC_INS >= '2018-04-27' AND a.confirmado = 'SI' AND axa.ci_alu = p.ci_alu AND axa.agno = a.anno_mat" _
& " AND  apo.rut_apo = axa.rut_apo" _
& " ) AS c LEFT JOIN  postulante_web pw ON c.ci_alu = pw.rut_pos"


 
objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close
End If


' Matriculados 2019
If alert = 9 Then
var_sql = " SELECT c.*,CAST(CONCAT(MID(pw.fecha, 7, 4), '-', MID(pw.fecha, 4, 2), '-', MID(pw.fecha, 1, 2)) AS DATE) AS FECHA_POSTULACION FROM ( " _
& " SELECT   a.ci_alu ," _
& " CONCAT(nom_alu,' ',ape_pat_alu,' ',ape_mat_alu) AS 'NOMBRE_POSTULANTE',axa.rut_apo," _
& " CONCAT(noms_apo,' ',ape1_apo,' ',ape2_apo) AS 'NOMBRE_APODERADO'," _
& " CONCAT(p.num_gra,' ',ce.des_ens_abrev ) AS 'CURSO_POSTULACION'," _
& " agno_postulacion AS 'AÑO_POSTULACION',  fon_c_apo AS 'TELEFONO', email,  p.VII_FEC_INS  As 'FECHA_INSCRIPCION' " _
& " FROM matriculas a," _
 & " Postulantes p ," _
 & " codigos_ensenanza ce," _
 & " apo_x_alu AS axa," _
 & " apoderados As apo" _
& " WHERE a.activo_alu = 'SI' and a.ci_alu = p.ci_alu AND a.anno_mat = '2019' AND p.cod_ens = ce.cod_ens " _
& " AND a.CI_ALU NOT IN (SELECT CI_ALU FROM matriculas WHERE anno_mat = '2018') AND  p.VII_FEC_INS >= '2018-04-27' AND a.confirmado = 'SI' AND axa.ci_alu = p.ci_alu AND axa.agno = a.anno_mat" _
& " AND  apo.rut_apo = axa.rut_apo" _
& " ) AS c LEFT JOIN  postulante_web pw ON c.ci_alu = pw.rut_pos GROUP BY c.ci_alu"

 
objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close

End If

End Sub







Private Sub Rec2Grid(ByRef rec As ADODB.Recordset, ByRef grid As Object, Optional ByVal Numeracion As Boolean = True)
    grid.ClearStructure
    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
            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


