From 091963a50c3bb2926f559f01c49e8f5bd03d2bfd Mon Sep 17 00:00:00 2001 From: leshe4ka46 Date: Sun, 19 Oct 2025 13:08:03 +0300 Subject: xlsx --- pkg/adapters/json/json.go | 4 +- pkg/adapters/json/model.go | 8 +-- pkg/adapters/xlsx/model.go | 138 ++++++++++++++++++++++++++++++++++++++++++ pkg/adapters/xlsx/registry.go | 69 +++++++++++++++++++++ pkg/adapters/xlsx/xlsx.go | 90 +++++++++++++++++++++++++++ 5 files changed, 303 insertions(+), 6 deletions(-) create mode 100644 pkg/adapters/xlsx/model.go create mode 100644 pkg/adapters/xlsx/registry.go create mode 100644 pkg/adapters/xlsx/xlsx.go (limited to 'pkg/adapters') diff --git a/pkg/adapters/json/json.go b/pkg/adapters/json/json.go index c0ea4e4..47a563e 100644 --- a/pkg/adapters/json/json.go +++ b/pkg/adapters/json/json.go @@ -100,7 +100,7 @@ type JsonCard struct { func (r *JsonRoot) DumpToDb(ctx context.Context, s *store.Store) { var err error for _, user := range r.ForumProfiles { - dbUser := user.ToUser() + dbUser, _ := user.ToUser() dbUser, err = s.CreateOrGetUser(ctx, dbUser) if err != nil { panic(err) @@ -120,7 +120,7 @@ func (r *JsonRoot) DumpToDb(ctx context.Context, s *store.Store) { } for _, flight := range user.RegisteredFlights { - dbFlight := flight.ToFlight() + dbFlight, _ := flight.ToFlight() _, err = s.AddFlightToUser(ctx, dbUser.ID, dbFlight) if err != nil { fmt.Println(err) diff --git a/pkg/adapters/json/model.go b/pkg/adapters/json/model.go index 2a91f21..2cc5d8e 100644 --- a/pkg/adapters/json/model.go +++ b/pkg/adapters/json/model.go @@ -32,7 +32,7 @@ func onlyDigits(s string) string { return string(out) } -func (jp JsonProfile) ToUser() *model.User { +func (jp JsonProfile) ToUser() (*model.User, error) { return &model.User{ Name: sOrEmpty(jp.RealName.FirstName), Surname: sOrEmpty(jp.RealName.LastName), @@ -40,10 +40,10 @@ func (jp JsonProfile) ToUser() *model.User { Fathersname: "", Sex: jp.Sex, Birthday: model.SentinelBirthday(), - } + }, nil } -func (jf JsonFlight) ToFlight() *model.Flight { +func (jf JsonFlight) ToFlight() (*model.Flight, error) { return &model.Flight{ Number: jf.Flight, From: jf.Departure.Airport, @@ -53,7 +53,7 @@ func (jf JsonFlight) ToFlight() *model.Flight { ToCity: jf.Arrival.City, ToCountry: jf.Arrival.Country, Date: jf.Date.ToDateUTC(), - } + }, nil } func (jc JsonCard) ToCard() (*model.Card, error) { diff --git a/pkg/adapters/xlsx/model.go b/pkg/adapters/xlsx/model.go new file mode 100644 index 0000000..d8c5194 --- /dev/null +++ b/pkg/adapters/xlsx/model.go @@ -0,0 +1,138 @@ +package xlsx + +import ( + "errors" + "regexp" + "strconv" + "strings" + "time" + + "github.com/leonm1/airports-go" +) + +type Ticket struct { + Sheet string + Passenger string + Title string + FlightNumber string + FromCity string + ToCity string + FromAirport string + ToAirport string + FlightDate string // (raw, expected YYYY-MM-DD; Excel text may start with ') + FlightTime string // (raw, expected HH-MM or HH:MM; Excel text may start with ') + PNR string + Card string + TicketNumber string // (may have a leading ' in Excel) +} + +func (t Ticket) DateTime() (time.Time, *time.Location, error) { + loc := t.inferLocationFromAirports() + date := strings.TrimLeft(strings.TrimSpace(t.FlightDate), "'") + hm := strings.TrimLeft(strings.TrimSpace(t.FlightTime), "'") + hm = strings.ReplaceAll(hm, "-", ":") + + if date == "" || hm == "" { + return time.Time{}, loc, errors.New("missing FlightDate or FlightTime") + } + ts, err := time.ParseInLocation("2006-01-02 15:04", date+" "+hm, loc) + return ts, loc, err +} + +func (t Ticket) inferLocationFromAirports() *time.Location { + if loc := iataToLocation(t.FromAirport); loc != nil { + return loc + } + if loc := iataToLocation(t.ToAirport); loc != nil { + return loc + } + return time.Local +} + +func iataToLocation(code string) *time.Location { + iata := strings.ToUpper(strings.TrimSpace(code)) + if len(iata) != 3 { + return nil + } + ap, err := airports.LookupIATA(iata) + if err != nil { + return nil + } + // Prefer IANA tz name + if tz := strings.TrimSpace(ap.Tz); tz != "" && tz != `\N` { + if loc, err := time.LoadLocation(tz); err == nil { + return loc + } + } + // Fallback: fixed offset (no DST) + if ap.Timezone != 0 { + sec := int(ap.Timezone * 3600.0) + return time.FixedZone("UTC"+offsetLabel(sec), sec) + } + return nil +} + +func offsetLabel(sec int) string { + sign := "+" + if sec < 0 { + sign = "-" + sec = -sec + } + h := sec / 3600 + m := (sec % 3600) / 60 + return sign + two(h) + ":" + two(m) +} +func two(x int) string { + if x < 10 { + return "0" + strconv.Itoa(x) + } + return strconv.Itoa(x) +} + +func parseCardLine(s string) (prefix string, number uint64, bonus string) { + raw := strings.TrimSpace(s) + if raw == "" { + return "", 0, "" + } + // number = last run of digits + if m := regexp.MustCompile(`(\d{3,})\D*$`).FindStringSubmatch(raw); len(m) == 2 { + if n, err := strconv.ParseUint(m[1], 10, 64); err == nil { + number = n + } + } + + // tokens (letters with '-', '/', apostrophes) + tokRe := regexp.MustCompile(`[A-Za-z][A-Za-z'/-]*`) + toks := tokRe.FindAllString(s, -1) + + // prefix = first 2–3 letter all-caps-ish token + for _, t := range toks { + u := strings.ToUpper(t) + if len(u) >= 2 && len(u) <= 3 && regexp.MustCompile(`^[A-Z]{2,3}$`).MatchString(u) { + prefix = u + break + } + } + // bonus = all tokens except prefix + words := []string{} + for _, t := range toks { + if strings.ToUpper(t) == prefix { + continue + } + words = append(words, t) + } + if len(words) > 0 { + bonus = strings.Join(words, " ") + } + if bonus == "" && prefix != "" { + bonus = prefix + } + return +} + +func firstNonEmpty(a, b string) string { + if strings.TrimSpace(a) != "" { + return a + } + return b +} diff --git a/pkg/adapters/xlsx/registry.go b/pkg/adapters/xlsx/registry.go new file mode 100644 index 0000000..46c395e --- /dev/null +++ b/pkg/adapters/xlsx/registry.go @@ -0,0 +1,69 @@ +package xlsx + +import ( + "fmt" + "strings" + + "airlines/pkg/model" + "airlines/pkg/names" + + "github.com/leonm1/airports-go" +) + +func (t Ticket) ToUser() (model.User, error) { + fio, err := names.ParseLatinName(t.Passenger) + if err != nil { + return model.User{}, fmt.Errorf("%v %s", t.Sheet, err.Error()) + } + sex := names.GenderFromTitle(t.Title) + + u := model.User{ + Nick: "", + Name: fio.First, + Surname: fio.Last, + Fathersname: fio.Patronymic, + Sex: sex, + } + return u, nil +} + +func (t Ticket) ToCard() (model.Card, error) { + prefix, number, bonus := parseCardLine(t.Card) + if number == 0 && prefix == "" && bonus == "" { + return model.Card{}, nil + } + return model.Card{ + Prefix: prefix, + Number: number, + Bonusprogramm: "", + }, nil +} + +func (t Ticket) ToFlight() (model.Flight, error) { + // Resolve IATA records + fromIATA := strings.ToUpper(strings.TrimSpace(t.FromAirport)) + toIATA := strings.ToUpper(strings.TrimSpace(t.ToAirport)) + + fromRec, _ := airports.LookupIATA(fromIATA) + toRec, _ := airports.LookupIATA(toIATA) + + fromCity := firstNonEmpty(strings.TrimSpace(t.FromCity), fromRec.City) + toCity := firstNonEmpty(strings.TrimSpace(t.ToCity), toRec.City) + + fromCountry := fromRec.Country + toCountry := toRec.Country + departUTC, _, err := t.DateTime() + if err != nil { + return model.Flight{}, err + } + return model.Flight{ + Number: strings.TrimSpace(t.FlightNumber), + From: fromIATA, + FromCity: fromCity, + FromCountry: fromCountry, + To: toIATA, + ToCity: toCity, + ToCountry: toCountry, + Date: departUTC, + }, nil +} diff --git a/pkg/adapters/xlsx/xlsx.go b/pkg/adapters/xlsx/xlsx.go new file mode 100644 index 0000000..6ef9baa --- /dev/null +++ b/pkg/adapters/xlsx/xlsx.go @@ -0,0 +1,90 @@ +package xlsx + +import ( + "fmt" + "strings" + + "github.com/xuri/excelize/v2" +) + +func UnmarshallXlsxFile(fname string) ([]Ticket, error) { + var err error + f, err := excelize.OpenFile(fname) + if err != nil { + return nil, err + } + defer func() { + if err = f.Close(); err != nil { + fmt.Println(err) + } + }() + + get := func(sheet, cell string) (string, error) { + v, err := f.GetCellValue(sheet, cell) + if err != nil { + return "", fmt.Errorf("%s %s: %w", sheet, cell, err) + } + v = strings.Trim(v, " `'\"") + return v, nil + } + + sheetMap := f.GetSheetMap() + tickets := make([]Ticket, 0, len(sheetMap)) + + for _, sheet := range sheetMap { + t := Ticket{} + t.Sheet = sheet + + t.Passenger, err = get(sheet, "B3") + if err != nil { + return nil, err + } + t.Title, err = get(sheet, "A3") + if err != nil { + return nil, err + } + t.FlightNumber, err = get(sheet, "A5") + if err != nil { + return nil, err + } + t.FromCity, err = get(sheet, "D5") + if err != nil { + return nil, err + } + t.ToCity, err = get(sheet, "H5") + if err != nil { + return nil, err + } + t.FromAirport, err = get(sheet, "D7") + if err != nil { + return nil, err + } + t.ToAirport, err = get(sheet, "H7") + if err != nil { + return nil, err + } + t.FlightDate, err = get(sheet, "A9") + if err != nil { + return nil, err + } + t.FlightTime, err = get(sheet, "C9") + if err != nil { + return nil, err + } + t.PNR, err = get(sheet, "B13") + if err != nil { + return nil, err + } + t.Card, err = get(sheet, "F3") + if err != nil { + return nil, err + } + t.TicketNumber, err = get(sheet, "E13") + if err != nil { + return nil, err + } + + tickets = append(tickets, t) + } + return tickets, nil +} -- cgit v1.2.3