acceptanalyselogimpl.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.rpt;

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

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

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

public class AcceptanalyselogImpl extends ReportImpl {
	
	
	/**用于注册帐号统计页面获取报表
	 * 
	 */
	protected DataTable getRptList(Map _map) {
		DataTable dt = null;
		//String sQueryType = (String) _map.get("queryType");
		//if(sQueryType == null || sQueryType.equals("0")) {
		dt = getAcceptResultList(_map);
		//}
		return dt;
	}
	
	/**注册帐号统计列表
	 * 
	 * @param _map
	 * @return
	 */
	private DataTable getAcceptResultList(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()];
			//System.out.println("&&&&&&&&&&&&&&&&&"+sql);
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
				//System.out.println("&&&&&&&&&&&&&&&&&"+ap[i]);
			}
			return db.executeQuery(_sb.toString(), ap);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return null;
	}
	
	

	
	/**用于程控功能受理情况导出Excel页面获取报表
	 * 
	 */
	private DataTable getAllAcceptResultList(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;
	}
	
	/**用于程控功能受理情况导出Excel页面获取报表
	 * 
	 */
	public DataTable getAllList(Map _map) {
		DataTable dt = null;
		//String sQueryType = (String) _map.get("queryType");
		//if(sQueryType == null || sQueryType.equals("0")) {
		dt = getAllAcceptResultList(_map);
		//}
		
		return dt;
	}
	
	/**获取程控功能受理情况按条件总计的条数
	 * 
	 * @param _map
	 * @return
	 */
	private int getAccessLogSum(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 \"过期数量\")+\"激活数量\"+\"未激活量\"+\"注销数量\"+\"产品注册量\" 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 sum = db.getStringFromSqlQuery(sql1.toString(), ap);
			//System.out.println("总记数：" + sum);
			return Integer.parseInt(sum);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return 0;

	}
	
	public String getSum(ServletRequest request, ServletResponse response, Map _map) {
		String sum = "0";
		//String sQueryType = (String) _map.get("queryType");
		//if(sQueryType == null || sQueryType.equals("0")) {
		sum = String.valueOf(getAccessLogSum(_map));
		//}
		return sum;
		
	}
	
	/**用于分页
	 * 
	 */
	protected int getRptListCount(Map _map) {
		int count = 0;
		//String sQueryType = (String) _map.get("queryType");
		//if(sQueryType == null || sQueryType.equals("0")) {
		count =getAccessLogCount(_map);
		//}
		return count;
	}
	
	private int getAccessLogCount(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 buildWhereSQL(Map _map, StringBuffer whereSql, List oParam) {
		String sCityCode = (String) _map.get("CITYCODE");
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String timeFormat = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, sStartTime, sEndTime);
		timeFormat = result[1];
		sStartTime = result[2];
		sEndTime = result[3];
		
		whereSql.append("where r.nodeid = t.nodeid ");
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			whereSql.append("and t.citycode = ? ");
			oParam.add(sCityCode);
		}
		
		if (!ExtString.isEmpty(sStartTime)) {
//			if(ExtString.isEmpty(sCityCode) || sCityCode.equals("0590")) 
//				whereSql.append("where ");
//			else
				whereSql.append("and ");
			if(sStatisticType != null) {
				if(sStatisticType.equals("1")) {
					whereSql.append("t.CALC_TIME >= to_date(?, 'yyyy-mm-dd') ");
				}
				else{
					whereSql.append("t.CALC_TIME >= to_date(?, '"+timeFormat+"') ");
				}
			}
			else {
				whereSql.append("t.CALC_TIME >= to_date(?, '"+timeFormat+"') ");
			}
			
			oParam.add(sStartTime);
		}
		if (!ExtString.isEmpty(sEndTime)) {
//			if((ExtString.isEmpty(sCityCode) && ExtString.isEmpty(sStartTime)) || 
//					(sCityCode.equals("0590") && ExtString.isEmpty(sStartTime)))
//				whereSql.append("where ");
//			else
				whereSql.append("and ");
			if(sStatisticType != null) {
				if(sStatisticType.equals("1")) {
					whereSql.append("t.CALC_TIME <= to_date(?, 'yyyy-mm-dd hh24:mi:ss') ");
				}
				else{
					whereSql.append("t.CALC_TIME <= to_date(?, '"+timeFormat+"') ");
				}
			}
			else {
				whereSql.append("t.CALC_TIME <= to_date(?, '"+timeFormat+"') ");
			}
			
			oParam.add(sEndTime);
		}

	}
	
	private void buildGroupBySQL(Map _map, StringBuffer groupBySql, List oParam) {
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String str = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, "", "");
		str = result[0];
		
		groupBySql.append("group by to_char(t.CALC_TIME, '"+str+"') ");
	}
	
	private void buildSelectSQL(Map _map, StringBuffer selectSql, List oParam) {
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String str = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, "", "");
		str = result[0];
		
		selectSql.append("select to_char(t.CALC_TIME, '"+str+"') time1, sum(t.AMOUNT) cnt ");
	}
	
	private void buildOperAcceptTotalSQL(Map _map, StringBuffer operAcceptTotaSql, List oParam) {
		StringBuffer selectSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		StringBuffer groupBySql = new StringBuffer();
		buildSelectSQL(_map,selectSql,oParam);
		operAcceptTotaSql.append(selectSql);
		operAcceptTotaSql.append("from tl_reportofday t, td_report_node r ");
		buildWhereSQL(_map,whereSql,oParam);
		operAcceptTotaSql.append(whereSql);
		operAcceptTotaSql.append("and r.indexcode like '005%' ");
		//operAcceptTotaSql.append("and r.indexcode <> '005002001' ");
		
		buildGroupBySQL(_map,groupBySql,oParam);
		operAcceptTotaSql.append(groupBySql);
	}
	
	private void buildFunorderSQL(Map _map, StringBuffer custServSql, List oParam) {
		StringBuffer selectSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		StringBuffer groupBySql = new StringBuffer();
		buildSelectSQL(_map,selectSql,oParam);
		custServSql.append(selectSql);
		custServSql.append("from tl_reportofday t, td_report_node r ");
		buildWhereSQL(_map,whereSql,oParam);
		custServSql.append(whereSql);
		//2007.01.22
		//002002014是我的E家办理,是合在自助服务002002中,要求单独一列显示,故提取出来
		custServSql.append("and r.indexcode like '002002%' and r.indexcode not like '002002014%'");
		
		buildGroupBySQL(_map,groupBySql,oParam);
		custServSql.append(groupBySql);
	}
	
	private void buildCustServSQL(Map _map, StringBuffer custServSql, List oParam) {
		StringBuffer selectSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		StringBuffer groupBySql = new StringBuffer();
		buildSelectSQL(_map,selectSql,oParam);
		custServSql.append(selectSql);
		custServSql.append("from tl_reportofday t, td_report_node r ");
		buildWhereSQL(_map,whereSql,oParam);
		custServSql.append(whereSql);
		custServSql.append("and r.indexcode like '002001%' ");
		buildGroupBySQL(_map,groupBySql,oParam);
		custServSql.append(groupBySql);
	}
	//2007.1.22
	//我的E家套餐办理
	private void buildHomeorderSQL(Map _map, StringBuffer custServSql, List oParam) {
		StringBuffer selectSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		StringBuffer groupBySql = new StringBuffer();
		buildSelectSQL(_map,selectSql,oParam);
		custServSql.append(selectSql);
		custServSql.append("from tl_reportofday t, td_report_node r ");
		buildWhereSQL(_map,whereSql,oParam);
		custServSql.append(whereSql);
		custServSql.append("and  r.indexcode  like '002002014%'");
		
		buildGroupBySQL(_map,groupBySql,oParam);
		custServSql.append(groupBySql);
	}
	private void buildAcceptTotalSQL(Map _map, StringBuffer acceptTotalSql, List oParam) {
		StringBuffer selectSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		StringBuffer groupBySql = new StringBuffer();
		buildSelectSQL(_map,selectSql,oParam);
		acceptTotalSql.append(selectSql);
		acceptTotalSql.append("from tl_reportofday t, td_report_node r ");
		buildWhereSQL(_map,whereSql,oParam);
		acceptTotalSql.append(whereSql);
		acceptTotalSql.append("and (r.indexcode like '002001%' or r.indexcode like '002002%')  ");
		buildGroupBySQL(_map,groupBySql,oParam);
		acceptTotalSql.append(groupBySql);
	}
	
	
	private void buildSQL(Map _map, StringBuffer sql, List oParam) {
		StringBuffer operAcceptTotaSql = new StringBuffer();
		StringBuffer custServSql = new StringBuffer();
		StringBuffer funorderSql = new StringBuffer();
		StringBuffer homeorderSql = new StringBuffer();
		StringBuffer acceptTotalSql = new StringBuffer();
		
		sql.append("select d.time1 \"时间\",nvl(c.cnt,0) \"自助服务总量\",nvl(b.cnt,0) \"客户服务总量\",nvl(e.cnt,0) \"我的e家受理总量\",");
		sql.append("nvl(d.cnt,0) \"受理总量\" from (");
	//	buildOperAcceptTotalSQL(_map,operAcceptTotaSql,oParam);
	//	sql.append(operAcceptTotaSql);
	//	sql.append(") a,(");
		buildCustServSQL(_map,custServSql,oParam);
		sql.append(custServSql);
		sql.append(") b,(");
		buildFunorderSQL(_map,funorderSql,oParam);
		sql.append(funorderSql);
		sql.append(") c,(");
		buildHomeorderSQL(_map,homeorderSql,oParam);
		sql.append(homeorderSql);
		sql.append(") e,(");
		buildAcceptTotalSQL(_map,acceptTotalSql,oParam);
		sql.append(acceptTotalSql);
		//sql.append(") d where d.time1 = a.time1(+) ");
		sql.append(") d where  ");
		sql.append(" d.time1 = b.time1(+) ");
		sql.append("and d.time1 = e.time1(+) ");
		sql.append("and d.time1 = c.time1(+) ");
		sql.append("order by d.time1 desc ");
	}	
	
}