add migration for drift stock logs

add excel export for purchase supplier report

See merge request mbugroup/lti-api!563

internal/database/migrations/20260522113148_fix_adjustment_stock_532_price.down.sql

@@ -0,0 +1,4 @@
+UPDATE adjustment_stocks
+SET price = 9535,
+    grand_total = ROUND(8700 * 9535, 3)
+WHERE id = 532 AND adj_number = 'ADJ-00507';

internal/database/migrations/20260522113148_fix_adjustment_stock_532_price.up.sql

@@ -0,0 +1,5 @@
+
+UPDATE adjustment_stocks
+SET price = 12635,
+    grand_total = ROUND(8700 * 12635, 3)
+WHERE id = 532 AND adj_number = 'ADJ-00507';

internal/database/migrations/20260523035647_consolidate_pullet_cikaum_locations.down.sql

@@ -0,0 +1,31 @@
+BEGIN;
+
+-- Rollback konsolidasi: kembalikan data ke loc 18 / 25 sesuai snapshot pre-migration.
+-- Order: un-soft-delete locations dulu agar FK tidak gagal saat UPDATE child.
+
+-- 1. Un-soft-delete locations
+UPDATE locations SET deleted_at = NULL WHERE id IN (18, 25);
+
+-- 2. project_flocks: PF 30 -> 18, PF 25 & 31 -> 25
+UPDATE project_flocks SET location_id = 18, updated_at = NOW() WHERE id = 30;
+UPDATE project_flocks SET location_id = 25, updated_at = NOW() WHERE id IN (25, 31);
+
+-- 3. kandangs: K9, K72, K117 -> 18; K10, K73, K116 -> 25
+UPDATE kandangs SET location_id = 18, updated_at = NOW() WHERE id IN (9, 72, 117);
+UPDATE kandangs SET location_id = 25, updated_at = NOW() WHERE id IN (10, 73, 116);
+
+-- 4. kandang_groups: KG 26, 68 -> 18; KG 27, 67 -> 25
+UPDATE kandang_groups SET location_id = 18, updated_at = NOW() WHERE id IN (26, 68);
+UPDATE kandang_groups SET location_id = 25, updated_at = NOW() WHERE id IN (27, 67);
+
+-- 5. warehouses: W27, W145, W152 -> 18; W3, W146, W153 -> 25
+UPDATE warehouses SET location_id = 18, updated_at = NOW() WHERE id IN (27, 145, 152);
+UPDATE warehouses SET location_id = 25, updated_at = NOW() WHERE id IN (3, 146, 153);
+
+-- 6. expenses: list eksplisit per location
+UPDATE expenses SET location_id = 18, updated_at = NOW()
+WHERE id IN (36, 345, 500, 501, 502, 503, 504, 505, 506, 507, 508);
+UPDATE expenses SET location_id = 25, updated_at = NOW()
+WHERE id IN (9, 37, 509, 510, 511, 512, 513, 514, 515, 516, 517, 518);
+
+COMMIT;

internal/database/migrations/20260523035647_consolidate_pullet_cikaum_locations.up.sql

@@ -0,0 +1,34 @@
+BEGIN;
+
+-- Konsolidasi 3 lokasi "Pullet Cikaum" jadi 1.
+-- Pindahkan semua data di loc 18 (Pullet Cikaum 1) & 25 (Pullet Cikaum 2) ke loc 2 (Pullet Cikaum).
+-- Urutan wajib: semua UPDATE child harus selesai SEBELUM soft-delete locations,
+-- karena trigger trg_soft_delete_fk_locations akan RAISE EXCEPTION untuk FK
+-- RESTRICT (project_flocks, kandangs, kandang_groups, expenses) atau SET NULL
+-- untuk warehouses kalau masih ada child yang reference.
+
+-- 1. project_flocks (PF 25, 30, 31)
+UPDATE project_flocks SET location_id = 2, updated_at = NOW()
+WHERE location_id IN (18, 25);
+
+-- 2. kandangs (K9, K72, K117, K10, K73, K116)
+UPDATE kandangs SET location_id = 2, updated_at = NOW()
+WHERE location_id IN (18, 25);
+
+-- 3. kandang_groups (KG 26, 68, 27, 67)
+UPDATE kandang_groups SET location_id = 2, updated_at = NOW()
+WHERE location_id IN (18, 25);
+
+-- 4. warehouses (W3, W27, W145, W146, W152, W153)
+UPDATE warehouses SET location_id = 2, updated_at = NOW()
+WHERE location_id IN (18, 25);
+
+-- 5. expenses (23 row BOP)
+UPDATE expenses SET location_id = 2, updated_at = NOW()
+WHERE location_id IN (18, 25);
+
+-- 6. Soft-delete locations 18 & 25 (kosong, aman karena semua child sudah pindah)
+UPDATE locations SET deleted_at = NOW()
+WHERE id IN (18, 25) AND deleted_at IS NULL;
+
+COMMIT;

internal/database/migrations/20260528121631_normalize_warehouse_jamali_10_to_25.down.sql

@@ -0,0 +1,99 @@
+BEGIN;
+
+-- ============================================================
+-- Rollback dynamic via audit snapshots di schema `migration_audit.jamali_w10_*`.
+-- Semua reverse dibaca dari snapshot yang dibuat oleh UP migration —
+-- tidak ada IDs/qty yang hardcode. Robust terhadap data drift antara
+-- dump time dan UP apply time (misalnya row baru warehouse_id=10
+-- yang muncul setelah dump diambil).
+--
+-- LIMITASI: FK relinks di stock_logs / stock_allocations / recording_eggs /
+-- marketing_products / dll. TIDAK direverse di sini (skip audit per-row
+-- untuk hemat storage ~40MB). Setelah down, 9 PW W10 yang di-restore
+-- akan kosong dari child rows (semua child masih pointing ke W25 PW
+-- yang sebelumnya menerima merge). Untuk rollback penuh, restore DB
+-- dari backup pre-migration.
+-- ============================================================
+
+-- Guard: pastikan audit tables ada (kalau tidak, fail-loud)
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1 FROM information_schema.tables
+    WHERE table_schema = 'migration_audit'
+      AND table_name = 'jamali_w10_pw_deleted_snapshot'
+  ) THEN
+    RAISE EXCEPTION 'Audit table migration_audit.jamali_w10_* tidak ditemukan. UP migration belum dijalankan atau audit sudah di-drop. Restore dari DB backup jika perlu.';
+  END IF;
+END $$;
+
+-- 1. Un-soft-delete warehouse 10 (kalau memang di-softdelete oleh UP)
+UPDATE warehouses w
+SET deleted_at = NULL, updated_at = NOW()
+FROM migration_audit.jamali_w10_warehouse_softdeleted a
+WHERE w.id = a.id;
+
+-- 2. Un-soft-delete stock_transfers self-loop yang disoft-delete UP step 7.1
+UPDATE stock_transfers st
+SET deleted_at = NULL, updated_at = NOW()
+FROM migration_audit.jamali_w10_st_softdeleted a
+WHERE st.id = a.id;
+
+-- 3. Reverse stock_transfers redirect (CASE-based dari snapshot was_from_w10/was_to_w10)
+UPDATE stock_transfers st
+SET from_warehouse_id = CASE WHEN a.was_from_w10 THEN 10 ELSE st.from_warehouse_id END,
+    to_warehouse_id   = CASE WHEN a.was_to_w10   THEN 10 ELSE st.to_warehouse_id   END,
+    updated_at = NOW()
+FROM migration_audit.jamali_w10_st_redirected a
+WHERE st.id = a.id;
+
+-- 3b. Self-loop transfers (W10<->W25 awal) juga punya from_warehouse_id=25 atau
+-- to_warehouse_id=25 setelah UP step 7.2. Karena snapshot jamali_w10_st_softdeleted
+-- punya kolom from_warehouse_id & to_warehouse_id asli, pakai itu untuk reverse.
+UPDATE stock_transfers st
+SET from_warehouse_id = 10, updated_at = NOW()
+FROM migration_audit.jamali_w10_st_softdeleted a
+WHERE st.id = a.id AND a.from_warehouse_id = 10;
+
+UPDATE stock_transfers st
+SET to_warehouse_id = 10, updated_at = NOW()
+FROM migration_audit.jamali_w10_st_softdeleted a
+WHERE st.id = a.id AND a.to_warehouse_id = 10;
+
+-- 4. Reverse purchase_items.warehouse_id 25 -> 10
+UPDATE purchase_items
+SET warehouse_id = 10
+WHERE id IN (SELECT id FROM migration_audit.jamali_w10_purchase_items);
+
+-- 5. Reverse W10-only PW (warehouse_id 25 -> 10, restore pfk asli dari snapshot)
+UPDATE product_warehouses pw
+SET warehouse_id = 10, project_flock_kandang_id = a.original_pfk
+FROM migration_audit.jamali_w10_pw_w10only_snapshot a
+WHERE pw.id = a.id;
+
+-- 6. Subtract qty dari W25 PW (reverse merge)
+-- WARNING: kalau W25 qty sudah dikonsumsi pasca-UP (sales/recording/dll),
+-- hasil bisa negatif. Tidak ada CHECK constraint di product_warehouses.qty,
+-- jadi silent. Operator harus verifikasi manual post-down:
+--   SELECT id, qty FROM product_warehouses WHERE qty < 0;
+UPDATE product_warehouses pw
+SET qty = pw.qty - a.merged_qty
+FROM migration_audit.jamali_w10_qty_merge a
+WHERE pw.id = a.target_pw_id;
+
+-- 7. Re-INSERT 9 W10 PW rows yang di-DELETE oleh UP (PK asli + qty asli)
+INSERT INTO product_warehouses (id, product_id, warehouse_id, qty, project_flock_kandang_id)
+SELECT id, product_id, 10, qty, project_flock_kandang_id
+FROM migration_audit.jamali_w10_pw_deleted_snapshot;
+
+-- 8. Cleanup audit tables (drop satu per satu, tidak wildcard untuk safety)
+DROP TABLE migration_audit.jamali_w10_pw_deleted_snapshot;
+DROP TABLE migration_audit.jamali_w10_qty_merge;
+DROP TABLE migration_audit.jamali_w10_pw_w10only_snapshot;
+DROP TABLE migration_audit.jamali_w10_st_softdeleted;
+DROP TABLE migration_audit.jamali_w10_st_redirected;
+DROP TABLE migration_audit.jamali_w10_purchase_items;
+DROP TABLE migration_audit.jamali_w10_warehouse_softdeleted;
+-- Schema migration_audit dipertahankan (bisa dipakai migration lain di masa depan)
+
+COMMIT;

