Query largest spending that's related to a tag for WeeklyReport mail
MODIFY
app/Jobs/SendWeeklyReports.php
app/Jobs/SendWeeklyReports.php
@@ -27,6 +27,7 @@ class SendWeeklyReports implements ShouldQueue {
foreach ($spaces as $space) {
$totalSpent = DB::select('SELECT SUM(amount) AS foo FROM spendings WHERE space_id = ? AND happened_on >= ? AND happened_on <= ?', [$space->id, $lastWeekDate, $currentDate])[0]->foo;
+ $largestSpendingWithTag = DB::select('SELECT spendings.amount AS amount, tags.name AS tag_name FROM spendings INNER JOIN tags ON spendings.tag_id = tags.id WHERE spendings.space_id = ? AND spendings.happened_on >= ? AND spendings.happened_on <= ? AND spendings.tag_id IS NOT NULL ORDER BY spendings.amount DESC LIMIT 1', [$space->id, $lastWeekDate, $currentDate]);
foreach ($space->users as $user) {
// Only send if user wants to receive report
@@ -34,7 +35,8 @@ class SendWeeklyReports implements ShouldQueue {
Mail::to($user->email)->queue(new WeeklyReport(
$space,
$week,
- $totalSpent
+ $totalSpent,
+ $largestSpendingWithTag
));
}
}

MODIFY
app/Mail/WeeklyReport.php
app/Mail/WeeklyReport.php
@@ -14,11 +14,13 @@ class WeeklyReport extends Mailable {
protected $space;
protected $week;
protected $totalSpent;
+ protected $largestSpendingWithTag;
- public function __construct(Space $space, $week, $totalSpent) {
+ public function __construct(Space $space, $week, $totalSpent, $largestSpendingWithTag) {
$this->space = $space;
$this->week = $week;
$this->totalSpent = $totalSpent;
+ $this->largestSpendingWithTag = $largestSpendingWithTag;
}
public function build() {
@@ -26,7 +28,8 @@ class WeeklyReport extends Mailable {
->with([
'space' => $this->space,
'week' => $this->week,
- 'totalSpent' => $this->totalSpent
+ 'totalSpent' => $this->totalSpent,
+ 'largestSpendingWithTag' => $this->largestSpendingWithTag
]);
}
}

MODIFY
resources/views/emails/weekly_report.blade.php
resources/views/emails/weekly_report.blade.php
This week (#{{ $week }}) you've
<ul>
<li>Spent CURRENCY {{ number_format($totalSpent / 100, 2) }}</li>
- <li>Most of which you've spent on SPENT_MOST_TAG_NAME (CURRENCY SPENT_MOST_TAG_AMOUNT)</li>
+ @if (count($largestSpendingWithTag))
+ <li>Most of which you've spent on {{ $largestSpendingWithTag[0]->tag_name }} (CURRENCY {{ number_format($largestSpendingWithTag[0]->amount / 100, 2) }})</li>
+ @endif
</ul>
If you don't want to receive a weekly report, head over to <a href="{{ config('app.url') . '/settings' }}">settings</a> and let us know.

Query largest spending that's related to a tag for WeeklyReport mail