def find_elements(bill): try: keys = [k for k in loads(bill.breakdown).keys() if k.endswith("-gbp")] return set(k[:-4] for k in keys) except ZishLocationException as e: raise BadRequest("Can't parse the breakdown for bill id " + str(bill.id) + " attached to batch id " + str(bill.batch.id) + ": " + str(e))
def g_rates(sess, caches, g_contract_id, date): try: return caches['g_engine']['rates'][g_contract_id][date] except KeyError: try: ccache = caches['g_engine'] except KeyError: ccache = caches['g_engine'] = {} try: rss_cache = ccache['rates'] except KeyError: rss_cache = ccache['rates'] = {} try: cont_cache = rss_cache[g_contract_id] except KeyError: cont_cache = rss_cache[g_contract_id] = {} try: return cont_cache[date] except KeyError: month_after = date + relativedelta(months=1) + relativedelta( days=1) month_before = date - relativedelta(months=1) - relativedelta( days=1) rs = sess.query(GRateScript).filter( GRateScript.g_contract_id == g_contract_id, GRateScript.start_date <= date, or_(GRateScript.finish_date == null(), GRateScript.finish_date >= date)).first() if rs is None: rs = sess.query(GRateScript).filter( GRateScript.g_contract_id == g_contract_id).order_by( GRateScript.start_date.desc()).first() if date < rs.start_date: cstart = month_before cfinish = min(month_after, rs.start_date - HH) else: cstart = max(rs.finish_date + HH, month_before) cfinish = month_after else: cstart = max(rs.start_date, month_before) if rs.finish_date is None: cfinish = month_after else: cfinish = min(rs.finish_date, month_after) vals = PropDict( "the local rate script for contract " + str(g_contract_id) + " at " + hh_format(cstart) + ".", loads(rs.script), []) for dt in hh_range(caches, cstart, cfinish): if dt not in cont_cache: cont_cache[dt] = vals return vals
def get_file_script(caches, contract_name, date): try: return caches['contract_names'][contract_name]['rs'][date] except KeyError: try: contract_names = caches['contract_names'] except KeyError: contract_names = caches['contract_names'] = {} try: cont_cache = contract_names[contract_name] except KeyError: cont_cache = contract_names[contract_name] = {} try: cont = cont_cache['rs'] except KeyError: cont = cont_cache['rs'] = {} scripts = get_file_scripts(contract_name) for i, (start_date, finish_date, script_str) in enumerate(scripts): if i + 1 == len(scripts): sfinish = finish_date else: sfinish = scripts[i + 1][0] - HH if date >= start_date and not hh_after(date, sfinish): try: rs = start_date, sfinish, loads(script_str) except ZishException as e: raise BadRequest( "In the rate script " + url_root + 'industry_contracts/' + contract_name + '/rate_scripts/' + start_date.strftime("%Y%m%d%H%M") + " there's the problem " + str(e) + ".") begin_date = hh_max(date - YEAR, start_date) end_date = hh_min(date + YEAR, sfinish) for hh_date in hh_range(caches, begin_date, end_date): cont[hh_date] = rs scripts_start = scripts[0][0] if date < scripts_start: end_date = hh_min(date + YEAR, scripts_start - HH) for hh_date in hh_range(caches, date - YEAR, end_date): cont[hh_date] = None scripts_finish = scripts[-1][1] if hh_after(date, scripts_finish): start_date = hh_max(date - YEAR, scripts_finish + HH) for hh_date in hh_range(caches, start_date, date + YEAR): cont[hh_date] = None return cont[date]
def test_with_scenario(mocker, sess, client): mock_Thread = mocker.patch("chellow.reports.report_247.threading.Thread") properties = """{ "scenario_start_year": 2009, "scenario_start_month": 8, "scenario_duration": 1, "era_maps": { 2000-08-01T00:00:00Z: { "llfcs": { "22": { "new_export": "521" } }, "supplier_contracts": { "new_export": 10 } } }, "hh_data": { "CI017": { "generated": " 2009-08-01 00:00, 40 2009-08-15 00:00, 40" } } }""" scenario_props = loads(properties) scenario = Scenario.insert(sess, "New Gen", scenario_props) sess.commit() now = utc_datetime(2020, 1, 1) mocker.patch("chellow.reports.report_247.utc_datetime_now", return_value=now) site_code = "CI017" site = Site.insert(sess, site_code, "Water Works") data = { "site_id": site.id, "scenario_id": scenario.id, "compression": False, } response = client.post("/reports/247", data=data) match(response, 303) base_name = ["New Gen"] args = scenario_props, base_name, site.id, None, None, False, [], now mock_Thread.assert_called_with(target=content, args=args)
def _process_month(log_f, sess, contract, latest_rs, month_start, month_finish): latest_rs_id = latest_rs.id log_f( f"Checking to see if data is available from {hh_format(month_start)} " f"to {hh_format(month_finish)} on BMRS." ) rates = {} month_finish_ct = to_ct(month_finish) for d in range(month_finish_ct.day): day_ct = ct_datetime(month_finish_ct.year, month_finish_ct.month, d + 1) params = { "q": f"ajax/alldata/MID/Date,SP,Provider,Price,Volume/NULL/" f'{day_ct.strftime("%Y-%m-%d")}/ALL' } r = requests.get( "https://www.bmreports.com/bmrs/", params=params, timeout=60, verify=False ) res = r.json() for h in res["arr"]: dt = to_utc(day_ct + (int(h["settlementPeriod"]) - 1) * HH) try: rate = rates[dt] except KeyError: rate = rates[dt] = {} rate[h["DataProviderId"]] = Decimal(h["MarketIndexPrice"]) / Decimal(1000) if month_finish in rates: log_f("The whole month's data is there.") script = {"rates": rates} rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script( sess, rs, rs.start_date, month_finish, loads(rs.script) ) contract.insert_rate_script(sess, month_start, script) sess.commit() log_f(f"Added a new rate script starting at {hh_format(month_start)}.") else: msg = "There isn't a whole month there yet." if len(rates) > 0: msg += " The last date is {sorted(rates.keys())[-1]}" log_f(msg)
def run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = None try: sess = Session() self.log("Starting to check RCRCs.") contract = Contract.get_non_core_by_name(sess, "rcrc") latest_rs = ( sess.query(RateScript) .filter(RateScript.contract_id == contract.id) .order_by(RateScript.start_date.desc()) .first() ) latest_rs_id = latest_rs.id latest_rs_start = latest_rs.start_date month_start = latest_rs_start + relativedelta(months=1) month_finish = month_start + relativedelta(months=1) - HH now = utc_datetime_now() if now > month_finish: self.log( "Checking to see if data is available from " + hh_format(month_start) + " to " + hh_format(month_finish) + " on Elexon Portal." ) config = Contract.get_non_core_by_name(sess, "configuration") props = config.make_properties() scripting_key = props.get(ELEXON_PORTAL_SCRIPTING_KEY_KEY) if scripting_key is None: raise BadRequest( "The property " + ELEXON_PORTAL_SCRIPTING_KEY_KEY + " cannot be found in the configuration " "properties." ) contract_props = contract.make_properties() url_str = "".join( ( contract_props["url"], "file/download/RCRC_FILE?key=", scripting_key, ) ) r = requests.get(url_str, timeout=60) parser = csv.reader( (x.decode() for x in r.iter_lines()), delimiter=",", quotechar='"', ) next(parser) next(parser) month_rcrcs = {} for values in parser: hh_date = utc_datetime_parse(values[0], "%d/%m/%Y") hh_date += relativedelta(minutes=30 * int(values[2])) if month_start <= hh_date <= month_finish: month_rcrcs[key_format(hh_date)] = Decimal(values[3]) if key_format(month_finish) in month_rcrcs: self.log("The whole month's data is there.") script = {"rates": month_rcrcs} contract = Contract.get_non_core_by_name(sess, "rcrc") rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script( sess, rs, rs.start_date, month_finish, loads(rs.script) ) contract.insert_rate_script(sess, month_start, script) sess.commit() self.log( "Added a new rate script starting at " + hh_format(month_start) + "." ) else: msg = "There isn't a whole month there yet." if len(month_rcrcs) > 0: msg += ( " The last date is " + sorted(month_rcrcs.keys())[-1] ) self.log(msg) except BaseException: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: self.lock.release() self.log("Finished checking RCRC rates.") if sess is not None: sess.close() self.going.wait(30 * 60) self.going.clear()
def req_zish(name): try: return loads(req_str(name)) except ZishException as e: raise BadRequest( "Problem parsing the field " + name + " as Zish: " + str(e))
def process_url(self, sess, url, contract): self.log("Checking to see if there's any new data at " + url) res = requests.get(url) self.log("Received " + str(res.status_code) + " " + res.reason) book = xlrd.open_workbook(file_contents=res.content) sheet = book.sheet_by_index(0) cache = {} for row_index in range(1, sheet.nrows): row = sheet.row(row_index) raw_date_val = row[0].value if isinstance(raw_date_val, float): raw_date = Datetime( *xlrd.xldate_as_tuple(raw_date_val, book.datemode)) elif isinstance(raw_date_val, str): separator = raw_date_val[2] fmat = separator.join(("%d", "%m", "%Y")) raw_date = Datetime.strptime(raw_date_val, fmat) else: raise BadRequest("Type of date field " + str(raw_date_val) + " not recognized.") hh_date_ct = to_ct(raw_date) hh_date_ct += relativedelta(minutes=30 * (int(row[1].value) - 1)) hh_date = to_utc(hh_date_ct) price = Decimal(str(row[2].value)) run = row[5].value try: rs, rates, rts = cache[hh_date.year][hh_date.month] except KeyError: _save_cache(sess, cache) try: yr_cache = cache[hh_date.year] except KeyError: yr_cache = cache[hh_date.year] = {} rs = sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date <= hh_date, or_(RateScript.finish_date == null(), RateScript.finish_date >= hh_date)).first() while rs is None: self.log("There's no rate script at " + hh_format(hh_date) + ".") latest_rs = sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()).first() contract.update_rate_script( sess, latest_rs, latest_rs.start_date, latest_rs.start_date + relativedelta(months=2) - HH, loads(latest_rs.script)) new_rs_start = latest_rs.start_date + relativedelta( months=1) contract.insert_rate_script(sess, new_rs_start, {}) sess.commit() self.log("Added a rate script starting at " + hh_format(new_rs_start) + ".") rs = sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date <= hh_date, or_(RateScript.finish_date == null(), RateScript.finish_date >= hh_date)).first() rates = loads(rs.script) try: rts = rates['rates_gbp_per_mwh'] except KeyError: rts = rates['rates_gbp_per_mwh'] = {} yr_cache[hh_date.month] = rs, rates, rts key = key_format(hh_date) try: existing = rts[key] except KeyError: existing = rts[key] = {} if run not in existing: existing[run] = price self.log("Added rate at " + hh_format(hh_date) + " for run " + run + ".") _save_cache(sess, cache) book = sheet = None
def run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = None try: sess = Session() self.log("Starting to check bank holidays") contract = Contract.get_non_core_by_name(sess, "bank_holidays") contract_props = contract.make_properties() if contract_props.get("enabled", False): url_str = contract_props["url"] self.log("Downloading from " + url_str + ".") res = requests.get(url_str) self.log( " ".join(("Received", str(res.status_code), res.reason)) ) PREFIX = "DTSTART;VALUE=DATE:" hols = collections.defaultdict(list) for line in res.text.splitlines(): if line.startswith(PREFIX): dt = utc_datetime_parse(line[-8:], "%Y%m%d") hols[dt.year].append(dt) for year in sorted(hols.keys()): year_start = utc_datetime(year, 1, 1) year_finish = year_start + relativedelta(years=1) - HH rs = ( sess.query(RateScript) .filter( RateScript.contract == contract, RateScript.start_date == year_start, ) .first() ) if rs is None: self.log( "Adding a new rate script starting at " + hh_format(year_start) + "." ) latest_rs = ( sess.query(RateScript) .filter(RateScript.contract == contract) .order_by(RateScript.start_date.desc()) .first() ) contract.update_rate_script( sess, latest_rs, latest_rs.start_date, year_finish, loads(latest_rs.script), ) rs = contract.insert_rate_script(sess, year_start, {}) script = { "bank_holidays": [ v.strftime("%Y-%m-%d") for v in hols[year] ] } contract.update_rate_script( sess, rs, rs.start_date, rs.finish_date, script ) sess.commit() self.log( "Updated rate script starting at " + hh_format(year_start) + "." ) else: self.log( "The automatic importer is disabled. To " "enable it, edit the contract properties to " "set 'enabled' to True." ) except BaseException: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: if sess is not None: sess.close() self.lock.release() self.log("Finished checking bank holidays.") self.going.wait(24 * 60 * 60) self.going.clear()
def g_rates(sess, caches, g_contract_id, date): try: return caches["g_engine"]["rates"][g_contract_id][date] except KeyError: try: ccache = caches["g_engine"] except KeyError: ccache = caches["g_engine"] = {} try: rss_cache = ccache["rates"] except KeyError: rss_cache = ccache["rates"] = {} try: cont_cache = rss_cache[g_contract_id] except KeyError: cont_cache = rss_cache[g_contract_id] = {} try: return cont_cache[date] except KeyError: month_after = date + relativedelta(months=1) + relativedelta(days=1) month_before = date - relativedelta(months=1) - relativedelta(days=1) rs = ( sess.query(GRateScript) .filter( GRateScript.g_contract_id == g_contract_id, GRateScript.start_date <= date, or_( GRateScript.finish_date == null(), GRateScript.finish_date >= date, ), ) .first() ) if rs is None: rs = ( sess.query(GRateScript) .filter(GRateScript.g_contract_id == g_contract_id) .order_by(GRateScript.start_date.desc()) .first() ) if date < rs.start_date: cstart = month_before cfinish = min(month_after, rs.start_date - HH) else: cstart = max(rs.finish_date + HH, month_before) cfinish = month_after else: cstart = max(rs.start_date, month_before) if rs.finish_date is None: cfinish = month_after else: cfinish = min(rs.finish_date, month_after) vals = PropDict( "the rate script " + chellow.utils.url_root + "g_rate_scripts/" + str(rs.id) + " ", loads(rs.script), [], ) for dt in hh_range(caches, cstart, cfinish): if dt not in cont_cache: cont_cache[dt] = vals return vals
def _process_supply( sess, caches, supply_id, bill_map, forecast_date, contract, vbf, virtual_bill_titles, writer, titles, report_run, ): gaps = {} data_sources = {} market_role_code = contract.market_role.code bill_ids = bill_map[supply_id] while len(bill_ids) > 0: bill_id = list(sorted(bill_ids))[0] bill_ids.remove(bill_id) bill = (sess.query(Bill).filter(Bill.id == bill_id).options( joinedload(Bill.batch), joinedload(Bill.bill_type), joinedload(Bill.reads), joinedload(Bill.supply), joinedload(Bill.reads).joinedload(RegisterRead.present_type), joinedload(Bill.reads).joinedload(RegisterRead.previous_type), ).one()) virtual_bill = {"problem": ""} supply = bill.supply read_dict = {} for read in bill.reads: gen_start = read.present_date.replace(hour=0).replace(minute=0) gen_finish = gen_start + relativedelta(days=1) - HH msn_match = False read_msn = read.msn for read_era in supply.find_eras(sess, gen_start, gen_finish): if read_msn == read_era.msn: msn_match = True break if not msn_match: virtual_bill["problem"] += ( "The MSN " + read_msn + " of the register read " + str(read.id) + " doesn't match the MSN of the era.") for dt, typ in [ (read.present_date, read.present_type), (read.previous_date, read.previous_type), ]: key = str(dt) + "-" + read.msn try: if typ != read_dict[key]: virtual_bill["problem"] += ( " Reads taken " + "on " + str(dt) + " have differing read types.") except KeyError: read_dict[key] = typ bill_start = bill.start_date bill_finish = bill.finish_date covered_start = bill_start covered_finish = bill_start covered_bdown = {"sum-msp-kwh": 0, "net-gbp": 0, "vat-gbp": 0} vb_elems = set() enlarged = True while enlarged: enlarged = False covered_elems = find_elements(bill) covered_bills = OrderedDict((b.id, b) for b in sess.query( Bill).join(Batch).join(Contract).join(MarketRole).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start, MarketRole.code == market_role_code, ).order_by(Bill.start_date, Bill.issue_date)) while True: to_del = None for a, b in combinations(covered_bills.values(), 2): if all(( a.start_date == b.start_date, a.finish_date == b.finish_date, a.kwh == -1 * b.kwh, a.net == -1 * b.net, a.vat == -1 * b.vat, a.gross == -1 * b.gross, )): to_del = (a.id, b.id) break if to_del is None: break else: for k in to_del: del covered_bills[k] bill_ids.discard(k) for k, covered_bill in tuple(covered_bills.items()): elems = find_elements(covered_bill) if elems.isdisjoint(covered_elems): if k != bill.id: del covered_bills[k] continue else: covered_elems.update(elems) if covered_bill.start_date < covered_start: covered_start = covered_bill.start_date enlarged = True break if covered_bill.finish_date > covered_finish: covered_finish = covered_bill.finish_date enlarged = True break if len(covered_bills) == 0: continue primary_covered_bill = None for covered_bill in covered_bills.values(): bill_ids.discard(covered_bill.id) covered_bdown["net-gbp"] += float(covered_bill.net) covered_bdown["vat-gbp"] += float(covered_bill.vat) covered_bdown["sum-msp-kwh"] += float(covered_bill.kwh) covered_rates = defaultdict(set) for k, v in loads(covered_bill.breakdown).items(): if k in ("raw_lines", "raw-lines"): continue if isinstance(v, list): covered_rates[k].update(set(v)) else: if isinstance(v, Decimal): v = float(v) try: covered_bdown[k] += v except KeyError: covered_bdown[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " in " + str([b.id for b in covered_bills.values()]) + " the value " + str(v) + " can't be added to the existing value " + str(covered_bdown[k]) + ". " + str(detail)) if k.endswith("-gbp"): elem = k[:-4] covered_elems.add(elem) add_gap( caches, gaps, elem, covered_bill.start_date, covered_bill.finish_date, False, v, ) for k, v in covered_rates.items(): covered_bdown[k] = v.pop() if len(v) == 1 else None if primary_covered_bill is None or ( (covered_bill.finish_date - covered_bill.start_date) > (primary_covered_bill.finish_date - primary_covered_bill.start_date)): primary_covered_bill = covered_bill metered_kwh = 0 for era in (sess.query(Era).filter( Era.supply == supply, Era.start_date <= covered_finish, or_(Era.finish_date == null(), Era.finish_date >= covered_start), ).distinct().options( joinedload(Era.channels), joinedload(Era.cop), joinedload(Era.dc_contract), joinedload(Era.exp_llfc), joinedload(Era.exp_llfc).joinedload(Llfc.voltage_level), joinedload(Era.exp_supplier_contract), joinedload(Era.imp_llfc), joinedload(Era.imp_llfc).joinedload(Llfc.voltage_level), joinedload(Era.imp_supplier_contract), joinedload(Era.mop_contract), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.pc), joinedload(Era.supply).joinedload(Supply.dno), joinedload(Era.supply).joinedload(Supply.gsp_group), joinedload(Era.supply).joinedload(Supply.source), )): chunk_start = hh_max(covered_start, era.start_date) chunk_finish = hh_min(covered_finish, era.finish_date) if contract not in ( era.mop_contract, era.dc_contract, era.imp_supplier_contract, era.exp_supplier_contract, ): virtual_bill["problem"] += "".join(( "From ", hh_format(chunk_start), " to ", hh_format(chunk_finish), " the contract of ", "the era doesn't match the contract of the ", "bill.", )) continue if contract.market_role.code == "X": polarity = contract != era.exp_supplier_contract else: polarity = era.imp_supplier_contract is not None try: ds_key = ( chunk_start, chunk_finish, forecast_date, era.id, polarity, primary_covered_bill.id, ) data_source = data_sources[ds_key] except KeyError: data_source = data_sources[ ds_key] = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches, primary_covered_bill, ) vbf(data_source) if data_source.measurement_type == "hh": metered_kwh += sum(h["msp-kwh"] for h in data_source.hh_data) else: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches, ) metered_kwh += sum(h["msp-kwh"] for h in ds.hh_data) if market_role_code == "X": vb = data_source.supplier_bill vb_hhs = data_source.supplier_bill_hhs elif market_role_code == "C": vb = data_source.dc_bill vb_hhs = data_source.dc_bill_hhs elif market_role_code == "M": vb = data_source.mop_bill vb_hhs = data_source.mop_bill_hhs else: raise BadRequest("Odd market role.") for k, v in vb.items(): try: if isinstance(v, set): virtual_bill[k].update(v) else: virtual_bill[k] += v except KeyError: virtual_bill[k] = v except TypeError as detail: raise BadRequest("For key " + str(k) + " and value " + str(v) + ". " + str(detail)) for dt, bl in vb_hhs.items(): for k, v in bl.items(): if all((k.endswith("-gbp"), k != "net-gbp", v != 0)): add_gap(caches, gaps, k[:-4], dt, dt, True, v) for k in virtual_bill.keys(): if k.endswith("-gbp"): vb_elems.add(k[:-4]) long_map = {} vb_keys = set(virtual_bill.keys()) for elem in sorted(vb_elems, key=len, reverse=True): els = long_map[elem] = set() for k in tuple(vb_keys): if k.startswith(elem + "-"): els.add(k) vb_keys.remove(k) for elem in vb_elems.difference(covered_elems): for k in long_map[elem]: del virtual_bill[k] try: del virtual_bill["net-gbp"] except KeyError: pass virtual_bill["net-gbp"] = sum(v for k, v in virtual_bill.items() if k.endswith("-gbp")) era = supply.find_era_at(sess, bill_finish) if era is None: imp_mpan_core = exp_mpan_core = None site_code = site_name = None virtual_bill[ "problem"] += "This bill finishes before or after the supply. " else: imp_mpan_core = era.imp_mpan_core exp_mpan_core = era.exp_mpan_core site = (sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == era).one()) site_code = site.code site_name = site.name # Find bill to use for header data if bill.id not in covered_bills: for cbill in covered_bills.values(): if bill.batch == cbill.batch: bill = cbill values = [ bill.batch.reference, bill.reference, bill.bill_type.code, bill.kwh, bill.net, bill.vat, bill_start, bill_finish, imp_mpan_core, exp_mpan_core, site_code, site_name, covered_start, covered_finish, " | ".join(sorted([str(k) for k in covered_bills.keys()])), metered_kwh, ] for title in virtual_bill_titles: try: cov_val = covered_bdown[title] values.append(cov_val) del covered_bdown[title] except KeyError: cov_val = None values.append("") try: virt_val = virtual_bill[title] values.append(virt_val) del virtual_bill[title] except KeyError: virt_val = 0 values.append("") if title.endswith("-gbp"): if isinstance(virt_val, (int, float, Decimal)): if isinstance(cov_val, (int, float, Decimal)): values.append(float(cov_val) - float(virt_val)) else: values.append(0 - float(virt_val)) else: values.append(0) report_run_values = {} report_run_titles = list(titles) for title in sorted(virtual_bill.keys()): virt_val = virtual_bill[title] virt_title = "virtual-" + title values += [virt_title, virt_val] report_run_values[virt_title] = virt_val report_run_titles.append(virt_title) if title in covered_bdown: cov_title = "covered-" + title cov_val = covered_bdown[title] report_run_values[cov_title] = cov_val report_run_titles.append(cov_title) if title.endswith("-gbp"): if isinstance(virt_val, (int, float, Decimal)): if isinstance(cov_val, (int, float, Decimal)): diff_val = float(cov_val) - float(virt_val) else: diff_val = 0 - float(virt_val) else: diff_val = 0 report_run_values[f"difference-{title}"] = diff_val t = "difference-tpr-gbp" try: report_run_values[t] += diff_val except KeyError: report_run_values[t] = diff_val report_run_titles.append(t) else: cov_title, cov_val = "", "" values += [cov_title, cov_val] writer.writerow([csv_make_val(v) for v in values]) report_run_values.update(dict(zip(titles, values))) report_run_values["bill_id"] = bill.id report_run_values["batch_id"] = bill.batch.id report_run_values["supply_id"] = supply.id report_run_values["site_id"] = None if site_code is None else site.id report_run.insert_row(sess, "", report_run_titles, report_run_values) for bill in sess.query(Bill).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start, ): for k, v in loads(bill.breakdown).items(): if k.endswith("-gbp"): add_gap( caches, gaps, k[:-4], bill.start_date, bill.finish_date, False, v, ) # Avoid long-running transactions sess.commit() clumps = [] for element, elgap in sorted(gaps.items()): for start_date, hhgap in sorted(elgap.items()): if hhgap["has_virtual"] and not hhgap["has_covered"]: if len(clumps) == 0 or not all(( clumps[-1]["element"] == element, clumps[-1]["finish_date"] + HH == start_date, )): clumps.append({ "element": element, "start_date": start_date, "finish_date": start_date, "gbp": hhgap["gbp"], }) else: clumps[-1]["finish_date"] = start_date for i, clump in enumerate(clumps): vals = {} for title in titles: if title.startswith("difference-") and title.endswith("-gbp"): vals[title] = 0 else: vals[title] = None vals["covered-problem"] = "_".join(( "missing", clump["element"], "supplyid", str(supply.id), "from", hh_format(clump["start_date"]), )) vals["imp-mpan-core"] = imp_mpan_core vals["exp-mpan-core"] = exp_mpan_core vals["batch"] = "missing_bill" vals["bill-start-date"] = hh_format(clump["start_date"]) vals["bill-finish-date"] = hh_format(clump["finish_date"]) vals["difference-net-gbp"] = clump["gbp"] writer.writerow(csv_make_val(vals[title]) for title in titles) vals["bill_id"] = None vals["batch_id"] = None vals["supply_id"] = supply.id vals["site_id"] = None if site_code is None else site.id report_run.insert_row(sess, "", titles, vals) # Avoid long-running transactions sess.commit()
def run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = book = sbp_sheet = ssp_sheet = None try: sess = Session() self.log("Starting to check System Prices.") # ct_tz = pytz.timezone('Europe/London') contract = Contract.get_non_core_by_name( sess, 'system_price') contract_props = contract.make_properties() if contract_props.get('enabled', False): for rscript in sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()): ns = loads(rscript.script) rates = ns['gbp_per_nbp_mwh'] if len(rates) == 0: fill_start = rscript.start_date break elif rates[key_format( rscript.finish_date)]['run'] == 'DF': fill_start = rscript.finish_date + HH break config = Contract.get_non_core_by_name( sess, 'configuration') config_props = config.make_properties() scripting_key = config_props.get( ELEXON_PORTAL_SCRIPTING_KEY_KEY) if scripting_key is None: raise BadRequest( "The property " + ELEXON_PORTAL_SCRIPTING_KEY_KEY + " cannot be found in the configuration " "properties.") url_str = contract_props['url'] + \ 'file/download/BESTVIEWPRICES_FILE?key=' + \ scripting_key self.log("Downloading from " + url_str + " and extracting data from " + hh_format(fill_start)) url = urllib.parse.urlparse(url_str) if url.scheme == 'https': conn = http.client.HTTPSConnection( url.hostname, url.port) else: conn = http.client.HTTPConnection( url.hostname, url.port) conn.request("GET", url.path + '?' + url.query) res = conn.getresponse() self.log("Received " + str(res.status) + " " + res.reason) data = res.read() book = xlrd.open_workbook(file_contents=data) sbp_sheet = book.sheet_by_index(1) ssp_sheet = book.sheet_by_index(2) sp_months = [] sp_month = None for row_index in range(1, sbp_sheet.nrows): sbp_row = sbp_sheet.row(row_index) ssp_row = ssp_sheet.row(row_index) raw_date = datetime.datetime(*xlrd.xldate_as_tuple( sbp_row[0].value, book.datemode)) hh_date_ct = to_ct(raw_date) hh_date = to_utc(hh_date_ct) run_code = sbp_row[1].value for col_idx in range(2, 52): if hh_date >= fill_start: sbp_val = sbp_row[col_idx].value if sbp_val != '': if hh_date.day == 1 and \ hh_date.hour == 0 and \ hh_date.minute == 0: sp_month = {} sp_months.append(sp_month) ssp_val = ssp_row[col_idx].value sp_month[hh_date] = { 'run': run_code, 'sbp': sbp_val, 'ssp': ssp_val } hh_date += HH self.log("Successfully extracted data.") last_date = sorted(sp_months[-1].keys())[-1] if last_date.month == (last_date + HH).month: del sp_months[-1] if 'limit' in contract_props: sp_months = sp_months[0:1] for sp_month in sp_months: sorted_keys = sorted(sp_month.keys()) month_start = sorted_keys[0] month_finish = sorted_keys[-1] rs = sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date == month_start).first() if rs is None: self.log( "Adding a new rate script starting at " + hh_format(month_start) + ".") latest_rs = sess.query(RateScript).filter( RateScript.contract == contract).\ order_by(RateScript.start_date.desc()). \ first() contract.update_rate_script( sess, latest_rs, latest_rs.start_date, month_finish, loads(latest_rs.script)) rs = contract.insert_rate_script( sess, month_start, {}) sess.flush() script = { 'gbp_per_nbp_mwh': dict((key_format(k), v) for k, v in sp_month.items()) } self.log("Updating rate script starting at " + hh_format(month_start) + ".") contract.update_rate_script( sess, rs, rs.start_date, rs.finish_date, script) sess.commit() else: self.log("The automatic importer is disabled. To " "enable it, edit the contract properties to " "set 'enabled' to True.") except BaseException: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: book = sbp_sheet = ssp_sheet = None self.lock.release() self.log("Finished checking System Price rates.") if sess is not None: sess.close() self.going.wait(24 * 60 * 60) self.going.clear()
def make_raw_bills(self): raw_bills = [] for self._line_number, row in enumerate(self.csv_reader): bill_reference = row[0] if bill_reference == "" or bill_reference.startswith("#"): continue mprn = row[1] bill_type = row[2] account = row[3] issue_date = get_datetime(row, 4, "Issue Date", self.line_number) start_date = get_datetime(row, 5, "Start Date", self.line_number) finish_date = get_datetime(row, 6, "Finish Date", self.line_number) kwh = get_decimal(row, 7, "kWh", self.line_number) net = get_decimal(row, 8, "Net GBP", self.line_number) vat = get_decimal(row, 9, "VAT GBP", self.line_number) gross = get_decimal(row, 10, "Gross GBP", self.line_number) try: breakdown = loads(row[11]) except ZishLocationException as e: raise BadRequest( "Problem parsing the breakdown field at line number " + str(self.line_number) + ": " + str(e)) reads = [] for i in count(12, 10): if i > len(row) - 1 or len("".join(row[i:]).strip()) == 0: break msn = get_str(row, i, "Meter Serial Number", self.line_number) unit = get_str(row, i + 1, "Unit", self.line_number).upper() correction_factor = get_decimal(row, i + 2, "Correction Factor", self.line_number) calorific_value = get_str(row, i + 3, "Calorific Value", self.line_number) if len(calorific_value) > 0: calorific_value = get_decimal(row, i + 3, "Calorific Value", self.line_number) else: calorific_value = None prev_date = get_datetime(row, i + 4, "Previous Date", self.line_number) prev_value = get_decimal(row, i + 5, "Previous Value", self.line_number) prev_type = get_str(row, i + 6, "Previous Type", self.line_number) pres_date = get_datetime(row, i + 7, "Previous Date", self.line_number) pres_value = get_decimal(row, i + 8, "Present Value", self.line_number) pres_type = get_str(row, i + 9, "Present Type", self.line_number) reads.append({ "msn": msn, "unit": unit, "correction_factor": correction_factor, "calorific_value": calorific_value, "prev_date": prev_date, "prev_value": prev_value, "prev_type_code": prev_type, "pres_date": pres_date, "pres_value": pres_value, "pres_type_code": pres_type, }) raw_bills.append({ "mprn": mprn, "reference": bill_reference, "account": account, "reads": reads, "kwh": kwh, "breakdown": breakdown, "net_gbp": net, "vat_gbp": vat, "gross_gbp": gross, "raw_lines": self.titles + "\n" + ",".join(row), "bill_type_code": bill_type, "start_date": start_date, "finish_date": finish_date, "issue_date": issue_date, }) return raw_bills
def content(batch_id, bill_id, contract_id, start_date, finish_date, user): caches = {} tmp_file = sess = bill = None forecast_date = to_utc(Datetime.max) sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'bill_check.csv', user) tmp_file = open(running_name, mode='w', newline='') writer = csv.writer(tmp_file, lineterminator='\n') bills = sess.query(Bill).order_by( Bill.supply_id, Bill.reference).options( joinedload(Bill.supply), subqueryload(Bill.reads).joinedload(RegisterRead.present_type), subqueryload(Bill.reads).joinedload(RegisterRead.previous_type), joinedload(Bill.batch)) if batch_id is not None: batch = Batch.get_by_id(sess, batch_id) bills = bills.filter(Bill.batch == batch) contract = batch.contract elif bill_id is not None: bill = Bill.get_by_id(sess, bill_id) bills = bills.filter(Bill.id == bill.id) contract = bill.batch.contract elif contract_id is not None: contract = Contract.get_by_id(sess, contract_id) bills = bills.join(Batch).filter( Batch.contract == contract, Bill.start_date <= finish_date, Bill.finish_date >= start_date) market_role_code = contract.market_role.code vbf = chellow.computer.contract_func(caches, contract, 'virtual_bill') if vbf is None: raise BadRequest( 'The contract ' + contract.name + " doesn't have a function virtual_bill.") virtual_bill_titles_func = chellow.computer.contract_func( caches, contract, 'virtual_bill_titles') if virtual_bill_titles_func is None: raise BadRequest( 'The contract ' + contract.name + " doesn't have a function virtual_bill_titles.") virtual_bill_titles = virtual_bill_titles_func() titles = [ 'batch', 'bill-reference', 'bill-type', 'bill-kwh', 'bill-net-gbp', 'bill-vat-gbp', 'bill-start-date', 'bill-finish-date', 'imp-mpan-core', 'exp-mpan-core', 'site-code', 'site-name', 'covered-from', 'covered-to', 'covered-bills', 'metered-kwh'] for t in virtual_bill_titles: titles.append('covered-' + t) titles.append('virtual-' + t) if t.endswith('-gbp'): titles.append('difference-' + t) writer.writerow(titles) bill_map = defaultdict(set, {}) for bill in bills: bill_map[bill.supply.id].add(bill.id) for supply_id, bill_ids in bill_map.items(): gaps = {} data_sources = {} while len(bill_ids) > 0: bill_id = list(sorted(bill_ids))[0] bill_ids.remove(bill_id) bill = sess.query(Bill).filter(Bill.id == bill_id).options( joinedload(Bill.batch), joinedload(Bill.bill_type), joinedload(Bill.reads), joinedload(Bill.supply), joinedload(Bill.reads).joinedload( RegisterRead.present_type), joinedload(Bill.reads).joinedload( RegisterRead.previous_type)).one() virtual_bill = {'problem': ''} supply = bill.supply read_dict = {} for read in bill.reads: gen_start = read.present_date.replace(hour=0).replace( minute=0) gen_finish = gen_start + relativedelta(days=1) - HH msn_match = False read_msn = read.msn for read_era in supply.find_eras( sess, gen_start, gen_finish): if read_msn == read_era.msn: msn_match = True break if not msn_match: virtual_bill['problem'] += "The MSN " + read_msn + \ " of the register read " + str(read.id) + \ " doesn't match the MSN of the era." for dt, typ in [ (read.present_date, read.present_type), (read.previous_date, read.previous_type)]: key = str(dt) + "-" + read.msn try: if typ != read_dict[key]: virtual_bill['problem'] += " Reads taken " + \ "on " + str(dt) + \ " have differing read types." except KeyError: read_dict[key] = typ bill_start = bill.start_date bill_finish = bill.finish_date covered_start = bill_start covered_finish = bill_finish covered_bdown = {'sum-msp-kwh': 0, 'net-gbp': 0, 'vat-gbp': 0} vb_elems = set() enlarged = True while enlarged: enlarged = False covered_elems = find_elements(bill) covered_bills = OrderedDict( (b.id, b) for b in sess.query(Bill).join(Batch). join(Contract).join(MarketRole).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start, MarketRole.code == market_role_code).order_by( Bill.start_date, Bill.issue_date)) while True: to_del = None for a, b in combinations(covered_bills.values(), 2): if all( ( a.start_date == b.start_date, a.finish_date == b.finish_date, a.kwh == -1 * b.kwh, a.net == -1 * b.net, a.vat == -1 * b.vat, a.gross == -1 * b.gross)): to_del = (a.id, b.id) break if to_del is None: break else: for k in to_del: del covered_bills[k] for k, covered_bill in tuple(covered_bills.items()): elems = find_elements(covered_bill) if elems.isdisjoint(covered_elems): if k != bill.id: del covered_bills[k] continue else: covered_elems.update(elems) if covered_bill.start_date < covered_start: covered_start = covered_bill.start_date enlarged = True break if covered_bill.finish_date > covered_finish: covered_finish = covered_bill.finish_date enlarged = True break if len(covered_bills) == 0: continue primary_covered_bill = None for covered_bill in covered_bills.values(): if covered_bill.id in bill_ids: bill_ids.remove(covered_bill.id) covered_bdown['net-gbp'] += float(covered_bill.net) covered_bdown['vat-gbp'] += float(covered_bill.vat) covered_bdown['sum-msp-kwh'] += float(covered_bill.kwh) covered_rates = defaultdict(set) for k, v in loads(covered_bill.breakdown).items(): if k in ('raw_lines', 'raw-lines'): continue if isinstance(v, list): covered_rates[k].update(set(v)) else: if isinstance(v, Decimal): v = float(v) try: covered_bdown[k] += v except KeyError: covered_bdown[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " in " + str( [ b.id for b in covered_bills.values() ]) + " the value " + str(v) + " can't be added to the existing value " + str(covered_bdown[k]) + ". " + str(detail)) if k.endswith('-gbp'): elem = k[:-4] covered_elems.add(elem) add_gap( caches, gaps, elem, covered_bill.start_date, covered_bill.finish_date, False, v) for k, v in covered_rates.items(): covered_bdown[k] = v.pop() if len(v) == 1 else None if primary_covered_bill is None or ( ( covered_bill.finish_date - covered_bill.start_date) > ( primary_covered_bill.finish_date - primary_covered_bill.start_date)): primary_covered_bill = covered_bill metered_kwh = 0 for era in sess.query(Era).filter( Era.supply == supply, Era.start_date <= covered_finish, or_( Era.finish_date == null(), Era.finish_date >= covered_start) ).distinct().options( joinedload(Era.channels), joinedload(Era.cop), joinedload(Era.dc_contract), joinedload(Era.exp_llfc), joinedload(Era.exp_llfc).joinedload( Llfc.voltage_level), joinedload(Era.exp_supplier_contract), joinedload(Era.imp_llfc), joinedload(Era.imp_llfc).joinedload( Llfc.voltage_level), joinedload(Era.imp_supplier_contract), joinedload(Era.mop_contract), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.pc), joinedload(Era.supply).joinedload(Supply.dno), joinedload(Era.supply).joinedload(Supply.gsp_group), joinedload(Era.supply).joinedload(Supply.source)): chunk_start = hh_max(covered_start, era.start_date) chunk_finish = hh_min(covered_finish, era.finish_date) if contract not in ( era.mop_contract, era.dc_contract, era.imp_supplier_contract, era.exp_supplier_contract): virtual_bill['problem'] += ''.join( ( "From ", hh_format(chunk_start), " to ", hh_format(chunk_finish), " the contract of ", "the era doesn't match the contract of the ", "bill.")) continue if contract.market_role.code == 'X': polarity = contract != era.exp_supplier_contract else: polarity = era.imp_supplier_contract is not None ''' pairs = [] last_finish = chunk_start - HH for hd in chellow.computer.datum_range( sess, caches, 0, chunk_start, chunk_finish): if hd['utc-is-month-end'] or hd['ct-is-month-end']: end_date = hd['start-date'] pairs.append((last_finish + HH, end_date)) last_finish = end_date if hd['start-date'] > last_finish: pairs.append((last_finish + HH, hd['start-date'])) for ss_start, ss_finish in pairs: ''' try: ds_key = ( chunk_start, chunk_finish, forecast_date, era.id, polarity, primary_covered_bill.id) data_source = data_sources[ds_key] except KeyError: data_source = data_sources[ds_key] = \ chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches, primary_covered_bill) vbf(data_source) if data_source.measurement_type == 'hh': metered_kwh += sum( h['msp-kwh'] for h in data_source.hh_data) else: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches) metered_kwh += sum( h['msp-kwh'] for h in ds.hh_data) if market_role_code == 'X': vb = data_source.supplier_bill elif market_role_code == 'C': vb = data_source.dc_bill elif market_role_code == 'M': vb = data_source.mop_bill else: raise BadRequest("Odd market role.") for k, v in vb.items(): try: if isinstance(v, set): virtual_bill[k].update(v) else: virtual_bill[k] += v except KeyError: virtual_bill[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " and value " + str(v) + ". " + str(detail)) if all((k.endswith('-gbp'), k != 'net-gbp', v != 0)): add_gap( caches, gaps, k[:-4], chunk_start, chunk_finish, True, v) for k in virtual_bill.keys(): if k.endswith('-gbp'): vb_elems.add(k[:-4]) long_map = {} vb_keys = set(virtual_bill.keys()) for elem in sorted(vb_elems, key=len, reverse=True): els = long_map[elem] = set() for k in tuple(vb_keys): if k.startswith(elem + '-'): els.add(k) vb_keys.remove(k) for elem in vb_elems.difference(covered_elems): for k in long_map[elem]: del virtual_bill[k] try: del virtual_bill['net-gbp'] except KeyError: pass virtual_bill['net-gbp'] = sum( v for k, v in virtual_bill.items() if k.endswith('-gbp')) era = supply.find_era_at(sess, bill_finish) if era is None: imp_mpan_core = exp_mpan_core = None site_code = site_name = None virtual_bill['problem'] += \ "This bill finishes before or after the supply. " else: imp_mpan_core = era.imp_mpan_core exp_mpan_core = era.exp_mpan_core site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == era).one() site_code = site.code site_name = site.name # Find bill to use for header data if bill.id not in covered_bills: for cbill in covered_bills.values(): if bill.batch == cbill.batch: bill = cbill values = [ bill.batch.reference, bill.reference, bill.bill_type.code, bill.kwh, bill.net, bill.vat, hh_format(bill_start), hh_format(bill_finish), imp_mpan_core, exp_mpan_core, site_code, site_name, hh_format(covered_start), hh_format(covered_finish), ':'.join( str(i).replace(',', '') for i in covered_bills.keys()), metered_kwh] for title in virtual_bill_titles: try: cov_val = covered_bdown[title] values.append(cov_val) del covered_bdown[title] except KeyError: cov_val = None values.append('') try: virt_val = csv_make_val(virtual_bill[title]) values.append(virt_val) del virtual_bill[title] except KeyError: virt_val = 0 values.append('') if title.endswith('-gbp'): if isinstance(virt_val, (int, float, Decimal)): if isinstance(cov_val, (int, float, Decimal)): values.append(float(cov_val) - float(virt_val)) else: values.append(0 - float(virt_val)) else: values.append('') for title in sorted(virtual_bill.keys()): virt_val = csv_make_val(virtual_bill[title]) values += ['virtual-' + title, virt_val] if title in covered_bdown: values += ['covered-' + title, covered_bdown[title]] else: values += ['', ''] writer.writerow(values) for bill in sess.query(Bill).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start): for k, v in loads(bill.breakdown).items(): if k.endswith('-gbp'): add_gap( caches, gaps, k[:-4], bill.start_date, bill.finish_date, False, v) # Avoid long-running transactions sess.rollback() clumps = [] for element, elgap in sorted(gaps.items()): for start_date, hhgap in sorted(elgap.items()): if hhgap['has_virtual'] and not hhgap['has_covered']: if len(clumps) == 0 or not all( ( clumps[-1]['element'] == element, clumps[-1]['finish_date'] + HH == start_date)): clumps.append( { 'element': element, 'start_date': start_date, 'finish_date': start_date, 'gbp': hhgap['gbp']}) else: clumps[-1]['finish_date'] = start_date for i, clump in enumerate(clumps): vals = dict((title, '') for title in titles) vals['covered-problem'] = '_'.join( ( 'missing', clump['element'], 'supplyid', str(supply.id), 'from', hh_format(clump['start_date']))) vals['imp-mpan-core'] = imp_mpan_core vals['exp-mpan-core'] = exp_mpan_core vals['batch'] = 'missing_bill' vals['bill-start-date'] = hh_format(clump['start_date']) vals['bill-finish-date'] = hh_format(clump['finish_date']) vals['difference-net-gbp'] = clump['gbp'] writer.writerow(vals[title] for title in titles) # Avoid long-running transactions sess.rollback() except BadRequest as e: if bill is None: prefix = "Problem: " else: prefix = "Problem with bill " + str(bill.id) + ':' tmp_file.write(prefix + e.description) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + '\n') tmp_file.write("Problem " + msg) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)
def run_inner(self, sess): self.log("Starting to check GCv rates.") contract = Contract.get_non_core_by_name(sess, 'g_cv') latest_rs = sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()).first() latest_rs_id = latest_rs.id this_month_start = latest_rs.start_date + relativedelta(months=1) next_month_start = this_month_start + relativedelta(months=1) now = utc_datetime_now() props = contract.make_properties() if props.get('enabled', False): search_finish = next_month_start + relativedelta(days=1) if now > search_finish: url = props['url'] self.log("Checking to see if data is available from " + hh_format(this_month_start) + " to " + hh_format(search_finish) + " at " + url) res = requests.post( url, data={ 'LatestValue': 'true', 'PublicationObjectIds': '408:12265,+408:4636,+408:4637,+408:4639,' '+408:4638,+408:4640,+408:4641,+408:4642,' '+408:4643,+408:4644,+408:4645,+408:4646,' '+408:4647,+408:4648,+408:12269,+408:12268,' '+408:12270,+408:12266,+408:12267', 'Applicable': 'applicableFor', 'PublicationObjectCount': '19', 'FromUtcDatetime': param_format(this_month_start), 'ToUtcDateTime': param_format(search_finish), 'FileType': 'Csv' }) self.log("Received " + str(res.status_code) + " " + res.reason) month_cv = defaultdict(dict) cf = csv.reader(res.text.splitlines()) row = next(cf) # Skip title row last_date = to_utc(Datetime.min) for row in cf: applicable_at_str = row[0] applicable_for_str = row[1] applicable_for = to_utc( Datetime.strptime(applicable_for_str, "%d/%m/%Y")) data_item = row[2] value_str = row[3] if 'LDZ' in data_item and \ this_month_start <= applicable_for < \ next_month_start: ldz = data_item[-3:-1] cvs = month_cv[ldz] applicable_at = to_utc( Datetime.strptime(applicable_at_str, "%d/%m/%Y %H:%M:%S")) last_date = max(last_date, applicable_at) cv = Decimal(value_str) try: existing = cvs[applicable_for.day] if applicable_at > existing['applicable_at']: existing['cv'] = cv existing['applicable_at'] = applicable_at except KeyError: cvs[applicable_for.day] = { 'cv': cv, 'applicable_at': applicable_at } all_equal = len(set(map(len, month_cv.values()))) <= 1 if last_date + Timedelta(days=1) >= next_month_start and \ all_equal: self.log("The whole month's data is there.") script = {'cvs': month_cv} contract = Contract.get_non_core_by_name(sess, 'g_cv') rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script( sess, rs, rs.start_date, rs.start_date + relativedelta(months=2) - HH, loads(rs.script)) sess.flush() contract.insert_rate_script( sess, rs.start_date + relativedelta(months=1), script) sess.commit() self.log("Added new rate script.") else: self.log("There isn't a whole month there yet. The " "last date is " + hh_format(last_date) + ".") else: self.log("The automatic importer is disabled. To " "enable it, edit the contract properties to " "set 'enabled' to True.")
def _process_url(logger, sess, url, contract): logger(f"Checking to see if there's any new data at {url}") res = requests.get(url) content_disposition = res.headers.get("Content-Disposition") logger(f"Received {res.status_code} {res.reason} {content_disposition}") cache = {} parsed_rows = [] filetype = _find_file_type(content_disposition) if filetype == "csv": reader = csv.reader(res.text.splitlines()) next(reader) # Skip titles for row in reader: date_str = row[0] date = Datetime.strptime(date_str, "%d/%m/%Y") period_str = row[1] period = int(period_str) price_str = row[2] price = Decimal(price_str) run = row[5] parsed_rows.append((date, period, price, run)) elif filetype == "xls": book = xlrd.open_workbook(file_contents=res.content) sheet = book.sheet_by_index(0) for row_index in range(1, sheet.nrows): row = sheet.row(row_index) date_val = row[0].value if isinstance(date_val, float): date = Datetime(*xlrd.xldate_as_tuple(date_val, book.datemode)) elif isinstance(date_val, str): separator = date_val[2] fmat = separator.join(("%d", "%m", "%Y")) date = Datetime.strptime(date_val, fmat) else: raise BadRequest( f"Type of date field {date_val} not recognized.") period = int(row[1].value) price = Decimal(str(row[2].value)) run = row[5].value parsed_rows.append((date, period, price, run)) else: raise BadRequest(f"The file extension {filetype} is not recognised.") for date, period, price, run in parsed_rows: hh_date_ct = to_ct(date) hh_date_ct += relativedelta(minutes=30 * (period - 1)) hh_date = to_utc(hh_date_ct) try: rs, rates, rts = cache[hh_date.year][hh_date.month] except KeyError: _save_cache(sess, cache) try: yr_cache = cache[hh_date.year] except KeyError: yr_cache = cache[hh_date.year] = {} rs = (sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date <= hh_date, or_( RateScript.finish_date == null(), RateScript.finish_date >= hh_date, ), ).first()) while rs is None: logger(f"There's no rate script at {hh_format(hh_date)}.") latest_rs = (sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()).first()) contract.update_rate_script( sess, latest_rs, latest_rs.start_date, latest_rs.start_date + relativedelta(months=2) - HH, loads(latest_rs.script), ) new_rs_start = latest_rs.start_date + relativedelta(months=1) contract.insert_rate_script(sess, new_rs_start, {}) sess.commit() logger( f"Added a rate script starting at {hh_format(new_rs_start)}." ) rs = (sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date <= hh_date, or_( RateScript.finish_date == null(), RateScript.finish_date >= hh_date, ), ).first()) rates = loads(rs.script) try: rts = rates["rates_gbp_per_mwh"] except KeyError: rts = rates["rates_gbp_per_mwh"] = {} yr_cache[hh_date.month] = rs, rates, rts key = key_format(hh_date) try: existing = rts[key] except KeyError: existing = rts[key] = {} if run not in existing: existing[run] = price logger(f"Added rate at {hh_format(hh_date)} for run {run}.") _save_cache(sess, cache)
def req_zish(name): try: return loads(req_str(name)) except ZishException as e: raise BadRequest(f"Problem parsing the field {name} as Zish: {e}")
def _process_line(cache, sess, contract, log_func, values): hh_date_ct = to_ct(Datetime.strptime(values[0], "%d/%m/%Y")) hh_date = to_utc(hh_date_ct) hh_date += relativedelta(minutes=30 * (int(values[2]) - 1)) run = values[1] gsp_group_code = GSP_GROUP_LOOKUP[values[3]] off_taking_str = values[4] try: off_taking = Decimal(off_taking_str) except InvalidOperation as e: raise BadRequest("Problem parsing 'off-taking' field '" + off_taking_str + "' in the row " + str(values) + ". " + str(e)) delivering = Decimal(values[5]) try: rs, rates, rts = cache[hh_date.year][hh_date.month] except KeyError: _save_cache(sess, cache) try: yr_cache = cache[hh_date.year] except KeyError: yr_cache = cache[hh_date.year] = {} rs = (sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date <= hh_date, or_(RateScript.finish_date == null(), RateScript.finish_date >= hh_date), ).first()) while rs is None: log_func("There's no rate script at " + hh_format(hh_date) + ".") latest_rs = (sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()).first()) contract.update_rate_script( sess, latest_rs, latest_rs.start_date, latest_rs.start_date + relativedelta(months=2) - HH, loads(latest_rs.script), ) new_rs_start = latest_rs.start_date + relativedelta(months=1) contract.insert_rate_script(sess, new_rs_start, {}) sess.commit() log_func("Added a rate script starting at " + hh_format(new_rs_start) + ".") rs = (sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date <= hh_date, or_( RateScript.finish_date == null(), RateScript.finish_date >= hh_date, ), ).first()) rates = loads(rs.script) try: rts = rates["tlms"] except KeyError: rts = rates["tlms"] = {} yr_cache[hh_date.month] = rs, rates, rts sess.rollback() key = key_format(hh_date) try: existing = rts[key] except KeyError: existing = rts[key] = {} try: group = existing[gsp_group_code] except KeyError: group = existing[gsp_group_code] = {} if run not in group: group[run] = {"off_taking": off_taking, "delivering": delivering} log_func("Found rate at " + hh_format(hh_date) + " for GSP Group " + gsp_group_code + " and run " + run + ".")
def make_raw_bills(self): raw_bills = [] for self._line_number, row in enumerate(self.csv_reader): bill_reference = row[0] if bill_reference == '' or bill_reference.startswith('#'): continue mprn = row[1] bill_type = row[2] account = row[3] issue_date = get_datetime(row, 4, 'Issue Date', self.line_number) start_date = get_datetime(row, 5, 'Start Date', self.line_number) finish_date = get_datetime(row, 6, 'Finish Date', self.line_number) kwh = get_decimal(row, 7, 'kWh', self.line_number) net = get_decimal(row, 8, 'Net GBP', self.line_number) vat = get_decimal(row, 9, 'VAT GBP', self.line_number) gross = get_decimal(row, 10, 'Gross GBP', self.line_number) try: breakdown = loads(row[11]) except ZishLocationException as e: raise BadRequest( "Problem parsing the breakdown field at line number " + str(self.line_number) + ": " + str(e)) reads = [] for i in count(12, 10): if i > len(row) - 1 or len(''.join(row[i:]).strip()) == 0: break msn = get_str(row, i, 'Meter Serial Number', self.line_number) unit = get_str(row, i + 1, 'Unit', self.line_number).upper() correction_factor = get_decimal(row, i + 2, 'Correction Factor', self.line_number) calorific_value = get_str(row, i + 3, 'Calorific Value', self.line_number) if len(calorific_value) > 0: calorific_value = get_decimal(row, i + 3, 'Calorific Value', self.line_number) else: calorific_value = None prev_date = get_datetime(row, i + 4, 'Previous Date', self.line_number) prev_value = get_decimal(row, i + 5, 'Previous Value', self.line_number) prev_type = get_str(row, i + 6, 'Previous Type', self.line_number) pres_date = get_datetime(row, i + 7, 'Previous Date', self.line_number) pres_value = get_decimal(row, i + 8, 'Present Value', self.line_number) pres_type = get_str(row, i + 9, 'Present Type', self.line_number) reads.append({ 'msn': msn, 'unit': unit, 'correction_factor': correction_factor, 'calorific_value': calorific_value, 'prev_date': prev_date, 'prev_value': prev_value, 'prev_type_code': prev_type, 'pres_date': pres_date, 'pres_value': pres_value, 'pres_type_code': pres_type }) raw_bills.append({ 'mprn': mprn, 'reference': bill_reference, 'account': account, 'reads': reads, 'kwh': kwh, 'breakdown': breakdown, 'net_gbp': net, 'vat_gbp': vat, 'gross_gbp': gross, 'raw_lines': self.titles + '\n' + ','.join(row), 'bill_type_code': bill_type, 'start_date': start_date, 'finish_date': finish_date, 'issue_date': issue_date }) return raw_bills
def fetch_cvs(sess, log_f): log_f("Starting to check GCv rates.") contract = Contract.get_non_core_by_name(sess, "g_cv") latest_rs = (sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()).first()) latest_rs_id = latest_rs.id latest_rs_start_date_ct = to_ct(latest_rs.start_date) month_pairs = list( c_months_u( start_year=latest_rs_start_date_ct.year, start_month=latest_rs_start_date_ct.month, months=2, )) month_start, month_finish = month_pairs[1] now = utc_datetime_now() props = contract.make_properties() if not props.get("enabled", False): log_f("The automatic importer is disabled. To enable it, edit the " "contract properties to set 'enabled' to true.") return search_start = month_start - relativedelta(days=1) search_finish = month_finish + relativedelta(days=1) if now <= search_finish: return url = props["url"] log_f(f"Checking to see if data is available " f"from {hh_format(search_start)} to " f"{hh_format(search_finish)} at {url}") res = requests.post( url, data={ "LatestValue": "true", "PublicationObjectIds": "408:28,+408:5328,+408:5320,+408:5291," "+408:5366,+408:5312,+408:5346,+408:5324,+408:5316,+408:5308," "+408:5336,+408:5333,+408:5342,+408:5354,+408:82,+408:70," "+408:59,+408:38,+408:49", "PublicationObjectStagingIds": "PUBOBJ1660,PUBOB4507,PUBOB4508," "PUBOB4510,PUBOB4509,PUBOB4511,PUBOB4512,PUBOB4513,PUBOB4514," "PUBOB4515,PUBOB4516,PUBOB4517,PUBOB4518,PUBOB4519,PUBOB4521," "PUBOB4520,PUBOB4522,PUBOBJ1661,PUBOBJ1662", "Applicable": "applicableFor", "PublicationObjectCount": "19", "FromUtcDatetime": param_format(search_start), "ToUtcDateTime": param_format(search_finish), "FileType": "Csv", }, ) log_f(f"Received {res.status_code} {res.reason}") month_cv = defaultdict(dict) cf = csv.reader(res.text.splitlines()) row = next(cf) # Skip title row last_date = utc_datetime(1900, 1, 1) for row in cf: applicable_at_str = row[0] applicable_for_str = row[1] applicable_for = to_utc( to_ct(Datetime.strptime(applicable_for_str, "%d/%m/%Y"))) data_item = row[2] value_str = row[3] if "LDZ" in data_item and month_start <= applicable_for < month_finish: ldz = data_item[-3:-1] cvs = month_cv[ldz] applicable_at = to_utc( to_ct(Datetime.strptime(applicable_at_str, "%d/%m/%Y %H:%M:%S"))) last_date = max(last_date, applicable_at) cv = Decimal(value_str) try: existing = cvs[applicable_for.day] if applicable_at > existing["applicable_at"]: existing["cv"] = cv existing["applicable_at"] = applicable_at except KeyError: cvs[applicable_for.day] = { "cv": cv, "applicable_at": applicable_at } all_equal = len(set(map(len, month_cv.values()))) <= 1 if last_date + Timedelta(days=1) > month_finish and all_equal: log_f("The whole month's data is there.") script = {"cvs": month_cv} contract = Contract.get_non_core_by_name(sess, "g_cv") rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script(sess, rs, rs.start_date, month_finish, loads(rs.script)) sess.flush() contract.insert_rate_script(sess, month_start, script) sess.commit() log_f("Added new rate script.") else: log_f(f"There isn't a whole month there yet. The " f"last date is {hh_format(last_date)}.")