Trying to loop an array and group by user for totals, phpspreadsheet

[thenorman138]

I'm trying to create a spreadsheet within a php script that selects data from a mysql query.

 

The query works and my below script works in the sense that it inserts my entire result set into my spreadsheet like so:

 

https://i.stack.imgur.com/Euynu.png

 

 

 

The only thing I can't figure out is how to modify the array loop so that I can sum totals of the 4 columns (C,D,E and F) by user. Essentially, it needs to realize that it's at the last record for user ID 48 so I can insert a row of totals, and move to the next user. 

 

I feel it should be fairly straightforward but I just can't seem to make sense of it right now. The only other factor is that when this query runs, every user could have up to 5 records so I can use the USER ID to associate a user but the records would hinge on the ID as well as the Date, if that makes sense.

 

How can I get this to group by user and total their numbers before writing the info for the next user?

 

 

            $sql = "

select 

concat(u.first_name, '  ', u.last_name) as Name,

t.ext_id as ID,

t.total_talk_time_minutes as TalkTime,

t.total_outbound as Outbound,

t.total_inbound as Inbound,

t.dealers_contacted as Dealers,

t.date_of_report as Date

From

ambition.ambition_totals t

INNER JOIN

ambition.ambition_users u

ON 

t.extension = u.extension

where

date_of_report 

between

curdate() - interval 5 day and curdate()"  or die(mysqli_error( $conn));





$result=mysqli_query($conn,$sql);





$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();



                $sheet->setCellValue('A1', 'Name');

$sheet->setCellValue('B1', 'User ID');

$sheet->setCellValue('C1', 'Talk Time Per Call');

$sheet->setCellValue('D1', 'Outbound Calls');

$sheet->setCellValue('E1', 'Inbound Calls');

$sheet->setCellValue('F1', 'Dealer Contacts');

$sheet->setCellValue('G1', 'Date');



                $rowNumber = 2;



  while($row = mysqli_fetch_assoc($result)) 

  {

$col = 'A';



foreach($row as $cell){

$sheet -> setCellValue($col.$rowNumber, $cell);

$col++;

}



$rowNumber++;

}

Edited February 6, 2018 by Zane
remember to use [code] tags

[requinix]

Essentially, it needs to realize that it's at the last record for user ID 48 so I can insert a row of totals, and move to the next user.

That's not really a great approach for a spreadsheet. How about putting the totals into their own sheet?

[Psycho]

Including subtotals in such a report is a pretty standard report format.

 

Before I provide a solution, a couple notes:

 

1. When defining the string for the query, why do you have an "or die()". Are you afraid PHP won't be able to define the variable? I assume you meant that to go after the line that actually executes the query. But, that is not a good implementation. Lear to check for errors and handle them gracefully.

 

2. Use Comments! They make your job (and those that help you) easier.

 

3. I'm not liking how you are setting the column to 'A' and then incrementing it using '++'. I'm actually surprised it works. But, I think it makes more sense to be explicit and it will make the job of creating the subtotals a little easier.

 

4. You should order the records by ID

 

This isn't tested, so it may have a typo or two

//Create and run query
$sql = "
    SELECT CONCAT(u.first_name, '  ', u.last_name) as Name,
           t.ext_id as ID,
           t.total_talk_time_minutes as TalkTime,
           t.total_outbound as Outbound,
           t.total_inbound as Inbound,
           t.dealers_contacted as Dealers,
           t.date_of_report as Date
    FROM ambition.ambition_totals t
    INNER JOIN ambition.ambition_users u
      ON t.extension = u.extension
    WHERE date_of_report  between
        curdate() - interval 5 day and curdate()
    ORDER BY ID";
$result = mysqli_query($conn,$sql);
 
//Start the spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//Create header row
$sheet->setCellValue('A1', 'Name');
$sheet->setCellValue('B1', 'User ID');
$sheet->setCellValue('C1', 'Talk Time Per Call');
$sheet->setCellValue('D1', 'Outbound Calls');
$sheet->setCellValue('E1', 'Inbound Calls');
$sheet->setCellValue('F1', 'Dealer Contacts');
$sheet->setCellValue('G1', 'Date');
 
