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