import * as sql from "mssql";
|
import BaseService from "../baseService";
|
import { PurchaseShelve } from "../../entity/inbound/purchase/purchaseShelve";
|
import { BaseShelveRegular } from "../../entity/basicInfo/rule/baseShelveRegular";
|
import { ResultInfo } from "../../public/commonInterface";
|
|
/**
|
* 上架扫描Service
|
*/
|
export default class ShelveScanService extends BaseService {
|
//#region 待上架单扫描上架 - 获取数据
|
/// <summary>
|
/// 待上架单扫描上架 - 获取数据
|
/// </summary>
|
/// <returns></returns>
|
public async getData() {
|
let userInfo = await this.userInfo;
|
if (!this.body.shelveCode) {
|
this.info.result = false;
|
this.info.msg = "待上架单号不能为空！";
|
return this.info;
|
}
|
|
let shelveInfo = await this.dbRead.findOne(PurchaseShelve, {
|
shelveCode: this.body.shelveCode,
|
userProduct_Id: userInfo.userProduct_Id
|
});
|
if (!shelveInfo) {
|
this.info.result = false;
|
this.info.msg = this.body.shelveCode + "待上架单不存在！";
|
return this.info;
|
}
|
|
if ("待上架,部分上架,上架中".indexOf(shelveInfo.onShelveStatus) < 0) {
|
this.info.result = false;
|
this.info.msg = "只有待上架、部分上架或上架中的才允许操作！";
|
return this.info;
|
}
|
|
let sql = `
|
SELECT shelveList_Id,L.productCode,L.productModel,L.productName,L.productSpec,
|
ISNULL(quantity,0) - ISNULL(L.shelvedQuantity,0) AS quantity,
|
ISNULL(L.onShelveQuantity,0) AS onShelveQuantity,
|
ISNULL(L.shelvedQuantity,0) AS shelvedQuantity,
|
dbo.getRecommendPositionName(S.storage_Id, L.product_Id, L.productSpec, 0) AS positionName,
|
L.plateCode, L.produceDate, I.purchasePrice,
|
(SELECT shelveCode FROM Purchase_Shelve WHERE shelve_Id=L.shelve_Id) AS shelveCode,
|
I.relationCode, I.relationCode2, I.relationCode3, I.relationCode4, I.relationCode5,
|
middleBarcode, middleunitConvert, bigBarcode, I.unitConvert,L.weight,L.totalWeight, I.smallUnit
|
FROM dbo.Purchase_ShelveList L INNER JOIN dbo.Base_ProductInfo I ON L.product_Id=I.product_Id
|
INNER JOIN dbo.Purchase_Shelve S ON S.shelve_Id=L.shelve_Id
|
WHERE L.shelve_Id=@0 And quantity>isnull(L.shelvedQuantity, 0)
|
Order By productModel;
|
`;
|
|
let dataList = await this.dbRead.query(sql, [shelveInfo.shelve_Id]);
|
if (dataList.length == 0) {
|
this.info.result = false;
|
this.info.msg = "上架单号没有可上架的数量！";
|
return this.info;
|
} else {
|
//更新这个上架单为当前用户所有
|
await this.dbWrite.update(PurchaseShelve, shelveInfo.shelve_Id, {
|
user_Id: userInfo.user_Id,
|
userTrueName: userInfo.userTrueName,
|
onShelveStatus: () => {
|
return "(case when onShelveStatus='待上架' then '上架中' else onShelveStatus end)";
|
}
|
});
|
|
this.info.result = true;
|
this.info.msg = "";
|
this.info.data = dataList;
|
return this.info;
|
}
|
}
|
//#endregion
|
|
//#region 根据上架单获得获取最近上架货位
|
public async getShelvePositionName(scanDataList: any, shelveCode: string) {
|
let userInfo = await this.userInfo;
|
let shelveInfo = await this.dbRead.findOne(PurchaseShelve, {
|
shelveCode: shelveCode,
|
userProduct_Id: userInfo.userProduct_Id
|
});
|
let info: ResultInfo = {
|
result: false
|
};
|
if (shelveInfo == null) {
|
info.result = false;
|
info.msg = "上架单号不存在";
|
return info;
|
}
|
let regularInfo = await this.dbRead.find(BaseShelveRegular, {
|
where: {
|
userProduct_Id: userInfo.userProduct_Id,
|
enable: 1
|
},
|
order: {
|
orderNo: "DESC"
|
}
|
});
|
if (regularInfo.length == 0) {
|
info.result = false;
|
info.msg = "请创建上架策略数据";
|
return info;
|
}
|
for (let shelveDetail of scanDataList) {
|
for (let List of regularInfo) {
|
let sql = "";
|
let params = [shelveDetail.enterList_Id, shelveInfo.storage_Id];
|
if (List.shelveRegularName == "已有库存货位") {
|
sql = `SELECT distinct TOP(5) p.positionName AS name,L.enterList_Id AS id
|
FROM dbo.Base_ProductPosition AS p INNER JOIN Purchase_EnterList AS L
|
ON p.product_Id=L.product_Id
|
WHERE L.enterList_Id=@0 AND p.storage_Id=@1
|
AND EXISTS(SELECT * FROM dbo.Base_Position WHERE positionName=p.positionName AND positionType in(1,12,13)
|
AND storage_Id=@1 AND Enable=1);`;
|
params = [shelveDetail.enterList_Id, shelveInfo.storage_Id];
|
if (shelveInfo.orderType == "残品预到货") {
|
sql = sql.replace("in(1,12,13)", "= 2");
|
}
|
} else if (List.shelveRegularName == "默认货位") {
|
sql = `SELECT distinct TOP(5) P.positionName AS name,L.enterList_Id AS id
|
FROM dbo.vBase_ProductInfo_SKU AS P INNER JOIN Purchase_EnterList AS L
|
ON p.product_Id=L.product_Id
|
WHERE L.enterList_Id=@0 AND ISNULL(P.positionName,'')<>''
|
AND EXISTS(SELECT * FROM dbo.Base_Position WHERE positionName=P.positionName AND positionType in(1,12,13)
|
AND storage_Id=@1 AND Enable=1)`;
|
params = [shelveDetail.enterList_Id, shelveInfo.storage_Id];
|
if (shelveInfo.orderType == "残品预到货") {
|
sql = sql.replace("in(1,12,13)", "= 2");
|
}
|
} else if (List.shelveRegularName == "未使用的货位") {
|
sql = `SELECT distinct TOP(30) positionName AS name,Position_Id AS id
|
FROM dbo.Base_Position p
|
WHERE positionType in(1,12,13) AND
|
NOT EXISTS(SELECT positionName FROM dbo.Base_ProductPosition WHERE positionName=p.positionName and storage_Id=@0)
|
AND NOT EXISTS(SELECT Position_Id FROM Base_Position t WHERE t.ParentId=p.Position_Id and storage_Id=@0)
|
AND p.storage_Id=@0 AND p.Enable=1;`;
|
params = [shelveInfo.storage_Id];
|
|
if (shelveInfo.orderType == "残品预到货") {
|
sql = sql.replace("in(1,12,13)", "= 2");
|
}
|
} else if (List.shelveRegularName == "同品牌货位") {
|
sql = `SELECT distinct TOP(30) positionName AS name ,ROW_NUMBER() OVER(ORDER BY positionName) AS id
|
FROM dbo.Base_ProductPosition
|
WHERE product_Id IN(SELECT product_Id FROM dbo.Base_ProductInfo
|
WHERE Brand_Id=(SELECT Brand_Id FROM dbo.Purchase_EnterList WHERE enterList_Id=@0)
|
AND ISNULL(BrandName,'')<>'') AND storage_Id=@1
|
AND EXISTS(SELECT * FROM dbo.Base_Position WHERE positionName=Base_ProductPosition.positionName AND positionType in(1,12,13)
|
AND storage_Id=@1 AND Enable=1)
|
GROUP BY positionName;`;
|
params = [shelveDetail.enterList_Id, shelveInfo.storage_Id];
|
} else if (List.shelveRegularName == "同供应商货位") {
|
sql = `SELECT distinct TOP(30) p.positionName AS name,L.enterList_Id AS id
|
FROM dbo.Base_ProductPosition AS p INNER JOIN dbo.vPurchase_EnterList AS L
|
ON L.Provider_Id = p.Provider_Id
|
WHERE L.enterList_Id=@0 AND p.storage_Id=@1
|
AND EXISTS(SELECT * FROM dbo.Base_Position WHERE positionName=p.positionName AND positionType in(1,12,13)
|
AND storage_Id=@1 AND Enable=1);`;
|
params = [shelveDetail.enterList_Id, shelveInfo.storage_Id];
|
if (shelveInfo.orderType == "残品预到货") {
|
sql = sql.replace("in(1,12,13)", "= 2");
|
}
|
} else if (List.shelveRegularName == "同批次货位") {
|
sql = `SELECT distinct TOP(30) p.positionName AS name,L.enterList_Id AS id
|
FROM dbo.Base_ProductPosition AS p INNER JOIN Purchase_EnterList AS L
|
ON p.batchNumber=L.batchNumber
|
WHERE L.enterList_Id=@0 AND p.storage_Id=@1
|
AND EXISTS(SELECT * FROM dbo.Base_Position WHERE positionName=p.positionName AND positionType in(1,12,13) AND storage_Id=@1 AND Enable=1);`;
|
params = [shelveDetail.enterList_Id, shelveInfo.storage_Id];
|
if (shelveInfo.orderType == "残品预到货") {
|
sql = sql.replace("in(1,12,13)", "= 2");
|
}
|
}
|
if (!sql) {
|
continue;
|
}
|
|
let dataList = await this.dbRead.query(sql, params);
|
if (dataList.length) shelveDetail.shelvePositionNameList = dataList;
|
}
|
if (!shelveDetail.shelvePositionNameList) {
|
shelveDetail.shelvePositionNameList = [];
|
}
|
}
|
}
|
//#endregion
|
|
//#region 常规扫描上架 - shelveSave
|
/// <summary>
|
/// 常规扫描上架保存
|
/// </summary>
|
/// <returns></returns>
|
public async shelveSave(shelveCode, shelveDataList: Array<any>) {
|
let userInfo = await this.userInfo;
|
let menu_Id = 223;
|
|
if (shelveDataList.length == 0) {
|
this.info.result = false;
|
this.info.msg = "已扫描数量不能全部为零";
|
return this.info;
|
}
|
if (!shelveCode) {
|
this.info.result = false;
|
this.info.msg = "采购入库单号【" + shelveCode + "】不能为空";
|
return this.info;
|
}
|
let excelDT = new sql.Table();
|
excelDT.columns.add("shelveList_Id", sql.Int);
|
excelDT.columns.add("finishedQuantity", sql.Int);
|
excelDT.columns.add("shelvePositionName", sql.NVarChar(50));
|
excelDT.columns.add("weight", sql.Decimal(14, 4));
|
excelDT.columns.add("totalWeight", sql.Decimal(14, 4));
|
for (let item of shelveDataList) {
|
excelDT.rows.add(item.shelveList_Id, item.finishedQuantity, item.shelvePositionName, item.weight, item.totalWeight);
|
}
|
const connection: any = await this.dbWrite.connection;
|
let request = new sql.Request(connection.driver.master);
|
request.input("shelveCode", shelveCode);
|
request.input("user_Id", userInfo.user_Id);
|
request.input("menu_Id", menu_Id);
|
request.input("excelDT", excelDT);
|
request.output("outMsg", sql.NVarChar(2000));
|
let result = await request.execute("sp_Purchase_ShelveWait_Check");
|
let outMsg = result.output.outMsg;
|
|
if (outMsg && "部分上架,完全上架".indexOf(outMsg) < 0) {
|
this.info.msg = outMsg;
|
this.info.result = false;
|
return this.info;
|
} else {
|
//获得上架单状态
|
let oStatusText = await this.dbRead.findOne(PurchaseShelve, {
|
select: ["onShelveStatus"],
|
where: {
|
shelveCode: shelveCode
|
}
|
});
|
let statusText = oStatusText != null ? oStatusText : null;
|
// 是否实时将库存推送到ERP
|
let in_sendInventoryToErp = this.ctx.service.common.getConfigBool("in_sendInventoryToErp");
|
if (in_sendInventoryToErp) {
|
await this.ctx.curl("http://127.0.0.1:7001/api/task/inventoryPush", {
|
// 必须指定 method
|
method: "GET",
|
// 明确告诉 HttpClient 以 JSON 格式处理返回的响应 body
|
dataType: "json"
|
});
|
}
|
|
this.info.msg = "确认上架成功";
|
this.info.result = true;
|
this.info.dynamic = statusText;
|
return this.info;
|
}
|
}
|
//#endregion
|
|
/* *****************************************************
|
* 无单扫描上架
|
* *****************************************************/
|
//#region 无单扫描上架 - 获取无单数据
|
/**
|
* 获取待上架单数据
|
*/
|
public async getShelveNoBillData(storage_Id: number, positionName: string, productModel: string) {
|
try {
|
let sKU_ProductToMultiBarcode = await this.ctx.service.common.getConfigBool("sKU_ProductToMultiBarcode"); //支持一品多码
|
var where = "";
|
if (sKU_ProductToMultiBarcode) {
|
where = `(I.productModel COLLATE Chinese_PRC_CS_AS=@2
|
OR I.relationCode COLLATE Chinese_PRC_CS_AS=@2
|
OR I.relationCode2 COLLATE Chinese_PRC_CS_AS=@2
|
OR I.relationCode3 COLLATE Chinese_PRC_CS_AS=@2
|
OR I.relationCode4 COLLATE Chinese_PRC_CS_AS=@2
|
OR I.relationCode5 COLLATE Chinese_PRC_CS_AS=@2
|
OR I.middleBarcode COLLATE Chinese_PRC_CS_AS=@2
|
OR I.bigBarcode COLLATE Chinese_PRC_CS_AS=@2)
|
`;
|
} else {
|
where = "I.productModel COLLATE Chinese_PRC_CS_AS=@2 ";
|
}
|
|
let sql = "";
|
if (!positionName) {
|
this.info.result = false;
|
this.info.msg = "收货位不能为空！";
|
return this.info;
|
}
|
if (!productModel) {
|
this.info.result = false;
|
this.info.msg = "条码不能为空！";
|
return this.info;
|
}
|
|
sql = `
|
SELECT top 1 L.product_Id,L.productCode,L.productModel,0 AS finishedQuantity,L.productSpec, L.productName,
|
L.positionName, NULL AS shelvePosition, storage_Id, storageName, L.batchNumber,L.purchasePrice,
|
L.plateCode, L.produceDate, I.relationCode, I.relationCode2, I.relationCode3, I.relationCode4, I.relationCode5,
|
I.middleBarcode, middleUnitConvert, I.bigBarcode, I.unitConvert,L.consignor_Id, L.consignorCode, L.consignorName,L.weight
|
FROM dbo.vBase_ProductPosition L INNER JOIN dbo.vBase_ProductInfo_SKU I ON L.product_Id=I.product_Id
|
WHERE L.positionName=@1
|
AND L.storage_Id=@0
|
AND (${where})
|
AND L.positionType=4
|
AND L.validQty>0
|
;`;
|
|
let orderList = await this.dbRead.query(sql, [storage_Id, positionName, productModel]);
|
if (orderList.length == 0) {
|
this.info.result = false;
|
this.info.msg = "未找到可上架明细数据";
|
return this.info;
|
} else {
|
this.info.result = true;
|
this.info.msg = "";
|
this.info.data = orderList;
|
|
return this.info;
|
}
|
} catch (e) {
|
this.info.result = false;
|
this.info.msg = e.message;
|
return this.info;
|
}
|
}
|
//#endregion
|
|
//#region 无单扫描上架 - 无单扫描保存
|
/// <summary>
|
/// 确认入库并生成上架单
|
/// </summary>
|
/// <returns></returns>
|
public async shelveNoBillSave(orderDataList: Array<any>) {
|
let userInfo = await this.userInfo;
|
let menu_Id = 103;
|
if (orderDataList.length == 0) {
|
this.info.result = false;
|
this.info.msg = "已扫描数量不能全部为零";
|
this.ctx.body = this.info;
|
return;
|
}
|
try {
|
let excelDT = new sql.Table();
|
excelDT.columns.add("storage_Id", sql.Int);
|
excelDT.columns.add("storageName", sql.NVarChar(50));
|
excelDT.columns.add("product_Id", sql.Int);
|
excelDT.columns.add("finishedQuantity", sql.Int);
|
excelDT.columns.add("productCode", sql.NVarChar(50));
|
excelDT.columns.add("productModel", sql.NVarChar(50));
|
excelDT.columns.add("positionName", sql.NVarChar(50));
|
excelDT.columns.add("shelvePosition", sql.NVarChar(50));
|
excelDT.columns.add("produceDate", sql.NVarChar(50));
|
|
excelDT.columns.add("purchasePrice", sql.Decimal(14, 4));
|
excelDT.columns.add("batchNumber", sql.NVarChar(50));
|
excelDT.columns.add("productSpec", sql.NVarChar(50));
|
excelDT.columns.add("plateCode", sql.NVarChar(50));
|
excelDT.columns.add("consignor_Id", sql.Int);
|
excelDT.columns.add("consignorCode", sql.NVarChar(50));
|
excelDT.columns.add("consignorName", sql.NVarChar(50));
|
|
excelDT.columns.add("weight", sql.Decimal(14, 4));
|
excelDT.columns.add("totalWeight", sql.Decimal(14, 4));
|
for (let dataItem of orderDataList) {
|
excelDT.rows.add(
|
dataItem.storage_Id,
|
dataItem.storageName,
|
dataItem.product_Id,
|
dataItem.finishedQuantity,
|
|
dataItem.productCode,
|
dataItem.productModel,
|
dataItem.positionName,
|
dataItem.shelvePosition,
|
dataItem.produceDate,
|
|
dataItem.purchasePrice,
|
dataItem.batchNumber,
|
dataItem.productSpec,
|
dataItem.plateCode,
|
dataItem.consignor_Id,
|
dataItem.consignorCode,
|
dataItem.consignorName,
|
|
dataItem.weight,
|
dataItem.totalWeight
|
);
|
}
|
let shelveCode = await this.ctx.service.common.getCodeRegular(269);
|
|
const connection: any = await this.dbWrite.connection;
|
let request = new sql.Request(connection.driver.master);
|
request.input("shelveCode", shelveCode);
|
request.input("user_Id", userInfo.user_Id);
|
request.input("menu_Id", menu_Id);
|
request.input("excelDT", excelDT);
|
request.output("outMsg", sql.NVarChar(2000));
|
let result = await request.execute("sp_Purchase_Shelve_NoBill");
|
let outMsg = result.output.outMsg;
|
|
if (outMsg) {
|
this.info.msg = outMsg;
|
this.info.result = false;
|
return this.info;
|
} else {
|
this.info.msg = "无单扫描上架成功";
|
this.info.result = true;
|
return this.info;
|
}
|
} catch (error) {
|
this.info.msg = error.message;
|
this.info.result = false;
|
return this.info;
|
}
|
}
|
//#endregion
|
}
|
