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

<?php

namespace app\admin\controller\finance;

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

class CardController
{
    public function list(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);
        $times = $request->get('times', [date('Y-m-01 00:00:00'), date('Y-m-d 23:59:59')]);
        $times[0] = strtotime($times[0]);
        $times[1] = strtotime(date('Y-m-d 23:59:59', strtotime($times[1])));
        $name = $request->get('card_main_name');
        $searchMainAmount = $request->get('card_main_amount');
        $cardMain = CardMain::when(!empty($name), function ($query) use ($name) {
            $query->where('card_main_name', 'like', '%' . $name . '%');
        })->when(!empty($searchMainAmount), function ($query) use ($searchMainAmount) {
            $query->where('card_main_amount', $searchMainAmount);
        })->get()
            ->toArray();
        $cardIds = array_column($cardMain, 'card_main_id');
        $cardAmount = array_column($cardMain, 'card_main_amount', 'card_main_id');
        $cardName = array_column($cardMain, 'card_main_name', 'card_main_id');

        $cardStatus = [
            'issue' => 'ISSUE',
            'add' => 'ADD',
            'waiting' => 'WAITING',
            'pending' => 'PENDING',
            'used' => 'USED',
            'done' => 'DONE',
            'expired' => 'EXPIRED',
            'paused' => 'PAUSED'
        ];
        $data = [];
        foreach ($cardStatus as $key => $status) {
            $cards = Card::whereIn('join_card_main_id', $cardIds)
                ->when($status == 'ISSUE', function ($query) use ($times) {
                    $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.issue_datetime'))) as SIGNED) >= {$times[0]}")
                        ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.issue_datetime'))) as SIGNED) <= {$times[1]}")
                        ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.issue_datetime' )))),'%Y-%m-%d') AS group_by_time");
                })->when($status == 'ADD', function ($query) use ($times) {
                    $query->whereBetween('card_addtimes', $times)
                        ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(card_addtimes),'%Y-%m-%d') AS group_by_time");
                })->when($status == 'WAITING', function ($query) use ($times) {
                    $query->whereRaw("CAST(UNIX_TIMESTAMP(card_assign_datetime) as SIGNED) >= {$times[0]}")
                        ->whereRaw("CAST(UNIX_TIMESTAMP(card_assign_datetime) as SIGNED) <= {$times[1]}")
                        ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(card_assign_datetime)),'%Y-%m-%d') AS group_by_time");
                })->when($status == 'PENDING', function ($query) use ($times) {
                    $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.buy_datetime'))) as SIGNED) >= {$times[0]}")
                        ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.buy_datetime'))) as SIGNED) <= {$times[1]}")
                        ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.buy_datetime' )))),'%Y-%m-%d') AS group_by_time");
                })->when($status == 'USED', function ($query) use ($times) {
                    $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.used_datetime'))) as SIGNED) >= {$times[0]}")
                        ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.used_datetime'))) as SIGNED) <= {$times[1]}")
                        ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.used_datetime' )))),'%Y-%m-%d') AS group_by_time");
                })->when($status == 'DONE', function ($query) use ($times) {
                    $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.done_time'))) as SIGNED) >= {$times[0]}")
                        ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.done_time'))) as SIGNED) <= {$times[1]}")
                        ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.done_time' )))),'%Y-%m-%d') AS group_by_time");
                })->when($status == 'EXPIRED', function ($query) use ($times) {
                    $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.expired_datetime'))) as SIGNED) >= {$times[0]}")
                        ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.expired_datetime'))) as SIGNED) <= {$times[1]}")
                        ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.expired_datetime' )))),'%Y-%m-%d') AS group_by_time");
                })->when($status == 'PAUSED', function ($query) use ($times) {
                    $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.paused_datetime'))) as SIGNED) >= {$times[0]}")
                        ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.paused_datetime'))) as SIGNED) <= {$times[1]}")
                        ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.paused_datetime' )))),'%Y-%m-%d') AS group_by_time");
                })->selectRaw('count(card_id) as total,join_card_main_id')
                ->groupBy('join_card_main_id', 'group_by_time')
                ->get()
                ->toArray();
            $data[$key] = $cards;
        }
        $start = new \DateTime(date('Y-m-d', $times[0]));
        $end = new \DateTime(date('Y-m-d 23:59:59', $times[1]));

        // 创建一个 DateInterval，设置为一天
        $interval = new \DateInterval('P1D');
        // 创建一个日期范围对象，用于迭代所有日期
        $dateRange = new \DatePeriod($start, $interval, $end);
        $rows = [];
        $statistics = [
            'issue' => ['total' => 0, 'amount' => 0],
            'add' => ['total' => 0, 'amount' => 0],
            'waiting' => ['total' => 0, 'amount' => 0],
            'pending' => ['total' => 0, 'amount' => 0],
            'used' => ['total' => 0, 'amount' => 0],
            'done' => ['total' => 0, 'amount' => 0],
            'expired' => ['total' => 0, 'amount' => 0],
            'paused' => ['total' => 0, 'amount' => 0],
        ];
        foreach ($dateRange as $date) {
            $dayDate = $date->format('Y-m-d');
            foreach ($data as $key => $datum) {
                if (!empty($datum)) {
                    foreach ($datum as $item) {
                        if ($item['group_by_time'] == $dayDate) {
                            $dateKey = $dayDate . '_' . $item['join_card_main_id'];
                            $total = $item['total'];
                            $statistics[$key]['total'] += $total;
                            $mainAmount = $cardAmount[$item['join_card_main_id']] ?? 0;
                            $amount = $total * $mainAmount;
                            $statistics[$key]['amount'] += $amount;
                            if (!empty($rows[$dateKey]) && !empty($rows[$dateKey][$key]) && !empty($rows[$dateKey][$key]['total'])) {
                                $total = $rows[$dateKey][$key]['total'] + $total;
                                $amount = $rows[$dateKey][$key]['amount'] + $amount;
                            }
                            $rows[$dateKey]['key'] = strtotime($dayDate) . '_' . $item['join_card_main_id'];
                            $rows[$dateKey]['date'] = $dayDate;
                            $rows[$dateKey]['card_main_id'] = $item['join_card_main_id'];
                            $rows[$dateKey]['card_main_name'] = $cardName[$item['join_card_main_id']] ?? '';
                            $rows[$dateKey]['card_main_amount'] = $mainAmount;
                            $rows[$dateKey][$key] = [
                                'total' => $total,
                                'amount' => $amount
                            ];
                        }
                    }
                }
            }
        }

        $rows = array_reverse($rows);
        $rowsCount = count($rows);
        $start = $pageSize * ($page - 1);
        $data = array_slice($rows, $start, $pageSize);

        return json_success('success', [
            'total' => $rowsCount,
            'rows' => array_values($data),
            'page' => $page,
            'pageSize' => $pageSize,
            'statistics' => $statistics
        ]);
    }

    public function exportCard(Request $request)
    {
        $times = $request->get('times', [date('Y-m-01 00:00:00'), date('Y-m-d 23:59:59')]);
        $times[0] = strtotime($times[0]);
        $times[1] = strtotime(date('Y-m-d 23:59:59', strtotime($times[1])));
        $name = $request->get('card_main_name');
        $keys = $request->get('keys', []);
        $searchMainAmount = $request->get('card_main_amount');
        $cardMain = CardMain::when(!empty($name), function ($query) use ($name) {
            $query->where('card_main_name', 'like', '%' . $name . '%');
        })->when(!empty($searchMainAmount), function ($query) use ($searchMainAmount) {
            $query->where('card_main_amount', $searchMainAmount);
        })->get()
            ->toArray();
        $cardIds = array_column($cardMain, 'card_main_id');
        $cardAmount = array_column($cardMain, 'card_main_amount', 'card_main_id');
        $cardName = array_column($cardMain, 'card_main_name', 'card_main_id');

        $cardStatus = [
            'issue' => 'ISSUE',
            'add' => 'ADD',
            'waiting' => 'WAITING',
            'pending' => 'PENDING',
            'used' => 'USED',
            'done' => 'DONE',
            'expired' => 'EXPIRED',
            'paused' => 'PAUSED'
        ];
        $data = [];
        foreach ($cardStatus as $key => $status) {
            $cards = Card::when(!empty($cardIds), function ($query) use ($cardIds) {
                $query->whereIn('join_card_main_id', $cardIds);
            })->when($status == 'ISSUE', function ($query) use ($times) {
                $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.issue_datetime'))) as SIGNED) >= {$times[0]}")
                    ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.issue_datetime'))) as SIGNED) <= {$times[1]}")
                    ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.issue_datetime' )))),'%Y-%m-%d') AS group_by_time");
            })->when($status == 'ADD', function ($query) use ($times) {
                $query->whereBetween('card_addtimes', $times)
                    ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(card_addtimes),'%Y-%m-%d') AS group_by_time");
            })->when($status == 'WAITING', function ($query) use ($times) {
                $query->whereRaw("CAST(UNIX_TIMESTAMP(card_assign_datetime) as SIGNED) >= {$times[0]}")
                    ->whereRaw("CAST(UNIX_TIMESTAMP(card_assign_datetime) as SIGNED) <= {$times[1]}")
                    ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(card_assign_datetime)),'%Y-%m-%d') AS group_by_time");
            })->when($status == 'PENDING', function ($query) use ($times) {
                $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.buy_datetime'))) as SIGNED) >= {$times[0]}")
                    ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.buy_datetime'))) as SIGNED) <= {$times[1]}")
                    ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.buy_datetime' )))),'%Y-%m-%d') AS group_by_time");
            })->when($status == 'USED', function ($query) use ($times) {
                $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.used_datetime'))) as SIGNED) >= {$times[0]}")
                    ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.used_datetime'))) as SIGNED) <= {$times[1]}")
                    ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.used_datetime' )))),'%Y-%m-%d') AS group_by_time");
            })->when($status == 'DONE', function ($query) use ($times) {
                $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.done_time'))) as SIGNED) >= {$times[0]}")
                    ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.done_time'))) as SIGNED) <= {$times[1]}")
                    ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.done_time' )))),'%Y-%m-%d') AS group_by_time");
            })->when($status == 'EXPIRED', function ($query) use ($times) {
                $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.expired_datetime'))) as SIGNED) >= {$times[0]}")
                    ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.expired_datetime'))) as SIGNED) <= {$times[1]}")
                    ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.expired_datetime' )))),'%Y-%m-%d') AS group_by_time");
            })->when($status == 'PAUSED', function ($query) use ($times) {
                $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.paused_datetime'))) as SIGNED) >= {$times[0]}")
                    ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(card_extend_json,'$.paused_datetime'))) as SIGNED) <= {$times[1]}")
                    ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP( JSON_UNQUOTE( JSON_EXTRACT( card_extend_json, '$.paused_datetime' )))),'%Y-%m-%d') AS group_by_time");
            })->selectRaw('count(card_id) as total,join_card_main_id')
                ->groupBy('join_card_main_id', 'group_by_time')
                ->get()
                ->toArray();
            $data[$key] = $cards;
        }
        $start = new \DateTime(date('Y-m-d', $times[0]));
        $end = new \DateTime(date('Y-m-d 23:59:59', $times[1]));

        // 创建一个 DateInterval，设置为一天
        $interval = new \DateInterval('P1D');
        // 创建一个日期范围对象，用于迭代所有日期
        $dateRange = new \DatePeriod($start, $interval, $end);
        $rows = [];
        $statistics = [
            'issue' => ['total' => 0, 'amount' => 0],
            'add' => ['total' => 0, 'amount' => 0],
            'waiting' => ['total' => 0, 'amount' => 0],
            'pending' => ['total' => 0, 'amount' => 0],
            'used' => ['total' => 0, 'amount' => 0],
            'done' => ['total' => 0, 'amount' => 0],
            'expired' => ['total' => 0, 'amount' => 0],
            'paused' => ['total' => 0, 'amount' => 0],
        ];
        foreach ($dateRange as $date) {
            $dayDate = $date->format('Y-m-d');
            foreach ($data as $key => $datum) {
                if (!empty($datum)) {
                    foreach ($datum as $item) {
                        if ($item['group_by_time'] == $dayDate) {
                            // 有选择，过滤
                            if (!empty($keys) && !in_array(strtotime($dayDate) . '_' . $item['join_card_main_id'], $keys)) {
                                continue;
                            }
                            $dateKey = $dayDate . '_' . $item['join_card_main_id'];
                            $total = $item['total'];
                            $statistics[$key]['total'] += $total;
                            $mainAmount = $cardAmount[$item['join_card_main_id']] ?? 0;
                            $amount = $total * $mainAmount;
                            $statistics[$key]['amount'] += $amount;
                            if (!empty($rows[$dateKey]) && !empty($rows[$dateKey][$key]) && !empty($rows[$dateKey][$key]['total'])) {
                                $total = $rows[$dateKey][$key]['total'] + $total;
                                $amount = $rows[$dateKey][$key]['amount'] + $amount;
                            }
                            $rows[$dateKey]['date'] = $dayDate;
                            $rows[$dateKey]['card_main_id'] = $item['join_card_main_id'];
                            $rows[$dateKey]['card_main_name'] = $cardName[$item['join_card_main_id']] ?? '';
                            $rows[$dateKey]['card_main_amount'] = $mainAmount;
                            $rows[$dateKey][$key] = [
                                'total' => $total,
                                'amount' => $amount
                            ];
                        }
                    }
                }
            }
        }

        $rows = array_reverse($rows);
        $exportData = [];
        foreach ($rows as $row) {
            $exportData[] = [
                'date' => $row['date'],
                'card_main_name' => $row['card_main_name'],
                'card_main_amount' => $row['card_main_amount'],
                'issue' => isset($row['issue']) ? $row['issue']['total'] . '张 (￥' . sprintf('%.2f', $row['issue']['amount']) . ')' : '--',
                'add' => isset($row['add']) ? $row['add']['total'] . '张 (￥' . sprintf('%.2f', $row['add']['amount']) . ')' : '--',
                'waiting' => isset($row['waiting']) ? $row['waiting']['total'] . '张 (￥' . sprintf('%.2f', $row['waiting']['amount']) . ')' : '--',
                'pending' => isset($row['pending']) ? $row['pending']['total'] . '张 (￥' . sprintf('%.2f', $row['pending']['amount']) . ')' : '--',
                'used' => isset($row['used']) ? $row['used']['total'] . '张 (￥' . sprintf('%.2f', $row['used']['amount']) . ')' : '--',
                'done' => isset($row['done']) ? $row['done']['total'] . '张 (￥' . sprintf('%.2f', $row['done']['amount']) . ')' : '--',
                'expired' => isset($row['expired']) ? $row['expired']['total'] . '张 (￥' . sprintf('%.2f', $row['expired']['amount']) . ')' : '--',
                'paused' => isset($row['paused']) ? $row['paused']['total'] . '张 (￥' . sprintf('%.2f', $row['paused']['amount']) . ')' : '--'
            ];
        }

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

    public function listOld(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);
        $classify = $request->get('card_main_classify');
        $categoryId = $request->get('join_card_main_category_id');
        $name = $request->get('card_main_name');
        $addtimes = $request->get('card_main_addtimes');

        $cardMain = CardMain::when(!empty($classify), function ($query) use ($classify) {
            $query->where('card_main_classify', $classify);
        })->when(!empty($categoryId), function ($query) use ($categoryId) {
            $query->where('join_card_main_category_id', $categoryId);
        })->when(!empty($name), function ($query) use ($name) {
            $query->where('card_main_name', 'like', '%' . $name . '%');
        })->when(!empty($addtimes), function ($query) use ($addtimes) {
            $addtimes[0] = strtotime($addtimes[0]);
            $addtimes[1] = strtotime($addtimes[1]);
            $query->whereBetween('card_main_addtimes', $addtimes);
        });
        $total = $cardMain->count();
        $rows = $cardMain->select('card_main_id', 'card_main_amount', 'card_main_name')
            ->orderBy('card_main_addtimes', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();
        $statistics = [
            'total' => ['total' => 0, 'amount' => 0],
            'is_issue' => ['total' => 0, 'amount' => 0],
            'init' => ['total' => 0, 'amount' => 0],
            'waiting' => ['total' => 0, 'amount' => 0],
            'pending' => ['total' => 0, 'amount' => 0],
            'used' => ['total' => 0, 'amount' => 0],
            'done' => ['total' => 0, 'amount' => 0],
            'paused' => ['total' => 0, 'amount' => 0]
        ];
        foreach ($rows as &$row) {
            // 发行统计
            $isIssueTotal = Card::where('is_issue', 'Y')->where('join_card_main_id', $row['card_main_id'])->count();
            $row['is_issue']['total'] = $isIssueTotal;
            $row['is_issue']['amount'] = sprintf('%.2f', $isIssueTotal * $row['card_main_amount']);
            $statistics['is_issue']['total'] += $isIssueTotal;
            $statistics['is_issue']['amount'] = sprintf('%.2f', $isIssueTotal * $row['card_main_amount'] + $statistics['is_issue']['amount']);
            // 待分配统计
            $initTotal = Card::where('card_status', 'INIT')->where('join_card_main_id', $row['card_main_id'])->count();
            $row['init']['total'] = $initTotal;
            $row['init']['amount'] = sprintf('%.2f', $initTotal * $row['card_main_amount']);
            $statistics['init']['total'] += $initTotal;
            $statistics['init']['amount'] = sprintf('%.2f', $initTotal * $row['card_main_amount'] + $statistics['init']['amount']);
            // 已分配，待售统计
            $waitingTotal = Card::where('card_status', 'WAITING')->where('join_card_main_id', $row['card_main_id'])->count();
            $row['waiting']['total'] = $waitingTotal;
            $row['waiting']['amount'] = sprintf('%.2f', $waitingTotal * $row['card_main_amount']);
            $statistics['waiting']['total'] += $waitingTotal;
            $statistics['waiting']['amount'] = sprintf('%.2f', $waitingTotal * $row['card_main_amount'] + $statistics['waiting']['amount']);
            // 已售，待激活统计
            $pendingTotal = Card::where('card_status', 'PENDING')->where('join_card_main_id', $row['card_main_id'])->count();
            $row['pending']['total'] = $pendingTotal;
            $row['pending']['amount'] = sprintf('%.2f', $pendingTotal * $row['card_main_amount']);
            $statistics['pending']['total'] += $pendingTotal;
            $statistics['pending']['amount'] = sprintf('%.2f', $pendingTotal * $row['card_main_amount'] + $statistics['pending']['amount']);
            // 已激活统计
            $usedTotal = Card::where('card_status', 'USED')->where('join_card_main_id', $row['card_main_id'])->count();
            $row['used']['total'] = $usedTotal;
            $row['used']['amount'] = sprintf('%.2f', $usedTotal * $row['card_main_amount']);
            $statistics['used']['total'] += $usedTotal;
            $statistics['used']['amount'] = sprintf('%.2f', $usedTotal * $row['card_main_amount'] + $statistics['used']['amount']);
            // 使用完成统计
            $doneTotal = Card::where('card_status', 'DONE')->where('join_card_main_id', $row['card_main_id'])->count();
            $row['done']['total'] = $doneTotal;
            $row['done']['amount'] = sprintf('%.2f', $doneTotal * $row['card_main_amount']);
            $statistics['done']['total'] += $doneTotal;
            $statistics['done']['amount'] = sprintf('%.2f', $doneTotal * $row['card_main_amount'] + $statistics['done']['amount']);
            // 冻结统计
            $pausedTotal = Card::where('card_status', 'PAUSED')->where('join_card_main_id', $row['card_main_id'])->count();
            $row['paused']['total'] = $pausedTotal;
            $row['paused']['amount'] = sprintf('%.2f', $pausedTotal * $row['card_main_amount']);
            $statistics['paused']['total'] += $pausedTotal;
            $statistics['paused']['amount'] = sprintf('%.2f', $pausedTotal * $row['card_main_amount'] + $statistics['paused']['total']);
        }
        $statistics['total']['total'] = $statistics['init']['total'] + $statistics['waiting']['total']
            + $statistics['pending']['total'] + $statistics['used']['total'] + $statistics['done']['total']
            + $statistics['paused']['total'];
        $statistics['total']['amount'] = sprintf('%.2f', $statistics['init']['amount']
            + $statistics['waiting']['amount'] + $statistics['pending']['amount'] + $statistics['used']['amount']
            + $statistics['done']['amount'] + $statistics['paused']['amount']);

        return json_success('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);
    }
}