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_NFicha_Renuncias 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "CRM Renuncias "
   ClientHeight    =   8400
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   14430
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   8400
   ScaleWidth      =   14430
   Begin VB.CommandButton Command1 
      Caption         =   "Exportar a Excel"
      Height          =   975
      Left            =   4080
      Picture         =   "Form_NFicha_Renuncias.frx":0000
      Style           =   1  'Graphical
      TabIndex        =   2
      Top             =   7320
      Width           =   1815
   End
   Begin VB.CommandButton Cerrar 
      Caption         =   "Cerrar"
      Height          =   975
      Left            =   7560
      Picture         =   "Form_NFicha_Renuncias.frx":05D0
      Style           =   1  'Graphical
      TabIndex        =   1
      Top             =   7320
      Width           =   1815
   End
   Begin MSHierarchicalFlexGridLib.MSHFlexGrid MSHFlexGrid1 
      Height          =   7095
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   14175
      _ExtentX        =   25003
      _ExtentY        =   12515
      _Version        =   393216
      _NumberOfBands  =   1
      _Band(0).Cols   =   2
   End
   Begin MSComDlg.CommonDialog CommonDialog1 
      Left            =   6480
      Top             =   7320
      _ExtentX        =   847
      _ExtentY        =   847
      _Version        =   393216
   End
End
Attribute VB_Name = "Form_NFicha_Renuncias"
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"

CommonDialog1.ShowSave

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 = .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) >= 2017;"
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




    var_sql = " SELECT DISTINCT p.rut_pos AS RUT_POSTULANTE, " _
    & " CONCAT(p.nombres_pos,' ',p.ape_pat_pos,' ',p.ape_mat_pos) AS NOMBRE_POSTULANTE," _
    & " p.rut_apo AS RUT_APODERADO," _
    & " CONCAT(p.nombres_apo,p.ape_pat_apo,p.ape_mat_apo) AS NOMBRE_APODERADO," _
    & " p.telefono AS TELEFONO, p.telefono_celular AS CELULAR, p.MAIL, comentario AS COMENTARIO, 'POSTULACION' AS PROCESO, 'WEB' AS POSTULACION," _
    & " CONCAT(MID(pw.fecha, 7, 4), '-', MID(pw.fecha, 4, 2), '-', MID(pw.fecha, 1, 2)) AS FECHA" _
    & " 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' AND MID(pw.fecha, 7, 4) >= 2018 GROUP BY p.rut_pos " _
    & " Union" _
    & " SELECT r.ci_alumno AS RUT_POSTULANTE, CONCAT(p.nom_alu,' ',p.ape_pat_alu,' ',p.ape_mat_alu)  AS NOMBRE_ALUMNO," _
    & " a.rut_apo AS RUT_APODERADO,CONCAT(ap.noms_apo,' ',ap.ape1_apo,' ',ap.ape2_apo) AS NOMBRE_APODERADO,ap.fon_c_apo AS TELEFONO," _
    & " ap.cel_apo AS CELULAR," _
    & " ap.email AS MAIL,r.proceso AS PROCESO, comentario AS COMENTARIO, 'oficina' AS VIA_POSTULACION," _
    & " r.fecha AS FECHA" _
    & " 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' GROUP BY r.ci_alumno"

objRS.Open var_sql, conn

Call Rec2Grid(objRS, MSHFlexGrid1)

objRS.Close

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