internal/database/migrations/20260528121631_normalize_warehouse_jamali_10_to_25.up.sql

@@ -0,0 +1,241 @@
+BEGIN;
+
+-- ============================================================
+-- Normalisasi warehouse 10 (Jamali NON_AKTIF) -> 25 (Gudang Farm Jamali)
+-- Background: Dua warehouse LOKASI di area & lokasi sama (area_id=6,
+-- location_id=16). W10 sudah ditandai NON_AKTIF tapi masih punya 13
+-- product_warehouses, 3,590 stock_logs, ~790K stock_allocations,
+-- 332 marketing_products, 17 purchase_items, dan 14 stock_transfers.
+-- Migration ini konsolidasikan semua relasi ke W25 lalu soft-delete W10.
+--
+-- Klasifikasi data:
+-- A. 9 product_warehouses W10 overlap dengan W25 (sama product_id, pfk=NULL)
+--    -> merge qty ke W25, relink semua FK ke product_warehouses.id,
+--       lalu DELETE W10 PW rows.
+-- B. 4 product_warehouses W10-only -> UPDATE warehouse_id=25.
+--    Rows 1188/1189/1190 punya pfk=98 (anomali LOKASI, seharusnya NULL
+--    per aturan di CLAUDE.md [2026-05-06]) -> normalisasi sekalian.
+-- C. 17 purchase_items.warehouse_id=10 -> UPDATE 25 (no unique conflict).
+-- D. 3 stock_transfers W10<->W25 (PND-LTI-00107/00109/00119) akan jadi
+--    self-loop W25<->W25 setelah merge -> soft-delete.
+-- E. 12 stock_transfers EGG_FARM_CUTOVER to_warehouse_id=10 -> UPDATE 25.
+-- F. warehouse_id=10 sendiri -> soft-delete.
+--
+-- UP membuat 7 snapshot table di schema `migration_audit.jamali_w10_*`
+-- sebelum mutasi. DOWN baca snapshot itu untuk reverse dynamic (tidak
+-- hardcode IDs/qty), sehingga apapun yang ada di production saat UP
+-- dijalankan akan ter-audit dan ter-reverse. FK relinks
+-- (stock_logs/stock_allocations/dll) TIDAK di-audit (storage ~40MB)
+-- — limitation: tidak bisa di-reverse DOWN, full rollback = DB backup.
+-- ============================================================
+
+-- STEP -1: Buat schema audit + snapshot tables (idempotent rerun via DROP IF EXISTS)
+CREATE SCHEMA IF NOT EXISTS migration_audit;
+
+DROP TABLE IF EXISTS migration_audit.jamali_w10_pw_deleted_snapshot;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_qty_merge;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_pw_w10only_snapshot;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_st_softdeleted;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_st_redirected;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_purchase_items;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_warehouse_softdeleted;
+
+-- Snapshot 9 W10 PW yang akan di-DELETE (overlap dgn W25, pfk=NULL)
+CREATE TABLE migration_audit.jamali_w10_pw_deleted_snapshot AS
+SELECT pw10.id, pw10.product_id, pw10.qty, pw10.project_flock_kandang_id
+FROM product_warehouses pw10
+JOIN product_warehouses pw25
+  ON pw25.product_id = pw10.product_id
+ AND pw25.warehouse_id = 25
+ AND pw25.project_flock_kandang_id IS NULL
+WHERE pw10.warehouse_id = 10 AND pw10.project_flock_kandang_id IS NULL;
+
+-- Snapshot qty delta per W25 target (untuk reverse subtract)
+CREATE TABLE migration_audit.jamali_w10_qty_merge AS
+SELECT pw25.id AS target_pw_id, pw10.id AS source_pw_id, pw10.qty AS merged_qty
+FROM product_warehouses pw10
+JOIN product_warehouses pw25
+  ON pw25.product_id = pw10.product_id
+ AND pw25.warehouse_id = 25
+ AND pw25.project_flock_kandang_id IS NULL
+WHERE pw10.warehouse_id = 10 AND pw10.project_flock_kandang_id IS NULL;
+
+-- Snapshot W10-only PW (yang akan di-UPDATE warehouse_id 10->25)
+CREATE TABLE migration_audit.jamali_w10_pw_w10only_snapshot AS
+SELECT pw10.id, pw10.project_flock_kandang_id AS original_pfk
+FROM product_warehouses pw10
+WHERE pw10.warehouse_id = 10
+  AND pw10.id NOT IN (SELECT id FROM migration_audit.jamali_w10_pw_deleted_snapshot);
+
+-- Snapshot stock_transfers yang akan di-soft-delete (self-loop W10<->W25)
+-- Simpan from/to_warehouse_id asli supaya DOWN bisa reverse direction tepat
+CREATE TABLE migration_audit.jamali_w10_st_softdeleted AS
+SELECT id, movement_number, from_warehouse_id, to_warehouse_id
+FROM stock_transfers
+WHERE deleted_at IS NULL
+  AND ((from_warehouse_id = 10 AND to_warehouse_id = 25)
+    OR (from_warehouse_id = 25 AND to_warehouse_id = 10));
+
+-- Snapshot stock_transfers yang akan di-UPDATE (W10<->other, bukan self-loop)
+CREATE TABLE migration_audit.jamali_w10_st_redirected AS
+SELECT id,
+       (from_warehouse_id = 10) AS was_from_w10,
+       (to_warehouse_id = 10)   AS was_to_w10
+FROM stock_transfers
+WHERE deleted_at IS NULL
+  AND (from_warehouse_id = 10 OR to_warehouse_id = 10)
+  AND id NOT IN (SELECT id FROM migration_audit.jamali_w10_st_softdeleted);
+
+-- Snapshot purchase_items IDs (cheap, ~17 rows)
+CREATE TABLE migration_audit.jamali_w10_purchase_items AS
+SELECT id FROM purchase_items WHERE warehouse_id = 10;
+
+-- Snapshot warehouses soft-delete flag (1 row, kalau memang masih aktif)
+CREATE TABLE migration_audit.jamali_w10_warehouse_softdeleted AS
+SELECT id FROM warehouses WHERE id = 10 AND deleted_at IS NULL;
+
+-- STEP 0: Pre-check sanity (idempotent guards)
+DO $$
+DECLARE v_count INT;
+BEGIN
+  SELECT COUNT(*) INTO v_count FROM warehouses
+  WHERE id IN (10, 25) AND type = 'LOKASI' AND area_id = 6 AND location_id = 16;
+  IF v_count <> 2 THEN
+    RAISE EXCEPTION 'Pre-check: warehouse 10/25 schema mismatch (got % rows)', v_count;
+  END IF;
+
+  SELECT COUNT(*) INTO v_count FROM purchase_items a
+  JOIN purchase_items b ON a.purchase_id = b.purchase_id
+    AND a.product_id = b.product_id
+    AND a.id <> b.id
+  WHERE a.warehouse_id = 10 AND b.warehouse_id = 25;
+  IF v_count > 0 THEN
+    RAISE EXCEPTION 'Pre-check: % purchase_items unique conflict (purchase_id,product_id)', v_count;
+  END IF;
+END $$;
+
+-- STEP 1: Merge qty W10 -> W25 untuk overlap (pfk=NULL)
+UPDATE product_warehouses pw25
+SET qty = pw25.qty + pw10.qty
+FROM product_warehouses pw10
+WHERE pw10.warehouse_id = 10 AND pw10.project_flock_kandang_id IS NULL
+  AND pw25.warehouse_id = 25 AND pw25.project_flock_kandang_id IS NULL
+  AND pw25.product_id = pw10.product_id;
+
+-- STEP 2: Build temp mapping (W10 PW id -> W25 PW id) untuk overlap saja
+CREATE TEMP TABLE _pw_map ON COMMIT DROP AS
+SELECT pw10.id AS old_id, pw25.id AS new_id
+FROM product_warehouses pw10
+JOIN product_warehouses pw25
+  ON pw25.product_id = pw10.product_id
+ AND pw25.warehouse_id = 25
+ AND pw25.project_flock_kandang_id IS NULL
+WHERE pw10.warehouse_id = 10 AND pw10.project_flock_kandang_id IS NULL;
+
+CREATE INDEX ON _pw_map(old_id);
+
+-- STEP 3: Relink semua FK ke product_warehouses.id (hanya rows di _pw_map)
+UPDATE stock_logs SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE stock_logs.product_warehouse_id = m.old_id;
+
+UPDATE stock_allocations SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE stock_allocations.product_warehouse_id = m.old_id;
+
+UPDATE recording_eggs SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE recording_eggs.product_warehouse_id = m.old_id;
+
+UPDATE recording_stocks SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE recording_stocks.product_warehouse_id = m.old_id;
+
+UPDATE recording_depletions SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE recording_depletions.product_warehouse_id = m.old_id;
+UPDATE recording_depletions SET source_product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE recording_depletions.source_product_warehouse_id = m.old_id;
+
+UPDATE adjustment_stocks SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE adjustment_stocks.product_warehouse_id = m.old_id;
+
+UPDATE marketing_products SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE marketing_products.product_warehouse_id = m.old_id;
+
+UPDATE marketing_delivery_products SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE marketing_delivery_products.product_warehouse_id = m.old_id;
+
+UPDATE project_chickins SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE project_chickins.product_warehouse_id = m.old_id;
+
+UPDATE project_chickin_details SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE project_chickin_details.product_warehouse_id = m.old_id;
+
+UPDATE project_flock_populations SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE project_flock_populations.product_warehouse_id = m.old_id;
+
+UPDATE laying_transfers SET source_product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE laying_transfers.source_product_warehouse_id = m.old_id;
+
+UPDATE laying_transfer_sources SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE laying_transfer_sources.product_warehouse_id = m.old_id;
+
+UPDATE laying_transfer_targets SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE laying_transfer_targets.product_warehouse_id = m.old_id;
+
+UPDATE stock_transfer_details SET source_product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE stock_transfer_details.source_product_warehouse_id = m.old_id;
+UPDATE stock_transfer_details SET dest_product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE stock_transfer_details.dest_product_warehouse_id = m.old_id;
+
+UPDATE purchase_items SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE purchase_items.product_warehouse_id = m.old_id;
+
+-- FIFO v2 tables (kosong di dump 2026-05-25, defensive)
+UPDATE fifo_stock_v2_operation_log SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE fifo_stock_v2_operation_log.product_warehouse_id = m.old_id;
+UPDATE fifo_stock_v2_reflow_checkpoints SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE fifo_stock_v2_reflow_checkpoints.product_warehouse_id = m.old_id;
+UPDATE fifo_stock_v2_shadow_allocations SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE fifo_stock_v2_shadow_allocations.product_warehouse_id = m.old_id;
+
+-- STEP 4: Hard-delete W10 PW yang sudah merged (9 rows expected)
+DELETE FROM product_warehouses WHERE id IN (SELECT old_id FROM _pw_map);
+
+-- STEP 5: Sisa W10 PW (4 rows: 1188/1189/1190/1196) -> warehouse_id=25,
+-- pfk dinormalisasi ke NULL sekalian (LOKASI rule)
+UPDATE product_warehouses
+SET warehouse_id = 25, project_flock_kandang_id = NULL
+WHERE warehouse_id = 10;
+
+-- STEP 6: purchase_items.warehouse_id (17 rows)
+UPDATE purchase_items SET warehouse_id = 25 WHERE warehouse_id = 10;
+
+-- STEP 7: stock_transfers
+-- 7.1 Soft-delete self-loop (W10<->W25 akan jadi W25<->W25)
+UPDATE stock_transfers
+SET deleted_at = NOW(), updated_at = NOW()
+WHERE deleted_at IS NULL
+  AND ((from_warehouse_id = 10 AND to_warehouse_id = 25)
+    OR (from_warehouse_id = 25 AND to_warehouse_id = 10));
+
+-- 7.2 Sisa W10<->other -> 25 (12 EGG_FARM_CUTOVER ke W10)
+UPDATE stock_transfers SET from_warehouse_id = 25, updated_at = NOW() WHERE from_warehouse_id = 10;
+UPDATE stock_transfers SET to_warehouse_id = 25, updated_at = NOW() WHERE to_warehouse_id = 10;
+
+-- STEP 8: Soft-delete warehouse 10 sendiri
+UPDATE warehouses SET deleted_at = NOW(), updated_at = NOW()
+WHERE id = 10 AND deleted_at IS NULL;
+
+-- STEP 9: Post-check (fail-fast jika ada residu)
+DO $$
+DECLARE v_count INT;
+BEGIN
+  SELECT COUNT(*) INTO v_count FROM product_warehouses WHERE warehouse_id = 10;
+  IF v_count <> 0 THEN RAISE EXCEPTION 'product_warehouses W10 residual %', v_count; END IF;
+
+  SELECT COUNT(*) INTO v_count FROM purchase_items WHERE warehouse_id = 10;
+  IF v_count <> 0 THEN RAISE EXCEPTION 'purchase_items W10 residual %', v_count; END IF;
+
+  SELECT COUNT(*) INTO v_count FROM stock_transfers
+  WHERE deleted_at IS NULL AND (from_warehouse_id = 10 OR to_warehouse_id = 10);
+  IF v_count <> 0 THEN RAISE EXCEPTION 'stock_transfers W10 residual %', v_count; END IF;
+END $$;
+
+COMMIT;

