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

ModuleMaster/pc/pallet_dashboard.aspx.vb
162 строки8 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6

Imports System.Data
Imports System.IO
Imports System.Web.UI.DataVisualization.Charting

Partial Class r
    Inherits System.Web.UI.Page
    Private line As String


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim dv_hourly As DataView

        Dim label As String
        Dim i As Integer
        Dim sql1 As New StringBuilder '  sql1 - для графика с колонками
        Dim dashboard_type As String = Request.QueryString("type")


        Select Case dashboard_type
            Case "TV"
                Hourly.Titles.Item(0).Text = "Pallet delivery status VD"
            Case "WM"
                Hourly.Titles.Item(0).Text = "Pallet delivery status WM"
        End Select


        'Строка запроса к базе для почасового графика
        sql1.AppendLine("declare @final_table as table ([hour] datetime, [plan] integer, [result] integer)")
        sql1.AppendLine("declare @diff integer")
        sql1.AppendLine("declare @hour datetime")
        sql1.AppendLine("declare @plan integer")
        sql1.AppendLine("declare @result integer")
        sql1.AppendLine("set @diff = 0")
        sql1.AppendLine("declare diff_cursor cursor for")
        sql1.AppendLine("(")
        sql1.AppendLine("----Начало курсора")
        sql1.AppendLine("select cast('1900-01-01 ' + right('0'+[plan].[hour],2) +':00:00' as datetime) as [hour], [plan], isnull([result], 0) as [result]")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select [hour], [plan] from")
        sql1.AppendLine("")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("sum (cast (H8 as integer)) as [8]")
        sql1.AppendLine(",sum (cast (H9 as integer)) as [9]")
        sql1.AppendLine(",sum (cast (H10 as integer)) as [10]")
        sql1.AppendLine(",sum (cast (H11 as integer)) as [11]")
        sql1.AppendLine(",sum (cast (H12 as integer)) as [12]")
        sql1.AppendLine(",sum (cast (H13 as integer)) as [13]")
        sql1.AppendLine(",sum (cast (H14 as integer)) as [14]")
        sql1.AppendLine(",sum (cast (H15 as integer)) as [15]")
        sql1.AppendLine(",sum (cast (H16 as integer)) as [16]")
        sql1.AppendLine("from [UserModules].[user].[PALLET_PLAN] [plan]")
        Select Case dashboard_type
            Case "TV"
                sql1.AppendLine("left join [UserModules].[user].[PALLET_MASTER] [master]")
                sql1.AppendLine("on [plan].PALLET_NO = [MASTER].PALLET_NO")
                sql1.AppendLine("where [master].[TYPE] = 'TV'")
            Case "WM"
                sql1.AppendLine("left join [UserModules].[user].[PALLET_MASTER] [master]")
                sql1.AppendLine("on [plan].PALLET_NO = [MASTER].PALLET_NO")
                sql1.AppendLine("where [master].[TYPE] = 'WM'")
        End Select
        sql1.AppendLine(") p")
        sql1.AppendLine("unpivot ([plan] for [hour] in ([8], [9], [10], [11], [12], [13], [14], [15], [16])) as unp")
        sql1.AppendLine(") [plan]")
        sql1.AppendLine("")
        sql1.AppendLine("left join")
        sql1.AppendLine("(")
        sql1.AppendLine("	select")
        sql1.AppendLine("	[hour]")
        sql1.AppendLine("	,sum (cast(qty as integer)) as result")
        sql1.AppendLine("	from")
        sql1.AppendLine("	(")
        sql1.AppendLine("		select")
        sql1.AppendLine("		cast(qty as integer) as qty")
        sql1.AppendLine("		,case when cast(datepart(hour, [datetime]) as varchar(2)) <= 8 then 8")
        sql1.AppendLine("			else cast(datepart(hour, [datetime]) as varchar(2))")
        sql1.AppendLine("		end as [hour]")
        sql1.AppendLine("		from [UserModules].[user].PALLET_STOCK [stock]")
        Select Case dashboard_type
            Case "TV"
                sql1.AppendLine("left join [UserModules].[user].[PALLET_MASTER] [master]")
                sql1.AppendLine("on [stock].PALLET_NO = [MASTER].PALLET_NO")
                sql1.AppendLine("where [master].[TYPE] = 'TV'")
            Case "WM"
                sql1.AppendLine("left join [UserModules].[user].[PALLET_MASTER] [master]")
                sql1.AppendLine("on [stock].PALLET_NO = [MASTER].PALLET_NO")
                sql1.AppendLine("where [master].[TYPE] = 'WM'")
        End Select
        sql1.AppendLine("	) src")
        sql1.AppendLine("	group by [hour]")
        sql1.AppendLine(") result")
        sql1.AppendLine("")
        sql1.AppendLine("on result.[hour] = [plan].[hour]")
        sql1.AppendLine("------Конец курсора")
        sql1.AppendLine(")")
        sql1.AppendLine("")
        sql1.AppendLine("OPEN diff_cursor")
        sql1.AppendLine("	FETCH NEXT FROM diff_cursor INTO @hour, @plan, @result")
        sql1.AppendLine("")
        sql1.AppendLine("	WHILE @@FETCH_STATUS = 0")
        sql1.AppendLine("	BEGIN")
        sql1.AppendLine("")
        sql1.AppendLine("		if @result + @diff > @plan")
        sql1.AppendLine("		begin")
        sql1.AppendLine("			set @diff = @result - @plan + @diff")
        sql1.AppendLine("			set @result = @plan")
        sql1.AppendLine("		end")
        sql1.AppendLine("		else")
        sql1.AppendLine("		begin")
        sql1.AppendLine("			set @result = @result + @diff")
        sql1.AppendLine("			set @diff = 0")
        sql1.AppendLine("		end")
        sql1.AppendLine("		")
        sql1.AppendLine("		insert into @final_table ([hour], [plan], [result]) values (@hour, @plan, @result)")
        sql1.AppendLine("		FETCH NEXT FROM diff_cursor INTO @hour, @plan, @result")
        sql1.AppendLine("")
        sql1.AppendLine("	end")
        sql1.AppendLine("	CLOSE diff_cursor")
        sql1.AppendLine("	DEALLOCATE diff_cursor")
        sql1.AppendLine("")
        sql1.AppendLine("	select *")
        sql1.AppendLine("	from @final_table")


        Sql_hourly.SelectCommand = sql1.ToString
        'SqlDataSource_summary_table.SelectCommand = 

        'Распихиваем данные по таблицам
        dv_hourly = Sql_hourly.Select(DataSourceSelectArguments.Empty)

        'Формируем почасовой график
        Hourly.Series.FindByName("Plan").Points.DataBind(dv_hourly, "hour", "plan", "")
        Hourly.Series.FindByName("Result").Points.DataBind(dv_hourly, "hour", "result", "")


        'Группируем колонки
        Hourly.Series.FindByName("Plan")("StackedGroupName") = "Gropu1"
        Hourly.Series.FindByName("Result")("StackedGroupName") = "Gropu2"


        ''Делаем подписи по оси X
        Hourly.ChartAreas("ChartArea1").AxisX.LabelStyle.Format = "H:mm"
        i = 0
        For Each row As DataRowView In dv_hourly
            Hourly.ChartAreas("ChartArea1").AxisX.CustomLabels.Add(Hourly.Series.FindByName("Plan").Points(i).XValue - 0.017, Hourly.Series.FindByName("Plan").Points(i).XValue + 0.017, "", 0, LabelMarkStyle.LineSideMark)
            label = Date.Parse(row.Item(0).ToString).ToString("H:mm") + " - " + DateAdd(DateInterval.Hour, 1, Date.Parse(row.Item(0).ToString)).ToString("H:mm")
            Hourly.ChartAreas("ChartArea1").AxisX.CustomLabels.Add(Hourly.Series.FindByName("Plan").Points(i).XValue - 0.017, Hourly.Series.FindByName("Plan").Points(i).XValue + 0.017, label, 1, LabelMarkStyle.LineSideMark)
            Hourly.ChartAreas("ChartArea1").AxisX.CustomLabels(i + i + 1).MarkColor = Drawing.Color.White
            i = i + 1
        Next
        If i <= 8 Then
            Hourly.ChartAreas("ChartArea1").AxisX.Maximum = 0.7
        Else
            'Hourly.ChartAreas("ChartArea1").AxisX.Maximum = Double.NaN
            Hourly.ChartAreas("ChartArea1").AxisX.Maximum = 0.7
        End If
    End Sub


End Class