dskaccesslogimpl.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.rpt.dsk;

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;
import com.doone.fj1w.fjmgr.rpt.*;

public class DskAccesslogImpl extends ReportImpl {
	private static int count = 0;
	private static int sum = 0; 
	AccesslogImpl method=new AccesslogImpl();
	/**用于系统访问量页面获取报表
	 * 
	 */
	protected DataTable getRptList(Map _map) {
		DacClient db = new DacClient();
		try {
			StringBuffer _sb = new StringBuffer();
			StringBuffer sql = new StringBuffer();
			StringBuffer selectSql = new StringBuffer();
			String sOptype = (String) _map.get("OPTYPE");
			String sSearName = (String) _map.get("SEARNAME");
			String sNodeId = (String) _map.get("NODEID");
			int len=0;
			len=this.levelNum(_map);
			List oParam = new ArrayList();
			int pageSize = Integer.parseInt((String) _map.get("pageSize"));
			int currentPage = Integer.parseInt((String) _map.get("currentPage"));
			int sum=0;
			sum = getAccessLogSum(_map);
			//String sNodeStr=this.isNodeLevel(_map);
			String sOpLevel = (String) _map.get("OPLEVEL");
			int iOpLevel=0;
			if ((sOpLevel==null)||(sOpLevel.equals("A"))){
				iOpLevel=3+len;
			}else{
				iOpLevel=Integer.parseInt(sOpLevel)+len;
			}
			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;}		
			if (ExtString.isEmpty(sSearName)) {
				if (!sOptype.equals("3")) {
					_sb.append("SELECT * FROM (" );
					_sb.append("SELECT row_.*, decode("+sum+",0,0,round(row_.访问量 /?  * 100, 2))  占比情况, rownum rownum_ FROM(");
					oParam.add(new Integer(sum));
					sql.append(" select * from (");
					buildSelectSQL(_map, selectSql,oParam);
					sql.append(selectSql);
					sql.append("from td_report_node t1 ");
					sql.append("where t1.state='E' and t1.type='4' and (Length(t1.indexcode)-3) / 3<="+iOpLevel+" and t1.UPNODEID!=0 ");
					sql.append("CONNECT BY PRIOR t1.nodeid = t1.upnodeid ");
					sql.append("START WITH t1. nodeid = 551) ");
					_sb.append(sql);
					_sb.append(")row_ WHERE rownum <= ?");
					_sb.append(") WHERE rownum_ > ?");
					oParam.add(new Integer(endrecord));
					oParam.add(new Integer(startrecord)); 
				} 
				if (sOptype.equals("3")) {
					sum = getFourLevelSum(_map);
					_sb.append("SELECT * FROM (" );
					_sb.append("SELECT row_.*, decode("+sum+",0,0,round(row_.访问量 /?  * 100, 2))  占比情况 ,rownum rownum_ from ");
					_sb.append("(select action_type 第1级,action_desc 第2级,action_state 第3级,sum(AMOUNT) 访问量  from TL_REPORTOFDAY ");
					_sb.append("where nodeid = ? and action_state is not null ");
					if (sum!=0) {				
						oParam.add(new Integer(sum));}
					oParam.add(sNodeId);
					buildWhereSQL(_map, _sb,oParam);
					
					_sb.append("group by action_type, ACTION_DESC,ACTION_STATE ");
					_sb.append(")row_ WHERE rownum <= ?");
					_sb.append(") WHERE rownum_ > ?");
					oParam.add(new Integer(endrecord));
					oParam.add(new Integer(startrecord)); 
					
				} 
			}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 b.state = 'E' and b.type = ? ");
				oParam.add(sOptype);
				sql.append(" and b.nodename like ? || '%' ");
				oParam.add(sSearName);
				sql.append(" CONNECT BY PRIOR b.nodeid = b.UPnodeID ");
				sql.append(" START WITH b. nodeid = ?) t2 ");
				oParam.add(sNodeId);
				_sb.append("SELECT * FROM (" );
				_sb.append(" 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("ap[i]===>"+ap[i]);
			}
//			System.out.println("ap[i]===>"+_sb.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();
			StringBuffer selectSql = new StringBuffer();
			String sOptype = (String) _map.get("OPTYPE");
			String sSearName = (String) _map.get("SEARNAME");
			String sNodeId = (String) _map.get("NODEID");
			int len=0;
			len=this.levelNum(_map);
			List oParam = new ArrayList();
			int sum=0;
			sum = getAccessLogSum(_map);
			
			String sOpLevel = (String) _map.get("OPLEVEL");
			int iOpLevel=0;
			if ((sOpLevel==null)||(sOpLevel.equals("A")))
			{iOpLevel=3+len;}
			else
			{iOpLevel=Integer.parseInt(sOpLevel)+len;}
			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 td_report_node t1 ");
					sql.append("where t1.state='E' and t1.type='4' and (Length(t1.indexcode)-3) / 3<="+iOpLevel+" and t1.UPNODEID!=0 ");
					sql.append("CONNECT BY PRIOR t1.nodeid = t1.upnodeid ");
					sql.append("START WITH t1. nodeid = 551)) row_ ");
				}
				if (sOptype.equals("3")) {//业务受理量状态子级显示
					sum = getFourLevelSum(_map);
					sql.append("select a.*, decode("+sum+",0,0,round(row_.访问量 /?  * 100, 2))  占比情况 from ");
					sql.append("(select action_type 第1级,action_desc 第2级,action_state 第3级,sum(AMOUNT) 访问量  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, sql,oParam);
					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 b.state = 'E' and b.type = ? ");
				oParam.add(sOptype);
				sql.append(" and b.nodename like ? || '%' ");
				oParam.add(sSearName);
				sql.append(" CONNECT BY PRIOR b.nodeid = b.UPnodeID ");
				sql.append(" START WITH b. nodeid = ?) t2 ");
				oParam.add(sNodeId);
			}
			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 selectSql = new StringBuffer();
			List oParam = new ArrayList(); 
			String sNodeId="";
			sNodeId = (String) _map.get("NODEID");
			sql.append("select  访问量  from (");
			buildSelectSQL(_map, selectSql,oParam);
			sql.append(selectSql);
			sql.append(" from  td_report_node t1 ");
			sql.append(" where t1.state='E' and t1.type='4' ");  
			sql.append("  and t1.nodeid=?)");
			oParam.add(sNodeId);
			Object ap[] = new Object[oParam.size()];
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			
			String sum = db.getStringFromSqlQuery(sql.toString(), ap);
			if(sum == null || sum.equals("") || sum.equals("")) sum = "0";
			this.set_sum(Integer.parseInt(sum));
			return Integer.parseInt(sum);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		return 0;
	}
	/**获取操作状态按条件总计的条数
	 * 
	 * @param _map
	 * @return
	 */
	private int getFourLevelSum(Map _map) {
		DacClient db = new DacClient();
		try {
			StringBuffer sql = new StringBuffer();
			
			String sNodeId="";
			List oParam = new ArrayList();
			sNodeId = (String) _map.get("NODEID");
			sql.append("select sum(a.访问量) from ");
			sql.append("(select sum(AMOUNT) 访问量  from TL_REPORTOFDAY ");
			sql.append("where nodeid = ? and action_state is not null ");
			oParam.add(sNodeId);
			buildWhereSQL(_map, sql,oParam);
			sql.append("group by action_type, ACTION_DESC,ACTION_STATE ) a");
			
			Object ap[] = new Object[oParam.size()];
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			String sum = db.getStringFromSqlQuery(sql.toString(), ap);
			if(sum == null || sum.equals("") || sum.equals("")) sum = "0";
			this.set_sum(Integer.parseInt(sum));
			return Integer.parseInt(sum);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		return 0;
	}
	
	protected int get_count() {
		return count;
	}
	
	protected void set_count(int icount) {
		count = icount;
	}
	protected int get_sum() {
		return sum;
	}
	
	protected void set_sum(int isum) {
		sum = isum;
	}
	/**用于分页
	 * 
	 */
	protected int getRptListCount(Map _map) {
		DacClient db = new DacClient();
		try {
			StringBuffer sql = new StringBuffer();
			StringBuffer selectSql = new StringBuffer();
			String sOptype = (String) _map.get("OPTYPE");
			String sSearName = (String) _map.get("SEARNAME");
			String sNodeId = (String) _map.get("NODEID");
			int len=0;
			len=this.levelNum(_map);
			String sOpLevel = (String) _map.get("OPLEVEL");
			int iOpLevel=0;
			if ((sOpLevel==null)||(sOpLevel.equals("A")))
			{iOpLevel=3+len;}
			else
			{iOpLevel=Integer.parseInt(sOpLevel)+len;}
			List oParam = new ArrayList();
			if (ExtString.isEmpty(sSearName)) {
				if (!sOptype.equals("3")) {
					sql.append("select count(*) from( select distinct (t1.indexcode),");
					buildSumSQL(_map, selectSql,oParam);//加入求访问量语句
					sql.append(selectSql);
					sql.append("from td_report_node t1 ");
					sql.append("where t1.state='E' and t1.type='4' and (Length(t1.indexcode)-3) / 3<="+iOpLevel+" and t1.UPNODEID!=0 ");
					sql.append("CONNECT BY PRIOR t1.nodeid = t1.upnodeid ");
					sql.append("START WITH t1. nodeid = 551) ");
				}
				if (sOptype.equals("3")) {
					sql.append("select count(a.访问量) from ");
					sql.append("(select action_type 第1级,action_desc 第2级,action_state 第3级,sum(AMOUNT) 访问量  from TL_REPORTOFDAY ");
					sql.append("where nodeid = ? and action_state is not null ");
					oParam.add(sNodeId);
					buildWhereSQL(_map, sql,oParam);
					sql.append("group by action_type, ACTION_DESC,ACTION_STATE ) a");
				}  
			}else{
				sql.append("select count(nodeid)  from ("); 
				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 b.state = 'E' and b.type = ? ");
				oParam.add(sOptype);
				sql.append(" and b.nodename like ? || '%' ");
				oParam.add(sSearName);
				sql.append(" CONNECT BY PRIOR b.nodeid = b.UPnodeID ");
				sql.append(" START WITH b. nodeid = ?) t2) ");
				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]==>"+ap[i]);
			}
//			System.out.println("sql.toString()==>"+sql.toString());
			String count = db.getStringFromSqlQuery(sql.toString(), ap);
			
			if (count==null||count.trim().length()==0) {
				this.set_count(0);
				return 0;
			}
			else {
				this.set_count(Integer.parseInt(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 sAreaCode = (String) _map.get("AREA");
		String sCityCode = (String) _map.get("CITYCODE");
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sApplyType = (String) _map.get("APPLYTYPE");//申请类别
		String sOrdertype = (String) _map.get("ORDERTYPE");//业务类别
		
		
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			if(sCityCode.equals("-0590")) {
				sCityCode = "0590";
			}
			whereSql.append(" and  citycode = ? ");
			oParam.add(sCityCode);
		}
		
		if (!ExtString.isEmpty(sAreaCode) && !sAreaCode.equals("-1")) {
			whereSql.append(" and  areacode = ? ");
			oParam.add(sAreaCode);
		}
		
		if (!ExtString.isEmpty(sStartTime)) {
			whereSql.append(" and ");
			whereSql.append("  CALC_TIME >= to_date(? || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sStartTime);
		}
		
		if (!ExtString.isEmpty(sEndTime)) {
			whereSql.append(" and ");
			whereSql.append("  CALC_TIME <= to_date(? || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sEndTime);
		}
		
		if (!ExtString.isEmpty(sApplyType) && !sApplyType.equals("6")) {
			whereSql.append(" and  ACTION_DESC like ? || '%' ");
			oParam.add(sApplyType);
		}
		if (!ExtString.isEmpty(sOrdertype) && (! sOrdertype.equals("A"))) {