ASP.NET

<asp:ObjectDataSource ID="SearchResultsObjectDataSource" runat="server" 
        SelectMethod="getRequests" TypeName="ATVS.Initiation.BLL.SortSearchManager" 
        SortParameterName="SortExpression">
        <SelectParameters>
            <asp:SessionParameter Name="searchCriteria" SessionField="SearchCriteria" 
                Type="Object" />
            <asp:SessionParameter DefaultValue="" Name="SearchType" SessionField="TYPE" 
                Type="String" />
            <asp:SessionParameter Name="UserGroup" SessionField="USERGROUP" Type="Int32" />
            <asp:SessionParameter Name="SortExpression" SessionField="SortExpression" 
                Type="String" />
            <asp:SessionParameter Name="SortDirection" SessionField="SortDirestion" 
                Type="Int32" />
        </SelectParameters>
    </asp:ObjectDataSource>

<asp:GridView ID="resultsGrid" runat="server" AllowPaging="True" 
        AutoGenerateColumns="False" 
        DataSourceID="SearchResultsObjectDataSource" CssClass="grid" 
        AllowSorting="True">
        <Columns>
            <asp:HyperLinkField DataNavigateUrlFields="ARID" DataNavigateUrlFormatString="/LastResponse.aspx?ARID={0}"
                            HeaderText="ARID" Target="_blank" DataTextField="ARID" 
                SortExpression="ARID">
                <HeaderStyle CssClass="groupHeading" />
            </asp:HyperLinkField>
            <asp:BoundField DataField="InitiatorName" HeaderText="Initiator Name" 
                SortExpression="INITIATOR_LOGIN" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="LloydeNo" HeaderText="Lloyde #" 
                SortExpression="LLOYDS_NBR" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="TankerName" HeaderText="Tanker Name" 
                SortExpression="CURRENT_NAME" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="Port" HeaderText="Port" SortExpression="PORT_NAME" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="InitiationDate" HeaderText="Initiation Date" 
                SortExpression="created_date" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="Status" HeaderText="Status" 
                SortExpression="REQUEST_STATUS_NAME" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="EvaluatorName" HeaderText="Evaluator Name" 
                SortExpression="EVALUATOR" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="DaysLeft" HeaderText="Days Left" 
                SortExpression="EXPIRATION_DATE" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:BoundField DataField="Expirydate" HeaderText="Expiry Date" 
                SortExpression="EXPIRATION_DATE" >
                <HeaderStyle CssClass="groupHeading" />
            </asp:BoundField>
            <asp:ButtonField ButtonType="Button" CommandName="Select_Override" 
                Text="Override" >
                <ControlStyle CssClass="button" />
            </asp:ButtonField>
            <asp:ButtonField ButtonType="Button" CommandName="Select_Followup" 
                Text="Follow Up" >
                <ControlStyle CssClass="button" />
            </asp:ButtonField>
            <asp:ButtonField ButtonType="Button" CommandName="Cancel" Text="Cancel" >
                <ControlStyle CssClass="button" />
            </asp:ButtonField>
            <asp:BoundField DataField="InitiatorGroup" 
                SortExpression="InitiatorGroup" />
            <asp:BoundField DataField="StatusID" SortExpression="StatusID" />
            <asp:BoundField DataField="IsInitiatorReply" 
                SortExpression="IsInitiatorReply" />
        </Columns>
        <FooterStyle HorizontalAlign="Center" />
        <HeaderStyle CssClass="groupHeading" />
    </asp:GridView>

Protected Sub resultsGrid_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles resultsGrid.Sorting
    Session("SortDirestion") = CInt(e.SortDirection)
    Session("SortExpression") = e.SortExpression.ToString()
    resultsGrid.DataBind()
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsNothing(Session("USERGROUP")) Then
            Response.Redirect("Default.aspx")
        End If
        Session("TYPE") = Request.QueryString("TYPE")

        resultsGrid.Visible = False
        If Session("TYPE") = "" Then
            Session("TYPE") = "ALL"
        End If
        Dim type As String = Session("TYPE")
        If type.Equals("FOLLOWUP") Or type.Equals("OVERRIDE") Then
            statusDropDownList.Visible = False
            StatusLabel.Visible = False
        End If
        If Not IsPostBack Then
            Session("SortDirestion") = 0
            Session("SortExpression") = "ARID"
        End If
    End Sub

