import BaseController from "../baseController";
|
import { Post } from "egg-shell-decorators";
|
import { TMSWayBillReceive } from "../../entity/express/tms/tmsWayBillReceive";
|
import { TMSWayBill } from "../../entity/express/tms/tmsWayBill";
|
import { TMSWayBillUpload } from "../../entity/express/tms/tmsWayBillUpload";
|
import { In, getConnection } from "typeorm";
|
// import { ResultInfo } from "../../public/commonInterface";
|
import { TMSPort } from "../../entity/express/tms/tmsPort";
|
import { BaseExpressCorp } from "../../entity/basicInfo/base/baseExpressCorp";
|
import { SysParamValue } from "../../entity/sys/core/sysParamValue";
|
import { SaleOrder } from "../../entity/outbound/sale/saleOrder";
|
import { BaseExpressSpareCode } from "../../entity/express/spare/baseExpressSpareCode";
|
import { ExpressSpareOrder } from "../../entity/express/spare/expressSpareOrder";
|
import { TmsGoodsRegion } from "../../entity/express/panel/tmsGoodsRegion";
|
import { TMSWayBillList } from "../../entity/express/tms/tmsWayBillList";
|
import * as XLSX from "xlsx";
|
import * as path from "path";
|
import { TMSWayBillTracking } from "../../entity/express/tms/tmsWayBillTracking";
|
import { BaseProductInfo } from "../../entity/basicInfo/base/baseProductInfo";
|
import { vTMSWayBillList } from "../../entity/express/tms/vtmsWayBillList";
|
import * as xlsxStye from "xlsx-style";
|
import * as mssql from "mssql";
|
/**
|
* 运单管理
|
*/
|
export default class WayBillController extends BaseController {
|
//#region 运单客户开启
|
//运单客户端开启
|
@Post()
|
public async multiBatchOpen() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
if (!Array.isArray(body.ids)) {
|
this.info.result = false;
|
this.info.msg = "没有可执行的数据！";
|
return this.info;
|
}
|
try {
|
await this.dbWrite.update(
|
TMSWayBill,
|
{
|
wayBill_Id: In(body.ids)
|
},
|
{
|
orderStatus: "新建"
|
}
|
);
|
this.info.result = true;
|
this.info.msg = "开启成功！";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 运单客户端终止
|
//运单客户端终止
|
@Post()
|
public async multiBatchStop() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
if (!Array.isArray(body.ids)) {
|
this.info.result = false;
|
this.info.msg = "没有可执行的数据！";
|
return;
|
}
|
try {
|
await this.dbWrite.update(
|
TMSWayBill,
|
{
|
wayBill_Id: In(body.ids)
|
},
|
{
|
orderStatus: "终止"
|
}
|
);
|
this.info.result = true;
|
this.info.msg = "终止成功！";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region ModifyplateCode 修改组板号
|
/// <summary>
|
/// 修改组板号
|
/// </summary>
|
/// <param name="body"></param>
|
/// <returns></returns>
|
@Post()
|
public async modifyplateCode() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
if (!body.code) {
|
this.info.result = false;
|
this.info.msg = "组板号不能为空";
|
ctx.body = this.info;
|
return;
|
}
|
let wayBillList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(body.ids)
|
});
|
|
await this.dbWrite.update(
|
TMSWayBill,
|
{
|
wayBill_Id: In(body.ids)
|
},
|
{
|
plateCode: body.code
|
}
|
);
|
|
// 轨迹数据
|
let plateCode = "";
|
for (let wayBillInfo of wayBillList) {
|
plateCode = wayBillInfo.plateCode;
|
await ctx.service.tms.wayBillHelper.setStatusHistory(wayBillInfo, "修改组板号", "修改组板号为" + body.code);
|
}
|
|
let redis = this.ctx.app.redis.clients.get("fenjianji");
|
//清除redis缓存
|
try {
|
if (plateCode != null) {
|
var key = plateCode + "_*";
|
var keyList = await redis.keys(key);
|
await redis.del(...keyList);
|
this.logger.info(plateCode + "清空redis成功");
|
} else {
|
this.logger.info(body.code + "的原托盘号不存在");
|
}
|
} catch (ex) {
|
await this.ctx.service.common.errorLog("modifyplateCode", "清空redis失败：" + ex.message + "，托盘号：" + plateCode);
|
}
|
|
this.info.result = true;
|
this.info.msg = "托盘号修改成功";
|
} catch (ex) {
|
this.info.msg = ex.message;
|
this.info.result = false;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 获取晋江分拣码
|
/// <summary>
|
/// 获取晋江分拣码
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async getSortingCode() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
let wayBillInfo = await this.dbRead.findOne(TMSWayBill, body.wayBill_Id);
|
let info = await ctx.service.express.sortingCodeHelper.getSortingCode(body.wayBill_Id); // = SortingCodeService.GetSortingCode(wayBillInfo);
|
if (info.result) {
|
var sortingCode = info.dynamic;
|
wayBillInfo.bigPen = sortingCode.data.SortingCode;
|
this.info.result = true;
|
this.info.msg = "获取分拣码完成。";
|
} else {
|
this.info.result = false;
|
this.info.msg = "获取分拣码失败。";
|
}
|
if (wayBillInfo.apiSendCount == null) {
|
wayBillInfo.apiSendCount = 1;
|
} else {
|
wayBillInfo.apiSendCount += 1;
|
}
|
await this.dbWrite.update(TMSWayBill, wayBillInfo.wayBill_Id, {
|
apiSendCount: ++wayBillInfo.apiSendCount,
|
bigPen: wayBillInfo.bigPen
|
});
|
} catch (ex) {
|
this.info.msg = ex.message;
|
this.info.result = false;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 查询面单图片
|
@Post()
|
public async getWayBillPruture() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
let waybillupload = await this.dbRead.findOne(TMSWayBillUpload, {
|
wayBillCode: body.wayBillCode
|
});
|
if (waybillupload != null) {
|
this.info.result = true;
|
this.info.data = {
|
filePath: waybillupload.filePath
|
};
|
} else {
|
this.info.result = false;
|
this.info.msg = "面单不存在";
|
}
|
} catch (ex) {
|
this.info.msg = ex.message;
|
this.info.result = false;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 运单快递统计导出
|
@Post()
|
public async exportBillCodeCont() {
|
let { ctx } = this;
|
let where = await ctx.service.common.getWhere();
|
// let body = ctx.request.body;
|
try {
|
let fieldStr = ` voyageCode AS '航空主单号',wayBillCode AS '运单号',expressCode AS '国内快递单号',consigneeName AS '收货人',consigneeMobile AS '收货人电话',consigneeAddress AS '收货人详细地址',
|
(select stuff((select ';'+productName from (SELECT L.productName AS productName FROM TMS_wayBillList AS L WITH(nolock)
|
Where T.wayBill_Id=L.wayBill_Id) AS T for xml path ('')),1,1,'')) AS '物料名称',T.totalQuantityOrder AS '物料数量',
|
GrossWeight AS '包裹毛重(kg)'`;
|
let fields = fieldStr.split(",");
|
|
let wayBillList: Array<any> = await this.dbRead.createQueryBuilder(TMSWayBill, "T").select(fields).where(where).take(30000).getRawMany();
|
|
let root = path.resolve();
|
let url = "/download/运单快递统计导出.xlsx";
|
let fileName = root + url.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
let jsonWorkSheetquanzhou = XLSX.utils.json_to_sheet(wayBillList);
|
|
// 构造workBook
|
let workBook1 = {
|
SheetNames: ["快递统计导出"],
|
Sheets: {
|
快递统计导出: jsonWorkSheetquanzhou
|
}
|
};
|
XLSX.writeFile(workBook1, fileName);
|
|
this.info.data = {
|
url: url
|
};
|
this.info.result = true;
|
this.info.msg = "导出成功！";
|
|
this.info.result = true;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 晋江口岸导出
|
/// <summary>
|
/// </summary>
|
/// <param name="body"></param>
|
/// <returns></returns>
|
@Post()
|
public async exportjinjiangDeclaration() {
|
let { ctx } = this;
|
// let body = ctx.request.body;
|
try {
|
let where: any = await ctx.service.common.getWhere();
|
//#region 申报数据(晋江口岸)
|
// let path = HttpContext.Current.Server.MapPath("/Upload/ImportTemplate/申报数据.xlsx");
|
//指定文档
|
//#region Sheet1 陆地港数据源
|
let fieldStr = `
|
ROW_NUMBER() OVER(ORDER BY WayBillCode ) AS '序号', '' AS '总运单号',MAX(expressCode) AS '分运单号', '厦门通宇' AS '快件公司','' AS '航班','' AS '航次','' AS '进口日期','' AS '到达卸货地时间',MAX(HSCode) AS 'HS编码',
|
(select stuff((select ';'+CIQNameCn from (SELECT (P.CIQNameCn + '*' +CONVERT(VARCHAR, L.quantityOrder) ) AS CIQNameCn FROM TMS_WayBill AS W WITH(nolock) LEFT JOIN
|
TMS_wayBillList AS L WITH(nolock) ON W.wayBill_Id = L.wayBill_Id LEFT JOIN
|
TMS_ProductInfo_Port AS P WITH(nolock) ON L.product_Id = P.product_Id
|
WHERE W.port_Id = 6 AND W.wayBillCode=T.wayBillCode AND P.port_Id=6) AS T for xml path ('')),1,1,'')) AS '货物品名',
|
'普通物品' AS '物料类别', 1 AS '货物件数',MAX(T.GrossWeight) AS '货物毛重','WANFU' AS '发件人','auburn' AS '发件地址','澳大利亚' AS '起运地',
|
MAX(consigneeName) AS '收件人',MAX(consigneeAddress) AS '收件地址',SUM((P.DeclarePrice * TL.quantityOrder)) AS '货物价值','' AS '体积','RMB' AS '币别','B类' AS '货物类别','厦门' AS '进境口岸'`;
|
|
let fields = fieldStr.split(",");
|
let Ludigang: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "T")
|
.select(fields)
|
.innerJoin("TMSWayBillList", "TL", "T.wayBill_Id = TL.wayBill_Id")
|
.leftJoin("TMSProductInfoPort", "P", "TL.product_Id = P.product_Id")
|
.where(where)
|
.andWhere("T.portName = '泉州口岸' AND P.port_Id =6")
|
.take(5000)
|
.groupBy("wayBillCode, T.port_Id,expressCode")
|
.orderBy({
|
wayBillCode: "ASC",
|
expressCode: "ASC"
|
})
|
.getRawMany();
|
//#endregion
|
|
//#region SHeet2 报关申报清单
|
fieldStr = `
|
DENSE_RANK() OVER(order by W.WayBillCode) as '序号',
|
CASE WHEN W.expressCode IS NULL THEN '' ELSE W.expressCode END as '分运单号','WANFU' AS '发件人',W.consigneeName as '收件人','B' AS '报关类别',
|
(CASE WHEN P.PostMailCode ='01010700' THEN P.CIQNameCn + '*' + CONVERT(VARCHAR,L.quantityOrder)+B.smallUnit ELSE P.CIQNameCn + '*' + CONVERT(VARCHAR,L.quantityOrder) END ) AS '货物品名',
|
P.PostMailCode as '物料编码',P.productSpec as '物料规格、型号', P.Weight*L.quantityOrder AS '毛/净重KG', L.quantityOrder AS '件数','纸箱' AS '包装种类',
|
(CASE WHEN P.PostMailCode IN ('01010700','01010800','01020100','01020200') THEN (P.NetWeight * L.quantityOrder) ELSE L.quantityOrder END) AS '申报数量',
|
P.DeclareUnit as '申报计量单位','' AS '第一（法定）数量','' AS '第一（法定）计量单位',P.DeclarePrice as '单价',142 AS '币制',0 AS '申报单位类别','' AS '监管方式',
|
'' AS '征免性质分类','' AS '成交方式','' AS '征减免税方式','身份证' AS '收发件人证件类型',W.consigneeIdcard as '收发件人证件号','3502480005' AS '申报单位代码',
|
'厦门通宇报关有限公司' AS '申报单位名称','372301' AS '码头/货场代码','601' AS '发件人国别','auburn' AS '发件人城市',P.CIQName as '英文品名',W.cityName as'收件人城市',W.consigneeAddress as '收件人地址',W.consigneeMobile as '收件人电话',
|
'' AS '运费币制' ,'' AS '运费/率','' AS '保险费标记','' AS '保险费币制','' AS '保险费/率' , '' AS '杂费标记','' AS '杂费币制','' AS '杂费/率','' AS '备注','' AS '用途',
|
'' AS '第二数量','' AS '第二计量单位','' AS '关联编号字段'`;
|
fields = fieldStr.split(",");
|
|
let Baoguandate: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "W")
|
.select(fields)
|
.innerJoin("TMSWayBillList", "L", "W.wayBill_Id = L.wayBill_Id")
|
.leftJoin("TMSProductInfoPort", "P", "L.product_Id = P.product_Id And W.port_Id=P.port_Id")
|
.leftJoin("BaseProductInfo", "B", "B.product_Id = L.product_Id")
|
.where(where)
|
.andWhere("W.portName = '泉州口岸' AND W.port_Id =6 AND P.port_Id =6")
|
.take(5000)
|
.orderBy({
|
wayBillCode: "ASC",
|
expressCode: "ASC"
|
})
|
.getRawMany();
|
//#endregion
|
|
//#region Sheet3 报检-厦门通宇物流有限公司数据导入模板
|
fieldStr = `
|
ROW_NUMBER() OVER(ORDER BY wayBillCode ) AS '序号','' AS '总单号(最多16位)', CASE WHEN MAX(expressCode) IS null THEN '' ELSE MAX(expressCode) END AS '分运单号(最多20位)','WANFU' AS '发货人',MAX(consigneeName) AS '收货人', 'E' AS '申报类别(必须填写E)', 1 AS '件数',
|
(select stuff((select ';'+CIQNameCn from (SELECT (P.CIQNameCn +'*' +CONVERT(VARCHAR, L.quantityOrder) ) AS CIQNameCn FROM TMS_WayBill AS W WITH(nolock) LEFT JOIN
|
TMS_wayBillList AS L WITH(nolock) ON W.wayBill_Id = L.wayBill_Id LEFT JOIN
|
TMS_ProductInfo_Port AS P WITH(NOLOCK) ON L.product_Id = P.product_Id
|
WHERE W.port_Id = T.port_Id AND W.wayBillCode=T.wayBillCode AND P.port_Id =6) AS T FOR XML PATH ('')),1,1,'')) AS '货物品名',
|
MAX(T.GrossWeight) AS '重量KG',SUM((PT.DeclarePrice * TL.quantityOrder)) AS '总价','142' AS '币种','4M' AS '包装种类(最多4位)','601' AS '原产国（最多6位）','15667487' AS '码头代码（必填,最多12位）',
|
'399100' AS '接收方代码','' AS '货物英文名称',1 AS '证件类型',MAX(consigneeIdcard) AS '证件号',MAX(consigneeMobile) AS '收件人电话','142' AS '收件人国别（最多6位）',
|
MAX(cityName) AS '收件人城市',MAX(consigneeAddress) AS '收件人地址',NULL AS '货主单位名称',MAX(HSCode) AS '物料编码','601' AS '发件人国别（最多6位）','auburn' AS '发件人城市','3010' AS '监管方式(最多6位)','1' AS '成交方式(最多2位)',MAX(PT.productSpec) AS '物料规格、型号',
|
'auburn' AS '货主城市','' AS '经营单位代码','' AS '经营单位名称'`;
|
fields = fieldStr.split(",");
|
|
let Tywl: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "T")
|
.select(fields)
|
.innerJoin("TMSWayBillList", "TL", "T.wayBill_Id = TL.wayBill_Id")
|
.leftJoin("TMSProductInfoPort", "PT", "TL.product_Id = PT.product_Id")
|
.where(where)
|
.andWhere("T.portName = '泉州口岸' AND T.port_Id =6 AND PT.port_Id =6")
|
.take(5000)
|
.groupBy(" wayBillCode, T.port_Id ")
|
.orderBy({
|
wayBillCode: "ASC",
|
expressCode: "ASC"
|
})
|
.getRawMany();
|
//#endregion
|
let root1 = path.resolve();
|
let url2 = "/download/申报数据.xlsx";
|
let fileName = root1 + url2.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
|
let jsonWorkSheetLudig = XLSX.utils.json_to_sheet(Ludigang);
|
let arrayWorkSheet = XLSX.utils.json_to_sheet(Baoguandate);
|
let jsonWorkSheetTywl = XLSX.utils.json_to_sheet(Tywl);
|
// 构造workBook
|
let workBook = {
|
SheetNames: ["陆地港进口晋江快件中心货物清单", "报关申报清单", "报检厦门通宇物流有限公司数据导入模板"],
|
Sheets: {
|
陆地港进口晋江快件中心货物清单: jsonWorkSheetLudig,
|
报关申报清单: arrayWorkSheet,
|
报检厦门通宇物流有限公司数据导入模板: jsonWorkSheetTywl
|
}
|
};
|
// 设置列宽
|
jsonWorkSheetLudig["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
arrayWorkSheet["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
jsonWorkSheetTywl["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
// 设置自定义单元格值
|
let worksheet = workBook.Sheets["陆地港进口晋江快件中心货物清单"];
|
let worksheet2 = workBook.Sheets["报关申报清单"];
|
let worksheet3 = workBook.Sheets["报检厦门通宇物流有限公司数据导入模板"];
|
// 设置内容格式
|
let rowCount = Ludigang.length;
|
let setStyle = (cell, horizontal) => {
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: horizontal
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
};
|
for (let rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
|
for (let colIndex = 0; colIndex < 23; colIndex++) {
|
let col = XLSX.utils.encode_col(colIndex); // 转为字母
|
let cellIndex = col + (0 + rowIndex);
|
let cell = worksheet[cellIndex];
|
if (!cell) continue;
|
|
if (colIndex == 11) {
|
setStyle(cell, "left");
|
}
|
}
|
}
|
// 设置标题格式
|
for (let index = 0; index < 23; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
}
|
// 设置内容格式
|
rowCount = Baoguandate.length;
|
setStyle = (cell, horizontal) => {
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: horizontal
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
};
|
for (let rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
|
for (let colIndex = 0; colIndex < 23; colIndex++) {
|
let col = XLSX.utils.encode_col(colIndex); // 转为字母
|
let cellIndex = col + (0 + rowIndex);
|
let cell = worksheet[cellIndex];
|
if (!cell) continue;
|
|
if (colIndex == 17) {
|
setStyle(cell, "left");
|
}
|
if (colIndex == 18) {
|
setStyle(cell, "left");
|
}
|
}
|
}
|
// 设置标题格式
|
for (let index = 0; index < 46; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet2[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
}
|
// 设置内容格式
|
rowCount = Tywl.length;
|
setStyle = (cell, horizontal) => {
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: horizontal
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
};
|
for (let rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
|
for (let colIndex = 0; colIndex < 23; colIndex++) {
|
let col = XLSX.utils.encode_col(colIndex); // 转为字母
|
let cellIndex = col + (0 + rowIndex);
|
let cell = worksheet[cellIndex];
|
if (!cell) continue;
|
|
if (colIndex == 7) {
|
setStyle(cell, "left");
|
}
|
}
|
}
|
// 设置标题格式
|
for (let index = 0; index < 32; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet3[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
}
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook, fileName, wopts);
|
|
// XLSX.writeFile(workBook, fileName);
|
|
//#region 泉州派送数据
|
// let path2 = HttpContext.Current.Server.MapPath("/Upload/ImportTemplate/泉州派送数据.xlsx");
|
|
fieldStr = `
|
ROW_NUMBER() OVER(ORDER BY W.WayBillCode) AS '序号',CASE WHEN W.ExpressCode IS NULL THEN '' ELSE W.ExpressCode END AS '邮政单号', W.ConsigneeName as '收件人', 1 AS '件数', '' AS '货物品名', W.GrossWeight as '总量(KG)',
|
W.ConsigneeMobile as '收件人电话', (W.ProvinceName + W.CityName + W.RegionName + W.ConsigneeAddress) AS '收件人地址'`;
|
fields = fieldStr.split(",");
|
let wayBilPslList: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "W")
|
.select(fields)
|
.where(where)
|
.andWhere("W.portName = '泉州口岸' AND W.port_Id = 6")
|
.take(5000)
|
// .orderBy({
|
// expressCode: "DESC"
|
// })
|
.getRawMany();
|
//#endregion
|
let root = path.resolve();
|
let url = "/download/泉州派送数据.xlsx";
|
fileName = root + url.replace(/\//g, path.sep);
|
pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
let jsonWorkSheetquanzhou = XLSX.utils.json_to_sheet(wayBilPslList);
|
|
// 构造workBook
|
let workBook1 = {
|
SheetNames: ["泉州派送数据"],
|
Sheets: {
|
泉州派送数据: jsonWorkSheetquanzhou
|
}
|
};
|
// 设置列宽
|
jsonWorkSheetquanzhou["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
// 设置自定义单元格值
|
worksheet = workBook1.Sheets["泉州派送数据"];
|
// 设置标题格式
|
for (let index = 0; index < 8; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
}
|
for (let index = 5; index < 8; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
color: { rgb: "EE3B3B" },
|
name: "Arial"
|
}
|
};
|
}
|
// XLSX.writeFile(workBook1, fileName);
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook1, fileName, wopts);
|
|
this.info.data = {
|
url: url,
|
url2: url2
|
};
|
this.info.result = true;
|
this.info.msg = "导出成功！";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
console.log("错误信息：" + ex.query);
|
}
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
//#endregion
|
|
//#region 福州口岸
|
/// <summary>
|
/// </summary> 福州口岸
|
/// <param name="body"></param>
|
/// <returns></returns>
|
//#region 福州口岸
|
@Post()
|
public async exportFuzhouDeclaration() {
|
let { ctx } = this;
|
// let body = ctx.request.body;
|
try {
|
let where: any = await ctx.service.common.getWhere();
|
//#region 福州口岸派送数据
|
let fieldStr = `
|
W.wayBillCode as '订单号', W.expressCode AS '邮件号',3 AS '内件性质',W.GrossWeight as '重量(克)',W.consigneeName as '收件人姓名',W.consigneeAddress as '收件人街道',W.consigneeMobile as '收件人电话2',
|
'BL' AS '寄件人姓名','0061287103969' AS '寄件人电话2','syd' AS '寄件人街道','3' AS '运输方式'
|
`;
|
let fields = fieldStr.split(",");
|
where.portName = "福州口岸";
|
|
let wayBillList: Array<any> = await this.dbRead.createQueryBuilder(TMSWayBill, "W").select(fields).where(where).take(5000).getRawMany();
|
|
let root = path.resolve();
|
let url = "/download/福州派送数据.xlsx";
|
let fileName = root + url.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
|
let jsonWorkSheet1 = XLSX.utils.json_to_sheet(wayBillList);
|
// 构造workBook
|
let workBook = {
|
SheetNames: ["普通业务"],
|
Sheets: {
|
普通业务: jsonWorkSheet1
|
}
|
};
|
// 设置列宽
|
jsonWorkSheet1["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 100 }, //3
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 180 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 }
|
];
|
let worksheet1 = workBook.Sheets["普通业务"];
|
|
// 设置内容格式
|
let rowCount = wayBillList.length;
|
let setStyle = (cell, horizontal) => {
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: horizontal
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
};
|
for (let rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
|
for (let colIndex = 0; colIndex < 11; colIndex++) {
|
let col = XLSX.utils.encode_col(colIndex); // 转为字母
|
let cellIndex = col + (1 + rowIndex);
|
let cell = worksheet1[cellIndex];
|
if (!cell) continue;
|
|
if (colIndex == 5) {
|
setStyle(cell, "left");
|
} else {
|
setStyle(cell, "center");
|
}
|
}
|
}
|
|
// 设置标题格式
|
for (let index = 0; index < 11; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet1[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: true,
|
name: "宋体"
|
},
|
fill: {
|
fgColor: {
|
rgb: "ff0000" // 单元格背景颜色
|
}
|
}
|
};
|
}
|
worksheet1["C1"] = {
|
v: "内件性质",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
color: { rgb: "0D0D0D" }, // 字体颜色
|
name: "宋体"
|
},
|
alignment: {
|
//垂直水平居中
|
vertical: "center",
|
horizontal: "center"
|
},
|
fill: {
|
fgColor: {
|
rgb: "218868" // 单元格背景颜色
|
}
|
}
|
}
|
};
|
|
// 设置标题格式
|
for (let index = 8; index < 11; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet1[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: true,
|
color: { rgb: "0D0D0D" }, // 字体颜色
|
name: "宋体"
|
},
|
fill: {
|
fgColor: {
|
rgb: "218868" // 单元格背景颜色
|
}
|
}
|
};
|
}
|
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook, fileName, wopts);
|
// XLSX.writeFile(workBook, fileName);
|
//#endregion
|
|
//#region 报关数据(福州口岸)
|
fieldStr = `
|
DENSE_RANK() OVER(order by W.wayBillCode) as 序号, W.expressCode as '分运单号', CASE WHEN P.CIQNameCn IS NULL THEN K.ProductName ELSE P.CIQNameCn END AS '货物品名',
|
CASE WHEN P.BrandName IS NULL THEN K.BrandName ELSE P.BrandName END AS '品牌', 1 AS '件数',
|
(Case when P.TypeName = '奶粉' THEN(L.QuantityOrder * 1.2) ELSE(CASE WHEN P.Weight IS NULL THEN K.Weight ELSE P.Weight end) * L.QuantityOrder END) AS '毛重(提单重量)',
|
(L.QuantityOrder * 1.2) - 0.1 AS '净重(实际重量)',
|
(CASE WHEN P.TypeName = '鞋靴' THEN(P.DeclareQuantityOrder * L.QuantityOrder)
|
WHEN P.TypeName = '奶粉' THEN((CASE WHEN P.NetWeight IS NULL THEN K.NetWeight ELSE P.NetWeight end) * L.QuantityOrder) ELSE(L.QuantityOrder) END) AS '数量',
|
L.QuantityOrder as '实际数量', CASE WHEN P.DeclareUnit IS NULL THEN K.DeclareUnit ELSE P.DeclareUnit END AS '单位',
|
'RMB' AS '货币编码', P.DeclarePrice as '单价', CASE WHEN P.PostMailCode IS NULL THEN K.PostMailCode ELSE P.PostMailCode END AS '个人完税税号', CASE WHEN P.ProductSpec IS NULL THEN K.ProductSpec ELSE P.ProductSpec END AS '型号', '601' AS '国别代码', '澳大利亚' AS '原产国',
|
CASE WHEN P.HSCode IS NULL THEN K.HSCode ELSE P.HSCode END AS 'HS编码', W.ConsigneeIdcard as '收件人ID', W.consigneeName as '收件人',
|
REPLACE(REPLACE(REPLACE( REPLACE(REPLACE( W.consigneeAddress,'中国',''),'北京市北京市','北京市'),'上海市上海市','上海市'),'天津市天津市','天津市'),'重庆市重庆市','重庆市') as '地址',
|
W.consigneeMobile as '收件人电话',
|
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( W.provinceName,'省',''),'市',''),'维吾尔自治区',''),'特别行政区',''),'壮族自治区',''),'回族自治区',''),'中国',''),'自治区','') AS 'TO',
|
W.expressCode as '落地配单号', 'AUOD PTY LTD' as '寄件人公司', 'AUOD' AS '寄件人', 'auburn' AS '寄件人地址', '0061287103969' AS '寄件人电话', 'SYD' AS 'FROM',
|
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( W.provinceName,'省',''),'市',''),'维吾尔自治区',''),'特别行政区',''),'壮族自治区',''),'回族自治区',''),'中国',''),'自治区','') as '货主城市'`;
|
fields = fieldStr.split(",");
|
where.portName = "福州口岸";
|
let wayBillBgList: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "W")
|
.select(fields)
|
.innerJoin("TMSWayBillList", "L", "W.wayBill_Id = L.wayBill_Id")
|
.leftJoin("BaseProductInfo", "K", "L.product_Id = K.product_Id")
|
.leftJoin("TMSProductInfoPort", "P", "L.product_Id = P.product_Id And W.port_Id=P.port_Id")
|
.where(where)
|
.take(5000)
|
.andWhere("W.portName = '福州口岸' AND W.port_Id = 5")
|
.getRawMany();
|
|
root = path.resolve();
|
let url2 = "/download/福州报关数据.xlsx";
|
fileName = root + url2.replace(/\//g, path.sep);
|
pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
|
let opts = {
|
cellStyles: true,
|
origin: 5 // 跳过前N行
|
};
|
|
let jsonWorkSheet = XLSX.utils.json_to_sheet(wayBillBgList, opts);
|
// 构造workBook
|
let workBook1 = {
|
SheetNames: ["报关数据"],
|
Sheets: {
|
报关数据: jsonWorkSheet
|
}
|
};
|
// 设置行高
|
jsonWorkSheet["!rows"] = [
|
{
|
hpx: 50
|
}
|
];
|
// 设置列宽
|
jsonWorkSheet["!cols"] = [
|
//设置宽度
|
{ wpx: 80 }, //1-第一列
|
{ wpx: 80 }, //2-第二列
|
{ wpx: 140 }, //3
|
{ wpx: 100 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 200 },
|
{ wpx: 100 },
|
{ wpx: 80 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 80 },
|
{ wpx: 80 }
|
];
|
|
// 设置自定义单元格值
|
let worksheet = workBook1.Sheets["报关数据"];
|
|
// 设置内容格式
|
rowCount = wayBillBgList.length;
|
setStyle = (cell, horizontal) => {
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: horizontal
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
};
|
for (let rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
|
for (let colIndex = 0; colIndex < 29; colIndex++) {
|
let col = XLSX.utils.encode_col(colIndex); // 转为字母
|
let cellIndex = col + (6 + rowIndex);
|
let cell = worksheet[cellIndex];
|
if (!cell) continue;
|
|
if (colIndex == 19) {
|
setStyle(cell, "left");
|
} else {
|
setStyle(cell, "center");
|
}
|
}
|
}
|
|
worksheet["A1"] = {
|
v: "晋江吉迅供应链管理有限公司——申报清单(包裹)",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
vertical: "center"
|
}
|
}
|
};
|
worksheet["A2"] = {
|
v: "目的地：",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
//垂直水平居中
|
vertical: "center",
|
horizontal: "center"
|
}
|
}
|
};
|
worksheet["C2"] = {
|
v: "客户",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
//垂直水平居中
|
vertical: "center",
|
horizontal: "center"
|
}
|
}
|
};
|
worksheet["C3"] = {
|
v: "航空件数",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
color: { rgb: "FF0000" }, // 字体颜色
|
name: "Arial"
|
},
|
alignment: {
|
//垂直水平居中
|
vertical: "center",
|
horizontal: "center"
|
}
|
}
|
};
|
worksheet["D2"] = {
|
v: "吉迅",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
//垂直水平居中
|
vertical: "center",
|
horizontal: "center"
|
}
|
}
|
};
|
worksheet["A3"] = {
|
v: "主提单号：",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
//垂直水平居中
|
vertical: "center",
|
horizontal: "center"
|
}
|
}
|
};
|
worksheet["A4"] = {
|
v: "航班号：",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
//垂直水平居中
|
vertical: "center",
|
horizontal: "center"
|
}
|
}
|
};
|
worksheet["A5"] = {
|
v: "航班日期：",
|
s: {
|
font: {
|
sz: 10,
|
bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
//垂直水平居中
|
vertical: "center",
|
horizontal: "center"
|
}
|
}
|
};
|
|
// 设置标题格式
|
for (let index = 0; index < 29; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "6"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
},
|
fill: {
|
fgColor: {
|
rgb: "ffff33" // 单元格背景颜色
|
}
|
},
|
border: {
|
bottom: {
|
// 底线颜色
|
style: "thin",
|
color: { rgb: "989799" }
|
}
|
}
|
};
|
}
|
|
/* bookType can be 'xlsx' or 'xlsm' or 'xlsb' */
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook1, fileName, wopts);
|
//#endregion
|
this.info.result = true;
|
this.info.data = {
|
url: url,
|
url2: url2
|
};
|
this.info.msg = "导出成功!";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
|
//#endregion
|
//#endregion
|
|
//#region 北京口岸运单数据
|
/// <summary>
|
/// </summary> 北京口岸导出
|
/// <param name="body"></param>
|
/// <returns></returns>
|
@Post()
|
public async exportBeijinDeclaration() {
|
let { ctx } = this;
|
// let body = ctx.request.body;
|
try {
|
// let sql = "";
|
///#region 北京运单数据
|
let where = await ctx.service.common.getWhere();
|
let fieldStr = `W.wayBillCode as '订单编号',W.voyageCode as '主单号',W.expressCode as '物流运单编号','天津志嘉供应链管理有限公司' AS '跨境商户企业名称','12109402A8' AS '跨境商户企业代码','0127' AS '主管海关代码',
|
'110101' AS '施检机构代码',
|
CONVERT(varchar, W.GroupBoardDate, 120 ) as '业务时间', '1111980076' AS '物流企业代码','北京昊运联合国际货运代理有限公司'AS '物流企业名称','1111980076' AS '担保企业代码',
|
'1111980076' AS '申报企业代码','' AS '备注','0' AS '运费','0' AS '保价费','142' AS '币种',W.GrossWeight as '毛重',W.totalQuantityOrder as '物料数量','1' AS '件数','飞机' AS '运输工具名称','CA174' AS '航班航次号',
|
'1111660005' AS '监管场所代码','11010102' AS '货物存放地点代码','0142' AS '指运港代码',NULL AS '经停港代码','601' AS '起运港代码',NULL AS '主要货物信息',NULL AS '许可证件号','BL'AS '发货人名称',
|
'BL' AS '发货人地址','0061287103969' AS '发货人电话','601' AS '发货人所在国家(地区)代码',W.consigneeName as '收货人名称',W.provinceName+W.cityName+W.regionName+W.consigneeAddress AS '收货人地址',W.consigneeMobile as '收货人电话',
|
'142' AS '收货人所在国家(地区)代码','5' AS '运输方式代码','2' AS '包装类型代码','601' AS '起运国/运抵国（地区）代码', CONVERT(varchar, W.GroupBoardDate, 120) as '进出境日期','I' AS '进出口标识',NULL AS '错误信息(系统生成不需要填写)'`;
|
let fields = fieldStr.split(",");
|
|
let wayBillList: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "W")
|
.select(fields)
|
.where(where)
|
.take(5000)
|
.andWhere("W.portName = '北京口岸' AND W.port_Id = 2")
|
.getRawMany();
|
|
let root = path.resolve();
|
let url = "/download/北京运单数据.xlsx";
|
let fileName = root + url.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
|
let jsonWorkSheet = XLSX.utils.json_to_sheet(wayBillList);
|
// 构造workBook
|
let workBook = {
|
SheetNames: ["运单数据"],
|
Sheets: {
|
运单数据: jsonWorkSheet
|
}
|
};
|
// 设置行高
|
jsonWorkSheet["!rows"] = [
|
{
|
hpx: 13
|
}
|
];
|
// 设置列宽
|
jsonWorkSheet["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
// 设置自定义单元格值
|
let worksheet = workBook.Sheets["运单数据"];
|
// 设置标题格式
|
for (let index = 0; index < 42; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 12,
|
bold: false
|
}
|
};
|
}
|
// XLSX.writeFile(workBook, fileName);
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook, fileName, wopts);
|
|
//#region 北京口岸订单数据模板
|
fieldStr = `W.wayBillCode as '订单编号',W.voyageCode as '主单号',W.expressCode as '物流运单编号',NULL AS '国际条码','0127' AS '主管海关代码','0127' AS '口岸海关代码',
|
CONVERT(varchar, Max(W.AuditDate), 120) AS '业务时间', 'I' AS '进出口标识','12109402A8' AS '电商服务平台代码',
|
'天津志嘉供应链管理有限公司' AS '电商服务平台名称', '12109402A8' AS '跨境商户企业代码','天津志嘉供应链管理有限公司' AS '跨境商户企业名称',MAX(W.consigneeMobile) AS '订购人注册号',MAX(W.consigneeName) AS '订购人姓名','1' AS '订购人证件类型',
|
MAX(W.consigneeIdcard) AS '订购人证件号码',MAX(W.consigneeMobile) AS '订购人电话','9610' AS '贸易方式','' AS '备注',SUM((L.quantityOrder) * (P.DeclarePrice)) AS '总费用',SUM((L.quantityOrder) * (P.DeclarePrice)) AS '物料货款','0' AS '其他杂费',
|
'0.0' AS '非现金抵扣金额','0.0' AS '代扣税款','142' AS '币制',NULL AS '支付企业代码',NULL AS '支付企业名称',NULL AS '支付单编号',NULL AS '物料批次号',NULL AS '账册编号',NULL AS '区内企业代码',NULL AS '区内企业名称',SUM(P.NetWeight*L.quantityOrder) AS '净重','1111980076' AS '物流企业代码', '北京昊运联合国际货运代理有限公司'AS '物流企业名称',
|
'BL'AS '发货人中文名',NULL AS '发货人英文名','auburn' AS '发货人城市','auburn' AS '发货人地址','0061287103969' AS '发货人电话','601' AS '发货人所在国家(地区)代码',MAX(W.consigneeName) AS '收货人中文名',Null AS '收货人英文名',MAX(W.consigneeMobile) AS '收货人电话',
|
'142' AS '收货人所在国家（地区）代码',MAX(W.provinceName) AS '收货人省份',MAX(W.cityName) AS '收货人城市',MAX(W.regionName) AS '收货人区',MAX(W.street) AS '收货人街道',MAX(W.consigneeAddress) AS '收货人详细地址',NULL AS '收货地址行政区划代码',NULL AS '国内物流企业代码',NULL AS '错误信息(系统生成不需要填写)'`;
|
fields = fieldStr.split(",");
|
|
let wayBillLists: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "W")
|
.select(fields)
|
.innerJoin("TMSWayBillList", "L", "W.wayBill_Id = L.wayBill_Id")
|
.leftJoin("TMSProductInfoPort", "P", "L.product_Id = P.product_Id And W.port_Id=P.port_Id")
|
.where(where)
|
.take(5000)
|
.andWhere("W.portName = '北京口岸' AND W.port_Id = 2")
|
.groupBy(" wayBillCode, voyageCode, expressCode")
|
.getRawMany();
|
//#region sheet2 数据源
|
fieldStr = `W.wayBillCode as '订单编号', ROW_NUMBER() over( partition by L.WayBill_Id order by L.WayBill_Id ) AS '物料序号',CASE WHEN P.productCode IS NULL THEN K.productCode ELSE P.productCode END AS '物料货号',
|
CASE WHEN P.productName IS NULL THEN K.productName ELSE P.productName END AS '物料中文名称','' AS '物料英文名称','' AS '企业物料描述',
|
CASE WHEN P.productSpec IS NULL THEN K.productSpec ELSE P.productSpec END AS '物料规格类型',CASE WHEN P.BrandName IS NULL THEN K.BrandName ELSE P.BrandName END AS '品牌',
|
CASE WHEN P.productModel IS NULL THEN K.productModel ELSE P.productModel END AS '物料条形码',
|
'' AS '账册备案料号','' AS '物料毛重','' AS '物料净重', CASE WHEN P.HSCode IS NULL THEN K.HSCode ELSE P.HSCode END AS '海关10位物料编码',
|
CASE WHEN P.OriginPlace IS NULL THEN K.OriginPlace ELSE P.OriginPlace END AS '原产国','142' AS '币种', CASE WHEN P.DeclareUnit IS NULL THEN K.DeclareUnit ELSE P.DeclareUnit END AS '法定计量单位',
|
(P.DeclareQuantityOrder* L.quantityOrder) AS '法定数量',
|
P.StatutoryUnit2 AS '第二计量单位',(P.StatutoryQty2*L.quantityOrder) AS '第二数量','142' AS '成交单位',L.quantityOrder as '成交数量',P.DeclarePrice as '成交单价',(P.DeclarePrice * L.quantityOrder) AS '总价',
|
'0.00' AS '折扣浮动价格','' AS '废旧物品标识','N' AS '是否赠品','0.00' AS '海关税率','' AS '备注','' AS '错误信息(系统生成不需要填写)'`;
|
|
fields = fieldStr.split(",");
|
|
let wayBillXiList: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "W")
|
.select(fields)
|
.innerJoin("TMSWayBillList", "L", "W.wayBill_Id = L.wayBill_Id")
|
.leftJoin("BaseProductInfo", "K", "L.product_Id = K.product_Id")
|
.leftJoin("TMSProductInfoPort", "P", "L.product_Id = P.product_Id And W.port_Id=P.port_Id")
|
.where(where)
|
.take(5000)
|
.andWhere("W.portName = '北京口岸' AND W.port_Id = 2")
|
.getRawMany();
|
|
// if (body.code) {
|
// sql += " And " + body.code;
|
// }
|
// let ProductList = await this.dbRead.query(sql);
|
//#endregion
|
|
let root1 = path.resolve();
|
let url2 = "/download/北京订单数据.xlsx";
|
fileName = root1 + url2.replace(/\//g, path.sep);
|
pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
|
jsonWorkSheet = XLSX.utils.json_to_sheet(wayBillLists);
|
let arrayWorkSheet = XLSX.utils.json_to_sheet(wayBillXiList);
|
// 构造workBook
|
let workBook1 = {
|
SheetNames: ["订单主体", "物料信息"],
|
Sheets: {
|
订单主体: jsonWorkSheet,
|
物料信息: arrayWorkSheet
|
}
|
};
|
// 设置列宽
|
jsonWorkSheet["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
// 设置自定义单元格值
|
worksheet = workBook1.Sheets["订单主体"];
|
let worksheet2 = workBook1.Sheets["物料信息"];
|
// 设置标题格式
|
for (let index = 0; index < 53; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 12,
|
bold: false
|
}
|
};
|
}
|
for (let index = 0; index < 42; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet2[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 12,
|
bold: false
|
}
|
};
|
}
|
// XLSX.writeFile(workBook, fileName);
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook1, fileName, wopts);
|
// XLSX.writeFile(workBook1, fileName);
|
|
//#endregion
|
this.info.result = true;
|
this.info.data = {
|
url: url,
|
url2: url2
|
};
|
this.info.msg = "导出成功！";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
///#endregion
|
//#endregion
|
|
//#region 西安报关口岸导出
|
/// <summary>
|
/// 西安报关口岸导出
|
/// </summary>
|
/// <param name="body"></param>
|
/// <returns></returns>
|
@Post()
|
public async exportXianDeclaration() {
|
let { ctx } = this;
|
|
try {
|
let where = await ctx.service.common.getWhere();
|
let fieldStr = `W.expressCode as '分运单号',W.wayBillCode as '运单号','B' AS '申报类型' ,(CASE WHEN P.CIQNameCn IS NULL THEN K.productName ELSE P.CIQNameCn END) AS '物品名称',
|
CASE WHEN P.CIQName IS NULL THEN K.CIQName ELSE P.CIQName END AS '英文物品名称',
|
CASE WHEN P.PostMailCode IS NULL THEN K.PostMailCode ELSE P.PostMailCode END AS '物料编码',
|
CASE WHEN (P.NetWeight * L.quantityOrder) IS NULL THEN (K.DeclareQuantityOrder *L.quantityOrder) ELSE (P.NetWeight * L.quantityOrder) END AS '净重(KG)',
|
CASE WHEN P.Weight IS NULL THEN K.Weight ELSE P.Weight END AS '毛重(KG)',
|
CASE WHEN P.productSpec IS NULL THEN K.productSpec ELSE P.productSpec END AS '规格/型号',
|
'' AS '产销城市','' AS '币制',L.quantityOrder as '申报数量',(L.quantityOrder * P.DeclarePrice) AS '申报总价',
|
CASE WHEN P.DeclareUnit IS NULL THEN K.DeclareUnit ELSE P.DeclareUnit END AS '申报计量单位',W.consigneeName as '收件人',W.cityName as '收件人城市',W.consigneeAddress as '收件人地址',W.consigneeMobile as '收件人电话',
|
'' AS '发件人国家','' AS '发件人','' AS '英文发件人','' AS '发件人城市', '' AS '英文发件人城市','' AS '英文经停城市','' AS '发件人地址','' AS '英文发件人地址','' AS '收件人电话','' AS '收发件人证件类型',
|
W.consigneeIdcard as '收发件人证件号','' AS '包装种类','' AS '是否含木质包装','' AS '是否为旧物品','' AS '是否未低温运输','' AS '生产国别','' AS '贸易国别'`;
|
let fields = fieldStr.split(",");
|
|
let wayBillList: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "W")
|
.select(fields)
|
.innerJoin("TMSWayBillList", "L", "W.wayBill_Id = L.wayBill_Id")
|
.leftJoin("BaseProductInfo", "K", "L.product_Id = K.product_Id")
|
.leftJoin("TMSProductInfoPort", "P", "L.product_Id = P.product_Id And W.port_Id=P.port_Id")
|
.where(where)
|
.take(5000)
|
.andWhere("W.portName = '西安口岸' AND W.port_Id = 7")
|
.orderBy({
|
expressCode: "DESC"
|
})
|
.getRawMany();
|
|
let root = path.resolve();
|
let url = "/download/报关数据(西安口岸模板).xlsx";
|
let fileName = root + url.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
|
// wayBillList = wayBillList.splice(0, 0, {
|
// "分运单号":"xxxxxx"
|
// }, {"申报类型": "xxxxsssss"}, {});
|
let jsonWorkSheet = XLSX.utils.json_to_sheet(wayBillList);
|
// 构造workBook
|
let workBook = {
|
SheetNames: ["数据"],
|
Sheets: {
|
数据: jsonWorkSheet
|
}
|
};
|
// 设置行高
|
jsonWorkSheet["!rows"] = [
|
{
|
hpx: 50
|
}
|
];
|
// 设置列宽
|
jsonWorkSheet["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
|
let worksheet = workBook.Sheets["数据"];
|
// 设置标题格式
|
for (let index = 0; index < 34; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 12,
|
bold: false
|
},
|
fill: {
|
fgColor: {
|
rgb: "ff9999" // 单元格背景颜色
|
}
|
}
|
// border: {
|
// bottom: {
|
// // 底线颜色
|
// style: "thin",
|
// color: { rgb: "989799" }
|
// }
|
// }
|
};
|
}
|
worksheet["I1"] = {
|
v: "产销城市",
|
s: {
|
font: {
|
sz: 12,
|
bold: true
|
},
|
alignment: {
|
vertical: "center",
|
horizontal: "center"
|
},
|
fill: {
|
fgColor: {
|
rgb: "9AFF9A" // 单元格背景颜色
|
}
|
}
|
}
|
};
|
worksheet["M1"] = {
|
v: "申报计量单位",
|
s: {
|
font: {
|
sz: 12,
|
bold: true
|
},
|
alignment: {
|
vertical: "center",
|
horizontal: "center"
|
},
|
fill: {
|
fgColor: {
|
rgb: "9AFF9A" // 单元格背景颜色
|
}
|
}
|
}
|
};
|
worksheet["W1"] = {
|
v: "英文经停城市",
|
s: {
|
font: {
|
sz: 12,
|
bold: true
|
},
|
alignment: {
|
vertical: "center",
|
horizontal: "center"
|
},
|
fill: {
|
fgColor: {
|
rgb: "9AFF9A" // 单元格背景颜色
|
}
|
}
|
}
|
};
|
|
/* bookType can be 'xlsx' or 'xlsm' or 'xlsb' */
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook, fileName, wopts);
|
|
// XLSX.writeFile(workBook, fileName);
|
|
this.info.result = true;
|
this.info.data = {
|
url: url
|
};
|
this.info.msg = "导出成功！";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 广州口岸运单数据
|
/// <summary>
|
/// </summary> 广州口岸导出
|
/// <param name="body"></param>
|
/// <returns></returns>
|
@Post()
|
public async exportguangzhouDeclaration() {
|
let { ctx } = this;
|
// let body = ctx.request.body;
|
try {
|
//#region 广州运单数据
|
let where = await ctx.service.common.getWhere();
|
let fieldStr = `t.expressCode AS '进口分运单号',totalQuantityOrder AS '整单件数',2 AS '报关类型','万福' AS '发件人','WANFU' AS '英文发件人',601 AS '发件人国别',
|
'悉尼' AS '发件人城市','Sydney' AS '英文发件人城市','N' AS '英文经停城市','358 Chisholm Rd, Auburn 2144' AS '发件人地址','358 Chisholm Rd, Auburn 2144' AS '英文发件人地址',
|
'0061287103969' AS '发件人电话',t.consigneeName AS '收件人',1 AS '收件人证件类型',t.consigneeIdcard AS '收/发件人证件号码',t.cityName AS '收件人城市',t.consigneeAddress AS '收件人地址',
|
t.consigneeMobile AS '收件人电话','601' AS '贸易国别',''AS '监管方式','' AS '成交方式','2' AS '包装种类','' AS '是否含木质包装','' AS '是否旧物','' AS '是否低温运输',
|
''AS '运费','' AS '运费币制',''AS '保费',''AS '保费币制',''AS '杂费','' AS '杂费币制','' AS '收件人信用代码'`;
|
let fields = fieldStr.split(",");
|
let wayBillList: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "t")
|
.select(fields)
|
.where(where)
|
.andWhere("t.portName = '广州口岸' AND t.port_Id = 8")
|
.orderBy({
|
expressCode: "DESC"
|
})
|
.take(5000)
|
.getRawMany();
|
|
//#region sheet2 数据源
|
fieldStr = `L.expressCode AS '分运单号', (case when P.postMailCode is null then K.postMailCode else P.postMailCode end) AS '物料编码行邮税码',(case when P.cIQNameCn is null then K.productName else P.cIQNameCn end) AS '物料名称',
|
(case when P.cIQName is null then K.cIQName else P.cIQName end) AS '物料英文名称',(case when P.productSpec is null then K.productSpec else P.productSpec end) AS '规格型号',
|
'601'AS '产销国','悉尼'AS '产销城市',
|
L.quantityOrder AS '实际内件数量',
|
(CASE WHEN P.PostMailCode IN ('01010700','01010800','01020100','01020200') THEN (P.netWeight * L.quantityOrder) ELSE L.quantityOrder END) AS '申报数量',
|
CASE WHEN P.declareUnit IS NULL THEN K.declareUnit ELSE P.declareUnit END AS '申报计量单位', Case when (P.weight * L.quantityOrder) is null then (K.weight * L.quantityOrder ) ELSE (P.weight * L.quantityOrder) END AS '毛重',
|
CASE WHEN (P.netWeight * L.quantityOrder) IS NULL THEN (K.netWeight * L.quantityOrder) ELSE (P.netWeight * L.quantityOrder) END AS '净重',
|
Convert(decimal(18,4), Case When P.declarePrice is null THEN 0 ELSE (P.declarePrice * (CASE WHEN P.PostMailCode IN ('01010700','01010800','01020100','01020200') THEN (P.netWeight * L.quantityOrder) ELSE L.quantityOrder END)) END) AS '成交金额',
|
'142' AS '成交币制',''AS '生产厂家(C类必填)',
|
'' AS '第一数量','' AS '第一单位',''AS '第二数量',''AS '第二单位'`;
|
fields = fieldStr.split(",");
|
//#endregion
|
let wayBillXiList: Array<any> = await this.dbRead
|
.createQueryBuilder(vTMSWayBillList, "L")
|
.select(fields)
|
.innerJoin("BaseProductInfo", "k", "k.Product_Id = L.Product_Id")
|
.leftJoin("TMSProductInfoPort", "P", "K.Product_Id = P.product_Id and P.port_Id = 8 ")
|
.where(where)
|
.andWhere("L.portName = '广州口岸' AND L.port_Id = 8")
|
.orderBy({
|
expressCode: "DESC"
|
})
|
.take(5000)
|
.getRawMany();
|
|
let root = path.resolve();
|
let url = "/download/B类申报资料.xlsx";
|
let fileName = root + url.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
|
let jsonWorkSheet = XLSX.utils.json_to_sheet(wayBillList);
|
let arrayWorkSheet = XLSX.utils.json_to_sheet(wayBillXiList);
|
|
// 构造workBook
|
let workBook = {
|
SheetNames: ["分运单", "物料信息"],
|
Sheets: {
|
分运单: jsonWorkSheet,
|
物料信息: arrayWorkSheet
|
}
|
};
|
jsonWorkSheet["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
arrayWorkSheet["!cols"] = [
|
//设置宽度
|
{ wpx: 100 }, //1-第一列
|
{ wpx: 140 }, //2-第二列
|
{ wpx: 180 } //3
|
];
|
// 设置自定义单元格值
|
let worksheet = workBook.Sheets["分运单"];
|
let worksheet1 = workBook.Sheets["物料信息"];
|
// 设置标题格式
|
for (let index = 0; index < 29; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 12,
|
bold: false
|
}
|
};
|
}
|
for (let index = 0; index < 29; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet1[col + "1"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 12,
|
bold: false
|
}
|
};
|
}
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook, fileName, wopts);
|
|
// XLSX.writeFile(workBook, fileName);
|
|
//#endregion
|
this.info.result = true;
|
this.info.data = {
|
url: url
|
};
|
this.info.msg = "导出成功！";
|
} catch (ex) {
|
this.info.result = false;
|
// this.info.msg = "错误信息：" + ex.query;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region 厦门口岸
|
/// <summary>
|
/// </summary> 厦门口岸
|
/// <param name="body"></param>
|
/// <returns></returns>
|
//#region 厦门口岸
|
@Post()
|
public async exportxiamenDeclaration() {
|
let { ctx } = this;
|
// let body = ctx.request.body;
|
try {
|
let where: any = await ctx.service.common.getWhere();
|
//#region 厦门(厦门口岸)
|
let fieldStr = `
|
DENSE_RANK() OVER(order by W.wayBillCode) as 序号, W.expressCode as '分运单号',
|
'澳德' AS '发件人','AUOD' AS '英文发件人','澳大利亚' AS '发件人国别','悉尼' AS '发件人城市','Sydney' AS '英文发件人城市',
|
'N' as '英文经停城市','46-48 Princes Road West Auburn NSW 2144 AUSTRALIA' AS '发件人地址','46-48 Princes Road West Auburn NSW 2144 AUSTRALIA' AS '英文发件人地址',
|
'000881122' AS '发件人电话',W.consigneeName as '收件人','' AS '英文收件人','中国' AS '收件人国别',W.CityName as '收件人城市',
|
W.ConsigneeAddress as '收件人地址','' as '英文收件人地址', W.consigneeMobile as '收件人电话', 'B' AS '报关类别',P.cIQNameCn AS '物料/物品名称',P.cIQName AS '英文物料名称',
|
P.postMailCode AS '物料编码',P.productSpec AS '物料规格、型号', L.QuantityOrder as '物料数量',
|
(CASE WHEN P.Weight IS NULL THEN K.Weight ELSE P.Weight end) * L.QuantityOrder AS '毛重(提单重量)',1 as '件数','纸箱' as '包装种类',0 as '是否含木质包装',0 as '是否为旧物品',
|
0 as '是否为低温运输', (CASE WHEN P.declareUnit ='千克' then (P.netWeight * L.quantityOrder) ELSE L.quantityOrder END) AS '申报数量',
|
P.declareUnit as '申报计量单位','' as '第一（法定）数量','' as '第一（法定）计量单位',P.declarePrice as '单价',142 AS '币制','' AS '申报单位类别',
|
'' AS '监管方式','' AS '征免性质分类','' AS '成交方式','' AS '征减免税方式','身份证' as '收发件人证件类型',W.ConsigneeIdcard AS '收发件人证件号',
|
'' AS '收发货人代码','' AS '收发货人名称','' AS '收发货人统一社会信用代码',371301 AS '码头/货场代码','澳大利亚' AS '贸易国别', '澳大利亚' AS '产销国',
|
'悉尼' AS '产销城市', '' AS'生产厂商', '' AS '货主单位地区代码', '' AS '货主单位代码', '' AS '货主单位统一社会信用代码', '' AS '合同号','' AS '运费标记',
|
'' AS '运费币制' , '' AS '运费/率','' AS '保险费标记','' AS '保险费币制','' AS '保险费/率', '' AS '杂费标记','' AS '杂费币制', '' AS '杂费/率', '' AS '备注',
|
'' AS '用途','' AS '第二数量', '' AS '第二计量单位','' AS '关联编号字段'`;
|
let fields = fieldStr.split(",");
|
where.portName = "厦门口岸";
|
let wayBillBgList: Array<any> = await this.dbRead
|
.createQueryBuilder(TMSWayBill, "W")
|
.select(fields)
|
.innerJoin("TMSWayBillList", "L", "W.wayBill_Id = L.wayBill_Id")
|
.leftJoin("BaseProductInfo", "K", "L.product_Id = K.product_Id")
|
.leftJoin("TMSProductInfoPort", "P", "L.product_Id = P.product_Id And W.port_Id=P.port_Id")
|
.where(where)
|
.take(5000)
|
.andWhere("W.portName = '厦门口岸' AND W.port_Id = 9")
|
.getRawMany();
|
|
let root = path.resolve();
|
let url2 = "/download/厦门报关数据.xlsx";
|
let fileName = root + url2.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
|
let opts = {
|
cellStyles: true,
|
origin: 3 // 跳过前N行
|
};
|
|
let jsonWorkSheet = XLSX.utils.json_to_sheet(wayBillBgList, opts);
|
// 构造workBook
|
let workBook1 = {
|
SheetNames: ["报关数据"],
|
Sheets: {
|
报关数据: jsonWorkSheet
|
}
|
};
|
// 设置行高
|
jsonWorkSheet["!rows"] = [
|
{
|
hpx: 50
|
}
|
];
|
// 设置列宽
|
jsonWorkSheet["!cols"] = [
|
//设置宽度
|
{ wpx: 80 }, //1-第一列
|
{ wpx: 100 }, //2-第二列
|
{ wpx: 80 }, //3
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 80 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 80 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 200 },
|
{ wpx: 100 },
|
{ wpx: 80 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 100 },
|
{ wpx: 80 },
|
{ wpx: 80 }
|
];
|
jsonWorkSheet["!merges"] = [
|
{
|
s: {
|
//s为开始
|
c: 0, //开始列
|
r: 0 //可以看成开始行,实际是取值范围
|
},
|
e: {
|
//e结束
|
c: 27, //结束列
|
r: 0 //结束行
|
}
|
},
|
{
|
s: {
|
//s为开始
|
c: 0, //开始列
|
r: 1 //可以看成开始行,实际是取值范围
|
},
|
e: {
|
//e结束
|
c: 27, //结束列
|
r: 1 //结束行
|
}
|
},
|
{
|
s: {
|
//s为开始
|
c: 0, //开始列
|
r: 2 //可以看成开始行,实际是取值范围
|
},
|
e: {
|
//e结束
|
c: 27, //结束列
|
r: 2 //结束行
|
}
|
}
|
];
|
// 设置自定义单元格值
|
let worksheet = workBook1.Sheets["报关数据"];
|
|
// 设置内容格式
|
let rowCount = wayBillBgList.length;
|
let setStyle = (cell, horizontal) => {
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: horizontal
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
}
|
};
|
};
|
for (let rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
|
for (let colIndex = 0; colIndex < 67; colIndex++) {
|
let col = XLSX.utils.encode_col(colIndex); // 转为字母
|
let cellIndex = col + (3 + rowIndex);
|
let cell = worksheet[cellIndex];
|
if (!cell) continue;
|
|
if (colIndex == 19) {
|
setStyle(cell, "left");
|
} else {
|
setStyle(cell, "center");
|
}
|
}
|
}
|
|
worksheet["A1"] = {
|
v: "中外运空运发展股份有限公司厦门分公司——进口快件清单",
|
s: {
|
font: {
|
sz: 17,
|
bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
vertical: "center",
|
horizontal: "center"
|
}
|
}
|
};
|
worksheet["A2"] = {
|
v: "报关单号码:",
|
s: {
|
font: {
|
sz: 11,
|
// bold: true,
|
name: "Arial"
|
},
|
alignment: {
|
//左对齐
|
vertical: "left"
|
// horizontal: "center"
|
}
|
}
|
};
|
worksheet["A3"] = {
|
v: "起运地:SYD 提单号:HH3TGXM200306297 运输工具名称:NH880 航班号:NH880 进出口日期:2020-02-14 申报日期:2020-02-14",
|
s: {
|
font: {
|
sz: 12,
|
bold: true,
|
color: { rgb: "FF0000" }, // 字体颜色
|
name: "Arial"
|
},
|
alignment: {
|
//左对齐
|
vertical: "left"
|
// horizontal: "center"
|
}
|
}
|
};
|
// 设置标题格式
|
for (let index = 0; index < 29; index++) {
|
let col = XLSX.utils.encode_col(index); // 转为字母
|
let cell = worksheet[col + "0"];
|
if (!cell) continue;
|
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: "center"
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
},
|
fill: {
|
fgColor: {
|
rgb: "FFFFFF" // 单元格背景颜色
|
}
|
},
|
border: {
|
bottom: {
|
// 底线颜色
|
style: "thin",
|
color: { rgb: "989799" }
|
}
|
}
|
};
|
}
|
|
/* bookType can be 'xlsx' or 'xlsm' or 'xlsb' */
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook1, fileName, wopts);
|
//#endregion
|
this.info.result = true;
|
this.info.data = {
|
url2: url2
|
};
|
this.info.msg = "导出成功!";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
|
//#endregion
|
//#endregion
|
|
//#region UpdatePort 修改口岸
|
@Post()
|
public async updatePort() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
//口岸
|
let portInfo = await this.dbRead.findOne(TMSPort, body.port_Id);
|
if (!portInfo) {
|
this.info.result = false;
|
this.info.msg = "口岸不存在";
|
ctx.body = this.info;
|
return;
|
}
|
//快递
|
let expressCorp = await this.dbRead.findOne(BaseExpressCorp, body.expressCorp_Id);
|
if (!expressCorp) {
|
this.info.result = false;
|
this.info.msg = "快递不存在";
|
ctx.body = this.info;
|
return;
|
}
|
|
//快递类型
|
let paramValueInfo = await this.dbRead.findOne(SysParamValue, {
|
type_Id: 503,
|
value01: body.expressCorpType
|
});
|
if (!expressCorp) {
|
this.info.result = false;
|
this.info.msg = "快递类型不存在";
|
ctx.body = this.info;
|
return;
|
}
|
// 口岸名称 快递类别是否可以对上 portExpress
|
let portExpressList = ctx.helper.arrayToCase(JSON.parse(portInfo.portExpress));
|
let isTrue = true;
|
for (let item of portExpressList) {
|
if (item.expressCorpType == body.expressCorpType) {
|
isTrue = true;
|
break;
|
} else {
|
isTrue = false;
|
}
|
}
|
|
if (isTrue == false) {
|
this.info.result = false;
|
this.info.msg = "口岸名称和快递类别不符合,请重新选择！";
|
ctx.body = this.info;
|
return;
|
}
|
|
let wayBillList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(body.wayBill_Ids)
|
});
|
for (let wayBillInfo of wayBillList) {
|
wayBillInfo.portName = portInfo.portName;
|
wayBillInfo.port_Id = portInfo.port_Id;
|
wayBillInfo.expressCorpName = expressCorp.expressCorpName;
|
wayBillInfo.expressCorp_Id = expressCorp.expressCorp_Id;
|
wayBillInfo.expressCorpType = paramValueInfo.value01.toString();
|
|
//#region 撤回单号
|
let wbinfoReset = await ctx.service.tms.wayBill.clearExpressCode(wayBillInfo);
|
if (!wbinfoReset.result) {
|
this.info.result = wbinfoReset.result;
|
this.info.msg = wbinfoReset.msg;
|
this.ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
await this.dbWrite.update(TMSWayBill, wayBillInfo.wayBill_Id, {
|
portName: portInfo.portName,
|
port_Id: portInfo.port_Id,
|
expressCorpName: expressCorp.expressCorpName,
|
expressCorp_Id: expressCorp.expressCorp_Id,
|
expressCorpType: paramValueInfo.value01.toString()
|
});
|
|
//#region 更新运单状态
|
await this.dbWrite.update(TMSWayBill, wayBillInfo.wayBill_Id, {
|
orderStatus: "审核成功",
|
expressCode: null
|
});
|
//#endregion
|
|
// 修改落口
|
await this.dbWrite.update(TMSWayBill, wayBillInfo.wayBill_Id, {
|
planDropOffPort: () => {
|
return `(Select top 1 replace(regionName, '#', '') as planDropOffPort
|
From Tms_GoodsRegion Where portName=TMS_WayBill.portName)`;
|
}
|
});
|
|
// 轨迹数据
|
await ctx.service.tms.wayBillHelper.setStatusHistory(wayBillInfo, "修改口岸", "修改口岸为" + portInfo.portName);
|
|
this.info.result = true;
|
this.info.msg = "更改成功";
|
}
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "异常" + ex;
|
ctx.body = this.info;
|
return;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region 获得运单号获取托盘号
|
/// <summary>
|
/// 获得运单号获取托盘号
|
/// </summary>
|
/// <param name="reqinfo">请求参数</param>
|
/// <returns>返回运单信息</returns>
|
@Post()
|
public async getPlateCode() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
if (!body.wayBillCode) {
|
this.info.result = false;
|
this.info.msg = "运单号不能为空";
|
ctx.body = this.info;
|
return;
|
}
|
try {
|
//根据输入运单号，查询运单表是否存在此运单号
|
var bill = await this.dbRead.findOne(TMSWayBill, {
|
wayBillCode: body.wayBillCode
|
});
|
if (bill != null) {
|
this.info.result = true;
|
this.info.data = bill;
|
} else {
|
//运单表不存在，查询面单上传查询表是否存在
|
var upload = await this.dbRead.findOne(TMSWayBillUpload, {
|
wayBillCode: body.wayBillCode
|
});
|
if (upload != null) {
|
let wb = new TMSWayBill();
|
wb.wayBillCode = upload.wayBillCode;
|
wb.consignor_Id = upload.consignor_Id;
|
wb.consignorCode = upload.consignorCode;
|
wb.consignorName = upload.consignorName;
|
wb.orderStatus = upload.orderStatus;
|
wb.plateCode = null;
|
this.info.data = wb;
|
this.info.result = true;
|
} else {
|
//面单上传查询不存在，查询揽收查询是否存在
|
var receive = await this.dbRead.findOne(TMSWayBillReceive, {
|
wayBillCode: body.wayBillCode
|
});
|
if (receive != null) {
|
this.info.result = true;
|
var wb = new TMSWayBill();
|
wb = Object.assign(wb, receive);
|
wb.wayBillCode = receive.wayBillCode;
|
wb.orderStatus = receive.collectStatus;
|
this.info.data = wb;
|
} else {
|
this.info.result = false;
|
this.info.msg = "此运单号不存在，请核对后输入！";
|
}
|
}
|
}
|
} catch (ex) {
|
let msg = "异常错误信息：" + ex.message;
|
this.info.result = false;
|
this.info.msg = msg;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 运单管理批量退货
|
/// <summary>
|
/// 修改日期：2019/3/12
|
/// 运单管理批量退货
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async billReturnGoods() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let userInfo = await ctx.helper.userInfo();
|
try {
|
//#region 校验数据
|
let sql = "";
|
|
if ((!Array.isArray(body.ids) || !body.ids.length) && !body.wayBillCode) {
|
this.info.result = false;
|
this.info.msg = "数据不存在";
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
let billList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(body.ids)
|
});
|
// 没有运单时，直接讲揽收单状态改为已退货
|
if (!billList.length) {
|
if (body.wayBillCode) {
|
let receiveInfo = await this.dbRead.findOne(TMSWayBillReceive, {
|
wayBillCode: body.wayBillCode
|
});
|
if (receiveInfo) {
|
await this.dbWrite.update(TMSWayBillReceive, receiveInfo.wayBillReceive_Id, {
|
collectStatus: "已退货",
|
orderStatus: "已退货"
|
});
|
}
|
let wb = new TMSWayBill();
|
wb.wayBill_Id = 0;
|
wb.wayBillCode = receiveInfo.wayBillCode;
|
wb.orderStatus = receiveInfo.collectStatus;
|
wb.plateCode = null;
|
wb.abnormalReason = body.abnormalReason;
|
//添加运单追踪记录
|
await ctx.service.tms.wayBillHelper.setStatusHistory(wb, "运单退货", "已退货", "已退货");
|
}
|
this.info.result = true;
|
this.info.msg = "退货成功";
|
this.ctx.body = this.info;
|
return;
|
}
|
|
//#region 添加运单追踪记录
|
for (let row of billList) {
|
let wayBill_Id = row["wayBill_Id"];
|
let wayBillCode = row["wayBillCode"]; //运单号
|
let orderStatus = row["orderStatus"]; //运单操作前状态
|
let plateCode = row["plateCode"];
|
let wb = new TMSWayBill();
|
wb.wayBill_Id = wayBill_Id;
|
wb.wayBillCode = wayBillCode;
|
wb.orderStatus = orderStatus;
|
wb.plateCode = plateCode;
|
wb.abnormalReason = body.abnormalReason;
|
//添加运单追踪记录
|
await ctx.service.tms.wayBillHelper.setStatusHistory(wb, "运单退货", "已退货", "已退货");
|
}
|
//#endregion
|
|
let msg = "";
|
for (let item of billList) {
|
//#region 撤回单号
|
try {
|
let wbinfoReset = await ctx.service.tms.wayBill.clearExpressCode(item);
|
if (!wbinfoReset.result) {
|
this.info.result = wbinfoReset.result;
|
this.info.msg = wbinfoReset.msg;
|
this.ctx.body = this.info;
|
return;
|
}
|
} catch (error) {
|
console.log("运单退货，撤回单号", error.message);
|
}
|
//#endregion
|
|
if (item.orderType == "仓配") {
|
//根据运单号对应的店铺订单号查询出库单状态
|
let orderInfo = await this.dbRead.findOne(SaleOrder, {
|
storeOrderCode: item.wayBillCode
|
});
|
if (orderInfo != null) {
|
let statusText = orderInfo.statusText;
|
if (
|
statusText == "新建" ||
|
statusText == "审核成功" ||
|
statusText == "波次完成" ||
|
statusText == "拣货中" ||
|
statusText == "等待配货" ||
|
statusText == "终止" ||
|
statusText == "部分打包" ||
|
statusText == "打包完成" ||
|
statusText == "待审核" ||
|
statusText == "发运完成"
|
) {
|
sql = `Update TMS_WayBill Set orderStatus='已退货',expressCode=null Where wayBillCode=@0;`;
|
sql += `Update TMS_WayBillReceive SET collectStatus='已退货' Where wayBillCode=@0;`;
|
await this.dbWrite.query(sql, [item.wayBillCode]);
|
sql =
|
`
|
Delete from Base_ProductPlaceHolder Where MainID =${orderInfo.order_Id} And ClassName='销售订单';
|
Update Sale_OrderList Set LackStorage=0, BatchQuantity=0 Where order_Id= ${orderInfo.order_Id};
|
Update Sale_Order Set statusID=16, statusText='已关闭', sortingStatus=1, SortingDate=null, Auditing=0,
|
ModifyID=${userInfo.user_Id}, Modifier='${userInfo.userTrueName}', ModifyDate=getdate()
|
Where order_Id=` + orderInfo.order_Id;
|
sql += "Update Sale_OrderList set sortingStatus=1, LackStorage=quantityOrder Where order_Id=" + orderInfo.order_Id;
|
await this.dbWrite.query(sql);
|
// Sale_OrderService.Instance.ReleaseStorage(orderInfo.order_Id);
|
msg += item.wayBillCode + "退货更改成功！<br/>";
|
} else {
|
msg += "<font color='red'>" + item.wayBillCode + "已经做出库操作了，不允许操作</font><br/>";
|
}
|
} else {
|
let statusText = item.orderStatus;
|
if (
|
statusText == "已提交" ||
|
statusText == "审核成功" ||
|
statusText == "录入异常" ||
|
statusText == "组板失败" ||
|
statusText == "取消组板" ||
|
statusText == "处理中" ||
|
statusText == "处理完成"
|
) {
|
sql = "Update TMS_WayBill Set orderStatus='已退货',expressCode=null Where wayBillCode=@0;";
|
sql += "Update TMS_WayBillReceive SET collectStatus='已退货' Where wayBillCode=@0;";
|
await this.dbWrite.query(sql, [item.wayBillCode]);
|
msg += item.wayBillCode + "退货更改成功！<br/>";
|
} else {
|
msg += "<font color='red'>" + item.wayBillCode + "状态，不允许操作</font><br/>";
|
}
|
}
|
} else {
|
// 获取连接并创建新的queryRunner
|
const connection = getConnection();
|
const runner = connection.createQueryRunner();
|
// 使用我们的新queryRunner建立真正的数据库连
|
await runner.connect();
|
// 开始事务：
|
await runner.startTransaction();
|
try {
|
// 对此事务执行一些操作：
|
await runner.manager.update(
|
TMSWayBill,
|
{
|
wayBillCode: item.wayBillCode
|
},
|
{
|
orderStatus: "已退货",
|
expressCode: null
|
}
|
);
|
await runner.manager.update(
|
TMSWayBillReceive,
|
{
|
wayBillCode: item.wayBillCode
|
},
|
{
|
orderStatus: "已退货"
|
}
|
);
|
|
// 提交事务：
|
await runner.commitTransaction();
|
// connection.close();
|
} catch (err) {
|
// 有错误做出回滚更改
|
await runner.rollbackTransaction();
|
}
|
msg += item.wayBillCode + "退货成功";
|
}
|
|
//#region 清空分配订单号
|
let express = await this.dbRead.findOne(BaseExpressSpareCode, {
|
orderCode: item.wayBillCode
|
});
|
if (express) {
|
await this.dbWrite.update(BaseExpressSpareCode, express.expressSpareCode_Id, {
|
orderCode: null
|
});
|
}
|
//#endregion
|
let exorder = await this.dbRead.findOne(ExpressSpareOrder, {
|
storeOrderCode: item.wayBillCode
|
});
|
if (exorder) {
|
//#region 删除本地单号匹配
|
await this.dbWrite.delete(ExpressSpareOrder, {
|
storeOrderCode: item.wayBillCode
|
});
|
}
|
//#endregion
|
|
//添加运单追踪记录
|
// await ctx.service.tms.wayBillHelper.setStatusHistory(item, "运单退货", "已退货", "已退货");
|
}
|
this.info.result = msg.indexOf("color='red'") < 0;
|
this.info.msg = msg;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region 运单管理批量取消退货
|
/// <summary>
|
/// 修改日期：2019/3/12
|
/// 运单管理批量取消退货
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async billmultiCancelReturn() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
|
try {
|
//#region 校验数据
|
if (!Array.isArray(body.ids) || !body.ids.length) {
|
this.info.result = false;
|
this.info.msg = "数据不存在";
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region 查询运单信息
|
let wayBillList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(body.ids)
|
});
|
// 没有运单时，直接讲揽收单状态改为已退货
|
if (!wayBillList.length) {
|
if (body.wayBillCode) {
|
let receiveInfo = await this.dbRead.findOne(TMSWayBillReceive, {
|
wayBillCode: body.wayBillCode
|
});
|
if (receiveInfo) {
|
await this.dbWrite.update(TMSWayBillReceive, receiveInfo.wayBillReceive_Id, {
|
collectStatus: "已揽收",
|
orderStatus: "已揽收"
|
});
|
}
|
let wb = new TMSWayBill();
|
wb.wayBill_Id = 0;
|
wb.wayBillCode = receiveInfo.wayBillCode;
|
wb.orderStatus = receiveInfo.collectStatus;
|
wb.plateCode = null;
|
wb.abnormalReason = body.abnormalReason;
|
//添加运单追踪记录
|
await ctx.service.tms.wayBillHelper.setStatusHistory(wb, "取消退货", "已揽收", "已揽收");
|
}
|
this.info.result = true;
|
this.info.msg = "退货成功";
|
this.ctx.body = this.info;
|
return;
|
}
|
|
for (let item of wayBillList) {
|
await this.dbWrite.update(
|
TMSWayBillReceive,
|
{
|
wayBillCode: item.wayBillCode
|
},
|
{
|
collectStatus: "已揽收"
|
}
|
);
|
|
//添加运单追踪记录
|
await ctx.service.tms.wayBillHelper.setStatusHistory(item, "取消退货", "已提交", "取消退货");
|
|
await this.dbWrite.update(TMSWayBill, item.wayBill_Id, {
|
orderStatus: "已提交",
|
collectStatus: "已揽收"
|
});
|
}
|
|
this.info.result = true;
|
this.info.msg = "取消退货成功！";
|
//#endregion
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 获取口岸落口
|
/// <summary>
|
/// 获取口岸落口
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async getGoodsRegion() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
|
try {
|
let goodregion = await this.dbRead.findOne(TmsGoodsRegion, {
|
portName: body.portName
|
});
|
if (goodregion != null) {
|
this.info.result = true;
|
this.info.data = goodregion;
|
} else {
|
this.info.result = false;
|
}
|
} catch (ex) {
|
this.info.msg = ex.message;
|
this.info.result = false;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region 后端系统批量审核
|
/// <summary>
|
/// 修改日期：2019/1/11
|
/// 如果明细为空则不让审核通过
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async batchAuditingTms() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let userInfo = await ctx.helper.userInfo();
|
let msg = "";
|
try {
|
//#region 校验数据
|
let sql = "";
|
// let where = "";
|
if (!Array.isArray(body.wayBill_Ids) && !body.wayBill_Ids.length) {
|
this.info.result = false;
|
this.info.msg = "数据不存在";
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
let corpInfo = await this.dbRead.findOne(BaseExpressCorp, body.expressCorp_Id);
|
if (corpInfo == null) {
|
this.info.result = false;
|
this.info.msg = "快递公司不存在";
|
ctx.body = this.info;
|
return;
|
}
|
let portInfo = await this.dbRead.findOne(TMSPort, body.port_Id);
|
if (portInfo == null) {
|
this.info.result = false;
|
this.info.msg = "口岸不存在";
|
ctx.body = this.info;
|
return;
|
}
|
|
// 口岸名称 快递类别是否可以对上
|
let portExpressList = ctx.helper.arrayToCase(JSON.parse(portInfo.portExpress));
|
let isTrue = true;
|
for (let item of portExpressList) {
|
if (item.expressCorpType == parseInt(body.expressCorpType)) {
|
isTrue = true;
|
break;
|
} else {
|
isTrue = false;
|
}
|
}
|
if (isTrue == false) {
|
this.info.result = false;
|
this.info.msg = "口岸名称和快递类别不符合,不能审核！";
|
ctx.body = this.info;
|
return;
|
}
|
//#region 执行审核
|
let wayBillList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(body.wayBill_Ids)
|
});
|
let errMsg = "";
|
//审核成功不能再次审核
|
for (let item of wayBillList) {
|
if ("已提交、处理中、处理完成".indexOf(item.orderStatus) < 0) {
|
errMsg += "运单号" + item.wayBillCode + "状态【" + item.orderStatus + "】不允许审核，已提交、处理中、处理完成才允许审核。";
|
}
|
}
|
if (errMsg) {
|
this.info.result = false;
|
this.info.msg = errMsg;
|
ctx.body = this.info;
|
return;
|
}
|
|
for (let wayBillInfo of wayBillList) {
|
let updateCols = "";
|
|
sql = `Update TMS_WayBill Set orderStatus='审核成功', Auditor='${userInfo.userTrueName}', Auditing=2, AuditDate=getdate(),expressCorp_Id=${corpInfo.expressCorp_Id},expressCorpType='${body.expressCorpType}',
|
expressCorpName='${corpInfo.expressCorpName}',port_Id='${portInfo.port_Id}', portName='${portInfo.portName}' ${updateCols},
|
planDropOffPort=(Select top 1 replace(regionName, '#', '') as planDropOffPort
|
From Tms_GoodsRegion Where portName='${portInfo.portName}')
|
Where wayBill_Id=${wayBillInfo.wayBill_Id}`;
|
await this.dbWrite.query(sql, [wayBillInfo.wayBill_Id]);
|
|
//添加运单追踪记录
|
await ctx.service.tms.wayBillHelper.setStatusHistory(wayBillInfo, "运单审核", "审核成功", "订单已成功导入");
|
wayBillInfo.orderStatus = "审核成功";
|
msg += wayBillInfo.wayBillCode + "审核成功<br/>";
|
|
// 更新身份证库中的手机号
|
// if (wayBillInfo.consigneeIdcard) {
|
// let cardInfo = await this.dbRead.findOne(TMSIdCard, {
|
// idCardCode: wayBillInfo.consigneeIdcard
|
// });
|
// if (
|
// cardInfo != null &&
|
// cardInfo.mobile != wayBillInfo.consigneeMobile &&
|
// cardInfo.mobile2 != wayBillInfo.consigneeMobile &&
|
// cardInfo.mobile3 != wayBillInfo.consigneeMobile
|
// ) {
|
// if (!cardInfo.mobile) {
|
// cardInfo.mobile = wayBillInfo.consigneeMobile;
|
// } else if (!cardInfo.mobile2) {
|
// cardInfo.mobile2 = wayBillInfo.consigneeMobile;
|
// } else if (!cardInfo.mobile3) {
|
// cardInfo.mobile3 = wayBillInfo.consigneeMobile;
|
// }
|
// await this.dbWrite.save(cardInfo);
|
// }
|
// }
|
}
|
|
//#region 订单类型为仓配的，将订单数据传到wms的出库订单
|
// 异步执行
|
setTimeout(async () => {
|
const connection: any = await this.dbWrite.connection;
|
let request = new mssql.Request(connection.driver.master);
|
await request.execute("sp_AutoWayBillToSaleOrder");
|
}, 0);
|
|
// for (let wayBillInfo of wayBillList) {
|
// if (wayBillInfo.orderStatus != "审核成功") continue;
|
|
// let wayBill_Id = wayBillInfo.wayBill_Id; //运单ID
|
// let orderType = wayBillInfo.orderType; //运单类型
|
// let bill = await this.dbRead.findOne(TMSWayBill, {
|
// wayBill_Id: wayBill_Id
|
// });
|
|
// if (orderType == "仓配") {
|
// // 保证不能重复生成 orderCode 删除终止的SaleOrder对应的单号
|
// await this.dbWrite.delete(SaleOrder, {
|
// orderCode: bill.wayBillCode,
|
// statusText: "终止"
|
// });
|
|
// //添加出库数据
|
// let order = new SaleOrder();
|
// order.orderCode = bill.wayBillCode;
|
// order.orderType = "仓配订单";
|
// order.storeOrderCode = bill.wayBillCode;
|
// order.shippingName = bill.consigneeName;
|
// order.mobile = bill.consigneeMobile;
|
// order.shippingAddress = bill.consigneeAddress;
|
// order.province_Id = bill.province_Id;
|
// order.provinceName = bill.provinceName;
|
// order.city_Id = bill.city_Id;
|
// order.cityName = bill.cityName;
|
// order.region_Id = bill.region_Id;
|
// order.regionName = bill.regionName;
|
// order.street = bill.street;
|
// order.expressCorp_Id = corpInfo.expressCorp_Id;
|
// order.expressCorpName = corpInfo.expressCorpName;
|
// order.expressCode = bill.expressCode;
|
// order.grandTotal = bill.grandTotal;
|
// order.totalQuantityOrder = bill.totalQuantityOrder;
|
// order.storage_Id = bill.storage_Id;
|
// order.storageName = bill.storageName;
|
// order.user_Id = bill.user_Id;
|
// order.userTrueName = bill.userTrueName;
|
// order.applyDate = new Date();
|
// order.statusID = 1;
|
// order.statusText = "待审核";
|
// order.consignor_Id = bill.consignor_Id;
|
// order.consignorCode = bill.consignorCode;
|
// order.consignorName = bill.consignorName;
|
// order.sortingStatus = 1;
|
// order.createID = userInfo.platUser_Id;
|
// order.creator = userInfo.platUserName;
|
// order.createDate = new Date();
|
// order.weight = bill.grossWeight;
|
// order.billingName = bill.billingName;
|
// order.billingTel = bill.billingMobile;
|
// order.billingAddress = bill.billingAddress;
|
// await this.setAccountInfo(order);
|
// let oderInfo = await this.dbWrite.save(order);
|
// if (oderInfo.order_Id) {
|
// let wayBillList = await this.dbRead.find(TMSWayBillList, {
|
// wayBill_Id: wayBill_Id
|
// });
|
// //添加出库明细数据
|
// for (let item of wayBillList) {
|
// let orderDetailInfo = new SaleOrderList();
|
// orderDetailInfo.order_Id = oderInfo.order_Id;
|
// orderDetailInfo.product_Id = item.product_Id;
|
// orderDetailInfo.productCode = item.productCode;
|
// orderDetailInfo.productName = item.productName;
|
// orderDetailInfo.productModel = item.productModel;
|
// orderDetailInfo.productSpec = item.productSpec;
|
// orderDetailInfo.smallUnit = item.smallUnit;
|
// orderDetailInfo.quantityOrder = item.quantityOrder;
|
// orderDetailInfo.quantityOuted = 0;
|
// orderDetailInfo.salePrice = item.salePrice;
|
// orderDetailInfo.createID = userInfo.platUser_Id;
|
// orderDetailInfo.creator = userInfo.platUserName;
|
// orderDetailInfo.createDate = new Date();
|
// await this.dbWrite.save(orderDetailInfo);
|
// }
|
// }
|
// }
|
// }
|
//#endregion
|
msg = "审核成功";
|
this.info.result = true;
|
this.info.msg = msg;
|
//#endregion
|
|
ctx.body = this.info;
|
return;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region 批量取消组板
|
@Post()
|
public async cancelpanel() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
//#region 校验数据
|
if (!Array.isArray(body.ids) || !body.ids.length) {
|
this.info.result = false;
|
this.info.msg = "数据不存在";
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
let wayBillList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(body.ids)
|
});
|
for (let item of wayBillList) {
|
//添加运单追踪记录
|
await ctx.service.tms.wayBillHelper.setStatusHistory(item, "取消组板", "取消组板");
|
await this.dbWrite.update(TMSWayBill, item.wayBill_Id, {
|
abnormalReason: body.value,
|
orderStatus: "取消组板",
|
plateCode: null,
|
remark: null
|
});
|
}
|
this.info.result = true;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region UpdatePrintNum
|
/// <summary>
|
/// 更新运单打印数量
|
/// </summary>
|
/// <param name="this.body"></param>
|
/// <returns></returns>
|
@Post()
|
public async updatePrintNum() {
|
try {
|
let wbList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(this.body.ids)
|
});
|
for (var item of wbList) {
|
if (item.printNum) {
|
item.printNum += 1;
|
} else {
|
item.printNum = 1;
|
}
|
await this.dbWrite.save(item);
|
}
|
this.info.result = true;
|
} catch (ex) {
|
this.info.msg = ex.message;
|
this.info.result = false;
|
}
|
return this.info;
|
}
|
//#endregion
|
|
//#region 客户端批量提交
|
/// <summary>
|
/// 修改日期：2019/3/11
|
/// 批量提交
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async batchAuditing() {
|
try {
|
//#region 校验数据
|
if (!Array.isArray(this.body.ids) || !this.body.ids.length) {
|
this.info.result = false;
|
this.info.msg = "数据不存在";
|
this.ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//先获取数据
|
let wbList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(this.body.ids)
|
});
|
|
// 提交单据
|
await this.dbWrite.update(TMSWayBill, this.body.ids, {
|
orderStatus: "已提交"
|
});
|
for (var item of wbList) {
|
await this.dbWrite.update(
|
TMSWayBillReceive,
|
{
|
wayBillCode: item.wayBillCode
|
},
|
{
|
orderStatus: "已揽收"
|
}
|
);
|
//添加运单追踪记录
|
await this.ctx.service.tms.wayBillHelper.setStatusHistory(item, "运单提交", "提交成功", "订单已创建成功");
|
}
|
this.info.result = true;
|
this.info.msg = "提交成功！";
|
this.ctx.body = this.info;
|
return;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
this.ctx.body = this.info;
|
return;
|
}
|
//#endregion
|
|
//#region 修改导入
|
/// <summary>
|
/// 修改日期：2019/11/07
|
/// 批量提交
|
/// </summary>
|
/// <returns></returns>
|
@Post()
|
public async importExel() {
|
setTimeout(async () => {
|
await this.importExelWork();
|
}, 0);
|
|
this.info.result = true;
|
this.ctx.body = this.info;
|
}
|
|
@Post()
|
private async importExelWork() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let redis = ctx.app.redis.clients.get("common"); // 将消息放入redis缓存
|
let fileUrl = body.url;
|
redis.expire(body.key, 5 * 60);
|
let errorMsg = "";
|
let i = 0;
|
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;
|
//#endregion
|
for (let item of dataList) {
|
index += 1;
|
if (!item["运单号"]) {
|
this.setMsg(`${index}、订单号不能为空`, "red");
|
}
|
if (!item["物料名称"]) {
|
this.setMsg(`${index}、物料名称不能为空`, "red");
|
}
|
if (!item["条形码"]) {
|
this.setMsg(`${index}、条形码不能为空`, "red");
|
}
|
if (!item["数量"]) {
|
this.setMsg(`${index}、数量不能为空`, "red");
|
}
|
if (!item["更改物料名称"]) {
|
this.setMsg(`${index}、更改物料名称不能为空`, "red");
|
}
|
if (!item["更改条形码"]) {
|
this.setMsg(`${index}、更改条形码不能为空`, "red");
|
}
|
}
|
if (this.isMsgError) {
|
this.setMsg("-1");
|
return;
|
}
|
for (let row of dataList) {
|
i += 1;
|
let product = await this.dbRead.findOne(BaseProductInfo, {
|
productModel: "" + row["更改条形码"]
|
});
|
if (product) {
|
let wayinfo = await this.dbRead.findOne(TMSWayBill, {
|
wayBillCode: row["运单号"]
|
});
|
if (
|
wayinfo.orderStatus == "审核成功" ||
|
wayinfo.orderStatus == "组板失败" ||
|
wayinfo.orderStatus == "处理中" ||
|
wayinfo.orderStatus == "处理完成"
|
) {
|
await this.dbWrite.update(
|
TMSWayBillList,
|
{
|
wayBill_Id: wayinfo.wayBill_Id,
|
productModel: row["条形码"]
|
},
|
{
|
productName: row["更改物料名称"],
|
productModel: row["更改条形码"],
|
quantityOrder: row["更改数量"],
|
product_Id: product.product_Id,
|
productCode: product.productCode,
|
productSpec: product.productSpec,
|
smallUnit: product.smallUnit
|
}
|
);
|
} else {
|
this.setMsg(`第${i}、条数据，状态不能为审核成功，组板失败，处理中，处理完成`, "red");
|
}
|
} else {
|
this.setMsg(`第${i}、条数据，` + row["条形码"] + `在物料信息表中不存在`, "red");
|
}
|
}
|
if (this.isMsgError) {
|
this.setMsg(errorMsg, "red");
|
this.setMsg("-1");
|
return;
|
} else {
|
errorMsg += "导入成功" + i + "条\r\n ";
|
this.setMsg(errorMsg, "blue");
|
}
|
} catch (ex) {
|
this.setMsg("出现异常：" + ex.message + ex.StackTrace, "red");
|
}
|
this.setMsg("-1");
|
}
|
|
//#endregion
|
|
//#region 账单导出
|
/// <summary>
|
/// 账单导出
|
/// </summary>
|
/// <param name="body"></param>
|
/// <returns></returns>
|
@Post()
|
public async exportbill() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
|
try {
|
let sql = "";
|
sql = `SELECT TOP 20 wayBillCode AS '订单号',collectDate AS '揽收时间',collectStore AS '门店',
|
(select stuff((select ';'+ProductName from (SELECT L.ProductName AS ProductName FROM TMS_WayBillList AS L WITH(nolock)
|
Where T.WayBill_Id=L.WayBill_Id) AS T for xml path ('')),1,1,'')) AS '物料名称',T.TotalQuantityOrder AS '总件数',
|
GrossWeight AS '毛重' FROM TMS_WayBill T WITH(nolock)`;
|
if (body.code) {
|
sql += " Where " + body.code;
|
} else {
|
sql += " Where 1!=1";
|
}
|
let wayBillList: Array<any> = await this.dbRead.query(sql);
|
|
let root = path.resolve();
|
let url = "/download/账单导出模板.xlsx";
|
let fileName = root + url.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
let jsonWorkSheet = XLSX.utils.json_to_sheet(wayBillList);
|
// 构造workBook
|
let workBook = {
|
SheetNames: ["数据"],
|
Sheets: {
|
数据: jsonWorkSheet
|
}
|
};
|
XLSX.writeFile(workBook, fileName);
|
|
this.info.result = true;
|
this.info.data = {
|
url: url
|
};
|
this.info.msg = "导出成功！";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 同步顺丰陆地港
|
@Post()
|
public async synchroSfAddress() {
|
try {
|
var wayBillList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(this.body.ids)
|
});
|
let msg = "";
|
for (let item of wayBillList) {
|
if (!item.expressCode) {
|
msg += item.wayBillCode + "运单的快递单号不能为空";
|
}
|
if (item.expressCorpName != "泉州顺丰") {
|
msg += item.wayBillCode + "运单必须是泉州顺丰单";
|
}
|
if (!item.consigneeName) {
|
msg += item.wayBillCode + "运单的收件人不能为空";
|
}
|
if (!item.consigneeAddress) {
|
msg += item.wayBillCode + "运单的收件人地址不能为空";
|
}
|
if (!item.consigneeMobile) {
|
msg += item.wayBillCode + "运单的收件人电话不能为空";
|
}
|
if (!item.cityName) {
|
msg += item.wayBillCode + "运单的收件人市不能为空";
|
}
|
if (!item.provinceName) {
|
msg += item.wayBillCode + "运单的收件人省不能为空";
|
}
|
}
|
if (msg) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + msg;
|
this.ctx.body = this.info;
|
return;
|
}
|
let allSuccess = true;
|
for (var item of wayBillList) {
|
this.info = await this.ctx.service.tms.wayBill.synchroSfAddress(item);
|
if (!this.info.result) {
|
allSuccess = false;
|
}
|
msg += this.info.msg;
|
}
|
this.info.result = allSuccess;
|
this.info.msg = msg;
|
this.ctx.body = this.info;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "错误信息：" + ex.message;
|
}
|
this.ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region 溢装处理
|
/// <summary>
|
/// 溢装处理
|
/// </summary>
|
/// <param name="body"></param>
|
/// <returns></returns>
|
@Post()
|
public async saveOverloading() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let userInfo = await ctx.helper.userInfo();
|
try {
|
//口岸
|
let port = await this.dbRead.findOne(TMSPort, body.port_Id);
|
if (!port) {
|
this.info.result = false;
|
this.info.msg = "口岸不存在";
|
ctx.body = this.info;
|
}
|
//快递
|
let expressCorp = await this.dbRead.findOne(BaseExpressCorp, body.expressCorp_Id);
|
if (expressCorp == null) {
|
this.info.result = false;
|
this.info.msg = "快递不存在";
|
ctx.body = this.info;
|
}
|
|
//快递类型
|
let ParamValueInfo = await this.dbRead.findOne(SysParamValue, {
|
type_Id: 503,
|
value01: body.expressCorpType
|
});
|
if (ParamValueInfo == null) {
|
this.info.result = false;
|
this.info.msg = "快递类型不存在";
|
ctx.body = this.info;
|
}
|
let WayBillList = await this.dbRead.find(TMSWayBill, {
|
wayBill_Id: In(body.wayBill_Ids)
|
});
|
|
for (let item of WayBillList) {
|
var id = item.wayBill_Id;
|
if (
|
item.expressCorpName != expressCorp.expressCorpName ||
|
item.portName != port.portName ||
|
Number(item.expressCorpType) != ParamValueInfo.value01
|
) {
|
var zhuangtai = item.orderStatus;
|
|
//item.expressCode = null;
|
//清空快递单和对应接口单信息
|
await ctx.service.tms.wayBill.clearExpressCode(item);
|
// ExpressService.ClearExpressCode(item);
|
//更新口岸快递信息
|
// item.voyageCode = body.voyageCode;
|
// item.plateCode = body.plateCode;
|
// item.port_Id = port.port_Id;
|
// item.portName = port.portName;
|
// item.expressCorp_Id = expressCorp.expressCorp_Id;
|
// item.expressCorpType =String(ParamValueInfo.value01);
|
// item.expressCorpName = expressCorp.expressCorpName;
|
// item.expressCode = null;
|
// if (body.orderStatus) {
|
// item.orderStatus = body.orderStatus;
|
// }
|
// await this.dbWrite.update(TMSWayBill, id,item);
|
|
if (body.orderStatus) {
|
item.orderStatus = body.orderStatus;
|
}
|
await this.dbWrite.update(TMSWayBill, id, {
|
voyageCode: body.voyageCode,
|
plateCode: body.plateCode,
|
port_Id: port.port_Id,
|
portName: port.portName,
|
expressCorp_Id: expressCorp.expressCorp_Id,
|
expressCorpType: String(ParamValueInfo.value01),
|
expressCorpName: expressCorp.expressCorpName,
|
expressCode: null,
|
orderStatus: item.orderStatus
|
});
|
|
if (body.IsGouxuan == 1) {
|
// 推送到快递接口表中
|
await ctx.service.tms.wayBill.pushWayBillToInterface(item.wayBill_Id);
|
}
|
let WayBillTracking = new TMSWayBillTracking();
|
WayBillTracking.wayBill_Id = item.wayBill_Id;
|
WayBillTracking.wayBillCode = item.wayBillCode;
|
WayBillTracking.fromStatus = zhuangtai;
|
if (body.orderStatus == null) {
|
WayBillTracking.toStatus = "口岸：" + port.portName + "快递" + ParamValueInfo.value02;
|
} else {
|
WayBillTracking.toStatus = "溢装处理" + body.orderStatus + "口岸：" + port.portName + "快递" + ParamValueInfo.value02;
|
}
|
WayBillTracking.remark = "溢装处理";
|
WayBillTracking.userTrueName = userInfo.userTrueName;
|
WayBillTracking.user_Id = userInfo.user_Id;
|
WayBillTracking.createDate = new Date();
|
WayBillTracking.creator = userInfo.userName;
|
await this.dbWrite.insert(TMSWayBillTracking, WayBillTracking);
|
} else {
|
let data: any = {};
|
if (body.voyageCode) {
|
data.voyageCode = body.voyageCode;
|
}
|
if (body.plateCode) {
|
data.plateCode = body.plateCode;
|
}
|
if (body.orderStatus) {
|
data.orderStatus = body.orderStatus;
|
}
|
await this.dbWrite.update(TMSWayBill, id, data);
|
//#region 添加状态流
|
let Tracking = new TMSWayBillTracking();
|
Tracking.wayBill_Id = item.wayBill_Id;
|
Tracking.wayBillCode = item.wayBillCode;
|
Tracking.plateCode = body.plateCode;
|
Tracking.fromStatus = item.orderStatus;
|
if (body.orderStatus == null) {
|
Tracking.toStatus = "溢装处理" + item.expressCode;
|
} else {
|
Tracking.toStatus = "溢装处理" + body.orderStatus;
|
}
|
Tracking.userTrueName = userInfo.userName;
|
Tracking.user_Id = userInfo.user_Id;
|
Tracking.createDate = new Date();
|
Tracking.creator = userInfo.userName;
|
// TMS_WayBillTrackingRepository.Instance.Add(Tracking);
|
await this.dbWrite.insert(TMSWayBillTracking, Tracking);
|
//#endregion
|
}
|
}
|
this.info.result = true;
|
this.info.msg = "更改成功";
|
} catch (ex) {
|
this.info.msg = "出现异常：" + ex.message;
|
this.info.result = false;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
}
|
