VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "MsComCtl.ocx"
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSComCt2.ocx"
Begin VB.Form frmCanjeFecha 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Canjes por Fecha"
   ClientHeight    =   8280
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   10785
   Icon            =   "frmCanjeFecha.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   ScaleHeight     =   8280
   ScaleWidth      =   10785
   StartUpPosition =   2  'CenterScreen
   Begin VB.CommandButton cmdMensaje 
      Caption         =   "Espere un momento. Generando Reporte ..."
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   3000
      TabIndex        =   15
      Top             =   7680
      Visible         =   0   'False
      Width           =   4695
   End
   Begin VB.CommandButton cmdExcel 
      Caption         =   "&Editar en Excel"
      Height          =   375
      Left            =   7800
      TabIndex        =   11
      Top             =   7800
      Width           =   1335
   End
   Begin VB.CommandButton cmdCliente 
      Caption         =   "&Ver Cliente"
      Height          =   375
      Index           =   1
      Left            =   1560
      TabIndex        =   10
      Top             =   7800
      Width           =   1335
   End
   Begin VB.CommandButton cmdCliente 
      Caption         =   "&Ver Cliente"
      Height          =   375
      Index           =   0
      Left            =   1560
      TabIndex        =   7
      Top             =   3960
      Width           =   1335
   End
   Begin VB.CommandButton cmdSalir 
      Caption         =   "&Salir"
      Height          =   375
      Left            =   9240
      TabIndex        =   12
      Top             =   7800
      Width           =   1335
   End
   Begin VB.CommandButton cmdExtornar 
      Caption         =   "E&xtornar"
      Height          =   375
      Index           =   0
      Left            =   120
      TabIndex        =   6
      Top             =   3960
      Width           =   1335
   End
   Begin VB.CommandButton cmdExtornar 
      Caption         =   "Ext&ornar"
      Height          =   375
      Index           =   1
      Left            =   120
      TabIndex        =   9
      Top             =   7800
      Width           =   1335
   End
   Begin VB.CommandButton cmdBuscar 
      Caption         =   "B&uscar"
      Default         =   -1  'True
      Height          =   375
      Left            =   4440
      TabIndex        =   4
      Top             =   120
      Width           =   1335
   End
   Begin MSComCtl2.DTPicker dtpFInicio 
      Height          =   315
      Left            =   720
      TabIndex        =   1
      Top             =   120
      Width           =   1215
      _ExtentX        =   2143
      _ExtentY        =   556
      _Version        =   393216
      CalendarForeColor=   -2147483640
      CalendarTitleBackColor=   -2147483645
      CalendarTitleForeColor=   -2147483629
      CalendarTrailingForeColor=   -2147483645
      CustomFormat    =   "dd/MM/yyyy"
      Format          =   23658499
      CurrentDate     =   37042
   End
   Begin MSComCtl2.DTPicker dtpFFin 
      Height          =   315
      Left            =   3000
      TabIndex        =   3
      Top             =   120
      Width           =   1215
      _ExtentX        =   2143
      _ExtentY        =   556
      _Version        =   393216
      CalendarForeColor=   -2147483640
      CalendarTitleBackColor=   -2147483645
      CalendarTitleForeColor=   -2147483629
      CalendarTrailingForeColor=   -2147483645
      CustomFormat    =   "dd/MM/yyyy"
      Format          =   23658499
      CurrentDate     =   37042
   End
   Begin MSComctlLib.ListView lvwCanjePunto 
      Height          =   2985
      Left            =   120
      TabIndex        =   5
      Top             =   840
      Width           =   10455
      _ExtentX        =   18441
      _ExtentY        =   5265
      LabelEdit       =   1
      LabelWrap       =   -1  'True
      HideSelection   =   0   'False
      _Version        =   393217
      Icons           =   "imlCanje"
      SmallIcons      =   "imlCanje"
      ForeColor       =   -2147483640
      BackColor       =   -2147483643
      BorderStyle     =   1
      Appearance      =   1
      NumItems        =   0
   End
   Begin MSComctlLib.ListView lvwCanjeTicket 
      Height          =   2985
      Left            =   120
      TabIndex        =   8
      Top             =   4680
      Width           =   10455
      _ExtentX        =   18441
      _ExtentY        =   5265
      LabelEdit       =   1
      LabelWrap       =   -1  'True
      HideSelection   =   0   'False
      _Version        =   393217
      Icons           =   "imlCanje"
      SmallIcons      =   "imlCanje"
      ForeColor       =   -2147483640
      BackColor       =   -2147483643
      BorderStyle     =   1
      Appearance      =   1
      NumItems        =   0
   End
   Begin MSComctlLib.ImageList imlCanje 
      Left            =   7440
      Top             =   0
      _ExtentX        =   1005
      _ExtentY        =   1005
      BackColor       =   -2147483643
      ImageWidth      =   16
      ImageHeight     =   16
      MaskColor       =   12632256
      _Version        =   393216
      BeginProperty Images {2C247F25-8591-11D1-B16A-00C0F0283628} 
         NumListImages   =   2
         BeginProperty ListImage1 {2C247F27-8591-11D1-B16A-00C0F0283628} 
            Picture         =   "frmCanjeFecha.frx":0442
            Key             =   ""
         EndProperty
         BeginProperty ListImage2 {2C247F27-8591-11D1-B16A-00C0F0283628} 
            Picture         =   "frmCanjeFecha.frx":0894
            Key             =   ""
         EndProperty
      EndProperty
   End
   Begin VB.Label lblLabels 
      Alignment       =   2  'Center
      BorderStyle     =   1  'Fixed Single
      Caption         =   "Canje  Puntos"
      Height          =   255
      Index           =   2
      Left            =   120
      TabIndex        =   14
      Top             =   600
      Width           =   10455
   End
   Begin VB.Label lblLabels 
      Alignment       =   2  'Center
      BorderStyle     =   1  'Fixed Single
      Caption         =   "Canje Tickets"
      Height          =   255
      Index           =   3
      Left            =   120
      TabIndex        =   13
      Top             =   4440
      Width           =   10455
   End
   Begin VB.Label lblLabels 
      Caption         =   "Al"
      Height          =   255
      Index           =   1
      Left            =   2520
      TabIndex        =   2
      Top             =   240
      Width           =   495
   End
   Begin VB.Label lblLabels 
      Caption         =   "Del"
      Height          =   255
      Index           =   0
      Left            =   120
      TabIndex        =   0
      Top             =   240
      Width           =   255
   End
End
Attribute VB_Name = "frmCanjeFecha"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public dFInicio As Date
Public dFFin As Date

Private Sub Form_Activate()

  On Error GoTo LineaError
  
  'Form Actual
  psForm = kFORM_CANJE_FECHA
  
  Exit Sub
  
LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation
  
End Sub

Private Sub Form_Load()

  'Carga el formulario
  
  On Error GoTo LineaError

  Screen.MousePointer = vbHourglass
  
  Call ConfigurarColumnas
  Call InicializarForm
  
  Screen.MousePointer = vbDefault
  
  Exit Sub
  
LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation
  Screen.MousePointer = vbDefault
  
End Sub

Private Sub ConfigurarColumnas()

  On Error GoTo LineaError

  Dim aColumnTitle() As String
  Dim aColumnWidth() As Single
  Dim aColumnAlign() As Integer
  
  ReDim aColumnTitle(9) As String
  ReDim aColumnWidth(9) As Single
  ReDim aColumnAlign(9) As Integer

  aColumnTitle(0) = "Fecha Canje"
  aColumnTitle(1) = "Tarjeta"
  aColumnTitle(2) = "Cliente"
  aColumnTitle(3) = "Premio"
  aColumnTitle(4) = "Cantidad"
  aColumnTitle(5) = "Puntos Canj."
  aColumnTitle(6) = "Usuario"
  aColumnTitle(7) = "Fecha Extorno"
  aColumnTitle(8) = "Usuario Extorno"
  
  aColumnWidth(0) = 0.13
  aColumnWidth(1) = 0.09
  aColumnWidth(2) = 0.2
  aColumnWidth(3) = 0.2
  aColumnWidth(4) = 0.09
  aColumnWidth(5) = 0.09
  aColumnWidth(6) = 0.12
  aColumnWidth(7) = 0.12
  aColumnWidth(8) = 0.12
    
  aColumnAlign(0) = lvwColumnLeft
  aColumnAlign(1) = lvwColumnLeft
  aColumnAlign(2) = lvwColumnLeft
  aColumnAlign(3) = lvwColumnLeft
  aColumnAlign(4) = lvwColumnRight
  aColumnAlign(5) = lvwColumnRight
  aColumnAlign(6) = lvwColumnLeft
  aColumnAlign(7) = lvwColumnLeft
  aColumnAlign(8) = lvwColumnLeft
    
  Call oListAdmin.InicializarLista(lvwCanjePunto, aColumnTitle, _
                                   aColumnWidth, aColumnAlign, lvwReport)
                                   
  ReDim aColumnTitle(9) As String
  ReDim aColumnWidth(9) As Single
  ReDim aColumnAlign(9) As Integer

  aColumnTitle(0) = "Fecha Canje"
  aColumnTitle(1) = "Tarjeta"
  aColumnTitle(2) = "Cliente"
  aColumnTitle(3) = "Premio"
  aColumnTitle(4) = "Cantidad"
  aColumnTitle(5) = "Tickets Canj."
  aColumnTitle(6) = "Usuario"
  aColumnTitle(7) = "Fecha Extorno"
  aColumnTitle(8) = "Usuario Extorno"
  
  aColumnWidth(0) = 0.13
  aColumnWidth(1) = 0.09
  aColumnWidth(2) = 0.2
  aColumnWidth(3) = 0.2
  aColumnWidth(4) = 0.09
  aColumnWidth(5) = 0.09
  aColumnWidth(6) = 0.12
  aColumnWidth(7) = 0.12
  aColumnWidth(8) = 0.12
    
  aColumnAlign(0) = lvwColumnLeft
  aColumnAlign(1) = lvwColumnLeft
  aColumnAlign(2) = lvwColumnLeft
  aColumnAlign(3) = lvwColumnLeft
  aColumnAlign(4) = lvwColumnRight
  aColumnAlign(5) = lvwColumnRight
  aColumnAlign(6) = lvwColumnLeft
  aColumnAlign(7) = lvwColumnLeft
  aColumnAlign(8) = lvwColumnLeft
    
  Call oListAdmin.InicializarLista(lvwCanjeTicket, aColumnTitle, _
                                   aColumnWidth, aColumnAlign, lvwReport)
                                   
  Exit Sub
  
LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

End Sub

Private Sub InicializarForm()

  'Inicializa el Form
  
  On Error GoTo LineaError
  
  Call CentrarControl(Me, cmdMensaje)
  
  dtpFInicio.Value = Date - 15
  dtpFFin.Value = Date

  Call cmdBuscar_Click
  
  Exit Sub
  
LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation
  
End Sub

Public Sub cmdBuscar_Click()

  'Buscar Acumulacion según rango de fecha

  On Error GoTo LineaError

  lblLabels(2).Caption = "Canje Puntos"
  lblLabels(3).Caption = "Canje Tickets"
  
  lvwCanjePunto.ListItems.Clear
  lvwCanjeTicket.ListItems.Clear

  LlenarListaCanjePunto
  LlenarListaCanjeTicket
  
  dFInicio = dtpFInicio.Value
  dFFin = dtpFFin.Value
  
  ManejarControles

  Exit Sub

LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

End Sub

Public Sub LlenarListaCanjePunto()

  'Llena la lista CanjePunto

  On Error GoTo LineaError

  Dim sSQL As String
  Dim ADORS As ADODB.Recordset

  Dim itemX As ListItem

  sSQL = "SELECT a.*, sPremDesc, sClieApel, sClieName " + _
         "FROM Canje_Punto a, Premio_Punto b, Cliente c " + _
         "WHERE a.cPremCode = b.cPremCode AND " + _
         "a.cClieCode = c.cClieCode AND " + _
         "FORMAT(dCanjDate, 'yyyy/mm/dd') >= '$' AND " + _
         "FORMAT(dCanjDate, 'yyyy/mm/dd') <= '$' " + _
         "Order by dCanjDate DESC, sClieApel + ' ' + sClieName, sPremDesc"
  sSQL = VBsprintf(sSQL, Format(dtpFInicio.Value, "yyyy/mm/dd"), _
         Format(dtpFFin.Value, "yyyy/mm/dd"))
  If Not oDBAdmin.EjecutarConsulta(ADOCnn, sSQL, ADORS) Then GoTo Salir

  Do While Not ADORS.EOF

    Set itemX = lvwCanjePunto.ListItems.Add(, "k" + RPad(LeerCampo(ADORS!cClieCode), 10, " ") + LeerCampo(ADORS!cCanjCode), Format(LeerCampo(ADORS!dCanjDate), "dd/mm/yyyy hh:mm"))
    itemX.SubItems(1) = LeerCampo(ADORS!nTarjNumb)
    itemX.SubItems(2) = LeerCampo(ADORS!sClieApel) + " " + LeerCampo(ADORS!sClieName)
    itemX.SubItems(3) = LeerCampo(ADORS!sPremDesc)
    itemX.SubItems(4) = Format(LeerCampo(ADORS!nPremCant), "##,##0")
    itemX.SubItems(5) = Format(LeerCampo(ADORS!nPuntCant), "##,##0")
    itemX.SubItems(6) = LeerCampo(ADORS!sUserDesc)
    
    If LeerCampo(ADORS!dExtoDate) = "" Then
      itemX.Icon = 1
      itemX.SmallIcon = 1
    Else
      itemX.SubItems(7) = Format(LeerCampo(ADORS!dExtoDate), "dd/mm/yyyy hh:mm")
      itemX.SubItems(8) = LeerCampo(ADORS!sUseEDesc)
      itemX.Icon = 2
      itemX.SmallIcon = 2
    End If

    ADORS.MoveNext

  Loop

  lblLabels(2).Caption = "Canje Puntos   -   Del " + Format(dtpFInicio.Value, "dd/mm/yyyy") + " Al " + Format(dtpFFin.Value, "dd/mm/yyyy")
  
  GoTo Salir

LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation
  
Salir:
  Set ADORS = Nothing

End Sub

