backend/app/Services/Cartables/RoadItem/ReportService.php

<?php

namespace App\Services\Cartables\RoadItem;

use App\Models\RoadItemsProject;
use Illuminate\Http\Request;

class ReportService
{
    public function countryActivityPerSubItem(Request $request): array
    {
        $fromDate = $request->from_date ? $request->from_date . ' 00:00:00' : now()->startOfDay()->toDateTimeString();
        $toDate = $request->date_to ? $request->date_to . ' 23:59:59' : now()->endOfDay()->toDateTimeString();
        $item = $request->item;

        $provinceRoadItemsPerSubItem = RoadItemsProject::query()->selectRaw('
            sub_item AS t,
            COUNT(*) AS c,
            CAST(SUM(CASE WHEN sub_item_data > 0 THEN sub_item_data ELSE ABS(sub_item_data) END) AS UNSIGNED) AS s,
            province_id AS p')
            ->where('item', '=', $item)
            ->whereBetween('activity_date_time', [$fromDate, $toDate])
            ->where('status', '=', 1)
            ->groupBy('province_id', 'sub_item')
            ->get();

        $countryRoadItemsPerSubItem = RoadItemsProject::query()->selectRaw('
            sub_item AS t,
            COUNT(*) AS c,
            CAST(SUM(CASE WHEN sub_item_data > 0 THEN sub_item_data ELSE ABS(sub_item_data) END) AS UNSIGNED) AS s,
            -1 AS p')
            ->where('item', '=', $item)
            ->whereBetween('activity_date_time', [$fromDate, $toDate])
            ->where('status', '=', 1)
            ->groupBy('sub_item')
            ->get();


        $activities = $provinceRoadItemsPerSubItem->push(...$countryRoadItemsPerSubItem);

        return [
            'data' => $activities,
            'fromDate' => $fromDate,
            'toDate' => $toDate,
        ];
    }

    public function provinceActivityPerSubItem(Request $request): array
    {
        $fromDate = $request->from_date ? $request->from_date . ' 00:00:00' : now()->startOfDay()->toDateTimeString();
        $toDate = $request->date_to ? $request->date_to . ' 23:59:59' : now()->endOfDay()->toDateTimeString();
        $item = $request->item;
        $province = $request->province_id;

        $cityRoadItemsPerSubItem = RoadItemsProject::query()->selectRaw('
            sub_item AS t,
            COUNT(*) AS c,
            CAST(SUM(CASE WHEN sub_item_data > 0 THEN sub_item_data ELSE ABS(sub_item_data) END) AS UNSIGNED) AS s,
            province_id AS p,
            edarat_id AS ci
        ')
            ->where('item', '=', $item)
            ->where('province_id', '=', $province)
            ->whereBetween('activity_date_time', [$fromDate, $toDate])
            ->where('status', '=', 1)
            ->groupBy('edarat_id', 'sub_item')
            ->get();

        $provinceRoadItemsPerSubItem = RoadItemsProject::query()->selectRaw('
            sub_item AS t,
            COUNT(*) AS c,
            CAST(SUM(CASE WHEN sub_item_data > 0 THEN sub_item_data ELSE ABS(sub_item_data) END) AS UNSIGNED) AS s,
            province_id AS p,
            -1 AS ci
        ')
            ->where('item', '=', $item)
            ->where('province_id', '=', $province)
            ->whereBetween('activity_date_time', [$fromDate, $toDate])
            ->where('status', '=', 1)
            ->groupBy('sub_item')
            ->get();


        $activities = $cityRoadItemsPerSubItem->push(...$provinceRoadItemsPerSubItem);

        return [
            'data' => $activities,
            'fromDate' => $fromDate,
            'toDate' => $toDate,
        ];
    }

    public function countryActivityPerItem(Request $request): array
    {
        $fromDate = $request->from_date ? $request->from_date . ' 00:00:00' : now()->startOfDay()->toDateTimeString();
        $toDate = $request->date_to ? $request->date_to . ' 23:59:59' : now()->endOfDay()->toDateTimeString();
        $user = auth()->user();
//        $isSpecialUser = in_array($user->username, ['witel', 'drdanesh']);
        $isSpecialUser = $user->hasPermissionTo("show-road-item-supervise-cartable");
        $provinceId = $isSpecialUser ? null : $user->province_id;

        $provinceActivity = RoadItemsProject::query()
            ->selectRaw('COUNT(*) AS s, province_id AS p, item AS i')
            ->where('status', 1)
            ->whereBetween('activity_date_time', [$fromDate, $toDate])
            ->when(!$isSpecialUser, function ($query) use ($provinceId) {
                $query->where('province_id', $provinceId);
            })
            ->groupBy('province_id', 'item');

        $countryActivity = RoadItemsProject::query()
            ->selectRaw('COUNT(*) AS s, -1 AS p, item AS i')
            ->where('status', 1)
            ->whereBetween('activity_date_time', [$fromDate, $toDate])
            ->when(!$isSpecialUser, function ($query) use ($provinceId) {
                $query->where('province_id', $provinceId);
            })
            ->groupBy('item');

        $activities = $provinceActivity
            ->unionAll($countryActivity)
            ->get();

        return [
            'data' => $activities,
            'fromDate' => $fromDate,
            'toDate' => $toDate,
        ];
    }

    public function provinceActivityPerItem(Request $request): array
    {
        $provinceId = $request->province_id;
        $fromDate = $request->from_date ? $request->from_date . ' 00:00:00' : now()->startOfDay()->toDateTimeString();
        $toDate = $request->date_to ? $request->date_to . ' 23:59:59' : now()->endOfDay()->toDateTimeString();

        $edarehActivity = RoadItemsProject::query()
            ->selectRaw('
            COUNT(*) AS s,
            edarat_id AS c,
            item AS i')
            ->where('status', '=', 1)
            ->whereBetween('activity_date_time', [$fromDate, $toDate])
            ->where('province_id', $provinceId)
            ->groupBy('item', 'edarat_id');

        $cityActivity = RoadItemsProject::query()
            ->selectRaw('
            COUNT(*) AS s,
            -1 AS c,
            item AS i')
            ->where('status', '=', 1)
            ->whereBetween('activity_date_time', [$fromDate, $toDate])
            ->where('province_id', $provinceId)
            ->groupBy('item');

        $activities = $edarehActivity
            ->unionAll($cityActivity)
            ->get();

        return [
            'data' => $activities,
            'fromDate' => $fromDate,
            'toDate' => $toDate,
        ];
    }
}