66.142.0.142/66.142.0.142.tar/66.142.0.142/tblDepartmentslist.aspx.vb

Text File | 2010-08-24 | 19KB | 707 lines

Imports System.Data
Imports System.Data.Common
Imports System.Xml
Imports System.IO
Imports System.Data.OleDb

'
' ASP.NET code-behind class (Page)
'

Partial Class tblDepartmentslist
    Inherits AspNetMaker7_tfpssnet

    ' Page object
    Public tblDepartments_list As ctblDepartments_list

    '
    ' Page Class
    '
    Class ctblDepartments_list
        Inherits AspNetMakerPage
        Implements IDisposable

        ' Used by system generated functions
        Private RsWrk As Object, sSqlWrk As String, sWhereWrk As String

        Private arwrk As Object

        Private armultiwrk() As String

        ' Page URL
        Public ReadOnly Property PageUrl() As String
            Get
                Dim Url As String = ew_CurrentPage() & "?"
                If tblDepartments.UseTokenInUrl Then Url = Url & "t=" & tblDepartments.TableVar & "&" ' Add page token
                Return Url
            End Get
        End Property

        ' Validate page request
        Public Function IsPageRequest() As Boolean
            Dim Result As Boolean
            If tblDepartments.UseTokenInUrl Then
                Result = False
                If ObjForm IsNot Nothing Then
                    Result = (tblDepartments.TableVar = ObjForm.GetValue("t"))
                End If
                If ew_Get("t") <> "" Then
                    Result = (tblDepartments.TableVar = ew_Get("t"))
                End If
                Return Result
            End If
            Return True
        End Function

        ' tblDepartments
        Public Property tblDepartments() As ctblDepartments
            Get
                Return ParentPage.tblDepartments
            End Get
            Set(ByVal v As ctblDepartments)
                ParentPage.tblDepartments = v
            End Set
        End Property

        ' tblDepartments
        Public Property tblEmployees() As ctblEmployees
            Get
                Return ParentPage.tblEmployees
            End Get
            Set(ByVal v As ctblEmployees)
                ParentPage.tblEmployees = v
            End Set
        End Property

        '
        '  Constructor
        '  - init objects
        '  - open connection
        '
        Public Sub New(ByRef APage As AspNetMaker7_tfpssnet)
            m_ParentPage = APage
            m_Page = Me
            m_PageID = "list"
            m_PageObjName = "tblDepartments_list"
            m_PageObjTypeName = "ctblDepartments_list"

            ' Table Name
            m_TableName = "tblDepartments"

            ' Initialize table object
            tblDepartments = New ctblDepartments(Me)
            tblEmployees = New ctblEmployees(Me)

            ' Connect to database
            Conn = New cConnection()

            ' Initialize list options
            ListOptions = New cListOptions
        End Sub

        '
        '  Subroutine Page_Init
        '  - called before page main
        '  - check Security
        '  - set up response header
        '  - call page load events
        '
        Public Sub Page_Init()
            Security = New cAdvancedSecurity(Me)
            If Not Security.IsLoggedIn() Then Security.AutoLogin()
            If Not Security.IsLoggedIn() Then
                Security.SaveLastUrl()
                Page_Terminate("login.aspx")
            End If

            ' Table Permission loading event
            Security.TablePermission_Loading()
            Security.LoadCurrentUserLevel(TableName)

            ' Table Permission loaded event
            Security.TablePermission_Loaded()
            If Not Security.CanList Then
                Security.SaveLastUrl()
                Page_Terminate("login.aspx")
            End If

            ' User ID loading event
            Security.UserID_Loading()
            If Security.IsLoggedIn() Then Call Security.LoadUserID()

            ' User ID loaded event
            Security.UserID_Loaded()
            tblDepartments.Export = ew_Get("export") ' Get export parameter
            ParentPage.gsExport = tblDepartments.Export ' Get export parameter, used in header
            ParentPage.gsExportFile = tblDepartments.TableVar ' Get export file, used in header

            ' Global page loading event (in ewglobal*.vb)
            ParentPage.Page_Loading()

            ' Page load event, used in current page
            Page_Load()
        End Sub

        '
        '  Class terminate
        '  - clean up page object
        '
        Public Sub Dispose() Implements IDisposable.Dispose
            Page_Terminate("")
        End Sub

        '
        '  Sub Page_Terminate
        '  - called when exit page
        '  - clean up connection and objects
        '  - if URL specified, redirect to URL
        '
        Sub Page_Terminate(url As String)

            ' Page unload event, used in current page
            Page_Unload()

            ' Global page unloaded event (in ewglobal*.vb)
            ParentPage.Page_Unloaded()

            ' Close connection
            Conn.Dispose()
            Security = Nothing
            tblDepartments.Dispose()
            tblEmployees.Dispose()
            ListOptions = Nothing

            ' Go to URL if specified
            If url <> "" Then
                HttpContext.Current.Response.Clear()
                HttpContext.Current.Response.Redirect(url)
            End If
        End Sub

    Public lDisplayRecs As Integer ' Number of display records

    Public lStartRec As Integer, lStopRec As Integer, lTotalRecs As Integer, lRecRange As Integer

    Public sSrchWhere As String

    Public lRecCnt As Integer

    Public lEditRowCnt As Integer

    Public lRowCnt As Integer, lRowIndex As Integer

    Public lOptionCnt As Integer

    Public lRecPerRow As Integer, lColCnt As Integer

    Public sDeleteConfirmMsg As String ' Delete confirm message

    Public sDbMasterFilter As String, sDbDetailFilter As String

    Public bMasterRecordExists As Boolean

    Public ListOptions As Object

    Public sMultiSelectKey As String

    Public x_ewPriv As Integer

    '
    ' Page main processing
    '
    Public Sub Page_Main()
        lDisplayRecs = 20
        lRecRange = EW_PAGER_RANGE
        lRecCnt = 0 ' Record count

        ' Search filters
        Dim sSrchAdvanced As String = "" ' Advanced search filter
        Dim sSrchBasic As String = "" ' Basic search filter
        Dim sFilter As String = ""
        sSrchWhere = "" ' Search WHERE clause

        ' Master/Detail
        sDbMasterFilter = "" ' Master filter
        sDbDetailFilter = "" ' Detail filter
        If IsPageRequest Then ' Validate request

            ' Set up records per page dynamically
            SetUpDisplayRecs()

            ' Handle reset command
            ResetCmd()

            ' Get basic search criteria
            If ParentPage.gsSearchError = "" Then
                sSrchBasic = BasicSearchWhere()
            End If

            ' Set Up Sorting Order
            SetUpSortOrder()
        End If

        ' Restore display records
        If (tblDepartments.RecordsPerPage = -1 OrElse tblDepartments.RecordsPerPage > 0) Then
            lDisplayRecs = tblDepartments.RecordsPerPage ' Restore from Session
        Else
            lDisplayRecs = 20 ' Load default
        End If

        ' Load Sorting Order
        LoadSortOrder()

        ' Build search criteria
        If sSrchAdvanced <> "" Then
            If sSrchWhere <> "" Then
                sSrchWhere = "(" & sSrchWhere & ") AND (" & sSrchAdvanced & ")"
            Else
                sSrchWhere = sSrchAdvanced
            End If
        End If
        If sSrchBasic <> "" Then
            If sSrchWhere <> "" Then
                sSrchWhere = "(" & sSrchWhere & ") AND (" & sSrchBasic & ")"
            Else
                sSrchWhere = sSrchBasic
            End If
        End If

        ' Recordset Searching event
        tblDepartments.Recordset_Searching(sSrchWhere)

        ' Save search criteria
        If sSrchWhere <> "" Then
            If sSrchBasic = "" Then ResetBasicSearchParms()
            tblDepartments.SearchWhere = sSrchWhere ' Save to Session
            lStartRec = 1 ' Reset start record counter
            tblDepartments.StartRecordNumber = lStartRec
        Else
            RestoreSearchParms()
        End If

        ' Build filter
            sFilter = "[depIsActive]=True"
        If Not Security.CanList Then
            sFilter = "(0=1)" ' Filter all records
        End If
        If sDbDetailFilter <> "" Then
            If sFilter <> "" Then
                sFilter = "(" & sFilter & ") AND (" & sDbDetailFilter & ")"
            Else
                sFilter = sDbDetailFilter
            End If
        End If
        If sSrchWhere <> "" Then
            If sFilter <> "" Then
                sFilter = "(" & sFilter & ") AND (" & sSrchWhere & ")"
            Else
                sFilter = sSrchWhere
            End If
        End If

        ' Set up filter in Session
        tblDepartments.SessionWhere = sFilter
        tblDepartments.CurrentFilter = ""
    End Sub

    '
    ' Set up number of records displayed per page
    '
    Sub SetUpDisplayRecs()
        Dim sWrk As String
        sWrk = ew_Get(EW_TABLE_REC_PER_PAGE)
        If sWrk <> "" Then
            If IsNumeric(sWrk) Then
                lDisplayRecs = ew_ConvertToInt(sWrk)
            Else
                If ew_SameText(sWrk, "all") Then ' Display all records
                    lDisplayRecs = -1
                Else
                    lDisplayRecs = 20 ' Non-numeric, load default
                End If
            End If
            tblDepartments.RecordsPerPage = lDisplayRecs ' Save to Session

            ' Reset start position
            lStartRec = 1
            tblDepartments.StartRecordNumber = lStartRec
        End If
    End Sub

    '
    ' Return Basic Search SQL
    '
    Function BasicSearchSQL(Keyword As String) As String
        Dim sKeyword As String, sSql As String = ""
        sKeyword = ew_AdjustSql(Keyword)
        sSql = sSql & "[depDescription] LIKE '%" & sKeyword & "%' OR "
        sSql = sSql & "[depAcctCode] LIKE '%" & sKeyword & "%' OR "
        sSql = sSql & "[depNotes] LIKE '%" & sKeyword & "%' OR "
        If sSql.EndsWith(" OR ") Then sSql = sSql.SubString(0, sSql.Length-4)
        Return sSql
    End Function

    '
    ' Return Basic Search WHERE based on search keyword and type
    '
    Function BasicSearchWhere() As String
        Dim sSearchStr As String = "", sSearchKeyword As String, sSearchType As String
        Dim sSearch As String, arKeyword() As String, sKeyword As String
        If Not Security.CanSearch Then Return ""
        sSearchKeyword = ew_Get(EW_TABLE_BASIC_SEARCH)
        sSearchType = ew_Get(EW_TABLE_BASIC_SEARCH_TYPE)
        If sSearchKeyword <> "" Then
            sSearch = sSearchKeyword.Trim()
            If sSearchType <> "" Then
                While InStr(sSearch, "  ") > 0
                    sSearch = sSearch.Replace("  ", " ")
                End While
                arKeyword = sSearch.Trim().Split(New Char() {" "c})
                For Each sKeyword In arKeyword
                    If sSearchStr <> "" Then sSearchStr = sSearchStr & " " & sSearchType & " "
                    sSearchStr = sSearchStr & "(" & BasicSearchSQL(sKeyword) & ")"
                Next
            Else
                sSearchStr = BasicSearchSQL(sSearch)
            End If
        End If
        If sSearchKeyword <> "" then
            tblDepartments.BasicSearchKeyword = sSearchKeyword
            tblDepartments.BasicSearchType = sSearchType
        End If
        Return sSearchStr
    End Function

    '
    ' Clear all search parameters
    '
    Sub ResetSearchParms()

        ' Clear search where
        sSrchWhere = ""
        tblDepartments.SearchWhere = sSrchWhere

        ' Clear basic search parameters
        ResetBasicSearchParms()
    End Sub

    '
    ' Clear all basic search parameters
    '
    Sub ResetBasicSearchParms()

        ' Clear basic search parameters
        tblDepartments.BasicSearchKeyword = ""
        tblDepartments.BasicSearchType = ""
    End Sub

    '
    ' Restore all search parameters
    '
    Sub RestoreSearchParms()
        sSrchWhere = tblDepartments.SearchWhere
    End Sub

    '
    ' Set up Sort parameters based on Sort Links clicked
    '
    Sub SetUpSortOrder()
        Dim sOrderBy As String
        Dim sSortField As String, sLastSort As String, sThisSort As String
        Dim bCtrl As Boolean

        ' Check for Ctrl pressed
        bCtrl = (ew_Get("ctrl") <> "")

        ' Check for an Order parameter
        If ew_Get("order") <> "" Then
            tblDepartments.CurrentOrder = ew_Get("order")
            tblDepartments.CurrentOrderType = ew_Get("ordertype")
            tblDepartments.UpdateSort(tblDepartments.depDescription, bCtrl) ' depDescription
            tblDepartments.UpdateSort(tblDepartments.depAcctCode, bCtrl) ' depAcctCode
            tblDepartments.StartRecordNumber = 1 ' Reset start position
        End If
    End Sub

    '
    ' Load Sort Order parameters
    '
    Sub LoadSortOrder()
        Dim sOrderBy As String
        sOrderBy = tblDepartments.SessionOrderBy ' Get order by from Session
        If sOrderBy = "" Then
            If tblDepartments.SqlOrderBy <> "" Then
                sOrderBy = tblDepartments.SqlOrderBy
                tblDepartments.SessionOrderBy = sOrderBy
            End If
        End If
    End Sub

    '
    ' Reset command based on querystring parameter "cmd"
    ' - reset: reset search parameters
    ' - resetall: reset search and master/detail parameters
    ' - resetsort: reset sort parameters
    '
    Sub ResetCmd()
        Dim sCmd As String

        ' Get reset cmd
        If ew_Get("cmd") <> "" Then
            sCmd = ew_Get("cmd")

            ' Reset search criteria
            If ew_SameText(sCmd, "reset") OrElse ew_SameText(sCmd, "resetall") Then
                ResetSearchParms()
            End If

            ' Reset sort criteria
            If ew_SameText(sCmd, "resetsort") Then
                Dim sOrderBy As String = ""
                tblDepartments.SessionOrderBy = sOrderBy
                tblDepartments.depDescription.Sort = ""
                tblDepartments.depAcctCode.Sort = ""
            End If

            ' Reset start position
            lStartRec = 1
            tblDepartments.StartRecordNumber = lStartRec
        End If
    End Sub

    Public Pager As Object

    '
    ' Set up Starting Record parameters
    '
    Sub SetUpStartRec()
        Dim nPageNo As Integer

        ' Exit if lDisplayRecs = 0
        If lDisplayRecs = 0 Then Exit Sub
        If IsPageRequest Then ' Validate request

            ' Check for a "start" parameter
            If ew_Get(EW_TABLE_START_REC) <> "" AndAlso IsNumeric(ew_Get(EW_TABLE_START_REC)) Then
                lStartRec = ew_ConvertToInt(ew_Get(EW_TABLE_START_REC))
                tblDepartments.StartRecordNumber = lStartRec
            ElseIf ew_Get(EW_TABLE_PAGE_NO) <> "" AndAlso IsNumeric(ew_Get(EW_TABLE_PAGE_NO)) Then
                nPageNo = ew_ConvertToInt(ew_Get(EW_TABLE_PAGE_NO))
                lStartRec = (nPageNo-1)*lDisplayRecs+1
                If lStartRec <= 0 Then
                    lStartRec = 1
                ElseIf lStartRec >= ((lTotalRecs-1)\lDisplayRecs)*lDisplayRecs+1 Then
                    lStartRec = ((lTotalRecs-1)\lDisplayRecs)*lDisplayRecs+1
                End If
                tblDepartments.StartRecordNumber = lStartRec
            End If
        End If
        lStartRec = tblDepartments.StartRecordNumber

        ' Check if correct start record counter
        If lStartRec <= 0 Then ' Avoid invalid start record counter
            lStartRec = 1 ' Reset start record counter
            tblDepartments.StartRecordNumber = lStartRec
        ElseIf lStartRec > lTotalRecs Then ' Avoid starting record > total records
            lStartRec = ((lTotalRecs-1)\lDisplayRecs)*lDisplayRecs+1 ' Point to last page first record
            tblDepartments.StartRecordNumber = lStartRec
        ElseIf (lStartRec-1) Mod lDisplayRecs <> 0 Then
            lStartRec = ((lStartRec-1)\lDisplayRecs)*lDisplayRecs+1 ' Point to page boundary
            tblDepartments.StartRecordNumber = lStartRec
        End If
    End Sub

    '
    ' Load default values
    '
    Sub LoadDefaultValues()
    End Sub

    '
    ' Load recordset
    '
    Function LoadRecordset() As OleDbDataReader

        ' Recordset Selecting event
        tblDepartments.Recordset_Selecting(tblDepartments.CurrentFilter)

        ' Load list page SQL
        Dim sSql As String = tblDepartments.ListSQL

        ' Write SQL for debug
        If EW_DEBUG_ENABLED Then ew_Write(sSql)

        ' Count
        lTotalRecs = -1
        Try
            If sSql.StartsWith("SELECT * FROM ", StringComparison.InvariantCultureIgnoreCase) AndAlso _
                ew_Empty(tblDepartments.SqlGroupBy) AndAlso _
                ew_Empty(tblDepartments.SqlHaving) Then
                Dim sCntSql As String = tblDepartments.SelectCountSQL

                ' Write SQL for debug
                If EW_DEBUG_ENABLED Then ew_Write("<br>" & sCntSql)
                lTotalRecs = Conn.ExecuteScalar(sCntSql)
            End If
        Catch
        End Try

        ' Load recordset
        Dim Rs As OleDbDataReader = Conn.GetDataReader(sSql)
        If lTotalRecs < 0 AndAlso Rs.HasRows Then
            lTotalRecs = 0
            While Rs.Read()
                lTotalRecs = lTotalRecs + 1
            End While
            Rs.Close()
            Rs = Conn.GetDataReader(sSql)
        End If

        ' Recordset Selected event
        tblDepartments.Recordset_Selected(Rs)
        Return Rs
    End Function

    '
    ' Load row based on key values
    '
    Function LoadRow() As Boolean
        Dim RsRow As OleDbDataReader
        Dim sFilter As String = tblDepartments.KeyFilter

        ' Row Selecting event
        tblDepartments.Row_Selecting(sFilter)

        ' Load SQL based on filter
        tblDepartments.CurrentFilter = sFilter
        Dim sSql As String = tblDepartments.SQL

        ' Write SQL for debug
        If EW_DEBUG_ENABLED Then ew_Write(sSql)
        Try
            RsRow = Conn.GetTempDataReader(sSql)
            If Not RsRow.Read() Then
                Return False
            Else
                LoadRowValues(RsRow) ' Load row values

                ' Row Selected event
                tblDepartments.Row_Selected(RsRow)
                Return True
            End If
        Catch
            If EW_DEBUG_ENABLED Then Throw
            Return False
        Finally
            Conn.CloseTempDataReader()
        End Try
    End Function

    '
    ' Load row values from recordset
    '
    Sub LoadRowValues(ByRef RsRow As OleDbDataReader)
        tblDepartments.depID.DbValue = RsRow("depID")
        tblDepartments.depDescription.DbValue = RsRow("depDescription")
        tblDepartments.depAcctCode.DbValue = RsRow("depAcctCode")
        tblDepartments.depNotes.DbValue = RsRow("depNotes")
    End Sub

    '
    ' Render row values based on field settings
    '
    Sub RenderRow()

        ' Row Rendering event
        tblDepartments.Row_Rendering()

        '
        '  Common render codes for all row types
        '
        ' depDescription

        tblDepartments.depDescription.CellCssStyle = ""
        tblDepartments.depDescription.CellCssClass = ""

        ' depAcctCode
        tblDepartments.depAcctCode.CellCssStyle = ""
        tblDepartments.depAcctCode.CellCssClass = ""

        '
        '  View  Row
        '

        If tblDepartments.RowType = EW_ROWTYPE_VIEW Then ' View row

            ' depDescription
            tblDepartments.depDescription.ViewValue = tblDepartments.depDescription.CurrentValue
            tblDepartments.depDescription.CssStyle = ""
            tblDepartments.depDescription.CssClass = ""
            tblDepartments.depDescription.ViewCustomAttributes = ""

            ' depAcctCode
            tblDepartments.depAcctCode.ViewValue = tblDepartments.depAcctCode.CurrentValue
            tblDepartments.depAcctCode.CssStyle = ""
            tblDepartments.depAcctCode.CssClass = ""
            tblDepartments.depAcctCode.ViewCustomAttributes = ""

            ' View refer script
            ' depDescription

            tblDepartments.depDescription.HrefValue = ""

            ' depAcctCode
            tblDepartments.depAcctCode.HrefValue = ""
        End If

        ' Row Rendered event
        tblDepartments.Row_Rendered()
    End Sub

        ' Page Load event
        Public Sub Page_Load()

            'HttpContext.Current.Response.Write("Page Load")
        End Sub

        ' Page Unload event
        Public Sub Page_Unload()

            'HttpContext.Current.Response.Write("Page Unload")
        End Sub

    ' Form Custom Validate event
    Public Function Form_CustomValidate(ByRef CustomError As String) As Boolean

        'Return error message in CustomError
        Return True
    End Function
    End Class

    '
    ' ASP.NET Page_Load event
    '

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Response.Buffer = EW_RESPONSE_BUFFER
        Response.Cache.SetCacheability(HttpCacheability.NoCache)

        ' Page init
        tblDepartments_list = New ctblDepartments_list(Me)
        tblDepartments_list.Page_Init()

        ' Page main processing
        tblDepartments_list.Page_Main()
    End Sub

    '
    ' ASP.NET Page_Unload event
    '

    Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload

        ' Dispose page object
        If tblDepartments_list IsNot Nothing Then tblDepartments_list.Dispose()
    End Sub
End Class