appealdao.java

电信的网厅的整站代码

/**
 * 创建日期 2005-7-21
 *
 */
package com.doone.fj1w.fjmgr.order;

import java.text.SimpleDateFormat;
import com.doone.data.DataTable;
import com.doone.util.FileLogger;

/**
 * @author huzx
 *
 */
public class AppealDAO extends DAO {

	static FileLogger _logger = new FileLogger();
	/** 订单处理状态* */
	public static final String WAIT_FOR_PROCESS = "1";

	public static final String IN_PROCESS_STATE = "2";

	public static final String PROCESS_END_STATE = "3";

	private int pageSize = 20; // 默认每页20条

//	private String sql = "";

	/** 故障投诉建议序号 */
	private String appealid = "";

	/** 登陆帐号 * */
	private String userid = "";

	private String upappealid = "";

	/** 故障投诉建议类别序号* */
	private String appealtypeid = "";

	/** 地市编码* */
	private String citycode = "";

	/** 投诉人* */
	private String accountno = "";

	/** 性别* */
	private String sex = "";

	/** 邮件地址* */
	private String email = "";

	/** 要求回复时间* */
	private String requresttime = "";

	/** 联系电话* */
	private String relationtel = "";

	/** 故障电话* */
	private String failuretel = "";

	/** 投诉事由* */
	private String appealtitle = "";

	/** 投诉内容* */
	private String appeacontent = "";

	/** 类型* */
	private String appealmode = "";

	/** 进度状态* */
	private String progressstate = "";

	/** 第三方定单流水号* */
	private String otherorder = "";

	private String LOCKSTAFFID = "";

	private String outdate = "";
	/** 重要程度* */
	private String importancedegree = "";

	/** 投诉时间* */
	private String createtime = "";

    private String BOSOMPROGRESSSTATE = "";

    private String FAULTTYPE = "";

    private String CUSTTYPE  = "";   //      VARCHAR2(10)  Y                大商公流
    private String WEBORDER  = ""; //         VARCHAR2(20)  Y                网上故障定单流水号
    private String APPEALORDERNUM = "" ;

	public String getFAULTTYPE() {
		return FAULTTYPE;
	}

	public void setFAULTTYPE(String faulttype) {
		FAULTTYPE = faulttype;
	}

	public String getBOSOMPROGRESSSTATE() {
		return BOSOMPROGRESSSTATE;
	}

	public void setBOSOMPROGRESSSTATE(String bosomprogressstate) {
		BOSOMPROGRESSSTATE = bosomprogressstate;
	}

	public AppealDAO() {
		FileLogger.getLogger().debug("AppealDAO 数据库链接" + _dac);
	}
	
	/**
	 * 获取一张故障单的故障级别
	 */
	public String getFaultType(String appealid,String citycode){
		try{
			Object[] _object = new Object[2];
			_object[0] = citycode;
			_object[1] = appealid;
            
			StringBuffer _sb = new StringBuffer();
			_sb.append("select APPEALCODE from ");
			_sb.append("(select t.APPEALCODE,SYS_CONNECT_BY_PATH(t.appealtypeid,'/'),level from td_appealtype t ");
			_sb.append("connect by prior t.upappealtypeid = t.appealtypeid  ");
			_sb.append("start with t.citycode=? and t.appealtypeid =? ");
			_sb.append("order by level desc) ");
			_sb.append("where rownum=1");


			DataTable _dt = _dac.executeQuery(_sb.toString(),_object);
			if(_dt != null && _dt.getRows().getCount() > 0){
				String code = _dt.getRow(0).getString("APPEALCODE");
				return code;
			}
		}catch(Exception ex){
			_logger.warn(ex.getMessage(),ex);
		}
		return "";

	}

	/**
	 *  获取一张投诉单的故障级别
	 * @param appealid
	 * @param citycode
	 * @return
	 */
	public String getAppealType(String appealid,String citycode){
		try{
			Object[] _object = new Object[2];
			_object[0] = citycode;
			_object[1] = appealid;
            
			StringBuffer _sb = new StringBuffer();
			_sb.append("select t.APPEALCODE,SYS_CONNECT_BY_PATH(t.appealtypeid,'/'),level from td_appealtype t ");
			_sb.append("connect by prior t.upappealtypeid = t.appealtypeid  ");
			_sb.append("start with t.citycode=? and t.appealtypeid =? ");
			_sb.append("order by level asc");

			DataTable _dt = _dac.executeQuery(_sb.toString(),_object);
			if(_dt != null && _dt.getRows().getCount() > 0){
				int i = _dt.getRows().getCount();
				String code = "";
				while(--i >= 0){
				 code += _dt.getRow(i).getString("APPEALCODE")+"~!";
				}

				return code;
			}
		}catch(Exception ex){
			_logger.warn(ex.getMessage(),ex);
		}
		return "";

	}

