VERSION 5.00
Begin VB.Form Form_Alerta_Postulaciones 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "CRM"
   ClientHeight    =   11640
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   13215
   Icon            =   "Form_Alerta_Postulaciones.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   11640
   ScaleWidth      =   13215
   Begin VB.Frame Frame1 
      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          =   11295
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   12855
      Begin VB.Label Label12 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   975
         Left            =   3480
         TabIndex        =   10
         Top             =   8040
         Width           =   2775
      End
      Begin VB.Label Label7 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   975
         Left            =   9720
         TabIndex        =   9
         Top             =   4440
         Width           =   2775
      End
      Begin VB.Image Image9 
         Height          =   3210
         Left            =   9720
         Picture         =   "Form_Alerta_Postulaciones.frx":2A8B2
         Stretch         =   -1  'True
         Top             =   4080
         Width           =   2805
      End
      Begin VB.Label Label6 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   975
         Left            =   6600
         TabIndex        =   7
         Top             =   4440
         Width           =   2775
      End
      Begin VB.Image Image8 
         Height          =   3210
         Left            =   6600
         Picture         =   "Form_Alerta_Postulaciones.frx":3791A
         Stretch         =   -1  'True
         Top             =   4080
         Width           =   2805
      End
      Begin VB.Label Label3 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   975
         Left            =   6600
         TabIndex        =   8
         Top             =   7920
         Width           =   2775
      End
      Begin VB.Image Image7 
         Height          =   3210
         Left            =   6600
         Picture         =   "Form_Alerta_Postulaciones.frx":44099
         Stretch         =   -1  'True
         Top             =   7560
         Width           =   2805
      End
      Begin VB.Label Label2 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   1215
         Left            =   360
         TabIndex        =   6
         Top             =   840
         Width           =   2775
      End
      Begin VB.Image Image6 
         Height          =   3210
         Left            =   360
         Picture         =   "Form_Alerta_Postulaciones.frx":4FBF9
         Stretch         =   -1  'True
         Top             =   600
         Width           =   2805
      End
      Begin VB.Label Label8 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   1095
         Left            =   3480
         TabIndex        =   5
         Top             =   4440
         Width           =   2775
      End
      Begin VB.Label Label11 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   975
         Left            =   360
         TabIndex        =   4
         Top             =   4440
         Width           =   2775
      End
      Begin VB.Label Label1 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   1215
         Left            =   3480
         TabIndex        =   3
         Top             =   840
         Width           =   2775
      End
      Begin VB.Label Label4 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   1095
         Left            =   6600
         TabIndex        =   2
         Top             =   840
         Width           =   2775
      End
      Begin VB.Label Label5 
         Alignment       =   2  'Center
         BackStyle       =   0  'Transparent
         Caption         =   "0"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   48
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00FFFFFF&
         Height          =   1095
         Left            =   9720
         TabIndex        =   1
         Top             =   840
         Width           =   2775
      End
      Begin VB.Image Image1 
         Height          =   3210
         Left            =   3480
         Picture         =   "Form_Alerta_Postulaciones.frx":5E522
         Stretch         =   -1  'True
         Top             =   600
         Width           =   2805
      End
      Begin VB.Image Image3 
         Height          =   3210
         Left            =   6600
         Picture         =   "Form_Alerta_Postulaciones.frx":6CE3F
         Stretch         =   -1  'True
         Top             =   600
         Width           =   2805
      End
      Begin VB.Image Image4 
         Height          =   3210
         Left            =   9720
         Picture         =   "Form_Alerta_Postulaciones.frx":7BBCF
         Stretch         =   -1  'True
         Top             =   600
         Width           =   2805
      End
      Begin VB.Image Image2 
         Height          =   3210
         Left            =   360
         Picture         =   "Form_Alerta_Postulaciones.frx":874A1
         Stretch         =   -1  'True
         Top             =   4080
         Width           =   2805
      End
      Begin VB.Image Image5 
         Height          =   3210
         Left            =   3480
         Picture         =   "Form_Alerta_Postulaciones.frx":94236
         Stretch         =   -1  'True
         Top             =   4080
         Width           =   2805
      End
      Begin VB.Image Image12 
         Height          =   3210
         Left            =   3480
         Picture         =   "Form_Alerta_Postulaciones.frx":A1635
         Stretch         =   -1  'True
         Top             =   7560
         Width           =   2805
      End
   End
End
Attribute VB_Name = "Form_Alerta_Postulaciones"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False


Private Sub Command2_Click()
If Not (Label4.Caption = 0) Then
alert = 2
Form_Lista_Alerta.Show
Else
    Command2.Enabled = True
