VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_Consulta_Titular 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Consultar Titular"
   ClientHeight    =   7995
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   10995
   Icon            =   "Form_Consulta_Titular.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   7995
   ScaleWidth      =   10995
   Begin VB.CommandButton Command3 
      Caption         =   "Limpiar"
      Height          =   495
      Left            =   8880
      TabIndex        =   20
      Top             =   240
      Width           =   1695
   End
   Begin VB.CommandButton Command1 
      Caption         =   "Listar Apoderado"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   8880
      TabIndex        =   19
      Top             =   1080
      Width           =   1695
   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            =   9240
      Picture         =   "Form_Consulta_Titular.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   17
      Top             =   7080
      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            =   240
      Picture         =   "Form_Consulta_Titular.frx":2AED8
      Style           =   1  'Graphical
      TabIndex        =   16
      Top             =   7080
      Width           =   1575
   End
   Begin VB.Frame Frame_Titular 
      Caption         =   "Titulares"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   4455
      Left            =   120
      TabIndex        =   12
      Top             =   2160
      Width           =   10695
      Begin VB.Frame Frame2 
         Caption         =   "Frame2"
         Height          =   2175
         Left            =   960
         TabIndex        =   14
         Top             =   1080
         Visible         =   0   'False
         Width           =   8415
         Begin VB.Label Label5 
            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            =   1920
            TabIndex        =   15
            Top             =   600
            Width           =   5415
         End
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   4095
         Left            =   0
         TabIndex        =   13
         Top             =   240
         Width           =   10575
         _ExtentX        =   18653
         _ExtentY        =   7223
         _Version        =   393216
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "Seleccione Rango de fecha"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   855
      Left            =   120
      TabIndex        =   7
      Top             =   1080
      Width           =   8295
      Begin MSComCtl2.DTPicker DTP_inicio 
         Height          =   315
         Left            =   720
         TabIndex        =   8
         Top             =   360
         Width           =   2295
         _ExtentX        =   4048
         _ExtentY        =   556
         _Version        =   393216
         Format          =   51576833
         CurrentDate     =   42103
      End
      Begin MSComCtl2.DTPicker DTP_hasta 
         Height          =   315
         Left            =   5880
         TabIndex        =   10
         Top             =   360
         Width           =   2295
         _ExtentX        =   4048
         _ExtentY        =   556
         _Version        =   393216
         Format          =   51576833
         CurrentDate     =   42103
      End
      Begin VB.Label Label4 
         Caption         =   "Hasta"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   5280
         TabIndex        =   11
         Top             =   480
         Width           =   615
      End
      Begin VB.Label Label3 
         Caption         =   "Desde"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   120
         TabIndex        =   9
         Top             =   480
         Width           =   615
      End
   End
   Begin VB.ListBox lst_Nombre 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   690
      Left            =   840
      TabIndex        =   6
      Top             =   600
      Visible         =   0   'False
      Width           =   3735
   End
   Begin VB.ListBox lst_RutBusqueda 
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   690
      Left            =   6000
      TabIndex        =   5
      Top             =   600
      Visible         =   0   'False
      Width           =   2175
   End
   Begin VB.Frame Frame_Apo_Caja 
      Caption         =   "Seleccione Titular"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   855
      Left            =   120
      TabIndex        =   0
      Top             =   0
      Width           =   8295
      Begin VB.TextBox txt_NombreCompleto 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   720
         TabIndex        =   2
         Top             =   360
         Width           =   3735
      End
      Begin VB.TextBox txt_RutBusqueda 
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   5880
         TabIndex        =   1
         Top             =   360
         Width           =   2175
      End
      Begin VB.Label Label1 
         Caption         =   "Nombre"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   120
         TabIndex        =   4
         Top             =   360
         Width           =   735
      End
      Begin VB.Label Label2 
         Caption         =   "R.u.t."
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   5280
         TabIndex        =   3
         Top             =   360
         Width           =   615
      End
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   18
      Top             =   6720
      Width           =   10695
      _ExtentX        =   18865
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
End
Attribute VB_Name = "Form_Consulta_Titular"
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 btn_Cancelar_Click()
    Unload Me
End Sub

Private Sub btn_Ver_Apo_Click()
    
End Sub

Private Sub btnOtroApoderado_Click()
   btn_Ver_Apo.Enabled = True
   txt_RutBusqueda.Locked = False
   txt_NombreCompleto.Locked = False
   'limpiar
End Sub



Private Sub cmd_salir_Click()
    Unload Me
End Sub

Private Sub Command1_Click()
    Dim var_sql As String
    Dim conecta As Boolean
    Dim maximo_mes As Integer
    Dim dia As Integer, mes As Integer, agno As Integer
    Dim mes_palabra As String
   
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    Frame2.Visible = True
                    
