<?php
namespace App\Controller;
/**
* Created by PhpStorm.
* User: C
* Date: 12/24/2018
* Time: 1:18 PM
*/
use App\Entity\Answers;
use App\Entity\Ebook;
use App\Entity\Group;
use App\Entity\Pages;
use App\Entity\Question;
use App\Entity\Student;
use App\Entity\Survey;
use App\Entity\SurveySent;
use App\Entity\Trainer;
use App\Entity\Users;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpParser\Node\Stmt\Else_;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
class SurveyController extends DefaultController
{
public function userSurvey($id)
{
$entityManager = $this->getDoctrine()->getManager();
$user = $this->getUser();
if ($user != null) {
$survey = $entityManager->getRepository(SurveySent::class)
->findOneBy(
array(
'userid' => $user->getId(),
'status' => 0,
'surveyid' => $id
)
);
if ($survey != null) {
$SurveyObject = $entityManager->getRepository(Survey::class)
->find($survey->getSurveyid());
$currentdate = new \DateTime();
$question = $entityManager->getRepository(Question::class)
->findBy(array(
'type' => $SurveyObject->getType()
));
if ($SurveyObject->getEndDate() > $currentdate and $SurveyObject->getStartDate() < $currentdate) {
return $this->render(
'user/survey.html.twig',
array('questions' => $question, 'survey' => $survey, 'SurveyObject' => $SurveyObject)
);
} else {
return $this->redirectToRoute('home');
}
}
}
return $this->redirectToRoute('home');
}
public function saveSurvey(Request $request)
{
$entityManager = $this->getDoctrine()->getManager();
$survey = $entityManager->getRepository(SurveySent::class)
->findOneBy(
array(
'userid' => $request->request->get('userId'),
'surveyid' => $request->request->get('surveyId'),
)
);
$survey->setStatus((int)$request->request->get('status'));
$survey->SetQ1a1((int)$request->request->get('q1a1'));
$survey->SetQ1a2((int)$request->request->get('q1a2'));
$survey->SetQ1a3((int)$request->request->get('q1a3'));
$survey->SetQ1a4((int)$request->request->get('q1a4'));
$survey->SetQ1a5((int)$request->request->get('q1a5'));
$survey->SetQ1a6((int)$request->request->get('q1a6'));
$survey->SetQ1a7((int)$request->request->get('q1a7'));
$survey->setCat1($request->request->get('cat1'));
$survey->setexp1($request->request->get('exp1'));
$survey->SetQ2a1((int)$request->request->get('q2a1'));
$survey->SetQ2a2((int)$request->request->get('q2a2'));
$survey->SetQ2a3((int)$request->request->get('q2a3'));
$survey->SetQ2a4((int)$request->request->get('q2a4'));
$survey->SetQ2a5((int)$request->request->get('q2a5'));
$survey->SetQ2a6((int)$request->request->get('q2a6'));
$survey->SetQ2a7((int)$request->request->get('q2a7'));
$survey->setCat2($request->request->get('cat2'));
$survey->setexp2($request->request->get('exp2'));
$survey->SetQ3a1((int)$request->request->get('q3a1'));
$survey->SetQ3a2((int)$request->request->get('q3a2'));
$survey->SetQ3a3((int)$request->request->get('q3a3'));
$survey->SetQ3a4((int)$request->request->get('q3a4'));
$survey->SetQ3a5((int)$request->request->get('q3a5'));
$survey->SetQ3a6((int)$request->request->get('q3a6'));
$survey->SetQ3a7((int)$request->request->get('q3a7'));
$survey->setCat3($request->request->get('cat3'));
$survey->setexp3($request->request->get('exp3'));
if ($request->request->has('q4a1')) {
$survey->SetQ4a1((int)$request->request->get('q4a1'));
$survey->SetQ4a2((int)$request->request->get('q4a2'));
$survey->SetQ4a3((int)$request->request->get('q4a3'));
$survey->SetQ4a4((int)$request->request->get('q4a4'));
$survey->SetQ4a5((int)$request->request->get('q4a5'));
$survey->SetQ4a6((int)$request->request->get('q4a6'));
$survey->SetQ4a7((int)$request->request->get('q4a7'));
$survey->setCat4($request->request->get('cat4'));
$survey->setexp4($request->request->get('exp4'));
$survey->SetQ5a1((int)$request->request->get('q5a1'));
$survey->SetQ5a2((int)$request->request->get('q5a2'));
$survey->SetQ5a3((int)$request->request->get('q5a3'));
$survey->SetQ5a4((int)$request->request->get('q5a4'));
$survey->SetQ5a5((int)$request->request->get('q5a5'));
$survey->SetQ5a6((int)$request->request->get('q5a6'));
$survey->SetQ5a7((int)$request->request->get('q5a7'));
$survey->setCat5($request->request->get('cat5'));
$survey->setexp5($request->request->get('exp5'));
$survey->SetQ6a1((int)$request->request->get('q6a1'));
$survey->SetQ6a2((int)$request->request->get('q6a2'));
$survey->SetQ6a3((int)$request->request->get('q6a3'));
$survey->SetQ6a4((int)$request->request->get('q6a4'));
$survey->SetQ6a5((int)$request->request->get('q6a5'));
$survey->SetQ6a6((int)$request->request->get('q6a6'));
$survey->SetQ6a7((int)$request->request->get('q6a7'));
$survey->setCat6($request->request->get('cat6'));
$survey->setexp6($request->request->get('exp6'));
$survey->SetQ7a1((int)$request->request->get('q7a1'));
$survey->SetQ7a2((int)$request->request->get('q7a2'));
$survey->SetQ7a3((int)$request->request->get('q7a3'));
$survey->SetQ7a4((int)$request->request->get('q7a4'));
$survey->SetQ7a5((int)$request->request->get('q7a5'));
$survey->SetQ7a6((int)$request->request->get('q7a6'));
$survey->SetQ7a7((int)$request->request->get('q7a7'));
$survey->setCat7($request->request->get('cat7'));
$survey->setexp7($request->request->get('exp7'));
$survey->SetQ8a1((int)$request->request->get('q8a1'));
$survey->SetQ8a2((int)$request->request->get('q8a2'));
$survey->SetQ8a3((int)$request->request->get('q8a3'));
$survey->SetQ8a4((int)$request->request->get('q8a4'));
$survey->SetQ8a5((int)$request->request->get('q8a5'));
$survey->SetQ8a6((int)$request->request->get('q8a6'));
$survey->SetQ8a7((int)$request->request->get('q8a7'));
$survey->setCat8($request->request->get('cat8'));
$survey->setExp8($request->request->get('exp8'));
}
$survey->setComment($request->request->get('cat9'));
$entityManager->persist($survey);
$entityManager->flush();
return new Response('success', 200);
}
public function generateReport($id)
{
$entityManager = $this->getDoctrine()->getManager();
$survey = $entityManager->getRepository(Survey::class)
->find($id);
$questions = $entityManager->getRepository(Question::class)
->findBy(array('type' => $survey->getType()));
$j = count($questions);
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('reordered');
$sheet->mergeCells("B1:H1");
$sheet->mergeCells("L1:Q1");
$sheet->mergeCells("V1:AB1");
if ($j > 3) {
$sheet->mergeCells("AF1:AL1");
$sheet->mergeCells("AP1:AV1");
$sheet->mergeCells("AZ1:BF1");
$sheet->mergeCells("BJ1:BP1");
$sheet->mergeCells("BT1:BZ1");
}
if ($j > 3) {
$sheet->setCellValue('B1', 'Decision Making ');
$sheet->setCellValue('L1', 'Conflict Management ');
$sheet->setCellValue('V1', 'Strategy Setting ');
$sheet->setCellValue('AF1', 'Authority');
$sheet->setCellValue('AP1', 'Advocacy ');
$sheet->setCellValue('AZ1', 'Coordination');
$sheet->setCellValue('BJ1', 'Critique & Feedback');
$sheet->setCellValue('BT1', 'Accountability ');
} else {
$sheet->setCellValue('B1', 'Decision Making ');
$sheet->setCellValue('L1', 'Conflict Management ');
$sheet->setCellValue('V1', 'Critique & Feedback ');
}
$sheet->setCellValue('A2', 'ID');
$sheet->setCellValue('B2', 'Opp');
$sheet->setCellValue('C2', '1,1');
$sheet->setCellValue('D2', '1,9');
$sheet->setCellValue('E2', '5,5');
$sheet->setCellValue('F2', '9,1');
$sheet->setCellValue('G2', 'PAT');
$sheet->setCellValue('H2', '9,9');
$sheet->setCellValue('I2', 'High');
$sheet->setCellValue('J2', 'Low');
$sheet->setCellValue('K2', 'Total Percent Q1');
$sheet->setCellValue('L2', 'Opp');
$sheet->setCellValue('M2', '1,1');
$sheet->setCellValue('N2', '1,9');
$sheet->setCellValue('O2', '5,5');
$sheet->setCellValue('P2', '9,1');
$sheet->setCellValue('Q2', 'PAT');
$sheet->setCellValue('R2', '9,9');
$sheet->setCellValue('S2', 'High');
$sheet->setCellValue('T2', 'Low');
$sheet->setCellValue('U2', 'Total Percent Q2');
$sheet->setCellValue('V2', 'Opp');
$sheet->setCellValue('W2', '1,1');
$sheet->setCellValue('X2', '1,9');
$sheet->setCellValue('Y2', '5,5');
$sheet->setCellValue('Z2', '9,1');
$sheet->setCellValue('AA2', 'PAT');
$sheet->setCellValue('AB2', '9,9');
$sheet->setCellValue('AC2', 'High');
$sheet->setCellValue('AD2', 'Low');
$sheet->setCellValue('AE2', 'Total Percent Q3');
if ($j > 3) {
$sheet->setCellValue('AF2', 'Opp');
$sheet->setCellValue('AG2', '1,1');
$sheet->setCellValue('AH2', '1,9');
$sheet->setCellValue('AI2', '5,5');
$sheet->setCellValue('AJ2', '9,1');
$sheet->setCellValue('AK2', 'PAT');
$sheet->setCellValue('AL2', '9,9');
$sheet->setCellValue('AM2', 'High');
$sheet->setCellValue('AN2', 'Low');
$sheet->setCellValue('AO2', 'Total Percent Q4');
$sheet->setCellValue('AP2', 'Opp');
$sheet->setCellValue('AQ2', '1,1');
$sheet->setCellValue('AR2', '1,9');
$sheet->setCellValue('AS2', '5,5');
$sheet->setCellValue('AT2', '9,1');
$sheet->setCellValue('AU2', 'PAT');
$sheet->setCellValue('AV2', '9,9');
$sheet->setCellValue('AW2', 'High');
$sheet->setCellValue('AX2', 'Low');
$sheet->setCellValue('AY2', 'Total Percent Q5');
$sheet->setCellValue('AZ2', 'Opp');
$sheet->setCellValue('BA2', '1,1');
$sheet->setCellValue('BB2', '1,9');
$sheet->setCellValue('BC2', '5,5');
$sheet->setCellValue('BD2', '9,1');
$sheet->setCellValue('BE2', 'PAT');
$sheet->setCellValue('BF2', '9,9');
$sheet->setCellValue('BG2', 'High');
$sheet->setCellValue('BH2', 'Low');
$sheet->setCellValue('BI2', 'Total Percent Q6');
$sheet->setCellValue('BJ2', 'Opp');
$sheet->setCellValue('BK2', '1,1');
$sheet->setCellValue('BL2', '1,9');
$sheet->setCellValue('BM2', '5,5');
$sheet->setCellValue('BN2', '9,1');
$sheet->setCellValue('BO2', 'PAT');
$sheet->setCellValue('BP2', '9,9');
$sheet->setCellValue('BQ2', 'High');
$sheet->setCellValue('BR2', 'Low');
$sheet->setCellValue('BS2', 'Total Percent Q7');
$sheet->setCellValue('BT2', 'Opp');
$sheet->setCellValue('BU2', '1,1');
$sheet->setCellValue('BV2', '1,9');
$sheet->setCellValue('BW2', '5,5');
$sheet->setCellValue('BX2', '9,1');
$sheet->setCellValue('BY2', 'PAT');
$sheet->setCellValue('BZ2', '9,9');
$sheet->setCellValue('CA2', 'High');
$sheet->setCellValue('CB2', 'Low');
$sheet->setCellValue('CC2', 'Total Percent Q8');
}
$reorderingArray[] = array();
$j = 0;
foreach ($questions as $question) {
$i = 0;
$j++;
foreach ($question->getAnswers() as $answer) {
$i++;
if ($j == 1) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'B';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'C';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'D';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'E';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'F';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'G';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'H';
}
} else {
if ($j == 2) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'L';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'M';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'N';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'O';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'P';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'Q';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'R';
}
} else {
if ($j == 3) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'V';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'W';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'X';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'Y';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'Z';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'AA';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AB';
}
} else {
if ($j == 4) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'AF';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'AG';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AH';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'AI';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'AJ';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'AK';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AL';
}
} else {
if ($j == 5) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'AP';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'AQ';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AR';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'AS';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'AT';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'AU';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AV';
}
} else {
if ($j == 6) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'AZ';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BA';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BB';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'BC';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BD';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'BE';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BF';
}
} else {
if ($j == 7) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'BJ';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BK';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BL';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'BM';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BN';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'BO';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BP';
}
} else {
if ($j == 8) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'BT';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BU';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BV';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'BW';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BX';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'BY';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BZ';
}
}
}
}
}
}
}
}
}
}
}
$conn = $entityManager->getConnection();
$sql = 'SELECT * FROM `student_survey`,users,team,student WHERE users.id=student.id and student.team_id=team.id and student_id= users.id and survey_id= :survey_id';
$stmt = $conn->prepare($sql);
$stmt->execute(['survey_id' => $id]);
$data = $stmt->fetchAll();
$rowNB = 3;
$avarageROW = array();
foreach ($data as $student) {
$q1Answers = array(
$student['q1a1'],
$student['q1a2'],
$student['q1a3'],
$student['q1a4'],
$student['q1a5'],
$student['q1a6'],
$student['q1a7'],
);
if (array_sum($q1Answers) == 100) {
$avarageROW['q1a1'][] = $student['q1a1'];
$avarageROW['q1a2'][] = $student['q1a2'];
$avarageROW['q1a3'][] = $student['q1a3'];
$avarageROW['q1a4'][] = $student['q1a4'];
$avarageROW['q1a5'][] = $student['q1a5'];
$avarageROW['q1a6'][] = $student['q1a6'];
$avarageROW['q1a7'][] = $student['q1a7'];
$avarageROW['q1L'][] = min($q1Answers);
$avarageROW['q1H'][] = max($q1Answers);
$sheet->setCellValue('I' . $rowNB, max($q1Answers));
$sheet->setCellValue('J' . $rowNB, min($q1Answers));
$sheet->setCellValue('K' . $rowNB, array_sum($q1Answers));
$sheet->setCellValue($reorderingArray['q1a1'] . $rowNB, $student['q1a1']);
$sheet->setCellValue($reorderingArray['q1a2'] . $rowNB, $student['q1a2']);
$sheet->setCellValue($reorderingArray['q1a3'] . $rowNB, $student['q1a3']);
$sheet->setCellValue($reorderingArray['q1a4'] . $rowNB, $student['q1a4']);
$sheet->setCellValue($reorderingArray['q1a5'] . $rowNB, $student['q1a5']);
$sheet->setCellValue($reorderingArray['q1a6'] . $rowNB, $student['q1a6']);
$sheet->setCellValue($reorderingArray['q1a7'] . $rowNB, $student['q1a7']);
}
$q2Answers = array(
$student['q2a1'],
$student['q2a2'],
$student['q2a3'],
$student['q2a4'],
$student['q2a5'],
$student['q2a6'],
$student['q2a7'],
);
if (array_sum($q2Answers) == 100) {
$avarageROW['q2a1'][] = $student['q2a1'];
$avarageROW['q2a2'][] = $student['q2a2'];
$avarageROW['q2a3'][] = $student['q2a3'];
$avarageROW['q2a4'][] = $student['q2a4'];
$avarageROW['q2a5'][] = $student['q2a5'];
$avarageROW['q2a6'][] = $student['q2a6'];
$avarageROW['q2a7'][] = $student['q2a7'];
$avarageROW['q2H'][] = max($q2Answers);
$avarageROW['q2L'][] = min($q2Answers);
$sheet->setCellValue('S' . $rowNB, max($q2Answers));
$sheet->setCellValue('T' . $rowNB, min($q2Answers));
$sheet->setCellValue('u' . $rowNB, array_sum($q2Answers));
$sheet->setCellValue($reorderingArray['q2a1'] . $rowNB, $student['q2a1']);
$sheet->setCellValue($reorderingArray['q2a2'] . $rowNB, $student['q2a2']);
$sheet->setCellValue($reorderingArray['q2a3'] . $rowNB, $student['q2a3']);
$sheet->setCellValue($reorderingArray['q2a4'] . $rowNB, $student['q2a4']);
$sheet->setCellValue($reorderingArray['q2a5'] . $rowNB, $student['q2a5']);
$sheet->setCellValue($reorderingArray['q2a6'] . $rowNB, $student['q2a6']);
$sheet->setCellValue($reorderingArray['q2a7'] . $rowNB, $student['q2a7']);
}
$q3Answers = array(
$student['q3a1'],
$student['q3a2'],
$student['q3a3'],
$student['q3a4'],
$student['q3a5'],
$student['q3a6'],
$student['q3a7'],
);
if (array_sum($q3Answers) == 100) {
$avarageROW['q3a1'][] = $student['q3a1'];
$avarageROW['q3a2'][] = $student['q3a2'];
$avarageROW['q3a3'][] = $student['q3a3'];
$avarageROW['q3a4'][] = $student['q3a4'];
$avarageROW['q3a5'][] = $student['q3a5'];
$avarageROW['q3a6'][] = $student['q3a6'];
$avarageROW['q3a7'][] = $student['q3a7'];
$avarageROW['q3H'][] = max($q3Answers);
$avarageROW['q3L'][] = min($q3Answers);
$sheet->setCellValue('AC' . $rowNB, max($q3Answers));
$sheet->setCellValue('AD' . $rowNB, min($q3Answers));
$sheet->setCellValue('AE' . $rowNB, array_sum($q3Answers));
$sheet->setCellValue($reorderingArray['q3a1'] . $rowNB, $student['q3a1']);
$sheet->setCellValue($reorderingArray['q3a2'] . $rowNB, $student['q3a2']);
$sheet->setCellValue($reorderingArray['q3a3'] . $rowNB, $student['q3a3']);
$sheet->setCellValue($reorderingArray['q3a4'] . $rowNB, $student['q3a4']);
$sheet->setCellValue($reorderingArray['q3a5'] . $rowNB, $student['q3a5']);
$sheet->setCellValue($reorderingArray['q3a6'] . $rowNB, $student['q3a6']);
$sheet->setCellValue($reorderingArray['q3a7'] . $rowNB, $student['q3a7']);
}
if ($j > 3) {
$q4Answers = array(
$student['q4a1'],
$student['q4a2'],
$student['q4a3'],
$student['q4a4'],
$student['q4a5'],
$student['q4a6'],
$student['q4a7'],
);
if (array_sum($q4Answers) == 100) {
$avarageROW['q4a1'][] = $student['q4a1'];
$avarageROW['q4a2'][] = $student['q4a2'];
$avarageROW['q4a3'][] = $student['q4a3'];
$avarageROW['q4a4'][] = $student['q4a4'];
$avarageROW['q4a5'][] = $student['q4a5'];
$avarageROW['q4a6'][] = $student['q4a6'];
$avarageROW['q4a7'][] = $student['q4a7'];
$avarageROW['q4H'][] = max($q4Answers);
$avarageROW['q4L'][] = min($q4Answers);
$sheet->setCellValue('AM' . $rowNB, max($q4Answers));
$sheet->setCellValue('AN' . $rowNB, min($q4Answers));
$sheet->setCellValue('AO' . $rowNB, array_sum($q4Answers));
$sheet->setCellValue($reorderingArray['q4a1'] . $rowNB, $student['q4a1']);
$sheet->setCellValue($reorderingArray['q4a2'] . $rowNB, $student['q4a2']);
$sheet->setCellValue($reorderingArray['q4a3'] . $rowNB, $student['q4a3']);
$sheet->setCellValue($reorderingArray['q4a4'] . $rowNB, $student['q4a4']);
$sheet->setCellValue($reorderingArray['q4a5'] . $rowNB, $student['q4a5']);
$sheet->setCellValue($reorderingArray['q4a6'] . $rowNB, $student['q4a6']);
$sheet->setCellValue($reorderingArray['q4a7'] . $rowNB, $student['q4a7']);
}
$q5Answers = array(
$student['q5a1'],
$student['q5a2'],
$student['q5a3'],
$student['q5a4'],
$student['q5a5'],
$student['q5a6'],
$student['q5a7'],
);
if (array_sum($q5Answers) == 100) {
$avarageROW['q5a1'][] = $student['q5a1'];
$avarageROW['q5a2'][] = $student['q5a2'];
$avarageROW['q5a3'][] = $student['q5a3'];
$avarageROW['q5a4'][] = $student['q5a4'];
$avarageROW['q5a5'][] = $student['q5a5'];
$avarageROW['q5a6'][] = $student['q5a6'];
$avarageROW['q5a7'][] = $student['q5a7'];
$avarageROW['q5H'][] = max($q5Answers);
$avarageROW['q5L'][] = min($q5Answers);
$sheet->setCellValue('AW' . $rowNB, max($q5Answers));
$sheet->setCellValue('AX' . $rowNB, min($q5Answers));
$sheet->setCellValue('AY' . $rowNB, array_sum($q5Answers));
$sheet->setCellValue($reorderingArray['q5a1'] . $rowNB, $student['q5a1']);
$sheet->setCellValue($reorderingArray['q5a2'] . $rowNB, $student['q5a2']);
$sheet->setCellValue($reorderingArray['q5a3'] . $rowNB, $student['q5a3']);
$sheet->setCellValue($reorderingArray['q5a4'] . $rowNB, $student['q5a4']);
$sheet->setCellValue($reorderingArray['q5a5'] . $rowNB, $student['q5a5']);
$sheet->setCellValue($reorderingArray['q5a6'] . $rowNB, $student['q5a6']);
$sheet->setCellValue($reorderingArray['q5a7'] . $rowNB, $student['q5a7']);
}
$q6Answers = array(
$student['q6a1'],
$student['q6a2'],
$student['q6a3'],
$student['q6a4'],
$student['q6a5'],
$student['q6a6'],
$student['q6a7'],
);
if (array_sum($q6Answers) == 100) {
$avarageROW['q6a1'][] = $student['q6a1'];
$avarageROW['q6a2'][] = $student['q6a2'];
$avarageROW['q6a3'][] = $student['q6a3'];
$avarageROW['q6a4'][] = $student['q6a4'];
$avarageROW['q6a5'][] = $student['q6a5'];
$avarageROW['q6a6'][] = $student['q6a6'];
$avarageROW['q6a7'][] = $student['q6a7'];
$avarageROW['q6H'][] = max($q6Answers);
$avarageROW['q6L'][] = min($q6Answers);
$sheet->setCellValue('BG' . $rowNB, max($q6Answers));
$sheet->setCellValue('BH' . $rowNB, min($q6Answers));
$sheet->setCellValue('BI' . $rowNB, array_sum($q6Answers));
$sheet->setCellValue($reorderingArray['q6a1'] . $rowNB, $student['q6a1']);
$sheet->setCellValue($reorderingArray['q6a2'] . $rowNB, $student['q6a2']);
$sheet->setCellValue($reorderingArray['q6a3'] . $rowNB, $student['q6a3']);
$sheet->setCellValue($reorderingArray['q6a4'] . $rowNB, $student['q6a4']);
$sheet->setCellValue($reorderingArray['q6a5'] . $rowNB, $student['q6a5']);
$sheet->setCellValue($reorderingArray['q6a6'] . $rowNB, $student['q6a6']);
$sheet->setCellValue($reorderingArray['q6a7'] . $rowNB, $student['q6a7']);
}
$q7Answers = array(
$student['q7a1'],
$student['q7a2'],
$student['q7a3'],
$student['q7a4'],
$student['q7a5'],
$student['q7a6'],
$student['q7a7'],
);
if (array_sum($q7Answers) == 100) {
$avarageROW['q7a1'][] = $student['q7a1'];
$avarageROW['q7a2'][] = $student['q7a2'];
$avarageROW['q7a3'][] = $student['q7a3'];
$avarageROW['q7a4'][] = $student['q7a4'];
$avarageROW['q7a5'][] = $student['q7a5'];
$avarageROW['q7a6'][] = $student['q7a6'];
$avarageROW['q7a7'][] = $student['q7a7'];
$avarageROW['q7H'][] = max($q7Answers);
$avarageROW['q7L'][] = min($q7Answers);
$sheet->setCellValue('BQ' . $rowNB, max($q7Answers));
$sheet->setCellValue('BR' . $rowNB, min($q7Answers));
$sheet->setCellValue('BS' . $rowNB, array_sum($q7Answers));
$sheet->setCellValue($reorderingArray['q7a1'] . $rowNB, $student['q7a1']);
$sheet->setCellValue($reorderingArray['q7a2'] . $rowNB, $student['q7a2']);
$sheet->setCellValue($reorderingArray['q7a3'] . $rowNB, $student['q7a3']);
$sheet->setCellValue($reorderingArray['q7a4'] . $rowNB, $student['q7a4']);
$sheet->setCellValue($reorderingArray['q7a5'] . $rowNB, $student['q7a5']);
$sheet->setCellValue($reorderingArray['q7a6'] . $rowNB, $student['q7a6']);
$sheet->setCellValue($reorderingArray['q7a7'] . $rowNB, $student['q7a7']);
}
$q8Answers = array(
$student['q8a1'],
$student['q8a2'],
$student['q8a3'],
$student['q8a4'],
$student['q8a5'],
$student['q8a6'],
$student['q8a7'],
);
if (array_sum($q8Answers) == 100) {
$avarageROW['q8a1'][] = $student['q8a1'];
$avarageROW['q8a2'][] = $student['q8a2'];
$avarageROW['q8a3'][] = $student['q8a3'];
$avarageROW['q8a4'][] = $student['q8a4'];
$avarageROW['q8a5'][] = $student['q8a5'];
$avarageROW['q8a6'][] = $student['q8a6'];
$avarageROW['q8a7'][] = $student['q8a7'];
$avarageROW['q8H'][] = max($q8Answers);
$avarageROW['q8L'][] = min($q8Answers);
$sheet->setCellValue('CA' . $rowNB, max($q8Answers));
$sheet->setCellValue('CB' . $rowNB, min($q8Answers));
$sheet->setCellValue('CC' . $rowNB, array_sum($q8Answers));
$sheet->setCellValue($reorderingArray['q8a1'] . $rowNB, $student['q8a1']);
$sheet->setCellValue($reorderingArray['q8a2'] . $rowNB, $student['q8a2']);
$sheet->setCellValue($reorderingArray['q8a3'] . $rowNB, $student['q8a3']);
$sheet->setCellValue($reorderingArray['q8a4'] . $rowNB, $student['q8a4']);
$sheet->setCellValue($reorderingArray['q8a5'] . $rowNB, $student['q8a5']);
$sheet->setCellValue($reorderingArray['q8a6'] . $rowNB, $student['q8a6']);
$sheet->setCellValue($reorderingArray['q8a7'] . $rowNB, $student['q8a7']);
}
}
$sheet->setCellValue('CD' . $rowNB, $student['username']);
$sheet->setCellValue('CE' . $rowNB, $student['first_name']);
$rowNB++;
}
$sheet->mergeCells("B" . $rowNB . ":H" . $rowNB . "");
$sheet->mergeCells("L" . $rowNB . ":Q" . $rowNB . "");
$sheet->mergeCells("V" . $rowNB . ":AB" . $rowNB . "");
if ($j > 3) {
$sheet->mergeCells("AF" . $rowNB . ":AL" . $rowNB . "");
$sheet->mergeCells("AP" . $rowNB . ":AV" . $rowNB . "");
$sheet->mergeCells("AZ" . $rowNB . ":BF" . $rowNB . "");
$sheet->mergeCells("BJ" . $rowNB . ":BP" . $rowNB . "");
$sheet->mergeCells("BT" . $rowNB . ":BZ" . $rowNB . "");
}
if ($j > 3) {
$sheet->setCellValue('B' . $rowNB . '', 'Decision Making ');
$sheet->setCellValue('L' . $rowNB . '', 'Conflict Management ');
$sheet->setCellValue('V' . $rowNB . '', 'Strategy Setting ');
$sheet->setCellValue('AF' . $rowNB . '', 'Authority');
$sheet->setCellValue('AP' . $rowNB . '', 'Advocacy ');
$sheet->setCellValue('AZ' . $rowNB . '', 'Coordination');
$sheet->setCellValue('BJ' . $rowNB . '', 'Critique & Feedback');
$sheet->setCellValue('BT' . $rowNB . '', 'Accountability ');
} else {
$sheet->setCellValue('B' . $rowNB . '', 'Decision Making ');
$sheet->setCellValue('L' . $rowNB . '', 'Conflict Management ');
$sheet->setCellValue('V' . $rowNB . '', 'Critique & Feedback ');
}
$rowNB++;
$sheet->setCellValue('A' . $rowNB . '', 'ID');
$sheet->setCellValue('B' . $rowNB . '', 'Opp');
$sheet->setCellValue('C' . $rowNB . '', '1,1');
$sheet->setCellValue('D' . $rowNB . '', '1,9');
$sheet->setCellValue('E' . $rowNB . '', '5,5');
$sheet->setCellValue('F' . $rowNB . '', '9,1');
$sheet->setCellValue('G' . $rowNB . '', 'PAT');
$sheet->setCellValue('H' . $rowNB . '', '9,9');
$sheet->setCellValue('I' . $rowNB . '', 'High');
$sheet->setCellValue('J' . $rowNB . '', 'Low');
$sheet->setCellValue('K' . $rowNB . '', 'Total Percent Q1');
$sheet->setCellValue('L' . $rowNB . '', 'Opp');
$sheet->setCellValue('M' . $rowNB . '', '1,1');
$sheet->setCellValue('N' . $rowNB . '', '1,9');
$sheet->setCellValue('O' . $rowNB . '', '5,5');
$sheet->setCellValue('P' . $rowNB . '', '9,1');
$sheet->setCellValue('Q' . $rowNB . '', 'PAT');
$sheet->setCellValue('R' . $rowNB . '', '9,9');
$sheet->setCellValue('S' . $rowNB . '', 'High');
$sheet->setCellValue('T' . $rowNB . '', 'Low');
$sheet->setCellValue('U' . $rowNB . '', 'Total Percent Q2');
$sheet->setCellValue('V' . $rowNB . '', 'Opp');
$sheet->setCellValue('W' . $rowNB . '', '1,1');
$sheet->setCellValue('X' . $rowNB . '', '1,9');
$sheet->setCellValue('Y' . $rowNB . '', '5,5');
$sheet->setCellValue('Z' . $rowNB . '', '9,1');
$sheet->setCellValue('AA' . $rowNB . '', 'PAT');
$sheet->setCellValue('AB' . $rowNB . '', '9,9');
$sheet->setCellValue('AC' . $rowNB . '', 'High');
$sheet->setCellValue('AD' . $rowNB . '', 'Low');
$sheet->setCellValue('AE' . $rowNB . '', 'Total Percent Q3');
if ($j > 3) {
$sheet->setCellValue('AF' . $rowNB . '', 'Opp');
$sheet->setCellValue('AG' . $rowNB . '', '1,1');
$sheet->setCellValue('AH' . $rowNB . '', '1,9');
$sheet->setCellValue('AI' . $rowNB . '', '5,5');
$sheet->setCellValue('AJ' . $rowNB . '', '9,1');
$sheet->setCellValue('AK' . $rowNB . '', 'PAT');
$sheet->setCellValue('AL' . $rowNB . '', '9,9');
$sheet->setCellValue('AM' . $rowNB . '', 'High');
$sheet->setCellValue('AN' . $rowNB . '', 'Low');
$sheet->setCellValue('AO' . $rowNB . '', 'Total Percent Q4');
$sheet->setCellValue('AP' . $rowNB . '', 'Opp');
$sheet->setCellValue('AQ' . $rowNB . '', '1,1');
$sheet->setCellValue('AR' . $rowNB . '', '1,9');
$sheet->setCellValue('AS' . $rowNB . '', '5,5');
$sheet->setCellValue('AT' . $rowNB . '', '9,1');
$sheet->setCellValue('AU' . $rowNB . '', 'PAT');
$sheet->setCellValue('AV' . $rowNB . '', '9,9');
$sheet->setCellValue('AW' . $rowNB . '', 'High');
$sheet->setCellValue('AX' . $rowNB . '', 'Low');
$sheet->setCellValue('AY' . $rowNB . '', 'Total Percent Q5');
$sheet->setCellValue('AZ' . $rowNB . '', 'Opp');
$sheet->setCellValue('BA' . $rowNB . '', '1,1');
$sheet->setCellValue('BB' . $rowNB . '', '1,9');
$sheet->setCellValue('BC' . $rowNB . '', '5,5');
$sheet->setCellValue('BD' . $rowNB . '', '9,1');
$sheet->setCellValue('BE' . $rowNB . '', 'PAT');
$sheet->setCellValue('BF' . $rowNB . '', '9,9');
$sheet->setCellValue('BG' . $rowNB . '', 'High');
$sheet->setCellValue('BH' . $rowNB . '', 'Low');
$sheet->setCellValue('BI' . $rowNB . '', 'Total Percent Q6');
$sheet->setCellValue('BJ' . $rowNB . '', 'Opp');
$sheet->setCellValue('BK' . $rowNB . '', '1,1');
$sheet->setCellValue('BL' . $rowNB . '', '1,9');
$sheet->setCellValue('BM' . $rowNB . '', '5,5');
$sheet->setCellValue('BN' . $rowNB . '', '9,1');
$sheet->setCellValue('BO' . $rowNB . '', 'PAT');
$sheet->setCellValue('BP' . $rowNB . '', '9,9');
$sheet->setCellValue('BQ' . $rowNB . '', 'High');
$sheet->setCellValue('BR' . $rowNB . '', 'Low');
$sheet->setCellValue('BS' . $rowNB . '', 'Total Percent Q7');
$sheet->setCellValue('BT' . $rowNB . '', 'Opp');
$sheet->setCellValue('BU' . $rowNB . '', '1,1');
$sheet->setCellValue('BV' . $rowNB . '', '1,9');
$sheet->setCellValue('BW' . $rowNB . '', '5,5');
$sheet->setCellValue('BX' . $rowNB . '', '9,1');
$sheet->setCellValue('BY' . $rowNB . '', 'PAT');
$sheet->setCellValue('BZ' . $rowNB . '', '9,9');
$sheet->setCellValue('CA' . $rowNB . '', 'High');
$sheet->setCellValue('CB' . $rowNB . '', 'Low');
$sheet->setCellValue('CC' . $rowNB . '', 'Total Percent Q8');
}
$rowNB++;
if (!empty($avarageROW)) {
// $sheet->setCellValue('I' . $rowNB, array_sum($avarageROW['q1H']) / count($avarageROW['q1H']));
// $sheet->setCellValue('J' . $rowNB, array_sum($avarageROW['q1L']) / count($avarageROW['q1L']));
$sheet->setCellValue('I' . $rowNB, max($avarageROW['q1H']));
$sheet->setCellValue('J' . $rowNB, min($avarageROW['q1L']));
$sheet->setCellValue($reorderingArray['q1a1'] . $rowNB, array_sum($avarageROW['q1a1']) / count($avarageROW['q1a1']));
$sheet->setCellValue($reorderingArray['q1a2'] . $rowNB, array_sum($avarageROW['q1a2']) / count($avarageROW['q1a2']));
$sheet->setCellValue($reorderingArray['q1a3'] . $rowNB, array_sum($avarageROW['q1a3']) / count($avarageROW['q1a3']));
$sheet->setCellValue($reorderingArray['q1a4'] . $rowNB, array_sum($avarageROW['q1a4']) / count($avarageROW['q1a4']));
$sheet->setCellValue($reorderingArray['q1a5'] . $rowNB, array_sum($avarageROW['q1a5']) / count($avarageROW['q1a5']));
$sheet->setCellValue($reorderingArray['q1a6'] . $rowNB, array_sum($avarageROW['q1a6']) / count($avarageROW['q1a6']));
$sheet->setCellValue($reorderingArray['q1a7'] . $rowNB, array_sum($avarageROW['q1a7']) / count($avarageROW['q1a7']));
// $sheet->setCellValue('S' . $rowNB, array_sum($avarageROW['q2H']) / count($avarageROW['q2H']));
// $sheet->setCellValue('T' . $rowNB, array_sum($avarageROW['q2L']) / count($avarageROW['q2L']));
$sheet->setCellValue('S' . $rowNB, max($avarageROW['q2H']));
$sheet->setCellValue('T' . $rowNB, min($avarageROW['q2L']));
$sheet->setCellValue($reorderingArray['q2a1'] . $rowNB, array_sum($avarageROW['q2a1']) / count($avarageROW['q2a1']));
$sheet->setCellValue($reorderingArray['q2a2'] . $rowNB, array_sum($avarageROW['q2a2']) / count($avarageROW['q2a2']));
$sheet->setCellValue($reorderingArray['q2a3'] . $rowNB, array_sum($avarageROW['q2a3']) / count($avarageROW['q2a3']));
$sheet->setCellValue($reorderingArray['q2a4'] . $rowNB, array_sum($avarageROW['q2a4']) / count($avarageROW['q2a4']));
$sheet->setCellValue($reorderingArray['q2a5'] . $rowNB, array_sum($avarageROW['q2a5']) / count($avarageROW['q2a5']));
$sheet->setCellValue($reorderingArray['q2a6'] . $rowNB, array_sum($avarageROW['q2a6']) / count($avarageROW['q2a6']));
$sheet->setCellValue($reorderingArray['q2a7'] . $rowNB, array_sum($avarageROW['q2a7']) / count($avarageROW['q2a7']));
// $sheet->setCellValue('AC' . $rowNB, array_sum($avarageROW['q3H']) / count($avarageROW['q3H']));
// $sheet->setCellValue('AD' . $rowNB, array_sum($avarageROW['q3L']) / count($avarageROW['q3L']));
$sheet->setCellValue('AC' . $rowNB, max($avarageROW['q3H']));
$sheet->setCellValue('AD' . $rowNB, min($avarageROW['q3L']));
$sheet->setCellValue($reorderingArray['q3a1'] . $rowNB, array_sum($avarageROW['q3a1']) / count($avarageROW['q3a1']));
$sheet->setCellValue($reorderingArray['q3a2'] . $rowNB, array_sum($avarageROW['q3a2']) / count($avarageROW['q3a2']));
$sheet->setCellValue($reorderingArray['q3a3'] . $rowNB, array_sum($avarageROW['q3a3']) / count($avarageROW['q3a3']));
$sheet->setCellValue($reorderingArray['q3a4'] . $rowNB, array_sum($avarageROW['q3a4']) / count($avarageROW['q3a4']));
$sheet->setCellValue($reorderingArray['q3a5'] . $rowNB, array_sum($avarageROW['q3a5']) / count($avarageROW['q3a5']));
$sheet->setCellValue($reorderingArray['q3a6'] . $rowNB, array_sum($avarageROW['q3a6']) / count($avarageROW['q3a6']));
$sheet->setCellValue($reorderingArray['q3a7'] . $rowNB, array_sum($avarageROW['q3a7']) / count($avarageROW['q3a7']));
if (isset($avarageROW['q4H'])) {
// $sheet->setCellValue('AM' . $rowNB, array_sum($avarageROW['q4H']) / count($avarageROW['q4H']));
// $sheet->setCellValue('AN' . $rowNB, array_sum($avarageROW['q4L']) / count($avarageROW['q4L']));
$sheet->setCellValue('AM' . $rowNB, max($avarageROW['q4H']));
$sheet->setCellValue('AN' . $rowNB, min($avarageROW['q4L']));
$sheet->setCellValue($reorderingArray['q4a1'] . $rowNB, array_sum($avarageROW['q4a1']) / count($avarageROW['q4a1']));
$sheet->setCellValue($reorderingArray['q4a2'] . $rowNB, array_sum($avarageROW['q4a2']) / count($avarageROW['q4a2']));
$sheet->setCellValue($reorderingArray['q4a3'] . $rowNB, array_sum($avarageROW['q4a3']) / count($avarageROW['q4a3']));
$sheet->setCellValue($reorderingArray['q4a4'] . $rowNB, array_sum($avarageROW['q4a4']) / count($avarageROW['q4a4']));
$sheet->setCellValue($reorderingArray['q4a5'] . $rowNB, array_sum($avarageROW['q4a5']) / count($avarageROW['q4a5']));
$sheet->setCellValue($reorderingArray['q4a6'] . $rowNB, array_sum($avarageROW['q4a6']) / count($avarageROW['q4a6']));
$sheet->setCellValue($reorderingArray['q4a7'] . $rowNB, array_sum($avarageROW['q4a7']) / count($avarageROW['q4a7']));
// $sheet->setCellValue('AW' . $rowNB, array_sum($avarageROW['q5H']) / count($avarageROW['q5H']));
// $sheet->setCellValue('AX' . $rowNB, array_sum($avarageROW['q5L']) / count($avarageROW['q5L']));
$sheet->setCellValue('AW' . $rowNB, max($avarageROW['q5H']));
$sheet->setCellValue('AX' . $rowNB, min($avarageROW['q5L']));
$sheet->setCellValue($reorderingArray['q5a1'] . $rowNB, array_sum($avarageROW['q5a1']) / count($avarageROW['q5a1']));
$sheet->setCellValue($reorderingArray['q5a2'] . $rowNB, array_sum($avarageROW['q5a2']) / count($avarageROW['q5a2']));
$sheet->setCellValue($reorderingArray['q5a3'] . $rowNB, array_sum($avarageROW['q5a3']) / count($avarageROW['q5a3']));
$sheet->setCellValue($reorderingArray['q5a4'] . $rowNB, array_sum($avarageROW['q5a4']) / count($avarageROW['q5a4']));
$sheet->setCellValue($reorderingArray['q5a5'] . $rowNB, array_sum($avarageROW['q5a5']) / count($avarageROW['q5a5']));
$sheet->setCellValue($reorderingArray['q5a6'] . $rowNB, array_sum($avarageROW['q5a6']) / count($avarageROW['q5a6']));
$sheet->setCellValue($reorderingArray['q5a7'] . $rowNB, array_sum($avarageROW['q5a7']) / count($avarageROW['q5a7']));
// $sheet->setCellValue('BG' . $rowNB, array_sum($avarageROW['q6H']) / count($avarageROW['q6H']));
// $sheet->setCellValue('BH' . $rowNB, array_sum($avarageROW['q6L']) / count($avarageROW['q6L']));
$sheet->setCellValue('BG' . $rowNB, max($avarageROW['q6H']));
$sheet->setCellValue('BH' . $rowNB, min($avarageROW['q6L']));
$sheet->setCellValue($reorderingArray['q6a1'] . $rowNB, array_sum($avarageROW['q6a1']) / count($avarageROW['q6a1']));
$sheet->setCellValue($reorderingArray['q6a2'] . $rowNB, array_sum($avarageROW['q6a2']) / count($avarageROW['q6a2']));
$sheet->setCellValue($reorderingArray['q6a3'] . $rowNB, array_sum($avarageROW['q6a3']) / count($avarageROW['q6a3']));
$sheet->setCellValue($reorderingArray['q6a4'] . $rowNB, array_sum($avarageROW['q6a4']) / count($avarageROW['q6a4']));
$sheet->setCellValue($reorderingArray['q6a5'] . $rowNB, array_sum($avarageROW['q6a5']) / count($avarageROW['q6a5']));
$sheet->setCellValue($reorderingArray['q6a6'] . $rowNB, array_sum($avarageROW['q6a6']) / count($avarageROW['q6a6']));
$sheet->setCellValue($reorderingArray['q6a7'] . $rowNB, array_sum($avarageROW['q6a7']) / count($avarageROW['q6a7']));
// $sheet->setCellValue('BQ' . $rowNB, array_sum($avarageROW['q7H']) / count($avarageROW['q7H']));
// $sheet->setCellValue('BR' . $rowNB, array_sum($avarageROW['q7L']) / count($avarageROW['q7L']));
$sheet->setCellValue('BQ' . $rowNB, max($avarageROW['q7H']));
$sheet->setCellValue('BR' . $rowNB, min($avarageROW['q7L']));
$sheet->setCellValue($reorderingArray['q7a1'] . $rowNB, array_sum($avarageROW['q7a1']) / count($avarageROW['q7a1']));
$sheet->setCellValue($reorderingArray['q7a2'] . $rowNB, array_sum($avarageROW['q7a2']) / count($avarageROW['q7a2']));
$sheet->setCellValue($reorderingArray['q7a3'] . $rowNB, array_sum($avarageROW['q7a3']) / count($avarageROW['q7a3']));
$sheet->setCellValue($reorderingArray['q7a4'] . $rowNB, array_sum($avarageROW['q7a4']) / count($avarageROW['q7a4']));
$sheet->setCellValue($reorderingArray['q7a5'] . $rowNB, array_sum($avarageROW['q7a5']) / count($avarageROW['q7a5']));
$sheet->setCellValue($reorderingArray['q7a6'] . $rowNB, array_sum($avarageROW['q7a6']) / count($avarageROW['q7a6']));
$sheet->setCellValue($reorderingArray['q7a7'] . $rowNB, array_sum($avarageROW['q7a7']) / count($avarageROW['q7a7']));
// $sheet->setCellValue('CA' . $rowNB, array_sum($avarageROW['q8H']) / count($avarageROW['q8H']));
// $sheet->setCellValue('CB' . $rowNB, array_sum($avarageROW['q8L']) / count($avarageROW['q8L']));
$sheet->setCellValue('CA' . $rowNB, max($avarageROW['q8H']));
$sheet->setCellValue('CB' . $rowNB, min($avarageROW['q8L']));
$sheet->setCellValue($reorderingArray['q8a1'] . $rowNB, array_sum($avarageROW['q8a1']) / count($avarageROW['q8a1']));
$sheet->setCellValue($reorderingArray['q8a2'] . $rowNB, array_sum($avarageROW['q8a2']) / count($avarageROW['q8a2']));
$sheet->setCellValue($reorderingArray['q8a3'] . $rowNB, array_sum($avarageROW['q8a3']) / count($avarageROW['q8a3']));
$sheet->setCellValue($reorderingArray['q8a4'] . $rowNB, array_sum($avarageROW['q8a4']) / count($avarageROW['q8a4']));
$sheet->setCellValue($reorderingArray['q8a5'] . $rowNB, array_sum($avarageROW['q8a5']) / count($avarageROW['q8a5']));
$sheet->setCellValue($reorderingArray['q8a6'] . $rowNB, array_sum($avarageROW['q8a6']) / count($avarageROW['q8a6']));
$sheet->setCellValue($reorderingArray['q8a7'] . $rowNB, array_sum($avarageROW['q8a7']) / count($avarageROW['q8a7']));
}
}
$reorderedSheet = new Worksheet($spreadsheet, 'comments');
$rowNB = 1;
if ($j > 3) {
$reorderedSheet->setCellValue('B' . $rowNB, 'Decision Making Impact ');
$reorderedSheet->setCellValue('A' . $rowNB, 'Decision Making Examples ');
$reorderedSheet->setCellValue('D' . $rowNB, 'Conflict Resolution Impact ');
$reorderedSheet->setCellValue('C' . $rowNB, 'Conflict Management Examples');
$reorderedSheet->setCellValue('F' . $rowNB, 'Goals & Objective Impact ');
$reorderedSheet->setCellValue('E' . $rowNB, 'Goals & Objective Example ');
$reorderedSheet->setCellValue('H' . $rowNB, 'Authority Impact ');
$reorderedSheet->setCellValue('G' . $rowNB, 'Authority Examples');
$reorderedSheet->setCellValue('J' . $rowNB, 'Advocacy Impact');
$reorderedSheet->setCellValue('I' . $rowNB, 'Advocacy Examples ');
$reorderedSheet->setCellValue('L' . $rowNB, 'Coordination Impact ');
$reorderedSheet->setCellValue('K' . $rowNB, 'Coordination Examples ');
$reorderedSheet->setCellValue('N' . $rowNB, 'Critique & Feedback Impact ');
$reorderedSheet->setCellValue('M' . $rowNB, 'Critique & Feedback Examples ');
$reorderedSheet->setCellValue('P' . $rowNB, 'Accountability Impact ');
$reorderedSheet->setCellValue('O' . $rowNB, 'Accountability Examples ');
} else {
$reorderedSheet->setCellValue('B' . $rowNB, 'Decision Making Impact ');
$reorderedSheet->setCellValue('A' . $rowNB, 'Decision Making Examples ');
$reorderedSheet->setCellValue('D' . $rowNB, 'Conflict Resolution Impact ');
$reorderedSheet->setCellValue('C' . $rowNB, 'Conflict Management Examples');
$reorderedSheet->setCellValue('F' . $rowNB, 'Critique & Feedback Impact ');
$reorderedSheet->setCellValue('E' . $rowNB, 'Critique & Feedback Examples');
}
$rowNB++;
foreach ($data as $student) {
$reorderedSheet->setCellValue('B' . $rowNB, $student['exp1']);
$reorderedSheet->setCellValue('A' . $rowNB, $student['cat1']);
$reorderedSheet->setCellValue('D' . $rowNB, $student['exp2']);
$reorderedSheet->setCellValue('C' . $rowNB, $student['cat2']);
$reorderedSheet->setCellValue('F' . $rowNB, $student['exp3']);
$reorderedSheet->setCellValue('E' . $rowNB, $student['cat3']);
if ($j > 3) {
$reorderedSheet->setCellValue('H' . $rowNB, $student['exp4']);
$reorderedSheet->setCellValue('G' . $rowNB, $student['cat4']);
$reorderedSheet->setCellValue('J' . $rowNB, $student['exp5']);
$reorderedSheet->setCellValue('I' . $rowNB, $student['cat5']);
$reorderedSheet->setCellValue('L' . $rowNB, $student['exp6']);
$reorderedSheet->setCellValue('K' . $rowNB, $student['cat6']);
$reorderedSheet->setCellValue('N' . $rowNB, $student['exp7']);
$reorderedSheet->setCellValue('M' . $rowNB, $student['cat7']);
$reorderedSheet->setCellValue('P' . $rowNB, $student['exp8']);
$reorderedSheet->setCellValue('O' . $rowNB, $student['cat8']);
}
$reorderedSheet->setCellValue('Q' . $rowNB, $student['username']);
$reorderedSheet->setCellValue('R' . $rowNB, $student['first_name']);
$rowNB++;
}
$spreadsheet->addSheet($reorderedSheet, 1);
$conn = $entityManager->getConnection();
$sql = 'SELECT DISTINCT (team.id) ,team.name FROM `student_survey`,users,team,student WHERE users.id=student.id and student.team_id=team.id and student_id= users.id and survey_id= :survey_id';
$stmt = $conn->prepare($sql);
$stmt->execute(['survey_id' => $id]);
$teams = $stmt->fetchAll();
$sheetIndex = 1;
foreach ($teams as $team) {
$sheetIndex++;
$teanSheet = $this->addTeamSheet($id, $team, $spreadsheet, $survey);
$spreadsheet->addSheet($teanSheet, $sheetIndex);
}
$writer = new Xlsx($spreadsheet);
$truncatedName = (mb_strlen($survey->getName()) > 25) ? mb_substr($survey->getName(), 0, 25) : $survey->getName();
// Create a Temporary file in the system
$fileName = $truncatedName . '.xlsx';
$temp_file = tempnam(sys_get_temp_dir(), $fileName);
// Create the excel file in the tmp directory of the system
$writer->save($temp_file);
// Return the excel file as an attachment
return $this->file($temp_file, $fileName, ResponseHeaderBag::DISPOSITION_INLINE);
}
public function addTeamSheet($surveyID, $team, $spreadsheet, $survey)
{
$entityManager = $this->getDoctrine()->getManager();
// $truncatedName = (strlen( $team['name']) > 25) ? substr( $team['name'], 0, 25) : $team['name'];
$truncatedName = (mb_strlen($team['name']) > 25) ? mb_substr($team['name'], 0, 25) : $team['name'];
$questions = $entityManager->getRepository(Question::class)
->findBy(array('type' => $survey->getType()));
$j = count($questions);
$teamSheet = new Worksheet($spreadsheet, $truncatedName);
$teamSheet->mergeCells("B1:H1");
$teamSheet->mergeCells("L1:Q1");
$teamSheet->mergeCells("V1:AB1");
if ($j > 3) {
$teamSheet->mergeCells("AF1:AL1");
$teamSheet->mergeCells("AP1:AV1");
$teamSheet->mergeCells("AZ1:BF1");
$teamSheet->mergeCells("BJ1:BP1");
$teamSheet->mergeCells("BT1:BZ1");
}
if ($j > 3) {
$teamSheet->setCellValue('B1', 'Decision Making ');
$teamSheet->setCellValue('L1', 'Conflict Management ');
$teamSheet->setCellValue('V1', 'Strategy Setting ');
$teamSheet->setCellValue('AF1', 'Authority');
$teamSheet->setCellValue('AP1', 'Advocacy ');
$teamSheet->setCellValue('AZ1', 'Coordination');
$teamSheet->setCellValue('BJ1', 'Critique & Feedback');
$teamSheet->setCellValue('BT1', 'Accountability ');
} else {
$teamSheet->setCellValue('B1', 'Decision Making ');
$teamSheet->setCellValue('L1', 'Conflict Management ');
$teamSheet->setCellValue('V1', 'Critique & Feedback');
}
$teamSheet->setCellValue('A2', 'ID');
$teamSheet->setCellValue('B2', 'Opp');
$teamSheet->setCellValue('C2', '1,1');
$teamSheet->setCellValue('D2', '1,9');
$teamSheet->setCellValue('E2', '5,5');
$teamSheet->setCellValue('F2', '9,1');
$teamSheet->setCellValue('G2', 'PAT');
$teamSheet->setCellValue('H2', '9,9');
$teamSheet->setCellValue('I2', 'High');
$teamSheet->setCellValue('J2', 'Low');
$teamSheet->setCellValue('K2', 'Total Percent Q1');
$teamSheet->setCellValue('L2', 'Opp');
$teamSheet->setCellValue('M2', '1,1');
$teamSheet->setCellValue('N2', '1,9');
$teamSheet->setCellValue('O2', '5,5');
$teamSheet->setCellValue('P2', '9,1');
$teamSheet->setCellValue('Q2', 'PAT');
$teamSheet->setCellValue('R2', '9,9');
$teamSheet->setCellValue('S2', 'High');
$teamSheet->setCellValue('T2', 'Low');
$teamSheet->setCellValue('U2', 'Total Percent Q2');
$teamSheet->setCellValue('V2', 'Opp');
$teamSheet->setCellValue('W2', '1,1');
$teamSheet->setCellValue('X2', '1,9');
$teamSheet->setCellValue('Y2', '5,5');
$teamSheet->setCellValue('Z2', '9,1');
$teamSheet->setCellValue('AA2', 'PAT');
$teamSheet->setCellValue('AB2', '9,9');
$teamSheet->setCellValue('AC2', 'High');
$teamSheet->setCellValue('AD2', 'Low');
$teamSheet->setCellValue('AE2', 'Total Percent Q3');
if ($j > 3) {
$teamSheet->setCellValue('AF2', 'Opp');
$teamSheet->setCellValue('AG2', '1,1');
$teamSheet->setCellValue('AH2', '1,9');
$teamSheet->setCellValue('AI2', '5,5');
$teamSheet->setCellValue('AJ2', '9,1');
$teamSheet->setCellValue('AK2', 'PAT');
$teamSheet->setCellValue('AL2', '9,9');
$teamSheet->setCellValue('AM2', 'High');
$teamSheet->setCellValue('AN2', 'Low');
$teamSheet->setCellValue('AO2', 'Total Percent Q4');
$teamSheet->setCellValue('AP2', 'Opp');
$teamSheet->setCellValue('AQ2', '1,1');
$teamSheet->setCellValue('AR2', '1,9');
$teamSheet->setCellValue('AS2', '5,5');
$teamSheet->setCellValue('AT2', '9,1');
$teamSheet->setCellValue('AU2', 'PAT');
$teamSheet->setCellValue('AV2', '9,9');
$teamSheet->setCellValue('AW2', 'High');
$teamSheet->setCellValue('AX2', 'Low');
$teamSheet->setCellValue('AY2', 'Total Percent Q5');
$teamSheet->setCellValue('AZ2', 'Opp');
$teamSheet->setCellValue('BA2', '1,1');
$teamSheet->setCellValue('BB2', '1,9');
$teamSheet->setCellValue('BC2', '5,5');
$teamSheet->setCellValue('BD2', '9,1');
$teamSheet->setCellValue('BE2', 'PAT');
$teamSheet->setCellValue('BF2', '9,9');
$teamSheet->setCellValue('BG2', 'High');
$teamSheet->setCellValue('BH2', 'Low');
$teamSheet->setCellValue('BI2', 'Total Percent Q6');
$teamSheet->setCellValue('BJ2', 'Opp');
$teamSheet->setCellValue('BK2', '1,1');
$teamSheet->setCellValue('BL2', '1,9');
$teamSheet->setCellValue('BM2', '5,5');
$teamSheet->setCellValue('BN2', '9,1');
$teamSheet->setCellValue('BO2', 'PAT');
$teamSheet->setCellValue('BP2', '9,9');
$teamSheet->setCellValue('BQ2', 'High');
$teamSheet->setCellValue('BR2', 'Low');
$teamSheet->setCellValue('BS2', 'Total Percent Q7');
$teamSheet->setCellValue('BT2', 'Opp');
$teamSheet->setCellValue('BU2', '1,1');
$teamSheet->setCellValue('BV2', '1,9');
$teamSheet->setCellValue('BW2', '5,5');
$teamSheet->setCellValue('BX2', '9,1');
$teamSheet->setCellValue('BY2', 'PAT');
$teamSheet->setCellValue('BZ2', '9,9');
$teamSheet->setCellValue('CA2', 'High');
$teamSheet->setCellValue('CB2', 'Low');
$teamSheet->setCellValue('CC2', 'Total Percent Q8');
}
$reorderingArray[] = array();
$j = 0;
foreach ($questions as $question) {
$i = 0;
$j++;
foreach ($question->getAnswers() as $answer) {
$i++;
if ($j == 1) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'B';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'C';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'D';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'E';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'F';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'G';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'H';
}
} else {
if ($j == 2) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'L';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'M';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'N';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'O';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'P';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'Q';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'R';
}
} else {
if ($j == 3) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'V';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'W';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'X';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'Y';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'Z';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'AA';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AB';
}
} else {
if ($j == 4) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'AF';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'AG';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AH';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'AI';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'AJ';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'AK';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AL';
}
} else {
if ($j == 5) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'AP';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'AQ';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AR';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'AS';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'AT';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'AU';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'AV';
}
} else {
if ($j == 6) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'AZ';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BA';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BB';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'BC';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BD';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'BE';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BF';
}
} else {
if ($j == 7) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'BJ';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BK';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BL';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'BM';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BN';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'BO';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BP';
}
} else {
if ($j == 8) {
if (trim($answer->getDescription()) == 'OPP') {
$reorderingArray['q' . $j . 'a' . $i] = 'BT';
} elseif (trim($answer->getDescription()) == '1,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BU';
} elseif (trim($answer->getDescription()) == '1,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BV';
} elseif (trim($answer->getDescription()) == '5,5') {
$reorderingArray['q' . $j . 'a' . $i] = 'BW';
} elseif (trim($answer->getDescription()) == '9,1') {
$reorderingArray['q' . $j . 'a' . $i] = 'BX';
} elseif (trim($answer->getDescription()) == 'PAT') {
$reorderingArray['q' . $j . 'a' . $i] = 'BY';
} elseif (trim($answer->getDescription()) == '9,9') {
$reorderingArray['q' . $j . 'a' . $i] = 'BZ';
}
}
}
}
}
}
}
}
}
}
}
$conn = $entityManager->getConnection();
$sql = 'SELECT * FROM `student_survey`,users,team,student WHERE users.id=student.id and student.team_id=team.id and student_id= users.id and survey_id= :survey_id and team.id = :team_id';
$stmt = $conn->prepare($sql);
$stmt->execute(['survey_id' => $surveyID, 'team_id' => $team['id']]);
$data = $stmt->fetchAll();
$rowNB = 3;
$avarageROW = array();
foreach ($data as $student) {
$q1Answers = array(
$student['q1a1'],
$student['q1a2'],
$student['q1a3'],
$student['q1a4'],
$student['q1a5'],
$student['q1a6'],
$student['q1a7'],
);
if (array_sum($q1Answers) == 100) {
$avarageROW['q1a1'][] = $student['q1a1'];
$avarageROW['q1a2'][] = $student['q1a2'];
$avarageROW['q1a3'][] = $student['q1a3'];
$avarageROW['q1a4'][] = $student['q1a4'];
$avarageROW['q1a5'][] = $student['q1a5'];
$avarageROW['q1a6'][] = $student['q1a6'];
$avarageROW['q1a7'][] = $student['q1a7'];
$avarageROW['q1L'][] = min($q1Answers);
$avarageROW['q1H'][] = max($q1Answers);
$teamSheet->setCellValue('I' . $rowNB, max($q1Answers));
$teamSheet->setCellValue('J' . $rowNB, min($q1Answers));
$teamSheet->setCellValue('K' . $rowNB, array_sum($q1Answers));
$teamSheet->setCellValue($reorderingArray['q1a1'] . $rowNB, $student['q1a1']);
$teamSheet->setCellValue($reorderingArray['q1a2'] . $rowNB, $student['q1a2']);
$teamSheet->setCellValue($reorderingArray['q1a3'] . $rowNB, $student['q1a3']);
$teamSheet->setCellValue($reorderingArray['q1a4'] . $rowNB, $student['q1a4']);
$teamSheet->setCellValue($reorderingArray['q1a5'] . $rowNB, $student['q1a5']);
$teamSheet->setCellValue($reorderingArray['q1a6'] . $rowNB, $student['q1a6']);
$teamSheet->setCellValue($reorderingArray['q1a7'] . $rowNB, $student['q1a7']);
}
$q2Answers = array(
$student['q2a1'],
$student['q2a2'],
$student['q2a3'],
$student['q2a4'],
$student['q2a5'],
$student['q2a6'],
$student['q2a7'],
);
if (array_sum($q2Answers) == 100) {
$avarageROW['q2a1'][] = $student['q2a1'];
$avarageROW['q2a2'][] = $student['q2a2'];
$avarageROW['q2a3'][] = $student['q2a3'];
$avarageROW['q2a4'][] = $student['q2a4'];
$avarageROW['q2a5'][] = $student['q2a5'];
$avarageROW['q2a6'][] = $student['q2a6'];
$avarageROW['q2a7'][] = $student['q2a7'];
$avarageROW['q2H'][] = max($q2Answers);
$avarageROW['q2L'][] = min($q2Answers);
$teamSheet->setCellValue('S' . $rowNB, max($q2Answers));
$teamSheet->setCellValue('T' . $rowNB, min($q2Answers));
$teamSheet->setCellValue('u' . $rowNB, array_sum($q2Answers));
$teamSheet->setCellValue($reorderingArray['q2a1'] . $rowNB, $student['q2a1']);
$teamSheet->setCellValue($reorderingArray['q2a2'] . $rowNB, $student['q2a2']);
$teamSheet->setCellValue($reorderingArray['q2a3'] . $rowNB, $student['q2a3']);
$teamSheet->setCellValue($reorderingArray['q2a4'] . $rowNB, $student['q2a4']);
$teamSheet->setCellValue($reorderingArray['q2a5'] . $rowNB, $student['q2a5']);
$teamSheet->setCellValue($reorderingArray['q2a6'] . $rowNB, $student['q2a6']);
$teamSheet->setCellValue($reorderingArray['q2a7'] . $rowNB, $student['q2a7']);
}
$q3Answers = array(
$student['q3a1'],
$student['q3a2'],
$student['q3a3'],
$student['q3a4'],
$student['q3a5'],
$student['q3a6'],
$student['q3a7'],
);
if (array_sum($q3Answers) == 100) {
$avarageROW['q3a1'][] = $student['q3a1'];
$avarageROW['q3a2'][] = $student['q3a2'];
$avarageROW['q3a3'][] = $student['q3a3'];
$avarageROW['q3a4'][] = $student['q3a4'];
$avarageROW['q3a5'][] = $student['q3a5'];
$avarageROW['q3a6'][] = $student['q3a6'];
$avarageROW['q3a7'][] = $student['q3a7'];
$avarageROW['q3H'][] = max($q3Answers);
$avarageROW['q3L'][] = min($q3Answers);
$teamSheet->setCellValue('AC' . $rowNB, max($q3Answers));
$teamSheet->setCellValue('AD' . $rowNB, min($q3Answers));
$teamSheet->setCellValue('AE' . $rowNB, array_sum($q3Answers));
$teamSheet->setCellValue($reorderingArray['q3a1'] . $rowNB, $student['q3a1']);
$teamSheet->setCellValue($reorderingArray['q3a2'] . $rowNB, $student['q3a2']);
$teamSheet->setCellValue($reorderingArray['q3a3'] . $rowNB, $student['q3a3']);
$teamSheet->setCellValue($reorderingArray['q3a4'] . $rowNB, $student['q3a4']);
$teamSheet->setCellValue($reorderingArray['q3a5'] . $rowNB, $student['q3a5']);
$teamSheet->setCellValue($reorderingArray['q3a6'] . $rowNB, $student['q3a6']);
$teamSheet->setCellValue($reorderingArray['q3a7'] . $rowNB, $student['q3a7']);
}
if ($j > 3) {
$q4Answers = array(
$student['q4a1'],
$student['q4a2'],
$student['q4a3'],
$student['q4a4'],
$student['q4a5'],
$student['q4a6'],
$student['q4a7'],
);
if (array_sum($q4Answers) == 100) {
$avarageROW['q4a1'][] = $student['q4a1'];
$avarageROW['q4a2'][] = $student['q4a2'];
$avarageROW['q4a3'][] = $student['q4a3'];
$avarageROW['q4a4'][] = $student['q4a4'];
$avarageROW['q4a5'][] = $student['q4a5'];
$avarageROW['q4a6'][] = $student['q4a6'];
$avarageROW['q4a7'][] = $student['q4a7'];
$avarageROW['q4H'][] = max($q4Answers);
$avarageROW['q4L'][] = min($q4Answers);
$teamSheet->setCellValue('AM' . $rowNB, max($q4Answers));
$teamSheet->setCellValue('AN' . $rowNB, min($q4Answers));
$teamSheet->setCellValue('AO' . $rowNB, array_sum($q4Answers));
$teamSheet->setCellValue($reorderingArray['q4a1'] . $rowNB, $student['q4a1']);
$teamSheet->setCellValue($reorderingArray['q4a2'] . $rowNB, $student['q4a2']);
$teamSheet->setCellValue($reorderingArray['q4a3'] . $rowNB, $student['q4a3']);
$teamSheet->setCellValue($reorderingArray['q4a4'] . $rowNB, $student['q4a4']);
$teamSheet->setCellValue($reorderingArray['q4a5'] . $rowNB, $student['q4a5']);
$teamSheet->setCellValue($reorderingArray['q4a6'] . $rowNB, $student['q4a6']);
$teamSheet->setCellValue($reorderingArray['q4a7'] . $rowNB, $student['q4a7']);
}
$q5Answers = array(
$student['q5a1'],
$student['q5a2'],
$student['q5a3'],
$student['q5a4'],
$student['q5a5'],
$student['q5a6'],
$student['q5a7'],
);
if (array_sum($q5Answers) == 100) {
$avarageROW['q5a1'][] = $student['q5a1'];
$avarageROW['q5a2'][] = $student['q5a2'];
$avarageROW['q5a3'][] = $student['q5a3'];
$avarageROW['q5a4'][] = $student['q5a4'];
$avarageROW['q5a5'][] = $student['q5a5'];
$avarageROW['q5a6'][] = $student['q5a6'];
$avarageROW['q5a7'][] = $student['q5a7'];
$avarageROW['q5H'][] = max($q5Answers);
$avarageROW['q5L'][] = min($q5Answers);
$teamSheet->setCellValue('AW' . $rowNB, max($q5Answers));
$teamSheet->setCellValue('AX' . $rowNB, min($q5Answers));
$teamSheet->setCellValue('AY' . $rowNB, array_sum($q5Answers));
$teamSheet->setCellValue($reorderingArray['q5a1'] . $rowNB, $student['q5a1']);
$teamSheet->setCellValue($reorderingArray['q5a2'] . $rowNB, $student['q5a2']);
$teamSheet->setCellValue($reorderingArray['q5a3'] . $rowNB, $student['q5a3']);
$teamSheet->setCellValue($reorderingArray['q5a4'] . $rowNB, $student['q5a4']);
$teamSheet->setCellValue($reorderingArray['q5a5'] . $rowNB, $student['q5a5']);
$teamSheet->setCellValue($reorderingArray['q5a6'] . $rowNB, $student['q5a6']);
$teamSheet->setCellValue($reorderingArray['q5a7'] . $rowNB, $student['q5a7']);
}
$q6Answers = array(
$student['q6a1'],
$student['q6a2'],
$student['q6a3'],
$student['q6a4'],
$student['q6a5'],
$student['q6a6'],
$student['q6a7'],
);
if (array_sum($q6Answers) == 100) {
$avarageROW['q6a1'][] = $student['q6a1'];
$avarageROW['q6a2'][] = $student['q6a2'];
$avarageROW['q6a3'][] = $student['q6a3'];
$avarageROW['q6a4'][] = $student['q6a4'];
$avarageROW['q6a5'][] = $student['q6a5'];
$avarageROW['q6a6'][] = $student['q6a6'];
$avarageROW['q6a7'][] = $student['q6a7'];
$avarageROW['q6H'][] = max($q6Answers);
$avarageROW['q6L'][] = min($q6Answers);
$teamSheet->setCellValue('BG' . $rowNB, max($q6Answers));
$teamSheet->setCellValue('BH' . $rowNB, min($q6Answers));
$teamSheet->setCellValue('BI' . $rowNB, array_sum($q6Answers));
$teamSheet->setCellValue($reorderingArray['q6a1'] . $rowNB, $student['q6a1']);
$teamSheet->setCellValue($reorderingArray['q6a2'] . $rowNB, $student['q6a2']);
$teamSheet->setCellValue($reorderingArray['q6a3'] . $rowNB, $student['q6a3']);
$teamSheet->setCellValue($reorderingArray['q6a4'] . $rowNB, $student['q6a4']);
$teamSheet->setCellValue($reorderingArray['q6a5'] . $rowNB, $student['q6a5']);
$teamSheet->setCellValue($reorderingArray['q6a6'] . $rowNB, $student['q6a6']);
$teamSheet->setCellValue($reorderingArray['q6a7'] . $rowNB, $student['q6a7']);
}
$q7Answers = array(
$student['q7a1'],
$student['q7a2'],
$student['q7a3'],
$student['q7a4'],
$student['q7a5'],
$student['q7a6'],
$student['q7a7'],
);
if (array_sum($q7Answers) == 100) {
$avarageROW['q7a1'][] = $student['q7a1'];
$avarageROW['q7a2'][] = $student['q7a2'];
$avarageROW['q7a3'][] = $student['q7a3'];
$avarageROW['q7a4'][] = $student['q7a4'];
$avarageROW['q7a5'][] = $student['q7a5'];
$avarageROW['q7a6'][] = $student['q7a6'];
$avarageROW['q7a7'][] = $student['q7a7'];
$avarageROW['q7H'][] = max($q7Answers);
$avarageROW['q7L'][] = min($q7Answers);
$teamSheet->setCellValue('BQ' . $rowNB, max($q7Answers));
$teamSheet->setCellValue('BR' . $rowNB, min($q7Answers));
$teamSheet->setCellValue('BS' . $rowNB, array_sum($q7Answers));
$teamSheet->setCellValue($reorderingArray['q7a1'] . $rowNB, $student['q7a1']);
$teamSheet->setCellValue($reorderingArray['q7a2'] . $rowNB, $student['q7a2']);
$teamSheet->setCellValue($reorderingArray['q7a3'] . $rowNB, $student['q7a3']);
$teamSheet->setCellValue($reorderingArray['q7a4'] . $rowNB, $student['q7a4']);
$teamSheet->setCellValue($reorderingArray['q7a5'] . $rowNB, $student['q7a5']);
$teamSheet->setCellValue($reorderingArray['q7a6'] . $rowNB, $student['q7a6']);
$teamSheet->setCellValue($reorderingArray['q7a7'] . $rowNB, $student['q7a7']);
}
$q8Answers = array(
$student['q8a1'],
$student['q8a2'],
$student['q8a3'],
$student['q8a4'],
$student['q8a5'],
$student['q8a6'],
$student['q8a7'],
);
if (array_sum($q8Answers) == 100) {
$avarageROW['q8a1'][] = $student['q8a1'];
$avarageROW['q8a2'][] = $student['q8a2'];
$avarageROW['q8a3'][] = $student['q8a3'];
$avarageROW['q8a4'][] = $student['q8a4'];
$avarageROW['q8a5'][] = $student['q8a5'];
$avarageROW['q8a6'][] = $student['q8a6'];
$avarageROW['q8a7'][] = $student['q8a7'];
$avarageROW['q8H'][] = max($q8Answers);
$avarageROW['q8L'][] = min($q8Answers);
$teamSheet->setCellValue('CA' . $rowNB, max($q8Answers));
$teamSheet->setCellValue('CB' . $rowNB, min($q8Answers));
$teamSheet->setCellValue('CC' . $rowNB, array_sum($q8Answers));
$teamSheet->setCellValue($reorderingArray['q8a1'] . $rowNB, $student['q8a1']);
$teamSheet->setCellValue($reorderingArray['q8a2'] . $rowNB, $student['q8a2']);
$teamSheet->setCellValue($reorderingArray['q8a3'] . $rowNB, $student['q8a3']);
$teamSheet->setCellValue($reorderingArray['q8a4'] . $rowNB, $student['q8a4']);
$teamSheet->setCellValue($reorderingArray['q8a5'] . $rowNB, $student['q8a5']);
$teamSheet->setCellValue($reorderingArray['q8a6'] . $rowNB, $student['q8a6']);
$teamSheet->setCellValue($reorderingArray['q8a7'] . $rowNB, $student['q8a7']);
}
}
$teamSheet->setCellValue('CD' . $rowNB, $student['username']);
$teamSheet->setCellValue('CE' . $rowNB, $student['first_name']);
$rowNB++;
}
$teamSheet->mergeCells("B" . $rowNB . ":H" . $rowNB . "");
$teamSheet->mergeCells("L" . $rowNB . ":Q" . $rowNB . "");
$teamSheet->mergeCells("V" . $rowNB . ":AB" . $rowNB . "");
$teamSheet->mergeCells("AF" . $rowNB . ":AL" . $rowNB . "");
$teamSheet->mergeCells("AP" . $rowNB . ":AV" . $rowNB . "");
$teamSheet->mergeCells("AZ" . $rowNB . ":BF" . $rowNB . "");
$teamSheet->mergeCells("BJ" . $rowNB . ":BP" . $rowNB . "");
$teamSheet->mergeCells("BT" . $rowNB . ":BZ" . $rowNB . "");
if ($j > 3) {
$teamSheet->setCellValue('B' . $rowNB . '', 'Decision Making ');
$teamSheet->setCellValue('L' . $rowNB . '', 'Conflict Management ');
$teamSheet->setCellValue('V' . $rowNB . '', 'Strategy Setting ');
$teamSheet->setCellValue('AF' . $rowNB . '', 'Authority');
$teamSheet->setCellValue('AP' . $rowNB . '', 'Advocacy ');
$teamSheet->setCellValue('AZ' . $rowNB . '', 'Coordination');
$teamSheet->setCellValue('BJ' . $rowNB . '', 'Critique & Feedback');
$teamSheet->setCellValue('BT' . $rowNB . '', 'Accountability ');
} else {
$teamSheet->setCellValue('B' . $rowNB . '', 'Decision Making ');
$teamSheet->setCellValue('L' . $rowNB . '', 'Conflict Management ');
$teamSheet->setCellValue('V' . $rowNB . '', 'Critique & Feedback ');
}
$rowNB++;
$teamSheet->setCellValue('A' . $rowNB . '', 'ID');
$teamSheet->setCellValue('B' . $rowNB . '', 'Opp');
$teamSheet->setCellValue('C' . $rowNB . '', '1,1');
$teamSheet->setCellValue('D' . $rowNB . '', '1,9');
$teamSheet->setCellValue('E' . $rowNB . '', '5,5');
$teamSheet->setCellValue('F' . $rowNB . '', '9,1');
$teamSheet->setCellValue('G' . $rowNB . '', 'PAT');
$teamSheet->setCellValue('H' . $rowNB . '', '9,9');
$teamSheet->setCellValue('I' . $rowNB . '', 'High');
$teamSheet->setCellValue('J' . $rowNB . '', 'Low');
$teamSheet->setCellValue('K' . $rowNB . '', 'Total Percent Q1');
$teamSheet->setCellValue('L' . $rowNB . '', 'Opp');
$teamSheet->setCellValue('M' . $rowNB . '', '1,1');
$teamSheet->setCellValue('N' . $rowNB . '', '1,9');
$teamSheet->setCellValue('O' . $rowNB . '', '5,5');
$teamSheet->setCellValue('P' . $rowNB . '', '9,1');
$teamSheet->setCellValue('Q' . $rowNB . '', 'PAT');
$teamSheet->setCellValue('R' . $rowNB . '', '9,9');
$teamSheet->setCellValue('S' . $rowNB . '', 'High');
$teamSheet->setCellValue('T' . $rowNB . '', 'Low');
$teamSheet->setCellValue('U' . $rowNB . '', 'Total Percent Q2');
$teamSheet->setCellValue('V' . $rowNB . '', 'Opp');
$teamSheet->setCellValue('W' . $rowNB . '', '1,1');
$teamSheet->setCellValue('X' . $rowNB . '', '1,9');
$teamSheet->setCellValue('Y' . $rowNB . '', '5,5');
$teamSheet->setCellValue('Z' . $rowNB . '', '9,1');
$teamSheet->setCellValue('AA' . $rowNB . '', 'PAT');
$teamSheet->setCellValue('AB' . $rowNB . '', '9,9');
$teamSheet->setCellValue('AC' . $rowNB . '', 'High');
$teamSheet->setCellValue('AD' . $rowNB . '', 'Low');
$teamSheet->setCellValue('AE' . $rowNB . '', 'Total Percent Q3');
if ($j > 3) {
$teamSheet->setCellValue('AF' . $rowNB . '', 'Opp');
$teamSheet->setCellValue('AG' . $rowNB . '', '1,1');
$teamSheet->setCellValue('AH' . $rowNB . '', '1,9');
$teamSheet->setCellValue('AI' . $rowNB . '', '5,5');
$teamSheet->setCellValue('AJ' . $rowNB . '', '9,1');
$teamSheet->setCellValue('AK' . $rowNB . '', 'PAT');
$teamSheet->setCellValue('AL' . $rowNB . '', '9,9');
$teamSheet->setCellValue('AM' . $rowNB . '', 'High');
$teamSheet->setCellValue('AN' . $rowNB . '', 'Low');
$teamSheet->setCellValue('AO' . $rowNB . '', 'Total Percent Q4');
$teamSheet->setCellValue('AP' . $rowNB . '', 'Opp');
$teamSheet->setCellValue('AQ' . $rowNB . '', '1,1');
$teamSheet->setCellValue('AR' . $rowNB . '', '1,9');
$teamSheet->setCellValue('AS' . $rowNB . '', '5,5');
$teamSheet->setCellValue('AT' . $rowNB . '', '9,1');
$teamSheet->setCellValue('AU' . $rowNB . '', 'PAT');
$teamSheet->setCellValue('AV' . $rowNB . '', '9,9');
$teamSheet->setCellValue('AW' . $rowNB . '', 'High');
$teamSheet->setCellValue('AX' . $rowNB . '', 'Low');
$teamSheet->setCellValue('AY' . $rowNB . '', 'Total Percent Q5');
$teamSheet->setCellValue('AZ' . $rowNB . '', 'Opp');
$teamSheet->setCellValue('BA' . $rowNB . '', '1,1');
$teamSheet->setCellValue('BB' . $rowNB . '', '1,9');
$teamSheet->setCellValue('BC' . $rowNB . '', '5,5');
$teamSheet->setCellValue('BD' . $rowNB . '', '9,1');
$teamSheet->setCellValue('BE' . $rowNB . '', 'PAT');
$teamSheet->setCellValue('BF' . $rowNB . '', '9,9');
$teamSheet->setCellValue('BG' . $rowNB . '', 'High');
$teamSheet->setCellValue('BH' . $rowNB . '', 'Low');
$teamSheet->setCellValue('BI' . $rowNB . '', 'Total Percent Q6');
$teamSheet->setCellValue('BJ' . $rowNB . '', 'Opp');
$teamSheet->setCellValue('BK' . $rowNB . '', '1,1');
$teamSheet->setCellValue('BL' . $rowNB . '', '1,9');
$teamSheet->setCellValue('BM' . $rowNB . '', '5,5');
$teamSheet->setCellValue('BN' . $rowNB . '', '9,1');
$teamSheet->setCellValue('BO' . $rowNB . '', 'PAT');
$teamSheet->setCellValue('BP' . $rowNB . '', '9,9');
$teamSheet->setCellValue('BQ' . $rowNB . '', 'High');
$teamSheet->setCellValue('BR' . $rowNB . '', 'Low');
$teamSheet->setCellValue('BS' . $rowNB . '', 'Total Percent Q7');
$teamSheet->setCellValue('BT' . $rowNB . '', 'Opp');
$teamSheet->setCellValue('BU' . $rowNB . '', '1,1');
$teamSheet->setCellValue('BV' . $rowNB . '', '1,9');
$teamSheet->setCellValue('BW' . $rowNB . '', '5,5');
$teamSheet->setCellValue('BX' . $rowNB . '', '9,1');
$teamSheet->setCellValue('BY' . $rowNB . '', 'PAT');
$teamSheet->setCellValue('BZ' . $rowNB . '', '9,9');
$teamSheet->setCellValue('CA' . $rowNB . '', 'High');
$teamSheet->setCellValue('CB' . $rowNB . '', 'Low');
$teamSheet->setCellValue('CC' . $rowNB . '', 'Total Percent Q8');
}
$rowNB++;
if (isset($avarageROW['q1H'])) {
// $teamSheet->setCellValue('I' . $rowNB, array_sum($avarageROW['q1H']) / count($avarageROW['q1H']));
// $teamSheet->setCellValue('J' . $rowNB, array_sum($avarageROW['q1L']) / count($avarageROW['q1L']));
$teamSheet->setCellValue('I' . $rowNB, max($avarageROW['q1H']));
$teamSheet->setCellValue('J' . $rowNB, min($avarageROW['q1L']));
$teamSheet->setCellValue($reorderingArray['q1a1'] . $rowNB, array_sum($avarageROW['q1a1']) / count($avarageROW['q1a1']));
$teamSheet->setCellValue($reorderingArray['q1a2'] . $rowNB, array_sum($avarageROW['q1a2']) / count($avarageROW['q1a2']));
$teamSheet->setCellValue($reorderingArray['q1a3'] . $rowNB, array_sum($avarageROW['q1a3']) / count($avarageROW['q1a3']));
$teamSheet->setCellValue($reorderingArray['q1a4'] . $rowNB, array_sum($avarageROW['q1a4']) / count($avarageROW['q1a4']));
$teamSheet->setCellValue($reorderingArray['q1a5'] . $rowNB, array_sum($avarageROW['q1a5']) / count($avarageROW['q1a5']));
$teamSheet->setCellValue($reorderingArray['q1a6'] . $rowNB, array_sum($avarageROW['q1a6']) / count($avarageROW['q1a6']));
$teamSheet->setCellValue($reorderingArray['q1a7'] . $rowNB, array_sum($avarageROW['q1a7']) / count($avarageROW['q1a7']));
}
if (isset($avarageROW['q2H'])) {
// $teamSheet->setCellValue('S' . $rowNB, array_sum($avarageROW['q2H']) / count($avarageROW['q2H']));
// $teamSheet->setCellValue('T' . $rowNB, array_sum($avarageROW['q2L']) / count($avarageROW['q2L']));
$teamSheet->setCellValue('S' . $rowNB, max($avarageROW['q2H']));
$teamSheet->setCellValue('T' . $rowNB, min($avarageROW['q2L']));
$teamSheet->setCellValue($reorderingArray['q2a1'] . $rowNB, array_sum($avarageROW['q2a1']) / count($avarageROW['q2a1']));
$teamSheet->setCellValue($reorderingArray['q2a2'] . $rowNB, array_sum($avarageROW['q2a2']) / count($avarageROW['q2a2']));
$teamSheet->setCellValue($reorderingArray['q2a3'] . $rowNB, array_sum($avarageROW['q2a3']) / count($avarageROW['q2a3']));
$teamSheet->setCellValue($reorderingArray['q2a4'] . $rowNB, array_sum($avarageROW['q2a4']) / count($avarageROW['q2a4']));
$teamSheet->setCellValue($reorderingArray['q2a5'] . $rowNB, array_sum($avarageROW['q2a5']) / count($avarageROW['q2a5']));
$teamSheet->setCellValue($reorderingArray['q2a6'] . $rowNB, array_sum($avarageROW['q2a6']) / count($avarageROW['q2a6']));
$teamSheet->setCellValue($reorderingArray['q2a7'] . $rowNB, array_sum($avarageROW['q2a7']) / count($avarageROW['q2a7']));
}
if (isset($avarageROW['q3H'])) {
// $teamSheet->setCellValue('AC' . $rowNB, array_sum($avarageROW['q3H']) / count($avarageROW['q3H']));
// $teamSheet->setCellValue('AD' . $rowNB, array_sum($avarageROW['q3L']) / count($avarageROW['q3L']));
$teamSheet->setCellValue('AC' . $rowNB, max($avarageROW['q3H']));
$teamSheet->setCellValue('AD' . $rowNB, min($avarageROW['q3L']));
$teamSheet->setCellValue($reorderingArray['q3a1'] . $rowNB, array_sum($avarageROW['q3a1']) / count($avarageROW['q3a1']));
$teamSheet->setCellValue($reorderingArray['q3a2'] . $rowNB, array_sum($avarageROW['q3a2']) / count($avarageROW['q3a2']));
$teamSheet->setCellValue($reorderingArray['q3a3'] . $rowNB, array_sum($avarageROW['q3a3']) / count($avarageROW['q3a3']));
$teamSheet->setCellValue($reorderingArray['q3a4'] . $rowNB, array_sum($avarageROW['q3a4']) / count($avarageROW['q3a4']));
$teamSheet->setCellValue($reorderingArray['q3a5'] . $rowNB, array_sum($avarageROW['q3a5']) / count($avarageROW['q3a5']));
$teamSheet->setCellValue($reorderingArray['q3a6'] . $rowNB, array_sum($avarageROW['q3a6']) / count($avarageROW['q3a6']));
$teamSheet->setCellValue($reorderingArray['q3a7'] . $rowNB, array_sum($avarageROW['q3a7']) / count($avarageROW['q3a7']));
}
if (isset($avarageROW['q4H'])) {
// $teamSheet->setCellValue('AM' . $rowNB, array_sum($avarageROW['q4H']) / count($avarageROW['q4H']));
// $teamSheet->setCellValue('AN' . $rowNB, array_sum($avarageROW['q4L']) / count($avarageROW['q4L']));
$teamSheet->setCellValue('AM' . $rowNB, max($avarageROW['q4H']));
$teamSheet->setCellValue('AN' . $rowNB, min($avarageROW['q4L']));
$teamSheet->setCellValue($reorderingArray['q4a1'] . $rowNB, array_sum($avarageROW['q4a1']) / count($avarageROW['q4a1']));
$teamSheet->setCellValue($reorderingArray['q4a2'] . $rowNB, array_sum($avarageROW['q4a2']) / count($avarageROW['q4a2']));
$teamSheet->setCellValue($reorderingArray['q4a3'] . $rowNB, array_sum($avarageROW['q4a3']) / count($avarageROW['q4a3']));
$teamSheet->setCellValue($reorderingArray['q4a4'] . $rowNB, array_sum($avarageROW['q4a4']) / count($avarageROW['q4a4']));
$teamSheet->setCellValue($reorderingArray['q4a5'] . $rowNB, array_sum($avarageROW['q4a5']) / count($avarageROW['q4a5']));
$teamSheet->setCellValue($reorderingArray['q4a6'] . $rowNB, array_sum($avarageROW['q4a6']) / count($avarageROW['q4a6']));
$teamSheet->setCellValue($reorderingArray['q4a7'] . $rowNB, array_sum($avarageROW['q4a7']) / count($avarageROW['q4a7']));
}
if (isset($avarageROW['q5H'])) {
// $teamSheet->setCellValue('AW' . $rowNB, array_sum($avarageROW['q5H']) / count($avarageROW['q5H']));
// $teamSheet->setCellValue('AX' . $rowNB, array_sum($avarageROW['q5L']) / count($avarageROW['q5L']));
$teamSheet->setCellValue('AW' . $rowNB, max($avarageROW['q5H']));
$teamSheet->setCellValue('AX' . $rowNB, min($avarageROW['q5L']));
$teamSheet->setCellValue($reorderingArray['q5a1'] . $rowNB, array_sum($avarageROW['q5a1']) / count($avarageROW['q5a1']));
$teamSheet->setCellValue($reorderingArray['q5a2'] . $rowNB, array_sum($avarageROW['q5a2']) / count($avarageROW['q5a2']));
$teamSheet->setCellValue($reorderingArray['q5a3'] . $rowNB, array_sum($avarageROW['q5a3']) / count($avarageROW['q5a3']));
$teamSheet->setCellValue($reorderingArray['q5a4'] . $rowNB, array_sum($avarageROW['q5a4']) / count($avarageROW['q5a4']));
$teamSheet->setCellValue($reorderingArray['q5a5'] . $rowNB, array_sum($avarageROW['q5a5']) / count($avarageROW['q5a5']));
$teamSheet->setCellValue($reorderingArray['q5a6'] . $rowNB, array_sum($avarageROW['q5a6']) / count($avarageROW['q5a6']));
$teamSheet->setCellValue($reorderingArray['q5a7'] . $rowNB, array_sum($avarageROW['q5a7']) / count($avarageROW['q5a7']));
}
if (isset($avarageROW['q6H'])) {
// $teamSheet->setCellValue('BG' . $rowNB, array_sum($avarageROW['q6H']) / count($avarageROW['q6H']));
// $teamSheet->setCellValue('BH' . $rowNB, array_sum($avarageROW['q6L']) / count($avarageROW['q6L']));
$teamSheet->setCellValue('BG' . $rowNB, max($avarageROW['q6H']));
$teamSheet->setCellValue('BH' . $rowNB, min($avarageROW['q6L']));
$teamSheet->setCellValue($reorderingArray['q6a1'] . $rowNB, array_sum($avarageROW['q6a1']) / count($avarageROW['q6a1']));
$teamSheet->setCellValue($reorderingArray['q6a2'] . $rowNB, array_sum($avarageROW['q6a2']) / count($avarageROW['q6a2']));
$teamSheet->setCellValue($reorderingArray['q6a3'] . $rowNB, array_sum($avarageROW['q6a3']) / count($avarageROW['q6a3']));
$teamSheet->setCellValue($reorderingArray['q6a4'] . $rowNB, array_sum($avarageROW['q6a4']) / count($avarageROW['q6a4']));
$teamSheet->setCellValue($reorderingArray['q6a5'] . $rowNB, array_sum($avarageROW['q6a5']) / count($avarageROW['q6a5']));
$teamSheet->setCellValue($reorderingArray['q6a6'] . $rowNB, array_sum($avarageROW['q6a6']) / count($avarageROW['q6a6']));
$teamSheet->setCellValue($reorderingArray['q6a7'] . $rowNB, array_sum($avarageROW['q6a7']) / count($avarageROW['q6a7']));
}
if (isset($avarageROW['q7H'])) {
// $teamSheet->setCellValue('BQ' . $rowNB, array_sum($avarageROW['q7H']) / count($avarageROW['q7H']));
// $teamSheet->setCellValue('BR' . $rowNB, array_sum($avarageROW['q7L']) / count($avarageROW['q7L']));
$teamSheet->setCellValue('BQ' . $rowNB, max($avarageROW['q7H']));
$teamSheet->setCellValue('BR' . $rowNB, min($avarageROW['q7L']));
$teamSheet->setCellValue($reorderingArray['q7a1'] . $rowNB, array_sum($avarageROW['q7a1']) / count($avarageROW['q7a1']));
$teamSheet->setCellValue($reorderingArray['q7a2'] . $rowNB, array_sum($avarageROW['q7a2']) / count($avarageROW['q7a2']));
$teamSheet->setCellValue($reorderingArray['q7a3'] . $rowNB, array_sum($avarageROW['q7a3']) / count($avarageROW['q7a3']));
$teamSheet->setCellValue($reorderingArray['q7a4'] . $rowNB, array_sum($avarageROW['q7a4']) / count($avarageROW['q7a4']));
$teamSheet->setCellValue($reorderingArray['q7a5'] . $rowNB, array_sum($avarageROW['q7a5']) / count($avarageROW['q7a5']));
$teamSheet->setCellValue($reorderingArray['q7a6'] . $rowNB, array_sum($avarageROW['q7a6']) / count($avarageROW['q7a6']));
$teamSheet->setCellValue($reorderingArray['q7a7'] . $rowNB, array_sum($avarageROW['q7a7']) / count($avarageROW['q7a7']));
}
if (isset($avarageROW['q8H'])) {
// $teamSheet->setCellValue('CA' . $rowNB, array_sum($avarageROW['q8H']) / count($avarageROW['q8H']));
// $teamSheet->setCellValue('CB' . $rowNB, array_sum($avarageROW['q8L']) / count($avarageROW['q8L']));
$teamSheet->setCellValue('CA' . $rowNB, max($avarageROW['q8H']));
$teamSheet->setCellValue('CB' . $rowNB, min($avarageROW['q8L']));
$teamSheet->setCellValue($reorderingArray['q8a1'] . $rowNB, array_sum($avarageROW['q8a1']) / count($avarageROW['q8a1']));
$teamSheet->setCellValue($reorderingArray['q8a2'] . $rowNB, array_sum($avarageROW['q8a2']) / count($avarageROW['q8a2']));
$teamSheet->setCellValue($reorderingArray['q8a3'] . $rowNB, array_sum($avarageROW['q8a3']) / count($avarageROW['q8a3']));
$teamSheet->setCellValue($reorderingArray['q8a4'] . $rowNB, array_sum($avarageROW['q8a4']) / count($avarageROW['q8a4']));
$teamSheet->setCellValue($reorderingArray['q8a5'] . $rowNB, array_sum($avarageROW['q8a5']) / count($avarageROW['q8a5']));
$teamSheet->setCellValue($reorderingArray['q8a6'] . $rowNB, array_sum($avarageROW['q8a6']) / count($avarageROW['q8a6']));
$teamSheet->setCellValue($reorderingArray['q8a7'] . $rowNB, array_sum($avarageROW['q8a7']) / count($avarageROW['q8a7']));
}
return $teamSheet;
}
}