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

ModuleMaster/usercontrols/editablegridnew.ascx.vb
3 819 строк168 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.Collections.Specialized
Imports System.IO
Imports System.Data.SqlClient
Imports AjaxControlToolkit
Imports System.Globalization
Imports sharedClasses.dbFunctions
Imports edGridNamespace
Imports System.Web.Script.Serialization
Imports sharedClasses.CreateExcelFile

Partial Class UserControls_editableGridNew
    Inherits System.Web.UI.UserControl
#Region "Private"
    Private dateFormat As String = "yyyy-MM-dd"
    Private commandColumns As New Collection
    Private idColumn As Integer = 0
    Private skipColumns() As Integer = {0}
    Private Const Separator As String = "$"
    Private Const UserSeparator As String = "x"

    'Will make one table Fiedl Config

    Public FieldList As New Collection
    Private FieldTypes As New Collection
    Private FieldFormulas As New Collection
    Private FieldVisibility As New Collection
    Private FieldVisibilityByName As New Collection
    Private FieldEnabled As New Collection
    '-----------------------------------
    'For pager
    Private Property currentPageIndex As Integer
        Set(value As Integer)
            GridView1.Attributes.Add("currentPageIndex", value)
        End Set
        Get
            If IsNothing(GridView1.Attributes("currentPageIndex")) Then
                GridView1.Attributes.Add("currentPageIndex", 0)
            End If
            Return GridView1.Attributes("currentPageIndex")
        End Get
    End Property

    Private currentDataRecords As Integer = 0
    Private totalDataRecords As Integer = 0
    Private totalPages As Integer = 1
    '------------------------------------------------------------------------

    Private NewValues As New Collection
    'Private ColumnsConfiguration As System.Data.DataTable
    Private Property thisInstanceId As String
        Set(value As String)
            InstanceId.Value = value
        End Set
        Get
            If InstanceId.Value = "" Then
                InstanceId.Value = Guid.NewGuid().ToString
            End If
            Return InstanceId.Value
        End Get
    End Property

    Private Property ColumnsConfiguration As System.Data.DataTable
        Set(value As System.Data.DataTable)
            Session.Add("config_" & thisInstanceId, value)
        End Set
        Get
            Return Session("config_" & thisInstanceId)
        End Get

    End Property

    Private bReadOnly As Boolean = False

    Private allowRowAdd As Boolean = False
    Private allowRowDelete As Boolean = False
    Private allowCellsEdit As Boolean = False


    Private ShowCheckBoxAsText As Boolean = False

    Public accessType As String = "R"
    Public fields As String = ""

    Private Const BatchSize As Integer = 100

    Private ExcelDownloadNow As Boolean = False
    Private FilesAsLinks As Boolean = False

    Private WithInvisibleFields As Boolean = False

    Private emptyFlag As Boolean = False
    Private _schema As String = "user"
    Private _tableParams As DataRow

    Private sqlf As New SqlFunctions
#End Region

#Region "Public Properties"
    Public ReadOnly Property gridNotice As Panel
        Get
            Return noticeBaloon
        End Get
    End Property

    ' --------------  Experimental ------------------------
    Private _config As edGridNamespace.tableConfig = Nothing

    Public ReadOnly Property Actions As PlaceHolder
        Get
            Return phActions
        End Get
    End Property

    Public ReadOnly Property HeaderRow As GridViewRow
        Get
            Return GridView1.HeaderRow
        End Get
    End Property

    Public ReadOnly Property rows As GridViewRowCollection
        Get
            Return GridView1.Rows
        End Get
    End Property
    Public ReadOnly Property config As edGridNamespace.tableConfig
        Get
            If _tableId <> 0 Then
                If IsNothing(Session("config_" & _tableId)) Then
                    _config = New tableConfig(_tableId)
                    Session.Add("config_" & _tableId, _config)
                Else
                    _config = Session("config_" & _tableId)
                End If
                Return _config
            End If
            Return Nothing
        End Get
    End Property
    '-------------------------------------------------------------

    Public ReadOnly Property Grid As GridView
        Get
            Return GridView1
        End Get
    End Property

    Public ReadOnly Property SelectConditionByFilters As String
        Get
            Return BuildSelectConditionByFilters()
        End Get
    End Property

    Public Property refresh_after_event As String
        Get
            Return pr_refresh_after_event.Value
        End Get
        Set(value As String)
            pr_refresh_after_event.Value = value
        End Set

    End Property

    Public Property IsFiltersVisible As Boolean
        Set(value As Boolean)
            tblFilters.Visible = value
        End Set
        Get
            Return tblFilters.Visible
        End Get
    End Property

    Public Property Caption As String
        Set(value As String)
            GridView1.Caption = value
        End Set
        Get
            Return GridView1.Caption
        End Get
    End Property

    Public Property Message As String
        Set(value As String)
            lblMessageText.Text = value
        End Set
        Get
            Return lblMessageText.Text
        End Get
    End Property

    Public ReadOnly Property SubReport As PlaceHolder
        Get
            Return phSubReport
        End Get
    End Property

    Public Property typedMessage As String()
        Set(value As String())
            If value.Count > 1 Then
                lblMessageText.Text = value(1)
                Select Case value(0)
                    Case "ERROR"
                        lblMessageText.BackColor = Drawing.Color.Coral
                    Case "WARNING"
                        lblMessageText.BackColor = Drawing.Color.Yellow
                    Case "INFO"
                        lblMessageText.BackColor = Drawing.Color.DeepSkyBlue
                    Case Else
                        lblMessageText.BackColor = Drawing.Color.Transparent
                End Select
            End If
        End Set
        Get
            Return {lblMessageText.Text}
        End Get
    End Property

    Public ReadOnly Property FiltersPlace As PlaceHolder
        Get
            Return phFilters
        End Get
    End Property

    Public Property InsertVisible As Boolean
        Set(value As Boolean)
            AddNewItem.Visible = value
        End Set
        Get
            Return AddNewItem.Visible
        End Get
    End Property

    Public Property MailSendVisible As Boolean
        Set(value As Boolean)
            ibSendEmail.Visible = value
        End Set
        Get
            Return ibSendEmail.Visible
        End Get
    End Property

    Public Property DeleteAllVisible As Boolean
        Set(value As Boolean)
            ibDeleteData.Visible = value
            pnlDeleteAll.Visible = value
        End Set
        Get
            Return ibDeleteData.Visible
        End Get
    End Property

    Public Property IsReadOnly As Boolean
        Set(value As Boolean)
            bReadOnly = value
        End Set
        Get
            Return bReadOnly
        End Get
    End Property

    Public Property SelectedIndex As Integer
        Set(value As Integer)
            GridView1.SelectedIndex = value
        End Set
        Get
            Return GridView1.SelectedIndex
        End Get
    End Property

    Public Property debugMode As Boolean

    Public Function GetCellContent(rowNum As Integer, cellNum As Integer) As String
        Return GridView1.Rows(rowNum).Cells(cellNum).Text
    End Function

    Public Property PageSize As Integer
        Set(value As Integer)
            GridView1.PageSize = value
        End Set
        Get
            Return GridView1.PageSize
        End Get
    End Property

    Public Property Width As Unit
        Set(value As Unit)
            GridView1.Width = value
        End Set
        Get
            Return GridView1.Width
        End Get
    End Property

    Protected sqlQuery As String = ""

    Private _tableId As Integer = -1

    Public Property TableId As Integer
        Set(ByVal value As Integer)
            _tableId = value
            lblTableId.Value = value
        End Set
        Get
            Return _tableId
        End Get
    End Property

    Public Property ViewID As Integer
        Set(value As Integer)
            lblViewId.Value = value
        End Set
        Get
            If IsNothing(lblViewId.Value) Then
                Return -1
            ElseIf IsNumeric(lblViewId.Value) Then
                Return lblViewId.Value
            Else
                Return -1
            End If
        End Get
    End Property

    Public Property TableName As String
        Set(ByVal value As String)
            lblTableName.Value = value
        End Set
        Get
            Return lblTableName.Value
        End Get
    End Property

    Public ReadOnly Property TableDescription As String
        Get
            Return lblCaption.Text
        End Get
    End Property

    Public Property UserName As String
        Set(ByVal value As String)
            hfUserName.Value = value
        End Set
        Get
            Return hfUserName.Value
        End Get
    End Property

    Public Sub BuildColumnsConfiguration()
        ColumnsConfiguration = GetColumnsConfiguration(FullTableName, ViewID)
        _config = New tableConfig(_tableId)
        Session.Add("config_" & _tableId, _config)
    End Sub

    Private Sub BuildAuxilaryConfig()
        'Building auxilary tables
        Dim gridColCount As Integer = 0
        Dim colCount As Integer = 0
        If Not IsNothing(ColumnsConfiguration) Then
            For Each dr As DataRow In ColumnsConfiguration.Rows
                If Not commandColumns.Contains(gridColCount) Then

                    If Not skipColumns.Contains(colCount) Then
                        Try

                            If fields <> "" Then
                                If Not fields.Contains(dr("DbFieldName")) Then
                                    dr("enabled") = "N"
                                End If
                            End If

                            If Not FieldList.Contains(colCount) Then
                                FieldList.Add(dr("dbfieldname"), colCount)
                            End If

                            If Not FieldTypes.Contains(colCount) Then
                                FieldTypes.Add(dr("FieldType"), colCount)
                            End If
                            If Not FieldFormulas.Contains(colCount) Then
                                FieldFormulas.Add(dr("formula"), colCount)
                            End If
                            If Not FieldVisibility.Contains(colCount) Then
                                FieldVisibility.Add(dr("visible"), colCount)
                                FieldVisibilityByName.Add(dr("visible"), dr("DbFieldName"))
                            End If
                            If Not FieldEnabled.Contains(colCount) Then
                                FieldEnabled.Add(dr("enabled"), colCount)
                            End If
                        Catch ex As Exception
                        End Try
                    End If
                    colCount += 1

                End If
                gridColCount += 1
            Next
        End If
    End Sub


    Public DataBindingMode As String = "datatable" '    datareader

    Private Const SystemSettingsStoragePrefix As String = "[UserModulesAttachments].[dbo]."
    Public _AttachmentsStoragePrefix As String = "[UserModulesAttachments]"
    Public _UserDataStoragePrefix As String = "[UserModules]"

    Public ReadOnly Property AttachmentsStoragePrefix As String
        Get
            Return _AttachmentsStoragePrefix & "." & _schema
        End Get
    End Property

    Public ReadOnly Property UserDataStoragePrefix As String
        Get
            Return _UserDataStoragePrefix & "." & _schema
        End Get
    End Property

    Public ReadOnly Property FullTableName As String
        Get
            Return UserDataStoragePrefix & "." & TableName
        End Get
    End Property

    Private _clientScriptManager As ClientScriptManager

    Public WriteOnly Property ClientScriptMan As ClientScriptManager
        Set(value As ClientScriptManager)
            _clientScriptManager = value
        End Set
    End Property

    Public Property Filters As Hashtable
        Set(value As Hashtable)
            SaveFilterValues(value)
        End Set
        Get
            Dim ht As Hashtable = ReadFilterValues()
            If ht Is Nothing Then
                ht = New Hashtable
                SaveFilterValues(ht)
            End If
            Return ReadFilterValues()
        End Get
    End Property

    Public Property rawFilters As Hashtable
        Set(value As Hashtable)
            SaveRawFilterValues(value)
        End Set
        Get
            Dim ht As Hashtable = ReadRawFilterValues()
            If ht Is Nothing Then
                ht = New Hashtable
                SaveRawFilterValues(ht)
            End If
            Return ReadRawFilterValues()
        End Get
    End Property

#End Region

#Region "Events"

    Public Event RequestForGraphData(ByRef graphData As String)
    'Public Event NewRecordSaving(ByRef params As SqlParameterCollection)
    Public Event NewRecordSaving(ByRef com As SqlCommand)
    Public Event NewRecordSaved(newRecordId As Integer)

    Public Event NewRecordCancel()

    Public Event RecordSelected(selecterRowIndex As Integer)
    Public Event RecordDeleting(ByVal itemid As Integer, ByRef cancelDelete As Boolean)
    Public Event RecordDeleted(ByVal itemid As Integer)

    Public Event MassDeleting(ByVal condition As String, ByRef cancelDelete As Boolean)

    Public Event DataSelecting(ByRef sql As String)
    Public Event DataSelected(dt As DataTable)

    Public Event RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs)
    Public Event ActionRequest(ByVal sender As Object, ByVal e As EventArgs)

    Public Event AuthorityCheckComplete(IsReadOnly As Boolean)

    Public Event GridFilterChanged()

    Public Event RowDataBinding(ByRef row As GridViewRow, ByRef canEdit As Boolean)
    Public Event RowDataBoundComplete(ByRef row As GridViewRow)

    Public Event SavingElement(tableName As String, fieldName As String, rowId As String, value As String, fieldType As String, byButton As Boolean, text As String, ByRef cancelSave As Boolean)
    Public Event SavedElement(tableName As String, fieldName As String, rowId As String, value As String, fieldType As String, byButton As Boolean, text As String)

    Public Event AddedAttachment(tableName As String, fieldName As String, rowid As String)
    Public Event DeletedAttachment(tableName As String, fieldName As String, rowid As String)

    Public Event DataWasChanged(tablename As String, rowId As String)

#End Region

