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

ModuleMaster/mm/ls26.aspx.vb
317 строк13 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 = "$#"

    Dim strPreviousLocation As String = String.Empty
    Dim SubTotalLocation As Integer = 0
    Dim GrantTotalLocation As Integer = 0
    Dim intSubTotalIndex As Integer = 1




    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        sqlId = 939
        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, isnull(MODULE_NAME,'NOT SPECIFIED') as  MODULE_NAME, isnull(REFRESH_RATE, 1) as REFRESH_RATE 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 IsNumeric(dr("REFRESH_RATE")) Then
                        refreshTimer.Interval = dr("REFRESH_RATE") * 1000
                        If refreshTimer.Interval > 1000 Then
                            refreshTimer.Enabled = True
                        Else
                            refreshTimer.Enabled = False
                        End If
                    End If

                    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

        'CommonFunctions.CollectHeaderColumn(report, 0, 0)

    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 SqlDataSource1_Selected(sender As Object, e As SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected
        rowCount.Text = "Total rows selected: " & e.AffectedRows
    End Sub

    Private Sub report_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles report.RowDataBound
        ' This is for calculation of column (Total = Direct + Referral)
        If e.Row.RowType = DataControlRowType.DataRow Then

            strPreviousLocation = DataBinder.Eval(e.Row.DataItem, "S-type").ToString()

            Dim Location As Int32 = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Qty").ToString())

            'Dim lblTotalRevenue As Label = DirectCast(e.Row.FindControl("lblTotalRevenue"), Label)
            'lblTotalRevenue.Text = String.Format("{0:0}", (Location))

            SubTotalLocation += Location
            GrantTotalLocation += Location

        End If
    End Sub

    Private Sub report_RowCreated(sender As Object, e As GridViewRowEventArgs) Handles report.RowCreated
        Dim IsSubTotalRowNeedToAdd As Boolean = False
        Dim IsGrandTotalRowNeedtoAdd As Boolean = False


        If (strPreviousLocation <> String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "S-type") IsNot Nothing) Then
            If strPreviousLocation <> DataBinder.Eval(e.Row.DataItem, "S-type").ToString() Then
                IsSubTotalRowNeedToAdd = True
            End If
        End If

        If (strPreviousLocation <> String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "S-type") Is Nothing) Then
            IsSubTotalRowNeedToAdd = True
            IsGrandTotalRowNeedtoAdd = True
            intSubTotalIndex = 0
        End If

        If IsSubTotalRowNeedToAdd Then
            Dim grdViewProducts As GridView = DirectCast(sender, GridView)

            ' Creating a Row
            Dim SubTotalRow As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

            'Adding Total Cell 
            Dim HeaderCell As New TableCell()
            HeaderCell.Text = "Sub Total"
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 3
            ' For merging first, second row cells to one
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)


            'Adding S-type Cell 
            HeaderCell = New TableCell()
            HeaderCell.Text = strPreviousLocation & " location"
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 5
            ' For merging first, second row cells to one
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding SubTotal Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0}", SubTotalLocation)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding Empty Cell 
            HeaderCell = New TableCell()
            HeaderCell.Text = ""
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 4
            ' For merging first, second row cells to one
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding the Row at the RowIndex position in the Grid
            grdViewProducts.Controls(0).Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, SubTotalRow)
            intSubTotalIndex += 1

            SubTotalLocation = 0
            strPreviousLocation = ""
        End If




        If IsGrandTotalRowNeedtoAdd Then
            Dim grdViewProducts As GridView = DirectCast(sender, GridView)
            ' Creating a Row
            Dim GrandTotalRow As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

            'Adding Total Cell 
            Dim HeaderCell As New TableCell()
            HeaderCell.Text = "Total"
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 8
            ' For merging first, second row cells to one
            HeaderCell.CssClass = "GrandTotalRowStyle"
            GrandTotalRow.Cells.Add(HeaderCell)

            'Adding SubTotal Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0}", GrantTotalLocation)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "GrandTotalRowStyle"
            GrandTotalRow.Cells.Add(HeaderCell)

            'Adding Empty Cell 
            HeaderCell = New TableCell()
            HeaderCell.Text = ""
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 4
            ' For merging first, second row cells to one
            HeaderCell.CssClass = "GrandTotalRowStyle"
            GrandTotalRow.Cells.Add(HeaderCell)

            'Adding the Row at the RowIndex position in the Grid
            grdViewProducts.Controls(0).Controls.AddAt(e.Row.RowIndex, GrandTotalRow)

        End If


    End Sub
End Class