Calculation/db_script/project_spread.js

'use strict';

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

const SpreadConst = require('../app/const/spread');
const BaseUtil = require('./baseUtils');
const querySql = BaseUtil.querySql;

const generateSpreadSetting = function(colSet, emptyBase, BaseSetCol, BaseSpreadColSetting, spreadType = 'bills') {
    const spreadSetting = JSON.parse(JSON.stringify(emptyBase));
    for (const col of colSet) {
        if (!col.valid) continue;
        const dc = BaseSetCol.find(x => { return x.key === col.key; });
        if (!dc) continue;
        const orgBaseCols = BaseSpreadColSetting[col.key];
        if (!orgBaseCols) continue;

        const baseCols = JSON.parse(JSON.stringify(orgBaseCols));
        if (dc.fixed.indexOf('alias') < 0 && col.alias) {
            if (baseCols.length === 1) {
                if (baseCols[0].aliasFormat) {
                    baseCols[0].title = baseCols[0].aliasFormat.replace('{%s}', col.alias);
                    delete baseCols[0].aliasFormat;
                } else {
                    baseCols[0].title = col.alias;
                }
            } else {
                baseCols.forEach(x => {
                    if (x.aliasFormat) {
                        x.title = x.aliasFormat.replace('{%s}', col.alias);
                        delete x.aliasFormat;
                    }
                });
            }
        }
        spreadSetting.cols.push(...baseCols);
    }
    return spreadSetting;
};

const generateRelaSpread = function (colSetType, colSet) {
    const baseSetCol = colSetType.indexOf('stage') > 0 ? SpreadConst.BaseSetCol.Stage : SpreadConst.BaseSetCol.Ledger;
    const baseSpreadColSetting = colSetType.indexOf('stage') > 0 ? SpreadConst.BaseSpreadColSetting.Stage : SpreadConst.BaseSpreadColSetting.Ledger;
    const billsSpread = generateSpreadSetting(colSet, SpreadConst.EmptySpreadSetting[colSetType].bills, baseSetCol, baseSpreadColSetting.bills);
    const posSpread = generateSpreadSetting(colSet, SpreadConst.EmptySpreadSetting[colSetType].pos, baseSetCol, baseSpreadColSetting.pos, 'pos');
    return [billsSpread, posSpread];
};

const InsertSql = function (tableName, data) {
    const column = [], query = [], value = [];
    for (const prop in data) {
        column.push(prop);
        query.push('?');
        value.push(data[prop]);
    }
    return [`INSERT INTO ${tableName} (${column.join(',')}) VALUES (${query.join(',')})`, value]
};

const initProjectSpread = async function (project) {
    const exist = await querySql('Select * From zh_project_spread WHERE id = ?', [project.id]);
    if (exist.length > 0) {
        await querySql('DELETE FROM zh_project_spread WHERE id = ?', [project.id]);
    }

    for (const SpreadTemplate of SpreadConst.ProjectSpreadTemplate) {
        const DefaultSet = JSON.parse(JSON.stringify(SpreadTemplate.template));

        const sjsRela = project.sjs_rela ? JSON.parse(project.sjs_rela) : null;
        if (sjsRela) {
            for (const prop in DefaultSet) {
                const template = DefaultSet[prop];
                for (const lc of sjsRela.ledgerCol) {
                    const col = template.find(x => { return x.key === lc.field; });
                    if (lc.show) col.valid = 1;
                    if (lc.alias) col.alias = lc.alias;
                    if (lc.pos === 'name') {
                        const lColIndex = template.findIndex(x => { return x.key === lc.field; });
                        const lCol = template[lColIndex];
                        const preColIndex = template.findIndex(x => { return x.key === lc.pos; });
                        const preCol = template[preColIndex];
                        if (preCol && preColIndex !== lColIndex - 1) {
                            template.splice(lColIndex, 1);
                            template.splice(preColIndex + 1, 0, lCol);
                        }
                    }
                }
            }
        }
        [DefaultSet.tz_ledger_bills_spread, DefaultSet.tz_ledger_pos_spread] = generateRelaSpread('tz_ledger_set', DefaultSet.tz_ledger_set);
        [DefaultSet.tz_stage_bills_spread, DefaultSet.tz_stage_pos_spread] = generateRelaSpread('tz_stage_set', DefaultSet.tz_stage_set);
        [DefaultSet.gcl_ledger_bills_spread, DefaultSet.gcl_ledger_pos_spread] = generateRelaSpread('gcl_ledger_set', DefaultSet.gcl_ledger_set);
        [DefaultSet.gcl_stage_bills_spread, DefaultSet.gcl_stage_pos_spread] = generateRelaSpread('gcl_stage_set', DefaultSet.gcl_stage_set);
        for (const prop in DefaultSet) {
            DefaultSet[prop] = JSON.stringify(DefaultSet[prop]);
        }
        DefaultSet.pid = project.id;
        DefaultSet.code = SpreadTemplate.code;
        DefaultSet.name = SpreadTemplate.name;
        DefaultSet.is_default = SpreadTemplate.isDefault || 0;
        const [sql, sqlParams] = InsertSql('zh_project_spread', DefaultSet);
        await querySql(sql, sqlParams);
    };
};

const doComplete = async function(projectCode) {
    try {
        const whereSql = projectCode ? ` WHERE code = "${projectCode}"` : '';
        const project = await querySql(`Select * From zh_project ${whereSql}`);
        for (const p of project) {
            console.log(`Update Project ${p.code}(${p.id}):`);
            await initProjectSpread(p);
        }
    } catch (err) {
        console.log(err);
    }
    BaseUtil.closePool();
};

doComplete(process.argv[3]);