[SOLVED] Group and Sum of Array

[knox203]

Hello all, I'm running results of a query into a 2 level array, and am now in need of finding the time difference, of all the results per user. an example of the array:

Array
(
    [1] => Array
        (
            [Driver] => 002
            [Event] => 1
            [Timecode] => 1220546013
            [DateTime] => 09-04-08 09:09:33
        )

    [2] => Array
        (
            [Driver] => 002
            [Event] => 2
            [Timecode] => 1220549134
            [DateTime] => 09-04-08 10:09:34
        )

    [3] => Array
        (
            [Driver] => 002
            [Event] => 1
            [Timecode] => 1220894334
            [DateTime] => 09-08-08 10:09:54
        )

    [4] => Array
        (
            [Driver] => 002
            [Event] => 2
            [Timecode] => 1220984242
            [DateTime] => 09-09-08 11:09:22
        )
)

 

I'm at a loss on how to code this correctly, if anyone could point me into the right direction, it would be greatly appreciated!! My current idea is to put every other result into a separate array (the sql query is sorting the results by time) then find the difference between $array1[0]["timecode"] and $array2[0]["timecode"] so on and so forth, but on top of that, am at a loss on how to group all the users when all is said and done. I've got a feeling this could be done much much more efficiently, someone please prove me right 

[F1Fan]

array_multisort should do what you want, if I understand corectly.

 

http://us3.php.net/manual/en/function.array-multisort.php

[knox203]

Thanks for the response, F1Fan. I'm not sure if array_multisort is going to do what I need it to do. Ultimately what I need to do is calculate the time difference of the first two results in the array for a driver, then the next two so on and so forth. I then need to group those results by the corresponding driver to give me a total time worked for each driver. Does that make sense?

[discomatt]

Where are you getting this data from?

[F1Fan]

It somewhat makes sense. You'll need to loop through them, but I'm confused as to what you want to do with the info. This should get you started:

 

<?php
$calc = false;
$diff = "";
$compare = "";
foreach ($array as $k=>$arr){
  if (!$calc){
    $calc = true;
    $compare = $arr['DateTime'];
  }
  else{
    $calc = false;
    $diff = abs($compare-$arr['DateTime'];
  }
}
?>

 

Not sure what you want to do with the $diff value, but like I said, it should get you started.

[knox203]

Discomatt, I'm getting these values from MSSQL, here's the code:

<?php
$job_select = "	SELECT	Driver.DriverCode AS Driver, 
		ManifestDriverEvent.ManifestDriverEventTypeID AS Event, 
		DATEPART(dd, ManifestDriverEvent.StartDateTime) AS Day, 
		DATEPART(mm, ManifestDriverEvent.StartDateTime) AS Month, 
		DATEPART(yyyy, ManifestDriverEvent.StartDateTime) AS Year, 
		DATEPART(hh, ManifestDriverEvent.StartDateTime) AS Hour, 
		DATEPART(mi, ManifestDriverEvent.StartDateTime) AS Minute, 
		DATEPART(ss, ManifestDriverEvent.StartDateTime) AS Second
	FROM	dbo.Driver INNER JOIN dbo.ManifestDriver ON Driver.DriverID = ManifestDriver.DriverID 
		INNER JOIN dbo.ManifestDriverEvent ON ManifestDriver.ManifestDriverID = ManifestDriverEvent.ManifestDriverID
	WHERE	(CONVERT(VARCHAR(25), ManifestDriverEvent.StartDateTime, 110) > CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1), GETDATE()), 110)) AND
		(DATEPART(yyyy, ManifestDriverEvent.StartDateTime) = DATEPART(yyyy, GETDATE()))
	ORDER BY Driver.DriverCode, ManifestDriverEvent.StartDateTime";
