import BaseController from "../../baseController";
|
import { Post } from "egg-shell-decorators";
|
import * as XLSX from "xlsx";
|
import * as path from "path";
|
import { BaseProductInfo } from "../../../entity/basicInfo/base/baseProductInfo";
|
import { TMSProductInfoPort } from "../../../entity/express/tms/tmsProductInfoPort";
|
import { TMSPort } from "../../../entity/express/tms/tmsPort";
|
|
/**
|
* 基础信息 - 口岸
|
*/
|
export default class PortController extends BaseController {
|
// #region 导入Excel
|
/// <summary>
|
/// 导入Excel
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async importExel() {
|
setTimeout(async () => {
|
await this.importExelWork();
|
}, 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;
|
}
|
let spdaoruCount = 0;
|
let spgengxinCount = 0;
|
let qZdaoruCount = 0;
|
let qZgengxinCount = 0;
|
let fuzhoudaoruCount = 0;
|
let fuzhougengxinCount = 0;
|
let xiandaoruCount = 0;
|
let xiangengxinCount = 0;
|
let beijingdaoruCount = 0;
|
let beijinggengxinCount = 0;
|
let guangzhoudaoruCount = 0;
|
let guangzhougengxinCount = 0;
|
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;
|
}
|
|
// #region 校验数据
|
this.isMsgError = false;
|
let index = 0;
|
for (let row of dataList) {
|
index++;
|
this.setMsg(`${index}、开始验证...`);
|
let productModel = row["条形码"];
|
let productCode = row["物料编号"];
|
let productName = row["物料名称"];
|
let typeName = row["物料类别"];
|
let productSpec = row["物料规格"]; /////////对应物料信息表
|
let originPlace = row["原产地"];
|
|
let currency = row["申报币种"];
|
let netWeight = row["净重（kg）"];
|
let weight = row["毛重（kg）"];
|
let smallUnit = row["成交单位"];
|
let postMailCode = row["行邮税号"];
|
let hSCode = row["HSCode"];
|
|
// 非空验证
|
if (!productModel) {
|
this.setMsg(`${index}、条形码不能为空`, "red");
|
}
|
if (!productCode) {
|
this.setMsg(`${index}、物料编号不能为空`, "red");
|
}
|
|
if (!productName) {
|
this.setMsg(`${index}、物料称不能为空`, "red");
|
}
|
|
if (!typeName) {
|
this.setMsg(`${index}、物料类别不能为空`, "red");
|
}
|
if (!productSpec) {
|
this.setMsg(`${index}、物料规格不能为空`, "red");
|
}
|
if (!originPlace) {
|
this.setMsg(`${index}、原产地不能为空`, "red");
|
}
|
if (!currency) {
|
this.setMsg(`${index}、申报币种`, "red");
|
}
|
if (!netWeight) {
|
this.setMsg(`${index}、净重不能为空`, "red");
|
}
|
if (!weight) {
|
this.setMsg(`${index}、毛重不能为空`, "red");
|
}
|
if (!smallUnit) {
|
this.setMsg(`${index}、成交单位不能为空`, "red");
|
}
|
if (!postMailCode) {
|
this.setMsg(`${index}、行邮税号不能为空`, "red");
|
}
|
if (!hSCode) {
|
this.setMsg(`${index}、HSCode不能为空`, "red");
|
}
|
// if (msg) {
|
// this.info.msg = msg;
|
// this.info.result = false;
|
// this.ctx.body = this.info;
|
// return;
|
// }
|
}
|
//#endregion
|
if (this.isMsgError) {
|
this.setMsg("-1");
|
return;
|
}
|
|
index = 0;
|
for (let row of dataList) {
|
index++;
|
let productModel = row["条形码"];
|
let productCode = row["物料编号"];
|
let productName = row["物料名称"];
|
let cIQName = row["英文名称"];
|
let relationCode3 = row["助记码"];
|
let brandName = row["品牌"];
|
let typeName = row["物料类别"];
|
let productSpec = row["物料规格"]; /////////对应物料信息表
|
let originPlace = row["原产地"];
|
|
let currency = row["申报币种"];
|
let netWeight = row["净重（kg）"];
|
let weight = row["毛重（kg）"];
|
let smallUnit = row["成交单位"];
|
let postMailCode = row["行邮税号"];
|
let hSCode = row["HSCode"];
|
// 泉州
|
let quanzhouCIQNameCn = row["泉州申报品名"];
|
let quanzhouProductSpec = row["泉州申报规格"]; /////口岸物料里面对应规格
|
let quanzhouDeclareQuantityOrder = row["泉州申报数量"];
|
let quanzhouDeclareUnit = row["泉州申报单位"];
|
let quanzhouDeclarePrice = row["泉州申报价格"];
|
//福州
|
let fuzhouCIQNameCn = row["福州申报品名"];
|
let fuzhouProductSpec = row["福州申报规格"]; /////口岸物料里面对应规格
|
let fuzhouDeclareQuantityOrder = row["福州申报数量"];
|
let fuzhouDeclareUnit = row["福州申报单位"];
|
let fuzhouDeclarePrice = row["福州申报价格"];
|
//西安
|
let xianCIQNameCn = row["西安申报品名"];
|
let xianProductSpec = row["西安申报规格"]; /////口岸物料里面对应规格
|
let xianDeclareQuantityOrder = row["西安申报数量"];
|
let xianDeclareUnit = row["西安申报单位"];
|
let xianDeclarePrice = row["西安申报价格"];
|
//北京
|
let beijingCIQNameCn = row["北京申报品名"];
|
let beijingProductSpec = row["北京申报规格"]; /////口岸物料里面对应规格
|
let beijingDeclareQuantityOrder = row["北京申报数量"];
|
let beijingDeclareUnit = row["北京申报单位"];
|
let beijingDeclarePrice = row["北京申报价格"];
|
let beijingStatutoryQty2 = row["北京第二法定数量"];
|
let beijingStatutoryUnit2 = row["北京第二法定单位"];
|
//广州
|
let guangzhouCIQNameCn = row["广州申报品名"];
|
let guangzhouProductSpec = row["广州申报规格"]; /////口岸物料里面对应规格
|
let guangzhouDeclareQuantityOrder = row["广州申报数量"];
|
let guangzhouDeclareUnit = row["广州申报单位"];
|
let guangzhouDeclarePrice = row["广州申报价格"];
|
|
let productInfo = await this.dbRead.findOne(BaseProductInfo, {
|
productCode: "" + productCode,
|
productModel: "" + productModel
|
});
|
// #region 新增或修改物料信息
|
if (!productInfo) {
|
spdaoruCount += 1;
|
//添加物料信息
|
await this.dbWrite.insert(BaseProductInfo, {
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
productModel: productModel,
|
productCode: productCode,
|
productName: productName,
|
cIQName: cIQName,
|
relationCode3: relationCode3,
|
brandName: brandName,
|
typeName: typeName,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
productSpec: productSpec,
|
originPlace: originPlace,
|
currency: currency,
|
netWeight: netWeight,
|
weight: weight,
|
smallUnit: smallUnit,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
consignor_Id: 30,
|
consignorCode: "HZ20180002",
|
consignorName: "默认货主",
|
enable: 1,
|
createDate: new Date()
|
});
|
} else {
|
//更新物料基础表
|
spgengxinCount += 1;
|
this.dbWrite.update(BaseProductInfo, productInfo.product_Id, {
|
productModel: productModel,
|
productCode: productCode,
|
productName: productName,
|
cIQName: cIQName,
|
relationCode3: relationCode3,
|
brandName: brandName,
|
typeName: typeName,
|
productSpec: productSpec,
|
originPlace: originPlace,
|
currency: currency,
|
netWeight: netWeight,
|
weight: weight,
|
smallUnit: smallUnit,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
consignor_Id: 30,
|
consignorCode: "HZ20180002",
|
consignorName: "默认货主",
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
enable: 1,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
createDate: new Date()
|
});
|
}
|
//#endregion
|
let base_ProductInfo = await this.dbRead.findOne(BaseProductInfo, {
|
productCode: "" + productCode,
|
productModel: "" + productModel
|
});
|
|
//#region 泉州不为空
|
if (quanzhouCIQNameCn) {
|
let tms_ProductInfo_Port = await this.dbRead.findOne(TMSProductInfoPort, {
|
portName: "" + "泉州口岸",
|
productCode: "" + productCode
|
});
|
if (tms_ProductInfo_Port) {
|
qZgengxinCount += 1;
|
await this.dbWrite.update(TMSProductInfoPort, tms_ProductInfo_Port.portProduct_Id, {
|
port_Id: tms_ProductInfo_Port.port_Id,
|
product_Id: base_ProductInfo.product_Id,
|
cIQNameCn: quanzhouCIQNameCn,
|
productSpec: quanzhouProductSpec,
|
declareQuantityOrder: quanzhouDeclareQuantityOrder,
|
declareUnit: quanzhouDeclareUnit,
|
declarePrice: quanzhouDeclarePrice,
|
portName: tms_ProductInfo_Port.portName,
|
productName: base_ProductInfo.productName,
|
productCode: base_ProductInfo.productCode,
|
productModel: base_ProductInfo.productModel,
|
cIQName: cIQName,
|
typeName: typeName,
|
brandName: brandName,
|
originPlace: originPlace,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
statutoryUnit2: tms_ProductInfo_Port.statutoryUnit2,
|
statutoryQty2: tms_ProductInfo_Port.statutoryQty2,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
createDate: new Date()
|
});
|
} else {
|
qZdaoruCount += 1;
|
await this.dbWrite.insert(TMSProductInfoPort, {
|
port_Id: 6,
|
portName: "泉州口岸",
|
product_Id: base_ProductInfo.product_Id,
|
productCode: base_ProductInfo.productCode,
|
productName: base_ProductInfo.productName,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: quanzhouCIQNameCn,
|
cIQName: base_ProductInfo.cIQName,
|
typeName: typeName,
|
productSpec: quanzhouProductSpec,
|
brandName: brandName,
|
originPlace: originPlace,
|
declareQuantityOrder: quanzhouDeclareQuantityOrder,
|
declareUnit: quanzhouDeclareUnit,
|
declarePrice: quanzhouDeclarePrice,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
declareCurrency: base_ProductInfo.currency,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
createDate: new Date()
|
});
|
}
|
}
|
//#endregion
|
|
//#region 福州不为空
|
if (fuzhouCIQNameCn) {
|
let tms_ProductInfo_Port = await this.dbRead.findOne(TMSProductInfoPort, {
|
portName: "" + "福州口岸",
|
productCode: "" + productCode
|
});
|
if (tms_ProductInfo_Port) {
|
fuzhougengxinCount += 1;
|
await this.dbWrite.update(TMSProductInfoPort, tms_ProductInfo_Port.portProduct_Id, {
|
port_Id: tms_ProductInfo_Port.port_Id,
|
product_Id: base_ProductInfo.product_Id,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: fuzhouCIQNameCn,
|
cIQName: cIQName,
|
productSpec: fuzhouProductSpec,
|
declareQuantityOrder: fuzhouDeclareQuantityOrder,
|
declareUnit: fuzhouDeclareUnit,
|
declarePrice: fuzhouDeclarePrice,
|
declareCurrency: base_ProductInfo.currency,
|
portName: tms_ProductInfo_Port.portName,
|
productName: base_ProductInfo.productName,
|
productCode: base_ProductInfo.productCode,
|
typeName: typeName,
|
brandName: brandName,
|
originPlace: originPlace,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
statutoryUnit2: tms_ProductInfo_Port.statutoryUnit2,
|
statutoryQty2: tms_ProductInfo_Port.statutoryQty2,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
createDate: new Date()
|
});
|
} else {
|
fuzhoudaoruCount += 1;
|
await this.dbWrite.insert(TMSProductInfoPort, {
|
port_Id: 5,
|
portName: "福州口岸",
|
product_Id: base_ProductInfo.product_Id,
|
productCode: base_ProductInfo.productCode,
|
productName: base_ProductInfo.productName,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: fuzhouCIQNameCn,
|
cIQName: base_ProductInfo.cIQName,
|
typeName: typeName,
|
productSpec: fuzhouProductSpec,
|
brandName: brandName,
|
originPlace: originPlace,
|
declareQuantityOrder: fuzhouDeclareQuantityOrder,
|
declareUnit: fuzhouDeclareUnit,
|
declarePrice: fuzhouDeclarePrice,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
declareCurrency: base_ProductInfo.currency,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
createDate: new Date()
|
});
|
}
|
}
|
//#endregion
|
//#region 西安不为空
|
if (xianCIQNameCn) {
|
let tms_ProductInfo_Port = await this.dbRead.findOne(TMSProductInfoPort, {
|
portName: "" + "西安口岸",
|
productCode: "" + productCode
|
});
|
if (tms_ProductInfo_Port) {
|
xiangengxinCount += 1;
|
await this.dbWrite.update(TMSProductInfoPort, tms_ProductInfo_Port.portProduct_Id, {
|
port_Id: tms_ProductInfo_Port.port_Id,
|
product_Id: base_ProductInfo.product_Id,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: xianCIQNameCn,
|
cIQName: cIQName,
|
productSpec: xianProductSpec,
|
declareQuantityOrder: xianDeclareQuantityOrder,
|
declareUnit: xianDeclareUnit,
|
declarePrice: xianDeclarePrice,
|
declareCurrency: base_ProductInfo.currency,
|
portName: tms_ProductInfo_Port.portName,
|
productName: base_ProductInfo.productName,
|
productCode: base_ProductInfo.productCode,
|
typeName: typeName,
|
brandName: brandName,
|
originPlace: originPlace,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
statutoryUnit2: tms_ProductInfo_Port.statutoryUnit2,
|
statutoryQty2: tms_ProductInfo_Port.statutoryQty2,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
createDate: new Date()
|
});
|
} else {
|
xiandaoruCount += 1;
|
await this.dbWrite.insert(TMSProductInfoPort, {
|
port_Id: 7,
|
portName: "西安口岸",
|
product_Id: base_ProductInfo.product_Id,
|
productCode: base_ProductInfo.productCode,
|
productName: base_ProductInfo.productName,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: xianCIQNameCn,
|
cIQName: base_ProductInfo.cIQName,
|
typeName: typeName,
|
productSpec: xianProductSpec,
|
brandName: brandName,
|
originPlace: originPlace,
|
declareQuantityOrder: xianDeclareQuantityOrder,
|
declareUnit: xianDeclareUnit,
|
declarePrice: xianDeclarePrice,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
declareCurrency: base_ProductInfo.currency,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
createDate: new Date()
|
});
|
}
|
}
|
//#endregion
|
|
//#region 北京不为空
|
if (beijingCIQNameCn) {
|
let tms_ProductInfo_Port = await this.dbRead.findOne(TMSProductInfoPort, {
|
portName: "" + "北京口岸",
|
productCode: "" + productCode
|
});
|
if (tms_ProductInfo_Port) {
|
beijinggengxinCount += 1;
|
await this.dbWrite.update(TMSProductInfoPort, tms_ProductInfo_Port.portProduct_Id, {
|
port_Id: tms_ProductInfo_Port.port_Id,
|
product_Id: base_ProductInfo.product_Id,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: beijingCIQNameCn,
|
cIQName: cIQName,
|
productSpec: beijingProductSpec,
|
declareQuantityOrder: beijingDeclareQuantityOrder,
|
declareUnit: beijingDeclareUnit,
|
declarePrice: beijingDeclarePrice,
|
declareCurrency: base_ProductInfo.currency,
|
portName: tms_ProductInfo_Port.portName,
|
productName: base_ProductInfo.productName,
|
productCode: base_ProductInfo.productCode,
|
typeName: typeName,
|
brandName: brandName,
|
originPlace: originPlace,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
statutoryQty2: beijingStatutoryQty2,
|
statutoryUnit2: beijingStatutoryUnit2,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
createDate: new Date()
|
});
|
} else {
|
beijingdaoruCount += 1;
|
await this.dbWrite.insert(TMSProductInfoPort, {
|
port_Id: 2,
|
portName: "北京口岸",
|
product_Id: base_ProductInfo.product_Id,
|
productCode: base_ProductInfo.productCode,
|
productName: base_ProductInfo.productName,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: beijingCIQNameCn,
|
cIQName: cIQName,
|
typeName: typeName,
|
productSpec: beijingProductSpec,
|
brandName: brandName,
|
originPlace: originPlace,
|
declareQuantityOrder: beijingDeclareQuantityOrder,
|
declareUnit: beijingDeclareUnit,
|
declarePrice: beijingDeclarePrice,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
declareCurrency: base_ProductInfo.currency,
|
statutoryQty2: beijingStatutoryQty2,
|
statutoryUnit2: beijingStatutoryUnit2,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
createDate: new Date()
|
});
|
}
|
}
|
//endregion
|
|
//#region 广州不为空
|
if (guangzhouCIQNameCn) {
|
let tms_ProductInfo_Port = await this.dbRead.findOne(TMSProductInfoPort, {
|
portName: "" + "广州口岸",
|
productCode: "" + productCode
|
});
|
if (tms_ProductInfo_Port) {
|
guangzhougengxinCount += 1;
|
await this.dbWrite.update(TMSProductInfoPort, tms_ProductInfo_Port.portProduct_Id, {
|
port_Id: tms_ProductInfo_Port.port_Id,
|
product_Id: base_ProductInfo.product_Id,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: guangzhouCIQNameCn,
|
cIQName: cIQName,
|
productSpec: guangzhouProductSpec,
|
declareQuantityOrder: guangzhouDeclareQuantityOrder,
|
declareUnit: guangzhouDeclareUnit,
|
declarePrice: guangzhouDeclarePrice,
|
declareCurrency: base_ProductInfo.currency,
|
portName: tms_ProductInfo_Port.portName,
|
productName: base_ProductInfo.productName,
|
productCode: base_ProductInfo.productCode,
|
typeName: typeName,
|
brandName: brandName,
|
originPlace: originPlace,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
statutoryUnit2: tms_ProductInfo_Port.statutoryUnit2,
|
statutoryQty2: tms_ProductInfo_Port.statutoryQty2,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
createDate: new Date()
|
});
|
} else {
|
guangzhoudaoruCount += 1;
|
await this.dbWrite.insert(TMSProductInfoPort, {
|
port_Id: 8,
|
portName: "广州口岸",
|
product_Id: base_ProductInfo.product_Id,
|
productCode: base_ProductInfo.productCode,
|
productName: base_ProductInfo.productName,
|
productModel: base_ProductInfo.productModel,
|
cIQNameCn: guangzhouCIQNameCn,
|
cIQName: base_ProductInfo.cIQName,
|
typeName: typeName,
|
productSpec: guangzhouProductSpec,
|
brandName: brandName,
|
originPlace: originPlace,
|
declareQuantityOrder: guangzhouDeclareQuantityOrder,
|
declareUnit: guangzhouDeclareUnit,
|
declarePrice: guangzhouDeclarePrice,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
declareCurrency: base_ProductInfo.currency,
|
createID: userInfo.user_Id,
|
creator: userInfo.userTrueName,
|
createDate: new Date()
|
});
|
}
|
}
|
//#endregion
|
}
|
//#region
|
msg +=
|
"<div>导入物料信息表" +
|
spdaoruCount +
|
"条,导入物料信息表 " +
|
spgengxinCount +
|
"条</div>" +
|
"\r\n" +
|
"<div>泉州口岸导入" +
|
qZdaoruCount +
|
"条,泉州口岸更新 " +
|
qZgengxinCount +
|
"条</div>" +
|
"\r\n" +
|
"<div>福州口岸导入" +
|
fuzhoudaoruCount +
|
"条,福州口岸更新 " +
|
fuzhougengxinCount +
|
"条</div>" +
|
"\r\n" +
|
"<div>西安口岸导入" +
|
xiandaoruCount +
|
"条,西安口岸更新 " +
|
xiangengxinCount +
|
"</div>" +
|
"\r\n" +
|
"<div>京口岸导入" +
|
beijingdaoruCount +
|
"条,北京口岸更新 " +
|
beijinggengxinCount +
|
"</div>" +
|
"\r\n" +
|
"<div>广州口岸导入" +
|
guangzhoudaoruCount +
|
"条,广州口岸更新 " +
|
guangzhougengxinCount +
|
"</div>";
|
//#endregion
|
this.setMsg(msg, "blue");
|
this.setMsg("-1");
|
} catch (ex) {
|
this.setMsg("出现异常：" + ex.message + ex.StackTrace, "red");
|
}
|
this.setMsg("-1");
|
}
|
// #endregion
|
|
//#region 原来导入
|
/// <summary>
|
/// 导入Excel
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async importOldExel() {
|
setTimeout(async () => {
|
await this.importOldExelWork();
|
}, 0);
|
|
this.info.result = true;
|
this.ctx.body = this.info;
|
}
|
|
private async importOldExelWork() {
|
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 = "";
|
let msgCount = 0;
|
let errorgengxinCount = 0;
|
redis.expire(body.key, 5 * 60);
|
if (!body.key) {
|
this.setMsg(body.key, "上传key不存在");
|
return;
|
}
|
if (!fileUrl) {
|
redis.rpush(body.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;
|
}
|
this.isMsgError = false;
|
let index = 0;
|
for (let row of dataList) {
|
index++;
|
// this.setMsg(`${index}、开始验证...`);
|
let portName = row["口岸名称"];
|
let productCode = row["物料编号"];
|
let productModel = row["条形码"];
|
let cIQNameCn = row["申报品名"];
|
let typeName = row["类别"];
|
let productSpec = row["物料规格"];
|
let brandName = row["品牌"];
|
let originPlace = row["原产地"];
|
// let placeCode = row["产地双字母代码"];
|
|
let netWeight = row["净重"];
|
let weight = row["毛重"];
|
let postMailCode = row["行邮税号"];
|
let hSCode = row["HSCode"];
|
let declareCurrency = row["申报币种"];
|
let bEDeclareCurrency = row["个物申报币种"];
|
// let cIQName = row["英文名称"];
|
// let declareQuantityOrder = row["申报数量"];
|
// let declareUnit = row["申报单位"];
|
// let declarePrice = row["申报价格"];
|
// let englishType = row["英文类别"];
|
|
// let statutoryUnit2 = row["第二法定单位"];
|
// let statutoryQty2 = row["第二法定数量"];
|
|
// let bondedDeclareUnit = row["保税申报单位"];
|
// let cIQProductModel = row["物料海关编号"];
|
// let recordCode = row["备案口岸代码"];
|
// let manufacturer = row["生产厂家"];
|
// let composition = row["材质成分"];
|
// let usage = row["用途"];
|
// let remark = row["备注"];
|
|
// #region 校验数据
|
// 非空验证
|
if (!portName) {
|
this.setMsg(`${index}、口岸名称不能为空`, "red");
|
}
|
if (!productCode) {
|
this.setMsg(`${index}、物料编号不能为空`, "red");
|
}
|
if (!productModel) {
|
this.setMsg(`${index}、条形码不能为空`, "red");
|
}
|
if (!cIQNameCn) {
|
this.setMsg(`${index}、申报品名不能为空`, "red");
|
}
|
if (!typeName) {
|
this.setMsg(`${index}、类别不能为空`, "red");
|
}
|
if (!productSpec) {
|
this.setMsg(`${index}、物料规格不能为空`, "red");
|
}
|
if (!brandName) {
|
this.setMsg(`${index}、牌不能为空`, "red");
|
}
|
if (!originPlace) {
|
this.setMsg(`${index}、原产地不能为空`, "red");
|
}
|
if (!netWeight) {
|
this.setMsg(`${index}、净重不能为空`, "red");
|
}
|
if (!weight) {
|
this.setMsg(`${index}、毛重不能为空`, "red");
|
}
|
if (!postMailCode) {
|
this.setMsg(`${index}、行邮税号不能为空`, "red");
|
}
|
if (!hSCode) {
|
this.setMsg(`${index}、HSCode不能为空`, "red");
|
}
|
if (!declareCurrency) {
|
this.setMsg(`${index}、申报币种不能为空`, "red");
|
}
|
if (!bEDeclareCurrency) {
|
this.setMsg(`${index}、个物申报币种`, "red");
|
}
|
}
|
if (this.isMsgError) {
|
this.setMsg("-1");
|
return;
|
}
|
index = 0;
|
for (let row of dataList) {
|
index++;
|
let portName = row["口岸名称"];
|
let productCode = row["物料编号"];
|
let productModel = row["条形码"];
|
let cIQNameCn = row["申报品名"];
|
let typeName = row["类别"];
|
let productSpec = row["物料规格"];
|
let brandName = row["品牌"];
|
let originPlace = row["原产地"];
|
let placeCode = row["产地双字母代码"];
|
|
let netWeight = row["净重"];
|
let weight = row["毛重"];
|
let postMailCode = row["行邮税号"];
|
let hSCode = row["HSCode"];
|
let declareCurrency = row["申报币种"];
|
let bEDeclareCurrency = row["个物申报币种"];
|
let cIQName = row["英文名称"];
|
let declareQuantityOrder = row["申报数量"];
|
let declareUnit = row["申报单位"];
|
let declarePrice = row["申报价格"];
|
let englishType = row["英文类别"];
|
|
let statutoryUnit2 = row["第二法定单位"];
|
let statutoryQty2 = row["第二法定数量"];
|
|
let bondedDeclareUnit = row["保税申报单位"];
|
let cIQProductModel = row["物料海关编号"];
|
let recordCode = row["备案口岸代码"];
|
let manufacturer = row["生产厂家"];
|
let composition = row["材质成分"];
|
let usage = row["用途"];
|
let remark = row["备注"];
|
|
// #endregion
|
let tms_Port = await this.dbRead.findOne(TMSPort, {
|
portName: "" + portName
|
});
|
if (!tms_Port) {
|
msg += "<font color='red'>请在口岸管理中添加" + portName + ";</font><br/>";
|
} else {
|
let productInfo = await this.dbRead.findOne(BaseProductInfo, {
|
productCode: "" + productCode,
|
productModel: "" + productModel
|
});
|
if (!productInfo) {
|
msg += "<font color='red'>" + productCode + "在物料信息表中无此存在了，不允许导入;</font><br/>";
|
} else {
|
let tmsProductInfo_Port = await this.dbRead.findOne(TMSProductInfoPort, {
|
portName: "" + portName,
|
productCode: "" + productInfo.productCode
|
});
|
if (tmsProductInfo_Port) {
|
errorgengxinCount += 1;
|
await this.dbWrite.update(TMSProductInfoPort, tmsProductInfo_Port.portProduct_Id, {
|
port_Id: tms_Port.port_Id,
|
portName: tms_Port.portName,
|
product_Id: productInfo.product_Id,
|
productName: productInfo.productName,
|
productCode: productInfo.productCode,
|
productModel: productInfo.productModel,
|
cIQNameCn: cIQNameCn,
|
cIQName: cIQName,
|
typeName: typeName,
|
englishType: englishType,
|
productSpec: productSpec,
|
brandName: brandName,
|
originPlace: originPlace,
|
placeCode: placeCode,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
declareQuantityOrder: declareQuantityOrder,
|
declareUnit: declareUnit,
|
declarePrice: declarePrice,
|
declareCurrency: declareCurrency,
|
bEDeclareCurrency: bEDeclareCurrency,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
statutoryUnit2: statutoryUnit2,
|
statutoryQty2: Number(statutoryQty2),
|
bondedDeclareUnit: bondedDeclareUnit,
|
cIQProductModel: cIQProductModel,
|
recordCode: recordCode,
|
manufacturer: manufacturer,
|
composition: composition,
|
usage: usage,
|
remark: remark
|
});
|
} else {
|
msgCount += 1;
|
await this.dbWrite.insert(TMSProductInfoPort, {
|
port_Id: tms_Port.port_Id,
|
portName: tms_Port.portName,
|
product_Id: productInfo.product_Id,
|
productName: productInfo.productName,
|
productCode: productInfo.productCode,
|
productModel: productInfo.productModel,
|
cIQNameCn: cIQNameCn,
|
cIQName: cIQName,
|
typeName: typeName,
|
englishType: englishType,
|
productSpec: productSpec,
|
brandName: brandName,
|
originPlace: originPlace,
|
placeCode: placeCode,
|
netWeight: netWeight,
|
weight: weight,
|
postMailCode: postMailCode,
|
hSCode: hSCode,
|
declareQuantityOrder: declareQuantityOrder,
|
declareUnit: declareUnit,
|
declarePrice: declarePrice,
|
declareCurrency: declareCurrency,
|
bEDeclareCurrency: bEDeclareCurrency,
|
platUser_Id: userInfo.platUser_Id,
|
platUserName: userInfo.platUserName,
|
platUserCode: userInfo.platUserCode,
|
platCorpName: userInfo.platCorpName,
|
userProduct_Id: userInfo.userProduct_Id,
|
userProductCode: userInfo.userProductCode,
|
userProductAlias: userInfo.userProductAlias,
|
statutoryUnit2: statutoryUnit2,
|
statutoryQty2: Number(statutoryQty2),
|
bondedDeclareUnit: bondedDeclareUnit,
|
cIQProductModel: cIQProductModel,
|
recordCode: recordCode,
|
manufacturer: manufacturer,
|
composition: composition,
|
usage: usage,
|
remark: remark
|
});
|
}
|
}
|
}
|
}
|
msg += "导入成功" + msgCount + "条，导入更新成功" + errorgengxinCount + "条\r\n ";
|
this.setMsg(msg, "blue");
|
} catch (ex) {
|
this.setMsg("出现异常：" + ex.message + ex.StackTrace + ex.query, "red");
|
}
|
this.setMsg("-1");
|
}
|
//#endregion
|
}
|
