package cn.ksource.web.facade.ywStatis;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.page.PageInfo;
|
import cn.ksource.core.util.ConvertUtil;
|
import cn.ksource.core.util.NumberUtil;
|
import cn.ksource.core.util.StringUtil;
|
|
@Service
|
public class YwStatisFacadeImpl implements YwStatisFacade{
|
@Autowired
|
private BaseDao baseDao;
|
@SuppressWarnings({ "rawtypes", "unchecked" })
|
@Override
|
public List<Map> getEngineerOperation(Map<String, String> params) {
|
//开始时间
|
if(StringUtil.isNotBlank(params.get("start_date"))){
|
params.put("start_date", params.get("start_date") + "000000");
|
}
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("end_date"))){
|
params.put("end_date", params.get("end_date") + "240000");
|
}
|
StringBuilder sql = new StringBuilder();
|
List<Map> resList = new ArrayList<Map>();
|
sql.append(" select t.current_dealer_name,t.current_dealer_id,ifnull(i.i_num,0) i_num,ifnull(ia.i_num,0) ia_num,ifnull(la.i_num,0) la_num,ifnull(i.i_hour,0) i_hour,ifnull(i.i_score,'') i_score,ifnull(l.l_num,0) l_num,ifnull(l.l_hour,0) l_hour,ifnull(l.l_score,'') l_score,ifnull(q.q_num,0) q_num,ifnull(q.q_hour,0) q_hour, ");
|
sql.append(" ifnull(c.c_num,0) c_num,ifnull(c.c_hour,0) c_hour,ifnull(r.r_num,0) r_num,ifnull(r.r_hour,0) r_hour from ( ");
|
sql.append(" SELECT g.id current_dealer_id,g.ZSXM current_dealer_name from gg_user g LEFT JOIN (select n.current_dealer_id,n.current_dealer_name from workflow_base b,workflow_node n where b.id = n.flowid and b.WFSTATE <> 3 AND b.businesstype IN (8, 9, 5, 12) and n.current_dealer_id IS NOT NULL AND n.current_dealer_id <> '-1' ) m ");
|
sql.append(" on m.current_dealer_id = g.id WHERE g.zt=1 ");
|
if(StringUtil.isNotBlank(params.get("engineer"))){
|
String engineer = params.get("engineer");
|
sql.append(" and g.`zsxm` like :engineer ");
|
params.put("engineer", "%"+engineer+"%");
|
}
|
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,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 ");
|
sql.append(" ) i on t.current_dealer_id = i.current_dealer_id ");
|
|
sql.append(" LEFT JOIN (SELECT t.current_dealer_id,count(*) i_num FROM (SELECT n.current_dealer_id,n.flowid FROM workflow_node n,sc_workflow_incident d,workflow_base b WHERE d.id = b.business_id AND b.id = n.flowid AND b.WFSTATE <> 3 and (d.ANSWER_TIMEOUT=1 or d.DEAL_TIMEOUT=1) ");
|
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,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);
|
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 ");
|
sql.append(" ) l on t.current_dealer_id = l.current_dealer_id ");
|
|
|
sql.append(" LEFT JOIN (SELECT t.current_dealer_id,count(*) i_num FROM (SELECT n.current_dealer_id,n.flowid FROM workflow_node n,sc_workflow_incident_local d,workflow_base b WHERE d.id = b.business_id AND b.id = n.flowid AND b.WFSTATE <> 3 and (d.ANSWER_TIMEOUT=1 or d.DEAL_TIMEOUT=1) ");
|
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 ) la ON t.current_dealer_id = la.current_dealer_id");
|
sql.append(" left join ( ");
|
|
sql.append(" select t.current_dealer_id,count(*) q_num,sum(t.w_hour) q_hour from ( ");
|
sql.append(" select n.current_dealer_id,n.flowid,sum(n.DEAL_USETIME) w_hour from workflow_base b,workflow_node n,sc_workflow_question d ");
|
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 group by t.current_dealer_id ");
|
sql.append(" ) q on t.current_dealer_id = q.current_dealer_id left join ( ");
|
sql.append(" select t.current_dealer_id,count(*) c_num,sum(t.w_hour) c_hour from ( ");
|
sql.append(" select n.current_dealer_id,n.flowid,sum(n.DEAL_USETIME) w_hour from workflow_base b,workflow_node n,sc_workflow_change d ");
|
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 group by t.current_dealer_id ");
|
sql.append(" ) c on t.current_dealer_id = c.current_dealer_id left join ( ");
|
sql.append(" select t.current_dealer_id,count(*) r_num,sum(t.w_hour) r_hour from ( ");
|
sql.append(" select n.current_dealer_id,n.flowid,sum(n.DEAL_USETIME) w_hour from workflow_base b,workflow_node n,sc_workflow_release d ");
|
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 group by t.current_dealer_id ");
|
sql.append(" ) r on t.current_dealer_id = r.current_dealer_id ");
|
resList = baseDao.queryForList(sql.toString(), params);
|
String sqla="select m.ZC_ID,TRUNCATE(m.SCORE/m.num,2) pj from (SELECT count(E.ID) num,E.CARD_MONTH,sum(E.SCORE) SCORE," +
|
"E.STATE,E.SUBMIT_TIME,E.CREATE_TIME,D.USER_ID AS ZC_ID,D.ALLUSER,D.SUB_CUSTOMER_ID,D.SUB_CUSTOMER_NAME," +
|
"D.CUSTOMER_ID," +
|
"D.CUSTOMER_NAME FROM ((SELECT A.USER_ID,A.USER_NAME AS ALLUSER,A.CUSTOMER_ID AS SUB_CUSTOMER_ID," +
|
"B.CUSTOMER_NAME AS SUB_CUSTOMER_NAME,B.CUSTOMER_ID,C.CUSTOMER_NAME FROM SC_CUSTOMER_ZC_UESR A," +
|
"SC_PARTNER_CUSTOMER_SUB B,SC_PARTNER_CUSTOMER_INFO C WHERE A.CUSTOMER_ID = B.ID AND B.CUSTOMER_ID = C.ID" +
|
") UNION ALL (SELECT A.USER_ID,A.USER_NAME AS ALLUSER,A.CUSTOMER_ID AS SUB_CUSTOMER_ID," +
|
"C.CUSTOMER_NAME AS SUB_CUSTOMER_NAME,C.ID AS CUSTOMER_ID,C.CUSTOMER_NAME FROM SC_CUSTOMER_ZC_UESR A," +
|
"SC_PARTNER_CUSTOMER_INFO C WHERE A.CUSTOMER_ID = C.ID ) )D,AUDITING_SATIS_INFO E WHERE " +
|
"1 = 1 and E.USER_ID = D.USER_ID AND E.STATE=3 ";
|
//开始时间
|
|
if(StringUtil.isNotBlank(params.get("start_date"))){
|
params.put("start_date", params.get("start_date").substring(0, 6)+"01");
|
sqla+=" and E.SUBMIT_TIME >=:start_date ";
|
}
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("end_date"))){
|
params.put("end_date", params.get("end_date").substring(0, 6)+"31");
|
sqla+=" and E.SUBMIT_TIME <=:end_date ";
|
}
|
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){
|
for(Map mapa:resList){
|
String userId=ConvertUtil.obj2StrBlank(mapa.get("current_dealer_id"));
|
for(Map mapb:listF){
|
String createId=ConvertUtil.obj2StrBlank(mapb.get("ZC_ID"));
|
if(userId.equals(createId)){
|
String lscore=ConvertUtil.obj2StrBlank(mapa.get("l_score"));
|
String pj=ConvertUtil.obj2StrBlank(mapb.get("pj"));
|
if(StringUtil.isEmpty(lscore) && StringUtil.notEmpty(pj)){
|
mapa.put("l_score", pj);
|
}else if(StringUtil.isEmpty(pj) && StringUtil.notEmpty(lscore)){
|
mapa.put("l_score", lscore);
|
}else if(StringUtil.notEmpty(pj) && StringUtil.notEmpty(lscore)){
|
double zfs=NumberUtil.add(ConvertUtil.obj2Double(mapa.get("l_score")), ConvertUtil.obj2Double(mapb.get("pj")));
|
double fs=NumberUtil.div(zfs, 2.0, 2);
|
mapa.put("l_score", fs);
|
}
|
}
|
}
|
}
|
}
|
}
|
for (Map map : resList) {
|
Double i_hour = ConvertUtil.obj2Double(map.get("i_hour"));
|
i_hour=NumberUtil.div(i_hour, 3600.00, 4);
|
Double q_hour = ConvertUtil.obj2Double(map.get("q_hour"));
|
q_hour=NumberUtil.div(q_hour, 3600.00, 4);
|
Double c_hour = ConvertUtil.obj2Double(map.get("c_hour"));
|
c_hour=NumberUtil.div(c_hour, 3600.00, 4);
|
Double r_hour = ConvertUtil.obj2Double(map.get("r_hour"));
|
r_hour=NumberUtil.div(r_hour, 3600.00, 4);
|
Double l_hour = ConvertUtil.obj2Double(map.get("l_hour"));
|
l_hour=NumberUtil.div(l_hour, 3600.00, 4);
|
Double t_hour = i_hour + q_hour + c_hour + r_hour+l_hour;
|
map.put("t_hour", t_hour);
|
map.put("i_hour", i_hour);
|
map.put("q_hour", q_hour);
|
map.put("c_hour", c_hour);
|
map.put("r_hour", r_hour);
|
map.put("l_hour", l_hour);
|
}
|
return resList;
|
}
|
private void initSurverQuery(StringBuilder sql, Map<String, String> params){
|
if(StringUtil.isNotBlank(params.get("cusId"))){
|
sql.append(" and b.CUSTOMER_ID =:cusId ");
|
}
|
if(StringUtil.isNotBlank(params.get("subId"))){
|
sql.append(" and b.SUB_CUSTOMER_ID =:subId ");
|
}
|
//开始时间
|
if(StringUtil.isNotBlank(params.get("start_date"))){
|
sql.append(" and b.createtime >=:start_date ");
|
}
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("end_date"))){
|
sql.append(" and b.createtime <=:end_date ");
|
}
|
}
|
@Override
|
public List<Map> getFieldSurvey(Map<String, String> params) {
|
//开始时间
|
if(StringUtil.isNotBlank(params.get("start_date"))){
|
params.put("start_date", params.get("start_date") + "000000");
|
}
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("end_date"))){
|
params.put("end_date", params.get("end_date") + "240000");
|
}
|
|
StringBuilder sql = new StringBuilder();
|
List<Map> resList = new ArrayList<Map>();
|
//查询事件
|
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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" 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(" group by b.businesstype ) h on t.businesstype=h.businesstype ");
|
resList.addAll(baseDao.queryForList(sql.toString(), params));
|
|
//合计
|
Map<String, Object> totalMap = new HashMap<String, Object>();
|
String type_name = "合 计";
|
Integer t_num = 0;
|
Integer f_num = 0;
|
Integer c_num = 0;
|
Integer a_num = 0;
|
Integer d_num = 0;
|
Double w_hour = 0.00;
|
for (Map map : resList) {
|
t_num = t_num + ConvertUtil.obj2Integer(map.get("t_num"));
|
f_num = f_num + ConvertUtil.obj2Integer(map.get("f_num"));
|
c_num = c_num + ConvertUtil.obj2Integer(map.get("c_num"));
|
a_num = a_num + ConvertUtil.obj2Integer(map.get("a_num"));
|
d_num = d_num + ConvertUtil.obj2Integer(map.get("d_num"));
|
w_hour=w_hour+NumberUtil.div(ConvertUtil.obj2Double(map.get("w_hour")), 3600.00, 2);
|
map.put("w_hour", NumberUtil.div(ConvertUtil.obj2Double(map.get("w_hour")), 3600.00, 2));
|
}
|
totalMap.put("type_name", type_name);
|
totalMap.put("t_num", t_num);
|
totalMap.put("f_num", f_num);
|
totalMap.put("c_num", c_num);
|
totalMap.put("a_num", a_num);
|
totalMap.put("d_num", d_num);
|
totalMap.put("w_hour", w_hour);
|
resList.add(totalMap);
|
return resList;
|
}
|
@Override
|
public PageInfo queryGcsSjData(Map<String, String> params, PageInfo pageInfo) {
|
String sql="select a.NAME,a.ORDER_CODE,b.CREATETIME,b.BUSINESSTYPE,b.BUSINESS_ID,b.id bid from workflow_base b,workflow_node n";
|
String type=params.get("type");
|
if(type.equals("1")){
|
sql+=",SC_WORKFLOW_INCIDENT a ";
|
}else if(type.equals("2")){
|
sql+=",SC_WORKFLOW_QUESTION a ";
|
}else if(type.equals("3")){
|
sql+=",SC_WORKFLOW_CHANGE a ";
|
}else if(type.equals("4")){
|
sql+=",SC_WORKFLOW_RELEASE a ";
|
}else if(type.equals("5")){
|
sql+=",SC_WORKFLOW_INCIDENT_LOCAL a ";
|
}
|
sql+=" where a.id=b.BUSINESS_ID and b.id=n.FLOWID and b.WFSTATE<>3 ";
|
if(StringUtil.isNotBlank(params.get("cusId"))){
|
sql+=" and b.CUSTOMER_ID =:cusId ";
|
}
|
if(StringUtil.isNotBlank(params.get("subId"))){
|
sql+=" and b.SUB_CUSTOMER_ID =:subId ";
|
}
|
if(params.get("flag").equals("flag")){
|
sql+=" AND (a.ANSWER_TIMEOUT = 1 OR a.DEAL_TIMEOUT = 1) ";
|
}
|
//开始时间
|
if(StringUtil.isNotBlank(params.get("beginTime"))){
|
params.put("start_date", params.get("beginTime")+"000000");
|
sql+=" and b.createtime >=:start_date ";
|
}
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("endTime"))){
|
params.put("end_date", params.get("endTime")+"235959");
|
sql+=" and b.createtime <=:end_date ";
|
}
|
if(StringUtil.isNotBlank(params.get("gid"))){
|
sql+=" and n.CURRENT_DEALER_ID=:gid ";
|
}
|
sql+=" group by b.id";
|
return baseDao.queryforSplitPageInfo(pageInfo, sql, params);
|
}
|
@Override
|
public int queryGcsSjCount(Map<String, String> params) {
|
String sql="select count(c.id) from (select b.id from workflow_base b,workflow_node n";
|
String type=params.get("type");
|
if(type.equals("1")){
|
sql+=",SC_WORKFLOW_INCIDENT a ";
|
}else if(type.equals("2")){
|
sql+=",SC_WORKFLOW_QUESTION a ";
|
}else if(type.equals("3")){
|
sql+=",SC_WORKFLOW_CHANGE a ";
|
}else if(type.equals("4")){
|
sql+=",SC_WORKFLOW_RELEASE a ";
|
}else if(type.equals("5")){
|
sql+=",SC_WORKFLOW_INCIDENT_LOCAL a ";
|
}
|
sql+=" where a.id=b.BUSINESS_ID and b.id=n.FLOWID and b.WFSTATE<>3 ";
|
if(StringUtil.isNotBlank(params.get("cusId"))){
|
sql+=" and b.CUSTOMER_ID =:cusId ";
|
}
|
if(StringUtil.isNotBlank(params.get("subId"))){
|
sql+=" and b.SUB_CUSTOMER_ID =:subId ";
|
}
|
if(params.get("flag").equals("flag")){
|
sql+=" AND (a.ANSWER_TIMEOUT = 1 OR a.DEAL_TIMEOUT = 1) ";
|
}
|
//开始时间
|
if(StringUtil.isNotBlank(params.get("beginTime"))){
|
params.put("start_date", params.get("beginTime")+"000000");
|
sql+=" and b.createtime >=:start_date ";
|
}
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("endTime"))){
|
params.put("end_date", params.get("endTime")+"235959");
|
sql+=" and b.createtime <=:end_date ";
|
}
|
if(StringUtil.isNotBlank(params.get("gid"))){
|
sql+=" and n.CURRENT_DEALER_ID=:gid ";
|
}
|
sql+=" group by b.id) c";
|
return baseDao.queryForInteger(sql, params);
|
}
|
@Override
|
public PageInfo queryYwSjData(Map<String, String> params, PageInfo pageInfo) {
|
String sql="select a.NAME,a.ORDER_CODE,b.CREATETIME,b.BUSINESSTYPE,b.BUSINESS_ID,b.id bid from workflow_base b";
|
String orderType=params.get("orderType");
|
if(orderType.equals("1")){
|
sql+=",SC_WORKFLOW_INCIDENT a ";
|
}else if(orderType.equals("2")){
|
sql+=",SC_WORKFLOW_INCIDENT_LOCAL a ";
|
}else if(orderType.equals("3")){
|
sql+=",SC_WORKFLOW_QUESTION a ";
|
}else if(orderType.equals("4")){
|
sql+=",SC_WORKFLOW_CHANGE a ";
|
}else if(orderType.equals("5")){
|
sql+=",SC_WORKFLOW_RELEASE a ";
|
}
|
sql+=" where a.id=b.BUSINESS_ID ";
|
if(StringUtil.isNotBlank(params.get("cusId"))){
|
sql+=" and b.CUSTOMER_ID =:cusId ";
|
}
|
if(StringUtil.isNotBlank(params.get("subId"))){
|
sql+=" and b.SUB_CUSTOMER_ID =:subId ";
|
}
|
//开始时间
|
if(StringUtil.isNotBlank(params.get("beginTime"))){
|
params.put("start_date", params.get("beginTime")+"000000");
|
sql+=" and b.createtime >=:start_date ";
|
}
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("endTime"))){
|
params.put("end_date", params.get("endTime")+"235959");
|
sql+=" and b.createtime <=:end_date ";
|
}
|
if(StringUtil.isNotBlank(params.get("type"))){
|
String type=params.get("type");
|
if(type.equals("2")){
|
sql+=" and (b.WFSTATE=2 or b.WFSTATE=4) ";
|
}else if(type.equals("3")){
|
sql+=" and b.WFSTATE=3 ";
|
}
|
}
|
sql+=" group by b.id";
|
return baseDao.queryforSplitPageInfo(pageInfo, sql, params);
|
}
|
@Override
|
public int queryYwSjCount(Map<String, String> params) {
|
String sql="select count(c.id) from (select b.id from workflow_base b";
|
String orderType=params.get("orderType");
|
if(orderType.equals("1")){
|
sql+=",SC_WORKFLOW_INCIDENT a ";
|
}else if(orderType.equals("2")){
|
sql+=",SC_WORKFLOW_INCIDENT_LOCAL a ";
|
}else if(orderType.equals("3")){
|
sql+=",SC_WORKFLOW_QUESTION a ";
|
}else if(orderType.equals("4")){
|
sql+=",SC_WORKFLOW_CHANGE a ";
|
}else if(orderType.equals("5")){
|
sql+=",SC_WORKFLOW_RELEASE a ";
|
}
|
sql+=" where a.id=b.BUSINESS_ID ";
|
if(StringUtil.isNotBlank(params.get("cusId"))){
|
sql+=" and b.CUSTOMER_ID =:cusId ";
|
}
|
if(StringUtil.isNotBlank(params.get("subId"))){
|
sql+=" and b.SUB_CUSTOMER_ID =:subId ";
|
}
|
//开始时间
|
if(StringUtil.isNotBlank(params.get("beginTime"))){
|
params.put("start_date", params.get("beginTime")+"000000");
|
sql+=" and b.createtime >=:start_date ";
|
}
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("endTime"))){
|
params.put("end_date", params.get("endTime")+"235959");
|
sql+=" and b.createtime <=:end_date ";
|
}
|
if(StringUtil.isNotBlank(params.get("type"))){
|
String type=params.get("type");
|
if(type.equals("2")){
|
sql+=" and (b.WFSTATE=2 or b.WFSTATE=4) ";
|
}else if(type.equals("3")){
|
sql+=" and b.WFSTATE=3 ";
|
}
|
}
|
sql+=" group by b.id) c";
|
return baseDao.queryForInteger(sql, params);
|
}
|
|
}
|
