| | |
| | | sql.append(" and g.`zsxm` like :engineer "); |
| | | params.put("engineer", "%"+engineer+"%"); |
| | | } |
| | | sql.append(" group by g.id " ); |
| | | sql.append(" group by g.id, g.ZSXM " ); |
| | | sql.append(" ) t left join ( "); |
| | | sql.append(" select t.current_dealer_id,count(*) i_num,sum(t.w_hour) i_hour,format(avg(s.score),2) i_score from ( "); |
| | | sql.append(" select t.current_dealer_id,count(*) i_num,sum(t.w_hour) i_hour,round(avg(s.score),2) i_score from ( "); |
| | | sql.append(" select n.current_dealer_id,n.flowid,sum(n.DEAL_USETIME) w_hour from workflow_node n,sc_workflow_incident d,workflow_base b "); |
| | | sql.append(" where d.id = b.business_id and b.id = n.flowid and b.WFSTATE<>3 "); |
| | | initSurverQuery(sql,params); |
| | |
| | | sql.append(" GROUP BY n.current_dealer_id,n.flowid) t LEFT JOIN order_satis_info s ON t.flowid = s.business_id GROUP BY t.current_dealer_id ) ia ON t.current_dealer_id = ia.current_dealer_id"); |
| | | |
| | | sql.append(" left join ( "); |
| | | sql.append(" select t.current_dealer_id,count(*) l_num,sum(t.w_hour) l_hour,format(avg(s.score),2) l_score from ( "); |
| | | sql.append(" select t.current_dealer_id,count(*) l_num,sum(t.w_hour) l_hour,round(avg(s.score),2) l_score from ( "); |
| | | sql.append(" select n.current_dealer_id,n.flowid,sum(n.DEAL_USETIME) w_hour from workflow_node n,sc_workflow_incident_local d,workflow_base b "); |
| | | sql.append(" where d.id = b.business_id and b.id = n.flowid and b.WFSTATE<>3 "); |
| | | initSurverQuery(sql,params); |
| | |
| | | params.put("end_date", params.get("end_date").substring(0, 6)+"31"); |
| | | sqla+=" and E.SUBMIT_TIME <=:end_date "; |
| | | } |
| | | sqla+=" GROUP BY ZC_ID ORDER BY E.CARD_MONTH DESC, E.SUBMIT_TIME DESC)m"; |
| | | sqla+=" GROUP BY D.user_id,E.CARD_MONTH,E.STATE, E.SUBMIT_TIME, E.CREATE_TIME, \n" + |
| | | " D.ALLUSER,D.SUB_CUSTOMER_ID, D.SUB_CUSTOMER_NAME,D.CUSTOMER_ID,D.CUSTOMER_NAME" + |
| | | " ORDER BY E.CARD_MONTH DESC, E.SUBMIT_TIME DESC)m"; |
| | | List<Map> listF=baseDao.queryForList(sqla, params); |
| | | if(resList.size()>0){ |
| | | if(listF.size()>0){ |
| | |
| | | sql.append(" select '事件工单'as type_name,'1' as orderType, t.t_num, ifnull(f.f_num, 0) f_num, ifnull(c.c_num, 0) c_num, ifnull(a.a_num, 0) a_num, ifnull(d.d_num, 0) d_num,ifnull(h.w_hour, 0) w_hour from ( "); |
| | | sql.append(" select b.businesstype, count( *) t_num from workflow_base b, sc_workflow_incident d where b.business_id=d.id "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )t left join ( "); |
| | | sql.append(" group by b.businesstype )t left join ( "); |
| | | sql.append(" select businesstype, count(*) f_num from workflow_base b,sc_workflow_incident d where b.business_id=d.id and b.wfstate in(2,4) "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) c_num from workflow_base b,sc_workflow_incident d where b.business_id=d.id and b.wfstate = 3 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) a_num from workflow_base b,sc_workflow_incident d where b.business_id=d.id and b.wfstate = 1 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) d_num from workflow_base b,sc_workflow_incident d where b.business_id=d.id and b.wfstate = 1 and d.ANSWER_TIME is null "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select b.businesstype, sum(n.deal_usetime) w_hour from workflow_base b, workflow_node n,sc_workflow_incident d where b.id = n.flowid and b.business_id = d.id and b.wfstate in(2,4) "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" ) h on t.businesstype=h.businesstype "); |
| | | sql.append(" group by b.businesstype ) h on t.businesstype=h.businesstype "); |
| | | resList.addAll(baseDao.queryForList(sql.toString(), params)); |
| | | //查询驻场事件 |
| | | sql.setLength(0); |
| | | sql.append(" select '驻场工单'as type_name,'2' as orderType, t.t_num, ifnull(f.f_num, 0) f_num, ifnull(c.c_num, 0) c_num, ifnull(a.a_num, 0) a_num, ifnull(d.d_num, 0) d_num,ifnull(h.w_hour,0) w_hour from ( "); |
| | | sql.append(" select b.businesstype, count( *) t_num from workflow_base b, sc_workflow_incident_local d where b.business_id=d.id "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )t left join ( "); |
| | | sql.append(" group by b.businesstype )t left join ( "); |
| | | sql.append(" select businesstype, count(*) f_num from workflow_base b,sc_workflow_incident_local d where b.business_id=d.id and b.wfstate in(2,4) "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) c_num from workflow_base b,sc_workflow_incident_local d where b.business_id=d.id and b.wfstate = 3 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) a_num from workflow_base b,sc_workflow_incident_local d where b.business_id=d.id and b.wfstate = 1 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) d_num from workflow_base b,sc_workflow_incident_local d where b.business_id=d.id and b.wfstate = 1 and d.ANSWER_TIME is null "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select b.businesstype, sum(n.deal_usetime) w_hour from workflow_base b, workflow_node n,sc_workflow_incident_local d where b.id = n.flowid and b.business_id = d.id and b.wfstate in(2,4) "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" ) h on t.businesstype=h.businesstype "); |
| | | sql.append(" group by b.businesstype ) h on t.businesstype=h.businesstype "); |
| | | resList.addAll(baseDao.queryForList(sql.toString(), params)); |
| | | //问题 |
| | | sql.setLength(0); |
| | | sql.append(" select '问题工单'as type_name,'3' as orderType, t.t_num, ifnull(f.f_num, 0) f_num, ifnull(c.c_num, 0) c_num, ifnull(a.a_num, 0) a_num, ifnull(d.d_num, 0) d_num,ifnull(h.w_hour,0) w_hour from ( "); |
| | | sql.append(" select b.businesstype, count( *) t_num from workflow_base b, sc_workflow_question d where b.business_id=d.id "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )t left join ( "); |
| | | sql.append(" group by b.businesstype )t left join ( "); |
| | | sql.append(" select businesstype, count(*) f_num from workflow_base b,sc_workflow_question d where b.business_id=d.id and b.wfstate = 2 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) c_num from workflow_base b,sc_workflow_question d where b.business_id=d.id and b.wfstate = 3 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) a_num from workflow_base b,sc_workflow_question d where b.business_id=d.id and b.wfstate = 1 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) d_num from workflow_base b, sc_workflow_question d where b.business_id=d.id and b.wfstate = 3 and d.SHOULI_TIME is null "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select b.businesstype, sum(n.deal_usetime) w_hour from workflow_base b, workflow_node n,sc_workflow_question d where b.id = n.flowid and b.business_id = d.id and b.wfstate in(2,4) "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" ) h on t.businesstype=h.businesstype "); |
| | | sql.append(" group by b.businesstype ) h on t.businesstype=h.businesstype "); |
| | | resList.addAll(baseDao.queryForList(sql.toString(), params)); |
| | | //变更 |
| | | sql.setLength(0); |
| | | sql.append(" select '变更工单'as type_name,'4' as orderType, t.t_num, ifnull(f.f_num, 0) f_num, ifnull(c.c_num, 0) c_num, ifnull(a.a_num, 0) a_num, (0) d_num,ifnull(h.w_hour,0) w_hour from ( "); |
| | | sql.append(" select b.businesstype, count( *) t_num from workflow_base b, sc_workflow_change d where b.business_id=d.id "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )t left join ( "); |
| | | sql.append(" group by b.businesstype )t left join ( "); |
| | | sql.append(" select businesstype, count(*) f_num from workflow_base b,sc_workflow_change d where b.business_id=d.id and b.wfstate = 2 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) c_num from workflow_base b,sc_workflow_change d where b.business_id=d.id and b.wfstate = 3 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) a_num from workflow_base b,sc_workflow_change d where b.business_id=d.id and b.wfstate = 1 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) d_num from workflow_base b, workflow_node n,sc_workflow_change d where b.current_node_id = n.id and b.business_id=d.id and n.flowstate <> 1 and b.wfstate=1 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select b.businesstype, sum(n.deal_usetime) w_hour from workflow_base b, workflow_node n,sc_workflow_change d where b.id = n.flowid and b.business_id = d.id and b.wfstate in(2,4) "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" ) h on t.businesstype=h.businesstype "); |
| | | sql.append(" group by b.businesstype ) h on t.businesstype=h.businesstype "); |
| | | resList.addAll(baseDao.queryForList(sql.toString(), params)); |
| | | //发布 |
| | | sql.setLength(0); |
| | | sql.append(" select '发布工单'as type_name,'5' as orderType, t.t_num, ifnull(f.f_num, 0) f_num, ifnull(c.c_num, 0) c_num, ifnull(a.a_num, 0) a_num, (0) d_num,ifnull(h.w_hour,0) w_hour from ( "); |
| | | sql.append(" select b.businesstype, count( *) t_num from workflow_base b, sc_workflow_release d where b.business_id=d.id "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )t left join ( "); |
| | | sql.append(" group by b.businesstype )t left join ( "); |
| | | sql.append(" select businesstype, count(*) f_num from workflow_base b,sc_workflow_release d where b.business_id=d.id and b.wfstate = 2 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )f on t.businesstype = f.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) c_num from workflow_base b,sc_workflow_release d where b.business_id=d.id and b.wfstate = 3 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )c on c.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) a_num from workflow_base b,sc_workflow_release d where b.business_id=d.id and b.wfstate = 1 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )a on a.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select businesstype, count(*) d_num from workflow_base b, workflow_node n,sc_workflow_release d where b.current_node_id = n.id and b.business_id=d.id and n.flowstate <> 1 and b.wfstate=1 "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" group by b.businesstype )d on d.businesstype = t.businesstype left join ( "); |
| | | sql.append(" select b.businesstype, sum(n.deal_usetime) w_hour from workflow_base b, workflow_node n,sc_workflow_release d where b.id = n.flowid and b.business_id = d.id and b.wfstate in(2,4) "); |
| | | initSurverQuery(sql,params); |
| | | sql.append(" ) h on t.businesstype=h.businesstype "); |
| | | sql.append(" group by b.businesstype ) h on t.businesstype=h.businesstype "); |
| | | resList.addAll(baseDao.queryForList(sql.toString(), params)); |
| | | |
| | | //合计 |