VERSION 5.00
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
Begin VB.Form Form_Carga_Convenios 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Carga convenios"
   ClientHeight    =   3000
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   3615
   Icon            =   "Form_Carga_Convenios.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   3000
   ScaleWidth      =   3615
   Begin VB.Frame Frame2 
      Caption         =   "Cargar convenios"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00C00000&
      Height          =   2655
      Left            =   240
      TabIndex        =   1
      Top             =   120
      Width           =   3135
      Begin VB.CommandButton Command3 
         Caption         =   "Cuotas Convenios"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   480
         TabIndex        =   4
         Top             =   1800
         Width           =   2175
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Convenios"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   480
         TabIndex        =   3
         Top             =   360
         Width           =   2175
      End
      Begin VB.CommandButton Command2 
         Caption         =   "Pagos convenios"
         BeginProperty Font 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   480
         TabIndex        =   2
         Top             =   1080
         Width           =   2175
      End
      Begin MSComDlg.CommonDialog CommonDialog1 
         Left            =   720
         Top             =   2760
         _ExtentX        =   847
         _ExtentY        =   847
         _Version        =   393216
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "Frame1"
      Height          =   135
      Left            =   120
      TabIndex        =   0
      Top             =   240
      Width           =   15
   End
End
Attribute VB_Name = "Form_Carga_Convenios"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()
Dim tab2 As Integer
Dim i As Integer
Dim var_sql As String
Dim conecta As Boolean
Dim sw As Boolean
Dim sw2 As Boolean

'Tienes que abrir el archivo con...

CommonDialog1.DialogTitle = "Seleccione el archivo de texto"
CommonDialog1.Filter = "Archivos de texto*.txt"
CommonDialog1.ShowOpen

If CommonDialog1.FileName = "" Then
    MsgBox "Debe Seleccionar un Archivo"
  Exit Sub
End If

Open CommonDialog1.FileName For Input As #1

'Luego tienes que leerlo con...

Dim linea As String, total As String
i = 0

conecta = ConectarBDSiempre(conn_deuda, "192.168.7.91", "comunes_gescol", "groot", "kripplefight", True)

conn_deuda.Execute "delete from temp_convenios"

sw = False
sw2 = False

Do Until EOF(1)
        
    Line Input #1, linea
    'Total = Total + Linea + vbCrLf
    'MsgBox Mid(Linea, 1, 11)
    'MsgBox Mid(Linea, 13, 50)
    'Linea.Split (vbTab)
    'tab2 = InStr(1, Mid(Linea, 13), vbTab)
    
    tab1 = InStr(1, Mid(linea, 1), vbTab)
    tab2 = InStr(1, Mid(linea, (tab1 + 1)), vbTab) + tab1
    tab3 = InStr(1, Mid(linea, (tab2 + 1)), vbTab) + tab2
    tab4 = InStr(1, Mid(linea, (tab3 + 1)), vbTab) + tab3
    tab5 = InStr(1, Mid(linea, (tab4 + 1)), vbTab) + tab4
    tab6 = InStr(1, Mid(linea, (tab5 + 1)), vbTab) + tab5
    tab7 = InStr(1, Mid(linea, (tab6 + 1)), vbTab) + tab6
    tab8 = InStr(1, Mid(linea, (tab7 + 1)), vbTab) + tab7
    tab9 = InStr(1, Mid(linea, (tab8 + 1)), vbTab) + tab8
    tab10 = InStr(1, Mid(linea, (tab9 + 1)), vbTab) + tab9
    tab11 = InStr(1, Mid(linea, (tab10 + 1)), vbTab) + tab10
    tab12 = InStr(1, Mid(linea, (tab11 + 1)), vbTab) + tab11
        
    rut_cli = ""
    nom_cli = ""
    nro_conv = ""
    fecha_conv = ""
    nro_ctas = ""
    monto_abon = ""
    monto_conv = ""
    nro_cta_conv = ""
    fecha_venc_conv_cta = ""
    monto_cta = ""
    monto_hon = ""
    id_col = ""
    
    rut_cli = Mid(linea, 1, tab1 - 1)
    nom_cli = Mid(linea, (tab1 + 1), tab2 - (tab1 + 1))
    nro_conv = Mid(linea, (tab2 + 1), tab3 - (tab2 + 1))
    fecha_conv = Mid(linea, (tab3 + 1), tab4 - (tab3 + 1))
    nro_ctas = Mid(linea, (tab4 + 1), tab5 - (tab4 + 1))
    monto_abon = Mid(linea, (tab5 + 1), tab6 - (tab5 + 1))
    monto_conv = Mid(linea, (tab6 + 1), tab7 - (tab6 + 1))
    nro_cta_conv = Mid(linea, (tab7 + 1), tab8 - (tab7 + 1))
    fecha_venc_conv_cta = Mid(linea, (tab8 + 1), tab9 - (tab8 + 1))
    monto_cta = Mid(linea, (tab9 + 1), tab10 - (tab9 + 1))
    monto_hon = Mid(linea, (tab10 + 1), tab11 - (tab10 + 1))
    id_col = Mid(linea, (tab12 + 1))
    
    If sw = False Then
        If Len(rut_cli) < 9 Then sw2 = True
        If val(monto_conv) <= 0 Then sw2 = True
        If val(id_col) <= 0 Then sw2 = True
    End If
    sw = True
    If sw2 = True Then
        MsgBox "Formato de archivo no es valido", vbInformation
        Close #1
        Exit Sub
    End If
                
    var_sql = "insert INTO temp_convenios(rut_cli,nom_cli,nro_conv,fecha_conv,nro_ctas,monto_abon,monto_conv," _
            & "nro_cta_conv,fecha_venc_conv_cta,monto_cta,monto_hon,cod_id_col,id_col)" _
            & "values('" & rut_cli & "','" & nom_cli & "','" & nro_conv & "','" & Format(fecha_conv, "yyyy/mm/dd") & "'," & nro_ctas & "," & monto_abon & "," & monto_conv & "," _
            & nro_cta_conv & ",'" & Format(fecha_venc_conv_cta, "yyyy/mm/dd") & "'," & monto_cta & "," & monto_hon & ",'','" & id_col & "');"
    conn_deuda.Execute var_sql
            
    'Format(fecha_venc_conv_cta, "yyyy/mm/dd")
            
    i = i + 1
    'MsgBox CStr(i) + "-" + CStr(Len(Linea))
        
