BomWeb/src/utils/uploadExcelByUrl.js

import LuckyExcel from 'luckyexcel';
import { getToken } from '@/utils/utils';

const uploadExcelByUrl = (nodeType, versionId, projectName) => {
  const TEMPLATE_URL = 'https://gt-digitization.oss-cn-hangzhou.aliyuncs.com/public/bom/psr.xlsx';

  return new Promise((resolve, reject) => {
    LuckyExcel.transformExcelToLuckyByUrl(
      TEMPLATE_URL,
      '模板.xlsx',
      async (exportJson, luckysheetfile) => {
        let [record] = await getExcel(versionId);

        let len = exportJson.sheets.length;
        const excelData = exportJson.sheets;
        delete record.id;
        record.index = len + '_' + Math.floor(Math.random() * 100);
        record.status = '0';
        record.name = '清单';
        // var sheets = [...excelData, record];
        var res = [];
        const category = getCategoryData(record);
        // 处理Estimate表
        // initEstimate(sheets[0], category);

        // 处理psr预算
        excelData[1].status = 1;
        res.push(initPSR(excelData[1], category, projectName));

        if (nodeType == 4) {
          // 处理现金流
          res.push(initActual(excelData[3], category, projectName));
        }

        res.push(record);

        // 隐藏Estimate表
        excelData[0].hide = 1;
        excelData[0].status = 0;

        res.push(excelData[0]);

        resolve(res.map((item, index) => ({ ...item, order: index })));
      }
    );
  });
};

async function getExcel(gridKey) {
  var formData = new FormData();
  formData.append('gridKey', gridKey);
  let res = await fetch(
    `/api/v1/purchase/record/sheet?gridKey=${gridKey}&JWT-TOKEN=${getToken()}`,
    {
      method: 'POST',
      body: formData,
    }
  ).then(response => response.text());
  return JSON.parse(JSON.parse(res));
}

function getCellValue(cell) {
  let v = '';
  if (cell.v) {
    v = cell.v;
  } else if (cell?.ct?.s) {
    v = cell.ct.s.map(item => item.v).join('');
  }
  return v;
}

function formatNumber(str) {
  const number = parseFloat(str);
  if (!isNaN(number) && number % 1 !== 0) {
    return number.toFixed(1);
  }
  return str;
}
// 根据清单获取表分类总价
function getCategoryData(bom) {
  let bomData = [],
    bomTitle = {};
  bom.celldata.forEach(item => {
    let v = getCellValue(item.v);
    if (item.r == 0) {
      // 设置表头
      bomTitle[item.c] = v;
    } else {
      let key = bomTitle[item.c];
      if (!bomData[item.r - 1]) {
        bomData[item.r - 1] = {};
      }
      bomData[item.r - 1][key] = v;
    }
  });
  let category = {
    'GT-UF膜': 0,
    原平制造: 0,
    其它膜: 0,
    水泵: 0,
    阀门: 0,
    加药系统: 0,
    过滤器: 0,
    空压机: 0,
    非标: 0,
    仪表: 0,
    电气自控: 0,
    双胞胎硬件: 0,
    材料: 0,
    安装: 0,
    土建: 0,
    运输: 0,
    其它: 0,
  };
  bomData.forEach(item => {
    if (category.hasOwnProperty(item['类别'])) {
      let price = parseFloat(item['总价（元）']);
      if (isNaN(price)) return;
      category[item['类别']] += price;
    }
  });
  return category;
}

// 处理现金流表
function initActual(actual, category, projectName) {
  let actualCategory = [];
  actual.celldata.forEach((item, i, celldata) => {
    if (item.c == 0 && item.v?.v) {
      // 处理序号转float出现多余小数的情况
      item.v.v = formatNumber(item.v?.v);
    }
    if (item.c == 1 && item.r == 0) item.v.v = projectName;
    // 清单分类的总价填入对应预算列
    if (item.c == 2) {
      // c=2 为名称列
      let value = getCellValue(item.v);
      // 判断该行是否为类型总列
      if (category.hasOwnProperty(value)) {
        // 名称后第三列为预算列
        celldata[i + 3].v.v = category[value];
      }
    }
  });
  return actual;
}

// 处理毛利概算表
function initEstimate(estimate, category) {
  estimate.celldata.forEach(item => {
    if (item.r == 15 && item.c == 2) {
      // 金科制造中心-UF膜
      item.v.v = category['GT-UF膜'];
    } else if (item.r == 14 && item.c == 2) {
      // 金科制造中心设备制造费
      item.v.v = category['原平制造'];
    } else if (item.r == 16 && item.c == 2) {
      // 设备购置费
      item.v.v =
        category['其它膜'] +
        category['水泵'] +
        category['阀门'] +
        category['加药系统'] +
        category['过滤器'] +
        category['空压机'] +
        category['非标'] +
        category['仪表'] +
        category['电气自控'] +
        category['材料'] +
        category['运输'] +
        category['其它'];
    } else if (item.r == 18 && item.c == 2) {
      // 数字双胞胎
      item.v.v = category['双胞胎硬件'];
    } else if (item.r == 19 && item.c == 2) {
      // 安装
      item.v.v = category['安装'];
    } else if (item.r == 20 && item.c == 2) {
      // 土建
      item.v.v = category['土建'];
    }
  });
  return estimate;
}

// 处理PSR表预算
function initPSR(psr, category, projectName) {
  psr.celldata.forEach(item => {
    if (item.r == 0 && item.c == 1) {
      item.v.v = projectName;
    } else if (item.r == 38 && item.c == 2) {
      item.v.v = category['GT-UF膜'];
    } else if (item.r == 37 && item.c == 2) {
      item.v.v = category['原平制造'];
    } else if (item.r == 36 && item.c == 2) {
      item.v.v =
        category['其它膜'] +
        category['水泵'] +
        category['阀门'] +
        category['加药系统'] +
        category['过滤器'] +
        category['空压机'] +
        category['非标'] +
        category['仪表'] +
        category['电气自控'] +
        category['材料'] +
        category['运输'] +
        category['其它'];
    } else if (item.r == 39 && item.c == 2) {
      item.v.v = category['双胞胎硬件'];
    } else if (item.r == 40 && item.c == 2) {
      item.v.v = category['安装'];
    } else if (item.r == 41 && item.c == 2) {
      item.v.v = category['土建'];
    }
  });
  return psr;
}

export default uploadExcelByUrl;