Level 104
What is the connection between Transaction and client_id?
Sep 12, 2022
16
Level 1
How to calculate a php variable for each individual client
Hey all,
Building a simple unit balance calculator, that takes inputs from a couple tables and calculates the current total dynamic unit balance. I have the formula set up, and is working rudimentary.
The issue i'm having is that the php variable $totalBalance is being displayed next to each client, as I've placed it as a member in the foreach loop that displays the rest of the client information.
What's the best way to associate a $totalBalance php variable to a client_id, so that each clients $totalBalance is individually calculated and displayed separately.
$totalBalance variable calculation located in my index controller:
$totalIssues = Transaction::where('transaction_type', 'Issue')->sum(\DB::raw('gross_value / unit_price - entry_fee'));
$totalRedemptions = Transaction::where('transaction_type', 'Redemption')->sum(\DB::raw('gross_value / unit_price'));
$totalBalance = $totalIssues - $totalRedemptions;
Relevant section of view that displays the $totalBalance:
<td>{{ $loop->iteration }}</td>
<td>{{ $unit_balance->entityLink->entity_name }}</td>
<td>{{ $unit_balance->priceLink->unit_price }}</td>
<td>{{ $unit_balance->unit_balance }}</td>
<td>{{ $totalBalance}}</td>
Clients can be differentiated between with their client_id, and each client_id can only have one unit_balance, which is why the above is setup the way it is, and displays names through relationship links.
Level 1
@tykus There is an extra step involved in between, each entity has a client that owns it.
So each entity has its own unit balance, each entity has an associated client_id as well.
Level 104
@Tchopa not following; can you share the relevant database tables?
Level 1
Sure
Clients migration:
Schema::create('clients', function (Blueprint $table) {
$table->id();
$table->string('first_name');
$table->string('last_name');
$table->string('street_address');
$table->string('suburb');
$table->string('state');
$table->integer('postcode');
$table->string('country');
Entity migration:
Schema::create('entities', function (Blueprint $table) {
$table->id();
$table->string('entity_name');
$table->unsignedInteger('client_id');
$table->string('business_address');
$table->string('suburb');
$table->string('state');
$table->integer('postcode');
$table->string('country');
Unit Balance migration:
Schema::create('unit_balances', function (Blueprint $table) {
$table->id();
$table->unsignedInteger('entity_id');
$table->decimal('unit_price', 10, 8);
$table->decimal('unit_balance', 25, 8);
Transactions migration:
Schema::create('transactions', function (Blueprint $table) {
$table->id();
$table->unsignedInteger('entity_id');
$table->date('effective_date');
$table->decimal('gross_value', 25, 2);
$table->decimal('units_transacted', 20, 8);
$table->decimal('unit_price', 15, 8);
$table->string('transaction_type');
$table->decimal('entry_fee', 20, 2);
Level 104
@Tchopa last question (I think!)
each entity has a client that owns it
So the Client has one Entity; and an Entity has many Transaction? How does unit_balances contribute to $totalBalance here?
1 like
Level 1
@tykus You can ask as many questions as you like mate! You're doing me a huge favour helping out.
Yeah so a client has one entity, and an entity can have many transactions. These transactions affect the entities unit balance.
Unit_balances doesn't actually impact the $totalBalance here, I just want it displayed on the unit_balances index view.
The transactions table is where the data is being drawn from to calculate this $totalBalance.
Level 104
@Tchopa okay, and you're iterating over a Collection of Client instances?
Level 1
@tykus Over a collection of Entity instances, the goal is to calculate each entities unit balance, as transactions are associated with entities via a link with entity_id.
Level 104
@Tchopa I don't know the exact query you need, but working up (or down) from this example query:
$entities = Entity::query()
->selectRaw("
entities.*,
SUM(CASE WHEN transaction_type = 'Issue' THEN gross_value / unit_price - entry_fee) as total_issues,
SUM(CASE WHEN transaction_type = 'Redemption' THEN gross_value / unit_price) as total_redemptions,
")
->join('transactions', 'transactions.entity_id', 'entities.id')
->groupBy('transactions.entity_id')
->get();
// looping over entities:
<td>{{ $entity->total_issues - $entity->total_redemptions }}</td>
1 like
Level 1
Yeah alright I think I can work with that, you're a lifesaver mate, been stuck on this for a few days. Thanks a lot!
Level 1
Hey mate, just tried plugging in your query with a few modifications and got this back
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as total_issues, SUM(CASE WHEN transaction_type = 'Redemption' THEN gr' at line 3 (SQL: select transactions.*, SUM(CASE WHEN transaction_type = 'Issue' THEN gross_value / unit_price - entry_fee) as total_issues, SUM(CASE WHEN transaction_type = 'Redemption' THEN gross_value / unit_price) as total_redemptions, from `transactions` inner join `transactions` on `transactions`.`entity_id` = `entities`.`id` group by `transactions`.`entity_id`)
Level 104
@Tchopa there is a stray comma at the end of the raw SELECT statement that needs removing; forgot the END as the end of the CASE staement:
->selectRaw("
entities.*,
SUM(CASE WHEN transaction_type = 'Issue' THEN gross_value / unit_price - entry_fee END) as total_issues,
SUM(CASE WHEN transaction_type = 'Redemption' THEN gross_value / unit_price END) as total_redemptions
")
Aside, you have selected FROM transactions and JOIN transactions???
from `transactions` inner join `transactions`
Level 1
The transactions that contain the 'transaction_type' and 'gross value unit price etc' are all located in the transactions table. I might join the entities table instead.
Level 104
@Tchopa you're looping over Entities so I started there; you're aggregating the Transactions
Level 1
@tykus Okay, I getcha. I'll go with what you sent and let you know how it goes. Thanks
Level 1
Mate, looks like its working. Balances are correct and its matching to the correct entities. You're the best.
1 like
Please or to participate in this conversation.