From 6e06bba1c89f8077e29d0fbf0ce12f89f027d8d2 Mon Sep 17 00:00:00 2001
From: cy <1664593601@qq.com>
Date: 星期三, 22 六月 2022 16:16:37 +0800
Subject: [PATCH] fix(预防性防护): MySQL=》DMSQL
---
src/cn/ksource/web/facade/tj/LocalStatisFacadeImpl.java | 54 ++++++++++++++++++++++++++++++------------------------
1 files changed, 30 insertions(+), 24 deletions(-)
diff --git a/src/cn/ksource/web/facade/tj/LocalStatisFacadeImpl.java b/src/cn/ksource/web/facade/tj/LocalStatisFacadeImpl.java
index 1aa9f3b..51a8e26 100644
--- a/src/cn/ksource/web/facade/tj/LocalStatisFacadeImpl.java
+++ b/src/cn/ksource/web/facade/tj/LocalStatisFacadeImpl.java
@@ -254,9 +254,9 @@
Map paramMap = new HashMap();
StringBuilder builder = new StringBuilder("");
if(type.equals("2")) {
- builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m-%d') AS DC_TIME FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE 1=1 ");
+ builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,to_char(C.CREATE_TIME,'yyyy-MM-dd') AS DC_TIME FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE 1=1 ");
} else {
- builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m') AS DC_TIME FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE 1=1 ");
+ builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,to_char(C.CREATE_TIME,'yyyy-MM') AS DC_TIME FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE 1=1 ");
}
if(!queryType.equals("3")){
builder.append(" and state!=5 ");
@@ -278,7 +278,12 @@
}
}
builder.append(" and $KEY is not null ");
- builder.append(" GROUP BY $KEY,DC_TIME ");
+ if(type.equals("2")) {
+ builder.append(" GROUP BY $KEY,to_char(C.CREATE_TIME,'yyyy-MM-dd') ");
+ }else{
+ builder.append(" GROUP BY $KEY,to_char(C.CREATE_TIME,'yyyy-MM') ");
+ }
+
String sql = builder.toString().replace("$KEY", queryMap.get("col").toString());
return baseDao.queryForList(sql,paramMap);
}
@@ -291,13 +296,14 @@
StringBuilder sql = new StringBuilder();
Map paramMap = new HashMap();
- sql.append(" SELECT HOUR(HAPPEN_TIME) MHOUR,count(ID) NUM FROM SC_WORKFLOW_INCIDENT_LOCAL C");
- sql.append(" where c.state!=5 and DATE_FORMAT(HAPPEN_TIME,'%Y%m%d')=:selDay and HOUR(HAPPEN_TIME)>=7 and HOUR(HAPPEN_TIME)<=18 ");
+
+ sql.append(" SELECT to_number(substr(HAPPEN_TIME||'',9,2)) MHOUR,count(ID) NUM FROM SC_WORKFLOW_INCIDENT_LOCAL C");
+ sql.append(" where c.state!=5 and to_number(substr(HAPPEN_TIME||'',1,8))=:selDay and to_number(substr(HAPPEN_TIME||'',9,2))>=7 and to_number(substr(HAPPEN_TIME||'',9,2))<=18 ");
if(StringUtil.notEmpty(cusId) ) {
sql.append(" AND CUSTOMER_ID = :cusId ");
paramMap.put("cusId", cusId);
}
- sql.append(" GROUP BY MHOUR ");
+ sql.append(" GROUP BY to_number(substr(HAPPEN_TIME||'',9,2)) ");
paramMap.put("selDay", selDay);
List<Map> list = baseDao.queryForList(sql.toString(),paramMap);
@@ -387,12 +393,12 @@
sql.append(" mlevel.level_num,manswer.answer_num,mdeal.deal_num,mresolve.resolve_num,mstate.state_num ");
sql.append(" from ");
sql.append(" ( ");
- sql.append(" select count(ID) total_num,DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') mdate from SC_WORKFLOW_INCIDENT_LOCAL ");
+ sql.append(" select count(ID) total_num,substr(HAPPEN_TIME||'',1,8) mdate from SC_WORKFLOW_INCIDENT_LOCAL ");
sql.append(" where HAPPEN_TIME>=:beginTime and HAPPEN_TIME<=:endTime ");
if(StringUtil.isNotBlank(cusId)){
sql.append("and CUSTOMER_ID=:cusId ");
}
- sql.append(" group by DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') ");
+ sql.append(" group by substr(HAPPEN_TIME||'',1,8) ");
sql.append(" ) mtotal ");
//浜嬩欢绫诲瀷
@@ -454,12 +460,12 @@
String column,String tempTableName,String flag,String cusId){
sql.append(" left join ");
sql.append(" ( ");
- sql.append(" select mdate,GROUP_CONCAT(num order by ordernum asc) "+numName+" from ");
+ sql.append(" select mdate,WM_CONCAT(num) "+numName+" from ");
sql.append(" ( ");
sql.append(" select count(i.ID) num,mdate,datakey,ordernum from ");
sql.append(" ( ");
sql.append(" select DATAKEY,mdate,ordernum from ");
- sql.append(" (select DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') mdate from SC_WORKFLOW_INCIDENT_LOCAL group by mdate ) d,");
+ sql.append(" (select substr(HAPPEN_TIME||'',1,8) mdate from SC_WORKFLOW_INCIDENT_LOCAL group by substr(HAPPEN_TIME||'',1,8) ) d,");
sql.append(" ( ");
if(StringUtil.isNotBlank(categoryKey)){
sql = getDicSql(sql,categoryKey);
@@ -478,8 +484,8 @@
}else{
sql = getStateSql(sql,cusId);
}
- sql.append(" i on i."+column+" = b.DATAKEY and DATE_FORMAT(i.HAPPEN_TIME,'%Y%m%d')=b.mdate ");
- sql.append(" group by b.mdate,b.datakey ");
+ sql.append(" i on i."+column+" = b.DATAKEY and substr(HAPPEN_TIME||'',1,8)=b.mdate ");
+ sql.append(" group by b.mdate,b.datakey,b.ordernum ");
sql.append(" ) t group by mdate ");
sql.append(" ) "+tempTableName);
sql.append(" on "+tempTableName+".mdate = mtotal.mdate ");
@@ -937,7 +943,7 @@
StringBuilder builder4 = new StringBuilder(sql);
- String endSql = " GROUP BY THIRD_CATEGORY_ID ";
+ String endSql = " GROUP BY THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME ";
builder1.append(endSql);
@@ -1043,7 +1049,7 @@
StringBuilder builder = new StringBuilder();
builder.append(" select mtotal.THIRD_CATEGORY_ID,mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num,IFNULL(close_num,0) close_num from ");
- builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mtotal left join ");
+ builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal left join ");
builder.append(" (select count(i.id) close_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=3 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mclose ");
builder.append(" on mtotal.THIRD_CATEGORY_ID = mclose.THIRD_CATEGORY_ID ");
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
@@ -1122,15 +1128,15 @@
StringBuilder builder = new StringBuilder();
builder.append(" select mtotal.THIRD_CATEGORY_ID,mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num from ");
- builder.append(" (select THIRD_CATEGORY_NAME,FIRST_CATEGORY_NAME,SECOND_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mtotal ");
+ builder.append(" (select THIRD_CATEGORY_NAME,FIRST_CATEGORY_NAME,SECOND_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,FIRST_CATEGORY_NAME,SECOND_CATEGORY_NAME) mtotal ");
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
//鏌ヨ鏁版嵁瀛楀吀
List<Map> typeList = dataDictionaryService.getDataDictionaryByCategoryKey("INCIDENT_TYPE");
//鏌ヨ瑙ｅ喅鏂瑰紡,鍏抽棴鍘熷洜
builder.setLength(0);
- builder.append(" select count(id) m_num,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID) m_key,THIRD_CATEGORY_NAME,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL ");
- builder.append(" where CUSTOMER_ID=:cusId and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by TYPE_ID,THIRD_CATEGORY_ID ");
+ builder.append(" select count(id) m_num,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID) m_key,THIRD_CATEGORY_NAME,THIRD_CATEGORY_ID,TYPE_ID from SC_WORKFLOW_INCIDENT_LOCAL ");
+ builder.append(" where CUSTOMER_ID=:cusId and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by TYPE_ID,THIRD_CATEGORY_ID,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID),THIRD_CATEGORY_NAME ");
List<Map> extendList = baseDao.queryForList(builder.toString(),paramMap);
if(extendList!=null&&extendList.size()>0){
for(Map extend : extendList){
@@ -1179,8 +1185,8 @@
StringBuilder builder = new StringBuilder();
builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num,IFNULL(finish_num,0) finish_num,truncate(ifnull(avg_time,0)*3600*1000,0) avg_time,truncate(ifnull(sum_time,0)*3600*1000,0) sum_time from ");
- builder.append(" (select AVG(a.DEAL_USE_TIME) avg_time,sum(a.DEAL_USE_TIME) sum_time,b.CATEGORY_NAME THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL a,SC_SERVCE_CATEGORY b where a.state!=10 and a.CUSTOMER_ID=:cusId and a.CREATE_TIME>=:beginDay and a.CREATE_TIME<=:endDay and a.THIRD_CATEGORY_ID=b.id group by a.THIRD_CATEGORY_ID) mtotal left join ");
- builder.append(" (select THIRD_CATEGORY_NAME,count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and (b.WFSTATE=2 or b.WFSTATE=4) and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mfinish ");
+ builder.append(" (select AVG(a.DEAL_USE_TIME) avg_time,sum(a.DEAL_USE_TIME) sum_time,b.CATEGORY_NAME THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL a,SC_SERVCE_CATEGORY b where a.state!=10 and a.CUSTOMER_ID=:cusId and a.CREATE_TIME>=:beginDay and a.CREATE_TIME<=:endDay and a.THIRD_CATEGORY_ID=b.id group by a.THIRD_CATEGORY_ID,b.CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal left join ");
+ builder.append(" (select THIRD_CATEGORY_NAME,count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and (b.WFSTATE=2 or b.WFSTATE=4) and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME) mfinish ");
builder.append(" on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
@@ -1224,7 +1230,7 @@
StringBuilder builder = new StringBuilder();
builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(timeout_num,0) timeout_num, ");
builder.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(first_num, 0)*100/total_num,2) first_rate,truncate((IFNULL(total_num,0)-IFNULL(timeout_num,0))*100/IFNULL(total_num,0),2) ontime_rate from ");
- builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mtotal ");
+ builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal ");
builder.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) manswer ");
builder.append(" on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
builder.append(" left join (select count(i.id) activi_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mactivi ");
@@ -1290,13 +1296,13 @@
sql+=" and a.zsxm like :userName ";
}
List<Map> userList=baseDao.queryForList(sql, param);
- sql="SELECT count(*) num,RESOLVE_USER_ID,DATE_FORMAT(RESOLVE_TIME,'%Y%m%d') day from sc_workflow_incident_local ";
-
- sql+=" where DATE_FORMAT(RESOLVE_TIME,'%Y%m%d')>=:beginDay and DATE_FORMAT(RESOLVE_TIME,'%Y%m%d')<=:endDay ";
+ sql="SELECT count(*) num,RESOLVE_USER_ID,to_number(substr(RESOLVE_TIME,1,8)) day from sc_workflow_incident_local ";
+
+ sql+=" where to_number(substr(RESOLVE_TIME,1,8))>=:beginDay and to_number(substr(RESOLVE_TIME,1,8))<=:endDay ";
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
sql+=" and CUSTOMER_ID=:cusId ";
}
- sql+=" GROUP BY RESOLVE_USER_ID,DATE_FORMAT(RESOLVE_TIME,'%Y%m%d')";
+ sql+=" GROUP BY RESOLVE_USER_ID,to_number(substr(RESOLVE_TIME,1,8))";
List<Map> numList=baseDao.queryForList(sql, param);
Long beginDay=ConvertUtil.obj2Long(param.get("beginDay"));
Long endDay=ConvertUtil.obj2Long(param.get("endDay"));
--
Gitblit v1.9.1