internal/database/migrations/20260528123243_fix_stock_log_drift_jamali_merge.down.sql

@@ -0,0 +1,29 @@
+BEGIN;
+
+-- ============================================================
+-- Rollback stock_log drift fix: DELETE corrective rows yang di-insert UP.
+-- IDs ditarik dari audit table `migration_audit.jamali_w10_stocklog_corrections`.
+-- Setelah delete, `last_stock_log.stock` kembali ke nilai pre-fix (drift muncul lagi).
+-- ============================================================
+
+-- Guard: audit table harus ada
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1 FROM information_schema.tables
+    WHERE table_schema = 'migration_audit'
+      AND table_name = 'jamali_w10_stocklog_corrections'
+  ) THEN
+    RAISE EXCEPTION
+      'Audit table migration_audit.jamali_w10_stocklog_corrections tidak ditemukan. UP belum dijalankan atau audit sudah di-drop.';
+  END IF;
+END $$;
+
+-- DELETE corrective stock_logs yang di-insert oleh UP
+DELETE FROM stock_logs
+WHERE id IN (SELECT stock_log_id FROM migration_audit.jamali_w10_stocklog_corrections);
+
+-- Cleanup audit table
+DROP TABLE migration_audit.jamali_w10_stocklog_corrections;
+
+COMMIT;

internal/database/migrations/20260528123243_fix_stock_log_drift_jamali_merge.up.sql

@@ -0,0 +1,111 @@
+BEGIN;
+
+-- ============================================================
+-- Fix stock_log drift pasca-merge warehouse Jamali (NON_AKTIF) -> Gudang Farm Jamali.
+-- Follow-up migration setelah 20260528121631_normalize_warehouse_jamali_10_to_25.
+--
+-- Setelah merge, `stock_logs.stock` (running ledger) drift dari
+-- `product_warehouses.qty` karena: pre-existing drift di W10 + W25 sources,
+-- plus FIFO reflow yang trigger pasca-merge (Recording-Edit) recompute
+-- pw.qty tapi stock_logs tidak ikut update.
+--
+-- Migration ini insert 1 ADJUSTMENT stock_log corrective per PW yang drift
+-- supaya `last_stock_log.stock = pw.qty`. Logic ekivalen dengan
+-- `cmd/fix-stock-log-drift`.
+--
+-- Karakteristik dynamic:
+--   - Tidak hardcode PW IDs atau drift values
+--   - Iterate via merge target + W10-only kept PWs (data-driven dari snapshot)
+--   - Per PW: hitung drift runtime, skip kalau negligible (< 0.001) atau no logs
+--   - Track stock_log IDs yang di-insert untuk DOWN reverse
+-- ============================================================
+
+-- Guard: previous migration (normalisasi) audit harus ada
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1 FROM information_schema.tables
+    WHERE table_schema = 'migration_audit'
+      AND table_name = 'jamali_w10_qty_merge'
+  ) THEN
+    RAISE EXCEPTION
+      'Migration 20260528121631 (normalize_warehouse_jamali) belum dijalankan atau audit-nya sudah di-drop. Apply UP-nya dulu sebelum migration ini.';
+  END IF;
+END $$;
+
+-- Audit table untuk track stock_log IDs yang di-insert (untuk DOWN reverse)
+DROP TABLE IF EXISTS migration_audit.jamali_w10_stocklog_corrections;
+CREATE TABLE migration_audit.jamali_w10_stocklog_corrections (
+  stock_log_id BIGINT NOT NULL PRIMARY KEY,
+  product_warehouse_id BIGINT NOT NULL,
+  drift NUMERIC(15,3) NOT NULL,
+  inserted_at TIMESTAMPTZ DEFAULT NOW()
+);
+
+-- Insert corrective ADJUSTMENT stock_log untuk tiap PW yang drift
+DO $$
+DECLARE
+  rec RECORD;
+  v_last_log_stock NUMERIC(15,3);
+  v_drift NUMERIC(15,3);
+  v_new_log_id BIGINT;
+  v_inserts INT := 0;
+BEGIN
+  FOR rec IN (
+    SELECT pw.id AS pw_id, pw.qty AS qty
+    FROM product_warehouses pw
+    WHERE pw.id IN (
+      -- Merge target W25 PWs (9 rows)
+      SELECT target_pw_id FROM migration_audit.jamali_w10_qty_merge
+      UNION
+      -- W10-only PWs yang di-update warehouse_id 10->25 (4 rows)
+      SELECT id FROM migration_audit.jamali_w10_pw_w10only_snapshot
+    )
+  ) LOOP
+    -- Ambil stock akhir di stock_logs ledger
+    SELECT stock INTO v_last_log_stock
+    FROM stock_logs
+    WHERE product_warehouse_id = rec.pw_id
+    ORDER BY id DESC
+    LIMIT 1;
+
+    -- PW tanpa stock_logs entry (mis. 1188/1189/1190 ayam) -> skip
+    IF v_last_log_stock IS NULL THEN
+      CONTINUE;
+    END IF;
+
+    v_drift := rec.qty - v_last_log_stock;
+
+    -- Drift negligible -> skip
+    IF ABS(v_drift) < 0.001 THEN
+      CONTINUE;
+    END IF;
+
+    -- Insert corrective ADJUSTMENT stock_log
+    INSERT INTO stock_logs (
+      product_warehouse_id, loggable_type, loggable_id,
+      notes, increase, decrease, stock, created_by, created_at
+    ) VALUES (
+      rec.pw_id,
+      'ADJUSTMENT',
+      0,
+      'Koreksi stock_log drift pasca-merge warehouse Jamali (migration 20260528123243)',
+      CASE WHEN v_drift > 0 THEN v_drift ELSE 0 END,
+      CASE WHEN v_drift < 0 THEN -v_drift ELSE 0 END,
+      rec.qty,
+      1,
+      NOW()
+    ) RETURNING id INTO v_new_log_id;
+
+    -- Track ke audit table untuk DOWN
+    INSERT INTO migration_audit.jamali_w10_stocklog_corrections (
+      stock_log_id, product_warehouse_id, drift
+    ) VALUES (v_new_log_id, rec.pw_id, v_drift);
+
+    v_inserts := v_inserts + 1;
+  END LOOP;
+
+  RAISE NOTICE 'Inserted % corrective stock_logs to align ledger with pw.qty', v_inserts;
+END $$;
+
+COMMIT;

internal/modules/marketing/controllers/deliveryorder.export.go

