using MES.Service.DB;
|
using MES.Service.Dto.webApi;
|
using MES.Service.Modes;
|
using SqlSugar;
|
using Newtonsoft.Json;
|
using System.Text;
|
using OfficeOpenXml;
|
using OfficeOpenXml.Style;
|
|
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");
|
}
|
|
// 优化点1: 使用原生SQL ROW_NUMBER()在数据库端完成去重和排序
|
var rackingTaskSql = @"
|
SELECT ITEM_BARCODE AS ItemBarcode, PALLETCODE AS PalletCode, CODE AS Code
|
FROM (
|
SELECT ITEM_BARCODE, PALLETCODE, CODE,
|
ROW_NUMBER() OVER (PARTITION BY ITEM_BARCODE ORDER BY ID DESC) AS RN
|
FROM XB_RACKING_TASK_SYXT_LOG
|
WHERE ITEM_BARCODE IS NOT NULL AND (CODE IS NULL OR CODE != '500')
|
) WHERE RN = 1";
|
|
var rackingTaskData = Db.Ado.SqlQuery<RackingTaskDto>(rackingTaskSql);
|
|
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
|
}
|
};
|
}
|
|
// 优化点2: 过滤null值并使用字典提高查找效率
|
var validRackingData = rackingTaskData.Where(x => !string.IsNullOrEmpty(x.ItemBarcode)).ToList();
|
|
if (!validRackingData.Any())
|
{
|
return new PagedResult<ReturnableStockDto>
|
{
|
TbBillList = new List<ReturnableStockDto>(),
|
Pagination = new PaginationInfo
|
{
|
CurrentPage = searchDto.PageIndex,
|
PageSize = searchDto.PageSize,
|
TotalRecords = 0,
|
TotalPages = 0
|
}
|
};
|
}
|
|
var rackingTaskDict = validRackingData.ToDictionary(x => x.ItemBarcode);
|
var distinctBarcodes = validRackingData.Select(x => x.ItemBarcode).ToList();
|
|
// 优化点3: 构建查询条件
|
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);
|
|
var conditions = searchDto.Conditions;
|
if (conditions != null)
|
{
|
if (!string.IsNullOrEmpty(conditions.IqcStatus))
|
{
|
if (conditions.IqcStatus == "1")
|
{
|
query = query.Where((stock, item, depot, org, unit) =>
|
stock.IqcStatus == "特采直接使用" ||
|
stock.IqcStatus == "已检" ||
|
stock.IqcStatus == "免检");
|
}
|
else
|
{
|
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)) ||
|
(stock.StackCode != null &&
|
stock.StackCode.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: 使用字典查找替代Where().FirstOrDefault()
|
var tempDataList = queryResult.Select(x =>
|
{
|
var barcodeToMatch = !string.IsNullOrEmpty(x.StockStackCode)
|
? x.StockStackCode
|
: x.ItemBarcode;
|
|
rackingTaskDict.TryGetValue(barcodeToMatch, out var rackingTask);
|
|
string stockStatus = "进入立库的路上";
|
if (rackingTask?.Code != null)
|
{
|
stockStatus = rackingTask.Code == "200" ? "已在立库中" : "进入立库的路上";
|
}
|
|
return new
|
{
|
IqcStatus = x.IqcStatus == "已检" ? "1" : "0",
|
ItemType = x.DepotName == "原材料仓" ? "0" : "1",
|
StackCode = rackingTask?.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,
|
StockStatus = stockStatus
|
};
|
}).ToList();
|
|
if (conditions != null && !string.IsNullOrEmpty(conditions.StackCode))
|
{
|
tempDataList = tempDataList
|
.Where(x =>
|
x.StackCode != null &&
|
x.StackCode.Contains(conditions.StackCode))
|
.ToList();
|
}
|
|
if (conditions != null && !string.IsNullOrEmpty(conditions.StockStatus))
|
{
|
tempDataList = tempDataList
|
.Where(x => x.StockStatus == conditions.StockStatus)
|
.ToList();
|
}
|
|
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,
|
x.StockStatus
|
})
|
.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,
|
StockStatus = g.Key.StockStatus
|
}).ToList();
|
|
if (conditions?.ItemType != null)
|
{
|
dataList = dataList.Where(x => x.ItemType == conditions.ItemType)
|
.ToList();
|
}
|
|
var totalRecords = dataList.Count;
|
var totalPages =
|
(int)Math.Ceiling((double)totalRecords / searchDto.PageSize);
|
var skip = (searchDto.PageIndex - 1) * searchDto.PageSize;
|
|
var pagedDataList =
|
dataList.Skip(skip).Take(searchDto.PageSize).ToList();
|
|
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: 使用原生SQL在数据库端完成去重和排序，避免全表加载到内存
|
// Oracle 11g使用ROW_NUMBER()窗口函数获取每个条码的最新记录
|
var rackingTaskSql = @"
|
SELECT ITEM_BARCODE AS ItemBarcode, PALLETCODE AS PalletCode, CODE AS Code
|
FROM (
|
SELECT ITEM_BARCODE, PALLETCODE, CODE,
|
ROW_NUMBER() OVER (PARTITION BY ITEM_BARCODE ORDER BY ID DESC) AS RN
|
FROM XB_RACKING_TASK_SYXT_LOG
|
WHERE ITEM_BARCODE IS NOT NULL AND (CODE IS NULL OR CODE != '500')
|
) WHERE RN = 1";
|
|
var rackingTaskData = Db.Ado.SqlQuery<RackingTaskDto>(rackingTaskSql);
|
|
if (rackingTaskData == null || !rackingTaskData.Any())
|
{
|
return new List<ReturnableStockDto>();
|
}
|
|
// 优化点2: 过滤null值并使用字典提高条码匹配效率，从O(n)降低到O(1)
|
var validRackingData = rackingTaskData.Where(x => !string.IsNullOrEmpty(x.ItemBarcode)).ToList();
|
var rackingTaskDict = validRackingData.ToDictionary(x => x.ItemBarcode);
|
var distinctBarcodes = validRackingData.Select(x => x.ItemBarcode).ToList();
|
|
// 优化点3: 在数据库层面完成关联查询，一次性获取所有需要的数据
|
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();
|
|
// 优化点4: 使用字典查找替代Where().FirstOrDefault()，提高匹配性能
|
var result = queryResult.Select(x =>
|
{
|
rackingTaskDict.TryGetValue(x.ItemBarcode, out var rackingTask);
|
|
string stockStatus = "0";
|
if (rackingTask?.Code != null)
|
{
|
stockStatus = rackingTask.Code == "200" ? "1" : "2";
|
}
|
|
return 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,
|
StockStatus = stockStatus
|
};
|
}).ToList();
|
|
return result;
|
}
|
|
private class RackingTaskDto
|
{
|
public string ItemBarcode { get; set; }
|
public string PalletCode { get; set; }
|
public string Code { get; set; }
|
}
|
|
/// <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)
|
{
|
// 检查最近两分钟内是否已经存在相同的 barcode 被处理过
|
var twoMinutesAgo = DateTime.Now.AddMinutes(-2);
|
var recentTask = Db.Queryable<XbRackingTaskSyxtLog>()
|
.Where(x =>
|
x.ItemBarcode == barcode &&
|
x.CreateDate >= twoMinutesAgo)
|
.First();
|
|
if (recentTask != null)
|
{
|
throw new Exception(
|
$"物料条码 {barcode} 在两分钟内已被扫描处理，请勿重复操作。为避免立库系统任务重复下发，系统限制同一物料条码在两分钟内只能处理一次。");
|
}
|
|
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();
|
|
// 检查是否存在未完成的任务（基于PalletCode，Code为null）
|
if (!string.IsNullOrEmpty(rackingTaskInfo?.PalletCode))
|
{
|
var pendingTask = Db.Queryable<XbRackingTaskSyxtLog>()
|
.Where(x => x.PalletCode == rackingTaskInfo.PalletCode && x.Code == null)
|
.OrderByDescending(x => x.Id)
|
.First();
|
|
if (pendingTask != null)
|
{
|
throw new Exception($"托盘编号 {rackingTaskInfo.PalletCode} 已存在未完成的立库任务，请等待任务完成后再操作。");
|
}
|
}
|
|
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;
|
}
|
}
|
|
/// <summary>
|
/// 导出可退货物料库存为Excel
|
/// </summary>
|
/// <param name="searchDto">搜索请求参数</param>
|
/// <returns>Excel文件字节数组</returns>
|
public byte[] ExportReturnableStocksToExcel(
|
ReturnableStockSearchDto searchDto)
|
{
|
// 设置 EPPlus 许可证上下文(非商业用途)
|
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
|
|
// 获取所有数据(不分页)
|
var tempSearchDto = new ReturnableStockSearchDto
|
{
|
PageIndex = 1,
|
PageSize = int.MaxValue, // 获取所有数据
|
Conditions = searchDto.Conditions
|
};
|
|
// 调用查询方法获取数据
|
var result = GetReturnableStocks(tempSearchDto);
|
var dataList = result.TbBillList;
|
|
// 创建 Excel 文件
|
using var package = new ExcelPackage();
|
var worksheet = package.Workbook.Worksheets.Add("可退货物料库存");
|
|
// 设置表头
|
var headers = new[]
|
{
|
"良品状态",
|
"是否成品",
|
"母托盘编号",
|
"仓库编码",
|
"仓库名称",
|
"虚拟库位",
|
"物料编号",
|
"物料名称",
|
"物料规格",
|
"物料数量",
|
"物料单位",
|
"入库时间",
|
"组织编码",
|
"组织名称",
|
"物料条码",
|
"库存状态"
|
};
|
|
// 写入表头
|
for (int i = 0; i < headers.Length; i++)
|
{
|
worksheet.Cells[1, i + 1].Value = headers[i];
|
worksheet.Cells[1, i + 1].Style.Font.Bold = true;
|
worksheet.Cells[1, i + 1].Style.Fill.PatternType =
|
ExcelFillStyle.Solid;
|
worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor
|
.SetColor(System.Drawing.Color.LightGray);
|
worksheet.Cells[1, i + 1].Style.HorizontalAlignment =
|
ExcelHorizontalAlignment.Center;
|
}
|
|
// 写入数据
|
int row = 2;
|
foreach (var item in dataList)
|
{
|
worksheet.Cells[row, 1].Value =
|
item.IqcStatus == "1" ? "良品" : "不良品";
|
worksheet.Cells[row, 2].Value = item.ItemType == "1" ? "成品" : "非成品";
|
worksheet.Cells[row, 3].Value = item.StackCode;
|
worksheet.Cells[row, 4].Value = item.DepotCode;
|
worksheet.Cells[row, 5].Value = item.DepotName;
|
worksheet.Cells[row, 6].Value = item.DepotSectionsCode;
|
worksheet.Cells[row, 7].Value = item.ItemNo;
|
worksheet.Cells[row, 8].Value = item.ItemName;
|
worksheet.Cells[row, 9].Value = item.ItemModel;
|
worksheet.Cells[row, 10].Value = item.Quantity;
|
worksheet.Cells[row, 11].Value = item.ItemUnitName;
|
worksheet.Cells[row, 12].Value =
|
item.IndepDate?.ToString("yyyy-MM-dd HH:mm:ss");
|
worksheet.Cells[row, 13].Value = item.OrgCode;
|
worksheet.Cells[row, 14].Value = item.OrgName;
|
worksheet.Cells[row, 15].Value = item.ItemBarcode;
|
worksheet.Cells[row, 16].Value = item.StockStatus;
|
row++;
|
}
|
|
// 自动调整列宽
|
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
|
|
// 返回 Excel 文件字节数组
|
return package.GetAsByteArray();
|
}
|
}
|
