<?xml version="1.0"?>
<!DOCTYPE pdf PUBLIC "-//big.faceless.org//report" "report-1.1.dtd">
<%@ page language="java" import="java.sql.*, java.util.*, java.text.*" contentType="text/xml; charset=UTF-8"%>
<jsp:useBean id="dbase" class="phonebill.PhoneDatabase" scope="session" />   


<%--

This is a sample JSP which creates an XML definition for a PDF file.

The JSP is broken into three main stages

1. Headers.
   The correct XML headers need to be sent to the PDF conversion servlet,
   so that the XML parser knows how to process the file. This needs to
   be done right at the start, so the first two lines of this file are
   the XML headers to send, followed by two lines for the JSP engine -
   one language directive and the other loading a simple database bean.

2. Initialization.
   Next we read the parameters passed in the URL, load the account info
   from the database and set up a few basic variables, like number
   formatters, date formatters, the tax rate, pagesize and so on. If
   the specified account isn't found, we throw an error.

3. The XML.
   This is mostly sent verbatim to the PDF conversion servlet, with the
   exception of a few embedded JSP tags to substitute the account info,
   call breakdown and so on. This is the meat of the example, and we'll
   go into more detail on this further down in the source code

The database bean we're using is a custom one we use in-house, which
handles connection pooling and so on. We only use it to establish the
initial database connection here, so you can easily substitute your own.

--%>

<%
// 1. Initialization phase
//

// Set some basic information
//
double tax=0.175;

String pagesize = request.getParameter("pagesize");
if (pagesize==null) pagesize="A4";

// Much of the JSP is localized to the locale of the requester (that's you).
// We take whatever settings your web browser has specified, but if no country
// is specified force it to something so we get a value for the currency symbol.
// This "country-guessing" algorithm isn't very intelligent. If you get funny
// currency values, it's guessed wrong.
//
// This has nothing to do with PDF generation at all, but we think localization
// is one of Java's best kept secrets, and we use it whenever we have an excuse.
//
Locale loc = new Locale(request.getLocale().getLanguage(), (request.getLocale().getCountry().length()==0 ? (request.getLocale().getLanguage().equals("en") ? "GB" : request.getLocale().getLanguage().toUpperCase()) : request.getLocale().getCountry()));

// Create some formats to use throughout the PDF
//
NumberFormat amtf = NumberFormat.getCurrencyInstance(loc);
DateFormat datef = DateFormat.getDateInstance(DateFormat.LONG, loc);
DateFormat datetimef = DateFormat.getDateTimeInstance(DateFormat.MEDIUM, DateFormat.SHORT, loc);


// Load account information from the database
//
String account=request.getParameter("account"), name=null, address=null;
Connection c = dbase.getConnection();
Statement s = c.createStatement();

