# Laporan Pivot Aktivitas Transaksi Berbasis Step & Personil
## Dengan Integrasi CodeIgniter 3 + MariaDB 10.3 + PHP 5.6

Dokumen ini berisi **satu file utuh** arsitektur lengkap untuk membuat laporan pivot yang menampilkan keterlibatan setiap personil dalam setiap tahapan (step) transaksi, termasuk step berulang (misal GRN berkali-kali dalam satu PO). Solusi menggunakan **ETL berkala** untuk mengurai data `counters_intext` (serialisasi `print_r` PHP) menjadi tabel materialized, lalu menyajikannya melalui **VIEW** dan **Stored Procedure dinamis** yang siap diintegrasikan dengan DataTable.

**Lingkungan:**
- Database: MariaDB 10.3
- PHP: 5.6
- Framework: CodeIgniter 3

---

## Daftar Isi
1. [Persiapan Database](#1-persiapan-database)
2. [Implementasi di CodeIgniter 3](#2-implementasi-di-codeigniter-3)
3. [Menjalankan ETL](#3-menjalankan-etl)
4. [Catatan Khusus](#4-catatan-khusus)
5. [Contoh Hasil Laporan](#5-contoh-hasil-laporan)

---

## 1. Persiapan Database

Jalankan skrip SQL berikut di database Anda.

### 1.1. Tabel Dimensi Personil
```sql
CREATE TABLE IF NOT EXISTS dim_person (
    person_id INT PRIMARY KEY,
    person_name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.2. Tabel Fakta Aktivitas (Materialized)
sql
CREATE TABLE IF NOT EXISTS trx_activity_raw (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trx_id INT NOT NULL,
    person_id INT NOT NULL,
    step_code VARCHAR(50) NOT NULL,
    counter INT DEFAULT 0,
    fulldate DATE,
    jenis_master VARCHAR(24),
    nomer VARCHAR(120),
    INDEX idx_trx (trx_id),
    INDEX idx_person (person_id),
    INDEX idx_step (step_code),
    INDEX idx_date (fulldate),
    INDEX idx_master (jenis_master)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.3. VIEW Detail dan Agregasi
sql
-- VIEW Detail (Base)
CREATE OR REPLACE VIEW vw_activity_detail AS
SELECT 
    a.trx_id,
    a.person_id,
    p.person_name,
    a.step_code,
    a.counter,
    a.fulldate,
    a.jenis_master,
    a.nomer,
    CASE 
        WHEN a.jenis_master = '582' THEN 'Penjualan'
        WHEN a.jenis_master = '789' THEN 'Pembelian'   -- sesuaikan dengan kode modul lain
        ELSE 'Lainnya' 
    END AS modul_label
FROM trx_activity_raw a
JOIN dim_person p ON a.person_id = p.person_id;

-- VIEW Agregasi Harian
CREATE OR REPLACE VIEW vw_activity_daily AS
SELECT 
    person_id,
    person_name,
    step_code,
    fulldate AS tgl,
    SUM(counter) AS total_aktivitas,
    jenis_master
FROM vw_activity_detail
GROUP BY person_id, person_name, step_code, fulldate, jenis_master;

-- VIEW Agregasi Bulanan (opsional)
CREATE OR REPLACE VIEW vw_activity_monthly AS
SELECT 
    person_id,
    person_name,
    step_code,
    YEAR(tgl) AS tahun,
    MONTH(tgl) AS bulan,
    SUM(total_aktivitas) AS total_aktivitas,
    jenis_master
FROM vw_activity_daily
GROUP BY person_id, person_name, step_code, YEAR(tgl), MONTH(tgl), jenis_master;
1.4. Stored Procedure Pivot Dinamis
sql
DELIMITER //

CREATE OR REPLACE PROCEDURE SP_GET_PIVOT_ACTIVITY(
    IN p_date_start DATE,
    IN p_date_end DATE,
    IN p_group_by VARCHAR(10),   -- 'DAY', 'MONTH', 'QUARTER', 'YEAR'
    IN p_sort_col VARCHAR(50),   
    IN p_sort_dir VARCHAR(4)     
)
BEGIN
    SET SESSION group_concat_max_len = 1000000;

    SET @group_cols = 'person_id, person_name';
    SET @select_cols = 'person_id, person_name';
    SET @date_group = '';

    IF p_group_by = 'MONTH' THEN
        SET @group_cols = CONCAT(@group_cols, ', tahun, bulan');
        SET @select_cols = CONCAT(@select_cols, ', tahun, bulan');
        SET @date_group = ', YEAR(tgl) AS tahun, MONTH(tgl) AS bulan';
    ELSEIF p_group_by = 'YEAR' THEN
        SET @group_cols = CONCAT(@group_cols, ', tahun');
        SET @select_cols = CONCAT(@select_cols, ', tahun');
        SET @date_group = ', YEAR(tgl) AS tahun';
    ELSEIF p_group_by = 'QUARTER' THEN
        SET @group_cols = CONCAT(@group_cols, ', tahun, kuartal');
        SET @select_cols = CONCAT(@select_cols, ', tahun, kuartal');
        SET @date_group = ', YEAR(tgl) AS tahun, QUARTER(tgl) AS kuartal';
    ELSE  
        SET @date_group = '';
    END IF;

    SELECT GROUP_CONCAT(DISTINCT 
           CONCAT('SUM(CASE WHEN step_code = ''', step_code, ''' THEN total_aktivitas ELSE 0 END) AS `', step_code, '`')
           ORDER BY step_code ASC)
    INTO @sql_steps
    FROM vw_activity_daily
    WHERE tgl BETWEEN p_date_start AND p_date_end;

    IF @sql_steps IS NULL THEN
        SET @sql_steps = '1 AS dummy';
    END IF;

    SET @sql = CONCAT('
        SELECT ', @select_cols, ', 
               ', @sql_steps, ',
               SUM(total_aktivitas) AS grand_total
        FROM vw_activity_daily
        WHERE tgl BETWEEN ''', p_date_start, ''' AND ''', p_date_end, '''
        ', @date_group, '
        GROUP BY ', @group_cols
    );

    IF p_sort_col IS NOT NULL AND p_sort_col <> '' THEN
        SET @sql = CONCAT(@sql, ' ORDER BY `', p_sort_col, '` ', p_sort_dir);
    ELSE
        SET @sql = CONCAT(@sql, ' ORDER BY grand_total DESC');
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;
2. Implementasi di CodeIgniter 3
2.1. Model: Activity_model.php
Letakkan di application/models/Activity_model.php

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

class Activity_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    /**
     * ETL: Parse counters_intext dan isi trx_activity_raw serta dim_person
     */
    public function run_etl() {
        // Truncate tables
        $this->db->query("TRUNCATE TABLE trx_activity_raw");
        $this->db->query("TRUNCATE TABLE dim_person");

        // Ambil semua transaksi yang memiliki counters_intext
        $sql = "SELECT id, counters_intext, fulldate, jenis_master, nomer, 
                       oleh_id, oleh_nama, customers_id, customers_nama 
                FROM transaksi 
                WHERE counters_intext IS NOT NULL AND counters_intext != ''";
        $query = $this->db->query($sql);
        $rows = $query->result_array();

        $persons = array();
        $batch_data = array();

        foreach ($rows as $row) {
            $text = $row['counters_intext'];
            if (empty($text)) continue;

            // Pola blok [<stepCode>|...|olehID] => Array ( ... )
            preg_match_all('/\[(.*?)\|olehID\]\s*=>\s*Array\s*\(\s*(.*?)\s*\)\s*\)/s', $text, $blocks, PREG_SET_ORDER);

            foreach ($blocks as $block) {
                $inner = $block[2];
                preg_match_all('/\[([^\]]+)\|([0-9]+)\]\s*=>\s*([0-9]+)/', $inner, $items, PREG_SET_ORDER);

                foreach ($items as $item) {
                    $stepCode = $item[1];
                    $personId = (int)$item[2];
                    $counter  = (int)$item[3];

                    if ($counter == 0) continue;

                    // Kumpulkan personil
                    if (!isset($persons[$personId])) {
                        if ($personId == $row['oleh_id'] && !empty($row['oleh_nama'])) {
                            $persons[$personId] = $row['oleh_nama'];
                        } elseif ($personId == $row['customers_id'] && !empty($row['customers_nama'])) {
                            $persons[$personId] = $row['customers_nama'];
                        } else {
                            $persons[$personId] = 'User_' . $personId;
                        }
                    }

                    // Siapkan data batch untuk insert
                    $batch_data[] = array(
                        'trx_id'      => $row['id'],
                        'person_id'   => $personId,
                        'step_code'   => $stepCode,
                        'counter'     => $counter,
                        'fulldate'    => $row['fulldate'],
                        'jenis_master'=> $row['jenis_master'],
                        'nomer'       => $row['nomer']
                    );
                }
            }
        }

        // Insert batch ke trx_activity_raw
        if (!empty($batch_data)) {
            $this->db->insert_batch('trx_activity_raw', $batch_data);
        }

        // Insert ke dim_person
        if (!empty($persons)) {
            $person_insert = array();
            foreach ($persons as $id => $name) {
                $person_insert[] = array('person_id' => $id, 'person_name' => $name);
            }
            // Insert with ON DUPLICATE KEY UPDATE
            $this->db->query("INSERT INTO dim_person (person_id, person_name) VALUES " . 
                             implode(',', array_map(function($p) {
                                 return "({$p['person_id']}, '{$p['person_name']}')";
                             }, $person_insert)) . 
                             " ON DUPLICATE KEY UPDATE person_name = VALUES(person_name)");
        }

        return count($batch_data);
    }

    /**
     * Panggil Stored Procedure untuk mendapatkan data pivot
     */
    public function get_pivot_data($start, $end, $group_by = 'DAY', $sort_col = '', $sort_dir = 'DESC') {
        $sql = "CALL SP_GET_PIVOT_ACTIVITY('$start', '$end', '$group_by', '$sort_col', '$sort_dir')";
        $query = $this->db->query($sql);
        $result = $query->result_array();
        // Tutup dan buka ulang koneksi untuk menghindari "Commands out of sync"
        $this->db->close();
        $this->db->initialize();
        return $result;
    }
}
?>
2.2. Controller: Laporan.php
Letakkan di application/controllers/Laporan.php

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

class Laporan extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->model('activity_model');
        $this->load->database();
    }

    /**
     * Halaman utama laporan (tampilan DataTable)
     */
    public function index() {
        $this->load->view('laporan_aktivitas');
    }

    /**
     * Endpoint AJAX untuk DataTable
     */
    public function ajax_aktivitas() {
        $start   = $this->input->get('date_start') ?: date('Y-m-01');
        $end     = $this->input->get('date_end') ?: date('Y-m-d');
        $group   = $this->input->get('group_by') ?: 'DAY';
        $sortCol = $this->input->get('order_col') ?: '';
        $sortDir = $this->input->get('order_dir') ?: 'DESC';

        // Validasi
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $start)) $start = date('Y-m-01');
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $end)) $end = date('Y-m-d');

        $data = $this->activity_model->get_pivot_data($start, $end, $group, $sortCol, $sortDir);
        echo json_encode(array('data' => $data));
    }

    /**
     * URL untuk menjalankan ETL (bisa diakses via browser atau cron)
     * Contoh: http://yourdomain.com/laporan/run_etl
     */
    public function run_etl() {
        $count = $this->activity_model->run_etl();
        echo "ETL selesai. Total baris aktivitas dimasukkan: $count";
    }
}
?>
2.3. View untuk DataTable (dengan kolom dinamis)
Buat file application/views/laporan_aktivitas.php:

html
<!DOCTYPE html>
<html>
<head>
    <title>Laporan Aktivitas Personil</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.js"></script>
    <style>
        body { padding: 20px; font-family: Arial; }
        .filter-area { margin-bottom: 15px; }
        .filter-area label { margin-right: 15px; }
        #table-aktivitas { width: 100%; }
    </style>
</head>
<body>
    <h2>Laporan Pivot Aktivitas per Step</h2>
    <div class="filter-area">
        <label>Tanggal Mulai: <input type="date" id="filter_start" value="<?php echo date('Y-m-01'); ?>"></label>
        <label>Tanggal Akhir: <input type="date" id="filter_end" value="<?php echo date('Y-m-d'); ?>"></label>
        <label>Group By:
            <select id="filter_group">
                <option value="DAY">Harian</option>
                <option value="MONTH">Bulanan</option>
                <option value="QUARTER">Kuartal</option>
                <option value="YEAR">Tahunan</option>
            </select>
        </label>
        <button id="btn_refresh">Refresh</button>
    </div>
    <table id="table-aktivitas" class="display"></table>

    <script>
    $(document).ready(function() {
        var table = $('#table-aktivitas').DataTable({
            processing: true,
            serverSide: false,
            ajax: {
                url: '<?php echo base_url("laporan/ajax_aktivitas"); ?>',
                data: function(d) {
                    d.date_start = $('#filter_start').val();
                    d.date_end   = $('#filter_end').val();
                    d.group_by   = $('#filter_group').val();
                    d.order_col  = 'grand_total';
                    d.order_dir  = 'DESC';
                },
                dataSrc: function(json) {
                    window._pivotData = json.data;
                    return json.data;
                }
            },
            columns: [] // akan diisi secara dinamis
        });

        function buildColumns(data) {
            if (!data || data.length === 0) return;
            var firstRow = data[0];
            var cols = [];
            var fixedKeys = ['person_id', 'person_name', 'grand_total'];
            var dynamicKeys = Object.keys(firstRow).filter(function(k) {
                return fixedKeys.indexOf(k) === -1;
            });
            dynamicKeys.sort();
            cols.push({ data: 'person_id', title: 'Person ID' });
            cols.push({ data: 'person_name', title: 'Nama' });
            dynamicKeys.forEach(function(key) {
                cols.push({ data: key, title: key });
            });
            cols.push({ data: 'grand_total', title: 'Grand Total' });
            return cols;
        }

        $('#table-aktivitas').on('xhr.dt', function(e, settings, json) {
            if (json.data && json.data.length > 0) {
                var newCols = buildColumns(json.data);
                table.destroy();
                table = $('#table-aktivitas').DataTable({
                    processing: true,
                    serverSide: false,
                    ajax: {
                        url: '<?php echo base_url("laporan/ajax_aktivitas"); ?>',
                        data: function(d) {
                            d.date_start = $('#filter_start').val();
                            d.date_end   = $('#filter_end').val();
                            d.group_by   = $('#filter_group').val();
                            d.order_col  = 'grand_total';
                            d.order_dir  = 'DESC';
                        },
                        dataSrc: 'data'
                    },
                    columns: newCols,
                    order: [[ newCols.length - 1, 'desc' ]]
                });
            }
        });

        $('#btn_refresh').on('click', function() {
            table.ajax.reload();
        });

        table.ajax.reload();
    });
    </script>
</body>
</html>
3. Menjalankan ETL
Melalui browser: Akses http://yourdomain.com/laporan/run_etl

Melalui cron (CLI): php /path/to/index.php laporan run_etl

Jadwalkan cron harian (misal jam 01:00) untuk menjaga data tetap mutakhir.

4. Catatan Khusus
Stored Procedure & Multi-Result: Fungsi get_pivot_data menutup dan membuka ulang koneksi untuk menghindari error Commands out of sync. Jika masih terjadi, gunakan $this->db->conn_id->next_result() setelah query.

Keamanan: Semua input dari user divalidasi (tanggal) sebelum digunakan di SP. Untuk production, tambahkan filtering lebih ketat.

Performa: Untuk transaksi sangat besar (jutaan), batasi ETL hanya untuk data baru (incremental) atau gunakan partition.

Nama Personil: Untuk akurasi, perbaiki dim_person dengan join ke tabel master karyawan/pelanggan/pemasok sesuai person_id.

5. Contoh Hasil Laporan
Tabel pivot akan menampilkan:

person_id	person_name	582spo	582so	582pkd	582spd	582	grand_total
245	jkt2	2	1	1	1	1	6
Jika memilih grouping bulan, akan muncul kolom tahun dan bulan atau kuartal.

Akhir Dokumen
File ini adalah satu kesatuan. Salin seluruh konten mulai dari baris pertama sampai baris terakhir, simpan sebagai laporan_aktivitas.md, dan gunakan sebagai panduan lengkap implementasi.

text

---

**Cara menggunakan:** 
- Blok teks di atas (mulai dari `---markdown` sampai `---`) adalah satu file utuh.
- Copy seluruh blok tersebut ke editor teks (misal VS Code, Notepad++).
- Simpan dengan nama `laporan_aktivitas.md`.
- File ini siap dibawa ke agentic system Anda atau digunakan sebagai dokumentasi proyek.

Semua komponen sudah terintegrasi dan siap diimplementasikan di aplikasi ERP CodeIgniter 3 Anda.