VERSION 5.00
Object = "{0F987290-56EE-11D0-9C43-00A0C90F29FC}#1.0#0"; "ActBar.ocx"
Object = "{B02F3647-766B-11CE-AF28-C3A2FBE76A13}#2.5#0"; "ss32x25.ocx"
Begin VB.Form FCO2030 
   Caption         =   "Traslados de Administradoras"
   ClientHeight    =   4455
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   9180
   BeginProperty Font 
      Name            =   "Arial"
      Size            =   8.25
      Charset         =   0
      Weight          =   400
      Underline       =   0   'False
      Italic          =   0   'False
      Strikethrough   =   0   'False
   EndProperty
   Icon            =   "Traslados Administradoras.frx":0000
   KeyPreview      =   -1  'True
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   ScaleHeight     =   4455
   ScaleWidth      =   9180
   Begin VB.ComboBox cboEstado 
      Height          =   330
      ItemData        =   "Traslados Administradoras.frx":030A
      Left            =   7650
      List            =   "Traslados Administradoras.frx":0314
      Style           =   2  'Dropdown List
      TabIndex        =   5
      Top             =   210
      Width           =   1125
   End
   Begin VB.ComboBox cboAdmNew 
      Height          =   330
      Left            =   5460
      TabIndex        =   3
      Top             =   210
      Width           =   1245
   End
   Begin VB.ComboBox cboAdmOld 
      Height          =   330
      Left            =   4185
      TabIndex        =   2
      Top             =   210
      Width           =   1275
   End
   Begin FPSpread.vaSpread sprDatos 
      Bindings        =   "Traslados Administradoras.frx":032E
      Height          =   3120
      Left            =   225
      TabIndex        =   4
      Top             =   585
      Width           =   8655
      _Version        =   131077
      _ExtentX        =   15266
      _ExtentY        =   5503
      _StockProps     =   64
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      MaxCols         =   1
      MaxRows         =   1
      SpreadDesigner  =   "Traslados Administradoras.frx":0344
   End
   Begin VB.TextBox txtBusq 
      Height          =   315
      Index           =   0
      Left            =   225
      TabIndex        =   0
      Top             =   210
      Width           =   975
   End
   Begin VB.TextBox txtBusq 
      Height          =   315
      Index           =   1
      Left            =   1230
      TabIndex        =   1
      Top             =   210
      Width           =   2925
   End
   Begin ActiveBarLibraryCtl.ActiveBar abToolbar 
      Left            =   0
      Top             =   0
      _ExtentX        =   847
      _ExtentY        =   847
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Bands           =   "Traslados Administradoras.frx":049D
   End
End
Attribute VB_Name = "FCO2030"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private WithEvents clsSpread As NSpread
Attribute clsSpread.VB_VarHelpID = -1
Private Mado As ODBC.ODBCDirecto
Private MiCx As Integer 'Conexión
Private MiX As Integer 'Ancho de la forma
Private MiY As Integer 'Alta de la forma
Private MBitacora As New ADAMBitacora.Bitacora

Private MbRenovar As Boolean
Private MsSql As String
Private MsSqlIni As String
Private MSOrder As String
Private MbOrdenAsc As Boolean
Private MsAntOrden As String

Private MsTabla As String
Private MsAdmOld() As String
Private MsAdmNew() As String
Private MiKey As Integer
Private MsWhereAdi As String

Private Sub abToolbar_Click(ByVal Tool As ActiveBarLibraryCtl.Tool)
    Select Case LCase(Tool.Name)
        Case "tlalta": Altas
        Case "tlbaja": Bajas
        Case "tlordena": OrdenarDatos
        Case "tlrenueva": AplicaFiltros
        Case "tlguarda": GuardaCambios
        Case "tlsalida": Unload Me
    End Select
End Sub

Private Sub cboAdmNew_Click()
  If cboAdmNew.ListIndex > -1 Or Len(Trim(cboAdmNew)) > 0 Then
    AplicaFiltros
  End If

