package charts;


import java.awt.Color;
import java.awt.Font;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import javax.servlet.http.HttpSession;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartRenderingInfo;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.CategoryAxis;
import org.jfree.chart.axis.CategoryLabelPositions;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.entity.StandardEntityCollection;
import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.chart.renderer.category.CategoryItemRenderer;
import org.jfree.chart.servlet.ServletUtilities;
import org.jfree.chart.title.TextTitle;
import org.jfree.data.category.CategoryDataset;
import org.jfree.data.category.DefaultCategoryDataset;

public class DataBaseBarChart
{
	 /**
     * 根据SQL取结果集
     * @param sql
     * @return
     */
    public static ResultSet getData(String sql)
    {
        // 数据库查询
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try
        {
            // 加载数据库驱动类
            Class.forName("com.mysql.jdbc.Driver")
                    .newInstance();
            // 数据库连接URL
            String url = "jdbc:mysql://localhost:3306/pubs";
            // 数据库用户名
            String user = "root";
            // 数据库密码
            String password = "123456";
            // 根据数据库参数取得一个数据库连接
            conn = DriverManager.getConnection(url, user, password);
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
        return rs;
    }

    /**
     * 构造报表的结果集
     * 
     * @return
     */
    private static CategoryDataset createDataset()
    {
        // 创建结果集对象
        DefaultCategoryDataset dataset = new DefaultCategoryDataset();
        // 向结果集中添加数据
        String series1 = "商业银行贷存比";

        String sql = "select * from bank";
        ResultSet rs = getData(sql);
        try
        {
            while (rs.next())
            {
                float percents = rs.getFloat("percents");
                String bank = rs.getString("bank");
                dataset.addValue(percents, series1, bank);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return dataset;
    }

    /**
     * 根据结果集构造JFreechart报表对象
     * @param dataSet
     * @return
     */
    private static JFreeChart createChart(CategoryDataset dataSet)
    {
        JFreeChart jfreechart = ChartFactory.createBarChart(
                "全球WCDMA产业联盟成员构成情况", // 报表标题
                "成员", // 柱体排列轴标签
                "数量", // 柱体数值范围轴标签
                dataSet, // 结果集
                PlotOrientation.VERTICAL, // 柱状图的方向
                false, // 是否显示图例
                true, // 是否有工具条提示
                false // 是否有链接
                );
        // 设置图表背景颜色
        jfreechart.setBackgroundPaint(Color.WHITE);
        jfreechart.addSubtitle(new TextTitle("------《2008年银行业行业报告》", new Font(
                "Dialog", Font.ITALIC, 10)));
        // 取得CategoryPlot对象的引用，通过这个对象可以对图表进行具体的设置
        CategoryPlot plot = (CategoryPlot) jfreechart.getPlot();
        plot.setRangeGridlinePaint(Color.BLACK);

        // 显示柱体代表的值
        CategoryItemRenderer renderer = plot.getRenderer();
        renderer
                .setBaseItemLabelGenerator(new StandardCategoryItemLabelGenerator(
                        "{2}", java.text.NumberFormat.getPercentInstance()));
        renderer.setItemLabelsVisible(true);

        // 设置柱体的外观
        BarRenderer barrenderer = (BarRenderer) plot.getRenderer();
        barrenderer.setDrawBarOutline(true);
        barrenderer.setMaximumBarWidth(0.05);
        barrenderer.setSeriesPaint(0, Color.PINK);
        // 设置坐标
        NumberAxis rangeAxis = (NumberAxis) plot.getRangeAxis();
        rangeAxis.setUpperMargin(0.15);
        rangeAxis.setNumberFormatOverride(new DecimalFormat("0.0%"));
        CategoryAxis domainAxis = plot.getDomainAxis();
        domainAxis.setCategoryLabelPositions(CategoryLabelPositions.UP_45);
        return jfreechart;
    }

    /**
     * 生成二维柱状图的图片，返回图片文件的名称
     * @param session
     * @param pw
     * @return
     */
    public static String generateBarChart(HttpSession session, PrintWriter pw)
    {
        String filename = null;
        CategoryDataset dataset = createDataset();
        JFreeChart chart = createChart(dataset);
        ChartRenderingInfo info = new ChartRenderingInfo(
                new StandardEntityCollection());
        try
        {
            filename = ServletUtilities.saveChartAsPNG(chart, 500, 300, info,
                    session);
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        try
        {
            ChartUtilities.writeImageMap(pw, filename, info, false);
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        pw.flush();
        return filename;
    }
}