派生自 larks_base_dm
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)); } }
