waypoint-data.sql

select
    1 as ordergroup
  , 0 as ordergroup2
  , 'Departure' as stage
  , concat(coalesce(concat(t.departurestation, ', '),''), s.name) as system
  , null as source
  , 0 as reached
from stwalkerster_ed_explore.trip t
  inner join stwalkerster_ed_explore.system s on s.id = t.`from`
where t.id = %d
union all
select
    3 as ordergroup
  , 0 as ordergroup2
  , 'Arrival' as stage
  , concat(coalesce(concat(t.arrivalstation, ', '),''), s.name) as system
  , null as source
  , w.reached as reached
from stwalkerster_ed_explore.trip t
  inner join stwalkerster_ed_explore.waypoint w on w.id = t.to and w.special = 'a'
  inner join stwalkerster_ed_explore.system s on s.id = w.system
where t.id = %d
union all
select
    2 as ordergroup
  , w.sequence as ordergroup2
  , case w.special
                when 'v' then concat('Via ', w.number)
                else concat('Waypoint ', w.number)
        end as stage
  , coalesce(s.name, '<i>Unplotted</i>') as system
  , concat('CMDR ', c.name) as source
  , w.reached as reached
from stwalkerster_ed_explore.waypoint w
  left join stwalkerster_ed_explore.system s on s.id = w.system
  left join stwalkerster_ed_explore.commander c on c.id = w.commander
where w.trip = %d
  and coalesce(w.special, 'v') = 'v'
order by ordergroup, ordergroup2;