Protected Sub resultsGrid_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles resultsGrid.PageIndexChanging
        resultsGrid.PageIndex = e.NewPageIndex
        'resultsGrid.DataBind()
        startSearch()
    End Sub

Private Sub resultsGrid_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles resultsGrid.PreRender
        If resultsGrid.Rows.Count = 0 Then
            ErrLabel.Visible = True
            ErrLabel.Text = "There is no result matching your search criteria"
        Else
            ErrLabel.Visible = False
        End If
        'If Results.Count > 0 Then
        populateResultGrid(Session("USERGROUP"))
        'End If
    End Sub

Protected Sub resultsGrid_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles resultsGrid.RowCommand
        Dim x As Integer = 0
        If e.CommandName.Equals("Select_Override") Then
            x = CInt(e.CommandArgument)
            Dim HyperLinkFromGrid As New HyperLink
            HyperLinkFromGrid = resultsGrid.Rows(x).Cells(0).Controls(0)
            Dim ARID As String = HyperLinkFromGrid.Text
            Session("ARID") = ARID
            Session("PORT") = resultsGrid.Rows(x).Cells.Item(4).Text
            Session("OverrideDataTableCreationFlag") = False
            Session("CurrentReqStatusID") = CInt(resultsGrid.Rows(x).Cells.Item(resultsGrid.Columns.Count - 2).Text)
            'Destroy all Sessions
            Session("TYPE") = Nothing
            Session("SearchCriteria") = Nothing
            Response.Redirect("Override_Request.aspx")
        ElseIf e.CommandName.Equals("Select_Followup") Then
            x = CInt(e.CommandArgument)
            Dim HyperLinkFromGrid As New HyperLink
            HyperLinkFromGrid = resultsGrid.Rows(x).Cells(0).Controls(0)
            Dim ARID As String = HyperLinkFromGrid.Text
            Session("ARID") = ARID
            Session("PORT") = resultsGrid.Rows(x).Cells.Item(4).Text
            Dim status As Integer = CInt(resultsGrid.Rows(x).Cells.Item(14).Text)
            'Destroy all Sessions
            Session("TYPE") = Nothing
            Session("SearchCriteria") = Nothing
            If status = 3 Then
                Response.Redirect("Followup_Request.aspx")
            ElseIf status = 9 Then
                Response.Redirect("OverrideFollowupRequest.aspx")
            End If
        ElseIf e.CommandName.Equals("Cancel") Then
            x = CInt(e.CommandArgument)
            Dim HyperLinkFromGrid As New HyperLink
            HyperLinkFromGrid = resultsGrid.Rows(x).Cells(0).Controls(0)
            Dim ARID As String = HyperLinkFromGrid.Text
            Session("ARID") = ARID
            'Destroy all Sessions
            Session("TYPE") = Nothing
            Session("SearchCriteria") = Nothing
            Response.Redirect("CancelRequest.aspx")
        End If
    End Sub

Imports System.Data.OracleClient
Imports ATVS.Initiation.Entities
Imports Microsoft.ApplicationBlocks.Data
Imports Microsoft.ApplicationBlocks.ExceptionManagement

Public Class SortSearchDA
    Public Function SearchRequests(ByVal searchCriteria As SearchCriteria, ByVal SortExpression As String, ByVal SortDirection As Integer) As List(Of SearchResults)
        Dim searchs As New List(Of SearchResults)
        Try
            Dim SQLStr As String = ""
            SQLStr = PrepareSelectStatement(SQLStr, searchCriteria)
            If Not SortExpression.Equals(String.Empty) Then
                SQLStr += " ORDER BY " + SortExpression + " "
                If SortDirection = 0 Then
                    SQLStr += "ASC"
                Else
                    SQLStr += "DESC"
                End If
            End If
            Dim dsResult As New System.Data.DataSet
            dsResult = OracleHelper.ExecuteDataset(StoredProcedure.strConnection, CommandType.Text, SQLStr)
            Dim tbl As DataTable = dsResult.Tables(0)
            For Each row As DataRow In tbl.Rows
                Dim search As New SearchResults
                Dim dsGroup As New DataSet
                search = prepareSearchEntity(row)
                Dim SQLStr2 As String = "SELECT DISTINCT RE.ROLE_ID FROM T_RTT_TVS_ROLE_EMPLOYEE RE WHERE RE.VTMS_ROLE_OR_LOGIN='" + search.InitiatorName.ToUpper() + "'"
                dsGroup = OracleHelper.ExecuteDataset(StoredProcedure.strConnection, CommandType.Text, SQLStr2)
                Dim table As New DataTable
                table = dsGroup.Tables(0)
                If dsGroup.Tables(0).Rows.Count > 0 Then
                    search.InitiatorGroup = CInt(dsGroup.Tables(0).Rows(0).Item(0).ToString())
                End If
                searchs.Add(search)
            Next
        Catch ex As ApplicationException

        End Try
        Return searchs
    End Function

