import BaseController from "../baseController";
|
import { Post } from "egg-shell-decorators";
|
import { BaseExpressSpareCode } from "../../entity/express/spare/baseExpressSpareCode";
|
import * as XLSX from "xlsx";
|
import * as path from "path";
|
import { BaseExpressCorp } from "../../entity/basicInfo/base/baseExpressCorp";
|
import { SysParamValue } from "../../entity/sys/core/sysParamValue";
|
|
export default class SpareCodeController extends BaseController {
|
// #region 导入importExcel
|
/// <summary>
|
/// 导入importExcel
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async importExcel() {
|
this.ctx.socket.setTimeout(15 * 60 * 1000);
|
setTimeout(async () => {
|
await this.importExelWork();
|
this.ctx.socket.end();
|
}, 0);
|
|
this.info.result = true;
|
this.ctx.body = this.info;
|
}
|
|
private async importExelWork() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let redis = ctx.app.redis.clients.get("common"); // 将消息放入redis缓存
|
let fileUrl = body.url;
|
let userInfo = await ctx.helper.userInfo();
|
let msg = "";
|
redis.expire(body.key, 5 * 60);
|
if (!fileUrl) {
|
redis.rpush(body.key, "上传文件不存在");
|
return;
|
}
|
if (!body.key) {
|
this.setMsg(body.key, "上传key不存在");
|
return;
|
}
|
try {
|
let rootPath = path.resolve(); // 获得根目录
|
let filePath = rootPath + path.sep + fileUrl.replace(/\//gi, path.sep); // 上传文件路径
|
var workbook = XLSX.readFile(filePath); //整个 excel 文档
|
var sheetNames = workbook.SheetNames; //获取所有工作薄名
|
var sheet1 = workbook.Sheets[sheetNames[0]]; //根据工作薄名获取工作薄
|
let dataList = XLSX.utils.sheet_to_json(sheet1); // 获得当前sheet表单数据转为json格式
|
|
//#region 验证数据正确性
|
this.info.result = true;
|
if (!dataList.length) {
|
this.setMsg(`没有可导入的数据`, "red");
|
return;
|
}
|
let index = 0,
|
successCount = 0,
|
failedCount = 0;
|
let insertList = [];
|
let existList = [];
|
for (let row of dataList) {
|
index += 1;
|
let spareCode = row["*备用单号"];
|
let expressCorpName = row["*快递名称"];
|
let expressCorpTypeName = row["*快递类型"];
|
if (!spareCode) {
|
this.setMsg(`${index}、${spareCode}备用单号为空，跳过！`, "red");
|
failedCount++;
|
continue;
|
}
|
|
let spareInfo = await this.dbRead.findOne(BaseExpressSpareCode, {
|
select: ["spareCode"],
|
where: {
|
spareCode: "" + spareCode
|
}
|
});
|
if (spareInfo) {
|
existList.push(spareCode);
|
if (index % 50 === 1) {
|
this.setMsg(`${index}、${spareCode}备用单号重复，系统已有此单号，跳过！`, "red");
|
}
|
failedCount++;
|
continue;
|
}
|
|
let expressname = await this.dbRead.findOne(BaseExpressCorp, {
|
userProduct_Id: userInfo.userProduct_Id,
|
expressCorpName: expressCorpName
|
});
|
if (!expressname) {
|
this.setMsg(`${index}、${spareCode}所属单号的快递名称不存在，跳过！`, "red");
|
failedCount++;
|
continue;
|
}
|
|
let paramValuetype = await this.dbRead.findOne(SysParamValue, {
|
enable: 1,
|
value02: expressCorpTypeName
|
});
|
if (!paramValuetype) {
|
this.setMsg(`${index}、${spareCode}所属单号的快递类型不存在，跳过！`, "red");
|
failedCount++;
|
continue;
|
}
|
|
let express = new BaseExpressSpareCode();
|
|
express.expressCorp_Id = expressname.expressCorp_Id;
|
express.expressCorpName = expressname.expressCorpName;
|
express.spareCode = spareCode;
|
express.userProduct_Id = userInfo.userProduct_Id;
|
express.userProductCode = userInfo.userProductCode;
|
express.userProductAlias = userInfo.userProductAlias;
|
express.platUser_Id = userInfo.platUser_Id;
|
express.platUserCode = userInfo.platUserCode;
|
express.platUserName = userInfo.platUserName;
|
express.platCorpName = userInfo.platCorpName;
|
express.createID = userInfo.userProduct_Id;
|
express.enable = 1;
|
express.expressCorpType = paramValuetype.value01;
|
express.createDate = new Date();
|
express.creator = userInfo.userName;
|
insertList.push(express);
|
if (index % 50 === 1) {
|
await this.dbWrite.insert(BaseExpressSpareCode, insertList);
|
insertList = [];
|
msg = `${index}、导入${spareCode}成功`;
|
this.setMsg(msg, "blue");
|
}
|
successCount++;
|
}
|
if (insertList.length > 0) {
|
await this.dbWrite.insert(BaseExpressSpareCode, insertList);
|
msg = "导入第" + successCount + "行成功";
|
this.setMsg(msg, "blue");
|
insertList = [];
|
}
|
|
msg = `导入成功${successCount}条，失败${failedCount}条\r\n`;
|
this.setMsg(msg, "blue");
|
if (existList.length) {
|
msg = "存在的单号：" + existList.join(", ");
|
this.setMsg(msg, "red");
|
existList = [];
|
}
|
} catch (ex) {
|
this.setMsg("出现异常：" + ex.message + ex.StackTrace, "red");
|
}
|
this.setMsg("-1");
|
}
|
}
|
