123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203 |
- /*
- 修改脚本,请现在指定项目测试
- 例如: 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]);
- // 所有支付审批标段归属到该项目下
- await querySql('UPDATE zh_payment_tender SET spid = ? WHERE pid = ?', [subProject.id, project.id]);
- await querySql('UPDATE zh_payment_folder SET spid = ? WHERE pid = ?', [subProject.id, project.id]);
- const ppAudits = await querySql('SELECT * FROM zh_payment_permission_audit where pid = ?', [project.id]);
- // 添加用户payment_setting权限
- // 项目下所有用户都在子项目下新增用户权限
- 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};
- if (ppAudits.length > 0) {
- const ppAudit = ppAudits.find(x => { return x.uid === u.id; });
- if (ppAudit) {
- const payment_permission = [1];
- const one_pp = ppAudit.permission_json ? JSON.parse(ppAudit.permission_json) : null;
- if (one_pp) {
- if (one_pp.admin) payment_permission.push(2);
- if (one_pp.view_all) payment_permission.push(3);
- }
- sp_permission.payment_permission = payment_permission.join(',');
- }
- }
- const [spSql, spSqlParam] = getInsertSql('zh_sub_project_permission', sp_permission);
- await querySql(spSql, spSqlParam);
- }
- };
- const copyColSet = async function(subProject) {
- console.log(`Copy sub_project ${subProject.name}(${subProject.id}) category`);
- const exist = await querySql('SELECT * FROM zh_project_col_set where pid = ? and spid = ?', [subProject.project_id, subProject.id]);
- if (exist.length > 0) return;
- const colSet = await querySql('SELECT * FROM zh_project_col_set where pid = ? and spid = ?', [subProject.project_id, '']);
- for (const cs of colSet) {
- const newCs = { pid: subProject.project_id, spid: subProject.id, info: cs.info };
- const [sql, sqlParam] = getInsertSql('zh_project_col_set', newCs);
- const insertResult = await querySql(sql, sqlParam);
- }
- };
- 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, fun_set, payment_setting');
- 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, sp.fun_set = p.fun_set, sp.payment_setting = p.payment_setting 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; });
- }
- const dataCollectAudits = await querySql('SELECT * FROM zh_datacollect_audit where pid = ?', [p.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]);
- }
- }
- if (dataCollectAudits.length > 0) {
- console.log(`Copy sub_project ${sp.name}(${sp.id}) datacollect_permission`);
- const updateAudits = [];
- const insertAudits = [];
- const spAudits = await querySql('SELECT * FROM zh_sub_project_permission where spid = ?', [sp.id]);
- for (const d of dataCollectAudits) {
- if (d.uid) {
- const spAudit = spAudits.find(x => { return x.uid === d.uid; });
- if (!spAudit) {
- const user = await querySql('SELECT * FROM zh_project_account where id = ?', [d.uid]);
- if (user.length === 0) continue;
- insertAudits.push({ id: uuid.v4(), spid: sp.id, pid: p.id, uid: d.uid, self_category_level: user[0].self_category_level, datacollect_permission: '1' });
- } else {
- updateAudits.push({ id: spAudit.id, datacollect_permission: '1' });
- }
- } else if (d.company_id) {
- const constructionUnits = await querySql('SELECT * FROM zh_construction_unit where id = ?', [d.company_id]);
- if (constructionUnits.length === 0) continue;
- const users = await querySql('SELECT * FROM zh_project_account where company_id = ?', [d.company_id]);
- for (const user of users) {
- const spAudit = spAudits.find(x => { return x.uid === user.id; });
- if (!spAudit) {
- if (insertAudits.find(x => { return x.uid === user.id; })) continue;
- insertAudits.push({ id: uuid.v4(), spid: sp.id, pid: p.id, uid: user.id, self_category_level: user.self_category_level, datacollect_permission: '1' });
- } else {
- if (updateAudits.find(x => { return x.id === spAudit.id; })) continue;
- updateAudits.push({ id: spAudit.id, datacollect_permission: '1' });
- }
- }
- }
- }
- if (insertAudits.length > 0) {
- for (const audit of insertAudits) {
- const [spSql, spSqlParam] = getInsertSql('zh_sub_project_permission', audit);
- await querySql(spSql, spSqlParam);
- }
- }
- if (updateAudits.length > 0) {
- for (const audit of updateAudits) {
- await querySql('UPDATE zh_sub_project_permission SET datacollect_permission = ? WHERE id = ?', ['1', audit.id]);
- }
- }
- }
- }
- for (const sp of subProj) {
- await copyColSet(sp);
- }
- console.log('END Update;');
- }
- } catch (err) {
- console.log(err);
- }
- BaseUtil.closePool();
- };
- const projectCode = process.argv[3];
- doComplete(projectCode);
|