<!--- 
  Filename: SendOrderConfirmation1.cfm
  Author:   Nate Weiss (NMW)
  Purpose:  Sends an email message to the person who placed an order
--->

<!--- Tag attributes --->
<CFPARAM NAME="ATTRIBUTES.OrderID" TYPE="numeric">

<!--- Retrieve order information from database --->
<CFQUERY DATASOURCE="#REQUEST.Datasource#" NAME="GetOrder">
  SELECT 
    c.ContactID, c.FirstName, c.LastName, c.Email,
    o.OrderDate, o.ShipAddress, o.ShipCity, 
    o.ShipState, o.ShipZip, o.ShipCountry,
    oi.OrderQty, oi.ItemPrice,
    m.MerchName,
    f.MovieTitle
  FROM 
    Contacts c,
    MerchandiseOrders o,
    MerchandiseOrdersItems oi, 
    Merchandise m,
    Films f
  WHERE 
    o.OrderID = #ATTRIBUTES.OrderID#
    AND c.ContactID = o.ContactID
    AND m.MerchID   = oi.ItemID
    AND o.OrderID   = oi.OrderID
    AND f.FilmID    = m.FilmID
  ORDER BY
    m.MerchName
</CFQUERY>

<!--- Re-Query the GetOrders query to find total $ spent --->
<!--- The DBTYPE="Query" invokes CF's "Query Of Queries" --->
<CFQUERY DBTYPE="Query" NAME="GetTotal">
  SELECT SUM(ItemPrice * OrderQty) AS OrderTotal
  FROM GetOrder
</CFQUERY>

<!--- We do not want ColdFusion to suppress whitespace here --->
<CFPROCESSINGDIRECTIVE SUPPRESSWHITESPACE="No">

<!--- Send email to the user --->
<!--- Because of the GROUP attribute, the inner <CFOUTPUT> --->
<!--- block will be repeated for each item in the order --->
<CFMAIL
  QUERY="GetOrder"
  GROUP="ContactID"
  GROUPCASESENSITIVE="No"
  STARTROW="1"
  SUBJECT="Thanks for your order (Order number #ATTRIBUTES.OrderID#)"
  TO="""#FirstName# #LastName#"" <#Email#>"
  FROM="""Orange Whip Online Store"" <orders@orangewhipstudios.com>"
>Thank you for ordering from Orange Whip Studios.
Here are the details of your order, which will ship shortly.
Please save or print this email for your records.

Order Number:  #ATTRIBUTES.OrderID#
Items Ordered: #RecordCount#
Date of Order: #DateFormat(OrderDate, "dddd, mmmm d, yyyy")#
               #TimeFormat(OrderDate)#
               
------------------------------------------------------
<CFOUTPUT>
#CurrentRow#. #MerchName# 
   (in commemoration of the film "#MovieTitle#")
   Price: #LSCurrencyFormat(ItemPrice)#
   Qty:   #OrderQty#
</CFOUTPUT>
------------------------------------------------------
Order Total: #LSCurrencyFormat(GetTotal.OrderTotal)#

This order will be shipped to:;;
#FirstName# #LastName#
#ShipAddress#
#ShipCity#
#ShipState# #ShipZip# #ShipCountry#

If you have any questions, please write back to us at
orders@orangewhipstudios.com, or just reply to this email.
</CFMAIL>

</CFPROCESSINGDIRECTIVE>