wanyue_app/app/admin/controller/finance/MemberAccountController.php

<?php

namespace app\admin\controller\finance;

use app\model\Member;
use app\model\MemberRole;
use support\Db;
use support\Request;

class MemberAccountController
{

    public function list(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);
        // $classify = $request->get('classify', 'CASH');
        $days = $request->get('member_addtimes', []);
        $level = $request->get('level', '');
        $memberId = $request->get('member_id');
        $accountType = $request->get('account_type', 'CASH');
        $premisesId = intval($request->get('premises_id', ''));
        if (!empty($days)) {
            $days[0] = strtotime($days[0]);
            $days[1] = strtotime($days[1]);

            $month = date('Ym', $days[0]);
            $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
        } else {
            $month = date('Ym');
        }

        // 兼容老等级搜索
        $levelIds = [];
        if (!empty($level)) {
            $levelName = MemberRole::where('member_role_id', $level)->value('member_role_name');
            if (!empty($levelName)) {
                $levelIds = MemberRole::where('member_role_name', $levelName)->pluck('member_role_id')->toArray();
            }
        }
        $rows = Member::with([
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname');
            },
            'role' => function ($query) {
                $query->select('member_role_id', 'member_role_name');
            }
        ])->join('member_account as ma', function ($join) use ($accountType) {
            $join->on('member.member_id', '=', 'ma.join_account_member_id')->where('ma.member_account_classify', '=', $accountType);
        })->leftJoin('member_role', 'member_role.member_role_id', 'member.join_member_role_id')
            ->when(!empty($level), function ($query) use ($level, $levelIds) {
                if ($level == '00') {
                    $query->where('join_member_role_id', '')->orWhere('join_member_role_id', NULL);
                } else if ($level == 'VIP') {
                    $query->where('member.member_is_vip', 'Y');
                } else if (!empty($levelIds)) {
                    $query->whereIn('member_role.member_role_id', $levelIds);
                } else {
                    $query->where('join_member_role_id', $level);
                }

            })->when(!empty($memberId), function ($query) use ($memberId) {
                $query->where('member.member_id', $memberId);
            })->when(!empty($days), function ($query) use ($days) {
                $query->whereBetween('member.member_addtimes', $days);
            })->when(!empty($premisesId), function ($query) use ($premisesId) {
                $query->whereJsonContains('member.member_extend_json->belong->premises', $premisesId);
            })->where('member.member_mobile', '<>', '0000');

        $memberTotalModel = clone $rows;
        $statistics['memberTotal'] = $memberTotalModel->distinct('ma.join_account_member_id')->count();

        $total = $rows->count('member_id');
        $statistics['added'] = $rows->sum('member_account_added');
        $statistics['surplus'] = round($rows->sum('ma.member_account_surplus')+$statistics['added']);
        $statistics['income'] = $rows->sum('ma.member_account_income');
        $statistics['expend'] = $rows->sum('member_account_expend');

