root
142 lines
4.6 KiB
PHP
142 lines
4.6 KiB
PHP
<?php
|
|
|
|
namespace App\Database\Migrations;
|
|
|
|
use CodeIgniter\Database\Migration;
|
|
|
|
class EnsureFinancialModelSchoolYears extends Migration
|
|
{
|
|
public function up()
|
|
{
|
|
$this->ensureColumn('invoice_lines', 'invoice_id');
|
|
$this->backfillFromParent('invoice_lines', 'invoices', 'invoice_id');
|
|
|
|
$this->ensureColumn('refund_payouts', 'refund_id');
|
|
$this->backfillFromParent('refund_payouts', 'refunds', 'refund_id');
|
|
|
|
$this->ensureColumn('payment_corrections', 'parent_id');
|
|
$this->backfillFromParent('payment_corrections', 'invoices', 'invoice_id');
|
|
|
|
$this->ensureColumn('reimbursement_batch_admin_files', 'batch_id');
|
|
$this->backfillFromParent('reimbursement_batch_admin_files', 'reimbursement_batches', 'batch_id');
|
|
|
|
$this->ensureColumn('purchase_orders', 'status');
|
|
$this->backfillCurrentYear('purchase_orders');
|
|
|
|
$this->ensureColumn('purchase_order_items', 'purchase_order_id');
|
|
$this->backfillFromParent('purchase_order_items', 'purchase_orders', 'purchase_order_id');
|
|
}
|
|
|
|
public function down()
|
|
{
|
|
// school_year is now part of the financial model contract; do not drop it.
|
|
}
|
|
|
|
private function ensureColumn(string $table, string $after): void
|
|
{
|
|
if (!$this->db->tableExists($table)) {
|
|
return;
|
|
}
|
|
|
|
if (!$this->db->fieldExists('school_year', $table)) {
|
|
$this->forge->addColumn($table, [
|
|
'school_year' => [
|
|
'type' => 'VARCHAR',
|
|
'constraint' => 9,
|
|
'null' => true,
|
|
'after' => $after,
|
|
],
|
|
]);
|
|
}
|
|
}
|
|
|
|
private function backfillFromParent(string $childTable, string $parentTable, string $foreignKey): void
|
|
{
|
|
if (
|
|
!$this->db->tableExists($childTable)
|
|
|| !$this->db->tableExists($parentTable)
|
|
|| !$this->db->fieldExists('school_year', $childTable)
|
|
|| !$this->db->fieldExists('school_year', $parentTable)
|
|
|| !$this->db->fieldExists($foreignKey, $childTable)
|
|
) {
|
|
return;
|
|
}
|
|
|
|
$this->db->query(sprintf(
|
|
"UPDATE %s child
|
|
INNER JOIN %s parent ON parent.id = child.%s
|
|
SET child.school_year = parent.school_year
|
|
WHERE child.school_year IS NULL OR TRIM(child.school_year) = ''",
|
|
$this->db->escapeIdentifiers($childTable),
|
|
$this->db->escapeIdentifiers($parentTable),
|
|
$this->db->escapeIdentifiers($foreignKey)
|
|
));
|
|
|
|
$this->backfillCurrentYear($childTable);
|
|
}
|
|
|
|
private function backfillCurrentYear(string $table): void
|
|
{
|
|
if (!$this->db->tableExists($table) || !$this->db->fieldExists('school_year', $table)) {
|
|
return;
|
|
}
|
|
|
|
$this->db->query(
|
|
sprintf(
|
|
'UPDATE %s SET school_year = ? WHERE school_year IS NULL OR TRIM(school_year) = ?',
|
|
$this->db->escapeIdentifiers($table)
|
|
),
|
|
[$this->currentSchoolYear(), '']
|
|
);
|
|
|
|
$this->db->query(sprintf(
|
|
'ALTER TABLE %s MODIFY school_year VARCHAR(9) NOT NULL',
|
|
$this->db->escapeIdentifiers($table)
|
|
));
|
|
|
|
$this->ensureIndex($table);
|
|
}
|
|
|
|
private function ensureIndex(string $table): void
|
|
{
|
|
$indexName = 'idx_' . $table . '_school_year';
|
|
if (strlen($indexName) > 64) {
|
|
$indexName = 'idx_' . substr(hash('sha256', $table . '_school_year'), 0, 24);
|
|
}
|
|
|
|
$indexes = $this->db->query('SHOW INDEX FROM ' . $this->db->escapeIdentifiers($table))->getResultArray();
|
|
foreach ($indexes as $index) {
|
|
if (($index['Key_name'] ?? '') === $indexName) {
|
|
return;
|
|
}
|
|
}
|
|
|
|
$this->db->query(sprintf(
|
|
'CREATE INDEX %s ON %s (school_year)',
|
|
$this->db->escapeIdentifiers($indexName),
|
|
$this->db->escapeIdentifiers($table)
|
|
));
|
|
}
|
|
|
|
private function currentSchoolYear(): string
|
|
{
|
|
if ($this->db->tableExists('school_years')) {
|
|
$row = $this->db->table('school_years')
|
|
->select('name')
|
|
->where('status', 'active')
|
|
->orderBy('id', 'DESC')
|
|
->get(1)
|
|
->getRowArray();
|
|
|
|
$name = trim((string)($row['name'] ?? ''));
|
|
if (preg_match('/^\d{4}-\d{4}$/', $name) === 1) {
|
|
return $name;
|
|
}
|
|
}
|
|
|
|
$year = (int)date('Y');
|
|
|
|
return $year . '-' . ($year + 1);
|
|
}
|
|
}
|