Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Imports System.String
Imports ModCommon
Partial Class UserControl_ShowProductDetails
    Inherits System.Web.UI.UserControl

    Dim str, str1 As String
    Dim slc1 = "Select Date"
    'Dim slc2 = "MM"
    Dim slc3 = "Select Year"
    Dim slc4 = "Select Country"
    Dim strcon As String = ConfigurationSettings.AppSettings.Item("ConnectionString")
    Dim con As New SqlConnection(strcon)
    Dim cmd As New SqlCommand()
    Dim dr As SqlDataReader
    Shared Category(10) As String
    Shared i As Integer
    Shared CatID1 As String
    Shared CatID2 As String
    Shared CatID3 As String
    Shared c As String
    Dim sproduct As Integer
    Shared dt1 As DataTable
    Dim categories As DataTable
    Dim count As Integer
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
        'Put user code to initialize the page here
        Try

        
            Dim parentid As String
            If Not Me.Page.IsPostBack Then
                gvProducts.Visible = False
                '------getting the value of subcatid from the getparents function that declared in this form--------
                sproduct = 0
                'Session("g_User") = "0000]28"
                'Session("g_Company") = "00006Z4"
                'Session("g_FinYear") = "000010N"
                g_FinYear = Session("g_FinYear").ToString()
                g_Company = Session("g_Company").ToString()
                g_User = Session("g_User").ToString()
                parentid = Request.QueryString("subcatid")
                If parentid = Nothing Or parentid = "Root" Then
                    parentid = "Root Category"
                Else
                    parentid = Request.QueryString("subcatid")
                End If
                i = 0
                LoadCategory(parentid)
                'SpSubCat.Visible = False
                ' SpSubCat1.Visible = False
                ''extra code
                cbosubCategory1.Visible = False
                cbosubCategory2.Visible = False
                ' txtcertification.Visible = False
            End If

            cboCategory.Visible = True
        Catch ex As Exception

        End Try
    End Sub



    Private Sub cboCategory_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCategory.SelectedIndexChanged
        Try

      
            gvProducts.Visible = False
            If cboCategory.SelectedItem.Text = "Select Category" Then
                ' Panel1.Visible = True
                Label2.Visible = True
                Label2.Text = "Please Select the Category"
                '  Spcat.InnerText = cboCategory.SelectedItem.Text + " > "
                '  SpSubCat1.Visible = False
                ''extrs code
                cbosubCategory1.Visible = False
                cbosubCategory2.Visible = False
                Label15.Visible = False
            Else
                Session("lastsubcategory") = ""
                Session("lastsubcategory") = cboCategory.SelectedItem.Value
                CatID1 = cboCategory.SelectedItem.Value
                CatID2 = ""
                CatID3 = ""
                getparents(cboCategory.SelectedItem.Value)
                LoadSubCategory1(cboCategory.SelectedItem.Value)
            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub cbosubCategory1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbosubCategory1.SelectedIndexChanged
        Try

      
            gvProducts.Visible = False
            Session("lastsubcategory") = cbosubCategory1.SelectedItem.Value
            CatID2 = cbosubCategory1.SelectedItem.Value
            CatID1 = ""
            CatID3 = ""
            getparents(cbosubCategory1.SelectedItem.Value)
            LoadSubCategory2(cbosubCategory1.SelectedItem.Value)
            '   SpSubCat.InnerText = cbosubCategory1.SelectedItem.Text + " > "
            '  Session("subcategory1") = SpSubCat.InnerText
            'Response.Write(Session("subcategory1"))
            '   SpSubCat.Visible = True
            '   SpSubCat1.Visible = True
            ''extra code
            cbosubCategory1.Visible = True
            ''cbosubCategory2.Visible = True
            '   SpSubCat1.InnerText = "Select Sub Category"

        Catch ex As Exception

        End Try

    End Sub
    Private Sub cbosubCategory2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbosubCategory2.SelectedIndexChanged
        Try
            gvProducts.Visible = False
            Session("lastsubcategory") = cbosubCategory2.SelectedItem.Value
            CatID3 = cbosubCategory2.SelectedItem.Value
            CatID1 = ""
            CatID2 = ""
            getparents(cbosubCategory2.SelectedItem.Value)
            LoadSubCategory3(cbosubCategory2.SelectedItem.Value)
        Catch ex As Exception

        End Try

    End Sub
    Private Sub LoadSubCategory3(ByVal intId As String)
        Try
            Dim strsql As String
            'strsql = "select cat_id as id,catname as Name from categorymaster where parent_id=" & intId & "order by catname asc"
            strsql = "SELECT Category_Kid as id, Category_Name as Name FROM Category_Master WHERE Category_ParentId = '" & intId & "' ORDER BY Category_Name"
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            con.Open()

            Dim cmd As New SqlCommand(strsql, con)
            Dim mReader As SqlDataReader
            mReader = cmd.ExecuteReader
            cboCategory.Items.Clear()
            cboCategory.Items.Add(New ListItem("Select Subcategory", "-4"))
            If mReader.Read Then
                cboCategory.Items.Add(New ListItem(mReader("Name"), mReader("id")))
                While mReader.Read
                    cboCategory.Items.Add(New ListItem(mReader("Name"), mReader("id")))
                End While
                Label14.Visible = True
                Label15.Visible = False
                Label16.Visible = False
                cbosubCategory1.Visible = False
                cbosubCategory2.Visible = False
                cboCategory.ForeColor = Drawing.Color.Red
                cbosubCategory1.ForeColor = Drawing.Color.Black
                cbosubCategory2.ForeColor = Drawing.Color.Black
                Label14.Text = "Select the next level"
            Else
                Label14.Visible = False
                Label15.Visible = False
                Label16.Visible = False
                Label2.Visible = True
                Label2.Text = "Last category selected."
                Session("CategoryId") = intId

                'Response.Redirect("Product_Information.aspx?CatID=" & intId)
            End If
            mReader.Close()
        Catch ex As Exception

        End Try

    End Sub
    Private Sub LoadCategory(ByVal parentid As String)
        Try
            Dim strsql As String
            'strsql = "select cat_id as id,catname as Name from categorymaster where parent_id=" & parentid & "order by catname asc"
            strsql = "SELECT Category_Kid as id, Category_Name as Name FROM Category_Master WHERE Category_ParentId = '" & parentid & "' ORDER BY Category_Name"
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            con.Open()
            Dim cmd As New SqlCommand(strsql, con)
            Dim mReader As SqlDataReader
            mReader = cmd.ExecuteReader
            cboCategory.Items.Clear()
            cboCategory.Items.Add(New ListItem("Select Category", "-1"))
            If mReader.Read Then
                cboCategory.Items.Add(New ListItem(mReader("Name"), mReader("id")))
                While mReader.Read
                    cboCategory.Items.Add(New ListItem(mReader("Name"), mReader("id")))
                End While
                Label14.Visible = True
                Label15.Visible = False
                Label16.Visible = False

                cboCategory.ForeColor = Drawing.Color.Red
                cbosubCategory1.ForeColor = Drawing.Color.Black
                cbosubCategory2.ForeColor = Drawing.Color.Black

                Label14.Text = "Select the category"
            Else
                Label14.Visible = False
                Label15.Visible = False
                Label16.Visible = False
                Label2.Visible = True
                Label2.Text = "Last category selected."
                Session("CategoryId") = parentid

                'Response.Redirect("Product_Information.aspx?CatID =" & parentid)
            End If
            mReader.Close()

        Catch ex As Exception

        End Try

    End Sub
    Private Sub LoadSubCategory1(ByVal intId As String)
        Try
            Dim strsql As String
            'strsql = "Select cat_id as id,catname as Name from categorymaster where parent_id=" & intId & "order by catname asc"
            strsql = "SELECT Category_Kid as id, Category_Name as Name FROM Category_Master WHERE Category_ParentId = '" & intId & "' ORDER BY Category_Name"
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            con.Open()

            Dim cmd As New SqlCommand(strsql, con)
            Dim mReader As SqlDataReader
            mReader = cmd.ExecuteReader
            cbosubCategory1.Items.Clear()
            cbosubCategory1.Items.Add(New ListItem("Select Subcategory", "-2"))
            If mReader.Read Then
                cbosubCategory1.Items.Add(New ListItem(mReader("Name"), mReader("id")))
                While mReader.Read
                    cbosubCategory1.Items.Add(New ListItem(mReader("Name"), mReader("id")))
                End While
                Label14.Visible = False
                Label15.Visible = True
                Label16.Visible = False
                cboCategory.ForeColor = Drawing.Color.Black
                cbosubCategory1.ForeColor = Drawing.Color.Red
                cbosubCategory2.ForeColor = Drawing.Color.Black
                cbosubCategory1.Visible = True
                cbosubCategory2.Visible = False
                Label15.Text = "Select the next level"
            Else
                'Response.Write("check")
                cbosubCategory1.Visible = False
                cbosubCategory2.Visible = False
                Label14.Visible = False
                Label15.Visible = False
                Label16.Visible = False
                Label2.Visible = True
                Label2.Text = "Last category selected."
                Session("CategoryId") = intId

                'Response.Redirect("Product_Information.aspx?CatID =" & intId)
            End If
            mReader.Close()
        Catch ex As Exception

        End Try

    End Sub
    Private Sub LoadSubCategory2(ByVal intId As String)
        Try
            Dim strsql As String
            'strsql = "select cat_id as id,catname as Name from categorymaster where parent_id=" & intId & "order by catname asc"
            strsql = "SELECT Category_Kid as id, Category_Name as Name FROM Category_Master WHERE Category_ParentId = '" & intId & "' ORDER BY Category_Name"
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            con.Open()

            Dim cmd As New SqlCommand(strsql, con)
            Dim mReader As SqlDataReader
            mReader = cmd.ExecuteReader
            cbosubCategory2.Items.Clear()
            cbosubCategory2.Items.Add(New ListItem("Select Subcategory", "-3"))
            If mReader.Read Then
                cbosubCategory2.Items.Add(New ListItem(mReader("Name"), mReader("id")))
                While mReader.Read
                    cbosubCategory2.Items.Add(New ListItem(mReader("Name"), mReader("id")))
                End While
                Label14.Visible = False
                Label15.Visible = False
                Label16.Visible = True
                cboCategory.ForeColor = Drawing.Color.Black
                cbosubCategory1.ForeColor = Drawing.Color.Black
                cbosubCategory2.ForeColor = Drawing.Color.Red
                Label16.Text = "Select the next level"
                cbosubCategory2.Visible = True
            Else
                cbosubCategory2.Visible = False
                Label14.Visible = False
                Label15.Visible = False
                Label16.Visible = False
                Label2.Visible = True
                Label2.Text = "Last category selected."
                Session("CategoryId") = intId

                'Response.Redirect("Product_Information.aspx?CatID =" & intId)
            End If
            mReader.Close()

        Catch ex As Exception

        End Try

    End Sub
    Private Function getparents(ByVal i As String) As String
        Try
            Dim strsql As String
            'strsql = "select parent_id ,catname as Name from categorymaster where cat_id=" & i
            strsql = "SELECT Category_ParentId, Category_Name FROM Category_Master WHERE Category_Kid = '" & i & "'"
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            con.Open()
            Dim cmd As New SqlCommand(strsql, con)
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            Dim j As String
            Dim flag As Boolean = False
            If dr.Read Then
                j = dr(0)
                'Response.Write(j)
                flag = True
            Else
                'Response.Write("not")
            End If

            If flag = True Then
                ''If j = 0 Then
                ''Exit Function
                ''ElseIf j <> 0 Then
                str1 = dr(1) & " > " & str1


                'str = "<a href=../ProductDetails/prodinfo.aspx?subcatid=" & dr(0) & ">" & dr(1) & "</a>" & " > " & str
                str = "<a href=./category-wise-product.aspx?subcatid=" & dr(0) & ">" & dr(1) & "</a>" & " > " & str
                getparents(j)
                ''End If
            End If
            dr.Close()
            con.Close()
            Label13.Text = str
            Session("categorylevel") = Nothing
            Session("categorylevel") = str1
            Session("Category") = str1
        Catch ex As Exception

        End Try

    End Function
    Public Sub FillCategoryName(ByVal Catid As String)
        Try
            Dim dt As DataTable
            dt = ReturnDataTable("Select * From Category_Master Where Category_Kid ='" & Catid & "' and Category_Isdeleted = '0' and Category_CompanyId ='" & g_Company & "'")
            Dim ParentId As String
            ParentId = dt.Rows(0).Item(3).ToString()
            If ParentId = "Root Category" Then
                'Label1.Text += dt.Rows(0).Item(2).ToString()
                'Dim CategoryName(10) As String
                Dim j As Integer
                Category(i) = dt.Rows(0).Item(2).ToString() & "  >  "
                'Array.Reverse(Category)
                For j = 0 To i
                    '  lblError.Text += Category(i)
                    i = i - 1
                Next
                Exit Sub
            Else
                Category(i) = dt.Rows(0).Item(2).ToString() & "  >  "
                i = i + 1
                'Label1.Text += dt.Rows(0).Item(2).ToString() & "  >  "
                FillCategoryName(dt.Rows(0).Item(3).ToString())
            End If
            'GridView1.DataSource = dt
            'GridView1.DataBind()

        Catch ex As Exception

        End Try
    End Sub

    Public Function NewPrimaryKey1(ByVal aspxpage As System.Web.UI.UserControl) As String
        Try
            NewPrimaryKey1 = ""

            Dim mycon As New SqlConnection()
            mycon.ConnectionString = ConfigurationManager.AppSettings("ConnectionString")     'Read the ConnectionString from web.config File
            mycon.Open()
            Dim myCommand As New SqlCommand("New_Kid", mycon)
            myCommand.CommandType = CommandType.StoredProcedure
            Dim retValParam As New SqlParameter("@sKey", SqlDbType.Char, 7)
            retValParam.Direction = ParameterDirection.Output
            retValParam.IsNullable = True
            retValParam.Size = 7
            myCommand.Parameters.Add(retValParam)
            Dim reader As SqlDataReader = myCommand.ExecuteReader()
            NewPrimaryKey1 = retValParam.Value.ToString

            myCommand.Dispose()
            If Not reader.IsClosed Then reader.Close()
            If mycon.State = ConnectionState.Open Then mycon.Close()

            'CreateMessageAlert(Me, Ex.Message, "StrKeyVal")
            NewPrimaryKey1 = ""
        Catch ex As Exception

        End Try
    End Function
    Protected Sub btnSupplierName_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSupplierName.Click
        Try
            If cboCategory.SelectedItem.Text = "Select Category" Then
                Label2.Visible = True
                Label2.Text = "Please Select Category.."
                Label2.Text = ""
                gvProducts.DataSource = Nothing
                gvProducts.DataBind()
                Exit Sub
            Else
                Label2.Visible = False
            End If
            If CatID1.ToString.Trim <> "" Then
                c = "'" & CatID1.ToString.Trim & "'"
            ElseIf CatID2.ToString.Trim <> "" Then
                c = "'" & CatID2.ToString.Trim & "'"
            ElseIf CatID3.ToString.Trim <> "" Then
                c = "'" & CatID3.ToString.Trim & "'"
            End If
            If Not IsNothing(dt1) Then
                dt1.Rows.Clear()
                dt1.Columns.Clear()
                dt1.Clear()
            End If
            gvProducts.PageIndex = 0
            Dim CatBool As Boolean = True
            c = c.Replace("'", "")
            dt1 = ReturnDataTable("SELECT     Category_Kid,Category_Name  FROM Category_Master WHERE     (Category_ParentId = '" & c & "') AND (Category_IsDeleted = N'0')")
            dt1.Columns.Add("NoOfProducts")
            dt1.Columns.Add("SrNo")
            Dim no As Integer = 0
            For Each row As DataRow In dt1.Rows
                sproduct = 0
                no = no + 1
                getsubcategory(RemoveLiterals(row("Category_Kid")), sproduct)
                Dim r As DataRow = dt1.NewRow()
                row("NoOfProducts") = sproduct
                row("SrNo") = no
            Next
            gvProducts.DataSource = dt1
            gvProducts.Visible = True
            gvProducts.DataBind()
            'dt1.Clear()
            'dt1 = Nothing
        Catch ex As Exception

        End Try
    End Sub
    Protected Sub getsubcategory(ByVal categoryid As String, ByRef sproduct As Integer)
        Try

       
            Dim str As String = "SELECT     count(*)  FROM Category_Master WHERE     (Category_ParentId = '" & categoryid & "') AND (Category_IsDeleted = N'0')"
            If getcount(categoryid, str) > 0 Then
                Dim categories As DataTable = ReturnDataTable("SELECT     Category_Kid, Category_Name as Name  FROM Category_Master WHERE     (Category_ParentId = '" & categoryid & "') AND (Category_IsDeleted = N'0')")
                For Each row As DataRow In categories.Rows
                    getsubcategory(row("Category_Kid"), sproduct)
                Next
            Else
                Dim strqry As String = "select count(*) from product_master where product_categoryid='" & categoryid & "' and product_isdeleted='0' and product_approved='Y'"
                sproduct = sproduct + getcount(categoryid, strqry)
                Exit Sub
            End If
        Catch ex As Exception

        End Try
    End Sub
    Protected Function getcount(ByVal categoryid As String, ByVal str As String) As Integer
        Dim con As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))

        Dim cmd As New SqlCommand(str, con)

        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        count = Convert.ToInt32(cmd.ExecuteScalar())
        con.Close()
        Return count
    End Function
    Public Sub BindGrid()
        'Dim dt As DataTable
        Try
            Dim c1 As String = c.ToString
            Dim i As Integer
            Dim myCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
            Dim myDataSet As DataSet = New DataSet
            Dim myCommandText As String = "SELECT DISTINCT Product_Master.Product_SupplierId, Supplier_Master.Supplier_Name as [Name], Supplier_Master.Supplier_TelExtn as [TelExtn] , Supplier_Master.Supplier_TelNumber as [TelNumber] FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid INNER JOIN Category_Master ON Product_Master.Product_CategoryId = Category_Master.Category_Kid WHERE Product_Master.Product_CategoryId In ( " & c.ToString.Trim & " )"
            Dim myCommand As SqlCommand = New SqlCommand(myCommandText, myCon)
            Dim myAdapter As SqlDataAdapter = New SqlDataAdapter()
            myAdapter.SelectCommand = myCommand
            myCon.Open()
            myAdapter.Fill(myDataSet, "SPD")
            myDataSet.Tables("SPD").Columns.Add("SrNo", GetType(Integer))
            Dim i1 As Integer = 1
            For Each dr As DataRow In myDataSet.Tables("SPD").Rows
                dr("SrNo") = i1
                i1 = i1 + 1
            Next
            Dim j As Integer = myDataSet.Tables("SPD").Rows.Count
            If j > 0 Then
                Label3.Visible = True
                Label3.Text = j & " Supplier(s) Founds."
            Else
                Label3.Text = ""
                Label3.Visible = False
            End If

            gvProducts.DataSource = myDataSet.Tables("SPD").DefaultView
            gvProducts.Visible = True
            gvProducts.DataBind()
            'Dim dt As DataTable = ReturnDataTable("SELECT DISTINCT Product_Master.Product_SupplierId, Supplier_Master.Supplier_Name, Supplier_Master.Supplier_TelExtn, Supplier_Master.Supplier_TelNumber FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid INNER JOIN Category_Master ON Product_Master.Product_CategoryId = Category_Master.Category_Kid WHERE Product_Master.Product_CategoryId In ( " & c.ToString.Trim & " ) ")
            For i = 0 To myDataSet.Tables(0).Rows.Count - 1
                Dim lbl1 As Label = CType(gvProducts.Rows(i).FindControl("Label1"), Label)
                If myDataSet.Tables(0).Rows(i).Item(2).ToString <> "" Then
                    lbl1.Text = myDataSet.Tables(0).Rows(i).Item(2).ToString.Trim & "-" & myDataSet.Tables(0).Rows(i).Item(3).ToString.Trim
                Else
                    lbl1.Text = myDataSet.Tables(0).Rows(i).Item(3).ToString.Trim
                End If
            Next
        Catch ex As Exception

        End Try
    End Sub
    Protected Sub gvSupplierDetail_PageIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        'Fill_Grid("SELECT DISTINCT Product_Master.Product_SupplierId, Supplier_Master.Supplier_Name as [Name], Supplier_Master.Supplier_TelExtn as [TelExtn] , Supplier_Master.Supplier_TelNumber as [TelNumber] FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid INNER JOIN Category_Master ON Product_Master.Product_CategoryId = Category_Master.Category_Kid WHERE Product_Master.Product_CategoryId In ( " & c.ToString.Trim & " )", gvProducts)
    End Sub
    Protected Sub gvSupplierDetail_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

    End Sub
    Protected Sub gvSupplierDetail_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)

    End Sub

    Protected Sub gvProducts_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvProducts.PageIndexChanging
        gvProducts.PageIndex = e.NewPageIndex
        gvProducts.DataSource = dt1
        gvProducts.Visible = True
        gvProducts.DataBind()
    End Sub

 
    Protected Sub gvProducts_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvProducts.SelectedIndexChanged

    End Sub

End Class