Calculation/db_script/ledger_his.js

'use strict';

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

const audit = require('../app/const/audit');

const mysql = require('mysql');
const oss = require('ali-oss');
const config = process.argv.splice(2)[0];
if (['local', 'uat', 'default'].indexOf(config) < 0) throw `参数错误: ${config}`;
const options = require(`../config/config.${config}`)({ baseDir: __dirname + '/app', root: __dirname, name: 'calc' });

const pool = mysql.createPool(options.mysql.client);
const ossOption = {
    bucket: options.oss.clients.his.bucket,
    accessKeyId: options.oss.default.accessKeyId,
    accessKeySecret: options.oss.default.accessKeySecret,
    endpoint: options.oss.default.endpoint,
    timeout: options.oss.default.timeout,
};
const ossClient = new oss(ossOption);

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

const getTableName = function(tender, table) {
    // switch(table) {
    //     case 'ledger': return 'zh_ledger_' + (tender.id % 10);
    //     case 'pos': return 'zh_pos_' + (tender.id % 20);
    //     case 'revise_bills': return 'zh_revise_bills_' + (tender.id % 10);
    //     case 'revise_pos': return 'zh_revise_pos_' + (tender.id % 20);
    // }
    return table.indexOf('pos') > 0 ? `zh_${table}_${tender.id % 20}` : `zh_${table}_${tender.id % 10}`;
};

const saveLedgerHis = async function(tender) {
    const now = new Date();
    const timestamp = (now).getTime();

    const billsHis = `${tender.project_id}/${tender.id}/ledger/bills${timestamp}.json`;
    const bills = await querySql('Select * From ?? where tender_id = ?', [getTableName(tender, 'ledger'), tender.id]);
    console.log(`saveBillsFile: ${billsHis}(${bills.length})`);
    await ossClient.put(options.hisOssPath + billsHis, Buffer.from(JSON.stringify(bills), 'utf8'));

    const posHis = `${tender.project_id}/${tender.id}/ledger/pos${timestamp}.json`;
    const pos = await querySql('Select * From ?? where tid = ?', [getTableName(tender, 'pos'), tender.id]);
    console.log(`savePosFile: ${posHis}(${pos.length})`);
    await ossClient.put(options.hisOssPath + posHis, Buffer.from(JSON.stringify(pos), 'utf8'));

    const result = await querySql('Insert Into zh_ledger_history(pid, tid, in_time, bills_file, pos_file) Values(?, ?, ?, ?, ?)',
        [tender.project_id, tender.id, now, billsHis, posHis]);

    if (!result) throw 'err';
    return result.insertId;
};

const saveReviseLedgerHis = async function (tender, revise) {
    const now = new Date();
    const billsHis = revise.bills_file;
    const posHis = revise.pos_file;

    const result = await querySql('Insert Into zh_ledger_history(pid, tid, in_time, bills_file, pos_file, rid, rorder) Values(?, ?, ?, ?, ?)',
        [tender.project_id, tender.id, now, billsHis, posHis, revise.id, revise.corder]);

    console.log(result);
    if (!result) throw 'err';
    return result.insertId;
};

const doCompleteTender = async function(t) {
    const revise = await querySql('Select * From zh_ledger_revise where tid = ? order By in_time asc', [t.id]);
    if (revise.length > 0) {
        let his_id;
        for (const r of revise) {
            const preRevise = revise.find(x => { return x.status === audit.revise.status.checked && x.corder === r.corder - 1});
            if (preRevise) {
                await querySql('Update zh_stage Set pre_his_id = ? Where id = ?', [preRevise.his_id, r.id]);
            }
            if (r.bills_file) {
                r.his_id = saveReviseLedgerHis(t, r);
                his_id = r.his_id;
            }
        }
        if (!his_id) his_id = await saveLedgerHis(t);
        await querySql('Update zh_tender Set his_id = ? Where id = ?', [his_id, t.id]);
        const stages = await querySql('Select * From zh_stage where tid = ? and status = ?', [t.id, audit.stage.status.checked]);
        for (const s of stages) {
            if (s.status === audit.stage.status.checked) {
                await querySql('Update zh_stage Set his_id = ? Where id = ?', [his_id, s.id]);
            }
        }
    } else {
        const ledgerHis = await saveLedgerHis(t);
        console.log(`saveLedgerHis: ${t.name}(${t.id})`);
        await querySql('Update zh_tender Set his_id = ? Where id = ?', [ledgerHis, t.id]);
        const stages = await querySql('Select * From zh_stage where tid = ? and status = ?', [t.id, audit.stage.status.checked]);
        for (const s of stages) {
            if (s.status === audit.stage.status.checked) {
                await querySql('Update zh_stage Set his_id = ? Where id = ?', [ledgerHis, s.id]);
            }
        }
    }
};

const doComplete = async function() {
    try {
        const tenders = await querySql('Select * From zh_tender where ledger_status <> ?', [audit.ledger.status.uncheck]);
        for (const t of tenders) {
            await doCompleteTender(t);
        }
    } catch (err) {
        console.log(err);
    }
    pool.end();
};
doComplete();

// const doCompeletTest = async function() {
//     try {
//         const t = await querySql('Select * From zh_tender where id = ?', [1003]);
//         await doCompleteTender(t[0]);
//     } catch (err) {
//         console.log(err);
//     }
//     pool.end();
// };
// doCompeletTest();