def content(start_date, finish_date, site_id, typ, user):
sess = f = writer = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"site_hh_data_" + to_ct(start_date).strftime("%Y%m%d%H%M") +
".csv", user)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
writer.writerow(("Site Code", "Type", "HH Start Clock-Time") +
tuple(map(str, range(1, 51))))
site = Site.get_by_id(sess, site_id)
line = None
for hh in site.hh_data(sess, start_date, finish_date):
hh_start_ct = to_ct(hh["start_date"])
if (hh_start_ct.hour, hh_start_ct.minute) == (0, 0):
if line is not None:
writer.writerow(line)
line = [site.code, typ, hh_start_ct.strftime("%Y-%m-%d")]
line.append(str(hh[typ]))
if line is not None:
writer.writerow(line)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg)
writer.writerow([msg])
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def content(start_date, finish_date, site_id, typ, user):
sess = f = writer = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"site_hh_data_" + start_date.strftime("%Y%m%d%H%M") + ".csv", user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
writer.writerow(
('Site Code', 'Type', 'Date') + tuple(map(str, range(1, 49))))
site = Site.get_by_id(sess, site_id)
line = None
for hh in site.hh_data(sess, start_date, finish_date):
hh_start = hh['start_date']
if (hh_start.hour, hh_start.minute) == (0, 0):
if line is not None:
writer.writerow(line)
line = [site.code, typ, hh_start.strftime("%Y-%m-%d")]
line.append(str(hh[typ]))
if line is not None:
writer.writerow(line)
except:
msg = traceback.format_exc()
sys.stderr.write(msg)
writer.writerow([msg])
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def long_process(start_date, finish_date, st_id, months, year, month, user):
caches = {}
tmp_file = sess = None
try:
sess = Session()
if st_id is None:
st = None
base_name = "site_monthly_duration_for_all_site_for_" + \
str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
else:
st = Site.get_by_id(sess, st_id)
base_name = "site_monthly_duration_for_" + st.code + "_" + \
str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
running_name, finished_name = chellow.dloads.make_names(
base_name, user)
tmp_file = open(running_name, "w")
forecast_date = chellow.computer.forecast_date()
tmp_file.write(
"Site Id,Site Name,Associated Site Ids,Sources,"
"Generator Types,Month,Metered Imported kWh,"
"Metered Displaced kWh,Metered Exported kWh,Metered Used kWh,"
"Metered Parasitic kWh,Metered Generated kWh,"
"Metered 3rd Party Import kWh,Metered 3rd Party Export kWh,"
"Metered Imported GBP,Metered Displaced GBP,Metered Exported GBP,"
"Metered Used GBP,Metered 3rd Party Import GBP,"
"Billed Imported kWh,Billed Imported GBP,Metering Type,Problem")
for i in range(months):
sites = sess.query(Site).order_by(Site.code)
if st is not None:
sites = sites.filter(Site.id == st.id)
for site in sites:
month_start = start_date + relativedelta(months=i)
month_finish = month_start + relativedelta(months=1) - HH
tmp_file.write(
'\r\n' + ','.join(
'"' + str(value) + '"' for value in process_site(
sess, site, month_start, month_finish,
forecast_date, tmp_file, start_date, finish_date,
caches)))
tmp_file.flush()
except:
msg = traceback.format_exc()
sys.stderr.write(msg + '\n')
tmp_file.write("Problem " + msg)
finally:
if sess is not None:
sess.close()
tmp_file.close()
os.rename(running_name, finished_name)
def content(start_date, finish_date, site_id, typ, sess):
try:
site = Site.get_by_id(sess, site_id)
yield ','.join(
('Site Code', 'Type', 'Date') + tuple(map(str, range(1, 49))))
for group in site.groups(sess, start_date, finish_date, True):
for hh in group.hh_data(sess):
hh_start = hh['start_date']
if (hh_start.hour, hh_start.minute) == (0, 0):
yield '\r\n' + ','.join(
(site.code, typ, hh_start.strftime("%Y-%m-%d")))
yield "," + str(hh[typ])
except:
yield traceback.format_exc()
def site_content(site_id, start_date, finish_date, user, file_name):
sess = f = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
file_name, user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
site = Site.get_by_id(sess, site_id)
sites = sess.query(Site).filter(Site.id == site_id)
start_date_str = hh_format(start_date)
finish_date_str = hh_format(finish_date)
for site in sites:
writer.writerow(
[
"Site Code", "Site Name", "Associated Site Codes",
"Sources", "Generator Types", "From", "To", "Type",
"Date"] + list(map(str, range(1, 49))))
associates = ' '.join(
s.code for s in site.find_linked_sites(
sess, start_date, finish_date))
source_codes = set()
gen_types = set()
for supply in sess.query(Supply).join(Era).join(SiteEra).filter(
SiteEra.is_physical == true(), SiteEra.site == site,
Era.start_date <= finish_date, or_(
Era.finish_date == null(),
Era.finish_date >= start_date)).distinct().options(
joinedload(Supply.source),
joinedload(Supply.generator_type)):
source_codes.add(supply.source.code)
gen_type = supply.generator_type
if gen_type is not None:
gen_types.add(gen_type.code)
source_codes_str = ', '.join(sorted(source_codes))
gen_types_str = ', '.join(sorted(gen_types))
vals = None
for hh in site.hh_data(sess, start_date, finish_date):
hh_start = hh['start_date']
if hh_start.hour == 0 and hh_start.minute == 0:
if vals is not None:
writer.writerow(vals)
vals = [
site.code, site.name, associates, source_codes_str,
gen_types_str, start_date_str, finish_date_str, 'used',
hh_start.strftime('%Y-%m-%d')]
used_gen_kwh = hh['imp_gen'] - hh['exp_net'] - hh['exp_gen']
used_3p_kwh = hh['imp_3p'] - hh['exp_3p']
used_kwh = hh['imp_net'] + used_gen_kwh + used_3p_kwh
vals.append(str(round(used_kwh, 2)))
if vals is not None:
writer.writerow(vals)
except:
msg = traceback.format_exc()
sys.stderr.write(msg)
f.write(msg)
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def content(year, site_id, user):
caches = {}
sess = f = writer = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'output.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
writer.writerow(
(
"Site Code", "Site Name", "Displaced TRIAD 1 Date",
"Displaced TRIAD 1 MSP kW", "Displaced TRIAD LAF",
"Displaced TRIAD 1 GSP kW", "Displaced TRIAD 2 Date",
"Displaced TRIAD 2 MSP kW", "Displaced TRIAD 2 LAF",
"Displaced TRIAD 2 GSP kW", "Displaced TRIAD 3 Date",
"Displaced TRIAD 3 MSP kW", "Displaced TRIAD 3 LAF",
"Displaced TRIAD 3 GSP kW", "Displaced GSP kW",
"Displaced Rate GBP / kW", "GBP"))
march_finish = datetime.datetime(year, 4, 1, tzinfo=pytz.utc) - HH
march_start = datetime.datetime(year, 3, 1, tzinfo=pytz.utc)
forecast_date = chellow.computer.forecast_date()
if site_id is None:
sites = sess.query(Site).join(SiteEra).join(Era).join(Supply).join(
Source).filter(
Source.code.in_(('gen', 'gen-net')),
Era.start_date <= march_finish,
or_(
Era.finish_date == null(),
Era.finish_date >= march_start)).distinct()
else:
site = Site.get_by_id(sess, site_id)
sites = sess.query(Site).filter(Site.id == site.id)
for site in sites.order_by(Site.code):
displaced_era = chellow.computer.displaced_era(
sess, caches, site, march_start, march_finish, forecast_date)
if displaced_era is None:
continue
site_ds = chellow.computer.SiteSource(
sess, site, march_start, march_finish, forecast_date, caches,
displaced_era)
chellow.duos.duos_vb(site_ds)
chellow.triad.hh(site_ds)
chellow.triad.bill(site_ds)
bill = site_ds.supplier_bill
for rname, rset in site_ds.supplier_rate_sets.items():
if len(rset) == 1:
bill[rname] = rset.pop()
values = [site.code, site.name]
for i in range(1, 4):
triad_prefix = 'triad-actual-' + str(i)
values.append(hh_format(bill[triad_prefix + '-date']))
for suffix in ['-msp-kw', '-laf', '-gsp-kw']:
values.append(bill[triad_prefix + suffix])
values += [
str(bill['triad-actual-' + suf]) for suf in [
'gsp-kw', 'rate', 'gbp']]
writer.writerow(values)
except BadRequest as e:
writer.writerow([e.description])
except:
writer.writerow([traceback.format_exc()])
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def site_content(site_id, start_date, finish_date, user, file_name):
sess = f = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
file_name, user)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
site = Site.get_by_id(sess, site_id)
sites = sess.query(Site).filter(Site.id == site_id)
start_date_str = hh_format(start_date)
finish_date_str = hh_format(finish_date)
for site in sites:
writer.writerow([
"Site Code",
"Site Name",
"Associated Site Codes",
"Sources",
"Generator Types",
"From",
"To",
"Type",
"Date",
] + list(map(str, range(1, 51))))
associates = " ".join(
s.code
for s in site.find_linked_sites(sess, start_date, finish_date))
source_codes = set()
gen_types = set()
for supply in (sess.query(Supply).join(Era).join(SiteEra).filter(
SiteEra.is_physical == true(),
SiteEra.site == site,
Era.start_date <= finish_date,
or_(Era.finish_date == null(),
Era.finish_date >= start_date),
).distinct().options(joinedload(Supply.source),
joinedload(Supply.generator_type))):
source_codes.add(supply.source.code)
gen_type = supply.generator_type
if gen_type is not None:
gen_types.add(gen_type.code)
source_codes_str = ", ".join(sorted(source_codes))
gen_types_str = ", ".join(sorted(gen_types))
vals = None
for hh in site.hh_data(sess, start_date, finish_date):
hh_start_ct = to_ct(hh["start_date"])
if hh_start_ct.hour == 0 and hh_start_ct.minute == 0:
if vals is not None:
writer.writerow(vals)
vals = [
site.code,
site.name,
associates,
source_codes_str,
gen_types_str,
start_date_str,
finish_date_str,
"used",
hh_start_ct.strftime("%Y-%m-%d"),
]
used_gen_kwh = hh["imp_gen"] - hh["exp_net"] - hh["exp_gen"]
used_3p_kwh = hh["imp_3p"] - hh["exp_3p"]
used_kwh = hh["imp_net"] + used_gen_kwh + used_3p_kwh
vals.append(str(round(used_kwh, 2)))
if vals is not None:
writer.writerow(vals)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg)
f.write(msg)
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def content(end_year, end_month, months, site_id, sess):
caches = {}
try:
finish_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) + \
relativedelta(months=1) - HH
start_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) - \
relativedelta(months=months-1)
forecast_date = chellow.computer.forecast_date()
site = Site.get_by_id(sess, site_id)
month_start = start_date
month_finish = month_start + relativedelta(months=1) - HH
while not month_finish > finish_date:
for group in site.groups(sess, month_start, month_finish, True):
if group.start_date > month_start:
chunk_start = group.start_date
else:
chunk_start = month_start
if group.finish_date > month_finish:
chunk_finish = month_finish
else:
chunk_finish = group.finish_date
displaced_era = chellow.computer.displaced_era(
sess, group, chunk_start, chunk_finish)
if displaced_era is None:
continue
supplier_contract = displaced_era.imp_supplier_contract
linked_sites = ','.join(
a_site.code for a_site in group.sites
if not a_site == site)
generator_types = ' '.join(
sorted(
[
supply.generator_type.code for
supply in group.supplies
if supply.generator_type is not None]))
total_gen_breakdown = {}
results = iter(
sess.execute(
"select supply.id, hh_datum.value, "
"hh_datum.start_date, channel.imp_related, "
"source.code, generator_type.code as gen_type_code "
"from hh_datum, channel, source, era, supply left "
"outer join generator_type on "
"supply.generator_type_id = generator_type.id where "
"hh_datum.channel_id = channel.id and "
"channel.era_id = era.id and era.supply_id = "
"supply.id and supply.source_id = source.id and "
"channel.channel_type = 'ACTIVE' and not "
"(source.code = 'net' and channel.imp_related is "
"true) and hh_datum.start_date >= :chunk_start and "
"hh_datum.start_date <= :chunk_finish and "
"supply.id = any(:supply_ids) order by "
"hh_datum.start_date, supply.id",
params={
'chunk_start': chunk_start,
'chunk_finish': chunk_finish,
'supply_ids': [s.id for s in group.supplies]}))
try:
res = next(results)
hhChannelValue = res.value
hhChannelStartDate = res.start_date
imp_related = res.imp_related
source_code = res.code
gen_type = res.gen_type_code
hh_date = chunk_start
while hh_date <= finish_date:
gen_breakdown = {}
exported = 0
while hhChannelStartDate == hh_date:
if not imp_related and source_code in (
'net', 'gen-net'):
exported += hhChannelValue
if (imp_related and source_code == 'gen') or (
not imp_related and
source_code == 'gen-net'):
gen_breakdown[gen_type] = \
gen_breakdown.setdefault(gen_type, 0) + \
hhChannelValue
if (not imp_related and source_code == 'gen') or \
(imp_related and source_code == 'gen-net'):
gen_breakdown[gen_type] = \
gen_breakdown.setdefault(gen_type, 0) - \
hhChannelValue
try:
res = next(results)
source_code = res.code
hhChannelValue = res.value
hhChannelStartDate = res.start_date
imp_related = res.imp_related
gen_type = res.gen_type_code
except StopIteration:
hhChannelStartDate = None
displaced = sum(gen_breakdown.values()) - exported
added_so_far = 0
for key in sorted(gen_breakdown.keys()):
kwh = gen_breakdown[key]
if kwh + added_so_far > displaced:
total_gen_breakdown[key] = \
total_gen_breakdown.get(key, 0) + \
displaced - added_so_far
break
else:
total_gen_breakdown[key] = \
total_gen_breakdown.get(key, 0) + kwh
added_so_far += kwh
hh_date += HH
except StopIteration:
pass
site_ds = chellow.computer.SiteSource(
sess, site, chunk_start, chunk_finish, forecast_date, None,
caches, displaced_era)
disp_func = chellow.computer.contract_func(
caches, supplier_contract, 'displaced_virtual_bill', None)
disp_func(site_ds)
bill = site_ds.supplier_bill
bill_titles = chellow.computer.contract_func(
caches, supplier_contract, 'displaced_virtual_bill_titles',
None)()
yield ','.join(
[
'Site Code', 'Site Name', 'Associated Site Ids',
'From', 'To', 'Gen Types', 'CHP kWh', 'LM kWh',
'Turbine kWh', 'PV kWh'] + bill_titles) + '\n'
yield ','.join('"' + str(value) + '"' for value in [
site.code, site.name, linked_sites, hh_format(chunk_start),
hh_format(chunk_finish), generator_types] + [
total_gen_breakdown.get(t, '') for t in [
'chp', 'lm', 'turb', 'pv']])
for title in bill_titles:
if title in bill:
v = bill[title]
if isinstance(v, Datetime):
val = hh_format(v)
else:
val = str(v)
del bill[title]
else:
val = ''
yield ',"' + val + '"'
for k in sorted(bill.keys()):
v = bill[k]
if isinstance(v, Datetime):
val = hh_format(v)
else:
val = str(v)
yield ',"' + k + '","' + val + '"'
yield '\n'
month_start += relativedelta(months=1)
month_finish = month_start + relativedelta(months=1) - HH
except:
yield traceback.format_exc()
def content(scenario_props, base_name, site_id, supply_id, user, compression,
site_codes, now):
report_context = {}
try:
comp = report_context["computer"]
except KeyError:
comp = report_context["computer"] = {}
try:
rate_cache = comp["rates"]
except KeyError:
rate_cache = comp["rates"] = {}
try:
ind_cont = report_context["contract_names"]
except KeyError:
ind_cont = report_context["contract_names"] = {}
sess = None
try:
sess = Session()
start_year = scenario_props["scenario_start_year"]
start_month = scenario_props["scenario_start_month"]
months = scenario_props["scenario_duration"]
month_pairs = list(
c_months_u(start_year=start_year,
start_month=start_month,
months=months))
start_date_utc = month_pairs[0][0]
finish_date_utc = month_pairs[-1][-1]
base_name.append(
hh_format(start_date_utc).replace(" ", "_").replace(":",
"").replace(
"-", ""))
base_name.append("for")
base_name.append(str(months))
base_name.append("months")
if "forecast_from" in scenario_props:
forecast_from = scenario_props["forecast_from"]
else:
forecast_from = None
if forecast_from is None:
forecast_from = chellow.computer.forecast_date()
else:
forecast_from = to_utc(forecast_from)
sites = sess.query(Site).distinct().order_by(Site.code)
if site_id is not None:
site = Site.get_by_id(sess, site_id)
sites = sites.filter(Site.id == site.id)
base_name.append("site")
base_name.append(site.code)
if supply_id is not None:
supply = Supply.get_by_id(sess, supply_id)
base_name.append("supply")
base_name.append(str(supply.id))
sites = sites.join(SiteEra).join(Era).filter(Era.supply == supply)
if len(site_codes) > 0:
base_name.append("sitecodes")
sites = sites.filter(Site.code.in_(site_codes))
running_name, finished_name = chellow.dloads.make_names(
"_".join(base_name) + ".ods", user)
rf = open(running_name, "wb")
site_rows = []
era_rows = []
for rate_script in scenario_props.get("local_rates", []):
contract_id = rate_script["contract_id"]
try:
cont_cache = rate_cache[contract_id]
except KeyError:
cont_cache = rate_cache[contract_id] = {}
try:
rate_script_start = rate_script["start_date"]
except KeyError:
raise BadRequest(
f"Problem in the scenario properties. Can't find the "
f"'start_date' key of the contract {contract_id} in "
f"the 'local_rates' map.")
try:
rate_script_start = rate_script["start_date"]
except KeyError:
raise BadRequest(
f"Problem in the scenario properties. Can't find the "
f"'start_date' key of the contract {contract_id} in "
f"the 'local_rates' map.")
props = PropDict("scenario properties", rate_script["script"])
for dt in hh_range(report_context, rate_script_start,
rate_script["finish_date"]):
cont_cache[dt] = props
for rate_script in scenario_props.get("industry_rates", []):
contract_name = rate_script["contract_name"]
try:
cont_cache = ind_cont[contract_name]
except KeyError:
cont_cache = ind_cont[contract_name] = {}
rfinish = rate_script["finish_date"]
if rfinish is None:
raise BadRequest(
f"For the industry rate {contract_name} the finish_date "
f"can't be null.")
for dt in hh_range(report_context, rate_script["start_date"],
rfinish):
cont_cache[dt] = PropDict("scenario properties",
rate_script["script"])
era_maps = scenario_props.get("era_maps", {})
by_hh = scenario_props.get("by_hh", False)
scenario_hh = scenario_props.get("hh_data", {})
era_header_titles = [
"creation-date",
"imp-mpan-core",
"imp-supplier-contract",
"exp-mpan-core",
"exp-supplier-contract",
"metering-type",
"source",
"generator-type",
"supply-name",
"msn",
"pc",
"site-id",
"site-name",
"associated-site-ids",
"month",
]
site_header_titles = [
"creation-date",
"site-id",
"site-name",
"associated-site-ids",
"month",
"metering-type",
"sources",
"generator-types",
]
summary_titles = [
"import-net-kwh",
"export-net-kwh",
"import-gen-kwh",
"export-gen-kwh",
"import-3rd-party-kwh",
"export-3rd-party-kwh",
"displaced-kwh",
"used-kwh",
"used-3rd-party-kwh",
"import-net-gbp",
"export-net-gbp",
"import-gen-gbp",
"export-gen-gbp",
"import-3rd-party-gbp",
"export-3rd-party-gbp",
"displaced-gbp",
"used-gbp",
"used-3rd-party-gbp",
"billed-import-net-kwh",
"billed-import-net-gbp",
"billed-supplier-import-net-gbp",
"billed-dc-import-net-gbp",
"billed-mop-import-net-gbp",
]
title_dict = {}
for cont_type, con_attr in (
("mop", Era.mop_contract),
("dc", Era.dc_contract),
("imp-supplier", Era.imp_supplier_contract),
("exp-supplier", Era.exp_supplier_contract),
):
titles = []
title_dict[cont_type] = titles
conts = (sess.query(Contract).join(con_attr).join(
Era.supply).join(Source).filter(
Era.start_date <= finish_date_utc,
or_(Era.finish_date == null(),
Era.finish_date >= start_date_utc),
).distinct().order_by(Contract.id))
if supply_id is not None:
conts = conts.filter(Era.supply_id == supply_id)
for cont in conts:
title_func = chellow.computer.contract_func(
report_context, cont, "virtual_bill_titles")
if title_func is None:
raise Exception(
f"For the contract {cont.name} there doesn't seem to "
f"be a 'virtual_bill_titles' function.")
for title in title_func():
if title not in titles:
titles.append(title)
tpr_query = (sess.query(Tpr).join(MeasurementRequirement).join(
Ssc).join(Era).filter(
Era.start_date <= finish_date_utc,
or_(Era.finish_date == null(),
Era.finish_date >= start_date_utc),
).order_by(Tpr.code).distinct())
for tpr in tpr_query.filter(Era.imp_supplier_contract != null()):
for suffix in ("-kwh", "-rate", "-gbp"):
title_dict["imp-supplier"].append(tpr.code + suffix)
for tpr in tpr_query.filter(Era.exp_supplier_contract != null()):
for suffix in ("-kwh", "-rate", "-gbp"):
title_dict["exp-supplier"].append(tpr.code + suffix)
era_rows.append(
era_header_titles + summary_titles + [None] +
["mop-" + t for t in title_dict["mop"]] + [None] +
["dc-" + t for t in title_dict["dc"]] + [None] +
["imp-supplier-" + t for t in title_dict["imp-supplier"]] +
[None] + ["exp-supplier-" + t for t in title_dict["exp-supplier"]])
site_rows.append(site_header_titles + summary_titles)
sites = sites.all()
deltas = {}
normal_reads = set()
normal_read_rows = []
for site in sites:
deltas[site.id] = _make_site_deltas(sess, report_context, site,
scenario_hh, forecast_from,
supply_id)
for month_start, month_finish in month_pairs:
for site in sites:
if by_hh:
sf = [(d, d) for d in hh_range(report_context, month_start,
month_finish)]
else:
sf = [(month_start, month_finish)]
for start, finish in sf:
normal_reads = normal_reads | _process_site(
sess,
report_context,
forecast_from,
start,
finish,
site,
deltas[site.id],
supply_id,
era_maps,
now,
summary_titles,
title_dict,
era_rows,
site_rows,
)
normal_read_rows = [[
"mpan_core", "date", "msn", "type", "registers"
]]
for mpan_core, r in sorted(list(normal_reads)):
row = [mpan_core, r.date, r.msn, r.type] + list(r.reads)
normal_read_rows.append(row)
write_spreadsheet(rf, compression, site_rows, era_rows,
normal_read_rows)
except BadRequest as e:
msg = e.description + traceback.format_exc()
sys.stderr.write(msg + "\n")
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, era_rows,
normal_read_rows)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg + "\n")
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, era_rows,
normal_read_rows)
finally:
if sess is not None:
sess.close()
try:
rf.close()
os.rename(running_name, finished_name)
except BaseException:
msg = traceback.format_exc()
r_name, f_name = chellow.dloads.make_names("error.txt", user)
ef = open(r_name, "w")
ef.write(msg + "\n")
ef.close()
def content(year, site_id, sess):
caches = {}
try:
march_finish = datetime.datetime(year, 4, 1, tzinfo=pytz.utc) - HH
march_start = datetime.datetime(year, 3, 1, tzinfo=pytz.utc)
yield ', '.join(
(
"Site Code", "Site Name", "Displaced TRIAD 1 Date",
"Displaced TRIAD 1 MSP kW", "Displaced TRIAD LAF",
"Displaced TRIAD 1 GSP kW", "Displaced TRIAD 2 Date",
"Displaced TRIAD 2 MSP kW", "Displaced TRIAD 2 LAF",
"Displaced TRIAD 2 GSP kW", "Displaced TRIAD 3 Date",
"Displaced TRIAD 3 MSP kW", "Displaced TRIAD 3 LAF",
"Displaced TRIAD 3 GSP kW", "Displaced GSP kW",
"Displaced Rate GBP / kW", "GBP")) + '\n'
forecast_date = chellow.computer.forecast_date()
if site_id is None:
sites = sess.query(Site).join(SiteEra).join(Era).join(Supply).join(
Source).filter(
Source.code.in_(('gen', 'gen-net')),
Era.start_date <= march_finish,
or_(
Era.finish_date == null(),
Era.finish_date >= march_start)).distinct()
else:
site = Site.get_by_id(sess, site_id)
sites = sess.query(Site).filter(Site.id == site.id)
for site in sites:
for site_group in site.groups(
sess, march_start, march_finish, True):
if site_group.start_date > march_start:
chunk_start = site_group.start_date
else:
chunk_start = march_start
if not site_group.finish_date < march_finish:
chunk_finish = march_finish
else:
continue
yield '"' + site.code + '","' + site.name + '"'
displaced_era = chellow.computer.displaced_era(
sess, site_group, chunk_start, chunk_finish)
if displaced_era is None:
continue
site_ds = chellow.computer.SiteSource(
sess, site, chunk_start, chunk_finish, forecast_date, None,
caches, displaced_era)
chellow.duos.duos_vb(site_ds)
chellow.triad.hh(site_ds)
chellow.triad.bill(site_ds)
bill = site_ds.supplier_bill
for rname, rset in site_ds.supplier_rate_sets.items():
if len(rset) == 1:
bill[rname] = rset.pop()
values = []
for i in range(1, 4):
triad_prefix = 'triad-actual-' + str(i)
for suffix in ['-date', '-msp-kw', '-laf', '-gsp-kw']:
values.append(bill[triad_prefix + suffix])
values += [
bill['triad-actual-' + suf] for suf in [
'gsp-kw', 'rate', 'gbp']]
for value in values:
if isinstance(value, datetime.datetime):
yield "," + hh_format(value)
else:
yield "," + str(value)
yield '\n'
except:
yield traceback.format_exc()
def content(scenario_props, scenario_id, base_name, site_id, supply_id, user):
now = Datetime.now(pytz.utc)
report_context = {}
future_funcs = {}
report_context['future_funcs'] = future_funcs
sess = None
try:
sess = Session()
if scenario_props is None:
scenario_contract = Contract.get_supplier_by_id(sess, scenario_id)
scenario_props = scenario_contract.make_properties()
base_name.append(scenario_contract.name)
for contract in sess.query(Contract).join(MarketRole).filter(
MarketRole.code == 'Z'):
try:
props = scenario_props[contract.name]
except KeyError:
continue
try:
rate_start = props['start_date']
except KeyError:
raise BadRequest(
"In " + scenario_contract.name + " for the rate " +
contract.name + " the start_date is missing.")
if rate_start is not None:
rate_start = rate_start.replace(tzinfo=pytz.utc)
lib = importlib.import_module('chellow.' + contract.name)
if hasattr(lib, 'create_future_func'):
future_funcs[contract.id] = {
'start_date': rate_start,
'func': lib.create_future_func(
props['multiplier'], props['constant'])}
start_date = scenario_props['scenario_start']
if start_date is None:
start_date = Datetime(
now.year, now.month, 1, tzinfo=pytz.utc)
else:
start_date = start_date.replace(tzinfo=pytz.utc)
base_name.append(
hh_format(start_date).replace(' ', '_').replace(':', '').
replace('-', ''))
months = scenario_props['scenario_duration']
base_name.append('for')
base_name.append(str(months))
base_name.append('months')
finish_date = start_date + relativedelta(months=months)
if 'kwh_start' in scenario_props:
kwh_start = scenario_props['kwh_start']
else:
kwh_start = None
if kwh_start is None:
kwh_start = chellow.computer.forecast_date()
else:
kwh_start = kwh_start.replace(tzinfo=pytz.utc)
sites = sess.query(Site).join(SiteEra).join(Era).filter(
Era.start_date <= finish_date,
or_(
Era.finish_date == null(),
Era.finish_date >= start_date)).distinct().order_by(Site.code)
if site_id is not None:
site = Site.get_by_id(sess, site_id)
sites = sites.filter(Site.id == site.id)
base_name.append('site')
base_name.append(site.code)
if supply_id is not None:
supply = Supply.get_by_id(sess, supply_id)
base_name.append('supply')
base_name.append(str(supply.id))
sites = sites.filter(Era.supply == supply)
running_name, finished_name = chellow.dloads.make_names(
'_'.join(base_name) + '.ods', user)
rf = open(running_name, "wb")
f = odswriter.writer(rf, '1.1')
group_tab = f.new_sheet("Site Level")
sup_tab = f.new_sheet("Supply Level")
changes = defaultdict(list, {})
try:
kw_changes = scenario_props['kw_changes']
except KeyError:
kw_changes = ''
for row in csv.reader(io.StringIO(kw_changes)):
if len(''.join(row).strip()) == 0:
continue
if len(row) != 4:
raise BadRequest(
"Can't interpret the row " + str(row) + " it should be of "
"the form SITE_CODE, USED / GENERATED, DATE, MULTIPLIER")
site_code, typ, date_str, kw_str = row
date = Datetime.strptime(date_str.strip(), "%Y-%m-%d").replace(
tzinfo=pytz.utc)
changes[site_code.strip()].append(
{
'type': typ.strip(), 'date': date,
'multiplier': float(kw_str)})
sup_header_titles = [
'imp-mpan-core', 'exp-mpan-core', 'metering-type', 'source',
'generator-type', 'supply-name', 'msn', 'pc', 'site-id',
'site-name', 'associated-site-ids', 'month']
site_header_titles = [
'site-id', 'site-name', 'associated-site-ids', 'month',
'metering-type', 'sources', 'generator-types']
summary_titles = [
'import-net-kwh', 'export-net-kwh', 'import-gen-kwh',
'export-gen-kwh', 'import-3rd-party-kwh', 'export-3rd-party-kwh',
'displaced-kwh', 'used-kwh', 'used-3rd-party-kwh',
'import-net-gbp', 'export-net-gbp', 'import-gen-gbp',
'export-gen-gbp', 'import-3rd-party-gbp', 'export-3rd-party-gbp',
'displaced-gbp', 'used-gbp', 'used-3rd-party-gbp',
'billed-import-net-kwh', 'billed-import-net-gbp']
title_dict = {}
for cont_type, con_attr in (
('mop', Era.mop_contract), ('dc', Era.hhdc_contract),
('imp-supplier', Era.imp_supplier_contract),
('exp-supplier', Era.exp_supplier_contract)):
titles = []
title_dict[cont_type] = titles
conts = sess.query(Contract).join(con_attr) \
.join(Era.supply).join(Source).filter(
Era.start_date <= start_date,
or_(
Era.finish_date == null(),
Era.finish_date >= start_date),
Source.code.in_(('net', '3rd-party'))
).distinct().order_by(Contract.id)
if supply_id is not None:
conts = conts.filter(Era.supply_id == supply_id)
for cont in conts:
title_func = chellow.computer.contract_func(
report_context, cont, 'virtual_bill_titles', None)
if title_func is None:
raise Exception(
"For the contract " + cont.name +
" there doesn't seem to be a "
"'virtual_bill_titles' function.")
for title in title_func():
if title not in titles:
titles.append(title)
sup_tab.writerow(
sup_header_titles + summary_titles + [None] +
['mop-' + t for t in title_dict['mop']] +
[None] + ['dc-' + t for t in title_dict['dc']] + [None] +
['imp-supplier-' + t for t in title_dict['imp-supplier']] +
[None] + ['exp-supplier-' + t for t in title_dict['exp-supplier']])
group_tab.writerow(site_header_titles + summary_titles)
sites = sites.all()
month_start = start_date
while month_start < finish_date:
month_finish = month_start + relativedelta(months=1) - HH
for site in sites:
site_changes = changes[site.code]
site_associates = set()
site_category = None
site_sources = set()
site_gen_types = set()
site_month_data = defaultdict(int)
for group in site.groups(
sess, month_start, month_finish, False):
site_associates.update(
set(
s.code for s in group.sites
if s.code != site.code))
for cand_supply in group.supplies:
site_sources.add(cand_supply.source.code)
if cand_supply.generator_type is not None:
site_gen_types.add(cand_supply.generator_type.code)
for cand_era in sess.query(Era).filter(
Era.supply == cand_supply,
Era.start_date <= group.finish_date, or_(
Era.finish_date == null(),
Era.finish_date >= group.start_date)). \
options(
joinedload(Era.channels),
joinedload(Era.pc),
joinedload(Era.mtc).joinedload(
Mtc.meter_type)):
if site_category != 'hh':
if cand_era.pc.code == '00':
site_category = 'hh'
elif site_category != 'amr':
if len(cand_era.channels) > 0:
site_category = 'amr'
elif site_category != 'nhh':
if cand_era.mtc.meter_type.code \
not in ['UM', 'PH']:
site_category = 'nhh'
else:
site_category = 'unmetered'
for group in site.groups(
sess, month_start, month_finish, True):
calcs = []
deltas = defaultdict(int)
group_associates = set(
s.code for s in group.sites if s.code != site.code)
for supply in group.supplies:
if supply_id is not None and supply.id != supply_id:
continue
for era in sess.query(Era).join(Supply) \
.join(Source).filter(
Era.supply == supply,
Era.start_date <= group.finish_date, or_(
Era.finish_date == null(),
Era.finish_date >= group.start_date)) \
.options(
joinedload(Era.ssc),
joinedload(Era.hhdc_contract),
joinedload(Era.mop_contract),
joinedload(Era.imp_supplier_contract),
joinedload(Era.exp_supplier_contract),
joinedload(Era.channels),
joinedload(Era.imp_llfc).joinedload(
Llfc.voltage_level),
joinedload(Era.exp_llfc).joinedload(
Llfc.voltage_level),
joinedload(Era.cop),
joinedload(Era.supply).joinedload(
Supply.dno_contract),
joinedload(Era.mtc).joinedload(
Mtc.meter_type)):
if era.start_date > group.start_date:
ss_start = era.start_date
else:
ss_start = group.start_date
if hh_before(era.finish_date, group.finish_date):
ss_finish = era.finish_date
else:
ss_finish = group.finish_date
if era.imp_mpan_core is None:
imp_ss = None
else:
imp_ss = SupplySource(
sess, ss_start, ss_finish, kwh_start, era,
True, None, report_context)
if era.exp_mpan_core is None:
exp_ss = None
measurement_type = imp_ss.measurement_type
else:
exp_ss = SupplySource(
sess, ss_start, ss_finish, kwh_start, era,
False, None, report_context)
measurement_type = exp_ss.measurement_type
order = meter_order[measurement_type]
calcs.append(
(
order, era.imp_mpan_core,
era.exp_mpan_core, imp_ss, exp_ss))
if imp_ss is not None and len(era.channels) == 0:
for hh in imp_ss.hh_data:
deltas[hh['start-date']] += hh['msp-kwh']
imp_net_delts = defaultdict(int)
exp_net_delts = defaultdict(int)
imp_gen_delts = defaultdict(int)
displaced_era = chellow.computer.displaced_era(
sess, group, group.start_date, group.finish_date)
site_ds = chellow.computer.SiteSource(
sess, site, group.start_date, group.finish_date,
kwh_start, None, report_context, displaced_era)
for hh in site_ds.hh_data:
try:
delta = deltas[hh['start-date']]
hh['import-net-kwh'] += delta
hh['used-kwh'] += delta
except KeyError:
pass
for hh in site_ds.hh_data:
for change in site_changes:
if change['type'] == 'used' and \
change['date'] <= hh['start-date']:
used = change['multiplier'] * hh['used-kwh']
exp_net = max(
0, hh['import-gen-kwh'] -
hh['export-gen-kwh'] -
used)
exp_net_delt = exp_net - hh['export-net-kwh']
exp_net_delts[hh['start-date']] += exp_net_delt
displaced = hh['import-gen-kwh'] - \
hh['export-gen-kwh'] - exp_net
imp_net = used - displaced
imp_delt = imp_net - hh['import-net-kwh']
imp_net_delts[hh['start-date']] += imp_delt
hh['import-net-kwh'] = imp_net
hh['used-kwh'] = used
hh['export-net-kwh'] = exp_net
hh['msp-kwh'] = displaced
elif change['type'] == 'generated' and \
change['date'] <= hh['start-date']:
imp_gen = change['multiplier'] * \
hh['import-gen-kwh']
imp_gen_delt = imp_gen - hh['import-gen-kwh']
exp_net = max(
0, imp_gen - hh['export-gen-kwh'] -
hh['used-kwh'])
exp_net_delt = exp_net - hh['export-net-kwh']
exp_net_delts[hh['start-date']] += exp_net_delt
displaced = imp_gen - hh['export-gen-kwh'] - \
exp_net
imp_net = hh['used-kwh'] - displaced
imp_net_delt = imp_net - hh['import-net-kwh']
imp_net_delts[hh['start-date']] += imp_net_delt
imp_gen_delts[hh['start-date']] += imp_gen_delt
hh['import-net-kwh'] = imp_net
hh['export-net-kwh'] = exp_net
hh['import-gen-kwh'] = imp_gen
hh['msp-kwh'] = displaced
if displaced_era is not None and supply_id is None:
month_data = {}
for sname in (
'import-net', 'export-net', 'import-gen',
'export-gen', 'import-3rd-party',
'export-3rd-party', 'msp', 'used',
'used-3rd-party', 'billed-import-net'):
for xname in ('kwh', 'gbp'):
month_data[sname + '-' + xname] = 0
month_data['used-kwh'] = \
month_data['displaced-kwh'] = \
sum(hh['msp-kwh'] for hh in site_ds.hh_data)
disp_supplier_contract = \
displaced_era.imp_supplier_contract
disp_vb_function = chellow.computer.contract_func(
report_context, disp_supplier_contract,
'displaced_virtual_bill', None)
if disp_vb_function is None:
raise BadRequest(
"The supplier contract " +
disp_supplier_contract.name +
" doesn't have the displaced_virtual_bill() "
"function.")
disp_vb_function(site_ds)
disp_supplier_bill = site_ds.supplier_bill
try:
gbp = disp_supplier_bill['net-gbp']
except KeyError:
disp_supplier_bill['problem'] += \
'For the supply ' + \
site_ds.mpan_core + \
' the virtual bill ' + \
str(disp_supplier_bill) + \
' from the contract ' + \
disp_supplier_contract.name + \
' does not contain the net-gbp key.'
month_data['used-gbp'] = \
month_data['displaced-gbp'] = \
site_ds.supplier_bill['net-gbp']
out = [
None, None, displaced_era.make_meter_category(),
'displaced', None, None, None, None, site.code,
site.name,
','.join(sorted(list(group_associates))),
month_finish] + \
[month_data[t] for t in summary_titles]
sup_tab.writerow(out)
for k, v in month_data.items():
site_month_data[k] += v
for i, (
order, imp_mpan_core, exp_mpan_core, imp_ss,
exp_ss) in enumerate(sorted(calcs, key=str)):
if imp_ss is None:
era = exp_ss.era
else:
era = imp_ss.era
supply = era.supply
source = supply.source
source_code = source.code
site_sources.add(source_code)
month_data = {}
for name in (
'import-net', 'export-net', 'import-gen',
'export-gen', 'import-3rd-party',
'export-3rd-party', 'displaced', 'used',
'used-3rd-party', 'billed-import-net'):
for sname in ('kwh', 'gbp'):
month_data[name + '-' + sname] = 0
if source_code == 'net':
delts = imp_net_delts
elif source_code == 'gen':
delts = imp_gen_delts
else:
delts = []
if len(delts) > 0 and imp_ss is not None:
for hh in imp_ss.hh_data:
diff = hh['msp-kwh'] + delts[hh['start-date']]
if diff < 0:
hh['msp-kwh'] = 0
hh['msp-kw'] = 0
delts[hh['start-date']] -= hh['msp-kwh']
else:
hh['msp-kwh'] += delts[hh['start-date']]
hh['msp-kw'] += hh['msp-kwh'] / 2
del delts[hh['start-date']]
left_kwh = sum(delts.values())
if left_kwh > 0:
first_hh = imp_ss.hh_data[0]
first_hh['msp-kwh'] += left_kwh
first_hh['msp-kw'] += left_kwh / 2
imp_supplier_contract = era.imp_supplier_contract
if imp_supplier_contract is not None:
import_vb_function = contract_func(
report_context, imp_supplier_contract,
'virtual_bill', None)
if import_vb_function is None:
raise BadRequest(
"The supplier contract " +
imp_supplier_contract.name +
" doesn't have the virtual_bill() "
"function.")
import_vb_function(imp_ss)
imp_supplier_bill = imp_ss.supplier_bill
try:
gbp = imp_supplier_bill['net-gbp']
except KeyError:
imp_supplier_bill['problem'] += \
'For the supply ' + \
imp_ss.mpan_core + \
' the virtual bill ' + \
str(imp_supplier_bill) + \
' from the contract ' + \
imp_supplier_contract.name + \
' does not contain the net-gbp key.'
if source_code in ('net', 'gen-net'):
month_data['import-net-gbp'] += gbp
month_data['used-gbp'] += gbp
elif source_code == '3rd-party':
month_data['import-3rd-party-gbp'] += gbp
month_data['used-gbp'] += gbp
elif source_code == '3rd-party-reverse':
month_data['export-3rd-party-gbp'] += gbp
month_data['used-gbp'] -= gbp
kwh = sum(
hh['msp-kwh'] for hh in imp_ss.hh_data)
if source_code in ('net', 'gen-net'):
month_data['import-net-kwh'] += kwh
month_data['used-kwh'] += kwh
elif source_code == '3rd-party':
month_data['import-3rd-party-kwh'] += kwh
month_data['used-kwh'] += kwh
elif source_code == '3rd-party-reverse':
month_data['export-3rd-party-kwh'] += kwh
month_data['used-kwh'] -= kwh
elif source_code in ('gen', 'gen-net'):
month_data['import-gen-kwh'] += kwh
exp_supplier_contract = era.exp_supplier_contract
if exp_supplier_contract is None:
kwh = sess.query(
func.coalesce(
func.sum(
cast(HhDatum.value, Float)), 0)). \
join(Channel).filter(
Channel.era == era,
Channel.channel_type == 'ACTIVE',
Channel.imp_related == false()).scalar()
if source_code == 'gen':
month_data['export-net-kwh'] += kwh
else:
export_vb_function = contract_func(
report_context, exp_supplier_contract,
'virtual_bill', None)
export_vb_function(exp_ss)
exp_supplier_bill = exp_ss.supplier_bill
try:
gbp = exp_supplier_bill['net-gbp']
except KeyError:
exp_supplier_bill['problem'] += \
'For the supply ' + \
imp_ss.mpan_core + \
' the virtual bill ' + \
str(imp_supplier_bill) + \
' from the contract ' + \
imp_supplier_contract.name + \
' does not contain the net-gbp key.'
kwh = sum(hh['msp-kwh'] for hh in exp_ss.hh_data)
if source_code in ('net', 'gen-net'):
month_data['export-net-kwh'] += kwh
month_data['export-net-gbp'] += gbp
elif source_code in \
('3rd-party', '3rd-party-reverse'):
month_data['export-3rd-party-kwh'] += kwh
month_data['export-3rd-party-gbp'] += gbp
month_data['used-kwh'] -= kwh
month_data['used-gbp'] -= gbp
elif source_code == 'gen':
month_data['export-gen-kwh'] += kwh
sss = exp_ss if imp_ss is None else imp_ss
dc_contract = era.hhdc_contract
sss.contract_func(
dc_contract, 'virtual_bill')(sss)
dc_bill = sss.dc_bill
gbp = dc_bill['net-gbp']
mop_contract = era.mop_contract
mop_bill_function = sss.contract_func(
mop_contract, 'virtual_bill')
mop_bill_function(sss)
mop_bill = sss.mop_bill
gbp += mop_bill['net-gbp']
if source_code in ('3rd-party', '3rd-party-reverse'):
month_data['import-3rd-party-gbp'] += gbp
else:
month_data['import-net-gbp'] += gbp
month_data['used-gbp'] += gbp
if source_code in ('gen', 'gen-net'):
generator_type = supply.generator_type.code
site_gen_types.add(generator_type)
else:
generator_type = None
sup_category = era.make_meter_category()
if CATEGORY_ORDER[site_category] < \
CATEGORY_ORDER[sup_category]:
site_category = sup_category
for bill in sess.query(Bill).filter(
Bill.supply == supply,
Bill.start_date <= sss.finish_date,
Bill.finish_date >= sss.start_date):
bill_start = bill.start_date
bill_finish = bill.finish_date
bill_duration = (
bill_finish - bill_start).total_seconds() + \
(30 * 60)
overlap_duration = (
min(bill_finish, sss.finish_date) -
max(bill_start, sss.start_date)
).total_seconds() + (30 * 60)
overlap_proportion = \
float(overlap_duration) / bill_duration
month_data['billed-import-net-kwh'] += \
overlap_proportion * float(bill.kwh)
month_data['billed-import-net-gbp'] += \
overlap_proportion * float(bill.net)
out = [
era.imp_mpan_core, era.exp_mpan_core,
sup_category, source_code,
generator_type, supply.name, era.msn, era.pc.code,
site.code, site.name,
','.join(sorted(list(site_associates))),
month_finish] + [
month_data[t] for t in summary_titles] + [None] + [
(mop_bill[t] if t in mop_bill else None)
for t in title_dict['mop']] + [None] + \
[(dc_bill[t] if t in dc_bill else None)
for t in title_dict['dc']]
if imp_supplier_contract is None:
out += [None] * \
(len(title_dict['imp-supplier']) + 1)
else:
out += [None] + [
(
imp_supplier_bill[t]
if t in imp_supplier_bill else None)
for t in title_dict['imp-supplier']]
if exp_supplier_contract is not None:
out += [None] + [
(
exp_supplier_bill[t]
if t in exp_supplier_bill else None)
for t in title_dict['exp-supplier']]
for k, v in month_data.items():
site_month_data[k] += v
sup_tab.writerow(out)
group_tab.writerow(
[
site.code, site.name,
''.join(sorted(list(site_associates))),
month_finish, site_category,
', '.join(sorted(list(site_sources))),
', '.join(sorted(list(site_gen_types)))] +
[site_month_data[k] for k in summary_titles])
sess.rollback()
month_start += relativedelta(months=1)
except BadRequest as e:
msg = e.description + traceback.format_exc()
sys.stderr.write(msg + '\n')
group_tab.writerow(["Problem " + msg])
except:
msg = traceback.format_exc()
sys.stderr.write(msg + '\n')
group_tab.writerow(["Problem " + msg])
finally:
if sess is not None:
sess.close()
try:
f.close()
rf.close()
os.rename(running_name, finished_name)
except:
msg = traceback.format_exc()
r_name, f_name = chellow.dloads.make_names('error.txt', user)
ef = open(r_name, "w")
ef.write(msg + '\n')
ef.close()
def get(self, site_id):
return Site.get_by_id(g.sess, site_id)
def content(base_name, site_id, g_supply_id, user, compression, start_date,
months):
now = utc_datetime_now()
report_context = {}
sess = None
try:
sess = Session()
base_name.append(
hh_format(start_date).replace(' ',
'_').replace(':',
'').replace('-', ''))
base_name.append('for')
base_name.append(str(months))
base_name.append('months')
finish_date = start_date + relativedelta(months=months)
forecast_from = chellow.computer.forecast_date()
sites = sess.query(Site).distinct().order_by(Site.code)
if site_id is not None:
site = Site.get_by_id(sess, site_id)
sites = sites.filter(Site.id == site.id)
base_name.append('site')
base_name.append(site.code)
if g_supply_id is not None:
g_supply = GSupply.get_by_id(sess, g_supply_id)
base_name.append('g_supply')
base_name.append(str(g_supply.id))
sites = sites.join(SiteGEra).join(GEra).filter(
GEra.g_supply == g_supply)
running_name, finished_name = chellow.dloads.make_names(
'_'.join(base_name) + '.ods', user)
rf = open(running_name, "wb")
site_rows = []
g_era_rows = []
era_header_titles = [
'creation_date', 'mprn', 'supply_name', 'exit_zone', 'msn', 'unit',
'contract', 'site_id', 'site_name', 'associated_site_ids', 'month'
]
site_header_titles = [
'creation_date', 'site_id', 'site_name', 'associated_site_ids',
'month'
]
summary_titles = ['kwh', 'gbp', 'billed_kwh', 'billed_gbp']
vb_titles = []
conts = sess.query(GContract).join(GEra).join(GSupply).filter(
GEra.start_date <= finish_date,
or_(GEra.finish_date == null(),
GEra.finish_date >= start_date)).distinct().order_by(
GContract.id)
if g_supply_id is not None:
conts = conts.filter(GEra.g_supply_id == g_supply_id)
for cont in conts:
title_func = chellow.computer.contract_func(
report_context, cont, 'virtual_bill_titles')
if title_func is None:
raise Exception("For the contract " + cont.name +
" there doesn't seem " +
"to be a 'virtual_bill_titles' function.")
for title in title_func():
if title not in vb_titles:
vb_titles.append(title)
g_era_rows.append(era_header_titles + summary_titles + vb_titles)
site_rows.append(site_header_titles + summary_titles)
sites = sites.all()
month_start = start_date
while month_start < finish_date:
month_finish = month_start + relativedelta(months=1) - HH
for site in sites:
site_kwh = site_gbp = site_billed_kwh = site_billed_gbp = 0
for g_era in sess.query(GEra).join(SiteGEra).filter(
SiteGEra.site == site, SiteGEra.is_physical == true(),
GEra.start_date <= month_finish,
or_(GEra.finish_date == null(),
GEra.finish_date >= month_start)).options(
joinedload(GEra.g_contract),
joinedload(GEra.g_supply),
joinedload(GEra.g_supply).joinedload(
GSupply.g_exit_zone)).order_by(GEra.id):
g_supply = g_era.g_supply
if g_supply_id is not None and g_supply.id != g_supply_id:
continue
ss_start = hh_max(g_era.start_date, month_start)
ss_finish = hh_min(g_era.finish_date, month_finish)
ss = GDataSource(sess, ss_start, ss_finish, forecast_from,
g_era, report_context, None)
contract = g_era.g_contract
vb_function = contract_func(report_context, contract,
'virtual_bill')
if vb_function is None:
raise BadRequest(
"The contract " + contract.name +
" doesn't have the virtual_bill() function.")
vb_function(ss)
bill = ss.bill
try:
gbp = bill['net_gbp']
except KeyError:
gbp = 0
bill['problem'] += 'For the supply ' + ss.mprn + \
' the virtual bill ' + str(bill) + \
' from the contract ' + contract.name + \
' does not contain the net_gbp key.'
try:
kwh = bill['kwh']
except KeyError:
kwh = 0
bill['problem'] += "For the supply " + ss.mprn + \
" the virtual bill " + str(bill) + \
" from the contract " + contract.name + \
" does not contain the 'kwh' key."
billed_kwh = billed_gbp = 0
g_era_associates = {
s.site.code
for s in g_era.site_g_eras if not s.is_physical
}
for g_bill in sess.query(GBill).filter(
GBill.g_supply == g_supply,
GBill.start_date <= ss_finish,
GBill.finish_date >= ss_start):
bill_start = g_bill.start_date
bill_finish = g_bill.finish_date
bill_duration = (
bill_finish - bill_start).total_seconds() + \
(30 * 60)
overlap_duration = (min(bill_finish, ss_finish) - max(
bill_start, ss_start)).total_seconds() + (30 * 60)
overlap_proportion = overlap_duration / bill_duration
billed_kwh += overlap_proportion * float(g_bill.kwh)
billed_gbp += overlap_proportion * float(g_bill.net)
associated_site_ids = ','.join(sorted(g_era_associates))
g_era_rows.append([
now, g_supply.mprn, g_supply.name, g_supply.g_exit_zone
.code, g_era.msn, g_era.g_unit.code, contract.name,
site.code, site.name, associated_site_ids,
month_finish, kwh, gbp, billed_kwh, billed_gbp
] + [make_val(bill.get(t)) for t in vb_titles])
site_kwh += kwh
site_gbp += gbp
site_billed_kwh += billed_kwh
site_billed_gbp += billed_gbp
linked_sites = ', '.join(s.code
for s in site.find_linked_sites(
sess, month_start, month_finish))
site_rows.append([
now, site.code, site.name, linked_sites, month_finish,
site_kwh, site_gbp, site_billed_kwh, site_billed_gbp
])
sess.rollback()
write_spreadsheet(rf, compression, site_rows, g_era_rows)
month_start += relativedelta(months=1)
except BadRequest as e:
msg = e.description + traceback.format_exc()
sys.stderr.write(msg + '\n')
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, g_era_rows)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg + '\n')
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, g_era_rows)
finally:
if sess is not None:
sess.close()
try:
rf.close()
os.rename(running_name, finished_name)
except BaseException:
msg = traceback.format_exc()
r_name, f_name = chellow.dloads.make_names('error.txt', user)
ef = open(r_name, "w")
ef.write(msg + '\n')
ef.close()
def content(site_id,
g_supply_id,
user,
compression,
finish_year,
finish_month,
months,
now=None):
if now is None:
now = ct_datetime_now()
report_context = {}
sess = None
month_list = list(
c_months_u(finish_year=finish_year,
finish_month=finish_month,
months=months))
start_date, finish_date = month_list[0][0], month_list[-1][-1]
try:
sess = Session()
base_name = [
"g_monthly_duration",
hh_format(start_date).replace(" ",
"_").replace(":",
"").replace("-", ""),
"for",
str(months),
"months",
]
forecast_from = chellow.computer.forecast_date()
sites = (sess.query(Site).join(SiteGEra).join(GEra).filter(
SiteGEra.is_physical == true()).distinct().order_by(Site.code))
if site_id is not None:
site = Site.get_by_id(sess, site_id)
sites = sites.filter(Site.id == site.id)
base_name.append("site")
base_name.append(site.code)
if g_supply_id is not None:
g_supply = GSupply.get_by_id(sess, g_supply_id)
base_name.append("g_supply")
base_name.append(str(g_supply.id))
sites = sites.filter(GEra.g_supply == g_supply)
running_name, finished_name = chellow.dloads.make_names(
"_".join(base_name) + ".ods", user)
rf = open(running_name, "wb")
site_rows = []
g_era_rows = []
era_header_titles = [
"creation_date",
"mprn",
"supply_name",
"exit_zone",
"msn",
"unit",
"contract",
"site_id",
"site_name",
"associated_site_ids",
"month",
]
site_header_titles = [
"creation_date",
"site_id",
"site_name",
"associated_site_ids",
"month",
]
summary_titles = ["kwh", "gbp", "billed_kwh", "billed_gbp"]
vb_titles = []
conts = (sess.query(GContract).join(GEra).join(GSupply).filter(
GEra.start_date <= finish_date,
or_(GEra.finish_date == null(), GEra.finish_date >= start_date),
).distinct().order_by(GContract.id))
if g_supply_id is not None:
conts = conts.filter(GEra.g_supply_id == g_supply_id)
for cont in conts:
title_func = chellow.computer.contract_func(
report_context, cont, "virtual_bill_titles")
if title_func is None:
raise Exception("For the contract " + cont.name +
" there doesn't seem " +
"to be a 'virtual_bill_titles' function.")
for title in title_func():
if title not in vb_titles:
vb_titles.append(title)
g_era_rows.append(era_header_titles + summary_titles + vb_titles)
site_rows.append(site_header_titles + summary_titles)
for month_start, month_finish in month_list:
for site in sites.filter(
GEra.start_date <= month_finish,
or_(GEra.finish_date == null(),
GEra.finish_date >= month_start),
):
site_kwh = site_gbp = site_billed_kwh = site_billed_gbp = 0
for g_era in (sess.query(GEra).join(SiteGEra).filter(
SiteGEra.site == site,
SiteGEra.is_physical == true(),
GEra.start_date <= month_finish,
or_(GEra.finish_date == null(),
GEra.finish_date >= month_start),
).options(
joinedload(GEra.g_contract),
joinedload(GEra.g_supply),
joinedload(GEra.g_supply).joinedload(
GSupply.g_exit_zone),
).order_by(GEra.id)):
g_supply = g_era.g_supply
if g_supply_id is not None and g_supply.id != g_supply_id:
continue
ss_start = hh_max(g_era.start_date, month_start)
ss_finish = hh_min(g_era.finish_date, month_finish)
ss = GDataSource(
sess,
ss_start,
ss_finish,
forecast_from,
g_era,
report_context,
None,
)
contract = g_era.g_contract
vb_function = contract_func(report_context, contract,
"virtual_bill")
if vb_function is None:
raise BadRequest(
"The contract " + contract.name +
" doesn't have the virtual_bill() function.")
vb_function(ss)
bill = ss.bill
try:
gbp = bill["net_gbp"]
except KeyError:
gbp = 0
bill["problem"] += (
"For the supply " + ss.mprn +
" the virtual bill " + str(bill) +
" from the contract " + contract.name +
" does not contain the net_gbp key.")
try:
kwh = bill["kwh"]
except KeyError:
kwh = 0
bill["problem"] += ("For the supply " + ss.mprn +
" the virtual bill " + str(bill) +
" from the contract " +
contract.name +
" does not contain the 'kwh' key.")
billed_kwh = billed_gbp = 0
g_era_associates = {
s.site.code
for s in g_era.site_g_eras if not s.is_physical
}
for g_bill in sess.query(GBill).filter(
GBill.g_supply == g_supply,
GBill.start_date <= ss_finish,
GBill.finish_date >= ss_start,
):
bill_start = g_bill.start_date
bill_finish = g_bill.finish_date
bill_duration = (bill_finish - bill_start
).total_seconds() + (30 * 60)
overlap_duration = (min(bill_finish, ss_finish) - max(
bill_start, ss_start)).total_seconds() + (30 * 60)
overlap_proportion = overlap_duration / bill_duration
billed_kwh += overlap_proportion * float(g_bill.kwh)
billed_gbp += overlap_proportion * float(g_bill.net)
associated_site_ids = ",".join(sorted(g_era_associates))
g_era_rows.append([
make_val(v) for v in [
now,
g_supply.mprn,
g_supply.name,
g_supply.g_exit_zone.code,
g_era.msn,
g_era.g_unit.code,
contract.name,
site.code,
site.name,
associated_site_ids,
month_finish,
kwh,
gbp,
billed_kwh,
billed_gbp,
]
] + [make_val(bill.get(t)) for t in vb_titles])
site_kwh += kwh
site_gbp += gbp
site_billed_kwh += billed_kwh
site_billed_gbp += billed_gbp
linked_sites = ", ".join(s.code
for s in site.find_linked_sites(
sess, month_start, month_finish))
site_rows.append([
make_val(v) for v in [
now,
site.code,
site.name,
linked_sites,
month_finish,
site_kwh,
site_gbp,
site_billed_kwh,
site_billed_gbp,
]
])
sess.rollback()
write_spreadsheet(rf, compression, site_rows, g_era_rows)
except BadRequest as e:
site_rows.append(["Problem " + e.description])
write_spreadsheet(rf, compression, site_rows, g_era_rows)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg + "\n")
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, g_era_rows)
finally:
if sess is not None:
sess.close()
try:
rf.close()
os.rename(running_name, finished_name)
except BaseException:
msg = traceback.format_exc()
r_name, f_name = chellow.dloads.make_names("error.txt", user)
ef = open(r_name, "w")
ef.write(msg + "\n")
ef.close()
def content(year, site_id, user):
caches = {}
sess = f = writer = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'output.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
writer.writerow(("Site Code", "Site Name", "Displaced TRIAD 1 Date",
"Displaced TRIAD 1 MSP kW", "Displaced TRIAD LAF",
"Displaced TRIAD 1 GSP kW", "Displaced TRIAD 2 Date",
"Displaced TRIAD 2 MSP kW", "Displaced TRIAD 2 LAF",
"Displaced TRIAD 2 GSP kW", "Displaced TRIAD 3 Date",
"Displaced TRIAD 3 MSP kW", "Displaced TRIAD 3 LAF",
"Displaced TRIAD 3 GSP kW", "Displaced GSP kW",
"Displaced Rate GBP / kW", "GBP"))
march_finish = datetime.datetime(year, 4, 1, tzinfo=pytz.utc) - HH
march_start = datetime.datetime(year, 3, 1, tzinfo=pytz.utc)
forecast_date = chellow.computer.forecast_date()
if site_id is None:
sites = sess.query(Site).join(SiteEra).join(Era).join(Supply).join(
Source).filter(
Source.code.in_(('gen', 'gen-net')),
Era.start_date <= march_finish,
or_(Era.finish_date == null(),
Era.finish_date >= march_start)).distinct()
else:
site = Site.get_by_id(sess, site_id)
sites = sess.query(Site).filter(Site.id == site.id)
for site in sites.order_by(Site.code):
displaced_era = chellow.computer.displaced_era(
sess, caches, site, march_start, march_finish, forecast_date)
if displaced_era is None:
continue
site_ds = chellow.computer.SiteSource(sess, site, march_start,
march_finish, forecast_date,
caches, displaced_era)
chellow.duos.duos_vb(site_ds)
chellow.triad.hh(site_ds)
chellow.triad.bill(site_ds)
bill = site_ds.supplier_bill
for rname, rset in site_ds.supplier_rate_sets.items():
if len(rset) == 1:
bill[rname] = rset.pop()
values = [site.code, site.name]
for i in range(1, 4):
triad_prefix = 'triad-actual-' + str(i)
values.append(hh_format(bill[triad_prefix + '-date']))
for suffix in ['-msp-kw', '-laf', '-gsp-kw']:
values.append(bill[triad_prefix + suffix])
values += [
str(bill['triad-actual-' + suf])
for suf in ['gsp-kw', 'rate', 'gbp']
]
writer.writerow(values)
# Avoid long-running transaction
sess.rollback()
except BadRequest as e:
writer.writerow([e.description])
except BaseException:
writer.writerow([traceback.format_exc()])
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def content(scenario_props, scenario_id, base_name, site_id, supply_id, user,
compression):
now = utc_datetime_now()
report_context = {}
try:
comp = report_context['computer']
except KeyError:
comp = report_context['computer'] = {}
try:
rate_cache = comp['rates']
except KeyError:
rate_cache = comp['rates'] = {}
try:
ind_cont = report_context['contract_names']
except KeyError:
ind_cont = report_context['contract_names'] = {}
sess = None
try:
sess = Session()
if scenario_props is None:
scenario_contract = Contract.get_supplier_by_id(sess, scenario_id)
scenario_props = scenario_contract.make_properties()
base_name.append(scenario_contract.name)
start_date = scenario_props['scenario_start']
if start_date is None:
start_date = utc_datetime(now.year, now.month, 1)
else:
start_date = to_utc(start_date)
base_name.append(
hh_format(start_date).replace(' ',
'_').replace(':',
'').replace('-', ''))
months = scenario_props['scenario_duration']
base_name.append('for')
base_name.append(str(months))
base_name.append('months')
finish_date = start_date + relativedelta(months=months)
if 'forecast_from' in scenario_props:
forecast_from = scenario_props['forecast_from']
else:
forecast_from = None
if forecast_from is None:
forecast_from = chellow.computer.forecast_date()
else:
forecast_from = to_utc(forecast_from)
sites = sess.query(Site).distinct().order_by(Site.code)
if site_id is not None:
site = Site.get_by_id(sess, site_id)
sites = sites.filter(Site.id == site.id)
base_name.append('site')
base_name.append(site.code)
if supply_id is not None:
supply = Supply.get_by_id(sess, supply_id)
base_name.append('supply')
base_name.append(str(supply.id))
sites = sites.join(SiteEra).join(Era).filter(Era.supply == supply)
running_name, finished_name = chellow.dloads.make_names(
'_'.join(base_name) + '.ods', user)
rf = open(running_name, "wb")
site_rows = []
era_rows = []
for rate_script in get_map_list(scenario_props, 'local_rates'):
contract_id = rate_script['contract_id']
try:
cont_cache = rate_cache[contract_id]
except KeyError:
cont_cache = rate_cache[contract_id] = {}
try:
rate_script_start = rate_script['start_date']
except KeyError:
raise BadRequest(
"Problem in the scenario properties. Can't find the " +
"'start_date' key of the contract " + str(contract_id) +
" in the 'local_rates' map.")
try:
rate_script_start = rate_script['start_date']
except KeyError:
raise BadRequest(
"Problem in the scenario properties. Can't find the " +
"'start_date' key of the contract " + str(contract_id) +
" in the 'local_rates' map.")
for dt in hh_range(report_context, rate_script_start,
rate_script['finish_date']):
cont_cache[dt] = PropDict('scenario properties',
rate_script['script'])
for rate_script in get_map_list(scenario_props, 'industry_rates'):
contract_name = rate_script['contract_name']
try:
cont_cache = ind_cont[contract_name]
except KeyError:
cont_cache = ind_cont[contract_name] = {}
rfinish = rate_script['finish_date']
if rfinish is None:
raise BadRequest("For the industry rate " + contract_name +
" the "
"finish_date can't be null.")
for dt in hh_range(report_context, rate_script['start_date'],
rfinish):
cont_cache[dt] = PropDict('scenario properties',
rate_script['script'])
era_maps = scenario_props.get('era_maps', {})
scenario_hh = scenario_props.get('hh_data', {})
era_header_titles = [
'creation-date', 'imp-mpan-core', 'imp-supplier-contract',
'exp-mpan-core', 'exp-supplier-contract', 'metering-type',
'source', 'generator-type', 'supply-name', 'msn', 'pc', 'site-id',
'site-name', 'associated-site-ids', 'month'
]
site_header_titles = [
'creation-date', 'site-id', 'site-name', 'associated-site-ids',
'month', 'metering-type', 'sources', 'generator-types'
]
summary_titles = [
'import-net-kwh', 'export-net-kwh', 'import-gen-kwh',
'export-gen-kwh', 'import-3rd-party-kwh', 'export-3rd-party-kwh',
'displaced-kwh', 'used-kwh', 'used-3rd-party-kwh',
'import-net-gbp', 'export-net-gbp', 'import-gen-gbp',
'export-gen-gbp', 'import-3rd-party-gbp', 'export-3rd-party-gbp',
'displaced-gbp', 'used-gbp', 'used-3rd-party-gbp',
'billed-import-net-kwh', 'billed-import-net-gbp'
]
title_dict = {}
for cont_type, con_attr in (('mop', Era.mop_contract),
('dc', Era.dc_contract),
('imp-supplier',
Era.imp_supplier_contract),
('exp-supplier',
Era.exp_supplier_contract)):
titles = []
title_dict[cont_type] = titles
conts = sess.query(Contract).join(con_attr).join(Era.supply). \
join(Source).filter(
Era.start_date <= finish_date, or_(
Era.finish_date == null(),
Era.finish_date >= start_date),
Source.code.in_(('net', '3rd-party'))
).distinct().order_by(Contract.id)
if supply_id is not None:
conts = conts.filter(Era.supply_id == supply_id)
for cont in conts:
title_func = chellow.computer.contract_func(
report_context, cont, 'virtual_bill_titles')
if title_func is None:
raise Exception("For the contract " + cont.name +
" there doesn't seem to be a "
"'virtual_bill_titles' function.")
for title in title_func():
if title not in titles:
titles.append(title)
tpr_query = sess.query(Tpr).join(MeasurementRequirement).join(Ssc). \
join(Era).filter(
Era.start_date <= finish_date, or_(
Era.finish_date == null(),
Era.finish_date >= start_date)
).order_by(Tpr.code).distinct()
for tpr in tpr_query.filter(Era.imp_supplier_contract != null()):
for suffix in ('-kwh', '-rate', '-gbp'):
title_dict['imp-supplier'].append(tpr.code + suffix)
for tpr in tpr_query.filter(Era.exp_supplier_contract != null()):
for suffix in ('-kwh', '-rate', '-gbp'):
title_dict['exp-supplier'].append(tpr.code + suffix)
era_rows.append(
era_header_titles + summary_titles + [None] +
['mop-' + t for t in title_dict['mop']] + [None] +
['dc-' + t for t in title_dict['dc']] + [None] +
['imp-supplier-' + t for t in title_dict['imp-supplier']] +
[None] + ['exp-supplier-' + t for t in title_dict['exp-supplier']])
site_rows.append(site_header_titles + summary_titles)
sites = sites.all()
deltas = {}
for site in sites:
try:
site_scenario_hh = scenario_hh[site.code]
except KeyError:
site_scenario_hh = scenario_hh[site.code] = {}
site_deltas = deltas[site.code] = {'hhs': {}}
delts = site_deltas['supply_deltas'] = {}
for is_import in (True, False):
delts[is_import] = {}
for src in ('gen', 'net', 'gen-net', '3rd-party',
'3rd-party-reverse', 'sub'):
delts[is_import][src] = {'site': {}}
earliest_delta = to_utc(Datetime.max)
latest_delta = to_utc(Datetime.min)
found_hh = False
for typ in ('used', 'generated', 'parasitic', 'gen_net'):
hh_str = site_scenario_hh.get(typ, '')
hh_data = site_scenario_hh[typ] = {}
for row in csv.reader(StringIO(hh_str)):
cells = [cell.strip() for cell in row]
if len(''.join(cells)) == 0:
continue
if len(cells) != 2:
raise BadRequest(
"Can't interpret the row " + str(cells) +
" it should be of the form 'timestamp, kWh'")
date_str, kw_str = cells
ts = parse_hh_start(date_str)
earliest_delta = min(ts, earliest_delta)
latest_delta = max(ts, latest_delta)
hh_data[ts] = float(kw_str)
found_hh = True
if not found_hh:
continue
scenario_used = site_scenario_hh['used']
scenario_generated = site_scenario_hh['generated']
scenario_parasitic = site_scenario_hh['parasitic']
scenario_gen_net = site_scenario_hh['gen_net']
month_start = utc_datetime(earliest_delta.year,
earliest_delta.month)
while month_start <= latest_delta:
month_finish = month_start + relativedelta(months=1) - HH
chunk_start = hh_max(month_start, earliest_delta)
chunk_finish = hh_min(month_finish, latest_delta)
site_ds = chellow.computer.SiteSource(sess, site, chunk_start,
chunk_finish,
forecast_from,
report_context)
hh_map = dict((h['start-date'], h) for h in site_ds.hh_data)
for era in sess.query(Era).join(SiteEra).join(Pc).filter(
SiteEra.site == site, SiteEra.is_physical == true(),
Era.imp_mpan_core != null(), Pc.code != '00',
Era.start_date <= chunk_finish,
or_(Era.finish_date == null(),
Era.finish_date >= chunk_start),
~Era.channels.any()):
if supply_id is not None and era.supply_id != supply_id:
continue
ss_start = hh_max(era.start_date, chunk_start)
ss_finish = hh_min(era.finish_date, chunk_finish)
ss = SupplySource(sess, ss_start, ss_finish, forecast_from,
era, True, report_context)
for hh in ss.hh_data:
sdatum = hh_map[hh['start-date']]
sdatum['import-net-kwh'] += hh['msp-kwh']
sdatum['used-kwh'] += hh['msp-kwh']
for era in sess.query(Era).join(SiteEra).join(Pc).join(
Supply).join(Source).filter(
SiteEra.site == site,
SiteEra.is_physical == true(),
Era.imp_mpan_core != null(),
Era.start_date <= chunk_finish,
or_(Era.finish_date == null(),
Era.finish_date >= chunk_start),
Source.code == 'gen-net'):
if supply_id is not None and era.supply_id != supply_id:
continue
ss_start = hh_max(era.start_date, chunk_start)
ss_finish = hh_min(era.finish_date, chunk_finish)
ss = SupplySource(sess, ss_start, ss_finish, forecast_from,
era, False, report_context)
for hh in ss.hh_data:
sdatum = hh_map[hh['start-date']]
try:
sdatum['gen-net-kwh'] += hh['msp-kwh']
except KeyError:
sdatum['gen-net-kwh'] = hh['msp-kwh']
for hh in site_ds.hh_data:
hh_start = hh['start-date']
if hh_start in scenario_used:
used_delt = scenario_used[hh_start] - hh['used-kwh']
imp_net_delt = 0
exp_net_delt = 0
if used_delt < 0:
diff = hh['import-net-kwh'] + used_delt
if diff < 0:
imp_net_delt -= hh['import-net-kwh']
exp_net_delt -= diff
else:
imp_net_delt += used_delt
else:
diff = hh['export-net-kwh'] - used_delt
if diff < 0:
exp_net_delt -= hh['export-net-kwh']
imp_net_delt -= diff
else:
exp_net_delt -= used_delt
try:
delts[False]['net']['site'][hh_start] += \
exp_net_delt
except KeyError:
delts[False]['net']['site'][hh_start] = \
exp_net_delt
try:
delts[True]['net']['site'][hh_start] += \
imp_net_delt
except KeyError:
delts[True]['net']['site'][hh_start] = imp_net_delt
hh['import-net-kwh'] += imp_net_delt
hh['export-net-kwh'] += exp_net_delt
hh['used-kwh'] += used_delt
hh['msp-kwh'] -= exp_net_delt
if hh_start in scenario_generated:
imp_gen_delt = scenario_generated[hh_start] - \
hh['import-gen-kwh']
imp_net_delt = 0
exp_net_delt = 0
if imp_gen_delt < 0:
diff = hh['export-net-kwh'] + imp_gen_delt
if diff < 0:
exp_net_delt -= hh['export-net-kwh']
imp_net_delt -= diff
else:
exp_net_delt += imp_gen_delt
else:
diff = hh['import-net-kwh'] - imp_gen_delt
if diff < 0:
imp_net_delt -= hh['import-net-kwh']
exp_net_delt -= diff
else:
imp_net_delt -= imp_gen_delt
try:
delts[True]['gen']['site'][hh_start] += \
imp_gen_delt
except KeyError:
delts[True]['gen']['site'][hh_start] = imp_gen_delt
try:
delts[False]['net']['site'][hh_start] += \
exp_net_delt
except KeyError:
delts[False]['net']['site'][hh_start] = \
exp_net_delt
try:
delts[True]['net']['site'][hh_start] += \
imp_net_delt
except KeyError:
delts[True]['net']['site'][hh_start] = imp_net_delt
hh['import-net-kwh'] += imp_net_delt
hh['export-net-kwh'] += exp_net_delt
hh['import-gen-kwh'] += imp_gen_delt
hh['msp-kwh'] -= imp_net_delt
if hh_start in scenario_parasitic:
exp_gen_delt = scenario_parasitic[hh_start] - \
hh['export-gen-kwh']
imp_net_delt = 0
exp_net_delt = 0
if exp_gen_delt < 0:
diff = hh['import-net-kwh'] + exp_gen_delt
if diff < 0:
imp_net_delt -= hh['import-net-kwh']
exp_net_delt -= diff
else:
imp_net_delt += exp_gen_delt
else:
diff = hh['export-net-kwh'] - exp_gen_delt
if diff < 0:
exp_net_delt -= hh['export-net-kwh']
imp_net_delt -= diff
else:
exp_net_delt -= exp_gen_delt
try:
delts[False]['gen']['site'][hh_start] += \
imp_gen_delt
except KeyError:
delts[False]['gen']['site'][hh_start] = \
exp_gen_delt
try:
delts[False]['net']['site'][hh_start] += \
exp_net_delt
except KeyError:
delts[False]['net']['site'][hh_start] = \
exp_net_delt
try:
delts[True]['net']['site'][hh_start] += \
imp_net_delt
except KeyError:
delts[True]['net']['site'][hh_start] = imp_net_delt
hh['import-net-kwh'] += imp_net_delt
hh['export-net-kwh'] += exp_net_delt
hh['export-gen-kwh'] += exp_gen_delt
hh['msp-kwh'] -= imp_net_delt
if hh_start in scenario_gen_net:
gen_net_delt = scenario_gen_net[hh_start] - \
hh['gen-net-kwh']
try:
delts[False]['gen-net']['site'][hh_start] += \
gen_net_delt
except KeyError:
delts[False]['gen-net']['site'][hh_start] = \
gen_net_delt
hh['import-gen-kwh'] += gen_net_delt
hh['export-net-kwh'] += gen_net_delt
site_deltas['hhs'][hh_start] = hh
month_start += relativedelta(months=1)
month_start = start_date
while month_start < finish_date:
month_finish = month_start + relativedelta(months=1) - HH
for site in sites:
site_category = None
site_sources = set()
site_gen_types = set()
site_month_data = defaultdict(int)
calcs = []
for era in sess.query(Era).join(SiteEra).join(Pc).filter(
SiteEra.site == site, SiteEra.is_physical == true(),
Era.start_date <= month_finish,
or_(Era.finish_date == null(),
Era.finish_date >= month_start)).options(
joinedload(Era.ssc),
joinedload(Era.dc_contract),
joinedload(Era.mop_contract),
joinedload(Era.imp_supplier_contract),
joinedload(Era.exp_supplier_contract),
joinedload(Era.channels),
joinedload(Era.imp_llfc).joinedload(
Llfc.voltage_level),
joinedload(Era.exp_llfc).joinedload(
Llfc.voltage_level), joinedload(Era.cop),
joinedload(Era.supply).joinedload(Supply.dno),
joinedload(Era.supply).joinedload(
Supply.gsp_group),
joinedload(Era.supply).joinedload(
Supply.source),
joinedload(Era.mtc).joinedload(Mtc.meter_type),
joinedload(Era.pc),
joinedload(Era.site_eras)).order_by(Pc.code):
supply = era.supply
if supply.generator_type is not None:
site_gen_types.add(supply.generator_type.code)
if supply_id is not None and supply.id != supply_id:
continue
ss_start = hh_max(era.start_date, month_start)
ss_finish = hh_min(era.finish_date, month_finish)
if era.imp_mpan_core is None:
imp_ss = None
else:
sup_deltas = site_deltas['supply_deltas'][True][
supply.source.code]
imp_ss = SupplySource(sess,
ss_start,
ss_finish,
forecast_from,
era,
True,
report_context,
era_maps=era_maps,
deltas=sup_deltas)
if era.exp_mpan_core is None:
exp_ss = None
measurement_type = imp_ss.measurement_type
else:
sup_deltas = site_deltas['supply_deltas'][False][
supply.source.code]
exp_ss = SupplySource(sess,
ss_start,
ss_finish,
forecast_from,
era,
False,
report_context,
era_maps=era_maps,
deltas=sup_deltas)
measurement_type = exp_ss.measurement_type
order = meter_order[measurement_type]
calcs.append((order, era.imp_mpan_core, era.exp_mpan_core,
imp_ss, exp_ss))
# Check if gen deltas haven't been consumed
extra_sss = set()
for is_imp in (True, False):
sup_deltas = site_deltas['supply_deltas'][is_imp]['gen']
if len(
list(t for t in sup_deltas['site']
if month_start <= t <= month_finish)) > 0:
extra_sss.add(is_imp)
displaced_era = chellow.computer.displaced_era(
sess,
report_context,
site,
month_start,
month_finish,
forecast_from,
has_scenario_generation=len(extra_sss) > 0)
if len(extra_sss) > 0:
if True in extra_sss:
sup_deltas = site_deltas['supply_deltas'][True]['gen']
imp_ss_name = site.code + "_extra_gen_TRUE"
imp_ss = ScenarioSource(
sess, month_start, month_finish, True,
report_context, sup_deltas,
displaced_era.imp_supplier_contract, imp_ss_name)
else:
imp_ss_name = imp_ss = None
if False in extra_sss:
exp_ss_name = site.code + "_extra_gen_FALSE"
sup_deltas = site_deltas['supply_deltas'][False]['gen']
exp_ss = ScenarioSource(
sess, month_start, month_finish, False,
report_context, sup_deltas,
displaced_era.imp_supplier_contract, imp_ss_name)
else:
exp_ss_name = exp_ss = None
calcs.append((0, imp_ss_name, exp_ss_name, imp_ss, exp_ss))
# Check if exp net deltas haven't been consumed
sup_deltas = site_deltas['supply_deltas'][False]['net']
if len(
list(t for t in sup_deltas['site']
if month_start <= t <= month_finish)) > 0:
ss_name = site.code + "_extra_net_export"
ss = SupplySource(sess,
month_start,
month_finish,
forecast_from,
displaced_era,
False,
report_context,
era_maps=era_maps,
deltas=sup_deltas)
calcs.append((0, None, ss_name, None, ss))
site_ds = chellow.computer.SiteSource(sess,
site,
month_start,
month_finish,
forecast_from,
report_context,
displaced_era,
deltas=site_deltas)
if displaced_era is not None and supply_id is None:
month_data = {}
for sname in ('import-net', 'export-net', 'import-gen',
'export-gen', 'import-3rd-party',
'export-3rd-party', 'msp', 'used',
'used-3rd-party', 'billed-import-net'):
for xname in ('kwh', 'gbp'):
month_data[sname + '-' + xname] = 0
month_data['used-kwh'] = month_data['displaced-kwh'] = sum(
hh['msp-kwh'] for hh in site_ds.hh_data)
disp_supplier_contract = \
displaced_era.imp_supplier_contract
disp_vb_function = chellow.computer.contract_func(
report_context, disp_supplier_contract,
'displaced_virtual_bill')
if disp_vb_function is None:
raise BadRequest(
"The supplier contract " +
disp_supplier_contract.name +
" doesn't have the displaced_virtual_bill() "
"function.")
disp_vb_function(site_ds)
disp_supplier_bill = site_ds.supplier_bill
try:
gbp = disp_supplier_bill['net-gbp']
except KeyError:
disp_supplier_bill['problem'] += 'For the supply ' + \
site_ds.mpan_core + ' the virtual bill ' + \
str(disp_supplier_bill) + ' from the contract ' + \
disp_supplier_contract.name + \
' does not contain the net-gbp key.'
month_data['used-gbp'] = month_data['displaced-gbp'] = \
site_ds.supplier_bill['net-gbp']
out = [
now, None, disp_supplier_contract.name, None, None,
displaced_era.meter_category, 'displaced', None, None,
None, None, site.code, site.name, '', month_finish
] + [month_data[t] for t in summary_titles
] + [None] + [None] * len(title_dict['mop']) + [
None
] + [None] * len(title_dict['dc']) + [
None
] + make_bill_row(title_dict['imp-supplier'],
disp_supplier_bill)
era_rows.append(out)
for k, v in month_data.items():
site_month_data[k] += v
for i, (order, imp_mpan_core, exp_mpan_core, imp_ss,
exp_ss) in enumerate(sorted(calcs, key=str)):
if imp_ss is None:
source_code = exp_ss.source_code
supply = exp_ss.supply
else:
source_code = imp_ss.source_code
supply = imp_ss.supply
site_sources.add(source_code)
month_data = {}
for name in ('import-net', 'export-net', 'import-gen',
'export-gen', 'import-3rd-party',
'export-3rd-party', 'displaced', 'used',
'used-3rd-party', 'billed-import-net'):
for sname in ('kwh', 'gbp'):
month_data[name + '-' + sname] = 0
if imp_ss is not None:
imp_supplier_contract = imp_ss.supplier_contract
if imp_supplier_contract is not None:
import_vb_function = contract_func(
report_context, imp_supplier_contract,
'virtual_bill')
if import_vb_function is None:
raise BadRequest(
"The supplier contract " +
imp_supplier_contract.name +
" doesn't have the virtual_bill() "
"function.")
import_vb_function(imp_ss)
kwh = sum(hh['msp-kwh'] for hh in imp_ss.hh_data)
imp_supplier_bill = imp_ss.supplier_bill
try:
gbp = imp_supplier_bill['net-gbp']
except KeyError:
gbp = 0
imp_supplier_bill['problem'] += \
'For the supply ' + \
imp_ss.mpan_core + \
' the virtual bill ' + \
str(imp_supplier_bill) + \
' from the contract ' + \
imp_supplier_contract.name + \
' does not contain the net-gbp key.'
if source_code in ('net', 'gen-net'):
month_data['import-net-gbp'] += gbp
month_data['import-net-kwh'] += kwh
month_data['used-gbp'] += gbp
month_data['used-kwh'] += kwh
if source_code == 'gen-net':
month_data['export-gen-kwh'] += kwh
elif source_code == '3rd-party':
month_data['import-3rd-party-gbp'] += gbp
month_data['import-3rd-party-kwh'] += kwh
month_data['used-3rd-party-gbp'] += gbp
month_data['used-3rd-party-kwh'] += kwh
month_data['used-gbp'] += gbp
month_data['used-kwh'] += kwh
elif source_code == '3rd-party-reverse':
month_data['export-3rd-party-gbp'] += gbp
month_data['export-3rd-party-kwh'] += kwh
month_data['used-3rd-party-gbp'] -= gbp
month_data['used-3rd-party-kwh'] -= kwh
month_data['used-gbp'] -= gbp
month_data['used-kwh'] -= kwh
elif source_code == 'gen':
month_data['import-gen-kwh'] += kwh
if exp_ss is not None:
exp_supplier_contract = exp_ss.supplier_contract
if exp_supplier_contract is not None:
export_vb_function = contract_func(
report_context, exp_supplier_contract,
'virtual_bill')
export_vb_function(exp_ss)
kwh = sum(hh['msp-kwh'] for hh in exp_ss.hh_data)
exp_supplier_bill = exp_ss.supplier_bill
try:
gbp = exp_supplier_bill['net-gbp']
except KeyError:
exp_supplier_bill['problem'] += \
'For the supply ' + imp_ss.mpan_core + \
' the virtual bill ' + \
str(imp_supplier_bill) + \
' from the contract ' + \
imp_supplier_contract.name + \
' does not contain the net-gbp key.'
if source_code in ('net', 'gen-net'):
month_data['export-net-gbp'] += gbp
month_data['export-net-kwh'] += kwh
if source_code == 'gen-net':
month_data['import-gen-kwh'] += kwh
elif source_code == '3rd-party':
month_data['export-3rd-party-gbp'] += gbp
month_data['export-3rd-party-kwh'] += kwh
month_data['used-3rd-party-gbp'] -= gbp
month_data['used-3rd-party-kwh'] -= kwh
month_data['used-gbp'] -= gbp
month_data['used-kwh'] -= kwh
elif source_code == '3rd-party-reverse':
month_data['import-3rd-party-gbp'] += gbp
month_data['import-3rd-party-kwh'] += kwh
month_data['used-3rd-party-gbp'] += gbp
month_data['used-3rd-party-kwh'] += kwh
month_data['used-gbp'] += gbp
month_data['used-kwh'] += kwh
elif source_code == 'gen':
month_data['export-gen-kwh'] += kwh
sss = exp_ss if imp_ss is None else imp_ss
dc_contract = sss.dc_contract
if dc_contract is not None:
sss.contract_func(dc_contract, 'virtual_bill')(sss)
dc_bill = sss.dc_bill
gbp = dc_bill['net-gbp']
mop_contract = sss.mop_contract
if mop_contract is not None:
mop_bill_function = sss.contract_func(
mop_contract, 'virtual_bill')
mop_bill_function(sss)
mop_bill = sss.mop_bill
gbp += mop_bill['net-gbp']
if source_code in ('3rd-party', '3rd-party-reverse'):
month_data['import-3rd-party-gbp'] += gbp
month_data['used-3rd-party-gbp'] += gbp
else:
month_data['import-net-gbp'] += gbp
month_data['used-gbp'] += gbp
generator_type = sss.generator_type_code
if source_code in ('gen', 'gen-net'):
site_gen_types.add(generator_type)
era_category = sss.measurement_type
if CATEGORY_ORDER[site_category] < \
CATEGORY_ORDER[era_category]:
site_category = era_category
era_associates = set()
if mop_contract is not None:
era_associates.update({
s.site.code
for s in era.site_eras if not s.is_physical
})
for bill in sess.query(Bill).filter(
Bill.supply == supply,
Bill.start_date <= sss.finish_date,
Bill.finish_date >= sss.start_date):
bill_start = bill.start_date
bill_finish = bill.finish_date
bill_duration = (
bill_finish - bill_start).total_seconds() + \
(30 * 60)
overlap_duration = (
min(bill_finish, sss.finish_date) -
max(bill_start, sss.start_date)
).total_seconds() + (30 * 60)
overlap_proportion = overlap_duration / \
bill_duration
month_data['billed-import-net-kwh'] += \
overlap_proportion * float(bill.kwh)
month_data['billed-import-net-gbp'] += \
overlap_proportion * float(bill.net)
if imp_ss is None:
imp_supplier_contract_name = None
pc_code = exp_ss.pc_code
else:
if imp_supplier_contract is None:
imp_supplier_contract_name = ''
else:
imp_supplier_contract_name = \
imp_supplier_contract.name
pc_code = imp_ss.pc_code
if exp_ss is None:
exp_supplier_contract_name = None
else:
if exp_supplier_contract is None:
exp_supplier_contract_name = ''
else:
exp_supplier_contract_name = \
exp_supplier_contract.name
out = [
now, imp_mpan_core, imp_supplier_contract_name,
exp_mpan_core, exp_supplier_contract_name,
era_category, source_code, generator_type,
sss.supply_name, sss.msn, pc_code, site.code,
site.name, ','.join(sorted(list(era_associates))),
month_finish] + [
month_data[t] for t in summary_titles] + [None] + \
make_bill_row(title_dict['mop'], mop_bill) + [None] + \
make_bill_row(title_dict['dc'], dc_bill)
if imp_ss is None:
out += [None] * (len(title_dict['imp-supplier']) + 1)
else:
out += [None] + make_bill_row(
title_dict['imp-supplier'], imp_supplier_bill)
if exp_ss is not None:
out += [None] + make_bill_row(
title_dict['exp-supplier'], exp_supplier_bill)
for k, v in month_data.items():
site_month_data[k] += v
era_rows.append(out)
site_rows.append([
now, site.code, site.name, ', '.join(
s.code for s in site.find_linked_sites(
sess, month_start, month_finish)), month_finish,
site_category, ', '.join(sorted(list(site_sources))),
', '.join(sorted(list(site_gen_types)))
] + [site_month_data[k] for k in summary_titles])
sess.rollback()
write_spreadsheet(rf, compression, site_rows, era_rows)
month_start += relativedelta(months=1)
except BadRequest as e:
msg = e.description + traceback.format_exc()
sys.stderr.write(msg + '\n')
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, era_rows)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg + '\n')
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, era_rows)
finally:
if sess is not None:
sess.close()
try:
rf.close()
os.rename(running_name, finished_name)
except BaseException:
msg = traceback.format_exc()
r_name, f_name = chellow.dloads.make_names('error.txt', user)
ef = open(r_name, "w")
ef.write(msg + '\n')
ef.close()