ModuleMaster/app_code/commonfunctions.vb - main - ivan/ModMas

ModuleMaster/app_code/commonfunctions.vb
529 строк27 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports Microsoft.VisualBasic
Imports System.Reflection
Imports System.Web.UI.WebControls
Imports System.Data
Imports sharedClasses.dbFunctions
Imports Newtonsoft.Json

Public Class CommonFunctions

    Public Shared Function CloneControls(o As Object) As Object
        Dim type As Type = o.GetType
        If type.Name = "DataControlLinkButton" Then
            type = (New LinkButton).GetType
        End If
        Dim properties() As PropertyInfo = type.GetProperties
        Dim NewObject As Object = type.InvokeMember("", System.Reflection.BindingFlags.CreateInstance, Nothing, o, Nothing)
        For Each p As PropertyInfo In properties
            If p.CanWrite Then
                p.SetValue(NewObject, p.GetValue(o, Nothing), Nothing)
            End If
        Next
        Return NewObject
    End Function

    Public Shared Sub ApplyGridViewHeader(ParentId As String, ByRef gv As GridView, ByRef a(,) As String, Optional style As Collection = Nothing)
        'процедура написана таким образом, что в нее всегда должны передаваться индексы с учетом только фактически отображаемых колонок
        'т.е. при вычислении индекса объединяемой колонки (передаваемой в качестве параметра в процедуру) считается,
        'что колонок со свойством Visible = false в таблице как бы нет.
        'индексы (передаваемые в качестве параметров в процедуру) указываются по исходному порядку необъединенных колонок

        Dim AllowSorting As Boolean = gv.AllowSorting
        If gv.Rows.Count > 0 Then
            Dim row = New GridViewRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal)
            Dim r = gv.HeaderRow
            Dim cell As TableHeaderCell

            Dim i, j As Integer

            Dim invisibleColsCount As Integer = 0
            Dim cnt As Integer = 0

            For Each rcell As TableCell In r.Cells
                cell = New TableHeaderCell
                If AllowSorting Then
                    Dim lb As New HyperLink
                    Try
                        lb.Style("color") = "White"
                        lb.Text = CType(rcell.Controls(0), LinkButton).Text
                        lb.NavigateUrl = "javascript:__doPostBack('" & ParentId & "$" & gv.ID & "','Sort$" & CType(rcell.Controls(0), LinkButton).CommandArgument & "')"
                        cell.Controls.Add(lb)
                    Catch ex As Exception
                        cell.Text = rcell.Text
                    End Try
                Else
                    cell.Text = rcell.Text
                End If
                'For Each c In rcell.Controls
                '    cell.Controls.Add(c)
                'Next

                cell.Style("text-align") = "center"
                cell.Visible = rcell.Visible
                cnt += 1
                row.Cells.Add(cell)
            Next

            'для нужных ячеек в верхней строке, прописываем тексты объединяющих ячеек и их ColumnSpan
            For i = 0 To a.GetUpperBound(0)
                row.Cells(a(i, 1)).Text = a(i, 0)
                row.Cells(a(i, 1)).ColumnSpan = a(i, 2) - invisibleColsCount
            Next

            'идем по верхней строке у оставшихся ячеек (не задействованных) в предыдущем цикле ставим им RowSpan = 2
            'одновременно ячееки с таким же индексом в нижней строке помечаем на удаление
            For i = 0 To row.Cells.Count - 1
                If row.Cells(i).Visible Then
                    If row.Cells(i).ColumnSpan < 2 Then
                        row.Cells(i).RowSpan = 2
                        r.Cells(i).ColumnSpan = 100
                    Else
                        'пропускаем ячейки на которые распространяется ColumnSpan
                        i = i + row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                    End If
                End If
            Next

            'красим ячейки
            i = 0
            cnt = 1

            While i < row.Cells.Count
                If row.Cells(i).ColumnSpan > 1 Then

                    If Not IsNothing(style) Then
                        If style(cnt) <> "" Then
                            Dim st() As String = style(cnt).split(";")
                            For Each s As String In st
                                If s <> "" Then
                                    Try
                                        row.Cells(i).Style.Add(s.Split(":")(0), s.Split(":")(1))
                                        For j = 0 To row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                                            r.Cells(i + j).Style.Add(s.Split(":")(0), s.Split(":")(1))
                                        Next
                                    Catch ex As Exception
                                    End Try
                                End If
                            Next
                        End If
                    Else
                        row.Cells(i).BackColor = Drawing.Color.FromArgb(4013117)
                    End If
                    cnt += 1
                End If
                i = i + 1
            End While

            'идем по верхней строке удаляем соответствующее ColumnSpan количество ячеек после ячеек имеющих ColumnSpan > 1 
            i = 0
            cnt = 1

            While i < row.Cells.Count
                If row.Cells(i).ColumnSpan > 1 Then
                    For j = 1 To row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                        row.Cells.RemoveAt(i + 1)
                    Next
                    cnt += 1
                End If
                i = i + 1
            End While

            'удаляем помеченные ячейки в нижней строке
            i = 0
            While i < r.Cells.Count
                If r.Cells(i).ColumnSpan = 100 Then
                    r.Cells.RemoveAt(i)
                Else : i = i + 1
                End If
            End While


            CType(gv.Controls(0), Table).Rows.AddAt(0, row)
        End If
    End Sub

    Public Shared Sub ApplyGridViewHeader(ParentId As String, ByRef gv As GridView, ByRef a As Collection, Optional style As Collection = Nothing)
        'процедура написана таким образом, что в нее всегда должны передаваться индексы с учетом только фактически отображаемых колонок
        'т.е. при вычислении индекса объединяемой колонки (передаваемой в качестве параметра в процедуру) считается,
        'что колонок со свойством Visible = false в таблице как бы нет.
        'индексы (передаваемые в качестве параметров в процедуру) указываются по исходному порядку необъединенных колонок

        Dim AllowSorting As Boolean = gv.AllowSorting
        If gv.Rows.Count > 0 Then
            Dim row = New GridViewRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal)
            Dim r = gv.HeaderRow
            Dim cell As TableHeaderCell

            Dim i, j As Integer

            Dim invisibleColsCount As Integer = 0
            Dim cnt As Integer = 0

            'i = 1

            For Each rcell As TableCell In r.Cells
                cell = New TableHeaderCell
                If AllowSorting Then
                    Dim lb As New HyperLink
                    Try
                        lb.Style("color") = "White"
                        lb.Text = CType(rcell.Controls(0), LinkButton).Text
                        lb.NavigateUrl = "javascript:__doPostBack('" & ParentId & "$" & gv.ID & "','Sort$" & CType(rcell.Controls(0), LinkButton).CommandArgument & "')"
                        cell.Controls.Add(lb)
                    Catch ex As Exception
                        cell.Text = rcell.Text
                    End Try
                Else
                    cell.Text = rcell.Text
                End If
                cell.Style("text-align") = "center"
                cell.Visible = rcell.Visible

                'If cnt > a(i)(1) And cnt < a(i)(1) + a(i)(2) Then
                '    If Not rcell.Visible Then
                '        invisibleColsCount += 1
                '    End If
                'End If

                cnt += 1

                row.Cells.Add(cell)
                'End If
            Next

            'для нужных ячеек в верхней строке, прописываем тексты объединяющих ячеек и их ColumnSpan
            For i = 1 To a.Count
                row.Cells(a(i)(1)).Text = a(i)(0)
                row.Cells(a(i)(1)).ColumnSpan = a(i)(2) - invisibleColsCount
            Next

            'идем по верхней строке у оставшихся ячеек (не задействованных) в предыдущем цикле ставим им RowSpan = 2
            'одновременно ячееки с таким же индексом в нижней строке помечаем на удаление
            For i = 0 To row.Cells.Count - 1
                If row.Cells(i).Visible Then
                    If row.Cells(i).ColumnSpan < 2 Then
                        row.Cells(i).RowSpan = 2
                        r.Cells(i).ColumnSpan = 100
                    Else
                        'пропускаем ячейки на которые распространяется ColumnSpan
                        i = i + row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                    End If
                End If
            Next

            'удаляем помеченные ячейки в нижней строке
            i = 0
            While i < r.Cells.Count
                If r.Cells(i).ColumnSpan = 100 Then
                    r.Cells.RemoveAt(i)
                Else : i = i + 1
                End If
            End While

            'идем по верхней строке удаляем соответствующее ColumnSpan количество ячеек после ячеек имеющих ColumnSpan > 1 
            i = 0
            While i < row.Cells.Count
                If row.Cells(i).ColumnSpan > 1 Then
                    For j = 1 To row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                        row.Cells.RemoveAt(i + 1)
                    Next
                End If
                i = i + 1
            End While

            CType(gv.Controls(0), Table).Rows.AddAt(0, row)
        End If
    End Sub

    Public Shared Sub SendEmail(_recepient As String, _subject As String, _body As String, Optional attachment As String = "")
        Dim com As New System.Data.SqlClient.SqlCommand
        com.CommandText = "msdb.dbo.sp_send_dbmail"
        With com.Parameters
            .AddWithValue("profile_name", "Orchestra")
            .AddWithValue("recipients", _recepient & "@samsung.com")
            .AddWithValue("body_format", "HTML")
            .AddWithValue("subject", _subject)
            .AddWithValue("body", _body) ' & "<div id=""FactorySumDiv"" align=""center""><embed type=""application/x-shockwave-flash"" src=""http://106.109.79.119:8080/pm/FusionCharts/FCF_StackedColumn3D.swf"" width=""1000"" height=""550"" id=""FactorySum"" name=""FactorySum"" quality=""high"" allowscriptaccess=""always"" flashvars=""chartWidth=1000&amp;chartHeight=550&amp;debugMode=0&amp;DOMId=FactorySum&amp;registerWithJS=0&amp;scaleMode=noScale&amp;lang=EN&amp;dataXML=&lt;graph caption='Tasks weight' subCaption='By User' decimalPrecision='0' showNames='1'  numberSuffix=' points' formatNumberScale='0'&gt;&lt;categories&gt;&lt;category name='P.PLAKHOV' /&gt;&lt;category name='A.ALEHIN' /&gt;&lt;category name='ST.KALMYKOV' /&gt;&lt;category name='K.YUSHIN' /&gt;&lt;category name='I.KRIVOKON' /&gt;&lt;category name='PAVEL.GERAS' /&gt;&lt;category name='A.VASIEV' /&gt;&lt;category name='D.BARANOV' /&gt;&lt;category name='SY1120.PARK' /&gt;&lt;category name='YOONJUNG.CHA' /&gt;&lt;/categories&gt;&lt;DataTable seriesName='InProgress tasks' color='FFFF66'&gt;&lt;set value='80' /&gt;&lt;set value='95' /&gt;&lt;set value='40' /&gt;&lt;set value='105' /&gt;&lt;set value='180' /&gt;&lt;set value='40' /&gt;&lt;set value='80' /&gt;&lt;set value='80' /&gt;&lt;set value='100' /&gt;&lt;set value='30' /&gt;&lt;/DataTable&gt;&lt;DataTable seriesName='Completed tasks' color='66FF66'&gt;&lt;set value='151' /&gt;&lt;set value='161' /&gt;&lt;set value='150' /&gt;&lt;set value='77' /&gt;&lt;set value='66' /&gt;&lt;set value='72' /&gt;&lt;set value='60' /&gt;&lt;set value='35' /&gt;&lt;set value='0' /&gt;&lt;set value='0' /&gt;&lt;/DataTable&gt;&lt;DataTable seriesName='On-time score' color='66CCFF'&gt;&lt;set value='110' /&gt;&lt;set value='60' /&gt;&lt;set value='40' /&gt;&lt;set value='100' /&gt;&lt;set value='70' /&gt;&lt;set value='60' /&gt;&lt;set value='60' /&gt;&lt;set value='30' /&gt;&lt;set value='0' /&gt;&lt;set value='0' /&gt;&lt;/DataTable&gt;&lt;/graph&gt;""></div>")
            If attachment.Trim <> "" Then
                .AddWithValue("file_attachments", attachment.Trim)
            End If
        End With
        com.CommandType = Data.CommandType.StoredProcedure
        Call (New SqlFunctions).NonQuery(com)
    End Sub
    Public Shared Sub SendEmail2(_recipients As String, _subject As String, _body As String, Optional attachment As String = "")
        Dim com As New System.Data.SqlClient.SqlCommand
        com.CommandText = "msdb.dbo.sp_send_dbmail"
        With com.Parameters
            .AddWithValue("profile_name", "Orchestra")
            .AddWithValue("recipients", _recipients)
            .AddWithValue("body_format", "HTML")
            .AddWithValue("subject", _subject)
            .AddWithValue("body", _body) ' & "<div id=""FactorySumDiv"" align=""center""><embed type=""application/x-shockwave-flash"" src=""http://106.109.79.119:8080/pm/FusionCharts/FCF_StackedColumn3D.swf"" width=""1000"" height=""550"" id=""FactorySum"" name=""FactorySum"" quality=""high"" allowscriptaccess=""always"" flashvars=""chartWidth=1000&amp;chartHeight=550&amp;debugMode=0&amp;DOMId=FactorySum&amp;registerWithJS=0&amp;scaleMode=noScale&amp;lang=EN&amp;dataXML=&lt;graph caption='Tasks weight' subCaption='By User' decimalPrecision='0' showNames='1'  numberSuffix=' points' formatNumberScale='0'&gt;&lt;categories&gt;&lt;category name='P.PLAKHOV' /&gt;&lt;category name='A.ALEHIN' /&gt;&lt;category name='ST.KALMYKOV' /&gt;&lt;category name='K.YUSHIN' /&gt;&lt;category name='I.KRIVOKON' /&gt;&lt;category name='PAVEL.GERAS' /&gt;&lt;category name='A.VASIEV' /&gt;&lt;category name='D.BARANOV' /&gt;&lt;category name='SY1120.PARK' /&gt;&lt;category name='YOONJUNG.CHA' /&gt;&lt;/categories&gt;&lt;DataTable seriesName='InProgress tasks' color='FFFF66'&gt;&lt;set value='80' /&gt;&lt;set value='95' /&gt;&lt;set value='40' /&gt;&lt;set value='105' /&gt;&lt;set value='180' /&gt;&lt;set value='40' /&gt;&lt;set value='80' /&gt;&lt;set value='80' /&gt;&lt;set value='100' /&gt;&lt;set value='30' /&gt;&lt;/DataTable&gt;&lt;DataTable seriesName='Completed tasks' color='66FF66'&gt;&lt;set value='151' /&gt;&lt;set value='161' /&gt;&lt;set value='150' /&gt;&lt;set value='77' /&gt;&lt;set value='66' /&gt;&lt;set value='72' /&gt;&lt;set value='60' /&gt;&lt;set value='35' /&gt;&lt;set value='0' /&gt;&lt;set value='0' /&gt;&lt;/DataTable&gt;&lt;DataTable seriesName='On-time score' color='66CCFF'&gt;&lt;set value='110' /&gt;&lt;set value='60' /&gt;&lt;set value='40' /&gt;&lt;set value='100' /&gt;&lt;set value='70' /&gt;&lt;set value='60' /&gt;&lt;set value='60' /&gt;&lt;set value='30' /&gt;&lt;set value='0' /&gt;&lt;set value='0' /&gt;&lt;/DataTable&gt;&lt;/graph&gt;""></div>")
            If attachment.Trim <> "" Then
                .AddWithValue("file_attachments", attachment.Trim)
            End If
        End With
        com.CommandType = Data.CommandType.StoredProcedure
        Call (New SqlFunctions).NonQuery(com)
    End Sub

    Public Shared Sub CollectHeaderColumn(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 'curCell.Text 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

    Public Shared Sub CollectHeaderColumn(ByRef gv As Table, colNumber As Integer, Optional referenceColNumber As Integer = -1, Optional skipRowsCount As Integer = 0)
        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 TableRow In CType(gv.Controls(0), Table).Rows
        Dim cnt As Integer = 1
        For Each dr As TableRow In gv.Rows
            If cnt > skipRowsCount Then
                If dr.Cells(ColNumberToCheckText).Text <> curText Or curText = "&nbsp;" Then 'curCell.Text 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
            End If
            cnt += 1
        Next

        If rowSpan > 1 Then
            curCell.RowSpan = rowSpan
        End If
    End Sub

    Public Shared Function SaveFile(ByVal file As FileUpload, savePath As String) As String
        Dim fileName As String = file.FileName
        Dim pathToCheck As String = savePath + fileName
        Dim tempfileName As String = ""
        If Not System.IO.Directory.Exists(savePath) Then
            Try
                System.IO.Directory.CreateDirectory(savePath)
            Catch ex As Exception
                SaveFile = ex.Message
            End Try
        End If

        If (System.IO.File.Exists(pathToCheck)) Then
            System.IO.File.Delete(pathToCheck)
            fileName = tempfileName
            SaveFile = "A file with the same name was overwritten " + fileName
        Else
            SaveFile = "Your file was uploaded successfully."
        End If
        file.SaveAs(pathToCheck)

    End Function

    ' This function converts HTML code to plain text
    ' Any step is commented to explain it better
    ' You can change or remove unnecessary parts to suite your needs
    Public Shared Function HTMLToText(ByVal HTMLCode As String) As String
        ' Remove new lines since they are not visible in HTML
        'HTMLCode = HTMLCode.Replace("\n", " ")

        '' Remove tab spaces
        'HTMLCode = HTMLCode.Replace("\t", " ")

        '' Remove multiple white spaces from HTML
        'HTMLCode = Regex.Replace(HTMLCode, "\\s+", "  ")

        '' Remove HEAD tag
        'HTMLCode = Regex.Replace(HTMLCode, "<head.*?</head>", "" _
        '  , RegexOptions.IgnoreCase Or RegexOptions.Singleline)

        '' Remove any JavaScript
        'HTMLCode = Regex.Replace(HTMLCode, "<script.*?</script>", "" _
        '  , RegexOptions.IgnoreCase Or RegexOptions.Singleline)

        ' Replace special characters like &, <, >, " etc.
        Dim sbHTML As StringBuilder = New StringBuilder(HTMLCode)
        ' Note: There are many more special characters, these are just
        ' most common. You can add new characters in this arrays if needed
        Dim OldWords() As String = {"&nbsp;", "&amp;", "&quot;", "&lt;", "&gt;", "&reg;", "&copy;", "&bull;", "&trade;"}
        Dim NewWords() As String = {" ", "&", """", "<", ">", "Â®", "Â©", "â€¢", "â„¢"}
        For i As Integer = 0 To OldWords.Length - 1
            sbHTML.Replace(OldWords(i), NewWords(i))
        Next i

        ' Check if there are line breaks (<br>) or paragraph (<p>)
        sbHTML.Replace("<br>", "\n<br>")
        sbHTML.Replace("<br ", "\n<br ")
        sbHTML.Replace("<p ", "\n<p ")

        ' Finally, remove all HTML tags and return plain text
        'Return 'System.Text.RegularExpressions.Regex.Replace(
        Return sbHTML.ToString()
        ', "<[^>]*>", "")
    End Function

    Public Shared Function UploadDataTableToDB(databaseName As String, schema As String, ByVal dbtableName As String, dt As Data.DataTable, Optional ByVal ClearData As Boolean = False, Optional BulkOrExact As Integer = 0, Optional ByRef exception As String = "") As Integer
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        Dim rowcount As Integer = 0
        Try
            Dim SQL As String = ""
            If ClearData Then
                SQL = "TRUNCATE TABLE " & databaseName & "." & schema & "." & dbtableName
                sqlf.NonQuery(SQL)
            End If
            Dim sqlScript As String = "USE " & databaseName & " select name as COLUMN_NAME from sys.columns where object_id in ( " &
                "select object_id from sys.tables " &
                "where name = '" & dbtableName & "' " &
                ") and name <> 'id'  order by column_id "
            Dim columns As Data.DataTable = sqlf.GetData(sqlScript)
            '----------------------------------------------
            Dim i As Integer = 0
            Dim sColumnsList As String = ""
            Dim sParamsList As String = ""
            Dim iColumnsNumber As Integer = 0
            For Each dr As DataRow In columns.Rows
                If dr("COLUMN_NAME") <> "ID" Then
                    If i > 0 Then
                        sColumnsList &= ","
                        sParamsList &= ","
                    End If
                    sColumnsList &= dr("COLUMN_NAME")
                    sParamsList &= "@" & dr("COLUMN_NAME")
                    i += 1
                End If
            Next

            iColumnsNumber = i
            Dim com As New SqlClient.SqlCommand
            SQL = " Use " & databaseName & " INSERT INTO " & databaseName & "." & schema & "." & dbtableName & " ("
            SQL &= sColumnsList
            SQL &= ") VALUES ("
            SQL &= sParamsList & ")"
            com.CommandText = SQL

            For Each dr As DataRow In dt.Rows
                com.Parameters.Clear()
                If BulkOrExact = 0 Then
                    ' Uploading data by column order 
                    i = 1
                    Dim fieldsCount As Integer = 0
                    For i = 0 To iColumnsNumber - 1
                        If i > dr.ItemArray.Count - 1 Then
                            com.Parameters.AddWithValue(sParamsList.Split(",")(i), DBNull.Value)
                        Else
                            com.Parameters.AddWithValue(sParamsList.Split(",")(i), dr.ItemArray(i).ToString)
                        End If
                    Next
                    sqlf.NonQuery(com)
                    rowcount += 1
                Else
                    'Uploading data by matching field name
                    Dim paramsArray() As String = sColumnsList.Split(",")
                    Dim params As New Collection
                    For Each p As String In paramsArray
                        params.Add(p, p)
                    Next
                    For i = 0 To dt.Columns.Count - 1
                        com.Parameters.AddWithValue(dt.Columns(i).ColumnName, dr(dt.Columns(i).ColumnName))
                        params.Remove(dt.Columns(i).ColumnName)
                    Next
                    For Each p As String In params
                        com.Parameters.AddWithValue(p, DBNull.Value)
                    Next

                    sqlf.NonQuery(com)
                    rowcount += 1
                End If
            Next
        Catch ex As Exception
            exception = ex.Message
            Return -1
        End Try
        Return rowcount
    End Function

    Public Function getSqlData(sqlid As Integer, sqlParams As NameValueCollection, Optional returnType As Integer = 0) As Object '0 - dataTable / 1 - JSON
        Dim parameterPrefix As String = "$#"
        Dim dt As Data.DataTable = Nothing
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        Dim sql As String = ""
        If Not IsNothing(sqlid) Then
            If IsNumeric(sqlid) Then
                sql = sqlf.GetScalar("SELECT sql_query FROM UserModules.[user].Orchestra_sql WHERE id = " & sqlid)

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

                sql = CommonFunctions.HTMLToText(sql)

                If Not IsNothing(sql) Then
                    Dim startExec As DateTime = DateTime.Now
                    'dt = sqlf.GetData(sql.Replace("--", ""))
                    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)
                End If
            End If
        ElseIf Not IsNothing(Sql) Then
            dt = sqlf.GetData(Sql)
        End If
        Try
            If returnType = 0 Then
                Return dt
            ElseIf returnType = 1 Then
                Return JsonConvert.SerializeObject(dt, Formatting.None)
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

End Class