Data sources · SQLite-backed source adapters

<?php

declare(strict_types=1);

namespace App\Reports\DatabaseSources;

use App\Models\DemoOrder;
use Guggach\Reports\Aggregates\Count;
use Guggach\Reports\Aggregates\Sum;
use Guggach\Reports\Definition\ReportBuilder;
use Guggach\Reports\Filters\FilterBuilder;
use Guggach\Reports\Filters\FilterControl;
use Guggach\Reports\Report as BaseReport;
use Guggach\Reports\Sources\ArraySource;
use Guggach\Reports\Sources\CollectionSource;
use Guggach\Reports\Sources\EloquentSource;
use Guggach\Reports\Sources\QuerySource;
use Guggach\Reports\Sources\RawSqlSource;
use Guggach\Reports\Sources\ReportField;
use Guggach\Reports\Sources\ReportSource;
use Illuminate\Support\Facades\DB;

/**
 * SQLite-backed demo: the same report can be rendered through every source
 * adapter. Pick the source in the generated filter form; the report start band
 * shows which adapter is active.
 */
final class Report extends BaseReport
{
    /** @var array<string, string> */
    private const SOURCE_LABELS = [
        'query' => 'QuerySource · DB::table()',
        'eloquent' => 'EloquentSource · Model',
        'raw_sql' => 'RawSqlSource · SQL + Bindings',
        'collection' => 'CollectionSource · Collection',
        'array' => 'ArraySource · Array',
    ];

    public function key(): string
    {
        return 'database_sources';
    }

    public function name(): string
    {
        return 'Datenquellen (SQLite)';
    }

    public function layout(): string
    {
        return 'default';
    }

    public function source(): ReportSource
    {
        $fields = $this->dataFields();

        $source = match ($this->selectedSource()) {
            'eloquent' => new EloquentSource(DemoOrder::class, $fields),
            'raw_sql' => new RawSqlSource('select * from demo_orders', [], $fields),
            'collection' => new CollectionSource(DemoOrder::all(), $fields),
            'array' => new ArraySource(DemoOrder::all()->toArray(), $fields),
            default => new QuerySource(DB::table('demo_orders'), $fields),
        };

        return $source
            ->withFilters($this->filters())
            ->withSorts(['amount' => 'desc']);
    }

    /**
     * @return list<FilterControl>
     */
    public function filterControls(): array
    {
        $sourceControl = new FilterControl(
            key: 'source',
            label: 'Quelle',
            control: 'select',
            type: 'enum',
            operators: ['='],
            defaultOperator: '=',
            options: self::SOURCE_LABELS,
            required: true,
        );

        return [
            $sourceControl,
            ...FilterBuilder::for($this->dataFields())->build(),
        ];
    }

    public function define(ReportBuilder $r): void
    {
        $r->reportStart(view: 'report-start');
        $r->gridHeader(view: 'grid-head');
        $r->detail(view: 'row');
        $r->reportEnd(view: 'report-end');

        $r->aggregate('total', Sum::class, field: 'amount', scope: 'report');
        $r->aggregate('orders', Count::class, field: 'order_number', scope: 'report');
    }

    /**
     * @return array<string, string>
     */
    public static function sourceLabels(): array
    {
        return self::SOURCE_LABELS;
    }

    /**
     * @return list<ReportField>
     */
    private function dataFields(): array
    {
        return [
            new ReportField('order_number', 'Auftrag'),
            new ReportField('customer', 'Kunde'),
            new ReportField('category', 'Kategorie', type: 'enum', options: [
                'Miete' => 'Miete',
                'Service' => 'Service',
                'Verkauf' => 'Verkauf',
            ]),
            new ReportField('amount', 'Betrag', type: 'number', aggregatable: true),
            new ReportField('ordered_at', 'Datum', type: 'date'),
            new ReportField('status', 'Status', type: 'enum', options: [
                'offen' => 'offen',
                'bezahlt' => 'bezahlt',
            ]),
        ];
    }

