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

<?php

namespace app\admin\controller\finance;

use app\admin\service\sys_manage\DeptService;
use app\common\Util;
use support\Db;
use support\Request;
use function _PHPStan_cc8d35ffb\RingCentral\Psr7\str;

class PremisesMemberController
{

    private $accountType = ['CASH', 'POINTS', 'CARD'];

    public function list(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);
        $accountType = $request->get('account_type', $this->accountType);
        $premises = $request->get('premises', DeptService::getPremisesList());
        $premisesKeyValue = [];
        foreach($premises as $item){
            $premisesKeyValue[$item['key']] = $item['label'];
        }
        $months = $request->get('month', []);
        if (empty($months)) {
            $months = [date('Y-m')];
//            $months = ['2024-06'];
        }else{
            foreach($months as $key => $month){
                $months[$key] = date('Y-m',strtotime($month));
            }
        }
        $data = [];
        $statistics = ['surplus'=>0,'income'=>0,'added'=>0,'expend'=>0];
        foreach ($months as $month) {
            $day = $month . '-01';
            $monthStr = date('m', strtotime($day));
            $firstDay = date('Y-m-t', strtotime('last month', strtotime($day)));
            $firstMonth = date('Ym', strtotime($firstDay));
            $lastDay = date('Y-m-t', strtotime($month));
            if (strtotime("-1 days") < strtotime($lastDay)) {
                $lastDay = date('Y-m-d', strtotime("-1 days"));
            }
            $lastMonth = date('Ym', strtotime($lastDay));
            foreach ($premises as $premise) {
                $premiseKey = intval($premise['key']);
                foreach ($accountType as $item) {
                    $firstData = [];
                    $firstTable = "data_account_{$firstMonth}";
                    if (Util::schema()->hasTable($firstTable)) {
                        $firstData = Db::table("{$firstTable} as da")
                            ->leftJoin('member', 'member.member_id', '=', 'da.join_data_account_member_id')
                            ->whereJsonContains('member.member_extend_json->belong->premises', $premiseKey)
                            ->where('data_account_days', $firstDay)
                            ->where('data_account_classify', $item)
                            ->selectRaw("SUM(data_account_income) as data_account_income,SUM(data_account_expend) as data_account_expend,SUM(data_account_surplus) as data_account_surplus,SUM(data_account_added) as data_account_added")
                            ->groupBy('data_account_days')
                            ->first();
                    }

                    $lastData = Db::table("data_account_{$lastMonth} as da")
                        ->leftJoin('member', 'member.member_id', '=', 'da.join_data_account_member_id')
                        ->whereJsonContains('member.member_extend_json->belong->premises', $premiseKey)
                        ->where('data_account_days', $lastDay)
                        ->where('data_account_classify', $item)
                        ->selectRaw('SUM(data_account_income) as data_account_income,SUM(data_account_expend) as data_account_expend,SUM(data_account_surplus) as data_account_surplus,SUM(data_account_added) as data_account_added')
                        ->groupBy('data_account_days')
                        ->first();

                    $record = [
                        'month' => $monthStr,
                        'premise' => isset($premisesKeyValue[$premiseKey]) ? $premisesKeyValue[$premiseKey] : $premiseKey,
                        'account_type' => $item,
                        'data_account_surplus' => $lastData->data_account_surplus ?? '0.00',
                        'data_account_income' => !empty($firstData) ? number_format($lastData->data_account_income - $firstData->data_account_income,2,'.','') : ($lastData->data_account_income ?? '0.00'),
                        'data_account_expend' => !empty($firstData) ? number_format($lastData->data_account_expend - $firstData->data_account_expend,2,'.','') : ($lastData->data_account_expend ?? '0.00'),
                        'data_account_added' => !empty($firstData) ? number_format($lastData->data_account_added - $firstData->data_account_added,2,'.','') : ($lastData->data_account_added ?? '0.00'),
                    ];
                    $statistics['surplus'] = number_format($statistics['surplus']+$record['data_account_surplus'],2,'.','');
                    $statistics['income'] = number_format($statistics['income']+$record['data_account_income'],2,'.','');
                    $statistics['expend'] = number_format($statistics['expend']+$record['data_account_expend'],2,'.','');
                    $statistics['added'] = number_format($statistics['added']+$record['data_account_added'],2,'.','');

                    $data[] = $record;
                }
            }
        }

        return json_success('', ['rows'=>$data,'statistics'=>$statistics]);
    }
}