telereportnetjdbcdao.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.NetInfo;
import teleReport.domain.Product;
import teleReport.domain.NetSaler;
import teleReport.domain.NetType;
import teleReport.persistence.iface.TeleReportNetDAO;

/*
 * @description:  跟NetService相关的数据库接口,定义了底层数据库操作中要实现的各种方法
 * @author： 徐杨
 * @time:   2007.7.16
 * @version 1.0
 */

public class TeleReportNetJdbcDAO extends BaseJdbcDAO implements TeleReportNetDAO{
	
	
	public  TeleReportNetJdbcDAO(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 and isChecked = 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<NetType> getNetType(){
		String sql = "select net_type_id,net_type_name from net_type";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<NetType> resultlist = new ArrayList<NetType>();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){
				NetType findType = new NetType();
				findType.setNetTypeId(rs.getString("net_type_id"));
				findType.setNetTypeName(rs.getString("net_type_name"));
				resultlist.add(findType);
			}
		}catch (SQLException e){
			dealException(e);
		} finally{
			close(rs,pstm);
		}		
		return resultlist;
	}
	public List<NetSaler> getNetSaler(){
		String sql = "select net_saler_id,net_saler_name from net_saler";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<NetSaler> resultlist = new ArrayList<NetSaler>();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){
				NetSaler findType = new NetSaler();
				findType.setNetSalerId(rs.getString("net_saler_id"));
				findType.setNetSalerName(rs.getString("net_saler_name"));
				resultlist.add(findType);
			}
		}catch (SQLException e){
			dealException(e);
		} finally{
			close(rs,pstm);
		}		
		return resultlist;
	}
	
	
	//录入网间信息
    public Long insertNet(NetInfo netInfo){
    	String seqSql = "select SEQ_NET_INFO_ID.nextval as netInfoId from dual";
	    String sql = "insert into net_info(net_info_id,net_month,cityId,productId,"
	    	+"net_saler_id,net_type_id,net_money,state,checkerId,check_time) values(?,?,?,?,?,?,?,?,?,?)";
	
		PreparedStatement pstm = null;
		ResultSet rs = null;                //存放查询结果
		Long netInfoId = null;              //网间信息编号
		
		int insertNum  = 0;	               //测试操作是否成功
		
		try{			
			pstm = getConnection().prepareStatement(seqSql);    //获取编号
			rs = pstm.executeQuery();
			while(rs.next()){
				netInfoId = rs.getLong(1);
			}
		
			Date netMonth = Date.valueOf(netInfo.getNetMonth());
									
			pstm = getConnection().prepareStatement(sql);
			pstm.setLong(1, netInfoId);
			pstm.setDate(2, netMonth);
			pstm.setLong(3, Long.parseLong(netInfo.getCityId()));
			pstm.setLong(4, Long.parseLong(netInfo.getProductId()));
			pstm.setLong(5,Long.parseLong(netInfo.getNetSalerId()));
		    pstm.setLong(6, Long.parseLong(netInfo.getNetTypeId()));
			pstm.setLong(7, Long.parseLong(netInfo.getNetMoney()));
			pstm.setString(8, "2");
			pstm.setString(9, null);
			pstm.setDate(10,null);			
			
			System.out.println("ok`````````````````````````````");
			insertNum = pstm.executeUpdate();						
			System.out.println("ok```````````````?????????????????????????```");
		}catch(SQLException e){
			dealException(e);
		}finally
		{
			close(rs,pstm);
		}
		return insertNum == 0? null: netInfoId;	//判断操作是否成功，是则返回主键
	}
    


    //网间查询信息
    public List<NetInfo> getNetInfoInputer (NetInfo netInfo){
		
		String sql="SELECT net_info_id,net_month,city.cityName as cityName, "
		+"	product.prodName as prodName,net_type.net_Type_Name as netTypeName," 
		+"	net_saler.net_Saler_Name as netSalerName," 
		+"	net_money,state,checkerId,check_time "
		
		+"	FROM net_info,city,product,net_type,net_saler "


		+"	WHERE  city.cityId = net_info.cityId "
		+"	AND product.prodId = net_info.productId "
		+"	AND net_type.net_type_id=net_info.net_type_id "
		+"	AND net_saler.net_saler_id=net_info.net_saler_id";
		if(netInfo!=null){
			if(netInfo.getNetMonth().equals("")){
				sql=sql+" AND  net_info.net_month LIKE '%'";
			}else{
				System.out.println("~~~~~~~~~~~~~~~~~~~~~~~1~~~~~~~~~~~~~~~~~~~~!");
				sql=sql+" AND  net_info.net_month = TO_DATE('"+netInfo.getNetMonth()+"','yyyy-MM-dd')";
				
				System.out.println("~~~~~~~~~~~~~~~~~~~~~~~1~~~~~~~~~~~~~~~~~~~~!");
			}
			if(netInfo.getCityId().equals("-1")){
				sql=sql+" AND net_info.CITYID LIKE '%'";
			}else{
				sql=sql+" AND net_info.CITYID="+netInfo.getCityId();
			}
			if(netInfo.getProductId().equals("-1")){
				sql=sql+" AND net_info.ProductId LIKE '%'";
			}else{
				sql=sql+" AND net_info.ProductId="+netInfo.getProductId();
			}
			if(netInfo.getNetSalerId().equals("-1")){
				sql=sql+" AND net_info.net_saler_id LIKE '%'";
			}else{
				sql=sql+" AND net_info.net_saler_id="+netInfo.getNetSalerId();
			}
			if(netInfo.getNetTypeId().equals("-1")){
				sql=sql+" AND net_info.net_type_id LIKE '%'";
			}else{
				sql=sql+" AND net_info.net_type_id="+netInfo.getNetTypeId();
			}
			if(netInfo.getState().equals("-1")){
				sql=sql+" AND net_info.state LIKE '%'";
			}else{
				sql=sql+" AND net_info.state="+netInfo.getState();
			}
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		List<NetInfo> resultList=new ArrayList<NetInfo>();
		try{
			pstm = getConnection().prepareStatement(sql);
			
			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()){
			NetInfo findNet = new NetInfo();     //新建对象来存储数据
			String netMonth = rs.getDate("net_month").toString();
			findNet.setNetInfoId(rs.getString("net_info_id"));
			findNet.setNetMonth(netMonth);
			findNet.setMonth(netMonth.substring(5,7));
			findNet.setYear(netMonth.substring(0, 4));
			findNet.setCityName(rs.getString("cityName"));
			findNet.setProdName(rs.getString("prodName"));
			findNet.setNetTypeName(rs.getString("netTypeName"));
			findNet.setNetSalerName(rs.getString("netSalerName"));
			findNet.setNetMoney(rs.getString("net_money"));
			findNet.setState(rs.getString("state"));
			findNet.setCheckerId(rs.getString("checkerId"));
            findNet.setCheck_time(rs.getString("check_time"));
            resultList.add(findNet);
            

			//将数据写到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 NetInfo updateAllNet(NetInfo netInfo){
		String sql="update net_info set cityId=?,productId=?,net_type_id=?,net_saler_id=?,net_money = ?,state = ?  where net_info_id = ?";
		String sqlResult ="select net_info_id,net_month,city.cityName as cityName," 
	      +"product.prodName as prodName,net_type.net_Type_Name as netTypeName, "
	      +"net_saler.net_Saler_Name as netSalerName,"
	      +"net_money,state,checkerId,check_time from net_info,city,product,net_type,net_saler "
	      +"where net_info.net_Info_Id = ? and city.cityId = net_info.cityId "
	      +"and product.prodId = net_info.productId and net_type.net_Type_Id=net_info.net_Type_Id "
	      +"and net_saler.net_Saler_Id= net_info.net_saler_id";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		NetInfo findNet = new NetInfo();
		
	try{
		pstm = getConnection().prepareStatement(sql);
	    pstm.setString(1, netInfo.getCityId());
	    pstm.setString(2, netInfo.getProductId());
	    pstm.setString(3, netInfo.getNetTypeId());
	    pstm.setString(4, netInfo.getNetSalerId());
	    pstm.setString(5, netInfo.getNetMoney());
	    pstm.setString(6, netInfo.getState());
	    pstm.setString(7, netInfo.getNetInfoId());
	    pstm.executeUpdate();
	
	pstm = getConnection().prepareStatement(sqlResult);
	pstm.setString(1, netInfo.getNetInfoId());
	rs = pstm.executeQuery();
	
	while(rs.next()){
		String netMonth = rs.getDate("net_month").toString();
		findNet.setNetInfoId(rs.getString("net_info_id"));
		findNet.setNetMonth(netMonth);
		findNet.setMonth(netMonth.substring(5,7));
		findNet.setYear(netMonth.substring(0, 4));			
		findNet.setNetMoney(rs.getString("net_money"));
		findNet.setState(rs.getString("state"));
		findNet.setCheckerId(rs.getString("checkerId"));
		findNet.setCheck_time(rs.getString("check_time"));
		findNet.setCityName(rs.getString("cityName"));
		findNet.setNetTypeName(rs.getString("netTypeName"));
		findNet.setNetSalerName(rs.getString("netSalerName"));
		findNet.setProdName(rs.getString("prodName"));
		
	  }
	
	}catch(SQLException e){
		dealException(e);			
	}finally{
		close(rs,pstm);
	}
	
	return findNet;
}
			
			
		
		
		
		
		
	
	//更新网间结算信息状态，根据网间结算信息编号，更新稽核者信息，稽核时间，和稽核状态
  public NetInfo updateNetState(NetInfo netInfo){
		
	  String sql="update net_info set checkerId=?,check_time=?,state=? where net_Info_Id=?";
	  String sqlResult = "select net_info_id,net_month,city.cityName as cityName, "
		  +"product.prodName as prodName,net_type.net_Type_Name as netTypeName, "
		  +"net_saler.net_Saler_Name as netSalerName,"
		  +"net_money,state,checkerId,check_time from net_info,city,product,net_type,net_saler "
		  +"where net_info.net_Info_Id = ? and city.cityId = net_info.cityId "
		  +"and product.prodId = net_info.productId and net_type.net_type_id=net_info.net_type_id "
		  +"and net_saler.net_saler_id=net_info.net_saler_id";
			
	    PreparedStatement pstm= null;
		ResultSet rs = null;
		NetInfo findNet = new NetInfo();
		
		try{
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, netInfo.getCheckerId());
			pstm.setDate(2, Date.valueOf(netInfo.getCheck_time()));
			pstm.setString(3, netInfo.getState());
			pstm.setString(4, netInfo.getNetInfoId());
			pstm.executeUpdate();
			
			pstm = getConnection().prepareStatement(sqlResult);
			pstm.setString(1, netInfo.getNetInfoId());
			rs = pstm.executeQuery();
			
			while(rs.next()){
				findNet.setNetInfoId(rs.getString("net_info_id"));
				findNet.setNetMonth(rs.getDate("net_month").toString());			
				findNet.setNetMoney(rs.getString("net_money"));
				findNet.setState(rs.getString("state"));
				findNet.setCheckerId(rs.getString("checkerId"));
				findNet.setCheck_time(rs.getString("check_time"));
				findNet.setCityName(rs.getString("cityName"));
				findNet.setNetTypeName(rs.getString("netTypeName"));
				findNet.setNetSalerName(rs.getString("netSalerName"));
				findNet.setProdName(rs.getString("prodName"));
			}

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

//删除网间结算信息
	public void deleteNet(String netInfoId){
		String sql = "delete from net_info where net_info_id = ?";
		PreparedStatement pstm = null;
		
		try{
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, netInfoId); //设置要删除的通知单编号
			pstm.executeUpdate();
		}catch(SQLException e ){
			dealException(e);
		}finally{
			close(pstm);
		}		
		return;
	}

	public NetInfo getNetById(String netInfoId){
		String sql = "select * from net_info where net_info_id = " + netInfoId;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		NetInfo findNet = new NetInfo();
		try{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){				
				findNet.setNetMoney(rs.getString("net_money"));			
				findNet.setCityName(rs.getString("cityId"));
				findNet.setNetTypeName(rs.getString("net_type_id"));
				findNet.setNetSalerName(rs.getString("net_saler_id"));
				findNet.setProdName(rs.getString("productId"));
				findNet.setNetInfoId(rs.getString("net_info_id"));
			}
		}catch(SQLException e){
			dealException(e);
		}finally{
			close(rs,pstm);
		}
		return findNet;
	}

	
}