priceEmpty.js 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574
  1. // 空数据表
  2. const EMPTY_BOOK = (() => {
  3. const setting = {
  4. header: [
  5. { headerName: '主从对应码', headerWidth: 100, dataCode: 'code', dataType: 'String', hAlign: 'left', vAlign: 'center', formatter: "@" },
  6. { headerName: '别名编码', headerWidth: 70, dataCode: 'classCode', dataType: 'String', hAlign: 'left', vAlign: 'center', formatter: "@" },
  7. { headerName: '计算式', headerWidth: 100, dataCode: 'expString', dataType: 'String', hAlign: 'left', vAlign: 'center' },
  8. { headerName: '材料名称', headerWidth: 275, dataCode: 'name', dataType: 'String', hAlign: 'left', vAlign: 'center' },
  9. { headerName: '规格型号', headerWidth: 180, dataCode: 'specs', dataType: 'String', hAlign: 'left', vAlign: 'center' },
  10. { headerName: '单位', headerWidth: 80, dataCode: 'unit', dataType: 'String', hAlign: 'center', vAlign: 'center' },
  11. ],
  12. };
  13. // 初始化表格
  14. const workBookObj = {
  15. book: null,
  16. sheet: null,
  17. };
  18. const buildWorkBook = () => {
  19. workBookObj.book = initSheet($('.empty-spread')[0], setting);
  20. workBookObj.book.options.allowUserDragDrop = true;
  21. workBookObj.book.options.allowUserDragFill = true;
  22. workBookObj.book.options.allowExtendPasteRange = false;
  23. lockUtil.lockSpreads([workBookObj.book], locked);
  24. workBookObj.sheet = workBookObj.book.getSheet(0);
  25. }
  26. // 总数据(数据没合并前)
  27. let totalData = [];
  28. let totalMap = {};
  29. // 表格显示的数据(合并后)
  30. const cache = [];
  31. const getCompareKey = (item) => {
  32. const props = ['name', 'specs', 'unit'];
  33. return props.map(prop => item[prop] ? item[prop].trim() : '').join('@');
  34. }
  35. const setTotalMap = (items) => {
  36. totalMap = {};
  37. items.forEach(item => {
  38. const key = getCompareKey(item);
  39. if (totalMap[key]) {
  40. totalMap[key].push(item);
  41. } else {
  42. totalMap[key] = [item];
  43. }
  44. })
  45. }
  46. // 获取表格数据,汇总空数据,多地区可能存在相同材料,按名称规格单位做筛选,重复材料仅显示一条即可
  47. const getTableData = (items) => {
  48. const map = {};
  49. items.forEach(item => {
  50. const key = getCompareKey(item);
  51. if (!map[key]) {
  52. map[key] = { ...item };
  53. }
  54. });
  55. return Object.values(map);
  56. }
  57. // 根据表格数据,获取实际信息价数据(一对多)
  58. const getItemsFromTableItem = (item) => {
  59. const key = getCompareKey(item);
  60. return totalMap[key] || [];
  61. }
  62. // 获取材料关键字: 名称 规格
  63. const getKeyword = (item) => {
  64. // return item ? `${item.name} ${item.specs}` : '';
  65. return item ? `${item.name}` : '';
  66. }
  67. // 改变关键字
  68. const changeKeyword = (item) => {
  69. const keyword = getKeyword(item);
  70. $('#recommend-search').val(keyword);
  71. if (!keyword) {
  72. RECOMMEND_BOOK.clear();
  73. } else {
  74. RECOMMEND_BOOK.loadRecommendData(keyword);
  75. }
  76. }
  77. // ai填值缓存,用于ai填值报错时,可以从当前报错处开始匹配,而不用重头开始匹配
  78. let aiMatchCache = null;
  79. // 清空
  80. function clear() {
  81. cache.length = 0;
  82. workBookObj.sheet.setRowCount(0);
  83. aiMatchCache = null;
  84. }
  85. let curRow = 0;
  86. // 初始化数据
  87. async function initData() {
  88. clear();
  89. curRow = 0;
  90. $.bootstrapLoading.start();
  91. try {
  92. const matchAll = $('#match-all-input')[0].checked;
  93. const areaID = matchAll ? '' : AREA_BOOK.curArea?.ID;
  94. totalData = await ajaxPost('/priceInfo/getPriceEmptyData', { libID, compilationID, areaID }, 1000 * 60 * 10);
  95. setTotalMap(totalData);
  96. const tableData = getTableData(totalData);
  97. cache.push(...tableData)
  98. showData(workBookObj.sheet, cache, setting.header);
  99. changeKeyword(cache[0]);
  100. } catch (err) {
  101. clear();
  102. alert(err);
  103. } finally {
  104. $.bootstrapLoading.end();
  105. }
  106. }
  107. // 编辑处理
  108. async function handleEdit(changedCells, diffMap, needRefresh, saveAll) {
  109. const postData = []; // 请求用
  110. // 更新缓存用
  111. const updateData = [];
  112. const deleteData = [];
  113. const insertData = [];
  114. try {
  115. changedCells.forEach(({ row }) => {
  116. if (cache[row]) {
  117. const rowData = getRowData(workBookObj.sheet, row, setting.header);
  118. if (Object.keys(rowData).length) { // 还有数据,更新
  119. let diffData;
  120. if (diffMap) {
  121. diffData = diffMap[row];
  122. } else {
  123. diffData = saveAll ? getRowAllData(rowData, setting.header) : getRowDiffData(rowData, cache[row], setting.header);
  124. }
  125. if (diffData) {
  126. // 改一行, 实际可能是改多行,表格一行数据是多行合并显示的
  127. const items = getItemsFromTableItem(cache[row]);
  128. items.forEach(item => {
  129. // 只有珠海建筑才更新计算式
  130. const updateObj = { ...diffData };
  131. const area = AREA_BOOK.cache.find(areaItem => areaItem.ID === item.areaID);
  132. if (diffMap) {
  133. delete updateObj.expString;
  134. delete diffData.expString;
  135. }
  136. postData.push({ type: UpdateType.UPDATE, ID: item.ID, areaID: area.ID, compilationID, period: curLibPeriod, data: updateObj });
  137. });
  138. updateData.push({ row, data: diffData });
  139. }
  140. } else { // 该行无数据了,删除
  141. const items = getItemsFromTableItem(cache[row]);
  142. items.forEach(item => {
  143. const area = AREA_BOOK.cache.find(areaItem => areaItem.ID === item.areaID);
  144. postData.push({ type: UpdateType.DELETE, areaID: area.ID, compilationID, period: curLibPeriod, ID: item.ID });
  145. });
  146. deleteData.push(cache[row]);
  147. }
  148. }
  149. });
  150. if (postData.length) {
  151. if (!saveAll) {
  152. $.bootstrapLoading.start();
  153. }
  154. await ajaxPost('/priceInfo/editPriceData', { postData }, TIME_OUT);
  155. // 更新缓存,先更新然后删除,最后再新增,防止先新增后缓存数据的下标与更新、删除数据的下标对应不上
  156. updateData.forEach(item => {
  157. // 更新总表
  158. const curItem = cache[item.row];
  159. const compareKey = getCompareKey(curItem);
  160. const totalItems = totalMap[compareKey];
  161. if (totalItems) {
  162. const newCompareKey = getCompareKey({ ...curItem, ...item.data });
  163. totalItems.forEach(totalItem => {
  164. Object.assign(totalItem, item.data);
  165. });
  166. if (newCompareKey !== compareKey) {
  167. totalMap[newCompareKey] = totalItems;
  168. delete totalMap[compareKey];
  169. }
  170. }
  171. // 更新表格缓存
  172. Object.assign(cache[item.row], item.data);
  173. });
  174. deleteData.forEach(item => {
  175. // 更新总表
  176. const compareKey = getCompareKey(item);
  177. const totalItems = totalMap[compareKey];
  178. if (totalItems) {
  179. const totalItemIDs = totalItems.map(item => item.ID);
  180. totalData = totalData.filter(totalItem => !totalItemIDs.includes(totalItem));
  181. delete totalMap[compareKey];
  182. }
  183. // 更新表格缓存
  184. const index = cache.indexOf(item);
  185. if (index >= 0) {
  186. cache.splice(index, 1);
  187. }
  188. });
  189. insertData.forEach(item => cache.push(item));
  190. if (deleteData.length || insertData.length || needRefresh) {
  191. showData(workBookObj.sheet, cache, setting.header);
  192. }
  193. if (!saveAll) {
  194. $.bootstrapLoading.end();
  195. }
  196. CLASS_BOOK.reload();
  197. }
  198. } catch (err) {
  199. // 恢复各单元格数据
  200. showData(workBookObj.sheet, cache, setting.header);
  201. $.bootstrapLoading.end();
  202. }
  203. }
  204. // 跟新行的编号、编码编码
  205. async function updateRowCode(code, classCode, expString) {
  206. const item = cache[curRow];
  207. if (!item) {
  208. return;
  209. }
  210. const diffData = { code, classCode, expString };
  211. await handleEdit([{ row: curRow }], { [curRow]: diffData }, true);
  212. }
  213. const bindEvent = () => {
  214. workBookObj.sheet.bind(GC.Spread.Sheets.Events.ValueChanged, function (e, info) {
  215. const changedCells = [{ row: info.row }];
  216. handleEdit(changedCells);
  217. });
  218. workBookObj.sheet.bind(GC.Spread.Sheets.Events.SelectionChanged, function (e, info) {
  219. const row = info.newSelections && info.newSelections[0] ? info.newSelections[0].row : 0;
  220. if (curRow !== row) {
  221. const item = cache[row];
  222. changeKeyword(item);
  223. }
  224. curRow = row;
  225. });
  226. workBookObj.sheet.bind(GC.Spread.Sheets.Events.RangeChanged, function (e, info) {
  227. const changedRows = [];
  228. let preRow;
  229. info.changedCells.forEach(({ row }) => {
  230. if (row !== preRow) {
  231. changedRows.push({ row });
  232. }
  233. preRow = row;
  234. });
  235. handleEdit(changedRows);
  236. });
  237. }
  238. // 将空表的表格保存至总表
  239. const saveInSummary = async () => {
  240. const documents = [];
  241. const removeIDs = [];
  242. cache.filter(item => item.code && item.classCode).forEach(item => {
  243. removeIDs.push(item.ID);
  244. documents.push({
  245. ID: uuid.v1(),
  246. code: item.code ? item.code.trim() : '',
  247. classCode: item.classCode ? item.classCode.trim() : '',
  248. expString: item.expString ? item.expString.trim() : '',
  249. name: item.name ? item.name.trim() : '',
  250. specs: item.specs ? item.specs.trim() : '',
  251. unit: item.unit ? item.unit.trim() : '',
  252. });
  253. });
  254. if (!documents.length) {
  255. alert('不存在可保存数据');
  256. return;
  257. }
  258. console.log(documents);
  259. try {
  260. $.bootstrapLoading.progressStart('保存至总表', true);
  261. $("#progress_modal_body").text('正在保存至总表,请稍后...');
  262. await ajaxPost('/priceInfoSummary/saveInSummary', { documents }, 1000 * 60 * 5);
  263. setTimeout(() => {
  264. $.bootstrapLoading.progressEnd();
  265. alert('保存成功');
  266. const filterCache = cache.filter(item => !removeIDs.includes(item.ID));
  267. cache.length = 0;
  268. cache.push(...filterCache);
  269. showData(workBookObj.sheet, cache, setting.header);
  270. }, 1000);
  271. } catch (error) {
  272. setTimeout(() => {
  273. $.bootstrapLoading.progressEnd();
  274. }, 500);
  275. console.log(error);
  276. alert(error);
  277. }
  278. }
  279. // 获取最大别名编码
  280. const getMaxClassCode = (priceInfoSummary) => {
  281. let maxClassCode = '';
  282. let maxClassNumber = 0;
  283. priceInfoSummary.forEach(item => {
  284. if (!item.classCode) {
  285. return;
  286. }
  287. const numMatch = item.classCode.match(/\d+/);
  288. if (numMatch && +numMatch[0] > maxClassNumber) {
  289. maxClassNumber = +numMatch[0];
  290. maxClassCode = item.classCode;
  291. }
  292. });
  293. // return { maxClassNumber, maxClassCode };
  294. return maxClassCode;
  295. }
  296. // 在最大别名编码基础上+1;
  297. const getNewMaxClassCode = (maxClassCode) => {
  298. const numMatch = maxClassCode.match(/\d+/);
  299. if (!numMatch) {
  300. return 1;
  301. }
  302. const maxNumber = +numMatch[0];
  303. let newMaxClassCode = String(maxNumber + 1);
  304. // 补齐的位数
  305. const pattern = numMatch[0].length - newMaxClassCode.length;
  306. if (pattern > 0) {
  307. for (let i = 0; i < pattern; i++) {
  308. newMaxClassCode = `0${newMaxClassCode}`;
  309. }
  310. }
  311. return newMaxClassCode;
  312. }
  313. const getFourCode = (code) => {
  314. if (!code) {
  315. return '';
  316. }
  317. return code.substring(0, 4);
  318. }
  319. const getAIMatchData = (summaryGroupMap, noCodeSummary) => {
  320. if (aiMatchCache && aiMatchCache.changedCells.length && aiMatchCache.curIndex > 0) {
  321. return aiMatchCache;
  322. }
  323. const curPercent = 0;
  324. const curIndex = 0;
  325. const totalRows = workBookObj.sheet.getRowCount();
  326. const changedCells = [];
  327. const noMatchRows = []; // 没有匹配、ai没有命中的行,后续需要自动生成别名编码(最大的别名编码+1)
  328. for (let i = 0; i < totalRows; i++) {
  329. const rowData = getRowData(workBookObj.sheet, i, setting.header);
  330. // const code = rowData.code || '';
  331. const code = getFourCode(rowData.code);
  332. const toMatchSummary = code ? summaryGroupMap[code] || [] : noCodeSummary;
  333. if (toMatchSummary.length) {
  334. changedCells.push({ row: i });
  335. } else {
  336. noMatchRows.push(i);
  337. }
  338. }
  339. return {
  340. curPercent,
  341. curIndex,
  342. changedCells,
  343. noMatchRows,
  344. }
  345. }
  346. // ai填值
  347. const aiMatch = async () => {
  348. let percent = 0;
  349. let curIndex = 0;
  350. let noMatchRows = [];
  351. let changedCells = [];
  352. try {
  353. // 获取信息价总表
  354. const priceInfoSummary = await ajaxPost('/priceInfoSummary/getData', {}, 1000 * 60 * 5);
  355. const summaryGroupMap = _.groupBy(priceInfoSummary, item => getFourCode(item.code));
  356. const noCodeSummary = priceInfoSummary.filter(item => !item.code);
  357. const aiMatchData = getAIMatchData(summaryGroupMap, noCodeSummary);
  358. percent = aiMatchData.curPercent;
  359. curIndex = aiMatchData.curIndex;
  360. changedCells = aiMatchData.changedCells;
  361. noMatchRows = aiMatchData.noMatchRows;
  362. if (!changedCells.length) {
  363. return;
  364. }
  365. const classCodeCol = setting.header.findIndex(h => h.dataCode === 'classCode');
  366. const expStringCol = setting.header.findIndex(h => h.dataCode === 'expString');
  367. // const chunks = _.chunk(changedCells, 20);
  368. const chunks = _.chunk(changedCells, 1); // 只能一条一条匹配改成,否则经常ai服务经常挂
  369. $.bootstrapLoading.progressStart('AI填值', false);
  370. $('#progress_modal_body').text(`正在进行AI填值,请稍后${curIndex + 1}/${chunks.length}...`);
  371. await setTimeoutSync(500);
  372. const matchResCache = {};
  373. // 分块进行ai匹配
  374. const step = 100 / (chunks.length || 1);
  375. for (let i = curIndex; i < chunks.length; i++) {
  376. curIndex = i;
  377. const chunk = chunks[i];
  378. const listA = [];
  379. const listB = [];
  380. const summaryData = [];
  381. chunk.forEach(item => {
  382. const rowData = getRowData(workBookObj.sheet, item.row, setting.header);
  383. listA.push(`${rowData.name || ''} ${rowData.specs}`);
  384. const code = getFourCode(rowData.code);
  385. const toMatchSummary = code ? summaryGroupMap[code] || [] : noCodeSummary;
  386. summaryData.push(toMatchSummary);
  387. const summaryKeys = toMatchSummary.map(summary => `${summary.name || ''} ${summary.specs || ''}`);
  388. listB.push([...new Set(summaryKeys)]);
  389. });
  390. const test = listB.map(item => item.length);
  391. console.log(test);
  392. const matchRes = matchResCache[listA[0]] ? matchResCache[listA[0]] : await ajaxPost('/priceInfoSummary/aiMatch', { listA, listB }, 1000 * 60 * 5);
  393. matchResCache[listA[0]] = matchRes;
  394. // 填匹配值到表格,不实时保存,因为需要人工核查
  395. workBookObj.sheet.suspendEvent();
  396. workBookObj.sheet.suspendPaint();
  397. matchRes.forEach((item, index) => {
  398. const firstMatch = item[0];
  399. const chunkItem = chunk[index];
  400. const summaryIndex = item[0].index;
  401. const summaryItem = summaryData[index][summaryIndex];
  402. const curUnit = cache[chunkItem.row]?.unit || '';
  403. const summaryItemUnit = summaryItem?.unit || '';
  404. // 相似度过低的、单位不一致的,不命中
  405. if (firstMatch.similarity < 70 || curUnit !== summaryItemUnit) {
  406. noMatchRows.push(chunkItem.row);
  407. return;
  408. };
  409. if (chunkItem && summaryItem) {
  410. workBookObj.sheet.setValue(chunkItem.row, classCodeCol, summaryItem.classCode);
  411. cache[chunkItem.row].classCode = summaryItem.classCode;
  412. const items = getItemsFromTableItem(cache[chunkItem.row]);
  413. items.forEach(item => {
  414. item.classCode = summaryItem.classCode;
  415. });
  416. // 如果实际行存在珠海地区的,才填计算式
  417. const tableItems = getItemsFromTableItem(cache[chunkItem.row]);
  418. const needExpString = tableItems.some(tItem => {
  419. const area = AREA_BOOK.cache.find(areaItem => areaItem.ID === tItem.areaID)
  420. return area && area.name && /珠海/.test(area.name);
  421. });
  422. if (needExpString) {
  423. workBookObj.sheet.setValue(chunkItem.row, expStringCol, summaryItem.expString);
  424. cache[chunkItem.row].expString = summaryItem.expString;
  425. items.forEach(item => {
  426. item.expString = summaryItem.expString;
  427. });
  428. }
  429. }
  430. });
  431. workBookObj.sheet.resumeEvent();
  432. workBookObj.sheet.resumePaint();
  433. percent += step;
  434. $('#progress_modal_body').text(`正在进行AI填值,请稍后${i + 1}/${chunks.length}...`);
  435. $("#progress_modal_bar").css('width', `${percent}%`);
  436. await setTimeoutSync(100);
  437. }
  438. // 没匹配到的行,自动生成别名编码
  439. workBookObj.sheet.suspendEvent();
  440. workBookObj.sheet.suspendPaint();
  441. let curMaxClassCode = getMaxClassCode(priceInfoSummary);
  442. for (const row of noMatchRows) {
  443. const newClassCode = getNewMaxClassCode(curMaxClassCode);
  444. workBookObj.sheet.setValue(row, classCodeCol, newClassCode);
  445. cache[row].classCode = newClassCode;
  446. const items = getItemsFromTableItem(cache[row]);
  447. items.forEach(item => {
  448. item.classCode = newClassCode;
  449. });
  450. curMaxClassCode = newClassCode;
  451. }
  452. workBookObj.sheet.resumeEvent();
  453. workBookObj.sheet.resumePaint();
  454. aiMatchCache = null;
  455. $("#ai-match").text('AI填值');
  456. } catch (error) {
  457. console.log(error);
  458. aiMatchCache = {
  459. curPercent: percent,
  460. curIndex,
  461. noMatchRows,
  462. changedCells,
  463. }
  464. $("#ai-match").text('继续AI填值');
  465. alert(error);
  466. }
  467. await setTimeoutSync(500);
  468. $.bootstrapLoading.progressEnd();
  469. }
  470. // 保存ai填值
  471. const saveData = async () => {
  472. try {
  473. // 分批保存数据,以免数据库压力过大
  474. const totalRows = workBookObj.sheet.getRowCount();
  475. const changedCells = [];
  476. for (let i = 0; i < totalRows; i++) {
  477. changedCells.push({ row: i });
  478. }
  479. if (!changedCells.length) {
  480. return;
  481. }
  482. $.bootstrapLoading.progressStart('保存AI填值', false);
  483. $("#progress_modal_body").text('正在保存AI填值,请稍后...');
  484. await setTimeoutSync(500);
  485. const chunks = _.chunk(changedCells, 100);
  486. let percent = 0;
  487. const step = 100 / (chunks.length || 1);
  488. for (const chunk of chunks) {
  489. await handleEdit(chunk, undefined, undefined, true);
  490. percent += parseInt(`${step}`);
  491. $("#progress_modal_bar").css('width', `${percent}%`);
  492. await setTimeoutSync(200);
  493. }
  494. } catch (error) {
  495. console.log(error);
  496. alert(error);
  497. }
  498. setTimeout(() => {
  499. $.bootstrapLoading.progressEnd();
  500. }, 500);
  501. }
  502. return {
  503. buildWorkBook,
  504. bindEvent,
  505. clear,
  506. initData,
  507. workBookObj,
  508. updateRowCode,
  509. saveInSummary,
  510. aiMatch,
  511. saveData,
  512. }
  513. })();
  514. $(document).ready(() => {
  515. $('#empty-area').on('shown.bs.modal', function () {
  516. if (!EMPTY_BOOK.workBookObj.book) {
  517. EMPTY_BOOK.buildWorkBook();
  518. EMPTY_BOOK.bindEvent();
  519. }
  520. EMPTY_BOOK.initData();
  521. });
  522. $('#empty-area').on('hidden.bs.modal', function () {
  523. EMPTY_BOOK.clear();
  524. $("#ai-match").text('AI填值');
  525. });
  526. // 保存至总表
  527. $('#save-in-summary').click(() => {
  528. EMPTY_BOOK.saveInSummary();
  529. });
  530. // AI填值
  531. $('#ai-match').click(() => {
  532. EMPTY_BOOK.aiMatch();
  533. })
  534. // 保存AI填值
  535. $('#save-data').click(() => {
  536. EMPTY_BOOK.saveData();
  537. })
  538. });