Loop
Close #1

var_sql = "REPLACE INTO convenios(rut_cli,nom_cli,nro_conv,fecha_conv,nro_ctas,monto_abon,monto_conv,nro_cta_conv,fecha_venc_conv_cta,monto_cta,monto_hon,cod_id_col,id_col)" _
        & " SELECT rut_cli,nom_cli,nro_conv,fecha_conv,nro_ctas,monto_abon,monto_conv,nro_cta_conv,fecha_venc_conv_cta,monto_cta,monto_hon,cod_id_col,id_col FROM temp_convenios;"
conn_deuda.Execute var_sql

MsgBox "Se han insertado " + CStr(i) + " registros", vbInformation
conn_deuda.Close

End Sub

Private Sub Command2_Click()
Dim tab2 As Integer
Dim i As Integer
Dim var_sql As String
Dim conecta As Boolean
Dim sw As Boolean
Dim sw2 As Boolean

'Tienes que abrir el archivo con...

CommonDialog1.DialogTitle = "Seleccione el archivo de texto"
CommonDialog1.Filter = "Archivos de texto*.txt"
CommonDialog1.ShowOpen

If CommonDialog1.FileName = "" Then
    MsgBox "Debe Seleccionar un Archivo"
  Exit Sub
End If


Open CommonDialog1.FileName For Input As #1

'Luego tienes que leerlo con...

Dim linea As String, total As String
i = 0

conecta = ConectarBDSiempre(conn_deuda, "192.168.7.91", "comunes_gescol", "groot", "kripplefight", True)

conn_deuda.Execute "delete from temp_pagos"

sw = False
sw2 = False

Do Until EOF(1)
        
    Line Input #1, linea
    'Total = Total + Linea + vbCrLf
    'MsgBox Mid(Linea, 1, 11)
    'MsgBox Mid(Linea, 13, 50)
   ' Linea.Split (vbTab)
    'tab2 = InStr(1, Mid(Linea, 13), vbTab)
    
    tab1 = InStr(1, Mid(linea, 1), vbTab)
    tab2 = InStr(1, Mid(linea, (tab1 + 1)), vbTab) + tab1
    tab3 = InStr(1, Mid(linea, (tab2 + 1)), vbTab) + tab2
    tab4 = InStr(1, Mid(linea, (tab3 + 1)), vbTab) + tab3
    tab5 = InStr(1, Mid(linea, (tab4 + 1)), vbTab) + tab4
