ultimatepos/Modules/ManagementTools/Services/ExecutiveCockpit/TransactionMetricsQuery.php

<?php

namespace Modules\ManagementTools\Services\ExecutiveCockpit;

use App\Services\Holding\AssignmentQueryScopeService;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class TransactionMetricsQuery
{
    public function __construct(protected AssignmentQueryScopeService $assignmentScope) {}

    public function sumFinalSells(
        int $businessId,
        Carbon $startDate,
        Carbon $endDate,
        ?array $locationIds = null
    ): float {
        if (! Schema::hasTable('transactions')) {
            return 0.0;
        }

        $query = DB::table('transactions')
            ->where('business_id', $businessId)
            ->where('type', 'sell')
            ->where('status', 'final')
            ->where('transaction_date', '>=', $startDate->copy()->startOfDay())
            ->where('transaction_date', '<=', $endDate->copy()->endOfDay());

        $this->applyLocationFilter($query, $locationIds);

        $this->assignmentScope->applyToQuery($query, null, [
            'holding_entity_id' => 'transactions.holding_entity_id',
            'location_id' => 'transactions.location_id',
        ]);

        return (float) $query->sum('final_total');
    }

    /**
     * @param  array<int, int>  $entityLocationMap
     * @return array<int, float>
     */
    public function entitySalesBatch(
        int $businessId,
        array $entityIds,
        array $entityLocationMap,
        Carbon $startDate,
        Carbon $endDate
    ): array {
        if (empty($entityIds) || ! Schema::hasTable('transactions')) {
            return [];
        }

        $results = array_fill_keys($entityIds, 0.0);

        if (Schema::hasColumn('transactions', 'holding_entity_id')) {
            $query = DB::table('transactions')
                ->select('holding_entity_id', DB::raw('SUM(final_total) as total'))
                ->where('business_id', $businessId)
                ->where('type', 'sell')
                ->where('status', 'final')
                ->whereIn('holding_entity_id', $entityIds)
                ->where('transaction_date', '>=', $startDate->copy()->startOfDay())
                ->where('transaction_date', '<=', $endDate->copy()->endOfDay())
                ->groupBy('holding_entity_id');

            $this->assignmentScope->applyToQuery($query, null, [
                'holding_entity_id' => 'transactions.holding_entity_id',
                'location_id' => 'transactions.location_id',
            ]);

            foreach ($query->get() as $row) {
                $results[(int) $row->holding_entity_id] = (float) $row->total;
            }

            return $results;
        }

        foreach ($entityIds as $entityId) {
            $locationId = (int) ($entityLocationMap[$entityId] ?? 0);
            if ($locationId <= 0) {
                continue;
            }

            $query = DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'sell')
                ->where('status', 'final')
                ->where('location_id', $locationId)
                ->where('transaction_date', '>=', $startDate->copy()->startOfDay())
                ->where('transaction_date', '<=', $endDate->copy()->endOfDay());

            $this->assignmentScope->applyToQuery($query, null, [
                'location_id' => 'transactions.location_id',
            ]);

            $results[$entityId] = (float) $query->sum('final_total');
        }

        return $results;
    }

    /**
     * @return array<int, float>
     */
    public function monthlySalesSeries(int $businessId, Carbon $endDate, int $months = 6): array
    {
        if (! Schema::hasTable('transactions')) {
            return array_fill(0, $months, 0.0);
        }

        $start = $endDate->copy()->startOfMonth()->subMonths($months - 1)->startOfDay();
        $end = $endDate->copy()->endOfMonth()->endOfDay();

        $query = DB::table('transactions')
            ->selectRaw('DATE_FORMAT(transaction_date, "%Y-%m") as month_key, SUM(final_total) as total')
            ->where('business_id', $businessId)
            ->where('type', 'sell')
            ->where('status', 'final')
            ->where('transaction_date', '>=', $start)
            ->where('transaction_date', '<=', $end)
            ->groupBy('month_key');

        $this->assignmentScope->applyToQuery($query, null, [
            'holding_entity_id' => 'transactions.holding_entity_id',
            'location_id' => 'transactions.location_id',
        ]);

        $byMonth = $query->pluck('total', 'month_key')->all();

        $series = [];
        for ($i = $months - 1; $i >= 0; $i--) {
            $key = $endDate->copy()->startOfMonth()->subMonths($i)->format('Y-m');
            $series[] = (float) ($byMonth[$key] ?? 0);
        }

        return $series;
    }

    /**
     * @return array<int, int>
     */
    public function monthlyCountSeries(
        int $businessId,
        string $table,
        string $dateColumn,
        Carbon $endDate,
        int $months = 6
    ): array {
        if (! Schema::hasTable($table) || ! Schema::hasColumn($table, $dateColumn)) {
            return array_fill(0, $months, 0);
        }

        $start = $endDate->copy()->startOfMonth()->subMonths($months - 1)->startOfDay();
        $end = $endDate->copy()->endOfMonth()->endOfDay();

        $byMonth = DB::table($table)
            ->selectRaw('DATE_FORMAT('.$dateColumn.', "%Y-%m") as month_key, COUNT(*) as total')
            ->where('business_id', $businessId)
            ->where($dateColumn, '>=', $start)
            ->where($dateColumn, '<=', $end)
            ->groupBy('month_key')
            ->pluck('total', 'month_key')
            ->all();

        $series = [];
        for ($i = $months - 1; $i >= 0; $i--) {
            $key = $endDate->copy()->startOfMonth()->subMonths($i)->format('Y-m');
            $series[] = (int) ($byMonth[$key] ?? 0);
        }

        return $series;
    }

    protected function applyLocationFilter($query, ?array $locationIds): void
    {
        if ($locationIds === null) {
            $locationIds = $this->assignmentScope->allowedLocationIds();
        }

        if ($locationIds !== null && ! empty($locationIds)) {
            $query->whereIn('location_id', $locationIds);
        }
    }
}