base example counting sapronak

internal/modules/closings/controllers/closing.controller.go

@@ -74,3 +74,24 @@ func (u *ClosingController) GetOne(c *fiber.Ctx) error {
 			Data:    dto.ToClosingListDTO(*result),
 		})
 }
+
+func (u *ClosingController) GetSapronakReport(c *fiber.Ctx) error {
+	query := &validation.SapronakQuery{
+		ProjectFlockID: uint(c.QueryInt("project_flock_id", 0)),
+		KandangID:      uint(c.QueryInt("kandang_id", 0)),
+		Status:         c.Query("status"),
+	}
+
+	result, err := u.ClosingService.GetSapronakReport(c, query)
+	if err != nil {
+		return err
+	}
+
+	return c.Status(fiber.StatusOK).
+		JSON(response.Success{
+			Code:    fiber.StatusOK,
+			Status:  "success",
+			Message: "Get sapronak report successfully",
+			Data:    result,
+		})
+}

internal/modules/closings/dto/sapronak.dto.go

@@ -0,0 +1,30 @@
+package dto
+
+import "time"
+
+type SapronakItemDTO struct {
+	ProductID     uint    `json:"product_id"`
+	ProductName   string  `json:"product_name"`
+	Flag          string  `json:"flag"`
+	IncomingQty   float64 `json:"incoming_qty"`
+	IncomingValue float64 `json:"incoming_value"`
+	UsageQty      float64 `json:"usage_qty"`
+	UsageValue    float64 `json:"usage_value"`
+	RemainingQty  float64 `json:"remaining_qty"`
+	AveragePrice  float64 `json:"average_price"`
+}
+
+type SapronakReportDTO struct {
+	ProjectFlockKandangID uint              `json:"project_flock_kandang_id"`
+	ProjectFlockID        uint              `json:"project_flock_id"`
+	ProjectName           string            `json:"project_name"`
+	KandangID             uint              `json:"kandang_id"`
+	KandangName           string            `json:"kandang_name"`
+	Period                int               `json:"period"`
+	Status                string            `json:"status"`
+	StartDate             *time.Time        `json:"start_date,omitempty"`
+	EndDate               *time.Time        `json:"end_date,omitempty"`
+	TotalIncomingValue    float64           `json:"total_incoming_value"`
+	TotalUsageValue       float64           `json:"total_usage_value"`
+	Items                 []SapronakItemDTO `json:"items"`
+}

internal/modules/closings/module.go

