contractstatisticdal.java

sso呵呵

package com.mdcl.mocha.jlcmcc.contract.dao;

import java.sql.*;
import java.util.*;

import com.mdcl.mocha.jlcmcc.DBconnection.DBConnectionManager;
import com.mdcl.mocha.jlcmcc.contract.bean.ContractQueryBean;
import com.mdcl.mocha.jlcmcc.contract.bean.ContractStatisticBean;
import com.mdcl.mocha.jlcmcc.contract.bean.QueryCondition;
/**
 * <strong>Title : ContractStatisticDAL<br></strong>
 * <strong>Description : 实现合同统计的相关数据访问</strong><br>
 * <strong>Create on : 2007-10-11<br></strong>
 * <p>
 * <strong>Copyright (C) Mocha Software Co.,Ltd.<br></strong>
 * <p>
 * @author wangchl [e-mail]<br>
 * @version <strong>吉林移动BPM一期</strong><br>
 * <br>
 * <strong>修改历史:</strong><br>
 * 修改人		修改日期		修改描述<br>
 * -------------------------------------------<br>
 * <br>
 * <br>
 */
public class ContractStatisticDAL {

	private String conName = "idb";
	//合同查询的 结果结构
	private static String querySqlMain = new StringBuffer().append("SELECT ins.bo_instance_id as INSTANCE, ")
											.append("item1.HETONGBIANHAO, item2.HETONGMINGCHENG, item3.CHENGBANBUMEN, item4.HETONGJINE, item5.SHIYINSHIJIAN ")
											.append("from mocha_bo_instance ins ")
											.append("inner join mocha_document_body doc on doc.document_id = ins.document_id ")
											.append("inner join (")
											.append("SELECT item_value AS \"HETONGBIANHAO\", document_id ")
											.append("FROM mocha_document_content WHERE item_name = 'hetongbianhao') item1 on item1.document_id = doc.document_id ")
											.append("LEFT OUTER JOIN (")
											.append("SELECT item_value AS \"HETONGMINGCHENG\", document_id ")
											.append("FROM mocha_document_content WHERE item_name = 'hetongmingcheng') item2 ON item1.document_id = item2.document_id ")
											.append("LEFT OUTER JOIN (")
											.append("SELECT item_value AS \"CHENGBANBUMEN\", document_id ")
											.append("FROM mocha_document_content WHERE item_name = 'chengbanbumen') item3 ON item1.document_id = item3.document_id " )
											.append("LEFT OUTER JOIN (")
											.append("SELECT item_value AS \"HETONGJINE\", document_id ")
											.append("FROM mocha_document_content WHERE item_name = 'hetongjine') item4 ON item1.document_id = item4.document_id ")
											.append("LEFT OUTER JOIN (")
											.append("SELECT item_value AS \"SHIYINSHIJIAN\", document_id ")
											.append("FROM mocha_document_content WHERE item_name = 'shiyinshijian') item5 ON item1.document_id = item5.document_id ")
											.append("inner join mocha_bo_instance bo on item1.document_id=bo.document_id ")
											.append("inner join mocha_fe_doc_log_0 log on bo.process_id=log.doc_id ")
											.toString();
	//合同统计查询 结构
	private static String statisticSqlMain = new StringBuffer().append("SELECT item1.huobileixing AS \"HETONGLEIXING\", SUM(TO_NUMBER(item2.hetongjine)) AS \"HETONGJINE\", COUNT(*) AS \"HETONGSHULIANG\" ")
										.append("FROM (SELECT item_value AS \"HUOBILEIXING\", document_id ")
										.append("FROM mocha_document_content WHERE item_name = 'huobileixing') item1 ")
										.append("LEFT OUTER JOIN (")
										.append("SELECT item_value AS \"HETONGJINE\", document_id ")
										.append("FROM mocha_document_content WHERE item_name = 'hetongjine') item2 ")
										.append("ON item1.document_id = item2.document_id ")
										.append("inner join mocha_bo_instance bo on item1.document_id=bo.document_id ")
										.append("inner join mocha_fe_doc_log_0 log on bo.process_id=log.doc_id ")
										.toString();

