316 lines
11 KiB
PHP
316 lines
11 KiB
PHP
<?php
|
||
declare(strict_types=1);
|
||
|
||
use PhpOffice\PhpSpreadsheet\IOFactory;
|
||
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
|
||
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;
|
||
|
||
$projectRoot = dirname(__DIR__, 6);
|
||
require $projectRoot . '/vendor/autoload.php';
|
||
|
||
$input = $argv[1] ?? '';
|
||
if ($input === '' || !is_file($input)) {
|
||
fwrite(STDERR, "Usage: php import-agent-xlsx.php /absolute/path/agents.xlsx\n");
|
||
exit(1);
|
||
}
|
||
|
||
$moduleRoot = dirname(__DIR__, 2);
|
||
$logoDir = $moduleRoot . '/view/frontend/web/images/agents';
|
||
$dataFile = $moduleRoot . '/view/frontend/web/data/agents.json';
|
||
|
||
if (!is_dir($logoDir) && !mkdir($logoDir, 0775, true) && !is_dir($logoDir)) {
|
||
throw new RuntimeException('Unable to create logo directory.');
|
||
}
|
||
if (!is_dir(dirname($dataFile)) && !mkdir(dirname($dataFile), 0775, true) && !is_dir(dirname($dataFile))) {
|
||
throw new RuntimeException('Unable to create data directory.');
|
||
}
|
||
|
||
foreach (glob($logoDir . '/*.png') ?: [] as $oldLogo) {
|
||
unlink($oldLogo);
|
||
}
|
||
|
||
$spreadsheet = IOFactory::load($input);
|
||
$sheet = $spreadsheet->getSheet(0);
|
||
$rows = $sheet->toArray(null, true, true, true);
|
||
|
||
$drawingsByRow = [];
|
||
foreach ($sheet->getDrawingCollection() as $drawing) {
|
||
$coordinate = $drawing->getCoordinates();
|
||
if (!preg_match('/^H(\d+)$/i', $coordinate, $matches)) {
|
||
continue;
|
||
}
|
||
|
||
$drawingsByRow[(int) $matches[1]][] = $drawing;
|
||
}
|
||
|
||
// Excel may anchor two logos in one tall H cell for two adjacent records (the
|
||
// BRACK/ALLTRON rows in the current source file). Move extra drawings to the
|
||
// next logo-less record while keeping their visual order.
|
||
foreach ($drawingsByRow as $rowNumber => $rowDrawings) {
|
||
if (count($rowDrawings) < 2) {
|
||
continue;
|
||
}
|
||
|
||
$drawingsByRow[$rowNumber] = [array_shift($rowDrawings)];
|
||
$targetRow = $rowNumber + 1;
|
||
foreach ($rowDrawings as $drawing) {
|
||
while (isset($drawingsByRow[$targetRow])) {
|
||
$targetRow++;
|
||
}
|
||
$drawingsByRow[$targetRow] = [$drawing];
|
||
$targetRow++;
|
||
}
|
||
}
|
||
ksort($drawingsByRow);
|
||
|
||
$agents = [];
|
||
$usedSlugs = [];
|
||
$unmappedCountries = [];
|
||
|
||
foreach ($rows as $rowNumber => $row) {
|
||
if ($rowNumber === 1 || trim((string) ($row['B'] ?? '')) === '') {
|
||
continue;
|
||
}
|
||
|
||
$countrySource = cleanText((string) ($row['C'] ?? ''));
|
||
$country = countryMetadata($countrySource);
|
||
if ($country === null) {
|
||
$unmappedCountries[$countrySource] = true;
|
||
continue;
|
||
}
|
||
|
||
$id = count($agents) + 1;
|
||
$mapPosition = mapPosition($country['code']);
|
||
$name = cleanText((string) ($row['B'] ?? $row['A'] ?? ''));
|
||
$baseSlug = slugify($name) ?: sprintf('agent-%03d', $id);
|
||
$slug = $baseSlug;
|
||
$suffix = 2;
|
||
while (isset($usedSlugs[$slug])) {
|
||
$slug = $baseSlug . '-' . $suffix++;
|
||
}
|
||
$usedSlugs[$slug] = true;
|
||
|
||
$logo = '';
|
||
if (isset($drawingsByRow[$rowNumber][0])) {
|
||
$logo = $slug . '.png';
|
||
saveDrawingAsPng($drawingsByRow[$rowNumber][0], $logoDir . '/' . $logo);
|
||
}
|
||
|
||
$agents[] = [
|
||
'id' => $id,
|
||
'slug' => $slug,
|
||
'system_name' => cleanText((string) ($row['A'] ?? '')),
|
||
'name' => $name,
|
||
'continent' => $country['continent'],
|
||
'country_code' => $country['code'],
|
||
'country' => $country['name'],
|
||
'website' => normalizeWebsite((string) ($row['E'] ?? '')),
|
||
'address' => cleanText((string) ($row['F'] ?? '')),
|
||
'logo' => $logo,
|
||
'note' => cleanText((string) ($row['I'] ?? '')),
|
||
'map_x' => $mapPosition['x'],
|
||
'map_y' => $mapPosition['y'],
|
||
'sort_order' => $id,
|
||
];
|
||
}
|
||
|
||
if ($unmappedCountries !== []) {
|
||
throw new RuntimeException('Unmapped countries: ' . implode(', ', array_keys($unmappedCountries)));
|
||
}
|
||
|
||
$payload = [
|
||
'version' => 1,
|
||
'source_updated_at' => date('Y-m-d', (int) filemtime($input)),
|
||
'continents' => [
|
||
['code' => 'north_america', 'label' => 'North America', 'x' => 21.5, 'y' => 34.5],
|
||
['code' => 'south_america', 'label' => 'South America', 'x' => 31.5, 'y' => 64.5],
|
||
['code' => 'europe', 'label' => 'Europe', 'x' => 49.5, 'y' => 31.5],
|
||
['code' => 'africa', 'label' => 'Africa', 'x' => 51.5, 'y' => 55.5],
|
||
['code' => 'asia', 'label' => 'Asia', 'x' => 69.0, 'y' => 37.5],
|
||
['code' => 'oceania', 'label' => 'Oceania', 'x' => 83.0, 'y' => 68.0],
|
||
['code' => 'antarctica', 'label' => 'Antarctica', 'x' => 51.0, 'y' => 91.0],
|
||
],
|
||
'agents' => $agents,
|
||
];
|
||
|
||
file_put_contents(
|
||
$dataFile,
|
||
json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR) . "\n"
|
||
);
|
||
|
||
printf("Imported %d agents and %d logos.\n", count($agents), count(array_filter(array_column($agents, 'logo'))));
|
||
|
||
function cleanText(string $value): string
|
||
{
|
||
$value = str_replace(["\xc2\xa0", "\r\n", "\r"], [' ', "\n", "\n"], $value);
|
||
$value = preg_replace('/[ \t]+/u', ' ', trim($value)) ?? trim($value);
|
||
return $value;
|
||
}
|
||
|
||
function normalizeWebsite(string $website): string
|
||
{
|
||
$website = cleanText($website);
|
||
if ($website === '') {
|
||
return '';
|
||
}
|
||
|
||
$website = preg_replace('#^http:/([^/])#i', 'http://$1', $website) ?? $website;
|
||
if (!preg_match('#^https?://#i', $website)) {
|
||
$website = 'https://' . $website;
|
||
}
|
||
|
||
return $website;
|
||
}
|
||
|
||
function slugify(string $value): string
|
||
{
|
||
$ascii = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value) ?: $value;
|
||
$slug = strtolower((string) preg_replace('/[^a-zA-Z0-9]+/', '-', $ascii));
|
||
return trim($slug, '-');
|
||
}
|
||
|
||
function countryMetadata(string $source): ?array
|
||
{
|
||
$key = mb_strtolower(trim($source));
|
||
$key = str_replace(['（', '）'], ['(', ')'], $key);
|
||
$key = preg_replace('/\s+/u', ' ', $key) ?? $key;
|
||
|
||
$countries = [
|
||
'argentina' => ['Argentina', 'AR', 'south_america'],
|
||
'australia' => ['Australia', 'AU', 'oceania'],
|
||
'austria' => ['Austria', 'AT', 'europe'],
|
||
'belgium' => ['Belgium', 'BE', 'europe'],
|
||
'brazil' => ['Brazil', 'BR', 'south_america'],
|
||
'bulgaria' => ['Bulgaria', 'BG', 'europe'],
|
||
'canada' => ['Canada', 'CA', 'north_america'],
|
||
'chile' => ['Chile', 'CL', 'south_america'],
|
||
'china(taiwan)' => ['China (Taiwan)', 'TW', 'asia'],
|
||
'china (taiwan)' => ['China (Taiwan)', 'TW', 'asia'],
|
||
'ecuador' => ['Ecuador', 'EC', 'south_america'],
|
||
'egypt' => ['Egypt', 'EG', 'africa'],
|
||
'estonia' => ['Estonia', 'EE', 'europe'],
|
||
'finland' => ['Finland', 'FI', 'europe'],
|
||
'france' => ['France', 'FR', 'europe'],
|
||
'germany' => ['Germany', 'DE', 'europe'],
|
||
'greece' => ['Greece', 'GR', 'europe'],
|
||
'guatemala' => ['Guatemala', 'GT', 'north_america'],
|
||
'india' => ['India', 'IN', 'asia'],
|
||
'indonesia' => ['Indonesia', 'ID', 'asia'],
|
||
'israel' => ['Israel', 'IL', 'asia'],
|
||
'korea' => ['Korea', 'KR', 'asia'],
|
||
'mexico' => ['Mexico', 'MX', 'north_america'],
|
||
'mongolia' => ['Mongolia', 'MN', 'asia'],
|
||
'netherlands' => ['Netherlands', 'NL', 'europe'],
|
||
'new zealand' => ['New Zealand', 'NZ', 'oceania'],
|
||
'norway' => ['Norway', 'NO', 'europe'],
|
||
'peru' => ['Peru', 'PE', 'south_america'],
|
||
'philippines' => ['Philippines', 'PH', 'asia'],
|
||
'poland' => ['Poland', 'PL', 'europe'],
|
||
'russia' => ['Russia', 'RU', 'europe'],
|
||
'saudia arabia' => ['Saudi Arabia', 'SA', 'asia'],
|
||
'singapore' => ['Singapore', 'SG', 'asia'],
|
||
'south africa' => ['South Africa', 'ZA', 'africa'],
|
||
'switzerland' => ['Switzerland', 'CH', 'europe'],
|
||
'thailand' => ['Thailand', 'TH', 'asia'],
|
||
'turkey' => ['Turkey', 'TR', 'asia'],
|
||
'uk' => ['United Kingdom', 'GB', 'europe'],
|
||
'united arab emirates' => ['United Arab Emirates', 'AE', 'asia'],
|
||
'united kingdom' => ['United Kingdom', 'GB', 'europe'],
|
||
'united states' => ['United States', 'US', 'north_america'],
|
||
'uzbekistan' => ['Uzbekistan', 'UZ', 'asia'],
|
||
'vietnam' => ['Vietnam', 'VN', 'asia'],
|
||
];
|
||
|
||
if (!isset($countries[$key])) {
|
||
return null;
|
||
}
|
||
|
||
return [
|
||
'name' => $countries[$key][0],
|
||
'code' => $countries[$key][1],
|
||
'continent' => $countries[$key][2],
|
||
];
|
||
}
|
||
|
||
function mapPosition(string $countryCode): array
|
||
{
|
||
// This SVG is an intentionally stylized projection. These positions are
|
||
// calibrated against its actual land shapes rather than raw longitude and latitude.
|
||
$svgOverrides = [
|
||
'ID' => ['x' => 81.5, 'y' => 66.0],
|
||
'SG' => ['x' => 77.0, 'y' => 60.5],
|
||
'TH' => ['x' => 76.2, 'y' => 52.0],
|
||
'TW' => ['x' => 81.6, 'y' => 45.0],
|
||
'VN' => ['x' => 77.6, 'y' => 52.5],
|
||
];
|
||
|
||
if (isset($svgOverrides[$countryCode])) {
|
||
return $svgOverrides[$countryCode];
|
||
}
|
||
|
||
$centroids = [
|
||
'AE' => [54.0, 24.0], 'AR' => [-64.0, -34.0], 'AT' => [14.0, 47.5],
|
||
'AU' => [134.0, -25.0], 'BE' => [4.5, 50.5], 'BG' => [25.0, 43.0],
|
||
'BR' => [-52.0, -10.0], 'CA' => [-106.0, 56.0], 'CH' => [8.0, 47.0],
|
||
'CL' => [-71.0, -33.0], 'DE' => [10.0, 51.0], 'EC' => [-78.0, -1.5],
|
||
'EE' => [25.5, 58.5], 'EG' => [30.0, 27.0], 'FI' => [26.0, 64.0],
|
||
'FR' => [2.0, 46.0], 'GB' => [-2.0, 54.0], 'GR' => [22.0, 39.0],
|
||
'GT' => [-90.2, 15.7], 'ID' => [118.0, -2.0], 'IL' => [35.0, 31.5],
|
||
'IN' => [79.0, 22.0], 'KR' => [128.0, 36.0], 'MN' => [104.0, 46.0],
|
||
'MX' => [-102.0, 23.0], 'NL' => [5.5, 52.0], 'NO' => [9.0, 61.0],
|
||
'NZ' => [172.0, -41.0], 'PE' => [-75.0, -9.0], 'PH' => [122.0, 13.0],
|
||
'PL' => [19.0, 52.0], 'RU' => [80.0, 55.0], 'SA' => [45.0, 24.0],
|
||
'SG' => [103.8, 1.3], 'TH' => [101.0, 15.0], 'TR' => [35.0, 39.0],
|
||
'TW' => [121.0, 24.0], 'US' => [-98.0, 39.0], 'UZ' => [64.0, 41.0],
|
||
'VN' => [108.0, 16.0], 'ZA' => [24.0, -30.0],
|
||
];
|
||
|
||
if (!isset($centroids[$countryCode])) {
|
||
return ['x' => null, 'y' => null];
|
||
}
|
||
|
||
[$longitude, $latitude] = $centroids[$countryCode];
|
||
$x = ($longitude + 180) / 360 * 100;
|
||
$y = (90 - $latitude) / 180 * 100 + max(0, $latitude) / 12;
|
||
|
||
return [
|
||
'x' => round(max(2, min(98, $x)), 3),
|
||
'y' => round(max(4, min(92, $y)), 3),
|
||
];
|
||
}
|
||
|
||
function saveDrawingAsPng(Drawing|MemoryDrawing $drawing, string $destination): void
|
||
{
|
||
if ($drawing instanceof MemoryDrawing) {
|
||
ob_start();
|
||
$renderingFunction = $drawing->getRenderingFunction();
|
||
$renderingFunction($drawing->getImageResource());
|
||
$bytes = (string) ob_get_clean();
|
||
} else {
|
||
$bytes = file_get_contents($drawing->getPath());
|
||
if ($bytes === false) {
|
||
throw new RuntimeException('Unable to read drawing: ' . $drawing->getName());
|
||
}
|
||
}
|
||
|
||
$source = imagecreatefromstring($bytes);
|
||
if ($source === false) {
|
||
throw new RuntimeException('Unsupported logo image: ' . $drawing->getName());
|
||
}
|
||
|
||
$width = imagesx($source);
|
||
$height = imagesy($source);
|
||
$scale = min(1, 560 / max(1, $width), 220 / max(1, $height));
|
||
$targetWidth = max(1, (int) round($width * $scale));
|
||
$targetHeight = max(1, (int) round($height * $scale));
|
||
$target = imagecreatetruecolor($targetWidth, $targetHeight);
|
||
imagealphablending($target, false);
|
||
imagesavealpha($target, true);
|
||
$transparent = imagecolorallocatealpha($target, 255, 255, 255, 127);
|
||
imagefill($target, 0, 0, $transparent);
|
||
imagecopyresampled($target, $source, 0, 0, 0, 0, $targetWidth, $targetHeight, $width, $height);
|
||
imagepng($target, $destination, 8);
|
||
imagedestroy($source);
|
||
imagedestroy($target);
|
||
}
|