ModuleMaster/modulemaster2.aspx.vb - main - ivan/ModMas

ModuleMaster/modulemaster2.aspx.vb
1 018 строк42 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data.SqlClient
Imports AjaxControlToolkit
Imports System.Data

Partial Class ModuleMaster
    Inherits System.Web.UI.Page

    Dim bRecreateTable As Boolean
    Private sqlf As New sharedClasses.dbFunctions.SqlFunctions

    Private refreshResultingTable As Boolean = False


    Private Function NormilizeName(name As String) As String
        Return name.ToUpper _
            .Replace(" ", "_") _
            .Replace(",", "_") _
            .Replace(".", "_") _
            .Replace(";", "_") _
            .Replace(":", "_") _
            .Replace("/", "_") _
            .Replace("\", "_") _
            .Replace("?", "_") _
            .Replace("!", "_") _
            .Replace("'", "_") _
            .Replace("""", "_") _
            .Replace("(", "_") _
            .Replace(")", "_") _
            .Replace("[", "_") _
            .Replace("]", "_") _
            .Replace("{", "_") _
            .Replace("}", "_") _
            .Replace("$", "_") _
            .Replace("@", "_") _
            .Replace("&", "_") _
            .Replace("%", "_") _
            .Replace("№", "No") _
            .Replace("#", "No") _
            .Replace("-", "_") _
            .Replace("*", "_") _
            .Replace("|", "_") _
            .Replace("^", "_") _
            .Replace("=", "_") _
            .Replace("+", "_") _
            .Replace("~", "_")
    End Function

#Region "Grid actions"
    Protected Sub ModuleRowSelected(ByVal rowNum As Integer) Handles edModule.RecordSelected
        edTable.SelectedIndex = -1
        edTable.UpdateFilters("moduleid", "=", "'" & edModule.GetCellContent(rowNum, 0) & "'")
        edTable.RefreshData(True)
        TabContainer1.ActiveTabIndex = 1
        tpTable.Visible = True

        tpFields.Visible = False
        tpViews.Visible = False

        tpFieldVaslues.Visible = False

        tpGraph.Visible = False

        tpGroupingText.Visible = False

        tpMailingGroup.Visible = False
        tpMailingSchedule.Visible = False


        tpMenu.Visible = False
        tpPivot.Visible = False

        hfModuleId.Value = edModule.GetCellContent(rowNum, 0)
        lblSelectedModuleName.Text = edModule.GetCellContent(rowNum, 1)

        upMain.Update()
    End Sub

    Protected Sub TableRowSelected(ByVal rownum As Integer) Handles edTable.RecordSelected
        'lblTableName.Text = edTable.GetCellContent(rownum, 2)

        hfTableId.Value = edTable.GetCellContent(rownum, 0)

        edFields.SelectedIndex = -1

        edFields.UpdateFilters("tableid", "=", "'" & hfTableId.Value & "'")
        edFields.RefreshData(True)

        edViews.UpdateFilters("table_id", "=", "'" & hfTableId.Value & "'")
        edViews.RefreshData(True)

        edMailSchedule.UpdateFilters("table_id", "=", "'" & hfTableId.Value & "'")
        edMailSchedule.RefreshData(True)

        edGroupingText.UpdateFilters("table_id", "=", "'" & hfTableId.Value & "'")
        edGroupingText.RefreshData(True)

        graphForTable.TableId = hfTableId.Value

        lnkTableView.Text = "View table"
        lnkTableView.NavigateUrl = "~/viewtable.aspx?tableid=" & edTable.GetCellContent(rownum, 0)

        lnkGraphView.Text = "View graph"
        lnkGraphView.NavigateUrl = "~/viewgraph.aspx?tableid=" & edTable.GetCellContent(rownum, 0)

        TabContainer1.ActiveTabIndex = 2

        tpFields.Visible = True
        tpViews.Visible = True

        tpGraph.Visible = True
        'tpPivot.Visible = True
        tpGroupingText.Visible = True

        tpMailingGroup.Visible = True
        tpMailingSchedule.Visible = True

        fsColumns.TableId = hfTableId.Value
        fsRows.TableId = hfTableId.Value
        fsValues.TableId = hfTableId.Value

        fsColumns.SelectedList.Clear()
        fsRows.SelectedList.Clear()
        fsValues.SelectedList.Clear()

        fsRows.BuildFiledsList()
        fsValues.BuildFiledsList()
        fsColumns.BuildFiledsList()

        'edPivot.UpdateFilters("tableid", "=", "'" & hfTableId.Value & "'")
        'edPivot.RefreshData(True)

        edGraph.UpdateFilters("tableid", "=", "'" & hfTableId.Value & "'")
        edGraph.RefreshData(True)


        lblSelectedTableName.Text = edTable.GetCellContent(rownum, 2)
        lblAssignedMenuId.Text = edTable.GetCellContent(rownum, 12)
        'sdsMenu.SelectCommand = sdsMenu.SelectCommand.Replace("@id", lblAssignedMenuId.Text)



        edResultingTable.TableName = edTable.GetCellContent(rownum, 4)
        edResultingTable.TableId = hfTableId.Value
        edResultingTable.AuthorityCheck()

        refreshResultingTable = True

        upMain.Update()
    End Sub

    Protected Sub FieldsRowSelected(ByVal rownum As Integer) Handles edFields.RecordSelected
        If edFields.GetCellContent(rownum, 5) = "DROPDOWNLIST" Then
            tpFieldVaslues.Visible = True
            TabContainer1.ActiveTabIndex = 4
        Else
            tpFieldVaslues.Visible = False
        End If
        edFieldsValues.UpdateFilters("fieldid", "=", "'" & edFields.GetCellContent(rownum, 0) & "'")
        edFieldsValues.RefreshData(True)
        upMain.Update()
    End Sub

    Protected Sub RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) Handles edFields.RowCommand
        Try
            Dim ComName As String = e.CommandName
            If Not {"Page", "Sort", ""}.Contains(ComName) Then
                Dim pos As Integer = e.CommandArgument
                'Dim tablename As String = gvTables.SelectedRow.Cells(4).Text
                Dim tableid As String = hfTableId.Value ' edFields.Filters("[TABLEID]").ToString.Replace("'", "").Replace("=", "") ' .TableId 'gvTables.SelectedRow.Cells(1).Text
                Dim sql As New StringBuilder
                Select Case e.CommandName
                    Case "UP"
                        If pos = 1 Then Exit Sub
                        sql.Append("DECLARE ")
                        sql.Append("  @pos INT ")
                        sql.Append("  SET @pos =  " & pos)
                        sql.Append("  DECLARE ")
                        sql.Append("    @tmp INT ")
                        sql.Append("    SET @tmp = -1 ")
                        sql.Append("    UPDATE [UserModules].[DBO].[TableFields] ")
                        sql.Append("    SET [Position]   = @tmp ")
                        sql.Append("    WHERE [Position] = (@pos-1) ")
                        sql.Append("    AND TableId      = " & tableid)
                        sql.Append("    UPDATE [UserModules].[DBO].[TableFields] ")
                        sql.Append("    SET [Position]   = ([Position] -1) ")
                        sql.Append("    WHERE [Position] = (@pos) ")
                        sql.Append("    AND TableId      = " & tableid)
                        sql.Append("    UPDATE [UserModules].[DBO].[TableFields] ")
                        sql.Append("    SET [Position]   = (@Pos) ")
                        sql.Append("    WHERE [Position] = @Tmp ")
                        sql.Append("    AND TableId      = " & tableid)
                    Case "DOWN"
                        If pos = 0 Then Exit Sub
                        sql.Append("DECLARE ")
                        sql.Append("  @pos INT ")
                        sql.Append("  SET @pos = " & pos)
                        sql.Append("  DECLARE ")
                        sql.Append("    @tmp INT ")
                        sql.Append("    SET @tmp = -1 ")
                        sql.Append("    UPDATE [UserModules].[DBO].[TableFields] ")
                        sql.Append("    SET [Position]   = @tmp ")
                        sql.Append("    WHERE [Position] = (@pos+1) ")
                        sql.Append("    AND TableId      = " & tableid)
                        sql.Append("    UPDATE [UserModules].[DBO].[TableFields] ")
                        sql.Append("    SET [Position]   = ([Position] +1) ")
                        sql.Append("    WHERE [Position] = (@pos) ")
                        sql.Append("    AND TableId      =  " & tableid)
                        sql.Append("    UPDATE [UserModules].[DBO].[TableFields] ")
                        sql.Append("    SET [Position]   = (@Pos) ")
                        sql.Append("    WHERE [Position] = @Tmp ")
                        sql.Append("    AND TableId      = " & tableid)
                    Case "FORMULA"
                        formulaEditor1.TableId = hfTableId.Value
                        formulaEditor1.FieldId = e.CommandArgument
                        formulaEditor1.Formula = sqlf.GetScalar("select isnull(FORMULA,'') as formula from [UserModules].[dbo].TableFields where id = " & e.CommandArgument)
                        formulaEditor1.Visible = True
                        formulaEditor1.BuildComboBx()
                        upMain.Update()
                End Select
                sqlf.NonQuery(sql.ToString)
                'edFields.DataBind()
                edFields.RefreshData(True)
                refreshResultingTable = True
            End If
        Catch ex As Exception

        End Try

    End Sub

    Protected Sub ViewPivot(sender As Object, e As GridViewCommandEventArgs) Handles edPivot.RowCommand
        Try
            Dim dr As Data.DataRow = sqlf.GetData("select * from usermodules.dbo.tablepivot where id=" & e.CommandArgument).rows(0)
            Dim tableId As String = dr("tableid")
            Dim pivotName As String = dr("pivotname")
            'Response.Write("<script type='text/javascript'>detailedresults=window.open('viewpivot.aspx?tableid=" & tableId & "&pivotname=" & pivotName & "');</script>")
            pivotForTable.TableId = tableId
            pivotForTable.PivotName = pivotName
            pivotForTable.BuildPivot()
            upPivot.Update()
        Catch ex As Exception

        End Try


    End Sub

    Private Sub SaveFormula(tableid As Integer, fieldId As Integer, formula As String) Handles formulaEditor1.SaveFormula
        sqlf.NonQuery("UPDATE [UserModules].[dbo].TableFields SET FORMULA = '" & formula & "' WHERE id=" & fieldId)
        formulaEditor1.Visible = False
        edFields.RefreshData()
        refreshResultingTable = True

        upMain.Update()
    End Sub

    Private Sub CancelFormula() Handles formulaEditor1.CancelFormula
        formulaEditor1.Visible = False
        upMain.Update()
    End Sub

#End Region

#Region "Add/Manage data"

    Protected Sub AddGRaphToTable(ByRef com As SqlCommand) Handles edGraph.NewRecordSaving
        com.Parameters("@tableId").Value = hfTableId.Value
        com.Parameters("@tableName").Value = edResultingTable.TableName
        If com.Parameters("@width").Value.ToString = "" Then
            com.Parameters("@width").Value = "0"
        End If
        If com.Parameters("@height").Value.ToString = "" Then
            com.Parameters("@height").Value = "0"
        End If
        If com.Parameters("@QTY_IN_A_ROW").Value.ToString = "" Then
            com.Parameters("@QTY_IN_A_ROW").Value = "0"
        End If

    End Sub

    Protected Sub AddNewFieldToTable(ByRef com As SqlCommand) Handles edFields.NewRecordSaving
        Dim lastpos As String = sqlf.GetScalar("SELECT isnull(MAX(position),0)+1 FROM [UserModules].[dbo].TableFields where tableid = " & hfTableId.Value)
        com.Parameters("@Position").Value = lastpos
        com.Parameters("@tableid").Value = hfTableId.Value
        com.Parameters("@dbFieldName").Value = NormilizeName(com.Parameters("@fieldname").Value.ToString.Trim)
        If CheckForColumnExists(com.Parameters("@dbFieldName").Value, hfTableId.Value) Then
            Dim rnd As New Random
            com.Parameters("@dbFieldName").Value &= "_" & rnd.Next(1000).ToString
        End If
        sqlf.NonQuery("USE UserModules ALTER TABLE " & edResultingTable.FullTableName & " ADD [" & com.Parameters("@dbfieldname").Value.ToString & "] nvarchar(max)")
        sqlf.NonQuery("USE UserModulesHistory ALTER TABLE [user]." & edResultingTable.TableName & " ADD [" & com.Parameters("@dbfieldname").Value.ToString & "] nvarchar(max)")

    End Sub

    Protected Sub DeletingFieldFromTable(ByVal itemid As Integer, ByRef cancelDelete As Boolean) Handles edFields.RecordDeleting

        Dim fieldName As String = sqlf.GetScalar("SELECT dbFieldName from [UserModules].[dbo].[TableFields] WHERE id = " & itemid)
        sqlf.NonQuery("USE UserModules ALTER TABLE " & edResultingTable.FullTableName & " DROP column [" & fieldName & "]")
        'sqlf.NonQuery("USE UserModulesHistory ALTER TABLE [user]." & edResultingTable.TableName & " DROP column [" & fieldName & "]")
    End Sub

    Protected Sub DeletedFieldFromTable(ByVal itemid As Integer) Handles edFields.RecordDeleted
        'sqlf.NonQuery("DECLARE @cnt INT SET @cnt =- 1 UPDATE [UserModules].[dbo].TableFields SET [Position] = @cnt, @cnt =@cnt + 1 WHERE id IN ( SELECT TOP 100 PERCENT id FROM [UserModules].[dbo].TableFields WHERE tableid = " & hfTableId.Value & " ORDER BY [Position] )")
        'edFields.RefreshData(True)
    End Sub

    Protected Sub AddingValuesForFiels(ByRef com As SqlCommand) Handles edFieldsValues.NewRecordSaving
        com.CommandText = "INSERT INTO [UserModules].dbo.[FieldsValues]([FIELDTEXT],[Enabled],[TableId],[FieldId],[FieldValue]) VALUES (@FIELDTEXT,@Enabled, @TableId,@FieldId,@FieldValue)"

        com.Parameters("@TableId").Value = edFields.GetCellContent(edFields.SelectedIndex, 1)
        com.Parameters("@FieldId").Value = edFields.GetCellContent(edFields.SelectedIndex, 0)
        com.Parameters("@FieldValue").Value = com.Parameters("@FieldText").Value
    End Sub

    Protected Sub AddedNewFieldToTable(fieldID As Integer) Handles edFields.NewRecordSaved
        addNewFieldToView(fieldID, hfTableId.Value)
        refreshResultingTable = True
    End Sub

    Protected Sub AddingNewMailingSchedule(ByRef com As SqlCommand) Handles edMailSchedule.NewRecordSaving

        If Not IsNothing(hfTableId.Value) Then
            com.Parameters("@Table_Id").Value = hfTableId.Value
        Else
            com.CommandText = ""
            com.Parameters.Clear()
        End If
    End Sub

    Private Function CheckForTableExists(ByVal tablename As String) As Boolean
        If sqlf.GetScalar("SELECT count(*) FROM UserModules.sys.tables where name ='" & tablename & "'") <> 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Function CheckForColumnExists(columnName As String, tableid As String) As Boolean
        If sqlf.GetScalar("select count(*) from UserModules.sys.columns where object_id in (select object_id from UserModules .sys.tables where name = (select DbTableName from UserModules.dbo.ModuleTables where id = " & tableid & ")) and name = '" & columnName & "'") <> 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Protected Sub CreateNewModule(ByRef com As SqlCommand) Handles edModule.NewRecordSaving
        com.Parameters("@CReateDate").Value = DateTime.Now
        com.Parameters("@Enabled").Value = "Y"
    End Sub
#Region "View"
    Protected Sub CreatingNewView(ByRef com As SqlCommand) Handles edViews.NewRecordSaving
        If hfTableId.Value > 0 Then
            Dim sql As New StringBuilder
            Dim viewId As Integer = sqlf.GetScalar("select isnull(max(view_id),0) + 1 from UserModules.dbo.VIEWS")
            sql.AppendLine("insert into UserModules.dbo.VIEWS ")
            sql.AppendLine("(view_name, ")
            sql.AppendLine("DESCRIPTION, ")
            sql.AppendLine("table_id, ")
            sql.AppendLine("field_name, ")
            sql.AppendLine("FIELD_ID, ")
            sql.AppendLine("enable, ")
            sql.AppendLine("[position], ")
            sql.AppendLine("visible, ")
            sql.AppendLine("filter, ")
            sql.AppendLine("SEARCH, ")
            sql.AppendLine("style, ")
            sql.AppendLine("view_id) ")

            sql.AppendLine("select ")
            sql.AppendLine("@view_name, ")
            sql.AppendLine("@description, ")
            sql.AppendLine(hfTableId.Value & " as tableid, ")
            sql.AppendLine("FieldName, ")
            sql.AppendLine("id as field_id, ")
            sql.AppendLine("Enabled, ")
            sql.AppendLine("[position], ")
            sql.AppendLine("Visible, ")
            sql.AppendLine("Filter, ")
            sql.AppendLine("SEARCH, ")
            sql.AppendLine("STYLE, ")
            sql.AppendLine(viewId & " ")
            sql.AppendLine("from UserModules.dbo.TableFields ")
            sql.AppendLine("where tableid = " & hfTableId.Value)

            com.CommandText = sql.ToString
        Else
            com.CommandText = ""
        End If
    End Sub

    Private Sub addNewFieldToView(fieldID As Integer, tableID As Integer)
        Dim viewId As DataTable = sqlf.GetData("select distinct view_id, view_name,DESCRIPTION  from UserModules.dbo.VIEWS where table_id=" & tableID)
        If Not IsNothing(viewId) Then
            Dim sql As New StringBuilder
            sql.AppendLine("insert into UserModules.dbo.VIEWS ")
            sql.AppendLine("(view_name, ")
            sql.AppendLine("DESCRIPTION, ")
            sql.AppendLine("table_id, ")
            sql.AppendLine("field_name, ")
            sql.AppendLine("FIELD_ID, ")
            sql.AppendLine("enable, ")
            sql.AppendLine("[position], ")
            sql.AppendLine("visible, ")
            sql.AppendLine("filter, ")
            sql.AppendLine("SEARCH, ")
            sql.AppendLine("style, ")
            sql.AppendLine("view_id) ")

            sql.AppendLine("select ")
            sql.AppendLine("@view_name, ")
            sql.AppendLine("@description, ")
            sql.AppendLine(tableID & " as tableid, ")
            sql.AppendLine("FieldName, ")
            sql.AppendLine("id as field_id, ")
            sql.AppendLine("Enabled, ")
            sql.AppendLine("[position], ")
            sql.AppendLine("Visible, ")
            sql.AppendLine("Filter, ")
            sql.AppendLine("SEARCH, ")
            sql.AppendLine("STYLE, ")
            sql.AppendLine("@viewId ")
            sql.AppendLine("from UserModules.dbo.TableFields ")
            sql.AppendLine("where id = " & fieldID)
            Dim com As New SqlCommand With {.CommandText = sql.ToString}

            For Each dr As DataRow In viewId.Rows
                With com.Parameters
                    .Clear()
                    .AddWithValue("view_name", dr("view_name"))
                    .AddWithValue("description", dr("description"))
                    .AddWithValue("viewid", dr("view_id"))
                End With
                sqlf.NonQuery(com)
            Next
        End If
    End Sub
    Protected Sub SelectingView() Handles edViews.GridFilterChanged
        If edViews.Filters.ContainsKey("[VIEW_ID]") Then
            edGroupingText.UpdateFilters("[VIEW_ID]", "=", edViews.Filters("[VIEW_ID]").ToString.Trim("=").Trim("N"))
            edGroupingText.RefreshData(True)
        Else
            edGroupingText.Filters.Remove("[VIEW_ID]")
        End If
    End Sub
#End Region

#Region "New table"

    Protected Sub CreateNewTable(ByRef com As SqlCommand) Handles edTable.NewRecordSaving
        Dim tableName As String = NormilizeName(com.Parameters("@TableName").Value.ToString.Trim)

        com.Parameters("@Enabled").Value = "Y"
        com.Parameters("@ModuleId").Value = hfModuleId.Value
        com.Parameters("@CReateDate").Value = DateTime.Now
        If hfModuleId.Value = 3 Then
            com.Parameters("@Schema").Value = "[dbo]"
        Else
            com.Parameters("@Schema").Value = "[user]"
        End If

        Dim sql As New StringBuilder
        If CheckForTableExists(tableName) Then
            Dim rnd As New Random
            tableName &= "_" & rnd.Next(1000).ToString
            'If bRecreateTable Then
            '    sqlf.NonQuery("drop table [UserModules].[user].[" & tableName & "]")
            'Else
            '    Exit Sub
            'End If
        End If

        com.Parameters("@DbTableName").Value = tableName

        sql.Append("USE [UserModules] ")
        sql.Append("SET ANSI_NULLS ON ")
        sql.Append("SET QUOTED_IDENTIFIER ON ")
        sql.Append("SET ANSI_PADDING ON ")

        If hfModuleId.Value = 3 Then
            sql.Append("CREATE TABLE [dbo].[" & tableName & "] ")
        Else
            sql.Append("CREATE TABLE [user].[" & tableName & "] ")
        End If

        sql.Append("  ( ")
        sql.Append("    [id] [INT] IDENTITY(1,1) NOT NULL, ")
        sql.Append("    [CREATED_BY] [varchar](50) ")
        sql.Append(", CONSTRAINT [PK_" & tableName & "] PRIMARY KEY CLUSTERED ([id] ASC ) ")
        sql.Append("WITH ")
        sql.Append("  ( ")
        sql.Append("    PAD_INDEX              = OFF, ")
        sql.Append("    STATISTICS_NORECOMPUTE = OFF, ")
        sql.Append("    IGNORE_DUP_KEY         = OFF, ")
        sql.Append("    ALLOW_ROW_LOCKS        = ON, ")
        sql.Append("    ALLOW_PAGE_LOCKS       = ON ")
        sql.Append("  ) ")
        sql.Append("  ON [PRIMARY] ")
        sql.Append("  ) ")
        sql.Append("  ON [PRIMARY]")
        sql.Append(vbNewLine)
        sql.Append("  SET ANSI_PADDING OFF ")

        sqlf.NonQuery(sql.ToString)

        sql.Clear()
        sql.Append("USE [UserModulesAttachments] ")
        sql.Append("SET ANSI_NULLS ON ")
        sql.Append("SET QUOTED_IDENTIFIER ON ")
        sql.Append("SET ANSI_PADDING ON ")
        sql.Append("CREATE TABLE [user].[" & tableName & "] ")
        sql.Append("  ( ")
        sql.Append("    [id] [INT] IDENTITY(1,1) NOT NULL, ")
        sql.Append("    [type] [VARCHAR](50) NOT NULL, ")
        sql.Append("    [parentId] [INT] NOT NULL, ")
        sql.Append("    [name] [nvarchar](max) NOT NULL, ")
        sql.Append("    [doc] [VARBINARY](MAX) NOT NULL, ")
        sql.Append("    [deleted] [CHAR](1) NOT NULL, ")
        sql.Append("    [thumb] varbinary(max), ")
        sql.Append("    CONSTRAINT [PK_" & tableName & "] PRIMARY KEY CLUSTERED ( [id] ASC ) ")
        sql.Append("WITH ")
        sql.Append("  ( ")
        sql.Append("    PAD_INDEX              = OFF, ")
        sql.Append("    STATISTICS_NORECOMPUTE = OFF, ")
        sql.Append("    IGNORE_DUP_KEY         = OFF, ")
        sql.Append("    ALLOW_ROW_LOCKS        = ON, ")
        sql.Append("    ALLOW_PAGE_LOCKS       = ON ")
        sql.Append("  ) ")
        sql.Append("  ON [PRIMARY] ")
        sql.Append("  ) ")
        sql.Append("  ON [PRIMARY] ")
        sql.Append("  SET Ansi_Padding OFF ALTER TABLE [user].[" & tableName & "] ADD CONSTRAINT [DF_" & tableName & "_deleted] DEFAULT ")
        sql.Append("  ( ")
        sql.Append("    'N' ")
        sql.Append("  ) ")
        sql.Append("  FOR [deleted]")
        sqlf.NonQuery(sql.ToString)
    End Sub

    Protected Sub CreatedNewTable(ByVal newTableId As Integer) Handles edTable.NewRecordSaved

        Dim sql As New StringBuilder
        'Dim newTableId As Integer = sqlf.GetScalar("SELECT @@IDENTITY")
        sql.Clear()
        sql.Append("INSERT INTO [UserModules].[DBO].[TableFields](")
        sql.Append("[TableId]")
        sql.Append(",[FieldName]")
        sql.Append(",[FieldDescription]")
        sql.Append(",[FieldType]")
        sql.Append(",[DbFieldName]")
        sql.Append(",[Enabled]")
        sql.Append(",[Position]")
        sql.Append(",[Visible]")
        sql.Append(",[Filter], [CREATED_BY], [DEFAULT_SORT]) VALUES (")
        sql.Append(newTableId)
        sql.Append(",'id'")
        sql.Append(",'System field'")
        sql.Append(",'INT'")
        sql.Append(",'id'")
        sql.Append(",'N'")
        sql.Append(",0")
        sql.Append(",'N'")
        sql.Append(",'N', 'SYSTEM','DESC')")
        sqlf.NonQuery(sql.ToString)

        sql.Clear()
        sql.Append("INSERT INTO [UserModules].[DBO].[TableFields](")
        sql.Append("[TableId]")
        sql.Append(",[FieldName]")
        sql.Append(",[FieldDescription]")
        sql.Append(",[FieldType]")
        sql.Append(",[DbFieldName]")
        sql.Append(",[Enabled]")
        sql.Append(",[Position]")
        sql.Append(",[Visible]")
        sql.Append(",[Filter], [CREATED_BY]) VALUES (")
        sql.Append(newTableId)
        sql.Append(",'Created By'")
        sql.Append(",'It is for user control.'")
        sql.Append(",'TEXT'")
        sql.Append(",'CREATED_BY'")
        sql.Append(",'N'")
        sql.Append(",1")
        sql.Append(",'N'")
        sql.Append(",'N', '" & Auth1.UserLogin & "')")
        sqlf.NonQuery(sql.ToString)

        sql.Clear()
        sql.Append("INSERT INTO [UserModules].[DBO].[Authorization](")
        sql.Append("tableId, fieldId, groupId, accesstype) VALUES (")
        sql.Append(newTableId & ", 0,7,'F')")
        sqlf.NonQuery(sql.ToString)
    End Sub

#End Region

    Dim fromDbFieldName As String = ""
    Dim toDbFieldName As String = ""

    Protected Sub CreateNewGroupingText(ByRef com As SqlCommand) Handles edGroupingText.NewRecordSaving
        com.Parameters("@table_id").Value = hfTableId.Value

        'com.Parameters("@view_id").Value = "-1"

        fromDbFieldName = com.Parameters("@START_COLUMN_NAME").Value
        toDbFieldName = com.Parameters("@END_COLUMN_NAME").Value


        com.Parameters("@START_COLUMN_NAME").Value = sqlf.GetScalar("SELECT DISTINCT FieldName FROM UserModules.dbo.TableFields WHERE tableid = " & com.Parameters("@TABLE_ID").Value & " AND DBFieldName='" & fromDbFieldName & "'")
        com.Parameters("@END_COLUMN_NAME").Value = sqlf.GetScalar("SELECT DISTINCT FieldName FROM UserModules.dbo.TableFields WHERE tableid = " & com.Parameters("@TABLE_ID").Value & " AND DBFieldName='" & toDbFieldName & "'")
    End Sub

    Protected Sub CreatedNewGroupingText(ByVal newRecordId As Integer) Handles edGroupingText.NewRecordSaved
        sqlf.NonQuery("UPDATE UserModules.dbo.GROUPING_TEXT SET START_COLUMN_DB_NAME='" & fromDbFieldName & "', END_COLUMN_DB_NAME='" & toDbFieldName & "' WHERE id=" & newRecordId)
    End Sub


    Protected Sub ChangeElement(tableName As String,
                                fieldName As String,
                                rowId As String,
                                value As String,
                                fieldType As String,
                                byButton As Boolean,
                                text As String) Handles edTable.SavedElement
        Select Case fieldName
            Case "KEEP_HISTORY"

                Dim tblName As String = sqlf.GetScalar("SELECT DBTABLENAME FROM UserModules.dbo.MODULETABLES WHERE id = " & rowId)
                tblName = tblName.Trim

                Dim tableExist As Boolean =
                (sqlf.GetScalar("USE UserModulesHistory SELECT COUNT(*) FROM sys.tables WHERE name = '" &
                                tblName.Replace("[", "").Replace("]", "") & "'") > 0)
                If Not tableExist Then
                    Dim sql As New StringBuilder
                    sql.AppendLine("USE UserModulesHistory ")
                    sql.AppendLine("select top 0 * ")
                    sql.AppendLine("INTO [user].[" & tblName & "] ")
                    sql.AppendLine("from UserModules.[user].[" & tblName & "] ")
                    'sql.AppendLine("GO ")
                    sql.AppendLine("ALTER TABLE [user].[" & tblName & "] DROP column id ")
                    'sql.AppendLine("GO ")
                    sql.AppendLine("ALTER TABLE [user].[" & tblName & "] ADD id int NOT NULL ")
                    'sql.AppendLine("GO ")
                    sql.AppendLine("ALTER TABLE [User].[" & tblName & "] ADD h_id int NOT NULL IDENTITY (1, 1) ")
                    'sql.AppendLine("GO ")
                    sql.AppendLine("ALTER TABLE [User].[" & tblName & "] ADD CONSTRAINT ")
                    sql.AppendLine("	PK_" & tblName & " PRIMARY KEY ")
                    sql.AppendLine("	(h_id) WITH( ")
                    sql.AppendLine("	STATISTICS_NORECOMPUTE = OFF, ")
                    sql.AppendLine("	IGNORE_DUP_KEY = OFF, ")
                    sql.AppendLine("	ALLOW_ROW_LOCKS = ON, ")
                    sql.AppendLine("	ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ")
                    'sql.AppendLine("GO ")
                    sql.AppendLine("ALTER TABLE [User].[" & tblName & "] ")
                    sql.AppendLine("ADD [h_change_date] datetime NOT NULL DEFAULT getdate() ")

                    sql.AppendLine("ALTER TABLE [User].[" & tblName & "] ")
                    sql.AppendLine("ADD [h_user_id] nvarchar(max) NOT NULL DEFAULT 'SYSTEM' ")

                    sql.AppendLine("ALTER TABLE [User].[" & tblName & "] ")
                    sql.AppendLine("ADD [h_user_ip] nvarchar(max) NOT NULL DEFAULT '::1'")

                    'sql.AppendLine("GO ")
                    sql.AppendLine("declare @fields varchar(max) ")
                    sql.AppendLine("set @fields=( ")
                    sql.AppendLine("select stuff(( ")
                    sql.AppendLine("select ',[' + cast(name as varchar(max)) + ']' ")
                    sql.AppendLine("from sys.columns ")
                    sql.AppendLine("where object_id in ")
                    sql.AppendLine("(select object_id from sys.tables WHERE name = '" & tblName & "') ")
                    sql.AppendLine("AND name not in ('id','h_id','h_change_date','h_user_id','h_user_ip') ")
                    sql.AppendLine("ORDER BY column_id for XML PATH('') ")
                    sql.AppendLine("),1,1,'')) ")
                    sql.AppendLine("EXEC( ")
                    sql.AppendLine("'INSERT INTO UserModulesHistory.[user].[" & tblName & "] ( ' + @fields + ',[id]) ")
                    sql.AppendLine("SELECT ' + @fields + ',[id] FROM UserModules.[user].[" & tblName & "]' ")
                    sql.AppendLine(") ")
                    'sql.AppendLine("GO ")
                    sqlf.NonQuery(sql.ToString)
                End If
        End Select

    End Sub

#End Region

    'For excel download
    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            TabContainer1.ActiveTabIndex = 0
        End If
        Try
            edResultingTable.TableId = hfTableId.Value
        Catch ex As Exception

        End Try

        If Auth1.UserLogin = "" Then
            If Not IsNothing(Request.QueryString("orchUserLogin")) Then
                Dim userlogin As String = Request.QueryString("orchUserLogin")
                Dim username As String = sqlf.GetScalar("SELECT userName FROM [SerkIntranet].[dbo].webUsers WHERE UserLogin = '" & userlogin & "'")
                Auth1.UserLogin = userlogin
                Auth1.UserName = username
            End If
        Else
            If Not IsNothing(Request.QueryString("orchUserLogin")) Then
                If Request.QueryString("orchUserLogin") <> Auth1.UserLogin Then
                    Dim userlogin As String = Request.QueryString("orchUserLogin")
                    Dim username As String = sqlf.GetScalar("SELECT userName FROM [SerkIntranet].[dbo].webUsers WHERE UserLogin = '" & userlogin & "'")
                    Auth1.UserLogin = userlogin
                    Auth1.UserName = username
                End If
            End If
        End If

        If Auth1.UserLogin = "" Then
            edModule.IsReadOnly = True
            edTable.IsReadOnly = True
            edFields.IsReadOnly = True
            edFieldsValues.IsReadOnly = True
            edResultingTable.IsReadOnly = True
            edViews.IsReadOnly = True
        Else
            edModule.IsReadOnly = False
            edTable.IsReadOnly = False
            edFields.IsReadOnly = False
            edFieldsValues.IsReadOnly = False
            edResultingTable.IsReadOnly = False

            edModule.UserName = Auth1.UserLogin
            edTable.UserName = Auth1.UserLogin
            edFields.UserName = Auth1.UserLogin

            edViews.UserName = Auth1.UserLogin

            edFieldsValues.UserName = Auth1.UserLogin
            edResultingTable.UserName = Auth1.UserLogin
            edGraph.UserName = Auth1.UserLogin
            edPivot.UserName = Auth1.UserLogin
            edGroupingText.UserName = Auth1.UserLogin

            edMailGroup.UserName = Auth1.UserLogin
            edMailSchedule.UserName = Auth1.UserLogin
        End If

        edFields.UpdateSorting("Position", "ASC")

        lblMessage.Text = ""
    End Sub

#Region "Menu Editor"
    Protected Sub ddlMenu1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMenu1.SelectedIndexChanged
        If ddlMenu1.SelectedValue <> -1 Then
            ddlMenu2.DataTextField = "MenuItem"
            ddlMenu2.DataValueField = "MenuItemId"
            ddlMenu2.DataSource = BuildMenuComboBox("", ddlMenu1.SelectedValue)
            ddlMenu2.DataBind()
            ddlMenu2.Visible = True
        Else
            ddlMenu2.Visible = False
        End If
        ddlMenu3.Visible = False
        ddlMenu4.Visible = False

        ShowDeleteButton(lblAssignedMenuId.Text, ddlMenu1.SelectedValue.ToString, ibDeleteMenuItem)
    End Sub

    Protected Sub ddlMenu2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMenu2.SelectedIndexChanged
        If ddlMenu2.SelectedValue <> -1 Then
            ddlMenu3.DataTextField = "MenuItem"
            ddlMenu3.DataValueField = "MenuItemId"
            ddlMenu3.DataSource = BuildMenuComboBox("", ddlMenu2.SelectedValue)
            ddlMenu3.DataBind()
            ddlMenu3.Visible = True
        Else
            ddlMenu3.Visible = False
        End If
        ddlMenu4.Visible = False

        ShowDeleteButton(lblAssignedMenuId.Text, ddlMenu2.SelectedValue.ToString, ibDeleteMenuItem)
    End Sub

    Protected Sub ddlMenu3_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMenu3.SelectedIndexChanged
        If ddlMenu3.SelectedValue <> -1 Then
            ddlMenu4.DataTextField = "MenuItem"
            ddlMenu4.DataValueField = "MenuItemId"
            ddlMenu4.DataSource = BuildMenuComboBox("", ddlMenu3.SelectedValue)
            ddlMenu4.DataBind()
            ddlMenu4.Visible = True
        Else
            ddlMenu4.Visible = False
        End If

        ShowDeleteButton(lblAssignedMenuId.Text, ddlMenu3.SelectedValue.ToString, ibDeleteMenuItem)
    End Sub

    Protected Sub ddlMenu4_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMenu4.SelectedIndexChanged
        ShowDeleteButton(lblAssignedMenuId.Text, ddlMenu4.SelectedValue.ToString, ibDeleteMenuItem)
    End Sub

    Private Function BuildMenuComboBox(ByVal MenuItemText As String, ByVal parentMenuId As Integer) As System.Data.DataTable
        Dim con As New MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings("portalConnectionString").ConnectionString)
        Dim sql As New StringBuilder
        sql.Append("SELECT '")
        sql.Append(MenuItemText)
        sql.Append("' as MenuItem, -1 as MenuItemID UNION ALL ")
        sql.Append("SELECT name_menu, id from wms_menu WHERE parent_id=")
        sql.Append(parentMenuId)
        Try
            Dim com As New MySql.Data.MySqlClient.MySqlCommand(sql.ToString, con)
            Dim da As New MySql.Data.MySqlClient.MySqlDataAdapter(com)
            Dim ds As New Data.DataTable
            con.Open()
            da.Fill(ds)
            con.Close()
            Return ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Private Function CreateMenuItem(ByVal MenuName As String, ByVal ParentId As Integer, ByVal params As String, ByVal moduleId As Integer, ByVal actionID As Integer) As Integer
        Dim con As New MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings("portalConnectionString").ConnectionString)
        Dim sql As New StringBuilder
        sql.Append("INSERT INTO wms_menu (name_menu, parent_id, level, params, module_id, id_action) VALUES (")
        sql.Append("'" & MenuName & "',")
        sql.Append("'" & ParentId & "',")
        sql.Append("'999',")
        sql.Append("'" & params & "',")
        sql.Append(moduleId & ",")
        sql.Append(actionID)
        sql.Append(")")
        Dim insertedRowId As Integer = -1
        Dim com As New MySql.Data.MySqlClient.MySqlCommand(sql.ToString, con)
        Try
            con.Open()
            com.ExecuteNonQuery()
            com.CommandText = "SELECT @@IDENTITY"
            insertedRowId = com.ExecuteScalar
            con.Close()
        Catch ex As Exception

        End Try
        Return insertedRowId
    End Function

    Private Sub ShowDeleteButton(ByVal assignedMenu As String, ByVal selectedMenu As String, ByVal ib As ImageButton)
        If assignedMenu.Contains(selectedMenu) Then
            ib.Visible = True
        Else
            ib.Visible = False
        End If
    End Sub

#End Region
    Protected Sub ibSaveMenu_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ibSaveMenu.Click
        Dim cb As ComboBox
        Dim parent_id As Integer
        If ddlMenu4.Visible Then
            cb = ddlMenu4
            parent_id = ddlMenu3.SelectedValue
        ElseIf ddlMenu3.Visible Then
            cb = ddlMenu3
            parent_id = ddlMenu2.SelectedValue
        ElseIf ddlMenu2.Visible Then
            cb = ddlMenu2
            parent_id = ddlMenu1.SelectedValue
        Else
            cb = ddlMenu1
            parent_id = 0
        End If

        Dim txt As String = cb.Text
        If txt.Trim <> "" And txt <> "-1" Then
            Dim createdMenuID As Integer = CreateMenuItem(cb.Text, parent_id, "&url=http://serkweb.serk.lan:8080/modulemaster/viewtable.aspx?tableid=" & hfTableId.Value, 7, 402)
            If createdMenuID <> -1 Then
                Call sqlf.NonQuery("UPDATE [usermodules].[dbo].[moduletables] SET assigned_menu_id=isnull(assigned_menu_id,'') + '|' + '" & createdMenuID & "' WHERE id=" & hfTableId.Value)
            End If

            cb.SelectedIndex = -1
            cb.DataSource = BuildMenuComboBox("", parent_id)
            cb.DataBind()
            Try
                cb.Items.FindByText(txt).Selected = True
            Catch ex As Exception

            End Try
            lblMessage.Text = "Menu item '" & txt & "' created~~"
        Else
            lblMessage.Text = "You shoul enter menu name!"
        End If
        'upMenu.Update()
    End Sub

    Protected Sub ibCreateMenuItem_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ibCreateMenuItem.Click
        Dim cb As ComboBox
        Dim nextCb As ComboBox
        Dim parent_id As Integer
        If ddlMenu4.Visible Then
            cb = ddlMenu4
            parent_id = ddlMenu3.SelectedValue
            nextCb = Nothing
        ElseIf ddlMenu3.Visible Then
            cb = ddlMenu3
            parent_id = ddlMenu2.SelectedValue
            nextCb = ddlMenu4
        ElseIf ddlMenu2.Visible Then
            cb = ddlMenu2
            parent_id = ddlMenu1.SelectedValue
            nextCb = ddlMenu3
        Else
            cb = ddlMenu1
            parent_id = 0
            nextCb = ddlMenu2
        End If

        Dim txt As String = cb.Text
        If txt.Trim <> "" And txt <> "-1" Then
            Dim createdMenuID As Integer = CreateMenuItem(cb.Text, parent_id, "", 7, 77)

            cb.SelectedIndex = -1
            cb.DataTextField = "MenuItem"
            cb.DataValueField = "MenuItemId"
            cb.DataSource = BuildMenuComboBox("", parent_id)
            cb.DataBind()
            Try
                cb.Items.FindByText(txt).Selected = True
            Catch ex As Exception

            End Try

            If Not IsNothing(nextCb) Then
                nextCb.DataTextField = "MenuItem"
                nextCb.DataValueField = "MenuItemId"
                nextCb.DataSource = BuildMenuComboBox("", createdMenuID)
                nextCb.DataBind()
                nextCb.Visible = True
            End If
            ' upMenu.Update()
            lblMessage.Text = "Menu node '" & txt & "' created~~"
        Else
            lblMessage.Text = "You shoul enter menu name!"
        End If
    End Sub

    Protected Sub ibRefreshMenu_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ibRefreshMenu.Click
        ddlMenu1.Visible = True
        ddlMenu1.DataTextField = "MenuItem"
        ddlMenu1.DataValueField = "MenuItemId"
        ddlMenu1.DataSource = BuildMenuComboBox("", 0)
        ddlMenu1.DataBind()
        ddlMenu2.Visible = False
        ddlMenu3.Visible = False
        ddlMenu4.Visible = False
        'lblAssignedMenuId.Text = DateTime.Now.ToString
        '        upMenu.Update()
    End Sub

    Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
        If refreshResultingTable Then
            edResultingTable.Filters.Clear()
            edResultingTable.SortParameters.Clear()
            edResultingTable.BuildColumnsConfiguration()
            edResultingTable.RefreshFilters()
            edResultingTable.RefreshData(True)
        Else
            edResultingTable.Refresh()
        End If

        graphForTable.DrawGraph()
    End Sub

    Protected Sub ibSavePivot_Click(sender As Object, e As ImageClickEventArgs) Handles ibSavePivot.Click
        If txtPivotName.Text <> "" Then
            Dim sql As String = "INSERT INTO UserModules.dbo.TablePivot " & _
                "(tableid, dbtablename, created_by ,pivotname, dbFieldName, [type], [function]) values " & _
                "(@tableid, @dbtablename, @created_by, @pivotname, @dbFieldname, @type, @function)"
            Dim com As New SqlCommand
            com.CommandText = sql

            With com.Parameters
                .AddWithValue("tableid", hfTableId.Value)
                .AddWithValue("dbtablename", edResultingTable.TableName)
                .AddWithValue("created_by", Auth1.UserLogin)
                .AddWithValue("pivotname", txtPivotName.Text)
                If fsColumns.SelectedList.Count > 0 Then
                    .AddWithValue("dbFieldName", fsColumns.SelectedList(0).Value)
                    .AddWithValue("type", "COLUMN")
                    .AddWithValue("function", "")

                    sqlf.NonQuery(com)
                End If
            End With

            If fsRows.SelectedList.Count > 0 Then
                com.Parameters("dbfieldname").Value = fsRows.SelectedList(0).Value
                com.Parameters("type").Value = "ROW"
                sqlf.NonQuery(com)
            End If

            If fsValues.SelectedList.Count > 0 Then
                com.Parameters("dbfieldname").Value = fsValues.SelectedList(0).Value
                com.Parameters("type").Value = "VALUE"
                com.Parameters("function").Value = "SUM"
                sqlf.NonQuery(com)
            End If

        End If
    End Sub

    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete
        edResultingTable.CreateGroupingText()
    End Sub
End Class