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.

Comments

Post a Comment

Popular posts from this blog

2 Column unique validation in controller in Laravel

Two Column unique validation in controller in Laravel In Laravel, ensuring a combination of two columns is unique within a database table often arises when dealing with complex data relationships, such as enforcing that a user can only have one role within a specific organization. This scenario cannot be directly handled through Laravel's standard unique validation rule, which traditionally operates on single fields. However, Laravel is flexible enough to accommodate this requirement through custom validation logic or advanced usage of its validation features. To implement a two-column unique validation in a Laravel controller, one effective method involves utilizing a combination of custom validation rules and querying the database to check for the existence of the specific combination of values. The approach involves creating a custom validation closure that leverages Laravel's Query Builder to search the relevant table for records matching both column values submitted by th...
Read more

Axis Bank BBPS API Integration

Integrating with Axis Bank's Bharat Bill Payment System (BBPS) involves a few key steps that require both technical know-how and access to specific financial services APIs. The BBPS, facilitated by the National Payments Corporation of India (NPCI), provides a standardized platform for making bill payments across the country. While I can guide you through the general process, please note that the exact details, including API credentials and specific integration steps, will be available only through Axis Bank's official channels and documentation. ### General Steps for API Integration: 1. **Register with Axis Bank**: Before you can integrate with their BBPS API, you need to establish a formal relationship with Axis Bank. This involves registering your business and applying for access to their payment APIs. The registration process may require you to submit business details, KYC documentation, and other relevant information. 2. **API Documentation**: Once registered, you will gain...
Read more