客服工作台

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;}