'    tab6 = InStr(1, Mid(linea, (tab5 + 1)), vbTab) + tab5
'    tab7 = InStr(1, Mid(linea, (tab6 + 1)), vbTab) + tab6
'    tab8 = InStr(1, Mid(linea, (tab7 + 1)), vbTab) + tab7
'    tab9 = InStr(1, Mid(linea, (tab8 + 1)), vbTab) + tab8
'    tab10 = InStr(1, Mid(linea, (tab9 + 1)), vbTab) + tab9
'    tab11 = InStr(1, Mid(linea, (tab10 + 1)), vbTab) + tab10
'    tab12 = InStr(1, Mid(linea, (tab11 + 1)), vbTab) + tab11
        
    rut_cli = ""
    nro_conv = ""
    nro_cta_conv = ""
    monto_canc = ""
    fecha_canc = ""
'    monto_abon = ""
'    monto_conv = ""
'    nro_cta_conv = ""
'    fecha_venc_conv_cta = ""
'    monto_cta = ""
'    monto_hon = ""
'    id_col = ""
    
    rut_cli = Mid(linea, 1, tab1 - 1)
    nro_conv = Mid(linea, (tab1 + 1), tab2 - (tab1 + 1))
    nro_cta_conv = Mid(linea, (tab2 + 1), tab3 - (tab2 + 1))
    monto_canc = Mid(linea, (tab3 + 1), tab4 - (tab3 + 1))
    fecha_canc = Mid(linea, (tab5 + 1))
'    monto_abon = Mid(linea, (tab5 + 1), tab6 - (tab5 + 1))
'    monto_conv = Mid(linea, (tab6 + 1), tab7 - (tab6 + 1))
'    nro_cta_conv = Mid(linea, (tab7 + 1), tab8 - (tab7 + 1))
'    fecha_venc_conv_cta = Mid(linea, (tab8 + 1), tab9 - (tab8 + 1))
'    monto_cta = Mid(linea, (tab9 + 1), tab10 - (tab9 + 1))
'    monto_hon = Mid(linea, (tab10 + 1), tab11 - (tab10 + 1))
'    id_col = Mid(linea, (tab12 + 1))

    If sw = False Then
        If Len(rut_cli) < 9 Then sw2 = True
        If val(nro_conv) <= 0 Then sw2 = True
        'If val(id_col) <= 0 Then sw2 = True
    End If
    sw = True
    If sw2 = True Then
        MsgBox "Formato de archivo no es valido", vbInformation
        Close #1
        Exit Sub
    End If
            
    var_sql = "insert INTO temp_pagos(rut_cli,nro_conv,nro_cta_conv,monto_canc,fecha_canc)" _
            & "values('" & rut_cli & "','" & nro_conv & "'," & nro_cta_conv & "," & monto_canc & ",'" & Format(fecha_canc, "yyyy/mm/dd") & "')"
    conn_deuda.Execute var_sql
            
    'Format(fecha_venc_conv_cta, "yyyy/mm/dd")
            
    i = i + 1
    'MsgBox CStr(i) + "-" + CStr(Len(Linea))
        
Loop
Close #1

var_sql = "REPLACE INTO pagos(rut_cli,nro_conv,nro_cta_conv,monto_canc,fecha_canc)" _
        & " SELECT rut_cli,nro_conv,nro_cta_conv,monto_canc,fecha_canc FROM temp_pagos;"
conn_deuda.Execute var_sql

MsgBox "Se han insertado " + CStr(i) + " registros", vbInformation
conn_deuda.Close

End Sub

Private Sub Command3_Click()
Dim tab2 As Integer
Dim i As Integer
Dim var_sql As String
Dim conecta As Boolean
Dim sw As Boolean
Dim sw2 As Boolean

'Tienes que abrir el archivo con...

CommonDialog1.DialogTitle = "Seleccione el archivo de texto"
CommonDialog1.Filter = "Archivos de texto*.txt"
CommonDialog1.ShowOpen

If CommonDialog1.FileName = "" Then
    MsgBox "Debe Seleccionar un Archivo"
  Exit Sub
End If

Open CommonDialog1.FileName For Input As #1

'Luego tienes que leerlo con...

Dim linea As String, total As String
i = 0

conecta = ConectarBDSiempre(conn_deuda, "192.168.7.91", "comunes_gescol", "groot", "kripplefight", True)

conn_deuda.Execute "delete from temp_pagos_cuotas_cuponeras"

