-- 按周统计（UTC-5时间）- 每周日到周六 
SELECT 
    week_start,
    COUNT(DISTINCT id) AS total_orders,
    SUM(CASE WHEN ReplaceStatus = 'N' THEN 1 ELSE 0 END) AS cancelled_orders,
    (SELECT COUNT(*) 
     FROM label_scan_history 
     WHERE DATE(DATE_ADD(DATE_SUB(DATE_ADD(createdat, INTERVAL -5 HOUR), INTERVAL (WEEKDAY(DATE_ADD(createdat, INTERVAL -5 HOUR)) + 1) % 7 DAY), INTERVAL 0 HOUR)) = outer_query.week_start
    ) AS scan_count,
    (SELECT COUNT(*) 
     FROM label_scan_history 
     WHERE DATE(DATE_ADD(DATE_SUB(DATE_ADD(createdat, INTERVAL -5 HOUR), INTERVAL (WEEKDAY(DATE_ADD(createdat, INTERVAL -5 HOUR)) + 1) % 7 DAY), INTERVAL 0 HOUR)) = outer_query.week_start
       AND Result = 0 
    ) AS success_scan_count,
    (SELECT COUNT(*) 
     FROM label_scan_history 
     WHERE DATE(DATE_ADD(DATE_SUB(DATE_ADD(createdat, INTERVAL -5 HOUR), INTERVAL (WEEKDAY(DATE_ADD(createdat, INTERVAL -5 HOUR)) + 1) % 7 DAY), INTERVAL 0 HOUR)) = outer_query.week_start
       AND Result != 0 
    ) AS failed_scan_count,
    -- 每周集包数量 
    (SELECT COUNT(DISTINCT btw.id) 
     FROM bag_tags bt 
     JOIN bag_tag_waybills btw ON bt.TagNumber = btw.TagNumber 
     JOIN label_replace_requests lrr2 ON btw.FinalMileTrackingNumber = lrr2.FinalMileTrackingNumber 
     WHERE DATE(DATE_ADD(DATE_SUB(DATE_ADD(btw.createdat, INTERVAL -5 HOUR), INTERVAL (WEEKDAY(DATE_ADD(btw.createdat, INTERVAL -5 HOUR)) + 1) % 7 DAY), INTERVAL 0 HOUR)) = outer_query.week_start
    ) AS bag_count,
    -- 集包扫描次数 
    (SELECT COUNT(*) 
     FROM order_logs 
     WHERE DATE(DATE_ADD(DATE_SUB(DATE_ADD(createdat, INTERVAL -5 HOUR), INTERVAL (WEEKDAY(DATE_ADD(createdat, INTERVAL -5 HOUR)) + 1) % 7 DAY), INTERVAL 0 HOUR)) = outer_query.week_start
       AND OperationType = '集包' 
    ) AS bag_scan_count,
    -- 订单所有操作失败次数 
    (SELECT COUNT(*) 
     FROM order_logs 
     WHERE DATE(DATE_ADD(DATE_SUB(DATE_ADD(createdat, INTERVAL -5 HOUR), INTERVAL (WEEKDAY(DATE_ADD(createdat, INTERVAL -5 HOUR)) + 1) % 7 DAY), INTERVAL 0 HOUR)) = outer_query.week_start
       AND OperationResult = '失败' 
    ) AS order_failed_operations
FROM (
    SELECT 
        id,
        ReplaceStatus,
        DATE(DATE_ADD(DATE_SUB(DATE_ADD(createdat, INTERVAL -5 HOUR), INTERVAL (WEEKDAY(DATE_ADD(createdat, INTERVAL -5 HOUR)) + 1) % 7 DAY), INTERVAL 0 HOUR)) AS week_start
    FROM label_replace_requests
) outer_query
GROUP BY week_start
ORDER BY week_start;