FlyingExcel.php

<?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');
    }

}