Public Sub LlenarListaCanjeTicket()

  'Llena la lista CanjeTicket

  On Error GoTo LineaError

  Dim sSQL As String
  Dim ADORS As ADODB.Recordset

  Dim itemX As ListItem

  sSQL = "SELECT a.*, sPremDesc, sClieApel, sClieName " + _
         "FROM Canje_Ticket a, Premio_Ticket b, Cliente c " + _
         "WHERE a.cPremCode = b.cPremCode AND " + _
         "a.cClieCode = c.cClieCode AND " + _
         "FORMAT(dCanjDate, 'yyyy/mm/dd') >= '$' AND " + _
         "FORMAT(dCanjDate, 'yyyy/mm/dd') <= '$' " + _
         "Order by dCanjDate DESC, sClieApel + ' ' + sClieName"
  sSQL = VBsprintf(sSQL, Format(dtpFInicio.Value, "yyyy/mm/dd"), _
         Format(dtpFFin.Value, "yyyy/mm/dd"))
  If Not oDBAdmin.EjecutarConsulta(ADOCnn, sSQL, ADORS) Then GoTo Salir

  Do While Not ADORS.EOF

    Set itemX = lvwCanjeTicket.ListItems.Add(, "k" + RPad(LeerCampo(ADORS!cClieCode), 10, " ") + RPad(LeerCampo(ADORS!cCanjCode), 10, " ") + LeerCampo(ADORS!cPremCode), Format(LeerCampo(ADORS!dCanjDate), "dd/mm/yyyy hh:mm"))
    itemX.SubItems(1) = LeerCampo(ADORS!nTarjNumb)
    itemX.SubItems(2) = LeerCampo(ADORS!sClieApel) + " " + LeerCampo(ADORS!sClieName)
    itemX.SubItems(3) = LeerCampo(ADORS!sPremDesc)
    itemX.SubItems(4) = Format(LeerCampo(ADORS!nPremCant), "##,##0")
    itemX.SubItems(5) = Format(LeerCampo(ADORS!nTickCant), "##,##0")
    itemX.SubItems(6) = LeerCampo(ADORS!sUserDesc)
    
    If LeerCampo(ADORS!dExtoDate) = "" Then
      itemX.Icon = 1
      itemX.SmallIcon = 1
    Else
      itemX.SubItems(7) = Format(LeerCampo(ADORS!dExtoDate), "dd/mm/yyyy hh:mm")
      itemX.SubItems(8) = LeerCampo(ADORS!sUseEDesc)
      itemX.Icon = 2
      itemX.SmallIcon = 2
    End If

    ADORS.MoveNext

  Loop

  lblLabels(3).Caption = "Canje Tickets   -   Del " + Format(dtpFInicio.Value, "dd/mm/yyyy") + " Al " + Format(dtpFFin.Value, "dd/mm/yyyy")
  
  GoTo Salir

LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation
  
Salir:
  Set ADORS = Nothing

End Sub

Public Sub ManejarControles()

  'Maneja el uso de los controles

  On Error GoTo LineaError

  Call DeshabilitarControl(cmdExtornar(kPUNTO), False)
  Call DeshabilitarControl(cmdExtornar(kTICKET), False)
  
  Call DeshabilitarControl(cmdExcel, False)
  
  If piPerfil_Codigo = kPERFIL_ADMINISTRADOR Or piPerfil_Codigo = kPERFIL_SUPERVISOR Then

    If lvwCanjePunto.ListItems.Count > 0 Then
      lvwCanjePunto.SelectedItem.EnsureVisible
      Call HabilitarControl(cmdExtornar(kPUNTO), False)
    End If
    
    If lvwCanjeTicket.ListItems.Count > 0 Then
      lvwCanjeTicket.SelectedItem.EnsureVisible
      Call HabilitarControl(cmdExtornar(kTICKET), False)
    End If
    
    If lvwCanjePunto.ListItems.Count > 0 Or lvwCanjeTicket.ListItems.Count > 0 Then
    Call HabilitarControl(cmdExcel, False)
  End If

  End If
  
  Exit Sub

LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

End Sub

Private Sub lvwCanjePunto_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

  'Ordenar Según la columna seleccionada

  On Error GoTo LineaError

  If ColumnHeader.Index = 1 Or ColumnHeader.Index = 8 Then

    Call APISort(lvwCanjePunto, ColumnHeader.Index, _
                 gbytLVW_SORT_DATE)

  ElseIf ColumnHeader.Index = 5 Or ColumnHeader.Index = 6 Then

    Call APISort(lvwCanjePunto, ColumnHeader.Index, _
                 gbytLVW_SORT_VALUE)

  Else

    Call APISort(lvwCanjePunto, ColumnHeader.Index, _
                 gbytLVW_SORT_DEFAULT)

  End If
  Exit Sub

LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

End Sub

Private Sub lvwCanjeTicket_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

  'Ordenar Según la columna seleccionada

  On Error GoTo LineaError

  If ColumnHeader.Index = 1 Or ColumnHeader.Index = 8 Then

    Call APISort(lvwCanjeTicket, ColumnHeader.Index, _
                 gbytLVW_SORT_DATE)

  ElseIf ColumnHeader.Index = 5 Or ColumnHeader.Index = 6 Then

    Call APISort(lvwCanjeTicket, ColumnHeader.Index, _
                 gbytLVW_SORT_VALUE)

  Else

    Call APISort(lvwCanjeTicket, ColumnHeader.Index, _
                 gbytLVW_SORT_DEFAULT)

  End If

  Exit Sub

LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

End Sub

Private Sub cmdExtornar_Click(Index As Integer)

  'Extornar

  On Error GoTo LineaError
  
  If Index = kPUNTO Then
    ExtornarCanjePunto
  Else
    ExtornarCanjeTicket
  End If
  
  Screen.MousePointer = vbDefault

  Exit Sub

LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation
  Screen.MousePointer = vbDefault

End Sub

