Create TagRepository and move function for getting most expensive tags to it
MODIFY
app/Http/Controllers/DashboardController.php
app/Http/Controllers/DashboardController.php
namespace App\Http\Controllers;
+use App\Repositories\TagRepository;
use Illuminate\Http\Request;
use App\Earning;
@@ -22,26 +23,9 @@ class DashboardController extends Controller {
$leftToSpend = $balance - $recurrings;
$totalSpent = session('space')->spendings()->whereRaw('YEAR(happened_on) = ? AND MONTH(happened_on) = ?', [$currentYear, $currentMonth])->sum('amount');
- $mostExpensiveTags = DB::select('
- SELECT
- tags.name AS name,
- tags.color AS color,
- SUM(spendings.amount) AS amount
- FROM
- tags
- LEFT OUTER JOIN
- spendings ON tags.id = spendings.tag_id AND spendings.deleted_at IS NULL
- WHERE
- tags.space_id = ?
- AND MONTH(happened_on) = ?
- GROUP BY
- tags.id
- HAVING
- SUM(spendings.amount) > 0
- ORDER BY
- SUM(spendings.amount) DESC
- LIMIT 3;
- ', [$space_id, date('m')]);
+
+ $tagRepository = new TagRepository();
+ $mostExpensiveTags = $tagRepository->getMostExpensiveTags($space_id, 3, $currentYear, $currentMonth);
$balanceTick = 0;
$dailyBalance = [];

MODIFY
app/Http/Controllers/ReportController.php
app/Http/Controllers/ReportController.php
namespace App\Http\Controllers;
+use App\Repositories\TagRepository;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
@@ -41,28 +42,12 @@ class ReportController extends Controller {
}
private function mostExpensiveTags() {
- $mostExpensiveTags = DB::select('
- SELECT
- tags.name AS name,
- tags.color AS color,
- SUM(spendings.amount) AS amount
- FROM
- tags
- LEFT OUTER JOIN
- spendings ON tags.id = spendings.tag_id AND spendings.deleted_at IS NULL
- WHERE
- tags.space_id = ?
- GROUP BY
- tags.id
- # HAVING
- # SUM(spendings.amount) > 0
- ORDER BY
- SUM(spendings.amount) DESC;
- ', [session('space')->id]);
-
$totalSpent = session('space')->spendings()->sum('amount');
- return view('reports.most_expensive_tags', compact('mostExpensiveTags', 'totalSpent'));
+ $tagRepository = new TagRepository();
+ $mostExpensiveTags = $tagRepository->getMostExpensiveTags(session('space')->id);
+
+ return view('reports.most_expensive_tags', compact('totalSpent', 'mostExpensiveTags'));
}
public function show(Request $request, $slug) {

ADD
app/Repositories/TagRepository.php
+<?php
+
+namespace App\Repositories;
+
+use Illuminate\Support\Facades\DB;
+
+class TagRepository {
+ public function getMostExpensiveTags(int $spaceId, int $limit = null, int $year = null, int $month = null) {
+ $sql = '
+ SELECT
+ tags.name AS name,
+ tags.color AS color,
+ SUM(spendings.amount) AS amount
+ FROM
+ tags
+ LEFT OUTER JOIN
+ spendings ON tags.id = spendings.tag_id AND spendings.deleted_at IS NULL
+ WHERE
+ tags.space_id = ?';
+
+ if ($year) {
+ $sql .= ' AND YEAR(happened_on) = ?';
+ }
+
+ if ($month) {
+ $sql .= ' AND MONTH(happened_on) = ?';
+ }
+
+ $sql .= '
+ GROUP BY
+ tags.id
+ ORDER BY
+ SUM(spendings.amount) DESC
+ ';
+
+ if ($limit) {
+ $sql .= ' LIMIT ' . $limit;
+ }
+
+ $data = [$spaceId];
+
+ if ($year) {
+ $data[] = $year;
+ }
+
+ if ($month) {
+ $data[] = $month;
+ }
+
+ return DB::select($sql . ';', $data);
+ }
+}

Create TagRepository and move function for getting most expensive tags to it