	/**
	 *  获取一张建议单的故障级别
	 * @param appealid
	 * @param citycode
	 * @return
	 */
	public String getConServiceType(String appealid,String citycode){
		try{
			Object[] _object = new Object[2];
			_object[0] = citycode;
			_object[1] = appealid;

			String sql = "select t.APPEALCODE,SYS_CONNECT_BY_PATH(t.appealtypeid,'/'),level from td_appealtype t  "+
					"connect by prior t.upappealtypeid = t.appealtypeid  "+
					"start with t.citycode=? and t.appealtypeid =? "+
					"order by level asc";

			DataTable _dt = _dac.executeQuery(sql,_object);
			if(_dt != null && _dt.getRows().getCount() > 0){
				int i = _dt.getRows().getCount();
				String code = "";
				while(--i >= 0){
				 code += _dt.getRow(i).getString("APPEALCODE")+"~!";
				}

				return code;
			}
		}catch(Exception ex){
			_logger.warn(ex.getMessage(),ex);
		}
		return "";

	}

	/**
	 * <p>
	 *   所定管理员号
	 * </p>
	 * @param manid
	 * @return
	 */
	public String getLockMan(String manid){
		try{
			Object[] _object = new Object[1];
			_object[0] = manid;

			String sql = "select staffcode from tf_staff where staffid=? and STATE='E'";

			DataTable _dt = _dac.executeQuery(sql,_object);
			if(_dt != null && _dt.getRows().getCount() > 0){
				String code = _dt.getRow(0).getString("staffcode");
				return code;
			}
		}catch(Exception ex){
			_logger.warn(ex.getMessage(),ex);
		}
		return "";
	}

