def _cache_scada_wrapper(
network: Optional[NetworkSchema] = None,
networks: Optional[List[NetworkSchema]] = None,
network_region: Optional[str] = None,
facilities: Optional[List[str]] = None,
) -> Optional[ScadaDateRange]:
key_list = []
if network:
key_list = [network.code]
if networks:
key_list += [n.code for n in networks]
if facilities:
key_list += facilities
key = frozenset(key_list)
ret: Optional[ScadaDateRange] = None
try:
_val: Dict = scada_cache[key]
ret = ScadaDateRange(**_val)
logger.debug("scada range HIT at key: {}".format(key))
return ret
except KeyError:
ret = func(network, networks, network_region, facilities)
logger.debug("scada range MISS at key: {}".format(key))
if ret:
scada_cache[key] = ret.dict()
return ret
def energy_network_fueltech_all(
network: Optional[NetworkSchema],
network_region: Optional[str],
scada_range: ScadaDateRange,
):
timezone = "AEST"
if network:
timezone = network.get_timezone(postgres_format=True)
__query = """
SET SESSION TIME ZONE '{timezone}';
select
date_trunc('month', t.trading_interval),
sum(t.facility_energy),
t.fueltech_code
from (
select
time_bucket_gapfill('1 day', trading_interval) AS trading_interval,
energy_sum(fs.generated, '1 day') * interval_size('1 day', count(fs.generated)) / 1000 as facility_energy,
f.code,
ft.code as fueltech_code
from facility_scada fs
join facility f on fs.facility_code = f.code
join fueltech ft on f.fueltech_id = ft.code
where
fs.trading_interval >= {scada_min}
and fs.trading_interval <= {scada_max}
and f.fueltech_id is not null
{network_query}
{network_region_query}
group by 1, 3, 4
) as t
group by 1, 3
order by 1 desc;
"""
network_query = ""
network_region_query = ""
if network:
network_query = f"and fs.network_id = '{network.code}' "
if network_region:
network_region_query = f"and f.network_region='{network_region}' "
query = __query.format(
network_code=network.code,
network_query=network_query,
network_region_query=network_region_query,
scada_min=scada_range.get_start_sql(as_date=True),
scada_max=scada_range.get_end_sql(as_date=True),
timezone=timezone,
)
return query
def price_network_monthly(
network: Optional[NetworkSchema],
network_region_code: Optional[str],
scada_range: ScadaDateRange,
):
timezone = "AEST"
networks = [network.code]
if network.code == "au":
networks = ["WEM", "NEM"]
if network:
timezone = network.get_timezone(postgres_format=True)
__query = """
SET SESSION TIME ZONE '{timezone}';
select
date_trunc('month', t.trading_interval),
avg(t.price),
t.network_id
from (
select
time_bucket_gapfill('1 day', trading_interval) AS trading_interval,
avg(bs.price) as price,
bs.network_id as network_id
from balancing_summary bs
where
bs.trading_interval >= {scada_min}
and bs.trading_interval <= {scada_max}
and bs.network_id IN ({network_codes})
group by 1, 3
) as t
group by 1, 3
order by 1 desc;
"""
network_codes = ""
for network_code in networks:
network_codes = ", ".join("'{}'".format(network_code.upper()))
query = __query.format(
network_codes=network_codes,
scada_min=scada_range.get_start_sql(as_date=True),
scada_max=scada_range.get_end_sql(as_date=True),
timezone=timezone,
)
return query
def observation_query_all(
station_codes: List[str],
scada_range: ScadaDateRange,
network: NetworkSchema = NetworkNEM,
) -> str:
#
timezone = network.timezone_database
if not timezone:
timezone = "UTC"
__query = """
select
date_trunc('month', t.observation_time at time zone '{timezone}') as observation_month,
t.station_id,
avg(t.temp_avg),
min(t.temp_min),
max(t.temp_max)
from
(
select
time_bucket_gapfill('1 day', observation_time) as observation_time,
fs.station_id,
avg(fs.temp_air) as temp_avg,
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}
group by 1, 2
) as t
group by 1, 2
order by 1 desc, 2 desc
"""
date_end = scada_range.get_end_sql()
date_start = scada_range.get_start_sql()
query = __query.format(
station_codes=station_id_case(station_codes),
timezone=timezone,
date_start=date_start,
date_end=date_end,
)
return query
def date_range_from_week(
year: int, week: int, network: Optional[NetworkSchema] = None
) -> ScadaDateRange:
"""
Get a scada date range from week number with
network awareness
"""
start_date_str = f"{year}-W{week - 1}-1"
start_date_dt = datetime.strptime(start_date_str, "%Y-W%W-%w")
end_date = start_date_dt + timedelta(days=7)
scada_range = ScadaDateRange(start=start_date_dt, end=end_date, network=network)
return scada_range
def price_network_region(
network: NetworkSchema,
network_region_code: str,
interval: TimeInterval,
period: TimePeriod,
scada_range: ScadaDateRange,
year: Optional[int] = None,
) -> str:
timezone = network.get_timezone(postgres_format=True)
if not timezone:
timezone = "UTC"
__query = """
SET SESSION TIME ZONE '{timezone}';
select
time_bucket_gapfill('{trunc}', bs.trading_interval) AS trading_interval,
bs.network_region,
coalesce(avg(bs.price), 0) as price
from balancing_summary bs
where
bs.trading_interval >= {date_min_query}
and bs.trading_interval <= {scada_max}
{network_query}
{network_region_query}
group by 1, 2
order by 1 desc
"""
network_query = ""
network_region_query = ""
if network:
network_query = f"and bs.network_id = '{network.code}' "
if network_region_code:
network_region_query = f"and bs.network_region='{network_region_code}' "
date_min_query = ""
if period:
date_min_query = "{scada_max}::timestamp - interval '{period}'::interval".format(
scada_max=scada_range.get_end_sql(), period=period.period_sql)
if year:
date_min_query = "'{year}-01-01'::date ".format(year=year)
if not period and not year:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail="Require one of period or year",
)
query = __query.format(
trunc=interval.interval_sql,
timezone=timezone,
network_query=network_query,
network_region_query=network_region_query,
scada_max=scada_range.get_end_sql(),
date_min_query=date_min_query,
)
return query
def energy_network_fueltech_year(
network: NetworkSchema,
interval: TimeInterval,
year: int,
network_region: str = None,
scada_range: ScadaDateRange = None,
) -> str:
"""
Get Energy for a network or network + region
based on a year
"""
timezone = network.get_timezone(postgres_format=True)
if not timezone:
timezone = "UTC"
year_max = "'{}-12-31'".format(year)
if year == datetime.now().year:
year_max = scada_range.get_end_sql(as_date=False)
__query = """
SET SESSION TIME ZONE '{timezone}';
select
t.trading_interval,
sum(t.facility_energy),
t.fueltech_code
from (
select
time_bucket_gapfill('{trunc}', trading_interval) AS trading_interval,
energy_sum(fs.generated, '{trunc}') * interval_size('1 day', count(fs.generated)) / 1000 as facility_energy,
f.code,
ft.code as fueltech_code
from facility_scada fs
join facility f on fs.facility_code = f.code
join fueltech ft on f.fueltech_id = ft.code
where
fs.trading_interval >= '{year}-01-01'
and fs.trading_interval <= {year_max}
and fs.network_id = '{network_code}'
and f.fueltech_id is not null
{network_region_query}
group by 1, 3, 4
) as t
group by 1, 3
order by 1 desc;
"""
network_region_query = ""
if network_region:
network_region_query = f"and f.network_region='{network_region}'"
query = __query.format(
network_code=network.code,
trunc=interval.interval_sql,
year=year,
year_max=year_max,
scale=network.intervals_per_hour,
network_region_query=network_region_query,
timezone=timezone,
)
return query