treedemo.java

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

package imis_elec;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;

import database.DBPoolManager;

public class TreeDemo {
	
	//	 得到指定电表编号的倍率
	public int getElecMulties(String elecMeterRegNo) {
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
        int elecMulties    = -1;
        
		try {				
			String    sql  = "select ElecMulties from ElecMeter_info where ElecMeterRegNo='" + elecMeterRegNo + "'";
			Statement stmt = db.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);
			
			if (rest.next()) {
				elecMulties= rest.getInt("ElecMulties");
			}			
			
			rest.close();
			stmt.close();	
		} catch (SQLException ex) {
			//System.err.println("Get OrganTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} finally {
			db.freeConnection();
		}	
		
		return elecMulties;
	}
	
	//	 得到指定户号的地址
	public String getUserAddr(String userId) {
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
        String    userAddr    = null;
        
		try {				
			String    sql  = "select addr from ElecInfo where UserId='" + userId + "'";
			Statement stmt = db.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);
			
			if (rest.next()) {
				userAddr= rest.getString("addr");
			}			
			
			rest.close();
			stmt.close();	
		} catch (SQLException ex) {
			//System.err.println("Get OrganTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} finally {
			db.freeConnection();
		}	
		
		return userAddr;
	}
	
	//	 得到用户
	public ArrayList getElecInfoUser() {
		ArrayList list = new ArrayList();
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
		
		try {			
			String    s    = null;
			String    sql  = "select userId from ElecInfo order by userId Desc";
			Statement stmt = db.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);

			Hashtable ht = new Hashtable();
			ht.put("OrganID", "1");
			ht.put("SupOrganID", "0");
			ht.put("OrganName", "用户编号");
			list.add(ht);
			int i = 11;
			
			while (rest.next()) {
				Hashtable hash = new Hashtable();
				s = rest.getString("UserId");
			 	s = s.trim();

				hash.put("OrganID", String.valueOf(i));
				hash.put("SupOrganID", "1");
				hash.put("OrganName", s);
										
				list.add(hash);
				i++;
			}			
			
			rest.close();
			stmt.close();	
		} catch (SQLException ex) {
			//System.err.println("Get OrganTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} finally {
			db.freeConnection();
		}	
		
		return list;
	}
	
