sub_project.js 7.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127
  1. /*
  2. 修改脚本,请现在指定项目测试
  3. 例如: node db_script/sub_project local T201711273363
  4. 没有意外再全部执行
  5. 所有修改均应考虑脚本二次执行时的兼容,应检查是否已执行过,避免在生产环境运行时出现问题后需要二次执行,参见自定义分类脚本
  6. */
  7. const BaseUtil = require('./baseUtils');
  8. const querySql = BaseUtil.querySql;
  9. const uuid = require('node-uuid');
  10. const getInsertSql = function (tableName, data) {
  11. const column = [], query = [], value = [];
  12. for (const prop in data) {
  13. column.push(prop);
  14. query.push('?');
  15. value.push(data[prop]);
  16. }
  17. return [`INSERT INTO ${tableName} (${column.join(',')}) VALUES (${query.join(',')})`, value]
  18. };
  19. const createDefaultSubProject = async function (project) {
  20. console.log('Insert deafult sub_project');
  21. const users = await querySql('SELECT * FROM zh_project_account where project_id = ?', [project.id]);
  22. const manager = users.find(x => { return x.is_admin; });
  23. 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};
  24. const [sql, sqlParam] = getInsertSql('zh_sub_project', subProject);
  25. await querySql(sql, sqlParam);
  26. // 所有标段归属到改项目下
  27. await querySql('UPDATE zh_tender SET spid = ? WHERE project_id = ?', [subProject.id, project.id]);
  28. // 项目下所有用户都在子项目下新增用户权限
  29. console.log('Insert default sub_project user permission');
  30. for (const u of users) {
  31. if (manager && u.id === manager.id) continue;
  32. const sp_permission = { id: uuid.v4(), spid: subProject.id, pid: project.id, uid: u.id, self_category_level: u.self_category_level};
  33. const [spSql, spSqlParam] = getInsertSql('zh_sub_project_permission', sp_permission);
  34. await querySql(spSql, spSqlParam);
  35. }
  36. };
  37. const doComplete = async function(code) {
  38. try {
  39. const filter = code ? ` where code = '${code}'` : '';
  40. const project = await querySql('Select * From zh_project' + filter);
  41. for (const p of project) {
  42. console.log(`Update Project ${p.code}(${p.id}):`);
  43. const existSubProj = await querySql('SELECT * FROM zh_sub_project where project_id = ? and is_folder = 0 and is_delete = 0;', [p.id]);
  44. if (existSubProj.length === 0) {
  45. console.log(`DELETE empty sub_project data`);
  46. // 删除旧数据(即使有数据,数据应该都是文件夹,应清理掉)
  47. await querySql('Update zh_sub_project SET is_delete = 1 where project_id = ?', [p.id]);
  48. // 创建默认数据
  49. await createDefaultSubProject(p);
  50. } else {
  51. console.log('Exist sub_project');
  52. }
  53. // 初始化所有subProject的项目配置 todo
  54. // 拷贝显示设置page_show, 功能设置fun_rela,决策大屏设置data_collect data_collect_pages
  55. // page_show可以后台设置,如果迁移至子项目下,后台已有的功能设置将作废,需要做针对子项目的项目设置
  56. // todo rpt_authority rpt_items rpt_level rpt_nature 是否拷贝?
  57. // 拷贝sql必须放在这里,不能放在update.sql,必须先执行上一步创建默认子项目
  58. console.log('Copy sub_project properties: page_show, fun_rela, data_collect, data_collect_pages');
  59. 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; ');
  60. // 拷贝标段自定义类别设置
  61. const subProj = await querySql('SELECT * FROM zh_sub_project where project_id = ? and is_folder = 0 and is_delete = 0;', [p.id]);
  62. // 不使用Insert Into SELECT, 避免死锁
  63. const category = await querySql('SELECT * FROM zh_category where pid = ? and spid = ?', [p.id, '']);
  64. const categoryValue = await querySql('SELECT * FROM zh_category_value where pid = ? and spid = ?', [p.id, '']);
  65. for (const c of category) {
  66. c.value = categoryValue.filter(x => { return x.cid === c.id; });
  67. }
  68. for (const sp of subProj) {
  69. console.log(`Copy sub_project ${sp.name}(${sp.id}) category`);
  70. // 存在分类信息跳过 --- 防止脚本执行问题,需要重复执行
  71. const existCate = await querySql('SELECT * FROM zh_category where pid = ? and spid = ?', [p.id, sp.id]);
  72. if (existCate.length > 0) continue;
  73. const copyCategory = [];
  74. for (const cate of category) {
  75. const copyCate = { pid: cate.pid, spid: sp.id, name: cate.name, type: cate.type, level: cate.level };
  76. const [cateSql, cateSqlParam] = getInsertSql('zh_category', copyCate);
  77. const insertResult = await querySql(cateSql, cateSqlParam);
  78. copyCate.id = insertResult.insertId;
  79. copyCate.orgId = cate.id;
  80. copyCate.value = [];
  81. for (const value of cate.value) {
  82. const copyValue = { pid: value.pid, spid: sp.id, cid: insertResult.insertId, value: value.value, sort: value.sort};
  83. const [cateSql, cateSqlParam] = getInsertSql('zh_category_value', copyValue);
  84. const insertResultValue = await querySql(cateSql, cateSqlParam);
  85. copyValue.id = insertResultValue.insertId;
  86. copyValue.orgId = value.id;
  87. copyCate.value.push(copyValue);
  88. }
  89. copyCategory.push(copyCate);
  90. }
  91. const tenders = await querySql('SELECT * FROM zh_tender WHERE spid = ?', [sp.id]);
  92. for (const t of tenders) {
  93. const tCategory = t.category ? JSON.parse(t.category) : null;
  94. const newTCateGory = [];
  95. if (tCategory) {
  96. for (const tCate of tCategory) {
  97. const cate = copyCategory.find(x => { return x.orgId === tCate.cid; });
  98. if (!cate) continue;
  99. const value = cate.value.find(x => { return x.orgId === tCate.value; });
  100. if (!value) continue;
  101. newTCateGory.push({ cid: cate.id, value: value.id});
  102. }
  103. await querySql('UPDATE zh_tender SET category = ? WHERE id = ?', [JSON.stringify(newTCateGory), t.id]);
  104. }
  105. }
  106. // 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, '']);
  107. // 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, '']);
  108. }
  109. console.log('END Update;');
  110. }
  111. } catch (err) {
  112. console.log(err);
  113. }
  114. BaseUtil.closePool();
  115. };
  116. const projectCode = process.argv[3];
  117. doComplete(projectCode);