End If

End Sub

Private Sub Command3_Click()
If Not (Label5.Caption = 0) Then
alert = 3
Form_Lista_Alerta.Show
Else
    Command3.Enabled = True
    MsgBox "No hay alertas disponibles"
End If

End Sub

Private Sub Command4_Click()
If Not (Label11.Caption = 0) Then
alert = 4
Form_Lista_Alerta.Show
Else
    Command4.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub

Private Sub Command5_Click()
If Not (Label6.Caption = 0) Then
alert = 5
Form_Lista_Alerta.Show
Else
    Command4.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub


Private Sub Image1_Click()
If Not (Label1.Caption = 0) Then
alert = 1

Form_Lista_Alerta.Show
Else
    Image1.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub






Private Sub Image12_Click()
If Not (Label12.Caption = 0) Then
alert = 12
Form_Lista_Alerta.Show
Else
    Image12.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub

Private Sub Image2_Click()
If Not (Label11.Caption = 0) Then
alert = 4
Form_Lista_Alerta.Show
Else
    Image2.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub

Private Sub Image3_Click()
If Not (Label4.Caption = 0) Then
alert = 2
Form_Lista_Alerta.Show
Else
    Image3.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub


Private Sub Image4_Click()
If Not (Label5.Caption = 0) Then
alert = 3
Form_Lista_Alerta.Show
Else
    Image4.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub


Private Sub Image5_Click()
If Not (Label8.Caption = 0) Then
alert = 5
Form_Lista_Alerta.Show
Else
    Image5.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub

Private Sub Image6_Click()
If Not (Label2.Caption = 0) Then
alert = 6
Form_Lista_Alerta.Show
Else
    Image5.Enabled = True
    MsgBox "No hay alertas disponibles"
End If
End Sub

Private Sub Image8_Click()
If Not (Label6.Caption = 0) Then
alert = 7
Form_Lista_Alerta.Show
    Else
        Image5.Enabled = True
        MsgBox "No hay alertas disponibles"
    End If
End Sub

Private Sub Image9_Click()


If Not (Label7.Caption = 0) Then
alert = 9
Form_Lista_Alerta.Show
    Else
        Image5.Enabled = True
        MsgBox "No hay alertas disponibles"
    End If
    
End Sub

Private Sub Form_Load()
 
ancho = Form_main.Width - Form_Alerta_Postulaciones.Width
alto = Form_main.Height - Form_Alerta_Postulaciones.Height
dist1 = ancho / 2
dist2 = alto / 2
Form_Alerta_Postulaciones.Left = dist1
Form_Alerta_Postulaciones.Top = dist2 - 2000



Dim var_sql, var_sql2, a, b, Res
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
var_sql = "SELECT COUNT(*) AS RUT_POSTULANTE" _
        & " 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 >= '2019' AND activo_alu = 'si') AND eliminado = 'NO' ORDER BY oficina"

conn.Execute var_sql

objRS.Open var_sql, conn
Label2.Caption = objRS("RUT_POSTULANTE")
objRS.Close


' Interesados sin contactar

var_sql = " SELECT count(DISTINCT rut_pos) AS no_contactados" _
& " 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 >= '2019')" _
& " AND rut_pos NOT IN (SELECT ci_alu FROM postulantes WHERE agno_postulacion >= 2019) AND eliminado = 'NO'"

conn.Execute var_sql

objRS.Open var_sql, conn
Label1.Caption = objRS("No_Contactados")
objRS.Close

' Interesados no Inscritos
var_sql = "  SELECT DISTINCT COUNT( a.rut_pos ) AS RUT_POSTULANTE" _
 & " 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' "
conn.Execute var_sql

objRS.Open var_sql, conn
Label4.Caption = objRS("RUT_POSTULANTE")
objRS.Close


'Inscritos no Postulados
var_sql = "SELECT  COUNT(*) AS inscritos_no_postulados " _
        & "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 p.ci_alu NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT >= '2018' AND activo_alu = 'si') AND p.ci_alu NOT IN (SELECT ci_alu FROM matriculas WHERE ANNO_MAT >= '2019' AND activo_alu = 'si')"
conn.Execute var_sql

objRS.Open var_sql, conn
Label5.Caption = objRS("inscritos_no_postulados")
objRS.Close

'Postulados sin nota (o sin fecha de evaluacion)
var_sql = "SELECT COUNT(*) as sin_prueba_asignada " _
        & " 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) "
conn.Execute var_sql

