strprfcrm/app/Services/ImportService.php

<?php

namespace App\Services;

use App\Helpers\DateHelper;
use App\Models\Product;
use Exception;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

class ImportService
{
    const HEADERS_TO_FIELDS = [
        "Фото" => '',
        "Артикул образца"               => 'article',
        "Наименование по ТЗ"            => 'name_tz',
        "Тип по ТЗ"                     => 'type_tz',
        "№ по номенкл."                 => 'nomenclature_number',
        "Габаритные размеры"            => 'sizes',
        "Производитель"                 => 'manufacturer',
        "ед. изм."                      => 'unit',
        "Тип оборудования"              => 'type',
        "Цена поставки"                 => 'product_price',
        "Цена установки"                => 'installation_price',
        "Итого цена"                    => 'total_price',
        "Наименование производителя"    => 'manufacturer_name',
        "Примечание"                    => 'note',
        "Наименование по паспорту"      => 'passport_name',
        "Наименование в ведомости"      => 'statement_name',
        "Срок службы"                   => 'service_life',
        "Номер сертификата"             => 'certificate_number',
        "Дата сертификата"              => 'certificate_date',
        "Орган сертификации"            => 'certificate_issuer',
        "Вид сертификата"               => 'certificate_type',
        "Вес"                           => 'weight',
        "Объем"                         => 'volume',
        "Мест"                          => 'places',
    ];


    /**
     * @param string $path
     * @param int $year
     * @return void
     * @throws Exception
     */
    public function handle(string $path, int $year): void
    {
        $path = Storage::disk('upload')->path($path);

        $reader = new Xlsx();
        $spreadsheet = $reader->load($path);
        $sheet = $spreadsheet->getActiveSheet();

        $rowIterator = $sheet->getRowIterator();

        $headers = $this->rowToArray($rowIterator->current());



        if($this->checkHeaders($headers)) {
            foreach ($rowIterator as $row){
                $record = $this->rowToArray($row);
                if($record[0] === 'Фото') continue;
                if(!isset($record[4])) continue;
                $certDate = (int) $record[18];
                Product::query()
                    ->updateOrCreate(['year' => $year, 'nomenclature_number' => $record[4]],
                    [
                        'article'               => (string) $record[1],
                        'name_tz'               => (string) $record[2],
                        'type_tz'               => (string) $record[3],
                        'sizes'                 => (string) $record[5],
                        'manufacturer'          => (string) $record[6],
                        'unit'                  => (string) $record[7],
                        'type'                  => (string) $record[8],
                        'product_price'         => (float) $record[9],
                        'installation_price'    => (float) $record[10],
                        'total_price'           => (float) $record[11],
                        'manufacturer_name'     => (string) $record[12],
                        'note'                  => (string) $record[13],
                        'passport_name'         => (string) $record[14],
                        'statement_name'        => (string) $record[15],
                        'service_life'          => (int) $record[16],
                        'certificate_number'    => (string) $record[17],
                        'certificate_date'      => ($certDate > 0) ? DateHelper::excelDateToISODate($certDate) : null,
                        'certificate_issuer'    => (string) $record[19],
                        'certificate_type'      => (string) $record[20],
                        'weight'                => (float) $record[21],
                        'volume'                => (float) $record[22],
                        'places'                => (int) $record[23],
                    ]);
            }
        } else {
            throw new Exception('Ошибка заголовков файла!');
        }
    }

    protected function rowToArray($row): array
    {
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells, even if a cell value is not set.
        $row_content = [];
        foreach ($cellIterator as $cell) {
            $row_content[] = $cell->getValue();
        }

        return $row_content;
    }

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


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