outjdbcdao.java

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

package teleReport.persistence.jdbcdao;

import java.io.OutputStream;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import com.ibatis.dao.client.DaoManager;
import commons.persistence.BaseJdbcDAO;   
import teleReport.domain.OutInfo;
import teleReport.domain.OutType;
import teleReport.domain.Product;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import teleReport.persistence.iface.OutDAO;

/** 
 * application name:OutJdbcDAO
 * application describing:数据库关联类
 * Copyright：Copyright 2008 国软 05级Java实训部版权所有。
 * company:ISS
 * time:2008.07.12
 * @author 喻才
 * @version 
 */ 

public class OutJdbcDAO extends BaseJdbcDAO implements OutDAO {

	public OutJdbcDAO(DaoManager daoManager) {
		super(daoManager);
	}
	
	//录入者查询信息
	public List<OutInfo> getOutInputer (OutInfo outInfo) {
		
		Long outTypeId = Long.parseLong(outInfo.getOutTypeId());	//查询的出账类型
		Long prodId = Long.parseLong(outInfo.getProdId());	//查询的产品号
		String out_date = outInfo.getOut_date();			//查询的录入日期
		
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<OutInfo> resultList = new ArrayList<OutInfo>(); //存放最后的出账集合
		
		try{
			if ( (outTypeId != -1) && (prodId != -1)){ //用户选择的出账编号和产品号均不为空时，设置查询参数
				
				String sql = "select outInfoId,out_date,out_type.outTypeName as outTypeName, "
					+"product.prodName as prodName,"
					+"fee,state,checkerId,check_time from outInfo,product,out_type "
					+"where to_char(outinfo.out_date,'yyyy-MM') = '"+ out_date.substring(0,7)+"' and outInfo.prodId = ? "
					+"and outInfo.outTypeId = ? and state = ? "
					+"and product.prodId = outInfo.prodId and out_Type.outTypeId=outInfo.outTypeId";
				pstm = getConnection().prepareStatement(sql);
				//pstm.setDate(1, Date.valueOf(out_date));
				pstm.setLong(1, prodId);
				pstm.setLong(2, outTypeId);
				pstm.setString(3, outInfo.getState());		
					
			}else if ( (outTypeId == -1) && (prodId != -1) ){ //用户选择的出账类型为空时，设置查询参数
				String sql = "select outInfoId,out_date,product.prodName as prodName, "
					+"out_type.outTypeName as outTypeName,fee,state,checkerId,check_time "
					+"from outInfo,product,out_type where  to_char(outinfo.out_date,'yyyy-MM') = '"+ out_date.substring(0,7)+"'  and outInfo.prodId = ? "
					+"and state = ? "
					+"and product.prodId = outInfo.prodId and out_type.outTypeId=outInfo.outTypeId ";
				pstm = getConnection().prepareStatement(sql);
				//pstm.setDate(1, Date.valueOf(out_date));				
				pstm.setLong(1, prodId);
				pstm.setString(2, outInfo.getState());
			
			}else if ( (outTypeId != -1) && (prodId == -1) ){ //用户选择的产品号为空时，设置查询参数
				String sql = "select outInfoId,out_date,product.prodName as prodName, "
					+"out_type.outTypeName as outTypeName,fee,state,checkerId,check_time "
					+"from outInfo,product,out_type where  to_char(outinfo.out_date,'yyyy-MM') = '"+ out_date.substring(0,7)+"'  "
					+"and outInfo.outTypeId = ? and state = ? "
					+"and product.prodId = outInfo.prodId and out_type.outTypeId=outInfo.outTypeId";
				pstm = getConnection().prepareStatement(sql);
				//pstm.setDate(1, Date.valueOf(out_date));				
				pstm.setLong(1, outTypeId);
				pstm.setString(2, outInfo.getState());
			
			}else{	//用户选择的出账类型号和产品号均为空时，设置查询参数
				String sql = "select outInfoId,out_date,product.prodName as prodName,"
					+"out_type.outTypeName as outTypeName, fee,state,checkerId,check_time "
					+"from outInfo,product,out_type where   to_char(outinfo.out_date,'yyyy-MM') = '"+ out_date.substring(0,7)+"'   and state = ? "
					+"and product.prodId = outInfo.prodId "
					+"and out_Type.outTypeId=outInfo.outTypeId" ;
				pstm = getConnection().prepareStatement(sql);
				//pstm.setDate(1, Date.valueOf(out_date));
				pstm.setString(1, outInfo.getState());
				
			}	
			
			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()){
				OutInfo findOut = new OutInfo();	//新建对象来存放查询的数据
				findOut.setOutInfoId(rs.getString("outInfoId"));
				findOut.setOut_date(rs.getDate("out_date").toString());			
				findOut.setFee(rs.getString("fee"));
				findOut.setState(rs.getString("state"));
				findOut.setCheckerId(rs.getString("checkerId"));
				findOut.setCheck_time(rs.getString("check_time"));
				findOut.setOutTypeName(rs.getString("outTypeName"));
				findOut.setProdName(rs.getString("prodName"));				
				resultList.add(findOut);		//将对象存放到集合中	
				
				//将数据写到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 (Exception e)
		{
			dealException(e);
		} finally
		{
			close(rs, pstm);
		}		
		return resultList;	//返回对象的集合
	}

			
	//录入者录入信息
	public Long insertOut(OutInfo outInfo) {
		String seqSql = "select SEQ_OUTINFOID.nextval as outInfoId from dual";	//从sequence中获取通知单编号作为主键
		String sql = "INSERT INTO OUTINFO (OUTINFOID,OUT_DATE,PRODID,OUTTYPEID,FEE,STATE,CHECKERID,CHECK_TIME) VALUES(?,?,?,?,?,?,?,?)";
		//执行插入操作的语句
		PreparedStatement pstm = null;
		ResultSet rs = null;			//存放查询结果
		Long outInfoId = null;			//第一条语句的查询结果——通知单编号

		int insertNum = 0;				//测试操作是否成功
		try
		{
			pstm = getConnection().prepareStatement(seqSql);		//获取通知单编号
			rs = pstm.executeQuery();

			while (rs.next()){
				outInfoId = rs.getLong(1);
			}
			Date outDate = Date.valueOf(outInfo.getOut_date());    	

			pstm = getConnection().prepareStatement(sql);			//设置插入数据
			pstm.setLong(1, outInfoId);
			pstm.setDate(2, outDate);
			pstm.setLong(3, Long.parseLong(outInfo.getProdId()));
			pstm.setString(4, outInfo.getOutTypeId());
			pstm.setDouble(5, Double.parseDouble(outInfo.getFee()));
			pstm.setString(6, "2");				//插入时状态为未稽核
			pstm.setString(7, null);			//插入时稽核人员id为空
			pstm.setDate(8, null);				//插入时稽核时间为空

			insertNum = pstm.executeUpdate();

		} catch (SQLException e)
		{
			dealException(e);
		} finally
		{
			close(rs, pstm);
		}
		return insertNum == 0 ? null : outInfoId;		//判断操作是否成功，成功则返回主键结果
	}


	//录入者更新出账信息，根据出账编号，更新产品号，出账类型号和金额
	public OutInfo updateOut(OutInfo outInfo){

		String sql = "update outInfo set prodId = ?, outTypeId = ?, fee = ?, state = ? where outInfoId = ?";
		String sqlResult = "select outInfoId, out_date,"
			+"product.prodName as prodName,out_type.outTypeName as outTypeName, "
			+"fee,state, checkerId,check_time from outInfo, product, out_type "
			+"where outInfo.outInfoId = ? and "
			+"product.prodId = outInfo.prodId and out_Type.outTypeId=outInfo.outTypeId";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		OutInfo findOut = new OutInfo();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, outInfo.getProdId());
			pstm.setString(2, outInfo.getOutTypeId());
			pstm.setDouble(3, Double.parseDouble(outInfo.getFee()));
			pstm.setString(4,outInfo.getState());
			pstm.setString(5, outInfo.getOutInfoId());
			pstm.executeUpdate();
			
			pstm = getConnection().prepareStatement(sqlResult);
			pstm.setString(1, outInfo.getOutInfoId());
			rs = pstm.executeQuery();
			
			while(rs.next()){
				findOut.setOutInfoId(rs.getString("outInfoId"));
				findOut.setOut_date(rs.getDate("out_date").toString());			
				findOut.setFee(Double.toString(rs.getDouble("fee")));
				findOut.setState(rs.getString("state"));
				findOut.setCheckerId(rs.getString("checkerId"));
				findOut.setCheck_time(rs.getString("check_time"));
				findOut.setOutTypeName(rs.getString("outTypeName"));
				findOut.setProdName(rs.getString("prodName"));
			}

		}catch(SQLException e){
			dealException(e);			
		}finally{
			close(rs,pstm);
		}
		
