<!--- Remember sort order for user, using SESSION scope --->
<CFPARAM NAME="SESSION.NextNSortCol" TYPE="string" DEFAULT="d">
<CFPARAM NAME="SESSION.NextNSortAsc" TYPE="boolean" DEFAULT="No">

<!--- If user is asking to change sort order --->
<CFIF IsDefined("URL.SortCol") AND IsDefined("URL.SortDir")>
  <!--- Save new order column/order in SESSION scope --->
  <CFSET SESSION.NextNSortCol = URL.SortCol>
  <CFSET SESSION.NextNSortAsc = URL.SortDir>
  <!--- Send user back to first row of query results --->
  <CFSET URL.StartRow = 1>
</CFIF>

<!--- Retrieve Expense Records from Database --->
<!--- Will be sorted by default order (expense date) --->
<CFQUERY NAME="GetExp" DATASOURCE="#REQUEST.DataSource#"
  CACHEDWITHIN="#CreateTimeSpan(0,0,30,0)#">
  SELECT 
    f.FilmID, f.MovieTitle, 
    e.Description, e.ExpenseAmount, e.ExpenseDate
  FROM 
    Expenses e INNER JOIN Films f
    ON e.FilmID = f.FilmID
  ORDER BY 
    ExpenseDate DESC
</CFQUERY>

<!--- If user's current sort order differs from the --->
<!--- default, use Q-of-Q to re-sort original query --->
<CFIF NOT (SESSION.NextNSortCol EQ "d" AND SESSION.NextNSortAsc EQ "No")>
  <!--- Re-query "GetExp" with appropriate ORDER BY --->
  <CFQUERY NAME="GetExp" DBTYPE="query">
    SELECT * FROM GetExp
    ORDER BY 
    <!--- Use appropriate sort column --->
    <CFSWITCH EXPRESSION="#SESSION.NextNSortCol#">
      <CFCASE VALUE="f">MovieTitle</CFCASE>
      <CFCASE VALUE="e">Description</CFCASE>
      <CFCASE VALUE="a">ExpenseAmount</CFCASE>
      <CFDEFAULTCASE>   ExpenseDate</CFDEFAULTCASE>
    </CFSWITCH>
    <!--- Appropriate sort direction --->
    <CFIF SESSION.NextNSortAsc>ASC<CFELSE>DESC</CFIF>
  </CFQUERY>
</CFIF>



<!--- Number of Rows to display per Next/Back page  --->
<CFSET RowsPerPage = 10>
<!--- What row to start at? Assume first by default --->
<CFPARAM NAME="URL.StartRow" DEFAULT="1" TYPE="numeric">
<!--- Allow for "Show All" parameter in the URL --->
<CFPARAM NAME="URL.ShowAll" TYPE="boolean" DEFAULT="No">

<!--- We know the total number of rows from query   --->
<CFSET TotalRows    = GetExp.RecordCount>
<!--- Show all on page if "ShowAll" passed in URL   --->
<CFIF URL.ShowAll>
  <CFSET RowsPerPage = TotalRows>
</CFIF>
<!--- Last row is 10 rows past the starting row, or --->
<!--- total number of query rows, whichever is less --->
<CFSET EndRow       = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
<!--- "Next" button goes to 1 past current end row  --->
<CFSET StartRowNext = EndRow + 1>
<!--- "Back" button goes back N rows from start row --->
<CFSET StartRowBack = URL.StartRow - RowsPerPage>


<!--- Page Title --->
<HTML>
<HEAD><TITLE>Expense Browser</TITLE></HEAD>
<BODY>
<CFOUTPUT><H2>#REQUEST.CompanyName# Expense Report</H2></CFOUTPUT>

<!--- Simple Style Sheet for formatting --->
<STYLE>
  TH        {font-family:sans-serif;font-size:smaller;
             background:navy;color:white}
  TD        {font-family:sans-serif;font-size:smaller}
  TD.DataA  {background:silver;color:black}
  TD.DataB  {background:lightgrey;color:black}
  A.Head    {color:white}
  A.Head:visited {color:white}
</STYLE>

