Calculation/db_script/checkStageFinal.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date
 * @version
 */
const bc = require('../app/lib/base_calc');
const _ = require('lodash');
const fs = require('fs');
const path = require('path');

const mysql = require('mysql');
const config = process.argv.splice(2);
const mysqlOptions = require(`../config/config.${config}`)({ baseDir: __dirname + '/app', root: __dirname, name: 'calc' }).mysql;

const pool = mysql.createPool(mysqlOptions.client);
const zeroRange = 0.0000000001;
const temp = 'temp';

const utils = {
    add(num1, num2) {
        return bc.add(num1 ? num1 : 0, num2 ? num2 : 0);
    },
    sub(num1, num2) {
        return bc.sub(num1 ? num1 : 0, num2 ? num2 : 0);
    },
    sum(array) {
        let result = 0;
        for (const a of array) {
            result = this.add(result, a);
        }
        return result;
    },
    checkZero(value) {
        return value === undefined || value === null || (_.isNumber(value) && Math.abs(value) < zeroRange);
    },
    checkNumEqual(value1, value2) {
        return Math.abs(this.sub(value2, value1)) < zeroRange;
    },
};

const querySql = async function (sql, sqlParam) {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, conn) {
            if (err) {
                reject(err);
            } else {
                conn.query(sql, sqlParam, function (err, rows, fields) {
                    //释放连接
                    conn.release();
                    //传递Promise回调对象
                    resolve(rows);
                });
            }
        });
    });
};

const stageSql = 'select * from zh_stage where tid = ? and `order` = ?';
const timesLen = 100;

const getDelSql = function (table, data) {
    const sql = [];
    for (const d of data) {
        sql.push('DELETE FROM `calculation`.`'+ table + '` WHERE `id`=' + d.id + ';');
    }
    return sql;
};

