Calculation/db_script/change.js

// 计算变更令，正负变更数

const defaultInfo = require('../app/const/tender_info');
const BaseUtil = require('./baseUtils');
const querySql = BaseUtil.querySql;
const ZhCalc = BaseUtil.ZhCalc;

const checkChange = async function(change, decimal) {
    const changeBills = await querySql('Select * From zh_change_audit_list where cid = ?', [change.cid]);
    let p_tp = 0, n_tp = 0;
    for (const cb of changeBills) {
        cb.tp = ZhCalc.mul(cb.spamount, cb.unit_price, change.tp_decimal || decimal.tp);
        if (cb.spamount > 0) {
            p_tp = ZhCalc.add(p_tp, cb.tp);
        } else if (cb.spamount < 0){
            n_tp = ZhCalc.add(n_tp, cb.tp);
        }
    }
    await querySql('Update zh_change Set positive_tp = ?, negative_tp = ? Where cid = ?', [p_tp, n_tp, change.cid]);
    console.log(`Update Change ${change.cid}: p_tp(${p_tp}), n_tp(${n_tp})`);
};

const checkStage = async function(stage, decimal, preStage) {
    const billsTable = 'zh_stage_bills_' + stage.tid % 10;
    const stageBills = await querySql(`Select * From ${billsTable} Where sid = ?`, [stage.id]);

    const history = stage.tp_history ? JSON.parse(stage.tp_history) : null;
    if (history) {
        for (const h of history) {
            const sbFilter = stageBills.filter(x => {
                return x.times < h.times || (x.times === h.times && x.order <= h.order);
            });
            const sbLatest = BaseUtil.filterLastestData(sbFilter, ['lid']);
            history.positive_qc_tp = 0;
            history.negative_qc_tp = 0;
            for (const sbl of sbLatest) {
                stage.positive_qc_tp = ZhCalc.add(sbl.positive_qc_tp, stage.positive_qc_tp);
                stage.negative_qc_tp = ZhCalc.add(sbl.negative_qc_tp, stage.negative_qc_tp);
            }
        }
        await querySql('Update zh_stage Set tp_history = ? Where id = ?', [JSON.stringify(history), stage.id]);
    }

    const stageBillsLatest = BaseUtil.filterLastestData(stageBills, ['lid']);
    if (stageBillsLatest.length === 0) return;

    stage.positive_qc_tp = 0;
    stage.pre_positive_qc_tp = preStage ? ZhCalc.add(preStage.positive_qc_tp, preStage.pre_positive_qc_tp) : 0;
    stage.negative_qc_tp = 0;
    stage.pre_negative_qc_tp = preStage ? ZhCalc.add(preStage.negative_qc_tp, preStage.pre_negative_qc_tp) : 0;
    for (const sbl of stageBillsLatest) {
        stage.positive_qc_tp = ZhCalc.add(sbl.positive_qc_tp, stage.positive_qc_tp);
        stage.negative_qc_tp = ZhCalc.add(sbl.negative_qc_tp, stage.negative_qc_tp);
    }

    await querySql('Update zh_stage Set positive_qc_tp = ?, pre_positive_qc_tp = ?, negative_qc_tp = ?, pre_negative_qc_tp = ? Where id = ?', [stage.positive_qc_tp, stage.pre_positive_qc_tp, stage.negative_qc_tp, stage.pre_negative_qc_tp, stage.id]);
    console.log(`Update Stage ${stage.order}: p_tp(${stage.positive_qc_tp}), pre_p_tp(${stage.pre_positive_qc_tp}), n_tp(${stage.negative_qc_tp}), pre_n_tp(${stage.pre_negative_qc_tp})`);
};

