Public Class FrmAlarmasExp

    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
        Dim dtFechaHoy As String

        Try

            dtFechaHoy = Classcom.ObtenFecha(Now().Date, "dd/mm/yyyy")

            Select Case CMBRango.Text
                Case Is = "<= 1 Mes"
                    'SUMA DEACUERDO AL RIESGO 36
                    dtFecha1 = Now().Date
                    'DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
                    dtFecha2 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
                Case Is = "1 a 3 Meses"
                    'SUMA DEACUERDO AL RIESGO 36
                    'dtFecha1 = Now()
                    dtFecha1 = Now().Date
                    dtFecha1 = DateAdd(DateInterval.Month, 1, CDate(dtFecha1))
                    dtFecha2 = DateAdd(DateInterval.Month, 2, CDate(dtFecha1))
                Case Is = "3 a 6 Meses"
                    dtFecha1 = Now().Date
                    dtFecha1 = DateAdd(DateInterval.Month, 3, CDate(dtFecha1))
                    dtFecha2 = DateAdd(DateInterval.Month, 3, CDate(dtFecha1))
                Case Is = "6 Meses a 1 Aņo"
                    dtFecha1 = Now().Date
                    dtFecha1 = DateAdd(DateInterval.Month, 6, CDate(dtFecha1))
                    dtFecha2 = DateAdd(DateInterval.Month, 6, CDate(dtFecha1))
            End Select


            clCmp = New clsComplejo
            ' ''llena grid de riesgo
            'VENCIDO BAJO RIESGO 36 MESES
            SQL = "SELECT  distinct "
            SQL = SQL & " CT.nic as 'NUMERO DE CLIENTE',A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO,dateadd(month,36,fecha_prorroga) AS 'FECHA VERIFICACION' "
            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 C.RIESGO = 'BAJO RIESGO'"

            If CMBRango.Text = "<= 1 Mes" Then

                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)"

            Else
                SQL = SQL & " AND B.fecha_prorroga IS NOT NULL"
                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)"

            End If

            SQL = SQL & " UNION "
            'SQL = ""
            'VENCIDO MEDIO RIESGO 18 MESES
            SQL = SQL & "SELECT  DISTINCT "
            SQL = SQL & " CT.nic as 'NUMERO DE CLIENTE',A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO,dateadd(month,18,fecha_prorroga) AS 'FECHA VERIFICACION' "
            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'"

            If CMBRango.Text = "<= 1 Mes" Then

                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)"
            Else
                SQL = SQL & " AND B.fecha_prorroga IS NOT NULL"
                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)"

            End If
            SQL = SQL & " UNION "

            SQL = SQL & "SELECT DISTINCT "
            SQL = SQL & " CT.nic as 'NUMERO DE CLIENTE',A.CLIENTENOM AS 'NOMBRE CLIENTE',C.RIESGO AS RIESGO,dateadd(month,12,fecha_prorroga) AS 'FECHA VERIFICACION' "
            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'"


            If CMBRango.Text = "<= 1 Mes" Then

                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)"
            Else
                SQL = SQL & " AND B.fecha_prorroga IS NOT NULL"
                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)"

            End If

            clCmp.fdtDataSetTabla(SQL)

            clsVaribles.strSQL = SQL

            'If clCmp.ds.Tables(0).Rows.Count <> 0 Then
            Me.C1TrueDBGrid1.SetDataBinding(clCmp.ds, "Tabla")
            'Me.Grid2.Splits(0).DisplayColumns(0).FetchStyle = True
            Me.C1TrueDBGrid1.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 BTSalir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTSalir.Click
        Me.Close()
    End Sub

    Private Sub FrmAlarmasExp_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 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
        Dim Cartas As New FrmCartas

        If C1TrueDBGrid1.Columns(0).Text <> "" Then
            clsVaribles.lngFiltroIDCliente = C1TrueDBGrid1.Columns(0).Text
        Else
            clsVaribles.lngFiltroIDCliente = 0
        End If

        Cartas.ShowDialog()
    End Sub
End Class