$job_query = mssql_query($job_select) or die("Didn't Work!");
$dates_array = array();
while ( $job_result = mssql_fetch_assoc($job_query) ) {
$dates_array[] = array(	Driver => $job_result['Driver'],
			Event => $job_result['Event'],
			Timecode => mktime($job_result['Hour'], $job_result['Minute'], $job_result['Second'], $job_result['Month'], $job_result['Day'], $job_result['Year']),
			DateTime => date('m-d-y H:m:s', mktime($job_result['Hour'], $job_result['Minute'], $job_result['Second'], $job_result['Month'], $job_result['Day'], $job_result['Year']))	);
}
?>

 

Allow me to add on to what I previously explained, I want to compare the time difference between the first two results for a driver, then do the same for the next two results and so on, then combine the total time differences for each driver to give the total time worked. I need to do it this way because we have drivers that open a manifest that we dispatch jobs to. The drivers frequently close their manifests when they're not working, this can happen multiple times in a day. It doesn't matter to the drivers how long they work, since they are paid on pure commission depending on how many packages they deliver, but we need to know the total time worked for these guys for various reasons for our dispatchers. Thanks for your help guys!

 

- Adam

[F1Fan]

I am officially lost. Do you want the difference of driver1 vs driver2, driver2 vs driver3, and so on, or driver1 vs driver2, driver1 vs driver3, driver2 vs driver1, driver2 vs driver3, and so on?

[knox203]

Driver 1 Result 1 vs Driver 1 Result 2, Driver 1 Result 3 vs Driver 1 Result 4. Sub Total 1 plus Sub Total 2 to give total time worked, then the same thing for the next driver (all drivers are returned in the array with the results sorted by driver, then by datetime). Again, drivers open and close their manifests multiple times per day, so there are multiple results per driver per day.

[F1Fan]

OK, I think I'm close to understanding. Is it sorta like "Event" 1 is the start, and "Event" 2 is the end, and you need the total times between all the 1's and 2's for each driver?

[knox203]

