kupshop/bundles/External/HannahBundle/Util/Seller/ImportSellers.php

<?php

declare(strict_types=1);

namespace External\HannahBundle\Util\Seller;

use KupShop\KupShopBundle\Util\StringUtil;
use KupShop\SellerBundle\Utils\CountryProvider;
use Query\Operator;

class ImportSellers
{
    private const FILE = 'https://docs.google.com/spreadsheets/d/1OoIr7LmHCyiIbGn17UviAR90O7XiAGpNjCdk6sf1HtA/export?format=csv&gid=0';

    private $countryProvider;

    public function __construct(CountryProvider $countryProvider)
    {
        $this->countryProvider = $countryProvider;
    }

    public function process(): void
    {
        foreach ($this->loadCSV(self::FILE, 4, ',', false) as $item) {
            if (!($sellerId = $this->getSeller($item))) {
                continue;
            }

            $sellerData = sqlQueryBuilder()
                ->select('data')
                ->from('sellers')
                ->where(Operator::equals(['id' => $sellerId]))
                ->execute()->fetchOne();

            $sellerData = json_decode($sellerData ?: '', true) ?? [];

            $openingHours = $this->getOpeningHours($item);

            sqlQueryBuilder()
                ->update('sellers')
                ->set('position', 'POINT(:latitude, :longtitude)')
                ->addParameters(
                    [
                        'latitude' => str_replace(',', '.', $item[5]),
                        'longtitude' => str_replace(',', '.', $item[6]),
                    ]
                )
                ->directValues(
                    [
                        'title' => $item[3],
                        //                        'email' => $item['email'],
                        //                        'url' => $item['url-link'],
                        'phone' => $item[11],
                        'street' => $item[9],
                        'number' => $item[10],
                        'psc' => $item[8],
                        'city' => $item[7],
                        'country' => $this->getCountry($item['state'] ?? 'CZ'),
                        'type' => $this->getType($item[4]),
                        'data' => json_encode(array_merge($sellerData, ['opening_hours' => $openingHours])),
                    ]
                )
                ->where(Operator::equals(['id' => $sellerId]))
                ->execute();

            // update deliveries opening hours
            $qb = sqlQueryBuilder()
                ->select('dtd.id, dtd.data')
                ->from('sellers', 'sell')
                ->join('sell', 'stores', 's', 's.id = sell.id_store')
                ->join('s', 'delivery_type_delivery', 'dtd', 'dtd.id = s.id_delivery')
                ->where(Operator::equals(['sell.id' => $sellerId]));

            foreach ($qb->execute() as $delivery) {
                $data = json_decode($delivery['data'] ?? '', true) ?? [];
                $data['opening_hours'] = $openingHours;

                sqlQueryBuilder()
                    ->update('delivery_type_delivery')
                    ->directValues(
                        [
                            'data' => json_encode($data),
                        ]
                    )
                    ->where(Operator::equals(['id' => $delivery['id']]))
                    ->execute();
            }
        }
    }

    private function getOpeningHours(array $item): array
    {
        // opening hours
        $mapping = [
            1 => [12, 13],
            2 => [14, 15],
            3 => [16, 17],
            4 => [18, 19],
            5 => [20, 21],
            6 => [22, 23],
            7 => [24, 25],
        ];

        $openingHours = [];
        foreach ($mapping as $day => [$indexFrom, $indexTo]) {
            $openingHours[$day] = $this->getOpeningHour([$item[$indexFrom] ?? '', $item[$indexTo] ?? '']);
        }

        return $openingHours;
    }

    private function getOpeningHour(array $hours): array
    {
        foreach ($hours as &$hour) {
            if (mb_strtolower($hour) === 'zavřeno') {
                $hour = '';
            }
        }

        return $hours;
    }

    private function getCountry(string $countryName): string
    {
        if (!($country = $this->countryProvider->getCountryCodeByTranslatedName($countryName))) {
            return 'CZ';
        }

        return $country;
    }

    private function getType(string $type): string
    {
        switch ($type) {
            case 'Partnerské prodejny':
                return 'p';
            case 'Internetové obchody':
                return 'i';
            case 'Hannah prodejny':
            case 'Rock Point prodejny':
            case 'Naše prodejny':
                return 'h';
        }

        return 'p';
    }

    private function getSeller(array $item): ?int
    {
        if (empty($item[3]) || empty($item[0])) {
            return null;
        }

        $sellerId = sqlQueryBuilder()
            ->select('id')
            ->from('sellers')
            ->where(Operator::equals(['id_external' => $item[0]]))
            ->execute()->fetchColumn();

        if (!$sellerId) {
            $sellerId = sqlQueryBuilder()
                ->select('id')
                ->from('sellers')
                ->where(Operator::equals(['title' => $item[3]]))
                ->execute()->fetchColumn();

            if ($sellerId) {
                sqlQueryBuilder()
                    ->update('sellers')
                    ->directValues(
                        [
                            'id_external' => $item[0],
                        ]
                    )
                    ->where(Operator::equals(['id' => $sellerId]))
                    ->execute();

                return (int) $sellerId;
            }
        }

        if (!$sellerId) {
            $sellerId = sqlGetConnection()->transactional(function () use ($item) {
                sqlQueryBuilder()
                    ->insert('sellers')
                    ->directValues(
                        [
                            'id' => $item[0],
                            'id_external' => $item[0],
                            'title' => $item[3],
                        ]
                    )->execute();

                return sqlInsertId();
            });
        }

        return (int) $sellerId;
    }

    private function loadCSV(string $file, int $dataLine = 0, string $separator = ',', bool $withHeader = true): array
    {
        $file = fopen($file, 'r');
        $return = [];
        $header = [];
        $index = 0;
        $headerData = [];
        while (($data = fgetcsv($file, 0, $separator)) !== false) {
            if ($index++ === 0) {
                $header = [];
                foreach ($data as $key => $value) {
                    $header[] = StringUtil::slugify($value);
                }
                continue;
            }

            if ($index < $dataLine) {
                foreach ($header as $key => $value) {
                    if (!empty($value)) {
                        continue;
                    }

                    if (!empty($data[$key])) {
                        $header[$key] = StringUtil::slugify($data[$key]);
                    }
                }

                continue;
            }

            if ($withHeader) {
                $return[] = array_combine($header, $data);
            } else {
                $return[] = $data;
            }

            $index++;
        }

        return $return;
    }
}