sub_project.js 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203
  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. await querySql('UPDATE zh_payment_tender SET spid = ? WHERE pid = ?', [subProject.id, project.id]);
  30. await querySql('UPDATE zh_payment_folder SET spid = ? WHERE pid = ?', [subProject.id, project.id]);
  31. const ppAudits = await querySql('SELECT * FROM zh_payment_permission_audit where pid = ?', [project.id]);
  32. // 添加用户payment_setting权限
  33. // 项目下所有用户都在子项目下新增用户权限
  34. console.log('Insert default sub_project user permission');
  35. for (const u of users) {
  36. if (manager && u.id === manager.id) continue;
  37. const sp_permission = { id: uuid.v4(), spid: subProject.id, pid: project.id, uid: u.id, self_category_level: u.self_category_level};
  38. if (ppAudits.length > 0) {
  39. const ppAudit = ppAudits.find(x => { return x.uid === u.id; });
  40. if (ppAudit) {
  41. const payment_permission = [1];
  42. const one_pp = ppAudit.permission_json ? JSON.parse(ppAudit.permission_json) : null;
  43. if (one_pp) {
  44. if (one_pp.admin) payment_permission.push(2);
  45. if (one_pp.view_all) payment_permission.push(3);
  46. }
  47. sp_permission.payment_permission = payment_permission.join(',');
  48. }
  49. }
  50. const [spSql, spSqlParam] = getInsertSql('zh_sub_project_permission', sp_permission);
  51. await querySql(spSql, spSqlParam);
  52. }
  53. };
  54. const copyColSet = async function(subProject) {
  55. console.log(`Copy sub_project ${subProject.name}(${subProject.id}) category`);
  56. const exist = await querySql('SELECT * FROM zh_project_col_set where pid = ? and spid = ?', [subProject.project_id, subProject.id]);
  57. if (exist.length > 0) return;
  58. const colSet = await querySql('SELECT * FROM zh_project_col_set where pid = ? and spid = ?', [subProject.project_id, '']);
  59. for (const cs of colSet) {
  60. const newCs = { pid: subProject.project_id, spid: subProject.id, info: cs.info };
  61. const [sql, sqlParam] = getInsertSql('zh_project_col_set', newCs);
  62. const insertResult = await querySql(sql, sqlParam);
  63. }
  64. };
  65. const doComplete = async function(code) {
  66. try {
  67. const filter = code ? ` where code = '${code}'` : '';
  68. const project = await querySql('Select * From zh_project' + filter);
  69. for (const p of project) {
  70. console.log(`Update Project ${p.code}(${p.id}):`);
  71. const existSubProj = await querySql('SELECT * FROM zh_sub_project where project_id = ? and is_folder = 0 and is_delete = 0;', [p.id]);
  72. if (existSubProj.length === 0) {
  73. console.log(`DELETE empty sub_project data`);
  74. // 删除旧数据(即使有数据,数据应该都是文件夹,应清理掉)
  75. await querySql('Update zh_sub_project SET is_delete = 1 where project_id = ?', [p.id]);
  76. // 创建默认数据
  77. await createDefaultSubProject(p);
  78. } else {
  79. console.log('Exist sub_project');
  80. }
  81. // 初始化所有subProject的项目配置 todo
  82. // 拷贝显示设置page_show, 功能设置fun_rela,决策大屏设置data_collect data_collect_pages
  83. // page_show可以后台设置,如果迁移至子项目下,后台已有的功能设置将作废,需要做针对子项目的项目设置
  84. // todo rpt_authority rpt_items rpt_level rpt_nature 是否拷贝?
  85. // 拷贝sql必须放在这里,不能放在update.sql,必须先执行上一步创建默认子项目
  86. console.log('Copy sub_project properties: page_show, fun_rela, data_collect, data_collect_pages, fun_set, payment_setting');
  87. 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; ');
  88. // 拷贝标段自定义类别设置
  89. const subProj = await querySql('SELECT * FROM zh_sub_project where project_id = ? and is_folder = 0 and is_delete = 0;', [p.id]);
  90. // 不使用Insert Into SELECT, 避免死锁
  91. const category = await querySql('SELECT * FROM zh_category where pid = ? and spid = ?', [p.id, '']);
  92. const categoryValue = await querySql('SELECT * FROM zh_category_value where pid = ? and spid = ?', [p.id, '']);
  93. for (const c of category) {
  94. c.value = categoryValue.filter(x => { return x.cid === c.id; });
  95. }
  96. const dataCollectAudits = await querySql('SELECT * FROM zh_datacollect_audit where pid = ?', [p.id]);
  97. for (const sp of subProj) {
  98. console.log(`Copy sub_project ${sp.name}(${sp.id}) category`);
  99. // 存在分类信息跳过 --- 防止脚本执行问题,需要重复执行
  100. const existCate = await querySql('SELECT * FROM zh_category where pid = ? and spid = ?', [p.id, sp.id]);
  101. if (existCate.length > 0) continue;
  102. const copyCategory = [];
  103. for (const cate of category) {
  104. const copyCate = { pid: cate.pid, spid: sp.id, name: cate.name, type: cate.type, level: cate.level };
  105. const [cateSql, cateSqlParam] = getInsertSql('zh_category', copyCate);
  106. const insertResult = await querySql(cateSql, cateSqlParam);
  107. copyCate.id = insertResult.insertId;
  108. copyCate.orgId = cate.id;
  109. copyCate.value = [];
  110. for (const value of cate.value) {
  111. const copyValue = { pid: value.pid, spid: sp.id, cid: insertResult.insertId, value: value.value, sort: value.sort};
  112. const [cateSql, cateSqlParam] = getInsertSql('zh_category_value', copyValue);
  113. const insertResultValue = await querySql(cateSql, cateSqlParam);
  114. copyValue.id = insertResultValue.insertId;
  115. copyValue.orgId = value.id;
  116. copyCate.value.push(copyValue);
  117. }
  118. copyCategory.push(copyCate);
  119. }
  120. const tenders = await querySql('SELECT * FROM zh_tender WHERE spid = ?', [sp.id]);
  121. for (const t of tenders) {
  122. const tCategory = t.category ? JSON.parse(t.category) : null;
  123. const newTCateGory = [];
  124. if (tCategory) {
  125. for (const tCate of tCategory) {
  126. const cate = copyCategory.find(x => { return x.orgId === tCate.cid; });
  127. if (!cate) continue;
  128. const value = cate.value.find(x => { return x.orgId === tCate.value; });
  129. if (!value) continue;
  130. newTCateGory.push({ cid: cate.id, value: value.id});
  131. }
  132. await querySql('UPDATE zh_tender SET category = ? WHERE id = ?', [JSON.stringify(newTCateGory), t.id]);
  133. }
  134. }
  135. if (dataCollectAudits.length > 0) {
  136. console.log(`Copy sub_project ${sp.name}(${sp.id}) datacollect_permission`);
  137. const updateAudits = [];
  138. const insertAudits = [];
  139. const spAudits = await querySql('SELECT * FROM zh_sub_project_permission where spid = ?', [sp.id]);
  140. for (const d of dataCollectAudits) {
  141. if (d.uid) {
  142. const spAudit = spAudits.find(x => { return x.uid === d.uid; });
  143. if (!spAudit) {
  144. const user = await querySql('SELECT * FROM zh_project_account where id = ?', [d.uid]);
  145. if (user.length === 0) continue;
  146. 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' });
  147. } else {
  148. updateAudits.push({ id: spAudit.id, datacollect_permission: '1' });
  149. }
  150. } else if (d.company_id) {
  151. const constructionUnits = await querySql('SELECT * FROM zh_construction_unit where id = ?', [d.company_id]);
  152. if (constructionUnits.length === 0) continue;
  153. const users = await querySql('SELECT * FROM zh_project_account where company_id = ?', [d.company_id]);
  154. for (const user of users) {
  155. const spAudit = spAudits.find(x => { return x.uid === user.id; });
  156. if (!spAudit) {
  157. if (insertAudits.find(x => { return x.uid === user.id; })) continue;
  158. insertAudits.push({ id: uuid.v4(), spid: sp.id, pid: p.id, uid: user.id, self_category_level: user.self_category_level, datacollect_permission: '1' });
  159. } else {
  160. if (updateAudits.find(x => { return x.id === spAudit.id; })) continue;
  161. updateAudits.push({ id: spAudit.id, datacollect_permission: '1' });
  162. }
  163. }
  164. }
  165. }
  166. if (insertAudits.length > 0) {
  167. for (const audit of insertAudits) {
  168. const [spSql, spSqlParam] = getInsertSql('zh_sub_project_permission', audit);
  169. await querySql(spSql, spSqlParam);
  170. }
  171. }
  172. if (updateAudits.length > 0) {
  173. for (const audit of updateAudits) {
  174. await querySql('UPDATE zh_sub_project_permission SET datacollect_permission = ? WHERE id = ?', ['1', audit.id]);
  175. }
  176. }
  177. }
  178. }
  179. for (const sp of subProj) {
  180. await copyColSet(sp);
  181. }
  182. console.log('END Update;');
  183. }
  184. } catch (err) {
  185. console.log(err);
  186. }
  187. BaseUtil.closePool();
  188. };
  189. const projectCode = process.argv[3];
  190. doComplete(projectCode);