198 lines
9.2 KiB
VB.net
198 lines
9.2 KiB
VB.net
|
Public Class PopUp
|
||
|
|
|
||
|
|
Private Sub PopUp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
|
||
|
|
Dim SQL As String
|
||
|
|
Dim Classcom As New clsComplejo
|
||
|
|
Dim Cifra As Long = 0
|
||
|
|
Dim Cifra2 As Long = 0
|
||
|
|
Dim dtFecha1 As String
|
||
|
|
Dim dtFecha2 As String
|
||
|
|
|
||
|
|
Try
|
||
|
|
|
||
|
|
dtFecha1 = Classcom.ObtenFecha(Now(), "yyyymmdd")
|
||
|
|
dtFecha2 = Classcom.ObtenFecha(DateAdd(DateInterval.Month, 1, Now()), "yyyymmdd")
|
||
|
|
|
||
|
|
Cifra = 0
|
||
|
|
|
||
|
|
'conteo de las formas
|
||
|
|
SQL = "SELECT apo.FechaFormaMig as 'Fecha Vigencia',cli.clientenom as CLIENTE,"
|
||
|
|
SQL = SQL & " cli.idcliente as 'Numero de Cliente',apo.nombre as 'Nombre Apoderado',"
|
||
|
|
SQL = SQL & " apo.apaterno as 'Paterno Apoderado',apo.amaterno as 'Materno Apoderado'"
|
||
|
|
SQL = SQL & " FROM Kyc_apoderado apo,cliente cli "
|
||
|
|
SQL = SQL & " WHERE apo.idcliente = cli.idcliente"
|
||
|
|
SQL = SQL & " AND apo.FechaFormaMig is not null "
|
||
|
|
SQL = SQL & " AND apo.FechaFormaMig <> ''"
|
||
|
|
SQL = SQL & " AND apo.FechaFormaMig <> '01/01/1900'"
|
||
|
|
SQL = SQL & " AND apo.FechaFormaMig <= convert(smalldatetime,'" & Classcom.ObtenFecha(DateAdd(DateInterval.Month, 1, Now().Date), "yyyymmdd") & "',112)"
|
||
|
|
SQL = SQL & " AND apo.FormaMigratoria is not null "
|
||
|
|
SQL = SQL & " AND apo.FormaMigratoria <> ''"
|
||
|
|
SQL = SQL & " AND cli.TipoPersona = 2 "
|
||
|
|
SQL = SQL & " ORDER BY cli.clientenom "
|
||
|
|
|
||
|
|
Classcom.fdtDataSetTabla(SQL)
|
||
|
|
|
||
|
|
If Classcom.ds.Tables(0).Rows.Count <> 0 Then
|
||
|
|
Cifra = Cifra + Classcom.ds.Tables(0).Rows.Count
|
||
|
|
End If
|
||
|
|
|
||
|
|
'conteo de las identificaciones
|
||
|
|
SQL = "SELECT apo.FechaId as 'Fecha Vigencia',cli.clientenom as Empresa,"
|
||
|
|
SQL = SQL & " cli.idcliente as 'Numero de Cliente',apo.nombre as 'Nombre Apoderado',"
|
||
|
|
SQL = SQL & " apo.apaterno as 'Paterno Apoderado',apo.amaterno as 'Materno Apoderado'"
|
||
|
|
SQL = SQL & " FROM Kyc_apoderado apo,cliente cli "
|
||
|
|
SQL = SQL & " WHERE apo.idcliente = cli.idcliente"
|
||
|
|
SQL = SQL & " AND apo.FechaId is not null "
|
||
|
|
SQL = SQL & " AND apo.FechaId <> ''"
|
||
|
|
SQL = SQL & " AND apo.FechaId <> '01/01/1900'"
|
||
|
|
SQL = SQL & " AND apo.FechaId <= convert(smalldatetime,'" & Classcom.ObtenFecha(DateAdd(DateInterval.Month, 1, Now().Date), "yyyymmdd") & "',112)"
|
||
|
|
SQL = SQL & " AND apo.Id_Identificacion is not null "
|
||
|
|
SQL = SQL & " AND apo.Id_Identificacion <> ''"
|
||
|
|
'SQL = SQL & " AND apo.Id_Identificacion = 7 "
|
||
|
|
SQL = SQL & " AND apo.Id_Identificacion <> 15 "
|
||
|
|
SQL = SQL & " ORDER BY cli.clientenom "
|
||
|
|
|
||
|
|
Classcom = New clsComplejo
|
||
|
|
|
||
|
|
Classcom.fdtDataSetTabla(SQL)
|
||
|
|
|
||
|
|
If Classcom.ds.Tables(0).Rows.Count <> 0 Then
|
||
|
|
Cifra = Cifra + Classcom.ds.Tables(0).Rows.Count
|
||
|
|
End If
|
||
|
|
|
||
|
|
|
||
|
|
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 & "',112) "
|
||
|
|
SQL = SQL & " or FECHA_VIGENCIA < CONVERT(DATETIME,'" & dtFecha1 & "',103)) "
|
||
|
|
|
||
|
|
|
||
|
|
Classcom = New clsComplejo
|
||
|
|
|
||
|
|
Classcom.fdtDataSetTabla(SQL)
|
||
|
|
|
||
|
|
If Classcom.ds.Tables(0).Rows.Count <> 0 Then
|
||
|
|
Cifra = Cifra + Classcom.ds.Tables(0).Rows.Count
|
||
|
|
End If
|
||
|
|
|
||
|
|
TXTDocum.Text = "Documentos Vencidos = " & Cifra
|
||
|
|
|
||
|
|
Cifra2 = 0
|
||
|
|
|
||
|
|
'EXPEDIENTES
|
||
|
|
|
||
|
|
'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'"
|
||
|
|
|
||
|
|
dtFecha1 = Now().Date
|
||
|
|
dtFecha2 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
|
||
|
|
|
||
|
|
'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 & " AND (dateadd(month,36,fecha_prorroga) "
|
||
|
|
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "yyyymmdd") & "',112) "
|
||
|
|
SQL = SQL & " AND CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha2, "yyyymmdd") & "',112) or dateadd(month,36,fecha_prorroga) < CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "yyyymmdd") & "',112)"
|
||
|
|
SQL = SQL & " or B.fecha_prorroga IS NULL)"
|
||
|
|
|
||
|
|
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 dateadd(month,18,fecha_prorroga) "
|
||
|
|
|
||
|
|
dtFecha1 = Now().Date
|
||
|
|
dtFecha2 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
|
||
|
|
|
||
|
|
'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 & " AND (dateadd(month,18,fecha_prorroga) "
|
||
|
|
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "yyyymmdd") & "',112) "
|
||
|
|
SQL = SQL & " AND CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha2, "yyyymmdd") & "',112) or dateadd(month,18,fecha_prorroga) < CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "yyyymmdd") & "',112)"
|
||
|
|
SQL = SQL & " or B.fecha_prorroga IS NULL)"
|
||
|
|
|
||
|
|
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 dateadd(month,12,fecha_prorroga) "
|
||
|
|
|
||
|
|
dtFecha1 = Now().Date
|
||
|
|
dtFecha2 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
|
||
|
|
|
||
|
|
'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 & " AND (dateadd(month,12,fecha_prorroga) "
|
||
|
|
SQL = SQL & " BETWEEN CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "yyyymmdd") & "',112) "
|
||
|
|
SQL = SQL & " AND CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha2, "yyyymmdd") & "',112) or dateadd(month,12,fecha_prorroga) < CONVERT(DATETIME,'" & Classcom.ObtenFecha(dtFecha1, "yyyymmdd") & "',112)"
|
||
|
|
SQL = SQL & " or B.fecha_prorroga IS NULL)"
|
||
|
|
|
||
|
|
Classcom = New clsComplejo
|
||
|
|
|
||
|
|
Classcom.fdtDataSetTabla(SQL)
|
||
|
|
|
||
|
|
If Classcom.ds.Tables(0).Rows.Count <> 0 Then
|
||
|
|
Cifra2 = Cifra2 + Classcom.ds.Tables(0).Rows.Count
|
||
|
|
End If
|
||
|
|
|
||
|
|
TXTExp.Text = "Expedientes por Verificar = " & Cifra2
|
||
|
|
|
||
|
|
If Cifra = 0 And Cifra2 = 0 Then
|
||
|
|
Me.Close()
|
||
|
|
End If
|
||
|
|
|
||
|
|
Catch ex As Exception
|
||
|
|
|
||
|
|
End Try
|
||
|
|
|
||
|
|
End Sub
|
||
|
|
|
||
|
|
Private Sub BTDocum_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTDocum.Click
|
||
|
|
|
||
|
|
Dim Doc As New frmConsultaAlarmas
|
||
|
|
|
||
|
|
Doc.ShowDialog()
|
||
|
|
|
||
|
|
End Sub
|
||
|
|
|
||
|
|
Private Sub BTExp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTExp.Click
|
||
|
|
|
||
|
|
Dim Doc As New FrmAlarmasExp
|
||
|
|
|
||
|
|
Doc.ShowDialog()
|
||
|
|
|
||
|
|
|
||
|
|
End Sub
|
||
|
|
End Class
|