@@ -75,9 +75,18 @@ func setMarketingExportColumns(file *excelize.File, sheet string) error {
 		"B": 14,
 		"C": 18,
 		"D": 20,
-		"E": 18,
-		"F": 60,
-		"G": 24,
+		"E": 14,
+		"F": 40,
+		"G": 10,
+		"H": 12,
+		"I": 12,
+		"J": 12,
+		"K": 16,
+		"L": 16,
+		"M": 18,
+		"N": 18,
+		"O": 18,
+		"P": 24,
 	}
 
 	for col, width := range columnWidths {
@@ -95,13 +104,22 @@ func setMarketingExportColumns(file *excelize.File, sheet string) error {
 
 func setMarketingExportHeaders(file *excelize.File, sheet string) error {
 	headers := []string{
-		"No. Order",
-		"Tanggal",
-		"Status",
-		"Customer",
-		"Grand Total",
-		"Products",
-		"Notes",
+		"No. Order",    // A
+		"Tanggal",      // B
+		"Status",       // C
+		"Customer",     // D
+		"Tipe",         // E
+		"Nama Produk",  // F
+		"Week",         // G
+		"Jumlah",       // H
+		"Satuan",       // I
+		"Qty Peti",     // J
+		"Berat Rata-rata (kg)", // K
+		"Total Berat (kg)",     // L
+		"Harga Satuan", // M
+		"Total Harga",  // N
+		"Grand Total",  // O
+		"Catatan",      // P
 	}
 
 	for i, header := range headers {
@@ -130,7 +148,7 @@ func setMarketingExportHeaders(file *excelize.File, sheet string) error {
 		return err
 	}
 
-	return file.SetCellStyle(sheet, "A1", "G1", headerStyle)
+	return file.SetCellStyle(sheet, "A1", "P1", headerStyle)
 }
 
 func setMarketingExportRows(file *excelize.File, sheet string, items []dto.MarketingListDTO) error {
@@ -138,70 +156,154 @@ func setMarketingExportRows(file *excelize.File, sheet string, items []dto.Marke
 		return nil
 	}
 
-	for i, item := range items {
-		rowNumber := i + 2
-		if err := file.SetCellValue(sheet, "A"+strconv.Itoa(rowNumber), safeMarketingExportText(item.SoNumber)); err != nil {
-			return err
+	row := 1
+	for _, item := range items {
+		soNumber := safeMarketingExportText(item.SoNumber)
+		soDate := formatMarketingExportDate(item.SoDate)
+		status := formatMarketingExportStatus(item)
+		customer := safeMarketingExportText(item.Customer.Name)
+		grandTotal := sumMarketingGrandTotal(item.SalesOrder)
+		notes := safeMarketingExportText(item.Notes)
+
+		if len(item.SalesOrder) == 0 {
+			row++
+			r := strconv.Itoa(row)
+			vals := map[string]interface{}{
+				"A": soNumber, "B": soDate, "C": status, "D": customer,
+				"E": "-", "F": "-", "G": "-", "H": "-", "I": "-", "J": "-",
+				"K": "-", "L": "-", "M": "-", "N": "-",
+				"O": grandTotal, "P": notes,
+			}
+			for col, val := range vals {
+				if err := file.SetCellValue(sheet, col+r, val); err != nil {
+					return err
+				}
+			}
+			continue
 		}
-		if err := file.SetCellValue(sheet, "B"+strconv.Itoa(rowNumber), formatMarketingExportDate(item.SoDate)); err != nil {
-			return err
-		}
-		if err := file.SetCellValue(sheet, "C"+strconv.Itoa(rowNumber), formatMarketingExportStatus(item)); err != nil {
-			return err
-		}
-		if err := file.SetCellValue(sheet, "D"+strconv.Itoa(rowNumber), safeMarketingExportText(item.Customer.Name)); err != nil {
-			return err
-		}
-		if err := file.SetCellValue(sheet, "E"+strconv.Itoa(rowNumber), sumMarketingGrandTotal(item.SalesOrder)); err != nil {
-			return err
-		}
-		if err := file.SetCellValue(sheet, "F"+strconv.Itoa(rowNumber), formatMarketingProducts(item.SalesOrder)); err != nil {
-			return err
-		}
-		if err := file.SetCellValue(sheet, "G"+strconv.Itoa(rowNumber), safeMarketingExportText(item.Notes)); err != nil {
-			return err
+
+		for _, prod := range item.SalesOrder {
+			row++
+			r := strconv.Itoa(row)
+
+			productName := "-"
+			if prod.ProductWarehouse != nil && prod.ProductWarehouse.Product != nil {
+				if n := strings.TrimSpace(prod.ProductWarehouse.Product.Name); n != "" {
+					productName = n
+				}
+			}
+
+			week := "-"
+			if prod.Week != nil {
+				week = strconv.Itoa(*prod.Week)
+			}
+
+			satuan := "-"
+			if prod.ConvertionUnit != nil && strings.TrimSpace(*prod.ConvertionUnit) != "" {
+				satuan = *prod.ConvertionUnit
+			}
+
+			if err := file.SetCellValue(sheet, "A"+r, soNumber); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "B"+r, soDate); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "C"+r, status); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "D"+r, customer); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "E"+r, safeMarketingExportText(prod.MarketingType)); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "F"+r, productName); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "G"+r, week); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "H"+r, prod.Qty); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "I"+r, satuan); err != nil {
+				return err
+			}
+			if prod.TotalPeti != nil {
+				if err := file.SetCellValue(sheet, "J"+r, *prod.TotalPeti); err != nil {
+					return err
+				}
+			} else {
+				if err := file.SetCellValue(sheet, "J"+r, "-"); err != nil {
+					return err
+				}
+			}
+			if err := file.SetCellValue(sheet, "K"+r, prod.AvgWeight); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "L"+r, prod.TotalWeight); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "M"+r, prod.UnitPrice); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "N"+r, prod.TotalPrice); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "O"+r, grandTotal); err != nil {
+				return err
+			}
+			if err := file.SetCellValue(sheet, "P"+r, notes); err != nil {
+				return err
+			}
 		}
 	}
 
-	lastRow := len(items) + 1
+	lastRow := row
+	lastRowStr := strconv.Itoa(lastRow)
+	border := []excelize.Border{
+		{Type: "left", Color: "D1D5DB", Style: 1},
+		{Type: "top", Color: "D1D5DB", Style: 1},
+		{Type: "bottom", Color: "D1D5DB", Style: 1},
+		{Type: "right", Color: "D1D5DB", Style: 1},
+	}
+
 	dataStyle, err := file.NewStyle(&excelize.Style{
-		Alignment: &excelize.Alignment{
-			Horizontal: "left",
-			Vertical:   "center",
-			WrapText:   true,
-		},
-		Border: []excelize.Border{
-			{Type: "left", Color: "D1D5DB", Style: 1},
-			{Type: "top", Color: "D1D5DB", Style: 1},
-			{Type: "bottom", Color: "D1D5DB", Style: 1},
-			{Type: "right", Color: "D1D5DB", Style: 1},
-		},
+		Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center", WrapText: true},
+		Border:    border,
 	})
 	if err != nil {
 		return err
 	}
