water/server/plugin/saiadmin/db/20260822_seed_merchant_daily_statistics.sql

-- ============================================================
-- 商户每日统计演示数据
-- 日期: 2026-08-22
-- 说明: 为【演示数据】商户生成最近 60 天数据；INSERT IGNORE 保证可重复执行，
--       不会覆盖之后由真实订单任务生成的同日统计。
-- ============================================================

INSERT IGNORE INTO `sa_merchant_daily_statistics` (
    `merchant_id`, `stat_date`, `turnover_cents`, `income_cents`, `order_count`,
    `wechat_turnover_cents`, `alipay_turnover_cents`, `balance_turnover_cents`,
    `wechat_order_count`, `alipay_order_count`, `balance_order_count`,
    `device_usage_rate`, `device_count`, `create_time`, `update_time`
)
SELECT
    base.`merchant_id`,
    base.`stat_date`,
    base.`turnover_cents`,
    ROUND(base.`turnover_cents` * base.`profit_rate` / 100),
    base.`order_count`,
    FLOOR(base.`turnover_cents` * 0.56),
    FLOOR(base.`turnover_cents` * 0.34),
    base.`turnover_cents` - FLOOR(base.`turnover_cents` * 0.56) - FLOOR(base.`turnover_cents` * 0.34),
    FLOOR(base.`order_count` * 0.56),
    FLOOR(base.`order_count` * 0.34),
    base.`order_count` - FLOOR(base.`order_count` * 0.56) - FLOOR(base.`order_count` * 0.34),
    base.`device_usage_rate`,
    base.`device_count`,
    NOW(),
    NOW()
FROM (
    SELECT
        merchant.`id` AS `merchant_id`,
        DATE_SUB(CURDATE(), INTERVAL day_offset.`n` DAY) AS `stat_date`,
        CASE
            WHEN merchant.`total_amount` <= 0 THEN 0
            ELSE 1800 + MOD(merchant.`id` * 193 + day_offset.`n` * 431, 14000)
        END AS `turnover_cents`,
        CASE
            WHEN merchant.`total_amount` <= 0 THEN 0
            ELSE 2 + MOD(merchant.`id` * 7 + day_offset.`n` * 3, 18)
        END AS `order_count`,
        CASE
            WHEN merchant.`device_count` <= 0 THEN 0
            ELSE ROUND(28 + MOD(merchant.`id` * 17 + day_offset.`n` * 11, 6900) / 100, 2)
        END AS `device_usage_rate`,
        merchant.`device_count`,
        merchant.`profit_rate`
    FROM `sa_merchant` merchant
    CROSS JOIN (
        SELECT ones.`n` + tens.`n` * 10 AS `n`
        FROM (
            SELECT 0 AS `n` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
            UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
        ) ones
        CROSS JOIN (
            SELECT 0 AS `n` UNION ALL SELECT 1 UNION ALL SELECT 2
            UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        ) tens
    ) day_offset
    WHERE merchant.`delete_time` IS NULL
      AND merchant.`description` LIKE '【演示数据】%'
) base;