<TABLE WIDTH="600" BORDER="0" CELLSPACING="0" CELLPADDING="1">
  <!--- Row at top of table, above column headers --->
  <TR>
    <TD WIDTH="500" COLSPAN="3">
      <!--- Message about which rows are being displayed --->
      <CFOUTPUT>
        Displaying <B>#URL.StartRow#</B> to <B>#EndRow#</B>
        of <B>#TotalRows#</B> Records<BR>
      </CFOUTPUT>
    </TD>
    <TD WIDTH="100" ALIGN="right">
      <CFIF NOT URL.ShowAll>
        <!--- Provide Next/Back links --->
        <CFINCLUDE TEMPLATE="../23/NextNIncludeBackNext.cfm">
      </CFIF>
    </TD>
  </TR>

  <!--- Row for Column Headers --->
  <TR>
    <CFOUTPUT>
      <!--- For each of the four columns... --->
      <CFLOOP LIST="Date,Film,Expense,Amount" INDEX="Col">
        <!--- Use 1st letter of column as "Alias" to pass in URL --->
        <CFSET Alias = LCase(Left(Col, 1))>
        <!--- If user already viewing by this col, link should   --->
        <!--- reverse order; otherwise, order should be ASC for  --->
        <!--- all columns except Date, when order should be DESC --->
        <CFIF SESSION.NextNSortCol EQ Alias>
          <CFSET SortDir = NOT SESSION.NextNSortAsc>
        <CFELSE>
          <CFSET SortDir = Col NEQ "Date">
        </CFIF>
        <!--- URL for when user clicks on column name --->
        <CFSET SortLink = "#CGI.SCRIPT_NAME#?SortCol=#Alias#&SortDir=#SortDir#">
        <TH>
          <!--- Show column heading as link that changes order --->
          <A HREF="#SortLink#" CLASS="Head">#Col#</A>
          <!--- If this is current column, show icon to incicate current sort --->
          <CFIF SESSION.NextNSortCol EQ Alias>
            <CFSET SortImgSrc = IIF(SESSION.NextNSortAsc, "'SortA.gif'", "'SortD.gif'")>
            <IMG SRC="../images/#SortImgSrc#" WIDTH="12" HEIGHT="12" ALT="">
          </CFIF>          
        </TH>
      </CFLOOP>
    </CFOUTPUT>
  </TR>
  
  <!--- For each query row that should be shown now --->
  <CFLOOP QUERY="GetExp" StartRow="#URL.StartRow#" ENDROW="#EndRow#">
    <!--- Use class "DataA" or "DataB" for alternate rows --->
    <CFSET Class = IIF(GetExp.CurrentRow MOD 2 EQ 0, "'DataA'", "'DataB'")>
  
    <CFOUTPUT>
      <TR VALIGN="baseline">
        <TD CLASS="#Class#" WIDTH="100">#LSDateFormat(ExpenseDate)#</TD>
        <TD CLASS="#Class#" WIDTH="250">#MovieTitle#</TD>
        <TD CLASS="#Class#" WIDTH="150"><I>#Description#</I></TD>
        <TD CLASS="#Class#" WIDTH="100">#LSCurrencyFormat(ExpenseAmount)#</TD>
      </TR>
    </CFOUTPUT>
  </CFLOOP>
  
  <!--- Row at bottom of table, after rows of data --->
  <TR>
    <TD WIDTH="500" COLSPAN="3">
      <CFIF NOT URL.ShowAll>
        <!--- Shortcut links for "Pages" of search results --->
        Page <CFINCLUDE TEMPLATE="../23/NextNIncludePageLinks.cfm">
        <!--- "Show All" Link --->
        <CFOUTPUT>
          <A HREF="#CGI.SCRIPT_NAME#?ShowAll=Yes">Show All</A>
        </CFOUTPUT>
      </CFIF> 
    </TD>
    <TD WIDTH="100" ALIGN="right">
      <CFIF NOT URL.ShowAll>
        <!--- Provide Next/Back links --->
        <CFINCLUDE TEMPLATE="../23/NextNIncludeBackNext.cfm">
      </CFIF>
    </TD>
  </TR>
</TABLE>

</BODY>
</HTML>
