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

ModuleMaster/srdc/plan.aspx.vb
928 строк49 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)
            Case "d2_"
                class_attr = "d2_" + time_previous.Substring(0, 2)
            Case "d3_"
                class_attr = "d3_" + time_previous.Substring(0, 2)
            Case "d4_"
                class_attr = "d4_" + time_previous.Substring(0, 2)
            Case "d5_"
                class_attr = "d5_" + time_previous.Substring(0, 2)
            Case "d6_"
                class_attr = "d6_" + time_previous.Substring(0, 2)
            Case "d7_"
                class_attr = "d7_" + 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 sql As New StringBuilder
        sql.AppendLine("select * from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("isnull(d1.div , '') as [d1_div] ")
        sql.AppendLine(",isnull(d1.so , '') as [d1_so] ")
        sql.AppendLine(",isnull(d1.do , '') as [d1_do] ")
        sql.AppendLine(",isnull(d1.sold_to , '') as [d1_sold] ")
        sql.AppendLine(",isnull(d1.sold_to_name , '') as [d1_sold_to_name] ")
        sql.AppendLine(",isnull(d1.plan_gi_date, ( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 1 ")
        sql.AppendLine(") ")
        sql.AppendLine(")as [d1_plan_gi_date] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d1.ARRIVE_TIME, d2.ARRIVE_TIME), d3.arrive_time), d4.arrive_time), d5.arrive_time), d6.arrive_time), d7.arrive_time) as [d1_arrive_time] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d1.load_time, d2.load_time), d3.load_time), d4.load_time), d5.load_time), d6.load_time), d7.load_time) as [d1_load_time] ")
        sql.AppendLine(",isnull(d1.volume , '') as [d1_volume] ")
        sql.AppendLine(",isnull(d1.qty , '') as [d1_qty] ")
        sql.AppendLine(",isnull(d1.row_id , '') as [d1_row] ")
        sql.AppendLine(",isnull(d1.TR_ZONE , '') as [d1_tr_zone] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(",isnull(d2.div , '') as [d2_div] ")
        sql.AppendLine(",isnull(d2.so , '') as [d2_so] ")
        sql.AppendLine(",isnull(d2.do , '') as [d2_do] ")
        sql.AppendLine(",isnull(d2.sold_to , '') as [d2_sold] ")
        sql.AppendLine(",isnull(d2.sold_to_name , '') as [d2_sold_to_name] ")
        sql.AppendLine(",isnull(d2.plan_gi_date, ( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 2 ")
        sql.AppendLine(") ")
        sql.AppendLine(") as [d2_plan_gi_date] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d2.ARRIVE_TIME, d1.ARRIVE_TIME), d3.arrive_time), d4.arrive_time), d5.arrive_time), d6.arrive_time), d7.arrive_time) as [d2_arrive_time] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d2.load_time, d1.load_time), d3.load_time), d4.load_time), d5.load_time), d6.load_time), d7.load_time) as [d2_load_time] ")
        sql.AppendLine(",isnull(d2.volume , '') as [d2_volume] ")
        sql.AppendLine(",isnull(d2.qty , '') as [d2_qty] ")
        sql.AppendLine(",isnull(d2.row_id , '') as [d2_row] ")
        sql.AppendLine(",isnull(d2.TR_ZONE , '') as [d2_tr_zone] ")
        sql.AppendLine(" ")
        sql.AppendLine(",isnull(d3.div , '') as [d3_div] ")
        sql.AppendLine(",isnull(d3.so , '') as [d3_so] ")
        sql.AppendLine(",isnull(d3.do , '') as [d3_do] ")
        sql.AppendLine(",isnull(d3.sold_to , '') as [d3_sold] ")
        sql.AppendLine(",isnull(d3.sold_to_name , '') as [d3_sold_to_name] ")
        sql.AppendLine(",isnull(d3.plan_gi_date, ( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 3 ")
        sql.AppendLine(") ")
        sql.AppendLine(") as [d3_plan_gi_date] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d3.ARRIVE_TIME, d2.ARRIVE_TIME), d1.arrive_time), d4.arrive_time), d5.arrive_time), d6.arrive_time), d7.arrive_time) as [d3_arrive_time] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d3.load_time, d2.load_time), d1.load_time), d4.load_time), d5.load_time), d6.load_time), d7.load_time) as [d3_load_time] ")
        sql.AppendLine(",isnull(d3.volume , '') as [d3_volume] ")
        sql.AppendLine(",isnull(d3.qty , '') as [d3_qty] ")
        sql.AppendLine(",isnull(d3.row_id , '') as [d3_row] ")
        sql.AppendLine(",isnull(d3.TR_ZONE , '') as [d3_tr_zone] ")
        sql.AppendLine(" ")
        sql.AppendLine(",isnull(d4.div , '') as [d4_div] ")
        sql.AppendLine(",isnull(d4.so , '') as [d4_so] ")
        sql.AppendLine(",isnull(d4.do , '') as [d4_do] ")
        sql.AppendLine(",isnull(d4.sold_to , '') as [d4_sold] ")
        sql.AppendLine(",isnull(d4.sold_to_name , '') as [d4_sold_to_name] ")
        sql.AppendLine(",isnull(d4.plan_gi_date, ( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 4 ")
        sql.AppendLine(") ")
        sql.AppendLine(") as [d4_plan_gi_date] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d4.ARRIVE_TIME, d2.ARRIVE_TIME), d3.arrive_time), d1.arrive_time), d5.arrive_time), d6.arrive_time), d7.arrive_time) as [d4_arrive_time] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d4.load_time, d2.load_time), d3.load_time), d1.load_time), d5.load_time), d6.load_time), d7.load_time) as [d4_load_time] ")
        sql.AppendLine(",isnull(d4.volume , '') as [d4_volume] ")
        sql.AppendLine(",isnull(d4.qty , '') as [d4_qty] ")
        sql.AppendLine(",isnull(d4.row_id , '') as [d4_row] ")
        sql.AppendLine(",isnull(d4.TR_ZONE , '') as [d4_tr_zone] ")
        sql.AppendLine(" ")
        sql.AppendLine(",isnull(d5.div , '') as [d5_div] ")
        sql.AppendLine(",isnull(d5.so , '') as [d5_so] ")
        sql.AppendLine(",isnull(d5.do , '') as [d5_do] ")
        sql.AppendLine(",isnull(d5.sold_to , '') as [d5_sold] ")
        sql.AppendLine(",isnull(d5.sold_to_name , '') as [d5_sold_to_name] ")
        sql.AppendLine(",isnull(d5.plan_gi_date, ( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 5 ")
        sql.AppendLine(") ")
        sql.AppendLine(") as [d5_plan_gi_date] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d5.ARRIVE_TIME, d2.ARRIVE_TIME), d3.arrive_time), d4.arrive_time), d1.arrive_time), d6.arrive_time), d7.arrive_time) as [d5_arrive_time] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d5.load_time, d2.load_time), d3.load_time), d4.load_time), d1.load_time), d6.load_time), d7.load_time) as [d5_load_time] ")
        sql.AppendLine(",isnull(d5.volume , '') as [d5_volume] ")
        sql.AppendLine(",isnull(d5.qty , '') as [d5_qty] ")
        sql.AppendLine(",isnull(d5.row_id , '') as [d5_row] ")
        sql.AppendLine(",isnull(d5.TR_ZONE , '') as [d5_tr_zone] ")
        sql.AppendLine(" ")
        sql.AppendLine(",isnull(d6.div , '') as [d6_div] ")
        sql.AppendLine(",isnull(d6.so , '') as [d6_so] ")
        sql.AppendLine(",isnull(d6.do , '') as [d6_do] ")
        sql.AppendLine(",isnull(d6.sold_to , '') as [d6_sold] ")
        sql.AppendLine(",isnull(d6.sold_to_name , '') as [d6_sold_to_name] ")
        sql.AppendLine(",isnull(d6.plan_gi_date, ( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 6 ")
        sql.AppendLine(") ")
        sql.AppendLine(") as [d6_plan_gi_date] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d6.ARRIVE_TIME, d2.ARRIVE_TIME), d3.arrive_time), d4.arrive_time), d5.arrive_time), d1.arrive_time), d7.arrive_time) as [d6_arrive_time] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d6.load_time, d2.load_time), d3.load_time), d4.load_time), d5.load_time), d1.load_time), d7.load_time) as [d6_load_time] ")
        sql.AppendLine(",isnull(d6.volume , '') as [d6_volume] ")
        sql.AppendLine(",isnull(d6.qty , '') as [d6_qty] ")
        sql.AppendLine(",isnull(d6.row_id , '') as [d6_row] ")
        sql.AppendLine(",isnull(d6.TR_ZONE , '') as [d6_tr_zone] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(",isnull(d7.div , '') as [d7_div] ")
        sql.AppendLine(",isnull(d7.so , '') as [d7_so] ")
        sql.AppendLine(",isnull(d7.do , '') as [d7_do] ")
        sql.AppendLine(",isnull(d7.sold_to , '') as [d7_sold] ")
        sql.AppendLine(",isnull(d7.sold_to_name , '') as [d7_sold_to_name] ")
        sql.AppendLine(",isnull(d7.plan_gi_date, ( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 7 ")
        sql.AppendLine(") ")
        sql.AppendLine(") as [d7_plan_gi_date] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d7.ARRIVE_TIME, d2.ARRIVE_TIME), d3.arrive_time), d4.arrive_time), d5.arrive_time), d6.arrive_time), d1.arrive_time) as [d7_arrive_time] ")
        sql.AppendLine(",isnull(isnull(isnull(isnull(isnull(isnull(d7.load_time, d2.load_time), d3.load_time), d4.load_time), d5.load_time), d6.load_time), d1.load_time) as [d7_load_time] ")
        sql.AppendLine(",isnull(d7.volume , '') as [d7_volume] ")
        sql.AppendLine(",isnull(d7.qty , '') as [d7_qty] ")
        sql.AppendLine(",isnull(d7.row_id , '') as [d7_row] ")
        sql.AppendLine(",isnull(d7.TR_ZONE , '') as [d7_tr_zone] ")
        sql.AppendLine(" ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        sql.AppendLine(",arrive_time ")
        sql.AppendLine(",load_time ")
        sql.AppendLine(",volume ")
        sql.AppendLine(", qty ")
        sql.AppendLine(",row_number () over (partition by plan_gi_date, load_time order by load_time) as row_id ")
        sql.AppendLine(",TR_ZONE ")
        sql.AppendLine(",tta ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        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(",case ")
        sql.AppendLine("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 (arrive_time 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(",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 sold_to = value1 and param_name = 'tta' ")
        sql.AppendLine("where ")
        sql.AppendLine("PLAN_GI_DATE= ")
        sql.AppendLine("( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 1 ")
        sql.AppendLine(") ")
        sql.AppendLine("and 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, so, do, PLAN_GI_DATE ,ARRIVE_TIME, TR_ZONE, value2 ")
        sql.AppendLine(") as [Day] ")
        sql.AppendLine(") as D1 ")
        sql.AppendLine(" ")
        sql.AppendLine("full join ")
        sql.AppendLine(" ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        sql.AppendLine(",arrive_time ")
        sql.AppendLine(",load_time ")
        sql.AppendLine(",volume ")
        sql.AppendLine(", qty ")
        sql.AppendLine(",row_number () over (partition by plan_gi_date, load_time order by load_time) as row_id ")
        sql.AppendLine(",TR_ZONE ")
        sql.AppendLine(",tta ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        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(",case ")
        sql.AppendLine("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 (arrive_time 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(",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 sold_to = value1 and param_name = 'tta' ")
        sql.AppendLine("where ")
        sql.AppendLine("PLAN_GI_DATE= ")
        sql.AppendLine("( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 2 ")
        sql.AppendLine(") ")
        sql.AppendLine("and 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, so, do, PLAN_GI_DATE ,ARRIVE_TIME, TR_ZONE, value2 ")
        sql.AppendLine(") as [Day] ")
        sql.AppendLine(") as D2 ")
        sql.AppendLine("on (d1.load_time = d2.load_time ")
        sql.AppendLine("and d1.row_id = d2.row_id) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("full join ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        sql.AppendLine(",arrive_time ")
        sql.AppendLine(",load_time ")
        sql.AppendLine(",volume ")
        sql.AppendLine(", qty ")
        sql.AppendLine(",row_number () over (partition by plan_gi_date, load_time order by load_time) as row_id ")
        sql.AppendLine(",TR_ZONE ")
        sql.AppendLine(",tta ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        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(",case ")
        sql.AppendLine("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 (arrive_time 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(",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 sold_to = value1 and param_name = 'tta' ")
        sql.AppendLine("where ")
        sql.AppendLine("PLAN_GI_DATE= ")
        sql.AppendLine("( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 3 ")
        sql.AppendLine(") ")
        sql.AppendLine("and 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, so, do, PLAN_GI_DATE ,ARRIVE_TIME, TR_ZONE, value2 ")
        sql.AppendLine(") as [Day] ")
        sql.AppendLine(") as D3 ")
        sql.AppendLine("on (d1.load_time = d3.load_time ")
        sql.AppendLine("and d1.row_id = d3.row_id) ")
        sql.AppendLine("or (d2.load_time = d3.load_time ")
        sql.AppendLine("and d2.row_id = d3.row_id) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("full join ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        sql.AppendLine(",arrive_time ")
        sql.AppendLine(",load_time ")
        sql.AppendLine(",volume ")
        sql.AppendLine(", qty ")
        sql.AppendLine(",row_number () over (partition by plan_gi_date, load_time order by load_time) as row_id ")
        sql.AppendLine(",TR_ZONE ")
        sql.AppendLine(",tta ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        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(",case ")
        sql.AppendLine("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 (arrive_time 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(",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 sold_to = value1 and param_name = 'tta' ")
        sql.AppendLine("where ")
        sql.AppendLine("PLAN_GI_DATE= ")
        sql.AppendLine("( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 4 ")
        sql.AppendLine(") ")
        sql.AppendLine("and 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, so, do, PLAN_GI_DATE ,ARRIVE_TIME, TR_ZONE, value2 ")
        sql.AppendLine(") as [Day] ")
        sql.AppendLine(") as D4 ")
        sql.AppendLine("on (d1.load_time = d4.load_time ")
        sql.AppendLine("and d1.row_id = d4.row_id) ")
        sql.AppendLine("or (d2.load_time = d4.load_time ")
        sql.AppendLine("and d2.row_id = d4.row_id) ")
        sql.AppendLine("or (d3.load_time = d4.load_time ")
        sql.AppendLine("and d3.row_id = d4.row_id) ")
        sql.AppendLine(" ")
        sql.AppendLine("full join ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        sql.AppendLine(",arrive_time ")
        sql.AppendLine(",load_time ")
        sql.AppendLine(",volume ")
        sql.AppendLine(", qty ")
        sql.AppendLine(",row_number () over (partition by plan_gi_date, load_time order by load_time) as row_id ")
        sql.AppendLine(",TR_ZONE ")
        sql.AppendLine(",tta ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        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(",case ")
        sql.AppendLine("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 (arrive_time 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(",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 sold_to = value1 and param_name = 'tta' ")
        sql.AppendLine("where ")
        sql.AppendLine("PLAN_GI_DATE= ")
        sql.AppendLine("( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 5 ")
        sql.AppendLine(") ")
        sql.AppendLine("and 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, so, do, PLAN_GI_DATE ,ARRIVE_TIME, TR_ZONE, value2 ")
        sql.AppendLine(") as [Day] ")
        sql.AppendLine(") as D5 ")
        sql.AppendLine("on (d1.load_time = d5.load_time ")
        sql.AppendLine("and d1.row_id = d5.row_id) ")
        sql.AppendLine("or (d2.load_time = d5.load_time ")
        sql.AppendLine("and d2.row_id = d5.row_id) ")
        sql.AppendLine("or (d3.load_time = d5.load_time ")
        sql.AppendLine("and d3.row_id = d5.row_id) ")
        sql.AppendLine("or (d4.load_time = d5.load_time ")
        sql.AppendLine("and d4.row_id = d5.row_id) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("full join ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        sql.AppendLine(",arrive_time ")
        sql.AppendLine(",load_time ")
        sql.AppendLine(",volume ")
        sql.AppendLine(", qty ")
        sql.AppendLine(",row_number () over (partition by plan_gi_date, load_time order by load_time) as row_id ")
        sql.AppendLine(",TR_ZONE ")
        sql.AppendLine(",tta ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        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(",case ")
        sql.AppendLine("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 (arrive_time 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(",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 sold_to = value1 and param_name = 'tta' ")
        sql.AppendLine("where ")
        sql.AppendLine("PLAN_GI_DATE= ")
        sql.AppendLine("( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 6 ")
        sql.AppendLine(") ")
        sql.AppendLine("and 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, so, do, PLAN_GI_DATE ,ARRIVE_TIME, TR_ZONE, value2 ")
        sql.AppendLine(") as [Day] ")
        sql.AppendLine(") as D6 ")
        sql.AppendLine("on (d1.load_time = d6.load_time ")
        sql.AppendLine("and d1.row_id = d6.row_id) ")
        sql.AppendLine("or (d2.load_time = d6.load_time ")
        sql.AppendLine("and d2.row_id = d6.row_id) ")
        sql.AppendLine("or (d3.load_time = d6.load_time ")
        sql.AppendLine("and d3.row_id = d6.row_id) ")
        sql.AppendLine("or (d4.load_time = d6.load_time ")
        sql.AppendLine("and d4.row_id = d6.row_id) ")
        sql.AppendLine("or (d5.load_time = d6.load_time ")
        sql.AppendLine("and d5.row_id = d6.row_id) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("full join ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        sql.AppendLine(",arrive_time ")
        sql.AppendLine(",load_time ")
        sql.AppendLine(",volume ")
        sql.AppendLine(", qty ")
        sql.AppendLine(",row_number () over (partition by plan_gi_date, load_time order by load_time) as row_id ")
        sql.AppendLine(",TR_ZONE ")
        sql.AppendLine(",tta ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("div ")
        sql.AppendLine(",so ")
        sql.AppendLine(",do ")
        sql.AppendLine(",sold_to ")
        sql.AppendLine(",sold_to_name ")
        sql.AppendLine(",PLAN_GI_DATE ")
        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(",case ")
        sql.AppendLine("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 (arrive_time 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(",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 sold_to = value1 and param_name = 'tta' ")
        sql.AppendLine("where ")
        sql.AppendLine("PLAN_GI_DATE= ")
        sql.AppendLine("( ")
        sql.AppendLine("select plan_gi_date ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct top 7 PLAN_GI_DATE, dense_rank () over (order by plan_GI_date) as [rank] ")
        sql.AppendLine("from [UserModules].[user].[outbound_planning] ")
        sql.AppendLine("where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) ")
        sql.AppendLine(") as [7days] ")
        sql.AppendLine("where [rank] = 7 ")
        sql.AppendLine(") ")
        sql.AppendLine("and 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, so, do, PLAN_GI_DATE ,ARRIVE_TIME, TR_ZONE, value2 ")
        sql.AppendLine(") as [Day] ")
        sql.AppendLine(") as D7 ")
        sql.AppendLine("on (d1.load_time = d7.load_time ")
        sql.AppendLine("and d1.row_id = d7.row_id) ")
        sql.AppendLine("or (d2.load_time = d7.load_time ")
        sql.AppendLine("and d2.row_id = d7.row_id) ")
        sql.AppendLine("or (d3.load_time = d7.load_time ")
        sql.AppendLine("and d3.row_id = d7.row_id) ")
        sql.AppendLine("or (d4.load_time = d7.load_time ")
        sql.AppendLine("and d4.row_id = d7.row_id) ")
        sql.AppendLine("or (d5.load_time = d7.load_time ")
        sql.AppendLine("and d5.row_id = d7.row_id) ")
        sql.AppendLine("or (D6.load_time = d7.load_time ")
        sql.AppendLine("and D6.row_id = d7.row_id) ")
        sql.AppendLine(") as result ")
        sql.AppendLine("order by d1_PLAN_GI_DATE, d1_load_time, d2_PLAN_GI_DATE, d2_load_time, d3_PLAN_GI_DATE, d3_load_time, d4_PLAN_GI_DATE, d4_load_time, d5_PLAN_GI_DATE, d5_load_time, d6_PLAN_GI_DATE, d6_load_time, d7_PLAN_GI_DATE, d7_load_time ")
        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 date_table As DataTable = sqlf.GetData("select distinct top 7 PLAN_GI_DATE from [UserModules].[user].[outbound_planning] where PLAN_GI_DATE >= cast (dateadd (dayofyear, 1, getdate()) as date) order by plan_gi_date")


        Dim Tbl As Table = schedule

        Dim time_current As String
        Dim time_previous As String = ""

        Dim d1_volume As Decimal = 0
        Dim d2_volume As Decimal = 0
        Dim d3_volume As Decimal = 0
        Dim d4_volume As Decimal = 0
        Dim d5_volume As Decimal = 0
        Dim d6_volume As Decimal = 0
        Dim d7_volume As Decimal = 0

        Dim d1_qty As Decimal = 0
        Dim d2_qty As Decimal = 0
        Dim d3_qty As Decimal = 0
        Dim d4_qty As Decimal = 0
        Dim d5_qty As Decimal = 0
        Dim d6_qty As Decimal = 0
        Dim d7_qty As Decimal = 0

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



        Dim tmp_str As String


        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_arrive_time", "") _
                   .Replace("d1_load_time", time_previous) _
                   .Replace("d1_volume", d1_volume) _
                   .Replace("d1_qty", d1_qty) _
                   .Replace("d2_div", "") _
                   .Replace("d2_SO", "") _
                   .Replace("d2_do", "") _
                   .Replace("d2_sold_to_name", "") _
                   .Replace("d2_arrive_time", "") _
                   .Replace("d2_load_time", time_previous) _
                   .Replace("d2_volume", d2_volume) _
                   .Replace("d2_qty", d2_qty) _
                   .Replace("d3_div", "") _
                   .Replace("d3_SO", "") _
                   .Replace("d3_do", "") _
                   .Replace("d3_sold_to_name", "") _
                   .Replace("d3_arrive_time", "") _
                   .Replace("d3_load_time", time_previous) _
                   .Replace("d3_volume", d3_volume) _
                   .Replace("d3_qty", d3_qty) _
                   .Replace("d4_div", "") _
                   .Replace("d4_SO", "") _
                   .Replace("d4_do", "") _
                   .Replace("d4_sold_to_name", "") _
                   .Replace("d4_arrive_time", "") _
                   .Replace("d4_load_time", time_previous) _
                   .Replace("d4_volume", d4_volume) _
                   .Replace("d4_qty", d4_qty) _
                   .Replace("d5_div", "") _
                   .Replace("d5_SO", "") _
                   .Replace("d5_do", "") _
                   .Replace("d5_sold_to_name", "") _
                   .Replace("d5_arrive_time", "") _
                   .Replace("d5_load_time", time_previous) _
                   .Replace("d5_volume", d5_volume) _
                   .Replace("d5_qty", d5_qty) _
                   .Replace("d6_div", "") _
                   .Replace("d6_SO", "") _
                   .Replace("d6_do", "") _
                   .Replace("d6_sold_to_name", "") _
                   .Replace("d6_arrive_time", "") _
                   .Replace("d6_load_time", time_previous) _
                   .Replace("d6_volume", d6_volume) _
                   .Replace("d6_qty", d6_qty) _
                   .Replace("d7_div", "") _
                   .Replace("d7_SO", "") _
                   .Replace("d7_do", "") _
                   .Replace("d7_sold_to_name", "") _
                   .Replace("d7_arrive_time", "") _
                   .Replace("d7_load_time", time_previous) _
                   .Replace("d7_volume", d7_volume) _
                   .Replace("d7_qty", d7_qty)
                    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
                d2_volume = 0
                d3_volume = 0
                d4_volume = 0
                d5_volume = 0
                d6_volume = 0
                d7_volume = 0

                d1_qty = 0
                d2_qty = 0
                d3_qty = 0
                d4_qty = 0
                d5_qty = 0
                d6_qty = 0
                d7_qty = 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", 49)
                    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_arrive_time", data_row("d1_arrive_time").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("d2_div", data_row("d2_div")) _
                        .Replace("d2_SO", data_row("d2_so")) _
                        .Replace("d2_do", data_row("d2_do")) _
                        .Replace("d2_sold_to_name", data_row("d2_sold_to_name")) _
                        .Replace("d2_arrive_time", data_row("d2_arrive_time").ToString) _
                        .Replace("d2_load_time", data_row("d2_load_time").ToString) _
                        .Replace("d2_volume", data_row("d2_volume")) _
                        .Replace("d2_qty", data_row("d2_qty")) _
                        .Replace("d3_div", data_row("d3_div")) _
                        .Replace("d3_SO", data_row("d3_so")) _
                        .Replace("d3_do", data_row("d3_do")) _
                        .Replace("d3_sold_to_name", data_row("d3_sold_to_name")) _
                        .Replace("d3_arrive_time", data_row("d3_arrive_time").ToString) _
                        .Replace("d3_load_time", data_row("d3_load_time").ToString) _
                        .Replace("d3_volume", data_row("d3_volume")) _
                        .Replace("d3_qty", data_row("d3_qty")) _
                        .Replace("d4_div", data_row("d4_div")) _
                        .Replace("d4_SO", data_row("d4_so")) _
                        .Replace("d4_do", data_row("d4_do")) _
                        .Replace("d4_sold_to_name", data_row("d4_sold_to_name")) _
                        .Replace("d4_arrive_time", data_row("d4_arrive_time").ToString) _
                        .Replace("d4_load_time", data_row("d4_load_time").ToString) _
                        .Replace("d4_volume", data_row("d4_volume")) _
                        .Replace("d4_qty", data_row("d4_qty")) _
                        .Replace("d5_div", data_row("d5_div")) _
                        .Replace("d5_SO", data_row("d5_so")) _
                        .Replace("d5_do", data_row("d5_do")) _
                        .Replace("d5_sold_to_name", data_row("d5_sold_to_name")) _
                        .Replace("d5_arrive_time", data_row("d5_arrive_time").ToString) _
                        .Replace("d5_load_time", data_row("d5_load_time").ToString) _
                        .Replace("d5_volume", data_row("d5_volume")) _
                        .Replace("d5_qty", data_row("d5_qty")) _
                        .Replace("d6_div", data_row("d6_div")) _
                        .Replace("d6_SO", data_row("d6_so")) _
                        .Replace("d6_do", data_row("d6_do")) _
                        .Replace("d6_sold_to_name", data_row("d6_sold_to_name")) _
                        .Replace("d6_arrive_time", data_row("d6_arrive_time").ToString) _
                        .Replace("d6_load_time", data_row("d6_load_time").ToString) _
                        .Replace("d6_volume", data_row("d6_volume")) _
                        .Replace("d6_qty", data_row("d6_qty")) _
                        .Replace("d7_div", data_row("d7_div")) _
                        .Replace("d7_SO", data_row("d7_so")) _
                        .Replace("d7_do", data_row("d7_do")) _
                        .Replace("d7_sold_to_name", data_row("d7_sold_to_name")) _
                        .Replace("d7_arrive_time", data_row("d7_arrive_time").ToString) _
                        .Replace("d7_load_time", data_row("d7_load_time").ToString) _
                        .Replace("d7_volume", data_row("d7_volume")) _
                        .Replace("d7_qty", data_row("d7_qty")) _
                        + "</nobr>"
                tc.Attributes.Add("class", td_class)
                tr.Cells.Add(tc)
            Next
            d1_volume = d1_volume + data_row("d1_volume")
            d2_volume = d2_volume + data_row("d2_volume")
            d3_volume = d3_volume + data_row("d3_volume")
            d4_volume = d4_volume + data_row("d4_volume")
            d5_volume = d5_volume + data_row("d5_volume")
            d6_volume = d6_volume + data_row("d6_volume")
            d7_volume = d7_volume + data_row("d7_volume")

            d1_qty = d1_qty + data_row("d1_qty")
            d2_qty = d2_qty + data_row("d2_qty")
            d3_qty = d3_qty + data_row("d3_qty")
            d4_qty = d4_qty + data_row("d4_qty")
            d5_qty = d5_qty + data_row("d5_qty")
            d6_qty = d6_qty + data_row("d6_qty")
            d7_qty = d7_qty + data_row("d7_qty")

            Tbl.Rows.Add(tr)
        Next


        Dim header_row As TableRow = plan_calendar
        For Each day As DataRow In date_table.Rows
            Dim tcell As New TableCell
            tcell.Text = day.ItemArray(0)
            tcell.Attributes.Add("Colspan", 7)
            tcell.Attributes.Add("align", "center")
            header_row.Cells.Add(tcell)
        Next



    End Sub
End Class