-
-	if err := file.SetCellStyle(sheet, "A2", "G"+strconv.Itoa(lastRow), dataStyle); err != nil {
+	if err := file.SetCellStyle(sheet, "A2", "P"+lastRowStr, dataStyle); err != nil {
 		return err
 	}
 
-	moneyStyle, err := file.NewStyle(&excelize.Style{
-		Alignment: &excelize.Alignment{
-			Horizontal: "right",
-			Vertical:   "center",
-		},
-		Border: []excelize.Border{
-			{Type: "left", Color: "D1D5DB", Style: 1},
-			{Type: "top", Color: "D1D5DB", Style: 1},
-			{Type: "bottom", Color: "D1D5DB", Style: 1},
-			{Type: "right", Color: "D1D5DB", Style: 1},
-		},
+	numberStyle, err := file.NewStyle(&excelize.Style{
+		Alignment: &excelize.Alignment{Horizontal: "right", Vertical: "center"},
+		Border:    border,
 	})
 	if err != nil {
 		return err
 	}
+	if err := file.SetCellStyle(sheet, "K2", "O"+lastRowStr, numberStyle); err != nil {
+		return err
+	}
 
-	return file.SetCellStyle(sheet, "E2", "E"+strconv.Itoa(lastRow), moneyStyle)
+	centerStyle, err := file.NewStyle(&excelize.Style{
+		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
+		Border:    border,
+	})
+	if err != nil {
+		return err
+	}
+	for _, col := range []string{"G", "H", "J"} {
+		if err := file.SetCellStyle(sheet, col+"2", col+lastRowStr, centerStyle); err != nil {
+			return err
+		}
+	}
+	return nil
 }
 
 func formatMarketingExportDate(value time.Time) string {
@@ -225,36 +327,6 @@ func formatMarketingExportStatus(item dto.MarketingListDTO) string {
 	return safeMarketingExportText(item.LatestApproval.StepName)
 }
 
-func formatMarketingProducts(items []dto.DeliveryMarketingProductDTO) string {
-	if len(items) == 0 {
-		return "-"
-	}
-
-	seen := make(map[string]struct{})
-	names := make([]string, 0, len(items))
-	for _, item := range items {
-		if item.ProductWarehouse == nil || item.ProductWarehouse.Product == nil {
-			continue
-		}
-
-		name := strings.TrimSpace(item.ProductWarehouse.Product.Name)
-		if name == "" {
-			continue
-		}
-
-		if _, exists := seen[name]; exists {
-			continue
-		}
-		seen[name] = struct{}{}
-		names = append(names, name)
-	}
-
-	if len(names) == 0 {
-		return "-"
-	}
-
-	return strings.Join(names, ", ")
-}
 
 func sumMarketingGrandTotal(items []dto.DeliveryMarketingProductDTO) float64 {
 	total := 0.0

internal/modules/marketing/dto/deliveryorder.dto.go

@@ -29,6 +29,7 @@ type MarketingListDTO struct {
 	SalesPerson    userDTO.UserRelationDTO         `json:"sales_person"`
 	SoDocs         string                          `json:"so_docs"`
 	SalesOrder     []DeliveryMarketingProductDTO   `json:"sales_order"`
+	DeliveryOrder  []DeliveryGroupDTO              `json:"delivery_order"`
 	CreatedUser    userDTO.UserRelationDTO         `json:"created_user"`
 	CreatedAt      time.Time                       `json:"created_at"`
 	UpdatedAt      time.Time                       `json:"updated_at"`
@@ -203,6 +204,7 @@ func ToMarketingListDTO(marketing *entity.Marketing, deliveryProducts []entity.M
 		SalesPerson:          salesPerson,
 		SoDocs:               marketing.SoDocs,
 		SalesOrder:           salesOrderProducts,
+		DeliveryOrder:        extractDeliveryGroupsFromProducts(marketing),
 		CreatedUser:          createdUser,
 		CreatedAt:            marketing.CreatedAt,
 		UpdatedAt:            marketing.UpdatedAt,
@@ -376,6 +378,23 @@ func GenerateDeliveryOrderNumber(soNumber string, deliveryDate *time.Time, wareh
 	return numberPrefix
 }
 
+func extractDeliveryGroupsFromProducts(marketing *entity.Marketing) []DeliveryGroupDTO {
+	var dps []MarketingDeliveryProductDTO
+	for _, product := range marketing.Products {
+		if product.DeliveryProduct == nil || product.DeliveryProduct.DeliveryDate == nil {
+			continue
+		}
+		dp := ToMarketingDeliveryProductDTO(*product.DeliveryProduct)
+		if product.ProductWarehouse.Id != 0 {
+			mapped := productwarehouseDTO.ToProductWarehouseNestedDTO(product.ProductWarehouse)
+			dp.ProductWarehouse = &mapped
+		}
+		dp.ConvertionUnit = product.ConvertionUnit
+		dps = append(dps, dp)
+	}
+	return groupDeliveryProducts(dps, marketing.SoNumber)
+}
+
 func collectDoNumbers(marketing *entity.Marketing) []string {
 	if marketing == nil || len(marketing.Products) == 0 {
 		return nil

internal/modules/purchases/controllers/purchase.export.go

@@ -435,6 +435,21 @@ func formatPurchaseExportEntityStatus(purchase *entity.Purchase) string {
 	return safePurchaseExportText(purchase.LatestApproval.StepName)
 }
 
+var purchaseIndonesianMonths = map[time.Month]string{
+	time.January:   "Jan",
+	time.February:  "Feb",
+	time.March:     "Mar",
+	time.April:     "Apr",
+	time.May:       "Mei",
+	time.June:      "Jun",
+	time.July:      "Jul",
+	time.August:    "Ags",
+	time.September: "Sep",
+	time.October:   "Okt",
+	time.November:  "Nov",
+	time.December:  "Des",
+}
+
 func formatPurchaseExportDate(value *time.Time) string {
 	if value == nil || value.IsZero() {
 		return "-"
@@ -446,7 +461,8 @@ func formatPurchaseExportDate(value *time.Time) string {
 		t = t.In(location)
 	}
 
-	return t.Format("02-01-2006")
+	month := purchaseIndonesianMonths[t.Month()]
+	return fmt.Sprintf("%d-%s-%02d", t.Day(), month, t.Year()%100)
 }
 
 func safePurchaseExportPointerText(value *string) string {

internal/modules/purchases/services/purchase.service.go

@@ -264,6 +264,14 @@ func (s *purchaseService) GetAll(c *fiber.Ctx, params *validation.Query) ([]enti
 
 		sortBy := strings.TrimSpace(params.SortBy)
 		sortOrder := strings.ToUpper(strings.TrimSpace(params.SortOrder))
+
+		if sortBy == "" && (filterBy == "po_date" || filterBy == "due_date" || filterBy == "received_date" || filterBy == "created_at") {
+			sortBy = filterBy
+			if sortOrder == "" {
+				sortOrder = "ASC"
+			}
+		}
+
 		if sortOrder == "" {
 			sortOrder = "DESC"
 		}
@@ -2240,6 +2248,11 @@ func (s *purchaseService) attachLatestApprovals(ctx context.Context, items []ent
 }
 
 func parsePurchaseDateRangeForQuery(fromStr, toStr, fieldName string) (*time.Time, *time.Time, error) {
+	jakartaLoc, err := time.LoadLocation("Asia/Jakarta")
+	if err != nil {
+		jakartaLoc = time.FixedZone("WIB", 7*60*60)
+	}
+
 	var fromPtr *time.Time
 	var toPtr *time.Time
 
@@ -2248,7 +2261,8 @@ func parsePurchaseDateRangeForQuery(fromStr, toStr, fieldName string) (*time.Tim
 		if err != nil {
 			return nil, nil, errors.New(fieldName + "_from must use format YYYY-MM-DD")
 		}
-		fromPtr = &parsed
+		t := time.Date(parsed.Year(), parsed.Month(), parsed.Day(), 0, 0, 0, 0, jakartaLoc)
+		fromPtr = &t
 	}
 
 	if strings.TrimSpace(toStr) != "" {
@@ -2256,7 +2270,8 @@ func parsePurchaseDateRangeForQuery(fromStr, toStr, fieldName string) (*time.Tim
 		if err != nil {
 			return nil, nil, errors.New(fieldName + "_to must use format YYYY-MM-DD")
 		}
-		nextDay := parsed.AddDate(0, 0, 1)
+		t := time.Date(parsed.Year(), parsed.Month(), parsed.Day(), 0, 0, 0, 0, jakartaLoc)
+		nextDay := t.AddDate(0, 0, 1)
 		toPtr = &nextDay
 	}
 

internal/modules/repports/controllers/repport.balance_monitoring.export.go

@@ -0,0 +1,286 @@
+package controller
+
+import (
+	"fmt"
+	"strconv"
+	"strings"
+	"time"
+
+	"github.com/gofiber/fiber/v2"
+	"github.com/xuri/excelize/v2"
+	"gitlab.com/mbugroup/lti-api.git/internal/modules/repports/dto"
+)
+
+func isBalanceMonitoringExcelExportRequest(c *fiber.Ctx) bool {
+	return strings.EqualFold(strings.TrimSpace(c.Query("export")), "excel")
+}
+
+func exportBalanceMonitoringExcel(c *fiber.Ctx, items []dto.BalanceMonitoringRowDTO, totals dto.BalanceMonitoringTotalsDTO) error {
+	content, err := buildBalanceMonitoringWorkbook(items, totals)
+	if err != nil {
+		return fiber.NewError(fiber.StatusInternalServerError, "failed to generate excel file")
+	}
+
+	filename := fmt.Sprintf("laporan-balance-monitoring-%s.xlsx", time.Now().Format("2006-01-02-1504"))
+	c.Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
+	c.Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, filename))
+	return c.Status(fiber.StatusOK).Send(content)
+}
+
+func buildBalanceMonitoringWorkbook(items []dto.BalanceMonitoringRowDTO, totals dto.BalanceMonitoringTotalsDTO) ([]byte, error) {
+	file := excelize.NewFile()
+	defer file.Close()
+
+	const sheet = "Balance Monitoring"
+	defaultSheet := file.GetSheetName(file.GetActiveSheetIndex())
+	if defaultSheet != sheet {
+		if err := file.SetSheetName(defaultSheet, sheet); err != nil {
+			return nil, err
+		}
+	}
+
+	if err := setBalanceMonitoringColumns(file, sheet); err != nil {
+		return nil, err
+	}
+	if err := setBalanceMonitoringHeaders(file, sheet); err != nil {
+		return nil, err
+	}
+	if err := writeBalanceMonitoringRows(file, sheet, items, totals); err != nil {
+		return nil, err
+	}
+	if err := file.SetPanes(sheet, &excelize.Panes{
+		Freeze:      true,
+		YSplit:      2,
+		TopLeftCell: "A3",
+		ActivePane:  "bottomLeft",
+	}); err != nil {
+		return nil, err
+	}
+
+	buf, err := file.WriteToBuffer()
+	if err != nil {
+		return nil, err
+	}
+	return buf.Bytes(), nil
+}
+
+var bmColumnWidths = map[string]float64{
+	"A": 5,
+	"B": 28,
+	"C": 18,
+	"D": 12,
+	"E": 12,
+	"F": 20,
+	"G": 12,
+	"H": 12,
+	"I": 20,
+	"J": 20,
+	"K": 18,
+	"L": 12,
+	"M": 16,
+	"N": 20,
+}
+
+func setBalanceMonitoringColumns(file *excelize.File, sheet string) error {
+	for col, width := range bmColumnWidths {
+		if err := file.SetColWidth(sheet, col, col, width); err != nil {
+			return err
+		}
+	}
+	if err := file.SetRowHeight(sheet, 1, 24); err != nil {
+		return err
+	}
+	return file.SetRowHeight(sheet, 2, 24)
+}
+
+func setBalanceMonitoringHeaders(file *excelize.File, sheet string) error {
+	borderStyle := []excelize.Border{
+		{Type: "left", Color: "000000", Style: 1},
+		{Type: "top", Color: "000000", Style: 1},
+		{Type: "bottom", Color: "000000", Style: 1},
+		{Type: "right", Color: "000000", Style: 1},
+	}
+	headerStyle, err := file.NewStyle(&excelize.Style{
+		Font: &excelize.Font{Bold: true, Color: "FFFFFF", Family: "Arial", Size: 10},
+		Fill: excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"4472C4"}},
+		Alignment: &excelize.Alignment{
+			Horizontal: "center",
+			Vertical:   "center",
+			WrapText:   true,
+		},
+		Border: borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	// Single-column headers: merge rows 1 and 2 vertically
+	singleColHeaders := map[string]string{
+		"A": "No",
+		"B": "Customer",
+		"C": "Saldo Awal",
+		"J": "Penjualan Trading",
+		"K": "Pembayaran",
+		"L": "Aging",
+		"M": "Aging Rata-Rata",
+		"N": "Saldo Akhir",
+	}
+	for col, header := range singleColHeaders {
+		if err := file.SetCellValue(sheet, col+"1", header); err != nil {
+			return err
+		}
+		if err := file.MergeCell(sheet, col+"1", col+"2"); err != nil {
+			return err
+		}
+	}
+
+	// Group headers: merge columns horizontally in row 1
+	if err := file.SetCellValue(sheet, "D1", "Penjualan Ayam"); err != nil {
+		return err
+	}
+	if err := file.MergeCell(sheet, "D1", "F1"); err != nil {
+		return err
+	}
+	if err := file.SetCellValue(sheet, "G1", "Penjualan Telur"); err != nil {
+		return err
+	}
+	if err := file.MergeCell(sheet, "G1", "I1"); err != nil {
+		return err
+	}
+
+	// Sub-column headers in row 2
+	subHeaders := map[string]string{
+		"D": "Ekor",
+		"E": "Kg",
+		"F": "Nominal",
+		"G": "Butir",
+		"H": "Kg",
+		"I": "Nominal",
+	}
+	for col, header := range subHeaders {
+		if err := file.SetCellValue(sheet, col+"2", header); err != nil {
+			return err
+		}
+	}
+
+	return file.SetCellStyle(sheet, "A1", "N2", headerStyle)
+}
+
+func writeBalanceMonitoringRows(file *excelize.File, sheet string, items []dto.BalanceMonitoringRowDTO, totals dto.BalanceMonitoringTotalsDTO) error {
+	borderStyle := []excelize.Border{
+		{Type: "left", Color: "000000", Style: 1},
+		{Type: "top", Color: "000000", Style: 1},
+		{Type: "bottom", Color: "000000", Style: 1},
+		{Type: "right", Color: "000000", Style: 1},
+	}
+
+	dataStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Color: "000000", Family: "Arial", Size: 10},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	totalStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Bold: true, Color: "000000", Family: "Arial", Size: 10},
+		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"E2EFDA"}},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	redDataStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Color: "FF0000", Family: "Arial", Size: 10},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	redTotalStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Bold: true, Color: "FF0000", Family: "Arial", Size: 10},
+		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"E2EFDA"}},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	for i, row := range items {
+		rowNum := i + 3
+		rowStr := strconv.Itoa(rowNum)
+
+		cells := map[string]interface{}{
+			"A": i + 1,
+			"B": row.Customer.Name,
+			"C": row.SaldoAwal,
+			"D": row.PenjualanAyam.Ekor,
+			"E": row.PenjualanAyam.Kg,
+			"F": row.PenjualanAyam.Nominal,
+			"G": row.PenjualanTelur.Butir,
+			"H": row.PenjualanTelur.Kg,
+			"I": row.PenjualanTelur.Nominal,
+			"J": row.PenjualanTrading.Nominal,
+			"K": row.Pembayaran,
+			"L": fmt.Sprintf("%d hari", row.Aging),
+			"M": formatBMAging(row.AgingRataRata),
+			"N": row.SaldoAkhir,
+		}
+		for col, val := range cells {
+			if err := file.SetCellValue(sheet, col+rowStr, val); err != nil {
+				return err
+			}
+		}
+		if err := file.SetCellStyle(sheet, "A"+rowStr, "N"+rowStr, dataStyle); err != nil {
+			return err
+		}
+		if row.SaldoAkhir < 0 {
+			if err := file.SetCellStyle(sheet, "N"+rowStr, "N"+rowStr, redDataStyle); err != nil {
+				return err
+			}
+		}
+	}
+
+	// Totals row
+	totalRowStr := strconv.Itoa(len(items) + 3)
+	totalCells := map[string]interface{}{
+		"A": "Total",
+		"C": totals.SaldoAwal,
+		"D": totals.PenjualanAyam.Ekor,
+		"E": totals.PenjualanAyam.Kg,
+		"F": totals.PenjualanAyam.Nominal,
+		"G": totals.PenjualanTelur.Butir,
+		"H": totals.PenjualanTelur.Kg,
+		"I": totals.PenjualanTelur.Nominal,
+		"J": totals.PenjualanTrading.Nominal,
+		"K": totals.Pembayaran,
+		"N": totals.SaldoAkhir,
+	}
+	for col, val := range totalCells {
+		if err := file.SetCellValue(sheet, col+totalRowStr, val); err != nil {
+			return err
+		}
+	}
+	if err := file.SetCellStyle(sheet, "A"+totalRowStr, "N"+totalRowStr, totalStyle); err != nil {
+		return err
+	}
+	if totals.SaldoAkhir < 0 {
+		if err := file.SetCellStyle(sheet, "N"+totalRowStr, "N"+totalRowStr, redTotalStyle); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func formatBMAging(v float64) string {
+	s := strconv.FormatFloat(v, 'f', 2, 64)
+	s = strings.ReplaceAll(s, ".", ",")
+	return s + " hari"
+}

internal/modules/repports/controllers/repport.controller.go

@@ -324,6 +324,13 @@ func (c *RepportController) GetPurchaseSupplier(ctx *fiber.Ctx) error {
 		return err
 	}
 
+	if isPurchaseSupplierExcelExportRequest(ctx) {
+		return exportPurchaseSupplierExcel(ctx, result)
+	}
+	if isPurchaseSupplierExcelAllExportRequest(ctx) {
+		return exportPurchaseSupplierExcelAll(ctx, result)
+	}
+
 	filters := map[string]interface{}{
 		"area_id":             query.AreaIDs,
 		"supplier_id":         query.SupplierIDs,
@@ -555,6 +562,10 @@ func (c *RepportController) GetBalanceMonitoring(ctx *fiber.Ctx) error {
 		return err
 	}
 
+	if isBalanceMonitoringExcelExportRequest(ctx) {
+		return exportBalanceMonitoringExcel(ctx, result, totals)
+	}
+
 	limit := query.Limit
 	if limit < 1 {
 		limit = 10

internal/modules/repports/controllers/repport.purchase_supplier.export.go

@@ -0,0 +1,415 @@
+package controller
+
+import (
+	"fmt"
+	"strings"
+	"time"
+
+	"github.com/gofiber/fiber/v2"
+	"github.com/xuri/excelize/v2"
+	"gitlab.com/mbugroup/lti-api.git/internal/modules/repports/dto"
+)
+
+func isPurchaseSupplierExcelExportRequest(c *fiber.Ctx) bool {
+	return strings.EqualFold(strings.TrimSpace(c.Query("export")), "excel")
+}
+
+func isPurchaseSupplierExcelAllExportRequest(c *fiber.Ctx) bool {
+	return strings.EqualFold(strings.TrimSpace(c.Query("export")), "excel-all")
+}
+
+func exportPurchaseSupplierExcel(c *fiber.Ctx, items []dto.PurchaseSupplierDTO) error {
+	content, err := buildPurchaseSupplierWorkbook(items)
+	if err != nil {
+		return fiber.NewError(fiber.StatusInternalServerError, "failed to generate excel file")
+	}
+
+	filename := fmt.Sprintf("laporan-pembelian-supplier-%s.xlsx", time.Now().Format("2006-01-02-1504"))
+	c.Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
+	c.Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, filename))
+	return c.Status(fiber.StatusOK).Send(content)
+}
+
+func exportPurchaseSupplierExcelAll(c *fiber.Ctx, items []dto.PurchaseSupplierDTO) error {
+	content, err := buildPurchaseSupplierAllWorkbook(items)
+	if err != nil {
+		return fiber.NewError(fiber.StatusInternalServerError, "failed to generate excel file")
+	}
+
+	filename := fmt.Sprintf("laporan-pembelian-supplier-all-%s.xlsx", time.Now().Format("2006-01-02-1504"))
+	c.Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
+	c.Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, filename))
+	return c.Status(fiber.StatusOK).Send(content)
+}
+
+// buildPurchaseSupplierWorkbook creates a workbook with one sheet per supplier.
+func buildPurchaseSupplierWorkbook(items []dto.PurchaseSupplierDTO) ([]byte, error) {
+	file := excelize.NewFile()
+	defer file.Close()
+
+	defaultSheet := file.GetSheetName(file.GetActiveSheetIndex())
+
+	if len(items) == 0 {
+		if err := writePurchaseSupplierSheet(file, defaultSheet, dto.PurchaseSupplierDTO{}); err != nil {
+			return nil, err
+		}
+		buf, err := file.WriteToBuffer()
+		if err != nil {
+			return nil, err
+		}
+		return buf.Bytes(), nil
+	}
+
+	for idx, item := range items {
+		sheetName := sanitizePurchaseSupplierSheetName(purchaseSupplierName(item))
+		if sheetName == "" {
+			sheetName = fmt.Sprintf("Supplier %d", idx+1)
+		}
+
+		if idx == 0 {
+			if defaultSheet != sheetName {
+				if err := file.SetSheetName(defaultSheet, sheetName); err != nil {
+					return nil, err
+				}
+			}
+		} else {
+			if _, err := file.NewSheet(sheetName); err != nil {
+				return nil, err
+			}
+		}
+
+		if err := writePurchaseSupplierSheet(file, sheetName, item); err != nil {
+			return nil, err
+		}
+	}
+
+	buf, err := file.WriteToBuffer()
+	if err != nil {
+		return nil, err
+	}
+	return buf.Bytes(), nil
+}
+
+// buildPurchaseSupplierAllWorkbook creates a single-sheet workbook with all suppliers.
+func buildPurchaseSupplierAllWorkbook(items []dto.PurchaseSupplierDTO) ([]byte, error) {
+	file := excelize.NewFile()
+	defer file.Close()
+
+	const sheet = "Rekap Pembelian Supplier"
+	defaultSheet := file.GetSheetName(file.GetActiveSheetIndex())
+	if defaultSheet != sheet {
+		if err := file.SetSheetName(defaultSheet, sheet); err != nil {
+			return nil, err
+		}
+	}
+
+	if err := setPurchaseSupplierAllColumns(file, sheet); err != nil {
+		return nil, err
+	}
+	if err := setPurchaseSupplierAllHeaders(file, sheet); err != nil {
+		return nil, err
+	}
+	if err := writePurchaseSupplierAllRows(file, sheet, items); err != nil {
+		return nil, err
+	}
+	if err := file.SetPanes(sheet, &excelize.Panes{
+		Freeze:      true,
+		YSplit:      1,
+		TopLeftCell: "A2",
+		ActivePane:  "bottomLeft",
+	}); err != nil {
+		return nil, err
+	}
+
+	buf, err := file.WriteToBuffer()
+	if err != nil {
+		return nil, err
+	}
+	return buf.Bytes(), nil
+}
+
+var purchaseSupplierSheetHeaders = []string{
+	"No",
+	"Tanggal Terima",
+	"Tanggal PO",
+	"No. Referensi",
+	"Nama Produk",
+	"Tujuan",
+	"QTY",
+	"Harga Beli (Rp)",
+	"Value Harga Beli (Rp)",
+	"Transport (Rp)",
+	"Value Transport (Rp)",
+	"Jumlah (Rp)",
+	"Ekspedisi",
+	"Surat Jalan",
+}
+
+var purchaseSupplierAllSheetHeaders = append([]string{"Supplier"}, purchaseSupplierSheetHeaders...)
+
+var purchaseSupplierSheetColumnWidths = map[string]float64{
+	"A": 5,
+	"B": 14,
+	"C": 12,
+	"D": 16,
+	"E": 20,
+	"F": 20,
+	"G": 10,
+	"H": 20,
+	"I": 20,
+	"J": 22,
+	"K": 22,
+	"L": 16,
+	"M": 20,
+	"N": 20,
+}
+
+var purchaseSupplierAllSheetColumnWidths = map[string]float64{
+	"A": 24,
+	"B": 6,
+	"C": 14,
+	"D": 12,
+	"E": 16,
+	"F": 20,
+	"G": 20,
+	"H": 10,
+	"I": 20,
+	"J": 20,
+	"K": 22,
+	"L": 22,
+	"M": 16,
+	"N": 20,
+	"O": 20,
+}
+
+func writePurchaseSupplierSheet(file *excelize.File, sheet string, item dto.PurchaseSupplierDTO) error {
+	for col, width := range purchaseSupplierSheetColumnWidths {
+		if err := file.SetColWidth(sheet, col, col, width); err != nil {
+			return err
+		}
+	}
+
+	for i, h := range purchaseSupplierSheetHeaders {
+		col, _ := excelize.ColumnNumberToName(i + 1)
+		if err := file.SetCellValue(sheet, col+"1", h); err != nil {
+			return err
+		}
+	}
+
+	for i, row := range item.Rows {
+		rowNum := i + 2
+		rowStr := fmt.Sprintf("%d", rowNum)
+
+		values := purchaseSupplierRowCells(row, i+1)
+		for colIdx, val := range values {
+			col, _ := excelize.ColumnNumberToName(colIdx + 1)
+			if err := file.SetCellValue(sheet, col+rowStr, val); err != nil {
+				return err
+			}
+		}
+	}
+
+	// Summary row
+	totalRowNum := len(item.Rows) + 2
+	totalRowStr := fmt.Sprintf("%d", totalRowNum)
+	totalCells := map[string]interface{}{
+		"A": "Total",
+		"G": item.Summary.TotalQty,
+		"H": item.Summary.TotalUnitPrice,
+		"I": item.Summary.TotalPurchaseValue,
+		"J": item.Summary.TotalTransportUnitPrice,
+		"K": item.Summary.TotalTransportValue,
+		"L": item.Summary.TotalAmount,
+	}
+	for col, val := range totalCells {
+		if err := file.SetCellValue(sheet, col+totalRowStr, val); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func setPurchaseSupplierAllColumns(file *excelize.File, sheet string) error {
+	for col, width := range purchaseSupplierAllSheetColumnWidths {
+		if err := file.SetColWidth(sheet, col, col, width); err != nil {
+			return err
+		}
+	}
+	if err := file.SetRowHeight(sheet, 1, 24); err != nil {
+		return err
+	}
+	return nil
+}
+
+func setPurchaseSupplierAllHeaders(file *excelize.File, sheet string) error {
+	headerStyle, err := file.NewStyle(&excelize.Style{
+		Font: &excelize.Font{Bold: true, Color: "FFFFFF", Family: "Arial", Size: 10},
+		Fill: excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"4472C4"}},
+		Alignment: &excelize.Alignment{
+			Horizontal: "center",
+			Vertical:   "center",
+			WrapText:   true,
+		},
+		Border: []excelize.Border{
+			{Type: "left", Color: "000000", Style: 1},
+			{Type: "top", Color: "000000", Style: 1},
+			{Type: "bottom", Color: "000000", Style: 1},
+			{Type: "right", Color: "000000", Style: 1},
+		},
+	})
+	if err != nil {
+		return err
+	}
+
+	for i, h := range purchaseSupplierAllSheetHeaders {
+		col, _ := excelize.ColumnNumberToName(i + 1)
+		if err := file.SetCellValue(sheet, col+"1", h); err != nil {
+			return err
+		}
+	}
+
+	lastCol, _ := excelize.ColumnNumberToName(len(purchaseSupplierAllSheetHeaders))
+	return file.SetCellStyle(sheet, "A1", lastCol+"1", headerStyle)
+}
+
+func writePurchaseSupplierAllRows(file *excelize.File, sheet string, items []dto.PurchaseSupplierDTO) error {
+	borderStyle := []excelize.Border{
+		{Type: "left", Color: "000000", Style: 1},
+		{Type: "top", Color: "000000", Style: 1},
+		{Type: "bottom", Color: "000000", Style: 1},
+		{Type: "right", Color: "000000", Style: 1},
+	}
+
+	dataStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Color: "000000", Family: "Arial", Size: 10},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	totalStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Bold: true, Color: "000000", Family: "Arial", Size: 10},
+		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"E2EFDA"}},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	lastHeaderCol, _ := excelize.ColumnNumberToName(len(purchaseSupplierAllSheetHeaders))
+
+	currentRow := 2
+	for _, item := range items {
+		supplierName := purchaseSupplierName(item)
+
+		// Data rows
+		for seq, row := range item.Rows {
+			rowStr := fmt.Sprintf("%d", currentRow)
+			if err := file.SetCellValue(sheet, "A"+rowStr, supplierName); err != nil {
+				return err
+			}
+			values := purchaseSupplierRowCells(row, seq+1)
+			for colIdx, val := range values {
+				col, _ := excelize.ColumnNumberToName(colIdx + 2)
+				if err := file.SetCellValue(sheet, col+rowStr, val); err != nil {
+					return err
+				}
+			}
+			if err := file.SetCellStyle(sheet, "A"+rowStr, lastHeaderCol+rowStr, dataStyle); err != nil {
+				return err
+			}
+			currentRow++
+		}
+
+		// Summary row
+		totalRowStr := fmt.Sprintf("%d", currentRow)
+		totalCells := map[string]interface{}{
+			"A": supplierName,
+			"B": "Total",
+			"H": item.Summary.TotalQty,
+			"I": item.Summary.TotalUnitPrice,
+			"J": item.Summary.TotalPurchaseValue,
+			"K": item.Summary.TotalTransportUnitPrice,
+			"L": item.Summary.TotalTransportValue,
+			"M": item.Summary.TotalAmount,
+		}
+		for col, val := range totalCells {
+			if err := file.SetCellValue(sheet, col+totalRowStr, val); err != nil {
+				return err
+			}
+		}
+		if err := file.SetCellStyle(sheet, "A"+totalRowStr, lastHeaderCol+totalRowStr, totalStyle); err != nil {
+			return err
+		}
+		currentRow++
+
+		// Empty separator row
+		currentRow++
+	}
+
+	return nil
+}
+
+// purchaseSupplierRowCells returns cell values for one data row.
+func purchaseSupplierRowCells(row dto.PurchaseSupplierRowDTO, seq int) []interface{} {
+	productName := "-"
+	if row.Product != nil && strings.TrimSpace(row.Product.Name) != "" {
+		productName = row.Product.Name
+	}
+	warehouseName := "-"
+	if row.Warehouse != nil && strings.TrimSpace(row.Warehouse.Name) != "" {
+		warehouseName = row.Warehouse.Name
+	}
+
+	return []interface{}{
+		seq,
+		safePurchaseSupplierText(row.ReceiveDate),
+		safePurchaseSupplierText(row.PoDate),
+		safePurchaseSupplierText(row.PoNumber),
+		productName,
+		warehouseName,
+		row.Qty,
+		row.UnitPrice,
+		row.PurchaseValue,
+		row.TransportUnitPrice,
+		row.TransportValue,
+		row.TotalAmount,
+		safePurchaseSupplierText(row.Expedition),
+		safePurchaseSupplierText(row.DeliveryNumber),
+	}
+}
+
+func purchaseSupplierName(item dto.PurchaseSupplierDTO) string {
+	if item.Supplier != nil && strings.TrimSpace(item.Supplier.Name) != "" {
+		return item.Supplier.Name
+	}
+	return "Supplier"
+}
+
+func sanitizePurchaseSupplierSheetName(name string) string {
+	replacer := strings.NewReplacer(
+		":", " ", "\\", " ", "/", " ",
+		"?", " ", "*", " ", "[", " ", "]", " ",
+	)
+	sanitized := strings.TrimSpace(replacer.Replace(name))
+	if sanitized == "" {
+		return "Sheet"
+	}
+	runes := []rune(sanitized)
+	if len(runes) > 31 {
+		return string(runes[:31])
+	}
+	return sanitized
+}
+
+func safePurchaseSupplierText(s string) string {
+	t := strings.TrimSpace(s)
+	if t == "" {
+		return "-"
+	}
+	return t
+}

