Group by Provider ID of Logged in User in Laravel

 To adjust the query to accommodate transactions where the status_id is either 1 or 6, you can use the whereIn method provided by Laravel's Query Builder. This method allows you to specify an array of values that the status_id column must match. Here's how you can modify the getTransactionAggregates method in your controller to include this logic:

namespace App\Http\Controllers; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; class TransactionController extends Controller { public function getTransactionAggregates($user_id, Request $request) { $startDate = $request->query('startDate'); $endDate = $request->query('endDate'); $transactionAggregates = DB::table('reports') ->where('reports.user_id', $user_id) ->join('providers', 'reports.provider_id', '=', 'providers.id') ->whereBetween('reports.created_at', ["$startDate 00:00:00", "$endDate 23:59:59"]) ->whereIn('reports.status_id', [1, 6]) // Modified to include status_id 1 or 6 ->select( 'providers.id', 'providers.provider_name', DB::raw('COUNT(reports.id) as Count'), DB::raw('SUM(reports.amount) as Value'), DB::raw('SUM(reports.profit) as Profit') ) ->selectRaw( 'SUM(CASE WHEN reports.provider_id = 158 AND (reports.status_id = 1 OR reports.status_id = 6) AND reports.created_at BETWEEN ? AND ? THEN reports.profit ELSE 0 END) as commission', ["$startDate 00:00:00", "$endDate 23:59:59"] ) ->groupBy('providers.id', 'providers.provider_name') ->paginate(50); return response()->json($transactionAggregates); } }

• In the selectRaw method for calculating commission, the condition has been adjusted to include transactions where status_id is either 1 or 6. This is done by using (reports.status_id = 1 OR reports.status_id = 6) within the CASE statement.

This modification allows the query to aggregate transactions and calculate commissions based on the specified status_id conditions, providing flexibility in how transaction data is filtered and summarized.