const curBillsSql = function (tid) {
    const departTable = 'zh_stage_bills_' + tid % 10;
    const sql =
        'SELECT Bills.* FROM ' + departTable + ' As Bills ' +
        '  INNER JOIN ( ' +
        '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + departTable +
        '      WHERE tid = ? And sid = ?' +
        '      GROUP BY `lid`' +
        '  ) As MaxFilter ' +
        '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`';
    return sql;
};
const preBillsSql = function (tid) {
    return 'Select * from zh_stage_bills_final_' + (tid % 10) + ' where sid = ?';
};
const calcBillsFinalData = function (stage, pre, cur) {
    if (!cur || cur.length === 0) return pre;
    if (!pre || pre.length === 0) return cur;

    for (const c of cur) {
        delete c.id;
        delete c.said;
        delete c.times;
        delete c.order;
        delete c.postil;
        c.sorder = stage.order;
        const p = pre.find(x => { return x.lid === c.lid; });
        if (p) {
            c.contract_qty = utils.add(c.contract_qty, p.contract_qty);
            c.contract_tp = utils.add(c.contract_tp, p.contract_tp);
            c.qc_qty = utils.add(c.qc_qty, p.qc_qty);
            c.qc_tp = utils.add(c.qc_tp, p.qc_tp);
            c.used = p.used || !utils.checkZero(c.contract_qty) || !utils.checkZero(c.qc_qty)
                || !utils.checkZero(c.contract_tp) || !utils.checkZero(c.qc_tp);
            pre.splice(pre.indexOf(p), 1);
        } else {
            c.used = !utils.checkZero(c.contract_qty) || !utils.checkZero(c.qc_qty)
                || !utils.checkZero(c.contract_tp) || !utils.checkZero(c.qc_tp);
        }
    }

    for (const p of pre) {
        if (p.id !== undefined) delete p.id;
        p.sid = stage.id;
        p.sorder = stage.order;
    }
    return cur.concat(pre);
};
const checkBillsFinal = async function (stage, preStage) {
    const curBills = await querySql(curBillsSql(stage.tid), [stage.tid, stage.id]);
    const preBills = await querySql(preBillsSql(stage.tid), [preStage.id]);

    const checkBills = await calcBillsFinalData(stage, preBills, curBills);

    const finalBills = await querySql(preBillsSql(stage.tid), [stage.id]);
    console.log(`checkBills(${stage.tid}, ${stage.id}): checkBills ${checkBills.length}, finalBills ${finalBills.length}`);
    const error = [];

    const tBills = [], fBills = [];
    if (finalBills.length > checkBills.length) {
        fs.writeFileSync(path.join(__dirname, temp, 'FinalBills_org_' + stage.tid + '_' + stage.order + '.json'), JSON.stringify(finalBills, '', '\t'));
        fs.writeFileSync(path.join(__dirname, temp, 'FinalBills_check_' + stage.tid + '_' + stage.order + '.json'), JSON.stringify(checkBills, '', '\t'));
        let show = false;
        for (const cb of checkBills) {
            let index = finalBills.findIndex(x => { return x.lid === cb.lid });
            if (index === -1) error.push(`bills error: ${data.id}(${data.lid})`);
            let check = false;
            while (index >= 0) {
                const data = finalBills[index];
                finalBills.splice(index, 1);
                if (!check && utils.checkNumEqual(data.contract_qty, cb.contract_qty) &&
                    utils.checkNumEqual(data.contract_tp, cb.contract_tp) &&
                    utils.checkNumEqual(data.qc_qty, cb.qc_qty) &&
                    utils.checkNumEqual(data.qc_tp, cb.qc_tp)) {
                    check = true;
                    tBills.push(data);
                } else {
                    if (!show && !check) {
                        show = true;
                        console.log(data, cb,
                            utils.checkNumEqual(data.contract_qty, cb.contract_qty),
                            utils.checkNumEqual(data.contract_tp, cb.contract_tp),
                            utils.checkNumEqual(data.qc_qty, cb.qc_qty),
                            utils.checkNumEqual(data.qc_tp, cb.qc_tp)
                        );
                    }
                    fBills.push(data);
                }
                index = finalBills.findIndex(x => { return x.lid === cb.lid });
            }
        }
        if (tBills.length !== checkBills.length) console.log('error: ' + stage.tid + ' ' + stage.id);

        fs.writeFileSync(path.join(__dirname, temp, 'FinalBills_keep_' + stage.tid + '_' + stage.order + '.json'), JSON.stringify(tBills, '', '\t'));
        fs.writeFileSync(path.join(__dirname, temp, 'FinalBills_del_' + stage.tid + '_' + stage.order + '.json'), JSON.stringify(fBills, '', '\t'));
        const sqlList = getDelSql('zh_stage_bills_final_' + (stage.tid % 10), fBills);
        fs.writeFileSync(path.join(__dirname, temp, 'FinalBills_del_' + stage.tid + '_' + stage.order + '.sql'), sqlList.join('\n'));
        console.log('check: ' + checkBills.length + ' keep: ' + tBills.length + ' del: ' + fBills.length);
        if (error.length > 0) fs.writeFileSync(path.join(__dirname, temp, 'FinalBills_error_' + stage.tid + '_' + stage.order + '.log'), error.join('\n'));
        for (const sl of sqlList) {
            console.log(sl);
            await querySql(sl, []);
        }
    }
};

const filterLastestData = function(stagePos) {
    const stagePosIndex = {};
    for (const sp of stagePos) {
        const key = 'sp-' + sp.pid;
        const spi = stagePosIndex[key];
        if (spi) {
            if ((spi.times * timesLen + spi.order) < (sp.times * timesLen + sp.order)) stagePosIndex[key] = sp;
        } else {
            stagePosIndex[key] = sp;
        }
    }
    const result = [];
    for (const prop in stagePosIndex) {
        result.push(stagePosIndex[prop]);
    }
    return result;
};
const getCurPosData = async function (tid, sid) {
    const departTable = 'zh_stage_pos_' + tid % 20;
    const sql = 'SELECT id, tid, sid, pid, lid, contract_qty, qc_qty, postil, `times`, `order`, `contract_expr`' +
        '  FROM ' + departTable +
        '  WHERE tid = ? And sid = ? ';
    const sqlParam = [tid, sid];
    const stagePos = await querySql(sql, sqlParam);
    return filterLastestData(stagePos);
};
const prePosSql = function (tid) {
    return 'Select * from zh_stage_pos_final_' + (tid % 20) + ' where sid = ?';
};
const calcPosFinalData = function (stage, pre, cur) {
    if (!cur || cur.length === 0) return pre;
    if (!pre || pre.length === 0) return cur;

    for (const c of cur) {
        delete c.id;
        delete c.said;
        delete c.times;
        delete c.order;
        delete c.postil;
        c.sorder = stage.order;
        const p = pre.find(x => { return x.pid === c.pid; });
        if (p) {
            c.contract_qty = utils.add(c.contract_qty, p.contract_qty);
            c.qc_qty = utils.add(c.qc_qty, p.qc_qty);
            c.used = p.used || !utils.checkZero(c.contract_qty) || !utils.checkZero(c.qc_qty);
            pre.splice(pre.indexOf(p), 1);
        } else {
            c.used = !utils.checkZero(c.contract_qty) || !utils.checkZero(c.qc_qty);
        }
    }

    for (const p of pre) {
        if (p.id !== undefined) delete p.id;
        p.sid = stage.id;
        p.sorder = stage.order;
    }
    return cur.concat(pre);
};
const findPos = function (arr, pid) {
    for (const [i, a] of arr.entries()) {
        if (a.pid === pid) return [i, a];
    }
    return [-1, null];
};

