Calculation/app/service/change_ledger.js

'use strict';

/**
 * 变更插入 --- 标段--台账 数据模型
 *
 * @author CaiAoLin
 * @date 2017/12/1
 * @version
 */

const needField = {
    id: 'ledger_id',
    pid: 'ledger_pid',
    order: 'order',
    level: 'level',
    fullPath: 'full_path',
    isLeaf: 'is_leaf',
};
const keyFields = {
    table: ['id'],
    index: ['tender_id', 'ledger_id'],
};
// 以下字段仅可通过树结构操作改变，不可直接通过update方式从接口提交，发现时过滤
const readOnlyFields = ['id', 'tender_id', 'ledger_id', 'ledger_pid', 'order', 'level', 'full_path', 'is_leaf'];
const calcFields = ['unit_price', 'sgfh_qty', 'sgfh_tp', 'sjcl_qty', 'sjcl_tp', 'qtcl_qty', 'qtcl_tp', 'deal_qty', 'deal_tp', 'dgn_qty1', 'dgn_qty2'];
const upFields = ['unit_price'];
const qtyFields = ['sgfh_qty', 'sjcl_qty', 'qtcl_qty', 'quantity', 'deal_qty'];
const tpFields = ['sgfh_tp', 'sjcl_tp', 'qtcl_tp', 'total_price', 'deal_tp'];
const rootId = -1;
const keyPre = 'tender_node_maxId:';
const measureType = require('../const/tender').measureType;
const SumLoad = require('../lib/sum_load');
const billsUtils = require('../lib/bills_utils');

