migrations/Version20240905104814.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 Version20240905104814 extends AbstractMigration
  14.     {
  15.         public function getDescription(): string
  16.         {
  17.             return '';
  18.         }
  20.         public function up(Schema $schema): void
  21.         {
  22.             $this->addSql(
  23.                 'ALTER VIEW report_total_or_installments_count AS
  24.     select a.academic_year, a.title, count(a.id) as total
  25.     from (SELECT DISTINCT s.id    AS id,
  26.                           CASE
  27.                               WHEN i.title = \'U cjelosti\' THEN \'Uplata u cjelosti\'
  28.                               ELSE \'Rate\'
  29.                               END AS title,
  30.                           e.academic_year
  31.           FROM students s
  32.                    LEFT JOIN udg_fms.profiles p ON s.id = p.student_id
  33.                    LEFT JOIN enrollment_events e ON e.profile_id = p.id
  34.                    LEFT JOIN tuition_installments i ON e.id = i.enrollment_id
  35.           where e.academic_year is not null
  36.             and e.tuition_modality_template_id is not null) a
  37.     group by a.academic_year, a.title;
  38. '
  39.             );
  41.             $this->addSql(
  42.                 'ALTER VIEW report_total_or_installments_amount AS
  43.     select a.academic_year, a.title, sum(a.total) as total
  44.     from (SELECT DISTINCT e.academic_year,
  45.                           CASE
  46.                               WHEN t.title = \'U cjelosti\' THEN \'Uplata u cjelosti\'
  47.                               ELSE \'Rate\'
  48.                               END       AS title,
  49.                           sum(t.amount) as total
  50.           FROM students s
  51.                    LEFT JOIN udg_fms.profiles p ON s.id = p.student_id
  52.                    LEFT JOIN enrollment_events e ON e.profile_id = p.id
  53.                    left join tuition_installments t on t.enrollment_id = e.id
  54.           WHERE e.academic_year is not null
  55.             and e.tuition_modality_template_id is not null
  56.             and t.payed_on is not null
  57.           GROUP BY e.academic_year, title) a
  58.     group by a.academic_year, a.title;'
  59.             );
  61.         }
  63.         public function down(Schema $schema): void
  64.         {
  65.             $this->addSql(
  66.                 'ALTER VIEW report_total_or_installments_count AS
  67. select a.academic_year, a.title, count(a.id) as total
  68. from (SELECT DISTINCT s.id    AS id,
  69.                       CASE
  70.                           WHEN ti.title = \'Uplata u cjelosti\' THEN ti.title
  71.                           ELSE \'Rate\'
  72.                           END AS title,
  73.                       e.academic_year
  74.       FROM students s
  75.                LEFT JOIN udg_fms.profiles p ON s.id = p.student_id
  76.                LEFT JOIN enrollment_events e ON e.profile_id = p.id
  77.                LEFT JOIN tuition_modality_templates t ON e.tuition_modality_template_id = t.id
  78.                LEFT JOIN tuition_installments_templates ti ON t.id = ti.tuition_modality_template_id
  79.       where e.academic_year is not null  and e.tuition_modality_template_id is not null
  80.       ) a
  81. group by a.academic_year, a.title;
  82. '
  83.             );
  85.             $this->addSql(
  86.                 'ALTER VIEW report_total_or_installments_amount AS
  87. select a.academic_year, a.title, sum(t.amount) as total
  88. from (SELECT DISTINCT e.id,
  89.                       CASE
  90.                           WHEN ti.title = \'Uplata u cjelosti\' THEN ti.title
  91.                           ELSE \'Rate\'
  92.                           END AS title,
  93.                       e.academic_year
  94.       FROM students s
  95.                LEFT JOIN udg_fms.profiles p ON s.id = p.student_id
  96.                LEFT JOIN enrollment_events e ON e.profile_id = p.id
  97.                LEFT JOIN tuition_modality_templates t ON e.tuition_modality_template_id = t.id
  98.                LEFT JOIN tuition_installments_templates ti ON t.id = ti.tuition_modality_template_id
  99.       WHERE e.academic_year is not null  and e.tuition_modality_template_id is not null
  100.       GROUP BY e.id, title) a
  101.          left join tuition_installments t on t.enrollment_id = a.id
  102. where t.payed_on is not null
  103. group by a.academic_year,a.title;'
  104.             );
  106.         }
  107.     }