MOdifying existing loop for spreadsheet record

[thenorman138]

Another member here, @psycho, was kind enough to help get this script on track for me but I'm trying to modify it for other values.

 

Right now, it groups by user and adds a totals row for them under their records but I'm wanting to add 3 rows for Mean, Mode and Median. I'm not exactly sure how to follow the same format and add 3 rows for each user but I feel like the structure would be the same. My idea is that I would set variables to the formulas and then add them but how would the rows cascade?

    // Check connectionif ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";








//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,
           DAYNAME(t.date_of_report) as Day
    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();
$sheet ->getColumnDimension('A') -> setAutoSize(true);
$sheet ->getColumnDimension('B') -> setAutoSize(true);
$sheet ->getColumnDimension('C') -> setAutoSize(true);
$sheet ->getColumnDimension('D') -> setAutoSize(true);
$sheet ->getColumnDimension('E') -> setAutoSize(true);
$sheet ->getColumnDimension('F') -> setAutoSize(true);
$sheet ->getColumnDimension('G') -> setAutoSize(true);


//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');
$sheet->setCellValue('H1', 'Day');


//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}", $record['Name']);
        $sheet->setCellValue("B{$rowNo}", $record['ID']);
        $sheet->setCellValue("C{$rowNo}", $record['TalkTime']);
        $sheet->setCellValue("D{$rowNo}", $record['Outbound']);
        $sheet->setCellValue("E{$rowNo}", $record['Inbound']);
        $sheet->setCellValue("F{$rowNo}", $record['Dealers']);
        $sheet->setCellValue("G{$rowNo}", $record['Date']);
        $sheet->setCellValue("H{$rowNo}", $record['Day']);
        //Update the subtotals
        $subtotalTalktime += $record['TalkTime'];
        $subtotalOutbound += $record['Outbound'];
        $subtotalInbound  += $record['Inbound'];
        $subtotalDealers  += $record['Dealers'];
    }


    //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);
}


var_dump($resultData);


$worksheet1 = $spreadsheet->createSheet();
$worksheet1->setTitle('Department Total');


//Add in the query for department totals


//Add results to spreadsheet




mysqli_close($conn);


$writer = new xlsx($spreadsheet);
$writer->save('Coaching Report - Test.xlsx');


?>

    

Edited February 7, 2018 by thenorman138

[thenorman138]

I updated this slightly to get the header fields for mean median and mode:

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}", $record['Name']);
		        $sheet->setCellValue("B{$rowNo}", $record['ID']);
		        $sheet->setCellValue("C{$rowNo}", $record['TalkTime']);
		        $sheet->setCellValue("F{$rowNo}", $record['Outbound']);
		        $sheet->setCellValue("I{$rowNo}", $record['Inbound']);
		        $sheet->setCellValue("L{$rowNo}", $record['Dealers']);
		        $sheet->setCellValue("O{$rowNo}", $record['Date']);
		        $sheet->setCellValue("P{$rowNo}", $record['Day']);

		    }

	    //Increment row number
	    $rowNo++;

	    //Add subtotal row to spreadsheet
	    $sheet->setCellValue("C{$rowNo}", "Mean");
	    $sheet->setCellValue("D{$rowNo}", "Median");
	    $sheet->setCellValue("E{$rowNo}", "Mode");
	    $sheet->setCellValue("F{$rowNo}", "Mean");
	    $sheet->setCellValue("G{$rowNo}", "Median");
	    $sheet->setCellValue("H{$rowNo}", "Mode");
	    $sheet->setCellValue("I{$rowNo}", "Mean");
	    $sheet->setCellValue("J{$rowNo}", "Median");
	    $sheet->setCellValue("K{$rowNo}", "Mode");
	    $sheet->setCellValue("L{$rowNo}", "Mean");
	    $sheet->setCellValue("M{$rowNo}", "Median");
	    $sheet->setCellValue("N{$rowNo}", "Mode");

	}

 But I"m still not quite sure how to formulate and add in the cellvalues per user.

[thenorman138]

I've actually gotten this to work:

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)
	{
		 $userFirstRow = $rowNo+1;

	    //Iterate over the records for this ID
	    foreach($idRecords as $record)
		    {
		        //Increment row number
		        $rowNo++;
		        //Add record row to spreadsheet
		        $sheet->setCellValue("A{$rowNo}", $record['Name']);
		        $sheet->setCellValue("B{$rowNo}", $record['ID']);
		        $sheet->setCellValue("C{$rowNo}", $record['TalkTime']);
		        $sheet->setCellValue("F{$rowNo}", $record['Outbound']);
		        $sheet->setCellValue("I{$rowNo}", $record['Inbound']);
		        $sheet->setCellValue("L{$rowNo}", $record['Dealers']);
		        $sheet->setCellValue("O{$rowNo}", $record['Date']);
		        $sheet->setCellValue("P{$rowNo}", $record['Day']);

		    }

	    //Increment row number
	    $rowNo++;

	    $range = 'C'.$userFirstRow.':C'.($rowNo-1);
		$sheet->setCellValue("C{$rowNo}", "Mean");
		$sheet->setCellValue("C{$rowNo}".+1., '=AVERAGE('.$range.')');


	}

