def energy_year_network(network_code: str = "WEM", year: int = None) -> str:
if not year:
year = datetime.today().year
network_code = network_code.upper()
network = network_from_network_region(network_code)
timezone = network.timezone_database
if not timezone:
timezone = "UTC"
return """
select
date_trunc('day', fs.trading_interval AT TIME ZONE '{timezone}') AS trading_day,
max(fs.eoi_quantity) as energy_output,
f.fueltech_id as fueltech
from facility_scada fs
left join facility f on fs.facility_code = f.code
where
f.fueltech_id is not null
and extract('year' from fs.trading_interval AT TIME ZONE '{timezone}') = {year}
and fs.network_id = '{network_code}'
group by 1, f.fueltech_id
order by 1 asc, 2 asc
""".format(year=year, network_code=network_code, timezone=timezone)
def energy_network(
engine=Depends(get_database_engine),
network_code: str = Query(..., description="Network code"),
year: int = Query(None, description="Year"),
) -> OpennemDataSet:
query = energy_year_network(year=year, network_code=network_code)
results = []
network = network_from_network_region(network_code)
if not network:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND, detail="Network not found."
)
network_timezone = network.get_timezone()
with engine.connect() as c:
results = list(c.execute(query))
if len(results) < 1:
print(query)
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND, detail="No results"
)
stat_groups = {}
for fueltech, record in groupby(results, attrgetter("fueltech")):
if fueltech not in stat_groups:
stat_groups[fueltech] = []
stat_groups[fueltech] += record
def registry_to_station(registry_station: dict,
_id: int) -> StationImportSchema:
station = StationImportSchema(
id=_id,
code=registry_station.get("station_id", ""),
name=registry_station.get("display_name", ""),
network_name=registry_station.get("display_name", ""),
)
station.location = LocationSchema(
state=registry_station.get("location", {}).get("state", None),
geocode_by="opennem.registry",
geocode_approved=True,
lat=registry_station.get("location", {}).get("latitude", None),
lng=registry_station.get("location", {}).get("longitude", None),
)
if "duid_data" not in registry_station:
logger.info("Registry: station has no duid data: {}".format(
registry_station.get("display_name", "")))
return station
for duid, registry_facility in registry_station["duid_data"].items():
network_region = map_compat_network_region(
registry_station.get("region_id", ""))
facility = FacilitySchema(
**{
"code":
duid,
"created_by":
"opennem.registry",
"created_at":
datetime.now(),
"network":
network_from_network_region(network_region),
"network_region":
network_region,
"station_code":
registry_station.get("station_id", ""),
"dispatch_type":
"GENERATOR",
"status":
parse_facility_status(
map_compat_facility_state(
registry_station.get("status", {}).get("state", ""))),
"fueltech":
parse_facility_fueltech(
map_compat_fueltech(
registry_facility.get("fuel_tech", None))),
"capacity_registered":
registry_facility.get("registered_capacity", None),
})
station.facilities.append(facility)
return station
def energy_facility(
facility_codes: List[str],
network_code: str,
interval: str = "1d",
period: str = "7d",
) -> str:
network_code = network_code.upper()
trunc, interval_str = get_interval_map(interval)
period = get_period_map(period)
scale = 1
if network_code == "NEM":
scale = 12
network = network_from_network_region(network_code)
timezone = network.timezone_database
if not timezone:
timezone = "UTC"
__query = """
with intervals as (
select generate_series(
date_trunc('{trunc}', now() AT TIME ZONE '{timezone}') - '{period}'::interval,
date_trunc('{trunc}', now() AT TIME ZONE '{timezone}'),
'{interval}'::interval
)::timestamp as interval
)
select
i.interval AS trading_day,
fs.facility_code as facility_code,
coalesce(sum(fs.eoi_quantity), NULL) / {scale} as energy_output
from intervals i
left join facility_scada fs on date_trunc('{trunc}', fs.trading_interval AT TIME ZONE '{timezone}')::timestamp = i.interval
where
fs.facility_code in ({facility_codes_parsed})
and fs.trading_interval > now() AT TIME ZONE '{timezone}' - '{period}'::interval
and fs.network_id = '{network_code}'
group by 1, 2
order by 2 asc, 1 asc
"""
query = __query.format(
facility_codes_parsed=duid_in_case(facility_codes),
network_code=network_code,
trunc=trunc,
interval=interval_str,
period=period,
scale=scale,
timezone=timezone,
)
return query
def power_network(
network_code: str,
interval: TimeInterval,
period: TimePeriod,
) -> str:
scale = 1
network = network_from_network_region(network_code)
timezone = network.timezone_database
if not timezone:
timezone = "UTC"
__query = """
with intervals as (
select generate_series(
date_trunc('{trunc}', now() AT TIME ZONE '{timezone}') - '{period}'::interval,
date_trunc('{trunc}', now() AT TIME ZONE '{timezone}'),
'{interval}'::interval
)::timestamp as interval
)
select
i.interval as trading_day,
fs.generated,
fs.facility_code as facility_code
from intervals i
left outer join
(select
date_trunc('{trunc}', fs.trading_interval AT TIME ZONE '{timezone}')::timestamp {interval_remainder} as interval,
fs.facility_code,
coalesce(avg(generated), 0) as generated
from facility_scada fs
where
fs.trading_interval > now() AT TIME ZONE '{timezone}' - '{period}'::interval
and fs.network_id = '{network_code}'
group by 1, 2
) as fs on fs.interval = i.interval
order by 1 desc, 2 desc
"""
query = __query.format(
network_code=network_code,
trunc=interval.trunc,
interval=interval.interval_human,
interval_remainder=interval.get_sql_join(
timezone=network.timezone_database),
period=period.period_sql,
scale=scale,
timezone=timezone,
)
return query
def energy_facility(
facility_codes: List[str],
network_code: str,
interval: TimeInterval,
period: TimePeriod,
) -> str:
network = network_from_network_region(network_code)
timezone = network.timezone_database
__query = """
with intervals as (
select generate_series(
date_trunc('{trunc}', now() AT TIME ZONE '{timezone}') - '{period}'::interval,
date_trunc('{trunc}', now() AT TIME ZONE '{timezone}'),
'{interval}'::interval
)::timestamp as interval
)
select
i.interval as trading_day,
fs.generated,
fs.facility_code as facility_code
from intervals i
left outer join
(select
date_trunc('{trunc}', fs.trading_interval AT TIME ZONE '{timezone}')::timestamp {interval_remainder} as interval,
fs.facility_code,
coalesce(sum(fs.eoi_quantity), 0.0) / {scale} as generated
from facility_scada fs
where
fs.facility_code in ({facility_codes_parsed})
and fs.trading_interval > now() AT TIME ZONE '{timezone}' - '{period}'::interval
and fs.network_id = '{network_code}'
group by 1, 2
) as fs on fs.interval = i.interval
order by 1 desc, 2 asc"""
query = __query.format(
facility_codes_parsed=duid_in_case(facility_codes),
network_code=network_code,
trunc=interval.trunc,
interval=interval.interval_sql,
interval_remainder=interval.get_sql_join(
timezone=network.timezone_database),
period=period.period_sql,
scale=network.intervals_per_hour,
timezone=timezone,
)
return query
def price_network_region(
network_code: str,
region_code: str,
interval: str = "1d",
period: str = "7d",
) -> str:
network_code = network_code.upper()
trunc, interval_str = get_interval_map(interval)
period = get_period_map(period)
network = network_from_network_region(network_code)
timezone = network.timezone_database
if not timezone:
timezone = "UTC"
__query = """
with intervals as (
select generate_series(
date_trunc('{trunc}', now() AT TIME ZONE '{timezone}') - '{period}'::interval,
date_trunc('{trunc}', now() AT TIME ZONE '{timezone}'),
'{interval}'::interval
)::timestamp as interval
)
select
i.interval AS trading_day,
avg(bs.price) as price
from intervals i
left join balancing_summary bs on date_trunc('{trunc}', bs.trading_interval AT TIME ZONE '{timezone}')::timestamp = i.interval
where
bs.network_region = '{region_code}'
and bs.trading_interval > now() AT TIME ZONE '{timezone}' - '{period}'::interval
and bs.network_id = '{network_code}'
group by 1
order by 1
"""
query = __query.format(
region_code=region_code,
network_code=network_code,
trunc=trunc,
interval=interval_str,
period=period,
timezone=timezone,
)
return query