ModuleMaster/handlers/getsqldata.ashx - main - ivan/ModMas

ModuleMaster/handlers/getsqldata.ashx
127 строк6 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
<%@ WebHandler Language="VB" Class="getsqldata" %>

Imports System
Imports System.Web
Imports Newtonsoft.Json
Imports sharedClasses.CreateExcelFile

Public Class getsqldata : Implements IHttpHandler

    Private Const parameterPrefix As String = "$#"

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        context.Response.ContentType = "text/plain"
        With context.Request

            'Dim who As String = .ToString
            Dim extractMode As String = .QueryString("extractmode")
            If IsNothing(extractMode) Then
                extractMode = "json"
            End If

            Dim sql As String = "" '.QueryString("sql")
            Dim sqlId As String = .QueryString("id")

            Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
            Dim dt As Data.DataTable = Nothing

            Dim affectedRows As Integer = -1
            Dim identity As Integer = -1
            Dim query_type As String = ""
            Dim query_name As String = "default"

            If Not IsNothing(sqlId) Then
                If IsNumeric(sqlId) Then
                    Dim params As System.Data.DataRow = sqlf.GetRow("SELECT isnull(QUERY_TYPE, 'SELECT') as QUERY_TYPE, sql_query, isnull(module_name, 'DEFAULT')  as name FROM UserModules.[user].Orchestra_sql WHERE id = " & sqlId)
                    ' sql = sqlf.GetScalar("SELECT sql_query FROM UserModules.[user].Orchestra_sql WHERE id = " & sqlId)
                    sql = params("sql_query")
                    query_type = params("QUERY_TYPE")
                    query_name = params("name")

                    For Each item In .QueryString.Keys
                        sql = sql.Replace(parameterPrefix & item, .QueryString(item).replace("--", "").replace("$amp;", "&"))
                        'sql &= "//" & parameterPrefix & item
                    Next

                    sql = CommonFunctions.HTMLToText(sql)

                    If Not IsNothing(sql) Then
                        Dim startExec As DateTime = DateTime.Now
                        'dt = sqlf.GetData(sql.Replace("--", ""))
                        Select Case query_type
                            Case "SELECT"
                                dt = sqlf.GetData(sql)
                            Case "INSERT"
                                identity = sqlf.Insert(sql)
                            Case "UPDATE", "DELETE"
                                affectedRows = sqlf.NonQuery(sql)
                            Case Else
                                dt = sqlf.GetData(sql)
                        End Select
                        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)
                    End If
                End If
            End If
            Try
                Select Case query_type
                    Case "SELECT"
                        If extractMode.ToUpper = "CSV" Then
                            context.Response.Write(DtToCSV(dt))
                        ElseIf extractMode.ToUpper = "XLS" Then
                            Dim filename As String = query_name.Replace(" ", "_") & "_" & DateTime.Now.ToString("yyyyMMddHHmmss")
                            CreateExcelDocument(dt.DataSet, context.Server.MapPath("~") & "\UploadedFiles\" & filename & ".xlsx")
                            context.Response.Clear()
                            context.Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xlsx")
                            context.Response.ContentType = "application/vnd.xlsx"
                            context.Response.WriteFile(context.Server.MapPath("~") & "\UploadedFiles\" & filename & ".xlsx", System.IO.FileMode.Open)
                            IO.File.Delete(context.Server.MapPath("~") & "\UploadedFiles\" & filename & ".xlsx")
                            context.Response.End()
                        Else
                            context.Response.Write(JsonConvert.SerializeObject(dt, Formatting.None))
                            'context.Response.Write(sql)
                        End If
                    Case "INSERT"
                        context.Response.Write(identity)
                    Case "UPDATE", "DELETE"
                        context.Response.Write(affectedRows)
                    Case Else
                        context.Response.Write(JsonConvert.SerializeObject(dt, Formatting.None))
                End Select
            Catch ex As Exception
                context.Response.Write("")
            End Try
        End With
    End Sub

    Private Function DtToCSV(d As Data.DataTable) As String
        Dim result As New StringBuilder
        If Not IsNothing(d) Then
            For Each dc As Data.DataColumn In d.Columns
                result.Append(dc.ColumnName)
                result.Append(vbTab)
            Next
            result.AppendLine()
            For Each dr As Data.DataRow In d.Rows
                For Each dc As Data.DataColumn In d.Columns
                    result.Append(dr(dc.ColumnName))
                    result.Append(vbTab)
                Next
                result.AppendLine()
            Next
        End If
        Return result.ToString
    End Function

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class