datastat.java

JFreechart 饼图源代码

package com.mwq.database;

import java.sql.ResultSet;
import java.sql.SQLException;

public class DataStat extends JDBC {

	public double[] cake(String typeId, String year, String month) {
		double[] data = null;

		String sql = "select info.name+info.model,sum((sale.sale_price-info.cost_price)*sale.amount) as income from tb_merchInfo as info,tb_merchSale as sale where info.merchType_id="
				+ typeId
				+ " and sale.merchInfo_id=info.id and sale.date>='"
				+ year + "-" + month + "-1'";
		if (month.equals("12")) {
			year = (Integer.valueOf(year) + 1) + "";
			month = "1";
		} else {
			month = (Integer.valueOf(month) + 1) + "";
		}
		sql = sql + " and sale.date<'" + year + "-" + month
				+ "-1' group by info.name+info.model order by income";
		this.openConn();
		try {
			this.stmt = this.conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			this.rs = this.stmt.executeQuery(sql);
			rs.last();
			data = new double[rs.getRow()];
			rs.beforeFirst();
			int index = 0;
			while (rs.next()) {
				data[index] = rs.getInt(2);
				index++;
			}
		} catch (SQLException e) {
			System.out.println("在统计数据时抛出异常，内容如下：");
			e.printStackTrace();
		}
		this.closeConn();
		return data;
	}

	public Object[] getCutlineInfo(String typeId, String year, String month) {
		Object[] cutlines = new Object[2];
		String sql = "select info.id,info.name+'（'+info.model+'）' from tb_merchInfo as info,tb_merchSale as sale where info.merchType_id="
				+ typeId
				+ " and sale.merchInfo_id=info.id and sale.date>='"
				+ year + "-" + month + "-1'";
		if (month.equals("12")) {
			year = (Integer.valueOf(year) + 1) + "";
			month = "1";
		} else {
			month = (Integer.valueOf(month) + 1) + "";
		}
		sql += " and sale.date<'" + year + "-" + month
				+ "-1' group by info.id,info.name+'（'+info.model+'）'";
		this.openConn();
		try {
			this.stmt = this.conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			this.rs = this.stmt.executeQuery(sql);
			rs.last();
			int[] ids = new int[rs.getRow()];
			String[] names = new String[rs.getRow()];
			rs.beforeFirst();
			int index = 0;
			while (rs.next()) {
				ids[index] = rs.getInt(1);
				names[index] = rs.getString(2);
				index++;
			}
			cutlines[0] = ids;
			cutlines[1] = names;
		} catch (SQLException e) {
			System.out.println("在获得图例时抛出异常，内容如下：");
			e.printStackTrace();
		}
		this.closeConn();
		return cutlines;
	}
//
//	public Object[] select(String sql) {
//		Object[] objs = null;
//		this.openConn();
//		try {
//			this.stmt = this.conn.createStatement(
//					ResultSet.TYPE_SCROLL_INSENSITIVE,
//					ResultSet.CONCUR_READ_ONLY);
//			this.rs = stmt.executeQuery(sql);
//			rs.last();
//			int row = rs.getRow();
//			int column = rs.getMetaData().getColumnCount();
//			rs.beforeFirst();
//			objs = new Object[row];
//			if (column == 1) {
//				while (rs.next()) {
//					row--;
//					objs[row] = rs.getObject(1).toString();
//				}
//			} else {
//				String[] strs;
//				while (rs.next()) {
//					row--;
//					strs = new String[column];
//					for (int i = 0; i < column; i++) {
//						strs[i] = rs.getObject(i + 1).toString();
//					}
//					objs[row] = strs;
//				}
//			}
//		} catch (SQLException e) {
//			System.out.println("在执行Select语句检索数据时抛出异常，内容如下：");
//			e.printStackTrace();
//		}
//		this.closeConn();
//		return objs;
//	}
//
	public String[] getYear() {
		String[] years = null;
		this.openConn();
		try {
			this.stmt = this.conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			this.rs = this.stmt
					.executeQuery("select distinct(datepart(YY,date)) as dtdp from tb_merchSale order by dtdp");
			rs.last();
			years = new String[rs.getRow()];
			rs.beforeFirst();
			int index = 0;
			while (rs.next()) {
				years[index] = rs.getInt(1) + "";
				index++;
			}
		} catch (SQLException e) {
			System.out.println("在获得年时抛出异常，内容如下：");
			e.printStackTrace();
		}
		this.closeConn();
		return years;
	}

	public String[] getMonth(String year) {
		String[] months = null;
		this.openConn();
		try {
			this.stmt = this.conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			this.rs = this.stmt
					.executeQuery("select distinct(datepart(MM,date)) as dtdp from tb_merchSale where date between '"
							+ year
							+ "-1-1' and '"
							+ year
							+ "-12-31' order by dtdp");
			rs.last();
			months = new String[rs.getRow()];
			rs.beforeFirst();
			int index = 0;
			while (rs.next()) {
				months[index] = rs.getInt(1) + "";
				index++;
			}
		} catch (SQLException e) {
			System.out.println("在获得月时抛出异常，内容如下：");
			e.printStackTrace();
		}
		this.closeConn();
		return months;
	}

	public String[][] getType() {
		String[][] types = null;
		this.openConn();
		try {
			this.stmt = this.conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			this.rs = this.stmt.executeQuery("select * from tb_merchType");
			rs.last();
			types = new String[rs.getRow()][rs.getMetaData().getColumnCount()];
			rs.beforeFirst();
			int index = 0;
			while (rs.next()) {
				types[index][0] = rs.getInt(1) + "";
				types[index][1] = rs.getString(2);
				index++;
			}
		} catch (SQLException e) {
			System.out.println("在获得月时抛出异常，内容如下：");
			e.printStackTrace();
		}
		this.closeConn();
		return types;
	}

//	public String[][] getMerch(String typeId) {
//		String[][] merchs = null;
//		this.openConn();
//		try {
//			this.stmt = this.conn.createStatement(
//					ResultSet.TYPE_SCROLL_INSENSITIVE,
//					ResultSet.CONCUR_READ_ONLY);
//			this.rs = this.stmt
//					.executeQuery("select id,name+'（'+model+'）' from tb_merchInfo where merchType_id="
//							+ typeId);
//			rs.last();
//			merchs = new String[rs.getRow()][rs.getMetaData().getColumnCount()];
//			rs.beforeFirst();
//			int index = 0;
//			while (rs.next()) {
//				merchs[index][0] = rs.getInt(1) + "";
//				merchs[index][1] = rs.getString(2);
//				index++;
//			}
//		} catch (SQLException e) {
//			System.out.println("在获得月时抛出异常，内容如下：");
//			e.printStackTrace();
//		}
//		this.closeConn();
//		return merchs;
//	}

}