//	 得到用户	
	public ArrayList getElecInfoUser(String OrganIDOfUser) {
		ArrayList list = new ArrayList();
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
		
       
		try {			
			String    s    = null;
			String    sql  = "select userId from ElecInfo where OrganId in (  Select organid from organ Connect by prior organid=suporganid Start with organid = "  + OrganIDOfUser + " ) order by userId Desc";
			Statement stmt = db.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);

			Hashtable ht = new Hashtable();
			ht.put("OrganID", "1");
			ht.put("SupOrganID", "0");
			ht.put("OrganName", "用户编号");
			list.add(ht);
			int i = 11;
			
			while (rest.next()) {
				Hashtable hash = new Hashtable();
				s = rest.getString("UserId");
			 	s = s.trim();

				hash.put("OrganID", String.valueOf(i));
				hash.put("SupOrganID", "1");
				hash.put("OrganName", s);
										
				list.add(hash);
				i++;
			}			
			
			rest.close();
			stmt.close();	
		} catch (SQLException ex) {
			//System.err.println("Get OrganTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} finally {
			db.freeConnection();
		}	
		
		return list;
	}
		
	
	
	//	 得到指定用户下的所有电表编号及相关数据
	public ArrayList getElecMeterRegNo(String userId) {
		ArrayList list = new ArrayList();
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
        ElecUseInfoTO elecUseInfoTO = null;
		
		try {			
			String    elecMeterRegNo = null;
			String    sql  = "select ElecMeterRegNo , ElecMeterName from ElecMeter_Info where UserId='" + userId + "' order by ElecMeterRegNo";
			Statement stmt = db.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);
			ElecUseInfoDAOImpl elecUseInfoDAO = (ElecUseInfoDAOImpl) DAOFactory.getInstance().getElecUseInfoDAO();
			
			while (rest.next()) {
				
				//if (rest.getString("ElecMeterName")!=null)				
				    //elecMeterRegNo = rest.getString("ElecMeterRegNo") + rest.getString("ElecMeterName") ;
				//else
				
				
			    elecMeterRegNo = rest.getString("ElecMeterRegNo");
				
				elecMeterRegNo = elecMeterRegNo.trim();
				elecUseInfoTO = elecUseInfoDAO.getElecUseInfo(elecMeterRegNo, false);
				
				ElecUseInfoTO bean = new ElecUseInfoTO();
				if(elecUseInfoTO == null) {
					elecUseInfoTO = new ElecUseInfoTO();
				}
				    
					bean.setElecMeterRegNo(elecMeterRegNo);
					if (rest.getString("ElecMeterName")!=null)
					    bean.setElecMeterName(rest.getString("ElecMeterRegNo") + rest.getString("ElecMeterName"));
					else
						bean.setElecMeterName(" ");
					bean.setElecMeterReading(elecUseInfoTO.getNowReading());
					bean.setPriceUnit(Double.parseDouble(this.getPriceForTable(elecMeterRegNo)));

				list.add(bean);
			}			
			
			rest.close();
			stmt.close();	
		} catch (SQLException ex) {
			System.err.println("Get OrganTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		finally {
			db.freeConnection();
		}	
		
		return list;
	}
	
	//	 得到用户和该用户下的所有电表编号
	public ArrayList getElecInfoUserAndElecMeterRegNo(String isInsert) {
		ArrayList list = new ArrayList();
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
        
        String organName = "";
        if(isInsert.equals("no")) {
        	organName = "户号和户号下的电表编号";
        } else {
        	organName = "所有用户的户号";
        }
		
		try {			
			String    s    = null;
			String	  addr = null;
			String    sql  = "select userId from ElecInfo order by userId Desc";
			Statement stmt = db.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);

			Hashtable ht = new Hashtable();
			ht.put("OrganID", "1");
			ht.put("SupOrganID", "0");
			ht.put("OrganName", organName);
			ht.put("SupName", "null");

			list.add(ht);
			int i = 11;
			
			while (rest.next()) {
				Hashtable hash = new Hashtable();
				s = rest.getString("UserId");
			 	s = s.trim();

				hash.put("OrganID", String.valueOf(i));
				hash.put("SupOrganID", "1");
				hash.put("OrganName", s);
				hash.put("SupName", s);
				
				// 得到电表编号
				if(isInsert.equals("no")) {
					String    sNo    = null;
					String    sqlNo  = "select ElecMeterRegNo from ElecMeter_Info where UserId='" + s + "' order by ElecMeterRegNo Desc";
					Statement stmtNo = db.conn.createStatement();
					ResultSet restNo = stmtNo.executeQuery(sqlNo);
					int iUser = 1;
					while (restNo.next()) {
						Hashtable hashUser = new Hashtable();
						sNo = restNo.getString("ElecMeterRegNo");
					 	sNo = sNo.trim();
		
						hashUser.put("OrganID", String.valueOf(i * 10 + iUser));
						hashUser.put("SupOrganID", i);
						hashUser.put("OrganName", sNo);
						hashUser.put("SupName", s);				
						
						//System.out.println("UserID = " + sUser);
								
						list.add(hashUser);		
						iUser++;
					}
					restNo.close();
					stmtNo.close();	
				}
						
				list.add(hash);
				i++;
			}			
			
			rest.close();
			stmt.close();	
		} catch (SQLException ex) {
			//System.err.println("Get OrganTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} finally {
			db.freeConnection();
		}	
		
		return list;
	}
	
	
	public ArrayList getElecInfoUserAndElecMeterRegNo(String isInsert , String organofuser) {
		ArrayList list = new ArrayList();
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
        
        String organName = "";
        if(isInsert.equals("no")) {
        	organName = "户号和户号下的电表编号";
        } else {
        	organName = "所有用户的户号";
        }
		
		try {			
			String    s    = null;
			String	  addr = null;
			String    sql  = "select userId from ElecInfo where  OrganId in (  Select organid from organ Connect by prior organid=suporganid Start with organid = "  + organofuser + " )  order by userId Desc";
			Statement stmt = db.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);

			Hashtable ht = new Hashtable();
			ht.put("OrganID", "1");
			ht.put("SupOrganID", "0");
			ht.put("OrganName", organName);
			ht.put("SupName", "null");

			list.add(ht);
			int i = 11;
			
			while (rest.next()) {
				Hashtable hash = new Hashtable();
				s = rest.getString("UserId");
			 	s = s.trim();

				hash.put("OrganID", String.valueOf(i));
				hash.put("SupOrganID", "1");
				hash.put("OrganName", s);
				hash.put("SupName", s);
				
				// 得到电表编号
				if(isInsert.equals("no")) {
					String    sNo    = null;
					String    sqlNo  = "select ElecMeterRegNo from ElecMeter_Info where UserId='" + s + "' order by ElecMeterRegNo Desc";
					Statement stmtNo = db.conn.createStatement();
					ResultSet restNo = stmtNo.executeQuery(sqlNo);
					int iUser = 1;
					while (restNo.next()) {
						Hashtable hashUser = new Hashtable();
						sNo = restNo.getString("ElecMeterRegNo");
					 	sNo = sNo.trim();
		
						hashUser.put("OrganID", String.valueOf(i * 10 + iUser));
						hashUser.put("SupOrganID", i);
						hashUser.put("OrganName", sNo);
						hashUser.put("SupName", s);				
						
						//System.out.println("UserID = " + sUser);
								
						list.add(hashUser);		
						iUser++;
					}
					restNo.close();
					stmtNo.close();	
				}
						
				list.add(hash);
				i++;
			}			
			
			rest.close();
			stmt.close();	
		} catch (SQLException ex) {
			//System.err.println("Get OrganTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} finally {
			db.freeConnection();
		}	
		
		return list;
	}
		
	
	
	// 得到地址和该地址下的所有电表编号
	public ArrayList getElecInfoAddrAndElecMeterRegNo(String isInsert) {
		
		ArrayList list = new ArrayList();
        DBPoolManager db = new DBPoolManager();
        db.getConnection();
        
        try {	        
			String    sAddr    = null;
			String    sqlAddr  = "select distinct(addr) from ElecInfo order by addr";
			Statement stmt = db.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sqlAddr);
	
			Hashtable htAddr = new Hashtable();
			htAddr.put("OrganID", "1");
			htAddr.put("SupOrganID", "0");
			htAddr.put("OrganName", "所有地址");
			list.add(htAddr);
			int i = 11;
			
			while (rest.next()) {
				Hashtable hash = new Hashtable();
				sAddr = rest.getString("addr");
				
				if(sAddr == null || sAddr.equals("")) {
					continue;
				}
				sAddr = sAddr.trim();
	
				hash.put("OrganID", String.valueOf(i));
				hash.put("SupOrganID", "1");
				hash.put("OrganName", sAddr);
				
				// 得到地址下的户号
				String    sUserID    = null;
				String    sqlUserID  = "select userId from ElecInfo where addr='" + sAddr + "' order by userId Desc";
				Statement stmtUserID = db.conn.createStatement();
				ResultSet restUserID = stmtUserID.executeQuery(sqlUserID);
				int iUser = 1;
				while (restUserID.next()) {
					sUserID = restUserID.getString("userId");
				 	sUserID = sUserID.trim();
				 	
					// 得到电表编号
				 	if(isInsert.equals("yes")) {
						Hashtable hashNo = new Hashtable();
		
						hashNo.put("OrganID", String.valueOf(i * 10 + iUser));
						hashNo.put("SupOrganID", i);
						hashNo.put("OrganName", sUserID);
						list.add(hashNo);		
				 	} else {
						String    sNo    = null;
						String    sqlNo  = "select ElecMeterRegNo from ElecMeter_Info where UserId='" + sUserID + "' order by ElecMeterRegNo Desc";
						Statement stmtNo = db.conn.createStatement();
						ResultSet restNo = stmtNo.executeQuery(sqlNo);
						while (restNo.next()) {
							Hashtable hashNo = new Hashtable();
							sNo = restNo.getString("ElecMeterRegNo");
						 	sNo = sNo.trim();
			
							hashNo.put("OrganID", String.valueOf(i * 10 + iUser));
							hashNo.put("SupOrganID", i);
							hashNo.put("OrganName", sNo);
									
							list.add(hashNo);		
							iUser++;
						}
						restNo.close();
						stmtNo.close();
				 	}
		
					iUser++;
				}
				restUserID.close();
				stmtUserID.close();								
						
				list.add(hash);
				i++;
			}
			
			rest.close();
			stmt.close();
		} catch (SQLException ex) {
			//System.err.println("Get OrganTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			db.freeConnection();
		}
		
		return list;