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

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

/*
区域、交易时间、代理商分账都是wallet_trans_records表role字段为代理商角色然后分别获取districe、trans_time、amount字段，
代理商是wallet_trans_records表role字段为代理商角色然后获取wallet_id字段关联wallet表获取owner_id字段再与agent_infos表关联通过agent_infos表的id进行对比然后获取user_id字段和member_users表关联通过member_users的id对比最后获取nickname字段做为代理商，
平台分账与代理商分账是一样的数据，市场经费是代理商分账的一半
*/


/* 区域、交易时间、代理商、代理商分账、平台分账、市场经费统计 */
DROP TABLE IF EXISTS report_agent_income;
CREATE TABLE report_agent_income AS
WITH trans_base AS (
    SELECT 
        wtr.district /* 区域 */,
        wtr.trans_time /* 交易时间 */,
        wtr.wallet_id /* 钱包ID */,
        wtr.amount /* 交易金额 */,
        w.owner_id /* 所属主体编号 */
    FROM wallet_trans_records wtr
    INNER JOIN wallet w ON w.id = wtr.wallet_id
    WHERE wtr.role = '代理商'
        AND wtr.deleted_at IS NULL
)
SELECT 
    tb.district as district /* 区域 */,
    tb.trans_time as trans_time /* 交易时间 */,
    mu.nickname as nickname /* 代理商昵称 */,
    tb.amount as agent_amount /* 代理商分账 */,
    tb.amount as platform_amount /* 平台分账 */,
    tb.amount / 2 as market_amount /* 市场经费 */
FROM trans_base tb
INNER JOIN agent_infos ai ON ai.id = tb.owner_id AND ai.deleted_at IS NULL
INNER JOIN member_users mu ON mu.id = ai.user_id AND mu.deleted_at IS NULL
ORDER BY 
    tb.district,
    tb.trans_time DESC;