//        $statistics['memberTotal'] = Member::where('member_mobile', '<>', '0000')
//            ->when(!empty($level), function ($query) use ($level, $levelIds) {
//                if ($level == '00') {
//                    $query->where('join_member_role_id', '')->orWhere('join_member_role_id', NULL);
//                } else if ($level == 'VIP') {
//                    $query->where('member_is_vip', 'Y');
//                } else if (!empty($levelIds)) {
//                    $query->whereIn('member_role_id', $levelIds);
//                } else {
//                    $query->where('join_member_role_id', $level);
//                }
//
//            })->when(!empty($memberId), function ($query) use ($memberId) {
//                $query->where('member_id', $memberId);
//            })->when(!empty($days), function ($query) use ($days) {
//                $query->whereBetween('member_addtimes', $days);
//            })->when(!empty($premisesId), function ($query) use ($premisesId) {
//                $query->whereJsonContains('member_extend_json->belong->premises', $premisesId);
//            })->count();

        $rows = $rows->select('member.member_id', 'member.member_mobile', 'member.member_addtimes', 'member.join_member_role_id',
            'ma.member_account_id as member_account_id','ma.member_account_income as member_account_income', 'ma.member_account_expend as member_account_expend', 'ma.member_account_surplus as member_account_surplus', 'ma.member_account_added as member_account_added'
        )
            ->orderBy('member.member_addtimes', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        return json_success('', compact('rows', 'page', 'pageSize', 'total', 'statistics'));
    }

    /**
     * @Desc 导出
     * @Author Gorden
     * @Date 2024/11/19 8:56
     *
     * @param Request $request
     * @return \support\Response
     */
    public function exportMemberAccount(Request $request)
    {
        $days = $request->get('member_addtimes', []);
        $level = $request->get('level', '');
        $memberId = $request->get('member_id');
        $accountType = $request->get('account_type', 'CASH');
        $premisesId = intval($request->get('premises_id', ''));
        $memberAccountIds = $request->get('member_account_ids');
        if (!empty($days)) {
            $days[0] = strtotime($days[0]);
            $days[1] = strtotime($days[1]);

            $month = date('Ym', $days[0]);
            $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
        } else {
            $month = date('Ym');
        }

        // 兼容老等级搜索
        $levelIds = [];
        if (!empty($level)) {
            $levelName = MemberRole::where('member_role_id', $level)->value('member_role_name');
            if (!empty($levelName)) {
                $levelIds = MemberRole::where('member_role_name', $levelName)->pluck('member_role_id')->toArray();
            }
        }
        $rows = Member::with([
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname');
            },
            'role' => function ($query) {
                $query->select('member_role_id', 'member_role_name');
            }
        ])->join('member_account as ma', function ($join) use ($accountType) {
            $join->on('member.member_id', '=', 'ma.join_account_member_id')->where('ma.member_account_classify', '=', $accountType);
        })->leftJoin('member_role', 'member_role.member_role_id', 'member.join_member_role_id')
            ->when(!empty($level), function ($query) use ($level, $levelIds) {
                if ($level == '00') {
                    $query->where('join_member_role_id', '')->orWhere('join_member_role_id', NULL);
                } else if ($level == 'VIP') {
                    $query->where('member.member_is_vip', 'Y');
                } else if (!empty($levelIds)) {
                    $query->whereIn('member_role.member_role_id', $levelIds);
                } else {
                    $query->where('join_member_role_id', $level);
                }

            })->when(!empty($memberId), function ($query) use ($memberId) {
                $query->where('member.member_id', $memberId);
            })->when(!empty($days), function ($query) use ($days) {
                $query->whereBetween('member.member_addtimes', $days);
            })->when(!empty($premisesId), function ($query) use ($premisesId) {
                $query->whereJsonContains('member.member_extend_json->belong->premises', $premisesId);
            })->when(!empty($memberAccountIds),function($query) use ($memberAccountIds){
                $query->whereIn('member_account_id',$memberAccountIds);
            })->where('member.member_mobile', '<>', '0000')
            ->select('member.member_id', 'member.member_mobile', 'member.member_addtimes', 'member.join_member_role_id',
                'ma.member_account_id as member_account_id','ma.member_account_income as member_account_income', 'ma.member_account_expend as member_account_expend', 'ma.member_account_surplus as member_account_surplus', 'ma.member_account_added as member_account_added'
            )
            ->orderBy('member.member_addtimes', 'DESC')
            ->get()
            ->toArray();

        $data = [];
        foreach ($rows as $row) {
            $data[] = [
                'nickname' => $row['info']['member_info_nickname'] ?? '--',
                'mobile' => $row['member_mobile'] ?? '--',
                'role_name' => $row['role']['member_role_name'] ?? '--',
                'balance' => sprintf('%.2f', $row['member_account_surplus'] + $row['member_account_added']),
                'income' => $row['member_account_income'],
                'expend' => $row['member_account_expend'],
                'added' => $row['member_account_added'],
                'addtimes' => $row['member_addtimes']
            ];
        }

        return json_success('success', $data);
    }
}