301 lines
10 KiB
Go
301 lines
10 KiB
Go
package mysql
|
|
|
|
import (
|
|
"context"
|
|
"errors"
|
|
"strings"
|
|
"time"
|
|
|
|
"gorm.io/gorm"
|
|
|
|
flightinspection "wucher/internal/domain/flight_inspection"
|
|
"wucher/internal/domain/helicopter"
|
|
reserveac "wucher/internal/domain/reserve_ac"
|
|
"wucher/internal/shared/pkg/uuidv7"
|
|
shareddto "wucher/internal/transport/http/dto/shared"
|
|
)
|
|
|
|
type ReserveAcRepository struct {
|
|
db *gorm.DB
|
|
}
|
|
|
|
func NewReserveAcRepository(db *gorm.DB) *ReserveAcRepository {
|
|
return &ReserveAcRepository{db: db}
|
|
}
|
|
|
|
func (r *ReserveAcRepository) Create(ctx context.Context, row *reserveac.ReserveAc) error {
|
|
return r.db.WithContext(ctx).Create(row).Error
|
|
}
|
|
|
|
func (r *ReserveAcRepository) Update(ctx context.Context, row *reserveac.ReserveAc) error {
|
|
return r.db.WithContext(ctx).Save(row).Error
|
|
}
|
|
|
|
func (r *ReserveAcRepository) Delete(ctx context.Context, id []byte, deletedBy []byte) error {
|
|
now := time.Now().UTC()
|
|
updates := map[string]any{
|
|
"deleted_at": now,
|
|
"deleted_by": deletedBy,
|
|
"updated_by": deletedBy,
|
|
}
|
|
res := r.db.WithContext(ctx).
|
|
Model(&reserveac.ReserveAc{}).
|
|
Where("id = ? AND deleted_at IS NULL", id).
|
|
Updates(updates)
|
|
if res.Error != nil {
|
|
return res.Error
|
|
}
|
|
if res.RowsAffected == 0 {
|
|
return gorm.ErrRecordNotFound
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *ReserveAcRepository) GetByID(ctx context.Context, id []byte) (*reserveac.ReserveAc, error) {
|
|
var row reserveac.ReserveAc
|
|
err := r.db.WithContext(ctx).
|
|
Preload("Aircraft").
|
|
Preload("Inspection").
|
|
Where("id = ? AND deleted_at IS NULL", id).
|
|
First(&row).Error
|
|
if errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return nil, nil
|
|
}
|
|
return &row, err
|
|
}
|
|
|
|
func (r *ReserveAcRepository) GetActiveByInspectionID(ctx context.Context, inspectionID []byte) (*reserveac.ReserveAc, error) {
|
|
if len(inspectionID) != 16 {
|
|
return nil, nil
|
|
}
|
|
var row reserveac.ReserveAc
|
|
err := r.db.WithContext(ctx).
|
|
Preload("Aircraft").
|
|
Preload("Inspection").
|
|
Where("inspection_id = ? AND deleted_at IS NULL", inspectionID).
|
|
Limit(1).
|
|
Find(&row).Error
|
|
if errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return nil, nil
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if len(row.ID) == 0 {
|
|
return nil, nil
|
|
}
|
|
return &row, nil
|
|
}
|
|
|
|
func (r *ReserveAcRepository) List(ctx context.Context, filter string, sort string, limit, offset int) ([]reserveac.ReserveAc, int64, error) {
|
|
rows := make([]reserveac.ReserveAc, 0)
|
|
var total int64
|
|
|
|
base := r.db.WithContext(ctx).Model(&reserveac.ReserveAc{}).Where("deleted_at IS NULL")
|
|
if strings.TrimSpace(filter) != "" {
|
|
like := "%" + strings.ToLower(strings.TrimSpace(filter)) + "%"
|
|
base = base.Where(
|
|
"LOWER(status) LIKE ? OR LOWER(HEX(id)) LIKE ? OR LOWER(HEX(helicopter_id)) LIKE ? OR LOWER(HEX(inspection_id)) LIKE ?",
|
|
like, like, like, like,
|
|
)
|
|
}
|
|
|
|
query := base
|
|
if sort != "" {
|
|
query = query.Order(sort)
|
|
}
|
|
if err := base.Count(&total).Error; err != nil {
|
|
return nil, 0, err
|
|
}
|
|
if limit > 0 {
|
|
query = query.Limit(limit).Offset(offset)
|
|
}
|
|
if err := query.Find(&rows).Error; err != nil {
|
|
return nil, 0, err
|
|
}
|
|
return rows, total, nil
|
|
}
|
|
|
|
func (r *ReserveAcRepository) AircraftExists(ctx context.Context, helicopterID []byte) (bool, error) {
|
|
if len(helicopterID) != 16 {
|
|
return false, nil
|
|
}
|
|
var total int64
|
|
err := r.db.WithContext(ctx).
|
|
Model(&helicopter.Helicopter{}).
|
|
Where("id = ?", helicopterID).
|
|
Count(&total).Error
|
|
return total > 0, err
|
|
}
|
|
|
|
func (r *ReserveAcRepository) FlightInspectionExists(ctx context.Context, inspectionID []byte) (bool, error) {
|
|
if len(inspectionID) != 16 {
|
|
return false, nil
|
|
}
|
|
var total int64
|
|
err := r.db.WithContext(ctx).
|
|
Model(&flightinspection.FlightInspection{}).
|
|
Where("id = ?", inspectionID).
|
|
Count(&total).Error
|
|
return total > 0, err
|
|
}
|
|
|
|
func (r *ReserveAcRepository) ExistsActiveByInspectionID(ctx context.Context, inspectionID []byte, excludeID []byte) (bool, error) {
|
|
if len(inspectionID) != 16 {
|
|
return false, nil
|
|
}
|
|
var total int64
|
|
q := r.db.WithContext(ctx).
|
|
Model(&reserveac.ReserveAc{}).
|
|
Where("inspection_id = ? AND deleted_at IS NULL", inspectionID)
|
|
if len(excludeID) == 16 {
|
|
q = q.Where("id <> ?", excludeID)
|
|
}
|
|
if err := q.Count(&total).Error; err != nil {
|
|
return false, err
|
|
}
|
|
return total > 0, nil
|
|
}
|
|
|
|
func (r *ReserveAcRepository) GetLastDailyInspectionByHelicopterID(ctx context.Context, helicopterID []byte) (*shareddto.LastDailyInspection, error) {
|
|
if len(helicopterID) != 16 {
|
|
return nil, nil
|
|
}
|
|
|
|
type row struct {
|
|
ID []byte `gorm:"column:id"`
|
|
Name string `gorm:"column:name"`
|
|
ShortName string `gorm:"column:short_name"`
|
|
At time.Time `gorm:"column:inspected_at"`
|
|
}
|
|
var out row
|
|
err := r.db.WithContext(ctx).
|
|
Table("reserve_acs ra").
|
|
Select(`
|
|
fi.id AS id,
|
|
TRIM(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))) AS name,
|
|
COALESCE(NULLIF(pp.short_name, ''), NULLIF(tp.short_name, ''), '') AS short_name,
|
|
COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at) AS inspected_at`).
|
|
Joins("JOIN flight_inspections fi ON fi.id = ra.inspection_id").
|
|
Joins("LEFT JOIN before_flight_inspections bfi ON bfi.flight_inspection_id = fi.id").
|
|
Joins("LEFT JOIN flight_prep_checks fpc ON fpc.flight_inspection_id = fi.id").
|
|
Joins("LEFT JOIN after_flight_inspections afi ON afi.flight_inspection_id = fi.id").
|
|
Joins(`JOIN users u ON u.id = CASE
|
|
WHEN afi.updated_at IS NOT NULL THEN afi.updated_by
|
|
WHEN fpc.updated_at IS NOT NULL THEN fpc.updated_by
|
|
WHEN bfi.updated_at IS NOT NULL THEN bfi.updated_by
|
|
ELSE NULL
|
|
END`).
|
|
Joins("LEFT JOIN pilot_profiles pp ON pp.user_id = u.id").
|
|
Joins("LEFT JOIN technician_profiles tp ON tp.user_id = u.id").
|
|
Where("ra.helicopter_id = ? AND ra.deleted_at IS NULL", helicopterID).
|
|
Where("COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at) IS NOT NULL").
|
|
Order("fi.inspection_date DESC").
|
|
Order("COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at) DESC").
|
|
Limit(1).
|
|
Take(&out).Error
|
|
if errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return nil, nil
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return &shareddto.LastDailyInspection{
|
|
ID: out.ID,
|
|
Name: strings.TrimSpace(out.Name),
|
|
ShortName: strings.TrimSpace(out.ShortName),
|
|
At: out.At.UTC(),
|
|
}, nil
|
|
}
|
|
|
|
func (r *ReserveAcRepository) GetLastDailyInspectionSummaryByHelicopterID(ctx context.Context, helicopterID []byte) (*shareddto.LastDailyInspectionSummary, error) {
|
|
if len(helicopterID) != 16 {
|
|
return nil, nil
|
|
}
|
|
|
|
type row struct {
|
|
InspectionID []byte `gorm:"column:inspection_id"`
|
|
InspectorID []byte `gorm:"column:inspector_id"`
|
|
InspectorName string `gorm:"column:inspector_name"`
|
|
InspectorLicenseNo string `gorm:"column:inspector_license_no"`
|
|
BaseID []byte `gorm:"column:base_id"`
|
|
BaseName string `gorm:"column:base_name"`
|
|
BaseAbbreviation string `gorm:"column:base_abbreviation"`
|
|
BaseType string `gorm:"column:base_type"`
|
|
InspectedAt time.Time `gorm:"column:inspected_at"`
|
|
CompletedCount int `gorm:"column:completed_count"`
|
|
TotalCount int `gorm:"column:total_count"`
|
|
}
|
|
var out row
|
|
err := r.db.WithContext(ctx).
|
|
Table("reserve_acs ra").
|
|
Select(`
|
|
ra.inspection_id AS inspection_id,
|
|
COALESCE(u.id, ra.updated_by) AS inspector_id,
|
|
COALESCE(
|
|
NULLIF(TRIM(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))), ''),
|
|
NULLIF(TRIM(u.username), ''),
|
|
NULLIF(TRIM(u.email), ''),
|
|
''
|
|
) AS inspector_name,
|
|
COALESCE(NULLIF(pp.license_no, ''), NULLIF(tp.license_no, ''), '') AS inspector_license_no,
|
|
ra.base_id AS base_id,
|
|
COALESCE(TRIM(b.base), '') AS base_name,
|
|
COALESCE(TRIM(b.base_abbreviation), '') AS base_abbreviation,
|
|
COALESCE(NULLIF(LOWER(bc.key), ''), 'base') AS base_type,
|
|
COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at, ra.updated_at) AS inspected_at,
|
|
(
|
|
SELECT COUNT(*)
|
|
FROM flight_inspection_file_checklists fic
|
|
WHERE fic.flight_inspection_id = fi.id
|
|
) AS total_count,
|
|
(
|
|
SELECT COUNT(*)
|
|
FROM flight_inspection_file_checklists fic
|
|
WHERE fic.flight_inspection_id = fi.id AND fic.is_done = 1
|
|
) AS completed_count
|
|
`).
|
|
Joins("JOIN flight_inspections fi ON fi.id = ra.inspection_id").
|
|
Joins("LEFT JOIN before_flight_inspections bfi ON bfi.flight_inspection_id = fi.id").
|
|
Joins("LEFT JOIN flight_prep_checks fpc ON fpc.flight_inspection_id = fi.id").
|
|
Joins("LEFT JOIN after_flight_inspections afi ON afi.flight_inspection_id = fi.id").
|
|
Joins(`LEFT JOIN users u ON u.id = CASE
|
|
WHEN afi.updated_at IS NOT NULL THEN afi.updated_by
|
|
WHEN fpc.updated_at IS NOT NULL THEN fpc.updated_by
|
|
WHEN bfi.updated_at IS NOT NULL THEN bfi.updated_by
|
|
ELSE ra.updated_by
|
|
END`).
|
|
Joins("LEFT JOIN pilot_profiles pp ON pp.user_id = u.id").
|
|
Joins("LEFT JOIN technician_profiles tp ON tp.user_id = u.id").
|
|
Joins("LEFT JOIN bases b ON b.id = ra.base_id AND b.deleted_at IS NULL").
|
|
Joins("LEFT JOIN base_categories bc ON bc.id = b.base_category_id").
|
|
Where("ra.helicopter_id = ? AND ra.deleted_at IS NULL", helicopterID).
|
|
Where("COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at, ra.updated_at) IS NOT NULL").
|
|
Order("fi.inspection_date DESC, COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at, ra.updated_at) DESC").
|
|
Limit(1).
|
|
Take(&out).Error
|
|
if errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return nil, nil
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
inspectionID, _ := uuidv7.BytesToString(out.InspectionID)
|
|
inspectorID, _ := uuidv7.BytesToString(out.InspectorID)
|
|
baseID, _ := uuidv7.BytesToString(out.BaseID)
|
|
return &shareddto.LastDailyInspectionSummary{
|
|
InspectionID: inspectionID,
|
|
Status: "inspected_cleared",
|
|
InspectorID: inspectorID,
|
|
InspectorName: strings.TrimSpace(out.InspectorName),
|
|
InspectorLicenseNo: strings.TrimSpace(out.InspectorLicenseNo),
|
|
BaseID: baseID,
|
|
BaseName: strings.TrimSpace(out.BaseName),
|
|
BaseAbbreviation: strings.TrimSpace(out.BaseAbbreviation),
|
|
BaseType: strings.TrimSpace(out.BaseType),
|
|
InspectedAt: out.InspectedAt.UTC(),
|
|
InspectionFilesChecklistCompleted: out.CompletedCount,
|
|
InspectionFilesChecklistTotal: out.TotalCount,
|
|
}, nil
|
|
}
|