255 lines
6.8 KiB
Go
255 lines
6.8 KiB
Go
// internal/repositories/item_repo.go
|
|
package repositories
|
|
|
|
import (
|
|
"errors"
|
|
"lost-and-found/internal/models"
|
|
"time"
|
|
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
type ItemRepository struct {
|
|
db *gorm.DB
|
|
}
|
|
|
|
func NewItemRepository(db *gorm.DB) *ItemRepository {
|
|
return &ItemRepository{db: db}
|
|
}
|
|
|
|
// Create creates a new item
|
|
func (r *ItemRepository) Create(item *models.Item) error {
|
|
return r.db.Create(item).Error
|
|
}
|
|
|
|
// FindByID finds item by ID
|
|
func (r *ItemRepository) FindByID(id uint) (*models.Item, error) {
|
|
var item models.Item
|
|
err := r.db.
|
|
Preload("Category").
|
|
Preload("Reporter").
|
|
Preload("Reporter.Role").
|
|
Preload("CaseClosedBy_User").
|
|
Preload("CaseClosedBy_User.Role").
|
|
Preload("Claims", "deleted_at IS NULL"). // ✅ TAMBAH INI!
|
|
First(&item, id).Error
|
|
|
|
if err != nil {
|
|
if errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return nil, errors.New("item not found")
|
|
}
|
|
return nil, err
|
|
}
|
|
|
|
return &item, nil
|
|
}
|
|
|
|
// ✅ IMPLEMENTASI PROCEDURE 1: Archive Expired Items
|
|
// CallArchiveExpiredProcedure memanggil SP sp_archive_expired_items
|
|
func (r *ItemRepository) CallArchiveExpiredProcedure() (int, error) {
|
|
var archivedCount int
|
|
|
|
// Menggunakan transaksi untuk eksekusi procedure
|
|
err := r.db.Transaction(func(tx *gorm.DB) error {
|
|
// 1. Eksekusi Procedure dengan variabel output session MySQL (@count)
|
|
if err := tx.Exec("CALL sp_archive_expired_items(@count)").Error; err != nil {
|
|
return err
|
|
}
|
|
|
|
// 2. Ambil nilai dari variabel output
|
|
// Kita menggunakan Raw SQL karena GORM tidak support OUT param secara native di semua driver
|
|
type Result struct {
|
|
Count int
|
|
}
|
|
var res Result
|
|
if err := tx.Raw("SELECT @count as count").Scan(&res).Error; err != nil {
|
|
return err
|
|
}
|
|
archivedCount = res.Count
|
|
return nil
|
|
})
|
|
|
|
return archivedCount, err
|
|
}
|
|
|
|
// ✅ IMPLEMENTASI PROCEDURE 2: Dashboard Stats
|
|
// GetDashboardStatsSP memanggil SP sp_get_dashboard_stats
|
|
func (r *ItemRepository) GetDashboardStatsSP() (map[string]int64, error) {
|
|
stats := make(map[string]int64)
|
|
|
|
err := r.db.Transaction(func(tx *gorm.DB) error {
|
|
// 1. Eksekusi Procedure dengan 4 variabel output
|
|
query := "CALL sp_get_dashboard_stats(@total, @unclaimed, @verified, @pending)"
|
|
if err := tx.Exec(query).Error; err != nil {
|
|
return err
|
|
}
|
|
|
|
// 2. Select nilai variabel tersebut
|
|
type Result struct {
|
|
Total int64
|
|
Unclaimed int64
|
|
Verified int64
|
|
Pending int64
|
|
}
|
|
var res Result
|
|
querySelect := "SELECT @total as total, @unclaimed as unclaimed, @verified as verified, @pending as pending"
|
|
|
|
if err := tx.Raw(querySelect).Scan(&res).Error; err != nil {
|
|
return err
|
|
}
|
|
|
|
stats["total_items"] = res.Total
|
|
stats["unclaimed_items"] = res.Unclaimed
|
|
stats["verified_items"] = res.Verified
|
|
stats["pending_claims"] = res.Pending
|
|
|
|
return nil
|
|
})
|
|
|
|
return stats, err
|
|
}
|
|
|
|
// FindAll returns all items with filters
|
|
// internal/repositories/item_repo.go
|
|
|
|
func (r *ItemRepository) FindAll(page, limit int, status, category, search string) ([]models.Item, int64, error) {
|
|
var items []models.Item
|
|
var total int64
|
|
|
|
query := r.db.Model(&models.Item{})
|
|
|
|
if status != "" {
|
|
if status == "!expired" {
|
|
query = query.Where("status NOT IN ?", []string{models.ItemStatusExpired, models.ItemStatusCaseClosed})
|
|
} else {
|
|
query = query.Where("status = ?", status)
|
|
}
|
|
}
|
|
|
|
if category != "" {
|
|
query = query.Joins("JOIN categories ON categories.id = items.category_id").Where("categories.slug = ?", category)
|
|
}
|
|
|
|
if search != "" {
|
|
query = query.Where("name LIKE ? OR description LIKE ?", "%"+search+"%", "%"+search+"%")
|
|
}
|
|
|
|
if err := query.Count(&total).Error; err != nil {
|
|
return nil, 0, err
|
|
}
|
|
|
|
offset := (page - 1) * limit
|
|
|
|
// ✅ FIX: Tambahkan Preload Claims untuk hitung status dinamis
|
|
err := query.
|
|
Preload("Category").
|
|
Preload("Reporter").
|
|
Preload("Reporter.Role").
|
|
Preload("Claims", "deleted_at IS NULL"). // ✅ TAMBAH INI!
|
|
Order("date_found DESC").
|
|
Offset(offset).
|
|
Limit(limit).
|
|
Find(&items).Error
|
|
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
|
|
return items, total, nil
|
|
}
|
|
|
|
// Update updates item data
|
|
func (r *ItemRepository) Update(item *models.Item) error {
|
|
return r.db.Save(item).Error
|
|
}
|
|
|
|
// UpdateStatus updates item status
|
|
func (r *ItemRepository) UpdateStatus(id uint, status string) error {
|
|
return r.db.Model(&models.Item{}).Where("id = ?", id).Update("status", status).Error
|
|
}
|
|
|
|
// Delete soft deletes an item
|
|
func (r *ItemRepository) Delete(id uint) error {
|
|
return r.db.Delete(&models.Item{}, id).Error
|
|
}
|
|
|
|
// FindExpired finds expired items
|
|
func (r *ItemRepository) FindExpired() ([]models.Item, error) {
|
|
var items []models.Item
|
|
now := time.Now()
|
|
err := r.db.Where("expires_at <= ? AND status = ?", now, models.ItemStatusUnclaimed).
|
|
Preload("Category").Find(&items).Error
|
|
return items, err
|
|
}
|
|
|
|
// ArchiveItem moves item to archive
|
|
func (r *ItemRepository) ArchiveItem(item *models.Item, reason string, claimedBy *uint) error {
|
|
return r.db.Transaction(func(tx *gorm.DB) error {
|
|
// Create archive record
|
|
archive := models.CreateFromItem(item, reason, claimedBy)
|
|
if err := tx.Create(archive).Error; err != nil {
|
|
return err
|
|
}
|
|
|
|
// Update item status
|
|
if err := tx.Model(item).Updates(map[string]interface{}{
|
|
"status": models.ItemStatusExpired,
|
|
}).Error; err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
})
|
|
}
|
|
|
|
// CountByStatus counts items by status
|
|
func (r *ItemRepository) CountByStatus(status string) (int64, error) {
|
|
var count int64
|
|
err := r.db.Model(&models.Item{}).Where("status = ?", status).Count(&count).Error
|
|
return count, err
|
|
}
|
|
|
|
// FindByReporter finds items by reporter ID
|
|
func (r *ItemRepository) FindByReporter(reporterID uint, page, limit int) ([]models.Item, int64, error) {
|
|
var items []models.Item
|
|
var total int64
|
|
|
|
query := r.db.Model(&models.Item{}).Where("reporter_id = ?", reporterID)
|
|
|
|
// Count total
|
|
if err := query.Count(&total).Error; err != nil {
|
|
return nil, 0, err
|
|
}
|
|
|
|
// Get paginated results
|
|
offset := (page - 1) * limit
|
|
err := query.Preload("Category").Order("date_found DESC").
|
|
Offset(offset).Limit(limit).Find(&items).Error
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
|
|
return items, total, nil
|
|
}
|
|
|
|
// SearchForMatching searches items for matching with lost items
|
|
func (r *ItemRepository) SearchForMatching(categoryID uint, name, color string) ([]models.Item, error) {
|
|
var items []models.Item
|
|
|
|
query := r.db.Where("status = ? AND category_id = ?", models.ItemStatusUnclaimed, categoryID)
|
|
|
|
if name != "" {
|
|
query = query.Where("name ILIKE ?", "%"+name+"%")
|
|
}
|
|
|
|
err := query.Preload("Category").Order("date_found DESC").Limit(10).Find(&items).Error
|
|
return items, err
|
|
}
|
|
|
|
func (r *ItemRepository) CountAll() (int64, error) {
|
|
var count int64
|
|
err := r.db.Model(&models.Item{}).Count(&count).Error
|
|
return count, err
|
|
}
|
|
|