app/DoctrineMigrations/Version25010701_AddProductImageExCsv.php line 1

Open in your IDE?
  1. <?php
  2. declare(strict_types=1);
  4. namespace DoctrineMigrations;
  6. use Doctrine\DBAL\Schema\Schema;
  7. use Doctrine\Migrations\AbstractMigration;
  9. final class Version25010900_AddProductImageExCsv extends AbstractMigration
  10. {
  11.     public function getDescription(): string
  12.     {
  13.         return 'Import product image ex data from CSV';
  14.     }
  16.     private function loadCsvData(): array
  17.     {
  18.         // CSVファイルのパスを指定
  19.         $csvPath = __DIR__ . '/Data/senri_web_data.csv';
  20.         
  21.         if (!file_exists($csvPath)) {
  22.             throw new \RuntimeException('CSV file not found: ' . $csvPath);
  23.         }
  25.         $data = [];
  26.         if (($handle = fopen($csvPath, 'r')) !== false) {
  27.             // ヘッダー行をスキップ
  28.             fgetcsv($handle);
  29.             
  30.             while (($row = fgetcsv($handle)) !== false) {
  31.                 // CSVの列順序に合わせて配列にマッピング
  32.                 $data[] = [
  33.                     'product_id' => $row[0],
  34.                     'large_image' => $row[1],
  35.                     'middle_image' => $row[2],
  36.                     'small_image' => $row[3],
  37.                     'display_description' => $row[4],
  38.                     'accessibility_text' => $row[5],
  39.                     'data_type_no' => $row[6],
  40.                 ];
  41.             }
  42.             fclose($handle);
  43.         }
  44.         
  45.         return $data;
  46.     }
  48.     public function up(Schema $schema): void
  49.     {
  50.       try {
  51.         // まずテーブルをTRUNCATE
  52.         $this->addSql('SET FOREIGN_KEY_CHECKS = 0');
  53.         $this->addSql('TRUNCATE TABLE dtb_product_image_ex');
  54.         $this->addSql('SET FOREIGN_KEY_CHECKS = 1');
  55.          
  56.         $images = $this->loadCsvData();
  57.          
  58.         // 存在する商品IDを取得
  59.         $existingProductIds = $this->connection->fetchFirstColumn('SELECT id FROM dtb_product');
  61.         foreach ($images as $image) {
  62.           // 商品が存在する場合のみ挿入
  63.           if (in_array($image['product_id'], $existingProductIds)) {
  64.             $this->addSql(
  65.               'INSERT INTO dtb_product_image_ex (
  66.                 product_id,
  67.                 large_image,
  68.                 middle_image,
  69.                 small_image,
  70.                 display_description,
  71.                 accessibility_text,
  72.                 data_type_no,
  73.                 create_date,
  74.                 update_date,
  75.                 discriminator_type
  76.               ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), ?)',
  77.               [
  78.                 $image['product_id'],
  79.                 $image['large_image'],
  80.                 $image['middle_image'],
  81.                 $image['small_image'],
  82.                 $image['display_description'],
  83.                 $image['accessibility_text'],
  84.                 $image['data_type_no'],
  85.                 'productimageex'
  86.               ]
  87.             );
  88.           } else {
  89.             $this->write(sprintf('Skipping product_id %s: Product does not exist', $image['product_id']));
  90.           }
  91.         }
  92.       } catch (\Exception $e) {
  93.         $this->write('Migration failed: ' . $e->getMessage());
  94.         throw $e;
  95.       }
  96.     }
  98.     public function down(Schema $schema): void
  99.     {
  100.         // 追加したすべてのデータを削除
  101.         $this->addSql('TRUNCATE TABLE dtb_product_image_ex');
  102.     }
  103. }