fix

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

@@ -240,32 +240,22 @@ 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)
-
-	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)
-		db = r.db.WithContext(ctx).
-			Table("marketing_products mp").
-			Select("m.customer_id AS customer_id, COALESCE(SUM(mp.total_price), 0) AS total").
-			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)
-	} else {
-		// sold_at: count all SO products ordered before 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").
-			Joins("INNER JOIN marketings m ON m.id = mp.marketing_id").
-			Where("m.customer_id IN ?", customerIDs).
-			Where("m.deleted_at IS NULL").
-			Where("DATE(m.so_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(fmt.Sprintf("DATE(%s) < ?", dateColumn), startDate)
 
 	if len(filters.SalesIDs) > 0 {
 		db = db.Where("m.sales_person_id IN ?", filters.SalesIDs)
@@ -328,40 +318,28 @@ func (r *balanceMonitoringRepositoryImpl) GetSalesByCategoryInPeriod(ctx context
 		return map[uint]BalanceMonitoringCategoryRow{}, nil
 	}
 
-	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`
+	dateColumn := resolveBalanceMonitoringDateColumn(filters.FilterBy)
 
 	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)
-		db = r.db.WithContext(ctx).
-			Table("marketing_products mp").
-			Select(selectCols).
-			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)
-	} else {
-		// sold_at: count all SO products placed in the period regardless of delivery status
-		db = r.db.WithContext(ctx).
-			Table("marketing_products mp").
-			Select(selectCols).
-			Joins("INNER JOIN marketings m ON m.id = mp.marketing_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)
-	}
+	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)
 
 	if len(filters.SalesIDs) > 0 {
 		db = db.Where("m.sales_person_id IN ?", filters.SalesIDs)