Private Function PrepareSelectStatement(ByVal SQLStr As String, ByVal searchCriteria As SearchCriteria) As String
        SQLStr = "SELECT DISTINCT ar.ARID,ar.INITIATOR_LOGIN,ar.LLOYDS_NBR,TAN.DEADWT_TONNAGE,TAN.CURRENT_NAME,ar.SUBMITTEDDATE created_date,ar.REQUEST_STATUS_ID,"
        SQLStr += "ars.REQUEST_STATUS_NAME,ar.EXPIRATION_DATE, ar.EVALUATOR,"
        SQLStr += "( (CASE WHEN (V.TVS_ACC_ID > 0 OR AR.EXPIRATION_DATE < SYSDATE) THEN 'EXPIRED'"
        SQLStr += "ELSE DECODE(AR.REQUEST_STATUS_ID, 4,'VALID') END)) AS STATUS,"
        SQLStr += "( (CASE WHEN (V.TVS_ACC_ID > 0 OR AR.EXPIRATION_DATE < SYSDATE) THEN ''"
        SQLStr += "ELSE DECODE(AR.REQUEST_STATUS_ID, 3,'Follow up') END)) AS DISPLAY_TEXT,"
        SQLStr += "planned.SAUDI_PORT_CODE,port.PORT_NAME,ars.REQUEST_STATUS_NAME AS STATUS_NAME, AR.YN_INITIATOR_REPLY"
        SQLStr += " FROM   RTTDBA.T_RTT_ACCEPTANCE_REQUEST ar,RTTDBA.T_RTT_ACCEPTANC_REQUEST_STATUS ars,RTTDBA.T_RTT_TANKER TAN,"
        SQLStr += "RTTDBA.T_RTT_VISIT V,RTTDBA.T_RTT_PLANNED_VISIT planned, RTTDBA.T_RTT_SAUDI_PORT port"
        SQLStr += " WHERE  ar.REQUEST_STATUS_ID = ars.REQUEST_STATUS_ID AND"
        SQLStr += " ar.LLOYDS_NBR = TAN.LLOYDS_NBR AND"
        SQLStr += " V.TVS_ACC_ID(+) = AR.ARID AND"
        SQLStr += " planned.SAUDI_PORT_CODE = port.SAUDI_PORT_CODE AND"
        SQLStr += " ar.ARID ="
        If searchCriteria.ARID = 0 Then
            SQLStr += "ar.ARID"
        Else
            SQLStr += searchCriteria.ARID.ToString()
        End If
        SQLStr += " AND planned.ARID = ar.ARID"
        SQLStr += " AND ar.LLOYDS_NBR ="
        If searchCriteria.LloydsNo = 0 Then
            SQLStr += "ar.LLOYDS_NBR "
        Else
            SQLStr += searchCriteria.LloydsNo.ToString()
        End If
        SQLStr += " AND ar.MODIFIED_DATE >="
        If searchCriteria.InitiationDateFrom.Equals(CDate("1/1/1900")) Then
            SQLStr += "ar.MODIFIED_DATE"
        Else
            SQLStr += searchCriteria.InitiationDateFrom.ToString()
        End If
        SQLStr += " AND ar.MODIFIED_DATE <="
        If searchCriteria.InitiationDateTo.Equals(CDate("1/1/1900")) Then
            SQLStr += "ar.MODIFIED_DATE"
        Else
            SQLStr += searchCriteria.InitiationDateTo.ToString()
        End If
        SQLStr += " AND lower(ar.INITIATOR_LOGIN) LIKE '%'||"
        If searchCriteria.InitiatorName.Equals(String.Empty) Then
            SQLStr += "lower(ar.INITIATOR_LOGIN)||'%'"
        Else
            SQLStr += "lower(" + searchCriteria.InitiatorName + ")||'%'"
        End If

        SQLStr += " AND ars.REQUEST_STATUS_ID ="
        If searchCriteria.Status = 0 Then
            SQLStr += "ars.REQUEST_STATUS_ID"
        Else
            SQLStr += searchCriteria.Status
        End If
        SQLStr += " AND lower(TAN.CURRENT_NAME) LIKE '%'||"
        If searchCriteria.TankerName.Equals(String.Empty) Then
            SQLStr += "lower(TAN.CURRENT_NAME)||'%'"
        Else
            SQLStr += "lower(" + searchCriteria.TankerName + ")||'%'"
        End If
        Return SQLStr
    End Function

    Public Function prepareNewRequestEntity(ByVal row As DataRow) As NewRequest
        Dim search As New NewRequest
        If Not IsDBNull(row) Then
            search.ARID = row("ARID")

            If row("EXPIRATION_DATE").Equals(DBNull.Value) Then
                'do nothing
            Else
                search.ExpiryDate = row("EXPIRATION_DATE")
            End If

            search.Tanker.LloydNum = row("LLOYDS_NBR")
            search.PortNCargo.SaudiPortCodeName = row("PORT_NAME")
            search.Status = row("REQUEST_STATUS_NAME")
            search.Tanker.TankerName = row("CURRENT_NAME")
        End If
        Return search
    End Function
