owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/15平台订单收入.sql

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

/*
区域是order表中的area_code字段，订单编号是order表在的id字段，
技师名称是通过order表中coach_id去关联coach_users表然后获取coach_users表中的user_id字段再去关联member_users表对比member_users表id获取nickname字段做为技师名称，
店铺名称是order表通过shop_id关联shop_auth_records表对比shop_id是否相同，相同则获取shop_name做为店铺名称，不相同则空着即可，
用户名称是order表通过user_id关联member_users表对比member_users表id然后获取nickname字段做为用户名称，
订单金额是order表中的total_amount字段，
代理商是order表通过agent_id关联agent_infos表对比agent_infos表的id然后获取user_id字段去关联member_users表对比member_users表id获取nickname字段做为代理商，
接单时间是order表通过id去关联order_records表通过判断state字段的状态为已结单状态获取对应的created_at字段做为接单时间，
下单时间是order表中的created_at字段，，路费是order表中的traffic_amount字段，
技师分成、店铺分成、路费、渠道分成、业务员分成、代理商分成、市场经费、平台分成都是通过order表的id去关联wallet_split_records表通过split_type分账类型为师分成、店铺分成、路费、渠道分成、业务员分成、代理商分成、市场经费、平台分成分别对应获取split_amount字段
wallet_split_records表split_type分账类型是：技师分成  业务员分成  代理商分成  市场经费  平台分成   渠道分成  店铺分成
*/

DROP TABLE IF EXISTS report_platform_order_income;

CREATE TABLE report_platform_order_income AS
/* 使用WITH子句定义多个查询因子 */
/* 创建平台订单视图 */
DROP VIEW IF EXISTS view_platform_order_income;

CREATE OR REPLACE VIEW view_platform_order_income AS
SELECT
    base.area_code as area_code /* 区域 */,
    base.id as order_id /* 订单编号 */,
    tech_info.coach_name as coach_name /* 技师名称 */,
    shop_info.shop_name as shop_name /* 店铺名称 */,
    muser.nickname as user_name /* 用户名称 */,
    base.total_amount as total_amount /* 订单金额 */,
    agent_info.agent_name as agent_name /* 代理商 */,
    accept_time.accept_time as accept_time /* 接单时间 */,
    base.created_at as created_at /* 下单时间 */,
    split_info.coach_amount as coach_amount /* 技师分成 */,
    split_info.shop_amount as shop_amount /* 店铺分成 */,
    base.traffic_amount as traffic_amount /* 路费 */,
    split_info.channel_amount as channel_amount /* 渠道分成 */,
    split_info.salesman_amount as salesman_amount /* 业务员分成 */,
    split_info.agent_amount as agent_amount /* 代理商分成 */,
    split_info.market_amount as market_amount /* 市场经费 */,
    split_info.platform_amount as platform_amount /* 平台分成 */
FROM (
        SELECT ord.id /* 订单编号 */, ord.area_code /* 区域 */, ord.coach_id /* 技师ID */, ord.shop_id /* 店铺ID */, ord.user_id /* 用户ID */, ord.agent_id /* 代理商ID */, ord.total_amount /* 订单金额 */, ord.traffic_amount /* 路费 */, ord.created_at /* 下单时间 */
        FROM `order` ord /* 订单表 */
        WHERE
            ord.deleted_at IS NULL /* 未删除的订单 */
    ) base
    LEFT JOIN (
        SELECT cu.id /* 技师ID */, mu.nickname coach_name /* 技师名称 */
        FROM
            coach_users cu /* 技师表 */
            LEFT JOIN member_users mu ON cu.user_id = mu.id /* 用户表 */
    ) tech_info ON base.coach_id = tech_info.id /* 关联技师信息 */
    LEFT JOIN (
        SELECT sar.shop_id /* 店铺ID */, sar.shop_name /* 店铺名称 */
        FROM shop_auth_records sar /* 店铺认证记录表 */
    ) shop_info ON base.shop_id = shop_info.shop_id /* 关联店铺信息 */
    LEFT JOIN member_users muser ON base.user_id = muser.id /* 关联用户信息 */
    LEFT JOIN (
        SELECT ai.id /* 代理商ID */, mu.nickname agent_name /* 代理商名称 */
        FROM
            agent_infos ai /* 代理商表 */
            LEFT JOIN member_users mu ON ai.user_id = mu.id /* 用户表 */
    ) agent_info ON base.agent_id = agent_info.id /* 关联代理商信息 */
    LEFT JOIN (
        SELECT orec.order_id /* 订单ID */, orec.created_at accept_time /* 接单时间 */
        FROM order_records orec /* 订单记录表 */
        WHERE
            orec.state = 13 /* 已完成状态 */
    ) accept_time ON base.id = accept_time.order_id /* 关联接单时间 */
    LEFT JOIN (
        SELECT
            ws.order_id /* 订单ID */,
            MAX(
                CASE
                    WHEN ws.split_type = 'COACH' THEN ws.split_amount
                    ELSE 0
                END
            ) coach_amount /* 技师分成 */,
            MAX(
                CASE
                    WHEN ws.split_type = 'SHOP' THEN ws.split_amount
                    ELSE 0
                END
            ) shop_amount /* 店铺分成 */,
            MAX(
                CASE
                    WHEN ws.split_type = 'CHANNEL' THEN ws.split_amount
                    ELSE 0
                END
            ) channel_amount /* 渠道分成 */,
            MAX(
                CASE
                    WHEN ws.split_type = 'SALESMAN' THEN ws.split_amount
                    ELSE 0
                END
            ) salesman_amount /* 业务员分成 */,
            MAX(
                CASE
                    WHEN ws.split_type = 'AGENT' THEN ws.split_amount
                    ELSE 0
                END
            ) agent_amount /* 代理商分成 */,
            MAX(
                CASE
                    WHEN ws.split_type = 'MARKET' THEN ws.split_amount
                    ELSE 0
                END
            ) market_amount /* 市场经费 */,
            MAX(
                CASE
                    WHEN ws.split_type = 'PLATFORM' THEN ws.split_amount
                    ELSE 0
                END
            ) platform_amount /* 平台分成 */
        FROM wallet_split_records ws /* 钱包分账记录表 */
        GROUP BY
            ws.order_id
    ) split_info ON base.id = split_info.order_id /* 关联分账信息 */
ORDER BY base.created_at DESC /* 按下单时间倒序 */;

select * from view_platform_order_income;