module.exports = app => {

    class ChangeLedger extends app.BaseBillsService {

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

        /**
         * 获取节点数据
         * @param {Number} mid - masterId
         * @param {Number} id
         * @returns {Promise<void>}
         */
        async getDataByKid(mid, kid) {
            const bills = await this.db.get(this.ctx.service.ledger.tableName, this.getCondition({
                mid: mid, kid: kid,
            }));
            if (bills) {
                this.newBills = false;
                return bills;
            }
            const cBills = await this.db.get(this.tableName, this.getCondition({mid: mid, kid: kid}));
            // 判断父节点是否是变更新建的
            const parentBills = await this.db.get(this.tableName, this.getCondition({
                mid: mid, kid: cBills[this.setting.pid],
            }));
            this.newBills = parentBills !== null && parentBills !== undefined;
            return cBills;
        }

        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(this.tableName, mid, select[this.setting.pid], select[this.setting.order] - 1);
                if (selects.length < count) throw '数据错误';
                return selects.slice(0, count);
            } else {
                return [select];
            }
        }

        /**
         * 更新order
         * @param {Number} mid - master id
         * @param {Number} pid - 父节点id
         * @param {Number} order - 开始更新的order
         * @param {Number} incre - 更新的增量
         * @returns {Promise<*>}
         * @private
         */
        async _updateChildrenOrder(tableName, mid, pid, order, incre = 1, transaction = this.transaction) {
            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(tableName, 'update');
            const data = await transaction.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.ctx.service.ledger.tableName, mid];
                const queryResult = await this.db.queryOne(sql, sqlParam);
                maxId = queryResult.max_id || 0;
                // if (this.newBills) {
                const sql2 = 'SELECT Max(??) As max_id FROM ?? Where ' + this.setting.mid + ' = ?';
                const sqlParam2 = [this.setting.kid, this.tableName, mid];
                const queryResult2 = await this.db.queryOne(sql2, sqlParam2);
                if (maxId < queryResult2.max_id || 0) {
                    maxId = queryResult2.max_id || 0;
                }
                // }
                const oldCacheKey = 'change_ledger_maxLid:' + mid;
                this.cache.del(oldCacheKey);
                this.cache.set(cacheKey, maxId, 'EX', this.ctx.app.config.cacheTime);
            }
            return maxId;
        }
        /**
         * 移除最大节点id（修订时使用）
         *
         * @param {Number} mid - master id
         * @return {Number}
         * @private
         */
        async _removeCacheMaxLid(mid) {
            return await this.cache.del(this.setting.keyPre + mid);
        }

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

        async getChildBetween(tableName, 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(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(tableName, 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(tableName);
            const data = await this.db.query(sql, sqlParam);

            return data;
        }

        /**
         * 根据节点Id获取数据
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} nodeId - 项目节/工程量清单节点id
         * @return {Object} - 返回查询到的节点数据
         */
        async getDataByNodeId(tenderId, nodeId) {
            if ((nodeId <= 0) || (tenderId <= 0)) {
                return undefined;
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_id', {
                value: nodeId,
                operate: '=',
            });

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.queryOne(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;
            this.newBills = false;
            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(this.tableName, mid, select[this.setting.pid], select[this.setting.order]+1);
                if (!this.newBills) {
                    if (select) await this._updateChildrenOrder(this.ctx.service.ledger.tableName, 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]);
                let updateData = await this.getNextsData(this.tableName, mid, select[this.setting.pid], select[this.setting.order] + 1);
                if (!this.newBills) {
                    const ledgerUpdateData = await this.getNextsData(this.ctx.service.ledger.tableName, mid, select[this.setting.pid], select[this.setting.order] + 1);
                    updateData = this._.orderBy(this._.concat(updateData, ledgerUpdateData), [this.setting.order], ['asc']);
                }
                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;
            this.newBills = false;
            const select = kid ? await this.getDataByKid(mid, kid) : null;
            if (kid && !select) throw '新增节点数据错误';

            this.transaction = await this.db.beginTransaction();
            try {
                // 判断select的父节点是否是变更新增的，如果是则修改自己的表就行了，否则修改2个ledger，changeLedger表
                if (select) await this._updateChildrenOrder(this.tableName, mid, select[this.setting.pid], select[this.setting.order] + 1, count);
                if (!this.newBills) {
                    if (select) await this._updateChildrenOrder(this.ctx.service.ledger.tableName, 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) {
                let createData = await this.getChildBetween(this.tableName, mid, select[this.setting.pid], select[this.setting.order], select[this.setting.order] + count + 1);
                let updateData = await this.getNextsData(this.tableName, mid, select[this.setting.pid], select[this.setting.order] + count);
                if (!this.newBills) {
                    const ledgerCreateData = await this.getChildBetween(this.ctx.service.ledger.tableName, mid, select[this.setting.pid], select[this.setting.order], select[this.setting.order] + count + 1);
                    const ledgerUpdateData = await this.getNextsData(this.ctx.service.ledger.tableName, mid, select[this.setting.pid], select[this.setting.order] + count);
                    createData = this._.orderBy(this._.concat(createData, ledgerCreateData), [this.setting.order], ['asc']);
                    updateData = this._.orderBy(this._.concat(updateData, ledgerUpdateData), [this.setting.order], ['asc']);
                }
                return { create: createData, update: updateData };
            } else {
                const createData = await this.getChildBetween(this.ctx.service.ledger.tableName, mid, -1, 0, count + 1);
                return { create: createData };
            }
        }

        /**
         * 删除相关数据 用于继承
         * @param mid
         * @param deleteData
         * @return {Promise<void>}
         * @private
         */
        async _deleteRelaData(mid, deleteData, transaction = this.transaction) {
            await this.ctx.service.changePos.deletePosData(transaction, mid, this._.map(deleteData, 'id'));
        }

        async _deleteChangeAuditListData(mid, deleteData, transaction = this.transaction) {
            await this.ctx.service.changeAuditList.deleteDataByRevise(transaction, mid, this._.map(deleteData, 'id'));
        }

        /**
         * 删除节点
         * @param {Number} tenderId - 标段id
         * @param {Object} deleteData - 删除节点数据
         * @return {Promise<*>}
         * @private
         */
        async _deleteNodeData(mid, deleteNode, transaction = this.transaction) {
            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 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 [];
            this.newBills = false;
            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(this.tableName, 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) {
                    let count = await this.db.count(this.tableName, this.getCondition({mid: mid, pid: select[this.setting.pid]}));
                    if (!this.newBills) {
                        const ledgerCount = await this.db.count(this.ctx.service.ledger.tableName, this.getCondition({mid: mid, pid: select[this.setting.pid]}));
                        count = count + ledgerCount;
                    }
                    if (count === 1) {
                        const updateParent = {id: parent.id };
                        updateParent[this.setting.isLeaf] = true;
                        await this.transaction.update(this.tableName, updateParent);
                    }
                }
                // 选中节点--全部后节点 order--
                await this._updateChildrenOrder(this.tableName, mid, select[this.setting.pid], select[this.setting.order] + 1, -1);
                if (!this.newBills) {
                    await this._updateChildrenOrder(this.ctx.service.ledger.tableName, mid, select[this.setting.pid], select[this.setting.order] + 1, -1);
                }
                // 删除部位明细
                await this._deleteRelaData(mid, deleteData);
                await this._deleteChangeAuditListData(mid, deleteData);
                await this.transaction.commit();
                this.transaction = null;
            } catch (err) {
                await this.transaction.rollback();
                this.transaction = null;
                throw err;
            }
            // 查询结果
            let updateData = await this.getNextsData(this.tableName, mid, select[this.setting.pid], select[this.setting.order] - 1);
            if (!this.newBills) {
                const ledgerUpdateData = await this.getNextsData(this.ctx.service.ledger.tableName, mid, select[this.setting.pid], select[this.setting.order] - 1);
                updateData = this._.orderBy(this._.concat(updateData, ledgerUpdateData), [this.setting.order], ['asc']);
            }
            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 [];
            this.newBills = false;
            const selects = await this.getDataByKidAndCount(mid, kid, count);
            const first = selects[0];
            const newBills = this.newBills;
            const parent = await this.getDataByKid(mid, first[this.setting.pid]);
            let childCount = 0;
            if (parent) {
                childCount = await this.db.count(this.tableName, this.getCondition({mid: mid, pid: parent[this.setting.kid]}));
                if (!newBills) {
                    const ledgerCount = await this.db.count(this.ctx.service.ledger.tableName, this.getCondition({mid: mid, pid: parent[this.setting.pid]}));
                    childCount = childCount + ledgerCount;
                }
            } else {
                childCount = -1;
            }
            // 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(this.tableName, 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(this.tableName, mid, first[this.setting.pid], first[this.setting.order] + count, -count);
                if (!newBills) {
                    await this._updateChildrenOrder(this.ctx.service.ledger.tableName, mid, first[this.setting.pid], first[this.setting.order] + count, -count);
                }
                // 删除部位明细
                await this._deleteRelaData(mid, deleteData);
                await this._deleteChangeAuditListData(mid, deleteData);
                await this.transaction.commit();
                this.transaction = null;

                let updateData = await this.getNextsData(this.tableName, mid, first[this.setting.pid], first[this.setting.order] - 1);
                if (!newBills) {
                    const ledgerUpdateData = await this.getNextsData(this.ctx.service.ledger.tableName, mid, first[this.setting.pid], first[this.setting.order] - 1);
                    updateData = this._.orderBy(this._.concat(updateData, ledgerUpdateData), [this.setting.order], ['asc']);
                }
                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 = null) {
            if (count && count > 1) {
                return await this.deleteNodes(mid, kid, count);
            } else {
                return await this.deleteNode(mid, kid);
            }
        }

        async deleteBySettle(transcation, mid, ledgerDatas) {
            if (ledgerDatas.length <= 0) return;
            const allDeleteData = [];
            for (const l of ledgerDatas) {
                // 删除部位明细
                const deleteData = await this.getDataByFullPath(this.tableName, mid, l[this.setting.fullPath] + '%', transcation);
                if (deleteData.length === 0) throw '删除节点数据错误';
                // deleteData数组插入到allDeleteData后面
                allDeleteData.push(...deleteData);
                const operate = await this._deleteNodeData(mid, l, transcation);
                await this._updateChildrenOrder(this.ctx.service.ledger.tableName, mid, l[this.setting.pid], l[this.setting.order] + 1, -1, transcation);
            }
            await this._deleteRelaData(mid, allDeleteData, transcation);
            await this._deleteChangeAuditListData(mid, allDeleteData, transcation);
        }

        /**
         * 上移节点
         *
         * @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;
            this.newBills = false;
            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 });
                    if (!this.newBills) {
                        await this.transaction.update(this.ctx.service.ledger.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 });
                if (!this.newBills) {
                    await this.transaction.update(this.ctx.service.ledger.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;
            this.newBills = false;
            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 });
                    if (!this.newBills) {
                        await this.transaction.update(this.ctx.service.ledger.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 });
                if (!this.newBills) {
                    await this.transaction.update(this.ctx.service.ledger.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 {Number} tenderId - 标段Id
         * @param {Number} selectId - 选中节点Id
         * @param {Object} data - 批量插入数据
         * @return {Promise<void>}
         */
        async batchInsertChild(tenderId, selectId, data) {
            const result = { ledger: {}, pos: null };
            if ((tenderId <= 0) || (selectId <= 0)) {
                return result;
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '位置数据错误';
            }

            this.transaction = await this.db.beginTransaction();
            const newIds = [];
            const lastChild = await this.getLastChildData(tenderId, selectId);
            try {
                // 更新父项isLeaf
                if (!lastChild) {
                    await this.transaction.update(this.tableName, {
                        id: selectData.id,
                        is_leaf: false,
                        unit_price: null,
                        sgfh_qty: null,
                        sgfh_tp: null,
                        sjcl_qty: null,
                        sjcl_tp: null,
                        qtcl_qty: null,
                        qtcl_tp: null,
                        quantity: null,
                        total_price: null,
                        deal_qty: null,
                        deal_tp: null,
                    });
                }
                const order = lastChild ? lastChild.order : 0;
                // 计算id
                const maxId = await this._getMaxLid(tenderId);

                // 数据库创建新增节点数据
                for (let i = 0, iLen = data.length; i < iLen; i++) {
                    // 合并新增数据
                    const qd = {
                        id: this.uuid.v4(),
                        tender_id: tenderId,
                        ledger_id: maxId + i + 1,
                        ledger_pid: selectData.ledger_id,
                        is_leaf: true,
                        order: order + i + 1,
                        level: selectData.level + 1,
                        b_code: data[i].b_code,
                        name: data[i].name,
                        unit: data[i].unit,
                        unit_price: data[i].price,
                    };
                    qd.full_path = selectData.full_path + '-' + qd.ledger_id;
                    const insertResult = await this.transaction.insert(this.tableName, qd);
                    newIds.push(qd.id);
                    if (data[i].pos.length > 0) {
                        await this.ctx.service.pos.insertLedgerPosData(this.transaction, tenderId, qd, data[i].pos);
                        await this.calcNode(qd, this.transaction);
                    }
                }
                this._cacheMaxLid(tenderId, maxId + data.length);
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询应返回的结果
            result.ledger.create = await this.getDataByIds(newIds);
            if (!lastChild) {
                result.ledger.update = await this.getDataByIds([selectData.id]);
            }
            result.pos = await this.ctx.service.pos.getPosData({tid: tenderId, lid: newIds });
            return result;
        }

        /**
         * 批量插入后项
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 选中节点Id
         * @param {Object} data - 批量插入数据
         * @return {Promise<void>}
         */
        async batchInsertNext(tenderId, selectId, data) {
            const result = { ledger: {}, pos: null };
            if ((tenderId <= 0) || (selectId <= 0)) {
                return result;
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '位置数据错误';
            }
            const parentData = await this.getDataByNodeId(tenderId, selectData.ledger_pid);
            if (!parentData) {
                throw '位置数据错误';
            }

            this.transaction = await this.db.beginTransaction();
            const newIds = [];
            try {
                // 选中节点的所有后兄弟节点，order+粘贴节点个数
                await this._updateChildrenOrder(tenderId, selectData.ledger_pid, selectData.order + 1, data.length);
                // 计算id和order
                const maxId = await this._getMaxLid(tenderId);
                const order = selectData.order;
                // 数据库创建新增节点数据
                for (let i = 0, iLen = data.length; i < iLen; i++) {
                    // 合并新增数据
                    const qd = {
                        id: this.uuid.v4(),
                        tender_id: tenderId,
                        ledger_id: maxId + i + 1,
                        ledger_pid: parentData.ledger_id,
                        is_leaf: true,
                        order: order + i + 1,
                        level: parentData.level + 1,
                        b_code: data[i].b_code,
                        name: data[i].name,
                        unit: data[i].unit,
                        unit_price: data[i].price,
                    };
                    qd.full_path = parentData.full_path + '-' + qd.ledger_id;
                    const insertResult = await this.transaction.insert(this.tableName, qd);
                    newIds.push(qd.id);
                    if (data[i].pos.length > 0) {
                        await this.ctx.service.pos.insertLedgerPosData(this.transaction, tenderId, qd, data[i].pos);
                        await this.calcNode(qd, this.transaction);
                    }
                }
                this._cacheMaxLid(tenderId, maxId + data.length);
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询应返回的结果
            result.ledger.create = await this.getDataByIds(newIds);
            result.ledger.update = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.order + data.length);
            result.pos = await this.ctx.service.pos.getPosData({tid: tenderId, lid: newIds });
            return result;
        }

        /**
         * 升级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.getLastChildDataFormCR(select[this.setting.mid], select[this.setting.kid]);
            const nexts = await this.getNextsData(this.tableName, 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]);
                // 存在下节点，则把此变更清单删除
                await this._deleteChangeAuditListData(select[this.setting.mid], [select]);
                return resultData;
            }
        }

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

            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,
                    });
                    // throw '不能升级为最高级节点';
                }
                // 选中节点--父节点--全部后兄弟节点 order+1
                await this._updateChildrenOrder(this.tableName, mid, parent[this.setting.pid], parent[this.setting.order] + 1, count);
                if (!this.newBills) {
                    await this._updateChildrenOrder(this.ctx.service.ledger.tableName, 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.id === last.id) {
                        const nexts = await this.getNextsData(this.tableName, mid, parent[this.setting.kid], last[this.setting.order]);
                        if (!this.newBills) {
                            const ledgerNexts = await this.getNextsData(this.ctx.service.ledger.tableName, mid, parent[this.setting.kid], last[this.setting.order]);
                            if (ledgerNexts.length > 0) {
                                throw '该节点下存在台账节点，不可升级';
                            }
                            // nexts = this._.orderBy(this._.concat(ledgerNexts, nexts), [this.setting.order], ['asc']);
                        }
                        if (s.is_leaf && 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(this.tableName, mid, parent[this.setting.pid], parent[this.setting.order] - 1);
            if (!this.newBills) {
                const ledgerUpdateData = await this.getNextsData(this.ctx.service.ledger.tableName, mid, parent[this.setting.pid], parent[this.setting.order] - 1);
                updateData = this._.orderBy(this._.concat(updateData, ledgerUpdateData), [this.setting.order], ['asc']);
            }
            for (const path of newPath) {
                const children = await this.getDataByFullPath(this.tableName, mid, path + '-%');
                updateData = updateData.concat(children);
                if (!this.newBills) {
                    updateData = updateData.concat(await this.getDataByFullPath(this.ctx.service.ledger.tableName, mid, path + '-%'));
                }
            }
            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;
        }

        async getDataByFullPath(tableName, mid, full_path, transaction = null) {
            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(tableName);
            const resultData = transaction ? await transaction.query(sql, sqlParam) : await this.db.query(sql, sqlParam);
            return resultData;
        }

        /**
         * 获取最末的子节点
         * @param {Number} mid - masterId
         * @param {Number} pid - 父节点id
         * @return {Object}
         */
        async getLastChildDataFormCR(mid, pid) {
            let preLastChild = await this.getLastChildData(this.tableName, mid, pid);
            if (!this.newBills) {
                const preLedgerLastChild = await this.getLastChildData(this.ctx.service.ledger.tableName, mid, pid);
                if (!preLastChild) {
                    preLastChild = preLedgerLastChild;
                } else {
                    preLastChild = preLedgerLastChild && preLedgerLastChild[this.setting.order] > preLastChild[this.setting.order] ? preLedgerLastChild : preLastChild;
                }
            }
            return preLastChild;
        }

        async getLastChildData(tableName, 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(tableName);
            const resultData = await this.db.queryOne(sql, sqlParam);

            return resultData;
        }

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

            const newPath = [];
            this.transaction = await this.db.beginTransaction();
            try {
                // 选中节点--全部后节点 order--
                await this._updateChildrenOrder(this.tableName, mid, first[this.setting.pid], last[this.setting.order] + 1, -count);
                if (!this.newBills) {
                    await this._updateChildrenOrder(this.ctx.service.ledger.tableName, mid, first[this.setting.pid], last[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]);
                    // console.log(updateData);
                    await this.transaction.update(this.tableName, updateData);
                    // 选中节点--全部子节点（含孙） level++, full_path
                    await this._syncDownlevelChildren(s, pre);
                }
                // 选中节点--前兄弟节点 is_leaf应为false, 清空计算相关字段
                if (pre.ccid) {
                    const updateData2 = { id: pre.id };
                    updateData2[this.setting.isLeaf] = false;
                    this.clearParentingData(updateData2);
                    await this.transaction.update(this.tableName, updateData2);
                    await this._deleteChangeAuditListData(mid, [pre]);
                } else if (!pre.ccid && pre.is_leaf === 1) {
                    throw '原台账节点为子项时不能降级成为它的子项';
                }
                await this.transaction.commit();
                this.transaction = null;
            } catch (err) {
                await this.transaction.rollback();
                this.transaction = null;
                throw err;
            }

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

        async pasteBlockData (tid, sid, pasteData, defaultData) {
            if ((tid <= 0) || (sid <= 0)) return [];

            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].ledger_pid !== pasteData[0][0].ledger_pid) throw '复制数据错误：仅可操作同层节点';
            }

            const selectData = await this.getDataByKid(tid, sid);
            if (!selectData) throw '粘贴数据错误';
            const newParentPath = selectData.full_path.replace(selectData.ledger_id, '');

            const pasteBillsData = [], pastePosData = [], leafBillsId = [];
            const tpDecimal = this.ctx.tender.info.decimal.tp;
            let maxId = await this._getMaxLid(this.ctx.tender.id);
            for (const [i, pd] of pasteData.entries()) {
                for (const d of pd) {
                    d.children = pd.filter(function (x) {
                        return x.ledger_pid === d.ledger_id;
                    });
                }
                const pbd = [];
                for (const [j, d] of pd.entries()) {
                    const newBills = {
                        id: this.uuid.v4(),
                        tender_id: tid,
                        ledger_id: maxId + j + 1,
                        ledger_pid: j === 0 ? selectData.ledger_pid : d.ledger_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,
                        b_code: d.b_code,
                        name: d.name,
                        unit: d.unit,
                        unit_price: this.ctx.helper.round(d.unit_price, this.ctx.tender.info.decimal.up),
                        source: d.source,
                        remark: d.remark,
                        drawing_code: d.drawing_code,
                        memo: d.memo,
                        ex_memo1: d.ex_memo1,
                        ex_memo2: d.ex_memo2,
                        ex_memo3: d.ex_memo3,
                        node_type: d.node_type,
                        sgfh_expr: d.sgfh_expr,
                        sjcl_expr: d.sjcl_expr,
                        qtcl_expr: d.qtcl_expr,
                        check_calc: 1,
                        ccid: this.ctx.change.cid,
                        formc: 1,
                        features: d.features,
                    };
                    for (const c of d.children) {
                        c.ledger_pid = newBills.ledger_id;
                    }
                    const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, newBills.unit);
                    newBills.deal_qty = this.ctx.helper.round(d.deal_qty, precision.value);
                    if (d.pos && d.pos.length > 0) {
                        for (const pos of d.pos) {
                            const newPos = {
                                id: this.uuid.v4(),
                                tid: tid,
                                lid: newBills.id,
                                name: pos.name,
                                drawing_code: pos.drawing_code,
                                add_user: this.ctx.session.sessionUser.accountId,
                                in_time: new Date(),
                                porder: pos.porder,
                                position: pos.position,
                                sgfh_expr: pos.sgfh_expr ? pos.sgfh_expr : '',
                                sjcl_expr: pos.sjcl_expr ? pos.sjcl_expr : '',
                                qtcl_expr: pos.qtcl_expr ? pos.qtcl_expr : '',
                                add_stage: 0,
                                add_stage_order: 0,
                                add_times: 0,
                                ex_memo1: pos.ex_memo1,
                                ex_memo2: pos.ex_memo2,
                                ex_memo3: pos.ex_memo3,
                                ccid: this.ctx.change.cid,
                                formc: 1,
                            };
                            this._calcExpr(newPos, 'sgfh_qty', pos.sgfh_expr, pos.sgfh_qty, precision);
                            this._calcExpr(newPos, 'sjcl_qty', pos.sjcl_expr, pos.sjcl_qty, precision);
                            this._calcExpr(newPos, 'qtcl_qty', pos.qtcl_expr, pos.qtcl_qty, precision);
                            newPos.quantity = this.ctx.helper.add(newPos.sgfh_qty,
                                this.ctx.helper.add(newPos.sjcl_qty, newPos.qtcl_qty));
                            newBills.sgfh_qty = this.ctx.helper.add(newBills.sgfh_qty, newPos.sgfh_qty);
                            newBills.sjcl_qty = this.ctx.helper.add(newBills.sjcl_qty, newPos.sjcl_qty);
                            newBills.qtcl_qty = this.ctx.helper.add(newBills.qtcl_qty, newPos.qtcl_qty);
                            if (defaultData) this.ctx.helper._.assignIn(newPos, defaultData);
                            pastePosData.push(newPos);
                        }
                    } else {
                        this._calcExpr(newBills, 'sgfh_qty', newBills.sgfh_expr, d.sgfh_qty, precision);
                        this._calcExpr(newBills, 'sjcl_qty', newBills.sjcl_expr, d.sjcl_qty, precision);
                        this._calcExpr(newBills, 'qtcl_qty', newBills.qtcl_expr, d.qtcl_qty, precision);
                    }
                    newBills.quantity = this.ctx.helper.add(newBills.sgfh_qty,
                        this.ctx.helper.add(newBills.sjcl_qty, newBills.qtcl_qty));
                    newBills.sgfh_tp = this.ctx.helper.mul(newBills.sgfh_qty, newBills.unit_price, tpDecimal);
                    newBills.sjcl_tp = this.ctx.helper.mul(newBills.qtcl_qty, newBills.unit_price, tpDecimal);
                    newBills.qtcl_tp = this.ctx.helper.mul(newBills.sjcl_qty, newBills.unit_price, tpDecimal);
                    newBills.total_price = this.ctx.helper.mul(newBills.quantity, newBills.unit_price, tpDecimal);
                    newBills.deal_tp = this.ctx.helper.mul(newBills.deal_qty, newBills.unit_price, tpDecimal);
                    if (defaultData) this.ctx.helper._.assignIn(newBills, defaultData);
                    pbd.push(newBills);
                }
                for (const d of pbd) {
                    const parent = pbd.find(function (x) {
                        return x.ledger_id === d.ledger_pid;
                    });
                    d.full_path = parent
                        ? parent.full_path + '-' + d.ledger_id
                        : newParentPath + d.ledger_id;
                    if (defaultData) this.ctx.helper._.assignIn(pbd, defaultData);
                    pasteBillsData.push(d);
                }
                maxId = maxId + pbd.length;
            }

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

            // 查询应返回的结果
            let updateData = await this.getNextsData(this.tableName, selectData.tender_id, selectData.ledger_pid, selectData.order + pasteData.length);
            if (!this.newBills) {
                const ledgerUpdateData = await this.getNextsData(this.ctx.service.ledger.tableName, selectData.tender_id, selectData.ledger_pid, selectData.order + pasteData.length);
                updateData = this._.orderBy(this._.concat(updateData, ledgerUpdateData), [this.setting.order], ['asc']);
            }
            return {
                ledger: { create: pasteBillsData, update: updateData },
                pos: pastePosData,
            };
        }

        /**
         * 添加标准节点，将选择的标准节点，添加为子节点（排序为末尾）
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 添加目标节点Id
         * @param {Object} stdData - 标准节点数据
         * @returns {Promise<*>}
         */
        async addStdNodeAsChild(tenderId, selectId, stdData, changeId) {
            const newData = this._filterStdData(stdData);
            const result = await this.addChild(tenderId, selectId, newData, changeId);
            return result;
        }
        /**
         * 新增子节点，并排在所有子节点的末尾
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 父节点Id
         * @param {Object} data - 新增节点数据（编号名称等）
         * @returns {Promise<*>}
         */
        async addChild(tenderId, selectId, data, changeId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            this.newBills = false;
            const selectData = await this.getDataByKid(tenderId, selectId);
            if (!selectData) {
                throw '新增节点数据错误';
            }
            let children = await this.getChildrenByParentId(this.tableName, tenderId, selectId);
            if (!this.newBills) {
                const ledgerChildren = await this.getChildrenByParentId(this.ctx.service.ledger.tableName, tenderId, selectId);
                children = this._.orderBy(this._.concat(ledgerChildren, children), [this.setting.order], ['asc']);
            }

            const maxId = await this._getMaxLid(tenderId);

            data.id = this.uuid.v4();
            data.tender_id = tenderId;
            data.ledger_id = maxId + 1;
            data.ledger_pid = selectData.ledger_id;
            data.level = selectData.level + 1;
            data.order = children.length + 1;
            data.full_path = selectData.full_path + '-' + data.ledger_id;
            data.is_leaf = true;
            data.ccid = changeId;
            data.check_calc = 1;
            // if (reviseId) data.crid = reviseId;

            this.transaction = await this.db.beginTransaction();
            try {
                const result = await this.transaction.insert(this.tableName, data);
                if (children.length === 0 && selectData.ccid) {
                    await this.transaction.update(this.tableName,
                        {
                            is_leaf: false,
                            sgfh_qty: null, sgfh_tp: null, qtcl_qty: null, qtcl_tp: null, sjcl_qty: null, sjcl_tp: null,
                            quantity: null, unit_price: null, total_price: null, deal_qty: null, deal_tp: null
                        },
                        { where: {tender_id: tenderId, ledger_id: selectData.ledger_id} });
                } else if (!selectData.ccid && children.length === 0) {
                    throw '原台账节点为子项时不能添加它的子项';
                }
                await this.transaction.commit();
            } catch (err) {
                this.transaction.rollback();
                throw err;
            }

            this._cacheMaxLid(tenderId, maxId + 1);

            // 查询应返回的结果
            const resultData = {};
            resultData.create = await this.getDataByKid(tenderId, data.ledger_id);
            if (children.length === 0) {
                resultData.update = await this.getDataByKid(tenderId, selectId);
            }
            return resultData;
        }

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

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: nodeIds,
                operate: 'in',
            });
            this.sqlBuilder.orderBy = [['order', 'ASC']];

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

            return data;
        }

        /**
         * 添加节点(来自标准清单)
         * @param {Number} tenderId
         * @param {Number} selectId
         * @param {Object} stdData
         * @return {Promise<*>}
         */
        async addStdNode(tenderId, selectId, stdData, changeId) {
            const newData = this._filterStdData(stdData);
            const result = await this.addBillsNode(tenderId, selectId, newData, changeId);
            return result;
        }

        async addBillsNode(tenderId, selectId, data, changeId) {
            if (data) {
                if (changeId) data.ccid = changeId;
                data.check_calc = 1;
                return await this.addNode(tenderId, selectId, data);
            } else {
                return await this.addNode(tenderId, selectId, {ccid: changeId, check_calc: 1});
            }
        }

        /**
         * 添加节点，并同步添加父节点
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @param {Object} stdData - 节点数据
         * @param {StandardLib} stdLib - 标准库
         * @return {Promise<void>}
         */
        async addStdNodeWithParent(tenderId, stdData, stdLib, changeId) {
            // 查询完整标准清单，并按层次排序
            const fullLevel = await stdLib.getFullLevelDataByFullPath(stdData.list_id, stdData.full_path);
            fullLevel.sort(function(x, y) {
                return x.level - y.level;
            });

            let isNew = false,
                node,
                firstNew,
                updateParent,
                addResult;
            const expandIds = [];
            this.transaction = await this.db.beginTransaction();
            try {
                // 从最顶层节点依次查询是否存在，否则添加
                for (let i = 0, len = fullLevel.length; i < len; i++) {
                    const stdNode = fullLevel[i];

                    if (isNew) {
                        const newData = this._filterStdData(stdNode);
                        newData.is_leaf = (i === len - 1);
                        [addResult, node] = await this._addChildNodeData(tenderId, node, newData, changeId);
                    } else {
                        const parent = node;
                        node = await this.getDataByCondition({
                            tender_id: tenderId,
                            ledger_pid: parent ? parent.ledger_id : rootId,
                            code: stdNode.code,
                            name: stdNode.name,
                        }) || await this.ctx.service.ledger.getDataByCondition({tender_id: tenderId, ledger_pid: parent ? parent.ledger_id : rootId, code: stdNode.code, name: stdNode.name });
                        if (!node) {
                            let children = await this.getChildrenByParentId(this.tableName, tenderId, parent.ledger_id);
                            if (!this.newBills) {
                                const ledgerChildren = await this.getChildrenByParentId(this.ctx.service.ledger.tableName, tenderId, parent.ledger_id);
                                children = this._.orderBy(this._.concat(ledgerChildren, children), [this.setting.order], ['asc']);
                            }
                            if (children.length === 0 && !parent.ccid) {
                                throw '原台账节点为子项时不能添加它的子项';
                            }
                            isNew = true;
                            const newData = this._filterStdData(stdNode);
                            newData.is_leaf = (i === len - 1);
                            [addResult, node] = await this._addChildAutoOrder(tenderId, parent, newData, changeId);
                            if (parent && parent.is_leaf && this.newBills) {
                                await this.transaction.update(this.tableName, { id: parent.id, is_leaf: false,
                                    unit_price: null, quantity: null, total_price: null, deal_qty: null, deal_tp: null});
                                updateParent = parent;
                            }
                            firstNew = node;
                        } else {
                            expandIds.push(node.ledger_id);
                        }
                    }
                }
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询应返回的结果
            let createData = [],
                updateData = [];
            if (firstNew) {
                createData = await this.getDataByFullPath(this.tableName, tenderId, firstNew.full_path + '%');
                updateData = await this.getNextsData(this.tableName, tenderId, firstNew.ledger_pid, firstNew.order);
                if (!this.newBills) {
                    createData = createData.concat(await this.getDataByFullPath(this.ctx.service.ledger.tableName, tenderId, firstNew.full_path + '%'));
                    const ledgerUpdateData = await this.getNextsData(this.ctx.service.ledger.tableName, tenderId, firstNew.ledger_pid, firstNew.order);
                    updateData = this._.orderBy(this._.concat(updateData, ledgerUpdateData), [this.setting.order], ['asc']);
                }
                if (updateParent) {
                    updateData.push(await this.getDataByCondition({ id: updateParent.id }));
                }
            }
            return { create: createData, update: updateData };
        }

        /**
         * 根据parentData, data新增数据(新增为parentData的最后一个子项)
         * @param {Number} tenderId - 标段id
         * @param {Object} parentData - 父项数据
         * @param {Object} data - 新增节点，初始数据
         * @return {Promise<*>} - 新增结果
         * @private
         */
        async _addChildNodeData(tenderId, parentData, data, changeId) {
            if (tenderId <= 0) {
                return undefined;
            }
            if (!data) {
                data = {};
            }
            const pid = parentData ? parentData.ledger_id : rootId;

            const maxId = await this._getMaxLid(tenderId);

            data.id = this.uuid.v4();
            data.tender_id = tenderId;
            data.ledger_id = maxId + 1;
            data.ledger_pid = pid;
            if (data.order === undefined) {
                data.order = 1;
            }
            data.level = parentData ? parentData.level + 1 : 1;
            data.full_path = parentData ? parentData.full_path + '-' + data.ledger_id : '' + data.ledger_id;
            if (data.is_leaf === undefined) {
                data.is_leaf = true;
            }
            data.check_calc = 1;
            if (changeId) data.ccid = changeId;
            const result = await this.transaction.insert(this.tableName, data);

            this._cacheMaxLid(tenderId, maxId + 1);

            return [result, data];
        }

        /**
         * 根据parentData, data新增数据(自动排序)
         * @param tenderId
         * @param parentData
         * @param data
         * @return {Promise<void>}
         * @private
         */
        async _addChildAutoOrder(tenderId, parentData, data, changeId) {
            const self = this;
            const findPreData = function(list, a) {
                if (!list || list.length === 0) { return null; }
                for (let i = 0, iLen = list.length; i < iLen; i++) {
                    if (billsUtils.compareCode(list[i].code, a.code) > 0) {
                        return i > 0 ? list[i - 1] : null;
                    }
                }
                return list[list.length - 1];
            };

            const pid = parentData ? parentData.ledger_id : rootId;
            let children = await this.getChildrenByParentId(this.tableName, tenderId, pid);
            if (!this.newBills) {
                const ledgerChildren = await this.getChildrenByParentId(this.ctx.service.ledger.tableName, tenderId, pid);
                children = this._.orderBy(this._.concat(children, ledgerChildren), [this.setting.order], ['asc']);
            }
            const preData = findPreData(children, data);
            if (!preData || children.indexOf(preData) < children.length - 1) {
                await this._updateChildrenOrder(this.tableName, tenderId, pid, preData ? preData.order + 1 : 1);
                if (!this.newBills) {
                    await this._updateChildrenOrder(this.ctx.service.ledger.tableName, tenderId, pid, preData ? preData.order + 1 : 1);
                }
            }
            data.order = preData ? preData.order + 1 : 1;
            const [addResult, node] = await this._addChildNodeData(tenderId, parentData, data, changeId);

            return [addResult, node];
        }

        /**
         * 提交数据 - 响应计算(增量方式计算)
         * @param {Number} tenderId
         * @param {Object} data
         * @return {Promise<*>}
         */
        async updateCalc(tenderId, data) {
            const helper = this.ctx.helper;
            // 简单验证数据
            if (tenderId <= 0 || !this.ctx.tender) {
                throw '标段不存在';
            }
            const info = this.ctx.tender.info;
            if (!data) {
                throw '提交数据错误';
            }
            const datas = data instanceof Array ? data : [data];
            const ids = [];
            for (const row of datas) {
                if (tenderId !== row.tender_id) {
                    throw '提交数据错误';
                }
                ids.push(row.id);
            }

            this.transaction = await this.db.beginTransaction();
            try {
                const updateDatas = [];
                for (const row of datas) {
                    const updateNode = await this.getDataById(row.id);
                    if (!updateNode || tenderId !== updateNode.tender_id || row.ledger_id !== updateNode.ledger_id) {
                        throw '提交数据错误';
                    }
                    let updateData;

                    // 更新单位或单价，全部数据都应重算
                    if (row.unit !== undefined || row.unit_price !== undefined) {
                        if (row.sgfh_qty === undefined) { row.sgfh_qty = updateNode.sgfh_qty; }
                        if (row.sjcl_qty === undefined) { row.sjcl_qty = updateNode.sjcl_qty; }
                        if (row.qtcl_qty === undefined) { row.qtcl_qty = updateNode.qtcl_qty; }
                        if (row.deal_qty === undefined) { row.deal_qty = updateNode.deal_qty; }
                    }

                    // 项目节、工程量清单相关
                    if (row.b_code) {
                        row.dgn_qty1 = null;
                        row.dgn_qty2 = null;
                        row.code = null;
                    }
                    if (row.code) row.b_code = null;

                    if (this._checkCalcField(row)) {
                        const calcData = JSON.parse(JSON.stringify(row));
                        calcData.check_calc = 1;
                        const precision = helper.findPrecision(info.precision, row.unit ? row.unit : updateNode.unit);
                        // 数量保留小数位数
                        helper.checkFieldPrecision(calcData, qtyFields, precision.value);
                        // 单位保留小数位数
                        helper.checkFieldPrecision(calcData, upFields, info.decimal.up);
                        // 未提交单价则读取数据库单价
                        if (row.unit_price === undefined) calcData.unit_price = updateNode.unit_price;
                        // 计算
                        if (row.sgfh_qty !== undefined || row.sjcl_qty !== undefined || row.qtcl_qty !== undefined ||
                            row.deal_qty !== undefined || row.unit_price) {
                            if (row.sgfh_qty === undefined) calcData.sgfh_qty = updateNode.sgfh_qty;
                            if (row.sjcl_qty === undefined) calcData.sjcl_qty = updateNode.sjcl_qty;
                            if (row.qtcl_qty === undefined) calcData.qtcl_qty = updateNode.qtcl_qty;
                            if (row.deal_qty === undefined) calcData.deal_qty = updateNode.deal_qty;
                            calcData.quantity = helper.sum([calcData.sgfh_qty, calcData.sjcl_qty, calcData.qtcl_qty]);
                            calcData.sgfh_tp = helper.mul(calcData.sgfh_qty, calcData.unit_price, info.decimal.tp);
                            calcData.sjcl_tp = helper.mul(calcData.sjcl_qty, calcData.unit_price, info.decimal.tp);
                            calcData.qtcl_tp = helper.mul(calcData.qtcl_qty, calcData.unit_price, info.decimal.tp);
                            calcData.total_price = helper.mul(calcData.quantity, calcData.unit_price, info.decimal.tp);
                            calcData.deal_tp = helper.mul(calcData.deal_qty, calcData.unit_price, info.decimal.tp);
                        } else if (row.sgfh_tp !== undefined || row.sjcl_tp !== undefined || row.qtcl_tp !== undefined || row.deal_tp !== undefined) {
                            calcData.sgfh_qty = null;
                            calcData.sjcl_qty = null;
                            calcData.qtcl_qty = null;
                            calcData.quantity = null;
                            calcData.deal_qty = null;
                            calcData.sgfh_tp = helper.round(row.sgfh_tp !== undefined ? calcData.row.sgfh_tp : updateNode.sgfh_tp, info.decimal.tp);
                            calcData.sjcl_tp = helper.round(row.sgfh_tp !== undefined ? calcData.row.sjcl_tp : updateNode.sjcl_tp, info.decimal.tp);
                            calcData.qtcl_tp = helper.round(row.sgfh_tp !== undefined ? calcData.row.qtcl_tp : updateNode.qtcl_tp, info.decimal.tp);
                            calcData.total_price = helper.sum([calcData.sgfh_tp, calcData.sjcl_tp, calcData.qtcl_tp]);
                            calcData.deal_tp = helper.round(row.deal_tp !== undefined ? calcData.row.deal_tp : updateNode.deal_tp, info.decimal.tp);
                        } else if (row.unit_price !== undefined) {
                            calcData.sgfh_tp = helper.mul(calcData.sgfh_qty, calcData.unit_price, info.decimal.tp);
                            calcData.sjcl_tp = helper.mul(calcData.sjcl_qty, calcData.unit_price, info.decimal.tp);
                            calcData.qtcl_tp = helper.mul(calcData.qtcl_qty, calcData.unit_price, info.decimal.tp);
                            calcData.total_price = helper.mul(calcData.quantity, calcData.unit_price, info.decimal.tp);
                            calcData.deal_tp = helper.mul(calcData.deal_qty, calcData.unit_price, info.decimal.tp);
                        }
                        updateData = this._filterUpdateInvalidField(updateNode.id, calcData);
                    } else {
                        updateData = this._filterUpdateInvalidField(updateNode.id, row);
                    }
                    await this.transaction.update(this.tableName, updateData);
                    // 如非子节点，需要更新底下所有已选清单的分部分项等数据
                    updateDatas.push(updateData);
                }
                // console.log(updateDatas);
                // 如果是子节点 更新了单位或单价，则更新数据并重算变更令总金额
                await this.ctx.service.changeAuditList.updateDataByReviseLedger(this.transaction, tenderId, updateDatas);
                await this.transaction.commit();
                this.transaction = null;
            } catch (err) {
                await this.transaction.rollback();
                this.transaction = null;
                throw err;
            }

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

    }

    return ChangeLedger;
};