Passed
Push — master ( fdf147...e3de03 )
by James
18:29 queued 09:33
created

VerifyDatabase::reportZeroAmount()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 13
Code Lines 8

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
eloc 8
dl 0
loc 13
rs 10
c 0
b 0
f 0
cc 2
nc 2
nop 0
1
<?php
2
/**
3
 * VerifyDatabase.php
4
 * Copyright (c) 2018 [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
/** @noinspection PhpDynamicAsStaticMethodCallInspection */
23
24
declare(strict_types=1);
25
26
namespace FireflyIII\Console\Commands;
27
28
use DB;
29
use Exception;
30
use FireflyIII\Models\Account;
31
use FireflyIII\Models\AccountMeta;
32
use FireflyIII\Models\AccountType;
33
use FireflyIII\Models\Budget;
34
use FireflyIII\Models\BudgetLimit;
35
use FireflyIII\Models\Category;
36
use FireflyIII\Models\LinkType;
37
use FireflyIII\Models\PiggyBankEvent;
38
use FireflyIII\Models\Transaction;
39
use FireflyIII\Models\TransactionCurrency;
40
use FireflyIII\Models\TransactionJournal;
41
use FireflyIII\Models\TransactionType;
42
use FireflyIII\Repositories\User\UserRepositoryInterface;
43
use FireflyIII\User;
44
use Illuminate\Console\Command;
45
use Illuminate\Database\Eloquent\Builder;
46
use Illuminate\Support\Collection;
47
use Log;
48
use Schema;
49
use stdClass;
50
51
/**
52
 * Class VerifyDatabase.
53
 *
54
 * @SuppressWarnings(PHPMD.ExcessiveClassComplexity)
55
 * @SuppressWarnings(PHPMD.CouplingBetweenObjects)
56
 * @codeCoverageIgnore
57
 */
