Public Class FrmAlarmas

    Private Sub FrmAlarmas_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        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

        objVarMesesBajo = Classcom.BuscaValores("mesesverificacion", "verifriesgo", "Riesgo = 'BAJO'")

        If IsDBNull(objVarMesesBajo) Then
            objVarMesesBajo = 0
        End If

        Classcom = New clsComplejo
        objVarMesesMedio = Classcom.BuscaValores("mesesverificacion", "verifriesgo", "Riesgo = 'MEDIO'")

        If IsDBNull(objVarMesesMedio) Then
            objVarMesesMedio = 0
        End If

        objVarMesesAlto = Classcom.BuscaValores("mesesverificacion", "verifriesgo", "Riesgo = 'ALTO'")

        If IsDBNull(objVarMesesAlto) Then
            objVarMesesAlto = 0
        End If


        'llena Grid documentos morales

        SQL = "SELECT  'VENCIDO' AS ESTATUS,"
        SQL = SQL & " 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"
        SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
        SQL = SQL & " AND A.idcliente = C.idcliente"
        SQL = SQL & " AND B.FECHA_VIGENCIA IS NOT NULL"
        SQL = SQL & " AND D.ID_DoctoCheckList = B.Id_DoctoCheckList"
        'SQL = SQL & " AND C.RIESGO = 'BAJO RIESGO'"
        SQL = SQL & " AND DATEDIFF(MONTH,GETDATE(),FECHA_VIGENCIA) <= 0"
        SQL = SQL & "UNION "
        SQL = SQL & " SELECT 'POR VENCER' AS ESTATUS,"
        SQL = SQL & " 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"
        SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
        SQL = SQL & " AND A.idcliente = C.idcliente"
        SQL = SQL & " AND B.FECHA_VIGENCIA IS NOT NULL"
        SQL = SQL & " AND D.ID_DoctoCheckList = B.Id_DoctoCheckList"
        'SQL = SQL & " AND C.RIESGO = 'BAJO RIESGO'"
        SQL = SQL & " AND DATEDIFF(MONTH,GETDATE(),FECHA_VIGENCIA) = 1"

        clCmp.fdtDataSetTabla(SQL)

        If clCmp.ds.Tables(0).Rows.Count = 0 Then
            'Me.BTSalir.PerformClick()
        Else
            Me.C1TrueDBGrid1.SetDataBinding(clCmp.ds, "Tabla")
            Me.C1TrueDBGrid1.Splits(0).DisplayColumns(0).FetchStyle = True
            Me.C1TrueDBGrid1.Visible = True

        End If

        clCmp = New clsComplejo

        'DATEADD(day, 21, pubdate)

        ' ''llena grid de riesgo
        'VENCIDO BAJO RIESGO 36 MESES
        SQL = "SELECT  distinct 'VENCIDO' AS ESTATUS,"
        SQL = SQL & " A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
        SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C "
        SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
        SQL = SQL & " AND A.idcliente = C.idcliente"
        SQL = SQL & " AND B.fecha_prorroga IS NOT NULL"
        SQL = SQL & " AND C.RIESGO = 'BAJO RIESGO'"
        SQL = SQL & " AND DATEDIFF(MONTH,GETDATE(),dateadd(month,36,fecha_prorroga)) <= " & objVarMesesBajo
        SQL = SQL & "UNION "
        'proximos a vencer
        SQL = SQL & "SELECT  distinct 'POR VENCER' AS ESTATUS,"
        SQL = SQL & " A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
        SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C "
        SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
        SQL = SQL & " AND A.idcliente = C.idcliente"
        SQL = SQL & " AND B.fecha_prorroga IS NOT NULL"
        SQL = SQL & " AND C.RIESGO = 'BAJO RIESGO'"
        SQL = SQL & " AND DATEDIFF(MONTH,GETDATE(),dateadd(month,36,fecha_prorroga)) = " & objVarMesesBajo - 1
        SQL = "UNION "
        'VENCIDO MEDIO RIESGO 18 MESES
        SQL = "SELECT  'VENCIDO' AS ESTATUS,"
        SQL = SQL & " A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
        SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C"
        SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
        SQL = SQL & " AND A.idcliente = C.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
        SQL = SQL & " UNION "
        'PROXIMOS A VENCER
        SQL = SQL & "SELECT  'POR VENCER' AS ESTATUS,"
        SQL = SQL & " A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
        SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C"
        SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
        SQL = SQL & " AND A.idcliente = C.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 - 1
        SQL = SQL & " UNION "
        'VENCIDO MEDIO RIESGO 12 MESES
        SQL = SQL & "SELECT  'VENCIDO' AS ESTATUS,"
        SQL = SQL & " A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
        SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C"
        SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
        SQL = SQL & " AND A.idcliente = C.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
        SQL = SQL & " UNION "
        SQL = SQL & "SELECT  'POR VENCER' AS ESTATUS,"
        SQL = SQL & " A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO "
        SQL = SQL & " FROM CLIENTE A,KYC_CHECKLIST B,KYC_MODELORIESGO C"
        SQL = SQL & " WHERE(A.idcliente = B.idcliente)"
        SQL = SQL & " AND A.idcliente = C.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 - 1

        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

    End Sub

    Private Sub C1TrueDBGrid2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    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
            clsVaribles.IDCliente = IDcliente
        Else
            clsVaribles.IDCliente = 0
        End If

        Doctos.ShowDialog()

    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 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 cmdEliminaCotitular_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTSalir.Click

        'Dim Busqueda As New KYC.Busqueda

        Me.Hide()
        Me.Close()

        'Busqueda.ShowDialog()

    End Sub

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

    End Sub
End Class