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 VIEW IF EXISTS v_withraw_management;
CREATE OR REPLACE VIEW v_withraw_management AS
SELECT
    wwr.id as id, /* 提现记录ID */
    wwr.wallet_id as wallet_id, /* 钱包ID */
    wwr.external_no as external_no, /* 外部订单号 */
    wwr.withdraw_type as withdraw_type, /* 提现方式 */
    wwr.withdraw_account as withdraw_account, /* 提现账户 */
    wwr.withdraw_account_name as withdraw_account_name, /* 提现账户名称 */
    wwr.amount as amount, /* 提现金额 */
    wwr.fee as fee, /* 提现手续费 */
    wwr.withdraw_time as withdraw_time, /* 提现时间 */
    wwr.area_code as area_code, /* 区/县 */
    wwr.remark as remark, /* 备注 */
    wwr.auditor as auditor, /* 审核人 */
    wwr.audit_state as audit_state, /* 审核状态 */
    wwr.audit_time as audit_time, /* 审核时间 */
    wwr.audit_remark as audit_remark, /* 审核备注 */
    wwr.trans_no as trans_no, /* 交易流水号 */
    wwr.state as state, /* 提现状态 */
    wwr.created_at as created_at, /* 提现申请时间 */
    CASE
        WHEN w.owner_type = 'App\Models\MemberUser' THEN '用户'
        WHEN w.owner_type = 'App\Models\CoachInfoRecord' THEN '技师'
        WHEN w.owner_type = 'App\Models\AgentInfoRecord' THEN '代理商'
        WHEN w.owner_type = 'App\Models\ShopAuthRecord' THEN '店铺'
        ELSE '未知角色'
    END as role, /* 提现用户角色 */
    COALESCE(
        CASE
            WHEN w.owner_type = 'App\Models\MemberUser' THEN mu.nickname
            WHEN w.owner_type = 'App\Models\CoachInfoRecord' THEN ci.nickname
            WHEN w.owner_type = 'App\Models\AgentInfoRecord' THEN ai.contact_name
            WHEN w.owner_type = 'App\Models\ShopAuthRecord' THEN sar.shop_name
        END, '未知昵称') as nickname, /* 用户/技师/代理商/店铺昵称 */
    COALESCE(
        CASE
            WHEN w.owner_type = 'App\Models\MemberUser' THEN mu.mobile
            WHEN w.owner_type = 'App\Models\CoachInfoRecord' THEN ci.mobile
            WHEN w.owner_type = 'App\Models\AgentInfoRecord' THEN ai.contact_mobile
            WHEN w.owner_type = 'App\Models\ShopAuthRecord' THEN sar.contact_phone
        END, '未知联系方式') as contact, /* 用户/技师/代理商/店铺联系方式 */
    CASE
        WHEN w.owner_type = 'App\Models\MemberUser' THEN mu.avatar
        WHEN w.owner_type = 'App\Models\CoachInfoRecord' THEN ci.avatar
        ELSE NULL
    END as avatar, /* 用户/技师头像 */
    w.total_balance as total_balance, /* 总余额 */
    w.available_balance as available_balance, /* 可用余额 */
    w.frozen_amount as frozen_amount, /* 冻结余额 */
    w.total_income as total_income, /* 累计收入 */
    w.total_expense as total_expense, /* 累计支出 */
    w.owner_id as owner_id, /* 用户ID */
    COALESCE((
        SELECT SUM(wwr_sub.amount)
        FROM wallet_withdraw_records wwr_sub
        WHERE wwr_sub.wallet_id = wwr.wallet_id AND wwr_sub.state = 2
    ), 0) as total_successful_withdrawal, /* 累计提现成功金额 */
    (SELECT COUNT(*) FROM wallet_withdraw_records wwr_sub WHERE wwr_sub.wallet_id = wwr.wallet_id) as total_withdrawals, /* 提现次数 */
    (SELECT COUNT(*) FROM wallet_withdraw_records wwr_sub WHERE wwr_sub.wallet_id = wwr.wallet_id AND wwr_sub.state = 2) as successful_withdrawals, /* 提现成功次数 */
    (SELECT COUNT(*) FROM wallet_withdraw_records wwr_sub WHERE wwr_sub.wallet_id = wwr.wallet_id AND wwr_sub.state = 3) as failed_withdrawals, /* 提现失败次数 */
    (SELECT COUNT(*) FROM wallet_withdraw_records wwr_sub WHERE wwr_sub.wallet_id = wwr.wallet_id AND wwr_sub.state = 1) as processing_withdrawals /* 正在提现次数 */
FROM wallet_withdraw_records wwr /* 钱包提现记录表 */
LEFT JOIN wallet w ON w.id = wwr.wallet_id /* 钱包表 */
LEFT JOIN member_users mu ON mu.id = w.owner_id AND w.owner_type = 'App\Models\MemberUser' /* 用户表 */
LEFT JOIN coach_info_records ci ON ci.id = w.owner_id AND w.owner_type = 'App\Models\CoachInfoRecord' /* 技师表 */
LEFT JOIN agent_info_records ai ON ai.id = w.owner_id AND w.owner_type = 'App\Models\AgentInfoRecord' /* 代理商表 */
LEFT JOIN shop_auth_records sar ON sar.id = w.owner_id AND w.owner_type = 'App\Models\ShopAuthRecord' /* 店铺表 */