    private function selectedSource(): string
    {
        $raw = $this->filters()['source'] ?? 'query';

        if (is_array($raw)) {
            $raw = $raw['value'] ?? $raw[0] ?? 'query';
        }

        return is_string($raw) && array_key_exists($raw, self::SOURCE_LABELS) ? $raw : 'query';
    }
}

<?php

declare(strict_types=1);

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

final class DemoOrder extends Model
{
    protected $table = 'demo_orders';

    public $timestamps = false;

    protected $guarded = [];
}

<?php

declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('demo_orders', function (Blueprint $table): void {
            $table->id();
            $table->string('order_number')->unique();
            $table->string('customer');
            $table->string('category');
            $table->decimal('amount', 10, 2);
            $table->date('ordered_at');
            $table->string('status');
        });

        DB::table('demo_orders')->insert([
            ['order_number' => 'AU-2026-1001', 'customer' => 'Alpine Studio GmbH', 'category' => 'Miete', 'amount' => 2400.00, 'ordered_at' => '2026-09-18', 'status' => 'bezahlt'],
            ['order_number' => 'AU-2026-1002', 'customer' => 'BA Contemporary Dance', 'category' => 'Miete', 'amount' => 2133.35, 'ordered_at' => '2026-09-18', 'status' => 'offen'],
            ['order_number' => 'AU-2026-1003', 'customer' => 'Müller Informatik AG', 'category' => 'Service', 'amount' => 860.00, 'ordered_at' => '2026-09-22', 'status' => 'bezahlt'],
            ['order_number' => 'AU-2026-1004', 'customer' => 'Seeblick Immobilien', 'category' => 'Miete', 'amount' => 3200.00, 'ordered_at' => '2026-09-28', 'status' => 'offen'],
            ['order_number' => 'AU-2026-1005', 'customer' => 'Nordlicht GmbH', 'category' => 'Verkauf', 'amount' => 12500.00, 'ordered_at' => '2026-10-02', 'status' => 'bezahlt'],
            ['order_number' => 'AU-2026-1006', 'customer' => 'Kunsthaus West', 'category' => 'Service', 'amount' => 1450.00, 'ordered_at' => '2026-10-05', 'status' => 'offen'],
            ['order_number' => 'AU-2026-1007', 'customer' => 'Rheinblick AG', 'category' => 'Miete', 'amount' => 1800.00, 'ordered_at' => '2026-10-08', 'status' => 'bezahlt'],
            ['order_number' => 'AU-2026-1008', 'customer' => 'Studio Nord', 'category' => 'Verkauf', 'amount' => 7600.00, 'ordered_at' => '2026-10-12', 'status' => 'offen'],
            ['order_number' => 'AU-2026-1009', 'customer' => 'Atelier Blau', 'category' => 'Service', 'amount' => 980.00, 'ordered_at' => '2026-10-17', 'status' => 'bezahlt'],
            ['order_number' => 'AU-2026-1010', 'customer' => 'Hofmann Consulting', 'category' => 'Miete', 'amount' => 2700.00, 'ordered_at' => '2026-10-21', 'status' => 'offen'],
            ['order_number' => 'AU-2026-1011', 'customer' => 'Kreativwerkstatt AG', 'category' => 'Service', 'amount' => 640.00, 'ordered_at' => '2026-10-24', 'status' => 'bezahlt'],
            ['order_number' => 'AU-2026-1012', 'customer' => 'Zürich Design Lab', 'category' => 'Verkauf', 'amount' => 9400.00, 'ordered_at' => '2026-10-29', 'status' => 'offen'],
        ]);
    }

    public function down(): void
    {
        Schema::dropIfExists('demo_orders');
    }
};

@php
    $selected = $ctx->filters['source'] ?? 'query';

    if (is_array($selected)) {
        $selected = $selected['value'] ?? $selected[0] ?? 'query';
    }

    $labels = \App\Reports\DatabaseSources\Report::sourceLabels();
@endphp