Yes, that's exactly right, although there are cases where "event 3" and "event 4" pop up, I haven't been able to determine which stands for what (The data is from a closed source application and they're not giving me much info into this) that's why I'm sorting by datatime, to try to avoid comparing by the Event code (even though it seems it would be much easier to do it that way).

[F1Fan]

<?php
$drivertimes = array();
$driverstart = array();
foreach ($array as $k=>$arr){
  if (($arr['Event']%2)==0){ // divisible by two means it's an end, not a start
    if (!isset($drivertimes[$arr['Driver']])) $drivertimes[$arr['Driver']] = 0;
    $drivertimes[$arr['Driver']] += $arr['DateTime']-$driverstart[$arr['Driver']];
  }
  else{
    $driverstart[$arr['Driver']] = $arr['DateTime'];
  }
}
?>

 

$drivertimes will have the total times for each driver with the 'Driver' as the key.

[knox203]

Here's the return for $drivertimes():

Array
(
    [002] => 0
    [007] => 0
    [008] => 0
    [011] => 0
    [013] => 0
    [077] => 0
    [085] => 0
    [088] => 9
    [090] => 18
    [1017] => 9
    [1031] => 9
    [1038] => 18
    [108] => 252
    [112] => 0
    [1164] => 9
    [1309] => 0
    [1324] => 9
    [1334] => 0
    [1354] => 0
    [1363] => 9
    [1375] => 9
    [1378] => 9
    [1379] => 27
    [1381] => 0
    [1385] => 0
    [1392] => 9
    [1396] => 18
    [1402] => 0
    [144] => 0
    [158] => 0
    [236] => 0
    [238] => 0
    [287] => 0
    [313] => 0
    [533] => 0
    [535] => 144
    [618] => 0
    [684] => 0
    [744] => 9
    [781] => 0
    [8] => 0
    [843] => 27
    [932] => 9
    [983] => 0
    [990024] => 0
    [990027] => 9
    [990047] => 9
    [990059] => 9
    [990097] => 0
    [990126] => 0
    [990141] => 45
    [990155] => 9
    [990168] => 9
    [990170] => 9
    [990176] => 9
    [990204] => 0
    [990209] => 0
    [990214] => 0
    [990226] => 0
    [990232] => 0
    [990233] => 0
    [990238] => 0
    [990240] => 0
    [990242] => 54
    [990245] => 54
    [990261] => 9
    [990262] => 27
    [990264] => 0
    [990266] => 0
    [990267] => 9
    [990277] => 9
    [990287] => 18
    [990289] => 9
    [990292] => 0
    [990299] => 9
    [990304] => 9
    [990309] => 9
    [990311] => 0
    [990312] => 9
    [990317] => 9
    [990318] => 9
    [990320] => 0
    [990325] => 0
    [990327] => 0
    [990328] => 0
    [990329] => 0
    [990331] => 0
    [990332] => 0
    [990333] => 0
    [990334] => 0
    [994] => 198
    [9999] => 0
)

I'm not 100% sure what I'm looking at here, would it be easier to get the total number of seconds returned by adding the differences of the Timecode, then I could just turn that into hours.

 

I should mention that every once in a while there is a result that looks like this:

    [55] => Array
        (
            [Driver] => 013
            [Event] => 2
            [Timecode] => 1222099751
            [DateTime] => 09-22-08 09:09:11
        )

    [56] => Array
        (
            [Driver] => 013
            [Event] => 2
            [Timecode] => 1222113483
            [DateTime] => 09-22-08 12:09:03
        )

    [57] => Array
        (
            [Driver] => 056
            [Event] => 1
            [Timecode] => 1221668478
            [DateTime] => 09-17-08 09:09:18
        )

    [58] => Array
        (
            [Driver] => 077
            [Event] => 1
            [Timecode] => 1220363863
            [DateTime] => 09-02-08 06:09:43
        )

    [59] => Array
        (
            [Driver] => 077
            [Event] => 2
            [Timecode] => 1220363875
            [DateTime] => 09-02-08 06:09:55
        )

Notice how driver 56 (result #57) has only returned one result, I hope this doesn't screw up what I'm trying to accomplish too much  :-\

 

Thanks for all your help F1Fan!!

[F1Fan]

Wow, that data is UGLY! I'm noticing that the 'Event' is basically useless. Try this one;

 

<?php
$drivertimes = array();
$driverstart = array();
foreach ($array as $k=>$arr){
  if (isset($driverstart[$arr['Driver']])){ // Check if the start exists
    if (!isset($drivertimes[$arr['Driver']])) $drivertimes[$arr['Driver']] = 0;
    $drivertimes[$arr['Driver']] += strtotime($arr['DateTime'])-$driverstart[$arr['Driver']];
    unset($driverstart[$arr['Driver']]);
  }
  else{
    $driverstart[$arr['Driver']] = strtotime($arr['DateTime']);
  }
}
?>

 

That should account for the weird 'Event' problem and will return the results in seconds. It will also basically ignore any driver with only one event, or ignore the last event of any driver with an odd number of events.

[knox203]

Haha, you're telling me! My head is running in circles trying to understand the best way to do this. Anyways, here's the output from your code, looks like were definitely getting closer, looks like there's still a couple of oddities:

Array
(
    [007] => 0
    [011] => -1234145363
    [077] => 107996
    [085] => 5353136
    [088] => -1249999817
    [090] => 5306472
    [1031] => -1257786507
    [108] => -1247141331
    [112] => 10321194
    [1164] => 7725662
    [1334] => 21009486
    [1375] => 15685186
    [1378] => 10558885
    [1379] => 18388873
    [1385] => 18536304
    [144] => 5281152
    [236] => -1252494674
    [313] => -1247382614
    [533] => 15843645
    [535] => -1252289407
    [618] => 13291228
    [684] => 5108410
    [781] => 5119167
    [8] => 0
    [843] => -1249773005
    [990024] => -1247152218
    [990027] => -1247026223
    [990047] => -1239257422
    [990059] => 15681636
    [990097] => 13136420
    [990126] => -1260274143
    [990141] => 15436738
    [990155] => 18190840
    [990170] => -1234202890
    [990176] => 5076019
    [990204] => 7851555
    [990209] => 5306374
    [990214] => 23716773
    [990226] => -1252458675
    [990232] => -1252455039
    [990233] => -1254910128
    [990238] => -1241881658
    [990240] => -1249787325
    [990242] => -1249996139
    [990245] => 2872747
    [990262] => -1239656943
    [990264] => -1241745027
    [990266] => -1252271306
    [990267] => -1255151284
    [990287] => -1242126584
    [990289] => 7685972
    [990292] => 0
    [990299] => -1244599854
    [990304] => 20905112
    [990311] => 93666
    [990312] => -1250046642
    [990317] => -1247069351
    [990318] => -1247335793
    [990320] => 20844011
    [990325] => -1252354228
    [990327] => -1252498142
    [990328] => -1257585014
    [990329] => -1247105193
    [990331] => -1260248925
    [990332] => 2613548
    [990333] => 39596
    [990334] => 0
    [994] => -1252361310
    [9999] => -1249960229
)

[F1Fan]

It looks like your incoming array is not sorted by the date/time. Try using array_multisort to sort by the driver then by the date/time. Then run the code.

 

You could do a abs() around each calculation, but that would probably just mask the solution, and not calculate it correctly.

 

If you've never used array_multisort, follow the link I left before and follow the example on that page.

[discomatt]

I'm thinking more of this sorting/calculation sohuld be done at a database-level... I'm not very familiar with MSSQL though, so I can't really help

[F1Fan]

Doesn't look like he has access to that, though.

[knox203]

I have not used array_multisort, so I hope I'm doing this correctly, here's the code:

<?php 
$job_select = "	SELECT	Driver.DriverCode AS Driver, 
		ManifestDriverEvent.ManifestDriverEventTypeID AS Event, 
		DATEPART(dd, ManifestDriverEvent.StartDateTime) AS Day, 
		DATEPART(mm, ManifestDriverEvent.StartDateTime) AS Month, 
		DATEPART(yyyy, ManifestDriverEvent.StartDateTime) AS Year, 
		DATEPART(hh, ManifestDriverEvent.StartDateTime) AS Hour, 
		DATEPART(mi, ManifestDriverEvent.StartDateTime) AS Minute, 
		DATEPART(ss, ManifestDriverEvent.StartDateTime) AS Second
	FROM	Driver INNER JOIN ManifestDriver ON Driver.DriverID = ManifestDriver.DriverID INNER JOIN 
		ManifestDriverEvent ON ManifestDriver.ManifestDriverID = ManifestDriverEvent.ManifestDriverID INNER JOIN 
		DispatchGroup ON Driver.DispatchGroupID = DispatchGroup.DispatchGroupID
	WHERE	(CONVERT(VARCHAR(25), ManifestDriverEvent.StartDateTime, 110) > CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1), GETDATE()), 110)) AND
		(DATEPART(yyyy, ManifestDriverEvent.StartDateTime) = DATEPART(yyyy, GETDATE())) AND
		(DispatchGroup.DispatchGroup = 'Corp Driv' OR
		DispatchGroup.DispatchGroup = 'Truck' OR
		DispatchGroup.DispatchGroup = 'Corridor' OR
		DispatchGroup.DispatchGroup = 'PRDriv')
	ORDER BY Driver.DriverCode, ManifestDriverEvent.StartDateTime";