End Sub

Private Sub cboAdmNew_KeyPress(KeyAscii As Integer)
  If KeyAscii <> vbKeyReturn And KeyAscii <> 10 Then Exit Sub
  'If cboAdmNew.ListIndex > -1 Or Len(Trim(cboAdmNew)) > 0 Then
    MiKey = KeyAscii
    AplicaFiltros
  'End If

End Sub

Private Sub cboAdmOld_Click()
  If cboAdmOld.ListIndex > -1 Or Len(Trim(cboAdmOld)) > 0 Then
    AplicaFiltros
  End If

End Sub

Private Sub cboAdmOld_KeyPress(KeyAscii As Integer)
  If KeyAscii <> vbKeyReturn And KeyAscii <> 10 Then Exit Sub
  'If cboAdmOld.ListIndex > -1 Or Len(Trim(cboAdmOld)) > 0 Then
    MiKey = KeyAscii
    AplicaFiltros
  'End If

End Sub

Private Sub cboEstado_Click()
    AplicaFiltros
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyF3: Unload Me
        Case vbKeyF5: RenuevaDatos
        Case vbKeyF6: Altas
        Case vbKeyF7
            Screen.MousePointer = vbHourglass
            MBitacora.mBorra
            Screen.MousePointer = vbDefault
        Case vbKeyF11: GuardaCambios
        Case vbKeyF12: abToolbar.Bands(1).TrackPopup -1, -1
    End Select
End Sub

Private Sub Form_Load()
    Dim sEstado As String
    
    GbAError = False
    Me.Tag = GiVenMax%
    Gven(Val(Me.Tag)).icx = Gicx%
    MiCx = Gven(Val(Me.Tag)).icx
    Set Mado = Gtc(MiCx).Ado
    If GbSinRegistrar Then RegistraVersion Mado

    'Checa nivel de seguridad
    Gven(Val(Me.Tag)).iNivSeguridad = iObtenNivSeguridad(GsOper, GsUid, MiCx)
    If Gven(Val(Me.Tag)).iNivSeguridad = 0 Then
        Screen.MousePointer = vbDefault
        Me.Top = -10000
        GbAError = True
        Exit Sub
    End If
    
    If Not MDIChild Then
        WindowState = 0
        Move (Screen.Width - Width) \ 2, (Screen.Height - Height) \ 2
        Screen.MousePointer = vbDefault
    End If
       
    'Tamaño de la forma
    If Me.WindowState = 0 Then
        MiX = 9300
        MiY = 4860
        Me.Width = MiX
        Me.Height = MiY
    End If
    
    Set clsSpread = New NSpread
    GAdam.ObtieneColores

    If Mado.DBMS = "SQLSERVER" Then
        sEstado = "CASE WHEN estado = 1 THEN 'Aplicado' ELSE 'Registrado' END"
    Else
        sEstado = "DECODE(estado, 1, 'Aplicado', 'Registrado')"
    End If

    If GsTipo = "EPS" Then
        Caption = GsDsn & " / " & GsAbreviaturaCia & " - Traslado de EPS"
        MsTabla = "fco_traslados_EPS"
        
        MsSqlIni = "SELECT A.Trabajador, REPLACE(B.Nombre,'/', ' '), EPS_Actual, EPS_Nueva, Fecha_Cambio, " & sEstado & _
        " FROM " & MsTabla & " A, Trabajadores B" & _
        " WHERE A.Trabajador = B.Trabajador" & _
        " AND Compania= '" & GsCveCia & "'" & _
        " And A.trabajador In (SELECT trabajador FROM trabajadores_grales" & _
        " WHERE compania = '" & GsCveCia & "' And " & _
        " clase_nomina in (SELECT clase_nomina FROM rel_usu_clase_nom WHERE usuario = '" & GsUid & "'))"
        
    Else
        Caption = GsDsn & " / " & GsAbreviaturaCia & " - Traslado de Fondo de Pensiones"
        MsTabla = "fco_traslados_FPO"
        
        MsSqlIni = "SELECT A.Trabajador, REPLACE(B.Nombre,'/', ' '), FPO_actual, FPO_nueva, Fecha_Cambio, " & sEstado & _
        " FROM " & MsTabla & " A, Trabajadores B" & _
        " WHERE A.Trabajador = B.Trabajador" & _
        " AND A.Compania= '" & GsCveCia & "'" & _
        " And A.trabajador In (SELECT trabajador FROM trabajadores_grales" & _
        " WHERE compania = '" & GsCveCia & "' And " & _
        " clase_nomina in (SELECT clase_nomina FROM rel_usu_clase_nom WHERE usuario = '" & GsUid & "'))"
    End If

    AsignaIconos abToolbar, 3, LALTA, LBAJA, LORDENA, LRENUEVA, LGUARDA, LSALIDA
    ToolInicioGrupo abToolbar, 3, True, 2, 5
    
    CreaBitacora
    MsSql = MsSqlIni
    MsWhereAdi = "" '" AND 1=2"
    
    MSOrder = " ORDER BY fecha_cambio DESC"
            
    cboEstado.ListIndex = 0
    RenuevaDatos
    LlenarCombos
    
