affairreportimpl.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.rpt;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

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

public class AffairReportImpl extends ReportImpl {

	protected DataTable getRptList(Map _map) {
		DacClient db = new DacClient();
		try {
			String sQueryType = (String) _map.get("QUERYTYPE");
			StringBuffer sql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer gSql = new StringBuffer();
			List oParam = new ArrayList();

			sql.append("select t.title, t.click_count, t.usertype, t.fullname, t.citycode");
			sql.append(" from vl_bbs_count t");

			buildWhereSQL(_map, whereSql, oParam);

			if (whereSql.length() > 0) {
				sql.append(" where ").append(whereSql);
			}

			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;
		
			if (sQueryType.equalsIgnoreCase("NN")) {

			} else if (sQueryType.equalsIgnoreCase("JJ")) {

			} else if (sQueryType.equalsIgnoreCase("YE")) {

			} else if (sQueryType.equalsIgnoreCase("XU")) {

			} else if (sQueryType.equalsIgnoreCase("ZH")) {

			} else if (sQueryType.equalsIgnoreCase("RI")) {

			} else {
				gSql.append("select b.cityname \"地区\",");
				gSql.append("a.title \"标题\", ");
				gSql.append("decode(a.usertype,'U','注册帐号','B','大客户','UP','关联的产品登录客户','E','内部员工','0','游客','P','产品登录客户','未知') \"客户类别\",");
				gSql.append("a.click_count \"总访问量\", ");
				//gSql.append("a.fullname, ");
				gSql.append("count(1) \"访问量\", round(count(1) / a.click_count * 100,2) || '%' \"占比情况\"");
				gSql.append(" from ( ");
				gSql.append(sql).append(" ) a,");
				gSql.append(" td_city b");
				gSql.append(" where a.citycode = b.citycode ");
				gSql.append(" group by a.title, a.click_count, a.usertype, a.fullname, b.cityname");
				gSql.append(" order by a.title");
			}
			
			StringBuffer _sb = new StringBuffer();
			_sb.append("SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM(");
			_sb.append(gSql);
			_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;
	}

	private void buildWhereSQL(Map _map, StringBuffer whereSql, List oParam) {
		String sClientType = (String) _map.get("CLIENTTYPE");
		String sCityCode = (String) _map.get("CITYCODE");
		String sTitle = (String) _map.get("TITLE");
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");

		if (!ExtString.isEmpty(sTitle)) {
			whereSql.append(" t.title like '%' || ? || '%'");
			whereSql.append(" and t.content like '%' || ? || '%'");

			oParam.add(sTitle);
			oParam.add(sTitle);
		}

		if (!ExtString.isEmpty(sStartTime)) {
			if (whereSql.length() > 0)
				whereSql.append(" and ");
			whereSql.append(" t.visitingdate >= ?");
			Date d = ExtString.toDate(sStartTime + " 00:00:00");
			if (d != null)
				oParam.add(d);
		}

		if (!ExtString.isEmpty(sEndTime)) {
			if (whereSql.length() > 0)
				whereSql.append(" and ");
			whereSql.append(" t.visitingdate <= ?");
			Date d = ExtString.toDate(sStartTime + " 00:00:00");
			if (d != null)
				oParam.add(d);
		}

		if (!ExtString.isEmpty(sClientType) && !sCityCode.equals("0590")) {
			if (whereSql.length() > 0)
				whereSql.append(" and ");
			whereSql.append(" t.citycode = ?");
			oParam.add(sCityCode);
		}

		if (!ExtString.isEmpty(sClientType) && !sClientType.equalsIgnoreCase("A")) {
			if (whereSql.length() > 0)
				whereSql.append(" and ");
			whereSql.append(" t.usertype = ?");
			oParam.add(sClientType);
		}
	}

	protected int getRptListCount(Map _map) {
		DacClient db = new DacClient();
		try {
			String sQueryType = (String) _map.get("QUERYTYPE");
			StringBuffer sql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer gSql = new StringBuffer();
			List oParam = new ArrayList();

			sql.append("select t.title, t.click_count, t.usertype, t.fullname, t.citycode");
			sql.append(" from vl_bbs_count t");

			buildWhereSQL(_map, whereSql, oParam);

			if (whereSql.length() > 0) {
				sql.append(" where ").append(whereSql);
			}

			if (sQueryType.equalsIgnoreCase("NN")) {

			} else if (sQueryType.equalsIgnoreCase("JJ")) {

			} else if (sQueryType.equalsIgnoreCase("YE")) {

			} else if (sQueryType.equalsIgnoreCase("XU")) {

			} else if (sQueryType.equalsIgnoreCase("ZH")) {

			} else if (sQueryType.equalsIgnoreCase("RI")) {

			} else {
				gSql.append(" select count(1) from (");
				gSql.append("select a.title, a.click_count, a.usertype, a.fullname, b.cityname");
				gSql.append(" from ( ");
				gSql.append(sql).append(" ) a,");
				gSql.append(" td_city b");
				gSql.append(" where a.citycode = b.citycode ");
				gSql.append(" group by a.title, a.click_count, a.usertype, a.fullname, b.cityname");
				gSql.append(")");
			}

			Object ap[] = new Object[oParam.size()];
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			
			String tmp = db.getStringFromSqlQuery(gSql.toString(), ap);
System.out.println("记录数：" + tmp);
			return Integer.parseInt(tmp);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}

		return -1;
	}

}