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

ModuleMaster/mm/early_warning.aspx.vb
183 строки8 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 = "936"
        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
    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")
        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(report, sqlf.GetScalar("SELECT module + '_' + module_name FROM UserModules.[user].Orchestra_sql WHERE id = " & sqlId) & "_" & DateTime.Now.ToString("yyyyMMddHHmmss"))
            Else
                report.DataBind()
            End If
        Catch ex As Exception
            Message.Text &= " " & vbNewLine & ex.Message
        End Try
    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 Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub


    Protected Sub getData_Click(sender As Object, e As EventArgs) Handles getData.Click
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        sqlf.NonQuery("Update UserModules.[user].WA_JOB_LIST Set RUN_IMMEDIATELY='Y' WHERE id=61 ")
        Message.Text = "Выгрузка поставлена в очередь. Время ожидания 15-20 минут"
    End Sub




End Class