BomWeb/src/utils/uploadExcelByUrl.js

import LuckyExcel from 'luckyexcel';
import { getToken } from '@/utils/utils';
import moment from 'moment';
window.moment = moment;

const uploadExcelByUrl = (nodeType, versionId, project) => {
  const TEMPLATE_URL =
    'https://water-service-test.oss-cn-hangzhou.aliyuncs.com/public/bom/psr1121-2.xlsx';
  // 'https://water-service-test.oss-cn-hangzhou.aliyuncs.com/public/bom/psr1010.xlsx';
  // 'https://water-service-test.oss-cn-hangzhou.aliyuncs.com/public/bom/psr1009.xlsx';
  // 'https://water-service-test.oss-cn-hangzhou.aliyuncs.com/public/bom/psr1008.xlsx';
  // 'https://gt-digitization.oss-cn-hangzhou.aliyuncs.com/public/bom/psr0913.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, project?.project_name));

        if (nodeType == 4) {
          // 隐藏现金流表
          excelData[3].hide = 1;
          excelData[3].status = 0;
          // 处理现金流
          res.push(initActual(excelData[3], category, project));
        }

        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) {
  if (str.length < 6) return 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.trim();
    } 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['总价'] || item['总价（元）']);
      if (isNaN(price)) return;
      category[item['类别']] += price;
    }
  });
  return category;
}

// 处理现金流表
function initActual(actual, category, project) {
  let actualCategory = [];
  actual.celldata.forEach((item, i, celldata) => {
    if (item.c == 0 && item.v?.v) {
      // LuckyExcel会将序号转float后再次转回string，导致小数出现精度问题
      // 序号后超过2位数的固定处理，不超过两位数的保留一位小数
      switch (item.r) {
        case 65:
          item.v.v = '5.10';
          break;
        case 66:
          item.v.v = '5.11';
          break;
        case 126:
          item.v.v = '9.12';
          break;
        case 127:
          item.v.v = '9.13';
          break;
        case 147:
          item.v.v = '10.12';
          break;
        case 148:
          item.v.v = '10.13';
          break;
        case 262:
          item.v.v = '17.15';
          break;
        case 264:
          item.v.v = '17.17';
        case 266:
          item.v.v = '17.19';
          break;
        default:
          // 处理序号转float出现多余小数的情况
          item.v.v = formatNumber(item.v?.v);
          break;
      }
    } else if (item.c == 1 && item.r == 0) {
      item.v.v = project?.project_name;
    } else if (item.c == 1 && item.r == 1) {
      item.v.v = project?.project_full_code;
    } else if (item.c == 2) {
      // 清单分类的总价填入对应预算列
      // c=2 为名称列
      let value = getCellValue(item.v);
      // 判断该行是否为类型总列
      if (category.hasOwnProperty(value)) {
        // 名称后第三列为预算列
        celldata[i + 3].v.v = category[value];
      }
    } else if (item.c == 12 && item.r == 5) {
      // 设置第一个月时间
      let start_date = moment('1900-01-01 00:00:00');
      let end_date = moment().startOf('month');
      item.v.v = end_date.diff(start_date, 'days');
    }
  });
  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;