ModuleMaster/purchasing/mat_comparison_pivot.aspx.vb - main - ivan/ModMas

ModuleMaster/purchasing/mat_comparison_pivot.aspx.vb
667 строк26 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports sharedClasses.dbFunctions

Partial Class Purchasing_mat_comparison_pivot
    Inherits System.Web.UI.Page

    Private PlantColumnStart As Integer = 7

    Private HeaderColumn() As String = {"Price", "Qty", "Amount"}
    Private _borderStyle As String = "2px Solid Navy"

    Private EXcelDownload As Boolean = False

    Private Sub RefreshDDl(Optional exceptList() As String = Nothing)
        If Not IsNothing(exceptList) Then
            If Not exceptList.Contains("ddlModel") Then
                BuildList(ddlModel, "SUBSTRING([model], 3, 6)", "MODEL", "MODEL", , True)
            End If
            If Not exceptList.Contains("ddlMaterialGroup") Then
                BuildList(ddlMaterialGroup, "[GROUP]")
            End If
            If Not exceptList.Contains("ddlParent") Then
                BuildList(ddlParent, "[PARENT]", , , " AND SUBSTRING([model], 3, 6) LIKE '%" & ddlModel.SelectedValue & "%'", True)
            End If
            If Not exceptList.Contains("ddlVendor") Then
                BuildList(ddlVendor, "[VENDOR]", , , " AND SUBSTRING([model], 3, 6) LIKE '%" & ddlModel.SelectedValue & "%'", True)
            End If
            If Not exceptList.Contains("ddlPlant") Then
                BuildList(ddlPlant, "[PLANT]", , , "AND plant <> 'P901'", False)
            End If
        Else
            BuildList(ddlModel, "SUBSTRING([model], 3, 6)", "MODEL", "MODEL", , True)
            BuildList(ddlMaterialGroup, "[GROUP]")
            BuildList(ddlParent, "[PARENT]", , , " AND SUBSTRING([model], 3, 6) LIKE '%" & ddlModel.SelectedValue & "%'", True)
            BuildList(ddlVendor, "[VENDOR]", , , " AND SUBSTRING([model], 3, 6) LIKE '%" & ddlModel.SelectedValue & "%'", True)
            BuildList(ddlPlant, "[PLANT]", , , "AND plant <> 'P901'", False)
        End If

    End Sub

    Protected Sub ibRefresh_Click(sender As Object, e As System.Web.UI.ImageClickEventArgs) Handles ibRefresh.Click
        RefreshDDl()
    End Sub

    Private Sub BuildList(ByRef ddl As DropDownList, fieldName As String, Optional DataTextField As String = "", Optional DataValueField As String = "", Optional condition As String = "", Optional withAll As Boolean = True)
        Dim sqlf As New SqlFunctions
        Dim sql As String
        If DataTextField = "" Then
            sql = "SELECT DISTINCT " & fieldName & " FROM UserModules.[user].DATA_FOR_PIVOT WHERE COST_APPLIED = 'Y' " & condition & " ORDER BY " & fieldName
        Else
            sql = "SELECT DISTINCT " & fieldName & " AS " & DataTextField & " FROM UserModules.[user].DATA_FOR_PIVOT WHERE COST_APPLIED = 'Y' " & condition & " ORDER BY " & fieldName
        End If
        Dim ds As Data.DataTable = sqlf.GetData(sql)
        ddl.DataSource = ds
        ddl.DataTextField = IIf(DataTextField = "", fieldName.Replace("[", "").Replace("]", ""), DataTextField)
        ddl.DataValueField = IIf(DataValueField = "", fieldName.Replace("[", "").Replace("]", ""), DataValueField)
        ddl.AppendDataBoundItems = True
        ddl.Items.Clear()
        If withAll Then
            Dim item As New ListItem("ALL", "%")
            ddl.Items.Add(item)
        End If
        ddl.DataBind()
    End Sub

    Private Sub BuildReport()
        Dim sql As New StringBuilder
        sql.Append("SELECT ")
        'Add model 
        sql.Append(" [Model], ")

        sql.Append(" [Material group],")
        If ShowParent.Checked Then
            sql.Append(" [Parent material],")
        End If
        If ShowMaterial.Checked Then
            sql.Append("[Part number], Description,")
        End If
        If ShowVendor.Checked Then
            sql.Append("[Code], [Name],")
        End If
        sql.Append(PrepareSUMSelectPlantList(cblPlants))
        sql.Append(" FROM ( ")
        sql.Append("SELECT ")

        'Add model
        sql.Append(" [Model], ")

        sql.Append(" [Material group],")
        If ShowParent.Checked Then
            sql.Append("[Parent material], ")
        End If
        sql.Append(" Material as [Part number], Description, ")
        sql.Append(" Vendor as [Code], vendor_name as [Name],")
        sql.Append(PrepareSelectPlantList(cblPlants))
        sql.Append(" FROM (")
        sql.Append("				SELECT Plant+'_Price' as Plant_Price, Plant+'_Qty' as Plant_Qty, ")
        sql.Append("				[group] AS [Material group], ")
        If ShowParent.Checked Then
            sql.Append("				Parent AS [Parent material], ")
        End If

        'Add model
        sql.Append(" SUBSTRING (model, 3, 6) as MODEL, ")

        sql.Append("				MATERIAL_CODE AS Material, ")
        sql.Append("				Description, ")
        sql.Append("				vendor, vendor_name, ")
        sql.Append("				Cast(Qty as decimal(18,5)) as Qty, ")
        sql.Append("				Cast (UNIT_PRICE as DEcimal(18,5)) AS [Unit price] ")
        sql.Append("				FROM UserModules.[user].DATA_FOR_PIVOT ")
        sql.Append("				WHERE	COST_APPLIED = 'Y' and [group] is not null ")
        sql.Append("				AND SUBSTRING (model, 3, 6) like  '%" & ddlModel.SelectedValue & "%' and [group] like '%" & ddlMaterialGroup.SelectedValue & "%' and parent like '%" & ddlParent.SelectedValue & "%' ")
        sql.Append("                AND Plant IN (" & PreparePivotPlantList(cblPlants, "").Replace("[", "'").Replace("]", "'") & ")  AND [VENDOR] LIKE '%" & ddlVendor.SelectedValue & "%'")
        sql.Append(") p ")
        sql.Append("PIVOT (Sum([Unit price]) FOR plant_price IN (" & PreparePivotPlantList(cblPlants, "_Price") & ")) AS pvt1 ")
        sql.Append("PIVOT (Sum([Qty]) FOR plant_Qty in (" & PreparePivotPlantList(cblPlants, "_Qty") & ") )as pvt2 ")
        sql.Append(" ) TTT GROUP BY ")

        'Add model
        sql.Append(" [Model], ")

        sql.Append("[Material group] ")
        If ShowMaterial.Checked Then
            sql.Append(",[Part number], Description")
        End If
        If ShowVendor.Checked Then
            sql.Append(",[Code], [Name]")
        End If
        If ShowParent.Checked Then
            sql.Append(",[Parent material]")
        End If
        sql.Append(" ORDER BY [Model] , [Material Group]")
        If ShowParent.Checked Then
            sql.Append(",[Parent material]")
        End If
        Dim ds As Data.DataTable
        Dim sqlf As New SqlFunctions
        ds = sqlf.GetData(sql.ToString)

        GridView1.DataSource = ds
        Try
            GridView1.DataBind()
        Catch ex As Exception
            GridView1.DataSource = Nothing
        End Try

    End Sub

    Private Function PrepareSelectPlantList(cbl As CheckBoxList) As String
        Dim result As New StringBuilder
        For Each item As ListItem In cbl.Items
            If item.Selected Then
                If result.Length > 0 Then
                    result.Append(",")
                End If
                result.Append(" [PLANT_Price] as 'PLANT_Unit Price($)', [PLANT_Qty], [PLANT_Price] * [PLANT_Qty] as 'PLANT_Amount($)' ".Replace("PLANT", item.Value))
            End If
        Next
        Return result.ToString
    End Function

    Private Function PrepareSUMSelectPlantList(cbl As CheckBoxList) As String
        Dim result As New StringBuilder
        For Each item As ListItem In cbl.Items
            If item.Selected Then
                If result.Length > 0 Then
                    result.Append(",")
                End If
                result.Append(" SUM([PLANT_Unit Price($)]) as 'PLANT_Unit Price($)', SUM([PLANT_Qty]) AS 'PLANT_Qty', SUM([PLANT_Amount($)]) as 'PLANT_Amount($)' ".Replace("PLANT", item.Value))
            End If
        Next
        Return result.ToString
    End Function

    Private Function PreparePivotPlantList(cbl As CheckBoxList, suffix As String) As String
        Dim result As New StringBuilder
        For Each item As ListItem In cbl.Items
            If item.Selected Then
                If result.Length > 0 Then
                    result.Append(",")
                End If
                result.Append(" [" & item.Value & suffix & "] ")
            End If
        Next
        Return result.ToString
    End Function

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        PlantColumnStart = 7

        If Not ShowVendor.Checked Then
            PlantColumnStart -= 2
        End If
        If Not ShowMaterial.Checked Then
            PlantColumnStart -= 2
        End If
        If Not ShowParent.Checked Then
            PlantColumnStart -= 1
        End If
        If Not IsPostBack Then
            RefreshDDl()
            cblPlants.DataBind()
            For Each item As ListItem In cblPlants.Items
                item.Selected = True
            Next
        End If
        BuildReport()
    End Sub

    Private Function GetPlantNameByNumber(number As Integer) As String
        Dim cnt As Integer = 0
        For Each item As ListItem In cblPlants.Items
            If item.Selected Then
                If cnt = number Then
                    Return item.Value
                Else
                    cnt += 1
                End If
            End If
        Next
        Return ""
    End Function

    Protected Sub GridView1_RowDataBound(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        With e.Row
            If .RowType = DataControlRowType.DataRow Then
                Dim count As Integer = .Cells.Count - 1
                .Cells(count).Text = .Cells(count).Text.TrimEnd("0").TrimEnd(",").TrimEnd(".")
                .Cells(count - 1).Text = .Cells(count - 1).Text.TrimEnd("0").TrimEnd(",").TrimEnd(".")
                .Cells(count - 2).Text = .Cells(count - 2).Text.TrimEnd("0").TrimEnd(",").TrimEnd(".")

                If IsNumeric(.Cells(count).Text) Then
                    If .Cells(count).Text <> 0 Then
                        .Cells(count).BackColor = Drawing.Color.LightCoral
                    End If
                End If

                If IsNumeric(.Cells(count - 1).Text) Then
                    .Cells(count - 1).Text = Math.Round(Convert.ToDecimal(.Cells(count - 1).Text), 5).ToString
                ElseIf IsNumeric(.Cells(count - 1).Text.Replace(".", ",")) Then
                    .Cells(count - 1).Text = Math.Round(Convert.ToDecimal(.Cells(count - 1).Text.Replace(".", ",")), 5).ToString
                End If

                If IsNumeric(.Cells(count - 2).Text) Then
                    .Cells(count - 2).Text = Math.Round(Convert.ToDecimal(.Cells(count - 2).Text), 5).ToString
                ElseIf IsNumeric(.Cells(count - 2).Text.Replace(".", ",")) Then
                    .Cells(count - 2).Text = Math.Round(Convert.ToDecimal(.Cells(count - 2).Text.Replace(".", ",")), 5).ToString
                End If
                Dim cnt As Integer = 0
                Dim chk As Integer = -1
                For Each cell As TableCell In e.Row.Cells
                    If cnt >= PlantColumnStart Then
                        cell.ToolTip = GetPlantNameByNumber((cnt - PlantColumnStart) \ 3) & ":" & HeaderColumn((cnt - PlantColumnStart) Mod 3)
                        If chk <> (cnt - PlantColumnStart) \ 3 Then
                            chk = (cnt - PlantColumnStart) \ 3
                            cell.Style.Add("border-left", _borderStyle)
                        End If
                    End If
                    cnt += 1
                Next

            End If
        End With
    End Sub

    Protected Sub ibExcel_Click(sender As Object, e As System.Web.UI.ImageClickEventArgs) Handles ibExcel.Click
        'ExcelDownloadSub(GridView1, "comparison_P901_" & ddlPlant.SelectedValue & "_" & DateTime.Now.ToString("yyyyMMddHHmmss"))
        EXcelDownload = True
    End Sub

    Sub ExcelDownloadSub(ByVal gv As GridView, ByVal filename As String)
        Response.Clear()

        Response.Buffer = True

        Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
        Response.ContentType = "application/vnd.xls"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8")
        EnableViewState = False

        Response.Charset = "utf-8"

        Dim myCItrad As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("EN-US", True)

        gv.AllowSorting = False
        gv.AllowPaging = False


        Dim sw As IO.StringWriter = New IO.StringWriter(myCItrad)
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

        gv.RenderControl(hw)
        Response.Write(sw.ToString)

        Response.End()
    End Sub

    Sub ExcelDownloadSub(ByVal tbl As Table, ByVal filename As String)
        Response.Clear()

        Response.Buffer = True

        Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
        Response.ContentType = "application/vnd.xls"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8")
        EnableViewState = False

        Response.Charset = "utf-8"

        Dim myCItrad As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("EN-US", True)

        Dim sw As IO.StringWriter = New IO.StringWriter(myCItrad)
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

        tbl.RenderControl(hw)
        Response.Write(sw.ToString)

        Response.End()
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Public Sub CollectHeaderColumn(ByRef gv As Table, colNumber As Integer, Optional referenceColNumber As Integer = -1, Optional skipRowsCount As Integer = 0)
        Dim curCell As New TableCell With {.Text = "."}
        Dim rowSpan As Integer = 1

        Dim ColNumberToCheckText As Integer
        If referenceColNumber = -1 Then
            ColNumberToCheckText = colNumber
        Else
            ColNumberToCheckText = referenceColNumber
        End If

        Dim curText As String = "."

        'For Each dr As TableRow In CType(gv.Controls(0), Table).Rows
        Dim cnt As Integer = 1
        For Each dr As TableRow In gv.Rows
            If cnt > skipRowsCount Then
                If dr.Cells(ColNumberToCheckText).Text <> curText Or curText = "&nbsp;" Then 'curCell.Text Then
                    If rowSpan > 1 Then
                        curCell.RowSpan = rowSpan
                    End If
                    curCell = dr.Cells(colNumber)
                    curText = dr.Cells(ColNumberToCheckText).Text
                    rowSpan = 1
                Else
                    dr.Cells(colNumber).Visible = False
                    rowSpan += 1
                End If
            End If
            cnt += 1
        Next

        If rowSpan > 1 Then
            curCell.RowSpan = rowSpan
        End If
    End Sub

    Public Sub CountSubTotal(ByRef gv As GridView, colNumbers As Collection, referenceColumns As Collection)
        Dim rowsToAdd As New Hashtable

        For Each referenceColNumber As Integer In referenceColumns
            Dim rowsForThisColumn As New Hashtable

            Dim inc As Integer = 0
            Dim subTotals As New Hashtable
            For Each idx As Integer In colNumbers
                subTotals.Add(idx, 0)
            Next

            Dim curText As String = "."
            Dim first As Boolean = True
            Dim preRow As GridViewRow = Nothing

            For Each dr As GridViewRow In gv.Rows
                If dr.RowType = DataControlRowType.DataRow Then
                    If dr.Cells(referenceColNumber).Text <> curText Then
                        If Not first Then
                            Dim newRow As New TableRow

                            Dim i As Integer = 0
                            For Each cell As TableCell In preRow.Cells()
                                Dim nc As New TableCell
                                If subTotals.ContainsKey(i) Then
                                    nc.Text = subTotals(i)
                                ElseIf i = referenceColNumber Then
                                    nc.Text = "Sub total by: " & preRow.Cells(i).Text
                                Else
                                    If i < referenceColNumber Then
                                        nc.Text = cell.Text
                                    Else
                                        nc.Text = "&nbsp;" 'cell.Text
                                    End If
                                End If

                                nc.BackColor = Drawing.Color.LightGray
                                nc.ForeColor = Drawing.Color.Navy
                                nc.Font.Bold = True
                                nc.Style.Add("border-left", cell.Style("border-left"))

                                newRow.Cells.Add(nc)
                                i += 1
                            Next
                            '-----------------------------------------------------
                            rowsForThisColumn.Add(dr.RowIndex + inc, newRow)
                            '-----------------------------------------------------
                        End If

                        curText = dr.Cells(referenceColNumber).Text
                        For Each idx As Integer In colNumbers
                            If IsNumeric(dr.Cells(idx).Text) Then
                                subTotals(idx) = Convert.ToDecimal(dr.Cells(idx).Text)
                            Else
                                subTotals(idx) = 0
                            End If
                        Next
                        first = False
                    Else
                        For Each idx As Integer In colNumbers
                            If IsNumeric(dr.Cells(idx).Text) Then
                                subTotals(idx) += dr.Cells(idx).Text
                            End If
                        Next
                    End If

                    preRow = dr
                End If
            Next

            'Add last row-----------------------------------------
            Dim newnewRow As New TableRow
            Dim ii As Integer = 0
            For Each cell As TableCell In preRow.Cells()
                Dim nc As New TableCell
                If subTotals.ContainsKey(ii) Then
                    nc.Text = subTotals(ii)
                ElseIf ii = referenceColNumber Then
                    nc.Text = "Sub total by: " & preRow.Cells(ii).Text
                Else
                    If ii < referenceColNumber Then
                        nc.Text = cell.Text
                    Else
                        nc.Text = "&nbsp;" 'cell.Text
                    End If
                End If
                nc.BackColor = Drawing.Color.LightGray
                nc.ForeColor = Drawing.Color.Navy
                nc.Font.Bold = True
                nc.Style.Add("border-left", cell.Style("border-left"))

                newnewRow.Cells.Add(nc)
                ii += 1
            Next
            '---------------------

            '-----------------------------------------------------
            rowsForThisColumn.Add(preRow.RowIndex + 1 + inc, newnewRow)
            '-----------------------------------------------------

            rowsToAdd.Add(referenceColNumber, rowsForThisColumn)
        Next

        PrepareTable(gv, gvTheGrid, rowsToAdd)

        'Create GRand total row
        Dim grandTotalRow As New TableRow
        Dim grandTotals As New Hashtable
        For Each idx As Integer In colNumbers
            grandTotals.Add(idx, 0)
        Next

        For Each dr As GridViewRow In gv.Rows
            If dr.RowType = DataControlRowType.DataRow Then
                'Count grand total
                For Each idx As Integer In colNumbers
                    If IsNumeric(dr.Cells(idx).Text) Then
                        grandTotals(idx) += dr.Cells(idx).Text
                    End If
                Next
                '-----------------
            End If
        Next

        For j As Integer = 0 To gv.Rows(0).Cells.Count - 1
            Dim nc As New TableCell With {.BackColor = Drawing.Color.Navy, .ForeColor = Drawing.Color.White}
            nc.Style.Add("font-weight", " bold")
            If colNumbers.Contains(j) Then
                nc.Text = grandTotals(j)
            Else
                nc.Text = "&nbsp;"
            End If
            grandTotalRow.Cells.Add(nc)
        Next
        grandTotalRow.Cells(0).Text = "GRAND TOTAL: "

        gvTheGrid.Rows.RemoveAt(gvTheGrid.Rows.Count - 1)
        gvTheGrid.Rows.Add(grandTotalRow)
    End Sub

    Private Sub PrepareTable(gv As GridView, ByRef tbl As Table, rowsToAdd As Hashtable)
        Dim cnt As Integer = -1
        For Each r As TableRow In CType(gv.Controls(0), Table).Rows
            'For Each r As GridViewRow In gv.Rows
            tbl.Rows.Add(CopyRow(r))
            For i As Integer = rowsToAdd.Count - 1 To 0 Step -1
                If rowsToAdd(i).ContainsKey(cnt) Then
                    tbl.Rows.Add(rowsToAdd(i)(cnt))
                    rowsToAdd(i).Remove(cnt)
                End If
            Next
            cnt += 1
        Next
    End Sub

    'Private Function CopyRow(r As TableRow) As TableRow
    '    Dim nr As New TableRow
    '    For Each c In r.Cells
    '        Dim nc As TableCell
    '        If c.GetType.ToString.Contains("header") Then
    '            nc = New TableHeaderCell With {.Text = c.Text, .RowSpan = c.RowSpan, .ColumnSpan = c.ColumnSpan}
    '        Else
    '            nc = New TableCell With {.Text = c.Text, .RowSpan = c.RowSpan, .ColumnSpan = c.ColumnSpan}
    '        End If
    '        nr.Cells.Add(nc)
    '    Next
    '    Return nr
    'End Function

    Private Function CopyRow(r As GridViewRow) As TableRow
        Dim tr As TableRow
        If r.RowType = DataControlRowType.Header Then
            tr = New TableHeaderRow
        ElseIf r.RowType = DataControlRowType.Footer Then
            tr = New TableFooterRow
        Else
            tr = New TableRow
        End If

        For Each c As TableCell In r.Cells
            Dim nc As TableCell
            If r.RowType = DataControlRowType.Header Then
                nc = New TableHeaderCell With {.Text = c.Text, .RowSpan = c.RowSpan, .ColumnSpan = c.ColumnSpan, .ToolTip = c.ToolTip}
                nc.Style.Add("border-left", c.Style("border-left"))
            Else
                nc = New TableCell With {.Text = c.Text, .RowSpan = c.RowSpan, .ColumnSpan = c.ColumnSpan, .ToolTip = c.ToolTip}
                nc.Style.Add("border-left", c.Style("border-left"))
            End If
            tr.Cells.Add(nc)
        Next
        Return tr
    End Function

    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete
        If GridView1.Rows.Count > 0 Then

            Dim Plants As New Collection
            For Each c As TableCell In GridView1.HeaderRow.Cells
                If c.Text.Contains("_") Then
                    If Not Plants.Contains(c.Text.Split("_")(0)) Then
                        Plants.Add(c.Text.Split("_")(0), c.Text.Split("_")(0))
                    End If
                    c.Text = c.Text.Split("_")(1)
                End If
            Next

            Dim b As New Collection

            If ShowMaterial.Checked Then
                If ShowVendor.Checked Then
                    b.Add({"Material", (PlantColumnStart - 4).ToString, "2"})
                Else
                    b.Add({"Material", (PlantColumnStart - 2).ToString, "2"})
                End If
            End If

            If ShowVendor.Checked Then
                b.Add({"Vendor", (PlantColumnStart - 2).ToString, "2"})
            End If


            Dim dt As Data.DataTable = Nothing
            If ddlModel.SelectedValue <> "%" Then
                Try
                    dt = (New SqlFunctions).GetData("select DISTINCT plant, [model] from UserModules.[user].DATA_FOR_PIVOT where SUBSTRING([model], 3, 6) LIKE '%" & ddlModel.SelectedValue & "%'")
                Catch ex As Exception

                End Try
            End If


            Dim startPos As Integer = PlantColumnStart
            For Each plant As String In Plants


                If Not IsNothing(dt) Then
                    Dim rows() As Data.DataRow = dt.Select("plant='" & plant & "'")
                    If rows.Count > 0 Then
                        plant &= " / " & rows(0)(1)
                    End If
                End If

                b.Add({plant, startPos.ToString, "3"})
                startPos += 3
            Next

            CommonFunctions.ApplyGridViewHeader(ID.ToString, GridView1, b)

            Dim columns As New Collection
            For i = PlantColumnStart To PlantColumnStart + Plants.Count * 3 - 1
                columns.Add(i, i)
            Next

            Dim referenceColumns As New Collection
            If (ShowParent.Checked Or ShowVendor.Checked Or ShowMaterial.Checked) Then
                referenceColumns.Add(0)
            End If
            If ShowParent.Checked Then
                referenceColumns.Add(1)
            End If
            CountSubTotal(GridView1, columns, referenceColumns)
            GridView1.Visible = False
            If CollectRowHeaders.Checked Then
                CollectHeaderColumn(gvTheGrid, 0, , PlantColumnStart - 4)
                If ShowParent.Checked Then
                    CollectHeaderColumn(gvTheGrid, 1, , PlantColumnStart - 4)
                End If
                If ShowMaterial.Checked Then
                    CollectHeaderColumn(gvTheGrid, 2, , PlantColumnStart - 4)
                    CollectHeaderColumn(gvTheGrid, 3, , PlantColumnStart - 4)
                End If
                If ShowVendor.Checked Then
                    CollectHeaderColumn(gvTheGrid, PlantColumnStart - 2, , 2)
                    CollectHeaderColumn(gvTheGrid, PlantColumnStart - 1, , 2)
                End If
            End If
            For Each c As TableCell In gvTheGrid.Rows(0).Cells
                c.ForeColor = Drawing.Color.White
                c.BackColor = Drawing.Color.Navy
                c.Font.Bold = True
                c.HorizontalAlign = HorizontalAlign.Center
            Next

            For Each c As TableCell In gvTheGrid.Rows(1).Cells
                c.ForeColor = Drawing.Color.White
                c.BackColor = Drawing.Color.Navy
                c.Font.Bold = True
                c.HorizontalAlign = HorizontalAlign.Center
            Next

            If EXcelDownload Then
                ExcelDownloadSub(gvTheGrid, "Material_price_compare_" & DateTime.Now.ToString("yyyyMMddHHmmss"))
            End If
        End If
    End Sub

    Protected Sub cblPlants_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles cblPlants.SelectedIndexChanged
        GridView1.DataBind()
    End Sub

    Protected Sub ddlModel_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ddlModel.SelectedIndexChanged
        RefreshDDl({"ddlModel"})
    End Sub
End Class