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
def energy_facility_query(
facility_codes: List[str],
network: NetworkSchema,
period: TimePeriod,
interval: Optional[TimeInterval] = None,
) -> str:
"""
Get Energy for a list of facility codes
"""
__query = """
select
date_trunc('{trunc}', t.trading_interval at time zone '{timezone}') as trading_day,
t.code,
sum(t.energy) / 1000 as fueltech_energy,
sum(t.market_value) as fueltech_market_value,
sum(t.emissions) as fueltech_emissions
from mv_facility_all t
where
t.trading_interval <= '{date_max}' and
t.trading_interval >= '{date_min}' and
t.code in ({facility_codes_parsed})
group by 1, 2
order by
trading_day desc;
"""
timezone = network.timezone_database
offset = network.get_timezone(postgres_format=True)
date_range: ScadaDateRange = get_scada_range(network=network,
facilities=facility_codes)
if not interval:
interval = network.get_interval()
if not date_range:
raise Exception("Require a date range for query")
if not period:
raise Exception("Require a period")
if not interval:
interval = network.get_interval()
if not interval:
raise Exception("Require an interval")
trunc = interval.trunc
date_max = date_range.get_end()
date_min = date_range.get_start()
if period.period_human == "1M":
date_min = date_range.get_end() - timedelta(minutes=period.period)
elif period.period_human == "1Y":
# might have to do +offset times
year = datetime.now().year
date_min = "{}-01-01 00:00:00{}".format(year, offset)
elif period.period_human in ["7d", "5Y", "10Y"]:
date_min = date_range.get_end() - timedelta(minutes=period.period)
# elif period.period_human == "all":
# else:
# date_min = date_range.get_end() - timedelta(minutes=period.period)
query = dedent(
__query.format(
facility_codes_parsed=duid_in_case(facility_codes),
trunc=trunc,
date_max=date_max,
date_min=date_min,
timezone=timezone,
))
return query
def power_facility_query(
facility_codes: List[str],
network: NetworkSchema,
period: TimePeriod,
interval: Optional[TimeInterval] = None,
date_range: Optional[ScadaDateRange] = None,
) -> str:
timezone = network.get_timezone(postgres_format=True)
if not date_range:
date_range = get_scada_range(network=network,
facilities=facility_codes)
timezone = network.timezone_database
__query = """
select
t.trading_interval at time zone '{timezone}',
coalesce(avg(t.facility_power), 0),
t.facility_code
from (
select
time_bucket_gapfill('{trunc}', fs.trading_interval) AS trading_interval,
coalesce(
avg(fs.generated), 0
) as facility_power,
fs.facility_code
from facility_scada fs
join facility f on fs.facility_code = f.code
where
fs.trading_interval <= '{date_max}' and
fs.trading_interval > '{date_min}' and
fs.facility_code in ({facility_codes_parsed})
group by 1, 3
) as t
group by 1, 3
order by 1 desc
"""
if not interval:
interval = network.get_interval()
if not date_range:
raise Exception("Require a date range for query")
if not interval:
raise Exception("Require an interval")
date_max = date_range.get_end()
date_min = date_range.get_start()
if period:
date_min = date_range.get_end() - timedelta(minutes=period.period)
query = __query.format(
facility_codes_parsed=duid_in_case(facility_codes),
trunc=interval.interval_sql,
period=period.period_sql,
timezone=timezone,
date_max=date_max,
date_min=date_min,
)
return query
def power_network_fueltech(
network: NetworkSchema,
interval: TimeInterval,
period: TimePeriod,
network_region: Optional[str] = None,
scada_range: Optional[ScadaDateRange] = None,
) -> str:
timezone = network.get_timezone(postgres_format=True)
if not timezone:
timezone = "UTC"
__query = """
SET SESSION TIME ZONE '{timezone}';
select
t.trading_interval,
sum(t.facility_power),
t.fueltech_code
from (
select
time_bucket_gapfill('{trunc}', trading_interval) AS trading_interval,
coalesce(
avg(fs.generated), 0
) as facility_power,
fs.facility_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 <= {date_end}
and fs.trading_interval >= {date_end}::timestamp - '{period}'::interval
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}'"
date_end = "now()"
if scada_range:
date_end = scada_range.get_end_sql()
query = __query.format(
network_code=network.code,
trunc=interval.interval_sql,
period=period.period_sql,
network_region_query=network_region_query,
timezone=timezone,
date_end=date_end,
)
return query