<!--- Retrieve expense records from database --->
<!--- Query will be cached for 15 minutes at a time --->
<CFQUERY NAME="GetExp" DATASOURCE="#REQUEST.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
  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>#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}
</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="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>
        <!--- 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>
