package test;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.xml.parsers.*;

import java.awt.*;

import org.w3c.dom.*;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import com.lowagie.text.Font;
import com.lowagie.text.pdf.*;
import com.lucaslee.report.*;
import com.lucaslee.report.model.crosstable.*;
import com.lucaslee.report.grouparithmetic.*;
import com.lucaslee.report.model.*;
import com.lucaslee.report.model.Table;
import com.lucaslee.report.printer.*;

/**
 *
 * <p>Title: 生成交叉表報(bào)表例子。</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company:Lucas-lee Soft </p>
 * @author Lucas Lee
 * @version 1.0
 */
public class TestCrossReport {
  public TestCrossReport() {
  }

  public static Connection getConn() throws Exception {
    Connection con = null;
    Class.forName("oracle.jdbc.driver.OracleDriver");
    con = DriverManager.getConnection("jdbc:oracle:thin:@t3:1521:bslgs",
                                      "sinopec", "sinopec");
    return con;
  }

  public static void getHTMLReport(Report report) throws Exception {
    FileOutputStream fo = new FileOutputStream("crossTab.html");
    HTMLCss css = new HTMLCss();
    css.setGroupTotal("BACKGROUND-COLOR: #d8e4f1; font: bold 12pt 隸書;");
    css.setHead("BACKGROUND-COLOR: #ffdead; font: bold 12pt 隸書;");
    css.setTotal("BACKGROUND-COLOR: #d8e4f1; font: bold 12pt 隸書;");
    css.setTitle("font: bold 18pt ;");
    css.setData("font: 12pt");
    css.setCrossHeadHead("BACKGROUND-COLOR: #a68763; font: 9pt ");
    new HTMLPrinter().print(report, css, fo);
    fo.close();
  }

  public static void getPDFReport(Report report) throws Exception {
    FileOutputStream fo = new FileOutputStream("crossTab.pdf");

    BaseFont bfChinese = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H",
                                             BaseFont.NOT_EMBEDDED);
    BaseFont bfChineseBold = BaseFont.createFont("STSong-Light,Bold",
                                                 "UniGB-UCS2-H",
                                                 BaseFont.NOT_EMBEDDED);
    Font FontChinese = new Font(bfChinese, 10, Font.NORMAL); //創(chuàng)建中文字體
    Font FontChineseBold = new Font(bfChineseBold, 10, Font.NORMAL); //創(chuàng)建中文字體

    PDFCss css = new PDFCss();

    PDFCssItem item = new PDFCssItem();
    item.setBackgroudColor(new Color(0xd8e4f1));
    item.setFont(FontChinese);
    css.setGroupTotal(item);
    css.setTotal(item);

    item = new PDFCssItem();
    item.setBackgroudColor(new Color(0xffdead));
    item.setFont(FontChineseBold);
    css.setHead(item);

    item = new PDFCssItem();
    item.setFont(new Font(bfChineseBold, 15, Font.BOLD));
    css.setTitle(item);

    item = new PDFCssItem();
    item.setFont(new Font(bfChinese, 10, Font.NORMAL));
    css.setData(item);

    item = new PDFCssItem();
    item.setFont(new Font(bfChinese, 7, Font.NORMAL));
    item.setBackgroudColor(new Color(0xffdead));
    css.setCrossHeadHead(item);

