accesslogimpl.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 javax.servlet.http.*;
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 AccesslogImpl extends ReportImpl {
	
	
	/**用于系统访问量页面获取报表
	 * 
	 */
	protected DataTable getRptList(Map _map) {
		DacClient db = new DacClient();
		try {
			StringBuffer sql = new StringBuffer();
			StringBuffer selectSql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer orderBySql = 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;

			String isLink = isLink(null,null,_map);
			
			if(isLink != null && isLink.equals("0")) {
				buildSelectSQL(_map, selectSql,oParam);
				sql.append(selectSql);
				sql.append("from tl_accesslog t1, td_report_tree t2, td_report_tree t3 ");
				buildWhereSQL(_map, whereSql, oParam);
				if (whereSql.length() > 0) {
					sql.append(whereSql);
				}
				buildGroupBySQL(_map, groupBySql);
				if (groupBySql.length() > 0) {
					sql.append(groupBySql);
				}
				buildOrderBySQL(_map, orderBySql);
				sql.append(orderBySql);
			}
			else {
				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);
			}

			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();
			StringBuffer selectSql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer orderBySql = new StringBuffer();
			List oParam = new ArrayList();
			
            String isLink = isLink(null,null,_map);
			
			if(isLink != null && isLink.equals("0")) {
				buildSelectSQL(_map, selectSql,oParam);
				sql.append(selectSql);
				sql.append("from tl_accesslog t1, td_report_tree t2, td_report_tree t3 ");
				buildWhereSQL(_map, whereSql, oParam);
				if (whereSql.length() > 0) {
					sql.append(whereSql);
				}
				buildGroupBySQL(_map, groupBySql);
				if (groupBySql.length() > 0) {
					sql.append(groupBySql);
				}
				buildOrderBySQL(_map, orderBySql);
				sql.append(orderBySql);
			}
			else {
				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;
	}
	
	/**获取系统访问量按条件总计的条数
	 * 
	 * @param _map
	 * @return
	 */
	private int getAccessLogSum(Map _map) {
		DacClient db = new DacClient();
		try {
			
			StringBuffer sql = new StringBuffer();
			StringBuffer sql1 = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			List oParam = new ArrayList();
			
			sql.append("select count(1) cnt ");
			sql.append("from tl_accesslog t1, td_report_tree t2, td_report_tree t3 ");
			buildWhereSQL(_map, whereSql, oParam);
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			buildGroupBySQL(_map, groupBySql);
			if (groupBySql.length() > 0) {
				sql.append(groupBySql);
			}
			
			sql1.append("select sum(cnt) 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);
			if(sum == null || sum.equals("") || sum.equals("")) sum = "0";
			return Integer.parseInt(sum);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return 0;

	}
	
	/**用于分页
	 * 
	 */
	protected int getRptListCount(Map _map) {
		DacClient db = new DacClient();
		try {
			
			StringBuffer sql = new StringBuffer();
			StringBuffer sql1 = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			List oParam = new ArrayList();
			
            String isLink = isLink(null,null,_map);
			
			if(isLink != null && isLink.equals("0")) {
				sql.append("select count(1) cnt ");
				sql.append("from tl_accesslog t1, td_report_tree t2, td_report_tree t3 ");
				buildWhereSQL(_map, whereSql, oParam);
				if (whereSql.length() > 0) {
					sql.append(whereSql);
				}
				buildGroupBySQL(_map, groupBySql);
				if (groupBySql.length() > 0) {
					sql.append(groupBySql);
				}
			}
			else {
				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);
				//System.out.println("ap[i]：" + ap[i]);
			}
			
			//System.out.println("_sb.toString()：" + sql1.toString());
			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 sPurviewId = (String) _map.get("pid");
		String sLevel = (String) _map.get("level");
		String sAreaCode = (String) _map.get("AREA");
		String sCityCode = (String) _map.get("CITYCODE");
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sClientType = (String) _map.get("CLIENTTYPE");
		
		whereSql.append("where t1.purviewid = t2.purviewid and t2.state='E' and t3.state='E' ");
		whereSql.append("and t3.indexcode = substr(t2.indexcode, 1, ?) ");
		oParam.add(sLevel);
		
		if(sPurviewId != null) {
			if(!sPurviewId.equals("959") && !sPurviewId.equals("1201")) {
				whereSql.append("and t2.indexcode like ? || '%'");
				TD_Report_tree td_report_tree = new TD_Report_tree(new DacClient(),Long.parseLong(sPurviewId));
				oParam.add(td_report_tree.getIndexCode());
			}
		}
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			if(sCityCode.equals("-0590")) {
				sCityCode = "0590";
			}
			whereSql.append("and t1.citycode = ? ");
			oParam.add(sCityCode);
		}
		
		if (!ExtString.isEmpty(sAreaCode) && !sAreaCode.equals("-1")) {
			whereSql.append("and t1.areacode = ? ");
			oParam.add(sAreaCode);
		}

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

		if (!ExtString.isEmpty(sEndTime)) {
			whereSql.append("and ");
			whereSql.append("t1.recordtime <= to_date(? || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sEndTime);
		}
		
		if (!ExtString.isEmpty(sClientType) && !sClientType.equalsIgnoreCase("A")) {
			whereSql.append("and ");
			whereSql.append("t1.usertype = ? ");
			oParam.add(sClientType);
		}
	}
	
	private void buildGroupBySQL(Map _map, StringBuffer groupBySql) {
		groupBySql.append("group by t3.purviewid,t1.citycode,t1.cityname,t3.purviewname");
		String sClientType = (String) _map.get("CLIENTTYPE");
		String sCityCode = (String) _map.get("CITYCODE");
		String sAreaCode = (String) _map.get("AREA");
		
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equalsIgnoreCase("0590") && !ExtString.isEmpty(sAreaCode)) {
			groupBySql.append(",t1.areaname,t1.areacode ");
		}
		
		if (!ExtString.isEmpty(sClientType) && !sClientType.equalsIgnoreCase("A")) {
			groupBySql.append(",t1.usertype ");
		}
	}
	
	private void buildOrderBySQL(Map _map, StringBuffer orderBySql) {
		orderBySql.append(" order by t1.cityname,t3.purviewname");
		String sCityCode = (String) _map.get("CITYCODE");
		String sAreaCode = (String) _map.get("AREA");

		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equalsIgnoreCase("0590") && !ExtString.isEmpty(sAreaCode)) {
			orderBySql.append(",t1.areaname,t1.areacode");
		}

	}
	
	private void buildSelectSQL(Map _map, StringBuffer selectSql, List oParam) {
		String sCityCode = (String) _map.get("CITYCODE");
		String sAreaCode = (String) _map.get("AREA");
		//String sClientType = (String) _map.get("CLIENTTYPE");