elecuseinfodaoimpl.java

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

	        		break;
	        	}
	        }
	        
	    	rs.close();
	    	pStatement.close();
	    } catch (SQLException e) {
	    	e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return flag;
	    
	}	

	public boolean insertElecUseInfo(ElecUseInfoTO elecUseInfo) throws Exception {
		
		boolean flag = false;
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    try {
	        connection = db.conn;
	        //Prepare a statement to insert a record
	        pStatement = connection.prepareStatement(CREATE_CUSTOMER_SQL);
	        
	        // 把字符串转换成 Date 对象
	        /*String strDate = elecUseInfo.getReadDate();
	        String[] arrDate = strDate.split("[-]");
	        Calendar calender = Calendar.getInstance();
	        int year = Integer.parseInt(arrDate[0]);
	        int month = Integer.parseInt(arrDate[1]) - 1;
	        int date = Integer.parseInt(arrDate[2]);
	        calender.set(year, month, date);*/
	        	        
	        // pStatement.setInt(1, elecUseInfo.getUseId());
	        pStatement.setString(1, elecUseInfo.getElecMeterRegNo());
	        pStatement.setDouble(2, elecUseInfo.getNowReading());
	        pStatement.setDouble(3, elecUseInfo.getElecMeterReading());
	        pStatement.setString(4, elecUseInfo.getReadOn());
	        pStatement.setDouble(5, elecUseInfo.getXianLoss());
	        pStatement.setDouble(6, elecUseInfo.getBianLoss());
	        pStatement.setDouble(7, elecUseInfo.getTuiBu());
	        pStatement.setDouble(8, elecUseInfo.getSumElec());
	        pStatement.setDouble(9, elecUseInfo.getPriceUnit());
	        pStatement.setString(10, elecUseInfo.getReadDate());
	        pStatement.setInt(11, elecUseInfo.getReMark());
	        pStatement.setString(12, elecUseInfo.getMemo());
	        
	        int i = pStatement.executeUpdate();
	        flag = i > 0 ? true : false;
	        
	        pStatement.close();	        
	    } catch (SQLException e) {
	    	e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return flag;
	}

	public ArrayList searchElecUseInfos(ElecUseInfoTO search, String endDate)	throws Exception {
		
	    ArrayList arrayList = new ArrayList();
	    Connection connection = null;
	    Statement statement = null;
	    ResultSet rs = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    //Build the search criterias
	    StringBuffer criteriaSql = new StringBuffer(512);
	    criteriaSql.append(SEARCH_CUSTOMER_SQL);
	    if (search.getMemo() != null ){
	    	 criteriaSql.append(" e1.UserId in ( select UserId from ElecInfo where  OrganId in ( Select organid from organ Connect by prior organid=suporganid Start with organid = " +
	                    DBUtil.fixSqlFieldValue(search.getMemo()) + " )) AND ");
	    	
	    	
	    }
	    
	    
	    if (search.getReadDate() != null && !search.getReadDate().equals("") && endDate != null && !endDate.equals("")) {
		    String[] startDate = search.getReadDate().split("-");
		    int startYear = Integer.parseInt(startDate[0]);
		    int startMonth = Integer.parseInt(startDate[1]);
		    int startDay = 1;	    
		    String start = startYear + "-" + startMonth + "-" + startDay;

		    String[] endDateArr = endDate.split("-");
		    int endYear = Integer.parseInt(endDateArr[0]);
		    int endMonth = Integer.parseInt(endDateArr[1]) - 1;
		    Calendar calendar = new GregorianCalendar(endYear, endMonth, 1);
		    calendar.add(Calendar.MONTH, 1);
		    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		    String end = format.format(calendar.getTime());

	        criteriaSql.append("ReadDate between (to_date('" +
	                DBUtil.fixSqlFieldValue(start) + "','yyyy-mm-dd')) AND (to_date('" + 
	                DBUtil.fixSqlFieldValue(end) + "','yyyy-mm-dd'))" + " AND ");
	    }
	    if (search.getElecMeterRegNo() != null) {
	        criteriaSql.append("e2.ElecMeterRegNo LIKE '%" +
	        		DBUtil.fixSqlFieldValue(search.getElecMeterRegNo()) + "%' AND ");
	    }
	    if (search.getUserId() != null) {
	    	criteriaSql.append("e1.UserId LIKE '%" + 
	    			DBUtil.fixSqlFieldValue(search.getUserId()) + "%' AND ");
	    }

	    
	    //Remove unused 'And' & 'WHERE'
	    if (criteriaSql.substring(criteriaSql.length()-5).
	            equals(" AND "))
	        criteriaSql.delete(criteriaSql.length()-5,
	                criteriaSql.length()-1);
	    if (criteriaSql.substring(criteriaSql.length()-7).
	            equals(" WHERE "))
	        criteriaSql.delete(criteriaSql.length()-7,
	                criteriaSql.length()-1);
	    
	    criteriaSql.append(" order by e1.UserId,e2.ElecMeterRegNo,e2.ReadDate");
	    
	    try {
	        connection = db.conn;
	        statement = connection.createStatement();
	        rs = statement.executeQuery(criteriaSql.toString());
	        
	        while (rs.next()) {
	        	ElecUseInfoTO elecUseInfo = new ElecUseInfoTO();
	        	elecUseInfo.setUserId(rs.getString("UserId"));
	        	elecUseInfo.setElecMeterRegNo(rs.getString("ElecMeterRegNo"));
	        	elecUseInfo.setNowReading(rs.getDouble("NowReading"));
	        	elecUseInfo.setElecMeterReading(rs.getDouble("ElecMeterReading"));
	        	elecUseInfo.setReadOn(rs.getString("ReadOn"));
	        	elecUseInfo.setXianLoss(rs.getDouble("XianLoss"));
	        	elecUseInfo.setBianLoss(rs.getDouble("BianLoss"));
	        	elecUseInfo.setTuiBu(rs.getDouble("TuiBu"));
	        	elecUseInfo.setSumElec(rs.getDouble("SumElec"));
	        	elecUseInfo.setPriceUnit(rs.getDouble("PriceUnit"));
	        	elecUseInfo.setReadDate(rs.getDate("ReadDate").toString());
	        	elecUseInfo.setReMark(rs.getInt("ReMark"));
	        	elecUseInfo.setMemo(rs.getString("Memo"));
	        	elecUseInfo.setSum(elecUseInfo.getSumElec() * elecUseInfo.getPriceUnit());
	
	            arrayList.add(elecUseInfo);
	        }
	        
	        statement.close();
	        rs.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return arrayList;
	}

	public boolean updateElecUseInfo(ElecUseInfoTO elecUseInfo) throws Exception {
		
		boolean flag = false;
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    try {
	        connection = db.conn;
	        // Prepare a statement to insert a record
	        pStatement = connection.prepareStatement(UPDATE_CUSTOMER_SQL);            
	
	        // 把字符串转换成 Date 对象
	        /*String strDate = elecUseInfo.getReadDate();
	        String[] arrDate = strDate.split("-");
	        Calendar calender = Calendar.getInstance();
	        int year = Integer.parseInt(arrDate[0]);
	        int month = Integer.parseInt(arrDate[1]) - 1;
	        int date = Integer.parseInt(arrDate[2]);
	        calender.set(year, month, date);*/
	        
	        //pStatement.setInt(1, elecUseInfo.getUseId());
	        pStatement.setDouble(1, elecUseInfo.getNowReading());
	        pStatement.setDouble(2, elecUseInfo.getElecMeterReading());
	        pStatement.setString(3, elecUseInfo.getReadOn());
	        pStatement.setDouble(4, elecUseInfo.getXianLoss());
	        pStatement.setDouble(5, elecUseInfo.getBianLoss());
	        pStatement.setDouble(6, elecUseInfo.getTuiBu());
	        pStatement.setDouble(7, elecUseInfo.getSumElec());
	        pStatement.setDouble(8, elecUseInfo.getPriceUnit());
	        pStatement.setString(9, elecUseInfo.getReadDate());
	        pStatement.setInt(10, elecUseInfo.getReMark());
	        pStatement.setString(11, elecUseInfo.getMemo());
	        pStatement.setInt(12, elecUseInfo.getUseId());
	        
	        int i = pStatement.executeUpdate();
	        flag = i > 0 ? true : false;
	        pStatement.close();
	    } catch (SQLException e) {
	    	e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return flag;
	}

}