ModuleMaster/mm/incoming_monitor.aspx.vb - main - ivan/ModMas

ModuleMaster/mm/incoming_monitor.aspx.vb
251 строка10 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.IO
Imports CommonFunctions
Partial Class r
    Inherits System.Web.UI.Page

    Private sqlf As New sharedClasses.dbFunctions.SqlFunctions
    Private Sql As String = ""
    Private paramsDescription As String = ""
    Private sqlId As String = ""
    Private filters As New Collection
    Private params As New Collection

    Private ExcelDownload As Boolean = False

    Private Const parameterPrefix As String = "$#"

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        'sqlId = Context.Request.QueryString("id")
        sqlId = "794"
        If Not IsNothing(sqlId) Then
            If IsNumeric(sqlId) Then
                Dim dr As Data.DataRow = sqlf.GetRow("SELECT sql_query, isnull(QUERY_PARAMS_DESCRIPTION,'') as QUERY_PARAMS_DESCRIPTION, MODULE_NAME FROM UserModules.[user].Orchestra_sql WHERE id = " & sqlId)
                If Not IsNothing(dr) Then
                    'Title = dr("MODULE_NAME")
                    Sql = dr("sql_query")
                    paramsDescription = dr("QUERY_PARAMS_DESCRIPTION")

                    If paramsDescription.ToString <> "" Then
                        For Each l As String In paramsDescription.Split(Chr(10))
                            If l.Split("|").Count > 2 Then
                                params.Add(l.Split("|")(1) & "|" & l.Split("|")(2), l.Split("|")(0).Replace(parameterPrefix, ""))
                            End If
                        Next
                    End If

                    If Sql.Contains(parameterPrefix) Then
                        Dim fromPos As Integer = 0
                        Dim toPos As Integer = 0
                        While fromPos <> -1
                            fromPos = Sql.IndexOf(parameterPrefix, fromPos + 1)
                            toPos = Math.Min(Math.Min(Math.Min(IndexOf(Sql, " ", fromPos + 1), IndexOf(Sql, Chr(10), fromPos + 1)), IndexOf(Sql, "'", fromPos + 1)), IndexOf(Sql, ")", fromPos + 1))
                            If toPos = Integer.MaxValue Then
                                toPos = Sql.Length
                            End If
                            Try
                                If Not filters.Contains(Sql.Substring(fromPos, toPos - fromPos)) Then
                                    filters.Add(Sql.Substring(fromPos, toPos - fromPos), Sql.Substring(fromPos, toPos - fromPos))
                                End If
                            Catch ex As Exception
                                'Message.Text &= ex.Message
                            End Try
                        End While
                    End If
                    For Each f As String In filters
                        f = f.Replace(parameterPrefix, "")
                        Dim txt As String
                        Dim type As Integer
                        If params.Contains(f) Then
                            txt = params(f).split("|")(1)
                            Select Case params(f).split("|")(0)
                                Case "TEXT"
                                    type = 11
                                Case "DATE"
                                    type = 4
                                Case Else
                                    type = 11
                            End Select
                        Else
                            txt = f
                            type = 11
                        End If

                        phFilters.Controls.Add(New Label With {.ID = "lbl" & f, .Text = txt & ": "})

                        txt = Context.Request.QueryString(f)

                        phFilters.Controls.Add(New TextBox With {.ID = "txt" & f, .ToolTip = f, .Text = txt, .TextMode = type})

                    Next
                End If
            End If
        End If

        monitor.DataSource = SqlDataSource1
        Try
            monitor.DataBind()
        Catch ex As Exception

        End Try

    End Sub

    Private Function IndexOf(str As String, c As String, startPos As Integer) As Integer
        Return IIf(str.IndexOf(c, startPos) = -1, Integer.MaxValue, str.IndexOf(c, startPos))
    End Function


    Private Sub r_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
        Dim dt As Data.DataTable = Nothing
        Dim sqlId As String = Context.Request.QueryString("id")
        monitor.Columns(0).Visible = True
        If IsPostBack Then
            For Each f In filters
                f = f.Replace(parameterPrefix, "")
                Sql = Sql.Replace(parameterPrefix & f, CType(phFilters.FindControl("txt" & f), TextBox).Text)
            Next
        Else
            For Each item In Context.Request.QueryString.Keys
                Sql = Sql.Replace(parameterPrefix & item, Context.Request.QueryString(item))
            Next
        End If

        Sql = HTMLToText(Sql)

        If Request.QueryString("mode") = "dev" Then
            Message.Text = Sql
        End If
        'dt = buildReport(Sql, sqlId)
        SqlDataSource1.SelectCommand = Sql

        ' report.DataSource = dt
        Try
            If ExcelDownload Then
                ExcelDownloadSub(monitor, sqlf.GetScalar("SELECT module + '_' + module_name FROM UserModules.[user].Orchestra_sql WHERE id = " & sqlId) & "_" & DateTime.Now.ToString("yyyyMMddHHmmss"))
            Else
                monitor.DataBind()
            End If
        Catch ex As Exception
            Message.Text &= " " & vbNewLine & ex.Message
        End Try

        CollectHeaderColumn(monitor, 1, 0)
        CollectHeaderColumn(monitor, 2, 0)
        CollectHeaderColumn(monitor, 3, 0)
        CollectHeaderColumn(monitor, 4, 0)
        CollectHeaderColumn(monitor, 5, 0)
        CollectHeaderColumn(monitor, 6, 0)
        CollectHeaderColumn(monitor, 10, 0)
        CollectHeaderColumn(monitor, 11, 0)
        monitor.Columns(0).Visible = False


    End Sub
    Private Function buildReport(_sql As String, _sqlid As Integer) As DataTable
        Dim dt As DataTable

        Dim startExec As DateTime = DateTime.Now
        dt = sqlf.GetData(_sql)
        Dim endExec As DateTime = DateTime.Now
        Dim execTime As TimeSpan = endExec.Subtract(startExec)
        sqlf.NonQuery("UPDATE UserModules.[user].Orchestra_sql SET LAST_EXEC_TIME = convert(varchar, getdate(),121),  " &
                                  "TOTAL_EXEC_QTY = isnull(TOTAL_EXEC_QTY,0) + 1,  " &
                                  "AVG__EXECUTION_TIME__MS =(cast(isnull(AVG__EXECUTION_TIME__MS, 0) as decimal(18,2)) + " & execTime.TotalMilliseconds & ") / 2, " &
                                  "AVG_EXEC_INTERVAL = (isnull(AVG_EXEC_INTERVAL, 0) + DATEDIFF(SECOND, isnull(convert(datetime, LAST_EXEC_TIME,121),getdate()), getdate())) / 2 " &
                                  "WHERE id=" & _sqlid)

        Return dt
    End Function
    Protected Sub ibExcelDownload_Click(sender As Object, e As ImageClickEventArgs) Handles ibExcelDownload.Click
        ExcelDownload = 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
        gv.DataBind()

        Dim sw As StringWriter = New StringWriter(myCItrad)
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

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

        Response.End()
    End Sub


    Public Sub CollectHeaderColumn(ByRef gv As GridView, colNumber As Integer, Optional referenceColNumber As Integer = -1)
        Dim curCell As New TableCell With {.Text = "."}
        Dim rowSpan As Integer = 1

        Dim ColNumberToCheckText As Integer
        If referenceColNumber = -1 Then
            ColNumberToCheckText = colNumber
        Else
            ColNumberToCheckText = referenceColNumber
        End If

        Dim curText As String = "."

        For Each dr As GridViewRow In gv.Rows
            If dr.Cells(ColNumberToCheckText).Text <> curText Then
                If rowSpan > 1 Then
                    curCell.RowSpan = rowSpan
                End If
                curCell = dr.Cells(colNumber)
                curText = dr.Cells(ColNumberToCheckText).Text
                rowSpan = 1
            Else
                dr.Cells(colNumber).Visible = False
                rowSpan += 1
            End If
        Next
        If rowSpan > 1 Then
            curCell.RowSpan = rowSpan
        End If
    End Sub


    Private Sub monitor_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles monitor.RowDataBound
        Dim GRColNum As Integer = 10
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(GRColNum).Attributes.Add("class", "gr")
            e.Row.Cells(GRColNum).Attributes.Add("id", e.Row.Cells(0).Text)
            TryCast(e.Row.Cells(GRColNum).Controls(0), CheckBox).Enabled = True
            TryCast(e.Row.Cells(GRColNum).Controls(0), CheckBox).Attributes.Add("onclick", "return false;")
            If TryCast(e.Row.Cells(GRColNum).Controls(0), CheckBox).Checked Then e.Row.BackColor = Drawing.Color.LightGreen


            e.Row.Cells(GRColNum - 1).Attributes.Add("class", "real_qty")
            e.Row.Cells(GRColNum - 1).Attributes.Add("id", e.Row.Cells(0).Text + "_" + e.Row.Cells(GRColNum - 3).Text + ";" + e.Row.Cells(GRColNum - 2).Text)

            e.Row.Cells(GRColNum + 2).Attributes.Add("class", "del_no")
            e.Row.Cells(GRColNum + 2).Attributes.Add("id", e.Row.Cells(0).Text + "_" + e.Row.Cells(GRColNum - 3).Text + ";" + e.Row.Cells(GRColNum - 2).Text)



            If e.Row.Cells(GRColNum - 1).Text <> e.Row.Cells(GRColNum - 2).Text Then
                e.Row.Cells(GRColNum - 1).BackColor = Drawing.Color.Red
                e.Row.Cells(GRColNum - 2).BackColor = Drawing.Color.Red
            End If


        End If
    End Sub

End Class