Merge pull request 'feature/exclusive-summary' (#19) from feature/exclusive-summary into main

Reviewed-on: #19

go.sum

@@ -351,8 +351,6 @@ github.com/yuin/goldmark v1.3.5/go.mod h1:mwnBkeHKe2W/ZEtQ+71ViKU8L12m81fl3OWwC1
 github.com/yuin/goldmark v1.4.13/go.mod h1:6yULJ656Px+3vBD8DxQVa3kxgyrAnzto9xy5taEt/CY=
 github.com/zeebo/errs v1.4.0 h1:XNdoD/RRMKP7HD0UhJnIzUy74ISdGGxURlYG8HSWSfM=
 github.com/zeebo/errs v1.4.0/go.mod h1:sgbWHsvVuTPHcqJJGQ1WhI5KbWlHYz+2+2C/LSEtCw4=
-github.com/zeebo/xxh3 v1.1.0 h1:s7DLGDK45Dyfg7++yxI0khrfwq9661w9EN78eP/UZVs=
-github.com/zeebo/xxh3 v1.1.0/go.mod h1:IisAie1LELR4xhVinxWS5+zf1lA4p0MW4T+w+W07F5s=
 go.opencensus.io v0.21.0/go.mod h1:mSImk1erAIZhrmZN+AvHh14ztQfjbGwt4TtuofqLduU=
 go.opencensus.io v0.22.0/go.mod h1:+kGneAE2xo2IficOXnaByMWTGM9T73dGwxeWcUqIpI8=
 go.opencensus.io v0.22.2/go.mod h1:yxeiOL68Rb0Xd1ddK5vPZ/oVn4vY4Ynel7k9FzqtOIw=
@@ -382,6 +380,7 @@ go.opentelemetry.io/otel/trace v1.35.0/go.mod h1:WUk7DtFp1Aw2MkvqGdwiXYDZZNvA/1J
 go.uber.org/atomic v1.7.0/go.mod h1:fEN4uk6kAWBTFdckzkM89CLk9XfWZrxpCo0nPH17wJc=
 go.uber.org/atomic v1.11.0 h1:ZvwS0R+56ePWxUNi+Atn9dWONBPp/AUETXlHW0DxSjE=
 go.uber.org/atomic v1.11.0/go.mod h1:LUxbIzbOniOlMKjJjyPfpl4v+PKK2cNJn91OQbhoJI0=
+go.uber.org/goleak v1.1.11 h1:wy28qYRKZgnJTxGxvye5/wgWr1EKjmUDGYox5mGlRlI=
 go.uber.org/goleak v1.1.11/go.mod h1:cwTWslyiVhfpKIDGSZEM2HlOvcqm+tG4zioyIeLoqMQ=
 go.uber.org/goleak v1.3.0 h1:2K3zAYmnTNqV73imy9J1T3WC+gmCePx2hEGkimedGto=
 go.uber.org/goleak v1.3.0/go.mod h1:CoHD4mav9JJNrW/WLlf7HGZPjdw8EucARQHekz1X6bE=

internal/repository/analytics_repository.go

@@ -2,7 +2,6 @@ package repository
 
 import (
 	"context"
-	"sort"
 	"time"
 
 	"apskel-pos-be/internal/entities"
@@ -44,14 +43,6 @@ func purchaseOrderItemTotalAmountSQL() string {
 	return "CASE WHEN pc.type = '" + string(entities.PurchaseCategoryTypeRawMaterial) + "' THEN COALESCE(poi.quantity, 0) * poi.amount ELSE poi.amount END"
 }
 
-func purchaseOrderRawMaterialAmountSQL() string {
-	return "CASE WHEN pc.type = '" + string(entities.PurchaseCategoryTypeRawMaterial) + "' THEN " + purchaseOrderItemTotalAmountSQL() + " ELSE 0 END"
-}
-
-func purchaseOrderExpenseAmountSQL() string {
-	return "CASE WHEN pc.type = '" + string(entities.PurchaseCategoryTypeExpense) + "' THEN " + purchaseOrderItemTotalAmountSQL() + " ELSE 0 END"
-}
-
 func (r *AnalyticsRepositoryImpl) GetPaymentMethodAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]*entities.PaymentMethodAnalytics, error) {
 	var results []*entities.PaymentMethodAnalytics
 
@@ -167,11 +158,7 @@ func (r *AnalyticsRepositoryImpl) getPurchaseOrderPurchasingAnalytics(ctx contex
 		Table("purchase_orders po").
 		Select(`
 			COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) as total_purchases,
-			COALESCE(SUM(`+purchaseOrderRawMaterialAmountSQL()+`), 0) as raw_material_purchases,
-			COALESCE(SUM(`+purchaseOrderExpenseAmountSQL()+`), 0) as expense_purchases,
 			COUNT(DISTINCT po.id) as total_purchase_orders,
-			COUNT(DISTINCT CASE WHEN pc.type = '`+string(entities.PurchaseCategoryTypeRawMaterial)+`' THEN po.id END) as raw_material_purchase_orders,
-			COUNT(CASE WHEN pc.type = '`+string(entities.PurchaseCategoryTypeExpense)+`' THEN poi.id END) as expense_count,
 			COALESCE(SUM(poi.quantity), 0) as total_quantity,
 			CASE
 				WHEN COUNT(DISTINCT po.id) > 0
@@ -212,11 +199,7 @@ func (r *AnalyticsRepositoryImpl) getPurchaseOrderPurchasingAnalytics(ctx contex
 		Select(`
 			`+dateFormat+` as date,
 			COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) as purchases,
-			COALESCE(SUM(`+purchaseOrderRawMaterialAmountSQL()+`), 0) as raw_material_purchases,
-			COALESCE(SUM(`+purchaseOrderExpenseAmountSQL()+`), 0) as expense_purchases,
 			COUNT(DISTINCT po.id) as purchase_orders,
-			COUNT(DISTINCT CASE WHEN pc.type = '`+string(entities.PurchaseCategoryTypeRawMaterial)+`' THEN po.id END) as raw_material_purchase_orders,
-			COUNT(CASE WHEN pc.type = '`+string(entities.PurchaseCategoryTypeExpense)+`' THEN poi.id END) as expense_count,
 			COALESCE(SUM(poi.quantity), 0) as quantity,
 			COUNT(DISTINCT i.id) as ingredients,
 			COUNT(DISTINCT COALESCE(po.vendor_id::text, 'no-vendor')) as vendors
@@ -227,7 +210,6 @@ func (r *AnalyticsRepositoryImpl) getPurchaseOrderPurchasingAnalytics(ctx contex
 		Joins("LEFT JOIN units u ON poi.unit_id = u.id").
 		Where("po.organization_id = ?", organizationID).
 		Where("po.status != ?", "cancelled").
-		Where("pc.type = ?", entities.PurchaseCategoryTypeRawMaterial).
 		Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo).
 		Group(dateFormat).
 		Order(dateFormat)
@@ -318,13 +300,6 @@ func (r *AnalyticsRepositoryImpl) GetProductAnalytics(ctx context.Context, organ
 		Select(`
 			p.id as product_id,
 			p.name as product_name,
-			p.sku as product_sku,
-			COALESCE(
-				NULLIF(pop.price, 0),
-				(SELECT price FROM product_outlet_prices WHERE product_id = p.id ORDER BY updated_at DESC LIMIT 1),
-				NULLIF(p.price, 0),
-				0
-			) as product_price,
 			c.id as category_id,
 			c.name as category_name,
 			c.order as category_order,
@@ -358,7 +333,6 @@ func (r *AnalyticsRepositoryImpl) GetProductAnalytics(ctx context.Context, organ
 		Joins("JOIN products p ON oi.product_id = p.id").
 		Joins("JOIN categories c ON p.category_id = c.id").
 		Joins("JOIN orders o ON oi.order_id = o.id").
-		Joins("LEFT JOIN product_outlet_prices pop ON pop.product_id = p.id AND pop.outlet_id = o.outlet_id").
 		Joins("LEFT JOIN (?) mahpp ON mahpp.product_id = p.id",
 			r.db.Table("product_recipes pr2").
 				Select("pr2.product_id, SUM(pr2.quantity * (1 + COALESCE(pr2.waste_percentage, 0)/100.0) * COALESCE(ma.moving_avg_cost, ing.cost)) as hpp_per_unit").
@@ -384,7 +358,7 @@ func (r *AnalyticsRepositoryImpl) GetProductAnalytics(ctx context.Context, organ
 	query = r.resolveOutletID(query, outletID, "o.outlet_id")
 
 	err := query.
-		Group("p.id, p.name, p.sku, p.price, p.cost, pop.price, c.id, c.name, c.order, mahpp.hpp_per_unit").
+		Group("p.id, p.name, p.cost, c.id, c.name, c.order, mahpp.hpp_per_unit").
 		Order("revenue DESC").
 		Limit(limit).
 		Scan(&results).Error
@@ -520,11 +494,6 @@ func (r *AnalyticsRepositoryImpl) GetProfitLossAnalytics(ctx context.Context, or
 	if err := summaryQuery.Scan(&summary).Error; err != nil {
 		return nil, err
 	}
-	periodHPP, err := r.getPurchaseOrderRawMaterialTotal(ctx, organizationID, outletID, dateFrom, dateTo)
-	if err != nil {
-		return nil, err
-	}
-	applyProfitLossSummaryCost(&summary, periodHPP)
 
 	var timeFormat string
 	switch groupBy {
@@ -572,11 +541,6 @@ func (r *AnalyticsRepositoryImpl) GetProfitLossAnalytics(ctx context.Context, or
 	if err := dataQuery.Scan(&data).Error; err != nil {
 		return nil, err
 	}
-	poCostData, err := r.getPurchaseOrderRawMaterialCostByPeriod(ctx, organizationID, outletID, dateFrom, dateTo, groupBy)
-	if err != nil {
-		return nil, err
-	}
-	data = mergeProfitLossDataWithPurchaseOrderCost(data, poCostData)
 
 	var productData []entities.ProductProfitData
 	productQuery := r.db.WithContext(ctx).
@@ -637,11 +601,6 @@ func (r *AnalyticsRepositoryImpl) GetProfitLossAnalytics(ctx context.Context, or
 	if err := todayQuery.Scan(&todayRC).Error; err != nil {
 		return nil, err
 	}
-	todayHPP, err := r.getPurchaseOrderRawMaterialTotal(ctx, organizationID, outletID, todayStart, todayEnd)
-	if err != nil {
-		return nil, err
-	}
-	todayRC.Cost = todayHPP
 
 	var mtdRC revenueCostResult
 	mtdQuery := r.db.WithContext(ctx).
@@ -659,41 +618,21 @@ func (r *AnalyticsRepositoryImpl) GetProfitLossAnalytics(ctx context.Context, or
 	if err := mtdQuery.Scan(&mtdRC).Error; err != nil {
 		return nil, err
 	}
-	mtdHPP, err := r.getPurchaseOrderRawMaterialTotal(ctx, organizationID, outletID, mtdStart, todayEnd)
-	if err != nil {
-		return nil, err
-	}
-	mtdRC.Cost = mtdHPP
 
 	todayExpenseByCategory, err := r.getExpenseByCategory(ctx, organizationID, outletID, todayStart, todayEnd)
 	if err != nil {
 		return nil, err
 	}
-	todayPOExpenseByCategory, err := r.getPurchaseOrderExpenseByCategory(ctx, organizationID, outletID, todayStart, todayEnd)
-	if err != nil {
-		return nil, err
-	}
-	todayExpenseByCategory = mergeExpenseCategoryTotals(todayExpenseByCategory, todayPOExpenseByCategory)
 
 	mtdExpenseByCategory, err := r.getExpenseByCategory(ctx, organizationID, outletID, mtdStart, todayEnd)
 	if err != nil {
 		return nil, err
 	}
-	mtdPOExpenseByCategory, err := r.getPurchaseOrderExpenseByCategory(ctx, organizationID, outletID, mtdStart, todayEnd)
-	if err != nil {
-		return nil, err
-	}
-	mtdExpenseByCategory = mergeExpenseCategoryTotals(mtdExpenseByCategory, mtdPOExpenseByCategory)
 
 	opsItems, err := r.getOperationalExpenseItems(ctx, organizationID, outletID, mtdStart, todayEnd)
 	if err != nil {
 		return nil, err
 	}
-	poOpsItems, err := r.getPurchaseOrderExpenseItems(ctx, organizationID, outletID, mtdStart, todayEnd)
-	if err != nil {
-		return nil, err
-	}
-	opsItems = mergeOperationalExpenseItems(opsItems, poOpsItems)
 
 	return &entities.ProfitLossAnalytics{
 		Summary:                 summary,
@@ -709,200 +648,6 @@ func (r *AnalyticsRepositoryImpl) GetProfitLossAnalytics(ctx context.Context, or
 	}, nil
 }
 
-func (r *AnalyticsRepositoryImpl) getPurchaseOrderRawMaterialTotal(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) (float64, error) {
-	type totalResult struct {
-		Total float64
-	}
-	var result totalResult
-
-	query := r.db.WithContext(ctx).
-		Table("purchase_order_items poi").
-		Select(`COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) as total`).
-		Joins("JOIN purchase_orders po ON poi.purchase_order_id = po.id").
-		Joins("JOIN purchase_categories pc ON poi.purchase_category_id = pc.id").
-		Where("po.organization_id = ?", organizationID).
-		Where("po.status = ?", "received").
-		Where("pc.type = ?", entities.PurchaseCategoryTypeRawMaterial).
-		Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo)
-	query = r.applyPurchaseOrderItemOutletFilter(query, outletID)
-
-	if err := query.Scan(&result).Error; err != nil {
-		return 0, err
-	}
-	return result.Total, nil
-}
-
-func (r *AnalyticsRepositoryImpl) getPurchaseOrderRawMaterialCostByPeriod(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, groupBy string) ([]entities.ProfitLossData, error) {
-	var dateFormat string
-	switch groupBy {
-	case "hour":
-		dateFormat = "DATE_TRUNC('hour', po.transaction_date::timestamp)"
-	case "week":
-		dateFormat = "DATE_TRUNC('week', po.transaction_date::timestamp)"
-	case "month":
-		dateFormat = "DATE_TRUNC('month', po.transaction_date::timestamp)"
-	default:
-		dateFormat = "DATE_TRUNC('day', po.transaction_date::timestamp)"
-	}
-
-	var results []entities.ProfitLossData
-	query := r.db.WithContext(ctx).
-		Table("purchase_order_items poi").
-		Select(`
-			`+dateFormat+` as date,
-			COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) as cost
-		`).
-		Joins("JOIN purchase_orders po ON poi.purchase_order_id = po.id").
-		Joins("JOIN purchase_categories pc ON poi.purchase_category_id = pc.id").
-		Where("po.organization_id = ?", organizationID).
-		Where("po.status = ?", "received").
-		Where("pc.type = ?", entities.PurchaseCategoryTypeRawMaterial).
-		Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo).
-		Group(dateFormat).
-		Order(dateFormat)
-	query = r.applyPurchaseOrderItemOutletFilter(query, outletID)
-
-	if err := query.Scan(&results).Error; err != nil {
-		return nil, err
-	}
-	return results, nil
-}
-
-func applyProfitLossSummaryCost(summary *entities.ProfitLossSummary, cost float64) {
-	summary.TotalCost = cost
-	summary.GrossProfit = summary.TotalRevenue - cost
-	summary.GrossProfitMargin = ratio(summary.GrossProfit, summary.TotalRevenue)
-	summary.NetProfit = summary.TotalRevenue - cost - summary.TotalDiscount
-	summary.NetProfitMargin = ratio(summary.NetProfit, summary.TotalRevenue)
-	if summary.TotalOrders > 0 {
-		summary.AverageProfit = summary.NetProfit / float64(summary.TotalOrders)
-	} else {
-		summary.AverageProfit = 0
-	}
-	summary.ProfitabilityRatio = ratio(summary.GrossProfit, cost)
-}
-
-func mergeProfitLossDataWithPurchaseOrderCost(data, costs []entities.ProfitLossData) []entities.ProfitLossData {
-	indexByDate := make(map[time.Time]int, len(data))
-	for i, item := range data {
-		data[i].Cost = 0
-		indexByDate[item.Date] = i
-	}
-
-	for _, cost := range costs {
-		if i, ok := indexByDate[cost.Date]; ok {
-			data[i].Cost = cost.Cost
-			continue
-		}
-
-		indexByDate[cost.Date] = len(data)
-		data = append(data, entities.ProfitLossData{Date: cost.Date, Cost: cost.Cost})
-	}
-
-	for i := range data {
-		data[i].GrossProfit = data[i].Revenue - data[i].Cost
-		data[i].GrossProfitMargin = ratio(data[i].GrossProfit, data[i].Revenue)
-		data[i].NetProfit = data[i].Revenue - data[i].Cost - data[i].Discount
-		data[i].NetProfitMargin = ratio(data[i].NetProfit, data[i].Revenue)
-	}
-
-	sort.Slice(data, func(i, j int) bool {
-		return data[i].Date.Before(data[j].Date)
-	})
-	return data
-}
-
-func ratio(numerator, denominator float64) float64 {
-	if denominator == 0 {
-		return 0
-	}
-	return (numerator / denominator) * 100
-}
-
-func (r *AnalyticsRepositoryImpl) getPurchaseOrderExpenseByCategory(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]entities.ExpenseCategoryTotal, error) {
-	var results []entities.ExpenseCategoryTotal
-
-	query := r.db.WithContext(ctx).
-		Table("purchase_order_items poi").
-		Select(`
-			pc.name as category_name,
-			COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) as amount
-		`).
-		Joins("JOIN purchase_orders po ON poi.purchase_order_id = po.id").
-		Joins("JOIN purchase_categories pc ON poi.purchase_category_id = pc.id").
-		Where("po.organization_id = ?", organizationID).
-		Where("po.status = ?", "received").
-		Where("pc.type = ?", entities.PurchaseCategoryTypeExpense).
-		Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo).
-		Group("pc.id, pc.name, pc.sort_order").
-		Order("pc.sort_order ASC, pc.name ASC")
-	query = r.applyPurchaseOrderItemOutletFilter(query, outletID)
-
-	if err := query.Scan(&results).Error; err != nil {
-		return nil, err
-	}
-	return results, nil
-}
-
-func (r *AnalyticsRepositoryImpl) getPurchaseOrderExpenseItems(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]entities.OperationalExpenseItem, error) {
-	var results []entities.OperationalExpenseItem
-
-	query := r.db.WithContext(ctx).
-		Table("purchase_order_items poi").
-		Select(`
-			COALESCE(NULLIF(poi.description, ''), pc.name) as item,
-			COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) as amount
-		`).
-		Joins("JOIN purchase_orders po ON poi.purchase_order_id = po.id").
-		Joins("JOIN purchase_categories pc ON poi.purchase_category_id = pc.id").
-		Where("po.organization_id = ?", organizationID).
-		Where("po.status = ?", "received").
-		Where("pc.type = ?", entities.PurchaseCategoryTypeExpense).
-		Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo).
-		Group("COALESCE(NULLIF(poi.description, ''), pc.name)").
-		Order("amount DESC")
-	query = r.applyPurchaseOrderItemOutletFilter(query, outletID)
-
-	if err := query.Scan(&results).Error; err != nil {
-		return nil, err
-	}
-	return results, nil
-}
-
-func mergeExpenseCategoryTotals(base, extra []entities.ExpenseCategoryTotal) []entities.ExpenseCategoryTotal {
-	indexByName := make(map[string]int, len(base))
-	for i, item := range base {
-		indexByName[item.CategoryName] = i
-	}
-
-	for _, item := range extra {
-		if i, ok := indexByName[item.CategoryName]; ok {
-			base[i].Amount += item.Amount
-			continue
-		}
-		indexByName[item.CategoryName] = len(base)
-		base = append(base, item)
-	}
-	return base
-}
-
-func mergeOperationalExpenseItems(base, extra []entities.OperationalExpenseItem) []entities.OperationalExpenseItem {
-	indexByName := make(map[string]int, len(base))
-	for i, item := range base {
-		indexByName[item.Item] = i
-	}
-
-	for _, item := range extra {
-		if i, ok := indexByName[item.Item]; ok {
-			base[i].Amount += item.Amount
-			continue
-		}
-		indexByName[item.Item] = len(base)
-		base = append(base, item)
-	}
-	return base
-}
-
 func (r *AnalyticsRepositoryImpl) getExpenseByCategory(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]entities.ExpenseCategoryTotal, error) {
 	var results []entities.ExpenseCategoryTotal