Public Class frmHistoricoAlarmas Inherits System.Windows.Forms.Form Dim clCmp As clsComplejo = New clsComplejo Dim Patron As clsComplejo = New clsComplejo 'Para llenar combo patron 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 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 = "" clCmp = New clsComplejo SQL = fsDefineSQL("ReporteInusualHistoricoCDTO ") clCmp.fdtDataSetTabla(SQL) ''Auditoria Dim ClassAud As New ClassMyUtils Dim sTextoAud As String sTextoAud = "Reporte Inusual Historico Cred, Periodo del " & dtpFechaInicio.Value.Date & " al " & dtpFechaFin.Value.Date If cmbPatron.Text <> "" Then sTextoAud = sTextoAud & ", Patron:" & cmbPatron.Text End If If cmbCliente.Text <> "" Then sTextoAud = sTextoAud & ", IdCliente:" & cmbCliente.SelectedValue End If If cmbCuenta.Text <> "" Then sTextoAud = sTextoAud & ", Cuenta:" & cmbCuenta.Text End If If cmbStatus.Text <> "" Then sTextoAud = sTextoAud & ", Estatus:" & cmbStatus.Text End If ClassAud.RT_Auditoria(clsVaribles.varUsuario, 118, 1, sTextoAud) ''Fin de auditoria OcultaColums() Me.C1TrueDBGrid1.SetDataBinding(clCmp.ds, "Tabla") Me.C1TrueDBGrid1.Splits(0).DisplayColumns(1).Visible = False Me.C1TrueDBGrid1.Splits(0).DisplayColumns(2).Visible = False Me.C1TrueDBGrid1.Splits(0).DisplayColumns(3).Visible = False Me.C1TrueDBGrid1.Splits(0).DisplayColumns(4).Visible = False Me.C1TrueDBGrid1.Splits(0).DisplayColumns(5).Visible = False Me.C1TrueDBGrid1.Splits(0).DisplayColumns(6).Visible = False Me.C1TrueDBGrid1.Splits(0).DisplayColumns(7).Visible = False Me.C1TrueDBGrid1.Columns(14).NumberFormat = "Currency" Me.C1TrueDBGrid1.Columns(27).NumberFormat = "Currency" Me.C1TrueDBGrid1.Columns(28).NumberFormat = "Currency" ''Me.C1TrueDBGrid1.Splits(0).DisplayColumns(6).FetchStyle = True ''Me.C1TrueDBGrid1.Splits(0).DisplayColumns(6).Style.VerticalAlignment = C1.Win.C1TrueDBGrid.AlignVertEnum.Center Me.C1TrueDBGrid1.Splits(0).DisplayColumns("idTransaccion").Visible = False Me.C1TrueDBGrid1.Splits(0).DisplayColumns("idCuenta").Visible = False '-- 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 'REVISAR 02/07/2015 'Me.C1TrueDBGrid1.Splits(0).DisplayColumns("Historico").Style.VerticalAlignment = C1.Win.C1TrueDBGrid.AlignVertEnum.Center 'Me.C1TrueDBGrid1.Splits(0).DisplayColumns("Historico").Width = 30 Me.C1TrueDBGrid1.Visible = True 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 lblPeriodoHist.Text = "Botón derecho del mouse sobre el registro a calificar." ''Coloca los colorines 'REVISAR 02/07/2015 'Me.C1TrueDBGrid1.Splits(0).DisplayColumns("ReportadoPor").FetchStyle = True ''Activa evento C1TrueDBGrid1_FetchCellStyle ''Fin de cambiar el color. 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 Me.cmbCuenta.SelectedIndex = -1 Me.cmbCuenta.SelectedIndex = -1 'combo patron Me.cmbPatron.SelectedIndex = -1 Me.cmbPatron.SelectedIndex = -1 'combo Estatus Me.cmbStatus.SelectedIndex = -1 Me.cmbStatus.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 ''hago una copia por valor de la tabla 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 Try 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 = "Historico Alarmas Credito" '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 = " NULL," Dim lsFin As String = " NULL" Dim lsCliente As String = " NULL, " Dim lsCuenta As String = " NULL, " Dim lsPatron As String = " NULL, " Dim lsEstatus As String = " NULL," 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 Me.cmbCliente.Text <> "" And Me.cmbCuenta.Text = "" Then '-- se agrego esta opcion para que traiga todas las cuentas de un cliente '-- c. clain 12/mar/2013 _Opcion = 9 lsCliente = CInt(Me.cmbCliente.SelectedValue) & ", " lsCuenta = "null, " If Me.cmbPatron.SelectedIndex = -1 Then lsPatron = "null, " Else lsPatron = CInt(Me.cmbPatron.SelectedValue) & ", " End If If Me.cmbStatus.SelectedIndex = -1 Then lsPatron = "null, " Else lsEstatus = CInt(Me.cmbStatus.SelectedValue) & "," End If Else '******************************************************************************************** If Me.cmbCliente.Text = "" And Me.cmbPatron.Text = "" And Me.cmbStatus.Text = "" Then _Opcion = 1 ElseIf Me.cmbCliente.Text = "" And Me.cmbPatron.Text = "" And Me.cmbStatus.Text <> "" Then _Opcion = 2 lsEstatus = CInt(Me.cmbStatus.SelectedValue) & "," ElseIf Me.cmbCliente.Text = "" And Me.cmbPatron.Text <> "" And Me.cmbStatus.Text = "" Then _Opcion = 3 lsPatron = CInt(Me.cmbPatron.SelectedValue) & ", " ElseIf Me.cmbCliente.Text = "" And Me.cmbPatron.Text <> "" And Me.cmbStatus.Text <> "" Then _Opcion = 4 lsEstatus = CInt(Me.cmbStatus.SelectedValue) & ", " lsPatron = CInt(Me.cmbPatron.SelectedValue) & ", " ElseIf Me.cmbCliente.Text <> "" And Me.cmbCuenta.Text <> "" And Me.cmbPatron.Text = "" And Me.cmbStatus.Text = "" Then _Opcion = 5 lsCliente = CInt(Me.cmbCliente.SelectedValue) & ", " lsCuenta = "'" & CStr(Me.cmbCuenta.SelectedValue) & "', " ElseIf Me.cmbCliente.Text <> "" And Me.cmbCuenta.Text <> "" And Me.cmbPatron.Text = "" And Me.cmbStatus.Text <> "" Then _Opcion = 6 lsCliente = CInt(Me.cmbCliente.SelectedValue) & ", " lsCuenta = "'" & CStr(Me.cmbCuenta.SelectedValue) & "', " lsEstatus = CInt(Me.cmbStatus.SelectedValue) & "," ElseIf Me.cmbCliente.Text <> "" And Me.cmbCuenta.Text <> "" And Me.cmbPatron.Text <> "" And Me.cmbStatus.Text = "" Then _Opcion = 7 lsCliente = CInt(Me.cmbCliente.SelectedValue) & ", " lsCuenta = "'" & CStr(Me.cmbCuenta.SelectedValue) & "', " lsPatron = CInt(Me.cmbPatron.SelectedValue) & ", " ElseIf Me.cmbCliente.Text <> "" And Me.cmbCuenta.Text <> "" And Me.cmbPatron.Text <> "" And Me.cmbStatus.Text <> "" Then _Opcion = 8 lsCliente = CInt(Me.cmbCliente.SelectedValue) & ", " lsCuenta = "'" & CStr(Me.cmbCuenta.SelectedValue) & "', " lsPatron = CInt(Me.cmbPatron.SelectedValue) & ", " lsEstatus = CInt(Me.cmbStatus.SelectedValue) & "," End If End If lsSQL = "" lsSQL = lsSQL & SP lsSQL = lsSQL & lsPatron lsSQL = lsSQL & lsCliente lsSQL = lsSQL & lsCuenta lsSQL = lsSQL & lsEstatus lsSQL = lsSQL & lsIni lsSQL = lsSQL & lsFin '-- asigna valores a variables globales para en caso de que consulte el detalle '-- del historico clsVaribles.Finicio = CDate(dtpFechaInicio.Value.ToShortDateString) '-- FchIni '--lsIni clsVaribles.Ffin = CDate(dtpFechaFin.Value.ToShortDateString) ' '--lsFin '--- Return lsSQL Catch ex As Exception MessageBox.Show(ex.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Function Private Sub frmHistoricoAlarmas_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 Estatus.sbLlenaCombo("spLlenaComboEstatusTransacciones", Me.cmbStatus) cmbStatus.SelectedIndex = -1 Patron.sbLlenaCombo("spLlenaComboPatron 0", Me.cmbPatron) ''Patron.sbLlenaCombo("spLlenaComboPatron 'CREDITO'", Me.cmbPatron) cmbPatron.SelectedIndex = -1 End Sub Private Sub C1TrueDBGrid1_AfterFilter(sender As Object, 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 C1TrueDBGrid1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles C1TrueDBGrid1.DoubleClick Try Dim strHist As String Dim FilaActual As Integer FilaActual = Me.C1TrueDBGrid1.Row Dim fila As DataRow fila = clCmp.ds.Tables(0).Rows(FilaActual) clsVaribles.VarIdCliente = C1TrueDBGrid1.Columns("IdCliente").Text '-- los demas parametros no se envian ára que en la '-- consulta del detalle se despliegen todas las que tiene el cliente '-- en el periodo mencionado strHist = C1TrueDBGrid1.Columns(0).Text If strHist = "X" Then Dim frmDetalleHistorico As frmDetalleHistorico = New frmDetalleHistorico frmDetalleHistorico.Text = "Detalle Historico de Alarmas" frmDetalleHistorico.ShowDialog() 'refrescar() 'c1.BackColor = System.Drawing.Color.White sbBuscar() Else strHist = C1TrueDBGrid1.Columns(1).Text If C1TrueDBGrid1.Columns(1).Text <> "RELEVANTE" Then clsVar.ReportadoPor = C1TrueDBGrid1.Columns("ReportadoPor").Text clsVar.Nombre = C1TrueDBGrid1.Columns("NOMBRE DEL CLIENTE").Text clsVar.No_Referencia = C1TrueDBGrid1.Columns("NumeroReferencia").Text clsVar.Id_Transaccion = C1TrueDBGrid1.Columns("idtransaccion").Text clsVar.Id_Producto = C1TrueDBGrid1.Columns("IdProducto").Text clsVar.Id_SubProducto = C1TrueDBGrid1.Columns("IdSubproducto").Text clsVar.Id_Patron = C1TrueDBGrid1.Columns("IdPatron").Text clsVar.Id_EstatusTransaccion = C1TrueDBGrid1.Columns("IdEstatusTransaccion").Text Dim frmDetalles As Detalles = New Detalles frmDetalles.Text = "Detalle Crédito" frmDetalles.ShowDialog() 'refrescar() sbBuscar() End If End If Catch ex As Exception MessageBox.Show(ex.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub Private Sub C1TrueDBGrid1_FetchCellStyle(ByVal sender As Object, ByVal e As C1.Win.C1TrueDBGrid.FetchCellStyleEventArgs) Handles C1TrueDBGrid1.FetchCellStyle Dim S As String = Me.C1TrueDBGrid1(e.Row, e.Col) If S = "RELEVANTE" Then e.CellStyle.BackColor = System.Drawing.Color.White e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON NUEVAS INUSUALES" Then e.CellStyle.BackColor = System.Drawing.Color.White e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PREOCUPANTE" Then e.CellStyle.BackColor = System.Drawing.Color.White e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON FECHA" Then e.CellStyle.BackColor = System.Drawing.Color.Yellow e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON MONTO" Then e.CellStyle.BackColor = System.Drawing.Color.Red e.CellStyle.ForeColor = System.Drawing.Color.White End If If S = "PATRON PAGO ANTICIPADO" Then e.CellStyle.BackColor = System.Drawing.Color.DarkGreen e.CellStyle.ForeColor = System.Drawing.Color.White End If If S = "PATRON TRANSACCIONES X DIA" Then e.CellStyle.BackColor = System.Drawing.Color.Blue e.CellStyle.ForeColor = System.Drawing.Color.White End If If S = "PATRON LIQUIDACION CREDITO" Then e.CellStyle.BackColor = System.Drawing.Color.SkyBlue e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON TASA" Then e.CellStyle.BackColor = System.Drawing.Color.Aquamarine e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON FUNCIONARIO" Then e.CellStyle.BackColor = System.Drawing.Color.Pink e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON REVOLVENCIA" Then e.CellStyle.BackColor = System.Drawing.Color.DarkKhaki e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON DIVISA" Then e.CellStyle.BackColor = System.Drawing.Color.Gold e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON TIPOCAMBIO" Then e.CellStyle.BackColor = System.Drawing.Color.DarkTurquoise e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON BANCO" Then e.CellStyle.BackColor = System.Drawing.Color.SkyBlue e.CellStyle.ForeColor = System.Drawing.Color.Black End If If S = "PATRON ORDENANTE/BENEFICIARIO" Then e.CellStyle.BackColor = System.Drawing.Color.DarkGreen e.CellStyle.ForeColor = System.Drawing.Color.Black End If End Sub Private Sub C1TrueDBGrid1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles C1TrueDBGrid1.MouseDown Dim iResp As Integer '-- pregunta si presiono el boton derecho del raton FilaActual = C1TrueDBGrid1.SelectedRows.IndexOf(0) If e.Button = Windows.Forms.MouseButtons.Right Then 'MsgBox("presiono boton derecho") '- indico cual fue la ultima linea donde se quedo FilaActual = Me.C1TrueDBGrid1.Row If Me.C1TrueDBGrid1.RowCount = 0 Then Exit Sub End If '-- pregunta si quiere calificar o mostrar el detalle de la historia iResp = MsgBox("¿Desea Calificar la Alarma?", MsgBoxStyle.YesNo, "MINDS CLIENTE 2.5") If iResp = vbYes Then Try Dim FilaActual As Integer FilaActual = Me.C1TrueDBGrid1.Row Dim fila As DataRow fila = clCmp.ds.Tables(0).Rows(FilaActual) clsVar.ReportadoPor = C1TrueDBGrid1.Columns("ReportadoPor").Text clsVar.Nombre = C1TrueDBGrid1.Columns("NOMBRE DEL CLIENTE").Text clsVar.No_Referencia = C1TrueDBGrid1.Columns("NumeroReferencia").Text clsVar.Id_Transaccion = C1TrueDBGrid1.Columns("idtransaccion").Text clsVar.Id_Producto = C1TrueDBGrid1.Columns("IdProducto").Text clsVar.Id_SubProducto = C1TrueDBGrid1.Columns("IdSubproducto").Text clsVar.Id_Patron = C1TrueDBGrid1.Columns("IdPatron").Text clsVar.Id_EstatusTransaccion = C1TrueDBGrid1.Columns("IdEstatusTransaccion").Text Dim frmDetalles As Detalles = New Detalles frmDetalles.Text = "Detalle Crédito" frmDetalles.ShowDialog() 'refrescar() sbBuscar() Catch ex As Exception MessageBox.Show(ex.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End If '-- refresca la ventana sbBuscar() End If End Sub Private Sub cmbCuenta_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbCuenta.DropDown ''Lena el combo de cuenta Try If Me.cmbCliente.Text = "" Then Me.cmbCliente.Focus() MessageBox.Show("Debes de seleccionar un cliente ", "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Information) Else 'If BanCuenta = False Then Dim cad As String cad = "spLlenaComboCuenta " cad = cad & CInt(cmbCliente.SelectedValue) & ",1" 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