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

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

Partial Class Xml_viewTable
    Inherits System.Web.UI.Page

    Private sqlf As New sharedClasses.dbFunctions.SqlFunctions
    Private a As sharedClasses.Auth.auth
    Public dt As Data.DataTable
    Public dr As DataRowCollection
    Public dt_plan As Data.DataTable
    Public dr_plan As DataRowCollection
    Dim sql As New StringBuilder '

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

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

        Dim userid As String = ""
        If Not IsNothing(Request.QueryString("OrchUserLogin")) Then
            userid = Request.QueryString("OrchUserLogin")
        End If

        If Not IsNothing(Request.QueryString("u")) Then
            userid = Request.QueryString("u")
        End If

        If userid = "" Then
            authorize("SSO")

            userid = a.userId
            edTableToView.Message = "By SSO: " & a.userId
        End If

        Dim tableId As String = "1185"
        Dim viewId As String = ""
        'If Not IsNothing(Request.QueryString("tableId")) Then
        '    tableId = Request.QueryString("tableId")
        'End If
        'If Not IsNothing(Request.QueryString("t")) Then
        '    tableId = Request.QueryString("t")
        'End If

        'If Not IsNothing(Request.QueryString("viewid")) Then
        '    viewId = Request.QueryString("viewid")
        'End If
        'If Not IsNothing(Request.QueryString("v")) Then
        '    viewId = Request.QueryString("v")
        'End If

        'If Not IsNothing(Request.QueryString("debug")) Then
        '    edTableToView.debugMode = (Request.QueryString("debug") = "true")
        'End If

        If Not IsPostBack Then
            If Not IsNothing(Request.QueryString("p")) Then
                If IsNumeric(Request.QueryString("p")) Then
                    edTableToView.PageSize = Request.QueryString("p")
                End If
            End If
        End If

        edTableToView.IsReadOnly = True
        edTableToView.UserName = userid.ToUpper

        If Not IsNothing(Request.QueryString("viewonly")) Then
            If Request.QueryString("viewonly").ToString.ToUpper = "YES" Then
                edTableToView.UserName = ""
            End If
        End If

        Try
            If Not IsNothing(Request.QueryString("mailscheduleid")) Then
                Try
                    Dim ScheduleParams As DataRow

                    ScheduleParams = sqlf.GetData("SELECT * FROM UserModules.dbo.Mail_Schedule WHERE id = " & Request.QueryString("mailscheduleid") & " and enabled='Y'").Rows(0)

                    tableId = ScheduleParams("table_id")

                    edTableToView.TableId = tableId
                    graphForTable.TableId = tableId

                    Dim mailRecepients As DataTable = sqlf.GetData("SELECT * from UserModules.dbo.Mail_Groups WHERE MAIL_GROUP_NAME = '" & ScheduleParams("MAIL_GROUP") & "' and enabled = 'Y'")
                    If mailRecepients.Rows.Count > 0 Then
                        UpdteMessageSendLog(Request.QueryString("mailscheduleid"), "OK", "START")
                        For Each dr As DataRow In mailRecepients.Rows
                            If dr("single_id") <> "" Then
                                'Dim UserTableName As String = (New SqlFunctions).GetScalar("SELECT tablename FROM UserModules.dbo.ModuleTables where id = " & tableId)
                                Dim messageBody As String = ScheduleParams("body_text") & "<hr />"
                                edTableToView.AuthorityCheck()
                                edTableToView.BuildColumnsConfiguration()
                                Select Case ScheduleParams("message_Type")
                                    Case "Graph"
                                        messageBody &= graphForTable.GetHTMLGraph
                                    Case "Table"
                                        messageBody &= edTableToView.GetHTMLGrid
                                    Case "Table & Graph"
                                        messageBody &= edTableToView.GetHTMLGrid
                                        messageBody &= "<hr />"
                                        messageBody &= graphForTable.GetHTMLGraph
                                End Select
                                CommonFunctions.SendEmail(dr("single_id"), ScheduleParams("subject_text"), messageBody)
                                UpdteMessageSendLog(Request.QueryString("mailscheduleid"), "OK", dr("single_id"))
                            End If
                        Next
                        UpdteMessageSendLog(Request.QueryString("mailscheduleid"), "OK", "FINISH")
                    End If
                Catch ex As Exception

                End Try
                Response.Write("Message send.")
                Response.End()
            End If

            If IsNumeric(tableId) Then
                edTableToView.TableId = tableId
                graphForTable.TableId = tableId
            Else
                edTableToView.TableId = -1
                graphForTable.TableId = -1
            End If
            If IsNumeric(viewId) Then
                edTableToView.ViewID = viewId
            Else
                edTableToView.ViewID = -1
            End If


            graphForTable.SelectCondition = edTableToView.SelectConditionByFilters

        Catch ex As Exception
        End Try

    End Sub

    Private Sub UpdteMessageSendLog(mailScheduleId As Integer, result As String, message As String)
        Dim sql As String = "INSERT Into UserModules.DBO.MAIL_SEND_LOG (MAIL_SCHEDULE_ID, LAST_RUN, RESULT,[MESSAGE]) VALUES (" & mailScheduleId & ",'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm") & "','OK','" & message & "')"
        sqlf.NonQuery(sql)
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub filterChanged() Handles edTableToView.GridFilterChanged

    End Sub

    Private Sub DataSelected(dt As DataTable) Handles edTableToView.DataSelected

    End Sub
    Protected Sub Page_PreRender(sender As Object, e As System.EventArgs) Handles Me.PreRender
        'ClientScript.RegisterStartupScript(Me.GetType(), "CreateGridHeader", "<script>CreateGridHeader('DataDiv', 'editableGrid1_GridView1', 'HeaderDiv');</script>")

        graphForTable.SelectCondition = edTableToView.SelectConditionByFilters

        graphForTable.Visible = graphForTable.DrawGraph()

        If (Request.QueryString("tableId") IsNot Nothing) And graphForTable.Visible Then
            lnkGraphView.Visible = True
            lnkGraphView.Text = "View graph in a new window"
            lnkGraphView.NavigateUrl = "~/viewgraph.aspx?tableid=" & Request.QueryString("tableId")
        Else
            lnkGraphView.Visible = False
        End If

        If edTableToView.IsReadOnly Then
            edTableToView.CollectHeaderColumn(1)
        End If
        'Title = edTableToView.TableDescription
        Title = "Demmurage Monitoring"


        'If edTableToView.copyFlag Then
        '    edTableToView.AddFormMode(edTableToView.copyId)
        'End If

        edTableToView.SubReport.Controls.Add(createSubreport(edTableToView.TableId))




        Dim script As String = sqlf.GetScalar("SELECT Gridcommand_handler FROM UserModules.dbo.ModuleTables WHERE id=" & edTableToView.TableId)
        If script <> "" Then
            gridcommandhandlerscript.Controls.Add(New Literal With {.Text = "<script type=""text/javascript"">" & script & "</script>"})
        End If
        Dim refresh_flag As String = sqlf.GetScalar("SELECT refresh_after_event FROM UserModules.dbo.ModuleTables WHERE id=" & edTableToView.TableId)
        If refresh_flag <> "" Then
            edTableToView.refresh_after_event = refresh_flag
            refresh_after_event.Value = refresh_flag
        End If
    End Sub

    Private Function createSubreport(tid As String) As GridView

        Dim gv As New GridView

        Dim query As String = sqlf.GetScalar("SELECT isnull(SUBREPORT_QUERY,'') from usermodules.dbo.moduletables where id = " & tid)
        If query <> "" And Not (query.Contains("update") Or
                               query.Contains("delete") Or
                               query.Contains("exec") Or
                               query.Contains("truncate") Or
                               query.Contains("drop")) Then
            gv.DataSource = sqlf.GetData(query)
            Try
                gv.DataBind()
            Catch ex As Exception
                Return Nothing
            End Try
        End If
        Return gv
    End Function

    Protected Sub UpdateUserInfo(ByRef com As SqlCommand) Handles edTableToView.NewRecordSaving
        Try
            com.Parameters("@CHANGED_BY").Value = a.userId ' auth Request.QueryString("orchuserlogin")
        Catch ex As Exception

        End Try
    End Sub

    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete
        edTableToView.CreateGroupingText()
    End Sub

    Protected Sub GetGraphData(ByRef graphData As String) Handles edTableToView.RequestForGraphData
        graphData = graphForTable.GetHTMLGraph
    End Sub
    Private Sub RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) Handles edTableToView.RowCommand
        If e.CommandName = "REBUILD" Then

            Dim SelectedRow As String = e.CommandArgument


            Dim sql As New StringBuilder
            sql.Clear()
            sql.AppendLine("declare @id as integer ")
            sql.AppendLine("set @id = " + SelectedRow + " ")
            sql.AppendLine("declare @car_no as nvarchar(max) ")
            sql.AppendLine("declare @date as nvarchar(max) ")
            sql.AppendLine("set @car_no = (select top 1 CAR_No from [UserModules].[user].[DEMURAGE_MONITORING] where id = @id) ")
            sql.AppendLine("set @date = (select top 1 cast(YARD_IN as date) from [UserModules].[user].[DEMURAGE_MONITORING] where id = @id) ")
            sql.AppendLine(" ")
            sql.AppendLine("declare @tmp table (car_no nvarchar(max), trailer_no1 nvarchar(max), yard_gate_check_in_time datetime, yard_gate_check_out_time datetime, front_gate_check_in_time datetime, rear_gate_check_out_time datetime) ")
            sql.AppendLine("declare @queue as nvarchar(max) ")
            sql.AppendLine("set @queue = ' ")
            sql.AppendLine("select car_no, trailer_no1, yard_gate_check_in_time, yard_gate_check_out_time, front_gate_check_in_time, rear_gate_check_out_time ")
            sql.AppendLine("from openquery(ORCHESTRA2, ''select * from portal.twp_gate_yard where car_no = '''''+@car_no +''''''') ")
            sql.AppendLine("where cast(yard_gate_check_in_time as date) = cast('''+@date+''' as date) ")
            sql.AppendLine("' ")
            sql.AppendLine("insert into @tmp exec(@queue) ")
            sql.AppendLine("UPDATE ")
            sql.AppendLine("[UserModules].[user].[DEMURAGE_MONITORING] ")
            sql.AppendLine("SET ")
            sql.AppendLine("TRAILER_No1 = (select top 1 trailer_no1 from @tmp where CAR_No= car_no ) ")
            sql.AppendLine(",YARD_IN = (select top 1 yard_gate_check_in_time from @tmp where CAR_No= car_no ) ")
            sql.AppendLine(", GATE_IN_DATE = (select top 1 front_gate_check_in_time from @tmp where CAR_No= car_no ) ")
            sql.AppendLine(", GATE_OUT_DATE = (select top 1 rear_gate_check_out_time from @tmp where CAR_No= car_no ) ")
            sql.AppendLine(",FREE_HOURS_OF_LOADING = (select top 1 loading from [UserModules].[user].[DEMURRAGE_MASTER] dm where dm.DESTINATION = [UserModules].[user].[DEMURAGE_MONITORING].DESTINATION) ")
            sql.AppendLine(",FREE_HOURS_OF_UNLOADING = (select top 1 UNLOADING from [UserModules].[user].[DEMURRAGE_MASTER] dm where dm.DESTINATION = [UserModules].[user].[DEMURAGE_MONITORING].DESTINATION ) ")
            sql.AppendLine(",DELAY_HOURS = datediff (hour, cast(BOOKING_DATE + ' ' + BOOKING_TIME as datetime) , cast((select top 1 yard_gate_check_in_time from @tmp where CAR_No= car_no ) as datetime)) ")
            sql.AppendLine(",DEMURRAGE_ON_LOADING = datediff (hour, cast((select top 1 front_gate_check_in_time   from @tmp where CAR_No= car_no ) as datetime), cast(EXPORT_CC_FINISH  + ' ' + EXPORT_CC_FINISH_TIME as datetime)) -  (select top 1 cast(loading as integer) from [UserModules].[user].[DEMURRAGE_MASTER] dm where dm.DESTINATION = [UserModules].[user].[DEMURAGE_MONITORING].DESTINATION)")
            sql.AppendLine(",DEMURRAGE_ON_UNLOADING = datediff (hour, cast(UNLOADING_FINISH + ' ' + UNLOADING_FINISH_TIME as datetime) , cast(IMPORT_CC_START + ' ' + IMPORT_CC_START_TIME as datetime)) - (select top 1 UNLOADING from [UserModules].[user].[DEMURRAGE_MASTER] dm where dm.DESTINATION = [UserModules].[user].[DEMURAGE_MONITORING].DESTINATION ) ")
            sql.AppendLine("where id = @id ")


            Dim result As Integer

            result = sqlf.NonQuery(sql.ToString)



            edTableToView.RefreshData()
        End If
    End Sub
End Class