/**
* 音泰思计算机技术(成都)有限公司
* 2017年11月21日 17:55:18
*
*/
package com.qhyf.app.bl.service;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.core.PageInfo;
import com.qhyf.app.bl.BlConstant;
import com.qhyf.app.bl.base.controller.QhyfController;
import com.qhyf.app.bl.base.service.QhyfService;
import com.qhyf.app.bl.notify.cordys.model.BpmLastActivity;
import club.newepoch.cordysclient.api.PerformTaskApi;
import club.newepoch.cordysclient.exception.CordysException;
import club.newepoch.persistent.db.common.Db;
import club.newepoch.persistent.db.common.Page;
import club.newepoch.persistent.db.exception.ActiveRecordException;
import club.newepoch.persistent.db.pojo.Record;
import club.newepoch.utils.AssertUtils;
import club.newepoch.utils.DateUtils;
import club.newepoch.utils.StringUtils;
/**
* 单证任务调配管理Service
*
* @author tangzheng
*
*/
public class BizDocumentTaskDeployService extends QhyfService {
/**
* 获取单证任务调配管理信息列表
*
* @param ctr
* @return
* @throws ActiveRecordException
*/
public Page<Record> getTaskList(QhyfController ctr) throws ActiveRecordException, ParseException {
Page<Record> taskDeployPage = null;
List<Object> paras = new ArrayList<Object>();
// 获取分页信息
PageInfo pageInfo = ctr.getPageInfo();
AssertUtils.notNull(pageInfo, "分页信息不能为空");
// 设置排序
String order = ctr.getOrderString(pageInfo);
// 查询条件
String where = "";
// 获取姓名
String userName = ctr.getPara("userName");
// 不为空时添加查询条件
userName = strToEscape(userName);
if (StringUtils.notBlank(userName)) {
where += " and a.user_name like ? ";
paras.add("%" + userName + "%");
}
// 获取任务状态
String taskState = ctr.getPara("taskState");
// 不为空时添加查询条件
if (StringUtils.notBlank(taskState)) {
if (taskState.equals("1")) {
where += " and z.status = '已完成 ' ";
} else if (taskState.equals("0")) {
where += " and z.status = '已领取'";
}
}
// 获取在线状态 onlineState online_state
String onlineState = ctr.getPara("onlineState");
// int a = Integer.parseInt(onlineState);
// 不为空时添加查询条件
if (StringUtils.notBlank(onlineState)) {
where += " and b.online_state = ? ";
paras.add(onlineState);
}
// 获取日期-起
String startDate = ctr.getPara("startDate"); // 2018-08-03
// 获取日期-至
String endDate = ctr.getPara("endDate");// 2018-08-03
// 当无查询条件时，默认查询当前时间的
String climeDate = "";
String completeDate = "";
if (StringUtils.notBlank(startDate) && StringUtils.notBlank(endDate)) {
climeDate = " and " + DateUtils.parseDate(startDate).getTime() + "< a.claim_time and a.claim_time <"
+ DateUtils.parseDate(endDate).getTime();
completeDate = " and " + DateUtils.parseDate(startDate).getTime()
+ "< a.complete_time and a.complete_time <" + DateUtils.parseDate(endDate).getTime();
} else {
climeDate ="&& FROM_UNIXTIME(a.claim_time/1000,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')";
completeDate ="&& FROM_UNIXTIME(a.complete_time/1000,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')";
}
String selectSql = "SELECT "
+ " a.user_name, "
+ " a.user_id, "
+ " a.uuid, "
+ " b.group_id, "
+ " b.online_state, "
+ " z.task_user_uuid, "
+ " z.pay_priority, "
+ " z.claim_num, "
+ " z.complete_num, "
+ "( z.claim_num+z.complete_num) as total,"
+ " z. STATUS ";
String fromSql = String.format("FROM "
+ " sys_user a, "
+ " biz_document_user_info b, "
+ " ( " + " SELECT "
+ " b.user_uuid AS task_user_uuid, "
+ " a. STATUS, "
+ " COUNT( "
+ " CASE "
+ " WHEN d.pay_priority = 1 THEN "
+ " '急单个数' "
+ " END " + " ) AS pay_priority, "
+ " COUNT( " + " CASE "
+ " WHEN (a. STATUS = '已领取' %s) THEN "
+ " '已领取个数' " + " END "
+ " ) AS claim_num, "
+ " COUNT( " + " CASE "
+ " WHEN (a. STATUS = '已完成' %s) THEN "
+ " '已完成个数' " + " END "
+ " ) AS complete_num "
+ " FROM "
+ " bpm_activity a, "
+ " bpm_task_user b, "
+ " bpm_process_instance c, "
+ " biz_pay_comfirm_info d "
+ " WHERE "
+ " a.ACTIVITY_NAME IN ( "
+ " '单证线上初审', "
+ " '单证线上复审', "
+ " '单证线下纸质审核', "
+ " '单证审核纸质资料', "
+ " '单证线上审核', "
+ " '项目单证线下纸质审核' " + " ) "
+ " AND a.sys_status = b.sys_status = c.sys_status = d.sys_status = 1 "
+ "AND a.task_id = b.task_id "
+ "AND a.instance_id = c.instance_id "
+ "AND c.biz_uuid = d.uuid "
+ " GROUP BY "
+ " b.user_uuid " + ") z "
+ "WHERE " + " a.uuid = z.task_user_uuid "
+ "AND a.uuid = b.user_id "
+ "AND a.sys_status = 1 "
+ "AND b.sys_status = 1 "
+ "%s ", climeDate, completeDate, where);
taskDeployPage = Db.paginate(pageInfo.getPageIndex(), pageInfo.getPageSize(), selectSql, fromSql + order,
paras.toArray());
// 获取登录用户uuid u1038
String loginUserID = ctr.getCurrentUser().getStr(BlConstant.FIELD_USER_ID);
boolean isGroupLeader = false;
String groupId = null;
// 获取组长的id，和那条数据的uuid
String sql = "select uuid,group_leader_id from biz_document_group_info where sys_status = 1";
List<Record> list = Db.find(sql);
isGroup: for (Record r : list) {
// 查询出来的组长的id为多条，拆开一下
String[] strings = r.getStr("groupLeaderId").split(",");
// 便利数组
for (String s : strings) {
// 便利出来的 组长的id和当前用户的uuid一样的话，就确定为组长了
if (s.equals(loginUserID)) {
// 因为前段我么你只要一个true，false 的结果来判断按钮是否可用
// 所以申请了一个变量 ，来赋值 一下
isGroupLeader = true;
groupId = r.getStr("uuid");
break isGroup;
}
}
}
// 如果为true的话
if (isGroupLeader) {
// 便利 当前的这个 taskDeployPage.getList() 查询出来的所有的数据
for (Record record : taskDeployPage.getList()) {
// 如果查询出来的组的id和 组长的那条uuid相同的话，就设置一个标志位返回给前端能使用
if (record.getStr("groupId").equals(groupId)) { // 说明为同一个组的，当前用户为组长，其他人为组员
record.set("isGroup", true);
record.set("isGroup", false);
}
}
}
for (Record record : taskDeployPage.getList()) {
boolean daishen = false;
// long completeNum = record.getLong("completeNum"); // 完成的数量
long yiLingQuStatus = record.getLong("claimNum"); // 待审总数
// 待审总数
if (yiLingQuStatus >= 1) {
daishen = true;
}
record.set("daishen", daishen);
// record.set("total", completeNum+yiLingQuStatus);
}
return taskDeployPage;
}
/**
* 获取待完成单据信息列表
* @param ctr
* @return
* @throws ActiveRecordException
*/
public Page<Record> getCompletedList(QhyfController ctr) throws ActiveRecordException {
Page<Record> onlineTaskPage = null;
Page<Record> offlineTaskPage = null;
Page<Record> admittanceTaskPage = null;
List<String> paras = new ArrayList<String>();
// 获取主界面里面的对应点击人的uuid
// String mainUserId =ctr.getPara("mainUuid");
// StringUtils.notBlank(mainUserId);
// 获取分页信息
PageInfo pageInfo = ctr.getPageInfo();
AssertUtils.notNull(pageInfo, "分页信息不能为空");
// 设置排序
String order = ctr.getOrderString(pageInfo);
// 查询条件
String where1 = "";
String where2 = "";
String where3 = "";
// UserId 不能为空 上个界面传入过来的
// String minUserId =ctr.getPara("mainUserId");
String mainUserId = ctr.getPara("mainUuid");
// StringUtils.notBlank(minUserId);
if (StringUtils.notBlank(mainUserId)) {
where1 += " and b.user_uuid = ?";
paras.add(mainUserId);
}
// 获取单据类型
String documentType = ctr.getPara("documentType");
// 不为空时添加查询条件
if (StringUtils.notBlank(documentType)) {
where1 += " and d.pay_priority = ? ";
paras.add(documentType);
}
// 获取任务类型
String taskId = ctr.getPara("taskId");
// 不为空时添加查询条件
if (StringUtils.notBlank(taskId)) {
where1 += " and e.uuid = ? ";
paras.add(taskId);
}
// 获取付款确认书编号
String payId = ctr.getPara("payId");
// 不为空时添加查询条件
if (StringUtils.notBlank(payId)) {
where1 += " and d.pay_id like ? ";
paras.add('%'+payId+'%');
}
if (StringUtils.notBlank(mainUserId)) {
where2 += " and b.user_uuid = ?";
paras.add(mainUserId);
}
if (StringUtils.notBlank(taskId)) {
where2 += " and e.uuid = ? ";
paras.add(taskId);
}
if (StringUtils.notBlank(payId)) {
where2 += " and d.pay_id like ? ";
paras.add('%'+payId+'%');
}
if (StringUtils.notBlank(mainUserId)) {
where3 += " and b.user_uuid = ?";
paras.add(mainUserId);
}
/*if (StringUtils.notBlank(documentType)) {
where2 += " and d.pay_priority = ? ";
paras.add(documentType);
}*/
if (StringUtils.notBlank(documentType)) {
where3 += " and d.pay_priority = ? ";
paras.add(documentType);
}
if (StringUtils.notBlank(taskId)) {
where3 += " and e.uuid = ? ";
paras.add(taskId);
}
if (StringUtils.notBlank(payId)) {
where3 += " and d.pay_id like ? ";
paras.add('%'+payId+'%');
}
String onlineTaskName = "a.ACTIVITY_NAME IN ('单证线上初审','单证线上复审')";
String admittanceTaskName = "a.ACTIVITY_NAME IN ('单证线上审核','项目单证线下纸质审核')";
String offlineTaskName = "a.ACTIVITY_NAME IN ('单证线下纸质审核','项目单证线下纸质审核')";
String select = " SELECT "
+ " task_uuid, "
+ " task_name, "
+ " task_user_uuid, "
+ " pay_id , "
+ " uuid, "
+ " pay_priority, "
+ " core_enterprise_name, "
+ " financial_institutions_name, "
+ " suppliers_name, "
+ " item_company_name, "
+ " num,"
+ " invoice_amount ";
// 保理线上
// onlineTaskName = "a.ACTIVITY_NAME IN ('单证线上初审','单证线上复审')";
String selectSqlone = " SELECT "
+ " e.uuid as task_uuid, "
+ " e.task_name, "
+ " b.user_uuid AS task_user_uuid, "
+ " d.pay_id , "
+ " d.uuid, "
+ " d.pay_priority, "
+ " f.core_enterprise_name, "
+ " i.financial_institutions_name, "
+ " h.suppliers_name, "
+ " j.item_company_name, "
+ " z.num,"
+ " z.invoice_amount ";
String fromSqlone = String.format(" FROM "
+ " bpm_activity a, "
+ " bpm_task_user b, "
+ " bpm_process_instance c, "
+ " biz_pay_comfirm_info d, "
+ " biz_document_task_info e, "
+ " biz_core_enterprise f, "
+ " biz_financial_institutions i, "
+ " biz_suppliers h, "
+ " biz_item_company j , "
+ " ( "
+ " SELECT "
+ " a.pay_id, "
+ " SUM(invoice_amount) AS invoice_amount, "
+ " COUNT(*) AS num "
+ " FROM "
+ " biz_invoice_info a, "
+ " biz_pay_comfirm_info b "
+ " WHERE "
+ " a.pay_id = b.uuid "
+ " AND a.sys_status = b.sys_status = 1"
+ " GROUP BY " + " a.pay_id "
+ " ) z "
+ "WHERE %s "
+ "AND a.task_id = b.task_id "
+ "AND d.uuid = z.pay_id "
+ "AND a.instance_id = c.instance_id "
+ "AND c.biz_uuid = d.uuid "
+ "AND f.uuid = d.core_enterprise_id "
+ "AND i.uuid = d.financial_institutions_id "
+ "AND h.uuid = d.suppliers_id "
+ "AND j.uuid = d.item_company_id "
+ "AND a.ACTIVITY_NAME = e.activity_name "
+ "AND c.process_name = e.process_name "
+ "AND d.core_enterprise_id = e.core_enterprise_id "
+ "AND d.financial_institutions_id = e.financial_institutions_id "
+ "AND d.signing_body_id = e.signing_body_id "
+ "AND A.sys_status = 1 "
+ "AND B.sys_status = 1 "
+ "AND C.sys_status = 1 "
+ "AND D.sys_status = 1 "
+ "AND F.sys_status = 1 "
+ "AND I.sys_status = 1 "
+ "AND H.sys_status = 1 "
+ "AND j.sys_status = 1 "
+ " %s ",onlineTaskName,where1);
// 第二种情况 单证线上审核','项目单证线下纸质审核
String selectSqlTwo = " SELECT "
+ " e.uuid as task_uuid, "
+ " e.task_name, "
+ " b.user_uuid AS task_user_uuid, "
// + " k.agreement_no AS pay_id, "
+ " d.pay_id , "
+ " d1.uuid, "
+ " '' as pay_priority, "
+ " f.core_enterprise_name, "
+ " i.financial_institutions_name, "
+ " h.suppliers_name, "
+ " '' AS item_company_name, "
+ " '' AS num, "
+ " '' AS invoice_amount ";
// 首次准入
// a.ACTIVITY_NAME IN ('单证线上审核','项目单证线下纸质审核')
// admittanceTaskName = "a.ACTIVITY_NAME IN ('单证线上审核','项目单证线下纸质审核')";
String fromSqlTwo = String.format(" FROM "
+ " bpm_activity a, "
+ " bpm_task_user b, "
+ " bpm_process_instance c, "
+ " biz_first_contract d1, "
+ " biz_core_enterprise f, "
+ " biz_document_task_info e, "
+ " biz_financial_institutions i, "
+ " biz_suppliers h, "
// + " biz_framework_agreement k "
+" ( SELECT uuid , agreement_no as pay_id FROM biz_framework_agreement WHERE sys_status=1) as d "
+ "WHERE %s "
+ "AND a.task_id = b.task_id "
+ "AND a.instance_id = c.instance_id "
+ "AND c.biz_uuid = d1.uuid "
+ "AND f.uuid = d1.core_enterprise_id "
+ "AND i.uuid = d1.financial_institutions_id "
+ "AND h.uuid = d1.suppliers_id "
// + "AND d1.agreement_id = k.uuid "
+ "AND d1.agreement_id = d.uuid "
+ "AND e.process_name = c.process_name "
+ "AND e.activity_name = a.ACTIVITY_NAME "
+ "AND e.core_enterprise_id = d1.core_enterprise_id "
+ "AND e.financial_institutions_id = d1.financial_institutions_id "
+ "AND e.signing_body_id = d1.signing_body_id "
+ "AND A.sys_status = 1 "
+ "AND B.sys_status = 1 "
+ "AND C.sys_status = 1 "
+ "AND D1.sys_status = 1 "
+ "AND F.sys_status = 1 "
+ "AND I.sys_status = 1 "
+ "AND H.sys_status = 1 "
+ "AND e.sys_status = 1 "
+ " %s ",admittanceTaskName ,where2);
// 保理线下
// 第三种情况 '单证线下纸质审核','项目单证线下纸质审核'
// offlineTaskName = "a.ACTIVITY_NAME IN ('单证线下纸质审核','项目单证线下纸质审核')";
String selectSqlThree = "SELECT "
+ " e.uuid AS task_uuid, "
+ " e.task_name, "
+ " b.user_uuid AS task_user_uuid, "
+ " d.pay_id, "
+ " d.uuid, "
+ " d.pay_priority, "
+ " f.core_enterprise_name, "
+ " i.financial_institutions_name, "
+ " h.suppliers_name, "
+ " j.item_company_name, "
+ " z.num, "
+ " z.invoice_amount ";
String fromSqlThree = String.format("FROM "
+ " bpm_activity a, "
+ " bpm_task_user b, "
+ " bpm_process_instance c, "
+ " biz_pay_comfirm_info d, "
+ " biz_document_task_info e, "
+ " biz_core_enterprise f, "
+ " biz_financial_institutions i, "
+ " biz_suppliers h, "
+ " biz_item_company j, "
+ " biz_factoring_reception k, "
+ " ( " + " SELECT "
+ " a.pay_id, "
+ " SUM(invoice_amount) AS invoice_amount, "
+ " COUNT(*) AS num "
+ " FROM "
+ " biz_invoice_info a, "
+ " biz_pay_comfirm_info b "
+ " WHERE "
+ " a.pay_id = b.uuid "
+ " AND a.sys_status = b.sys_status = 1 "
+ " GROUP BY "
+ " a.pay_id "
+ " ) z " + "WHERE %s " +
/*
* " a.ACTIVITY_NAME IN ( " + " '单证线下纸质审核', " +
* " '项目单证线下纸质审核' " + " ) " +
*/
"AND a.task_id = b.task_id "
+ "AND d.uuid = z.pay_id "
+ "AND a.instance_id = c.instance_id "
+ "AND c.biz_uuid = d.uuid "
+ "AND f.uuid = d.core_enterprise_id "
+ "AND i.uuid = d.financial_institutions_id "
+ "AND h.uuid = d.suppliers_id "
+ "AND j.uuid = d.item_company_id "
+ "AND a.ACTIVITY_NAME = e.activity_name "
+ "AND c.process_name = e.process_name "
+ "AND d.core_enterprise_id = e.core_enterprise_id "
+ "AND d.financial_institutions_id = e.financial_institutions_id "
+ "AND d.signing_body_id = e.signing_body_id "
+ "AND z.pay_id = d.uuid "
+ "AND d.uuid = k.biz_id "
+ "AND k.receive_no = e.auto_assign "
+ "AND A.sys_status = 1 "
+ "AND B.sys_status = 1 "
+ "AND C.sys_status = 1 "
+ "AND D.sys_status = 1 "
+ "AND F.sys_status = 1 "
+ "AND I.sys_status = 1 "
+ "AND H.sys_status = 1 "
+ "AND j.sys_status = 1 "
+ "AND e.sys_status = 1 "
+ "AND k.sys_status = 1 "
+ " %s ",offlineTaskName, where3);
String from =" from ( " + selectSqlone + fromSqlone + " union " + selectSqlTwo + fromSqlTwo +" union " + selectSqlThree + fromSqlThree + " ) tt";
onlineTaskPage = Db.paginate(pageInfo.getPageIndex(), pageInfo.getPageSize(), select,from+order , paras.toArray());
/* onlineTaskPage = Db.paginate(pageInfo.getPageIndex(), pageInfo.getPageSize(), selectSqlone,
String.format(fromSqlone, onlineTaskName) + where + order, paras.toArray());
offlineTaskPage = Db.paginate(pageInfo.getPageIndex(), pageInfo.getPageSize(), selectSqlTwo,
String.format(fromSqlTwo, admittanceTaskName) + where + order, paras.toArray());
admittanceTaskPage = Db.paginate(pageInfo.getPageIndex(), pageInfo.getPageSize(), selectSqlThree,
String.format(fromSqlThree, offlineTaskName) + where + order, paras.toArray());
List<Record> tempRecord = onlineTaskPage.getList();
for (Record record : offlineTaskPage.getList()) {
tempRecord.add(record);
}
for (Record record : admittanceTaskPage.getList()) {
tempRecord.add(record);
}
onlineTaskPage.setList(tempRecord);
int totalRow = onlineTaskPage.getList().size();
int totalPage = totalRow / 10 + 1;
onlineTaskPage.setTotalRow(totalRow);
onlineTaskPage.setTotalPage(totalPage);*/
return onlineTaskPage;
}
/**
* 页面初始化的时候， 判断当前用户是什么角色 主管还是 单证人员
* @param ctl
* @return flag
* @throws ActiveRecordException
*/
public boolean getPermission(QhyfController ctl) throws ActiveRecordException {
boolean flag = false;
// 获取到当前用户的sys_user表ID
String loginUserID = ctl.getCurrentUser().getStr(BlConstant.FIELD_USER_ID);
// 构造查询SQL 查询当前用户的角色有那些
String selectSql = String.format(""
+ " SELECT a.role_name ,a.uuid "
+ " FROM sys_role a , "
+ " sys_user_role b , "
+ " sys_user c , "
+ " sys_business_configure_info d "
+ " WHERE a.sys_status = 1 "
+ " AND b.sys_status = 1 "
+ " AND c.sys_status = 1 "
+ " AND d.sys_status = 1 "
+ " AND a.uuid = b.role_uuid "
+ " AND b.user_uuid = c.uuid "
+ " AND d.configu_date = a.uuid "
+ "AND d.configu_type_id in('document_is_view_modify','IT_is_view_modify') "
+ " AND c.uuid= ? "
);
List<Record> record = Db.find(selectSql, loginUserID);
if (!record.isEmpty()) {
flag = true;
}
return flag;
}
/**
* 获取单证人员信息列表
* @param ctr
* @throws ActiveRecordException
*/
public Page<Record> getUserList(QhyfController ctr) throws ActiveRecordException {
Page<Record> userPage = null;
List<String> paras = new ArrayList<String>();
// 获取分页信息
PageInfo pageInfo = ctr.getPageInfo();
AssertUtils.notNull(pageInfo, "分页信息不能为空");
// 设置排序
String order = ctr.getOrderString(pageInfo);
// 查询条件
String where = "";
// 第一次加载的任务类型的id
// 获取任务窗口传过来的 任务名称及其任务的id
String taskUuid = ctr.getPara("taskUuid");
String isTask = "";
if (StringUtils.notBlank(taskUuid)) {
isTask = " and e.uuid = ? ";
paras.add(taskUuid);
}
//获取分配人uuid
String uuid = ctr.getPara("mainUuid");
if(!StringUtils.isBlank(uuid)){
where += " and a.uuid <> ? ";
paras.add(uuid);
}
// 获取姓名
String userName = ctr.getPara("userName");
// 不为空时添加查询条件
if (StringUtils.notBlank(userName)) {
where += " and a.user_name = ? ";
paras.add(userName);
}
// 获取任务类型
String taskUuids = ctr.getPara("taskId");
// 不为空时添加查询条件
if (StringUtils.notBlank(taskUuids)) {
String[] taskU = taskUuids.split(",");
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < taskU.length; i++) {
stringBuilder.append("'" + taskU[i] + "',");
}
stringBuilder.deleteCharAt(stringBuilder.length() - 1);
isTask += " and e.uuid in ( " + stringBuilder.toString() + " ) ";
}
// 获取审核层级
String approveRate = ctr.getPara("approveRate");
// 不为空时添加查询条件
if (StringUtils.notBlank(approveRate)) {
where += " and e.uuid = ? ";
paras.add(approveRate);
}
String selectSql = " SELECT DISTINCT "
+ " (a.uuid) AS user_uuid,"
+ " a.user_name, "
+ " a.user_id, "
+ " c.online_state, "
+ " e.level_name, "
+ " e.uuid as euuid , "
+ " x.yiLingQuStatus, "
+ " x.payPriority, "
+ " x.task_name , "
+ " x.task_id , "
+ " x.uuid ";
String fromSql = String.format("FROM "
+ " sys_user a, "
+ " sys_user_role b, "
+ " biz_document_user_info c, "
+ " sys_role d, "
+ " biz_document_level_info e, "
+ " ( "
+ " SELECT "
+ " b.user_uuid AS task_user_uuid, "
+ " a. STATUS, "
+ " a.task_id , "
+ " e.task_name, "
+ " e.uuid, "
+ " COUNT( "
+ " CASE "
+ " WHEN d.pay_priority = 1 THEN "
+ " '急单个数' "
+ " END " + " ) AS payPriority, "
+ " COUNT( "
+ " CASE "
+ " WHEN a. STATUS = '已领取' THEN "
+ " '已领取个数' "
+ " END "
+ " ) AS yiLingQuStatus "
+ " FROM "
+ " bpm_activity a, "
+ " bpm_task_user b, "
+ " bpm_process_instance c, "
+ " biz_pay_comfirm_info d, "
+ " biz_document_task_info e "
+ " WHERE "
+ " a.sys_status = 1 "
+ " AND b.sys_status = 1 "
+ " AND c.sys_status = 1 "
+ " AND d.sys_status = 1 "
+ " AND e.sys_status = 1 "
+ " AND a.task_id = b.task_id "
+ " AND a.instance_id = c.instance_id "
+ " AND c.biz_uuid = d.uuid "
+ " AND a.ACTIVITY_NAME = e.activity_name "
+ " AND c.process_name = e.process_name "
+ " AND d.core_enterprise_id = e.core_enterprise_id "
+ " AND d.financial_institutions_id = e.financial_institutions_id "
+ " AND d.signing_body_id = e.signing_body_id "
+" %s "
+ " GROUP BY "
+ " b.user_uuid " + ") x "
+ "WHERE "
+ " a.uuid = b.user_uuid "
+ "AND b.role_uuid = d.uuid "
+ "AND x.task_user_uuid = a.uuid "
+ "AND a.sys_status = 1 "
+ "AND b.sys_status = 1 "
+ "AND c.sys_status = 1 "
+ "AND d.sys_status = 1 "
+ "AND e.sys_status = 1 "
+ "AND a.uuid = c.user_id "
+ "AND c.online_state = 1 "
+ "AND e.uuid = c.document_level_id "
+ "AND b.role_uuid IN ( " + " 'qhyf1011', "
+ " 'qhyf1010', " + " 'qhyf1048' " + ") ",isTask);
userPage = Db.paginate(pageInfo.getPageIndex(), pageInfo.getPageSize(), selectSql,fromSql+where + order,
paras.toArray());
return userPage;
}
// /**
// * 获取批量的单证人员信息列表
// *
// * @param ctr
// * @return
// * @throws ActiveRecordException
// */
// public Page<Record> getUserManyList(QhyfController ctr) throws ActiveRecordException {
// Page<Record> userPage = null;
// List<String> paras = new ArrayList<String>();
// // 获取分页信息
// PageInfo pageInfo = ctr.getPageInfo();
// AssertUtils.notNull(pageInfo, "分页信息不能为空");
// // 设置排序
// String order = ctr.getOrderString(pageInfo);
// // 查询条件
// String where = "";
// // 第一次加载的任务类型的id
// // 获取任务窗口传过来的 任务名称及其任务的id
// /*
// * String taskUuid = ctr.getPara("taskUuid"); if
// * (StringUtils.notBlank(taskUuid)) { where += " and x.uuid = ? ";
// * paras.add(taskUuid); }
// * // 获取姓名 String userName = ctr.getPara("userName"); // 不为空时添加查询条件 if
// * (StringUtils.notBlank(userName)) { where += " and a.user_name = ? ";
// * paras.add(userName); }
// * // 获取任务类型 String taskId = ctr.getPara("taskId"); // 不为空时添加查询条件 if
// * (StringUtils.notBlank(taskId)) { where += " and x.uuid = ? ";
// * paras.add(taskId); }
// * // 获取审核层级 String approveRate = ctr.getPara("approveRate"); //
// * 不为空时添加查询条件 if (StringUtils.notBlank(approveRate)) { where +=
// * " and e.uuid = ? "; paras.add(approveRate); }
// */
// //获取分配人uuid
// String uuid = ctr.getPara("mainUuid");
// if(!StringUtils.isBlank(uuid)){
// where += " and a.uuid <> ? ";
// paras.add(uuid);
// }
// // 获取任务类型的uuid
// String taskUuids = ctr.getPara("taskUuids"); // 获取的数组
// String isTakes ="";
// // 不为空时添加查询条件
// if (StringUtils.notBlank(taskUuids)) {
// String[] taskU = taskUuids.split(",");
// StringBuilder stringBuilder = new StringBuilder();
// for (int i = 0; i < taskU.length; i++) {
// stringBuilder.append("'" + taskU[i] + "',");
// }
// stringBuilder.deleteCharAt(stringBuilder.length() - 1);
// //where += " and x.uuid in ( " + stringBuilder.toString() + " ) ";
// isTakes += " and e.uuid in ( " + stringBuilder.toString() + " ) ";
//
// }
// String selectSql = " SELECT DISTINCT " +
// " a.uuid AS user_uuid, " +
// " a.user_name, "
// + " a.user_id, "
// + " c.online_state, "
// + " e.level_name, "
// + " e.uuid as euuid , "
// + " x.yiLingQuStatus, "
// + " x.payPriority, "
// + " x.task_name, "
// + " x.task_id , "
// + " x.uuid ";
// String fromSql =String.format( "FROM "
// + " sys_user a, "
// + " sys_user_role b, "
// + " biz_document_user_info c, "
// + " sys_role d, "
// + " biz_document_level_info e, "
// + " ( " + " SELECT "
// + " b.user_uuid AS task_user_uuid, "
// + " a. STATUS, "
// + " a.task_id , "
// + " e.task_name, "
// + " e.uuid, "
// + " COUNT( "
// + " CASE "
// + " WHEN d.pay_priority = 1 THEN "
// + " '急单个数' "
// + " END "
// + " ) AS payPriority, "
// + " COUNT( "
// + " CASE "
// + " WHEN a. STATUS = '已领取' THEN "
// + " '已领取个数' "
// + " END "
// + " ) AS yiLingQuStatus "
// + " FROM "
// + " bpm_activity a, "
// + " bpm_task_user b, "
// + " bpm_process_instance c, "
// + " biz_pay_comfirm_info d, "
// + " biz_document_task_info e "
// + " WHERE "
// + " a.sys_status = 1 "
// + " AND b.sys_status = 1 "
// + " AND c.sys_status = 1 "
// + " AND d.sys_status = 1 "
// + " AND e.sys_status = 1 "
// + " AND a.task_id = b.task_id "
// + " AND a.instance_id = c.instance_id "
// + " AND c.biz_uuid = d.uuid "
// + " AND a.ACTIVITY_NAME = e.activity_name "
// + " AND c.process_name = e.process_name "
// + " AND d.core_enterprise_id = e.core_enterprise_id "
// + " AND d.financial_institutions_id = e.financial_institutions_id "
// + " AND d.signing_body_id = e.signing_body_id "
// +" %s "
// + " GROUP BY "
// + " b.user_uuid "
// + " ) x " + "WHERE "
// + " a.uuid = b.user_uuid "
// + "AND b.role_uuid = d.uuid "
// + "AND x.task_user_uuid = a.uuid "
// + "AND a.sys_status = 1 "
// + "AND b.sys_status = 1 "
// + "AND c.sys_status = 1 "
// + "AND d.sys_status = 1 "
// + "AND e.sys_status = 1 "
// + "AND a.uuid = c.user_id "
// + "AND c.online_state = 1 "
// + "AND e.uuid = c.document_level_id "
// + "AND b.role_uuid IN ( "
// + " 'qhyf1011', "
// + " 'qhyf1010', "
// + " 'qhyf1048' " + ") ",isTakes);
// userPage = Db.paginate(pageInfo.getPageIndex(), pageInfo.getPageSize(), selectSql, fromSql + where + order,
// paras.toArray());
// return userPage;
// }
/**
* 加载任务类型的下拉列表
* @param ctr
* @return 任务类型
* @throws ActiveRecordException
*/
public List<Record> initSelect(QhyfController ctr) throws ActiveRecordException {
// 创建结果集
List<Record> taskNameList = null;
String sql = "SELECT uuid as fuuid ,task_name FROM biz_document_task_info WHERE sys_status= 1 ";
taskNameList = Db.find(sql);
return taskNameList;
}
/**
* 加载审核层级下拉列表
* @param ctr
* @return 审核层级
* @throws ActiveRecordException
*/
public List<Record> getApproveRate(QhyfController ctr) throws ActiveRecordException {
// 创建结果集
List<Record> approveList = null;
String sql = "SELECT UUID,level_name FROM biz_document_level_info ";
approveList = Db.find(sql);
return approveList;
}
// /**
// * 确认分配处理
// *
// * @param ctr
// * @throws ActiveRecordException
// * @throws CordysException
// */
public boolean comfirDistribute12(QhyfController ctr) throws ActiveRecordException, CordysException {
// 取得数据 ：为json字符串
String temp = ctr.getPara("documentUsers");
List<JSONObject> list = JSON.parseArray(temp, JSONObject.class); // 把获取的转化成对象
String uuid = "";
String userUuid = "";
String userName = "";
String taskId = "";
// 把任务调配给谁的id
String userId = "";
//获取付款确认书的uuid
String payUuid="";
//主界面人的userID
String mainUserId ="" ;
for (JSONObject jsonObject : list) { // 遍历
// 获取前端的任务的uuid，用戶的uuid，姓名 ，任务的taskId
uuid += (StringUtils.notBlank(uuid) ? "," : "") + jsonObject.getString("uuid");
userUuid += (StringUtils.notBlank(userUuid) ? "," : "") + jsonObject.getString("userUuid");
userName += (StringUtils.notBlank(userName) ? "," : "") + jsonObject.getString("userName");
taskId += (StringUtils.notBlank(taskId) ? "," : "") + jsonObject.getString("taskId");
userId += (StringUtils.notBlank(userId) ? "," : "") + jsonObject.getString("userId");
mainUserId= jsonObject.getString("mainUserId");
payUuid=jsonObject.getString("payUuid");
this.setCordysUser(userId);
this.setCordysUser(mainUserId);
}
// 获取到当前用户的sys_user表ID
String loginUserID = ctr.getCurrentUser().getStr(BlConstant.FIELD_USER_ID);
String sql = "select user_id from sys_user where sys_status = 1 and uuid = ?";
String loginUserId = Db.queryStr(sql,loginUserID);
// AssertUtils.notNull(userName, "数据库未存在此人信息");
//根据付款确认书的uuid查出流程信息表的流程id
String processSql="SELECT instance_id FROM bpm_process_instance WHERE sys_status = 1 and biz_uuid= ?";
String processId = Db.queryStr(processSql,payUuid);
//根据流程的id 查出 流程最新活动表 里面的taskid
String farOutTaskidSql="SELECT task_id FROM bpm_last_activity WHERE sys_status = 1 and instance_id= ? ";
String farOutTaskid = Db.queryStr(farOutTaskidSql, processId);
//"afeb63df-b665-11e8-f4cf-cfd3455a169b"
// 判断分配任务是否成功 pengzhiwei fuliulin
boolean isOkTask=PerformTaskApi.delegateTask(loginUserId,userId, farOutTaskid, "", true);
//创建最新流程表的实力
BpmLastActivity bpmLastActivity=new BpmLastActivity();
boolean isOk=false;
//根据userId查出他的userName，设置到表bpm_last_activity里面
String sysUserNameSql="SELECT user_name from sys_user WHERE sys_status = 1 and user_id = ?";
String sysUserName=Db.queryStr(sysUserNameSql,userId);
if (isOkTask==true) {
// 更新表bpm_last_activity 里面的username
bpmLastActivity.set("instanceId",processId );
bpmLastActivity.set("taskId",farOutTaskid );
bpmLastActivity.set("userName",sysUserName );
isOk=ctr.merge(BpmLastActivity.dao.getTable().getName(), bpmLastActivity);
}
return isOk;
}
/**
* 确认分配处理
* @param ctr
* @throws ActiveRecordException
* @throws CordysException
*/
public boolean comfirDistribute(QhyfController ctr) throws ActiveRecordException, CordysException {
// 取得数据 ：为json字符串
String temp = ctr.getPara("documentUsers");
List<JSONObject> list = JSON.parseArray(temp, JSONObject.class); // 把获取的转化成对象
String uuid = "";
String userUuid = "";
String userName = "";
String taskId = "";
// 把任务调配给谁的id
String userId = "";
//获取付款确认书的uuid
String payUuid="";
//主界面人的userID
String mainUserId ="" ;
for (JSONObject jsonObject : list) { // 遍历
// 获取前端的任务的uuid，用戶的uuid，姓名 ，任务的taskId
uuid += (StringUtils.notBlank(uuid) ? "," : "") + jsonObject.getString("uuid");
userUuid += (StringUtils.notBlank(userUuid) ? "," : "") + jsonObject.getString("userUuid");
userName += (StringUtils.notBlank(userName) ? "," : "") + jsonObject.getString("userName");
taskId += (StringUtils.notBlank(taskId) ? "," : "") + jsonObject.getString("taskId");
userId += (StringUtils.notBlank(userId) ? "," : "") + jsonObject.getString("userId");
mainUserId= jsonObject.getString("mainUserId");
// 在待完成信息列表里面传了多个付款确认书的uuid过来
payUuid += (StringUtils.notBlank(payUuid) ? "," : "") +jsonObject.getString("payUuid"); // 这里已经是个数组
this.setCordysUser(userId);
this.setCordysUser(mainUserId);
}
// 获取到当前用户的sys_user表ID
String loginUserID = ctr.getCurrentUser().getStr(BlConstant.FIELD_USER_ID);
String sql = "select user_id from sys_user where sys_status = 1 and uuid = ?";
String loginUserId = Db.queryStr(sql,loginUserID);
// AssertUtils.notNull(userName, "数据库未存在此人信息");
boolean isOk=false;
String[] payUuids=payUuid.split(",");
for (int i = 0; i < payUuids.length; i++) {
//根据付款确认书的uuid查出流程信息表的流程id (id为数组)
String processSql="SELECT instance_id FROM bpm_process_instance WHERE sys_status = 1 and biz_uuid= ?";
String processId = Db.queryStr(processSql,payUuids[i]);
//根据流程的id 查出 流程最新活动表 里面的taskid
String farOutTaskidSql = "SELECT task_id FROM bpm_last_activity WHERE sys_status = 1 and instance_id= ? ";
Record record = Db.findFirst(farOutTaskidSql,processId);
boolean isOkTask=PerformTaskApi.delegateTask(loginUserId,userId , record.getStr("taskId"), "", true);
String[] userIds=userId.split(",");
for (int j = 0; j < userIds.length; j++) {
String sysUserNameSql="SELECT user_name from sys_user WHERE sys_status = 1 and user_id = ?";
String sysUserName=Db.queryStr(sysUserNameSql,userIds[j]);
//创建最新流程表的对象
BpmLastActivity bpmLastActivity=new BpmLastActivity();
if (isOkTask) {
//根据userId查出他的userName，设置到表bpm_last_activity里面
// 更新表bpm_last_activity 里面的username
bpmLastActivity.set("instanceId",processId );
bpmLastActivity.set("taskId",record.getStr("taskId") );
bpmLastActivity.set("userName",sysUserName );
isOk=ctr.merge(BpmLastActivity.dao.getTable().getName(), bpmLastActivity);
}
}
}
return isOk;
}
/**
* 前台参数转义处理方法
* @param str
* String类型的前台参数
* @return String
*/
private String strToEscape(String str){
//转义百分号
if (str.contains("%")) {
str = str.replace("%", "\\%");
}
//转义下划线
if (str.contains("_")) {
str = str.replace("_", "\\_");
}
//trim处理
str = str.trim();
//返回处理结果
return str;
}
}
