# Implementation Plan - Personnel Transaction Activity Pivot Report (Sales Focus)

This plan implements a periodic/on-demand ETL process to parse serialized step activity trackers (`counters_intext` print_r strings) from the `transaksi` table, map them to specific personnel, aggregate them through database views, and display them in a dynamic pivot table on the SAN BI Portal dashboard.

The initial rollout focuses on the **Penjualan (Sales)** module, but the database architecture and ETL logic are built to scale dynamically to all other modules.

---

## Proposed Changes

### 1. Database Layer (MySQL/MariaDB)

#### [NEW] [setup_activity_db.sql](file:///w:/new_san_variant/scratch/setup_activity_db.sql)
We will create a SQL script to set up:
- Dimension table: `dim_person`
- Materialized raw log table: `trx_activity_raw`
- View `vw_activity_detail` (resolves personnel names, codes, and maps modules)
- View `vw_activity_daily` (aggregates daily activity counts)
- Stored Procedure `SP_GET_PIVOT_ACTIVITY` (dynamically pivots steps within date range, optionally filtering by module)

```sql
-- 1. Tables
CREATE TABLE IF NOT EXISTS dim_person (
    person_id INT PRIMARY KEY,
    person_name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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;

-- 2. Views
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 IN ('582', '382', '1582', '982') THEN 'Penjualan'
        WHEN a.jenis_master IN ('466', '461', '460', '463') THEN 'Pembelian'
        WHEN a.jenis_master IN ('583', '585', '3583', '3585') THEN 'Distribusi'
        WHEN a.jenis_master IN ('675', '676', '677') THEN 'Biaya'
        WHEN a.jenis_master IN ('1334', '334') THEN 'Konversi'
        ELSE 'Lainnya' 
    END AS modul_label
FROM trx_activity_raw a
JOIN dim_person p ON a.person_id = p.person_id;

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;

-- 3. Stored Procedure
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),
    IN p_module VARCHAR(20),
    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;

    SET @filter_clause = '1=1';
    IF p_module = 'sales' THEN
        SET @filter_clause = 'jenis_master IN (\'582\', \'382\', \'1582\', \'982\')';
    END IF;

    SET @sql_steps = NULL;
    SET @query_steps = CONCAT('
        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_temp
        FROM vw_activity_daily
        WHERE tgl BETWEEN \'', p_date_start, '\' AND \'', p_date_end, '\'
          AND ', @filter_clause
    );
    
    PREPARE stmt_steps FROM @query_steps;
    EXECUTE stmt_steps;
    DEALLOCATE PREPARE stmt_steps;
    
    SET @sql_steps = @sql_steps_temp;

    IF @sql_steps IS NULL THEN
        SET @sql_steps = '0 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, '\'
          AND ', @filter_clause, '
        ', @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. Backend Layer (CodeIgniter 3)

#### [NEW] [Activity_model.php](file:///w:/new_san_variant/application/models/Activity_model.php)
We will create this model to run the ETL parser (using corrected regex) and retrieve aggregated pivot data from the stored procedure.
- Handles clean truncation and batch insertion.
- Standardizes user name lookup using transaction-level fields (`oleh_nama`/`customers_nama`).
- Uses dynamic SP execution and safely restarts database connections for MariaDB compatibility.

#### [MODIFY] [LaporanApi.php](file:///w:/new_san_variant/application/controllers/eusvc/LaporanApi.php)
We will add two new endpoints inside the `LaporanApi` controller:
1. `activity_pivot()`: Exposes `Activity_model::get_pivot_data` as a JSON endpoint with query params (`start_date`, `end_date`, `group_by`).
2. `run_etl()`: Exposes a secure manual ETL execution endpoint (`Activity_model::run_etl`) that reports back the number of parsed log rows.

---

### 3. Frontend Layer (SAN BI Portal)

#### [MODIFY] [index.html](file:///w:/new_san_variant/bi_reports/index.html)
We will add a new wide report card at the bottom of the grid:
- Id: `#sec-personnel`
- Displays the personnel activity pivot.
- Includes a dropdown selector to choose grouping (`DAY`, `MONTH`, `QUARTER`, `YEAR`).
- Includes a "Jalankan ETL" button with inline loading state to refresh data.

#### [MODIFY] [app.js](file:///w:/new_san_variant/bi_reports/assets/app.js)
We will extend the Javascript rendering logic:
- Fetch personnel activity from `LaporanApi/activity_pivot` on page load or filter changes.
- Implement `renderPersonnel()` which dynamically builds DataTable columns based on step codes returned by the API (columns will adjust dynamically if new step codes appear).
- Add event listeners for the "Group By" dropdown and the "Jalankan ETL" button.

---

## Verification Plan

### Automated Verification
- Run syntax validation checks on all modified/new files:
  ```powershell
  php -l application/models/Activity_model.php
  php -l application/controllers/eusvc/LaporanApi.php
  ```
- Run a DB setup validation script `scratch/setup_activity_db.php` to execute the DDL queries and verify that the database objects are successfully created.

### Manual Verification
1. Open the SAN BI Portal dashboard.
2. Click "Jalankan ETL" to trigger log parsing and populate the tables.
3. Verify that the table `trx_activity_raw` has rows and `dim_person` has unique names.
4. Change the Date Range and verify that the Personnel Activity table updates correctly.
5. Change the "Group By" filter (e.g. to "Bulanan") and verify that columns and rows adapt.