import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import com.lowagie.text.*;
import com.lowagie.text.pdf.*;
import java.sql.*;
import java.awt.*;
import java.text.*;

public class clientInfoRpt extends HttpServlet
{
   public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException
   {
      // Open the output streams.
      OutputStream out = response.getOutputStream();
      ByteArrayOutputStream baos = new ByteArrayOutputStream();

      // Set constants.
      int pageWidth = 612;
      int pageHeight = 792;
      int leftMargin = 36;
      int rightMargin = 36;
      int topMargin = 36;
      int bottomMargin = 36;
      String company = "Keystone Capital Management, Inc.";
      String reportName = "Client Info";
      Color titleGray = new Color(175, 175, 175);
      Color dataGray = new Color(225, 225, 225);

      // Add check of session ID against the set cookie.

      // Pull the username, password, management number, and
      // as of date from the session.
      String username = "012001CRJ";
      String password = "keystone8";
      String clientMgmtNo = "404592";
      String asOfDateString = "01/10/2003";
      
      try
      {
         // Initialize the database connection.
         String url = "jdbc:oracle:thin:@oracle:1521:oracle";
         Class.forName("oracle.jdbc.driver.OracleDriver");
         Connection myConnection =
            DriverManager.getConnection(url, username, password);
         Statement myStatement = myConnection.createStatement();

         // Set up the number formatting.
         NumberFormat form0 = NumberFormat.getInstance();
         form0.setMinimumIntegerDigits(1);
         form0.setMinimumFractionDigits(0);
         form0.setMaximumFractionDigits(0);
         NumberFormat form1 = NumberFormat.getInstance();
         form1.setMinimumIntegerDigits(1);
         form1.setMinimumFractionDigits(1);
         form1.setMaximumFractionDigits(1);
         NumberFormat form2 = NumberFormat.getInstance();
         form2.setMinimumIntegerDigits(1);
         form2.setMinimumFractionDigits(2);
         form2.setMaximumFractionDigits(2);

         // Open the PDF document and create the base fonts.
         Document document = new Document(PageSize.LETTER, 0, 0, 0, 0);
         PdfWriter writer = PdfWriter.getInstance(document, baos);
         document.open();
         PdfContentByte cb = writer.getDirectContent();
         BaseFont bf = BaseFont.createFont(BaseFont.TIMES_ROMAN,
            BaseFont.CP1252, BaseFont.NOT_EMBEDDED);
         BaseFont bfBold = BaseFont.createFont(BaseFont.TIMES_BOLD,
            BaseFont.CP1252, BaseFont.NOT_EMBEDDED);
         com.lowagie.text.Font headerFont =
            new com.lowagie.text.Font(com.lowagie.text.Font.TIMES_NEW_ROMAN, 10,
            com.lowagie.text.Font.BOLD);
         com.lowagie.text.Font dataFont =
            new com.lowagie.text.Font(com.lowagie.text.Font.TIMES_NEW_ROMAN, 10,
            com.lowagie.text.Font.NORMAL);

         // Put the report header out.
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_CENTER, company,
            pageWidth / 2, pageHeight - 18, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 14);
         cb.showTextAligned(PdfContentByte.ALIGN_CENTER, reportName,
            pageWidth / 2, pageHeight - 50, 0);
         cb.endText();
         cb.setLineWidth(2f);
         cb.moveTo(275, pageHeight - 52);
         cb.lineTo(338, pageHeight - 52);
         cb.stroke();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_CENTER, "(as of " +
            asOfDateString + ")", pageWidth / 2, pageHeight - 64, 0);
         cb.endText();

