Calculation/app/base/base_bills_service.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date
 * @version
 */

const TreeService = require('./base_tree_service');
// sql拼装器
const rootId = -1;
const calcFields = ['unit_price', 'sgfh_qty', 'sgfh_tp', 'sjcl_qty', 'sjcl_tp', 'qtcl_qty', 'qtcl_tp', 'deal_qty', 'deal_tp', 'dgn_qty1', 'dgn_qty2', 'ex_qty1', 'ex_tp1'];
const readOnlyFields = ['id', 'tender_id', 'ledger_id', 'ledger_pid', 'order', 'level', 'full_path', 'is_leaf'];
const upFields = ['unit_price'];
const qtyFields = ['sgfh_qty', 'sjcl_qty', 'qtcl_qty', 'quantity', 'deal_qty', 'ex_qty1'];
const tpFields = ['sgfh_tp', 'sjcl_tp', 'qtcl_tp', 'total_price', 'deal_tp', 'ex_tp1'];
const measureType = require('../const/tender').measureType;
const billsUtils = require('../lib/bills_utils');

class BaseBillsSerivce extends TreeService {

    constructor (ctx, setting, relaPosName, relaAncGclName, relaAncGclDetailName, relaExtraName, relaPosDetailName) {
        super(ctx, setting);
        this.relaPosService = relaPosName;
        this.relaAncGclService = relaAncGclName;
        this.relaAncGclDetailService = relaAncGclDetailName;
        this.relaExtraService = relaExtraName;
        this.relaPosDetailService = relaPosDetailName;
    }

    async getFinalData(mid, columns) {
        return await this.db.select(this.departTableName(mid), {
            where: this.getCondition({mid: mid}),
            columns,
        });
    }

    set relaPosService(posName) {
        this._posName = posName;
    }
    get relaPosService() {
        return this.ctx.service[this._posName];
    }

    set relaAncGclService(ancGclName) {
        this._ancGclName = ancGclName;
    }
    get relaAncGclService() {
        return this._ancGclName ? this.ctx.service[this._ancGclName] : undefined;
    }

    set relaAncGclDetailService(ancGclDetailName) {
        this._ancGclDetailName = ancGclDetailName;
    }
    get relaAncGclDetailService() {
        return this._ancGclDetailName ? this.ctx.service[this._ancGclDetailName] : undefined;
    }

    set relaExtraService(extraName) {
        this._extraName = extraName
    }
    get relaExtraService() {
        return this._extraName ? this.ctx.service[this._extraName] : undefined;
    }

    set relaPosDetailService(posDetailName) {
        this._posDetailName = posDetailName
    }
    get relaPosDetailService() {
        return this._posDetailName ? this.ctx.service[this._posDetailName] : undefined;
    }

