prejdbcdao.java

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

package teleReport.persistence.jdbcdao;


import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;

import java.io.OutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.ibatis.dao.client.DaoManager;
import commons.persistence.BaseJdbcDAO;

import teleReport.domain.ChargeType;
import teleReport.domain.City;
import teleReport.domain.OrderInfo;
import teleReport.domain.PreInfo;
import teleReport.domain.Product;
import teleReport.persistence.iface.PreDAO;

public class PreJdbcDAO extends BaseJdbcDAO implements PreDAO {

	public PreJdbcDAO(DaoManager daoManager) {
		super(daoManager);
	}

	public Long insertPre(PreInfo preInfo) {
		String seqSql = "select SEQ_ORDERINFOID.nextval as preId from dual";
		String sql = "insert into preincome(preid,sale_time,cityId,prodId, chargeTypeId,"
				+ "money,state,checkerId,checktime) values(?,?,?,?,?,?,?,?,?)";

		PreparedStatement pstm = null;
		ResultSet rs = null;
		Long preid = null;

		int insertNum = 0;

		try {
			pstm = getConnection().prepareStatement(seqSql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				preid = rs.getLong(1);
			}

			Date sale_time = Date.valueOf(preInfo.getSale_time());

			pstm = getConnection().prepareStatement(sql);
			pstm.setLong(1, preid);
			pstm.setDate(2, sale_time);

			pstm.setLong(3, Long.parseLong(preInfo.getCityId()));
			pstm.setLong(4, Long.parseLong(preInfo.getProdId()));

			pstm.setString(5, preInfo.getChargeTypeId());
			pstm.setDouble(6, Double.parseDouble(preInfo.getMoney()));
			pstm.setString(7, "2");
			pstm.setString(8, null);
			pstm.setDate(9, null);

			insertNum = pstm.executeUpdate();
		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}
		return insertNum == 0 ? null : preid;
	}

