📄 matebyjfree.java
字号:
package imis_mate.common;
import imis_mate.DAO.DBConnection;
import imis_mate.DAO.MaterialTypeDAO;
import imis_mate.DAO.OrganDAO;
import imis_mate.bean.MaterialChangeBean;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PiePlot;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.DefaultCategoryDataset;
import org.jfree.data.DefaultPieDataset;
import org.jfree.data.XYDataset;
import org.jfree.data.time.Day;
import org.jfree.data.time.Month;
import org.jfree.data.time.RegularTimePeriod;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;
import org.jfree.data.time.TimeSeriesDataItem;
import org.jfree.data.time.Year;
import database.DBPoolManager;
public class MateByJFree {
// public static HashMap getTongjiTu(String userId, String startDate, String endDate, String type) {
// HashMap list = new HashMap();
// DBPoolManager db = new DBPoolManager();
// db.getConnection();
// String sql ="";
// String sql1 = "";
// if(!userId.equals("all")) {
// sql1 = "select MaterialTypeNo from ElecMeter_Info where UserId='" + userId + "' order by ElecMeterRegNo Desc"; // 户号下的电表编号
// } else {
// sql1 = "select ElecMeterRegNo from ElecMeter_Info order by ElecMeterRegNo Desc"; // 户号下的电表编号
// }
// String no = "";
//
// try {
// String s = null;
// Statement stmt = db.conn.createStatement();
// ResultSet rest = stmt.executeQuery(sql1);
//
// while (rest.next()) {
// no = no + "'" + rest.getString("ElecMeterRegNo") + "',";
// }
// no = no.substring(0, no.length() - 1);
//
// rest.close();
//
// if(type.equals("1") && !userId.equals("all")) { // 按电量
// sql = "select sum(nowreading - ElecMeterReading) as nowreading,to_char(readdate,'yyyy-mm') as readdate from Elec_Use_Info " +
// "where elecmeterregno in (" + no + ") and readdate between (to_date('"+ startDate + "','yyyy-mm-dd')) " +
// "and (to_date('"+ endDate + "','yyyy-mm-dd')) group by to_char(readdate,'yyyy-mm')";
// } else if(type.equals("1") && userId.equals("all")) { // 按电量
// sql = "select sum(nowreading - ElecMeterReading) as nowreading,to_char(readdate,'yyyy-mm') as readdate from Elec_Use_Info " +
// "where readdate between (to_date('"+ startDate + "','yyyy-mm-dd')) " +
// "and (to_date('"+ endDate + "','yyyy-mm-dd')) group by to_char(readdate,'yyyy-mm')";
// } else if(type.equals("2") && !userId.equals("all")) { // 按价格
// sql = "select sum((nowreading - ElecMeterReading)*priceunit) as nowreading,to_char(readdate,'yyyy-mm') as readdate from Elec_Use_Info " +
// "where elecmeterregno in (" + no + ") and readdate between (to_date('"+ startDate + "','yyyy-mm-dd')) " +
// "and (to_date('"+ endDate + "','yyyy-mm-dd')) group by to_char(readdate,'yyyy-mm')";
// } else if(type.equals("2") && userId.equals("all")) { // 按价格
// sql = "select sum((nowreading - ElecMeterReading)*priceunit) as nowreading,to_char(readdate,'yyyy-mm') as readdate from Elec_Use_Info " +
// "where readdate between (to_date('"+ startDate + "','yyyy-mm-dd')) " +
// "and (to_date('"+ endDate + "','yyyy-mm-dd')) group by to_char(readdate,'yyyy-mm')";
// }
//
// Statement stmt1 = db.conn.createStatement();
//
// ResultSet rest1 = stmt1.executeQuery(sql);
// while (rest1.next()) {
// list.put(rest1.getString("readdate"), rest1.getFloat("nowreading"));
// }
// rest1.close();
// stmt1.close();
// stmt.close();
//
// } catch (SQLException ex) {
// System.err.println("Get OrganTree SQLException: " + ex.toString());
// ex.printStackTrace();
// } finally {
// db.freeConnection();
// }
//
// return list;
// }
public static JFreeChart getchart(HashMap hash,int a,String title, String y){
JFreeChart chart=null;
int length = hash.size();
String []deptname = new String[length]; //日期数组
float []deptarea = new float[length]; //数值数组
//
Iterator areaPieKey = hash.keySet().iterator();//键迭代
Iterator areaPieValue = hash.values().iterator();//值迭代
int i=0;
while(areaPieKey.hasNext()){
deptname[i]=(String)areaPieKey.next();
i++;
}
Arrays.sort(deptname);
for(int j = 0; j < deptname.length; j++) {
deptarea[j] = Float.parseFloat(hash.get(deptname[j]).toString());
}
/*i=0;
while(areaPieValue.hasNext()){
deptarea[i]=Float.parseFloat(areaPieValue.next().toString());
i++;
}*/
if(a==1){
DefaultPieDataset data = new DefaultPieDataset();
for(int j=0;j<length;j++ )
data.setValue(deptname[j],deptarea[j]);
PiePlot plot = new PiePlot(data);
chart = new JFreeChart("",JFreeChart.DEFAULT_TITLE_FONT, plot, true);
chart.setBackgroundPaint(java.awt.Color.white); //可选，设置图片背景色
chart.setTitle(title); //可选，设置图片标题
}
if(a==2){
DefaultCategoryDataset dataset = new DefaultCategoryDataset();
for(int j=0;j<length;j++ ){
dataset.addValue(deptarea[j],"",deptname[j]);
}
chart = ChartFactory.createBarChart3D(
title, // 图表标题
"日期", // 目录轴的显示标签
y, // 数值轴的显示标签
dataset, // 数据集
PlotOrientation.VERTICAL, // 图表方向：水平、垂直
false, // 是否显示图例(对于简单的柱状图必须是false)
false, // 是否生成工具
false // 是否生成URL链接
);
}
if(a==3) {
TimeSeries time = new TimeSeries("用户");
if(length != 0) {
int startYear = Integer.parseInt(deptname[0].split("-")[0]);
int startMonth = Integer.parseInt(deptname[0].split("-")[1]);
int endYear = Integer.parseInt(deptname[length - 1].split("-")[0]);
int endMonth = Integer.parseInt(deptname[length - 1].split("-")[1]);
for(int k = 0; k < deptname.length; k++) {
int year = Integer.parseInt(deptname[k].split("-")[0]);
int mon = Integer.parseInt(deptname[k].split("-")[1]);
time.add(new TimeSeriesDataItem(new Day(1,mon+1,year),new Double(deptarea[k])));
}
}
TimeSeriesCollection dataset = new TimeSeriesCollection();
dataset.addSeries(time);
chart = ChartFactory.createTimeSeriesChart(
title,
"日期",
y,
dataset,
false,
false,
false);
}
return chart;
}
public static HashMap getTongjiTu(String materialTypeNo, String startDate, String endDate, String type) {
// TODO Auto-generated method stub
HashMap list = new HashMap();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = null;
MaterialTypeDAO mateTypeDAO = new MaterialTypeDAO();
if(type.equals("1") && materialTypeNo.equals("all")) {
sql = "SELECT SUM(Material_Buy_Detail.InCount) as nowreading, to_char(InDate,'yyyy-mm') as readdate " +
"FROM Material_Buy_Detail,Material_Buy_Table " +
"WHERE Material_Buy_Detail.BuyNo = Material_Buy_Table.BuyNo AND " +
// "Material_Buy_Table.ApplyDep = "+applyDep+" AND " +
"Material_Buy_Table.InDate BETWEEN TO_DATE('"+startDate+"','YYYY-MM-DD') AND TO_DATE('"+endDate+"','YYYY-MM-DD') " +
"GROUP by to_char(InDate,'yyyy-mm')";
} else if(type.equals("1")) {
sql = "SELECT SUM(Material_Buy_Detail.InCount) as nowreading, to_char(InDate,'yyyy-mm') as readdate " +
"FROM Material_Buy_Detail,Material_Buy_Table " +
"WHERE Material_Buy_Detail.BuyNo = Material_Buy_Table.BuyNo AND " +
// "Material_Buy_Table.ApplyDep = "+applyDep+" AND " +
"Material_Buy_Table.InDate BETWEEN TO_DATE('"+startDate+"','YYYY-MM-DD') AND TO_DATE('"+endDate+"','YYYY-MM-DD') AND " +
// "Material_Buy_Detail.materialTypeNo = '"+materialTypeNo+"' " +
"Material_Buy_Detail.materialTypeNo IN "+mateTypeDAO.getMaterialSQL(materialTypeNo)+" "+
"GROUP by to_char(InDate,'yyyy-mm')";
}else if(type.equals("2") && materialTypeNo.equals("all")) {
sql = "SELECT SUM(Material_Buy_Detail.PriceUnit) as nowreading, to_char(InDate,'yyyy-mm') as readdate " +
"FROM Material_Buy_Detail,Material_Buy_Table " +
"WHERE Material_Buy_Detail.BuyNo = Material_Buy_Table.BuyNo AND " +
// "Material_Buy_Table.ApplyDep = "+applyDep+" AND " +
"Material_Buy_Table.InDate BETWEEN TO_DATE('"+startDate+"','YYYY-MM-DD') AND TO_DATE('"+endDate+"','YYYY-MM-DD') " +
"GROUP by to_char(InDate,'yyyy-mm')";
}else if(type.equals("2")) {
sql = "SELECT SUM(Material_Buy_Detail.PriceUnit) as nowreading, to_char(InDate,'yyyy-mm') as readdate " +
"FROM Material_Buy_Detail,Material_Buy_Table " +
"WHERE Material_Buy_Detail.BuyNo = Material_Buy_Table.BuyNo AND " +
// "Material_Buy_Table.ApplyDep = "+applyDep+" AND " +
"Material_Buy_Table.InDate BETWEEN TO_DATE('"+startDate+"','YYYY-MM-DD') AND TO_DATE('"+endDate+"','YYYY-MM-DD')AND " +
// "Material_Buy_Detail.materialTypeNo = '"+materialTypeNo+"' " +
"Material_Buy_Detail.materialTypeNo IN "+mateTypeDAO.getMaterialSQL(materialTypeNo)+" "+
"GROUP by to_char(InDate,'yyyy-mm')";
}
// System.out.println(sql);
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()) {
list.put(rs.getString("readdate"), rs.getFloat("nowreading"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.close(rs);
DBConnection.close(stmt);
DBConnection.close(conn);
}
return list;
}
public static HashMap getOutTongjiTu(String materialTypeNo, String startDate, String endDate, String useDep, String type) {
// 领用统计
HashMap list = new HashMap();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = null;
OrganDAO organDAO = new OrganDAO();
MaterialTypeDAO mateTypeDAO = new MaterialTypeDAO();
if(type.equals("1") && materialTypeNo.equals("all")) {
sql="SELECT SUM(OutCount) AS nowreading, TO_CHAR(OutDate,'YYYY-MM') AS readdate " +
"FROM Material_Out_Table,Material_Out_Detail,organ " +
"WHERE Material_Out_Table.Outno = Material_Out_Detail.Outno " +
"AND Material_Out_Table.UseDep = organ.organid " +
//"AND UseDep = " + useDep + "or organ.SupOrganID = " + useDep +
"AND UseDep IN "+organDAO.getOrganSQL(Integer.parseInt(useDep),"useDep")+" "+
"AND OutDate BETWEEN TO_DATE('"+startDate+"','YYYY-MM-DD') AND TO_DATE('"+endDate+"','YYYY-MM-DD') " +
"GROUP BY TO_CHAR(OutDate,'YYYY-MM')";
} else if(type.equals("1")) {
sql="SELECT SUM(OutCount) AS nowreading, TO_CHAR(OutDate,'YYYY-MM') AS readdate " +
"FROM Material_Out_Table,Material_Out_Detail,organ " +
"WHERE Material_Out_Table.Outno = Material_Out_Detail.Outno " +
"AND Material_Out_Table.UseDep = organ.organid " +
// "AND UseDep = " + useDep + "or organ.SupOrganID = " + useDep +
"AND UseDep IN "+organDAO.getOrganSQL(Integer.parseInt(useDep),"useDep")+" "+
// "AND MaterialTypeNo = " + materialTypeNo +
"AND materialTypeNo IN "+mateTypeDAO.getMaterialSQL(materialTypeNo)+" "+
"AND OutDate BETWEEN TO_DATE('"+startDate+"','YYYY-MM-DD') AND TO_DATE('"+endDate+"','YYYY-MM-DD') " +
"GROUP BY TO_CHAR(OutDate,'YYYY-MM')";
}else if(type.equals("2") && materialTypeNo.equals("all")) {
sql="SELECT SUM(PriceUnit) AS nowreading, TO_CHAR(OutDate,'YYYY-MM') AS readdate " +
"FROM Material_Out_Table,Material_Out_Detail,organ " +
"WHERE Material_Out_Table.Outno = Material_Out_Detail.Outno " +
"AND Material_Out_Table.UseDep = organ.organid " +
// "AND UseDep = " + useDep + " or organ.SupOrganID = " + useDep +
"AND UseDep IN "+organDAO.getOrganSQL(Integer.parseInt(useDep),"useDep")+" "+
"AND OutDate BETWEEN TO_DATE('"+startDate+"','YYYY-MM-DD') AND TO_DATE('"+endDate+"','YYYY-MM-DD') " +
"GROUP BY TO_CHAR(OutDate,'YYYY-MM')";
}else if(type.equals("2")) {
sql="SELECT SUM(PriceUnit) AS nowreading, TO_CHAR(OutDate,'YYYY-MM') AS readdate " +
"FROM Material_Out_Table,Material_Out_Detail,organ " +
"WHERE Material_Out_Table.Outno = Material_Out_Detail.Outno " +
"AND Material_Out_Table.UseDep = organ.organid " +
// "AND UseDep = " + useDep + " or organ.SupOrganID = " + useDep +
"AND UseDep IN "+organDAO.getOrganSQL(Integer.parseInt(useDep),"useDep")+" "+
// "AND MaterialTypeNo = " + materialTypeNo +
"AND materialTypeNo IN "+mateTypeDAO.getMaterialSQL(materialTypeNo)+" "+
"AND OutDate BETWEEN TO_DATE('"+startDate+"','YYYY-MM-DD') AND TO_DATE('"+endDate+"','YYYY-MM-DD') " +
"GROUP BY TO_CHAR(OutDate,'YYYY-MM')";
}
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()) {
list.put(rs.getString("readdate"), rs.getFloat("nowreading"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.close(rs);
DBConnection.close(stmt);
DBConnection.close(conn);
}
return list;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -