5f8aec456b
- New unified append-only quote log table parts_log replaces three separate log tables (stock_log, partnumber_log_competitors, lot_log) - Migrations 042-049: extend supplier, create parts_log/import_formats/ ignore_rules, rework qt_lot_metadata composite PK, add lead_time_weeks to pricelist_items, backfill data, migrate ignore rules - New services: PartsLogBackfillService, ImportFormatService, UnifiedImportService; new world pricelist type (all supplier types) - qt_lot_metadata PK changed to (lot_name, pricelist_type); all queries now filter WHERE pricelist_type='estimate' - Fix pre-existing bug: qt_component_usage_stats column names quotes_last30d/quotes_last7d (no underscore) — added explicit gorm tags - Bible: full table inventory, baseline schema snapshot, updated pricelist/ data-rules/api/history/architecture docs Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
117 lines
3.3 KiB
Go
117 lines
3.3 KiB
Go
package repository
|
|
|
|
import (
|
|
"time"
|
|
|
|
"git.mchus.pro/mchus/priceforge/internal/models"
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
type StatsRepository struct {
|
|
db *gorm.DB
|
|
}
|
|
|
|
func NewStatsRepository(db *gorm.DB) *StatsRepository {
|
|
return &StatsRepository{db: db}
|
|
}
|
|
|
|
func (r *StatsRepository) GetByLotName(lotName string) (*models.ComponentUsageStats, error) {
|
|
var stats models.ComponentUsageStats
|
|
err := r.db.Where("lot_name = ?", lotName).First(&stats).Error
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return &stats, nil
|
|
}
|
|
|
|
func (r *StatsRepository) Upsert(stats *models.ComponentUsageStats) error {
|
|
return r.db.Save(stats).Error
|
|
}
|
|
|
|
func (r *StatsRepository) IncrementUsage(lotName string, quantity int, revenue float64) error {
|
|
now := time.Now()
|
|
|
|
result := r.db.Model(&models.ComponentUsageStats{}).
|
|
Where("lot_name = ?", lotName).
|
|
Updates(map[string]interface{}{
|
|
"quotes_total": gorm.Expr("quotes_total + 1"),
|
|
"quotes_last30d": gorm.Expr("quotes_last30d + 1"),
|
|
"quotes_last7d": gorm.Expr("quotes_last7d + 1"),
|
|
"total_quantity": gorm.Expr("total_quantity + ?", quantity),
|
|
"total_revenue": gorm.Expr("total_revenue + ?", revenue),
|
|
"last_used_at": now,
|
|
})
|
|
|
|
if result.RowsAffected == 0 {
|
|
stats := &models.ComponentUsageStats{
|
|
LotName: lotName,
|
|
QuotesTotal: 1,
|
|
QuotesLast30d: 1,
|
|
QuotesLast7d: 1,
|
|
TotalQuantity: quantity,
|
|
TotalRevenue: revenue,
|
|
LastUsedAt: &now,
|
|
}
|
|
return r.db.Create(stats).Error
|
|
}
|
|
|
|
return result.Error
|
|
}
|
|
|
|
func (r *StatsRepository) GetTopComponents(limit int) ([]models.ComponentUsageStats, error) {
|
|
var stats []models.ComponentUsageStats
|
|
err := r.db.
|
|
Order("quotes_last30d DESC").
|
|
Limit(limit).
|
|
Find(&stats).Error
|
|
return stats, err
|
|
}
|
|
|
|
func (r *StatsRepository) GetTrendingComponents(limit int) ([]models.ComponentUsageStats, error) {
|
|
var stats []models.ComponentUsageStats
|
|
err := r.db.
|
|
Where("trend_direction = ? AND trend_percent > ?", models.TrendUp, 20).
|
|
Order("trend_percent DESC").
|
|
Limit(limit).
|
|
Find(&stats).Error
|
|
return stats, err
|
|
}
|
|
|
|
// ResetWeeklyCounters resets quotes_last_7d (run weekly via cron)
|
|
func (r *StatsRepository) ResetWeeklyCounters() error {
|
|
return r.db.Model(&models.ComponentUsageStats{}).
|
|
Where("1 = 1").
|
|
Update("quotes_last7d", 0).Error
|
|
}
|
|
|
|
// ResetMonthlyCounters resets quotes_last30d (run monthly via cron)
|
|
func (r *StatsRepository) ResetMonthlyCounters() error {
|
|
return r.db.Model(&models.ComponentUsageStats{}).
|
|
Where("1 = 1").
|
|
Update("quotes_last30d", 0).Error
|
|
}
|
|
|
|
// UpdatePopularityScores recalculates popularity_score in qt_lot_metadata
|
|
// based on supplier quotes from lot_log table
|
|
func (r *StatsRepository) UpdatePopularityScores() error {
|
|
// Formula: popularity_score = quotes_last_30d * 3 + quotes_last_90d * 1 + quotes_total * 0.1
|
|
// This gives more weight to recent supplier activity
|
|
return r.db.Exec(`
|
|
UPDATE qt_lot_metadata m
|
|
LEFT JOIN (
|
|
SELECT
|
|
lot,
|
|
COUNT(*) as quotes_total,
|
|
SUM(CASE WHEN date >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as quotes_last_30d,
|
|
SUM(CASE WHEN date >= DATE_SUB(NOW(), INTERVAL 90 DAY) THEN 1 ELSE 0 END) as quotes_last_90d
|
|
FROM lot_log
|
|
GROUP BY lot
|
|
) s ON m.lot_name = s.lot
|
|
SET m.popularity_score = COALESCE(
|
|
s.quotes_last_30d * 3 + s.quotes_last_90d * 1 + s.quotes_total * 0.1,
|
|
0
|
|
)
|
|
WHERE m.pricelist_type = 'estimate'
|
|
`).Error
|
|
}
|