consomerdao.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.order;

import com.doone.data.DataTable;

import java.util.LinkedList;
import java.util.Map;

/**
 * <strong> 投诉建议单的个性部分 </strong>
 * 
 * @author predan
 * 
 */
public class ConsomerDAO extends AppealDAO {

	public ConsomerDAO() {
		super();
	}

	/**
	 * <code>
	 *  统计故障单的每种状态的工单总数，
	 *  如等待，暂存，作废等.
	 * </code>
	 * 
	 * @param _map
	 * @param statu
	 * @return
	 * @throws java.sql.SQLException
	 * @throws RuntimeException
	 */
	static public DataTable getOrderMethod(Map _map, String statu)
			throws java.sql.SQLException, RuntimeException {

		try {
			String citycode = (String) _map.get("CITY");// 地市编码
			String orderid = (String) _map.get("ORDERID");// 流水号
			String area = (String) _map.get("AREA");// 地区编码
			String affirtype = (String) _map.get("AFFIRTYPE");// 业务类型
			String affircom = (String) _map.get("AFFIRCOM");// 投诉事由
			String staffid = (String) _map.get("STAFFID");// 管理员工号
			String starttime = (String) _map.get("STARTTIME");// 开始时间
			String endtime = (String) _map.get("ENDTIME");// 结束时间
			String custtype = (String) _map.get("CUSTTYPE");
			String oparettype = (String) _map.get("OPARETTYPE");//工单类型			

			StringBuffer sql = new StringBuffer();
			LinkedList _linkList = new LinkedList();
			// 基本条件
			sql.append("select count(t.appealid) as count from ");
			sql.append("tf_custinfoweb b,tf_appeal t,td_appealtype a ");
			sql.append("where t.userid = b.userid ");
			sql.append("and t.appealtypeid=a.appealtypeid ");
			sql.append("and t.upappealid=0 ");

			// 业务类型
			if (affirtype != null && !affirtype.equals("")) {
				sql.append(" and t.appealtypeid=?");
				_linkList.add(affirtype);
			}

			// 投诉事由
			if (affircom != null && !affircom.equals("")) {
				sql.append(" and t.appeacontent like ?");
				_linkList.add("%" + affircom.trim() + "%");
			}

			// 流水号
			if (orderid != null && !orderid.equals("")) {
				sql.append(" and t.WEBORDER like ?");
				_linkList.add("%" + orderid.trim() + "%");
			}

			/**
			 * 状态 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(staffid);
				} 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='9' and sysdate-t.CREATETIME > ");
					sql.append(getAdviceArchivalDay());
				}

			}

			//工单类型
			if (oparettype != null && !oparettype.equals("")) {
				if (oparettype.equals("12")) {
					sql.append(" and a.appealtypename<>'无'");
				} else if (oparettype.equals("3")){
					sql.append(" and a.appealtypename='无'");
				}
			}			
			
			// 开始时间
			if (starttime != null && !starttime.equals("")) {
				sql.append(" and t.CREATETIME >= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
				_linkList.add(starttime);
			}
			// 结束时间
			if (endtime != null && !endtime.equals("")) {
				sql.append(" and t.CREATETIME <= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
				_linkList.add(endtime);
			}
			
//			 是否大商客
			if (custtype != null && !custtype.equals("") && !custtype.equals("2")) {
				sql.append(" and substr(t.appeacontent ,0,5) = '大商客性质'");
			}
			
			sql.append("and a.appealmode in('2','3') and a.state='E' ");

			// 权限值
			if (citycode != null && !citycode.equals("")
					&& !citycode.equals("0590")) {
				sql.append(" and b.citycode=?");
				_linkList.add(citycode);
				// 地区
				if (area != null && !area.equals("")) {
					sql.append(" and decode(b.areacode,null,b.citycode||'00',b.areacode)=?");
					_linkList.add(area);
				}
			}

			sql.append(" order by t.createtime asc");
//			 System.out.println("sql \n:"+sql.toString());
			if (_linkList.size() == 0) {
				return _dac.executeQuery(sql.toString());
			} else {
				return _dac.executeQuery(sql.toString(), _linkList.toArray());
			}
		} 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;
		}
	}

	/**
	 * <p>
	 * 业务单分页处理
	 * </p>
	 * 
	 * @param map:
	 *            <code>所有的查询条件</code>
	 * @param startrecord:
	 *            <code>从第几条开始</code>
	 * @param recordnum:
	 *            <code>显示几条</code>
	 * @return
	 */
	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 citycode = (String) _map.get("CITY");// 地市编码
			String orderid = (String) _map.get("ORDERID");// 流水号
			String statu = (String) _map.get("ACTION");
			String area = (String) _map.get("AREA");// 地区编码
			String affirtype = (String) _map.get("AFFIRTYPE");// 业务类型
			String affircom = (String) _map.get("AFFIRCOM");// 投诉事由
			String staffid = (String) _map.get("STAFFID");// 管理员工号
			String starttime = (String) _map.get("STARTTIME");// 开始时间
			String endtime = (String) _map.get("ENDTIME");// 结束时间
			String sort = CommonMethod.formatString((String)_map.get("SORT"));
			String custtype = CommonMethod.formatString((String)_map.get("CUSTTYPE"));
			String oparettype = (String) _map.get("OPARETTYPE");//工单类型

			StringBuffer sql = new StringBuffer();
			LinkedList _linkList = new LinkedList();
			// 基本条件
			sql.append("select t.appealid,t.weborder,a.appealtypename,");
			sql.append("t.appeacontent,t.createtime,t.lockstaffid,");
			sql.append("decode(t.BOSOMPROGRESSSTATE,'1','待处理','2','转派','3',");
			sql.append("'暂存',4,'回退','8','作废',9,'完成','待处理') as statu ");
			sql.append("from tf_custinfoweb b,tf_appeal t,td_appealtype a ");
			sql.append("where t.userid = b.userid ");
			sql.append("and t.appealtypeid=a.appealtypeid ");
			sql.append("and t.upappealid=0 ");
			// 业务类型
			if (affirtype != null && !affirtype.equals("")) {
				sql.append(" and t.appealtypeid=?");
				_linkList.add(affirtype);
			}

			// 投诉事由
			if (affircom != null && !affircom.equals("")) {
				sql.append(" and t.appeacontent like ?");
				_linkList.add("%" + affircom.trim() + "%");
			}

			// 流水号
			if (orderid != null && !orderid.equals("")) {
				sql.append(" and t.WEBORDER like ?");
				_linkList.add("%" + orderid.trim() + "%");
			}

			/**
			 * 状态 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(staffid);
				} 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='9' and sysdate-t.CREATETIME > ");
					sql.append(getAdviceArchivalDay());
				}

			}

			//工单类型
			if (oparettype != null && !oparettype.equals("")) {
				if (oparettype.equals("12")) {
					sql.append(" and a.appealtypename<>'无'");
				} else if (oparettype.equals("3")){
					sql.append(" and a.appealtypename='无'");
				}
			}
			
			// 开始时间
			if (starttime != null && !starttime.equals("")) {
				sql.append(" and t.CREATETIME >= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
				_linkList.add(starttime);
			}
			// 结束时间
			if (endtime != null && !endtime.equals("")) {
				sql.append(" and t.CREATETIME <= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
				_linkList.add(endtime);
			}
			
//			 是否大商客
			if (custtype != null && !custtype.equals("") && !custtype.equals("2")) {
				sql.append(" and substr(t.appeacontent ,0,5) = '大商客性质'");
			}
			
			sql.append("and a.appealmode in('2','3') and a.state='E' ");

			// 权限值
			if (citycode != null && !citycode.equals("")
					&& !citycode.equals("0590")) {
				sql.append(" and b.citycode=?");
				_linkList.add(citycode);
				// 地区
				if (area != null && !area.equals("")) {
					sql.append(" and decode(b.areacode,null,b.citycode||'00',b.areacode)=?");
					_linkList.add(area);
				}
			}

			if(sort.equals("")){
				if (isCompositor(statu)) {
					sql.append(" order by t.createtime asc");
				} else {
					sql.append(" order by t.createtime desc");
				}
			}else{
				sql.append(" order by t.createtime ");
				sql.append(sort);
			}
			
			return executePageList(startrecord, endrecord, sql.toString(),
					_linkList.toArray());

		} 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("分页内容： \n"+_sb.toString());
		if (_param == null || _param.length == 0) {
			return _dac.executeQuery(_sb.toString());
		} else {
			return _dac.executeQuery(_sb.toString(), _param);
		}
	}

	public static DataTable getOrderList(String citycode)
			throws RuntimeException, java.sql.SQLException {

		String sql = "select t.appealtypename,t.appealtypeid from td_appealtype t "
				+ "where t.appealmode in('2','3') " + "and   t.citycode = ?";

		return _dac.executeQuery(sql, new Object[] { citycode });
	}

	/**
	 * <title>工单是否升降排序</title> <body>true : 升序 1: 待处理 3: 暂存 2: 转派 6: 自己锁定 7:回退</body>
	 * <body>false : 降序 0: 所有订单 4: 已作废 5: 已完成 8:归档
	 * 
	 * </body>
	 * 
	 * @param active
	 * @return
	 */
	private static boolean isCompositor(String active) {
		if (active == null || active.equals("")) {
			return false;
		}
		if (active.equals("1")) {
			return true;
		}
		if (active.equals("3")) {
			return true;
		}
		if (active.equals("2")) {
			return true;
		}
		if (active.equals("4")) {
			return false;
		}
		if (active.equals("5")) {
			return false;
		}
		if (active.equals("6")) {
			return true;
		}
		if (active.equals("7")) {
			return true;
		}
		if (active.equals("8")) {
			return false;
		}
		return false;
	}
}