package cn.ksource.web.facade.uc.order;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.servlet.http.HttpServletRequest;
|
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.dao.SqlParameter;
|
import cn.ksource.core.page.PageInfo;
|
import cn.ksource.core.util.ConvertUtil;
|
import cn.ksource.core.util.DateUtil;
|
import cn.ksource.core.util.StringUtil;
|
import cn.ksource.web.Constants;
|
import cn.ksource.web.service.DataDictionaryService;
|
import cn.ksource.web.service.file.FileService;
|
import cn.ksource.web.service.order.OrderService;
|
|
@Service("ucQuestionFacade")
|
public class UcQuestionFacadeImpl implements UcQuestionFacade {
|
|
@Autowired
|
private BaseDao baseDao;
|
|
@Autowired
|
private FileService fileService;
|
|
@Autowired
|
private OrderService orderService;
|
|
@Autowired
|
private DataDictionaryService dataDictionaryService;
|
|
@Override
|
public int queryQuestionOrderCount(Map<String,String> params) {
|
String cusId = params.get("cusId");
|
StringBuilder sql = new StringBuilder();
|
StringBuilder sqlpart = new StringBuilder();
|
sql.append("SELECT COUNT(WB.ID) FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB LEFT JOIN WORKFLOW_NODE C ON C.FLOWID = WB.ID AND WB.CURRENT_NODE_ID = C.ID AND C.FLOWSTATE <> 3 ");
|
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND WB.BUSINESSTYPE = :businessType ");
|
|
sqlpart.append(" AND B.CUSTOMER_ID = :cusId ");
|
|
params.put("cusId", cusId);
|
|
sqlpart = getQuestionSql(sqlpart,params);
|
|
sql.append(sqlpart);
|
params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
return baseDao.queryForInteger(sql.toString(),params);
|
}
|
|
@Override
|
public PageInfo queryQuestionOrderList(PageInfo pageInfo,Map<String,String> params) {
|
String cusId = params.get("cusId");
|
StringBuilder sql = new StringBuilder();
|
StringBuilder sqlpart = new StringBuilder();
|
sql.append("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE,C.FLOWSTATE , ");
|
sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.SUB_CUSTOMER_NAME,WB.PROJECT_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,B.STATE ,B.NAME ");
|
sql.append("FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB LEFT JOIN WORKFLOW_NODE C ON C.FLOWID = WB.ID AND WB.CURRENT_NODE_ID = C.ID AND C.FLOWSTATE <> 3 ");
|
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND WB.BUSINESSTYPE = :businessType ");
|
params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
sqlpart.append(" AND B.CUSTOMER_ID = :cusId ");
|
|
params.put("cusId", cusId);
|
sqlpart = getQuestionSql(sqlpart,params);
|
|
sql.append(sqlpart);
|
sql.append( " ORDER BY WB.CREATETIME DESC ");
|
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
public StringBuilder getQuestionSql(StringBuilder sqlpart,Map<String,String> params){
|
String orderName = params.get("orderName");
|
if(StringUtil.notEmpty(orderName)) {
|
sqlpart.append(" AND B.NAME LIKE :orderName ");
|
params.put("orderName", "%"+orderName+"%");
|
}
|
|
String orderCode = params.get("orderCode");
|
if(StringUtil.notEmpty(orderCode)) {
|
sqlpart.append(" AND B.ORDER_CODE LIKE :orderCode ");
|
params.put("orderCode", "%"+orderCode+"%");
|
}
|
|
|
String contract = params.get("contract");
|
if(StringUtil.notEmpty(contract)) {
|
sqlpart.append(" AND B.CONTACT_NAME LIKE :contract ");
|
params.put("contract", "%"+contract+"%");
|
}
|
|
String states = params.get("state");
|
if(StringUtil.notEmpty(states)) {
|
sqlpart.append(" AND WB.WFSTATE = :state ");
|
}
|
|
String pri = params.get("pri");
|
if(StringUtil.notEmpty(pri)) {
|
String[] pris = pri.split(",");
|
if(pris.length==1) {
|
sqlpart.append(" AND B.PRIORITY_ID = :priority_id");
|
params.put("priority_id", pris[0]);
|
} else {
|
sqlpart.append(" AND B.PRIORITY_ID IN (");
|
for(int i=0; i<pris.length; i++) {
|
sqlpart.append(" :pri"+i);
|
sqlpart.append(",");
|
params.put("pri"+i, pris[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
|
String dg = params.get("dg");
|
if(StringUtil.notEmpty(dg)) {
|
String[] dgs = dg.split(",");
|
if(dgs.length==1) {
|
sqlpart.append(" AND B.INFLUENCE_ID = :influence_id");
|
params.put("influence_id", dgs[0]);
|
} else {
|
sqlpart.append(" AND B.INFLUENCE_ID IN (");
|
for(int i=0; i<dgs.length; i++) {
|
sqlpart.append(" :dg"+i);
|
sqlpart.append(",");
|
params.put("dg"+i, dgs[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
String source = params.get("source");
|
if(StringUtil.notEmpty(source)) {
|
String[] sources = source.split(",");
|
if(sources.length==1) {
|
sqlpart.append(" AND B.SOURCE_ID = :type_id");
|
params.put("type_id", sources[0]);
|
} else {
|
sqlpart.append(" AND B.SOURCE_ID IN (");
|
for(int i=0; i<sources.length; i++) {
|
sqlpart.append(" :type"+i);
|
sqlpart.append(",");
|
params.put("type"+i, sources[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
String sl = params.get("sl");
|
if(StringUtil.notEmpty(sl)) {
|
String[] sls = sl.split(",");
|
if(sls.length==1) {
|
sqlpart.append(" AND B.THIRD_CATEGORY_ID = :third_category_id");
|
params.put("third_category_id", sls[0]);
|
} else {
|
sqlpart.append(" AND B.THIRD_CATEGORY_ID IN (");
|
for(int i=0; i<sls.length; i++) {
|
sqlpart.append(" :third_category_id"+i);
|
sqlpart.append(",");
|
params.put("third_category_id"+i, sls[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
String status = params.get("status");
|
if(StringUtil.notEmpty(status)) {
|
String[] state = status.split(",");
|
if("ywc".equals(state[0])){
|
sqlpart.append( " AND (B.STATE = :state OR B.STATE = :state1 )");
|
params.put("state", Constants.SC_WORKFLOW_QUESTION_STATE_YWC);
|
params.put("state1", Constants.SC_WORKFLOW_QUESTION_STATE_YPJ);
|
}else if("jxz".equals(state[0])){
|
sqlpart.append( " AND (B.STATE != :state AND B.STATE != :state1 AND B.STATE != :state2 ) ");
|
params.put("state", Constants.SC_WORKFLOW_QUESTION_STATE_YWC);
|
params.put("state1", Constants.SC_WORKFLOW_QUESTION_STATE_YPJ);
|
params.put("state2", Constants.SC_WORKFLOW_QUESTION_STATE_YGB);
|
}else if(!"all".equals(state[0])){
|
sqlpart.append( " AND B.STATE = :state ");
|
params.put("state", state[0]);
|
if(state.length == 2){
|
sqlpart.append( " AND C.FLOWSTATE = :flowstate ");
|
params.put("flowstate", state[1]);
|
}
|
}
|
}
|
return sqlpart;
|
}
|
|
@Override
|
public Map queryQuestionCount(String cusId) {
|
//初始化变量
|
|
//问题受理响应中
|
int wtsldxy = 0;
|
//问题受理处理中
|
int wtslclz = 0;
|
|
//问题审核待响应
|
int wtshdxy = 0;
|
//问题审核处理中
|
int wtshclz = 0;
|
|
//问题诊断待响应
|
int wtzddxy = 0;
|
//问题诊断处理中
|
int wtzdclz = 0;
|
|
|
//方案审批待响应
|
int faspdxy = 0;
|
//方案审批处理中
|
int faspclz = 0;
|
|
//方案实施待响应
|
int fassdxy = 0;
|
//方案实施处理中
|
int fassclz = 0;
|
|
//问题回顾待响应
|
int wthgdxy = 0;
|
//问题回顾处理中
|
int wthgclz = 0;
|
|
//已完成
|
int ywc = 0;
|
//进行中
|
int jxz = 0;
|
|
String selectSql = new String();
|
String sql = new String();
|
|
|
Map paramMap = new HashMap();
|
selectSql = "SELECT COUNT(*) AS NUM,A.STATE,C.FLOWSTATE FROM SC_WORKFLOW_QUESTION A,WORKFLOW_BASE B LEFT JOIN WORKFLOW_NODE C ON B.ID = C.FLOWID AND B.CURRENT_NODE_ID = C.ID AND C.FLOWSTATE <> :flowstate WHERE A.CUSTOMER_ID = :cusId AND A.ID = B.BUSINESS_ID GROUP BY A.STATE,C.FLOWSTATE ";
|
sql = "SELECT COUNT(ID) FROM SC_WORKFLOW_QUESTION WHERE STATE != :yjj AND STATE !=:gb AND STATE != :ypj AND CUSTOMER_ID = :cusId";
|
paramMap.put("cusId", cusId);
|
paramMap.put("flowstate", 3);
|
|
|
List<Map> list = baseDao.queryForList(selectSql,paramMap);
|
if(null!=list && list.size()>0) {
|
for(Map map : list) {
|
String state = ConvertUtil.obj2StrBlank(map.get("STATE"));
|
int flowstate = 0;
|
if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(map.get("FLOWSTATE")))){
|
flowstate = ConvertUtil.obj2Integer(map.get("FLOWSTATE"));
|
}
|
int num = ConvertUtil.obj2Int(map.get("NUM"));
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SL)) {
|
if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
|
wtsldxy += num;
|
}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
|
wtslclz += num;
|
}
|
continue;
|
}
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SH)) {
|
if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
|
wtshdxy += num;
|
}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
|
wtshclz += num;
|
}
|
continue;
|
}
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_WTZD)) {
|
if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
|
wtzddxy += num;
|
}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
|
wtzdclz += num;
|
}
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASP)) {
|
if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
|
faspdxy += num;
|
}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
|
faspclz += num;
|
}
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASS)) {
|
if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
|
fassdxy += num;
|
}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
|
fassclz += num;
|
}
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_HG)) {
|
if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
|
wthgdxy += num;
|
}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
|
wthgclz += num;
|
}
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_YWC)) {
|
ywc += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_YPJ)) {
|
ywc += num;
|
continue;
|
}
|
|
}
|
}
|
|
Map map = new HashMap();
|
map.put("wtsldxy", wtsldxy);
|
map.put("wtslclz", wtslclz);
|
|
map.put("wtshdxy", wtshdxy);
|
map.put("wtshclz", wtshclz);
|
|
map.put("wtzddxy", wtzddxy);
|
map.put("wtzdclz", wtzdclz);
|
|
map.put("faspdxy", faspdxy);
|
map.put("faspclz", faspclz);
|
|
map.put("fassdxy", fassdxy);
|
map.put("fassclz", fassclz);
|
|
map.put("wthgdxy", wthgdxy);
|
map.put("wthgclz", wthgclz);
|
|
//查询该加盟商未完成的事件
|
|
paramMap.put("yjj", Constants.SC_WORKFLOW_QUESTION_STATE_YWC);
|
paramMap.put("gb", Constants.SC_WORKFLOW_QUESTION_STATE_YGB);
|
paramMap.put("ypj", Constants.SC_WORKFLOW_QUESTION_STATE_YPJ);
|
|
|
jxz = baseDao.queryForInteger(sql,paramMap);
|
|
map.put("ywc", ywc);
|
map.put("jxz", jxz);
|
|
return map;
|
}
|
|
|
@Override
|
public Map queryQuestionBaseMsg( String orderId) {
|
String selectSql = "SELECT A.*,N.ANSWER_TIME FROM SC_WORKFLOW_QUESTION A LEFT JOIN WORKFLOW_BASE B ON A.ID = B.BUSINESS_ID LEFT JOIN WORKFLOW_NODE N ON B.CURRENT_NODE_ID = N.ID WHERE A.ID = :orderId ";
|
Map baseMap = baseDao.queryForMap(selectSql,new SqlParameter("orderId",orderId));
|
return baseMap;
|
}
|
|
@Override
|
public Map questionInfo(HttpServletRequest request) {
|
String questionId=request.getParameter("orderId");
|
String flowId=request.getParameter("flowId");
|
Map param =new HashMap();
|
param.put("questionId", questionId);
|
param.put("flowId", flowId);
|
StringBuilder sql=new StringBuilder();
|
sql.append("SELECT c.*,u.SJHM FROM SC_WORKFLOW_QUESTION c,gg_user u WHERE c.CREATE_USER_ID=u.id AND c.ID=:questionId ");
|
Map questionMap=baseDao.queryForMap(sql.toString(), param);
|
questionMap.put("STATE", Constants.getmapSC_WORKFLOW_QUESTION_STATE_Label(questionMap.get("STATE").toString()));
|
Map map = new HashMap();
|
map.put("questionMap", questionMap);
|
//关联工单
|
List<Map> orderList=orderService.queryLinkOrders(flowId);
|
if(orderList!=null && orderList.size()>0){
|
for(Map orderMap:orderList){
|
orderMap.put("WFSTATE", Constants.getWORKFLOW_BASE_WFSTATE_Label(ConvertUtil.obj2StrBlank(orderMap.get("WFSTATE"))));
|
orderMap.put("BUSINESSTYPE", Constants.mapWORKFLOW_BUSINESS_TYPE_Label(orderMap.get("BUSINESSTYPE").toString()));
|
}
|
}
|
map.put("orderList", orderList);
|
//关联设备
|
sql.setLength(0);
|
sql.append("SELECT * FROM cmdb_ci_ref_order r,cmdb_ci_base b WHERE r.CI_ID=b.ID");
|
sql.append(" AND r.FLOW_ID=:flowId");
|
List ciList=baseDao.queryForList(sql.toString(),param);
|
map.put("ciList", ciList);
|
//关联文档
|
List<Map> fileList=fileService.getFileList(flowId);
|
if(fileList!=null && fileList.size()>0){
|
for(Map fileMap:fileList){
|
fileMap.put("FILE_SIZE", ConvertUtil.byte2KM(Double.parseDouble(fileMap.get("FILE_SIZE").toString()), 2));
|
}
|
}
|
map.put("fileList", fileList);
|
return map;
|
}
|
|
@Override
|
public Map getLastMonthQuestionEffect(HttpServletRequest request) {
|
Map chartMap = new HashMap();
|
String cusId = request.getParameter("cusId");
|
String userId = request.getParameter("userId");
|
Long endDay = DateUtil.getCurrentDate8();
|
Long startDay = DateUtil.getDateAdd(endDay,-30,8);
|
|
SqlParameter param = new SqlParameter();
|
param.addValue("startTime", startDay+"000000")
|
.addValue("endTime", endDay + "600000")
|
.addValue("userId", userId)
|
.addValue("cusId", cusId);
|
|
StringBuilder builder = new StringBuilder();
|
if(StringUtil.isBlank(userId)){
|
builder.append(" SELECT count(ID) NUM,INFLUENCE_ID FROM SC_WORKFLOW_QUESTION WHERE ");
|
builder.append(" CUSTOMER_ID = :cusId AND CREATE_TIME >= :startTime AND CREATE_TIME <= :endTime ");
|
builder.append(" GROUP BY INFLUENCE_ID ");
|
}else{
|
builder.append(" SELECT count(q.ID) NUM,q.INFLUENCE_ID FROM SC_WORKFLOW_QUESTION q,WORKFLOW_BASE b ");
|
builder.append(" where b.ID = q.FLOW_ID and EXISTS ( ");
|
builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
|
builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime group by q.INFLUENCE_ID ");
|
}
|
|
List<Map> dataList = baseDao.queryForList(builder.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataMap = new HashMap();
|
Map tempMap = new HashMap();
|
List serieslist = new ArrayList();
|
for(Map data : dataList){
|
dataMap.put(data.get("INFLUENCE_ID").toString(), data);
|
}
|
List<Map> catelist = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.EVENT_EFFECT_DG);
|
if(catelist!=null&&catelist.size()>0){
|
for(Map cate : catelist){
|
List tempList = new ArrayList();
|
tempList.add(cate.get("DATAVALUE").toString());
|
if(dataMap.get(cate.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataMap.get(cate.get("DATAKEY").toString())).get("NUM"));
|
}else{
|
tempList.add(0);
|
}
|
serieslist.add(tempList);
|
}
|
}
|
chartMap.put("seriesData", serieslist);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getLastMonthQuestionServer(HttpServletRequest request) {
|
Map chartMap = new HashMap();
|
Long endDay = DateUtil.getCurrentDate8();
|
Long startDay = DateUtil.getDateAdd(endDay,-30,8);
|
|
String cusId = request.getParameter("cusId");
|
String userId = request.getParameter("userId");
|
StringBuilder builder = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startTime", startDay+"000000")
|
.addValue("endTime", endDay + "600000")
|
.addValue("userId", userId);
|
if(StringUtil.isBlank(userId)){
|
builder.append(" select count(ID) NUM,THIRD_CATEGORY_ID SERVER_ID from SC_WORKFLOW_QUESTION where CUSTOMER_ID = :cusId ");
|
builder.append(" and CREATE_TIME>=:startTime and CREATE_TIME<=:endTime group by SERVER_ID ");
|
}else{
|
builder.append(" select count(q.ID) NUM,q.THIRD_CATEGORY_ID SERVER_ID from SC_WORKFLOW_QUESTION q,WORKFLOW_BASE b ");
|
builder.append(" where b.ID = q.FLOW_ID and EXISTS ( ");
|
builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
|
builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime group by SERVER_ID ");
|
}
|
|
|
|
|
List<Map> dataList = baseDao.queryForList(builder.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
builder.setLength(0);
|
builder.append(" SELECT C.ID SERVER_ID,C.CATEGORY_NAME SERVER_NAME FROM SC_SERVCE_CATEGORY C,SC_SERVCE_CATEGORY_CUSTOMER P ");
|
builder.append(" WHERE C.ID = P.CATEGORY_ID and C.LEVEL = 3 and C.STATE=1 ");
|
builder.append(" AND P.STATE = 1 AND P.CUSTOMER_ID = :cusId ORDER BY SERIAL ASC ");
|
List<Map> categoriesList = baseDao.queryForList(builder.toString(),param);
|
List<String> categories = new ArrayList<String>();
|
Map dataMap = new HashMap();
|
Map tempMap = new HashMap();
|
List<Map> series = new ArrayList<Map>();
|
tempMap.put("name", "数量");
|
List tempList = new ArrayList();
|
for(Map data:dataList){
|
dataMap.put(data.get("SERVER_ID").toString(), data);
|
}
|
for(Map cate:categoriesList){
|
categories.add(cate.get("SERVER_NAME").toString());
|
if(dataMap.get(cate.get("SERVER_ID").toString())!=null){
|
tempList.add(((Map)dataMap.get(cate.get("SERVER_ID").toString())).get("NUM"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
chartMap.put("categories",categories);
|
chartMap.put("series",series);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getLastMonthQuestionLv(HttpServletRequest request) {
|
Map chartMap = new HashMap();
|
String cusId = request.getParameter("cusId");
|
String userId = request.getParameter("userId");
|
Long endDay = DateUtil.getCurrentDate8();
|
Long startDay = DateUtil.getDateAdd(endDay,-30,8);
|
|
SqlParameter param = new SqlParameter();
|
param.addValue("startTime", startDay+"000000")
|
.addValue("endTime", endDay + "600000")
|
.addValue("userId", userId)
|
.addValue("cusId", cusId);
|
|
StringBuilder builder = new StringBuilder();
|
if(StringUtil.isBlank(userId)){
|
builder.append(" SELECT count(ID) NUM,PRI_LEVEL_ID FROM SC_WORKFLOW_QUESTION WHERE ");
|
builder.append(" CUSTOMER_ID = :cusId AND CREATE_TIME >= :startTime AND CREATE_TIME <= :endTime ");
|
builder.append(" GROUP BY PRI_LEVEL_ID ");
|
}else{
|
builder.append(" SELECT count(q.ID) NUM,q.PRI_LEVEL_ID FROM SC_WORKFLOW_QUESTION q,WORKFLOW_BASE b ");
|
builder.append(" where b.ID = q.FLOW_ID and EXISTS ( ");
|
builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
|
builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime group by q.PRI_LEVEL_ID ");
|
}
|
|
List<Map> dataList = baseDao.queryForList(builder.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataMap = new HashMap();
|
Map tempMap = new HashMap();
|
List serieslist = new ArrayList();
|
|
for(Map data : dataList){
|
dataMap.put(data.get("PRI_LEVEL_ID").toString(), data);
|
}
|
|
builder.setLength(0);
|
builder.append(" select ID LEVEL_ID,LEVEL_NAME from SC_SLA order by SERIAL ");
|
List<Map> catelist = baseDao.queryForList(builder.toString());
|
for(Map cate : catelist){
|
List tempList = new ArrayList();
|
tempList.add(cate.get("LEVEL_NAME").toString());
|
if(dataMap.get(cate.get("LEVEL_ID").toString())!=null){
|
tempList.add(((Map)dataMap.get(cate.get("LEVEL_ID").toString())).get("NUM"));
|
}else{
|
tempList.add(0);
|
}
|
serieslist.add(tempList);
|
}
|
chartMap.put("seriesData", serieslist);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getLastMonthQuestionPri(HttpServletRequest request) {
|
Map chartMap = new HashMap();
|
String cusId = request.getParameter("cusId");
|
String userId = request.getParameter("userId");
|
Long endDay = DateUtil.getCurrentDate8();
|
Long startDay = DateUtil.getDateAdd(endDay,-30,8);
|
|
SqlParameter param = new SqlParameter();
|
param.addValue("startTime", startDay+"000000")
|
.addValue("endTime", endDay + "600000")
|
.addValue("userId", userId)
|
.addValue("cusId", cusId);
|
|
StringBuilder builder = new StringBuilder();
|
if(StringUtil.isBlank(userId)){
|
builder.append(" SELECT count(ID) NUM,PRIORITY_ID FROM SC_WORKFLOW_QUESTION WHERE ");
|
builder.append(" CUSTOMER_ID = :cusId AND CREATE_TIME >= :startTime AND CREATE_TIME <= :endTime ");
|
builder.append(" GROUP BY PRIORITY_ID ");
|
}else{
|
builder.append(" SELECT count(q.ID) NUM,q.PRIORITY_ID FROM SC_WORKFLOW_QUESTION q,WORKFLOW_BASE b ");
|
builder.append(" where b.ID = q.FLOW_ID and EXISTS ( ");
|
builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
|
builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime group by q.PRIORITY_ID ");
|
}
|
|
List<Map> dataList = baseDao.queryForList(builder.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataMap = new HashMap();
|
Map tempMap = new HashMap();
|
List serieslist = new ArrayList();
|
for(Map data : dataList){
|
dataMap.put(data.get("PRIORITY_ID").toString(), data);
|
}
|
List<Map> catelist = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.EVENT_PRI);
|
for(Map cate : catelist){
|
List tempList = new ArrayList();
|
tempList.add(cate.get("DATAVALUE").toString());
|
if(dataMap.get(cate.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataMap.get(cate.get("DATAKEY").toString())).get("NUM"));
|
}else{
|
tempList.add(0);
|
}
|
serieslist.add(tempList);
|
}
|
chartMap.put("seriesData", serieslist);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getLastMonthQuestionCount(HttpServletRequest request) {
|
Map chartMap = new HashMap();
|
Long endDay = DateUtil.getCurrentDate8();
|
Long startDay = DateUtil.getDateAdd(endDay,-30,8);
|
|
String cusId = request.getParameter("cusId");
|
String userId = request.getParameter("userId");
|
StringBuilder builder = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startTime", startDay+"000000")
|
.addValue("userId", userId)
|
.addValue("endTime", endDay + "600000");
|
if(StringUtil.isBlank(userId)){
|
builder.append(" select count(ID) NUM,DATE_FORMAT(CREATE_TIME,'%Y%m%d') CREATEDAY from SC_WORKFLOW_QUESTION where CUSTOMER_ID = :cusId ");
|
builder.append(" and CREATE_TIME>=:startTime and CREATE_TIME<=:endTime group by CREATEDAY ");
|
}else{
|
builder.append(" select count(q.ID) NUM,DATE_FORMAT(q.CREATE_TIME,'%Y%m%d') CREATEDAY from workflow_base b ,SC_WORKFLOW_QUESTION q ");
|
builder.append(" where b.ID = q.FLOW_ID and EXISTS ( ");
|
builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
|
builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime group by CREATEDAY ");
|
}
|
|
List<Map> dataList = baseDao.queryForList(builder.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
|
List<String> categories = DateUtil.getDates(startDay ,endDay);
|
categories.add(String.valueOf(DateUtil.getCurrentDate8()));
|
List<String> categories_format = new ArrayList<String>();
|
Map dataMap = new HashMap();
|
Map tempMap = new HashMap();
|
List<Map> series = new ArrayList<Map>();
|
tempMap.put("name", "问题数量");
|
List tempList = new ArrayList();
|
for(Map data:dataList){
|
dataMap.put(data.get("CREATEDAY").toString(), data);
|
}
|
for(String day:categories){
|
categories_format.add(DateUtil.format("yyyy-MM-dd",day));
|
if(dataMap.get(day)!=null){
|
tempList.add(((Map)dataMap.get(day)).get("NUM"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
chartMap.put("categories",categories_format);
|
chartMap.put("step",Math.ceil(categories.size()/9));
|
chartMap.put("series",series);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map queryQuestionpoolCount(String cusId) {
|
//初始化变量
|
int ywc = 0;
|
//问题受理
|
int wtsl = 0;
|
|
//问题审核
|
int wtsh = 0;
|
|
//问题诊断
|
int wtzd = 0;
|
|
//方案审批
|
int fasp = 0;
|
|
//方案实施
|
int fass = 0;
|
|
//问题回顾
|
int wthg = 0;
|
StringBuffer sql = new StringBuffer();
|
StringBuffer sqlall = new StringBuffer();
|
Map paramMap = new HashMap();
|
paramMap.put("wfstate", Constants.WORKFLOW_BASE_WFSTATE_DELETE);
|
paramMap.put("cusId", cusId);
|
//查询该人员所属的项目
|
sql.append("SELECT COUNT(*) AS NUM,E.STATE FROM SC_WORKFLOW_QUESTION E,WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_ROLE B WHERE B.STATE = 1 ");
|
sqlall.append("SELECT COUNT(F.ID) FROM (SELECT E.* FROM SC_WORKFLOW_QUESTION E,WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_ROLE B WHERE B.STATE = 1 ");
|
sql.append(") C WHERE E.ID = D.BUSINESS_ID AND E.CUSTOMER_ID = :cusId AND D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
|
sql.append(" GROUP BY E.STATE ");
|
sqlall.append(") C WHERE E.ID = D.BUSINESS_ID AND E.CUSTOMER_ID = :cusId AND D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
|
sqlall.append(" GROUP BY E.ID) F ");
|
List<Map> list = baseDao.queryForList(sql.toString(),paramMap);
|
if(null!=list && list.size()>0) {
|
for(Map map : list) {
|
String state = ConvertUtil.obj2StrBlank(map.get("STATE"));
|
int num = ConvertUtil.obj2Int(map.get("NUM"));
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SL)) {
|
wtsl += num;
|
continue;
|
}
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SH)) {
|
wtsh += num;
|
continue;
|
}
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_WTZD)) {
|
wtzd += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASP)) {
|
fasp += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASS)) {
|
fass += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_HG)) {
|
wthg += num;
|
continue;
|
}
|
|
}
|
}
|
int all = baseDao.queryForInteger(sqlall.toString(), paramMap);
|
Map map = new HashMap();
|
map.put("wtsl", wtsl);
|
|
map.put("wtsh", wtsh);
|
|
map.put("wtzd", wtzd);
|
|
map.put("fasp", fasp);
|
|
map.put("fass", fass);
|
|
map.put("wthg", wthg);
|
|
map.put("all", all);
|
return map;
|
}
|
|
@Override
|
public int queryQuestionJxzCount(Map<String,String> params) {
|
StringBuilder sql = new StringBuilder();
|
StringBuilder sqlpart = new StringBuilder();
|
sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB ");
|
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND WB.BUSINESSTYPE = :businessType AND WB.WFSTATE = 1 ");
|
|
sqlpart = getJxzSql(sqlpart,params);
|
|
sql.append(sqlpart);
|
sql.append( " GROUP BY B.ID ) D");
|
params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
return baseDao.queryForInteger(sql.toString(),params);
|
}
|
|
@Override
|
public PageInfo queryQuestionJxzList(PageInfo pageInfo,Map<String,String> params) {
|
StringBuilder sql = new StringBuilder();
|
StringBuilder sqlpart = new StringBuilder();
|
sql.append("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE , ");
|
sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.SUB_CUSTOMER_NAME,WB.PROJECT_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,B.STATE ,B.NAME ");
|
sql.append("FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
|
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND C.FLOWID = WB.ID AND WB.BUSINESSTYPE = :businessType AND WB.WFSTATE = 1 ");
|
params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
sqlpart = getJxzSql(sqlpart,params);
|
sql.append(sqlpart);
|
sql.append( " GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
|
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
public StringBuilder getJxzSql(StringBuilder sqlpart,Map<String,String> params){
|
|
String cusId = params.get("cusId");
|
if(StringUtil.notEmpty(cusId)) {
|
sqlpart.append(" AND B.CUSTOMER_ID = :cusId ");
|
}
|
String subCustomerId = params.get("subCustomerId");
|
if(StringUtil.notEmpty(subCustomerId)) {
|
sqlpart.append(" AND B.SUB_CUSTOMER_ID = :subCustomerId ");
|
params.put("subCustomerId", subCustomerId);
|
}
|
|
String orderName = params.get("orderName");
|
if(StringUtil.notEmpty(orderName)) {
|
sqlpart.append(" AND B.NAME LIKE :orderName ");
|
params.put("orderName", "%"+orderName+"%");
|
}
|
|
String orderCode = params.get("orderCode");
|
if(StringUtil.notEmpty(orderCode)) {
|
sqlpart.append(" AND B.ORDER_CODE LIKE :orderCode ");
|
params.put("orderCode", "%"+orderCode+"%");
|
}
|
|
|
String contract = params.get("contract");
|
if(StringUtil.notEmpty(contract)) {
|
sqlpart.append(" AND B.CONTACT_NAME LIKE :contract ");
|
params.put("contract", "%"+contract+"%");
|
}
|
|
|
String pri = params.get("pri");
|
if(StringUtil.notEmpty(pri)) {
|
String[] pris = pri.split(",");
|
if(pris.length==1) {
|
sqlpart.append(" AND B.PRIORITY_ID = :priority_id");
|
params.put("priority_id", pris[0]);
|
} else {
|
sqlpart.append(" AND B.PRIORITY_ID IN (");
|
for(int i=0; i<pris.length; i++) {
|
sqlpart.append(" :pri"+i);
|
sqlpart.append(",");
|
params.put("pri"+i, pris[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
|
String dg = params.get("dg");
|
if(StringUtil.notEmpty(dg)) {
|
String[] dgs = dg.split(",");
|
if(dgs.length==1) {
|
sqlpart.append(" AND B.INFLUENCE_ID = :influence_id");
|
params.put("influence_id", dgs[0]);
|
} else {
|
sqlpart.append(" AND B.INFLUENCE_ID IN (");
|
for(int i=0; i<dgs.length; i++) {
|
sqlpart.append(" :dg"+i);
|
sqlpart.append(",");
|
params.put("dg"+i, dgs[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
String source = params.get("source");
|
if(StringUtil.notEmpty(source)) {
|
String[] sources = source.split(",");
|
if(sources.length==1) {
|
sqlpart.append(" AND B.SOURCE_ID = :type_id");
|
params.put("type_id", sources[0]);
|
} else {
|
sqlpart.append(" AND B.SOURCE_ID IN (");
|
for(int i=0; i<sources.length; i++) {
|
sqlpart.append(" :type"+i);
|
sqlpart.append(",");
|
params.put("type"+i, sources[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
String sl = params.get("sl");
|
if(StringUtil.notEmpty(sl)) {
|
String[] sls = sl.split(",");
|
if(sls.length==1) {
|
sqlpart.append(" AND B.THIRD_CATEGORY_ID = :third_category_id");
|
params.put("third_category_id", sls[0]);
|
} else {
|
sqlpart.append(" AND B.THIRD_CATEGORY_ID IN (");
|
for(int i=0; i<sls.length; i++) {
|
sqlpart.append(" :third_category_id"+i);
|
sqlpart.append(",");
|
params.put("third_category_id"+i, sls[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
String status = params.get("status");
|
if(StringUtil.notEmpty(status)) {
|
sqlpart.append( " AND B.STATE = :state ");
|
params.put("state", status);
|
}
|
return sqlpart;
|
}
|
|
@Override
|
public int queryQuestionEndCount(Map<String,String> params) {
|
StringBuilder sql = new StringBuilder();
|
StringBuilder sqlpart = new StringBuilder();
|
sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB ");
|
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND WB.BUSINESSTYPE = :businessType AND WB.WFSTATE = :type ");
|
|
sqlpart = getEndSql(sqlpart,params);
|
sql.append(sqlpart);
|
sql.append( " GROUP BY B.ID ) D");
|
params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
return baseDao.queryForInteger(sql.toString(),params);
|
}
|
|
@Override
|
public PageInfo queryQuestionEndList(PageInfo pageInfo,Map<String,String> params) {
|
StringBuilder sql = new StringBuilder();
|
StringBuilder sqlpart = new StringBuilder();
|
sql.append("SELECT B.RESOLVE_TYPE_NAME,B.CREATE_TIME,B.CREATE_USER_NAME,WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE ,WB.ENDTIME , ");
|
sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.SUB_CUSTOMER_NAME,WB.PROJECT_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,B.STATE ,B.NAME ,");
|
sql.append(" D.HANG_UP_USERNAME,D.HANG_UP_TIME,D.HANG_UP_REASON ,WB.SCORE ");
|
sql.append("FROM (SELECT F.*,E.SCORE FROM WORKFLOW_BASE F LEFT JOIN ORDER_SATIS_INFO E ON F.ID = E.BUSINESS_ID WHERE F.BUSINESSTYPE = :businessType ) WB, WORKFLOW_NODE C ,SC_WORKFLOW_QUESTION B LEFT JOIN HANG_UP_INFO D ON B.ID = D.BUS_ID ");
|
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND C.FLOWID = WB.ID AND WB.WFSTATE = :type ");
|
params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
sqlpart = getEndSql(sqlpart,params);
|
|
sql.append(sqlpart);
|
sql.append( " GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
|
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
public StringBuilder getEndSql(StringBuilder sqlpart,Map<String,String> params){
|
String customerId = params.get("cusId");
|
if(StringUtil.notEmpty(customerId)) {
|
sqlpart.append(" AND B.CUSTOMER_ID = :customerId ");
|
params.put("customerId", customerId);
|
}
|
String subCustomerId = params.get("subCustomerId");
|
if(StringUtil.notEmpty(subCustomerId)) {
|
sqlpart.append(" AND B.SUB_CUSTOMER_ID = :subCustomerId ");
|
params.put("subCustomerId", subCustomerId);
|
}
|
|
String orderName = params.get("orderName");
|
if(StringUtil.notEmpty(orderName)) {
|
sqlpart.append(" AND B.NAME LIKE :orderName ");
|
params.put("orderName", "%"+orderName+"%");
|
}
|
|
String orderCode = params.get("orderCode");
|
if(StringUtil.notEmpty(orderCode)) {
|
sqlpart.append(" AND B.ORDER_CODE LIKE :orderCode ");
|
params.put("orderCode", "%"+orderCode+"%");
|
}
|
|
|
String contract = params.get("contract");
|
if(StringUtil.notEmpty(contract)) {
|
sqlpart.append(" AND B.CONTACT_NAME LIKE :contract ");
|
params.put("contract", "%"+contract+"%");
|
}
|
|
|
String pri = params.get("pri");
|
if(StringUtil.notEmpty(pri)) {
|
String[] pris = pri.split(",");
|
if(pris.length==1) {
|
sqlpart.append(" AND B.PRIORITY_ID = :priority_id");
|
params.put("priority_id", pris[0]);
|
} else {
|
sqlpart.append(" AND B.PRIORITY_ID IN (");
|
for(int i=0; i<pris.length; i++) {
|
sqlpart.append(" :pri"+i);
|
sqlpart.append(",");
|
params.put("pri"+i, pris[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
|
String dg = params.get("dg");
|
if(StringUtil.notEmpty(dg)) {
|
String[] dgs = dg.split(",");
|
if(dgs.length==1) {
|
sqlpart.append(" AND B.INFLUENCE_ID = :influence_id");
|
params.put("influence_id", dgs[0]);
|
} else {
|
sqlpart.append(" AND B.INFLUENCE_ID IN (");
|
for(int i=0; i<dgs.length; i++) {
|
sqlpart.append(" :dg"+i);
|
sqlpart.append(",");
|
params.put("dg"+i, dgs[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
String source = params.get("source");
|
if(StringUtil.notEmpty(source)) {
|
String[] sources = source.split(",");
|
if(sources.length==1) {
|
sqlpart.append(" AND B.SOURCE_ID = :type_id");
|
params.put("type_id", sources[0]);
|
} else {
|
sqlpart.append(" AND B.SOURCE_ID IN (");
|
for(int i=0; i<sources.length; i++) {
|
sqlpart.append(" :type"+i);
|
sqlpart.append(",");
|
params.put("type"+i, sources[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
String sl = params.get("sl");
|
if(StringUtil.notEmpty(sl)) {
|
String[] sls = sl.split(",");
|
if(sls.length==1) {
|
sqlpart.append(" AND B.THIRD_CATEGORY_ID = :third_category_id");
|
params.put("third_category_id", sls[0]);
|
} else {
|
sqlpart.append(" AND B.THIRD_CATEGORY_ID IN (");
|
for(int i=0; i<sls.length; i++) {
|
sqlpart.append(" :third_category_id"+i);
|
sqlpart.append(",");
|
params.put("third_category_id"+i, sls[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
String status = params.get("status");
|
if(StringUtil.notEmpty(status)) {
|
sqlpart.append( " AND B.STATE = :state ");
|
params.put("state", status);
|
}
|
return sqlpart;
|
}
|
|
@Override
|
public int questionpoolCount(Map<String,String> params) {
|
/**
|
* 查询我的工单池规则
|
* 如果是一二三线或者驻场工程师桌面,则直接查询工单表中 所属分组是当前工程师所在组的
|
* 如果是项目服务台，则直接查询工单表中所属分组是当前工程师所在组的 （同上）
|
* 如果是客户服务台，查询我的工单池 则查询工单表中分组是 （查询该客户下所有的项目的服务台分组）
|
* 如果是总服务台，则查询所有客户下（加盟商下所有项目的服务台分组）
|
*/
|
|
|
StringBuffer sql = new StringBuffer();
|
params.put("wfstate",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_WFSTATE_DELETE));
|
|
sql.append("SELECT COUNT(D.ID) FROM WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_ROLE B WHERE STATE = 1");
|
sql.append(") C WHERE D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
|
StringBuilder builder = new StringBuilder(sql);
|
|
builder = getPoolSql(builder,params);
|
|
builder.append(" AND D.BUSINESSTYPE = :businessType");
|
params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
|
int count = baseDao.queryForInteger(builder.toString(),params);
|
|
return 0;
|
}
|
|
@Override
|
public PageInfo questionpoolData(PageInfo pageInfo,Map<String,String> params) {
|
|
/**
|
* 查询我的工单池规则
|
* 如果是项目服务台，则直接查询工单表
|
* 如果是客户服务台，查询我的工单池 则查询工单表中分组是 （查询该客户下所有的项目的分组）
|
* 如果是总服务台，则查询所有客户下（加盟商下所有项目的分组）
|
*/
|
|
|
StringBuffer sql = new StringBuffer();
|
params.put("wfstate",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_WFSTATE_DELETE));
|
//查询该人员所属的项目
|
sql.append("SELECT D.* FROM WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_ROLE B WHERE STATE = 1 ");
|
|
sql.append(") C WHERE D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
|
StringBuilder builder = new StringBuilder(sql);
|
|
builder = getPoolSql(builder,params);
|
|
builder.append(" AND D.BUSINESSTYPE = :businessType");
|
params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
|
PageInfo result = baseDao.queryforSplitPageInfo(pageInfo, builder.toString(), params);
|
|
return result;
|
}
|
|
public StringBuilder getPoolSql(StringBuilder builder,Map<String,String> params){
|
|
String status = params.get("status");
|
if(StringUtil.notEmpty(status)) {
|
builder.append(" AND D.WFSTATE = :status ");
|
}
|
String cusId = params.get("cusId");
|
if(StringUtil.notEmpty(cusId)) {
|
builder.append(" AND D.CUSTOMER_ID = :cusId ");
|
}
|
String subCustomerId = params.get("subCustomerId");
|
if(StringUtil.notEmpty(subCustomerId)) {
|
builder.append(" AND D.SUB_CUSTOMER_ID = :subCustomerId ");
|
}
|
|
String orderName = params.get("orderName");
|
if(StringUtil.notEmpty(orderName)) {
|
builder.append(" AND D.WFNAME LIKE :orderName ");
|
params.put("orderName", "%"+orderName+"%");
|
}
|
|
|
String orderCode = params.get("orderCode");
|
if(StringUtil.notEmpty(orderCode)) {
|
builder.append(" AND D.ORDER_CODE LIKE :orderCode");
|
params.put("orderCode", "%"+orderCode+"%");
|
}
|
|
String customerName = params.get("customerName");
|
if(StringUtil.notEmpty(customerName)) {
|
builder.append(" AND D.CUSTOMER_NAME LIKE :customerName ");
|
params.put("customerName", "%"+customerName+"%");
|
}
|
return builder;
|
}
|
|
@Override
|
public Map queryQuestionJxzNodeCount(String cusId) {
|
//初始化变量
|
int ywc = 0;
|
//问题受理
|
int wtsl = 0;
|
|
//问题审核
|
int wtsh = 0;
|
|
//问题诊断
|
int wtzd = 0;
|
|
//方案审批
|
int fasp = 0;
|
|
//方案实施
|
int fass = 0;
|
|
//问题回顾
|
int wthg = 0;
|
|
|
//只有服务台和管理人员才可以看到所有的工单，（所以查询当前的用户属于那种服务台）
|
StringBuffer selectSql = new StringBuffer();
|
StringBuffer sql = new StringBuffer();
|
|
Map paramMap = new HashMap();
|
selectSql.append( "SELECT COUNT(*) AS NUM,A.STATE FROM SC_WORKFLOW_QUESTION A,WORKFLOW_BASE B WHERE A.ID = B.BUSINESS_ID AND B.WFSTATE = 1 ");
|
sql.append( "SELECT COUNT(A.ID) FROM SC_WORKFLOW_QUESTION A,WORKFLOW_BASE B WHERE A.ID = B.BUSINESS_ID AND B.WFSTATE = 1 ");
|
selectSql.append(" AND A.CUSTOMER_ID = :cusId ");
|
sql.append(" AND A.CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", cusId);
|
selectSql.append(" GROUP BY A.STATE ");
|
|
List<Map> list = baseDao.queryForList(selectSql.toString(),paramMap);
|
if(null!=list && list.size()>0) {
|
for(Map map : list) {
|
String state = ConvertUtil.obj2StrBlank(map.get("STATE"));
|
int num = ConvertUtil.obj2Int(map.get("NUM"));
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SL)) {
|
wtsl += num;
|
continue;
|
}
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SH)) {
|
wtsh += num;
|
continue;
|
}
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_WTZD)) {
|
wtzd += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASP)) {
|
fasp += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASS)) {
|
fass += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_HG)) {
|
wthg += num;
|
continue;
|
}
|
|
}
|
}
|
int all = baseDao.queryForInteger(sql.toString(), paramMap);
|
Map map = new HashMap();
|
map.put("wtsl", wtsl);
|
|
map.put("wtsh", wtsh);
|
|
map.put("wtzd", wtzd);
|
|
map.put("fasp", fasp);
|
|
map.put("fass", fass);
|
|
map.put("wthg", wthg);
|
|
map.put("all", all);
|
|
return map;
|
}
|
|
@Override
|
public Map queryQuestionNodeCount(String cusId) {
|
//初始化变量
|
int ywc = 0;
|
//问题受理
|
int wtsl = 0;
|
|
//问题审核
|
int wtsh = 0;
|
|
//问题诊断
|
int wtzd = 0;
|
|
//方案审批
|
int fasp = 0;
|
|
//方案实施
|
int fass = 0;
|
|
//问题回顾
|
int wthg = 0;
|
|
|
//只有服务台和管理人员才可以看到所有的工单，（所以查询当前的用户属于那种服务台）
|
StringBuffer selectSql = new StringBuffer();
|
StringBuffer sql = new StringBuffer();
|
|
Map paramMap = new HashMap();
|
selectSql.append( "SELECT COUNT(*) AS NUM,D.STATE FROM (SELECT A.* FROM SC_WORKFLOW_QUESTION A,WORKFLOW_BASE B , WORKFLOW_NODE C WHERE B.ID = C.FLOWID AND C.FLOWSTATE = 1 AND C.CURRENT_DEALER_ID IS NOT NULL AND A.ID = B.BUSINESS_ID ");
|
sql.append( "SELECT COUNT(D.ID) FROM (SELECT A.* FROM SC_WORKFLOW_QUESTION A,WORKFLOW_BASE B , WORKFLOW_NODE C WHERE B.ID = C.FLOWID AND C.FLOWSTATE = 1 AND C.CURRENT_DEALER_ID IS NOT NULL AND A.ID = B.BUSINESS_ID ");
|
selectSql.append(" AND A.CUSTOMER_ID = :cusId ");
|
sql.append(" AND A.CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId",cusId);
|
selectSql.append(" GROUP BY A.ID) D GROUP BY D.STATE ");
|
sql.append(" GROUP BY A.ID) D ");
|
|
List<Map> list = baseDao.queryForList(selectSql.toString(),paramMap);
|
if(null!=list && list.size()>0) {
|
for(Map map : list) {
|
String state = ConvertUtil.obj2StrBlank(map.get("STATE"));
|
int num = ConvertUtil.obj2Int(map.get("NUM"));
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SL)) {
|
wtsl += num;
|
continue;
|
}
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SH)) {
|
wtsh += num;
|
continue;
|
}
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_WTZD)) {
|
wtzd += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASP)) {
|
fasp += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASS)) {
|
fass += num;
|
continue;
|
}
|
|
if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_HG)) {
|
wthg += num;
|
continue;
|
}
|
|
}
|
}
|
int all = baseDao.queryForInteger(sql.toString(), paramMap);
|
Map map = new HashMap();
|
map.put("wtsl", wtsl);
|
|
map.put("wtsh", wtsh);
|
|
map.put("wtzd", wtzd);
|
|
map.put("fasp", fasp);
|
|
map.put("fass", fass);
|
|
map.put("wthg", wthg);
|
|
map.put("all", all);
|
|
return map;
|
}
|
@Override
|
public int queryQuestionOrderNodeCount(Map<String,String> params) {
|
StringBuilder sql = new StringBuilder();
|
StringBuilder sqlpart = new StringBuilder();
|
sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
|
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND C.FLOWID = WB.ID AND C.FLOWSTATE = 1 AND C.CURRENT_DEALER_ID IS NOT NULL AND WB.BUSINESSTYPE = :businessType ");
|
|
sqlpart = getNodeSql(sqlpart,params);
|
sql.append(sqlpart);
|
sql.append( " GROUP BY B.ID ) D");
|
params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
int count = baseDao.queryForInteger(sql.toString(),params);
|
return 0;
|
}
|
@Override
|
public PageInfo queryQuestionOrderNodeList(PageInfo pageInfo,Map<String,String> params) {
|
StringBuilder sql = new StringBuilder();
|
StringBuilder sqlpart = new StringBuilder();
|
sql.append("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE , ");
|
sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.SUB_CUSTOMER_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,B.STATE ,B.NAME ");
|
sql.append("FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
|
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND C.FLOWID = WB.ID AND C.FLOWSTATE = 1 AND C.CURRENT_DEALER_ID IS NOT NULL AND WB.BUSINESSTYPE = :businessType ");
|
params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
|
|
sqlpart = getNodeSql(sqlpart,params);
|
sql.append(sqlpart);
|
sql.append( " GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
|
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
public StringBuilder getNodeSql(StringBuilder sqlpart,Map<String,String> params){
|
|
String cusId = params.get("cusId");
|
if(StringUtil.notEmpty(cusId)) {
|
sqlpart.append(" AND B.CUSTOMER_ID = :cusId ");
|
}
|
String subCustomerId = params.get("subCustomerId");
|
if(StringUtil.notEmpty(subCustomerId)) {
|
sqlpart.append(" AND B.SUB_CUSTOMER_ID = :subCustomerId ");
|
}
|
|
String orderName = params.get("orderName");
|
if(StringUtil.notEmpty(orderName)) {
|
sqlpart.append(" AND B.NAME LIKE :orderName ");
|
params.put("orderName", "%"+orderName+"%");
|
}
|
|
String orderCode = params.get("orderCode");
|
if(StringUtil.notEmpty(orderCode)) {
|
sqlpart.append(" AND B.ORDER_CODE LIKE :orderCode ");
|
params.put("orderCode", "%"+orderCode+"%");
|
}
|
|
|
String contract = params.get("contract");
|
if(StringUtil.notEmpty(contract)) {
|
sqlpart.append(" AND B.CONTACT_NAME LIKE :contract ");
|
params.put("contract", "%"+contract+"%");
|
}
|
|
|
String pri = params.get("pri");
|
if(StringUtil.notEmpty(pri)) {
|
String[] pris = pri.split(",");
|
if(pris.length==1) {
|
sqlpart.append(" AND B.PRIORITY_ID = :priority_id");
|
params.put("priority_id", pris[0]);
|
} else {
|
sqlpart.append(" AND B.PRIORITY_ID IN (");
|
for(int i=0; i<pris.length; i++) {
|
sqlpart.append(" :pri"+i);
|
sqlpart.append(",");
|
params.put("pri"+i, pris[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
|
String dg = params.get("dg");
|
if(StringUtil.notEmpty(dg)) {
|
String[] dgs = dg.split(",");
|
if(dgs.length==1) {
|
sqlpart.append(" AND B.INFLUENCE_ID = :influence_id");
|
params.put("influence_id", dgs[0]);
|
} else {
|
sqlpart.append(" AND B.INFLUENCE_ID IN (");
|
for(int i=0; i<dgs.length; i++) {
|
sqlpart.append(" :dg"+i);
|
sqlpart.append(",");
|
params.put("dg"+i, dgs[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
String source = params.get("source");
|
if(StringUtil.notEmpty(source)) {
|
String[] sources = source.split(",");
|
if(sources.length==1) {
|
sqlpart.append(" AND B.SOURCE_ID = :type_id");
|
params.put("type_id", sources[0]);
|
} else {
|
sqlpart.append(" AND B.SOURCE_ID IN (");
|
for(int i=0; i<sources.length; i++) {
|
sqlpart.append(" :type"+i);
|
sqlpart.append(",");
|
params.put("type"+i, sources[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
|
|
String sl = params.get("sl");
|
if(StringUtil.notEmpty(sl)) {
|
String[] sls = sl.split(",");
|
if(sls.length==1) {
|
sqlpart.append(" AND B.THIRD_CATEGORY_ID = :third_category_id");
|
params.put("third_category_id", sls[0]);
|
} else {
|
sqlpart.append(" AND B.THIRD_CATEGORY_ID IN (");
|
for(int i=0; i<sls.length; i++) {
|
sqlpart.append(" :third_category_id"+i);
|
sqlpart.append(",");
|
params.put("third_category_id"+i, sls[i]);
|
}
|
sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
|
sqlpart.append(")");
|
}
|
}
|
String status = params.get("status");
|
if(StringUtil.notEmpty(status)) {
|
sqlpart.append( " AND B.STATE = :state ");
|
params.put("state", status);
|
}
|
return sqlpart;
|
}
|
|
}
|
