Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
<?php
namespace App\Services;
use App\Models\Taxe;
use Illuminate\Support\Facades\DB;
use InvalidArgumentException;
class TaxeStatService
{
/**
* Calculate sum for a specific tax field with optional filters.
*/
public function calculateSum(string $field, ?string $departmentId = null, ?int $year = null): float
{
$column = $this->validateAndGetColumn($field);
$query = $this->buildBaseQuery($departmentId, $year);
return $query->sum($column);
}
/**
* Calculate average for a specific tax field with optional filters.
*/
public function calculateAverage(string $field, ?string $departmentId = null, ?int $year = null): float
{
$column = $this->validateAndGetColumn($field);
$query = $this->buildBaseQuery($departmentId, $year);
return round($query->avg($column), 2);
}
/**
* Get statistics grouped by location (department or region).
*/
public function getStatsByLocation(string $groupBy = 'department', ?int $year = null): array
{
// Validate groupBy parameter (whitelist approach)
if (!in_array($groupBy, ['department', 'region'], true)) {
throw new InvalidArgumentException('Le paramètre group_by doit être "department" ou "region".');
}
$query = Taxe::query();
// Setup grouping
if ($groupBy === 'region') {
$query->join('departments', 'taxes.department_id', '=', 'departments.department_id');
$query->groupBy('departments.region_name');
$query->select('departments.region_name as location');
} else {
$query->groupBy('taxes.department_id');
$query->select('taxes.department_id as location');
}
// Apply year filter if provided
if ($year !== null) {
$query->where('taxes.year', $year);
}
// Build aggregate selects
$sqlSelects = $this->buildAggregateSelects();
$query->addSelect(DB::raw(implode(', ', $sqlSelects)));
return $query->get()->toArray();
}
/**
* Validate field and return the corresponding column name.
*/
private function validateAndGetColumn(string $field): string
{
if (!in_array($field, Taxe::ALLOWED_STAT_FIELDS)) {
throw new InvalidArgumentException('Champ invalide');
}
return $field . '_amount';
}
/**
* Build base query with optional filters.
*/
private function buildBaseQuery(?string $departmentId, ?int $year)
{
$query = Taxe::query();
if ($departmentId !== null) {
$query->where('department_id', $departmentId);
}
if ($year !== null) {
$query->where('year', $year);
}
return $query;
}
/**
* Build aggregate SELECT statements for stats query.
*/
private function buildAggregateSelects(): array
{
$sqlSelects = [];
// Aggregate amount fields
foreach (Taxe::AMOUNT_FIELDS as $col => $alias) {
$sqlSelects[] = "SUM({$col}) as {$alias}_total_amount";
$sqlSelects[] = "ROUND(AVG({$col}), 2) as {$alias}_avg_amount";
}
// Aggregate percentage fields
foreach (Taxe::PERCENTAGE_FIELDS as $col => $alias) {
$sqlSelects[] = "ROUND(AVG({$col}), 2) as {$alias}_avg_rate";
}
return $sqlSelects;
}
}