usedbyjfree.java

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

			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;
	}
}