root
102 lines
3.6 KiB
PHP
102 lines
3.6 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Database\Migrations;
|
|
|
|
use CodeIgniter\Database\Migration;
|
|
|
|
final class BackfillInvoiceStudentsList extends Migration
|
|
{
|
|
public function up(): void
|
|
{
|
|
foreach (['invoice_students_list', 'invoices', 'enrollments', 'students', 'student_class'] as $table) {
|
|
if (! $this->db->tableExists($table)) {
|
|
return;
|
|
}
|
|
}
|
|
|
|
$invoiceFilters = [];
|
|
if ($this->db->fieldExists('invoice_number', 'invoices')) {
|
|
$invoiceFilters[] = "COALESCE(i.invoice_number, '') NOT LIKE 'CF-%'";
|
|
}
|
|
if ($this->db->fieldExists('semester', 'invoices')) {
|
|
$invoiceFilters[] = "LOWER(TRIM(COALESCE(i.semester, ''))) <> 'opening balance'";
|
|
}
|
|
if ($this->db->fieldExists('description', 'invoices')) {
|
|
$invoiceFilters[] = "LOWER(COALESCE(i.description, '')) NOT LIKE '%carried over%'";
|
|
$invoiceFilters[] = "LOWER(COALESCE(i.description, '')) NOT LIKE '%carry-forward%'";
|
|
$invoiceFilters[] = "LOWER(COALESCE(i.description, '')) NOT LIKE '%carry over%'";
|
|
$invoiceFilters[] = "LOWER(COALESCE(i.description, '')) NOT LIKE '%previous school year%'";
|
|
}
|
|
|
|
$nonEventFilter = $this->db->fieldExists('is_event_only', 'student_class')
|
|
? 'AND COALESCE(sc.is_event_only, 0) = 0'
|
|
: '';
|
|
|
|
$where = $invoiceFilters === [] ? '' : 'AND ' . implode("\n AND ", $invoiceFilters);
|
|
|
|
$this->db->query(
|
|
"INSERT INTO invoice_students_list (
|
|
invoice_id,
|
|
student_id,
|
|
student_firstname,
|
|
student_lastname,
|
|
school_id,
|
|
enrolled,
|
|
tuition_fee,
|
|
school_year,
|
|
created_at,
|
|
updated_at
|
|
)
|
|
SELECT
|
|
i.id AS invoice_id,
|
|
e.student_id,
|
|
MIN(COALESCE(s.firstname, '')) AS student_firstname,
|
|
MIN(COALESCE(s.lastname, '')) AS student_lastname,
|
|
MIN(COALESCE(s.school_id, 0)) AS school_id,
|
|
MAX(CASE
|
|
WHEN LOWER(TRIM(COALESCE(e.enrollment_status, ''))) IN ('enrolled', 'payment pending') THEN 1
|
|
ELSE 0
|
|
END) AS enrolled,
|
|
0.00 AS tuition_fee,
|
|
i.school_year,
|
|
UTC_TIMESTAMP(),
|
|
UTC_TIMESTAMP()
|
|
FROM invoices i
|
|
INNER JOIN enrollments e
|
|
ON e.parent_id = i.parent_id
|
|
AND e.school_year = i.school_year
|
|
INNER JOIN students s
|
|
ON s.id = e.student_id
|
|
WHERE LOWER(TRIM(COALESCE(e.enrollment_status, ''))) IN (
|
|
'enrolled',
|
|
'payment pending',
|
|
'withdrawn',
|
|
'refund pending',
|
|
'withdraw under review'
|
|
)
|
|
AND EXISTS (
|
|
SELECT 1
|
|
FROM student_class sc
|
|
WHERE sc.student_id = e.student_id
|
|
AND sc.school_year = i.school_year
|
|
{$nonEventFilter}
|
|
)
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM invoice_students_list isl
|
|
WHERE isl.invoice_id = i.id
|
|
AND isl.student_id = e.student_id
|
|
)
|
|
{$where}
|
|
GROUP BY i.id, e.student_id, i.school_year"
|
|
);
|
|
}
|
|
|
|
public function down(): void
|
|
{
|
|
// Backfilled invoice snapshots are intentionally retained.
|
|
}
|
|
}
|