appealhisdao.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.order;

import java.util.Map;

import com.doone.data.DataTable;

public class AppealHisDAO extends AppealDAO{
	
	public AppealHisDAO(){
		super();
	}
	
	/**
	 * <code>
	 *   获取一张工单的历史记录的不同状态分类
	 * </code>
	 */
	static public DataTable getOrderMethod(Map _map, String statu)
	throws java.sql.SQLException, RuntimeException {
		
		try {
			String orderid = (String) _map.get("ORDERID");
			String userid = (String) _map.get("STAFFID");
			String starttime = (String) _map.get("STARTTIME");
			String endtime = (String) _map.get("ENDTIME");
			
			StringBuffer sql = new StringBuffer();
			if (orderid == null && orderid.equals(""))
				throw new NullPointerException("传入的流水号为空。");
			// 基本条件
			sql.append("select count(t.appealid) as count ");
			sql.append("from tf_appeal t ");
			sql.append("where t.weborder=? "); 
			
			/**
			 * 状态 statu: 0: 所有订单 1: 待处理 2: 转派 3: 暂存 4: 已作废 5: 已完成 6: 自己锁定
			 */
			if (statu != null) {
				
				if (statu.equals("1")) {
					sql
					.append(" and t.PROGRESSSTATE='1' and t.BOSOMPROGRESSSTATE='1'");
				} else if (statu.equals("2")) {
					sql
					.append(" and t.PROGRESSSTATE='2' and t.BOSOMPROGRESSSTATE='2'");
				} else if (statu.equals("3"))
					sql
					.append(" and t.PROGRESSSTATE='2' and t.BOSOMPROGRESSSTATE='3'");
				else if (statu.equals("4"))
					sql
					.append(" and t.PROGRESSSTATE='3' and t.BOSOMPROGRESSSTATE='8'");
				else if (statu.equals("5"))
					sql
					.append(" and t.PROGRESSSTATE='3' and t.BOSOMPROGRESSSTATE='9'");
				else if (statu.equals("6")) {
					sql.append(" and t.LOCKSTAFFID=");
					sql.append(userid);
				}else if (statu.equals("7")) {
					sql.append(" and t.PROGRESSSTATE='1' and t.BOSOMPROGRESSSTATE='4'");
				}else if (statu.equals("8")) {
					sql.append(" and t.PROGRESSSTATE='3' and t.BOSOMPROGRESSSTATE='A'");
				}
				
			}
			
			// 开始时间
			if (starttime != null && !starttime.equals("")) {
				sql.append(" and t.CREATETIME >= to_date('");
				sql.append(starttime);
				sql.append("','YYYY-MM-DD HH24:MI:SS')");
			}
			// 结束时间
			if (endtime != null && !endtime.equals("")) {
				sql.append(" and t.CREATETIME <= to_date('");
				sql.append(endtime);
				sql.append("','YYYY-MM-DD HH24:MI:SS')");
			}
			//System.out.println("sql \n:"+sql.toString());
			return _dac.executeQuery(sql.toString(),new Object[]{orderid});
			
		} catch (java.sql.SQLException sqlx) {
			throw sqlx;
		} catch (RuntimeException rux) {
			_logger.warn(rux.getMessage(), rux);
			throw rux;
		}
	}
	
