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 14: Custom Invoice using SQL Data (Solution)

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

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, vat, 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, ROUND(SUM(IF(books.format='ebook',0.2*orders.quantity*books.price,0)),2) AS vat, (SUM(books.price*orders.quantity)+ROUND(SUM(IF(books.format='ebook',0.2*orders.quantity*books.price,0)),2)+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\\%
    \bfseries Item &
    \bfseries Quantity &
    \bfseries Unit Price (\pounds) &
    \bfseries VAT \% &
    \bfseries Price (\pounds)%
      ``\BookTitle'' (\BookFormat) &
      \OrderQuantity &
      \BookPrice &
      \ifdefstring{\BookFormat}{ebook}{20}{0}\% &
    \multicolumn{4}{r}{\bfseries Sub-Total} & \SubTotal\\
    \multicolumn{4}{r}{\bfseries VAT} & \Vat\\
    \multicolumn{4}{r}{\bfseries Postage and Packaging} & \Postage\\
    \multicolumn{4}{r}{\bfseries Promotional Discount} & $-\OrderDiscount$\\
    \multicolumn{4}{r}{\bfseries Total} & \Total


Download invoice-longtable-sql.tex or invoice-longtable-sql.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