strprfcrm/app/Services/ImportService.php

<?php

namespace App\Services;

use App\Models\Product;
use Exception;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ImportService
{
    const HEADERS_TO_FIELDS = [
        "Фото" => '',
        "Наименование по ТЗ"            => 'name_tz',
        "Тип по ТЗ"                     => 'type_tz',
        "№ по номенкл."                 => 'nomenclature_number',
        "Габаритные размеры"            => 'sizes',
        "Производитель"                 => 'manufacturer',
        "ед. изм."                      => 'unit',
        "Тип оборудования"              => 'type',
        "Статус цены"                   => 'price_status',
        "Цена поставки"                 => 'product_price',
        "Цена установки"                => 'installation_price',
        "Цена обслуживания"             => 'service_price',
        "Итого цена"                    => 'total_price',
        "Наименование производителя"    => 'manufacturer_name',
        "Артикул образца"               => 'article',
        "Примечание"                    => 'note',
    ];


    /**
     * @throws Exception
     */
    public function handle(string $path, int $year): void
    {
        $path = Storage::disk('upload')->path($path);
        $xls = IOFactory::createReaderForFile($path);
        $xls->setReadDataOnly(true);
        $sheet = $xls->load($path);

        $records = $sheet->setActiveSheetIndex(0)->toArray();

        $data = [];

        $err = [];
        if($this->checkHeaders($records[0])) {
            foreach ($records as $k => $record){
                if($k === 0) continue;
                Product::query()
                    ->updateOrCreate(['year' => $year, 'nomenclature_number' => $record[3]],
                    [
                        'name_tz'           => $record[1],
                        'type_tz'           => $record[2],
                        'sizes'             => $record[4],
                        'manufacturer'      => $record[5],
                        'unit'              => $record[6],
                        'type'              => $record[7],
                        'price_status'      => $record[8],
                        'product_price'     => $record[9],
                        'installation_price'=> $record[10],
                        'service_price'     => $record[11],
                        'total_price'       => $record[12],
                        'manufacturer_name' => $record[13],
                        'article'           => $record[14],
                        'note'              => $record[15],
                    ]);
            }
        } else {
            throw new Exception('Ошибка заголовков файла!');
        }
    }


    protected function checkHeaders(array $headers): bool
    {
        return $this->getHeaders() == $headers;
    }


    /**
     * @return array
     */
    protected function getHeaders(): array
    {
        return array_keys(self::HEADERS_TO_FIELDS);
    }
}