using MES.Service.DB;
|
using MES.Service.Dto.webApi;
|
using MES.Service.Modes;
|
using SqlSugar;
|
using Newtonsoft.Json;
|
using System.Text;
|
|
namespace MES.Service.service.Warehouse;
|
|
public class MesInvItemStocksManager : Repository<MesInvItemStocks>
|
{
|
/// <summary>
|
/// 查询可以退货的物料(带分页和搜索)
|
/// </summary>
|
/// <param name="searchDto">搜索请求参数</param>
|
/// <returns>分页结果</returns>
|
public PagedResult<ReturnableStockDto> GetReturnableStocks(ReturnableStockSearchDto searchDto)
|
{
|
// 参数校验
|
if (searchDto.PageIndex < 1)
|
{
|
throw new Exception("页码必须大于0");
|
}
|
|
if (!new[] { 10, 20, 50 }.Contains(searchDto.PageSize))
|
{
|
throw new Exception("每页条数必须为10、20或50");
|
}
|
|
// 1. 查询XB_RACKING_TASK_SYXT_LOG中ItemBarcode和PalletCode的映射关系
|
var allRackingTaskData = Db.Queryable<XbRackingTaskSyxtLog>()
|
.Where(x => !string.IsNullOrEmpty(x.ItemBarcode))
|
.Select(x => new { x.ItemBarcode, x.PalletCode, x.Code, x.Id })
|
.ToList();
|
|
var rackingTaskData = allRackingTaskData
|
.GroupBy(x => x.ItemBarcode)
|
.Select(g => g.OrderByDescending(x => x.Id).First())
|
.Where(x => x.Code != "500")
|
.ToList();
|
|
if (rackingTaskData == null || !rackingTaskData.Any())
|
{
|
return new PagedResult<ReturnableStockDto>
|
{
|
TbBillList = new List<ReturnableStockDto>(),
|
Pagination = new PaginationInfo
|
{
|
CurrentPage = searchDto.PageIndex,
|
PageSize = searchDto.PageSize,
|
TotalRecords = 0,
|
TotalPages = 0
|
}
|
};
|
}
|
|
// 1.1 提取去重后的条码列表用于查询
|
var distinctBarcodes = rackingTaskData.Select(x => x.ItemBarcode).Distinct().ToList();
|
|
// 2. 构建查询条件
|
var query = Db.Queryable<MesInvItemStocks>()
|
.LeftJoin<MesItems>((stock, item) => stock.ItemId == item.Id)
|
.LeftJoin<MesDepots>((stock, item, depot) => stock.DepotsCode == depot.DepotCode)
|
.LeftJoin<Organize>((stock, item, depot, org) => item.UseOrg == org.Id.ToString())
|
.LeftJoin<MesUnit>((stock, item, depot, org, unit) => item.ItemUnit == unit.Id.ToString())
|
.Where((stock, item, depot, org, unit) =>
|
(distinctBarcodes.Contains(stock.ItemBarcode) || distinctBarcodes.Contains(stock.StackCode) ) &&
|
stock.Quantity > 0);
|
|
// 3. 应用搜索条件
|
var conditions = searchDto.Conditions;
|
if (conditions != null)
|
{
|
// 精确匹配条件
|
if (!string.IsNullOrEmpty(conditions.IqcStatus))
|
{
|
query = query.Where((stock, item, depot, org, unit) => stock.IqcStatus == conditions.IqcStatus);
|
}
|
|
if (conditions.Quantity.HasValue)
|
{
|
query = query.Where((stock, item, depot, org, unit) => stock.Quantity == conditions.Quantity.Value);
|
}
|
|
// 模糊匹配条件
|
|
if (!string.IsNullOrEmpty(conditions.DepotName))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
depot.DepotName != null && depot.DepotName.Contains(conditions.DepotName));
|
}
|
|
if (!string.IsNullOrEmpty(conditions.DepotSectionsCode))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
stock.DepotSectionsCode != null && stock.DepotSectionsCode.Contains(conditions.DepotSectionsCode));
|
}
|
|
if (!string.IsNullOrEmpty(conditions.ItemNo))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
item.ItemNo != null && item.ItemNo.Contains(conditions.ItemNo));
|
}
|
|
if (!string.IsNullOrEmpty(conditions.ItemName))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
item.ItemName != null && item.ItemName.Contains(conditions.ItemName));
|
}
|
|
if (!string.IsNullOrEmpty(conditions.ItemModel))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
item.ItemModel != null && item.ItemModel.Contains(conditions.ItemModel));
|
}
|
|
if (!string.IsNullOrEmpty(conditions.ItemUnitName))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
unit.Fname != null && unit.Fname.Contains(conditions.ItemUnitName));
|
}
|
|
if (!string.IsNullOrEmpty(conditions.OrgCode))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
org.Fnumber != null && org.Fnumber.Contains(conditions.OrgCode));
|
}
|
|
if (!string.IsNullOrEmpty(conditions.OrgName))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
org.Fname != null && org.Fname.Contains(conditions.OrgName));
|
}
|
|
if (!string.IsNullOrEmpty(conditions.ItemBarcode))
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
stock.ItemBarcode != null && stock.ItemBarcode.Contains(conditions.ItemBarcode));
|
}
|
|
// 日期范围条件
|
if (!string.IsNullOrEmpty(conditions.IndepDateStart))
|
{
|
if (DateTime.TryParse(conditions.IndepDateStart, out var startDate))
|
{
|
query = query.Where((stock, item, depot, org, unit) => stock.IndepDate >= startDate);
|
}
|
}
|
|
if (!string.IsNullOrEmpty(conditions.IndepDateEnd))
|
{
|
if (DateTime.TryParse(conditions.IndepDateEnd, out var endDate))
|
{
|
query = query.Where((stock, item, depot, org, unit) => stock.IndepDate <= endDate);
|
}
|
}
|
}
|
|
// 4. 查询所有符合条件的数据（不分页）
|
var queryResult = query
|
.OrderByDescending((stock, item, depot, org, unit) => stock.IndepDate)
|
.Select((stock, item, depot, org, unit) => new
|
{
|
stock.IqcStatus,
|
DepotCode = stock.DepotsCode,
|
depot.DepotName,
|
stock.DepotSectionsCode,
|
item.ItemNo,
|
item.ItemName,
|
item.ItemModel,
|
stock.Quantity,
|
item.ItemUnit,
|
ItemUnitName = unit.Fname,
|
stock.IndepDate,
|
OrgCode = org.Fnumber,
|
OrgName = org.Fname,
|
stock.ItemBarcode,
|
StockStackCode = stock.StackCode
|
})
|
.ToList();
|
|
// 5. 在内存中转换为DTO，关联PalletCode并赋值ItemBarcode（优先使用箱条码StockStackCode）
|
var tempDataList = queryResult.Select(x =>
|
{
|
// 优先使用StockStackCode，否则使用ItemBarcode去查找PalletCode
|
var barcodeToMatch = !string.IsNullOrEmpty(x.StockStackCode) ? x.StockStackCode : x.ItemBarcode;
|
var palletCode = rackingTaskData
|
.Where(r => r.ItemBarcode == barcodeToMatch)
|
.Select(r => r.PalletCode)
|
.FirstOrDefault();
|
|
return new
|
{
|
IqcStatus = x.IqcStatus == "已检" ? "1" : "0",
|
ItemType = x.DepotName == "原材料仓" ? "0" : "1",
|
StackCode = palletCode, // 使用PalletCode作为母托盘编号
|
x.DepotCode,
|
x.DepotName,
|
x.DepotSectionsCode,
|
x.ItemNo,
|
x.ItemName,
|
x.ItemModel,
|
x.Quantity,
|
x.ItemUnit,
|
x.ItemUnitName,
|
x.IndepDate,
|
x.OrgCode,
|
x.OrgName,
|
ItemBarcode = barcodeToMatch
|
};
|
}).ToList();
|
|
// 5.1 应用IqcStatus搜索条件（在内存中过滤）
|
if (conditions != null && !string.IsNullOrEmpty(conditions.IqcStatus))
|
{
|
tempDataList = tempDataList
|
.Where(x => x.IqcStatus == conditions.IqcStatus)
|
.ToList();
|
}
|
|
// 5.2 应用StackCode搜索条件（在内存中过滤PalletCode）
|
if (conditions != null && !string.IsNullOrEmpty(conditions.StackCode))
|
{
|
tempDataList = tempDataList
|
.Where(x => x.StackCode != null && x.StackCode.Contains(conditions.StackCode))
|
.ToList();
|
}
|
|
// 5.3 分组并合计Quantity
|
var dataList = tempDataList
|
.GroupBy(x => new
|
{
|
x.IqcStatus,
|
x.ItemType,
|
x.StackCode,
|
x.DepotCode,
|
x.DepotName,
|
x.DepotSectionsCode,
|
x.ItemNo,
|
x.ItemName,
|
x.ItemModel,
|
x.ItemUnit,
|
x.ItemUnitName,
|
x.OrgCode,
|
x.OrgName,
|
x.ItemBarcode
|
})
|
.Select(g => new ReturnableStockDto
|
{
|
IqcStatus = g.Key.IqcStatus,
|
ItemType = g.Key.ItemType,
|
StackCode = g.Key.StackCode,
|
DepotCode = g.Key.DepotCode,
|
DepotName = g.Key.DepotName,
|
DepotSectionsCode = g.Key.DepotSectionsCode,
|
ItemNo = g.Key.ItemNo,
|
ItemName = g.Key.ItemName,
|
ItemModel = g.Key.ItemModel,
|
Quantity = g.Sum(x => x.Quantity),
|
ItemUnit = g.Key.ItemUnit,
|
ItemUnitName = g.Key.ItemUnitName,
|
IndepDate = g.Max(x => x.IndepDate),
|
OrgCode = g.Key.OrgCode,
|
OrgName = g.Key.OrgName,
|
ItemBarcode = g.Key.ItemBarcode
|
}).ToList();
|
|
// 6. 应用ItemType筛选(在内存中过滤)
|
if (conditions?.ItemType != null)
|
{
|
dataList = dataList.Where(x => x.ItemType == conditions.ItemType).ToList();
|
}
|
|
// 7. 计算总记录数和分页参数（基于最终结果）
|
var totalRecords = dataList.Count;
|
var totalPages = (int)Math.Ceiling((double)totalRecords / searchDto.PageSize);
|
var skip = (searchDto.PageIndex - 1) * searchDto.PageSize;
|
|
// 8. 对最终结果进行分页
|
var pagedDataList = dataList.Skip(skip).Take(searchDto.PageSize).ToList();
|
|
// 9. 返回分页结果
|
return new PagedResult<ReturnableStockDto>
|
{
|
TbBillList = pagedDataList,
|
Pagination = new PaginationInfo
|
{
|
CurrentPage = searchDto.PageIndex,
|
PageSize = searchDto.PageSize,
|
TotalRecords = totalRecords,
|
TotalPages = totalPages
|
}
|
};
|
}
|
|
/// <summary>
|
/// 查询可以退货的物料(旧版本,保留兼容)
|
/// </summary>
|
/// <returns>可退货物料库存列表</returns>
|
public List<ReturnableStockDto> GetReturnableStocks()
|
{
|
// 1. 查询XB_RACKING_TASK_SYXT_LOG中所有的条码并去重
|
var distinctBarcodes = Db.Queryable<XbRackingTaskSyxtLog>()
|
.Where(x => !string.IsNullOrEmpty(x.ItemBarcode))
|
.Select(x => x.ItemBarcode)
|
.Distinct()
|
.ToList();
|
|
if (distinctBarcodes == null || !distinctBarcodes.Any())
|
{
|
return new List<ReturnableStockDto>();
|
}
|
|
// 2. 使用条码查询MES_INV_ITEM_STOCKS中的数据,关联MES_ITEMS、MES_DEPOTS、ORGANIZE、MES_UNIT表
|
var queryResult = Db.Queryable<MesInvItemStocks>()
|
.LeftJoin<MesItems>((stock, item) => stock.ItemId == item.Id)
|
.LeftJoin<MesDepots>((stock, item, depot) =>
|
stock.DepotsCode == depot.DepotCode)
|
.LeftJoin<Organize>((stock, item, depot, org) =>
|
item.UseOrg == org.Id.ToString())
|
.LeftJoin<MesUnit>((stock, item, depot, org, unit) =>
|
item.ItemUnit == unit.Id.ToString())
|
.Where((stock, item, depot, org, unit) =>
|
distinctBarcodes.Contains(stock.ItemBarcode) &&
|
stock.Quantity > 0)
|
.Select((stock, item, depot, org, unit) => new
|
{
|
stock.IqcStatus,
|
stock.StackCode,
|
DepotCode = stock.DepotsCode,
|
depot.DepotName,
|
stock.DepotSectionsCode,
|
item.ItemNo,
|
item.ItemName,
|
item.ItemModel,
|
stock.Quantity,
|
item.ItemUnit,
|
ItemUnitName = unit.Fname,
|
stock.IndepDate,
|
OrgCode = org.Fnumber,
|
OrgName = org.Fname,
|
stock.ItemBarcode
|
})
|
.ToList();
|
|
// 3. 在内存中进行数据转换
|
var result = queryResult.Select(x => new ReturnableStockDto
|
{
|
IqcStatus = x.IqcStatus == "已检" ? "1" : "0",
|
ItemType = x.DepotName == "原材料仓" ? "0" : "1",
|
StackCode = x.StackCode,
|
DepotCode = x.DepotCode,
|
DepotName = x.DepotName,
|
DepotSectionsCode = x.DepotSectionsCode,
|
ItemNo = x.ItemNo,
|
ItemName = x.ItemName,
|
ItemModel = x.ItemModel,
|
Quantity = x.Quantity,
|
ItemUnit = x.ItemUnit,
|
ItemUnitName = x.ItemUnitName,
|
IndepDate = x.IndepDate,
|
OrgCode = x.OrgCode,
|
OrgName = x.OrgName,
|
ItemBarcode = x.ItemBarcode
|
}).ToList();
|
|
return result;
|
}
|
|
/// <summary>
|
/// 根据用户编码和字符串生成退料请求单
|
/// </summary>
|
/// <param name="dto">查询参数DTO</param>
|
/// <returns>是否成功</returns>
|
public bool CreateReturnMaterialRequest(ItemStockQueryDto dto)
|
{
|
// 请求地址: http://172.20.5.5:50080/Services/Wmcs/RetrieveTask
|
// 参数格式: [{"taskCode":"xxx", "taskType":"1", "palletCode":"xxx", "widthType":"180", "station":"3"}]
|
|
if (dto?.ItemBarcodes == null || !dto.ItemBarcodes.Any())
|
{
|
return false;
|
}
|
|
try
|
{
|
// 对每个条码单独处理
|
foreach (var barcode in dto.ItemBarcodes)
|
{
|
decimal messageId = 0;
|
string taskCode = "";
|
|
// 根据条码查询XB_RACKING_TASK_SYXT_LOG表，查询max(PALLETCODE)和对应的widthType
|
var rackingTaskInfo = Db.Queryable<XbRackingTaskSyxtLog>()
|
.Where(x => x.ItemBarcode == barcode)
|
.OrderByDescending(x => x.Id)
|
.Select(x => new { x.PalletCode, x.WidthType })
|
.First();
|
|
try
|
{
|
// 1. 查询该条码对应的库存信息，按物料和批号汇总
|
var stockItems = Db.Queryable<MesInvItemStocks>()
|
.LeftJoin<MesItems>((stock, item) =>
|
stock.ItemId == item.Id)
|
.Where((stock, item) =>
|
(stock.ItemBarcode == barcode || stock.StackCode == barcode) && stock.Quantity > 0)
|
.GroupBy((stock, item) =>
|
new { stock.ItemId, stock.LotNo })
|
.Select((stock, item) => new
|
{
|
ItemNo = SqlFunc.AggregateMax(item.ItemNo),
|
ItemName = SqlFunc.AggregateMax(item.ItemName),
|
LotNo = stock.LotNo ?? "",
|
Quantity = SqlFunc.AggregateSum(stock.Quantity),
|
StackCode = SqlFunc.AggregateMax(stock.DepotSectionsCode)
|
})
|
.ToList();
|
|
if (!stockItems.Any())
|
{
|
continue; // 跳过没有库存的条码
|
}
|
|
// 2. 生成任务号
|
taskCode =
|
Db.Ado.GetString(
|
"SELECT getbillcode1(1000, 1000, 'LKRK') FROM DUAL");
|
|
// 3. 获取第一条记录用于主表
|
var firstStock = stockItems.First();
|
|
// 4. 构建请求参数（数组格式）
|
var requestData = new
|
{
|
taskCode = taskCode,
|
taskType = "1",
|
palletCode = rackingTaskInfo?.PalletCode ?? "",
|
widthType = rackingTaskInfo?.WidthType?.ToString() ?? "2000",
|
station = dto.Station
|
};
|
var requestList = new[] { requestData };
|
var jsonRequest = JsonConvert.SerializeObject(requestList);
|
|
// 5. 记录到MessageCenter表(请求前)
|
|
var messageCenter = new MessageCenter
|
{
|
TableName = "XB_RACKING_TASK_SYXT_LOG",
|
Url =
|
"http://172.20.5.5:50080/Services/Wmcs/RetrieveTask",
|
Method = "POST",
|
Status = 1,
|
CreateBy = dto.UserCode,
|
CreateDate =
|
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
|
Title = "立库出库任务",
|
PageName = taskCode,
|
Data = jsonRequest,
|
DataInserted = jsonRequest
|
};
|
messageId = Db.Insertable(messageCenter).ExecuteReturnIdentity();
|
|
// 6. 调用HTTP接口
|
var apiUrl =
|
"http://172.20.5.5:50080/Services/Wmcs/RetrieveTask";
|
|
using var http = new HttpClient();
|
http.Timeout = TimeSpan.FromSeconds(30);
|
var content = new StringContent(jsonRequest, Encoding.UTF8,
|
"application/json");
|
var response = http.PostAsync(apiUrl, content).Result;
|
var responseStr =
|
response.Content.ReadAsStringAsync().Result;
|
|
//接口的响应是 {"Code":"200","JsonMessage":null,"CodeMessage":null,"Data":null}
|
|
//只有当Code为200时，才插入XbRackingTaskSyxtLog表 不然就是抛出异常，异常的内容为JsonMessage中的内容
|
|
// 6. 解析响应JSON
|
dynamic responseJson = null;
|
try
|
{
|
responseJson =
|
JsonConvert.DeserializeObject<dynamic>(responseStr);
|
}
|
catch
|
{
|
throw new Exception($"解析响应失败: {responseStr}");
|
}
|
|
var code = responseJson?.Code?.ToString();
|
var jsonMessage = responseJson?.JsonMessage?.ToString();
|
|
// 7. 根据Code判断成功或失败
|
if (code == "200")
|
{
|
// 成功：插入任务记录到XB_RACKING_TASK_SYXT_LOG表
|
var taskLog = new XbRackingTaskSyxtLog
|
{
|
CreateBy = dto.UserCode,
|
CreateDate = DateTime.Now,
|
TaskCode = taskCode,
|
TaskType = "立库出库请求",
|
PalletCode = rackingTaskInfo?.PalletCode ?? "",
|
WidthType = rackingTaskInfo?.WidthType ?? 2000,
|
MaterialName = firstStock.ItemName,
|
MaterialCode = firstStock.ItemNo,
|
BatchNo = firstStock.LotNo,
|
Quantity = firstStock.Quantity,
|
WarehousingJson = responseStr,
|
QcStatus = 2,
|
ItemBarcode = barcode
|
};
|
Db.Insertable(taskLog).ExecuteCommand();
|
|
// 更新MessageCenter表为成功状态
|
var successMessage = "立库任务下发成功，任务号为" + taskCode;
|
Db.Updateable<MessageCenter>()
|
.SetColumns(it => it.Result == 1)
|
.SetColumns(it => it.ResultData == successMessage)
|
.SetColumns(it => it.DealWith == 1)
|
.Where(it => it.Id == messageId)
|
.ExecuteCommand();
|
}
|
else
|
{
|
// 失败：更新MessageCenter表为失败状态
|
var errorMessage = string.IsNullOrEmpty(jsonMessage)
|
? "任务请求失败"
|
: jsonMessage;
|
var failureMessage =
|
(string)("立库任务下发失败: " + errorMessage);
|
|
Db.Updateable<MessageCenter>()
|
.SetColumns(it => it.Result == 0)
|
.SetColumns(it => it.ResultData == failureMessage)
|
.SetColumns(it => it.DealWith == 0)
|
.Where(it => it.Id == messageId)
|
.ExecuteCommand();
|
|
// 抛出异常，异常内容为JsonMessage
|
throw new Exception(errorMessage);
|
}
|
}
|
catch (Exception ex)
|
{
|
// 异常时也要更新MessageCenter表
|
if (messageId > 0)
|
{
|
var exceptionMessage =
|
(string)("立库任务异常: " + ex.Message);
|
Db.Updateable<MessageCenter>()
|
.SetColumns(it => it.Result == 0)
|
.SetColumns(it =>
|
it.ResultData == exceptionMessage)
|
.SetColumns(it => it.DealWith == 0)
|
.Where(it => it.Id == messageId)
|
.ExecuteCommand();
|
}
|
|
// 继续抛出异常
|
throw;
|
}
|
}
|
|
return true;
|
}
|
catch (Exception ex)
|
{
|
// 记录日志并重新抛出异常，让Controller层获取具体错误信息
|
Console.WriteLine($"生成退料请求单失败: {ex.Message}");
|
throw;
|
}
|
}
|
}
|
