ModuleMaster/srdc/daily_plan.aspx.vb - main - ivan/ModMas

ModuleMaster/srdc/daily_plan.aspx.vb
300 строк13 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Partial Class SRDC_Plan
    Inherits System.Web.UI.Page
    Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
    Public class_array As New List(Of String)


    Private Function set_attr(cell As TableCell, time_previous As String) As String
        Dim class_text As String
        Dim class_attr As String
        class_text = cell.Text.Substring(0, 3)
        Select Case class_text
            Case "d1_"
                class_attr = "d1_" + time_previous.Substring(0, 2)
        End Select
        Return class_attr
    End Function

    Private Sub SRDC_Plan_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim date_table As DataTable = sqlf.GetData("select distinct top 1 PLAN_GI_DATE from [UserModules].[user].[outbound_planning] where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) order by plan_gi_date")

        If Not IsPostBack Then
            TextBox1.Text = date_table.Rows(0).ItemArray(0)
        End If
        draw(TextBox1.Text)

    End Sub

    Private Sub draw(user_date As Date)
        Dim sql As New StringBuilder
        sql.AppendLine("select ")
        sql.AppendLine("div as d1_div ")
        sql.AppendLine(",so as d1_so ")
        sql.AppendLine(",do as d1_do ")
        sql.AppendLine(",sold_to as d1_sold_to ")
        sql.AppendLine(",sold_to_name as d1_sold_to_name ")
        sql.AppendLine(",ship_to as d1_ship_to ")
        sql.AppendLine(",rdd as d1_rdd ")
        sql.AppendLine(",load_date as d1_load_date ")
        sql.AppendLine(",arrive_time as d1_arrive_time ")
        sql.AppendLine(",load_time as d1_load_time ")
        sql.AppendLine(",convert(varchar, rdd_datetime, 120) as d1_rdd_datetime ")
        sql.AppendLine(",load_datetime as d1_load_datetime ")
        sql.AppendLine(",volume as d1_volume ")
        sql.AppendLine(", qty as d1_qty ")
        sql.AppendLine(", amount as d1_amount ")
        sql.AppendLine(",row_number () over (partition by load_date, load_time order by load_time) as d1_row_id ")
        sql.AppendLine(",TR_ZONE as d1_tr_zone ")
        sql.AppendLine(",tta as d1_tta ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",ship_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",rdd ")
        sql.AppendLine(",case ")
        sql.AppendLine("when ARRIVE_TIME = '00:00:00' then '24:00:00' ")
        sql.AppendLine("else arrive_time ")
        sql.AppendLine("end as arrive_time ")
        sql.AppendLine(",cast(rdd +' '+ arrive_time as smalldatetime) as rdd_datetime ")
        sql.AppendLine(",convert(date, cast(dateadd(hour, -cast(isnull (value2, (select value2 from [UserModules].[user].[OUTBOUND_PLANNING_settings] where param_name = 'tta' and value1 = 'default')) as int) , cast(rdd +' '+ arrive_time as smalldatetime)) as smalldatetime) , 20)as load_datetime")
        sql.AppendLine(",case when ARRIVE_TIME <> '00:00:00' then cast(dateadd(hour, -cast(isnull (value2, (select value2 from [UserModules].[user].[OUTBOUND_PLANNING_settings] where param_name = 'tta' and value1 = 'default')) as int) , cast(rdd +' '+ arrive_time as smalldatetime)) as date) ")
        sql.AppendLine("else rdd ")
        sql.AppendLine("end as load_date ")
        sql.AppendLine(",case ")
        sql.AppendLine("when ARRIVE_TIME <> '00:00:00' then cast(cast(cast(datepart(hour,dateadd(hour, -cast(isnull (value2, (select value2 from [UserModules].[user].[OUTBOUND_PLANNING_settings] where param_name = 'tta' and value1 = 'default')) as int) , cast (arrive_time as time(0)))) as nvarchar) +':00:00' as time(0)) as nvarchar) ")
        sql.AppendLine("else '24:00:00' ")
        sql.AppendLine("end as load_time ")
        sql.AppendLine(",sum(CAST(volume as float)) as volume ")
        sql.AppendLine(",sum(CAST(qty as float)) as qty ")
        sql.AppendLine("--,row_number () over (partition by plan_gi_date, arrive_time order by arrive_time) as row_id ")
        sql.AppendLine(",TR_ZONE ")
        sql.AppendLine(",sum(CAST(so_amount as float)) as amount ")
        sql.AppendLine(",isnull (value2, (select value2 from [UserModules].[user].[OUTBOUND_PLANNING_settings] where param_name = 'tta' and value1 = 'default')) as tta ")
        sql.AppendLine(" ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("left join [UserModules].[user].[OUTBOUND_PLANNING_settings] ")
        sql.AppendLine("on ship_to = value1 and param_name = 'tta' ")
        sql.AppendLine("where ")
        sql.AppendLine("--PLAN_GI_DATE= '2016-07-30' and ")
        sql.AppendLine("tr_zone in (select value1 from [UserModules].[user].[Outbound_planning_settings] where param_name = 't_zone') ")
        sql.AppendLine("group by div, sold_to, sold_to_name, ship_to, so, do, rdd ,ARRIVE_TIME, TR_ZONE, value2 ")
        sql.AppendLine(") as [Day] ")
        sql.AppendLine("where ")
        sql.AppendLine("load_date = '" + user_date.ToString("yyyy-MM-dd") + "' ")
        Dim dt As DataTable = sqlf.GetData(sql.ToString)

        Dim settings_table As DataTable = sqlf.GetData("select * from [UserModules].[user].[OUTBOUND_PLANNING_SETTINGS] where param_name='capa'")



        Dim Tbl As Table = schedule
        Dim time_current As String
        Dim time_previous As String = ""

        Dim d1_volume As Decimal = 0
        Dim d1_qty As Decimal = 0
        Dim d1_amount As Decimal = 0

        Dim capa_capa As Integer
        Dim capa_less As Integer
        Dim capa_over As Integer

        Dim tmp_str As String

        Dim total_qty As Decimal = 0
        Dim total_volume As Decimal = 0
        Dim total_amount As Decimal = 0


        For Each str As DataRow In settings_table.Rows
            Select Case str.ItemArray(3)
                Case "capa"
                    capa_capa = str.ItemArray(4)
                Case "less"
                    capa_less = str.ItemArray(4)
                Case "over"
                    capa_over = str.ItemArray(4)
            End Select
        Next



        For Each data_row As DataRow In dt.Rows

            time_current = data_row("d1_load_time").ToString
            If time_previous = "" Then time_previous = time_current

            If time_current <> time_previous Then
                Dim tr_sum As New TableRow

                For Each c As TableCell In templateRow.Cells
                    Dim tc As New TableCell
                    Dim td_class As String = set_attr(c, time_previous)
                    ' If c.Text <> "d1_load_time" Then
                    tc.Attributes.Add("id", td_class)
                    tr_sum.Cells.Add(tc)
                    'End If
                    tc.Text = c.Text.Replace("d1_div", "") _
                   .Replace("d1_SO", "") _
                   .Replace("d1_do", "") _
                   .Replace("d1_sold_to_name", "") _
                   .Replace("d1_ship_to", "") _
                   .Replace("d1_arrive_time", "") _
                   .Replace("d1_load_time", time_previous) _
                   .Replace("d1_volume", d1_volume) _
                   .Replace("d1_qty", d1_qty) _
                   .Replace("d1_amount", "")
                    tr_sum.Cells.Add(tc)
                    If c.Text.Contains("volume") Then
                        If CInt(tc.Text) > capa_over Then
                            tmp_str = "#" + td_class + " {background-color: red;} "
                        End If
                        If CInt(tc.Text) > capa_capa And CInt(tc.Text) <= capa_over Then
                            tmp_str = "#" + td_class + " {background-color: yellow;} "
                        End If
                        If CInt(tc.Text) > capa_less And CInt(tc.Text) <= capa_capa Then
                            tmp_str = "#" + td_class + " {background-color: gray;} "
                        End If
                        If CInt(tc.Text) <= capa_less Then
                            tmp_str = "#" + td_class + " {background-color: green;} "
                        End If
                        class_array.Add(tmp_str)
                    End If

                Next
                time_previous = time_current
                Tbl.Rows.Add(tr_sum)

                d1_volume = 0
                d1_qty = 0
                d1_amount = 0

                If time_current = "24:00:00" Then
                    Dim tc As New TableCell
                    Dim trow As New TableRow
                    tc.Text = "<font size=+2><b>No time assigned</b></font>"
                    tc.Attributes.Add("colspan", 9)
                    trow.Cells.Add(tc)
                    Tbl.Rows.Add(trow)
                End If


            End If

            Dim tr As New TableRow
            For Each c As TableCell In templateRow.Cells
                Dim tc As New TableCell
                Dim td_class As String = set_attr(c, time_previous)

                tc.Text = "<nobr>" +
                        c.Text.Replace("d1_div", data_row("d1_div")) _
                        .Replace("d1_SO", data_row("d1_so")) _
                        .Replace("d1_do", data_row("d1_do")) _
                        .Replace("d1_sold_to_name", data_row("d1_sold_to_name")) _
                        .Replace("d1_ship_to", data_row("d1_ship_to")) _
                        .Replace("d1_arrive_time", data_row("d1_rdd_datetime").ToString) _
                        .Replace("d1_load_time", data_row("d1_load_time").ToString) _
                        .Replace("d1_volume", data_row("d1_volume")) _
                        .Replace("d1_qty", data_row("d1_qty")) _
                        .Replace("d1_amount", data_row("d1_amount")) _
                        + "</nobr>"
                tc.Attributes.Add("class", td_class)
                tr.Cells.Add(tc)
            Next
            d1_volume = d1_volume + data_row("d1_volume")
            d1_qty = d1_qty + data_row("d1_qty")
            d1_amount = d1_amount + data_row("d1_amount")

            total_qty = total_qty + data_row("d1_qty")
            total_volume = total_volume + data_row("d1_volume")
            total_amount = total_amount + data_row("d1_amount")
            Tbl.Rows.Add(tr)
        Next


        If time_current <> "24:00:00" Then
            Dim tr_sum As New TableRow

            For Each c As TableCell In templateRow.Cells
                Dim tc As New TableCell
                Dim td_class As String = set_attr(c, time_previous)
                ' If c.Text <> "d1_load_time" Then
                tc.Attributes.Add("id", td_class)
                tr_sum.Cells.Add(tc)
                'End If
                tc.Text = c.Text.Replace("d1_div", "") _
                   .Replace("d1_SO", "") _
                   .Replace("d1_do", "") _
                   .Replace("d1_sold_to_name", "") _
                   .Replace("d1_ship_to", "") _
                   .Replace("d1_arrive_time", "") _
                   .Replace("d1_load_time", time_previous) _
                   .Replace("d1_volume", d1_volume) _
                   .Replace("d1_qty", d1_qty) _
                   .Replace("d1_amount", "")
                tr_sum.Cells.Add(tc)
                If c.Text.Contains("volume") Then
                    If CInt(tc.Text) > capa_over Then
                        tmp_str = "#" + td_class + " {background-color: red;} "
                    End If
                    If CInt(tc.Text) > capa_capa And CInt(tc.Text) <= capa_over Then
                        tmp_str = "#" + td_class + " {background-color: yellow;} "
                    End If
                    If CInt(tc.Text) > capa_less And CInt(tc.Text) <= capa_capa Then
                        tmp_str = "#" + td_class + " {background-color: gray;} "
                    End If
                    If CInt(tc.Text) <= capa_less Then
                        tmp_str = "#" + td_class + " {background-color: green;} "
                    End If
                    class_array.Add(tmp_str)
                End If

            Next
            Tbl.Rows.Add(tr_sum)

            d1_volume = 0
            d1_qty = 0
            d1_amount = 0

        End If






        Dim tc_total_text As New TableCell
        Dim tc_total_volume As New TableCell
        Dim tc_total_qty As New TableCell
        Dim tc_total_amount As New TableCell
        Dim tr_total As New TableRow
        tc_total_text.Text = "<font size=+2><b>Total:</b></font>"
        tc_total_text.Attributes.Add("colspan", 6)
        tr_total.Cells.Add(tc_total_text)

        tc_total_volume.Text = "<font size=+2><b>" + total_volume.ToString + "</b></font>"
        tr_total.Cells.Add(tc_total_volume)

        tc_total_qty.Text = "<font size=+2><b>" + total_qty.ToString + "</b></font>"
        tr_total.Cells.Add(tc_total_qty)

        tc_total_amount.Text = "<font size=+2><b>" + total_amount.ToString + "</b></font>"
        tr_total.Cells.Add(tc_total_amount)

        Tbl.Rows.Add(tr_total)


    End Sub
    Protected Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged

    End Sub
    Protected Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick

    End Sub
End Class