<commit_before><?php

namespace Gallib\Macope\App\Queries;

use Gallib\Macope\App\JournalEntry;

class JournalEntryQuery extends AbstractQuery
{
    /**
     * Specify the model class name
     *
     * @return  string
     */
    protected function modelClassName()
    {
        return JournalEntry::class;
    }

    /**
     * Getter for monthly balances
     *
     * @param  integer $accountId
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function getMonthlyBalances($accountId)
    {
        $query = $this->model->newQuery();

        $query
            ->whereIn('date', function($query) use ($accountId){
                $query
                    ->select(\DB::raw('MAX(date)'))
                    ->from($this->model->getTable())
                    ->whereNotNull('balance')
                    ->where('account_id', '=', $accountId)
                    ->distinct()
                    ->groupBy(\DB::raw('YEAR(date), MONTH(date)'));
            })
            ->whereNotNull('balance')
            ->where('account_id', '=', $accountId);

        return $query->get();
    }

    /**
     * Getter for the yearly billing
     *
     * @param integer|null $year
     * @return \Illuminate\Support\Collection
     */
    public function getYearlyBilling($year = null)
    {
        $query = \DB::table('journal_entries')
            ->select('categories.name as category_name', 'type_categories.name as type_category_name', \DB::raw('YEAR(journal_entries.date) as year'), \DB::raw('MONTH(journal_entries.date) as month'), \DB::raw('SUM(journal_entries.credit) as credit'), \DB::raw('SUM(journal_entries.debit) as debit'))
            ->join('categories', 'categories.id', '=', 'journal_entries.category_id')
            ->join('type_categories', 'type_categories.id', '=', 'categories.type_category_id')
            ->groupBy(\DB::raw('categories.name, YEAR(journal_entries.date), MONTH(journal_entries.date)'));

        if (!is_null($year)) {
            $query->whereYear('journal_entries.date', '=', $year);
        }

        return $query->get();
    }

    /**
     * Returns years that have at least one entry
     *
     * @return \Illuminate\Support\Collection
     */
    public function getAvailableYears()
    {
        $query = \DB::table('journal_entries')
            ->select(\DB::raw('YEAR(journal_entries.date) as year'))
            ->groupBy('year');

        return $query->get();
    }
}
<commit_msg>fix SQL error (closes #18)<commit_after><?php

namespace Gallib\Macope\App\Queries;

use Gallib\Macope\App\JournalEntry;

class JournalEntryQuery extends AbstractQuery
{
    /**
     * Specify the model class name
     *
     * @return  string
     */
    protected function modelClassName()
    {
        return JournalEntry::class;
    }

    /**
     * Getter for monthly balances
     *
     * @param  integer $accountId
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function getMonthlyBalances($accountId)
    {
        $query = $this->model->newQuery();

        $query
            ->whereIn('date', function($query) use ($accountId){
                $query
                    ->select(\DB::raw('MAX(date)'))
                    ->from($this->model->getTable())
                    ->whereNotNull('balance')
                    ->where('account_id', '=', $accountId)
                    ->distinct()
                    ->groupBy(\DB::raw('YEAR(date), MONTH(date)'));
            })
            ->whereNotNull('balance')
            ->where('account_id', '=', $accountId);

        return $query->get();
    }

    /**
     * Getter for the yearly billing
     *
     * @param integer|null $year
     * @return \Illuminate\Support\Collection
     */
    public function getYearlyBilling($year = null)
    {
        $query = \DB::table('journal_entries')
            ->select('categories.name as category_name', 'type_categories.name as type_category_name', \DB::raw('YEAR(journal_entries.date) as year'), \DB::raw('MONTH(journal_entries.date) as month'), \DB::raw('SUM(journal_entries.credit) as credit'), \DB::raw('SUM(journal_entries.debit) as debit'))
            ->join('categories', 'categories.id', '=', 'journal_entries.category_id')
            ->join('type_categories', 'type_categories.id', '=', 'categories.type_category_id')
            ->groupBy(\DB::raw('type_categories.name, categories.name, YEAR(journal_entries.date), MONTH(journal_entries.date)'));

        if (!is_null($year)) {
            $query->whereYear('journal_entries.date', '=', $year);
        }

        return $query->get();
    }

    /**
     * Returns years that have at least one entry
     *
     * @return \Illuminate\Support\Collection
     */
    public function getAvailableYears()
    {
        $query = \DB::table('journal_entries')
            ->select(\DB::raw('YEAR(journal_entries.date) as year'))
            ->groupBy('year');

        return $query->get();
    }
}

{'fraction_non_alphanumeric': 0.15838196820285771, 'fraction_numerical': 0.0004024954719259408, 'mean_word_length': 2.276203032300593, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 49, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4250635', 'n_tokens_mistral': 1458, 'n_tokens_neox': 1425, 'n_words': 253}