add migration for normalize wrong location pullet cikaum

fix monitoring saldo

See merge request mbugroup/lti-api!556

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/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/repositories/balance_monitoring.repository.go

@@ -248,23 +248,27 @@ func (r *balanceMonitoringRepositoryImpl) GetSalesTotalsBeforeDate(ctx context.C
 
 	var db *gorm.DB
 	if strings.ToLower(strings.TrimSpace(filters.FilterBy)) == "realized_at" {
-		// Count products that have at least one delivery before startDate (no double-counting)
+		// realized_at: gunakan data DO (mdp.total_price), filter by delivery_date < startDate
 		db = r.db.WithContext(ctx).
-			Table("marketing_products mp").
-			Select("m.customer_id AS customer_id, COALESCE(SUM(mp.total_price), 0) AS total").
+			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("EXISTS (SELECT 1 FROM marketing_delivery_products mdp WHERE mdp.marketing_product_id = mp.id AND mdp.deleted_at IS NULL AND mdp.delivery_date IS NOT NULL AND DATE(mdp.delivery_date) < ?)", startDate)
+			Where("mdp.delivery_date IS NOT NULL").
+			Where("DATE(mdp.delivery_date) < ?", startDate)
 	} else {
-		// sold_at: count all SO products ordered before startDate
+		// 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(mp.total_price), 0) AS total").
+			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("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 {
@@ -328,39 +332,45 @@ func (r *balanceMonitoringRepositoryImpl) GetSalesByCategoryInPeriod(ctx context
 		return map[uint]BalanceMonitoringCategoryRow{}, nil
 	}
 
+	// 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 mp.qty          ELSE 0 END), 0) AS ayam_qty,
-		COALESCE(SUM(CASE WHEN m.marketing_type IN ('AYAM','AYAM_PULLET') THEN mp.total_weight  ELSE 0 END), 0) AS ayam_kg,
-		COALESCE(SUM(CASE WHEN m.marketing_type IN ('AYAM','AYAM_PULLET') THEN mp.total_price   ELSE 0 END), 0) AS ayam_nominal,
-		COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN mp.qty          ELSE 0 END), 0) AS telur_qty,
-		COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN mp.total_weight  ELSE 0 END), 0) AS telur_kg,
-		COALESCE(SUM(CASE WHEN m.marketing_type = 'TELUR' THEN mp.total_price   ELSE 0 END), 0) AS telur_nominal,
-		COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN mp.qty          ELSE 0 END), 0) AS trading_qty,
-		COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN mp.total_weight  ELSE 0 END), 0) AS trading_kg,
-		COALESCE(SUM(CASE WHEN m.marketing_type = 'TRADING' THEN mp.total_price   ELSE 0 END), 0) AS trading_nominal`
+		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)
 
 	var db *gorm.DB
 	if strings.ToLower(strings.TrimSpace(filters.FilterBy)) == "realized_at" {
-		// Count products that have at least one delivery in the period (no double-counting)
+		// realized_at: FROM mdp langsung, filter by delivery_date in period — data DO
 		db = r.db.WithContext(ctx).
-			Table("marketing_products mp").
+			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("EXISTS (SELECT 1 FROM marketing_delivery_products mdp WHERE mdp.marketing_product_id = mp.id AND mdp.deleted_at IS NULL AND mdp.delivery_date IS NOT NULL AND DATE(mdp.delivery_date) >= ? AND DATE(mdp.delivery_date) <= ?)", startDate, endDate)
+			Where("mdp.delivery_date IS NOT NULL").
+			Where("DATE(mdp.delivery_date) >= ?", startDate).
+			Where("DATE(mdp.delivery_date) <= ?", endDate)
 	} else {
-		// sold_at: count all SO products placed in the period regardless of delivery status
+		// 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("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 {

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).