123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463 |
- <?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');
- $cardMain = CardMain::when(!empty($name), function ($query) use ($name) {
- $query->where('card_main_name', 'like', '%' . $name . '%');
- })->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) {
- $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', []);
- $cardMain = CardMain::when(!empty($name), function ($query) use ($name) {
- $query->where('card_main_name', 'like', '%' . $name . '%');
- })->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);
- }
- }
|