usedbyjfree.java

JSP移动商品管理平台源代码.........

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")) {		// 按价格
		        	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 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")) {		// 按价格
		        	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 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")) {		// 按价格
		        	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  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) {