?? usedbyjfree.java
字號:
package imis_elec;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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.CategoryDataset;
import org.jfree.data.DatasetUtilities;
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 UsedByJFree {
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 ElecMeterRegNo 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") + "',";
}
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,'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")) { // 按價(jià)格
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")) { // 按價(jià)格
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 HashMap getTongjiTuLastYear(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 ElecMeterRegNo 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") + "',";
}
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 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")) { // 按價(jià)格
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")) { // 按價(jià)格
sql = "select sum((nowreading - ElecMeterReading)*priceunit) as nowreading,to_char(readdate,'mm-yyyy') as readdate from Elec_Use_Info " +
"where readdate between (to_date('"+ startDate + "','yyyy-mm')) " +
"and (to_date('"+ endDate + "','yyyy-mm')) group by to_char(readdate,'mm-yyyy')";
}
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 HashMap getTongjiTu(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,'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 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")) { // 按價(jià)格
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")) { // 按價(jià)格
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')";
}
//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) {
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -