# 数据看板新查询逻辑设计

## 1. 新查询逻辑概述

### 1.1 基本思路
- 从到货交接单（arrival_handover_forms）开始查询
- 以到货交接单的交接单号（HandoverNumber）去匹配标签替换请求（label_replace_requests）表中的提单号（BillOfLadingNumber）或主包号（MasterPackageNumber）
- 保持现有的两个模块：提单号预报和大箱号预报

### 1.2 实现步骤
1. 获取所有符合条件的到货交接单
2. 对于每个到货交接单，根据其交接单号匹配标签替换请求
3. 按提单号或大箱号分组统计
4. 计算各种统计指标

## 2. 具体实现逻辑

### 2.1 提单号预报查询逻辑
1. 获取所有符合条件的到货交接单
2. 对于每个到货交接单，以其交接单号作为提单号，匹配 label_replace_requests 表中的 BillOfLadingNumber
3. 按 BillOfLadingNumber 分组统计
4. 计算统计指标

### 2.2 大箱号预报查询逻辑
1. 获取所有符合条件的到货交接单
2. 对于每个到货交接单，以其交接单号作为大箱号，匹配 label_replace_requests 表中的 MasterPackageNumber
3. 按 MasterPackageNumber 分组统计
4. 计算统计指标

## 3. 代码实现设计

### 3.1 修改 LabelReplaceService.cs 中的 GetDashboardDataAsync 方法

