# 数据库视图逻辑修复计划

## 问题分析

### 当前问题表现
- 视图查询结果大多为0
- 仅 `DailyShouldReplaceCount` = 3592 和 `CumulativeTotalReplaceCount` = 3315 有数据
- 其他指标（新增数、完成数、到仓数等）全为0

### 根本原因
当前视图SQL存在以下关键问题：

1. **硬编码NOW()比较问题** （第17、24、34、42、49、56、62、69、76、83、91、100行）
   - 使用 `CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)` 与 `NOW()` 进行比较
   - 每次查询都与当前时间比较，导致查询特定历史日期时大部分条件都不满足
   - 应该改为接受参数化的查询日期

2. **JOIN逻辑混乱**（第109-115行）
   - 对`label_scan_history`的JOIN使用子查询且索引方式错误
   - 对`arrival_handover_forms`的JOIN条件不合理：`r.BillOfLadingNumber = ahf.HandoverNumber OR r.MasterPackageNumber = ahf.HandoverNumber`
   - 应该支持多种匹配方式，但需要明确的业务逻辑

3. **时区转换过度** （多个CONVERT_TZ调用）
   - 重复的CONVERT_TZ导致性能下降
   - 应该在应用层处理时区转换，简化视图逻辑

4. **指标计算与业务逻辑不一致**
   - `DailyShouldReplaceCount` 应该基于 `arrival_handover_forms` 表的接收时间
   - 当前逻辑：基于 `label_replace_requests` 的创建时间判断
   - 业务逻辑（源自MetricsCalculationService）：
     - 先获取当日到仓的交接单（按ReceiptTime）
     - 计算每个交接单的标签率（≥80%时才计为应换单）
     - 统计满足条件的订单数

## 修复方案

### 方案1：修改为参数化查询的视图（推荐）

**思路**：不在视图中使用 `NOW()`，改为在应用层传入查询日期，从而支持灵活的日期范围查询

**优点**：
- 支持查询任意日期的指标
- 简化SQL逻辑，提高性能
- 易于调试和验证

**缺点**：
- 不能直接在SQL中使用 `SELECT * FROM view WHERE date = '2026-05-15'`
- 需要使用存储过程或改为表函数

### 方案2：创建存储过程（更优）

**思路**：使用存储过程接受 `QueryDate` 参数，动态生成查询语句

**优点**：
- 完全灵活，支持任意日期查询
- 保持SQL优化和性能
- 易于维护和扩展

**缺点**：
- 需要修改应用层调用方式

### 方案3：直接修复当前视图（临时方案）

**思路**：
1. 在视图中使用 `CURDATE()` 代替 `NOW()`
2. 简化JOIN逻辑
3. 修正指标计算

**缺点**：
- 只能查询当前日期数据
- 不符合长期需求

## 选择：方案2（存储过程）

### 原因
1. 最符合实际业务需求
2. 应用层已有 `db.SqlQueryable<dynamic>(sql)` 的调用方式
3. 易于与C#应用集成

### 实现步骤

#### 步骤1：创建存储过程 `sp_GetDailyMetricsSummary`

存储过程需要接受一个 `QueryDate` 参数（YYYY-MM-DD格式），返回当天的所有指标。

**核心逻辑**：