@@ -7,6 +7,7 @@ import (
 
 	rClosing "gitlab.com/mbugroup/lti-api.git/internal/modules/closings/repositories"
 	sClosing "gitlab.com/mbugroup/lti-api.git/internal/modules/closings/services"
+	rProjectFlock "gitlab.com/mbugroup/lti-api.git/internal/modules/production/project_flocks/repositories"
 
 	rUser "gitlab.com/mbugroup/lti-api.git/internal/modules/users/repositories"
 	sUser "gitlab.com/mbugroup/lti-api.git/internal/modules/users/services"
@@ -16,11 +17,11 @@ type ClosingModule struct{}
 
 func (ClosingModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate *validator.Validate) {
 	closingRepo := rClosing.NewClosingRepository(db)
+	projectFlockKandangRepo := rProjectFlock.NewProjectFlockKandangRepository(db)
 	userRepo := rUser.NewUserRepository(db)
 
-	closingService := sClosing.NewClosingService(closingRepo, validate)
+	closingService := sClosing.NewClosingService(closingRepo, projectFlockKandangRepo, validate)
 	userService := sUser.NewUserService(userRepo, validate)
 
 	ClosingRoutes(router, userService, closingService)
 }
-

internal/modules/closings/route.go

@@ -21,5 +21,6 @@ func ClosingRoutes(v1 fiber.Router, u user.UserService, s closing.ClosingService
 	// route.Delete("/:id", m.Auth(u), ctrl.DeleteOne)
 
 	route.Get("/", ctrl.GetAll)
+	route.Get("/sapronak/report", ctrl.GetSapronakReport)
 	route.Get("/:id", ctrl.GetOne)
 }

internal/modules/closings/services/closing.service.go

@@ -1,11 +1,17 @@
 package service
 
 import (
+	"context"
 	"errors"
+	"sort"
+	"strings"
+	"time"
 
 	entity "gitlab.com/mbugroup/lti-api.git/internal/entities"
+	"gitlab.com/mbugroup/lti-api.git/internal/modules/closings/dto"
 	repository "gitlab.com/mbugroup/lti-api.git/internal/modules/closings/repositories"
 	validation "gitlab.com/mbugroup/lti-api.git/internal/modules/closings/validations"
+	projectFlockRepo "gitlab.com/mbugroup/lti-api.git/internal/modules/production/project_flocks/repositories"
 	"gitlab.com/mbugroup/lti-api.git/internal/utils"
 
 	"github.com/go-playground/validator/v10"
@@ -17,19 +23,26 @@ import (
 type ClosingService interface {
 	GetAll(ctx *fiber.Ctx, params *validation.Query) ([]entity.ProjectFlock, int64, error)
 	GetOne(ctx *fiber.Ctx, id uint) (*entity.ProjectFlock, error)
+	GetSapronakReport(ctx *fiber.Ctx, params *validation.SapronakQuery) ([]dto.SapronakReportDTO, error)
 }
 
 type closingService struct {
-	Log        *logrus.Logger
-	Validate   *validator.Validate
-	Repository repository.ClosingRepository
+	Log                     *logrus.Logger
+	Validate                *validator.Validate
+	Repository              repository.ClosingRepository
+	ProjectFlockKandangRepo projectFlockRepo.ProjectFlockKandangRepository
 }
 
-func NewClosingService(repo repository.ClosingRepository, validate *validator.Validate) ClosingService {
+func NewClosingService(
+	repo repository.ClosingRepository,
+	projectFlockKandangRepo projectFlockRepo.ProjectFlockKandangRepository,
+	validate *validator.Validate,
+) ClosingService {
 	return &closingService{
-		Log:        utils.Log,
-		Validate:   validate,
-		Repository: repo,
+		Log:                     utils.Log,
+		Validate:                validate,
+		Repository:              repo,
+		ProjectFlockKandangRepo: projectFlockKandangRepo,
 	}
 }
 
@@ -70,3 +83,362 @@ func (s closingService) GetOne(c *fiber.Ctx, id uint) (*entity.ProjectFlock, err
 	}
 	return closing, nil
 }
+
+var sapronakFlags = []string{
+	string(utils.FlagDOC),
+	string(utils.FlagPakan),
+	string(utils.FlagOVK),
+}
+
+type sapronakIncomingRow struct {
+	ProductID    uint
+	ProductName  string
+	Flag         string
+	Qty          float64
+	Value        float64
+	DefaultPrice float64
+}
+
+type sapronakUsageRow struct {
+	ProductID    uint
+	ProductName  string
+	Flag         string
+	Qty          float64
+	DefaultPrice float64
+}
+
+func (s closingService) GetSapronakReport(c *fiber.Ctx, params *validation.SapronakQuery) ([]dto.SapronakReportDTO, error) {
+	if err := s.Validate.Struct(params); err != nil {
+		return nil, err
+	}
+
+	pfks, err := s.loadProjectFlockKandangs(c, params)
+	if err != nil {
+		return nil, err
+	}
+	if len(pfks) == 0 {
+		return []dto.SapronakReportDTO{}, nil
+	}
+
+	startMap, err := s.mapStartDates(c.Context(), pfks)
+	if err != nil {
+		s.Log.Errorf("Failed to prepare start dates for sapronak report: %+v", err)
+		return nil, fiber.NewError(fiber.StatusInternalServerError, "Failed to prepare sapronak report")
+	}
+	statusMap, nextStartMap := s.computeStatusAndNextStart(pfks, startMap)
+
+	filterStatus := strings.ToLower(strings.TrimSpace(params.Status))
+	if filterStatus == "" {
+		filterStatus = "all"
+	}
+
+	results := make([]dto.SapronakReportDTO, 0, len(pfks))
+	for _, pfk := range pfks {
+		status := statusMap[pfk.Id]
+		if status == "" {
+			status = "closing"
+		}
+
+		if (filterStatus == "active" && status != "active") || (filterStatus == "closing" && status != "closing") {
+			continue
+		}
+
+		start := startMap[pfk.Id]
+		var startPtr *time.Time
+		if !start.IsZero() {
+			startCopy := start
+			startPtr = &startCopy
+		}
+
+		var endPtr *time.Time
+		if end, ok := nextStartMap[pfk.Id]; ok {
+			endCopy := end
+			endPtr = &endCopy
+		}
+
+		items, totalIncoming, totalUsage, err := s.buildSapronakItems(c.Context(), pfk, startPtr, endPtr)
+		if err != nil {
+			s.Log.Errorf("Failed to build sapronak items for pfk %d: %+v", pfk.Id, err)
+			return nil, fiber.NewError(fiber.StatusInternalServerError, "Failed to calculate sapronak report")
+		}
+
+		results = append(results, dto.SapronakReportDTO{
+			ProjectFlockKandangID: pfk.Id,
+			ProjectFlockID:        pfk.ProjectFlockId,
+			ProjectName:           pfk.ProjectFlock.FlockName,
+			KandangID:             pfk.KandangId,
+			KandangName:           pfk.Kandang.Name,
+			Period:                pfk.Period,
+			Status:                status,
+			StartDate:             startPtr,
+			EndDate:               endPtr,
+			TotalIncomingValue:    totalIncoming,
+			TotalUsageValue:       totalUsage,
+			Items:                 items,
+		})
+	}
+
+	sort.Slice(results, func(i, j int) bool {
+		if results[i].KandangID == results[j].KandangID {
+			if results[i].Period == results[j].Period {
+				return results[i].ProjectFlockKandangID < results[j].ProjectFlockKandangID
+			}
+			return results[i].Period < results[j].Period
+		}
+		return results[i].KandangID < results[j].KandangID
+	})
+
+	return results, nil
+}
+
+func (s closingService) loadProjectFlockKandangs(c *fiber.Ctx, params *validation.SapronakQuery) ([]entity.ProjectFlockKandang, error) {
+	db := s.ProjectFlockKandangRepo.DB().
+		WithContext(c.Context()).
+		Preload("ProjectFlock").
+		Preload("Kandang")
+
+	if params != nil {
+		if params.ProjectFlockID > 0 {
+			db = db.Where("project_flock_kandangs.project_flock_id = ?", params.ProjectFlockID)
+		}
+		if params.KandangID > 0 {
+			db = db.Where("project_flock_kandangs.kandang_id = ?", params.KandangID)
+		}
+	}
+
+	var pfks []entity.ProjectFlockKandang
+	if err := db.Find(&pfks).Error; err != nil {
+		s.Log.Errorf("Failed to load project flock kandangs for sapronak report: %+v", err)
+		return nil, fiber.NewError(fiber.StatusInternalServerError, "Failed to load project flock kandangs")
+	}
+
+	return pfks, nil
+}
+
+func (s closingService) mapStartDates(ctx context.Context, pfks []entity.ProjectFlockKandang) (map[uint]time.Time, error) {
+	result := make(map[uint]time.Time, len(pfks))
+	if len(pfks) == 0 {
+		return result, nil
+	}
+
+	ids := make([]uint, len(pfks))
+	for i, pfk := range pfks {
+		ids[i] = pfk.Id
+	}
+
+	var rows []struct {
+		ProjectFlockKandangID uint       `gorm:"column:project_flock_kandang_id"`
+		StartDate             *time.Time `gorm:"column:start_date"`
+	}
+
+	if err := s.ProjectFlockKandangRepo.DB().
+		WithContext(ctx).
+		Table("project_chickins").
+		Select("project_flock_kandang_id, MIN(chick_in_date) AS start_date").
+		Where("project_flock_kandang_id IN ?", ids).
+		Group("project_flock_kandang_id").
+		Scan(&rows).Error; err != nil {
+		return nil, err
+	}
+
+	for _, row := range rows {
+		if row.StartDate != nil {
+			result[row.ProjectFlockKandangID] = row.StartDate.UTC()
+		}
+	}
+
+	for _, pfk := range pfks {
+		if _, exists := result[pfk.Id]; !exists {
+			result[pfk.Id] = pfk.CreatedAt.UTC()
+		}
+	}
+
+	return result, nil
+}
+
+func (s closingService) computeStatusAndNextStart(pfks []entity.ProjectFlockKandang, startMap map[uint]time.Time) (map[uint]string, map[uint]time.Time) {
+	statusMap := make(map[uint]string, len(pfks))
+	nextStartMap := make(map[uint]time.Time, len(pfks))
+
+	if len(pfks) == 0 {
+		return statusMap, nextStartMap
+	}
+
+	grouped := make(map[uint][]entity.ProjectFlockKandang)
+	for _, pfk := range pfks {
+		grouped[pfk.KandangId] = append(grouped[pfk.KandangId], pfk)
+	}
+
+	for _, list := range grouped {
+		sort.Slice(list, func(i, j int) bool {
+			if list[i].Period == list[j].Period {
+				return startMap[list[i].Id].Before(startMap[list[j].Id])
+			}
+			return list[i].Period < list[j].Period
+		})
+
+		for idx, item := range list {
+			if idx < len(list)-1 {
+				next := list[idx+1]
+				if start, ok := startMap[next.Id]; ok {
+					nextStartMap[item.Id] = start
+				}
+				statusMap[item.Id] = "closing"
+				continue
+			}
+			statusMap[item.Id] = "active"
+		}
+	}
+
+	return statusMap, nextStartMap
+}
+
+func (s closingService) buildSapronakItems(ctx context.Context, pfk entity.ProjectFlockKandang, start, end *time.Time) ([]dto.SapronakItemDTO, float64, float64, error) {
+	incoming, err := s.fetchIncomingSapronak(ctx, pfk.KandangId, start, end)
+	if err != nil {
+		return nil, 0, 0, err
+	}
+	usage, err := s.fetchUsageSapronak(ctx, pfk.Id)
+	if err != nil {
+		return nil, 0, 0, err
+	}
+
+	itemMap := make(map[uint]dto.SapronakItemDTO, len(incoming)+len(usage))
+
+	for _, row := range incoming {
+		avgPrice := row.DefaultPrice
+		if row.Qty > 0 && row.Value > 0 {
+			avgPrice = row.Value / row.Qty
+		}
+
+		itemMap[row.ProductID] = dto.SapronakItemDTO{
+			ProductID:     row.ProductID,
+			ProductName:   row.ProductName,
+			Flag:          row.Flag,
+			IncomingQty:   row.Qty,
+			IncomingValue: row.Value,
+			RemainingQty:  row.Qty,
+			AveragePrice:  avgPrice,
+		}
+	}
+
+	for _, row := range usage {
+		existing := itemMap[row.ProductID]
+		price := existing.AveragePrice
+		if price == 0 {
+			price = row.DefaultPrice
+		}
+
+		usageValue := row.Qty * price
+
+		existing.ProductID = row.ProductID
+		if existing.ProductName == "" {
+			existing.ProductName = row.ProductName
+		}
+		if existing.Flag == "" {
+			existing.Flag = row.Flag
+		}
+		existing.AveragePrice = price
+		existing.UsageQty += row.Qty
+		existing.UsageValue += usageValue
+		if existing.IncomingQty >= existing.UsageQty {
+			existing.RemainingQty = existing.IncomingQty - existing.UsageQty
+		} else {
+			existing.RemainingQty = 0
+		}
+
+		itemMap[row.ProductID] = existing
+	}
+
+	items := make([]dto.SapronakItemDTO, 0, len(itemMap))
+	var totalIncoming, totalUsage float64
+	for _, item := range itemMap {
+		totalIncoming += item.IncomingValue
+		totalUsage += item.UsageValue
+		items = append(items, item)
+	}
+
+	sort.Slice(items, func(i, j int) bool {
+		if items[i].Flag == items[j].Flag {
+			return strings.ToLower(items[i].ProductName) < strings.ToLower(items[j].ProductName)
+		}
+		return items[i].Flag < items[j].Flag
+	})
+
+	return items, totalIncoming, totalUsage, nil
+}
+
+func (s closingService) fetchIncomingSapronak(ctx context.Context, kandangID uint, start, end *time.Time) (map[uint]sapronakIncomingRow, error) {
+	rows := make([]sapronakIncomingRow, 0)
+
+	db := s.Repository.DB().
+		WithContext(ctx).
+		Table("purchase_items AS pi").
+		Select(`
+			pi.product_id AS product_id,
+			p.name AS product_name,
+			f.name AS flag,
+			COALESCE(SUM(pi.total_qty), 0) AS qty,
+			COALESCE(SUM(pi.total_qty * pi.price), 0) AS value,
+			COALESCE(p.product_price, 0) AS default_price
+		`).
+		Joins("JOIN purchases po ON po.id = pi.purchase_id AND po.deleted_at IS NULL").
+		Joins("JOIN products p ON p.id = pi.product_id").
+		Joins("JOIN flags f ON f.flagable_id = p.id AND f.flagable_type = ?", entity.FlagableTypeProduct).
+		Joins("JOIN warehouses w ON w.id = pi.warehouse_id").
+		Where("w.kandang_id = ?", kandangID).
+		Where("f.name IN ?", sapronakFlags).
+		Where("pi.received_date IS NOT NULL")
+
+	if start != nil {
+		db = db.Where("pi.received_date >= ?", *start)
+	}
+	if end != nil {
+		db = db.Where("pi.received_date < ?", *end)
+	}
+
+	if err := db.Group("pi.product_id, p.name, f.name, p.product_price").Scan(&rows).Error; err != nil {
+		return nil, err
+	}
+
+	result := make(map[uint]sapronakIncomingRow, len(rows))
+	for _, row := range rows {
+		result[row.ProductID] = row
+	}
+	return result, nil
+}
+
+func (s closingService) fetchUsageSapronak(ctx context.Context, pfkID uint) (map[uint]sapronakUsageRow, error) {
+	rows := make([]sapronakUsageRow, 0)
+
+	if pfkID == 0 {
+		return map[uint]sapronakUsageRow{}, nil
+	}
+
+	db := s.Repository.DB().
+		WithContext(ctx).
+		Table("recording_stocks AS rs").
+		Select(`
+			pw.product_id AS product_id,
+			p.name AS product_name,
+			f.name AS flag,
+			COALESCE(SUM(rs.usage_qty), 0) AS qty,
+			COALESCE(p.product_price, 0) AS default_price
+		`).
+		Joins("JOIN recordings r ON r.id = rs.recording_id AND r.deleted_at IS NULL").
+		Joins("JOIN product_warehouses pw ON pw.id = rs.product_warehouse_id").
+		Joins("JOIN products p ON p.id = pw.product_id").
+		Joins("JOIN flags f ON f.flagable_id = p.id AND f.flagable_type = ?", entity.FlagableTypeProduct).
+		Where("r.project_flock_kandangs_id = ?", pfkID).
+		Where("f.name IN ?", sapronakFlags)
+
+	if err := db.Group("pw.product_id, p.name, f.name, p.product_price").Scan(&rows).Error; err != nil {
+		return nil, err
+	}
+
+	result := make(map[uint]sapronakUsageRow, len(rows))
+	for _, row := range rows {
+		result[row.ProductID] = row
+	}
+	return result, nil
+}

internal/modules/closings/validations/sapronak.validation.go

@@ -0,0 +1,7 @@
+package validation
+
+type SapronakQuery struct {
+	ProjectFlockID uint   `query:"project_flock_id" validate:"omitempty,gt=0"`
+	KandangID      uint   `query:"kandang_id" validate:"omitempty,gt=0"`
+	Status         string `query:"status" validate:"omitempty,oneof=active closing all"`
+}