VERSION 5.00
Begin VB.Form Frm_declaracion_jurada 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Declaración jurada"
   ClientHeight    =   1530
   ClientLeft      =   30
   ClientTop       =   360
   ClientWidth     =   2460
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   1530
   ScaleWidth      =   2460
   Begin VB.CommandButton Command2 
      Caption         =   "Generar"
      Height          =   375
      Left            =   600
      TabIndex        =   2
      Top             =   840
      Width           =   1215
   End
   Begin VB.ComboBox cmb_agno 
      Height          =   315
      Left            =   600
      TabIndex        =   1
      Text            =   "Combo2"
      Top             =   240
      Width           =   1692
   End
   Begin VB.Label Label2 
      Caption         =   "Año"
      Height          =   255
      Left            =   120
      TabIndex        =   0
      Top             =   240
      Width           =   375
   End
End
Attribute VB_Name = "Frm_declaracion_jurada"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'Private Sub Command1_Click()
'    Dim objRS As ADODB.Recordset
'    Set objRS = New ADODB.Recordset
'    Dim sql As String
'    Dim Mes As String
'    Dim Registro As String
'    Dim ruta As String
'
'    sql = "DROP TABLE IF EXISTS temp_alu;"
'    conn.Execute sql
'    sql = "DROP TABLE IF EXISTS temp_asi;"
'    conn.Execute sql
'    sql = "DROP TABLE IF EXISTS temp_asi_alu;"
'    conn.Execute sql
'
''    sql = "CREATE TABLE temp_alu TYPE = MYISAM" _
''        & " SELECT c.ci_alu,MID(CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu,'                                            '),1,40) AS alumno," _
''        & "LENGTH(MID(CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu,'                                            '),1,40) ) AS largo" _
''        & " FROM alu_x_cur c,postulantes p" _
''        & " WHERE c.ci_alu = p.ci_alu AND agno = " & cmb_agno.Text & " AND activo_alu = 'SI';"
'
'    sql = "CREATE TABLE temp_alu TYPE = MYISAM select left(rdb,(length(rdb)-2)) as rdb,ci_alu from alu_x_cur, (select rdb from establecimientos) as e" _
'        & " where agno = " & cmb_agno.Text & " AND activo_alu = 'SI';"
'    conn.Execute sql
'
'    sql = "CREATE TABLE temp_asi_alu TYPE = MYISAM" _
'        & " SELECT ci_alu,SUM(asi_r) AS asi_r,SUM(asi_t) AS asi_t FROM asistencia" _
'        & " WHERE agno = " & cmb_agno.Text & " AND asi_r <> 0" _
'        & " GROUP BY ci_alu;"
'    conn.Execute sql
'
'    sql = "CREATE TABLE temp_asi TYPE = MYISAM" _
'        & " SELECT ci_alu,asi_r,asi_t,((100*asi_t)/asi_r) AS porcentaje FROM temp_asi_alu; "
'    conn.Execute sql
'
'    sql = "SELECT rdb,left(alu.ci_alu,(length(alu.ci_alu)-2)) as ci_alu ,IF(porcentaje IS NULL OR porcentaje < 85,'N','S') AS porcentaje,porcentaje AS A" _
'        & " FROM temp_alu alu LEFT JOIN temp_asi asi" _
'        & " ON alu.ci_alu = asi.ci_alu"
'    'conn.Execute sql
'
''    sql = "SELECT concat(alu.ci_alu,alumno,IF(porcentaje IS NULL OR porcentaje < 85,'NO','SI')) as a" _
''        & " FROM temp_alu alu LEFT JOIN temp_asi asi" _
''        & " ON alu.ci_alu = asi.ci_alu"
'
'    objRS.Open sql, conn
'
'   ' MsgBox Time
'
'    ruta = "C:\dj_" & Mid(devuelvecampo("establecimientos", "", "nom_est", "rdb <> ''", conn), 16) & "_" & Date & ".csv"
'    Call Crear_Archivo_Cobranza(ruta)
'    While Not objRS.EOF
'        Registro = objRS("rdb") & ";" & objRS("ci_alu") & ";" & objRS("porcentaje")
'       ' Registro = objRS("a")
'        Call AgregarRegistroCobranza(Registro, ruta)
'        objRS.MoveNext
'    Wend
'    objRS.Close
'
'    MsgBox "El archivo fue generado exitosamente", vbInformation
'
'End Sub

