<?php
declare(strict_types=1);
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
final class Version25010701_AddProductImageExCsv extends AbstractMigration
{
public function getDescription(): string
{
return 'Import product image ex data from CSV';
}
private function loadCsvData(): array
{
// CSVファイルのパスを指定
$csvPath = __DIR__ . '/Data/senri_web_data.csv';
if (!file_exists($csvPath)) {
throw new \RuntimeException('CSV file not found: ' . $csvPath);
}
$data = [];
if (($handle = fopen($csvPath, 'r')) !== false) {
// ヘッダー行をスキップ
fgetcsv($handle);
while (($row = fgetcsv($handle)) !== false) {
// CSVの列順序に合わせて配列にマッピング
$data[] = [
'product_id' => $row[0],
'large_image' => $row[1],
'middle_image' => $row[2],
'small_image' => $row[3],
'display_description' => $row[4],
'accessibility_text' => $row[5],
'data_type_no' => $row[6],
];
}
fclose($handle);
}
return $data;
}
public function up(Schema $schema): void
{
try {
// まずテーブルをTRUNCATE
$this->addSql('SET FOREIGN_KEY_CHECKS = 0');
$this->addSql('TRUNCATE TABLE dtb_product_image_ex');
$this->addSql('SET FOREIGN_KEY_CHECKS = 1');
$images = $this->loadCsvData();
// 存在する商品IDを取得
$existingProductIds = $this->connection->fetchFirstColumn('SELECT id FROM dtb_product');
foreach ($images as $image) {
// 商品が存在する場合のみ挿入
if (in_array($image['product_id'], $existingProductIds)) {
$this->addSql(
'INSERT INTO dtb_product_image_ex (
product_id,
large_image,
middle_image,
small_image,
display_description,
accessibility_text,
data_type_no,
create_date,
update_date,
discriminator_type
) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), ?)',
[
$image['product_id'],
$image['large_image'],
$image['middle_image'],
$image['small_image'],
$image['display_description'],
$image['accessibility_text'],
$image['data_type_no'],
'productimage'
]
);
} else {
$this->write(sprintf('Skipping product_id %s: Product does not exist', $image['product_id']));
}
}
} catch (\Exception $e) {
$this->write('Migration failed: ' . $e->getMessage());
throw $e;
}
}
public function down(Schema $schema): void
{
// 追加したすべてのデータを削除
$this->addSql('TRUNCATE TABLE dtb_product_image_ex');
}
}