teojincheng
Posts: 2
Joined: Wed Jul 29, 2020 9:59 am

Dirver Login Event Report and Trip & Idle Detailed Report

Thu Aug 06, 2020 8:37 am

Hello All,

I have added drivers and vehicles to my application.

I have also created the Driver Login event rule.

I am able to create a Event Rule report based on driver login for all events. For the event report. I can see the driver name as a string

See Image attached.
driverLoginEventRuleReport.PNG
driverLoginEventRuleReport.PNG (83.56 KiB) Viewed 317 times
I am also able to create a Trip and Idle (Detailed) report of my vehicles.

See image attached.
TripAndIdleDetailed.PNG
TripAndIdleDetailed.PNG (87.32 KiB) Viewed 317 times
What I want to achieve is to create a Report that is similar to Trip and Idle (Detailed) but I want to be able to show the driver name as a string for the vehicle in that report.

I am not very sure how to go about creating such a report.

Below are approaches which I thought is possible.

1. Write my own SQL code which joins the related tables and put that SQL code as new a data provider. After that, I can create a new kind of report based on the new data provider. The problem in this approach is. I cannot see connection between the users table and the other tables(example reportv3_data_store ) inside the database system.

2. Try and manipulate the existing data provider like eventprovider and distance01provider such that I can somehow include the driver name

3. Create some sort of even rule in my application for the vehicle start and stopping and combine it with my driver login event rule. With all these event rules combined. I generate a Event Rule report which fulfills 3 events of vehicle start, stop, driver login. Then the report generated will achieve the outcome I want.


I am not sure if my approaches are logical. I hope to hear more suggestions on how I can create the report I want.

Thank you in advance.

ggsgeek
Posts: 329
Joined: Wed Feb 02, 2011 6:51 am

Re: Dirver Login Event Report and Trip & Idle Detailed Report

Thu Aug 06, 2020 8:13 pm

You should go with approach #2.

Create a report "Trip & Idle with Driver Name" and modify the query underneath. Most likely you should find the Driver name in the dataprovider, if not you can create and new provider and edit the data collection to include the driver.

Regards,

teojincheng
Posts: 2
Joined: Wed Jul 29, 2020 9:59 am

Re: Dirver Login Event Report and Trip & Idle Detailed Report

Wed Aug 12, 2020 4:26 am

I have been trying to mess around with the code to achieve Trip and Idle detailed report with the vehicle driver name.

Inside Reporting>Manage Reports, I went to modify the SQL based on my current understanding of GPSGate.


Below is my SQL code:

Code: Select all

SELECT DISTINCT
	CAST([PeriodStart] AS DATETIME) AS PeriodStart,
	CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
	[ApplicationID] AS ApplicationID,
	Users.Username,
	Users.Name,
	TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart)) AS Duration,
	CASE Distance01.FatPointState WHEN 'idle' 
		THEN TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart))
		ELSE 0 END AS IdleDuration,
	CASE Distance01.FatPointState WHEN 'run' 
		THEN TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart))
		ELSE 0 END AS TripTime,
	Distance01.SamplingStart,
	Distance01.SamplingEnd,
	Distance01.UserID,
	Distance01.StartAddress,
	Distance01.StopAddress,
	Distance01.DistanceGps,
	Distance01.DistanceOdo,
	Distance01.MaxSpeed,
	CASE WHEN Distance01.DistanceGps > 0 AND TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart)) > 0 THEN
		Distance01.DistanceGps / TIME_TO_SEC(TIMEDIFF(Distance01.SamplingEnd, Distance01.SamplingStart)) 
	ELSE
		0
	END AS AvgSpeed,
	Distance01.FatPointState,
	Distance01.Fuel,
        Drivers.DriverName
FROM Distance01
	JOIN Users ON Users.UserID = Distance01.UserID
        JOIN Drivers ON Distance01.UserID = Drivers.VehicleUserID
WHERE Distance01.UserID IN (SELECT UserID FROM TagUsers WHERE TagUsers.TagID IN [TagID])
	AND Distance01.SamplingStart >= [PeriodStart] 
	AND Distance01.SamplingStart <= [PeriodEnd]
	AND Distance01.FatPointState != 'sleep'
	ORDER BY Distance01.UserID, Distance01.SamplingStart

I join the 'Drivers' system query because I want the driver's name from the 'Drivers' system query.

As soon as I do this, I encounter 2 problems. One. There are duplicate records for just the ordinary trip and idle data. Data like start time, stop time, duration are duplicated because of the JOIN. I overcome the situation by using DISTINCT. It seemingly works.

Use the report designer, I group by the DriverName variable.

Please see picture below of my current output.
TJCOUTPUT.PNG
TJCOUTPUT.PNG (58.53 KiB) Viewed 219 times
I think the output is the effect that I want. But I noticed a 'problem'.

Lets say on Monday, the driver alan is 'logged in' via the ibutton value to the device 'vehicle1'. I run my report on monday. The outcome of the report is correct.

I leave the app as it is. On tuesday, I move the simulator vehicle1 from start point to end point. On the list of vehicles UI, It still shows that alan is the driver name for vehicle1.

But when I run my customised report. There is no entry for vehicle1. Then when I change the ibutton/drive id for vehicle1 to another driver. Then I move the vehicle again. When I run my report. It will show the entry for vehicle1 and the new driver.

I am not sure if my understanding of gpsgate is insufficient or my query is incorrect.

Return to “GpsGate Server - How to setup and use”