Calculation/app/service/calc_tmpl.js

'use strict';

/**
 * 计算模板
 *
 * 如需修改请联系作者，常量&私有方法(以下划线开头)勿动
 *
 * @author Mai
 * @date
 * @version
 */

const math = require('mathjs');
math.config({
    number: 'BigNumber',
});
math.import({
    if: function(con, a, b) { return con ? a : b; },
});
const ValidTemplateType = ['posCalc', 'cost'];
const PosCalc = (function(){
    const EmptySpreadCache = {
        cols: [],
        emptyRows: 6,
        headRows: 1,
        headRowHeight: [32],
        defaultRowHeight: 21,
        headerFont: '12px 微软雅黑',
        font: '12px 微软雅黑',
        frozenLineColor: '#93b5e4',
    };
    const BaseSpreadColSetting = {
        str: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 0, width: 80, formatter: '@' },
        num: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 2, width: 80, type: 'Number' },
        spec: { title: '规格', colSpan: '1', rowSpan: '1', field: 'spec', hAlign: 1, width: 80, formatter: '@', cellType: 'spec', relaCol: '' },
        qty: {  title: '数量', colSpan: '1', rowSpan: '1', field: 'qty', hAlign: 2, width: 80, type: 'Number', readOnly: true },
    };
    const ValidColInfo = [
        { key: 'str', name: '文本', fields: ['str1', 'str2', 'str3', 'str4'], valid: ['title', 'width'], def: { title: '文字', width: 80, type: 'str'} },
        {
            key: 'num', name: '数值/计算',
            fields: ['num_a', 'num_b', 'num_c', 'num_d', 'num_e', 'num_f', 'num_g', 'num_h', 'num_i', 'num_j', 'num_k', 'num_l', 'num_m', 'num_n', 'num_o', 'num_p', 'num_q', 'num_r', 'num_s', 'num_t', 'num_u'], // 'num_v', 'num_w', 'num_x', 'num_y', 'num_z'],
            valid: ['title', 'width', 'calc_code', 'decimal', 'unit', 'expr'],
            def: { title: '数值', width: 80, decimal: 2, type: 'num', unit: ''}
        },
        { key: 'spec', name: '规格', fields: ['spec'], valid: ['title', 'width', 'rela_col', 'spec_set'], def: { title: '规格', width: 80, rela_col: '', type: 'spec'} },
        { key: 'qty', name: '数量', fields: ['qty'], valid: ['title', 'width', 'expr', 'decimal'], def: { title: '数量', width: 80, decimal: 2, expr: '', type: 'qty' } },
    ];
    ValidColInfo.forEach(vci => { return vci.count = vci.fields.length; });
    const TestData = {
        col_set: [
            { title: '名称', width: 80, type: 'str', field: 'str1' },
            { title: '长度', width: 80, type: 'num', field: 'num_a', decimal: 2, unit: 'm', calc_code: 'A' },
            { title: '宽度', width: 80, type: 'num', field: 'num_b', decimal: 2, unit: 'm', calc_code: 'B' },
            { title: '高度', width: 80, type: 'num', field: 'num_c', decimal: 2, unit: 'm', calc_code: 'C' },
            { title: '数量', width: 80, type: 'num', field: 'qty', decimal: 2, },
        ]
    };
    return { EmptySpreadCache, BaseSpreadColSetting, ValidColInfo, TestData, ValidCount: 50 };
})();
const Cost = (function(){
    const EmptySpreadCache = {
        cols: [],
        emptyRows: 0,
        headRows: 1,
        headRowHeight: [32],
        defaultRowHeight: 21,
        headerFont: '12px 微软雅黑',
        font: '12px 微软雅黑',
        frozenColCount: 2,
        frozenLineColor: '#93b5e4',
    };
    const BaseSpreadColSetting = {
        code: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 0, width: 80, formatter: '@' },
        name: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 0, width: 80, formatter: '@' },
        party_b: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 0, width: 80, formatter: '@' },
        yf_excl_tax_tp: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 2, width: 80, type: 'Number' },
        in_excl_tax_tp: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 2, width: 80, type: 'Number' },
        sf_excl_tax_tp: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 2, width: 80, type: 'Number' },
        str: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 0, width: 80, formatter: '@' },
        num: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 2, width: 80, type: 'Number' },
        postil: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 0, width: 80, formatter: '@', cellType: 'ellipsisAutoTip'},
        sf_percent: { title: '', colSpan: '1', rowSpan: '1', field: '', hAlign: 2, width: 80, type: 'Number', readOnly: true },
    };
    const ValidColInfo = [
        { key: 'code', name: '编号', fields: ['code'], valid: ['title', 'width'], def: { title: '编号', width: 80, type: 'code'} },
        { key: 'name', name: '名称', fields: ['name'], valid: ['title', 'width'], def: { title: '名称', width: 80, type: 'name'} },
        { key: 'party_b', name: '乙方', fields: ['party_b'], valid: ['title', 'width'], def: { title: '乙方', width: 80, type: 'party_b'} },
        { key: 'yf_excl_tax_tp', name: '应付(不含税)', fields: ['yf_excl_tax_tp'], valid: ['title', 'width', 'calc_code', 'decimal', 'sum'], def: { title: '已结(不含税)', width: 80, type: 'yf_excl_tax_tp', decimal: 6} },
        { key: 'in_excl_tax_tp', name: '入账(不含税)', fields: ['in_excl_tax_tp'], valid: ['title', 'width', 'calc_code', 'decimal', 'sum'], def: { title: '入账(不含税)', width: 80, type: 'in_excl_tax_tp', decimal: 6} },
        { key: 'sf_excl_tax_tp', name: '实付(不含税)', fields: ['sf_excl_tax_tp'], valid: ['title', 'width', 'calc_code', 'decimal', 'sum'], def: { title: '实付(不含税)', width: 80, type: 'sf_excl_tax_tp', decimal: 6} },
        { key: 'str', name: '文本', fields: ['str1', 'str2', 'str3', 'str4'], valid: ['title', 'width'], def: { title: '文本', width: 80, type: 'str'} },
        {
            key: 'num', name: '数值/计算',
            fields: ['num_a', 'num_b', 'num_c', 'num_d', 'num_e', 'num_f', 'num_g', 'num_h', 'num_i', 'num_j', 'num_k', 'num_l', 'num_m', 'num_n', 'num_o', 'num_p'], // 'num_q', 'num_r', 'num_s', 'num_t', 'num_u', 'num_v', 'num_w', 'num_x', 'num_y', 'num_z'],
            valid: ['title', 'width', 'calc_code', 'decimal', 'unit', 'expr', 'sum'],
            def: { title: '数值/计算', width: 80, decimal: 6, type: 'num', unit: ''}
        },
        { key: 'postil', name: '本期批注', fields: ['postil'], valid: ['title', 'width'], def: { title: '本期批注', width: 120, type: 'postil'} },
        { key: 'sf_percent', name: '实付比例', fields: ['sf_percent'], valid: ['title', 'width', 'calc_code'], def: { title: '实付比例', width: 80, type: 'sf_percent', decimal: 2} },
    ];
    ValidColInfo.forEach(vci => { return vci.count = vci.fields.length; });
    return { EmptySpreadCache, BaseSpreadColSetting, ValidColInfo, ValidCount: 10, subSpreadReadOnly: ['code', 'name', 'party_b', 'yf_excl_tax_tp', 'sf_excl_tax_tp', 'in_excl_tax_tp'] };
})();
function randomWord(randomFlag, min, max){
    let str = "",
        range = min,
        arr = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'],
        pos;

    // 随机产生
    if(randomFlag){
        range = Math.round(Math.random() * (max-min)) + min;
    }
    for(var i=0; i<range; i++){
        pos = Math.round(Math.random() * (arr.length-1));
        str += arr[pos];
    }
    return str;
}

