<?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 Version20230413070310 extends AbstractMigration
{
public function getDescription(): string
{
return '';
}
public function up(Schema $schema): void
{
//QUOTATION
$this->addSql("CREATE PROCEDURE ProcessQuotations()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE customerQuotationsId INT;
DECLARE line1 VARCHAR(255);
DECLARE city VARCHAR(255);
DECLARE zip VARCHAR(255);
DECLARE country VARCHAR(255);
DECLARE details VARCHAR(255);
DECLARE newUuid CHAR(36);
DECLARE newAddressId INT;
DECLARE cur CURSOR FOR
SELECT
id,
CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.address')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.address')) END as line1,
CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.city')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.city')) END as city,
CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.zip')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.zip')) END as zip,
COALESCE(JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.country')), 'Unknown') as country,
CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.district')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(delivery_address, '$.district')) END as details,
UUID() AS new_uuid
FROM customer_quotations
WHERE JSON_LENGTH(delivery_address) > 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO customerQuotationsId, line1, city, zip, country, details, newUuid;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO address (line1, city, zip, country, details, uuid)
VALUES (
NULLIF(line1, ''),
NULLIF(city, ''),
NULLIF(zip, ''),
NULLIF(country, ''),
NULLIF(details, ''),
newUuid
);
SET newAddressId = LAST_INSERT_ID();
UPDATE customer_quotations
SET address_id = newAddressId
WHERE id = customerQuotationsId;
END LOOP;
CLOSE cur;
END;");
$this->addSql("CALL ProcessQuotations();");
$this->addSql("DROP PROCEDURE ProcessQuotations");
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
}
}