$job_query = mssql_query($job_select) or die("Didn't Work!");
$dates_array = array();
while ( $job_result = mssql_fetch_assoc($job_query) ) {
    $dates_array[] = array(	    Driver => $job_result['Driver'],
    		    Event => $job_result['Event'],
	    	    Timecode => mktime($job_result['Hour'], $job_result['Minute'], $job_result['Second'], $job_result['Month'], $job_result['Day'], $job_result['Year']),
		    DateTime => date('m-d-y H:m:s', mktime($job_result['Hour'], $job_result['Minute'], $job_result['Second'], $job_result['Month'], $job_result['Day'], $job_result['Year']))	);
}
array_multisort(  $dates_array[]["Driver"], SORT_DESC, SORT_NUMERIC,
	$dates_array[]["Timecode"], SORT_DESC, SORT_NUMERIC); 
?>

 

Here's the result:

Array
(
    [007] => 0
    [011] => -1234145363
    [077] => 107996
    [085] => 5353136
    [088] => -1249999817
    [090] => 5306472
    [1031] => -1257786507
    [108] => -1247141331
    [112] => 10321194
    [1164] => 7725662
    [1334] => 21009486
    [1375] => 15685186
    [1378] => 10558885
    [1379] => 18388873
    [1385] => 18536304
    [144] => 5281152
    [236] => -1252494674
    [313] => -1247382614
    [533] => 15843645
    [535] => -1252289407
    [618] => 13291228
    [684] => 5108410
    [781] => 5119167
    [8] => 0
    [843] => -1249773005
    [990024] => -1247152218
    [990027] => -1247026223
    [990047] => -1239257422
    [990059] => 15681636
    [990097] => 13136420
    [990126] => -1260274143
    [990141] => 15436738
    [990155] => 18190840
    [990170] => -1234202890
    [990176] => 5076019
    [990204] => 7851555
    [990209] => 5306374
    [990214] => 23716773
    [990226] => -1252458675
    [990232] => -1252455039
    [990233] => -1254910128
    [990238] => -1241881658
    [990240] => -1249787325
    [990242] => -1249996139
    [990245] => 2872747
    [990262] => -1239656943
    [990264] => -1241745027
    [990266] => -1252271306
    [990267] => -1255151284
    [990287] => -1242126584
    [990289] => 7685972
    [990292] => 0
    [990299] => -1244599854
    [990304] => 20905112
    [990311] => 93666
    [990312] => -1250046642
    [990317] => -1247069351
    [990318] => -1247335793
    [990320] => 20844011
    [990325] => -1252354228
    [990327] => -1252498142
    [990328] => -1257585014
    [990329] => -1247105193
    [990331] => -1260248925
    [990332] => 2613548
    [990333] => 39596
    [990334] => 0
    [994] => -1252361310
    [9999] => -1249960229
    [] => 0
)

