| | |
| | | sql.append(" mlevel.level_num,manswer.answer_num,mdeal.deal_num,mresolve.resolve_num,mstate.state_num "); |
| | | sql.append(" from "); |
| | | sql.append(" ( "); |
| | | sql.append(" select count(ID) total_num,DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') mdate from SC_WORKFLOW_INCIDENT "); |
| | | sql.append(" select count(ID) total_num,substr(HAPPEN_TIME||'',1,8) mdate from SC_WORKFLOW_INCIDENT "); |
| | | sql.append(" where HAPPEN_TIME>=:beginTime and HAPPEN_TIME<=:endTime "); |
| | | if(StringUtil.isNotBlank(cusId)){ |
| | | sql.append("and CUSTOMER_ID=:cusId "); |
| | | } |
| | | sql.append(" group by DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') "); |
| | | sql.append(" group by substr(HAPPEN_TIME||'',1,8) "); |
| | | |
| | | sql.append(" ) mtotal "); |
| | | //事件类型 |
| | |
| | | String column,String tempTableName,String flag,String cusId){ |
| | | sql.append(" left join "); |
| | | sql.append(" ( "); |
| | | sql.append(" select mdate,GROUP_CONCAT(num order by ordernum asc) "+numName+" from "); |
| | | sql.append(" select mdate,WM_CONCAT(num) "+numName+" from "); |
| | | sql.append(" ( "); |
| | | sql.append(" select count(i.ID) num,mdate,datakey,ordernum from "); |
| | | sql.append(" ( "); |
| | | sql.append(" select DATAKEY,mdate,ordernum from "); |
| | | sql.append(" (select DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') mdate from SC_WORKFLOW_INCIDENT group by mdate ) d,"); |
| | | sql.append(" (select substr(HAPPEN_TIME||'',1,8) mdate from SC_WORKFLOW_INCIDENT group by substr(HAPPEN_TIME||'',1,8) ) d,"); |
| | | sql.append(" ( "); |
| | | if(StringUtil.isNotBlank(categoryKey)){ |
| | | sql = getDicSql(sql,categoryKey); |
| | |
| | | }else{ |
| | | sql = getStateSql(sql,cusId); |
| | | } |
| | | sql.append(" i on i."+column+" = b.DATAKEY and DATE_FORMAT(i.HAPPEN_TIME,'%Y%m%d')=b.mdate "); |
| | | sql.append(" group by b.mdate,b.datakey order by ordernum"); |
| | | sql.append(" ) t group by mdate "); |
| | | sql.append(" i on i."+column+" = b.DATAKEY and substr(HAPPEN_TIME||'',1,8)=b.mdate "); |
| | | sql.append(" group by b.mdate,b.datakey,b.ordernum order by b.ordernum"); |
| | | sql.append(" ) t group by t.mdate "); |
| | | sql.append(" ) "+tempTableName); |
| | | sql.append(" on "+tempTableName+".mdate = mtotal.mdate "); |
| | | return sql; |
| | |
| | | StringBuilder builder4 = new StringBuilder(sql); |
| | | |
| | | |
| | | String endSql = " GROUP BY THIRD_CATEGORY_ID "; |
| | | String endSql = " GROUP BY THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME "; |
| | | |
| | | builder1.append(endSql); |
| | | |
| | |
| | | |
| | | StringBuilder builder = new StringBuilder(); |
| | | builder.append(" select mtotal.THIRD_CATEGORY_ID,mtotal.THIRD_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_num,IFNULL(close_num,0) close_num from "); |
| | | builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mtotal left join "); |
| | | builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,\n" + |
| | | " SECOND_CATEGORY_NAME,\n" + |
| | | " FIRST_CATEGORY_NAME) mtotal left join "); |
| | | builder.append(" (select count(i.id) close_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=3 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mclose "); |
| | | builder.append(" on mtotal.THIRD_CATEGORY_ID = mclose.THIRD_CATEGORY_ID "); |
| | | List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap); |
| | |
| | | |
| | | StringBuilder builder = new StringBuilder(); |
| | | builder.append(" select mtotal.THIRD_CATEGORY_ID,mtotal.THIRD_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_num from "); |
| | | builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mtotal "); |
| | | builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal "); |
| | | List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap); |
| | | |
| | | //查询数据字典 |
| | | List<Map> typeList = dataDictionaryService.getDataDictionaryByCategoryKey("INCIDENT_TYPE"); |
| | | //查询解决方式,关闭原因 |
| | | builder.setLength(0); |
| | | builder.append(" select count(id) m_num,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID) m_key,THIRD_CATEGORY_NAME,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT "); |
| | | builder.append(" where CUSTOMER_ID=:cusId and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by TYPE_ID,THIRD_CATEGORY_ID "); |
| | | builder.append(" select count(id) m_num,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID) m_key,THIRD_CATEGORY_NAME,THIRD_CATEGORY_ID,TYPE_ID from SC_WORKFLOW_INCIDENT "); |
| | | builder.append(" where CUSTOMER_ID=:cusId and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by TYPE_ID,THIRD_CATEGORY_ID,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID),THIRD_CATEGORY_NAME "); |
| | | List<Map> extendList = baseDao.queryForList(builder.toString(),paramMap); |
| | | if(extendList!=null&&extendList.size()>0){ |
| | | for(Map extend : extendList){ |
| | |
| | | |
| | | StringBuilder builder = new StringBuilder(); |
| | | builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_num,IFNULL(finish_num,0) finish_num,truncate(ifnull(avg_time,0)*3600*1000,0) avg_time,truncate(ifnull(sum_time,0)*3600*1000,0) sum_time from "); |
| | | builder.append(" (select AVG(a.DEAL_USE_TIME) avg_time,sum(a.DEAL_USE_TIME) sum_time,b.CATEGORY_NAME THIRD_CATEGORY_NAME,a.SECOND_CATEGORY_NAME,a.FIRST_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT a,SC_SERVCE_CATEGORY b where a.state!=10 and a.CUSTOMER_ID=:cusId and a.CREATE_TIME>=:beginDay and a.CREATE_TIME<=:endDay and a.THIRD_CATEGORY_ID=b.id group by a.THIRD_CATEGORY_ID) mtotal left join "); |
| | | builder.append(" (select THIRD_CATEGORY_NAME,count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and (b.WFSTATE=2 or b.WFSTATE=4) and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mfinish "); |
| | | builder.append(" (select AVG(a.DEAL_USE_TIME) avg_time,sum(a.DEAL_USE_TIME) sum_time,b.CATEGORY_NAME THIRD_CATEGORY_NAME,a.SECOND_CATEGORY_NAME,a.FIRST_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT a,SC_SERVCE_CATEGORY b where a.state!=10 and a.CUSTOMER_ID=:cusId and a.CREATE_TIME>=:beginDay and a.CREATE_TIME<=:endDay and a.THIRD_CATEGORY_ID=b.id group by a.THIRD_CATEGORY_ID,b.CATEGORY_NAME ,a.SECOND_CATEGORY_NAME,a.FIRST_CATEGORY_NAME) mtotal left join "); |
| | | builder.append(" (select THIRD_CATEGORY_NAME,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=2 or b.WFSTATE=4) and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID,i.THIRD_CATEGORY_NAME) mfinish "); |
| | | builder.append(" on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID "); |
| | | List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap); |
| | | |
| | |
| | | StringBuilder builder = new StringBuilder(); |
| | | builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(timeout_num,0) timeout_num, "); |
| | | builder.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(first_num, 0)*100/total_num,2) first_rate,truncate((IFNULL(total_num,0)-IFNULL(timeout_num,0))*100/IFNULL(total_num,0),2) ontime_rate from "); |
| | | builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mtotal "); |
| | | builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal "); |
| | | builder.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) manswer "); |
| | | builder.append(" on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID "); |
| | | builder.append(" left join (select count(i.id) activi_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mactivi "); |
| | |
| | | */ |
| | | private void getDateWhereSql(String frequency,StringBuilder sql,String colName){ |
| | | if(frequency.equals("day")||frequency==null){ |
| | | sql.append(" and date_format("+colName+",'%Y%m%d')>=:startDate and date_format("+colName+",'%Y%m%d')<=:endDate "); |
| | | sql.append(" and substr("+colName+"||'',1,8)>=:startDate and substr("+colName+"||'',1,8)<=:endDate "); |
| | | }else if(frequency.equals("month")){ |
| | | sql.append(" and date_format("+colName+",'%Y%m')>=:startDate and date_format("+colName+",'%Y%m')<=:endDate "); |
| | | sql.append(" and substr("+colName+"||'',1,6)>=:startDate and substr("+colName+"||'',1,6)<=:endDate "); |
| | | } |
| | | } |
| | | |
| | |
| | | .addValue("endDate", endDate); |
| | | |
| | | StringBuilder sql = new StringBuilder(); |
| | | sql.append(" select id as 'key',level_name as val from sc_sla where state=1 order by serial asc"); |
| | | sql.append(" select id as key,level_name as val from sc_sla where state=1 order by serial asc"); |
| | | List<Map> categoryList = baseDao.queryForList(sql.toString(),param); |
| | | |
| | | sql.setLength(0); |
| | |
| | | .addValue("startDate", startDate) |
| | | .addValue("endDate", endDate); |
| | | StringBuilder sql = new StringBuilder(); |
| | | sql.append(" select hour(happen_time) mhour,count(id) num from sc_workflow_incident c"); |
| | | sql.append(" where c.state!=10 and hour(happen_time)>=7 and hour(happen_time)<=18 "); |
| | | sql.append(" select to_number(substr(happen_time||'',9,2)) mhour,count(id) num from sc_workflow_incident c"); |
| | | sql.append(" where c.state!=10 and to_number(substr(happen_time||'',9,2))>=7 and to_number(substr(happen_time||'',9,2))<=18 "); |
| | | if(StringUtil.isNotBlank(cusId)){ |
| | | sql.append(" and customer_id = :cusId "); |
| | | } |
| | | getDateWhereSql(frequency,sql,"create_time"); |
| | | sql.append(" group by mhour "); |
| | | sql.append(" group by to_number(substr(happen_time||'',9,2)) "); |
| | | |
| | | List<Map> list = baseDao.queryForList(sql.toString(),param); |
| | | Map categoriesMap = new HashMap(); |
| | |
| | | "( SELECT sum(SCORE) score,count(b.id) num,RESOLVE_USER_ID FROM order_satis_info a," + |
| | | "workflow_base b,sc_workflow_incident c WHERE a.BUSINESS_ID = b.id "; |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){ |
| | | sql+=" and date_format(c.create_time,'%Y%m%d')>=:beginDay "; |
| | | sql+=" and to_number(substr(c.create_time||'',1,8))>=:beginDay "; |
| | | } |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){ |
| | | sql+=" and date_format(c.create_time,'%Y%m%d')<=:endDay "; |
| | | sql+=" and to_number(substr(c.create_time||'',1,8))<=:endDay "; |
| | | } |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){ |
| | | sql+=" and c.CUSTOMER_ID=:cusId "; |
| | |
| | | "c.CURRENT_DEALER_ID from sc_workflow_incident a,workflow_base b,workflow_node c" + |
| | | " where a.id=b.BUSINESS_ID and b.id=c.FLOWID "; |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){ |
| | | sql+=" and date_format(a.create_time,'%Y%m%d')>=:beginDay "; |
| | | sql+=" and to_number(substr(a.create_time||'',1,8))>=:beginDay "; |
| | | } |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){ |
| | | sql+=" and date_format(a.create_time,'%Y%m%d')<=:endDay "; |
| | | sql+=" and to_number(substr(a.create_time||'',1,8))<=:endDay "; |
| | | } |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){ |
| | | sql+=" and a.CUSTOMER_ID=:cusId "; |
| | |
| | | " LEFT JOIN " ; |
| | | sql+=" (SELECT count(*) num,RESOLVE_USER_ID from sc_workflow_incident WHERE 1=1 "; |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){ |
| | | sql+=" and date_format(create_time,'%Y%m%d')>=:beginDay "; |
| | | |
| | | sql+=" and to_number(substr(create_time||'',1,8))>=:beginDay "; |
| | | } |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){ |
| | | sql+=" and date_format(create_time,'%Y%m%d')<=:endDay "; |
| | | sql+=" and to_number(substr(create_time||'',1,8))<=:endDay "; |
| | | } |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){ |
| | | sql+=" and CUSTOMER_ID=:cusId "; |
| | |
| | | sql="SELECT RESOLVE_USER_ID,SLA_ID,COUNT(a.id) num from sc_workflow_incident a," + |
| | | "workflow_base b where a.id=b.BUSINESS_ID and b.WFSTATE in(2,4) "; |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){ |
| | | sql+=" and date_format(a.create_time,'%Y%m%d')>=:beginDay "; |
| | | sql+=" and to_number(substr(a.create_time||'',1,8))>=:beginDay "; |
| | | } |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){ |
| | | sql+=" and date_format(a.create_time,'%Y%m%d')<=:endDay "; |
| | | sql+=" and to_number(substr(a.create_time||'',1,8))<=:endDay "; |
| | | } |
| | | if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){ |
| | | sql+=" and a.CUSTOMER_ID=:cusId "; |
| | | } |
| | | sql+=" GROUP BY RESOLVE_USER_ID,SLA_ID order by SLA_NAME"; |
| | | sql+=" GROUP BY RESOLVE_USER_ID,SLA_ID,SLA_NAME order by SLA_NAME"; |
| | | List<Map> lvList=baseDao.queryForList(sql, param); |
| | | if(lvList.size()>0){ |
| | | for(Map mapa:userList){ |