команда для генерации прайс-листов

GeneratePricelist.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;


/**********
 *  Формирование файлов - документация: https://phpspreadsheet.readthedocs.io
 **********/


class GeneratePricelist extends Command
{
    /**
     * type {dealer|partner|retail}, city - add stores contacts for this city, brand - add items only this brand
     * @var string
     */
    protected $signature = 'pricelist:generate {type} {city?} {brand?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Generate pricelist';


    public $styles = [
        'title' => [
            'font' => [
                'bold' => true,
                'size' => 16,
            ],
        ],
        'second_title' => [
            'font' => [
                'bold' => true,
                'size' => 13,
            ],
        ],
        'category_title' => [
            'font' => [
                'bold' => 'true',
            ],
        ],

    ];

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }


    /**** Набор цен в зависимости от типа прайса ****/
    private function getFieldsByType($type) {
        switch ($type) {
            case 'retail':
                return [
                    'cost4' => 'Розница',
                ];
            case 'partner':
                return [
                    'cost4' => 'Розница',
                    'cost8' => 'Партнер',
                ];
            case 'dealer':
                return [
                    'cost4' => 'Розница',
                    'cost2' => 'По карте',
                    'cost3' => 'Опт1',
                    'cost5' => 'Опт2',
                    'cost6' => 'Опт3',
                ];
            default:
                return [
                    'cost4' => 'Розница',
                ];
        }
    }


    /**
     * generate pricelists
     *
     * @return mixed
     */
    public function handle()
    {
        $type = $this->argument('type'); // тип прайса
        $city = ($this->argument('city')) ? \App\Models\City::where('slug', $this->argument('city'))->first() : \App\Models\City::first();
        $brand = ($this->argument('brand')) ? $this->argument('brand') : false;
        $add_header = (bool) !$brand;


        // добавить возможность убирать лого

        $fields = $this->getFieldsByType($type);

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

        /***** категории первого уровня - по ним формируется список листов *****/
        $firstCategories = \App\Models\Catalogue\Category::where('parent_id', 0)
                                                         ->where('title', '<>', '')
                                                         ->where('in_cat','>', 0)
                                                         ->where('price_sort', '>', 0)
                                                         ->orderBy('price_sort', 'asc')->get();

        foreach ($firstCategories as $category)
        {
            $col = 1;
            $row = ($add_header) ? 10 : 2; // if no header with logo, start from second row

            if ($brand) {
                $brand_count = \App\Models\Catalogue\Item::where('prod', $brand)
                                                         ->where('uri', 'like', $category->uri .'%')->count();
                if ($brand_count == 0)
                    continue;
            }

            $worksheetByCategory = $spreadsheet->createSheet();
            $worksheetByCategory->setTitle( $this->prepareCategoryTitle( $category->title ) );

            if ( $add_header ) {
                $worksheetByCategory->mergeCells('A1:B1');
                $worksheetByCategory->mergeCells('C1:E1');
                $worksheetByCategory->mergeCells('C2:E2');

                $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing->setName('Logo');
                $drawing->setDescription('Logo');
                $drawing->setPath(public_path('/theme/n23_new/i/price-logo.png'));
                $drawing->setHeight(60);
                $drawing->setCoordinates('A1');
                $drawing->setWorksheet($worksheetByCategory);


                /* ширина/высота некоторых колонок и строк */
                $worksheetByCategory->getRowDimension('1')->setRowHeight(50);

                $worksheetByCategory->setCellValue('C1', "Сеть магазинов видеонаблюдения «Наблюдатель»");
                $worksheetByCategory->setCellValue('C2', "Адреса магазинов {$city->t6}:");
                $worksheetByCategory->getStyle('C1')->applyFromArray($this->styles['title']);
                $worksheetByCategory->getStyle('C2')->applyFromArray($this->styles['second_title']);

                $this->fillHeader( $worksheetByCategory, $city );
            }

            $worksheetByCategory->getColumnDimension('A')->setWidth(20);
            $worksheetByCategory->getColumnDimension('B')->setWidth(30);
            $worksheetByCategory->getColumnDimension('C')->setWidth(20);
            $worksheetByCategory->getColumnDimension('D')->setWidth(20);
            $worksheetByCategory->getColumnDimension('E')->setWidth(50);
            $worksheetByCategory->getColumnDimension('F')->setWidth(25);
            $worksheetByCategory->getColumnDimension('G')->setWidth(20);
            $worksheetByCategory->getColumnDimension('H')->setWidth(20);
            $worksheetByCategory->getColumnDimension('I')->setWidth(20);
            $worksheetByCategory->getColumnDimension('J')->setWidth(20);



            /*******  доступные колонки цен  ********/
            $start_price_cols = 6;
            foreach ($fields as $key => $field) {
                $worksheetByCategory->setCellValue($this->getCellID(10, $start_price_cols++), $field);
            }

            /******  Вторичные категории - см. подзаголовки в списке товара  ******/
            $secondCategories = $category->getVisibleChildren();

            # заполнение подкатегорий и их товаров
            foreach($secondCategories as $sCategory)
            {
                if ($brand) { // если указан бренд, то выводить только те категории, в которых этот бренд присутствует
                    $brand_count = \App\Models\Catalogue\Item::where('prod', $brand)
                                                             ->where('uri', 'like', $sCategory->uri .'%')->count();
                    if ($brand_count == 0)
                        continue;
                }

                $worksheetByCategory->getStyle($this->getCellID($row, 5))->applyFromArray($this->styles['category_title']);
                $worksheetByCategory->setCellValue($this->getCellID($row++, 5), $sCategory->title);

                $items = $sCategory->allItems()->where('in_cat', 1)->orderBy('sort');

                if ($brand)
                    $items->where('prod', $brand);

                foreach ($items->get() as $item)
                {
                    // $col++; // image
                    $worksheetByCategory->getRowDimension($row)->setRowHeight(50);

                    if ($item->getFirstPicture()) {
                        if (file_exists(public_path('/img_cat/thumbs/' . $item->getFirstPicture()))) {
                            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                            $drawing->setName($item->prod);
                            $drawing->setDescription($item->title);
                            $drawing->setPath(public_path('/img_cat/thumbs/' . $item->getFirstPicture()));
                            $drawing->setHeight(50);
                            $drawing->setCoordinates($this->getCellID($row, $col));
                            $drawing->setWorksheet($worksheetByCategory);
                        }
                    }

                    $col++;

                    $this->fillCell($worksheetByCategory, $row, $col++, $category->title);
                    $this->fillCell($worksheetByCategory, $row, $col++, $item->category->title);
                    $this->fillCell($worksheetByCategory, $row, $col++, $item->prod);
                    $this->fillCell($worksheetByCategory, $row, $col++, $item->title );

                    foreach ($fields as $k => $title) { // остальные поля - цены
                        $this->fillCell($worksheetByCategory, $row, $col++, $item->$k );
                    }

                    $row++;
                    $col = 1;
                }
                $row++;

            }

            echo "  {$category->title} - added to price \n";
        }

        # удаление нулевого листа (пустого)
        $spreadsheet->removeSheetByIndex(0);

        echo "  saving.... \n";
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save( $this->path() );
        echo "  saved! \n";

    }


    private function path() 
    {
        if ($this->argument('type') == 'dealer') {
            return storage_path('/app/public/prices/dealer/pricelist.xlsx');
        } elseif ($this->argument('type') == 'partner') {
            return storage_path('/app/public/prices/partner/pricelist.xlsx');
        } elseif ($this->argument('type') == 'retail') {
            $city = $this->argument('city') ? $this->argument('city') : 'krasnodar';
            return storage_path('/app/public/prices/retail/pricelist-'.$city.'.xlsx');
        }


    }

    private function fillCell($worksheet, $row, $col, $value, $vertical = true) 
    {
        if ($vertical)
            $worksheet->getStyle($this->getCellID($row, $col))->getAlignment()
                      ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

        $worksheet->setCellValue($this->getCellID($row, $col), $value);

        return $this;
    }


    /**
     *  Заполнение шапки информацией о магазине/магазинах в данном городе
     **/
    private function fillHeader($sheet, $city) {
        $col = $start_col = 2;
        $row = $start_row = 3;
        $i = 0;

        $fields = [
            'title', 'phone', 'email',
        ];

        foreach ($city->branches as $branch) {
            foreach ($fields as $field)
                $sheet->setCellValue( $this->getCellID( $row, $col++ ), $branch->$field );

            if ($i == 3) {
                $col = $start_col = 6;
                $row = $start_row;
            } else {
                $col = $start_col;
                $row++;
            }

            $i++;
        }

    }



    private function getCellID($row, $col) {
        $cols = [
            1 => 'A',
            2 => 'B',
            3 => 'C',
            4 => 'D',
            5 => 'E',
            6 => 'F',
            7 => 'G',
            8 => 'H',
            9 => 'I',
            10 => 'J',
            11 => 'K',
            12 => 'L',
            13 => 'M',
            14 => 'N',
            15 => 'O',
            16 => 'P',
            17 => 'R',
        ];

       // foreach ($cols as $k => $v)
            //$col = str_replace($k, $v, $col);

        return $cols[$col].$row;
    }

    private function prepareCategoryTitle($title) {
        /*  обработка заголовков категорий для создания листов  */
        $replace = [
            'Корпуса для камер видеонаблюдения' => 'Корпуса',
            'Монтажные материалы и инструменты' => 'Монтажные материалы',
            'Приемо/передатчики видеосигнала' => 'Приемо-передатчики',
            'Разъемы/Переходники' => 'Разъемы и переходники',
        ];

        foreach ($replace as $k => $v)
            $title = str_replace($k, $v, $title);

        return $title;
    }

    private function getCities() {
        return \App\Models\City::all();
    }







}