From d901764964598e4c64b7957d157fa4a68f038708 Mon Sep 17 00:00:00 2001 From: soufiane Date: Mon, 30 Oct 2023 16:22:35 +0100 Subject: [PATCH] wip #5475 @12:00 # Conflicts: # app/Http/Controllers/Admin/Operations/FluidbookPublication/StatsOperation.php --- .../FluidbookPublication/StatsOperation.php | 161 +++--------------- .../views/fluidbook_stats/stats.blade.php | 4 + 2 files changed, 24 insertions(+), 141 deletions(-) diff --git a/app/Http/Controllers/Admin/Operations/FluidbookPublication/StatsOperation.php b/app/Http/Controllers/Admin/Operations/FluidbookPublication/StatsOperation.php index 5eaa9ff11..d326226db 100644 --- a/app/Http/Controllers/Admin/Operations/FluidbookPublication/StatsOperation.php +++ b/app/Http/Controllers/Admin/Operations/FluidbookPublication/StatsOperation.php @@ -5,26 +5,19 @@ namespace App\Http\Controllers\Admin\Operations\FluidbookPublication; use App\Fluidbook\Stats; use App\Http\Middleware\CheckIfAdmin; use App\Models\FluidbookPublication; -use App\Models\FluidbookTranslate; -use App\Models\ToolboxTranslate; use Carbon\Carbon; use Carbon\CarbonInterface; use Cubist\Matomo\Reporting; use Illuminate\Support\Facades\Route; use NumberFormatter; use voku\helper\HtmlDomParser; +use Cubist\Excel\Excel; use Cubist\Util\Files\Files; use Cubist\Util\Str; use PhpOffice\PhpSpreadsheet\Exception; use Psr\Container\ContainerExceptionInterface; use Psr\Container\NotFoundExceptionInterface; -use PhpOffice\PhpSpreadsheet\Spreadsheet; -use PhpOffice\PhpSpreadsheet\Style\Alignment; -use PhpOffice\PhpSpreadsheet\Style\NumberFormat; -use PhpOffice\PhpSpreadsheet\Writer\Xlsx; - - // __('!! Statistiques') trait StatsOperation { @@ -41,7 +34,7 @@ trait StatsOperation // API testing tool (intended for superadmins only) Route::get($segment . '/stats/API/{id?}', $controller . '@statsAPI'); - // Route to export datas to Excel + // Route::post($segment . '/stats/{fluidbook_id}_{hash}/{date?}/{period_override?}/generate', $controller . '@generateExcel') ->name('generateexcel'); } @@ -92,143 +85,29 @@ trait StatsOperation public function generateExcel($fluidbook_id, $hash, $date = null, $period_override = null) { $url = route('stats', compact('fluidbook_id', 'hash', 'date', 'period_override')); - $dateForFilename = str_replace('-','',str_replace(',','_', $date)); - $name = "stats_".$fluidbook_id."_".$dateForFilename; - - $locale = backpack_user()->locale; - $acceptLang = [ - "fr" => "fr-FR,fr;q=0.9", - "en" => "en-US,en;q=0.5" - ]; - - $html = $this->translateHtml($url,$acceptLang[$locale]); - $html = HtmlDomParser::str_get_html($html); - - $names = [...$html->find('h2'), ...$html->find('h3')]; - - $tables = $html->find('table'); - $tmpfile = Files::tempnam() . '.xlsx'; - $this->Excel_($tables, $names, null, $tmpfile); - return response()->download($tmpfile, $name . '.xlsx')->deleteFileAfterSend(true); - die(); - } + $name = "excel example"; + $safename = Str::slug($name); + $html = HtmlDomParser::file_get_html($url); + $tr = $html->find('table', 0)->find('tr'); + $excelData = []; + $keys = []; + $values = []; - /** - * @param $tables array - * @param $sheetnames array - * @param $head array|null - * @param $output string|null - * @param $width int|null - * @return Spreadsheet - * @throws \PhpOffice\PhpSpreadsheet\Exception - */ - public function Excel_($tables, $sheetnames, $head = null, $output = null, $width = null) - { - $columns = 0; - - $excel = new Spreadsheet(); - $excel->getDefaultStyle() - ->getNumberFormat() - ->setFormatCode( - NumberFormat::FORMAT_TEXT - ); - - foreach ($tables as $key => $table) { - $sheet = $key === 0 ? $excel->getActiveSheet() : $excel->createSheet(); - $sheetname = strip_tags($sheetnames[$key]->getAttribute('data-title-excel')); - $sheetname = \Illuminate\Support\Str::limit($sheetname, 28, '...'); - $sheet->setTitle($sheetname); - - $tr = $table->find('tr'); - $theads = $table->find('th'); - $head = []; - $datas = []; - - if($theads) { - foreach ($theads as $thead) { - $head[] = $thead->text; - } - } - - foreach ($tr as $k => $lines) { - foreach ($lines->find('td') as $td) { - $text = $td->text; - if($td->getAttribute('data-flag')) { - $text = $td->getAttribute('data-flag'); - } - - if($td->getAttribute('sorttable_customkey')) { - $text = str_replace(',', ', ',$td->getAttribute('sorttable_customkey')); - } - - if($td->getAttribute('data-type-media')) { - $text = $td->getAttribute('data-type-media') . ' : ' . $text ; - } - - $datas[$k][] = trim(preg_replace('/\s\s+/', ' ', $text)); - } - } - - $line = 1; - if (!is_null($head) && sizeof($head) > 0) { - $c = 0; - foreach ($head as $label) { - $c++; //must be positive value - $columns = max($columns, $c); - $cell = $sheet->getCellByColumnAndRow($c, $line); - $cell->setValue($label); - $style = $sheet->getStyleByColumnAndRow($c, $line); - $style->getFont()->setBold(true); - $style->getAlignment()->setVertical(Alignment::VERTICAL_CENTER); - } - $line++; - } - - foreach ($datas as $l) { - $c = 1; - foreach ($l as $v) { - $columns = max($columns, $c); - $sheet->getCellByColumnAndRow($c, $line)->setValue($v); - $style = $sheet->getStyleByColumnAndRow($c, $line); - $style->getAlignment()->setVertical(Alignment::VERTICAL_CENTER); - $style->getAlignment()->setWrapText(true); - $c++; - } - $line++; - } - - for ($i = 0; $i <= $columns; $i++) { - if (null === $width || !isset($width[$i]) || null === $width[$i] || $width[$i] == 'auto') { - $sheet->getColumnDimensionByColumn($i)->setAutoSize(true); - } else { - $sheet->getColumnDimensionByColumn($i)->setAutoSize(false)->setWidth($width[$i]); - } - } - } - if (null !== $output) { - self::_save($excel, $output); + foreach ($tr as $lines) { + $keys[] = $lines->find('td',0)->text; + $values[] = trim(preg_replace('/\s\s+/', ' ', $lines->find('td',1)->text)); } - return $excel; - } + $excelData[] = $keys; + $excelData[] = $values; - protected static function _save($excel, $output) - { - $writer = new Xlsx($excel); - $writer->save($output); - } + //dump($excelData); + //die(); - protected function translateHtml($url,$lang) { - $ch = curl_init(); - curl_setopt($ch, CURLOPT_URL,$url); - curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); - $headers = [ - 'Accept-Language: '.$lang, - ]; - curl_setopt($ch, CURLOPT_HTTPHEADER, $headers); - $html = curl_exec ($ch); - curl_close ($ch); - return $html; + $tmpfile = Files::tempnam() . '.xlsx'; + Excel::fromArray('name', $excelData, null, $tmpfile); + return response()->download($tmpfile, $safename . '.xlsx')->deleteFileAfterSend(true); + die(); } } diff --git a/resources/views/fluidbook_stats/stats.blade.php b/resources/views/fluidbook_stats/stats.blade.php index 7ef241665..3bd5b58d5 100644 --- a/resources/views/fluidbook_stats/stats.blade.php +++ b/resources/views/fluidbook_stats/stats.blade.php @@ -37,6 +37,10 @@

{{ __('Statistiques') }}

+
$start_date.','.$end_date, 'period_override'=>$period]) }}" method="POST"> + @csrf + +
-- 2.39.5