```csharp
public async Task<List<DashboardDataDto>> GetDashboardDataAsync(string type, string billOfLadingNumber, string masterPackageNumber, string startDate, string endDate, int? customerId)
{
    try
    {
        _logger.LogInformation("Retrieving dashboard data. Type: {type}, BillOfLading: {billOfLading}, MasterPackage: {masterPackage}", 
            type, billOfLadingNumber, masterPackageNumber);

        // 1. 获取所有符合条件的到货交接单
        var allArrivalForms = await _arrivalHandoverFormService.GetAllAsync();

        // 2. 过滤到货交接单
        var filteredArrivalForms = allArrivalForms.Where(form =>
        {
            // 按日期过滤
            if (!string.IsNullOrEmpty(startDate))
            {
                var start = DateTime.Parse(startDate);
                if (form.ReceiptTime < start)
                    return false;
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                var end = DateTime.Parse(endDate);
                if (form.ReceiptTime > end)
                    return false;
            }

            return true;
        }).ToList();

        // 3. 获取所有标签替换请求
        var allRequests = await _labelReplaceRepository.GetAllAsync();

        // 4. 按类型匹配数据
        var matchedRequests = new List<LabelReplaceEntity>();
        foreach (var form in filteredArrivalForms)
        {
            if (type == "billOfLading")
            {
                // 提单号预报：以交接单号匹配提单号
                var requests = allRequests.Where(req => 
                    req.BillOfLadingNumber == form.HandoverNumber &&
                    (!customerId.HasValue || req.CustomerId == customerId.Value) &&
                    (string.IsNullOrEmpty(billOfLadingNumber) || req.BillOfLadingNumber == billOfLadingNumber)
                );
                matchedRequests.AddRange(requests);
            }
            else
            {
                // 大箱号预报：以交接单号匹配主包号
                var requests = allRequests.Where(req => 
                    req.MasterPackageNumber == form.HandoverNumber &&
                    (!customerId.HasValue || req.CustomerId == customerId.Value) &&
                    (string.IsNullOrEmpty(masterPackageNumber) || req.MasterPackageNumber == masterPackageNumber)
                );
                matchedRequests.AddRange(requests);
            }
        }

        // 5. 按提单号或大箱号分组
        var groupedData = new Dictionary<string, List<LabelReplaceEntity>>();
        foreach (var req in matchedRequests)
        {
            string key;
            if (type == "billOfLading")
            {
                key = req.BillOfLadingNumber ?? "Unknown";
            }
            else
            {
                key = req.MasterPackageNumber ?? "Unknown";
            }

            if (!groupedData.ContainsKey(key))
            {
                groupedData[key] = new List<LabelReplaceEntity>();
            }
            groupedData[key].Add(req);
        }

        // 6. 处理每个分组的数据
        var dashboardData = new List<DashboardDataDto>();
        foreach (var group in groupedData)
        {
            var requests = group.Value;
            if (requests.Count == 0)
                continue;

            var firstRequest = requests.First();

            // 获取客户简称
            string customerCode = "Unknown";
            if (firstRequest.CustomerId.HasValue)
            {
                var customer = await _customerRepository.GetByIdAsync(firstRequest.CustomerId.Value);
                if (customer != null)
                {
                    customerCode = customer.CustomerCode;
                }
            }

            // 计算到货订单数量
            int arrivalOrderCount = requests.Count;

            // 计算无标签数据数量
            int noLabelDataCount = requests.Count(req => string.IsNullOrEmpty(req.Label));

            // 计算已有标签订单数
            int labeledOrderCount = requests.Count(req => !string.IsNullOrEmpty(req.Label));

            // 计算已有标签率
            string labelRate = "0%";
            if (arrivalOrderCount > 0)
            {
                double rate = (double)labeledOrderCount / arrivalOrderCount * 100;
                labelRate = $"{rate:F2}%";
            }

            // 计算换单完成数量（根据扫描记录）
            int replaceCompletedCount = 0;
            try
            {
                // 收集所有需要查询的中性面单单号
                var waybillNumbers = requests.Select(req => req.NeutralWaybillNumber).Where(num => !string.IsNullOrEmpty(num)).Distinct().ToList();
                
                if (waybillNumbers.Count > 0)
                {
                    // 一次性批量查询所有扫描记录
                    var allScans = await _labelScanService.GetScanRecordsByNeutralWaybillNumbersAsync(waybillNumbers);
                    
                    // 在内存中处理结果
                    var returnedLabelScans = allScans.Where(scan => scan.Result == ScanResult.ReturnedLabel).Select(scan => scan.NeutralWaybillNumber).ToHashSet();
                    
                    // 统计换单完成数量
                    replaceCompletedCount = requests.Count(req => !string.IsNullOrEmpty(req.NeutralWaybillNumber) && returnedLabelScans.Contains(req.NeutralWaybillNumber));
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error calculating replace completed count");
                // 如果批量查询失败，回退到单条查询
                foreach (var req in requests)
                {
                    try
                    {
                        var scans = await _labelScanService.GetScanRecordsByNeutralWaybillNumberAsync(req.NeutralWaybillNumber);
                        if (scans.Any(scan => scan.Result == ScanResult.ReturnedLabel))
                        {
                            replaceCompletedCount++;
                        }
                    }
                    catch (Exception innerEx)
                    {
                        _logger.LogError(innerEx, "Error querying scan records for waybill: {WaybillNumber}", req.NeutralWaybillNumber);
                    }
                }
            }

            // 计算未换单完成数量
            int replacePendingCount = arrivalOrderCount - replaceCompletedCount;

            // 获取到货时间（从到货交接单表中获取）
            DateTime? arrivalTime = null;
            try
            {
                // 尝试从到货交接单表中获取到货时间
                string handoverNumber = type == "billOfLading" ? firstRequest.BillOfLadingNumber : firstRequest.MasterPackageNumber;
                if (!string.IsNullOrEmpty(handoverNumber))
                {
                    var arrivalForms = await _arrivalHandoverFormService.GetArrivalHandoverFormsByHandoverNumberAsync(handoverNumber);
                    if (arrivalForms != null && arrivalForms.Count > 0)
                    {
                        arrivalTime = arrivalForms.Min(form => form.ReceiptTime);
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error retrieving arrival time from handover forms");
                // 如果出错，不显示到货时间
                arrivalTime = null;
            }

            // 创建数据看板DTO
            var dto = new DashboardDataDto
            {
                Key = group.Key,
                CustomerCode = customerCode,
                ArrivalOrderCount = arrivalOrderCount,
                ReplaceCompletedCount = replaceCompletedCount,
                ReplacePendingCount = replacePendingCount,
                NoLabelDataCount = noLabelDataCount,
                LabeledOrderCount = labeledOrderCount,
                LabelRate = labelRate,
                ArrivalTime = arrivalTime,
                BillOfLadingNumber = firstRequest.BillOfLadingNumber,
                MasterPackageNumber = firstRequest.MasterPackageNumber
            };

            dashboardData.Add(dto);
        }

        _logger.LogInformation("Retrieved {count} dashboard data items", dashboardData.Count);
        return dashboardData;
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error retrieving dashboard data");
        return new List<DashboardDataDto>();
    }
}
```

## 4. 性能优化考虑

### 4.1 索引优化
- 为 `arrival_handover_forms` 表的 `HandoverNumber` 字段添加索引
- 为 `label_replace_requests` 表的 `BillOfLadingNumber` 和 `MasterPackageNumber` 字段添加索引
- 为 `label_replace_requests` 表的 `CustomerId` 字段添加索引

### 4.2 查询优化
- 使用批量查询减少数据库访问次数
- 避免在循环中进行数据库查询
- 合理使用内存缓存

### 4.3 数据量控制
- 考虑添加分页功能
- 对于历史数据，可以考虑归档策略

## 5. 测试计划

### 5.1 功能测试
- 测试提单号预报功能
- 测试大箱号预报功能
- 测试各种过滤条件

### 5.2 性能测试
- 测试大数据量下的查询性能
- 测试响应时间

### 5.3 数据准确性测试
- 使用测试数据验证统计结果的准确性
- 与手动计算结果进行比较

## 6. 注意事项

- 处理一个到货交接单对应多个提单号或主包号的情况
- 处理标签替换请求中没有提单号或主包号的情况
- 确保与前端API接口兼容
- 保持代码的可读性和可维护性