<style>
    .db-demo-head {
        display: flex;
        justify-content: space-between;
        align-items: flex-start;
        gap: 8mm;
        border-bottom: 3px solid #0f766e;
        padding-bottom: 4mm;
        margin-bottom: 5mm;
    }
    .db-demo-head h1 { margin: 0 0 1mm; font-size: 22px; color: #0f172a; }
    .db-demo-head p { margin: 0; font-size: 10px; color: #64748b; }
    .db-source-badge {
        padding: 2mm 3mm;
        border-radius: 2mm;
        background: #ccfbf1;
        color: #0f766e;
        font-size: 9px;
        font-weight: 700;
        white-space: nowrap;
    }
    .db-source-hint {
        margin-bottom: 5mm;
        padding: 2.5mm 3mm;
        background: #f8fafc;
        border-left: 3px solid #0f766e;
        font-size: 9px;
        color: #475569;
    }
    .db-grid-head,
    .db-row {
        display: flex;
        align-items: center;
        gap: 3mm;
        box-sizing: border-box;
    }
    .db-grid-head {
        height: 9mm;
        border-bottom: 2px solid #0f766e;
        font-size: 8px;
        font-weight: 700;
        color: #0f172a;
    }
    .db-row {
        min-height: 9mm;
        border-bottom: 1px solid #e2e8f0;
        font-size: 9px;
        color: #334155;
    }
    .db-col-order { flex: 0 0 30mm; font-weight: 600; color: #0f172a; }
    .db-col-customer { flex: 1 1 auto; }
    .db-col-category { flex: 0 0 22mm; }
    .db-col-date { flex: 0 0 22mm; color: #64748b; }
    .db-col-status { flex: 0 0 20mm; }
    .db-col-amount { flex: 0 0 24mm; text-align: right; font-variant-numeric: tabular-nums; }
    .db-status {
        display: inline-block;
        padding: 0.8mm 1.6mm;
        border-radius: 1mm;
        font-size: 8px;
        font-weight: 600;
    }
    .db-status--paid { background: #dcfce7; color: #166534; }
    .db-status--open { background: #fef3c7; color: #92400e; }
    .db-summary {
        display: flex;
        justify-content: flex-end;
        gap: 10mm;
        margin-top: 4mm;
        padding-top: 3mm;
        border-top: 2px solid #0f172a;
        font-size: 11px;
        font-weight: 700;
    }
</style>

<div class="db-demo-head">
    <div>
        <h1>Datenquellen</h1>
        <p>Dieselben SQLite-Daten, gerendert durch den gewählten Source-Adapter.</p>
    </div>
    <div class="db-source-badge">{{ $labels[$selected] ?? $selected }}</div>
</div>

<div class="db-source-hint">
    Wähle im Filterformular eine andere Quelle. Alle fünf Adapter lesen dieselbe Tabelle
    <code>demo_orders</code>; Query- und Eloquent-Source filtern und sortieren per SQL,
    die übrigen Adapter in-memory.
</div>

<div class="db-grid-head">
    <span class="db-col-order">Auftrag</span>
    <span class="db-col-customer">Kunde</span>
    <span class="db-col-category">Kategorie</span>
    <span class="db-col-date">Datum</span>
    <span class="db-col-status">Status</span>
    <span class="db-col-amount">Betrag</span>
</div>

<div class="db-row">
    <span class="db-col-order">{{ $ctx->order_number }}</span>
    <span class="db-col-customer">{{ $ctx->customer }}</span>
    <span class="db-col-category">{{ $ctx->category }}</span>
    <span class="db-col-date">{{ \Illuminate\Support\Carbon::parse($ctx->ordered_at)->format('d.m.Y') }}</span>
    <span class="db-col-status">
        <span class="db-status {{ $ctx->status === 'bezahlt' ? 'db-status--paid' : 'db-status--open' }}">
            {{ $ctx->status }}
        </span>
    </span>
    <span class="db-col-amount">CHF {{ number_format((float) $ctx->amount, 2, '.', "'") }}</span>
</div>

<div class="db-summary">
    <span>{{ $ctx->aggregates['orders'] }} Aufträge</span>
    <span>Total CHF {{ number_format((float) $ctx->aggregates['total'], 2, '.', "'") }}</span>
</div>