End Class

Imports ATVS.Common
Imports ATVS.Initiation.Entities
Imports ATVS.Initiation.DAL
Imports ATVS.Initiation.DAL.TnakerInfoDA
Public Class SortSearchManager
    Public Function getRequests(ByVal searchCriteria As SearchCriteria, ByVal SearchType As String, ByVal UserGroup As Integer, ByVal SortExpression As String, ByVal SortDirection As Integer) As List(Of SearchResults)
        Dim results As New List(Of SearchResults)
        Dim Search As New SortSearchDA
        If SearchType = "" Then
            Return results
        End If
        results = Search.SearchRequests(searchCriteria, SortExpression, SortDirection)
        'Apply filtering if user group is basic
        If UserGroup = UserRole.INITIATOR_BASIC Then
            results = FilterSearch(results, UserGroup)
        End If
        'Apply Filtering if the search is by override or followup
        If SearchType.Equals("OVERRIDE") Or SearchType.Equals("FOLLOWUP") Then
            results = FilterSearch(results, SearchType, searchCriteria)
        End If

        'results(0).DaysLeft = results(0).Expirydate.Day - results(0).InitiationDate.Day
        Return results
    End Function
    Private Function FilterSearch(ByVal results As List(Of SearchResults), ByVal SearchType As String, ByVal searchCriteria As SearchCriteria) As List(Of SearchResults)
        Dim TempResults As New List(Of SearchResults)
        If SearchType.Equals("OVERRIDE") Then
            For Each result As SearchResults In results
                If result.StatusID = 4 Or result.StatusID = 5 Or result.StatusID = 6 Then
                    If searchCriteria.InitiationDateFrom.Equals(CDate("1/1/1900")) And searchCriteria.InitiationDateTo.Equals(CDate("1/1/1900")) Then
                        If Not result.InitiationDate < Now.AddMonths(-1) Then
                            TempResults.Add(result)
                        End If
                    Else
                        TempResults.Add(result)
                    End If
                End If
            Next
        ElseIf SearchType.Equals("FOLLOWUP") Then
            For Each result As SearchResults In results
                If result.StatusID = 3 Or result.StatusID = 9 Then
                    If searchCriteria.InitiationDateFrom.Equals(CDate("1/1/1900")) And searchCriteria.InitiationDateTo.Equals(CDate("1/1/1900")) Then
                        If Not result.InitiationDate < Now.AddMonths(-1) Then
                            TempResults.Add(result)
                        End If
                    Else
                        TempResults.Add(result)
                    End If
                End If
            Next
        End If
        results = New List(Of SearchResults)
        results = TempResults
        Return results
    End Function
    Private Function FilterSearch(ByVal results As List(Of SearchResults), ByVal UserGroup As Integer) As List(Of SearchResults)
        Dim TempResults As New List(Of SearchResults)
        For Each Result As SearchResults In results
            If Result.InitiatorGroup = UserRole.INITIATOR_BASIC Then
                TempResults.Add(Result)
            End If
        Next
        Return TempResults
    End Function
End Class