% This is an example document accompanying "LaTeX for Administrative Work"
% See www.dickimaw-books.com/latex/admin/ for the book details,
% including licence.
%
% This requires two datatooltk calls.
% Arara v4.0 directives for datatooltk:
% arara: datatooltk: {
% arara: --> output: order.dbtex,
% arara: --> sqldb: samples,
% arara: --> sqluser: sampleuser,
% arara: --> sql: "SELECT people.surname, people.forenames,
% arara: --> people.title, people.address1, people.address2,
% arara: --> people.town, people.county, countries.name AS countryname,
% arara: --> people.postcode, ordergroups.discount, ordergroups.postage
% arara: --> FROM people, ordergroups, countries
% arara: --> WHERE people.id = ordergroups.customerid AND ordergroups.id = 2
% arara: --> AND countries.code = people.country"}
% arara: datatooltk: {
% arara: --> output: orderlist.dbtex,
% arara: --> sqldb: samples,
% arara: --> sqluser: sampleuser,
% arara: --> sql: "SELECT books.title AS booktitle,
% arara: --> books.author, books.format, books.price, orders.quantity
% arara: --> FROM books, orders
% arara: --> WHERE orders.groupid = 2 AND orders.bookid = books.id"}
%
% Arara directive to build document:
% arara: pdflatex
\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}
