Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Imports System.String
Imports ModCommon
Partial Class UserControl_ShowSupplierDetails
    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
   




    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            'Put user code to initialize the page here
            Dim parentid As String
            lblMSG.Visible = False
            If Not Me.Page.IsPostBack Then

                If Session("Bool") <> Nothing Then
                    btnSupplierName.Text = "Update Category"
                    gvSupplierDetail.Visible = False
                    Label3.Visible = False
                    btnSupplierName.Attributes.Add("onclick", "return confirm('Are you sure you want to update.?');")
                End If

                'pid = Session("uid")
                ' dateofedate()
                'monthofedate()
                ' yearrofedate() 
                'loadcountry()
                '------getting the value of subcatid from the getparents function that declared in this form--------
                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
        End Try
    End Sub



    Private Sub cboCategory_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCategory.SelectedIndexChanged
        Try
            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)

              
                gvSupplierDetail.Visible = False

            End If
        Catch
        End Try

    End Sub
    Private Sub cbosubCategory1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbosubCategory1.SelectedIndexChanged
        Try
            Session("lastsubcategory") = cbosubCategory1.SelectedItem.Value
            CatID2 = cbosubCategory1.SelectedItem.Value
            CatID1 = ""
            CatID3 = ""
            getparents(cbosubCategory1.SelectedItem.Value)
            LoadSubCategory2(cbosubCategory1.SelectedItem.Value)
          
            cbosubCategory1.Visible = True
           
            gvSupplierDetail.Visible = False
        Catch
        End Try
    End Sub
    Private Sub cbosubCategory2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbosubCategory2.SelectedIndexChanged
        Try
            Session("lastsubcategory") = cbosubCategory2.SelectedItem.Value
            CatID3 = cbosubCategory2.SelectedItem.Value
            CatID1 = ""
            CatID2 = ""
            getparents(cbosubCategory2.SelectedItem.Value)
            LoadSubCategory3(cbosubCategory2.SelectedItem.Value)
          
            gvSupplierDetail.Visible = False
        Catch
        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 & "' and Category_Isdeleted='0' 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
        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 & "'  and Category_Isdeleted='0' 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
        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 & "'  and Category_Isdeleted='0' 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
        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 & "'  and Category_Isdeleted='0' 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
        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 & "'  and Category_Isdeleted='0'"
            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

                If Session("Update") = Nothing Then
                    str = "<a href=./categorywisesupplier.aspx?subcatid=" & dr(0) & ">" & dr(1) & "</a>" & " > " & str
                Else
                    str = "<a href=./product-category-update.aspx?subcatid=" & dr(0) & ">" & dr(1) & "</a>" & " > " & str
                End If
                'str = "<a href=../ProductDetails/prodinfo.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
        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
        End Try
    End Sub

    Public Function NewPrimaryKey1(ByVal aspxpage As System.Web.UI.UserControl) As String
        NewPrimaryKey1 = ""
        Try
            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()

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

   
    Protected Sub btnSupplierName_Click(ByVal sender As Object, ByVal e As System.EventArgs)

        Try
            gvSupplierDetail.Visible = True

            If cboCategory.SelectedItem.Text = "Select Category" Then
                showmessage("Please Select Category.", lblMSG)
                'Label2.Visible = True
                'Label2.Text = "Please Select Category.."
                'Label2.Text = ""
                gvSupplierDetail.DataSource = Nothing
                gvSupplierDetail.DataBind()
                Exit Sub
            Else
                Label2.Visible = False
            End If


            If CatID1.ToString.Trim <> "" Then
                c = ""
                c = "'" & CatID1.ToString.Trim & "'"
            ElseIf CatID2.ToString.Trim <> "" Then
                c = ""
                c = "'" & CatID2.ToString.Trim & "'"
            ElseIf CatID3.ToString.Trim <> "" Then
                c = ""
                c = "'" & CatID3.ToString.Trim & "'"
            End If

            If Session("Bool") = Nothing Then

                Dim CatBool As Boolean = True


DataTable:
                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 & " ) and Product_Isdeleted='0' ")

                gvSupplierDetail.DataSource = Nothing
                gvSupplierDetail.DataBind()


                If dt.Rows.Count = 0 Then
                    dt = ReturnDataTable("SELECT Category_Kid,Category_Name FROM Category_Master WHERE Category_ParentId In ( " & c.ToString.Trim & " )  and Category_Isdeleted='0'")
                    If dt.Rows.Count > 0 Then
                        For i = 0 To dt.Rows.Count - 1
                            If CatBool = True Then
                                c = ""
                                c = "'" & dt.Rows(i).Item(0).ToString.Trim & "'"
                                CatBool = False
                            Else
                                c &= ","
                                c &= "'" & dt.Rows(i).Item(0).ToString.Trim & "'"
                            End If
                        Next
                        Threading.Thread.Sleep(2000)
                        dt = Nothing
                        GoTo DataTable
                    End If
                Else


                    BindGrid()

                End If

            Else
                If Session("Update") = Nothing Then
                    showmessage("Please Select Product Name.", lblMSG)
                Else
                    ExecuteQuery("Update Product_Master Set Product_CategoryId =" & c.ToString() & ",Product_ClientIP='" & Request.UserHostAddress & "' where Product_KId = '" & Session("Update") & "'  and Product_Isdeleted='0'")
                    showmessage("Record is update successfully.", lblMSG)
                    Session("Update") = Nothing
                End If




                Try

                Catch ex As Exception

                End Try

            End If
        Catch
        End Try
            
    End Sub

    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 & " ) and Product_Isdeleted='0'"
            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))

            'dt.PrimaryKey = New DataColumn() {Id}
            'dt.Columns("SrNo").AutoIncrement = True
            'dt.Columns("SrNo").AutoIncrementSeed = 1
            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

            gvSupplierDetail.DataSource = myDataSet.Tables("SPD").DefaultView
            gvSupplierDetail.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(gvSupplierDetail.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

            'For i = 0 To myDataSet.Tables("SPD").Rows.Count - 1
            '    'Dim lbl1 As Label = CType(gvSupplierDetail.Rows(i).FindControl("Label1"), Label)
            '    CType(gvSupplierDetail.Rows(i).FindControl("Label1"), Label).Text = myDataSet.Tables("SPD").Rows(i).Item(2).ToString.Trim & "-" & myDataSet.Tables("SPD").Rows(i).Item(3).ToString.Trim
            'Next

        Catch
        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 & " )", gvSupplierDetail)

    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)
        Try
            gvSupplierDetail.PageIndex = e.NewPageIndex
            BindGrid()
        Catch
        End Try
    End Sub
End Class