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