Calculation/app/base/base_tree_service.js

'use strict';

/**
 * 提供基础操作:增删改查
 *
 * @author Mai
 * @date
 * @version
 */

const Service = require('./base_service');
// sql拼装器
const SqlBuilder = require('../lib/sql_builder');
const rootId = -1;

class TreeService extends Service {
    /**
     * 构造函数
     *
     * @param {Object} ctx - egg全局context
     * @param {Object} setting - 树结构设置
     * e.g.: {
     *     mid: 'tender_id', 分块id（例如tender_id, rid, list_id）
     *     kid: 'ledger_id', 分块内的树结构id
     *     pid: 'ledger_pid', 父节点id
     *     order: 'order',
     *     level: 'level',
     *     fullPath: 'full_path',
     *     isLeaf: 'is_leaf',
     *     keyPre: 'revise_bills_maxLid:'
     * }
     * @return {void}
     */
    constructor(ctx, setting) {
        super(ctx);
        this.tableName = setting.tableName;
        this.setting = setting;
        // 以下字段仅可通过树结构操作改变，不可直接通过update方式从接口提交，发现时过滤
        this.readOnlyFields = ['id'];
        this.readOnlyFields.push(this.setting.mid);
        this.readOnlyFields.push(this.setting.kid);
        this.readOnlyFields.push(this.setting.pid);
        this.readOnlyFields.push(this.setting.order);
        this.readOnlyFields.push(this.setting.level);
        this.readOnlyFields.push(this.setting.fullPath);
        this.readOnlyFields.push(this.setting.isLeaf);
    }

    getCondition (condition) {
        const result = {};
        if (condition.mid) result[this.setting.mid] = condition.mid;
        if (condition.kid) result[this.setting.kid] = condition.kid;
        if (condition.pid) result[this.setting.pid] = condition.pid;
        if (condition.order) result[this.setting.order] = condition.order;
        if (condition.level) result[this.setting.level] = condition.level;
        if (condition.fullPath) result[this.setting.fullPath] = condition.fullPath;
        if (condition.isLeaf) result[this.setting.isLeaf] = condition.isLeaf;
        return result;
    }

    /**
     * 获取 修订 清单数据
     * @param {Number} mid - masterId
     * @returns {Promise<void>}
     */
    async getData(mid, level) {
        if (level) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('list_id', {
                operate: '=',
                value: mid,
            });
            this.sqlBuilder.setAndWhere('level', {
                operate: '<=',
                value: level,
            });
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            return await this.db.query(sql, sqlParam);
        } else {
            return await this.db.select(this.tableName, {
                where: this.getCondition({mid: mid})
            });
        }
    }

    /**
     * 获取节点数据
     * @param {Number} mid - masterId
     * @param {Number} id
     * @returns {Promise<void>}
     */
    async getDataByKid (mid, kid) {
        return await this.db.get(this.tableName, this.getCondition({
            mid: mid, kid: kid,
        }));
    }

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

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

    /**
     * 获取节点数据
     * @param id
     * @returns {Promise<Array>}
     */
    async getDataById(id) {
        if (id instanceof Array) {
            return await this.db.select(this.tableName, { where: {id: id} });
        } else {
            return await this.db.get(this.tableName, { id: id });
        }
    }

    /**
     * 获取最末的子节点
     * @param {Number} mid - masterId
     * @param {Number} pid - 父节点id
     * @return {Object}
     */
    async getLastChildData(mid, pid) {
        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.pid, {
            value: pid,
            operate: '=',
        });
        this.sqlBuilder.orderBy = [['order', 'DESC']];
        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
        const resultData = await this.db.queryOne(sql, sqlParam);

        return resultData;
    }

    /**
     * 根据 父节点id 和 节点排序order 获取数据
     *
     * @param {Number} mid - master id
     * @param {Number} pid - 父节点id
     * @param {Number|Array} order - 排序
     * @return {Object|Array} - 查询结果
     */
    async getDataByParentAndOrder(mid, pid, order) {
        const result = await this.db.select(this.tableName, {
            where: this.getCondition({mid: mid, pid: pid, order: order})
        });
        return order instanceof Array ? result : (result.length > 0 ? result[0] : null);
    }

    async getChildBetween(mid, pid, order1, order2) {
        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.pid, {
            value: pid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.order, {
            value: order1,
            operate: '>',
        });
        this.sqlBuilder.setAndWhere(this.setting.order, {
            value: order2,
            operate: '<',
        });
        this.sqlBuilder.orderBy = [['order', 'ASC']];

        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
        const data = await this.db.query(sql, sqlParam);

        return data;
    }

    /**
     * 根据 父节点ID 和 节点排序order 获取全部后节点数据
     * @param {Number} mid - master id
     * @param {Number} pid - 父节点id
     * @param {Number} order - 排序
     * @return {Array}
     */
    async getNextsData(mid, pid, order) {
        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.pid, {
            value: pid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.order, {
            value: order,
            operate: '>',
        });
        this.sqlBuilder.orderBy = [['order', 'ASC']];

        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
        const data = await this.db.query(sql, sqlParam);

        return data;
    }

    /**
     * 根据full_path获取数据 full_path Like ‘1.2.3%’（传参full_path = '1.2.3%'）
     * @param {Number} tenderId - 标段id
     * @param {String} full_path - 路径
     * @return {Promise<void>}
     */
    async getDataByFullPath(mid, full_path) {
        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.fullPath, {
            value: this.db.escape(full_path),
            operate: 'Like',
        });
        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
        const resultData = await this.db.query(sql, sqlParam);
        return resultData;
    }
    /**
     * 根据full_path检索自己及所有父项
     * @param {Number} tenderId - 标段id
     * @param {Array|String} fullPath - 节点完整路径
     * @return {Promise<*>}
     * @private
     */
    async getFullLevelDataByFullPath(mid, fullPath) {
        const explodePath = this.ctx.helper.explodePath(fullPath);

        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.fullPath, {
            value: explodePath,
            operate: 'in',
        });

        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
        const data = await this.db.query(sql, sqlParam);

        return data;
    }

    /**
     * 根据 父节点id 获取子节点
     * @param tenderId
     * @param nodeId
     * @return {Promise<*>}
     */
    async getChildrenByParentId(mid, pid) {
        if (mid <= 0 || !pid) return undefined;
        const pids = pid instanceof Array ? pid : [pid];

        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.pid, {
            value: pids,
            operate: 'in',
        });
        this.sqlBuilder.orderBy = [['order', 'ASC']];

        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
        const data = await this.db.query(sql, sqlParam);

        return data;
    }
    /**
     * 根据 父节点id 获取孙子节点
     * @param tenderId
     * @param nodeId
     * @return {Promise<void>}
     */
    async getPosterityByParentId(mid, pid) {
        if (mid <= 0 || !pid) return undefined;

        const node = await this.getDataByKid(mid, pid);

        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.fullPath, {
            value: this.db.escape(node[this.setting.fullPath] + '-%'),
            operate: 'like',
        });

        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
        const data = await this.db.query(sql, sqlParam);

        return data;
    }

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

    /**
     * 缓存最大节点id
     *
     * @param {Number} mid - master id
     * @param {Number} maxId - 当前最大节点id
     * @returns {Promise<void>}
     * @private
     */
    _cacheMaxLid(mid, maxId) {
        this.cache.set(this.setting.keyPre + mid , 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(mid, pid, order, incre = 1) {
        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.order, {
            value: order,
            operate: '>=',
        });
        this.sqlBuilder.setAndWhere(this.setting.pid, {
            value: pid,
            operate: '=',
        });
        this.sqlBuilder.setUpdateData(this.setting.order, {
            value: Math.abs(incre),
            selfOperate: incre > 0 ? '+' : '-',
        });
        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
        const data = await this.transaction.query(sql, sqlParam);

        return data;
    }

    /**
     * 新增数据(新增为selectData的后项，该方法不可单独使用)
     *
     * @param {Number} mid - 台账id
     * @param {Object} select - 选中节点的数据
     * @param {Object} data - 新增节点的初始数据
     * @return {Object} - 新增结果
     * @private
     */
    async _addNodeData(mid, select, data) {
        if (!data) {
            data = {};
        }
        const maxId = await this._getMaxLid(mid);

        if (this.setting.uuid) data.id = this.uuid.v4();
        data[this.setting.kid] = maxId + 1;
        data[this.setting.pid] = select ? select[this.setting.pid] : rootId;
        data[this.setting.mid] = mid;
        data[this.setting.level] = select ? select[this.setting.level] : 1;
        data[this.setting.order] = select ? select[this.setting.order] + 1 : 1;
        data[this.setting.fullPath] = data[this.setting.level] > 1 ? select[this.setting.fullPath].replace('-' + select[this.setting.kid], '-' + data[this.setting.kid]) : data[this.setting.kid] + '';
        data[this.setting.isLeaf] = true;
        const result = await this.transaction.insert(this.tableName, data);

        this._cacheMaxLid(mid, maxId + 1);

        return result;
    }

    /**
     * 新增节点
     * @param {Number} mid - 台账id
     * @param {Number} kid - 清单节点id
     * @returns {Promise<void>}
     */
    async addNode(mid, kid, data) {
        if (!mid) return null;
        const select = kid ? await this.getDataByKid(mid, kid) : null;
        if (kid && !select) throw '新增节点数据错误';

        this.transaction = await this.db.beginTransaction();
        try {
            if (select) await this._updateChildrenOrder(mid, select[this.setting.pid], select[this.setting.order]+1);
            const newNode = await this._addNodeData(mid, select, data);
            if (newNode.affectedRows !== 1) throw '新增节点数据错误';
            await this.transaction.commit();
            this.transaction = null;
        } catch (err) {
            await this.transaction.rollback();
            this.transaction = null;
            throw err;
        }

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

    async addNodeBatch(mid, kid, data, count = 1) {
        if (!mid) return null;
        const select = kid ? await this.getDataByKid(mid, kid) : null;
        if (kid && !select) throw '新增节点数据错误';

        this.transaction = await this.db.beginTransaction();
        try {
            if (select) await this._updateChildrenOrder(mid, select[this.setting.pid], select[this.setting.order] + 1, count);
            const newDatas = [];
            const maxId = await this._getMaxLid(mid);
            for (let i = 1; i < count + 1; i++) {
                const newData = Object.assign({}, data);
                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.mid] = mid;
                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 this.transaction.insert(this.tableName, newDatas);
            this._cacheMaxLid(mid, maxId + count);

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

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

    /**
     * 删除相关数据 用于继承
     * @param mid
     * @param deleteData
     * @returns {Promise<void>}
     * @private
     */
    async _deleteRelaData(mid, deleteData) {

    }

    /**
     * 删除节点
     * @param {Number} tenderId - 标段id
     * @param {Object} deleteData - 删除节点数据
     * @return {Promise<*>}
     * @private
     */
    async _deleteNodeData(mid, deleteNode) {
        this.initSqlBuilder();
        this.sqlBuilder.setAndWhere(this.setting.mid, {
            value: mid,
            operate: '=',
        });
        this.sqlBuilder.setAndWhere(this.setting.fullPath, {
            value: this.db.escape(deleteNode[this.setting.fullPath] + '%'),
            operate: 'Like',
        });
        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'delete');
        const result = await this.transaction.query(sql, sqlParam);

        return result;
    }

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

        this.transaction = await this.db.beginTransaction();
        try {
            // 删除
            const operate = await this._deleteNodeData(mid, select);
            // 选中节点--父节点 只有一个子节点时，应升级is_leaf
            if (parent) {
                const count = await this.db.count(this.tableName, this.getCondition({mid: mid, pid: select[this.setting.pid]}));
                if (count === 1) {
                    const updateParent = {id: parent.id };
                    updateParent[this.setting.isLeaf] = true;
                    await this.transaction.update(this.tableName, updateParent);
                }
            }
            // 选中节点--全部后节点 order--
            await this._updateChildrenOrder(mid, select[this.setting.pid], select[this.setting.order] + 1, -1);
            // 删除部位明细
            await this._deleteRelaData(mid, deleteData);
            await this.transaction.commit();
            this.transaction = null;
        } catch (err) {
            await this.transaction.rollback();
            this.transaction = null;
            throw err;
        }
        // 查询结果
        const updateData = await this.getNextsData(mid, select[this.setting.pid], select[this.setting.order] - 1);
        if (parent) {
            const updateData1 = await this.getDataByKid(mid, select[this.setting.pid]);
            if (updateData1[this.setting.isLeaf]) {
                updateData.push(updateData1);
            }
        }
        return { delete: deleteData, update: updateData };
    }

    async deleteNodes(mid, kid, count) {
        if ((mid <= 0) || (kid <= 0) || (count <= 0)) return [];
        const selects = await this.getDataByKidAndCount(mid, kid, count);
        const first = selects[0];
        const parent = await this.getDataByKid(mid, first[this.setting.pid]);
        const childCount = parent ? await this.count(this.getCondition({mid: mid, pid: parent[this.setting.kid]})) : -1;
        let deleteData = [];
        for (const s of selects) {
            deleteData = deleteData.concat(await this.getDataByFullPath(mid, s[this.setting.fullPath] + '%'));
        }

        this.transaction = await this.db.beginTransaction();
        try {
            // 删除
            for (const s of selects) {
                const operate = await this._deleteNodeData(mid, s);
            }
            // 选中节点--父节点 只有一个子节点时，应升级is_leaf
            if (parent && childCount === count) {
                const updateParent = {id: parent.id };
                updateParent[this.setting.isLeaf] = true;
                await this.transaction.update(this.tableName, updateParent);
            }
            // 选中节点--全部后节点 order--
            await this._updateChildrenOrder(mid, first[this.setting.pid], first[this.setting.order] + count, -count);
            await this.transaction.commit();
            this.transaction = null;

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

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

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

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

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

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

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

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

    /**
     * 节点成为父项时，可能需要修改父项数据，供子类继承
     * @param data
     */
    clearParentingData (data) {
    }

    /**
     * 升级selectData, 同步修改所有子节点
     * @param {Object} selectData - 升级操作，选中节点
     * @return {Object}
     * @private
     */
    async _syncUplevelChildren(select) {
        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 data = await this.transaction.query(sql, sqlParam);

        return data;
    }

    /**
     * 选中节点的后兄弟节点，全部变为当前节点的子节点
     * @param {Object} selectData - 选中节点
     * @return {Object}
     * @private
     */
    async _syncUpLevelNexts(select) {
        // 查询selectData的lastChild
        const lastChild = await this.getLastChildData(select[this.setting.mid], select[this.setting.kid]);
        const nexts = await this.getNextsData(select[this.setting.mid], 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');
            await this.transaction.query(sql1, sqlParam1);

            // 选中节点 is_leaf应为false
            if (select.is_leaf) {
                const updateData = { id: select.id, is_leaf: false };
                await this.transaction.update(this.tableName, updateData);
            }

            // 修改nextsData及其子节点的full_path
            const oldSubStr = this.db.escape(select[this.setting.pid] + '-');
            const newSubStr = this.db.escape(select[this.setting.kid] + '-');
            const sqlArr = [];
            sqlArr.push('Update ?? SET `full_path` = Replace(`full_path`,' + oldSubStr + ',' + newSubStr + ') Where');
            sqlArr.push('(`' + this.setting.mid + '` = ' + select[this.setting.mid] + ')');
            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 = await this.transaction.query(sql, [this.tableName]);
            return resultData;
        }
    }

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

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

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

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

    /**
     * 降级selectData, 同步修改所有子节点
     * @param {Object} selectData - 选中节点
     * @param {Object} preData - 选中节点的前一节点（降级后为父节点）
     * @return {Promise<*>}
     * @private
     */
    async _syncDownlevelChildren(select, pre) {
        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.kid] + '-'), this.db.escape(pre[this.setting.kid] + '-' + select[this.setting.kid] + '-')],
            literal: 'Replace',
        });
        const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
        const data = await this.transaction.query(sql, sqlParam);

        return data;
    }

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

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

            for (const [i, s] of selects.entries()) {
                // 选中节点 修改pid, level, order, full_path
                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;
                const orgLastPath = s[this.setting.level] === 1 ? s[this.setting.kid] : '-' + s[this.setting.kid];
                const newLastPath = s[this.setting.level] === 1 ? pre[this.setting.kid] + '-' + s[this.setting.kid] : '-' + pre[this.setting.kid] + '-' + s[this.setting.kid];
                updateData[this.setting.fullPath] = s[this.setting.fullPath].replace(orgLastPath, newLastPath);
                newPath.push(updateData[this.setting.fullPath]);
                await this.transaction.update(this.tableName, updateData);
                // 选中节点--全部子节点（含孙） level++, full_path
                await this._syncDownlevelChildren(s, pre);
            }
            // 选中节点--前兄弟节点 is_leaf应为false, 清空计算相关字段
            const updateData2 = { id: pre.id };
            updateData2[this.setting.isLeaf] = false;
            this.clearParentingData(updateData2);
            await this.transaction.update(this.tableName, updateData2);
            await this.transaction.commit();
            this.transaction = null;
        } catch (err) {
            await this.transaction.rollback();
            this.transaction = null;
            throw err;
        }

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

    /**
     * 过滤data中update方式不可提交的字段
     * @param {Number} id - 主键key
     * @param {Object} data
     * @return {Object<{id: *}>}
     * @private
     */
    _filterUpdateInvalidField(id, data) {
        const result = {id: id};
        for (const prop in data) {
            if (this.readOnlyFields.indexOf(prop) === -1) {
                result[prop] = data[prop];
            }
        }
        return result;
    }

    /**
     * 提交多条数据 - 不影响计算等未提交项
     * @param {Number} tenderId - 标段id
     * @param {Array} datas - 提交数据
     * @return {Array} - 提交后的数据
     */
    async updateInfos(mid, datas) {
        if (mid <= 0) throw '数据错误';

        if (Array.isArray(datas)) {
            const updateDatas = [];
            for (const d of datas) {
                if (mid !== d[this.setting.mid]) throw '提交数据错误';
                const node = await this.getDataById(d.id);
                if (!node || mid !== node[this.setting.mid] || d[this.setting.kid] !== node[this.setting.kid]) throw '提交数据错误';
                updateDatas.push(this._filterUpdateInvalidField(node.id, d));
            }
            await this.db.updateRows(this.tableName, updateDatas);

            const resultData = await this.getDataById(this._.map(datas, 'id'));
            return resultData;
        } else {
            if (mid !== datas[this.setting.mid]) throw '提交数据错误';
            const node = await this.getDataById(datas.id);
            if (!node || mid !== node[this.setting.mid] || datas[this.setting.kid] !== node[this.setting.kid]) throw '提交数据错误';
            const updateData = this._filterUpdateInvalidField(node.id, datas);
            await this.db.update(this.tableName, updateData);
            return await this.getDataById(datas.id);
        }
    }

    async pasteBlockRelaData(relaData) {
        if (!this.transaction) throw '更新数据错误';
        // for (const id of relaData) {
        //     await this.ctx.service.pos.copyBillsPosData(id.org, id.new, this.transaction);
        // }
    }

    async getPasteBlockResult(select, copyNodes) {
        const createData = await this.getDataByIds(newIds);
        const updateData = await this.getNextsData(selectData[this.setting.mid], selectData[this.setting.pid], selectData[this.setting.order] + copyNodes.length);
        //const posData = await this.ctx.service.pos.getPosData({ lid: newIds });
        return {
            ledger: { create: createData, update: updateData },
            //pos: posData,
        };
    }

    /**
     * 复制粘贴整块
     * @param {Number} tenderId - 标段Id
     * @param {Number} selectId - 选中几点Id
     * @param {Array} block - 复制节点Id
     * @return {Object} - 提价后的数据(其中新增粘贴数据，只返回第一层)
     */
    async pasteBlock(mid, kid, paste) {
        if ((mid <= 0) || (kid <= 0)) return [];

        const selectData = await this.getDataByKid(mid, kid);
        if (!selectData) throw '数据错误';

        const copyNodes = await this.getDataByNodeIds(paste.tid, paste.block);
        if (!copyNodes || copyNodes.length <= 0)  throw '复制数据错误';
        for (const node of copyNodes) {
            if (node[this.setting.pid] !== copyNodes[0][this.setting.pid]) throw '复制数据错误：仅可操作同层节点';
        }

        const newParentPath = selectData[this.setting.fullPath].replace(selectData[this.setting.kid], '');
        const orgParentPath = copyNodes[0][this.setting.fullPath].replace(copyNodes[0][this.setting.kid], '');
        const newIds = [];
        this.transaction = await this.db.beginTransaction();
        try {
            // 选中节点的所有后兄弟节点，order+粘贴节点个数
            await this._updateChildrenOrder(mid, selectData[this.setting.pid], selectData[this.setting.order], copyNodes.length);
            for (let iNode = 0; iNode < copyNodes.length; iNode++) {
                const node = copyNodes[iNode];
                let datas = await this.getDataByFullPath(mid, node[this.setting.fullPath] + '%');
                datas = this._.sortBy(datas, 'level');

                const maxId = await this._getMaxLid(mid);
                this._cacheMaxLid(mid, maxId + datas.length);

                const billsId = [];
                // 计算粘贴数据中需更新部分
                for (let index = 0; index < datas.length; index++) {
                    const data = datas[index];
                    const newId = maxId + index + 1;
                    const idChange = {
                        org: data.id,
                    };
                    if (this.setting.uuid) data.id = this.uuid.v4();
                    idChange.new = data.id;
                    data[this.setting.mid] = mid;
                    if (!data[this.setting.isLeaf]) {
                        for (const children of datas) {
                            children[this.setting.fullPath] = children[this.setting.fullPath].replace('-' + data[this.setting.kid], '-' + newId);
                            if (children[this.setting.pid] === data[this.setting.kid]) {
                                children[this.setting.pid] = newId;
                            }
                        }
                    } else {
                        data[this.setting.fullPath] = data[this.setting.fullPath].replace('-' + data[this.setting.kid], '-' + newId);
                    }
                    data[this.setting.kid] = newId;
                    data[this.setting.fullPath] = data[this.setting.fullPath].replace(orgParentPath, newParentPath);
                    if (data[this.setting.pid] === node[this.setting.pid]) {
                        data[this.setting.pid] = selectData[this.setting.pid];
                        data[this.setting.order] = selectData[this.setting.order] + iNode + 1;
                    }
                    data[this.setting.level] = data[this.setting.level] + selectData[this.setting.level] - copyNodes[0][this.setting.level];
                    idChange.isLeaf = data[this.setting.isLeaf];
                    billsId.push(idChange);
                    newIds.push(data.id);
                }
                const newData = await this.transaction.insert(this.tableName, datas);
                await this.pasteBlockRelaData(billsId);
            }
            // 数据库创建新增节点数据
            await this.transaction.commit();
            this.transaction = null;
        } catch (err) {
            await this.transaction.rollback();
            this.transaction = null;
            throw err;
        }

        // 查询应返回的结果
        return await this.getPasteBlockResult(selectData, copyNodes);
    }

}

module.exports = TreeService;