464 lines
11 KiB
PHP
464 lines
11 KiB
PHP
<?php
|
|
namespace App\Service;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date;
|
|
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
|
use PhpOffice\PhpSpreadsheet\Style\Fill;
|
|
use Carbon\Carbon;
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
|
|
|
|
class Informador
|
|
{
|
|
protected $title;
|
|
protected $columns;
|
|
protected $data;
|
|
protected $col_formats;
|
|
protected $totals;
|
|
|
|
public function __construct($title = '')
|
|
{
|
|
if ($title != '') {
|
|
$this->title = $title;
|
|
} else {
|
|
$this->title = 'Informe';
|
|
}
|
|
}
|
|
|
|
public function addColumn(string $title, $col = '')
|
|
{
|
|
if ($col == '') {
|
|
if ($this->columns == null) {
|
|
$i = 0;
|
|
} else{
|
|
$i = count($this->columns);
|
|
}
|
|
$col = $this->mapColumn($i);
|
|
}
|
|
|
|
if (isset($this->columns[$col])) {
|
|
$columns = [];
|
|
foreach ($this->columns as $c => $data) {
|
|
if (ord($c) < ord($col)) {
|
|
$columns[$c] = $data;
|
|
} elseif (ord($c) == ord($col)) {
|
|
$columns[$col] = $title;
|
|
} else {
|
|
$columns[chr(ord($c) + 1)] = $data;
|
|
}
|
|
}
|
|
$this->columns = $columns;
|
|
} else {
|
|
$this->columns[$col] = $title;
|
|
}
|
|
}
|
|
public function mapColumn($n)
|
|
{
|
|
$letters = range('A', 'Z');
|
|
$max = count($letters);
|
|
$r = $n / $max + 1;
|
|
$p = floor($r - 1) - 1;
|
|
$i = $n % $max;
|
|
if ($r >= 2) {
|
|
return $letters[$p] . $letters[$i];
|
|
}
|
|
return $letters[$i];
|
|
}
|
|
public function getColumn($col_title)
|
|
{
|
|
return array_search($col_title, $this->columns);
|
|
}
|
|
public function getColumnNumber($col)
|
|
{
|
|
$letters = range('A', 'Z');
|
|
if (strlen($col) > 1) {
|
|
$ls = str_split($col);
|
|
$n = 0;
|
|
foreach ($ls as $i => $l) {
|
|
$n += array_search($l, $letters) + $i * count($letters) + 1;
|
|
}
|
|
return $n;
|
|
}
|
|
return array_search($col, $letters);
|
|
}
|
|
public function addColumns(array $columns)
|
|
{
|
|
foreach ($columns as $column) {
|
|
$this->addColumn($column);
|
|
}
|
|
}
|
|
public function addData(int $row, $data, $col = '')
|
|
{
|
|
if (!isset($this->data[$row])) {
|
|
$this->data[$row] = [];
|
|
}
|
|
if ($col == '') {
|
|
$i = count($this->data[$row]);
|
|
$col = $this->mapColumn($i);
|
|
} elseif (ctype_print($col)) {
|
|
$col = $this->getColumn($col);
|
|
} else {
|
|
$col = $this->mapColumn($col);
|
|
}
|
|
|
|
if (isset($this->data[$row][$col])) {
|
|
$data_array = [];
|
|
foreach ($this->data[$row] as $c => $d) {
|
|
if (ord($c) < ord($col)) {
|
|
$data_array[$c] = $d;
|
|
} elseif (ord($c) == ord($col)) {
|
|
$data_array[$col] = $this->parseData($data);
|
|
} else {
|
|
$data_array[chr(ord($c) + 1)] = $d;
|
|
}
|
|
}
|
|
$this->data[$row] = $data_array;
|
|
} else {
|
|
$this->data[$row][$col] = $this->parseData($data);
|
|
}
|
|
}
|
|
protected function parseData($data)
|
|
{
|
|
if ($this->isDate($data)) {
|
|
// Date
|
|
return Date::PHPToExcel($data);
|
|
}
|
|
return $data;
|
|
}
|
|
protected function isDate($data)
|
|
{
|
|
try {
|
|
if (strpos($data, '-') === false) {
|
|
return false;
|
|
}
|
|
$fecha = explode('-', $data);
|
|
if (count($fecha) != 3) {
|
|
return false;
|
|
}
|
|
list($year, $month, $day) = $fecha;
|
|
if (strlen($year) == 4 and strlen($month) <= 2 and strlen($day) <= 2) {
|
|
return true;
|
|
}
|
|
if (ctype_digit($year) and ctype_digit($month) and ctype_digit($day)) {
|
|
$f = Carbon::parse($data, config('app.timezone'));
|
|
return true;
|
|
}
|
|
return false;
|
|
} catch(\Exception $e) {
|
|
return false;
|
|
}
|
|
return false;
|
|
}
|
|
public function addDataRow(int $row, array $data_array)
|
|
{
|
|
foreach ($data_array as $data) {
|
|
$this->addData($row, $data);
|
|
}
|
|
}
|
|
public function addDatas(array $data_matrix)
|
|
{
|
|
foreach ($data_matrix as $row => $data_array) {
|
|
$this->addDataRow($row, $data_array);
|
|
}
|
|
}
|
|
protected function findTitleColumn($title)
|
|
{
|
|
return array_search($title, $this->columns);
|
|
}
|
|
public function addFormat($format, $title = '')
|
|
{
|
|
$col = 'A';
|
|
if ($title == '') {
|
|
$i = count($this->col_formats);
|
|
$col = $this->mapColumn($i);
|
|
} else {
|
|
$col = $this->findTitleColumn($title);
|
|
}
|
|
|
|
if (isset($this->col_formats[$col])) {
|
|
$columns = [];
|
|
foreach ($this->col_formats as $c => $data) {
|
|
if (ord($c) < ord($col)) {
|
|
$columns[$c] = $data;
|
|
} elseif (ord($c) == ord($col)) {
|
|
$columns[$col] = $this->translateFormat($format);
|
|
} else {
|
|
$columns[chr(ord($c) + 1)] = $data;
|
|
}
|
|
}
|
|
$this->col_formats = $columns;
|
|
} else {
|
|
$this->col_formats[$col] = $this->translateFormat($format);
|
|
}
|
|
|
|
uksort($this->col_formats, function($ak, $bk) {
|
|
return strcmp($ak, $bk);
|
|
});
|
|
}
|
|
protected function translateFormat(array $format)
|
|
{
|
|
$translated = [];
|
|
foreach ($format as $category => $values) {
|
|
switch ($category) {
|
|
case 'numberFormat':
|
|
$data = $this->translateNumberFormat($values);
|
|
break;
|
|
case 'alignment':
|
|
$data = $this->translateAlignment($values);
|
|
break;
|
|
case 'font':
|
|
$data = $this->translateFont($values);
|
|
break;
|
|
case 'fill':
|
|
$data = $this->translateFill($values);
|
|
break;
|
|
case 'borders':
|
|
$data = $this->translateBorders($values);
|
|
break;
|
|
}
|
|
$translated[$category] = $data;
|
|
}
|
|
return $translated;
|
|
}
|
|
protected function translateNumberFormat(array $values)
|
|
{
|
|
$translated = [];
|
|
foreach ($values as $value) {
|
|
switch ($value) {
|
|
case 'short-date':
|
|
$translated['formatCode'] = 'dd-mm-yyyy';
|
|
break;
|
|
case 'date':
|
|
$translated['formatCode'] = 'dd mmmm, yyyy';
|
|
break;
|
|
case 'thousands':
|
|
$translated['formatCode'] = NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1;
|
|
break;
|
|
case 'pesos':
|
|
$translated['formatCode'] = '$ #.###';
|
|
break;
|
|
default:
|
|
$translated['formatCode'] = $value;
|
|
}
|
|
}
|
|
return $translated;
|
|
}
|
|
protected function translateAlignment(array $values)
|
|
{
|
|
$translated = [];
|
|
foreach ($values as $type => $value) {
|
|
switch ($type) {
|
|
case 'horizontal':
|
|
switch ($value) {
|
|
case 'center':
|
|
$new = Alignment::HORIZONTAL_CENTER;
|
|
break;
|
|
}
|
|
break;
|
|
case 'vertical':
|
|
switch ($value) {
|
|
case 'middle':
|
|
case 'center':
|
|
$new = Alignment::VERTICAL_CENTER;
|
|
}
|
|
default:
|
|
$new = $value;
|
|
break;
|
|
}
|
|
$translated[$type] = $new;
|
|
}
|
|
return $translated;
|
|
}
|
|
protected function translateFont(array $values)
|
|
{
|
|
$translated = [];
|
|
foreach ($values as $type => $value) {
|
|
switch ($type) {
|
|
case 'color':
|
|
$new = $this->translateColor($value);
|
|
break;
|
|
default:
|
|
$new = $value;
|
|
break;
|
|
}
|
|
$translated[$type] = $new;
|
|
}
|
|
return $translated;
|
|
}
|
|
protected function translateFill(array $values)
|
|
{
|
|
$translated = [];
|
|
foreach ($values as $type => $value) {
|
|
switch ($type) {
|
|
case 'fillType':
|
|
switch ($value) {
|
|
case 'solid':
|
|
$new = Fill::FILL_SOLID;
|
|
break;
|
|
default:
|
|
$new = $value;
|
|
break;
|
|
}
|
|
break;
|
|
case 'color':
|
|
$new = $this->translateColor($value);
|
|
break;
|
|
default:
|
|
$new = $value;
|
|
break;
|
|
}
|
|
$translated[$type] = $new;
|
|
}
|
|
return $translated;
|
|
}
|
|
protected function translateBorders(array $values)
|
|
{
|
|
$translated = [];
|
|
foreach ($format as $category => $value) {
|
|
switch ($category) {
|
|
case 'allBorders':
|
|
case 'left':
|
|
case 'right':
|
|
case 'top':
|
|
case 'bottom':
|
|
case 'diagonal':
|
|
case 'vertical':
|
|
case 'horizontal':
|
|
$data = $this->translateBorder($value);
|
|
break;
|
|
default:
|
|
$data = $value;
|
|
}
|
|
$translated[$category] = $data;
|
|
}
|
|
return $translated;
|
|
}
|
|
protected function translateBorder(array $values)
|
|
{
|
|
$translated = [];
|
|
foreach ($values as $type => $value) {
|
|
switch ($type) {
|
|
case 'color':
|
|
$new = $this->translateColor($value);
|
|
break;
|
|
default:
|
|
$new = $value;
|
|
break;
|
|
}
|
|
$translated[$type] = $new;
|
|
}
|
|
return $translated;
|
|
}
|
|
protected function translateColor($color)
|
|
{
|
|
$color_definitions = [
|
|
'red' => 'ff0000',
|
|
'light-red' => 'ffcccc',
|
|
'dark-red' => 'a00000'
|
|
];
|
|
if (is_array($color)) {
|
|
$t = dechex(255 * $color['transparency'] / 100);
|
|
$c = $color_definitions[$color['color']];
|
|
$hex = $t . $c;
|
|
return ['argb' => $hex];
|
|
}
|
|
return ['rgb' => $color_definitions[$color]];
|
|
}
|
|
public function addFormats(array $formats)
|
|
{
|
|
foreach ($formats as $title => $format) {
|
|
$this->addFormat($format, $title);
|
|
}
|
|
}
|
|
public function addTotal(string $col)
|
|
{
|
|
if (isset($this->columns[$col])) {
|
|
$sum = 0;
|
|
foreach ($this->data as $row => $data) {
|
|
$sum += $data[$col];
|
|
}
|
|
$this->totals[$col] = $sum;
|
|
}
|
|
}
|
|
public function addAverage(string $col)
|
|
{
|
|
$this->addTotal($col);
|
|
$this->totals[$col] /= count($this->data);
|
|
}
|
|
|
|
protected function fillData()
|
|
{
|
|
foreach ($this->data as $row => $data) {
|
|
$this->data[$row] = $this->fillAndSort($data);
|
|
}
|
|
if (count($this->totals) > 0) {
|
|
$this->totals = $this->fillAndSort($this->totals);
|
|
}
|
|
}
|
|
protected function fillAndSort(array $row)
|
|
{
|
|
foreach ($this->columns as $val) {
|
|
if (!isset($row[$val])) {
|
|
$row[$val] = '';
|
|
}
|
|
}
|
|
function sortArrayByArray(Array $array, Array $orderArray) {
|
|
$ordered = array();
|
|
foreach($orderArray as $key) {
|
|
if(array_key_exists($key, $array)) {
|
|
$ordered[$key] = $array[$key];
|
|
unset($array[$key]);
|
|
}
|
|
}
|
|
return $ordered + $array;
|
|
}
|
|
$row = sortArrayByArray($row, $this->columns);
|
|
|
|
return $row;
|
|
}
|
|
|
|
public function informe()
|
|
{
|
|
$ea = new Spreadsheet();
|
|
$ea->getProperties()->setCreator('Juan Pablo Vial B.');
|
|
$ea->getProperties()->setTitle($this->title);
|
|
$ea->getProperties()->setCompany('Incoviba S.A.');
|
|
|
|
$ews = $ea->getActiveSheet();
|
|
|
|
$ews->fromArray(array($this->columns), '', 'A1');
|
|
$ews->fromArray($this->data, '', 'A2');
|
|
$end = 2;
|
|
if ($this->totals != null and count($this->totals) > 0) {
|
|
$ews->fromArray($this->totals, '', 'A' . count($data) + 2);
|
|
$end = 3;
|
|
}
|
|
|
|
if ($this->col_formats != null and count($this->col_formats) > 0) {
|
|
foreach ($this->col_formats as $col => $format) {
|
|
$ews->getStyleByColumnAndRow($this->getColumnNumber($col), 2, $this->getColumnNumber($col), count($this->data) + $end)->applyFromArray($format);
|
|
}
|
|
}
|
|
|
|
for ($col = 0; $col < count($this->columns); $col ++) {
|
|
$ews->getColumnDimensionByColumn($col)->setAutoSize(true);
|
|
}
|
|
|
|
$ews->setAutoFilterByColumnAndRow(0, 1, count($this->columns) - 1, count($this->data));
|
|
|
|
$hoy = Carbon::now(config('app.timezone'));
|
|
$filename = str_replace('ñ', 'n', $this->title . ' - ' . $hoy->format('Y-m-d') . '.xlsx');
|
|
|
|
$writer = IOFactory::createWriter($ea, "Xlsx");
|
|
|
|
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");
|
|
header('Content-Transfer-Encoding: binary');
|
|
header('Content-Disposition: attachment; filename="' . $filename . '"');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
$writer->save('php://output');
|
|
}
|
|
}
|
|
?>
|