From 425675051e544cf29b2132615cfbf7a93dc5e51f Mon Sep 17 00:00:00 2001
From: cy <1664593601@qq.com>
Date: 星期三, 22 六月 2022 15:50:59 +0800
Subject: [PATCH] fix(dm): MySQL=》DMSQL
---
src/cn/ksource/web/facade/workReport/WorkReportFacadeImpl.java | 587 +++++++++++++++++++++++++++++++---------------------------
1 files changed, 312 insertions(+), 275 deletions(-)
diff --git a/src/cn/ksource/web/facade/workReport/WorkReportFacadeImpl.java b/src/cn/ksource/web/facade/workReport/WorkReportFacadeImpl.java
index be79802..dac3140 100644
--- a/src/cn/ksource/web/facade/workReport/WorkReportFacadeImpl.java
+++ b/src/cn/ksource/web/facade/workReport/WorkReportFacadeImpl.java
@@ -26,12 +26,12 @@
@Autowired
private DataDictionaryService dicService;
-
+
@Override
public List<Map> getWeekByYear(String year) {
Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.YEAR, Integer.valueOf(year));
- List<Map> list = new ArrayList<Map>();
+ List<Map> list = new ArrayList<Map>();
int count = DateUtil.getWeekNumByYear(Integer.valueOf(year));
SimpleDateFormat sf=new SimpleDateFormat("yyyy/MM/dd");
if(count>0){
@@ -51,7 +51,7 @@
@Override
public List<Map> getWeekByMonth(String date){
- List<Map> list = new ArrayList<Map>();
+ List<Map> list = new ArrayList<Map>();
int count = DateUtil.getWeekNumByMonth(date);
SimpleDateFormat sf=new SimpleDateFormat("yyyy/MM/dd");
if(count>0){
@@ -65,7 +65,7 @@
String beginDay = DateUtil.format("yyyy/MM/dd", DateUtil.getMonthWeekFirstDay(date,i));
String endDay = DateUtil.format("yyyy/MM/dd", DateUtil.getMonthWeekEndDay(date,i));
int weekNum = DateUtil.getYearWeekNum(date,i);
-
+
map.put("val", date.substring(0,4)+weekNum);
map.put("beginDay", beginDay);
map.put("endDay", endDay);
@@ -74,10 +74,10 @@
}
return list;
}
-
+
@Override
public List<Map> getMonthByYear(String year) {
- List<Map> list = new ArrayList<Map>();
+ List<Map> list = new ArrayList<Map>();
for(int i=1;i<=12;i++){
Map map = new HashMap();
map.put("name", year+"骞�"+i+"鏈�");
@@ -96,9 +96,9 @@
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
param.put("cusId",cusId);
-
+
sql.append(" select wfstate data_name,businesstype cate_id,count(id) num from workflow_base where wfstate in (1,2,3,4) ");
- setWhereSql(sql,"createtime",selDate,statisType,param);
+ setWhereSql(sql,"to_date(createtime,'YYYYMMDDHH24MISS')",selDate,statisType,param);
sql.append(" and customer_id=:cusId group by wfstate,businesstype order by businesstype,wfstate");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
Map<String,Map> dataSetMap = new HashMap();
@@ -130,23 +130,23 @@
System.out.println("chartMap"+JsonUtil.map2Json(chartMap));
return chartMap;
}
-
+
@Override
public Map getOrderSurveyTable(String selDate,String statisType, String cusId) {
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
param.put("cusId",cusId);
-
+
sql.append(" select wfstate data_name,businesstype cate_id,count(id) num from workflow_base where wfstate in (1,2,3,4) ");
- setWhereSql(sql,"createtime",selDate,statisType,param);
+ setWhereSql(sql,"to_date(createtime,'YYYYMMDDHH24MISS')",selDate,statisType,param);
sql.append(" and customer_id=:cusId group by businesstype,wfstate ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
sql.setLength(0);
sql.append(" SELECT businesstype cate_id,count(id) num FROM workflow_base WHERE customer_id = :cusId ");
- setWhereSql(sql,"createtime",selDate,statisType,param);
+ setWhereSql(sql,"to_date(createtime,'YYYYMMDDHH24MISS')",selDate,statisType,param);
sql.append(" group by businesstype ");
List<Map> extendList = baseDao.queryForList(sql.toString(),param);
-
+
Map dataSetMap = new HashMap();
Map extendSetMap = new HashMap();
for(Map map:dataList){
@@ -157,7 +157,7 @@
}
//鑾峰彇宸ュ崟绫诲瀷List
List<Map> cateList = getOrderCateList();
-
+
List<String> labelList = new ArrayList<String>();
List<List<String>> tableDataList = new ArrayList<List<String>>();
//缁勭粐琛ㄥご
@@ -176,14 +176,14 @@
String key = cate.get("key").toString();
List<String> tempList = new ArrayList<String>();
tempList.add(cate.get("val").toString());
-
+
if(extendSetMap.get(key)!=null){
tempList.add(((Map)extendSetMap.get(key)).get("num").toString());
totalNum = totalNum + Integer.valueOf(((Map)extendSetMap.get(key)).get("num").toString());
}else{
tempList.add("0");
}
-
+
key = "1" + "_" + cate.get("key").toString() ;
if(dataSetMap.get(key)!=null){
tempList.add(((Map)dataSetMap.get(key)).get("num").toString());
@@ -234,9 +234,9 @@
tableMap.put("dataList", tableDataList);
return tableMap;
}
-
-
-
+
+
+
/**
* 鑾峰彇宸ュ崟绫诲瀷鍒楄〃
* @author chenlong
@@ -268,7 +268,7 @@
}
return cateList;
}
-
+
/**
* 缁勮绫诲瀷鏌ヨ鏉′欢
* @author chenlong
@@ -311,9 +311,9 @@
if(statisType.equals("month")){
sql.append(" and month =:selDate ");
}else if(statisType.equals("year")){
- sql.append(" and left(month,4) =:selDate ");
+ sql.append(" and left(month,4) =:selDate ");
}else if(statisType.equals("quarter")){
- sql.append(" and concat(left(month,4),quarter(concat(month,'01'))) =:selDate ");
+ sql.append(" and concat(left(month,4),quarter(concat(month,'01'))) =:selDate ");
}
sql.append(" order by update_time desc ");
Double y = baseDao.queryForDouble(sql.toString(), param);
@@ -330,7 +330,7 @@
SqlParameter param = new SqlParameter();
param.put("cus_id", cusId);
param.put("selDate", selDate);
-
+
sql.append("SELECT AVG(A.ALL_SCORE) AS ALL_SCORE ,B.CUSTOMER_NAME,B.YWJL_NAME,B.ID FROM TOTLE_SATIS_INFO A,SC_PARTNER_CUSTOMER_INFO B WHERE A.CUSTOMER_ID = B.ID AND A.CUSTOMER_ID = :cus_id ");
if(statisType.equals("month")){
sql.append(" AND A.MONTH =:selDate ");
@@ -339,9 +339,9 @@
}else if(statisType.equals("quarter")){
sql.append(" AND CONCAT(left(month,4),quarter(CONCAT(month,'01'))) =:selDate ");
}
- sql.append(" GROUP BY A.CUSTOMER_ID");
+ sql.append(" GROUP BY A.CUSTOMER_ID,B.CUSTOMER_NAME, B.YWJL_NAME, B.ID");
Map map = baseDao.queryForMap(sql.toString(),param);
-
+
StringBuffer detailsql = new StringBuffer("SELECT A.CUSTOMER_ID,B.SATIS_TYPE,SUM(B.ALL_COUNT) AS ALL_COUNT,SUM(SATIS_COUNT) AS SATIS_COUNT,AVG(FG_RATE) AS FG_RATE,AVG(REQUEST_FG_RATE) AS REQUEST_FG_RATE,AVG(SATIS_SCORE) AS SATIS_SCORE,AVG(REQUEST_SATIS_SCORE) AS REQUEST_SATIS_SCORE,AVG(SATIS_QZ) AS SATIS_QZ FROM TOTLE_SATIS_INFO A,TOTLE_SATIS_DETAIL B WHERE A.ID = B.BUS_ID AND A.CUSTOMER_ID = :cus_id ");
if(statisType.equals("month")){
detailsql.append(" AND A.MONTH =:selDate ");
@@ -369,15 +369,15 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,type_id,type_name from sc_workflow_incident where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
- sql.append(" group by type_id ");
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
+ sql.append(" group by type_id,type_name ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
for(Map data:dataList){
dataSetMap.put(data.get("type_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.INCIDENT_TYPE);
List seriesData = new ArrayList();
for(Map dic:dicList){
@@ -428,7 +428,7 @@
for(Map data:dataList){
dataSetMap.put(data.get("satis_type").toString(), data);
}
-
+
List<Map> series = new ArrayList<Map>();
List<String> categories = new ArrayList<String>();
categories.add("鍒嗘暟");
@@ -461,10 +461,10 @@
tempMap.put("data", tempList);
series.add(tempMap);
}
-
+
}
-
-
+
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
@@ -481,15 +481,15 @@
df = new SimpleDateFormat("yyyyw");
}
Calendar cal = Calendar.getInstance();
- cal.setMinimalDaysInFirstWeek(7);
- cal.setFirstDayOfWeek(Calendar.MONDAY); //璁剧疆姣忓懆鐨勭涓�澶╀负鏄熸湡涓�
- cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//姣忓懆浠庡懆涓�寮�濮�
+ cal.setMinimalDaysInFirstWeek(7);
+ cal.setFirstDayOfWeek(Calendar.MONDAY); //璁剧疆姣忓懆鐨勭涓�澶╀负鏄熸湡涓�
+ cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//姣忓懆浠庡懆涓�寮�濮�
try {
cal.setTime(df.parse(selDate));
} catch (ParseException e) {
e.printStackTrace();
}
-
+
if(statisType.equals("month")){
cal.add(Calendar.MONTH, -1);
}else if(statisType.equals("week")){
@@ -499,32 +499,36 @@
if(statisType.equals("quarter")){
lastDate = getLastQuarter(selDate);
}
-
+
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
param.put("cusId",cusId);
param.put("selDate",selDate);
param.put("lastDate",lastDate);
param.put("busType",Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT);
-
-
+
+
+ String groupByStr = "";
if(statisType.equals("month")){
- sql.append(" select date_format(createtime,'%Y%m') cate_id,");
+ sql.append(" select date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%m') cate_id,");
+ groupByStr="date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%m')";
}else if(statisType.equals("week")){
- sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
+ sql.append(" select concat(year(to_date(createtime,'YYYYMMDDHH24MISS')),week(to_date(createtime,'YYYYMMDDHH24MISS'),5)) cate_id,");
+ groupByStr = "concat(year(to_date(createtime,'YYYYMMDDHH24MISS')),week(to_date(createtime,'YYYYMMDDHH24MISS'),5))";
}else if(statisType.equals("quarter")){
- sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
+ sql.append(" select concat(year(to_date(createtime,'YYYYMMDDHH24MISS')),quarter(to_date(createtime,'YYYYMMDDHH24MISS'))) cate_id,");
+ groupByStr = "concat(year(to_date(createtime,'YYYYMMDDHH24MISS')),quarter(to_date(createtime,'YYYYMMDDHH24MISS')))";
}
sql.append(" wfstate data_name,count(id) num from workflow_base where wfstate in (1,2,3,4) and businesstype=:busType ");
if(statisType.equals("month")){
- sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
+ sql.append(" and (date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%m') = :selDate or date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%m')=:lastDate) ");
}else if(statisType.equals("week")){
- sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5) ) ");
+ sql.append(" and year(to_date(createtime,'YYYYMMDDHH24MISS'))=left(:selDate,4) and (week(to_date(createtime,'YYYYMMDDHH24MISS'),5) = substring(:selDate,5) or week(to_date(createtime,'YYYYMMDDHH24MISS'),5)=substring(:lastDate,5) ) ");
}else if(statisType.equals("quarter")){
- sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5) ) ");
+ sql.append(" and year(to_date(createtime,'YYYYMMDDHH24MISS'))=left(:selDate,4) and (quarter(to_date(createtime,'YYYYMMDDHH24MISS')) = substring(:selDate,5) or quarter(to_date(createtime,'YYYYMMDDHH24MISS'))=substring(:lastDate,5) ) ");
}
-
- sql.append(" and customer_id=:cusId group by cate_id,data_name ");
+
+ sql.append(" and customer_id=:cusId group by " + groupByStr +",wfstate ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
Map<String,Map> dataSet = new HashMap();
@@ -567,7 +571,7 @@
}
sql.append(" group by cate_id ");
List<Map> extendList = baseDao.queryForList(sql.toString(),param);
-
+
Map dataSetMap = new HashMap();
Map extendSetMap = new HashMap();
for(Map map:dataList){
@@ -576,7 +580,7 @@
for(Map map:extendList){
extendSetMap.put(map.get("cate_id"), map);
}
-
+
List<Map> series = new ArrayList<Map>();
List<String> categories = new ArrayList<String>();
if(statisType.equals("month")){
@@ -589,7 +593,7 @@
categories.add("涓婂搴�");
categories.add("鏈搴�");
}
-
+
for(int i=0;i<4;i++){
Map tempMap = new HashMap();
List tempList = new ArrayList();
@@ -608,7 +612,7 @@
if(j==1){
key = selDate;
}
-
+
if(i==0){
if(extendSetMap.get(key)!=null){
tempList.add(((Map)extendSetMap.get(key)).get("num"));
@@ -625,7 +629,7 @@
}
tempMap.put("data", tempList);
series.add(tempMap);
-
+
}
chartMap.put("categories", categories);
chartMap.put("series", series);
@@ -644,7 +648,7 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,source_id from sc_workflow_incident where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by source_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
@@ -652,7 +656,7 @@
for(Map data:dataList){
dataSetMap.put(data.get("source_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.INCIDENT_SOURCE);
List seriesData = new ArrayList();
for(Map dic:dicList){
@@ -680,16 +684,16 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,resolve_type_id from sc_workflow_incident where customer_id = :cusId and resolve_type_id is not null ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by resolve_type_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
-
+
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
for(Map data:dataList){
dataSetMap.put(data.get("resolve_type_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.CLOSE_REASON);
List<Map> series = new ArrayList<Map>();
List<String> categories = new ArrayList<String>();
@@ -719,12 +723,12 @@
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
param.put("cusId", cusId);
- sql.append(" select TRUNCATE((total_num-IFNULL(outtime_num,0))*100/total_num,2) ontime_rate from ( ");
+ sql.append(" select case when total_num=0 then 0 else TRUNCATE((total_num-IFNULL(outtime_num,0))*100/total_num,2) end ontime_rate from ( ");
sql.append(" select count(id) total_num from sc_workflow_incident where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mtotal, ( ");
sql.append(" select count(id) outtime_num from sc_workflow_incident where customer_id = :cusId and (answer_timeout=1 or deal_timeout=1) ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mfinish ");
Double y = baseDao.queryForDouble(sql.toString(), param);
Map chartMap = new HashMap();
@@ -743,16 +747,16 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,sla_id from sc_workflow_incident where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by sla_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
-
+
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
for(Map data:dataList){
dataSetMap.put(data.get("sla_id"), data);
}
-
+
sql.setLength(0);
sql.append(" select id,level_name from sc_sla where state=1 order by serial asc");
List<Map> dicList = baseDao.queryForList(sql.toString(), param);
@@ -779,49 +783,49 @@
SqlParameter param = new SqlParameter();
param.put("cusId", cusId);
param.put("nodeTemplateId", Constants.YXCL);
- List<String> lebalList = new ArrayList<String>();
- List<List<String>> dataList = new ArrayList<List<String>>();
-
+ List<String> lebalList = new ArrayList<String>();
+ List<List<String>> dataList = new ArrayList<List<String>>();
+
StringBuilder sql = new StringBuilder();
sql.append(" select mtotal.THIRD_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, ");
sql.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((total_num-IFNULL(timeout_num, 0))*100/total_num,2) ontime_rate from ");
sql.append(" (select THIRD_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
- sql.append(" group by THIRD_CATEGORY_ID) mtotal ");
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
+ sql.append(" group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME) mtotal ");
sql.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT 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 ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by i.THIRD_CATEGORY_ID) manswer ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(i.id) activi_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by i.THIRD_CATEGORY_ID) mactivi ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE in (2,4) and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by i.THIRD_CATEGORY_ID) mfinish ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(id) timeout_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by THIRD_CATEGORY_ID) mtimeout ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mtimeout.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(id) pri_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where SLA_NAME='LEVEL1' and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by THIRD_CATEGORY_ID) mpri ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mpri.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(id) ques_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where QUESTIONID is not null and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by THIRD_CATEGORY_ID) mques ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mques.THIRD_CATEGORY_ID ");
sql.append(" left join (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by THIRD_CATEGORY_ID) mdeal ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mdeal.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(i.id) first_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by i.THIRD_CATEGORY_ID) mfirst ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mfirst.THIRD_CATEGORY_ID ");
List<Map> baseList = baseDao.queryForList(sql.toString(),param);
-
+
//缁勭粐琛ㄥご
lebalList.add("鏈嶅姟鐩綍");
lebalList.add("浜嬩欢鏁�");
@@ -834,41 +838,41 @@
lebalList.add("骞冲潎澶勭悊鏃堕暱");
lebalList.add("涓�绾胯В鍐崇巼");
lebalList.add("鏈嶅姟绾у埆杈炬垚鐜�");
-
+
//缁勭粐鏁版嵁
if(baseList!=null&&baseList.size()>0){
sql.setLength(0);
sql.append(" select 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, ");
sql.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((total_num-IFNULL(timeout_num, 0))*100/total_num,2) ontime_rate from ");
sql.append(" (select count(id) total_num from SC_WORKFLOW_INCIDENT where customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mtotal, ");
sql.append(" (select count(i.id) answer_num from SC_WORKFLOW_INCIDENT 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 ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) manswer, ");
sql.append(" (select count(i.id) activi_num from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mactivi, ");
sql.append(" (select count(i.id) finish_num from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE in (2,4) and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mfinish, ");
sql.append(" (select count(id) timeout_num from SC_WORKFLOW_INCIDENT where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mtimeout, ");
sql.append(" (select count(id) pri_num from SC_WORKFLOW_INCIDENT where SLA_NAME='LEVEL1' and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mpri, ");
sql.append(" (select count(id) ques_num from SC_WORKFLOW_INCIDENT where QUESTIONID is not null and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mques, ");
sql.append(" (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time from SC_WORKFLOW_INCIDENT where customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mdeal, ");
sql.append(" (select count(i.id) first_num from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mfirst ");
Map extendMap = baseDao.queryForMap(sql.toString(),param);
-
+
for(Map base:baseList){
List<String> tempList = new ArrayList<String>();
tempList.add(ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
@@ -898,7 +902,7 @@
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("ontime_rate"))+"%");
dataList.add(tempList);
}
-
+
Map res = new HashMap();
res.put("lebalList",lebalList);
res.put("dataList",dataList);
@@ -914,7 +918,7 @@
param.put("selDate", selDate);
param.put("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT);
sql.append(" select count(id) num,wfstate from workflow_base where businesstype =:busType and customer_id = :cusId ");
- setWhereSql(sql, "createtime", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATETIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by wfstate order by wfstate");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
Map<String,Map> dataxx = new HashMap();
@@ -943,7 +947,7 @@
for(Map data:dataList){
dataSetMap.put(data.get("wfstate").toString(), data);
}
-
+
List<Map> dicList = new ArrayList<Map>();
for(int i=1;i<=3;i++){
Map map = new HashMap();
@@ -957,7 +961,7 @@
map.put("DATAKEY", i);
dicList.add(map);
}
-
+
List seriesData = new ArrayList();
for(Map dic:dicList){
List tempList = new ArrayList();
@@ -973,7 +977,7 @@
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -985,9 +989,9 @@
df = new SimpleDateFormat("yyyyw");
}
Calendar cal = Calendar.getInstance();
- cal.setFirstDayOfWeek(Calendar.MONDAY); //璁剧疆姣忓懆鐨勭涓�澶╀负鏄熸湡涓�
- cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//姣忓懆浠庡懆涓�寮�濮�
- cal.setMinimalDaysInFirstWeek(7);
+ cal.setFirstDayOfWeek(Calendar.MONDAY); //璁剧疆姣忓懆鐨勭涓�澶╀负鏄熸湡涓�
+ cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//姣忓懆浠庡懆涓�寮�濮�
+ cal.setMinimalDaysInFirstWeek(7);
try {
cal.setTime(df.parse(selDate));
} catch (ParseException e) {
@@ -1002,54 +1006,63 @@
if(statisType.equals("quarter")){
lastDate = getLastQuarter(selDate);
}
-
+
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
param.put("cusId",cusId);
param.put("selDate",selDate);
param.put("lastDate",lastDate);
param.put("busType",Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION);
-
-
+
+
+ String groupByStr = "";
if(statisType.equals("month")){
- sql.append(" select date_format(createtime,'%Y%m') cate_id,");
+ sql.append(" select date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%m') cate_id,");
+ groupByStr = "date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%m') ";
}else if(statisType.equals("week")){
- sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
+ sql.append(" select concat(year(to_date(createtime,'YYYYMMDDHH24MISS')),week(to_date(createtime,'YYYYMMDDHH24MISS'),5)) cate_id,");
+ groupByStr = "concat(year(to_date(createtime,'YYYYMMDDHH24MISS')),week(to_date(createtime,'YYYYMMDDHH24MISS'),5)) ";
}else if(statisType.equals("quarter")){
- sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
+ sql.append(" select concat(year(to_date(createtime,'YYYYMMDDHH24MISS')),quarter(to_date(createtime,'YYYYMMDDHH24MISS'))) cate_id,");
+ groupByStr = " concat(year(to_date(createtime,'YYYYMMDDHH24MISS')),quarter(to_date(createtime,'YYYYMMDDHH24MISS'))) ";
}
sql.append(" wfstate data_name,count(id) num from workflow_base where wfstate in (1,2,3) and businesstype=:busType ");
if(statisType.equals("month")){
- sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
+ sql.append(" and (date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%m') = :selDate or date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%m')=:lastDate) ");
}else if(statisType.equals("week")){
- sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5)) ");
- }else if(statisType.equals("quarter")){
- sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5)) ");
+ sql.append(" and year(to_date(createtime,'YYYYMMDDHH24MISS'))=left(:selDate,4) and (week(to_date(createtime,'YYYYMMDDHH24MISS'),5) = substring(:selDate,5) or week(to_date(createtime,'YYYYMMDDHH24MISS'),5)=substring(:lastDate,5)) ");
+ }else if(statisType.equals("to_date(createtime,'YYYYMMDDHH24MISS')")){
+ sql.append(" and year(to_date(createtime,'YYYYMMDDHH24MISS'))=left(:selDate,4) and (quarter(to_date(createtime,'YYYYMMDDHH24MISS')) = substring(:selDate,5) or quarter(to_date(createtime,'YYYYMMDDHH24MISS'))=substring(:lastDate,5)) ");
}
-
- sql.append(" and customer_id=:cusId group by cate_id,data_name ");
+
+ sql.append(" and customer_id=:cusId group by "+ groupByStr +",wfstate ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
-
+
+ String newcreatetime = "to_date(createtime,'YYYYMMDDHH24MISS')";
if(dataList!=null&&dataList.size()>0){
+ String groupCateId="";
sql.setLength(0);
if(statisType.equals("month")){
- sql.append(" select date_format(createtime,'%Y%m') cate_id,");
+ sql.append(" select date_format(" + newcreatetime+",'%Y%m') cate_id,");
+ groupCateId = " date_format(" + newcreatetime+",'%Y%m') ";
}else if(statisType.equals("week")){
- sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
+ sql.append(" select concat(year("+newcreatetime+"),week("+newcreatetime+",5)) cate_id,");
+ groupCateId = " concat(year("+newcreatetime+"),week("+newcreatetime+",5)) ";
}else if(statisType.equals("quarter")){
- sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
+ sql.append(" select concat(year("+newcreatetime+"),quarter("+newcreatetime+")) cate_id,");
+ groupCateId=" concat(year("+newcreatetime+"),quarter("+newcreatetime+")) ";
}
sql.append(" count(id) num FROM workflow_base WHERE customer_id = :cusId and businesstype=:busType ");
if(statisType.equals("month")){
- sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
+ sql.append(" and (date_format("+newcreatetime+",'%Y%m') = :selDate or date_format("+newcreatetime+",'%Y%m')=:lastDate) ");
}else if(statisType.equals("week")){
- sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5)) ");
+ sql.append(" and year("+newcreatetime+")=left(:selDate,4) and (week("+newcreatetime+",5) = substring(:selDate,5) or week("+newcreatetime+",5)=substring(:lastDate,5)) ");
}else if(statisType.equals("quarter")){
- sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5)) ");
+ sql.append(" and year("+newcreatetime+")=left(:selDate,4) and (quarter("+newcreatetime+") = substring(:selDate,5) or quarter("+newcreatetime+")=substring(:lastDate,5)) ");
}
- sql.append(" group by cate_id ");
+ sql.append(" group by "+groupCateId);
List<Map> extendList = baseDao.queryForList(sql.toString(),param);
-
+
Map dataSetMap = new HashMap();
Map extendSetMap = new HashMap();
for(Map map:dataList){
@@ -1058,7 +1071,7 @@
for(Map map:extendList){
extendSetMap.put(map.get("cate_id"), map);
}
-
+
List<Map> series = new ArrayList<Map>();
List<String> categories = new ArrayList<String>();
if(statisType.equals("month")){
@@ -1071,7 +1084,7 @@
categories.add("涓婂搴�");
categories.add("鏈搴�");
}
-
+
for(int i=0;i<4;i++){
Map tempMap = new HashMap();
List tempList = new ArrayList();
@@ -1090,7 +1103,7 @@
if(j==1){
key = selDate;
}
-
+
if(i==0){
if(extendSetMap.get(key)!=null){
tempList.add(((Map)extendSetMap.get(key)).get("num"));
@@ -1107,15 +1120,15 @@
}
tempMap.put("data", tempList);
series.add(tempMap);
-
+
}
-
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -1127,7 +1140,7 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,source_id from sc_workflow_question where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by source_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
@@ -1135,7 +1148,7 @@
for(Map data:dataList){
dataSetMap.put(data.get("source_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.QUESTION_SOURCE);
List seriesData = new ArrayList();
for(Map dic:dicList){
@@ -1163,16 +1176,16 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,resolve_type_id from sc_workflow_question where customer_id = :cusId and state=7 ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by resolve_type_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
-
+
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
for(Map data:dataList){
dataSetMap.put(data.get("resolve_type_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.QUESTIONCLOSE_RESOLVE_TYPE);
List<Map> series = new ArrayList<Map>();
List<String> categories = new ArrayList<String>();
@@ -1189,13 +1202,13 @@
tempMap.put("data", tempList);
series.add(tempMap);
}
-
+
chartMap.put("series", series);
chartMap.put("categories", categories);
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -1207,16 +1220,16 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,influence_id from sc_workflow_question where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(create_time,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by influence_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
-
+
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
for(Map data:dataList){
dataSetMap.put(data.get("influence_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.EVENT_EFFECT_DG);
List seriesData = new ArrayList();
for(Map dic:dicList){
@@ -1241,41 +1254,41 @@
SqlParameter param = new SqlParameter();
param.put("cusId", cusId);
//瀹氫箟鎶ヨ〃鍙橀噺
- List<String> lebalList = new ArrayList<String>();
- List<List<String>> dataList = new ArrayList<List<String>>();
+ List<String> lebalList = new ArrayList<String>();
+ List<List<String>> dataList = new ArrayList<List<String>>();
//鏌ヨ鏁版嵁
StringBuilder sql = new StringBuilder();
sql.append(" select THIRD_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(sleep_num,0) sleep_num, ");
sql.append(" IFNULL(finish_num,0) finish_num, IFNULL(close_num,0) close_num from ( ");
sql.append(" select count(b.id) total_num,i.THIRD_CATEGORY_NAME,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
- setWhereSql(sql, "b.createtime", selDate, statisType, param);
- sql.append(" GROUP BY I.THIRD_CATEGORY_ID ");
+ setWhereSql(sql, "to_date(b.createtime,'YYYYMMDDHH24MISS')", selDate, statisType, param);
+ sql.append(" GROUP BY I.THIRD_CATEGORY_ID,i.THIRD_CATEGORY_NAME ");
sql.append(" ) mtotal left join ( ");
sql.append(" select count(b.id) answer_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i,( ");
sql.append(" select FLOWID from workflow_node where FLOWSTATE = 1 group by FLOWID ");
sql.append(" ) n where b.ID = i.FLOW_ID and b.id = n.FLOWID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
- setWhereSql(sql, "b.createtime", selDate, statisType, param);
+ setWhereSql(sql, "to_date(b.createtime,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" and b.WFSTATE = 1 GROUP BY I.THIRD_CATEGORY_ID ");
sql.append(" ) manswer on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID left join ( ");
sql.append(" select count(b.id) activi_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
- setWhereSql(sql, "b.createtime", selDate, statisType, param);
+ setWhereSql(sql, "to_date(b.createtime,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" and b.WFSTATE=1 GROUP BY I.THIRD_CATEGORY_ID ");
sql.append(" ) mactivi on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID left join ( ");
sql.append(" select count(b.id) sleep_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
- setWhereSql(sql, "b.createtime", selDate, statisType, param);
+ setWhereSql(sql, "to_date(b.createtime,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" and b.WFSTATE=6 GROUP BY I.THIRD_CATEGORY_ID ");
sql.append(" ) msleep on mtotal.THIRD_CATEGORY_ID = msleep.THIRD_CATEGORY_ID left join ( ");
sql.append(" select count(b.id) finish_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
- setWhereSql(sql, "b.createtime", selDate, statisType, param);
+ setWhereSql(sql, "to_date(b.createtime,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" and (b.WFSTATE=2 or b.WFSTATE=4) GROUP BY I.THIRD_CATEGORY_ID ");
sql.append(" ) mfinish on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID left join ( ");
sql.append(" select count(b.id) close_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
- setWhereSql(sql, "b.createtime", selDate, statisType, param);
+ setWhereSql(sql, "to_date(b.createtime,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" and i.state=7 GROUP BY I.THIRD_CATEGORY_ID ");
sql.append(" ) mclose on mtotal.THIRD_CATEGORY_ID = mclose.THIRD_CATEGORY_ID ");
List<Map> baseList = baseDao.queryForList(sql.toString(),param);
@@ -1323,7 +1336,7 @@
sql.append(" and i.state=7 ");
sql.append(" ) mclose ");
Map extendMap = baseDao.queryForMap(sql.toString(),param);
-
+
for(Map base:baseList){
List<String> tempList = new ArrayList<String>();
tempList.add(ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
@@ -1344,7 +1357,7 @@
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("finish_num")));
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("close_num")));
dataList.add(tempList);
-
+
}
Map res = new HashMap();
res.put("lebalList",lebalList);
@@ -1360,9 +1373,9 @@
df = new SimpleDateFormat("yyyyw");
}
Calendar cal = Calendar.getInstance();
- cal.setFirstDayOfWeek(Calendar.MONDAY); //璁剧疆姣忓懆鐨勭涓�澶╀负鏄熸湡涓�
- cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//姣忓懆浠庡懆涓�寮�濮�
- cal.setMinimalDaysInFirstWeek(7);
+ cal.setFirstDayOfWeek(Calendar.MONDAY); //璁剧疆姣忓懆鐨勭涓�澶╀负鏄熸湡涓�
+ cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//姣忓懆浠庡懆涓�寮�濮�
+ cal.setMinimalDaysInFirstWeek(7);
try {
cal.setTime(df.parse(selDate));
} catch (ParseException e) {
@@ -1377,34 +1390,39 @@
if(statisType.equals("quarter")){
lastDate = getLastQuarter(selDate);
}
-
+
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
param.put("cusId",cusId);
param.put("selDate",selDate);
param.put("lastDate",lastDate);
param.put("busType",Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT_LOCAL);
-
-
+
+
+ String gruopByStr = "";
+ String newCreateTime = "to_date(createtime,'YYYYMMDDHH24MISS')";
if(statisType.equals("month")){
- sql.append(" select date_format(createtime,'%Y%m') cate_id,");
+ sql.append(" select date_format(" + newCreateTime +",'%Y%m') cate_id,");
+ gruopByStr = "date_format("+ newCreateTime +",'%Y%m') ";
}else if(statisType.equals("week")){
- sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
+ sql.append(" select concat(year(" + newCreateTime + "),week("+newCreateTime + ",5)) cate_id,");
+ gruopByStr = " concat(year(" + newCreateTime + "),week("+newCreateTime + ",5))";
}else if(statisType.equals("quarter")){
- sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
+ sql.append(" select concat(year(" + newCreateTime +"),quarter(" + newCreateTime + ")) cate_id,");
+ gruopByStr = " concat(year(" + newCreateTime +"),quarter(" + newCreateTime + ")) ";
}
sql.append(" ( CASE WHEN WFSTATE = 1 THEN '1' WHEN WFSTATE = 2 THEN '2' WHEN WFSTATE = 3 THEN '3' WHEN WFSTATE = 4 THEN '2' WHEN WFSTATE = 6 THEN '6' END ) data_name,count(id) num from workflow_base where wfstate in (1,2,3,4) and businesstype=:busType ");
if(statisType.equals("month")){
- sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
+ sql.append(" and (date_format("+newCreateTime+",'%Y%m') = :selDate or date_format("+newCreateTime+",'%Y%m')=:lastDate) ");
}else if(statisType.equals("week")){
- sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5)) ");
+ sql.append(" and year("+newCreateTime+")=left(:selDate,4) and (week("+newCreateTime+",5) = substring(:selDate,5) or week("+newCreateTime+",5)=substring(:lastDate,5)) ");
}else if(statisType.equals("quarter")){
- sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5)) ");
+ sql.append(" and year("+newCreateTime+")=left(:selDate,4) and (quarter("+newCreateTime+") = substring(:selDate,5) or quarter("+newCreateTime+")=substring(:lastDate,5)) ");
}
-
- sql.append(" and customer_id=:cusId group by cate_id,data_name ");
+
+ sql.append(" and customer_id=:cusId group by "+gruopByStr +",WFSTATE ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
-
+
if(dataList!=null&&dataList.size()>0){
sql.setLength(0);
if(statisType.equals("month")){
@@ -1424,7 +1442,7 @@
}
sql.append(" group by cate_id ");
List<Map> extendList = baseDao.queryForList(sql.toString(),param);
-
+
Map dataSetMap = new HashMap();
Map extendSetMap = new HashMap();
for(Map map:dataList){
@@ -1433,7 +1451,7 @@
for(Map map:extendList){
extendSetMap.put(map.get("cate_id"), map);
}
-
+
List<Map> series = new ArrayList<Map>();
List<String> categories = new ArrayList<String>();
if(statisType.equals("month")){
@@ -1446,7 +1464,7 @@
categories.add("涓婂搴�");
categories.add("鏈搴�");
}
-
+
for(int i=0;i<4;i++){
Map tempMap = new HashMap();
List tempList = new ArrayList();
@@ -1465,7 +1483,7 @@
if(j==1){
key = selDate;
}
-
+
if(i==0){
if(extendSetMap.get(key)!=null){
tempList.add(((Map)extendSetMap.get(key)).get("num"));
@@ -1482,15 +1500,15 @@
}
tempMap.put("data", tempList);
series.add(tempMap);
-
+
}
-
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -1502,7 +1520,7 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,source_id from sc_workflow_incident_local where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by source_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
@@ -1510,7 +1528,7 @@
for(Map data:dataList){
dataSetMap.put(data.get("source_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.INCIDENT_SOURCE);
List seriesData = new ArrayList();
for(Map dic:dicList){
@@ -1527,7 +1545,7 @@
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -1539,15 +1557,15 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,type_id,type_name from sc_workflow_incident_local where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
- sql.append(" group by type_id ");
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
+ sql.append(" group by type_id ,type_name");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
for(Map data:dataList){
dataSetMap.put(data.get("type_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.INCIDENT_TYPE);
List seriesData = new ArrayList();
for(Map dic:dicList){
@@ -1575,7 +1593,7 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,resolve_type_id from sc_workflow_incident_local where customer_id = :cusId and resolve_type_id is not null ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by resolve_type_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
@@ -1583,7 +1601,7 @@
for(Map data:dataList){
dataSetMap.put(data.get("resolve_type_id"), data);
}
-
+
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.CLOSE_REASON);
List<Map> series = new ArrayList<Map>();
List<String> categories = new ArrayList<String>();
@@ -1600,7 +1618,7 @@
tempMap.put("data", tempList);
series.add(tempMap);
}
-
+
chartMap.put("series", series);
chartMap.put("categories", categories);
}else{
@@ -1614,12 +1632,12 @@
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
param.put("cusId", cusId);
- sql.append(" select TRUNCATE((total_num-IFNULL(outtime_num,0))*100/total_num,2) ontime_rate from ( ");
+ sql.append(" select case when total_num=0 then 0 else TRUNCATE((total_num-IFNULL(outtime_num,0))*100/total_num,2) end ontime_rate from ( ");
sql.append(" select count(id) total_num from sc_workflow_incident_local where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mtotal, ( ");
sql.append(" select count(id) outtime_num from sc_workflow_incident_local where customer_id = :cusId and (answer_timeout=1 or deal_timeout=1) ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mfinish ");
Double y = baseDao.queryForDouble(sql.toString(), param);
Map chartMap = new HashMap();
@@ -1638,7 +1656,7 @@
param.put("cusId", cusId);
param.put("selDate", selDate);
sql.append(" select count(id) num,sla_id from sc_workflow_incident_local where customer_id = :cusId ");
- setWhereSql(sql, "create_time", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by sla_id ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
@@ -1646,7 +1664,7 @@
for(Map data:dataList){
dataSetMap.put(data.get("sla_id"), data);
}
-
+
sql.setLength(0);
sql.append(" select id,level_name from sc_sla where state=1 order by serial asc");
List<Map> dicList = baseDao.queryForList(sql.toString(), param);
@@ -1673,49 +1691,49 @@
SqlParameter param = new SqlParameter();
param.put("cusId", cusId);
param.put("nodeTemplateId", Constants.YXCL);
- List<String> lebalList = new ArrayList<String>();
- List<List<String>> dataList = new ArrayList<List<String>>();
-
+ List<String> lebalList = new ArrayList<String>();
+ List<List<String>> dataList = new ArrayList<List<String>>();
+
StringBuilder sql = new StringBuilder();
sql.append(" select mtotal.THIRD_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, ");
sql.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(finish_num, 0)*100/total_num,2) first_rate,truncate((total_num-IFNULL(timeout_num, 0))*100/total_num,2) ontime_rate from ");
sql.append(" (select a.THIRD_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL a,workflow_base b where a.customer_id=:cusId and a.id=b.BUSINESS_ID and b.WFSTATE<>3 ");
- setWhereSql(sql, "a.CREATE_TIME", selDate, statisType, param);
- sql.append(" group by a.THIRD_CATEGORY_ID) mtotal ");
+ setWhereSql(sql, "to_date(a.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
+ sql.append(" group by a.THIRD_CATEGORY_ID,a.THIRD_CATEGORY_NAME) mtotal ");
sql.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.WFSTATE<>3 and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by i.THIRD_CATEGORY_ID) manswer ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
sql.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 b.WFSTATE<>3 and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by i.THIRD_CATEGORY_ID) mactivi ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID ");
sql.append(" left join (select 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 in (2,4) and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by i.THIRD_CATEGORY_ID) mfinish ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(id) timeout_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by THIRD_CATEGORY_ID) mtimeout ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mtimeout.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(id) pri_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where SLA_NAME='LEVEL1' and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by THIRD_CATEGORY_ID) mpri ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mpri.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(id) ques_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where QUESTIONID is not null and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by THIRD_CATEGORY_ID) mques ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mques.THIRD_CATEGORY_ID ");
sql.append(" left join (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by THIRD_CATEGORY_ID) mdeal ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mdeal.THIRD_CATEGORY_ID ");
sql.append(" left join (select count(i.id) first_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 (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" group by i.THIRD_CATEGORY_ID) mfirst ");
sql.append(" on mtotal.THIRD_CATEGORY_ID = mfirst.THIRD_CATEGORY_ID ");
List<Map> baseList = baseDao.queryForList(sql.toString(),param);
-
+
//缁勭粐琛ㄥご
lebalList.add("鏈嶅姟鐩綍");
lebalList.add("浜嬩欢鏁�");
@@ -1728,41 +1746,41 @@
lebalList.add("骞冲潎澶勭悊鏃堕暱");
lebalList.add("椹诲満瑙ｅ喅鐜�");
lebalList.add("鏈嶅姟绾у埆杈炬垚鐜�");
-
+
//缁勭粐鏁版嵁
if(baseList!=null&&baseList.size()>0){
sql.setLength(0);
sql.append(" select 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, ");
sql.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(finish_num, 0)*100/total_num,2) first_rate,truncate((total_num-IFNULL(timeout_num, 0))*100/total_num,2) ontime_rate from ");
sql.append(" (select count(a.id) total_num from SC_WORKFLOW_INCIDENT_LOCAL a,workflow_base b where a.customer_id=:cusId and a.id=b.BUSINESS_ID and b.WFSTATE<>3 ");
- setWhereSql(sql, "a.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(a.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mtotal, ");
sql.append(" (select count(i.id) answer_num from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.WFSTATE<>3 and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) manswer, ");
sql.append(" (select count(i.id) activi_num from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mactivi, ");
sql.append(" (select count(i.id) finish_num from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE in (2,4) and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mfinish, ");
sql.append(" (select count(id) timeout_num from SC_WORKFLOW_INCIDENT_LOCAL where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mtimeout, ");
sql.append(" (select count(id) pri_num from SC_WORKFLOW_INCIDENT_LOCAL where SLA_NAME='LEVEL1' and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mpri, ");
sql.append(" (select count(id) ques_num from SC_WORKFLOW_INCIDENT_LOCAL where QUESTIONID is not null and customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mques, ");
sql.append(" (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time from SC_WORKFLOW_INCIDENT_LOCAL where customer_id=:cusId ");
- setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mdeal, ");
sql.append(" (select count(i.id) first_num 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 (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.customer_id=:cusId ");
- setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
+ setWhereSql(sql, "to_date(i.CREATE_TIME,'YYYYMMDDHH24MISS')", selDate, statisType, param);
sql.append(" ) mfirst ");
Map extendMap = baseDao.queryForMap(sql.toString(),param);
-
+
for(Map base:baseList){
List<String> tempList = new ArrayList<String>();
tempList.add(ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
@@ -1773,8 +1791,14 @@
tempList.add(ConvertUtil.obj2StrBlank(base.get("timeout_num")));
tempList.add(ConvertUtil.obj2StrBlank(base.get("pri_num")));
tempList.add(ConvertUtil.obj2StrBlank(base.get("ques_num")));
-
- tempList.add(DateUtil.secToTime(Math.round((Double)base.get("avg_time"))));
+ Object avg_time = base.get("avg_time");
+ String avgTimeSTr = null;
+ if (avg_time == null){
+ avgTimeSTr = "0";
+ } else {
+ avgTimeSTr = String.valueOf(avg_time);
+ }
+ tempList.add(DateUtil.secToTime(Math.round(Double.valueOf(avgTimeSTr))));
tempList.add(ConvertUtil.obj2StrBlank(base.get("first_rate"))+"%");
tempList.add(ConvertUtil.obj2StrBlank(base.get("ontime_rate"))+"%");
dataList.add(tempList);
@@ -1782,13 +1806,21 @@
List<String> tempList = new ArrayList<String>();
tempList.add("鍚堣");
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("total_num")));
+
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("answer_num")));
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("activi_num")));
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("finish_num")));
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("timeout_num")));
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("pri_num")));
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("ques_num")));
- tempList.add(DateUtil.secToTime(Math.round((Double)extendMap.get("avg_time"))));
+ Object avg_time = extendMap.get("avg_time");
+ String avgTimeSTr = null;
+ if (avg_time == null){
+ avgTimeSTr = "0";
+ } else {
+ avgTimeSTr = String.valueOf(avg_time);
+ }
+ tempList.add(DateUtil.secToTime(Math.round(Double.valueOf(avgTimeSTr))));
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("first_rate"))+"%");
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("ontime_rate"))+"%");
dataList.add(tempList);
@@ -1821,21 +1853,25 @@
if(statisType.equals("quarter")){
lastDate = getLastQuarter(selDate);
}
-
+
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
param.put("cusId",cusId);
param.put("selDate",selDate);
param.put("lastDate",lastDate);
param.put("busType",Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT);
-
+
+ String cateIdStr = "";
//鏌ヨ鏁版嵁
if(statisType.equals("month")){
sql.append(" select i.month cate_id, ");
+ cateIdStr = " i.month ";
}else if(statisType.equals("year")){
sql.append(" select left(i.month,4) cate_id, ");
+ cateIdStr = " left(i.month,4) ";
}else if(statisType.equals("quarter")){
sql.append(" select concat(left(i.month,4),quarter(concat(i.month,'01'))) cate_id, ");
+ cateIdStr = " concat(left(i.month,4),quarter(concat(i.month,'01'))) ";
}
sql.append(" d.satis_type data_name,truncate(avg(d.satis_score),0) num from totle_satis_info i,totle_satis_detail d where i.id = d.bus_id ");
if(statisType.equals("month")){
@@ -1845,7 +1881,7 @@
}else if(statisType.equals("quarter")){
sql.append(" and (concat(left(i.month,4),quarter(concat(i.month,'01'))) = :selDate or concat(left(i.month,4),quarter(concat(i.month,'01')))=:lastDate) ");
}
- sql.append(" and i.customer_id=:cusId group by cate_id,data_name ");
+ sql.append(" and i.customer_id=:cusId group by "+cateIdStr+",d.satis_type ");
sql.append(" union ");
if(statisType.equals("month")){
sql.append(" select i.month cate_id, ");
@@ -1862,7 +1898,7 @@
}else if(statisType.equals("quarter")){
sql.append(" and (concat(left(i.month,4),quarter(concat(i.month,'01'))) = :selDate or concat(left(i.month,4),quarter(concat(i.month,'01')))=:lastDate) ");
}
- sql.append(" and i.customer_id=:cusId group by cate_id,data_name ");
+ sql.append(" and i.customer_id=:cusId group by "+cateIdStr+" ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
@@ -1870,8 +1906,8 @@
for(Map map:dataList){
dataSetMap.put(map.get("data_name")+"_"+map.get("cate_id"), map);
}
-
-
+
+
List<Map> series = new ArrayList<Map>();
List<String> categories = new ArrayList<String>();
if(statisType.equals("month")){
@@ -1884,7 +1920,7 @@
categories.add("涓婂搴�");
categories.add("鏈搴�");
}
-
+
for(int i=0;i<=3;i++){
if(i!=2){
Map tempMap = new HashMap();
@@ -1911,9 +1947,9 @@
tempMap.put("data", tempList);
series.add(tempMap);
}
-
+
}
-
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
@@ -1936,7 +1972,7 @@
sql.append(" and concat(left(i.month,4),quarter(concat(i.month,'01')))=:selDate ");
}
sql.append(" and i.customer_id=:cusId group by d.satis_type ");
-
+
List<Map> dataList= baseDao.queryForList(sql.toString(), param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
@@ -1952,7 +1988,7 @@
}
}
List<Map> series = new ArrayList<Map>();
-
+
for(int i=1;i<=2;i++){
Map tempMap = new HashMap();
if(i==1){
@@ -1978,7 +2014,7 @@
}
}
}
-
+
}
tempMap.put("data", tempList);
series.add(tempMap);
@@ -1998,10 +2034,10 @@
param.addValue("cusId", cusId)
.addValue("selDate", selDate)
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT);
-
+
StringBuilder sql = new StringBuilder();
- sql.append(" select count(id) num,date_format(createtime,'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
- sql.append(" and date_format(createtime,'%Y') = :selDate group by cate_id ");
+ sql.append(" select count(id) num,date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
+ sql.append(" and date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :selDate group by date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%c') ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
@@ -2024,13 +2060,13 @@
}
tempMap.put("data", tempList);
series.add(tempMap);
-
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -2046,16 +2082,16 @@
}
cal.add(Calendar.YEAR, -1);
String lastDate = df.format(cal.getTime());
-
+
SqlParameter param = new SqlParameter();
param.addValue("cusId", cusId)
.addValue("selDate", selDate)
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT)
.addValue("lastDate", lastDate);
-
+
StringBuilder sql = new StringBuilder();
- sql.append(" select count(id) num,date_format(createtime,'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
- sql.append(" and (date_format(createtime,'%Y') = :selDate or date_format(createtime,'%Y') = :lastDate) group by cate_id ");
+ sql.append(" select count(id) num,date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
+ sql.append(" and (date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :selDate or date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :lastDate) group by date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
@@ -2065,7 +2101,7 @@
List<String> categories = new ArrayList<String>();
categories.add("鏁伴噺");
List<Map> series = new ArrayList<Map>();
-
+
for(int i=1;i<=2;i++){
Map tempMap = new HashMap();
tempMap.put("name", "鍘诲勾");
@@ -2083,13 +2119,13 @@
tempMap.put("data", tempList);
series.add(tempMap);
}
-
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -2100,10 +2136,10 @@
param.addValue("cusId", cusId)
.addValue("selDate", selDate)
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION);
-
+
StringBuilder sql = new StringBuilder();
- sql.append(" select count(id) num,date_format(createtime,'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
- sql.append(" and date_format(createtime,'%Y') = :selDate group by cate_id ");
+ sql.append(" select count(id) num,date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
+ sql.append(" and date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :selDate group by date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%c') ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
@@ -2146,16 +2182,16 @@
}
cal.add(Calendar.YEAR, -1);
String lastDate = df.format(cal.getTime());
-
+
SqlParameter param = new SqlParameter();
param.addValue("cusId", cusId)
.addValue("selDate", selDate)
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION)
.addValue("lastDate", lastDate);
-
+
StringBuilder sql = new StringBuilder();
- sql.append(" select count(id) num,date_format(createtime,'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
- sql.append(" and (date_format(createtime,'%Y') = :selDate or date_format(createtime,'%Y') = :lastDate) group by cate_id ");
+ sql.append(" select count(id) num,date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
+ sql.append(" and (date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :selDate or date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :lastDate) group by date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
@@ -2165,7 +2201,7 @@
List<String> categories = new ArrayList<String>();
categories.add("鏁伴噺");
List<Map> series = new ArrayList<Map>();
-
+
for(int i=1;i<=2;i++){
Map tempMap = new HashMap();
tempMap.put("name", "鍘诲勾");
@@ -2183,13 +2219,13 @@
tempMap.put("data", tempList);
series.add(tempMap);
}
-
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -2200,10 +2236,10 @@
param.addValue("cusId", cusId)
.addValue("selDate", selDate)
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT_LOCAL);
-
+
StringBuilder sql = new StringBuilder();
- sql.append(" select count(id) num,date_format(createtime,'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
- sql.append(" and date_format(createtime,'%Y') = :selDate group by cate_id ");
+ sql.append(" select count(id) num,date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
+ sql.append(" and date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :selDate group by date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y%c') ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
@@ -2226,7 +2262,7 @@
}
tempMap.put("data", tempList);
series.add(tempMap);
-
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
@@ -2247,16 +2283,16 @@
}
cal.add(Calendar.YEAR, -1);
String lastDate = df.format(cal.getTime());
-
+
SqlParameter param = new SqlParameter();
param.addValue("cusId", cusId)
.addValue("selDate", selDate)
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT_LOCAL)
.addValue("lastDate", lastDate);
-
+
StringBuilder sql = new StringBuilder();
- sql.append(" select count(id) num,date_format(createtime,'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
- sql.append(" and (date_format(createtime,'%Y') = :selDate or date_format(createtime,'%Y') = :lastDate) group by cate_id ");
+ sql.append(" select count(id) num,date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
+ sql.append(" and (date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :selDate or date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') = :lastDate) group by date_format(to_date(createtime,'YYYYMMDDHH24MISS'),'%Y') ");
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
if(dataList!=null&&dataList.size()>0){
Map dataSetMap = new HashMap();
@@ -2266,7 +2302,7 @@
List<String> categories = new ArrayList<String>();
categories.add("鏁伴噺");
List<Map> series = new ArrayList<Map>();
-
+
for(int i=1;i<=2;i++){
Map tempMap = new HashMap();
tempMap.put("name", "鍘诲勾");
@@ -2284,13 +2320,13 @@
tempMap.put("data", tempList);
series.add(tempMap);
}
-
+
chartMap.put("categories", categories);
chartMap.put("series", series);
}else{
chartMap.put("nodata", true);
}
-
+
return chartMap;
}
@@ -2300,14 +2336,14 @@
SqlParameter param = new SqlParameter();
param.put("cusId",cusId);
param.put("selDate",selDate);
-
+
StringBuilder builder = new StringBuilder();
builder.append(" SELECT I.ALL_SCORE,D.SATIS_SCORE,D.SATIS_TYPE,I.MONTH FROM TOTLE_SATIS_DETAIL D ,TOTLE_SATIS_INFO I ");
- builder.append(" WHERE LEFT(I.MONTH,4)=:selDate AND D.BUS_ID = I.ID AND I.CUSTOMER_ID = :cusId GROUP BY D.SATIS_TYPE,I.MONTH ORDER BY SATIS_TYPE,MONTH");
+ builder.append(" WHERE LEFT(I.MONTH,4)=:selDate AND D.BUS_ID = I.ID AND I.CUSTOMER_ID = :cusId GROUP BY D.SATIS_TYPE,I.MONTH,I.ALL_SCORE, D.SATIS_SCORE ORDER BY SATIS_TYPE,MONTH");
List<Map> queryList = baseDao.queryForList(builder.toString(), param);
- builder.setLength(0);
+ builder.setLength(0);
Map<String,Object> queryMap = new HashMap<String,Object>();
-
+
if(queryList!=null&&queryList.size()>0){
for(Map map:queryList){
String key = map.get("SATIS_TYPE").toString()+"_"+map.get("MONTH").toString();
@@ -2318,11 +2354,11 @@
for(int i=1;i<=12;i++){
categories.add(i+"鏈�");
}
-
+
List<String> typeList = new ArrayList();
typeList.add("1");
typeList.add("3");
-
+
Map tempWholeMap = new HashMap();
List tempWholeList = new ArrayList();
tempWholeMap.put("name", "鏁翠綋寰楀垎");
@@ -2343,7 +2379,7 @@
}else{
key = key + i;
}
-
+
if(queryMap.get(key)!=null){
tempList.add(((Map)queryMap.get(key)).get("SATIS_SCORE"));
//鏁翠綋寰楀垎
@@ -2358,7 +2394,7 @@
}
}
}
-
+
tempMap.put("data", tempList);
tempMap.put("type", "column");
series.add(tempMap);
@@ -2369,7 +2405,7 @@
chartMap.put("series", series);
chartMap.put("categories", categories);
}
-
+
}else{
chartMap.put("nodata", true);
}
@@ -2378,7 +2414,7 @@
@Override
public boolean updateWorkSummary(HttpServletRequest request) {
-
+
String id = request.getParameter("id");
String note = request.getParameter("note");
String summaryType = request.getParameter("summaryType");
@@ -2389,7 +2425,7 @@
String userName = user.get("ZSXM").toString();
StringBuilder sql = new StringBuilder();
SqlParameter param = new SqlParameter();
-
+
param.addValue("note", note)
.addValue("cusId", cusId)
.addValue("userId", userId)
@@ -2398,7 +2434,7 @@
.addValue("updateTime", DateUtil.getCurrentDate14())
.addValue("summaryType", summaryType)
.addValue("summaryDate", summaryDate);
-
+
if(StringUtil.isBlank(id)){
sql.append(" insert into work_summary (id,create_time,create_user_id,create_user_name,note,update_time,summary_type,summary_date,cus_id,state) ");
sql.append(" values (:id,:createTime,:userId,:userName,:note,:updateTime,:summaryType,:summaryDate,:cusId,1) ");
@@ -2470,7 +2506,7 @@
public List<String> getCusYearToCur(String cusId) {
SqlParameter param = new SqlParameter();
param.addValue("cusId", cusId);
- String sql = "select year(CREATE_TIME) from SC_PARTNER_CUSTOMER_INFO where id = :cusId";
+ String sql = "select substr(CREATE_TIME||'',1,4) from SC_PARTNER_CUSTOMER_INFO where id = :cusId";
Integer startYear = baseDao.queryForInteger(sql,param);
Calendar cal = Calendar.getInstance();
Integer endYear = cal.get(Calendar.YEAR);
@@ -2480,7 +2516,7 @@
}
return years;
}
-
+
private String getLastQuarter(String selDate){
Integer year = Integer.valueOf(selDate.substring(0, 4));
Integer quarter = Integer.valueOf(selDate.substring(4));
@@ -2509,25 +2545,26 @@
@Override
public String getNRecordDate(Map<String, String> params) {
String col = "";
+ String createTimeStr = "to_date(createtime,'YYYYMMDDHH24MISS')";
if(params.get("type").equals("week")){
- col = "week(createtime,5)";
+ col = "week("+ createTimeStr +",5)";
}else if(params.get("type").equals("month")){
- col = "date_format(createtime,'%Y%m')";
+ col = "date_format("+ createTimeStr +",'%Y%m')";
}else if(params.get("type").equals("quarter")){
- col = "quarter(createtime)";
+ col = "quarter("+ createTimeStr +")";
}else if(params.get("type").equals("year")){
- col = "year(createtime)";
+ col = "year("+ createTimeStr +")";
}
-
+
StringBuilder sql = new StringBuilder();
- sql.append(" select GROUP_CONCAT(d) from ( ");
+ sql.append(" select wm_CONCAT(d) from ( ");
sql.append(" select $col d from workflow_base where businesstype in (8,9,10) and customer_id=:cusId ");
if(!params.get("type").equals("year")){
- sql.append(" and year(createtime)=:year ");
+ sql.append(" and substr(createtime||'',1,4)=:year ");
}
sql.append(" group by $col ");
sql.append(" ) t ");
- String rsql = sql.toString().replace("$col", col);
+ String rsql = sql.toString().replace("$col", col);
return ConvertUtil.obj2StrBlank(baseDao.queryForString(rsql,params));
}
}
--
Gitblit v1.9.1