End Sub

Private Sub Form_MouseUp(Button As Integer, Shift As Integer, x As Single, y As Single)
    If Button = 2 Then abToolbar.Bands(1).TrackPopup -1, -1
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    Set Mado = Nothing
    Set MBitacora = Nothing
    Set clsSpread = Nothing
    Gtc(MiCx).ihijas = Gtc(MiCx).ihijas - 1
    Set FCO2030 = Nothing
    
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If GbExterno Then End
End Sub

Private Sub sprDatos_Change(ByVal Col As Long, ByVal Row As Long)
    MBitacora.mCambia
End Sub

Private Sub sprDatos_DataFill(ByVal Col As Long, ByVal Row As Long, ByVal DataType As Integer, ByVal fGetData As Integer, Cancel As Integer)
    Dim Aux As Variant
    Select Case Col
        Case 2
            sprDatos.GetDataFillData Aux, 1
            sprDatos.SetText Col, Row, Replace(Aux, "/", " ")
            Cancel = True
    End Select
End Sub

Private Sub txtBusq_KeyPress(Index As Integer, KeyAscii As Integer)
  If KeyAscii <> vbKeyReturn And KeyAscii <> 10 Then Exit Sub
  'If Len(Trim(Me.txtBusq(Index))) > 0 Then
    MiKey = KeyAscii
    AplicaFiltros
  'End If
End Sub

Private Sub txtBusq_KeyUp(Index As Integer, KeyCode As Integer, Shift As Integer)
    Select Case Index
        Case 0: If Trim(txtBusq(1)) <> "" Then txtBusq(1) = ""
        Case 1: If Trim(txtBusq(0)) <> "" Then txtBusq(0) = ""
    End Select
End Sub

Private Sub Altas()
    FCO2030A.Show 1
    GsTrabajador = ""
    
    If Len(Trim(GsTrabajador)) > 0 Then
      MiKey = vbKeyReturn
      LlenarCombos
      Me.txtBusq(0) = GsTrabajador
    End If

      cboEstado.ListIndex = 0
      AplicaFiltros
End Sub

Private Sub Bajas()
    If sprDatos.VirtualMaxRows <= 0 Then Exit Sub
    MBitacora.mBorra
End Sub

Private Sub ConfiguraSpread()
    sprDatos.VisibleCols = 5
    With sprDatos
        .Visible = False
        .Action = 28
        GAdam.ConfiguracionSpread sprDatos
        .Row = 0:
        .Col = 1: .Text = "Trabajador"
        .Col = 2: .Text = "Nombre"
        .Col = 3: .Text = "Actual"
        .Col = 4: .Text = "Nueva"
        .Col = 5: .Text = "Fecha"
        .Col = 6: .Text = "Estado"
        .Col = 2
        .DataFillEvent = True
        ConfiguraSpreadJCL sprDatos
        .Visible = False
    End With