//Preprocess the data into a multi-dimensional array
//  with the id as the parent index
$resultData = array();
while($row = mysqli_fetch_assoc($result)) 
{
    $resultData[$row['ID']][] = $row;
}
 
//Set starting row number
$rowNo = 1;
//Iterate over the results for each unique id
foreach($resultData as $idRecords)
{
    //Set subtotal variables to 0
    $subtotalTalktime = 0;
    $subtotalOutbound = 0;
    $subtotalInbound = 0;
    $subtotalDealers = 0;
    //Iterate over the records for this ID
    foreach($idRecords as $record)
    {
        //Increment row number
        $rowNo++;
        //Add record row to spreadsheet
        $sheet->setCellValue("A{$rowNo}", $row['Name']);
        $sheet->setCellValue("B{$rowNo}", $row['ID']);
        $sheet->setCellValue("C{$rowNo}", $row['TalkTime']);
        $sheet->setCellValue("D{$rowNo}", $row['Outbound']);
        $sheet->setCellValue("E{$rowNo}", $row['Inbound']);
        $sheet->setCellValue("F{$rowNo}", $row['Dealers']);
        $sheet->setCellValue("G{$rowNo}", $row['Date']);
        //Update the subtotals
        $subtotalTalktime += $row['TalkTime'];
        $subtotalOutbound += $row['Outbound'];
        $subtotalInbound  += $row['Inbound'];
        $subtotalDealers  += $row['Date'];
    }
    //Increment row number
    $rowNo++;
    //Add subtotal row to spreadsheet
    $sheet->setCellValue("C{$rowNo}", $subtotalTalktime);
    $sheet->setCellValue("D{$rowNo}", $subtotalOutbound);
    $sheet->setCellValue("E{$rowNo}", $subtotalInbound);
    $sheet->setCellValue("F{$rowNo}", $subtotalDealers);
}

Edited February 7, 2018 by Psycho

[requinix]

Including subtotals in such a report is a pretty standard report format.

Subtotals mixed into the data? Not that I've seen. It pretty much prevents the viewer from manipulating the data in any way. Can't sort, can't group, can't apply formulas...

 

If we're treating this as a true report then that's one thing, having all the data calculated and displayed into the final result, but creating a table of data in a spreadsheet doesn't seem like a report to me.

[thenorman138]

 

Including subtotals in such a report is a pretty standard report format.

 

Before I provide a solution, a couple notes:

 

1. When defining the string for the query, why do you have an "or die()". Are you afraid PHP won't be able to define the variable? I assume you meant that to go after the line that actually executes the query. But, that is not a good implementation. Lear to check for errors and handle them gracefully.

 

2. Use Comments! They make your job (and those that help you) easier.

 

3. I'm not liking how you are setting the column to 'A' and then incrementing it using '++'. I'm actually surprised it works. But, I think it makes more sense to be explicit and it will make the job of creating the subtotals a little easier.

 

4. You should order the records by ID

 

This isn't tested, so it may have a typo or two

//Create and run query
$sql = "
    SELECT CONCAT(u.first_name, '  ', u.last_name) as Name,
           t.ext_id as ID,
           t.total_talk_time_minutes as TalkTime,
           t.total_outbound as Outbound,
           t.total_inbound as Inbound,
           t.dealers_contacted as Dealers,
           t.date_of_report as Date
    FROM ambition.ambition_totals t
    INNER JOIN ambition.ambition_users u
      ON t.extension = u.extension
    WHERE date_of_report  between
        curdate() - interval 5 day and curdate()
    ORDER BY ID";
$result = mysqli_query($conn,$sql);
 
//Start the spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//Create header row
$sheet->setCellValue('A1', 'Name');
$sheet->setCellValue('B1', 'User ID');
$sheet->setCellValue('C1', 'Talk Time Per Call');
$sheet->setCellValue('D1', 'Outbound Calls');
$sheet->setCellValue('E1', 'Inbound Calls');
$sheet->setCellValue('F1', 'Dealer Contacts');
$sheet->setCellValue('G1', 'Date');
 
