Passed
Push — master ( 37b02e...ebbbe1 )
by James
08:59
created

app/Console/Commands/UpgradeDatabase.php (1 issue)

Labels
Severity
1
<?php
2
declare(strict_types=1);
3
/**
4
 * UpgradeDatabase.php
5
 * Copyright (c) 2017 [email protected]
6
 *
7
 * This file is part of Firefly III.
8
 *
9
 * Firefly III is free software: you can redistribute it and/or modify
10
 * it under the terms of the GNU General Public License as published by
11
 * the Free Software Foundation, either version 3 of the License, or
12
 * (at your option) any later version.
13
 *
14
 * Firefly III is distributed in the hope that it will be useful,
15
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17
 * GNU General Public License for more details.
18
 *
19
 * You should have received a copy of the GNU General Public License
20
 * along with Firefly III. If not, see <http://www.gnu.org/licenses/>.
21
 */
22
23
namespace FireflyIII\Console\Commands;
24
25
use DB;
26
use FireflyIII\Models\Account;
27
use FireflyIII\Models\AccountMeta;
28
use FireflyIII\Models\AccountType;
29
use FireflyIII\Models\Attachment;
30
use FireflyIII\Models\Note;
31
use FireflyIII\Models\Transaction;
32
use FireflyIII\Models\TransactionCurrency;
33
use FireflyIII\Models\TransactionJournal;
34
use FireflyIII\Models\TransactionJournalMeta;
35
use FireflyIII\Models\TransactionType;
36
use FireflyIII\Repositories\Currency\CurrencyRepositoryInterface;
37
use Illuminate\Console\Command;
38
use Illuminate\Database\QueryException;
39
use Illuminate\Support\Collection;
40
use Log;
41
use Preferences;
42
use Schema;
43
44
/**
45
 * Class UpgradeDatabase.
46
 *
47
 * Upgrade user database.
48
 *
49
 *
50
 * @SuppressWarnings(PHPMD.CouplingBetweenObjects) // it just touches a lot of things.
51
 */
