prodorderdao.java

电信的网厅的整站代码

/**
 *  
 */
package com.doone.fj1w.fjmgr.order;

import java.text.SimpleDateFormat;
import java.util.*;

import com.doone.data.DacClient;
import com.doone.data.DataRow;
import com.doone.data.DataTable;
import com.doone.fj1w.fj1w.order.Tf_ProdOrder;
import com.doone.iossp.FormBody;
import com.doone.util.FileLogger;

public class ProdorderDAO extends DAO {

	static FileLogger _logger = new FileLogger();

	private static DataTable _dataTable = null;

	/** 订单处理状态* */
	public static final String WAIT_FOR_PROCESS = "1";

	public static final String IN_PROCESS_STATE = "2";

	public static final String PROCESS_END_STATE = "3";

	public static final String CITYCODE_SOPE = "0591,0592,0593,0594,0595,0596,0597,0598,0599";

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

	private String sql = "";

	private String PRODORDERID = "";// NUMBER(10) 预受理序号

	private String UPPRODORDERID = "";// NUMBER(10) 0 父预受理序号

	private String PRODUCTID = "";// NUMBER(10) Y 产品序号

	private String USERID = "";// VARCHAR2(20) Y 登陆帐号

	private String WEBORDER = "";// VARCHAR2(20) 网上定单流水号

	private String PROGRESSSTATE = "";// VARCHAR2(9) '1' 进度状态：

	/**
	 * 1、等待处理 2、处理中
	 * （分二种，一种已提供给第三方系统，一种是审核岗查阅过但还未提供给第三方系统，他们根据Tf_ProdOrderInterface表中的第三方定单流水号字段来区分）
	 * 3、处理结束
	 */
	// 详见系统参数配置表
	private String USERNAME = "";// VARCHAR2(20) 用户名称

	private String OPERATIONPWD = "";// VARCHAR2(6) 业务密码

	private String VIPNO = "";// VARCHAR2(20) Y VIP号

	private String CALLING = "";// VARCHAR2(9) 详见系统参数配置表

	private String CARTNAME = "";// VARCHAR2(9) 详见系统参数配置表

	private String CARTNO = "";// VARCHAR2(20) 证件号码

	private String ABOUTTEL = "";// VARCHAR2(20) Y 就近电话

	private String POSTALCODE = "";// VARCHAR2(6) 邮政编码

	private String LINKMAN = "";// VARCHAR2(20) 联系人

	private String EMAIL = "";// VARCHAR2(50) Y 邮件地址

	private String MAILADDRESS = "";// VARCHAR2(50) Y 信函投递地址

	private String RELATIONTEL = "";// VARCHAR2(30) 联系电话

	private String BUILDPAYMENTMODE = "";// VARCHAR2(9) 详见系统参数配置表

	private String COMMPAYMENTMODE = "";// VARCHAR2(9) 通信费缴费方式

	private String HOPEUSETIME = "";// DATE Y 希望开通时间

	private String OTHERDESC = "";// VARCHAR2(500) Y 其他补充事项

	private String ACCEPTTIME = "";// DATE 受理时间

	private String CUSTCODE = "";// VARCHAR2(20) Y 此值来源于第三方系统

	private String LOCKSTAFFID = "";// NUMBER(10) Y 定单锁定员工标识

	// 此值来源于员工档案表
	private String OUTDATE = "";// DATE 超时时限

	// 此值由系统根据受理时间+电信产品受理时限生成
	private String BOSOMPROGRESSSTATE = "";// VARCHAR2(9) '1' 受理单处理过程状态

	private String OLDADDR = ""; // VARCHAR2(255) Y 旧装机地址

	private String NEWADDR = ""; // VARCHAR2(255) Y 新装机地址

	private String USEMODENMODE = ""; // VARCHAR2(9) Y 终端设备(1、ADSL MODEN租用

	// 2、ADSL MODEN购买（220元/台） 3、□ADSL
	// MODEN自备

	private String ADSL_PRICE_ID = ""; // NUMBER Y ADSL价格ID

	private String OPERATIONTYPE = "";

	private String SERV_NBR_NAME = ""; // VARCHAR2(20) Y 宽带帐户(新装)