internal/modules/repports/repositories/balance_monitoring.repository.go

@@ -240,22 +240,36 @@ func (r *balanceMonitoringRepositoryImpl) GetSalesTotalsBeforeDate(ctx context.C
 		return map[uint]float64{}, nil
 	}
 
-	dateColumn := resolveBalanceMonitoringDateColumn(filters.FilterBy)
-
 	type row struct {
 		CustomerID uint    `gorm:"column:customer_id"`
 		Total      float64 `gorm:"column:total"`
 	}
 	rows := make([]row, 0)
-	db := r.db.WithContext(ctx).
-		Table("marketing_delivery_products mdp").
-		Select("m.customer_id AS customer_id, COALESCE(SUM(mdp.total_price), 0) AS total").
-		Joins("INNER JOIN marketing_products mp ON mp.id = mdp.marketing_product_id").
-		Joins("INNER JOIN marketings m ON m.id = mp.marketing_id").
-		Where("m.customer_id IN ?", customerIDs).
-		Where("m.deleted_at IS NULL").
-		Where("mdp.delivery_date IS NOT NULL").
-		Where(fmt.Sprintf("DATE(%s) < ?", dateColumn), startDate)
+
+	var db *gorm.DB
+	if strings.ToLower(strings.TrimSpace(filters.FilterBy)) == "realized_at" {
+		// realized_at: gunakan data DO (mdp.total_price), filter by delivery_date < startDate
+		db = r.db.WithContext(ctx).
+			Table("marketing_delivery_products mdp").
+			Select("m.customer_id AS customer_id, COALESCE(SUM(mdp.total_price), 0) AS total").
+			Joins("INNER JOIN marketing_products mp ON mp.id = mdp.marketing_product_id").
+			Joins("INNER JOIN marketings m ON m.id = mp.marketing_id").
+			Where("m.customer_id IN ?", customerIDs).
+			Where("m.deleted_at IS NULL").
+			Where("mdp.delivery_date IS NOT NULL").
+			Where("DATE(mdp.delivery_date) < ?", startDate)
+	} else {
+		// sold_at: SO-date sebelum startDate DAN approval terbaru sudah DO — gunakan data DO (mdp.total_price)
+		db = r.db.WithContext(ctx).
+			Table("marketing_products mp").
+			Select("m.customer_id AS customer_id, COALESCE(SUM(mdp.total_price), 0) AS total").
+			Joins("INNER JOIN marketings m ON m.id = mp.marketing_id").
+			Joins("INNER JOIN marketing_delivery_products mdp ON mdp.marketing_product_id = mp.id").
+			Where("m.customer_id IN ?", customerIDs).
+			Where("m.deleted_at IS NULL").
+			Where("DATE(m.so_date) < ?", startDate).
+			Where("(SELECT step_number FROM approvals WHERE approvable_type = 'MARKETINGS' AND approvable_id = mp.marketing_id ORDER BY id DESC LIMIT 1) >= ?", uint16(utils.MarketingDeliveryOrder))
+	}
 
 	if len(filters.SalesIDs) > 0 {
 		db = db.Where("m.sales_person_id IN ?", filters.SalesIDs)
@@ -318,28 +332,46 @@ func (r *balanceMonitoringRepositoryImpl) GetSalesByCategoryInPeriod(ctx context
 		return map[uint]BalanceMonitoringCategoryRow{}, nil
 	}
 
-	dateColumn := resolveBalanceMonitoringDateColumn(filters.FilterBy)
+	// Gunakan data DO (mdp) bukan SO (mp) agar nominal/qty/kg mencerminkan nilai aktual DO
+	const selectCols = `m.customer_id AS customer_id,
+		COALESCE(SUM(CASE WHEN m.marketing_type IN ('AYAM','AYAM_PULLET') THEN (mdp.usage_qty + mdp.pending_qty) ELSE 0 END), 0) AS ayam_qty,
+		COALESCE(SUM(CASE WHEN m.marketing_type IN ('AYAM','AYAM_PULLET') THEN mdp.total_weight  ELSE 0 END), 0) AS ayam_kg,
+		COALESCE(SUM(CASE WHEN m.marketing_type IN ('AYAM','AYAM_PULLET') THEN mdp.total_price   ELSE 0 END), 0) AS ayam_nominal,
+		COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN (mdp.usage_qty + mdp.pending_qty) ELSE 0 END), 0) AS telur_qty,
+		COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN mdp.total_weight  ELSE 0 END), 0) AS telur_kg,
+		COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN mdp.total_price   ELSE 0 END), 0) AS telur_nominal,
+		COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN (mdp.usage_qty + mdp.pending_qty) ELSE 0 END), 0) AS trading_qty,
+		COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN mdp.total_weight  ELSE 0 END), 0) AS trading_kg,
+		COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN mdp.total_price   ELSE 0 END), 0) AS trading_nominal`
 
 	rows := make([]BalanceMonitoringCategoryRow, 0)
-	db := r.db.WithContext(ctx).
-		Table("marketing_delivery_products mdp").
-		Select(`m.customer_id AS customer_id,
-			COALESCE(SUM(CASE WHEN m.marketing_type IN ('AYAM','AYAM_PULLET') THEN mdp.usage_qty    ELSE 0 END), 0) AS ayam_qty,
-			COALESCE(SUM(CASE WHEN m.marketing_type IN ('AYAM','AYAM_PULLET') THEN mdp.total_weight ELSE 0 END), 0) AS ayam_kg,
-			COALESCE(SUM(CASE WHEN m.marketing_type IN ('AYAM','AYAM_PULLET') THEN mdp.total_price  ELSE 0 END), 0) AS ayam_nominal,
-			COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN mdp.usage_qty    ELSE 0 END), 0) AS telur_qty,
-			COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN mdp.total_weight ELSE 0 END), 0) AS telur_kg,
-			COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN mdp.total_price  ELSE 0 END), 0) AS telur_nominal,
-			COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN mdp.usage_qty    ELSE 0 END), 0) AS trading_qty,
-			COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN mdp.total_weight ELSE 0 END), 0) AS trading_kg,
-			COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN mdp.total_price  ELSE 0 END), 0) AS trading_nominal`).
-		Joins("INNER JOIN marketing_products mp ON mp.id = mdp.marketing_product_id").
-		Joins("INNER JOIN marketings m ON m.id = mp.marketing_id").
-		Where("m.customer_id IN ?", customerIDs).
-		Where("m.deleted_at IS NULL").
-		Where("mdp.delivery_date IS NOT NULL").
-		Where(fmt.Sprintf("DATE(%s) >= ?", dateColumn), startDate).
-		Where(fmt.Sprintf("DATE(%s) <= ?", dateColumn), endDate)
+
+	var db *gorm.DB
+	if strings.ToLower(strings.TrimSpace(filters.FilterBy)) == "realized_at" {
+		// realized_at: FROM mdp langsung, filter by delivery_date in period — data DO
+		db = r.db.WithContext(ctx).
+			Table("marketing_delivery_products mdp").
+			Select(selectCols).
+			Joins("INNER JOIN marketing_products mp ON mp.id = mdp.marketing_product_id").
+			Joins("INNER JOIN marketings m ON m.id = mp.marketing_id").
+			Where("m.customer_id IN ?", customerIDs).
+			Where("m.deleted_at IS NULL").
+			Where("mdp.delivery_date IS NOT NULL").
+			Where("DATE(mdp.delivery_date) >= ?", startDate).
+			Where("DATE(mdp.delivery_date) <= ?", endDate)
+	} else {
+		// sold_at: SO-date dalam period DAN approval terbaru DO — JOIN mdp untuk data DO
+		db = r.db.WithContext(ctx).
+			Table("marketing_products mp").
+			Select(selectCols).
+			Joins("INNER JOIN marketings m ON m.id = mp.marketing_id").
+			Joins("INNER JOIN marketing_delivery_products mdp ON mdp.marketing_product_id = mp.id").
+			Where("m.customer_id IN ?", customerIDs).
+			Where("m.deleted_at IS NULL").
+			Where("DATE(m.so_date) >= ?", startDate).
+			Where("DATE(m.so_date) <= ?", endDate).
+			Where("(SELECT step_number FROM approvals WHERE approvable_type = 'MARKETINGS' AND approvable_id = mp.marketing_id ORDER BY id DESC LIMIT 1) >= ?", uint16(utils.MarketingDeliveryOrder))
+	}
 
 	if len(filters.SalesIDs) > 0 {
 		db = db.Where("m.sales_person_id IN ?", filters.SalesIDs)

internal/modules/repports/repositories/debt_supplier.repository.go

@@ -514,7 +514,7 @@ func (r *debtSupplierRepositoryImpl) baseExpenseSupplierIDs(ctx context.Context,
 		Table("expenses").
 		Select("DISTINCT expenses.supplier_id").
 		Joins("JOIN (?) AS la ON la.approvable_id = expenses.id", r.latestExpenseApproval(ctx)).
-		Where("la.step_number >= ?", uint16(utils.ExpenseStepFinance)).
+		Where("la.step_number >= ?", uint16(utils.ExpenseStepRealisasi)).
 		Where("(la.action IS NULL OR la.action != ?)", string(entity.ApprovalActionRejected)).
 		Where("expenses.deleted_at IS NULL")
 
@@ -623,7 +623,7 @@ func (r *debtSupplierRepositoryImpl) GetExpensesBySuppliers(ctx context.Context,
 		Model(&entity.Expense{}).
 		Joins("JOIN (?) AS la ON la.approvable_id = expenses.id", r.latestExpenseApproval(ctx)).
 		Where("expenses.supplier_id IN ?", supplierIDs).
-		Where("la.step_number >= ?", uint16(utils.ExpenseStepFinance)).
+		Where("la.step_number >= ?", uint16(utils.ExpenseStepRealisasi)).
 		Where("(la.action IS NULL OR la.action != ?)", string(entity.ApprovalActionRejected)).
 		Where("expenses.deleted_at IS NULL")
 
@@ -692,7 +692,7 @@ func (r *debtSupplierRepositoryImpl) GetExpenseTotalsBeforeDate(ctx context.Cont
 		Joins("JOIN expense_nonstocks en ON en.expense_id = expenses.id").
 		Joins("JOIN (?) AS la ON la.approvable_id = expenses.id", r.latestExpenseApproval(ctx)).
 		Where("expenses.supplier_id IN ?", supplierIDs).
-		Where("la.step_number >= ?", uint16(utils.ExpenseStepFinance)).
+		Where("la.step_number >= ?", uint16(utils.ExpenseStepRealisasi)).
 		Where("(la.action IS NULL OR la.action != ?)", string(entity.ApprovalActionRejected)).
 		Where("expenses.deleted_at IS NULL").
 		Where("DATE(expenses.transaction_date) < ?", dateFrom).