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");
// 获取当前登录用户uuid
String 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 = "";
// 定义子查询参数List
List<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 start
List<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 + "%' ";
}
}
// selectSql
String 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 ";
// fromSql
String 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 start
Page<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 = "";
// 获取供应商ID
String suppliersId = customerDeskInfo.getStr("suppliersId");
// 获取核心企业ID
String coreEnterpriseUuid = customerDeskInfo.getStr("coreEnterpriseId");
// 以供应商ID、核心企业ID为条件，获取该供应商最新的签约信息
Record firstContractInfo = getFirstContractInfo(suppliersId, coreEnterpriseUuid);
// 签约信息不为空时
if(firstContractInfo != null){
// 获取首次签约ID
String firstContractId = firstContractInfo.getStr("uuid");
// 根据首次签约ID, 获取最新准入接收ID
String 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;
}