Private Sub Command2_Click()
'Beep
'If MsgBox("¿Realmente desea exportar la declaración jurada a Excel?", vbQuestion + vbYesNo, "Consulta") = vbYes Then


Dim obj As Object
Dim Libro As Object
Dim Hoja As Object

Set obj = CreateObject("Excel.application")
Set Libro = obj.Workbooks.Add()
Set Hoja = Libro.Sheets(1)

Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim sql As String
    Dim Mes As String
    Dim Registro As String
    Dim ruta As String
        
    sql = "DROP TABLE IF EXISTS temp_rend;"
    conn.Execute sql
    sql = "DROP TABLE IF EXISTS temp_alu;"
    conn.Execute sql
    sql = "DROP TABLE IF EXISTS temp_asi;"
    conn.Execute sql
    sql = "DROP TABLE IF EXISTS temp_asi_alu;"
    conn.Execute sql
        
'    sql = "CREATE TABLE temp_alu TYPE = MYISAM" _
'        & " SELECT c.ci_alu,MID(CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu,'                                            '),1,40) AS alumno," _
'        & "LENGTH(MID(CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu,'                                            '),1,40) ) AS largo" _
'        & " FROM alu_x_cur c,postulantes p" _
'        & " WHERE c.ci_alu = p.ci_alu AND agno = " & cmb_agno.Text & " AND activo_alu = 'SI';"
        
    sql = "CREATE TABLE temp_alu TYPE = MYISAM select LEFT(rdb,(LENGTH(rdb)-2)) AS rdb,ci_alu from alu_x_cur, (select rdb from establecimientos) as e" _
        & " where agno = " & cmb_agno.Text & " AND activo_alu = 'SI';"
    conn.Execute sql
        
    sql = "CREATE TABLE temp_asi_alu TYPE = MYISAM" _
        & " SELECT ci_alu,SUM(asi_r) AS asi_r,SUM(asi_t) AS asi_t FROM asistencia" _
        & " WHERE agno = " & cmb_agno.Text & " AND asi_r <> 0" _
        & " GROUP BY ci_alu;"
    conn.Execute sql
        
    sql = "CREATE TABLE temp_asi TYPE = MYISAM" _
        & " SELECT ci_alu,asi_r,asi_t,((100*asi_t)/asi_r) AS porcentaje FROM temp_asi_alu; "
    conn.Execute sql
    
    sql = "CREATE TABLE temp_rend TYPE = MYISAM SELECT alu.ci_alu ,IF(porcentaje IS NULL OR porcentaje < 85,'N','S') AS porcentaje" _
        & " FROM temp_alu alu LEFT JOIN temp_asi asi" _
        & " ON alu.ci_alu = asi.ci_alu"
    conn.Execute sql
    
    sql = "SELECT rdb,concat(left(alu.ci_alu,(length(alu.ci_alu)-2)),mid(alu.ci_alu,(length(alu.ci_alu)),1)) as ci_alu ,IF(porcentaje IS NULL OR porcentaje < 85,'N','S') AS porcentaje,porcentaje AS A" _
        & " FROM temp_alu alu LEFT JOIN temp_asi asi" _
        & " ON alu.ci_alu = asi.ci_alu"
    'conn.Execute sql
    
'    sql = "SELECT concat(alu.ci_alu,alumno,IF(porcentaje IS NULL OR porcentaje < 85,'NO','SI')) as a" _
'        & " FROM temp_alu alu LEFT JOIN temp_asi asi" _
'        & " ON alu.ci_alu = asi.ci_alu"
    
    objRS.Open sql, conn
    
   ' MsgBox Time
    
    ruta = "C:\dj_" & Mid(devuelvecampo("establecimientos", "", "nom_est", "rdb <> ''", conn), 16) & "_" & Date & "_" & Mid(Time, 1, 2) & Mid(Time, 4, 2) & Mid(Time, 7, 2) & ".csv"
    Call Crear_Archivo_Cobranza(ruta)
    i = 0
    While Not objRS.EOF
        i = i + 1
        Registro = objRS("rdb") & ";" & objRS("ci_alu") & ";" & objRS("porcentaje")
        Hoja.Cells(i, 1) = objRS("rdb")
        Hoja.Cells(i, 2) = objRS("ci_alu")
        Hoja.Cells(i, 3) = objRS("porcentaje")
       ' Registro = objRS("a")
        Call AgregarRegistroCobranza(Registro, ruta)
        objRS.MoveNext
    Wend
    objRS.Close
  
   
