everywhereorderrptimpl.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.rpt;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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

public class EverywhereOrderRptImpl extends ReportImpl {
	
	
	/**用于获取报表
	 * 
	 */
	public DataTable getRptList(Map _map) {
		DacClient db = new DacClient();
		try {
			StringBuffer sql = new StringBuffer();
			List oParam = new ArrayList();

			int pageSize = Integer.parseInt((String) _map.get("pageSize"));
			int currentPage = Integer.parseInt((String) _map.get("currentPage"));
			
			int startrecord = currentPage * pageSize;
			int endrecord = pageSize;
			if (startrecord < 0)
				startrecord = 0;
			if (endrecord > 0 && startrecord > 0)
				endrecord = startrecord + pageSize;

			buildSQL(_map,sql,oParam);	
			
			StringBuffer _sb = new StringBuffer();
			_sb.append("SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM(");
			_sb.append(sql);
			_sb.append(")row_ WHERE rownum <= ?");
			_sb.append(") WHERE rownum_ > ?");
			
			oParam.add(new Integer(endrecord));
			oParam.add(new Integer(startrecord));
			
			Object ap[] = new Object[oParam.size()];
			
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			System.out.println("sql.toString()==>"+sql.toString());
			return db.executeQuery(_sb.toString(), ap);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return null;
	}
	
	/**用于导出Excel页面获取报表
	 * 
	 */
	public DataTable getAllList(Map _map) {
		DacClient db = new DacClient();
		try {
			
			StringBuffer sql = new StringBuffer();
			List oParam = new ArrayList();
			
			buildSQL(_map,sql,oParam);	
			
			Object ap[] = new Object[oParam.size()];
			
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			
			return db.executeQuery(sql.toString(), ap);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return null;
	}
	
	/**用于分页
	 * 
	 */
	public int getRptListCount(Map _map) {
		DacClient db = new DacClient();
		try {
			
			StringBuffer sql = new StringBuffer();
			StringBuffer sql1 = new StringBuffer();
			List oParam = new ArrayList();
			
			buildSQL(_map,sql,oParam);	
			
			sql1.append("select count(1) from(");
			sql1.append(sql).append(")");
			
			Object ap[] = new Object[oParam.size()];
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			
			String count = db.getStringFromSqlQuery(sql1.toString(), ap);
			//System.out.println("记录数：" + count);
			return Integer.parseInt(count);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return 0;
	}
	
	private void buildSelectSQL(Map _map, StringBuffer selectSql, List oParam,int orderType) {
		if(orderType == 0) {
			selectSql.append("select t11.cityname \"地区\",t1.citycode,nvl(t1.cnt, 0) \"业务受理单超时\",nvl(t2.cnt, 0) \"业务受理单未超时\",");
			selectSql.append("decode(nvl(t1.cnt, 0) + nvl(t2.cnt, 0),'0','',round(nvl(t1.cnt, 0) / (nvl(t1.cnt, 0) + nvl(t2.cnt, 0)) * 100, 2) || '%') \"业务受理单超时百分比\",");
			selectSql.append("decode(nvl(t1.cnt, 0) + nvl(t2.cnt, 0),'0','',round(nvl(t2.cnt, 0) / (nvl(t1.cnt, 0) + nvl(t2.cnt, 0)) * 100, 2) || '%') \"业务受理单未超时百分比\",");
			selectSql.append("nvl(t3.cnt, 0) \"程控受理单超时\",nvl(t4.cnt, 0) \"程控受理单未超时\",");
			selectSql.append("decode(nvl(t3.cnt, 0) + nvl(t4.cnt, 0),'0','',round(nvl(t3.cnt, 0) / (nvl(t3.cnt, 0) + nvl(t4.cnt, 0)) * 100, 2) || '%') \"程控受理单超时百分比\",");
			selectSql.append("decode(nvl(t3.cnt, 0) + nvl(t4.cnt, 0),'0','',round(nvl(t4.cnt, 0) / (nvl(t3.cnt, 0) + nvl(t4.cnt, 0)) * 100, 2) || '%') \"程控受理单未超时百分比\",");
			selectSql.append("nvl(t5.cnt, 0) \"故障申告单超时\",nvl(t6.cnt, 0) \"故障申告单未超时\",");
			selectSql.append("decode(nvl(t5.cnt, 0) + nvl(t6.cnt, 0),'0','',round(nvl(t5.cnt, 0) / (nvl(t5.cnt, 0) + nvl(t6.cnt, 0)) * 100, 2) || '%') \"故障申告单超时百分比\",");
			selectSql.append("decode(nvl(t5.cnt, 0) + nvl(t6.cnt, 0),'0','',round(nvl(t6.cnt, 0) / (nvl(t5.cnt, 0) + nvl(t6.cnt, 0)) * 100, 2) || '%') \"故障申告单未超时百分比\",");
			selectSql.append("nvl(t7.cnt, 0) \"服务投诉单超时\",nvl(t8.cnt, 0) \"服务投诉单未超时\",");
			selectSql.append("decode(nvl(t7.cnt, 0) + nvl(t8.cnt, 0),'0','',round(nvl(t7.cnt, 0) / (nvl(t7.cnt, 0) + nvl(t8.cnt, 0)) * 100, 2) || '%') \"服务投诉单超时百分比\",");
			selectSql.append("decode(nvl(t7.cnt, 0) + nvl(t8.cnt, 0),'0','',round(nvl(t8.cnt, 0) / (nvl(t7.cnt, 0) + nvl(t8.cnt, 0)) * 100, 2) || '%') \"服务投诉单未超时百分比\",");
			selectSql.append("nvl(t9.cnt, 0) \"建议单超时\",nvl(t10.cnt, 0) \"建议单未超时\",");
			selectSql.append("decode(nvl(t9.cnt, 0) + nvl(t10.cnt, 0),'0','',round(nvl(t9.cnt, 0) / (nvl(t9.cnt, 0) + nvl(t10.cnt, 0)) * 100, 2) || '%') \"建议单超时百分比\",");
			selectSql.append("decode(nvl(t9.cnt, 0) + nvl(t10.cnt, 0),'0','',round(nvl(t10.cnt, 0) / (nvl(t9.cnt, 0) + nvl(t10.cnt, 0)) * 100, 2) || '%') \"建议单未超时百分比\",");
			selectSql.append("nvl(t1.cnt, 0) + nvl(t3.cnt, 0) + nvl(t5.cnt, 0) + nvl(t7.cnt, 0) + nvl(t9.cnt, 0) \"超时\",");
			selectSql.append("nvl(t2.cnt, 0) + nvl(t4.cnt, 0) + nvl(t6.cnt, 0) + nvl(t8.cnt, 0) + nvl(t10.cnt, 0) \"未超时\",");
			selectSql.append("nvl(t1.cnt, 0) + nvl(t2.cnt, 0) + nvl(t3.cnt, 0) + nvl(t4.cnt, 0) + nvl(t5.cnt, 0) + ");
			selectSql.append("nvl(t6.cnt, 0) + nvl(t7.cnt, 0) + nvl(t8.cnt, 0) + nvl(t9.cnt, 0) + nvl(t10.cnt, 0) \"总计\" ");
		}
		else if(orderType == 1 || orderType == 2) {
			selectSql.append("select substr(a.weborder, 5, 4) citycode, count(1) cnt ");
		}
		else if(orderType == 3 || orderType == 4) {
			selectSql.append("select substr(b.weborder, 5, 4) citycode, count(1) cnt ");
		}
		else {
			selectSql.append("select substr(c.weborder, 5, 4) citycode, count(1) cnt ");
		}

	}
	
	private void buildWhereSQL(Map _map, StringBuffer whereSql, List oParam,int orderType) {
		String sCityCode = (String) _map.get("CITYCODE");
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		//sRptType为1时是第一次受理情况，为2时是受理完毕受理情况
		String sRptType = (String) _map.get("RPTTYPE");
		
		String timeTitle = "";
		String byName = "";
		
		if(orderType == 0) {
			byName = "t1.";
		}
		else if(orderType == 1 || orderType == 2) {
			timeTitle = "accepttime";
			byName = "a.";
		}
		else if(orderType == 3 || orderType == 4) {
			timeTitle = "accepttime";
			byName = "b.";
		}
		else {
			timeTitle = "createtime";
			byName = "c.";
		}
		
		if(orderType == 0) {
			whereSql.append("where t11.citycode = t1.citycode(+) and t11.citycode = t2.citycode(+) and t1.citycode = t3.citycode(+) and ");
			whereSql.append("t11.citycode = t4.citycode(+) and t11.citycode = t5.citycode(+) and ");
			whereSql.append("t11.citycode = t6.citycode(+) and t11.citycode = t7.citycode(+) and ");
			whereSql.append("t11.citycode = t8.citycode(+) and t11.citycode = t9.citycode(+) and ");
			whereSql.append("t11.citycode = t10.citycode(+) ");
		}
		else if(orderType == 3) {
			whereSql.append("where (");
			whereSql.append("(b.finishtime is null and b.progressstate != '3' and sysdate - b.accepttime > 1) or ");
			whereSql.append("(b.finishtime is not null and b.progressstate = '3' and (b.finishtime - b.accepttime) > 1) ");
			whereSql.append(") ");
		}
		else if(orderType == 4) {
			whereSql.append("where (");
			whereSql.append("(b.finishtime is null and b.progressstate != '3' and sysdate - b.accepttime <= 1) or ");
			whereSql.append("(b.finishtime is null and b.progressstate = '3') or ");
			whereSql.append("(b.finishtime is not null and b.progressstate = '3' and (b.finishtime - b.accepttime) < 1) ");
			whereSql.append(") ");
		}
		else if(orderType == 1 || orderType == 5 || orderType == 7 || orderType == 9){
			whereSql.append("where (");
			if(sRptType == null || sRptType.equals("1")) {
				whereSql.append("("+byName+"finishtime is null and "+byName+"outdate is null and "+byName+"progressstate != '3' and sysdate - "+byName+timeTitle+" > 1) or ");
				whereSql.append("("+byName+"finishtime is null and "+byName+"outdate is not null and "+byName+"progressstate != '3' and sysdate > "+byName+"outdate) or ");
			}
			whereSql.append("("+byName+"finishtime is not null and "+byName+"outdate is null and "+byName+"progressstate = '3' and ("+byName+"finishtime - "+byName+timeTitle+") > 1) or ");
			whereSql.append("("+byName+"finishtime is not null and "+byName+"outdate is not null and "+byName+"progressstate = '3' and "+byName+"finishtime > "+byName+"outdate) ");
			whereSql.append(") ");
			if(orderType == 5) {
				whereSql.append("and c.appealmode = '1' ");
			}
			else if(orderType == 7) {
				whereSql.append("and c.appealmode = '2' ");
			}
			else if(orderType == 9) {
				whereSql.append("and c.appealmode = '3' ");
			}
		}
		else {
			whereSql.append("where (");
			if(sRptType == null || sRptType.equals("1")) {
				whereSql.append("("+byName+"finishtime is null and "+byName+"outdate is null and "+byName+"progressstate != '3' and sysdate - "+byName+timeTitle+" <= 1) or ");
				whereSql.append("("+byName+"finishtime is null and "+byName+"outdate is not null and "+byName+"progressstate != '3' and sysdate <= "+byName+"outdate) or ");
			}
			whereSql.append("("+byName+"finishtime is null and "+byName+"progressstate = '3') or ");
			whereSql.append("("+byName+"finishtime is not null and "+byName+"outdate is null and "+byName+"progressstate = '3' and ("+byName+"finishtime - "+byName+timeTitle+") <= 1) or ");
			whereSql.append("("+byName+"finishtime is not null and "+byName+"outdate is not null and "+byName+"progressstate = '3' and "+byName+"finishtime <= "+byName+"outdate) ");
			whereSql.append(") ");
			if(orderType == 6) {
				whereSql.append("and c.appealmode = '1' ");
			}
			else if(orderType == 8) {
				whereSql.append("and c.appealmode = '2' ");
			}
			else if(orderType == 10) {
				whereSql.append("and c.appealmode = '3' ");
			}
		}
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			if(sCityCode.equals("-0590")) {
				sCityCode = "0590";
			}
			if(orderType == 0)
				whereSql.append("and "+byName+"citycode = ? ");
			else 
				whereSql.append("and substr("+byName+"weborder, 5, 4) = ? ");
			oParam.add(sCityCode);
		}

		if (orderType != 0 && !ExtString.isEmpty(sStartTime)) {
			whereSql.append("and ");
			whereSql.append(byName+timeTitle+" >= to_date(? || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sStartTime);
		}

		if (orderType != 0 && !ExtString.isEmpty(sEndTime)) {
			whereSql.append("and ");
			whereSql.append(byName+timeTitle+" <= to_date(? || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sEndTime);
		}
	}
	
	private void buildGroupBySQL(Map _map, StringBuffer groupBySql, List oParam,int orderType) {
		if(orderType == 0) {
		}
		else if(orderType == 1 || orderType == 2) {
			groupBySql.append("group by substr(a.weborder, 5, 4) ");
		}
		else if(orderType == 3 || orderType == 4) {
			groupBySql.append("group by substr(b.weborder, 5, 4) ");
		}
		else {
			groupBySql.append("group by substr(c.weborder, 5, 4) ");
		}
	}
	
	private void buildProdorderSQL(Map _map, StringBuffer prodorderSql, List oParam,boolean isOverTime) {
		int orderType = 1;
		if (isOverTime)
			orderType = 1;
		else 
			orderType = 2;
		StringBuffer selectSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		StringBuffer groupBySql = new StringBuffer();
		StringBuffer orderSql = new StringBuffer();
		
		buildSelectSQL(_map,selectSql,oParam,orderType);
		prodorderSql.append(selectSql);
		buildOrderSQL(_map,orderSql,oParam,"prodorder");
		prodorderSql.append("from ").append(orderSql).append(" a ");
		buildWhereSQL(_map,whereSql,oParam,orderType);
		prodorderSql.append(whereSql);
		buildGroupBySQL(_map,groupBySql,oParam,orderType);
		prodorderSql.append(groupBySql);
	}
	
	private void buildFunorderSQL(Map _map, StringBuffer funorderSql, List oParam,boolean isOverTime) {
		int orderType = 3;
		if (isOverTime)
			orderType = 3;
		else 
			orderType = 4;
		StringBuffer selectSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		StringBuffer groupBySql = new StringBuffer();
		
		buildSelectSQL(_map,selectSql,oParam,orderType);
		funorderSql.append(selectSql);
		funorderSql.append("from tf_funorder b ");
		buildWhereSQL(_map,whereSql,oParam,orderType);
		funorderSql.append(whereSql);
		buildGroupBySQL(_map,groupBySql,oParam,orderType);
		funorderSql.append(groupBySql);
	}
	
	/**找出第一次受理的子单，和没有子单的原单
	 * 
	 * @param _map
	 * @param firstOrderSql
	 * @param oParam
	 */
	private void buildOrderSQL(Map _map, StringBuffer orderSql, List oParam,String orderType) {
		//sRptType为1时是第一次受理情况，为2时是受理完毕受理情况
		String sRptType = (String) _map.get("RPTTYPE");
		String timeName = "";
		String tableName = "";
		String idName = "";
		String upidName = "";
		String modeNameA = "";
		String modeNameB = "";
		if(orderType.equals("prodorder")) {
			timeName = "accepttime";
			tableName = "tf_prodorder";
			idName = "prodorderid";
			upidName = "upprodorderid";
		}
		else if(orderType.equals("appeal")) {
			timeName = "createtime";
			tableName = "tf_appeal";