Calculation/db_script/sub_project.js

/*
 修改脚本，请现在指定项目测试
 例如: node db_script/sub_project local T201711273363
 没有意外再全部执行

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


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 createDefaultSubProject = async function (project) {
    console.log('Insert deafult sub_project');
    const users = await querySql('SELECT * FROM zh_project_account where project_id = ?', [project.id]);
    const manager = users.find(x => { return x.is_admin; });
    const subProject = { id: uuid.v4(), project_id: project.id, tree_pid: '-1', tree_order: 1, tree_level: 1, name: project.name, user_id: manager ? manager.id : 0};
    const [sql, sqlParam] = getInsertSql('zh_sub_project', subProject);
    await querySql(sql, sqlParam);
    // 所有标段归属到改项目下
    await querySql('UPDATE zh_tender SET spid = ? WHERE project_id = ?', [subProject.id, project.id]);
    // 项目下所有用户都在子项目下新增用户权限
    console.log('Insert default sub_project user permission');
    for (const u of users) {
        if (manager && u.id === manager.id) continue;
        const sp_permission = { id: uuid.v4(), spid: subProject.id, pid: project.id, uid: u.id, self_category_level: u.self_category_level};
        const [spSql, spSqlParam] = getInsertSql('zh_sub_project_permission', sp_permission);
        await querySql(spSql, spSqlParam);
    }
};

const doComplete = async function(code) {
    try {
        const filter = code ? ` where code = '${code}'` : '';
        const project = await querySql('Select * From zh_project' + filter);
        for (const p of project) {
            console.log(`Update Project ${p.code}(${p.id}):`);
            const existSubProj = await querySql('SELECT * FROM zh_sub_project where project_id = ? and is_folder = 0 and is_delete = 0;', [p.id]);
            if (existSubProj.length === 0) {
                console.log(`DELETE empty sub_project data`);
                // 删除旧数据（即使有数据，数据应该都是文件夹，应清理掉）
                await querySql('Update zh_sub_project SET is_delete = 1 where project_id = ?', [p.id]);
                // 创建默认数据
                await createDefaultSubProject(p);
            } else {
                console.log('Exist sub_project');
            }

            // 初始化所有subProject的项目配置 todo
            // 拷贝显示设置page_show, 功能设置fun_rela，决策大屏设置data_collect data_collect_pages
            // page_show可以后台设置，如果迁移至子项目下，后台已有的功能设置将作废，需要做针对子项目的项目设置
            // todo rpt_authority rpt_items rpt_level rpt_nature 是否拷贝?
            // 拷贝sql必须放在这里，不能放在update.sql，必须先执行上一步创建默认子项目
            console.log('Copy sub_project properties: page_show, fun_rela, data_collect, data_collect_pages');
            await querySql('Update zh_sub_project sp LEFT JOIN zh_project p ON sp.project_id = p.id SET sp.page_show = p.page_show, sp.fun_rela = p.fun_rela, sp.data_collect = p.data_collect, sp.data_collect_pages = p.data_collect_pages WHERE sp.is_folder = 0 and is_delete = 0; ');
            // 拷贝标段自定义类别设置
            const subProj = await querySql('SELECT * FROM zh_sub_project where project_id = ? and is_folder = 0 and is_delete = 0;', [p.id]);
            // 不使用Insert Into SELECT, 避免死锁
            const category = await querySql('SELECT * FROM zh_category where pid = ? and spid = ?', [p.id, '']);
            const categoryValue = await querySql('SELECT * FROM zh_category_value where pid = ? and spid = ?', [p.id, '']);
            for (const c of category) {
                c.value = categoryValue.filter(x => { return x.cid === c.id; });
            }
            for (const sp of subProj) {
                console.log(`Copy sub_project ${sp.name}(${sp.id}) category`);
                // 存在分类信息跳过 --- 防止脚本执行问题，需要重复执行
                const existCate = await querySql('SELECT * FROM zh_category where pid = ? and spid = ?', [p.id, sp.id]);
                if (existCate.length > 0) continue;

                const copyCategory = [];
                for (const cate of category) {
                    const copyCate = { pid: cate.pid, spid: sp.id, name: cate.name, type: cate.type, level: cate.level };
                    const [cateSql, cateSqlParam] = getInsertSql('zh_category', copyCate);
                    const insertResult = await querySql(cateSql, cateSqlParam);
                    copyCate.id = insertResult.insertId;
                    copyCate.orgId = cate.id;
                    copyCate.value = [];
                    for (const value of cate.value) {
                        const copyValue = { pid: value.pid, spid: sp.id, cid: insertResult.insertId, value: value.value, sort: value.sort};
                        const [cateSql, cateSqlParam] = getInsertSql('zh_category_value', copyValue);
                        const insertResultValue = await querySql(cateSql, cateSqlParam);
                        copyValue.id = insertResultValue.insertId;
                        copyValue.orgId = value.id;
                        copyCate.value.push(copyValue);
                    }
                    copyCategory.push(copyCate);
                }

                const tenders = await querySql('SELECT * FROM zh_tender WHERE spid = ?', [sp.id]);
                for (const t of tenders) {
                    const tCategory = t.category ? JSON.parse(t.category) : null;
                    const newTCateGory = [];
                    if (tCategory) {
                        for (const tCate of tCategory) {
                            const cate = copyCategory.find(x => { return x.orgId === tCate.cid; });
                            if (!cate) continue;
                            const value = cate.value.find(x => { return x.orgId === tCate.value; });
                            if (!value) continue;
                            newTCateGory.push({ cid: cate.id, value: value.id});
                        }
                        await querySql('UPDATE zh_tender SET category = ? WHERE id = ?', [JSON.stringify(newTCateGory), t.id]);
                    }
                }

                // await querySql('INSERT INTO zh_category (pid, spid, name, type, level) SELECT pid, ?, name, type, level FROM zh_category WHERE pid = ? and spid = ?', [sp.id, p.id, '']);
                // await querySql('INSERT INTO zh_category_value (pid, spid, cid, value, sort) SELECT pid, ?, cid, value, sort FROM zh_category_value WHERE pid = ? and spid = ?', [sp.id, p.id, '']);
            }
            console.log('END Update;');
        }
    } catch (err) {
        console.log(err);
    }
    BaseUtil.closePool();
};
const projectCode = process.argv[3];
doComplete(projectCode);