def findMostRecentTakeoff(address: str, addressType: int) -> LogbookItem:
strSql = f"SELECT id, ts, address, address_type, aircraft_type, event, lat, lon, location_icao " \
f"FROM logbook_events " \
f"WHERE address = '{address}' AND address_type={addressType} AND event='T' " \
f"ORDER by ts DESC LIMIT 1;"
with DbSource(dbConnectionInfo).getConnection() as cur:
cur.execute(strSql)
row = cur.fetchone()
if row:
id, ts, address, addressType, aircraftType, event, lat, lon, location = row
item = LogbookItem(id=id,
address=address,
address_type=addressType,
aircraft_type=aircraftType,
takeoff_ts=ts,
takeoff_lat=lat,
takeoff_lon=lon,
takeoff_icao=location)
return item
return None
def listArrivals(address=None,
icaoCode=None,
registration=None,
forDay=None,
limit=None,
icaoFilter: list = [],
sortTsDesc=False):
cond = _prepareCondition(address=address,
icaoCode=icaoCode,
registration=registration)
condTs = ''
condLimit = ''
startTs, endTs = getDayTimestamps(forDay)
if startTs and endTs:
condTs = f" AND l.ts >= {startTs} AND l.ts <= {endTs}"
if limit:
condLimit = f" limit {limit}"
condIcao = ''
if len(icaoFilter) > 0:
c = ""
for i, prefix in enumerate(icaoFilter):
c += f"l.location_icao LIKE '{prefix}%'"
if i < (len(icaoFilter) - 1):
c += ' OR '
condIcao += f" AND ({c})"
sortTs = 'DESC' if sortTsDesc else 'ASC'
records = list()
with DbSource(dbConnectionInfo).getConnection() as c:
# (d.tracked != false OR d.tracked IS NULL) AND (d.identified != false OR d.identified IS NULL)
strSql = f"""SELECT l.ts, l.address, l.address_type, l.aircraft_type, l.lat, l.lon, l.location_icao, l.flight_time,
d.device_type, d.aircraft_type, d.aircraft_registration, d.aircraft_cn
FROM logbook_events AS l
LEFT JOIN ddb AS d ON l.address = d.device_id
WHERE l.event = 'L' AND 1 {cond} {condTs} {condIcao}
ORDER by ts {sortTs} {condLimit};"""
c.execute(strSql)
rows = c.fetchall()
for row in rows:
(ts, address, addrType, aircraftTypeCode, lat, lon, locationIcao,
flightTime, devType, aircraftType, registration, cn) = row
item = LogbookItem(address=address,
takeoff_ts=0,
takeoff_lat=0,
takeoff_lon=0,
takeoff_icao=None,
landing_ts=ts,
landing_lat=float(lat),
landing_lon=float(lon),
landing_icao=locationIcao,
flight_time=flightTime,
device_type=devType,
registration=registration,
cn=cn,
aircraft_type=aircraftType)
records.append(item)
return records
def listFlights(address=None,
icaoCode=None,
registration=None,
forDay=None,
limit=None,
icaoFilter: list = [],
sortTsDesc=False,
orderByCol='takeoff_ts'):
c1 = c2 = ''
if icaoCode:
c1 = f" AND (l.takeoff_icao = '{icaoCode}' OR l.landing_icao = '{icaoCode}')"
if registration:
c2 = f" AND d.aircraft_registration = '{registration}'"
cond = c1 + c2
condTs = ''
condLimit = ''
startTs, endTs = getDayTimestamps(forDay)
if startTs and endTs:
condTs = f" AND l.takeoff_ts >= {startTs} AND l.landing_ts <= {endTs}"
if limit:
condLimit = f" limit {limit}"
condIcao = ''
if len(icaoFilter) > 0:
c = ""
for i, prefix in enumerate(icaoFilter):
c += f"l.takeoff_icao LIKE '{prefix}%' OR l.landing_icao like '{prefix}%'"
if i < (len(icaoFilter) - 1):
c += ' OR '
condIcao += f" AND ({c})"
sortTs = 'DESC' if sortTsDesc else 'ASC'
records = list()
with DbSource(dbConnectionInfo).getConnection() as c:
# (d.tracked != false OR d.tracked IS NULL) AND (d.identified != false OR d.identified IS NULL)
strSql = f"""SELECT l.address, l.takeoff_ts, l.takeoff_lat, l.takeoff_lon, l.takeoff_icao,
l.landing_ts, l.landing_lat, l.landing_lon, l.landing_icao, l.flight_time,
d.device_type, d.aircraft_type, d.aircraft_registration, d.aircraft_cn
FROM logbook_entries as l
LEFT JOIN ddb AS d ON l.address = d.device_id
WHERE 1 {cond} {condTs} {condIcao}
ORDER by {orderByCol} {sortTs} {condLimit};"""
c.execute(strSql)
rows = c.fetchall()
for row in rows:
(address, ts1, lat1, lon1, locationIcao1, ts2, lat2, lon2,
locationIcao2, flightTime, devType, aircraftType, registration,
cn) = row
item = LogbookItem(
address=address,
takeoff_ts=ts1 if ts1 else None,
takeoff_lat=float(lat1) if lat1 else None,
takeoff_lon=float(lon1) if lon1 else None,
takeoff_icao=locationIcao1 if locationIcao1 else None,
landing_ts=ts2,
landing_lat=float(lat2),
landing_lon=float(lon2),
landing_icao=locationIcao2,
flight_time=flightTime,
device_type=devType,
registration=registration,
cn=cn,
aircraft_type=aircraftType)
records.append(item)
return records