	private String IF_MOVE = ""; // VARCHAR2(1) Y ADSL/固定电话是否同移

	private String THIRDMSG = ""; // VARCHAR2(500) Y 第三方返回信息

	private static String list = ""; // 地市列表

	/**
	 * 1、待处理 2、转派 3、暂存 8、作废（作废的受理单也纳入完成范围内） 9、完成
	 */

	public ProdorderDAO() {
		_logger.debug("ProdorderDAO 数据库链接" + _dac);
	}

	public static String getStaffCode(String id) {
		try {
			Object[] _object = new Object[1];
			_object[0] = id;
			String sql = "select staffcode from tf_staff where staffid=? and STATE='E'";
			_dataTable = _dac.executeQuery(sql, _object);
			if (_dataTable != null && _dataTable.getRows().getCount() > 0) {
				String code = _dataTable.getRow(0).getString("staffcode");
				return code;
			}
		} catch (Exception ex) {
		}
		return "";
	}

	static public String getProductList() {
		try {

			String sql = "select productid,productname from td_product t where t.upproductid='0'";
			_dataTable = _dac.executeQuery(sql);

			if (_dataTable == null || _dataTable.getRows().getCount() <= 0) {
				return "<option>无分类</option>";
			}

			String ss = "<option>请选择</option>";
			for (int i = 0; i < _dataTable.getRows().getCount(); i++) {
				String id = _dataTable.getRow(i).getString("productid");
				String name = _dataTable.getRow(i).getString("productname");
				ss += "<option value=" + id + ">" + name + "</option>";
			}
			ss += "<option value=-1>所有</option>";
			list = ss;

			return list;
		} catch (Exception ex) {
		}
		return "";
	}

