telereportjdbcdao.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.City;
import teleReport.domain.OrderInfo;
import teleReport.domain.OrderType;
import teleReport.domain.Product;
import teleReport.persistence.iface.TeleReportDAO;

/*
 * @description:  跟OrderService相关的数据库接口,定义了底层数据库操作中要实现的各种方法
 * @author： 朱倩
 * @time:   2007.7.16
 * @version 1.0
 */
public class TeleReportJdbcDAO extends BaseJdbcDAO implements TeleReportDAO{
	
	public TeleReportJdbcDAO(DaoManager daoManager){
		super(daoManager);
	}
	
	public List<City> getCity(){
		String sql = "select cityId, cityName from city";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<City> resultlist = new ArrayList<City>();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){
				City findCity = new City();
				findCity.setCityId(rs.getString("cityId"));
				findCity.setCityName(rs.getString("cityName"));
				resultlist.add(findCity);
			}
		}catch (SQLException e){
			dealException(e);
		} finally{
			close(rs,pstm);
		}
		
		return resultlist;
	}
	
	public List<Product> getProd(){
		String sql = "select prodId,prodName from product where isused = 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 findProd = new Product();
				findProd.setProdId(rs.getString("prodId"));
				findProd.setProdName(rs.getString("prodName"));
				resultlist.add(findProd);
			}
		}catch (SQLException e){
			dealException(e);
		} finally{
			close(rs,pstm);
		}
	
		return resultlist;
	}
	
	public List<OrderType> getOrderType(){
		String sql = "select ordertypeId,ordertypeName from order_type";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<OrderType> resultlist = new ArrayList<OrderType>();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){
				OrderType findType = new OrderType();
				findType.setOrderTypeId(rs.getString("ordertypeId"));
				findType.setOrderTypeName(rs.getString("ordertypeName"));
				resultlist.add(findType);
			}
		}catch (SQLException e){
			dealException(e);
		} finally{
			close(rs,pstm);
		}		
		return resultlist;
	}
	//录入通知单信息
	public Long insertOrder(OrderInfo orderInfo){
		String seqSql = "select SEQ_ORDERINFOID.nextval as orderInfoId from dual";	//从sequence中获取通知单编号作为主键
		String sql = "insert into orderInfo(orderInfoId,order_date,cityId,prodId,orderTypeId, "
			+"amout,state,checkerId,check_time) values(?,?,?,?,?,?,?,?,?)";
		//执行插入操作的语句
		
		PreparedStatement pstm = null;
		ResultSet rs = null;			//存放查询结果
		Long orderInfoId = null;		//第一条语句的查询结果——通知单编号
		
		int insertNum  = 0;				//测试操作是否成功
		
		try{			
			pstm = getConnection().prepareStatement(seqSql);	//获取通知单编号
			rs = pstm.executeQuery();
			while(rs.next()){
				orderInfoId = rs.getLong(1); 
			}
						
			pstm = getConnection().prepareStatement(sql);		//设置插入数据
			pstm.setLong(1, orderInfoId);
			pstm.setDate(2,	Date.valueOf(orderInfo.getOrder_date()));
			pstm.setLong(3, Long.parseLong(orderInfo.getCityId()));
			pstm.setLong(4, Long.parseLong(orderInfo.getProdId()));
			pstm.setString(5, orderInfo.getOrderTypeId());
			pstm.setDouble(6, Double.parseDouble(orderInfo.getAmount()));
			pstm.setString(7, "2"); //插入时状态为未稽核
			pstm.setString(8, null);//插入时稽核人员id为空			                  
			pstm.setDate(9,null);	//插入时稽核时间为空
			
			insertNum = pstm.executeUpdate();						
		}catch(SQLException e){
			dealException(e);
		}finally
		{
			close(rs,pstm);
		}
		return insertNum == 0? null: orderInfoId;	//判断操作是否成功，成功则返回主键结果
	}

	//查询通知单信息
	public List<OrderInfo> getOrderInfoInputer (OrderInfo orderInfo){
		
		Long cityId = Long.parseLong(orderInfo.getCityId());	//查询的城市号
		Long prodId = Long.parseLong(orderInfo.getProdId());	//查询的产品号
		String order_date = orderInfo.getOrder_date();			//查询的录入日期
		
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<OrderInfo> resultList = new ArrayList<OrderInfo>(); //存放最后的通知单集合
		
		try{
			if ( (cityId != -1) && (prodId != -1)){ //用户选择的城市号和产品号均不为空时，设置查询参数
				
				String sql = "select orderInfoId,order_date,city.cityName as cityName, "
					+"product.prodName as prodName,order_type.orderTypeName as orderTypeName, "
					+"amout,state,checkerId,check_time from orderInfo,city,product,order_type "
					+"where orderInfo.order_date=? and orderInfo.cityId=? and orderInfo.prodId = ? "
					+"and orderInfo.orderTypeId = ? and state =? and city.cityId = orderInfo.cityId "
					+"and product.prodId = orderInfo.prodId and order_Type.orderTypeId=orderInfo.orderTypeId";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(order_date));
				pstm.setLong(2, cityId);
				pstm.setLong(3, prodId);
				pstm.setString(4, orderInfo.getOrderTypeId());
				pstm.setString(5, orderInfo.getState());			
					
			}else if ( (cityId == -1) && (prodId != -1) ){ //用户选择的城市号为空时，设置查询参数
				String sql = "select orderInfoId,order_date,city.cityName as cityName,product.prodName as prodName, "
					+"order_type.orderTypeName as orderTypeName,amout,state,checkerId,check_time "
					+"from orderInfo,city,product,order_type where order_date=? and orderInfo.prodId = ? "
					+"and orderInfo.orderTypeId = ? and state =?and city.cityId = orderInfo.cityId "
					+"and product.prodId = orderInfo.prodId and order_Type.orderTypeId=orderInfo.orderTypeId ";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(order_date));				
				pstm.setLong(2, prodId);
				pstm.setString(3, orderInfo.getOrderTypeId());
				pstm.setString(4, orderInfo.getState());
			
			}else if ( (cityId != -1) && (prodId == -1) ){ //用户选择的产品号为空时，设置查询参数
				String sql = "select orderInfoId,order_date,city.cityName as cityName,product.prodName as prodName, "
					+"order_type.orderTypeName as orderTypeName,amout,state,checkerId,check_time "
					+"from orderInfo,city,product,order_type where order_date = ? and orderInfo.cityId=? "
					+"and orderInfo.orderTypeId = ? and state =?and city.cityId = orderInfo.cityId "
					+"and product.prodId = orderInfo.prodId and order_Type.orderTypeId=orderInfo.orderTypeId";
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(order_date));				
				pstm.setLong(2, cityId);
				pstm.setString(3, orderInfo.getOrderTypeId());
				pstm.setString(4, orderInfo.getState());
			
			}else{	//用户选择的城市号和产品号均为空时，设置查询参数
				String sql = "select orderInfoId,order_date,city.cityName as cityName,product.prodName as prodName, "
					+"order_type.orderTypeName as orderTypeName,amout,state,checkerId,check_time "
					+"from orderInfo,city,product,order_type where order_date=?  and orderInfo.orderTypeId = ? "
					+"and state =? and city.cityId = orderInfo.cityId and  product.prodId = orderInfo.prodId "
					+"and order_Type.orderTypeId=orderInfo.orderTypeId" ;
				pstm = getConnection().prepareStatement(sql);
				pstm.setDate(1, Date.valueOf(order_date));	
				pstm.setString(2, orderInfo.getOrderTypeId());
				pstm.setString(3, orderInfo.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 os = new FileOutputStream(filePath);
		    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()){
				OrderInfo findOrder = new OrderInfo();	//新建对象来存放查询的数据
				findOrder.setOrderInfoId(rs.getString("orderInfoId"));
				findOrder.setOrder_date(rs.getDate("order_date").toString());			
				findOrder.setAmount(rs.getString("amout"));
				findOrder.setState(rs.getString("state"));
				findOrder.setCheckerId(rs.getString("checkerId"));
				findOrder.setCheck_time(rs.getString("check_time"));
				findOrder.setCityName(rs.getString("cityName"));
				findOrder.setOrderTypeName(rs.getString("orderTypeName"));
				findOrder.setProdName(rs.getString("prodName"));				
				resultList.add(findOrder);		//将对象存放到集合中	
				
				//将数据写到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 void deleteOrder(String orderInfoId){
		String sql = "delete from orderInfo where orderInfoId = ?";
		PreparedStatement pstm = null;
		
		try{
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, orderInfoId); //设置要删除的通知单编号
			pstm.executeUpdate();
		}catch(SQLException e ){
			dealException(e);
		}finally{
			close(pstm);
		}		
		return;
	}

	public OrderInfo getOrderById(String orderInfoId){
		String sql = "select * from orderInfo where orderInfoId = " + orderInfoId;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		OrderInfo findOrder = new OrderInfo();
		try{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){				
				findOrder.setAmount(rs.getString("amout"));			
				findOrder.setCityId(rs.getString("cityId"));
				findOrder.setOrderTypeId(rs.getString("orderTypeId"));
				findOrder.setProdId(rs.getString("prodId"));
				findOrder.setOrderInfoId(rs.getString("orderInfoid"));
			}
		}catch(SQLException e){
			dealException(e);
		}finally{
			close(rs,pstm);
		}
		return findOrder;
	}
	
	//更新通知单信息，根据通知单编号，更新城市号，产品号，通知单类型号和金额
	public OrderInfo updateAllOrder(OrderInfo orderInfo){
		
		String sql = "update orderInfo set cityId=?,prodId=?,orderTypeId=?,amout = ?, state = ? where orderInfoId = ?";
		String sqlResult = "select orderInfoId,order_date,city.cityName as cityName, "
			+"product.prodName as prodName,order_type.orderTypeName as orderTypeName, "
			+"amout,state,checkerId,check_time from orderInfo,city,product,order_type "
			+"where orderInfo.orderInfoId = ? and city.cityId = orderInfo.cityId "
			+"and product.prodId = orderInfo.prodId and order_Type.orderTypeId=orderInfo.orderTypeId";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		OrderInfo findOrder = new OrderInfo();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, orderInfo.getCityId());
			pstm.setString(2, orderInfo.getProdId());
			pstm.setString(3, orderInfo.getOrderTypeId());
			pstm.setString(4, orderInfo.getAmount());
			pstm.setString(5, orderInfo.getState());
			pstm.setString(6, orderInfo.getOrderInfoId());
			pstm.executeUpdate();
			
			pstm = getConnection().prepareStatement(sqlResult);
			pstm.setString(1, orderInfo.getOrderInfoId());
			rs = pstm.executeQuery();
			
			while(rs.next()){
				findOrder.setOrderInfoId(rs.getString("orderInfoId"));
				findOrder.setOrder_date(rs.getDate("order_date").toString());			
				findOrder.setAmount(rs.getString("amout"));
				findOrder.setState(rs.getString("state"));
				findOrder.setCheckerId(rs.getString("checkerId"));
				findOrder.setCheck_time(rs.getString("check_time"));
				findOrder.setCityName(rs.getString("cityName"));
				findOrder.setOrderTypeName(rs.getString("orderTypeName"));
				findOrder.setProdName(rs.getString("prodName"));
			}

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

	//更新通知单状态，根据通知单编号，更新稽核者信息，稽核时间，和稽核状态
	public OrderInfo updateOrderState(OrderInfo orderInfo){
		
		String sql = "update orderInfo set checkerId=?,check_time=?,state=? where orderInfoId=?";
		String sqlResult = "select orderInfoId,order_date,city.cityName as cityName, "
			+"product.prodName as prodName,order_type.orderTypeName as orderTypeName, "
			+"amout,state,checkerId,check_time from orderInfo,city,product,order_type "
			+"where orderInfo.orderInfoId = ? and city.cityId = orderInfo.cityId "
			+"and product.prodId = orderInfo.prodId and order_Type.orderTypeId=orderInfo.orderTypeId";
		
		PreparedStatement pstm= null;
		ResultSet rs = null;
		OrderInfo findOrder = new OrderInfo();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, orderInfo.getCheckerId());
			pstm.setDate(2, Date.valueOf(orderInfo.getCheck_time()));
			pstm.setString(3, orderInfo.getState());
			pstm.setString(4, orderInfo.getOrderInfoId());
			pstm.executeUpdate();
			
			pstm = getConnection().prepareStatement(sqlResult);
			pstm.setString(1, orderInfo.getOrderInfoId());
			rs = pstm.executeQuery();
			
			while(rs.next()){
				findOrder.setOrderInfoId(rs.getString("orderInfoId"));
				findOrder.setOrder_date(rs.getDate("order_date").toString());			
				findOrder.setAmount(rs.getString("amout"));
				findOrder.setState(rs.getString("state"));
				findOrder.setCheckerId(rs.getString("checkerId"));
				findOrder.setCheck_time(rs.getString("check_time"));
				findOrder.setCityName(rs.getString("cityName"));
				findOrder.setOrderTypeName(rs.getString("orderTypeName"));
				findOrder.setProdName(rs.getString("prodName"));
			}

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