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