Calculation/app/service/change.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date 2018/8/14
 * @version
 */

const audit = require('../const/audit');
// const auditConst = require('../const/audit').change;
const auditType = require('../const/audit').auditType;
const fs = require('fs');
const path = require('path');
const changeConst = require('../const/change');
const smsTypeConst = require('../const/sms_type');
const SMS = require('../lib/sms');
const SmsAliConst = require('../const/sms_alitemplate');
const wxConst = require('../const/wechat_template');
const pushType = require('../const/audit').pushType;
const projectLogConst = require('../const/project_log');
const pushOperate = require('../const/spec_3f').pushOperate;

module.exports = app => {
    class Change extends app.BaseService {
        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'change';
        }

        /**
         * 查找数据
         *
         * @param {Object} data - 筛选表单中的get数据
         * @return {void}
         */
        searchFilter(data) {
            this.initSqlBuilder();
            // this.sqlBuilder.columns = ['id', 'username', 'real_name', 'create_time', 'last_login', 'login_ip',
            //     'group_id', 'token', 'can_login'];

            data.type = parseInt(data.status);
            if (data.keyword !== undefined) {
                switch (data.type) {
                    // 用户名
                    case 1:
                        this.sqlBuilder.setAndWhere('username', {
                            value: this.db.escape(data.keyword + '%'),
                            operate: 'like',
                        });
                        break;
                    // 姓名
                    case 2:
                        this.sqlBuilder.setAndWhere('real_name', {
                            value: this.db.escape(data.keyword + '%'),
                            operate: 'like',
                        });
                        break;
                    // 联系电话
                    case 3:
                        this.sqlBuilder.setAndWhere('telephone', {
                            value: this.db.escape(data.keyword + '%'),
                            operate: 'like',
                        });
                        break;
                    default:
                        break;
                }
            }

            // 办事处筛选
            if (data.office !== undefined && data.office !== '') {
                this.sqlBuilder.setAndWhere('office', {
                    value: this.db.escape(data.office),
                    operate: '=',
                });
            }
        }

        async loadChangeAuditViewData(change) {
            const times = change.status === audit.change.status.checkNo || change.status === audit.change.status.revise ? change.times - 1 : change.times;

            if (!change.user) change.user = await this.ctx.service.projectAccount.getAccountInfoById(change.uid);
            change.auditHistory = await this.ctx.service.changeAudit.getAuditorHistory(change.cid, times);
            // 获取审批流程中左边列表
            if ((change.status === audit.change.status.checkNo || change.status === audit.change.status.revise) && change.uid !== this.ctx.session.sessionUser.accountId && !this.ctx.session.sessionUser.is_admin) {
                const auditors = await this.ctx.service.changeAudit.getAuditorsNew(change.cid, times); // 全部参与的审批人
                const auditorGroups = this.ctx.helper.groupAuditors(auditors, 'usort');
                change.auditors2 = this.ctx.helper.groupAuditorsUniq(auditorGroups);
                // change.auditors2.unshift([{
                //     uid: change.user.id, usort: 0, usite: 0, times: change.times - 1, audit_order: 0, audit_type: audit.auditType.key.common,
                //     name: change.user.name, role: change.user.role, company: change.user.company,
                // }]);
            } else {
                change.auditors2 = change.userGroups;
            }
            if (change.status === audit.change.status.uncheck || change.status === audit.change.status.checkNo || change.status === audit.change.status.revise) {
                change.auditorList = await this.ctx.service.changeAudit.getAuditorsNew(change.cid, change.times);
            }
        }

        async loadChangeUser(change) {
            const status = audit.change.status;
            const accountId = this.ctx.session.sessionUser.accountId;

            change.user = await this.ctx.service.projectAccount.getAccountInfoById(change.uid);
            change.auditors = await this.ctx.service.changeAudit.getAuditorsNew(change.cid, change.times); // 全部参与的审批人
            change.auditorIds = this._.map(change.auditors, 'uid');
            change.curAuditors = change.auditors.filter(x => { return x.status === status.checking; }); // 当前流程中审批中的审批人
            change.curAuditorIds = this._.map(change.curAuditors, 'uid');
            change.flowAuditors = change.curAuditors.length > 0 ? change.auditors.filter(x => { return x.usort === change.curAuditors[0].usort; }) : []; // 当前流程中参与的审批人（包含会签时，审批通过的人）
            change.flowAuditorIds = this._.map(change.flowAuditors, 'uid');
            change.nextAuditors = change.curAuditors.length > 0 ? change.auditors.filter(x => { return x.usort === change.curAuditors[0].usort + 1; }) : [];
            change.nextAuditorIds = this._.map(change.nextAuditors, 'uid');
            change.auditorGroups = this.ctx.helper.groupAuditors(change.auditors, 'usort');
            change.userGroups = this.ctx.helper.groupAuditorsUniq(change.auditorGroups);
            // change.userGroups.unshift([{
            //     uid: change.user.id, usort: 0, usite: 0, times: change.times, audit_order: 0, audit_type: audit.auditType.key.common,
            //     name: change.user.name, role: change.user.role, company: change.user.company,
            // }]);
            change.finalAuditorIds = change.userGroups[change.userGroups.length - 1].map(x => { return x.uid; });
        }

        async add(tenderId, userId, code, plan_code, name, delimit = 100) {
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `tid` = ? AND ((`code` = ? AND `status` != ?) OR (`p_code` = ? AND `status` = ?))';
            const sqlParam = [this.tableName, tenderId, code, audit.change.status.checked, code, audit.change.status.checked];
            const codeCount = await this.db.queryOne(sql, sqlParam);
            const count = codeCount.count;
            if (count > 0) {
                throw '变更令号重复';
            }

            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const cid = this.uuid.v4();
                const change = {
                    cid,
                    tid: tenderId,
                    uid: userId,
                    status: audit.change.status.uncheck,
                    times: 1,
                    valid: true,
                    in_time: new Date(),
                    code,
                    name,
                    plan_code,
                    state: 3,
                    delimit,
                    // tp_decimal: this.ctx.tender.info.decimal.tp,
                    // up_decimal: this.ctx.tender.info.decimal.up,
                };
                if (plan_code) {
                    const planInfo = await this.ctx.service.changePlan.getDataByCondition({ tid: tenderId, code: plan_code });
                    if (planInfo) {
                        change.org_name = planInfo.org_name;
                        change.peg = planInfo.peg;
                        change.org_code = planInfo.new_code;
                        change.new_code = planInfo.c_new_code;
                        change.new_name = planInfo.design_name;
                        // 工程变更类别读取
                        const projectData = await this.ctx.service.project.getDataById(this.ctx.session.sessionProject.id);
                        const fun_set = await this.ctx.service.project.getFunSet(projectData.fun_set);
                        const classInfo = this._.find(fun_set.change_class, function(item) {
                            return item.name === planInfo.class || (item.checked && item.new_name === planInfo.class);
                        });
                        change.class = classInfo ? classInfo.value : this._.find(fun_set.change_class, { checked: true }).value;
                        const qualityIndex = planInfo.quality && this._.indexOf(changeConst.qualityName, planInfo.quality) !== -1 ? this._.indexOf(changeConst.qualityName, planInfo.quality) : 0;
                        change.quality = qualityIndex ? qualityIndex : changeConst.quality.common.value;
                        let content = planInfo.reason ? planInfo.reason.replace(/[\r\n]/g, '\r\n') : '';
                        content = content + (planInfo.content ? (planInfo.reason ? '\r\n' : '') + planInfo.content.replace(/[\r\n]/g, '\r\n') : '');
                        change.content = content ? content : null;
                        change.expr = planInfo.expr;
                    }
                }
                const operate = await this.transaction.insert(this.tableName, change);

                if (operate.affectedRows <= 0) {
                    throw '新建变更令数据失败';
                }

                // 把提交人信息添加到zh_change_audit
                const userInfo = await this.ctx.service.projectAccount.getDataById(userId);
                const changeaudit = [
                    {
                        tid: tenderId, cid, uid: userId,
                        name: userInfo.name, jobs: userInfo.role, company: userInfo.company,
                        times: 1, usite: 0, usort: 0,
                        begin_time: new Date(),
                        status: audit.change.status.checking,
                        audit_type: auditType.key.common, audit_order: 0,
                    },
                ];
                // 并把之前存在的变更令审批人添加到zh_change_audit
                // 先找出标段最近存在的变更令审批人的变更令info
                const changeInfo = await this.ctx.service.change.getHaveAuditLastInfo(tenderId);
                if (changeInfo) {
                    // 再获取非原报审批人
                    const auditList = await this.ctx.service.changeAudit.getUniqAuditor(changeInfo.cid, changeInfo.times); // 全部参与的审批人
                    for (const x of auditList) {
                        if (x.audit_order !== 0) {
                            changeaudit.push({
                                tid: tenderId, cid, uid: x.uid,
                                name: x.name, jobs: x.jobs, company: x.company,
                                times: 1, usite: x.usite, usort: x.audit_order, status: audit.change.status.uncheck,
                                audit_type: x.audit_type, audit_order: x.audit_order,
                            });
                        }
                    }
                }
                await this.transaction.insert(this.ctx.service.changeAudit.tableName, changeaudit);

                result = change;
                this.transaction.commit();
            } catch (error) {
                console.log(error);
                // 回滚
                await this.transaction.rollback();
            }

            return result;
        }

        async getHaveAuditLastInfo(tenderId) {
            const sql = 'SELECT a.* FROM ?? as a LEFT JOIN ?? as b ON a.`cid` = b.`cid` WHERE a.`tid` = ? AND b.`usite` > 0 ORDER BY a.`in_time` DESC';
            const sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, tenderId];
            return await this.db.queryOne(sql, sqlParam);
        }

        async pendingDatas(tenderId, userId) {
            return await this.getAllDataByCondition({
                tid: tenderId,
                uid: userId,
                status: audit.change.status.checking,
            });
        }

        async uncheckDatas(tenderId, userId) {
            return await this.getAllDataByCondition({
                tid: tenderId,
                uid: userId,
                status: audit.change.status.uncheck,
            });
        }

        async checkingDatas(tenderId, userId) {
            return await this.getAllDataByCondition({
                tid: tenderId,
                uid: userId,
                status: audit.change.status.checking,
            });
        }

        async checkedDatas(tenderId, userId) {
            return await this.getAllDataByCondition({
                tid: tenderId,
                uid: userId,
                status: audit.change.status.checked,
            });
        }

        async checkNoDatas(tenderId, userId) {
            return await this.getAllDataByCondition({
                tid: tenderId,
                uid: userId,
                status: audit.change.status.checkNo,
            });
        }

        async checkNoCount(tenderId, userId) {
            return await this.count({
                tid: tenderId,
                uid: userId,
                status: audit.change.status.checkNo,
            });
        }

        /**
         * 获取变更令列表
         * @param {int} tenderId - 标段id
         * @param {int} status - 状态
         * @param {int} hadlimit - 分页
         * @return {object} list - 列表
         */
        async getListByStatus(tenderId, status = 0, hadlimit = 1, sortBy = '', orderBy = '', state = 0) {
            let sql = '';
            let sqlParam = '';
            const stateSql = state ? ' AND a.state = ' + state : '';
            if ((this.ctx.tender.isTourist || this.ctx.session.sessionUser.is_admin) && status === 0) {
                sql = 'SELECT a.* FROM ?? As a WHERE a.tid = ?' + stateSql;
                sqlParam = [this.tableName, tenderId];
            } else {
                switch (status) {
                    case 0: // 包含你的所有变更令
                        sql =
                            'SELECT a.* FROM ?? AS a WHERE a.tid = ? AND' +
                            ' (a.uid = ? OR (a.status != ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid))' +
                            ' OR (a.status = ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times - 1 = b.times GROUP BY b.cid))' +
                            ' OR a.status = ?)' + stateSql;
                        sqlParam = [
                            this.tableName,
                            tenderId,
                            this.ctx.session.sessionUser.accountId,
                            audit.change.status.uncheck,
                            this.ctx.service.changeAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            audit.change.status.checkNo,
                            this.ctx.service.changeAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            audit.change.status.checked,
                        ];
                        break;
                    case 1: // 待处理(你的)
                        sql = 'SELECT a.* FROM ?? as a WHERE cid in(SELECT b.cid FROM ?? as b WHERE tid = ? AND uid = ? AND status = ?)' + stateSql;
                        sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.change.status.checking];
                        break;
                    case 5: // 待上报(所有的)PS：取未上报，退回，修订的变更令
                        sql =
                            'SELECT a.* FROM ?? AS a WHERE' +
                            ' ((a.status != ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid))' +
                            ' OR (a.status = ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times - 1 = b.times GROUP BY b.cid)))' +
                            // 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? GROUP BY b.cid)' +
                            ' AND (a.status = ? OR a.status = ? OR a.status = ?) AND a.tid = ?' + stateSql;
                        sqlParam = [
                            this.tableName,
                            audit.change.status.uncheck,
                            this.ctx.service.changeAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            audit.change.status.checkNo,
                            this.ctx.service.changeAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            audit.change.status.uncheck,
                            audit.change.status.checkNo,
                            audit.change.status.revise,
                            tenderId,
                        ];
                        break;
                    case 2: // 进行中（所有的）
                    case 4: // 终止（所有的）
                        sql =
                            'SELECT a.* FROM ?? AS a WHERE ' +
                            'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) AND ' +
                            'a.status = ? AND a.tid = ?' + stateSql;
                        sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, status, tenderId];
                        break;
                    case 3: // 已完成（所有的）
                        sql = 'SELECT a.* FROM ?? AS a WHERE a.status = ? AND a.tid = ?' + stateSql;
                        sqlParam = [this.tableName, status, tenderId];
                        break;
                    default:
                        break;
                }
            }
            if (sortBy && orderBy) {
                if (sortBy === 'code') {
                    sql += ' ORDER BY CHAR_LENGTH(a.code) ' + orderBy + ',convert(a.code using gbk) ' + orderBy;
                } else {
                    sql += ' ORDER BY a.in_time ' + orderBy;
                }
            } else {
                sql += ' ORDER BY a.in_time DESC';
            }
            if (hadlimit) {
                const limit = this.ctx.pageSize ? this.ctx.pageSize : this.app.config.pageSize;
                const offset = limit * (this.ctx.page - 1);
                const limitString = offset >= 0 ? offset + ',' + limit : limit;
                sql += ' LIMIT ' + limitString;
            }
            const list = await this.db.query(sql, sqlParam);
            return list;
        }

        /**
         * 获取变更令个数
         * @param {int} tenderId - 标段id
         * @param {int} status - 状态
         * @return {void}
         */
        async getCountByStatus(tenderId, status, state = 0) {
            const stateSql = state ? ' AND a.state = ' + state : '';
            if ((this.ctx.tender.isTourist || this.ctx.session.sessionUser.is_admin) && status === 0) {
                const sql5 = 'SELECT count(*) AS count FROM ?? AS a WHERE a.tid = ?' + stateSql + ' ORDER BY a.in_time DESC';
                const sqlParam5 = [this.tableName, tenderId];
                const result5 = await this.db.query(sql5, sqlParam5);
                return result5[0].count;
            }
            switch (status) {
                case 0: // 包含你的所有变更令
                    const sql =
                        'SELECT count(*) AS count FROM ?? AS a WHERE a.tid = ? AND ' +
                        '(a.uid = ? OR (a.status != ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid))' +
                        ' OR (a.status = ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times - 1 = b.times GROUP BY b.cid))' +
                        ' OR a.status = ? )' + stateSql;
                    const sqlParam = [
                        this.tableName,
                        tenderId,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.uncheck,
                        this.ctx.service.changeAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.checkNo,
                        this.ctx.service.changeAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.checked,
                    ];
                    const result = await this.db.query(sql, sqlParam);
                    return result[0].count;
                case 1: // 待处理(你的)
                    // return await this.ctx.service.changeAudit.count({
                    //     tid: tenderId,
                    //     uid: this.ctx.session.sessionUser.accountId,
                    //     status: 2,
                    // });
                    const sql6 = 'SELECT count(*) AS count FROM ?? as a WHERE cid in(SELECT b.cid FROM ?? as b WHERE tid = ? AND uid = ? AND status = ?)' + stateSql;
                    const sqlParam6 = [this.tableName, this.ctx.service.changeAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.change.status.checking];
                    const result6 = await this.db.query(sql6, sqlParam6);
                    return result6[0].count;
                case 5: // 待上报(所有的)PS：取未上报，退回，修订的变更令
                    const sql2 =
                        'SELECT count(*) AS count FROM ?? AS a WHERE' +
                        ' ((a.status != ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid))' +
                        ' OR (a.status = ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times - 1 = b.times GROUP BY b.cid)))' +
                        // 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) ' +
                        ' AND (a.status = ? OR a.status = ? OR a.status = ?) AND a.tid = ?' + stateSql;
                    const sqlParam2 = [
                        this.tableName,
                        audit.change.status.uncheck,
                        this.ctx.service.changeAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.checkNo,
                        this.ctx.service.changeAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.uncheck,
                        audit.change.status.checkNo,
                        audit.change.status.revise,
                        tenderId,
                    ];
                    const result2 = await this.db.query(sql2, sqlParam2);
                    return result2[0].count;
                case 2: // 进行中（所有的）
                case 4: // 终止（所有的）
                    const sql3 =
                        'SELECT count(*) AS count FROM ?? AS a WHERE ' +
                        'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) AND a.status = ? AND a.tid = ?' + stateSql;
                    const sqlParam3 = [this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, status, tenderId];
                    const result3 = await this.db.query(sql3, sqlParam3);
                    return result3[0].count;
                case 3: // 已完成（所有的）
                    const sql4 = 'SELECT count(*) AS count FROM ?? AS a WHERE a.status = ? AND a.tid = ?' + stateSql;
                    const sqlParam4 = [this.tableName, status, tenderId];
                    const result4 = await this.db.query(sql4, sqlParam4);
                    return result4[0].count;
                default:
                    break;
            }
        }

        async getTp(tenderId, status) {
            if ((this.ctx.tender.isTourist || this.ctx.session.sessionUser.is_admin) && status === 0) {
                const sql5 = 'SELECT SUM(cast (total_price as decimal(18,6))) AS total_price FROM ?? WHERE tid = ?';
                const sqlParam5 = [this.tableName, tenderId];
                const result5 = await this.db.query(sql5, sqlParam5);
                return result5[0].total_price ? result5[0].total_price : 0;
            }
            switch (status) {
                case 0: // 包含你的所有变更令
                    const sql =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? AS a WHERE a.tid = ? AND ' +
                        '(a.uid = ? OR (a.status != ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid))' +
                        ' OR (a.status = ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times - 1 = b.times GROUP BY b.cid))' +
                        ' OR a.status = ? )';
                    const sqlParam = [
                        this.tableName,
                        tenderId,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.uncheck,
                        this.ctx.service.changeAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.checkNo,
                        this.ctx.service.changeAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.checked,
                    ];
                    const result = await this.db.query(sql, sqlParam);
                    return result[0].total_price ? result[0].total_price : 0;
                case 1: // 待处理(你的)
                    const sql6 = 'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? as a WHERE cid in(SELECT b.cid FROM ?? as b WHERE tid = ? AND uid = ? AND status = ?)';
                    const sqlParam6 = [this.tableName, this.ctx.service.changeAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.change.status.checking];
                    const result6 = await this.db.query(sql6, sqlParam6);
                    return result6[0].total_price ? result6[0].total_price : 0;
                case 5: // 待上报(所有的)PS：取未上报，退回，修订的变更令
                    const sql2 =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? AS a WHERE' +
                        ' ((a.status != ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid))' +
                        ' OR (a.status = ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times - 1 = b.times GROUP BY b.cid)))' +
                        // 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) ' +
                        ' AND (a.status = ? OR a.status = ? OR a.status = ?) AND a.tid = ?';
                    const sqlParam2 = [
                        this.tableName,
                        audit.change.status.uncheck,
                        this.ctx.service.changeAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.checkNo,
                        this.ctx.service.changeAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.change.status.uncheck,
                        audit.change.status.checkNo,
                        audit.change.status.revise,
                        tenderId,
                    ];
                    const result2 = await this.db.query(sql2, sqlParam2);
                    return result2[0].total_price ? result2[0].total_price : 0;
                case 2: // 进行中（所有的）
                case 4: // 终止（所有的）
                    const sql3 =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? AS a WHERE ' +
                        'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) AND a.status = ? AND a.tid = ?';
                    const sqlParam3 = [this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, status, tenderId];
                    const result3 = await this.db.query(sql3, sqlParam3);
                    return result3[0].total_price ? result3[0].total_price : 0;
                case 3: // 已完成（所有的）
                    const sql4 = 'SELECT SUM(cast (total_price as decimal(18,6))) AS total_price FROM ?? WHERE status = ? AND tid = ?';
                    const sqlParam4 = [this.tableName, status, tenderId];
                    const result4 = await this.db.query(sql4, sqlParam4);
                    return result4[0].total_price ? result4[0].total_price : 0;
                default:
                    break;
            }
        }

        /**
         * 获取变更令个数
         * @param {int} tenderId - 标段id
         * @param {int} quality - 变更性质
         * @return {void}
         */
        async getCountByQuality(tenderId, quality) {
            return await this.db.count(this.tableName, { tid: tenderId, quality });
        }

        /**
         * 获取变更令个数
         * @param {int} tenderId - 标段id
         * @param {int} status - 状态
         * @return {void}
         */
        async getCountByStatus2(tenderId, status) {
            if (status === audit.change.status.uncheck) {
                const sql =
                    'SELECT count(*) AS count FROM ?? WHERE ' +
                    'tid = ? AND (status = ? OR status = ? OR status = ?)';
                const sqlParam = [
                    this.tableName,
                    tenderId,
                    audit.change.status.uncheck,
                    audit.change.status.checkNo,
                    audit.change.status.revise,
                ];
                const result = await this.db.queryOne(sql, sqlParam);
                return result ? result.count : 0;
            }
            return await this.db.count(this.tableName, { tid: tenderId, status });
        }

        // 获取最后一个变更令
        async getLastChange(tenderId) {
            const sql = 'select * from ?? where tid = ? order by in_time desc LIMIT 1';
            const sqlParam = [this.tableName, tenderId];
            const result = await this.db.queryOne(sql, sqlParam);
            return result;
        }

        // 根据cid获取唯一的变更令
        async getChangeByCid(cid) {
            const sql = 'select * from ?? where cid = "' + cid + '"';
            const sqlParam = [this.tableName, cid];
            const result = await this.db.queryOne(sql, sqlParam);
            return result;
        }

        /**
         * 获取已批复变更令的总金额
         * @param {int} tenderId - 标段id
         * @param {int} quality - 变更性质
         * @return {void}
         */
        async getChangeTp(tenderId) {
            const sql = 'SELECT SUM(`total_price`) AS tp, SUM(`positive_tp`) AS p_tp, SUM(`negative_tp`) AS n_tp, SUM(`valuation_tp`) AS v_tp, SUM(`unvaluation_tp`) AS uv_tp FROM ?? WHERE tid = ? AND status = ?';
            const sqlParam = [this.tableName, tenderId, audit.change.status.checked];
            const result = await this.db.queryOne(sql, sqlParam);
            return result ? [result.tp, result.p_tp, result.n_tp, result.v_tp, result.uv_tp] : [0, 0, 0, 0, 0];
        }

        /**
         * 上报或重新上报或保存修改功能
         * @param {int} postData - 表单提交的数据
         * @param {int} tenderId - 标段id
         * @return {void}
         */
        async save(postData, tenderId) {
            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                // 变更令信息
                const changeInfo = await this.getDataByCondition({ cid: postData.cid });
                // 该变更令原报人信息
                const lastUser = await this.ctx.service.changeAudit.getLastUser(changeInfo.cid, changeInfo.times, 0);
                // 先删除本次原有的变更审批人和清单
                await this.ctx.service.changeAudit.deleteAuditData(this.transaction, changeInfo.cid, changeInfo.times);
                await this.transaction.delete(this.ctx.service.changeAuditList.tableName, { cid: changeInfo.cid });
                let change_status = false;
                // 获取变更令提交状态
                if (postData.changestatus !== undefined && parseInt(postData.changestatus) === 1) {
                    change_status = true;
                    // 更新原报人审批状态
                    await this.transaction.update(this.ctx.service.changeAudit.tableName, {
                        id: lastUser.id,
                        status: audit.change.status.checked,
                        sin_time: new Date(),
                    });
                }
                // 再插入postData里的变更审批人和清单
                if (postData.changeaudit !== undefined && postData.changeaudit !== '') {
                    const changeAudit = postData.changeaudit.split(',');
                    const insertCA = [];
                    let uSite = 1;
                    let uSort = parseInt(lastUser.usort) + 1;
                    for (const [index, ca] of changeAudit.entries()) {
                        const auditInfo = ca.split('/%/');
                        const uStatus = change_status && index === 0 ? audit.change.status.checking : audit.change.status.uncheck;
                        const sin_time = change_status && index === 0 ? new Date() : null;
                        const caArray = {
                            tid: tenderId,
                            cid: changeInfo.cid,
                            uid: auditInfo[0],
                            name: auditInfo[1],
                            jobs: auditInfo[2],
                            company: auditInfo[3],
                            times: changeInfo.times,
                            usite: uSite,
                            usort: uSort,
                            status: uStatus,
                            sin_time,
                        };
                        uSite++;
                        uSort++;
                        insertCA.push(caArray);

                        // 添加短信通知-需要审批提醒功能
                        if (change_status && index === 0) {
                            const sms = new SMS(this.ctx);
                            const code = await sms.contentChange(changeInfo.code);
                            const shenpiUrl = await this.ctx.helper.urlToShort(
                                this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeInfo.tid + '/change/' + changeInfo.cid + '/information#shenpi'
                            );
                            await this.ctx.helper.sendAliSms(auditInfo[0], smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, {
                                biangeng: code,
                                code: shenpiUrl,
                            });
                            // 微信模板通知
                            const wechatData = {
                                wap_url: shenpiUrl,
                                status: wxConst.status.check,
                                tips: wxConst.tips.check,
                                code: this.ctx.session.sessionProject.code,
                                c_name: changeInfo.name,
                            };
                            await this.ctx.helper.sendWechat(auditInfo[0], smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData);
                        }
                    }
                    await this.transaction.insert(this.ctx.service.changeAudit.tableName, insertCA);
                }
                let changeList = [];
                if (postData.changelist !== undefined && postData.changelist !== '') {
                    changeList = postData.changelist.split('^_^');
                }
                let changeWhiteList = [];
                if (postData.changewhitelist !== undefined && postData.changewhitelist !== '') {
                    changeWhiteList = postData.changewhitelist.split('^_^');
                }
                changeList.push.apply(changeList, changeWhiteList);
                const insertCL = [];
                let total_price = 0;
                if (changeList.length > 0) {
                    for (const cl of changeList) {
                        const clInfo = cl.split('*;*');
                        const clArray = {
                            tid: tenderId,
                            cid: changeInfo.cid,
                            lid: clInfo[8],
                            code: clInfo[0],
                            name: clInfo[1],
                            bwmx: clInfo[2],
                            unit: clInfo[3],
                            unit_price: clInfo[4],
                            oamount: clInfo[5],
                            camount: clInfo[6],
                            samount: '',
                            detail: clInfo[7],
                            spamount: clInfo[6],
                            xmj_code: clInfo[9] !== '' ? clInfo[9] : null,
                            xmj_jldy: clInfo[10] !== '' ? clInfo[10] : null,
                            gcl_id: clInfo[11] !== '' ? clInfo[11] : '',
                        };
                        if (clInfo[4] === '') {
                            delete clArray.unit_price;
                        }
                        insertCL.push(clArray);
                        total_price = this.ctx.helper.accAdd(total_price, this.ctx.helper.mul(clArray.unit_price, clArray.spamount, this.ctx.tender.info.decimal.tp));
                    }
                    await this.transaction.insert(this.ctx.service.changeAuditList.tableName, insertCL);
                }
                // 修改变更令基本数据
                const cArray = {
                    code: postData.code,
                    name: postData.name,
                    peg: postData.peg,
                    org_name: postData.org_name,
                    org_code: postData.org_code,
                    new_name: postData.new_name,
                    new_code: postData.new_code,
                    content: postData.content,
                    basis: postData.basis,
                    expr: postData.expr,
                    memo: postData.memo,
                    type: postData.type.join(','),
                    class: postData.class,
                    quality: postData.quality,
                    company: postData.company,
                    charge: postData.charge,
                    w_code: postData.w_code,
                    total_price,
                    // tp_decimal: this.ctx.tender.info.decimal.tp,
                };
                const options = {
                    where: {
                        cid: changeInfo.cid,
                    },
                };
                if (change_status) {
                    cArray.tp_decimal = this.ctx.tender.info.decimal.tp;
                    cArray.up_decimal = this.ctx.tender.info.decimal.up;
                    cArray.status = audit.change.status.checking;
                    cArray.cin_time = Date.parse(new Date()) / 1000;
                }
                await this.transaction.update(this.tableName, cArray, options);
                await this.transaction.commit();
                result = true;
            } catch (error) {
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 保存变更信息
         * @param {int} postData - 表单提交的数据
         * @param {int} tenderId - 标段id
         * @return {void}
         */
        async saveInfo(postData, delimit = 100) {
            // 初始化事务
            const transaction = await this.db.beginTransaction();
            let result = false;
            try {
                result = {};
                const options = {
                    where: {
                        cid: this.ctx.change.cid,
                    },
                };
                if (postData.state && parseInt(postData.state) !== this.ctx.change.state) {
                    // 变更清单需要同步设置计量上限值
                    // 判断是否存在已调用清单，并判断新上限值是否比它小，更新计量上限值
                    const changeList = await this.ctx.service.changeAuditList.getList(this.ctx.change.cid);
                    // 获取是否已存在调用变更令
                    let changeUsedData = await this.ctx.service.stageChange.getAllFinalUsedData(this.ctx.tender.id, this.ctx.change.cid);
                    changeUsedData = this._.orderBy(this._.filter(changeUsedData, function(item) {
                        return item.qty !== null;
                    }), ['sorder'], ['desc']);
                    const useChangeUsedData = [];
                    if (changeUsedData.length > 0) { // 防止未创建期时调用
                        for (const cu of changeUsedData) {
                            const index = this._.findIndex(useChangeUsedData, { cbid: cu.cbid });
                            if (index !== -1) {
                                useChangeUsedData[index].qty = this.ctx.helper.add(useChangeUsedData[index].qty, cu.qty);
                            } else {
                                useChangeUsedData.push(cu);
                            }
                        }
                    }
                    const updateList = [];
                    for (const cl of changeList) {
                        const one = {
                            id: cl.id,
                        };
                        if (useChangeUsedData.length > 0 && this._.findIndex(useChangeUsedData, { cbid: cl.id }) !== -1) {
                            // 获取比例值
                            const uc = this._.find(useChangeUsedData, { cbid: cl.id });
                            const minLimit = Math.ceil(this.ctx.helper.div(uc.qty, cl.camount) * 100);
                            if (minLimit < delimit) {
                                one.delimit = delimit;
                                cl.delimit = delimit;
                            }
                        } else if (cl.delimit !== delimit) {
                            one.delimit = delimit;
                            cl.delimit = delimit;
                        }
                        if (!this._.isEqual(one, { id: cl.id })) updateList.push(one);
                    }
                    if (updateList.length > 0) await transaction.updateRows(this.ctx.service.changeAuditList.tableName, updateList);
                    result.changeList = changeList;
                    result.deLimit = delimit;
                    if (this.ctx.change.delimit !== delimit) postData.delimit = delimit;
                }

                await transaction.update(this.tableName, postData, options);
                await transaction.commit();
                result.msg = '保存成功';
            } catch (error) {
                await transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 保存变更信息
         * @param {int} order_by - 表单提交的数据
         * @return {void}
         */
        async saveOrderBy(order_by, newLedgerList = []) {
            const transaction = await this.db.beginTransaction();
            let result = [];
            try {
                const postData = { order_by };
                let changeList = await this.ctx.service.changeAuditList.getList(this.ctx.change.cid);
                if (order_by) {
                    let i = 1;
                    const updateArray = [];
                    for (const cl of changeList) {
                        updateArray.push({
                            id: cl.id,
                            order: i,
                        });
                        cl.order = i;
                        i++;
                    }
                    if (updateArray.length > 0) await transaction.updateRows(this.ctx.service.changeAuditList.tableName, updateArray);
                } else {
                    await this.ctx.service.changeAuditList.saveLedgerListDatas(newLedgerList, null, order_by);
                    changeList = await this.ctx.service.changeAuditList.getList(this.ctx.change.cid, order_by);
                }
                const options = {
                    where: {
                        cid: this.ctx.change.cid,
                    },
                };
                await transaction.update(this.tableName, postData, options);
                await transaction.commit();
                result = { changeList,
                    usedList: await this.ctx.service.stageChange.getAllFinalUsedData(this.ctx.tender.id, this.ctx.change.cid) };
            } catch (error) {
                await transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 审批通过
         * @param {Number} pid 项目id
         * @param {int} postData - 表单提交的数据
         * @param {int} changeData - 变更令的数据
         * @return {void}
         */
        async approvalSuccess(pid, postData, changeData) {
            const accountId = this.ctx.session.sessionUser.accountId;
            const time = new Date();
            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                // 判断是否终审方法需要更改了，因为存在修改审批流程的操作，会使判断是否终审有问题
                const audits = await this.ctx.service.changeAudit.getCurAuditors(changeData.cid, changeData.times);
                if (audits.length === 0) throw '审核数据错误';
                const selfAudit = audits.find(x => { return x.uid === accountId; });
                if (!selfAudit) throw '当前标段您无权审批';
                const flowAudits = await this.ctx.service.changeAudit.getAllDataByCondition({ where: { cid: changeData.cid, times: changeData.times, usort: selfAudit.usort } });
                const nextAudits = await this.ctx.service.changeAudit.getAllDataByCondition({ where: { cid: changeData.cid, times: changeData.times, usort: selfAudit.usort + 1 } });
                // 获取所有审核人列表
                const auditors = await this.ctx.service.changeAudit.getAuditGroupByList(changeData.cid, changeData.times);
                // 添加到消息推送表
                const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId, postData.sdesc);
                const records = [];
                auditors.forEach(auditor => {
                    records.push({
                        pid,
                        type: pushType.change,
                        uid: auditor.uid,
                        status: audit.change.status.checked,
                        content: noticeContent,
                    });
                });
                await this.transaction.insert('zh_notice', records);
                // 设置审批人通过
                const audit_update = {
                    id: selfAudit.id,
                    sdesc: postData.sdesc,
                    status: audit.change.status.checked,
                    sin_time: time,
                    end_time: time,
                };
                await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update);
                await this.ctx.service.noticeAgain.stopNoticeAgain(this.transaction, this.ctx.service.changeAudit.tableName, selfAudit.id);
                if (audits.length === 1 || selfAudit.audit_type !== auditType.key.and) {
                    // 或签更新他人审批状态
                    if (selfAudit.audit_type === auditType.key.or) {
                        const updateOther = [];
                        for (const a of audits) {
                            if (a.uid === selfAudit.uid) continue;
                            updateOther.push({
                                id: a.id,
                                status: audit.change.status.checkSkip,
                                sdesc: '',
                                end_time: time,
                                sin_time: time,
                            });
                            await this.ctx.service.noticeAgain.stopNoticeAgain(this.transaction, this.ctx.service.changeAudit.tableName, a.id);
                        }
                        if (updateOther.length > 0) this.transaction.updateRows(this.ctx.service.changeAudit.tableName, updateOther);
                    }
                    const change_update = {
                        w_code: postData.w_code,
                        status: audit.change.status.checking,
                        cin_time: Date.parse(time) / 1000,
                    };
                    // 清单数据更新
                    const bills_list = await this.ctx.service.changeAuditList.getList(changeData.cid, changeData.order_by);
                    let total_price = 0;
                    let valuation_tp = 0;
                    let unvaluation_tp = 0;
                    const tp_decimal = changeData.tp_decimal ? changeData.tp_decimal : this.ctx.tender.info.decimal.tp;
                    const updateList = [];
                    for (const bl of bills_list) {
                        total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(bl.unit_price, bl.spamount, tp_decimal));
                        const audit_amount = bl.audit_amount !== null && bl.audit_amount !== '' ? bl.audit_amount.split(',') : [];
                        audit_amount.push(bl.spamount);
                        const list_update = {
                            id: bl.id,
                            audit_amount: audit_amount.join(','),
                        };
                        if (nextAudits.length === 0) {
                            list_update.samount = bl.spamount;
                            list_update.checked_amount = bl.spamount;
                            list_update.checked_price = this.ctx.helper.mul(bl.unit_price, list_update.checked_amount, tp_decimal);
                            // 统计计价和不计价金额
                            valuation_tp = bl.is_valuation ? this.ctx.helper.add(valuation_tp, list_update.checked_price) : valuation_tp;
                            unvaluation_tp = !bl.is_valuation ? this.ctx.helper.add(unvaluation_tp, list_update.checked_price) : unvaluation_tp;
                        }
                        updateList.push(list_update);
                    }
                    if (updateList.length > 0) await this.transaction.updateRows(this.ctx.service.changeAuditList.tableName, updateList);
                    if (nextAudits.length === 0) {
                        // 变更令审批完成
                        change_update.status = audit.change.status.checked;
                        change_update.p_code = postData.p_code;
                        change_update.sin_time = Date.parse(time) / 1000;
                        change_update.is_revise = 0;

                        await this.ctx.service.tenderTag.saveTenderTag(changeData.tid, { bgl_time: time }, this.transaction);

                        await this.ctx.service.changeAuditList.updateToLedger(this.transaction, changeData.tid, changeData.cid);

                        // 审批通过 - 检查三方特殊推送
                        await this.ctx.service.specMsg.addChangeMsg(this.transaction, this.ctx.session.sessionProject.id, changeData, pushOperate.change.checked);

                        // 添加短信通知-审批通过提醒功能
                        // const mobile_array = [];
                        const users = this._.uniq(this._.map(auditors, 'uid'));
                        const sms = new SMS(this.ctx);
                        const code = await sms.contentChange(changeData.code);
                        await this.ctx.helper.sendAliSms(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), SmsAliConst.template.change_result, {
                            biangeng: code,
                            status: SmsAliConst.status.success,
                        });
                        // 微信模板通知
                        const shenpiUrl = await this.ctx.helper.urlToShort(
                            this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/information#shenpi'
                        );
                        const wechatData = {
                            wap_url: shenpiUrl,
                            status: wxConst.status.success,
                            tips: wxConst.tips.success,
                            code: this.ctx.session.sessionProject.code,
                            c_name: changeData.name,
                        };
                        await this.ctx.helper.sendWechat(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), wxConst.template.change, wechatData);
                    } else {
                        // 设置下一个审批人为审批状态
                        const updateUserData = nextAudits.map(x => { return { id: x.id, status: audit.change.status.checking, begin_time: time, sin_time: time }; });
                        await this.transaction.updateRows(this.ctx.service.changeAudit.tableName, updateUserData);

                        // 添加短信通知-需要审批提醒功能
                        const sms = new SMS(this.ctx);
                        const code = await sms.contentChange(changeData.code);
                        const shenpiUrl = await this.ctx.helper.urlToShort(
                            this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/information#shenpi'
                        );
                        await this.ctx.helper.sendAliSms(this._.map(nextAudits, 'uid'), smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, {
                            biangeng: code,
                            code: shenpiUrl,
                        });
                        // 微信模板通知
                        const wechatData = {
                            wap_url: shenpiUrl,
                            status: wxConst.status.check,
                            tips: wxConst.tips.check,
                            code: this.ctx.session.sessionProject.code,
                            c_name: changeData.name,
                        };
                        await this.ctx.helper.sendWechat(this._.map(nextAudits, 'uid'), smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData);
                        // 重新发送配置
                        for (const nextAudit of nextAudits) {
                            await this.ctx.service.noticeAgain.addNoticeAgain(this.transaction, smsTypeConst.const.BG, {
                                pid: this.ctx.session.sessionProject.id,
                                tid: changeData.tid,
                                uid: nextAudit.uid,
                                sp_type: 'change',
                                sp_id: nextAudit.id,
                                table_name: this.ctx.service.changeAudit.tableName,
                                template: wxConst.template.change,
                                wx_data: wechatData,
                            });
                        }
                    }
                    change_update.total_price = total_price;
                    change_update.valuation_tp = valuation_tp;
                    change_update.unvaluation_tp = unvaluation_tp;
                    const options = {
                        where: {
                            cid: changeData.cid,
                        },
                    };
                    await this.transaction.update(this.tableName, change_update, options);
                }
                await this.transaction.commit();
                result = true;
            } catch (error) {
                console.log(error);
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 审批终止
         * @param {int} postData - 表单提交的数据
         * @return {void}
         */
        async approvalStop(postData) {
            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                // 设置审批人终止
                const audit_update = {
                    id: postData.audit_id,
                    sdesc: postData.sdesc,
                    status: 4,
                    sin_time: new Date(),
                };
                await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update);
                await this.ctx.service.noticeAgain.stopNoticeAgain(this.transaction, this.ctx.service.changeAudit.tableName, postData.audit_id);
                // 设置变更令终止
                const change_update = {
                    w_code: postData.w_code,
                    status: 4,
                    cin_time: Date.parse(new Date()) / 1000,
                };
                const options = {
                    where: {
                        cid: postData.change_id,
                    },
                };
                await this.transaction.update(this.tableName, change_update, options);
                await this.transaction.commit();
                result = true;
            } catch (error) {
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 审批退回到原报人
         * @param {Number} pid 项目id
         * @param {int} postData - 表单提交的数据
         * @param {int} changeData - 变更令的数据
         * @return {void}
         */
        async approvalCheckNo(pid, postData, changeData) {
            const accountId = this.ctx.session.sessionUser.accountId;
            const time = new Date();
            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                // 整理当前流程审核人状态更新
                const audits = await this.ctx.service.changeAudit.getCurAuditors(changeData.cid, changeData.times);
                if (!audits) throw '审核数据错误';
                const selfAudit = audits.find(x => { return x.uid === accountId; });
                if (!selfAudit) throw '当前标段您无权审批';
                const flowAudits = await this.ctx.service.changeAudit.getAllDataByCondition({ where: { cid: changeData.cid, times: selfAudit.times, usort: selfAudit.usort } });

                const auditors = await this.ctx.service.changeAudit.getUniqAuditor(changeData.cid, changeData.times); // 全部参与的审批人
                const newAuditors = auditors.map(x => {
                    return {
                        uid: x.uid, tid: changeData.tid, cid: changeData.cid,
                        times: changeData.times + 1, usite: x.audit_order, usort: x.audit_order, status: x.audit_order === 0 ? audit.change.status.checking : audit.change.status.uncheck,
                        name: x.name, jobs: x.role || x.jobs, company: x.company,
                        begin_time: x.audit_order === 0 ? time : null, sin_time: x.audit_order === 0 ? time : null,
                        audit_type: x.audit_type, audit_order: x.audit_order,
                    };
                });
                // 获取所有审核人列表
                // const auditors = await this.ctx.service.changeAudit.getAllAuditors(changeData.tid);
                // 添加到消息推送表
                const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId, postData.sdesc);
                const records = [];
                auditors.forEach(auditor => {
                    records.push({
                        pid,
                        type: pushType.change,
                        uid: auditor.uid,
                        status: audit.change.status.checkNo,
                        content: noticeContent,
                    });
                });
                await this.transaction.insert('zh_notice', records);
                const updateData = [];
                audits.forEach(x => {
                    updateData.push({
                        id: x.id,
                        status: x.uid === selfAudit.uid ? audit.change.status.checkNo : audit.change.status.checkSkip,
                        sdesc: x.uid === selfAudit.uid ? postData.sdesc : '',
                        end_time: x.uid === selfAudit.uid ? time : null,
                        sin_time: x.uid === selfAudit.uid ? time : null,
                    });
                });
                await this.transaction.updateRows(this.ctx.service.changeAudit.tableName, updateData);
                await this.ctx.service.noticeAgain.stopNoticeAgain(this.transaction, this.ctx.service.changeAudit.tableName, this._.map(updateData, 'id'));
                // 拷贝新一次审核流程列表
                await this.transaction.insert(this.ctx.service.changeAudit.tableName, newAuditors);
                // 变更金额也退回
                const changeList = await this.ctx.service.changeAuditList.getList(changeData.cid, changeData.order_by);
                // 生成内容保存表至zh_change_history中，用于撤回
                await this.ctx.service.changeHistory.saveHistory(this.transaction, changeData, changeList);
                let total_price = 0;
                const tp_decimal = changeData.tp_decimal ? changeData.tp_decimal : this.ctx.tender.info.decimal.tp;
                const updateChangeList = [];
                for (const cl of changeList) {
                    updateChangeList.push({
                        id: cl.id,
                        audit_amount: null,
                        spamount: cl.camount,
                    });
                    total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, cl.camount, tp_decimal));
                }
                if (updateChangeList.length > 0) await this.transaction.updateRows(this.ctx.service.changeAuditList.tableName, updateChangeList);
                // 设置变更令退回
                const change_update = {
                    w_code: postData.w_code,
                    status: audit.change.status.checkNo,
                    times: changeData.times + 1,
                    cin_time: Date.parse(time) / 1000,
                    total_price,
                    tp_decimal: null,
                    up_decimal: null,
                };
                const options = {
                    where: {
                        cid: changeData.cid,
                    },
                };
                await this.transaction.update(this.tableName, change_update, options);

                await this.transaction.commit();
                result = true;
                const users = this._.uniq(this._.map(newAuditors, 'uid'));
                const sms = new SMS(this.ctx);
                const code = await sms.contentChange(changeData.code);
                await this.ctx.helper.sendAliSms(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), SmsAliConst.template.change_result, {
                    biangeng: code,
                    status: SmsAliConst.status.back,
                });
                // 微信模板通知
                const shenpiUrl = await this.ctx.helper.urlToShort(
                    this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/information#shenpi'
                );
                const wechatData = {
                    wap_url: shenpiUrl,
                    status: wxConst.status.back,
                    tips: wxConst.tips.back,
                    code: this.ctx.session.sessionProject.code,
                    c_name: changeData.name,
                };
                await this.ctx.helper.sendWechat(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), wxConst.template.change, wechatData);
            } catch (error) {
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 审批退回到上一个审批人
         * @param {Number} pid 项目id
         * @param {int} postData - 表单提交的数据
         * @param {int} changeData - 变更令的数据
         * @return {void}
         */
        async approvalCheckNoPre(pid, postData, changeData) {
            // 初始化事务
            const accountId = this.ctx.session.sessionUser.accountId;
            const time = new Date();
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const audits = await this.ctx.service.changeAudit.getCurAuditors(changeData.cid, changeData.times);
                if (audits.length === 0 || audits[0].usite <= 1) throw '审核数据错误';
                const selfAudit = audits.find(x => { return x.uid === accountId; });
                if (!selfAudit) throw '当前标段您无权审批';
                const flowAudits = await this.ctx.service.changeAudit.getAllDataByCondition({ where: { cid: changeData.cid, times: changeData.times, usort: selfAudit.usort } });
                const auditors = await this.ctx.service.changeAudit.getAuditGroupByList(changeData.cid, changeData.times);
                const preAuditors = auditors.filter(x => { return x.audit_order === selfAudit.audit_order - 1; });
                // 添加到消息推送表
                const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId, postData.sdesc);
                const records = [];
                auditors.forEach(auditor => {
                    records.push({
                        pid,
                        type: pushType.change,
                        uid: auditor.uid,
                        status: audit.change.status.checkNoPre,
                        content: noticeContent,
                    });
                });
                await this.transaction.insert('zh_notice', records);
                // 设置审批人退回
                const updateData = [];
                for (const a of audits) {
                    if (a.uid === selfAudit.uid) {
                        updateData.push({
                            id: a.id, status: audit.change.status.checkNoPre, sdesc: postData.sdesc, end_time: time, sin_time: time,
                        });
                    } else {
                        updateData.push({
                            id: a.id, status: audit.change.status.checkSkip, sdesc: '', end_time: null,
                        });
                    }
                }
                await this.transaction.updateRows(this.ctx.service.changeAudit.tableName, updateData);
                await this.ctx.service.noticeAgain.stopNoticeAgain(this.transaction, this.ctx.service.changeAudit.tableName, this._.map(updateData, 'id'));
                // 顺移其后审核人流程顺序
                const sql = 'UPDATE ' + this.ctx.service.changeAudit.tableName + ' SET `usort` = `usort` + 2 WHERE cid = ? AND times = ? AND `usort` > ?';
                await this.transaction.query(sql, [changeData.cid, selfAudit.times, selfAudit.usort]);
                // 上一审批人,当前审批人 再次添加至流程
                const newAuditors = [];
                preAuditors.forEach(x => {
                    newAuditors.push({
                        tid: changeData.tid, cid: changeData.cid, uid: x.uid,
                        times: x.times, usort: selfAudit.usort + 1, usite: x.usite,
                        status: audit.change.status.checking, begin_time: time,
                        name: x.name, jobs: x.role || x.jobs, company: x.company,
                        audit_type: x.audit_type, audit_order: x.audit_order,
                    });
                });
                // 获取ids值
                const newAuditors_result = await this.transaction.insert(this.ctx.service.changeAudit.tableName, newAuditors);
                // 获取刚批量添加的所有list
                for (let j = 0; j < preAuditors.length; j++) {
                    newAuditors[j].id = newAuditors_result.insertId + j;
                }
                const newFlowAuditors = [];
                flowAudits.forEach(x => {
                    newFlowAuditors.push({
                        tid: changeData.tid, cid: changeData.cid, uid: x.uid,
                        times: x.times, usort: selfAudit.usort + 2, usite: x.usite,
                        status: audit.change.status.uncheck,
                        name: x.name, jobs: x.role || x.jobs, company: x.company,
                        audit_type: x.audit_type, audit_order: x.audit_order,
                    });
                });
                await this.transaction.insert(this.ctx.service.changeAudit.tableName, newFlowAuditors);
                // 审批列表数据也要回退
                const changeList = await this.ctx.service.changeAuditList.getList(changeData.cid, changeData.order_by);
                let total_price = 0;
                const tp_decimal = changeData.tp_decimal ? changeData.tp_decimal : this.ctx.tender.info.decimal.tp;
                const updateList = [];
                for (const cl of changeList) {
                    const audit_amount = cl.audit_amount.split(',');
                    const last_amount = audit_amount.length - 1 !== 0 && audit_amount[audit_amount.length - 1] ? audit_amount[audit_amount.length - 1] : cl.camount;
                    audit_amount.splice(-1, 1);
                    const list_update = {
                        id: cl.id,
                        audit_amount: audit_amount.join(','),
                        spamount: parseFloat(last_amount),
                    };
                    total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, parseFloat(last_amount), tp_decimal));
                    updateList.push(list_update);
                }
                if (updateList.length > 0) await this.transaction.updateRows(this.ctx.service.changeAuditList.tableName, updateList);

                // 设置变更令退回
                const change_update = {
                    w_code: postData.w_code,
                    status: audit.change.status.checkNoPre,
                    cin_time: Date.parse(new Date()) / 1000,
                    total_price,
                };
                const options = {
                    where: {
                        cid: changeData.cid,
                    },
                };
                await this.transaction.update(this.tableName, change_update, options);
                const sms = new SMS(this.ctx);
                const code = await sms.contentChange(changeData.code);
                const shenpiUrl = await this.ctx.helper.urlToShort(
                    this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/information#shenpi'
                );
                await this.ctx.helper.sendAliSms(this._.map(preAuditors, 'uid'), smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, {
                    biangeng: code,
                    code: shenpiUrl,
                });
                // 微信模板通知
                const wechatData = {
                    wap_url: shenpiUrl,
                    status: wxConst.status.check,
                    tips: wxConst.tips.check,
                    code: this.ctx.session.sessionProject.code,
                    c_name: changeData.name,
                };
                await this.ctx.helper.sendWechat(this._.map(preAuditors, 'uid'), smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData);
                // 重新发送配置
                for (const a of newAuditors) {
                    await this.ctx.service.noticeAgain.addNoticeAgain(this.transaction, smsTypeConst.const.BG, {
                        pid: this.ctx.session.sessionProject.id,
                        tid: changeData.tid,
                        uid: a.uid,
                        sp_type: 'change',
                        sp_id: a.id,
                        table_name: this.ctx.service.changeAudit.tableName,
                        template: wxConst.template.change,
                        wx_data: wechatData,
                    });
                }
                await this.transaction.commit();
                result = true;
            } catch (error) {
                console.log(error);
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 查询可用的变更令
         * @param bills - 查询的清单
         * @param pos - 查询的部位
         * @return {Promise<*>} - 可用的变更令列表
         */
        async getValidChanges(tender, stage, data, noValue) {
            const bills = data.bills,
                pos = data.pos;
            const self = this;
            const getFilterPart = function(field, value) {
                return value
                    ? field + ' = ' + self.db.escape(value)
                    : self.db.format("(?? = null or ?? = '')", [field, field]);
            };
            const filter = getFilterPart('cb.code', bills.b_code) +
                ' And ' + getFilterPart('cb.name', bills.name) +
                ' And ' + getFilterPart('cb.unit', bills.unit) +
                ' And cb.`unit_price` = ' + this.db.escape(bills.unit_price);
            // if (data.pos) filter = filter + ' And ' + getFilterPart('cb.bwmx', pos.name);
            const sql =
                'SELECT c.cid, c.code, c.name, c.w_code, c.p_code, c.peg, c.org_name, c.org_code, c.new_name, c.new_code,' +
                '    c.content, c.basis, c.memo, c.type, c.class, c.quality, c.company, c.charge,' +
                '    cb.id As cbid, cb.code As b_code, cb.name As b_name, cb.unit As b_unit, cb.samount As b_amount, cb.detail As b_detail, cb.bwmx As b_bwmx, cb.gcl_id, cb.is_valuation, cb.delimit,' +
                '    scb.used_amount' +
                '  FROM ' + this.tableName + ' As c ' +
                '  Left Join ' + this.ctx.service.changeAuditList.tableName + ' As cb On c.cid = cb.cid ' +
                '  Left Join (' +
                '    SELECT SUM(qty) As used_amount, cbid' +
                '      FROM ' + this.ctx.service.stageChangeFinal.tableName +
                '        WHERE tid = ? and sorder < ?' +
                '        GROUP BY cbid' +
                '  ) As scb ON cb.id = scb.cbid' +
                '  WHERE c.tid = ? And c.status = ? And c.valid And ' + filter +
                '  ORDER BY c.in_time';
            const sqlParam = [tender.id, stage.order, tender.id, audit.change.status.checked];
            let changes = await this.db.query(sql, sqlParam);
            if (noValue) {
                if (data.noValue) {
                    changes = changes.filter(c => {
                        return !c.is_valuation;
                    });
                } else {
                    changes = changes.filter(c => {
                        return c.is_valuation;
                    });
                }
            }
            for (const c of changes) {
                let sSql = 'SELECT * FROM ' + this.ctx.service.stageChange.tableName + ' WHERE sid = ? and cbid = ?';
                const sSqlParam = [stage.id, c.cbid];
                if (stage.readOnly) {
                    sSql = sSql + ' and (stimes < ? or (stimes = ? and sorder <= ?))';
                    sSqlParam.push(stage.curTimes, stage.curTimes, stage.curOrder);
                }
                const stageUsedData = await this.db.query(sSql, sSqlParam);
                const filter = this.ctx.helper.filterLastestData(stageUsedData, ['pid', 'lid', 'no_value'], 'stimes', 'sorder');
                c.stage_used_amount = this.ctx.helper.sum(filter.map(x => { return x.qty; }));
                const aSql = 'SELECT ca.*, pa.name As u_name, pa.role As u_role ' +
                    '  FROM ?? As ca ' +
                    '  Left Join ?? As pa ' +
                    '  On ca.uid = pa.id ' +
                    '  Where ca.cid = ?';
                const aSqlParam = [this.ctx.service.changeAtt.tableName, this.ctx.service.projectAccount.tableName, c.cid];
                c.attachments = await this.db.query(aSql, aSqlParam);
            }
            return changes;
        }

        /**
         * 查询变更令 + 变更令执行
         * @param tid
         * @return {Promise<void>}
         */
        async getChangeAndUsedInfo(tid) {
            const lastStage = await this.ctx.service.stage.getLastestStage(tid, true);
            let filter;
            if (lastStage.id === this.ctx.stage.id) {
                filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And (sChange.`stimes` < ? OR (sChange.`stimes` = ? And sChange.`sorder` <= ?))))', [
                    lastStage.order,
                    lastStage.order,
                    this.ctx.stage.curTimes,
                    this.ctx.stage.curTimes,
                    this.ctx.stage.curOrder,
                ]);
            } else {
                if (lastStage.status === audit.stage.status.uncheck) {
                    filter = ' And s.order < ' + lastStage.order;
                } else if (lastStage.status === audit.stage.status.checked) {
                    filter = '';
                } else if (lastStage.status === audit.stage.status.checkNo) {
                    filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And sChange.`stimes` <= ?))', [lastStage.order, lastStage.order, lastStage.times]);
                } else {
                    const curAuditor = await this.ctx.service.stageAudit.getCurAuditor(lastStage.id, lastStage.times);
                    filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And (sChange.`stimes` < ? OR (sChange.`stimes` = ? And sChange.`sorder` <= ?))))', [
                        lastStage.order,
                        lastStage.order,
                        lastStage.times,
                        lastStage.times,
                        curAuditor.order - 1,
                    ]);
                }
            }
            const sql =
                'SELECT C.*, Sum(U.utp) As used_tp, TRUNCATE(Sum(U.utp) / C.total_price * 100 + 0.005, 2) As used_pt' +
                '  FROM ' + this.tableName + ' As C' +
                '  LEFT JOIN (SELECT sc.tid, sc.cid, sc.cbid, IF(SUM(sc.qty) > 0, TRUNCATE(SUM(sc.qty) * cb.unit_price + ?, ?), TRUNCATE(SUM(sc.qty) * cb.unit_price - ?, ?)) As utp,' +
                '      IF(SUM(sc.qty) > 0, TRUNCATE(SUM(sc.qty) * cb.unit_price + ?, ?), 0, ?)) As utp_po, IF(SUM(sc.qty) > 0, 0, TRUNCATE(SUM(sc.qty) * cb.unit_price - ?, ?)) As utp_ne' +
                '    FROM ' + this.ctx.service.stageChange.tableName + ' As sc' +
                '    INNER JOIN (' +
                '      SELECT MAX(`stimes`) As `stimes`, MAX(`sorder`) As `sorder`, `lid`, `pid`, `cbid`, sChange.`sid` ' +
                '        FROM ' + this.ctx.service.stageChange.tableName + ' As sChange ' +
                '        LEFT JOIN ' + this.ctx.service.stage.tableName + ' As s' +
                '        ON sChange.sid = s.id' +
                '        WHERE sChange.tid = ?' + filter +
                '        GROUP By `lid`, `pid`, `cbid`, `sid`' +
                '    ) As m' +
                '    ON sc.stimes = m.stimes And sc.sorder = m.sorder And sc.`cbid` = m.`cbid` AND sc.`sid` = m.`sid` And sc.`lid` = m.`lid` And sc.`pid` = m.`pid`' +
                '    LEFT JOIN ' + this.ctx.service.changeAuditList.tableName +
                ' As cb ON sc.cbid = cb.id' +
                '    GROUP By sc.`cbid`' +
                '  ) As U ON C.cid = U.cid' +
                '  WHERE C.tid = ? And C.status = ? And C.valid' +
                '  GROUP By C.cid' +
                '  ORDER By in_time';
            // 舍入步长
            const step = parseFloat('0.' + '0000000'.substr(0, this.ctx.tender.info.decimal.tp) + '5');
            const sqlParam = [step, this.ctx.tender.info.decimal.tp, step, this.ctx.tender.info.decimal.tp, tid, tid, audit.change.status.checked];
            const data = await this.db.query(sql, sqlParam);
            return data;
        }

        /**
         * 查询可用的变更令
         * @param { string } cid - 查询的清单
         * @return {Promise<*>} - 可用的变更令列表
         */
        async delete(cid) {
            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const changeInfo = await this.getDataByCondition({ cid });
                // 先删除清单，审批人列表
                await this.transaction.delete(this.ctx.service.changeAuditList.tableName, { cid });
                await this.transaction.delete(this.ctx.service.changeAudit.tableName, { cid });
                // 再删除附件和附件文件ni zuo
                const attList = await this.ctx.service.changeAtt.getAllDataByCondition({ where: { cid } });
                await this.ctx.helper.delFiles(attList);
                await this.transaction.delete(this.ctx.service.changeAtt.tableName, { cid });
                await this.transaction.delete(this.ctx.service.changeHistory.tableName, { cid });
                // if (attList.length !== 0) {
                //     for (const att of attList) {
                //         await fs.unlinkSync(path.join(this.app.baseDir, att.filepath));
                //     }
                //     await this.transaction.delete(this.ctx.service.changeAtt.tableName, { cid });
                // }
                // 最后删除变更令
                await this.transaction.delete(this.tableName, { cid });
                // 记录删除日志
                await this.ctx.service.projectLog.addProjectLog(this.transaction, projectLogConst.type.change, projectLogConst.status.delete, changeInfo.code);
                await this.transaction.commit();
                result = true;
            } catch (e) {
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 重新审批变更令
         * @param { string } cid - 查询的清单
         * @return {Promise<*>} - 可用的变更令列表
         */
        async checkRevise(change) {
            // 初始化事务
            const accountId = this.ctx.session.sessionUser.accountId;
            const time = new Date();
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const pid = this.ctx.session.sessionProject.id;
                const auditors = await this.ctx.service.changeAudit.getUniqAuditor(change.cid, change.times); // 全部参与的审批人
                // 添加到消息推送表
                const noticeContent = await this.getNoticeContent(pid, change.tid, change.cid, this.ctx.session.sessionUser.accountId, '发起重新审批');
                const records = [];
                auditors.forEach(auditor => {
                    records.push({
                        pid,
                        type: pushType.change,
                        uid: auditor.uid,
                        status: audit.change.status.revise,
                        content: noticeContent,
                    });
                });
                await this.transaction.insert('zh_notice', records);
                const order = change.auditors.length > 0 ? change.auditors.reduce((rst, a) => { return Math.max(rst, a.usort); }, 0) + 1 : 1; // 最大值 + 1
                // 当前审批人添加至流程中
                const checkReviseAuditor = {
                    tid: change.tid, cid: change.cid, uid: change.uid,
                    name: auditors[0].name, jobs: auditors[0].role || auditors[0].jobs, company: auditors[0].company,
                    times: change.times, usite: 0, usort: order,
                    status: audit.change.status.revise,
                    begin_time: time, end_time: time, sin_time: new Date(),
                    audit_type: auditType.key.common, audit_order: 0,
                };
                await this.transaction.insert(this.ctx.service.changeAudit.tableName, checkReviseAuditor);
                // 修订人加入审批流程
                const newAuditors = auditors.map(x => {
                    return {
                        uid: x.uid, tid: change.tid, cid: change.cid,
                        times: change.times + 1, usite: x.audit_order, usort: x.audit_order, status: x.audit_order === 0 ? audit.change.status.checking : audit.change.status.uncheck,
                        name: x.name, jobs: x.role || x.jobs, company: x.company,
                        begin_time: x.audit_order === 0 ? time : null, sin_time: x.audit_order === 0 ? time : null,
                        audit_type: x.audit_type, audit_order: x.audit_order,
                    };
                });
                await this.transaction.insert(this.ctx.service.changeAudit.tableName, newAuditors);

                const changeList = await this.ctx.service.changeAuditList.getList(change.cid, change.order_by);
                // 生成内容保存表至zh_change_history中，用于撤销修订回退
                const changeData = await this.getDataByCondition({ cid: change.cid });
                await this.ctx.service.changeHistory.saveHistory(this.transaction, changeData, changeList);
                // 申请变更金额更新为上一次审批审批变更后数量,清空audit_amount值
                const updateTpList = [];
                const readySettle = await this.ctx.service.settle.getReadySettle(change.tid);
                const settleBills = readySettle ? await this.ctx.service.settleBills.getAllDataByCondition({ where: { settle_id: readySettle.id } }) : [];
                const settlePos = readySettle ? await this.ctx.service.settlePos.getAllDataByCondition({ where: { settle_id: readySettle.id } }) : [];
                const insertSettleList = [];
                for (const cl of changeList) {
                    let settleInfo = null;
                    if (cl.gcl_id) {
                        settleInfo = cl.mx_id ? this._.find(settlePos, { lid: cl.gcl_id, pid: cl.mx_id }) : this._.find(settleBills, { lid: cl.gcl_id });
                        if (settleInfo) {
                            insertSettleList.push({
                                tid: changeData.tid,
                                cid: changeData.cid,
                                gcl_id: cl.gcl_id,
                                mx_id: cl.mx_id,
                                amount: cl.checked_amount,
                            });
                        }
                    }
                    updateTpList.push({ id: cl.id, camount: cl.spamount, audit_amount: null, samount: settleInfo ? cl.spamount : '' });
                }
                if (updateTpList.length > 0) {
                    await this.transaction.updateRows(this.ctx.service.changeAuditList.tableName, updateTpList);
                }
                // 设置变更令修订状态
                const change_update = {
                    w_code: change.w_code,
                    status: audit.change.status.revise,
                    times: change.times + 1,
                    cin_time: Date.parse(time) / 1000,
                    // total_price,
                    tp_decimal: null,
                    up_decimal: null,
                    is_revise: 1,
                    final_auditor_str: '',
                    valuation_tp: 0,
                    unvaluation_tp: 0,
                };
                const options = {
                    where: {
                        cid: change.cid,
                    },
                };
                await this.transaction.update(this.tableName, change_update, options);
                // 添加已结算清单到change_settle_list表中，防止删除
                await this.transaction.delete(this.ctx.service.changeSettleList.tableName, { cid: change.cid });
                if (insertSettleList.length > 0) await this.transaction.insert(this.ctx.service.changeSettleList.tableName, insertSettleList);

                await this.transaction.commit();
                result = true;
            } catch (error) {
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 撤销修订变更令
         * @param { string } cid - 查询的清单
         * @return {Promise<*>} - 可用的变更令列表
         */
        async cancelRevise(cid, times) {
            // 初始化事务
            const time = new Date();
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const changeData = await this.getDataByCondition({ cid });
                const pid = this.ctx.session.sessionProject.id;
                // 获取所有审核人列表
                const auditors = await this.ctx.service.changeAudit.getUniqAuditor(cid, times);
                // 添加到消息推送表
                const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId, '撤销修订');
                const records = [];
                auditors.forEach(auditor => {
                    records.push({
                        pid,
                        type: pushType.change,
                        uid: auditor.uid,
                        status: audit.change.status.checked,
                        content: noticeContent,
                    });
                });
                await this.transaction.insert('zh_notice', records);
                await this.ctx.service.changeHistory.returnHistory(this.transaction, cid);
                await this.transaction.delete(this.ctx.service.changeAudit.tableName, { cid, times });
                await this.transaction.delete(this.ctx.service.changeHistory.tableName, { cid });

                const lastauditInfo = await this.ctx.service.changeAudit.getLastUser(cid, times - 1, 1, 0);
                // 新增一个撤销修订状态到审批流程中
                const revise_audit = {
                    tid: lastauditInfo.tid, cid, uid: this.ctx.session.sessionUser.accountId,
                    name: lastauditInfo.name, jobs: lastauditInfo.jobs, company: lastauditInfo.company,
                    times: times - 1, usite: 0, usort: lastauditInfo.usort + 1,
                    begin_time: time, end_time: time, sin_time: time,
                    status: audit.change.status.cancelRevise,
                    audit_type: lastauditInfo.audit_type, audit_order: lastauditInfo.audit_order,
                };
                await this.transaction.insert(this.ctx.service.changeAudit.tableName, revise_audit);
                await this.transaction.commit();
                result = true;
            } catch (error) {
                console.log(error);
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 重新审批变更令
         * @param { string } cid - 查询的清单
         * @return {Promise<*>} - 可用的变更令列表
         */
        async checkAgain(change) {
            const accountId = this.ctx.session.sessionUser.accountId;
            // 初始化事务
            const time = new Date();
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                // 这里要排除usite为0的
                const noYbAuditors = change.auditors.filter(x => { return x.usite !== 0; });
                const noYbMaxOrder = noYbAuditors[noYbAuditors.length - 1].usort;
                const maxOrder = change.auditors[change.auditors.length - 1].usort;
                const audits = change.auditors.filter(x => { return x.usort === noYbMaxOrder; });
                if (!audits || audits.length === 0 || maxOrder < 1) throw '审核数据错误';
                const selfAudit = audits.find(x => { return x.uid === accountId; });
                if (!selfAudit) throw '当前标段您无权审批';
                const finalAudit = selfAudit || audits[0];
                // 当前审批人2次添加至流程中
                const checkAgainAuditors = [];
                audits.forEach(x => {
                    checkAgainAuditors.push({
                        tid: change.tid, cid: change.cid, uid: x.uid,
                        times: x.times, usite: x.usite, usort: maxOrder + 1,
                        status: !selfAudit || x.uid === selfAudit.uid ? audit.change.status.checkAgain : audit.change.status.checkSkip,
                        begin_time: time, end_time: time, sin_time: time, sdesc: '',
                        name: x.name, jobs: x.role || x.jobs, company: x.company,
                        audit_type: x.audit_type, audit_order: x.audit_order,
                    });
                });
                const checkingAuditors = [];
                audits.forEach(x => {
                    checkingAuditors.push({
                        tid: change.tid, cid: change.cid, uid: x.uid,
                        times: x.times, usite: x.usite, usort: maxOrder + 2,
                        status: audit.change.status.checking,
                        begin_time: time, sin_time: time, sdesc: '',
                        name: x.name, jobs: x.role || x.jobs, company: x.company,
                        audit_type: x.audit_type, audit_order: x.audit_order,
                    });
                });
                await this.transaction.insert(this.ctx.service.changeAudit.tableName, checkAgainAuditors);
                const checkingAuditors_result = await this.transaction.insert(this.ctx.service.changeAudit.tableName, checkingAuditors);
                // 获取刚批量添加的所有list
                for (let j = 0; j < checkingAuditors.length; j++) {
                    checkingAuditors[j].id = checkingAuditors_result.insertId + j;
                }

                // 审批列表数据也要回退
                let total_price = 0;
                const tp_decimal = change.tp_decimal ? change.tp_decimal : this.ctx.tender.info.decimal.tp;
                const changeList = await this.ctx.service.changeAuditList.getList(change.cid);
                const updateChangeList = [];
                const readySettle = await this.ctx.service.settle.getReadySettle(change.tid);
                const settleBills = readySettle ? await this.ctx.service.settleBills.getAllDataByCondition({ where: { settle_id: readySettle.id } }) : [];
                const settlePos = readySettle ? await this.ctx.service.settlePos.getAllDataByCondition({ where: { settle_id: readySettle.id } }) : [];
                const insertSettleList = [];
                for (const cl of changeList) {
                    if (cl.gcl_id) {
                        const settleInfo = cl.mx_id ? this._.find(settlePos, { lid: cl.gcl_id, pid: cl.mx_id }) : this._.find(settleBills, { lid: cl.gcl_id });
                        if (settleInfo) {
                            insertSettleList.push({
                                tid: change.tid,
                                cid: change.cid,
                                gcl_id: cl.gcl_id,
                                mx_id: cl.mx_id,
                                amount: cl.checked_amount,
                            });
                        }
                    }
                    const audit_amount = cl.audit_amount.split(',');
                    const last_amount = audit_amount[audit_amount.length - 1] ? audit_amount[audit_amount.length - 1] : 0;
                    audit_amount.splice(-1, 1);
                    const list_update = {
                        id: cl.id,
                        audit_amount: audit_amount.join(','),
                        samount: '',
                        spamount: parseFloat(last_amount),
                    };
                    total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, parseFloat(last_amount), tp_decimal));
                    updateChangeList.push(list_update);
                }
                if (updateChangeList.length > 0) await this.transaction.updateRows(this.ctx.service.changeAuditList.tableName, updateChangeList);

                // 设置变更令审批中
                const change_update = {
                    p_code: null,
                    status: audit.change.status.checking,
                    cin_time: Date.parse(time) / 1000,
                    sin_time: null,
                    total_price,
                    final_auditor_str: '',
                    valuation_tp: 0,
                    unvaluation_tp: 0,
                };
                const options = {
                    where: {
                        cid: change.cid,
                    },
                };
                await this.transaction.update(this.tableName, change_update, options);

                // 添加已结算清单到change_settle_list表中，防止删除
                await this.transaction.delete(this.ctx.service.changeSettleList.tableName, { cid: change.cid });
                if (insertSettleList.length > 0) await this.transaction.insert(this.ctx.service.changeSettleList.tableName, insertSettleList);
                await this.transaction.commit();
                result = true;
                // const sms = new SMS(this.ctx);
                // const code = await sms.contentChange(changeInfo.code);
                // const shenpiUrl = await this.ctx.helper.urlToShort(
                //     this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeInfo.tid + '/change/' + changeInfo.cid + '/information#shenpi'
                // );
                // await this.ctx.helper.sendAliSms(zsAudit.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, {
                //     biangeng: code,
                //     code: shenpiUrl,
                // });
                //
                // // 微信模板通知
                // const wechatData = {
                //     wap_url: shenpiUrl,
                //     status: wxConst.status.check,
                //     tips: wxConst.tips.check,
                //     code: this.ctx.session.sessionProject.code,
                //     c_name: changeInfo.name,
                // };
                // await this.ctx.helper.sendWechat(zsAudit.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData);

            } catch (error) {
                console.log(error);
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 判断是否有重名的变更令
         * @param cid
         * @param code
         * @param tid
         * @return {Promise<void>}
         */
        async isRepeat(cid, code, tid) {
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE ((`code` = ? AND `status` != ?) OR (`p_code` = ? AND `status` = ?)) AND `cid` != ? AND `tid` = ?';
            const sqlParam = [this.tableName, code, audit.change.status.checked, code, audit.change.status.checked, cid, tid];
            const result = await this.db.queryOne(sql, sqlParam);
            return result.count !== 0;
        }

        async getAllCheckedChanges(tid) {
            return await this.getAllDataByCondition({
                where: { tid, status: audit.change.status.checked },
                orders: [['in_time', 'desc']],
            });
        }

        /**
         * 用于添加推送所需的content内容
         * @param {Number} pid 项目id
         * @param {Number} tid 台账id
         * @param {Number} cid 变更id
         * @param {Number} uid 审核人id
         */
        async getNoticeContent(pid, tid, cid, uid, opinion = '') {
            const noticeSql =
                'SELECT * FROM (SELECT ' +
                '  t.`id` As `tid`, t.`name`, c.`cid`, c.`code` As `c_code`, pa.`name` As `su_name`, pa.role As `su_role`' +
                '  FROM (SELECT * FROM ?? WHERE `id` = ? ) As t' +
                '  LEFT JOIN ?? As c ON c.`cid` = ?' +
                '  LEFT JOIN ?? As pa ON pa.`id` = ?' +
                '  WHERE  t.`project_id` = ? ) as new_t GROUP BY new_t.`tid`';
            const noticeSqlParam = [this.ctx.service.tender.tableName, tid, this.ctx.service.change.tableName, cid, this.ctx.service.projectAccount.tableName, uid, pid];
            const content = await this.db.query(noticeSql, noticeSqlParam);
            if (content.length) {
                content[0].opinion = opinion;
            }
            return content.length ? JSON.stringify(content[0]) : '';
        }

        /**
         * 获取当前标段其他变更令
         * @param {Number} tid - 标段id
         * @param {Number} cid - 当前变更令
         */
        async getOthersChange(tid, cid) {
            const sql = 'SELECT * FROM ?? WHERE tid = ? AND cid != ? ORDER By `in_time` desc ';
            const sqlParam = [this.tableName, tid, cid];
            const data = await this.db.query(sql, sqlParam);
            const changeClassObj = {};
            for (const c in changeConst.class) {
                if (changeConst.class.hasOwnProperty(c)) {
                    const item = changeConst.class[c];
                    changeClassObj[item.value] = item.name;
                }
            }
            for (let i = 0; i < data.length; i++) {
                data[i].class = changeClassObj[data[i].class];
            }
            return data;
        }

        /**
         * 拷贝变更令至当前变更令
         * @param {String} cid - 当前变更令
         * @param {String} copy_cid - 要拷贝的变更令
         */
        async handleCopyChange(cid, copy_cid) {
            // const change = await this.getDataByCondition({ cid });
            const copyChange = await this.getDataByCondition({ cid: copy_cid });
            return await this.update({ peg: copyChange.peg, org_name: copyChange.org_name, org_code: copyChange.org_code, new_name: copyChange.new_name, content: copyChange.content, basis: copyChange.basis, expr: copyChange.expr, memo: copyChange.memo, type: copyChange.type,
                class: copyChange.class, quality: copyChange.quality, company: copyChange.company, charge: copyChange.charge, new_code: copyChange.new_code }, {
                cid,
            });
        }

        async updateDecimalAndTp() {
            // 判断是否可修改
            // 判断t_type是否为费用
            const transaction = await this.db.beginTransaction();
            try {
                await this.ctx.service.changeAuditList.calcCamountSum(transaction, true);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async updateChangeSelect(data) {
            const conn = await this.db.beginTransaction();
            try {
                for (const d of data) {
                    await conn.update(this.tableName, { selected: d.selected }, { where: { cid: d.cid } });
                }
                await conn.commit();
            } catch (err) {
                await conn.rollback();
                throw err;
            }
        }

        async getAllChangeHasMinus(tenderId) {
            const sql = `SELECT * FROM ${this.tableName} WHERE tid = ? AND negative_tp < 0 ORDER BY code asc`;
            return this.db.query(sql, [tenderId]);
        }

        async getListByBudgetInfo(tenders) {
            if (tenders) {
                const sql = 'SELECT * FROM ?? WHERE `status` = ? AND `tid` in (' + this.ctx.helper.getInArrStrSqlFilter(tenders.split(',')) + ') ORDER BY `total_price` DESC LIMIT 0, 10';
                const params = [this.tableName, audit.change.status.checked];
                return await this.db.query(sql, params);
            }
            return [];
        }

        async getTotalTpByBudgetInfo(tenders) {
            if (tenders) {
                const sql = 'SELECT SUM(`total_price`) AS tp FROM ?? WHERE `status` = ? AND `tid` in (' + this.ctx.helper.getInArrStrSqlFilter(tenders.split(',')) + ')';
                const sqlParam = [this.tableName, audit.change.status.checked];
                const result = await this.db.queryOne(sql, sqlParam);
                return result && result.tp ? result.tp : 0;
            }
            return 0;
        }

        async doCheckChangeCanCancel(change) {
            // 获取当前审批人的上一个审批人，判断是否是当前登录人，并赋予撤回功能，（当审批人存在有审批过时，上一人不允许再撤回）
            const status = audit.change.status;
            const accountId = this.ctx.session.sessionUser.accountId;
            change.cancancel = 0;
            if (change.status !== status.checked && change.status !== status.uncheck && change.status !== status.revise) {
                if (change.status !== status.checkNo) {
                    // 找出当前操作人上一个审批人，包括审批完成的和退回上一个审批人的，同时当前操作人为第一人时，就是则为原报
                    if (change.flowAuditors.find(x => { return x.status !== status.checking; }) && change.flowAuditorIds.indexOf(accountId) < 0) return; // 当前流程存在审批人审批通过时，不可撤回
                    if (change.curAuditorIds.indexOf(accountId) < 0 && change.flowAuditorIds.indexOf(accountId) >= 0) {
                        change.cancancel = 5; // 会签未全部审批通过时，审批人撤回审批通过
                        return;
                    }
                    const preAuditors = change.curAuditors[0].usort !== 1 ? change.auditors.filter(x => { return x.usort === change.curAuditors[0].usort - 1; }) : [];
                    const preAuditorCheckAgain = preAuditors.find(pa => { return pa.status === status.checkAgain; });
                    // const preAuditorRevise = preAuditors.find(pa => { return pa.status === status.revise; });
                    // const preAuditorCancelRevise = preAuditors.find(pa => { return pa.status === status.cancelRevise; });
                    const preAuditorCheckCancel = preAuditors.find(pa => { return pa.status === status.checkCancel; });
                    const preAuditorIds = preAuditorCheckAgain ? [] : preAuditors.map(x => { return x.uid; }); // 重审不可撤回
                    if ((this._.isEqual(change.flowAuditorIds, preAuditorIds) && preAuditorCheckCancel)) {
                        return; // 不可以多次撤回
                    }

                    const preAuditChecked = preAuditors.find(pa => { return pa.status === status.checked && pa.uid === accountId; });
                    const preAuditCheckNoPre = preAuditors.find(pa => { return pa.status === status.checkNoPre && pa.uid === accountId; });
                    if (preAuditorIds.indexOf(accountId) >= 0) {
                        if (preAuditChecked && change.status === status.checking) {
                            change.cancancel = 2;// 审批人撤回审批通过
                        } else if (preAuditCheckNoPre && change.status === status.checkNoPre) {
                            change.cancancel = 3;// 审批人撤回审批退回上一人
                        }
                        change.preAuditors = preAuditors;
                    } else if (preAuditors.length === 0 && accountId === change.uid) {
                        change.cancancel = 1;// 原报撤回
                    }
                } else {
                    const lastAuditors = await this.service.changeAudit.getAuditors(change.cid, change.times - 1);
                    const onAuditor = this._.findLast(lastAuditors, { status: status.checkNo });
                    if (onAuditor && onAuditor.uid === accountId) {
                        change.cancancel = 4;// 审批人撤回退回原报
                        change.preAuditors = lastAuditors.filter(x => { return x.usort === onAuditor.usort; });
                    }
                }
            }
        }

        async checkSettleUpdate(tid, settleInfo = null) {
            const readySettle = settleInfo ? settleInfo : await this.ctx.service.settle.getReadySettle(tid);
            if (readySettle && readySettle.settle_order !== this.ctx.tender.data.settle_order) {
                // 更新tender里的settle_order值且更新变更数据
                const transaction = await this.db.beginTransaction();
                try {
                    const changeLedgerData = await this.ctx.service.changeLedger.getData(tid);
                    const changePosData = await this.ctx.service.changePos.getPosData({ tid });
                    // const changeSettleData = await this.ctx.service.changeSettleList.getAllDataByCondition({ where: { tid } });
                    if (changeLedgerData.length > 0 || changePosData.length > 0) {
                        const settleBills = await this.ctx.service.settleBills.getAllDataByCondition({ where: { settle_id: readySettle.id } });
                        const removeLedgerList = [];
                        const settleStatus = this.ctx.service.settle.settleStatus;
                        for (const l of changeLedgerData) {
                            const parent = this._.find(settleBills, { tree_id: l.ledger_pid });
                            if (parent && parent.settle_status === settleStatus.finish) {
                                removeLedgerList.push(l);
                            }
                        }
                        // 移除计量单元
                        const removePosList = [];
                        for (const p of changePosData) {
                            const parent = this._.find(settleBills, { lid: p.lid });
                            if (parent && parent.settle_status === settleStatus.finish) {
                                removePosList.push(p);
                            }
                        }
                        // // 移除结算清单
                        // const removeSettleIdList = [];
                        // const settlePos = await this.ctx.service.settlePos.getAllDataByCondition({ where: { settle_id: readySettle.id } });
                        // for (const s of changeSettleData) {
                        //     const info = s.mx_id ? this._.find(settlePos, { lid: s.gcl_id, pid: s.mx_id }) : this._.find(settleBills, { lid: s.gcl_id });
                        //     if (!info || info.settle_status !== settleStatus.finish) {
                        //         removeSettleIdList.push(s.id);
                        //     }
                        // }
                        if (removeLedgerList.length > 0) {
                            await this.ctx.service.changeLedger.deleteBySettle(transaction, tid, removeLedgerList);
                        }
                        if (removePosList.length > 0) {
                            await this.ctx.service.changePos.deleteBySettle(transaction, tid, removePosList);
                        }
                        // if (removeSettleIdList.length > 0) {
                        //     await transaction.delete(this.ctx.service.changeSettleList.tableName, { id: removeSettleIdList });
                        // }
                    }
                    await transaction.update(this.ctx.service.tender.tableName, { id: tid, settle_order: readySettle.settle_order });
                    await transaction.commit();
                    return true;
                } catch (err) {
                    console.log(err);
                    await transaction.rollback();
                    throw err;
                }
            }
        }
        // 获取列表并按审批完成时间排序
        async getListByArchives(tid, ids) {
            if (ids.length === 0) return [];
            const list = await this.getAllDataByCondition({ where: { tid, cid: ids }, orders: [['sin_time', 'desc'], ['cin_time', 'desc']] });
            // 为了通用化id和code值，这里需要把值改改 cid->id,p_code->code
            for (const l of list) {
                l.id = l.cid;
                l.code = l.p_code;
            }
            return list;
        }
    }

    return Change;
};