//Preprocess the data into a multi-dimensional array
//  with the id as the parent index
$resultData = array();
while($row = mysqli_fetch_assoc($result)) 
{
    $resultData[$row['ID']][] = $row;
}
 
//Set starting row number
$rowNo = 1;
//Iterate over the results for each unique id
foreach($resultData as $idRecords)
{
    //Set subtotal variables to 0
    $subtotalTalktime = 0;
    $subtotalOutbound = 0;
    $subtotalInbound = 0;
    $subtotalDealers = 0;
    //Iterate over the records for this ID
    foreach($idRecords as $record)
    {
        //Increment row number
        $rowNo++;
        //Add record row to spreadsheet
        $sheet->setCellValue("A{$rowNo}", $row['Name']);
        $sheet->setCellValue("B{$rowNo}", $row['ID']);
        $sheet->setCellValue("C{$rowNo}", $row['TalkTime']);
        $sheet->setCellValue("D{$rowNo}", $row['Outbound']);
        $sheet->setCellValue("E{$rowNo}", $row['Inbound']);
        $sheet->setCellValue("F{$rowNo}", $row['Dealers']);
        $sheet->setCellValue("G{$rowNo}", $row['Date']);
        //Update the subtotals
        $subtotalTalktime += $row['TalkTime'];
        $subtotalOutbound += $row['Outbound'];
        $subtotalInbound  += $row['Inbound'];
        $subtotalDealers  += $row['Date'];
    }
    //Increment row number
    $rowNo++;
    //Add subtotal row to spreadsheet
    $sheet->setCellValue("C{$rowNo}", $subtotalTalktime);
    $sheet->setCellValue("D{$rowNo}", $subtotalOutbound);
    $sheet->setCellValue("E{$rowNo}", $subtotalInbound);
    $sheet->setCellValue("F{$rowNo}", $subtotalDealers);
}

Thank you for your answer! It seems like this makes sense, however it is currently printing blank rows with 0 for each subtotal, though it is the right number of rows. In other words, 4 blank rows with zeroes for subtotals, and then 5 rows with zeroes. So it seems like the count is correct but I'm just moving through it to see why it's not printing the numbers into the cells.

[thenorman138]

Also, doing a var_dump of $resultData does print what it should. I'm just trying to figure out why it's not printing to the cells now

[thenorman138]

Ah I fixed it, it was using $row[] to set cell values instead of $record[]. Thanks again!

[thenorman138]

So @psycho, I have a question now that I have this report looking right. It's been suggested that we may add more formulas over time with the subtotals such as average and mode. I can use those formulas in cells but in order to add more formulas per user, would I just follow the same structure as the current subtotals?

[Psycho]

Subtotals mixed into the data? Not that I've seen. It pretty much prevents the viewer from manipulating the data in any way. Can't sort, can't group, can't apply formulas...

 

If we're treating this as a true report then that's one thing, having all the data calculated and displayed into the final result, but creating a table of data in a spreadsheet doesn't seem like a report to me.

 

We routinely create processes to export data into "formatted" excel reports. We could export the raw data into Excel and then use the tools in Excel to format the data into the necessary report, but that would take manual effort.

 

 

So @psycho, I have a question now that I have this report looking right. It's been suggested that we may add more formulas over time with the subtotals such as average and mode. I can use those formulas in cells but in order to add more formulas per user, would I just follow the same structure as the current subtotals?

 

Without knowing exactly what you are doing, I can't give a solid answer. If you need to determine averages/modes/etc. then you would need the count of records for each group. FYI: There is the ability to include subtotals with MySQL queries, I think it is easier to do that in the output. However, if you start doing more complicated summations, you might want to leverage SQL to generate the data. It might require multiple queries, but it creates a separation between extracting the data and displaying the data. There isn't one way to do it.

[thenorman138]

Great I may try to do that now, Thanks! I'm just playing around with getting averages in there