@php $date = date('Y-m-d'); // Get all categories and their expenses average for all year $allYearExpenses = App\Models\ExpenseCalculation::where('types', 'expense') ->whereMonth('date', '!=', now()->month) ->groupBy('category_id') ->select('category_id', DB::raw('sum(amount) as totalExpense'), DB::raw('count(distinct MONTH(date)) as totalMonths')) ->get(); $thisYearExpense = []; foreach ($allYearExpenses as $expense) { $averageExpense = $expense->totalExpense / $expense->totalMonths; $thisYearExpense[] = [ 'category_id' => $expense->category_id, 'totalExpense' => $expense->totalExpense, 'averageExpense' => $averageExpense, ]; } // Now $thisYearExpense contains the category ID, total expense, and average expense for each category. // dd($thisYearExpense); // Get the average expense in a month form all year $thisYearExpenseTotal = App\Models\ExpenseCalculation::where('types', 'expense')->select(DB::raw('sum(amount) as totalExpense'), DB::raw('count(distinct MONTH(date)) as totalMonths'), DB::raw('sum(amount)/count(distinct MONTH(date)) as averageExpense'))->first()->averageExpense; // dd($thisYearExpenseTotal); // Calculate the last month and year based on the current date $lastMonth = date('m') == '01' ? '12' : str_pad(date('m') - 1, 2, '0', STR_PAD_LEFT); $lastYear = date('m') == '01' ? date('Y') - 1 : date('Y'); // Get all categories and their expenses for the last month $lastMonthExpense = App\Models\ExpenseCalculation::whereYear('date', $lastYear) ->whereMonth('date', $lastMonth) ->where('types', 'expense') ->groupBy('category_id') ->select('category_id', \DB::raw('SUM(amount) as totalExpense')) ->get(); $lastMonthExpenseTotal = App\Models\ExpenseCalculation::whereYear('date', $lastYear) ->whereMonth('date', $lastMonth) ->where('types', 'expense') ->sum('amount'); // Get all categories $categories = App\Models\Category::all(); // Calculate the total expense without category_id 1 $totalExpenseWithoutCategory1 = App\Models\ExpenseCalculation::where('types', 'expense')->avg('amount'); @endphp

Projected Monthly Budget

@csrf

{{-- @foreach ($categories as $category) @endforeach --}} @foreach ($categories as $category) @php $thisYearExpenseCategory = collect($thisYearExpense)->where('category_id', $category->id); $lastMonthExpenseCategory = collect($lastMonthExpense)->where('category_id', $category->id); @endphp @endforeach

Category	Avg Expense	Last Month Expensed	This Month Projected Expense
{{ $category->name }}	{{ $thisYearExpense->where('category_id', $category->id)->avg('totalExpense') }}	{{ $lastMonthExpense->where('category_id', $category->id)->avg('totalExpense') }}
Total	{{ $thisYearExpenseTotal }}	{{ $lastMonthExpenseTotal }}
Total without Category 1			{{ $totalExpenseWithoutCategory1 }}
{{ $category->name }}	{{ $thisYearExpenseCategory->avg('totalExpense') }}	{{ $lastMonthExpenseCategory->avg('totalExpense') }}
Total	{{ $thisYearExpenseTotal }}	{{ $lastMonthExpenseTotal }}
Total without Category 1			{{ $totalExpenseWithoutCategory1 }}

Submit

@php $date = date('Y-m-d'); // Get all categories and their expenses for this year $thisYearExpense = App\Models\ExpenseCalculation::whereYear('date', date('Y')) ->groupBy('category_id') ->select('category_id', \DB::raw('SUM(amount) as totalExpense')) ->get(); $thisYearExpenseTotal = App\Models\ExpenseCalculation::whereYear('date', date('Y'))->where('types', 'expense')->sum('amount')/12; // Calculate the last month and year based on the current date $lastMonth = date('m') == '01' ? '12' : str_pad(date('m') - 1, 2, '0', STR_PAD_LEFT); $lastYear = date('m') == '01' ? date('Y') - 1 : date('Y'); // Get all categories and their expenses for the last month $lastMonthExpense = App\Models\ExpenseCalculation::whereYear('date', $lastYear) ->whereMonth('date', $lastMonth) ->groupBy('category_id') ->select('category_id', \DB::raw('SUM(amount) as totalExpense')) ->get(); $lastMonthExpenseTotal = App\Models\ExpenseCalculation::whereYear('date', $lastYear) ->whereMonth('date', $lastMonth)->where('types', 'expense')->sum('amount'); // Get all categories $categories = App\Models\Category::all(); // Calculate the total expense without category_id 1 $totalExpenseWithoutCategory1 = App\Models\ExpenseCalculation::where('types', 'expense')->avg('amount'); @endphp

Projected Monthly Budget

@csrf

@foreach ($categories as $category) @endforeach

Category	This Year Avg Expense	Last Month Expensed	This Month Projected Expense
{{ $category->name }}	{{ $thisYearExpense->where('category_id', $category->id)->avg('totalExpense') }}	{{ $lastMonthExpense->where('category_id', $category->id)->avg('totalExpense') }}
Total	{{ $thisYearExpenseTotal }}	{{ $lastMonthExpenseTotal }}
Total without Category 1			{{ $totalExpenseWithoutCategory1 }}

Submit