派生自 larks_base_dm
src/cn/ksource/web/facade/incident/IncidentFacadeImpl.java
@@ -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);
