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 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, user):
caches = {}
tmp_file = sess = None
forecast_date = Datetime.max.replace(tzinfo=pytz.utc)
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')
if batch_id is not None:
batch = Batch.get_by_id(sess, batch_id)
bills = sess.query(Bill).filter(
Bill.batch_id == batch.id).order_by(Bill.reference)
elif bill_id is not None:
bill = Bill.get_by_id(sess, bill_id)
bills = sess.query(Bill).filter(Bill.id == bill.id)
batch = bill.batch
contract = batch.contract
market_role_code = contract.market_role.code
vbf = chellow.computer.contract_func(
caches, contract, 'virtual_bill', None)
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', None)
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',
'bill-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)
for bill in bills:
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:
problem += "The MSN " + read_msn + \
" of the register read " + str(read.id) + \
" doesn't match the MSN of the era."
for dt, type in [
(read.present_date, read.present_type),
(read.previous_date, read.previous_type)]:
key = str(dt) + "-" + read.msn
try:
if type != read_dict[key]:
problem += " Reads taken on " + str(dt) + \
" have differing read types."
except KeyError:
read_dict[key] = type
bill_start = bill.start_date
bill_finish = bill.finish_date
era = supply.find_era_at(sess, bill.finish_date)
if era is None:
raise BadRequest(
"Extraordinary! There isn't an era for the bill " +
str(bill.id) + ".")
values = [
batch.reference, bill.reference, bill.bill_type.code,
bill.kwh, bill.net, bill.vat, hh_format(bill_start),
hh_format(bill_finish), era.imp_mpan_core]
covered_start = bill_start
covered_finish = bill_finish
covered_bill_ids = []
covered_bdown = {'sum-msp-kwh': 0, 'net-gbp': 0, 'vat-gbp': 0}
covered_primary_bill = None
enlarged = True
while enlarged:
enlarged = False
covered_bills = []
cand_bills = dict(
(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.issue_date.desc(), Bill.start_date))
while True:
to_del = None
for a, b in combinations(cand_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 cand_bills[k]
for cand_bill_id in sorted(cand_bills.keys()):
cand_bill = cand_bills[cand_bill_id]
if covered_primary_bill is None and \
len(cand_bill.reads) > 0:
covered_primary_bill = cand_bill
if cand_bill.start_date < covered_start:
covered_start = cand_bill.start_date
enlarged = True
break
if cand_bill.finish_date > covered_finish:
covered_finish = cand_bill.finish_date
enlarged = True
break
covered_bills.append(cand_bill)
for covered_bill in covered_bills:
covered_bill_ids.append(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)
if len(covered_bill.breakdown) > 0:
covered_rates = collections.defaultdict(set)
for k, v in eval(covered_bill.breakdown, {}).items():
if k.endswith('rate'):
covered_rates[k].add(v)
elif k != 'raw-lines':
try:
covered_bdown[k] += v
except KeyError:
covered_bdown[k] = v
except TypeError as detail:
raise BadRequest(
"For key " + str(k) + " the value " +
str(v) +
" can't be added to the existing value " +
str(covered_bdown[k]) + ". " + str(detail))
for k, v in covered_rates.items():
covered_bdown[k] = v.pop() if len(v) == 1 else None
virtual_bill = {}
metered_kwh = 0
for era in sess.query(Era).filter(
Era.supply_id == supply.id, Era.imp_mpan_core != null(),
Era.start_date <= covered_finish,
or_(
Era.finish_date == null(),
Era.finish_date >= covered_start)).distinct():
site = sess.query(Site).join(SiteEra).filter(
SiteEra.is_physical == true(),
SiteEra.era_id == era.id).one()
if covered_start > era.start_date:
chunk_start = covered_start
else:
chunk_start = era.start_date
if hh_before(covered_finish, era.finish_date):
chunk_finish = covered_finish
else:
chunk_finish = era.finish_date
data_source = chellow.computer.SupplySource(
sess, chunk_start, chunk_finish, forecast_date, era, True,
None, caches, covered_primary_bill)
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,
True, None, caches)
metered_kwh += sum(h['msp-kwh'] for h in ds.hh_data)
vbf(data_source)
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():
if k.endswith('-rate'):
if k not in virtual_bill:
virtual_bill[k] = set()
virtual_bill[k].add(v)
else:
try:
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))
values += [
site.code, site.name, hh_format(covered_start),
hh_format(covered_finish),
','.join(str(id).replace(',', '') for id in covered_bill_ids),
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 = virtual_bill[title]
if isinstance(virt_val, set):
virt_val = ', '.join(str(v) for v in virt_val)
elif isinstance(virt_val, Datetime):
virt_val = hh_format(virt_val)
values.append(virt_val)
del virtual_bill[title]
except KeyError:
virt_val = None
values.append('')
if title.endswith('-gbp'):
if all(isinstance(val, (int, float)) for val in [
cov_val, virt_val]):
values.append(cov_val - virt_val)
else:
values.append('')
for title in sorted(virtual_bill.keys()):
virt_val = virtual_bill[title]
if isinstance(virt_val, set):
virt_val = ', '.join(str(v) for v in virt_val)
elif isinstance(virt_val, Datetime):
virt_val = hh_format(virt_val)
values += ['virtual-' + title, virt_val]
if title in covered_bdown:
values += ['covered-' + title, covered_bdown[title]]
else:
values += ['', '']
writer.writerow(values)
except BadRequest as e:
tmp_file.write("Problem: " + e.description)
except:
msg = traceback.format_exc()
sys.stderr.write(msg + '\n')
tmp_file.write("Problem " + msg)
finally:
if sess is not None:
sess.close()
tmp_file.close()
os.rename(running_name, finished_name)
def run(self):
sess = None
try:
sess = Session()
batch = Batch.get_by_id(sess, self.batch_id)
bill_types = keydefaultdict(
lambda k: BillType.get_by_code(sess, k))
tprs = keydefaultdict(lambda k: None
if k is None else Tpr.get_by_code(sess, k))
read_types = keydefaultdict(
lambda k: ReadType.get_by_code(sess, k))
for bf in (sess.query(BatchFile).filter(
BatchFile.batch == batch).order_by(
BatchFile.upload_timestamp)):
self.parser = _process_batch_file(sess, bf, self._log)
for self.bill_num, raw_bill in enumerate(
self.parser.make_raw_bills()):
if "error" in raw_bill:
self.failed_bills.append(raw_bill)
else:
try:
mpan_core = raw_bill["mpan_core"]
supply = Supply.get_by_mpan_core(sess, mpan_core)
with sess.begin_nested():
bill = batch.insert_bill(
sess,
raw_bill["account"],
raw_bill["reference"],
raw_bill["issue_date"],
raw_bill["start_date"],
raw_bill["finish_date"],
raw_bill["kwh"],
raw_bill["net"],
raw_bill["vat"],
raw_bill["gross"],
bill_types[raw_bill["bill_type_code"]],
raw_bill["breakdown"],
supply,
)
for raw_read in raw_bill["reads"]:
bill.insert_read(
sess,
tprs[raw_read["tpr_code"]],
raw_read["coefficient"],
raw_read["units"],
raw_read["msn"],
raw_read["mpan"],
raw_read["prev_date"],
raw_read["prev_value"],
read_types[raw_read["prev_type_code"]],
raw_read["pres_date"],
raw_read["pres_value"],
read_types[raw_read["pres_type_code"]],
)
self.successful_bills.append(raw_bill)
except KeyError as e:
err = raw_bill.get("error", "")
raw_bill["error"] = err + " " + str(e)
self.failed_bills.append(raw_bill)
except BadRequest as e:
raw_bill["error"] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log(f"The import has finished, but there were "
f"{len(self.failed_bills)} failures, and so the "
f"whole import has been rolled back.")
except BadRequest as e:
sess.rollback()
self._log(f"Problem: {e.description}")
except BaseException:
sess.rollback()
self._log(f"I've encountered a problem: {traceback.format_exc()}")
finally:
if sess is not None:
sess.close()
def run(self):
sess = None
try:
sess = Session()
self._log(
"Starting to parse the file with '" + self.parser_name + "'.")
set_read_write(sess)
batch = Batch.get_by_id(sess, self.batch_id)
raw_bills = self.parser.make_raw_bills()
self._log(
"Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
with sess.begin_nested():
sess.execute(
"set transaction isolation level serializable "
"read write")
bill_type = BillType.get_by_code(
sess, raw_bill['bill_type_code'])
bill = batch.insert_bill(
sess, raw_bill['account'], raw_bill['reference'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net'], raw_bill['vat'],
raw_bill['gross'],
bill_type, raw_bill['breakdown'])
sess.flush()
for raw_read in raw_bill['reads']:
tpr_code = raw_read['tpr_code']
if tpr_code is None:
tpr = None
else:
tpr = Tpr.get_by_code(sess, tpr_code)
prev_type = ReadType.get_by_code(
sess, raw_read['prev_type_code'])
pres_type = ReadType.get_by_code(
sess, raw_read['pres_type_code'])
bill.insert_read(
sess, tpr, raw_read['coefficient'],
raw_read['units'], raw_read['msn'],
raw_read['mpan'], raw_read['prev_date'],
raw_read['prev_value'], prev_type,
raw_read['pres_date'], raw_read['pres_value'],
pres_type)
self.successful_bills.append(raw_bill)
except BadRequest as e:
raw_bill['error'] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log(
"The import has finished, but there were " +
str(len(self.failed_bills)) + " failures, and so the "
"whole import has been rolled back.")
except:
sess.rollback()
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
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)
def run(self):
sess = None
try:
sess = Session()
self._log("Starting to parse the file with '" + self.parser_name +
"'.")
bill_types = keydefaultdict(
lambda k: BillType.get_by_code(sess, k))
tprs = keydefaultdict(lambda k: None
if k is None else Tpr.get_by_code(sess, k))
read_types = keydefaultdict(
lambda k: ReadType.get_by_code(sess, k))
batch = Batch.get_by_id(sess, self.batch_id)
contract = batch.contract
raw_bills = self.parser.make_raw_bills()
self._log("Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
account = raw_bill['account']
supply = sess.query(Supply).join(Era).filter(
or_(
and_(Era.imp_supplier_contract == contract,
Era.imp_supplier_account == account),
and_(Era.exp_supplier_contract == contract,
Era.exp_supplier_account == account),
and_(Era.mop_contract == contract,
Era.mop_account == account),
and_(Era.dc_contract == contract, Era.dc_account ==
account))).distinct().order_by(
Supply.id).first()
if supply is None:
raise BadRequest("Can't find an era with contract '" +
contract.name + "' and account '" +
account + "'.")
with sess.begin_nested():
bill = batch.insert_bill(
sess, account, raw_bill['reference'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net'], raw_bill['vat'],
raw_bill['gross'],
bill_types[raw_bill['bill_type_code']],
raw_bill['breakdown'], supply)
for raw_read in raw_bill['reads']:
bill.insert_read(
sess, tprs[raw_read['tpr_code']],
raw_read['coefficient'], raw_read['units'],
raw_read['msn'], raw_read['mpan'],
raw_read['prev_date'], raw_read['prev_value'],
read_types[raw_read['prev_type_code']],
raw_read['pres_date'], raw_read['pres_value'],
read_types[raw_read['pres_type_code']])
self.successful_bills.append(raw_bill)
except BadRequest as e:
raw_bill['error'] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log("The import has finished, but there were " +
str(len(self.failed_bills)) +
" failures, and so the "
"whole import has been rolled back.")
except BadRequest as e:
sess.rollback()
self._log("Problem: " + e.description)
except BaseException:
sess.rollback()
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
def run(self):
sess = None
try:
sess = Session()
self._log(
"Starting to parse the file with '" + self.parser_name + "'.")
bill_types = keydefaultdict(
lambda k: BillType.get_by_code(sess, k))
tprs = keydefaultdict(
lambda k: None if k is None else Tpr.get_by_code(sess, k))
read_types = keydefaultdict(
lambda k: ReadType.get_by_code(sess, k))
batch = Batch.get_by_id(sess, self.batch_id)
contract = batch.contract
raw_bills = self.parser.make_raw_bills()
self._log(
"Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
account = raw_bill['account']
supply = sess.query(Supply).join(Era).filter(
or_(
and_(
Era.imp_supplier_contract == contract,
Era.imp_supplier_account == account),
and_(
Era.exp_supplier_contract == contract,
Era.exp_supplier_account == account),
and_(
Era.mop_contract == contract,
Era.mop_account == account),
and_(
Era.hhdc_contract == contract,
Era.hhdc_account == account))
).distinct().order_by(Supply.id).first()
if supply is None:
raise BadRequest(
"Can't find an era with contract '" +
contract.name + "' and account '" + account + "'.")
with sess.begin_nested():
bill = batch.insert_bill(
sess, account, raw_bill['reference'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net'], raw_bill['vat'],
raw_bill['gross'],
bill_types[raw_bill['bill_type_code']],
raw_bill['breakdown'], supply)
for raw_read in raw_bill['reads']:
bill.insert_read(
sess, tprs[raw_read['tpr_code']],
raw_read['coefficient'], raw_read['units'],
raw_read['msn'], raw_read['mpan'],
raw_read['prev_date'], raw_read['prev_value'],
read_types[raw_read['prev_type_code']],
raw_read['pres_date'], raw_read['pres_value'],
read_types[raw_read['pres_type_code']])
self.successful_bills.append(raw_bill)
except BadRequest as e:
raw_bill['error'] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log(
"The import has finished, but there were " +
str(len(self.failed_bills)) + " failures, and so the "
"whole import has been rolled back.")
except:
sess.rollback()
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()