, * granularity:string, * new:list, * expired:list, * summary:array{available:int, used:int, new:int, expired:int} * } */ public function get(Carbon $start, Carbon $end): array { [$start, $end] = $this->normalizeRange($start, $end); $granularity = $start->diffInDays($end) > self::MAX_DAILY_DAYS ? 'month' : 'day'; $labels = $this->labels($start, $end, $granularity); $format = $granularity === 'month' ? '%Y-%m' : '%Y-%m-%d'; $newRows = DB::table('gift_cards') ->selectRaw("DATE_FORMAT(created_at, '".$format."') as period, COUNT(*) as total") ->whereBetween('created_at', [$start, $end]) ->groupBy('period') ->pluck('total', 'period'); $expiredRows = DB::table('gift_cards') ->selectRaw("DATE_FORMAT(expirationdate, '".$format."') as period, COUNT(*) as total") ->whereNotNull('expirationdate') ->whereBetween('expirationdate', [$start, $end]) ->groupBy('period') ->pluck('total', 'period'); $new = []; $expired = []; foreach ($labels as $label) { $new[] = (int) ($newRows[$label] ?? 0); $expired[] = (int) ($expiredRows[$label] ?? 0); } return [ 'labels' => $labels, 'granularity' => $granularity, 'new' => $new, 'expired' => $expired, 'summary' => [ 'available' => $this->availableCount(), 'used' => $this->usedCount(), 'new' => array_sum($new), 'expired' => array_sum($expired), ], ]; } /** * 校正区间：保证 start <= end，并且区间长度不超过 MAX_RANGE_DAYS。 * * @return array{0:Carbon, 1:Carbon} */ protected function normalizeRange(Carbon $start, Carbon $end): array { $start = $start->copy()->startOfDay(); $end = $end->copy()->endOfDay(); if ($start->greaterThan($end)) { [$start, $end] = [$end->copy()->startOfDay(), $start->copy()->endOfDay()]; } if ($start->diffInDays($end) > self::MAX_RANGE_DAYS) { $start = $end->copy()->subDays(self::MAX_RANGE_DAYS)->startOfDay(); } return [$start, $end]; } /** * 生成完整的横轴标签（没有数据的区间补 0，避免图表出现断点）。 * * @return list */ protected function labels(Carbon $start, Carbon $end, string $granularity): array { $period = $granularity === 'month' ? CarbonPeriod::create($start->copy()->startOfMonth(), '1 month', $end->copy()->startOfMonth()) : CarbonPeriod::create($start->copy(), '1 day', $end->copy()); $format = $granularity === 'month' ? 'Y-m' : 'Y-m-d'; $labels = []; foreach ($period as $date) { $labels[] = $date->format($format); } return $labels; } /** * 当前可用（未使用且未过期）的礼品卡数量。 */ protected function availableCount(): int { return (int) DB::table('gift_cards') ->where('giftcard_status', 1) ->where(function ($query) { $query->whereNull('expirationdate') ->orWhere('expirationdate', '>=', now()); }) ->count(); } /** * 已经用完（状态为已使用）的礼品卡数量。 */ protected function usedCount(): int { return (int) DB::table('gift_cards') ->where('giftcard_status', 2) ->count(); } }