I am not very good at SQL, so I tried to just process it through PHP and make very simple mySQL queries, but apparently I am running out of memory (in PHP), since I process a lot of data (and makes tons of terrible nested foreach's ) I hope there may be a way to overwrite my SQL so that this does not happen.
Here are the tables I'm dealing with (only the relevant fields are shown):
The organization
Fundingycles
- CycleID
- NonUnityCutBackAmount
- UnityCutBackAmount
AdministrativeRequests
- CycleID
- Orgid
- Amountrequested
- Amountfunded
- Peroff
CapitalRequests
- CycleID
- Orgid
- Amountrequested
- Amountfunded
- Peroff
Eventrequests
- CycleID
- Orgid
- Amountrequested
- Amountfunded
- Peroff
- Unityreq
So, here is the hard part. I need to create the amounts for each organization AmountRequested , AmountFunded (these two seem fairly easy, although they are not sure about the summation over several tables) and the calculated AmountAfterCutback field from the queries that are in all the query tables ( AdministrativeRequests , CapitalRequests , EventRequests ).
AmountAfterCutback is: AmountFunded - (AmountFunded * Cutback ).
This can be quite simple if the reduction was just one value. However, Cutback comes from the FundingCycles table. Thus, I can’t just get the direct amount and apply the reduction, I have to find out the reduction for each request, and to do this, I have to look at each request and then check its CycleID and see what reduction for this request is in the table FundingCycles .
By default, Cutback will be NonUnityCutBack . However, if the request is a Unity request ( EventRequests only, UnityReq == 1 ), the reduction is UnityCutBack . However, it becomes even more complicated since the reduction can be applied individually to each request, redefining unity or uneven reduction (if PerOff is > 0 ).
So, somehow I have to summarize each request that the organization made, and correctly calculate the fields mentioned above. I will show my solution below, which I will warn about, definitely not very, and at the moment it does not even work, because it runs out of memory when trying to process it through PHP. I know this is the best way to do this. Is there some way to do a conditional calculation of each request so that it can be summarized in the request, and not my below inconvenient PHP solution? Any ideas? Thanks to everyone who had the patience to even read this far, I know this is a PITA question. Any help is much appreciated!
function calculate_public_records() { $total_req_total = 0; $total_funded_total = 0; $total_cutback_total = 0; $organizations = array(); foreach($this->organizations as $org) { $id = $org['OrgID']; $org_req_total = 0; $org_funded_total = 0; $org_cutback_total = 0; $cycles = array(); foreach($this->cycles as $cycle) { $cycle_req_total = 0; $cycle_funded_total = 0; $cycle_cutback_total = 0; $cycle_requests = array(); foreach($this->request_types as $type) { $reqs = $this->funding_request_model->getRequests($type, $cycle['CycleID'], $id); foreach($reqs as $r) { $cutback = $cycle['NonUnityCutBack']; if ($type == "Event") $cutback = ($r->UnityReq == 1) ? $cycle['UnityCutBack'] : $cutback; $cutback = ($r->PerOff != 0) ? $r->PerOff : $cutback; $request = array(); $request['id'] = $r->ReqID; $request['type'] = $type; $request['name'] = $r->Title; $request['requested'] = number_format($r->RequestTotal, 2); $request['funded'] = number_format($r->AmtFunded, 2); $request['cutback'] = number_format(($r->AmtFunded - ($r->AmtFunded * $cutback)), 2); $cycle_req_total += $request['requested']; $cycle_funded_total += $request['funded']; $cycle_cutback_total += $request['cutback']; $cycle_requests[] = $request; } } $cycle_totals = array(); $cycle_totals['requested'] = number_format($cycle_req_total, 2); $cycle_totals['funded'] = number_format($cycle_funded_total, 2); $cycle_totals['cutback'] = number_format($cycle_cutback_total, 2); $org_req_total += $cycle_req_total; $org_funded_total += $cycle_funded_total; $org_cutback_total += $cycle_cutback_total; $cycles[] = array('name' => $cycle['CycleName'], 'requests' => $cycle_requests, 'totals' => $cycle_totals); } $org_totals = array(); $org_totals['requested'] = number_format($org_req_total, 2); $org_totals['funded'] = number_format($org_funded_total, 2); $org_totals['cutback'] = number_format($org_cutback_total, 2); $total_req_total += $org_req_total; $total_funded_total += $org_funded_total; $total_cutback_total += $org_cutback_total; $organizations[] = array('id' => $org['OrgID'], 'name' => $org['Organization'], 'cycles' => $cycles, 'totals' => $org_totals); } $totals = array(); $totals['requested'] = number_format($total_req_total, 2); $totals['funded'] = number_format($total_funded_total, 2); $totals['cutback'] = number_format($total_cutback_total, 2); return array('organizations' => $organizations, 'totals' => $totals);
Calculation Summary:
# For tables AdministrativeRequests, CapitalRequests & EventRequests, calculate: SUM(AmountRequested) SUM(AmountFunded) # For tables AdministrativeRequests, CapitalRequests & EventRequests, calculate: AmountAfterCutback = AmountFunded - (AmountFunded * Cutback) # Cutback calculation pseudocode if( <table>.PerOff > 0 ) { Cutback = <table>.PerOff } elseif ( <table> == EventRequests && EventRequests.UnityReq == 1 ) { Cutback = FundingCycles.UnityCutBack } else { Cutback = FundingCycles.NonUnityCutBack }