From c39905b29b9f9674ef4f13b19cddfca36d5446eb Mon Sep 17 00:00:00 2001 From: Vincent Vanwaelscappel Date: Fri, 23 Apr 2021 20:58:08 +0200 Subject: [PATCH] wait #4406 @4 --- app/Models/FeedbackProcess.php | 159 ++++++++++++++++++++++++++++----- 1 file changed, 139 insertions(+), 20 deletions(-) diff --git a/app/Models/FeedbackProcess.php b/app/Models/FeedbackProcess.php index 3d1f515..09731f7 100644 --- a/app/Models/FeedbackProcess.php +++ b/app/Models/FeedbackProcess.php @@ -5,6 +5,7 @@ namespace App\Models; use Cubist\Util\Files\Files; use Cubist\Util\PHP; +use PhpOffice\PhpSpreadsheet\Cell\Cell; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; use PhpOffice\PhpSpreadsheet\Style\Fill; @@ -195,6 +196,35 @@ class FeedbackProcess return mb_strtolower(trim($sheet->getCellByColumnAndRow(1, 1)->getValue())); } + protected function _getHeadersRow($sheet) + { + $highestRow = $sheet->getHighestRow(); + for ($i = 1; $i <= $highestRow; $i++) { + if ($sheet->getCellByColumnAndRow(1, $i)->getValue() === 'Picture References') { + $headersRow = $i; + break; + } + } + return $headersRow; + } + + protected function _getMaxCol($sheet) + { + $hr = $this->_getHeadersRow($sheet); + $maxcol = Coordinate::columnIndexFromString($sheet->getHighestColumn()); + $max = 0; + $values = []; + for ($i = 15; $i <= $maxcol; $i++) { + $v = $sheet->getCellByColumnAndRow($i, $hr)->getValue(); + $values[] = $v; + if ($v === null || trim($v) === '') { + break; + } + $max = max($i, $max); + } + return $max; + } + /** * @param $templateSheet Worksheet * @param $feedbacks Worksheet[] @@ -204,15 +234,11 @@ class FeedbackProcess { // Find header row $highestRow = $templateSheet->getHighestRow(); - for ($i = 1; $i <= $highestRow; $i++) { - if ($templateSheet->getCellByColumnAndRow(1, $i)->getValue() === 'Picture References') { - $headersRow = $i; - break; - } - } + $headersRow = $this->_getHeadersRow($templateSheet); + $templateMaxCol = $this->_getMaxCol($templateSheet); // Add ranking column - $templateSheet->insertNewColumnBefore('O', 1); + $templateSheet->insertNewColumnBefore('O', 2); $h = $templateSheet->getCell('O' . $headersRow); // Style it $h->getStyle()->getFill()->setFillType(Fill::FILL_SOLID) @@ -220,34 +246,67 @@ class FeedbackProcess ->setARGB('5eba7d'); // Set the caption $h->setValue('Rank'); + // Score column + $h = $templateSheet->getCell('P' . $headersRow); + // Style it + $h->getStyle()->getFill()->setFillType(Fill::FILL_SOLID) + ->getStartColor() + ->setARGB('5eba7d'); + // Set the caption + $h->setValue('Global score'); // Get data from feedbacks - $startCol = 15; $eanCol = 4; $merged = []; $feedbackId = 0; foreach ($feedbacks as $feedback) { - $maxcol = Coordinate::columnIndexFromString($feedback->getHighestColumn()); + $hr = $this->_getHeadersRow($feedback); + $maxcol = $this->_getMaxCol($feedback); $maxrow = $feedback->getHighestRow(); - for ($j = $headersRow + 1; $j <= $maxrow; $j++) { + for ($j = $hr + 1; $j <= $maxrow; $j++) { $ean = trim($feedback->getCellByColumnAndRow($eanCol, $j)->getValue()); if (!isset($merged[$ean])) { $merged[$ean] = []; } for ($i = $startCol; $i <= $maxcol; $i++) { $v = $feedback->getCellByColumnAndRow($i, $j)->getValue(); - if (null === $v) { - continue; + if ($v === null) { + $v = ''; } $v = trim($v); - if (!$v) { - continue; - } $merged[$ean][$i - $startCol] = $v; } } } + + $offset = 0; + // Add score & rank local columns + for ($i = $startCol; $i <= $templateMaxCol; $i += 4) { + // Add ranking column + $colname = Coordinate::stringFromColumnIndex(2 + 3 + $i + $offset + 1); + $templateSheet->insertNewColumnBefore($colname, 2); + $h = $templateSheet->getCell($colname . $headersRow); + // Style it + $h->getStyle()->getFill()->setFillType(Fill::FILL_SOLID) + ->getStartColor() + ->setARGB('5eba7d'); + // Set the caption + $h->setValue('Rank'); + + $offset++; + + $colname = Coordinate::stringFromColumnIndex(2 + 3 + $i + $offset + 1); + $h = $templateSheet->getCell($colname . $headersRow); + // Style it + $h->getStyle()->getFill()->setFillType(Fill::FILL_SOLID) + ->getStartColor() + ->setARGB('5eba7d'); + // Set the caption + $h->setValue('Local score'); + $offset++; + } + $rankingData = []; // Put feedbacks is template foreach ($merged as $ean => $data) { @@ -264,15 +323,19 @@ class FeedbackProcess } $rankingData[$row] = []; // Put data on that row + $offset = 0; foreach ($data as $col => $value) { + $i = $col % 4; // Set the value - $templateSheet->setCellValueByColumnAndRow($startCol + 1 + $col, $row, $value); + $templateSheet->setCellValueByColumnAndRow($startCol + 2 + $col + $offset, $row, $value); // Gather data to make the ranking - if ($col % 4 === 0) { + if ($i === 0) { $k = count($rankingData[$row]); $rankingData[$row][$k] = (mb_strtoupper($value) === 'YES' ? 'Y' : 'N'); - } else if ($col % 2 === 0) { + } else if ($i == 2) { $rankingData[$row][$k] .= (mb_strtoupper($value) ?? 'C'); + } else if ($i === 3) { + $offset += 2; } } } @@ -289,20 +352,76 @@ class FeedbackProcess //Compute score by line $scores = []; + $detailScores = []; foreach ($rankingData as $row => $data) { $scores[$row] = 0; - foreach ($data as $rank) { + $detailScores[$row] = []; + + foreach ($data as $local => $rank) { $s = $coefs[$rank] ?? 0; + $detailScores[$row][$local] = $s; $scores[$row] += $s; } } + $locales = []; + foreach ($detailScores as $row => $localeScores) { + foreach ($localeScores as $locale => $s) { + if (!isset($locales[$locale])) { + $locales[$locale] = []; + } + $locales[$locale][$row] = $s; + } + } + + $localeRanks = []; + foreach ($locales as $locale => $data) { + $ranks = []; + $rank = 1; + $formerRank = 0; + $formerScore = 0; + arsort($data); + foreach ($data as $row => $score) { + if ($score === $formerScore) { + $orank = $formerRank; + } else { + $orank = $rank; + $formerScore = $score; + $formerRank = $rank; + } + $ranks[$row] = $orank; + $rank++; + } + $localeRanks[$locale] = $ranks; + } + // Sort score to define ranks arsort($scores); $rank = 1; + $formerScore = 0; + $formerRank = 0; foreach ($scores as $row => $score) { - $templateSheet->setCellValueByColumnAndRow($startCol, $row, $rank); + if ($score === $formerScore) { + $orank = $formerRank; + } else { + $orank = $rank; + $formerScore = $score; + $formerRank = $rank; + } + $templateSheet->setCellValueByColumnAndRow($startCol, $row, $orank); + $templateSheet->setCellValueByColumnAndRow($startCol + 1, $row, $score); $templateSheet->getStyleByColumnAndRow($startCol, $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER); + $templateSheet->getStyleByColumnAndRow($startCol + 1, $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER); + $offset = 0; + foreach ($detailScores[$row] as $locale => $detailScore) { + $col = $startCol + 1 + 5 + $offset; + $templateSheet->setCellValueByColumnAndRow($col, $row, $localeRanks[$locale][$row]); + $templateSheet->setCellValueByColumnAndRow($col + 1, $row, $detailScore); + $templateSheet->getStyleByColumnAndRow($col, $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER); + $templateSheet->getStyleByColumnAndRow($col + 1, $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER); + + $offset += 6; + } $rank++; } -- 2.39.5