jortega/client
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
client/frmConsultaAlarmas.vb

361 lines
16 KiB
VB.net
Raw Permalink Normal View History

Primera carga de Minds Cliente para el proyecto de blackrock
2019-07-05 13:43:55 -05:00
Public Class frmConsultaAlarmas
Private Sub BTSalir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTSalir.Click
Me.Close()
End Sub
Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TXTMeses.KeyPress
Dim classCom As New clsComplejo
e.KeyChar = classCom.ValidaCampos("numeros", e.KeyChar)
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TXTMeses.TextChanged
End Sub
Private Sub BTConsultar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTConsultar.Click
Dim SQL As String
Dim clCmp As New clsComplejo
Dim Classcom As New clsComplejo
Dim objVarMesesBajo As Object
Dim objVarMesesAlto As Object
Dim objVarMesesMedio As Object
Dim intMeses As Integer
Dim dtFecha1 As String
Dim dtFecha2 As String
Try
Select Case CMBRango.Text
Case Is = "<= 1 Mes"
dtFecha1 = Classcom.ObtenFecha(Now(), "yyyymmdd")
dtFecha2 = Classcom.ObtenFecha(DateAdd(DateInterval.Month, 1, Now()), "yyyymmdd")
Case Is = "1 a 3 Meses"
dtFecha1 = Classcom.ObtenFecha(DateAdd(DateInterval.Month, 1, Now()), "yyyymmdd")
dtFecha2 = Classcom.ObtenFecha(DateAdd(DateInterval.Month, 3, Now()), "yyyymmdd")
Case Is = "3 a 6 Meses"
dtFecha1 = Classcom.ObtenFecha(DateAdd(DateInterval.Month, 3, Now()), "yyyymmdd")
dtFecha2 = Classcom.ObtenFecha(DateAdd(DateInterval.Month, 6, Now()), "yyyymmdd")
Case Is = "6 Meses a 1 A<>o"
dtFecha1 = Classcom.ObtenFecha(DateAdd(DateInterval.Month, 6, Now()), "yyyymmdd")
dtFecha2 = Classcom.ObtenFecha(DateAdd(DateInterval.Month, 12, Now()), "yyyymmdd")
End Select
SQL = "SELECT "
SQL = SQL & " CT.nic as 'NUMERO DE CLIENTE',A.CLIENTENOM AS 'NOMBRE CLIENTE',D.NOMBRE AS DOCUMENTO,C.RIESGO AS RIESGO "
SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C,DOCTOSCHECKLIST D,CUENTA CT"
SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
SQL = SQL & " AND A.idcliente = C.idcliente"
SQL = SQL & " AND A.idcliente = CT.idcliente"
SQL = SQL & " AND B.FECHA_VIGENCIA IS NOT NULL"
SQL = SQL & " AND D.ID_DoctoCheckList = B.Id_DoctoCheckList"
'SQL = SQL & " AND FECHA_VIGENCIA "
'SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & dtFecha1 & "',103) AND CONVERT(DATETIME,'" & dtFecha2 & "',103)"
If CMBRango.Text = "<= 1 Mes" Then
SQL = SQL & " AND (FECHA_VIGENCIA "
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & dtFecha1 & "',112) AND CONVERT(DATETIME,'" & dtFecha2 & "',112) "
SQL = SQL & " or FECHA_VIGENCIA < CONVERT(DATETIME,'" & dtFecha1 & "',112)) "
Else
SQL = SQL & " AND FECHA_VIGENCIA "
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & dtFecha1 & "',112) AND CONVERT(DATETIME,'" & dtFecha2 & "',112) "
'SQL = SQL & " or apo.FechaFormaMig < CONVERT(DATETIME,'" & dtFecha1 & "',103)) "
End If
clCmp.fdtDataSetTabla(SQL)
Me.C1TrueDBGrid1.SetDataBinding(clCmp.ds, "Tabla")
'Me.C1TrueDBGrid1.Splits(0).DisplayColumns(0).FetchStyle = True
Me.C1TrueDBGrid1.Visible = True
'documentos apoderados
SQL = "SELECT 'FORMA MIGRATORIA' IDENTIFICACION,apo.FechaFormaMig as 'FECHA VIGENCIA',cli.clientenom as CLIENTE,"
SQL = SQL & " ct.no_cuenta as 'NUMERO DE CLIENTE',apo.nombre as 'NOMBRE APODERADO',"
SQL = SQL & " apo.apaterno as 'PATERNO APODERADO',apo.amaterno as 'MARTERNO APODERADO'"
SQL = SQL & " FROM Kyc_apoderado apo,cliente cli,cuenta ct "
SQL = SQL & " WHERE apo.idcliente = cli.idcliente"
SQL = SQL & " AND apo.idcliente = ct.idcliente"
SQL = SQL & " AND apo.FechaFormaMig is not null "
SQL = SQL & " AND apo.FechaFormaMig <> ''"
SQL = SQL & " AND apo.FechaFormaMig <> '01/01/1900'"
If CMBRango.Text = "<= 1 Mes" Then
SQL = SQL & " AND (apo.FechaFormaMig "
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & dtFecha1 & "',112) AND CONVERT(DATETIME,'" & dtFecha2 & "',112) "
SQL = SQL & " or apo.FechaFormaMig < CONVERT(DATETIME,'" & dtFecha1 & "',103)) "
Else
SQL = SQL & " AND apo.FechaFormaMig "
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & dtFecha1 & "',112) AND CONVERT(DATETIME,'" & dtFecha2 & "',112) "
'SQL = SQL & " or apo.FechaFormaMig < CONVERT(DATETIME,'" & dtFecha1 & "',103)) "
End If
SQL = SQL & " AND apo.FormaMigratoria is not null "
SQL = SQL & " AND apo.FormaMigratoria <> ''"
SQL = SQL & " AND cli.TipoPersona = 2 "
'SQL = SQL & " AND apo.id_identificacion = i.id_identificacion "
'SQL = SQL & " ORDER BY cli.clientenom "
SQL = SQL & " UNION ALL "
'conteo de las identificaciones
SQL = SQL & " SELECT i.identificacion IDENTIFICACION,apo.FechaId as 'FECHA VIGENCIA',cli.clientenom as CLIENTE,"
SQL = SQL & " ct.no_cuenta as 'NUMERO DE CLIENTE',apo.nombre as 'NOMBRE APODERADO',"
SQL = SQL & " apo.apaterno as 'PATERNO APODERADO',apo.amaterno as 'MARTERNO APODERADO'"
SQL = SQL & " FROM Kyc_apoderado apo,cliente cli,identificacion i,cuenta ct "
SQL = SQL & " WHERE apo.idcliente = cli.idcliente"
SQL = SQL & " AND apo.idcliente = ct.idcliente"
SQL = SQL & " AND apo.FechaId is not null "
SQL = SQL & " AND apo.FechaId <> ''"
SQL = SQL & " AND apo.FechaId <> '01/01/1900'"
If CMBRango.Text = "<= 1 Mes" Then
SQL = SQL & " AND (apo.FechaId "
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & dtFecha1 & "',112) AND CONVERT(DATETIME,'" & dtFecha2 & "',112) "
SQL = SQL & " or apo.FechaId < CONVERT(DATETIME,'" & dtFecha1 & "',103)) "
Else
SQL = SQL & " AND apo.FechaId "
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & dtFecha1 & "',112) AND CONVERT(DATETIME,'" & dtFecha2 & "',112) "
'SQL = SQL & " or apo.FechaId < CONVERT(DATETIME,'" & dtFecha1 & "',103)) "
End If
SQL = SQL & " AND apo.Id_Identificacion is not null "
SQL = SQL & " AND apo.Id_Identificacion <> ''"
SQL = SQL & " AND apo.id_identificacion = i.id_identificacion "
SQL = SQL & " AND apo.Id_Identificacion <> 15 "
SQL = SQL & " ORDER BY cli.clientenom "
clCmp = New clsComplejo
clCmp.fdtDataSetTabla(SQL)
Me.Grid2.SetDataBinding(clCmp.ds, "Tabla")
'Me.Grid2.Splits(0).DisplayColumns(0).FetchStyle = True
Me.Grid2.Visible = True
'*************************
'clCmp = New clsComplejo
'' ''llena grid de riesgo
''VENCIDO BAJO RIESGO 36 MESES
''SQL = "SELECT distinct 'VENCIDO' AS ESTATUS,"
'SQL = "SELECT distinct "
'SQL = SQL & " CT.nic as 'NUMERO DE CLIENTE',A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
'SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C,CUENTA CT "
'SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
'SQL = SQL & " AND A.idcliente = C.idcliente"
'SQL = SQL & " AND A.idcliente = CT.idcliente"
'SQL = SQL & " AND B.fecha_prorroga IS NOT NULL"
'SQL = SQL & " AND C.RIESGO = 'BAJO RIESGO'"
'Select Case CMBRango.Text
' Case Is = "<= 1 Mes"
' 'SUMA DEACUERDO AL RIESGO 36
' dtFecha1 = DateAdd(DateInterval.Month, 36, Now())
' dtFecha2 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
' Case Is = "1 a 3 Meses"
' 'SUMA DEACUERDO AL RIESGO 36
' dtFecha1 = DateAdd(DateInterval.Month, 36, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 2, CDate(dtFecha1))
' Case Is = "3 a 6 Meses"
' dtFecha1 = DateAdd(DateInterval.Month, 36, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 3, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 3, CDate(dtFecha1))
' Case Is = "6 Meses a 1 A<>o"
' dtFecha1 = DateAdd(DateInterval.Month, 36, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 6, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 6, CDate(dtFecha1))
'End Select
'SQL = SQL & " AND dateadd(month,36,fecha_prorroga) "
'SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "DD/MM/YYYY") & "',103) "
'SQL = SQL & " AND CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha2, "DD/MM/YYYY") & "',103)"
'SQL = SQL & " UNION "
''VENCIDO MEDIO RIESGO 18 MESES
''SQL = SQL & "SELECT 'VENCIDO' AS ESTATUS,"
'SQL = SQL & "SELECT DISTINCT "
'SQL = SQL & " CT.nic as 'NUMERO DE CLIENTE',A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
'SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C,CUENTA CT"
'SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
'SQL = SQL & " AND A.idcliente = C.idcliente"
'SQL = SQL & " AND A.idcliente = CT.idcliente"
'SQL = SQL & " AND B.fecha_prorroga IS NOT NULL"
'SQL = SQL & " AND C.RIESGO = 'MEDIO RIESGO'"
''SQL = SQL & " AND DATEDIFF(MONTH,GETDATE(),dateadd(month,18,fecha_prorroga)) <= " & objVarMesesMedio + intMeses
'Select Case CMBRango.Text
' Case Is = "<= 1 Mes"
' 'SUMA DEACUERDO AL RIESGO 18
' dtFecha1 = DateAdd(DateInterval.Month, 18, Now())
' dtFecha2 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
' Case Is = "1 a 3 Meses"
' 'SUMA DEACUERDO AL RIESGO 18
' dtFecha1 = DateAdd(DateInterval.Month, 18, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 2, CDate(dtFecha1))
' Case Is = "3 a 6 Meses"
' dtFecha1 = DateAdd(DateInterval.Month, 18, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 3, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 3, CDate(dtFecha1))
' Case Is = "6 Meses a 1 A<>o"
' dtFecha1 = DateAdd(DateInterval.Month, 18, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 6, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 6, CDate(dtFecha1))
'End Select
'SQL = SQL & " AND dateadd(month,18,fecha_prorroga) "
'SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "DD/MM/YYYY") & "',103) "
'SQL = SQL & " AND CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha2, "DD/MM/YYYY") & "',103)"
'SQL = SQL & " UNION "
'SQL = SQL & "SELECT DISTINCT "
'SQL = SQL & " CT.nic as 'NUMERO DE CLIENTE',A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
'SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C,CUENTA CT"
'SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
'SQL = SQL & " AND A.idcliente = C.idcliente"
'SQL = SQL & " AND A.idcliente = CT.idcliente"
'SQL = SQL & " AND B.fecha_prorroga IS NOT NULL"
'SQL = SQL & " AND C.RIESGO = 'ALTO RIESGO'"
''SQL = SQL & " AND DATEDIFF(MONTH,GETDATE(),dateadd(month,12,fecha_prorroga)) <= " & objVarMesesAlto + intMeses
''SQL = SQL & "UNION "
'Select Case CMBRango.Text
' Case Is = "<= 1 Mes"
' 'SUMA DEACUERDO AL RIESGO 12
' dtFecha1 = DateAdd(DateInterval.Month, 12, Now())
' dtFecha2 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
' Case Is = "1 a 3 Meses"
' 'SUMA DEACUERDO AL RIESGO 12
' dtFecha1 = DateAdd(DateInterval.Month, 12, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 2, CDate(dtFecha1))
' Case Is = "3 a 6 Meses"
' dtFecha1 = DateAdd(DateInterval.Month, 12, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 3, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 3, CDate(dtFecha1))
' Case Is = "6 Meses a 1 A<>o"
' dtFecha1 = DateAdd(DateInterval.Month, 12, Now())
' dtFecha1 = DateAdd(DateInterval.Month, 6, CDate(dtFecha1))
' dtFecha2 = DateAdd(DateInterval.Month, 6, CDate(dtFecha1))
'End Select
'SQL = SQL & " AND dateadd(month,12,fecha_prorroga) "
'SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "DD/MM/YYYY") & "',103) "
'SQL = SQL & " AND CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha2, "DD/MM/YYYY") & "',103)"
'clCmp.fdtDataSetTabla(SQL)
'If clCmp.ds.Tables(0).Rows.Count <> 0 Then
' Me.Grid2.SetDataBinding(clCmp.ds, "Tabla")
' 'Me.Grid2.Splits(0).DisplayColumns(0).FetchStyle = True
' Me.Grid2.Visible = True
'End If
'Dim Cont As Integer
'Cont = clCmp.ds.Tables(0).Rows.Count
'lblNumReg.Text = "Elementos: " & Cont
'*************************
Catch ex As Exception
End Try
End Sub
Private Sub frmConsultaAlarmas_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If CMBRango.Items.Count <> 0 Then
CMBRango.SelectedIndex = 0
BTConsultar.PerformClick()
End If
End Sub
Private Sub Grid2_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Grid2.DoubleClick
End Sub
Private Sub grid2_FetchCellStyle(ByVal sender As Object, ByVal e As C1.Win.C1TrueDBGrid.FetchCellStyleEventArgs) Handles Grid2.FetchCellStyle
Dim S As String = Me.Grid2(e.Row, e.Col) & ""
If S = "VENCIDO" Then
e.CellStyle.BackColor = System.Drawing.Color.Red
e.CellStyle.ForeColor = System.Drawing.Color.White
End If
If S = "POR VENCER" Then
e.CellStyle.BackColor = System.Drawing.Color.Yellow
e.CellStyle.ForeColor = System.Drawing.Color.Black
End If
End Sub
Private Sub C1TrueDBGrid1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles C1TrueDBGrid1.DoubleClick
Dim Doctos As New FrmDocChk
Dim IDcliente As Object
Dim Classcom As New clsComplejo
IDcliente = Classcom.BuscaValores("idcliente", "cliente", " clientenom = '" & C1TrueDBGrid1.Columns(1).Text & "'")
If Not IsDBNull(IDcliente) Then
If C1TrueDBGrid1.Columns(0).Text <> "" Then
clsVaribles.IDCliente = IDcliente
Doctos.ShowDialog()
End If
Else
clsVaribles.IDCliente = 0
End If
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 = "VENCIDO" Then
e.CellStyle.BackColor = System.Drawing.Color.Red
e.CellStyle.ForeColor = System.Drawing.Color.White
End If
If S = "POR VENCER" Then
e.CellStyle.BackColor = System.Drawing.Color.Yellow
e.CellStyle.ForeColor = System.Drawing.Color.Black
End If
End Sub
Private Sub C1TrueDBGrid1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles C1TrueDBGrid1.Click
End Sub
Private Sub Grid2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Grid2.Click
End Sub
End Class
Reference in New Issue Copy Permalink