nshop/packages/Longyi/Gift/src/Services/GiftCardStatisticsService.php

<?php

namespace Longyi\Gift\Services;

use Carbon\Carbon;
use Carbon\CarbonPeriod;
use Illuminate\Support\Facades\DB;

/**
 * 礼品卡统计。
 *
 * - 新增礼品卡：`gift_cards.created_at` 落在统计区间内的数量。
 * - 过期礼品卡：`gift_cards.expirationdate` 落在统计区间内的数量（不区分是否已使用）。
 */
class GiftCardStatisticsService
{
    /**
     * 区间超过这个天数就按月聚合，否则按天。
     */
    public const MAX_DAILY_DAYS = 92;

    /**
     * 单次统计允许的最大区间（天），防止区间过大把图表撑爆。
     */
    public const MAX_RANGE_DAYS = 730;

    /**
     * @return array{
     *     labels:list<string>,
     *     granularity:string,
     *     new:list<int>,
     *     expired:list<int>,
     *     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<string>
     */
    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();
    }
}