Calculation/db_script/sub_project_permission_financial.js

/*
 修改脚本，请现在指定spid测试
 例如: node db_script/sub_project local 3fcee213-92c9-41de-8f5a-b896c986311a
 没有意外再全部执行

 所有修改均应考虑脚本二次执行时的兼容，应检查是否已执行过，避免在生产环境运行时出现问题后需要二次执行，参见自定义分类脚本
  */


const BaseUtil = require('./baseUtils');
const querySql = BaseUtil.querySql;
const uuid = require('node-uuid');

const getInsertSql = function (tableName, data) {
    const column = [], query = [], value = [];
    for (const prop in data) {
        column.push(prop);
        query.push('?');
        value.push(data[prop]);
    }
    return [`INSERT INTO ${tableName} (${column.join(',')}) VALUES (${query.join(',')})`, value];
};

const doComplete = async function(spid) {
    try {
        const filter = spid ? ` where spid = '${spid}'` : '';
        const audits = await querySql('Select * From zh_financial_audit' + filter);
        for (const a of audits) {
            const existSubProj = await querySql('SELECT * FROM zh_sub_project where id = ? and is_folder = 0 and is_delete = 0;', [a.spid]);
            if (existSubProj.length === 0) {
                continue;
            } else {
                const existSubProjPermission = await querySql('SELECT * FROM zh_sub_project_permission WHERE spid = ? and uid = ?', [existSubProj[0].id, a.uid]);
                const fund_trans_permission = [];
                if (a.permission_transfer_show) {
                    fund_trans_permission.push(1);
                }
                if (a.permission_transfer_add) {
                    fund_trans_permission.push(2);
                }
                if (a.permission_transfer_file) {
                    fund_trans_permission.push(3);
                }
                const fund_pay_permission = [];
                if (a.permission_pay_show) {
                    fund_pay_permission.push(1);
                }
                if (a.permission_pay_file) {
                    fund_pay_permission.push(3);
                }
                if (fund_trans_permission.length > 0 || fund_pay_permission.length > 0) {
                    console.log('Copy sub_project_permission: ' + a.uid + ' ' + a.spid + ' ');
                    if (existSubProjPermission.length === 0) {
                        const sp_permission = { id: uuid.v4(), spid: existSubProj[0].id, pid: existSubProj[0].project_id, uid: a.uid, fund_trans_permission: fund_trans_permission.length > 0 ? fund_trans_permission.join(',') : '', fund_pay_permission: fund_pay_permission.length > 0 ? fund_pay_permission.join(',') : '' };
                        const [spSql, spSqlParam] = getInsertSql('zh_sub_project_permission', sp_permission);
                        await querySql(spSql, spSqlParam);
                    } else {
                        await querySql('Update zh_sub_project_permission SET fund_trans_permission = ?, fund_pay_permission = ? WHERE id = ?; ', [fund_trans_permission.length > 0 ? fund_trans_permission.join(',') : '', fund_pay_permission.length > 0 ? fund_pay_permission.join(',') : '', existSubProjPermission[0].id]);
                    }
                }
            }
            console.log('END Update;');
        }
    } catch (err) {
        console.log(err);
    }
    BaseUtil.closePool();
};
const spid = process.argv[3];
doComplete(spid);