	public List<PreInfo> getPreInfoInputer(PreInfo preInfo){

		Long cityId = Long.parseLong(preInfo.getCityId());
		Long prodId = Long.parseLong(preInfo.getProdId());
		String chargeTypeId= preInfo.getChargeTypeId();
		
		String sale_time = preInfo.getSale_time();

		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<PreInfo> resultList = new ArrayList<PreInfo>();
		boolean a1,a2,a3,a4,a5,a6,a7;
        a1=(cityId != -1) && (prodId != -1)&&(!chargeTypeId.equals("-1"));
        a2=(cityId == -1) && (prodId != -1)&&(!chargeTypeId.equals("-1"));
        a3=(cityId != -1 )&& (prodId == -1)&&(!chargeTypeId.equals("-1"));
        a4=(cityId == -1 )&& (prodId == -1)&&(!chargeTypeId.equals("-1"));
        a5=(cityId != -1 )&& (prodId != -1)&&(chargeTypeId.equals("-1"));
        a6=(cityId == -1 )&& (prodId != -1)&&(chargeTypeId.equals("-1"));
        a7=(cityId != -1 )&& (prodId == -1)&&(chargeTypeId.equals("-1"));
        


		try {
			if (a1) {
				String sql = "select preid,sale_time,city.cityName as cityName,"
						+ "product.prodName as prodName,charge_type.typeofcharge as typeofcharge, "
						+ "money,state,checkerId, checktime from Preincome,city,product,charge_type "
						+ "where preincome.sale_time=? and Preincome.cityId=? and Preincome.prodId = ? "
						+ "and Preincome.chargeTypeId = ? and state =? and city.cityId = Preincome.cityId "
						+ "and product.prodId = Preincome.prodId and charge_type.chargeTypeId=Preincome.chargeTypeId";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(sale_time));
				pstm.setLong(2, cityId);
				pstm.setLong(3, prodId);
				pstm.setString(4, preInfo.getChargeTypeId());
				pstm.setString(5, preInfo.getState());
			} else if (a2) {
				String sql = "select preid,sale_time,city.cityName as cityName,product.prodName as prodName, "
						+ "charge_type.typeofcharge as typeofcharge,money,state,checkerId,checktime "
						+ "from Preincome,city,product,charge_type where sale_time=? and Preincome.prodId = ? "
						+ "and Preincome.chargeTypeId = ? and state =?and city.cityId = Preincome.cityId "
						+ "and product.prodId = Preincome.prodId and charge_type.chargeTypeId=Preincome.chargeTypeId";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(sale_time));
				pstm.setLong(2, prodId);
				pstm.setString(3, preInfo.getChargeTypeId());
				pstm.setString(4, preInfo.getState());
			} else if (a3) {
				String sql = "select PreId,sale_time,city.cityName as cityName,product.prodName as prodName, "
						+ "charge_type.typeofcharge as typeofcharge,money,state,checkerId,checktime "
						+ "from Preincome,city,product,charge_type  where  sale_time = ? and Preincome.cityId=? "
						+ "and Preincome.chargeTypeId = ? and state =?and city.cityId = Preincome.cityId "
						+ "and product.prodId = Preincome.prodId and charge_type.chargeTypeId=Preincome.chargeTypeId";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(sale_time));
				pstm.setLong(2, cityId);
				pstm.setString(3, preInfo.getChargeTypeId());
				pstm.setString(4, preInfo.getState());
			} else if(a4){ 
				String sql = "select preid,sale_time,city.cityName as cityName,product.prodName as prodName, "
						+ "charge_type.typeofcharge as typeofcharge,money,state,checkerId,checktime "
						+ "from Preincome,city,product,charge_type where sale_time=?  and Preincome.chargeTypeId = ? "
						+ "and state =? and city.cityId = Preincome.cityId and  product.prodId = Preincome.prodId "
						+ "and charge_type.chargeTypeId=Preincome.chargeTypeId";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(sale_time));
				pstm.setString(2, preInfo.getChargeTypeId());
				pstm.setString(3, preInfo.getState());
			}
			else if(a5){
            	String sql = "select preid,sale_time,city.cityName as cityName,product.prodName as prodName, "
				     	+ "product.prodName as prodName,charge_type.typeofcharge as typeofcharge, "
						+ "money,state,checkerId, checktime from Preincome,city,product,charge_type "
						+ "where preincome.sale_time=? and Preincome.cityId=? and Preincome.prodId = ? "
						+ " and state =? and city.cityId = Preincome.cityId "
						+ "and product.prodId = Preincome.prodId and charge_type.chargeTypeId=Preincome.chargeTypeId";

		        pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(sale_time));
				pstm.setLong(2, cityId);
				pstm.setLong(3, prodId);
				pstm.setString(4, preInfo.getState());
			 

			}
			else if(a6){
				String sql = "select preid,sale_time,city.cityName as cityName,product.prodName as prodName, "
						+ "charge_type.typeofcharge as typeofcharge,money,state,checkerId,checktime "
						+ "from Preincome,city,product,charge_type where sale_time=? and Preincome.prodId = ? "
						+ "and state =?and city.cityId = Preincome.cityId "
						+ "and product.prodId = Preincome.prodId and charge_type.chargeTypeId=Preincome.chargeTypeId";

				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(sale_time));
				pstm.setLong(2, prodId);
				pstm.setString(3, preInfo.getState());

				
			}
			else if(a7){
				String sql = "select PreId,sale_time,city.cityName as cityName,product.prodName as prodName, "
						+ "charge_type.typeofcharge as typeofcharge,money,state,checkerId,checktime "
						+ "from Preincome,city,product,charge_type  where  sale_time = ? and Preincome.cityId=? "
						+ " and state =?and city.cityId = Preincome.cityId "
						+ "and product.prodId = Preincome.prodId and charge_type.chargeTypeId=Preincome.chargeTypeId";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(sale_time));
				pstm.setLong(2, cityId);
			    pstm.setString(3, preInfo.getState());

			}
			else{
				String sql = "select preid,sale_time,city.cityName as cityName,product.prodName as prodName, "
						+ "charge_type.typeofcharge as typeofcharge,money,state,checkerId,checktime "
						+ "from Preincome,city,product,charge_type where sale_time=?"
						+ "and state =? and city.cityId = Preincome.cityId and  product.prodId = Preincome.prodId "
						+ "and charge_type.chargeTypeId=Preincome.chargeTypeId";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(sale_time));
				pstm.setString(2, preInfo.getState());

			}

			rs = pstm.executeQuery();
			 String filePath="D:\\download.xls";
				File myFilePath=new File(filePath);
				if(!( myFilePath).exists())
				myFilePath.createNewFile();
				FileWriter resultFile=new FileWriter(myFilePath);
				resultFile.close();
				       //��JXL���½����ļ����������
				OutputStream outf = new FileOutputStream(filePath);
			       


			WritableWorkbook book = Workbook.createWorkbook(outf);   
			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()) {
				PreInfo findPre = new PreInfo();
				findPre.setPreid(rs.getString("preid"));
				findPre.setSale_time(rs.getDate("sale_time").toString());
				findPre.setMoney(rs.getString("money"));
				findPre.setState(rs.getString("state"));
				findPre.setCheckerId(rs.getString("checkerId"));
				findPre.setChecktime(rs.getString("checktime"));
				findPre.setCityName(rs.getString("cityName"));

				findPre.setTypeofcharge(rs.getString("typeofcharge"));

				findPre.setProdName(rs.getString("prodName"));