def content(start_date, finish_date, g_contract_id, user):
report_context = {}
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"gas_virtual_bills.csv", user
)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
g_contract = GContract.get_by_id(sess, g_contract_id)
forecast_dt = forecast_date()
month_start = utc_datetime(start_date.year, start_date.month, 1)
month_finish = month_start + relativedelta(months=1) - HH
bill_titles = contract_func(report_context, g_contract, "virtual_bill_titles")()
writer.writerow(
["MPRN", "Site Code", "Site Name", "Account", "From", "To"] + bill_titles
)
while not month_start > finish_date:
period_start = hh_max(start_date, month_start)
period_finish = hh_min(finish_date, month_finish)
for g_era in (
sess.query(GEra)
.distinct()
.filter(
GEra.g_contract == g_contract,
GEra.start_date <= period_finish,
or_(GEra.finish_date == null(), GEra.finish_date >= period_start),
)
):
chunk_start = hh_max(g_era.start_date, period_start)
chunk_finish = hh_min(g_era.finish_date, period_finish)
data_source = GDataSource(
sess,
chunk_start,
chunk_finish,
forecast_dt,
g_era,
report_context,
None,
)
site = (
sess.query(Site)
.join(SiteGEra)
.filter(SiteGEra.g_era == g_era, SiteGEra.is_physical == true())
.one()
)
vals = [
data_source.mprn,
site.code,
site.name,
data_source.account,
hh_format(data_source.start_date),
hh_format(data_source.finish_date),
]
contract_func(report_context, g_contract, "virtual_bill")(data_source)
bill = data_source.bill
for title in bill_titles:
if title in bill:
val = make_val(bill[title])
del bill[title]
else:
val = ""
vals.append(val)
for k in sorted(bill.keys()):
vals.append(k)
vals.append(str(bill[k]))
writer.writerow(vals)
month_start += relativedelta(months=1)
month_finish = month_start + relativedelta(months=1) - HH
except BadRequest as e:
writer.writerow(["Problem: " + e.description])
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, contract_id, user):
caches = {}
sess = f = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'virtual_bills.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
contract = Contract.get_supplier_by_id(sess, contract_id)
forecast_date = chellow.computer.forecast_date()
month_start = Datetime(
start_date.year, start_date.month, 1, tzinfo=pytz.utc)
month_finish = month_start + relativedelta(months=1) - HH
bill_titles = contract_func(
caches, contract, 'virtual_bill_titles', None)()
writer.writerow(
['MPAN Core', 'Site Code', 'Site Name', 'Account', 'From', 'To'] +
bill_titles)
while not month_start > finish_date:
period_start = start_date \
if month_start < start_date else month_start
if month_finish > finish_date:
period_finish = finish_date
else:
period_finish = month_finish
for era in sess.query(Era).distinct().filter(
or_(
Era.imp_supplier_contract_id == contract.id,
Era.exp_supplier_contract_id == contract.id),
Era.start_date <= period_finish,
or_(
Era.finish_date == null(),
Era.finish_date >= period_start)):
era_start = era.start_date
if period_start < era_start:
chunk_start = era_start
else:
chunk_start = period_start
era_finish = era.finish_date
if hh_after(period_finish, era_finish):
chunk_finish = era_finish
else:
chunk_finish = period_finish
polarities = []
if era.imp_supplier_contract == contract:
polarities.append(True)
if era.exp_supplier_contract == contract:
polarities.append(False)
for polarity in polarities:
vals = []
data_source = SupplySource(
sess, chunk_start, chunk_finish, forecast_date, era,
polarity, None, caches)
site = sess.query(Site).join(SiteEra).filter(
SiteEra.era == era,
SiteEra.is_physical == true()).one()
vals = [
data_source.mpan_core, site.code, site.name,
data_source.supplier_account,
hh_format(data_source.start_date),
hh_format(data_source.finish_date)]
contract_func(
caches, contract, 'virtual_bill', None)(data_source)
bill = data_source.supplier_bill
for title in bill_titles:
if title in bill:
val = str(bill[title])
del bill[title]
else:
val = ''
vals.append(val)
for k in sorted(bill.keys()):
vals.append(k)
vals.append(str(bill[k]))
writer.writerow(vals)
month_start += relativedelta(months=1)
month_finish = month_start + relativedelta(months=1) - HH
except BadRequest as e:
writer.writerow([e.description])
except:
writer.writerow([traceback.format_exc()])
finally:
if sess is not None:
sess.close()
f.close()
os.rename(running_name, finished_name)
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 _process_site(
sess,
report_context,
forecast_from,
start_date,
finish_date,
site,
site_deltas,
supply_id,
era_maps,
now,
summary_titles,
title_dict,
era_rows,
site_rows,
):
calcs, displaced_era, site_gen_types = _make_calcs(
sess,
site,
start_date,
finish_date,
supply_id,
site_deltas,
forecast_from,
report_context,
era_maps,
)
site_month_data = defaultdict(int)
site_ds = chellow.computer.SiteSource(
sess,
site,
start_date,
finish_date,
forecast_from,
report_context,
displaced_era,
deltas=site_deltas,
)
bill_ids = set()
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["billed-supplier-import-net-gbp"] = None
month_data["billed-dc-import-net-gbp"] = None
month_data["billed-mop-import-net-gbp"] = None
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(
f"The supplier contract {disp_supplier_contract.name} "
f" 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"] += (
f"For the supply {site_ds.mpan_core} the virtual bill "
f"{disp_supplier_bill} from the contract {disp_supplier_contract.name} "
f" does not contain the net-gbp key.")
month_data["used-gbp"] = month_data["displaced-gbp"] = gbp
out = ([
now,
None,
disp_supplier_contract.name,
None,
None,
displaced_era.meter_category,
"displaced",
None,
None,
None,
None,
site.code,
site.name,
"",
finish_date,
] + [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([make_val(v) for v in out])
for k, v in month_data.items():
if v is not None:
site_month_data[k] += v
site_category = None
site_sources = set()
normal_reads = set()
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
month_data["billed-supplier-import-net-gbp"] = 0
month_data["billed-dc-import-net-gbp"] = 0
month_data["billed-mop-import-net-gbp"] = 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(
f"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"] += (
f"For the supply {imp_ss.mpan_core} the virtual bill " +
f"{imp_supplier_bill} from the contract " +
f"{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 sss.era.site_eras if not s.is_physical})
for bill in sess.query(Bill).filter(
Bill.supply == supply,
Bill.start_date <= finish_date,
Bill.finish_date >= start_date,
):
bill_ids.add(bill.id)
bill_role_code = bill.batch.contract.market_role.code
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, finish_date) - max(
bill_start, start_date)).total_seconds() + (30 * 60)
proportion = overlap_duration / bill_duration
month_data["billed-import-net-kwh"] += proportion * float(
bill.kwh)
bill_prop_gbp = proportion * float(bill.net)
month_data["billed-import-net-gbp"] += bill_prop_gbp
if bill_role_code == "X":
month_data[
"billed-supplier-import-net-gbp"] += bill_prop_gbp
elif bill_role_code == "C":
month_data["billed-dc-import-net-gbp"] += bill_prop_gbp
elif bill_role_code == "M":
month_data["billed-mop-import-net-gbp"] += bill_prop_gbp
else:
raise BadRequest("Role code not recognized.")
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))),
finish_date,
] + [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)
for n in imp_ss.normal_reads:
normal_reads.add((imp_mpan_core, n))
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([make_val(v) for v in out])
for bill in (sess.query(Bill).join(Supply).join(
Supply.eras).join(SiteEra).filter(
SiteEra.site == site,
SiteEra.is_physical == true(),
Bill.start_date <= finish_date,
Bill.finish_date >= start_date,
)):
if bill.id in bill_ids:
continue
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
month_data["billed-supplier-import-net-gbp"] = 0
month_data["billed-dc-import-net-gbp"] = 0
month_data["billed-mop-import-net-gbp"] = 0
for bill in sess.query(Bill).filter(
Bill.supply == bill.supply,
Bill.start_date <= finish_date,
Bill.finish_date >= start_date,
):
bill_ids.add(bill.id)
bill_role_code = bill.batch.contract.market_role.code
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, finish_date) - max(
bill_start, start_date)).total_seconds() + (30 * 60)
proportion = overlap_duration / bill_duration
month_data["billed-import-net-kwh"] += proportion * float(bill.kwh)
bill_prop_gbp = proportion * float(bill.net)
month_data["billed-import-net-gbp"] += bill_prop_gbp
if bill_role_code == "X":
month_data["billed-supplier-import-net-gbp"] += bill_prop_gbp
site_month_data[
"billed-supplier-import-net-gbp"] += bill_prop_gbp
elif bill_role_code == "C":
month_data["billed-dc-import-net-gbp"] += bill_prop_gbp
site_month_data["billed-dc-import-net-gbp"] += bill_prop_gbp
elif bill_role_code == "M":
month_data["billed-mop-import-net-gbp"] += bill_prop_gbp
site_month_data["billed-mop-import-net-gbp"] += bill_prop_gbp
else:
raise BadRequest("Role code not recognized.")
era = sess.execute(
select(Era).filter(Era.supply == bill.supply).order_by(
Era.start_date.desc())).first()[0]
imp_supplier_contract = era.imp_supplier_contract
exp_supplier_contract = era.exp_supplier_contract
out = [
now,
era.imp_mpan_core,
None
if imp_supplier_contract is None else imp_supplier_contract.name,
era.exp_mpan_core,
None
if exp_supplier_contract is None else exp_supplier_contract.name,
era.meter_category,
era.supply.source.code,
None,
era.supply.name,
era.msn,
era.pc.code,
site.code,
site.name,
None,
finish_date,
] + [month_data[t] for t in summary_titles]
era_rows.append([make_val(v) for v in out])
site_row = [
now,
site.code,
site.name,
", ".join(
s.code
for s in site.find_linked_sites(sess, start_date, finish_date)),
finish_date,
site_category,
", ".join(sorted(list(site_sources))),
", ".join(sorted(list(site_gen_types))),
] + [site_month_data[k] for k in summary_titles]
site_rows.append([make_val(v) for v in site_row])
sess.rollback()
return normal_reads
def content(g_supply_id, start_date, finish_date, user):
caches = {}
try:
sess = Session()
g_supply = GSupply.get_by_id(sess, g_supply_id)
forecast_date = chellow.computer.forecast_date()
prev_titles = None
running_name, finished_name = chellow.dloads.make_names(
'g_supply_virtual_bills_hh_' + str(g_supply_id) + '.csv', user)
f = open(running_name, mode='w', newline='')
w = csv.writer(f, lineterminator='\n')
for hh_start in hh_range(caches, start_date, finish_date):
g_era = sess.query(GEra).filter(
GEra.g_supply == g_supply, GEra.start_date <= hh_start,
or_(GEra.finish_date == null(),
GEra.finish_date >= hh_start)).one()
site = sess.query(Site).join(SiteGEra).filter(
SiteGEra.g_era == g_era, SiteGEra.is_physical == true()).one()
ds = GDataSource(sess, hh_start, hh_start, forecast_date, g_era,
caches, None)
titles = [
'MPRN', 'Site Code', 'Site Name', 'Account', 'HH Start', ''
]
output_line = [
ds.mprn, site.code, site.name, ds.account,
hh_format(ds.start_date), ''
]
contract = g_era.g_contract
output_line.append('')
contract_titles = contract_func(caches, contract,
'virtual_bill_titles')()
titles.append('')
titles.extend(contract_titles)
contract_func(caches, contract, 'virtual_bill')(ds)
bill = ds.bill
for title in contract_titles:
output_line.append(csv_make_val(bill.get(title, '')))
if title in bill:
del bill[title]
for k in sorted(bill.keys()):
output_line.extend([k, csv_make_val(bill[k])])
if titles != prev_titles:
prev_titles = titles
w.writerow(titles)
w.writerow(output_line)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg)
w.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(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(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()
def create_csv(f, sess, start_date, finish_date, contract_id):
caches = {}
writer = csv.writer(f, lineterminator="\n")
contract = Contract.get_supplier_by_id(sess, contract_id)
forecast_date = chellow.computer.forecast_date()
start_date_ct, finish_date_ct = to_ct(start_date), to_ct(finish_date)
month_pairs = c_months_u(
start_year=start_date_ct.year,
start_month=start_date_ct.month,
finish_year=finish_date_ct.year,
finish_month=finish_date_ct.month,
)
bill_titles = contract_func(caches, contract, "virtual_bill_titles")()
for tpr in (
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),
or_(
Era.imp_supplier_contract == contract,
Era.exp_supplier_contract == contract,
),
)
.order_by(Tpr.code)
.distinct()
):
for suffix in ("-kwh", "-rate", "-gbp"):
bill_titles.append(tpr.code + suffix)
writer.writerow(
["MPAN Core", "Site Code", "Site Name", "Account", "From", "To"] + bill_titles
)
vb_func = contract_func(caches, contract, "virtual_bill")
for month_start, month_finish in month_pairs:
period_start = hh_max(start_date, month_start)
period_finish = hh_min(finish_date, month_finish)
for era in (
sess.query(Era)
.filter(
or_(
Era.imp_supplier_contract == contract,
Era.exp_supplier_contract == contract,
),
Era.start_date <= period_finish,
or_(Era.finish_date == null(), Era.finish_date >= period_start),
)
.order_by(Era.imp_mpan_core)
):
try:
vals = _process_era(
sess,
caches,
vb_func,
forecast_date,
bill_titles,
contract,
period_start,
period_finish,
era,
)
writer.writerow(csv_make_val(v) for v in vals)
except BadRequest as e:
raise BadRequest(
f"Problem with {chellow.utils.url_root}eras/{era.id}/edit "
f"{e.description}"
)