migrations/Version20241006121120.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 Version20241006121120 extends AbstractMigration
  14.     {
  15.         public function getDescription(): string
  16.         {
  17.             return '';
  18.         }
  20.         public function up(Schema $schema): void
  21.         {
  22.             $this->addSql(
  23.                 'create view report_total_or_installments_amount_per_year_and_student as
  24.     select p.faculty,
  25.            p.index_no,
  26.            p.current_semester                                             as semester,
  27.            concat(s.first_name, \' \', s.last_name)                         as full_name,
  28.            e.academic_year                                                AS academic_year,
  29.            sp.name                                                        as study_program,
  30.            case
  31.                when t.title = \'U cjelosti\' or t.title = \'Uplata u cjelosti\' then \'Uplata u cjelosti\'
  32.                else \'Rate\' end                                            AS payment_type,
  33.            SUM(CASE WHEN t.payed_on IS NOT NULL THEN t.amount ELSE 0 END) AS amount,
  34.            case
  35.                when m.covered = 1 then m.title
  36.                else \'\' end                                                AS sponsored
  37.     from students s
  38.              left join profiles p on s.id = p.student_id
  39.              left join enrollment_events e on e.profile_id = p.id
  40.              left join tuition_modality_templates m on e.tuition_modality_template_id = m.id
  41.              left join tuition_installments t on t.enrollment_id = e.id
  42.              left join study_programmes sp on sp.id = p.study_programme_id
  43.     where e.academic_year is not null
  44.       and e.tuition_modality_template_id is not null
  45.     group by p.faculty, p.index_no, s.first_name, s.last_name, e.academic_year, sp.name, payment_type, sponsored,
  46.              p.current_semester
  47.     order by p.faculty, e.academic_year, sp.name, p.index_no;');
  48.         }
  50.         public function down(Schema $schema): void
  51.         {
  52.             $this->addSql('DROP VIEW report_total_or_installments_amount_per_year_and_student');
  54.         }
  55.     }