const checkStagePos = async function (stage, decimal, preStage) {
    const posTable = 'zh_stage_pos_' + stage.tid % 20;
    const posFinalTable = 'zh_stage_pos_final_' + stage.tid % 20;
    // 计算更新stagePos相关
    const stageChange = await querySql('SELECT * FROM zh_stage_change WHERE sid = ?', [stage.id]);
    const stagePos = await querySql(`SELECT * From ${posTable} where sid = ?`, [stage.id]);
    for (const sp of stagePos) {
        const filterTimesOrder = stageChange.filter(x => {
            if (x.pid === sp.pid && !x.no_value) {
                return x.stimes < sp.times || (x.stimes === sp.times && x.sorder <= sp.order);
            } else {
                return false;
            }
        });
        const filterLatest = BaseUtil.filterLastestData(filterTimesOrder, ['cbid'], 'stimes', 'sorder');
        if (filterLatest.length === 0) continue;

        sp.positive_qc_qty = 0;
        sp.negative_qc_qty = 0;
        for (const fl of filterLatest) {
            if (fl.minus) {
                sp.negative_qc_qty = ZhCalc.add(sp.negative_qc_qty, fl.qty);
            } else {
                sp.positive_qc_qty = ZhCalc.add(sp.positive_qc_qty, fl.qty);
            }
        }
        if (sp.positive_qc_qty || sp.negative_qc_qty)
            await querySql(`Update ${posTable} Set positive_qc_qty = ?, negative_qc_qty = ? Where id = ?`, [sp.positive_qc_qty, sp.negative_qc_qty, sp.id]);
    }
    console.log(`Update StagePos ${stage.order}: ${stagePos.length}`);
    // 计算更新stagePosFinal
    const stagePosLatest = BaseUtil.filterLastestData(stagePos, ['pid']);
    const stagePosLatestIndex = {};
    stagePosLatest.forEach(x => { stagePosLatestIndex[x.pid] = x; });
    const preStagePosFinal = preStage ? await querySql(`Select * From ${posFinalTable} Where sid = ?`, [preStage.id]) : [];
    const preStagePosFinalIndex = {};
    preStagePosFinal.forEach(x => { preStagePosFinalIndex[x.pid] = x; });
    const stagePosFinal = await querySql(`Select * From ${posFinalTable} Where sid = ?`, [stage.id]);
    for (const spf of stagePosFinal) {
        const curSp = stagePosLatestIndex[spf.pid];
        const preSp = preStagePosFinalIndex[spf.pid];
        if (preSp) {
            if (curSp) {
                spf.positive_qc_qty = ZhCalc.add(preSp.positive_qc_qty, curSp.positive_qc_qty);
                spf.negative_qc_qty = ZhCalc.add(preSp.negative_qc_qty, curSp.negative_qc_qty);
            } else {
                spf.positive_qc_qty = preSp.positive_qc_qty;
                spf.negative_qc_qty = preSp.negative_qc_qty;
            }
        } else if (curSp) {
            spf.positive_qc_qty = curSp.positive_qc_qty;
            spf.negative_qc_qty = curSp.negative_qc_qty;
        }
        if (spf.positive_qc_qty || spf.negative_qc_qty)
            await querySql(`Update ${posFinalTable} Set positive_qc_qty = ?, negative_qc_qty = ? Where id = ?`, [spf.positive_qc_qty, spf.negative_qc_qty, spf.id]);
    }
    console.log(`Update StagePosFinal ${stage.order}: ${stagePosFinal.length}`);
};

