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)}.")
