111

tjx

6 天以前 cdb8a4354d8ddaecec8f9d53c7bb9ccf3fc86192

111

 StandardPda/MES.Service/Dto/service/MesPalletBinding.cs

@@ -8,7 +8,7 @@
    /// <summary>
    ///     操作：A绑定，F解绑
    /// </summary>
    public string Type { get; set; }
    public string? Type { get; set; }

    /// <summary>
    ///     栈板码
@@ -18,12 +18,12 @@
    /// <summary>
    ///     登录号
    /// </summary>
    public string LoginId { get; set; }
    public string? LoginId { get; set; }

    /// <summary>
    ///     sn列表
    /// </summary>
    public List<snList1> SnList { get; set; }
    public List<snList1>? SnList { get; set; }

    public class snList1
    {

 StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs

@@ -28,21 +28,21 @@
            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();

        // ReturnableStockDto增加一个库存状态的字段
        // 当allRackingTaskData中的Code为null时，库存状态为立库入库中
        // 当allRackingTaskData中的Code为200时，库存状态为已经在立库内
        // 对每个条码，取最新ID的记录，并过滤掉Code为500（表示失败）的记录
        var rackingTaskData = allRackingTaskData
            .GroupBy(x => x.ItemBarcode) // 按条码分组
            .Select(g => g.OrderByDescending(x => x.Id).First()) // 取每个条码最新的一条记录
            .Where(x => x.Code != "500") // 过滤掉Code为500的失败记录
            .ToList();
        // 1. 优化：使用原生SQL获取每个条码的最新记录（避免全表加载到内存）
        // 使用窗口函数ROW_NUMBER在数据库层面完成分组取最新记录
        var sql = @"
            SELECT ITEM_BARCODE, PalletCode, Code, Id
            FROM (SELECT ITEM_BARCODE,
                         PalletCode,
                         Code,
                         Id,
                         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 != '500') t
            WHERE rn = 1";
        
        var rackingTaskData = Db.Ado.SqlQuery<RackingTaskInfo>(sql);

        if (rackingTaskData == null || !rackingTaskData.Any())
        {
@@ -63,160 +63,182 @@
        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. 应用搜索条件
        // 1.2 提取搜索条件（提升作用域）
        var conditions = searchDto.Conditions;
        if (conditions != null)

        // 2. 优化：分批处理条码列表（避免IN查询过大）
        const int batchSize = 500; // 每批处理500条
        var allQueryResults = new List<StockQueryResult>();
        
        for (int i = 0; i < distinctBarcodes.Count; i += batchSize)
        {
            //当conditions.IqcStatus为1时查询的值就要是特采直接使用，已检，免检，1的值
            // 精确匹配条件
            if (!string.IsNullOrEmpty(conditions.IqcStatus))
            var batchBarcodes = distinctBarcodes.Skip(i).Take(batchSize).ToList();
            
            // 2.1 构建查询条件
            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) =>
                    (batchBarcodes.Contains(stock.ItemBarcode) ||
                     batchBarcodes.Contains(stock.StackCode)) &&
                    stock.Quantity > 0);

            // 2.2 应用搜索条件（在数据库层面过滤）
            if (conditions != null)
            {
                if (conditions.IqcStatus == "1")
                //当conditions.IqcStatus为1时查询的值就要是特采直接使用，已检，免检，1的值
                // 精确匹配条件
                if (!string.IsNullOrEmpty(conditions.IqcStatus))
                {
                    // 当IqcStatus为"1"时，查询特采直接使用、已检、免检状态
                    query = query.Where((stock, item, depot, org, unit) =>
                        stock.IqcStatus == "特采直接使用" ||
                        stock.IqcStatus == "已检" ||
                        stock.IqcStatus == "免检");
                    if (conditions.IqcStatus == "1")
                    {
                        // 当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);
                    }
                }
                else

                if (conditions.Quantity.HasValue)
                {
                    // 其他情况按原值匹配
                    query = query.Where((stock, item, depot, org, unit) =>
                        stock.IqcStatus == conditions.IqcStatus);
                        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);
                    }
                }
            }

            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))
            // 2.3 执行当前批次查询
            var batchQueryResult = query
                .OrderByDescending((stock, item, depot, org, unit) =>
                    stock.IndepDate)
                .Select((stock, item, depot, org, unit) => new StockQueryResult
                {
                    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);
                }
            }
                    IqcStatus = stock.IqcStatus,
                    DepotCode = stock.DepotsCode,
                    DepotName = depot.DepotName,
                    DepotSectionsCode = stock.DepotSectionsCode,
                    ItemNo = item.ItemNo,
                    ItemName = item.ItemName,
                    ItemModel = item.ItemModel,
                    Quantity = stock.Quantity,
                    ItemUnit = item.ItemUnit,
                    ItemUnitName = unit.Fname,
                    IndepDate = stock.IndepDate,
                    OrgCode = org.Fnumber,
                    OrgName = org.Fname,
                    ItemBarcode = stock.ItemBarcode,
                    StockStackCode = stock.StackCode
                })
                .ToList();
            
            allQueryResults.AddRange(batchQueryResult);
        }
        
        // 3. 合并所有批次的查询结果
        var queryResult = allQueryResults;

        // 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();
        // 4. 优化：构建条码映射字典（提高查找效率）
        var rackingTaskDict = rackingTaskData
            .ToDictionary(x => x.ItemBarcode, x => x);

        // 5. 在内存中转换为DTO，关联PalletCode并赋值ItemBarcode（优先使用箱条码StockStackCode）
        var tempDataList = queryResult.Select(x =>
@@ -225,12 +247,12 @@
            var barcodeToMatch = !string.IsNullOrEmpty(x.StockStackCode)
                ? x.StockStackCode
                : x.ItemBarcode;
            var rackingTask = rackingTaskData
                .Where(r => r.ItemBarcode == barcodeToMatch)
                .FirstOrDefault();
            
            // 使用字典查找，性能更优
            rackingTaskDict.TryGetValue(barcodeToMatch, out var rackingTask);

            // 根据Code值确定库存状态: null为立库入库中(0), 200为已在立库内(1)
            string stockStatus = "进入立库的路上"; // 默认为立库入库中
            string? stockStatus = "进入立库的路上"; // 默认为立库入库中
            if (rackingTask?.Code != null)
            {
                stockStatus = rackingTask.Code == "200" ? "已在立库中" : "进入立库的路上";
@@ -349,6 +371,39 @@
    }

    /// <summary>
    ///     辅助类：立库任务信息
    /// </summary>
    private class RackingTaskInfo
    {
        public string? ItemBarcode { get; set; }
        public string? PalletCode { get; set; }
        public string? Code { get; set; }
        public decimal Id { get; set; }
    }

    /// <summary>
    ///     辅助类：库存查询中间结果
    /// </summary>
    private class StockQueryResult
    {
        public string? IqcStatus { get; set; }
        public string? DepotCode { get; set; }
        public string? DepotName { get; set; }
        public string? DepotSectionsCode { get; set; }
        public string? ItemNo { get; set; }
        public string? ItemName { get; set; }
        public string? ItemModel { get; set; }
        public decimal? Quantity { get; set; }
        public string? ItemUnit { get; set; }
        public string? ItemUnitName { get; set; }
        public DateTime? IndepDate { get; set; }
        public string? OrgCode { get; set; }
        public string? OrgName { get; set; }
        public string? ItemBarcode { get; set; }
        public string? StockStackCode { get; set; }
    }

    /// <summary>
    ///     查询可以退货的物料(旧版本,保留兼容)
    /// </summary>
    /// <returns>可退货物料库存列表</returns>
@@ -418,7 +473,7 @@
                .FirstOrDefault();

            // 根据Code值确定库存状态: null为立库入库中(0), 200为已在立库内(1)
            string stockStatus = "0"; // 默认为立库入库中
            string? stockStatus = "0"; // 默认为立库入库中
            if (rackingTask?.Code != null)
            {
                stockStatus =
@@ -487,7 +542,7 @@
                }

                decimal messageId = 0;
                string taskCode = "";
                string? taskCode = "";

                // 根据条码查询XB_RACKING_TASK_SYXT_LOG表，查询max(PALLETCODE)和对应的widthType
                var rackingTaskInfo = Db.Queryable<XbRackingTaskSyxtLog>()
@@ -592,8 +647,8 @@
                        throw new Exception($"解析响应失败: {responseStr}");
                    }

                    var code = responseJson?.Code?.ToString();
                    var jsonMessage = responseJson?.JsonMessage?.ToString();
                    var code = responseJson?.Code?.ToString()();
                    var jsonMessage = responseJson?.JsonMessage?.ToString()();

                    // 7. 根据Code判断成功或失败
                    if (code == "200")
@@ -633,7 +688,7 @@
                            ? "任务请求失败"
                            : jsonMessage;
                        var failureMessage =
                            (string)("立库任务下发失败: " + errorMessage);
                            (string?)("立库任务下发失败: " + errorMessage);

                        Db.Updateable<MessageCenter>()
                            .SetColumns(it => it.Result == 0)
@@ -652,7 +707,7 @@
                    if (messageId > 0)
                    {
                        var exceptionMessage =
                            (string)("立库任务异常: " + ex.Message);
                            (string?)("立库任务异常: " + ex.Message);
                        Db.Updateable<MessageCenter>()
                            .SetColumns(it => it.Result == 0)
                            .SetColumns(it =>