<!--- 
 Filename: NextN4.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="">

<!--- 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#">
 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>


<!--- 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}
</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 ---> 
 <input 
 type="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>
 <th width="100">Date</th>
 <th width="250">Film</th>
 <th width="150">Expense</th>
 <th width="100">Amount</th>
 </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>
