<!--- 
 Filename: NextN6.cfm
 Created by: Nate Weiss (NMW)
 Purpose: Displays Next N record-navigation interface
 Please Note Includes NextNIncludeBackNext.cfm and NextNIncludePageLinks.cfm
--->

<!--- Maintain ExpenseReport filtering variables at session level --->
<cfparam name="SESSION.expenseReport.userFilter" type="string" default="">
<cfparam name="SESSION.expenseReport.dateFrom" type="string" default="">
<cfparam name="SESSION.expenseReport.dateThru" type="string" default="">
<!--- Also which column is being sorted on, and in which direction --->
<cfparam name="SESSION.expenseReport.sortCol" type="string" default="d">
<cfparam name="SESSION.expenseReport.sortAsc" 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.expenseReport.sortCol = URL.sortCol>
 <cfset SESSION.expenseReport.sortAsc = URL.sortDir>
 <!--- Send user back to first row of query results --->
 <cfset URL.startRow = 1>
</cfif>

<!--- If the user is submitting the "filter" form, --->
<!--- we'll make their submission be the filter for rest of session --->
<cfif isDefined("FORM.userFilter")>
 <cfset SESSION.expenseReport.userFilter = FORM.userFilter>
 <cfset SESSION.expenseReport.dateFrom = FORM.dateFrom>
 <cfset SESSION.expenseReport.dateThru = FORM.dateThru>
</cfif>

<!--- Retrieve expense records from database --->
<cfquery name="getExp" datasource="#APPLICATION.DataSource#"
 cachedwithin="#createTimeSpan(0,0,15,0)#">
 SELECT 
 f.FilmID, f.MovieTitle, 
 e.Description, e.ExpenseAmount, e.ExpenseDate
 FROM 
 Expenses e INNER JOIN Films f
 ON e.FilmID = f.FilmID
 WHERE 
 0=0 
 <!--- If the user provided a filter string, ---> 
 <!--- show only matching films and/or expenses --->
 <cfif SESSION.expenseReport.userFilter is not "">
 AND (f.MovieTitle LIKE '%#SESSION.expenseReport.userFilter#%' OR
 e.Description LIKE '%#SESSION.expenseReport.userFilter#%')
 </cfif> 
 <!--- Also filter on From date, if provided --->
 <cfif isDate(SESSION.expenseReport.dateFrom)>
 AND e.ExpenseDate >= #createODBCDate(SESSION.expenseReport.dateFrom)#
 </cfif>
 <!--- Also filter on Through date, if provided --->
 <cfif isDate(SESSION.expenseReport.dateThru)>
 AND e.ExpenseDate <= #createODBCDate(SESSION.expenseReport.dateThru)#
 </cfif>
 ORDER BY 
 e.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.expenseReport.sortCol eq "d" and SESSION.expenseReport.sortAsc 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.expenseReport.sortCol#">
 <cfcase value="f">MovieTitle</cfcase>
 <cfcase value="e">Description</cfcase>
 <cfcase value="a">ExpenseAmount</cfcase>
 <cfdefaultcase> ExpenseDate</cfdefaultcase>
 </cfswitch>
 <!--- Appropriate sort direction --->
 <cfif SESSION.expenseReport.sortAsc>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>#APPLICATION.companyName# Expense Report</h2></cfoutput>

<!--- Simple style sheet for formatting --->
<style>
 FORM {font-family:sans-serif;font-size:smaller;}
 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>

<!--- Simple form to allow user to filter results --->
<cfform action="#CGI.script_name#" method="post">
 <!--- Filter string --->
 <b>Filter:</b>
 <cfinput type="Text" name="userFilter"
 value="#SESSION.expenseReport.userFilter#" size="15">
 
 <!--- From date ---> 
 &nbsp;
 <b>Dates:</b> from
 <cfinput type="Text" name="dateFrom" value="#SESSION.expenseReport.dateFrom#"
 size="9" validate="date" message="Please enter a valid date, or leave it blank.">
 
 <!--- Through date --->
 through
 <cfinput type="Text" name="dateThru" value="#SESSION.expenseReport.dateThru#"
 size="9" validate="date" message="Please enter a valid date, or leave it blank."> 
 
 <!--- Submit button to activate/change/clear filter --->
 <cfinput type="submit" name="submit" value="Apply"> 
</cfform>

<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="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.expenseReport.sortCol eq alias>
  <cfset sortDir = not SESSION.expenseReport.sortAsc>
 <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 indicate current sort --->
 <cfif SESSION.expenseReport.sortCol eq alias>
 <cfset sortImgSrc = iif(SESSION.expenseReport.sortAsc, "'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 AND totalRows gt rowsPerPage>
 <!--- Shortcut links for "Pages" of search results --->
 Page <cfinclude template="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="NextNIncludeBackNext.cfm">
 </cfif>
 </td>
 </tr>
</table>

</body>
</html>
