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

<?php

namespace app\admin\controller\finance;

use app\model\Member;
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', '');
        $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');
        }

        // $rows = Db::table("data_account_{$month} as da")
        //     ->leftJoin('member as m', 'm.member_id', '=', 'da.join_data_account_member_id')
        //     ->leftJoin('member_cert as mc', 'mc.join_cert_member_id', '=', 'da.join_data_account_member_id')
        //     ->leftJoin('member_info as mi', 'mi.join_info_member_id', '=', 'da.join_data_account_member_id')
        //     ->when(!empty($classify), function ($query) use ($classify) {
        //         $query->where('da.data_account_classify', $classify);
        //     })
        //     ->selectRaw("app_m.member_mobile,app_mc.member_cert_name,app_mi.member_info_nickname,app_m.member_addtimes as member_addtimes,
        //         GROUP_CONCAT(app_da.data_account_income) as data_account_income,GROUP_CONCAT(app_da.data_account_expend) as data_account_expend,
        //         GROUP_CONCAT(app_da.data_account_surplus) as data_account_surplus,GROUP_CONCAT(app_da.data_account_added) as data_account_added
        //         ")
        //     ->groupBy('da.join_data_account_member_id');

        // $total = $rows->count('*');
        // $rows = $rows->orderBy('m.member_addtimes','DESC')
        //     ->forPage($page, $pageSize)
        //     ->get()
        //     ->toArray();
        // $statistics = [];
        // foreach ($rows as &$row){
        //     $row->data_account_income = last(explode(',',$row->data_account_income));
        //     $row->data_account_expend = last(explode(',',$row->data_account_expend));
        //     $row->data_account_surplus = last(explode(',',$row->data_account_surplus));
        //     $row->data_account_added = last(explode(',',$row->data_account_added));
        //     $row->member_addtimes = date('Y-m-d H:i:s',$row->member_addtimes);
        // }

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


        $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);
        })->when(!empty($level), function ($query) use ($level) {
            if ($level == '00'){
                $query->where('join_member_role_id','')->orWhere('join_member_role_id',NULL);
            }else{
                $query->where('join_member_role_id', $level);
            }
            
        })->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);
        });

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

        $rows = $rows->select('member.member_id', 'member.member_mobile', 'member.member_addtimes', 'member.join_member_role_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'));
    }
}