客服工作臺

public Page<Record> customerDeskList(QhyfController controller) throws Exception {//獲取分頁信息PageInfo pageInfo = controller.getPageInfo();int pageNumber = pageInfo.getPageIndex();int pageSize = pageInfo.getPageSize();List<String> paras = new ArrayList<String>();//獲取查詢條件--核心企業String coreEnterpriseId = controller.getPara("coreEnterpriseId");//獲取查詢條件--供應商名稱String supplierName = controller.getPara("supplierName");//獲取查詢條件--是否已完成String completeStatus = controller.getPara("completeStatus");// 獲取當前登錄用戶uuidString userUuid = controller.getCurrentUser().get(BlConstant.FIELD_USER_ID);// 辦理事項String matterId = controller.getPara("matterId");// 付款確認書編號String payId = controller.getPara("payId");// 區域String coreRegionId = controller.getPara("regionId");// 保理申請開始日期String startTransferApplyDate = controller.getPara("startTransferApplyDate");// 保理申請結束日期String endTransferApplyDate = controller.getPara("endTransferApplyDate");// 當登錄用戶不是客服主管時，添加分配客服id條件String csWhere = "";if (!isCsUser(userUuid)) {csWhere = String.format(" AND a.cs_user_id = %s ", "'"+ userUuid+ "'");}// 定義總查詢條件String where = "";// 定義子查詢條件String aubQueryWhere = "";// 定義子查詢條件String queryWhere = "";// 定義子查詢參數ListList<String> aubQueryParas = new ArrayList<String>();// 查詢條件:是否已完成、辦理事項都不為空時，查詢該辦理事項 存在未完成/已完成 的供應商if(StringUtils.notBlank(completeStatus) && StringUtils.notBlank(matterId)){aubQueryWhere += " AND b.matter_id = ? and b.complete_status = ? ";aubQueryParas.add(matterId);aubQueryParas.add(completeStatus);}// 查詢條件:是否已完成不為空、辦理事項為空時，查詢該供應商對應的所有任務是否完成else if(StringUtils.notBlank(completeStatus) && StringUtils.isBlank(matterId)){if ("1".equals(completeStatus)) {where += " AND x.complete = x.total ";} else if ("0".equals(completeStatus)) {where += " AND x.complete <> x.total ";}}// 查詢條件:是否已完成為空、辦理事項不為空時，查詢包含有該任務的所有供應商else if(StringUtils.isBlank(completeStatus) && StringUtils.notBlank(matterId)){aubQueryWhere += " AND b.matter_id = ? ";aubQueryParas.add(matterId);}//查詢條件--付款確認書編號不為空時if(StringUtils.notBlank(payId)){// 調用共通轉義payId = this.strEscape(payId);aubQueryWhere += " AND c.pay_id LIKE ? ";aubQueryParas.add("%" + payId + "%");}//查詢條件--區域不為空時if(StringUtils.notBlank(coreRegionId)){String coreRegionIds[] = coreRegionId.split("-");String regionId = coreRegionIds[1];aubQueryWhere += " AND d.region_id = ? ";aubQueryParas.add(regionId);}//查詢條件--保理申請開始日期不為空時if(StringUtils.notBlank(startTransferApplyDate)){aubQueryWhere += " AND DATE_FORMAT(c.transfer_apply_date,'%Y-%m-%d') >= ? ";aubQueryParas.add(startTransferApplyDate);}//查詢條件--保理申請結束日期不為空時if(StringUtils.notBlank(endTransferApplyDate)){aubQueryWhere += " AND DATE_FORMAT(c.transfer_apply_date,'%Y-%m-%d') <= ? ";aubQueryParas.add(endTransferApplyDate);}// 子查詢條件不為空時if (StringUtils.notBlank(aubQueryWhere)){// 定義查詢SQL, 查詢出符合子查詢條件的供應商及對應的核心企業String querySQL  = String.format(""+ "select distinct "+ "       a.suppliers_id, "+ "       a.core_enterprise_id "+ "  from biz_business_task_allocate a, "+ "       biz_cs_task_info b "+ "       left join biz_pay_comfirm_info c "+ "         on c.sys_status = 1 "+ "            and c.uuid = b.biz_id "+ "       left join biz_item_company d "+ "         on d.uuid = c.item_company_id "+ "            and d.sys_status = 1 "+ " where a.sys_status = 1 "+ "   and b.sys_status = 1 "+ "   and a.biz_id = b.biz_id %s %s ", aubQueryWhere, csWhere);// 執行子查詢SQL, 獲取符合子查詢條件的供應商及對應的核心企業// add by wj 從讀庫中獲取數據 20181114 startList<Record> aubQueryRecordList = Db.use(BlConstant.READ_ONLY).find(querySQL, aubQueryParas.toArray());// add by wj 從讀庫中獲取數據 20181114 end// 循環子查詢結果，構造查詢條件for(int i = 0 ; i< aubQueryRecordList.size(); i++){// 結果只有一條數據時if(aubQueryRecordList.size() == 1){where += "AND ( x.suppliers_id = ? AND x.core_enterprise_id = ? )";paras.add(aubQueryRecordList.get(i).getStr("suppliersId"));paras.add(aubQueryRecordList.get(i).getStr("coreEnterpriseId"));queryWhere += "AND ( a.suppliers_id = '" + aubQueryRecordList.get(i).getStr("suppliersId") + "' AND a.core_enterprise_id = '" + aubQueryRecordList.get(i).getStr("coreEnterpriseId") + "' )";// 結果不止一條數據時}else{// 第一條數據if(i == 0){where += "AND ( (x.suppliers_id = ? AND x.core_enterprise_id = ?) OR ";paras.add(aubQueryRecordList.get(i).getStr("suppliersId"));paras.add(aubQueryRecordList.get(i).getStr("coreEnterpriseId"));queryWhere += "AND ( (a.suppliers_id = '" + aubQueryRecordList.get(i).getStr("suppliersId") + "' AND a.core_enterprise_id = '" + aubQueryRecordList.get(i).getStr("coreEnterpriseId") + "') OR ";}else if(i<aubQueryRecordList.size() - 1){where += " (x.suppliers_id = ? AND x.core_enterprise_id = ?) OR ";paras.add(aubQueryRecordList.get(i).getStr("suppliersId"));paras.add(aubQueryRecordList.get(i).getStr("coreEnterpriseId"));queryWhere += " (a.suppliers_id = '" + aubQueryRecordList.get(i).getStr("suppliersId") + "' AND a.core_enterprise_id = '" + aubQueryRecordList.get(i).getStr("coreEnterpriseId") + "') OR ";}// 最后一條數據else if(i == aubQueryRecordList.size() - 1){where += " (x.suppliers_id = ? AND x.core_enterprise_id = ?))";paras.add(aubQueryRecordList.get(i).getStr("suppliersId"));paras.add(aubQueryRecordList.get(i).getStr("coreEnterpriseId"));queryWhere += " (a.suppliers_id = '" + aubQueryRecordList.get(i).getStr("suppliersId") + "' AND a.core_enterprise_id = '" + aubQueryRecordList.get(i).getStr("coreEnterpriseId") + "'))";}}}}//查詢條件--核心企業不為空時if(StringUtils.notBlank(coreEnterpriseId)){where += " AND x.core_enterprise_id = ? ";paras.add(coreEnterpriseId);if(!StringUtils.notBlank(aubQueryWhere)){queryWhere += " AND a.core_enterprise_id = '" + coreEnterpriseId + "' ";}}//查詢條件--供應商名稱不為空時if(StringUtils.notBlank(supplierName)){// 調用共通轉義supplierName = this.strEscape(supplierName);where += " AND x.suppliers_name LIKE ? ";paras.add("%" + supplierName + "%");if(!StringUtils.notBlank(aubQueryWhere)){queryWhere += " AND d.suppliers_name LIKE '%" + supplierName + "%' ";}}// selectSqlString selectSql = " "+ " SELECT "+ "   x.core_enterprise_name, "+ "   x.suppliers_name, "+ "   x.complete, "+ "   x.total, "+ "   x.complete_status, "+ "   x.items_num, "+ "   x.suppliers_id, "+ "   x.core_enterprise_id ";// fromSqlString fromSql = String.format(""+ " FROM ("+ "        SELECT "+ "            y.core_enterprise_name, "+ "            y.suppliers_name, "+ "            y.suppliers_id, "+ "            y.core_enterprise_id, "+ "            SUM(y.complete_status = 1) AS complete, "+ "            COUNT(y.complete_status) AS total, "+ "            IF (SUM(y.complete_status = 1) = COUNT(y.complete_status), '已完成', '未完成') AS complete_status, "+ "            CONCAT_WS('/',CAST(    SUM(y.complete_status = 1) AS CHAR), CAST(COUNT(y.complete_status) AS CHAR) ) AS items_num "+ "        FROM "+ "            ( "+ "                SELECT "+ "                    c.core_enterprise_name, "+ "                    d.suppliers_name, "+ "                    b.complete_status, "+ "                    a.suppliers_id, "+ "                    a.core_enterprise_id "+ "                FROM "+ "                    biz_business_task_allocate a, "+ "                    biz_core_enterprise c, "+ "                    biz_suppliers d, "+ "                    biz_cs_task_info b "+ "                WHERE "+ "                    a.sys_status = 1 "+ "                    AND b.sys_status = 1 "+ "                    AND b.biz_type = 'F' "+ "                    AND a.biz_type = 'F' "+ "                    AND c.sys_status = 1 "+ "                    AND d.sys_status = 1 "+ "                    AND a.biz_id = b.biz_id "+ "                    AND a.core_enterprise_id = c.uuid "+ "                    AND a.suppliers_id = d.uuid "+ "                    AND a.biz_id NOT IN ( "+ "                                        SELECT "+ "                                            f.pay_id "+ "                                        FROM "+ "                                            biz_rollback_pay f "+ "                                        WHERE "+ "                                             f.`sys_status` = 1 "+ "                                            AND f.`biz_state` <> 15 "+ "                                        ) %s %s"+ "         UNION ALL "+ "                SELECT "+ "                    c.core_enterprise_name, "+ "                    d.suppliers_name, "+ "                    b.complete_status, "+ "                    a.suppliers_id, "+ "                    a.core_enterprise_id "+ "                FROM "+ "                    biz_business_task_allocate a, "+ "                    biz_core_enterprise c, "+ "                    biz_suppliers d, "+ "                    biz_cs_task_info b "+ "                WHERE "+ "                    a.sys_status = 1 "+ "                    AND b.sys_status = 1 "+ "                    AND c.sys_status = 1 "+ "                    AND d.sys_status = 1 "+ "                    AND b.biz_type = 'S' "+ "                    AND a.biz_type = 'S' "+ "                    AND a.biz_id = b.biz_id "+ "                    AND a.core_enterprise_id = c.uuid "+ "                    AND a.suppliers_id = d.uuid %s %s"+ "            ) y "+ "            GROUP BY y.suppliers_id,y.core_enterprise_id "+ "        ) x " + "    WHERE 1=1  %s "+ " ORDER BY x.suppliers_name DESC, x.core_enterprise_id DESC ", csWhere, queryWhere,csWhere, queryWhere, where);// 執行SQL，將獲取的數據返回前端// add by wj 從讀庫中獲取數據 20181114 startPage<Record> pageRecords = Db.use(BlConstant.READ_ONLY).paginate(pageNumber, pageSize, selectSql, fromSql, paras.toArray());// add by wj 從讀庫中獲取數據 20181114 end// 客服工作臺增加一列，顯示此供應商最近一次首次簽約（包含續簽）的流程狀態（包括線上、線下）。   if (pageRecords.getList() != null && pageRecords.getList().size() > 0) {for (Record customerDeskInfo : pageRecords.getList()) {// 準入流程最新流程狀態String instanceLastActivity = "";// 獲取供應商IDString suppliersId = customerDeskInfo.getStr("suppliersId");// 獲取核心企業IDString coreEnterpriseUuid = customerDeskInfo.getStr("coreEnterpriseId");// 以供應商ID、核心企業ID為條件，獲取該供應商最新的簽約信息Record firstContractInfo = getFirstContractInfo(suppliersId, coreEnterpriseUuid);// 簽約信息不為空時if(firstContractInfo != null){// 獲取首次簽約IDString firstContractId = firstContractInfo.getStr("uuid");// 根據首次簽約ID, 獲取最新準入接收IDString admittanceReceptionId = getAdmittanceReceptionId(firstContractId);// 準入接收ID不為空，則存在準入線下流程信息if(StringUtils.notBlank(admittanceReceptionId)){// 根據接收ID，獲取最新準入線下流程信息Record instanceLastActivityInfo = getInstanceLastActivityInfo(admittanceReceptionId);if(instanceLastActivityInfo != null){// 流程最新活動名稱String activityName = instanceLastActivityInfo.getStr("activityName");if("0".equals(firstContractInfo.getStr("renewFlag"))){// 不是續簽時instanceLastActivity = "首次簽約-" + activityName + "-" + instanceLastActivityInfo.getStr("status");}else{// 是續簽時instanceLastActivity = "第"+ firstContractInfo.getInt("renewCount") + "次續簽-" + activityName + "-" + instanceLastActivityInfo.getStr("status") ;}// 當流程風控審核已經完成時, 判斷資料是否齊全if("風控審核".equals(activityName) && "已完成".equals(instanceLastActivityInfo.getStr("status"))){// 判斷準入資料是否接收齊全boolean isCompleteFlag = isAdmittanceReceptionComplete(firstContractId);// 資料齊全時if(isCompleteFlag){instanceLastActivity = instanceLastActivity + "-資料齊全";}else{instanceLastActivity = instanceLastActivity + "-資料未齊全";}}}}// 準入接收ID為空，則只有準入線上流程信息else{// 根據首次簽約ID，獲取最新準入線上流程信息Record instanceLastActivityInfo = getInstanceLastActivityInfo(firstContractId);if(instanceLastActivityInfo != null){// 流程最新活動名稱String activityName = instanceLastActivityInfo.getStr("activityName");// 判斷是否續簽if("0".equals(firstContractInfo.getStr("renewFlag"))){// 不是續簽時instanceLastActivity = "首次簽約-" + activityName + "-" + instanceLastActivityInfo.getStr("status");}else{// 是續簽時instanceLastActivity = "第"+ firstContractInfo.getInt("renewCount") + "次續簽-" + activityName + "-" + instanceLastActivityInfo.getStr("status") ;}}}}// 準入流程最新流程狀態，添加進客服任務信息中customerDeskInfo.set("instanceLastActivity", instanceLastActivity);}}return pageRecords;}

?