ModuleMaster/demurrage/container_management.aspx.vb - main - ivan/ModMas

ModuleMaster/demurrage/container_management.aspx.vb
975 строк46 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.IO
Imports sharedClasses.dbFunctions
Imports System.Data.SqlClient
Imports System.Globalization
Imports System.Data
Imports System.Text.RegularExpressions



Partial Class demurrage_item
    Inherits System.Web.UI.Page

    Private db As New SqlFunctions

    Private row_action_cell As Integer = 0
    Private eta_placeholder_cell As Integer = 1
    Private record_id As Integer = 2
    Private odcy_id As Integer = 3
    Private odcy_flag As Integer = 4

    Private train_flag As Integer = 5

    Private Destination_cell_number As Integer = 6
    Private provider_cell_numbet As Integer = 7
    Private container_cell_number As Integer = 8
    Private condition_cell_number As Integer = 9
    Private cargo_cell_number As Integer = 10
    Private Arrival_date_cell_number As Integer = 11
    Private do_date As Integer = 12
    Private status_cell_number As Integer = 13
    Private CY_cell_numer As Integer = 14
    'По просьбе Кирилла текущий день надо поставить после Arrival date, поэтому номера у этих 2х столбцов совпадают
    Private current_day_cell As Integer = 14
    Private demurrage_cell_number As Integer = 15


    Private last_cell As Integer = 16 'Потому что 13х ячеек 2
    Private calendar_cells_count As Integer


    'Номера колонок в excell файле, содержащие destination и diviaison соответственно. И менно по комбинации этих 2х полей будет определяться принадлежность контейнера
    Private Destination_parse_col As Integer = 0
    Private Division_parse_col As Integer = 1


    Private transit_palceholder As String = "in transit"
    Private eta_palceholder As String = "eta"
    Private do_palceholder As String = "DO to SERK"
    Private a As sharedClasses.Auth.auth
    Private auth_flag As Boolean = False
    Public filter_train_flag As Boolean = False



    Private Sub authorize(mode As String)
        a = New sharedClasses.Auth.auth(mode, IIf(Request.UserHostAddress = "::1", "106.109.72.68", Request.UserHostAddress))
    End Sub


    Public Sub refresh(Optional ByVal param As String = "")
        If param = "" Then
            Response.Redirect(Request.RawUrl.Split("?")(0))
        Else
            Response.Redirect(Request.RawUrl + param)
        End If

    End Sub

    Public Sub get_data()
        Dim sql As New StringBuilder
        sql.AppendLine("SET DATEFORMAT ymd;")
        sql.AppendLine("begin try ")
        sql.AppendLine("select ")
        sql.AppendLine("header.DESTINATION as [Destination] ")
        sql.AppendLine(",header.PROVIDER as [Provider] ")
        sql.AppendLine(",header.ODCY_ID as [ODCY_ref] ")
        sql.AppendLine(",header.is_ODCY as [ODCY_flag] ")
        sql.AppendLine(",header.ETA_placeholder")
        sql.AppendLine(",item.id as [Container ID] ")
        sql.AppendLine(",item.CONTAINER as [Container] ")
        sql.AppendLine(",item.CONDITION as [Condition] ")
        sql.AppendLine(",item.CARGO as [Cargo] ")
        sql.AppendLine(",case	 ")
        sql.AppendLine("When isdate(item.ARRIVAL_DATE)=1 Then ")
        sql.AppendLine("item.ARRIVAL_DATE ")
        sql.AppendLine("Else ")
        sql.AppendLine("'' ")
        sql.AppendLine("End As [Arrival Date] ")
        sql.AppendLine(", item.do_date As [DO_date] ")
        sql.AppendLine(", isnull(item.[status], 'N/A') as [Status] ")
        sql.AppendLine(",item.do_date as [DO date] ")

        sql.AppendLine(",case ")
        sql.AppendLine("when [train_suggestion].[container] Is Not null then 'X'")
        sql.AppendLine("End As train_flag")

        sql.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] as item ")
        sql.AppendLine("left join [UserModules].[user].[DESTINATION_MASTER] as header ")
        sql.AppendLine("on header.id = item.CARRIER_ID ")

        sql.AppendLine("Left Join [UserModules].[user].[train_suggestion]")
        sql.AppendLine("On item.CONTAINER = [train_suggestion].[container]")

        sql.AppendLine("where ")
        sql.AppendLine("(( ")
        sql.AppendLine("	[status] = 'SERK' ")
        sql.AppendLine("	and ")
        sql.AppendLine("		( ")
        sql.AppendLine("		(len(arrival_date) =8 and cast(ARRIVAL_DATE as date) >= cast(getdate() as date)) ")
        sql.AppendLine("		or ")
        sql.AppendLine("		len(arrival_date) <>8 ")
        sql.AppendLine("		) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(") ")
        sql.AppendLine("or [status] <> 'SERK' or [status] is null) ")
        If filter_train_flag Then
            sql.AppendLine("and case when [train_suggestion].[container] Is Not null then 'X' End = 'X'")
        End If
        sql.AppendLine("end try ")
        sql.AppendLine("begin catch ")
        sql.AppendLine("end catch ")
        main_list.DataSource = db.GetData(sql.ToString)


        Try
            main_list.DataBind()
        Catch ex As Exception
            Response.Write(ex.ToString)
        End Try

    End Sub


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


        If Not IsPostBack Then

            Dim userid As String = ""
            authorize("SSO")
            userid = a.userId
            If userid = "naumenko.mv" Or userid = "jh77.won" Or userid = "i.krivokon" Or userid = "k.zuev" Then
                ImageAdd.Visible = True
                DataUpload.Visible = True
                auth_flag = True
            End If

            If Not IsNothing(Request.QueryString("train_filter")) Then
                If Request.QueryString("train_filter") = "true" Then
                    filter_train_flag = True
                Else
                    filter_train_flag = False
                End If
            End If


            'По умолчанию показываем все контейнеры, если есть параметр train_filter=true, Тогда показываем только конты, котоыре можно отправить поездом
            gridStyle.Attributes.Add("href", "http://" & HttpContext.Current.Request.Url.Authority & HttpContext.Current.Request.ApplicationPath & "/Styles/editableGrid1.css")
            pageStyle.Attributes.Add("href", "http://" & HttpContext.Current.Request.Url.Authority & HttpContext.Current.Request.ApplicationPath & "/Styles/page.css")
            If auth_flag Then
                main_list.Columns.Item(row_action_cell).Visible = True
            End If


            'Определяем дату и время выгрузки MRP run
            Dim MRP_date_row As Data.DataRow
            Dim sql_mrp As New StringBuilder
            sql_mrp.AppendLine("SET DATEFORMAT ymd; select top 1 cast(create_date as datetime) as mrp_date from [UserModules].[user].[MRP_FOR_TRAIN]")
            MRP_date_row = db.GetRow(sql_mrp.ToString)
            Dim mrp_date As Date = Date.Parse(MRP_date_row.Item("mrp_date"))
            MRP_date_label.Text = mrp_date.ToString


            'Определяем дату и время загрузки деталей по контейнерам
            Dim cont_details_date_row As Data.DataRow
            Dim sql_cont_details As New StringBuilder
            sql_cont_details.AppendLine("select isnull(max(cast(create_date as datetime)),0) as cont_details_date from [UserModules].[user].[CONTAINER_DETAILS]")
            cont_details_date_row = db.GetRow(sql_cont_details.ToString)
            Dim cont_details_date As Date = Date.Parse(cont_details_date_row.Item("cont_details_date"))
            Containers_details_date_label.Text = cont_details_date.ToString


            'Определяем дату и время загрузки трейсингов
            Dim trasing_date_row As Data.DataRow
            Dim sql_trasing As New StringBuilder
            sql_trasing.AppendLine("select isnull(max(cast(create_date as datetime)),0) as trasing_date from [UserModules].[user].[ITEM_INFORMATION]")
            trasing_date_row = db.GetRow(sql_trasing.ToString)
            Dim trasing_date As Date = Date.Parse(trasing_date_row.Item("trasing_date"))
            Trasing_date_label.Text = trasing_date.ToString



            'Определяем дату и время прогноза отправки поездов
            Dim train_date_row As Data.DataRow
            Dim sql_train As New StringBuilder
            sql_train.AppendLine("select isnull(max(cast(create_date as datetime)),0) as train_date from [UserModules].[user].[train_suggestion]")
            train_date_row = db.GetRow(sql_train.ToString)
            Dim train_date As Date = Date.Parse(train_date_row.Item("train_date"))
            Train_date_label.Text = train_date.ToString


            'Проверяем актуальность данных, основываясь на вышеполученных датах
            If trasing_date > cont_details_date Then
                Containers_details_date_label.BackColor = Drawing.Color.Red
                Containers_details_date_label.Text = Containers_details_date_label.Text + " the information is outdated. Upload actuall containers detail info"
            Else
                Containers_details_date_label.BackColor = Drawing.Color.LightGreen
                Containers_details_date_label.Text = Containers_details_date_label.Text + " - Data is Ok"
            End If


            If mrp_date > train_date Then
                Train_date_label.BackColor = Drawing.Color.Red
                Train_date_label.Text = Train_date_label.Text + " the information is outdated. Run calculation once again"
            Else
                Train_date_label.BackColor = Drawing.Color.LightGreen
                Train_date_label.Text = Train_date_label.Text + " Suggestion is actuall"
            End If

            If DateDiff(DateInterval.Day, trasing_date, mrp_date) >= 1 Then
                Trasing_date_label.BackColor = Drawing.Color.Red
                Trasing_date_label.Text = Trasing_date_label.Text + " the information is outdated. Upload actuall tracing info"
            Else
                Trasing_date_label.BackColor = Drawing.Color.LightGreen
                Trasing_date_label.Text = Trasing_date_label.Text + " - Data is Ok"
            End If

            'Так как решение принимается только в среду, определяем день недели и смотрим, какого числа у нас данные MRP
            Dim Today As DateTime = DateTime.Today
            Dim daysUntilTuesday As Integer = (DayOfWeek.Wednesday - Today.DayOfWeek + 7) Mod 7
            Dim nextWednesday As DateTime = Today.AddDays(daysUntilTuesday)
            If DateDiff(DateInterval.Day, nextWednesday, mrp_date) >= 7 Then
                MRP_date_label.BackColor = Drawing.Color.Red
                MRP_date_label.Text = MRP_date_label.Text + " the information is outdated. Exctrac MRP data from SAP"
            Else
                MRP_date_label.BackColor = Drawing.Color.LightGreen
                MRP_date_label.Text = MRP_date_label.Text + " - Data is Ok"
            End If








            'Определяем горизонт дат для отображения соответствующего количества столбцов
            Dim last_date_row As Data.DataRow
                Dim sql As New StringBuilder
                sql.AppendLine("SET DATEFORMAT ymd;")
                sql.AppendLine("if exists (")
                sql.AppendLine("select")
                sql.AppendLine("date_horizont")
                sql.AppendLine("from")
                sql.AppendLine("(")
                sql.AppendLine("select")
                sql.AppendLine("case	")
                sql.AppendLine("	when isdate(ARRIVAL_DATE)=1 then")
                sql.AppendLine("		isnull(cast(ARRIVAL_DATE as date), cast(getdate() as date))")
                sql.AppendLine("	else")
                sql.AppendLine("		cast(getdate() as date)")
                sql.AppendLine("end as date_horizont")
                sql.AppendLine("")
                sql.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] as item")
                sql.AppendLine("where")
                sql.AppendLine("[status] <> 'SERK'")
                sql.AppendLine(") a")
                sql.AppendLine(")")
                sql.AppendLine("BEGIN")
                sql.AppendLine("select")
                sql.AppendLine("max(date_horizont) as date_horizont")
                sql.AppendLine("from")
                sql.AppendLine("(")
                sql.AppendLine("select")
                sql.AppendLine("case	")
                sql.AppendLine("	when isdate(ARRIVAL_DATE)=1 then")
                sql.AppendLine("		isnull(cast(ARRIVAL_DATE as date), cast(getdate() as date))")
                sql.AppendLine("	else")
                sql.AppendLine("		cast(getdate() as date)")
                sql.AppendLine("end as date_horizont")
                sql.AppendLine("")
                sql.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] as item")
                sql.AppendLine("where")
                sql.AppendLine("[status] <> 'SERK'")
                sql.AppendLine(") a")
                sql.AppendLine("end")
                sql.AppendLine("else")
                sql.AppendLine("select cast(getdate() as date) as date_horizont")


                last_date_row = db.GetRow(sql.ToString)

                'Dim last_date As Date = Date.Parse("2019-10-10")
                Dim last_date As Date = Date.Parse(last_date_row.Item("date_horizont"))
                calendar_cells_count = DateDiff(DateInterval.Day, Now, last_date) + 1

                For i As Integer = 0 To calendar_cells_count
                    Dim field As New BoundField
                    field.HeaderText = DateAdd(DateInterval.Day, i, Now).ToShortDateString
                    field.HeaderStyle.Wrap = False

                    If i = 0 Then
                        main_list.Columns.Insert(current_day_cell, field)
                        CY_cell_numer = CY_cell_numer + 1
                        demurrage_cell_number = demurrage_cell_number + 1
                        last_cell = last_cell + 1
                    Else
                        main_list.Columns.Add(field)
                    End If

                Next
                get_data()
            End If




    End Sub

    Private headerLabels As New Hashtable

    Protected Sub main_list_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles main_list.RowDataBound
        Dim i As Integer = 0
        'Заполнение массиа headerLabels, из которого потом будет формироваться атрибут key для каждого элемента
        If e.Row.RowType = DataControlRowType.Header Then
            headerLabels.Clear()
            For Each cell As TableCell In e.Row.Cells
                headerLabels.Add(i, cell.Text)
                i += 1
            Next
        End If


        If e.Row.RowType = DataControlRowType.DataRow Then
            For Each cell As TableCell In e.Row.Cells

                'Если есть авторизация, то нарисовать кнопки "удалить", "заказ ДО", "отправить на ODCY"
                If auth_flag Then
                    If DirectCast(DirectCast(cell, System.Web.UI.WebControls.DataControlFieldCell).ContainingField, System.Web.UI.WebControls.BoundField).HeaderText = "Row action" Then
                        'Кнопка "удалить"
                        Dim img_del As New ImageButton With {.ImageUrl = "../images/Buttons/24/Trash_red.png", .Width = 30, .Height = 30, .ImageAlign = ImageAlign.AbsMiddle, .CssClass = "img_but"}
                        img_del.CommandArgument = e.Row.Cells.Item(record_id).Text
                        e.Row.Cells.Item(row_action_cell).Controls.Add(img_del)
                        AddHandler img_del.Click, AddressOf delete_row

                        'Кнопка Доставить
                        Dim img_do As New Image With {.ImageUrl = "../images/demurrage/DO.png", .Width = 30, .Height = 30, .ImageAlign = ImageAlign.AbsMiddle, .CssClass = "calendar"}
                        img_do.Attributes.Add("key", e.Row.Cells(record_id).Text & "|" & "DO_date")
                        e.Row.Cells.Item(row_action_cell).Controls.Add(img_do)
                        cell.Attributes.Add("key", e.Row.Cells(record_id).Text & "|" & "row_action")

                        e.Row.Cells.Item(row_action_cell).Attributes.CssStyle.Add(HtmlTextWriterStyle.TextAlign, "left")


                    End If

                    'Добавление tooltip для контейнеров
                    If DirectCast(DirectCast(cell, System.Web.UI.WebControls.DataControlFieldCell).ContainingField, System.Web.UI.WebControls.BoundField).HeaderText = "Container" Then
                        'e.Row.Cells.Item(container_cell_number).Text = "Test"
                        'Переменная для хранения даблицы с детальной информацие по контейнеру
                        Dim ContainerDetails As DataTable = Nothing
                        'Переменная для хранения текущего номера контейнера
                        Dim current_container As String = e.Row.Cells.Item(container_cell_number).Text
                        ContainerDetails = GetContainerDetails(current_container)
                        Dim debug As Integer = 0

                        Try

                            Dim toolTip As String = PrepareTooltip(ContainerDetails)
                            If toolTip <> "" Then
                                'e.Row.Cells.Item(container_cell_number).Text.Replace("@TEMPLATE", toolTip)


                                e.Row.Cells.Item(container_cell_number).Text = " <div data-toggle=""tooltip"" data-placement=""auto"" data-html=""true"" data-original-title=""" & toolTip & """ Style = ""cursor: pointer;"" >"
                                e.Row.Cells.Item(container_cell_number).Text = e.Row.Cells.Item(container_cell_number).Text + current_container
                                e.Row.Cells.Item(container_cell_number).Text = e.Row.Cells.Item(container_cell_number).Text + "</div>"

                            End If
                        Catch ex As Exception

                        End Try
                    End If



                    'Если в поле ODCY есть указание на контейнер ярд, то можно при желании переместить контейнер туда. Для этого надо отрисовать соответствующую кнопку
                    If DirectCast(DirectCast(cell, System.Web.UI.WebControls.DataControlFieldCell).ContainingField, System.Web.UI.WebControls.BoundField).HeaderText = "ODCY" And (e.Row.Cells.Item(status_cell_number).Text = "Not CC" Or e.Row.Cells.Item(status_cell_number).Text = "CC finished") Then
                        If cell.Text <> "" And cell.Text <> "&nbsp;" Then
                            'Кнопка переместить на ODCY
                            Dim img As New ImageButton With {.ImageUrl = "../images/demurrage/pending.png", .Width = 30, .Height = 30, .ImageAlign = ImageAlign.AbsMiddle, .CssClass = "img_but"}
                            img.CommandArgument = e.Row.Cells.Item(record_id).Text
                            AddHandler img.Click, AddressOf Send_to_ODCY

                            e.Row.Cells.Item(row_action_cell).Controls.Add(img)
                            e.Row.Cells.Item(row_action_cell).Attributes.CssStyle.Add(HtmlTextWriterStyle.TextAlign, "center")
                        End If
                    End If
                End If


                'Расчёт CY и Demurrage
                If DirectCast(DirectCast(cell, System.Web.UI.WebControls.DataControlFieldCell).ContainingField, System.Web.UI.WebControls.BoundField).HeaderText = "Condition" And e.Row.Cells.Item(status_cell_number).Text <> "in transit" And e.Row.Cells.Item(status_cell_number).Text <> "N/A" And e.Row.Cells.Item(Arrival_date_cell_number).Text <> "&nbsp;" Then
                    If cell.Text <> "" And cell.Text <> "&nbsp;" Then


                        Select Case e.Row.Cells.Item(odcy_flag).Text
                            Case "Y"
                                'Если контейнер на ODCY
                                Dim cy As Integer
                                cy = Convert.ToInt32(Regex.Match(cell.Text, "(?<=\/)[0-9]+").ToString)
                                Dim days As Integer = DateDiff(DateInterval.Day, Date.Parse(e.Row.Cells.Item(Arrival_date_cell_number).Text), Now) + 1
                                'Если укладываемся в условия беспатного хранения, то считаем cy
                                If days <= cy And days > 0 Then
                                    e.Row.Cells.Item(CY_cell_numer).Text = days.ToString
                                End If
                                'Если уже просрочено, то считаем cy и demurrage
                                If days > cy Then
                                    e.Row.Cells.Item(CY_cell_numer).Text = cy
                                    e.Row.Cells.Item(demurrage_cell_number).Text = days - cy
                                End If

                            Case "&nbsp;", "N"
                                'Если контейнер не на ODCY
                                Dim cy As Integer
                                cy = Convert.ToInt32(Regex.Match(cell.Text, "[0-9]+").ToString)
                                Dim days As Integer = DateDiff(DateInterval.Day, Date.Parse(e.Row.Cells.Item(Arrival_date_cell_number).Text), Now) + 1
                                'Если укладываемся в условия беспатного хранения, то считаем cy
                                If days <= cy And days > 0 Then
                                    e.Row.Cells.Item(CY_cell_numer).Text = days.ToString
                                End If
                                'Если уже просрочено, то считаем cy и demurrage
                                If days > cy Then
                                    e.Row.Cells.Item(CY_cell_numer).Text = cy
                                    e.Row.Cells.Item(demurrage_cell_number).Text = days - cy
                                End If

                        End Select

                    End If
                End If





                'Выделение цветом контейнера, если его можно отправить поездом
                If DirectCast(DirectCast(cell, System.Web.UI.WebControls.DataControlFieldCell).ContainingField, System.Web.UI.WebControls.BoundField).HeaderText = "Container" And e.Row.Cells.Item(train_flag).Text = "X" Then
                    e.Row.BackColor = Drawing.Color.Olive
                End If

                'Заполнение колонок с датами символами тразита, если контейнер в транзите или DO, если есть DO
                If e.Row.Cells.GetCellIndex(cell) >= last_cell Or e.Row.Cells.GetCellIndex(cell) = current_day_cell Then
                    'Если контейнер в транзите
                    If e.Row.Cells.Item(status_cell_number).Text = "in transit" Then
                        Dim days As Integer = DateDiff(DateInterval.Day,
                                                   Date.Parse(DirectCast(DirectCast(cell, System.Web.UI.WebControls.DataControlFieldCell).ContainingField, System.Web.UI.WebControls.BoundField).HeaderText),
                                                   Date.Parse(e.Row.Cells.Item(Arrival_date_cell_number).Text)
                                                   )
                        If days > 0 Then
                            cell.Text = transit_palceholder
                            cell.HorizontalAlign = HorizontalAlign.Center
                        ElseIf days = 0 Then
                            cell.Text = e.Row.Cells.Item(eta_placeholder_cell).Text
                            cell.HorizontalAlign = HorizontalAlign.Center
                            cell.CssClass = "eta"
                        End If
                    End If

                    'Если есть DO на контейнер
                    If e.Row.Cells.Item(do_date).Text <> "&nbsp;" Then

                        e.Row.Cells.Item(status_cell_number).ToolTip = e.Row.Cells.Item(do_date).Text

                        Dim days As Integer = DateDiff(DateInterval.Day,
                                                   Date.Parse(DirectCast(DirectCast(cell, System.Web.UI.WebControls.DataControlFieldCell).ContainingField, System.Web.UI.WebControls.BoundField).HeaderText),
                                                   Date.Parse(e.Row.Cells.Item(do_date).Text)
                                                   )
                        If days = 0 Then
                            cell.Text = do_palceholder
                            cell.HorizontalAlign = HorizontalAlign.Center
                            cell.CssClass = "eta"
                        End If

                    End If
                End If

                'Присвоение классов для различных типов полей. От класса зависит вид окна для редактирования этого поля
                Select Case auth_flag
                    Case True

                        Select Case i
                            Case container_cell_number, cargo_cell_number, condition_cell_number
                                cell.CssClass = "text"
                            Case Destination_cell_number, provider_cell_numbet
                                cell.CssClass = "droplist"
                            Case Arrival_date_cell_number
                                cell.CssClass = "calendar"
                            Case status_cell_number
                                cell.CssClass = "status"
                        End Select


                    Case False
                        Select Case i
                            Case container_cell_number, cargo_cell_number, condition_cell_number
                                cell.CssClass = "text_no_auth"
                            Case Destination_cell_number, provider_cell_numbet
                                cell.CssClass = "droplist_no_auth"
                            Case Arrival_date_cell_number
                                cell.CssClass = "calendar_no_auth"
                            Case status_cell_number
                                cell.CssClass = "status_no_auth"
                        End Select
                End Select
                'Присвоение атрибута key для каждого элемента таблицы
                If i <> 0 Then
                    cell.Attributes.Add("key", e.Row.Cells(record_id).Text & "|" & headerLabels(i))
                End If
                i += 1


            Next
        End If
    End Sub



    Private Sub Send_to_ODCY(ByVal sender As Object, ByVal e As ImageClickEventArgs)
        'main_list.SelectedIndex = sender.commandargument
        db.NonQuery("update [UserModules].[user].[ITEM_INFORMATION] Set carrier_id = (Select top 1 odcy_id from [UserModules].[user].[DESTINATION_MASTER] where id =carrier_id) where id = " & sender.commandargument)
        'get_data()
        refresh()
    End Sub



    Private Sub delete_row(ByVal sender As Object, ByVal e As ImageClickEventArgs)
        'main_list.SelectedIndex = sender.commandargument
        db.NonQuery("delete [UserModules].[user].[ITEM_INFORMATION]  where id = " & sender.commandargument)
        'get_data()
        refresh()
    End Sub



    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete
        Dim i As Integer = 0
        Dim row1 = New GridViewRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal)

        If main_list.Rows.Count > 0 Then




            Dim c0 As New TableCell With {.Text = ""}
            row1.Cells.Add(c0)
            row1.Cells(0).HorizontalAlign = HorizontalAlign.Center



            Dim c1 As New TableCell With {.Text = "Division"}
            row1.Cells.Add(c1)
            row1.Cells(1).ColumnSpan = 2
            row1.Cells(1).HorizontalAlign = HorizontalAlign.Center


            Dim c2 As New TableCell With {.Text = "Container info"}
            row1.Cells.Add(c2)
            row1.Cells(2).ColumnSpan = 9
            row1.Cells(2).HorizontalAlign = HorizontalAlign.Center
            CType(main_list.Controls(0), Table).Rows.AddAt(0, row1)



            Dim c3 As New TableCell With {.Text = "Calendar"}
            row1.Cells.Add(c3)
            If calendar_cells_count > 0 Then
                row1.Cells(3).ColumnSpan = calendar_cells_count + 1
            End If
            row1.Cells(3).HorizontalAlign = HorizontalAlign.Center
            CType(main_list.Controls(0), Table).Rows.AddAt(0, row1)



            main_list.Columns(record_id).Visible = False
            main_list.Columns(odcy_id).Visible = False
            main_list.Columns(odcy_flag).Visible = False
            main_list.Columns(eta_placeholder_cell).Visible = False
            main_list.Columns(train_flag).Visible = False
        End If


        If isExcelDownload Then
            ExcelDownloadSub(main_list, "[Container demurrage]" & DateTime.Now.ToString)
        End If
    End Sub

    Private isExcelDownload As Boolean = False


    Protected Sub ExcelDownload_Click(sender As Object, e As ImageClickEventArgs) Handles ExcelDownload.Click
        isExcelDownload = 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 StringWriter = New StringWriter(myCItrad)
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

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

        Response.End()
    End Sub



    Protected Sub ibUpload_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ibUpload.Click
        Dim iTotalLines As Integer
        Dim iInsertedLines As Integer

        Dim dtStart As DateTime = DateTime.Now

        UploadCSV(txtDataToUpload.Text, iTotalLines, iInsertedLines)

        ltrUploadResults.Text = "Duration (sec): " & DateTime.Now.Subtract(dtStart).Seconds

        txtDataToUpload.Text = ""


    End Sub



    Public Sub UploadCSV(text As String, Optional ByRef TotalLines As Integer = 0, Optional ByRef ProcessedLine As Integer = 0)
        Dim lines() As String = text.Split(Chr(10))
        TotalLines = lines.Count
        Dim com As New SqlCommand
        ProcessedLine = 0
        Dim sql As New StringBuilder
        Dim i As Integer = 0


        Dim provider As Integer = 0
        Dim CONTAINER_COLUMN_NUMBER As Integer = 2
        Dim CONDITION_COLUMN_NUMBER_CY As Integer = 3
        Dim CONDITION_COLUMN_NUMBER_DE As Integer = 3
        Dim cargo_column_number As Integer = 4
        Dim line_column_number As Integer = 5
        Dim port_column_number As Integer = 6
        Dim ARRIVAL_DATE_COLUMN_NUMBER As Integer = 7
        Dim DO_DATE_COLUMN_NUMBER As Integer = 8
        Dim status_column_number As Integer = 9





        Dim current_destination As String = ""
        Dim current_division As String = ""
        Dim dt As Data.DataTable = Nothing
        Dim sql_destination_master As String = "select id, DESTINATION,PROVIDER,ODCY_ID,IS_ODCY from [UserModules].[user].[DESTINATION_MASTER]"

        dt = db.GetData(sql_destination_master)
        Dim foundRows() As Data.DataRow



        If lines.Count > 0 Then
            sql.Clear()
            sql.AppendLine("delete [UserModules].[user].[ITEM_INFORMATION]")
            db.NonQuery(sql.ToString)
        End If


        For Each line As String In lines
            If line <> "" Then
                Dim vals() As String = line.Split(vbTab)
                If vals.Length > 0 Then
                    If vals(Destination_parse_col).Length > 0 Then
                        current_destination = vals(Destination_parse_col)
                        'Response.Write("<script>console.log('" & current_destination & " - " & current_division & "')</script>")
                    End If
                    If vals(Division_parse_col).Length > 0 Then
                        current_division = vals(Division_parse_col)
                        'Response.Write("<script>console.log('" & current_destination & " - " & current_division & "')</script>")
                    End If

                    If current_destination <> "" And current_division <> "" Then
                        foundRows = dt.Select("DESTINATION = '" & current_destination & "' and PROVIDER = '" & current_division & "'")


                        If foundRows.Length > 0 Then
                            'Response.Write("<script>console.log('Перевозчик зарегистрирован в базе')</script>")


                            Integer.TryParse(foundRows(0).ItemArray(0).ToString, provider)



                            Dim condition_regex_cy As String = Nothing
                            condition_regex_cy = Regex.Replace(vals(CONDITION_COLUMN_NUMBER_CY), "[^0-9\/]", "")
                            Dim condition_regex_de As String = Nothing
                            condition_regex_de = Regex.Replace(vals(CONDITION_COLUMN_NUMBER_DE), "[^0-9\/]", "")
                            Dim condition_final As String = Nothing

                            If CONDITION_COLUMN_NUMBER_CY <> CONDITION_COLUMN_NUMBER_DE Then
                                condition_final = condition_regex_cy + "/" + condition_regex_de
                            Else
                                condition_final = condition_regex_cy
                            End If


                            Dim date_parsed As Date
                            Dim arrival_parsed_string As String = ""
                            Dim do_parsed_string As String = ""



                            'If Date.TryParse(vals(ARRIVAL_DATE_COLUMN_NUMBER), date_parsed) = True Then
                            If Date.TryParse(vals(ARRIVAL_DATE_COLUMN_NUMBER), CultureInfo.CreateSpecificCulture("ru-RU"), DateTimeStyles.AssumeLocal, date_parsed) = True Then
                                arrival_parsed_string = date_parsed.Year & "-" & Right("0" & date_parsed.Month, 2) & "-" & Right("0" & date_parsed.Day, 2)
                            End If

                            'If Date.TryParse(vals(DO_DATE_COLUMN_NUMBER), date_parsed) = True Then
                            If Date.TryParse(vals(DO_DATE_COLUMN_NUMBER), CultureInfo.CreateSpecificCulture("ru-RU"), DateTimeStyles.AssumeLocal, date_parsed) = True Then
                                do_parsed_string = date_parsed.Year & "-" & Right("0" & date_parsed.Month, 2) & "-" & Right("0" & date_parsed.Day, 2)
                            End If


                            'Response.Write("<script>console.log('" & date_parsed & "')</script>")

                            sql.Clear()
                            sql.AppendLine("SET DATEFORMAT dmy;")
                            sql.AppendLine("declare @tmp as table (carrier_id nvarchar(max), container nvarchar(max), cargo nvarchar(max), condition nvarchar(max), arrival_date nvarchar(max), do_date nvarchar(max), [status] nvarchar(max), [line] nvarchar(max), [port] nvarchar(max))")
                            sql.AppendLine("insert into @tmp (carrier_id, container, cargo, arrival_date, do_date, condition, [status], [line], [port]) values ('" & provider & "','" & vals(CONTAINER_COLUMN_NUMBER) & "', '" & vals(cargo_column_number) & "', '" & arrival_parsed_string & "', '" & do_parsed_string & "', '" & condition_final & "', '" & vals(status_column_number) & "', '" & vals(line_column_number) & "', '" & vals(port_column_number) & "' ) ")

                            sql.AppendLine(" ")
                            sql.AppendLine("merge [usermodules].[user].[item_information] as trg ")
                            sql.AppendLine("using @tmp as src ")
                            sql.AppendLine("on trg.container collate Cyrillic_General_CI_AS = src.container collate Cyrillic_General_CI_AS ")
                            sql.AppendLine("when matched then ")
                            sql.AppendLine("	update ")
                            sql.AppendLine("	set trg.arrival_date = src.arrival_date")
                            sql.AppendLine("	,trg.do_date = src.do_date")
                            sql.AppendLine("	,trg.condition = src.condition ")
                            sql.AppendLine("	,trg.[status] = src.[status] ")
                            sql.AppendLine("	,trg.[line] = src.[line] ")
                            sql.AppendLine("	,trg.[port] = src.[port] ")
                            sql.AppendLine("	,trg.[create_date] = cast(getdate() as datetime) ")

                            sql.AppendLine("when Not matched then ")
                            sql.AppendLine("insert ")
                            sql.AppendLine("(carrier_id, container, cargo, arrival_date, do_date, condition, [status], [line], [port], create_date) ")
                            sql.AppendLine("values (carrier_id, container, cargo, arrival_date, do_date, condition, [status], [line], [port], cast(getdate() as datetime)) ")
                            sql.AppendLine("; ")

                            db.NonQuery(sql.ToString)



                        Else
                            Response.Write("<script>console.log('Такой перевозчик не найден в базе')</script>")
                        End If

                    End If


                End If
            End If
        Next
        refresh()
    End Sub


    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    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,
 Optional TTL_In_Track As Integer = 0) As String
        Dim result As String = ""

        Dim gv As New GridView
        gv.ShowFooter = showfooter


        AddHandler gv.RowDataBound, AddressOf gv_RowDataBound

        gv.DataSource = dt
        gv.DataBind()

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

        If gv.Rows.Count > 0 Or TTL_In_Track > 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.RenderControl(hw)
            result = sw.ToString.Replace("""", "'")

        End If
        gv.Dispose()
        Return result
    End Function


    Protected Sub gv_RowDataBound(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then

        End If
    End Sub



    Private Function GetContainerDetails(container As String) As DataTable
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        Return sqlf.GetData("select CREATE_DATE as [Entry date], CONTAINER as Container, [type] as [Type], cargo as Cargo, material as Material, qty as Qty from [UserModules].[user].[CONTAINER_DETAILS] where container = '" & container & "'")
    End Function



    Protected Sub Train_filter_Click(sender As Object, e As ImageClickEventArgs) Handles Train_filter.Click
        filter_train_flag = Not filter_train_flag
        If filter_train_flag Then
            refresh("?train_filter=true")
        Else
            refresh()
        End If

    End Sub
    Protected Sub Prediction_Click(sender As Object, e As ImageClickEventArgs) Handles Prediction.Click

        Dim sql As New StringBuilder
        sql.AppendLine("set nocount on")
        sql.AppendLine("delete [usermodules].[user].[train_suggestion]")
        sql.AppendLine("declare @container as varchar(max)")
        sql.AppendLine("declare @item_info_tmp as table (container varchar(max))")
        sql.AppendLine("declare @mrp_tmp as table (material varchar(max), stock_balance varchar(max))")
        sql.AppendLine("insert into @mrp_tmp")
        sql.AppendLine("select material, col_10")
        sql.AppendLine("from [UserModules].[user].[MRP_FOR_TRAIN]")
        sql.AppendLine("where MRP_ELEMENT = 'Balance (Stock)'")
        sql.AppendLine("insert into @item_info_tmp")
        sql.AppendLine("select distinct")
        sql.AppendLine("CONTAINER_DETAILS.CONTAINER")
        sql.AppendLine("from [UserModules].[user].[ITEM_INFORMATION]")
        sql.AppendLine("left join [UserModules].[user].[DESTINATION_MASTER]")
        sql.AppendLine("on [ITEM_INFORMATION].CARRIER_ID = [DESTINATION_MASTER].id")
        sql.AppendLine("left join [UserModules].[user].[CONTAINER_DETAILS]")
        sql.AppendLine("on ITEM_INFORMATION.CONTAINER = CONTAINER_DETAILS.CONTAINER")
        sql.AppendLine("where material is not null")
        sql.AppendLine("and [DESTINATION_MASTER].[id] = 4")
        sql.AppendLine("and [ITEM_INFORMATION].[line] = 'Maersk'")
        sql.AppendLine("and [ITEM_INFORMATION].[port] = 'FCT'")
        sql.AppendLine("and [ITEM_INFORMATION].[status] = 'CC finished'")
        sql.AppendLine("and ([ITEM_INFORMATION].[do_date] is null")
        sql.AppendLine("or [ITEM_INFORMATION].[do_date] = '')")
        sql.AppendLine("order by container")
        sql.AppendLine("DECLARE container_CURSOR CURSOR FOR")
        sql.AppendLine("(")
        sql.AppendLine("select container from @item_info_tmp")
        sql.AppendLine(")")
        sql.AppendLine("OPEN container_CURSOR")
        sql.AppendLine("FETCH NEXT FROM container_CURSOR INTO @container")
        sql.AppendLine("WHILE @@FETCH_STATUS = 0")
        sql.AppendLine("BEGIN")
        sql.AppendLine("if exists (")
        sql.AppendLine("select MRP.material, stock_balance from @mrp_tmp as MRP")
        sql.AppendLine("left join [UserModules].[user].[CONTAINER_DETAILS]")
        sql.AppendLine("on MRP.material collate Cyrillic_General_CI_AI = CONTAINER_DETAILS.MATERIAL collate Cyrillic_General_CI_AI")
        sql.AppendLine("where CONTAINER = @container")
        sql.AppendLine("and case when isnumeric(MRP.stock_balance) = 1	")
        sql.AppendLine("then cast(MRP.stock_balance as numeric(20,3))")
        sql.AppendLine("else 0")
        sql.AppendLine("end < 0")
        sql.AppendLine(")")
        sql.AppendLine("begin")
        sql.AppendLine("with update_stock as(")
        sql.AppendLine("select mrp.material")
        sql.AppendLine(", mrp.stock_balance")
        sql.AppendLine(", details.QTY")
        sql.AppendLine(",case when isnumeric(MRP.stock_balance) = 1")
        sql.AppendLine("then cast (mrp.stock_balance as numeric (20,3)) + cast(replace(replace(replace(rtrim(ltrim(isnull(qty,0))),' ',''),char(13),''),char(10),'') as numeric(20,3))")
        sql.AppendLine("else 0")
        sql.AppendLine("end as new_stock_balance")
        sql.AppendLine("from @mrp_tmp as mrp")
        sql.AppendLine("left join [UserModules].[user].[CONTAINER_DETAILS] as details")
        sql.AppendLine("on mrp.material collate Cyrillic_General_CI_AI = DETAILS.MATERIAL collate Cyrillic_General_CI_AI")
        sql.AppendLine("where")
        sql.AppendLine("details.CONTAINER = @container")
        sql.AppendLine(")")
        sql.AppendLine("update update_stock set stock_balance = new_stock_balance")
        sql.AppendLine("end")
        sql.AppendLine("else")
        sql.AppendLine("begin")
        sql.AppendLine("insert into [usermodules].[user].[train_suggestion]")
        sql.AppendLine("(create_date, container)")
        sql.AppendLine("values (cast (getdate() as datetime), @container)")
        sql.AppendLine("end")
        sql.AppendLine("FETCH NEXT FROM container_CURSOR INTO @container")
        sql.AppendLine("END")
        sql.AppendLine("CLOSE container_CURSOR")
        sql.AppendLine("DEALLOCATE container_CURSOR")

        db.NonQuery(sql.ToString)
        refresh()

    End Sub
    Protected Sub ImageSAP_Click(sender As Object, e As ImageClickEventArgs) Handles ImageSAP.Click

    End Sub

End Class