📄 newaccesslogimpl.java
字号:
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();
StringBuffer whereSql = 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, whereSql,oParam);
sql.append(whereSql);
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) {
try {
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 orderBySql = new StringBuffer();
StringBuffer connSql = new StringBuffer();
StringBuffer conn1Sql = 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");
List oParam = new ArrayList();
if (ExtString.isEmpty(sSearName)) {
if (!sOptype.equals("3")) {
sql.append(" select count(*) from ( ");
sql.append(" select distinct (t2.indexcode),");
buildSumSQL(_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");
}
if (sOptype.equals("3")) {
sql.append(" select count(a.访问量) 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 ");
oParam.add(sNodeId);
buildWhereSQL(_map, whereSql,oParam);
sql.append(whereSql);
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 ");
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("count^^^^^^^^^^^^^^^^"+ap[i]);
}
//System.out.println("count^^^^^^^^^^^^^^^^"+sql.toString());
DacClient db = new DacClient();
//System.out.println("StratTime:="+System.currentTimeMillis());
String count = db.getStringFromSqlQuery(sql.toString(), ap);
//System.out.println("EndTime:="+System.currentTimeMillis());
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) {
//System.out.println("EndTime:="+System.currentTimeMillis());
//e.printStackTrace();
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(? , 'yyyy-mm-dd') ");
oParam.add(sStartTime);
}
if (!ExtString.isEmpty(sEndTime)) {
whereSql.append(" and ");
whereSql.append(" CALC_TIME <= to_date(? , 'yyyy-mm-dd') ");
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"))) {
whereSql.append(" and ACTION_TYPE like ? || '%' ");
oParam.add(sOrdertype);
}
}
//统计总数量SQL语句里的条件
private void buildSumWhereSQL(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 sOptype = (String) _map.get("OPTYPE");
String sOrdertype = (String) _map.get("ORDERTYPE");//?????à±?
whereSql.append("where t3.type=? ");
oParam.add(sOptype);
if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
if(sCityCode.equals("-0590")) {
sCityCode = "0590";
}
whereSql.append("and t3.citycode = ? ");
oParam.add(sCityCode);
}
if (!ExtString.isEmpty(sAreaCode) && !sAreaCode.equals("-1")) {
whereSql.append("and t3.areacode = ? ");
oParam.add(sAreaCode);
}
if (!ExtString.isEmpty(sStartTime)) {
whereSql.append("and ");
whereSql.append("t3.CALC_TIME >= to_date(? , 'yyyy-mm-dd') ");
oParam.add(sStartTime);
}
if (!ExtString.isEmpty(sEndTime)) {
whereSql.append("and ");
whereSql.append("t3.CALC_TIME <= to_date(?, 'yyyy-mm-dd') ");
oParam.add(sEndTime);
}
if (!ExtString.isEmpty(sApplyType) && !sApplyType.equals("6")) {
whereSql.append("and t3.ACTION_DESC like ? || '%' ");
oParam.add(sApplyType);
}
if (!ExtString.isEmpty(sOrdertype) && (! sOrdertype.equals("A"))) {
whereSql.append("and t3.ACTION_TYPE like ? || '%' ");
oParam.add(sOrdertype);
}
}
//用业务名称查找时,使用的方法
private void buildSearNameSelectSQL(Map _map, StringBuffer selectSql, List oParam) {
StringBuffer sumSql = new StringBuffer();
String sCityCode = (String) _map.get("CITYCODE");
String sAreaCode = (String) _map.get("AREA");
String slevel = (String) _map.get("OPLEVEL");
String sSearName = (String) _map.get("SEARNAME");
selectSql.append(" select " );
if(!ExtString.isEmpty(sAreaCode))
{selectSql.append(" (select d.citycode from tl_reportofday d ");
selectSql.append(" where d.citycode = ? and rownum = 1) 城市编号 ,");
selectSql.append(" (select d.cityname from tl_reportofday d ");
selectSql.append(" where d.citycode = ? and rownum = 1) 地区 ,");
oParam.add(sCityCode);
oParam.add(sCityCode);
}
if (!ExtString.isEmpty(sCityCode)&&!ExtString.isEmpty(sAreaCode)&&(!sAreaCode.equals("-1"))&&(!sCityCode.equals("0590"))) {
selectSql.append(" (select d.areacode from tl_reportofday d ");
selectSql.append(" where d.citycode = ? and d.areacode=? and rownum = 1) areacode ,");
selectSql.append(" (select d.areaname from tl_reportofday d ");
selectSql.append(" where d.citycode = ? and d.areacode=? and rownum = 1) 区域 ,");
oParam.add(sCityCode);
oParam.add(sAreaCode);
oParam.add(sCityCode);
oParam.add(sAreaCode);
}
selectSql.append(" t2.nodeid, t2.nodename 名称,");
buildSumSQL(_map, sumSql,oParam);
selectSql.append(sumSql);
// int sum = getAccessLogSum(_map);
// oParam.add(new Integer(sum));
}
private void buildGroupBySQL(Map _map, StringBuffer groupBySql) {
String sOrdertype = (String) _map.get("ORDERTYPE");//业务类别
groupBySql.append(" group by ");
String sCityCode = (String) _map.get("CITYCODE");
String sAreaCode = (String) _map.get("AREA");
String sApplyType = (String) _map.get("APPLYTYPE");//申请类别
String sSearName = (String) _map.get("SEARNAME");
if(!ExtString.isEmpty(sAreaCode))
{groupBySql.append(" t2.citycode,t2.cityname ,");}
if (!ExtString.isEmpty(sCityCode)&&!ExtString.isEmpty(sAreaCode)&&(!sCityCode.equals("0590"))) {
groupBySql.append(" t2.areaname,t2.areacode ,");
}
groupBySql.append(" t2.NODEID,t2.NODENAME,t2.indexcode, t2.upnodeid ");
}
private void buildOrderBySQL(Map _map, StringBuffer orderBySql) {
String sCityCode = (String) _map.get("CITYCODE");
String sAreaCode = (String) _map.get("AREA");
if(!ExtString.isEmpty(sAreaCode)){
orderBySql.append(" order by t2.citycode ");}
if (!ExtString.isEmpty(sCityCode) && !sCityCode.equalsIgnoreCase("0590") && !ExtString.isEmpty(sAreaCode)) {
orderBySql.append(",t2.areaname,t2.areacode");
}
}
private void buildSelectSQL(Map _map, StringBuffer selectSql, List oParam) {
StringBuffer whereSql = new StringBuffer();
String sCityCode = (String) _map.get("CITYCODE");
String sAreaCode = (String) _map.get("AREA");
String sOptype = (String) _map.get("OPTYPE");
String slevel = (String) _map.get("OPLEVEL");
String sSearName = (String) _map.get("SEARNAME");
selectSql.append(" select distinct (t2.indexcode) indexcode , " );
if(!ExtString.isEmpty(sAreaCode))
{selectSql.append(" (select d.citycode from tl_reportofday d ");
selectSql.append(" where d.citycode = ? and rownum = 1) 城市编号 ,");
selectSql.append(" (select d.cityname from tl_reportofday d ");
selectSql.append(" where d.citycode = ? and rownum = 1) 地区 ,");
oParam.add(sCityCode);
oParam.add(sCityCode);
}
if (!ExtString.isEmpty(sCityCode)&&!ExtString.isEmpty(sAreaCode)&&(!sAreaCode.equals("-1"))&&(!sCityCode.equals("0590"))) {
selectSql.append(" (select d.areacode from tl_reportofday d ");
selectSql.append(" where d.citycode = ? and d.areacode=? and rownum = 1) areacode ,");
selectSql.append(" (select d.areaname from tl_reportofday d ");
selectSql.append(" where d.citycode = ? and d.areacode=? and rownum = 1) 区域 ,");
oParam.add(sCityCode);
oParam.add(sAreaCode);
oParam.add(sCityCode);
oParam.add(sAreaCode);
}
selectSql.append(" t2.NODEID, t2.upnodeid, t2.NODENAME 名称, (Length(t2.indexcode)-3) / 3 level1, ");
buildSumSQL(_map, selectSql,oParam);//加入访问量语句
selectSql.append(" , ");
selectSql.append(" (select count(nodeid) from td_report_node where upnodeid=t2.nodeid) nextlevel ");
}
private void buildSumSQL(Map _map, StringBuffer selectSql, List oParam) {
StringBuffer whereSql = new StringBuffer();
String sOptype = (String) _map.get("OPTYPE");
//selectSql.append(" (select /*+all_rows index(t3,TD_REPORTOFDAY_NODEID)*/ decode(sum(t3.AMOUNT),null,'0',sum(t3.AMOUNT)) ");
//selectSql.append(" (select decode(sum(t3.AMOUNT),null,'0',sum(t3.AMOUNT)) ");
//selectSql.append(" from TL_REPORTOFDAY t3 ");
selectSql.append(" (select/*+ index(t3 TD_REPORTOFDAY_NODEID)*/ nvl(sum(t3.AMOUNT),'0') ");
//selectSql.append(" from TL_REPORTOFDAY t3 ");
selectSql.append(" from TL_REPORTOFDAY t3, td_report_node a ");
buildSumWhereSQL(_map, whereSql, oParam);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -