Time Calculation

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
rockettpc
Posts: 13
Joined: 2015-03-06 17:59
Location: USA
Contact:

Time Calculation

Post by rockettpc » 2015-03-10 22:12

I have a question regarding time calculation.

How would I auto update a duration field? See attached photos.

Current view with my mock up.
calculation_request.png
calculation_request.png (39.05 KiB) Viewed 3734 times
This is my setting in AppGini
duration_image.png
duration_image.png (86.43 KiB) Viewed 3734 times
I assume that it will be similar to http://bigprof.com/appgini/tips-and-tut ... sing-hooks Still not sure how I would do it tho.

rockettpc
Posts: 13
Joined: 2015-03-06 17:59
Location: USA
Contact:

Re: Time Calculation

Post by rockettpc » 2015-03-11 16:02

<?php
require_once('db.php');
$sql = "SELECT service_call_number, trip_start_time, trip_end_time, TIMEDIFF( trip_end_time, trip_start_time ) AS TimeDiff
FROM service_calls"
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
echo $row['TimeDiff'];
}
?>

This SQL Query works and does my time calculation, but its not working in php tags. I was trying this to get around needing the field in the database, this would be a view basically. The data would not take up space in the database.

rockettpc
Posts: 13
Joined: 2015-03-06 17:59
Location: USA
Contact:

Re: Time Calculation

Post by rockettpc » 2015-03-11 20:09

Okay I have found a solution... See as follows.

1st I created a VIEW in phpadmin:

CREATE VIEW timediffview
AS
SELECT id, trip_start_time, trip_end_time,
TIMEDIFF (trip_end_time , trip_start_time) TimeDiff
FROM service_calls


2nd I created an EVENT in phpadmin:

CREATE EVENT update_duration

ON SCHEDULE EVERY 1 MINUTE

DO
UPDATE service_calls, timediffview
SET duration = timediffview.TimeDiff
Where service_calls.id = timediffview.id;

Now every 1 minute mysql will auto update the duration field under my service_call table with the calculated view column called TimeDiff. Note that by default my EVENT SCHEDULER was off, and i had to turn it on by running SQL command: SET GLOBAL EVENT_SCHEDULER = ON;

Then in my html template for the print view I added <%%VALUE(duration)%%> in place of my {AppData}
duration_image_2.png
duration_image_2.png (66.61 KiB) Viewed 3724 times

rockettpc
Posts: 13
Joined: 2015-03-06 17:59
Location: USA
Contact:

Re: Time Calculation

Post by rockettpc » 2015-03-12 23:24

Well that works great on my localhost, if you have a shared hosting server through GoDaddy for sure, possibly others you cannot enable the events scheduler. Tech support informed me that this option is only available with a dedicated server or VPS.

I have my own Windows Server 2012 at our business location with business internet and static IP's, so I installed WAMP and forwarded the ports and modifyed the phpmyadmin.conf and the httpd.conf file so that I can host and access my database application using my configuration above. The domain is our public ip address:portnumber. and that can be annoying for some, but you can setup a subdomain through your shared host provider and forward it to your public ip so then you have a example.com rather than a xxx.xxx.xxx.xxx:xxxx

Post Reply