% 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, people.postcode,
% arara: --> countries.name AS countryname, ordergroups.discount,
% arara: --> ordergroups.postage,
% arara: --> SUM(books.price * orders.quantity) AS subtotal,
% arara: --> ROUND(SUM(IF(books.format='ebook',0.2*orders.quantity*books.price,0)),2)
% arara: --> AS vat,
% arara: --> (SUM(books.price*orders.quantity)+ROUND(SUM(IF(books.format='ebook',0.2*orders.quantity*books.price,0)),2)+ordergroups.postage-ordergroups.discount)
% arara: --> AS total
% arara: --> FROM books, orders, ordergroups, people, countries
% arara: --> WHERE orders.groupid = 2 AND ordergroups.id=orders.groupid
% arara: --> AND orders.bookid = books.id
% arara: --> AND people.id = ordergroups.customerid
% 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: --> (books.price * orders.quantity) AS subtotal
% arara: --> FROM books, orders
% arara: --> WHERE orders.groupid = 2 AND orders.bookid = books.id"}
%
% Arara directive to build document:
% arara: pdflatex
% arara: pdflatex
\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}
