<?php
namespace App\EventListener;
use Pimcore\Model\Element\ValidationException;
use Pimcore\Event\Model\ElementEventInterface;
use Pimcore\Event\Model\DataObjectEvent;
use Pimcore\Model\DataObject\OEMUpload;
use Pimcore\Log\ApplicationLogger;
use App\Services\BackgroundProcessService;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Pimcore\Model\Asset;
use Pimcore\Model\Asset\Folder;
class OEMUploadListener
{
public function __construct(private ApplicationLogger $applicationLogger, private BackgroundProcessService $backgroundProcessService)
{
}
/**
* @param ElementEventInterface $event
*/
public function onPreAdd(ElementEventInterface $event)
{
if ($event instanceof DataObjectEvent) {
$object = $event->getObject();
if (!$object instanceof OEMUpload) {
return;
}
$count = OEMUpload::getList()->count();
if ($count >= 1) {
throw new ValidationException('We\'re not allowing to create object more than one of this class.');
}
}
}
/**
* @param ElementEventInterface $event
*/
public function onPreUpdate(ElementEventInterface $event)
{
if ($event instanceof DataObjectEvent) {
$object = $event->getObject();
if ($object instanceof OEMUpload) {
// Perform mandatory validation
$this->mandatoryValidation($object);
}
}
}
/**
* @param ElementEventInterface $event
*/
public function onPostUpdate(ElementEventInterface $event)
{
if ($event instanceof DataObjectEvent) {
$object = $event->getObject();
if (!$object instanceof OEMUpload) {
return;
}
if ($object->getUploadType() == 'model') {
$command = 'bulk:model';
} else {
$command = 'bulk:oem';
}
// Execute command
$errorLog = $command . ' command is executed to enrich bulk data by Excel File.';
$this->backgroundProcessService->execute($command, $errorLog);
}
}
/**
* @param object $object
*/
public function mandatoryValidation($object)
{
$this->validateFile($object);
$excelFile = $object->getOEMFile();
$filePath = $excelFile->getLocalFile();
$path = "OEM Import";
$pathExists = \Pimcore\Model\DataObject\Service::pathExists($path);
if (empty($pathExists)) {
$folder = \Pimcore\Model\DataObject\Service::createFolderByPath($path);
} else {
$folder = \Pimcore\Model\DataObject\Service::getElementByPath('object', $path);
}
$object->setParentId($folder->getId());
$object->setOEMFile(null);
$object->save();
$expectedColumns = $this->getExpectedColumns($object->getUploadType());
$actualColumns = $this->readExcelFile($filePath);
$this->validateColumns($actualColumns, $expectedColumns);
}
private function validateFile($object)
{
$file = $object->getOEMFile();
if (empty($object->getOEMFile())) {
throw new ValidationException('Please upload enriched excel file.');
}
$type = $object->getUploadType();
if ($type == 'part') {
$path = '/OEM Imports/Part/';
} else {
$path = '/OEM Imports/Model/';
}
$destinationFolder = Asset\Folder::getByPath($path);
if (!$destinationFolder instanceof Asset\Folder) {
$destinationFolder = Asset\Service::createFolderByPath($path);
}
$filename = basename($file->getFullPath());
$newFileName = rand(1, 5) . $filename;
$file->setFileName($newFileName);
$file->setParent($destinationFolder);
$file->save();
$excelMimes = [
'text/xls',
'text/xlsx',
'application/excel',
'application/vnd.msexcel',
'application/vnd.ms-excel',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
];
$file = $object->getOEMFile();
if (!in_array($file->getMimeType(), $excelMimes)) {
$file->delete();
throw new ValidationException('Please upload excel file.');
}
//if ($file->getFileSize() > 10485760) { // 10MB
if ($file->getFileSize() > 50 * 1024 * 1024) { // 50MB
$file->delete();
//throw new ValidationException('Maximum file size should be 10MB.');
throw new ValidationException('Maximum file size should be 50MB.');
}
}
private function getExpectedColumns($uploadType)
{
if ($uploadType === 'model') {
return [
"Variant Barcode(Model ID)",
"Store Name",
"Shopify ID",
"Shopify Status",
"Body",
"Handle",
"Title",
"Other Images (If multiple, add | sign to separate values: http://image.com|http://image.com)",
"Image Alt Text",
"Main Category (If multiple, add | sign to separate values: Mixer|Washdryer)",
"Sub Category (If multiple, add | sign to separate values: Mixer|Washdryer)",
"Other Category (If multiple, add sign to separate values: Mixer|Washdryer)",
"Tags(If multiple, add sign to separate values: Mixer|Washdryer)",
"SEO Title",
"SEO Description",
"Google Shopping Age Group",
"Google Shopping Condition",
"Google Shopping CustomLabel0",
"Google Shopping CustomLabel1",
"Google Shopping CustomLabel2",
"Google Shopping CustomLabel3",
"Google Shopping CustomLabel4",
"Google Shopping CustomProduct",
"Google Shopping Gender",
"Google Shopping Google Product Category",
"Google Shopping MPN",
"Model SKU(Compatible Parts)(If multiple, add sign to separate values: Mixer|Washdryer)",
"Option Name Revision(If multiple, add sign to separate values: Mixer|Washdryer)",
"Option Value Revision(If multiple, add sign to separate values: Mixer|Washdryer)",
"Product Type",
"Right to Repair",
"Self Repair Manual",
"Service Manual",
"Model Specifications",
"Model Features",
"Specification Manual",
"Installation Manual",
"Energy Rating Manual",
"Ready For Upload"
];
} else {
return [
"Base PN",
"Store Name",
"Body",
"Cost Per Item",
"Variant Price",
"Variant Compare at Price",
"Is Price Update",
"Handle",
"Title",
"Main Image",
"Spin Image",
"Other Images (If multiple, add comma separated: http://image.com, http://image.com)",
"Image Alt Text",
"Main Category (If multiple, add comma separated: Mixer, Washdryer)",
"Sub Category (If multiple, add comma separated: Mixer, Washdryer)",
"Other Category (If multiple, add comma separated: Mixer, Washdryer)",
"Web Category",
"Tags",
"SEO Title",
"SEO Description",
"Equivalent Part BasePN",
"Fbt Part BasePN",
"Google Shopping Age Group",
"Google Shopping Condition",
"Google Shopping CustomLabel0",
"Google Shopping CustomLabel1",
"Google Shopping CustomLabel2",
"Google Shopping CustomLabel3",
"Google Shopping CustomLabel4",
"Google Shopping CustomProduct",
"Google Shopping Gender",
"Google Shopping Google Product Category",
"Google Shopping MPN",
"Compatible Models",
"Popular Parts",
"Skill Level",
"Supercede BasePN",
"Variant Barcode",
"Variant Fulfillment Service",
"Product Type",
"Product Quantity",
"Variant Inventory Policy",
"Variant Inventory Tracker",
"No Longer Available",
"Ready For Upload",
"Shopify ID",
"Shopify Status"
];
}
}
private function validateColumns(array $actualColumns, array $expectedColumns)
{
$filteredActualColumns = array_filter($actualColumns, function ($value) {
return !is_null($value) && $value !== '';
});
$normalizedActualColumns = array_map('strtolower', $filteredActualColumns);
$normalizedExpectedColumns = array_map('strtolower', $expectedColumns);
$extraColumns = array_diff($normalizedActualColumns, $normalizedExpectedColumns);
$missingColumns = array_diff($normalizedExpectedColumns, $normalizedActualColumns);
if (count($filteredActualColumns) !== count($expectedColumns) || !empty($extraColumns) || !empty($missingColumns)) {
$countMismatchMsg = (count($filteredActualColumns) !== count($expectedColumns))
? "Count of columns does not match. Expected: " . count($expectedColumns) . ", Actual: " . count($filteredActualColumns)
: '';
$extraColumnsMsg = (!empty($extraColumns) ? ", Extra Column Name: " . implode(', ', $extraColumns) . ',' : '');
$missingColumnsMsg = (!empty($missingColumns) ? ", Missing Column Name: " . implode(', ', $missingColumns) : '');
$logs = $countMismatchMsg . $extraColumnsMsg . $missingColumnsMsg;
$errorMessage = rtrim($logs, ',');
throw new ValidationException($errorMessage);
}
}
private function readExcelFile($filePath)
{
$spreadsheet = IOFactory::load($filePath);
return $spreadsheet->getActiveSheet()->rangeToArray('A1:' . $spreadsheet->getActiveSheet()->getHighestColumn() . '1', null, true, false)[0];
}
}