|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
|
|
4
|
|
|
namespace App\Src\UseCases\Infra\Sql; |
|
5
|
|
|
|
|
6
|
|
|
|
|
7
|
|
|
use App\Src\UseCases\Infra\Sql\Model\CharacteristicsModel; |
|
8
|
|
|
use App\Src\UseCases\Infra\Sql\Model\InteractionModel; |
|
9
|
|
|
|
|
10
|
|
|
class ReportingCharacteristicSql |
|
11
|
|
|
{ |
|
12
|
|
|
public function getStatsByDepartment(int $pageId, string $type = 'follow') |
|
13
|
|
|
{ |
|
14
|
|
|
$interactions = InteractionModel::query() |
|
15
|
|
|
->selectRaw('count(*) AS count, contexts.department_number as department_number') |
|
16
|
|
|
->join('users', 'users.id', 'interactions.user_id') |
|
17
|
|
|
->join('contexts', 'users.context_id', 'contexts.id') |
|
18
|
|
|
->where(function ($query) use ($type){ |
|
19
|
|
|
$query->when($type === 'follow', function ($query) { |
|
20
|
|
|
$query->where('follow', true); |
|
21
|
|
|
$query->orWhere('done', true); |
|
22
|
|
|
}) |
|
23
|
|
|
->when($type === 'do', function ($query) { |
|
24
|
|
|
$query->where('done', true); |
|
25
|
|
|
}); |
|
26
|
|
|
}) |
|
27
|
|
|
->where('interactions.page_id', $pageId) |
|
28
|
|
|
->whereNotNull('interactions.user_id') |
|
29
|
|
|
->groupBy('department_number') |
|
30
|
|
|
->get(); |
|
31
|
|
|
|
|
32
|
|
|
$interactionsToReturn = []; |
|
33
|
|
|
foreach($interactions as $interaction){ |
|
34
|
|
|
$characteristicsModel = CharacteristicsModel::query()->where('code', $interaction->department_number)->first(); |
|
35
|
|
|
if(!isset($characteristicsModel)){ |
|
36
|
|
|
continue; |
|
37
|
|
|
} |
|
38
|
|
|
$characteristicsModel->icon = route('api.icon.serve', ['id' => $characteristicsModel->uuid]); |
|
39
|
|
|
$interaction->departmentData = $characteristicsModel; |
|
40
|
|
|
$interactionsToReturn[] = $interaction->toArray(); |
|
41
|
|
|
} |
|
42
|
|
|
return $interactionsToReturn; |
|
43
|
|
|
} |
|
44
|
|
|
|
|
45
|
|
|
public function getCharacteristicsByUserPage(int $pageId, string $type = 'follow', string $characteristicType = null) |
|
46
|
|
|
{ |
|
47
|
|
|
$characteristicsCount = InteractionModel::query() |
|
48
|
|
|
->selectRaw(' |
|
49
|
|
|
count(*) as count, |
|
50
|
|
|
user_characteristics.characteristic_id |
|
51
|
|
|
') |
|
52
|
|
|
->join('users', 'users.id', 'interactions.user_id') |
|
53
|
|
|
->join('contexts', 'users.context_id', 'contexts.id') |
|
54
|
|
|
->join('user_characteristics', 'user_characteristics.user_id', 'users.id') |
|
55
|
|
|
->join('characteristics', 'characteristics.id', 'user_characteristics.characteristic_id') |
|
56
|
|
|
->where(function ($query) use ($type){ |
|
57
|
|
|
$query->when($type === 'follow', function ($query) { |
|
58
|
|
|
$query->where('follow', true); |
|
59
|
|
|
$query->orWhere('done', true); |
|
60
|
|
|
}) |
|
61
|
|
|
->when($type === 'do', function ($query) { |
|
62
|
|
|
$query->where('done', true); |
|
63
|
|
|
}); |
|
64
|
|
|
}) |
|
65
|
|
|
->where('interactions.page_id', $pageId) |
|
66
|
|
|
->whereNotNull('interactions.user_id') |
|
67
|
|
|
->where('characteristics.type', $characteristicType) |
|
68
|
|
|
->groupBy('user_characteristics.characteristic_id') |
|
69
|
|
|
->orderBy('count', 'desc') |
|
70
|
|
|
->limit(10) |
|
71
|
|
|
->get(); |
|
72
|
|
|
|
|
73
|
|
|
$characteristicsToReturn = []; |
|
74
|
|
|
foreach($characteristicsCount as $characteristicCount){ |
|
75
|
|
|
$characteristic = CharacteristicsModel::query()->find($characteristicCount->characteristic_id); |
|
76
|
|
|
$characteristic->count = $characteristicCount->count; |
|
|
|
|
|
|
77
|
|
|
$c = $characteristic->toArray(); |
|
78
|
|
|
$c['pretty_page_label'] = str_replace('Catégorie:', '', $c['pretty_page_label']); |
|
79
|
|
|
$c['icon'] = route('api.icon.serve', ['id' => $c['uuid']]); |
|
80
|
|
|
$characteristicsToReturn[] = $c; |
|
81
|
|
|
} |
|
82
|
|
|
return $characteristicsToReturn; |
|
83
|
|
|
} |
|
84
|
|
|
} |
|
85
|
|
|
|
neayi /
insights
Checks if undeclared accessed properties appear in database migrations and if the creating migration is correct.