📄 familybranddao.java

📁 电信的网厅的整站代码
💻 JAVA
📖 第 1 页 / 共 2 页
字号:
12 下一页
package com.doone.fj1w.fjmgr.order;

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

import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

import com.doone.data.DataRow;
import com.doone.data.DataTable;
import com.doone.util.FileLogger;

public class FamilyBrandDAO extends DAO {
	static private FileLogger _logger = new FileLogger();

	private static DataTable _dataTable = null;

	public FamilyBrandDAO() {
		super();
		// TODO Auto-generated constructor stub
	}
/**
	 * linfei 2006-12-27 
	 * 客户品牌专区显示列表。
	 * 
	 * @param _map
	 * @param startrecord
	 * @param recordnum
	 * @return
	 * @throws java.sql.SQLException
	 * @throws RuntimeException
	 */
	static public DataTable getFamilyBrandList(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 = (String) _map.get("STARTTIME");
			String endtime = (String) _map.get("ENDTIME");
			String citycode = (String) _map.get("CITY");
			String statu = (String) _map.get("ACTION");// 传进来的当前分页状态方式
			String orderid = (String) _map.get("ORDERID");// 流水号
			String staffID = (String) _map.get("USERID");
			String sort = CommonMethod.formatString((String)_map.get("SORT"));
			String phonenum = (String) _map.get("PHONENUM");	// 宽带帐号、电话号码
			String servicetype = (String) _map.get("SERVICETYPE");// 套餐类型（e6或e8）

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

			StringBuffer sql = new StringBuffer();
			LinkedList _linkList = new LinkedList();
			// 基本条件funname_bss varchar2(50) y bss系统提供的程控功能名称

			sql.append(" select t.orderid, t.WEBORDER,t.SERVICETYPE,t.SERVICETYPECON,f.ABOUTTEL,f.ABOUTNET,f.ABOUTPHS,to_char(t.ACCEPTTIME,'YYYY-MM-DD HH24:MI') ACCEPTTIME,");
			sql.append(" t.BOSOMPROGRESSSTATE,");
			sql.append(" decode(t.BOSOMPROGRESSSTATE,'1','待处理','2','处理中','3','处理完成',4,'处理失败','其它') PROGRESSSTATENAME,");
			sql.append(" t.OTHERORDER,t.ORDERNBRTYPE,t.TEMPORDER,");
			sql.append(" t.PHONENUM,to_char(t.FINISHTIME,'YYYY-MM-DD HH24:MI') FINISHTIME,");
			sql.append(" decode(t.PRODUCTID,'1','固定电话','2','小灵通','其它') PRODUCTIDNAME,");
			sql.append(" t.CITYCODE,t.PRODUCTID ,staff.name"); 
			sql.append(" from tf_order t ,tf_orderhome f ,tf_staff staff");
			sql.append(" where t.ORDERID=f.ORDERID and t.lockstaffid=staff.staffid(+) and t.PRODUCTID=99 "); //99为我的e家
			// 权限值
			if (!citycode.equals("0590")) {
				sql.append(" and t.citycode=?");
				_linkList.add(citycode);
				sql.append(" and t.citycode=?");
				_linkList.add(citycode);

			}

			// STATE 受理状态。如果BSS返回全部为0
			
			if (statu != null && !statu.equals("")) {
				if (statu.equals("L")) {
					sql.append(" and t.lockstaffid=? ");
					_linkList.add(staffID);
				}
				else {
					if(statu.equals("4")){
						//处理失败与异常单并归.
						sql.append(" and t.BOSOMPROGRESSSTATE not in('1','2','3','911')");
					}else{
						sql.append(" and t.BOSOMPROGRESSSTATE=?");
						_linkList.add(statu);
					}
				}
			}

			// 套餐类型（e6或e8）
			if (servicetype != null && !servicetype.equals("")) {
				sql.append(" and t.SERVICETYPE like ?");
				_linkList.add("%" + servicetype + "%" );
			}

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

			// 宽带帐号、电话号码
			if (phonenum != null && !phonenum.equals("")) {
				sql.append(" and (f.ABOUTNET like ?");
				_linkList.add("%" + phonenum.trim() + "%" );
				sql.append(" or f.ABOUTPHS like ? ");
				_linkList.add("%" + phonenum.trim() + "%" );
				sql.append(" or f.ABOUTTEL like ? )");
				_linkList.add("%" + phonenum.trim() + "%" );
			}

			// 开始时间
			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(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);
			}
			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;
		}
	}
	static public DataTable getFamilyBrandOne(java.util.Map _map
		) throws java.sql.SQLException, RuntimeException {
		try {
			String orderid = (String) _map.get("ORDERID");// 流水号
			StringBuffer sql = new StringBuffer();
			LinkedList _linkList = new LinkedList();
			sql.append(" select h.username, ");         
			sql.append(" h.vipno, ");            
			sql.append(" h.cartname, ");        
			sql.append(" h.cartno, ");          
			sql.append(" h.address, ");          
			sql.append(" h.MAILADDRESS, ");
			sql.append(" h.POSTALCODE,");       
			sql.append(" h.EMAIL, ");           
			sql.append(" h.LINKMAN, ");          
			sql.append(" h.RELATIONTEL, ");
			sql.append(" h.ABOUTPHS, ");        
			sql.append(" h.ABOUTNET, ");        
			sql.append(" t.WEBORDER, ");        
			sql.append(" t.ACCEPTTIME, ");      
			sql.append(" t.AREACODE , ");
			sql.append(" t.SERVICETYPE,t.SERVICETYPECON");
			sql.append(" from tf_order t, tf_orderHome h  ");
			sql.append(" where t.orderid = h.orderid ");
		 
			// 受理单ID
			if (orderid != null && !orderid.equals("")) {
				sql.append(" and t.orderid=?");
				_linkList.add(orderid);
			}
			return _dac.executeQuery(sql.toString(), _linkList.toArray());

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

	}
	
	static public DataTable getFamilyBrandFun(java.util.Map _map
	) throws java.sql.SQLException, RuntimeException {
	try {
		String orderid = (String) _map.get("ORDERID");// 流水号
		StringBuffer sql = new StringBuffer();
		LinkedList _linkList = new LinkedList();
		sql.append(" select f.ORDERFUNTYPE, ");
		sql.append(" f.ORDERFUNTYPECON, ");           
		sql.append(" f.ORDERFUNCONTENT  ");          
		sql.append(" from tf_orderfun f "); 
	 
		// 受理单ID
		if (orderid != null && !orderid.equals("")) {
			sql.append(" where f.orderid=?");
			_linkList.add(orderid);
		}
		return _dac.executeQuery(sql.toString(), _linkList.toArray());

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

}
	/**
	 * ****************客户品牌********************** linfei
	 * 2006-12-27******************************
	 * 
	 * @param _map
	 * @param statu
	 * @return
	 * @throws java.sql.SQLException
	 * @throws RuntimeException
	 */
	static public DataTable getFamilyBrandMethod(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 starttime = (String) _map.get("STARTTIME");
			String endtime = (String) _map.get("ENDTIME");
			String staffID = (String) _map.get("USERID");
			// 增加计费号码
			String phonenum = (String) _map.get("PHONENUM");
 			String servicetype = (String) _map.get("SERVICETYPE");
			// PRODUCTID

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

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

			sql.append(" select count(t.ORDERID) count "); 
			sql.append(" from tf_order t ,tf_orderhome f ,tf_staff staff");
			sql.append(" where t.ORDERID=f.ORDERID and t.lockstaffid=staff.staffid(+) "); 
			// 权限值
			if (!citycode.equals("0590")) {
				sql.append(" and t.citycode=?");
				_linkList.add(citycode);
				sql.append(" and t.citycode=?");
				_linkList.add(citycode);

			}

			// STATE 受理状态。如果BSS返回全部为0
			
			if (statu != null && !statu.equals("")) {
				if (statu.equals("L")) {
					sql.append(" and t.lockstaffid=? ");
					_linkList.add(staffID);
				}
				else {
					if(statu.equals("4")){
						//处理失败与异常单并归.
						sql.append(" and t.BOSOMPROGRESSSTATE not in('1','2','3','911')");
					}else{
						sql.append(" and t.BOSOMPROGRESSSTATE=?");
						_linkList.add(statu);
					}
				}
			}

			// 套餐类型（e6或e8）
			if (servicetype != null && !servicetype.equals("")) {
				sql.append(" and t.SERVICETYPE like ?");
				_linkList.add("%" + servicetype + "%" );
			}

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

			// 宽带帐号、电话号码
			if (phonenum != null && !phonenum.equals("")) {
				sql.append(" and (f.ABOUTNET like ?");
				_linkList.add("%" + phonenum.trim() + "%" );
				sql.append(" or f.ABOUTPHS like ? ");
				_linkList.add("%" + phonenum.trim() + "%" );
				sql.append(" or f.ABOUTTEL like ? )");
				_linkList.add("%" + phonenum.trim() + "%" );
			}


			// 开始时间
			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 (_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;
		}
	}
	
	/**
	 * ***************客户品牌***************** linfei
	 * 增加。2006-12-27********************** 取条数。程控记录
	 * 
	 * @param _map
	 * @param statu
	 * @return
	 * @throws java.sql.SQLException
	 * @throws RuntimeException
	 */
	static public int getFamilyBrandListCount(java.util.Map _map, String statu)
			throws java.sql.SQLException, RuntimeException {

		try {
			_dataTable = getFamilyBrandMethod(_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;
		}
	}

	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);
12 下一页
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -