VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_deuda_bloqueos 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Deuda bloqueos"
   ClientHeight    =   7200
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   9645
   Icon            =   "Form_deuda_bloqueos.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   7200
   ScaleWidth      =   9645
   Begin VB.Frame Frame1 
      Caption         =   "Listado de alumnos"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   5415
      Left            =   120
      TabIndex        =   2
      Top             =   120
      Width           =   9375
      Begin VB.Frame Frame2 
         Caption         =   "Frame2"
         Height          =   2175
         Left            =   1800
         TabIndex        =   5
         Top             =   2520
         Visible         =   0   'False
         Width           =   5655
         Begin VB.Label Label2 
            Caption         =   "En proceso"
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   48
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   975
            Left            =   360
            TabIndex        =   6
            Top             =   600
            Width           =   5175
         End
      End
      Begin VB.Frame Frame3 
         Caption         =   "Ingrese rut alumno"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00C00000&
         Height          =   1095
         Left            =   120
         TabIndex        =   3
         Top             =   480
         Width           =   9135
         Begin VB.TextBox txt_ci_alu 
            Height          =   285
            Left            =   600
            MaxLength       =   10
            TabIndex        =   10
            Top             =   480
            Width           =   1215
         End
         Begin VB.CommandButton Command1 
            Caption         =   "Mostrar deuda"
            BeginProperty Font 
               Name            =   "Arial"
               Size            =   8.25
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            Height          =   495
            Left            =   5520
            TabIndex        =   4
            Top             =   360
            Width           =   2535
         End
         Begin VB.Label Label3 
            Caption         =   "Rut."
            Height          =   255
            Left            =   120
            TabIndex        =   11
            Top             =   480
            Width           =   375
         End
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   3255
         Left            =   120
         TabIndex        =   7
         Top             =   1920
         Width           =   9135
         _ExtentX        =   16113
         _ExtentY        =   5741
         _Version        =   393216
      End
   End
   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            =   7440
      Picture         =   "Form_deuda_bloqueos.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   1
      Top             =   6120
      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            =   480
      Picture         =   "Form_deuda_bloqueos.frx":2AED8
      Style           =   1  'Graphical
      TabIndex        =   0
      Top             =   6120
      Width           =   1575
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   8
      Top             =   5760
      Width           =   9375
      _ExtentX        =   16536
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
   Begin VB.Label Label1 
      Caption         =   "Label1"
      Height          =   495
      Left            =   6000
      TabIndex        =   9
      Top             =   3600
      Width           =   1215
   End
End
Attribute VB_Name = "Form_deuda_bloqueos"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

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

Private Sub cmd_salir_Click()
    Unload Me
End Sub

