From 425675051e544cf29b2132615cfbf7a93dc5e51f Mon Sep 17 00:00:00 2001
From: cy <1664593601@qq.com>
Date: 星期三, 22 六月 2022 15:50:59 +0800
Subject: [PATCH] fix(dm): MySQL=》DMSQL
---
src/cn/ksource/web/facade/incident/IncidentFacadeImpl.java | 89 +++++++++++++++++++++++---------------------
1 files changed, 46 insertions(+), 43 deletions(-)
diff --git a/src/cn/ksource/web/facade/incident/IncidentFacadeImpl.java b/src/cn/ksource/web/facade/incident/IncidentFacadeImpl.java
index 7c04073..5a8d902 100644
--- a/src/cn/ksource/web/facade/incident/IncidentFacadeImpl.java
+++ b/src/cn/ksource/web/facade/incident/IncidentFacadeImpl.java
@@ -884,7 +884,7 @@
}
@Override
public Map queryContactNameInfo(Map param) {
-
+
String selectSql = "SELECT CONTACT_NAME,YWKS,SUB_CUSTOMER_ID,SUB_CUSTOMER_NAME,CONTACTS_TYPE FROM CUSTOMER_CONTACTS WHERE CONTACT_NAME = :contactName AND CUSTOMER_ID = :customer_id ";
Map map = baseDao.queryForMap(selectSql,param);
@@ -1762,30 +1762,30 @@
String flowstate = params.get("flowstate");
if (StringUtil.notEmpty(flowstate)) {
if (flowstate.equals("1")) {
- builder.append(" SELECT DISTINCT D.* FROM ( ");
+ builder.append(" SELECT DISTINCT ID, WFNAME, BUSINESSTYPE, WFSTATE, WORKFLOWTYPE, CREATERNAME, CREATERID, CREATETIME, ENDTIME, MAINFLOWID, CURRENT_NODE_NAME, CURRENT_NODE_ID, DEAL_TYPE, CURRENT_DEALER_ID, CURRENT_DEALER_NAME, CURRENT_DEAL_ROLEIDA, CURRENT_DEAL_ROLENAME, WF_TEMPLATE_ID, BUSINESS_ID, ORDER_CODE, CUSTOMER_ID, CUSTOMER_NAME, PROJECT_ID, PROJECT_NAME, SUB_CUSTOMER_NAME, SUB_CUSTOMER_ID, NAME, CONTACT_NAME, PRIORITY_ID, INFLUENCE_ID, SLA_ID, THIRD_CATEGORY_ID, TYPE_ID FROM ( ");
//鏌ヨ澶勭悊浜烘槸鎴戠殑
- builder.append(" SELECT B.*,C.NAME,C.CONTACT_NAME,C.PRIORITY_ID,C.INFLUENCE_ID,C.SLA_ID,C.THIRD_CATEGORY_ID,C.TYPE_ID FROM WORKFLOW_NODE A,WORKFLOW_BASE B,SC_WORKFLOW_INCIDENT C WHERE A.FLOWID = B.ID AND B.BUSINESS_ID = C.ID AND A.CURRENT_DEALER_ID = :userId AND A.FLOWSTATE = :jddcl ");
+ builder.append(" SELECT B.ID, B.WFNAME, B.BUSINESSTYPE, B.WFSTATE, B.WORKFLOWTYPE, B.CREATERNAME, B.CREATERID, B.CREATETIME, B.ENDTIME, B.MAINFLOWID, B.CURRENT_NODE_NAME, B.CURRENT_NODE_ID, B.DEAL_TYPE, B.CURRENT_DEALER_ID, B.CURRENT_DEALER_NAME, B.CURRENT_DEAL_ROLEIDA, B.CURRENT_DEAL_ROLENAME, B.WF_TEMPLATE_ID, B.BUSINESS_ID, B.ORDER_CODE, B.NOTE, B.CUSTOMER_ID, B.CUSTOMER_NAME, B.PROJECT_ID, B.PROJECT_NAME, B.SUB_CUSTOMER_NAME, B.SUB_CUSTOMER_ID,C.NAME,C.CONTACT_NAME,C.PRIORITY_ID,C.INFLUENCE_ID,C.SLA_ID,C.THIRD_CATEGORY_ID,C.TYPE_ID FROM WORKFLOW_NODE A,WORKFLOW_BASE B,SC_WORKFLOW_INCIDENT C WHERE A.FLOWID = B.ID AND B.BUSINESS_ID = C.ID AND A.CURRENT_DEALER_ID = :userId AND A.FLOWSTATE = :jddcl ");
builder.append(")D");
builder.append(" WHERE 1=1 ");
params.put("jddcl", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE));
} else if (flowstate.equals("2")) {
- builder.append(" SELECT DISTINCT D.* FROM ( ");
- builder.append(" SELECT B.*,C.NAME,C.CONTACT_NAME,C.PRIORITY_ID,C.INFLUENCE_ID,C.SLA_ID,C.THIRD_CATEGORY_ID,C.TYPE_ID FROM WORKFLOW_NODE A,WORKFLOW_BASE B,SC_WORKFLOW_INCIDENT C WHERE A.FLOWID = B.ID AND B.BUSINESS_ID = C.ID AND A.CURRENT_DEALER_ID = :userId AND A.FLOWSTATE = :jdjxz");
+ builder.append(" SELECT DISTINCT ID, WFNAME, BUSINESSTYPE, WFSTATE, WORKFLOWTYPE, CREATERNAME, CREATERID, CREATETIME, ENDTIME, MAINFLOWID, CURRENT_NODE_NAME, CURRENT_NODE_ID, DEAL_TYPE, CURRENT_DEALER_ID, CURRENT_DEALER_NAME, CURRENT_DEAL_ROLEIDA, CURRENT_DEAL_ROLENAME, WF_TEMPLATE_ID, BUSINESS_ID, ORDER_CODE, CUSTOMER_ID, CUSTOMER_NAME, PROJECT_ID, PROJECT_NAME, SUB_CUSTOMER_NAME, SUB_CUSTOMER_ID, NAME, CONTACT_NAME, PRIORITY_ID, INFLUENCE_ID, SLA_ID, THIRD_CATEGORY_ID, TYPE_ID FROM ( ");
+ builder.append(" SELECT B.ID, B.WFNAME, B.BUSINESSTYPE, B.WFSTATE, B.WORKFLOWTYPE, B.CREATERNAME, B.CREATERID, B.CREATETIME, B.ENDTIME, B.MAINFLOWID, B.CURRENT_NODE_NAME, B.CURRENT_NODE_ID, B.DEAL_TYPE, B.CURRENT_DEALER_ID, B.CURRENT_DEALER_NAME, B.CURRENT_DEAL_ROLEIDA, B.CURRENT_DEAL_ROLENAME, B.WF_TEMPLATE_ID, B.BUSINESS_ID, B.ORDER_CODE, B.NOTE, B.CUSTOMER_ID, B.CUSTOMER_NAME, B.PROJECT_ID, B.PROJECT_NAME, B.SUB_CUSTOMER_NAME, B.SUB_CUSTOMER_ID,C.NAME,C.CONTACT_NAME,C.PRIORITY_ID,C.INFLUENCE_ID,C.SLA_ID,C.THIRD_CATEGORY_ID,C.TYPE_ID FROM WORKFLOW_NODE A,WORKFLOW_BASE B,SC_WORKFLOW_INCIDENT C WHERE A.FLOWID = B.ID AND B.BUSINESS_ID = C.ID AND A.CURRENT_DEALER_ID = :userId AND A.FLOWSTATE = :jdjxz");
builder.append(")D");
builder.append(" WHERE 1=1 ");
params.put("jdjxz", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_NODE_FLOWSTATE_DOING));
} else {
- builder.append(" SELECT DISTINCT D.* FROM ( ");
- builder.append(" SELECT B.*,C.NAME,C.CONTACT_NAME,C.PRIORITY_ID,C.INFLUENCE_ID,C.SLA_ID,C.THIRD_CATEGORY_ID,C.TYPE_ID FROM WORKFLOW_NODE A,WORKFLOW_BASE B,SC_WORKFLOW_INCIDENT C WHERE A.FLOWID = B.ID AND B.BUSINESS_ID = C.ID AND A.CURRENT_DEALER_ID = :userId AND A.FLOWSTATE = :jdywc ");
+ builder.append(" SELECT DISTINCT ID, WFNAME, BUSINESSTYPE, WFSTATE, WORKFLOWTYPE, CREATERNAME, CREATERID, CREATETIME, ENDTIME, MAINFLOWID, CURRENT_NODE_NAME, CURRENT_NODE_ID, DEAL_TYPE, CURRENT_DEALER_ID, CURRENT_DEALER_NAME, CURRENT_DEAL_ROLEIDA, CURRENT_DEAL_ROLENAME, WF_TEMPLATE_ID, BUSINESS_ID, ORDER_CODE, CUSTOMER_ID, CUSTOMER_NAME, PROJECT_ID, PROJECT_NAME, SUB_CUSTOMER_NAME, SUB_CUSTOMER_ID, NAME, CONTACT_NAME, PRIORITY_ID, INFLUENCE_ID, SLA_ID, THIRD_CATEGORY_ID, TYPE_ID FROM ( ");
+ builder.append(" SELECT B.ID, B.WFNAME, B.BUSINESSTYPE, B.WFSTATE, B.WORKFLOWTYPE, B.CREATERNAME, B.CREATERID, B.CREATETIME, B.ENDTIME, B.MAINFLOWID, B.CURRENT_NODE_NAME, B.CURRENT_NODE_ID, B.DEAL_TYPE, B.CURRENT_DEALER_ID, B.CURRENT_DEALER_NAME, B.CURRENT_DEAL_ROLEIDA, B.CURRENT_DEAL_ROLENAME, B.WF_TEMPLATE_ID, B.BUSINESS_ID, B.ORDER_CODE, B.NOTE, B.CUSTOMER_ID, B.CUSTOMER_NAME, B.PROJECT_ID, B.PROJECT_NAME, B.SUB_CUSTOMER_NAME, B.SUB_CUSTOMER_ID,C.NAME,C.CONTACT_NAME,C.PRIORITY_ID,C.INFLUENCE_ID,C.SLA_ID,C.THIRD_CATEGORY_ID,C.TYPE_ID FROM WORKFLOW_NODE A,WORKFLOW_BASE B,SC_WORKFLOW_INCIDENT C WHERE A.FLOWID = B.ID AND B.BUSINESS_ID = C.ID AND A.CURRENT_DEALER_ID = :userId AND A.FLOWSTATE = :jdywc ");
builder.append(")D");
builder.append(" WHERE 1=1 ");
params.put("jdywc", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_NODE_FLOWSTATE_FINISH));
}
} else {
- builder.append(" SELECT DISTINCT D.* FROM ( ");
+ builder.append(" SELECT DISTINCT ID, WFNAME, BUSINESSTYPE, WFSTATE, WORKFLOWTYPE, CREATERNAME, CREATERID, CREATETIME, ENDTIME, MAINFLOWID, CURRENT_NODE_NAME, CURRENT_NODE_ID, DEAL_TYPE, CURRENT_DEALER_ID, CURRENT_DEALER_NAME, CURRENT_DEAL_ROLEIDA, CURRENT_DEAL_ROLENAME, WF_TEMPLATE_ID, BUSINESS_ID, ORDER_CODE, CUSTOMER_ID, CUSTOMER_NAME, PROJECT_ID, PROJECT_NAME, SUB_CUSTOMER_NAME, SUB_CUSTOMER_ID, NAME, CONTACT_NAME, PRIORITY_ID, INFLUENCE_ID, SLA_ID, THIRD_CATEGORY_ID, TYPE_ID FROM ( ");
//鏌ヨ澶勭悊浜烘槸鎴戠殑
- builder.append(" SELECT B.*,C.NAME,C.CONTACT_NAME,C.PRIORITY_ID,C.INFLUENCE_ID,C.SLA_ID,C.THIRD_CATEGORY_ID,C.TYPE_ID FROM WORKFLOW_NODE A,WORKFLOW_BASE B,SC_WORKFLOW_INCIDENT C WHERE A.FLOWID = B.ID AND B.BUSINESS_ID = C.ID AND A.CURRENT_DEALER_ID = :userId ");
+ builder.append(" SELECT B.ID, B.WFNAME, B.BUSINESSTYPE, B.WFSTATE, B.WORKFLOWTYPE, B.CREATERNAME, B.CREATERID, B.CREATETIME, B.ENDTIME, B.MAINFLOWID, B.CURRENT_NODE_NAME, B.CURRENT_NODE_ID, B.DEAL_TYPE, B.CURRENT_DEALER_ID, B.CURRENT_DEALER_NAME, B.CURRENT_DEAL_ROLEIDA, B.CURRENT_DEAL_ROLENAME, B.WF_TEMPLATE_ID, B.BUSINESS_ID, B.ORDER_CODE, B.NOTE, B.CUSTOMER_ID, B.CUSTOMER_NAME, B.PROJECT_ID, B.PROJECT_NAME, B.SUB_CUSTOMER_NAME, B.SUB_CUSTOMER_ID,C.NAME,C.CONTACT_NAME,C.PRIORITY_ID,C.INFLUENCE_ID,C.SLA_ID,C.THIRD_CATEGORY_ID,C.TYPE_ID FROM WORKFLOW_NODE A,WORKFLOW_BASE B,SC_WORKFLOW_INCIDENT C WHERE A.FLOWID = B.ID AND B.BUSINESS_ID = C.ID AND A.CURRENT_DEALER_ID = :userId ");
builder.append(")D");
builder.append(" WHERE D.WFSTATE != :ysc ");
}
@@ -2339,7 +2339,7 @@
public int queryincidentEndCount(Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB ");
+ sql.append("SELECT COUNT(D.ID) FROM (SELECT B.ID FROM SC_WORKFLOW_INCIDENT 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);
@@ -2353,7 +2353,8 @@
public PageInfo queryincidentEndList(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("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("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.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_INCIDENT B LEFT JOIN HANG_UP_INFO D ON B.ID = D.BUS_ID ");
@@ -2362,7 +2363,7 @@
sqlpart = getEndSql(sqlpart, params);
sql.append(sqlpart);
- sql.append(" GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
+ sql.append(" GROUP BY WB.ID,B.RESOLVE_TYPE_NAME, B.CREATE_TIME, B.CREATE_USER_NAME, WB.ORDER_CODE, WB.WFNAME, WB.BUSINESSTYPE, WB.CREATERNAME, WB.CREATETIME, WB.ENDTIME , 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 , D.HANG_UP_USERNAME, D.HANG_UP_TIME, D.HANG_UP_REASON , WB.SCORE ORDER BY WB.CREATETIME DESC ");
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
}
@@ -2479,7 +2480,7 @@
public int queryincidentJxzCount(Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB ");
+ sql.append("SELECT COUNT(D.ID) FROM (SELECT B.ID FROM SC_WORKFLOW_INCIDENT 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);
@@ -2493,14 +2494,14 @@
public PageInfo queryincidentJxzList(PageInfo pageInfo, Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT WB.* ,B.STATE ,B.NAME ");
+ sql.append("SELECT WB.ID,WB.WFNAME,WB.BUSINESSTYPE,WB.WFSTATE,WB.WORKFLOWTYPE,WB.CREATERNAME,WB.CREATERID,WB.CREATETIME,WB.ENDTIME,WB.MAINFLOWID,WB.CURRENT_NODE_NAME,WB.CURRENT_NODE_ID,WB.DEAL_TYPE,WB.CURRENT_DEALER_ID,WB.CURRENT_DEALER_NAME,WB.CURRENT_DEAL_ROLEIDA,WB.CURRENT_DEAL_ROLENAME,WB.WF_TEMPLATE_ID,WB.BUSINESS_ID,WB.ORDER_CODE,WB.CUSTOMER_ID,WB.CUSTOMER_NAME,WB.PROJECT_ID,WB.PROJECT_NAME,WB.SUB_CUSTOMER_NAME,WB.SUB_CUSTOMER_ID ,B.STATE ,B.NAME ");
sql.append("FROM SC_WORKFLOW_INCIDENT 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_INCIDENT));
sqlpart = getJxzSql(sqlpart, params);
sql.append(sqlpart);
- sql.append(" GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
+ sql.append(" GROUP BY WB.ID,WB.WFNAME,WB.BUSINESSTYPE,WB.WFSTATE,WB.WORKFLOWTYPE,WB.CREATERNAME,WB.CREATERID,WB.CREATETIME,WB.ENDTIME,WB.MAINFLOWID,WB.CURRENT_NODE_NAME,WB.CURRENT_NODE_ID,WB.DEAL_TYPE,WB.CURRENT_DEALER_ID,WB.CURRENT_DEALER_NAME,WB.CURRENT_DEAL_ROLEIDA,WB.CURRENT_DEAL_ROLENAME,WB.WF_TEMPLATE_ID,WB.BUSINESS_ID,WB.ORDER_CODE,WB.CUSTOMER_ID,WB.CUSTOMER_NAME,WB.PROJECT_ID,WB.PROJECT_NAME,WB.SUB_CUSTOMER_NAME,WB.SUB_CUSTOMER_ID ,B.STATE ,B.NAME ORDER BY WB.CREATETIME DESC ");
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
}
@@ -2717,15 +2718,15 @@
StringBuffer sql = new StringBuffer();
Map paramMap = new HashMap();
- selectSql.append("SELECT COUNT(*) AS NUM,D.STATE FROM (SELECT A.* FROM SC_WORKFLOW_INCIDENT 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_INCIDENT 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("SELECT COUNT(*) AS NUM,D.STATE FROM (SELECT A.ID, A.STATE FROM SC_WORKFLOW_INCIDENT 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.ID FROM SC_WORKFLOW_INCIDENT 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 ");
String customer_Id = request.getParameter("customerId");
if (!StringUtil.isEmpty(customer_Id)) {
selectSql.append(" AND A.CUSTOMER_ID = :customer_Id ");
sql.append(" AND A.CUSTOMER_ID = :customer_Id ");
paramMap.put("customer_Id", customer_Id);
}
- selectSql.append(" GROUP BY A.ID) D GROUP BY D.STATE ");
+ selectSql.append(" GROUP BY A.ID, A.STATE) D GROUP BY D.STATE ");
sql.append(" GROUP BY A.ID) D ");
List<Map> list = baseDao.queryForList(selectSql.toString(), paramMap);
@@ -2773,7 +2774,7 @@
public int queryincidentOrderNodeCount(Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
+ sql.append("SELECT COUNT(D.ID) FROM (SELECT B.ID FROM SC_WORKFLOW_INCIDENT 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 = getOrderNodeSql(sqlpart, params);
sql.append(sqlpart);
@@ -2787,14 +2788,14 @@
public PageInfo queryincidentOrderNodeList(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("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,");
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_INCIDENT 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_INCIDENT));
sqlpart = getOrderNodeSql(sqlpart, params);
sql.append(sqlpart);
- sql.append(" GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
+ sql.append(" GROUP BY WB.ID,WB.ORDER_CODE, WB.WFNAME, WB.BUSINESSTYPE, WB.CREATERNAME, WB.CREATETIME, 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 ORDER BY WB.CREATETIME DESC ");
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
}
@@ -2915,7 +2916,7 @@
public int queryincidentTimeoutCount(Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB ");
+ sql.append("SELECT COUNT(D.ID) FROM (SELECT B.ID FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB ");
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND WB.BUSINESSTYPE = :businessType AND WB.WFSTATE = 1 AND (B.ANSWER_TIMEOUT = 1 OR B.DEAL_TIMEOUT = 1 ) ");
sqlpart = getincidentTimeOutSql(sqlpart, params);
@@ -2930,14 +2931,14 @@
public PageInfo queryincidentTimeoutList(PageInfo pageInfo, Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT WB.*,B.STATE ,B.NAME ");
+ sql.append("SELECT WB.ID,WB.WFNAME,WB.BUSINESSTYPE,WB.WFSTATE,WB.WORKFLOWTYPE,WB.CREATERNAME,WB.CREATERID,WB.CREATETIME,WB.ENDTIME,WB.MAINFLOWID,WB.CURRENT_NODE_NAME,WB.CURRENT_NODE_ID,WB.DEAL_TYPE,WB.CURRENT_DEALER_ID,WB.CURRENT_DEALER_NAME,WB.CURRENT_DEAL_ROLEIDA,WB.CURRENT_DEAL_ROLENAME,WB.WF_TEMPLATE_ID,WB.BUSINESS_ID,WB.ORDER_CODE,WB.CUSTOMER_ID,WB.CUSTOMER_NAME,WB.PROJECT_ID,WB.PROJECT_NAME,WB.SUB_CUSTOMER_NAME,WB.SUB_CUSTOMER_ID,B.STATE ,B.NAME ");
sql.append("FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB ");
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND WB.BUSINESSTYPE = :businessType AND WB.WFSTATE = 1 AND (B.ANSWER_TIMEOUT = 1 OR B.DEAL_TIMEOUT = 1 ) ");
params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT));
sqlpart = getincidentTimeOutSql(sqlpart, params);
sql.append(sqlpart);
- sql.append(" GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
+ sql.append(" GROUP BY WB.ID,WB.WFNAME,WB.BUSINESSTYPE,WB.WFSTATE,WB.WORKFLOWTYPE,WB.CREATERNAME,WB.CREATERID,WB.CREATETIME,WB.ENDTIME,WB.MAINFLOWID,WB.CURRENT_NODE_NAME,WB.CURRENT_NODE_ID,WB.DEAL_TYPE,WB.CURRENT_DEALER_ID,WB.CURRENT_DEALER_NAME,WB.CURRENT_DEAL_ROLEIDA,WB.CURRENT_DEAL_ROLENAME,WB.WF_TEMPLATE_ID,WB.BUSINESS_ID,WB.ORDER_CODE,WB.CUSTOMER_ID,WB.CUSTOMER_NAME,WB.PROJECT_ID,WB.PROJECT_NAME,WB.SUB_CUSTOMER_NAME,WB.SUB_CUSTOMER_ID,B.STATE ,B.NAME ORDER BY WB.CREATETIME DESC ");
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
}
@@ -3079,7 +3080,7 @@
paramMap.put("wfstate", Constants.WORKFLOW_BASE_WFSTATE_DELETE);
//鏌ヨ璇ヤ汉鍛樻墍灞炵殑椤圭洰
sql.append("SELECT COUNT(*) AS NUM,E.STATE FROM SC_WORKFLOW_INCIDENT E,WORKFLOW_BASE D,((SELECT B.ID FROM AC_ROLE B WHERE B.STATE = 1) UNION ALL (SELECT DISTINCT A.ID FROM AC_ROLE_GROUP A WHERE A.STATE = 1) ");
- sqlall.append("SELECT COUNT(F.ID) FROM (SELECT E.* FROM SC_WORKFLOW_INCIDENT E,WORKFLOW_BASE D,((SELECT B.ID FROM AC_ROLE B WHERE B.STATE = 1) UNION ALL (SELECT DISTINCT A.ID FROM AC_ROLE_GROUP A WHERE A.STATE = 1) ");
+ sqlall.append("SELECT COUNT(F.ID) FROM (SELECT E.ID FROM SC_WORKFLOW_INCIDENT E,WORKFLOW_BASE D,((SELECT B.ID FROM AC_ROLE B WHERE B.STATE = 1) UNION ALL (SELECT DISTINCT A.ID FROM AC_ROLE_GROUP A WHERE A.STATE = 1) ");
sql.append(") C WHERE E.ID = D.BUSINESS_ID AND D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
@@ -3137,7 +3138,7 @@
public int querymyincidentEndCount(Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
+ sql.append("SELECT COUNT(D.ID) FROM (SELECT B.ID FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND C.FLOWID = WB.ID AND C.CURRENT_DEALER_ID = :userId AND WB.BUSINESSTYPE = :businessType ");
sqlpart.append(" AND WB.WFSTATE = :state ");
@@ -3154,7 +3155,8 @@
public PageInfo querymyincidentEndList(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("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.ENDTIME , ");
+ 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.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_INCIDENT B LEFT JOIN HANG_UP_INFO D ON B.ID = D.BUS_ID ");
@@ -3164,7 +3166,7 @@
sqlpart.append(" AND WB.WFSTATE = :state ");
params.put("state", params.get("type"));
sql.append(sqlpart);
- sql.append(" GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
+ sql.append(" GROUP BY WB.ID, B.RESOLVE_TYPE_NAME, B.CREATE_TIME, B.CREATE_USER_NAME, WB.ORDER_CODE, WB.WFNAME, WB.BUSINESSTYPE, WB.CREATERNAME, WB.CREATETIME , WB.ENDTIME , 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, D.HANG_UP_USERNAME, D.HANG_UP_TIME, D.HANG_UP_REASON , WB.SCORE ORDER BY WB.CREATETIME DESC ");
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
}
@@ -3303,8 +3305,8 @@
Map paramMap = new HashMap();
paramMap.put("flowstate", type);
paramMap.put("userId", params.get("userId"));
- selectSql.append("SELECT COUNT(*) AS NUM,D.STATE FROM (SELECT A.* FROM SC_WORKFLOW_INCIDENT A,WORKFLOW_BASE B , WORKFLOW_NODE C WHERE B.ID = C.FLOWID AND C.FLOWSTATE = :flowstate AND C.CURRENT_DEALER_ID = :userId AND A.ID = B.BUSINESS_ID ");
- sql.append("SELECT COUNT(D.ID) FROM (SELECT A.* FROM SC_WORKFLOW_INCIDENT A,WORKFLOW_BASE B , WORKFLOW_NODE C WHERE B.ID = C.FLOWID AND C.FLOWSTATE = :flowstate AND C.CURRENT_DEALER_ID = :userId AND A.ID = B.BUSINESS_ID ");
+ selectSql.append("SELECT COUNT(*) AS NUM,D.STATE FROM (SELECT A.ID,A.STATE FROM SC_WORKFLOW_INCIDENT A,WORKFLOW_BASE B , WORKFLOW_NODE C WHERE B.ID = C.FLOWID AND C.FLOWSTATE = :flowstate AND C.CURRENT_DEALER_ID = :userId AND A.ID = B.BUSINESS_ID ");
+ sql.append("SELECT COUNT(D.ID) FROM (SELECT A.ID FROM SC_WORKFLOW_INCIDENT A,WORKFLOW_BASE B , WORKFLOW_NODE C WHERE B.ID = C.FLOWID AND C.FLOWSTATE = :flowstate AND C.CURRENT_DEALER_ID = :userId AND A.ID = B.BUSINESS_ID ");
String customer_Id = params.get("customerId");
if (!StringUtil.isEmpty(customer_Id)) {
@@ -3314,7 +3316,7 @@
}
- selectSql.append(" GROUP BY A.ID) D GROUP BY D.STATE ");
+ selectSql.append(" GROUP BY A.ID,A.STATE) D GROUP BY D.STATE ");
sql.append(" GROUP BY A.ID) D ");
List<Map> list = baseDao.queryForList(selectSql.toString(), paramMap);
@@ -3361,7 +3363,7 @@
public int querymyincidentOrderNodeCount(Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
+ sql.append("SELECT COUNT(D.ID) FROM (SELECT B.ID FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND C.FLOWID = WB.ID AND C.FLOWSTATE = :flowstate AND C.CURRENT_DEALER_ID = :userId AND WB.BUSINESSTYPE = :businessType ");
params.put("flowstate", params.get("type"));
sqlpart = getmyodernodeSql(sqlpart, params);
@@ -3376,7 +3378,7 @@
public PageInfo querymyincidentOrderNodeList(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 ,C.FLOWSTATE, ");
+ sql.append("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,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_INCIDENT B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND C.FLOWID = WB.ID AND C.FLOWSTATE = :flowstate AND C.CURRENT_DEALER_ID = :userId AND WB.BUSINESSTYPE = :businessType ");
@@ -3386,7 +3388,7 @@
sqlpart = getmyodernodeSql(sqlpart, params);
sql.append(sqlpart);
- sql.append(" GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
+ sql.append(" GROUP BY WB.ID, WB.ORDER_CODE, WB.WFNAME, WB.BUSINESSTYPE, WB.CREATERNAME, WB.CREATETIME, C.FLOWSTATE, 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 ORDER BY WB.CREATETIME DESC ");
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
}
@@ -3511,7 +3513,7 @@
public int querymyincidentTimeoutCount(Map<String, String> params) {
StringBuilder sql = new StringBuilder();
StringBuilder sqlpart = new StringBuilder();
- sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB ");
+ sql.append("SELECT COUNT(D.ID) FROM (SELECT B.ID FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB ");
sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND WB.BUSINESSTYPE = :businessType AND WB.WFSTATE = 1 AND (B.ANSWER_TIMEOUT = 1 OR B.DEAL_TIMEOUT = 1 ) AND WB.CURRENT_DEALER_ID = :current_dealer_id ");
params.put("current_dealer_id", params.get("userId"));
@@ -3638,7 +3640,8 @@
public PageInfo querymyincidentTimeoutList(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("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME, ");
+// 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_INCIDENT B,WORKFLOW_BASE WB WHERE WB.BUSINESS_ID = B.ID ");
sqlpart.append(" AND WB.BUSINESSTYPE = :businessType AND WB.WFSTATE = 1 AND (B.ANSWER_TIMEOUT = 1 OR B.DEAL_TIMEOUT = 1 ) AND WB.CURRENT_DEALER_ID = :current_dealer_id ");
@@ -3758,7 +3761,7 @@
}
sql.append(sqlpart);
- sql.append(" GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
+ sql.append(" GROUP BY WB.ID, WB.ORDER_CODE, WB.WFNAME, WB.BUSINESSTYPE, WB.CREATERNAME, 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,WB.CREATETIME ORDER BY WB.CREATETIME DESC ");
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
}
@@ -5498,12 +5501,12 @@
params.put("endTime", endDay + "600000");
if (StringUtil.isBlank(userId)) {
builder.append(" select count(ID) NUM,DATE_FORMAT(CREATE_TIME,'%Y%m%d') CREATEDAY from SC_WORKFLOW_INCIDENT where CUSTOMER_ID = :cusId ");
- builder.append(" and CREATE_TIME>=:startTime and CREATE_TIME<=:endTime group by CREATEDAY ");
+ builder.append(" and CREATE_TIME>=:startTime and CREATE_TIME<=:endTime group by DATE_FORMAT(CREATE_TIME,'%Y%m%d') ");
} else {
builder.append(" select count(q.ID) NUM,DATE_FORMAT(q.CREATE_TIME,'%Y%m%d') CREATEDAY from workflow_base b ,SC_WORKFLOW_INCIDENT 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 ");
+ builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime group by DATE_FORMAT(q.CREATE_TIME,'%Y%m%d') ");
}
List<Map> dataList = baseDao.queryForList(builder.toString(), params);
@@ -5642,12 +5645,12 @@
StringBuilder builder = new StringBuilder();
if (StringUtil.isBlank(userId)) {
builder.append(" select count(ID) NUM,THIRD_CATEGORY_ID SERVER_ID from SC_WORKFLOW_INCIDENT where CUSTOMER_ID = :cusId ");
- builder.append(" and CREATE_TIME>=:startTime and CREATE_TIME<=:endTime group by SERVER_ID ");
+ builder.append(" and CREATE_TIME>=:startTime and CREATE_TIME<=:endTime group by THIRD_CATEGORY_ID ");
} else {
builder.append(" select count(q.ID) NUM,q.THIRD_CATEGORY_ID SERVER_ID from SC_WORKFLOW_INCIDENT 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 ");
+ builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime group by q.THIRD_CATEGORY_ID ");
}
List<Map> dataList = baseDao.queryForList(builder.toString(), params);
@@ -5784,9 +5787,9 @@
}
}
}
-
+
return map;
-
+
}
@Override
--
Gitblit v1.9.1