Hi
I spent a couple of hours on this today.
Here's what I've done.
First I created a new list to store the plan names called 'Medical Aid Names'
Then I edited the main 'Medical Aid Plan Rates' list's element 'Medical Aid Name' and changed that to a database join element and re-edited all the records to replace the string value with the db look up from 'medical aid names' list.
(As soon as you see yourself typing in the same value into several records, you should stop and re-consider your database design as the structure is not normalized. This can lead to discrepancies in the data as records are e added/edited etc. The way I have updated it means that the plan name is managed from a central table, and all other lists/tables/elements that need to use those strings reference that master table.)
Then I edited your PHP calc.
First of all you had it running only when users EDITED the form, but as its not storing to the database edit is not possible. I set it to run on BOTH. This is probably where a lot of your confusion about it not working comes from?
I then updated the script for reference here's what I did:
PHP:
<?php
// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();
$session = JFactory::getSession();
$data = $formModel->_formData;
// The search criteria.
$children = (int) $data['___nr_child_dependents'];
$adults = (int) $data['___nr_adult_dependent'];
$salary = (float) $data['___gross_salary'];
// Get all the plan rates
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('r.*, n.name AS med_aid_label')->from('medaid_plan_rates AS r')
->join('INNER', 'med_aid_names AS n ON n.id = r.med_aid_name');
$db->setQuery($query);
$planRates = $db->loadObjectList();
/*
* Calculate the plan costs based on the search criteria. Store in session.
* 'Calculation result' list has calc elements which then query the session.
*/
foreach ($planRates as $planRate)
{
$key = $plan->plan . '.' . $plan->med_aid_name;
if ($children <= $planRate->child_depend && $adults <= $planRate->adult_depend)
{
// The plan is available for the # children/adults
$total = $plan->member_rate + ($adults * $plan->adult_depend) + ($children + $plan->child_depend);
// echo "$key = $total <br>";
$session->set($key, $total, 'calc');
}
else
{
// Its not available so clear out old calcs.
$session->clear($key, 'calc');
}
}
So when someone submits a search the plans are queried and any acceptable plans are calculated and the cost stored in Joomla's session.
Then I created a new list called 'Calculator results'.
I added two example calculations elements to this list for the first three columns. '
Basic / low savings', '
Hospital', 'Only savings'
Each one has a calculation similar to:
PHP:
$session = JFactory::getSession();
$key = 'Hospital Only.{medaid_calculator_results___medical_aid_name_raw}';
return $session->get($key, null, 'calc');
You should be able to replicate these elements for the other plans. Although I see you have 2 sections in plan_rates with the same name 'Savings' - not sure why? But this should be changed I would think. Again that's another reason why probably plan_names should be a look up list, so you can have 2 labels 'Savings' each with a unique reference which would distinguish in your data one 'Savings' from another.
