ModuleMaster/pc/truck_loading_plan.aspx.vb - main - ivan/ModMas

ModuleMaster/pc/truck_loading_plan.aspx.vb
429 строк18 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.IO

Partial Class r
    Inherits System.Web.UI.Page

    Private sqlf As New sharedClasses.dbFunctions.SqlFunctions
    Public WeekNumber As Integer
    Public YearNumber As Integer
    Public start_of_week As Date
    Public end_of_week As String
    Private TruckLoadingPlan As DataTable = Nothing



    Private Function GetWeekStartEndDate(weekNumber As Integer, year As Integer, Flag As Char) As Date 'Flag='f' - first day, else - last day
        Dim startDate As New DateTime(year, 1, 1)
        Dim weekDate As DateTime = DateAdd(DateInterval.WeekOfYear, weekNumber - 1, startDate)
        If Flag = "f" Then
            Return DateAdd(DateInterval.Day, (-weekDate.DayOfWeek) + 1, weekDate)
        Else
            Return DateAdd(DateInterval.Day, (-weekDate.DayOfWeek) + 7, weekDate)
        End If

    End Function



    Private Sub report_ItemDataBound(sender As Object, e As ListViewItemEventArgs) Handles report.ItemDataBound
        Dim i As ListViewItem = e.Item
        Dim step_counter As Integer = 0
        For Each collectionItem As Object In i.Controls
            ' Perform desired processing on each item.
            If collectionItem.id = "tooltip_d1" Or collectionItem.id = "tooltip_d2" Or collectionItem.id = "tooltip_d3" Or collectionItem.id = "tooltip_d4" Or collectionItem.id = "tooltip_d5" Or collectionItem.id = "tooltip_d6" Or collectionItem.id = "tooltip_d7" Then

                If Not CType(collectionItem.controls(1), System.Web.UI.WebControls.Label).Text.Equals("") And CType(i.Controls(3).Controls(1), System.Web.UI.WebControls.Label).Text.Equals("Loading_Efficiency") Then
                    CType(collectionItem.controls(1), System.Web.UI.WebControls.Label).Text += "%"
                End If

                If Not IsNothing(TruckLoadingPlan) Then
                        Try
                            TruckLoadingPlan.DefaultView.RowFilter = "[destination]='" & CType(i.Controls(1).Controls(1), System.Web.UI.WebControls.Label).Text & "' and [date]='" & DateAdd(DateInterval.Day, step_counter, start_of_week).ToString("yyyy-MM-dd") & "'"
                            Dim toolTip As String = PrepareTooltip(TruckLoadingPlan)
                            If toolTip = "" Then
                                CType(collectionItem.controls(0), LiteralControl).Text = ""
                            Else
                                CType(collectionItem.controls(0), LiteralControl).Text = CType(collectionItem.controls(0), LiteralControl).Text.Replace("@TEMPLATE", toolTip)
                            End If
                        Catch ex As Exception

                        End Try
                    End If
                    step_counter += 1
                End If
        Next collectionItem

    End Sub






    Public Shared Sub ApplyGridViewHeader(ParentId As String, ByRef gv As GridView, ByRef a As Collection)
        'процедура написана таким образом, что в нее всегда должны передаваться индексы с учетом только фактически отображаемых колонок
        'т.е. при вычислении индекса объединяемой колонки (передаваемой в качестве параметра в процедуру) считается,
        'что колонок со свойством Visible = false в таблице как бы нет.
        'индексы (передаваемые в качестве параметров в процедуру) указываются по исходному порядку необъединенных колонок

        Dim AllowSorting As Boolean = gv.AllowSorting
        If gv.Rows.Count > 0 Then
            Dim row = New GridViewRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal)
            Dim r = gv.HeaderRow
            Dim cell As TableHeaderCell

            Dim i, j As Integer

            Dim invisibleColsCount As Integer = 0
            Dim cnt As Integer = 0

            'i = 1

            For Each rcell As TableCell In r.Cells
                cell = New TableHeaderCell
                If AllowSorting Then
                    Dim lb As New HyperLink
                    Try
                        lb.Style("color") = "White"
                        lb.Text = CType(rcell.Controls(0), LinkButton).Text
                        lb.NavigateUrl = "javascript:__doPostBack('" & ParentId & "$" & gv.ID & "','Sort$" & CType(rcell.Controls(0), LinkButton).CommandArgument & "')"
                        cell.Controls.Add(lb)
                    Catch ex As Exception
                        cell.Text = rcell.Text
                    End Try
                Else
                    cell.Text = rcell.Text
                End If
                cell.Style("text-align") = "center"
                cell.Visible = rcell.Visible

                'If cnt > a(i)(1) And cnt < a(i)(1) + a(i)(2) Then
                ' If Not rcell.Visible Then
                ' invisibleColsCount += 1
                ' End If
                'End If

                cnt += 1

                row.Cells.Add(cell)
                'End If
            Next

            'для нужных ячеек в верхней строке, прописываем тексты объединяющих ячеек и их ColumnSpan
            For i = 1 To a.Count
                row.Cells(a(i)(1)).Text = a(i)(0)
                row.Cells(a(i)(1)).ColumnSpan = a(i)(2) - invisibleColsCount
            Next

            'идем по верхней строке у оставшихся ячеек (не задействованных) в предыдущем цикле ставим им RowSpan = 2
            'одновременно ячееки с таким же индексом в нижней строке помечаем на удаление
            For i = 0 To row.Cells.Count - 1
                If row.Cells(i).Visible Then
                    If row.Cells(i).ColumnSpan < 2 Then
                        row.Cells(i).RowSpan = 2
                        r.Cells(i).ColumnSpan = 100
                    Else
                        'пропускаем ячейки на которые распространяется ColumnSpan
                        i = i + row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                    End If
                End If
            Next

            'удаляем помеченные ячейки в нижней строке
            i = 0
            While i < r.Cells.Count
                If r.Cells(i).ColumnSpan = 100 Then
                    r.Cells.RemoveAt(i)
                Else : i = i + 1
                End If
            End While

            'идем по верхней строке удаляем соответствующее ColumnSpan количество ячеек после ячеек имеющих ColumnSpan > 1 
            i = 0
            While i < row.Cells.Count
                If row.Cells(i).ColumnSpan > 1 Then
                    For j = 1 To row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                        row.Cells.RemoveAt(i + 1)
                    Next
                End If
                i = i + 1
            End While

            CType(gv.Controls(0), Table).Rows.AddAt(0, row)
        End If
    End Sub

    Public Sub CollectHeaderColumn(ByRef gv As GridView, colNumber As Integer, Optional referenceColNumber As Integer = -1)
        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 GridViewRow In gv.Rows
            If dr.Cells(ColNumberToCheckText).Text <> curText 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
        Next
        If rowSpan > 1 Then
            curCell.RowSpan = rowSpan
        End If
    End Sub


    Protected Sub gv_prerender(sender As Object, e As EventArgs)


    End Sub


    Private Function PrepareTooltip(dt As DataTable,
 Optional headerGRoups As Collection = Nothing,
 Optional removeLastColumn As Boolean = False,
 Optional collectHeaders As Collection = Nothing,
 Optional showfooter As Boolean = False) As String
        Dim result As String = ""

        Dim gv As New GridView


        AddHandler gv.PreRender, AddressOf gv_prerender


        gv.ShowFooter = showfooter

        gv.DataSource = dt
        gv.DataBind()

        If Not IsNothing(headerGRoups) Then
            ApplyGridViewHeader(ID.ToString, gv, headerGRoups)
        End If

        If gv.Rows.Count > 0 Then
            If Not IsNothing(collectHeaders) Then
                For Each c In collectHeaders
                    CollectHeaderColumn(gv, c)
                Next
            Else
                CollectHeaderColumn(gv, 0)
                CollectHeaderColumn(gv, 1)
            End If

            Dim myCItrad As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("EN-US", True)
            Dim sw As StringWriter = New StringWriter(myCItrad)
            Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
            gv.CssClass = "detailTable"



            'gv.FooterStyle.BackColor = Drawing.Color.SteelBlue
            'gv.FooterStyle.ForeColor = Drawing.Color.White
            'gv.FooterRow.Cells(2).Text = TTLTruck
            'gv.FooterRow.Cells(0).ColumnSpan = 2
            'gv.FooterRow.Cells(0).Text = "Total"
            'gv.FooterRow.Cells(1).Visible = False

            If showfooter Then
                Dim GridRow As GridViewRow = New GridViewRow(gv.Rows.Count, 0, DataControlRowType.Footer, DataControlRowState.Normal)
                Dim TabCell As TableCell = New TableCell
                'TabCell.Text = "Total in trucks"
                'TabCell.ColumnSpan = 2
                'GridRow.Cells.Add(TabCell)
                'TabCell = New TableCell
                'TabCell.Text = TTL_In_Track
                'GridRow.Cells.Add(TabCell)
                'gv.Controls(0).Controls.Add(GridRow)
            End If



            gv.HeaderRow.Cells(0).Visible = False
            gv.HeaderRow.Cells(1).Visible = False
            For Each row In gv.Rows
                row.Cells(0).Visible = False
                row.Cells(1).Visible = False
            Next

            gv.RenderControl(hw)
            result = sw.ToString.Replace("""", "'")

        End If
        gv.Dispose()
        Return result
    End Function


    Private Function GetLoadingInfo() As DataTable
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        Dim sql As New StringBuilder
        sql.Append("select [destination], [date], [model], [qty]")
        sql.Append("from [UserModules].[user].[Truck_loading_weekly_report] ")
        Return sqlf.GetData(sql.ToString)
    End Function


    Private Sub r_Load(sender As Object, e As EventArgs) Handles Me.Load


        If week_num.Value = "" Then
            WeekNumber = DatePart(DateInterval.WeekOfYear, Date.Today, Microsoft.VisualBasic.FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays)
            YearNumber = DatePart(DateInterval.Year, Date.Today, Microsoft.VisualBasic.FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays)
        Else
            WeekNumber = Right(week_num.Value, 2)
            YearNumber = Left(week_num.Value, 4)
        End If

        start_of_week = GetWeekStartEndDate(WeekNumber, YearNumber, "f")


        Dim sql As New StringBuilder
        sql.AppendLine("declare @WeekNumber as integer ")
        sql.AppendLine("declare @YearNumber as char(4) ")
        sql.AppendLine("declare @start_of_week as date ")
        sql.AppendLine("declare @end_of_week as date ")
        sql.AppendLine("declare @date_range as nvarchar(max) ")
        sql.AppendLine("declare @sql as nvarchar(max) ")
        sql.AppendLine("declare @col_names as nvarchar(max) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("set @WeekNumber = " + WeekNumber.ToString + " ")
        sql.AppendLine("set @YearNumber = '" + YearNumber.ToString + "-01-01' ")
        sql.AppendLine("set @start_of_week = dateadd(week, datediff(week, 0, getdate()), 0) ")
        sql.AppendLine("set @start_of_week = dateadd(week, datediff(week, 0, dateadd(week, @WeekNumber, cast(@YearNumber as date)))-1, 0) ")
        sql.AppendLine("set @end_of_week = dateadd(day, 6, @start_of_week); ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("WITH sample AS ( ")
        sql.AppendLine(" SELECT CAST(@start_of_week AS DATE) AS dt ")
        sql.AppendLine(" UNION ALL ")
        sql.AppendLine(" SELECT DATEADD(dd, 1, dt) ")
        sql.AppendLine(" FROM sample s ")
        sql.AppendLine(" WHERE DATEADD(dd, 1, dt) <= CAST(@end_of_week AS DATE)) ")
        sql.AppendLine("select @date_range= (SELECT QUOTENAME(dt)+',' ")
        sql.AppendLine(" FROM sample ")
        sql.AppendLine(" for xml path('')) ")
        sql.AppendLine(" set @date_range = SUBSTRING(@date_range,0,LEN(@date_range)); ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" WITH cols AS ( ")
        sql.AppendLine(" SELECT 1 as i, CAST(@start_of_week AS DATE) AS dt ")
        sql.AppendLine(" UNION ALL ")
        sql.AppendLine(" SELECT i+1 as i, DATEADD(dd, 1, dt) ")
        sql.AppendLine(" FROM cols s ")
        sql.AppendLine(" WHERE DATEADD(dd, 1, dt) <= CAST(@end_of_week AS DATE)) ")
        sql.AppendLine("select @col_names= (SELECT QUOTENAME(dt)+' AS D'+cast(i as char(1))+',' ")
        sql.AppendLine(" FROM cols ")
        sql.AppendLine(" for xml path('')) ")
        sql.AppendLine(" set @col_names = SUBSTRING(@col_names,0,LEN(@col_names)) ")
        sql.AppendLine(" ")
        sql.AppendLine("set @sql =' ")
        sql.AppendLine(" ")
        sql.AppendLine("select Destination, [Index] , '+@col_names+' ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select ")
        sql.AppendLine("	[date] as [Date] ")
        sql.AppendLine("	,Destination ")
        sql.AppendLine("	,''Loading_Efficiency'' as [Index] ")
        sql.AppendLine("	, sum(cast(cast(qty as numeric(6,0))*100/cast(boxes_in_truck as numeric(6,0)) as numeric(5,2))) as [Val] ")
        sql.AppendLine("	from [UserModules].[user].[Truck_loading_weekly_report] wr ")
        sql.AppendLine("	left join [UserModules].[user].TRUCK_LOADING_MASTER_DATA md ")
        sql.AppendLine("	on wr.model=md.material and wr.truck_type = md.type_of_truck ")
        sql.AppendLine("	where [date] in ('+replace(replace(@date_range,'[',''''),']','''')+') ")
        sql.AppendLine("	group by [date], destination ")
        sql.AppendLine(" ")
        sql.AppendLine("	union ")
        sql.AppendLine(" ")
        sql.AppendLine("	select ")
        sql.AppendLine("	[date] as [Date] ")
        sql.AppendLine("	,Destination ")
        sql.AppendLine("	,''CMB'' as [Index] ")
        sql.AppendLine("	,sum(cast(qty as numeric(6,0))*cast(volume as numeric(7,3))) as [Val] ")
        sql.AppendLine("	from [UserModules].[user].[Truck_loading_weekly_report] wr ")
        sql.AppendLine("	left join [UserModules].[user].TRUCK_LOADING_MASTER_DATA md ")
        sql.AppendLine("	on wr.model=md.material and wr.truck_type = md.type_of_truck ")
        sql.AppendLine("	where [date] in ('+replace(replace(@date_range,'[',''''),']','''')+') ")
        sql.AppendLine("	group by [date], destination ")
        sql.AppendLine(") src ")
        sql.AppendLine(" ")
        sql.AppendLine("pivot ")
        sql.AppendLine("( ")
        sql.AppendLine("	sum([val]) ")
        sql.AppendLine("	for [date] in ('+@date_range+') ")
        sql.AppendLine(") pvt ")
        sql.AppendLine("order by destination asc, [Index] desc ")
        sql.AppendLine("' ")
        sql.AppendLine("exec(@sql) ")



        TruckLoadingPlan = GetLoadingInfo()
        SQLDataSource.SelectCommand = sql.ToString



        report.DataSource = SQLDataSource
        Try
            report.DataBind()
        Catch ex As Exception

        End Try

    End Sub

    Private Sub r_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
        CollectHeaderColumn_List(report, 1, 1)
    End Sub



    Public Sub CollectHeaderColumn_List(ByRef lv As ListView, colNumber As Integer, Optional referenceColNumber As Integer = -1)
        Dim curCell As New HtmlTableCell
        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 Object In lv.Items
            If DirectCast((dr.Controls(ColNumberToCheckText * 2 - 1).controls(1)), System.Web.UI.WebControls.Label).[Text] <> curText Then
                If rowSpan > 1 Then
                    curCell.RowSpan = rowSpan
                End If
                curCell = dr.Controls(colNumber * 2 - 1)
                curText = DirectCast((dr.Controls(ColNumberToCheckText * 2 - 1).controls(1)), System.Web.UI.WebControls.Label).[Text]
                rowSpan = 1
            Else
                dr.Controls(colNumber * 2 - 1).Visible = False
                rowSpan += 1
            End If
        Next
        If rowSpan > 1 Then
            curCell.RowSpan = rowSpan
        End If
    End Sub




End Class