sw = False
sw2 = False

Do Until EOF(1)
        
    Line Input #1, linea
    'Total = Total + Linea + vbCrLf
    'MsgBox Mid(Linea, 1, 11)
    'MsgBox Mid(Linea, 13, 50)
   ' Linea.Split (vbTab)
    'tab2 = InStr(1, Mid(Linea, 13), vbTab)
    
    tab1 = InStr(1, Mid(linea, 1), vbTab)
    tab2 = InStr(1, Mid(linea, (tab1 + 1)), vbTab) + tab1
    tab3 = InStr(1, Mid(linea, (tab2 + 1)), vbTab) + tab2
    tab4 = InStr(1, Mid(linea, (tab3 + 1)), vbTab) + tab3
    tab5 = InStr(1, Mid(linea, (tab4 + 1)), vbTab) + tab4
'    tab6 = InStr(1, Mid(linea, (tab5 + 1)), vbTab) + tab5
'    tab7 = InStr(1, Mid(linea, (tab6 + 1)), vbTab) + tab6
'    tab8 = InStr(1, Mid(linea, (tab7 + 1)), vbTab) + tab7
'    tab9 = InStr(1, Mid(linea, (tab8 + 1)), vbTab) + tab8
'    tab10 = InStr(1, Mid(linea, (tab9 + 1)), vbTab) + tab9
'    tab11 = InStr(1, Mid(linea, (tab10 + 1)), vbTab) + tab10
'    tab12 = InStr(1, Mid(linea, (tab11 + 1)), vbTab) + tab11
        
    rut_cli = ""
    nom_cli = ""
    nro_conv = ""
    nro_cupon = ""
    nro_cta_cup = ""
'    monto_abon = ""
'    monto_conv = ""
'    nro_cta_conv = ""
'    fecha_venc_conv_cta = ""
'    monto_cta = ""
'    monto_hon = ""
'    id_col = ""
    
    rut_cli = Mid(linea, 1, tab1 - 1)
    nom_cli = Mid(linea, (tab1 + 1), tab2 - (tab1 + 1))
    nro_conv = Mid(linea, (tab2 + 1), tab3 - (tab2 + 1))
    nro_cupon = Mid(linea, (tab3 + 1), tab4 - (tab3 + 1))
    nro_cta_cup = Mid(linea, (tab5 + 1))
'    monto_abon = Mid(linea, (tab5 + 1), tab6 - (tab5 + 1))
'    monto_conv = Mid(linea, (tab6 + 1), tab7 - (tab6 + 1))
'    nro_cta_conv = Mid(linea, (tab7 + 1), tab8 - (tab7 + 1))
'    fecha_venc_conv_cta = Mid(linea, (tab8 + 1), tab9 - (tab8 + 1))
'    monto_cta = Mid(linea, (tab9 + 1), tab10 - (tab9 + 1))
'    monto_hon = Mid(linea, (tab10 + 1), tab11 - (tab10 + 1))
'    id_col = Mid(linea, (tab12 + 1))

    If sw = False Then
        If Len(rut_cli) < 9 Then sw2 = True
        If val(nro_conv) <= 0 Then sw2 = True
        If val(nro_cta_cup) <= 0 Then sw2 = True
    End If
    sw = True
    If sw2 = True Then
        MsgBox "Formato de archivo no es valido", vbInformation
        Close #1
        Exit Sub
    End If
            
    var_sql = "insert INTO temp_pagos_cuotas_cuponeras(rut_cli,nom_cli,nro_conv,nro_cupon,nro_cta_cup)" _
            & "values('" & rut_cli & "','" & nom_cli & "'," & nro_conv & "," & val(CLng(nro_cupon) + 0) & ",'" & nro_cta_cup & "')"
    conn_deuda.Execute var_sql
            
    'Format(fecha_venc_conv_cta, "yyyy/mm/dd")
            
    i = i + 1
    'MsgBox CStr(i) + "-" + CStr(Len(Linea))
        
Loop
Close #1

var_sql = "REPLACE INTO pagos_cuotas_cuponeras(rut_cli,nom_cli,nro_conv,nro_cupon,nro_cta_cup)" _
        & " SELECT rut_cli,nom_cli,nro_conv,nro_cupon,nro_cta_cup FROM temp_pagos_cuotas_cuponeras;"
conn_deuda.Execute var_sql

MsgBox "Se han insertado " + CStr(i) + " registros", vbInformation
conn_deuda.Close

End Sub

