def process_nem_price(table: AEMOTableSchema) -> ControllerReturn:
"""Stores the NEM price for both dispatch price and trading price"""
session = get_scoped_session()
engine = get_database_engine()
cr = ControllerReturn(total_records=len(table.records))
records_to_store = []
primary_keys = []
price_field = "price"
if table.full_name == "dispatch_price":
price_field = "price_dispatch"
for record in table.records:
# @NOTE disable pk track
trading_interval = parse_date(record["settlementdate"])
primary_key = set([trading_interval,
record["regionid"]]) # type: ignore
if primary_key in primary_keys:
continue
primary_keys.append(primary_key)
records_to_store.append({
"network_id": "NEM",
"created_by": "opennem.controllers.nem",
"network_region": record["regionid"],
"trading_interval": trading_interval,
price_field: record["rrp"],
})
cr.processed_records += 1
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "network_region"],
set_={price_field: getattr(stmt.excluded, price_field)},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = cr.processed_records
cr.server_latest = max(
[i["trading_interval"] for i in records_to_store])
except Exception as e:
logger.error("Error inserting NEM price records")
logger.error(e)
cr.errors = cr.processed_records
finally:
session.rollback()
session.close()
engine.dispose()
return cr
def store_wem_facility_intervals(
balancing_set: WEMFacilityIntervalSet) -> ControllerReturn:
"""Persist WEM facility intervals"""
engine = get_database_engine()
session = get_scoped_session()
cr = ControllerReturn()
records_to_store = []
if not balancing_set.intervals:
return cr
cr.total_records = len(balancing_set.intervals)
cr.server_latest = balancing_set.server_latest
for _rec in balancing_set.intervals:
records_to_store.append({
"created_by": "wem.controller",
"network_id": "WEM",
"trading_interval": _rec.trading_interval,
"facility_code": _rec.facility_code,
"generated": _rec.generated,
"eoi_quantity": _rec.eoi_quantity,
})
cr.processed_records += 1
if len(records_to_store) < 1:
return cr
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=[
"trading_interval", "network_id", "facility_code", "is_forecast"
],
set_={
"generated": stmt.excluded.generated,
"eoi_quantity": stmt.excluded.eoi_quantity,
},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = len(records_to_store)
except Exception as e:
logger.error("Error: {}".format(e))
cr.errors = len(records_to_store)
cr.error_detail.append(str(e))
finally:
session.close()
engine.dispose()
return cr
def process_dispatch_interconnectorres(
table: AEMOTableSchema) -> ControllerReturn:
session = get_scoped_session()
engine = get_database_engine()
cr = ControllerReturn(total_records=len(table.records))
records_to_store = []
primary_keys = []
for record in table.records:
primary_key = set(
[record["settlementdate"], record["interconnectorid"]])
if primary_key in primary_keys:
continue
primary_keys.append(primary_key)
records_to_store.append({
"network_id": "NEM",
"created_by": "opennem.controller",
"facility_code": record["interconnectorid"],
"trading_interval": record["settlementdate"],
"generated": record["mwflow"],
})
cr.processed_records += 1
# insert
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=[
"trading_interval", "network_id", "facility_code", "is_forecast"
],
set_={"generated": stmt.excluded.generated},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = cr.processed_records
cr.server_latest = max(
[i["trading_interval"] for i in records_to_store])
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
cr.errors = cr.processed_records
return cr
finally:
session.rollback()
session.close()
engine.dispose()
return cr
def store_apvi_forecastset(forecast_set: APVIForecastSet) -> ControllerReturn:
"""Persist an APVI forecast set to the database"""
engine = get_database_engine()
session = get_scoped_session()
cr = ControllerReturn()
records_to_store = []
if not forecast_set.intervals:
return cr
cr.total_records = len(forecast_set.intervals)
for _rec in forecast_set.intervals:
records_to_store.append(
{**_rec.dict(exclude={"state"}), "created_by": "apvi.controller", "is_forecast": False}
)
cr.processed_records += 1
if len(records_to_store) < 1:
return cr
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "facility_code", "is_forecast"],
set_={
"generated": stmt.excluded.generated,
"created_by": stmt.excluded.created_by,
},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = len(records_to_store)
except Exception as e:
logger.error("Error: {}".format(e))
cr.errors = len(records_to_store)
cr.error_detail.append(str(e))
finally:
session.close()
engine.dispose()
return cr
def store_bom_observation_intervals(
observations: BOMObservationReturn) -> ControllerReturn:
"""Store BOM Observations"""
engine = get_database_engine()
cr = ControllerReturn(total_records=len(observations.observations))
latest_forecast: Optional[datetime] = max([
o.observation_time for o in observations.observations
if o.observation_time
])
if latest_forecast:
latest_forecast = latest_forecast.astimezone(
ZoneInfo("Australia/Sydney"))
logger.debug("server_latest is {}".format(latest_forecast))
cr.server_latest = latest_forecast
records_to_store = []
for obs in observations.observations:
records_to_store.append({
"station_id": observations.station_code,
"observation_time": obs.observation_time,
"temp_apparent": obs.apparent_t,
"temp_air": obs.air_temp,
"press_qnh": obs.press_qnh,
"wind_dir": obs.wind_dir,
"wind_spd": obs.wind_spd_kmh,
"wind_gust": obs.gust_kmh,
"cloud": obs.cloud,
"cloud_type": obs.cloud_type,
"humidity": obs.rel_hum,
})
cr.processed_records += 1
if not len(records_to_store):
return cr
stmt = insert(BomObservation).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["observation_time", "station_id"],
set_={
"temp_apparent": stmt.excluded.temp_apparent,
"temp_air": stmt.excluded.temp_air,
"press_qnh": stmt.excluded.press_qnh,
"wind_dir": stmt.excluded.wind_dir,
"wind_spd": stmt.excluded.wind_spd,
"wind_gust": stmt.excluded.wind_gust,
"cloud": stmt.excluded.cloud,
"cloud_type": stmt.excluded.cloud_type,
"humidity": stmt.excluded.humidity,
},
)
with get_scoped_session() as session:
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error: {}".format(e))
cr.errors = cr.processed_records
cr.error_detail.append(str(e))
finally:
session.close()
engine.dispose()
cr.inserted_records = cr.processed_records
return cr
def store_wem_balancingsummary_set(
balancing_set: WEMBalancingSummarySet) -> ControllerReturn:
"""Persist wem balancing set to the database"""
engine = get_database_engine()
session = get_scoped_session()
cr = ControllerReturn()
records_to_store = []
if not balancing_set.intervals:
return cr
cr.total_records = len(balancing_set.intervals)
cr.server_latest = balancing_set.server_latest
for _rec in balancing_set.intervals:
records_to_store.append({
"created_by": "wem.controller",
"trading_interval": _rec.trading_day_interval,
"network_id": "WEM",
"network_region": "WEM",
"is_forecast": _rec.is_forecast,
"forecast_load": _rec.forecast_mw,
"generation_total": _rec.actual_total_generation,
"generation_scheduled": _rec.actual_nsg_mw,
"price": _rec.price,
})
cr.processed_records += 1
if len(records_to_store) < 1:
return cr
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=[
"trading_interval",
"network_id",
"network_region",
],
set_={
"price": stmt.excluded.price,
"forecast_load": stmt.excluded.forecast_load,
"generation_total": stmt.excluded.generation_total,
"is_forecast": stmt.excluded.is_forecast,
},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = len(records_to_store)
except Exception as e:
logger.error("Error: {}".format(e))
cr.errors = len(records_to_store)
cr.error_detail.append(str(e))
finally:
session.close()
engine.dispose()
return cr
def process_trading_regionsum(table: AEMOTableSchema) -> ControllerReturn:
engine = get_database_engine()
if not table.records:
logger.debug(table)
raise Exception("Invalid table no records")
cr = ControllerReturn(total_records=len(table.records))
limit = None
records_to_store = []
records_processed = 0
primary_keys = []
for record in table.records:
if not isinstance(record, dict):
raise Exception("Invalid record type")
trading_interval = parse_date(
record["settlementdate"],
network=NetworkNEM,
dayfirst=False,
date_format="%Y/%m/%d %H:%M:%S",
)
if not trading_interval:
continue
_pk = set([trading_interval, record["regionid"]])
if _pk in primary_keys:
continue
primary_keys.append(_pk)
net_interchange = None
if "netinterchange" in record:
net_interchange = clean_float(record["netinterchange"])
records_to_store.append({
"network_id": "NEM",
"created_by": "opennem.controller.nem",
"network_region": record["regionid"],
"net_interchange_trading": net_interchange,
"trading_interval": trading_interval,
})
records_processed += 1
if limit and records_processed >= limit:
logger.info("Reached limit of: {} {}".format(
limit, records_processed))
break
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "network_region"],
set_={
"net_interchange_trading": stmt.excluded.net_interchange_trading,
},
)
session = get_scoped_session()
try:
session.execute(stmt)
session.commit()
cr.inserted_records = cr.processed_records
cr.server_latest = max(
[i["trading_interval"] for i in records_to_store])
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
records_to_store = []
cr.errors = cr.processed_records
finally:
session.rollback()
session.close()
engine.dispose()
return cr
def process_dispatch_regionsum(table: AEMOTableSchema) -> ControllerReturn:
session = get_scoped_session()
engine = get_database_engine()
cr = ControllerReturn(total_records=len(table.records))
records_to_store = []
primary_keys = []
for record in table.records:
if not isinstance(record, dict):
continue
trading_interval = parse_date(record.get("settlementdate"))
primary_key = set([trading_interval, record["regionid"]])
if primary_key in primary_keys:
continue
primary_keys.append(primary_key)
if "demand_and_nonschedgen" not in record:
raise Exception("bad value in dispatch_regionsum")
records_to_store.append({
"network_id":
"NEM",
"created_by":
"opennem.controller",
"network_region":
record["regionid"],
"trading_interval":
trading_interval,
"net_interchange":
record["netinterchange"],
"demand":
record["totaldemand"],
"demand_total":
record["demand_and_nonschedgen"],
})
cr.processed_records += 1
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "network_region"],
set_={
"net_interchange": stmt.excluded.net_interchange,
"demand_total": stmt.excluded.demand_total,
"demand": stmt.excluded.demand,
},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = cr.processed_records
cr.server_latest = max(
[i["trading_interval"] for i in records_to_store])
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
cr.errors = cr.processed_records
finally:
session.rollback()
session.close()
engine.dispose()
return cr