stemcell/app/code/Stemcell/Customer/Controller/Adminhtml/Lab/ImportPost.php

<?php

namespace Stemcell\Customer\Controller\Adminhtml\Lab;

use Magento\Backend\App\Action\Context;
use Magento\Customer\Model\Customer;
use Magento\Customer\Model\ResourceModel\Customer\CollectionFactory as CustomerCollectionFactory;
use \PhpOffice\PhpSpreadsheet\IOFactory;
use Magento\Framework\App\Action\HttpPostActionInterface;
use Magento\Framework\Controller\ResultFactory;
use Magento\Framework\Exception\FileSystemException;
use Magento\Framework\Filesystem\Driver\File;
use Magento\MediaStorage\Model\File\UploaderFactory;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\Filesystem;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Framework\App\Filesystem\DirectoryList;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
use Stemcell\Customer\Api\Data\LabInterface;
use Stemcell\Customer\Api\LabRepositoryInterface;
use Stemcell\Customer\Model\LabFactory;
use Stemcell\Customer\Model\ResourceModel\Salesperson\CollectionFactory;
use Stemcell\Customer\Model\Salesperson;

/**
 * 由于生产服务器是两台，只有media目录是两台服务器共享，因此将文件上传到media下面
 */
class ImportPost extends \Magento\Backend\App\Action implements HttpPostActionInterface
{
    private UploaderFactory $fileUploaderFactory;
    private Filesystem\Directory\WriteInterface $mediaDirectory;
    private StoreManagerInterface $storeManager;
    private Filesystem $fileSystem;
    private File $driverFile;
    private Customer $customer;
    private LabRepositoryInterface $labRepository;
    private LabInterface $lab;
    private LabFactory $labFactory;
    private CustomerCollectionFactory $customerCollection;
    private CollectionFactory $salespersonCollection;

    protected array $salespersons = [];

    protected array $customers = [];
    /**
     * @throws FileSystemException
     */
    public function __construct(
        Context $context,
        UploaderFactory $fileUploaderFactory,
        Filesystem $filesystem,
        StoreManagerInterface $storeManager,
        Filesystem $fileSystem,
        File $driverFile,
        CustomerCollectionFactory $customerCollection,
        LabRepositoryInterface $labRepository,
        LabFactory $labFactory,
        LabInterface $lab,
        CollectionFactory $salespersonCollection
    ) {
        parent::__construct($context);
        $this->fileUploaderFactory = $fileUploaderFactory;
        $this->mediaDirectory = $filesystem->getDirectoryWrite(DirectoryList::MEDIA);
        $this->storeManager = $storeManager;
        $this->fileSystem = $fileSystem;
        $this->driverFile = $driverFile;
        $this->customerCollection = $customerCollection;
        $this->labRepository = $labRepository;
        $this->labFactory = $labFactory;
        $this->lab = $lab;
        $this->salespersonCollection = $salespersonCollection;
    }

    protected function chineseEncoding(string $str)
    {
        return mb_convert_encoding($str, 'UTF-8', ['gb2312', 'gbk']);
    }


    protected function getSalesPersonId(string $name)
    {
        $name = trim($name);
        if (isset($this->salespersons[$name])) {
            return $this->salespersons[$name];
        }

        try {
            $result = $this->lab->getResource()->getConnection()
                ->query("select salesperson_id from salesperson where name = '$name' limit 1")
                ->fetch();
            if (is_array($result)) {
                $this->salespersons[$name] = $result['salesperson_id'];
                return $this->salespersons[$name];
            }
        } catch (\Zend_Db_Statement_Exception $e) {

        }

        return null;
    }

    protected function getCustomerId(string $name)
    {
        $name = trim($name);
        if (isset($this->customers[$name])) {
            return $this->customers[$name];
        }

        try {
            $result = $this->lab->getResource()->getConnection()
                ->query("select entity_id from customer_entity where `firstname` = '$name' limit 1")
                ->fetch();
            if (is_array($result)) {
                $this->customers[$name] = $result['entity_id'];
                return $this->customers[$name];
            }
        } catch (\Zend_Db_Statement_Exception $e) {
        }

        return null;
    }