const checkStageBills = async function (stage, decimal, preStage) {
    const billsTable = 'zh_stage_bills_' + stage.tid % 10;
    const billsFinalTable = 'zh_stage_bills_final_' + stage.tid % 10;
    // 计算更新stagePos相关
    const stageChange = await querySql('SELECT sc.*, cal.unit_price FROM zh_stage_change sc Left Join zh_change_audit_list cal ON sc.cbid = cal.id WHERE sid = ?', [stage.id]);
    const stageBills = await querySql(`SELECT * From ${billsTable} where sid = ?`, [stage.id]);
    for (const sb of stageBills) {
        const filterTimesOrder = stageChange.filter(x => {
            if (x.no_value) return false;
            if (x.lid !== sb.lid) return false;
            return x.stimes < sb.times || (x.stimes === sb.times && x.sorder <= sb.order);
        });
        const filterLatest = BaseUtil.filterLastestData(filterTimesOrder, ['pid', 'cbid'], 'stimes', 'sorder');
        if (filterLatest.length === 0) continue;

        sb.positive_qc_qty = 0;
        sb.negative_qc_qty = 0;
        for (const fl of filterLatest) {
            if (!sb.unit_price) sb.unit_price = fl.unit_price;
            if (fl.minus) {
                sb.negative_qc_qty = ZhCalc.add(sb.negative_qc_qty, fl.qty);
            } else {
                sb.positive_qc_qty = ZhCalc.add(sb.positive_qc_qty, fl.qty);
            }
        }
        if (sb.positive_qc_qty || sb.negative_qc_qty) {
            sb.positive_qc_tp = ZhCalc.mul(sb.unit_price, sb.positive_qc_qty, decimal.tp);
            sb.negative_qc_tp = ZhCalc.mul(sb.unit_price, sb.negative_qc_qty, decimal.tp);
            await querySql(`Update ${billsTable} Set positive_qc_qty = ?, positive_qc_tp = ?, negative_qc_qty = ?, negative_qc_tp = ? Where id = ?`,
                [sb.positive_qc_qty, sb.positive_qc_tp, sb.negative_qc_qty, sb.negative_qc_tp, sb.id]);
        }
    }
    console.log(`Update StageBills ${stage.order}: ${stageBills.length}`);
    // 计算更新stagePosFinal
    const stageBillsLatest = BaseUtil.filterLastestData(stageBills, ['lid']);
    const stageBillsLatestIndex = {};
    stageBillsLatest.forEach(x => { stageBillsLatestIndex[x.lid] = x; });
    const preStageBillsFinal = preStage ? await querySql(`Select * From ${billsFinalTable} Where sid = ?`, [preStage.id]) : [];
    const preStageBillsFinalIndex = {};
    preStageBillsFinal.forEach(x => { preStageBillsFinalIndex[x.lid] = x; });
    const stageBillsFinal = await querySql(`Select * From ${billsFinalTable} Where sid = ?`, [stage.id]);
    for (const sbf of stageBillsFinal) {
        const curSb = stageBillsLatestIndex[sbf.lid];
        const preSb = preStageBillsFinalIndex[sbf.lid];
        if (preSb) {
            if (curSb) {
                sbf.positive_qc_qty = ZhCalc.add(preSb.positive_qc_qty, curSb.positive_qc_qty);
                sbf.positive_qc_tp = ZhCalc.add(preSb.positive_qc_tp, curSb.positive_qc_tp);
                sbf.negative_qc_qty = ZhCalc.add(preSb.negative_qc_qty, curSb.negative_qc_qty);
                sbf.negative_qc_tp = ZhCalc.add(preSb.negative_qc_tp, curSb.negative_qc_tp);
            } else {
                sbf.positive_qc_qty = preSb.positive_qc_qty;
                sbf.positive_qc_tp = preSb.positive_qc_tp;
                sbf.negative_qc_qty = preSb.negative_qc_qty;
                sbf.negative_qc_tp = preSb.negative_qc_tp;
            }
        } else if (curSb) {
            sbf.positive_qc_qty = curSb.positive_qc_qty;
            sbf.positive_qc_tp = curSb.positive_qc_tp;
            sbf.negative_qc_qty = curSb.negative_qc_qty;
            sbf.negative_qc_tp = curSb.negative_qc_tp;
        }
        if (sbf.positive_qc_qty || sbf.negative_qc_qty)
            await querySql(`Update ${billsFinalTable} Set positive_qc_qty = ?, positive_qc_tp = ?, negative_qc_qty = ?, negative_qc_tp = ? Where id = ?`,
                [sbf.positive_qc_qty, sbf.positive_qc_tp, sbf.negative_qc_qty, sbf.negative_qc_tp, sbf.id]);
    }
    console.log(`Update StageBillsFinal ${stage.order}: ${stageBillsFinal.length}`);
};

const doComplete = async function() {
    try {
        const tender = await querySql('Select * From zh_tender Where id >= 11405');
        for (const t of tender) {
            console.log(`Update Tender ${t.id}:`);
            const info = await querySql('Select * From zh_tender_info where tid = ?', [t.id]);
            const decimal = info.length > 0 && info[0].decimal ? JSON.parse(info[0].decimal) : defaultInfo.defaultInfo.decimal;

            const changes = await querySql('Select * From zh_change where tid = ?', [t.id]);
            for (const c of changes) {
                await checkChange(c, decimal);
            }
            const stage = await querySql('Select * From zh_stage where tid = ? order by `order` asc', [t.id]);
            for (const [i, s] of stage.entries()) {
                await checkStagePos(s, decimal, i > 0 ? stage[i-1] : null);
                await checkStageBills(s, decimal, i > 0 ? stage[i-1] : null);
                await checkStage(s, decimal, i > 0 ? stage[i-1] : null);
            }
        }
    } catch (err) {
        console.log(err);
    }
    BaseUtil.closePool();
};
const doCompleteTest = async function(tid) {
    try {
        const tender = await querySql('Select * From zh_tender where id = ?', [tid]);
        for (const t of tender) {
            console.log(`Update Tender ${t.id}:`);
            const info = await querySql('Select * From zh_tender_info where tid = ?', [t.id]);
            const decimal = info.length > 0 && info[0].decimal ? JSON.parse(info[0].decimal) : defaultInfo.defaultInfo.decimal;

            const changes = await querySql('Select * From zh_change where tid = ?', [t.id]);
            for (const c of changes) {
                await checkChange(c, decimal);
            }
            const stage = await querySql('Select * From zh_stage where tid = ? order by `order` asc', [t.id]);
            for (const [i, s] of stage.entries()) {
                await checkStagePos(s, decimal, i > 0 ? stage[i-1] : null);
                await checkStageBills(s, decimal, i > 0 ? stage[i-1] : null);
                await checkStage(s, decimal, i > 0 ? stage[i-1] : null);
            }
        }
    } catch (err) {
        console.log(err);
    }
    BaseUtil.closePool();
};
const tenderId = process.argv[3];
if (tenderId) {
    doCompleteTest(tenderId);
} else {
    doComplete()
}