Wian Drs
204 lines
13 KiB
PHP
204 lines
13 KiB
PHP
<?php
|
|
defined('BASEPATH') OR exit('No direct script access allowed');
|
|
|
|
class Migration_Finalize_fixed_assets extends CI_Migration
|
|
{
|
|
private function column($table, $column, $definition)
|
|
{
|
|
if ($this->db->table_exists($table) && !$this->db->field_exists($column, $table)) {
|
|
$this->db->query("ALTER TABLE `{$table}` ADD `{$column}` {$definition}");
|
|
}
|
|
}
|
|
|
|
private function indexExists($table, $name)
|
|
{
|
|
return $this->db->query("SHOW INDEX FROM `{$table}` WHERE Key_name=?", array($name))->num_rows() > 0;
|
|
}
|
|
|
|
private function addIndex($table, $name, $columns, $unique = false)
|
|
{
|
|
if ($this->db->table_exists($table) && !$this->indexExists($table, $name)) {
|
|
$this->db->query('ALTER TABLE `'.$table.'` ADD '.($unique ? 'UNIQUE ' : '').'KEY `'.$name.'` ('.$columns.')');
|
|
}
|
|
}
|
|
|
|
public function up()
|
|
{
|
|
$this->db->query('DROP TRIGGER IF EXISTS trg_asset_event_no_update');
|
|
$this->db->query('DROP TRIGGER IF EXISTS trg_asset_event_no_delete');
|
|
|
|
$assetColumns = array(
|
|
'document_no' => 'VARCHAR(60) NULL',
|
|
'workflow_status' => "ENUM('draft','submitted','approved','rejected','capitalized') NOT NULL DEFAULT 'capitalized'",
|
|
'source_reference_no' => 'VARCHAR(120) NULL',
|
|
'supplier_id' => 'BIGINT UNSIGNED NULL',
|
|
'source_journal_mode' => "ENUM('post_journal','linked_journal','no_journal') NOT NULL DEFAULT 'no_journal'",
|
|
'source_journal_id' => 'INT NULL',
|
|
'acquisition_journal_id' => 'INT NULL',
|
|
'warehouse_id' => 'INT NULL',
|
|
'bin_id' => 'BIGINT UNSIGNED NULL',
|
|
'responsible_employee_id' => 'BIGINT NULL',
|
|
'department_id' => 'BIGINT NULL',
|
|
'project_id' => 'BIGINT UNSIGNED NULL',
|
|
'cost_center_id' => 'BIGINT UNSIGNED NULL',
|
|
'idempotency_key' => 'VARCHAR(120) NULL',
|
|
'created_by' => 'INT NULL',
|
|
'submitted_by' => 'INT NULL',
|
|
'submitted_at' => 'DATETIME NULL',
|
|
'approved_by' => 'INT NULL',
|
|
'approved_at' => 'DATETIME NULL',
|
|
'rejected_by' => 'INT NULL',
|
|
'rejected_at' => 'DATETIME NULL',
|
|
'rejection_reason' => 'VARCHAR(500) NULL',
|
|
'capitalized_by' => 'INT NULL',
|
|
'capitalized_at' => 'DATETIME NULL',
|
|
'updated_by' => 'INT NULL',
|
|
'updated_at' => 'DATETIME NULL'
|
|
);
|
|
foreach ($assetColumns as $column => $definition) $this->column('assets', $column, $definition);
|
|
$this->db->query("ALTER TABLE assets MODIFY source_type ENUM('purchase','warehouse','manual','opening','donation','other') NOT NULL DEFAULT 'manual'");
|
|
$this->db->query("UPDATE assets SET company_id=COALESCE(company_id,1),document_no=COALESCE(NULLIF(document_no,''),CONCAT('LEGACY-AST-',id)),workflow_status=COALESCE(NULLIF(workflow_status,''),'capitalized'),idempotency_key=COALESCE(NULLIF(idempotency_key,''),CONCAT('LEGACY-ASSET-',id)),created_by=COALESCE(created_by,1),capitalized_at=COALESCE(capitalized_at,created_at),capitalized_by=COALESCE(capitalized_by,created_by,1)");
|
|
$this->addIndex('assets', 'uq_asset_document_company', '`company_id`,`document_no`', true);
|
|
$this->addIndex('assets', 'uq_asset_idempotency', '`idempotency_key`', true);
|
|
$this->addIndex('assets', 'idx_asset_register_filter', '`company_id`,`workflow_status`,`lifecycle_status`,`category_id`,`lokasi_asset_id`,`tanggal_perolehan`');
|
|
$this->addIndex('assets', 'idx_asset_source_trace', '`company_id`,`source_type`,`source_id`');
|
|
$this->addIndex('assets', 'idx_asset_inventory_trace', '`item_id`,`barcode_id`,`warehouse_id`');
|
|
|
|
foreach (array(
|
|
'company_id' => 'BIGINT UNSIGNED NULL',
|
|
'updated_at' => 'DATETIME NULL',
|
|
'updated_by' => 'INT NULL'
|
|
) as $column => $definition) $this->column('asset_categories', $column, $definition);
|
|
if ($this->indexExists('asset_categories', 'uq_asset_category_code')) $this->db->query('ALTER TABLE asset_categories DROP INDEX uq_asset_category_code');
|
|
$this->db->query('UPDATE asset_categories SET company_id=COALESCE(company_id,1)');
|
|
$this->addIndex('asset_categories', 'uq_asset_category_company_code', '`company_id`,`code`', true);
|
|
$this->addIndex('asset_categories', 'idx_asset_category_active', '`company_id`,`is_active`,`name`');
|
|
|
|
foreach (array(
|
|
'company_id' => 'BIGINT UNSIGNED NULL',
|
|
'code' => 'VARCHAR(30) NULL',
|
|
'address' => 'TEXT NULL',
|
|
'responsible_employee_id' => 'BIGINT NULL',
|
|
'is_active' => 'TINYINT(1) NOT NULL DEFAULT 1',
|
|
'updated_at' => 'DATETIME NULL',
|
|
'updated_by' => 'INT NULL'
|
|
) as $column => $definition) $this->column('lokasi_asset', $column, $definition);
|
|
$this->db->query("UPDATE lokasi_asset SET company_id=COALESCE(company_id,1),code=COALESCE(NULLIF(code,''),CONCAT('LOC-',LPAD(id,4,'0'))),is_active=COALESCE(is_active,1)");
|
|
$this->addIndex('lokasi_asset', 'uq_asset_location_company_code', '`company_id`,`code`', true);
|
|
$this->addIndex('lokasi_asset', 'idx_asset_location_active', '`company_id`,`is_active`,`nama`');
|
|
|
|
foreach (array(
|
|
'company_id' => 'BIGINT UNSIGNED NULL',
|
|
'reversal_of_event_id' => 'BIGINT UNSIGNED NULL'
|
|
) as $column => $definition) $this->column('asset_events', $column, $definition);
|
|
$this->db->query("ALTER TABLE asset_events MODIFY event_type ENUM('acquisition','capitalization','addition','transfer','responsibility_change','maintenance','depreciation','impairment','revaluation','disposal','lost','damaged','opname_adjustment','reversal') NOT NULL");
|
|
$this->db->query('UPDATE asset_events e JOIN assets a ON a.id=e.asset_id SET e.company_id=COALESCE(e.company_id,a.company_id,1)');
|
|
$this->addIndex('asset_events', 'idx_asset_event_company_date', '`company_id`,`event_date`,`asset_id`');
|
|
$this->addIndex('asset_events', 'idx_asset_event_journal', '`journal_id`');
|
|
$this->addIndex('asset_events', 'idx_asset_event_reversal', '`reversal_of_event_id`');
|
|
|
|
$this->column('asset_depreciation_schedule', 'company_id', 'BIGINT UNSIGNED NULL');
|
|
$this->column('asset_depreciation_schedule', 'reversal_journal_id', 'INT NULL');
|
|
$this->column('asset_depreciation_schedule', 'reversed_at', 'DATETIME NULL');
|
|
$this->db->query('UPDATE asset_depreciation_schedule s JOIN assets a ON a.id=s.asset_id SET s.company_id=COALESCE(s.company_id,a.company_id,1)');
|
|
$this->addIndex('asset_depreciation_schedule', 'idx_asset_dep_company_period', '`company_id`,`period`,`status`,`asset_id`');
|
|
|
|
foreach (array(
|
|
'company_id' => 'BIGINT UNSIGNED NULL',
|
|
'maintenance_no' => 'VARCHAR(60) NULL',
|
|
'status' => "ENUM('posted','cancelled') NOT NULL DEFAULT 'posted'",
|
|
'journal_id' => 'INT NULL',
|
|
'asset_event_id' => 'BIGINT UNSIGNED NULL',
|
|
'idempotency_key' => 'VARCHAR(120) NULL'
|
|
) as $column => $definition) $this->column('asset_maintenance', $column, $definition);
|
|
$this->db->query("UPDATE asset_maintenance m JOIN assets a ON a.id=m.asset_id SET m.company_id=COALESCE(m.company_id,a.company_id,1),m.maintenance_no=COALESCE(NULLIF(m.maintenance_no,''),CONCAT('LEGACY-MNT-',m.id)),m.idempotency_key=COALESCE(NULLIF(m.idempotency_key,''),CONCAT('LEGACY-MNT-',m.id))");
|
|
$this->addIndex('asset_maintenance', 'uq_asset_maintenance_no', '`company_id`,`maintenance_no`', true);
|
|
$this->addIndex('asset_maintenance', 'uq_asset_maintenance_key', '`idempotency_key`', true);
|
|
$this->addIndex('asset_maintenance', 'idx_asset_maintenance_due', '`company_id`,`next_due_date`,`status`');
|
|
|
|
foreach (array(
|
|
'company_id' => 'BIGINT UNSIGNED NULL',
|
|
'snapshot_at' => 'DATETIME NULL',
|
|
'submitted_by' => 'INT NULL',
|
|
'submitted_at' => 'DATETIME NULL',
|
|
'rejected_by' => 'INT NULL',
|
|
'rejected_at' => 'DATETIME NULL',
|
|
'rejection_reason' => 'VARCHAR(500) NULL',
|
|
'posted_by' => 'INT NULL',
|
|
'posted_at' => 'DATETIME NULL',
|
|
'idempotency_key' => 'VARCHAR(120) NULL'
|
|
) as $column => $definition) $this->column('asset_opnames', $column, $definition);
|
|
$this->db->query("UPDATE asset_opnames SET company_id=COALESCE(company_id,1),snapshot_at=COALESCE(snapshot_at,NOW()),idempotency_key=COALESCE(NULLIF(idempotency_key,''),CONCAT('LEGACY-OPNAME-',id))");
|
|
$this->addIndex('asset_opnames', 'uq_asset_opname_key', '`idempotency_key`', true);
|
|
$this->addIndex('asset_opnames', 'idx_asset_opname_company_status', '`company_id`,`status`,`opname_date`');
|
|
|
|
foreach (array(
|
|
'company_id' => 'BIGINT UNSIGNED NULL',
|
|
'expected_location_id' => 'INT NULL',
|
|
'is_found' => 'TINYINT(1) NOT NULL DEFAULT 1',
|
|
'checked_at' => 'DATETIME NULL',
|
|
'checked_by' => 'INT NULL'
|
|
) as $column => $definition) $this->column('asset_opname_lines', $column, $definition);
|
|
$this->db->query('UPDATE asset_opname_lines l JOIN asset_opnames o ON o.id=l.asset_opname_id JOIN assets a ON a.id=l.asset_id SET l.company_id=COALESCE(l.company_id,o.company_id,a.company_id,1),l.expected_location_id=COALESCE(l.expected_location_id,a.lokasi_asset_id)');
|
|
$this->addIndex('asset_opname_lines', 'idx_asset_opname_line_company', '`company_id`,`asset_opname_id`,`asset_id`');
|
|
|
|
if ($this->db->table_exists('asset_reconciliations')) {
|
|
$this->column('asset_reconciliations', 'company_id', 'BIGINT UNSIGNED NULL');
|
|
$this->db->query('UPDATE asset_reconciliations SET company_id=COALESCE(company_id,1)');
|
|
$this->addIndex('asset_reconciliations', 'idx_asset_recon_company_date', '`company_id`,`as_of_date`,`status`');
|
|
}
|
|
|
|
$this->db->query("CREATE TABLE IF NOT EXISTS asset_workflow_history (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
company_id BIGINT UNSIGNED NOT NULL,
|
|
asset_id INT NOT NULL,
|
|
from_status VARCHAR(30) NULL,
|
|
to_status VARCHAR(30) NOT NULL,
|
|
notes VARCHAR(500) NULL,
|
|
user_id INT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
KEY idx_asset_workflow (company_id,asset_id,id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
$this->db->query("CREATE TABLE IF NOT EXISTS asset_responsibility_history (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
company_id BIGINT UNSIGNED NOT NULL,
|
|
asset_id INT NOT NULL,
|
|
effective_date DATE NOT NULL,
|
|
from_employee_id BIGINT NULL,
|
|
to_employee_id BIGINT NULL,
|
|
notes VARCHAR(500) NOT NULL,
|
|
asset_event_id BIGINT UNSIGNED NULL,
|
|
created_by INT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
KEY idx_asset_responsibility (company_id,asset_id,effective_date,id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$this->column('transaction_attachments', 'company_id', 'BIGINT UNSIGNED NULL');
|
|
$this->db->query("UPDATE transaction_attachments t JOIN assets a ON t.entity_type='asset' AND t.entity_id=a.id SET t.company_id=COALESCE(t.company_id,a.company_id,1)");
|
|
$this->db->query("UPDATE transaction_attachments t JOIN asset_maintenance m ON t.entity_type='asset_maintenance' AND t.entity_id=m.id SET t.company_id=COALESCE(t.company_id,m.company_id,1)");
|
|
$this->db->query("UPDATE transaction_attachments t JOIN asset_opnames o ON t.entity_type='asset_opname' AND t.entity_id=o.id SET t.company_id=COALESCE(t.company_id,o.company_id,1)");
|
|
$this->addIndex('transaction_attachments', 'idx_attachment_company_entity', '`company_id`,`entity_type`,`entity_id`,`id`');
|
|
|
|
$this->db->query("CREATE TABLE IF NOT EXISTS asset_report_snapshots (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
company_id BIGINT UNSIGNED NOT NULL,
|
|
report_type VARCHAR(40) NOT NULL,
|
|
as_of_date DATE NOT NULL,
|
|
payload_json LONGTEXT NOT NULL,
|
|
created_by INT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
KEY idx_asset_snapshot (company_id,report_type,as_of_date)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$this->db->query("INSERT IGNORE INTO asset_workflow_history(company_id,asset_id,from_status,to_status,notes,user_id,created_at) SELECT COALESCE(company_id,1),id,NULL,workflow_status,'Backfill status aset legacy',COALESCE(created_by,1),COALESCE(created_at,NOW()) FROM assets");
|
|
|
|
$this->db->query("CREATE TRIGGER trg_asset_event_no_update BEFORE UPDATE ON asset_events FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Asset event is immutable'");
|
|
$this->db->query("CREATE TRIGGER trg_asset_event_no_delete BEFORE DELETE ON asset_events FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Asset event is immutable'");
|
|
}
|
|
|
|
public function down()
|
|
{
|
|
throw new RuntimeException('Rollback finalisasi Aset Tetap wajib melalui restore backup database.');
|
|
}
|
|
}
|