'    var_sql = "SELECT m.anno_mat as 'Aņo Mat.',m.ci_alu AS 'Rut. Alu.',CONCAT(ape_pat_alu,' ',ape_mat_alu,' ',nom_alu) AS Alumno,a.rut_apo AS 'Rut. Apo.'," _
'                & "CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS Apoderado,fec_mat_r AS 'Fecha Mat.',fp_incorporacion,fp_matricula," _
'                & "fp_mensualidad , usuario_ingreso" _
'                & " FROM matriculas m,apo_x_alu axa,apoderados a,postulantes p" _
'                & " WHERE m.ci_alu = axa.ci_alu AND anno_mat = agno AND axa.rut_apo = a.rut_apo AND m.ci_alu = p.ci_alu AND" _
'                & " fec_mat_r >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & "' and fec_mat_r <= '" & Format(DTP_hasta.Value, "yyyy-mm-dd") & "' AND activo_alu = 'SI'" _
'                & Filtro
    Filtro = ""
    If txt_RutBusqueda.Text <> "" Then
        Filtro = "where c.run_apo = a.rut_apo AND run_titular = '" & txt_RutBusqueda.Text & "'" _
               & " AND fec_ingreso >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & " 00:00:00' AND fec_ingreso <= '" _
               & Format(DTP_hasta.Value, "yyyy-mm-dd") & " 23:59:59'"
    Else
        Filtro = "where c.run_apo = a.rut_apo AND fec_ingreso >= '" & Format(DTP_inicio.Value, "yyyy-mm-dd") & " 00:00:00' AND fec_ingreso <= '" _
               & Format(DTP_hasta.Value, "yyyy-mm-dd") & " 23:59:59'"
    End If
    
                
''    var_sql = "SELECT b.COLEGIO,RUT_APO,APODERADO,RUN_TITULAR,NOM_TITULAR," _
''            & "forma_pago , Concepto, b.MONTO_CHQ, MES_DE, FEC_VENC, Folio, fecha_blta, MONTO_BOL,c.fec_ingreso FEC_ING, c.usuario_ingreso" _
''            & " From" _
''            & " (SELECT nom_est AS COLEGIO,a.rut_apo AS RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO," _
''            & "UCASE(fp.descripcion) AS FORMA_PAGO,IF(cod_concepto = 'inco','C_INCORP.',IF(cod_concepto = 'ct00','MATRICULA','MENSUALIDAD')) AS CONCEPTO," _
''            & "SUM(MONTO_pago) AS MONTO_CHQ,UCASE(ME.MES) AS MES_DE,DATE_FORMAT(fech_venc_cptos,'%d-%M-%Y') AS FEC_VENC,folio_blta_fisico AS FOLIO," _
''            & "fecha_blta As fecha_blta, SUM(Monto_Pago) As MONTO_BOL, fecha_ingreso As FEC_ING, usuario_ingreso, YEAR(fech_venc_cptos) As agno" _
''            & " FROM fnza_boletas b,fnza_dtll_mvto n,fnza_movimientos_mensualidad m,fnza_cod_forma_pago fp,apoderados a,meses me,establecimientos e,postulantes p" _
''            & " Where a.rut_apo = b.run_apo And b.folio_blta = n.folio_blta And n.folio_mvto = m.folio_mvto And b.run_alu = p.ci_alu" _
''            & " AND m.tipo_pago = fp.forma_pago AND DATE_FORMAT(fech_venc_cptos,'%m') = me.num_mes AND fp.descripcion = 'Cheques'" _
''            & " GROUP BY fech_venc_cptos,run_apo,run_alu,me.mes ORDER BY fec_ing,run_apo,run_alu,me.num_mes) b" _
''            & " , (SELECT * FROM fnza_cheques_recibidos " & Filtro & " GROUP BY agno,run_apo) c" _
''            & " where b.rut_apo = c.run_apo AND b.agno = c.agno"
            
    var_sql = "SELECT nom_est AS COLEGIO,RUT_APO,CONCAT(ape1_apo,' ',ape2_apo,' ',noms_apo) AS APODERADO,RUN_TITULAR,NOM_TITULAR," _
            & "MONTO_CHQ,chq_fecha,c.fec_ingreso FEC_ING, c.usuario_ingreso" _
            & " FROM fnza_cheques_recibidos c,apoderados a,establecimientos e " & Filtro
            
               
                
    objRS.Open var_sql, conn
    If objRS.RecordCount > 0 Then
        Call Rec2Grid(objRS, MSFlexGrid1, True)
      
    Else
       MSFlexGrid1.Clear
       MSFlexGrid1.Rows = 2
       MsgBox "No hay titulares en el rango seleccionado", vbInformation
    End If
    objRS.Close
    MSFlexGrid1.Refresh
    
    Frame2.Visible = False
End Sub

Private Sub Command2_Click()
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    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
    ApExcel.Cells(1, 16).Font.Size = 12
    ApExcel.Cells(1, 16).Font.Bold = True
    
    pgbar.Min = 1
    pgbar.Max = MSFlexGrid1.Rows
    pgbar.Value = pgbar.Min
    'ApExcel.Columns("G"). = 300
    ApExcel.Range("G:G").ColumnWidth = 20
    ApExcel.Columns(7).Select
    'ApExcel.Selection.NumberFormat = "#"
    ApExcel.Selection.NumberFormat = "@"
    
