1168 lines
46 KiB
Go
1168 lines
46 KiB
Go
package repository
|
|
|
|
import (
|
|
"context"
|
|
"sort"
|
|
"time"
|
|
|
|
"apskel-pos-be/internal/entities"
|
|
|
|
"github.com/google/uuid"
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
type AnalyticsRepository interface {
|
|
GetPaymentMethodAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]*entities.PaymentMethodAnalytics, error)
|
|
GetSalesAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, groupBy string) ([]*entities.SalesAnalytics, error)
|
|
GetPurchasingAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, groupBy string) (*entities.PurchasingAnalytics, error)
|
|
GetProductAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, limit int) ([]*entities.ProductAnalytics, error)
|
|
GetProductAnalyticsPerCategory(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]*entities.ProductAnalyticsPerCategory, error)
|
|
GetDashboardOverview(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) (*entities.DashboardOverview, error)
|
|
GetProfitLossAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, groupBy string) (*entities.ProfitLossAnalytics, error)
|
|
GetExclusiveSummaryAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) (*entities.ExclusiveSummaryAnalytics, error)
|
|
GetExclusiveSummaryBankBalances(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID) ([]entities.ExclusiveSummaryBankBalance, error)
|
|
}
|
|
|
|
type AnalyticsRepositoryImpl struct {
|
|
db *gorm.DB
|
|
}
|
|
|
|
func NewAnalyticsRepositoryImpl(db *gorm.DB) *AnalyticsRepositoryImpl {
|
|
return &AnalyticsRepositoryImpl{
|
|
db: db,
|
|
}
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) resolveOutletID(query *gorm.DB, outletID *uuid.UUID, column string) *gorm.DB {
|
|
if outletID != nil {
|
|
return query.Where(column+" = ?", *outletID)
|
|
}
|
|
return query
|
|
}
|
|
|
|
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
|
|
|
|
query := r.db.WithContext(ctx).
|
|
Table("payments p").
|
|
Select(`
|
|
pm.id as payment_method_id,
|
|
pm.name as payment_method_name,
|
|
pm.type as payment_method_type,
|
|
COALESCE(SUM(p.amount), 0) as total_amount,
|
|
COUNT(DISTINCT p.order_id) as order_count,
|
|
COUNT(p.id) as payment_count
|
|
`).
|
|
Joins("JOIN payment_methods pm ON p.payment_method_id = pm.id").
|
|
Joins("JOIN orders o ON p.order_id = o.id").
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.is_void = ?", false).
|
|
Where("o.is_refund = ?", false).
|
|
Where("p.status = ?", entities.PaymentTransactionStatusCompleted).
|
|
Where("o.created_at >= ? AND o.created_at <= ?", dateFrom, dateTo)
|
|
|
|
query = r.resolveOutletID(query, outletID, "o.outlet_id")
|
|
|
|
err := query.
|
|
Group("pm.id, pm.name, pm.type").
|
|
Order("total_amount DESC").
|
|
Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) GetSalesAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, groupBy string) ([]*entities.SalesAnalytics, error) {
|
|
var results []*entities.SalesAnalytics
|
|
|
|
var dateFormat string
|
|
switch groupBy {
|
|
case "hour":
|
|
dateFormat = "DATE_TRUNC('hour', o.created_at)"
|
|
case "week":
|
|
dateFormat = "DATE_TRUNC('week', o.created_at)"
|
|
case "month":
|
|
dateFormat = "DATE_TRUNC('month', o.created_at)"
|
|
default:
|
|
dateFormat = "DATE(o.created_at)"
|
|
}
|
|
|
|
outletFilter := ""
|
|
args := []interface{}{organizationID, false, false, string(entities.PaymentStatusCompleted), dateFrom, dateTo}
|
|
if outletID != nil {
|
|
outletFilter = "AND o.outlet_id = ?"
|
|
args = append(args, *outletID)
|
|
}
|
|
|
|
rawQuery := `
|
|
SELECT
|
|
` + dateFormat + ` as date,
|
|
COALESCE(SUM(o.total_amount), 0) as sales,
|
|
COUNT(o.id) as orders,
|
|
COALESCE(SUM(oi_agg.total_items), 0) as items,
|
|
COALESCE(SUM(o.tax_amount), 0) as tax,
|
|
COALESCE(SUM(o.discount_amount), 0) as discount,
|
|
COALESCE(SUM(o.total_amount - o.tax_amount - o.discount_amount), 0) as net_sales
|
|
FROM orders o
|
|
LEFT JOIN (
|
|
SELECT
|
|
oi.order_id,
|
|
SUM(oi.quantity - COALESCE(oi.refund_quantity, 0)) as total_items
|
|
FROM order_items oi
|
|
WHERE oi.status != 'cancelled'
|
|
AND oi.is_fully_refunded = false
|
|
GROUP BY oi.order_id
|
|
) oi_agg ON oi_agg.order_id = o.id
|
|
WHERE o.organization_id = ?
|
|
AND o.is_void = ?
|
|
AND o.is_refund = ?
|
|
AND o.payment_status = ?
|
|
AND o.created_at >= ? AND o.created_at <= ?
|
|
` + outletFilter + `
|
|
GROUP BY date
|
|
ORDER BY date ASC
|
|
`
|
|
|
|
err := r.db.WithContext(ctx).Raw(rawQuery, args...).Scan(&results).Error
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) GetPurchasingAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, groupBy string) (*entities.PurchasingAnalytics, error) {
|
|
var outletName *string
|
|
|
|
if outletID != nil {
|
|
var outlet struct {
|
|
Name string
|
|
}
|
|
result := r.db.WithContext(ctx).
|
|
Table("outlets").
|
|
Select("name").
|
|
Where("id = ? AND organization_id = ?", *outletID, organizationID).
|
|
Limit(1).
|
|
Scan(&outlet)
|
|
if result.Error != nil {
|
|
return nil, result.Error
|
|
}
|
|
if result.RowsAffected > 0 {
|
|
outletName = &outlet.Name
|
|
}
|
|
}
|
|
return r.getPurchaseOrderPurchasingAnalytics(ctx, organizationID, outletID, outletName, dateFrom, dateTo, groupBy)
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) getPurchaseOrderPurchasingAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, outletName *string, dateFrom, dateTo time.Time, groupBy string) (*entities.PurchasingAnalytics, error) {
|
|
var summary entities.PurchasingSummary
|
|
summaryQuery := r.db.WithContext(ctx).
|
|
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
|
|
THEN COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) / COUNT(DISTINCT po.id)
|
|
ELSE 0
|
|
END as average_purchase_order_value,
|
|
COUNT(DISTINCT i.id) as total_ingredients,
|
|
COUNT(DISTINCT COALESCE(po.vendor_id::text, 'no-vendor')) as total_vendors
|
|
`).
|
|
Joins("LEFT JOIN purchase_order_items poi ON poi.purchase_order_id = po.id").
|
|
Joins("LEFT JOIN purchase_categories pc ON poi.purchase_category_id = pc.id").
|
|
Joins("LEFT JOIN ingredients i ON poi.ingredient_id = i.id").
|
|
Joins("LEFT JOIN units u ON poi.unit_id = u.id").
|
|
Where("po.organization_id = ?", organizationID).
|
|
Where("po.status != ?", "cancelled").
|
|
Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo)
|
|
summaryQuery = r.applyPurchaseOrderItemOutletFilter(summaryQuery, outletID)
|
|
|
|
if err := summaryQuery.Scan(&summary).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
var dateFormat string
|
|
switch groupBy {
|
|
case "hour":
|
|
dateFormat = "DATE_TRUNC('hour', po.created_at)"
|
|
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 data []entities.PurchasingAnalyticsData
|
|
dataQuery := r.db.WithContext(ctx).
|
|
Table("purchase_orders po").
|
|
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
|
|
`).
|
|
Joins("LEFT JOIN purchase_order_items poi ON poi.purchase_order_id = po.id").
|
|
Joins("LEFT JOIN purchase_categories pc ON poi.purchase_category_id = pc.id").
|
|
Joins("LEFT JOIN ingredients i ON poi.ingredient_id = i.id").
|
|
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)
|
|
dataQuery = r.applyPurchaseOrderItemOutletFilter(dataQuery, outletID)
|
|
|
|
if err := dataQuery.Scan(&data).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
var ingredientData []entities.PurchasingIngredientData
|
|
ingredientQuery := r.db.WithContext(ctx).
|
|
Table("purchase_order_items poi").
|
|
Select(`
|
|
i.id as ingredient_id,
|
|
i.name as ingredient_name,
|
|
COALESCE(SUM(poi.quantity), 0) as quantity,
|
|
COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) as total_cost,
|
|
CASE
|
|
WHEN SUM(poi.quantity) > 0
|
|
THEN COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) / SUM(poi.quantity)
|
|
ELSE 0
|
|
END as average_unit_cost,
|
|
COUNT(DISTINCT po.id) as purchase_order_count
|
|
`).
|
|
Joins("JOIN purchase_orders po ON poi.purchase_order_id = po.id").
|
|
Joins("JOIN purchase_categories pc ON poi.purchase_category_id = pc.id").
|
|
Joins("JOIN ingredients i ON poi.ingredient_id = i.id").
|
|
Joins("LEFT JOIN units u ON poi.unit_id = u.id").
|
|
Where("po.organization_id = ?", organizationID).
|
|
Where("po.status != ?", "cancelled").
|
|
Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo).
|
|
Group("i.id, i.name").
|
|
Order("total_cost DESC")
|
|
ingredientQuery = r.applyPurchaseOrderItemOutletFilter(ingredientQuery, outletID)
|
|
|
|
if err := ingredientQuery.Scan(&ingredientData).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
var vendorData []entities.PurchasingVendorData
|
|
vendorQuery := r.db.WithContext(ctx).
|
|
Table("purchase_orders po").
|
|
Select(`
|
|
v.id as vendor_id,
|
|
COALESCE(v.name, 'No Vendor') as vendor_name,
|
|
COALESCE(SUM(`+purchaseOrderItemTotalAmountSQL()+`), 0) as total_cost,
|
|
COUNT(DISTINCT po.id) as purchase_order_count,
|
|
COUNT(DISTINCT i.id) as ingredient_count,
|
|
COALESCE(SUM(poi.quantity), 0) as quantity
|
|
`).
|
|
Joins("LEFT JOIN vendors v ON po.vendor_id = v.id").
|
|
Joins("LEFT JOIN purchase_order_items poi ON poi.purchase_order_id = po.id").
|
|
Joins("LEFT JOIN purchase_categories pc ON poi.purchase_category_id = pc.id").
|
|
Joins("LEFT JOIN ingredients i ON poi.ingredient_id = i.id").
|
|
Joins("LEFT JOIN units u ON poi.unit_id = u.id").
|
|
Where("po.organization_id = ?", organizationID).
|
|
Where("po.status != ?", "cancelled").
|
|
Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo).
|
|
Group("v.id, COALESCE(v.name, 'No Vendor')").
|
|
Order("total_cost DESC")
|
|
vendorQuery = r.applyPurchaseOrderItemOutletFilter(vendorQuery, outletID)
|
|
|
|
if err := vendorQuery.Scan(&vendorData).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
return &entities.PurchasingAnalytics{
|
|
OutletName: outletName,
|
|
Summary: summary,
|
|
Data: data,
|
|
IngredientData: ingredientData,
|
|
VendorData: vendorData,
|
|
}, nil
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) applyPurchaseOrderItemOutletFilter(query *gorm.DB, outletID *uuid.UUID) *gorm.DB {
|
|
if outletID == nil {
|
|
return query
|
|
}
|
|
return query.Where("po.outlet_id = ?", *outletID)
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) GetProductAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, limit int) ([]*entities.ProductAnalytics, error) {
|
|
var results []*entities.ProductAnalytics
|
|
|
|
query := r.db.WithContext(ctx).
|
|
Table("order_items oi").
|
|
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,
|
|
COALESCE(SUM(oi.quantity), 0) as quantity_sold,
|
|
COALESCE(SUM(oi.total_price), 0) as revenue,
|
|
CASE
|
|
WHEN SUM(oi.quantity) > 0 THEN COALESCE(SUM(oi.total_price), 0) / SUM(oi.quantity)
|
|
ELSE 0
|
|
END as average_price,
|
|
COUNT(DISTINCT oi.order_id) as order_count,
|
|
COALESCE((
|
|
SELECT SUM(pr.quantity * (1 + COALESCE(pr.waste_percentage, 0)/100.0) * i.cost)
|
|
FROM product_recipes pr
|
|
JOIN ingredients i ON pr.ingredient_id = i.id
|
|
WHERE pr.product_id = p.id
|
|
), p.cost, 0) as standard_hpp_per_unit,
|
|
COALESCE((
|
|
SELECT SUM(pr.quantity * (1 + COALESCE(pr.waste_percentage, 0)/100.0) * i.cost)
|
|
FROM product_recipes pr
|
|
JOIN ingredients i ON pr.ingredient_id = i.id
|
|
WHERE pr.product_id = p.id
|
|
), p.cost, 0) * COALESCE(SUM(oi.quantity), 0) as standard_hpp_total,
|
|
CASE
|
|
WHEN SUM(oi.quantity) > 0 THEN COALESCE(SUM(oi.total_cost), 0) / SUM(oi.quantity)
|
|
ELSE 0
|
|
END as fifo_hpp_per_unit,
|
|
COALESCE(SUM(oi.total_cost), 0) as fifo_hpp_total,
|
|
COALESCE(mahpp.hpp_per_unit, p.cost, 0) as moving_average_hpp_per_unit,
|
|
COALESCE(mahpp.hpp_per_unit, p.cost, 0) * COALESCE(SUM(oi.quantity), 0) as moving_average_hpp_total
|
|
`).
|
|
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").
|
|
Joins("JOIN ingredients ing ON pr2.ingredient_id = ing.id").
|
|
Joins("LEFT JOIN (?) ma ON ma.ingredient_id = pr2.ingredient_id",
|
|
r.db.Table("inventory_movements im").
|
|
Select("im.item_id as ingredient_id, CASE WHEN SUM(im.quantity) > 0 THEN SUM(im.total_cost) / SUM(im.quantity) ELSE 0 END as moving_avg_cost").
|
|
Where("im.movement_type = ?", "purchase").
|
|
Where("im.item_type = ?", "INGREDIENT").
|
|
Where("im.organization_id = ?", organizationID).
|
|
Where("im.created_at <= ?", dateTo).
|
|
Group("im.item_id"),
|
|
).
|
|
Group("pr2.product_id"),
|
|
).
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.is_void = ?", false).
|
|
Where("o.is_refund = ?", false).
|
|
Where("o.payment_status = ?", entities.PaymentStatusCompleted).
|
|
Where("oi.status != ?", entities.OrderItemStatusCancelled).
|
|
Where("o.created_at >= ? AND o.created_at <= ?", dateFrom, dateTo)
|
|
|
|
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").
|
|
Order("revenue DESC").
|
|
Limit(limit).
|
|
Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) GetProductAnalyticsPerCategory(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]*entities.ProductAnalyticsPerCategory, error) {
|
|
var results []*entities.ProductAnalyticsPerCategory
|
|
|
|
query := r.db.WithContext(ctx).
|
|
Table("order_items oi").
|
|
Select(`
|
|
c.id as category_id,
|
|
c.name as category_name,
|
|
COALESCE(SUM(CASE WHEN oi.is_fully_refunded = false THEN oi.total_price - COALESCE(oi.refund_amount, 0) ELSE 0 END), 0) as total_revenue,
|
|
COALESCE(SUM(CASE WHEN oi.is_fully_refunded = false THEN oi.quantity - COALESCE(oi.refund_quantity, 0) ELSE 0 END), 0) as total_quantity,
|
|
COUNT(DISTINCT p.id) as product_count,
|
|
COUNT(DISTINCT oi.order_id) as order_count,
|
|
COALESCE(SUM(CASE WHEN oi.is_fully_refunded = false THEN COALESCE(shpp.hpp_per_unit, p.cost, 0) * (oi.quantity - COALESCE(oi.refund_quantity, 0)) ELSE 0 END), 0) as total_standard_hpp,
|
|
COALESCE(SUM(CASE WHEN oi.is_fully_refunded = false THEN oi.total_cost * ((oi.quantity - COALESCE(oi.refund_quantity, 0))::float / NULLIF(oi.quantity, 0)) ELSE 0 END), 0) as total_fifo_hpp,
|
|
COALESCE(SUM(CASE WHEN oi.is_fully_refunded = false THEN COALESCE(mahpp.hpp_per_unit, p.cost, 0) * (oi.quantity - COALESCE(oi.refund_quantity, 0)) ELSE 0 END), 0) as total_moving_average_hpp
|
|
`).
|
|
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 (SELECT pr.product_id, SUM(pr.quantity * (1 + COALESCE(pr.waste_percentage, 0)/100.0) * i.cost) as hpp_per_unit FROM product_recipes pr JOIN ingredients i ON pr.ingredient_id = i.id GROUP BY pr.product_id) shpp ON shpp.product_id = p.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").
|
|
Joins("JOIN ingredients ing ON pr2.ingredient_id = ing.id").
|
|
Joins("LEFT JOIN (?) ma ON ma.ingredient_id = pr2.ingredient_id",
|
|
r.db.Table("inventory_movements im").
|
|
Select("im.item_id as ingredient_id, CASE WHEN SUM(im.quantity) > 0 THEN SUM(im.total_cost) / SUM(im.quantity) ELSE 0 END as moving_avg_cost").
|
|
Where("im.movement_type = ?", "purchase").
|
|
Where("im.item_type = ?", "INGREDIENT").
|
|
Where("im.organization_id = ?", organizationID).
|
|
Where("im.created_at <= ?", dateTo).
|
|
Group("im.item_id"),
|
|
).
|
|
Group("pr2.product_id"),
|
|
).
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.is_void = ?", false).
|
|
Where("o.is_refund = ?", false).
|
|
Where("o.payment_status = ?", entities.PaymentStatusCompleted).
|
|
Where("oi.status != ?", entities.OrderItemStatusCancelled).
|
|
Where("o.created_at >= ? AND o.created_at <= ?", dateFrom, dateTo)
|
|
|
|
query = r.resolveOutletID(query, outletID, "o.outlet_id")
|
|
|
|
err := query.
|
|
Group("c.id, c.name").
|
|
Order("c.name ASC").
|
|
Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) GetDashboardOverview(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) (*entities.DashboardOverview, error) {
|
|
var result entities.DashboardOverview
|
|
|
|
query := r.db.WithContext(ctx).
|
|
Table("orders o").
|
|
Select(`
|
|
COALESCE(SUM(CASE WHEN o.is_void = false AND o.is_refund = false AND o.payment_status = 'completed' THEN o.total_amount ELSE 0 END), 0) as total_sales,
|
|
COUNT(CASE WHEN o.is_void = false THEN o.id END) as total_orders,
|
|
CASE
|
|
WHEN COUNT(CASE WHEN o.is_void = false THEN o.id END) > 0
|
|
THEN COALESCE(SUM(CASE WHEN o.is_void = false THEN o.total_amount ELSE 0 END), 0) / COUNT(CASE WHEN o.is_void = false THEN o.id END)
|
|
ELSE 0
|
|
END as average_order_value,
|
|
COUNT(DISTINCT o.customer_id) as total_customers,
|
|
COUNT(CASE WHEN o.is_void = true THEN o.id END) as voided_orders,
|
|
COUNT(CASE WHEN o.is_refund = true THEN o.id END) as refunded_orders
|
|
`).
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.created_at >= ? AND o.created_at <= ?", dateFrom, dateTo)
|
|
|
|
query = r.resolveOutletID(query, outletID, "o.outlet_id")
|
|
|
|
err := query.Scan(&result).Error
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
return &result, nil
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) GetProfitLossAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, groupBy string) (*entities.ProfitLossAnalytics, error) {
|
|
mtdStart := time.Date(dateTo.Year(), dateTo.Month(), 1, 0, 0, 0, 0, dateTo.Location())
|
|
todayStart := time.Date(dateTo.Year(), dateTo.Month(), dateTo.Day(), 0, 0, 0, 0, dateTo.Location())
|
|
todayEnd := todayStart.Add(24 * time.Hour).Add(-time.Nanosecond)
|
|
|
|
var summary entities.ProfitLossSummary
|
|
summaryQuery := r.db.WithContext(ctx).
|
|
Table("orders o").
|
|
Select(`
|
|
COALESCE(SUM(o.total_amount), 0) as total_revenue,
|
|
COALESCE(SUM(o.total_cost), 0) as total_cost,
|
|
COALESCE(SUM(o.total_amount - o.total_cost), 0) as gross_profit,
|
|
CASE
|
|
WHEN SUM(o.total_amount) > 0
|
|
THEN (SUM(o.total_amount - o.total_cost) / SUM(o.total_amount)) * 100
|
|
ELSE 0
|
|
END as gross_profit_margin,
|
|
COALESCE(SUM(o.tax_amount), 0) as total_tax,
|
|
COALESCE(SUM(o.discount_amount), 0) as total_discount,
|
|
COALESCE(SUM(o.total_amount - o.total_cost - o.discount_amount), 0) as net_profit,
|
|
CASE
|
|
WHEN SUM(o.total_amount) > 0
|
|
THEN (SUM(o.total_amount - o.total_cost - o.discount_amount) / SUM(o.total_amount)) * 100
|
|
ELSE 0
|
|
END as net_profit_margin,
|
|
COUNT(o.id) as total_orders,
|
|
CASE
|
|
WHEN COUNT(o.id) > 0
|
|
THEN SUM(o.total_amount - o.total_cost - o.discount_amount) / COUNT(o.id)
|
|
ELSE 0
|
|
END as average_profit,
|
|
CASE
|
|
WHEN SUM(o.total_cost) > 0
|
|
THEN (SUM(o.total_amount - o.total_cost) / SUM(o.total_cost)) * 100
|
|
ELSE 0
|
|
END as profitability_ratio
|
|
`).
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.status = ?", entities.OrderStatusCompleted).
|
|
Where("o.payment_status = ?", entities.PaymentStatusCompleted).
|
|
Where("o.is_void = false AND o.is_refund = false").
|
|
Where("o.created_at >= ? AND o.created_at <= ?", dateFrom, dateTo)
|
|
summaryQuery = r.resolveOutletID(summaryQuery, outletID, "o.outlet_id")
|
|
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 {
|
|
case "hour":
|
|
timeFormat = "DATE_TRUNC('hour', o.created_at)"
|
|
case "week":
|
|
timeFormat = "DATE_TRUNC('week', o.created_at)"
|
|
case "month":
|
|
timeFormat = "DATE_TRUNC('month', o.created_at)"
|
|
default:
|
|
timeFormat = "DATE_TRUNC('day', o.created_at)"
|
|
}
|
|
|
|
var data []entities.ProfitLossData
|
|
dataQuery := r.db.WithContext(ctx).
|
|
Table("orders o").
|
|
Select(`
|
|
`+timeFormat+` as date,
|
|
COALESCE(SUM(o.total_amount), 0) as revenue,
|
|
COALESCE(SUM(o.total_cost), 0) as cost,
|
|
COALESCE(SUM(o.total_amount - o.total_cost), 0) as gross_profit,
|
|
CASE
|
|
WHEN SUM(o.total_amount) > 0
|
|
THEN (SUM(o.total_amount - o.total_cost) / SUM(o.total_amount)) * 100
|
|
ELSE 0
|
|
END as gross_profit_margin,
|
|
COALESCE(SUM(o.tax_amount), 0) as tax,
|
|
COALESCE(SUM(o.discount_amount), 0) as discount,
|
|
COALESCE(SUM(o.total_amount - o.total_cost - o.discount_amount), 0) as net_profit,
|
|
CASE
|
|
WHEN SUM(o.total_amount) > 0
|
|
THEN (SUM(o.total_amount - o.total_cost - o.discount_amount) / SUM(o.total_amount)) * 100
|
|
ELSE 0
|
|
END as net_profit_margin,
|
|
COUNT(o.id) as orders
|
|
`).
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.status = ?", entities.OrderStatusCompleted).
|
|
Where("o.payment_status = ?", entities.PaymentStatusCompleted).
|
|
Where("o.is_void = false AND o.is_refund = false").
|
|
Where("o.created_at >= ? AND o.created_at <= ?", dateFrom, dateTo).
|
|
Group(timeFormat).
|
|
Order(timeFormat)
|
|
dataQuery = r.resolveOutletID(dataQuery, outletID, "o.outlet_id")
|
|
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).
|
|
Table("order_items oi").
|
|
Select(`
|
|
p.id as product_id,
|
|
p.name as product_name,
|
|
c.id as category_id,
|
|
c.name as category_name,
|
|
SUM(CASE WHEN oi.is_fully_refunded = false THEN oi.quantity - COALESCE(oi.refund_quantity, 0) ELSE 0 END) as quantity_sold,
|
|
SUM(CASE WHEN oi.is_fully_refunded = false THEN oi.total_price - COALESCE(oi.refund_amount, 0) ELSE 0 END) as revenue,
|
|
SUM(CASE WHEN oi.is_fully_refunded = false THEN oi.total_cost * ((oi.quantity - COALESCE(oi.refund_quantity, 0))::float / NULLIF(oi.quantity, 0)) ELSE 0 END) as cost,
|
|
SUM(CASE WHEN oi.is_fully_refunded = false THEN (oi.total_price - COALESCE(oi.refund_amount, 0)) - (oi.total_cost * ((oi.quantity - COALESCE(oi.refund_quantity, 0))::float / NULLIF(oi.quantity, 0))) ELSE 0 END) as gross_profit,
|
|
CASE
|
|
WHEN SUM(CASE WHEN oi.is_fully_refunded = false THEN oi.total_price - COALESCE(oi.refund_amount, 0) ELSE 0 END) > 0
|
|
THEN (SUM(CASE WHEN oi.is_fully_refunded = false THEN (oi.total_price - COALESCE(oi.refund_amount, 0)) - (oi.total_cost * ((oi.quantity - COALESCE(oi.refund_quantity, 0))::float / NULLIF(oi.quantity, 0))) ELSE 0 END) / SUM(CASE WHEN oi.is_fully_refunded = false THEN oi.total_price - COALESCE(oi.refund_amount, 0) ELSE 0 END)) * 100
|
|
ELSE 0
|
|
END as gross_profit_margin,
|
|
AVG(CASE WHEN oi.is_fully_refunded = false THEN oi.unit_price ELSE NULL END) as average_price,
|
|
AVG(CASE WHEN oi.is_fully_refunded = false THEN oi.unit_cost ELSE NULL END) as average_cost,
|
|
AVG(CASE WHEN oi.is_fully_refunded = false THEN oi.unit_price - oi.unit_cost ELSE NULL END) as profit_per_unit
|
|
`).
|
|
Joins("JOIN orders o ON oi.order_id = o.id").
|
|
Joins("JOIN products p ON oi.product_id = p.id").
|
|
Joins("JOIN categories c ON p.category_id = c.id").
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.status = ?", entities.OrderStatusCompleted).
|
|
Where("o.payment_status = ?", entities.PaymentStatusCompleted).
|
|
Where("o.is_void = false AND o.is_refund = false").
|
|
Where("oi.status != ?", entities.OrderItemStatusCancelled).
|
|
Where("o.created_at >= ? AND o.created_at <= ?", dateFrom, dateTo).
|
|
Group("p.id, p.name, c.id, c.name").
|
|
Order("p.name ASC").
|
|
Limit(1000)
|
|
productQuery = r.resolveOutletID(productQuery, outletID, "o.outlet_id")
|
|
if err := productQuery.Scan(&productData).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
type revenueCostResult struct {
|
|
Revenue float64
|
|
Cost float64
|
|
}
|
|
|
|
var todayRC revenueCostResult
|
|
todayQuery := r.db.WithContext(ctx).
|
|
Table("orders o").
|
|
Select(`
|
|
COALESCE(SUM(o.total_amount), 0) as revenue,
|
|
COALESCE(SUM(o.total_cost), 0) as cost
|
|
`).
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.status = ?", entities.OrderStatusCompleted).
|
|
Where("o.payment_status = ?", entities.PaymentStatusCompleted).
|
|
Where("o.is_void = false AND o.is_refund = false").
|
|
Where("o.created_at >= ? AND o.created_at <= ?", todayStart, todayEnd)
|
|
todayQuery = r.resolveOutletID(todayQuery, outletID, "o.outlet_id")
|
|
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).
|
|
Table("orders o").
|
|
Select(`
|
|
COALESCE(SUM(o.total_amount), 0) as revenue,
|
|
COALESCE(SUM(o.total_cost), 0) as cost
|
|
`).
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.status = ?", entities.OrderStatusCompleted).
|
|
Where("o.payment_status = ?", entities.PaymentStatusCompleted).
|
|
Where("o.is_void = false AND o.is_refund = false").
|
|
Where("o.created_at >= ? AND o.created_at <= ?", mtdStart, todayEnd)
|
|
mtdQuery = r.resolveOutletID(mtdQuery, outletID, "o.outlet_id")
|
|
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,
|
|
Data: data,
|
|
ProductData: productData,
|
|
TodayRevenue: todayRC.Revenue,
|
|
TodayCost: todayRC.Cost,
|
|
MtdRevenue: mtdRC.Revenue,
|
|
MtdCost: mtdRC.Cost,
|
|
TodayExpenseByCategory: todayExpenseByCategory,
|
|
MtdExpenseByCategory: mtdExpenseByCategory,
|
|
OperationalExpenseItems: opsItems,
|
|
}, 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
|
|
|
|
query := r.db.WithContext(ctx).
|
|
Table("expense_items ei").
|
|
Select(`COALESCE(parent_coa.name, coa.name, 'Lain-lain') as category_name, COALESCE(SUM(ei.amount), 0) as amount`).
|
|
Joins("JOIN expenses e ON ei.expense_id = e.id").
|
|
Joins("JOIN chart_of_accounts coa ON ei.chart_of_account_id = coa.id").
|
|
Joins("LEFT JOIN chart_of_accounts parent_coa ON coa.parent_id = parent_coa.id").
|
|
Where("e.organization_id = ?", organizationID).
|
|
Where("e.status = ?", "approved").
|
|
Where("e.transaction_date >= ? AND e.transaction_date <= ?", dateFrom, dateTo)
|
|
|
|
if outletID != nil {
|
|
query = query.Where("e.outlet_id = ?", *outletID)
|
|
}
|
|
|
|
err := query.
|
|
Group("COALESCE(parent_coa.name, coa.name, 'Lain-lain')").
|
|
Order("COALESCE(parent_coa.name, coa.name, 'Lain-lain')").
|
|
Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) getOperationalExpenseItems(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("expense_items ei").
|
|
Select(`COALESCE(NULLIF(ei.item, ''), ei.description, coa.name) as item, COALESCE(SUM(ei.amount), 0) as amount`).
|
|
Joins("JOIN expenses e ON ei.expense_id = e.id").
|
|
Joins("JOIN chart_of_accounts coa ON ei.chart_of_account_id = coa.id").
|
|
Where("e.organization_id = ?", organizationID).
|
|
Where("e.status = ?", "approved").
|
|
Where("e.transaction_date >= ? AND e.transaction_date <= ?", dateFrom, dateTo)
|
|
|
|
if outletID != nil {
|
|
query = query.Where("e.outlet_id = ?", *outletID)
|
|
}
|
|
|
|
err := query.
|
|
Group("COALESCE(NULLIF(ei.item, ''), ei.description, coa.name)").
|
|
Order("amount DESC").
|
|
Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) GetExclusiveSummaryAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) (*entities.ExclusiveSummaryAnalytics, error) {
|
|
type salesResult struct {
|
|
SalesTotal float64
|
|
SalesCount int64
|
|
}
|
|
|
|
var sales salesResult
|
|
salesQuery := r.db.WithContext(ctx).
|
|
Table("orders o").
|
|
Select(`
|
|
COALESCE(SUM(o.total_amount), 0) as sales_total,
|
|
COUNT(o.id) as sales_count
|
|
`).
|
|
Where("o.organization_id = ?", organizationID).
|
|
Where("o.status = ?", entities.OrderStatusCompleted).
|
|
Where("o.payment_status = ?", entities.PaymentStatusCompleted).
|
|
Where("o.is_void = false AND o.is_refund = false").
|
|
Where("o.created_at >= ? AND o.created_at <= ?", dateFrom, dateTo)
|
|
salesQuery = r.resolveOutletID(salesQuery, outletID, "o.outlet_id")
|
|
if err := salesQuery.Scan(&sales).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
hppBreakdown, err := r.getExclusiveSummaryHPPBreakdown(ctx, organizationID, outletID, dateFrom, dateTo)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
operationalExpenseBreakdown, err := r.getExclusiveSummaryOperationalExpenseBreakdown(ctx, organizationID, outletID, dateFrom, dateTo)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
dailySummary, err := r.getExclusiveSummaryDailySummary(ctx, organizationID, outletID, dateFrom, dateTo)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
dailyTransactions, err := r.getExclusiveSummaryDailyTransactions(ctx, organizationID, outletID, dateFrom, dateTo)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
return &entities.ExclusiveSummaryAnalytics{
|
|
SalesTotal: sales.SalesTotal,
|
|
SalesCount: sales.SalesCount,
|
|
HPPBreakdown: hppBreakdown,
|
|
OperationalExpenseBreakdown: operationalExpenseBreakdown,
|
|
DailySummary: dailySummary,
|
|
DailyTransactions: dailyTransactions,
|
|
}, nil
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) getExclusiveSummaryHPPBreakdown(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]entities.ExclusiveSummaryCategoryTotal, error) {
|
|
var results []entities.ExclusiveSummaryCategoryTotal
|
|
|
|
query := r.db.WithContext(ctx).
|
|
Table("purchase_order_items poi").
|
|
Select(`
|
|
pc.code as category_code,
|
|
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").
|
|
Joins("LEFT JOIN ingredients i ON poi.ingredient_id = i.id").
|
|
Joins("LEFT JOIN units u ON poi.unit_id = u.id").
|
|
Where("po.organization_id = ?", organizationID).
|
|
Where("pc.type = ?", entities.PurchaseCategoryTypeRawMaterial).
|
|
Where("po.status = ?", "received").
|
|
Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo)
|
|
query = r.applyPurchaseOrderItemOutletFilter(query, outletID)
|
|
|
|
err := query.
|
|
Group("pc.id, pc.code, pc.name, pc.sort_order").
|
|
Order("pc.sort_order ASC, pc.name ASC").
|
|
Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) getExclusiveSummaryOperationalExpenseBreakdown(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]entities.ExclusiveSummaryCategoryTotal, error) {
|
|
var results []entities.ExclusiveSummaryCategoryTotal
|
|
|
|
query := r.db.WithContext(ctx).
|
|
Table("purchase_order_items poi").
|
|
Select(`
|
|
pc.code as category_code,
|
|
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("pc.type = ?", entities.PurchaseCategoryTypeExpense).
|
|
Where("po.status = ?", "received").
|
|
Where("po.transaction_date >= ? AND po.transaction_date <= ?", dateFrom, dateTo)
|
|
query = r.applyPurchaseOrderItemOutletFilter(query, outletID)
|
|
|
|
err := query.
|
|
Group("pc.id, pc.code, pc.name, pc.sort_order").
|
|
Order("pc.sort_order ASC, pc.name ASC").
|
|
Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) getExclusiveSummaryDailySummary(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]entities.ExclusiveSummaryDailySummary, error) {
|
|
var results []entities.ExclusiveSummaryDailySummary
|
|
rawQuery, args := r.exclusiveSummaryPurchaseOrderItemQuery(organizationID, outletID, dateFrom, dateTo)
|
|
|
|
err := r.db.WithContext(ctx).Raw(`
|
|
SELECT date, COUNT(*) as transaction_count, COALESCE(SUM(amount), 0) as total_cost
|
|
FROM (`+rawQuery+`) transactions
|
|
GROUP BY date
|
|
ORDER BY date ASC
|
|
`, args...).Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) getExclusiveSummaryDailyTransactions(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) ([]entities.ExclusiveSummaryDailyTransaction, error) {
|
|
var results []entities.ExclusiveSummaryDailyTransaction
|
|
rawQuery, args := r.exclusiveSummaryPurchaseOrderItemQuery(organizationID, outletID, dateFrom, dateTo)
|
|
|
|
err := r.db.WithContext(ctx).Raw(`
|
|
SELECT date, category_code, category_name, description, amount, source
|
|
FROM (`+rawQuery+`) transactions
|
|
ORDER BY date ASC, category_name ASC, description ASC
|
|
`, args...).Scan(&results).Error
|
|
|
|
return results, err
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) exclusiveSummaryPurchaseOrderItemQuery(organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time) (string, []interface{}) {
|
|
outletFilter := ""
|
|
args := []interface{}{
|
|
organizationID,
|
|
"received",
|
|
dateFrom,
|
|
dateTo,
|
|
}
|
|
|
|
if outletID != nil {
|
|
outletFilter = "AND po.outlet_id = ?"
|
|
args = append(args, *outletID)
|
|
}
|
|
|
|
query := `
|
|
SELECT
|
|
DATE(po.transaction_date) as date,
|
|
pc.code as category_code,
|
|
pc.name as category_name,
|
|
COALESCE(NULLIF(poi.description, ''), i.name, pc.name) as description,
|
|
` + purchaseOrderItemTotalAmountSQL() + ` as amount,
|
|
'purchase_order' as source
|
|
FROM purchase_order_items poi
|
|
JOIN purchase_orders po ON poi.purchase_order_id = po.id
|
|
JOIN purchase_categories pc ON poi.purchase_category_id = pc.id
|
|
LEFT JOIN ingredients i ON poi.ingredient_id = i.id
|
|
LEFT JOIN units u ON poi.unit_id = u.id
|
|
WHERE po.organization_id = ?
|
|
AND po.status = ?
|
|
AND po.transaction_date >= ? AND po.transaction_date <= ?
|
|
` + outletFilter + `
|
|
`
|
|
|
|
return query, args
|
|
}
|
|
|
|
func (r *AnalyticsRepositoryImpl) GetExclusiveSummaryBankBalances(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID) ([]entities.ExclusiveSummaryBankBalance, error) {
|
|
type accountBalance struct {
|
|
Name string
|
|
OpeningBalance float64
|
|
CurrentBalance float64
|
|
Description *string
|
|
}
|
|
|
|
var accounts []accountBalance
|
|
query := r.db.WithContext(ctx).
|
|
Table("accounts").
|
|
Select("name, opening_balance, current_balance, description").
|
|
Where("organization_id = ?", organizationID).
|
|
Where("account_type IN ?", []entities.AccountType{entities.AccountTypeCash, entities.AccountTypeWallet, entities.AccountTypeBank}).
|
|
Where("is_active = ?", true)
|
|
|
|
if outletID != nil {
|
|
query = query.Where("outlet_id = ? OR outlet_id IS NULL", *outletID)
|
|
} else {
|
|
query = query.Where("outlet_id IS NULL")
|
|
}
|
|
|
|
err := query.
|
|
Order("number ASC, name ASC").
|
|
Scan(&accounts).Error
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
balances := make([]entities.ExclusiveSummaryBankBalance, len(accounts))
|
|
for i, account := range accounts {
|
|
openingBalance := account.OpeningBalance
|
|
closingBalance := account.CurrentBalance
|
|
balances[i] = entities.ExclusiveSummaryBankBalance{
|
|
Bank: account.Name,
|
|
OpeningBalance: &openingBalance,
|
|
ClosingBalance: &closingBalance,
|
|
Notes: account.Description,
|
|
}
|
|
}
|
|
|
|
return balances, nil
|
|
}
|