accounting_dev_v2/application/controllers/Accountingcheck.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

/** Pemeriksaan read-only fondasi accounting untuk deployment dan CI. */
class Accountingcheck extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
        if (!$this->input->is_cli_request()) {
            show_error('Accounting check hanya dapat dijalankan melalui CLI.', 403, 'Akses ditolak');
        }
    }

    public function index()
    {
        $failures = array();
        foreach (array('journals', 'journal_details', 'accounts', 'document_sequences', 'system_account_mappings', 'fiscal_years', 'accounting_periods', 'journal_status_histories') as $table) {
            if (!$this->db->table_exists($table)) {
                $failures[] = 'Tabel tidak ditemukan: ' . $table;
            }
        }

        if (empty($failures)) {
            try {
                $this->load->library('AccountMappingService');
                $this->load->library('JournalService');
                foreach (array(
                    'inventory', 'cost_of_goods_sold', 'accounts_receivable', 'deferred_revenue',
                    'inventory_in_sales', 'goods_revenue', 'customer_savings', 'depreciation_expense'
                ) as $mappingKey) {
                    $this->accountmappingservice->get($mappingKey);
                }
                $testTotals = $this->journalservice->totals(array(
                    array('debit' => 100, 'kredit' => 0),
                    array('debit' => 0, 'kredit' => 100)
                ));
                if ($testTotals['debit'] !== 100.0 || $testTotals['kredit'] !== 100.0) {
                    $failures[] = 'JournalService gagal menghitung total jurnal.';
                }
            } catch (Throwable $exception) {
                $failures[] = 'Service foundation gagal dimuat: ' . $exception->getMessage();
            }

            $invalidMappings = $this->db->query(
                'SELECT m.mapping_key FROM system_account_mappings m '
                . 'LEFT JOIN accounts a ON a.id = m.account_id '
                . 'WHERE m.is_active = 1 AND (a.id IS NULL OR a.is_active <> 1 OR a.allow_posting <> 1)'
            )->result();
            foreach ($invalidMappings as $mapping) {
                $failures[] = 'Mapping akun tidak valid: ' . $mapping->mapping_key;
            }

            $duplicates = $this->db->query(
                "SELECT no_ref, COUNT(*) total FROM journals WHERE no_ref IS NOT NULL AND no_ref <> '' "
                . 'GROUP BY no_ref HAVING COUNT(*) > 1 LIMIT 20'
            )->result();
            foreach ($duplicates as $duplicate) {
                $failures[] = 'Nomor jurnal duplikat: ' . $duplicate->no_ref;
            }

            $unbalanced = $this->db->query(
                'SELECT j.id, j.no_ref FROM journals j '
                . 'JOIN journal_details d ON d.journal_id = j.id '
                . 'GROUP BY j.id, j.no_ref '
                . 'HAVING ABS(SUM(d.debit) - SUM(d.kredit)) > 0.009 LIMIT 20'
            )->result();
            foreach ($unbalanced as $journal) {
                $failures[] = 'Jurnal tidak balance: ' . $journal->no_ref . ' (ID ' . $journal->id . ')';
            }

            $missingPeriods = $this->db->where('accounting_period_id IS NULL', null, false)->count_all_results('journals');
            if ($missingPeriods > 0) {
                $failures[] = $missingPeriods . ' jurnal belum terhubung ke periode accounting.';
            }
        }

        if (!empty($failures)) {
            foreach ($failures as $failure) {
                fwrite(STDERR, '[FAIL] ' . $failure . PHP_EOL);
            }
            exit(1);
        }

        echo '[OK] Tabel fondasi, mapping akun, nomor referensi, dan keseimbangan jurnal valid.' . PHP_EOL;
    }

    public function schema()
    {
        foreach (array('journals', 'journal_details', 'accounts', 'roles', 'users') as $table) {
            echo '--- ' . $table . ' ---' . PHP_EOL;
            foreach ($this->db->field_data($table) as $field) {
                echo implode(' | ', array(
                    $field->name,
                    $field->type,
                    isset($field->max_length) ? $field->max_length : '',
                    !empty($field->primary_key) ? 'PK' : '',
                    isset($field->default) ? 'default=' . $field->default : ''
                )) . PHP_EOL;
            }
        }

        $stats = $this->db->select('COUNT(*) total, MIN(tanggal) min_date, MAX(tanggal) max_date', false)->get('journals')->row();
        echo 'journals=' . $stats->total . ' range=' . $stats->min_date . '..' . $stats->max_date . PHP_EOL;

        echo '--- journal status ---' . PHP_EOL;
        foreach ($this->db->select('status, COUNT(*) total', false)->group_by('status')->get('journals')->result() as $row) {
            echo $row->status . '=' . $row->total . PHP_EOL;
        }

        echo '--- column definitions ---' . PHP_EOL;
        $columns = $this->db->query(
            "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE FROM information_schema.COLUMNS "
            . "WHERE TABLE_SCHEMA = DATABASE() AND ((TABLE_NAME = 'journals' AND COLUMN_NAME = 'status') "
            . "OR (TABLE_NAME = 'accounts' AND COLUMN_NAME IN ('tipe','posisi','kategori','sub_tipe')))"
        )->result();
        foreach ($columns as $column) {
            echo $column->TABLE_NAME . '.' . $column->COLUMN_NAME . '=' . $column->COLUMN_TYPE . ' nullable=' . $column->IS_NULLABLE . PHP_EOL;
        }

        echo '--- duplicate account codes ---' . PHP_EOL;
        $duplicates = $this->db->select('kode_akun, COUNT(*) total', false)->group_by('kode_akun')->having('COUNT(*) >', 1)->get('accounts')->result();
        echo count($duplicates) . PHP_EOL;
        echo '--- roles ---' . PHP_EOL;
        foreach ($this->db->order_by('id', 'ASC')->get('roles')->result() as $role) {
            echo $role->id . '=' . $role->nama_role . PHP_EOL;
        }
        echo '--- users per role ---' . PHP_EOL;
        foreach ($this->db->select('roles.nama_role, COUNT(users.id) total')->from('roles')->join('users', 'users.role_id = roles.id', 'left')->group_by('roles.id')->get()->result() as $role) {
            echo $role->nama_role . '=' . $role->total . PHP_EOL;
        }
    }

    public function periodtrigger()
    {
        $this->db->trans_begin();
        try {
            $reference = 'CHECK-' . date('YmdHis') . '-' . mt_rand(1000, 9999);
            $this->db->insert('journals', array(
                'tanggal' => date('Y-m-d'),
                'no_ref' => $reference,
                'keterangan' => 'Temporary period trigger check',
                'created_by' => 1,
                'status' => 'posted'
            ));
            $row = $this->db->get_where('journals', array('id' => $this->db->insert_id()))->row();
            if (!$row || !$row->accounting_period_id || !$row->posted_at) {
                throw new RuntimeException('Trigger tidak mengisi periode atau metadata posting.');
            }
            echo '[OK] Period trigger mengisi accounting_period_id dan metadata posted.' . PHP_EOL;
        } catch (Throwable $exception) {
            fwrite(STDERR, '[FAIL] ' . $exception->getMessage() . PHP_EOL);
            $this->db->trans_rollback();
            exit(1);
        }
        $this->db->trans_rollback();
    }
}