	/**
	 * 方法描述 返回合同统计列表 实现结果集到Object的映射
	 * @param QueryCondition 查询条件 bean
	 * @return List:(ContractStatisticBean)
	 */
	public List retrieveStatisticList(QueryCondition cond){
		if(cond == null ) return null;
//		System.out.println(">>>> ContractStatisticDAL.retrieveStatisticList()");
		List resList = null;
		Connection con = null;
		Statement statement = null;
		ResultSet result;
		try {
			con = this.getConnection();
			statement = con.createStatement();
			//查询字符串
			String sql = statisticSqlMain;
			//扩展条件
			String extSql = buildExtendCondition(cond);
//			System.out.println(">><< MainSQL:" + statisticSqlMain);
//			System.out.println(">><< extSQL:" + extSql);
			if(extSql != null && !"".equals(extSql)){
				sql += extSql +"GROUP BY ITEM1.HUOBILEIXING";
			}
//			System.out.println(">><< SQL:" + sql);

			result = statement.executeQuery(sql);
			int i = 0;
			while(result.next()){
				i++;
				//如果有记录 有第条数据获得时创建 结果集
				if(i>0 && i==1){
//					System.out.println(">>查询数据<< ");
//					System.out.println("|    货币类型    |      合同金额      |      合同数据      |");
					resList = new ArrayList();
				}
				ContractStatisticBean statisticBean = new ContractStatisticBean();
				//设置 Bean 的值
				statisticBean.setMoneyType(result.getString("HETONGLEIXING"));
//				System.out.print("|" + result.getString("HETONGLEIXING"));
				statisticBean.setMoneySum(result.getString("HETONGJINE"));
//				System.out.print("|" + statisticBean.getMoneySum());
				statisticBean.setDocSum(result.getString("HETONGSHULIANG"));
//				System.out.println("|" + statisticBean.getDocSum() + "|");

				//将 Bean 添加到返回的 结果集中
				resList.add(statisticBean);
			}
//			if(resList != null)
//				System.out.println(">><< size " + resList.size());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//解放资源
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			statement = null;
			result = null;
			freeConnection(con);
		}

//		System.out.println("<<<< ContractStatisticDAL.retrieveStatisticList()");
		return resList;
	}

	/**
	 * 方法描述 按印花税类型对合同进行统计
	 * @param QueryCondition 查询条件 bean
	 * @return List:(ContractStatisticBean)
	 */
	public List retrieveStatisticByStamp(String[] stamps, QueryCondition cond){
		if(stamps == null || cond == null) return null;
//		System.out.println(">>>> ContractStatisticDAL.retrieveStatisticList()");
		List resList = null;
		Connection con = null;
		Statement statement = null;
		ResultSet result;
		String sql, extSql;
		try {
			con = this.getConnection();
			statement = con.createStatement();
			//list() itme String 和  ContractStatisticBean
			resList = new ArrayList();
			for(int index=0; index<stamps.length; index++){
				//将印花税类型添加到返回结果
				resList.add(stamps[index]);

				cond.stampType = stamps[index];
				//查询字符串
				sql = statisticSqlMain;
				//扩展条件
				extSql = buildExtendCondition(cond);

				if(extSql != null && !"".equals(extSql)){
					sql += extSql +"GROUP BY ITEM1.HUOBILEIXING";
				}

//				System.out.println(">><< SQL:" + sql);

				result = statement.executeQuery(sql);

				while(result.next()){
					ContractStatisticBean statisticBean = new ContractStatisticBean();
					//设置 Bean 的值
					statisticBean.setMoneyType(result.getString("HETONGLEIXING"));
//					System.out.print("|" + result.getString("HETONGLEIXING"));
					statisticBean.setMoneySum(result.getString("HETONGJINE"));
//					System.out.print("|" + statisticBean.getMoneySum());
					statisticBean.setDocSum(result.getString("HETONGSHULIANG"));
//					System.out.println("|" + statisticBean.getDocSum() + "|");

					//将 Bean 添加到返回的 结果集中
					resList.add(statisticBean);
				}
			}

//			if(resList != null)
//				System.out.println(">><< size " + resList.size());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally{
			//解放资源
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			statement = null;
			result = null;
			freeConnection(con);
		}

//		System.out.println("<<<< ContractStatisticDAL.retrieveStatisticList()");
		return resList;
	}

