Exercise 12: Creating an Invoice for a Customer (isodoc.cls) using SQL Data (Solution)

This is a solution to the SQL part of Exercise 12.

You need the sample SQL data supplied in samples.sql. This requires two calls to datatooltk before the document can be built:

The first call creates a file called order.dbtex that contains the customer details, subtotal, total, discount and postage for the order identified by the group order id 2:

datatooltk --output order.dbtex --sqldb samples --sqluser sampleuser --sqlpassword sample-passwd --sql "SELECT people.surname, people.forenames, people.title, people.address1, people.address2, people.town, people.county, people.postcode, countries.name AS countryname, ordergroups.discount, ordergroups.postage, SUM(books.price * orders.quantity) AS subtotal, (SUM(books.price*orders.quantity)+ordergroups.postage-ordergroups.discount) AS total FROM books, orders, ordergroups, people, countries WHERE orders.groupid = 2 AND ordergroups.id=orders.groupid AND orders.bookid = books.id AND people.id = ordergroups.customerid AND countries.code = people.country"

The second call creates a file called orderlist/dbtex that contains the details of each of the books in the order along with the quantity ordered and the subtotal (quantity times price):

datatooltk --output orderlist.dbtex --sqldb samples --sqluser sampleuser --sqlpassword sample-passwd --sql "SELECT books.title AS booktitle, books.author, books.format, books.price, orders.quantity, books.price * orders.quantity AS subtotal FROM books, orders WHERE orders.groupid = 2 AND orders.bookid = books.id"

\documentclass{isodoc}

\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}

\usepackage{datatool}

\DTLloaddbtex{\thisorder}{order.dbtex}
\DTLloaddbtex{\orderlist}{orderlist.dbtex}

\begin{document}

\DTLassign{\thisorder}{1}{%
  \Title=title,%
  \Forenames=forenames,%
  \Surname=surname,%
  \AddressI=address1,%
  \AddressII=address2,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode,%
  \CountryName=countryname,%
  \OrderDiscount=discount,%
  \Postage=postage,%
  \SubTotal=subtotal,%
  \Total=total%
}

\invoice
[
 to={\DTLifnullorempty{\Title}{}{\Title\ }%
  \Forenames\ \Surname\\%
  \AddressI\\%
  \DTLifnullorempty{\AddressII}{}{\AddressII\\}%
  \Town\\%
  \DTLifnullorempty{\County}{}{\County\\}%
  \Postcode\\%
  \CountryName},
  currency={\pounds}
]
{
  \itable
  {
    \DTLforeach*{\orderlist}%
    {%
      \BookTitle=booktitle,%
      \BookAuthor=author,%
      \BookFormat=format,%
      \BookPrice=price,%
      \OrderQuantity=quantity,%
      \ThisSubTotal=subtotal%
    }%
    {%
      \iitem{\OrderQuantity\ $\times$ \BookTitle\newline 
      by \BookAuthor\ (\BookFormat) @ \BookPrice\ each}{\ThisSubTotal}%
    }%
    \itotal[Subtotal]{\SubTotal}
    \iitem{Postage and Packaging}{\Postage}%
    \iitem{Promotional Discount}{$-\OrderDiscount$}%
    \itotal{\Total}
  }
}

\end{document}

Download invoice-isodoc-csv.tex or invoice-isodoc-csv.pdf.