	static public DataTable getOrderList(java.util.Map _map, int startrecord,
			int recordnum) throws java.sql.SQLException, RuntimeException {
		int endrecord = recordnum;
		if (startrecord < 0)
			startrecord = 0;
		if (endrecord > 0 && startrecord > 0)
			endrecord = startrecord + recordnum;
		
		try {
			String orderid = (String) _map.get("ORDERID");
			String userid  = (String) _map.get("STAFFID");
			String statu   = (String) _map.get("ACTION");
			String starttime = (String) _map.get("STARTTIME");
			String endtime = (String) _map.get("ENDTIME");
			
			StringBuffer sql = new StringBuffer();
			if (orderid == null && orderid.equals(""))
				throw new NullPointerException("传入的流水号为空。");
			// 基本条件
			sql.append("select t.appealid as id,t.createtime as intime,");
			sql.append("decode(t.bosomprogressstate,'1','待处理',2,'转派',");
			sql.append("3,'暂存',4,'回退',8,'作废',9,'完成','待处理') as statu,"); 
			sql.append("t.lockstaffid ");
			sql.append("from tf_appeal t ");
			sql.append("where t.weborder='"); 
			sql.append(orderid);
			sql.append("'");
			/**
			 * 状态 statu: 0: 所有订单 1: 待处理 2: 转派 3: 暂存 4: 已作废 5: 已完成 6: 自己锁定
			 */
			if (statu != null) {
				
				if (statu.equals("1")) {
					sql
					.append(" and t.PROGRESSSTATE='1' and t.BOSOMPROGRESSSTATE='1'");
				} else if (statu.equals("2")) {
					sql
					.append(" and t.PROGRESSSTATE='2' and t.BOSOMPROGRESSSTATE='2'");
				} else if (statu.equals("3"))
					sql
					.append(" and t.PROGRESSSTATE='2' and t.BOSOMPROGRESSSTATE='3'");
				else if (statu.equals("4"))
					sql
					.append(" and t.PROGRESSSTATE='3' and t.BOSOMPROGRESSSTATE='8'");
				else if (statu.equals("5"))
					sql
					.append(" and t.PROGRESSSTATE='3' and t.BOSOMPROGRESSSTATE='9'");
				else if (statu.equals("6")) {
					sql.append(" and t.LOCKSTAFFID=");
					sql.append(userid);
				}else if (statu.equals("7")) {
					sql.append(" and t.PROGRESSSTATE='1' and t.BOSOMPROGRESSSTATE='4'");
				}else if (statu.equals("8")) {
					sql.append(" and t.PROGRESSSTATE='3' and t.BOSOMPROGRESSSTATE='A'");
				}
				
			}
			
			// 开始时间
			if (starttime != null && !starttime.equals("")) {
				sql.append(" and t.CREATETIME >= to_date('");
				sql.append(starttime);
				sql.append("','YYYY-MM-DD HH24:MI:SS')");
			}
			// 结束时间
			if (endtime != null && !endtime.equals("")) {
				sql.append(" and t.CREATETIME <= to_date('");
				sql.append(endtime);
				sql.append("','YYYY-MM-DD HH24:MI:SS')");
			}
			// 对结果排序
			sql.append(" order by t.CREATETIME");
			
			return executePageList(startrecord, endrecord, sql.toString(),null);
			
		} catch (java.sql.SQLException sqlx) {
			throw sqlx;
		} catch (RuntimeException rux) {
			_logger.warn(rux.getMessage(), rux);
			throw rux;
		}
		
	}
	
	/**
	 * 获取当前某一状态的总数
	 * @param _map
	 * @param statu
	 * @return
	 * @throws java.sql.SQLException
	 * @throws RuntimeException
	 */
	static public int getOrderListCount(Map _map,String statu)
	throws java.sql.SQLException, RuntimeException {
		
		try {
			DataTable _dt = getOrderMethod(_map,statu);
			return _dt.getRow(0).getInt("count");
			
		} catch (java.sql.SQLException sqlx) {
			throw sqlx;
		} catch (RuntimeException rux) {
			_logger.warn(rux.getMessage(), rux);
			throw rux;
		}
	}

	
	private static DataTable executePageList(int start, int end, String sql,Object[] _param)
	throws java.sql.SQLException {
		StringBuffer _sb = new StringBuffer();
		_sb.append("SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM(");
		_sb.append(sql);
		_sb.append(")row_ WHERE rownum <=");
		_sb.append(end);
		_sb.append(") WHERE rownum_ >");
		_sb.append(start);
	    //System.out.println("sql \n: "+_sb.toString());
		if(_param == null || _param.length ==0){
		    return _dac.executeQuery(_sb.toString());
		}else{
			return _dac.executeQuery(_sb.toString(),_param);
		}
	}
	
	/******************************单条工单**************************************/
	/**
	 * <code>不归档的工单记录历史查询</code>
	 * @param weborder
	 * @return
	 */
	public static DataTable getOrderHistory(String weborder,int startrecord,
			int recordnum) throws java.sql.SQLException, RuntimeException {
		int endrecord = recordnum;
		if (startrecord < 0)
			startrecord = 0;
		if (endrecord > 0 && startrecord > 0)
			endrecord = startrecord + recordnum;
		try{
			StringBuffer sql = new StringBuffer();
			
			sql.append("select t.APPEALID,to_char(t.CREATETIME,'YYYY-MM-DD HH24:mi:ss') as CREATETIME,");
			sql.append("decode(t.BOSOMPROGRESSSTATE,'1','待处理','2','转派','3',");
			sql.append("'暂存','4','回退','8','作废','9','完成','待处理') as statu,"); 
			sql.append("t.LOCKSTAFFID ");
			sql.append("from tf_appeal t ");
			sql.append("where t.weborder=? ");
			sql.append("order by t.CREATETIME");
			
			return executePageList(startrecord, endrecord, sql.toString(),new Object[]{weborder});
		}catch(java.sql.SQLException sqlx){
			throw new RuntimeException(sqlx);}
	}
	
	/**
	 * <code>不归档的工单记录历史总数</code>
	 * @param weborder
	 * @return
	 */
	public static DataTable getOrderHistoryCount(String weborder){
		
		try{
			StringBuffer sql = new StringBuffer();
			sql.append("select count(APPEALID) as count ");
			sql.append("from tf_appeal t ");
			sql.append("where t.weborder=? ");
			
			return _dac.executeQuery(sql.toString(),new Object[]{weborder});
		}catch(java.sql.SQLException sqlx){
			throw new RuntimeException(sqlx);}
	}
	
	/********************************************************************/

}