• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Time Calculation [Solved]

Status
Not open for further replies.

jeanl

Member
Hi

I would like to ask if anyone can help me to create a calculation element, and provide me with the correct calculation formula.

I have a list with two elements:
- service
- duration

In a second list, I have a db join element, rendered as a checkbox. When you select the checkboxes of services required, I would like the calculation value to be the sum of all the services selected, which is a db join from the first list.

Can anyone assist me please?

Thanks in advance.
 
Would it suit to have the first list with
- Service
- Hour
- Minute
- Duration (Hidden from Form and List View)

Have a PHP plugin on the form which would be similar too
Code:
$hour = $formModel->formData['list_one___hour'];
$minute = $formModel->formData['list_one___minute'];
$hourtominute = $hour * 60;
$total = $hourtominute + $minute;
$formModel->updateFormData('list_one___duration', $total, true);

This would then calculate the amount of total minutes for the duration of that specific task

You would then need to have a script (or possibly the calc plugin) to have all of the duration amounts of the tasks added together.

Then use this Form PHP Plugin on the second list
Code:
$duration = $formModel->formData['list_two___duration_calc'];
$hour = floor($duration / 60);
$minb = $duration - ($hour * 60);
$min = sprintf("%02d",(int)$minb);
$total = $hour . ':' . $min;
$formModel->updateFormData("list_two___two_total", $total, true);

Something like this should help sort your issue.
 
Hi Matthew

Thank you so much. I will try this. In list one I already have a duration plugin (std Fabrik plugin) showing the hours and minutes of each service, but I will change this to two normal fields as a test and see if your suggested method works. I will let you know.

Many thanks.

Jean
 
You can use the duration plugin if you like. Think you will need to change the first script to be more like

Could be a little off with syntax as I am not testing it
Code:
$hour = $formModel->formData['list_one___duration'][0];
$minute = $formModel->formData['list_one___duration'][1];
$hourtominute = $hour * 60;
$total = $hourtominute + $minute;
$formModel->updateFormData('list_one___duration', $total, true);
 
OK, I just tried the suggestion, and it is not working. However, I am not 100% sure if I am replacing 'list_one___duration' with the correct value?

In list one, called services, one element is called "Duration" and this is the plugin "hour & minutes" where I indicate the individual service's duration. This is the element I inserted in your suggestion, but it does not work.
 
Matthew
Are you certain about this:


Should it not be list_one___duration?
Definately sure. That should be a field that is created in the second table. It doesn't need to be shown as it is just used to calculate the total minutes from the tasks that are selected from the checkboxes.

I also noticed that my first script was wrong. Below should be better (a new duration_total field should be created to keep this in minutes so you are able to link them across)

Code:
$hour = $formModel->formData['list_one___duration'][0];
$minute = $formModel->formData['list_one___duration'][1];
$hourtominute = $hour * 60;
$total = $hourtominute + $minute;
$formModel->updateFormData('list_one___duration_total', $total, true);
 
A good way to test the output is
Code:
echo "<pre>";print_r($variable);exit;
When you replace $variable with your variable it will exit the script before it is completed and show the value outputted.
E.G to check to see if hours are showing correctly
Code:
$hour = $formModel->formData['list_one___duration'][0];
echo "<pre>";print_r($hour);exit;
$minute = $formModel->formData['list_one___duration'][1];
$hourtominute = $hour * 60;
$total = $hourtominute + $minute;
$formModel->updateFormData('list_one___duration_total', $total, true);
 
It warms my heart to see folk helping each other in the Community forums. I try and keep score on these things, and keep a mental note ... and folk who help others, tend to get a little more help from me in Community.

-- hugh
 
Matthew

OK, I tried this. To explain what I did.

I have a list (list one: services) with the following elements:

service (field)
duration (time)

On the form I added a php plugin with this code you provided, and it "executes" on form submit:
$hour = $formModel->formData['services___duration'][0];
$minute = $formModel->formData['services___duration'][1];
$hourtominute = $hour * 60;
$total = $hourtominute + $minute;
$formModel->updateFormData('services___duration_total', $total, true);

The Duration element is a time element.

List 2 = Job Card

I have the following elements:

services (dbjoin from list 1)
duration (field)
total_duration (field)


This is a dbjoin from the services list, and rendered as a checklist, so I can select the services required.

This is where I need to show the total time of all the services selected in the service field.

On the form of the 2nd list, Job Card, I added a php plugin, with the code:

$duration = $formModel->formData['jobcard___duration'];
$hour = floor($duration / 60);
$minb = $duration - ($hour * 60);
$min = sprintf("%02d",(int)$minb);
$total = $hour . ':' . $min;
$formModel->updateFormData("jobcard___total_duration", $total, true);

"total duration" is a 3rd field on the Jobcard list, where I assume the result will be shown?

Is this correct?
 
