1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* JournalCollector.php |
4
|
|
|
* Copyright (c) 2017 [email protected] |
5
|
|
|
* |
6
|
|
|
* This file is part of Firefly III. |
7
|
|
|
* |
8
|
|
|
* Firefly III is free software: you can redistribute it and/or modify |
9
|
|
|
* it under the terms of the GNU General Public License as published by |
10
|
|
|
* the Free Software Foundation, either version 3 of the License, or |
11
|
|
|
* (at your option) any later version. |
12
|
|
|
* |
13
|
|
|
* Firefly III is distributed in the hope that it will be useful, |
14
|
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
15
|
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
16
|
|
|
* GNU General Public License for more details. |
17
|
|
|
* |
18
|
|
|
* You should have received a copy of the GNU General Public License |
19
|
|
|
* along with Firefly III. If not, see <http://www.gnu.org/licenses/>. |
20
|
|
|
*/ |
21
|
|
|
|
22
|
|
|
declare(strict_types=1); |
23
|
|
|
|
24
|
|
|
namespace FireflyIII\Helpers\Collector; |
25
|
|
|
|
26
|
|
|
|
27
|
|
|
use Carbon\Carbon; |
28
|
|
|
use DB; |
29
|
|
|
use FireflyIII\Exceptions\FireflyException; |
30
|
|
|
use FireflyIII\Helpers\Filter\FilterInterface; |
31
|
|
|
use FireflyIII\Helpers\Filter\InternalTransferFilter; |
32
|
|
|
use FireflyIII\Helpers\Filter\NegativeAmountFilter; |
33
|
|
|
use FireflyIII\Helpers\Filter\OpposingAccountFilter; |
34
|
|
|
use FireflyIII\Helpers\Filter\PositiveAmountFilter; |
35
|
|
|
use FireflyIII\Helpers\Filter\TransferFilter; |
36
|
|
|
use FireflyIII\Models\AccountType; |
37
|
|
|
use FireflyIII\Models\Budget; |
38
|
|
|
use FireflyIII\Models\Category; |
39
|
|
|
use FireflyIII\Models\Tag; |
40
|
|
|
use FireflyIII\Models\Transaction; |
41
|
|
|
use FireflyIII\Repositories\Account\AccountRepositoryInterface; |
42
|
|
|
use FireflyIII\User; |
43
|
|
|
use Illuminate\Database\Eloquent\Builder as EloquentBuilder; |
44
|
|
|
use Illuminate\Database\Query\JoinClause; |
45
|
|
|
use Illuminate\Pagination\LengthAwarePaginator; |
46
|
|
|
use Illuminate\Support\Collection; |
47
|
|
|
use Log; |
48
|
|
|
use Steam; |
49
|
|
|
|
50
|
|
|
/** |
51
|
|
|
* Maybe this is a good idea after all... |
52
|
|
|
* |
53
|
|
|
* Class JournalCollector |
54
|
|
|
* |
55
|
|
|
* @package FireflyIII\Helpers\Collector |
56
|
|
|
* |
57
|
|
|
* @SuppressWarnings(PHPMD.ExcessiveClassComplexity) |
58
|
|
|
* @SuppressWarnings(PHPMD.CouplingBetweenObjects) |
59
|
|
|
*/ |
60
|
|
|
class JournalCollector implements JournalCollectorInterface |
61
|
|
|
{ |
62
|
|
|
|
63
|
|
|
/** @var array */ |
64
|
|
|
private $accountIds = []; |
65
|
|
|
/** @var int */ |
66
|
|
|
private $count = 0; |
67
|
|
|
/** @var array */ |
68
|
|
|
private $fields |
69
|
|
|
= [ |
70
|
|
|
'transaction_journals.id as journal_id', |
71
|
|
|
'transaction_journals.description', |
72
|
|
|
'transaction_journals.date', |
73
|
|
|
'transaction_journals.encrypted', |
74
|
|
|
'transaction_types.type as transaction_type_type', |
75
|
|
|
'transaction_journals.bill_id', |
76
|
|
|
'bills.name as bill_name', |
77
|
|
|
'bills.name_encrypted as bill_name_encrypted', |
78
|
|
|
|
79
|
|
|
'transactions.id as id', |
80
|
|
|
'transactions.description as transaction_description', |
81
|
|
|
'transactions.account_id', |
82
|
|
|
'transactions.identifier', |
83
|
|
|
'transactions.transaction_journal_id', |
84
|
|
|
'transactions.amount as transaction_amount', |
85
|
|
|
'transactions.transaction_currency_id as transaction_currency_id', |
86
|
|
|
|
87
|
|
|
'transaction_currencies.code as transaction_currency_code', |
88
|
|
|
'transaction_currencies.symbol as transaction_currency_symbol', |
89
|
|
|
'transaction_currencies.decimal_places as transaction_currency_dp', |
90
|
|
|
|
91
|
|
|
'transactions.foreign_amount as transaction_foreign_amount', |
92
|
|
|
'transactions.foreign_currency_id as foreign_currency_id', |
93
|
|
|
|
94
|
|
|
'foreign_currencies.code as foreign_currency_code', |
95
|
|
|
'foreign_currencies.symbol as foreign_currency_symbol', |
96
|
|
|
'foreign_currencies.decimal_places as foreign_currency_dp', |
97
|
|
|
|
98
|
|
|
'accounts.name as account_name', |
99
|
|
|
'accounts.encrypted as account_encrypted', |
100
|
|
|
'accounts.iban as account_iban', |
101
|
|
|
'account_types.type as account_type', |
102
|
|
|
|
103
|
|
|
]; |
104
|
|
|
/** @var array */ |
105
|
|
|
private $filters = [InternalTransferFilter::class]; |
106
|
|
|
|
107
|
|
|
/** @var bool */ |
108
|
|
|
private $joinedBudget = false; |
109
|
|
|
/** @var bool */ |
110
|
|
|
private $joinedCategory = false; |
111
|
|
|
/** @var bool */ |
112
|
|
|
private $joinedOpposing = false; |
113
|
|
|
/** @var bool */ |
114
|
|
|
private $joinedTag = false; |
115
|
|
|
/** @var int */ |
116
|
|
|
private $limit; |
117
|
|
|
/** @var int */ |
118
|
|
|
private $offset; |
119
|
|
|
/** @var int */ |
120
|
|
|
private $page = 1; |
121
|
|
|
/** @var EloquentBuilder */ |
122
|
|
|
private $query; |
123
|
|
|
/** @var bool */ |
124
|
|
|
private $run = false; |
125
|
|
|
/** @var User */ |
126
|
|
|
private $user; |
127
|
|
|
|
128
|
|
|
/** |
129
|
|
|
* @param string $filter |
130
|
|
|
* |
131
|
|
|
* @return JournalCollectorInterface |
132
|
|
|
*/ |
133
|
|
|
public function addFilter(string $filter): JournalCollectorInterface |
134
|
|
|
{ |
135
|
|
|
$interfaces = class_implements($filter); |
136
|
|
|
if (in_array(FilterInterface::class, $interfaces) && !in_array($filter, $this->filters)) { |
137
|
|
|
Log::debug(sprintf('Enabled filter %s', $filter)); |
138
|
|
|
$this->filters[] = $filter; |
139
|
|
|
} |
140
|
|
|
|
141
|
|
|
return $this; |
142
|
|
|
} |
143
|
|
|
|
144
|
|
|
/** |
145
|
|
|
* @param string $amount |
146
|
|
|
* |
147
|
|
|
* @return JournalCollectorInterface |
148
|
|
|
*/ |
149
|
|
|
public function amountIs(string $amount): JournalCollectorInterface |
150
|
|
|
{ |
151
|
|
|
$this->query->where( |
152
|
|
|
function (EloquentBuilder $q) use ($amount) { |
153
|
|
|
$q->where('transactions.amount', $amount); |
154
|
|
|
$q->orWhere('transactions.amount', bcmul($amount, '-1')); |
155
|
|
|
} |
156
|
|
|
); |
157
|
|
|
|
158
|
|
|
return $this; |
159
|
|
|
} |
160
|
|
|
|
161
|
|
|
/** |
162
|
|
|
* @param string $amount |
163
|
|
|
* |
164
|
|
|
* @return JournalCollectorInterface |
165
|
|
|
*/ |
166
|
|
|
public function amountLess(string $amount): JournalCollectorInterface |
167
|
|
|
{ |
168
|
|
|
$this->query->where( |
169
|
|
|
function (EloquentBuilder $q1) use ($amount) { |
170
|
|
|
$q1->where( |
171
|
|
|
function (EloquentBuilder $q2) use ($amount) { |
172
|
|
|
// amount < 0 and .amount > -$amount |
173
|
|
|
$amount = bcmul($amount,'-1'); |
|
|
|
|
174
|
|
|
$q2->where('transactions.amount', '<', 0)->where('transactions.amount', '>', $amount); |
175
|
|
|
} |
176
|
|
|
) |
177
|
|
|
->orWhere( |
178
|
|
|
function (EloquentBuilder $q3) use ($amount) { |
179
|
|
|
// amount > 0 and .amount < $amount |
180
|
|
|
$q3->where('transactions.amount', '>', 0)->where('transactions.amount', '<', $amount); |
181
|
|
|
} |
182
|
|
|
); |
183
|
|
|
} |
184
|
|
|
); |
185
|
|
|
|
186
|
|
|
return $this; |
187
|
|
|
} |
188
|
|
|
|
189
|
|
|
/** |
190
|
|
|
* @param string $amount |
191
|
|
|
* |
192
|
|
|
* @return JournalCollectorInterface |
193
|
|
|
*/ |
194
|
|
|
public function amountMore(string $amount): JournalCollectorInterface |
195
|
|
|
{ |
196
|
|
|
$this->query->where( |
197
|
|
|
function (EloquentBuilder $q1) use ($amount) { |
198
|
|
|
$q1->where( |
199
|
|
|
function (EloquentBuilder $q2) use ($amount) { |
200
|
|
|
// amount < 0 and .amount < -$amount |
201
|
|
|
$amount = bcmul($amount,'-1'); |
|
|
|
|
202
|
|
|
$q2->where('transactions.amount', '<', 0)->where('transactions.amount', '<', $amount); |
203
|
|
|
} |
204
|
|
|
) |
205
|
|
|
->orWhere( |
206
|
|
|
function (EloquentBuilder $q3) use ($amount) { |
207
|
|
|
// amount > 0 and .amount > $amount |
208
|
|
|
$q3->where('transactions.amount', '>', 0)->where('transactions.amount', '>', $amount); |
209
|
|
|
} |
210
|
|
|
); |
211
|
|
|
} |
212
|
|
|
); |
213
|
|
|
return $this; |
214
|
|
|
} |
215
|
|
|
|
216
|
|
|
/** |
217
|
|
|
* @return int |
218
|
|
|
* @throws FireflyException |
219
|
|
|
*/ |
220
|
|
|
public function count(): int |
221
|
|
|
{ |
222
|
|
|
if ($this->run === true) { |
223
|
|
|
throw new FireflyException('Cannot count after run in JournalCollector.'); |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
$countQuery = clone $this->query; |
227
|
|
|
|
228
|
|
|
// dont need some fields: |
229
|
|
|
$countQuery->getQuery()->limit = null; |
230
|
|
|
$countQuery->getQuery()->offset = null; |
231
|
|
|
$countQuery->getQuery()->unionLimit = null; |
232
|
|
|
$countQuery->getQuery()->groups = null; |
233
|
|
|
$countQuery->getQuery()->orders = null; |
234
|
|
|
$countQuery->groupBy('accounts.user_id'); |
235
|
|
|
$this->count = $countQuery->count(); |
236
|
|
|
|
237
|
|
|
return $this->count; |
238
|
|
|
} |
239
|
|
|
|
240
|
|
|
/** |
241
|
|
|
* @return Collection |
242
|
|
|
*/ |
243
|
|
|
public function getJournals(): Collection |
244
|
|
|
{ |
245
|
|
|
$this->run = true; |
246
|
|
|
/** @var Collection $set */ |
247
|
|
|
$set = $this->query->get(array_values($this->fields)); |
248
|
|
|
|
249
|
|
|
// run all filters: |
250
|
|
|
$set = $this->filter($set); |
251
|
|
|
|
252
|
|
|
// loop for decryption. |
253
|
|
|
$set->each( |
254
|
|
|
function (Transaction $transaction) { |
255
|
|
|
$transaction->date = new Carbon($transaction->date); |
256
|
|
|
$transaction->description = Steam::decrypt(intval($transaction->encrypted), $transaction->description); |
257
|
|
|
|
258
|
|
|
if (!is_null($transaction->bill_name)) { |
259
|
|
|
$transaction->bill_name = Steam::decrypt(intval($transaction->bill_name_encrypted), $transaction->bill_name); |
260
|
|
|
} |
261
|
|
|
$transaction->opposing_account_name = app('steam')->tryDecrypt($transaction->opposing_account_name); |
262
|
|
|
$transaction->account_iban = app('steam')->tryDecrypt($transaction->account_iban); |
263
|
|
|
$transaction->opposing_account_iban = app('steam')->tryDecrypt($transaction->opposing_account_iban); |
264
|
|
|
|
265
|
|
|
|
266
|
|
|
} |
267
|
|
|
); |
268
|
|
|
|
269
|
|
|
return $set; |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
/** |
273
|
|
|
* @return LengthAwarePaginator |
274
|
|
|
* @throws FireflyException |
275
|
|
|
*/ |
276
|
|
|
public function getPaginatedJournals(): LengthAwarePaginator |
277
|
|
|
{ |
278
|
|
|
if ($this->run === true) { |
279
|
|
|
throw new FireflyException('Cannot getPaginatedJournals after run in JournalCollector.'); |
280
|
|
|
} |
281
|
|
|
$this->count(); |
282
|
|
|
$set = $this->getJournals(); |
283
|
|
|
$journals = new LengthAwarePaginator($set, $this->count, $this->limit, $this->page); |
284
|
|
|
|
285
|
|
|
return $journals; |
286
|
|
|
} |
287
|
|
|
|
288
|
|
|
/** |
289
|
|
|
* @param string $filter |
290
|
|
|
* |
291
|
|
|
* @return JournalCollectorInterface |
292
|
|
|
*/ |
293
|
|
|
public function removeFilter(string $filter): JournalCollectorInterface |
294
|
|
|
{ |
295
|
|
|
$key = array_search($filter, $this->filters, true); |
296
|
|
|
if (!($key === false)) { |
297
|
|
|
Log::debug(sprintf('Removed filter %s', $filter)); |
298
|
|
|
unset($this->filters[$key]); |
299
|
|
|
} |
300
|
|
|
|
301
|
|
|
return $this; |
302
|
|
|
} |
303
|
|
|
|
304
|
|
|
/** |
305
|
|
|
* @param Collection $accounts |
306
|
|
|
* |
307
|
|
|
* @return JournalCollectorInterface |
308
|
|
|
*/ |
309
|
|
|
public function setAccounts(Collection $accounts): JournalCollectorInterface |
310
|
|
|
{ |
311
|
|
|
if ($accounts->count() > 0) { |
312
|
|
|
$accountIds = $accounts->pluck('id')->toArray(); |
313
|
|
|
$this->query->whereIn('transactions.account_id', $accountIds); |
314
|
|
|
Log::debug(sprintf('setAccounts: %s', join(', ', $accountIds))); |
315
|
|
|
$this->accountIds = $accountIds; |
316
|
|
|
} |
317
|
|
|
|
318
|
|
|
if ($accounts->count() > 1) { |
319
|
|
|
$this->addFilter(TransferFilter::class); |
320
|
|
|
} |
321
|
|
|
|
322
|
|
|
|
323
|
|
|
return $this; |
324
|
|
|
} |
325
|
|
|
|
326
|
|
|
/** |
327
|
|
|
* @param Carbon $after |
328
|
|
|
* |
329
|
|
|
* @return JournalCollectorInterface |
330
|
|
|
*/ |
331
|
|
|
public function setAfter(Carbon $after): JournalCollectorInterface |
332
|
|
|
{ |
333
|
|
|
$afterStr = $after->format('Y-m-d'); |
334
|
|
|
$this->query->where('transaction_journals.date', '>=', $afterStr); |
335
|
|
|
Log::debug(sprintf('JournalCollector range is now after %s (inclusive)', $afterStr)); |
336
|
|
|
|
337
|
|
|
return $this; |
338
|
|
|
} |
339
|
|
|
|
340
|
|
|
/** |
341
|
|
|
* @return JournalCollectorInterface |
342
|
|
|
*/ |
343
|
|
|
public function setAllAssetAccounts(): JournalCollectorInterface |
344
|
|
|
{ |
345
|
|
|
/** @var AccountRepositoryInterface $repository */ |
346
|
|
|
$repository = app(AccountRepositoryInterface::class); |
347
|
|
|
$repository->setUser($this->user); |
348
|
|
|
$accounts = $repository->getAccountsByType([AccountType::ASSET, AccountType::DEFAULT]); |
349
|
|
|
if ($accounts->count() > 0) { |
350
|
|
|
$accountIds = $accounts->pluck('id')->toArray(); |
351
|
|
|
$this->query->whereIn('transactions.account_id', $accountIds); |
352
|
|
|
$this->accountIds = $accountIds; |
353
|
|
|
} |
354
|
|
|
|
355
|
|
|
if ($accounts->count() > 1) { |
356
|
|
|
$this->addFilter(TransferFilter::class); |
357
|
|
|
} |
358
|
|
|
|
359
|
|
|
return $this; |
360
|
|
|
} |
361
|
|
|
|
362
|
|
|
/** |
363
|
|
|
* @param Carbon $before |
364
|
|
|
* |
365
|
|
|
* @return JournalCollectorInterface |
366
|
|
|
*/ |
367
|
|
|
public function setBefore(Carbon $before): JournalCollectorInterface |
368
|
|
|
{ |
369
|
|
|
$beforeStr = $before->format('Y-m-d'); |
370
|
|
|
$this->query->where('transaction_journals.date', '<=', $beforeStr); |
371
|
|
|
Log::debug(sprintf('JournalCollector range is now before %s (inclusive)', $beforeStr)); |
372
|
|
|
|
373
|
|
|
return $this; |
374
|
|
|
} |
375
|
|
|
|
376
|
|
|
/** |
377
|
|
|
* @param Collection $bills |
378
|
|
|
* |
379
|
|
|
* @return JournalCollectorInterface |
380
|
|
|
*/ |
381
|
|
|
public function setBills(Collection $bills): JournalCollectorInterface |
382
|
|
|
{ |
383
|
|
|
if ($bills->count() > 0) { |
384
|
|
|
$billIds = $bills->pluck('id')->toArray(); |
385
|
|
|
$this->query->whereIn('transaction_journals.bill_id', $billIds); |
386
|
|
|
} |
387
|
|
|
|
388
|
|
|
return $this; |
389
|
|
|
|
390
|
|
|
} |
391
|
|
|
|
392
|
|
|
/** |
393
|
|
|
* @param Budget $budget |
394
|
|
|
* |
395
|
|
|
* @return JournalCollectorInterface |
396
|
|
|
*/ |
397
|
|
|
public function setBudget(Budget $budget): JournalCollectorInterface |
398
|
|
|
{ |
399
|
|
|
$this->joinBudgetTables(); |
400
|
|
|
|
401
|
|
|
$this->query->where( |
402
|
|
|
function (EloquentBuilder $q) use ($budget) { |
403
|
|
|
$q->where('budget_transaction.budget_id', $budget->id); |
404
|
|
|
$q->orWhere('budget_transaction_journal.budget_id', $budget->id); |
405
|
|
|
} |
406
|
|
|
); |
407
|
|
|
|
408
|
|
|
return $this; |
409
|
|
|
} |
410
|
|
|
|
411
|
|
|
/** |
412
|
|
|
* @param Collection $budgets |
413
|
|
|
* |
414
|
|
|
* @return JournalCollectorInterface |
415
|
|
|
*/ |
416
|
|
|
public function setBudgets(Collection $budgets): JournalCollectorInterface |
417
|
|
|
{ |
418
|
|
|
$budgetIds = $budgets->pluck('id')->toArray(); |
419
|
|
|
if (count($budgetIds) === 0) { |
420
|
|
|
return $this; |
421
|
|
|
} |
422
|
|
|
$this->joinBudgetTables(); |
423
|
|
|
Log::debug('Journal collector will filter for budgets', $budgetIds); |
424
|
|
|
|
425
|
|
|
$this->query->where( |
426
|
|
|
function (EloquentBuilder $q) use ($budgetIds) { |
427
|
|
|
$q->whereIn('budget_transaction.budget_id', $budgetIds); |
428
|
|
|
$q->orWhereIn('budget_transaction_journal.budget_id', $budgetIds); |
429
|
|
|
} |
430
|
|
|
); |
431
|
|
|
|
432
|
|
|
return $this; |
433
|
|
|
} |
434
|
|
|
|
435
|
|
|
/** |
436
|
|
|
* @param Collection $categories |
437
|
|
|
* |
438
|
|
|
* @return JournalCollectorInterface |
439
|
|
|
*/ |
440
|
|
|
public function setCategories(Collection $categories): JournalCollectorInterface |
441
|
|
|
{ |
442
|
|
|
$categoryIds = $categories->pluck('id')->toArray(); |
443
|
|
|
if (count($categoryIds) === 0) { |
444
|
|
|
return $this; |
445
|
|
|
} |
446
|
|
|
$this->joinCategoryTables(); |
447
|
|
|
|
448
|
|
|
$this->query->where( |
449
|
|
|
function (EloquentBuilder $q) use ($categoryIds) { |
450
|
|
|
$q->whereIn('category_transaction.category_id', $categoryIds); |
451
|
|
|
$q->orWhereIn('category_transaction_journal.category_id', $categoryIds); |
452
|
|
|
} |
453
|
|
|
); |
454
|
|
|
|
455
|
|
|
return $this; |
456
|
|
|
} |
457
|
|
|
|
458
|
|
|
/** |
459
|
|
|
* @param Category $category |
460
|
|
|
* |
461
|
|
|
* @return JournalCollectorInterface |
462
|
|
|
*/ |
463
|
|
|
public function setCategory(Category $category): JournalCollectorInterface |
464
|
|
|
{ |
465
|
|
|
$this->joinCategoryTables(); |
466
|
|
|
|
467
|
|
|
$this->query->where( |
468
|
|
|
function (EloquentBuilder $q) use ($category) { |
469
|
|
|
$q->where('category_transaction.category_id', $category->id); |
470
|
|
|
$q->orWhere('category_transaction_journal.category_id', $category->id); |
471
|
|
|
} |
472
|
|
|
); |
473
|
|
|
|
474
|
|
|
return $this; |
475
|
|
|
} |
476
|
|
|
|
477
|
|
|
/** |
478
|
|
|
* @param int $limit |
479
|
|
|
* |
480
|
|
|
* @return JournalCollectorInterface |
481
|
|
|
*/ |
482
|
|
|
public function setLimit(int $limit): JournalCollectorInterface |
483
|
|
|
{ |
484
|
|
|
$this->limit = $limit; |
485
|
|
|
$this->query->limit($limit); |
486
|
|
|
Log::debug(sprintf('Set limit to %d', $limit)); |
487
|
|
|
|
488
|
|
|
return $this; |
489
|
|
|
} |
490
|
|
|
|
491
|
|
|
/** |
492
|
|
|
* @param int $offset |
493
|
|
|
* |
494
|
|
|
* @return JournalCollectorInterface |
495
|
|
|
*/ |
496
|
|
|
public function setOffset(int $offset): JournalCollectorInterface |
497
|
|
|
{ |
498
|
|
|
$this->offset = $offset; |
499
|
|
|
|
500
|
|
|
return $this; |
501
|
|
|
} |
502
|
|
|
|
503
|
|
|
/** |
504
|
|
|
* @param int $page |
505
|
|
|
* |
506
|
|
|
* @return JournalCollectorInterface |
507
|
|
|
*/ |
508
|
|
|
public function setPage(int $page): JournalCollectorInterface |
509
|
|
|
{ |
510
|
|
|
if ($page < 1) { |
511
|
|
|
$page = 1; |
512
|
|
|
} |
513
|
|
|
|
514
|
|
|
$this->page = $page; |
515
|
|
|
|
516
|
|
|
if ($page > 0) { |
517
|
|
|
$page--; |
518
|
|
|
} |
519
|
|
|
Log::debug(sprintf('Page is %d', $page)); |
520
|
|
|
|
521
|
|
|
if (!is_null($this->limit)) { |
522
|
|
|
$offset = ($this->limit * $page); |
523
|
|
|
$this->offset = $offset; |
524
|
|
|
$this->query->skip($offset); |
525
|
|
|
Log::debug(sprintf('Changed offset to %d', $offset)); |
526
|
|
|
|
527
|
|
|
return $this; |
528
|
|
|
} |
529
|
|
|
Log::debug('The limit is zero, cannot set the page.'); |
530
|
|
|
|
531
|
|
|
return $this; |
532
|
|
|
} |
533
|
|
|
|
534
|
|
|
/** |
535
|
|
|
* @param Carbon $start |
536
|
|
|
* @param Carbon $end |
537
|
|
|
* |
538
|
|
|
* @return JournalCollectorInterface |
539
|
|
|
*/ |
540
|
|
|
public function setRange(Carbon $start, Carbon $end): JournalCollectorInterface |
541
|
|
|
{ |
542
|
|
|
if ($start <= $end) { |
543
|
|
|
$startStr = $start->format('Y-m-d'); |
544
|
|
|
$endStr = $end->format('Y-m-d'); |
545
|
|
|
$this->query->where('transaction_journals.date', '>=', $startStr); |
546
|
|
|
$this->query->where('transaction_journals.date', '<=', $endStr); |
547
|
|
|
Log::debug(sprintf('JournalCollector range is now %s - %s (inclusive)', $startStr, $endStr)); |
548
|
|
|
} |
549
|
|
|
|
550
|
|
|
return $this; |
551
|
|
|
} |
552
|
|
|
|
553
|
|
|
/** |
554
|
|
|
* @param Tag $tag |
555
|
|
|
* |
556
|
|
|
* @return JournalCollectorInterface |
557
|
|
|
*/ |
558
|
|
|
public function setTag(Tag $tag): JournalCollectorInterface |
559
|
|
|
{ |
560
|
|
|
$this->joinTagTables(); |
561
|
|
|
$this->query->where('tag_transaction_journal.tag_id', $tag->id); |
562
|
|
|
|
563
|
|
|
return $this; |
564
|
|
|
} |
565
|
|
|
|
566
|
|
|
/** |
567
|
|
|
* @param Collection $tags |
568
|
|
|
* |
569
|
|
|
* @return JournalCollectorInterface |
570
|
|
|
*/ |
571
|
|
|
public function setTags(Collection $tags): JournalCollectorInterface |
572
|
|
|
{ |
573
|
|
|
$this->joinTagTables(); |
574
|
|
|
$tagIds = $tags->pluck('id')->toArray(); |
575
|
|
|
$this->query->whereIn('tag_transaction_journal.tag_id', $tagIds); |
576
|
|
|
|
577
|
|
|
return $this; |
578
|
|
|
} |
579
|
|
|
|
580
|
|
|
/** |
581
|
|
|
* @param array $types |
582
|
|
|
* |
583
|
|
|
* @return JournalCollectorInterface |
584
|
|
|
*/ |
585
|
|
|
public function setTypes(array $types): JournalCollectorInterface |
586
|
|
|
{ |
587
|
|
|
if (count($types) > 0) { |
588
|
|
|
Log::debug('Set query collector types', $types); |
589
|
|
|
$this->query->whereIn('transaction_types.type', $types); |
590
|
|
|
} |
591
|
|
|
|
592
|
|
|
return $this; |
593
|
|
|
} |
594
|
|
|
|
595
|
|
|
/** |
596
|
|
|
* @param User $user |
597
|
|
|
*/ |
598
|
|
|
public function setUser(User $user) |
599
|
|
|
{ |
600
|
|
|
Log::debug(sprintf('Journal collector now collecting for user #%d', $user->id)); |
601
|
|
|
$this->user = $user; |
602
|
|
|
$this->startQuery(); |
603
|
|
|
} |
604
|
|
|
|
605
|
|
|
/** |
606
|
|
|
* |
607
|
|
|
*/ |
608
|
|
|
public function startQuery() |
609
|
|
|
{ |
610
|
|
|
Log::debug('journalCollector::startQuery'); |
611
|
|
|
/** @var EloquentBuilder $query */ |
612
|
|
|
$query = Transaction::leftJoin('transaction_journals', 'transaction_journals.id', '=', 'transactions.transaction_journal_id') |
613
|
|
|
->leftJoin('transaction_types', 'transaction_types.id', 'transaction_journals.transaction_type_id') |
614
|
|
|
->leftJoin('bills', 'bills.id', 'transaction_journals.bill_id') |
615
|
|
|
->leftJoin('accounts', 'accounts.id', '=', 'transactions.account_id') |
616
|
|
|
->leftJoin('account_types', 'accounts.account_type_id', 'account_types.id') |
617
|
|
|
->leftJoin('transaction_currencies', 'transaction_currencies.id', 'transactions.transaction_currency_id') |
618
|
|
|
->leftJoin('transaction_currencies as foreign_currencies', 'foreign_currencies.id', 'transactions.foreign_currency_id') |
619
|
|
|
->whereNull('transactions.deleted_at') |
620
|
|
|
->whereNull('transaction_journals.deleted_at') |
621
|
|
|
->where('transaction_journals.user_id', $this->user->id) |
622
|
|
|
->orderBy('transaction_journals.date', 'DESC') |
623
|
|
|
->orderBy('transaction_journals.order', 'ASC') |
624
|
|
|
->orderBy('transaction_journals.id', 'DESC') |
625
|
|
|
->orderBy('transaction_journals.description', 'DESC') |
626
|
|
|
->orderBy('transactions.amount', 'DESC'); |
627
|
|
|
|
628
|
|
|
$this->query = $query; |
629
|
|
|
|
630
|
|
|
} |
631
|
|
|
|
632
|
|
|
/** |
633
|
|
|
* @return JournalCollectorInterface |
634
|
|
|
*/ |
635
|
|
|
public function withBudgetInformation(): JournalCollectorInterface |
636
|
|
|
{ |
637
|
|
|
$this->joinBudgetTables(); |
638
|
|
|
|
639
|
|
|
return $this; |
640
|
|
|
} |
641
|
|
|
|
642
|
|
|
/** |
643
|
|
|
* @return JournalCollectorInterface |
644
|
|
|
*/ |
645
|
|
|
public function withCategoryInformation(): JournalCollectorInterface |
646
|
|
|
{ |
647
|
|
|
|
648
|
|
|
$this->joinCategoryTables(); |
649
|
|
|
|
650
|
|
|
return $this; |
651
|
|
|
} |
652
|
|
|
|
653
|
|
|
/** |
654
|
|
|
* @return JournalCollectorInterface |
655
|
|
|
*/ |
656
|
|
|
public function withOpposingAccount(): JournalCollectorInterface |
657
|
|
|
{ |
658
|
|
|
$this->joinOpposingTables(); |
659
|
|
|
|
660
|
|
|
return $this; |
661
|
|
|
} |
662
|
|
|
|
663
|
|
|
/** |
664
|
|
|
* @return JournalCollectorInterface |
665
|
|
|
*/ |
666
|
|
|
public function withoutBudget(): JournalCollectorInterface |
667
|
|
|
{ |
668
|
|
|
$this->joinBudgetTables(); |
669
|
|
|
|
670
|
|
|
$this->query->where( |
671
|
|
|
function (EloquentBuilder $q) { |
672
|
|
|
$q->whereNull('budget_transaction.budget_id'); |
673
|
|
|
$q->whereNull('budget_transaction_journal.budget_id'); |
674
|
|
|
} |
675
|
|
|
); |
676
|
|
|
|
677
|
|
|
return $this; |
678
|
|
|
} |
679
|
|
|
|
680
|
|
|
/** |
681
|
|
|
* @return JournalCollectorInterface |
682
|
|
|
*/ |
683
|
|
|
public function withoutCategory(): JournalCollectorInterface |
684
|
|
|
{ |
685
|
|
|
$this->joinCategoryTables(); |
686
|
|
|
|
687
|
|
|
$this->query->where( |
688
|
|
|
function (EloquentBuilder $q) { |
689
|
|
|
$q->whereNull('category_transaction.category_id'); |
690
|
|
|
$q->whereNull('category_transaction_journal.category_id'); |
691
|
|
|
} |
692
|
|
|
); |
693
|
|
|
|
694
|
|
|
return $this; |
695
|
|
|
} |
696
|
|
|
|
697
|
|
|
/** |
698
|
|
|
* @param Collection $set |
699
|
|
|
* |
700
|
|
|
* @return Collection |
701
|
|
|
*/ |
702
|
|
|
private function filter(Collection $set): Collection |
703
|
|
|
{ |
704
|
|
|
// create all possible filters: |
705
|
|
|
$filters = [ |
706
|
|
|
InternalTransferFilter::class => new InternalTransferFilter($this->accountIds), |
707
|
|
|
OpposingAccountFilter::class => new OpposingAccountFilter($this->accountIds), |
708
|
|
|
TransferFilter::class => new TransferFilter, |
709
|
|
|
PositiveAmountFilter::class => new PositiveAmountFilter, |
710
|
|
|
NegativeAmountFilter::class => new NegativeAmountFilter, |
711
|
|
|
]; |
712
|
|
|
Log::debug(sprintf('Will run %d filters on the set.', count($this->filters))); |
713
|
|
|
foreach ($this->filters as $enabled) { |
714
|
|
|
if (isset($filters[$enabled])) { |
715
|
|
|
Log::debug(sprintf('Before filter %s: %d', $enabled, $set->count())); |
716
|
|
|
$set = $filters[$enabled]->filter($set); |
717
|
|
|
Log::debug(sprintf('After filter %s: %d', $enabled, $set->count())); |
718
|
|
|
} |
719
|
|
|
} |
720
|
|
|
|
721
|
|
|
return $set; |
722
|
|
|
} |
723
|
|
|
|
724
|
|
|
/** |
725
|
|
|
* |
726
|
|
|
*/ |
727
|
|
|
private function joinBudgetTables() |
728
|
|
|
{ |
729
|
|
|
if (!$this->joinedBudget) { |
730
|
|
|
// join some extra tables: |
731
|
|
|
$this->joinedBudget = true; |
732
|
|
|
$this->query->leftJoin('budget_transaction_journal', 'budget_transaction_journal.transaction_journal_id', '=', 'transaction_journals.id'); |
733
|
|
|
$this->query->leftJoin('budgets as transaction_journal_budgets', 'transaction_journal_budgets.id', '=', 'budget_transaction_journal.budget_id'); |
734
|
|
|
$this->query->leftJoin('budget_transaction', 'budget_transaction.transaction_id', '=', 'transactions.id'); |
735
|
|
|
$this->query->leftJoin('budgets as transaction_budgets', 'transaction_budgets.id', '=', 'budget_transaction.budget_id'); |
736
|
|
|
$this->query->whereNull('transaction_journal_budgets.deleted_at'); |
737
|
|
|
$this->query->whereNull('transaction_budgets.deleted_at'); |
738
|
|
|
|
739
|
|
|
$this->fields[] = 'budget_transaction_journal.budget_id as transaction_journal_budget_id'; |
740
|
|
|
$this->fields[] = 'transaction_journal_budgets.encrypted as transaction_journal_budget_encrypted'; |
741
|
|
|
$this->fields[] = 'transaction_journal_budgets.name as transaction_journal_budget_name'; |
742
|
|
|
|
743
|
|
|
$this->fields[] = 'budget_transaction.budget_id as transaction_budget_id'; |
744
|
|
|
$this->fields[] = 'transaction_budgets.encrypted as transaction_budget_encrypted'; |
745
|
|
|
$this->fields[] = 'transaction_budgets.name as transaction_budget_name'; |
746
|
|
|
} |
747
|
|
|
} |
748
|
|
|
|
749
|
|
|
/** |
750
|
|
|
* |
751
|
|
|
*/ |
752
|
|
|
private function joinCategoryTables() |
753
|
|
|
{ |
754
|
|
|
if (!$this->joinedCategory) { |
755
|
|
|
// join some extra tables: |
756
|
|
|
$this->joinedCategory = true; |
757
|
|
|
$this->query->leftJoin('category_transaction_journal', 'category_transaction_journal.transaction_journal_id', '=', 'transaction_journals.id'); |
758
|
|
|
$this->query->leftJoin( |
759
|
|
|
'categories as transaction_journal_categories', 'transaction_journal_categories.id', '=', 'category_transaction_journal.category_id' |
760
|
|
|
); |
761
|
|
|
|
762
|
|
|
$this->query->leftJoin('category_transaction', 'category_transaction.transaction_id', '=', 'transactions.id'); |
763
|
|
|
$this->query->leftJoin('categories as transaction_categories', 'transaction_categories.id', '=', 'category_transaction.category_id'); |
764
|
|
|
|
765
|
|
|
$this->fields[] = 'category_transaction_journal.category_id as transaction_journal_category_id'; |
766
|
|
|
$this->fields[] = 'transaction_journal_categories.encrypted as transaction_journal_category_encrypted'; |
767
|
|
|
$this->fields[] = 'transaction_journal_categories.name as transaction_journal_category_name'; |
768
|
|
|
|
769
|
|
|
$this->fields[] = 'category_transaction.category_id as transaction_category_id'; |
770
|
|
|
$this->fields[] = 'transaction_categories.encrypted as transaction_category_encrypted'; |
771
|
|
|
$this->fields[] = 'transaction_categories.name as transaction_category_name'; |
772
|
|
|
} |
773
|
|
|
} |
774
|
|
|
|
775
|
|
|
/** |
776
|
|
|
* |
777
|
|
|
*/ |
778
|
|
|
private function joinOpposingTables() |
779
|
|
|
{ |
780
|
|
|
if (!$this->joinedOpposing) { |
781
|
|
|
Log::debug('joinedOpposing is false'); |
782
|
|
|
// join opposing transaction (hard): |
783
|
|
|
$this->query->leftJoin( |
784
|
|
|
'transactions as opposing', function (JoinClause $join) { |
785
|
|
|
$join->on('opposing.transaction_journal_id', '=', 'transactions.transaction_journal_id') |
786
|
|
|
->where('opposing.identifier', '=', DB::raw('transactions.identifier')) |
787
|
|
|
->where('opposing.amount', '=', DB::raw('transactions.amount * -1')); |
788
|
|
|
} |
789
|
|
|
); |
790
|
|
|
$this->query->leftJoin('accounts as opposing_accounts', 'opposing.account_id', '=', 'opposing_accounts.id'); |
791
|
|
|
$this->query->leftJoin('account_types as opposing_account_types', 'opposing_accounts.account_type_id', '=', 'opposing_account_types.id'); |
792
|
|
|
$this->query->whereNull('opposing.deleted_at'); |
793
|
|
|
|
794
|
|
|
$this->fields[] = 'opposing.id as opposing_id'; |
795
|
|
|
$this->fields[] = 'opposing.account_id as opposing_account_id'; |
796
|
|
|
$this->fields[] = 'opposing_accounts.name as opposing_account_name'; |
797
|
|
|
$this->fields[] = 'opposing_accounts.encrypted as opposing_account_encrypted'; |
798
|
|
|
$this->fields[] = 'opposing_accounts.iban as opposing_account_iban'; |
799
|
|
|
|
800
|
|
|
$this->fields[] = 'opposing_account_types.type as opposing_account_type'; |
801
|
|
|
$this->joinedOpposing = true; |
802
|
|
|
Log::debug('joinedOpposing is now true!'); |
803
|
|
|
} |
804
|
|
|
} |
805
|
|
|
|
806
|
|
|
/** |
807
|
|
|
* |
808
|
|
|
*/ |
809
|
|
|
private function joinTagTables() |
810
|
|
|
{ |
811
|
|
|
if (!$this->joinedTag) { |
812
|
|
|
// join some extra tables: |
813
|
|
|
$this->joinedTag = true; |
814
|
|
|
$this->query->leftJoin('tag_transaction_journal', 'tag_transaction_journal.transaction_journal_id', '=', 'transaction_journals.id'); |
815
|
|
|
} |
816
|
|
|
} |
817
|
|
|
} |
818
|
|
|
|
It seems like you are assigning to a variable which was imported through a
use
statement which was not imported by reference.For clarity, we suggest to use a different name or import by reference depending on whether you would like to have the change visibile in outer-scope.
Change not visible in outer-scope
Change visible in outer-scope