migrations/Version20250511082611.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 Version20250511082611 extends AbstractMigration
  14. {
  15.     public function getDescription(): string
  16.     {
  17.         return '';
  18.     }
  20.     public function up(Schema $schema): void
  21.     {
  22.         $this->addSql('
  23.             UPDATE tourapp.travelpay_reckoning t
  24.             SET paymentdetails = sub.new_paymentdetails
  25.             FROM (
  26.                 SELECT
  27.                     id,
  28.                     json_agg(value) AS new_paymentdetails
  29.                 FROM tourapp.travelpay_reckoning,
  30.                      json_each(paymentdetails)
  31.                 WHERE json_typeof(paymentdetails) = \'object\'
  32.                 GROUP BY id
  33.             ) AS sub
  34.             WHERE t.id = sub.id;
  35.         ');
  37.         $this->addSql('
  38.             UPDATE tourapp.travelpay_reckoning t
  39.             SET tripdetails = sub.updated_tripdetails
  40.             FROM (
  41.                 SELECT
  42.                     id,
  43.                     json_agg(
  44.                         jsonb_set(
  45.                             elem,
  46.                             \'{participants_points}\',
  47.                             to_jsonb(
  48.                                 CASE
  49.                                     WHEN (elem->>\'participants_count\')::int > participantsthreshold THEN
  50.                                         CEIL(((elem->>\'participants_count\')::numeric - participantsthreshold) / participantsthreshold) * (elem->>\'points\')::int
  51.                                     ELSE 0
  52.                                 END
  53.                             )
  54.                         )
  55.                     ) AS updated_tripdetails
  56.                 FROM tourapp.travelpay_reckoning,
  57.                      jsonb_array_elements(tripdetails::jsonb) AS elem
  58.                 GROUP BY id
  59.             ) sub
  60.             WHERE t.id = sub.id;
  61.         ');
  63.         $this->addSql('
  64.             UPDATE tourapp.travelpay_reckoning t
  65.             SET tripdetails = sub.updated_tripdetails
  66.             FROM (
  67.                 SELECT
  68.                     id,
  69.                     json_agg(
  70.                         jsonb_set(
  71.                             elem,
  72.                             \'{participants_points}\',
  73.                             to_jsonb(
  74.                                 CASE
  75.                                     WHEN (elem->>\'participants_count\')::int > participantsthreshold THEN
  76.                                         CEIL(((elem->>\'participants_count\')::numeric - participantsthreshold) / participantsthreshold) * (elem->>\'points\')::int
  77.                                     ELSE 0
  78.                                 END
  79.                             )
  80.                         )
  81.                     ) AS updated_tripdetails
  82.                 FROM tourapp.travelpay_reckoning,
  83.                      jsonb_array_elements(tripdetails::jsonb) AS elem
  84.                 GROUP BY id
  85.             ) sub
  86.             WHERE t.id = sub.id;
  87.         ');
  89.         $this->addSql('
  90.             UPDATE tourapp.travelpay_reckoning
  91.             SET fixedamount=0
  92.             WHERE packageid !=1;
  93.         ');
  94.     }
  96.     public function down(Schema $schema): void
  97.     {}
  98. }