|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Blacklight; |
|
4
|
|
|
|
|
5
|
|
|
use App\Models\Group; |
|
6
|
|
|
use App\Models\Release; |
|
7
|
|
|
use App\Models\Category; |
|
8
|
|
|
use App\Models\Settings; |
|
9
|
|
|
use Chumper\Zipper\Zipper; |
|
10
|
|
|
use Illuminate\Support\Arr; |
|
11
|
|
|
use Blacklight\utility\Utility; |
|
12
|
|
|
use Illuminate\Support\Facades\DB; |
|
13
|
|
|
use Illuminate\Support\Facades\File; |
|
14
|
|
|
use Illuminate\Support\Facades\Cache; |
|
15
|
|
|
|
|
16
|
|
|
/** |
|
17
|
|
|
* Class Releases. |
|
18
|
|
|
*/ |
|
19
|
|
|
class Releases |
|
20
|
|
|
{ |
|
21
|
|
|
// RAR/ZIP Passworded indicator. |
|
22
|
|
|
public const PASSWD_NONE = 0; // No password. |
|
23
|
|
|
public const PASSWD_POTENTIAL = 1; // Might have a password. |
|
24
|
|
|
public const BAD_FILE = 2; // Possibly broken RAR/ZIP. |
|
25
|
|
|
public const PASSWD_RAR = 10; // Definitely passworded. |
|
26
|
|
|
|
|
27
|
|
|
/** |
|
28
|
|
|
* @var \Blacklight\SphinxSearch |
|
29
|
|
|
*/ |
|
30
|
|
|
public $sphinxSearch; |
|
31
|
|
|
|
|
32
|
|
|
/** |
|
33
|
|
|
* @var int |
|
34
|
|
|
*/ |
|
35
|
|
|
public $passwordStatus; |
|
36
|
|
|
|
|
37
|
|
|
/** |
|
38
|
|
|
* @var array Class instances. |
|
39
|
|
|
* @throws \Exception |
|
40
|
|
|
*/ |
|
41
|
|
|
public function __construct(array $options = []) |
|
42
|
|
|
{ |
|
43
|
|
|
$defaults = [ |
|
44
|
|
|
'Settings' => null, |
|
45
|
|
|
'Groups' => null, |
|
46
|
|
|
]; |
|
47
|
|
|
$options += $defaults; |
|
48
|
|
|
|
|
49
|
|
|
$this->sphinxSearch = new SphinxSearch(); |
|
50
|
|
|
} |
|
51
|
|
|
|
|
52
|
|
|
/** |
|
53
|
|
|
* Used for Browse results. |
|
54
|
|
|
* |
|
55
|
|
|
* |
|
56
|
|
|
* @param $page |
|
57
|
|
|
* @param $cat |
|
58
|
|
|
* @param $start |
|
59
|
|
|
* @param $num |
|
60
|
|
|
* @param $orderBy |
|
61
|
|
|
* @param int $maxAge |
|
62
|
|
|
* @param array $excludedCats |
|
63
|
|
|
* @param array $tags |
|
64
|
|
|
* @param int $groupName |
|
65
|
|
|
* @param int $minSize |
|
66
|
|
|
* |
|
67
|
|
|
* @return \Illuminate\Database\Eloquent\Collection|mixed |
|
68
|
|
|
*/ |
|
69
|
|
|
public function getBrowseRange($page, $cat, $start, $num, $orderBy, $maxAge = -1, array $excludedCats = [], $groupName = -1, $minSize = 0, array $tags = []) |
|
70
|
|
|
{ |
|
71
|
|
|
$orderBy = $this->getBrowseOrder($orderBy); |
|
72
|
|
|
|
|
73
|
|
|
$qry = sprintf( |
|
74
|
|
|
"SELECT r.*, cp.title AS parent_category, c.title AS sub_category, |
|
75
|
|
|
CONCAT(cp.title, ' > ', c.title) AS category_name, |
|
76
|
|
|
CONCAT(cp.id, ',', c.id) AS category_ids, |
|
77
|
|
|
df.failed AS failed, |
|
78
|
|
|
rn.releases_id AS nfoid, |
|
79
|
|
|
re.releases_id AS reid, |
|
80
|
|
|
v.tvdb, v.trakt, v.tvrage, v.tvmaze, v.imdb, v.tmdb, |
|
81
|
|
|
tve.title, tve.firstaired |
|
82
|
|
|
FROM |
|
83
|
|
|
( |
|
84
|
|
|
SELECT r.*, g.name AS group_name |
|
85
|
|
|
FROM releases r |
|
86
|
|
|
LEFT JOIN groups g ON g.id = r.groups_id |
|
87
|
|
|
%s |
|
88
|
|
|
WHERE r.nzbstatus = %d |
|
89
|
|
|
AND r.passwordstatus %s |
|
90
|
|
|
%s %s %s %s %s %s |
|
91
|
|
|
ORDER BY %s %s %s |
|
92
|
|
|
) r |
|
93
|
|
|
LEFT JOIN categories c ON c.id = r.categories_id |
|
94
|
|
|
LEFT JOIN categories cp ON cp.id = c.parentid |
|
95
|
|
|
LEFT OUTER JOIN videos v ON r.videos_id = v.id |
|
96
|
|
|
LEFT OUTER JOIN tv_episodes tve ON r.tv_episodes_id = tve.id |
|
97
|
|
|
LEFT OUTER JOIN video_data re ON re.releases_id = r.id |
|
98
|
|
|
LEFT OUTER JOIN release_nfos rn ON rn.releases_id = r.id |
|
99
|
|
|
LEFT OUTER JOIN dnzb_failures df ON df.release_id = r.id |
|
100
|
|
|
GROUP BY r.id |
|
101
|
|
|
ORDER BY %10\$s %11\$s", |
|
102
|
|
|
! empty($tags) ? ' LEFT JOIN tagging_tagged tt ON tt.taggable_id = r.id' : '', |
|
103
|
|
|
NZB::NZB_ADDED, |
|
104
|
|
|
$this->showPasswords(), |
|
105
|
|
|
! empty($tags) ? " AND tt.tag_name IN ('".implode("','", $tags)."')" : '', |
|
106
|
|
|
Category::getCategorySearch($cat), |
|
107
|
|
|
($maxAge > 0 ? (' AND postdate > NOW() - INTERVAL '.$maxAge.' DAY ') : ''), |
|
108
|
|
|
(\count($excludedCats) ? (' AND r.categories_id NOT IN ('.implode(',', $excludedCats).')') : ''), |
|
109
|
|
|
((int) $groupName !== -1 ? sprintf(' AND g.name = %s ', escapeString($groupName)) : ''), |
|
110
|
|
|
($minSize > 0 ? sprintf('AND r.size >= %d', $minSize) : ''), |
|
111
|
|
|
$orderBy[0], |
|
112
|
|
|
$orderBy[1], |
|
113
|
|
|
($start === false ? '' : ' LIMIT '.$num.' OFFSET '.$start) |
|
114
|
|
|
); |
|
115
|
|
|
|
|
116
|
|
|
$releases = Cache::get(md5($qry.$page)); |
|
117
|
|
|
if ($releases !== null) { |
|
118
|
|
|
return $releases; |
|
119
|
|
|
} |
|
120
|
|
|
$sql = Release::fromQuery($qry); |
|
121
|
|
|
if (\count($sql) > 0) { |
|
122
|
|
|
$possibleRows = $this->getBrowseCount($cat, $maxAge, $excludedCats, $groupName, $tags); |
|
123
|
|
|
$sql[0]->_totalcount = $sql[0]->_totalrows = $possibleRows; |
|
124
|
|
|
} |
|
125
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_medium')); |
|
126
|
|
|
Cache::put(md5($qry.$page), $sql, $expiresAt); |
|
127
|
|
|
|
|
128
|
|
|
return $sql; |
|
129
|
|
|
} |
|
130
|
|
|
|
|
131
|
|
|
/** |
|
132
|
|
|
* Used for pager on browse page. |
|
133
|
|
|
* |
|
134
|
|
|
* @param array $cat |
|
135
|
|
|
* @param int $maxAge |
|
136
|
|
|
* @param array $excludedCats |
|
137
|
|
|
* @param string|int $groupName |
|
138
|
|
|
* |
|
139
|
|
|
* @param array $tags |
|
140
|
|
|
* |
|
141
|
|
|
* @return int |
|
142
|
|
|
*/ |
|
143
|
|
|
public function getBrowseCount($cat, $maxAge = -1, array $excludedCats = [], $groupName = '', array $tags = []): int |
|
144
|
|
|
{ |
|
145
|
|
|
$sql = sprintf( |
|
146
|
|
|
'SELECT COUNT(r.id) AS count |
|
147
|
|
|
FROM releases r |
|
148
|
|
|
%s %s |
|
149
|
|
|
WHERE r.nzbstatus = %d |
|
150
|
|
|
AND r.passwordstatus %s |
|
151
|
|
|
%s |
|
152
|
|
|
%s %s %s %s ', |
|
153
|
|
|
($groupName !== -1 ? 'LEFT JOIN groups g ON g.id = r.groups_id' : ''), |
|
154
|
|
|
! empty($tags) ? ' LEFT JOIN tagging_tagged tt ON tt.taggable_id = r.id' : '', |
|
155
|
|
|
NZB::NZB_ADDED, |
|
156
|
|
|
$this->showPasswords(), |
|
157
|
|
|
($groupName !== -1 ? sprintf(' AND g.name = %s', escapeString($groupName)) : ''), |
|
158
|
|
|
! empty($tags) ? ' AND tt.tag_name IN ('.escapeString(implode(',', $tags)).')' : '', |
|
159
|
|
|
Category::getCategorySearch($cat), |
|
160
|
|
|
($maxAge > 0 ? (' AND r.postdate > NOW() - INTERVAL '.$maxAge.' DAY ') : ''), |
|
161
|
|
|
(\count($excludedCats) ? (' AND r.categories_id NOT IN ('.implode(',', $excludedCats).')') : '') |
|
162
|
|
|
); |
|
163
|
|
|
$count = Cache::get(md5($sql)); |
|
164
|
|
|
if ($count !== null) { |
|
165
|
|
|
return $count; |
|
166
|
|
|
} |
|
167
|
|
|
$count = Release::fromQuery($sql); |
|
168
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_short')); |
|
169
|
|
|
Cache::put(md5($sql), $count[0]->count, $expiresAt); |
|
170
|
|
|
|
|
171
|
|
|
return $count[0]->count ?? 0; |
|
172
|
|
|
} |
|
173
|
|
|
|
|
174
|
|
|
/** |
|
175
|
|
|
* @return string |
|
176
|
|
|
*/ |
|
177
|
|
|
public function showPasswords() |
|
178
|
|
|
{ |
|
179
|
|
|
$setting = (int) Settings::settingValue('..showpasswordedrelease'); |
|
180
|
|
|
$setting = $setting ?? 10; |
|
181
|
|
|
switch ($setting) { |
|
182
|
|
|
case 0: // Hide releases with a password or a potential password (Hide unprocessed releases). |
|
|
|
|
|
|
183
|
|
|
|
|
184
|
|
|
return '= '.self::PASSWD_NONE; |
|
185
|
|
|
case 1: // Show releases with no password or a potential password (Show unprocessed releases). |
|
|
|
|
|
|
186
|
|
|
|
|
187
|
|
|
return '<= '.self::PASSWD_POTENTIAL; |
|
188
|
|
|
case 2: // Hide releases with a password or a potential password (Show unprocessed releases). |
|
|
|
|
|
|
189
|
|
|
return '<= '.self::PASSWD_NONE; |
|
190
|
|
|
case 10: // Shows everything. |
|
|
|
|
|
|
191
|
|
|
default: |
|
192
|
|
|
return '<= '.self::PASSWD_RAR; |
|
193
|
|
|
} |
|
194
|
|
|
} |
|
195
|
|
|
|
|
196
|
|
|
/** |
|
197
|
|
|
* Use to order releases on site. |
|
198
|
|
|
* |
|
199
|
|
|
* @param string|array $orderBy |
|
200
|
|
|
* |
|
201
|
|
|
* @return array |
|
202
|
|
|
*/ |
|
203
|
|
|
public function getBrowseOrder($orderBy): array |
|
204
|
|
|
{ |
|
205
|
|
|
$orderArr = explode('_', ($orderBy === '' ? 'posted_desc' : $orderBy)); |
|
|
|
|
|
|
206
|
|
|
switch ($orderArr[0]) { |
|
207
|
|
|
case 'cat': |
|
208
|
|
|
$orderField = 'categories_id'; |
|
209
|
|
|
break; |
|
210
|
|
|
case 'name': |
|
211
|
|
|
$orderField = 'searchname'; |
|
212
|
|
|
break; |
|
213
|
|
|
case 'size': |
|
214
|
|
|
$orderField = 'size'; |
|
215
|
|
|
break; |
|
216
|
|
|
case 'files': |
|
217
|
|
|
$orderField = 'totalpart'; |
|
218
|
|
|
break; |
|
219
|
|
|
case 'stats': |
|
220
|
|
|
$orderField = 'grabs'; |
|
221
|
|
|
break; |
|
222
|
|
|
case 'posted': |
|
223
|
|
|
default: |
|
224
|
|
|
$orderField = 'postdate'; |
|
225
|
|
|
break; |
|
226
|
|
|
} |
|
227
|
|
|
|
|
228
|
|
|
return [$orderField, isset($orderArr[1]) && preg_match('/^(asc|desc)$/i', $orderArr[1]) ? $orderArr[1] : 'desc']; |
|
229
|
|
|
} |
|
230
|
|
|
|
|
231
|
|
|
/** |
|
232
|
|
|
* Return ordering types usable on site. |
|
233
|
|
|
* |
|
234
|
|
|
* @return string[] |
|
235
|
|
|
*/ |
|
236
|
|
|
public function getBrowseOrdering(): array |
|
237
|
|
|
{ |
|
238
|
|
|
return [ |
|
239
|
|
|
'name_asc', |
|
240
|
|
|
'name_desc', |
|
241
|
|
|
'cat_asc', |
|
242
|
|
|
'cat_desc', |
|
243
|
|
|
'posted_asc', |
|
244
|
|
|
'posted_desc', |
|
245
|
|
|
'size_asc', |
|
246
|
|
|
'size_desc', |
|
247
|
|
|
'files_asc', |
|
248
|
|
|
'files_desc', |
|
249
|
|
|
'stats_asc', |
|
250
|
|
|
'stats_desc', |
|
251
|
|
|
]; |
|
252
|
|
|
} |
|
253
|
|
|
|
|
254
|
|
|
/** |
|
255
|
|
|
* Get list of releases available for export. |
|
256
|
|
|
* |
|
257
|
|
|
* |
|
258
|
|
|
* @param string $postFrom |
|
259
|
|
|
* @param string $postTo |
|
260
|
|
|
* @param string $groupID |
|
261
|
|
|
* |
|
262
|
|
|
* @return \Illuminate\Database\Eloquent\Collection|\Illuminate\Support\Collection|static[] |
|
263
|
|
|
*/ |
|
264
|
|
|
public function getForExport($postFrom = '', $postTo = '', $groupID = '') |
|
265
|
|
|
{ |
|
266
|
|
|
$query = Release::query() |
|
267
|
|
|
->where('r.nzbstatus', NZB::NZB_ADDED) |
|
268
|
|
|
->select(['r.searchname', 'r.guid', 'g.name as gname', DB::raw("CONCAT(cp.title,'_',c.title) AS catName")]) |
|
269
|
|
|
->from('releases as r') |
|
270
|
|
|
->leftJoin('categories as c', 'c.id', '=', 'r.categories_id') |
|
271
|
|
|
->leftJoin('categories as cp', 'cp.id', '=', 'c.parentid') |
|
272
|
|
|
->leftJoin('groups as g', 'g.id', '=', 'r.groups_id'); |
|
273
|
|
|
|
|
274
|
|
|
if ($groupID !== '') { |
|
275
|
|
|
$query->where('r.groups_id', $groupID); |
|
276
|
|
|
} |
|
277
|
|
|
|
|
278
|
|
|
if ($postFrom !== '') { |
|
279
|
|
|
$dateParts = explode('/', $postFrom); |
|
280
|
|
|
if (\count($dateParts) === 3) { |
|
281
|
|
|
$query->where('r.postdate', '>', $dateParts[2].'-'.$dateParts[1].'-'.$dateParts[0].'00:00:00'); |
|
282
|
|
|
} |
|
283
|
|
|
} |
|
284
|
|
|
|
|
285
|
|
|
if ($postTo !== '') { |
|
286
|
|
|
$dateParts = explode('/', $postTo); |
|
287
|
|
|
if (\count($dateParts) === 3) { |
|
288
|
|
|
$query->where('r.postdate', '<', $dateParts[2].'-'.$dateParts[1].'-'.$dateParts[0].'23:59:59'); |
|
289
|
|
|
} |
|
290
|
|
|
} |
|
291
|
|
|
|
|
292
|
|
|
return $query->get(); |
|
293
|
|
|
} |
|
294
|
|
|
|
|
295
|
|
|
/** |
|
296
|
|
|
* Get date in this format : 01/01/2014 of the oldest release. |
|
297
|
|
|
* |
|
298
|
|
|
* @note Used for exporting NZB's. |
|
299
|
|
|
* @return mixed |
|
300
|
|
|
*/ |
|
301
|
|
|
public function getEarliestUsenetPostDate() |
|
302
|
|
|
{ |
|
303
|
|
|
$row = Release::query()->selectRaw("DATE_FORMAT(min(postdate), '%d/%m/%Y') AS postdate")->first(); |
|
304
|
|
|
|
|
305
|
|
|
return $row === null ? '01/01/2014' : $row['postdate']; |
|
306
|
|
|
} |
|
307
|
|
|
|
|
308
|
|
|
/** |
|
309
|
|
|
* Get date in this format : 01/01/2014 of the newest release. |
|
310
|
|
|
* |
|
311
|
|
|
* @note Used for exporting NZB's. |
|
312
|
|
|
* @return mixed |
|
313
|
|
|
*/ |
|
314
|
|
|
public function getLatestUsenetPostDate() |
|
315
|
|
|
{ |
|
316
|
|
|
$row = Release::query()->selectRaw("DATE_FORMAT(max(postdate), '%d/%m/%Y') AS postdate")->first(); |
|
317
|
|
|
|
|
318
|
|
|
return $row === null ? '01/01/2014' : $row['postdate']; |
|
319
|
|
|
} |
|
320
|
|
|
|
|
321
|
|
|
/** |
|
322
|
|
|
* Gets all groups for drop down selection on NZB-Export web page. |
|
323
|
|
|
* |
|
324
|
|
|
* @param bool $blnIncludeAll |
|
325
|
|
|
* |
|
326
|
|
|
* @note Used for exporting NZB's. |
|
327
|
|
|
* @return array |
|
328
|
|
|
*/ |
|
329
|
|
|
public function getReleasedGroupsForSelect($blnIncludeAll = true): array |
|
330
|
|
|
{ |
|
331
|
|
|
$groups = Release::query() |
|
332
|
|
|
->selectRaw('DISTINCT g.id, g.name') |
|
333
|
|
|
->leftJoin('groups as g', 'g.id', '=', 'releases.groups_id') |
|
334
|
|
|
->get(); |
|
335
|
|
|
$temp_array = []; |
|
336
|
|
|
|
|
337
|
|
|
if ($blnIncludeAll) { |
|
338
|
|
|
$temp_array[-1] = '--All Groups--'; |
|
339
|
|
|
} |
|
340
|
|
|
|
|
341
|
|
|
foreach ($groups as $group) { |
|
342
|
|
|
$temp_array[$group['id']] = $group['name']; |
|
343
|
|
|
} |
|
344
|
|
|
|
|
345
|
|
|
return $temp_array; |
|
346
|
|
|
} |
|
347
|
|
|
|
|
348
|
|
|
/** |
|
349
|
|
|
* Cache of concatenated category ID's used in queries. |
|
350
|
|
|
* @var null|array |
|
351
|
|
|
*/ |
|
352
|
|
|
private $concatenatedCategoryIDsCache = null; |
|
353
|
|
|
|
|
354
|
|
|
/** |
|
355
|
|
|
* Gets / sets a string of concatenated category ID's used in queries. |
|
356
|
|
|
* |
|
357
|
|
|
* @return array|null|string |
|
358
|
|
|
*/ |
|
359
|
|
|
public function getConcatenatedCategoryIDs() |
|
360
|
|
|
{ |
|
361
|
|
|
if ($this->concatenatedCategoryIDsCache === null) { |
|
362
|
|
|
$result = Category::query() |
|
363
|
|
|
->remember(config('nntmux.cache_expiry_long')) |
|
364
|
|
|
->whereNotNull('categories.parentid') |
|
365
|
|
|
->whereNotNull('cp.id') |
|
366
|
|
|
->selectRaw('CONCAT(cp.id, ", ", categories.id) AS category_ids') |
|
367
|
|
|
->leftJoin('categories as cp', 'cp.id', '=', 'categories.parentid') |
|
368
|
|
|
->get(); |
|
369
|
|
|
if (isset($result[0]['category_ids'])) { |
|
370
|
|
|
$this->concatenatedCategoryIDsCache = $result[0]['category_ids']; |
|
371
|
|
|
} |
|
372
|
|
|
} |
|
373
|
|
|
|
|
374
|
|
|
return $this->concatenatedCategoryIDsCache; |
|
375
|
|
|
} |
|
376
|
|
|
|
|
377
|
|
|
/** |
|
378
|
|
|
* Get TV for My Shows page. |
|
379
|
|
|
* |
|
380
|
|
|
* |
|
381
|
|
|
* @param $userShows |
|
382
|
|
|
* @param $offset |
|
383
|
|
|
* @param $limit |
|
384
|
|
|
* @param $orderBy |
|
385
|
|
|
* @param int $maxAge |
|
386
|
|
|
* @param array $excludedCats |
|
387
|
|
|
* @return \Illuminate\Database\Eloquent\Collection|mixed |
|
388
|
|
|
*/ |
|
389
|
|
|
public function getShowsRange($userShows, $offset, $limit, $orderBy, $maxAge = -1, array $excludedCats = []) |
|
390
|
|
|
{ |
|
391
|
|
|
$orderBy = $this->getBrowseOrder($orderBy); |
|
392
|
|
|
$sql = sprintf( |
|
393
|
|
|
"SELECT r.*, |
|
394
|
|
|
CONCAT(cp.title, '-', c.title) AS category_name, |
|
395
|
|
|
%s AS category_ids, |
|
396
|
|
|
groups.name AS group_name, |
|
397
|
|
|
rn.releases_id AS nfoid, re.releases_id AS reid, |
|
398
|
|
|
tve.firstaired, |
|
399
|
|
|
df.failed AS failed |
|
400
|
|
|
FROM releases r |
|
401
|
|
|
LEFT OUTER JOIN video_data re ON re.releases_id = r.id |
|
402
|
|
|
LEFT JOIN groups ON groups.id = r.groups_id |
|
403
|
|
|
LEFT OUTER JOIN release_nfos rn ON rn.releases_id = r.id |
|
404
|
|
|
LEFT OUTER JOIN tv_episodes tve ON tve.videos_id = r.videos_id |
|
405
|
|
|
LEFT JOIN categories c ON c.id = r.categories_id |
|
406
|
|
|
LEFT JOIN categories cp ON cp.id = c.parentid |
|
407
|
|
|
LEFT OUTER JOIN dnzb_failures df ON df.release_id = r.id |
|
408
|
|
|
WHERE %s %s |
|
409
|
|
|
AND r.nzbstatus = %d |
|
410
|
|
|
AND r.categories_id BETWEEN %d AND %d |
|
411
|
|
|
AND r.passwordstatus %s |
|
412
|
|
|
%s |
|
413
|
|
|
GROUP BY r.id |
|
414
|
|
|
ORDER BY %s %s %s", |
|
415
|
|
|
$this->getConcatenatedCategoryIDs(), |
|
|
|
|
|
|
416
|
|
|
$this->uSQL($userShows, 'videos_id'), |
|
417
|
|
|
(\count($excludedCats) ? ' AND r.categories_id NOT IN ('.implode(',', $excludedCats).')' : ''), |
|
418
|
|
|
NZB::NZB_ADDED, |
|
419
|
|
|
Category::TV_ROOT, |
|
420
|
|
|
Category::TV_OTHER, |
|
421
|
|
|
$this->showPasswords(), |
|
422
|
|
|
($maxAge > 0 ? sprintf(' AND r.postdate > NOW() - INTERVAL %d DAY ', $maxAge) : ''), |
|
423
|
|
|
$orderBy[0], |
|
424
|
|
|
$orderBy[1], |
|
425
|
|
|
($offset === false ? '' : (' LIMIT '.$limit.' OFFSET '.$offset)) |
|
426
|
|
|
); |
|
427
|
|
|
|
|
428
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_medium')); |
|
429
|
|
|
$result = Cache::get(md5($sql)); |
|
430
|
|
|
if ($result !== null) { |
|
431
|
|
|
return $result; |
|
432
|
|
|
} |
|
433
|
|
|
|
|
434
|
|
|
$result = Release::fromQuery($sql); |
|
435
|
|
|
Cache::put(md5($sql), $result, $expiresAt); |
|
436
|
|
|
|
|
437
|
|
|
return $result; |
|
438
|
|
|
} |
|
439
|
|
|
|
|
440
|
|
|
/** |
|
441
|
|
|
* Get count for my shows page pagination. |
|
442
|
|
|
* |
|
443
|
|
|
* @param $userShows |
|
444
|
|
|
* @param int $maxAge |
|
445
|
|
|
* @param array $excludedCats |
|
446
|
|
|
* |
|
447
|
|
|
* @return int |
|
448
|
|
|
*/ |
|
449
|
|
|
public function getShowsCount($userShows, $maxAge = -1, array $excludedCats = []): int |
|
450
|
|
|
{ |
|
451
|
|
|
return $this->getPagerCount( |
|
452
|
|
|
sprintf( |
|
453
|
|
|
'SELECT r.id |
|
454
|
|
|
FROM releases r |
|
455
|
|
|
WHERE %s %s |
|
456
|
|
|
AND r.nzbstatus = %d |
|
457
|
|
|
AND r.categories_id BETWEEN %d AND %d |
|
458
|
|
|
AND r.passwordstatus %s |
|
459
|
|
|
%s', |
|
460
|
|
|
$this->uSQL($userShows, 'videos_id'), |
|
461
|
|
|
(\count($excludedCats) ? ' AND r.categories_id NOT IN ('.implode(',', $excludedCats).')' : ''), |
|
462
|
|
|
NZB::NZB_ADDED, |
|
463
|
|
|
Category::TV_ROOT, |
|
464
|
|
|
Category::TV_OTHER, |
|
465
|
|
|
$this->showPasswords(), |
|
466
|
|
|
($maxAge > 0 ? sprintf(' AND r.postdate > NOW() - INTERVAL %d DAY ', $maxAge) : '') |
|
467
|
|
|
) |
|
468
|
|
|
); |
|
469
|
|
|
} |
|
470
|
|
|
|
|
471
|
|
|
/** |
|
472
|
|
|
* Delete multiple releases, or a single by ID. |
|
473
|
|
|
* |
|
474
|
|
|
* @param array|int|string $list Array of GUID or ID of releases to delete. |
|
475
|
|
|
* @throws \Exception |
|
476
|
|
|
*/ |
|
477
|
|
|
public function deleteMultiple($list): void |
|
478
|
|
|
{ |
|
479
|
|
|
$list = (array) $list; |
|
480
|
|
|
|
|
481
|
|
|
$nzb = new NZB(); |
|
482
|
|
|
$releaseImage = new ReleaseImage(); |
|
483
|
|
|
|
|
484
|
|
|
foreach ($list as $identifier) { |
|
485
|
|
|
$this->deleteSingle(['g' => $identifier, 'i' => false], $nzb, $releaseImage); |
|
486
|
|
|
} |
|
487
|
|
|
} |
|
488
|
|
|
|
|
489
|
|
|
/** |
|
490
|
|
|
* Deletes a single release by GUID, and all the corresponding files. |
|
491
|
|
|
* |
|
492
|
|
|
* @param array $identifiers ['g' => Release GUID(mandatory), 'id => ReleaseID(optional, pass |
|
493
|
|
|
* false)] |
|
494
|
|
|
* @param \Blacklight\NZB $nzb |
|
495
|
|
|
* @param \Blacklight\ReleaseImage $releaseImage |
|
496
|
|
|
* |
|
497
|
|
|
* @throws \Exception |
|
498
|
|
|
*/ |
|
499
|
|
|
public function deleteSingle($identifiers, NZB $nzb, ReleaseImage $releaseImage): void |
|
500
|
|
|
{ |
|
501
|
|
|
// Delete NZB from disk. |
|
502
|
|
|
$nzbPath = $nzb->NZBPath($identifiers['g']); |
|
503
|
|
|
if (! empty($nzbPath)) { |
|
504
|
|
|
File::delete($nzbPath); |
|
505
|
|
|
} |
|
506
|
|
|
|
|
507
|
|
|
// Delete images. |
|
508
|
|
|
$releaseImage->delete($identifiers['g']); |
|
509
|
|
|
|
|
510
|
|
|
// Delete from sphinx. |
|
511
|
|
|
$this->sphinxSearch->deleteRelease($identifiers); |
|
512
|
|
|
|
|
513
|
|
|
// Delete from DB. |
|
514
|
|
|
Release::whereGuid($identifiers['g'])->delete(); |
|
515
|
|
|
} |
|
516
|
|
|
|
|
517
|
|
|
/** |
|
518
|
|
|
* @param $guids |
|
519
|
|
|
* @param $category |
|
520
|
|
|
* @param $grabs |
|
521
|
|
|
* @param $videoId |
|
522
|
|
|
* @param $episodeId |
|
523
|
|
|
* @param $anidbId |
|
524
|
|
|
* @param $imdbId |
|
525
|
|
|
* @return bool|int |
|
526
|
|
|
*/ |
|
527
|
|
|
public function updateMulti($guids, $category, $grabs, $videoId, $episodeId, $anidbId, $imdbId) |
|
528
|
|
|
{ |
|
529
|
|
|
if (! \is_array($guids) || \count($guids) < 1) { |
|
530
|
|
|
return false; |
|
531
|
|
|
} |
|
532
|
|
|
|
|
533
|
|
|
$update = [ |
|
534
|
|
|
'categories_id' => $category === -1 ? 'categories_id' : $category, |
|
535
|
|
|
'grabs' => $grabs, |
|
536
|
|
|
'videos_id' => $videoId, |
|
537
|
|
|
'tv_episodes_id' => $episodeId, |
|
538
|
|
|
'anidbid' => $anidbId, |
|
539
|
|
|
'imdbid' => $imdbId, |
|
540
|
|
|
]; |
|
541
|
|
|
|
|
542
|
|
|
return Release::query()->whereIn('guid', $guids)->update($update); |
|
543
|
|
|
} |
|
544
|
|
|
|
|
545
|
|
|
/** |
|
546
|
|
|
* Creates part of a query for some functions. |
|
547
|
|
|
* |
|
548
|
|
|
* @param array|\Illuminate\Database\Eloquent\Collection $userQuery |
|
549
|
|
|
* @param string $type |
|
550
|
|
|
* |
|
551
|
|
|
* @return string |
|
552
|
|
|
*/ |
|
553
|
|
|
public function uSQL($userQuery, $type): string |
|
554
|
|
|
{ |
|
555
|
|
|
$sql = '(1=2 '; |
|
556
|
|
|
foreach ($userQuery as $query) { |
|
557
|
|
|
$sql .= sprintf('OR (r.%s = %d', $type, $query->$type); |
|
558
|
|
|
if (! empty($query->categories)) { |
|
559
|
|
|
$catsArr = explode('|', $query->categories); |
|
560
|
|
|
if (\count($catsArr) > 1) { |
|
561
|
|
|
$sql .= sprintf(' AND r.categories_id IN (%s)', implode(',', $catsArr)); |
|
562
|
|
|
} else { |
|
563
|
|
|
$sql .= sprintf(' AND r.categories_id = %d', $catsArr[0]); |
|
564
|
|
|
} |
|
565
|
|
|
} |
|
566
|
|
|
$sql .= ') '; |
|
567
|
|
|
} |
|
568
|
|
|
$sql .= ') '; |
|
569
|
|
|
|
|
570
|
|
|
return $sql; |
|
571
|
|
|
} |
|
572
|
|
|
|
|
573
|
|
|
/** |
|
574
|
|
|
* Function for searching on the site (by subject, searchname or advanced). |
|
575
|
|
|
* |
|
576
|
|
|
* |
|
577
|
|
|
* @param $searchName |
|
578
|
|
|
* @param $usenetName |
|
579
|
|
|
* @param $posterName |
|
580
|
|
|
* @param $fileName |
|
581
|
|
|
* @param $groupName |
|
582
|
|
|
* @param $sizeFrom |
|
583
|
|
|
* @param $sizeTo |
|
584
|
|
|
* @param $hasNfo |
|
585
|
|
|
* @param $hasComments |
|
586
|
|
|
* @param $daysNew |
|
587
|
|
|
* @param $daysOld |
|
588
|
|
|
* @param int $offset |
|
589
|
|
|
* @param int $limit |
|
590
|
|
|
* @param string|array $orderBy |
|
591
|
|
|
* @param int $maxAge |
|
592
|
|
|
* @param array $excludedCats |
|
593
|
|
|
* @param string $type |
|
594
|
|
|
* @param array $cat |
|
595
|
|
|
* @param int $minSize |
|
596
|
|
|
* @param array $tags |
|
597
|
|
|
* |
|
598
|
|
|
* @return array|\Illuminate\Database\Eloquent\Collection|mixed |
|
599
|
|
|
*/ |
|
600
|
|
|
public function search($searchName, $usenetName, $posterName, $fileName, $groupName, $sizeFrom, $sizeTo, $hasNfo, $hasComments, $daysNew, $daysOld, $offset = 0, $limit = 1000, $orderBy = '', $maxAge = -1, array $excludedCats = [], $type = 'basic', array $cat = [-1], $minSize = 0, array $tags = []) |
|
601
|
|
|
{ |
|
602
|
|
|
$sizeRange = [ |
|
603
|
|
|
1 => 1, |
|
604
|
|
|
2 => 2.5, |
|
605
|
|
|
3 => 5, |
|
606
|
|
|
4 => 10, |
|
607
|
|
|
5 => 20, |
|
608
|
|
|
6 => 30, |
|
609
|
|
|
7 => 40, |
|
610
|
|
|
8 => 80, |
|
611
|
|
|
9 => 160, |
|
612
|
|
|
10 => 320, |
|
613
|
|
|
11 => 640, |
|
614
|
|
|
]; |
|
615
|
|
|
if ($orderBy === '') { |
|
616
|
|
|
$orderBy = []; |
|
617
|
|
|
$orderBy[0] = 'postdate '; |
|
618
|
|
|
$orderBy[1] = 'desc '; |
|
619
|
|
|
} else { |
|
620
|
|
|
$orderBy = $this->getBrowseOrder($orderBy); |
|
621
|
|
|
} |
|
622
|
|
|
|
|
623
|
|
|
$searchFields = []; |
|
624
|
|
|
if ($searchName !== -1) { |
|
625
|
|
|
$searchFields['searchname'] = $searchName; |
|
626
|
|
|
} |
|
627
|
|
|
if ($usenetName !== -1) { |
|
628
|
|
|
$searchFields['name'] = $usenetName; |
|
629
|
|
|
} |
|
630
|
|
|
if ($posterName !== -1) { |
|
631
|
|
|
$searchFields['fromname'] = $posterName; |
|
632
|
|
|
} |
|
633
|
|
|
if ($fileName !== -1) { |
|
634
|
|
|
$searchFields['filename'] = $fileName; |
|
635
|
|
|
} |
|
636
|
|
|
|
|
637
|
|
|
$results = $this->sphinxSearch->searchIndexes('releases_rt', '', [], $searchFields); |
|
638
|
|
|
|
|
639
|
|
|
$searchResult = Arr::pluck($results, 'id'); |
|
640
|
|
|
|
|
641
|
|
|
$catQuery = ''; |
|
642
|
|
|
if ($type === 'basic') { |
|
643
|
|
|
$catQuery = Category::getCategorySearch($cat); |
|
644
|
|
|
} elseif ($type === 'advanced' && (int) $cat[0] !== -1) { |
|
645
|
|
|
$catQuery = sprintf('AND r.categories_id = %d', $cat[0]); |
|
646
|
|
|
} |
|
647
|
|
|
$whereSql = sprintf( |
|
648
|
|
|
'WHERE r.passwordstatus %s AND r.nzbstatus = %d %s %s %s %s %s %s %s %s %s %s %s %s %s', |
|
649
|
|
|
$this->showPasswords(), |
|
650
|
|
|
NZB::NZB_ADDED, |
|
651
|
|
|
! empty($tags) ? " AND tt.tag_name IN ('".implode("','", $tags)."')" : '', |
|
652
|
|
|
($maxAge > 0 ? sprintf(' AND r.postdate > (NOW() - INTERVAL %d DAY) ', $maxAge) : ''), |
|
653
|
|
|
((int) $groupName !== -1 ? sprintf(' AND r.groups_id = %d ', Group::getIDByName($groupName)) : ''), |
|
654
|
|
|
(array_key_exists($sizeFrom, $sizeRange) ? ' AND r.size > '.(104857600 * (int) $sizeRange[$sizeFrom]).' ' : ''), |
|
655
|
|
|
(array_key_exists($sizeTo, $sizeRange) ? ' AND r.size < '.(104857600 * (int) $sizeRange[$sizeTo]).' ' : ''), |
|
656
|
|
|
((int) $hasNfo !== 0 ? ' AND r.nfostatus = 1 ' : ''), |
|
657
|
|
|
((int) $hasComments !== 0 ? ' AND r.comments > 0 ' : ''), |
|
658
|
|
|
$catQuery, |
|
659
|
|
|
((int) $daysNew !== -1 ? sprintf(' AND r.postdate < (NOW() - INTERVAL %d DAY) ', $daysNew) : ''), |
|
660
|
|
|
((int) $daysOld !== -1 ? sprintf(' AND r.postdate > (NOW() - INTERVAL %d DAY) ', $daysOld) : ''), |
|
661
|
|
|
(\count($excludedCats) > 0 ? ' AND r.categories_id NOT IN ('.implode(',', $excludedCats).')' : ''), |
|
662
|
|
|
(! empty($searchResult) ? 'AND r.id IN ('.implode(',', $searchResult).')' : ''), |
|
663
|
|
|
($minSize > 0 ? sprintf('AND r.size >= %d', $minSize) : '') |
|
664
|
|
|
); |
|
665
|
|
|
$baseSql = sprintf( |
|
666
|
|
|
"SELECT r.searchname, r.guid, r.postdate, r.categories_id, r.size, r.totalpart, r.fromname, r.passwordstatus, r.grabs, r.comments, r.adddate, r.videos_id, r.tv_episodes_id, cp.title AS parent_category, c.title AS sub_category, |
|
667
|
|
|
CONCAT(cp.title, ' > ', c.title) AS category_name, |
|
668
|
|
|
%s AS category_ids, |
|
669
|
|
|
df.failed AS failed, |
|
670
|
|
|
g.name AS group_name, |
|
671
|
|
|
rn.releases_id AS nfoid, |
|
672
|
|
|
re.releases_id AS reid, |
|
673
|
|
|
cp.id AS categoryparentid, |
|
674
|
|
|
v.tvdb, v.trakt, v.tvrage, v.tvmaze, v.imdb, v.tmdb, |
|
675
|
|
|
tve.firstaired |
|
676
|
|
|
FROM releases r |
|
677
|
|
|
LEFT OUTER JOIN video_data re ON re.releases_id = r.id |
|
678
|
|
|
LEFT OUTER JOIN videos v ON r.videos_id = v.id |
|
679
|
|
|
LEFT OUTER JOIN tv_episodes tve ON r.tv_episodes_id = tve.id |
|
680
|
|
|
LEFT OUTER JOIN release_nfos rn ON rn.releases_id = r.id |
|
681
|
|
|
LEFT JOIN groups g ON g.id = r.groups_id |
|
682
|
|
|
LEFT JOIN categories c ON c.id = r.categories_id |
|
683
|
|
|
LEFT JOIN categories cp ON cp.id = c.parentid |
|
684
|
|
|
LEFT OUTER JOIN dnzb_failures df ON df.release_id = r.id |
|
685
|
|
|
%s %s", |
|
686
|
|
|
$this->getConcatenatedCategoryIDs(), |
|
|
|
|
|
|
687
|
|
|
! empty($tags) ? ' LEFT JOIN tagging_tagged tt ON tt.taggable_id = r.id' : '', |
|
688
|
|
|
$whereSql |
|
689
|
|
|
); |
|
690
|
|
|
$sql = sprintf( |
|
691
|
|
|
'SELECT * FROM ( |
|
692
|
|
|
%s |
|
693
|
|
|
) r |
|
694
|
|
|
ORDER BY r.%s %s |
|
695
|
|
|
LIMIT %d OFFSET %d', |
|
696
|
|
|
$baseSql, |
|
697
|
|
|
$orderBy[0], |
|
698
|
|
|
$orderBy[1], |
|
699
|
|
|
$limit, |
|
700
|
|
|
$offset |
|
701
|
|
|
); |
|
702
|
|
|
$releases = Cache::get(md5($sql)); |
|
703
|
|
|
if ($releases !== null) { |
|
704
|
|
|
return $releases; |
|
705
|
|
|
} |
|
706
|
|
|
$releases = ! empty($searchResult) ? Release::fromQuery($sql) : collect(); |
|
707
|
|
|
if ($releases->isNotEmpty()) { |
|
708
|
|
|
$releases[0]->_totalrows = $this->getPagerCount($baseSql); |
|
709
|
|
|
} |
|
710
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_medium')); |
|
711
|
|
|
Cache::put(md5($sql), $releases, $expiresAt); |
|
712
|
|
|
|
|
713
|
|
|
return $releases; |
|
714
|
|
|
} |
|
715
|
|
|
|
|
716
|
|
|
/** |
|
717
|
|
|
* Search function for API. |
|
718
|
|
|
* |
|
719
|
|
|
* |
|
720
|
|
|
* @param $searchName |
|
721
|
|
|
* @param $groupName |
|
722
|
|
|
* @param int $offset |
|
723
|
|
|
* @param int $limit |
|
724
|
|
|
* @param int $maxAge |
|
725
|
|
|
* @param array $excludedCats |
|
726
|
|
|
* @param array $cat |
|
727
|
|
|
* @param int $minSize |
|
728
|
|
|
* @param array $tags |
|
729
|
|
|
* |
|
730
|
|
|
* @return \Illuminate\Database\Eloquent\Collection|mixed |
|
731
|
|
|
*/ |
|
732
|
|
|
public function apiSearch($searchName, $groupName, $offset = 0, $limit = 1000, $maxAge = -1, array $excludedCats = [], array $cat = [-1], $minSize = 0, array $tags = []) |
|
733
|
|
|
{ |
|
734
|
|
|
if ($searchName !== -1) { |
|
735
|
|
|
$searchResult = Arr::pluck($this->sphinxSearch->searchIndexes('releases_rt', $searchName, ['searchname']), 'id'); |
|
736
|
|
|
} |
|
737
|
|
|
|
|
738
|
|
|
$catQuery = Category::getCategorySearch($cat); |
|
739
|
|
|
|
|
740
|
|
|
$whereSql = sprintf( |
|
741
|
|
|
'WHERE r.passwordstatus %s AND r.nzbstatus = %d %s %s %s %s %s %s %s', |
|
742
|
|
|
$this->showPasswords(), |
|
743
|
|
|
NZB::NZB_ADDED, |
|
744
|
|
|
! empty($tags) ? " AND tt.tag_name IN ('".implode("','", $tags)."')" : '', |
|
745
|
|
|
($maxAge > 0 ? sprintf(' AND r.postdate > (NOW() - INTERVAL %d DAY) ', $maxAge) : ''), |
|
746
|
|
|
((int) $groupName !== -1 ? sprintf(' AND r.groups_id = %d ', Group::getIDByName($groupName)) : ''), |
|
747
|
|
|
$catQuery, |
|
748
|
|
|
(\count($excludedCats) > 0 ? ' AND r.categories_id NOT IN ('.implode(',', $excludedCats).')' : ''), |
|
749
|
|
|
(! empty($searchResult) ? 'AND r.id IN ('.implode(',', $searchResult).')' : ''), |
|
750
|
|
|
($minSize > 0 ? sprintf('AND r.size >= %d', $minSize) : '') |
|
751
|
|
|
); |
|
752
|
|
|
$baseSql = sprintf( |
|
753
|
|
|
"SELECT r.searchname, r.guid, r.postdate, r.categories_id, r.size, r.totalpart, r.fromname, r.passwordstatus, r.grabs, r.comments, r.adddate, r.videos_id, r.tv_episodes_id, m.imdbid, m.tmdbid, m.traktid, cp.title AS parent_category, c.title AS sub_category, |
|
754
|
|
|
CONCAT(cp.title, ' > ', c.title) AS category_name, |
|
755
|
|
|
%s AS category_ids, |
|
756
|
|
|
g.name AS group_name, |
|
757
|
|
|
cp.id AS categoryparentid, |
|
758
|
|
|
v.tvdb, v.trakt, v.tvrage, v.tvmaze, v.imdb, v.tmdb, |
|
759
|
|
|
tve.firstaired, tve.title, tve.series, tve.episode |
|
760
|
|
|
FROM releases r |
|
761
|
|
|
LEFT OUTER JOIN videos v ON r.videos_id = v.id |
|
762
|
|
|
LEFT OUTER JOIN tv_episodes tve ON r.tv_episodes_id = tve.id |
|
763
|
|
|
LEFT JOIN movieinfo m ON m.id = r.movieinfo_id |
|
764
|
|
|
LEFT JOIN groups g ON g.id = r.groups_id |
|
765
|
|
|
LEFT JOIN categories c ON c.id = r.categories_id |
|
766
|
|
|
LEFT JOIN categories cp ON cp.id = c.parentid |
|
767
|
|
|
%s %s", |
|
768
|
|
|
$this->getConcatenatedCategoryIDs(), |
|
|
|
|
|
|
769
|
|
|
! empty($tags) ? ' LEFT JOIN tagging_tagged tt ON tt.taggable_id = r.id' : '', |
|
770
|
|
|
$whereSql |
|
771
|
|
|
); |
|
772
|
|
|
$sql = sprintf( |
|
773
|
|
|
'SELECT * FROM ( |
|
774
|
|
|
%s |
|
775
|
|
|
) r |
|
776
|
|
|
ORDER BY r.postdate DESC |
|
777
|
|
|
LIMIT %d OFFSET %d', |
|
778
|
|
|
$baseSql, |
|
779
|
|
|
$limit, |
|
780
|
|
|
$offset |
|
781
|
|
|
); |
|
782
|
|
|
$releases = Cache::get(md5($sql)); |
|
783
|
|
|
if ($releases !== null) { |
|
784
|
|
|
return $releases; |
|
785
|
|
|
} |
|
786
|
|
|
if ($searchName !== -1 && ! empty($searchResult)) { |
|
787
|
|
|
$releases = Release::fromQuery($sql); |
|
788
|
|
|
} elseif ($searchName !== -1 && empty($searchResult)) { |
|
789
|
|
|
$releases = collect(); |
|
790
|
|
|
} else { |
|
791
|
|
|
$releases = Release::fromQuery($sql); |
|
792
|
|
|
} |
|
793
|
|
|
if ($releases->isNotEmpty()) { |
|
794
|
|
|
$releases[0]->_totalrows = $this->getPagerCount($baseSql); |
|
795
|
|
|
} |
|
796
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_medium')); |
|
797
|
|
|
Cache::put(md5($sql), $releases, $expiresAt); |
|
798
|
|
|
|
|
799
|
|
|
return $releases; |
|
800
|
|
|
} |
|
801
|
|
|
|
|
802
|
|
|
/** |
|
803
|
|
|
* Search TV Shows via API. |
|
804
|
|
|
* |
|
805
|
|
|
* |
|
806
|
|
|
* @param array $siteIdArr |
|
807
|
|
|
* @param string $series |
|
808
|
|
|
* @param string $episode |
|
809
|
|
|
* @param string $airdate |
|
810
|
|
|
* @param int $offset |
|
811
|
|
|
* @param int $limit |
|
812
|
|
|
* @param string $name |
|
813
|
|
|
* @param array $cat |
|
814
|
|
|
* @param int $maxAge |
|
815
|
|
|
* @param int $minSize |
|
816
|
|
|
* @param array $excludedCategories |
|
817
|
|
|
* @param array $tags |
|
818
|
|
|
* @return \Illuminate\Database\Eloquent\Collection|mixed |
|
819
|
|
|
*/ |
|
820
|
|
|
public function tvSearch(array $siteIdArr = [], $series = '', $episode = '', $airdate = '', $offset = 0, $limit = 100, $name = '', array $cat = [-1], $maxAge = -1, $minSize = 0, array $excludedCategories = [], array $tags = []) |
|
821
|
|
|
{ |
|
822
|
|
|
$siteSQL = []; |
|
823
|
|
|
$showSql = ''; |
|
824
|
|
|
|
|
825
|
|
|
foreach ($siteIdArr as $column => $Id) { |
|
826
|
|
|
if ($Id > 0) { |
|
827
|
|
|
$siteSQL[] = sprintf('v.%s = %d', $column, $Id); |
|
828
|
|
|
} |
|
829
|
|
|
} |
|
830
|
|
|
|
|
831
|
|
|
if (\count($siteSQL) > 0) { |
|
832
|
|
|
// If we have show info, find the Episode ID/Video ID first to avoid table scans |
|
833
|
|
|
$showQry = sprintf( |
|
834
|
|
|
" |
|
835
|
|
|
SELECT |
|
836
|
|
|
v.id AS video, |
|
837
|
|
|
GROUP_CONCAT(tve.id SEPARATOR ',') AS episodes |
|
838
|
|
|
FROM videos v |
|
839
|
|
|
LEFT JOIN tv_episodes tve ON v.id = tve.videos_id |
|
840
|
|
|
WHERE (%s) %s %s %s |
|
841
|
|
|
GROUP BY v.id |
|
842
|
|
|
LIMIT 1", |
|
843
|
|
|
implode(' OR ', $siteSQL), |
|
844
|
|
|
($series !== '' ? sprintf('AND tve.series = %d', (int) preg_replace('/^s0*/i', '', $series)) : ''), |
|
845
|
|
|
($episode !== '' ? sprintf('AND tve.episode = %d', (int) preg_replace('/^e0*/i', '', $episode)) : ''), |
|
846
|
|
|
($airdate !== '' ? sprintf('AND DATE(tve.firstaired) = %s', escapeString($airdate)) : '') |
|
847
|
|
|
); |
|
848
|
|
|
$show = Release::fromQuery($showQry); |
|
849
|
|
|
|
|
850
|
|
|
if (! empty($show[0])) { |
|
851
|
|
|
if ((! empty($series) || ! empty($episode) || ! empty($airdate)) && $show[0]->episodes !== '') { |
|
852
|
|
|
$showSql = sprintf('AND r.tv_episodes_id IN (%s)', $show[0]->episodes); |
|
853
|
|
|
} elseif ((int) $show[0]->video > 0) { |
|
854
|
|
|
$showSql = 'AND r.videos_id = '.$show[0]->video; |
|
855
|
|
|
// If $series is set but episode is not, return Season Packs only |
|
856
|
|
|
if (! empty($series) && empty($episode)) { |
|
857
|
|
|
$showSql .= ' AND r.tv_episodes_id = 0'; |
|
858
|
|
|
} |
|
859
|
|
|
} else { |
|
860
|
|
|
// If we were passed Episode Info and no match was found, do not run the query |
|
861
|
|
|
return []; |
|
862
|
|
|
} |
|
863
|
|
|
} else { |
|
864
|
|
|
// If we were passed Site ID Info and no match was found, do not run the query |
|
865
|
|
|
return []; |
|
866
|
|
|
} |
|
867
|
|
|
} |
|
868
|
|
|
// If $name is set it is a fallback search, add available SxxExx/airdate info to the query |
|
869
|
|
|
if (! empty($name) && $showSql === '') { |
|
870
|
|
|
if (! empty($series) && (int) $series < 1900) { |
|
871
|
|
|
$name .= sprintf(' S%s', str_pad($series, 2, '0', STR_PAD_LEFT)); |
|
872
|
|
|
if (! empty($episode) && strpos($episode, '/') === false) { |
|
873
|
|
|
$name .= sprintf('E%s', str_pad($episode, 2, '0', STR_PAD_LEFT)); |
|
874
|
|
|
} |
|
875
|
|
|
} elseif (! empty($airdate)) { |
|
876
|
|
|
$name .= sprintf(' %s', str_replace(['/', '-', '.', '_'], ' ', $airdate)); |
|
877
|
|
|
} |
|
878
|
|
|
} |
|
879
|
|
|
|
|
880
|
|
|
if (! empty($name)) { |
|
881
|
|
|
$searchResult = Arr::pluck($this->sphinxSearch->searchIndexes('releases_rt', $name, ['searchname']), 'id'); |
|
882
|
|
|
} |
|
883
|
|
|
|
|
884
|
|
|
$whereSql = sprintf( |
|
885
|
|
|
'WHERE r.nzbstatus = %d |
|
886
|
|
|
AND r.passwordstatus %s |
|
887
|
|
|
%s %s %s %s %s %s %s', |
|
888
|
|
|
NZB::NZB_ADDED, |
|
889
|
|
|
$this->showPasswords(), |
|
890
|
|
|
! empty($tags) ? " AND tt.tag_name IN ('".implode("','", $tags)."')" : '', |
|
891
|
|
|
$showSql, |
|
892
|
|
|
(! empty($searchResult) ? 'AND r.id IN ('.implode(',', $searchResult).')' : ''), |
|
893
|
|
|
Category::getCategorySearch($cat), |
|
894
|
|
|
($maxAge > 0 ? sprintf('AND r.postdate > NOW() - INTERVAL %d DAY', $maxAge) : ''), |
|
895
|
|
|
($minSize > 0 ? sprintf('AND r.size >= %d', $minSize) : ''), |
|
896
|
|
|
! empty($excludedCategories) ? sprintf('AND r.categories_id NOT IN('.implode(',', $excludedCategories).')') : '' |
|
897
|
|
|
); |
|
898
|
|
|
$baseSql = sprintf( |
|
899
|
|
|
"SELECT r.searchname, r.guid, r.postdate, r.categories_id, r.size, r.totalpart, r.fromname, r.passwordstatus, r.grabs, r.comments, r.adddate, r.videos_id, r.tv_episodes_id, |
|
900
|
|
|
v.title, v.countries_id, v.started, v.tvdb, v.trakt, |
|
901
|
|
|
v.imdb, v.tmdb, v.tvmaze, v.tvrage, v.source, |
|
902
|
|
|
tvi.summary, tvi.publisher, tvi.image, |
|
903
|
|
|
tve.series, tve.episode, tve.se_complete, tve.title, tve.firstaired, tve.summary, cp.title AS parent_category, c.title AS sub_category, |
|
904
|
|
|
CONCAT(cp.title, ' > ', c.title) AS category_name, |
|
905
|
|
|
%s AS category_ids, |
|
906
|
|
|
g.name AS group_name, |
|
907
|
|
|
rn.releases_id AS nfoid, |
|
908
|
|
|
re.releases_id AS reid |
|
909
|
|
|
FROM releases r |
|
910
|
|
|
LEFT OUTER JOIN videos v ON r.videos_id = v.id AND v.type = 0 |
|
911
|
|
|
LEFT OUTER JOIN tv_info tvi ON v.id = tvi.videos_id |
|
912
|
|
|
LEFT OUTER JOIN tv_episodes tve ON r.tv_episodes_id = tve.id |
|
913
|
|
|
LEFT JOIN categories c ON c.id = r.categories_id |
|
914
|
|
|
LEFT JOIN categories cp ON cp.id = c.parentid |
|
915
|
|
|
LEFT JOIN groups g ON g.id = r.groups_id |
|
916
|
|
|
LEFT OUTER JOIN video_data re ON re.releases_id = r.id |
|
917
|
|
|
LEFT OUTER JOIN release_nfos rn ON rn.releases_id = r.id |
|
918
|
|
|
%s %s", |
|
919
|
|
|
$this->getConcatenatedCategoryIDs(), |
|
|
|
|
|
|
920
|
|
|
! empty($tags) ? ' LEFT JOIN tagging_tagged tt ON tt.taggable_id = r.id' : '', |
|
921
|
|
|
$whereSql |
|
922
|
|
|
); |
|
923
|
|
|
$sql = sprintf( |
|
924
|
|
|
'%s |
|
925
|
|
|
ORDER BY postdate DESC |
|
926
|
|
|
LIMIT %d OFFSET %d', |
|
927
|
|
|
$baseSql, |
|
928
|
|
|
$limit, |
|
929
|
|
|
$offset |
|
930
|
|
|
); |
|
931
|
|
|
$releases = Cache::get(md5($sql)); |
|
932
|
|
|
if ($releases !== null) { |
|
933
|
|
|
return $releases; |
|
934
|
|
|
} |
|
935
|
|
|
$releases = Release::fromQuery($sql); |
|
936
|
|
|
if ($releases->isNotEmpty()) { |
|
937
|
|
|
$releases[0]->_totalrows = $this->getPagerCount( |
|
938
|
|
|
preg_replace('#LEFT(\s+OUTER)?\s+JOIN\s+(?!tv_episodes)\s+.*ON.*=.*\n#i', ' ', $baseSql) |
|
939
|
|
|
); |
|
940
|
|
|
} |
|
941
|
|
|
|
|
942
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_medium')); |
|
943
|
|
|
Cache::put(md5($sql), $releases, $expiresAt); |
|
944
|
|
|
|
|
945
|
|
|
return $releases; |
|
946
|
|
|
} |
|
947
|
|
|
|
|
948
|
|
|
/** |
|
949
|
|
|
* Search TV Shows via APIv2. |
|
950
|
|
|
* |
|
951
|
|
|
* |
|
952
|
|
|
* @param array $siteIdArr |
|
953
|
|
|
* @param string $series |
|
954
|
|
|
* @param string $episode |
|
955
|
|
|
* @param string $airdate |
|
956
|
|
|
* @param int $offset |
|
957
|
|
|
* @param int $limit |
|
958
|
|
|
* @param string $name |
|
959
|
|
|
* @param array $cat |
|
960
|
|
|
* @param int $maxAge |
|
961
|
|
|
* @param int $minSize |
|
962
|
|
|
* @param array $excludedCategories |
|
963
|
|
|
* @param array $tags |
|
964
|
|
|
* @return \Illuminate\Database\Eloquent\Collection|mixed |
|
965
|
|
|
*/ |
|
966
|
|
|
public function apiTvSearch(array $siteIdArr = [], $series = '', $episode = '', $airdate = '', $offset = 0, $limit = 100, $name = '', array $cat = [-1], $maxAge = -1, $minSize = 0, array $excludedCategories = [], array $tags = []) |
|
967
|
|
|
{ |
|
968
|
|
|
$siteSQL = []; |
|
969
|
|
|
$showSql = ''; |
|
970
|
|
|
foreach ($siteIdArr as $column => $Id) { |
|
971
|
|
|
if ($Id > 0) { |
|
972
|
|
|
$siteSQL[] = sprintf('v.%s = %d', $column, $Id); |
|
973
|
|
|
} |
|
974
|
|
|
} |
|
975
|
|
|
|
|
976
|
|
|
if (\count($siteSQL) > 0) { |
|
977
|
|
|
// If we have show info, find the Episode ID/Video ID first to avoid table scans |
|
978
|
|
|
$showQry = sprintf( |
|
979
|
|
|
" |
|
980
|
|
|
SELECT |
|
981
|
|
|
v.id AS video, |
|
982
|
|
|
GROUP_CONCAT(tve.id SEPARATOR ',') AS episodes |
|
983
|
|
|
FROM videos v |
|
984
|
|
|
LEFT JOIN tv_episodes tve ON v.id = tve.videos_id |
|
985
|
|
|
WHERE (%s) %s %s %s |
|
986
|
|
|
GROUP BY v.id |
|
987
|
|
|
LIMIT 1", |
|
988
|
|
|
implode(' OR ', $siteSQL), |
|
989
|
|
|
($series !== '' ? sprintf('AND tve.series = %d', (int) preg_replace('/^s0*/i', '', $series)) : ''), |
|
990
|
|
|
($episode !== '' ? sprintf('AND tve.episode = %d', (int) preg_replace('/^e0*/i', '', $episode)) : ''), |
|
991
|
|
|
($airdate !== '' ? sprintf('AND DATE(tve.firstaired) = %s', escapeString($airdate)) : '') |
|
992
|
|
|
); |
|
993
|
|
|
$show = Release::fromQuery($showQry); |
|
994
|
|
|
|
|
995
|
|
|
if ($show->isNotEmpty()) { |
|
996
|
|
|
if ((! empty($series) || ! empty($episode) || ! empty($airdate)) && $show[0]->episodes != '') { |
|
997
|
|
|
$showSql = sprintf('AND r.tv_episodes_id IN (%s)', $show[0]->episodes); |
|
998
|
|
|
} elseif ((int) $show[0]->video > 0) { |
|
999
|
|
|
$showSql = 'AND r.videos_id = '.$show[0]->video; |
|
1000
|
|
|
// If $series is set but episode is not, return Season Packs only |
|
1001
|
|
|
if (! empty($series) && empty($episode)) { |
|
1002
|
|
|
$showSql .= ' AND r.tv_episodes_id = 0'; |
|
1003
|
|
|
} |
|
1004
|
|
|
} else { |
|
1005
|
|
|
// If we were passed Episode Info and no match was found, do not run the query |
|
1006
|
|
|
return []; |
|
1007
|
|
|
} |
|
1008
|
|
|
} else { |
|
1009
|
|
|
// If we were passed Site ID Info and no match was found, do not run the query |
|
1010
|
|
|
return []; |
|
1011
|
|
|
} |
|
1012
|
|
|
} |
|
1013
|
|
|
// If $name is set it is a fallback search, add available SxxExx/airdate info to the query |
|
1014
|
|
|
if (! empty($name) && $showSql === '') { |
|
1015
|
|
|
if (! empty($series) && (int) $series < 1900) { |
|
1016
|
|
|
$name .= sprintf(' S%s', str_pad($series, 2, '0', STR_PAD_LEFT)); |
|
1017
|
|
|
if (! empty($episode) && strpos($episode, '/') === false) { |
|
1018
|
|
|
$name .= sprintf('E%s', str_pad($episode, 2, '0', STR_PAD_LEFT)); |
|
1019
|
|
|
} |
|
1020
|
|
|
} elseif (! empty($airdate)) { |
|
1021
|
|
|
$name .= sprintf(' %s', str_replace(['/', '-', '.', '_'], ' ', $airdate)); |
|
1022
|
|
|
} |
|
1023
|
|
|
} |
|
1024
|
|
|
|
|
1025
|
|
|
if (! empty($name)) { |
|
1026
|
|
|
$searchResult = Arr::pluck($this->sphinxSearch->searchIndexes('releases_rt', $name, ['searchname']), 'id'); |
|
1027
|
|
|
} |
|
1028
|
|
|
|
|
1029
|
|
|
$whereSql = sprintf( |
|
1030
|
|
|
'WHERE r.nzbstatus = %d |
|
1031
|
|
|
AND r.passwordstatus %s |
|
1032
|
|
|
%s %s %s %s %s %s %s', |
|
1033
|
|
|
NZB::NZB_ADDED, |
|
1034
|
|
|
$this->showPasswords(), |
|
1035
|
|
|
! empty($tags) ? " AND tt.tag_name IN ('".implode("','", $tags)."')" : '', |
|
1036
|
|
|
$showSql, |
|
1037
|
|
|
(! empty($searchResult) ? 'AND r.id IN ('.implode(',', $searchResult).')' : ''), |
|
1038
|
|
|
Category::getCategorySearch($cat), |
|
1039
|
|
|
($maxAge > 0 ? sprintf('AND r.postdate > NOW() - INTERVAL %d DAY', $maxAge) : ''), |
|
1040
|
|
|
($minSize > 0 ? sprintf('AND r.size >= %d', $minSize) : ''), |
|
1041
|
|
|
! empty($excludedCategories) ? sprintf('AND r.categories_id NOT IN('.implode(',', $excludedCategories).')') : '' |
|
1042
|
|
|
); |
|
1043
|
|
|
$baseSql = sprintf( |
|
1044
|
|
|
"SELECT r.searchname, r.guid, r.postdate, r.categories_id, r.size, r.totalpart, r.fromname, r.passwordstatus, r.grabs, r.comments, r.adddate, r.tv_episodes_id, |
|
1045
|
|
|
v.title, v.type, v.tvdb, v.trakt,v.imdb, v.tmdb, v.tvmaze, v.tvrage, |
|
1046
|
|
|
tve.series, tve.episode, tve.se_complete, tve.title, tve.firstaired, cp.title AS parent_category, c.title AS sub_category, |
|
1047
|
|
|
CONCAT(cp.title, ' > ', c.title) AS category_name, |
|
1048
|
|
|
%s AS category_ids, |
|
1049
|
|
|
g.name AS group_name |
|
1050
|
|
|
FROM releases r |
|
1051
|
|
|
LEFT OUTER JOIN videos v ON r.videos_id = v.id AND v.type = 0 |
|
1052
|
|
|
LEFT OUTER JOIN tv_info tvi ON v.id = tvi.videos_id |
|
1053
|
|
|
LEFT OUTER JOIN tv_episodes tve ON r.tv_episodes_id = tve.id |
|
1054
|
|
|
LEFT JOIN categories c ON c.id = r.categories_id |
|
1055
|
|
|
LEFT JOIN categories cp ON cp.id = c.parentid |
|
1056
|
|
|
LEFT JOIN groups g ON g.id = r.groups_id |
|
1057
|
|
|
%s %s", |
|
1058
|
|
|
$this->getConcatenatedCategoryIDs(), |
|
|
|
|
|
|
1059
|
|
|
! empty($tags) ? ' LEFT JOIN tagging_tagged tt ON tt.taggable_id = r.id' : '', |
|
1060
|
|
|
$whereSql |
|
1061
|
|
|
); |
|
1062
|
|
|
$sql = sprintf( |
|
1063
|
|
|
'%s |
|
1064
|
|
|
ORDER BY postdate DESC |
|
1065
|
|
|
LIMIT %d OFFSET %d', |
|
1066
|
|
|
$baseSql, |
|
1067
|
|
|
$limit, |
|
1068
|
|
|
$offset |
|
1069
|
|
|
); |
|
1070
|
|
|
$releases = Cache::get(md5($sql)); |
|
1071
|
|
|
if ($releases !== null) { |
|
1072
|
|
|
return $releases; |
|
1073
|
|
|
} |
|
1074
|
|
|
$releases = Release::fromQuery($sql); |
|
1075
|
|
|
if ($releases->isNotEmpty()) { |
|
1076
|
|
|
$releases[0]->_totalrows = $this->getPagerCount( |
|
1077
|
|
|
preg_replace('#LEFT(\s+OUTER)?\s+JOIN\s+(?!tv_episodes)\s+.*ON.*=.*\n#i', ' ', $baseSql) |
|
1078
|
|
|
); |
|
1079
|
|
|
} |
|
1080
|
|
|
|
|
1081
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_medium')); |
|
1082
|
|
|
Cache::put(md5($sql), $releases, $expiresAt); |
|
1083
|
|
|
|
|
1084
|
|
|
return $releases; |
|
1085
|
|
|
} |
|
1086
|
|
|
|
|
1087
|
|
|
/** |
|
1088
|
|
|
* Search anime releases. |
|
1089
|
|
|
* |
|
1090
|
|
|
* |
|
1091
|
|
|
* @param $aniDbID |
|
1092
|
|
|
* @param int $offset |
|
1093
|
|
|
* @param int $limit |
|
1094
|
|
|
* @param string $name |
|
1095
|
|
|
* @param array $cat |
|
1096
|
|
|
* @param int $maxAge |
|
1097
|
|
|
* @param array $excludedCategories |
|
1098
|
|
|
* @return \Illuminate\Database\Eloquent\Collection|mixed |
|
1099
|
|
|
*/ |
|
1100
|
|
|
public function animeSearch($aniDbID, $offset = 0, $limit = 100, $name = '', array $cat = [-1], $maxAge = -1, array $excludedCategories = []) |
|
1101
|
|
|
{ |
|
1102
|
|
|
if (! empty($name)) { |
|
1103
|
|
|
$searchResult = Arr::pluck($this->sphinxSearch->searchIndexes('releases_rt', $name, ['searchname']), 'id'); |
|
1104
|
|
|
} |
|
1105
|
|
|
|
|
1106
|
|
|
$whereSql = sprintf( |
|
1107
|
|
|
'WHERE r.passwordstatus %s |
|
1108
|
|
|
AND r.nzbstatus = %d |
|
1109
|
|
|
%s %s %s %s %s', |
|
1110
|
|
|
$this->showPasswords(), |
|
1111
|
|
|
NZB::NZB_ADDED, |
|
1112
|
|
|
($aniDbID > -1 ? sprintf(' AND r.anidbid = %d ', $aniDbID) : ''), |
|
1113
|
|
|
(! empty($searchResult) ? 'AND r.id IN ('.implode(',', $searchResult).')' : ''), |
|
1114
|
|
|
! empty($excludedCategories) ? sprintf('AND r.categories_id NOT IN('.implode(',', $excludedCategories).')') : '', |
|
1115
|
|
|
Category::getCategorySearch($cat), |
|
1116
|
|
|
($maxAge > 0 ? sprintf(' AND r.postdate > NOW() - INTERVAL %d DAY ', $maxAge) : '') |
|
1117
|
|
|
); |
|
1118
|
|
|
$baseSql = sprintf( |
|
1119
|
|
|
"SELECT r.*, cp.title AS parent_category, c.title AS sub_category, |
|
1120
|
|
|
CONCAT(cp.title, ' > ', c.title) AS category_name, |
|
1121
|
|
|
%s AS category_ids, |
|
1122
|
|
|
g.name AS group_name, |
|
1123
|
|
|
rn.releases_id AS nfoid, |
|
1124
|
|
|
re.releases_id AS reid |
|
1125
|
|
|
FROM releases r |
|
1126
|
|
|
LEFT JOIN categories c ON c.id = r.categories_id |
|
1127
|
|
|
LEFT JOIN categories cp ON cp.id = c.parentid |
|
1128
|
|
|
LEFT JOIN groups g ON g.id = r.groups_id |
|
1129
|
|
|
LEFT OUTER JOIN release_nfos rn ON rn.releases_id = r.id |
|
1130
|
|
|
LEFT OUTER JOIN releaseextrafull re ON re.releases_id = r.id |
|
1131
|
|
|
%s", |
|
1132
|
|
|
$this->getConcatenatedCategoryIDs(), |
|
|
|
|
|
|
1133
|
|
|
$whereSql |
|
1134
|
|
|
); |
|
1135
|
|
|
$sql = sprintf( |
|
1136
|
|
|
'%s |
|
1137
|
|
|
ORDER BY postdate DESC |
|
1138
|
|
|
LIMIT %d OFFSET %d', |
|
1139
|
|
|
$baseSql, |
|
1140
|
|
|
$limit, |
|
1141
|
|
|
$offset |
|
1142
|
|
|
); |
|
1143
|
|
|
$releases = Cache::get(md5($sql)); |
|
1144
|
|
|
if ($releases !== null) { |
|
1145
|
|
|
return $releases; |
|
1146
|
|
|
} |
|
1147
|
|
|
$releases = Release::fromQuery($sql); |
|
1148
|
|
|
if ($releases->isNotEmpty()) { |
|
1149
|
|
|
$releases[0]->_totalrows = $this->getPagerCount($baseSql); |
|
1150
|
|
|
} |
|
1151
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_medium')); |
|
1152
|
|
|
Cache::put(md5($sql), $releases, $expiresAt); |
|
1153
|
|
|
|
|
1154
|
|
|
return $releases; |
|
1155
|
|
|
} |
|
1156
|
|
|
|
|
1157
|
|
|
/** |
|
1158
|
|
|
* Movies search through API and site. |
|
1159
|
|
|
* |
|
1160
|
|
|
* |
|
1161
|
|
|
* @param int $imDbId |
|
1162
|
|
|
* @param int $tmDbId |
|
1163
|
|
|
* @param int $traktId |
|
1164
|
|
|
* @param int $offset |
|
1165
|
|
|
* @param int $limit |
|
1166
|
|
|
* @param string $name |
|
1167
|
|
|
* @param array $cat |
|
1168
|
|
|
* @param int $maxAge |
|
1169
|
|
|
* @param int $minSize |
|
1170
|
|
|
* @param array $excludedCategories |
|
1171
|
|
|
* @param array $tags |
|
1172
|
|
|
* @return \Illuminate\Database\Eloquent\Collection|mixed |
|
1173
|
|
|
*/ |
|
1174
|
|
|
public function moviesSearch($imDbId = -1, $tmDbId = -1, $traktId = -1, $offset = 0, $limit = 100, $name = '', array $cat = [-1], $maxAge = -1, $minSize = 0, array $excludedCategories = [], array $tags = []) |
|
1175
|
|
|
{ |
|
1176
|
|
|
if (! empty($name)) { |
|
1177
|
|
|
$searchResult = Arr::pluck($this->sphinxSearch->searchIndexes('releases_rt', $name, ['searchname']), 'id'); |
|
1178
|
|
|
} |
|
1179
|
|
|
|
|
1180
|
|
|
$whereSql = sprintf( |
|
1181
|
|
|
'WHERE r.categories_id BETWEEN '.Category::MOVIE_ROOT.' AND '.Category::MOVIE_OTHER.' |
|
1182
|
|
|
AND r.nzbstatus = %d |
|
1183
|
|
|
AND r.passwordstatus %s |
|
1184
|
|
|
%s %s %s %s %s %s %s %s', |
|
1185
|
|
|
NZB::NZB_ADDED, |
|
1186
|
|
|
$this->showPasswords(), |
|
1187
|
|
|
(! empty($searchResult) ? 'AND r.id IN ('.implode(',', $searchResult).')' : ''), |
|
1188
|
|
|
! empty($tags) ? " AND tt.tag_name IN ('".implode("','", $tags)."')" : '', |
|
1189
|
|
|
($imDbId !== -1 && is_numeric($imDbId)) ? sprintf(' AND m.imdbid = %d ', str_pad($imDbId, 7, '0', STR_PAD_LEFT)) : '', |
|
1190
|
|
|
($tmDbId !== -1 && is_numeric($tmDbId)) ? sprintf(' AND m.tmdbid = %d ', $tmDbId) : '', |
|
1191
|
|
|
($traktId !== -1 && is_numeric($traktId)) ? sprintf(' AND m.traktid = %d ', $traktId) : '', |
|
1192
|
|
|
! empty($excludedCategories) ? sprintf('AND r.categories_id NOT IN('.implode(',', $excludedCategories).')') : '', |
|
1193
|
|
|
Category::getCategorySearch($cat), |
|
1194
|
|
|
$maxAge > 0 ? sprintf(' AND r.postdate > NOW() - INTERVAL %d DAY ', $maxAge) : '', |
|
1195
|
|
|
$minSize > 0 ? sprintf('AND r.size >= %d', $minSize) : '' |
|
1196
|
|
|
); |
|
1197
|
|
|
$baseSql = sprintf( |
|
1198
|
|
|
"SELECT r.searchname, r.guid, r.postdate, r.categories_id, r.size, r.totalpart, r.fromname, r.passwordstatus, r.grabs, r.comments, r.adddate, r.imdbid, r.videos_id, r.tv_episodes_id, m.imdbid, m.tmdbid, m.traktid, cp.title AS parent_category, c.title AS sub_category, |
|
1199
|
|
|
concat(cp.title, ' > ', c.title) AS category_name, |
|
1200
|
|
|
%s AS category_ids, |
|
1201
|
|
|
g.name AS group_name, |
|
1202
|
|
|
rn.releases_id AS nfoid |
|
1203
|
|
|
FROM releases r |
|
1204
|
|
|
LEFT JOIN movieinfo m ON m.id = r.movieinfo_id |
|
1205
|
|
|
LEFT JOIN groups g ON g.id = r.groups_id |
|
1206
|
|
|
LEFT JOIN categories c ON c.id = r.categories_id |
|
1207
|
|
|
LEFT JOIN categories cp ON cp.id = c.parentid |
|
1208
|
|
|
LEFT OUTER JOIN release_nfos rn ON rn.releases_id = r.id |
|
1209
|
|
|
%s %s", |
|
1210
|
|
|
$this->getConcatenatedCategoryIDs(), |
|
|
|
|
|
|
1211
|
|
|
! empty($tags) ? ' LEFT JOIN tagging_tagged tt ON tt.taggable_id = r.id' : '', |
|
1212
|
|
|
$whereSql |
|
1213
|
|
|
); |
|
1214
|
|
|
$sql = sprintf( |
|
1215
|
|
|
'%s |
|
1216
|
|
|
ORDER BY postdate DESC |
|
1217
|
|
|
LIMIT %d OFFSET %d', |
|
1218
|
|
|
$baseSql, |
|
1219
|
|
|
$limit, |
|
1220
|
|
|
$offset |
|
1221
|
|
|
); |
|
1222
|
|
|
|
|
1223
|
|
|
$releases = Cache::get(md5($sql)); |
|
1224
|
|
|
if ($releases !== null) { |
|
1225
|
|
|
return $releases; |
|
1226
|
|
|
} |
|
1227
|
|
|
$releases = Release::fromQuery($sql); |
|
1228
|
|
|
if ($releases->isNotEmpty()) { |
|
1229
|
|
|
$releases[0]->_totalrows = $this->getPagerCount($baseSql); |
|
1230
|
|
|
} |
|
1231
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_medium')); |
|
1232
|
|
|
Cache::put(md5($sql), $releases, $expiresAt); |
|
1233
|
|
|
|
|
1234
|
|
|
return $releases; |
|
1235
|
|
|
} |
|
1236
|
|
|
|
|
1237
|
|
|
/** |
|
1238
|
|
|
* @param $currentID |
|
1239
|
|
|
* @param $name |
|
1240
|
|
|
* @param array $excludedCats |
|
1241
|
|
|
* @return array|\Illuminate\Database\Eloquent\Collection |
|
1242
|
|
|
*/ |
|
1243
|
|
|
public function searchSimilar($currentID, $name, array $excludedCats = []) |
|
1244
|
|
|
{ |
|
1245
|
|
|
// Get the category for the parent of this release. |
|
1246
|
|
|
$ret = false; |
|
1247
|
|
|
$currRow = Release::getCatByRelId($currentID); |
|
1248
|
|
|
if ($currRow !== null) { |
|
1249
|
|
|
$catRow = Category::find($currRow['categories_id']); |
|
1250
|
|
|
$parentCat = $catRow['parentid']; |
|
1251
|
|
|
|
|
1252
|
|
|
$results = $this->search(getSimilarName($name), -1, -1, -1, -1, '', '', 0, 0, -1, -1, 0, '', '', -1, $excludedCats, [$parentCat]); |
|
|
|
|
|
|
1253
|
|
|
if (! $results) { |
|
1254
|
|
|
return $results; |
|
1255
|
|
|
} |
|
1256
|
|
|
|
|
1257
|
|
|
$ret = []; |
|
1258
|
|
|
foreach ($results as $res) { |
|
1259
|
|
|
if ($res['id'] !== $currentID && $res['categoryparentid'] === $parentCat) { |
|
1260
|
|
|
$ret[] = $res; |
|
1261
|
|
|
} |
|
1262
|
|
|
} |
|
1263
|
|
|
} |
|
1264
|
|
|
|
|
1265
|
|
|
return $ret; |
|
|
|
|
|
|
1266
|
|
|
} |
|
1267
|
|
|
|
|
1268
|
|
|
/** |
|
1269
|
|
|
* @param array $guids |
|
1270
|
|
|
* |
|
1271
|
|
|
* @return string |
|
1272
|
|
|
* @throws \Exception |
|
1273
|
|
|
*/ |
|
1274
|
|
|
public function getZipped(array $guids = []): string |
|
1275
|
|
|
{ |
|
1276
|
|
|
$nzb = new NZB(); |
|
1277
|
|
|
$zipped = new Zipper(); |
|
1278
|
|
|
$zippedFileName = now()->format('Ymdhis').'.nzb.zip'; |
|
1279
|
|
|
$zippedFilePath = resource_path().'/tmp/'.$zippedFileName; |
|
1280
|
|
|
|
|
1281
|
|
|
foreach ($guids as $guid) { |
|
1282
|
|
|
$nzbPath = $nzb->NZBPath($guid); |
|
1283
|
|
|
|
|
1284
|
|
|
if ($nzbPath) { |
|
1285
|
|
|
$nzbContents = Utility::unzipGzipFile($nzbPath); |
|
1286
|
|
|
|
|
1287
|
|
|
if ($nzbContents) { |
|
1288
|
|
|
$filename = $guid; |
|
1289
|
|
|
$r = Release::getByGuid($guid); |
|
1290
|
|
|
if ($r) { |
|
1291
|
|
|
$filename = $r['searchname']; |
|
1292
|
|
|
} |
|
1293
|
|
|
$zipped->make($zippedFilePath)->addString($filename.'.nzb', $nzbContents); |
|
1294
|
|
|
} |
|
1295
|
|
|
} |
|
1296
|
|
|
} |
|
1297
|
|
|
|
|
1298
|
|
|
$zipped->close(); |
|
1299
|
|
|
|
|
1300
|
|
|
return File::isFile($zippedFilePath) ? $zippedFilePath : ''; |
|
1301
|
|
|
} |
|
1302
|
|
|
|
|
1303
|
|
|
/** |
|
1304
|
|
|
* Get count of releases for pager. |
|
1305
|
|
|
* |
|
1306
|
|
|
* |
|
1307
|
|
|
* @param string $query The query to get the count from. |
|
1308
|
|
|
* |
|
1309
|
|
|
* @return int |
|
1310
|
|
|
*/ |
|
1311
|
|
|
private function getPagerCount($query): int |
|
1312
|
|
|
{ |
|
1313
|
|
|
$sql = sprintf( |
|
1314
|
|
|
'SELECT COUNT(z.id) AS count FROM (%s LIMIT %s) z', |
|
1315
|
|
|
preg_replace('/SELECT.+?FROM\s+releases/is', 'SELECT r.id FROM releases', $query), |
|
1316
|
|
|
config('nntmux.max_pager_results') |
|
1317
|
|
|
); |
|
1318
|
|
|
$count = Cache::get(md5($sql)); |
|
1319
|
|
|
if ($count !== null) { |
|
1320
|
|
|
return $count; |
|
1321
|
|
|
} |
|
1322
|
|
|
$count = Release::fromQuery($sql); |
|
1323
|
|
|
$expiresAt = now()->addMinutes(config('nntmux.cache_expiry_short')); |
|
1324
|
|
|
Cache::put(md5($sql), $count[0]->count, $expiresAt); |
|
1325
|
|
|
|
|
1326
|
|
|
return $count[0]->count ?? 0; |
|
1327
|
|
|
} |
|
1328
|
|
|
} |
|
1329
|
|
|
|
NNTmux /
newznab-tmux
Loading history...
As per the PSR-2 coding standard, case statements should not be wrapped in curly braces. There is no need for braces, since each case is terminated by the next
break.There is also the option to use a semicolon instead of a colon, this is discouraged because many programmers do not even know it works and the colon is universal between programming languages.
To learn more about the PSR-2 coding standard, please refer to the PHP-Fig.