Imports System.Data
Imports System.Web
Imports System.Data.SqlClient
Imports System.Net
'Imports System.Net.Mail
Imports System.Web.Mail


Partial Class CategoryDetails
    Inherits System.Web.UI.Page

    Shared g_User As String
    Shared g_Company As String
    Dim Sql As String
    Dim objdt As New DataTable
    Dim objdt1 As New DataTable
    Dim objdt2 As New DataTable
    Dim dssc As New DataSet
    Dim dsc As New DataSet
    Dim objds As New DataSet()
    Dim Str() As String
    Dim catid As String
    Dim flag As String
    Dim dtCategoryDistinct As DataTable
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Session("three") = "yes"
        If Not IsPostBack Then
            Dim con As New SqlConnection                        'Define connection variable for connecting to SQL-Server
            con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")    'Read the ConnectionString from web.config File
            con.Open()        'Open the Connection
            HiddenField1.Value = 0
            g_User = "U1"
            g_Company = "00006Z4"


            If IsNothing(Session("CategoryDistinct")) Then

                If IsNothing(CatDistinct) Then
                    GetCatDistinct()
                End If
                Session("CategoryDistinct") = CatDistinct
                'Session("CategoryDistinct") = ReturnDataTable("Select * from CategoryDistinct")
                dtCategoryDistinct = CType(Session("CategoryDistinct"), DataTable)
            Else
                dtCategoryDistinct = CType(Session("CategoryDistinct"), DataTable)
            End If



            If Session("MailId") <> Nothing Then
                Session("Mail") = Session("MailId")
            Else
                Session("Mail") = Nothing
            End If
            If Request.QueryString("catid") <> Nothing Then
                catid = Request.QueryString("catid")
                Session("Val") = catid
            ElseIf Request.QueryString("gvid") <> Nothing Then
                catid = Request.QueryString("gvid") 'the kid is fetch from this string
                Session("Val") = catid
                dtCategoryDistinct.DefaultView.RowFilter = "Category_KId ='" & RemoveLiterals(catid) & "'"
                'Sql = "Select Category_ParentId,Category_name from CategoryDistinct where Category_KId ='" & RemoveLiterals(catid) & "' and Category_IsDeleted='0'"
                'cmd = New SqlCommand(Sql, con)
                'dr = cmd.ExecuteReader()
                'If dr.Read() Then
                If dtCategoryDistinct.DefaultView.ToTable.Rows(0).Item("Category_ParentId") <> "Root Category" Then
                    catid = dtCategoryDistinct.DefaultView.ToTable.Rows(0).Item("Category_ParentId").ToString ' For Title including  category herarchy
                End If

                Session("MainCatName") = dtCategoryDistinct.DefaultView.ToTable.Rows(0).Item("Category_name").ToString
                hdCatNm.Value = Session("MainCatName")
                lblPrCatName.Text = hdCatNm.Value
                'End If
                'dr.Close()
            End If

            'For Main Category Name Display

            hdCatVal.Value = Session("Val")
            dtCategoryDistinct.DefaultView.RowFilter = "Category_ParentId ='" & RemoveLiterals(catid) & "'"
            Session("MainCatName") = dtCategoryDistinct.DefaultView.ToTable.Rows(0).Item("Category_ParentName").ToString
            hdCatNm.Value = Session("MainCatName")
            lblPrCatName.Text = hdCatNm.Value
            'Dim strCaNm As String = "Select Category_name from Category_Master where Category_KId ='" & hdCatVal.Value & "' and Category_IsDeleted='0'"
            'cmd = New SqlCommand(strCaNm, con)
            'Dim dscnm As New DataSet
            'Dim dtcnm As New DataTable
            'Dim dacnm As New SqlDataAdapter(strCaNm, con)
            'dacnm.Fill(dscnm)
            'dtcnm = dscnm.Tables(0)

            'Session("MainCatName") = dtcnm.Rows(0).Item(0).ToString
            'hdCatNm.Value = Session("MainCatName")
            'lblPrCatName.Text = hdCatNm.Value

            If catid <> "" Then
                Sql = " Select Category_Name as catname, Category_Kid from CategoryDistinct " & _
                      " where category_parentId='" & catid & "' "

                binddatalistSub(catid)
                binddatalistSub1()
                binddatalistSub2()

            End If
        End If    '''' End of Postback
    End Sub
    Private Sub binddatalistSub(ByVal str As String)
        Try
            ' Dim a As New CheckBoxList
            'Dim con As New SqlConnection()
            'con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")

            If Not IsPostBack Then
                dtCategoryDistinct.DefaultView.RowFilter = "category_parentId ='" & RemoveLiterals(catid) & "'"
                objdt = dtCategoryDistinct.DefaultView.ToTable
                For i As Integer = 0 To objdt.Rows.Count - 1
                    Dim nn As New TreeNode
                    nn.Text = objdt.Rows(i).Item("Category_Name").ToString
                    nn.Value = objdt.Rows(i).Item("category_kId").ToString
                    Dlcatlist.Nodes.Add(nn)
                Next
                objdt.Clear()
            End If
            con.Close()
        Catch ex As Exception

        End Try
    End Sub
    Private Sub binddatalistSub1(Optional ByVal str As String = "")
        Try
            'Dim con As New SqlConnection()
            'con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
            'con.Open()

            For i As Integer = 0 To Dlcatlist.Nodes.Count - 1
                dtCategoryDistinct.DefaultView.RowFilter = "category_parentId ='" & RemoveLiterals(Dlcatlist.Nodes(i).Value) & "'"
                objdt1 = dtCategoryDistinct.DefaultView.ToTable
                'ds1 = New DataSet
                'str = " Select c.Category_Name as catname,c1.Category_Name as catname,c1.category_kId " & _
                '     " from Category_Master c,Category_Master c1 where " & _
                '     " c.category_kId='" & objdt.Rows(i).Item(1).ToString & "' and c1.category_parentId='" & objdt.Rows(i).Item(1).ToString & "' and c.Category_IsDeleted='0'"

                'Dim objda As New SqlDataAdapter(str, con)
                'objda.Fill(ds1)
                If objdt1.Rows.Count > 0 Then
                    For j As Integer = 0 To objdt1.Rows.Count - 1
                        Dim cn As New TreeNode
                        cn.Text = objdt1.Rows(j).Item("Category_Name").ToString
                        cn.Value = objdt1.Rows(j).Item("category_kId").ToString
                        Dlcatlist.Nodes(i).ChildNodes.Add(cn)
                        Dlcatlist.Nodes(i).SelectAction = TreeNodeSelectAction.Expand
                    Next
                End If
                objdt1.Clear()
            Next

            con.Close()
        Catch ex As Exception

        End Try
    End Sub
    Private Sub binddatalistSub2(Optional ByVal str As String = "")
        Try
            'Dim con As New SqlConnection()
            'con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
            'con.Open()

            For i As Integer = 0 To Dlcatlist.Nodes.Count - 1
                For j As Integer = 0 To Dlcatlist.Nodes(i).ChildNodes.Count - 1
                    dtCategoryDistinct.DefaultView.RowFilter = "category_parentId ='" & RemoveLiterals(Dlcatlist.Nodes(i).ChildNodes(j).Value) & "'"
                    objdt2 = dtCategoryDistinct.DefaultView.ToTable
                    'ds2 = New DataSet
                    'str = " Select c.Category_Name as catname,c1.Category_Name as catname,c1.category_kId " & _
                    '                                             " from Category_Master c,Category_Master c1 where " & _
                    '                                             " c.category_kId='" & Dlcatlist.Nodes(i).ChildNodes(j).Value & "' and c1.category_parentId='" & Dlcatlist.Nodes(i).ChildNodes(j).Value & "' and c.Category_IsDeleted='0'"
                    'Dim objda As New SqlDataAdapter(str, con)
                    'objda.Fill(ds2)
                    If objdt2.Rows.Count > 0 Then
                        For k As Integer = 0 To objdt2.Rows.Count - 1
                            Dim cn As New TreeNode
                            cn.Text = objdt2.Rows(k).Item("Category_Name").ToString
                            cn.Value = objdt2.Rows(k).Item("category_kId").ToString
                            Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes.Add(cn)
                            Dlcatlist.Nodes(i).ChildNodes(j).SelectAction = TreeNodeSelectAction.Expand
                        Next
                    End If
                    objdt2.Clear()
                Next
            Next
            con.Close()
        Catch ex As Exception

        End Try
    End Sub
  
    'Protected Sub Dlcatlist_SelectedNodeChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Dlcatlist.SelectedNodeChanged
    '    Try
    '        lblPrCatName.Text = hdCatNm.Value
    '        Dim Sqlstr As String = " Select c1.category_kId from Category_Master c,Category_Master c1" & _
    '                               " where c.category_kId='" & hdCatVal.Value & "' and c1.category_parentId='" & hdCatVal.Value & "'"

    '        cmd = New SqlCommand(Sqlstr, con)
    '        Dim dsc As New DataSet
    '        Dim dt As New DataTable
    '        Dim dac As New SqlDataAdapter(Sqlstr, con)
    '        dac.Fill(dsc)
    '        dt = dsc.Tables(0)
    '        If dt.Rows.Count > 0 Then
    '            For i As Integer = 0 To dt.Rows.Count - 1
    '                If dt.Rows(i).Item(0).ToString = Dlcatlist.SelectedNode.Value Then
    '                    Exit Sub
    '                End If
    '            Next

    '            Dim con As New SqlConnection()
    '            con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
    '            con.Open()
    '            dssc = New DataSet

    '            Sql = " Select c.Category_Name as catname,c1.Category_Name as catname,c1.category_kId " & _
    '                                      " from Category_Master c,Category_Master c1 where " & _
    '                                      " c.category_kId='" & Dlcatlist.SelectedNode.Value & "' and c1.category_parentId='" & Dlcatlist.SelectedNode.Value & "'"

    '            Dim da As New SqlDataAdapter(Sql, con)
    '            da.Fill(dssc)
    '            If dssc.Tables(0).Rows.Count > 0 Then
    '                For j As Integer = 0 To dssc.Tables(0).Rows.Count - 1
    '                    Dim cn As New TreeNode
    '                    cn.Text = dssc.Tables(0).Rows(j).Item(1).ToString
    '                    cn.Value = dssc.Tables(0).Rows(j).Item(2).ToString
    '                    Dlcatlist.SelectedNode.ChildNodes.Add(cn)
    '                    Dlcatlist.SelectedNode.SelectAction = TreeNodeSelectAction.Expand
    '                Next
    '            End If
    '        End If
    '        con.Close()
    '        Dlcatlist.ShowExpandCollapse = True
    '    Catch ex As Exception

    '    End Try
    'End Sub
    Protected Sub btnReceiveA_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnReceiveA.Click
        If Session("Mail") <> "" And Dlcatlist.CheckedNodes.Count <> 0 Then

            Dim con As New SqlConnection                                                    'Define connection variable for connecting to SQL-Server
            con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")    'Read the ConnectionString from web.config File
            con.Open()                                                                       'Open the Connection
            Dim dt As DataTable
            Dim cmd As New SqlCommand                                                       'Define command variable to execute the query
            cmd.CommandType = CommandType.StoredProcedure                                   'Set the Command Type to Stored procedure
            cmd.CommandText = "AlertReceive"                                        'Assign the Name of Stored Procedure, to Execute
            cmd.Connection = con

            For i As Integer = 0 To Dlcatlist.Nodes.Count - 1
                If Dlcatlist.Nodes(i).Checked = True Then
                    If Dlcatlist.Nodes(i).ChildNodes.Count > 0 Then
                        For j As Integer = 0 To Dlcatlist.Nodes(i).ChildNodes.Count - 1
                            dt = ReturnDataTable("Select * from Receive_Alert where Receive_Email='" & Session("Mail") & "' and Receive_Sub2CatCode='" & Dlcatlist.Nodes(i).ChildNodes(j).Value & "' and Receive_IsDeleted='0'")
                            If dt.Rows.Count > 0 Then
                                cmd.Parameters.Clear()
                                cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = dt.Rows(0).Item("Receive_Kid").ToString
                                cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                                cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                                cmd.Parameters.Add(New SqlParameter("@Receive_Sub1CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                                cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                                'cmd.Parameters.Add(New SqlParameter("@Receive_Sub3CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                                cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                                cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                                cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                                cmd.Parameters.Add(New SqlParameter("@Receive_IsDeleted", SqlDbType.NVarChar, 10)).Value = "0"
                                cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                                cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Update"
                                cmd.ExecuteNonQuery()
                                dt.Clear()
                                CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")

                            Else
                                cmd.Parameters.Clear()
                                cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = NewPrimaryKey(Me)
                                cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                                cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                                cmd.Parameters.Add(New SqlParameter("@Receive_Sub1CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                                cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                                'cmd.Parameters.Add(New SqlParameter("@Receive_Sub3CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                                cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                                cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                                cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                                cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                                cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Insert"

                                cmd.ExecuteNonQuery()
                                CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                            End If
                        Next
                    Else
                        dt = ReturnDataTable("Select * from Receive_Alert where Receive_Email='" & Session("Mail") & "' and Receive_Sub2CatCode='" & Dlcatlist.Nodes(i).Value & "' and Receive_IsDeleted='0'")
                        If dt.Rows.Count > 0 Then
                            cmd.Parameters.Clear()
                            cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = dt.Rows(0).Item("Receive_Kid").ToString
                            cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                            cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                            cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                            'cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                            'cmd.Parameters.Add(New SqlParameter("@Receive_Sub3CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                            cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                            cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                            cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                            cmd.Parameters.Add(New SqlParameter("@Receive_IsDeleted", SqlDbType.NVarChar, 10)).Value = "0"
                            cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                            cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Update"
                            cmd.ExecuteNonQuery()
                            dt.Clear()
                            CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                        Else
                            cmd.Parameters.Clear()
                            cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = NewPrimaryKey(Me)
                            cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                            cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                            cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                            'cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                            'cmd.Parameters.Add(New SqlParameter("@Receive_Sub3CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                            cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                            cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                            cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                            cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                            cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Insert"

                            cmd.ExecuteNonQuery()
                            CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                        End If
                    End If
                Else
                    For j As Integer = 0 To Dlcatlist.Nodes(i).ChildNodes.Count - 1
                        If Dlcatlist.Nodes(i).ChildNodes(j).Checked = True Then
                            If Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes.Count > 0 Then
                                For k As Integer = 0 To Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes.Count - 1
                                    dt = ReturnDataTable("Select * from Receive_Alert where Receive_Email='" & Session("Mail") & "' and Receive_Sub2CatCode='" & Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value & "' and Receive_IsDeleted='0'")
                                    If dt.Rows.Count > 0 Then
                                        cmd.Parameters.Clear()
                                        cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = dt.Rows(0).Item("Receive_Kid").ToString
                                        cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                                        cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                                        cmd.Parameters.Add(New SqlParameter("@Receive_Sub1CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                                        ' cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                                        cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                                        cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                                        cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                                        cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                                        cmd.Parameters.Add(New SqlParameter("@Receive_IsDeleted", SqlDbType.NVarChar, 10)).Value = "0"
                                        cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                                        cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Update"

                                        cmd.ExecuteNonQuery()
                                        dt.Clear()
                                        CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                                    Else
                                        cmd.Parameters.Clear()
                                        cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = NewPrimaryKey(Me)
                                        cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                                        cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                                        cmd.Parameters.Add(New SqlParameter("@Receive_Sub1CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                                        ' cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                                        cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                                        cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                                        cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                                        cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                                        cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                                        cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Insert"

                                        cmd.ExecuteNonQuery()
                                        CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                                    End If
                                Next
                            Else
                                dt = ReturnDataTable("Select * from Receive_Alert where Receive_Email='" & Session("Mail") & "' and Receive_Sub2CatCode='" & Dlcatlist.Nodes(i).ChildNodes(j).Value & "' and Receive_IsDeleted='0'")
                                If dt.Rows.Count > 0 Then
                                    cmd.Parameters.Clear()
                                    cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = dt.Rows(0).Item("Receive_Kid").ToString
                                    cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                                    cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                                    cmd.Parameters.Add(New SqlParameter("@Receive_Sub1CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                                    cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                                    'cmd.Parameters.Add(New SqlParameter("@Receive_Sub3CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                                    cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                                    cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                                    cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                                    cmd.Parameters.Add(New SqlParameter("@Receive_IsDeleted", SqlDbType.NVarChar, 10)).Value = "0"
                                    cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                                    cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Update"

                                    cmd.ExecuteNonQuery()
                                    dt.Clear()
                                    CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                                Else
                                    cmd.Parameters.Clear()
                                    cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = NewPrimaryKey(Me)
                                    cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                                    cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                                    cmd.Parameters.Add(New SqlParameter("@Receive_Sub1CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                                    cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                                    'cmd.Parameters.Add(New SqlParameter("@Receive_Sub3CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                                    cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                                    cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                                    cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                                    cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                                    cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Insert"

                                    cmd.ExecuteNonQuery()
                                    CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                                End If
                            End If
                        Else

                            If Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes.Count > 0 Then
                                For k As Integer = 0 To Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes.Count - 1
                                    If Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Checked = True Then
                                        dt = ReturnDataTable("Select * from Receive_Alert where Receive_Email='" & Session("Mail") & "' and Receive_Sub2CatCode='" & Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value & "' and Receive_IsDeleted='0'")
                                        If dt.Rows.Count > 0 Then
                                            cmd.Parameters.Clear()
                                            cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = dt.Rows(0).Item("Receive_Kid").ToString
                                            cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                                            cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                                            cmd.Parameters.Add(New SqlParameter("@Receive_Sub1CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                                            '  cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                                            cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                                            cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                                            cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                                            cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                                            cmd.Parameters.Add(New SqlParameter("@Receive_IsDeleted", SqlDbType.NVarChar, 10)).Value = "0"
                                            cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                                            cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Update"
                                            cmd.ExecuteNonQuery()
                                            dt.Clear()
                                            CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                                        Else
                                            cmd.Parameters.Clear()
                                            cmd.Parameters.Add(New SqlParameter("@Receive_Kid", SqlDbType.NVarChar, 20)).Value = NewPrimaryKey(Me)
                                            cmd.Parameters.Add(New SqlParameter("@Receive_Email", SqlDbType.NVarChar, 200)).Value = Session("Mail")
                                            cmd.Parameters.Add(New SqlParameter("@Receive_CatCode", SqlDbType.NVarChar, 50)).Value = Session("Val")
                                            cmd.Parameters.Add(New SqlParameter("@Receive_Sub1CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).Value
                                            '  cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).Value
                                            cmd.Parameters.Add(New SqlParameter("@Receive_Sub2CatCode", SqlDbType.NVarChar, 20)).Value = Dlcatlist.Nodes(i).ChildNodes(j).ChildNodes(k).Value
                                            cmd.Parameters.Add(New SqlParameter("@Receive_date", SqlDbType.DateTime)).Value = Date.Today
                                            cmd.Parameters.Add(New SqlParameter("@Receive_CompanyId", SqlDbType.NVarChar, 20)).Value = Trim(g_Company)
                                            cmd.Parameters.Add(New SqlParameter("@Receive_UserId", SqlDbType.NVarChar, 20)).Value = Trim(g_User)
                                            cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 100)).Value = "Receive_Alert"
                                            cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 50)).Value = "Insert"

                                            cmd.ExecuteNonQuery()
                                            CreateMessageAlert(Me, "Record Saved Successfully...", "StrKeyVal")
                                        End If
                                    Else
                                    End If
                                Next
                            End If
                        End If
                    Next
                End If
            Next
            'Execute the Query
            ' showmessage("Record Saved Successfully", Label2)

            divTree.Visible = False
            divWelcome.Visible = True
            tblReg.Visible = True
            Label1.Text = "Dear, " & Session("Mail")
            BindGrid()
            Adminemail()
            Buyeremail()

            Dim cmd1 As New SqlCommand                                                       'Define command variable to execute the query
            cmd1.CommandType = CommandType.Text                                   'Set the Command Type to Stored procedure
            cmd1.CommandText = "Update Receive_Alert set Receive_date='" & Date.Today & "' where Receive_Email='" & Session("Mail") & "' and Receive_IsDeleted='0'"
            cmd1.Connection = con
            cmd1.ExecuteNonQuery()
        Else
            'showmessage("Please Select Atleast One Category From Category List", Label2)
            CreateMessageAlert(Me, "Please Select Atleast One Product From Product List.", "StrKeyVal")
        End If
        ' Session("Mail") = Nothing
    End Sub
    Public Function BindGrid()
        Dim Sqlstr As String
        HiddenField1.Value = 0
        'If flag = 1 Then
        '    Sqlstr = " Select distinct category_name as SelectedProducts from Category_master,Receive_alert" & _
        '             " where Category_kid = Receive_sub2catCode and Receive_Email='" & Session("Mail") & "' and Receive_IsDeleted='0'"

        'Else
        Sqlstr = " Select distinct category_name as [Selected Products] from Category_master,Receive_alert" & _
                 " where (Category_kid = Receive_sub2catCode)  and day(Receive_date)='" & Date.Today.Day & "' and Receive_Email='" & Session("Mail") & "' and Receive_IsDeleted='0'"

        'End If

        cmd = New SqlCommand(Sqlstr, con)
        Dim dt As New DataTable

        Dim dac As New SqlDataAdapter(Sqlstr, con)
        dac.Fill(dsc)
        dt = dsc.Tables(0)

        grdCat.DataSource = dt
        grdCat.DataBind()
        dsc = Nothing
    End Function
    Public Sub Adminemail()
        Try
            Dim StrMail As String = ""
            StrMail = "<div style='font-family:Verdana;FONT-SIZE: 10pt;'>"
            StrMail += "User, <b> " + Session("Mail") & " </b> wants to Use the services of Atcomaart Product Alert Receive<br/>"
            StrMail += "<br/> He has Selected Following Products For Receive Alerts :-<br/>"
            StrMail += "<table width='700' border='0' cellspacing='1' cellpadding='1' align='left' style='BORDER-RIGHT: dimgray 1px solid; position:fixed ; BORDER-TOP: dimgray 1px solid; FONT-SIZE: 10pt; BORDER-LEFT: dimgray 1px solid;  BORDER-BOTTOM: dimgray 1px solid; FONT-FAMILY: Verdana;BACKGROUND-COLOR: #f8f8f8'>"

            StrMail += " <tr> <td><b> Selected Products </b></td>"
            StrMail += "</tr>"
            If grdCat.Rows.Count > 0 Then
                For i As Integer = 0 To grdCat.Rows.Count - 1
                    StrMail += "<tr><td>" & grdCat.Rows(i).Cells(0).Text & "</td>"
                    StrMail += "</tr>"
                Next
            End If
            StrMail += "</table>"
            StrMail += "</Div>"

            ' ''Dim mailmsg As New MailMessage()
            ' ''mailmsg.From = New MailAddress("contact.atcomaart@atco.in", "www.atcomaart.com")
            ' ''mailmsg.To.Add(New MailAddress("contact.atcomaart@atco.in", "Atcomaart"))
            ' ''mailmsg.IsBodyHtml = True
            ' ''mailmsg.Priority = MailPriority.High
            ' ''mailmsg.Body = StrMail
            ' ''mailmsg.Subject = "AtcoMaart Product Alerts"
            ' ''Dim smtp As New SmtpClient("smtp.rediffmailpro.com")
            ' ''smtp.Credentials = New Net.NetworkCredential("contact.atcomaart@atco.in", "traamocta")
            ' ''smtp.Send(mailmsg)
            MailSender.SendEmail("contact@atcomaart.com", "AtcoMaart Product Alerts", StrMail, MailFormat.Html, "")

        Catch ex As Exception
            
        End Try
    End Sub
    Public Sub Buyeremail()
        Try
            Dim StrMail As String = ""
            StrMail = "<div style='font-family:Arial;FONT-SIZE: 10pt;'>"
            StrMail += "Dear ,<b> " & Session("Mail") & "</b><br/>"
            StrMail += "Thanks for using the services of AtcoMaart Product Alert <br/> "
            StrMail += "<b>You have Selected Following Products to Receive Alerts :</b><br/> "
            StrMail += "<table width='600' border='1' cellspacing='0' cellpadding='1' align='left' style='BORDER-RIGHT: dimgray 1px solid; position:fixed ; BORDER-TOP: dimgray 1px solid; FONT-SIZE: 10pt; BORDER-LEFT: dimgray 1px solid;  BORDER-BOTTOM: dimgray 1px solid; FONT-FAMILY: Arial;BACKGROUND-COLOR: #f8f8f8'>"
            StrMail += "<tr> <td><b> Selected Products </b></td>"
            StrMail += "</tr>"
            For i As Integer = 0 To grdCat.Rows.Count - 1
                StrMail += "<tr><td>" & grdCat.Rows(i).Cells(0).Text & "</td>"
                StrMail += "</tr>"
            Next
            StrMail += "</table><br/><br/><br/><br/><br/><br/><br/><br/>Have a nice day.<br/><br/>"
            StrMail += "Thanks & Regards,<br/>ATCOMAART TEAM<br/><br/><br/><br/><a href='www.atcomaart.com'> (www.atcomaart.com)</a>"
            StrMail += "</Div>"

            ' ''Dim mailmsg As New MailMessage()
            ' ''mailmsg.From = New MailAddress("contact.atcomaart@atco.in", "AtcoMaart Ltd.")
            ' ''mailmsg.To.Add(New MailAddress(Session("Mail").ToString, Session("Mail").ToString))
            ' ''mailmsg.IsBodyHtml = True
            ' ''mailmsg.Priority = MailPriority.High
            ' ''mailmsg.Body = StrMail
            ' ''mailmsg.Subject = "Product Alert Services From AtcoMaart"
            ' ''Dim smtp As New SmtpClient("smtp.rediffmailpro.com")
            ' ''smtp.Credentials = New Net.NetworkCredential("contact.atcomaart@atco.in", "traamocta")
            ' ''smtp.Send(mailmsg)

            MailSender.SendEmail(Session("Mail").ToString, "Product Alert Services From AtcoMaart", StrMail, MailFormat.Html, "")


        Catch ex As Exception

        End Try
    End Sub
    Protected Sub btnview_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnview.Click
        ' flag = 1
        HiddenField1.Value = 1
        BindGrid1()
    End Sub

    'Protected Sub grdCat_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdCat.PageIndexChanging
    '    grdCat.PageIndex = e.NewPageIndex
    '    BindGrid()
    'End Sub
    Protected Sub grdCat_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdCat.PageIndexChanging
        'grdCat.PageIndex = e.NewPageIndex
        If HiddenField1.Value = 1 Then
            grdCat.PageIndex = e.NewPageIndex
            BindGrid1()
        Else
            grdCat.PageIndex = e.NewPageIndex
            BindGrid()
        End If
    End Sub
    Public Function BindGrid1()
        Dim Sqlstr As String

        Sqlstr = " Select distinct category_name as SelectedProducts from Category_master,Receive_alert" & _
                 " where Category_kid = Receive_sub2catCode and Receive_Email='" & Session("Mail") & "' and Receive_IsDeleted='0'"

        cmd = New SqlCommand(Sqlstr, con)
        Dim dt As New DataTable

        Dim dac As New SqlDataAdapter(Sqlstr, con)
        dac.Fill(dsc)
        dt = dsc.Tables(0)

        grdCat.DataSource = dt
        grdCat.DataBind()
        dsc = Nothing
    End Function
End Class