Exercise 13: Creating an Invoice for a Customer (invoice.sty) using SQL Data (Solution)

This is a solution to the SQL part of Exercise 13. I've used the letter class file for my solution and used an empty \opening{} to ensure the recipient's address is displayed.

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 and the discount and postage for the order identified by the group order id 2:

datatooltk --output order.dbtex --sqldb samples --sqluser sampleuser --sql "SELECT people.surname, people.forenames, people.title, people.address1, people.address2, people.town, people.county, countries.name AS countryname, people.postcode, ordergroups.discount, ordergroups.postage FROM people, ordergroups, countries WHERE people.id = ordergroups.customerid AND ordergroups.id = 2 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:

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

\documentclass[12pt]{letter}

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

\usepackage[a4paper]{geometry}
\usepackage[british]{babel}

\usepackage{datatool}

\usepackage{invoice}

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

\renewcommand*{\Fees}{Products}
\renewcommand*{\UnitRate}{Price}
\renewcommand*{\Count}{Quantity}
\renewcommand*{\Activity}{Product}

\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%
}

\begin{letter}{\DTLifnullorempty{\Title}{}{\Title\ }%
  \Forenames\ \Surname\\%
  \AddressI\\%
  \DTLifnullorempty{\AddressII}{}{\AddressII\\}%
  \Town\\%
  \DTLifnullorempty{\County}{}{\County\\}%
  \Postcode\\%
  \CountryName}

  \opening{}

  \begin{invoice}{\pounds}{0}
   \ProjectTitle{Book Order}%
   \DTLforeach*{\orderlist}%
   {%
     \BookTitle=booktitle,%
     \BookAuthor=author,%
     \BookFormat=format,%
     \BookPrice=price,%
     \OrderQuantity=quantity%
   }%
   {%
     \Fee{\BookTitle\newline 
     by \BookAuthor\ (\BookFormat)}{\BookPrice}{\OrderQuantity}%
   }%
   \EBC{Postage and Packaging}{\Postage}%
   \Discount{Promotion}{\OrderDiscount}%
  \end{invoice}

\end{letter}

\end{document}

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