// If the account doens't exist, throw an error, otherwise......
//
if (account==null) {
    response.sendError(500, "No account number specified - need \"account\" parameter");
} else {

    ResultSet r = s.executeQuery("select accno, name, address from account where accno="+Integer.parseInt(account));

    while (r.next()) {
	name=r.getString("name");
	address=r.getString("address");
    }

    if (name==null) {
	response.sendError(500, "Unknown account \""+account+"\" - try \"12345678\"");
    } else {
	//
	// Load total amount for this bill.
	//
	double amount=0;
	r = s.executeQuery("select sum(seconds*charges.persecond) from calls, charges where charges.type=chargetype and accno="+Integer.parseInt(account));
	if (r.next()) {
	    amount=r.getDouble(1);
	}

	r.close();
	s.close();
	c.close();
%>


<%--

ACTUAL XML BEGINS HERE

First, we'd like to say that under normal circumstances this would
be split into a number of files. We'd have jsp:includes all over the
place to make this file smaller and more manageable.

In particular, a proper implementation would have an external
stylesheet which could be changed as necessary - for example,
customers could select their statements in color or black and white,
and all we need to do is load the appropriate stylesheet.



However, this is just an example and it's useful to have everything
in the one place to look at.



First, we start with the head. We use the "header" and "footer"
macros heavily in this example, to place a large standard header
on each page, and to put a payment slip on the first page. So in
this case, the head actually makes up most of the document.

--%>


<pdf>
<head>
<meta name="servlet-cache" value="15s"/>
<meta name="author" value="Mike Bremford"/>
<meta name="title" value="Sample Dynamic Report"/>
<meta name="subject" value="An example of a dynamic report created by the Big Faceless Report Library from a database"/>
<meta name="layout" value="one-column"/>

<style type="text/css">

/*
 * Some styles that are used throughout most of the document
 * search for 'class="info"' to see where these are used.
 */

.info		{ font-style:italic; }
.heading	{ font-family:helvetica; font-weight:bold; font-size:12; }
.address	{ width:3in; corner-radius:10; margin:10; padding:10; border:1; }
.amounttable	{ padding-left:0.5in; margin-right:0.5in; align:right; }

/*
 * The table with class="breakdown" is the main table in the document,
 * listing the itemized calls.
 *
 * We make fairly heavy use of styles here so that we can swap a new
 * style in quickly if we change our mind. Plus it's neater to do
 * it here than scatter attributes about the XML
 */

.breakdown		{ width:100% }
.breakdown thead tr	{ background-color:gray(20%); color:white; border-top:1; border-bottom:1; margin-bottom:4; font-weight:bold; font-family:helvetica; }
.breakdown thead td td			{ border:0; }
.breakdown thead tr td:firstchild	{ border-left:1; }
.breakdown thead tr td:lastchild	{ border-right:1; }
.breakdown tbody 	{ border:1; }

/*
 * These are the two graphs - numcalls the piegraph, callcost the bargraph
 */
#numcalls	{ xrotation:20; zrotation:20; display-key:rotated-inner-flat-outer; }
#callcost	{ xrotation:30; yrotation:20; xaxis-align:right; xaxis-valign:top;
		  yaxis-formatter:currency(); zwall-border-color:gray(70%); bar-width:80%;
		  bar-depth:80%; ywall-border-color:gray(70%); floor-border-color:gray(70%); }

/*
 * For both graphs, set some B+W printer friendly default colors.
 */
#callcost, #numcalls { default-colors:'#D0D0D0, pattern(stripes,#C0C0C0,#E0E0E0,4,2,30), pattern(spots,#C0C0C0,#E0E0E0,3), #A0A0A0, pattern(stripes,#E0E0E0,#C0C0C0,4,1.5,90)'; }

/*
 * The first page will always be given an ID of "page1". We can then
 * reference this in a stylesheet to create a custom footer - a "giro"
 * payment slip.
 */
#page1			{ footer:girofooter; footer-height:260; }

/*
 * The following styles all apply to the "Giro" payment slip
 * at the end of the first page.
 */
#giroslip 		{ width:100%; font-size:9; font-family:Times; font-style:normal; }
#giroslip .smallprint	{ font-size:6; font-family:Times; margin-top:2em; }
#giroslip .info		{ font-size:9; font-family:Times; font-style:italic; }
#giroslip .total	{ font-size:14; font-family:Times; font-weight:bold; margin-top:6; }
#giroslip .giro		{ font-size:16; font-family:Helvetica; font-weight:normal; }
#giroslip .sortcode	{ border:1; padding:1em; font-size:10; font-family:Helvetica; }
#giroslip td.poundbox	{ border:1; height:0.4in; width:0.9in; }
#giroslip td.pennybox	{ border:1; height:0.4in; width:0.4in; }
</style>

<macrolist>
  <macro id="girofooter">
    <!--

    This section creates a Giro payment slip (a Giro is a method of
    paying bills at the local post office, used in the UK). The author
    has never actually paid a bill at the post office with one of these,
    but believes it's possible.

    Rather than make this up, we copied it from an old British Telecom
    bill (a dreaded sight for computer people until flat-rate internet
    finally made it to England).

    Notice the nice dotted line at the top of the following <div>. Power
    users could place an absolutely positioned layer over this with one
    of the "pairs of scissors" characters in the predefined ZapfDingbats
    font, for added effect.

    -->

    <div id="giroslip" border-style="dotted" border-top="1" padding-top="10" margin-top="20">
      <table width="100%">
	<tr>

	  <!-- The left column. Logo, little boxes for the cashier to write in -->

	  <td margin-right="0.25in" valign="bottom">
	    <table cellpadding="0">
	      <tr>
		<td valign="top" margin-bottom="10">
		  <img src="bwlogo.jpg" dpi="300"/>
		  <p class="info" align="right"><big>Payment slip</big></p> 
		</td>
	      </tr>
	      <tr>
		<td>
		  <p class="smallprint">Cashier stamp and initials</p>

		  <!-- Create the corners-only box. -->

		  <shape border="1" width="80" height="80">
		    <shapepath>
		    <lineto x="10%" y="0%"/>
		    <moveto x="90%" y="0%"/>
		    <lineto x="100%" y="0%"/>
		    <lineto x="100%" y="10%"/>
		    <moveto x="100%" y="90%"/>
		    <lineto x="100%" y="100%"/>
		    <lineto x="90%" y="100%"/>
		    <moveto x="10%" y="100%"/>
		    <lineto x="0%" y="100%"/>
		    <lineto x="0%" y="90%"/>
		    <moveto x="0%" y="10%"/>
		    <lineto x="0%" y="0%"/>
		    </shapepath>
		  </shape>
		</td>
	      </tr>
	      <tr>
		<td> 
		  <table cellpadding="0">
		    <tr>
		      <td margin-right="20">
			<p class="smallprint"><nobr>No. cheques</nobr></p>
			<div border="1" width="30" height="30"/>
		      </td>
		      <td>
			<p class="smallprint">Fee</p>
			<div border="1" width="30" height="30"/>
		      </td>
		    </tr>
		  </table>
		</td>
	      </tr>
	    </table>
	  </td>

	  <!-- The right column. The main part of the Giro -->

	  <td>

	    <!-- First part of the right column. The amount, customer number
		 and Giro logo
	     -->

	    <table width="100%">
	      <tr>
		<td valign="bottom">
		  <p class="info" margin-right="0.5in"><nobr>Your Customer No.</nobr></p>
		</td>
		<td valign="bottom">
		  <p margin-right="1in"><nobr><%=account%></nobr></p>
		</td>
		<td padding="0" colspan="2" valign="bottom" align="right">

		  <!-- The "Bank Giro Credit" bit. On our bill the Giro symbol of
		       three circles doesn't fit inside the row the way normal HTML
		       table data would. This isn't a problem. We simply offset the
		       <div> containing the logo by 5 points vertically and create
		       the logo normally. Even though it's too big for the row,
		       it will display in full, overlapping the edge of it's
		       containing box due to the default overflow attribute value
		       of "visible".

		       Note the position="absolute" attributes on the various
		       elements.
		   -->

		  <table cellpadding="0"><tr>
		    <td>
		      <p class="giro"><nobr>Bank Giro Credit</nobr></p>
		    </td>
		    <td padding-bottom="0">
		      <div padding-left="10" width="30" height="0" y="-5">
			<circle position="absolute" x="14" y="6" radius="7" border="0.5"/>
			<circle position="absolute" x="7" y="18" radius="7" border="0.5"/>
			<circle position="absolute" x="21" y="18" radius="7" border="0.5"/>
			<shape position="absolute" width="100%" height="100%" border="2">
			  <shapepath>
			  <moveto x="21" y="6"/>
			  <lineto x="14" y="6"/>
			  <lineto x="7" y="18"/>
			  <moveto x="2.1" y="13.1"/>
			  <lineto x="7" y="18"/>
			  <lineto x="21" y="18"/>
			  <moveto x="16.1" y="22.9"/>
			  <lineto x="21" y="18"/>
			  <lineto x="14" y="6"/>
			  </shapepath>
			</shape>
		      </div>
		    </td>
		  </tr></table>
		</td>
	      </tr>
	      <tr>
		<td colspan="2" border-top="2" border-bottom="1">
		  <p class="total">Total amount due</p>
	        </td>
		<td border-top="2" border-bottom="1">
		  <p class="total"><%=amtf.format(amount*(1+tax))%></p>
		</td>
		<td></td>
	      </tr>
	    </table>

	    <!-- Second part of the right column. The "Dear Customer" bit
		 and the barcode.
	     -->

	    <table width="100%" margin-top="8">
	      <tr>
		<td margin-right="1in">
		  <p class="info">Dear Customer</p>
		  <ul>
		    <li><p>Please fill in parts 1 to 3 and insert a total next to the &#163; sign below.</p></li>
		    <li>Details of how to pay are shown overleaf.</li>
		    <li>Please do not send cash by post.</li>
		  </ul>
		</td>
		<td align="right" valign="middle">
		  <barcode codetype="code128" showtext="true" value="<%=account%>"/>
		</td>
	      </tr>
	    </table>

	    <!-- Third part of the right column. A 6 column table.
		 1st column = "Bank Details" text and the sort code box
		 2nd column = "Signature" and "Date" text
		 3rd column = blank, but has a bottom border to creat the underlines
		 4th column = "Cash" and "Cheques" text and the pound sign
		 5th column = the pounds box (would be the dollars box in the US)
		 6th column = the pennies box (would be the cents box in the US)
	     -->

	    <table width="100%" margin-top="10">
	      <tr>
		<td rowspan="3" valign="bottom">
		  <p>
		    Bank Details<br/>
		    Big Faceless Banking Corp.<br/>
		    Head Office Collection Acct.
		  </p>
		  <p class="sortcode">12-34-56</p>
		</td>
		<td valign="middle">1 Signature</td>
		<td border-bottom="1" width="1.5in" margin-right="0.2in"></td>
		<td valign="middle">3 Cash</td>
		<td class="poundbox"></td>
		<td class="pennybox"></td>
	      </tr>
	      <tr>
		<td valign="middle">2 Date</td>
		<td border-bottom="1" width="1.5in" margin-right="0.2in"></td>
		<td valign="middle">or cheques</td>
		<td class="poundbox"></td>
		<td class="pennybox"></td>
	      </tr>
	      <tr>
		<td colspan="3" valign="middle"><p align="right" class="giro">&#163;</p></td>
		<td colspan="2" class="poundbox"></td>
	      </tr>
	    </table>

	    <!-- Fourth part of the right side - the footnote -->

	    <p margin-top="2" class="smallprint">
	      Please do not fold, pin or staple this slip or write below this line
	    </p>
	  </td>
	</tr>
      </table>

      <!-- Create a black line at the bottom. Ideally we'd do this by setting a
	   the bottom border for the surrounding div, but we've already set the
	   top border to "dotted", and currently we can only set one border-style
	   for all the borders. We'll probably fix this in a later release
      -->
      <div width="100%" height="12" margin-top="2" margin-bottom="8" background-color="black"/>

    </div>
  </macro>

  <macro id="header">
    <!--
         This header is used on every page, and contains the
	 logo, address (ours and theres) and so on.
    -->

    <div>
      <table width="100%">
	<tr>
	  <!-- First the logo and it's accompanying text  -->

	  <td>
	    <table>
	      <tr>
		<td>
		  <img src="bwlogo.jpg" dpi="300"/>
		</td>
		<td valign="middle">
		  <p class="heading">Big Faceless Telecommunications Ltd.</p>
		</td>
	      </tr>
	    </table>
	  </td>

	  <!-- Next the customer number and our address -->

	  <td rowspan="2" align="right">
	    <table>
	      <tr>
		<td>
		  <p class="info" padding-right="0.5in">Your Customer No.</p>
		</td>
		<td>
		  <p><%=account%></p>
		</td>
	      </tr>
	      <tr border-bottom="1">
		<td>
		  <p class="info">Date</p>
		</td>
		<td>
		  <p><%=datef.format(new java.util.Date())%></p>
		</td>
	      </tr>
	      <tr border-bottom="2">
		<td colspan="2">
		  Big Faceless Telecommunications Ltd.<br/>
		  A fictitious company<br/>
		  Invented to demonstrate<br/>
		  The Report Generator<br/>
		  <br/>
		  http://bfo.com/products/report
		</td>
	      </tr>
	    </table>
	  </td>

	</tr>
	<tr>

	  <!-- On the next row, the customers address -->

	  <td valign="bottom"><pre class="address">
<%=name %>
<%=address%>
	    </pre></td>
	</tr>
      </table>

      <!-- Last of all, the title and pagenumber of the document -->

      <table width="100%" border-bottom="2" margin-bottom="8" class="heading">
	<tr>
	  <td>
	    Phone Bill for <%=name%>
	  </td>
	  <td>
	    <p align="right">Page <currentpage/> of <totalpages/></p>
	  </td>
	</tr>
      </table>
    </div>
  </macro>
</macrolist>

</head>

<!--

Now we begin the actual body of the document. In this example most
of the work is done in the headers and footers, so this bit is
fairly simple

Notice how we use the header="header" attribute. The quoted value is
just the ID of the appropriate macro to include - it could be called
anything, there's nothing special about it being called "header"

We also set the language for the whole document here - this is taken
from the locale, and is done in the form "en-US" or "de-DE". The
country part is used to format the currency values.

-->


<body size="<%=pagesize%>" header="header" header-height="160" padding="0.25in" font-size="10" lang="<%=loc.getLanguage()+"-"+loc.getCountry()%>">


<!-- First the heading table, giving the total amount of the bill -->

<table font-size="11" margin-bottom="10" >
  <tr>
    <td class="amounttable" border-bottom="1" padding-bottom="5">
      <p><%=amtf.format(amount)%></p>
    </td>
    <td>Call Charges</td>
  </tr>
  <tr>
    <td class="amounttable" padding-bottom="5">
      <p><%=amtf.format(amount)%></p>
    </td>
    <td>Subtotal excluding VAT</td>
  </tr>
  <tr>
    <td class="amounttable" padding-bottom="5" border-bottom="2">
      <p><%=amtf.format(amount*tax)%></p>
    </td>
    <td>Tax at <%=new DecimalFormat("#0.0%").format(tax)%></td>
  </tr>
  <tr font-weight="bold">
    <td class="amounttable">
      <p><%=amtf.format(amount*(1+tax))%></p>
    </td>
    <td>Total due</td>
  </tr>
</table>


<!-- Now the main table with the itemized calls. Because most of the
     styling information is done in the stylesheet back in the document
     head, this is surprisingly simple.

     We use the database again to get the call information.
-->

<table class="breakdown">
  <thead>
    <tr>
      <td>Date</td>
      <td>Number</td>
      <td>Destination</td>
      <td>Duration (seconds)</td>
      <td>Cost</td>
    </tr>
  </thead>
  <tbody>
<%
c = dbase.getConnection();
s = c.createStatement();
r = s.executeQuery("select date, callee, seconds, charges.name, charges.persecond from calls, charges where charges.type=calls.chargetype and accno="+account+" order by date");

while (r.next()) {
%>
<tr>
<td><%=datetimef.format(r.getTimestamp("date"))%></td>
<td><%=r.getString("callee")%></td>
<td><%=r.getString("charges.name")%></td>
<td><%=r.getInt("seconds")%></td>
<td><%=amtf.format(r.getInt("seconds") * r.getDouble("charges.persecond"))%></td>
</tr>
<%
}
r.close();
s.close();
c.close();
%>
  </tbody>
</table>

<!--

That's the call breakdown, now we place the final items on the statement,
the graphs analyzing the calls.

Again most of the styling for these graphs is done in the stylesheet in
the document head, so all we need to do here is put in the graph values

-->


<table width="100%" margin-top="20">
  <thead>
    <tr border-bottom="2" margin-bottom="8">
      <td colspan="2" class="heading">Call analysis</td>
    </tr>
  </thead>
  <tr>
    <td align="center">
      <h4>Number of calls</h4>

      <!-- First the Pie Graph -->

      <piegraph width="3.5in" height="3.5in" id="numcalls">
<%
c = dbase.getConnection();
s = c.createStatement();
r = s.executeQuery("select charges.name, count(*) 'tot' from charges, calls where calls.chargetype=charges.type group by chargetype;");
while (r.next()) {
%>
        <gdata name="<%=r.getString("charges.name")%>" value="<%=r.getDouble("tot")%>"/>
<%
}
r.close();
s.close();
c.close();
%>
      </piegraph>
    </td>
    <td align="center">

      <h4>Cost of calls</h4>

      <!-- Then the Bar Graph -->

      <bargraph width="3.5in" height="3.5in" id="callcost">
<%
c = dbase.getConnection();
s = c.createStatement();
r = s.executeQuery("select charges.name, sum(seconds)*persecond 'tot' from charges, calls where calls.chargetype=charges.type group by chargetype");
while (r.next()) {
%>
        <gdata name="<%=r.getString("charges.name")%>" value="<%=r.getDouble("tot")%>"/>
<%
}
r.close();
s.close();
c.close();
%>
      </bargraph>
    </td>
  </tr>
</table>

<!--

All done! Close the PDF. The final } brace matches the "if (account==null)"
test way back near the start of the document

-->
    

</body>
</pdf>
<% } } %>