[F1Fan]

For some reason, I didn't notice your query before. It looks like you are already sorting it by those fields and in that order. But it clearly isn't working. Since that's the case, I say get rid of your array_multisort and figure out why it's not sorting properly in your select.

 

Also, I don't see where "DateTime" is being declared.

[knox203]

DateTime is added as a key in the array (along with it's associated value)... I'm creating it with mktime() then date(). Can I just compare the Timecodes (mysql timecode) that's being created with mktime() from the query?

 

When I print_r() the main array, it looks like SQL is sorting it correctly.

[F1Fan]

What happens when you print the SQL results array, manually calculate a few of the drivers' times, and compare them to that other array's results?

[knox203]

Hey F1Fan, thanks so much for your help! I made the code compare the Timecode instead of DateTime, and it seems to be working just fine! Here's what I ended up with:

 

<?php
$drivertimes = array();
$driverstart = array();
foreach ($dates_array as $k=>$arr){
  if (isset($driverstart[$arr['Driver']])){ // Check if the start exists
    if (!isset($drivertimes[$arr['Driver']])) $drivertimes[$arr['Driver']] = 0; 
    $drivertimes[$arr['Driver']] += $arr['Timecode']-$driverstart[$arr['Driver']];
    unset($driverstart[$arr['Driver']]);
  }
  else{
    $driverstart[$arr['Driver']] = $arr['Timecode'];
  }
} 
?>

 

Not much different than what you originally gave me... either way, it seems to be working now. Thanks again so much for all your help, I couldn't have done it without ya!!

 

- Adam

[F1Fan]

I'm glad I can help!