owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/14提现管理.sql

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

/*
提现角色/渠道是wallet_withdraw_records表中wallet_id关联wallet表获取owner_type字段做为提现角色，
用户名称是wallet_withdraw_records表中wallet_id关联wallet表获取owner_type钱包类型去判断用户角色，
角色有技师角色、用户角色、店铺角色、业务员角色、代理商，
如果owner_type钱包类型为技师角色则通过owner_id继续关联coach_users表判断owner_id与coach_users表id是否相同，
相同则继续通过coach_users表的user_id去member_users表去关联查询出nickname做为用户名称，
如果owner_type钱包类型为用户角色则通过owner_id与member_users表去关联查询出nickname做为用户名称，
如果owner_type钱包类型为店铺角色则通过owner_id与shop_auth_records表shop_id判断是否相同，
相同则获取shop_name做为用户名称，不相同则用户名称空着即可，
如果owner_type钱包类型为业务员角色则通过owner_id与salesmen表id判断是否相同,
相同则获取salesmen表的user_id继续去关联member_users表获取nickname字段做为用户名称，
不相同则用户名空着即可，如果owner_type钱包类型为代理商角色则通过owner_id与agent_infos表id判断是否相同，
相同则获取agent_infos表的user_id继续去关联member_users表获取nickname字段做为用户名称，不相同则用户名称空着即可，
提现金额是wallet_withdraw_records表中amount字段，申请时间是wallet_withdraw_records表中的created_at字段，
提现方式是wallet_withdraw_records表中withdraw_type字段，提现手续费是wallet_withdraw_records表中的fee字段，
审核人是wallet_withdraw_records表中的auditor字段，体现时间是wallet_withdraw_records表中的withdraw_time字段，
体现状态是wallet_withdraw_record表中的audit_state字段，区/县是wallet_withdraw_records表中的area_code字段，
剩余金额是wallet_withdraw_record表通过wallet_id关联wallet表获取available_balance字段做为剩余金额

*/

DROP TABLE IF EXISTS report_withdraw_management;
CREATE TABLE report_withdraw_management AS
WITH withdraw_detail AS (
    SELECT 
        wwr.id /* 提现记录ID */,
        CASE w.owner_type
            WHEN '技师角色' THEN (
                SELECT mu.nickname 
                FROM coach_users cu 
                LEFT JOIN member_users mu ON cu.user_id = mu.id
                WHERE cu.id = w.owner_id
            )
            WHEN '用户角色' THEN (
                SELECT mu.nickname 
                FROM member_users mu 
                WHERE mu.id = w.owner_id
            )
            WHEN '店铺角色' THEN (
                SELECT sar.shop_name 
                FROM shop_auth_records sar 
                WHERE sar.id = w.owner_id
            )
            WHEN '业务员角色' THEN (
                SELECT mu.nickname 
                FROM salesmen s 
                LEFT JOIN member_users mu ON s.user_id = mu.id 
                WHERE s.id = w.owner_id
            )
            WHEN '代理商' THEN (
                SELECT mu.nickname 
                FROM agent_infos ai 
                LEFT JOIN member_users mu ON ai.user_id = mu.id 
                WHERE ai.id = w.owner_id
            )
        END username /* 用户名称 */,
        w.owner_type /* 提现角色/渠道 */,
        wwr.amount /* 提现金额 */,
        wwr.created_at /* 申请时间 */,
        wwr.withdraw_type /* 提现方式 */,
        wwr.fee /* 提现手续费 */,
        wwr.auditor /* 审核人 */,
        wwr.withdraw_time /* 提现时间 */,
        wwr.audit_state /* 提现状态 */,
        wwr.area_code /* 区/县 */,
        w.available_balance /* 剩余金额 */
    FROM 
        wallet_withdraw_records wwr /* 钱包提现记录表 */
    LEFT JOIN 
        wallet w ON w.id = wwr.wallet_id /* 钱包表 */
)
SELECT 
    username as username /* 用户名称 */,
    owner_type as owner_type /* 提现角色/渠道 */,
    amount as amount /* 提现金额 */,
    created_at as created_at /* 申请时间 */,
    withdraw_type as withdraw_type /* 提现方式 */,
    fee as fee /* 提现手续费 */,
    auditor as auditor /* 审核人 */,
    withdraw_time as withdraw_time /* 提现时间 */,
    audit_state as audit_state /* 提现状态 */,
    area_code as area_code /* 区/县 */,
    available_balance as available_balance /* 剩余金额 */
FROM 
    withdraw_detail /* 提现详情临时表 */