objRS.Open var_sql, conn
Label11.Caption = objRS("sin_prueba_asignada")
objRS.Close

'Aprobados sin Matricula
CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
If CODBC = "HUE001" Or CODBC = "ALT001" Then
    var_sql = " SELECT DISTINCT COUNT( a.CI_ALU) AS RUT_POSTULANTE" _
    & " 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 >= '2018') 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 postulante_web_seguimiento WHERE renuncia = 0) AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias)"
Else
    var_sql = " SELECT DISTINCT COUNT( a.CI_ALU) AS RUT_POSTULANTE" _
    & " 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 >= '2018') 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 postulante_web_seguimiento WHERE renuncia = 0) AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias)"
End If

conn.Execute var_sql

objRS.Open var_sql, conn
Label8.Caption = objRS("RUT_POSTULANTE")
objRS.Close

'Reprobados sin Matricula
CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
If CODBC = "HUE001" Or CODBC = "ALT001" Then
    var_sql = " SELECT DISTINCT COUNT( a.CI_ALU) AS RUT_POSTULANTE" _
    & " 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 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 postulante_web_seguimiento WHERE renuncia = 0) AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias)"
Else
    var_sql = " SELECT DISTINCT COUNT( a.CI_ALU) AS RUT_POSTULANTE" _
    & " 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 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 postulante_web_seguimiento WHERE renuncia = 0) AND a.CI_ALU NOT IN (SELECT ci_alumno FROM form_renuncias)"
End If

conn.Execute var_sql

objRS.Open var_sql, conn
Label12.Caption = objRS("RUT_POSTULANTE")
objRS.Close


'Matriculados 2018
var_sql = "  SELECT COUNT(c.ci_alu) AS 'RUT_ALUMNO' 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 = '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 "

conn.Execute var_sql

objRS.Open var_sql, conn
Label6.Caption = objRS("RUT_ALUMNO")
objRS.Close

'Matriculados 2019
var_sql = "  SELECT COUNT(distinct c.ci_alu) AS 'RUT_ALUMNO' 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"


conn.Execute var_sql

objRS.Open var_sql, conn
Label7.Caption = objRS("RUT_ALUMNO")
objRS.Close



'renuncias
var_sql = "SELECT SUM(a) AS suma FROM (" _
& " SELECT COUNT(DISTINCT p.rut_pos) AS a,3 as b" _
& " FROM postulante_web p, temp_postulante_web_seguimiento_2 pw" _
& " WHERE p.anio >= '2018' AND p.rut_pos = pw.rut_pos AND pw.renuncia = '0' AND p.eliminado='NO'" _
& "  UNION SELECT COUNT(DISTINCT r.ci_alumno) AS a,5 as b FROM form_renuncias r, postulantes p, apo_x_ins " _
& " a, apoderados ap WHERE r.ci_alumno = p.ci_alu AND p.ci_alu = a.ci_alu AND a.rut_apo = ap.rut_apo AND p.agno_postulacion >= 2019 AND r.fecha >= '2018-04-27') AS a"
conn.Execute var_sql

objRS.Open var_sql, conn
Label3.Caption = objRS("suma")
objRS.Close


End Sub





Private Sub Image7_Click()
Dim var_sql, val
Dim objRS As ADODB.Recordset
Set objRS = New ADODB.Recordset


var_sql = "SELECT SUM(c.a) as valor FROM ( " _
     & " SELECT COUNT(p.rut_pos) AS a" _
     & " FROM postulante_web p, postulante_web_seguimiento pw" _
     & " WHERE p.anio >= '2018' AND p.rut_pos = pw.rut_pos AND pw.renuncia = '0' AND p.eliminado='NO' " _
     & " Union " _
     & " SELECT COUNT(r.ci_alumno) AS a " _
     & " FROM form_renuncias r, postulantes p, apo_x_ins a, apoderados ap " _
     & " WHERE r.ci_alumno = p.ci_alu AND p.ci_alu = a.ci_alu AND a.rut_apo = ap.rut_apo AND p.agno_postulacion >= 2018 AND r.fecha >= '2018-04-27') AS c"
conn.Execute var_sql

objRS.Open var_sql, conn
val = objRS("valor")
objRS.Close


If val > 0 Then
    Load Form_NFicha_Renuncias
Else
    MsgBox " No hay Renuncias Disponibles"
End If

'If Not (Label2.Caption = 0) Then
'alert = 7
'Form_Lista_Alerta.Show
'Else
'    Image5.Enabled = True
'    MsgBox "No hay alertas disponibles"
'End If

End Sub