Private Sub ExtornarCanjePunto()

  'Extornar Canje de Puntos

  On Error GoTo LineaError

  Dim sSQL As String
  Dim ADORS As ADODB.Recordset

  Dim itemX As ListItem
  Dim bConnFlag As Boolean

  Screen.MousePointer = vbHourglass

  If lvwCanjePunto.SelectedItem.SmallIcon = 2 Then
    MsgBox "Transacción ya ha sido extornada", vbOKOnly + vbInformation
    Exit Sub
  End If

  If MsgBox("Se extornará transacción seleccionada (Fecha: " + lvwCanjePunto.SelectedItem.Text + " - Cliente: " + lvwCanjePunto.SelectedItem.SubItems(2) + " - Premio: " + lvwCanjePunto.SelectedItem.SubItems(3) + " - Cantidad: " + lvwCanjePunto.SelectedItem.SubItems(4) + " - Puntos Canjeados: " + lvwCanjePunto.SelectedItem.SubItems(5) + "). Confirma Acción?", _
  vbQuestion + vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub

  ADOCnn.BeginTrans
  bConnFlag = True

  sSQL = "UPDATE Canje_Punto SET dExtoDate = '$', sUseEDesc = '$' " + _
         "WHERE cClieCode = $ AND cCanjCode = $"
  sSQL = VBsprintf(sSQL, Format(Now, "yyyy/mm/dd hh:mm"), ReemplazarComilla(psUsuario_Descripcion), Mid(lvwCanjePunto.SelectedItem.Key, 2, 10), _
         Mid(lvwCanjePunto.SelectedItem.Key, 12))

  If Not oDBAdmin.EjecutarSentencia(ADOCnn, sSQL) Then GoTo LineaError1

  sSQL = "UPDATE Cliente SET nPuntCant = nPuntCant + $ " + _
         "WHERE cClieCode = $"
  sSQL = VBsprintf(sSQL, Format(lvwCanjePunto.SelectedItem.SubItems(5), "####0"), _
         Mid(lvwCanjePunto.SelectedItem.Key, 2, 10))

  If Not oDBAdmin.EjecutarSentencia(ADOCnn, sSQL) Then GoTo LineaError1
  
  ADOCnn.CommitTrans
  bConnFlag = False

  Set itemX = lvwCanjePunto.SelectedItem

  itemX.SubItems(7) = Format(Now, "dd/mm/yyyy hh:mm")
  itemX.SubItems(8) = psUsuario_Descripcion
  itemX.Icon = 2
  itemX.SmallIcon = 2
  
  Exit Sub
  
LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

LineaError1:
  If bConnFlag Then ADOCnn.RollbackTrans

End Sub

Private Sub ExtornarCanjeTicket()

  'Extornar Canje de Tickets

  On Error GoTo LineaError

  Dim sSQL As String
  Dim ADORS As ADODB.Recordset

  Dim itemX As ListItem
  Dim bConnFlag As Boolean

  Screen.MousePointer = vbHourglass

  If lvwCanjeTicket.SelectedItem.SmallIcon = 2 Then
    MsgBox "Transacción ya ha sido extornada", vbOKOnly + vbInformation
    Exit Sub
  End If

  If MsgBox("Se extornará transacción seleccionada (Fecha: " + lvwCanjeTicket.SelectedItem.Text + " - Cliente: " + lvwCanjeTicket.SelectedItem.SubItems(2) + " - Premio: " + lvwCanjeTicket.SelectedItem.SubItems(3) + " - Cantidad: " + lvwCanjeTicket.SelectedItem.SubItems(4) + " - Tickets Canjeados: " + lvwCanjeTicket.SelectedItem.SubItems(5) + "). Confirma Acción?", _
  vbQuestion + vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub

  ADOCnn.BeginTrans
  bConnFlag = True

  sSQL = "UPDATE Canje_Ticket SET dExtoDate = '$', sUseEDesc = '$' " + _
         "WHERE cClieCode = $ AND cCanjCode = $"
  sSQL = VBsprintf(sSQL, Format(Now, "yyyy/mm/dd hh:mm"), ReemplazarComilla(psUsuario_Descripcion), Mid(lvwCanjeTicket.SelectedItem.Key, 2, 10), _
         Mid(lvwCanjeTicket.SelectedItem.Key, 12, 10))

  If Not oDBAdmin.EjecutarSentencia(ADOCnn, sSQL) Then GoTo LineaError1

  sSQL = "UPDATE Cliente SET nTickCant = nTickCant + $ " + _
         "WHERE cClieCode = $"
  sSQL = VBsprintf(sSQL, Format(lvwCanjeTicket.SelectedItem.SubItems(5), "####0"), _
         Mid(lvwCanjeTicket.SelectedItem.Key, 2, 10))

  If Not oDBAdmin.EjecutarSentencia(ADOCnn, sSQL) Then GoTo LineaError1
  
  sSQL = "UPDATE Premio_Ticket SET nPremStoc = nPremStoc + $ " + _
         "WHERE cPremCode = $"
  sSQL = VBsprintf(sSQL, Format(lvwCanjeTicket.SelectedItem.SubItems(4), "####0"), _
         Mid(lvwCanjeTicket.SelectedItem.Key, 22))

  If Not oDBAdmin.EjecutarSentencia(ADOCnn, sSQL) Then GoTo LineaError1

  ADOCnn.CommitTrans
  bConnFlag = False

  Set itemX = lvwCanjeTicket.SelectedItem

  itemX.SubItems(7) = Format(Now, "dd/mm/yyyy hh:mm")
  itemX.SubItems(8) = psUsuario_Descripcion
  itemX.Icon = 2
  itemX.SmallIcon = 2
  
  Exit Sub
  
LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

LineaError1:
  If bConnFlag Then ADOCnn.RollbackTrans

End Sub

Private Sub cmdCliente_Click(Index As Integer)

  'Invoca al formulario Usuario
  
  On Error GoTo LineaError
  
  psLista = Index
  frmCliente.iEditar = kCMD_VER
  frmCliente.Show vbModal
  
  Exit Sub
  
LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

End Sub

Private Sub CmdExcel_Click()

  'Editar en Hoja Excel

  On Error GoTo LineaError

  Dim xlApp As Excel.Application
  Dim xlBook As Excel.Workbook
  Dim xlSheet1 As Excel.Worksheet
  Dim xlSheet2 As Excel.Worksheet
  Dim xlSheetTemp As Excel.Worksheet
  
  Dim lFila1 As Long
  Dim lFila2 As Long
  Dim i As Long
  Dim lRegistros1 As Long
  Dim lRegistros2 As Long
  
  Screen.MousePointer = vbHourglass
  
  cmdMensaje.Visible = True
  
  Set xlApp = CreateObject("excel.application")
  Set xlBook = xlApp.Workbooks.Add
  Set xlSheet1 = xlBook.Worksheets.Add
  Set xlSheet2 = xlBook.Worksheets.Add
  
  xlSheet1.Name = "Puntos"
  xlSheet2.Name = "Tickets"
  
  xlBook.Application.DisplayAlerts = False
  
  For Each xlSheetTemp In xlBook.Worksheets
    If xlSheetTemp.Name <> xlSheet1.Name And xlSheetTemp.Name <> xlSheet2.Name Then
      xlSheetTemp.Delete
    End If
  Next xlSheetTemp
  
  xlBook.Application.DisplayAlerts = True
  
  'Formato Página
  xlSheet1.PageSetup.Orientation = xlLandscape
  xlSheet1.PageSetup.LeftMargin = Application.InchesToPoints(0.3)
  xlSheet1.PageSetup.RightMargin = Application.InchesToPoints(0.3)
  xlSheet1.PageSetup.TopMargin = Application.InchesToPoints(0.5)
  xlSheet1.PageSetup.BottomMargin = Application.InchesToPoints(0.5)
  xlSheet1.PageSetup.HeaderMargin = Application.InchesToPoints(0.1)
  xlSheet1.PageSetup.FooterMargin = Application.InchesToPoints(0.1)
  xlSheet1.PageSetup.PrintTitleRows = xlSheet1.Rows("1:5").Address
  
  xlSheet2.PageSetup.Orientation = xlLandscape
  xlSheet2.PageSetup.LeftMargin = Application.InchesToPoints(0.3)
  xlSheet2.PageSetup.RightMargin = Application.InchesToPoints(0.3)
  xlSheet2.PageSetup.TopMargin = Application.InchesToPoints(0.5)
  xlSheet2.PageSetup.BottomMargin = Application.InchesToPoints(0.5)
  xlSheet2.PageSetup.HeaderMargin = Application.InchesToPoints(0.1)
  xlSheet2.PageSetup.FooterMargin = Application.InchesToPoints(0.1)
  xlSheet2.PageSetup.PrintTitleRows = xlSheet1.Rows("1:5").Address
    
  'Cabecera / Pie de Página
  xlSheet1.PageSetup.LeftHeader = "&6Moy Park"
  xlSheet1.PageSetup.RightHeader = "&6" + "Sistema de Afiliación de Clientes"
  xlSheet1.PageSetup.RightFooter = "&6página &P"
  
  xlSheet2.PageSetup.LeftHeader = "&6Moy Park"
  xlSheet2.PageSetup.RightHeader = "&6" + "Sistema de Afiliación de Clientes"
  xlSheet2.PageSetup.RightFooter = "&6página &P"
  
  'Columna
  xlSheet1.Columns(1).ColumnWidth = 13
  xlSheet1.Columns(2).ColumnWidth = 8.5
  xlSheet1.Columns(3).ColumnWidth = 26
  xlSheet1.Columns(4).ColumnWidth = 20
  xlSheet1.Columns(5).ColumnWidth = 8.5
  xlSheet1.Columns(6).ColumnWidth = 8.5
  xlSheet1.Columns(7).ColumnWidth = 12
  xlSheet1.Columns(8).ColumnWidth = 13
  xlSheet1.Columns(9).ColumnWidth = 12
  xlSheet1.Columns(10).ColumnWidth = 15
  
  xlSheet2.Columns(1).ColumnWidth = 13
  xlSheet2.Columns(2).ColumnWidth = 8.5
  xlSheet2.Columns(3).ColumnWidth = 26
  xlSheet2.Columns(4).ColumnWidth = 20
  xlSheet2.Columns(5).ColumnWidth = 8.5
  xlSheet2.Columns(6).ColumnWidth = 8.5
  xlSheet2.Columns(7).ColumnWidth = 12
  xlSheet2.Columns(8).ColumnWidth = 13
  xlSheet2.Columns(9).ColumnWidth = 12
  xlSheet2.Columns(10).ColumnWidth = 15
  
  lFila1 = 1
  lFila2 = 1
  
  'Título
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + 1, 1)).NumberFormat = "@"
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + 1, 1)).Font.Bold = True
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + 1, 1)).Font.Size = 11
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + 1, 1)).HorizontalAlignment = xlCenter
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Merge
  xlSheet1.Range(xlSheet1.Cells(lFila1 + 1, 1), xlSheet1.Cells(lFila1 + 1, 10)).Merge
  
  xlSheet1.Cells(lFila1, 1).Value = "CANJE DE PUNTOS"
  xlSheet1.Cells(lFila1 + 1, 1).Value = "DEL " + Format(dFInicio, "dd/mm/yyyy") + " AL " + Format(dFFin, "dd/mm/yyyy")
  
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + 1, 1)).NumberFormat = "@"
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + 1, 1)).Font.Bold = True
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + 1, 1)).Font.Size = 11
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + 1, 1)).HorizontalAlignment = xlCenter
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Merge
  xlSheet2.Range(xlSheet2.Cells(lFila2 + 1, 1), xlSheet2.Cells(lFila2 + 1, 10)).Merge
  
  xlSheet2.Cells(lFila2, 1).Value = "CANJE DE TICKETS"
  xlSheet2.Cells(lFila2 + 1, 1).Value = "DEL " + Format(dFInicio, "dd/mm/yyyy") + " AL " + Format(dFFin, "dd/mm/yyyy")
  
  lFila1 = lFila1 + 4
  lFila2 = lFila2 + 4
   
  'SubTítulo
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).NumberFormat = "@"
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Font.Bold = True
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Font.Size = 8
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).HorizontalAlignment = xlCenter
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Borders.LineStyle = xlContinuous
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Borders.Weight = 2
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Borders(xlEdgeTop).Weight = 3
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Borders(xlEdgeBottom).Weight = 3
  xlSheet1.Cells(lFila1, 1).Borders(xlEdgeLeft).Weight = 3
  xlSheet1.Cells(lFila1, 10).Borders(xlEdgeRight).Weight = 3
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Interior.ColorIndex = 15
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Interior.Pattern = xlSolid
  
  xlSheet1.Cells(lFila1, 1).Value = "Fecha Canje"
  xlSheet1.Cells(lFila1, 2).Value = "Tarjeta"
  xlSheet1.Cells(lFila1, 3).Value = "Cliente"
  xlSheet1.Cells(lFila1, 4).Value = "Premio"
  xlSheet1.Cells(lFila1, 5).Value = "Cantidad"
  xlSheet1.Cells(lFila1, 6).Value = "Punt.Canj."
  xlSheet1.Cells(lFila1, 7).Value = "Usuario"
  xlSheet1.Cells(lFila1, 8).Value = "Fecha Extorno"
  xlSheet1.Cells(lFila1, 9).Value = "Usuar.Extor."
  xlSheet1.Cells(lFila1, 10).Value = "Tipo"
  
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).NumberFormat = "@"
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Font.Bold = True
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Font.Size = 8
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).HorizontalAlignment = xlCenter
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Borders.LineStyle = xlContinuous
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Borders.Weight = 2
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Borders(xlEdgeTop).Weight = 3
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Borders(xlEdgeBottom).Weight = 3
  xlSheet2.Cells(lFila2, 1).Borders(xlEdgeLeft).Weight = 3
  xlSheet2.Cells(lFila2, 10).Borders(xlEdgeRight).Weight = 3
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Interior.ColorIndex = 15
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Interior.Pattern = xlSolid
  
  xlSheet2.Cells(lFila2, 1).Value = "Fecha Canje"
  xlSheet2.Cells(lFila2, 2).Value = "Tarjeta"
  xlSheet2.Cells(lFila2, 3).Value = "Cliente"
  xlSheet2.Cells(lFila2, 4).Value = "Premio"
  xlSheet2.Cells(lFila2, 5).Value = "Cantidad"
  xlSheet2.Cells(lFila2, 6).Value = "Tick.Canj."
  xlSheet2.Cells(lFila2, 7).Value = "Usuario"
  xlSheet2.Cells(lFila2, 8).Value = "Fecha Extorno"
  xlSheet2.Cells(lFila2, 9).Value = "Usuar.Extor."
  xlSheet2.Cells(lFila2, 10).Value = "Tipo"
  
  lFila1 = lFila1 + 1
  lFila2 = lFila2 + 1
  
  lRegistros1 = lvwCanjePunto.ListItems.Count
  lRegistros2 = lvwCanjeTicket.ListItems.Count

  'Datos
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 10)).NumberFormat = "@"
  xlSheet1.Range(xlSheet1.Cells(lFila1, 5), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 6)).NumberFormat = "##,##0"
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 10)).Font.Size = 8
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 10)).HorizontalAlignment = xlLeft
  xlSheet1.Range(xlSheet1.Cells(lFila1, 6), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 6)).HorizontalAlignment = xlRight
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 10)).Borders.LineStyle = xlContinuous
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 10)).Borders.Weight = 2
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Borders(xlEdgeTop).Weight = 3
  xlSheet1.Range(xlSheet1.Cells(lFila1 + lRegistros1 - 1, 1), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 10)).Borders(xlEdgeBottom).Weight = 3
  xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 1)).Borders(xlEdgeLeft).Weight = 3
  xlSheet1.Range(xlSheet1.Cells(lFila1, 10), xlSheet1.Cells(lFila1 + lRegistros1 - 1, 10)).Borders(xlEdgeRight).Weight = 3

  For i = 1 To lRegistros1

    xlSheet1.Cells(lFila1, 1) = lvwCanjePunto.ListItems(i).Text
    xlSheet1.Cells(lFila1, 2) = lvwCanjePunto.ListItems(i).SubItems(1)
    xlSheet1.Cells(lFila1, 3) = lvwCanjePunto.ListItems(i).SubItems(2)
    xlSheet1.Cells(lFila1, 4) = lvwCanjePunto.ListItems(i).SubItems(3)
    xlSheet1.Cells(lFila1, 5) = lvwCanjePunto.ListItems(i).SubItems(4)
    xlSheet1.Cells(lFila1, 6) = lvwCanjePunto.ListItems(i).SubItems(5)
    xlSheet1.Cells(lFila1, 7) = lvwCanjePunto.ListItems(i).SubItems(6)
    xlSheet1.Cells(lFila1, 8) = lvwCanjePunto.ListItems(i).SubItems(7)
    xlSheet1.Cells(lFila1, 9) = lvwCanjePunto.ListItems(i).SubItems(8)

    If lvwCanjePunto.ListItems(i).Icon = 1 Then
      xlSheet1.Cells(lFila1, 10) = "CANJE"
    Else
      xlSheet1.Cells(lFila1, 10) = "CANJE EXTORNADO"
      xlSheet1.Range(xlSheet1.Cells(lFila1, 1), xlSheet1.Cells(lFila1, 10)).Font.ColorIndex = 53
    End If

    lFila1 = lFila1 + 1

  Next

  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 10)).NumberFormat = "@"
  xlSheet2.Range(xlSheet2.Cells(lFila2, 5), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 6)).NumberFormat = "##,##0"
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 10)).Font.Size = 8
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 10)).HorizontalAlignment = xlLeft
  xlSheet2.Range(xlSheet2.Cells(lFila2, 5), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 6)).HorizontalAlignment = xlRight
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 10)).Borders.LineStyle = xlContinuous
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 10)).Borders.Weight = 2
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Borders(xlEdgeTop).Weight = 3
  xlSheet2.Range(xlSheet2.Cells(lFila2 + lRegistros2 - 1, 1), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 10)).Borders(xlEdgeBottom).Weight = 3
  xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 1)).Borders(xlEdgeLeft).Weight = 3
  xlSheet2.Range(xlSheet2.Cells(lFila2, 10), xlSheet2.Cells(lFila2 + lRegistros2 - 1, 10)).Borders(xlEdgeRight).Weight = 3

  For i = 1 To lRegistros2

    xlSheet2.Cells(lFila2, 1) = lvwCanjeTicket.ListItems(i).Text
    xlSheet2.Cells(lFila2, 2) = lvwCanjeTicket.ListItems(i).SubItems(1)
    xlSheet2.Cells(lFila2, 3) = lvwCanjeTicket.ListItems(i).SubItems(2)
    xlSheet2.Cells(lFila2, 4) = lvwCanjeTicket.ListItems(i).SubItems(3)
    xlSheet2.Cells(lFila2, 5) = lvwCanjeTicket.ListItems(i).SubItems(4)
    xlSheet2.Cells(lFila2, 6) = lvwCanjeTicket.ListItems(i).SubItems(5)
    xlSheet2.Cells(lFila2, 7) = lvwCanjeTicket.ListItems(i).SubItems(6)
    xlSheet2.Cells(lFila2, 8) = lvwCanjeTicket.ListItems(i).SubItems(7)
    xlSheet2.Cells(lFila2, 9) = lvwCanjeTicket.ListItems(i).SubItems(8)

    If lvwCanjeTicket.ListItems(i).Icon = 1 Then
      xlSheet2.Cells(lFila2, 10) = "CANJE"
    Else
      xlSheet2.Cells(lFila2, 10) = "CANJE EXTORNADO"
      xlSheet2.Range(xlSheet2.Cells(lFila2, 1), xlSheet2.Cells(lFila2, 10)).Font.ColorIndex = 53
    End If

    lFila2 = lFila2 + 1

  Next
  
  'Mostrar
  xlApp.WindowState = xlMaximized
  xlApp.Visible = True
    
  GoTo Salir
  
LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation
    
  On Error Resume Next

  xlBook.Close False
  xlApp.Quit
  
Salir:

  On Error Resume Next
  
  Set xlSheetTemp = Nothing
  Set xlSheet1 = Nothing
  Set xlBook = Nothing
  Set xlApp = Nothing
  
  cmdMensaje.Visible = False
  
  Screen.MousePointer = vbDefault

End Sub

Private Sub CmdSalir_Click()

  'Descargar Form

  On Error GoTo LineaError
  
  Unload Me

  Exit Sub

LineaError:
  MsgBox Err.Description, vbOKOnly + vbInformation

End Sub