    new PDFPrinter().print(report, css, fo);
    fo.close();
  }

  public static void getCSVReport(Report report) throws Exception {
    FileOutputStream fo = new FileOutputStream("crossTab.csv");
    new CSVPrinter().print(report, fo);
    fo.close();
  }

  private static Table getTableByConn() throws Exception {
    Table t = new Table();
    Connection con = null;
    t.setBorder(1);
    try {
      con = getConn();
      Statement st = con.createStatement();
      String sql = "select * from marketpriceview";
      ResultSet rs = st.executeQuery(sql);

      while (rs.next()) {
        TableRow tr = new TableRow();
        for (int i = 0; i < 4; i++) {
          tr.addCell(new TableCell(rs.getString(i + 1) + ""));
        }
        t.addRow(tr);
      }

    } finally {
      if (con != null)
        con.close();
    }
    return t;
  }

  private static void setTitleFooter(Report report) throws ReportException {
    /*****************設(shè)置標(biāo)題，腳注*********************/
    Table headerTable = new Table();
    int[] widths={20,60,20};
    headerTable.setWidths(widths);
    report.setHeaderTable(headerTable);
    Table footerTable = new Table();
    report.setFooterTable(footerTable);

    headerTable.setBorder(0);
    headerTable.setAlign(headerTable.ALIGN_CENTER);

    TableCell tc = null;
    TableRow tr = null;

    tr = new TableRow(3);
    headerTable.addRow(tr);
    tc = tr.getCell(0);
    tc.setColSpan(3);
    tc.setAlign(tc.ALIGN_CENTER);
    tc.setContent("中國(guó)XXX股份有限公司XXX分公司");
    tr.getCell(1).setIsHidden(true);
    tr.getCell(2).setIsHidden(true);

    tr = new TableRow(3);
    headerTable.addRow(tr);
    tc = tr.getCell(0);
    tc.setColSpan(3);
    tc.setAlign(tc.ALIGN_CENTER);
    tc.setContent("產(chǎn)品銷售統(tǒng)計(jì)報(bào)表");
    tc.setCssClass(Report.TITLE_TYPE);
    tr.getCell(1).setIsHidden(true);
    tr.getCell(2).setIsHidden(true);

    tr = new TableRow(3);
    headerTable.addRow(tr);

    tr = new TableRow(3);
    headerTable.addRow(tr);
    tc = tr.getCell(0);
    tc.setContent("單位：xxx分公司");
    tc.setAlign(tc.ALIGN_LEFT);
    tc = tr.getCell(1);
    tc.setContent("報(bào)表日期：2003-11-11至2003-11-16");
    tc.setAlign(tc.ALIGN_CENTER);
    tc = tr.getCell(2);
    tc.setAlign(tc.ALIGN_RIGHT);
    tc.setContent("單位：噸  元");

    tr = new TableRow(3);
    footerTable.setBorder(0);
    footerTable.setAlign(footerTable.ALIGN_CENTER);
    footerTable.addRow(tr);
    tr.getCell(0).setContent("制表人:xxx");
    tr.getCell(0).setAlign(tc.ALIGN_LEFT);
    tr.getCell(1).setContent("審核人:xxx");
    tr.getCell(1).setAlign(tc.ALIGN_CENTER);
    tr.getCell(2).setContent("制表日期:xxx");
    tr.getCell(2).setAlign(tc.ALIGN_RIGHT);
  }

  public static Table getTable() throws Exception {

    Table t = new Table();
    double multip = 100.00;
    for (int i = 0; i < 15; i++) {
      TableRow tr = new TableRow();
      t.addRow(tr);

      tr.addCell(new TableCell("產(chǎn)品" + i));
      tr.addCell(new TableCell("" + (i * multip)));
      tr.addCell(new TableCell("" + (i + 1) * multip));
      tr.addCell(new TableCell("" + (i + 2) * multip));

      t.addRow(tr.cloneAll());

      tr = new TableRow();
      t.addRow(tr);
      tr.addCell(new TableCell("產(chǎn)品" + i));
      tr.addCell(new TableCell("" + (i + 1) * multip));
      tr.addCell(new TableCell("" + (i + 2) * multip));
      tr.addCell(new TableCell("" + (i + 2) * multip));

    }

    for (int i = 0; i < 0; i++) {
      t.addCol(t.getCol(3).cloneAll());
    }

    return t;
  }

  private static Table getTableByXML() throws Exception {
    String fileName = "crossTabSample.xml";
    Table t = new Table();
    Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().
        parse(new File(fileName));
    Element e = doc.getDocumentElement();
    NodeList xmlTab = e.getChildNodes();
    for (int i = 0; i < xmlTab.getLength(); i++) {
      if (xmlTab.item(i).getNodeName().equals("Row")) {
        TableRow tr = new TableRow();
        NodeList xmlTR = xmlTab.item(i).getChildNodes();
        for (int j = 0; j < xmlTR.getLength(); j++) {
          if (xmlTR.item(j).getNodeName().equals("data")) {
            TableCell tc = new TableCell();
            tc.setContent(xmlTR.item(j).getFirstChild().getNodeValue());
            tr.addCell(tc);
          }
        }
        t.addRow(tr);
      }
    }
    return t;
  }

  public static void main(String[] args) throws Exception {

    ReportManager rm = new ReportManager();

    Table t = getTableByXML();

    //定義交叉表
    HeadCol[] colH = {
        new HeadCol(2, "廠家")};
    HeadCol[] rowH = {
        new HeadCol(0, "品種"), new HeadCol(1, "市場(chǎng)")};
    Vector sort1 = new Vector();
    sort1.add("本單位");
    sort1.add("長(zhǎng)江牌");
    colH[0].setSortSeq(sort1);

    CrossCol crs = new CrossCol(3,"銷售額", new SumArithmetic());
    CrossTable crossTab = new CrossTable(colH, rowH, crs);

    t = rm.generateCrossTab(t, crossTab);

    //行統(tǒng)計(jì)

    t = rm.generateCrossTabRowTotal(t, crossTab, true,
                                    new SumArithmetic());

    //列統(tǒng)計(jì)
    t = rm.generateCrossTabColTotal(t, crossTab, false, new SumArithmetic());

    //格式化數(shù)據(jù)
    t = rm.formatData(t, crossTab, new DefaultFormatter());

    Report report = new Report();
    ReportBody body = new ReportBody();
    body.setData(t);
    report.setBody(body);
    setTitleFooter(report);

    //生成HTML格式報(bào)表
    getHTMLReport(report);
    //生成PDF格式報(bào)表
    getPDFReport(report);
    //生成CSV格式報(bào)表
    getCSVReport(report);
  }

}