End Sub

Private Sub CreaBitacora()
   MBitacora.mCreaBitacora sprDatos, Mado.lhEnv, Mado.lhDbc, Mado.lhstmt, Mado
   MBitacora.mColTrabajador 1
   MBitacora.mDefineLlave 1
   MBitacora.p_ConfirmaBaja = True
   
   If GsTipo = "EPS" Then
      MBitacora.mTablaSPInfo 1, "sp_fco_baja_traslados_eps", "'" & GsCveCia & "'", 1, 5
      MBitacora.mTablaSPInfo 2, "sp_fco_cambio_traslados_eps", "'" & GsCveCia & "'", 1, 3, 4, 5
   Else
      MBitacora.mTablaSPInfo 1, "sp_fco_baja_traslados_fpo", "'" & GsCveCia & "'", 1, 5
      MBitacora.mTablaSPInfo 2, "sp_fco_cambio_traslados_fpo", "'" & GsCveCia & "'", 1, 3, 4, 5
   End If
   MBitacora.mColNoNulls 1
End Sub

Private Sub GuardaCambios()
   If sprDatos.MaxRows = 0 Then Exit Sub
   Screen.MousePointer = vbHourglass
   MbRenovar = MBitacora.mActualizaBD
   Screen.MousePointer = vbDefault
End Sub

Private Function iDeseaGuardarCambios() As Integer
    ' Descripción : Devuelve el valor de la opción seleccionada en la clsMsgBox
    ' Uso : iDeseaGuardarCambios
    ' Regresa : Un Entero , dependiendo de la respuesta en clsMsgBox
    If MBitacora.pTransActual > 0 Then
       iDeseaGuardarCambios = clsMsgbox.Mensaje("¿Desea guardar los cambios?", mSiNoCancelar, mConfirma, GsTitMsg)
       Select Case iDeseaGuardarCambios
          Case 6
             GuardaCambios
             If MbRenovar = 0 Then iDeseaGuardarCambios = 2
          Case 7
             MBitacora.mVaciaBuffer 'Vacía el Buffer de la Bitácora
       End Select
    Else
        iDeseaGuardarCambios = 1
    End If
End Function

Private Sub OrdenarDatos()
    Screen.MousePointer = vbHourglass
    If MsAntOrden = CStr(sprDatos.ActiveCol) Then
        MbOrdenAsc = Not (MbOrdenAsc)
    Else
        MbOrdenAsc = True
    End If
    MSOrder = " Order By " & CStr(sprDatos.ActiveCol) & IIf(MbOrdenAsc, "", "DESC")
    MsAntOrden = CStr(sprDatos.ActiveCol)
    RenuevaDatos
    Screen.MousePointer = vbDefault
    
End Sub

Private Sub RenuevaDatos()
    ConfiguraSpread
    Mado.Sql = MsSql & MsWhereAdi & MSOrder

    If Not Mado.SendSQL Then Exit Sub
    clsSpread.LlenaSpread 0, sprDatos, Mado, Mado.lhDbc, Mado.lhstmt
        
    With sprDatos
        .ColWidth(1) = 8
        .ColWidth(2) = 25
        .ColWidth(3) = 10
        .ColWidth(4) = 10
        .ColWidth(5) = 8
        .ColWidth(6) = 8
        .Col = 1: .Row = -1: .Lock = True
        .Col = 2: .Row = -1: .Lock = True
        .Col = 3: .Row = -1: .Lock = True
        .Col = 4: .Row = -1: .Lock = True
        .Col = 5: .Row = -1: .Lock = True
        .Col = 6: .Row = -1: .Lock = True
        .ScrollBars = ScrollBarsBoth
        .Visible = True
    End With
    GAdam.AjustaSpread sprDatos, 6
End Sub