	/**
	 * 方法描述 返回合同统计列表 实现结果集到Object的映射
	 * @param QueryCondition 查询条件 bean
	 * @return List:(ContractQueryBean)
	 */
	public List retrieveContractQueryList(QueryCondition cond){
		if(cond == null) return null;
//		System.out.println(">>>> ContractStatisticDAL.retrieveContractQueryList()");
		List resList = null;
		Connection con = null;
		Statement statement = null;
		ResultSet result;
		try {
			con = this.getConnection();
			statement = con.createStatement();
			//查询字符串
			String sql = querySqlMain;
			//扩展条件
			String extSql = buildExtendCondition(cond);
//			System.out.println(">><< MainSQL:" + statisticSqlMain);
//			System.out.println(">><< extSQL:" + extSql);
			if(extSql != null && !"".equals(extSql)){
				sql += extSql;

			}

//			System.out.println(">><< SQL:" + sql);

			result = statement.executeQuery(sql);
			int i = 0;
			while(result.next()){
				i++;
				//如果有记录 有第条数据获得时创建 结果集
				if(i>0 && i==1){
//					System.out.println(">>查询数据<< ");
//					System.out.println("|    业务流程社实例ID    |       合同编号       |       合同名称       |       承办部门       |       合同金额       |       施印时间");
					resList = new ArrayList();
				}
				ContractQueryBean CQBean = new ContractQueryBean();
				//设置 Bean 的值
				CQBean.setBoInsId(result.getString("INSTANCE"));
//				System.out.print("|" + CQBean.getBoInsId());
				CQBean.setContNumber(result.getString("HETONGBIANHAO"));
//				System.out.print("|" + CQBean.getContNumber());
				CQBean.setContName(result.getString("HETONGMINGCHENG"));
//				System.out.print("|" + CQBean.getContName());
				CQBean.setUndertakeDept(result.getString("CHENGBANBUMEN"));
//				System.out.print("|" + CQBean.getUndertakeDept());
				CQBean.setContMoney(result.getString("HETONGJINE"));
//				System.out.print("|" + CQBean.getContMoney());
				CQBean.setShiyinTime(result.getString("SHIYINSHIJIAN"));
//				System.out.println("|" + CQBean.getShiyinTime() + "|");

				//将 Bean 添加到返回的 结果集中
				resList.add(CQBean);
			}
//			if(resList != null)
//				System.out.println(">><< size " + resList.size());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally{
			//解放资源
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			statement = null;
			result = null;
			freeConnection(con);
		}

//		System.out.println("<<<< ContractStatisticDAL.retrieveContractQueryList()");
		return resList;

		}

	/**
	 *
	 * 方法描述 对查询条件拼串
	 * @param QueryCondition 查询条件的主数据
	 * @return	扩展的查询条件
	 */
	private String buildExtendCondition(QueryCondition cond){
		//连接个数
		int i = 0;
		String firstTable = "cond";
		StringBuffer bufsql = new StringBuffer("item1.document_id IN( SELECT cond.document_id FROM ( ");

		//根据 承办部门 连接个数
		if(cond.chbumen != null ){
			if(i>0)
				bufsql.append("INNER JOIN ( ");

			bufsql.append("SELECT document_id FROM mocha_document_content ")
				  .append("WHERE item_name = 'chengbanbumen' AND item_value like '%"+ cond.chbumen +"%' ) ");
			//添加连接列
			if(i==0)
				bufsql.append(firstTable+" ");
			else
				bufsql.append(firstTable).append(i).append(" ")
					  .append("ON ")