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