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

ModuleMaster/app_code/edgridhelpers.vb
444 строки20 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Namespace edGridNamespace
    Public Class edGridHelpers
        Public Function getControl(params As DataRow, value As String) As Object
            Dim o As New Object
            Select Case params("FieldType")
                Case "CHECKBOX"
                    Dim i As New CheckBox
                    i.Checked = (value = "Y")
                    i.Attributes.Add("onclick", "return false")
                    'i.Enabled = False
                    Return i
                Case "TEXTBOX"
                    Dim i As New Literal
                    i.Text = value.Replace(Chr(10), "<br>")
                    Return i
                    'Case "FILE"
                    '    'Dim fc As ASP.usercontrols_advancedfileslist_ascx = LoadControl("~/UserControls/advancedFilesList.ascx")
                    '    fc.SavePath = Server.MapPath("") & "\UploadedFiles\"
                    '    fc.GroupId = rowView("ID")
                    '    fc.GroupType = dr("dbfieldname")
                    '    fc.StorageName = FullTableName.Replace(UserDataStoragePrefix, AttachmentsStoragePrefix)
                    '    fc.ShowAsList = bReadOnly Or (dr("enabled") = "N")
                    '    fc.BuildFileList(bReadOnly Or (dr("enabled") = "N"))
                    '    cntrl = fc
                    'Case "PHOTO"
            End Select
            Return Nothing
        End Function
    End Class
    Public Class tableConfig
        Private _colConfig As System.Data.DataTable = Nothing
        Private _tableConfig As System.Data.DataTable = Nothing
        Private _objectId As Integer = 0 ' SQL sys table object id
        Private _dbName As String = "[UserModules]"
        Private sqlf As New sharedClasses.dbFunctions.SqlFunctions
        Public data_update As String
        Public Event ErrorHappened(source As String, ex As Exception)

        Public ReadOnly Property dbTableName As String
            Get
                Return GetParam(0, "DbTableName")
            End Get
        End Property

        Public ReadOnly Property objectId As String
            Get
                Return GetParam(0, "object_id")
            End Get
        End Property


        Public ReadOnly Property TableConfig As DataTable
            Get
                Return _tableConfig
            End Get
        End Property

        Public ReadOnly Property ColConfig As DataTable
            Get
                Return _colConfig
            End Get
        End Property

        Public Property dbName As String
            Set(value As String)
                _dbName = value
            End Set
            Get
                Return _dbName
            End Get
        End Property

        Public ReadOnly Property Formulas As DataTable
            Get
                Try
                    If Not IsNothing(_colConfig) Then
                        Dim f As DataTable = _colConfig.Clone
                        For Each r In _colConfig.Select("fieldType='FORMULA'")
                            f.ImportRow(r)
                        Next
                        Return f
                    Else
                        Return Nothing
                    End If
                Catch ex As Exception
                    Return Nothing
                End Try
            End Get
        End Property

        Public Sub New(tid As Integer)
            _colConfig = GetColConfig(tid)
            _tableConfig = GetTableConfig(tid)
        End Sub

        Public Sub New(tName As String)
            _colConfig = GetColConfig(tName)
            _tableConfig = GetTableConfig(tName)
        End Sub

        Public Function GetParam(rownum As Integer, paramName As String) As String
            Try
                Return _colConfig.Rows(rownum).Item(paramName)
            Catch ex As Exception
                Return ""
            End Try
        End Function
        Public Function GetParam(field As String, paramName As String) As String
            Try
                Return _colConfig.Select("NAME='" & field & "'")(0).Item(paramName)
            Catch ex As Exception
                Return ""
            End Try
        End Function

        Public Function GetParamRow(rownum As Integer) As DataRow
            Try
                Return _colConfig.Rows(rownum)
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

        Public Function GetParamRow(fieldName As String) As DataRow
            Try
                Return _colConfig.Select("name = '" & fieldName & "'")(0)
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

        Private Function GetTableConfig(ByVal tableId As Integer) As Data.DataTable
            Try
                Dim sql As String = "select tableName , dbTableName, isnull(pager_size,15) as pager_size, isnull(RESTRICTED_ACCESS,'Y') as RESTRICTED_ACCESS, CREATED_BY, isnull(BULK_DATA_UPLOAD,'N') as BULK_DATA_UPLOAD, isnull(SEND_MAIL,'N') as SEND_MAIL, isnull(ALLOW_ALL_DELETE,'N') as ALLOW_ALL_DELETE, isnull([schema],'user') as [schema], isnull(PLAY_SOUND_ON_CHANGE,'N') as PLAY_SOUND_ON_CHANGE, isnull(NEW_ROW_ADDED,'') as NEW_ROW_ADDED, isnull(SHOW_SEL_ROW_BUTTON,'Y') as SHOW_SEL_ROW_BUTTON, isnull(KEEP_HISTORY, 'N') as KEEP_HISTORY, isnull(ON_DATA_CHANGED,'') as on_data_changed  from [UserModules].[dbo].[ModuleTables] where id = " & tableId

                Dim dt As DataTable = sqlf.GetData(sql)
                If dt.Rows.Count = 0 Then
                    Return Nothing
                Else
                    Return dt
                End If
            Catch ex As Exception
                RaiseEvent ErrorHappened("GetTableConfig", ex)
                Return Nothing
            End Try
        End Function

        Private Function GetTableConfig(ByVal dbTableName As String) As Data.DataTable
            Try
                Dim sql As String = "select tableName , dbTableName, isnull(pager_size,15) as pager_size, isnull(RESTRICTED_ACCESS,'Y') as RESTRICTED_ACCESS, CREATED_BY, isnull(BULK_DATA_UPLOAD,'N') as BULK_DATA_UPLOAD, isnull(SEND_MAIL,'N') as SEND_MAIL, isnull(ALLOW_ALL_DELETE,'N') as ALLOW_ALL_DELETE, isnull([schema],'user') as [schema], isnull(PLAY_SOUND_ON_CHANGE,'N') as PLAY_SOUND_ON_CHANGE, isnull(NEW_ROW_ADDED,'') as NEW_ROW_ADDED, isnull(SHOW_SEL_ROW_BUTTON,'Y') as SHOW_SEL_ROW_BUTTON, isnull(KEEP_HISTORY, 'N') as KEEP_HISTORY, isnull(ON_DATA_CHANGED,'') as on_data_changed  from [UserModules].[dbo].[ModuleTables] where dbtableName = " & dbTableName

                Dim dt As DataTable = sqlf.GetData(sql)
                If dt.Rows.Count = 0 Then
                    Return Nothing
                Else
                    Return dt
                End If
            Catch ex As Exception
                RaiseEvent ErrorHappened("GetTableConfig", ex)
                Return Nothing
            End Try
        End Function
        Private Function GetColConfig(ByVal tableId As Integer) As Data.DataTable
            Try
                Dim sql As New StringBuilder
                sql.Append("Select ")
                sql.Append(" T.name as DbTableName, ")
                sql.Append("	C.Object_Id, ")
                sql.Append("	Tf.Id, ")
                sql.Append("	C.Name, ")
                sql.Append("	Tf.Fieldname, ")
                sql.Append("	Tf.Fielddescription, ")
                sql.Append("	C.Column_Id, ")
                sql.Append("	Tf.[Position], ")
                sql.Append("	Tf.Fieldtype, ")
                sql.Append("	Ty.Name as dbType, ")
                sql.Append("	C.Max_Length, ")
                sql.Append("	C.Precision, ")
                sql.Append("	C.Scale, ")
                sql.Append("	C.Collation_Name, ")
                sql.Append("	C.Is_Nullable, ")
                sql.Append("	isnull(Tf.Enabled,'N') as Enabled, ")
                sql.Append("	Tf.Visible, ")
                sql.Append("	Tf.Filter, ")
                sql.Append("	Tf.Search, ")
                sql.Append("	Tf.Formula, ")

                sql.Append("	Tf.Encode, ")

                sql.Append("	Tf.User_Dependent, ")
                sql.Append("	Tf.Style, ")
                sql.Append("	Tf.Default_Value, ")
                sql.Append("	Tf.Created_By ")
                sql.Append(" From ")
                sql.Append(_dbName & ".Sys.Tables T ")
                sql.Append(" Left Join " & _dbName & ".Sys.Columns C On T.Object_Id = C.Object_Id ")
                sql.Append(" Left Join " & _dbName & ".Sys.Types Ty On C.User_Type_Id = Ty.User_Type_Id ")
                sql.Append(" Left Join " & _dbName & ".Dbo.Tablefields Tf On C.Name = Tf.Dbfieldname ")
                sql.Append(" Where ")
                sql.Append("	T.Name = (Select Dbtablename From " & _dbName & ".Dbo.Moduletables Where Id = " & tableId & ") ")
                sql.Append(" And Tf.Tableid = " & tableId)
                sql.Append(" Order By Tf.[Position] ")

                Dim dt As DataTable = sqlf.GetData(sql.ToString)
                Return dt
            Catch ex As Exception
                RaiseEvent ErrorHappened("GetColConfig", ex)
                Return Nothing
            End Try
        End Function

        Private Function GetColConfig(ByVal dbTableName As String) As Data.DataTable
            Try
                Dim sql As New StringBuilder
                sql.Append("Select ")
                sql.Append(" T.name as DbTableName, ")
                sql.Append("	C.Object_Id, ")
                sql.Append("	Tf.Id, ")
                sql.Append("	C.Name, ")
                sql.Append("	Tf.Fieldname, ")
                sql.Append("	Tf.Fielddescription, ")
                sql.Append("	C.Column_Id, ")
                sql.Append("	Tf.[Position], ")
                sql.Append("	Tf.Fieldtype, ")
                sql.Append("	Ty.Name as dbType, ")
                sql.Append("	C.Max_Length, ")
                sql.Append("	C.Precision, ")
                sql.Append("	C.Scale, ")
                sql.Append("	C.Collation_Name, ")
                sql.Append("	C.Is_Nullable, ")
                sql.Append("	isnull(Tf.Enabled,'N') as Enabled, ")
                sql.Append("	Tf.Visible, ")
                sql.Append("	Tf.Filter, ")
                sql.Append("	Tf.Search, ")
                sql.Append("	Tf.Formula, ")

                sql.Append("	Tf.Encode, ")

                sql.Append("	Tf.User_Dependent, ")
                sql.Append("	Tf.Style, ")
                sql.Append("	Tf.Default_Value, ")
                sql.Append("	Tf.Created_By ")
                sql.Append("From ")
                sql.Append(_dbName & ".Sys.Tables T ")
                sql.Append(" Left Join " & _dbName & ".Sys.Columns C On T.Object_Id = C.Object_Id ")
                sql.Append(" Left Join " & _dbName & ".Sys.Types Ty On C.User_Type_Id = Ty.User_Type_Id ")
                sql.Append(" Left Join " & _dbName & ".Dbo.Tablefields Tf On C.Name = Tf.Dbfieldname ")
                sql.Append("And Tf.Tableid = (SELECT id from " & dbName & ".dbo.ModuleTables where DbTableName = '" & dbTableName & "') ")
                sql.Append("Where ")
                sql.Append("	T.Name = '" & dbTableName & "'")

                sql.Append("Order By Tf.[Position] ")

                Dim dt As DataTable = sqlf.GetData(sql.ToString)
                Return dt
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

        Public Sub UploadDataToTable(text As String, ByRef TotalLines As Integer, ByRef InsertedLine As Integer, clearData As Boolean)

            If clearData Then
                sqlf.NonQuery("DELETE FROM Usermodules.[user].[" & dbTableName & "]")
            End If
            Dim com As New System.Data.SqlClient.SqlCommand

            Dim j As Integer = 0
            Dim first As Boolean = False
            Dim batchCounter As Integer = 0

            Dim lines() As String = text.Split(Chr(10))
            TotalLines = lines.Count
            InsertedLine = 0

            Dim sql As String
            Dim sqlFields As New StringBuilder
            Dim sqlVals As New StringBuilder

            sql = "INSERT INTO " & "usermodules.[user].[" & dbTableName & "]( "

            For Each r As DataRow In _colConfig.Rows
                If r("name").ToString.ToUpper <> "ID" Then
                    '----------Building sql string
                    If first Then
                        sqlFields.Append(",")
                        sqlVals.Append(",")
                    End If
                    sqlFields.Append("[" & r("name") & "]")
                    sqlVals.Append("@" & r("name"))
                    first = True
                End If
            Next
            sql &= sqlFields.ToString & ") VALUES (" & sqlVals.ToString & ")"
            com.CommandText = sql

            For Each line As String In lines
                com.Parameters.Clear()
                j = 0
                Dim vals() As String = line.Split(vbTab)
                If vals.Length > 0 Then
                    'Skipping last row with '------------------------------'    text
                    If vals.Length > 1 Then
                        If vals(1).Contains("----") Then
                            Continue For
                        End If
                    End If
                    Try
                        For Each r As DataRow In _colConfig.Rows
                            If r("name").ToString.ToUpper <> "ID" Then
                                '----------Mapping Parameters
                                If r("visible").ToString = "Y" Then
                                    If j + 1 <= vals.Count Then
                                        vals(j) = vals(j).ToString.Replace(Chr(160), " ").Trim
                                        Select Case r("fieldtype").ToString
                                            Case "NUMBER"
                                                ' vals(j) = vals(j).Replace(" ", "").Replace(".", "").Replace(",", ".")
                                                vals(j) = vals(j).Replace(" ", "").Replace(",", ".")
                                            Case "DATE"
                                                Try
                                                    'vals(j) = Date.ParseExact(vals(j).Trim, "dd.MM.yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd")
                                                Catch ex As Exception
                                                End Try
                                            Case Else
                                        End Select
                                        com.Parameters.AddWithValue("@" & r("name").ToString, vals(j))
                                    Else
                                        If IsDBNull(r("Default_Value")) Then
                                            com.Parameters.AddWithValue("@" & r("name").ToString, "")
                                        ElseIf r("Default_Value").ToString = "TODAY" Then
                                            com.Parameters.AddWithValue("@" & r("name").ToString, DateTime.Now.ToString("yyyy-MM-dd"))
                                        ElseIf r("Default_Value").ToString = "NOW" Then
                                            com.Parameters.AddWithValue("@" & r("name").ToString, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                                        Else
                                            com.Parameters.AddWithValue("@" & r("name").ToString, r("Default_Value").ToString)
                                        End If
                                    End If
                                    j += 1
                                Else
                                    If IsDBNull(r("Default_Value")) Then
                                        com.Parameters.AddWithValue("@" & r("name").ToString, "")
                                    ElseIf r("Default_Value").ToString = "TODAY" Then
                                        com.Parameters.AddWithValue("@" & r("name").ToString, DateTime.Now.ToString("yyyy-MM-dd"))
                                    ElseIf r("Default_Value").ToString = "NOW" Then
                                        com.Parameters.AddWithValue("@" & r("name").ToString, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                                    Else
                                        com.Parameters.AddWithValue("@" & r("name").ToString, r("Default_Value").ToString)
                                    End If

                                End If
                                '------------------------------------
                            End If
                        Next
                        sqlf.NonQuery(com)

                        InsertedLine += 1
                    Catch ex As Exception

                    End Try
                End If
            Next

        End Sub

        Public Sub SaveHistory(parameters As SqlParameterCollection, rowid As Integer, userid As String, userip As String)
            Dim com As New SqlCommand
            Dim sql As New StringBuilder

            sql.AppendLine("INSERT INTO UserModulesHistory.[user]." & dbTableName & "(")
            Dim f As Boolean = False
            For Each p As SqlParameter In parameters
                If f Then sql.Append(",")
                sql.Append("[" & p.ParameterName & "]")
                f = True
            Next
            If Not parameters.Contains("id") Then
                sql.AppendLine(", id ")
            End If
            sql.AppendLine(", h_user_id, h_user_ip) VALUES ( ")
            f = False
            For Each p As SqlParameter In parameters
                If f Then sql.Append(",")
                sql.Append("@" & p.ParameterName & "")
                f = True
            Next
            If Not parameters.Contains("id") Then
                sql.AppendLine(", @id ")
            End If
            sql.AppendLine(", @h_user_id, @h_user_ip) ")
            With com.Parameters
                If Not parameters.Contains("id") Then
                    .AddWithValue("id", rowid)
                End If
                .AddWithValue("h_user_id", userid)
                .AddWithValue("h_user_ip", userip)
                For Each p As SqlParameter In parameters
                    .AddWithValue(p.ParameterName, p.Value)
                Next

            End With
            com.CommandText = sql.ToString
            sqlf.NonQuery(com)
        End Sub
        Public Function GetHistory(fieldName As String, rowid As Integer) As DataTable
            Dim sql As New StringBuilder
            sql.AppendLine("WITH T1 as( ")
            sql.AppendLine("SELECT ")
            sql.AppendLine("(RANK() OVER (ORDER BY h_id)) AS RNK, ")
            sql.AppendLine("t1.[" & fieldName & "] ")
            sql.AppendLine("FROM [User].[" & dbTableName & "] t1 ")
            sql.AppendLine("WHERE t1.id = " & rowid & " ")
            sql.AppendLine("AND t1.[" & fieldName & "] is not NULL ")
            sql.AppendLine(") , T2 as ( ")
            sql.AppendLine("SELECT ")
            sql.AppendLine("(RANK() OVER (ORDER BY h_id)) - 1 AS RNK, ")
            sql.AppendLine("t1.[" & fieldName & "], ")
            sql.AppendLine("t1.h_user_id, ")
            sql.AppendLine("t1.h_user_ip, ")
            sql.AppendLine("t1.h_change_date ")
            sql.AppendLine("FROM [User].[" & dbTableName & "] t1 ")
            sql.AppendLine("WHERE t1.id = " & rowid & " ")
            sql.AppendLine("AND t1.[" & fieldName & "] is not NULL ")
            sql.AppendLine(") ")
            sql.AppendLine("SELECT ")
            sql.AppendLine("ISNULL(T1.[" & fieldName & "], '-= ROW CREATED =-') as OLD_VALUE ")
            sql.AppendLine(", T2.[" & fieldName & "] as NEW_VALUE ")
            sql.AppendLine(", t2.h_user_id as CHANGED_BY ")
            sql.AppendLine(", T2.h_user_ip as ip ")
            sql.AppendLine(", T2.h_change_date as change_datetime ")
            sql.AppendLine("FROM T1 ")
            sql.AppendLine("FULL JOIN T2 ")
            sql.AppendLine("ON T1.RNK = T2.RNK ")
            sql.AppendLine("WHERE T2.[" & fieldName & "] is not null ")

            Return sqlf.GetData(sql.ToString)

        End Function

    End Class
End Namespace