Calculation/db_script/bak/ledger_his.js

'use strict';

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

const fs = require('fs');
const path = require('path');
var util = require('util');
var logPath = path.join(__dirname, 'update_revise.log');
var logFile = fs.createWriteStream(logPath, { flags: 'a' });

console.log = function() {
    logFile.write(util.format.apply(null, arguments) + '\n');
    process.stdout.write(util.format.apply(null, arguments) + '\n');
};

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

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);
    }
};

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, valid) Values(?, ?, ?, ?, ?, ?, ?, ?)',
        [tender.project_id, tender.id, now, billsHis, posHis, revise.id, revise.corder, revise.valid]);

    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, withoutHisRevise = [];
        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_ledger_revise Set pre_his_id = ? Where id = ?', [preRevise.his_id, r.id]);
            }
            if (r.bills_file) {
                r.his_id = await saveReviseLedgerHis(t, r);
                if (r.valid) his_id = r.his_id;
                await querySql('Update zh_ledger_revise Set his_id = ? Where id = ?', [r.his_id, r.id]);
            } else {
                if (r.status !== 1) withoutHisRevise.push(r);

            }
            if (!his_id || t.measure_type === measureType.gcl.value) 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]);
                }
            }
            for (const r of withoutHisRevise) {
                await querySql('Update zh_ledger_revise Set pre_his_id = ?, his_id = ? Where id = ?', [his_id, his_id, r.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 doCompleteTender2 = 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) {
//         for (const r of revise) {
//             const preRevise = revise.find(x => { return x.status === audit.revise.status.checked && x.corder === r.corder - 1});
//             if (!preRevise && !r.pre_his_id) {
//                 await querySql('Update zh_ledger_revise Set pre_his_id = ? Where id = ?', [r.his_id, r.id]);
//             }
//         }
//     }
// };
//
// const doCompeletTest = async function() {
//     try {
//         const tenders = await querySql('Select * From zh_tender where ledger_status <> ?', [audit.ledger.status.uncheck]);
//         //const tenders = await querySql('Select * From zh_tender where id = ?', [3863]);
//         for (const t of tenders) {
//             await doCompleteTender2(t);
//         }
//     } catch (err) {
//         console.log(err);
//     }
//     pool.end();
// };

// doCompeletTest();