'Hoja.Cells(1, 1) = "Título del DVD"
'Hoja.Cells(1, 2) = "Actor Principal"
'Hoja.Cells(1, 3) = "Género"
'Hoja.Cells(1, 4) = "Materia"
'Hoja.Cells(1, 4) = "Estante"
'Hoja.Cells(1, 5) = "Casilla"
'Hoja.Cells(1, 6) = "Calidad"
'Hoja.Cells(1, 7) = "Valor"
'Hoja.Cells(1, 8) = "Fecha Alta"
'Hoja.Cells(1, 9) = "Observaciones"

'    If dir(App.Path & "\nombre.xls") <> "" Then
'        Kill App.Path & "\nombre.xls"
'    End If

    If dir("C:\dj_" & Mid(devuelvecampo("establecimientos", "", "nom_est", "rdb <> ''", conn), 16) & "_" & Date & "_" & Mid(Time, 1, 2) & Mid(Time, 4, 2) & Mid(Time, 7, 2) & ".xls") <> "" Then
        Kill "C:\dj_" & Mid(devuelvecampo("establecimientos", "", "nom_est", "rdb <> ''", conn), 16) & "_" & Date & "_" & Mid(Time, 1, 2) & Mid(Time, 4, 2) & Mid(Time, 7, 2) & ".xls"
    End If
    
'Obj.Application.ActiveWorkbook.SaveAs App.Path & "\nombre.xls"
obj.Application.ActiveWorkbook.SaveAs "C:\dj_" & Mid(devuelvecampo("establecimientos", "", "nom_est", "rdb <> ''", conn), 16) & "_" & Date & "_" & Mid(Time, 1, 2) & Mid(Time, 4, 2) & Mid(Time, 7, 2) & ".xls"
obj.Application.Quit


Set obj = Nothing
Set Libro = Nothing
Set Hoja = Nothing

'End If

MsgBox "El archivo fue generado exitosamente", vbInformation

End Sub

Private Sub Form_Load()
    Dim sql As String
    
    cmb_agno.AddItem Anno - 1
    cmb_agno.AddItem Anno
    cmb_agno.AddItem Anno + 1
    cmb_agno.Text = Anno
    
'    cmb_mes.AddItem "Enero"
'    cmb_mes.AddItem "Febrero"
'    Cmb_Mes.AddItem "Marzo"
'    Cmb_Mes.AddItem "Abril"
'    Cmb_Mes.AddItem "Mayo"
'    Cmb_Mes.AddItem "Junio"
'    Cmb_Mes.AddItem "Julio"
'    Cmb_Mes.AddItem "Agosto"
'    Cmb_Mes.AddItem "Septiembre"
'    Cmb_Mes.AddItem "Octubre"
'    Cmb_Mes.AddItem "Noviembre"
'    Cmb_Mes.AddItem "Diciembre"
'    Cmb_Mes.Text = ""
    
    
    
End Sub



Function Crear_Archivo_Cobranza(ruta As String) As Boolean
On Error GoTo Controlerror:
    'ArchBCI = "\\" & Form_main.Winsock1.LocalIP & "\Reg_Cobranza\" & "deuda.txt"
    ArchBCI = ruta
    Crear_Archivo_Cobranza = False
    Canc = FreeFile
    Open ArchBCI For Output As #Canc
    Close #Canc

    Crear_Archivo_Cobranza = True
    Exit Function
Controlerror:
    Err.Raise Err.Number, "CrearArchBCI->" & Err.Source, Err.Description
End Function

Function AgregarRegistroCobranza(Registro As String, ruta As String) As Boolean
On Error GoTo Controlerror:
    Dim num_arch As Integer
    
    AgregarRegistroCobranza = False
    
    num_arch = FreeFile
    Arch = ruta
    Open Arch For Append As #num_arch
        Print #num_arch, Registro
    Close #num_arch
    AgregarRegistroCobranza = True
    Exit Function
Controlerror:
    Err.Raise Err.Number, "AgregarRegistroArchivoGeneracion->" & Err.Source, Err.Description
End Function

