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
/*
提现管理视图
*/

DROP VIEW IF EXISTS manage_withdraw;

CREATE OR REPLACE VIEW manage_withdraw AS
WITH
    withdraw_detail AS (
        SELECT
            wwr.id /* 提现记录ID */,
            CASE w.owner_type
                WHEN 4 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 3 THEN (
                    SELECT mu.nickname
                    FROM member_users mu
                    WHERE
                        mu.id = w.owner_id
                )
                WHEN 5 THEN (
                    SELECT sar.shop_name
                    FROM shop_auth_records sar
                    WHERE
                        sar.id = w.owner_id
                )
                WHEN 7 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 6 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 /* 提现详情临时表 */;

select * from manage_withdraw;