Calculation/db_script/bak/repair1027.js

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

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

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 checkStageBills = async function (stage, decimal, preStage, reCalcBills) {
    const billsTable = 'zh_stage_bills_' + stage.tid % 10;
    const billsFinalTable = 'zh_stage_bills_final_' + stage.tid % 10;
    const ledgerTable = 'zh_ledger_' + stage.tid % 10;
    // 计算更新stagePos相关
    const stageChange = await querySql(`SELECT sc.*, l.unit_price FROM zh_stage_change sc Left Join ${ledgerTable} l ON sc.lid = l.id WHERE sid = ?`, [stage.id]);
    const stageBills = await querySql(`SELECT * From ${billsTable} where sid = ?`, [stage.id]);
    if (reCalcBills) {
        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 doRepair1027 = async function(tid, sorder, reCalcBills) {
    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 stage = await querySql('Select * From zh_stage where tid = ? and `order` = ?', [t.id, sorder]);
            const preStage = sorder > 1 ? await querySql('Select * From zh_stage where tid = ? and `order` = ?', [t.id, sorder - 1]) : [];
            await checkStageBills(stage[0], decimal, preStage[0], reCalcBills);
            await checkStage(stage[0], decimal, preStage[0], reCalcBills);
        }
    } catch (err) {
        console.log(err);
    }
    BaseUtil.closePool();
};
const tenderId = parseInt(process.argv[3]);
const stageOrder = parseInt(process.argv[4]);
const reCalcBills = parseInt(process.argv[5]);
doRepair1027(tenderId, stageOrder, reCalcBills);