111

tjx

17 小时以前 367979de838ec3655d37dc618d98924c6b43b324

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

@@ -26,9 +26,9 @@

        // 优化点1: 使用原生SQL ROW_NUMBER()在数据库端完成去重和排序
        var rackingTaskSql = @"
            SELECT ITEM_BARCODE AS ItemBarcode, PALLETCODE AS PalletCode, CODE AS Code
            SELECT ITEM_BARCODE AS ItemBarcode, PALLETCODE AS PalletCode, CODE AS Code, TASKTYPE AS TaskType
            FROM (
                SELECT ITEM_BARCODE, PALLETCODE, CODE,
                SELECT ITEM_BARCODE, PALLETCODE, CODE, TASKTYPE,
                       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')
@@ -204,6 +204,7 @@
            .Select((stock, item, depot, org, unit) => new
            {
                stock.IqcStatus,
                stock.Visible,
                DepotCode = stock.DepotsCode,
                depot.DepotName,
                stock.DepotSectionsCode,
@@ -230,15 +231,27 @@
            
            rackingTaskDict.TryGetValue(barcodeToMatch, out var rackingTask);

            string stockStatus = "进入立库的路上";
            if (rackingTask?.Code != null)
            string stockStatus;
            if (rackingTask?.TaskType == "立库出库请求")
            {
                stockStatus = rackingTask.Code == "200" ? "已在立库中" : "进入立库的路上";
                // 出库请求：Code=200表示已出库，Code=null表示出库路上
                stockStatus = rackingTask.Code == "200" ? "已出立库" : "出立库路上";
            }
            else
            {
                // 回库请求（默认）：Code=200表示已在立库中，Code=null表示进入立库的路上
                stockStatus = rackingTask?.Code == "200" ? "已在立库中" : "进入立库的路上";
            }

            // 冻结标识：Visible为1表示已冻结
            var frozenValue = x.Visible == 1 ? "1" : "0";
            // 良品状态：根据IqcStatus判断（独立于冻结标识）
            var iqcStatusValue = x.IqcStatus == "特采直接使用" || x.IqcStatus == "已检" || x.IqcStatus == "免检" ? "1" : "0";

            return new
            {
                IqcStatus = x.IqcStatus == "已检" ? "1" : "0",
                IqcStatus = iqcStatusValue,
                Frozen = frozenValue,
                ItemType = x.DepotName == "原材料仓" ? "0" : "1",
                StackCode = rackingTask?.PalletCode,
                x.DepotCode,
@@ -278,6 +291,7 @@
            .GroupBy(x => new
            {
                x.IqcStatus,
                x.Frozen,
                x.ItemType,
                x.StackCode,
                x.DepotCode,
@@ -296,6 +310,7 @@
            .Select(g => new ReturnableStockDto
            {
                IqcStatus = g.Key.IqcStatus,
                Frozen = g.Key.Frozen,
                ItemType = g.Key.ItemType,
                StackCode = g.Key.StackCode,
                DepotCode = g.Key.DepotCode,
@@ -340,109 +355,13 @@
            }
        };
    }

    /// <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; }
        public string TaskType { get; set; }
    }

    /// <summary>
@@ -488,6 +407,20 @@
                    .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
                {
@@ -701,6 +634,7 @@
        var headers = new[]
        {
            "良品状态",
            "冻结标识",
            "是否成品",
            "母托盘编号",
            "仓库编码",
@@ -737,22 +671,23 @@
        {
            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 =
            worksheet.Cells[row, 2].Value = item.Frozen == "1" ? "已冻结" : "未冻结";
            worksheet.Cells[row, 3].Value = item.ItemType == "1" ? "成品" : "非成品";
            worksheet.Cells[row, 4].Value = item.StackCode;
            worksheet.Cells[row, 5].Value = item.DepotCode;
            worksheet.Cells[row, 6].Value = item.DepotName;
            worksheet.Cells[row, 7].Value = item.DepotSectionsCode;
            worksheet.Cells[row, 8].Value = item.ItemNo;
            worksheet.Cells[row, 9].Value = item.ItemName;
            worksheet.Cells[row, 10].Value = item.ItemModel;
            worksheet.Cells[row, 11].Value = item.Quantity;
            worksheet.Cells[row, 12].Value = item.ItemUnitName;
            worksheet.Cells[row, 13].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;
            worksheet.Cells[row, 14].Value = item.OrgCode;
            worksheet.Cells[row, 15].Value = item.OrgName;
            worksheet.Cells[row, 16].Value = item.ItemBarcode;
            worksheet.Cells[row, 17].Value = item.StockStatus;
            row++;
        }