create an element to calculate avg time

[vaguemind]

i need to make something to calculate the average time of a record since it created until this record status changes to "Open" ... i will explain here : if i have a list called flight booking and it has a dbjoin element "status" have many options ( open, onhold, closed , request , etc ) i need to make an element will name it "avg time" to calculate the avg time since the record created and status still "open" ,,, how can i do this ?

 

[cheesegrits]

So do you mean a single value for the entire table? So taking the total time between creation and "now" for all records which are still open, and dividing by the number of open records?

-- hugh

 

[vaguemind]

ya exactly , a single value for every record on the table, So taking the total time between creation and "now" for all records which are still open, and dividing by the number of open records. and show it in list view like attached screen u can see it ?

 

[cheesegrits]

I'd do that by hand, personally, not through any kind of Fabrik element. Use one of the extensions that lets you create modules with PHP code in them (there's one I particularly like but can't remember the name of it), and just write a simple query to read all open' rows, and some code to calculate the average time. Probably about a dozen lines of code, if that.

-- hugh

 

[vaguemind]

There is no any way to do it using fabrik, as I am not that good at writing queries ?

Sent from my Lenovo A6000 using Tapatalk

 

[cheesegrits]

Nope. Or at least, not without doing even more custom coding.

Something like this:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->select('date_time')->from('yourtable')->where('status = "open"');
$myDb->setQuery($myQuery);
$results = $myDb->loadObjectList();
$avg_minutes = 0;
$days_hours_mins = "0:0:0";
$num_records = 0;

if (!empty($results)) {
   $total_minutes = 0;
   $num_records = 0;
   $now_date = new DateTime()
   foreach ($results as $result) {
      $start_date =new DateTime($result->date_time);
      $since_start = $start_date->diff($now_date);
      $minutes = $since_start->days *24*60;
      $minutes += $since_start->h *60;
      $minutes += $since_start->i;
      $total_minutes += $minutes;
      $num_records++;
   }
   $avg_minutes = round($total_minutes / $num_records);
   $dtF =new DateTime("@0");
   $dtT =new DateTime("@$avg_minutes");
   $days_hours_mins = $dtF->diff($dtT)->format('%a:%h:%i');
}

echo "$days_hours_mins<br />$num_records";

Obviously substitute your table and date element names in the query.

Assumes you are wanting days:hours:minutes.

Tweak the echo for whatever layout you want, with divs, CSS classes, whatever.

Not tested, just written off the top of my head, so probably has errors in it! But gives you something to start from.

-- hugh

 

[vaguemind]

i made this code embedded in php joomla module , but it returns 0:0:00 !!
and i need this avg time for every record in the list not as a module to be shown ?

 

[cheesegrits]

If it's the same for every open row, surely you could just put it in a module?

Only way to get it in every row would be to put that code in a calc element (with a return instead of an echo) which would be very wasteful in list display, as it'd repeat the same code for every row. So if you show 20 rows, you add 20 queries to the page load..

Youll have to debug the code in the usual var_dump() way.

Hugh


Sent from my HTC One using Tapatalk

 

[vaguemind]

sorry , but i cant understand well , how can i debug the code using var_dump() way ?? and i made it return instead of echo in a calc element , but it still show 0:0:00 ???

 

[cheesegrits]

Search the forums for var_dump, you'll find hundreds of posts where we instruct people on how to use var_dump() to debug code.

-- hugh

 

[vaguemind]

I made debug using var_dump() , but shows nothing same result 0:0:00 ! can someone help pls

Sent from my Lenovo A6000 using Tapatalk

 

[cheesegrits]

So did you put this code in a calc element?

Immediately before the if() line, put

var_dump((string)$myQuery, $results); exit;

... and paste the result.

-- hugh

 

[vaguemind]

So did you put this code in a calc element?

Immediately before the if() line, put

var_dump((string)$myQuery, $results); exit;

... and paste the result.

-- hugh

ya in a calc element , and the result here :

string(79) " SELECT date_time FROM flight_booking_form WHERE Flight_Booking_Status = "open"" array(0) { }

 

[cheesegrits]

OK, so does that query look right? It doesn't seem to re returning any results. Is "open" the value you save for that option?

Try running that query in phpMyAdmin, see if it returns and results, and if not, why not.

-- hugh

 

[vaguemind]

ya the value is right "Open" !

i ran the query in phpMyAdmin but , it returns nothing at all !!

 

[vaguemind]

i changed the value "Open" with "2" which is the id ,,, and results the following :

string(76) " SELECT date_time FROM flight_booking_form WHERE Flight_Booking_Status = "2"" array(1) { [0]=> object(stdClass)#433 (1) { ["date_time"]=> string(19) "2015-10-06 15:18:18" } }

 

[cheesegrits]

Yup, that's why I asked if you were sure the value was 'open'.

OK, so now take the debug line out and see what happens.

Hugh


Sent from my HTC One using Tapatalk

 

[vaguemind]

it shows in all records like this in list view :
0:0:32
2

kindly see the attached pic also

 

[cheesegrits]

OK, so now put more var_dump() statements in for other variables, spot what doesn't look right. Like, dump $since_start and $minutes at the end of the foreach() loop.

-- hugh

 

[vaguemind]

it results to :

string(76) " SELECT date_time FROM flight_booking_form WHERE Flight_Booking_Status = "2"" array(2) { [0]=> object(stdClass)#401 (1) { ["date_time"]=> string(19) "2015-10-08 14:30:34" } [1]=> object(stdClass)#402 (1) { ["date_time"]=> string(19) "2015-10-11 07:25:06" } }