prodorderdao.java

电信的网厅的整站代码

			// 是否大商客
			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);
				}
			}

//			System.out.println("sql: \n"+sql.toString());

			if (_linkList.size() > 0) {
				return _dac.executeQuery(sql.toString(), _linkList.toArray());
			} else {
				return _dac.executeQuery(sql.toString());
			}

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

	/**
	 * <p>
	 * 
	 * </p>
	 * 
	 * @param start
	 * @param end
	 * @param sql
	 * @return
	 * @throws java.sql.SQLException
	 */
	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);

		if (_param == null || _param.length == 0) {
			return _dac.executeQuery(_sb.toString());
		} else {
			return _dac.executeQuery(_sb.toString(), _param);
		}
	}

	/**
	 * 获取一张原业务受理单的状态,可做权限认证
	 * 
	 * @param appealid
	 * @return
	 */
	public String[] getOrderState(String appealid) {
		try {
			String sql = "select PROGRESSSTATE,BOSOMPROGRESSSTATE,PRODUCTID,OPERATIONTYPE,ACCEPTTIME "
					+ "from tf_Prodorder where prodorderid=to_number(?) and UPPRODORDERID=0";
			Object[] _object = new Object[1];
			_object[0] = appealid;

			_dataTable = _dac.executeQuery(sql, _object);
			if (_dataTable != null && _dataTable.getRows().getCount() > 0) {
				String value[] = new String[5];
				value[0] = _dataTable.getRow(0).getString("PROGRESSSTATE");
				value[1] = _dataTable.getRow(0).getString("BOSOMPROGRESSSTATE");
				value[2] = _dataTable.getRow(0).getString("PRODUCTID");
				value[3] = _dataTable.getRow(0).getString("OPERATIONTYPE");
				value[4] = _dataTable.getRow(0).getString("ACCEPTTIME");
				return value;
			}

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

	/**
	 * 
	 * @param proState
	 *            订单处理状态
	 * @return 返回记录数
	 */
	public int getProdorderSize(String proState, String cityCode) {

		if (cityCode == null || cityCode.equals("0590")) {
			cityCode = CITYCODE_SOPE;
		}

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(PRODORDERID) AS count FROM tf_Prodorder a ");
		sql.append("tf_custinfoweb b, tf_Prodorder a ");
		sql.append("WHERE a.userid    = b.userid ");

		if (proState.equals("unlock")) {
			sql.append(" and a.LOCKSTAFFID is null ");
		} else if (proState.startsWith("lock")) {
			String[] value = proState.split("~");
			sql.append(" and a.LOCKSTAFFID=");
			sql.append(value[1]);
		} else if (!proState.equals("all")) {
			sql.append(" and a.PROGRESSSTATE='");
			sql.append(proState);
			sql.append("'");
		}
		sql.append("AND a.UPPRODORDERID=0 AND instr(?, b.citycode)>0");

		Object[] value = new Object[1];
		value[0] = cityCode;

		try {
			_dataTable = _dac.executeQuery(sql.toString(), value);
		} catch (Exception e) {
			_logger.debug(e.getMessage());
			return 0;
		}
		return _dataTable.getRow(0).getInt("count");
	}

	/**
	 * 受理单的显示问题 ---------------------------------------------------------------
	 * 1.在前台可以分页显示出第一次的记录，因为单的“状态”已经改变， 所以可以解决受理的分页显示问题。
	 * 2.在点击此受理单的时候，跟据主键值，寻找最一后修改此单的记录单， 如果有就显示它，没有就显示原单。
	 * 3.在修改的一份copy单的时候，要将用户与系统员的审核单“状态”同时 改变，原受理单才能供前台与后台分页显示查看。
	 * 
	 */
	/**
	 * 1.此方法可以保证受理单的正常受理时，出现的异常处理，使受理单正常受理 2.在进行受理单页面时调用.
	 * 
	 * 历史记录单：原受理单不变，为了保证客户的权利，而使用的一条与原受理单一至，只有其UPPRODORDERID字段，
	 * 指向原受理单的PRODORDERID的记录.
	 * 
	 * 功能： 锁定原受理单，添加一条备份单，对员工自己以前操作出现的误错的纠正
	 * 
	 * @param prodorderID
	 *            订单ID
	 * @return String -3 >>>未知异常情况 -2 >>>数据库连接出错 -1 >>>受理单冲突，系统中存在多条id一像的受理单 0
	 *         >>>锁定受理单失败 1 >>>查询原受理单失败 2 >>>添加历史记录单失败 newId >>>成功寻找到历史记录单的id 3
	 *         >>>查询历史记录单id失败,请重从开始受理 4 >>>受理单被别人锁定
	 */
	public String getValidataOrder(String prodorderID, String staffId) {
		try {
			String newId = "";

			String sql1 = "SELECT count(t.lockstaffid) lockstaffid FROM tf_prodorder t WHERE "
					+ "t.prodorderid=to_number(?) AND t.upprodorderid=0";

			Object[] value = new Object[1];
			value[0] = prodorderID;
			/** 1.受理单是否被锁定 */
			_dataTable = _dac.executeQuery(sql1, value);
			int row = _dataTable.getRows().getCount();
			if (row > 0)
				row = _dataTable.getRow(0).getInt("lockstaffid");
			else
				row = 0;

			String lockId = "";
			if (_dataTable != null) {
				switch (row) {

				case 0:
					/**
					 * 不被锁定 ----------------------------- 1.将此受理单改为自己锁定
					 * 2.查出此受理单的详细资料 3.将详细资料中的父受理单只向自己()生成一个历史记录，
					 */
					/** 自己能否锁定它 */
					boolean flags = false;
					ProdorderDAO _pp = null;
					String bakss = prodorderID;
					try {
						if (setProdorderLock(prodorderID, staffId)) {
							/** 查出是原定单是否有被修改的最近子单 */
							String finalID = getProdHistoryPId(prodorderID);
							if (finalID != null && !finalID.equals(""))
								prodorderID = finalID;

							/** 查出此单 */
							_pp = getUserLockOrder(prodorderID, staffId);
							if (_pp != null) {
								_pp.setUPPRODORDERID(bakss);
								/** 生成一张跟据上一次生成记录的子记录单 */
								if (addLockProdOrder(_pp)) {
									/** newId是现有刚刚成的子记录单id，我们将对这张单进行改，查，操作 */
									newId = getProdHistoryPId(bakss);
									if (newId == null || newId.equals("")) {
										flags = true;
										return "3";
									}
									return newId;
								} else {
									flags = true;
									return "2";
								}
							} else {
								flags = true;
								return "1";
							}
						} else {
							return "0";
						}

					} catch (RuntimeException rux) {
						_logger.warn(rux.getMessage(), rux);
						throw rux;
					} finally {
						if (flags) {
							setProdorderUnLock(bakss);
						}
						_pp = null;
					}
				case 1:
					try {
						// 锁定，是否被自己锁定
						sql1 = "SELECT to_char(t.lockstaffid) locks FROM tf_prodorder t WHERE "
								+ "t.prodorderid=to_number(?) AND t.upprodorderid=0";
						value = null;
						value = new Object[1];
						value[0] = prodorderID;
						_dataTable = _dac.executeQuery(sql1, value);
						if (_dataTable != null && _dataTable.getRows().getCount() > 0)
							lockId = _dataTable.getRow(0).getString("locks");

						if (lockId != null && lockId.equals(staffId)) {
							newId = getProdHistoryPId(prodorderID);
							if (newId == null || newId.equals("")) {
								setProdorderUnLock(prodorderID);
								return "3";
							}
							return newId;
						} else {
							lockId = getStaffCode(lockId);
							return "4~" + lockId;
						}

					} catch (RuntimeException rux) {
						_logger.warn(rux.getMessage(), rux);
						throw rux;
					}
				default:
					return "-1";
				}

			}
			return "-2";
		} catch (Exception e) {
			_logger.warn(e.getCause(), e);
		}
		return "-3";
	}

	/**
	 * <code>
	 *   走bss流程后，跟椐bss流程情况，做工单的处理
	 * </code>
	 * <p>
	 * 目的与上面的方法@seeProdorderDAO#getValidataOrder(String,String)同样。
	 * bss流程只针对本库中bss流程(td_bssFlowXXX)表做操作，而针对工单表（tf_prodorder）只是获取一些基本信息，不
	 * 对工单本身做一些改变（不包括状态），因此走bss流程的工单只判断一些是否被别
	 * 的工号锁定，一权限操作，而不生成新的子单。走完流程，只要更新原工单的状态即可
	 * </p>
	 * 
	 * @param prodorderID
	 * @param staffId
	 * @return 0 >>>锁定受理单失败 4 >>>受理单被别人锁定 -3 >>>未知异常情况 -2 >>>工单不存在
	 */
	public String verifyBssOrder(String prodorderID, String staffId) {

		try {

			String sql1 = "SELECT t.lockstaffid FROM tf_prodorder t "
					+ "WHERE t.prodorderid=to_number(?) AND t.upprodorderid=0";

			Object[] value = new Object[1];
			value[0] = prodorderID;
			/** 1.受理单是否被锁定 */
			try {
				_dataTable = _dac.executeQuery(sql1, value);
			} catch (java.sql.SQLException sqlx) {
				throw new RuntimeException(sqlx.getMessage());
			}

			String lockId = "";
			if (_dataTable != null && _dataTable.getRows().getCount() > 0) {
				String lockstaffid = _dataTable.getRow(0).getString("lockstaffid");

				/**
				 * 不被锁定 1.将此受理单改为自己锁定
				 */
				if (lockstaffid == null || lockstaffid.equals("")) {
					if (setProdorderLock(prodorderID, staffId))
						return prodorderID;
					else
						return "0";
				}

				/**
				 * 锁定，是否被自己锁定
				 */
				try {
					if (lockstaffid.equals(staffId))
						return prodorderID;
					else {
						lockId = getStaffCode(lockstaffid);
						return "4~" + lockId;
					}

				} catch (RuntimeException rux) {
					throw rux;
				}
			}

			return "-2";
		} catch (RuntimeException rux) {
			_logger.warn(rux.getCause(), rux);
		}
		return "-3";
	}

	/**
	 * 查询一张用户锁定订单详细信息(跨表)
	 * 
	 * @param prodorderID
	 *            订单ID
	 * @return DataTable
	 */
	public DataTable getProdorderDetail(String prodorderID) {
		StringBuffer _sb = new StringBuffer();
		_sb.append("SELECT ");
		_sb.append("t.PRODORDERID,t.UPPRODORDERID,t.PROGRESSSTATE,t.PRODUCTID,t.WEBORDER,t.CUSTCODE,t.OTHERDESC,");
		_sb.append("t.OLDADDR,t.NEWADDR,t.USEMODENMODE,t.ADSL_PRICE_ID,");
		_sb.append("t.SERV_NBR_NAME,t.THIRDMSG,t.IF_MOVE,");
		_sb.append("t.AcceptTime,t.CALLING,t.USERNAME,t.VipNo,t.CartName,");
		_sb.append("t.CartNo,t.ABOUTTEL,t.LinkMan,t.MailAddress,t.eMail,t.OPERATIONTYPE,");
		_sb.append("to_char(t.HopeUseTime,'yyyy-MM-dd') as UseTime,");
		_sb.append("t.RelationTel,t.Postalcode,t.BuildPaymentMode,");
		_sb.append("to_char(t.OUTDATE,'yyyy-MM-dd') as OUTDATE,");
		_sb.append("decode(t.BOSOMPROGRESSSTATE,'1','待处理','2','转派',");
		_sb.append("'3','暂存','4','回退','8','作废',9,'完成') as statu,");
		_sb.append("t.CommPaymentMode,t.LOCKSTAFFID,t.OTHERORDER,");
		_sb.append("p.ProductName,a.AREACODE,city.CITYCODE,city.CITYNAME");
		_sb.append(" FROM tf_custinfoweb a,tf_Prodorder t,Td_Product p,td_city city ");
		_sb.append(" WHERE t.userid    = a.userid");
		_sb.append(" AND   t.productid = p.productid");
		_sb.append(" AND   a.citycode  = city.citycode");
		_sb.append(" AND   PRODORDERID = ? ");

		try {
			return _dac.executeQuery(_sb.toString(), new Object[] { prodorderID });
		} catch (java.sql.SQLException sqlx) {
			_logger.warn(ProdorderDAO.class.getName() + " get Prodorder Detail error " + sqlx.getMessage());
		}
		return null;
	}

	/**
	 * 显示一张用户锁定的受理单详细资料(不跨表)
	 * 
	 * @param prodorderID
	 * @return
	 */
	public ProdorderDAO getUserLockOrder(String prodorderID, String lockid) {
		try {
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");

			Object[] _object = new Object[1];
			_object[0] = prodorderID;

			StringBuffer _sb = new StringBuffer();
			_sb.append("SELECT ");
			_sb.append("PRODORDERID,UPPRODORDERID,PRODUCTID,USERID,");
			_sb.append("WEBORDER,PROGRESSSTATE,USERNAME,OPERATIONPWD,VIPNO,");
			_sb.append("CALLING,CARTNAME,CARTNO,ABOUTTEL,POSTALCODE,");
			_sb.append("LINKMAN,EMAIL,MAILADDRESS,RELATIONTEL,BUILDPAYMENTMODE,");
			_sb.append("COMMPAYMENTMODE,HOPEUSETIME,OTHERDESC,");
			_sb.append("to_char(ACCEPTTIME,'yyyy-mm-dd hh24:mm:ss') as ACCEPTTIME,");
			_sb.append("CUSTCODE,LOCKSTAFFID,OUTDATE,BOSOMPROGRESSSTATE,OLDADDR,");
			_sb.append("NEWADDR,USEMODENMODE,ADSL_PRICE_ID,OPERATIONTYPE,SERV_NBR_NAME,IF_MOVE,THIRDMSG");
			_sb.append(" FROM tf_Prodorder t WHERE t.PRODORDERID=?");

			_dataTable = _dac.executeQuery(_sb.toString(), _object);
			if (_dataTable != null && _dataTable.getRows().getCount() > 0) {
				ProdorderDAO _prod = new ProdorderDAO();
				_prod.setPRODORDERID(String.valueOf(_dataTable.getRow(0).getInt("PRODORDERID")));
				_prod.setUPPRODORDERID(String.valueOf(_dataTable.getRow(0).getInt("UPPRODORDERID")));
				_prod.setPRODUCTID(String.valueOf(_dataTable.getRow(0).getInt("PRODUCTID")));
				_prod.setUSERID(_dataTable.getRow(0).getString("USERID"));
				_prod.setWEBORDER(_dataTable.getRow(0).getString("WEBORDER"));
				_prod.setPROGRESSSTATE(_dataTable.getRow(0).getString("PROGRESSSTATE"));
				_prod.setUSERNAME(_dataTable.getRow(0).getString("USERNAME"));
				_prod.setOPERATIONPWD(_dataTable.getRow(0).getString("OPERATIONPWD"));
				_prod.setVIPNO(_dataTable.getRow(0).getString("VIPNO"));
				_prod.setCALLING(_dataTable.getRow(0).getString("CALLING"));
				_prod.setCARTNAME(_dataTable.getRow(0).getString("CARTNAME"));
				_prod.setCARTNO(_dataTable.getRow(0).getString("CARTNO"));
				_prod.setABOUTTEL(_dataTable.getRow(0).getString("ABOUTTEL"));
				_prod.setPOSTALCODE(_dataTable.getRow(0).getString("POSTALCODE"));
				_prod.setLINKMAN(_dataTable.getRow(0).getString("LINKMAN"));
				_prod.setEMAIL(_dataTable.getRow(0).getString("EMAIL"));
				_prod.setMAILADDRESS(_dataTable.getRow(0).getString("MAILADDRESS"));
				_prod.setRELATIONTEL(_dataTable.getRow(0).getString("RELATIONTEL"));
				_prod.setBUILDPAYMENTMODE(_dataTable.getRow(0).getString("BUILDPAYMENTMODE"));
				_prod.setCOMMPAYMENTMODE(_dataTable.getRow(0).getString("COMMPAYMENTMODE"));
				if (_dataTable.getRow(0).getDate("HOPEUSETIME") != null)
					_prod.setHOPEUSETIME(df.format(_dataTable.getRow(0).getDate("HOPEUSETIME")));

				_prod.setOTHERDESC(_dataTable.getRow(0).getString("OTHERDESC"));
				_prod.setACCEPTTIME(_dataTable.getRow(0).getString("ACCEPTTIME"));
				_prod.setCUSTCODE(_dataTable.getRow(0).getString("CUSTCODE"));
				_prod.setLOCKSTAFFID(_dataTable.getRow(0).getString("LOCKSTAFFID"));
				_prod.setOUTDATE(df.format(_dataTable.getRow(0).getDate("OUTDATE")));
				_prod.setBOSOMPROGRESSSTATE(_dataTable.getRow(0).getString("BOSOMPROGRESSSTATE"));
				_prod.setOLDADDR(_dataTable.getRow(0).getString("OLDADDR"));
				_prod.setNEWADDR(_dataTable.getRow(0).getString("NEWADDR"));
				_prod.setUSEMODENMODE(_dataTable.getRow(0).getString("USEMODENMODE"));
				_prod.setADSL_PRICE_ID(_dataTable.getRow(0).getString("ADSL_PRICE_ID"));
				_prod.setOPERATIONTYPE(_dataTable.getRow(0).getString("OPERATIONTYPE"));
				_prod.setSERV_NBR_NAME(_dataTable.getRow(0).getString("SERV_NBR_NAME"));
				_prod.setIF_MOVE(_dataTable.getRow(0).getString("IF_MOVE"));
				_prod.setTHIRDMSG(_dataTable.getRow(0).getString("THIRDMSG"));
				return _prod;
			}
		} catch (RuntimeException rux) {