prodfunlogimpl.java

电信的网厅的整站代码

		applySql.append(" group by trunc(t3.accepttime)");
	}
	
	private void buildAccessSQL(Map _map, StringBuffer accessSql, List oParam) {
		String sCityCode = (String) _map.get("CITYCODE");
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		
		accessSql.append("select trunc(t4.accepttime) time4,count(1) cnt4 from tf_funorder t4,td_prodfun t9,tf_custinfoweb t ");
		accessSql.append("where t9.funtype = ? and t4.prodfunid = t9.prodfunid and t4.progressstate='2' ");
		String sFuntype = getFunType(_map);
		oParam.add(sFuntype);
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			accessSql.append("and t4.citycode = ? ");
			oParam.add(sCityCode);
		}
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			accessSql.append("and t9.citycode = ? ");
			oParam.add(sCityCode);
		}
		
		if (!ExtString.isEmpty(sStartTime)) {
			accessSql.append("and ");
			accessSql.append("t4.accepttime >= to_date(? || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sStartTime);
		}

		if (!ExtString.isEmpty(sEndTime)) {
			accessSql.append("and ");
			accessSql.append("t4.accepttime <= to_date(? || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sEndTime);
		}
		
		String sProdfun = (String) _map.get("PRODFUN");
		if (!ExtString.isEmpty(sProdfun) && !sProdfun.equals("0")) {
			accessSql.append("and ");
			accessSql.append("t4.prodfunid = ? ");
			oParam.add(sProdfun);
		}
		accessSql.append(" and t.userid=t4.userid and t.custtypeid!='11' and t.custtypeid!='7' and t.custtypeid!='12' and t.state='E' ");
		accessSql.append(" group by trunc(t4.accepttime)");
	}
	
	private void buildWaitSQL(Map _map, StringBuffer waitSql, List oParam) {
		String sCityCode = (String) _map.get("CITYCODE");
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		
		waitSql.append("select trunc(t5.accepttime) time5,count(1) cnt5 from tf_funorder t5,td_prodfun t10,tf_custinfoweb t ");
		waitSql.append("where t10.funtype = ? and t5.prodfunid = t10.prodfunid and t5.progressstate='1' ");
		String sFuntype = getFunType(_map);
		oParam.add(sFuntype);
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			waitSql.append("and t5.citycode = ? ");
			oParam.add(sCityCode);
		}
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			waitSql.append("and t10.citycode = ? ");
			oParam.add(sCityCode);
		}		

		if (!ExtString.isEmpty(sStartTime)) {
			waitSql.append("and ");
			waitSql.append("t5.accepttime >= to_date(? || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sStartTime);
		}

		if (!ExtString.isEmpty(sEndTime)) {
			waitSql.append("and ");
			waitSql.append("t5.accepttime <= to_date(? || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sEndTime);
		}
		
		String sProdfun = (String) _map.get("PRODFUN");
		if (!ExtString.isEmpty(sProdfun) && !sProdfun.equals("0")) {
			waitSql.append("and ");
			waitSql.append("t5.prodfunid = ? ");
			oParam.add(sProdfun);
		}
		waitSql.append(" and t.userid=t5.userid and t.custtypeid!='11' and t.custtypeid!='7' and t.custtypeid!='12' and t.state='E' ");
		waitSql.append(" group by trunc(t5.accepttime)");
	}
	
	private void buildAcceptResultSQL(Map _map, StringBuffer sql, List oParam) {
		StringBuffer totalSql = new StringBuffer();
		StringBuffer succeedSql = new StringBuffer();
		StringBuffer failSql = new StringBuffer();
		StringBuffer nullitySql = new StringBuffer();
		StringBuffer accessSql = new StringBuffer();
		StringBuffer waitSql = new StringBuffer();
		StringBuffer applySql = new StringBuffer();
		StringBuffer cancelSql = new StringBuffer();
		StringBuffer closeSql = new StringBuffer();
		StringBuffer setSql = new StringBuffer();
		StringBuffer changeSql = new StringBuffer();
		String sFuntype = getFunType(_map);
		
		sql.append("select to_char(p1.time1, 'yyyy-mm-dd') \"受理时间\",p1.cnt1 \"受理总量\",nvl(p2.cnt2, 0) \"受理成功总量\",");
		sql.append("nvl(p3.cnt3, 0) \"受理失败总量\",nvl(p4.cnt4, 0) \"受理中总量\",");
		sql.append("nvl(p5.cnt5, 0) \"等待受理总量\",nvl(p6.cnt3, 0) \"无效受理总量\"");
		if(sFuntype.equals("C")) {
			//sql.append(",nvl(p7.cnt3, 0) \"申请总量\",nvl(p8.cnt3, 0) \"取消总量\",nvl(p9.cnt3, 0) \"关闭总量\"");
			sql.append(",nvl(p7.cnt3, 0) \"开/套餐新装总量\",nvl(p10.cnt3, 0) \"设置总量\",nvl(p11.cnt3, 0) \"变更总量\",nvl(p8.cnt3, 0) \"取消总量\",nvl(p9.cnt3, 0) \"关闭总量\"");
		}
		sql.append(" from (");
		buildTotalSQL(_map,totalSql,oParam);
		sql.append(totalSql);
		sql.append(") p1,(");
		buildSucceedSQL(_map,succeedSql,oParam);
		sql.append(succeedSql);
		sql.append(") p2,(");
		buildFailSQL(_map,failSql,oParam);
		sql.append(failSql);
		sql.append(") p3,(");
		buildAccessSQL(_map,accessSql,oParam);
		sql.append(accessSql);
		sql.append(") p4,(");
		buildWaitSQL(_map,waitSql,oParam);
		sql.append(waitSql);
		sql.append(") p5,(");
		buildNullitySQL(_map,nullitySql,oParam);
		sql.append(nullitySql);
		sql.append(") p6 ");
		if(sFuntype.equals("C")) {
			sql.append(",(");
			buildActionSQL(_map,applySql,oParam,"1");
			sql.append(applySql);
			sql.append(") p7,(");
			buildActionSQL(_map,setSql,oParam,"3");
			sql.append(setSql);
			sql.append(") p10,(");
			buildActionSQL(_map,changeSql,oParam,"5");
			sql.append(changeSql);
			sql.append(") p11,(");
			buildActionSQL(_map,cancelSql,oParam,"4");
			sql.append(cancelSql);
			sql.append(") p8,(");
			buildActionSQL(_map,closeSql,oParam,"2");
			sql.append(closeSql);
			sql.append(") p9 ");
		}
		sql.append("where p1.time1 = p2.time2(+) and p1.time1 = p3.time3(+) ");
		sql.append("and p1.time1 = p4.time4(+) and p1.time1 = p5.time5(+) and p1.time1 = p6.time3(+) ");
		if(sFuntype.equals("C")) {
			sql.append("and p1.time1 = p7.time3(+) and p1.time1 = p8.time3(+) and p1.time1 = p9.time3(+) ");
			sql.append(" and p1.time1 = p10.time3(+) and p1.time1 = p11.time3(+) ");
		}
		sql.append("order by to_char(p1.time1, 'yyyy-mm-dd') desc ");
	}
	
	private void buildFailCauseSQL(Map _map, StringBuffer sql, List oParam) {
		StringBuffer failLogSql = new StringBuffer();
		StringBuffer failCauseSql = new StringBuffer();
		String sCityCode = (String) _map.get("CITYCODE");
		String sTime = (String) _map.get("TIME");
		String sQueryType = (String) _map.get("queryType");
		String sProdfun = (String) _map.get("PRODFUN");
		String sFunName =getFunName(_map);
		String sFuntype =getFunType(_map);
		sql.append("select p1.weborder \"受理流水号\",p1.phonenum \"电话号码\",to_char(p1.accepttime,'yyyy-mm-dd hh24:mi:ss') \"受理时间\", p2.funname \"所操作"+sFunName+"名称\",");
		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append(" p4.funname \"变更成的套餐名\",p2.funcode,");
		}
		sql.append("p3.paradesc \"失败原因\" from (");
		
		failLogSql.append("select t1.weborder,t1.phonenum,t1.accepttime,t1.prodfunid,t1.progressstate,t1.citycode ");
		if(sFuntype != null && sFuntype.equals("T")) {
			failLogSql.append(",t1.content,t2.productid,t2.funname ");
		}
		failLogSql.append("from tf_funorder t1,td_prodfun t2 ,tf_custinfoweb t ");
		failLogSql.append("where t2.funtype = ? ");
		oParam.add(sFuntype);
		failLogSql.append(" and t.userid=t1.userid and t.custtypeid!='11' and t.custtypeid!='7' and t.custtypeid!='12' and t.state='E' ");
		failLogSql.append("and t1.prodfunid = t2.prodfunid ");
		
		if(sQueryType.equals("4"))
			failLogSql.append("and (substrb(t1.progressstate,4) < '0' or t1.progressstate in ('4','f','F','911','not' )) ");
		else if(sQueryType.equals("ohter"))
			failLogSql.append("and (substrb(t1.progressstate,4) >= '0' or t1.progressstate = '119')");
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			failLogSql.append("and t1.citycode=? ");
			oParam.add(sCityCode);
		}
		failLogSql.append("and trunc(t1.accepttime) = to_date(?, 'yyyy-mm-dd') ");
		oParam.add(sTime);
		sql.append(failLogSql);
		
		sql.append(") p1,td_prodfun p2,(");
		failCauseSql.append("select t2.paravalue,t2.paradesc,t2.citycode from ts_configsub t2 ");
		failCauseSql.append("where t2.paraname='FunFailureStateLogDsec' ");
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			failCauseSql.append("and t2.citycode=? ");
			oParam.add(sCityCode);
		}
		sql.append(failCauseSql);
		sql.append(") p3 ");
		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append(",td_prodfun p4 ");
		}
		
		sql.append("where p1.prodfunid = p2.prodfunid and p1.progressstate = p3.paravalue(+) and p1.citycode = p3.citycode(+) ");
		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append("and p1.content = p4.funcode ");
			sql.append("and p1.productid = p4.productid ");
			sql.append("and p1.citycode = p4.citycode ");
		}
		
		if (!ExtString.isEmpty(sProdfun) && !sProdfun.equals("0")) {
			sql.append("and ");
			sql.append("p2.prodfunid = ? ");
			oParam.add(sProdfun);
		}
	}
	
	private void buildEverySQL(Map _map, StringBuffer sql, List oParam,String action) {
		StringBuffer everySql = new StringBuffer();
		StringBuffer stateSql = new StringBuffer();
		String sCityCode = (String) _map.get("CITYCODE");
		String sTime = (String) _map.get("TIME");
		String sFunName =getFunName(_map);
		String sFuntype =getFunType(_map);
		sql.append("select p1.weborder \"受理流水号\",p1.phonenum \"电话号码\",to_char(p1.accepttime,'yyyy-mm-dd hh24:mi:ss') \"受理时间\", ");
		sql.append("to_char(p1.finishtime, 'yyyy-mm-dd hh24:mi:ss') \"完成时间\", ");
		sql.append("p2.funname \"所操作"+sFunName+"名称\",p1.action \"操作\",");
		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append(" p4.funname \"变更成的套餐名\",p2.funcode,");
		}
		sql.append("decode(p3.paradesc,'处理结束','受理成功',p3.paradesc) \"处理状态\" from (");
		
		everySql.append("select t1.weborder,t1.phonenum,t1.accepttime,t1.finishtime,t1.prodfunid,t1.progressstate,t1.citycode ");
		everySql.append(", decode(t1.action,'1','申请','2','关','3','设置','4','取消设置','5','变更') action ");
		if(sFuntype != null && sFuntype.equals("T")) {
			everySql.append(",t1.content,t2.productid,t2.funname ");
		}
		everySql.append("from tf_funorder t1,td_prodfun t2 ,tf_custinfoweb t ");
		everySql.append("where t2.funtype = ? ");
		oParam.add(sFuntype);
		everySql.append(" and t.userid=t1.userid and t.custtypeid!='11' and t.custtypeid!='7' and t.custtypeid!='12' and t.state='E' ");
		if(action != null && !action.equals("")) {
			everySql.append("and t1.action = ? ");
			oParam.add(action);
		}
		everySql.append("and t1.prodfunid = t2.prodfunid ");
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			everySql.append("and t1.citycode=? ");
			oParam.add(sCityCode);
		}
		everySql.append("and trunc(t1.accepttime) = to_date(?, 'yyyy-mm-dd') ");
		oParam.add(sTime);
		sql.append(everySql);
		
		sql.append(") p1,td_prodfun p2,(");
		stateSql.append("select t2.paravalue,t2.paradesc,t2.citycode from ts_configsub t2 ");
		stateSql.append("where t2.paraname='FunFailureStateLogDsec' ");
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			stateSql.append("and t2.citycode=? ");
			oParam.add(sCityCode);
		}
		sql.append(stateSql);
		sql.append(") p3 ");
		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append(",td_prodfun p4 ");
		}
		
		sql.append("where p1.prodfunid = p2.prodfunid and p1.progressstate = p3.paravalue(+) and p1.citycode = p3.citycode(+) ");
		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append("and p1.content = p4.funcode ");
			sql.append("and p1.productid = p4.productid ");
			sql.append("and p1.citycode = p4.citycode ");
		}
		
		String sProdfun = (String) _map.get("PRODFUN");
		if (!ExtString.isEmpty(sProdfun) && !sProdfun.equals("0")) {
			sql.append("and ");
			sql.append("p2.prodfunid = ? ");
			oParam.add(sProdfun);
		}
		
	}
	
	private void buildListSQL(Map _map, StringBuffer sql, List oParam) {
		StringBuffer listSql = new StringBuffer();
		String sProgressstate = (String) _map.get("progressstate");
		String sCityCode = (String) _map.get("CITYCODE");
		String sTime = (String) _map.get("TIME");
		String sFunName =getFunName(_map);
		String sFuntype =getFunType(_map);
		
		sql.append("select p1.weborder \"受理流水号\",p1.phonenum \"电话号码\",to_char(p1.accepttime,'yyyy-mm-dd hh24:mi:ss') \"受理时间\", ");
		if(sProgressstate.equals("3"))  sql.append(" to_char(p1.finishtime, 'yyyy-mm-dd hh24:mi:ss') \"完成时间\", ");
		sql.append("p2.funname \"所操作"+sFunName+"名称\" ");
		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append(",p3.funname \"变更成的套餐名\",p2.funcode ");
		}
		sql.append("from (");
		
		listSql.append("select t1.weborder,t1.phonenum,t1.accepttime,t1.prodfunid,t1.citycode ");
		if(sProgressstate.equals("3")) listSql.append(",t1.finishtime ");
		if(sFuntype != null && sFuntype.equals("T")) {
			listSql.append(",t1.content,t2.productid,t2.funname ");
		}
		listSql.append("from tf_funorder t1,td_prodfun t2 ,tf_custinfoweb t ");
		listSql.append("where t2.funtype = ? ");
		oParam.add(sFuntype);
		listSql.append(" and t.userid=t1.userid and t.custtypeid!='11' and t.custtypeid!='7' and t.custtypeid!='12' and t.state='E' ");
		listSql.append("and t1.prodfunid = t2.prodfunid ");
		//if(sProgressstate.equals("3"))
			//listSql.append("and t1.progressstate in (?,'fun1') ");	
		//else 
			listSql.append("and t1.progressstate = ? ");
			
		oParam.add(sProgressstate);
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			listSql.append("and t1.citycode=? ");
			oParam.add(sCityCode);
		}
		listSql.append("and trunc(t1.accepttime) = to_date(?, 'yyyy-mm-dd') ");
		oParam.add(sTime);
		sql.append(listSql);
		sql.append(") p1,td_prodfun p2 ");

		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append(",td_prodfun p3 ");
		}
		sql.append("where p1.prodfunid = p2.prodfunid ");
		if(sFuntype != null && sFuntype.equals("T")) {
			sql.append("and p1.content = p3.funcode ");
			sql.append("and p1.productid = p3.productid ");
			sql.append("and p1.citycode = p3.citycode ");
		}
		
		String sProdfun = (String) _map.get("PRODFUN");
		if (!ExtString.isEmpty(sProdfun) && !sProdfun.equals("0")) {
			sql.append("and ");
			sql.append("p2.prodfunid = ? ");
			oParam.add(sProdfun);
		}

	}
	
	public static String getHistoryQueryType(HttpServletRequest request,HttpServletResponse response, Map _map) {
		String cityCode = "";
		try {
			cityCode = AccesslogImpl.getHistoryByName(request,response,_map,"queryType");
			if (cityCode == null) cityCode = "";
		}catch(Exception e) {
			FileLogger.getLogger().info(e.getMessage(),e);
		}
		return cityCode;
	}
	public static String getHistoryTime(HttpServletRequest request,HttpServletResponse response, Map _map) {
		String cityCode = "";
		try {
			cityCode = AccesslogImpl.getHistoryByName(request,response,_map,"TIME");
			if (cityCode == null) cityCode = "";
		}catch(Exception e) {
			FileLogger.getLogger().info(e.getMessage(),e);
		}
		return cityCode;
	}
	
	
}