Calculation/app/service/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 = ['quantity', 'unit_price', 'total_price', 'deal_qty', 'deal_tp', 'dgn_qty1', 'dgn_qty2'];
const upFields = ['unit_price'];
const qtyFields = ['quantity', 'deal_qty', 'dgn_qty1', 'dgn_qty2'];
const tpFields = ['total_price', 'deal_tp'];
const zeroRange = 0.0000000001;
const rootId = -1;
const keyPre = 'tender_node_maxId:';

module.exports = app => {

    class Ledger extends app.BaseService {

        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'ledger';
        }

        /**
         * 新增数据(供内部或其他service类调用, controller不可直接使用)
         * @param {Array|Object} data - 新增数据
         * @param {Number} tenderId - 标段id
         * @param {Object} transaction - 新增事务
         * @return {Promise<boolean>} - {Promise<是否正确新增成功>}
         */
        async innerAdd(data, tenderId, transaction) {
            const datas = data instanceof Array ? data : [data];
            if (tenderId <= 0) {
                throw '标段id错误';
            }
            if (datas.length <= 0) {
                throw '插入数据为空';
            }
            if (!transaction) {
                throw '内部错误';
            }
            // 整理数据
            const insertData = [];
            for (const tmp of datas) {
                tmp.ledger_id = tmp.id;
                tmp.ledger_pid = tmp.pid;
                tmp.tender_id = tenderId;
                delete tmp.id;
                delete tmp.pid;
                insertData.push(tmp);
            }
            const operate = await transaction.insert(this.tableName, insertData);
            return operate.affectedRows === datas.length;
        }
        /**
         * 新增数据
         *
         * @param {Object} data - 新增的数据(可批量)
         * @param {Number} tenderId - 标段id
         * @return {Boolean} - 返回新增的结果
         */
        async add(data, tenderId) {
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                result = await this.innerAdd(data, tenderId, this.transaction);
                if (!result) {
                    throw '新增数据错误';
                }
                await this.transaction.commit();
            } catch (error) {
                await this.transaction.rollback();
                result = false;
            }

            return result;
        }

        /**
         * 根据层级获取数据
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} showLevel - 显示层数
         * @return {Array} - 返回数据
         */
        async getDataByTenderId(tenderId, showLevel = 4) {
            if (tenderId <= 0) {
                return [];
            }
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });

            if (showLevel > 0) {
                this.sqlBuilder.setAndWhere('level', {
                    value: showLevel,
                    operate: '<=',
                });
            }

            const [sql, sqlParam] = this.sqlBuilder.build(this.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;
        }
        /**
         * 根据节点Id获取数据
         * @param {Number} tenderId - 标段Id
         * @param {Array} nodesIds - 节点Id
         * @return {Array}
         */
        async getDataByNodeIds(tenderId, nodesIds) {
            if (tenderId <= 0) {
                return [];
            }

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

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

            return data;
        }
        /**
         * 根据主键id获取数据
         * @param {Array|Number} id - 主键id
         * @return {Promise<*>}
         */
        async getDataByIds(id) {
            if (!id) {
                return;
            }
            const ids = id instanceof Array ? id : [id];
            if (ids.length === 0) {
                return;
            }
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('id', {
                value: ids,
                operate: 'in',
            });

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

            return data;
        }

        /**
         * 根据标准清单源检索
         * @param tenderId
         * @param source
         * @return {Promise<*>}
         */
        async getDataBySource(tenderId, source) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('source', {
                value: source,
                operate: '=',
            });

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

            return data;
        }

        /**
         * 获取最末的子节点
         * @param {Number} tenderId - 标段id
         * @param {Number} pid - 父节点id
         * @return {Object}
         */
        async getLastChildData(tenderId, pid) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_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} tenderId - 标段id
         * @param {Number} pid - 父节点id
         * @param {Number|Array} order - 排序
         * @return {Object|Array} - 查询结果
         */
        async getDataByParentAndOrder(tenderId, pid, order) {
            if ((tenderId <= 0) || (pid <= 0) || (order <= 0)) {
                return undefined;
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: pid,
                operate: '=',
            });
            if (order instanceof Array) {
                this.sqlBuilder.setAndWhere('order', {
                    value: order,
                    operate: 'in',
                });
            } else {
                this.sqlBuilder.setAndWhere('order', {
                    value: order,
                    operate: '=',
                });
            }

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            let data;
            if (order instanceof Array) {
                data = await this.db.query(sql, sqlParam);
            } else {
                data = await this.db.queryOne(sql, sqlParam);
            }

            return data;
        }
        /**
         * 根据 父节点id 获取子节点
         * @param tenderId
         * @param nodeId
         * @return {Promise<*>}
         */
        async getChildrenByParentId(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(this.tableName);
            const data = await this.db.query(sql, sqlParam);

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

            const node = await this.getDataByNodeId(tenderId, nodeId);

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('full_path', {
                value: this.db.escape(node.full_path + '.%'),
                operate: 'like',
            });

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

            return data;
        }
        /**
         * 根据 父节点ID 和 节点排序order 获取全部后节点数据
         * @param {Number} tenderId - 标段id
         * @param {Number} pid - 父节点id
         * @param {Number} order - 排序
         * @return {Array}
         */
        async getNextsData(tenderId, pid, order) {
            if ((tenderId <= 0) || (order < 0)) {
                return undefined;
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: pid,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('order', {
                value: order,
                operate: '>',
            });

            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(tenderId, full_path) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('full_path', {
                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(tenderId, fullPath) {
            const explodePath = this.ctx.helper.explodePath(fullPath);

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('full_path', {
                value: explodePath,
                operate: 'in',
            });

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

            return data;
        }

        /**
         * 获取项目工程量
         * @param tenderId
         * @returns {Promise<*>}
         */
        async getGatherGclBills(tenderId) {
            const sql = 'SELECT `b_code`, `name`, `unit`, `unit_price`, ' +
                        '    Sum(`quantity`) As `quantity`, Sum(`total_price`) As `total_price`, ' +
                        '    Sum(`deal_qty`) As `deal_qty`, Sum(`deal_tp`) As `deal_tp` ' +
                        '  From ?? ' +
                        '  WHERE `tender_id` = ? And `b_code` And `is_leaf` ' +
                        '  GROUP BY `b_code`, `name`, `unit`, `unit_price`';
            const sqlParam = [this.tableName, tenderId];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 获取sql条件语句片段，仅供search、searchRange方法使用
         * @param {Object} where - 条件参数
         * @return {*[]}
         * e.g.
         * where = {type: 'And', value: 'A', operate: '=', fields: ['code', 'name']}
         * return [sql: 'And (?? = \'A\' Or ?? = \'A\')', sqlParam: ['code', name]]
         * @private
         */
        _getWhereString(where) {
            const sqlParam = [],
                sqlPart = [];
            const values = where.value instanceof Array ? where.value : [where.value];
            const fields = where.fields instanceof Array ? where.fields : [where.fields];
            for (const field of fields) {
                for (const v of values) {
                    sqlPart.push('?? ' + where.operate + ' ' + v);
                    sqlParam.push(field);
                }
            }
            const sql = ' ' + (where.type ? where.type : 'And') + ' (' + sqlPart.join(' OR ') + ')';
            return [sql, sqlParam];
        }
        /**
         * 搜索台账
         *
         * @param {Number} tenderId - 标段id
         * @param {Object} key - 查询信息
         * @return {Promise<void>}
         */
        async search(tenderId, key) {
            let sql = 'Select * From ?? Where';
            let sqlParam = [this.tableName];

            sql = sql + ' ?? = ' + tenderId;
            sqlParam.push('tender_id');

            const [sql1, sqlParam1] = this._getWhereString(key);
            sql = sql + sql1;
            sqlParam = sqlParam.concat(sqlParam1);

            return this.db.query(sql, sqlParam);
        }
        /**
         * 范围内搜索台账
         *
         * @param {Number} tenderId - 标段id
         * @param {Object} key - 查询信息
         * @param {Array} range - 查询范围
         * @return {Promise<void>}
         */
        async searchRange(tenderId, key, range) {
            let sql = 'Select * From ?? Where';
            let sqlParam = [this.tableName];

            sql = sql + ' ?? = ' + tenderId;
            sqlParam.push('tender_id');

            let [sql1, sqlParam1] = this._getWhereString(key);
            sql = sql + sql1;
            sqlParam = sqlParam.concat(sqlParam1);

            for (const r of range) {
                [sql1, sqlParam1] = this._getWhereString(r);
                sql = sql + sql1;
                sqlParam = sqlParam.concat(sqlParam1);
            }

            return this.db.query(sql, sqlParam);
        }

        /**
         * 统计子节点total_price
         * @param {Number} tenderId - 标段id
         * @param {Number} pid - 父节点id
         * @param {Number} order - order取值
         * @param {String} orderOperate - order比较操作符
         * @return {Promise<void>}
         */
        async addUpChildren(tenderId, pid, order, orderOperate) {
            this.initSqlBuilder();
            const sql = ['SELECT SUM(??) As value FROM ?? ', ' WHERE '];
            const sqlParam = ['total_price', this.tableName];
            sql.push(' ?? = ' + tenderId);
            sqlParam.push('tender_id');
            sql.push(' And ?? = ' + pid);
            sqlParam.push('ledger_pid');
            sql.push(' And ?? ' + orderOperate + ' ' + order);
            sqlParam.push('order');
            const result = await this.db.queryOne(sql.join(''), sqlParam);

            return result.value;
        }

        /**
         * 更新order
         * @param {Number} tenderId - 标段id
         * @param {Number} parentId - 父节点id
         * @param {Number} order - 自增起始order(含)
         * @param {Number} incre - 自增量
         * @return {Promise<*>}
         * @private
         */
        async _updateChildrenOrderAfter(tenderId, parentId, order, incre = 1) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('order', {
                value: order,
                operate: '>=',
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: parentId,
                operate: '=',
            });
            this.sqlBuilder.setUpdateData('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;
        }
        /**
         *  select的全部后兄弟节点，Order自增
         *
         * @param {Object} select - 选中的节点
         * @param {Number} incre - 自增值
         * @return {Array} - 自增后的数据
         * @private
         */
        async _updateSelectNextsOrder(select, incre = 1) {
            return await this._updateChildrenOrderAfter(select.tender_id, select.ledger_pid, select.order + 1, incre);
        }

        /**
         * 从数据库获取标段的最大节点id
         *
         * @param {Number} tenderId - 标段id
         * @return {Number}
         * @private
         */
        async _getMaxNodeId(tenderId) {
            const sql = 'SELECT Max(??) As max_id FROM ?? Where tender_id = ' + tenderId;
            const sqlParam = ['ledger_id', this.tableName];
            const queryResult = await this.db.queryOne(sql, sqlParam);
            return queryResult.max_id;
        }

        /**
         * 根据selectData, data 新增数据(新增为selectData的后项，该方法不可单独使用)
         *
         * @param {Number} tenderId - 标段id
         * @param {Object} selectData - 选中节点的数据
         * @param {Object} data - 新增节点的初始数据
         * @return {Object} - 新增结果
         * @private
         */
        async _addNodeData(tenderId, selectData, data) {
            if (tenderId <= 0) {
                return undefined;
            }
            if (!data) {
                data = {};
            }
            const cacheKey = keyPre + tenderId;
            let maxId = parseInt(await this.cache.get(cacheKey));
            if (!maxId) {
                maxId = await this._getMaxNodeId(tenderId);
                this.cache.set(cacheKey, maxId, 'EX', this.ctx.app.config.cacheTime);
            }

            data.tender_id = tenderId;
            data.ledger_id = maxId + 1;
            data.ledger_pid = selectData.ledger_pid;
            data.level = selectData.level;
            data.order = selectData.order + 1;
            data.full_path = selectData.full_path.replace('.' + selectData.ledger_id, '.' + data.ledger_id);
            data.is_leaf = true;
            const result = await this.transaction.insert(this.tableName, data);

            this.cache.set(cacheKey, maxId + 1, 'EX', this.ctx.app.config.cacheTime);

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

            const cacheKey = keyPre + tenderId;
            let maxId = parseInt(await this.cache.get(cacheKey));
            if (!maxId) {
                maxId = await this._getMaxNodeId(tenderId);
            }
            this.cache.set(cacheKey, maxId + 1, 'EX', this.ctx.app.config.cacheTime);

            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;
            }
            const result = await this.transaction.insert(this.tableName, data);

            return [result, data];
        }

        /**
         * 根据parentData, data新增数据(自动排序)
         * @param tenderId
         * @param parentData
         * @param data
         * @return {Promise<void>}
         * @private
         */
        async _addChildAutoOrder(tenderId, parentData, data) {
            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 (self.ctx.helper.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;
            const children = await this.getChildrenByParentId(tenderId, pid);
            const preData = findPreData(children, data);
            const parent = null;
            if (!preData || children.indexOf(preData) < children.length - 1) {
                await this._updateChildrenOrderAfter(tenderId, pid, preData ? preData.order + 1 : 1);
            }
            data.order = preData ? preData.order + 1 : 1;
            const [addResult, node] = await this._addChildNodeData(tenderId, parentData, data);

            return [addResult, node];
        }

        /**
         * tenderId标段中, 在selectId后新增一个节点
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @param {Object} data - 新增节点初始化数据
         * @return {Array} 新增后的数据，其他被修改的数据
         */
        async addNode(tenderId, selectId, data) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '新增节点数据错误';
            }
            if (!this.transaction) {
                this.transaction = await this.db.beginTransaction();
            }

            try {
                // 选中节点的所有后兄弟节点，order+1
                await this._updateSelectNextsOrder(selectData);
                // 数据库创建新增节点数据
                const newNode = await this._addNodeData(tenderId, selectData, data);
                if (!newNode) { throw '新增节点数据错误'; }
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                this.transaction = null;
                throw err;
            }
            this.transaction = null;

            // 查询应返回的结果
            const createData = await this.getDataByParentAndOrder(selectData.tender_id, selectData.ledger_pid, [selectData.order + 1]);
            const updateData = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.order + 1);
            return { create: createData, update: updateData };
        }

        /**
         * 从标准数据中提取有效数据
         * @param {Object} stdData - 从标准库中查询所得
         * @returns {name, unit, source, code, b_code}
         * @private
         */
        _filterStdData(stdData) {
            const result = {
                name: stdData.name,
                unit: stdData.unit,
                source: stdData.source,
            };
            result.code = stdData.code ? stdData.code : '';
            result.b_code = stdData.b_code ? stdData.b_code : '';
            return result;
        }

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

        /**
         * 新增子节点，并排在所有子节点的末尾
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 父节点Id
         * @param {Object} data - 新增节点数据（编号名称等）
         * @returns {Promise<*>}
         */
        async addChild(tenderId, selectId, data) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '新增节点数据错误';
            }
            const children = await this.getChildrenByParentId(tenderId, selectId);

            const cacheKey = keyPre + tenderId;
            let maxId = parseInt(await this.cache.get(cacheKey));
            if (!maxId) {
                maxId = await this._getMaxNodeId(tenderId);
                this.cache.set(cacheKey, maxId, 'EX', this.ctx.app.config.cacheTime);
            }

            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;

            this.transaction = await this.db.beginTransaction();
            try {
                const result = await this.transaction.insert(this.tableName, data);
                if (children.length === 0) {
                    await this.transaction.update(this.tableName, {is_leaf: false},
                        { where: {tender_id: tenderId, ledger_id: selectData.ledger_id} });
                }
                await this.transaction.commit();
            } catch(err) {
                console.log(err);
                this.transaction.rollback();
                throw err;
            }

            this.cache.set(cacheKey, maxId + 1, 'EX', this.ctx.app.config.cacheTime);

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

        /**
         * 添加标准节点，将选择的标准节点，添加为子节点（排序为末尾）
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 添加目标节点Id
         * @param {Object} stdData - 标准节点数据
         * @returns {Promise<*>}
         */
        async addStdNodeAsChild(tenderId, selectId, stdData) {
            const newData = this._filterStdData(stdData);
            const result = await this.addChild(tenderId, selectId, newData);
            return result;
        }

        /**
         * 添加节点，并同步添加父节点
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @param {Object} stdData - 节点数据
         * @param {StandardLib} stdLib - 标准库
         * @return {Promise<void>}
         */
        async addStdNodeWithParent(tenderId, stdData, stdLib) {
            // 查询完整标准清单，并按层次排序
            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);
                    } else {
                        const parent = node;
                        node = await this.getDataByCondition({
                            tender_id: tenderId,
                            ledger_pid: parent ? parent.ledger_id : rootId,
                            code: stdNode.code,
                            name: stdNode.name,
                        });
                        if (!node) {
                            isNew = true;
                            const newData = this._filterStdData(stdNode);
                            newData.is_leaf = (i === len - 1);
                            [addResult, node] = await this._addChildAutoOrder(tenderId, parent, newData);
                            if (parent && parent.is_leaf) {
                                await this.transaction.update(this.tableName, { id: parent.id, is_leaf: false });
                                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(tenderId, firstNew.full_path + '%');
                updateData = await this.getNextsData(tenderId, firstNew.ledger_pid, firstNew.order);
                if (updateParent) {
                    updateData.push(await this.getDataByCondition({ id: updateParent.id }));
                }
            }
            //const expandData = await this.getChildrenByParentId(tenderId, expandIds);
            return { create: createData, update: updateData };
        }

        /**
         * 删除节点
         * @param {Number} tenderId - 标段id
         * @param {Object} deleteData - 删除节点数据
         * @return {Promise<*>}
         * @private
         */
        async _deleteNodeData(tenderId, deleteData) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('full_path', {
                value: this.db.escape(deleteData.full_path + '%'),
                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(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '删除节点数据错误';
            }
            const parentData = await this.getDataByNodeId(tenderId, selectData.ledger_pid);

            this.transaction = await this.db.beginTransaction();
            let deleteData = [];
            try {
                // 获取将要被删除的数据
                deleteData = await this.getDataByFullPath(tenderId, selectData.full_path + '%');
                // 删除
                const operate = await this._deleteNodeData(tenderId, selectData);
                // 选中节点--父节点 只有一个子节点时，应升级is_leaf
                if (parentData) {
                    const count = await this.db.count(this.tableName, { tender_id: tenderId, ledger_pid: selectData.ledger_pid });
                    if (count === 1) {
                        await this.transaction.update(this.tableName, { id: parentData.id, is_leaf: true });
                    }
                }
                // 选中节点--全部后节点 order--
                await this._updateSelectNextsOrder(selectData, -1);
                // 删除部位明细
                await this.transaction.delete(this.ctx.service.pos.tableName, { tid: tenderId, lid: this._.map(deleteData, 'id') });
                await this.ctx.service.pos.deletePosData(this.transaction, tenderId, this._.map(deleteData, 'id'));
                await this.transaction.commit();
            } catch (err) {
                deleteData = [];
                await this.transaction.rollback();
                throw err;
            }
            // 查询结果
            let updateData = [];
            if (deleteData.length > 0) {
                updateData = await this.getNextsData(tenderId, selectData.ledger_pid, selectData.order - 1);
                updateData = updateData ? updateData : [];
                if (selectData.ledger_pid !== rootId) {
                    const updateData1 = await this.getDataByNodeId(tenderId, selectData.ledger_pid);
                    if (updateData1.is_leaf !== parentData.is_leaf) {
                        updateData.push(updateData1);
                    }
                }
            }
            return { delete: deleteData, update: updateData };
        }

        /**
         * tenderId标段中, 选中节点selectId上移
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async upMoveNode(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '上移节点数据错误';
            }
            const preData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, selectData.order - 1);
            if (!preData) {
                throw '节点不可上移';
            }

            this.transaction = await this.db.beginTransaction();
            try {
                const sData = await this.transaction.update(this.tableName, { id: selectData.id, order: selectData.order - 1 });
                const pData = await this.transaction.update(this.tableName, { id: preData.id, order: preData.order + 1 });
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            const resultData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, [selectData.order, preData.order]);
            return { update: resultData };
        }

        /**
         * tenderId标段中, 选中节点selectId下移
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async downMoveNode(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '下移节点数据错误';
            }
            const nextData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, selectData.order + 1);
            if (!nextData) {
                throw '节点不可下移';
            }

            this.transaction = await this.db.beginTransaction();
            try {
                const sData = await this.transaction.update(this.tableName, { id: selectData.id, order: selectData.order + 1 });
                const pData = await this.transaction.update(this.tableName, { id: nextData.id, order: nextData.order - 1 });
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            const resultData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, [selectData.order, nextData.order]);
            return { update: resultData };
        }

        /**
         * 升级selectData, 同步修改所有子节点
         * @param {Object} selectData - 升级操作，选中节点
         * @return {Object}
         * @private
         */
        async _syncUplevelChildren(selectData) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: selectData.tender_id,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('full_path', {
                value: this.db.escape(selectData.full_path + '.%'),
                operate: 'like',
            });
            this.sqlBuilder.setUpdateData('level', {
                value: 1,
                selfOperate: '-',
            });
            this.sqlBuilder.setUpdateData('full_path', {
                value: ['`full_path`', this.db.escape(selectData.ledger_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(selectData) {
            // 查询selectData的lastChild
            const lastChildData = await this.getLastChildData(selectData.tender_id, selectData.ledger_id);
            const nextsData = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.order);
            if (nextsData && nextsData.length > 0) {
                // 修改nextsData pid, 排序
                this.initSqlBuilder();
                this.sqlBuilder.setUpdateData('ledger_pid', {
                    value: selectData.ledger_id,
                });
                const orderInc = lastChildData ? lastChildData.order - selectData.order : -selectData.order;
                this.sqlBuilder.setUpdateData('order', {
                    value: Math.abs(orderInc),
                    selfOperate: orderInc > 0 ? '+' : '-',
                });
                this.sqlBuilder.setAndWhere('tender_id', {
                    value: selectData.tender_id,
                    operate: '=',
                });
                this.sqlBuilder.setAndWhere('ledger_pid', {
                    value: selectData.ledger_pid,
                    operate: '=',
                });
                this.sqlBuilder.setAndWhere('order', {
                    value: selectData.order,
                    operate: '>',
                });
                const [sql1, sqlParam1] = this.sqlBuilder.build(this.tableName, 'update');
                await this.transaction.query(sql1, sqlParam1);

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

                // 修改nextsData及其子节点的full_path
                const oldSubStr = this.db.escape(selectData.ledger_pid + '.');
                const newSubStr = this.db.escape(selectData.ledger_id + '.');
                const sqlArr = [];
                sqlArr.push('Update ?? SET `full_path` = Replace(`full_path`,' + oldSubStr + ',' + newSubStr + ') Where');
                sqlArr.push('(`tender_id` = ' + selectData.tender_id + ')');
                sqlArr.push(' And (');
                for (const data of nextsData) {
                    sqlArr.push('`full_path` Like ' + this.db.escape(data.full_path + '%'));
                    if (nextsData.indexOf(data) < nextsData.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(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            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 newFullPath = selectData.full_path.replace(selectData.ledger_pid + '.', '');
            try {
                // 选中节点--父节点 选中节点为firstChild时，修改is_leaf
                if (selectData.order === 1) {
                    await this.transaction.update(this.tableName, {
                        id: parentData.id,
                        is_leaf: true,
                    });
                }
                // 选中节点--父节点--全部后兄弟节点 order+1
                await this._updateSelectNextsOrder(parentData);
                // 选中节点 修改pid, order, full_path
                const updateData = { id: selectData.id,
                    ledger_pid: parentData.ledger_pid,
                    order: parentData.order + 1,
                    level: selectData.level - 1,
                    full_path: newFullPath,
                };
                const nexts = await this.getNextsData(tenderId, parentData.ledger_id, selectData.order);
                if (nexts.length > 0) {
                    updateData.unit_price = null;
                    updateData.quantity = null;
                    updateData.total_price = null;
                    updateData.deal_qty = null;
                    updateData.deal_tp = null;
                }
                await this.transaction.update(this.tableName, updateData);
                // 选中节点--全部子节点（含孙） level-1, full_path变更
                await this._syncUplevelChildren(selectData);
                // 选中节点--全部后兄弟节点 收编为子节点 修改pid, order, full_path
                await this._syncUpLevelNexts(selectData);
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询修改的数据
            const resultData1 = await this.getDataByFullPath(tenderId, newFullPath + '%');
            const resultData2 = await this.getNextsData(tenderId, parentData.ledger_pid, parentData.order + 1);
            // 默认原Parent被刷新过，不核对total_price修改
            const preParent = await this.getDataByNodeId(tenderId, parentData.ledger_id);
            resultData2.push(preParent);
            return { update: resultData1.concat(resultData2) };
        }

        /**
         * 降级selectData, 同步修改所有子节点
         * @param {Object} selectData - 选中节点
         * @param {Object} preData - 选中节点的前一节点（降级后为父节点）
         * @return {Promise<*>}
         * @private
         */
        async _syncDownlevelChildren(selectData, preData) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: selectData.tender_id,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('full_path', {
                value: this.db.escape(selectData.full_path + '.%'),
                operate: 'like',
            });
            this.sqlBuilder.setUpdateData('level', {
                value: 1,
                selfOperate: '+',
            });
            this.sqlBuilder.setUpdateData('full_path', {
                value: ['`full_path`', this.db.escape('.' + selectData.ledger_id), this.db.escape('.' + preData.ledger_id + '.' + selectData.ledger_id)],
                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(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '降级节点数据错误';
            }
            const preData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, selectData.order - 1);
            if (!preData) {
                throw '节点不可降级';
            }
            const preLastChildData = await this.getLastChildData(tenderId, preData.ledger_id);

            this.transaction = await this.db.beginTransaction();
            const orgLastPath = selectData.level === 1 ? selectData.ledger_id : '.' + selectData.ledger_id;
            const newLastPath = selectData.level === 1 ? preData.ledger_id + '.' + selectData.ledger_id : '.' + preData.ledger_id + '.' + selectData.ledger_id;
            const newFullPath = selectData.full_path.replace(orgLastPath, newLastPath);
            try {
                // 选中节点--全部后节点 order--
                await this._updateSelectNextsOrder(selectData, -1);
                // 选中节点 修改pid, level, order, full_path
                const updateData = {
                    id: selectData.id,
                    ledger_pid: preData.ledger_id,
                    order: preLastChildData ? preLastChildData.order + 1 : 1,
                    level: selectData.level + 1,
                    full_path: newFullPath,
                };
                await this.transaction.update(this.tableName, updateData);
                // 选中节点--全部子节点（含孙） level++, full_path
                await this._syncDownlevelChildren(selectData, preData);
                // 选中节点--前兄弟节点 is_leaf应为false, 清空计算相关字段
                if (preData.is_leaf || preData.unit_price || preData.quantity || preData.total_price || preData.deal_qty || preData.deal_tp) {
                    const updateData2 = {
                        id: preData.id,
                        is_leaf: false,
                        unit_price: null,
                        quantity: null,
                        total_price: null,
                        deal_qty: null,
                        deal_tp: null,
                    };
                    await this.transaction.update(this.tableName, updateData2);
                }
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询修改的数据
            // 选中节点及子节点
            const resultData1 = await this.getDataByFullPath(tenderId, newFullPath + '%');
            // 选中节点--原前兄弟节点&全部后兄弟节点
            const queryOrder = (preData.is_leaf || this.ctx.helper.checkZero(selectData.total_price)) ? preData.order - 1 : preData.order;
            const resultData2 = await this.getNextsData(tenderId, preData.ledger_pid, queryOrder);
            return { update: resultData1.concat(resultData2) };
        }

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

        /**
         * newData中，以orgData为基准，过滤掉orgData中未定义或值相等的部分
         * @param {Object} orgData
         * @param {Object} newData
         * @private
         */
        _filterChangedField(orgData, newData) {
            const result = {};
            let bChanged = false;
            for (const prop in orgData) {
                if (this._.isEmpty(newData[prop]) && newData[prop] !== orgData[prop]) {
                    result[prop] = newData[prop];
                    bChanged = true;
                }
            }
            return bChanged ? result : undefined;
        }

        _checkField(data, field) {
            const fields = field instanceof Array ? field : [field];
            for (const prop in data) {
                if (fields.indexOf(prop) >= 0) {
                    return true;
                }
            }
            return false;
        }

        /**
         * 检查data中是否含有计算字段
         * @param {Object} data
         * @return {boolean}
         * @private
         */
        _checkCalcField(data) {
            for (const prop in data) {
                if (calcFields.indexOf(prop) >= 0) {
                    return true;
                }
            }
            return false;
        }

        /**
         * 提交数据 - 不影响计算等未提交项
         * @param {Number} tenderId - 标段id
         * @param {Object} data - 提交数据
         * @return {Object} - 提交后的数据
         */
        async updateInfo(tenderId, data) {
            // 简单校验数据
            if (tenderId <= 0) {
                throw '标段不存在';
            }
            if (tenderId !== data.tender_id) {
                throw '提交数据错误';
            }

            try {
                // 过滤不可提交字段
                const updateNode = await this.getDataById(data.id);
                if (!updateNode || tenderId !== updateNode.tender_id || data.ledger_id !== updateNode.ledger_id) {
                    throw '提交数据错误';
                }
                const updateData = this._filterUpdateInvalidField(updateNode.id, data);
                await this.db.update(this.tableName, updateData);
            } catch (err) {
                throw err;
            }

            const result = await this.getDataByNodeId(tenderId, data.ledger_id);
            return result;
        }

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

            this.transaction = await this.db.beginTransaction();
            try {
                for (const data of datas) {
                    const updateNode = await this.getDataById(data.id);
                    if (!updateNode || tenderId !== updateNode.tender_id || data.ledger_id !== updateNode.ledger_id) {
                        throw '提交数据错误';
                    }
                    const updateData = this._filterUpdateInvalidField(updateNode.id, data);
                    await this.transaction.update(this.tableName, updateData);
                }
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            const filter = [];
            for (const data of datas) {
                filter.push(data.id);
            }
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('id', {
                value: filter,
                operate: 'in',
            });
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const resultData = await this.db.query(sql, sqlParam);
            return resultData;
        }

        /**
         * 复制粘贴整块
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 选中几点Id
         * @param {Array} block - 复制节点Id
         * @return {Object} - 提价后的数据(其中新增粘贴数据，只返回第一层)
         */
        async pasteBlock(tenderId, selectId, block) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '位置数据错误';
            }
            const newParentPath = selectData.full_path.replace(selectData.ledger_id, '');

            const copyNodes = await this.getDataByNodeIds(tenderId, block);
            if (!copyNodes || copyNodes.length <= 0) {
                throw '复制数据错误';
            }
            let bSameParent = true;
            for (const node of copyNodes) {
                if (node.ledger_pid !== copyNodes[0].ledger_pid) {
                    bSameParent = false;
                    break;
                }
            }
            if (!bSameParent) {
                throw '复制数据错误：仅可操作同层节点';
            }
            const orgParentPath = copyNodes[0].full_path.replace(copyNodes[0].ledger_id, '');

            const newIds = [];
            this.transaction = await this.db.beginTransaction();
            try {
                // 选中节点的所有后兄弟节点，order+粘贴节点个数
                await this._updateSelectNextsOrder(selectData, copyNodes.length);
                // 数据库创建新增节点数据
                for (const node of copyNodes) {
                    const datas = await this.getDataByFullPath(tenderId, node.full_path + '%');

                    const cacheKey = keyPre + tenderId;
                    let maxId = parseInt(await this.cache.get(cacheKey));
                    if (!maxId) {
                        maxId = await this._getMaxNodeId(tenderId);
                    }
                    this.cache.set(cacheKey, maxId + datas.length, 'EX', this.ctx.app.config.cacheTime);

                    const leafBillsId = [];
                    // 计算粘贴数据中需更新部分
                    for (let index = 0; index < datas.length; index++) {
                        const data = datas[index];
                        const newId = maxId + index + 1;
                        const idChange = {
                            org: data.id,
                        };
                        delete data.id;
                        if (!data.is_leaf) {
                            for (const children of datas) {
                                children.full_path = children.full_path.replace('.' + data.ledger_id, '.' + newId);
                                if (children.ledger_pid === data.ledger_id) {
                                    children.ledger_pid = newId;
                                }
                            }
                        } else {
                            data.full_path = data.full_path.replace('.' + data.ledger_id, '.' + newId);
                        }
                        data.ledger_id = newId;
                        data.full_path = data.full_path.replace(orgParentPath, newParentPath);
                        if (data.ledger_pid === copyNodes[0].ledger_pid) {
                            data.ledger_pid = selectData.ledger_pid;
                            data.order = selectData.order + index + 1;
                        }
                        data.level = data.level + selectData.level - copyNodes[0].level;
                        const newData = await this.transaction.insert(this.tableName, data);
                        if (data.is_leaf) {
                            idChange.new = newData.insertId;
                            leafBillsId.push(idChange);
                        }
                        newIds.push(newData.insertId);
                    }
                    for (const id of leafBillsId) {
                        await this.ctx.service.pos.copyBillsPosData(id.org, id.new, this.transaction);
                    }
                }
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询应返回的结果
            const order = [];
            for (let i = 1; i <= copyNodes.length; i++) {
                order.push(selectData.order + i);
            }
            const createData = await this.getDataByIds(newIds);
            const updateData = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.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 {Object} updateMap - 增量更新数，使用更新父项的full_path为索引
         *        e.g: {'1.2.6.8': 30, '1.2.6.10': 40}表示'1.2.6.8'增量30，'1.2.6.10'增量40(此处同步更新'1.2.6', '1.2', '1')
         * @return {Promise<void>}
         * @private
         */
        async _increCalcParent(tenderId, updateMap) {
            for (const prop in updateMap) {
                this.initSqlBuilder();

                this.sqlBuilder.setAndWhere('tender_id', {
                    value: tenderId,
                    operate: '=',
                });
                const fullPath = this.ctx.helper.explodePath(prop);
                this.sqlBuilder.setAndWhere('full_path', {
                    value: this.ctx.helper.explodePath(prop),
                    operate: 'in',
                });
                this.sqlBuilder.setUpdateData('total_price', {
                    value: updateMap[prop] > 0 ? updateMap[prop] : -updateMap[prop],
                    selfOperate: updateMap[prop] > 0 ? '+' : '-',
                });

                const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
                await this.transaction.query(sql, sqlParam);
            }
        }

        /**
         * 提交数据 - 响应计算(增量方式计算)
         * @param {Number} tenderId
         * @param {Object} data
         * @return {Promise<*>}
         */
        async updateCalc(tenderId, data) {
            // 简单验证数据
            if (tenderId <= 0) {
                throw '标段不存在';
            }
            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 {
                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) {
                        if (!row.quantity) { row.quantity = updateNode.quantity; }
                        if (!row.deal_qty) { row.deal_qty = updateNode.deal_qty; }
                    }
                    if (row.b_code) {
                        row.dgn_qty1 = null;
                        row.dgn_qty2 = null;
                    }
                    if (this._checkCalcField(row)) {
                        let calcData = JSON.parse(JSON.stringify(row));
                        const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, row.unit ? row.unit : updateNode.unit);
                        // 数量保留小数位数
                        this.ctx.helper.checkFieldPrecision(calcData, qtyFields, precision.value);
                        // 单位保留小数位数
                        this.ctx.helper.checkFieldPrecision(calcData, upFields, this.ctx.tender.info.decimal.up);
                        // 计算
                        if (row.quantity !== undefined) {
                            if (row.unit_price  !== undefined) {
                                calcData.total_price = this.ctx.helper.times(calcData.quantity, calcData.unit_price);
                            } else {
                                calcData.total_price = this.ctx.helper.times(calcData.quantity, updateNode.unit_price);
                            }
                        } else if (row.unit_price !== undefined) {
                            calcData.total_price = this.ctx.helper.times(updateNode.quantity, calcData.unit_price);
                        }
                        if (row.total_price !== undefined) {
                            calcData.quantity = null;
                        }
                        if (row.deal_qty !== undefined) {
                            if (row.unit_price !== undefined) {
                                calcData.deal_tp = this.ctx.helper.times(calcData.deal_qty, calcData.unit_price);
                            } else {
                                calcData.deal_tp = this.ctx.helper.times(calcData.deal_qty, updateNode.unit_price);
                            }
                        } else if (row.unit_price !== undefined) {
                            calcData.deal_tp = this.ctx.helper.times(updateNode.deal_qty, calcData.unit_price);
                        }
                        if (row.deal_tp !== undefined) {
                            calcData.deal_qty = null;
                        }
                        updateData = this._filterUpdateInvalidField(updateNode.id, calcData);
                    } else {

                        updateData = this._filterUpdateInvalidField(updateNode.id, row);
                    }
                    await this.transaction.update(this.tableName, updateData);
                }
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

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

        /**
         *
         * @param tenderId
         * @param xmj
         * @param order
         * @param parentData
         * @return {Promise<*[]>}
         * @private
         */
        async _sortBatchInsertData(tenderId, xmj, order, parentData) {
            const result = [],
                newIds = [];
            let tp = 0;
            const cacheKey = keyPre + tenderId;
            let maxId = parseInt(await this.cache.get(cacheKey));
            if (!maxId) {
                maxId = await this._getMaxNodeId(tenderId);
            }
            this.cache.set(cacheKey, maxId + xmj.children.length + 1, 'EX', this.ctx.app.config.cacheTime);
            // 添加xmj数据
            const parent = {
                tender_id: tenderId,
                ledger_id: maxId + 1,
                ledger_pid: parentData.ledger_id,
                is_leaf: xmj.children.length === 0,
                order,
                level: parentData.level + 1,
                name: xmj.name,
            };
            parent.full_path = parentData.full_path + '.' + parent.ledger_id;
            // 添加gcl数据
            for (let i = 0, iLen = xmj.children.length; i < iLen; i++) {
                const gcl = xmj.children[i];
                const child = {
                    tender_id: tenderId,
                    ledger_id: maxId + 1 + i + 1,
                    ledger_pid: parent.ledger_id,
                    is_leaf: true,
                    order: i + 1,
                    level: parent.level + 1,
                    b_code: gcl.b_code,
                    name: gcl.name,
                    unit: gcl.unit,
                    unit_price: gcl.unit_price,
                    quantity: gcl.quantity,
                };
                child.full_path = parent.full_path + '.' + child.ledger_id;
                child.total_price = child.unit_price * child.quantity;
                tp = tp + child.total_price;
                result.push(child);
                newIds.push(child.ledger_id);
            }
            parent.total_price = tp;
            result.push(parent);
            newIds.push(parent.ledger_id);
            return [result, tp, newIds];
        }

        /**
         * 批量插入子项
         * @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,
                    });
                }
                const order = lastChild ? lastChild.order : 0;
                // 计算id
                const cacheKey = keyPre + tenderId;
                let maxId = parseInt(await this.cache.get(cacheKey));
                if (!maxId) {
                    maxId = await this._getMaxNodeId(tenderId);
                }
                // 数据库创建新增节点数据
                for (let i = 0, iLen = data.length; i < iLen; i++) {
                    // 合并新增数据
                    const qd = {
                        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 precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, qd.unit);
                    this.ctx.helper.checkFieldPrecision(qd, qtyFields, precision.value);
                    const insertResult = await this.transaction.insert(this.tableName, qd);
                    qd.id = insertResult.insertId;
                    newIds.push(insertResult.insertId);
                    if (data[i].pos.length > 0) {
                        await this.ctx.service.pos.insertLedgerPosData(this.transaction, tenderId, qd, data[i].pos);
                        await this.calc(tenderId, insertResult.insertId, this.transaction);
                    }
                }
                this.cache.set(cacheKey, maxId + data.length + 1, 'EX', this.ctx.app.config.cacheTime);
                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({ 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._updateSelectNextsOrder(selectData, data.length);
                // 计算id和order
                const cacheKey = keyPre + tenderId;
                let maxId = parseInt(await this.cache.get(cacheKey));
                if (!maxId) {
                    maxId = await this._getMaxNodeId(tenderId);
                }
                const order = selectData.order;
                // 数据库创建新增节点数据
                for (let i = 0, iLen = data.length; i < iLen; i++) {
                    // 合并新增数据
                    const qd = {
                        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 precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, qd.unit);
                    this.ctx.helper.checkFieldPrecision(qd, qtyFields, precision.value);
                    const insertResult = await this.transaction.insert(this.tableName, qd);
                    qd.id = insertResult.insertId;
                    newIds.push(insertResult.insertId);
                    if (data[i].pos.length > 0) {
                        await this.ctx.service.pos.insertLedgerPosData(this.transaction, tenderId, qd, data[i].pos);
                        await this.calc(tenderId, insertResult.insertId, this.transaction);
                    }
                }
                this.cache.set(cacheKey, maxId + data.length + 1, 'EX', this.ctx.app.config.cacheTime);
                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({ lid: newIds });
            return result;
        }

        /**
         *
         * @param {Number} tid - 标段id
         * @param {Number} id - 需要计算的节点的id
         * @param {Object} transaction - 操作所属事务，没有则创建
         * @return {Promise<void>}
         */
        async calc(tid, id, transaction) {
            const node = await this.getAllDataByCondition({ id });
            if (!node) {
                throw '数据错误';
            }

            const calcQtySql = 'UPDATE ??' +
                ' SET `quantity` = (SELECT SUM(`quantity`) FROM ?? WHERE `lid` = ?), `total_price` = `quantity` * `unit_price`' +
                ' WHERE `id` = ?';
            await transaction.query(this.db.format(calcQtySql, [this.tableName, this.ctx.service.pos.tableName, id, id]));
        }

        /**
         * 查找定位 --> 废弃
         * @param tenderId
         * @param nodeId
         * @return {Promise<{expand: *}>}
         */
        async locateNode(tenderId, nodeId) {
            const node = await this.getDataByNodeId(tenderId, nodeId);
            if (!node) {
                throw '查询数据有误';
            }
            const expandIds = node.full_path.split('.');
            expandIds.pop();
            const expandData = await this.getChildrenByParentId(tenderId, expandIds);
            return { expand: expandData };
        }

        async _importCacheTreeNode(transaction, node) {
            const data = {
                tender_id: this.ctx.tender.id,
                ledger_id: node.ledger_id,
                ledger_pid: node.ledger_pid,
                level: node.level,
                order: node.order,
                is_leaf: !node.children || node.children.length === 0,
                full_path: node.full_path,
                code: node.code,
                b_code: node.b_code,
                name: node.name,
                unit: node.unit,
                quantity: node.quantity,
                unit_price: node.unit_price,
                total_price: node.total_price,
                dgn_qty1: node.dgn_qty1,
                dgn_qty2: node.dgn_qty2,
                memo: node.memo,
                drawing_code: node.drawing_code,
            };
            const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, data.unit);
            this.ctx.helper.checkFieldPrecision(data, qtyFields, precision.value);
            const result = await transaction.insert(this.tableName, data);
            data.id = result.insertId;
            if (node.children && node.children.length > 0) {
                for (const child of node.children) {
                    await this._importCacheTreeNode(transaction, child);
                }
            } else if (node.pos && node.pos.length > 0) {
                await this.ctx.service.pos.insertLedgerPosData(transaction, this.ctx.tender.id, data, node.pos);
            }
        }

        /**
         * 导入Excel数据
         * @param excelData
         * @returns {Promise<void>}
         */
        async importExcel(excelData) {
            const AnalysisExcel = require('../lib/analysis_excel');
            const analysisExcel = new AnalysisExcel();
            const tempData = await this.ctx.service.tenderNodeTemplate.getData(true);
            const cacheTree = analysisExcel.analysisData(excelData, tempData);
            const cacheKey = keyPre + this.ctx.tender.id;
            const orgMaxId = parseInt(await this.cache.get(cacheKey));
            const transaction = await this.db.beginTransaction();
            try {
                await transaction.delete(this.tableName, {tender_id: this.ctx.tender.id});
                await transaction.delete(this.ctx.service.pos.tableName, {tid: this.ctx.tender.id});
                for (const node of cacheTree.roots) {
                    await this._importCacheTreeNode(transaction, node);
                }
                await transaction.commit();
                this.cache.set(cacheKey, cacheTree.items.length + 1, 'EX', this.ctx.app.config.cacheTime);
            } catch (err) {
                await transaction.rollback();
                if (orgMaxId) {
                    this.cache.set(cacheKey, orgMaxId, 'EX', this.ctx.app.config.cacheTime);
                }
                throw err;
            }
        }
    }

    return Ledger;
};