Latest news 2024-02-16: Experimental (unstable) datatool v2.49a available for testing. Download from the experimental page if you would like to try it out.

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:

  1. 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"
  2. 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"







 to={\DTLifnullorempty{\Title}{}{\Title\ }%
  \Forenames\ \Surname\\%
      \iitem{\OrderQuantity\ $\times$ \BookTitle\newline 
      by \BookAuthor\ (\BookFormat) @ \BookPrice\ each}{\ThisSubTotal}%
    \iitem{Postage and Packaging}{\Postage}%
    \iitem{Promotional Discount}{$-\OrderDiscount$}%


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

© 2015 Dickimaw Books. "Dickimaw", "Dickimaw Books" and the Dickimaw parrot logo are trademarks. The Dickimaw parrot was painted by Magdalene Pritchett.

Terms of Use Privacy Policy Cookies Site Map FAQs