	/**
	 * <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 starttime = CommonMethod.formatString((String) _map.get("STARTTIME"));
			String endtime = CommonMethod.formatString((String) _map.get("ENDTIME"));
			String citycode = CommonMethod.formatString((String) _map.get("CITY"));
			String statu = CommonMethod.formatString((String) _map.get("ACTION"));// 传进来的当前分页状态方式
			String orderid = CommonMethod.formatString((String) _map.get("ORDERID"));
			String custname = CommonMethod.formatString((String) _map.get("CUSTNAME"));
			String area = CommonMethod.formatString((String) _map.get("AREA"));
			String affirtype = CommonMethod.formatString((String) _map.get("AFFIRTYPE"));
			String userid = CommonMethod.formatString((String) _map.get("USERID"));
			String sort = CommonMethod.formatString((String) _map.get("SORT"));
			String custtype = CommonMethod.formatString((String) _map.get("CUSTTYPE"));

			citycode = citycode == null || citycode.equals("") ? "0590" : citycode;

			StringBuffer sql = new StringBuffer();
			LinkedList _linkList = new LinkedList();
			// 基本条件
			sql.append("select t.PRODORDERID,t.PRODUCTID,t.USERNAME,t.WEBORDER,to_char(t.AcceptTime,'YYYY-MM-DD HH24:MI') ");
			sql.append("AcceptTime,p.ProductName,");
			sql.append("decode(t.BOSOMPROGRESSSTATE,'1','待处理','2','转派','3','暂存',4,'回退','8','作废',9,'完成','待处理') ");
			sql.append("as state,to_char(t.LOCKSTAFFID) LOCKSTAFFID,t.OPERATIONTYPE from ");
			sql.append("tf_custinfoweb b,tf_Prodorder t,Td_Product p ");
			sql.append("where t.PRODUCTID = p.PRODUCTID ");
			sql.append("and t.userid = b.userid ");
			if (citycode.equals("0595")) {// and b.state='E'
				sql.append("and t.UPPRODORDERID=0 and t.OPERATIONTYPE not in ('C','O')");
			} else {
				sql.append("and t.UPPRODORDERID=0 and t.OPERATIONTYPE not in ('C','O','TB')");
			}

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

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

			// 客户名称
			if (custname != null && !custname.equals("")) {
				sql.append(" and t.USERNAME like ?");
				_linkList.add("%" + custname.trim() + "%");
			}

			/**
			 * 状态 statu: 0: 所有订单 1: 待处理 2: 转派 3: 暂存 4: 已作废 5: 已完成 6:
			 * 自己锁定,7:回退，8:归案
			 */
			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='9' and sysdate-t.accepttime > ");
					sql.append(getOrderArchivalDay());
				}

			}

			// 开始时间
			if (starttime != null && !starttime.equals("")) {
				sql.append(" and t.accepttime >= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
				_linkList.add(starttime);
			}
			// 结束时间
			if (endtime != null && !endtime.equals("")) {
				sql.append(" and t.accepttime <= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
				_linkList.add(endtime);
			}

			// 是否大商客
			if (custtype != null && !custtype.equals("") && !custtype.equals("2")) {
//				sql.append(" and substr(t.otherdesc ,0,5) = '大商客性质'");
				sql.append(" and (b.CUSTTYPEID = 7 or b.CUSTTYPEID = 12) and substr(t.otherdesc ,0,5) = '大商客性质'");
			}
			else {
				sql.append(" and b.CUSTTYPEID != 7 and b.CUSTTYPEID != 12");
			}			

//			 权限值
			if (!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.accepttime asc");
				} else {
					sql.append(" order by t.accepttime desc");
				}
			} else {
				sql.append(" order by t.accepttime ");
				sql.append(sort);
			}

//			 System.out.println("sql: \n"+sql.toString());
			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;
		}

	}

	/**
	 * <p>
	 * 获取当前查询的总记录数情况。
	 * </p>
	 * 
	 * @param _map
	 * @return
	 * @throws java.sql.SQLException
	 * @throws RuntimeException
	 */
	static public int getOrderListCount(java.util.Map _map, String statu) throws java.sql.SQLException,
			RuntimeException {

		try {
			_dataTable = getOrderMethod(_map, statu);
			if (_dataTable == null)
				throw new RuntimeException("获取记录数据总数异常！");

			return _dataTable.getRow(0).getInt("count");

		} catch (java.sql.SQLException sqlx) {
			throw sqlx;
		} catch (RuntimeException rux) {
			_logger.warn(rux.getMessage(), rux);
			throw rux;
		}
	}

	/**
	 * 
	 * @param _map
	 * @return
	 * @throws java.sql.SQLException
	 * @throws RuntimeException
	 */
	static public DataTable getOrderMethod(java.util.Map _map, String statu) throws java.sql.SQLException,
			RuntimeException {

		try {
			String citycode = (String) _map.get("CITY");
			String orderid = (String) _map.get("ORDERID");
			String custname = (String) _map.get("CUSTNAME");
			String area = (String) _map.get("AREA");
			String affirtype = (String) _map.get("AFFIRTYPE");
			String userid = (String) _map.get("USERID");
			String starttime = (String) _map.get("STARTTIME");
			String endtime = (String) _map.get("ENDTIME");
			String custtype = (String) _map.get("CUSTTYPE");

			citycode = citycode == null || citycode.equals("") ? "0590" : citycode;

			StringBuffer sql = new StringBuffer();
			// 当前所存在的所有条件
			LinkedList _linkList = new LinkedList();

			// 基本条件
			sql.append("select count(PRODORDERID) as count from ");
			sql.append("tf_custinfoweb b, tf_Prodorder t ");
			sql.append("where t.userid    = b.userid ");
			
			if (citycode.equals("0595")) {// and b.state='E'
				sql.append("and t.OPERATIONTYPE not in ('C','O') and t.UPPRODORDERID=0 ");
			} else {
				sql.append("and t.OPERATIONTYPE not in ('C','O','TB') and t.UPPRODORDERID=0 ");
			}

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

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

			/**
			 * 状态 statu: 0: 所有订单 1: 待处理 2: 转派 3: 暂存 4: 已作废 5: 已完成 6:
			 * 自己锁定,7:回退，8:归案
			 */

			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='9' and sysdate-t.accepttime > ");
					sql.append(getOrderArchivalDay());
				}

			}

			// 开始时间
			if (starttime != null && !starttime.equals("")) {
				sql.append(" and t.accepttime >= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
				_linkList.add(starttime);
			}
			// 结束时间
			if (endtime != null && !endtime.equals("")) {
				sql.append(" and t.accepttime <= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
				_linkList.add(endtime);
			}