From 935b4b66b99b1f022f82cdaef8e3ef6599afbc72 Mon Sep 17 00:00:00 2001
From: dhz <duhuizhe>
Date: 星期三, 22 六月 2022 15:52:54 +0800
Subject: [PATCH] 工单统计
---
src/cn/ksource/web/facade/tj/QuestionStatisFacadeImpl.java | 32 +++++++++++++++++++++-----------
1 files changed, 21 insertions(+), 11 deletions(-)
diff --git a/src/cn/ksource/web/facade/tj/QuestionStatisFacadeImpl.java b/src/cn/ksource/web/facade/tj/QuestionStatisFacadeImpl.java
index 252c3b5..654c25b 100644
--- a/src/cn/ksource/web/facade/tj/QuestionStatisFacadeImpl.java
+++ b/src/cn/ksource/web/facade/tj/QuestionStatisFacadeImpl.java
@@ -242,9 +242,11 @@
Map paramMap = new HashMap();
StringBuilder builder = new StringBuilder("");
if(frequency.equals("2")) {
- builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m-%d') AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1");
+ builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,to_char(C.CREATE_TIME,\n" +
+ " 'yyyy-MM-dd') AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1");
} else {
- builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m') AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1 ");
+ builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,to_char(C.CREATE_TIME,\n" +
+ " 'yyyy-MM') AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1 ");
}
if(StringUtil.isBlank(queryType)){
builder.append(" AND state!=7 ");
@@ -267,7 +269,12 @@
}
}
builder.append(" and $KEY is not null ");
- builder.append(" GROUP BY $KEY,DC_TIME ");
+ if(frequency.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", ConvertUtil.obj2StrBlank(queryMap.get("col")));
return baseDao.queryForList(sql,paramMap);
}
@@ -508,8 +515,8 @@
paramMap.put("subCustomerId", subCustomerId);
}
- 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);
@@ -598,12 +605,15 @@
List<List<String>> dataList = new ArrayList<List<String>>();
//鏌ヨ鏁版嵁
StringBuilder builder = new StringBuilder();
- builder.append(" SELECT q.NAME,q.DESCRIP,q.CONTACT_NAME,q.CONTACT_PHONE,q.SOURCE_NAME,q.THIRD_CATEGORY_NAME,q.SECOND_CATEGORY_NAME,q.FIRST_CATEGORY_NAME,DATE_FORMAT(q.WANT_DEAL_TIME,'%Y-%m-%d') WANT_DEAL_TIME, ");
- builder.append(" q.PRIORITY_NAME,q.INFLUENCE_NAME,q.STATE,q.RESOLVE_TYPE_NAME,q.RESOLVE,t.CREATE_USER_NAME,DATE_FORMAT(t.CREATE_TIME,'%Y-%m-%d') CREATE_TIME,IFNULL(t.deal_time,0) deal_time ");
+ builder.append(" SELECT q.NAME,q.DESCRIP,q.CONTACT_NAME,q.CONTACT_PHONE,q.SOURCE_NAME,q.THIRD_CATEGORY_NAME,q.SECOND_CATEGORY_NAME,q.FIRST_CATEGORY_NAME,q.WANT_DEAL_TIME WANT_DEAL_TIME, ");
+ builder.append(" q.PRIORITY_NAME,q.INFLUENCE_NAME,q.STATE,q.RESOLVE_TYPE_NAME,q.RESOLVE,t.CREATE_USER_NAME,t.CREATE_TIME CREATE_TIME,IFNULL(t.deal_time,0) deal_time ");
builder.append(" from SC_WORKFLOW_QUESTION q ");
builder.append(" LEFT JOIN ( ");
- builder.append(" select z.CREATE_USER_NAME,z.CREATE_TIME,b.ID,truncate(TIMESTAMPDIFF(SECOND, b.CREATETIME, b.ENDTIME)*1000,0) deal_time from QUESTION_ZD_HISTORY z,workflow_base b where b.ID = z.FLOWID ");
- builder.append(" and z.ISTHEEND =1 group by b.ID ");
+ builder.append(" select z.CREATE_USER_NAME,z.CREATE_TIME,b.ID,round((\n" +
+ " b.ENDTIME - b.CREATETIME\n" +
+ " )*1000,\n" +
+ " 0) deal_time from QUESTION_ZD_HISTORY z,workflow_base b where b.ID = z.FLOWID ");
+ builder.append(" and z.ISTHEEND =1 group by b.ID,z.CREATE_USER_NAME,z.CREATE_TIME,round(( b.ENDTIME - b.CREATETIME )*1000,0) ");
builder.append(" ) t on q.FLOW_ID = t.ID where q.CREATE_TIME >:beginDay and q.CREATE_TIME < :endDay ");
if(StringUtil.notEmpty(cusId)){
builder.append(" and q.CUSTOMER_ID = :cusId ");
@@ -677,7 +687,7 @@
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(sleep_num,0) sleep_num, ");
builder.append(" IFNULL(avg_deal_time,0) avg_deal_time,truncate(IFNULL(major_num,0)*100/total_num,2) major_rate,truncate(IFNULL(finish_num, 0)*100/total_num,2) solve_rate,truncate(IFNULL(close_num, 0)*100/total_num,2) close_rate, ");
builder.append(" truncate(IFNULL(repeat_num, 0)*100/total_num,2) repeat_rate,truncate(IFNULL(nosolve_num, 0)*100/total_num,2) nosolve_rate from ");
- builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(i.id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mtotal ");
+ builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(i.id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID,i.THIRD_CATEGORY_NAME,i.SECOND_CATEGORY_NAME,i.FIRST_CATEGORY_NAME) mtotal ");
builder.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b,(SELECT FLOWID FROM workflow_node WHERE FLOWSTATE = 1 GROUP BY FLOWID) n where b.BUSINESS_ID = i.ID AND b.id = n.FLOWID 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_QUESTION i,workflow_base b where b.BUSINESS_ID = i.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 ");
@@ -891,7 +901,7 @@
builder.append(" IFNULL(finish_num,0) finish_num, IFNULL(close_num,0) close_num from ( ");
builder.append(" select count(b.id) total_num,i.THIRD_CATEGORY_NAME,i.FIRST_CATEGORY_NAME,i.SECOND_CATEGORY_NAME,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
builder.append(" where b.BUSINESS_ID = i.ID and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
- builder.append(" GROUP BY I.THIRD_CATEGORY_ID ");
+ builder.append(" GROUP BY I.THIRD_CATEGORY_ID ,i.THIRD_CATEGORY_NAME,i.FIRST_CATEGORY_NAME,i.SECOND_CATEGORY_NAME ");
builder.append(" ) mtotal left join ( ");
builder.append(" select count(b.id) answer_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i,( ");
builder.append(" select FLOWID from workflow_node where FLOWSTATE = 1 group by FLOWID ");
--
Gitblit v1.9.1