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:

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"

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}

\usepackage{longtable}
\usepackage{array}
\usepackage{booktabs}

\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,%
  \Vat=vat,%
  \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}
]
{
    \begin{longtable}{>{\raggedright}p{0.3\linewidth}rrrr}
    \bfseries Item &
    \bfseries Quantity &
    \bfseries Unit Price (\pounds) &
    \bfseries VAT \% &
    \bfseries Price (\pounds)%
    \endhead
    \DTLforeach*{\orderlist}%
    {%
      \BookTitle=booktitle,%
      \BookAuthor=author,%
      \BookFormat=format,%
      \BookPrice=price,%
      \OrderQuantity=quantity,%
      \ThisSubTotal=subtotal%
    }%
    {%
      \\%
      ``\BookTitle'' (\BookFormat) &
      \OrderQuantity &
      \BookPrice &
      \ifdefstring{\BookFormat}{ebook}{20}{0}\% &
      \ThisSubTotal
    }%
    \\\midrule
    \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$\\
    \midrule
    \multicolumn{4}{r}{\bfseries Total} & \Total
    \end{longtable}
}

\end{document}

Download invoice-longtable-sql.tex or invoice-longtable-sql.pdf.