Public Class frmExcedentes
    Inherits System.Windows.Forms.Form
    Dim clCmp As clsComplejo = New clsComplejo
    Dim Periodo As clsComplejo = New clsComplejo   'Para llenar combo periodo
    Dim Cliente As clsComplejo = New clsComplejo   'para llenar combo cliente
    Dim Estatus As clsComplejo = New clsComplejo   'para llenar combo estatus
    Dim Cuenta As clsComplejo = New clsComplejo   'para llenar el combo de cuenta
    Dim clsVar As clsVaribles = New clsVaribles 'Para utilizar varibles publicas
    Dim BanCliente, BanCuenta, BanPatron, BanStatus As Boolean
    Dim FilaActual As Integer = 0

    Private Sub frmExcedentes_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        BanCliente = False
        BanCuenta = False
        BanPatron = False
        BanStatus = False

        Cliente.sbLlenaCombo("spLlenaComboCliente " & clsVaribles.liPerfilB, Me.cmbCliente) ' ClsVaribles
        cmbCliente.SelectedIndex = -1

        cmbCuenta.Text = ""
        Me.cmbCuenta.SelectedIndex = -1
        Me.cmbCuenta.SelectedIndex = -1

    End Sub

    Private Sub tlbInusualesDivisas_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles tlbInusualesDivisas.ButtonClick
        Select Case e.Button.Text
            Case "Consultar"
                sbBuscar()
            Case "Exportar"
                Exportar()
            Case "Limpiar"
                refrescar()
            Case "Salir"
                Me.Close()
        End Select
    End Sub

    Public Sub sbBuscar()
        Try
            Dim SQL As String
            SQL = ""
            SQL = fsDefineSQL("spReporteExcedente  ")
            clCmp.fdtDataSetTabla(SQL)
            ''Auditoria
            Dim ClassAud As New ClassMyUtils
            Dim sTextoAud As String
            sTextoAud = "Reporte Excedentes, Periodo del " & dtpFechaInicio.Value.Date & " al " & dtpFechaFin.Value.Date

            If cmbCliente.Text <> "" Then
                sTextoAud = sTextoAud & ", IdCliente:" & cmbCliente.SelectedValue
            End If
            If cmbCuenta.Text <> "" Then
                sTextoAud = sTextoAud & ", Cuenta:" & cmbCuenta.Text
            End If

            ClassAud.RT_Auditoria(clsVaribles.varUsuario, 118, 1, sTextoAud)
            ''Fin de auditoria

            Me.C1TrueDBGrid1.DataSource = Nothing
            Me.C1TrueDBGrid1.SetDataBinding()
            Me.C1TrueDBGrid1.SetDataBinding(clCmp.ds, "Tabla")
            Me.C1TrueDBGrid1.Visible = True
            LblNumReg.Text = "Elementos: " & Me.C1TrueDBGrid1.RowCount


            ''OcultaColums()
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(7).Visible = False
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(2).Visible = False
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(28).Visible = False
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(4).Visible = False
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(6).Visible = False
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(36).Visible = False
            ''Me.C1TrueDBGrid1.Splits(0).DisplayColumns("idCuenta").Visible = False

            'Me.C1TrueDBGrid1.Columns(15).NumberFormat = "Currency"
            'Me.C1TrueDBGrid1.Columns(14).NumberFormat = "Currency"
            'Me.C1TrueDBGrid1.Columns(26).NumberFormat = "Currency"
            'Me.C1TrueDBGrid1.Columns(25).NumberFormat = "Currency"
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(6).FetchStyle = True
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(6).Style.VerticalAlignment = C1.Win.C1TrueDBGrid.AlignVertEnum.Center

            ''-- los datos de la columna de si es hostorico el dato los coloca en ROJO
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(0).Style.ForeColor = Color.Red
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns("Historico").Style.VerticalAlignment = C1.Win.C1TrueDBGrid.AlignVertEnum.Center
            'Me.C1TrueDBGrid1.Splits(0).DisplayColumns("Historico").Width = 30

            'Me.C1TrueDBGrid1.Columns(2).GroupInfo.ColumnVisible = True
            'Me.C1TrueDBGrid1.DataView = C1.Win.C1TrueDBGrid.DataViewEnum.GroupBy

            'Dim Cont As Integer
            'Cont = clCmp.ds.Tables(0).Rows.Count
            ''LblNumReg.Text = "Elementos: " & Cont
            'LblNumReg.Text = "Elementos: " & Me.C1TrueDBGrid1.RowCount

        Catch er As Exception
            MessageBox.Show(er.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub refrescar()

        'Combo cliente
        Me.cmbCliente.SelectedIndex = -1
        Me.cmbCliente.SelectedIndex = -1

        'Combo Cuenta
        cmbCuenta.Text = ""
        Me.cmbCuenta.SelectedIndex = -1
        Me.cmbCuenta.SelectedIndex = -1

        'Grid
        Me.C1TrueDBGrid1.Visible = False
        Me.C1TrueDBGrid1.DataSource = Nothing
        Me.C1TrueDBGrid1.DataBindings.Clear()

        'etiqueta
        Me.LblNumReg.Text = ""

        'Establece las banderas a false para q sepuedan cargar los combos otra vez
        BanCliente = False
        BanCuenta = False
        BanPatron = False
        BanStatus = False

    End Sub

    Private Sub Exportar()
        If C1TrueDBGrid1.DataSource Is Nothing Then
            MessageBox.Show("Presione primero el botón de consultar y despues el boton de exportar.", "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return
        End If

        ''Preparo la tabla a exportar
        Dim copyDS As DataSet = clCmp.ds.Copy()
        If copyDS.Tables(0).Rows.Count = 0 Then
            MessageBox.Show("No existen datos para exportar", "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return
        End If
        Dim Resul As DialogResult
        Resul = MessageBox.Show("Este proceso puede tardar varios minutos, ¿Deseas continuar? ", "MINDS", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If Resul = DialogResult.No Then
            Return
        End If

        ''Quito las columnas ocultas del grid.
        Dim i As Integer
        For i = 0 To C1TrueDBGrid1.Columns.Count - 1
            If Me.C1TrueDBGrid1.Splits(0).DisplayColumns(i).Visible = False Then
                Dim j As Integer
                For j = 0 To copyDS.Tables(0).Columns.Count - 1
                    If Me.C1TrueDBGrid1.Splits(0).DisplayColumns(i).Name = copyDS.Tables(0).Columns.Item(j).ColumnName Then
                        copyDS.Tables(0).Columns.Remove(copyDS.Tables(0).Columns.Item(j).ColumnName)
                        Exit For
                    End If
                Next
            End If
        Next

        Dim MiClassTablaAExcel As New ClassTablaAExcel()

        Try
            Cursor = System.Windows.Forms.Cursors.WaitCursor

            Dim sError As String = ""
            ''Inicia Exportacion
            'Creo una instancia de la clase aExcel
            Dim clsExcel As aExcel = New aExcel
            'Obtenemos el nombre o el titulo del form
            clsExcel.Titulo = "Reporte de Excedentes de BT"
            'Utilizo la funcion ExportDataset de la clase aExcel
            clsExcel.ExportDataset(copyDS) 'Asigno el dataset a exportar

        Catch ex As Exception
            MessageBox.Show(ex.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        Cursor = System.Windows.Forms.Cursors.Default

    End Sub

    Private Sub OcultaColums()
        Try
            If clCmp.ds.Tables.Count <> 0 Then
                clCmp.OcultarColumna("IdTransaccion")
                clCmp.OcultarColumna("IdProducto")
                clCmp.OcultarColumna("IdSubproducto")
                clCmp.OcultarColumna("IdPatron")
                clCmp.OcultarColumna("IdEstatusTransaccion")
                clCmp.OcultarColumna("idCuenta")
            End If
        Catch er As Exception
            ' MessageBox.Show(er.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Private Function fsDefineSQL(ByVal SP As String) As String
        'Defino mi cadena para el procedimiento almacenado
        Try
            Dim lsSQL As String = ""
            Dim _Opcion As Integer
            Dim lsIni As String
            Dim lsFin As String
            Dim lsCliente As String
            Dim lsCuenta As String
            Dim Classcom As New clsComplejo
            Dim FchIni As Date
            Dim meses As Integer
            ' ************ Fechas Periodo****************
            lsIni = Classcom.ObtenFecha(CDate(dtpFechaInicio.Value.ToShortDateString), "yyyymmdd")
            lsIni = " '" & lsIni & "'"
            lsFin = Classcom.ObtenFecha(CDate(dtpFechaFin.Value.ToShortDateString), "yyyymmdd")
            lsFin = "'" & lsFin & "'"
            '*******************************************************************************

            If cmbCliente.SelectedIndex = -1 Then
                lsCliente = "NULL"
            Else
                lsCliente = CInt(Me.cmbCliente.SelectedValue)
            End If

            If cmbCuenta.SelectedIndex = -1 Then
                lsCuenta = "NULL"
            Else
                lsCuenta = "'" & CStr(Me.cmbCuenta.SelectedValue) & "'"
            End If

            lsSQL = SP & lsIni & "," & lsFin & "," & lsCliente & "," & lsCuenta

            Return lsSQL
        Catch ex As Exception
            MessageBox.Show(ex.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Function

    Private Sub C1TrueDBGrid1_AfterFilter(ByVal sender As Object, ByVal e As C1.Win.C1TrueDBGrid.FilterEventArgs) Handles C1TrueDBGrid1.AfterFilter
        LblNumReg.Text = "Elementos: " & Me.C1TrueDBGrid1.RowCount
    End Sub

    Private Sub C1TrueDBGrid1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles C1TrueDBGrid1.Click

    End Sub

    Private Sub cmbCuenta_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbCuenta.DropDown
        ''Lena el combo de cuenta
        Dim cad As String
        Try
            If Me.cmbCliente.Text = "" Then
                Me.cmbCliente.Focus()
                MessageBox.Show("Debes de seleccionar un cliente ", "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Information)
                cad = "spLlenaComboCuenta "
                cad = cad & CInt(0) & ",2"
                Cuenta.sbLlenaCombo1(cad, Me.cmbCuenta)

            Else
                'If BanCuenta = False Then

                cad = "spLlenaComboCuenta "
                cad = cad & CInt(cmbCliente.SelectedValue) & ",2"
                Cuenta.sbLlenaCombo1(cad, Me.cmbCuenta)
                BanCuenta = True
                'End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

End Class