def do_get(session):
user = g.user
date = req_date("date")
if "supply_id" in request.values:
supply_id = req_int("supply_id")
else:
supply_id = None
if "mpan_cores" in request.values:
mpan_cores_str = req_str("mpan_cores")
mpan_cores = mpan_cores_str.splitlines()
if len(mpan_cores) == 0:
mpan_cores = None
else:
for i in range(len(mpan_cores)):
mpan_cores[i] = parse_mpan_core(mpan_cores[i])
else:
mpan_cores = None
running_name, finished_name = chellow.dloads.make_names(
"supplies_snapshot.csv", user)
args = (running_name, finished_name, date, supply_id, mpan_cores)
threading.Thread(target=content, args=args).start()
return chellow_redirect("/downloads", 303)
def do_post(sess):
start_date = req_date("start")
finish_date = req_date("finish")
supply_id = req_int("supply_id") if "supply_id" in request.values else None
if "mpan_cores" in request.values:
mpan_cores_str = req_str("mpan_cores")
mpan_cores = mpan_cores_str.splitlines()
if len(mpan_cores) == 0:
mpan_cores = None
else:
for i in range(len(mpan_cores)):
mpan_cores[i] = parse_mpan_core(mpan_cores[i])
else:
mpan_cores = None
if finish_date < start_date:
raise BadRequest("The finish date can't be before the start date.")
is_zipped = req_bool("is_zipped")
user = g.user
threading.Thread(
target=content,
args=(start_date, finish_date, supply_id, mpan_cores, is_zipped, user),
).start()
return chellow_redirect("/downloads", 303)
def __next__(self):
try:
self.line_number, self.values = next(self.shredder)
mpan_core_str = self.get_field(0, "MPAN Core")
datum = {'mpan_core': parse_mpan_core(mpan_core_str)}
channel_type_str = self.get_field(1, "Channel Type")
datum['channel_type'] = parse_channel_type(channel_type_str)
start_date_str = self.get_field(2, "Start Date")
datum['start_date'] = validate_hh_start(
datetime.datetime.strptime(
start_date_str, "%Y-%m-%d %H:%M").replace(tzinfo=pytz.utc))
value_str = self.get_field(3, "Value")
datum['value'] = decimal.Decimal(value_str)
status = self.get_field(4, "Status")
if len(status) != 1:
raise BadRequest(
"The status character must be one character in length.")
datum['status'] = status
return datum
except BadRequest as e:
e.description = ''.join(
"Problem at line number: ", str(self.line_number), ": ",
str(self.values), ": ", e.description)
raise e
def do_post(sess):
start_date = req_date('start')
finish_date = req_date('finish')
supply_id = req_int('supply_id') if 'supply_id' in request.values else None
if 'mpan_cores' in request.values:
mpan_cores_str = req_str('mpan_cores')
mpan_cores = mpan_cores_str.splitlines()
if len(mpan_cores) == 0:
mpan_cores = None
else:
for i in range(len(mpan_cores)):
mpan_cores[i] = parse_mpan_core(mpan_cores[i])
else:
mpan_cores = None
if finish_date < start_date:
raise BadRequest("The finish date can't be before the start date.")
is_zipped = req_bool('is_zipped')
user = g.user
threading.Thread(
target=content, args=(
start_date, finish_date, supply_id, mpan_cores, is_zipped, user)
).start()
return chellow_redirect("/downloads", 303)
def do_get(session):
user = g.user
date = req_date('date')
if 'supply_id' in request.values:
supply_id = req_int('supply_id')
else:
supply_id = None
if 'mpan_cores' in request.values:
mpan_cores_str = req_str('mpan_cores')
mpan_cores = mpan_cores_str.splitlines()
if len(mpan_cores) == 0:
mpan_cores = None
else:
for i in range(len(mpan_cores)):
mpan_cores[i] = parse_mpan_core(mpan_cores[i])
else:
mpan_cores = None
running_name, finished_name = chellow.dloads.make_names(
'supplies_snapshot.csv', user)
args = (running_name, finished_name, date, supply_id, mpan_cores)
threading.Thread(target=content, args=args).start()
return chellow_redirect("/downloads", 303)
def __next__(self):
try:
self.line_number, self.values = next(self.shredder)
mpan_core_str = self.get_field(0, "MPAN Core")
datum = {'mpan_core': parse_mpan_core(mpan_core_str)}
channel_type_str = self.get_field(1, "Channel Type")
datum['channel_type'] = parse_channel_type(channel_type_str)
start_date_str = self.get_field(2, "Start Date")
datum['start_date'] = validate_hh_start(
to_utc(Datetime.strptime(start_date_str, "%Y-%m-%d %H:%M")))
value_str = self.get_field(3, "Value")
datum['value'] = Decimal(value_str)
status = self.get_field(4, "Status")
if len(status) != 1:
raise BadRequest(
"The status character must be one character in length.")
datum['status'] = status
return datum
except BadRequest as e:
e.description = ''.join("Problem at line number: ",
str(self.line_number), ": ",
str(self.values), ": ", e.description)
raise e
def __next__(self):
local_datum = None
try:
while local_datum is None:
self.line_number, self.line = next(self.reader)
lline = self.line.strip().upper()
if lline.startswith("#O"):
self.core = parse_mpan_core(lline[2:])
elif lline.startswith("#S"):
sensor = int(lline[2:].strip())
try:
self.channel_type = self.sensor_map[sensor]
except KeyError:
raise BadRequest(
"The sensor number must be between 1 and 4 "
"inclusive.")
elif lline.startswith("#F2") or len(lline) == 0:
continue
else:
fields = [f.strip() for f in lline.split(",")]
if len(fields) != 4:
raise BadRequest(
"There should be 4 comma separated values, but I "
"found " + str(len(fields)) + ".")
d_day, d_month, d_year = map(int, fields[0].split("/"))
time_fields = tuple(map(int, fields[1].split(":")))
if len(time_fields) > 2 and time_fields[2] != 0:
raise BadRequest(
"The number of seconds (if present) must always "
"be zero.")
start_date = (
utc_datetime(d_year, d_month, d_day, time_fields[0],
time_fields[1]) - HH)
try:
value = decimal.Decimal(fields[2])
except ValueError:
raise BadRequest("Problem parsing the value: " +
fields[2])
status = fields[3][-1]
local_datum = {
"mpan_core": self.core,
"channel_type": self.channel_type,
"start_date": start_date,
"value": value,
"status": status,
}
return local_datum
except BadRequest as e:
e.description = "".join((
"Problem at line number: ",
str(self.line_number),
": ",
self.line,
": ",
e.description,
))
raise e
def do_post(sess):
if "mpan_cores" in request.values:
mpan_cores_str = req_str("mpan_cores")
mpan_cores = mpan_cores_str.splitlines()
if len(mpan_cores) == 0:
mpan_cores = None
else:
for i in range(len(mpan_cores)):
mpan_cores[i] = parse_mpan_core(mpan_cores[i])
return handle_request(mpan_cores)
def _process_MAN(elements, headers):
madn = elements["MADN"]
dno = madn[0]
unique = madn[1]
check_digit = madn[2]
# pc = madn[3]
# mtc = madn[4]
# llfc = madn[5]
headers["mpan_core"] = parse_mpan_core("".join([dno, unique, check_digit]))
def do_post(sess):
if 'mpan_cores' in request.values:
mpan_cores_str = req_str('mpan_cores')
mpan_cores = mpan_cores_str.splitlines()
if len(mpan_cores) == 0:
mpan_cores = None
else:
for i in range(len(mpan_cores)):
mpan_cores[i] = parse_mpan_core(mpan_cores[i])
return handle_request(mpan_cores)
def __next__(self):
local_datum = None
try:
while local_datum is None:
self.line_number, self.line = next(self.reader)
lline = self.line.strip().upper()
if lline.startswith("#O"):
self.core = parse_mpan_core(lline[2:])
elif lline.startswith("#S"):
sensor = int(lline[2:].strip())
try:
self.channel_type = self.sensor_map[sensor]
except KeyError:
raise BadRequest(
"The sensor number must be between 1 and 4 "
"inclusive.")
elif lline.startswith("#F2") or len(lline) == 0:
continue
else:
fields = [f.strip() for f in lline.split(',')]
if len(fields) != 4:
raise BadRequest(
"There should be 4 comma separated values, but I "
"found " + str(len(fields)) + ".")
d_day, d_month, d_year = map(int, fields[0].split('/'))
time_fields = tuple(map(int, fields[1].split(':')))
if len(time_fields) > 2 and time_fields[2] != 0:
raise BadRequest(
"The number of seconds (if present) must always "
"be zero.")
start_date = utc_datetime(
d_year, d_month, d_day, time_fields[0],
time_fields[1]) - HH
try:
value = decimal.Decimal(fields[2])
except ValueError as e:
raise BadRequest(
"Problem parsing the value: " + fields[2])
status = fields[3][-1]
local_datum = {
'mpan_core': self.core,
'channel_type': self.channel_type,
'start_date': start_date, 'value': value,
'status': status}
return local_datum
except BadRequest as e:
e.description = ''.join(
(
"Problem at line number: ", str(self.line_number), ": ",
self.line, ": ", e.description))
raise e
def __next__(self):
datum = None
try:
while datum is None:
if self.col_idx > 50:
self.line_number, self.values = next(self.shredder)
if len(self.values) == 0:
continue
self.col_idx = 0
if self.col_idx == 0:
try:
self.core = self.values[self.col_idx]
except KeyError:
raise BadRequest(
"There doesn't seem to be an MPAN Core at the "
"beginning of this line. ")
self.core = self.mpan_map.get(self.core, self.core)
self.core = parse_mpan_core(self.core)
elif self.col_idx == 2:
day, month, year = map(
int, self.values[self.col_idx].split("/"))
self.date = utc_datetime(year, month, day)
elif 2 < self.col_idx < len(self.values):
hh_value = self.values[self.col_idx].strip()
mins = 30 * (self.col_idx - 3)
if len(hh_value) > 0:
datum = {
"mpan_core": self.core,
"channel_type": "ACTIVE",
"start_date": self.date + timedelta(minutes=mins),
"value": decimal.Decimal(hh_value),
"status": "A",
}
self.col_idx += 1
except BadRequest as e:
e.description = "".join("Problem at line number: ",
str(self.line_number), ": ", e.description)
raise e
return datum
def __next__(self):
datum = None
try:
while datum is None:
if self.col_idx > 50:
self.line_number, self.values = next(self.shredder)
if len(self.values) == 0:
continue
self.col_idx = 0
if self.col_idx == 0:
try:
self.core = self.values[self.col_idx]
except KeyError:
raise BadRequest(
"There doesn't seem to be an MPAN Core at the "
"beginning of this line. ")
self.core = self.mpan_map.get(self.core, self.core)
self.core = parse_mpan_core(self.core)
elif self.col_idx == 2:
day, month, year = map(
int, self.values[self.col_idx].split('/'))
self.date = datetime.datetime(
year, month, day, tzinfo=pytz.utc)
elif 2 < self.col_idx < len(self.values):
hh_value = self.values[self.col_idx].strip()
mins = 30 * (self.col_idx - 3)
if len(hh_value) > 0:
datum = {
'mpan_core': self.core, 'channel_type': 'ACTIVE',
'start_date': self.date + timedelta(minutes=mins),
'value': decimal.Decimal(hh_value), 'status': 'A'}
self.col_idx += 1
except BadRequest as e:
e.description = ''.join(
"Problem at line number: ", str(self.line_number), ": ",
e.description)
raise e
return datum
def make_raw_bills(self):
raw_bills = []
for i, line in enumerate(self.f):
self.line_number = i
record_type = line[62:66]
if record_type == "0100":
account = line[33:41]
reference = line[41:46]
start_date = None
finish_date = None
net = Decimal(0)
vat = Decimal(0)
mpan_core = None
elif record_type == "1460":
net += Decimal(line[67:79]) / Decimal(100)
vat += Decimal(line[85:97]) / Decimal(100)
elif record_type == "0461":
mpan_core = parse_mpan_core(line[135:148])
elif record_type == "0101":
start_date = parse_date(line[66:74])
finish_date = parse_date(line[74:82])
elif record_type == "1500":
raw_bill = {
"bill_type_code": "N",
"mpan_core": mpan_core,
"account": account,
"reference": reference,
"issue_date": start_date,
"start_date": start_date,
"finish_date": finish_date,
"kwh": Decimal("0.00"),
"net": net,
"vat": vat,
"gross": Decimal("0.00"),
"breakdown": {},
"reads": [],
}
raw_bills.append(raw_bill)
return raw_bills
def make_raw_bills(self):
raw_bills = []
breakdown = None
for self.line_number, code in enumerate(self.parser):
if code == "BCD":
ivdt = self.parser.elements[0]
issue_date = to_utc(to_ct_date(ivdt[0]))
invn = self.parser.elements[2]
reference = invn[0]
account = "SA" + reference[:9]
btcd = self.parser.elements[5]
bill_type_code = btcd[0]
sumo = self.parser.elements[7]
start_date = to_start_date(sumo[0])
if to_ct_date(sumo[1]) in (
ct_datetime(2020, 4, 1),
ct_datetime(2020, 3, 16),
):
finish_date = to_start_date(sumo[1]) - HH
else:
finish_date = to_finish_date(sumo[1])
elif code == "MHD":
type = self.parser.elements[1]
message_type = type[0]
if message_type == "UTLBIL":
issue_date = None
start_date = None
finish_date = None
account = None
reference = None
net = Decimal("0.00")
vat = Decimal("0.00")
gross = Decimal("0.00")
kwh = Decimal(0)
reads = []
bill_type_code = None
mpan_core = None
breakdown = defaultdict(int, {"raw-lines": []})
elif code == "CCD":
ccde = self.parser.elements[1]
consumption_charge_indicator = ccde[0]
if consumption_charge_indicator == "1":
prdt = self.parser.elements[6]
pvdt = self.parser.elements[7]
pres_read_date = to_finish_date(prdt[0])
prev_read_date = to_finish_date(pvdt[0])
tmod = self.parser.elements[3]
mtnr = self.parser.elements[4]
mloc = self.parser.elements[5]
mpan = mloc[0]
mpan_core = " ".join(
[mpan[:2], mpan[2:6], mpan[6:10], mpan[10:13]])
mpan = (mpan[13:15] + " " + mpan[15:18] + " " + mpan[18:] +
" " + mpan_core)
prrd = self.parser.elements[9]
pres_read_type = read_type_map[prrd[1]]
prev_read_type = read_type_map[prrd[3]]
adjf = self.parser.elements[12]
cons = self.parser.elements[13]
coefficient = Decimal(adjf[1]) / Decimal(100000)
pres_reading_value = Decimal(prrd[0])
prev_reading_value = Decimal(prrd[2])
msn = mtnr[0]
tpr_native = tmod[0]
if tpr_native not in tmod_map:
raise BadRequest(
"The TPR code " + tpr_native +
" can't be found in the TPR list for mpan " +
mpan + ".")
tpr_code = tmod_map[tpr_native]
if tpr_code == "kW":
units = "kW"
tpr_code = None
elif tpr_code == "kVA":
units = "kVA"
tpr_code = None
else:
units = "kWh"
kwh += to_decimal(cons) / Decimal("1000")
if mpan_core in WRONG_TPRS and pres_read_date == to_utc(
ct_datetime(2020, 4, 1, 23, 30)):
pres_read_date = to_utc(ct_datetime(
2020, 4, 1, 22, 30))
reads.append({
"msn":
"Separator Read",
"mpan":
mpan,
"coefficient":
coefficient,
"units":
units,
"tpr_code":
tpr_code,
"prev_date":
to_utc(ct_datetime(2020, 4, 1, 23)),
"prev_value":
0,
"prev_type_code":
"N",
"pres_date":
to_utc(ct_datetime(2020, 4, 1, 23)),
"pres_value":
0,
"pres_type_code":
"N",
})
reads.append({
"msn": msn,
"mpan": mpan,
"coefficient": coefficient,
"units": units,
"tpr_code": tpr_code,
"prev_date": prev_read_date,
"prev_value": prev_reading_value,
"prev_type_code": prev_read_type,
"pres_date": pres_read_date,
"pres_value": pres_reading_value,
"pres_type_code": pres_read_type,
})
elif consumption_charge_indicator == "2":
# tcod = self.parser.elements[2]
tmod = self.parser.elements[3]
mtnr = self.parser.elements[4]
mloc = self.parser.elements[5]
mpan = mloc[0]
mpan_core = " ".join(
[mpan[:2], mpan[2:6], mpan[6:10], mpan[10:13]])
mpan = (mpan[13:15] + " " + mpan[15:18] + " " + mpan[18:] +
" " + mpan_core)
prdt = self.parser.elements[6]
pvdt = self.parser.elements[7]
pres_read_date = to_finish_date(prdt[0])
prev_read_date = to_finish_date(pvdt[0])
ndrp = self.parser.elements[8]
prrd = self.parser.elements[9]
pres_read_type = read_type_map[prrd[1]]
prev_read_type = read_type_map[prrd[3]]
adjf = self.parser.elements[12]
cona = self.parser.elements[13]
coefficient = Decimal(adjf[1]) / Decimal(100000)
pres_reading_value = Decimal(prrd[0])
prev_reading_value = Decimal(prrd[2])
msn = mtnr[0]
tpr_code = tmod[0]
if tpr_code not in tmod_map:
raise BadRequest(
"The TPR code " + tpr_code +
" can't be found in the TPR list for mpan " +
mpan + ".")
tpr = tmod_map[tpr_code]
if tpr == "kW":
units = "kW"
tpr = None
prefix = "md-"
elif tpr == "kVA":
units = "kVA"
tpr = None
prefix = "md-"
else:
units = "kWh"
kwh += to_decimal(cona) / Decimal("1000")
prefix = tpr + "-"
nuct = self.parser.elements[15]
breakdown[prefix +
"kwh"] += to_decimal(nuct) / Decimal("1000")
cppu = self.parser.elements[18]
rate_key = prefix + "rate"
if rate_key not in breakdown:
breakdown[rate_key] = set()
breakdown[rate_key].add(
to_decimal(cppu) / Decimal("100000"))
ctot = self.parser.elements[19]
breakdown[prefix +
"gbp"] += to_decimal(ctot) / Decimal("100")
if mpan_core in WRONG_TPRS and pres_read_date == to_utc(
ct_datetime(2020, 4, 1, 23, 30)):
pres_read_date = to_utc(ct_datetime(
2020, 4, 1, 22, 30))
reads.append({
"msn":
"Separator Read",
"mpan":
mpan,
"coefficient":
coefficient,
"units":
units,
"tpr_code":
tpr,
"prev_date":
to_utc(ct_datetime(2020, 4, 1, 23)),
"prev_value":
0,
"prev_type_code":
"N",
"pres_date":
to_utc(ct_datetime(2020, 4, 1, 23)),
"pres_value":
0,
"pres_type_code":
"N",
})
reads.append({
"msn": msn,
"mpan": mpan,
"coefficient": coefficient,
"units": units,
"tpr_code": tpr,
"prev_date": prev_read_date,
"prev_value": prev_reading_value,
"prev_type_code": prev_read_type,
"pres_date": pres_read_date,
"pres_value": pres_reading_value,
"pres_type_code": pres_read_type,
})
elif consumption_charge_indicator == "3":
# tcod = self.parser.elements[2]
tmod = self.parser.elements[3]
tmod0 = tmod[0]
if tmod0 == "CCL":
prefix = kwh_prefix = "ccl-"
elif tmod0 in ["CQFITC", "CMFITC"]:
prefix = "fit-"
kwh_prefix = "fit-msp-"
elif tmod0 == "FITARR":
prefix = kwh_prefix = "fit-reconciliation-"
else:
tpr_code = tmod0
if tpr_code not in tmod_map:
raise BadRequest(
"The TPR code " + tpr_code +
" can't be found in the TPR list for mpan " +
mpan + ".")
prefix = kwh_prefix = tmod_map[tpr_code] + "-"
mtnr = self.parser.elements[4]
ndrp = self.parser.elements[8]
cona = self.parser.elements[13]
nuct = self.parser.elements[15]
breakdown[kwh_prefix +
"kwh"] += to_decimal(nuct) / Decimal("1000")
cppu = self.parser.elements[18]
rate_key = prefix + "rate"
if rate_key not in breakdown:
breakdown[rate_key] = set()
breakdown[rate_key].add(
to_decimal(cppu) / Decimal("100000"))
ctot = self.parser.elements[19]
breakdown[prefix +
"gbp"] += to_decimal(ctot) / Decimal("100")
elif consumption_charge_indicator == "4":
# tcod = self.parser.elements[2]
tmod = self.parser.elements[3]
tmod0 = tmod[0]
mtnr = self.parser.elements[4]
ndrp = self.parser.elements[8]
if len(ndrp[0]) > 0:
breakdown["standing-days"] += to_decimal(ndrp)
cona = self.parser.elements[13]
nuct = self.parser.elements[15]
cppu = self.parser.elements[18]
ctot = self.parser.elements[19]
if len(ctot[0]) > 0:
breakdown["standing-gbp"] += to_decimal(
ctot) / Decimal("100")
elif code == "MTR":
if message_type == "UTLBIL":
if mpan_core is None:
sess = Session()
era = (sess.query(Era).filter(
Era.imp_supplier_account == account).first())
if era is not None:
mpan_core = era.imp_mpan_core
sess.close()
raw_bill = {
"bill_type_code": bill_type_code,
"account": account,
"mpan_core": mpan_core,
"reference": reference,
"issue_date": issue_date,
"start_date": start_date,
"finish_date": finish_date,
"kwh": kwh,
"net": net,
"vat": vat,
"gross": gross,
"breakdown": breakdown,
"reads": reads,
}
raw_bills.append(raw_bill)
breakdown = None
elif code == "MAN":
madn = self.parser.elements[2]
"""
pc_code = madn[3]
mtc_code = madn[4]
llfc_code = madn[5]
"""
mpan_core = parse_mpan_core("".join(
(madn[0], madn[1], madn[2])))
elif code == "VAT":
uvla = self.parser.elements[5]
net += to_decimal(uvla) / Decimal("100")
uvtt = self.parser.elements[6]
vat += to_decimal(uvtt) / Decimal("100")
ucsi = self.parser.elements[7]
gross += to_decimal(ucsi) / Decimal("100")
if breakdown is not None:
breakdown["raw-lines"].append(self.parser.line)
return raw_bills
def make_raw_bills(self):
row_index = None
sess = None
try:
sess = Session()
bills = []
title_row = self.sheet.row(0)
for row_index in range(1, self.sheet.nrows):
row = self.sheet.row(row_index)
val = get_value(title_row, row, "mpan ref")
if val is None or val == "":
break
self._set_last_line(row_index, val)
msn = str(get_value(title_row, row, "meter")).strip()
mpan_core = parse_mpan_core(str(get_int(title_row, row, "mpan ref")))
start_date = get_start_date(title_row, row, "start", self.book.datemode)
issue_date = start_date
finish_date = get_finish_date(title_row, row, "end", self.book.datemode)
check = get_str(title_row, row, "check")
if check != "Billed":
continue
net = METER_RATE / 12
vat = round(net * Decimal("0.2"), 2)
breakdown = {
"raw_lines": [str(title_row)],
"cop": ["5"],
"settlement-status": ["non_settlement"],
"msn": [msn],
"meter-rate": [METER_RATE],
"meter-gbp": net,
}
bills.append(
{
"bill_type_code": "N",
"kwh": Decimal(0),
"vat": vat,
"net": net,
"gross": net + vat,
"reads": [],
"breakdown": breakdown,
"account": mpan_core,
"issue_date": issue_date,
"start_date": start_date,
"finish_date": finish_date,
"mpan_core": mpan_core,
"reference": "_".join(
(
start_date.strftime("%Y%m%d"),
finish_date.strftime("%Y%m%d"),
issue_date.strftime("%Y%m%d"),
mpan_core,
)
),
}
)
sess.rollback()
except BadRequest as e:
raise BadRequest("Row number: " + str(row_index) + " " + e.description)
finally:
if sess is not None:
sess.close()
return bills
def make_raw_bills(self):
row_index = None
sess = None
try:
sess = Session()
bills = []
title_row = self.sheet.row(10)
issue_date_str = get_str(self.sheet.row(6), 0)
issue_date = Datetime.strptime(issue_date_str[6:],
"%d/%m/%Y %H:%M:%S")
for row_index in range(11, self.sheet.nrows):
row = self.sheet.row(row_index)
val = get_value(row, 1)
if val is None or val == '':
break
self._set_last_line(row_index, val)
mpan_core = parse_mpan_core(str(get_int(row, 1)))
start_date = get_date(row, 3, self.book.datemode)
finish_date = get_date(row, 4, self.book.datemode) + \
Timedelta(days=1) - HH
era = sess.query(Era).filter(
or_(Era.imp_mpan_core == mpan_core,
Era.exp_mpan_core == mpan_core),
Era.start_date <= finish_date,
or_(Era.finish_date == null(),
Era.finish_date > start_date)).order_by(
Era.start_date).first()
if era is None:
era = sess.query(Era).filter(
or_(Era.imp_mpan_core == mpan_core,
Era.exp_mpan_core == mpan_core)).order_by(
Era.start_date.desc()).first()
if era is None:
account = mpan_core + '/DC'
else:
account = era.dc_account
net = round(get_dec(row, 31), 2)
cop_3_meters = get_int(row, 6)
cop_3_rate = get_dec(row, 7)
cop_3_gbp = get_dec(row, 8)
# Cop 5 meters
get_int(row, 9)
cop_5_rate = get_dec(row, 10)
cop_5_gbp = get_dec(row, 11)
ad_hoc_visits = get_dec(row, 21)
ad_hoc_rate = get_dec(row, 22)
ad_hoc_gbp = get_dec(row, 23)
annual_visits = get_int(row, 27)
annual_rate = get_dec(row, 28)
annual_gbp = get_dec(row, 29)
annual_date = hh_format(get_date(row, 30, self.book.datemode))
if cop_3_meters > 0:
cop = '3'
mpan_rate = cop_3_rate
mpan_gbp = cop_3_gbp
else:
cop = '5'
mpan_rate = cop_5_rate
mpan_gbp = cop_5_gbp
breakdown = {
'raw_lines': [str(title_row)],
'cop': [cop],
'settlement-status': ['settlement'],
'mpan-rate': [mpan_rate],
'mpan-gbp': mpan_gbp,
'ad-hoc-visits': ad_hoc_visits,
'ad-hoc-rate': [ad_hoc_rate],
'ad-hoc-gbp': ad_hoc_gbp,
'annual-visits-count': annual_visits,
'annual-visits-rate': [annual_rate],
'annual-visits-gbp': annual_gbp,
'annual-visits-date': [annual_date]
}
bills.append({
'bill_type_code':
'N',
'kwh':
Decimal(0),
'vat':
Decimal('0.00'),
'net':
net,
'gross':
net,
'reads': [],
'breakdown':
breakdown,
'account':
account,
'issue_date':
issue_date,
'start_date':
start_date,
'finish_date':
finish_date,
'mpans': [mpan_core],
'reference':
'_'.join((start_date.strftime('%Y%m%d'),
finish_date.strftime('%Y%m%d'),
issue_date.strftime('%Y%m%d'), mpan_core))
})
sess.rollback()
except BadRequest as e:
raise BadRequest("Row number: " + str(row_index) + " " +
e.description)
finally:
if sess is not None:
sess.close()
return bills
def make_raw_bills(self):
titles = [t.strip() for t in next(iter(self.reader))]
title_idx = dict((title, i) for i, title in enumerate(titles))
raw_bills = []
for vals in self.reader:
if len(vals) == 0 or vals[0].startswith("#") or "".join(
vals) == "":
continue
def val(title):
idx = title_idx[title]
try:
return vals[idx]
except KeyError:
raise BadRequest("For title " + title + " and index " +
str(idx))
def date_val(title):
try:
return to_utc(Datetime.strptime(val(title), "%d/%m/%Y"))
except ValueError as e:
raise BadRequest("At line number " +
str(self._line_number) +
", while trying to find the value of " +
title + " the date couldn't be parsed. " +
str(e) + " The full line is " +
self.last_line)
def dec_val(title):
return Decimal(val(title))
issue_date = date_val("Invoice Date")
bill_from = validate_hh_start(date_val("Bill Period Start"))
bill_to = (validate_hh_start(date_val("Bill Period End")) +
relativedelta(days=1) - HH)
kwh = dec_val("Site Data")
breakdown = dict([(v, dec_val(k)) for k, v in col_map.items()
if k in titles])
breakdown["raw_lines"] = [
self._title_line.strip(),
self.last_line.strip()
]
net = Decimal("0.00") + dec_val("NET_AMT")
vat = Decimal("0.00") + dec_val("VATTTL")
gross = Decimal("0.00") + dec_val("TTLCHG")
mpan_core = parse_mpan_core(val("Mpan"))
raw_bill = {
"bill_type_code": "N",
"account": val("Bill Ref No."),
"mpan_core": mpan_core,
"reference": val("Invoice No."),
"issue_date": issue_date,
"start_date": bill_from,
"finish_date": bill_to,
"kwh": kwh,
"net": net,
"vat": vat,
"gross": gross,
"breakdown": breakdown,
"reads": [],
}
raw_bills.append(raw_bill)
return raw_bills
def make_raw_bills(self):
raw_bills = []
for self.line_number, code in enumerate(self.parser):
if code == "CLO":
cloc = self.parser.elements[0]
account = cloc[1]
elif code == "BCD":
ivdt = self.parser.elements[0]
invn = self.parser.elements[2]
btcd = self.parser.elements[5]
reference = invn[0]
bill_type_code = btcd[0]
issue_date = to_date(ivdt[0])
elif code == "MHD":
typ = self.parser.elements[1]
message_type = typ[0]
if message_type == "UTLBIL":
issue_date = None
start_date = None
finish_date = None
account = None
reference = None
net = Decimal(0.00)
vat = Decimal(0.00)
reads = []
mpan_core = None
elif code == "CCD":
ccde = self.parser.elements[1]
consumption_charge_indicator = ccde[0]
charge_type = ccde[2]
if consumption_charge_indicator != "5" and charge_type in [
"7",
"8",
"9",
]:
prev_read_date = to_date(self.parser.elements[7][0])
if hh_after(start_date, prev_read_date):
start_date = prev_read_date
register_finish_date = to_date(self.parser.elements[6][0])
if finish_date is None or finish_date < register_finish_date:
finish_date = register_finish_date
if charge_type == "7":
tmod = self.parser.elements[3]
mtnr = self.parser.elements[4]
mloc = self.parser.elements[5]
prrd = self.parser.elements[9]
adjf = self.parser.elements[12]
pres_read_type = read_type_map[prrd[1]]
prev_read_type = read_type_map[prrd[3]]
coefficient = Decimal(adjf[1]) / Decimal(100000)
pres_read_value = Decimal(prrd[0]) / Decimal(1000)
prev_read_value = Decimal(prrd[2]) / Decimal(1000)
msn = mtnr[0]
tpr_code = tmod[0].zfill(5)
read = {
"msn": msn,
"mpan": mloc[0],
"coefficient": coefficient,
"units": "kWh",
"tpr_code": tpr_code,
"prev_date": prev_read_date,
"prev_value": prev_read_value,
"prev_type_code": prev_read_type,
"pres_date": register_finish_date,
"pres_value": pres_read_value,
"pres_type_code": pres_read_type,
}
reads.append(read)
elif code == "MTR":
if message_type == "UTLBIL":
raw_bill = {
"bill_type_code": bill_type_code,
"account": account,
"mpan_core": mpan_core,
"reference": reference,
"issue_date": issue_date,
"start_date": start_date,
"finish_date": finish_date,
"kwh": Decimal(0),
"net": net,
"vat": vat,
"gross": Decimal("0.00"),
"breakdown": {},
"reads": reads,
}
raw_bills.append(raw_bill)
elif code == "MAN":
madn = self.parser.elements[2]
# pc_code = "0" + madn[3]
# mtc_code = madn[4]
# llfc_code = madn[5]
mpan_core = parse_mpan_core(madn[0] + " " + madn[1] + madn[2])
elif code == "VAT":
uvla = self.parser.elements[5]
net = Decimal("0.00") + to_decimal(uvla)
uvtt = self.parser.elements[6]
vat = Decimal("0.00") + to_decimal(uvtt)
return raw_bills
def make_raw_bills(self):
row_index = sess = None
try:
sess = Session()
bills = []
title_row = self.sheet.row(10)
issue_date = get_start_date(self.sheet.row(5), 2,
self.book.datemode)
if issue_date is None:
raise BadRequest("Expected to find the issue date at cell C6.")
for row_index in range(11, self.sheet.nrows):
row = self.sheet.row(row_index)
val = get_value(row, 1)
if val is None or val == "":
break
self._set_last_line(row_index, val)
mpan_core = parse_mpan_core(str(get_int(row, 1)))
comms = get_str(row, 2)
settled_str = get_str(row, 3)
if settled_str == "Settled":
settlement_status = "settlement"
else:
settlement_status = "non_settlement"
start_date = get_start_date(row, 5, self.book.datemode)
finish_date = get_finish_date(row, 6, self.book.datemode)
meter_rate = get_dec(row, 7)
net = round(get_dec(row, 8), 2)
vat = round(get_dec(row, 9), 2)
gross = round(get_dec(row, 10), 2)
breakdown = {
"raw-lines": [str(title_row)],
"comms": comms,
"settlement-status": [settlement_status],
"meter-rate": [meter_rate],
"meter-gbp": net,
}
bills.append({
"bill_type_code":
"N",
"kwh":
Decimal(0),
"net":
net,
"vat":
vat,
"gross":
gross,
"reads": [],
"breakdown":
breakdown,
"account":
mpan_core,
"issue_date":
issue_date,
"start_date":
start_date,
"finish_date":
finish_date,
"mpan_core":
mpan_core,
"reference":
"_".join((
start_date.strftime("%Y%m%d"),
finish_date.strftime("%Y%m%d"),
issue_date.strftime("%Y%m%d"),
mpan_core,
)),
})
except BadRequest as e:
raise BadRequest("Row number: " + str(row_index) + " " +
e.description)
finally:
if sess is not None:
sess.close()
return bills
def make_raw_bills(self):
row_index = None
sess = None
try:
sess = Session()
bills = []
title_row = self.sheet.row(10)
issue_date_str = get_str(self.sheet.row(6), 0)
issue_date = Datetime.strptime(issue_date_str[6:],
"%d/%m/%Y %H:%M:%S")
for row_index in range(11, self.sheet.nrows):
row = self.sheet.row(row_index)
val = get_value(row, 1)
if val is None or val == "":
break
self._set_last_line(row_index, val)
mpan_core = parse_mpan_core(str(get_int(row, 1)))
start_date = get_start_date(row, 3, self.book.datemode)
finish_date = get_start_date(
row, 4, self.book.datemode) + relativedelta(hours=23,
minutes=30)
net = round(get_dec(row, 31), 2)
cop_3_meters = get_int(row, 6)
cop_3_rate = get_dec(row, 7)
cop_3_gbp = get_dec(row, 8)
# Cop 5 meters
get_int(row, 9)
cop_5_rate = get_dec(row, 10)
cop_5_gbp = get_dec(row, 11)
ad_hoc_visits = get_dec(row, 21)
ad_hoc_rate = get_dec(row, 22)
ad_hoc_gbp = get_dec(row, 23)
annual_visits = get_int(row, 27)
annual_rate = get_dec(row, 28)
annual_gbp = get_dec(row, 29)
annual_date = hh_format(
get_start_date(row, 30, self.book.datemode))
if cop_3_meters > 0:
cop = "3"
mpan_rate = cop_3_rate
mpan_gbp = cop_3_gbp
else:
cop = "5"
mpan_rate = cop_5_rate
mpan_gbp = cop_5_gbp
breakdown = {
"raw_lines": [str(title_row)],
"cop": [cop],
"settlement-status": ["settlement"],
"mpan-rate": [mpan_rate],
"mpan-gbp": mpan_gbp,
"ad-hoc-visits": ad_hoc_visits,
"ad-hoc-rate": [ad_hoc_rate],
"ad-hoc-gbp": ad_hoc_gbp,
"annual-visits-count": annual_visits,
"annual-visits-rate": [annual_rate],
"annual-visits-gbp": annual_gbp,
"annual-visits-date": [annual_date],
}
bills.append({
"bill_type_code":
"N",
"kwh":
Decimal(0),
"vat":
Decimal("0.00"),
"net":
net,
"gross":
net,
"reads": [],
"breakdown":
breakdown,
"account":
mpan_core,
"issue_date":
issue_date,
"start_date":
start_date,
"finish_date":
finish_date,
"mpan_core":
mpan_core,
"reference":
"_".join((
start_date.strftime("%Y%m%d"),
finish_date.strftime("%Y%m%d"),
issue_date.strftime("%Y%m%d"),
mpan_core,
)),
})
sess.rollback()
except BadRequest as e:
raise BadRequest("Row number: " + str(row_index) + " " +
e.description)
finally:
if sess is not None:
sess.close()
return bills
def _parse_row(row, row_index, datemode, title_row):
val = get_value(row, "Meter Point")
try:
mpan_core = parse_mpan_core(str(int(val)))
except ValueError as e:
raise BadRequest(
"Can't parse the MPAN core in column 'Meter Point' with value '" +
str(val) + "' : " + str(e))
bill_period = get_value(row, "Bill Period")
if "-" in bill_period:
period_start_naive, period_finish_naive = [
Datetime.strptime(v, "%Y-%m-%d") for v in bill_period.split(" - ")
]
period_start = to_utc(to_ct(period_start_naive))
period_finish = to_utc(
to_ct(period_finish_naive + relativedelta(days=1) - HH))
else:
period_start, period_finish = None, None
from_date = get_date(row, "From Date", datemode)
if from_date is None:
if period_start is None:
raise BadRequest("Can't find a bill start date.")
else:
from_date = period_start
to_date_naive = get_date_naive(row, "To Date", datemode)
if to_date_naive is None:
if period_finish is None:
raise BadRequest("Can't find a bill finish date.")
else:
to_date = period_finish
else:
to_date = to_utc(to_ct(to_date_naive + relativedelta(days=1) - HH))
issue_date = get_date(row, "Bill Date", datemode)
bill_number = get_value(row, "Bill Number")
bill = {
"bill_type_code": "N",
"kwh": Decimal(0),
"vat": Decimal("0.00"),
"net": Decimal("0.00"),
"reads": [],
"breakdown": {
"raw_lines": [str(title_row)]
},
"account": mpan_core,
"issue_date": issue_date,
"start_date": from_date,
"finish_date": to_date,
"mpan_core": mpan_core,
}
bd = bill["breakdown"]
usage = get_dec(row, "Usage")
# usage_units = get_value(row, 'Usage Unit')
price = get_dec(row, "Price")
amount = get_dec(row, "Amount")
product_item_name = get_value(row, "Product Item Name")
rate_name = get_value(row, "Rate Name")
if product_item_name == "Renewables Obligation (RO)" and usage is not None:
bill["kwh"] += round(usage, 2)
description = get_value(row, "Description")
product_class = get_value(row, "Product Item Class")
if description in ("Standard VAT@20%", "Reduced VAT@5%"):
bill["vat"] += round(amount, 2)
else:
bill["net"] += round(amount, 2)
path = [product_class, description, rate_name]
names = _find_names(ELEM_MAP, path)
duos_avail_prefix = "DUoS Availability ("
duos_excess_avail_prefix = "DUoS Excess Availability ("
if description.startswith("DUoS Availability Adjustment "):
_bd_add(bd, "duos-availability-gbp", amount)
elif description.startswith("DUoS Availability"):
if description.startswith(duos_avail_prefix):
_bd_add(
bd,
"duos-availability-kva",
int(description[len(duos_avail_prefix):-5]),
)
_bd_add(bd, "duos-availability-days", usage)
_bd_add(bd, "duos-availability-rate", price)
_bd_add(bd, "duos-availability-gbp", amount)
elif description.startswith("DUoS Excess Availability"):
if description.startswith(duos_excess_avail_prefix):
kva = int(description[len(duos_excess_avail_prefix):-5])
_bd_add(bd, "duos-excess-availability-kva", kva)
_bd_add(bd, "duos-excess-availability-days", usage)
_bd_add(bd, "duos-excess-availability-rate", price)
_bd_add(bd, "duos-excess-availability-gbp", amount)
elif description.startswith("BSUoS Black Start "):
_bd_add(bd, "black-start-gbp", amount)
elif description.startswith("BSUoS Reconciliation - "):
if usage is not None:
_bd_add(bd, "bsuos-nbp-kwh", usage)
if price is not None:
_bd_add(bd, "bsuos-rate", price)
_bd_add(bd, "bsuos-gbp", amount)
elif description.startswith("FiT Rec - "):
_bd_add(bd, "fit-gbp", amount)
elif description.startswith("FiT Reconciliation "):
_bd_add(bd, "fit-gbp", amount)
elif description.startswith(
"CfD FiT Rec - ") or description.startswith(
"CfD FiT Reconciliation"):
_bd_add(bd, "cfd-fit-gbp", amount)
elif description.startswith("Flex"):
_bd_add(bd, "reconciliation-gbp", amount)
elif description.startswith("Legacy TNUoS Reversal "):
_bd_add(bd, "triad-gbp", amount)
elif description.startswith("Hand Held Read -"):
_bd_add(bd, "meter-rental-gbp", amount)
elif description.startswith("RO Mutualisation "):
_bd_add(bd, "ro-gbp", amount)
elif description.startswith("OOC MOP - "):
_bd_add(bd, "meter-rental-gbp", amount)
elif description.startswith("KVa Adjustment "):
_bd_add(bd, "duos-availability-gbp", amount)
elif names is not None:
for elem_k, elem_v in zip(names, (amount, price, usage)):
if elem_k is not None:
_bd_add(bd, elem_k, elem_v)
else:
raise BadRequest(
f"For the path {path} the parser can't work out the element.")
reference = str(bill_number) + "_" + str(row_index + 1)
for k, v in tuple(bd.items()):
if isinstance(v, set):
bd[k] = list(v)
elif k.endswith("-gbp"):
reference += "_" + k[:-4]
bill["reference"] = reference
bill["gross"] = bill["net"] + bill["vat"]
return bill
def make_raw_bills(self):
row_index = sess = None
try:
sess = Session()
bills = []
title_row = self.sheet.row(10)
issue_date = get_date(self.sheet.row(5), 2, self.book.datemode)
if issue_date is None:
raise BadRequest("Expected to find the issue date at cell C6.")
for row_index in range(11, self.sheet.nrows):
row = self.sheet.row(row_index)
val = get_value(row, 1)
if val is None or val == "":
break
self._set_last_line(row_index, val)
mpan_core = parse_mpan_core(str(get_int(row, 1)))
start_date = finish_date = get_date(row, 5, self.book.datemode)
activity_name_raw = get_str(row, 6)
activity_name = activity_name_raw.lower().replace(" ", "_")
net_dec = get_dec(row, 8)
if net_dec is None:
raise BadRequest(
"Can't find a decimal at column I, expecting the net " "GBP."
)
net = round(net_dec, 2)
vat_dec = get_dec(row, 9)
if vat_dec is None:
raise BadRequest(
"Can't find a decimal at column J, expecting the VAT " "GBP."
)
vat = round(vat_dec, 2)
gross_dec = get_dec(row, 10)
if gross_dec is None:
raise BadRequest(
"Can't find a decimal at column K, expecting the " "gross GBP."
)
gross = round(gross_dec, 2)
breakdown = {
"raw-lines": [str(title_row)],
"activity-name": [activity_name],
"activity-gbp": net,
}
bills.append(
{
"bill_type_code": "N",
"kwh": Decimal(0),
"vat": vat,
"net": net,
"gross": gross,
"reads": [],
"breakdown": breakdown,
"account": mpan_core,
"issue_date": issue_date,
"start_date": start_date,
"finish_date": finish_date,
"mpan_core": mpan_core,
"reference": "_".join(
(
start_date.strftime("%Y%m%d"),
finish_date.strftime("%Y%m%d"),
issue_date.strftime("%Y%m%d"),
mpan_core,
)
),
}
)
except BadRequest as e:
raise BadRequest("Row number: " + str(row_index) + " " + e.description)
finally:
if sess is not None:
sess.close()
return bills
def make_raw_bills(self):
row_index = None
sess = None
try:
sess = Session()
bills = []
title_row = self.sheet.row(0)
for row_index in range(1, self.sheet.nrows):
row = self.sheet.row(row_index)
val = get_value(title_row, row, 'mpan ref')
if val is None or val == '':
break
self._set_last_line(row_index, val)
msn = str(get_value(title_row, row, 'meter')).strip()
mpan_core = parse_mpan_core(
str(get_int(title_row, row, 'mpan ref')))
start_date = get_date(
title_row, row, 'start', self.book.datemode)
issue_date = start_date
finish_date = get_date(
title_row, row, 'end', self.book.datemode) + Timedelta(
days=1) - HH
check = get_str(title_row, row, 'check')
if check != 'Billed':
continue
era = sess.query(Era).filter(
or_(
Era.imp_mpan_core == mpan_core,
Era.exp_mpan_core == mpan_core),
Era.start_date <= finish_date, or_(
Era.finish_date == null(),
Era.finish_date > start_date)).order_by(
Era.start_date).first()
if era is None:
era = sess.query(Era).filter(
or_(
Era.imp_mpan_core == mpan_core,
Era.exp_mpan_core == mpan_core)).order_by(
Era.start_date.desc()).first()
if era is None:
account = mpan_core + '/DC'
else:
account = era.dc_account
net = METER_RATE / 12
breakdown = {
'raw_lines': [str(title_row)], 'cop': ['5'],
'settlement-status': ['non_settlement'], 'msn': [msn],
'meter-rate': [METER_RATE], 'meter-gbp': net,
}
bills.append(
{
'bill_type_code': 'N', 'kwh': Decimal(0),
'vat': Decimal('0.00'), 'net': net, 'gross': net,
'reads': [], 'breakdown': breakdown,
'account': account, 'issue_date': issue_date,
'start_date': start_date, 'finish_date': finish_date,
'mpans': [mpan_core], 'reference': '_'.join(
(
start_date.strftime('%Y%m%d'),
finish_date.strftime('%Y%m%d'),
issue_date.strftime('%Y%m%d'),
mpan_core
)
)
}
)
sess.rollback()
except BadRequest as e:
raise BadRequest(
"Row number: " + str(row_index) + " " + e.description)
finally:
if sess is not None:
sess.close()
return bills
def make_raw_bills(self):
row_index = sess = None
try:
sess = Session()
bills = []
title_row = self.sheet.row(10)
issue_date = get_date(self.sheet.row(5), 2, self.book.datemode)
if issue_date is None:
raise BadRequest("Expected to find the issue date at cell C6.")
for row_index in range(11, self.sheet.nrows):
row = self.sheet.row(row_index)
val = get_value(row, 1)
if val is None or val == '':
break
self._set_last_line(row_index, val)
mpan_core = parse_mpan_core(str(get_int(row, 1)))
start_date = finish_date = get_date(row, 5, self.book.datemode)
activity_name_raw = get_str(row, 6)
activity_name = activity_name_raw.lower().replace(' ', '_')
net_dec = get_dec(row, 8)
if net_dec is None:
raise BadRequest(
"Can't find a decimal at column I, expecting the net "
"GBP.")
net = round(net_dec, 2)
era = sess.query(Era).filter(
or_(Era.imp_mpan_core == mpan_core,
Era.exp_mpan_core == mpan_core),
Era.start_date <= finish_date,
or_(Era.finish_date == null(),
Era.finish_date > start_date)).order_by(
Era.start_date).first()
if era is None:
era = sess.query(Era).filter(
or_(Era.imp_mpan_core == mpan_core,
Era.exp_mpan_core == mpan_core)).order_by(
Era.start_date.desc()).first()
if era is None:
account = mpan_core + '/MOP'
else:
account = era.mop_account
breakdown = {
'raw-lines': [str(title_row)],
'activity-name': [activity_name],
'activity-gbp': net
}
bills.append({
'bill_type_code':
'N',
'kwh':
Decimal(0),
'vat':
Decimal('0.00'),
'net':
net,
'gross':
net,
'reads': [],
'breakdown':
breakdown,
'account':
account,
'issue_date':
issue_date,
'start_date':
start_date,
'finish_date':
finish_date,
'mpans': [mpan_core],
'reference':
'_'.join((start_date.strftime('%Y%m%d'),
finish_date.strftime('%Y%m%d'),
issue_date.strftime('%Y%m%d'), mpan_core))
})
except BadRequest as e:
raise BadRequest("Row number: " + str(row_index) + " " +
e.description)
finally:
if sess is not None:
sess.close()
return bills
def _process_MPAN(row, headers):
dno = row[3]
unique = row[4].zfill(10)
check_digit = row[5]
headers["mpan_core"] = parse_mpan_core("".join([dno, unique, check_digit]))
def make_raw_bills(self):
raw_bills = []
next(iter(self.reader)) # skip title row
blank_set = set(("", ))
for self.line_number, self.vals in enumerate(self.reader):
# skip blank lines
if len(self.vals) == 0 or set(self.vals) == blank_set:
continue
bill_type_code = self.vals[0]
if bill_type_code.startswith("#"):
continue # skip comment lines
account = self.vals[1]
mpan_core = parse_mpan_core(self.vals[2])
reference = self.vals[3]
issue_date = parse_date(self.vals, 4)
start_date = parse_date(self.vals, 5)
finish_date = parse_date(self.vals, 6, True)
kwh = self.to_decimal(7, "kwh")
net = self.to_decimal(8, "net", True)
vat = self.to_decimal(9, "vat", True)
gross = self.to_decimal(10, "gross", True)
if len(self.vals) > 11:
breakdown_str = self.vals[11].strip()
if len(breakdown_str) == 0:
breakdown = {}
else:
try:
breakdown = loads(breakdown_str)
except SyntaxError as e:
raise BadRequest(str(e))
else:
raise BadRequest(
f"For the line, {self.vals} there isn't a 'breakdown' "
f"field on the end.")
while self.vals[-1] == "" and len(self.vals) > 12:
del self.vals[-1]
reads = []
for i in range(12, len(self.vals), 11):
tpr_str = self.vals[i + 4].strip()
tpr_code = None if len(tpr_str) == 0 else tpr_str.zfill(5)
reads.append({
"msn":
self.vals[i],
"mpan":
self.vals[i + 1],
"coefficient":
self.to_decimal(i + 2, "coefficient"),
"units":
self.vals[i + 3],
"tpr_code":
tpr_code,
"prev_date":
parse_date(self.vals, i + 5),
"prev_value":
Decimal(self.vals[i + 6]),
"prev_type_code":
self.vals[i + 7],
"pres_date":
parse_date(self.vals, i + 8),
"pres_value":
Decimal(self.vals[i + 9]),
"pres_type_code":
self.vals[i + 10],
})
raw_bill = {
"bill_type_code": bill_type_code,
"account": account,
"mpan_core": mpan_core,
"reference": reference,
"issue_date": issue_date,
"start_date": start_date,
"finish_date": finish_date,
"kwh": kwh,
"net": net,
"vat": vat,
"gross": gross,
"breakdown": breakdown,
"reads": reads,
}
raw_bills.append(raw_bill)
return raw_bills
def make_raw_bills(self):
row_index = sess = None
try:
sess = Session()
bills = []
title_row = self.sheet.row(10)
issue_date = get_date(self.sheet.row(5), 2, self.book.datemode)
if issue_date is None:
raise BadRequest("Expected to find the issue date at cell C6.")
for row_index in range(11, self.sheet.nrows):
row = self.sheet.row(row_index)
val = get_value(row, 1)
if val is None or val == '':
break
self._set_last_line(row_index, val)
mpan_core = parse_mpan_core(str(get_int(row, 1)))
comms = get_str(row, 2)
settled_str = get_str(row, 3)
if settled_str == 'Settled':
settlement_status = 'settlement'
else:
settlement_status = 'non_settlement'
start_date = get_date(row, 5, self.book.datemode)
finish_date = get_date(
row, 6, self.book.datemode) + Timedelta(days=1) - HH
meter_rate = get_dec(row, 7)
net = round(get_dec(row, 8), 2)
era = sess.query(Era).filter(
or_(Era.imp_mpan_core == mpan_core,
Era.exp_mpan_core == mpan_core),
Era.start_date <= finish_date,
or_(Era.finish_date == null(),
Era.finish_date > start_date)).order_by(
Era.start_date).first()
if era is None:
era = sess.query(Era).filter(
or_(Era.imp_mpan_core == mpan_core,
Era.exp_mpan_core == mpan_core)).order_by(
Era.start_date.desc()).first()
if era is None:
account = mpan_core + '/MOP'
else:
account = era.mop_account
breakdown = {
'raw-lines': [str(title_row)],
'comms': comms,
'settlement-status': [settlement_status],
'meter-rate': [meter_rate],
'meter-gbp': net
}
bills.append({
'bill_type_code':
'N',
'kwh':
Decimal(0),
'vat':
Decimal('0.00'),
'net':
net,
'gross':
net,
'reads': [],
'breakdown':
breakdown,
'account':
account,
'issue_date':
issue_date,
'start_date':
start_date,
'finish_date':
finish_date,
'mpans': [mpan_core],
'reference':
'_'.join((start_date.strftime('%Y%m%d'),
finish_date.strftime('%Y%m%d'),
issue_date.strftime('%Y%m%d'), mpan_core))
})
except BadRequest as e:
raise BadRequest("Row number: " + str(row_index) + " " +
e.description)
finally:
if sess is not None:
sess.close()
return bills
def _parse_row(row, row_index, datemode, title_row):
val = get_value(row, 'Meter Point')
try:
mpan_core = parse_mpan_core(str(int(val)))
except ValueError as e:
raise BadRequest(
"Can't parse the MPAN core in column 'Meter Point' at row " +
str(row_index + 1) + " with value '" + str(val) + "' : " + str(e))
bill_period = get_value(row, 'Bill Period')
if '-' in bill_period:
period_start, period_finish = [
to_utc(to_ct(Datetime.strptime(d, '%Y-%m-%d')))
for d in bill_period.split(' - ')
]
period_finish += relativedelta(days=1) - HH
else:
period_start, period_finish = None, None
from_date = get_date(row, 'From Date', datemode)
if from_date is None:
if period_start is None:
raise BadRequest("Can't find a bill finish date in row " +
str(row_index) + ".")
else:
from_date = period_start
to_date = get_date(row, 'To Date', datemode)
if to_date is None:
if period_finish is None:
raise BadRequest("Can't find a bill finish date in row " +
str(row_index) + " .")
else:
to_date = period_finish
else:
to_date += relativedelta(days=1) - HH
issue_date = get_date(row, 'Bill Date', datemode)
bill_number = get_value(row, 'Bill Number')
bill = {
'bill_type_code': 'N',
'kwh': Decimal(0),
'vat': Decimal('0.00'),
'net': Decimal('0.00'),
'reads': [],
'breakdown': {
'raw_lines': [str(title_row)]
},
'account': mpan_core,
'issue_date': issue_date,
'start_date': from_date,
'finish_date': to_date,
'mpans': [mpan_core],
}
bd = bill['breakdown']
usage = get_dec(row, 'Usage')
# usage_units = get_value(row, 'Usage Unit')
price = get_dec(row, 'Price')
amount = get_dec(row, 'Amount')
product_item_name = get_value(row, 'Product Item Name')
rate_name = get_value(row, 'Rate Name')
if product_item_name == 'Renewables Obligation (RO)':
bill['kwh'] += round(usage, 2)
description = get_value(row, 'Description')
product_class = get_value(row, 'Product Item Class')
if description in ('Standard VAT@20%', 'Reduced VAT@5%'):
bill['vat'] += round(amount, 2)
else:
bill['net'] += round(amount, 2)
path = [product_class, description, rate_name]
names = _find_names(ELEM_MAP, path)
if names is None:
duos_avail_prefix = "DUoS Availability ("
duos_excess_avail_prefix = "DUoS Excess Availability ("
if description.startswith("DUoS Availability"):
if description.startswith(duos_avail_prefix):
bd_add(bd, 'duos-availability-kva',
int(description[len(duos_avail_prefix):-5]))
bd_add(bd, 'duos-availability-days', usage)
bd_add(bd, 'duos-availability-rate', price)
bd_add(bd, 'duos-availability-gbp', amount)
elif description.startswith("DUoS Excess Availability"):
if description.startswith(duos_excess_avail_prefix):
kva = int(description[len(duos_excess_avail_prefix):-5])
bd_add(bd, 'duos-excess-availability-kva', kva)
bd_add(bd, 'duos-excess-availability-days', usage)
bd_add(bd, 'duos-excess-availability-rate', price)
bd_add(bd, 'duos-excess-availability-gbp', amount)
elif description.startswith('BSUoS Black Start '):
bd_add(bd, 'black-start-gbp', amount)
elif description.startswith('BSUoS Reconciliation - '):
if usage is not None:
bd_add(bd, 'bsuos-nbp-kwh', usage)
if price is not None:
bd_add(bd, 'bsuos-rate', price)
bd_add(bd, 'bsuos-gbp', amount)
elif description.startswith("FiT Rec - "):
bd_add(bd, 'fit-gbp', amount)
elif description.startswith("FiT Reconciliation "):
bd_add(bd, 'fit-gbp', amount)
elif description.startswith("CfD FiT Rec - "):
bd_add(bd, 'cfd-fit-gbp', amount)
elif description.startswith("Flex "):
bd_add(bd, 'reconciliation-gbp', amount)
elif description.startswith("Legacy TNUoS Reversal "):
bd_add(bd, 'triad-gbp', amount)
elif description.startswith("Hand Held Read -"):
bd_add(bd, 'meter-rental-gbp', amount)
elif description.startswith("RO Mutualisation "):
bd_add(bd, 'ro-gbp', amount)
elif description.startswith("OOC MOP - "):
bd_add(bd, 'meter-rental-gbp', amount)
elif description.startswith("KVa Adjustment "):
bd_add(bd, 'duos-availability-gbp', amount)
else:
raise BadRequest("For the path " + str(path) +
" the parser can't work out the element.")
else:
for elem_k, elem_v in zip(names, (usage, price, amount)):
if elem_k is not None:
bd_add(bd, elem_k, elem_v)
reference = str(bill_number) + '_' + str(row_index + 1)
for k, v in tuple(bd.items()):
if isinstance(v, set):
bd[k] = list(v)
elif k.endswith("-gbp"):
reference += "_" + k[:-4]
bill['reference'] = reference
bill['gross'] = bill['net'] + bill['vat']
return bill
def make_raw_bills(self):
raw_bills = []
last_key = None
title_row = self.sheet.row(0)
for row_index in range(1, self.sheet.nrows):
row = self.sheet.row(row_index)
mpan_core = parse_mpan_core(
str(int(get_value(row, 'Meter Point'))))
bill_period = get_value(row, 'Bill Period')
start_date, finish_date = [
to_utc(to_ct(Datetime.strptime(d, '%Y-%m-%d')))
for d in bill_period.split(' - ')]
finish_date = finish_date + relativedelta(days=1) - HH
key = (start_date, finish_date, mpan_core)
from_date = get_date(row, 'From Date', self.book.datemode)
# to_date = get_date(row, 'To Date', self.book.datemode) + \
# relativedelta(days=1) - HH
issue_date = get_date(row, 'Bill Date', self.book.datemode)
if last_key != key:
last_key = key
bd = {}
bill = {
'bill_type_code': 'N', 'account': mpan_core,
'mpans': [mpan_core],
'reference': '_'.join(
(
start_date.strftime('%Y%m%d'),
finish_date.strftime('%Y%m%d'), mpan_core)),
'issue_date': issue_date, 'start_date': start_date,
'finish_date': finish_date, 'kwh': Decimal(0),
'net': Decimal('0.00'), 'vat': Decimal('0.00'),
'breakdown': bd, 'reads': []}
raw_bills.append(bill)
usage = get_value(row, 'Usage')
usage_units = get_value(row, 'Usage Unit')
price = get_value(row, 'Price')
amount = get_value(row, 'Amount')
amount_dec = Decimal(amount)
product_item_name = get_value(row, 'Product Item Name')
rate_name = get_value(row, 'Rate Name')
if usage_units == 'kWh':
if product_item_name == 'Renewables Obligation (RO)':
bill['kwh'] += round(Decimal(usage), 2)
elif product_item_name == "Unit Rate":
bd_add(bd, 'sum-gsp-kwh', usage)
description = get_value(row, 'Description')
if description == 'Standard VAT@20%':
bill['vat'] += round(amount_dec, 2)
else:
bill['net'] += round(amount_dec, 2)
for q, qname in (
(usage, 'Usage'), (price, 'Price'), (amount, 'Amount')):
try:
elem_key = ELEM_MAP[(description, rate_name, qname)]
bd_add(bd, elem_key, q)
except KeyError:
pass
duos_avail_prefix = "DUoS Availability ("
duos_excess_avail_prefix = "DUoS Excess Availability ("
if description.startswith("DUoS Availability"):
if description.startswith(duos_avail_prefix):
bd_add(
bd, 'duos-availability-kva',
int(description[len(duos_avail_prefix):-5]))
bd_add(bd, 'duos-availability-days', usage)
bd_add(bd, 'duos-availability-rate', price)
bd_add(bd, 'duos-availability-gbp', amount)
elif description.startswith("DUoS Excess Availability"):
if description.startswith(duos_excess_avail_prefix):
bd_add(
bd, 'duos-excess-availability-kva',
int(description[len(duos_excess_avail_prefix):-5]))
bd_add(bd, 'duos-excess-availability-days', usage)
bd_add(bd, 'duos-excess-availability-rate', price)
bd_add(bd, 'duos-excess-availability-gbp', amount)
elif description == 'Balancing Services Use of System (BSUoS)':
if from_date == start_date:
bd_add(bd, 'bsuos-estimated-nbp-kwh', usage)
bd_add(bd, 'bsuos-estimated-rate', price)
bd_add(bd, 'bsuos-estimated-gbp', amount)
elif amount < 0:
bd_add(bd, 'bsuos-prev-estimated-nbp-kwh', usage)
bd_add(bd, 'bsuos-prev-estimated-rate', price)
bd_add(bd, 'bsuos-prev-estimated-gbp', amount)
else:
bd_add(bd, 'bsuos-prev-sf-nbp-kwh', usage)
bd_add(bd, 'bsuos-prev-sf-rate', price)
bd_add(bd, 'bsuos-prev-sf-gbp', amount)
elif description.startswith("FiT Rec - "):
bd_add(bd, 'fit-reconciliation-gbp', amount)
elif description.startswith("CfD FiT Rec - "):
bd_add(bd, 'cfd-fit-reconciliation-gbp', amount)
bd['raw_lines'] = [str(title_row), str(row)]
bill['gross'] = bill['net'] + bill['vat']
for raw_bill in raw_bills:
bd = raw_bill['breakdown']
for k, v in tuple(bd.items()):
if isinstance(v, set):
val = ', '.join(sorted(map(str, v)))
else:
val = v
bd[k] = val
return raw_bills