const checkPosFinal = async function (stage, preStage) {
    const curPos = await getCurPosData(stage.tid, stage.id);
    const prePos = await querySql(prePosSql(stage.tid), [preStage.id]);

    const checkPos = await calcPosFinalData(stage, prePos, curPos);

    const finalPos = await querySql(prePosSql(stage.tid), [stage.id]);
    console.log(`checkPos(${stage.tid}, ${stage.id}): checkPos ${checkPos.length}, finalPos ${finalPos.length}`);

    const tPos = [], fPos = [], error = [];
    if (finalPos.length > checkPos.length) {
        fs.writeFileSync(path.join(__dirname, temp, 'FinalPos_org_' + stage.tid + '_' + stage.order + '.json'), JSON.stringify(finalPos, '', '\t'));
        fs.writeFileSync(path.join(__dirname, temp, 'FinalPos_check_' + stage.tid + '_' + stage.order + '.json'), JSON.stringify(checkPos, '', '\t'));
        for (const cb of checkPos) {
            let [index, data] = findPos(finalPos, cb.pid);
            if (!data) error.push(`pos error: ${cb.pid}`);
            let check = false;
            while (data) {
                finalPos.splice(index, 1);
                if (!check && utils.checkNumEqual(data.contract_qty, cb.contract_qty) &&
                    utils.checkNumEqual(data.qc_qty, cb.qc_qty) ) {
                    check = true;
                    tPos.push(data);
                } else {
                    fPos.push(data);
                }
                [index, data] = findPos(finalPos, cb.pid);
            }
        }
        fs.writeFileSync(path.join(__dirname, temp, 'FinalPos_keep_' + stage.tid + '_' + stage.order + '.json'), JSON.stringify(tPos, '', '\t'));
        fs.writeFileSync(path.join(__dirname, temp, 'FinalPos_del_' + stage.tid + '_' + stage.order + '.json'), JSON.stringify(fPos, '', '\t'));
        const sqlList = getDelSql('zh_stage_pos_final_' + (stage.tid % 20), fPos);
        fs.writeFileSync(path.join(__dirname, temp, 'FinalPos_del_' + stage.tid + '_' + stage.order + '.sql'), sqlList.join('\n'));
        console.log('check: ' + checkPos.length + ' keep: ' + tPos.length + ' del: ' + fPos.length);
        if (error.length > 0) fs.writeFileSync(path.join(__dirname, temp, 'Pos_error_' + stage.tid + '_' + stage.order + '.log'), error.join('\n'));
        if (tPos.length !== checkPos.length) console.log('error tid:' + stage.tid + ' sid:' + stage.id + ' error: ' + error.length);
        for (const sl of sqlList) {
            console.log(sl);
            await querySql(sl, []);
        }
    }
};

const checkFinal = async function (tid, o) {
    const stage = await querySql(stageSql, [tid, o]);
    const preStage = o > 1 ? await querySql(stageSql, [tid, o - 1]) : null;

    //await checkBillsFinal(stage[0], preStage[0]);
    await checkPosFinal(stage[0], preStage[0]);
    pool.end();
};

//checkFinal(3057, [2, 3]);
checkFinal(3003, 8);