Lets start by getting the services list to output the data we need.
The services list (list_one) should have three fields:
  • service (The name of the service) - Field
  • duration (Your input of the amount of time it will take to complete the task) - Time
  • duraton_total (Amount of minutes to complete the task) - Field that is hidden from Form View and Table View (For testing purposes leave this in table view for now to make sure that the value is correct).
The script should then get the values for the duration and convert them into minutes to insert to duration_total which is on the same database table. When we have this working we then hide the minute value so only the system can use it.
On the second table (jobcard) you will need:
  • services (check box's that allow you to select entry's from the services list)
  • duration (This will display the amount of minutes for all of the services). This could possibly be done by using the calc plugin provided by fabrik but I am not 100% sure. This is hidden from form and table view as it is only used to bring values across.
  • total_duration (This is calculated from the second script which will populate automatically depending on the values chosen). This should be hidden in form view
 
It warms my heart to see folk helping each other in the Community forums. I try and keep score on these things, and keep a mental note ... and folk who help others, tend to get a little more help from me in Community.

-- hugh

The amount of times you guys have helped me, it is only fair to give back when I can.
 
Matthew

Lets start by getting the services list to output the data we need.
The services list (list_one) should have three fields:
  • service (The name of the service) - Field
  • duration (Your input of the amount of time it will take to complete the task) - Time
  • duraton_total (Amount of minutes to complete the task) - Field that is hidden from Form View and Table View (For testing purposes leave this in table view for now to make sure that the value is correct).
The script should then get the values for the duration and convert them into minutes to insert to duration_total which is on the same database table. When we have this working we then hide the minute value so only the system can use it.
On the second table (jobcard) you will need:
  • services (check box's that allow you to select entry's from the services list)
  • duration (This will display the amount of minutes for all of the services). This could possibly be done by using the calc plugin provided by fabrik but I am not 100% sure. This is hidden from form and table view as it is only used to bring values across.
  • total_duration (This is calculated from the second script which will populate automatically depending on the values chosen). This should be hidden in form view

Thank you very much. I will try this this afternoon and let you know. I really appreciate all your kind assistance so far.

Jean
 
Matthew

I followed the instructions and set it up. It does not show any calculation / result in the relevant fields. Can you advise? Could it have to do with the way the php executes, for example on formload, on saving?
 
Hello

OK, I managed to het the 1st part working in the Services list (list 1). So when I add a new service and set the duration to 02:30 for example, the value displayed in the total duration time is 150 which is correct.

In the second list, I added the php plugin on the form, and made sure the placeholders point to the correct elements, but the value in the field, after form submission, is 0:00. Can you please advise?

Jean
 
Good to hear that the first part worked.

Next you will need to make sure that the minutes are calculated in the form.

You could possibly use the calc plugin (http://fabrikar.com/download/details/36/914) to add all of the minutes into another element. Publish this in the table for the time being to see if it brings back the correct amount.
 
Matthew

Thank you. I added a "test duration" element, and the element is a calc plugin. What would the string be then if I want it to add the times selected by checkboxes?

Pls see attached doc.
 

Attachments

  • List 1.pdf
    97.7 KB · Views: 312
Hi Jeanl,

Not sure how we can get this to work but I am thinking that perhaps the calc plugin is probably not the one to use. Switch it back to a field element and we will do it via a form php script.

The code below won't work due to the output from the services card being an array and I am unsure on how to use the array in the queries but it is a start for someone else to help if they are able too.
Code:
$db = JFactory::getDBO();
$service = $formModel->formData['jobcard___services_raw'];
$db->setQuery("SELECT duration_total FROM services WHERE id = $service");
$min = $db->loadResult();
$calc = array_sum($min);
$formModel->updateFormData('jobcard___test_duration', $calc, true);
 
In general, to handle an array of values you want to match in a query, you use IN() rather than =, and "implode" the array into a comma seperated list. The PHP explode() and implode() functions are very useful, allowing you to 'explode' a delimited string (i.e. list of numbers separated by commas) into an array, or implode an array into a delimited string.

So an array of integers can be imploded into a comma separated string with implode(',', $service), which will produce a string like "1,2,3,4,5". Which can then be used in the IN() part of the query.

Like this:

Code:
$db->setQuery("SELECT duration_total FROM services WHERE id IN(" . implode(',', $service) . ")");

Also ... when doing straight forward summing, quicker and easier to let MySQL do that rather than doing it in PHP ...

Code:
$db->setQuery("SELECT SUM(duration_total) AS total FROM services WHERE id IN(" . implode(',', $service) . ")");

Also ... although not required, it's good to get in the habit of building queries using the "query builder" syntax. Not so important for simple queries like this, but a definite advantage when you start getting in to building more complex queries.

Code:
$query = $db->getQuery(true);
$query->select('SUM(duration_total) AS total ')
   ->from('services')
   ->where('id IN(' . implode(',', $service) . ')');
$db->setQuery($query);
$calc = $db->loadResult();

NOTE - this assumes $service is numbers (which it is). When dealing with an array of string you want to use in an IN(), you have to go through another step to quote the strings.

-- hugh
 
Status
Not open for further replies.
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top