?? usedbyjfree.java
字號:
System.err.println("Get OrganTree SQLException: " + ex.toString());
ex.printStackTrace();
} finally {
db.freeConnection();
}
return list;
}
public static HashMap getTongjiTuLastYear(String userId, String startDate, String endDate, String type ,String organu) {
HashMap list = new HashMap();
DBPoolManager db = new DBPoolManager();
db.getConnection();
String sql ="";
String sql1 = "";
if(!userId.equals("all")) {
sql1 = "select ElecMeterRegNo from ElecMeter_Info where UserId='" + userId + "' order by ElecMeterRegNo Desc"; // 戶號下的電表編號
} else {
sql1 = "select ElecMeterRegNo from ElecMeter_Info where UserId in ( select UserId from ElecInfo where OrganId in ( Select organid from organ Connect by prior organid=suporganid Start with organid = " + organu + ")) 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") + "',";
}
if(!no.equals("")) {
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,'mm-yyyy') as readdate from Elec_Use_Info " +
"where elecmeterregno in (" + no + ") and readdate between (to_date('"+ startDate + "','yyyy-mm')) " +
"and (to_date('"+ endDate + "','yyyy-mm')) group by to_char(readdate,'mm-yyyy')";
} else if(type.equals("1") && userId.equals("all")) { // 按電量
sql = "select sum(nowreading - ElecMeterReading) as nowreading,to_char(readdate,'mm-yyyy') as readdate from Elec_Use_Info " +
"where elecmeterregno in (" + no + ") and readdate between (to_date('"+ startDate + "','yyyy-mm')) " +
"and (to_date('"+ endDate + "','yyyy-mm')) group by to_char(readdate,'mm-yyyy')";
} else if(type.equals("2") && !userId.equals("all")) { // 按價格
sql = "select sum((nowreading - ElecMeterReading)*priceunit) as nowreading,to_char(readdate,'mm-yyyy') as readdate from Elec_Use_Info " +
"where elecmeterregno in (" + no + ") and readdate between (to_date('"+ startDate + "','yyyy-mm')) " +
"and (to_date('"+ endDate + "','yyyy-mm')) group by to_char(readdate,'mm-yyyy')";
} else if(type.equals("2") && userId.equals("all")) { // 按價格
sql = "select sum((nowreading - ElecMeterReading)*priceunit) as nowreading,to_char(readdate,'mm-yyyy') as readdate from Elec_Use_Info " +
"where elecmeterregno in (" + no + ") and readdate between (to_date('"+ startDate + "','yyyy-mm')) " +
"and (to_date('"+ endDate + "','yyyy-mm')) group by to_char(readdate,'mm-yyyy')";
}
System.out.println(sql);
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, HashMap lastYear,
String startData, String endData) throws Exception {
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();
//System.out.println(deptname[i]);
i++;
}
Arrays.sort(deptname);
for(int j = 0; j < deptname.length; j++) {
deptarea[j] = Float.parseFloat(hash.get(deptname[j]).toString());
//System.out.println(deptarea[j]);
}
/*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){
if(lastYear == null) {
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鏈接
);
} else {
int lastYearLength = lastYear.size();
String []deptnameLastYear = new String[lastYearLength]; //日期數組
float []deptareaLastYear = new float[lastYearLength]; //數值數組
//
Iterator areaPieKeyLastYear = lastYear.keySet().iterator();//鍵迭代
Iterator areaPieValueLastYear = lastYear.values().iterator();//值迭代
int l=0;
while(areaPieKeyLastYear.hasNext()){
deptnameLastYear[l]=(String)areaPieKeyLastYear.next();
l++;
}
Arrays.sort(deptnameLastYear);
for(int j = 0; j < deptnameLastYear.length; j++) {
deptareaLastYear[j] = Float.parseFloat(lastYear.get(deptnameLastYear[j]).toString());
}
int startYear = Integer.parseInt(startData.split("-")[0]);
int endYear = Integer.parseInt(endData.split("-")[0]);
int years = endYear - startYear; // 一共有幾年
double[][] data = new double[years][12];
for(int j = 0; j < deptnameLastYear.length; j++) {
int month = Integer.parseInt(deptnameLastYear[j].split("-")[0]);
int year = Integer.parseInt(deptnameLastYear[j].split("-")[1]) - startYear;
for(int w = 0; w < 12; w++) {
if(month == w + 1) {
data[year][w] = deptareaLastYear[j];
}
}
}
String[] rowKeys = new String[years];
for(int j = 0; j < years; j++) {
rowKeys[j] = String.valueOf(startYear + j);
//System.out.println(rowKeys[j]);
}
String[] columnKeys = new String[]{"1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"};
CategoryDataset dataset = DatasetUtilities.createCategoryDataset(rowKeys, columnKeys, data);
chart = ChartFactory.createBarChart3D("往年同期對比",
"日期",
y,
dataset,
PlotOrientation.VERTICAL,
true,
true,
false);
}
}
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]);
//System.out.println(deptname[k]);
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;
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -