def station_attach_stats(station: Station, session: Session) -> Station:
# @TODO update for new queries
since = datetime.now() - human_to_timedelta("7d")
facility_codes = list(set([f.code for f in station.facilities]))
stats = (session.query(FacilityScada).filter(
FacilityScada.facility_code.in_(facility_codes)).filter(
FacilityScada.trading_interval >= since).order_by(
FacilityScada.facility_code).order_by(
FacilityScada.trading_interval).all())
for facility in station.facilities:
facility_power = list(
filter(lambda s: s.facility_code == facility.code, stats))
# facility.scada_power = stats_factory(facility_power)
return station
def power_station(
station_code: str = Query(..., description="Station code"),
network_code: str = Query(..., description="Network code"),
since: datetime = Query(None, description="Since time"),
interval_human: str = Query(None, description="Interval"),
period_human: str = Query("7d", description="Period"),
session: Session = Depends(get_database_session),
engine=Depends(get_database_engine),
) -> OpennemDataSet:
if not since:
since = datetime.now() - human_to_timedelta("7d")
network = network_from_network_code(network_code)
if not network:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail="No such network",
)
if not interval_human:
# @NOTE rooftop data is 15m
if station_code.startswith("ROOFTOP"):
interval_human = "15m"
else:
interval_human = "{}m".format(network.interval_size)
interval = human_to_interval(interval_human)
period = human_to_period(period_human)
units = get_unit("power")
station = (
session.query(Station)
.join(Facility)
.filter(Station.code == station_code)
.filter(Facility.network_id == network.code)
.filter(Station.approved.is_(True))
.one_or_none()
)
if not station:
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Station not found")
facility_codes = list(set([f.code for f in station.facilities]))
stats = []
query = power_facility_query(facility_codes, network=network, interval=interval, period=period)
logger.debug(query)
with engine.connect() as c:
results = list(c.execute(query))
stats = [
DataQueryResult(interval=i[0], result=i[1], group_by=i[2] if len(i) > 1 else None)
for i in results
]
if len(stats) < 1:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail="Station stats not found",
)
result = stats_factory(
stats,
code=station_code,
network=network,
interval=interval,
period=period,
include_group_code=True,
units=units,
)
if not result:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail="No results found",
)
return result
def observation_query(
station_codes: List[str],
interval: TimeInterval,
network: NetworkSchema = NetworkNEM,
period: Optional[TimePeriod] = None,
scada_range: Optional[ScadaDateRange] = None,
year: Optional[Union[str, int]] = None,
) -> str:
if isinstance(year, int):
year = str(year)
timezone = network.timezone_database
if not timezone:
timezone = "AEST"
__query = """
select
time_bucket_gapfill('{trunc}', observation_time) as observation_time,
fs.station_id as station_id,
avg(fs.temp_air) as temp_air,
min(fs.temp_air) as temp_min,
max(fs.temp_air) as temp_max
from bom_observation fs
where
fs.station_id in ({station_codes})
and fs.observation_time <= {date_end}
and fs.observation_time > {date_start_condition}
group by 1, 2;
"""
date_end = ""
if scada_range:
date_end = scada_range.get_end_sql()
date_start_condition = ""
if period:
date_start_condition = "{date_end}::timestamp - '{period}'::interval".format(
date_end=date_end, period=period.period_sql)
date_start_condition = "'{}'".format(scada_range.get_end() -
human_to_timedelta("7d"))
if year:
date_start_condition = "'{year}-12-31'::date".format(year=int(year) -
1)
date_end = "'{year}-12-31'::date".format(year=year)
if not period and not year:
if not scada_range:
raise Exception("require a scada range ")
date_start_condition = "'{}'".format(scada_range.get_start())
query = dedent(
__query.format(
station_codes=station_id_case(station_codes),
trunc=interval.interval_sql,
timezone=timezone,
date_start_condition=date_start_condition,
date_end=date_end,
))
return query
def get_timedelta(self) -> timedelta:
return human_to_timedelta(self.interval_human)