kuvia/app/Models/Traffic.php

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Illuminate\Support\Facades\DB;

class Traffic extends Authenticatable
{
    use HasFactory, Notifiable;

    protected $table = "traffic";
    public $timestamps = true;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'tenant',
        'gallery',
        'year',
        'month',
        'day',
        'hour',
        'traffic'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
    ];

    public static function getLastDays(int $days = 7) {
        $sql = "SELECT year, month, day, typ, SUM(traffic) AS `traffic` FROM traffic  WHERE tenant = ".session("current_tenant_id")." GROUP BY year, month, day, typ ORDER BY  year DESC, month DESC, day DESC LIMIT ".($days + 2).";";
        $res = DB::select($sql);

        $now = new \DateTime( $days." days ago", new \DateTimeZone('UTC'));
        $interval = new \DateInterval( 'P1D'); // 1 Day interval
        $period = new \DatePeriod( $now, $interval, $days); // 7 Days
        $result = [];
        foreach( $period as $day) {
            $key = $day->format('Y-m-d');
            $result[$day->format('Y-m-d')] = ["cache" => 0, "s3" => 0];
            foreach($res as $r) {
                if($r->year == $day->format('Y') && $r->month == $day->format('m') && $r->day == $day->format('d')) {
                    $result[$day->format('Y-m-d')][$r->typ] = $r->traffic / 1000 / 1000;
                }
            }

        }
        return $result;
    }

}