'    If cb_factoring.Text <> "" Then
'        ApExcel.Cells(1, 16) = "FACTORING"
'    End If
    
    For i = 1 To MSFlexGrid1.Rows - 1
         For j = 1 To MSFlexGrid1.Cols - 1
            'ApExcel.Cells((i + 1), j).WrapText = True
            ApExcel.Cells((i + 1), j) = IIf(MSFlexGrid1.TextMatrix(i, j) = "Null", "", MSFlexGrid1.TextMatrix(i, j))
        
            pgbar.Value = i
         Next j
'         If cb_factoring.Text <> "" Then
'            ApExcel.Cells((i + 1), j) = cb_factoring.Text
'         End If
    Next i
            
    ApExcel.Visible = True
    'ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
    Set ApExcel = Nothing
End Sub

Private Sub Command3_Click()
    txt_RutBusqueda.Locked = False
    txt_NombreCompleto.Locked = False
    txt_NombreCompleto.Text = ""
    txt_RutBusqueda.Text = ""
End Sub

Private Sub Form_Load()
    DTP_inicio.Value = Date
    DTP_hasta.Value = Date
End Sub

Private Sub lst_nombre_Click()
    Dim dig As String
    dig = digito(lst_Nombre.ItemData(lst_Nombre.ListIndex))
    Dim rut As String
    Dim varsql As String
    rut = lst_Nombre.ItemData(lst_Nombre.ListIndex) & "-" & dig
    If Len(rut) = 9 Then
        rut = "0" & rut
    End If
    'LLena_SSTab_Personal (Rut)
    txt_RutBusqueda.Text = rut
    Dim objRS As ADODB.Recordset
    'Dim varsql As String
    varsql = "SELECT nom_titular" _
           & " FROM fnza_cheques_recibidos where run_titular='" & rut & "'"
    Set objRS = New ADODB.Recordset
    objRS.Open varsql, conn
    If Not objRS.EOF Then
        txt_NombreCompleto.Text = objRS("nom_titular")
    End If
    objRS.Close
    lst_Nombre.Visible = False
    lst_RutBusqueda.Visible = False
    If txt_RutBusqueda.Text <> "" And txt_NombreCompleto.Text <> "" Then
        txt_RutBusqueda.Locked = True
        txt_NombreCompleto.Locked = True
    End If
End Sub

Private Sub lst_RutBusqueda_Click()
    txt_RutBusqueda.Text = lst_RutBusqueda.Text
    'lst_Rut.Visible = False
    Dim objRS As ADODB.Recordset
    Dim varsql As String
    varsql = "SELECT nom_titular" _
           & " FROM fnza_cheques_recibidos where run_titular = '" & txt_RutBusqueda.Text & "'"

    Set objRS = New ADODB.Recordset
        objRS.Open varsql, conn
    If Not objRS.EOF Then
        txt_NombreCompleto.Text = objRS("nom_titular")
    End If
    objRS.Close
    lst_Nombre.Visible = False
    lst_RutBusqueda.Visible = False
    If txt_RutBusqueda.Text <> "" And txt_NombreCompleto.Text <> "" Then
        txt_RutBusqueda.Locked = True
        txt_NombreCompleto.Locked = True
    End If
End Sub

Private Sub txt_NombreCompleto_Change()
    If txt_NombreCompleto.Text <> "" Then
    Dim varsql As String
    Dim objRC As ADODB.Recordset
    Dim rut As Long
    Set objRS = New ADODB.Recordset
    varsql = "SELECT DISTINCT run_titular,nom_titular" _
           & " FROM fnza_cheques_recibidos" _
           & " where nom_titular LIKE '" & txt_NombreCompleto.Text & "%'" _
           & " order by nom_titular"
    objRS.Open varsql, conn
    lst_Nombre.Clear
    If Not objRS.EOF Then
        lst_Nombre.Visible = True
    End If
    lst_Nombre.Visible = True
    While Not objRS.EOF
        lst_Nombre.AddItem objRS("nom_titular")
        lst_Nombre.ItemData(lst_Nombre.NewIndex) = CLng(Mid(objRS("run_titular"), 1, 8))
        objRS.MoveNext
    Wend
    objRS.Close
    End If
End Sub

Private Sub txt_RutBusqueda_Change()
    Dim varsql As String
    Dim objRC As ADODB.Recordset
    Dim rut As Long
    Set objRS = New ADODB.Recordset
    varsql = "SELECT distinct run_titular" _
           & " FROM fnza_cheques_recibidos" _
           & " where run_titular LIKE '" & txt_RutBusqueda.Text & "%'"
    objRS.Open varsql, conn
    lst_RutBusqueda.Clear
    If Not objRS.EOF And txt_RutBusqueda.Text <> "" Then
        lst_RutBusqueda.Visible = True
    End If
    While Not objRS.EOF
        lst_RutBusqueda.AddItem objRS("run_titular")
        objRS.MoveNext
    Wend
    objRS.Close
End Sub
