Skip to content

Beautiful Open-Source Form Builder

2.4 KB
Raw Blame
<?php

namespace App\Service\Forms\Analytics;

use App\Models\Forms\Form;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

class FormViewStatistics
{
    private const FIELDS = ['source', 'device', 'country', 'browser', 'os'];

    public function emptyMetadata(): array
    {
        return array_fill_keys(self::FIELDS, []);
    }

    public function mergeMetadata(array $stats, array $counts): array
    {
        foreach (self::FIELDS as $field) {
            foreach ($counts[$field] ?? [] as $value => $count) {
                $stats[$field][$value] = ($stats[$field][$value] ?? 0) + $count;
            }
        }

        return $stats;
    }

    public function rowMetadata(object $row): array
    {
        $stats = $this->emptyMetadata();
        foreach (self::FIELDS as $field) {
            $stats[$field][$row->$field] = (int) $row->count;
        }

        return $stats;
    }

    public function groupedViews(Builder $query): Builder
    {
        $pattern = $query->getConnection()->getDriverName() === 'mysql'
            ? "COALESCE(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(meta, '$.%s')), ''), 'null'), 'Unknown')"
            : "COALESCE(NULLIF(NULLIF(meta->>'%s', ''), 'null'), 'Unknown')";

        foreach (self::FIELDS as $field) {
            $expression = sprintf($pattern, $field);
            $query->selectRaw("$expression as $field")->groupByRaw($expression);
        }

        return $query->selectRaw('COUNT(*) as count');
    }

    public function metadata(Form $form): array
    {
        $views = $this->groupedViews(DB::table('form_views')->where('form_id', $form->id))
            ->selectRaw('NULL as data');
        $history = DB::table('form_statistics')->where('form_id', $form->id);
        foreach (self::FIELDS as $field) {
            $history->selectRaw("NULL as $field");
        }
        $history->selectRaw('0 as count')->addSelect('data');

        // One statement keeps both sources on the same snapshot during cleanup.
        $stats = $this->emptyMetadata();
        foreach ($views->unionAll($history)->cursor() as $row) {
            $counts = $row->data === null
                ? $this->rowMetadata($row)
                : (json_decode($row->data, true)['meta_stats'] ?? []);
            $stats = $this->mergeMetadata($stats, $counts);
        }

        foreach (self::FIELDS as $field) {
            arsort($stats[$field]);
        }

        return $stats;
    }
}