    public function execute()
    {
        $jsonResult = $this->resultFactory->create(ResultFactory::TYPE_JSON);
        try {
            $fileUploader = $this->fileUploaderFactory->create([
                'fileId' => 'lab[import]'
            ]);
            $fileUploader->setAllowedExtensions(['xls', 'xlsx']);
            $fileUploader->setAllowRenameFiles(true);
            $fileUploader->setAllowCreateFolders(true);
            $fileUploader->setFilesDispersion(false);

            $dir = $this->mediaDirectory->getAbsolutePath(DirectoryList::VAR_IMPORT_EXPORT.'/lab');
            !is_dir($dir) && mkdir($dir, 0755, true);
            $data = $fileUploader->save($dir);
            /* @var $logger \Psr\Log\LoggerInterface */
            $logger = \Magento\Framework\App\ObjectManager::getInstance()->get(\Psr\Log\LoggerInterface::class);

            try {
                $spreadsheet = IOFactory::load($dir.'/'.$fileUploader->getUploadedFileName());
                $data = $spreadsheet->getActiveSheet()->toArray();
                array_shift($data);
                foreach ($data as $content) {
                    array_walk($content, function (&$value){
                        if (is_string($value)) {
                            $value = trim($value);
                        }
                    });
                    if (empty(array_filter($content))) {
                        continue;
                    }
                    try {
                        $content = array_slice($content, 0, 5);
                        $row = array_combine(['lab_code', 'booking_account', 'pi_sap_id', 'customer_name', 'salesperson'], $content);
                        
                        $column = 'lab_code, booking_account, pi_sap_id';
                        $duplicate = 'booking_account = new.booking_account, pi_sap_id = new.pi_sap_id';
                        $row['salesperson_id'] = $row['customer_id']= null;
                        if ($row['customer_name']) {
                            $customerId = $this->getCustomerId((string)$row['customer_name']);
                            if ($customerId) {
                                $column .= ',customer_id';
                                $duplicate  .= ', customer_id = new.customer_id';
                                $row['customer_id']  .= ', '. $customerId;
                            }

                            unset($row['customer_name']);

                        }
                        if ($row['salesperson']) {
                            /* @var $salesperson Salesperson */
                            $salespersonId = $this->getSalesPersonId((string)$row['salesperson']);

                            if ($salespersonId) {
                                $column .= ',salesperson_id';
                                $row['salesperson_id'] .= ', '.$salespersonId;
                                $duplicate  .= ', salesperson_id = new.salesperson_id';
                                //                            $this->lab->setData($row);
                            }
                        }
                        if ($row['lab_code']) {
                            $sql = "
                                INSERT INTO lab ($column)
                                VALUES ('{$row['lab_code']}', '{$row['booking_account']}', '{$row['pi_sap_id']}' {$row['customer_id']} {$row['salesperson_id']}) AS new
                                ON DUPLICATE KEY UPDATE
                                    $duplicate;
                                ";
                            $this->labFactory->create()->getResource()->getConnection()->query($sql);
                        }

                        $this->lab->unsetData();
                    } catch (FileSystemException | \Exception $e) {
                        $logger->critical('导入lab失败，原因：'.$e->getMessage(), ['data' => $row]);
                    }
                }
//                $logger->info('$this->customers', $this->customers);
//                $logger->info('$this->salespersons', $this->salespersons);
                $this->driverFile->deleteFile($dir.'/'.$fileUploader->getUploadedFileName());
            } catch (FileSystemException | Exception $exception) {
                $logger->debug($exception->getMessage());
            }
            return $jsonResult->setData($data);
        } catch (LocalizedException|Exception $error) {
            return $jsonResult->setData(['errorcode' => 0, 'error' => $error->getMessage()]);
        }
    }
}