		return findOut;
	}
	
	//录入者删除出账信息
	public void deleteOut(String outInfoId){
		String sql = "delete from outInfo where outInfoId = ?";
		PreparedStatement pstm = null;
		
		try{
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, outInfoId); //设置要删除的出账编号
			pstm.executeUpdate();
		}catch(SQLException e ){
			dealException(e);
		}finally{
			close(pstm);
		}		
		return;
	}

	//稽查者更新稽核状态
	public OutInfo updateOutState(OutInfo outInfo){

		String sql = "update outInfo set checkerId=?,check_time=?,state=? where outInfoId=?";
		String sqlResult = "select outInfoId,out_date,"
			+"product.prodName as prodName,out_type.outTypeName as outTypeName, "
			+"fee,state,checkerId,check_time from outInfo,product,out_type "
			+"where outInfo.outInfoId = ? and "
			+"product.prodId = outInfo.prodId and out_type.outTypeId=outInfo.outTypeId";
		
		PreparedStatement pstm= null;
		ResultSet rs = null;
		OutInfo findOut = new OutInfo();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, outInfo.getCheckerId());
			pstm.setDate(2, Date.valueOf(outInfo.getCheck_time()));
			pstm.setString(3, outInfo.getState());
			pstm.setString(4, outInfo.getOutInfoId());
			pstm.executeUpdate();
			
			pstm = getConnection().prepareStatement(sqlResult);
			pstm.setString(1, outInfo.getOutInfoId());
			rs = pstm.executeQuery();
			
			while(rs.next()){
				findOut.setOutInfoId(rs.getString("outInfoId"));
				findOut.setOut_date(rs.getDate("out_date").toString());			
				findOut.setFee(rs.getString("fee"));
				findOut.setState(rs.getString("state"));
				findOut.setCheckerId(rs.getString("checkerId"));
				findOut.setCheck_time(rs.getString("check_time"));
				findOut.setOutTypeName(rs.getString("outTypeName"));
				findOut.setProdName(rs.getString("prodName"));
			}

		}catch(SQLException e){
			dealException(e);
		}finally{
			close(pstm);
		}		
		return findOut;
	}
	
	//查询出账类型
	public List<OutType> getOutType() {

		String sql = "select outTypeId,outTypeName from out_type";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<OutType> resultList = new ArrayList<OutType>();
		
		try
		{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();

			while (rs.next())
			{
				OutType outType = new OutType();
				outType.setOutTypeId(rs.getString("outTypeId"));
				outType.setOutTypeName(rs.getString("outTypeName"));

				resultList.add(outType);
			}
			
		} catch (SQLException e)
		{
			dealException(e);
		} finally
		{
			close(rs, pstm);
		}
		
		return resultList;
	}
	
	//查询出账产品信息
	public List<Product> getProduct() {
		
		String sql = "select prodId,prodName from product where ISUSED = 1 AND ISUNION = 1";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Product> resultList = new ArrayList<Product>();
		
		try
		{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();

			while (rs.next())
			{
				Product product = new Product();
				product.setProdId(rs.getString("prodId"));
				product.setProdName(rs.getString("prodName"));

				resultList.add(product);
			}
			
		} catch (SQLException e)
		{
			dealException(e);
		} finally
		{
			close(rs, pstm);
		}
		
		return resultList;
	}

	public OutInfo gotoModif(OutInfo outInfo) {
		String sql = "select prodId, outTypeId, fee from outInfo where outInfoId=?";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<OutInfo> resultList = new ArrayList<OutInfo>();
		try {
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, outInfo.getOutInfoId());
			rs = pstm.executeQuery();

			while (rs.next()) {

				outInfo.setProdId(rs.getString("prodId"));
				outInfo.setOutTypeId(rs.getString("outTypeId"));
				outInfo.setFee(rs.getString("fee"));
				resultList.add(outInfo);
			}

		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}
		return outInfo;
	}

	
}