Skip to content

Firefly III: a personal finances manager

14.3 KB
Raw Blame
<?php

/**
 * CategoryRepository.php
 * Copyright (c) 2019 [email protected]
 *
 * This file is part of Firefly III (https://github.com/firefly-iii).
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 */
declare(strict_types=1);

namespace FireflyIII\Repositories\Category;

use Carbon\Carbon;
use Exception;
use FireflyIII\Exceptions\FireflyException;
use FireflyIII\Factory\CategoryFactory;
use FireflyIII\Models\Attachment;
use FireflyIII\Models\Category;
use FireflyIII\Models\Note;
use FireflyIII\Models\RecurrenceTransactionMeta;
use FireflyIII\Models\RuleAction;
use FireflyIII\Services\Internal\Destroy\CategoryDestroyService;
use FireflyIII\Services\Internal\Update\CategoryUpdateService;
use FireflyIII\Support\Repositories\UserGroup\UserGroupInterface;
use FireflyIII\Support\Repositories\UserGroup\UserGroupTrait;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;

/**
 * Class CategoryRepository.
 */
class CategoryRepository implements CategoryRepositoryInterface, UserGroupInterface
{
    use UserGroupTrait;

    public function categoryEndsWith(string $query, int $limit): Collection
    {
        $search = $this->user->categories();
        if ('' !== $query) {
            $search->whereLike('name', sprintf('%%%s', $query));
        }

        return $search->take($limit)->get();
    }

    public function categoryStartsWith(string $query, int $limit): Collection
    {
        Log::debug(sprintf('Find a category that starts with "%s"', $query));
        $search = $this->user->categories();
        if ('' !== $query) {
            $search->whereLike('name', sprintf('%s%%', $query));
        }

        return $search->take($limit)->get();
    }

    public function destroy(Category $category): bool
    {
        /** @var CategoryDestroyService $service */
        $service = app(CategoryDestroyService::class);
        $service->destroy($category);

        return true;
    }

    /**
     * Delete all categories.
     */
    public function destroyAll(): void
    {
        $categories = $this->getCategories();

        /** @var Category $category */
        foreach ($categories as $category) {
            DB::table('category_transaction')->where('category_id', $category->id)->delete();
            DB::table('category_transaction_journal')->where('category_id', $category->id)->delete();
            RecurrenceTransactionMeta::query()->where('name', 'category_id')->where('value', $category->id)->delete();
            RuleAction::query()->where('action_type', 'set_category')->where('action_value', $category->name)->delete();
            $category->delete();
        }
        Log::channel('audit')->info('Delete all categories through destroyAll');
    }

    /**
     * Find a category or return NULL
     */
    public function find(int $categoryId): ?Category
    {
        /** @var null|Category */
        return $this->user->categories()->find($categoryId);
    }

    /**
     * Find a category.
     */
    public function findByName(string $name): ?Category
    {
        /** @var null|Category */
        return $this->user->categories()->where('name', $name)->first(['categories.*']);
    }

    /**
     * @throws FireflyException
     */
    public function findCategory(?int $categoryId, ?string $categoryName): ?Category
    {
        Log::debug('Now in findCategory()');
        Log::debug(sprintf('Searching for category with ID #%d...', $categoryId));
        $result = $this->find((int) $categoryId);
        if (!$result instanceof Category) {
            Log::debug(sprintf('Searching for category with name %s...', $categoryName));
            $result = $this->findByName((string) $categoryName);
            if (!$result instanceof Category && '' !== (string) $categoryName) {
                // create it!
                $result = $this->store(['name' => $categoryName]);
            }
        }
        if ($result instanceof Category) {
            Log::debug(sprintf('Found category #%d: %s', $result->id, $result->name));
        }
        Log::debug(sprintf('Found category result is null? %s', var_export(!$result instanceof Category, true)));

        return $result;
    }

    public function firstUseDate(Category $category): ?Carbon
    {
        $firstJournalDate     = $this->getFirstJournalDate($category);
        $firstTransactionDate = $this->getFirstTransactionDate($category);

        if (!$firstTransactionDate instanceof Carbon && !$firstJournalDate instanceof Carbon) {
            return null;
        }
        if (!$firstTransactionDate instanceof Carbon) {
            return $firstJournalDate;
        }
        if (!$firstJournalDate instanceof Carbon) {
            return $firstTransactionDate;
        }

        if ($firstTransactionDate < $firstJournalDate) {
            return $firstTransactionDate;
        }

        return $firstJournalDate;
    }

    public function getAttachments(Category $category): Collection
    {
        $set  = $category->attachments()->get();

        $disk = Storage::disk('upload');

        return $set->each(static function (Attachment $attachment) use ($disk): Attachment {
            $notes                   = $attachment->notes()->first();
            $attachment->file_exists = $disk->exists($attachment->fileName());
            $attachment->notes_text  = null !== $notes ? $notes->text : '';

            return $attachment;
        });
    }

    /**
     * Get all categories with ID's.
     */
    public function getByIds(array $categoryIds): Collection
    {
        return $this->user->categories()->whereIn('id', $categoryIds)->get();
    }

    /**
     * Returns a list of all the categories belonging to a user.
     */
    public function getCategories(array $sort = [], array $filter = []): Collection
    {
        $query   = $this->user->categories()->with(['attachments']);

        // add sort parameters
        $allowed = config('firefly.allowed_db_sort_parameters.Category', []);
        $sorted  = 0;
        if (0 !== count($sort)) {
            foreach ($sort as $param) {
                // basic sort:
                if (in_array($param[0], $allowed, true)) {
                    $query->orderBy($param[0], $param[1]);
                    ++$sorted;
                }
            }
        }

        // add filter parameters.
        $allowed = config('firefly.allowed_filter_parameters.Category', []);
        if (0 !== count($filter)) {
            foreach ($filter as $field => $search) {
                if (in_array($field, $allowed, true)) {
                    if ('' !== (string) $search) {
                        $query->whereLike($field, sprintf('%%%s%%', $search));
                    }
                }
            }
        }

        if (0 === $sorted) {
            $query->orderBy('categories.name', 'ASC');
        }

        return $query->get(['categories.*']);
    }

    public function getNoteText(Category $category): ?string
    {
        $dbNote = $category->notes()->first();

        return $dbNote?->text;
    }

    /**
     * @throws Exception
     */
    public function lastUseDate(Category $category, Collection $accounts): ?Carbon
    {
        $lastJournalDate     = $this->getLastJournalDate($category, $accounts);
        $lastTransactionDate = $this->getLastTransactionDate($category, $accounts);

        if (!$lastTransactionDate instanceof Carbon && !$lastJournalDate instanceof Carbon) {
            return null;
        }
        if (!$lastTransactionDate instanceof Carbon) {
            return $lastJournalDate;
        }
        if (!$lastJournalDate instanceof Carbon) {
            return $lastTransactionDate;
        }

        if ($lastTransactionDate > $lastJournalDate) {
            return $lastTransactionDate;
        }

        return $lastJournalDate;
    }

