migrations/Version20230427075207.php line 1

Open in your IDE?
  1. <?php
  3. declare(strict_types=1);
  5. namespace DoctrineMigrations;
  7. use Doctrine\DBAL\Schema\Schema;
  8. use Doctrine\Migrations\AbstractMigration;
  10. /**
  11.  * Auto-generated Migration: Please modify to your needs!
  12.  */
  13. final class Version20230427075207 extends AbstractMigration
  14. {
  15.     public function getDescription(): string
  16.     {
  17.         return '';
  18.     }
  20.     public function up(Schema $schema): void
  21.     {
  22.         // Fetch all customer IDs
  23.         $sql = "SELECT id FROM customer";
  24.         $customerIds = $this->connection->executeQuery($sql)->fetchAllAssociative();
  25.         // Process each customer
  26.         foreach ($customerIds as $customer) {
  27.             $customerId = $customer['id'];
  28.             // Update address_id for customer_quotations
  29.             $this->addSql('
  30.                 UPDATE customer_quotations q
  31.                 JOIN customer c ON q.customer_id = c.id
  32.                 JOIN address a1 ON q.address_id = a1.id
  33.                 JOIN address a2 ON c.address_id = a2.id
  34.                 SET q.address_id = a2.id
  35.                 WHERE
  36.                     c.id = :customer_id AND
  37.                     q.address_id <> c.address_id AND
  38.                     a1.line1 <=> a2.line1 AND
  39.                     a1.line2 <=> a2.line2 AND
  40.                     a1.line3 <=> a2.line3 AND
  41.                     a1.city <=> a2.city AND
  42.                     a1.zip <=> a2.zip AND
  43.                     a1.country <=> a2.country AND
  44.                     a1.details <=> a2.details AND
  45.                     a1.type <=> a2.type
  46.                 ', ['customer_id' => $customerId]);
  48.             // Update address_id for customer_quotations with the same address under each customer
  49.             $this->addSql('
  50.                 UPDATE customer_quotations q1
  51.                 JOIN customer_quotations q2 ON q1.customer_id = q2.customer_id
  52.                 JOIN address a1 ON q1.address_id = a1.id
  53.                 JOIN address a2 ON q2.address_id = a2.id
  54.                 SET q1.address_id = a2.id
  55.                 WHERE
  56.                     q1.customer_id = :customer_id AND
  57.                     q1.id <> q2.id AND
  58.                     q1.address_id <> q2.address_id AND
  59.                     a1.line1 <=> a2.line1 AND
  60.                     a1.line2 <=> a2.line2 AND
  61.                     a1.line3 <=> a2.line3 AND
  62.                     a1.city <=> a2.city AND
  63.                     a1.zip <=> a2.zip AND
  64.                     a1.country <=> a2.country AND
  65.                     a1.details <=> a2.details AND
  66.                     a1.type <=> a2.type
  67.             ', ['customer_id' => $customerId]);
  68.         }
  70.         // Fetch all manufacturer IDs
  71.         $sql = "SELECT id FROM manufacturer";
  72.         $manufacturerIds = $this->connection->executeQuery($sql)->fetchAllAssociative();
  74.         // Process each manufacturer
  75.         foreach ($manufacturerIds as $manufacturer) {
  76.             $manufacturerId = $manufacturer['id'];
  78.             // Update address_id for inquiries with the same address as their manufacturer
  79.             $this->addSql('
  80.             UPDATE price_inquiry p
  81.             JOIN manufacturer m ON p.manufacturer_id = m.id
  82.             JOIN address a1 ON p.address_id = a1.id
  83.             JOIN address a2 ON m.address_id = a2.id
  84.             SET p.address_id = a2.id
  85.             WHERE
  86.                 m.id = :manufacturer_id AND
  87.                 p.address_id <> m.address_id AND
  88.                 a1.line1 <=> a2.line1 AND
  89.                 a1.line2 <=> a2.line2 AND
  90.                 a1.line3 <=> a2.line3 AND
  91.                 a1.city <=> a2.city AND
  92.                 a1.zip <=> a2.zip AND
  93.                 a1.country <=> a2.country AND
  94.                 a1.details <=> a2.details AND
  95.                 a1.type <=> a2.type
  96.             ', ['manufacturer_id' => $manufacturerId]);
  98.             // Update address_id for inquiries with the same address under each manufacturer
  99.             $this->addSql('
  100.                 UPDATE price_inquiry p1
  101.                 JOIN price_inquiry p2 ON p1.manufacturer_id = p2.manufacturer_id
  102.                 JOIN address a1 ON p1.address_id = a1.id
  103.                 JOIN address a2 ON p2.address_id = a2.id
  104.                 SET p1.address_id = a2.id
  105.                 WHERE
  106.                     p1.manufacturer_id = :manufacturer_id AND
  107.                     p1.id <> p2.id AND
  108.                     p1.address_id <> p2.address_id AND
  109.                     a1.line1 <=> a2.line1 AND
  110.                     a1.line2 <=> a2.line2 AND
  111.                     a1.line3 <=> a2.line3 AND
  112.                     a1.city <=> a2.city AND
  113.                     a1.zip <=> a2.zip AND
  114.                     a1.country <=> a2.country AND
  115.                     a1.details <=> a2.details AND
  116.                     a1.type <=> a2.type
  117.             ', ['manufacturer_id' => $manufacturerId]);
  119.         }
  121.     }
  124.     public function down(Schema $schema): void
  125.     {
  127.     }
  128. }