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
<?php
/**
* Class AnalyticsPodcastsByEpisodeModel
* Model for analytics_podcasts_by_episodes table in database
* @copyright 2020 Podlibre
* @license https://www.gnu.org/licenses/agpl-3.0.en.html AGPL3
* @link https://castopod.org/
*/
namespace App\Models;
use CodeIgniter\Model;
class AnalyticsPodcastsByEpisodeModel extends Model
{
protected $table = 'analytics_podcasts_by_episode';
protected $allowedFields = [];
protected $returnType = \App\Entities\AnalyticsPodcastsByEpisode::class;
protected $useSoftDeletes = false;
protected $useTimestamps = false;
/**
* @param int $podcastId, $episodeId
*
* @return array
*/
public function getDataByDay(int $podcastId, int $episodeId = null): array
{
if (!$episodeId) {
if (
!($found = cache(
"{$podcastId}_analytics_podcast_by_episode_by_day"
))
) {
$lastEpisodes = (new EpisodeModel())
->select('id, season_number, number, title')
->orderBy('id', 'DESC')
->where(['podcast_id' => $podcastId])
->findAll(5);
$found = $this->select('age AS X');
$letter = 97;
foreach ($lastEpisodes as $episode) {
$found = $found
->selectSum(
'(CASE WHEN `episode_id`=' .
$episode->id .
' THEN `hits` END)',
chr($letter) . 'Y'
)
->select(
'"' .
(empty($episode->season_number)
? ''
: $episode->season_number) .
(empty($episode->number)
? ''
: '-' . $episode->number . '/ ') .
$episode->title .
'" AS ' .
chr($letter) .
'Value'
);
$letter++;
}
$found = $found
->where([
'podcast_id' => $podcastId,
'age <' => 60,
])
->groupBy('X')
->orderBy('X', 'ASC')
->findAll();
cache()->save(
"{$podcastId}_analytics_podcast_by_episode_by_day",
$found,
14400
);
}
return $found;
} else {
if (
!($found = cache(
"{$podcastId}_{$episodeId}_analytics_podcast_by_episode_by_day"
))
) {
$found = $this->select('date as labels')
->selectSum('hits', 'values')
->where([
'episode_id' => $episodeId,
'podcast_id' => $podcastId,
])
->groupBy('labels')
->orderBy('labels', 'ASC')
->findAll();
cache()->save(
"{$podcastId}_{$episodeId}_analytics_podcast_by_episode_by_day",
$found,
14400
);
}
return $found;
}
}
}