芝麻web文件管理V1.00

编辑当前文件:/home/forge/stage.herta-bht.smartcon-survey.com/app/Services/TempoService.php

<?php

namespace App\Services;

use App\Models\Brand;
use App\Models\Product;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Collection;

class TempoService
{
    public function getFunnel(Brand|Product $model)
    {
        $currentQuarter = [
            'year' => Carbon::now()->year,
            'quarter' => Carbon::now()->quarter,
        ];

$rawData = require base_path('app/Services/data/funnel.php');
        $rawData = $rawData[$model->sid] ?? [];

$funnelData = [];

$previousQuarterData = null;

foreach ($rawData as $quarterYear => $data) {
            $quarterKey = str_replace(' ', '-', $quarterYear); // Format key as QX-YYYY

$currentQuarter = [
                'totalN' => $data['Base'],
                'title' => 'KPI Funnel',
                'quarterYear' => $quarterYear,
                'spontaneousAwareness' => [
                    'label' => 'Spontaneous Awareness',
                    'n' => $data['Spontaneous Awareness'][0],
                    'value' => (float) $data['Spontaneous Awareness'][1],
                    'conversion' => 0.0, // Typically no conversion for the first awareness metric
                    'delta' => $this->calculate_delta(
                        (float) $data['Spontaneous Awareness'][1],
                        $previousQuarterData ? (float) $previousQuarterData['spontaneousAwareness']['value'] : null
                    ),
                ],
                'topOfMind' => [
                    'label' => 'Top Of Mind', // Using the label from data, not 'Rejection'
                    'n' => $data['Top Of Mind'][0],
                    'value' => (float) $data['Top Of Mind'][1],
                    'conversion' => 0.0, // Assuming no direct funnel conversion for ToM
                    'delta' => $this->calculate_delta(
                        (float) $data['Top Of Mind'][1],
                        $previousQuarterData ? (float) $previousQuarterData['topOfMind']['value'] : null
                    ),
                ],
                'data' => [
                    [
                        'label' => 'Awareness',
                        'n' => $data['Awareness'][0],
                        'value' => (float) $data['Awareness'][1],
                        'conversion' => 0.0, // First step in funnel
                        'delta' => $this->calculate_delta(
                            (float) $data['Awareness'][1],
                            $previousQuarterData ? (float) $previousQuarterData['data'][0]['value'] : null // Index 0 = Awareness
                        ),
                    ],
                    [
                        'label' => 'Consideration',
                        'n' => $data['Consideration'][0],
                        'value' => (float) $data['Consideration'][1],
                        'conversion' => $this->calculate_conversion($data['Consideration'][0], $data['Awareness'][0]),
                        'delta' => $this->calculate_delta(
                            (float) $data['Consideration'][1],
                            $previousQuarterData ? (float) $previousQuarterData['data'][1]['value'] : null // Index 1 = Consideration
                        ),
                    ],
                    [
                        'label' => 'Trial',
                        'n' => $data['Trial'][0],
                        'value' => (float) $data['Trial'][1],
                        'conversion' => $this->calculate_conversion($data['Trial'][0], $data['Consideration'][0]),
                        'delta' => $this->calculate_delta(
                            (float) $data['Trial'][1],
                            $previousQuarterData ? (float) $previousQuarterData['data'][2]['value'] : null // Index 2 = Trial
                        ),
                    ],
                    [
                        'label' => 'Repurchase',
                        'n' => $data['Repurchase'][0],
                        'value' => (float) $data['Repurchase'][1],
                        'conversion' => $this->calculate_conversion($data['Repurchase'][0], $data['Trial'][0]),
                        'delta' => $this->calculate_delta(
                            (float) $data['Repurchase'][1],
                            $previousQuarterData ? (float) $previousQuarterData['data'][3]['value'] : null // Index 3 = Repurchase
                        ),
                    ],
                    [
                        'label' => 'Loyalty',
                        'n' => $data['Loyalty'][0],
                        'value' => (float) $data['Loyalty'][1],
                        'conversion' => $this->calculate_conversion($data['Loyalty'][0], $data['Repurchase'][0]),
                        'delta' => $this->calculate_delta(
                            (float) $data['Loyalty'][1],
                            $previousQuarterData ? (float) $previousQuarterData['data'][4]['value'] : null // Index 4 = Loyalty
                        ),
                    ],
                ],
            ];

$funnelData[$quarterKey] = $currentQuarter;
            $previousQuarterData = $currentQuarter; // Store for next iteration's delta calculation
        }

if (! isset($funnelData[array_key_last($funnelData)])) {
            dd($funnelData);
        }

return $funnelData[array_key_last($funnelData)];
    }

private function calculate_conversion($current_n, $previous_n): float
    {
        if ($previous_n == 0) {
            return 0.0;
        }

return round(($current_n / $previous_n) * 100, 1);
    }

private function calculate_delta($current_value, $previous_value): float
    {
        if ($previous_value === null) {
            return 0.0;
        }

return round($current_value - $previous_value, 1);
    }

public function getTimeLine(Brand|Product $model)
    {
        $rawData = require base_path('app/Services/data/timeline.php');

return $rawData[$model->sid] ?? [];
    }

public function getNPS(): array
    {
        return [
            'title' => 'Net Promoter Score',
            'totalN' => 11872, // Sum of Base for Rügenwalder Mühle across all quarters
            'data' => [
                'labels' => [
                    'Q1 2020',
                    'Q2 2020',
                    'Q3 2020',
                    'Q4 2020',
                    'Q1 2021',
                    'Q2 2021',
                    'Q3 2021',
                    'Q4 2021',
                    'Q1 2022',
                    'Q2 2022',
                    'Q3 2022',
                    'Q4 2022',
                    'Q1 2023',
                    'Q2 2023',
                    'Q3 2023',
                    'Q4 2023',
                    'Q1 2024',
                    'Q2 2024',
                    'Q3 2024',
                    'Q4 2024',
                    'Q1 2025',
                    'Q2 2025',
                    'Q3 2025',
                ],
                'datasets' => [
                    // Herta (Order matches example, data from table)
                    [
                        'id' => 1, // Assuming ID 1 based on example order
                        'label' => 'Herta',
                        'borderColor' => '#cc0404',
                        'data' => [
                            ['x' => 'Q1 2020', 'y' => '-6', 'n' => -36, 'base' => 583],
                            ['x' => 'Q2 2020', 'y' => '-5', 'n' => -29, 'base' => 603],
                            ['x' => 'Q3 2020', 'y' => '-17', 'n' => -103, 'base' => 608],
                            ['x' => 'Q4 2020', 'y' => '-12', 'n' => -69, 'base' => 599],
                            ['x' => 'Q1 2021', 'y' => '0', 'n' => -1, 'base' => 626],
                            ['x' => 'Q2 2021', 'y' => '-4', 'n' => -25, 'base' => 585],
                            ['x' => 'Q3 2021', 'y' => '-6', 'n' => -38, 'base' => 603],
                            ['x' => 'Q4 2021', 'y' => '-5', 'n' => -29, 'base' => 633],
                            ['x' => 'Q1 2022', 'y' => '-3', 'n' => -17, 'base' => 599],
                            ['x' => 'Q2 2022', 'y' => '1', 'n' => 4, 'base' => 608],
                            ['x' => 'Q3 2022', 'y' => '-3', 'n' => -18, 'base' => 604],
                            ['x' => 'Q4 2022', 'y' => '-6', 'n' => -37, 'base' => 591],
                            ['x' => 'Q1 2023', 'y' => '0', 'n' => 2, 'base' => 586],
                            ['x' => 'Q2 2023', 'y' => '1', 'n' => 5, 'base' => 554],
                            ['x' => 'Q3 2023', 'y' => '0', 'n' => 2, 'base' => 583],
                            ['x' => 'Q4 2023', 'y' => '-1', 'n' => -4, 'base' => 584],
                            ['x' => 'Q1 2024', 'y' => '15.03', 'n' => 539, 'base' => 754],
                            ['x' => 'Q2 2024', 'y' => '18.58', 'n' => 549, 'base' => 750],
                            ['x' => 'Q3 2024', 'y' => '16.32', 'n' => 570, 'base' => 752],
                            ['x' => 'Q4 2024', 'y' => '16.33', 'n' => 539, 'base' => 750],
                            ['x' => 'Q1 2025', 'y' => '17.19', 'n' => 541, 'base' => 753],
                            ['x' => 'Q2 2025', 'y' => '20.07', 'n' => 548, 'base' => 759],
                            ['x' => 'Q3 2025', 'y' => '19.64', 'n' => 565, 'base' => 755],
                        ],
                    ],
                    // Herta Finesse
                    [
                        'id' => 2,
                        'label' => 'Herta Finesse',
                        'borderColor' => '#b85f22',
                        'data' => [
                            ['x' => 'Q1 2020', 'y' => '3', 'n' => 15, 'base' => 498],
                            ['x' => 'Q2 2020', 'y' => '7', 'n' => 35, 'base' => 485],
                            ['x' => 'Q3 2020', 'y' => '-4', 'n' => -21, 'base' => 490],
                            ['x' => 'Q4 2020', 'y' => '1', 'n' => 5, 'base' => 462],
                            ['x' => 'Q1 2021', 'y' => '13', 'n' => 65, 'base' => 511],
                            ['x' => 'Q2 2021', 'y' => '4', 'n' => 21, 'base' => 482],
                            ['x' => 'Q3 2021', 'y' => '7', 'n' => 35, 'base' => 507],
                            ['x' => 'Q4 2021', 'y' => '6', 'n' => 32, 'base' => 508],
                            ['x' => 'Q1 2022', 'y' => '11', 'n' => 57, 'base' => 496],
                            ['x' => 'Q2 2022', 'y' => '13', 'n' => 61, 'base' => 488],
                            ['x' => 'Q3 2022', 'y' => '6', 'n' => 31, 'base' => 492],
                            ['x' => 'Q4 2022', 'y' => '5', 'n' => 24, 'base' => 500],
                            ['x' => 'Q1 2023', 'y' => '13', 'n' => 66, 'base' => 498],
                            ['x' => 'Q2 2023', 'y' => '15', 'n' => 74, 'base' => 481],
                            ['x' => 'Q3 2023', 'y' => '10', 'n' => 46, 'base' => 473],
                            ['x' => 'Q4 2023', 'y' => '11', 'n' => 54, 'base' => 478],
                            ['x' => 'Q1 2024', 'y' => '24.63', 'n' => 467, 'base' => 754],
                            ['x' => 'Q2 2024', 'y' => '19.53', 'n' => 466, 'base' => 750],
                            ['x' => 'Q3 2024', 'y' => '20.96', 'n' => 477, 'base' => 752],
                            ['x' => 'Q4 2024', 'y' => '21.97', 'n' => 478, 'base' => 750],
                            ['x' => 'Q1 2025', 'y' => '21.89', 'n' => 475, 'base' => 753],
                            ['x' => 'Q2 2025', 'y' => '28.47', 'n' => 467, 'base' => 759],
                            ['x' => 'Q3 2025', 'y' => '20.63', 'n' => 475, 'base' => 755],
                        ],
                    ],
                    // Gutfried
                    [
                        'id' => 3,
                        'label' => 'Gutfried',
                        'borderColor' => '#329bab',
                        'data' => [
                            ['x' => 'Q1 2020', 'y' => '-7', 'n' => -40, 'base' => 594],
                            ['x' => 'Q2 2020', 'y' => '-2', 'n' => -12, 'base' => 604],
                            ['x' => 'Q3 2020', 'y' => '-22', 'n' => -133, 'base' => 602],
                            ['x' => 'Q4 2020', 'y' => '-13', 'n' => -74, 'base' => 590],
                            ['x' => 'Q1 2021', 'y' => '-3', 'n' => -18, 'base' => 611],
                            ['x' => 'Q2 2021', 'y' => '-13', 'n' => -74, 'base' => 591],
                            ['x' => 'Q3 2021', 'y' => '-4', 'n' => -22, 'base' => 599],
                            ['x' => 'Q4 2021', 'y' => '-5', 'n' => -32, 'base' => 623],
                            ['x' => 'Q1 2022', 'y' => '2', 'n' => 15, 'base' => 613],
                            ['x' => 'Q2 2022', 'y' => '0', 'n' => -2, 'base' => 620],
                            ['x' => 'Q3 2022', 'y' => '0', 'n' => 0, 'base' => 589],
                            ['x' => 'Q4 2022', 'y' => '-6', 'n' => -35, 'base' => 595],
                            ['x' => 'Q1 2023', 'y' => '3', 'n' => 18, 'base' => 583],
                            ['x' => 'Q2 2023', 'y' => '4', 'n' => 26, 'base' => 598],
                            ['x' => 'Q3 2023', 'y' => '1', 'n' => 4, 'base' => 599],
                            ['x' => 'Q4 2023', 'y' => '-1', 'n' => -6, 'base' => 575],
                            ['x' => 'Q1 2024', 'y' => '11.20', 'n' => 598, 'base' => 754],
                            ['x' => 'Q2 2024', 'y' => '13.13', 'n' => 571, 'base' => 750],
                            ['x' => 'Q3 2024', 'y' => '17.11', 'n' => 602, 'base' => 752],
                            ['x' => 'Q4 2024', 'y' => '12.54', 'n' => 582, 'base' => 750],
                            ['x' => 'Q1 2025', 'y' => '16.70', 'n' => 575, 'base' => 753],
                            ['x' => 'Q2 2025', 'y' => '18.53', 'n' => 572, 'base' => 759],
                            ['x' => 'Q3 2025', 'y' => '15.67', 'n' => 574, 'base' => 755],
                        ],
                    ],
                    // Rügenwalder Mühle
                    [
                        'id' => 4,
                        'label' => 'Rügenwalder Mühle',
                        'borderColor' => '#4132ab',
                        'data' => [
                            ['x' => 'Q1 2020', 'y' => '17', 'n' => 101, 'base' => 600],
                            ['x' => 'Q2 2020', 'y' => '18', 'n' => 103, 'base' => 588],
                            ['x' => 'Q3 2020', 'y' => '11', 'n' => 70, 'base' => 619],
                            ['x' => 'Q4 2020', 'y' => '17', 'n' => 102, 'base' => 584],
                            ['x' => 'Q1 2021', 'y' => '21', 'n' => 126, 'base' => 596],
                            ['x' => 'Q2 2021', 'y' => '13', 'n' => 78, 'base' => 590],
                            ['x' => 'Q3 2021', 'y' => '17', 'n' => 103, 'base' => 610],
                            ['x' => 'Q4 2021', 'y' => '21', 'n' => 130, 'base' => 614],
                            ['x' => 'Q1 2022', 'y' => '23', 'n' => 135, 'base' => 599],
                            ['x' => 'Q2 2022', 'y' => '21', 'n' => 132, 'base' => 630],
                            ['x' => 'Q3 2022', 'y' => '24', 'n' => 142, 'base' => 600],
                            ['x' => 'Q4 2022', 'y' => '15', 'n' => 89, 'base' => 589],
                            ['x' => 'Q1 2023', 'y' => '21', 'n' => 120, 'base' => 576],
                            ['x' => 'Q2 2023', 'y' => '26', 'n' => 145, 'base' => 559],
                            ['x' => 'Q3 2023', 'y' => '19', 'n' => 107, 'base' => 568],
                            ['x' => 'Q4 2023', 'y' => '24', 'n' => 132, 'base' => 555],
                            ['x' => 'Q1 2024', 'y' => '15.76', 'n' => 571, 'base' => 754],
                            ['x' => 'Q2 2024', 'y' => '23.52', 'n' => 540, 'base' => 750],
                            ['x' => 'Q3 2024', 'y' => '20.52', 'n' => 580, 'base' => 752],
                            ['x' => 'Q4 2024', 'y' => '23.70', 'n' => 540, 'base' => 750],
                            ['x' => 'Q1 2025', 'y' => '23.63', 'n' => 546, 'base' => 753],
                            ['x' => 'Q2 2025', 'y' => '23.26', 'n' => 520, 'base' => 759],
                            ['x' => 'Q3 2025', 'y' => '21.53', 'n' => 520, 'base' => 755],
                        ],
                    ],
                ],
            ],
        ];
    }

public function getUsageData(): array
    {
        return [
            'title' => 'Usage in %',
            'totalN' => 11872, // Sum of Base for Rügenwalder Mühle across all quarters
            'data' => [
                'labels' => [
                    'Q1 2020',
                    'Q2 2020',
                    'Q3 2020',
                    'Q4 2020',
                    'Q1 2021',
                    'Q2 2021',
                    'Q3 2021',
                    'Q4 2021',
                    'Q1 2022',
                    'Q2 2022',
                    'Q3 2022',
                    'Q4 2022',
                    'Q1 2023',
                    'Q2 2023',
                    'Q3 2023',
                    'Q4 2023',
                    'Q1 2024',
                    'Q2 2024',
                    'Q3 2024',
                    'Q4 2024',
                    'Q1 2025',
                    'Q2 2025',
                    'Q3 2025',
                ],
                'datasets' => [
                    // Herta (Order matches example, data from table)
                    [
                        'id' => 1,
                        'label' => 'Herta',
                        'borderColor' => '#cc0404',
                        'data' => [
                            ['x' => 'Q1 2020', 'y' => 20.0, 'n' => 151, 'base' => 583],
                            ['x' => 'Q2 2020', 'y' => 22.0, 'n' => 166, 'base' => 603],
                            ['x' => 'Q3 2020', 'y' => 19.0, 'n' => 144, 'base' => 608],
                            ['x' => 'Q4 2020', 'y' => 20.0, 'n' => 151, 'base' => 599],
                            ['x' => 'Q1 2021', 'y' => 26.0, 'n' => 198, 'base' => 626],
                            ['x' => 'Q2 2021', 'y' => 23.0, 'n' => 174, 'base' => 585],
                            ['x' => 'Q3 2021', 'y' => 23.0, 'n' => 178, 'base' => 603],
                            ['x' => 'Q4 2021', 'y' => 24.0, 'n' => 187, 'base' => 633],
                            ['x' => 'Q1 2022', 'y' => 24.0, 'n' => 183, 'base' => 599],
                            ['x' => 'Q2 2022', 'y' => 24.0, 'n' => 183, 'base' => 608],
                            ['x' => 'Q3 2022', 'y' => 22.0, 'n' => 168, 'base' => 604],
                            ['x' => 'Q4 2022', 'y' => 23.0, 'n' => 180, 'base' => 591],
                            ['x' => 'Q1 2023', 'y' => 24.0, 'n' => 183, 'base' => 586],
                            ['x' => 'Q2 2023', 'y' => 20.0, 'n' => 152, 'base' => 554],
                            ['x' => 'Q3 2023', 'y' => 26.0, 'n' => 199, 'base' => 583],
                            ['x' => 'Q4 2023', 'y' => 25.0, 'n' => 191, 'base' => 584],
                            ['x' => 'Q1 2024', 'y' => 36.0, 'n' => 196, 'base' => 539],
                            ['x' => 'Q2 2024', 'y' => 36.0, 'n' => 200, 'base' => 549],
                            ['x' => 'Q3 2024', 'y' => 35.0, 'n' => 197, 'base' => 570],
                            ['x' => 'Q4 2024', 'y' => 38.0, 'n' => 205, 'base' => 539],
                            ['x' => 'Q1 2025', 'y' => 34.0, 'n' => 184, 'base' => 541],
                            ['x' => 'Q2 2025', 'y' => 38.0, 'n' => 204, 'base' => 544],
                            ['x' => 'Q3 2025', 'y' => 39.0, 'n' => 220, 'base' => 565],
                        ],
                    ],
                    // Herta Finesse
                    [
                        'id' => 2,
                        'label' => 'Herta Finesse',
                        'borderColor' => '#b85f22',
                        'data' => [
                            ['x' => 'Q1 2020', 'y' => 20.0, 'n' => 150, 'base' => 498],
                            ['x' => 'Q2 2020', 'y' => 20.0, 'n' => 150, 'base' => 485],
                            ['x' => 'Q3 2020', 'y' => 19.0, 'n' => 142, 'base' => 490],
                            ['x' => 'Q4 2020', 'y' => 18.0, 'n' => 132, 'base' => 462],
                            ['x' => 'Q1 2021', 'y' => 24.0, 'n' => 184, 'base' => 511],
                            ['x' => 'Q2 2021', 'y' => 19.0, 'n' => 141, 'base' => 482],
                            ['x' => 'Q3 2021', 'y' => 21.0, 'n' => 160, 'base' => 507],
                            ['x' => 'Q4 2021', 'y' => 22.0, 'n' => 172, 'base' => 508],
                            ['x' => 'Q1 2022', 'y' => 21.0, 'n' => 161, 'base' => 496],
                            ['x' => 'Q2 2022', 'y' => 20.0, 'n' => 155, 'base' => 488],
                            ['x' => 'Q3 2022', 'y' => 21.0, 'n' => 161, 'base' => 492],
                            ['x' => 'Q4 2022', 'y' => 20.0, 'n' => 157, 'base' => 500],
                            ['x' => 'Q1 2023', 'y' => 25.0, 'n' => 189, 'base' => 498],
                            ['x' => 'Q2 2023', 'y' => 22.0, 'n' => 165, 'base' => 481],
                            ['x' => 'Q3 2023', 'y' => 22.0, 'n' => 165, 'base' => 473],
                            ['x' => 'Q4 2023', 'y' => 22.0, 'n' => 168, 'base' => 478],
                            ['x' => 'Q1 2024', 'y' => 41.0, 'n' => 192, 'base' => 467],
                            ['x' => 'Q2 2024', 'y' => 40.0, 'n' => 186, 'base' => 466],
                            ['x' => 'Q3 2024', 'y' => 38.0, 'n' => 182, 'base' => 477],
                            ['x' => 'Q4 2024', 'y' => 40.0, 'n' => 189, 'base' => 478],
                            ['x' => 'Q1 2025', 'y' => 40.0, 'n' => 187, 'base' => 475],
                            ['x' => 'Q2 2025', 'y' => 49.0, 'n' => 185, 'base' => 467],
                            ['x' => 'Q3 2025', 'y' => 42.0, 'n' => 201, 'base' => 475],
                        ],
                    ],
                    // Gutfried
                    [
                        'id' => 3,
                        'label' => 'Gutfried',
                        'borderColor' => '#329bab',
                        'data' => [
                            ['x' => 'Q1 2020', 'y' => 22.0, 'n' => 167, 'base' => 594],
                            ['x' => 'Q2 2020', 'y' => 24.0, 'n' => 180, 'base' => 604],
                            ['x' => 'Q3 2020', 'y' => 20.0, 'n' => 152, 'base' => 602],
                            ['x' => 'Q4 2020', 'y' => 23.0, 'n' => 173, 'base' => 590],
                            ['x' => 'Q1 2021', 'y' => 26.0, 'n' => 194, 'base' => 611],
                            ['x' => 'Q2 2021', 'y' => 24.0, 'n' => 180, 'base' => 591],
                            ['x' => 'Q3 2021', 'y' => 21.0, 'n' => 164, 'base' => 599],
                            ['x' => 'Q4 2021', 'y' => 25.0, 'n' => 190, 'base' => 623],
                            ['x' => 'Q1 2022', 'y' => 28.0, 'n' => 212, 'base' => 613],
                            ['x' => 'Q2 2022', 'y' => 25.0, 'n' => 193, 'base' => 620],
                            ['x' => 'Q3 2022', 'y' => 28.0, 'n' => 212, 'base' => 589],
                            ['x' => 'Q4 2022', 'y' => 24.0, 'n' => 183, 'base' => 595],
                            ['x' => 'Q1 2023', 'y' => 29.0, 'n' => 224, 'base' => 583],
                            ['x' => 'Q2 2023', 'y' => 28.0, 'n' => 217, 'base' => 598],
                            ['x' => 'Q3 2023', 'y' => 28.0, 'n' => 218, 'base' => 599],
                            ['x' => 'Q4 2023', 'y' => 24.0, 'n' => 186, 'base' => 575],
                            ['x' => 'Q1 2024', 'y' => 39.0, 'n' => 234, 'base' => 598],
                            ['x' => 'Q2 2024', 'y' => 39.0, 'n' => 223, 'base' => 571],
                            ['x' => 'Q3 2024', 'y' => 37.0, 'n' => 225, 'base' => 602],
                            ['x' => 'Q4 2024', 'y' => 38.0, 'n' => 219, 'base' => 582],
                            ['x' => 'Q1 2025', 'y' => 40.0, 'n' => 224, 'base' => 575],
                            ['x' => 'Q2 2025', 'y' => 38.0, 'n' => 216, 'base' => 572],
                            ['x' => 'Q3 2025', 'y' => 37.0, 'n' => 215, 'base' => 574],
                        ],
                    ],
                    // Rügenwalder Mühle
                    [
                        'id' => 4,
                        'label' => 'Rügenwalder Mühle',
                        'borderColor' => '#4132ab',
                        'data' => [
                            ['x' => 'Q1 2020', 'y' => 39.0, 'n' => 293, 'base' => 600],
                            ['x' => 'Q2 2020', 'y' => 36.0, 'n' => 267, 'base' => 588],
                            ['x' => 'Q3 2020', 'y' => 37.0, 'n' => 281, 'base' => 619],
                            ['x' => 'Q4 2020', 'y' => 36.0, 'n' => 272, 'base' => 584],
                            ['x' => 'Q1 2021', 'y' => 42.0, 'n' => 317, 'base' => 596],
                            ['x' => 'Q2 2021', 'y' => 38.0, 'n' => 284, 'base' => 590],
                            ['x' => 'Q3 2021', 'y' => 37.0, 'n' => 285, 'base' => 610],
                            ['x' => 'Q4 2021', 'y' => 37.0, 'n' => 281, 'base' => 614],
                            ['x' => 'Q1 2022', 'y' => 37.0, 'n' => 286, 'base' => 599],
                            ['x' => 'Q2 2022', 'y' => 41.0, 'n' => 312, 'base' => 630],
                            ['x' => 'Q3 2022', 'y' => 38.0, 'n' => 292, 'base' => 600],
                            ['x' => 'Q4 2022', 'y' => 38.0, 'n' => 288, 'base' => 589],
                            ['x' => 'Q1 2023', 'y' => 34.0, 'n' => 258, 'base' => 576],
                            ['x' => 'Q2 2023', 'y' => 35.0, 'n' => 270, 'base' => 559],
                            ['x' => 'Q3 2023', 'y' => 37.0, 'n' => 283, 'base' => 568],
                            ['x' => 'Q4 2023', 'y' => 36.0, 'n' => 273, 'base' => 555],
                            ['x' => 'Q1 2024', 'y' => 51.0, 'n' => 289, 'base' => 571],
                            ['x' => 'Q2 2024', 'y' => 54.0, 'n' => 293, 'base' => 540],
                            ['x' => 'Q3 2024', 'y' => 48.0, 'n' => 278, 'base' => 580],
                            ['x' => 'Q4 2024', 'y' => 52.0, 'n' => 282, 'base' => 540],
                            ['x' => 'Q1 2025', 'y' => 51.0, 'n' => 281, 'base' => 546],
                            ['x' => 'Q2 2025', 'y' => 49.0, 'n' => 255, 'base' => 520],
                            ['x' => 'Q3 2025', 'y' => 53.0, 'n' => 277, 'base' => 520],
                        ],
                    ],
                ],
            ],
        ];
    }

public function getRejectionValue(): array
    {
        $labels = [
            'Q1 2020',
            'Q2 2020',
            'Q3 2020',
            'Q4 2020',
            'Q1 2021',
            'Q2 2021',
            'Q3 2021',
            'Q4 2021',
            'Q1 2022',
            'Q2 2022',
            'Q3 2022',
            'Q4 2022',
            'Q1 2023',
            'Q2 2023',
            'Q3 2023',
            'Q4 2023',
            'Q1 2024',
            'Q2 2024',
            'Q3 2024',
            'Q4 2024',
            'Q1 2025',
            'Q2 2025',
        ];

$brandData = [
            'Herta' => [
                'color' => '#cc0404',
                'base' => [752, 751, 751, 752, 753, 751, 765, 769, 769, 770, 764, 767, 768, 770, 765, 761, 754, 750, 750, 750, 750, 755],
                'n' => [26, 29, 43, 46, 22, 38, 26, 32, 27, 35, 40, 31, 32, 21, 23, 38, 28, 16, 27, 18, 23, 25],
            ],
            'Herta Finesse' => [
                'color' => '#b85f22',
                'base' => [752, 751, 751, 752, 753, 751, 765, 769, 769, 770, 764, 767, 768, 770, 765, 761, 754, 750, 750, 750, 750, 755],
                'n' => [27, 28, 36, 33, 19, 30, 29, 32, 20, 28, 33, 20, 24, 24, 24, 33, 20, 28, 25, 22, 23, 11],
            ],
            'Gutfried' => [
                'color' => '#329bab',
                'base' => [752, 751, 751, 752, 753, 751, 765, 769, 769, 770, 764, 767, 768, 770, 765, 761, 754, 750, 750, 750, 750, 755],
                'n' => [46, 48, 52, 63, 42, 53, 41, 52, 45, 43, 44, 49, 48, 37, 44, 49, 33, 33, 29, 35, 24, 24],
            ],
            'Rüegenwalder Mühle' => [
                'color' => '#4132ab',
                'base' => [752, 751, 751, 752, 753, 751, 765, 769, 769, 770, 764, 767, 768, 770, 765, 761, 754, 750, 750, 750, 750, 755],
                'n' => [26, 29, 43, 46, 18, 30, 24, 33, 23, 29, 30, 24, 23, 17, 27, 15, 28, 16, 27, 18, 19, 13],
            ],
        ];

$datasets = [];
        $id = 1;

foreach ($brandData as $label => $info) {
            $dataPoints = [];

foreach ($labels as $index => $quarter) {
                $base = $info['base'][$index];
                $n = $info['n'][$index];
                $percent = $base > 0 ? round(($n / $base) * 100, 1) : 0;

$dataPoints[] = [
                    'x' => $quarter,
                    'y' => $percent,
                    'n' => $n,
                    'base' => $base,
                ];
            }

$datasets[] = [
                'id' => $id++,
                'label' => $label,
                'borderColor' => $info['color'],
                'data' => $dataPoints,
            ];
        }

return [
            'title' => 'Rejection in %',
            'totalN' => array_sum(array_map(function ($b) {
                return array_sum($b['n']);
            }, $brandData)),
            'data' => [
                'labels' => $labels,
                'datasets' => $datasets,
            ],
        ];
    }

public function getStatements(Collection $brands): array
    {
        $result = [];
        $attribute_statements = [
            1 => 'ist eine innovative Marke',
            //            2 => 'verwendet qualitativ hochwertige Zutaten',
            3 => 'Ist eine Marke, für die ich mehr ausgeben würde',
            4 => 'bietet ein gutes Preis-/ Leistungsverhältnis',
            5 => 'ist eine Marke, der ich vertraue',
            //            6 => 'ist eine Marke, der ich mich nahe fühle',
            7 => 'handelt gesellschaftlich verantwortungsvoll',
            //            8 => 'bietet klare und verständliche Informationen über Inhalte und Nährwerte',
            9 => 'unterstützt einen aktiven Lebensstil',
            10 => 'steht für eine gesunde und ausgewogene Ernährung',
            //            11 => 'bietet Produkte, die für mein persönliches Wohlbefinden gut sind',
            //            12 => 'stellt gesunde und ernährungsphysiologisch wertvolle Produkte her',
            //            13 => 'steigert mit seinen Produkten die Lebensqualität der Menschen',
            14 => 'ist eine Marke, die gut zu meiner alltäglichen Ernährung passt',
            //            15 => 'ist eine moderne Marke',
            16 => 'bietet eine optimale Kombination von Leichtigkeit und herzhaftem Geschmack',
            17 => 'bietet Produkte von hoher Qualität',
            18 => 'diese Marke unterscheidet sich deutlich von anderen Marken',
            19 => 'wenn diese Marke plötzlich vom Markt genommen würde, würde ich das sehr bedauern',
            //            20 => "bietet Produkte für jede Gelegenheit",
            //            21 => 'Ist einfach unwiderstehlich',
            22 => 'schmeckt besser als andere Wurstmarken',
            //            23 => 'die Marke steht für besonders appetitlich aussehende Produkte',
            24 => 'ist natürlich ohne künstliche Zutaten',
            25 => 'bietet fettarme Produkte',
            //            26 => "hilft, die wertvollen Momente im Alltag zu geniessen",
            27 => 'bietet Produkte aus der Region',
            //            28 => 'ist eine Marke, die auch zu vegetarischen Produkten passt',
            //            29 => "ist eine Marke für die kleinen Freuden im Alltag",
            30 => 'ist eine Marke, die ich gerne verwende',
            31 => "bietet Produkte, die sowohl einfach, richtig und lecker sind",
            32 => 'bietet Produkte zum Snacken für unterwegs',
            //            33 => "bietet Produkte, die mir die Zubereitung von Mahlzeiten erleichtern",
            34 => 'berücksichtigt das Tierwohl bei der Erzeugung',
            //            35 => 'ist eine Marke, die mehr Auswahl bietet als andere Marken',
            //            36 => 'bietet Produkte, die einfach richtig lecker sind',
            37 => 'bietet Verpackungen an, die weniger Plastik enthalten',
            38 => 'bietet Produkte mit Fleisch aus Deutschland an',
        ];

$data = require_once base_path('app/Services/data/statements.php');
        $quarters = array_keys($data[1]['Base']);

$currentQuarter = $quarters[array_key_last($quarters)];
        $lastQuarter = $quarters[array_key_last($quarters) - 1];

$result['totalN'] = $data[1]['Base'][$currentQuarter];
        $result['quarterLabels'] = [
            'last' => $lastQuarter,
            'current' => $currentQuarter,
        ];
        foreach ($attribute_statements as $id => $text) {
            $statement = [
                'id' => $id,
                'text' => $text,
            ];
            foreach ($brands as $brand) {
                if (! isset($data[$brand->id])) {
                    continue;
                }
                if (! isset($data[$brand->id]['statements'][$id])) {
                    continue;
                }
                $brandData = $data[$brand->id];

if ($brandData['statements'][$id][$currentQuarter] == 0 && $brandData['statements'][$id][$lastQuarter] == 0) {
                    continue;
                }

$totalN = $brandData['Base'][$currentQuarter];
                $value = $brandData['statements'][$id][$currentQuarter];
                $statement['values']['current'][] = [
                    'brand' => $brand,
                    'n' => round($totalN * $value / 100),
                    'label' => $currentQuarter,
                    'totalN' => $totalN,
                    'value' => round($value),
                ];

$totalN = $brandData['Base'][$lastQuarter];
                $value = $brandData['statements'][$id][$lastQuarter];
                $statement['values']['last'][] = [
                    'brand' => $brand,
                    'n' => round($totalN * $value / 100),
                    'label' => $lastQuarter,
                    'totalN' => $totalN,
                    'value' => round($value),
                ];
            }
            if (
                ! isset($statement['values']['current']) ||
                ! isset($statement['values']['last'])
            ) {
                continue;
            }
            $result['data'][] = $statement;
        }

return $result;
    }
}