         // Put the client info out.
         String sql = "SELECT REG1, REG3, REG5, CITY, ST, ZIP, MSTRACCT, " +
            "PRGM, TO_CHAR(CONTRACT, 'MM/DD/YYYY') CONTRACT, TO_CHAR(CANCEL, " +
            "'MM/DD/YYYY') CANCEL, ACCTTYPE, TRANFROM, TRANTO, FEEGRP, " +
            "FEEBILL, TAXID, MEMO FROM CLNTMSTR WHERE MGMTNO = '" +
            clientMgmtNo + "'";
         ResultSet myResultSet = myStatement.executeQuery(sql);
         myResultSet.next();
         String clientReg1 = myResultSet.getString("REG1");
         String clientReg3 = myResultSet.getString("REG3");
         if (clientReg3 == null)
         {
            clientReg3 = "";
         }
         String clientReg5 = myResultSet.getString("REG5");
         String clientCity = myResultSet.getString("CITY");
         String clientSt = myResultSet.getString("ST");
         String clientZip = myResultSet.getString("ZIP");
         String clientMstrAcct = myResultSet.getString("MSTRACCT");
         String clientPrgm = myResultSet.getString("PRGM");
         String clientContract = myResultSet.getString("CONTRACT");
         if (clientContract == null)
         {
            clientContract = "N/A";
         }
         String clientCancel = myResultSet.getString("CANCEL");
         if (clientCancel == null)
         {
            clientCancel = "N/A";
         }
         String clientAcctType = myResultSet.getString("ACCTTYPE");
         String clientTranFrom = myResultSet.getString("TRANFROM");
         if (clientTranFrom == null)
         {
            clientTranFrom = "N/A";
         }
         String clientTranTo = myResultSet.getString("TRANTO");
         if (clientTranTo == null)
         {
            clientTranTo = "N/A";
         }
         String clientFeeGrp = myResultSet.getString("FEEGRP");
         if (clientFeeGrp == null)
         {
            clientFeeGrp = "N/A";
         }
         String clientFeeBill = myResultSet.getString("FEEBILL");
         if (clientFeeBill == null)
         {
            clientFeeBill = "N/A";
         }
         cb.beginText();
         String clientTaxId = myResultSet.getString("TAXID");
         if (clientTaxId == null)
         {
            clientTaxId = "N/A";
         }
         String clientMemo = myResultSet.getString("MEMO");
         if (clientMemo == null)
         {
            clientMemo = "";
         }
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, "REGISTRATION",
            leftMargin, pageHeight - 90, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientReg1,
            leftMargin, pageHeight - 102, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientReg3,
            leftMargin, pageHeight - 114, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientReg5,
            leftMargin, pageHeight - 126, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientCity + ", " +
            clientSt + " " + clientZip,
            leftMargin, pageHeight - 138, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "MASTER ACCT:",
            300, pageHeight - 90, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientMstrAcct,
            305, pageHeight - 90, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "PROGRAM:",
            300, pageHeight - 102, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientPrgm,
            305, pageHeight - 102, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "CONTRACT:",
            300, pageHeight - 114, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientContract,
            305, pageHeight - 114, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "CANCEL:",
            300, pageHeight - 126, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientCancel,
            305, pageHeight - 126, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "ACCOUNT TYPE:",
            300, pageHeight - 138, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientAcctType,
            305, pageHeight - 138, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "NOTE:",
            300, pageHeight - 150, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientMemo,
            305, pageHeight - 150, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "TRANSFER FROM:",
            510, pageHeight - 90, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientTranFrom,
            515, pageHeight - 90, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "TRANSFER TO:",
            510, pageHeight - 102, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientTranTo,
            515, pageHeight - 102, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "FEE GROUP:",
            510, pageHeight - 114, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientFeeGrp,
            515, pageHeight - 114, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "PAYMENT TYPE:",
            510, pageHeight - 126, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientFeeBill,
            515, pageHeight - 126, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bfBold, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_RIGHT, "TAX ID:",
            510, pageHeight - 138, 0);
         cb.endText();
         cb.beginText();
         cb.setFontAndSize(bf, 10);
         cb.showTextAligned(PdfContentByte.ALIGN_LEFT, clientTaxId,
            515, pageHeight - 138, 0);
         cb.endText();

         // Create table and set defaults.
         PdfPTable table = new PdfPTable(4);
         table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_LEFT);
         table.getDefaultCell().setVerticalAlignment(Element.ALIGN_MIDDLE);
         table.setTotalWidth(pageWidth - leftMargin - rightMargin);
         int[] widths = {15, 55, 15, 15};
         table.setWidths(widths);

         // Create table header row.
         PdfPCell headerCell1 = new PdfPCell(new Phrase("FUND", headerFont));
         headerCell1.setBorderWidth(0);
         headerCell1.setBackgroundColor(titleGray);
         table.addCell(headerCell1);
         PdfPCell headerCell2 = new PdfPCell(new Phrase("DESCRIPTION", 
            headerFont));
         headerCell2.setBorderWidth(0);
         headerCell2.setBackgroundColor(titleGray);
         table.addCell(headerCell2);
         PdfPCell headerCell3 = new PdfPCell(new Phrase("DOLLARS", headerFont));
         headerCell3.setBorderWidth(0);
         headerCell3.setBackgroundColor(titleGray);
         headerCell3.setHorizontalAlignment(Element.ALIGN_RIGHT);
         table.addCell(headerCell3);
         PdfPCell headerCell4 = new PdfPCell(new Phrase("% POS", headerFont));
         headerCell4.setBorderWidth(0);
         headerCell4.setBackgroundColor(titleGray);
         headerCell4.setHorizontalAlignment(Element.ALIGN_RIGHT);
         table.addCell(headerCell4);

         // Create the variables needed for the client data fetch and the
         // table row creation.
         String clientGrpCode;
         String clientDescrip;
         float clientDollars;
         float clientPercent;
         float totalDollars = 0;
         PdfPCell field1Cell;
         PdfPCell field2Cell;
         PdfPCell field3Cell;
         PdfPCell field4Cell;

         // Fetch the rows from the database and loop through the result set
         // writing out to the table.
         sql = "SELECT GRPCODE, DESCRIP, FLOOR(SUM(CLNTTRN.SHARES) * " +
            "MY_PRICE(TO_DATE('" + asOfDateString + "', 'MM/DD/YYYY'), " +
            "GRPCODE) * 100 + 0.5) / 100, ROUND((FLOOR(SUM(CLNTTRN.SHARES) " +
            "* MY_PRICE(TO_DATE('" + asOfDateString + "', 'MM/DD/YYYY'), " +
            "GRPCODE) * 100 + 0.5)) / ACCTBAL('" + clientMgmtNo + "', " +
            "TO_DATE('" + asOfDateString + "', 'MM/DD/YYYY')), 1) " +
            "FROM CLNTTRN, FUND WHERE CLNTTRN.MGMTNO = '" + clientMgmtNo +
            "' AND CLNTTRN.TRNDT <= TO_DATE('" + asOfDateString +
            "', 'MM/DD/YYYY') AND FUND.GRP = CLNTTRN.GRPCODE GROUP BY " +
            "CLNTTRN.GRPCODE, DESCRIP HAVING SUM(CLNTTRN.SHARES) <> 0 " +
            "ORDER BY 1";
         myResultSet = myStatement.executeQuery(sql);
         int rowCount = 0;
         while (myResultSet.next())
         {
            rowCount++;
            
            // Fetch the fund data from the query.
            clientGrpCode = myResultSet.getString("GRPCODE");
            clientDescrip = myResultSet.getString("DESCRIP");
            clientDollars = myResultSet.getFloat(3);
            clientPercent = myResultSet.getFloat(4);
            totalDollars = totalDollars + clientDollars;

            // Write the fund data to table cells.
            field1Cell = new PdfPCell(new Phrase(clientGrpCode, dataFont));
            field1Cell.setBorderWidth(0);
            if ((rowCount % 2) == 0)
            {
               field1Cell.setBackgroundColor(dataGray);
            }
            table.addCell(field1Cell);
            field2Cell = new PdfPCell(new Phrase(clientDescrip, dataFont));
            field2Cell.setBorderWidth(0);
            if ((rowCount % 2) == 0)
            {
               field2Cell.setBackgroundColor(dataGray);
            }
            table.addCell(field2Cell);
            field3Cell = new PdfPCell(new Phrase("$" +
               form2.format(clientDollars), dataFont));
            field3Cell.setBorderWidth(0);
            field3Cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
            if ((rowCount % 2) == 0)
            {
               field3Cell.setBackgroundColor(dataGray);
            }
            table.addCell(field3Cell);
            field4Cell = new PdfPCell(new Phrase(form1.format(clientPercent) +
               "%", dataFont));
            field4Cell.setBorderWidth(0);
            field4Cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
            if ((rowCount % 2) == 0)
            {
               field4Cell.setBackgroundColor(dataGray);
            }
            table.addCell(field4Cell);
         }

         // Create the table footer with the totals.
         PdfPCell footerCell1 = new PdfPCell(new Phrase("TOTAL: $" +
            form2.format(totalDollars), headerFont));
         footerCell1.setColspan(3);
         footerCell1.setBorderWidth(0);
         footerCell1.setBackgroundColor(titleGray);
         footerCell1.setHorizontalAlignment(Element.ALIGN_RIGHT);
         table.addCell(footerCell1);
         PdfPCell footerCell2 = new PdfPCell(new Phrase("", headerFont));
         footerCell2.setBorderWidth(0);
         footerCell2.setBackgroundColor(titleGray);
         table.addCell(footerCell2);

         // Write out the table to the PDF calculating the page breaks
         // and page numbers.
         //table.writeSelectedRows(0, -1, leftMargin, pageHeight - 162, cb);
         table.writeSelectedRows(0, 1, leftMargin, pageHeight - 162, cb);
         int availPageHeight = pageHeight - 177 - bottomMargin;
         float pageNumber = 1;
         for (int rowCounter = 1; rowCounter <= (rowCount + 1); rowCounter++)
         {
            if (availPageHeight > 30)
            {
               table.writeSelectedRows(rowCounter, rowCounter + 1,
                  leftMargin, availPageHeight + bottomMargin, cb);
               availPageHeight = availPageHeight - 15;
            }
            else
            {
               document.newPage();
               pageNumber++;
               cb.beginText();
               cb.setFontAndSize(bf, 10);
               cb.showTextAligned(PdfContentByte.ALIGN_RIGHT,
                  "Page " + form0.format(pageNumber), pageWidth - rightMargin,
                  pageHeight - 18, 0);
               cb.endText();
               table.writeSelectedRows(0, 1, leftMargin, pageHeight - topMargin,
                  cb);
               availPageHeight = pageHeight - topMargin - 15 - bottomMargin;
               table.writeSelectedRows(rowCounter, rowCounter + 1,
                  leftMargin, availPageHeight + bottomMargin, cb);
               availPageHeight = availPageHeight - 15;
            }
         }

         // Close the document and send it out.
         document.close();
         response.setContentType("application/pdf");
         response.setContentLength(baos.size());
         response.setBufferSize(baos.size());
         baos.writeTo(out);
         out.flush();
         baos.close();
         myStatement.close();
         myConnection.close();
      }
      catch (SQLException se)
      {
         System.err.println(se.getMessage());
      }
      catch (ClassNotFoundException ce)
      {
         System.err.println(ce.getMessage());
      }
      catch (DocumentException de)
      {
         System.err.println(de.getMessage());
      }
      catch (IOException ioe)
      {
         System.err.println(ioe.getMessage());
      }
      finally
      {
         out.close();
      }
   }
}