Calculation/db_script/stage-change-final.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date
 * @version
 */
const audit = require('../app/const/audit');

const mysql = require('mysql');

const pool = mysql.createPool({
    // host
    host: '192.168.1.76',
    // 端口号
    port: '3306',
    // 用户名
    user: 'zh_dev',
    // 密码
    password: 'zongheng2019',
    // 数据库名
    database: 'calculation',
    // database: 'calc_copy_pro',
});

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({"err": err, "rows": rows, "fields": fields});
                });
            }
        });
    });
};

const timesLen = 100;
const filterLastestData = function(data, keyFields) {
    const dataIndex = {};
    for (const d of data) {
        let key = 'd';
        for (const kf of keyFields) {
            key = key + '.' + (d[kf] || '');
        }

        const di = dataIndex[key];
        if (di) {
            if ((di.times * timesLen + di.order) < (d.times * timesLen + d.order)) dataIndex[key] = d;
        } else {
            dataIndex[key] = d;
        }
    }
    const result = [];
    for (const prop in dataIndex) {
        result.push(dataIndex[prop]);
    }
    return result;
};

const doComplete = async function () {
    const tenders = await querySql('Select * From zh_tender where ledger_status = ?', [audit.ledger.status.checked]);
    for (const t of tenders.rows) {
        const stages = await querySql('Select * From zh_stage where tid = ? and status = ?', [t.id, audit.stage.status.checked]);
        for (const s of stages.rows) {
            const stageChange = await querySql('Select * From zh_stage_change where sid = ?', [s.id]);
            const validStageChange = filterLastestData(stageChange.rows, ['lid', 'pid', 'cid', 'cbid']);

            const stageChangeFinal = [];
            for (const vsc of validStageChange) {
                if (!vsc.qty) continue;

                stageChangeFinal.push([
                    vsc.tid, vsc.sid,
                    vsc.lid, vsc.pid, vsc.cid, vsc.cbid,
                    vsc.qty,
                ]);
            }
            const result = await querySql('Insert Into zh_stage_change_final(tid, sid, lid, pid, cid, cbid, qty) Values ?', [stageChangeFinal]);
        }
    }
    pool.end();
};

doComplete();