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子句定义多个查询因子 */
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 = 'COMPLETED' /* 已完成状态 */
) 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 /* 按下单时间倒序 */;