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

ModuleMaster/usercontrols/editablegrid.ascx.vb
3 436 строк147 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

Partial Class UserControls_editableGrid
    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 ShowCheckBoxAsText As Boolean = False

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

    Private Const BatchSize As Integer = 100

    Private ExcelDownloadNow As Boolean = False
    Private WithInvisibleFields As Boolean = False

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

    Private sqlf As New SqlFunctions
#End Region

#Region "Public Properties"
    ' --------------  Experimental ------------------------
    Private _config As edGridNamespace.tableConfig = Nothing

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

    End Property

    Public ReadOnly Property Actions As PlaceHolder
        Get
            Return phActions
        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 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 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)
    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

#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(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 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
        Else
            queryFilters = Nothing
        End If

        If IsNothing(ColumnsConfiguration) Then
            BuildColumnsConfiguration()
        End If

        '-------------Experimental 
        Dim _formulas As New Hashtable
        Try
            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) Then
                        ff &= p
                    Else
                        ff &= "parseFloat(getCellValue(""" & p & """, RowIndex))"
                    End If
                Next
                ff &= ")"
                _formulas.Add(r("name"), ff)
            Next
            Dim serializer As New JavaScriptSerializer()
            formulas.Value = serializer.Serialize(_formulas)
        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

        '-----------------------------

        RefreshFilters()

        If Not IsPostBack Then
            Try
                'Dim defaultSorting() As DataRow = ColumnsConfiguration.Select("default_sort<>''")
                'If Not IsNothing(defaultSorting) 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
            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

        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()
        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
    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
        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.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
                        If Not FieldList.Contains(colCount) Then
                            FieldList.Add(rowView.DataView.Table.Columns(colCount).ColumnName, colCount)
                        End If
                        Try
                            Try
                                c.Attributes.Add("rownum", rowView("id"))
                                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"))
                            Catch ex As Exception

                            End Try


                            Dim dr As DataRow = ColumnsConfiguration.Select("name='" & rowView.DataView.Table.Columns(colCount).ColumnName & "'")(0)

                            'Authoriztion on a column level
                            '
                            If fields <> "" Then
                                If Not fields.Contains(dr("DbFieldName")) Then
                                    dr("enabled") = "N"
                                End If
                            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
                            If Not emptyFlag Then 'And Not bReadOnly And dr("enabled") = "Y" Then
                                If Not bReadOnly And dr("enabled") = "Y" Then
                                End If


                                Dim uc As UserControls_gridEditControl = LoadControl("~/UserControls/gridEditControl.ascx")
                                uc.Text = c.Text
                                uc.Table = sTableName
                                uc.Field = rowView.DataView.Table.Columns(colCount).ColumnName
                                uc.RecordID = rowView("ID")
                                uc.ID = ID & "_" & "_" & uc.Table.Replace(UserDataStoragePrefix, "") & "_" & uc.Field & "_" & rowView("id")

                                AddHandler uc.Editing, AddressOf EditingHandler
                                AddHandler uc.Saving, AddressOf SavingHandler
                                AddHandler uc.Saved, AddressOf SavedHandler
                                AddHandler uc.Cancelled, AddressOf CancelledHandler

                                Dim cntrl As Control
                                cntrl = uc
                                Try
                                    Select Case dr("FieldType")
                                        Case "LITERAL"
                                            Dim l As New LiteralControl
                                            l.Text = rowView.Item(colCount)
                                            c.Controls.Add(l)
                                            Continue For

                                        Case "DROPDOWNLIST"
                                            Dim params() As String = dr("Formula").ToString.Split("|")
                                            If IsNothing(phFilters.FindControl(ID & "_" & uc.Field & "_datasource")) Then
                                                Dim sql As String = ""
                                                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
                                                        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 & "_" & uc.Field & "_datasource"
                                                phFilters.Controls.Add(ds)
                                            End If
                                            uc.DataSource = ID & "_" & uc.Field & "_datasource"
                                            uc.DataTextField = "FieldText"
                                            uc.DataValueField = "FieldValue"
                                            uc.Type = "DROPDOWNLIST"
                                            c.HorizontalAlign = HorizontalAlign.Center
                                        Case "DATE", "CHECKBOX", "NUMBER", "TEXTBOX", "URL", "MARK", "TIME"
                                            If dr("FieldType") = "MARK" Then
                                                Select Case uc.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
                                            If dr("FieldType") = "TEXTBOX" Then
                                                Dim literalText As String = ""
                                                For Each stringChar As Char In uc.Text
                                                    If stringChar = Chr(10) Then
                                                        literalText &= "<br>"
                                                    Else
                                                        literalText &= stringChar
                                                    End If
                                                Next
                                                uc.Text = literalText
                                            End If

                                            uc.Type = dr("fieldtype")
                                            uc.OperationMode = "INLINE"
                                            uc.IsReadOnly = (dr("enabled") = "N")
                                            If dr("FieldType") = "CHECKBOX" Or dr("FieldType") = "MARK" Then
                                                c.HorizontalAlign = HorizontalAlign.Center
                                            ElseIf dr("FieldType") = "NUMBER" Then
                                                c.HorizontalAlign = HorizontalAlign.Right
                                            End If
                                        Case "CONDENSED TEXT"
                                            If Not ExcelDownloadNow Then
                                                Dim ct As ASP.usercontrols_condensedtext_ascx = LoadControl("~/UserControls/condensedText.ascx")
                                                ct.Text = rowView(dr("DBFIELDNAME")) 'c.Text
                                                ct.TableName = TableName
                                                ct.RecordId = rowView("ID")
                                                ct.FieldName = dr("DBFIELDNAME")
                                                If IsNumeric(dr("Formula")) Then
                                                    ct.ShortTextLength = dr("Formula")
                                                Else
                                                    ct.ShortTextLength = 8
                                                End If
                                                ct.isReadOnly = bReadOnly Or (dr("ENABLED") = "N")
                                                cntrl = ct
                                            End If
                                        Case "FORMULA", "DATEFORMULA", "TIMEFORMULA"
                                            uc.Type = "FORMULA"
                                            uc.IsReadOnly = True
                                            c.HorizontalAlign = HorizontalAlign.Right

                                            ' --------------  Experimental ------------------------
                                            If config.GetParam(colCount, "Formula") <> "" Then
                                                c.CssClass = "formula"

                                                c.ToolTip = config.GetParam(colCount, "Formula")
                                            End If
                                            '-------------------------------------------------------------
                                        Case "FILE", "PHOTO"
                                            'Dim fc As ASP.usercontrols_advancedfileslist_ascx = LoadControl("~/UserControls/advancedFilesList.ascx")
                                            'fc.SavePath = Server.MapPath("") & "\UploadedFiles\"
                                            'fc.GroupId = rowView("ID")
                                            'fc.GroupType = dr("dbfieldname")
                                            'fc.StorageName = FullTableName.Replace(UserDataStoragePrefix, AttachmentsStoragePrefix)
                                            'fc.ShowAsList = bReadOnly Or (dr("enabled") = "N")
                                            'fc.BuildFileList(bReadOnly Or (dr("enabled") = "N"))
                                            'AddHandler fc.AddedAttachment, AddressOf AddAttachment
                                            'AddHandler fc.DeletedAttachment, AddressOf DeleteAttachment

                                            'cntrl = fc


                                            'c.CssClass = "fileupload"
                                            Dim p As New Panel
                                            BuildList(p, rowView("ID"), config.GetParam(colCount, "NAME"), dr("FieldType"), bReadOnly Or (dr("enabled") = "N"), ExcelDownloadNow, dr("STYLE"))
                                            cntrl = p

                                            'Case "PHOTO"

                                            '    Dim p As New Panel
                                            '    BuildList(p, rowView("ID"), config.GetParam(colCount, "NAME"), "PHOTO")
                                            '    cntrl = p

                                            'Dim fc As ASP.usercontrols_advancedfileslist_ascx = LoadControl("~/UserControls/advancedFilesList.ascx")
                                            'fc.SavePath = Server.MapPath("") & "\UploadedFiles\"
                                            'fc.GroupId = rowView("ID")
                                            'fc.GroupType = dr("dbfieldname")
                                            'fc.Style = dr("STYLE")
                                            'fc.StorageName = FullTableName.Replace(UserDataStoragePrefix, AttachmentsStoragePrefix)
                                            'If Not ExcelDownloadNow Then
                                            '    fc.ShowMode = "PHOTO"
                                            'Else
                                            '    fc.ShowMode = "FILE"
                                            'End If
                                            'fc.ShowAsList = bReadOnly Or (dr("enabled") = "N")
                                            'fc.BuildFileList(bReadOnly Or (dr("enabled") = "N"))

                                            'AddHandler fc.AddedAttachment, AddressOf AddAttachment
                                            'AddHandler fc.DeletedAttachment, AddressOf DeleteAttachment

                                            'cntrl = fc

                                            'cntrl = Nothing
                                            'c.CssClass = "fileupload"

                                        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)
                                                    ac.CommandArgument = rowView(actionParams(3))
                                                    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"
                                            uc.Text = e.Row.RowIndex
                                            c.Text = (e.Row.RowIndex + 1) + currentPageIndex * GridView1.PageSize
                                            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 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

                                bReadOnly = _bReadOnly
                            End If
                        Catch ex As Exception
                        End Try
                    End If
                    colCount += 1
                End If
                gridColCount += 1
            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 = ""}
                Dim ib2 As New ImageButton With {.CommandName = "SEL", .CommandArgument = e.Row.RowIndex, .ImageUrl = "~/Images/Buttons/yes.png"}
                ib2.ID = ID & "_SelectButton"
                AddHandler ib2.Click, AddressOf SelectRow
                commandCell.Controls.Add(ib2)
                If Not bReadOnly And (accessType.Contains("D") Or accessType.Contains("F")) Then
                    If bCanDeleteThisRow Then
                        Dim ib As New ImageButton With {.CommandName = "DEL", .CommandArgument = e.Row.Cells(0).Text, .ImageUrl = "~/Images/Buttons/delete.png"}
                        ib.ID = ID & "_DeleteButton"
                        AddHandler ib.Click, AddressOf DeleteRowRequest
                        commandCell.Controls.Add(ib)
                    End If
                End If
                e.Row.Cells.Add(commandCell)
            Else
                'If bReadOnly Then
                Dim commandCell As New TableCell With {.Text = ""}
                e.Row.Cells.Add(commandCell)
                'End If
            End If
        End If

        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
        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.Contains("[" & 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.Contains("[" & 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 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 fullUpdate Then
            Try
                _tableId = ColumnsConfiguration.Rows(0)("tableId")
            Catch ex As Exception
            End Try

            FieldList.Clear()
            FieldTypes.Clear()
            FieldFormulas.Clear()
            FieldVisibility.Clear()
            FieldVisibilityByName.Clear()
            FieldEnabled.Clear()
        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 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"))
                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 = ""
        If Not IsNothing(Filters) Then
            If Filters.Count <> 0 Then
                sql &= " WHERE "
                Dim first As Boolean = False
                For Each item As DictionaryEntry In Filters
                    If first Then
                        sql &= " AND "
                    End If
                    'sql &= item.Key & "='" & item.Value & "'"

                    If item.Value = "=N''" Then
                        sql &= "(" & item.Key & item.Value & " or " & item.Key & " is null)"
                    Else
                        sql &= item.Key & item.Value
                    End If
                    first = True
                Next
            End If
        End If
        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 Hashtable, Optional pageIndex As Integer = 1) As Object
        Try
            Dim condition As String = BuildSelectConditionByFilters()
            Dim order As String = ""
            If Not IsNothing(sorting) Then
                If sorting.Count <> 0 Then
                    For Each item As DictionaryEntry In sorting
                        If order <> "" Then
                            order &= ", "
                        Else
                            order &= " ORDER BY "
                        End If
                        'sql &= item.Key & "='" & item.Value & "'"
                        order &= item.Key & " " & item.Value
                        'Message = order
                    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 &= condition

                If sql.Contains("WHERE") Then
                    sql &= " AND "
                Else
                    sql &= " WHERE "
                End If
                sql &= " ID NOT IN (SELECT TOP (" & (pageIndex - 1) * PageSize & ") ID FROM " & FullTableName & " " & condition & " " & order & ")"
                sql &= " " & order
                'Message = sql
            Else
                sql &= condition
                sql &= " " & order
            End If

            'Message = sql
            '-------------------------------------------------

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

                RaiseEvent DataSelected(dt)

                totalDataRecords = sqlf.GetScalar("SELECT COUNT(*) FROM " & FullTableName)
                currentDataRecords = sqlf.GetScalar("SELECT COUNT(*) FROM " & FullTableName & " " & condition)
                totalPages = Math.Ceiling(currentDataRecords / PageSize)
                ' Pager
                'GridView1.PagerSettings.Mode = PagerButtons.NextPreviousFirstLast

                '---------------------------------------
                Try
                    If _tableParams.Rows(0)("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

                'ElseIf DataBindingMode = "datareader" Then
                '    con.Open()
                '    com.CommandText = sqlQueryFinal
                '    Dim dr As SqlDataReader = com.ExecuteReader
                '    Return dr
            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
                If prevOperation = "/" Then
                    preparedFormula &= " cast(replace(" & CheckForFormula(p) & ",',','.') as decimal(18,3)) "
                Else
                    preparedFormula &= " ISNULL(cast(replace(" & CheckForFormula(p) & ",',','.') as decimal(18,3)),0) "
                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.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) = "TODAY" Then
                Return " DATEDIFF(day, convert (datetime, " & params(2) & ",20), getdate())"
            ElseIf params(2) = "TODAY" Then
                Return " DATEDIFF(day, getdate(), convert (datetime, " & params(0) & ",20))"
            Else
                Return " DATEDIFF(day, convert (datetime, " & params(2) & ",20), convert (datetime, " & params(0) & ",20))"
            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.Contains("[" & 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

    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
        RaiseEvent RowCommand(sender, e)
    End Sub

#End Region

#Region "Sorting"

    Public Property SortParameters As Hashtable
        Set(value As Hashtable)
            SaveSortingValues(value)
        End Set

        Get
            Dim ht As Hashtable = ReadSortingValues()
            If ht Is Nothing Then
                ht = New Hashtable
                SaveSortingValues(ht)
            End If
            Return ReadSortingValues()
        End Get
    End Property

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

    Private Function ReadSortingValues() As Hashtable
        Return (TryCast(ViewState("srt_" & UniqueID), Hashtable))
    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 Hashtable
        End If
        Dim _sortDirection As String = ""
        If SortParameters.Contains(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 Hashtable
        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 Sub SaveFilterValues(ht As Hashtable)
        ViewState.Add("filters_" & UniqueID, ht)
    End Sub

    Private Function ReadFilterValues() As Hashtable
        Return (TryCast(ViewState("filters_" & 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" Then
                        If r(0)("search") = "Y" 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

        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" 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
                    currentFilterValue = Filters("[" & dr("dbfieldname") & "]")
                End If

                Select Case dr("fieldtype")
                    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 Else
                        If dr("search").ToString = "Y" Then
                            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)
                        Else
                            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(")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)
                        End If
                End Select

                If (counter Mod 10) = 0 Then
                    tblF = New HtmlTable
                    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

    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 & "'")

        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)
        If txt.Text = "" Then
            ClearFilters(sender.id.split("_")(2).ToString.Replace(UserSeparator, "_"))
        Else
            UpdateFilters(sender.id.split("_")(2).ToString.Replace(UserSeparator, "_"), " like ", "'%" & txt.Text & "%'")
        End If

        RaiseEvent GridFilterChanged()
        lblEditingObject.Value = ""
        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

        RaiseEvent GridFilterChanged()

        RefreshData()
        lblEditingObject.Value = ""
        sender.focus()
    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

    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) As Control
        Dim cntrl As Control 'System.Web.UI.WebControls.WebControl
        Dim defaultValue As String = dr("DEFAULT_VALUE").ToString.Trim

        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")
                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

                'Dim imgaddfile As New Image With {.ImageUrl = "~/Images/Buttons/add.png"}
                'imgaddfile.ID = "btnAddFile_" & (New Random).Next(0, 1000)

                'imgaddfile.CssClass = "fileupload" '"addfile"

                'imgaddfile.Attributes.Add("field", config.GetParam(colcount, "NAME"))
                'imgaddfile.Attributes.Add("rownum", tempId)
                'imgaddfile.Attributes.Add("tid", _tableId)
                'imgaddfile.Attributes.Add("showmode", dr("fieldtype"))

                'cntrl = imgaddfile


                '    Dim fc As ASP.usercontrols_advancedfileslist_ascx = LoadControl("~/UserControls/advancedFilesList.ascx")
                '    fc.SavePath = Server.MapPath("") & "\UploadedFiles\"

                '    Dim genId As Integer
                '    If IsNothing(Session(FullTableName & "_FilesAttached")) Then
                '        genId = (New Random).Next(-100000, 0)
                '        Session.Add(FullTableName & "_FilesAttached", genId)
                '    Else
                '        genId = Session(FullTableName & "_FilesAttached")
                '    End If

                '    fc.GroupId = genId
                '    Session.Add(FullTableName & "_FilesAttached", genId)

                '    fc.GroupType = dr("dbfieldname")
                '    fc.StorageName = FullTableName.Replace(UserDataStoragePrefix, AttachmentsStoragePrefix)
                '    fc.ShowAsList = False
                '    fc.BuildFileList(False)

                '    cntrl = fc
                'Case "PHOTO"
                '    Dim fc As ASP.usercontrols_advancedfileslist_ascx = LoadControl("~/UserControls/advancedFilesList.ascx")
                '    fc.SavePath = Server.MapPath("") & "\UploadedFiles\"

                '    Dim genId As Integer
                '    If IsNothing(Session(FullTableName & "_FilesAttached")) Then
                '        genId = (New Random).Next(-100000, 0)
                '        Session.Add(FullTableName & "_FilesAttached", genId)
                '    Else
                '        genId = Session(FullTableName & "_FilesAttached")
                '    End If

                '    fc.GroupId = genId

                '    fc.GroupType = dr("dbfieldname")
                '    fc.StorageName = FullTableName.Replace(UserDataStoragePrefix, AttachmentsStoragePrefix)
                '    If Not ExcelDownloadNow Then
                '        fc.ShowMode = "PHOTO"
                '    Else
                '        fc.ShowMode = "FILE"
                '    End If
                '    fc.ShowAsList = bReadOnly Or (dr("enabled") = "N")
                '    fc.BuildFileList(bReadOnly Or (dr("enabled") = "N"))
                '    cntrl = fc
            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"
                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
        'cntrl.CssClass = "addFormControl"
        'cntrl.Attributes.Add("fieldname", dr("DBFIELDNAME"))
        Return cntrl
    End Function

    Public Sub AddFormMode()
        Dim gridColCount As Integer = 0
        Dim colCount As Integer = 0
        Dim activeColCount As Integer = 0

        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


        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" 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
                            cntrl = GetAppropriateControl(r, colCount, , tempId)
                            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

    Protected Sub btnRefresh_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnRefresh.Click
        lblEditingObject.Value = ""
        RefreshData()
    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
                    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(")")

        Dim com As New SqlCommand
        com.CommandText = sql.ToString
        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).SelectedValue)
                                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)
                '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)
            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 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

        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

        GridView1.AllowSorting = False
        GridView1.AllowPaging = False

        bReadOnly = True
        ShowCheckBoxAsText = True
        ExcelDownloadNow = True

        '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

        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"))
        Response.End()
    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 FieldVisibility(i) = "Y" Then
                            If first Then
                                sql.Append(",")
                            End If
                            sql.Append("[" & c & "]")
                            first = True
                            'End If
                            i += 1
                        Next
                        sql.Append(") VALUES (")
                        first = False
                        i = 1
                        For Each c In FieldList
                            'If FieldVisibility(i) = "Y" Then
                            If first Then
                                sql.Append(",")
                            End If
                            sql.Append("@" & c)
                            first = True
                            'End If
                            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"
                                                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

                        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
                        sql.Append("@" & c)
                        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

                For Each r In recepients
                    If r <> "" Then
                        Dim UserTableName As String = sqlf.GetScalar("SELECT tablename FROM UserModules.dbo.ModuleTables where id = " & TableId)
                        Dim subject As String = UserTableName & " [by " & UserName & "]"
                        CommonFunctions.SendEmail(r, subject, body)
                    End If
                Next
            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
        txtPagerSIze.Focus()
        lblEditingObject.Value = ""
    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.GetData("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  from [UserModules].[dbo].[ModuleTables] where id = " & TableId)
                If Not IsNothing(_tableParams) Then
                    Dim dr As DataRow = _tableParams.Rows(0)
                    If dr("RESTRICTED_ACCESS") = "Y" Then
                        If accessType.Contains("E") Or accessType.Contains("F") Then
                            IsReadOnly = False
                            pnlGrid.Visible = True
                            pnlNoAccess.Visible = False
                        ElseIf 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
                        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 'CONTROLS' 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], isnull([INSERT_VISIBLE],'Y') as [INSERT_VISIBLE] ")
            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 & "'	) ")
            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 = mappingCondition.Split("=")(0) & " collate Cyrillic_General_CI_AI = " &
                                            mappingCondition.Split("=")(1) & " 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 THIS_TABLE.ID = " & parentid)

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

            Catch ex As Exception

            End Try



            If ShowAsList Then
                If ExcelDownloadNow 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/getfile.ashx?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

                                        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/getfile.ashx?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
                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/getfile.ashx?id=" & dr("id") & "&storage=UserModulesAttachments.[user]." & TableName
                            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/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
                    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/getfile.ashx?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/add.png"}
                imgaddfile.ID = "btnAddFile_" & field & "_" & parentid

                imgaddfile.CssClass = "fileupload" '"addfile"

                imgaddfile.Attributes.Add("field", field)
                imgaddfile.Attributes.Add("rownum", parentid)
                imgaddfile.Attributes.Add("tid", _tableId)
                imgaddfile.Attributes.Add("showmode", showMode)

                p.Controls.Add(imgaddfile)
            End If
        Catch ex As Exception

        End Try
    End Sub



#End Region

End Class