58
class VerifyDatabase extends Command
59
{
60
    /**
61
     * The console command description.
62
     *
63
     * @var string
64
     */
65
    protected $description = 'Will verify your database.';
66
    /**
67
     * The name and signature of the console command.
68
     *
69
     * @var string
70
     */
71
    protected $signature = 'firefly:verify';
72
73
    /**
74
     * Execute the console command.
75
     */
76
    public function handle(): int
77
    {
78
        // if table does not exist, return false
79
        if (!Schema::hasTable('users')) {
80
            return 1;
81
        }
82
83
        $this->reportEmptyBudgets();
84
        $this->reportEmptyCategories();
85
        $this->reportObject('tag');
86
        $this->reportAccounts();
87
        $this->reportBudgetLimits();
88
        $this->reportSum();
89
        $this->reportJournals();
90
        $this->reportTransactions();
91
        $this->reportDeletedAccounts();
92
        $this->reportNoTransactions();
93
        $this->reportTransfersBudgets();
94
        $this->reportIncorrectJournals();
95
        $this->repairPiggyBanks();
96
        $this->createLinkTypes();
97
        $this->createAccessTokens();
98
        $this->fixDoubleAmounts();
99
        $this->fixBadMeta();
100
        $this->removeBills();
101
        $this->enableCurrencies();
102
        $this->reportZeroAmount();
103
104
        return 0;
105
    }
106
107
    /**
108
     * Create user access tokens, if not present already.
109
     */
110
    private function createAccessTokens(): void
111
    {
112
        $count = 0;
113
        $users = User::get();
114
        /** @var User $user */
115
        foreach ($users as $user) {
116
            $pref = app('preferences')->getForUser($user, 'access_token', null);
117
            if (null === $pref) {
118
                $token = $user->generateAccessToken();
119
                app('preferences')->setForUser($user, 'access_token', $token);
120
                $this->line(sprintf('Generated access token for user %s', $user->email));
121
                ++$count;
122
            }
123
        }
124
        if (0 === $count) {
125
            $this->info('All access tokens OK!');
126
        }
127
    }
128
129
    /**
130
     * Create default link types if necessary.
131
     */
132
    private function createLinkTypes(): void
133
    {
134
        $count = 0;
135
        $set   = [
136
            'Related'       => ['relates to', 'relates to'],
137
            'Refund'        => ['(partially) refunds', 'is (partially) refunded by'],
138
            'Paid'          => ['(partially) pays for', 'is (partially) paid for by'],
139
            'Reimbursement' => ['(partially) reimburses', 'is (partially) reimbursed by'],
140
        ];
141
        foreach ($set as $name => $values) {
142
            $link = LinkType::where('name', $name)->where('outward', $values[0])->where('inward', $values[1])->first();
143
            if (null === $link) {
144
                $link          = new LinkType;
145
                $link->name    = $name;
146
                $link->outward = $values[0];
147
                $link->inward  = $values[1];
148
                ++$count;
149
            }
150
            $link->editable = false;
151
            $link->save();
152
        }
153
        if (0 === $count) {
154
            $this->info('All link types OK!');
155
        }
156
    }
157
158
    /**
159
     * Will make sure that all currencies in use are actually enabled.
160
     */
161
    private function enableCurrencies(): void
162
    {
163
        $found = [];
164
        // get all meta entries
165
        /** @var Collection $meta */
166
        $meta = AccountMeta::where('name', 'currency_id')->groupBy('data')->get(['data']);
167
        foreach ($meta as $entry) {
168
            $found[] = (int)$entry->data;
169
        }
170
171
        // get all from journals:
172
        /** @var Collection $journals */
173
        $journals = TransactionJournal::groupBy('transaction_currency_id')->get(['transaction_currency_id']);
174
        foreach ($journals as $entry) {
175
            $found[] = (int)$entry->transaction_currency_id;
176
        }
177
178
        // get all from transactions
179
        /** @var Collection $transactions */
180
        $transactions = Transaction::groupBy('transaction_currency_id')->get(['transaction_currency_id']);
181
        foreach ($transactions as $entry) {
182
            $found[] = (int)$entry->transaction_currency_id;
183
        }
184
185
        // get all from budget limits
186
        /** @var Collection $limits */
187
        $limits = BudgetLimit::groupBy('transaction_currency_id')->get(['transaction_currency_id']);
188
        foreach ($limits as $entry) {
189
            $found[] = (int)$entry->transaction_currency_id;
190
        }
191
192
        $found = array_unique($found);
193
        TransactionCurrency::whereIn('id', $found)->update(['enabled' => true]);
194
195
    }
196
197
    /**
198
     * Fix the situation where the matching transactions of a journal somehow have non-matching categories or budgets.
199
     *
200
     * @SuppressWarnings(PHPMD.CyclomaticComplexity)
201
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
202
     */
203
    private function fixBadMeta(): void
204
    {
205
        // categories
206
        $set     = Transaction
207
            ::leftJoin('category_transaction', 'category_transaction.transaction_id', '=', 'transactions.id')
208
            ->whereNull('transactions.deleted_at')
209
            ->get(['transactions.id', 'transaction_journal_id', 'identifier', 'category_transaction.category_id', 'category_transaction.id as ct_id']);
210
        $results = [];
211
        foreach ($set as $obj) {
212
            $key      = $obj->transaction_journal_id . '-' . $obj->identifier;
213
            $category = (int)$obj->category_id;
214
215
            // value exists and is not category:
216
            if (isset($results[$key]) && $results[$key] !== $category) {
217
                $this->error(
218
                    sprintf(
219
                        'Transaction #%d referred to the wrong category. Was category #%d but is fixed to be category #%d.', $obj->transaction_journal_id,
220
                        $category, $results[$key]
221
                    )
222
                );
223
                DB::table('category_transaction')->where('id', $obj->ct_id)->update(['category_id' => $results[$key]]);
224
225
            }
226
227
            // value does not exist:
228
            if ($category > 0 && !isset($results[$key])) {
229
                $results[$key] = $category;
230
            }
231
        }
232
233
        // budgets
234
        $set     = Transaction
235
            ::leftJoin('budget_transaction', 'budget_transaction.transaction_id', '=', 'transactions.id')
236
            ->whereNull('transactions.deleted_at')
237
            ->get(['transactions.id', 'transaction_journal_id', 'identifier', 'budget_transaction.budget_id', 'budget_transaction.id as ct_id']);
238
        $results = [];
239
        foreach ($set as $obj) {
240
            $key    = $obj->transaction_journal_id . '-' . $obj->identifier;
241
            $budget = (int)$obj->budget_id;
242
243
            // value exists and is not budget:
244
            if (isset($results[$key]) && $results[$key] !== $budget) {
245
                $this->error(
246
                    sprintf(
247
                        'Transaction #%d referred to the wrong budget. Was budget #%d but is fixed to be budget #%d.', $obj->transaction_journal_id, $budget,
248
                        $results[$key]
249
                    )
250
                );
251
                DB::table('budget_transaction')->where('id', $obj->ct_id)->update(['budget_id' => $results[$key]]);
252
253
            }
254
255
            // value does not exist:
256
            if ($budget > 0 && !isset($results[$key])) {
257
                $results[$key] = $budget;
258
            }
259
        }
260
    }
261
262
    /**
263
     * Makes sure amounts are stored correctly.
264
     *
265
     * @SuppressWarnings(PHPMD.CyclomaticComplexity)
266
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
267
     */
268
    private function fixDoubleAmounts(): void
269
    {
270
        $count = 0;
271
        // get invalid journals
272
        $errored  = [];
273
        $journals = DB::table('transactions')
274
                      ->groupBy('transaction_journal_id')
275
                      ->get(['transaction_journal_id', DB::raw('SUM(amount) AS the_sum')]);
276
        /** @var stdClass $entry */
277
        foreach ($journals as $entry) {
278
            if (0 !== bccomp((string)$entry->the_sum, '0')) {
279
                $errored[] = $entry->transaction_journal_id;
280
            }
281
        }
282
        foreach ($errored as $journalId) {
283
            // select and update:
284
            $res = Transaction::whereNull('deleted_at')->where('transaction_journal_id', $journalId)->groupBy('amount')->get([DB::raw('MIN(id) as first_id')]);
285
            $ids = $res->pluck('first_id')->toArray();
286
            DB::table('transactions')->whereIn('id', $ids)->update(['amount' => DB::raw('amount * -1')]);
287
            ++$count;
288
            // report about it
289
            /** @var TransactionJournal $journal */
290
            $journal = TransactionJournal::find($journalId);
291
            if (null === $journal) {
292
                continue;
293
            }
294
            if (TransactionType::OPENING_BALANCE === $journal->transactionType->type) {
295
                $this->error(
296
                    sprintf(
297
                        'Transaction #%d was stored incorrectly. One of your asset accounts may show the wrong balance. Please visit /transactions/show/%d to verify the opening balance.',
298
                        $journalId, $journalId
299
                    )
300
                );
301
            }
302
            if (TransactionType::OPENING_BALANCE !== $journal->transactionType->type) {
303
                $this->error(
304
                    sprintf(
305
                        'Transaction #%d was stored incorrectly. Could be that the transaction shows the wrong amount. Please visit /transactions/show/%d to verify the opening balance.',
306
                        $journalId, $journalId
307
                    )
308
                );
309
            }
310
        }
311
        if (0 === $count) {
312
            $this->info('Amount integrity OK!');
313
        }
314
    }
315
316
    /**
317
     * Removes bills from journals that should not have bills.
318
     */
319
    private function removeBills(): void
320
    {
321
        /** @var TransactionType $withdrawal */
322
        $withdrawal = TransactionType::where('type', TransactionType::WITHDRAWAL)->first();
323
        $journals   = TransactionJournal::whereNotNull('bill_id')
324
                                        ->where('transaction_type_id', '!=', $withdrawal->id)->get();
325
        /** @var TransactionJournal $journal */
326
        foreach ($journals as $journal) {
327
            $this->line(sprintf('Transaction journal #%d should not be linked to bill #%d.', $journal->id, $journal->bill_id));
328
            $journal->bill_id = null;
329
            $journal->save();
330
        }
331
    }
332
333
    /**
334
     * Eeport (and fix) piggy banks. Make sure there are only transfers linked to piggy bank events.
335
     */
336
    private function repairPiggyBanks(): void
337
    {
338
        $set = PiggyBankEvent::with(['PiggyBank', 'TransactionJournal', 'TransactionJournal.TransactionType'])->get();
339
        $set->each(
340
            function (PiggyBankEvent $event) {
341
                if (null === $event->transaction_journal_id) {
342
                    return true;
343
                }
344
                /** @var TransactionJournal $journal */
345
                $journal = $event->transactionJournal()->first();
346
                if (null === $journal) {
347
                    return true;
348
                }
349
350
                $type = $journal->transactionType->type;
351
                if (TransactionType::TRANSFER !== $type) {
352
                    $event->transaction_journal_id = null;
353
                    $event->save();
354
                    $this->line(sprintf('Piggy bank #%d was referenced by an invalid event. This has been fixed.', $event->piggy_bank_id));
355
                }
356
357
                return true;
358
            }
359
        );
360
    }
361
362
    /**
363
     * Reports on accounts with no transactions.
364
     */
365
    private function reportAccounts(): void
366
    {
367
        $set = Account::leftJoin('transactions', 'transactions.account_id', '=', 'accounts.id')
368
                      ->leftJoin('users', 'accounts.user_id', '=', 'users.id')
369
                      ->groupBy(['accounts.id', 'accounts.encrypted', 'accounts.name', 'accounts.user_id', 'users.email'])
370
                      ->whereNull('transactions.account_id')
371
                      ->get(
372
                          ['accounts.id', 'accounts.encrypted', 'accounts.name', 'accounts.user_id', 'users.email']
373
                      );
374
375
        /** @var stdClass $entry */
376
        foreach ($set as $entry) {
377
            $name = $entry->name;
378
            $line = 'User #%d (%s) has account #%d ("%s") which has no transactions.';
379
            $line = sprintf($line, $entry->user_id, $entry->email, $entry->id, $name);
380
            $this->line($line);
381
        }
382
    }
383
384
    /**
385
     * Reports on budgets with no budget limits (which makes them pointless).
386
     */
387
    private function reportBudgetLimits(): void
388
    {
389
        $set = Budget::leftJoin('budget_limits', 'budget_limits.budget_id', '=', 'budgets.id')
390
                     ->leftJoin('users', 'budgets.user_id', '=', 'users.id')
391
                     ->groupBy(['budgets.id', 'budgets.name', 'budgets.encrypted', 'budgets.user_id', 'users.email'])
392
                     ->whereNull('budget_limits.id')
393
                     ->get(['budgets.id', 'budgets.name', 'budgets.user_id', 'budgets.encrypted', 'users.email']);
394
395
        /** @var Budget $entry */
396
        foreach ($set as $entry) {
397
            $line = sprintf(
398
                'User #%d (%s) has budget #%d ("%s") which has no budget limits.',
399
                $entry->user_id,
400
                $entry->email,
401
                $entry->id,
402
                $entry->name
403
            );
404
            $this->line($line);
405
        }
406
    }
407
408
    /**
409
     * Reports on deleted accounts that still have not deleted transactions or journals attached to them.
410
     */
411
    private function reportDeletedAccounts(): void
412
    {
413
        $set = Account::leftJoin('transactions', 'transactions.account_id', '=', 'accounts.id')
414
                      ->leftJoin('transaction_journals', 'transaction_journals.id', '=', 'transactions.transaction_journal_id')
415
                      ->whereNotNull('accounts.deleted_at')
416
                      ->whereNotNull('transactions.id')
417
                      ->where(
418
                          function (Builder $q) {
419
                              $q->whereNull('transactions.deleted_at');
420
                              $q->orWhereNull('transaction_journals.deleted_at');
421
                          }
422
                      )
423
                      ->get(
424
                          ['accounts.id as account_id', 'accounts.deleted_at as account_deleted_at', 'transactions.id as transaction_id',
425
                           'transactions.deleted_at as transaction_deleted_at', 'transaction_journals.id as journal_id',
426
                           'transaction_journals.deleted_at as journal_deleted_at',]
427
                      );
428
        /** @var stdClass $entry */
429
        foreach ($set as $entry) {
430
            $date = $entry->transaction_deleted_at ?? $entry->journal_deleted_at;
431
            $this->error(
432
                'Error: Account #' . $entry->account_id . ' should have been deleted, but has not.' .
433
                ' Find it in the table called "accounts" and change the "deleted_at" field to: "' . $date . '"'
434
            );
435
        }
436
    }
437
438
    /**
439
     * Report on budgets with no transactions or journals.
440
     */
441
    private function reportEmptyBudgets(): void
442
    {
443
        $set = Budget::leftJoin('budget_transaction_journal', 'budgets.id', '=', 'budget_transaction_journal.budget_id')
444
                     ->leftJoin('users', 'budgets.user_id', '=', 'users.id')
445
                     ->distinct()
446
                     ->whereNull('budget_transaction_journal.budget_id')
447
                     ->whereNull('budgets.deleted_at')
448
                     ->get(['budgets.id', 'budgets.name', 'budgets.user_id', 'users.email']);
449
450
        /** @var stdClass $entry */
451
        foreach ($set as $entry) {
452
            $objName = $entry->name;
453
454
            // also count the transactions:
455
            $countTransactions = DB::table('budget_transaction')->where('budget_id', $entry->id)->count();
456
457
            if (0 === $countTransactions) {
458
                $line = sprintf(
459
                    'User #%d (%s) has budget #%d ("%s") which has no transactions.',
460
                    $entry->user_id,
461
                    $entry->email,
462
                    $entry->id,
463
                    $objName
464
                );
465
                $this->line($line);
466
            }
467
        }
468
    }
469
470
    /**
471
     * Report on categories with no transactions or journals.
472
     */
473
    private function reportEmptyCategories(): void
474
    {
475
        $set = Category::leftJoin('category_transaction_journal', 'categories.id', '=', 'category_transaction_journal.category_id')
476
                       ->leftJoin('users', 'categories.user_id', '=', 'users.id')
477
                       ->distinct()
478
                       ->whereNull('category_transaction_journal.category_id')
479
                       ->whereNull('categories.deleted_at')
480
                       ->get(['categories.id', 'categories.name', 'categories.user_id', 'users.email']);
481
482
        /** @var stdClass $entry */
483
        foreach ($set as $entry) {
484
            $objName = $entry->name;
485
486
            // also count the transactions:
487
            $countTransactions = DB::table('category_transaction')->where('category_id', $entry->id)->count();
488
489
            if (0 === $countTransactions) {
490
                $line = sprintf(
491
                    'User #%d (%s) has category #%d ("%s") which has no transactions.',
492
                    $entry->user_id,
493
                    $entry->email,
494
                    $entry->id,
495
                    $objName
496
                );
497
                $this->line($line);
498
            }
499
        }
500
    }
501
502
    /**
503
     * Report on journals with bad account types linked to them.
504
     */
505
    private function reportIncorrectJournals(): void
506
    {
507
        $configuration = [
508
            // a withdrawal can not have revenue account:
509
            TransactionType::WITHDRAWAL => [AccountType::REVENUE],
510
            // deposit cannot have an expense account:
511
            TransactionType::DEPOSIT    => [AccountType::EXPENSE],
512
            // transfer cannot have either:
513
            TransactionType::TRANSFER   => [AccountType::EXPENSE, AccountType::REVENUE],
514
        ];
515
        foreach ($configuration as $transactionType => $accountTypes) {
516
            $set = TransactionJournal::leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id')
517
                                     ->leftJoin('transactions', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')
518
                                     ->leftJoin('accounts', 'accounts.id', '=', 'transactions.account_id')
519
                                     ->leftJoin('account_types', 'account_types.id', 'accounts.account_type_id')
520
                                     ->leftJoin('users', 'users.id', '=', 'transaction_journals.user_id')
521
                                     ->where('transaction_types.type', $transactionType)
522
                                     ->whereIn('account_types.type', $accountTypes)
523
                                     ->whereNull('transaction_journals.deleted_at')
524
                                     ->get(
525
                                         ['transaction_journals.id', 'transaction_journals.user_id', 'users.email', 'account_types.type as a_type',
526
                                          'transaction_types.type',]
527
                                     );
528
            foreach ($set as $entry) {
529
                $this->error(
530
                    sprintf(
531
                        'Transaction journal #%d (user #%d, %s) is of type "%s" but ' .
532
                        'is linked to a "%s". The transaction journal should be recreated.',
533
                        $entry->id,
534
                        $entry->user_id,
535
                        $entry->email,
536
                        $entry->type,
537
                        $entry->a_type
538
                    )
539
                );
540
            }
541
        }
542
    }
543
544
    /**
545
     * Any deleted transaction journals that have transactions that are NOT deleted:.
546
     */
547
    private function reportJournals(): void
548
    {
549
        $count = 0;
550
        $set   = TransactionJournal::leftJoin('transactions', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')
551
                                   ->whereNotNull('transaction_journals.deleted_at')// USE THIS
552
                                   ->whereNull('transactions.deleted_at')
553
                                   ->whereNotNull('transactions.id')
554
                                   ->get(
555
                                       [
556
                                           'transaction_journals.id as journal_id',
557
                                           'transaction_journals.description',
558
                                           'transaction_journals.deleted_at as journal_deleted',
559
                                           'transactions.id as transaction_id',
560
                                           'transactions.deleted_at as transaction_deleted_at',]
561
                                   );
562
        /** @var stdClass $entry */
563
        foreach ($set as $entry) {
564
            $this->error(
565
                'Error: Transaction #' . $entry->transaction_id . ' should have been deleted, but has not.' .
566
                ' Find it in the table called "transactions" and change the "deleted_at" field to: "' . $entry->journal_deleted . '"'
567
            );
568
            ++$count;
569
        }
570
        if (0 === $count) {
571
            $this->info('No orphaned transactions!');
572
        }
573
    }
574
575
    /**
576
     * Report on journals without transactions.
577
     */
578
    private function reportNoTransactions(): void
579
    {
580
        $count = 0;
581
        $set   = TransactionJournal::leftJoin('transactions', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')
582
                                   ->groupBy('transaction_journals.id')
583
                                   ->whereNull('transactions.transaction_journal_id')
584
                                   ->get(['transaction_journals.id']);
585
586
        foreach ($set as $entry) {
587
            $this->error(
588
                'Error: Journal #' . $entry->id . ' has zero transactions. Open table "transaction_journals" and delete the entry with id #' . $entry->id
589
            );
590
            ++$count;
591
        }
592
        if (0 === $count) {
593
            $this->info('No orphaned journals!');
594
        }
595
    }
596
597
    /**
598
     * Report on things with no linked journals.
599
     *
600
     * @param string $name
601
     */
602
    private function reportObject(string $name): void
603
    {
604
        $plural = str_plural($name);
605
        $class  = sprintf('FireflyIII\Models\%s', ucfirst($name));
606
        $field  = 'tag' === $name ? 'tag' : 'name';
607
        /** @noinspection PhpUndefinedMethodInspection */
608
        $set = $class::leftJoin($name . '_transaction_journal', $plural . '.id', '=', $name . '_transaction_journal.' . $name . '_id')
609
                     ->leftJoin('users', $plural . '.user_id', '=', 'users.id')
610
                     ->distinct()
611
                     ->whereNull($name . '_transaction_journal.' . $name . '_id')
612
                     ->whereNull($plural . '.deleted_at')
613
                     ->get([$plural . '.id', $plural . '.' . $field . ' as name', $plural . '.user_id', 'users.email']);
614
615
        /** @var stdClass $entry */
616
        foreach ($set as $entry) {
617
            $objName = $entry->name;
618
619
            $line = sprintf(
620
                'User #%d (%s) has %s #%d ("%s") which has no transactions.',
621
                $entry->user_id,
622
                $entry->email,
623
                $name,
624
                $entry->id,
625
                $objName
626
            );
627
            $this->line($line);
628
        }
629
    }
630
631
    /**
632
     * Reports for each user when the sum of their transactions is not zero.
633
     */
634
    private function reportSum(): void
635
    {
636
        /** @var UserRepositoryInterface $userRepository */
637
        $userRepository = app(UserRepositoryInterface::class);
638
639
        /** @var User $user */
640
        foreach ($userRepository->all() as $user) {
641
            $sum = (string)$user->transactions()->sum('amount');
642
            if (0 !== bccomp($sum, '0')) {
643
                $this->error('Error: Transactions for user #' . $user->id . ' (' . $user->email . ') are off by ' . $sum . '!');
644
            }
645
            if (0 === bccomp($sum, '0')) {
646
                $this->info(sprintf('Amount integrity OK for user #%d', $user->id));
647
            }
648
        }
649
    }
650
651
    /**
652
     * Reports on deleted transactions that are connected to a not deleted journal.
653
     */
654
    private function reportTransactions(): void
655
    {
656
        $set = Transaction::leftJoin('transaction_journals', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')
657
                          ->whereNotNull('transactions.deleted_at')
658
                          ->whereNull('transaction_journals.deleted_at')
659
                          ->get(
660
                              ['transactions.id as transaction_id', 'transactions.deleted_at as transaction_deleted', 'transaction_journals.id as journal_id',
661
                               'transaction_journals.deleted_at',]
662
                          );
663
        /** @var stdClass $entry */
664
        foreach ($set as $entry) {
665
            $this->error(
666
                'Error: Transaction journal #' . $entry->journal_id . ' should have been deleted, but has not.' .
667
                ' Find it in the table called "transaction_journals" and change the "deleted_at" field to: "' . $entry->transaction_deleted . '"'
668
            );
669
        }
670
    }
671
672
    /**
673
     * Report on transfers that have budgets.
674
     */
675
    private function reportTransfersBudgets(): void
676
    {
677
        $set = TransactionJournal::distinct()
678
                                 ->leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id')
679
                                 ->leftJoin('budget_transaction_journal', 'transaction_journals.id', '=', 'budget_transaction_journal.transaction_journal_id')
680
                                 ->whereIn('transaction_types.type', [TransactionType::TRANSFER, TransactionType::DEPOSIT])
681
                                 ->whereNotNull('budget_transaction_journal.budget_id')->get(['transaction_journals.*']);
682
683
        /** @var TransactionJournal $entry */
684
        foreach ($set as $entry) {
685
            $this->error(
686
                sprintf(
687
                    'Error: Transaction journal #%d is a %s, but has a budget. Edit it without changing anything, so the budget will be removed.',
688
                    $entry->id,
689
                    $entry->transactionType->type
690
                )
691
            );
692
        }
693
    }
694
695
    /**
696
     * Collect all journals with empty amount.
697
     */
698
    private function reportZeroAmount(): void
699
    {
700
        $set = Transaction::where('amount', 0)->get(['transaction_journal_id'])->pluck('transaction_journal_id')->toArray();
701
        $set = array_unique($set);
702
        /** @var Collection $journals */
703
        $journals = TransactionJournal::whereIn('id', $set)->get();
704
        /** @var TransactionJournal $journal */
705
        foreach ($journals as $journal) {
706
            $message = sprintf(
707
                'Transaction "%s" (#%d), owned by user %s, has amount zero (0.00). It should be deleted.', $journal->description,
708
                $journal->id, $journal->user->email
709
            );
710
            $this->error($message);
711
        }
712
    }
713
714
}
715