YangHuOperation/web/maintain/price_info_lib/js/priceEmpty.js

// 空数据表
const EMPTY_BOOK = (() => {
  const setting = {
    header: [
      { headerName: '主从对应码', headerWidth: 100, dataCode: 'code', dataType: 'String', hAlign: 'left', vAlign: 'center', formatter: "@" },
      { headerName: '别名编码', headerWidth: 70, dataCode: 'classCode', dataType: 'String', hAlign: 'left', vAlign: 'center', formatter: "@" },
      { headerName: '计算式', headerWidth: 100, dataCode: 'expString', dataType: 'String', hAlign: 'left', vAlign: 'center' },
      { headerName: '材料名称', headerWidth: 275, dataCode: 'name', dataType: 'String', hAlign: 'left', vAlign: 'center' },
      { headerName: '规格型号', headerWidth: 180, dataCode: 'specs', dataType: 'String', hAlign: 'left', vAlign: 'center' },
      { headerName: '单位', headerWidth: 80, dataCode: 'unit', dataType: 'String', hAlign: 'center', vAlign: 'center' },
    ],
  };
  // 初始化表格
  const workBookObj = {
    book: null,
    sheet: null,
  };
  const buildWorkBook = () => {
    workBookObj.book = initSheet($('.empty-spread')[0], setting);
    workBookObj.book.options.allowUserDragDrop = true;
    workBookObj.book.options.allowUserDragFill = true;
    workBookObj.book.options.allowExtendPasteRange = false;
    lockUtil.lockSpreads([workBookObj.book], locked);
    workBookObj.sheet = workBookObj.book.getSheet(0);
  }


  // 总数据（数据没合并前）
  let totalData = [];
  let totalMap = {};

  // 表格显示的数据（合并后）
  const cache = [];

  const getCompareKey = (item) => {
    const props = ['name', 'specs', 'unit'];
    return props.map(prop => item[prop] ? item[prop].trim() : '').join('@');
  }

  const setTotalMap = (items) => {
    totalMap = {};
    items.forEach(item => {
      const key = getCompareKey(item);
      if (totalMap[key]) {
        totalMap[key].push(item);
      } else {
        totalMap[key] = [item];
      }
    })
  }

  // 获取表格数据，汇总空数据，多地区可能存在相同材料，按名称规格单位做筛选，重复材料仅显示一条即可
  const getTableData = (items) => {
    const map = {};
    items.forEach(item => {
      const key = getCompareKey(item);
      if (!map[key]) {
        map[key] = { ...item };
      }
    });
    return Object.values(map);
  }

  // 根据表格数据，获取实际信息价数据（一对多）
  const getItemsFromTableItem = (item) => {
    const key = getCompareKey(item);
    return totalMap[key] || [];
  }

  // 获取材料关键字： 名称 规格
  const getKeyword = (item) => {
    // return item ? `${item.name} ${item.specs}` : '';
    return item ? `${item.name}` : '';
  }

  // 改变关键字
  const changeKeyword = (item) => {
    const keyword = getKeyword(item);
    $('#recommend-search').val(keyword);
    if (!keyword) {
      RECOMMEND_BOOK.clear();
    } else {
      RECOMMEND_BOOK.loadRecommendData(keyword);
    }
  }

  // ai填值缓存，用于ai填值报错时，可以从当前报错处开始匹配，而不用重头开始匹配
  let aiMatchCache = null;

  // 清空
  function clear() {
    cache.length = 0;
    workBookObj.sheet.setRowCount(0);
    aiMatchCache = null;
  }

  let curRow = 0;

  // 初始化数据
  async function initData() {
    clear();
    curRow = 0;
    $.bootstrapLoading.start();
    try {
      const matchAll = $('#match-all-input')[0].checked;
      const areaID = matchAll ? '' : AREA_BOOK.curArea?.ID;
      totalData = await ajaxPost('/priceInfo/getPriceEmptyData', { libID, compilationID, areaID }, 1000 * 60 * 10);
      setTotalMap(totalData);
      const tableData = getTableData(totalData);
      cache.push(...tableData)
      showData(workBookObj.sheet, cache, setting.header);
      changeKeyword(cache[0]);
    } catch (err) {
      clear();
      alert(err);
    } finally {
      $.bootstrapLoading.end();
    }
  }

  // 编辑处理
  async function handleEdit(changedCells, diffMap, needRefresh, saveAll) {
    const postData = []; // 请求用
    // 更新缓存用
    const updateData = [];
    const deleteData = [];
    const insertData = [];
    try {
      changedCells.forEach(({ row }) => {
        if (cache[row]) {
          const rowData = getRowData(workBookObj.sheet, row, setting.header);
          if (Object.keys(rowData).length) { // 还有数据，更新
            let diffData;
            if (diffMap) {
              diffData = diffMap[row];
            } else {
              diffData = saveAll ? getRowAllData(rowData, setting.header) : getRowDiffData(rowData, cache[row], setting.header);
            }
            if (diffData) {
              // 改一行， 实际可能是改多行，表格一行数据是多行合并显示的
              const items = getItemsFromTableItem(cache[row]);
              items.forEach(item => {
                // 只有珠海建筑才更新计算式
                const updateObj = { ...diffData };
                const area = AREA_BOOK.cache.find(areaItem => areaItem.ID === item.areaID);
                if (diffMap) {
                  delete updateObj.expString;
                  delete diffData.expString;
                }
                postData.push({ type: UpdateType.UPDATE, ID: item.ID, areaID: area.ID, compilationID, period: curLibPeriod, data: updateObj });
              });
              updateData.push({ row, data: diffData });
            }
          } else { // 该行无数据了，删除
            const items = getItemsFromTableItem(cache[row]);
            items.forEach(item => {
              const area = AREA_BOOK.cache.find(areaItem => areaItem.ID === item.areaID);
              postData.push({ type: UpdateType.DELETE, areaID: area.ID, compilationID, period: curLibPeriod, ID: item.ID });
            });
            deleteData.push(cache[row]);
          }
        }
      });
      if (postData.length) {
        if (!saveAll) {
          $.bootstrapLoading.start();
        }
        await ajaxPost('/priceInfo/editPriceData', { postData }, TIME_OUT);
        // 更新缓存，先更新然后删除，最后再新增，防止先新增后缓存数据的下标与更新、删除数据的下标对应不上
        updateData.forEach(item => {
          // 更新总表
          const curItem = cache[item.row];
          const compareKey = getCompareKey(curItem);
          const totalItems = totalMap[compareKey];
          if (totalItems) {
            const newCompareKey = getCompareKey({ ...curItem, ...item.data });
            totalItems.forEach(totalItem => {
              Object.assign(totalItem, item.data);
            });
            if (newCompareKey !== compareKey) {
              totalMap[newCompareKey] = totalItems;
              delete totalMap[compareKey];
            }
          }
          // 更新表格缓存
          Object.assign(cache[item.row], item.data);
        });
        deleteData.forEach(item => {
          // 更新总表
          const compareKey = getCompareKey(item);
          const totalItems = totalMap[compareKey];
          if (totalItems) {
            const totalItemIDs = totalItems.map(item => item.ID);
            totalData = totalData.filter(totalItem => !totalItemIDs.includes(totalItem));
            delete totalMap[compareKey];
          }
          // 更新表格缓存
          const index = cache.indexOf(item);
          if (index >= 0) {
            cache.splice(index, 1);
          }
        });
        insertData.forEach(item => cache.push(item));
        if (deleteData.length || insertData.length || needRefresh) {
          showData(workBookObj.sheet, cache, setting.header);
        }
        if (!saveAll) {
          $.bootstrapLoading.end();
        }
        CLASS_BOOK.reload();
      }
    } catch (err) {
      // 恢复各单元格数据
      showData(workBookObj.sheet, cache, setting.header);
      $.bootstrapLoading.end();
    }
  }

  // 跟新行的编号、编码编码
  async function updateRowCode(code, classCode, expString) {
    const item = cache[curRow];
    if (!item) {
      return;
    }
    const diffData = { code, classCode, expString };
    await handleEdit([{ row: curRow }], { [curRow]: diffData }, true);
  }

  const bindEvent = () => {
    workBookObj.sheet.bind(GC.Spread.Sheets.Events.ValueChanged, function (e, info) {
      const changedCells = [{ row: info.row }];
      handleEdit(changedCells);
    });
    workBookObj.sheet.bind(GC.Spread.Sheets.Events.SelectionChanged, function (e, info) {
      const row = info.newSelections && info.newSelections[0] ? info.newSelections[0].row : 0;
      if (curRow !== row) {
        const item = cache[row];
        changeKeyword(item);
      }
      curRow = row;
    });
    workBookObj.sheet.bind(GC.Spread.Sheets.Events.RangeChanged, function (e, info) {
      const changedRows = [];
      let preRow;
      info.changedCells.forEach(({ row }) => {
        if (row !== preRow) {
          changedRows.push({ row });
        }
        preRow = row;
      });
      handleEdit(changedRows);
    });
  }

  // 将空表的表格保存至总表
  const saveInSummary = async () => {
    const documents = [];
    const removeIDs = [];
    cache.filter(item => item.code && item.classCode).forEach(item => {
      removeIDs.push(item.ID);
      documents.push({
        ID: uuid.v1(),
        code: item.code ? item.code.trim() : '',
        classCode: item.classCode ? item.classCode.trim() : '',
        expString: item.expString ? item.expString.trim() : '',
        name: item.name ? item.name.trim() : '',
        specs: item.specs ? item.specs.trim() : '',
        unit: item.unit ? item.unit.trim() : '',
      });
    });
    if (!documents.length) {
      alert('不存在可保存数据');
      return;
    }
    console.log(documents);
    try {
      $.bootstrapLoading.progressStart('保存至总表', true);
      $("#progress_modal_body").text('正在保存至总表，请稍后...');
      await ajaxPost('/priceInfoSummary/saveInSummary', { documents }, 1000 * 60 * 5);
      setTimeout(() => {
        $.bootstrapLoading.progressEnd();
        alert('保存成功');
        const filterCache = cache.filter(item => !removeIDs.includes(item.ID));
        cache.length = 0;
        cache.push(...filterCache);
        showData(workBookObj.sheet, cache, setting.header);
      }, 1000);
    } catch (error) {
      setTimeout(() => {
        $.bootstrapLoading.progressEnd();
      }, 500);
      console.log(error);
      alert(error);
    }
  }

  // 获取最大别名编码
  const getMaxClassCode = (priceInfoSummary) => {
    let maxClassCode = '';
    let maxClassNumber = 0;
    priceInfoSummary.forEach(item => {
      if (!item.classCode) {
        return;
      }
      const numMatch = item.classCode.match(/\d+/);
      if (numMatch && +numMatch[0] > maxClassNumber) {
        maxClassNumber = +numMatch[0];
        maxClassCode = item.classCode;
      }
    });
    // return { maxClassNumber, maxClassCode };
    return maxClassCode;
  }

  // 在最大别名编码基础上+1;
  const getNewMaxClassCode = (maxClassCode) => {
    const numMatch = maxClassCode.match(/\d+/);
    if (!numMatch) {
      return 1;
    }
    const maxNumber = +numMatch[0];
    let newMaxClassCode = String(maxNumber + 1);
    // 补齐的位数
    const pattern = numMatch[0].length - newMaxClassCode.length;
    if (pattern > 0) {
      for (let i = 0; i < pattern; i++) {
        newMaxClassCode = `0${newMaxClassCode}`;
      }
    }
    return newMaxClassCode;

  }

  const getFourCode = (code) => {
    if (!code) {
      return '';
    }
    return code.substring(0, 4);
  }

  const getAIMatchData = (summaryGroupMap, noCodeSummary) => {
    if (aiMatchCache && aiMatchCache.changedCells.length && aiMatchCache.curIndex > 0) {
      return aiMatchCache;
    }
    const curPercent = 0;
    const curIndex = 0;
    const totalRows = workBookObj.sheet.getRowCount();
    const changedCells = [];
    const noMatchRows = []; // 没有匹配、ai没有命中的行，后续需要自动生成别名编码（最大的别名编码+1）
    for (let i = 0; i < totalRows; i++) {
      const rowData = getRowData(workBookObj.sheet, i, setting.header);
      // const code = rowData.code || '';
      const code = getFourCode(rowData.code);
      const toMatchSummary = code ? summaryGroupMap[code] || [] : noCodeSummary;
      if (toMatchSummary.length) {
        changedCells.push({ row: i });
      } else {
        noMatchRows.push(i);
      }
    }
    return {
      curPercent,
      curIndex,
      changedCells,
      noMatchRows,
    }
  }

  // ai填值
  const aiMatch = async () => {
    let percent = 0;
    let curIndex = 0;
    let noMatchRows = [];
    let changedCells = [];
    try {
      // 获取信息价总表
      const priceInfoSummary = await ajaxPost('/priceInfoSummary/getData', {}, 1000 * 60 * 5);
      const summaryGroupMap = _.groupBy(priceInfoSummary, item => getFourCode(item.code));
      const noCodeSummary = priceInfoSummary.filter(item => !item.code);

      const aiMatchData = getAIMatchData(summaryGroupMap, noCodeSummary);
      percent = aiMatchData.curPercent;
      curIndex = aiMatchData.curIndex;
      changedCells = aiMatchData.changedCells;
      noMatchRows = aiMatchData.noMatchRows;

      if (!changedCells.length) {
        return;
      }
      const classCodeCol = setting.header.findIndex(h => h.dataCode === 'classCode');
      const expStringCol = setting.header.findIndex(h => h.dataCode === 'expString');
      // const chunks = _.chunk(changedCells, 20);
      const chunks = _.chunk(changedCells, 1); // 只能一条一条匹配改成，否则经常ai服务经常挂
      $.bootstrapLoading.progressStart('AI填值', false);
      $('#progress_modal_body').text(`正在进行AI填值，请稍后${curIndex + 1}/${chunks.length}...`);
      await setTimeoutSync(500);
      const matchResCache = {};

      // 分块进行ai匹配
      const step = 100 / (chunks.length || 1);
      for (let i = curIndex; i < chunks.length; i++) {
        curIndex = i;
        const chunk = chunks[i];
        const listA = [];
        const listB = [];
        const summaryData = [];
        chunk.forEach(item => {
          const rowData = getRowData(workBookObj.sheet, item.row, setting.header);
          listA.push(`${rowData.name || ''} ${rowData.specs}`);
          const code = getFourCode(rowData.code);
          const toMatchSummary = code ? summaryGroupMap[code] || [] : noCodeSummary;
          summaryData.push(toMatchSummary);
          const summaryKeys = toMatchSummary.map(summary => `${summary.name || ''} ${summary.specs || ''}`);
          listB.push([...new Set(summaryKeys)]);
        });
        const test = listB.map(item => item.length);
        console.log(test);
        const matchRes = matchResCache[listA[0]] ? matchResCache[listA[0]] : await ajaxPost('/priceInfoSummary/aiMatch', { listA, listB }, 1000 * 60 * 5);
        matchResCache[listA[0]] = matchRes;
        // 填匹配值到表格，不实时保存，因为需要人工核查
        workBookObj.sheet.suspendEvent();
        workBookObj.sheet.suspendPaint();
        matchRes.forEach((item, index) => {
          const firstMatch = item[0];
          const chunkItem = chunk[index];
          const summaryIndex = item[0].index;
          const summaryItem = summaryData[index][summaryIndex];
          const curUnit = cache[chunkItem.row]?.unit || '';
          const summaryItemUnit = summaryItem?.unit || '';
          // 相似度过低的、单位不一致的，不命中
          if (firstMatch.similarity < 70 || curUnit !== summaryItemUnit) {
            noMatchRows.push(chunkItem.row);
            return;
          };
          if (chunkItem && summaryItem) {
            workBookObj.sheet.setValue(chunkItem.row, classCodeCol, summaryItem.classCode);
            cache[chunkItem.row].classCode = summaryItem.classCode;
            const items = getItemsFromTableItem(cache[chunkItem.row]);
            items.forEach(item => {
              item.classCode = summaryItem.classCode;
            });
            // 如果实际行存在珠海地区的，才填计算式
            const tableItems = getItemsFromTableItem(cache[chunkItem.row]);
            const needExpString = tableItems.some(tItem => {
              const area = AREA_BOOK.cache.find(areaItem => areaItem.ID === tItem.areaID)
              return area && area.name && /珠海/.test(area.name);
            });
            if (needExpString) {
              workBookObj.sheet.setValue(chunkItem.row, expStringCol, summaryItem.expString);
              cache[chunkItem.row].expString = summaryItem.expString;
              items.forEach(item => {
                item.expString = summaryItem.expString;
              });
            }
          }
        });
        workBookObj.sheet.resumeEvent();
        workBookObj.sheet.resumePaint();
        percent += step;
        $('#progress_modal_body').text(`正在进行AI填值，请稍后${i + 1}/${chunks.length}...`);
        $("#progress_modal_bar").css('width', `${percent}%`);
        await setTimeoutSync(100);
      }

      // 没匹配到的行，自动生成别名编码
      workBookObj.sheet.suspendEvent();
      workBookObj.sheet.suspendPaint();
      let curMaxClassCode = getMaxClassCode(priceInfoSummary);
      for (const row of noMatchRows) {
        const newClassCode = getNewMaxClassCode(curMaxClassCode);
        workBookObj.sheet.setValue(row, classCodeCol, newClassCode);
        cache[row].classCode = newClassCode;
        const items = getItemsFromTableItem(cache[row]);
        items.forEach(item => {
          item.classCode = newClassCode;
        });
        curMaxClassCode = newClassCode;
      }
      workBookObj.sheet.resumeEvent();
      workBookObj.sheet.resumePaint();
      aiMatchCache = null;
      $("#ai-match").text('AI填值');
    } catch (error) {
      console.log(error);
      aiMatchCache = {
        curPercent: percent,
        curIndex,
        noMatchRows,
        changedCells,
      }
      $("#ai-match").text('继续AI填值');
      alert(error);
    }
    await setTimeoutSync(500);
    $.bootstrapLoading.progressEnd();
  }

  // 保存ai填值
  const saveData = async () => {
    try {
      // 分批保存数据，以免数据库压力过大
      const totalRows = workBookObj.sheet.getRowCount();
      const changedCells = [];
      for (let i = 0; i < totalRows; i++) {
        changedCells.push({ row: i });
      }
      if (!changedCells.length) {
        return;
      }
      $.bootstrapLoading.progressStart('保存AI填值', false);
      $("#progress_modal_body").text('正在保存AI填值，请稍后...');
      await setTimeoutSync(500);
      const chunks = _.chunk(changedCells, 100);
      let percent = 0;
      const step = 100 / (chunks.length || 1);
      for (const chunk of chunks) {
        await handleEdit(chunk, undefined, undefined, true);
        percent += parseInt(`${step}`);
        $("#progress_modal_bar").css('width', `${percent}%`);
        await setTimeoutSync(200);
      }
    } catch (error) {
      console.log(error);
      alert(error);
    }
    setTimeout(() => {
      $.bootstrapLoading.progressEnd();
    }, 500);
  }



  return {
    buildWorkBook,
    bindEvent,
    clear,
    initData,
    workBookObj,
    updateRowCode,
    saveInSummary,
    aiMatch,
    saveData,
  }
})();

$(document).ready(() => {
  $('#empty-area').on('shown.bs.modal', function () {
    if (!EMPTY_BOOK.workBookObj.book) {
      EMPTY_BOOK.buildWorkBook();
      EMPTY_BOOK.bindEvent();
    }
    EMPTY_BOOK.initData();
  });

  $('#empty-area').on('hidden.bs.modal', function () {
    EMPTY_BOOK.clear();
    $("#ai-match").text('AI填值');
  });

  // 保存至总表
  $('#save-in-summary').click(() => {
    EMPTY_BOOK.saveInSummary();
  });

  // AI填值
  $('#ai-match').click(() => {
    EMPTY_BOOK.aiMatch();
  })

  // 保存AI填值
  $('#save-data').click(() => {
    EMPTY_BOOK.saveData();
  })
});