123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127 |
- /*
- 修改脚本,请现在指定项目测试
- 例如: 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);
|