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 = ['2024-06-01', '2024-06-30'];
        if (!empty($days)) {
            $days[0] = strtotime($days[0]);
            $days[1] = strtotime($days[1]);
            if (date('m', $days[0]) != date('m', $days[1])) {
                return json_fail('暂不支持跨月查询');
            }

            $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 ($classify){
           $join->on('member.member_id','=','ma.join_account_member_id')->where('ma.member_account_classify','=',$classify);
       });

       $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',
           'ma.member_account_income as member_account_income','ma.member_account_expend as member_account_expend'
       )
       ->orderBy('member.member_addtimes', 'DESC')
           ->forPage($page, $pageSize)
           ->get()
           ->toArray();

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