工单统计

dhz

2022-06-22 935b4b66b99b1f022f82cdaef8e3ef6599afbc72

 src/cn/ksource/web/facade/tj/QuestionStatisFacadeImpl.java

@@ -242,9 +242,11 @@
      Map paramMap = new HashMap();
      StringBuilder builder = new StringBuilder("");
      if(frequency.equals("2")) {
         builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m-%d') AS DC_TIME  FROM SC_WORKFLOW_QUESTION C WHERE 1=1");
         builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,to_char(C.CREATE_TIME,\n" +
               "        'yyyy-MM-dd')  AS DC_TIME  FROM SC_WORKFLOW_QUESTION C WHERE 1=1");
      } else {
         builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m') AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1 ");
         builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,to_char(C.CREATE_TIME,\n" +
               "        'yyyy-MM')  AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1 ");
      }
      if(StringUtil.isBlank(queryType)){
         builder.append(" AND state!=7 ");
@@ -267,7 +269,12 @@
         }
      }
      builder.append(" and $KEY is not null ");
      builder.append(" GROUP BY $KEY,DC_TIME ");
      if(frequency.equals("2")){
         builder.append(" GROUP BY $KEY,to_char(C.CREATE_TIME,'yyyy-MM-dd') ");
      }else {

         builder.append(" GROUP BY $KEY,to_char(C.CREATE_TIME,'yyyy-MM') ");
      }
      String sql = builder.toString().replace("$KEY", ConvertUtil.obj2StrBlank(queryMap.get("col")));
      return baseDao.queryForList(sql,paramMap);
   }
@@ -508,8 +515,8 @@
         paramMap.put("subCustomerId", subCustomerId);
      }
      
      String endSql = " GROUP BY THIRD_CATEGORY_ID ";
		
      String endSql = " GROUP BY THIRD_CATEGORY_ID ,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME ";

      builder1.append(endSql);
      
      
@@ -598,12 +605,15 @@
      List<List<String>> dataList = new ArrayList<List<String>>();  
      //查询数据
      StringBuilder builder = new StringBuilder();
      builder.append(" SELECT q.NAME,q.DESCRIP,q.CONTACT_NAME,q.CONTACT_PHONE,q.SOURCE_NAME,q.THIRD_CATEGORY_NAME,q.SECOND_CATEGORY_NAME,q.FIRST_CATEGORY_NAME,DATE_FORMAT(q.WANT_DEAL_TIME,'%Y-%m-%d') WANT_DEAL_TIME, ");
      builder.append(" q.PRIORITY_NAME,q.INFLUENCE_NAME,q.STATE,q.RESOLVE_TYPE_NAME,q.RESOLVE,t.CREATE_USER_NAME,DATE_FORMAT(t.CREATE_TIME,'%Y-%m-%d') CREATE_TIME,IFNULL(t.deal_time,0) deal_time  ");
      builder.append(" SELECT q.NAME,q.DESCRIP,q.CONTACT_NAME,q.CONTACT_PHONE,q.SOURCE_NAME,q.THIRD_CATEGORY_NAME,q.SECOND_CATEGORY_NAME,q.FIRST_CATEGORY_NAME,q.WANT_DEAL_TIME WANT_DEAL_TIME, ");
      builder.append(" q.PRIORITY_NAME,q.INFLUENCE_NAME,q.STATE,q.RESOLVE_TYPE_NAME,q.RESOLVE,t.CREATE_USER_NAME,t.CREATE_TIME CREATE_TIME,IFNULL(t.deal_time,0) deal_time  ");
      builder.append(" from SC_WORKFLOW_QUESTION q  ");
      builder.append(" LEFT JOIN ( ");
      builder.append("    select z.CREATE_USER_NAME,z.CREATE_TIME,b.ID,truncate(TIMESTAMPDIFF(SECOND, b.CREATETIME, b.ENDTIME)*1000,0) deal_time from QUESTION_ZD_HISTORY z,workflow_base b where b.ID = z.FLOWID ");
      builder.append("    and z.ISTHEEND =1 group by b.ID ");
      builder.append("    select z.CREATE_USER_NAME,z.CREATE_TIME,b.ID,round((\n" +
            "         b.ENDTIME - b.CREATETIME\n" +
            "         )*1000,\n" +
            "        0) deal_time from QUESTION_ZD_HISTORY z,workflow_base b where b.ID = z.FLOWID ");
      builder.append("    and z.ISTHEEND =1 group by b.ID,z.CREATE_USER_NAME,z.CREATE_TIME,round(( b.ENDTIME - b.CREATETIME )*1000,0) ");
      builder.append(" ) t on q.FLOW_ID = t.ID where q.CREATE_TIME >:beginDay and q.CREATE_TIME < :endDay  ");
      if(StringUtil.notEmpty(cusId)){
         builder.append(" and q.CUSTOMER_ID = :cusId ");
@@ -677,7 +687,7 @@
      builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(sleep_num,0) sleep_num,  ");
      builder.append(" IFNULL(avg_deal_time,0) avg_deal_time,truncate(IFNULL(major_num,0)*100/total_num,2) major_rate,truncate(IFNULL(finish_num, 0)*100/total_num,2) solve_rate,truncate(IFNULL(close_num, 0)*100/total_num,2) close_rate,  ");
      builder.append(" truncate(IFNULL(repeat_num, 0)*100/total_num,2) repeat_rate,truncate(IFNULL(nosolve_num, 0)*100/total_num,2) nosolve_rate   from  ");
      builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(i.id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mtotal   ");
      builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(i.id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID,i.THIRD_CATEGORY_NAME,i.SECOND_CATEGORY_NAME,i.FIRST_CATEGORY_NAME) mtotal   ");
      builder.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b,(SELECT FLOWID FROM workflow_node WHERE FLOWSTATE = 1 GROUP BY FLOWID) n where b.BUSINESS_ID = i.ID AND b.id = n.FLOWID  and i.CUSTOMER_ID=:cusId  and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) manswer  ");
      builder.append(" on  mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
      builder.append(" left join (select count(i.id) activi_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and b.WFSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mactivi  ");
@@ -891,7 +901,7 @@
      builder.append(" IFNULL(finish_num,0) finish_num, IFNULL(close_num,0) close_num from (  ");
      builder.append("    select count(b.id) total_num,i.THIRD_CATEGORY_NAME,i.FIRST_CATEGORY_NAME,i.SECOND_CATEGORY_NAME,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i     ");
      builder.append("    where b.BUSINESS_ID = i.ID  and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
      builder.append("    GROUP BY I.THIRD_CATEGORY_ID  ");
      builder.append("    GROUP BY I.THIRD_CATEGORY_ID ,i.THIRD_CATEGORY_NAME,i.FIRST_CATEGORY_NAME,i.SECOND_CATEGORY_NAME ");
      builder.append(" ) mtotal left join (  ");
      builder.append("    select count(b.id) answer_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i,(  ");
      builder.append("         select FLOWID from workflow_node where FLOWSTATE = 1 group by FLOWID ");