loginacclogimpl.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.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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

public class LoginAcclogImpl 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()];
			
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
				 //System.out.println("%%%%%%%%%%%%%%%%%%%"+ap[i]);
			}
			 //System.out.println("%%%%%%%%%%%%%%%%%%%"+_sb.toString());
			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);
				//System.out.println("ap[i]==>"+ap[i]);
			}
			
			//System.out.println("sql1.toString()==>"+sql1.toString());
			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);
				 //System.out.println("%%%%%%%%%%%%%%%%%%%"+ap[i]);
			}
			 //System.out.println("%%%%%%%%%%%%%%%%%%%"+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 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];
		
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			whereSql.append("where 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.createtime >= to_date(?, 'yyyy-mm-dd') ");
				}
				else{
					whereSql.append("t.createtime >= to_date(?, '"+timeFormat+"') ");
				}
			}
			else {
				whereSql.append("t.createtime >= 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.createtime <= to_date(?, 'yyyy-mm-dd hh24:mi:ss') ");
				}
				else{
					whereSql.append("t.createtime <= to_date(?, '"+timeFormat+"') ");
				}
			}
			else {
				whereSql.append("t.createtime <= to_date(?, '"+timeFormat+"') ");
			}
			
			oParam.add(sEndTime);
		}

	}
	private void buildCommandSQL(Map _map, StringBuffer passedSql, List oParam) {
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String str = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, sStartTime, sEndTime);
		str = result[0];
		
		passedSql.append("select to_char(t.createtime, '"+str+"') time1, count(1) cnt from tf_custinfoweb t ");
		
		StringBuffer whereSql = new StringBuffer();
		buildWhereSQL(_map, whereSql, oParam);
		if(whereSql.length() > 0) {
			passedSql.append(whereSql);
		}
		 
		
		
		passedSql.append("group by to_char(t.createtime, '"+str+"')");
	}
	//--过期数量2->注册,L自动锁定
	private void buildPassedSQL(Map _map, StringBuffer passedSql, List oParam) {
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String str = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, sStartTime, sEndTime);
		str = result[0];
		
		passedSql.append("select to_char(t.createtime, '"+str+"') time1, count(1) cnt from tf_custinfoweb t ");
		
		StringBuffer whereSql = new StringBuffer();
		buildWhereSQL(_map, whereSql, oParam);
		if(whereSql.length() > 0) {
			passedSql.append(whereSql);
			passedSql.append("and t.CUSTTYPEID = '2' ");
		}
		else
			passedSql.append("where t.custtypeid = '2' ");
		
		passedSql.append("and t.userstate = 'L' ");
		passedSql.append("and t.state = 'D' ");
		
		passedSql.append("group by to_char(t.createtime, '"+str+"')");
	}
	//--激活数量:2->注册A-激活
	private void buildActivationSQL(Map _map, StringBuffer activationSql, List oParam) {
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String str = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, sStartTime, sEndTime);
		str = result[0];
		
		activationSql.append("select to_char(t.createtime, '"+str+"') time1, count(1) cnt from tf_custinfoweb t ");
		
		StringBuffer whereSql = new StringBuffer();
		buildWhereSQL(_map, whereSql, oParam);
		if(whereSql.length() > 0) {
			activationSql.append(whereSql);
			activationSql.append("and t.custtypeid = '2' ");
		}
		else
			activationSql.append("where t.custtypeid = '2' ");
		
		activationSql.append("and t.userstate = 'A' ");
		activationSql.append("and t.state = 'E' ");
		
		activationSql.append("group by to_char(t.createtime, '"+str+"')");
	}
	//未激活数量:2->注册N-未激活
	private void buildUnActivationSQL(Map _map, StringBuffer unActivationSql, List oParam) {
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String str = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, sStartTime, sEndTime);
		str = result[0];
		
		unActivationSql.append("select to_char(t.createtime, '"+str+"') time1, count(1) cnt from tf_custinfoweb t ");
		
		StringBuffer whereSql = new StringBuffer();
		buildWhereSQL(_map, whereSql, oParam);
		if(whereSql.length() > 0) {
			unActivationSql.append(whereSql);
			unActivationSql.append("and t.custtypeid = '2' ");
		}
		else
			unActivationSql.append("where t.custtypeid = '2' ");
		
		unActivationSql.append("and t.userstate = 'N' ");
		unActivationSql.append("and t.state = 'E' ");
		
		unActivationSql.append("group by to_char(t.createtime, '"+str+"')");
	}
	//--注销数量,D->不可用
	private void buildLogoutSQL(Map _map, StringBuffer logoutSql, List oParam) {
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String str = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, sStartTime, sEndTime);
		str = result[0];
		
		logoutSql.append("select to_char(t.createtime, '"+str+"') time1, count(1) cnt from tf_custinfoweb t ");
		
		StringBuffer whereSql = new StringBuffer();
		buildWhereSQL(_map, whereSql, oParam);
		if(whereSql.length() > 0) {
			logoutSql.append(whereSql);
			logoutSql.append("and t.custtypeid = '2' ");
		}
		else
			logoutSql.append("where t.custtypeid = '2' ");
		
		logoutSql.append("and t.state = 'D' and t.Userstate <> 'L'");
		
		logoutSql.append("group by to_char(t.createtime, '"+str+"')");
	}
	//产品注册数量,!1,2,7,11产品
	private void buildLoginByproductSQL(Map _map, StringBuffer loginByproduct, List oParam) {
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sStatisticType = (String) _map.get("STATISTICTYPE");
		String str = "";
		String result[] = AccessanalyselogImpl.getStatisticType(sStatisticType, sStartTime, sEndTime);
		str = result[0];
		
		loginByproduct.append("select to_char(t.createtime, '"+str+"') time1, count(1) cnt from tf_custinfoweb t ");
		
		StringBuffer whereSql = new StringBuffer();
		buildWhereSQL(_map, whereSql, oParam);
		if(whereSql.length() > 0) {
			loginByproduct.append(whereSql);
			loginByproduct.append("and  t.custtypeid != 1 and t.custtypeid != 2  and t.custtypeid != 7  and t.custtypeid != 11 ");
		}
		else
			loginByproduct.append("where  t.custtypeid != 1 and t.custtypeid != 2  and t.custtypeid != 7  and t.custtypeid != 11 ");
		
		loginByproduct.append("and t.state = 'E' ");
		
		loginByproduct.append("group by to_char(t.createtime, '"+str+"')");
	}
	
	private void buildSQL(Map _map, StringBuffer sql, List oParam) {
		StringBuffer passedSql = new StringBuffer();
		StringBuffer activationSql = new StringBuffer();
		StringBuffer unActivationSql = new StringBuffer();
		StringBuffer logoutSql = new StringBuffer();
		StringBuffer loginByproductSql = new StringBuffer();
		StringBuffer commandSql = new StringBuffer();
		
		sql.append("select g.time1 \"时间\",nvl(a.cnt,0) \"过期数量\",nvl(b.cnt,0) \"激活数量\",");
		sql.append("nvl(c.cnt,0) \"未激活量\",nvl(d.cnt,0) \"注销数量\",nvl(e.cnt,0) \"产品注册量\" from (");
		buildCommandSQL(_map,commandSql,oParam);
		sql.append(commandSql);
		sql.append(") g,(");
		buildPassedSQL(_map,passedSql,oParam);
		sql.append(passedSql);
		sql.append(") a,(");
		buildActivationSQL(_map,activationSql,oParam);
		sql.append(activationSql);
		sql.append(") b,(");
		buildUnActivationSQL(_map,unActivationSql,oParam);
		sql.append(unActivationSql);
		sql.append(") c,(");
		buildLogoutSQL(_map,logoutSql,oParam);
		sql.append(logoutSql);
		sql.append(") d,(");
		buildLoginByproductSQL(_map,loginByproductSql,oParam);
		sql.append(loginByproductSql);
		sql.append(") e where  g.time1 = a.time1(+) and g.time1 = b.time1(+) and ");
		sql.append(" g.time1 = c.time1(+) and g.time1 = d.time1(+) and ");
		sql.append(" g.time1 = e.time1(+) ");
		sql.append(" order by g.time1 desc ");
	}
	
	public static String getHistoryStatisticType(HttpServletRequest request,HttpServletResponse response, Map _map) {
		String statisticType = "";
		try {
			statisticType = AccesslogImpl.getHistoryByName(request,response,_map,"statisticType");
			if (statisticType == null) statisticType = "";
		}catch(Exception e) {
			FileLogger.getLogger().info(e.getMessage(),e);
		}
		return statisticType;
	}
	
	
}