Jump to content

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


Go to solution Solved by Psycho,

Recommended Posts

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:

 


 

 

 

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 by Zane
remember to use [code] tags
Link to post
Share on other sites

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?
Link to post
Share on other sites
  • Solution

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 by Psycho
Link to post
Share on other sites

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.

Link to post
Share on other sites

 

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.