as long as I can find a way to make the '+1' work properly, but it is averaging as expected

[Psycho]

You know, it is best if you were to identify your requirements up front! If I had known you were needing these other calculated values, I would have taken a different approach. In this case I would add all the values for each "ID" entity to an array. Then use that array of values to do the calculations of Subtotal, Average, etc. That way, if there is no existing function to get the value I can just create a function and pass the array.

 

None of this is tested

 

function array_mean($array) {
    return array_sum($array) / count($array);
}
 
function array_median($array)
{
    //Sort the array
    sort($array);
    //Get the count
    $count = count($array);
    //Check if odd/even number
    if($count%2)
    {
        //Odd number - get middle value
        $medianIndex = ($count-1) / 2;
        $median = $array[$medianIndex];
    } else {
        //Even number number - get avg of middle two values
        $startIndex = ($count/2) - 1; //Get index of first of two middle items
        //Calculate the average of the middle pair
        $median = ($array[$startIndex]+$array[$startIndex+1]) / 2;
    }
    //Return median
    return $median;
}
 
function array_mode($array)
{
    //Get the count of all values in the array
    $valuesAry = array_count_values($array);
    //Sort by occurance rate
    sort($valuesAry);
    //Return the first key (value with highest frequency)
    return key($valuesAry);
}
 
foreach($resultData as $idRecords)
{
    //Set arrays to hold values to calculate
    $talktimeValues = array();
    $outboundValues = array();
    $inboundValues  = array();
    $dealersValues  = array();
    
    //Iterate over the records for this ID
    foreach($idRecords as $record)
    {
        //Increment row number
        $rowNo++;
        //Add record row to spreadsheet
        $sheet->setCellValue("A{$rowNo}", $record['Name']);
        $sheet->setCellValue("B{$rowNo}", $record['ID']);
        $sheet->setCellValue("C{$rowNo}", $record['TalkTime']);
        $sheet->setCellValue("F{$rowNo}", $record['Outbound']);
        $sheet->setCellValue("I{$rowNo}", $record['Inbound']);
        $sheet->setCellValue("L{$rowNo}", $record['Dealers']);
        $sheet->setCellValue("O{$rowNo}", $record['Date']);
        $sheet->setCellValue("P{$rowNo}", $record['Day']);
    }
 
    //Add subtotals
    $rowNo++;
    $sheet->setCellValue("B{$rowNo}", "Subtotals");
    $sheet->setCellValue("C{$rowNo}", array_sum($talktimeValues));
    $sheet->setCellValue("F{$rowNo}", array_sum($outboundValues));
    $sheet->setCellValue("I{$rowNo}", array_sum($inboundValues));
    $sheet->setCellValue("L{$rowNo}", array_sum($dealersValues));
    //Add mean
    $rowNo++;
    $sheet->setCellValue("B{$rowNo}", "Means");
    $sheet->setCellValue("C{$rowNo}", array_mean($talktimeValues));
    $sheet->setCellValue("F{$rowNo}", array_mean($outboundValues));
    $sheet->setCellValue("I{$rowNo}", array_mean($inboundValues));
    $sheet->setCellValue("L{$rowNo}", array_mean($dealersValues));
    //Add Median
    $rowNo++;
    $sheet->setCellValue("B{$rowNo}", "Medians");
    $sheet->setCellValue("C{$rowNo}", array_median($talktimeValues));
    $sheet->setCellValue("F{$rowNo}", array_median($outboundValues));
    $sheet->setCellValue("I{$rowNo}", array_median($inboundValues));
    $sheet->setCellValue("L{$rowNo}", array_median($dealersValues));
    //Add Mode
    $rowNo++;
    $sheet->setCellValue("B{$rowNo}", "Modes");
    $sheet->setCellValue("C{$rowNo}", array_mode($talktimeValues));
    $sheet->setCellValue("F{$rowNo}", array_mode($outboundValues));
    $sheet->setCellValue("I{$rowNo}", array_mode($inboundValues));
    $sheet->setCellValue("L{$rowNo}", array_mode($dealersValues));
 
}

[thenorman138]

Apologies, I had originally only needed subtotals. After going through some future ideas with teammates though, I wanted to go ahead and see what the possibilities would be. This is a great solution though, thank you so much! Sorry you went back to do extra work on this. The only issues seem to be undefined offset but I think I found the issue now. Many thanks!