Calculation/app/service/contract_tree.js

'use strict';

/**
 * Created by EllisRan on 2020/3/3.
 */

const BaseService = require('../base/base_service');
const contractConst = require('../const/contract');
const rootId = -1;

module.exports = app => {

    class ContractTree extends app.BaseBillsService {

        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            const setting = {
                spid: 'spid',
                mid: 'tid',
                type: 'contract_type',
                kid: 'contract_id',
                pid: 'contract_pid',
                order: 'order',
                level: 'level',
                isLeaf: 'is_leaf',
                fullPath: 'full_path',
                keyPre: 'contract_maxLid:', // 换个名称，防止缓存导致旧数据出问题
                uuid: true,
            };
            super(ctx, setting, 'pos');
            this.setting = setting;
            this.depart = 0;
            this.tableName = 'contract_tree';
        }

        _getStringOptions(options) {
            const optionStr = [];
            for (const key in options) {
                if (options.hasOwnProperty(key)) {
                    optionStr.push(options[key]);
                }
            }
            return optionStr.join('&&');
        }

        /**
         * 获取最大节点id
         *
         * @param {Number} mid - master id
         * @return {Number}
         * @private
         */
        async _getMaxLid(options) {
            const cacheKey = this.setting.keyPre + this._getStringOptions(options);
            let maxId = parseInt(await this.cache.get(cacheKey)) || 0;
            if (!maxId) {
                const sql = 'SELECT Max(??) As max_id FROM ?? Where ' + this.ctx.helper._getOptionsSql(options);
                const sqlParam = [this.setting.kid, this.tableName];
                const queryResult = await this.db.queryOne(sql, sqlParam);
                if (maxId < queryResult.max_id || 0) {
                    maxId = queryResult.max_id || 0;
                }
                const sql1 = 'SELECT Max(??) As max_id FROM ?? Where ' + this.ctx.helper._getOptionsSql(options);
                const sqlParam1 = [this.setting.kid, this.ctx.service.contract.tableName];
                const queryResult1 = await this.db.queryOne(sql1, sqlParam1);
                if (maxId < queryResult1.max_id || 0) {
                    maxId = queryResult1.max_id || 0;
                }
                this.cache.set(cacheKey, maxId, 'EX', this.ctx.app.config.cacheTime);
            }
            return maxId;
        }

        _cacheMaxLid(options, maxId) {
            const cacheKey = this.setting.keyPre + this._getStringOptions(options);
            this.cache.set(cacheKey, maxId, 'EX', this.ctx.app.config.cacheTime);
        }

        /**
         * 更新order
         * @param {Number} mid - master id
         * @param {Number} pid - 父节点id
         * @param {Number} order - 开始更新的order
         * @param {Number} incre - 更新的增量
         * @returns {Promise<*>}
         * @private
         */
        async _updateChildrenOrder(options, pid, order, incre = 1, transaction = null) {
            const optionSql = this.ctx.helper._getOptionsSql(options);
            const sql = 'UPDATE ?? SET `' + this.setting.order + '` = `' + this.setting.order + '` ' + (incre > 0 ? '+' : '-') + Math.abs(incre) + ' WHERE ' + optionSql + ' AND `' + this.setting.order + '` >= ? AND ' + this.setting.pid + ' = ?';
            const sqlParam = [this.tableName, order, pid];
            const data = transaction ? await transaction.query(sql, sqlParam) : await this.db.query(sql, sqlParam);

            const sql1 = 'UPDATE ?? SET `' + this.setting.order + '` = `' + this.setting.order + '` ' + (incre > 0 ? '+' : '-') + Math.abs(incre) + ' WHERE ' + optionSql + ' AND `' + this.setting.order + '` >= ? AND ' + this.setting.pid + ' = ?';
            const sqlParam1 = [this.ctx.service.contract.tableName, order, pid];
            transaction ? await transaction.query(sql1, sqlParam1) : await this.db.query(sql1, sqlParam1);

            return data;
        }

        _getOptionsSql(options) {
            const optionSql = [];
            for (const key in options) {
                if (options.hasOwnProperty(key)) {
                    optionSql.push(key + ' = ' + this.db.escape(options[key]));
                }
            }
            return optionSql.join(' AND ');
        }

        async insertTree(options, subInfo) {
            const hadTree = await this.getDataByCondition(options);
            if (!hadTree) {
                if (options.tid && !subInfo.spid) {
                    throw '该标段未绑定项目';
                }
                const subProj = options.spid ? subInfo : await this.ctx.service.subProject.getDataById(subInfo.spid);
                if (subProj.std_id === 0) {
                    throw '该项目未绑定概预算标准';
                }
                const stdInfo = await this.ctx.service.budgetStd.getDataById(subProj.std_id);
                if (!stdInfo) {
                    throw '概预算标准不存在';
                } else if (options.spid && !stdInfo.ht_project_template_id) {
                    throw '概预算标准未绑定项目合同模板';
                } else if (options.tid && !stdInfo.ht_tender_template_id) {
                    throw '概预算标准未绑定标段合同模板';
                }
                const ht_template_id = options.spid ? stdInfo.ht_project_template_id.split(',')[0] : stdInfo.ht_tender_template_id.split(',')[0];
                const ht_template_datas = await this.ctx.service.tenderNodeTemplate.getData(ht_template_id);
                if (!ht_template_datas.length) throw '模板数据有误';
                const expensesDatas = [];
                const incomeDatas = [];
                for (const t of ht_template_datas) {
                    const insertData = {
                        spid: options.spid || null,
                        tid: options.tid || null,
                        contract_id: t.template_id,
                        contract_pid: t.pid,
                        level: t.level,
                        order: t.order,
                        full_path: t.full_path,
                        is_leaf: t.is_leaf,
                        code: t.code,
                        name: t.name,
                        unit: t.unit,
                    };
                    const expensesData = this.ctx.helper._.cloneDeep(insertData);
                    expensesData.id = this.uuid.v4();
                    expensesData.contract_type = contractConst.type.expenses;
                    expensesDatas.push(expensesData);
                    const incomeData = this.ctx.helper._.cloneDeep(insertData);
                    incomeData.id = this.uuid.v4();
                    incomeData.contract_type = contractConst.type.income;
                    incomeDatas.push(incomeData);
                }
                await this.db.insert(this.tableName, [...expensesDatas, ...incomeDatas]);
            }
        }

        /**
         * 提交数据 - 响应计算(增量方式计算)
         * @param {Number} tenderId
         * @param {Object} data
         * @return {Promise<*>}
         */
        async updateCalc(options, data) {
            const helper = this.ctx.helper;
            if (!data) {
                throw '提交数据错误';
            }
            const datas = data instanceof Array ? data : [data];
            const ids = [];
            for (const row of datas) {
                ids.push(row.id);
            }

            const transaction = await this.db.beginTransaction();
            try {
                const updateDatas = [];
                for (const row of datas) {
                    const updateNode = await this.getDataById(row.id);
                    if (!updateNode) {
                        throw '提交数据错误';
                    }
                    const updateData = this._filterUpdateInvalidField(updateNode.id, row);
                    // 如非子节点，需要更新底下所有已选清单的分部分项等数据
                    updateDatas.push(updateData);
                }
                if (updateDatas.length > 0) await transaction.updateRows(this.tableName, updateDatas);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            return { update: await this.getDataById(ids) };
        }

        async getDataByKid(options, kid) {
            const condition = { ...options };
            condition[this.setting.kid] = kid;
            return await this.getDataByCondition(condition) || await this.ctx.service.contract.getDataByCondition(condition);
        }

        async getDataByKidAndCount(options, kid, count) {
            if (kid <= 0) return [];
            const select = await this.getDataByKid(options, kid);
            if (!select) throw '数据错误';

            if (count > 1) {
                const selects = await this.getNextsData(options, select[this.setting.pid], select[this.setting.order] - 1);
                if (selects.length < count) throw '数据错误';
                return selects.slice(0, count);
            } else {
                return [select];
            }
        }

        /**
         * 根据 父节点id 和 节点排序order 获取数据
         *
         * @param {Number} mid - master id
         * @param {Number} pid - 父节点id
         * @param {Number|Array} order - 排序
         * @return {Object|Array} - 查询结果
         */
        async getDataByParentAndOrder(options, pid, order) {
            const condition = { ...options };
            condition[this.setting.pid] = pid;
            condition[this.setting.order] = order;
            const result = await this.db.select(this.tableName, {
                where: condition,
            });
            const result1 = await this.db.select(this.ctx.service.contract.tableName, {
                where: condition,
            });
            // data和data1合并且按order排序
            const resultData = result.concat(result1).sort((a, b) => a.order - b.order);
            return order instanceof Array ? resultData : (resultData.length > 0 ? resultData[0] : null);
        }

        async addNodeBatch(options, kid, count = 1) {
            if (!options[this.setting.type]) throw '参数有误';
            const select = kid ? await this.getDataByKid(options, kid) : null;
            if (kid && !select) throw '新增节点数据错误';

            const transaction = await this.db.beginTransaction();
            try {
                // 判断select的父节点是否是变更新增的，如果是则修改自己的表就行了，否则修改2个ledger，changeLedger表
                if (select) await this._updateChildrenOrder(options, select[this.setting.pid], select[this.setting.order] + 1, count, transaction);
                const newDatas = [];
                const maxId = await this._getMaxLid(options);
                for (let i = 1; i < count + 1; i++) {
                    const newData = [];
                    if (this.setting.uuid) newData.id = this.uuid.v4();
                    newData[this.setting.kid] = maxId + i;
                    newData[this.setting.pid] = select ? select[this.setting.pid] : rootId;
                    newData[this.setting.spid] = options.spid || null;
                    newData[this.setting.type] = options[this.setting.type];
                    newData[this.setting.mid] = options.tid || null;
                    newData[this.setting.level] = select ? select[this.setting.level] : 1;
                    newData[this.setting.order] = select ? select[this.setting.order] + i : i;
                    newData[this.setting.fullPath] = newData[this.setting.level] > 1
                        ? select[this.setting.fullPath].replace('-' + select[this.setting.kid], '-' + newData[this.setting.kid])
                        : newData[this.setting.kid] + '';
                    newData[this.setting.isLeaf] = true;
                    newDatas.push(newData);
                }
                const insertResult = await transaction.insert(this.tableName, newDatas);
                this._cacheMaxLid(options, maxId + count);

                if (insertResult.affectedRows !== count) throw '新增节点数据错误';
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            if (select) {
                let createData = await this.getChildBetween(options, select[this.setting.pid], select[this.setting.order], select[this.setting.order] + count + 1);
                let updateData = await this.getNextsData(options, select[this.setting.pid], select[this.setting.order] + count);
                return { create: createData, update: updateData };
            } else {
                const createData = await this.getChildBetween(options, -1, 0, count + 1);
                return { create: createData };
            }
        }

        async addChildNode(options, kid, count = 1) {
            if (!options[this.setting.type]) throw '参数有误';
            const select = kid ? await this.getDataByKid(options, kid) : null;
            if (!select) throw '新增子节点数据错误';
            if (select && select.c_code) throw '合同无法新增子节点';

            const transaction = await this.db.beginTransaction();
            try {
                // 判断select的父节点是否是变更新增的，如果是则修改自己的表就行了，否则修改2个ledger，changeLedger表
                // if (select) await this._updateChildrenOrder(options, select[this.setting.pid], select[this.setting.order] + 1, count, transaction);
                const maxOrder = await this.ctx.service.contract.getMaxOrder(options, select[this.setting.kid], transaction);
                const newDatas = [];
                const maxId = await this._getMaxLid(options);
                for (let i = 0; i < count; i++) {
                    const newData = [];
                    if (this.setting.uuid) newData.id = this.uuid.v4();
                    newData[this.setting.kid] = maxId + i;
                    newData[this.setting.pid] = select[this.setting.kid];
                    newData[this.setting.spid] = options.spid || null;
                    newData[this.setting.type] = options[this.setting.type];
                    newData[this.setting.mid] = options.tid || null;
                    newData[this.setting.level] = select[this.setting.level] + 1;
                    newData[this.setting.order] = maxOrder + i;
                    newData[this.setting.fullPath] = select[this.setting.fullPath] + '-' + newData[this.setting.kid];
                    newData[this.setting.isLeaf] = true;
                    newDatas.push(newData);
                }
                const insertResult = await transaction.insert(this.tableName, newDatas);
                this._cacheMaxLid(options, maxId + count);

                if (insertResult.affectedRows !== count) throw '新增子节点数据错误';
                if (select[this.setting.isLeaf]) {
                    select.is_leaf = 0;
                    await transaction.update(this.tableName, { id: select.id, is_leaf: 0 });
                }

                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            let createData = await this.getLastChildData(options, select[this.setting.kid]);
            let updateData = select;
            return { create: createData, update: updateData };
        }

        /**
         *  tenderId标段中, 删除选中节点及其子节点
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 被删除的数据
         */
        async deleteNode(options, kid) {
            if (kid <= 0) return [];
            const select = await this.getDataByKid(options, kid);
            if (!select) throw '删除节点数据错误';
            const parent = await this.getDataByKid(options, select[this.setting.pid]);
            // 获取将要被删除的数据
            const deleteData = await this.getDataByFullPath(options, select[this.setting.fullPath] + '-%');
            deleteData.unshift(select);
            if (deleteData.length === 0) throw '删除节点数据错误';

            const transaction = await this.db.beginTransaction();
            try {
                // 删除
                if (select.c_code) {
                    if (select.uid !== this.ctx.session.sessionUser.accountId && !this.ctx.session.sessionUser.is_admin) throw '当前合同无权删除';
                    const contractPays = await this.ctx.service.contractPay.getDataByCondition({ cid: select.id });
                    if (contractPays) throw '还存在合同支付项，无法删除';
                    await transaction.delete(this.ctx.service.contract.tableName, { id: select.id });
                    const attList = await this.ctx.service.contractAtt.getAllDataByCondition({ where: { cid: select.id } });
                    await this.ctx.helper.delFiles(attList);
                    await transaction.delete(this.ctx.service.contractAtt.tableName, { cid: select.id });
                } else {
                    await transaction.delete(this.tableName, { id: select.id });
                    const delOptions = this._.cloneDeep(options);
                    delOptions.contract_id = this._.map(deleteData, 'contract_id');
                    await transaction.delete(this.ctx.service.contractTreeAudit.tableName, delOptions);
                    const contracts = this.ctx.helper._.filter(deleteData, function (item) {
                        return item.c_code;
                    });
                    if (contracts.length > 0) {
                        const contractUids = this.ctx.helper._.uniq(this.ctx.helper._.map(contracts, 'uid'));
                        if (contractUids.length > 1 || !(contractUids[0] === this.ctx.session.sessionUser.accountId || this.ctx.session.sessionUser.is_admin)) throw '存在合同你无权删除';
                        const contractPays = await transaction.select(this.ctx.service.contractPay.tableName, { where: { cid: this.ctx.helper._.map(contracts, 'id') } });
                        if (contractPays.length > 0) throw '还存在合同支付项，无法删除';
                        const attList = await this.ctx.service.contractAtt.getAllDataByCondition({ where: { cid: this.ctx.helper._.map(contracts, 'id') } });
                        await this.ctx.helper.delFiles(attList);
                        await transaction.delete(this.ctx.service.contractAtt.tableName, { cid: this.ctx.helper._.map(contracts, 'id') });
                    }
                    const operate = await this._deletePosterity(options, select, transaction);
                }
                // 选中节点--父节点 只有一个子节点时，应升级isLeaf
                if (parent) {
                    const condition = { ...options };
                    condition[this.setting.pid] = select[this.setting.pid];
                    const count = await this.db.count(this.tableName, condition);
                    const count1 = await this.db.count(this.ctx.service.contract.tableName, condition);
                    const sum = count + count1;
                    if (sum === 1) {
                        const updateParent = {id: parent.id };
                        updateParent[this.setting.isLeaf] = true;
                        await transaction.update(this.tableName, updateParent);
                    }
                }
                // 选中节点--全部后节点 order--
                await this._updateChildrenOrder(options, select[this.setting.pid], select[this.setting.order] + 1, -1, transaction);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            // 查询结果
            const updateData = await this.getNextsData(options, select[this.setting.pid], select[this.setting.order] - 1);
            if (parent) {
                const updateData1 = await this.getDataByKid(options, select[this.setting.pid]);
                if (updateData1[this.setting.isLeaf]) {
                    updateData.push(updateData1);
                }
            }
            return { delete: deleteData, update: updateData };
        }

        async deleteNodes(options, kid, count) {
            const _ = this.ctx.helper._;
            if ((kid <= 0) || (count <= 0)) return [];
            const selects = await this.getDataByKidAndCount(options, kid, count);
            const first = selects[0];
            const parent = await this.getDataByKid(options, first[this.setting.pid]);
            const condition = { ...options };
            condition[this.setting.pid] = parent[this.setting.kid];
            const childCount1 = parent ? await this.count(condition) : -1;
            const childCount2 = parent ? await this.db.count(this.ctx.service.contract.tableName, condition) : -1;
            const childCount = childCount1 + childCount2;
            let deleteData = [];
            for (const s of selects) {
                deleteData = deleteData.concat(await this.getDataByFullPath(options, s[this.setting.fullPath] + '-%'));
                deleteData.push(s);
            }

            const transaction = await this.db.beginTransaction();
            try {
                // 删除
                for (const s of selects) {
                    if (s.c_code) {
                        if (s.uid !== this.ctx.session.sessionUser.accountId && !this.ctx.session.sessionUser.is_admin) throw '存在合同你无权删除';
                        const contractPays = await this.ctx.service.contractPay.getDataByCondition({ cid: s.id });
                        if (contractPays) throw '部分合同还存在合同支付项，无法删除';
                        await transaction.delete(this.ctx.service.contract.tableName, { id: s.id });
                        const attList = await this.ctx.service.contractAtt.getAllDataByCondition({ where: { cid: s.id } });
                        await this.ctx.helper.delFiles(attList);
                        await transaction.delete(this.ctx.service.contractAtt.tableName, { cid: s.id });
                    } else {
                        await transaction.delete(this.tableName, { id: s.id });
                        const contracts = _.filter(deleteData, function (item) {
                            return item.c_code && _.includes(s.full_path, item.full_path);
                        });
                        if (contracts.length > 0) {
                            const contractUids = _.uniq(_.map(contracts, 'uid'));
                            if (contractUids.length > 1 || !(contractUids[0] === this.ctx.session.sessionUser.accountId || this.ctx.session.sessionUser.is_admin)) throw '存在合同你无权删除';
                            const contractPays = await transaction.select(this.ctx.service.contractPay.tableName, { where: { cid: _.map(contracts, 'id') } });
                            if (contractPays.length > 0) throw '还存在合同支付项，无法删除';
                            const attList = await this.ctx.service.contractAtt.getAllDataByCondition({ where: { cid: _.map(contracts, 'id') } });
                            await this.ctx.helper.delFiles(attList);
                            await transaction.delete(this.ctx.service.contractAtt.tableName, { cid: _.map(contracts, 'id') });
                        }
                    }
                    const operate = await this._deletePosterity(options, s, transaction);
                }
                // 选中节点--父节点 只有一个子节点时，应升级isLeaf
                if (parent && childCount === count) {
                    const updateParent = {id: parent.id };
                    updateParent[this.setting.isLeaf] = true;
                    await transaction.update(this.tableName, updateParent);
                }
                // 选中节点--全部后节点 order--
                await this._updateChildrenOrder(options, first[this.setting.pid], first[this.setting.order] + count, -count, transaction);
                const delOptions = this._.cloneDeep(options);
                delOptions.contract_id = this._.map(deleteData, 'contract_id');
                await transaction.delete(this.ctx.service.contractTreeAudit.tableName, delOptions);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            const updateData = await this.getNextsData(options, first[this.setting.pid], first[this.setting.order] - 1);
            if (parent && childCount === count) {
                const updateData1 = await this.getDataByKid(options, parent[this.setting.kid]);
                updateData.push(updateData1);
            }
            return { delete: deleteData, update: updateData };
        }

        async delete(options, kid, count = null) {
            if (count && count > 1) {
                return await this.deleteNodes(options, kid, count);
            } else {
                return await this.deleteNode(options, kid);
            }
        }

        /**
         * 上移节点
         *
         * @param {Number} mid - master id
         * @param {Number} kid - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async upMoveNode(options, kid, count) {
            if (!count) count = 1;
            if (!kid || (kid <= 0)) return null;
            const selects = await this.getDataByKidAndCount(options, kid, count);
            if (selects.length !== count) throw '上移节点数据错误';
            const first = selects[0];
            const pre = await this.getDataByParentAndOrder(options, first[this.setting.pid], first[this.setting.order] - 1);
            if (!pre) throw '节点不可上移';

            const order = [];
            const transaction = await this.db.beginTransaction();
            try {
                for (const s of selects) {
                    const sData = { id: s.id };
                    sData[this.setting.order] = s[this.setting.order] - 1;
                    !s.c_code ? await transaction.update(this.tableName, sData) : await transaction.update(this.ctx.service.contract.tableName, sData);
                    order.push(s[this.setting.order] - 1);
                }
                const pData = { id: pre.id };
                pData[this.setting.order] = pre[this.setting.order] + count;
                !pre.c_code ? await transaction.update(this.tableName, pData) : await transaction.update(this.ctx.service.contract.tableName, pData);
                order.push(pre[this.setting.order] + count);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            const resultData = await this.getDataByParentAndOrder(options, first[this.setting.pid], order);
            return { update: resultData };
        }

        /**
         * 下移节点
         *
         * @param {Number} mid - master id
         * @param {Number} kid - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async downMoveNode(options, kid, count) {
            if (!count) count = 1;
            if (!kid || (kid <= 0)) return null;
            const selects = await this.getDataByKidAndCount(options, kid, count);
            if (selects.length !== count) {
                throw '下移节点数据错误';
            }
            const last = selects[count - 1];
            const next = await this.getDataByParentAndOrder(options, last[this.setting.pid], last[this.setting.order] + 1);
            if (!next) {
                throw '节点不可下移';
            }

            const order = [];
            const transaction = await this.db.beginTransaction();
            try {
                for (const s of selects) {
                    const sData = { id: s.id };
                    sData[this.setting.order] = s[this.setting.order] + 1;
                    !s.c_code ? await transaction.update(this.tableName, sData) : await transaction.update(this.ctx.service.contract.tableName, sData);
                    order.push(s[this.setting.order] + 1);
                }
                const nData = { id: next.id };
                nData[this.setting.order] = next[this.setting.order] - count;
                !next.c_code ? await transaction.update(this.tableName, nData) : await transaction.update(this.ctx.service.contract.tableName, nData);
                order.push(next[this.setting.order] - count);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            const resultData = await this.getDataByParentAndOrder(options, last[this.setting.pid], order);
            return { update: resultData };
        }

        /**
         * 升级节点
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async upLevelNode(options, kid, count) {
            if (!count) count = 1;
            const selects = await this.getDataByKidAndCount(options, kid, count);

            if (selects.length !== count) throw '升级节点数据错误';
            const first = selects[0], last = selects[count - 1];
            const parent = await this.getDataByKid(options, first[this.setting.pid]);
            if (!parent) throw '升级节点数据错误';

            const newPath = [];
            const transaction = await this.db.beginTransaction();
            try {
                // 选中节点--父节点 选中节点为firstChild时，修改isLeaf
                if (first[this.setting.order] === 1) {
                    const updateParentData = { id: parent.id };
                    updateParentData[this.setting.isLeaf] = true;
                    await transaction.update(this.tableName, updateParentData);
                }
                // 选中节点--父节点--全部后兄弟节点 order+1
                await this._updateChildrenOrder(options, parent[this.setting.pid], parent[this.setting.order] + 1, count, transaction);
                for (const [i, s] of selects.entries()) {
                    // 选中节点 修改pid, order, fullPath, level, isLeaf, 清空计算项
                    const updateData = { id: s.id };
                    updateData[this.setting.pid] = parent[this.setting.pid];
                    updateData[this.setting.order] = parent[this.setting.order] + i + 1;
                    updateData[this.setting.level] = s[this.setting.level] - 1;
                    updateData[this.setting.fullPath] = s[this.setting.fullPath].replace(`-${s[this.setting.pid]}-`, '-');
                    newPath.push(updateData[this.setting.fullPath]);
                    if (s[this.setting.isLeaf] && s.id === last.id) {
                        const nexts = await this.getNextsData(options, parent[this.setting.kid], last[this.setting.order]);
                        if (nexts.length > 0) {
                            updateData[this.setting.isLeaf] = false;
                        }
                    }
                    await transaction.update(this.tableName, updateData);
                    // 选中节点--全部子节点（含孙） level-1, fullPath变更
                    await this._syncUplevelChildren(options, s, transaction);
                }
                // 选中节点--全部后兄弟节点 收编为子节点 修改pid, order, fullPath
                await this._syncUpLevelNexts(options, last, transaction);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            // 查询修改的数据
            let updateData = await this.getNextsData(options, parent[this.setting.pid], parent[this.setting.order] - 1);
            for (const path of newPath) {
                const children = await this.getDataByFullPath(options, path + '-%');
                updateData = updateData.concat(children);
            }
            return { update: updateData };
        }

        /**
         * 降级节点
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async downLevelNode(options, kid, count) {
            if (!count) count = 1;
            const selects = await this.getDataByKidAndCount(options, kid, count);
            if (!selects) throw '降级节点数据错误';
            const first = selects[0], last = selects[count - 1];
            const pre = await this.getDataByParentAndOrder(options, first[this.setting.pid], first[this.setting.order] - 1);
            if (!pre) throw '节点不可降级';
            const preLastChild = await this.getLastChildData(options, pre[this.setting.kid]);

            const newPath = [];
            const transaction = await this.db.beginTransaction();
            try {
                // 选中节点--全部后节点 order--
                await this._updateChildrenOrder(options, first[this.setting.pid], last[this.setting.order] + 1, -count, transaction);

                for (const [i, s] of selects.entries()) {
                    // 选中节点 修改pid, level, order, fullPath
                    const updateData = { id: s.id };
                    updateData[this.setting.pid] = pre[this.setting.kid];
                    updateData[this.setting.order] = preLastChild ? preLastChild[this.setting.order] + i + 1 : i + 1;
                    updateData[this.setting.level] = s[this.setting.level] + 1;
                    if (s[this.setting.level] === 1) {
                        updateData[this.setting.fullPath] = pre[this.setting.kid] + '-' + s[this.setting.kid];
                    } else {
                        const index = s[this.setting.fullPath].lastIndexOf(s[this.setting.kid]);
                        updateData[this.setting.fullPath] = s[this.setting.fullPath].substring(0, index-1) + '-' + pre[this.setting.kid] + '-' + s[this.setting.kid];
                    }
                    newPath.push(updateData[this.setting.fullPath]);
                    await transaction.update(this.tableName, updateData);
                    // 选中节点--全部子节点（含孙） level++, fullPath
                    await this._syncDownlevelChildren(options, s, updateData[this.setting.fullPath], transaction);
                }
                // 选中节点--前兄弟节点 isLeaf应为false, 清空计算相关字段
                const updateData2 = { id: pre.id };
                updateData2[this.setting.isLeaf] = false;
                await transaction.update(this.tableName, updateData2);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            // 查询修改的数据
            let updateData = await this.getNextsData(options, pre[this.setting.pid], pre[this.setting.order] - 1);
            // 选中节点及子节点
            for (const p of newPath) {
                updateData = updateData.concat(await this.getDataByFullPath(options, p + '-%'));
            }
            updateData = updateData.concat(await this.getDataById(selects.map(x => { return x.id; })));
            // 选中节点--原前兄弟节点&全部后兄弟节点
            return { update: updateData };
        }

        async pasteBlockData(options, kid, pasteData, defaultData) {
            if (!options[this.setting.type]) throw '参数有误';
            if (!pasteData || pasteData.length <= 0) throw '复制数据错误';
            for (const pd of pasteData) {
                if (!pd || pd.length <= 0) throw '复制数据错误';
                pd.sort(function (x, y) {
                    return x.level - y.level
                });
                if (pd[0].contract_pid !== pasteData[0][0].contract_pid) throw '复制数据错误：仅可操作同层节点';
            }
            const selectData = await this.getDataByKid(options, kid);
            if (!selectData) throw '粘贴数据错误';
            const newParentPath = selectData.full_path.replace(selectData.contract_id, '');

            const pasteBillsData = [];
            let maxId = await this._getMaxLid(options);
            for (const [i, pd] of pasteData.entries()) {
                for (const d of pd) {
                    d.children = pd.filter(function (x) {
                        return x.contract_pid === d.contract_id;
                    });
                }
                const pbd = [];
                for (const [j, d] of pd.entries()) {
                    const newBills = {
                        id: this.uuid.v4(),
                        spid: options.spid || null,
                        tid: options.tid || null,
                        contract_type: options.contract_type,
                        contract_id: maxId + j + 1,
                        contract_pid: j === 0 ? selectData.contract_pid : d.contract_pid,
                        level: d.level + selectData.level - pd[0].level,
                        order: j === 0 ? selectData.order + i + 1 : d.order,
                        is_leaf: d.is_leaf,
                        code: d.code,
                        name: d.name,
                        remark: d.remark,
                    };
                    for (const c of d.children) {
                        c.contract_pid = newBills.contract_id;
                    }
                    pbd.push(newBills);
                }
                for (const d of pbd) {
                    const parent = pbd.find(function (x) {
                        return x.contract_id === d.contract_pid;
                    });
                    d.full_path = parent
                        ? parent.full_path + '-' + d.contract_id
                        : newParentPath + d.contract_id;
                    if (defaultData) this.ctx.helper._.assignIn(pbd, defaultData);
                    pasteBillsData.push(d);
                }
                maxId = maxId + pbd.length;
            }

            const transaction = await this.db.beginTransaction();
            try {
                // 选中节点的所有后兄弟节点，order+粘贴节点个数
                await this._updateChildrenOrder(options, selectData.ledger_pid, selectData.order + 1, pasteData.length, transaction);
                // 数据库创建新增节点数据
                if (pasteBillsData.length > 0) {
                    const newData = await transaction.insert(this.tableName, pasteBillsData);
                }
                this._cacheMaxLid(options, maxId);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            // 查询应返回的结果
            const updateData = await this.getNextsData(options, selectData.contract_pid, selectData.order + pasteData.length);
            return {
                ledger: { create: pasteBillsData, update: updateData },
            };
        }

        /**
         * 删除节点
         * @param {Number} tenderId - 标段id
         * @param {Object} deleteData - 删除节点数据
         * @return {Promise<*>}
         * @private
         */
        async _deletePosterity(options, node, transaction = null) {
            const sql = 'DELETE FROM ?? WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.fullPath + ' LIKE ?';
            const sqlParam = [this.tableName, node[this.setting.fullPath] + '-%'];
            const result = transaction ? await transaction.query(sql, sqlParam) : await this.db.query(sql, sqlParam)

            const sql1 = 'DELETE FROM ?? WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.fullPath + ' LIKE ?';
            const sqlParam1 = [this.ctx.service.contract.tableName, node[this.setting.fullPath] + '-%'];
            const result1 = transaction ? await transaction.query(sql1, sqlParam1) : await this.db.query(sql1, sqlParam1)

            return result;
        }

        /**
         * 根据fullPath获取数据 fullPath Like ‘1.2.3%’（传参fullPath = '1.2.3%'）
         * @param {Number} tenderId - 标段id
         * @param {String} fullPath - 路径
         * @return {Promise<void>}
         */
        async getDataByFullPath(options, fullPath) {
            const sql = 'SELECT * FROM ?? WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.fullPath + ' LIKE ?';
            const sqlParam = [this.tableName, fullPath];
            const resultData = await this.db.query(sql, sqlParam);

            const sql1 = 'SELECT * FROM ?? WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.fullPath + ' LIKE ?';
            const sqlParam1 = [this.ctx.service.contract.tableName, fullPath];
            const resultData1 = await this.db.query(sql1, sqlParam1);
            return resultData.concat(resultData1).sort((a, b) => a.order - b.order);
        }

        async getChildBetween(options, pid, order1, order2) {
            const sql = 'SELECT * FROM ?? WHERE '+ this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.pid + ' = ? AND `order` > ? AND `order` < ? ORDER BY `order` ASC';
            const sqlParam = [this.tableName, pid, order1, order2];
            const data = await this.db.query(sql, sqlParam);

            const sql1 = 'SELECT * FROM ?? WHERE '+ this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.pid + ' = ? AND `order` > ? AND `order` < ? ORDER BY `order` ASC';
            const sqlParam1 = [this.ctx.service.contract.tableName, pid, order1, order2];
            const data1 = await this.db.query(sql1, sqlParam1);

            const resultData = data.concat(data1).sort((a, b) => a.order - b.order);
            return resultData;
        }

        /**
         * 根据 父节点ID 和 节点排序order 获取全部后节点数据
         * @param {Number} mid - master id
         * @param {Number} pid - 父节点id
         * @param {Number} order - 排序
         * @return {Array}
         */
        async getNextsData(options, pid, order) {
            const sql = 'SELECT * FROM ?? WHERE '+ this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.pid + ' = ? AND `order` > ? ORDER BY `order` ASC';
            const sqlParam = [this.tableName, pid, order];
            const data = await this.db.query(sql, sqlParam);

            const sql1 = 'SELECT * FROM ?? WHERE '+ this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.pid + ' = ? AND `order` > ? ORDER BY `order` ASC';
            const sqlParam1 = [this.ctx.service.contract.tableName, pid, order];
            const data1 = await this.db.query(sql1, sqlParam1);
            // data和data1合并且按order排序
            const resultData = data.concat(data1).sort((a, b) => a.order - b.order);
            return resultData;
        }

        /**
         * 获取最末的子节点
         * @param {Number} mid - masterId
         * @param {Number} pid - 父节点id
         * @return {Object}
         */
        async getLastChildData(options, pid, transaction = null) {
            const sql = 'SELECT * FROM ?? WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.pid + ' = ? ORDER BY `order` DESC';
            const sqlParam = [this.tableName, pid];
            const resultData = await this.db.queryOne(sql, sqlParam);

            const sql1 = 'SELECT * FROM ?? WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.pid + ' = ? ORDER BY `order` DESC';
            const sqlParam1 = [this.ctx.service.contract.tableName, pid];
            const resultData1 = await this.db.queryOne(sql1, sqlParam1);
            // 比较两个结果，返回order大的
            if (resultData && resultData1) {
                return resultData.order > resultData1.order ? resultData : resultData1;
            } else {
                return resultData || resultData1;
            }
        }

        /**
         * 选中节点的后兄弟节点，全部变为当前节点的子节点
         * @param {Object} selectData - 选中节点
         * @return {Object}
         * @private
         */
        async _syncUpLevelNexts(options, select, transaction = null) {
            // 查询selectData的lastChild
            const lastChild = await this.getLastChildData(options, select[this.setting.kid]);
            const nexts = await this.getNextsData(options, select[this.setting.pid], select[this.setting.order]);
            if (nexts && nexts.length > 0) {
                // 修改nextsData pid, 排序
                // this.initSqlBuilder();
                // this.sqlBuilder.setUpdateData(this.setting.pid, {
                //     value: select[this.setting.kid],
                // });
                // const orderInc = lastChild ? lastChild[this.setting.order] - select[this.setting.order] : - select[this.setting.order];
                // this.sqlBuilder.setUpdateData(this.setting.order, {
                //     value: Math.abs(orderInc),
                //     selfOperate: orderInc > 0 ? '+' : '-',
                // });
                // this.sqlBuilder.setAndWhere(this.setting.mid, {
                //     value: select[this.setting.mid],
                //     operate: '=',
                // });
                // this.sqlBuilder.setAndWhere(this.setting.pid, {
                //     value: select[this.setting.pid],
                //     operate: '=',
                // });
                // this.sqlBuilder.setAndWhere(this.setting.order, {
                //     value: select[this.setting.order],
                //     operate: '>',
                // });
                // const [sql1, sqlParam1] = this.sqlBuilder.build(this.tableName, 'update');
                const orderInc = lastChild ? lastChild[this.setting.order] - select[this.setting.order] : - select[this.setting.order];
                const sql1 = 'UPDATE ?? SET `' + this.setting.order + '` = ' + (orderInc > 0 ? '+' : '-') + Math.abs(orderInc) + ' WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.pid + ' = ? AND `' + this.setting.order + '` > ?';
                const sqlParam1 = [this.tableName, select[this.setting.kid], select[this.setting.pid], select[this.setting.order]];
                transaction ? await transaction.query(sql1, sqlParam1) : await this.db.query(sql1, sqlParam1);

                // 选中节点 isLeaf应为false
                if (select[this.setting.isLeaf]) {
                    const updateData = { id: select.id };
                    updateData[this.setting.isLeaf] = false;
                    transaction ? await transaction.update(this.tableName, updateData) : await this.db.update(this.tableName, updateData);
                }

                // 修改nextsData及其子节点的fullPath
                const oldSubStr = this.db.escape(select[this.setting.pid] + '-');
                const newSubStr = this.db.escape(select[this.setting.kid] + '-');
                const sqlArr = [];
                sqlArr.push('Update ?? SET `' + this.setting.fullPath + '` = Replace(`' + this.setting.fullPath + '`,' + oldSubStr + ',' + newSubStr + ') Where');
                sqlArr.push('(`' + this.ctx.helper._getOptionsSql(options) + ')');
                sqlArr.push(' And (');
                for (const data of nexts) {
                    sqlArr.push('`' + this.setting.fullPath + '` Like ' + this.db.escape(data[this.setting.fullPath] + '%'));
                    if (nexts.indexOf(data) < nexts.length - 1) {
                        sqlArr.push(' Or ');
                    }
                }
                sqlArr.push(')');
                const sql = sqlArr.join('');
                const resultData = transaction ? await transaction.query(sql, [this.tableName]) : await this.db.query(sql, [this.tableName]);
                return resultData;
            }
        }

        /**
         * 升级selectData, 同步修改所有子节点
         * @param {Object} selectData - 升级操作，选中节点
         * @return {Object}
         * @private
         */
        async _syncUplevelChildren(options, select, transaction = null) {
            // this.initSqlBuilder();
            // this.sqlBuilder.setAndWhere(this.setting.mid, {
            //     value: select[this.setting.mid],
            //     operate: '=',
            // });
            // this.sqlBuilder.setAndWhere(this.setting.fullPath, {
            //     value: this.db.escape(select[this.setting.fullPath] + '-%'),
            //     operate: 'like',
            // });
            // this.sqlBuilder.setUpdateData(this.setting.level, {
            //     value: 1,
            //     selfOperate: '-',
            // });
            // this.sqlBuilder.setUpdateData(this.setting.fullPath, {
            //     value: [this.setting.fullPath, this.db.escape(`-${select[this.setting.pid]}-`), this.db.escape('-')],
            //     literal: 'Replace',
            // });
            // const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
            const sql = 'UPDATE ?? SET ' + this.setting.level + ' = ' + this.setting.level + ' -1, ' + this.setting.fullPath + ' = ' +
                'Replace('+ [this.setting.fullPath, this.db.escape(`-${select[this.setting.pid]}-`), this.db.escape('-')].join(',') +') ' +
                'WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.fullPath + ' LIKE ?';
            const sqlParam = [this.tableName, select[this.setting.fullPath] + '-%'];
            const data = transaction ? await transaction.query(sql, sqlParam) : await this.db.query(sql, sqlParam);

            return data;
        }

        /**
         * 降级selectData, 同步修改所有子节点
         * @param {Object} selectData - 选中节点
         * @param {Object} preData - 选中节点的前一节点（降级后为父节点）
         * @return {Promise<*>}
         * @private
         */
        async _syncDownlevelChildren(options, select, newFullPath, transaction = null) {
            // this.initSqlBuilder();
            // this.sqlBuilder.setAndWhere(this.setting.mid, {
            //     value: select[this.setting.mid],
            //     operate: '=',
            // });
            // this.sqlBuilder.setAndWhere(this.setting.fullPath, {
            //     value: this.db.escape(select[this.setting.fullPath] + '-%'),
            //     operate: 'like',
            // });
            // this.sqlBuilder.setUpdateData(this.setting.level, {
            //     value: 1,
            //     selfOperate: '+',
            // });
            // this.sqlBuilder.setUpdateData(this.setting.fullPath, {
            //     value: [this.setting.fullPath, this.db.escape(select[this.setting.fullPath] + '-'), this.db.escape(newFullPath + '-')],
            //     literal: 'Replace',
            // });
            // const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
            const sql = 'UPDATE ?? SET ' + this.setting.level + ' = ' + this.setting.level + ' + 1, ' + this.setting.fullPath + ' = ' +
                'Replace(' + [[this.setting.fullPath, this.db.escape(select[this.setting.fullPath] + '-'), this.db.escape(newFullPath + '-')]].join(',') + ') ' +
                'WHERE ' + this.ctx.helper._getOptionsSql(options) + ' AND ' + this.setting.fullPath + ' LIKE ?';
            const sqlParam = [this.tableName, select[this.setting.fullPath] + '-%'];
            const data = transaction ? await transaction.query(sql, sqlParam) : await this.db.query(sql, sqlParam);

            return data;
        }
    }
    return ContractTree;
};