#Region "Page Events"
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        lblStatus.Text = ""
        AuthorityCheck()

        If Not IsPostBack Then
            gridStyle.Attributes.Add("href", "http://" & HttpContext.Current.Request.Url.Authority & HttpContext.Current.Request.ApplicationPath & "/Styles/editableGrid1.css")
            pageStyle.Attributes.Add("href", "http://" & HttpContext.Current.Request.Url.Authority & HttpContext.Current.Request.ApplicationPath & "/Styles/page.css")

            SortParameters.Clear()
            RegisterEvent(TableId, UserName)

            Filters.Clear()

            If Not IsNothing(Request.QueryString("f")) Then
                Try
                    queryFilters = Newtonsoft.Json.JsonConvert.DeserializeObject(Of Hashtable)(Request.QueryString("f"))
                Catch ex As Exception
                End Try
            End If
            Try
                ddlView.DataSource = sqlf.GetData("select 'Default' as view_name, -1 as view_id UNION ALL select distinct view_name, view_id from UserModules.dbo.views where table_id=" & TableId)
                ddlView.DataTextField = "view_name"
                ddlView.DataValueField = "view_id"
                ddlView.DataBind()
                If ddlView.Items.Count = 1 Then
                    ddlView.Visible = False
                End If
            Catch ex As Exception
                ddlView.Visible = False
            End Try
        Else
            queryFilters = Nothing
        End If

        If IsNothing(ColumnsConfiguration) Then
            BuildColumnsConfiguration()
        End If

        BuildAuxilaryConfig()

        '------------                -Experimental  ----------------------------------------------
        Dim _formulas As New Hashtable
        Try
            If Not IsNothing(config.Formulas) Then
                For Each r As DataRow In config.Formulas.Rows
                    Dim ff As String = "$(this).text(("
                    Dim f() As String = r("Formula").Split(" ")
                    For Each p As String In f
                        If "()+-*/".Contains(p) Or IsNumeric(p) Then
                            ff &= p
                        Else
                            ff &= "parseFloat(getCellValue(""" & p & """, RowIndex))"
                        End If
                    Next
                    ff &= ").toFixed(2))"
                    _formulas.Add(r("name"), ff)
                Next
                Dim serializer As New JavaScriptSerializer()
                formulas.Value = serializer.Serialize(_formulas)
                formulas.ID = "formulas_" & TableId
                formulas.ClientIDMode = UI.ClientIDMode.Static
            Else
                _formulas = Nothing
            End If
        Catch ex As Exception
            _formulas = Nothing
        End Try

        GridView1.Attributes.Add("tid", TableId)
        '---------------------------------------------------------------------------------------------------

        selectedfile.ID = "selectedfile_" & TableId
        choosefile.ID = "choosefile_" & TableId
        confirmdeletefile.ID = "confirmdeletefile_" & TableId
        confirmdeleterow.ID = "confirmdeleterow_" & TableId
        'GridView1.ClientIDMode = UI.ClientIDMode.Static

        deleterowbutton.ID = "deleterowbutton_" & TableId
        canceldeleterow.ID = "canceldeleterow_" & TableId
        '-----------------------------

        RefreshFilters()

        If Not IsPostBack Then
            Try
                GridView1.CssClass &= " gridview_" & TableId
                'Dim defaultSorting() As DataRow = ColumnsConfiguration.Select("default_sort<>''")
                'If Not IsNothing(defaultSorting) Then
                If Not IsNothing(ColumnsConfiguration) Then
                    For Each dr As DataRow In ColumnsConfiguration.Rows
                        If dr("default_sort").ToString.Trim <> "" Then
                            UpdateSorting("[" & dr("DbFieldName") & "]", dr("DEFAULT_SORT"), True)
                        End If
                    Next
                End If
                'End If
            Catch ex As Exception

            End Try
            RefreshData(True)
        Else
            RefreshData()
        End If

        If lblGridStatus.Value = "ADD" Then
            AddMode()
        ElseIf lblGridStatus.Value = "ADD_FORM" Then
            AddFormMode()
        End If

        If IsPostBack Then
            noticeBaloon.Visible = False
        End If

        HidePopup()
    End Sub

    Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
        If IsReadOnly Then
            AddNewItem.Visible = False
            DataUpload.Visible = False
            ibDeleteData.Visible = False
            pnlDataUpload.Visible = False
        Else
            If accessType.Contains("I") Or accessType.Contains("F") Then
                AddNewItem.Visible = True
                pnlDataUpload.Visible = True
            Else
                DataUpload.Visible = False
                AddNewItem.Visible = False
            End If
        End If

        'Decorate Pager
        UpdateColumnsHeader()
        'Правильный фильтр----------------------------------------------------------------
        'Закомментировать-----------------------------------------------------------------
        'Начало---------------------------------------------------------------------------
        RefreshFilters()
        'Конец----------------------------------------------------------------------------

        'Правильный фильтр----------------------------------------------------------------
        'Разкомментировать----------------------------------------------------------------
        'Начало---------------------------------------------------------------------------
        '        RefreshFilters(condition)
        'Конец----------------------------------------------------------------------------
        DecorateFilters()

        If Not IsNothing(GridView1.BottomPagerRow) Then
            Dim bpr As GridViewRow = GridView1.BottomPagerRow
            bpr.Visible = True
            Dim pt As Table = DirectCast(DirectCast(bpr.Controls.Item(0), System.Web.UI.WebControls.TableCell).Controls(0), Table)

            Dim tr As TableRow = pt.Rows(0)

            tr.Cells.Clear()

            'Message = ClientID.Replace(ClientIDSeparator, "$")

            Dim toFirst As New TableCell
            toFirst.Controls.Add(New Literal With {.Text = "<a href=""javascript:__doPostBack('" & ClientID.Replace(ClientIDSeparator, "$") & "$GridView1','Page$First')"" style=""color:White;"">&lt;&lt;</a>"})
            tr.Cells.Add(toFirst)

            For i As Integer = currentPageIndex - 3 To currentPageIndex + 5
                If i > 0 Then
                    If i > totalPages Then
                        Exit For
                    End If

                    Dim nc As New TableCell
                    Dim a As New Literal With {.Text = "<a href=""javascript:__doPostBack('" & ClientID.Replace(ClientIDSeparator, "$") & "$GridView1','Page$" & i & "')"" style=""color:White;"">" & i & "</a>"}
                    nc.Controls.Add(a)
                    pt.Rows(0).Cells.Add(nc)
                    If i = currentPageIndex + 1 Then
                        nc.BackColor = Drawing.Color.LightSteelBlue
                    End If
                End If
            Next
            Dim toLast As New TableCell
            toLast.Controls.Add(New Literal With {.Text = "<a href=""javascript:__doPostBack('" & ClientID.Replace(ClientIDSeparator, "$") & "$GridView1','Page$Last')"" style=""color:White;"">&gt;&gt;</a>"})
            tr.Cells.Add(toLast)
        End If

        'If copyFlag Then
        '    AddFormMode(copyId)
        'End If

    End Sub

#End Region

#Region "GridData"
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        Dim bCanDeleteThisRow As Boolean = True
        Dim _bReadOnly As Boolean = bReadOnly
        RaiseEvent RowDataBinding(e.Row, bReadOnly)
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim sTableName As String = FullTableName
            Dim rowView As DataRowView = CType(e.Row.DataItem, DataRowView)
            Dim gridColCount As Integer = 0
            Dim colCount As Integer = 0
            Try
                e.Row.Attributes.Add("rowid", rowView("id"))
                e.Row.Attributes.Add("tid", TableId)
                e.Row.Attributes.Add("onmouseover", "mover(" & rowView("id") & ");")
                e.Row.Attributes.Add("onmouseout", "mout(" & rowView("id") & ");")
                e.Row.CssClass = "gridrow"

            Catch ex As Exception
                e.Row.Attributes.Add("rowid", "-1")
            End Try

            For Each c As TableCell In e.Row.Cells
                If Not commandColumns.Contains(gridColCount) Then
                    If Not skipColumns.Contains(colCount) Then
                        Try
                            Dim dr As DataRow = ColumnsConfiguration.Select("name='" & rowView.DataView.Table.Columns(colCount).ColumnName & "'")(0)

                            Try
                                c.Attributes.Add("rownum", e.Row.RowIndex)
                                c.Attributes.Add("tid", _tableId)
                                c.Attributes.Add("field", config.GetParam(colCount, "NAME"))
                                c.Attributes.Add("type", config.GetParam(colCount, "Fieldtype"))
                                c.Attributes.Add("rowid", rowView("id"))
                                c.ToolTip = config.GetParam(colCount, "FIELDDESCRIPTION")
                            Catch ex As Exception

                            End Try

                            If Not emptyFlag Then 'And Not bReadOnly And dr("enabled") = "Y" Then
                                If Not bReadOnly And dr("enabled") = "Y" Then
                                End If
                                Dim cntrl As Control = Nothing
                                Dim controlClass As String = " " & rblOperationMOde.SelectedValue ' " ajaxedit_click"
                                Try
                                    If dr("user_dependent") = "Y" And rowView("created_by").ToString <> UserName.ToString Then
                                        controlClass = "ajaxedit_1"
                                    End If
                                Catch ex As Exception
                                End Try
                                Try
                                    Select Case dr("FieldType")
                                        Case "LITERAL"
                                            Dim l As New LiteralControl
                                            l.Text = rowView.Item(colCount)
                                            c.Controls.Add(l)
                                            cntrl = Nothing
                                        Case "TEXT", "DATE", "TIME", "TEXTBOX", "CONDENSED TEXT", "ENCODED TEXT"
                                            cntrl = Nothing
                                            If Not bReadOnly And dr("enabled") = "Y" Then
                                                c.CssClass &= controlClass
                                            End If
                                            If dr("FieldType") = "ENCODED TEXT" Then
                                                c.CssClass &= " encoded"
                                                c.ToolTip = "Personal data - encoded"
                                            End If
                                            If (dr("FieldType") = "TEXTBOX" Or dr("FieldType") = "CONDENSED TEXT") And Not ExcelDownloadNow Then
                                                Dim literalText As String = ""
                                                For Each stringChar As Char In c.Text
                                                    If stringChar = Chr(10) Then
                                                        literalText &= "<br>"
                                                    Else
                                                        literalText &= stringChar
                                                    End If
                                                Next
                                                c.Text = literalText
                                            End If
                                        Case "NUMBER"
                                            cntrl = Nothing
                                            If Not bReadOnly And dr("enabled") = "Y" Then
                                                c.CssClass &= controlClass
                                            End If
                                            c.Attributes.Add("total", "Y")
                                            c.HorizontalAlign = HorizontalAlign.Right
                                        Case "CHECKBOX", "MARK"
                                            If Not ExcelDownloadNow Then
                                                Dim chk As New CheckBox With {.ID = "mark_" & dr("id") & "_" & dr("name")}
                                                If dr("FieldType") = "MARK" Then
                                                    Select Case c.Text
                                                        Case "R"
                                                            e.Row.BackColor = Drawing.Color.Coral
                                                        Case "Y"
                                                            e.Row.BackColor = Drawing.Color.Yellow
                                                        Case "G"
                                                            e.Row.BackColor = Drawing.Color.LightGreen
                                                    End Select
                                                    chk.Checked = (c.Text.ToUpper = "R" Or c.Text.ToUpper = "Y" Or c.Text.ToUpper = "G")
                                                    chk.Attributes.Add("val", c.Text.Replace("&nbsp;", ""))
                                                Else
                                                    chk.Checked = (c.Text.ToUpper = "Y" Or c.Text.ToUpper = "True")
                                                End If
                                                chk.Attributes.Add("onclick", "Return False")
                                                cntrl = Nothing
                                                If Not bReadOnly And dr("enabled") = "Y" Then
                                                    c.CssClass &= controlClass
                                                End If
                                                c.HorizontalAlign = HorizontalAlign.Center
                                                c.Controls.Add(chk)
                                            Else
                                                If dr("FieldType") = "MARK" Then
                                                    Select Case c.Text
                                                        Case "R"
                                                            e.Row.BackColor = Drawing.Color.Coral
                                                        Case "Y"
                                                            e.Row.BackColor = Drawing.Color.Yellow
                                                        Case "G"
                                                            e.Row.BackColor = Drawing.Color.LightGreen
                                                    End Select
                                                End If
                                            End If
                                        Case "DROPDOWNLIST"
                                            If Not bReadOnly And dr("enabled") = "Y" Then
                                                c.CssClass &= controlClass
                                            End If
                                            cntrl = Nothing
                                        Case "URL"
                                            Dim l As New LiteralControl
                                            If config.GetParam(colCount, "Formula") <> "" Then
                                                l.Text = config.GetParam(colCount, "Formula")
                                                Try
                                                    For i As Integer = 0 To rowView.DataView.Table.Columns.Count - 1
                                                        l.Text = l.Text.Replace("@" & rowView.DataView.Table.Columns(i).ColumnName, rowView.Item(i).ToString)
                                                    Next
                                                Catch ex As Exception

                                                End Try
                                            Else
                                                l.Text = "<a target='blank' href='" & rowView.Item(colCount) & "'>" & rowView.Item(colCount) & "</a>"
                                            End If
                                            c.Controls.Add(l)
                                            If Not bReadOnly And dr("enabled") = "Y" Then
                                                c.CssClass &= controlClass
                                            End If
                                            cntrl = Nothing
                                        Case "FORMULA", "DATEFORMULA", "TIMEFORMULA"
                                            'uc.Type = "FORMULA"
                                            'uc.IsReadOnly = True
                                            cntrl = Nothing
                                            c.HorizontalAlign = HorizontalAlign.Right

                                            ' --------------  Experimental ------------------------
                                            If config.GetParam(colCount, "Formula") <> "" Then
                                                'c.CssClass &= "formula"
                                                c.ToolTip = config.GetParam(colCount, "Formula")
                                            End If
                                            If dr("FieldType") = "FORMULA" Then
                                                c.Attributes.Add("total", "Y")
                                                c.HorizontalAlign = HorizontalAlign.Right
                                            End If
                                            '-------------------------------------------------------------
                                        Case "FILE", "PHOTO"
                                            Dim p As New Panel
                                            BuildList(p,
                                                      rowView("ID"),
                                                      config.GetParam(colCount, "NAME"),
                                                      dr("FieldType"),
                                                      bReadOnly Or (dr("enabled") = "N"),
                                                      FilesAsLinks,
                                                       dr("STYLE")
                                                      )
                                            cntrl = p
                                        Case "ACTION"
                                            If Not ExcelDownloadNow Then
                                                Dim ac As ASP.usercontrols_gridactioncontrol_ascx = LoadControl("~/UserControls/gridActionControl.ascx")
                                                Try
                                                    Dim actionParams() As String = dr("Formula").ToString.Split("|")
                                                    ac.actionType = actionParams(0)
                                                    ac.Text = actionParams(1)
                                                    ac.ImageUrl = actionParams(1)
                                                    ac.CommandName = actionParams(2)
                                                    For Each p As String In actionParams(3).Split(":")
                                                        If ac.CommandArgument <> "" Then ac.CommandArgument &= ":"
                                                        ac.CommandArgument &= rowView(p)
                                                    Next
                                                    ac.Enabled = Not (bReadOnly) And (dr("enabled") = "Y")
                                                    ac.Description = dr("FieldDescription")
                                                    c.HorizontalAlign = HorizontalAlign.Center
                                                Catch ex As Exception

                                                End Try
                                                cntrl = ac
                                            Else
                                                cntrl = New LiteralControl With {.Text = "-"}
                                            End If
                                        Case "STATUS"
                                            Dim st As ASP.usercontrols_gridstatuscontrol_ascx = LoadControl("~/UserControls/gridStatusControl.ascx")
                                            Try
                                                Dim formulaSplit() As String = dr("Formula").ToString.Split("|")
                                                If formulaSplit.Count = 3 Then
                                                    Dim params() As String = formulaSplit(0).Split(" ")
                                                    If params.Count = 3 Then
                                                        st.ControlExpression = CheckParam(params(0), rowView) & " " & params(1) & " " & CheckParam(params(2), rowView) & "|" & formulaSplit(1) & "|" & formulaSplit(2)
                                                    Else
                                                        st.ControlExpression = ""
                                                    End If
                                                ElseIf formulaSplit.Count = 4 Then
                                                    Dim extParams() As String = formulaSplit(0).Split(";")
                                                    If extParams.Count = 2 Then
                                                        Dim params1() As String = extParams(0).Split(" ")
                                                        Dim params2() As String = extParams(1).Split(" ")
                                                        If params1.Count = 3 And params2.Count = 3 Then
                                                            st.ControlExpression = CheckParam(params1(0), rowView) & " " & params1(1) & " " & CheckParam(params1(2), rowView) & ";" &
                                                                                    CheckParam(params2(0), rowView) & " " & params2(1) & " " & CheckParam(params2(2), rowView) &
                                                                                    "|" & formulaSplit(1) & "|" & formulaSplit(2) & "|" & formulaSplit(3)
                                                        Else
                                                            st.ControlExpression = ""
                                                        End If
                                                    Else
                                                        Dim params() As String = formulaSplit(0).Split(" ")
                                                        If params.Count = 3 Then
                                                            st.ControlExpression = CheckParam(params(0), rowView) & " " & params(1) & " " & CheckParam(params(2), rowView) & "|" & formulaSplit(1) & "|" & formulaSplit(2)
                                                        Else
                                                            st.ControlExpression = ""
                                                        End If
                                                    End If
                                                Else
                                                    st.ControlExpression = ""
                                                End If
                                                st.DrawStatus()
                                            Catch ex As Exception

                                            End Try
                                            cntrl = st
                                        Case "ROWNUMBER"
                                            c.Text = (e.Row.RowIndex + 1) + currentPageIndex * GridView1.PageSize
                                            'uc.Text = e.Row.RowIndex
                                            'uc.Type = "TEXT"
                                        Case Else
                                            'uc.Type = "TEXT"
                                    End Select
                                Catch ex As Exception
                                    'uc.Type = "TEXT"
                                    'cntrl = uc
                                End Try

                                If dr("STYLE") <> "" Then
                                    Dim style() As String = dr("STYLE").split(";")
                                    If style.Count > 0 Then
                                        For Each st In style
                                            Dim stParams() As String = st.Split(":")
                                            If stParams.Count = 2 Then
                                                c.Style.Add(stParams(0), stParams(1))
                                            End If
                                        Next
                                    End If
                                End If
                                'End If

                                'Checking for user dependent filed
                                Dim __bReadOnly As Boolean = bReadOnly
                                If Not IsNothing(cntrl) Then
                                    If Not bReadOnly Then
                                        If dr("user_dependent") = "Y" And Not (accessType.Contains("F")) Then
                                            Try
                                                If rowView("created_by").ToString.ToUpper <> UserName.ToUpper Then
                                                    c.ToolTip = "Created by " & rowView("created_by") & " Only " & rowView("created_by") & " can edit."
                                                    bReadOnly = True
                                                    bCanDeleteThisRow = False
                                                    If dr("FieldType") = "FILE" Or
                                                    dr("FieldType") = "PHOTO" Then
                                                        CType(cntrl, ASP.usercontrols_advancedfileslist_ascx).ShowAsList = bReadOnly
                                                        CType(cntrl, ASP.usercontrols_advancedfileslist_ascx).BuildFileList(bReadOnly)
                                                    ElseIf dr("FieldType") = "ACTION" Then
                                                        CType(cntrl, ASP.usercontrols_gridactioncontrol_ascx).Enabled = False
                                                    End If
                                                Else
                                                    c.ToolTip = dr("FieldDescription")
                                                End If
                                            Catch ex As Exception
                                            End Try
                                        Else
                                            c.ToolTip = dr("FieldDescription")
                                        End If
                                    End If
                                    '----------------------------------------------

                                    If (Not bReadOnly And dr("enabled") = "Y") Or
                                        dr("FieldType") = "FILE" Or
                                        dr("FieldType") = "PHOTO" _
                                        Or dr("fieldType") = "CHECKBOX" _
                                        Or dr("fieldType") = "MARK" _
                                        Or dr("fieldType") = "ACTION" _
                                        Or dr("fieldType") = "URL" _
                                        Or dr("fieldType") = "STATUS" Then
                                        If dr("fieldType") = "CHECKBOX" Or dr("fieldType") = "MARK" Then
                                            CType(cntrl, UserControls_gridEditControl).IsReadOnly = (bReadOnly Or dr("enabled") = "N")
                                            If Not ShowCheckBoxAsText Then
                                                c.Controls.Add(cntrl)
                                            End If
                                        Else
                                            c.Controls.Add(cntrl)
                                        End If

                                        c.Attributes.Add("type", dr("fieldtype"))
                                        c.Attributes.Add("field", dr("dbfieldname"))

                                    ElseIf dr("fieldType") = "CONDENSED TEXT" Then
                                        If Not ExcelDownloadNow Then
                                            c.Controls.Add(cntrl)
                                        End If
                                    ElseIf dr("fieldType") = "NUMBER" Then
                                        'c.Text = CType(cntrl, UserControls_gridEditControl).Text ' "=" & dr("formula") 
                                        If c.Text.Contains(",") Or c.Text.Contains(".") Then
                                            c.Text = c.Text.Replace(",", ".").TrimEnd("0").TrimEnd(".")
                                        End If
                                    ElseIf dr("fieldType") = "FORMULA" Then
                                        c.ToolTip = c.Text
                                        c.Text = Math.Round(Convert.ToDecimal(c.Text), 4).ToString.Replace(",", ".").TrimEnd("0").TrimEnd(".")
                                    End If
                                End If
                                bReadOnly = __bReadOnly
                            End If
                        Catch ex As Exception
                        End Try
                    End If
                    colCount += 1
                End If
                gridColCount += 1
            Next
        ElseIf e.Row.RowType = DataControlRowType.Footer Then
            Dim cellcount As Integer = 0
            For Each c In e.Row.Cells
                c.Attributes.Add("field", TableId & "|Total|" & config.GetParam(cellcount, "NAME"))
                cellcount += 1

                c.HorizontalAlign = HorizontalAlign.Right
            Next

        End If

        'Processing configuration data
        If Not IsNothing(ColumnsConfiguration) Then
            If e.Row.RowType <> DataControlRowType.Pager Then
                Dim gridColCount As Integer = 0
                Dim colCount As Integer = 0
                For Each c As TableCell In e.Row.Cells
                    If Not commandColumns.Contains(gridColCount) Then
                        Try
                            Dim dr As DataRow = ColumnsConfiguration.Select("position='" & colCount & "'")(0)
                            If dr("visible") = "N" And Not WithInvisibleFields Then
                                c.Visible = False
                            Else
                                c.Visible = True
                            End If
                        Catch ex As Exception
                        End Try
                        colCount += 1
                    End If
                    gridColCount += 1
                Next
            End If
        End If

        'Add Select/Delete Command Column
        If Not ExcelDownloadNow Then
            If e.Row.RowType = DataControlRowType.DataRow Then
                Dim commandCell As New TableCell With {.Text = "", .CssClass = "command"}
                Dim rowView As DataRowView = CType(e.Row.DataItem, DataRowView)
                If _tableParams("SHOW_SEL_ROW_BUTTON") = "Y" Then
                    Dim ib2 As New ImageButton With {.CommandName = "SEL",
                        .CommandArgument = e.Row.RowIndex,
                        .ToolTip = "Select this row",
                        .CssClass = "rowselect",
                        .ImageUrl = "~/Images/Buttons/set2/yes.png"}
                    ib2.Attributes.Add("rownum", e.Row.Cells(0).Text)
                    ib2.ID = ID & "_SelectButton"
                    ib2.Style.Add("visibility", "hidden")

                    AddHandler ib2.Click, AddressOf SelectRow
                    commandCell.Controls.Add(ib2)
                End If
                If Not bReadOnly And (accessType.Contains("D") Or accessType.Contains("F")) Then
                    If bCanDeleteThisRow Then
                        Dim ib As New Image With {.ToolTip = "Delete this row",
                            .ImageUrl = "~/Images/Buttons/set2/delete.png",
                            .CssClass = "click rowdelete"}
                        ib.Attributes.Add("rownum", e.Row.Cells(0).Text)
                        ib.Attributes.Add("tid", TableId)
                        ib.ID = ID & "_deleterow_" & TableId & "_" & e.Row.Cells(0).Text
                        ib.Style.Add("visibility", "hidden")

                        commandCell.Controls.Add(New LiteralControl With {.Text = "&nbsp;"})
                        commandCell.Controls.Add(ib)


                    End If
                End If
                e.Row.Cells.Add(commandCell)
                '' Add copy row button
                'commandCell.Controls.Add(New LiteralControl With {.Text = "&nbsp;"})
                'Dim copyButton As New ImageButton With {.CommandName = "COPY_ROW", _
                '                                        .CommandArgument = e.Row.Cells(0).Text, _
                '                                        .ToolTip = "copy this row", _
                '                                        .ImageUrl = "~/Images/Buttons/item_copy.png", _
                '                                        .CssClass = "click rowcopy"}
                'commandCell.Controls.Add(copyButton)
            Else
                'If bReadOnly Then
                Dim commandCell As New TableCell With {.Text = ""}
                e.Row.Cells.Add(commandCell)
                'End If
            End If
        End If
        bReadOnly = _bReadOnly
        RaiseEvent RowDataBoundComplete(e.Row)
    End Sub
    Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.DataBound
        Try
            If Not IsReadOnly And Not ExcelDownloadNow Then
                commandColumns.Add(GridView1.HeaderRow.Cells.Count - 1, GridView1.HeaderRow.Cells.Count - 1)
            End If
        Catch ex As Exception

        End Try
        'GridView1.HeaderRow.TableSection = TableRowSection.TableHeader
    End Sub
    Private Sub UpdateColumnsHeader()
        Dim colCount As Integer = 0
        Dim visibleColCount As Integer = 0
        Dim gridcolcount As Integer = 0
        If Not IsNothing(GridView1.HeaderRow) Then
            Try
                For Each c As TableCell In GridView1.HeaderRow.Cells
                    If Not commandColumns.Contains(gridcolcount) Then
                        If Not skipColumns.Contains(colCount) Then
                            Try
                                If c.Visible Then
                                    visibleColCount += 1
                                End If

                                If FieldList.Count >= colCount Then
                                    Dim dr As DataRow = ColumnsConfiguration.Select("name='" & FieldList(colCount) & "'")(0)
                                    If bReadOnly Then
                                        If GridView1.AllowSorting Then
                                            If SortParameters.ContainsKey("[" & CType(c.Controls(0), LinkButton).Text & "]") Then
                                                Dim arr As New Image
                                                Select Case SortParameters("[" & CType(c.Controls(0), LinkButton).Text & "]")
                                                    Case "ASC"
                                                        arr.ImageUrl = "~/Images/Arrows/arrow_glossy_down_red_12.png"
                                                        arr.AlternateText = "ASC"
                                                    Case "DESC"
                                                        arr.ImageUrl = "~/Images/Arrows/arrow_glossy_up_red_12.png"
                                                        arr.AlternateText = "DESC"
                                                End Select
                                                c.Controls.Add(arr)
                                            End If
                                            CType(c.Controls(0), LinkButton).Text = dr("fieldname")
                                        Else
                                            c.Text = dr("fieldname")
                                        End If
                                    Else
                                        If SortParameters.ContainsKey("[" & CType(c.Controls(0), LinkButton).Text & "]") Then
                                            Dim arr As New Image
                                            Select Case SortParameters("[" & CType(c.Controls(0), LinkButton).Text & "]")
                                                Case "ASC"
                                                    arr.ImageUrl = "~/Images/Arrows/arrow_glossy_down_red_12.png"
                                                    arr.AlternateText = "ASC"
                                                Case "DESC"
                                                    arr.ImageUrl = "~/Images/Arrows/arrow_glossy_up_red_12.png"
                                                    arr.AlternateText = "DESC"
                                            End Select
                                            c.Controls.Add(arr)
                                        End If
                                        CType(c.Controls(0), LinkButton).Text = dr("fieldname")
                                    End If
                                End If
                            Catch ex As Exception

                            End Try

                        End If
                        colCount += 1
                    End If
                    gridcolcount += 1
                Next

                GridView1.BottomPagerRow.Cells(0).ColumnSpan = visibleColCount
                If GridView1.BottomPagerRow.Cells.Count > 1 Then
                    GridView1.BottomPagerRow.Cells(1).Visible = False
                End If
            Catch ex As Exception

            End Try
        End If
    End Sub

    Private Sub GridDataBind(ByRef grid As GridView, ByVal data As Object)
        If DataBindingMode = "datatable" Then
            grid.DataSource = CType(data, DataTable)
        ElseIf DataBindingMode = "datareader" Then
            'grid.AllowPaging = False
            grid.DataSource = CType(data, SqlDataReader)
        End If
        grid.DataBind()
        Try
            If DataBindingMode = "datatable" Then
                'lblStatus.Text = data.Rows.Count & "/" & GridView1.PageCount & "pages " 
                lblStatus.Text = currentDataRecords & " of " & totalDataRecords & "/" & totalPages & " pages"
            ElseIf DataBindingMode = "datareader" Then
                lblStatus.Text = ".../" & GridView1.PageCount & "pages "
            End If

            If Not IsPostBack Then
                txtPagerSIze.Text = PageSize
            End If
        Catch ex As Exception

        End Try
    End Sub

    Public Sub RefreshData(Optional ByVal fullUpdate As Boolean = False)
        If Not IsNothing(ColumnsConfiguration) Then
            If ColumnsConfiguration.Rows.Count > 0 Then
                If fullUpdate Then
                    _tableId = ColumnsConfiguration.Rows(0)("tableId")
                    FieldList.Clear()
                    FieldTypes.Clear()
                    FieldFormulas.Clear()
                    FieldVisibility.Clear()
                    FieldVisibilityByName.Clear()
                    FieldEnabled.Clear()

                    BuildAuxilaryConfig()
                End If

                sqlQuery = PrepareSQL(ColumnsConfiguration, FullTableName)
                GridDataBind(GridView1, GetData(sqlQuery, Filters, SortParameters, currentPageIndex + 1))
                Try
                    CreateActionsButtons(ColumnsConfiguration.Rows(0)("tableid"), bReadOnly)
                Catch ex As Exception

                End Try
                If fullUpdate Then
                    'upMain.Update()
                End If
            End If
        End If
    End Sub


    Public Sub RefreshFilters()
        If Not IsNothing(ColumnsConfiguration) Then
            CreateFilters(ColumnsConfiguration, FullTableName)
        End If
    End Sub

    Private Function PrepareSQL(ByVal colConfig As DataTable, ByVal tablename As String) As String
        iterationCount = 0
        Dim sql As New StringBuilder
        If Not IsNothing(colConfig) Then
            sql.Append("SELECT ")
            Dim first As Boolean = False
            For Each dr As DataRow In colConfig.Rows
                If first Then
                    sql.Append(", ")
                End If
                If dr("fieldtype").ToString = "FORMULA" Then
                    sql.Append(PrepareFormula(dr("formula")) & " as " & dr("name"))
                ElseIf dr("fieldtype").ToString = "DATEFORMULA" Then
                    sql.Append(PrepareDateFormula(dr("formula")) & " as " & dr("name"))
                ElseIf dr("fieldtype").ToString = "TIMEFORMULA" Then
                    sql.Append(PrepareTimeFormula(dr("formula")) & " as " & dr("name"))
                ElseIf dr("fieldtype").ToString = "ENCODED TEXT" Then
                    sql.Append("hr.dbo.fn_decodeString([" & dr("name") & "]) AS [" & dr("name") & "]")
                Else
                    sql.Append("[" & dr("name") & "]")
                End If

                first = True
            Next
        Else
            sql.Append("SELECT * ")
        End If
        sql.Append(" FROM ")
        sql.Append(tablename)
        Return sql.ToString

    End Function
    Private Function GetFieldType(fieldName As String) As String
        Dim rows() As DataRow = ColumnsConfiguration.Select("dbfieldname='" & fieldName & "'")
        If rows.Count > 0 Then
            Dim dr As DataRow = rows(0)
            Return dr("fieldtype")
        Else
            Return ""
        End If
    End Function
    Private Function BuildSelectConditionByFilters() As String
        Dim sql As String = ""
        FiltersQueryPrefix = ""
        If Not IsNothing(Filters) Then
            If Filters.Count <> 0 Then
                For Each item As DictionaryEntry In Filters
                    sql &= " AND "
                    If item.Value.GetType.Name = "String" Then
                        If item.Value = "=N''" Then
                            sql &= "(" & item.Key & item.Value & " or " & item.Key & " is null)"
                        Else
                            sql &= item.Key & item.Value
                        End If
                    ElseIf item.Value.GetType.Name = "filter" Then 'ENCODED STRINGS CHECK - TEMP 
                        If CType(item.Value, filter).fieldType = "ENCODED TEXT" Then
                            sql &= CType(item.Value, filter).fieldName & CType(item.Value, filter).sqlCondition
                            FiltersQueryPrefix &= vbNewLine & CType(item.Value, filter).sqlPrefix
                        End If
                    End If
                Next
            End If
        End If
        '----- Processing multiselect filters -------
        Try
            Dim tmpsql As String = ""
            For Each t As Control In phFilters.Controls
                If t.GetType.ToString = "System.Web.UI.HtmlControls.HtmlTable" Then
                    For Each r As HtmlTableRow In CType(t, HtmlTable).Rows
                        For Each c As HtmlTableCell In r.Cells
                            For Each cc As Control In c.Controls
                                If cc.GetType.ToString = "ASP.usercontrols_multiselect_ascx" Then
                                    Dim ms As ASP.usercontrols_multiselect_ascx = CType(cc, ASP.usercontrols_multiselect_ascx)
                                    If Not ms.allChecked Then
                                        Dim tmpsql1 As String = ""
                                        For Each element In ms.GetElements
                                            If element.value Then
                                                If tmpsql1 <> "" Then
                                                    tmpsql1 &= " OR "
                                                Else
                                                    tmpsql1 = "("
                                                End If
                                                tmpsql1 &= ms.FieldName & "='" & element.key & "'"
                                            End If
                                        Next
                                        If tmpsql1 <> "" Then tmpsql1 &= ")"

                                        If tmpsql <> "" Then tmpsql &= " AND "

                                        tmpsql &= tmpsql1
                                    End If
                                End If
                            Next
                        Next
                    Next
                End If
            Next
            If tmpsql <> "" Then
                sql &= " AND " & tmpsql
            End If
        Catch ex As Exception

        End Try
        Return sql
    End Function

    Dim _sql As String = ""

    Public Property sqlQueryFinal As String
        Set(value As String)
            _sql = value
        End Set
        Get
            Return _sql
        End Get
    End Property

    Private Function GetData(ByVal sql As String, ByVal filters As Hashtable, ByVal sorting As Dictionary(Of String, String), Optional pageIndex As Integer = 1) As DataTable
        Try
            Dim condition As String = BuildSelectConditionByFilters()
            Dim order As String = ""
            If Not IsNothing(sorting) Then
                If sorting.Count <> 0 Then
                    For Each item As KeyValuePair(Of String, String) In sorting
                        If order <> "" Then
                            order &= ", "
                        Else
                            order &= " ORDER BY "
                        End If
                        order &= item.Key & " " & item.Value
                    Next
                End If
            End If
            order = order.ToUpper
            If order <> "" Then
                If Not order.Contains(" [ID] ") Then
                    order &= ", [ID] ASC "
                End If
            Else
                order = "ORDER BY [ID] ASC"
            End If

            '---------------------- Making paging-------------------------------------------------
            If GridView1.AllowPaging Then
                sql = sql.Replace("SELECT", "SELECT TOP (" & PageSize & ") ")
                sql &= " WHERE 1=1 " & condition

                sql &= " AND ID NOT IN (SELECT TOP (" & (pageIndex - 1) * PageSize & ") ID FROM " & FullTableName & " WHERE 1=1 " & condition & " " & order & ")"
                sql &= " " & order
            Else
                sql &= " WHERE 1=1 " & condition
                sql &= " " & order
            End If
            '-------------------------------------------------
            ' Надо проверить есть ли префикс для закодированных данных
            If FiltersQueryPrefix <> "" Then
                sqlQueryFinal = FiltersQueryPrefix & vbNewLine & sql
            Else
                sqlQueryFinal = sql
            End If

            If debugMode Then
                Message = sqlQueryFinal
            End If

            RaiseEvent DataSelecting(sqlQueryFinal)
            If DataBindingMode = "datatable" Then
                Dim dt As DataTable = sqlf.GetData(sqlQueryFinal)
                If Not IsNothing(dt) Then
                    If dt.Rows.Count = 0 Then
                        dt.Rows.Add(dt.NewRow)
                        emptyFlag = True
                    Else
                        emptyFlag = False
                    End If
                End If
                RaiseEvent DataSelected(dt)

                totalDataRecords = sqlf.GetScalar("SELECT COUNT(*) FROM " & FullTableName)
                'If FiltersQueryPrefix <> "" Then
                '    currentDataRecords = -1 'Когда используем закодированные поля - количество записей считать не будем, чтобы не нагружать сервер 
                '    currentDataRecords = sqlf.GetScalar(FiltersQueryPrefix & vbNewLine & "SELECT COUNT(*) FROM " & FullTableName & " WHERE 1=1 " & condition)
                'Else
                '    currentDataRecords = sqlf.GetScalar("SELECT COUNT(*) FROM " & FullTableName & " WHERE 1=1 " & condition)
                'End If

                currentDataRecords = sqlf.GetScalar(FiltersQueryPrefix & vbNewLine & "SELECT COUNT(*) FROM " & FullTableName & " WHERE 1=1 " & condition)

                totalPages = Math.Ceiling(currentDataRecords / PageSize)
                '---------------------------------------
                Try
                    If _tableParams("PLAY_SOUND_ON_CHANGE") = "Y" Then
                        dvPlaySound.InnerHtml = ""
                        Dim rowCnt As Integer = dt.Rows.Count
                        If Not IsNothing(Session("rowsCnt_" & TableId)) Then
                            If rowCnt <> Session("rowsCnt_" & TableId) Then
                                dvPlaySound.InnerHtml = "<embed src=""Sound/3.wav""></embed>"
                            End If
                        End If
                        Session("rowsCnt_" & TableId) = rowCnt
                    End If
                Catch ex As Exception
                End Try
                '---------------------------------------
                Return dt
            End If
        Catch ex As Exception

        End Try

        Return Nothing
    End Function

#End Region

#Region "Formula"
    Dim iterationCount As Integer = 0

    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 = ""
        Dim prevOperation = ""
        For Each p As String In params
            p = p.Trim
            If {"*", "/", "+", "-", "(", ")"}.Contains(p) Then
                preparedFormula &= p
                prevOperation = p
            Else
                'TO-DO  -   сделать нормальный обработчик для сложного делителя, т.е. вида a/(b+c*d...)
                If prevOperation = "/" Then
                    preparedFormula &= " NULLIF(cast(replace(" & CheckForFormula(p) & ",',','.') as decimal(18,3)),0) "
                Else
                    preparedFormula &= " cast(replace(" & CheckForFormula(p) & ",',','.') as decimal(18,3)) "
                End If
            End If
            cnt += 1
        Next
        Return "ROUND(" & preparedFormula & ",2)"
    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 DataRow = ColumnsConfiguration.Select("dbfieldname='" & param & "'")
            If rows.Count > 0 Then
                Dim dr As DataRow = rows(0)
                If dr("fieldtype") = "FORMULA" Then
                    Return "(" & PrepareFormula(dr("formula")) & ")"
                Else
                    Return " nullif(rtrim([" & param & "]),'') "
                End If
            Else
                Return "'" & param & "'"
            End If
        Catch ex As Exception
            Return "'" & param & "'"
        End Try
    End Function

    Private Function PrepareDateFormula(formula As String) As String
        'Message &= "  " & formula
        Dim params() As String = formula.Trim.Split(" ")
        If params.Count <> 3 Then
            Return ""
        End If
        If IsNumeric(params(0)) And IsNumeric(params(2)) Then
            Return ""
        End If
        If Not IsNumeric(params(0)) And Not IsNumeric(params(2)) And params(1) = "+" Then
            Return ""
        End If
        If params(1) <> "+" And params(1) <> "-" Then
            Return ""
        End If
        Dim r As New StringBuilder
        If Not IsNumeric(params(0)) And Not IsNumeric(params(2)) Then
            If params(0) = "TODAY" Then
                r.AppendLine(" CASE ISDATE (REPLACE(LTRIM(RTRIM(" & params(2) & " )),' ','')) ")
                r.AppendLine(" WHEN 0 THEN NULL ELSE ")
                r.AppendLine(" DATEDIFF(day, convert (datetime,  REPLACE(LTRIM(RTRIM(" & params(2) & " )),' ','')  ,20), getdate())")
                r.AppendLine(" END ")
                Return r.ToString
            ElseIf params(2) = "TODAY" Then
                r.AppendLine(" CASE ISDATE (REPLACE(LTRIM(RTRIM(" & params(0) & " )),' ','')) ")
                r.AppendLine(" WHEN 0 THEN NULL ELSE ")
                r.AppendLine(" DATEDIFF(day, getdate(), Convert(DateTime, Replace(LTrim(RTrim(" & params(0) & ")),' ',''),20))")
                r.AppendLine(" END ")
                Return r.ToString
            Else
                r.AppendLine(" CASE isdate(REPLACE(LTRIM(RTRIM(" & params(2) & " )),' ','')) * isdate(REPLACE(LTRIM(RTRIM(" & params(0) & " )),' ','')) ")
                r.AppendLine(" WHEN 0 THEN NULL ELSE ")
                r.AppendLine(" DATEDIFF(day, convert (datetime, REPLACE(LTRIM(RTRIM(" & params(2) & " )),' ',''),20), convert (datetime, REPLACE(LTRIM(RTRIM(" & params(0) & " )),' ',''),20))")
                r.AppendLine(" END ")
                Return r.ToString
            End If
        Else
            If IsNumeric(params(0)) Then
                If params(1) = "TODAY" Then
                    Return " SUBSTRING(CONVERT(varchar, DATEADD(day," & params(1) & params(0) & ", getdate()), 120),1,10)"
                Else
                    Return " SUBSTRING(CONVERT(varchar, DATEADD(day," & params(1) & params(0) & ", convert (datetime, " & params(1) & ",20)), 120),1,10)"
                End If
            Else
                If params(0) = "TODAY" Then
                    Return " SUBSTRING(CONVERT(varchar, DATEADD(day," & params(1) & params(2) & ", getdate()), 120),1,10)"
                Else
                    Return "SUBSTRING(CONVERT(varchar,  DATEADD(day," & params(1) & params(2) & ", convert (datetime, " & params(0) & ",20)), 120),1,10)"
                End If
            End If
        End If
    End Function

    Private Function PrepareTimeFormula(formula As String) As String
        'Message &= "  " & formula
        Dim params() As String = formula.Split(" ")
        If params.Count <> 3 Then
            Return ""
        End If
        If IsNumeric(params(0)) And IsNumeric(params(2)) Then
            Return ""
        End If
        If Not IsNumeric(params(0)) And Not IsNumeric(params(2)) And params(1) = "+" Then
            Return ""
        End If
        If params(1) <> "+" And params(1) <> "-" Then
            Return ""
        End If
        If Not IsNumeric(params(0)) And Not IsNumeric(params(2)) Then
            If params(0) = "NOW" Then
                Return " DATEDIFF(minute, convert (time, " & params(2) & ",8), getdate())"
            ElseIf params(2) = "TODAY" Then
                Return " DATEDIFF(minute, getdate(), convert (time, " & params(0) & ",8))"
            Else
                Return " DATEDIFF(minute, convert (time, " & params(2) & ",8), convert (time, " & params(0) & ",8))"
            End If
        Else
            If IsNumeric(params(0)) Then
                If params(1) = "NOW" Then
                    Return " SUBSTRING(CONVERT(varchar, DATEADD(minute," & params(1) & params(0) & ", getdate()), 8),1,5)"
                Else
                    Return " SUBSTRING(CONVERT(varchar, DATEADD(minute," & params(1) & params(0) & ", convert (time, " & params(1) & ",8)), 8),1,5)"
                End If
            Else
                If params(0) = "NOW" Then
                    Return " SUBSTRING(CONVERT(varchar, DATEADD(minute," & params(1) & params(2) & ", getdate()), 8),1,5)"
                Else
                    Return "SUBSTRING(CONVERT(varchar,  DATEADD(minute," & params(1) & params(2) & ", convert (time, " & params(0) & ",8)), 8),1,5)"
                End If
            End If
        End If
    End Function

#End Region

#Region "Grid Actions Handlers"
    Protected Sub SavingHandler(tableName As String, fieldName As String, rowId As String, value As String, fieldType As String, byButton As Boolean, text As String, ByRef cancelSave As Boolean)
        RaiseEvent SavingElement(tableName, fieldName, rowId, value, fieldType, byButton, text, cancelSave)
    End Sub

    Protected Sub SavedHandler(tableName As String _
            , fieldName As String _
            , rowId As String _
            , value As String _
            , fieldType As String _
            , byButton As Boolean _
            , text As String)
        'lblMessage.Value = "Saved!!! " & rowId & " : " & fieldName
        'lblEditingObject.Value = ""
        Dim bRefreshed As Boolean = False

        If byButton Then
            If Filters.Contains("[" & fieldName & "]") Then
                RefreshData(True)
            ElseIf SortParameters.ContainsKey("[" & fieldName & "]") And Not bRefreshed Then
                RefreshData(True)
            ElseIf fieldType = "MARK" Then
                RefreshData(True)
            End If
        End If

        RaiseEvent SavedElement(tableName, fieldName, rowId, value, fieldType, byButton, text)
    End Sub

    'Protected Sub CancelledHandler(ByVal rowId As Integer, ByVal fieldName As String)
    '    'lblMessage.Value = "Cancelled! " & rowId & " : " & fieldName
    '    lblEditingObject.Value = ""
    'End Sub

    'Protected Sub EditingHandler(ByVal rowId As Integer, ByVal fieldName As String)
    '    If lblEditingObject.Value <> "" Then
    '        For Each r As GridViewRow In GridView1.Rows
    '            If r.Cells(idColumn).Text = lblEditingObject.Value.Split(Separator)(0) Then
    '                Dim colCount As Integer = 0
    '                Dim gridColCount As Integer = 0
    '                For Each c As TableCell In r.Cells
    '                    If Not commandColumns.Contains(gridColCount) Then
    '                        If Not skipColumns.Contains(colCount) Then
    '                            Try
    '                                If c.Controls.Count > 0 Then
    '                                    If CType(c.Controls(0), UserControls_gridEditControl).Field = lblEditingObject.Value.Split(Separator)(1) Then
    '                                        CType(c.Controls(0), UserControls_gridEditControl).Save()
    '                                        CType(c.Controls(0), UserControls_gridEditControl).Show(True)
    '                                    End If
    '                                End If
    '                            Catch ex As Exception

    '                            End Try
    '                        End If
    '                        colCount += 1
    '                    End If
    '                    gridColCount += 1
    '                Next
    '            End If
    '        Next
    '    End If
    '    lblEditingObject.Value = rowId & Separator & fieldName
    'End Sub

    Protected Sub CancelNewRecordHandler()
        'GridDataBind(GridView1, GetData(sqlQuery), Filters)
        RefreshData()
        lblGridStatus.Value = "VIEW"
    End Sub

    Protected Sub AddMode()
        Dim gridColCount As Integer = 0
        Dim colCount As Integer = 0
        For Each c As TableCell In GridView1.FooterRow.Cells
            If Not commandColumns.Contains(gridColCount) Then
                If Not skipColumns.Contains(colCount) Then
                    Dim cntrl As Control
                    Try
                        Dim dr As DataRow = ColumnsConfiguration.Select("position='" & colCount & "'")(0)
                        cntrl = GetAppropriateControl(dr, colCount)
                        GridView1.FooterRow.Cells(gridColCount).Controls.Add(cntrl)
                    Catch ex As Exception

                    End Try
                End If
                colCount += 1
            End If
            gridColCount += 1
        Next

        GridView1.FooterRow.Cells(GridView1.FooterRow.Cells.Count - 1).Controls.Clear()

        Dim ib1 As New ImageButton With {.ImageUrl = "~/Images/Buttons/save.png"}
        ib1.ID = "save_new_record"
        'AddHandler ib1.Click, AddressOf SaveNewRecord
        GridView1.FooterRow.Cells(GridView1.FooterRow.Cells.Count - 1).Controls.Add(ib1)

        Dim ib2 As New ImageButton With {.ImageUrl = "~/Images/Buttons/cancel.png"}
        ib2.ID = "cancel_new_record"
        AddHandler ib2.Click, AddressOf CancelNewRecordHandler
        GridView1.FooterRow.Cells(GridView1.FooterRow.Cells.Count - 1).Controls.Add(ib2)

        lblGridStatus.Value = "ADD"
    End Sub

    Protected Sub DeleteRowRequest(ByVal sender As Object, ByVal e As EventArgs)
        'Call (New SqlFunctions).NonQuery("DELETE FROM " & tableName & " WHERE ID=" & sender.CommandArgument)
        confirmationPopup1.ActionText = " delete "
        confirmationPopup1.ItemId = sender.commandargument
        confirmationPopup1.ShowPopup()
        'dfhfrdgtjhrtyfjty
    End Sub

    Protected Sub DeleteRow(ByVal itemid As Integer) Handles confirmationPopup1.Yes
        Dim cancelDelete As Boolean = False
        Select Case confirmationPopup1.ActionText
            Case " delete "
                RaiseEvent RecordDeleting(itemid, cancelDelete)
                If Not cancelDelete Then
                    sqlf.NonQuery("DELETE FROM " & FullTableName & " WHERE ID=" & itemid)
                    RaiseEvent RecordDeleted(itemid)
                    sqlf.NonQuery("DELETE FROM " & FullTableName.Replace("UserModules", "UserModulesAttachments") & " WHERE parentID=" & itemid)
                End If
            Case " delete all "

        End Select
        RefreshData()
    End Sub

    Protected Sub btnYes_Click(sender As Object, e As ImageClickEventArgs) Handles btnYes.Click
        Dim cancelDelete As Boolean = False
        Dim sql As String = "DELETE FROM " & FullTableName & " "
        Dim condition As String = ""
        If Not IsNothing(Filters) Then
            If Filters.Count <> 0 Then
                sql &= " WHERE "
                For Each item As DictionaryEntry In Filters
                    If condition.Length > 0 Then
                        condition &= " AND "
                    End If
                    condition &= item.Key & item.Value
                Next
            End If
        End If
        sql &= condition
        RaiseEvent MassDeleting(condition, cancelDelete)
        If Not cancelDelete Then
            sqlf.NonQuery(sql)
        End If
        RefreshData()
    End Sub

    Private Sub SelectRow(ByVal sender As Object, ByVal e As ImageClickEventArgs)
        GridView1.SelectedIndex = sender.commandargument
        GridView1.Attributes.Add("selectedindex", sender.commandargument)
        RaiseEvent RecordSelected(GridView1.SelectedIndex)
    End Sub

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
        'Message = e.NewPageIndex

        If Not pageIndexSet Then currentPageIndex = e.NewPageIndex
        GridView1.SelectedIndex = -1
        RefreshData()
        'lblEditingObject.Value = ""
    End Sub

    Private pageIndexSet As Boolean = False

    Public copyFlag As Boolean = False
    Public copyId As Integer = -1

    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        If e.CommandName = "Page" Then
            If e.CommandArgument = "First" Then
                currentPageIndex = 0
                pageIndexSet = True
            ElseIf e.CommandArgument = "Last" Then
                currentPageIndex = totalPages - 1
                pageIndexSet = True
            End If
        End If
        ' Handle ROW Copy request
        If e.CommandName = "COPY_ROW" Then
            copyFlag = True
            copyId = Convert.ToInt16(e.CommandArgument)
        End If
        '--------------------------------------
        RaiseEvent RowCommand(sender, e)
    End Sub

#End Region

#Region "Sorting"
    Public Property SortParameters As Dictionary(Of String, String)
        Set(value As Dictionary(Of String, String))
            SaveSortingValues(value)
        End Set

        Get
            Dim ht As Dictionary(Of String, String) = ReadSortingValues()
            If ht Is Nothing Then
                ht = New Dictionary(Of String, String)
                SaveSortingValues(ht)
            End If
            Return ReadSortingValues()
        End Get
    End Property

    Private Sub SaveSortingValues(ht As Dictionary(Of String, String))
        ViewState.Add("srt_" & UniqueID, ht)
    End Sub

    Private Function ReadSortingValues() As Dictionary(Of String, String)
        Return (TryCast(ViewState("srt_" & UniqueID), Dictionary(Of String, String)))
    End Function

    Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting
        Dim fieldType As String = ""
        Dim drr As DataRow() = ColumnsConfiguration.Select("DBFIELDNAME = '" & e.SortExpression & "'")
        If drr.Length > 0 Then
            fieldType = drr(0).Item("FIELDTYPE")
        End If

        If fieldType = "NUMBER" Then
            e.SortExpression = "UserModules.dbo.f_pad_left([" & e.SortExpression & "],10,'0')"
        Else
            e.SortExpression = "[" & e.SortExpression & "]"
        End If


        GridView1.SelectedIndex = -1
        If IsNothing(SortParameters) Then
            SortParameters = New Dictionary(Of String, String)
        End If
        Dim _sortDirection As String = ""
        If SortParameters.ContainsKey(e.SortExpression) Then
            Select Case SortParameters(e.SortExpression)
                Case "ASC" : _sortDirection = "DESC"
                Case "DESC" : _sortDirection = "ASC"
            End Select
            'Sorting.Remove(e.SortExpression)
        Else
            _sortDirection = "ASC"
        End If
        'Sorting.Clear()
        'Sorting.Add(e.SortExpression, _sortDirection)

        UpdateSorting(e.SortExpression, _sortDirection)
        RefreshData()

    End Sub

    Public Sub UpdateSorting(ByVal sortExpression As String, ByVal direction As String, Optional initialSet As Boolean = False)
        If IsNothing(SortParameters) Then
            SortParameters = New Dictionary(Of String, String)
        End If
        If Not initialSet Then
            SortParameters.Clear()
        End If
        SortParameters.Add(sortExpression, direction)
    End Sub

    Public Sub ClearSorting()
        SortParameters.Clear()
    End Sub

#End Region

#Region "Filters"
    Private queryFilters As Hashtable
    Private FiltersQueryPrefix As String = ""
    <Serializable()> Private Structure filter
        Public fieldName As String
        Public fieldType As String
        Public fieldValue As String
        Public sqlCondition As String
        Public sqlPrefix As String ' Для использования в фильтрах ENCODED TEXT 
    End Structure

    Private Sub SaveFilterValues(ht As Hashtable)
        ViewState.Add("filters_" & UniqueID, ht)
    End Sub

    Private Sub SaveRawFilterValues(ht As Hashtable)
        ViewState.Add("rawfilters_" & UniqueID, ht)
    End Sub

    Private Function ReadFilterValues() As Hashtable
        Return (TryCast(ViewState("filters_" & UniqueID), Hashtable))
    End Function

    Private Function ReadRawFilterValues() As Hashtable
        Return (TryCast(ViewState("rawfilters_" & UniqueID), Hashtable))
    End Function
    Private Sub CreateFilters(ByVal dt As DataTable, ByVal tableName As String)
        If Not IsNothing(queryFilters) Then
            For Each f As DictionaryEntry In queryFilters
                Dim r() As DataRow = dt.Select("dbfieldname='" & f.Key & "'")
                If r.Count > 0 Then
                    If r(0)("filter") = "Y" Or r(0)("filter_type") <> "" Then
                        If r(0)("fieldtype") = "DATE" Or r(0)("filter_type") = "DATE" Then
                            If f.Value.ToString.Contains("|") Then
                                UpdateFilters(f.Key, " between ", "'" & f.Value.ToString.Split("|")(0) & "' AND '" & f.Value.ToString.Split("|")(1) & "'")
                            Else
                                UpdateFilters(f.Key, " >= ", "'" & f.Value.ToString & "'")
                            End If
                        ElseIf r(0)("search") = "Y" Or r(0)("filter_type") = "SEARCH" Then
                            UpdateFilters(f.Key.ToString.Replace("--", ""), " like ", "'%" & f.Value.ToString.Replace("--", "") & "%'")
                        Else
                            UpdateFilters(f.Key, "=", f.Value)
                        End If
                    End If
                End If
            Next
        End If

        Dim multiSelectSavedState As New Hashtable
        Dim msState As Newtonsoft.Json.Linq.JObject = Newtonsoft.Json.JsonConvert.DeserializeObject(controlState.Value)

        For Each token As Newtonsoft.Json.Linq.JProperty In msState.Children
            multiSelectSavedState.Add(token.Name, token.Value)
        Next

        phFilters.Controls.Clear()

        Dim tblF As HtmlTable

        Dim drCaption As HtmlTableRow
        Dim drFilter As HtmlTableRow

        Dim counter As Integer = 0
        For Each dr As DataRow In dt.Rows
            If dr("filter") = "Y" Or dr("filter_type") <> " " Then

                Dim dc1 As New HtmlTableCell
                Dim dc2 As New HtmlTableCell

                dc1.Attributes.Add("class", "center")
                dc2.Attributes.Add("class", "center")

                dc1.InnerText = dr("FieldName")

                Dim currentFilterValue As String = ""

                If Filters.Contains("[" & dr("dbfieldname") & "]") Then
                    Dim f = Filters("[" & dr("dbfieldname") & "]")
                    If f.GetType.Name = "filter" Then
                        currentFilterValue = CType(f, filter).fieldValue
                    ElseIf f.GetType.Name = "String" Then
                        currentFilterValue = Filters("[" & dr("dbfieldname") & "]")
                    End If

                End If

                'для типа поля ENCODED TEXT фильтр типа SELECT использовать нельзя ! только поиск лайком
                If dr("FIELDTYPE") = "ENCODED TEXT" Then
                    '------------------------------------------------------------------------
                    Dim txt As New TextBox
                    txt.TextMode = TextBoxMode.Search
                    Dim fieldName As String = dr("name").ToString.ToUpper

                    txt.ID = ID & "_FILTER_" & fieldName.Replace("_", UserSeparator)
                    Dim value As String = Request.Form(txt.ID)
                    txt.AutoPostBack = True
                    AddHandler txt.TextChanged, AddressOf SearchFilterChanged
                    Try
                        If Not IsNothing(Filters("[" & fieldName & "]")) Then
                            Dim f As filter = Filters("[" & fieldName & "]")
                            'txt.Text = Filters("[" & fieldName & "]").ToString.Replace(" like N'%", "").Replace("%'", "").Trim("=").Trim("N").Trim("'")
                            txt.Text = f.fieldValue
                        End If
                    Catch ex As Exception

                    End Try
                    dc2.Controls.Add(txt)
                    '------------------------------------------------------------------------
                Else
                    Select Case dr("filter_type")
                        Case "DATE"
                            Dim txtFrom As New TextBox With {.Width = Unit.Pixel(70), .AutoPostBack = True}
                            txtFrom.ID = ID & "_FILTER_" & dr("name").ToString.Replace("_", UserSeparator) & "_from"

                            Dim ceFrom As New CalendarExtender
                            ceFrom.TargetControlID = txtFrom.ID
                            ceFrom.Format = dateFormat
                            ceFrom.FirstDayOfWeek = WebControls.FirstDayOfWeek.Monday

                            Dim txtTo As New TextBox With {.Width = Unit.Pixel(70), .AutoPostBack = True}
                            txtTo.ID = ID & "_FILTER_" & dr("name").ToString.Replace("_", UserSeparator) & "_to"

                            Dim ceTo As New CalendarExtender
                            ceTo.TargetControlID = txtTo.ID
                            ceTo.Format = dateFormat
                            ceTo.FirstDayOfWeek = WebControls.FirstDayOfWeek.Monday

                            AddHandler txtFrom.TextChanged, AddressOf DateFilterChanged
                            AddHandler txtTo.TextChanged, AddressOf DateFilterChanged

                            If currentFilterValue <> "" Then
                                If currentFilterValue.Contains("between") Then
                                    currentFilterValue = currentFilterValue.Trim.Replace("between ", "").Trim("N").Replace("'", "")
                                    Dim dates() As String = currentFilterValue.Split("N")
                                    dates(0) = dates(0).Trim("A").Trim
                                    dates(1) = dates(1).Trim("D").Trim

                                    txtFrom.Text = dates(0)
                                    txtTo.Text = dates(1)

                                ElseIf currentFilterValue.Contains("<=") Then
                                    currentFilterValue = currentFilterValue.Replace("<=", "").Trim.Trim("N").Trim("'")
                                    txtTo.Text = currentFilterValue
                                ElseIf currentFilterValue.Contains(">=") Then
                                    currentFilterValue = currentFilterValue.Replace(">=", "").Trim.Trim("N").Trim("'")
                                    txtFrom.Text = currentFilterValue
                                End If
                            End If

                            dc2.Controls.Add(txtFrom)
                            dc2.Controls.Add(ceFrom)

                            dc2.Controls.Add(txtTo)
                            dc2.Controls.Add(ceTo)
                        Case "SELECT"   'для типа поля ENCODED TEXT фильтр типа SELECT использовать нельзя !
                            Dim ddl As New DropDownList
                            ddl.ID = ID & "_FILTER_" & dr("name").ToString.Replace("_", UserSeparator)
                            ddl.CssClass = "UnicodeFont"
                            'ddl.Width = Unit.Pixel(130)
                            ddl.Style.Add("max-width", "130px")
                            Dim value As String = Request.Form(ddl.ID)
                            Dim sql As New StringBuilder
                            sql.Append("select 0 as rank, 'ALL' as text, '%' as value 				")
                            sql.Append("union all                                                   ")
                            sql.Append("select rank() over (order by t.text) , t.text, t.val from ( ")
                            sql.Append("select                                                      ")
                            sql.Append("	distinct cast(isnull([" & dr("name") & "],'') as nvarchar(500)) as text,               ")
                            sql.Append("	cast(isnull([" & dr("name") & "],'') as nvarchar(500)) as val                          ")
                            sql.Append("	from " & tableName)
                            sql.Append(" WITH(NOLOCK))t")

                            ddl.DataSource = sqlf.GetData(sql.ToString) '("Select 'ALL' as text, '%' as value union all select distinct cast([" & dr("name") & "] as nvarchar), cast([" & dr("name") & "] as nvarchar) from " & tableName)
                            ddl.DataTextField = "text"
                            ddl.DataValueField = "value"
                            Try
                                ddl.DataBind()
                            Catch ex As Exception
                                ddl.DataSource = Nothing
                            End Try

                            ddl.AutoPostBack = True
                            AddHandler ddl.SelectedIndexChanged, AddressOf FilterChanged

                            If Filters.Contains("[" & dr("name").ToString.ToUpper & "]") Then
                                Dim val As String = Filters("[" & dr("name").ToString.ToUpper & "]").ToString.Trim("=").Trim("N").Trim("'")
                                Try
                                    If IsNothing(ddl.Items.FindByValue(val)) Then
                                        Dim li As New ListItem
                                        li.Text = val
                                        li.Value = val
                                        ddl.Items.Add(li)
                                    End If
                                    ddl.Items.FindByValue(val).Selected = True
                                Catch ex As Exception

                                End Try
                            End If
                            dc2.Controls.Add(ddl)
                        Case "MULTISELECT"
                            Dim sql As New StringBuilder
                            sql.Append("select rank() over (order by t.text) , t.text, t.val from ( ")
                            sql.Append("select                                                      ")
                            sql.Append(" distinct cast(isnull([" & dr("name") & "],'') as nvarchar(500)) as text, ")
                            sql.Append(" cast(isnull([" & dr("name") & "],'') as nvarchar(500)) as val ")
                            sql.Append(" from " & tableName)
                            sql.Append(" WITH(NOLOCK))t                                                          ")

                            Dim ms As UserControls_multiselect = LoadControl("~/UserControls/multiselect.ascx")
                            ms.DataSource = sqlf.GetData(sql.ToString)
                            ms.TextField = "text"
                            ms.ValueField = "val"
                            ms.Caption = "..."
                            ms.FieldName = dr("name")

                            If Not IsNothing(multiSelectSavedState) Then
                                Try
                                    ms.Values = Newtonsoft.Json.JsonConvert.DeserializeObject(Of Hashtable)(multiSelectSavedState("'" & ms.FieldName & "'"))
                                Catch ex As Exception

                                End Try

                            End If

                            ms.DataBind()
                            ms.SetElements()
                            dc2.Controls.Add(ms)
                        Case Else
                            Dim txt As New TextBox
                            txt.TextMode = TextBoxMode.Search
                            txt.ID = ID & "_FILTER_" & dr("name").ToString.Replace("_", UserSeparator)
                            Dim value As String = Request.Form(txt.ID)
                            txt.AutoPostBack = True
                            AddHandler txt.TextChanged, AddressOf SearchFilterChanged
                            Try
                                If Not IsNothing(Filters("[" & dr("name").ToString.ToUpper & "]")) Then
                                    txt.Text = Filters("[" & dr("name").ToString.ToUpper & "]").ToString.Replace(" like N'%", "").Replace("%'", "").Trim("=").Trim("N").Trim("'")
                                End If
                            Catch ex As Exception

                            End Try
                            dc2.Controls.Add(txt)
                    End Select
                End If

                If (counter Mod 10) = 0 Then
                    tblF = New HtmlTable
                    tblF.Border = 0
                    drCaption = New HtmlTableRow
                    drFilter = New HtmlTableRow

                    tblF.Rows.Add(drCaption)
                    tblF.Rows.Add(drFilter)

                    phFilters.Controls.Add(tblF)
                End If

                drCaption.Cells.Add(dc1)
                drFilter.Cells.Add(dc2)

                counter += 1
            End If
        Next
    End Sub
    Private Sub updateRawFilter(key As String, value As String)
        If rawFilters.Contains(key) Then
            rawFilters(key) = value
        Else
            rawFilters.Add(key, value)
        End If
    End Sub
    Public Sub UpdateFilters(ByVal filter As String, ByVal operation As String, ByVal value As String)
        filter = "[" & filter.ToUpper.Trim("[").Trim("]") & "]"
        If IsNothing(Filters) Then
            Filters = New Hashtable
        End If
        If Filters.Contains(filter) Then
            Filters.Remove(filter)
        End If
        If value <> "'%'" Then
            If value.Contains("'") Then
                Filters.Add(filter, operation & "N" & value)
            Else
                Filters.Add(filter, operation & value)
            End If
        End If
    End Sub
    Private Sub UpdateFilters(ByVal fieldName As String, ByVal f As filter)
        fieldName = "[" & fieldName.ToUpper.Trim("[").Trim("]") & "]"
        If IsNothing(Filters) Then
            Filters = New Hashtable
        End If
        If Filters.Contains(fieldName) Then
            Filters.Remove(fieldName)
        End If
        Filters.Add(fieldName, f)
    End Sub
    Protected Sub FilterChanged(ByVal sender As Object, ByVal e As EventArgs)
        GridView1.SelectedIndex = -1
        currentPageIndex = 0
        Dim ddl As DropDownList = DirectCast(sender, DropDownList)
        UpdateFilters(sender.id.split("_")(2).ToString.Replace(UserSeparator, "_"), "=", "'" & ddl.SelectedValue & "'")
        updateRawFilter(sender.id.split("_")(2).ToString.Replace(UserSeparator, "_"), ddl.SelectedValue)
        RaiseEvent GridFilterChanged()
        RefreshData()
        'lblEditingObject.Value = ""
        sender.focus()
    End Sub

    Protected Sub SearchFilterChanged(ByVal sender As Object, ByVal e As EventArgs)
        GridView1.SelectedIndex = -1
        currentPageIndex = 0
        Dim txt As TextBox = DirectCast(sender, TextBox)
        Dim fieldName As String = txt.ID.Split("_")(2).ToString.Replace(UserSeparator, "_")
        Dim fieldType As String = GetFieldType(fieldName)

        If txt.Text = "" Then
            ClearFilters(fieldName)
        Else
            If fieldType = "ENCODED TEXT" Then

                Dim f As filter = New filter
                f.fieldName = fieldName
                f.fieldType = fieldType
                f.fieldValue = txt.Text
                '  Тут надо сделать фильтр 
                ' NAME_ENG_ENC IN (
                ' Select Case VALUE FROM 
                ' OPENJSON(@STR) )
                Dim str_name As String = "@STR_" & fieldName.Replace("]", "").Replace("[", "")

                f.sqlCondition = " IN (Select VALUE FROM OPENJSON(@STR))".Replace("@STR", str_name)
                ' и сформировать префикс 
                ' Declare @STR NVARCHAR(MAX)
                ' Set @STR = 'СКА'
                ' EXEC HR.DBO.SP_GETENCODEDVALUES @STR OUTPUT
                f.sqlPrefix = ("Declare @STR NVARCHAR(MAX)" &
                 vbNewLine & " Set @STR = '" + txt.Text + "'" &
                 vbNewLine & " EXEC HR.DBO.SP_GETENCODEDVALUES @STR OUTPUT").Replace("@STR", str_name)
                UpdateFilters(fieldName, f)
            Else
                UpdateFilters(fieldName, " like ", "'%" & txt.Text & "%'")
            End If
            updateRawFilter(fieldName, txt.Text)
        End If

        RaiseEvent GridFilterChanged()
        RefreshData()
        sender.focus()
    End Sub

    Protected Sub DateFilterChanged(ByVal sender As Object, ByVal e As EventArgs)
        GridView1.SelectedIndex = -1
        currentPageIndex = 0

        Dim txtId As String = sender.id

        Dim txtFrom As TextBox = phFilters.FindControl(sender.id.ToString.Replace("_to", "_from"))
        Dim txtTo As TextBox = phFilters.FindControl(sender.id.ToString.Replace("_from", "_to"))

        Dim filterExpression As String = sender.id.split("_")(2).ToString.Replace(UserSeparator, "_")

        If txtFrom.Text <> "" And txtTo.Text <> "" Then
            UpdateFilters(filterExpression, " between ", "'" & txtFrom.Text & "' AND '" & txtTo.Text & "'")
        ElseIf txtFrom.Text <> "" And txtTo.Text = "" Then
            UpdateFilters(filterExpression, " >= ", "'" & txtFrom.Text & "'")
        ElseIf txtFrom.Text = "" And txtTo.Text <> "" Then
            UpdateFilters(filterExpression, " <= ", "'" & txtTo.Text & "'")
        ElseIf txtFrom.Text = "" And txtTo.Text = "" Then
            ClearFilters(filterExpression)
        End If

        updateRawFilter(filterExpression, txtFrom.Text & ":::" & txtTo.Text)

        RaiseEvent GridFilterChanged()

        RefreshData()
        'lblEditingObject.Value = ""
        sender.focus()
    End Sub

    Public Sub ClearFilters(Optional ByVal filterExpression As String = "")
        If filterExpression <> "" Then
            filterExpression = "[" & filterExpression.ToUpper & "]"
            Filters.Remove(filterExpression)
        Else
            Filters.Clear()
        End If
    End Sub
    Private Sub DecorateFilters()
        Try
            For Each tbl As HtmlTable In phFilters.Controls
                For Each r As HtmlTableRow In tbl.Rows
                    For Each cell As HtmlTableCell In r.Cells
                        For Each c As Control In cell.Controls
                            If TypeOf c Is DropDownList Then
                                If CType(c, DropDownList).SelectedValue <> "%" Then
                                    CType(c, DropDownList).BackColor = Drawing.Color.Coral
                                Else
                                    CType(c, DropDownList).BackColor = Drawing.Color.White
                                End If
                            ElseIf TypeOf c Is TextBox Then
                                If CType(c, TextBox).Text <> "" Then
                                    CType(c, TextBox).BackColor = Drawing.Color.Coral
                                Else
                                    CType(c, TextBox).BackColor = Drawing.Color.White
                                End If
                            End If
                        Next
                    Next
                Next
            Next

        Catch ex As Exception

        End Try
    End Sub
#End Region

#Region "Add item"
    Public Event PrepareAddForm(ByRef skip As Boolean)
    Public Event PreparedAddForm(ByRef pnlAdd As Panel)

    Protected Sub AddNewItem_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles AddNewItem.Click
        Dim skip As Boolean = False
        RaiseEvent PrepareAddForm(skip)
        If Not skip Then
            AddFormMode()
            RaiseEvent PreparedAddForm(pnlAddNewItem)
        End If
    End Sub

    Private Function GetAppropriateControl(ByVal dr As DataRow, ByVal colcount As Integer,
                                           Optional ByVal addHandlers As Boolean = False,
                                           Optional tempId As Integer = -1,
                                           Optional value As String = "") As Control
        Dim cntrl As Control 'System.Web.UI.WebControls.WebControl

        Dim defaultValue As String = IIf(value = "", dr("DEFAULT_VALUE").ToString.Trim, value)

        Select Case dr("fieldtype")
            Case "MARK"
                Dim ddl As New DropDownList
                ddl.Items.Add("")
                ddl.Items.Add("R")
                ddl.Items.Add("Y")
                ddl.Items.Add("G")

                ddl.CssClass = "addFormControl"
                ddl.Attributes.Add("fieldname", dr("DBFIELDNAME"))

                cntrl = ddl

            Case "CHECKBOX"
                Dim chk As New CheckBox
                If defaultValue.Trim = "Y" Then
                    chk.Checked = True
                End If

                chk.CssClass = "addFormControl"
                chk.Attributes.Add("fieldname", dr("DBFIELDNAME"))

                cntrl = chk
            Case "DATE"
                Dim txt As UserControls_txtDatePicker = LoadControl("~/UserControls/txtDatePicker.ascx")
                txt.Text = ""

                If defaultValue = "TODAY" Then
                    txt.Text = Date.Today.ToString("yyyy-MM-dd")
                ElseIf defaultValue <> "" Then
                    txt.Text = defaultValue
                End If

                txt.CssClass = "addFormControl"
                txt.Attributes.Add("fieldname", dr("DBFIELDNAME"))

                cntrl = txt
            Case "DROPDOWNLIST"
                Dim ddl As New DropDownList
                ddl.Style.Add("max-width", "130px")
                Dim params() As String = dr("Formula").ToString.Split("|")

                Dim sql As String = ""

                ddl.DataTextField = "FieldText"
                ddl.DataValueField = "FieldValue"

                'If IsNothing(phFilters.FindControl(ID & "_" & dr("name") & "_datasource")) Then
                If params.Count < 3 Then
                    sql = " select FieldText, FieldValue FROM [UserModules].[dbo].[FieldsValues] where enabled='Y' and TableId = " & dr("tableid") & " AND FieldId = " & dr("id") & " ORDER BY FieldText"
                Else
                    sql = " select " & params(0) & " as FieldText, " & params(1) & " as  FieldValue FROM "
                    If params(2).Contains(".") Then
                        sql &= params(2)
                    Else
                        sql &= "UserModules.[user]." & params(2)
                    End If
                    If params.Count > 3 Then
                        ' User filter values for params
                        For Each it In Filters
                            params(3) = params(3).Replace("@" & it.Key.ToString.Trim("[").Trim("]"), it.Value.ToString.Trim("=").Trim("N"))
                        Next

                        'Use field default values for params is no filter set
                        If params(3).Contains("@") Then
                            For Each ddr As DataRow In ColumnsConfiguration.Rows
                                params(3) = params(3).Replace("@" & ddr("DBFIELDNAME"), ddr("DEFAULT_VALUE"))
                            Next
                        End If

                        If Not params(3).Contains("@") Then
                            sql &= " WHERE " & params(3)
                        End If

                        'Dim whereParams() As String = params(3).Split("@")
                        'sql &= " WHERE " & whereParams(0).Trim("=")
                        'If whereParams.Count > 1 Then
                        '    sql &= Filters("[" & whereParams(1) & "]")
                        'End If
                    End If
                    If params.Count > 4 Then
                        sql &= " " & params(4)
                    End If
                End If

                'Dim ds As New SqlDataSource(ConfigurationManager.ConnectionStrings("SERKIntranetConnectionString").ConnectionString, sql)
                'ds.ID = ID & "_" & dr("name") & "_datasource"
                'phFilters.Controls.Add(ds)
                'End If

                ddl.DataSource = sqlf.GetData(sql) ' = ID & "_" & dr("name") & "_datasource"

                Try
                    ddl.DataBind()
                    ddl.Items.FindByValue(defaultValue).Selected = True
                Catch ex As Exception

                End Try

                'Try
                '    ddl.Attributes.Add("DEFAULT", defaultValue)
                'Catch ex As Exception
                'End Try

                ddl.CssClass = "addFormControl"
                ddl.Attributes.Add("fieldname", dr("DBFIELDNAME"))

                cntrl = ddl
            Case "FILE", "PHOTO"
                Dim p As New Panel
                BuildList(p, tempId, config.GetParam(colcount, "NAME"), dr("FieldType"))
                cntrl = p
            Case "NUMBER"
                Dim nv As UserControls_numberValidator = LoadControl("~/UserControls/numberValidator.ascx")
                If IsNumeric(defaultValue) Then '
                    nv.Text = defaultValue
                End If

                nv.CssClass = "addFormControl"
                nv.Attributes.Add("fieldname", dr("DBFIELDNAME"))

                cntrl = nv
            Case "TEXTBOX", "CONDENSED TEXT", "ENCODED TEXT"
                Dim txt As New TextBox
                txt.TextMode = TextBoxMode.MultiLine
                txt.Rows = 2
                txt.Width = Unit.Pixel(120)
                txt.ID = "new_" & FieldList(colcount)
                txt.Text = defaultValue

                txt.CssClass = "addFormControl"
                txt.Attributes.Add("fieldname", dr("DBFIELDNAME"))

                cntrl = txt
            Case "TIME"
                Dim txt As New TextBox
                txt.TextMode = TextBoxMode.Time
                txt.ID = "new_" & FieldList(colcount)
                txt.Text = defaultValue

                txt.CssClass = "addFormControl"
                txt.Attributes.Add("fieldname", dr("DBFIELDNAME"))

                cntrl = txt
            Case Else
                Dim txt As New TextBox
                txt.ID = "new_" & FieldList(colcount)
                txt.Text = defaultValue

                txt.CssClass = "addFormControl"
                txt.Attributes.Add("fieldname", dr("DBFIELDNAME"))

                cntrl = txt
        End Select
        Return cntrl
    End Function

    Public Sub AddFormMode(Optional rowId As Integer = -1)
        Dim gridColCount As Integer = 0
        Dim colCount As Integer = 0
        Dim activeColCount As Integer = 0

        Dim itemToCopy As DataRow = Nothing
        If rowId <> -1 Then
            itemToCopy = sqlf.GetRow("SELECT * FROM UserModules.[user]." & TableName & " WHERE id=" & rowId)
        End If

        Dim tempId As Integer
        If tempNewItemId.Value = 0 Then
            Randomize()
            tempId = (New Random).Next(-1000, 0)
            tempNewItemId.Value = tempId
        Else
            tempId = tempNewItemId.Value
        End If

        tblAddNewItem.Rows.Clear()

        For Each r As DataRow In ColumnsConfiguration.Rows
            If Not commandColumns.Contains(gridColCount) Then
                If Not skipColumns.Contains(colCount) Then
                    Try
                        If FieldVisibility(colCount) = "N" Or
                            (FieldEnabled(colCount) = "N" And
                                (r("INSERT_VISIBLE") = "N" Or r("INSERT_VISIBLE") = "")
                                ) Or
                            r("fieldType") = "FORMULA" Or
                            r("fieldType") = "DATEFORMULA" Or
                            r("fieldType") = "TIMEFORMULA" Or
                            r("fieldType") = "ACTION" Or
                            r("fieldType") = "STATUS" Or
                            r("fieldType") = "CONST" Or
                            r("insert_visible") = "N" Then

                        Else
                            Dim tr As HtmlTableRow
                            If activeColCount < 13 Then
                                tr = New HtmlTableRow
                            Else
                                tr = tblAddNewItem.Rows(activeColCount Mod 13)
                            End If

                            Dim td1 As New HtmlTableCell With {.InnerText = r("fieldname")}
                            td1.Style("font-weight") = "Bold"
                            Dim td2 As New HtmlTableCell
                            Dim hf As New HiddenField With {.Value = colCount}

                            Dim cntrl As Control
                            If Not IsNothing(itemToCopy) Then
                                cntrl = GetAppropriateControl(r, colCount, , tempId, IIf(IsDBNull(itemToCopy(r("DBFIELDNAME"))), "", itemToCopy(r("DBFIELDNAME"))))
                            Else
                                cntrl = GetAppropriateControl(r, colCount, , tempId)
                            End If

                            cntrl.ID = r("dbFieldName")
                            Try
                                Dim t As Type = cntrl.GetType
                                Select Case t.Name
                                    Case "usercontrols_txtdatepicker_ascx"
                                        CType(cntrl, UserControls_txtDatePicker).CIdMode = UI.ClientIDMode.Predictable
                                        CType(cntrl, UserControls_txtDatePicker).TabIndex = activeColCount + 1
                                    Case "usercontrols_numbervalidator_ascx"
                                        CType(cntrl, UserControls_numberValidator).TabIndex = activeColCount + 1
                                    Case "usercontrols_advancedfileslist_ascx"
                                        CType(cntrl, UserControls_advancedFilesList).TabIndex = activeColCount + 1
                                    Case "DropDownList"
                                        AddHandler CType(cntrl, DropDownList).DataBound, AddressOf ddlDataBound
                                        CType(cntrl, WebControl).TabIndex = activeColCount + 1
                                    Case Else
                                        CType(cntrl, WebControl).TabIndex = activeColCount + 1
                                End Select
                            Catch ex As Exception

                            End Try

                            td2.Controls.Add(cntrl)
                            td2.Controls.Add(hf)

                            tr.Cells.Add(td1)
                            tr.Cells.Add(td2)
                            If activeColCount < 13 Then
                                tblAddNewItem.Rows.Add(tr)
                            End If
                            activeColCount += 1
                        End If
                    Catch ex As Exception
                    End Try
                End If
                colCount += 1
            End If
            gridColCount += 1
        Next
        pnlAddNewItem.Style("max-width") = 250 * ((activeColCount \ 14) + 1) & "px"
        pnlAddNewItem.Visible = True

        lblGridStatus.Value = "ADD_FORM"
    End Sub

    Private Sub ddlDataBound(sender As Object, e As EventArgs)
        Dim ddl As DropDownList = CType(sender, DropDownList)
        Dim defaultValue As String = ddl.Attributes("DEFAULT")
        If defaultValue IsNot Nothing Then
            Try
                ddl.Items.FindByText(defaultValue).Selected = True
            Catch ex As Exception

            End Try
        End If
    End Sub


    Public Event Refreshing()
    Public Event Refreshed()
    Protected Sub btnRefresh_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnRefresh.Click
        RaiseEvent Refreshing()
        'lblEditingObject.Value = ""
        RefreshData()
        RaiseEvent Refreshed()
    End Sub

    Public Event AskForSaveNewItem(ByRef skip As Boolean)

    Protected Sub ibSaveNewItem_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ibSaveNewItem.Click
        Dim skip As Boolean = False
        RaiseEvent AskForSaveNewItem(skip)
        If skip Then
            pnlAddNewItem.Visible = False
            lblGridStatus.Value = "VIEW"
            Exit Sub
        End If

        Dim first As Boolean = False
        Dim sql As New StringBuilder

        Dim addedFields As New Collection

        sql.Append("INSERT INTO " & FullTableName & "(")
        For Each r As HtmlTableRow In tblAddNewItem.Rows
            For Each c As HtmlTableCell In r.Cells
                Try
                    Dim key As Integer = CType(c.Controls(1), HiddenField).Value
                    addedFields.Add(FieldList(key), FieldList(key))
                    Dim val As String = "[" & FieldList(key) & "]"
                    If first Then
                        sql.Append(",")
                    End If
                    sql.Append(val)
                    first = True
                Catch ex As Exception
                End Try
            Next
        Next

        For Each item In FieldList
            If (FieldVisibilityByName(item) = "Y") Or (item = "CREATED_BY") Then
                If Not addedFields.Contains(item) Then
                    If first Then
                        sql.Append(",")
                    End If
                    sql.Append("[" & item & "]")
                    first = True
                End If
            End If
        Next

        sql.Append(") VALUES (")

        first = False
        For Each r As HtmlTableRow In tblAddNewItem.Rows
            For Each c As HtmlTableCell In r.Cells
                Try
                    Dim key As Integer = CType(c.Controls(1), HiddenField).Value
                    Dim Val As String = "@" & FieldList(key).ToString.Replace("-", "_")
                    If first Then
                        sql.Append(",")
                    End If
                    If GetFieldType(Val.Replace("@", "")) = "ENCODED TEXT" Then
                        sql.Append("hr.dbo.fn_encodeString(" & Val & ",'name')") '<<<<<<    Добавить кодирование для ENCODED TEXT
                    Else
                        sql.Append(Val)
                    End If

                    first = True
                Catch ex As Exception
                End Try
            Next
        Next

        For Each item In FieldList
            If (FieldVisibilityByName(item) = "Y") Or (item = "CREATED_BY") Then
                If Not addedFields.Contains(item) Then
                    If first Then
                        sql.Append(",")
                    End If
                    sql.Append("@" & item)
                    first = True
                End If
            End If
        Next

        sql.Append(")")

        Dim com As New SqlCommand
        com.CommandText = sql.ToString

        If debugMode Then
            Message = sql.ToString
        End If

        With com.Parameters
            first = False
            For Each r As HtmlTableRow In tblAddNewItem.Rows
                Dim colcount As Integer
                For Each c As HtmlTableCell In r.Cells
                    Try
                        colcount = CType(c.Controls(1), HiddenField).Value
                        Select Case c.Controls(0).GetType.ToString
                            Case "System.Web.UI.WebControls.TextBox"
                                .AddWithValue("@" & FieldList(colcount).ToString.Replace("-", "_"), CType(c.Controls(0), TextBox).Text)
                            Case "ASP.usercontrols_txtdatepicker_ascx"
                                .AddWithValue("@" & FieldList(colcount).ToString.Replace("-", "_"), CType(c.Controls(0), ASP.usercontrols_txtdatepicker_ascx).Text)
                            Case "ASP.usercontrols_numbervalidator_ascx"
                                .AddWithValue("@" & FieldList(colcount).ToString.Replace("-", "_"), IIf(CType(c.Controls(0), ASP.usercontrols_numbervalidator_ascx).Text = "", DBNull.Value, CType(c.Controls(0), ASP.usercontrols_numbervalidator_ascx).Text))
                            Case "System.Web.UI.WebControls.DropDownList"
                                Try
                                    .AddWithValue("@" & FieldList(colcount).ToString.Replace("-", "_"), CType(c.Controls(0), DropDownList).SelectedItem.Text)
                                Catch ex As Exception
                                    .AddWithValue("@" & FieldList(colcount).ToString.Replace("-", "_"), "")
                                End Try
                            Case "System.Web.UI.WebControls.CheckBox"
                                If CType(c.Controls(0), CheckBox).Checked Then
                                    .AddWithValue("@" & FieldList(colcount).ToString.Replace("-", "_"), "Y")
                                Else
                                    .AddWithValue("@" & FieldList(colcount).ToString.Replace("-", "_"), "N")
                                End If
                            Case Else
                                .AddWithValue("@" & FieldList(colcount).ToString.Replace("-", "_"), "")
                        End Select
                    Catch ex As Exception
                    End Try
                Next
            Next

            For Each item In FieldList
                If Not addedFields.Contains(item) Then
                    .AddWithValue("@" & item, DBNull.Value)
                End If
            Next

            If com.Parameters.Contains("@CREATED_BY") Then
                com.Parameters("@CREATED_BY").Value = UserName
            End If

            If com.Parameters.Contains("@CREATE_DATE") Then
                com.Parameters("@CREATE_DATE").Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            End If
        End With

        RaiseEvent NewRecordSaving(com)

        If com.CommandText <> "" Then
            Try
                Dim newRecordId As Integer = sqlf.Insert(com)
                '------------------------------------------------------------------------------
                '----------    Saving history -------------------------------------------------
                If _tableParams("KEEP_HISTORY") = "Y" Then
                    config.SaveHistory(com.Parameters, newRecordId, UserName, "IP")
                End If
                '------------------------------------------------------------------------------
                'If Not IsNothing(Session(FullTableName & "_FilesAttached")) Then
                com.Parameters.Clear()
                'com.CommandText = "UPDATE " & FullTableName.Replace(UserDataStoragePrefix, AttachmentsStoragePrefix) & " SET parentid=" & newRecordId & " WHERE parentid=" & Session(FullTableName & "_FilesAttached")
                com.CommandText = "UPDATE " & FullTableName.Replace(UserDataStoragePrefix, AttachmentsStoragePrefix) & " SET parentid=" & newRecordId & " WHERE parentid=" & tempNewItemId.Value
                tempNewItemId.Value = 0
                sqlf.NonQuery(com)
                'End If

                RaiseEvent NewRecordSaved(newRecordId)
                '------------------------------------------------------------------------------
                '----------    call NEW RECORD external event handler
                '------------------------------------------------------------------------------
                Try
                    Dim dr As DataRow = _tableParams
                    If dr("NEW_ROW_ADDED") <> "" Then
                        Dim wb As Net.WebRequest

                        'Dim url As String = "http://" & HttpContext.Current.Request.Url.Authority.ToString & "/modulemaster/" & dr("NEW_ROW_ADDED")
                        Dim url As String = dr("NEW_ROW_ADDED")
                        'Dim url As String = "http://" & HttpContext.Current.Request.Url.Authority.ToString & "/" & dr("NEW_ROW_ADDED")

                        url &= "?tid=" & TableId & "&rowid=" & newRecordId
                        'Message = url
                        wb = Net.WebRequest.Create(url)
                        wb.GetResponse()
                        'Dim res As Net.WebResponse = wb.GetResponse
                        'Dim sr As New StreamReader(res.GetResponseStream)
                        'Dim p As New Panel
                        'Dim l As New Literal
                        'l.Text = sr.ReadToEnd()
                    End If
                Catch ex As Exception

                End Try
            Catch ex As Exception
                RaiseEvent NewRecordSaved(-1)
            End Try
        Else
            RaiseEvent NewRecordSaved(-1)
        End If
        pnlAddNewItem.Visible = False
        lblGridStatus.Value = "VIEW"
        'RefreshData(True)
        RefreshData()
    End Sub

    Protected Sub ibCancelNewItem_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ibCancelNewItem.Click
        pnlAddNewItem.Visible = False
        lblGridStatus.Value = "VIEW"
        RaiseEvent NewRecordCancel()
        sqlf.NonQuery("DELETE FROM  " & FullTableName.Replace(UserDataStoragePrefix, AttachmentsStoragePrefix) & " WHERE parentid=" & tempNewItemId.Value)
        tempNewItemId.Value = 0
    End Sub
#End Region

#Region "File attachments operations"
    Protected Sub AddAttachment(tableName As String, fieldName As String, rowid As String)
        RaiseEvent AddedAttachment(tableName, fieldName, rowid)
    End Sub

    Protected Sub DeleteAttachment(tableName As String, fieldName As String, rowid As String)
        RaiseEvent DeletedAttachment(tableName, fieldName, rowid)
    End Sub

#End Region

#Region "Form Actions"
    Sub ExcelDownloadSubFast(ByVal gv As GridView, ByVal filename As String)
        gv.AllowPaging = False
        Dim dt As DataTable = GetData(PrepareSQL(ColumnsConfiguration, FullTableName), Filters, SortParameters, currentPageIndex + 1)
        CreateExcelDocument(dt.DataSet, Server.MapPath("~") & "\UploadedFiles\" & filename & ".xlsx")
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xlsx")
        Response.ContentType = "application/vnd.xlsx"
        Response.WriteFile(Server.MapPath("~") & "\UploadedFiles\" & filename & ".xlsx", FileMode.Open)
        IO.File.Delete(Server.MapPath("~") & "\UploadedFiles\" & filename & ".xlsx")

        Response.End()
    End Sub
    Sub ExcelDownloadSub(ByVal gv As GridView, ByVal filename As String)
        Response.Clear()
        Response.Buffer = True

        Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
        Response.ContentType = "application/vnd.xls"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8")
        EnableViewState = False

        Response.Charset = "utf-8"

        Dim myCItrad As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US", True)

        gv.AllowSorting = False
        gv.AllowPaging = False

        bReadOnly = True
        ShowCheckBoxAsText = True
        ExcelDownloadNow = True
        FilesAsLinks = True

        RefreshData()

        UpdateColumnsHeader()

        CreateGroupingText()

        Dim sw As StringWriter = New StringWriter(myCItrad)
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

        gv.RenderControl(hw)
        Response.Write(sw.ToString)
        'Response.Write(sw.ToString)

        Response.End()
    End Sub
    Public Function GetHTMLGrid() As String
        Dim _AllowSorting As Boolean = GridView1.AllowSorting
        Dim _AllowPaging As Boolean = GridView1.AllowPaging
        Dim _bReadOnly As Boolean = bReadOnly
        Dim _ShowCheckBoxAsText As Boolean = ShowCheckBoxAsText
        Dim _ExcelDownloadNow As Boolean = ExcelDownloadNow
        Dim _FilesAsLinks As Boolean = FilesAsLinks

        GridView1.AllowSorting = False
        GridView1.AllowPaging = False

        bReadOnly = True
        ShowCheckBoxAsText = True
        ExcelDownloadNow = True
        FilesAsLinks = False

        'AuthorityCheck()

        RefreshData() 'True)
        UpdateColumnsHeader()

        Dim sw As StringWriter = New StringWriter
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
        GridView1.RenderControl(hw)

        GridView1.AllowSorting = _AllowSorting
        GridView1.AllowPaging = _AllowPaging
        bReadOnly = _bReadOnly
        ShowCheckBoxAsText = _ShowCheckBoxAsText
        ExcelDownloadNow = _ExcelDownloadNow
        FilesAsLinks = _FilesAsLinks

        RefreshData(True)
        UpdateColumnsHeader()

        Return sw.ToString
    End Function
    Protected Sub ibExcelDownloadWithOoptions_Click(sender As Object, e As ImageClickEventArgs) Handles ibExcelDownloadWithOoptions.Click
        WithInvisibleFields = chkWithInvisibleFields.Checked
        ExcelDownloadSub(GridView1, FullTableName.Replace(UserDataStoragePrefix, "") & "_" & DateTime.Now.ToString("yyyyMMddHHmmss"))
    End Sub
    Protected Sub ibExcelDownloadFust_Click(sender As Object, e As ImageClickEventArgs) Handles ibExcelDownloadFust.Click
        ExcelDownloadSubFast(GridView1, FullTableName.Replace(UserDataStoragePrefix, "").Replace(".", "") & "_" & DateTime.Now.ToString("yyyyMMddHHmmss"))
    End Sub

#End Region

#Region "Actions"
    Private Sub CreateActionsButtons(ByVal tableid As Integer, isReadOnly As Boolean)
        Dim ds As DataTable = (New SqlFunctions).GetData("SELECT * FROM [UserModules].[dbo].[TableActioins] WHERE tableId = " & tableid & " AND enabled = 1 ORDER BY position")
        If Not IsNothing(ds) Then
            If ds.Rows.Count > 0 Then
                phActions.Controls.Clear()
                For Each r As DataRow In ds.Rows
                    If r("type") = "IMAGE" Then
                        Dim ib As New Image With {.CssClass = "gridaction"}
                        ib.ID = ID & "_ACTION_" & r("name") & "_" & r("type")
                        ib.ToolTip = r("description")
                        ib.ImageUrl = r("iconurl") ' "~\images\buttons\24\wizard.png"
                        ib.Style("padding-right") = "10px"
                        ib.Attributes.Add("CommandName", r("VALUES"))
                        phActions.Controls.Add(ib)
                    ElseIf Not isReadOnly Then
                        Dim ib As New ImageButton
                        ib.ID = ID & "_ACTION_" & r("name") & "_" & r("type")
                        ib.CommandName = r("Type") & "_" & r("targetfields").ToString.Replace("_", UserSeparator) & "_" & r("valuetype")
                        ib.CommandArgument = r("values")
                        ib.ToolTip = r("description")
                        ib.ImageUrl = r("iconurl") ' "~\images\buttons\24\wizard.png"
                        ib.Style("padding-right") = "10px"
                        AddHandler ib.Command, AddressOf ActionHandler
                        phActions.Controls.Add(ib)
                    End If
                Next
            End If
        End If
    End Sub



    Protected Sub ActionHandler(ByVal sender As Object, ByVal e As EventArgs)
        Dim CommandName As String = CType(e, CommandEventArgs).CommandName
        Dim CommandArgument As String = CType(e, CommandEventArgs).CommandArgument
        Dim actionType As String = CommandName.Split("_")(0)
        Dim targetFields As String = CommandName.Split("_")(1).Replace(UserSeparator, "_")
        Dim actionValues As String = CommandArgument
        Dim ValueType As String = CommandName.Split("_")(2)
        Dim Value As String = ""
        Select Case CommandName.Split("_")(0)
            Case "FILTER"
                If ValueType = "SQL" Then
                    Value = CType((New SqlFunctions).GetScalar("SELECT " & actionValues.Replace("<", "")), DateTime).ToString(dateFormat)
                End If

                UpdateFilters(targetFields, "", actionValues)
                RefreshData(True)

                Dim c As Control = phFilters.FindControl(ID & "_FILTER_" & targetFields.ToString.Replace("_", UserSeparator) & "_to")
                If Not IsNothing(c) Then
                    CType(c, TextBox).Text = Value
                End If
            Case "ACTION"
                RaiseEvent ActionRequest(sender, e)
        End Select
    End Sub
#End Region

#Region "Data upload"
    Protected Sub ibUpload_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ibUpload.Click
        Dim iTotalLines As Integer
        Dim iInsertedLines As Integer

        Dim dtStart As DateTime = DateTime.Now

        UploadCSV(txtDataToUpload.Text, iTotalLines, iInsertedLines)

        ltrUploadResults.Text = "Duration (sec): " & DateTime.Now.Subtract(dtStart).Seconds

        txtDataToUpload.Text = ""

        lblMessageText.Text = "Total lines: " & iTotalLines & " / Uploaded Rows: " & iInsertedLines
        RefreshData()
    End Sub

    Public Sub UploadCSV(text As String, Optional ByRef TotalLines As Integer = 0, Optional ByRef InsertedLine As Integer = 0)
        Dim lines() As String = text.Split(Chr(10))
        TotalLines = lines.Count
        Dim com As New SqlCommand
        InsertedLine = 0
        Dim sql As New StringBuilder
        Dim i As Integer = 0
        Dim first As Boolean = False

        Dim batchCounter As Integer = 0

        For Each line As String In lines
            If line <> "" Then
                Dim vals() As String = line.Split(vbTab)
                If vals.Length > 0 Then
                    Try
                        sql.Append("INSERT INTO " & FullTableName & "(")
                        i = 1
                        For Each c In FieldList
                            If first Then
                                sql.Append(",")
                            End If
                            sql.Append("[" & c & "]")
                            first = True
                            i += 1
                        Next
                        sql.Append(") VALUES (")
                        first = False
                        i = 1
                        For Each c In FieldList
                            If first Then
                                sql.Append(",")
                            End If
                            If GetFieldType(c) = "ENCODED TEXT" Then
                                sql.Append("hr.dbo.fn_encodeString(@" & c & ",'name')") '<<<<<<    Добавить кодирование для ENCODED TEXT
                            Else
                                sql.Append("@" & c)
                            End If

                            first = True
                            i += 1
                        Next
                        sql.Append(")")
                        com.CommandText = sql.ToString
                        com.Parameters.Clear()
                        i = 0
                        With com.Parameters
                            first = False
                            i = 1
                            Dim j As Integer = 0
                            For Each c In FieldList
                                If FieldVisibility(i) = "Y" Then
                                    If j + 1 <= vals.Count Then
                                        vals(j) = vals(j).ToString.Replace(Chr(160), " ").Trim
                                        Select Case FieldTypes(i)
                                            Case "NUMBER"
                                                vals(j) = vals(j).Replace(" ", "").Replace(",", ".")
                                                'Select Case rblDecimalNotation.SelectedValue
                                                    'Case 0 : vals(j) = vals(j).Replace(" ", "").Replace(".", "").Replace(",", ".")
                                                    'Case 1 : vals(j) = vals(j).Replace(" ", "").Replace(",", "")
                                                'End Select
                                            Case "DATE"
                                                Try
                                                    Select Case rblDateFormat.SelectedValue
                                                        Case 0 : vals(j) = Date.ParseExact(vals(j).Trim, "dd.MM.yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd")
                                                        Case 1
                                                    End Select
                                                Catch ex As Exception

                                                End Try
                                            Case Else
                                        End Select
                                        .AddWithValue("@" & c, vals(j))
                                    Else
                                        .AddWithValue("@" & c, "")
                                    End If
                                    j += 1
                                Else
                                    .AddWithValue("@" & c, "")
                                End If
                                i += 1
                            Next
                        End With
                        batchCounter += 1


                        If com.Parameters.Contains("@CREATE_DATE") Then
                            com.Parameters("@CREATE_DATE").Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                        End If
                        RaiseEvent NewRecordSaving(com) '.Parameters)

                        Dim newRecordId As Integer = sqlf.Insert(com)

                        RaiseEvent NewRecordSaved(newRecordId)

                        InsertedLine += 1 ' batchCounter
                        sql.Clear()
                        'End If
                    Catch ex As Exception
                        sql.Clear()
                    End Try
                End If
            End If
        Next
    End Sub

    Public Function UploadDataTable(tbl As DataTable) As String
        Dim com As New SqlCommand
        Dim InsertedLines As Integer = 0
        Dim sql As New StringBuilder
        Dim i As Integer = 0
        Dim first As Boolean = False

        Dim batchCounter As Integer = 0

        For Each dr As DataRow In tbl.Rows
            If dr.ItemArray.Length > 0 Then
                Try
                    'Prepare SQL string with parameters
                    sql.Append("INSERT INTO " & FullTableName & "(")
                    i = 1
                    For Each c In FieldList
                        If first Then
                            sql.Append(",")
                        End If
                        sql.Append("[" & c & "]")
                        first = True
                        i += 1
                    Next
                    sql.Append(") VALUES (")
                    first = False
                    i = 1
                    For Each c In FieldList
                        If first Then
                            sql.Append(",")
                        End If
                        If GetFieldType(c) = "ENCODED TEXT" Then
                            sql.Append("hr.dbo.fn_encodeString(@" & c & ",'name')") '<<<<<<    Добавить кодирование для ENCODED TEXT
                        Else
                            sql.Append("@" & c)
                        End If

                        first = True
                        i += 1
                    Next
                    sql.Append(")")
                    com.CommandText = sql.ToString
                    com.Parameters.Clear()
                    i = 0
                    With com.Parameters
                        first = False
                        i = 1
                        Dim j As Integer = 0
                        For Each c In FieldList
                            If FieldVisibility(i) = "Y" Then
                                If j + 1 <= dr.ItemArray.Length Then
                                    dr.Item(j) = dr.Item(j).ToString.Replace(Chr(160), " ").Trim
                                    Select Case FieldTypes(i)
                                        Case "NUMBER"
                                            Select Case rblDecimalNotation.SelectedValue
                                                Case 0 : dr.Item(j) = dr.Item(j).Replace(" ", "").Replace(".", "").Replace(",", ".")
                                                Case 1 : dr.Item(j) = dr.Item(j).Replace(" ", "").Replace(",", "")
                                            End Select
                                        Case "DATE"
                                            Try
                                                Select Case rblDateFormat.SelectedValue
                                                    Case 0 : dr.Item(j) = Date.ParseExact(dr.Item(j).Trim, "dd.MM.yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd")
                                                    Case 1
                                                End Select
                                            Catch ex As Exception

                                            End Try
                                        Case Else
                                    End Select
                                    .AddWithValue("@" & c, dr.Item(j))
                                Else
                                    .AddWithValue("@" & c, "")
                                End If
                                j += 1
                            Else
                                .AddWithValue("@" & c, "")
                            End If
                            i += 1
                        Next
                    End With
                    batchCounter += 1

                    RaiseEvent NewRecordSaving(com)

                    Dim newRecordId As Integer = sqlf.Insert(com)

                    RaiseEvent NewRecordSaved(newRecordId)

                    InsertedLines += 1
                    sql.Clear()
                Catch ex As Exception
                    sql.Clear()
                End Try
            End If
        Next
        Return InsertedLines
    End Function
#End Region
    Protected Sub ibSendEmail0_Click(sender As Object, e As ImageClickEventArgs) Handles doSendEmail.Click
        If txtMailRecepients.Text <> "" Then
            Dim recepients() As String = txtMailRecepients.Text.Split({Chr(10), Chr(13)})
            If recepients.Count > 0 Then
                Dim body As String = txtMessageText.Text & "<hr />" & GetHTMLGrid()
                Dim bodyGRaph As String = ""

                RaiseEvent RequestForGraphData(bodyGRaph)
                body &= "<hr />" & bodyGRaph
                Dim UserTableName As String = sqlf.GetScalar("SELECT tablename FROM UserModules.dbo.ModuleTables where id = " & TableId)
                Dim subject As String
                If mailTitle.Text = "" Then
                    subject = UserTableName & " [by " & UserName & "]"
                Else
                    subject = mailTitle.Text
                End If
                If separateCopy.Checked Then
                    For Each r In recepients
                        If r <> "" Then
                            CommonFunctions.SendEmail(r, subject, body)
                        End If
                    Next
                Else
                    Dim recepientList As String = ""
                    For Each r In recepients
                        If r <> "" Then
                            If r.Contains("@") Then
                                recepientList &= r & ";"
                            Else
                                recepientList &= r & "@samsung.com;"
                            End If

                        End If
                    Next
                    CommonFunctions.SendEmail2(recepientList, subject, body)
                End If
            End If
        End If
    End Sub

    Protected Sub txtPagerSIze_TextChanged(sender As Object, e As EventArgs) Handles txtPagerSIze.TextChanged
        If IsNumeric(txtPagerSIze.Text) Then
            PageSize = txtPagerSIze.Text
            'RefreshData()
        Else
            txtPagerSIze.Text = PageSize
        End If
        If PageSize > 150 Then
            PageSize = 150
            txtPagerSIze.Text = 150
        End If
        currentPageIndex = 0
        txtPagerSIze.Focus()
        'lblEditingObject.Value = ""
        RefreshData()
    End Sub

    Private Function CheckParam(ControlParam As String, rowVIew As Data.DataRowView) As String
        Dim ControlValue As String = Nothing
        If Not IsNumeric(ControlParam) Then
            Try
                ControlValue = rowVIew(ControlParam)
            Catch ex As Exception
                ControlValue = ControlParam
            End Try
        Else
            ControlValue = ControlParam
        End If
        If ControlValue = "" Then
            ControlValue = "''"
        End If
        Return ControlValue
    End Function

#Region "Misc"
    Protected Sub HidePopup()
        pnlEXcelDownloadOptions.CssClass &= " hidden"
    End Sub

    Public Sub CreateGroupingText()
        Try
            Dim dt As Data.DataTable = sqlf.GetData("SELECT * FROM UserModules.dbo.GROUPING_TEXT WHERE enabled='Y' and  table_id = " & TableId & " and isnull(view_id,-1)=" & ViewID)
            If dt.Rows.Count > 0 Then
                Dim c As New Collection

                Dim a(,) As String = Array.CreateInstance(GetType(String), 100, 3)
                Dim i As Integer = 0

                Dim style As New Collection

                For Each dr As DataRow In dt.Rows
                    a(i, 0) = dr("Grouping_Text")
                    style.Add(dr("style"))

                    Dim pos As DataTable = sqlf.GetData("SELECT isnull(v.position,isnull(t.position,9999)) as position " &
                                                        " FROM UserModules.dbo.TableFields t " &
                                                        " LEFT JOIN UserModules.dbo.views v on t.id = v.field_id " &
                                                        " and isnull(v.view_id, -1) = " & ViewID &
                                                        " WHERE t.tableid=" & TableId &
                                                        " and t.DbFieldName in ('" & dr("START_COLUMN_DB_NAME") & "','" & dr("END_COLUMN_DB_NAME") & "') ORDER BY isnull(v.position,isnull(t.position,9999)) ASC")
                    a(i, 1) = pos.Rows(0)(0)
                    a(i, 2) = pos.Rows(1)(0) - pos.Rows(0)(0) + 1

                    i += 1
                Next
                CommonFunctions.ApplyGridViewHeader(ID.ToString, GridView1, a, style)
            End If
        Catch ex As Exception
        End Try
    End Sub
    Public Sub CollectHeaderColumn(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 GridView1.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 Sub AuthorityCheck()
        If TableId <> -1 Then
            Dim accessParams As Hashtable = authorization.CheckAuthorityForTable(UserName, TableId)
            accessType = accessParams("ACCESSTYPE")
            fields = accessParams("FIELDS")

            Try
                _tableParams = sqlf.GetRow("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  from [UserModules].[dbo].[ModuleTables] where id = " & TableId)
                Dim dr As DataRow = _tableParams
                If dr("RESTRICTED_ACCESS") = "Y" Then

                    If accessType.Contains("E") Or accessType.Contains("F") Then
                        IsReadOnly = False
                        pnlGrid.Visible = True
                        pnlNoAccess.Visible = False

                        allowCellsEdit = True
                        allowRowDelete = True
                        allowRowAdd = True
                    End If

                    If accessType.Contains("I") Then
                        allowRowAdd = True
                    End If

                    If accessType.Contains("D") Then
                        allowRowDelete = True
                    End If

                    If accessType.Contains("R") Then
                        IsReadOnly = True
                        pnlGrid.Visible = True
                        pnlNoAccess.Visible = False
                        'Else
                        '    pnlGrid.Visible = False
                        '    pnlNoAccess.Visible = True
                        '    lblResponsble.Text = dr("CREATED_BY")
                    End If
                Else
                    If accessType.Contains("E") Or accessType.Contains("F") Then
                        IsReadOnly = False
                    Else
                        IsReadOnly = True
                    End If

                    pnlGrid.Visible = True
                    pnlNoAccess.Visible = False
                    pnlNoTableInfo.Visible = False
                End If

                If dr("BULK_DATA_UPLOAD") = "Y" Then
                    DataUpload.Visible = True
                Else
                    DataUpload.Visible = False
                End If

                If dr("SEND_MAIL") = "Y" Then
                    ibSendEmail.Visible = True
                Else
                    ibSendEmail.Visible = False
                End If

                If dr("ALLOW_ALL_DELETE") = "Y" Then
                    ibDeleteData.Visible = True
                    pnlDeleteAll.Visible = True
                Else
                    ibDeleteData.Visible = False
                    pnlDeleteAll.Visible = False
                End If

                _schema = dr("SCHEMA")

                TableName = "[" & dr("dbTableName") & "]"
                lblCaption.Text = dr("tableName")
                If Not IsPostBack Then
                    If PageSize = 1 Then
                        PageSize = IIf(IsNumeric(dr("pager_size")), dr("pager_size"), 15)
                    End If
                End If
            Catch ex As Exception
                IsReadOnly = True
                pnlGrid.Visible = False
                pnlNoAccess.Visible = True
                pnlNoTableInfo.Visible = False
            End Try
        Else
            pnlGrid.Visible = False
            pnlNoAccess.Visible = False
            pnlNoTableInfo.Visible = True
        End If

        RaiseEvent AuthorityCheckComplete(IsReadOnly)
    End Sub

    Public Sub Refresh()
        'upMain.Update()
    End Sub

    Private Function GetColumnsConfiguration(ByVal tableName As String, viewId As Integer) As Data.DataTable
        Try
            tableName = tableName.Split(".")(2).Replace("[", "").Replace("]", "")
            Dim sql As New StringBuilder
            sql.Append("USE UserModules ")
            sql.Append("SELECT '" & tableName & "' AS TableName, ")
            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(V.enable,isnull(t2.[Enabled] ,'Y')) AS [enabled] , ")
            sql.Append("isnull(v.[position],isnull(t2.[Position],9999)) AS [position] , ")
            sql.Append("isnull(v.visible,isnull(t2.[Visible] ,'Y')) AS [Visible] , ")
            sql.Append("isnull(v.filter,isnull(t2.[Filter] ,'N')) AS [Filter] , ")
            sql.Append("isnull(v.search,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("isnull(v.style,isnull(t2.[STYLE] ,'')) AS [STYLE], ")
            sql.Append("isnull(t2.[key] ,'N') AS [key], ")
            sql.Append("isnull(t2.[DEFAULT_VALUE] ,'') AS [DEFAULT_VALUE], isnull([DEFAULT_SORT],'') as [DEFAULT_SORT], ")
            sql.Append("isnull([INSERT_VISIBLE],'Y') as [INSERT_VISIBLE], ")
            sql.Append("isnull([FILTER_TYPE],' ') as [FILTER_TYPE], ")
            sql.Append("isnull([ENCODE],'N') as [ENCODE] ")

            'sql.Append(",isnull([NEW_ROW_ADDED],'') as NEW_ROW_ADDED ")
            sql.Append("FROM sys.columns t1 ")
            sql.Append("LEFT JOIN ")
            sql.Append("	(SELECT * ")
            sql.Append("	FROM TableFields ")
            sql.Append("	WHERE Tableid IN ")
            sql.Append("		(SELECT Id FROM Moduletables WHERE Dbtablename = '" & tableName & "'	) ")
            sql.Append("	)t2 ")
            sql.Append("ON t1.name = t2.DbFieldName ")
            sql.Append("LEFT JOIN views V ")
            sql.Append("on t2.id = v.field_id ")
            sql.Append("and v.view_id = " & viewId)
            sql.Append("WHERE t1.object_id IN ")
            sql.Append("	( SELECT object_id FROM sys.tables WHERE name = '" & tableName & "'	AND  schema_id in (SELECT schema_id FROM sys.SCHEMAS where name = 'user') ) ")
            sql.Append("ORDER BY isnull(v.position,isnull(t2.position,9999)) ASC ")

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

    Private Function CalculateFormula(ByVal formula As String, ByVal r As GridViewRow) As String
        Dim arr() As String = formula.Split(" ")
        Dim frmla As New ArrayList
        For Each s As String In arr
            'If {"*", "/", "+", "-"}.Contains(s) Then
            '    frmla.Add("OPERATOR", s)
            'Else
            '    frmla.Add("VALUE", s)
            'End If
            frmla.Add(s)
        Next

        Dim i As Integer = 0
        For i = 0 To frmla.Count - 1
            Dim s As String = frmla(i)
            Select Case s
                Case "*"
                Case "+"
                Case "/"
                Case "-"
                Case Else
                    Dim val As String = GetFieldValue(s, r)
                    If Not IsNothing(val) Then
                        frmla(i) = Convert.ToDecimal(val)
                    End If
            End Select
        Next

        For Each s As String In frmla
            Select Case s
                Case "*"
                    Return (frmla(0) * frmla(2)).ToString
                Case "+"
                    Return (frmla(0) + frmla(2)).ToString
                Case "/"
                    Return (frmla(0) / frmla(2)).ToString
                Case "-"
                    Return (frmla(0) - frmla(2)).ToString
            End Select
        Next

        Return Nothing
    End Function

    Private Function GetFieldValue(ByVal fieldName As String, ByVal r As GridViewRow) As String
        Dim gridColCount As Integer = 0
        Dim colCount As Integer = 0
        For Each c As TableCell In r.Cells
            If Not commandColumns.Contains(gridColCount) Then
                If Not skipColumns.Contains(colCount) Then
                    If fieldName = FieldList(colCount) Then
                        If FieldTypes(colCount) = "FORMULA" Then
                            Return CalculateFormula(FieldFormulas(colCount), r)
                        Else
                            Return c.Text
                        End If
                    End If
                End If
                colCount += 1
            End If
            gridColCount += 1
        Next
        Return Nothing
    End Function

    Private Sub RegisterEvent(_tableID As Integer, _userId As String)
        Dim com As New SqlCommand("INSERT INTO UserModules.dbo.EventLog (tableId, userId) VALUES (@tableId, @userId)")
        With com.Parameters
            .AddWithValue("tableId", _tableID)
            .AddWithValue("userId", _userId)
        End With
        Call sqlf.NonQuery(com)
    End Sub

    Private Sub BuildList(ByRef p As Panel, parentid As Integer, field As String, showMode As String, Optional ShowAsList As Boolean = False,
                          Optional toExcel As Boolean = False, Optional imgStyle As String = "")
        p.Controls.Clear()
        Try
            Dim files As DataTable = sqlf.GetData("SELECT [id], [name] FROM UserModulesAttachments.[user]." & TableName &
                                                  " WHERE [deleted] = 'N' AND [type] = '" & field & "' and [parentId] = " & parentid)
            Dim remoteFiles As DataTable = Nothing
            Dim remoteTableName As String = ""
            Dim remoteFieldName As String = ""
            Dim mappingCondition As String = ""
            Try
                Dim f As String = config.GetParam(field, "FORMULA")
                If f <> "" Then
                    Dim fparams() As String = f.Split("|")
                    If fparams.Length = 3 Then
                        remoteTableName = fparams(0)
                        remoteFieldName = fparams(1)
                        mappingCondition = fparams(2)

                        mappingCondition = "cast(" & mappingCondition.Split("=")(0) & " as varchar) collate Cyrillic_General_CI_AI = " &
                                            "cast(" & mappingCondition.Split("=")(1) & " as varchar) collate Cyrillic_General_CI_AI "
                        Dim sql As New StringBuilder
                        sql.AppendLine("SELECT ")
                        sql.AppendLine("F.ID, F.NAME FROM ")
                        sql.AppendLine("UserModules.[user]." & TableName & " THIS_TABLE ")
                        sql.AppendLine("LEFT JOIN UserModules.[user]." & remoteTableName & " REMOTE_TABLE ")
                        sql.AppendLine("ON " & mappingCondition)
                        sql.AppendLine("LEFT JOIN UserModulesAttachments.[user]." & remoteTableName & " F ")
                        sql.AppendLine("ON REMOTE_TABLE.ID = F.PARENTID ")
                        sql.AppendLine("AND F.TYPE='" & remoteFieldName & "' ")
                        sql.AppendLine("WHERE F.DELETED = 'N' AND THIS_TABLE.ID = " & parentid)

                        remoteFiles = sqlf.GetData(sql.ToString)
                    End If
                End If

            Catch ex As Exception

            End Try
            If ShowAsList Then
                If FilesAsLinks Then
                    showMode = "FILE"
                End If
                Select Case showMode
                    Case "PHOTO"
                        Dim tblPhotoes As New Table
                        For Each dr As DataRow In files.Rows
                            Dim img As New Image

                            If imgStyle <> "" Then
                                img.Attributes.Add("style", imgStyle)
                            End If
                            img.CssClass = "thumbnail"
                            img.ID = "img" & dr("id")
                            img.ImageUrl = Request.Url.GetLeftPart(UriPartial.Authority) &
                                Request.ApplicationPath & "/handlers/getimage.ashx?mode=thumb&id=" & dr("id") & "&storage=UserModulesAttachments.[user]." & TableName
                            Dim r As New TableRow
                            Dim c1 As New TableCell
                            c1.Controls.Add(img)
                            r.Cells.Add(c1)
                            tblPhotoes.Rows.Add(r)
                        Next
                        Try
                            If Not IsNothing(remoteFiles) Then
                                For Each dr As DataRow In remoteFiles.Rows
                                    If dr("name") <> "" Then
                                        Dim img As New Image
                                        img.CssClass = "thumbnail"
                                        img.ID = "img" & dr("id")
                                        img.ImageUrl = Request.Url.GetLeftPart(UriPartial.Authority) &
                                            Request.ApplicationPath & "/handlers/getimage.ashx?mode=thumb&id=" & dr("id") & "&storage=UserModulesAttachments.[user]." & remoteTableName
                                        Dim r As New TableRow
                                        Dim c1 As New TableCell
                                        c1.Controls.Add(img)
                                        r.Cells.Add(c1)
                                        tblPhotoes.Rows.Add(r)
                                    End If
                                Next
                            End If
                        Catch ex As Exception

                        End Try
                        p.Controls.Add(tblPhotoes)
                    Case Else
                        Dim ltrFilesList As New Literal
                        For Each dr As DataRow In files.Rows
                            If ltrFilesList.Text <> "" And dr("name").ToString.Trim <> "" Then
                                ltrFilesList.Text &= "<br />"
                                'ltrFilesList.Text &= " "
                            End If
                            ltrFilesList.Text &= "<a href='" & Request.Url.GetLeftPart(UriPartial.Authority) &
                                Request.ApplicationPath & "/handlers/getfile.ashx?id=" &
                                dr("id") & "&storage=UserModulesAttachments.[user]." & TableName & "'>" & dr("name") & "</a>"
                        Next
                        Try
                            If Not IsNothing(remoteFiles) Then
                                For Each dr As DataRow In remoteFiles.Rows
                                    If dr("name") <> "" Then
                                        If ltrFilesList.Text <> "" And dr("name").ToString.Trim <> "" Then
                                            ltrFilesList.Text &= "<br />"
                                            'ltrFilesList.Text &= " "
                                        End If
                                        ltrFilesList.Text &= "<a href='" & Request.Url.GetLeftPart(UriPartial.Authority) &
                                            Request.ApplicationPath & "/handlers/getfile.ashx?id=" &
                                            dr("id") & "&storage=UserModulesAttachments.[user]." & remoteTableName & "'>" & dr("name") & "</a>"
                                    End If
                                Next
                            End If
                        Catch ex As Exception

                        End Try
                        p.Controls.Add(ltrFilesList)
                End Select
            Else
                Dim tblFiles As New Table
                tblFiles.ClientIDMode = UI.ClientIDMode.Static

                tblFiles.ID = "tblfiles_" & _tableId & "_" & parentid & "_" & field
                tblFiles.Style.Add("border", "none")
                tblFiles.Style.Add("border-collapse", "collapse")
                ' GEt files aasotiated with current table
                Dim firstRow As TableRow = Nothing
                Dim rowCount As Integer = 0

                For Each dr As DataRow In files.Rows
                    Dim cntrl As Control
                    Dim b As New HyperLink
                    Dim img As New Image

                    Select Case showMode
                        Case "PHOTO"
                            img.ImageUrl = "~/handlers/getimage.ashx?mode=thumb&id=" & dr("id") & "&storage=UserModulesAttachments.[user]." & TableName

                            If imgStyle <> "" Then
                                img.Attributes.Add("style", imgStyle)
                            End If
                            img.CssClass = "thumbnail"


                            img.ID = "img" & dr("id")
                            cntrl = img
                        Case Else
                            b.Text = dr("name")
                            b.NavigateUrl = "~/handlers/getfile.ashx?id=" & dr("id") & "&storage=UserModulesAttachments.[user]." & TableName
                            cntrl = b
                    End Select

                    Dim ib As New Image
                    ib.ImageUrl = "~/Images/Buttons/set2/delete.png"

                    ib.Attributes.Add("fileid", dr("id"))
                    ib.Attributes.Add("tid", _tableId)

                    ib.ID = "btnDeleteFile_" & _tableId & "_" & dr("id")
                    ib.ClientIDMode = UI.ClientIDMode.Static
                    ib.ToolTip = "Delete file"
                    ib.CssClass = "filedelete"

                    Dim r As New TableRow
                    If rowCount = 0 Then
                        firstRow = r
                    End If
                    rowCount += 1

                    r.ID = "row_" & dr("id")
                    r.ClientIDMode = UI.ClientIDMode.Static

                    Dim c1 As New TableCell
                    Dim c2 As New TableCell

                    c1.CssClass = "noborder"
                    c2.CssClass = "noborder"

                    c1.Controls.Add(cntrl)
                    c2.Controls.Add(ib)
                    r.Cells.Add(c1)
                    r.Cells.Add(c2)

                    tblFiles.Rows.Add(r)
                Next

                Try
                    '--------------------------------------------------------------------------------------
                    'Get files specified by formula
                    '--------------------------------------------------------------------------------------

                    If Not IsNothing(remoteFiles) Then
                        For Each dr As DataRow In remoteFiles.Rows
                            If dr("name") <> "" Then
                                Dim cntrl As Control
                                Dim b As New HyperLink
                                Dim img As New Image

                                Select Case showMode
                                    Case "PHOTO"
                                        img.ImageUrl = "~/handlers/getimage.ashx?mode=thumb&id=" & dr("id") & "&storage=UserModulesAttachments.[user]." & remoteTableName
                                        img.CssClass = "thumbnail"
                                        img.ID = "img" & dr("id")
                                        cntrl = img
                                    Case Else
                                        b.Text = dr("name")
                                        b.NavigateUrl = "~/handlers/getfile.ashx?id=" & dr("id") & "&storage=UserModulesAttachments.[user]." & remoteTableName
                                        cntrl = b
                                End Select

                                Dim r As New TableRow
                                r.ID = "row_" & dr("id")
                                r.ClientIDMode = UI.ClientIDMode.Static

                                Dim c1 As New TableCell
                                Dim c2 As New TableCell

                                c1.CssClass = "noborder"
                                c2.CssClass = "noborder"

                                c1.Controls.Add(cntrl)
                                c2.Controls.Add(New Literal With {.Text = "&nbsp;"})
                                r.Cells.Add(c1)
                                r.Cells.Add(c2)

                                tblFiles.Rows.Add(r)
                            End If
                        Next
                    End If
                    '---------------------------------------------
                Catch ex As Exception

                End Try

                p.Controls.Add(tblFiles)

                p.HorizontalAlign = HorizontalAlign.Center
                p.Width = Unit.Percentage(100)


                Dim imgaddfile As New Image With {.ImageUrl = "~/Images/Buttons/set2/add.png", .ToolTip = "Add file"}
                imgaddfile.ID = "btnAddFile_" & field & "_" & parentid
                imgaddfile.CssClass = "fileupload"
                imgaddfile.Attributes.Add("field", field)
                imgaddfile.Attributes.Add("rownum", parentid)
                imgaddfile.Attributes.Add("tid", _tableId)
                imgaddfile.Attributes.Add("showmode", showMode)

                If IsNothing(firstRow) Then
                    p.Controls.Add(imgaddfile)
                Else
                    Dim c As New TableCell With {.RowSpan = rowCount, .CssClass = "noborder"}
                    firstRow.Cells.Add(c)
                    c.Controls.Add(imgaddfile)
                End If

            End If
        Catch ex As Exception

        End Try
    End Sub

#End Region

    Protected Sub ExDownCancel_DataBinding(sender As Object, e As EventArgs) Handles ExDownCancel.DataBinding

    End Sub


    Protected Sub ddlView_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlView.SelectedIndexChanged
        If ddlView.SelectedValue <> -1 Then
            ViewID = ddlView.SelectedValue
        Else
            ViewID = -1
        End If
        BuildColumnsConfiguration()
    End Sub


End Class