529 lines
15 KiB
Go
529 lines
15 KiB
Go
package mysql
|
|
|
|
import (
|
|
"context"
|
|
"errors"
|
|
"strings"
|
|
"time"
|
|
|
|
"gorm.io/gorm"
|
|
|
|
flightdata "wucher/internal/domain/flight_data"
|
|
"wucher/internal/domain/mission"
|
|
"wucher/internal/shared/pkg/txctx"
|
|
)
|
|
|
|
type MissionRepository struct {
|
|
db *gorm.DB
|
|
}
|
|
|
|
func NewMissionRepository(db *gorm.DB) *MissionRepository {
|
|
return &MissionRepository{db: db}
|
|
}
|
|
|
|
func (r *MissionRepository) Create(ctx context.Context, row *mission.Mission) error {
|
|
normalizeMissionTimeForDB(row)
|
|
if err := txctx.DB(ctx, r.db).Create(row).Error; err != nil {
|
|
if errors.Is(err, gorm.ErrDuplicatedKey) {
|
|
return mission.ErrDuplicateFlight
|
|
}
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *MissionRepository) WithTransaction(ctx context.Context, fn func(context.Context) error) error {
|
|
return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
|
|
return fn(txctx.With(ctx, tx))
|
|
})
|
|
}
|
|
|
|
// MaxCodeSeqByPrefix returns the highest numeric suffix among mission codes that
|
|
// start with prefix (e.g. "HEMS-26-"), or 0 when none exist. Used to compute the
|
|
// next per-type, per-year sequence number.
|
|
func (r *MissionRepository) MaxCodeSeqByPrefix(ctx context.Context, prefix string) (int, error) {
|
|
var maxSeq *int
|
|
err := txctx.DB(ctx, r.db).
|
|
Model(&mission.Mission{}).
|
|
Where("code LIKE ? AND deleted_at IS NULL", prefix+"%").
|
|
Select("MAX(CAST(SUBSTRING_INDEX(code, '-', -1) AS UNSIGNED))").
|
|
Scan(&maxSeq).Error
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
if maxSeq == nil {
|
|
return 0, nil
|
|
}
|
|
return *maxSeq, nil
|
|
}
|
|
|
|
func (r *MissionRepository) AttachFile(ctx context.Context, missionID []byte, fileAttachmentID []byte) error {
|
|
return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
|
|
var missionRow mission.Mission
|
|
if err := tx.Where("id = ? AND deleted_at IS NULL", missionID).
|
|
Take(&missionRow).Error; err != nil {
|
|
return err
|
|
}
|
|
var existing mission.MissionFile
|
|
err := tx.Where("mission_id = ? AND file_attachment_id = ?", missionID, fileAttachmentID).
|
|
Take(&existing).Error
|
|
if err == nil {
|
|
return nil
|
|
}
|
|
if !errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return err
|
|
}
|
|
return tx.Create(&mission.MissionFile{
|
|
MissionID: append([]byte(nil), missionID...),
|
|
FileAttachmentID: append([]byte(nil), fileAttachmentID...),
|
|
}).Error
|
|
})
|
|
}
|
|
|
|
func (r *MissionRepository) DetachFile(ctx context.Context, missionID []byte, fileAttachmentID []byte) error {
|
|
return r.db.WithContext(ctx).
|
|
Where("mission_id = ? AND file_attachment_id = ?", missionID, fileAttachmentID).
|
|
Delete(&mission.MissionFile{}).Error
|
|
}
|
|
|
|
func (r *MissionRepository) CreateCategory(ctx context.Context, row *mission.MissionCategory) error {
|
|
return r.db.WithContext(ctx).Create(row).Error
|
|
}
|
|
|
|
func (r *MissionRepository) UpdateFlightDataIDByID(ctx context.Context, missionID, flightDataID, updatedBy []byte) error {
|
|
patch := map[string]any{
|
|
"flight_data_id": flightDataID,
|
|
}
|
|
if len(updatedBy) == 16 {
|
|
patch["updated_by"] = updatedBy
|
|
}
|
|
res := txctx.DB(ctx, r.db).
|
|
Model(&mission.Mission{}).
|
|
Where("id = ? AND deleted_at IS NULL", missionID).
|
|
Updates(patch)
|
|
if res.Error != nil {
|
|
return res.Error
|
|
}
|
|
if res.RowsAffected == 0 {
|
|
return gorm.ErrRecordNotFound
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *MissionRepository) UpdateByID(ctx context.Context, missionID []byte, missionType string, missionCategoryID []byte, subtypeID []byte, note string, updatedBy []byte) error {
|
|
patch := map[string]any{
|
|
"type": missionType,
|
|
"mission_category_id": missionCategoryID,
|
|
"subtype_id": nil,
|
|
"note": strings.TrimSpace(note),
|
|
}
|
|
if len(subtypeID) == 16 {
|
|
patch["subtype_id"] = subtypeID
|
|
}
|
|
if len(updatedBy) == 16 {
|
|
patch["updated_by"] = updatedBy
|
|
}
|
|
|
|
res := r.db.WithContext(ctx).
|
|
Model(&mission.Mission{}).
|
|
Where("id = ? AND deleted_at IS NULL", missionID).
|
|
Updates(patch)
|
|
if res.Error != nil {
|
|
return res.Error
|
|
}
|
|
if res.RowsAffected == 0 {
|
|
return gorm.ErrRecordNotFound
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *MissionRepository) DeleteByID(ctx context.Context, missionID []byte, deletedBy []byte) error {
|
|
now := time.Now().UTC()
|
|
return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
|
|
patch := map[string]any{
|
|
"deleted_at": now,
|
|
"deleted_by": deletedBy,
|
|
}
|
|
if len(deletedBy) == 16 {
|
|
patch["updated_by"] = deletedBy
|
|
}
|
|
|
|
res := tx.Model(&mission.Mission{}).
|
|
Where("id = ? AND deleted_at IS NULL", missionID).
|
|
Updates(patch)
|
|
if res.Error != nil {
|
|
return res.Error
|
|
}
|
|
if res.RowsAffected == 0 {
|
|
return gorm.ErrRecordNotFound
|
|
}
|
|
|
|
fdPatch := map[string]any{
|
|
"deleted_at": now,
|
|
"deleted_by": deletedBy,
|
|
}
|
|
if len(deletedBy) == 16 {
|
|
fdPatch["updated_by"] = deletedBy
|
|
}
|
|
|
|
if err := tx.Model(&flightdata.FlightData{}).
|
|
Where("mission_id = ? AND deleted_at IS NULL", missionID).
|
|
Updates(fdPatch).Error; err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
})
|
|
}
|
|
|
|
func (r *MissionRepository) DeleteByFlightID(ctx context.Context, flightID []byte, deletedBy []byte) error {
|
|
now := time.Now().UTC()
|
|
patch := map[string]any{
|
|
"deleted_at": now,
|
|
"deleted_by": deletedBy,
|
|
}
|
|
if len(deletedBy) == 16 {
|
|
patch["updated_by"] = deletedBy
|
|
}
|
|
|
|
res := r.db.WithContext(ctx).
|
|
Model(&mission.Mission{}).
|
|
Where("flight_id = ? AND deleted_at IS NULL", flightID).
|
|
Updates(patch)
|
|
if res.Error != nil {
|
|
return res.Error
|
|
}
|
|
if res.RowsAffected == 0 {
|
|
return gorm.ErrRecordNotFound
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *MissionRepository) ListByFlightID(ctx context.Context, flightID []byte) ([]mission.Mission, error) {
|
|
rows := make([]mission.Mission, 0)
|
|
err := r.db.WithContext(ctx).
|
|
Select(r.statusSelectSQL()).
|
|
Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
|
|
Preload("MissionSubCategory").
|
|
Preload("Flight").
|
|
Preload("Files").
|
|
Preload("Files.FileAttachment").
|
|
Preload("Files.FileAttachment.File").
|
|
Where("missions.flight_id = ? AND missions.deleted_at IS NULL", flightID).
|
|
Order("missions.created_at ASC, missions.id ASC").
|
|
Find(&rows).Error
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return rows, nil
|
|
}
|
|
|
|
func (r *MissionRepository) ListByFlightIDs(ctx context.Context, flightIDs [][]byte) ([]mission.Mission, error) {
|
|
if len(flightIDs) == 0 {
|
|
return []mission.Mission{}, nil
|
|
}
|
|
rows := make([]mission.Mission, 0)
|
|
err := r.db.WithContext(ctx).
|
|
Select(r.statusSelectSQL()).
|
|
Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
|
|
Preload("MissionSubCategory").
|
|
Preload("Flight").
|
|
Preload("Files").
|
|
Preload("Files.FileAttachment").
|
|
Preload("Files.FileAttachment.File").
|
|
Where("missions.flight_id IN ? AND missions.deleted_at IS NULL", flightIDs).
|
|
Order("missions.flight_id ASC, missions.created_at ASC, missions.id ASC").
|
|
Find(&rows).Error
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return rows, nil
|
|
}
|
|
|
|
func (r *MissionRepository) GetByID(ctx context.Context, missionID []byte) (*mission.Mission, error) {
|
|
var row mission.Mission
|
|
err := r.db.WithContext(ctx).
|
|
Select(r.statusSelectSQL()).
|
|
Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
|
|
Preload("MissionSubCategory").
|
|
Preload("Flight").
|
|
Preload("Files").
|
|
Preload("Files.FileAttachment").
|
|
Preload("Files.FileAttachment.File").
|
|
Where("missions.id = ? AND missions.deleted_at IS NULL", missionID).
|
|
First(&row).Error
|
|
if errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return nil, nil
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return &row, nil
|
|
}
|
|
|
|
func (r *MissionRepository) GetByFlightID(ctx context.Context, flightID []byte) (*mission.Mission, error) {
|
|
var row mission.Mission
|
|
err := r.db.WithContext(ctx).
|
|
Select(r.statusSelectSQL()).
|
|
Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
|
|
Preload("MissionSubCategory").
|
|
Preload("Flight").
|
|
Preload("Files").
|
|
Preload("Files.FileAttachment").
|
|
Preload("Files.FileAttachment.File").
|
|
Where("missions.flight_id = ? AND missions.deleted_at IS NULL", flightID).
|
|
First(&row).Error
|
|
if errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return nil, nil
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return &row, nil
|
|
}
|
|
|
|
func (r *MissionRepository) List(ctx context.Context, filter, sort string, flightID []byte, flightDataStatus string, limit, offset int) ([]mission.Mission, int64, error) {
|
|
rows, total, _, err := r.listWithFilter(ctx, mission.ListFilter{
|
|
Search: filter,
|
|
Sort: sort,
|
|
FlightID: flightID,
|
|
FlightDataStatus: flightDataStatus,
|
|
}, limit, offset, false)
|
|
return rows, total, err
|
|
}
|
|
|
|
func (r *MissionRepository) ListDatatable(ctx context.Context, filter mission.ListFilter, limit, offset int) ([]mission.Mission, int64, int64, error) {
|
|
return r.listWithFilter(ctx, filter, limit, offset, true)
|
|
}
|
|
|
|
func (r *MissionRepository) statusSelectSQL() string {
|
|
return `missions.*,
|
|
COALESCE((
|
|
SELECT flight_data.id
|
|
FROM flight_data
|
|
WHERE flight_data.mission_id = missions.id
|
|
AND flight_data.deleted_at IS NULL
|
|
ORDER BY flight_data.created_at DESC, flight_data.id DESC
|
|
LIMIT 1
|
|
), missions.flight_data_id) AS flight_data_id,
|
|
` + r.flightDataStatusExprSQL() + ` AS flight_data_status`
|
|
}
|
|
|
|
func (r *MissionRepository) flightDataStatusExprSQL() string {
|
|
completeCondition := `(
|
|
flight_data.take_off IS NOT NULL
|
|
AND flight_data.landing IS NOT NULL
|
|
AND flight_data.duration > 0
|
|
AND flight_data.red > 0
|
|
AND flight_data.max_n1 > 0
|
|
AND flight_data.max_n2 > 0
|
|
AND flight_data.pax_count > 0
|
|
AND flight_data.landing_count > 0
|
|
AND flight_data.rotor_brake_cycle > 0
|
|
AND COALESCE(flight_data.ticket_no, '') <> ''
|
|
AND COALESCE(flight_data.engine, '') <> ''
|
|
AND COALESCE(flight_data.delivery_note_number, '') <> ''
|
|
AND COALESCE(flight_data.customer_name, '') <> ''
|
|
AND flight_data.flight_plan_distance > 0
|
|
AND flight_data.flight_plan_time > 0
|
|
AND flight_data.flight_plan_true_course > 0
|
|
AND flight_data.fuel_before_flight > 0
|
|
AND flight_data.fuel_upload > 0
|
|
AND flight_data.fuel_after_flight > 0
|
|
AND flight_data.fuel_planning > 0
|
|
AND COALESCE(flight_data.other_information, '') <> ''
|
|
)`
|
|
return `COALESCE((
|
|
SELECT CASE
|
|
WHEN COUNT(flight_data.id) = 0 THEN 'draft'
|
|
WHEN SUM(CASE WHEN ` + completeCondition + ` THEN 1 ELSE 0 END) = 0 THEN 'draft'
|
|
WHEN SUM(CASE WHEN ` + completeCondition + ` THEN 1 ELSE 0 END) = COUNT(flight_data.id) THEN 'completed'
|
|
ELSE 'in_progress'
|
|
END
|
|
FROM flight_data
|
|
WHERE flight_data.mission_id = missions.id
|
|
AND flight_data.deleted_at IS NULL
|
|
), 'draft')`
|
|
}
|
|
|
|
func (r *MissionRepository) listWithFilter(ctx context.Context, filter mission.ListFilter, limit, offset int, includeUnfilteredTotal bool) ([]mission.Mission, int64, int64, error) {
|
|
rows := make([]mission.Mission, 0)
|
|
var total int64
|
|
var filteredTotal int64
|
|
|
|
base := r.db.WithContext(ctx).
|
|
Model(&mission.Mission{}).
|
|
Joins("LEFT JOIN flights ON flights.id = missions.flight_id").
|
|
Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
|
|
Where("missions.deleted_at IS NULL")
|
|
|
|
if includeUnfilteredTotal {
|
|
if err := base.Session(&gorm.Session{}).Count(&total).Error; err != nil {
|
|
return nil, 0, 0, err
|
|
}
|
|
} else {
|
|
total = 0
|
|
}
|
|
|
|
base = r.applyMissionListFilter(base, filter)
|
|
|
|
countQuery := base.Session(&gorm.Session{})
|
|
if err := countQuery.Count(&filteredTotal).Error; err != nil {
|
|
return nil, 0, 0, err
|
|
}
|
|
|
|
query := base.Session(&gorm.Session{}).
|
|
Select(r.statusSelectSQL()).
|
|
Preload("MissionSubCategory").
|
|
Preload("Flight").
|
|
Preload("Files").
|
|
Preload("Files.FileAttachment").
|
|
Preload("Files.FileAttachment.File")
|
|
if filter.Sort != "" {
|
|
query = query.Order(filter.Sort)
|
|
}
|
|
if limit > 0 {
|
|
query = query.Limit(limit).Offset(offset)
|
|
}
|
|
|
|
if err := query.Find(&rows).Error; err != nil {
|
|
return nil, 0, 0, err
|
|
}
|
|
|
|
if !includeUnfilteredTotal {
|
|
total = filteredTotal
|
|
}
|
|
return rows, total, filteredTotal, nil
|
|
}
|
|
|
|
func (r *MissionRepository) applyMissionListFilter(query *gorm.DB, filter mission.ListFilter) *gorm.DB {
|
|
if filter.Search != "" {
|
|
like := "%" + filter.Search + "%"
|
|
query = query.Where(
|
|
"missions.type LIKE ? OR flights.mission_code LIKE ? OR DATE_FORMAT(flights.date, '%Y-%m-%d') LIKE ?",
|
|
like, like, like,
|
|
)
|
|
}
|
|
if len(filter.FlightID) == 16 {
|
|
query = query.Where("missions.flight_id = ?", filter.FlightID)
|
|
}
|
|
if filter.FlightDataStatus != "" {
|
|
query = query.Where(r.flightDataStatusExprSQL()+" = ?", filter.FlightDataStatus)
|
|
}
|
|
if filter.MissionType != "" {
|
|
query = query.Where("missions.type = ?", strings.ToUpper(strings.TrimSpace(filter.MissionType)))
|
|
}
|
|
if filter.StartDate != nil {
|
|
query = query.Where("flights.date >= ?", filter.StartDate.UTC().Format("2006-01-02"))
|
|
}
|
|
if filter.EndDate != nil {
|
|
query = query.Where("flights.date <= ?", filter.EndDate.UTC().Format("2006-01-02"))
|
|
}
|
|
if len(filter.PilotID) == 16 {
|
|
query = query.Where(`
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM duty_rosters dr
|
|
JOIN duty_roster_crews c ON c.roster_id = dr.id
|
|
WHERE dr.flight_id = missions.flight_id
|
|
AND dr.deleted_at IS NULL
|
|
AND c.deleted_at IS NULL
|
|
AND c.role_code = 'pilot'
|
|
AND c.user_id = ?
|
|
)`, filter.PilotID)
|
|
}
|
|
if len(filter.HelicopterID) == 16 {
|
|
query = query.Where(`
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM takeover_acs ta
|
|
JOIN reserve_acs ra ON ra.id = ta.reserve_ac_id
|
|
WHERE ta.id = flights.takeover_ac_id
|
|
AND ta.deleted_at IS NULL
|
|
AND ra.deleted_at IS NULL
|
|
AND ra.helicopter_id = ?
|
|
)`, filter.HelicopterID)
|
|
}
|
|
return query
|
|
}
|
|
|
|
func normalizeMissionTimeForDB(row *mission.Mission) {
|
|
if row == nil {
|
|
return
|
|
}
|
|
row.StartTime = normalizeMissionDBClock(row.StartTime)
|
|
row.EndTime = normalizeMissionDBClock(row.EndTime)
|
|
}
|
|
|
|
func normalizeMissionDBClock(raw string) string {
|
|
trimmed := strings.TrimSpace(raw)
|
|
if trimmed == "" {
|
|
return ""
|
|
}
|
|
if len(trimmed) == 5 {
|
|
return trimmed + ":00"
|
|
}
|
|
if len(trimmed) == 8 {
|
|
return trimmed
|
|
}
|
|
if parsed, err := time.Parse("15:04", trimmed); err == nil {
|
|
return parsed.Format("15:04:05")
|
|
}
|
|
if parsed, err := time.Parse("15:04:05", trimmed); err == nil {
|
|
return parsed.Format("15:04:05")
|
|
}
|
|
return trimmed
|
|
}
|
|
|
|
func (r *MissionRepository) ListCategoriesWithSubCategories(ctx context.Context) ([]mission.MissionCategory, error) {
|
|
var rows []mission.MissionCategory
|
|
if err := r.db.WithContext(ctx).
|
|
Preload("SubCategory", func(db *gorm.DB) *gorm.DB {
|
|
return db.Order("sub_type ASC")
|
|
}).
|
|
Order("code_type ASC").
|
|
Find(&rows).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
for i := range rows {
|
|
rows[i].CodeType = strings.TrimSpace(rows[i].CodeType)
|
|
rows[i].TypeName = strings.TrimSpace(rows[i].TypeName)
|
|
for j := range rows[i].SubCategory {
|
|
rows[i].SubCategory[j].SubCodeType = strings.TrimSpace(rows[i].SubCategory[j].SubCodeType)
|
|
rows[i].SubCategory[j].SubTypeName = strings.TrimSpace(rows[i].SubCategory[j].SubTypeName)
|
|
rows[i].SubCategory[j].TaskName = strings.TrimSpace(rows[i].SubCategory[j].TaskName)
|
|
}
|
|
}
|
|
|
|
return rows, nil
|
|
}
|
|
|
|
func (r *MissionRepository) FindCategoryByCode(ctx context.Context, code string) (*mission.MissionCategory, error) {
|
|
var row mission.MissionCategory
|
|
err := r.db.WithContext(ctx).
|
|
Where("code_type = ?", code).
|
|
Take(&row).Error
|
|
if errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return nil, nil
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return &row, nil
|
|
}
|
|
|
|
func (r *MissionRepository) SubCategoryBelongsToCategory(ctx context.Context, subCategoryID, categoryID []byte) (bool, error) {
|
|
if len(subCategoryID) != 16 || len(categoryID) != 16 {
|
|
return false, nil
|
|
}
|
|
var cnt int64
|
|
if err := r.db.WithContext(ctx).
|
|
Table("mission_subcategories").
|
|
Where("id = ? AND mission_category_id = ?", subCategoryID, categoryID).
|
|
Count(&cnt).Error; err != nil {
|
|
return false, err
|
|
}
|
|
return cnt > 0, nil
|
|
}
|