owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/11店铺订单.sql

-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test

/*
用户类型是order表中的order_source字段，用户昵称/店铺是通过order的order_source字段判断是用户通过平台下单是店铺在平台下应急订单，
用户昵称是order表通过user_id关联member_users表获取nickname字段为用户名称，
店铺名称是order表通过shop_id关联shop_auth_records并判断shop_id是否存在存在则获取shop_name字段为店铺名称不存在则空着即可，
订单类型是order表中的order_type字段，项目名称是order表的project_id关联shop_services表并判断是否存在project_id存在则获取name为项目名称不存在则空着即可，
订单金额是order表total_amount字段，到店时间是order表id关联order_records表并判断状态state为已到店状态时的created_at字段为到店时间，
订单状态是order表id关联order_records表最新的状态state,
店铺分成、推广分成、平台抽成都是order表id关联wallet_split_records通过该表中的split_type不同的分账类型去参与分成最后split_amount字段就是店铺分成、推广分成、平台抽成最终字段
*/

/*
基础订单信息与分账记录统计查询
*/
DROP TABLE IF EXISTS report_shop_order;

CREATE TABLE report_shop_order AS
/*
店铺订单视图
*/

DROP VIEW IF EXISTS manage_shop_order;

CREATE OR REPLACE VIEW manage_shop_order AS
WITH
    order_arrival AS (
        /* 获取订单到店记录 */
        SELECT
            order_id,
            created_at AS arrival_time,
            state
        FROM order_records /* 关联到店记录 */
        WHERE
            state = 10 /* 已到店状态 */
    ),
    latest_order_status AS (
        /* 获取订单最新状态 */
        SELECT order_id, state AS latest_state
        FROM order_records /* 关联订单状态 */
        WHERE (order_id, created_at) IN (
                SELECT order_id, MAX(created_at)
                FROM order_records /* 关联订单状态 */
                GROUP BY
                    order_id /* 按订单ID分组 */
            )
    ),
    split_amounts AS (
        /* 获取各类分账金额 */
        SELECT
            order_id,
            MAX(
                CASE
                    WHEN split_type = 'SHOP' THEN split_amount
                    ELSE 0
                END
            ) AS shop_amount /* 店铺分成 */,
            MAX(
                CASE
                    WHEN split_type = 'PROMOTION' THEN split_amount
                    ELSE 0
                END
            ) AS promotion_amount /* 推广分成 */,
            MAX(
                CASE
                    WHEN split_type = 'PLATFORM' THEN split_amount
                    ELSE 0
                END
            ) AS platform_amount /* 平台抽成 */
        FROM wallet_split_records /* 关联分账记录 */
        GROUP BY
            order_id /* 按订单ID分组 */
    )
SELECT
    o.source as source /* 订单来源 */,
    CASE
        WHEN o.source = 1 THEN mu.nickname
        WHEN o.source = 2 THEN sar.shop_name
        ELSE NULL
    END as source_name /* 用户昵称/店铺名称 */,
    o.type as type /* 订单类型 */,
    ss.name as service_name /* 项目名称 */,
    o.total_amount as total_amount /* 订单金额 */,
    oa.arrival_time as arrival_time /* 到店时间 */,
    los.latest_state as latest_state /* 订单状态 */,
    COALESCE(sar.contact_phone, '') as shop_phone /* 店铺联系电话 */,
    COALESCE(mu.mobile, '') as coach_phone /* 技师联系方式 */,
    o.area_code as area_code /* 区域 */,
    o.address as address /* 位置 */,
    o.distance as distance /* 距离 */,
    COALESCE(sa.shop_amount, 0) as shop_amount /* 店铺分成 */,
    COALESCE(sa.promotion_amount, 0) as promotion_amount /* 推广分成 */,
    COALESCE(sa.platform_amount, 0) as platform_amount /* 平台抽成 */
FROM
    `order` o
    LEFT JOIN member_users mu ON o.user_id = mu.id /* 关联用户信息 */
    LEFT JOIN shop_auth_records sar ON o.shop_id = sar.shop_id /* 关联店铺信息 */
    LEFT JOIN shop_services ss ON o.project_id = ss.id /* 关联服务项目 */
    LEFT JOIN order_arrival oa ON o.id = oa.order_id /* 关联到店记录 */
    LEFT JOIN latest_order_status los ON o.id = los.order_id /* 关联最新状态 */
    LEFT JOIN split_amounts sa ON o.id = sa.order_id /* 关联分账信息 */
ORDER BY o.id DESC /* 按订单ID倒序排列 */;

select * from manage_shop_order;