cardjdbcdao.java

本套系统采用了业界当前最为流行的beanAction组件

		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(pstm);
		}
		return findCardInfo;
	}

	// 更新卡销售记录状态
	public CardInfo updateCardState(CardInfo cardInfo) {
		String sql = "update cardSale set checkerId = ?, checkTime = ?, state = ? where cSaleId = ?";
		String sqlString = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
			+ "proNumber,discordV,saleDate,state,checkerId,checkTime "
			+ " FROM cardSale, city, product, personInfo "
			+ " WHERE cardSale.cSaleId = ? AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		CardInfo findCardInfo  = new CardInfo();

		try {
			pstm = getConnection().prepareStatement(sql);
			pstm.setLong(1, Long.parseLong(cardInfo.getCheckId()));
			pstm.setDate(2, Date.valueOf(cardInfo.getCheckTime()));
			pstm.setString(3, cardInfo.getState());
			pstm.setLong(4, Long.parseLong(cardInfo.getCardSaleId()));
			pstm.executeUpdate();
			
			pstm = getConnection().prepareStatement(sqlString);
			pstm.setLong(1, Long.parseLong(cardInfo.getCardSaleId()));
			rs = pstm.executeQuery();
			
			
			while (rs.next()) {
				findCardInfo.setCardSaleId(Long.toString(rs
						.getLong("cSaleId")));
				findCardInfo.setProName(rs.getString("cProdName"));
				findCardInfo.setCityName(rs.getString("cCityName"));
				findCardInfo.setSingleV(Double.toString(rs
						.getDouble("singleV")));
				findCardInfo.setProNumber(Long.toString(rs
						.getLong("proNumber")));
				findCardInfo.setDiscordV(Double.toString(rs
						.getDouble("discordV")));
				findCardInfo.setSaleDate(rs.getString("saleDate"));
				findCardInfo.setState(rs.getString("state"));
				findCardInfo.setCheckId(Long.toString(rs
						.getLong("checkerId")));
				findCardInfo.setCheckTime(rs.getString("checkTime"));
			}
		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(pstm);
		}
		return findCardInfo;
	}

	// inputer query card
	public List<CardInfo> getCardInputer(CardInfo cardInfo) {
		Long cityId = Long.parseLong(cardInfo.getCityId());
		Long proId = Long.parseLong(cardInfo.getProId());
		String saleDate = cardInfo.getSaleDate();
		String state = cardInfo.getState();
		String sql;

		System.out.println("cityId = " + cityId );
		System.out.println("proId = " + proId );
		System.out.println("state = " + state );
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<CardInfo> resultList = new ArrayList<CardInfo>();
		
		

		try {
			if(proId == -1 && cityId == -1 && state.equals("-1")){
				sql = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
					+ "proNumber,discordV,saleDate,state,checkerId,checkTime "
					+ " FROM cardSale, city, product, personInfo "
					+ " WHERE saleDate = TO_DATE(?,'YYYY-MM-DD') AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
				pstm = getConnection().prepareStatement(sql);
				
				pstm.setString(1, cardInfo.getSaleDate());
				
			
				
			}else if(cityId == -1 && state.equals("-1")){
				sql = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
					+ "proNumber,discordV,saleDate,state,checkerId,checkTime  FROM cardSale, city, product, personInfo "
					+ "WHERE cardSale.proId=?  AND saleDate = TO_DATE(?,'YYYY-MM-DD')"
					+ "AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
				pstm = getConnection().prepareStatement(sql);
				pstm.setString(1, cardInfo.getProId());
				pstm.setString(2, cardInfo.getSaleDate());
			}else if(proId == -1 && state.equals("-1")){
				sql = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
					+ "proNumber,discordV,saleDate,state,checkerId,checkTime  FROM cardSale, city, product, personInfo "
					+ "WHERE cardSale.cityId=?  AND saleDate = TO_DATE(?,'YYYY-MM-DD')"
					+ "AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
				pstm = getConnection().prepareStatement(sql);
				pstm.setString(1, cardInfo.getCityId());
				pstm.setString(2, cardInfo.getSaleDate());
			}else if(proId == -1 && cityId == -1 ){
				sql = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
					+ "proNumber,discordV,saleDate,state,checkerId,checkTime  FROM cardSale, city, product, personInfo "
					+ "WHERE  state=? AND saleDate = TO_DATE(?,'YYYY-MM-DD')"
					+ "AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
				pstm = getConnection().prepareStatement(sql);
				pstm.setString(1, cardInfo.getState()); 
				pstm.setString(2, cardInfo.getSaleDate());
			}else if(proId == -1){
				sql = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
					+ "proNumber,discordV,saleDate,state,checkerId,checkTime  FROM cardSale, city, product, personInfo "
					+ "WHERE cardSale.cityId=?  AND state=? AND saleDate = TO_DATE(?,'YYYY-MM-DD')"
					+ "AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
				pstm = getConnection().prepareStatement(sql);
				pstm.setString(1, cardInfo.getCityId());
				pstm.setString(2, cardInfo.getState()); 
				pstm.setString(3, cardInfo.getSaleDate());
			}else if(cityId == -1){
				sql = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
					+ "proNumber,discordV,saleDate,state,checkerId,checkTime  FROM cardSale, city, product, personInfo "
					+ "WHERE cardSale.proId=?  AND state=? AND saleDate = TO_DATE(?,'YYYY-MM-DD')"
					+ "AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
				pstm = getConnection().prepareStatement(sql);
				pstm.setString(1, cardInfo.getProId());
				pstm.setString(2, cardInfo.getState()); 
				pstm.setString(3, cardInfo.getSaleDate());
			}else if(state.equals("-1")){
				sql = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
					+ "proNumber,discordV,saleDate,state,checkerId,checkTime  FROM cardSale, city, product, personInfo "
					+ "WHERE cardSale.proId=? AND cardSale.cityId=?  AND saleDate = TO_DATE(?,'YYYY-MM-DD')"
					+ "AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
				pstm = getConnection().prepareStatement(sql);
				pstm.setString(1, cardInfo.getProId());
				pstm.setString(2, cardInfo.getCityId());
				pstm.setString(3, cardInfo.getSaleDate());
			}else{
				sql = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
					+ "proNumber,discordV,saleDate,state,checkerId,checkTime  FROM cardSale, city, product, personInfo "
					+ "WHERE cardSale.proId=? AND cardSale.cityId=?  AND state=? AND saleDate = TO_DATE(?,'YYYY-MM-DD')"
					+ "AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
				pstm = getConnection().prepareStatement(sql);
				pstm.setString(1, cardInfo.getProId());
				pstm.setString(2, cardInfo.getCityId());
				pstm.setString(3, cardInfo.getState()); 
				pstm.setString(4, cardInfo.getSaleDate());
			}
			// 将 string 传递给 数据库
			// pstm.setDate(3, java.sql.Date.valueOf(cardInfo.getSaleDate()));

			/*
			 * java.text.DateFormat formatter=new
			 * java.text.SimpleDateFormat("yyyy-MM-dd"); //"yyyy-MM-dd"日期格式 Date
			 * theDate=formatter.parse(cardInfo.getSaleDate());
			 * //cardInfo.getSaleDate()为string类型的日期 pstm.setDate(3, theDate);
			 */
			
		

			rs = pstm.executeQuery();

		
			
			OutputStream os = new FileOutputStream("D:\\download.xls");
		    WritableWorkbook book = Workbook.createWorkbook(os);   
		    WritableSheet sheet = book.createSheet("Sheet_1", 0);   	            	         
		    
		    int i=0;
		 	int j=0;
		 	for (int k = 0; k < rs.getMetaData().getColumnCount(); k++){
		 		sheet.addCell(new Label(k,0,rs.getMetaData().getColumnName(k+1)));
		 	}
			
		 
		 	
			while (rs.next()) {
				CardInfo cardInfo_inputer = new CardInfo();
				cardInfo_inputer.setCardSaleId(Long.toString(rs
						.getLong("cSaleId")));
				cardInfo_inputer.setProName(rs.getString("cProdName"));
				cardInfo_inputer.setCityName(rs.getString("cCityName"));
				cardInfo_inputer.setSingleV(Double.toString(rs
						.getDouble("singleV")));
				cardInfo_inputer.setProNumber(Long.toString(rs
						.getLong("proNumber")));
				cardInfo_inputer.setDiscordV(Double.toString(rs
						.getDouble("discordV")));
				cardInfo_inputer.setSaleDate(rs.getString("saleDate"));
				cardInfo_inputer.setState(rs.getString("state"));
				cardInfo_inputer.setCheckId(Long.toString(rs
						.getLong("checkerId")));
				cardInfo_inputer.setCheckTime(rs.getString("checkTime"));
				resultList.add(cardInfo_inputer);
				
				
				//将数据写到excel中
				for (int k = 0; k < rs.getMetaData().getColumnCount(); k++){
		 			sheet.addCell(new Label(k,j+i+1,rs.getString(k+1)));
		 		}  
		 		i++;
			}

			book.write();
		    book.close();   
			os.close();
		} catch (SQLException e) {
			dealException(e);
		} catch (Exception e) {
			dealException(e);
		}finally {
			close(rs, pstm);
		}
		return resultList;
	}

}