module.exports = app => {
    class CalcTmpl extends app.BaseService {
        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'calc_tmpl';
            this.TemplateRela = { posCalc: PosCalc, cost: Cost };
        }

        async getAllTemplate(pid, type, sort = 'asc') {
            // return this.getAllDataByCondition({
            //     columns: ['id', 'pid', 'name', 'create_user_id', 'is_locked'],
            //     where: { pid, type },
            //     orders: [['create_time', sort]]
            // });
            const sql = 'SELECT ct.id, ct.pid, ct.folder_id, ct.name, ct.create_user_id, ct.is_locked, pa.name AS user_name' +
                        `    FROM ${this.tableName} ct LEFT JOIN ${this.ctx.service.projectAccount.tableName} pa ON ct.create_user_id = pa.id WHERE ct.pid = ? and ct.type = ? ORDER BY ct.create_time ${sort}`;
            return this.db.query(sql, [pid, type]);
        }
        async analysisTemplate(data) {
            const datas = data instanceof Array ? data : [data];
            datas.forEach(x => {
                x.col_set = x.col_set ? JSON.parse(x.col_set) : [];
                x.spread_cache = x.spread_cache ? JSON.parse(x.spread_cache) : {};
                x.field_cache = x.field_cache ? JSON.parse(x.field_cache) : {};
                x.decimal = x.decimal ? JSON.parse(x.decimal) : {};
                x.multi_header = x.multi_header ? JSON.parse(x.multi_header) : undefined;
                x.calc_expr = x.calc_expr ? JSON.parse(x.calc_expr) : [];
                x.calc_order = x.calc_order ? x.calc_order.split(',') : [];
                x.sub_spread_cache = x.sub_spread_cache ? JSON.parse(x.sub_spread_cache) : {};
            });
            for (const d of datas) {
                if (d.spec_set) {
                    d.specValue = await this.ctx.service.stdExtra.getAllDataByCondition({ columns: ['spec', 'value'], where: { list_id: d.spec_set }, orders:[['order', 'asc']]});
                } else {
                    d.specValue = [];
                }
            }
        }
        async getAllTemplateDetail(pid, type) {
            const result = await this.getAllDataByCondition({
                where: { pid, type },
                orders: [['create_time', 'desc']]
            });
            await this.analysisTemplate(result);
            return result;
        }
        async getTemplate(id) {
            const result = await this.getDataById(id);
            await this.analysisTemplate(result);
            return result;
        }
        async getProjectCalcTmplSet () {
            if (this.projectSet) return;
            const defaultSet = { posCalc: { validCount: 50 }, cost: { validCount: 10 } };
            const project = await this.ctx.service.project.getDataById(this.ctx.session.sessionProject.id);
            if (!project.calc_tmpl_set) {
                this.projectSet = defaultSet;
            } else {
                this.projectSet = JSON.parse(project.calc_tmpl_set);
                for (const prop in defaultSet) {
                    if (this.projectSet[prop]) {
                        this.projectSet[prop] = this.ctx.helper._.assign(defaultSet[prop], this.projectSet[prop]);
                    } else {
                        this.projectSet[prop] = defaultSet[prop];
                    }
                }
            }
        }

        //-------------------------- 以下方法不可随意修改 --------------------------
        // 根据列设置计算spreadjs配置相关，方法命名须严格按照【方法_类型】命名
        _getFillRowCount(rowSpan) {
            let count = 0;
            rowSpan.forEach(x => {
                count = count + x ? x : 1;
            });
            return count;
        }
        _calcMultiColHeader(iCol, spreadCol, colTitle, multiHeader) {
            if (multiHeader) {
                const colSpan = [], rowSpan = [], title = [];
                for (let iRow = 0; iRow < multiHeader.headRows - 1; iRow ++) {
                    const setSpan = multiHeader.headSpan.find(x => { return x.col === iCol && x.row === iRow; });
                    if (setSpan) {
                        colSpan.push(setSpan.colCount);
                        rowSpan.push(setSpan.rowCount);
                        title.push(setSpan.title);
                    } else {
                        const relaSpan = multiHeader.headSpan.find(x => { return iCol >= x.col && iCol < x.col + x.colCount && iRow >= x.row && iRow < x.row + x.rowCount; });
                        if (relaSpan) {
                            colSpan.push('');
                            rowSpan.push('');
                            title.push('');
                        }
                    }
                }
                if (colSpan.length === 0) {
                    spreadCol.rowSpan = multiHeader.headRows + '';
                    spreadCol.title = colTitle;
                } else {
                    colSpan.push(1);
                    rowSpan.push(multiHeader.headRows - this._getFillRowCount(rowSpan));
                    title.push(colTitle);
                    spreadCol.colSpan = colSpan.join('|');
                    spreadCol.rowSpan = rowSpan.join('|');
                    spreadCol.title = title.join('|');
                }
            } else {
                spreadCol.title = colTitle;
            }
        }
        _transExpr(expr, colSet) {
            if (!expr) return '';

            let newExpr = expr;
            colSet.forEach(x => {
                if (x.calc_code) newExpr = newExpr.replace(new RegExp(x.calc_code, 'gm'), x.field);
            });
            return newExpr;
        }
        _calcSpreadCache_posCalc(colSet) {
            const relaConst = this.TemplateRela.posCalc;
            const result = JSON.parse(JSON.stringify(relaConst.EmptySpreadCache));
            for (const col of colSet) {
                const info = relaConst.ValidColInfo.find(x => { return x.key === col.type });
                const spreadCol = JSON.parse(JSON.stringify(relaConst.BaseSpreadColSetting[col.type]));
                spreadCol.field = col.field;
                info.valid.forEach(v => {
                    if (v === 'unit' && col.unit) {
                        spreadCol.title = col.title + '(' + col.unit + ')';
                    } else if (v === 'expr') {
                        spreadCol.expr = this._transExpr(col.expr, colSet);
                        spreadCol.readOnly = spreadCol.expr;
                    } else {
                        if (col[v] !== undefined) spreadCol[v] = col[v];
                    }
                });
                if (col.calc_code) spreadCol.title = spreadCol.title + '\n' + col.calc_code;
                if (col.expr) spreadCol.title = spreadCol.title + '\n' + col.expr;
                result.cols.push(spreadCol);
            }
            const specCol = colSet.find(x => { return x.type === 'spec'; });
            if (specCol) {
                const relaSet = colSet.find(x => { return x.calc_code === specCol.rela_col; });
                const relaCol = result.cols.find(x => { return x.field === relaSet.field; });
                relaCol.readOnly = true;
            }
            return result;
        }
        _calcSpreadCache_cost(colSet, multiHeader) {
            const relaConst = this.TemplateRela.cost;
            const result = JSON.parse(JSON.stringify(relaConst.EmptySpreadCache));
            if (multiHeader) {
                result.headRows = multiHeader.headRows;
                result.headRowHeight = multiHeader.headRowHeight;
            }
            for (const [iCol, col] of colSet.entries()) {
                const info = relaConst.ValidColInfo.find(x => { return x.key === col.type });
                const spreadCol = JSON.parse(JSON.stringify(relaConst.BaseSpreadColSetting[col.type]));
                spreadCol.field = col.field;
                let colTitle = col.title;
                info.valid.forEach(v => {
                    if (v === 'unit' && col.unit) {
                        colTitle = colTitle + '(' + col.unit + ')';
                    } else if (v === 'expr') {
                        spreadCol.expr = this._transExpr(col.expr, colSet);
                        spreadCol.readOnly = spreadCol.expr;
                    } else {
                        if (col[v] !== undefined) spreadCol[v] = col[v];
                    }
                });
                if (col.calc_code) {
                    if (col.expr) {
                        colTitle = colTitle + '\n' + col.calc_code + '(' + col.expr + ')';
                    } else {
                        colTitle = colTitle + '\n' + col.calc_code;
                    }
                }
                this._calcMultiColHeader(iCol, spreadCol, colTitle, multiHeader);

                result.cols.push(spreadCol);
            }
            return result;
        }
        calcSpreadCache(type, colSet, multiHeader) {
            const funName = '_calcSpreadCache_' + type;
            if (this[funName]) return this[funName](colSet, multiHeader);
            return this.TemplateRela[type] ? this.TemplateRela[type].EmptySpreadCache : {};
        }

        async getCalcTestData_posCalc(colSet, count) {
            const result = [];
            const specCol = colSet.find(x => { return x.type === 'spec'; });
            const specSet = specCol && specCol.spec_set ? await this.ctx.service.stdExtra.getAllDataByCondition({ columns: ['spec', 'value'], where: { list_id: specCol.spec_set }, orders:[['order', 'asc']]}) : [];
            const relaCol = specCol && specCol.rela_col ? colSet.find(x => { return x.calc_code === specCol.rela_col; }) : null;

            for (let i = 0; i < count; i++) {
                const testData = {};
                colSet.forEach(x => {
                    if (x.type === 'num') testData[x.field] = Math.max(Math.floor(Math.random()*10), 1);
                    if (x.type === 'str') testData[x.field] = randomWord(true, 3, 6);
                });
                if (specCol && specSet.length > 0) {
                    testData.spec = specSet[0].spec;
                    if (relaCol) testData[relaCol.field] = specSet[0].value;
                }
                result.push(testData);
            }
            return result;
        }
        async getCalcTestData_cost(colSet, count) {
            const result = [];
            for (let i = 0; i < count; i++) {
                const testData = {};
                colSet.forEach(x => {
                    if (x.type === 'num') testData[x.field] = Math.max(Math.floor(Math.random()*10), 1);
                    if (x.type === 'str') testData[x.field] = randomWord(true, 3, 6);
                });
                result.push(testData);
            }
            return result;
        }
        async getCalcTestData(colSet, type, count = 1) {
            const funName = 'getCalcTestData_' + type;
            if (this[funName]) return await this[funName](colSet, count);
            return [];
        }

        async _checkTemplateUsed_posCalc(template) {
            const templates = template instanceof Array ? template : [template];
            templates.forEach(x => { x.used = []; x.used_count = 0; });
            const tender = await this.ctx.service.tender.getAllDataByCondition({ columns: ['id'], where: { project_id: templates[0].pid }});
            for (const t of tender) {
                const used = await this.ctx.service.ledgerExtra.getUsedCalcTemplate(t.id);
                const ancGclUsed = await this.ctx.service.ancillaryGcl.getUsedCalcTemplate(t.id);
                templates.forEach(x => {
                    const u = used.find(ut => { return ut.calc_template === x.id; }) || { count: 0 };
                    const u2 = ancGclUsed.find(ut => { return ut.calc_template === x.id; }) || { count: 0 };
                    if (u.count > 0 || u2.count > 0) x.used.push(t.id);
                    x.used_count = this.ctx.helper.sum([ x.used_count, u.count, u2.count ]);
                });
            }
        }
        async _checkTemplateUsed_cost(template) {
            const templates = template instanceof Array ? template : [template];
            templates.forEach(x => { x.used = []; x.used_count = 0; });
            // todo 检查使用情况
        }
        async checkTemplateUsed(template, type) {
            if (!template || template.length === 0) return;

            const funName = '_checkTemplateUsed_' + type;
            if (this[funName]) return await this[funName](template);
        }
        // ----------------------------------------------------------------------

        async _addTemplate(name, type, folder_id) {
            if (ValidTemplateType.indexOf(type) < 0) throw '新增的模板类型非法';
            await this.getProjectCalcTmplSet();
            const count = await this.count({pid: this.ctx.session.sessionProject.id, type});
            if (count >= this.projectSet[type].validCount) throw '已达模板使用上限';

            const relaConst = this.TemplateRela[type];
            const insertData = {
                id: this.uuid.v4(),
                pid: this.ctx.session.sessionProject.id,
                // spid: this.ctx.subProject.id,
                // tid: this.ctx.tender.id,
                folder_id: folder_id || '',
                name: name || '新增计算模板',
                type,
                create_user_id: this.ctx.session.sessionUser.accountId,
                update_user_id: this.ctx.session.sessionUser.accountId,
                // 关键默认值
                col_set: '[]',
                spread_cache: JSON.stringify(relaConst.EmptySpreadCache),
                field_cache: '{}',
                decimal: JSON.stringify([{ def: 2 }]),
                sub_spread_cache: '[]',
            };
            await this.db.insert(this.tableName, insertData);
            const result = await this.getTemplate(insertData.id);
            result.user_name = this.ctx.session.sessionUser.name;
            return result;
        }
        async checkTemplateEdit(id) {
            const template = await this.getDataById(id);
            if (!template) throw '编辑的模板不存在';
            if (template.pid !== this.ctx.session.sessionProject.id) throw '模板不属于当前项目，请刷新后重试';
            if (template.create_user_id !== this.ctx.session.sessionUser.accountId) throw '非您创建的模板';
            if (template.is_locked) throw '模板已被锁定';
            await this.checkTemplateUsed(template, template.type);
            if (template.used.length > 0) throw '模板已被使用';
            return template;
        }
        _getCalcLeaf(cur, cols, parentField) {
            if (!cur || !cur.expr) return [];
            const codeReg = /num_[a-z]{1}/gm;
            const codeParam = cur.expr.match(codeReg);
            if (!codeParam || codeParam.length === 0) return [];

            const result = [...codeParam];
            for (const op of codeParam) {
                const relaCol = cols.find(x => { return x.field === op; });
                if (relaCol.field === cur.field || op === parentField) {
                    result.push(op);
                } else {
                    const sub = this._getCalcLeaf(relaCol, cols, cur.field);
                    if (sub.length > 0) {
                        result.push(...sub);
                    } else {
                        result.push(op);
                    }
                }
            }
            return this.ctx.helper._.uniq(result);
        }
        _getCalcExpr(cols) {
            const calcOrder = [];
            for (const c of cols) {
                if (c.type !== 'Number') continue;
                calcOrder.push({ field: c.field, expr: c.expr || '', decimal: c.decimal, calc_code: c.calc_code });
            }
            for (const co of calcOrder) {
                co.leaf = this._getCalcLeaf(co, calcOrder);
            }
            calcOrder.sort((x, y) => {return x.leaf.length - y.leaf.length; });
            return calcOrder;
        }
        async _saveTemplate(id, data) {
            const org = await this.checkTemplateEdit(id);
            const updateData = { id };
            if (data.name !== undefined) updateData.name = data.name;
            if (data.col_set !== undefined || data.multi_header !== undefined) {
                await this.checkTemplateUsed(org, org.type);
                if (org.used.length > 0) throw '模板已使用，不可修改配置！';

                updateData.col_set = JSON.stringify(data.col_set);
                updateData.multi_header = JSON.stringify(data.multi_header);
                const spread_cache = this.calcSpreadCache(org.type, data.col_set, data.multi_header);
                if (org.type === 'cost') {
                    spread_cache.cols[0].cellType = 'tree';
                    const treeSpreadCache = this.ctx.helper.clone(spread_cache);
                    treeSpreadCache.cols.splice(treeSpreadCache.cols.findIndex(x => { return x.field === 'party_b';}), 1);
                    updateData.spread_cache = JSON.stringify(treeSpreadCache);
                    const treeCol = treeSpreadCache.cols.find(x => { return x.cellType === 'tree'});
                    delete treeCol.cellType;
                    for (const col of treeSpreadCache.cols) {
                        if (this.TemplateRela.cost.subSpreadReadOnly.indexOf(col.field) >= 0) col.readOnly = true;
                    }
                    const sub_spread_cache = { common: treeSpreadCache };
                    const dealSpreadCache = this.ctx.helper.clone(spread_cache);
                    const treeDCol = dealSpreadCache.cols.find(x => { return x.cellType === 'tree'});
                    delete treeDCol.cellType;
                    sub_spread_cache.deal = dealSpreadCache;
                    for (const col of dealSpreadCache.cols) {
                        if (this.TemplateRela.cost.subSpreadReadOnly.indexOf(col.field) >= 0) col.readOnly = true;
                    }
                    updateData.sub_spread_cache = JSON.stringify(sub_spread_cache);
                } else {
                    updateData.spread_cache = JSON.stringify(spread_cache);
                }
                const field_cache = {};
                data.col_set.forEach(x => { field_cache[x.field] = x.title; });
                updateData.field_cache = JSON.stringify(field_cache);
                const decimal = { def: 2 };
                spread_cache.cols.forEach(x => { if (x.decimal !== null && x.decimal !== undefined) decimal[x.field] = x.decimal; });
                updateData.decimal = JSON.stringify(decimal);
                const calc_expr = this._getCalcExpr(spread_cache.cols);
                updateData.calc_expr = JSON.stringify(calc_expr);
                updateData.calc_order = calc_expr.map(x => { return x.field; }).join(',');
                const specCol = data.col_set.find(x => { return x.field === 'spec' });
                if (specCol) {
                    updateData.spec_set = specCol.spec_set;
                    updateData.spec_rela = data.col_set.find(x => { return x.calc_code === specCol.rela_col; }).field;
                } else {
                    updateData.spec_set = 0;
                    updateData.spec_rela = '';
                }
            }
            await this.db.update(this.tableName, updateData);
            return await this.getTemplate(id);
        }
        async _delTemplate(id) {
            const template = await this.checkTemplateEdit(id);
            await this.db.delete(this.tableName, { id });
            return template;
        }
        async _moveTemplate(id, folder_id) {
            const org = await this.getAllDataByCondition({ where: { id }});
            if (org.length !== id.length) throw '选择的模板不存在，请刷新后重试';

            const updateData = id.map(x => { return { id: x, folder_id }});
            await this.db.updateRows(this.tableName, updateData);
            return updateData;
        }
        async _copyTemplate(id, folder_id) {
            const org = await this.getDataById(id);
            if (!org || org.pid !== this.ctx.session.sessionProject.id) throw '复制的模板不存在，请刷新后重试';

            await this.getProjectCalcTmplSet();
            const count = await this.count({pid: this.ctx.session.sessionProject.id, type: org.type});
            if (count >= this.projectSet[org.type].validCount) throw '已达模板使用上限';

            const insertData = {
                id: this.uuid.v4(),
                pid: this.ctx.session.sessionProject.id,
                folder_id: folder_id,
                name: org.name,
                type: org.type,
                create_user_id: this.ctx.session.sessionUser.accountId,
                update_user_id: this.ctx.session.sessionUser.accountId,
                col_set: org.col_set,
                spread_cache: org.spread_cache,
                sub_spread_cache: org.sub_spread_cache,
                field_cache: org.field_cache,
                decimal: org.decimal,
                calc_expr: org.calc_expr,
                calc_order: org.calc_order,
                spec_set: org.spec_set,
                spec_rela: org.spec_rela,
                multi_header: org.multi_header,
            };
            await this.db.insert(this.tableName, insertData);

            const result = await this.getTemplate(insertData.id);
            result.user_name = this.ctx.session.sessionUser.name;
            return result;
        }
        async saveTemplate(data) {
            const result = {};
            if (data.add) result.add = await this._addTemplate(data.add.name, data.type, data.add.folder_id);
            if (data.del) result.del = await this._delTemplate(data.del);
            if (data.update) result.update = await this._saveTemplate(data.update.id, data.update);
            if (data.move) result.move = await this._moveTemplate(data.move, data.folder_id);
            if (data.copy) result.copy = await this._copyTemplate(data.copy.id, data.copy.folder_id);
            return result;
        }
        async reCalcTemplate(id, force) {
            const data = await this.checkTemplateEdit(id);
            const updateData = { id };
            if (!force) {
                await this.checkTemplateUsed(data);
                if (data.used.length > 0) throw '模板已使用，不可修改配置！';
            }
            await this.analysisTemplate(data);

            updateData.col_set = JSON.stringify(data.col_set);
            updateData.multi_header = JSON.stringify(data.multi_header);
            const spread_cache = this.calcSpreadCache(data.type, data.col_set, data.multi_header);
            updateData.spread_cache = JSON.stringify(spread_cache);
            const field_cache = {};
            data.col_set.forEach(x => { field_cache[x.field] = x.title; });
            updateData.field_cache = JSON.stringify(field_cache);
            const decimal = { def: 2 };
            spread_cache.cols.forEach(x => { if (x.decimal !== null && x.decimal !== undefined) decimal[x.field] = x.decimal; });
            updateData.decimal = JSON.stringify(decimal);
            const calc_expr = this._getCalcExpr(spread_cache.cols);
            updateData.calc_expr = JSON.stringify(calc_expr);
            updateData.calc_order = calc_expr.map(x => { return x.field; }).join(',');
            const specCol = data.col_set.find(x => { return x.field === 'spec' });
            if (specCol) {
                updateData.spec_set = specCol.spec_set;
                updateData.spec_rela = data.col_set.find(x => { return x.calc_code === specCol.rela_col; }).field;
            } else {
                updateData.spec_set = 0;
                updateData.spec_rela = '';
            }
            await this.db.update(this.tableName, updateData);
        }

        _calcExpr(formula) {
            const percentReg = /((\d+)|((\d+)(\.\d+)))%/g;
            const percent = formula.match(percentReg);
            if (percent) {
                for (const p of percent) {
                    const v = math.eval(p.replace(new RegExp('%', 'gm'), '/100'));
                    formula = formula.replace(p, v);
                }
            }
            try {
                // 使用mathjs计算 math.eval('17259401.95*0.9') = 15533461.754999999，正确应为 15533461.755
                // const value = math.eval(formula);
                // 使用逆波兰法四则运算，可防止出现误差，但是只支持四则运算，不支持科学运算
                // const value = this.ctx.helper.calcExprStrRpn(formula);
                // 使用mathjs的大数运算，可支持所有
                const value = parseFloat(math.eval(formula));
                return Number.isFinite(value) ? value : 0;
            } catch(err) {
                return 0;
            }
        }

        calcByTemplate(data, updateData, orgData, calcExpr, updateExprInfo) {
            const codeReg = /num_[a-z]{1}/gm;
            const specRegs = [];
            for (const ce of calcExpr) {
                if (!ce.field.match(codeReg)) specRegs.push(new RegExp(ce.field, 'gm'));
            }
            for (const ce of calcExpr) {
                if (ce.expr) {
                    let calcExpr = ce.expr;
                    for (const sr of specRegs) {
                        const specParam = calcExpr.match(sr);
                        if (specParam) {
                            for (const sp of specParam) {
                                calcExpr = calcExpr.replace(new RegExp(sp, 'gm'), (data[sp] !== undefined ? data[sp] : orgData[sp]) || 0);
                            }
                        }
                    }
                    const codeParam = calcExpr.match(codeReg);
                    if (codeParam) {
                        for (const cp of codeParam) {
                            calcExpr = calcExpr.replace(new RegExp(cp, 'gm'), (data[cp] !== undefined ? data[cp] : orgData[cp]) || 0);
                        }
                    }
                    if (updateExprInfo && updateExprInfo[ce.field]) updateExprInfo[ce.field] = calcExpr;
                    try {
                        data[ce.field] = this.ctx.helper.round(this._calcExpr(calcExpr.replace(new RegExp('%', 'gm'), '/100')), ce.decimal || 2);
                    } catch(err) {
                        data[ce.field] = 0;
                    }
                } else {
                    if (updateData[ce.field] !== undefined) data[ce.field] = this.ctx.helper.round(updateData[ce.field], ce.decimal || 2);
                }
            }
        }
    }

    return CalcTmpl;
};