38 lines
1.2 KiB
PHP
38 lines
1.2 KiB
PHP
<?php
|
|
|
|
namespace App\Domains\Analytics\Jobs;
|
|
|
|
use Illuminate\Bus\Queueable;
|
|
use Illuminate\Contracts\Queue\ShouldQueue;
|
|
use Illuminate\Foundation\Bus\Dispatchable;
|
|
use Illuminate\Queue\InteractsWithQueue;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
class RollupDailyJob implements ShouldQueue
|
|
{
|
|
use Dispatchable, InteractsWithQueue, Queueable;
|
|
|
|
public function handle(): void
|
|
{
|
|
$targetDay = now()->subDay()->startOfDay();
|
|
$nextDay = $targetDay->copy()->addDay();
|
|
|
|
DB::statement("
|
|
INSERT INTO clicks_daily (link_id, day, country, device, count, unique_visitors)
|
|
SELECT
|
|
link_id,
|
|
DATE(clicked_at) AS day,
|
|
COALESCE(country, '') AS country,
|
|
COALESCE(device, 'unknown') AS device,
|
|
COUNT(*) AS count,
|
|
COUNT(DISTINCT ip_hash) AS unique_visitors
|
|
FROM clicks
|
|
WHERE clicked_at >= ? AND clicked_at < ?
|
|
GROUP BY link_id, day, country, device
|
|
ON DUPLICATE KEY UPDATE
|
|
count = count + VALUES(count),
|
|
unique_visitors = GREATEST(unique_visitors, VALUES(unique_visitors))
|
|
", [$targetDay, $nextDay]);
|
|
}
|
|
}
|