<?php
namespace App\EventListener;
use Exception;
use Pimcore\Model\Element\ValidationException;
use Pimcore\Event\Model\ElementEventInterface;
use Pimcore\Event\Model\DataObjectEvent;
use Pimcore\Event\Model\AssetEvent;
use Pimcore\Db;
use Pimcore\Model\DataObject\ExcelUpload;
use Pimcore\Log\ApplicationLogger;
use App\Services\BackgroundProcessService;
use PhpOffice\PhpSpreadsheet\IOFactory;
class ExcelUploadListener
{
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 ExcelUpload) {
return;
}
$count = ExcelUpload::getList()->count();
if ($count >= 1) {
throw new ValidationException('We\'re not allowing to create object more than one of this class.');
}
}
}
public function onPreAssetAdd(AssetEvent $event)
{
if ($event instanceof AssetEvent) {
// $excelMimes = array('text/xls', 'text/xlsx', 'application/excel', 'application/vnd.msexcel', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// if (!in_array($event->getAsset()->getMimeType(), $excelMimes)) {
// $event->getAsset()->delete();
// throw new Exception('Please upload excel file.');
// }
// if ($event->getAsset()->getFileSize() > 10485760) {
// $event->getAsset()->delete();
// throw new Exception('Maximum file size should be 10MB');
// }
}
}
/**
* @param ElementEventInterface $event
*/
public function onPreUpdate(ElementEventInterface $event)
{
if ($event instanceof DataObjectEvent) {
$object = $event->getObject();
if ($object instanceof ExcelUpload) {
// Perform mandatory validation
$this->mandatoryValidation($object);
}
}
}
/**
* @param ElementEventInterface $event
*/
public function onPostUpdate(ElementEventInterface $event)
{
if ($event instanceof DataObjectEvent) {
$object = $event->getObject();
if (!$object instanceof ExcelUpload) {
return;
}
if (!empty($object->getExcelFile())) {
// Execute command
$command = 'bulk:enrichment';
$errorLog = $command . ' command is executed to enrich bulk data by Excel File.';
$this->backgroundProcessService->execute($command, $errorLog);
}
}
}
/**
* @param object $object
*/
public function mandatoryValidation($object)
{
if (empty($object->getExcelFile())) {
throw new ValidationException('Please upload enriched excel file.');
} else {
$excelMimes = array('text/xls', 'text/xlsx', 'application/excel', 'application/vnd.msexcel', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
if (!in_array($object->getExcelFile()->getMimeType(), $excelMimes)) {
$object->getExcelFile()->delete();
throw new ValidationException('Please upload excel file.');
}
if ($object->getExcelFile()->getFileSize() > 10485760) {
$object->getExcelFile()->delete();
throw new ValidationException('Maximum file size should be 10MB.');
}
$excelFile = $object->getExcelFile();
$filePath = $excelFile->getLocalFile();
$expectedColumns = [
"Name", "Part Number", "Mfg Part Number", "Brand", "Compatible Brands", "Category", "GRP Price", "MSRP",
"Core Price", "Channel", "Channel Price", "Distributor", "Vendor Price", "Store",
"Store GRP Price", "Store Msrp Price", "Store Core Price", "UPC", "ASIN", "PID", "CaliforniaProp65", "Competitor SKU", "Hazardous",
"Oversize", "Obsolete", "Substitute", "Substitute Part Number", "Saleable In",
"Is Returnable", "Description", "Long Description", "Height", "Width", "Length", "Weight",
"Box Height", "Box Width", "Box Length", "Spin Image Url", "Main Image Url",
"Additional Image Url", "Subscription Flag", "Subscription Term", "Required Shipping"
];
$actualColumns = $this->readExcelFile($filePath);
$normalizedActualColumns = array_map(fn($columnName) => trim(strtolower(preg_replace('/\(.*\)/', '', $columnName))), $actualColumns);
$normalizedExpectedColumns = array_map('strtolower', $expectedColumns);
$extraColumns = array_diff($normalizedActualColumns, $normalizedExpectedColumns);
$missingColumns = array_diff($normalizedExpectedColumns, $normalizedActualColumns);
if (count($actualColumns) !== count($expectedColumns) || !empty($extraColumns) || !empty($missingColumns)) {
$countMismatchMsg = (count($actualColumns) !== count($expectedColumns)) ? "Count of columns does not match. Expected: " . count($expectedColumns) . ", Actual: " . count($actualColumns) : '';
$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);
$excelData = $spreadsheet->getActiveSheet()->rangeToArray('A1:' . $spreadsheet->getActiveSheet()->getHighestColumn() . '1', NULL, TRUE, FALSE)[0];
return $excelData;
}
}