	public boolean changeConsume2(String id,String appealContext,
			                      String tele,String mailaddress){
		try{
			String sql = "update tf_appeal set APPEACONTENT=?" +
					     ",RELATIONTEL=?,EMAIL=? where APPEALID=to_number(?)";
			Object[] _object = new Object[4];
			_object[0] = appealContext;
			_object[1] = tele;
			_object[2] = mailaddress;
			_object[3] = id;

			_dac.beginTransaction(1000);
			int i = _dac.executeUpdate(sql,1000,_object);
			if(i > 0){
				endTransaction(true);
				return true;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		  endTransaction(false);
	   return false;
	}

	/**
	 * 获取一张投诉建议单的状态
	 * @param appealid
	 * @return
	 */
	public String[] getOrderState(String appealid){
		try{
			String sql = "select PROGRESSSTATE,BOSOMPROGRESSSTATE,CREATETIME " +
					     "from tf_appeal where APPEALID=to_number(?) and UPAPPEALID=0";
			Object[] _object = new Object[1];
			_object[0] = appealid;

			DataTable _dt = _dac.executeQuery(sql,_object);
			if(_dt != null && _dt.getRows().getCount() > 0){
			  String value[] = new String[3];
			  value[0] = _dt.getRow(0).getString("PROGRESSSTATE");
			  value[1] = _dt.getRow(0).getString("BOSOMPROGRESSSTATE");
			  value[2] = _dt.getRow(0).getString("CREATETIME");
			  return value;
			}

		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	/**
	 * 修改故障单资料
	 * @param id
	 * @param title
	 * @param context
	 * @return
	 */
	public boolean changeConsumeOrAppealOrder(AppealDAO _pp){
		try{
			String sql = "update tf_appeal set FAILURETEL=?,RELATIONTEL=?," +
					     "REQURESTTIME=to_date(?,'yyyy-mm-dd'),EMAIL=?,APPEALTITLE=?," +
					     "APPEACONTENT=? where APPEALID=to_number(?)";
			Object[] _object = new Object[7];
			_object[0] = _pp.getFailuretel();
			_object[1] = _pp.getRelationtel();
			_object[2] = _pp.getRequresttime();
			_object[3] = _pp.getEmail();
			_object[4] = _pp.getAppealtitle();
			_object[5] = _pp.getAppeacontent();
			_object[6] = _pp.getAppealid();

			_dac.beginTransaction(1000);
			int i = _dac.executeUpdate(sql,1000,_object);
			if(i > 0){
				endTransaction(true);
				return true;
			}
		}catch(Exception ex){
		}
		  endTransaction(false);
	   return false;
	}
	/**
	 * 故障列表
	 *
	 * @param procState
	 *            处理状态
	 * @param pageNo
	 *            查询第N页记录
	 * @return
	 */
	public DataTable getAppealList(String procState, String pageNo,
			String cityCodes) {
		if (procState.equalsIgnoreCase("all")) //查询所有状态
			return getAllAppeals(pageNo, cityCodes);
		else
			return getList(makeSql(Integer.parseInt(pageNo), "1", procState,
					cityCodes));
	}

	/**
	 * 所有故障列表
	 *
	 * @param pageNo
	 *            查询第N页记录
	 * @return
	 */
	private DataTable getAllAppeals(String pageNo, String cityCodes) {
		return getList(makeSql(Integer.parseInt(pageNo), "1", "all", cityCodes));
	}

	/**
	 * 投诉建议列表
	 *
	 * @param procState
	 *            处理状态
	 * @param pageNo
	 *            查询第N页记录
	 * @return
	 */
	public DataTable getAdviceList(String procState, String pageNo,
			String cityCodes) {
		if (procState.equalsIgnoreCase("all"))
			return getAllAdvice(pageNo, cityCodes);
		else
			return getList(makeSql(Integer.parseInt(pageNo), "", procState,
					cityCodes));
	}

	/**
	 * 所有投诉建议列表
	 *
	 * @param pageNo
	 *            查询第N页记录
	 * @return
	 */
	private DataTable getAllAdvice(String pageNo, String cityCodes) {

		return getList(makeSql(Integer.parseInt(pageNo), "", "all", cityCodes));
	}

	/**
	 * @param pageNo
	 *            查询第N页记录
	 * @param AppealMode
	 *            业务类型 1、故障2、投诉3、建议
	 * @param procState
	 *            订单处理状态 1、等待处理2、处理中3、处理结束
	 * @return
	 */
	public String makeSql(int pageNo, String appealMode, String procState,
			String cityCodes) {
		StringBuffer sql = new StringBuffer();
		sql.append("select t.AppealId,t.ACCOUNTNO,t.RequRestTime,t.RelationTel,t.FAILURETEL,"
				+ "t.APPEACONTENT,decode(t.ProgressState,'1','等待处理','2','处理中','3','处理结束','待处理') state "
			    + ",t.CREATETIME,decode(t.appealmode,'1','故障','2','投诉','3','建议','无操作') APPEALMODE"
			    + ",a.appealtypename,to_char(t.OUTDATE,'yyyy-MM-dd') OUTDATE,to_char(t.LOCKSTAFFID) LOCKSTAFFID" +
			    ",decode(t.BOSOMPROGRESSSTATE,'1','待处理','2','转派','3','暂存','8','作废',9,'完成','待处理') statua" +
			    ",t.FAULTTYPE,t.CUSTTYPE,t.WEBORDER from "
				+ "tf_appeal t join tf_custinfoweb b on b.userid = t.userid " 
				+ "join Td_AppealType a on t.APPEALTYPEID=a.APPEALTYPEID "
				+ "where t.UPAPPEALID=0 and b.state='E'");

		//省级单位
		if (cityCodes.equals("0590"))
			cityCodes = "0591,0592,0593,0594,0595,0596,0597,0598,0599";

		sql.append(" and instr('" + cityCodes + "',t.citycode)>0 ");

		if (appealMode.equals("")) {//投诉建议
			sql.append(" and t.appealMode in('2','3') ");
		} else { //故障
			sql.append(" and t.appealMode ='1' ");
		}

		if(procState.equals("unlock"))
			sql.append(" and t.LOCKSTAFFID is null ");
		else if(procState.startsWith("lock")){
			String[] value = procState.split("~");
			sql.append(" and t.LOCKSTAFFID="+value[1]);
		}else if (!procState.equalsIgnoreCase("all")) {
			sql.append(" and t.ProgressState='" + procState + "'");
			if(procState.equals("1")){
				sql.append(" and t.BOSOMPROGRESSSTATE='1'");
			}
		}

		sql.append(" order by state,t.CREATETIME desc");
		String statement ="SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM(" + sql.toString()
				+ ")row_ WHERE rownum <= " + pageNo * pageSize
				+ ") WHERE rownum_ >" + (pageNo - 1) * pageSize;
		return statement;
	}

	/**
	 * @param pageNo
	 *            查询第N页记录
	 * @param sql
	 *            查询sql
	 * @return
	 */
	private DataTable getList(String sql) {
		DataTable dataTable = null;
		try {
			dataTable = _dac.executeQuery(sql);
		} catch (Exception e) {
			FileLogger.getLogger().debug(e.getMessage());

		}
		return dataTable;