newaccesslogimpl.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.rpt;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;
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 NewAccesslogImpl extends ReportImpl {
	private static int count = 0;
	private static int sum = 0; 
	private static int defaultlevel = 4; //默认可以显示几级
	AccesslogImpl method=new AccesslogImpl();
	//sOptype=="3" //业务受理量状态子级显示
	/**用于系统访问量页面获取报表
	 * 
	 */
	protected DataTable getRptList(Map _map) {
		try {
			StringBuffer _sb = new StringBuffer();
 			StringBuffer sql = new StringBuffer();
			StringBuffer selectSql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer whereSql1 = new StringBuffer();
			StringBuffer whereSql2 = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer sumBySql = new StringBuffer();
			StringBuffer orderBySql = new StringBuffer();
			StringBuffer connSql = new StringBuffer();
			StringBuffer conn1Sql = new StringBuffer();
			StringBuffer rownumHeadSql = new StringBuffer();
			StringBuffer rownumTailSql = new StringBuffer();		
			String sAreaCode = (String) _map.get("AREA");
			String sCityCode = (String) _map.get("CITYCODE");
			String slevel = (String) _map.get("OPLEVEL");
			String sOptype = (String) _map.get("OPTYPE");
			String sSearName = (String) _map.get("SEARNAME");
			String sNodeId = (String) _map.get("NODEID");
			int sum=0;
			List oParam = new ArrayList();

			if (ExtString.isEmpty(sSearName)) {//不用模块名查询时
				if (!sOptype.equals("3")) {
					sum = getAccessLogSum(_map);
					_map.put("SUM",Integer.toString(sum));
					buildRownumHeadSQL(_map, rownumHeadSql,oParam);
					sql.append(rownumHeadSql);
					sql.append(" select * from (");
					buildSelectSQL(_map, selectSql,oParam);
					sql.append(selectSql);
					sql.append(" from ");
					sql.append(" td_report_node t2 ");
					sql.append(" where ");
					buildWhere1SQL(_map, whereSql1,oParam);
					sql.append(whereSql1);
					buildConnectSQL(_map, connSql,oParam);
					sql.append(connSql);
					sql.append(" ) where  访问量!=0");
					_sb.append(sql);
					//sum = getAccessLogSum(_map);
					_map.put("SUM", Integer.toString(sum));
					buildRownumTailSQL(_map, rownumTailSql,oParam); //分页操作
					_sb.append(rownumTailSql);
		
				} 
				if (sOptype.equals("3")) {  //业务受理量状态子级显示
					sum = getFourLevelSum(_map);
					_map.put("SUM",Integer.toString(sum));
					buildRownumHeadSQL(_map, rownumHeadSql,oParam);
					_sb.append(rownumHeadSql);
					_sb.append(" select action_type 第1级,action_desc 第2级,action_state 第3级,sum(AMOUNT) 访问量 ");
					_sb.append(" from TL_REPORTOFDAY ");
					_sb.append(" where nodeid = ? and action_state is not null ");
					oParam.add(sNodeId);
					buildWhereSQL(_map, whereSql,oParam);
					_sb.append(whereSql);
					_sb.append(" group by action_type, ACTION_DESC,ACTION_STATE ");
					//sum = getFourLevelSum(_map);
					_map.put("SUM",Integer.toString(sum));
					buildRownumTailSQL(_map, rownumTailSql,oParam); //分页操作
					_sb.append(rownumTailSql);
				} 
			}else{  //用模块名查询时
				buildSearNameSelectSQL(_map, selectSql,oParam);
				sql.append(selectSql);
				sql.append(" from ");
				sql.append(" (select SYS_CONNECT_BY_PATH(b.NODENAME, '->') NODENAME,");
				sql.append(" nodeid , indexcode ");
				sql.append(" from td_report_node b ");
				sql.append(" where ");
				buildWhere2SQL(_map, whereSql2,oParam);
				sql.append(whereSql2);
				buildConnect1SQL(_map, conn1Sql,oParam);
				sql.append(conn1Sql);
				sql.append(" ) t2 "); 
				buildRownum1SQL(_map, sql,rownumHeadSql,oParam);
				_sb.append(rownumHeadSql);	
			}
				Object ap[] = new Object[oParam.size()];
				for (int i = 0; i < ap.length; i++) {
					ap[i] = oParam.get(i);
					//System.out.println("&&&&&&&&&&&&&"+ap[i]);
				}
				//System.out.println("&&&&&&&&&&&&&"+_sb.toString());
				DacClient db = new DacClient();
				return db.executeQuery(_sb.toString(), ap);
		} catch (Exception e) {
			//e.printStackTrace();
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return null;
	}
	//求显示的级数
	private int buildGetLevel(Map _map) {
		String sOpLevel = (String) _map.get("OPLEVEL");
		int iOpLevel=0;
		int len=0;
		len=this.levelNum(_map);
	
		if ((sOpLevel==null)||(sOpLevel.length()==0)||(sOpLevel.equals("0"))){//级数为全部即三级
			iOpLevel=defaultlevel+len;
		}else{
			iOpLevel=Integer.parseInt(sOpLevel)+len;
		}
		return iOpLevel;
	}
	

	 //用模块名查询时,组成SQL语句中where语句
	private void buildWhere2SQL(Map _map, StringBuffer sql, List oParam) {
		String sOptype = (String) _map.get("OPTYPE"); 		
		String sSearName = (String) _map.get("SEARNAME");
		sql.append(" b.state = 'E' and b.type = ? ");
		oParam.add(sOptype);
		sql.append(" and   b.nodename like '%'|| ? || '%' ");
		oParam.add(sSearName);
	}
	 //不用模块名查询时,组成SQL语句中where语句
	private void buildWhere1SQL(Map _map, StringBuffer where1Sql, List oParam) {
		int iOpLevel=buildGetLevel(_map);
		String sOptype = (String) _map.get("OPTYPE"); 		
		String sNodeId = (String) _map.get("NODEID");
		where1Sql.append(" t2.state='E' and t2.type=?  and (Length(t2.indexcode)-3) / 3<="+iOpLevel+" and t2.UPNODEID!=0 " );//三级
		oParam.add(sOptype);
		where1Sql.append(" and nodeid!=? ");
		oParam.add(sNodeId);
	}
	 //组成SQL语句外部分页的语句
	private void buildRownumHeadSQL(Map _map, StringBuffer rownumHeadSql,List oParam) {
		String sSum = (String) _map.get("SUM");
		rownumHeadSql.append("SELECT * FROM (" );
		rownumHeadSql.append("SELECT row_.*, decode("+sSum+",0,0,round(row_.访问量 /?  * 100, 2))  占比情况, rownum rownum_ FROM(");
		oParam.add(sSum);
	}
	 //组成SQL语句外部分页的语句
	private void buildRownumTailSQL(Map _map,StringBuffer rownumTailSql,List oParam) {
		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;
			int icount=this.get_count();
			if (endrecord>icount)
			{endrecord=icount;}		
		rownumTailSql.append(")row_ WHERE rownum <= ?");
		rownumTailSql.append(") WHERE rownum_ > ?");
		oParam.add(new Integer(endrecord));
	    oParam.add(new Integer(startrecord));
	
	}
	 //组成SQL语句外部分页的语句
	private void buildRownum1SQL(Map _map, StringBuffer sql,StringBuffer rownumHeadSql, List oParam) {
		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;
			int icount=this.get_count();
			if (endrecord>icount)
			{endrecord=icount;}		
			rownumHeadSql.append("SELECT * FROM (" );
			rownumHeadSql.append(" SELECT row_.*, rownum rownum_ FROM(");
			rownumHeadSql.append(sql);
			rownumHeadSql.append(")row_ WHERE rownum <= ?");
			rownumHeadSql.append(") WHERE rownum_ > ?");
			oParam.add(new Integer(endrecord));
			oParam.add(new Integer(startrecord));
	}
 	
	 //组成SQL语句中取模块树的语句
	private void buildConnectSQL(Map _map, StringBuffer connSql, List oParam) {
		String sOptype = (String) _map.get("OPTYPE");
		String sNodeId = (String) _map.get("NODEID");
		/*connSql.append(" CONNECT BY PRIOR t2.nodeid = t2.upnodeID");
		connSql.append(" START WITH t2.");
		connSql.append(" nodeid in (");
		connSql.append(" select t2.NODEID ");
		connSql.append(" from  td_report_node t2 ");
		connSql.append(" where t2.type = ? and t2.state = 'E'  ");
		oParam.add(sOptype);
		if (!sNodeId.equals("1")) {
			connSql.append("and t2. nodeid =? ");
			oParam.add(sNodeId);
		}
		connSql.append("  group by t2.NODEID )" );*/
		connSql.append(" CONNECT BY PRIOR t2.nodeid = t2.upnodeID");
		connSql.append(" START WITH exists(select 'x' from td_report_node t4 ");
		connSql.append("  where t4.nodeid=t2.nodeid and t4.type = ? and t4.state = 'E' ");
		oParam.add(sOptype);
		if (!sNodeId.equals("1")) {
			connSql.append("and t4. nodeid =? ");
			oParam.add(sNodeId);
		}
		connSql.append(") ");
	}
	
	 //用模块名查询,组成SQL语句中取模块树的语句
	private void buildConnect1SQL(Map _map, StringBuffer conn1Sql, List oParam) {
		String sNodeId = (String) _map.get("NODEID");
		conn1Sql.append(" CONNECT BY PRIOR b.nodeid = b.UPnodeID ");
		conn1Sql.append(" START WITH b. nodeid = ? ");
		oParam.add(sNodeId);	
	}
	/**用于系统访问量导出Excel页面获取报表
	 * 
	 */
	public DataTable getAllList(Map _map) {
		DacClient db = new DacClient();
		try {
 			StringBuffer sql = new StringBuffer();
			StringBuffer selectSql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer whereSql1 = new StringBuffer();
			StringBuffer whereSql2 = new StringBuffer();
			StringBuffer connSql = new StringBuffer();
			StringBuffer conn1Sql = new StringBuffer();
			String sOptype = (String) _map.get("OPTYPE");
			String sSearName = (String) _map.get("SEARNAME");
			String sNodeId = (String) _map.get("NODEID");
			List oParam = new ArrayList();
			int sum=0;
			sum = getAccessLogSum(_map);
			if (ExtString.isEmpty(sSearName)) {
				if (!sOptype.equals("3")) {
					sql.append(" SELECT row_.*,  decode("+sum+",0,0,round(row_.访问量 /?  * 100, 2))  占比情况 FROM ( ");
					oParam.add(new Integer(sum));
					buildSelectSQL(_map, selectSql,oParam);
					sql.append(selectSql);
					sql.append(" from  ");
					sql.append(" td_report_node t2 ");
					sql.append(" where ");
					buildWhere1SQL(_map, whereSql1,oParam);
					sql.append(whereSql1);
					buildConnectSQL(_map, connSql,oParam);
					sql.append(connSql);
 					sql.append(" ) row_");
				}
				if (sOptype.equals("3")) {//业务受理量状态子级显示
						sum = getFourLevelSum(_map);
						sql.append(" select a.*, decode("+sum+",0,0,round(a.访问量 /?  * 100, 2))  占比情况 from ");
						sql.append(" (select action_type 第1级,action_desc 第2级,action_state 第3级,sum(AMOUNT) 访问量 ");
						sql.append(" from TL_REPORTOFDAY ");
						sql.append(" where nodeid = ? and action_state is not null ");
						if (sum!=0) {
						oParam.add(new Integer(sum));}
						oParam.add(sNodeId);
						buildWhereSQL(_map, whereSql,oParam);
						sql.append(whereSql);
						sql.append(" group by action_type, ACTION_DESC,ACTION_STATE ) a");
				} 
			}else{
				buildSearNameSelectSQL(_map, selectSql,oParam);
				sql.append(selectSql);
				sql.append(" from ");
				sql.append(" (select SYS_CONNECT_BY_PATH(b.NODENAME, '->') NODENAME,");
				sql.append(" nodeid , indexcode ");
				sql.append(" from td_report_node b ");
				sql.append(" where ");
				buildWhere2SQL(_map, whereSql2,oParam);
				sql.append(whereSql2); 
				buildConnect1SQL(_map, conn1Sql,oParam);
				sql.append(conn1Sql);
				sql.append(" ) t2 ");
				}
				Object ap[] = new Object[oParam.size()];
				for (int i = 0; i < ap.length; i++) {
					ap[i] = oParam.get(i);
					//System.out.println("&&&&&&&&&&&&&"+ap[i]);
				}
					//System.out.println("&&&&&&&&&&&&&"+sql.toString());
				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 selectSql = new StringBuffer();
				String sOptype = (String) _map.get("OPTYPE");
				String sNodeId = (String) _map.get("NODEID");
				List oParam = new ArrayList(); 
				sql.append("select  访问量  from (");
				buildSelectSQL(_map, selectSql,oParam);
				sql.append(selectSql);
				sql.append(" from  td_report_node t2 ");
				sql.append(" where t2.state='E' and t2.type=? ");  
				oParam.add(sOptype);
				sql.append("  and t2.nodeid=?)");
				oParam.add(sNodeId);
				Object ap[] = new Object[oParam.size()];
				for (int i = 0; i < ap.length; i++) {
					ap[i] = oParam.get(i);
				 	//System.out.println("=================="+ap[i]);
				}
				//System.out.println("================="+sql.toString());		 
				String sum = db.getStringFromSqlQuery(sql.toString(), ap);