Exercise 14: Custom Invoice using CSV Files (Solution)

This is a solution to the CSV part of Exercise 14.
You need the sample files people.csv, booklist.csv, country-codes.csv, ordergroups.csv and orders.csv.
\documentclass{isodoc}

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

\usepackage{datatool}

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

\DTLloaddb{orders}{orders.csv}
\DTLloaddb{ordergroups}{ordergroups.csv}
\DTLloaddb{books}{booklist.csv}
\DTLloaddb{countries}{country-codes.csv}
\DTLloaddb{people}{people.csv}

\DTLassignfirstmatch{ordergroups}{id}{2}{%
  \CustomerId=customerid,%
  \OrderDiscount=discount,%
  \Postage=postage%
}
\xDTLassignfirstmatch{people}{id}{\CustomerId}{%
  \Title=title,%
  \Forenames=forenames,%
  \Surname=surname,%
  \AddressI=address1,%
  \AddressII=address2,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode,%
  \CountryCode=country%
}
\xDTLassignfirstmatch{countries}{code}{\CountryCode}{\CountryName=name}

\begin{document}
\newcommand*{\CurrentTotal}{0}% initialise running total
\newcommand*{\VatTotal}{0}% initialise VAT total
\newcommand*{\mytablecontents}{}% initialise hook
\newcommand*{\vatrating}{}

\DTLforeach*[\equal{\OrderGroupId}{2}]{orders}%
{%
  \OrderGroupId=groupid,%
  \BookId=bookid,%
  \OrderQuantity=quantity%
}%
{%
  \xDTLassignfirstmatch{books}{id}{\BookId}%
  {%
    \BookTitle=title,%
    \BookAuthor=author,%
    \BookFormat=format,%
    \BookPrice=price%
  }%
% Compute quantity x price:
  \dtlmul{\subtotal}{\OrderQuantity}{\BookPrice}%
% Is this an ebook?
  \ifdefstring{\BookFormat}{ebook}%
  {% compute 20% VAT
    \dtlmul{\vat}{\subtotal}{0.2}%
   % add to VAT total
    \dtladd{\VatTotal}{\VatTotal}{\vat}%
    \renewcommand*{\vatrating}{20}%
  }%
  {%
    \renewcommand*{\vatrating}{0}%
  }%
% Add subtotal to the running total:
  \dtladd{\CurrentTotal}{\CurrentTotal}{\subtotal}%
% Round to 2 d.p.
  \dtlround{\subtotal}{\subtotal}{2}%
% Append to hook:
  \eappto\mytablecontents{%
   ``\BookTitle'' (\BookFormat) \noexpand&
   \OrderQuantity \noexpand&
   \BookPrice \noexpand&
   \vatrating\% \noexpand& 
   \subtotal\noexpand\\}%
}%
% Round the subtotal to 2 d.p.
\dtlround{\subtotal}{\CurrentTotal}{2}%
% Round the VAT to 2 d.p.
\dtlround{\VatTotal}{\VatTotal}{2}%
% Add the VAT to running total:
\dtladd{\CurrentTotal}{\CurrentTotal}{\VatTotal}%
% Add postage to running total:
\dtladd{\CurrentTotal}{\CurrentTotal}{\Postage}%
% Subtract discount from running total:
\dtlsub{\CurrentTotal}{\CurrentTotal}{\OrderDiscount}%
% Round to 2 d.p.:
\dtlround{\CurrentTotal}{\CurrentTotal}{2}%

\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)\\
  \midrule
  \endhead
  \mytablecontents
  \midrule
  \multicolumn{4}{r}{\bfseries Sub-Total} & \subtotal\\
  \multicolumn{4}{r}{\bfseries VAT} & \VatTotal\\
  \multicolumn{4}{r}{\bfseries Postage and Packaging} & \Postage\\
  \multicolumn{4}{r}{\bfseries Promotional Discount} & $-\OrderDiscount$\\
  \midrule
  \multicolumn{4}{r}{\bfseries Total} & \CurrentTotal
  \end{longtable}
}

\end{document}
Download invoice-longtable-csv.tex or invoice-longtable-csv.pdf.