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 getEngineerOperation(Map 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 resList = new ArrayList(); 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 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 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 getFieldSurvey(Map 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 resList = new ArrayList(); //查询事件 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 totalMap = new HashMap(); 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 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 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 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 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); } }