Private Sub Command1_Click()
    Dim var_sql As String
    Dim maximo_mes As Integer
    Dim sw As Boolean
    Dim rut_apo As String
    Dim conecta As Boolean
   
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    Frame2.Visible = True
    
    MSFlexGrid1.Cols = 2
    MSFlexGrid1.Rows = 2
    MSFlexGrid1.Clear
    
    sw = True
    
    ' Deuda alumno
    var_sql = "SELECT dm.agno,dm.run_alu,a.rut_apo,SUM(monto_tot_cuota-monto_abono) AS deuda,'' AS colegio,'Deuda del alumno' AS observacion" _
            & " FROM fnza_detalle_mensualidad dm,alu_x_cur c," _
            & "apo_x_alu axc,postulantes p,apoderados a WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu AND c.ci_alu = axc.ci_alu" _
            & " AND dm.agno = c.agno AND c.agno = axc.agno AND dm.agno > 2009 AND dm.run_alu = '" & txt_ci_alu.Text & "' AND dm.run_alu = p.ci_alu" _
            & " AND axc.rut_apo = a.rut_apo AND dm.agno < 2016 AND dm.mes > 2" _
            & " GROUP BY a.rut_apo,dm.agno" _
            & " HAVING SUM(monto_tot_cuota - monto_abono) >= 13500;"
    
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
        sw = False
    Else
       
    End If
    objRS.Close
    
    'Obtengo apoderado
    var_sql = "SELECT rut_apo FROM apo_x_alu WHERE agno = 2015 AND ci_alu = '" & txt_ci_alu.Text & "'"
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        rut_apo = objRS("rut_apo")
       
    End If
    objRS.Close
    
    
    ' Deuda apoderado
    var_sql = "SELECT dm.agno,dm.run_alu,a.rut_apo,SUM(monto_tot_cuota-monto_abono) AS deuda,'' AS colegio,'Deuda del apoderado' AS observacion" _
            & " FROM fnza_detalle_mensualidad dm,alu_x_cur c,apo_x_alu axc,postulantes p,apoderados a WHERE c.activo_alu = 'SI' AND dm.run_alu = c.ci_alu" _
            & " AND c.ci_alu = axc.ci_alu AND dm.agno = c.agno AND c.agno = axc.agno AND dm.agno > 2009 AND a.rut_apo = '" & rut_apo & "' AND" _
            & " dm.run_alu = p.ci_alu And dm.agno < 2016 And dm.mes > 2 And axc.rut_apo = a.rut_apo" _
            & " GROUP BY a.rut_apo,dm.agno" _
            & " HAVING SUM(monto_tot_cuota - monto_abono) >= 13500;"
    
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        If sw = True Then
            Call Rec2Grid(objRS, MSFlexGrid1, True)
            sw = False
        Else
            filas = MSFlexGrid1.Rows
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        End If
          
    End If
    objRS.Close
    
    'cambiar conexion
    conecta = ConectarBDSiempre(connLN, "192.168.7.91", "comunes_gescol", "groot", "kripplefight", True)
    
    ' Deuda alumno colegio
    var_sql = "SELECT '' AS agno,ci_alu,'' AS ci_apo,'' AS deuda,colegio,'Colegio de la deuda por alumno' AS observacion FROM `alumnos_deudores` WHERE ci_alu = '" & txt_ci_alu.Text & "';"
    
    objRS.Open var_sql, connLN
    If Not objRS.EOF Then
        If sw = True Then
            Call Rec2Grid(objRS, MSFlexGrid1, True)
            sw = False
        Else
            filas = MSFlexGrid1.Rows
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        End If
          
    End If
    objRS.Close
    
    
    'Deuda apoderado colegio
    var_sql = "SELECT '' AS agno,'' AS ci_alu,ci_apo,'' AS deuda,colegio,'Colegio de la deuda por apoderado' AS observacion FROM `apoderados_deudores` WHERE ci_apo = '" & rut_apo & "';"
    
    objRS.Open var_sql, connLN
    If Not objRS.EOF Then
        If sw = True Then
            Call Rec2Grid(objRS, MSFlexGrid1, True)
            sw = False
        Else
            filas = MSFlexGrid1.Rows
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        End If
          
    End If
    objRS.Close
    
    
    'Deuda cheques
    var_sql = "SELECT '' AS agno,'' AS ci_alu,ci_apo,'' AS deuda,colegio,'cheques protestados' AS observacion FROM apoderados_cheques_protestados WHERE ci_apo = '" & rut_apo & "';"
    objRS.Open var_sql, connLN
    If Not objRS.EOF Then
        If sw = True Then
            Call Rec2Grid(objRS, MSFlexGrid1, True)
            sw = False
        Else
            filas = MSFlexGrid1.Rows
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        End If
          
    End If
    objRS.Close
    
    
    'Lista negra alumnos
    var_sql = "SELECT '' AS agno,f.ci_alu,'' AS ci_apo,'' AS deuda,nombre AS colegio,motivo AS observacion" _
                & " FROM fnza_lista_negra_alumnos f,colegios c" _
                & " WHERE f.colegio = c.colegio AND ci_alu = '" & txt_ci_alu.Text & "';"
    objRS.Open var_sql, connLN
    If Not objRS.EOF Then
        If sw = True Then
            Call Rec2Grid(objRS, MSFlexGrid1, True)
            sw = False
        Else
            filas = MSFlexGrid1.Rows
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        End If
          
    End If
    objRS.Close
    
    
    'Lista negra apoderados
    var_sql = "SELECT '' AS agno,'' AS ci_alu,ci_apo,'' AS deuda,nombre AS colegio,motivo AS observacion" _
            & " FROM fnza_lista_negra_apoderados f,colegios c" _
            & " WHERE f.colegio = c.colegio AND ci_apo = '" & rut_apo & "';"
    objRS.Open var_sql, connLN
    If Not objRS.EOF Then
        If sw = True Then
            Call Rec2Grid(objRS, MSFlexGrid1, True)
            sw = False
        Else
            filas = MSFlexGrid1.Rows
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        End If
          
    End If
    objRS.Close
    
    connLN.Close
    
    'Repitentes
    var_sql = "SELECT agno,ci_alu,'' AS ci_apo,'' AS deuda,'' AS colegio,'Repitente' AS observacion" _
            & " FROM fnza_repitentes WHERE ci_alu = '" & txt_ci_alu.Text & "' AND agno = 2016"
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        If sw = True Then
            Call Rec2Grid(objRS, MSFlexGrid1, True)
            sw = False
        Else
            filas = MSFlexGrid1.Rows
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        End If
          
    End If
    objRS.Close
    
    
    'Caducidad
    var_sql = "SELECT agno,rut AS ci_alu,'' AS ci_apo,'' AS deuda,'' AS colegio,IF(condicional = '',caducidad,condicional) AS observacion" _
            & " FROM fnza_bloqueados_condicionales WHERE rut = '" & txt_ci_alu.Text & "' AND agno = 2016"
    objRS.Open var_sql, conn
    If Not objRS.EOF Then
        If sw = True Then
            Call Rec2Grid(objRS, MSFlexGrid1, True)
            sw = False
        Else
            filas = MSFlexGrid1.Rows
            While Not objRS.EOF
                filas = filas + 1
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                MSFlexGrid1.TextMatrix(filas - 1, 0) = (filas - 1)
                MSFlexGrid1.TextMatrix(filas - 1, 1) = objRS(0)
                MSFlexGrid1.TextMatrix(filas - 1, 2) = objRS(1)
                MSFlexGrid1.TextMatrix(filas - 1, 3) = objRS(2)
                MSFlexGrid1.TextMatrix(filas - 1, 4) = objRS(3)
                MSFlexGrid1.TextMatrix(filas - 1, 5) = objRS(4)
                MSFlexGrid1.TextMatrix(filas - 1, 6) = objRS(5)
                objRS.MoveNext
            Wend
        End If
          
    End If
    objRS.Close
    
    If sw = False Then
        MSFlexGrid1.ColWidth(5) = 2500
        MSFlexGrid1.ColWidth(6) = 5000
    End If
    
    MSFlexGrid1.Refresh
    
    Frame2.Visible = False
    
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 = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", MSFlexGrid1.TextMatrix(i, j))
        
        pgbar.Value = i
        Next j
    Next i
            
    ApExcel.Visible = True
    ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub


Private Sub Form_Load()
'    Dim i As Integer
'    For i = 2011 To Anno - 1
'        cb_agno.AddItem i
'    Next i
'    cb_agno.Text = Anno - 1
End Sub