52
class UpgradeDatabase extends Command
53
{
54
    /**
55
     * The console command description.
56
     *
57
     * @var string
58
     */
59
    protected $description = 'Will run various commands to update database records.';
60
    /**
61
     * The name and signature of the console command.
62
     *
63
     * @var string
64
     */
65
    protected $signature = 'firefly:upgrade-database';
66
67
    /**
68
     * Create a new command instance.
69
     */
70
    public function __construct()
71
    {
72
        parent::__construct();
73
    }
74
75
    /**
76
     * Execute the console command.
77
     *
78
     * @throws \Exception
79
     */
80
    public function handle()
81
    {
82
        $this->setTransactionIdentifier();
83
        $this->updateAccountCurrencies();
84
        $this->createNewTypes();
85
        $this->line('Updating currency information..');
86
        $this->updateTransferCurrencies();
87
        $this->updateOtherCurrencies();
88
        $this->line('Done updating currency information..');
89
        $this->migrateNotes();
90
        $this->migrateAttachmentData();
91
        $this->info('Firefly III database is up to date.');
92
93
        return;
94
    }
95
96
    /**
97
     * This method gives all transactions which are part of a split journal (so more than 2) a sort of "order" so they are easier
98
     * to easier to match to their counterpart. When a journal is split, it has two or three transactions: -3, -4 and -5 for example.
99
     *
100
     * In the database this is reflected as 6 transactions: -3/+3, -4/+4, -5/+5.
101
     *
102
     * When either of these are the same amount, FF3 can't keep them apart: +3/-3, +3/-3, +3/-3. This happens more often than you would
103
     * think. So each set gets a number (1,2,3) to keep them apart.
104
     */
105
    public function setTransactionIdentifier(): void
106
    {
107
        // if table does not exist, return false
108
        if (!Schema::hasTable('transaction_journals')) {
109
            return;
110
        }
111
        $subQuery   = TransactionJournal::leftJoin('transactions', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')
112
                                        ->whereNull('transaction_journals.deleted_at')
113
                                        ->whereNull('transactions.deleted_at')
114
                                        ->groupBy(['transaction_journals.id'])
115
                                        ->select(['transaction_journals.id', DB::raw('COUNT(transactions.id) AS t_count')]);
116
        $result     = DB::table(DB::raw('(' . $subQuery->toSql() . ') AS derived'))
117
                        ->mergeBindings($subQuery->getQuery())
118
                        ->where('t_count', '>', 2)
119
                        ->select(['id', 't_count']);
120
        $journalIds = array_unique($result->pluck('id')->toArray());
121
122
        foreach ($journalIds as $journalId) {
123
            $this->updateJournalidentifiers(intval($journalId));
124
        }
125
126
        return;
127
    }
128
129
    /**
130
     * Each (asset) account must have a reference to a preferred currency. If the account does not have one, it's forced upon the account.
131
     *
132
     * @SuppressWarnings(PHPMD.CyclomaticComplexity) // it's seven but it can't really be helped.
133
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
134
     */
135
    public function updateAccountCurrencies(): void
136
    {
137
        $accounts = Account::leftJoin('account_types', 'account_types.id', '=', 'accounts.account_type_id')
138
                           ->whereIn('account_types.type', [AccountType::DEFAULT, AccountType::ASSET])->get(['accounts.*']);
139
140
        $accounts->each(
141
            function (Account $account) {
142
                // get users preference, fall back to system pref.
143
                $defaultCurrencyCode = Preferences::getForUser($account->user, 'currencyPreference', config('firefly.default_currency', 'EUR'))->data;
144
                $defaultCurrency     = TransactionCurrency::where('code', $defaultCurrencyCode)->first();
145
                $accountCurrency     = intval($account->getMeta('currency_id'));
146
                $openingBalance      = $account->getOpeningBalance();
147
                $obCurrency          = intval($openingBalance->transaction_currency_id);
148
149
                // both 0? set to default currency:
150
                if (0 === $accountCurrency && 0 === $obCurrency) {
151
                    AccountMeta::where('account_id', $account->id)->where('name', 'currency_id')->forceDelete();
152
                    AccountMeta::create(['account_id' => $account->id, 'name' => 'currency_id', 'data' => $defaultCurrency->id]);
153
                    $this->line(sprintf('Account #%d ("%s") now has a currency setting (%s).', $account->id, $account->name, $defaultCurrencyCode));
154
155
                    return true;
156
                }
157
158
                // account is set to 0, opening balance is not?
159
                if (0 === $accountCurrency && $obCurrency > 0) {
160
                    AccountMeta::create(['account_id' => $account->id, 'name' => 'currency_id', 'data' => $obCurrency]);
161
                    $this->line(sprintf('Account #%d ("%s") now has a currency setting (%s).', $account->id, $account->name, $defaultCurrencyCode));
162
163
                    return true;
164
                }
165
166
                // do not match and opening balance id is not null.
167
                if ($accountCurrency !== $obCurrency && $openingBalance->id > 0) {
168
                    // update opening balance:
169
                    $openingBalance->transaction_currency_id = $accountCurrency;
170
                    $openingBalance->save();
171
                    $this->line(sprintf('Account #%d ("%s") now has a correct currency for opening balance.', $account->id, $account->name));
172
173
                    return true;
174
                }
175
176
                return true;
177
            }
178
        );
179
180
        return;
181
    }
182
183
    /**
184
     * This routine verifies that withdrawals, deposits and opening balances have the correct currency settings for
185
     * the accounts they are linked to.
186
     *
187
     * Both source and destination must match the respective currency preference of the related asset account.
188
     * So FF3 must verify all transactions.
189
     *
190
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
191
     */
192
    public function updateOtherCurrencies(): void
193
    {
194
        /** @var CurrencyRepositoryInterface $repository */
195
        $repository = app(CurrencyRepositoryInterface::class);
196
        $set        = TransactionJournal
197
            ::leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id')
198
            ->whereIn('transaction_types.type', [TransactionType::WITHDRAWAL, TransactionType::DEPOSIT, TransactionType::OPENING_BALANCE])
199
            ->get(['transaction_journals.*']);
200
201
        $set->each(
202
            function (TransactionJournal $journal) use ($repository) {
203
                // get the transaction with the asset account in it:
204
                /** @var Transaction $transaction */
205
                $transaction = $journal->transactions()
206
                                       ->leftJoin('accounts', 'accounts.id', '=', 'transactions.account_id')
207
                                       ->leftJoin('account_types', 'account_types.id', '=', 'accounts.account_type_id')
208
                                       ->whereIn('account_types.type', [AccountType::DEFAULT, AccountType::ASSET])->first(['transactions.*']);
209
                if (null === $transaction) {
210
                    return;
211
                }
212
                /** @var Account $account */
213
                $account      = $transaction->account;
214
                $currency     = $repository->find(intval($account->getMeta('currency_id')));
215
                $transactions = $journal->transactions()->get();
216
                $transactions->each(
217
                    function (Transaction $transaction) use ($currency) {
218
                        if (null === $transaction->transaction_currency_id) {
219
                            $transaction->transaction_currency_id = $currency->id;
220
                            $transaction->save();
221
                        }
222
223
                        // when mismatch in transaction:
224
                        if (!(intval($transaction->transaction_currency_id) === intval($currency->id))) {
225
                            $transaction->foreign_currency_id     = intval($transaction->transaction_currency_id);
226
                            $transaction->foreign_amount          = $transaction->amount;
227
                            $transaction->transaction_currency_id = $currency->id;
228
                            $transaction->save();
229
                        }
230
                    }
231
                );
232
                // also update the journal, of course:
233
                $journal->transaction_currency_id = $currency->id;
234
                $journal->save();
235
            }
236
        );
237
238
        return;
239
    }
240
241
    /**
242
     * This routine verifies that transfers have the correct currency settings for the accounts they are linked to.
243
     * For transfers, this is can be a destructive routine since we FORCE them into a currency setting whether they
244
     * like it or not. Previous routines MUST have set the currency setting for both accounts for this to work.
245
     *
246
     * A transfer always has the
247
     *
248
     * Both source and destination must match the respective currency preference. So FF3 must verify ALL
249
     * transactions.
250
     */
251
    public function updateTransferCurrencies()
252
    {
253
        $set = TransactionJournal
254
            ::leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id')
255
            ->where('transaction_types.type', TransactionType::TRANSFER)
256
            ->get(['transaction_journals.*']);
257
258
        $set->each(
259
            function (TransactionJournal $transfer) {
260
                // select all "source" transactions:
261
                /** @var Collection $transactions */
262
                $transactions = $transfer->transactions()->where('amount', '<', 0)->get();
263
                $transactions->each(
264
                    function (Transaction $transaction) {
265
                        $this->updateTransactionCurrency($transaction);
266
                        $this->updateJournalCurrency($transaction);
267
                    }
268
                );
269
            }
270
        );
271
    }
272
273
    private function createNewTypes(): void
274
    {
275
        // create transaction type "Reconciliation".
276
        $type = TransactionType::where('type', TransactionType::RECONCILIATION)->first();
277
        if (is_null($type)) {
278
            TransactionType::create(['type' => TransactionType::RECONCILIATION]);
279
        }
280
        $account = AccountType::where('type', AccountType::RECONCILIATION)->first();
281
        if (is_null($account)) {
282
            AccountType::create(['type' => AccountType::RECONCILIATION]);
283
        }
284
    }
285
286
    /**
287
     * Move the description of each attachment (when not NULL) to the notes or to a new note object
288
     * for all attachments.
289
     */
290
    private function migrateAttachmentData(): void
291
    {
292
        $attachments = Attachment::get();
293
294
        /** @var Attachment $att */
295
        foreach ($attachments as $att) {
296
297
            // move description:
298
            $description = strval($att->description);
299
            if (strlen($description) > 0) {
300
                // find or create note:
301
                $note = $att->notes()->first();
302
                if (is_null($note)) {
303
                    $note = new Note;
304
                    $note->noteable()->associate($att);
305
                }
306
                $note->text = $description;
307
                $note->save();
308
309
                // clear description:
310
                $att->description = '';
311
                $att->save();
312
313
                Log::debug(sprintf('Migrated attachment #%s description to note #%d', $att->id, $note->id));
314
            }
315
        }
316
    }
317
318
    /**
319
     * Move all the journal_meta notes to their note object counter parts.
320
     *
321
     * @throws \Exception
322
     */
323
    private function migrateNotes(): void
324
    {
325
        $set = TransactionJournalMeta::whereName('notes')->get();
326
        /** @var TransactionJournalMeta $meta */
327
        foreach ($set as $meta) {
328
            $journal = $meta->transactionJournal;
329
            $note    = $journal->notes()->first();
330
            if (null === $note) {
331
                $note = new Note();
332
                $note->noteable()->associate($journal);
333
            }
334
335
            $note->text = $meta->data;
336
            $note->save();
337
            Log::debug(sprintf('Migrated meta note #%d to Note #%d', $meta->id, $note->id));
338
            $meta->delete();
339
        }
340
    }
341
342
    /**
343
     * This method makes sure that the transaction journal uses the currency given in the transaction.
344
     *
345
     * @param Transaction $transaction
346
     */
347
    private function updateJournalCurrency(Transaction $transaction): void
348
    {
349
        /** @var CurrencyRepositoryInterface $repository */
350
        $repository = app(CurrencyRepositoryInterface::class);
351
        $currency   = $repository->find(intval($transaction->account->getMeta('currency_id')));
352
        $journal    = $transaction->transactionJournal;
353
354
        if (!(intval($currency->id) === intval($journal->transaction_currency_id))) {
0 ignored issues
show
The property transaction_currency_id does not exist on FireflyIII\Models\TransactionJournal. Did you mean transactionCurrency?
Loading history...
355
            $this->line(
356
                sprintf(
357
                    'Transfer #%d ("%s") has been updated to use %s instead of %s.',
358
                    $journal->id,
359
                    $journal->description,
360
                    $currency->code,
361
                    $journal->transactionCurrency->code
362
                )
363
            );
364
            $journal->transaction_currency_id = $currency->id;
365
            $journal->save();
366
        }
367
368
        return;
369
    }
370
371
    /**
372
     * grab all positive transactiosn from this journal that are not deleted. for each one, grab the negative opposing one
373
     * which has 0 as an identifier and give it the same identifier.
374
     *
375
     * @param int $journalId
376
     */
377
    private function updateJournalidentifiers(int $journalId): void
378
    {
379
        $identifier   = 0;
380
        $processed    = [];
381
        $transactions = Transaction::where('transaction_journal_id', $journalId)->where('amount', '>', 0)->get();
382
        /** @var Transaction $transaction */
383
        foreach ($transactions as $transaction) {
384
            // find opposing:
385
            $amount = bcmul(strval($transaction->amount), '-1');
386
387
            try {
388
                /** @var Transaction $opposing */
389
                $opposing = Transaction::where('transaction_journal_id', $journalId)
390
                                       ->where('amount', $amount)->where('identifier', '=', 0)
391
                                       ->whereNotIn('id', $processed)
392
                                       ->first();
393
            } catch (QueryException $e) {
394
                Log::error($e->getMessage());
395
                $this->error('Firefly III could not find the "identifier" field in the "transactions" table.');
396
                $this->error(sprintf('This field is required for Firefly III version %s to run.', config('firefly.version')));
397
                $this->error('Please run "php artisan migrate" to add this field to the table.');
398
                $this->info('Then, run "php artisan firefly:upgrade-database" to try again.');
399
400
                return;
401
            }
402
            if (null !== $opposing) {
403
                // give both a new identifier:
404
                $transaction->identifier = $identifier;
405
                $opposing->identifier    = $identifier;
406
                $transaction->save();
407
                $opposing->save();
408
                $processed[] = $transaction->id;
409
                $processed[] = $opposing->id;
410
            }
411
            ++$identifier;
412
        }
413
414
        return;
415
    }
416
417
    /**
418
     * This method makes sure that the tranaction uses the same currency as the source account does.
419
     * If not, the currency is updated to include a reference to its original currency as the "foreign" currency.
420
     *
421
     * The transaction that is sent to this function MUST be the source transaction (amount negative).
422
     *
423
     * Method is long and complex bit I'm taking it for granted.
424
     *
425
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
426
     * @SuppressWarnings(PHPMD.NPathComplexity)
427
     * @SuppressWarnings(PHPMD.CyclomaticComplexity)
428
     *
429
     * @param Transaction $transaction
430
     */
431
    private function updateTransactionCurrency(Transaction $transaction): void
432
    {
433
        /** @var CurrencyRepositoryInterface $repository */
434
        $repository = app(CurrencyRepositoryInterface::class);
435
        $currency   = $repository->find(intval($transaction->account->getMeta('currency_id')));
436
437
        // has no currency ID? Must have, so fill in using account preference:
438
        if (null === $transaction->transaction_currency_id) {
439
            $transaction->transaction_currency_id = intval($currency->id);
440
            Log::debug(sprintf('Transaction #%d has no currency setting, now set to %s', $transaction->id, $currency->code));
441
            $transaction->save();
442
        }
443
444
        // does not match the source account (see above)? Can be fixed
445
        // when mismatch in transaction and NO foreign amount is set:
446
        if (!(intval($transaction->transaction_currency_id) === intval($currency->id)) && null === $transaction->foreign_amount) {
447
            Log::debug(
448
                sprintf(
449
                    'Transaction #%d has a currency setting #%d that should be #%d. Amount remains %s, currency is changed.',
450
                    $transaction->id,
451
                    $transaction->transaction_currency_id,
452
                    $currency->id,
453
                    $transaction->amount
454
                )
455
            );
456
            $transaction->transaction_currency_id = intval($currency->id);
457
            $transaction->save();
458
        }
459
460
        // grab opposing transaction:
461
        /** @var TransactionJournal $journal */
462
        $journal = $transaction->transactionJournal;
463
        /** @var Transaction $opposing */
464
        $opposing         = $journal->transactions()->where('amount', '>', 0)->where('identifier', $transaction->identifier)->first();
465
        $opposingCurrency = $repository->find(intval($opposing->account->getMeta('currency_id')));
466
467
        if (null === $opposingCurrency->id) {
468
            Log::error(sprintf('Account #%d ("%s") must have currency preference but has none.', $opposing->account->id, $opposing->account->name));
469
470
            return;
471
        }
472
473
        // if the destination account currency is the same, both foreign_amount and foreign_currency_id must be NULL for both transactions:
474
        if (intval($opposingCurrency->id) === intval($currency->id)) {
475
            // update both transactions to match:
476
            $transaction->foreign_amount       = null;
477
            $transaction->foreign_currency_id  = null;
478
            $opposing->foreign_amount          = null;
479
            $opposing->foreign_currency_id     = null;
480
            $opposing->transaction_currency_id = $currency->id;
481
            $transaction->save();
482
            $opposing->save();
483
            Log::debug(sprintf('Cleaned up transaction #%d and #%d', $transaction->id, $opposing->id));
484
485
            return;
486
        }
487
        // if destination account currency is different, both transactions must have this currency as foreign currency id.
488
        if (!(intval($opposingCurrency->id) === intval($currency->id))) {
489
            $transaction->foreign_currency_id = $opposingCurrency->id;
490
            $opposing->foreign_currency_id    = $opposingCurrency->id;
491
            $transaction->save();
492
            $opposing->save();
493
            Log::debug(sprintf('Verified foreign currency ID of transaction #%d and #%d', $transaction->id, $opposing->id));
494
        }
495
496
        // if foreign amount of one is null and the other is not, use this to restore:
497
        if (null === $transaction->foreign_amount && null !== $opposing->foreign_amount) {
498
            $transaction->foreign_amount = bcmul(strval($opposing->foreign_amount), '-1');
499
            $transaction->save();
500
            Log::debug(sprintf('Restored foreign amount of transaction (1) #%d to %s', $transaction->id, $transaction->foreign_amount));
501
        }
502
503
        // if foreign amount of one is null and the other is not, use this to restore (other way around)
504
        if (null === $opposing->foreign_amount && null !== $transaction->foreign_amount) {
505
            $opposing->foreign_amount = bcmul(strval($transaction->foreign_amount), '-1');
506
            $opposing->save();
507
            Log::debug(sprintf('Restored foreign amount of transaction (2) #%d to %s', $opposing->id, $opposing->foreign_amount));
508
        }
509
510
        // when both are zero, try to grab it from journal:
511
        if (null === $opposing->foreign_amount && null === $transaction->foreign_amount) {
512
            $foreignAmount = $journal->getMeta('foreign_amount');
513
            if (null === $foreignAmount) {
514
                Log::debug(sprintf('Journal #%d has missing foreign currency data, forced to do 1:1 conversion :(.', $transaction->transaction_journal_id));
515
                $transaction->foreign_amount = bcmul(strval($transaction->amount), '-1');
516
                $opposing->foreign_amount    = bcmul(strval($opposing->amount), '-1');
517
                $transaction->save();
518
                $opposing->save();
519
520
                return;
521
            }
522
            $foreignPositive = app('steam')->positive(strval($foreignAmount));
523
            Log::debug(
524
                sprintf(
525
                    'Journal #%d has missing foreign currency info, try to restore from meta-data ("%s").',
526
                    $transaction->transaction_journal_id,
527
                    $foreignAmount
528
                )
529
            );
530
            $transaction->foreign_amount = bcmul($foreignPositive, '-1');
531
            $opposing->foreign_amount    = $foreignPositive;
532
            $transaction->save();
533
            $opposing->save();
534
        }
535
536
        return;
537
    }
538
539
}
540