```sql
DELIMITER //
CREATE PROCEDURE sp_GetDailyMetricsSummary(IN p_date DATE)
BEGIN
  -- 时间范围定义（UTC-5时区）
  -- 开始时间：查询日期 00:00 (UTC-5)
  -- 结束时间：查询日期 23:59:59 (UTC-5)
  
  SELECT 
    p_date AS MetricsDate,
    
    -- 1. DailyNewReplaceCount：当天新增应换单数
    -- 来源：当日到仓的交接单中，标签率≥80%的订单数
    
    -- 2. DailyShouldReplaceCount：应该换单数（累计）
    -- 来源：所有到仓的交接单（直到今天）中，标签率≥80%的订单数
    
    -- 3. DailySuccessCount：当天完成数
    -- 来源：当日扫描成功(Result=0)的订单数
    
    -- 4. CumulativeTotalReplaceCount：累计未完成数
    -- 来源：历史到仓记录中，未扫描成功的订单数
    
    -- 5. DailyStopCount：当日冻结数
    -- 来源：当日扫描结果中包含"STOP"的订单数
    
    -- 6. DailyLabelPushCount：当日标签推送数
    -- 来源：当日新增标签的订单数(Label不为空)
    
    -- 7. DailyScanCount：当日扫描总数
    
    -- 8. BeforeNoonArrivedCount：16点前到仓数
    -- 来源：当日到仓时间(ReceiptTime) < 16:00 (UTC-5)的订单数
    
    -- 9. AfternoonArrivedCount：16点后到仓数
    
    -- 10. BeforeNoonPassedCount：16点前完成数
    -- 来源：16点前到仓的订单中，在次日16:00前扫描成功的订单数
    
    -- 11. AfternoonPassedCount：16点后完成数
    
    -- 12. DailyFailureCount：当日失败数
    
    NOW() AS DataFetchTime
  FROM (
    -- 基础数据集：当日及历史到仓记录
    SELECT 
      r.Id AS OrderId,
      r.NeutralWaybillNumber,
      r.Label,
      ahf.HandoverNumber,
      ahf.ReceiptTime,
      s.Id AS ScanId,
      s.Result,
      s.Description,
      s.CreatedAt AS ScanCreatedAt
    FROM label_replace_requests r
    LEFT JOIN arrival_handover_forms ahf ON 
      r.BillOfLadingNumber = ahf.HandoverNumber 
      OR r.MasterPackageNumber = ahf.HandoverNumber
    LEFT JOIN (
      SELECT * FROM label_scan_history 
      WHERE CAST(DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00'))) AS DATE) >= DATE_SUB(p_date, INTERVAL 90 DAY)
    ) s ON r.NeutralWaybillNumber = s.NeutralWaybillNumber
    WHERE r.Label IS NOT NULL
  ) base_data
  GROUP BY p_date;
END //
DELIMITER ;
```

**关键业务规则**（基于MetricsCalculationService）：

1. **DailyShouldReplaceCount / BeforeNoonArrivedCount / AfternoonArrivedCount**
   - 获取当日接收的交接单（ReceiptTime在p_date这一天UTC-5）
   - 计算每个交接单的标签率（标签订单数 / 总订单数）
   - 只统计标签率≥80%的订单
   - 按16点划分

2. **DailySuccessCount / BeforeNoonPassedCount / AfternoonPassedCount**
   - 对于16点前到仓的订单：统计次日16:00前扫描成功的数量
   - 对于16点后到仓的订单：统计本日16:00前扫描成功的数量
   - Result = 0 表示扫描成功

3. **CumulativeTotalReplaceCount**
   - 统计所有历史到仓记录（直到昨天）中，未扫描成功的订单

4. **DailyFailureCount**
   - DailyShouldReplaceCount - DailySuccessCount

#### 步骤2：修改应用层调用

在 `MetricsCalculationService.GetDailySummaryAsync()` 中：

```csharp
string sql = $"CALL sp_GetDailyMetricsSummary('{date:yyyy-MM-dd}')";
var summaryList = await db.SqlQueryable<dynamic>(sql).ToListAsync();
```

#### 步骤3：验证和测试

1. 在数据库中执行 `CALL sp_GetDailyMetricsSummary('2026-05-15')`
2. 验证返回的各项指标是否大于0（符合实际数据）
3. 对比原始的11个异步查询结果
4. 性能测试：确保<1秒完成

## 风险评估

| 风险项 | 概率 | 影响 | 缓解方案 |
|--------|------|------|---------|
| 存储过程语法错误 | 中 | 中 | 保留原始视图作为备份，逐行测试 |
| 业务逻辑理解偏差 | 中 | 高 | 与用户确认每个指标的定义，对比原始结果 |
| 性能不达预期 | 低 | 中 | 添加必要索引，优化查询计划 |
| 时区转换错误 | 低 | 高 | 充分测试UTC-5转换逻辑，验证样本数据 |

## 实现时间表

1. **编写存储过程SQL** - 验证语法正确
2. **在数据库创建存储过程** - 确保无错误
3. **用样本数据测试** - 对比原始结果
4. **修改应用层代码** - 调整GetDailySummaryAsync方法
5. **集成测试** - 前端调用验证
6. **性能验证** - 确保<1秒目标
7. **部署** - 发布到生产环境

## 备选方案

如果存储过程方案遇到困难，改为直接优化视图：

1. 移除所有 `NOW()` 比较
2. 在应用层计算日期范围（UTC-5）
3. 使用 `BETWEEN` 比较日期范围而非精确日期
4. 简化JOIN逻辑，分离查询