    public function periodCollection(Category $category, Carbon $start, Carbon $end): array
    {
        Log::debug(sprintf('periodCollection(#%d, %s, %s)', $category->id, $start->format('Y-m-d'), $end->format('Y-m-d')));

        return $category
            ->transactionJournals()
            ->leftJoin('transactions', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')
            ->leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id')
            ->leftJoin('transaction_currencies', 'transaction_currencies.id', '=', 'transactions.transaction_currency_id')
            ->leftJoin('transaction_currencies as foreign_currencies', 'foreign_currencies.id', '=', 'transactions.foreign_currency_id')
            ->where('transaction_journals.date', '>=', $start)
            ->where('transaction_journals.date', '<=', $end)
            ->where('transactions.amount', '>', 0)
            ->get([
                // currencies
                'transaction_currencies.id as currency_id',
                'transaction_currencies.code as currency_code',
                'transaction_currencies.name as currency_name',
                'transaction_currencies.symbol as currency_symbol',
                'transaction_currencies.decimal_places as currency_decimal_places',

                // foreign
                'foreign_currencies.id as foreign_currency_id',
                'foreign_currencies.code as foreign_currency_code',
                'foreign_currencies.name as foreign_currency_name',
                'foreign_currencies.symbol as foreign_currency_symbol',
                'foreign_currencies.decimal_places as foreign_currency_decimal_places',

                // fields
                'transaction_journals.date',
                'transaction_types.type',
                'transaction_journals.transaction_currency_id',
                'transactions.amount',
                'transactions.native_amount as pc_amount',
                'transactions.foreign_amount',
            ])
            ->toArray()
        ;
    }

    public function removeNotes(Category $category): void
    {
        $category->notes()->delete();
    }

    public function searchCategory(string $query, int $limit): Collection
    {
        $search = $this->user->categories()->orderBy('name', 'ASC');
        if ('' !== $query) {
            $search->whereLike('name', sprintf('%%%s%%', $query));
        }

        return $search->take($limit)->get();
    }

    /**
     * @throws FireflyException
     */
    public function store(array $data): Category
    {
        /** @var CategoryFactory $factory */
        $factory  = app(CategoryFactory::class);
        $factory->setUser($this->user);
        $color    = array_key_exists('color', $data) ? $data['color'] : null;
        $category = $factory->findOrCreate(null, $data['name'], $color);

        if (null === $category) {
            throw new FireflyException(sprintf('400003: Could not store new category with name "%s"', $data['name']));
        }

        if (array_key_exists('notes', $data) && '' === $data['notes']) {
            $this->removeNotes($category);
        }
        if (array_key_exists('notes', $data) && '' !== $data['notes']) {
            $this->updateNotes($category, $data['notes']);
        }

        return $category;
    }

    /**
     * @throws Exception
     */
    public function update(Category $category, array $data): Category
    {
        /** @var CategoryUpdateService $service */
        $service = app(CategoryUpdateService::class);
        $service->setUser($this->user);

        return $service->update($category, $data);
    }

    public function updateNotes(Category $category, string $notes): void
    {
        $dbNote       = $category->notes()->first();
        if (null === $dbNote) {
            $dbNote = new Note();
            $dbNote->noteable()->associate($category);
        }
        $dbNote->text = trim($notes);
        $dbNote->save();
    }

    private function getFirstJournalDate(Category $category): ?Carbon
    {
        $query  = $category->transactionJournals()->orderBy('date', 'ASC');
        $result = $query->first(['transaction_journals.*']);

        return $result?->date;
    }

    private function getFirstTransactionDate(Category $category): ?Carbon
    {
        // check transactions:
        $query           = $category
            ->transactions()
            ->leftJoin('transaction_journals', 'transaction_journals.id', '=', 'transactions.transaction_journal_id')
            ->orderBy('transaction_journals.date', 'ASC')
        ;

        $lastTransaction = $query->first(['transaction_journals.*']);
        if (null !== $lastTransaction) {
            return new Carbon($lastTransaction->date);
        }

        return null;
    }

    private function getLastJournalDate(Category $category, Collection $accounts): ?Carbon
    {
        $query  = $category->transactionJournals()->orderBy('date', 'DESC');

        if ($accounts->count() > 0) {
            $query->leftJoin('transactions as t', 't.transaction_journal_id', '=', 'transaction_journals.id');
            $query->whereIn('t.account_id', $accounts->pluck('id')->toArray());
        }

        $result = $query->first(['transaction_journals.*']);

        return $result?->date;
    }

    /**
     * @throws Exception
     */
    private function getLastTransactionDate(Category $category, Collection $accounts): ?Carbon
    {
        // check transactions:
        $query           = $category
            ->transactions()
            ->leftJoin('transaction_journals', 'transaction_journals.id', '=', 'transactions.transaction_journal_id')
            ->orderBy('transaction_journals.date', 'DESC')
        ;
        if ($accounts->count() > 0) {
            // filter journals:
            $query->whereIn('transactions.account_id', $accounts->pluck('id')->toArray());
        }

        $lastTransaction = $query->first(['transaction_journals.*']);
        if (null !== $lastTransaction) {
            return new Carbon($lastTransaction->date);
        }

        return null;
    }
}