ModuleMaster/usercontrols/pivotfortable.ascx.vb - main - ivan/ModMas

ModuleMaster/usercontrols/pivotfortable.ascx.vb
197 строк7 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports sharedClasses.dbFunctions

Partial Class UserControls_pivotForTable
    Inherits System.Web.UI.UserControl

    Public Property TableId As Integer
    Public Property PivotName As String

    Private ColumnsConfiguration As Data.DataTable

    Private Function PrepareFormula(ByVal formula As String) As String 'NEW!!!
        Dim params() As String = formula.Split(" ")
        Dim cnt As Integer = 1
        Dim preparedFormula As String = ""
        For Each p As String In params
            p = p.Trim
            If {"*", "/", "+", "-", "(", ")"}.Contains(p) Then
                preparedFormula &= p
            Else
                preparedFormula &= " cast(" & CheckForFormula(p) & " as decimal(18,8)) "
            End If
            cnt += 1
        Next
        Return preparedFormula
    End Function

    Dim iterationCount As Integer = 0

    Private Function GetColumnsConfiguration(ByVal tableid As Integer) As Data.DataTable
        Try
            Dim sql As New StringBuilder
            sql.Append("USE UserModules ")
            sql.Append("SELECT ")
            sql.Append("  t1.name, ")
            sql.Append("  t2.id, ")
            sql.Append("  t2.tableid, ")
            sql.Append("  t2.FieldName , ")
            sql.Append("  t2.FieldDescription , ")
            sql.Append("  t2.FieldType , ")
            sql.Append("  t2.DbFieldName , ")
            sql.Append("  isnull(t2.[Enabled] ,'Y')  AS [enabled] , ")
            sql.Append("  isnull(t2.[Position],9999) AS [position] , ")
            sql.Append("  isnull(t2.[Visible] ,'Y')  AS [Visible] , ")
            sql.Append("  isnull(t2.[Filter] ,'N')   AS [Filter] ,  ")
            sql.Append("  isnull(t2.[SEARCH] ,'N')   AS [SEARCH] , ")
            sql.Append("  isnull(t2.[FORMULA] ,'N')   AS [FORMULA],  ")
            sql.Append("  isnull(t2.[USER_DEPENDENT] ,'N')   AS [USER_DEPENDENT]  ")
            sql.Append("FROM sys.columns t1 ")
            sql.Append("LEFT OUTER JOIN ")
            sql.Append("  (SELECT * ")
            sql.Append("  FROM TableFields ")
            sql.Append("  WHERE Tableid =" & tableid)
            sql.Append("  )t2 ")
            sql.Append("ON t1.name          = t2.DbFieldName ")
            sql.Append("WHERE t1.object_id IN ")
            sql.Append("  ( SELECT object_id FROM sys.tables WHERE name = (SELECT DISTINCT DBTABLENAME FROM UserModules.dbo.MOduleTAbles WHERE id = " & tableid & ") ")
            sql.Append("  ) ")
            sql.Append("ORDER BY isnull(t2.position,9999)")

            Dim dt As Data.DataTable = (New SqlFunctions).GetData(sql.ToString)
            If dt.Rows.Count = 0 Then
                Return Nothing
            Else
                Return dt
            End If
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Private Function CheckForFormula(ByVal param As String) As String 'NEW!!!
        Try
            iterationCount += 1
            If iterationCount > 100 Then
                Return "'" & param & "'"
            End If

            Dim rows() As Data.DataRow = ColumnsConfiguration.Select("dbfieldname='" & param & "'")
            If rows.Count > 0 Then
                Dim dr As Data.DataRow = rows(0)
                If dr("fieldtype") = "FORMULA" Then
                    Return "(" & PrepareFormula(dr("formula")) & ")"
                ElseIf dr("fieldtype") = "NUMBER" Then
                    Return " cast ( nullif(rtrim([" & param & "]),'') as decimal ) "
                Else
                    Return " nullif(rtrim([" & param & "]),'') "
                End If
            Else
                Return "'" & param & "'"
            End If
        Catch ex As Exception
            Return "'" & param & "'"
        End Try
    End Function


    Private Function GetPivotConfig(_tableId As Integer, _pivotName As String) As Data.DataTable
        Dim ds As Data.DataTable = (New SqlFunctions).GetData("SELECT * from UserModules.dbo.TablePivot WHERE tableId=" & _tableId & " AND pivotName='" & _pivotName & "'")
        If Not IsNothing(ds) Then
            Return ds
        Else
            Return Nothing
        End If
    End Function

    Private Function PrepareSql(_pivotConfig As Data.DataTable) As String
        If IsNothing(_pivotConfig) Then Return Nothing

        Dim _sql As New StringBuilder

        Dim _fieldList As String = ""
        Dim _columnsList As String = ""
        Dim _pivotValues As String = ""
        Dim _tableName As String = ""
        Dim _rowList As String = ""
        Dim _filterList As String = ""
        For Each dr As Data.DataRow In _pivotConfig.Rows
            Select Case dr("type")
                Case "ROW"
                    If _rowList = "" Then
                        _rowList &= dr("dbFieldName")
                    Else
                        _rowList &= "," & dr("dbFieldName")
                    End If
                Case "COLUMN"
                    _columnsList &= dr("dbFieldName")
                Case "VALUE"
                    _pivotValues &= dr("function") & " (" & dr("dbFieldName") & ") "
                Case "FILTER"
                    _filterList = dr("dbFieldName") & dr("function")
            End Select
            Dim _f As String = CheckForFormula(dr("dbFieldName"))
            If _fieldList = "" Then
                _fieldList &= _f & " as " & dr("dbFieldName")
            Else
                _fieldList &= "," & _f & " as " & dr("dbFieldName")
            End If
            _tableName = dr("dbTableName")
        Next

        If _columnsList = "" Then
            _sql.Append("SELECT ")
            _sql.Append(_rowList)
            _sql.Append(",")
            _sql.Append(_pivotValues & " AS '" & _pivotValues & "'")
            _sql.Append(" FROM UserModules.[USER]." & _tableName)
            If _filterList <> "" Then
                _sql.Append(" WHERE ")
                _sql.Append(_filterList)
            End If
            _sql.Append(" GROUP BY ")
            _sql.Append(_rowList)
        Else
            _sql.Append("USE UserModules ")
            _sql.Append(" DECLARE @COLUMNS VARCHAR(8000)")
            _sql.Append(" SELECT @COLUMNS = COALESCE(@COLUMNS + ',[' + " & _columnsList & " + ']','['+" & _columnsList & "+']') FROM [USER]." & _tableName & " GROUP BY " & _columnsList)
            _sql.Append(" DECLARE @QUERY VARCHAR(8000) ")
            _sql.Append(" SET @QUERY=' ")
            _sql.Append(" SELECT * FROM  ")
            _sql.Append("	(	SELECT  ")
            _sql.Append(_fieldList.Replace("'", "''"))
            _sql.Append("		FROM usermodules.[user].")
            _sql.Append(_tableName)
            _sql.Append(" ) AS source_table ")
            _sql.Append("PIVOT (" & _pivotValues.Replace("'", "''") & " FOR " & _columnsList)
            _sql.Append(" IN (' + @COLUMNS + ')) p1'")
            _sql.Append(" EXECUTE(@QUERY) ")
        End If

        Return _sql.ToString
    End Function

    Private Sub BindData(_sql As String)
        Try
            Dim ds As Data.DataTable = (New SqlFunctions).GetData(_sql)
            If Not IsNothing(ds) Then



                gvPivot.DataSource = ds
                gvPivot.DataBind()

            End If
        Catch ex As Exception

        End Try
    End Sub

    Public Sub BuildPivot()
        ColumnsConfiguration = GetColumnsConfiguration(TableId)
        BindData(PrepareSql(GetPivotConfig(TableId, PivotName)))
    End Sub

    Protected Sub gvPivot_Sorting(sender As Object, e As GridViewSortEventArgs) Handles gvPivot.Sorting
        gvPivot.Sort(e.SortExpression, e.SortDirection)
    End Sub
End Class