    // 继承方法
    clearParentingData(data) {
        data.unit_price = 0;
        data.sgfh_qty = 0;
        data.sgfh_tp = 0;
        data.sjcl_qty = 0;
        data.sjcl_tp = 0;
        data.qtcl_qty = 0;
        data.qtcl_tp = 0;
        data.quantity = 0;
        data.total_price = 0;
        data.deal_qty = 0;
        data.deal_tp = 0;
        data.ex_qty1 = 0;
        data.ex_tp1 = 0;
    }

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

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

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

        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.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) {
                await this.transaction.update(this.tableName,
                    {
                        is_leaf: false,
                        sgfh_qty: 0, sgfh_tp: 0, qtcl_qty: 0, qtcl_tp: 0, sjcl_qty: 0, sjcl_tp: 0,
                        quantity: 0, unit_price: 0, total_price: 0, deal_qty: 0, deal_tp: 0, ex_qty1: 0, ex_tp1: 0,
                    },
                    { where: {tender_id: tenderId, ledger_id: selectData.ledger_id} });
            }
            await this.transaction.commit();
        } catch(err) {
            await 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;
    }

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

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

    /**
     * 根据parentData, data新增数据(新增为parentData的最后一个子项)
     * @param {Number} tenderId - 标段id
     * @param {Object} parentData - 父项数据
     * @param {Object} data - 新增节点，初始数据
     * @return {Promise<*>} - 新增结果
     * @private
     */
    async _addChildNodeData(tenderId, parentData, data, reviseId) {
        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 (reviseId) data.crid = reviseId;
        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, orderField, reviseId) {
        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][orderField], a[orderField]) > 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);
        if (!preData || children.indexOf(preData) < children.length - 1) {
            await this._updateChildrenOrder(tenderId, pid, preData ? preData.order + 1 : 1);
        }
        data.order = preData ? preData.order + 1 : 1;
        const [addResult, node] = await this._addChildNodeData(tenderId, parentData, data, reviseId);

        return [addResult, node];
    }

    /**
     * 添加节点，并同步添加父节点
     * @param {Number} tenderId - 标段id
     * @param {Number} selectId - 选中节点id
     * @param {Object} stdData - 节点数据
     * @param {StandardLib} stdLib - 标准库
     * @return {Promise<void>}
     */
    async addStdNodeWithParent(tenderId, stdData, reviseId) {
        let node, firstNew, updateParent, addResult;
        const expandIds = [];
        this.transaction = await this.db.beginTransaction();
        try {
            // 从最顶层节点依次查询是否存在，否则添加
            for (let i = 0, len = stdData.length; i < len; i++) {
                const stdNode = stdData[i];
                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) {
                    expandIds.push(node.ledger_id);
                    continue;
                }

                if (firstNew) {
                    stdNode.is_leaf = (i === len - 1);
                    [addResult, node] = await this._addChildNodeData(tenderId, parent, stdNode, reviseId);
                } else {
                    stdNode.is_leaf = (i === len - 1);
                    [addResult, node] = await this._addChildAutoOrder(tenderId, parent, stdNode, 'code', reviseId);
                    if (parent && parent.is_leaf) {
                        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;
                }
            }
            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 }));
            }
        }
        return { create: createData, update: updateData };
    }

    async getLeafXmj(select) {
        const relaId = select.full_path.split('-');
        const parents = await this.getAllDataByCondition({ where: { tender_id: select.tender_id, ledger_id: relaId }, orders: [['level', 'asc']]});
        const xmjs = parents.filter(x => { return !!x.code; });
        return xmjs[xmjs.length - 1];
    }

    async addGclStdNode(tenderId, selectId, stdData, reviseId) {
        const selectNode = await this.getDataByKid(tenderId, selectId);
        if (selectNode.b_code) {
            return await this.addStdNode(tenderId, selectId, stdData, reviseId);
        } else {
            return await this.addStdNodeAsChild(tenderId, selectId, stdData, reviseId);
        }
    }

    async addGclStdNodeWithParent(tenderId, selectId, stdData, reviseId) {
        const selectData = await this.getDataByKid(tenderId, selectId);
        if (!selectData) throw '新增节点数据错误，请刷新后重试';

        let leafXmj = selectData.b_code ? await this.getLeafXmj(selectData) : selectData;
        if (!leafXmj) throw '找不到可插入清单的项目节，请刷新后重试';

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

                const parent = node;
                node = await this.getDataByCondition({
                    tender_id: tenderId, ledger_pid: parent.ledger_id,
                    code: stdNode.code, name: stdNode.name,
                });
                if (node) continue;

                stdNode.is_leaf = (i === len - 1);
                if (firstNew) {
                    [addResult, node] = await this._addChildNodeData(selectData.tender_id, parent, stdNode, reviseId);
                } else {
                    [addResult, node] = await this._addChildAutoOrder(selectData.tender_id, parent, stdNode, 'b_code', reviseId);
                    if (parent && parent.is_leaf) {
                        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;
                }
            }
            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 }));
            }
        }
        return { create: createData, update: updateData };
    }

    /**
     * 过滤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;
    }

    /**
     * 检查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
     * @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 {
            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.ex_qty1 === undefined) { row.ex_qty1 = updateNode.ex_qty1; }
                }

                // 项目节、工程量清单相关
                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)) {
                    let 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.ex_qty1 !== 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;
                        if (row.ex_qty1 === undefined) calcData.ex_qty1 = updateNode.ex_qty1;
                        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);
                        calcData.ex_tp1 = helper.mul(calcData.ex_qty1, calcData.unit_price, info.decimal.tp);
                    } else if (row.sgfh_tp !== undefined || row.sjcl_tp !== undefined || row.qtcl_tp !== undefined || row.deal_tp !== undefined || row.ex_tp1 !== undefined) {
                        calcData.sgfh_qty = 0;
                        calcData.sjcl_qty = 0;
                        calcData.qtcl_qty = 0;
                        calcData.quantity = 0;
                        calcData.deal_qty = 0;
                        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);
                        calcData.ex_tp1 = helper.round(row.ex_tp1 !== undefined ? calcData.row.ex_tp1 : updateNode.ex_tp1, 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);
                        calcData.ex_tp1 = helper.mul(calcData.ex_qty1, 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);
            }
            await this.transaction.commit();
            this.transaction = null;
        } catch (err) {
            await this.transaction.rollback();
            this.transaction = null;
            throw err;
        }

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

    // 统计方法
    async addUp(condition) {
        if (!condition.tender_id) throw new TypeError('statistical lacks necessary parameter');
        const sql = 'SELECT Sum(total_price) As total_price, Sum(deal_tp) As deal_tp' +
            '  FROM ' + this.departTableName(condition.tender_id) + this.ctx.helper.whereSql(condition);
        const result = await this.db.queryOne(sql);
        return result;
    }

    // 导入Excel
    async importGclExcel(id, sheet, data) {
        const node = await this.getDataById(id);
        if (!node || !node.is_leaf || node.tender_id !== this.ctx.tender.id) throw '数据错误';

        const maxId = await this._getMaxLid(this.ctx.tender.id);

        const AnalysisExcel = require('../lib/analysis_excel').AnalysisGclExcelTree;
        const analysisExcel = new AnalysisExcel(this.ctx, this.setting);
        const cacheData = analysisExcel.analysisData(sheet, node, maxId, data);
        if (!cacheData) throw '导入数据错误，请检查Excel文件后重试';

        const datas = [];
        for (const node of cacheData.items) {
            const data = {
                id: node.id, 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,
                b_code: node.b_code,
                name: node.name,
                unit: node.unit,
                unit_price: node.unit_price,
                crid: node.crid,
            };
            if (this.ctx.tender.data.measure_type === measureType.tz.value) {
                data.sgfh_qty = node.quantity;
                data.sgfh_tp = node.total_price;
                data.quantity = node.quantity;
                data.total_price = node.total_price;
            } else if (this.ctx.tender.data.measure_type === measureType.gcl.value) {
                data.deal_qty = node.quantity;
                data.deal_tp = node.total_price;
            }
            datas.push(data);
        }
        const conn = await this.db.beginTransaction();
        try {
            await this.db.update(this.tableName, {id: node.id, is_leaf: false});
            await this.db.insert(this.tableName, datas);
            await conn.commit();
        } catch(err) {
            await conn.rollback();
            throw err;
        }
        this._cacheMaxLid(this.ctx.tender.id, cacheData.keyNodeId);

        node.is_leaf = false;
        return { create: datas, update: [node]};
    }

    async deal2sgfh(tid) {
        const sql = 'UPDATE ' + this.tableName + ' SET sgfh_qty = deal_qty,' +
            '    quantity = IFNULL(deal_qty, 0) + IFNULL(sjcl_qty, 0) + IFNULL(qtcl_qty, 0), ' +
            '    sgfh_tp = deal_tp,' +
            '    total_price = IFNULL(deal_tp, 0) + IFNULL(sjcl_tp, 0) + IFNULL(qtcl_tp, 0)' +
            '  WHERE tender_id = ?';
        const sqlParam = [tid];
        await this.db.query(sql, sqlParam);
    }

    async sgfh2deal(tid) {
        const sql = 'UPDATE ' + this.tableName + ' SET deal_qty = sgfh_qty, deal_tp = sgfh_tp WHERE tender_id = ?';
        const sqlParam = [tid];
        await this.db.query(sql, sqlParam);
    }

    _calcExpr(data, field, expr, defaultValue, precision) {
        if (expr) {
            try {
                data[field] = this.ctx.helper.round(this.ctx.helper.calcExpr(expr), precision.value);
            } catch (err) {
            }
        } else {
            data[field] = this.ctx.helper.round(defaultValue, precision.value);
        }
    }

    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 userId = this.ctx.session.sessionUser.accountId;
        const qtyDecimal = this.ctx.tender.info.decimal.qty;
        this.newBills = false;
        const selectData = await this.getDataByKid(tid, sid);
        if (!selectData) throw '粘贴数据错误';
        const newParentPath = selectData.full_path.replace(selectData.ledger_id, '');

        const pasteBillsData = [], pastePosData = [], pasteAncGclData = [], pasteAncGclDetailData = [], pasteBillsExtraData = [], pastePosDetailData = [], leafBillsId = [];
        const tpDecimal = this.ctx.tender.info.decimal.tp;
        let maxId = await this._getMaxLid(this.ctx.tender.id);
        const calcTemplate = await this.ctx.service.calcTmpl.getAllTemplateDetail(this.ctx.session.sessionProject.id, 'posCalc');
        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,
                    dgn_qty1: d.dgn_qty1,
                    dgn_qty2: d.dgn_qty2,
                    features: d.features || '',
                };
                let template;
                if (d.calc_template) {
                    template = calcTemplate.find(x => { return x.id === d.calc_template });
                    if (!template) template = calcTemplate.find(x => { return x.name === d.calc_template_str; });
                    if (template) pasteBillsExtraData.push({ id: newBills.id, tid, calc_template: template.id });
                }
                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,
                        };
                        if (template && pos.calcDetail) {
                            let sumQty = 0;
                            for (const cd of pos.calcDetail) {
                                const newDetail = {
                                    id: this.uuid.v4(), tid: tid, lid: newBills.id, pid: newPos.id,
                                    create_user_id: this.ctx.session.sessionUser.accountId,
                                    update_user_id: this.ctx.session.sessionUser.accountId,
                                    pcd_order: cd.pcd_order,
                                };
                                if (template.id === d.calc_template) {
                                    newDetail.str1 = cd.str1 || '';
                                    newDetail.str2 = cd.str2 || '';
                                    newDetail.str3 = cd.str3 || '';
                                    newDetail.str4 = cd.str4 || '';
                                    newDetail.num_a = cd.num_a || 0;
                                    newDetail.num_b = cd.num_b || 0;
                                    newDetail.num_c = cd.num_c || 0;
                                    newDetail.num_d = cd.num_d || 0;
                                    newDetail.num_e = cd.num_e || 0;
                                    newDetail.num_f = cd.num_f || 0;
                                    newDetail.num_g = cd.num_g || 0;
                                    newDetail.num_h = cd.num_h || 0;
                                    newDetail.num_i = cd.num_i || 0;
                                    newDetail.num_j = cd.num_j || 0;
                                    newDetail.num_k = cd.num_k || 0;
                                    newDetail.num_l = cd.num_l || 0;
                                    newDetail.num_m = cd.num_m || 0;
                                    newDetail.num_n = cd.num_n || 0;
                                    newDetail.num_o = cd.num_o || 0;
                                    newDetail.num_p = cd.num_p || 0;
                                    newDetail.num_q = cd.num_q || 0;
                                    newDetail.num_r = cd.num_r || 0;
                                    newDetail.num_s = cd.num_s || 0;
                                    newDetail.num_t = cd.num_t || 0;
                                    newDetail.num_u = cd.num_u || 0;
                                    newDetail.qty = cd.qty || 0;
                                    newDetail.expr = cd.expr || '';
                                    newDetail.spec = cd.spec || '';
                                } else {
                                    this.ctx.service.posCalcDetail._loadDataAndCalc(newDetail, cd, {}, template);
                                }
                                sumQty = this.ctx.helper.add(sumQty, newDetail.qty);
                                pastePosDetailData.push(newDetail);
                            }
                            this._calcExpr(newPos, 'sgfh_qty', '', sumQty, precision);
                        } else {
                            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));
                        newPos.ex_qty1 = this.ctx.helper.round(pos.ex_qty1, precision.value);
                        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);
                        newBills.ex_qty1 = this.ctx.helper.add(newBills.ex_qty1, newPos.ex_qty1);
                        if (defaultData) this.ctx.helper._.assignIn(newPos, defaultData);

                        for (const ag of pos.ancGcl) {
                            const nig = {
                                id: this.uuid.v4(), tid: this.ctx.tender.id,
                                add_user_id: userId, update_user_id: userId,
                                lid: newPos.lid, pid: newPos.id, g_order: ag.g_order,
                                is_aux: ag.is_aux || 0, name: ag.name || '', unit: ag.unit || '',
                                drawing_code: ag.drawing_code || '', memo: ag.memo || '',
                                quantity: ag.quantity || 0, expr: ag.expr || '', calc_template: ag.calc_template || ''
                            };
                            pasteAncGclData.push(nig);
                            if (!ag.calc_template) continue;

                            let sumQty = 0;
                            for (const cd of ag.calcDetail) {
                                const newDetail = {
                                    id: this.uuid.v4(), tid: this.ctx.tender.id, lid: newPos.lid, pid: newPos.id, ag_id: nig.id,
                                    create_user_id: userId, update_user_id: userId,
                                    agd_order: cd.agd_order,
                                    str1 : cd.str1 || '', str2 : cd.str1 || '', str3 : cd.str1 || '', str4 : cd.str1 || '',
                                    num_a : cd.num_a || 0, num_b : cd.num_b || 0, num_c : cd.num_c || 0, num_d : cd.num_d || 0,
                                    num_e : cd.num_e || 0, num_f : cd.num_f || 0, num_g : cd.num_g || 0, num_h : cd.num_h || 0,
                                    num_i : cd.num_i || 0, num_j : cd.num_j || 0, num_k : cd.num_k || 0, num_l : cd.num_l || 0,
                                    num_m : cd.num_m || 0, num_n : cd.num_n || 0, num_o : cd.num_o || 0, num_p : cd.num_p || 0,
                                    num_q : cd.num_q || 0, num_r : cd.num_r || 0, num_s : cd.num_s || 0, num_t : cd.num_t || 0,
                                    num_u : cd.num_u || 0,
                                    qty: cd.qty || 0, expr: cd.expr || 0, spec: cd.spec || ''
                                };
                                sumQty = this.ctx.helper.add(sumQty, newDetail.qty);
                                pasteAncGclDetailData.push(newDetail);
                            }
                            nig.quantity = this.ctx.helper.round(sumQty, qtyDecimal);
                        }

                        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.ex_qty1 = this.ctx.helper.round(d.ex_qty1, precision.value);
                }
                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.sjcl_qty, newBills.unit_price, tpDecimal);
                newBills.qtcl_tp = this.ctx.helper.mul(newBills.qtcl_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);
                newBills.ex_tp1 = this.ctx.helper.mul(newBills.ex_qty1, newBills.unit_price, tpDecimal);
                if (defaultData) this.ctx.helper._.assignIn(newBills, defaultData);
                // if (d.ancGcl && d.ancGcl.length > 0) {
                //     for (const gcl of d.ancGcl) {
                //         const newAncGcl = {
                //             id: this.uuid.v4(), tid: tid, lid: newBills.id,
                //             add_user_id: userId, update_user_id: userId,
                //             name: gcl.name, unit: gcl.unit, g_order: gcl.g_order, is_aux: gcl.is_aux,
                //             quantity: gcl.quantity, expr: gcl.expr,
                //             drawing_code: gcl.drawing_code, memo: gcl.memo,
                //         };
                //         if (defaultData) this.ctx.helper._.assignIn(newAncGcl, defaultData);
                //         pasteAncGclData.push(newAncGcl);
                //     }
                // }
                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(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.relaPosService.tableName, pastePosData);
            }
            if (pasteAncGclData.length > 0 && this.relaAncGclService) await this.transaction.insert(this.relaAncGclService.tableName, pasteAncGclData);
            if (pasteAncGclDetailData.length > 0 && this.relaAncGclDetailService) await this.transaction.insert(this.relaAncGclDetailService.tableName, pasteAncGclDetailData);
            if (pasteBillsExtraData.length > 0 && this.relaExtraService) await this.transaction.insert(this.relaExtraService.tableName, pasteBillsExtraData);
            if (pastePosDetailData.length > 0 && this.relaPosDetailService) await this.transaction.insert(this.relaPosDetailService.tableName, pastePosDetailData);
            await this.transaction.commit();
        } catch (err) {
            await this.transaction.rollback();
            throw err;
        }

        // 查询应返回的结果
        const updateData = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.order + pasteData.length);
        const ancGcl = this.relaAncGclService ? { add: pasteAncGclData } : undefined;
        const ancGclDetail = this.relaAncGclDetailSerivce ? { add: pasteAncGclDetailData } : undefined;
        const posCalcDetail = this.relaPosDetailService ? { add: pastePosDetailData } : undefined;
        if (pasteBillsExtraData.length > 0) {
            for (const be of pasteBillsExtraData) {
                const b = pasteBillsData.find(x => { return x.id === be.id; });
                b.calc_template = be.calc_template;
            }
        }
        return {
            ledger: { create: pasteBillsData, update: updateData },
            pos: pastePosData, ancGcl, ancGclDetail, posCalcDetail,
        };
    }

    async getCompleteDataById(id) {
        const ledgerBills = await this.getDataById(id);
        const ledgerExtra = await this.ctx.service.ledgerExtra.getDataById(id);
        ledgerBills.is_tp = ledgerExtra ? ledgerExtra.is_tp : 0;
        return ledgerBills
    }

    /**
     * 获取最大节点id
     *
     * @param {Number} mid - master id
     * @return {Number}
     * @private
     */
    async _getMaxLid(mid) {
        const cacheKey = this.setting.keyPre + mid;
        let maxId = this.setting.cacheKey ? parseInt(await this.cache.get(cacheKey)) : undefined;
        if (this.setting.cacheKey) {
            // 判断是否存在变更新增部位maxId，有则取两者最大值
            const changeReviseCacheKey = 'change_ledger_maxLid2:' + mid;
            const changeReviseMaxId = await this.cache.get(changeReviseCacheKey);
            if (changeReviseMaxId) {
                maxId = !maxId ? parseInt(changeReviseMaxId) : Math.max(maxId, parseInt(changeReviseMaxId));
            }
        }
        if (!maxId) {
            const sql = 'SELECT Max(??) As max_id FROM ?? Where ' + this.setting.mid + ' = ?';
            const sqlParam = [this.setting.kid, this.tableName, mid];
            const queryResult = await this.db.queryOne(sql, sqlParam);
            maxId = queryResult.max_id || 0;
            const sql1 = 'SELECT Max(??) As max_id FROM ?? Where ' + this.setting.mid + ' = ?';
            const sqlParam1 = [this.setting.kid, this.ctx.service.changeLedger.tableName, mid];
            const queryResult1 = await this.db.queryOne(sql1, sqlParam1);
            const maxId1 = queryResult1.max_id || 0;
            maxId = Math.max(maxId, maxId1);
            if (this.setting.cacheKey) this.cache.set(cacheKey, maxId, 'EX', this.ctx.app.config.cacheTime);
        }
        return maxId;
    }
}

module.exports = BaseBillsSerivce;