<!--- Execute stored procedure to fetch film information --->
<CFSTOREDPROC PROCEDURE="FetchFilmInfo" DATASOURCE="owsServer">
  <!--- Provide the FilmID parameter --->
  <CFPROCPARAM
    TYPE="In"
    DBVARNAME="@FilmID"
    CFSQLTYPE="CF_SQL_INTEGER"
    VALUE="#URL.FilmID#">

  <!--- Film information --->    
  <CFPROCRESULT NAME="GetFilm" RESULTSET="1">
  <!--- Expense information --->    
  <CFPROCRESULT NAME="GetExpenses" RESULTSET="2">
  <!--- Actor information --->    
  <CFPROCRESULT NAME="GetActors" RESULTSET="3">
  <!--- Director information --->    
  <CFPROCRESULT NAME="GetDirectors" RESULTSET="4">
  <!--- Director information --->    
  <CFPROCRESULT NAME="GetMerch" RESULTSET="5">
</CFSTOREDPROC>

<!--- Get subtotals from the recordsets returned by stored procedure --->
<CFSET ExpenseSum  = ArraySum(ListToArray(ValueList(GetExpenses.ExpenseAmount)))>
<CFSET ActorSum    = ArraySum(ListToArray(ValueList(GetActors.Salary)))>
<CFSET DirectorSum = ArraySum(ListToArray(ValueList(GetDirectors.Salary)))>
<CFSET MerchSum    = ArraySum(ListToArray(ValueList(GetMerch.TotalSales)))>

<!--- Add up all expenses --->
<CFSET TotalExpenses = ExpenseSum + ActorSum + DirectorSum - MerchSum>
<!--- Determine how much money is left in the budget --->
<CFSET LeftInBudget = GetFilm.AmountBudgeted - TotalExpenses>

<HTML>
<HEAD><TITLE>Film Expenses</TITLE></HEAD>
<BODY>

<!--- Company logo and page title --->
<IMG SRC="../images/logo_b.gif" WIDTH="73" HEIGHT="73" ALIGN="absmiddle">
<FONT SIZE="+2"><B>Film Expenses</B></FONT><BR CLEAR="all">
  
<CFOUTPUT>
  <!--- Show film title--->
  <P><B>Film:</B> #GetFilm.MovieTitle#<BR>

  <!--- Film budget, expense total, and amount left in budget --->
  <P><B>Budget:</B> #LSCurrencyFormat(GetFilm.AmountBudgeted)#<BR>
  <B>Expenses:</B> #LSCurrencyFormat(TotalExpenses)#<BR>
  <B>Currently:</B> #LSCurrencyFormat(LeftInBudget)# 
  <!--- Are we currently over or under budget? --->
  #IIF(LeftInBudget LT 0, "'over budget'", "'under budget'")#<BR>

  <!--- Output information about actors --->  
  <P><B>Actors:</B>
  <CFLOOP QUERY="GetActors">
    <LI>#NameFirst# #NameLast# (Salary: #LSCurrencyFormat(Salary)#)
  </CFLOOP>

  <!--- Output information about directors --->
  <P><B>Directors:</B>
  <CFLOOP QUERY="GetDirectors">
    <LI>#FirstName# #LastName# (Salary: #LSCurrencyFormat(Salary)#)
  </CFLOOP>

  <!--- Output information about expenses --->  
  <P><B>Other Expenses:</B>
  <CFLOOP QUERY="GetExpenses">
    <LI>#Description# (#LSCurrencyFormat(ExpenseAmount)#)
  </CFLOOP>

  <!--- Output information about merchandise --->  
  <P><B>Income from merchandise:</B>
  <CFLOOP QUERY="GetMerch">
    <LI>#MerchName# (Sales: #LSCurrencyFormat(TotalSales)#)
  </CFLOOP>
</CFOUTPUT>

</BODY>
</HTML>