Private Sub AplicaFiltros()
    MsSql = MsSqlIni
    MsWhereAdi = ""
                    
    If Trim(txtBusq(0)) <> "" Then
        Select Case MiKey
            Case vbKeyReturn
                MsWhereAdi = " AND A.trabajador Like '%" & txtBusq(0) & "%'"
            Case 10
                MsWhereAdi = " AND A.trabajador Like '" & txtBusq(0) & "%'"
        End Select
    End If
                               
    If Trim(txtBusq(1)) <> "" Then
        Select Case MiKey
            Case vbKeyReturn
                MsWhereAdi = MsWhereAdi & " AND B.nombre Like '%" & Replace(txtBusq(1), "/", " ") & "%'"
            Case 10
                MsWhereAdi = MsWhereAdi & " AND B.nombre Like '" & Replace(txtBusq(1), "/", " ") & "%'"
        End Select
    End If
    
    If GsTipo = "EPS" Then
        If Trim(cboAdmOld.Text) <> "" Then
            MsWhereAdi = MsWhereAdi & " AND EPS_actual = '" & cboAdmOld.Text & "'"
        End If
        
        If Trim(cboAdmNew.Text) <> "" Then
            MsWhereAdi = MsWhereAdi & " AND EPS_nueva = '" & cboAdmNew.Text & "'"
        End If
    Else
        If Trim(cboAdmOld.Text) <> "" Then
            MsWhereAdi = MsWhereAdi & " AND FPO_actual = '" & cboAdmOld.Text & "'"
        End If
        
        If Trim(cboAdmNew.Text) <> "" Then
            MsWhereAdi = MsWhereAdi & " AND FPO_nueva = '" & cboAdmNew.Text & "'"
        End If
    End If
    
    If cboEstado.ListIndex = 0 Then
        MsWhereAdi = MsWhereAdi & " AND estado <> 1"
    Else
        MsWhereAdi = MsWhereAdi & " AND estado  = 1"
    End If

    RenuevaDatos
End Sub

Private Sub LlenarCombos()
cboAdmOld.Clear
cboAdmNew.Clear
    
If GsTipo = "EPS" Then
    Mado.Sql = _
    "SELECT DISTINCT a.EPS_actual " & _
    "FROM fco_traslados_eps a WHERE compania = '" & GsCveCia & "' ORDER BY 1"
    If Not Mado.SendSQL Then Exit Sub
    Do Until Mado.EOF
        cboAdmOld.AddItem Mado.GetField(1)
        ReDim Preserve MsAdmOld(cboAdmOld.NewIndex)
        Mado.Mnext
    Loop

    Mado.Sql = _
    "SELECT DISTINCT a.EPS_nueva " & _
    "FROM fco_traslados_eps a WHERE compania = '" & GsCveCia & "' ORDER BY 1"
    If Not Mado.SendSQL Then Exit Sub
    Do Until Mado.EOF
        cboAdmNew.AddItem Mado.GetField(1)
        ReDim Preserve MsAdmNew(cboAdmNew.NewIndex)
        Mado.Mnext
    Loop
Else
    Mado.Sql = _
    "SELECT DISTINCT a.FPO_actual " & _
    "FROM fco_traslados_fpo a WHERE compania = '" & GsCveCia & "' ORDER BY 1"
    If Not Mado.SendSQL Then Exit Sub
    Do Until Mado.EOF
        cboAdmOld.AddItem Mado.GetField(1)
        ReDim Preserve MsAdmOld(cboAdmOld.NewIndex)
        Mado.Mnext
    Loop

    Mado.Sql = _
    "SELECT DISTINCT a.FPO_nueva " & _
    "FROM fco_traslados_fpo a WHERE compania = '" & GsCveCia & "' ORDER BY 1"
    If Not Mado.SendSQL Then Exit Sub
    Do Until Mado.EOF
        cboAdmNew.AddItem Mado.GetField(1)
        ReDim Preserve MsAdmNew(cboAdmNew.NewIndex)
        Mado.Mnext
    Loop

End If
End Sub


