Skip to content

Instantly share code, notes, and snippets.

@zloadmin
Created August 10, 2018 11:14
Show Gist options
  • Save zloadmin/a67c5f2c374f4a2e44002787e3fbb446 to your computer and use it in GitHub Desktop.
Save zloadmin/a67c5f2c374f4a2e44002787e3fbb446 to your computer and use it in GitHub Desktop.
<?php
/**
* Created by PhpStorm.
* User: vladimir
* Date: 12/05/2018
* Time: 1:22 PM
*/
namespace App;
use Illuminate\Support\Facades\DB;
use Excel;
use PHPExcel_Cell;
use PHPExcel_Style_Border;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
class FlyingExcel
{
protected static $flying = [];
protected static $top = 0;
protected static $bottom = 0;
protected static $airlines = [];
protected static $regions = [];
public function __construct($flying)
{
self::$top = self::getTop($flying);
self::$bottom = self::getBottom($flying);
self::$airlines = self::getAirlines();
self::$regions = self::getRegions();
self::$flying = $flying;
}
protected static function getAirlines()
{
return DB::table('airlines')->where('airline_name', '!=', '')->where('airline_color', '!=', '')->orderBy('airline', 'desc')->pluck('airline_name', 'airline_color');
}
protected static function getRegions()
{
return DB::table('regions')->where('region_name', '!=', '')->where('region_color', '!=', '')->groupBy('region_name')->orderBy('region_name', 'asc')->pluck('region_name', 'region_color');
}
protected static function getTop($flying)
{
$max_array = [];
foreach ($flying as $hub => $items)
{
$max_array[$hub] = 0;
foreach ($items as $key => $item) {
if($key < 0) $max_array[$hub]++;
}
}
return $max_array ? max($max_array) : 0;
}
protected static function getBottom($flying)
{
$max_array = [];
foreach ($flying as $hub => $items)
{
$max_array[$hub] = 0;
foreach ($items as $key => $item) {
if($key > 0) $max_array[$hub]++;
}
}
return $max_array ? max($max_array) : 0;
}
/**
* @return string
*/
protected static function getRightColumn()
{
return PHPExcel_Cell::stringFromColumnIndex(count(self::$flying) + 1);
}
protected static function getNextRightColumn()
{
return PHPExcel_Cell::stringFromColumnIndex(count(self::$flying) + 2);
}
protected static function getRegionsCor($index)
{
return self::getRightColumn() . $index;
}
protected static function getColorSchemaIconCor($index)
{
return self::getRightColumn() . $index;
}
protected static function getColorSchemaTextCor($index)
{
return self::getNextRightColumn() . $index;
}
/**
* @param $sheet
* @param $col
* @param $item
*/
protected static function setBackground($sheet, $col, $color)
{
if (!empty($color)) {
$sheet->getStyle($col)->getFill()->applyFromArray(array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array(
'rgb' => substr($color, 1)
)
));
}
}
protected static function setFontColor($sheet, $col, $color)
{
$sheet->getStyle($col)->applyFromArray([
'font' => [
'color' => ['rgb' => $color]
]
]);
}
/**
* @param $hub
* @param $key
* @return int
*/
protected static function getFlyCor($hub, $key)
{
$hub_index = self::getHubIndex($hub);
$fly_row = self::getFlyRow($key);
return PHPExcel_Cell::stringFromColumnIndex($hub_index) . $fly_row;
}
/**
* @param $sheet
* @param $col
*/
protected static function addHTStyle($sheet, $col)
{
$sheet->getStyle($col)->applyFromArray(self::getArrayHubTimeStyle());
}
/**
* @param $sheet
* @param $col
* @param $hub
*/
protected static function addHTValue($sheet, $col, $hub)
{
$sheet->setCellValue($col, getHumansTimeFromMinutes($hub, 'G:i'));
}
protected static function addFlyValue($sheet, $cor, $value)
{
$sheet->setCellValue($cor, self::getFlyValue($value));
}
protected static function getFlyValue($value)
{
if(!empty($value['origin'])) return $value['origin'];
if(!empty($value['arrival'])) return $value['arrival'];
return '';
}
protected static function addFlyStyle($sheet, $col, $item)
{
$sheet->getStyle($col)->applyFromArray(self::getArrayFlyStyle());
if(isset($item['airline_color'])) {
self::setBackground($sheet, $col, $item['airline_color']);
$color_inverse = color_inverse($item['airline_color']);
self::setFontColor($sheet, $col, mb_strtoupper(substr($color_inverse, 1)));
}
if (!empty($item['region_color'])) {
$sheet->getStyle($col)->applyFromArray(
[
'borders' => [
'allborders' => [
'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
'color' => ['rgb' => mb_strtoupper(substr($item['region_color'], 1))]
]
]
]
);
}
}
protected static function sheetAddHubTimeLine($sheet)
{
$i = 0;
foreach (self::$flying as $hub => $fly)
{
$cor = self::getHTColName($i); $i++;
self::addHTValue($sheet, $cor, $hub);
self::addHTStyle($sheet, $cor);
}
}
static protected function sheetAddFlying($sheet)
{
foreach (self::$flying as $hub => $values)
{
foreach ($values as $key => $item) {
$cor = self::getFlyCor($hub, $key);
self::addFlyValue($sheet, $cor, $item);
self::addFlyStyle($sheet, $cor, $item);
}
}
}
static protected function sheetAddRegions($sheet)
{
$i = 2;
foreach (self::$regions as $region_color => $region_name)
{
$cor = self::getRegionsCor($i);
$sheet->setCellValue($cor, $region_name);
self::addRegionStyle($sheet, $cor, $region_color);
$i = $i + 2;
}
}
static protected function sheetAddColorSchema($sheet)
{
$i = 2;
foreach (self::$regions as $region_color => $region_name)
{
$cor_text = self::getColorSchemaTextCor($i);
$sheet->setCellValue($cor_text, $region_name);
$cor_icon = self::getColorSchemaIconCor($i);
self::addRegionStyle($sheet, $cor_icon, $region_color);
$sheet->setCellValue($cor_icon, " ");
$i = $i + 2;
}
foreach (self::$airlines as $airline_color => $airline_name)
{
$cor_text = self::getColorSchemaTextCor($i);
$sheet->setCellValue($cor_text, $airline_name);
$cor_icon = self::getColorSchemaIconCor($i);
self::setBackground($sheet, $cor_icon, $airline_color);
$i = $i + 2;
}
}
static protected function addRegionStyle($sheet, $cor, $region_color)
{
$sheet->getStyle($cor)->applyFromArray(
[
'borders' => [
'allborders' => [
'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
'color' => ['rgb' => mb_strtoupper(substr($region_color, 1))]
]
]
]
);
}
static protected function sheetAddAirlines($sheet)
{
$i = self::getAirlinesIndex();
foreach (self::$airlines as $airline_color => $airline_name)
{
$cor = self::getAirlineCor($i);
$sheet->setCellValue($cor, $airline_name);
self::setBackground($sheet, $cor, $airline_color);
$i = $i - 2;
}
}
static protected function getAirlinesIndex()
{
return self::$top + 1 + self::$bottom;
}
static protected function getAirlineCor($index)
{
return self::getRightColumn() . $index;
}
static function getCoordinates($fly, $hub)
{
$row = self::getRowByHub($hub);
}
static function getHubIndex($needle_hub)
{
$i = 0;
foreach (self::$flying as $hub => $fly)
{
if($hub == $needle_hub) return $i;
$i++;
}
}
protected static function getFlyRow($key)
{
if($key < 0) {
return self::$top - abs($key) + 1;
}
return self::$top + $key + 1;
}
/**
* @param $i
* @return string
*/
protected static function getHTColName($i)
{
return PHPExcel_Cell::stringFromColumnIndex($i) . (self::$top + 1);
}
protected static function getArrayHubTimeStyle()
{
return [
'font' => [
'size' => 10,
'name' => 'Arial'
],
'borders' => [
'left' => ['style' => PHPExcel_Style_Border::BORDER_NONE],
'right' => ['style' => PHPExcel_Style_Border::BORDER_NONE],
'top' => ['style' => PHPExcel_Style_Border::BORDER_THIN],
'bottom' => ['style' => PHPExcel_Style_Border::BORDER_THIN],
],
'alignment' => [
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
]
];
}
protected static function getArrayFlyStyle()
{
$array = [];
$array['font'] = [ 'size' => 10, 'name' => 'Arial'];
$array['alignment'] = ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER];
return $array;
}
public function download()
{
return Excel::create('FlyFile', function ($excel) {
$excel->sheet('FlySheet', function ($sheet) {
if(count(self::$flying) > 0) {
self::sheetAddHubTimeLine($sheet);
self::sheetAddFlying($sheet);
// self::sheetAddRegions($sheet);
// self::sheetAddAirlines($sheet);
self::sheetAddColorSchema($sheet);
}
});
})->download('xlsx');
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment