def do_get(sess):
batch_id = bill_id = contract_id = start_date = finish_date = None
if "mpan_cores" in request.values:
mpan_cores = req_str("mpan_cores").splitlines()
else:
mpan_cores = []
fname_additional = ""
if "batch_id" in request.values:
batch_id = req_int("batch_id")
batch = Batch.get_by_id(sess, batch_id)
fname_additional = f"_batch_{batch.reference}"
elif "bill_id" in request.values:
bill_id = req_int("bill_id")
bill = Bill.get_by_id(sess, bill_id)
fname_additional = "bill_" + str(bill.id)
elif "contract_id" in request.values:
contract_id = req_int("contract_id")
contract = Contract.get_by_id(sess, contract_id)
start_date = req_date("start_date")
finish_date = req_date("finish_date")
s = ["contract", str(contract.id)]
for dt in (start_date, finish_date):
s.append(hh_format(dt).replace(" ", "T").replace(":", ""))
fname_additional = "_".join(s)
else:
raise BadRequest(
"The bill check needs a batch_id, a bill_id or a start_date "
"and finish_date.")
args = (
batch_id,
bill_id,
contract_id,
start_date,
finish_date,
g.user,
mpan_cores,
fname_additional,
)
print(args)
threading.Thread(target=content, args=args).start()
return chellow_redirect("/downloads", 303)
def chellow_context_processor():
global_alerts = []
for task in chellow.hh_importer.tasks.values():
if task.is_error:
try:
contract = Contract.get_by_id(g.sess, task.contract_id)
global_alerts.append(
f"There's a problem with the automatic HH data "
f"importer for contract '{contract.name}'.")
except NotFound:
pass
for importer in (
chellow.bsuos.bsuos_importer,
chellow.g_cv.g_cv_importer,
chellow.bmarketidx.bmarketidx_importer,
):
if importer is not None and importer.global_alert is not None:
global_alerts.append(importer.global_alert)
return {"current_user": g.user, "global_alerts": global_alerts}
def content(
batch_id,
bill_id,
contract_id,
start_date,
finish_date,
user,
mpan_cores,
fname_additional,
):
caches = {}
tmp_file = sess = supply_id = None
forecast_date = to_utc(Datetime.max)
try:
running_name, finished_name = chellow.dloads.make_names(
"bill_check_" + fname_additional + ".csv", user)
tmp_file = open(running_name, mode="w", newline="")
writer = csv.writer(tmp_file, lineterminator="\n")
sess = Session()
report_run = ReportRun("bill_check", user, fname_additional)
sess.add(report_run)
bills = (sess.query(Bill).order_by(
Bill.supply_id, Bill.reference).options(
joinedload(Bill.supply),
subqueryload(Bill.reads).joinedload(RegisterRead.present_type),
subqueryload(Bill.reads).joinedload(
RegisterRead.previous_type),
joinedload(Bill.batch),
))
if len(mpan_cores) > 0:
mpan_cores = list(map(parse_mpan_core, mpan_cores))
supply_ids = [
i[0] for i in sess.query(Era.supply_id).filter(
or_(
Era.imp_mpan_core.in_(mpan_cores),
Era.exp_mpan_core.in_(mpan_cores),
)).distinct()
]
bills = bills.join(Supply).filter(Supply.id.in_(supply_ids))
if batch_id is not None:
batch = Batch.get_by_id(sess, batch_id)
bills = bills.filter(Bill.batch == batch)
contract = batch.contract
elif bill_id is not None:
bill = Bill.get_by_id(sess, bill_id)
bills = bills.filter(Bill.id == bill.id)
contract = bill.batch.contract
elif contract_id is not None:
contract = Contract.get_by_id(sess, contract_id)
bills = bills.join(Batch).filter(
Batch.contract == contract,
Bill.start_date <= finish_date,
Bill.finish_date >= start_date,
)
vbf = chellow.computer.contract_func(caches, contract, "virtual_bill")
if vbf is None:
raise BadRequest("The contract " + contract.name +
" doesn't have a function virtual_bill.")
virtual_bill_titles_func = chellow.computer.contract_func(
caches, contract, "virtual_bill_titles")
if virtual_bill_titles_func is None:
raise BadRequest("The contract " + contract.name +
" doesn't have a function virtual_bill_titles.")
virtual_bill_titles = virtual_bill_titles_func()
titles = [
"batch",
"bill-reference",
"bill-type",
"bill-kwh",
"bill-net-gbp",
"bill-vat-gbp",
"bill-start-date",
"bill-finish-date",
"imp-mpan-core",
"exp-mpan-core",
"site-code",
"site-name",
"covered-from",
"covered-to",
"covered-bills",
"metered-kwh",
]
for t in virtual_bill_titles:
titles.append("covered-" + t)
titles.append("virtual-" + t)
if t.endswith("-gbp"):
titles.append("difference-" + t)
writer.writerow(titles)
bill_map = defaultdict(set, {})
for bill in bills:
bill_map[bill.supply.id].add(bill.id)
for supply_id in bill_map.keys():
_process_supply(
sess,
caches,
supply_id,
bill_map,
forecast_date,
contract,
vbf,
virtual_bill_titles,
writer,
titles,
report_run,
)
report_run.update("finished")
sess.commit()
except BadRequest as e:
if report_run is not None:
report_run.update("problem")
if supply_id is None:
prefix = "Problem: "
else:
prefix = "Problem with supply " + str(supply_id) + ":"
tmp_file.write(prefix + e.description)
except BaseException:
if report_run is not None:
report_run.update("interrupted")
if supply_id is None:
prefix = "Problem: "
else:
prefix = "Problem with supply " + str(supply_id) + ":"
msg = traceback.format_exc()
sys.stderr.write(msg + "\n")
tmp_file.write(prefix + msg)
finally:
if sess is not None:
sess.close()
tmp_file.close()
os.rename(running_name, finished_name)
def content(batch_id, bill_id, contract_id, start_date, finish_date, user):
caches = {}
tmp_file = sess = bill = None
forecast_date = to_utc(Datetime.max)
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'bill_check.csv', user)
tmp_file = open(running_name, mode='w', newline='')
writer = csv.writer(tmp_file, lineterminator='\n')
bills = sess.query(Bill).order_by(
Bill.supply_id, Bill.reference).options(
joinedload(Bill.supply),
subqueryload(Bill.reads).joinedload(RegisterRead.present_type),
subqueryload(Bill.reads).joinedload(RegisterRead.previous_type),
joinedload(Bill.batch))
if batch_id is not None:
batch = Batch.get_by_id(sess, batch_id)
bills = bills.filter(Bill.batch == batch)
contract = batch.contract
elif bill_id is not None:
bill = Bill.get_by_id(sess, bill_id)
bills = bills.filter(Bill.id == bill.id)
contract = bill.batch.contract
elif contract_id is not None:
contract = Contract.get_by_id(sess, contract_id)
bills = bills.join(Batch).filter(
Batch.contract == contract, Bill.start_date <= finish_date,
Bill.finish_date >= start_date)
market_role_code = contract.market_role.code
vbf = chellow.computer.contract_func(caches, contract, 'virtual_bill')
if vbf is None:
raise BadRequest(
'The contract ' + contract.name +
" doesn't have a function virtual_bill.")
virtual_bill_titles_func = chellow.computer.contract_func(
caches, contract, 'virtual_bill_titles')
if virtual_bill_titles_func is None:
raise BadRequest(
'The contract ' + contract.name +
" doesn't have a function virtual_bill_titles.")
virtual_bill_titles = virtual_bill_titles_func()
titles = [
'batch', 'bill-reference', 'bill-type', 'bill-kwh', 'bill-net-gbp',
'bill-vat-gbp', 'bill-start-date', 'bill-finish-date',
'imp-mpan-core', 'exp-mpan-core', 'site-code', 'site-name',
'covered-from', 'covered-to', 'covered-bills', 'metered-kwh']
for t in virtual_bill_titles:
titles.append('covered-' + t)
titles.append('virtual-' + t)
if t.endswith('-gbp'):
titles.append('difference-' + t)
writer.writerow(titles)
bill_map = defaultdict(set, {})
for bill in bills:
bill_map[bill.supply.id].add(bill.id)
for supply_id, bill_ids in bill_map.items():
gaps = {}
data_sources = {}
while len(bill_ids) > 0:
bill_id = list(sorted(bill_ids))[0]
bill_ids.remove(bill_id)
bill = sess.query(Bill).filter(Bill.id == bill_id).options(
joinedload(Bill.batch),
joinedload(Bill.bill_type),
joinedload(Bill.reads),
joinedload(Bill.supply),
joinedload(Bill.reads).joinedload(
RegisterRead.present_type),
joinedload(Bill.reads).joinedload(
RegisterRead.previous_type)).one()
virtual_bill = {'problem': ''}
supply = bill.supply
read_dict = {}
for read in bill.reads:
gen_start = read.present_date.replace(hour=0).replace(
minute=0)
gen_finish = gen_start + relativedelta(days=1) - HH
msn_match = False
read_msn = read.msn
for read_era in supply.find_eras(
sess, gen_start, gen_finish):
if read_msn == read_era.msn:
msn_match = True
break
if not msn_match:
virtual_bill['problem'] += "The MSN " + read_msn + \
" of the register read " + str(read.id) + \
" doesn't match the MSN of the era."
for dt, typ in [
(read.present_date, read.present_type),
(read.previous_date, read.previous_type)]:
key = str(dt) + "-" + read.msn
try:
if typ != read_dict[key]:
virtual_bill['problem'] += " Reads taken " + \
"on " + str(dt) + \
" have differing read types."
except KeyError:
read_dict[key] = typ
bill_start = bill.start_date
bill_finish = bill.finish_date
covered_start = bill_start
covered_finish = bill_finish
covered_bdown = {'sum-msp-kwh': 0, 'net-gbp': 0, 'vat-gbp': 0}
vb_elems = set()
enlarged = True
while enlarged:
enlarged = False
covered_elems = find_elements(bill)
covered_bills = OrderedDict(
(b.id, b) for b in sess.query(Bill).join(Batch).
join(Contract).join(MarketRole).filter(
Bill.supply == supply,
Bill.start_date <= covered_finish,
Bill.finish_date >= covered_start,
MarketRole.code == market_role_code).order_by(
Bill.start_date, Bill.issue_date))
while True:
to_del = None
for a, b in combinations(covered_bills.values(), 2):
if all(
(
a.start_date == b.start_date,
a.finish_date == b.finish_date,
a.kwh == -1 * b.kwh,
a.net == -1 * b.net,
a.vat == -1 * b.vat,
a.gross == -1 * b.gross)):
to_del = (a.id, b.id)
break
if to_del is None:
break
else:
for k in to_del:
del covered_bills[k]
for k, covered_bill in tuple(covered_bills.items()):
elems = find_elements(covered_bill)
if elems.isdisjoint(covered_elems):
if k != bill.id:
del covered_bills[k]
continue
else:
covered_elems.update(elems)
if covered_bill.start_date < covered_start:
covered_start = covered_bill.start_date
enlarged = True
break
if covered_bill.finish_date > covered_finish:
covered_finish = covered_bill.finish_date
enlarged = True
break
if len(covered_bills) == 0:
continue
primary_covered_bill = None
for covered_bill in covered_bills.values():
if covered_bill.id in bill_ids:
bill_ids.remove(covered_bill.id)
covered_bdown['net-gbp'] += float(covered_bill.net)
covered_bdown['vat-gbp'] += float(covered_bill.vat)
covered_bdown['sum-msp-kwh'] += float(covered_bill.kwh)
covered_rates = defaultdict(set)
for k, v in loads(covered_bill.breakdown).items():
if k in ('raw_lines', 'raw-lines'):
continue
if isinstance(v, list):
covered_rates[k].update(set(v))
else:
if isinstance(v, Decimal):
v = float(v)
try:
covered_bdown[k] += v
except KeyError:
covered_bdown[k] = v
except TypeError as detail:
raise BadRequest(
"For key " + str(k) + " in " + str(
[
b.id for b in
covered_bills.values()
]) + " the value " + str(v) +
" can't be added to the existing value " +
str(covered_bdown[k]) + ". " + str(detail))
if k.endswith('-gbp'):
elem = k[:-4]
covered_elems.add(elem)
add_gap(
caches, gaps, elem,
covered_bill.start_date,
covered_bill.finish_date, False, v)
for k, v in covered_rates.items():
covered_bdown[k] = v.pop() if len(v) == 1 else None
if primary_covered_bill is None or (
(
covered_bill.finish_date -
covered_bill.start_date) > (
primary_covered_bill.finish_date -
primary_covered_bill.start_date)):
primary_covered_bill = covered_bill
metered_kwh = 0
for era in sess.query(Era).filter(
Era.supply == supply, Era.start_date <= covered_finish,
or_(
Era.finish_date == null(),
Era.finish_date >= covered_start)
).distinct().options(
joinedload(Era.channels),
joinedload(Era.cop),
joinedload(Era.dc_contract),
joinedload(Era.exp_llfc),
joinedload(Era.exp_llfc).joinedload(
Llfc.voltage_level),
joinedload(Era.exp_supplier_contract),
joinedload(Era.imp_llfc),
joinedload(Era.imp_llfc).joinedload(
Llfc.voltage_level),
joinedload(Era.imp_supplier_contract),
joinedload(Era.mop_contract),
joinedload(Era.mtc).joinedload(Mtc.meter_type),
joinedload(Era.pc),
joinedload(Era.supply).joinedload(Supply.dno),
joinedload(Era.supply).joinedload(Supply.gsp_group),
joinedload(Era.supply).joinedload(Supply.source)):
chunk_start = hh_max(covered_start, era.start_date)
chunk_finish = hh_min(covered_finish, era.finish_date)
if contract not in (
era.mop_contract, era.dc_contract,
era.imp_supplier_contract,
era.exp_supplier_contract):
virtual_bill['problem'] += ''.join(
(
"From ", hh_format(chunk_start), " to ",
hh_format(chunk_finish), " the contract of ",
"the era doesn't match the contract of the ",
"bill."))
continue
if contract.market_role.code == 'X':
polarity = contract != era.exp_supplier_contract
else:
polarity = era.imp_supplier_contract is not None
'''
pairs = []
last_finish = chunk_start - HH
for hd in chellow.computer.datum_range(
sess, caches, 0, chunk_start, chunk_finish):
if hd['utc-is-month-end'] or hd['ct-is-month-end']:
end_date = hd['start-date']
pairs.append((last_finish + HH, end_date))
last_finish = end_date
if hd['start-date'] > last_finish:
pairs.append((last_finish + HH, hd['start-date']))
for ss_start, ss_finish in pairs:
'''
try:
ds_key = (
chunk_start, chunk_finish, forecast_date, era.id,
polarity, primary_covered_bill.id)
data_source = data_sources[ds_key]
except KeyError:
data_source = data_sources[ds_key] = \
chellow.computer.SupplySource(
sess, chunk_start, chunk_finish, forecast_date,
era, polarity, caches, primary_covered_bill)
vbf(data_source)
if data_source.measurement_type == 'hh':
metered_kwh += sum(
h['msp-kwh'] for h in data_source.hh_data)
else:
ds = chellow.computer.SupplySource(
sess, chunk_start, chunk_finish, forecast_date,
era, polarity, caches)
metered_kwh += sum(
h['msp-kwh'] for h in ds.hh_data)
if market_role_code == 'X':
vb = data_source.supplier_bill
elif market_role_code == 'C':
vb = data_source.dc_bill
elif market_role_code == 'M':
vb = data_source.mop_bill
else:
raise BadRequest("Odd market role.")
for k, v in vb.items():
try:
if isinstance(v, set):
virtual_bill[k].update(v)
else:
virtual_bill[k] += v
except KeyError:
virtual_bill[k] = v
except TypeError as detail:
raise BadRequest(
"For key " + str(k) + " and value " +
str(v) + ". " + str(detail))
if all((k.endswith('-gbp'), k != 'net-gbp', v != 0)):
add_gap(
caches, gaps, k[:-4], chunk_start,
chunk_finish, True, v)
for k in virtual_bill.keys():
if k.endswith('-gbp'):
vb_elems.add(k[:-4])
long_map = {}
vb_keys = set(virtual_bill.keys())
for elem in sorted(vb_elems, key=len, reverse=True):
els = long_map[elem] = set()
for k in tuple(vb_keys):
if k.startswith(elem + '-'):
els.add(k)
vb_keys.remove(k)
for elem in vb_elems.difference(covered_elems):
for k in long_map[elem]:
del virtual_bill[k]
try:
del virtual_bill['net-gbp']
except KeyError:
pass
virtual_bill['net-gbp'] = sum(
v for k, v in virtual_bill.items() if k.endswith('-gbp'))
era = supply.find_era_at(sess, bill_finish)
if era is None:
imp_mpan_core = exp_mpan_core = None
site_code = site_name = None
virtual_bill['problem'] += \
"This bill finishes before or after the supply. "
else:
imp_mpan_core = era.imp_mpan_core
exp_mpan_core = era.exp_mpan_core
site = sess.query(Site).join(SiteEra).filter(
SiteEra.is_physical == true(),
SiteEra.era == era).one()
site_code = site.code
site_name = site.name
# Find bill to use for header data
if bill.id not in covered_bills:
for cbill in covered_bills.values():
if bill.batch == cbill.batch:
bill = cbill
values = [
bill.batch.reference, bill.reference, bill.bill_type.code,
bill.kwh, bill.net, bill.vat, hh_format(bill_start),
hh_format(bill_finish), imp_mpan_core, exp_mpan_core,
site_code, site_name, hh_format(covered_start),
hh_format(covered_finish), ':'.join(
str(i).replace(',', '') for i in covered_bills.keys()),
metered_kwh]
for title in virtual_bill_titles:
try:
cov_val = covered_bdown[title]
values.append(cov_val)
del covered_bdown[title]
except KeyError:
cov_val = None
values.append('')
try:
virt_val = csv_make_val(virtual_bill[title])
values.append(virt_val)
del virtual_bill[title]
except KeyError:
virt_val = 0
values.append('')
if title.endswith('-gbp'):
if isinstance(virt_val, (int, float, Decimal)):
if isinstance(cov_val, (int, float, Decimal)):
values.append(float(cov_val) - float(virt_val))
else:
values.append(0 - float(virt_val))
else:
values.append('')
for title in sorted(virtual_bill.keys()):
virt_val = csv_make_val(virtual_bill[title])
values += ['virtual-' + title, virt_val]
if title in covered_bdown:
values += ['covered-' + title, covered_bdown[title]]
else:
values += ['', '']
writer.writerow(values)
for bill in sess.query(Bill).filter(
Bill.supply == supply,
Bill.start_date <= covered_finish,
Bill.finish_date >= covered_start):
for k, v in loads(bill.breakdown).items():
if k.endswith('-gbp'):
add_gap(
caches, gaps, k[:-4], bill.start_date,
bill.finish_date, False, v)
# Avoid long-running transactions
sess.rollback()
clumps = []
for element, elgap in sorted(gaps.items()):
for start_date, hhgap in sorted(elgap.items()):
if hhgap['has_virtual'] and not hhgap['has_covered']:
if len(clumps) == 0 or not all(
(
clumps[-1]['element'] == element,
clumps[-1]['finish_date'] + HH ==
start_date)):
clumps.append(
{
'element': element,
'start_date': start_date,
'finish_date': start_date,
'gbp': hhgap['gbp']})
else:
clumps[-1]['finish_date'] = start_date
for i, clump in enumerate(clumps):
vals = dict((title, '') for title in titles)
vals['covered-problem'] = '_'.join(
(
'missing', clump['element'], 'supplyid',
str(supply.id), 'from',
hh_format(clump['start_date'])))
vals['imp-mpan-core'] = imp_mpan_core
vals['exp-mpan-core'] = exp_mpan_core
vals['batch'] = 'missing_bill'
vals['bill-start-date'] = hh_format(clump['start_date'])
vals['bill-finish-date'] = hh_format(clump['finish_date'])
vals['difference-net-gbp'] = clump['gbp']
writer.writerow(vals[title] for title in titles)
# Avoid long-running transactions
sess.rollback()
except BadRequest as e:
if bill is None:
prefix = "Problem: "
else:
prefix = "Problem with bill " + str(bill.id) + ':'
tmp_file.write(prefix + e.description)
except BaseException:
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)
