Symfony Profiler

<?php
declare(strict_types=1);
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Auto-generated Migration: Please modify to your needs!
*/
final class Version20221130092833 extends AbstractMigration
{
public function getDescription(): string
{
return '';
}
public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('CREATE TABLE `supplier_product` (id INT AUTO_INCREMENT NOT NULL, supplier_id INT NOT NULL, reference VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, description LONGTEXT NOT NULL, image LONGTEXT DEFAULT NULL, brand VARCHAR(255) NOT NULL, taxonomy VARCHAR(255) NOT NULL, purchase_price INT NOT NULL, retail_price INT NOT NULL, shipping_costs INT NOT NULL, eco_tax INT NOT NULL, vat_rate DOUBLE PRECISION NOT NULL, stock INT NOT NULL, INDEX IDX_522F70B22ADD6D8C (supplier_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
$this->addSql('ALTER TABLE `supplier_product` ADD CONSTRAINT FK_522F70B22ADD6D8C FOREIGN KEY (supplier_id) REFERENCES company (id)');
$this->addSql('ALTER TABLE supplier_taxonomy ADD hash VARCHAR(255) NOT NULL');
$this->addSql('ALTER TABLE supplier_product ADD new TINYINT(1) NOT NULL');
$this->addSql('CREATE INDEX shop_product_reference_idx ON shop_product (reference)');
$this->addSql('CREATE INDEX supplier_product_reference_idx ON supplier_product (reference)');
$this->addSql('CREATE INDEX supplier_brand_name_idx ON supplier_brand (name)');
$this->addSql('CREATE INDEX supplier_taxonomy_hash_idx ON supplier_taxonomy (hash)');
$this->addSql('ALTER TABLE shop_product ADD image_url LONGTEXT DEFAULT NULL');
$this->addSql('ALTER TABLE `order` ADD comment VARCHAR(255)');
$this->addSql('ALTER TABLE order_detail ADD number_order VARCHAR(255)');
$this->addSql('
CREATE PROCEDURE import(IN param_supplier_id INT)
BEGIN
DELETE FROM supplier_product
WHERE reference IN (
SELECT reference
FROM shop_product
WHERE supplier_id = param_supplier_id
AND state = "unwanted"
);
UPDATE shop_product
SET last_state = state,
state = "unavailable",
updated_at=NOW()
WHERE supplier_id = param_supplier_id
AND reference NOT IN (
SELECT reference
FROM supplier_product
WHERE supplier_product.supplier_id = param_supplier_id
);
UPDATE supplier_product AS sup LEFT JOIN shop_product AS shp ON shp.`reference` = sup.`reference` AND shp.`supplier_id` = param_supplier_id
SET sup.`new` = 1
WHERE sup.`supplier_id` = param_supplier_id AND shp.`reference` is NULL;
INSERT INTO shop_product (image, image_url, supplier_id, updated_at, reference, name, description, state, states_history, stock, creation_date, shipping_mode)
SELECT "image.png", image, supplier_id, NOW(), reference, name, description, "new", "a:0:{}", stock, creation_date, shipping_mode
FROM supplier_product
WHERE supplier_id = param_supplier_id
AND `new` = 1;
UPDATE shop_product
INNER JOIN supplier_product sp ON (shop_product.supplier_id = sp.supplier_id AND shop_product.reference = sp.reference)
INNER JOIN supplier_brand sb ON (sp.brand = sb.name AND shop_product.supplier_id = sb.supplier_id)
INNER JOIN supplier_taxonomy st ON (sp.taxonomy = st.hash AND shop_product.supplier_id = st.supplier_id)
SET shop_product.supplier_brand_id = sb.id,
shop_product.brand_id = sb.brand_id,
shop_product.supplier_taxonomy_id = st.id,
shop_product.taxonomy_id = st.taxonomy_id
WHERE sp.`new` = 1 AND sp.supplier_id = param_supplier_id;
INSERT INTO shop_vat (name, rate)
SELECT DISTINCT CONCAT(vat_rate * 100, "%"), vat_rate
FROM supplier_product
WHERE supplier_id = param_supplier_id
AND vat_rate NOT IN (SELECT rate FROM shop_vat);
INSERT INTO shop_price (product_id, vat_id, created_at, purchase_price, retail_price, shipping_costs, eco_tax, extra_cost)
SELECT p.id, (SELECT shop_vat.id FROM shop_vat WHERE rate = sp.vat_rate), NOW(), sp.purchase_price, sp.retail_price, sp.shipping_costs, sp.eco_tax, 0
FROM shop_product AS p
INNER JOIN supplier_product sp ON (p.supplier_id = sp.supplier_id AND p.reference = sp.reference)
WHERE sp.`new` = 1
AND sp.supplier_id = param_supplier_id;
UPDATE shop_product AS p
INNER JOIN shop_price as c ON (c.product_id = p.id)
INNER JOIN supplier_product sp ON (p.supplier_id = sp.supplier_id AND p.reference = sp.reference)
SET p.current_price_id = c.id
WHERE sp.`new` = 1
AND sp.supplier_id = param_supplier_id;
DELETE FROM shop_product WHERE current_price_id IS NULL AND supplier_id = param_supplier_id;
UPDATE shop_product AS p
INNER JOIN supplier_product sp ON (p.reference = sp.reference AND p.supplier_id = sp.supplier_id)
SET p.stock = sp.stock
WHERE p.supplier_id = param_supplier_id;
UPDATE shop_product AS p
INNER JOIN supplier_product sp ON (p.reference = sp.reference AND p.supplier_id = sp.supplier_id)
SET p.shipping_mode = sp.shipping_mode
WHERE p.supplier_id = param_supplier_id;
UPDATE shop_product AS p
SET p.last_state = state,
p.state = "unavailable"
WHERE p.supplier_id = param_supplier_id
AND p.stock = 0
AND (p.state != "unwanted" OR p.state != "deleted");
UPDATE shop_product AS p
SET p.state = p.last_state,
p.last_state = "unavailable"
WHERE p.supplier_id = param_supplier_id
AND p.last_state = "unavailable"
AND p.stock > 0
AND (p.state != "unwanted" OR p.state != "deleted");
DELETE FROM supplier_product WHERE `new` = 1 AND supplier_id = param_supplier_id;
UPDATE shop_product AS p
INNER JOIN shop_price AS s ON p.current_price_id = s.id
INNER JOIN shop_vat AS sv ON s.vat_id = sv.id
INNER JOIN supplier_product sp ON (p.reference = sp.reference AND p.supplier_id = sp.supplier_id)
SET p.last_state = state,
p.state = "on_hold"
WHERE p.supplier_id = param_supplier_id
AND (p.state = "published" OR p.state = "on_hold" OR p.state = "new")
AND (
s.purchase_price <> sp.purchase_price
OR s.retail_price <> sp.retail_price
OR s.eco_tax <> sp.eco_tax
OR s.shipping_costs <> sp.shipping_costs
OR sv.rate <> sp.vat_rate
);
INSERT INTO shop_price (product_id, vat_id, created_at, purchase_price, retail_price, shipping_costs, eco_tax, extra_cost)
SELECT p.id, (SELECT shop_vat.id FROM shop_vat WHERE rate = sp.vat_rate), NOW(), sp.purchase_price, sp.retail_price, sp.shipping_costs, sp.eco_tax, 0
FROM shop_product AS p
INNER JOIN shop_price AS s ON p.current_price_id = s.id
INNER JOIN shop_vat AS sv ON s.vat_id = sv.id
INNER JOIN supplier_product sp ON (p.reference = sp.reference AND p.supplier_id = sp.supplier_id)
WHERE (p.state = "published" OR p.state = "on_hold" OR p.state = "new")
AND (
s.purchase_price <> sp.purchase_price
OR s.retail_price <> sp.retail_price
OR s.eco_tax <> sp.eco_tax
OR s.shipping_costs <> sp.shipping_costs
OR sv.rate <> sp.vat_rate
)
AND sp.supplier_id = param_supplier_id;
UPDATE shop_product AS p
INNER JOIN (
SELECT MAX(sp.id) AS price_id, p.id AS product_id
FROM shop_product AS p
INNER JOIN shop_price sp on p.id = sp.product_id
WHERE p.supplier_id = param_supplier_id
AND p.state = "on_hold"
GROUP BY p.id
) sp on p.id = sp.product_id
SET p.current_price_id = sp.price_id
WHERE p.supplier_id = param_supplier_id
AND p.state = "on_hold";
DELETE FROM supplier_product WHERE supplier_id = param_supplier_id;
END
');
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE `supplier_product` DROP FOREIGN KEY FK_522F70B22ADD6D8C');
$this->addSql('ALTER TABLE supplier_taxonomy DROP hash');
$this->addSql('ALTER TABLE `supplier_product` DROP new');
$this->addSql('DROP INDEX shop_product_reference_idx ON shop_product');
$this->addSql('DROP INDEX supplier_product_reference_idx ON supplier_product');
$this->addSql('DROP INDEX supplier_brand_name_idx ON supplier_brand');
$this->addSql('DROP INDEX supplier_taxonomy_hash_idx ON supplier_taxonomy');
$this->addSql('ALTER TABLE `shop_product` DROP image_url');
$this->addSql('ALTER TABLE `order` DROP comment');
$this->addSql('ALTER TABLE order_detail DROP number_order');
$this->addSql('DROP PROCEDURE IF EXISTS import');
$this->addSql('DROP PROCEDURE IF EXISTS images_imported');
}
}

migrations/Version20221130092833.php line 1