def content(user, file_like, report_run_id):
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
FNAME + ".csv", user)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
report_run = ReportRun.get_by_id(sess, report_run_id)
props = Contract.get_non_core_by_name(
sess, "configuration").make_properties()
_process_sites(sess, file_like, writer, props, report_run)
report_run.update("finished")
sess.commit()
except BaseException:
msg = traceback.format_exc()
if report_run is not None:
report_run.update("interrupted")
report_run.insert_row(sess, "", ["error"], {"error": msg}, {})
sess.commit()
sys.stderr.write(msg)
writer.writerow([msg])
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def run(self):
sess = None
try:
self._log(
"Starting to parse the file with '" + self.parser_name + "'.")
sess = Session()
g_batch = GBatch.get_by_id(sess, self.g_batch_id)
raw_bills = self.parser.make_raw_bills()
self._log(
"Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
bill_type = BillType.get_by_code(
sess, raw_bill['bill_type_code'])
g_bill = g_batch.insert_g_bill(
sess, bill_type, raw_bill['mprn'],
raw_bill['reference'], raw_bill['account'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net_gbp'], raw_bill['vat_gbp'],
raw_bill['gross_gbp'], raw_bill['raw_lines'],
raw_bill['breakdown'])
sess.flush()
for raw_read in raw_bill['reads']:
prev_type = GReadType.get_by_code(
sess, raw_read['prev_type_code'])
pres_type = GReadType.get_by_code(
sess, raw_read['pres_type_code'])
g_units = GUnits.get_by_code(sess, raw_read['units'])
g_read = g_bill.insert_g_read(
sess, raw_read['msn'], raw_read['prev_value'],
raw_read['prev_date'], prev_type,
raw_read['pres_value'], raw_read['pres_date'],
pres_type, g_units, raw_read['correction_factor'],
raw_read['calorific_value'])
sess.expunge(g_read)
sess.commit()
self.successful_bills.append(raw_bill)
sess.expunge(g_bill)
except BadRequest as e:
sess.rollback()
raw_bill['error'] = e.description
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
self._log(
"The import has finished, but " +
str(len(self.failed_bills)) + " bills failed to load.")
except:
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
def run(self):
sess = None
try:
sess = Session()
contract = Contract.get_dc_by_id(sess, self.dc_contract_id)
sess.rollback()
properties = contract.make_properties()
mpan_map = properties.get("mpan_map", {})
parser_module = importlib.import_module(
"chellow.hh_parser_" + self.conv_ext[0][1:].replace(".", "_"))
self.converter = parser_module.create_parser(
self.istream, mpan_map)
sess.rollback()
HhDatum.insert(sess, self.converter, contract)
sess.commit()
except BadRequest as e:
self.messages.append(e.description)
except BaseException:
self.messages.append("Outer problem " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
def run(self):
while not self.stopped.isSet():
if self.lock.acquire(False):
sess = None
try:
sess = Session()
self.log("Starting to check BSUoS rates.")
contract = Contract.get_non_core_by_name(sess, 'bsuos')
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 = Datetime.now(pytz.utc)
props = contract.make_properties()
if props.get('enabled', False):
if now > next_month_start:
url = props['url']
self.log(
"Checking to see if data is available from " +
str(this_month_start) + " to " +
str(next_month_start - HH) +
" 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)
ct_tz = pytz.timezone('Europe/London')
month_bsuos = {}
for row_index in range(1, sheet.nrows):
row = sheet.row(row_index)
raw_date = Datetime(
*xlrd.xldate_as_tuple(
row[0].value, book.datemode))
hh_date_ct = ct_tz.localize(raw_date)
hh_date = pytz.utc.normalize(
hh_date_ct.astimezone(pytz.utc))
hh_date += relativedelta(
minutes=30*int(row[1].value))
if not hh_date < this_month_start and \
hh_date < next_month_start:
month_bsuos[key_format(hh_date)] = \
row[2].value
if key_format(next_month_start - HH) in \
month_bsuos:
self.log("The whole month's data is there.")
script = "def rates_gbp_per_mwh():\n " \
"return {\n" + ',\n'.join(
"'" + k + "': " + str(month_bsuos[k])
for k in sorted(
month_bsuos.keys())) + "}"
set_read_write(sess)
contract = Contract.get_non_core_by_name(
sess, 'bsuos')
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, 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 " +
sorted(month_bsuos.keys())[-1])
else:
self.log(
"The automatic importer is disabled. To "
"enable it, edit the contract properties to "
"set 'enabled' to True.")
except:
self.log("Outer problem " + traceback.format_exc())
sess.rollback()
finally:
if sess is not None:
sess.close()
self.lock.release()
self.log("Finished checking BSUoS rates.")
self.going.wait(30 * 60)
self.going.clear()
def run(self):
while not self.stopped.isSet():
if self.lock.acquire(False):
sess = None
try:
sess = Session()
self.log("Starting to check TLMs.")
contract = Contract.get_non_core_by_name(sess, 'tlms')
latest_rs = sess.query(RateScript).filter(
RateScript.contract_id == contract.id).order_by(
RateScript.start_date.desc()).first()
latest_rs_id = latest_rs.id
next_month_start = latest_rs.start_date + \
relativedelta(months=1)
next_month_finish = latest_rs.start_date + \
relativedelta(months=2) - HH
now = utc_datetime_now()
if now > next_month_start:
self.log(
"Checking to see if data is available from " +
str(next_month_start) + " to " +
str(next_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/TLM_FILE?key=',
scripting_key))
r = requests.get(url_str)
parser = csv.reader(
(l.decode() for l in r.iter_lines()),
delimiter=',', quotechar='"')
self.log("Opened " + url_str + ".")
next(parser, None)
month_tlms = {}
for values in parser:
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]))
if next_month_start <= hh_date <= \
next_month_finish:
month_tlms[key_format(hh_date)] = {
'off-taking': values[3],
'delivering': values[4]}
if key_format(next_month_finish) in month_tlms:
self.log("The whole month's data is there.")
script = "def tlms():\n return {\n" + \
',\n'.join(
"'" + k + "': " +
month_tlms[k]['off-taking'] for k in
sorted(month_tlms.keys())) + "}"
contract = Contract.get_non_core_by_name(
sess, 'tlms')
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,
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:
msg = "There isn't a whole month there yet."
if len(month_tlms) > 0:
msg += "The last date is " + \
sorted(month_tlms.keys())[-1]
self.log(msg)
except:
self.log("Outer problem " + traceback.format_exc())
sess.rollback()
finally:
if sess is not None:
sess.close()
self.lock.release()
self.log("Finished checking TLM rates.")
self.going.wait(30 * 60)
self.going.clear()
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, latest_rs.script)
rs = contract.insert_rate_script(
sess, year_start, '')
script = {
'bank_holidays': [
v.strftime("%Y-%m-%d")
for v in hols[year]]}
self.log(
"Updating rate script starting at " +
hh_format(year_start) + ".")
contract.update_rate_script(
sess, rs, rs.start_date, rs.finish_date,
json.dumps(
script, indent=' ', sort_keys=True))
sess.commit()
else:
self.log(
"The automatic importer is disabled. To "
"enable it, edit the contract properties to "
"set 'enabled' to True.")
except:
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 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 run(self):
sess = None
try:
self._log("Starting to parse the file with '" + self.parser_name +
"'.")
sess = Session()
g_batch = GBatch.get_by_id(sess, self.g_batch_id)
raw_bills = self.parser.make_raw_bills()
self._log("Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
bill_type = BillType.get_by_code(
sess, raw_bill['bill_type_code'])
g_supply = GSupply.get_by_mprn(sess, raw_bill['mprn'])
g_bill = g_batch.insert_g_bill(
sess, g_supply, bill_type, raw_bill['reference'],
raw_bill['account'], raw_bill['issue_date'],
raw_bill['start_date'], raw_bill['finish_date'],
raw_bill['kwh'], raw_bill['net_gbp'],
raw_bill['vat_gbp'], raw_bill['gross_gbp'],
raw_bill['raw_lines'], raw_bill['breakdown'])
sess.flush()
for raw_read in raw_bill['reads']:
prev_type = GReadType.get_by_code(
sess, raw_read['prev_type_code'])
pres_type = GReadType.get_by_code(
sess, raw_read['pres_type_code'])
g_unit = GUnit.get_by_code(sess, raw_read['unit'])
g_read = g_bill.insert_g_read(
sess, raw_read['msn'], g_unit,
raw_read['correction_factor'],
raw_read['calorific_value'],
raw_read['prev_value'], raw_read['prev_date'],
prev_type, raw_read['pres_value'],
raw_read['pres_date'], pres_type)
sess.expunge(g_read)
self.successful_bills.append(raw_bill)
sess.expunge(g_bill)
except BadRequest as e:
sess.rollback()
raw_bill['error'] = e.description
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log("The import has finished, but " +
str(len(self.failed_bills)) +
" bills failed to load, " +
"and so the whole import has been rolled back.")
except BadRequest as e:
self._log(e.description)
except BaseException:
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.rollback()
sess.close()
def run(self):
sess = None
try:
sess = Session()
self._log(
"Starting to parse the file with '" + self.parser_name + "'.")
bill_types = keydefaultdict(
lambda k: BillType.get_by_code(sess, k))
tprs = keydefaultdict(
lambda k: None if k is None else Tpr.get_by_code(sess, k))
read_types = keydefaultdict(
lambda k: ReadType.get_by_code(sess, k))
batch = Batch.get_by_id(sess, self.batch_id)
contract = batch.contract
raw_bills = self.parser.make_raw_bills()
self._log(
"Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
account = raw_bill['account']
supply = sess.query(Supply).join(Era).filter(
or_(
and_(
Era.imp_supplier_contract == contract,
Era.imp_supplier_account == account),
and_(
Era.exp_supplier_contract == contract,
Era.exp_supplier_account == account),
and_(
Era.mop_contract == contract,
Era.mop_account == account),
and_(
Era.hhdc_contract == contract,
Era.hhdc_account == account))
).distinct().order_by(Supply.id).first()
if supply is None:
raise BadRequest(
"Can't find an era with contract '" +
contract.name + "' and account '" + account + "'.")
with sess.begin_nested():
bill = batch.insert_bill(
sess, account, raw_bill['reference'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net'], raw_bill['vat'],
raw_bill['gross'],
bill_types[raw_bill['bill_type_code']],
raw_bill['breakdown'], supply)
for raw_read in raw_bill['reads']:
bill.insert_read(
sess, tprs[raw_read['tpr_code']],
raw_read['coefficient'], raw_read['units'],
raw_read['msn'], raw_read['mpan'],
raw_read['prev_date'], raw_read['prev_value'],
read_types[raw_read['prev_type_code']],
raw_read['pres_date'], raw_read['pres_value'],
read_types[raw_read['pres_type_code']])
self.successful_bills.append(raw_bill)
except BadRequest as e:
raw_bill['error'] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log(
"The import has finished, but there were " +
str(len(self.failed_bills)) + " failures, and so the "
"whole import has been rolled back.")
except:
sess.rollback()
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
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 = Datetime.now(pytz.utc)
if now > month_finish:
self.log(
"Checking to see if data is available from " +
str(month_start) + " to " + str(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)
parser = csv.reader(
(l.decode() for l in r.iter_lines()),
delimiter=',', quotechar='"')
piterator = iter(parser)
values = next(piterator)
values = next(piterator)
month_rcrcs = {}
for values in piterator:
hh_date = Datetime.strptime(
values[0], "%d/%m/%Y").replace(tzinfo=pytz.utc)
hh_date += relativedelta(minutes=30*int(values[2]))
if month_start <= hh_date <= month_finish:
month_rcrcs[key_format(hh_date)] = values[3]
if key_format(month_finish) in month_rcrcs:
self.log("The whole month's data is there.")
script = "def rates():\n return {\n" + \
',\n'.join(
"'" + k + "': " + month_rcrcs[k] for k in
sorted(month_rcrcs.keys())) + "}"
set_read_write(sess)
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,
rs.script)
contract.insert_rate_script(
sess, month_start, script)
sess.commit()
self.log("Added new rate script.")
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:
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 content(user, show_ignored, report_run_id):
sess = f = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(f"{FNAME}.csv", user)
f = open(running_name, mode="w", newline="")
report_run = ReportRun.get_by_id(sess, report_run_id)
props = Contract.get_non_core_by_name(sess, "configuration").make_properties()
ECOES_KEY = "ecoes"
try:
ecoes_props = props[ECOES_KEY]
except KeyError:
raise BadRequest(
f"The property {ECOES_KEY} cannot be found in the configuration "
f"properties."
)
for key in (
"user_name",
"password",
"prefix",
"exclude_mpan_cores",
"ignore_mpan_cores_msn",
):
try:
ecoes_props[key]
except KeyError:
raise BadRequest(
f"The property {key} cannot be found in the 'ecoes' section of "
f"the configuration properties."
)
exclude_mpan_cores = ecoes_props["exclude_mpan_cores"]
ignore_mpan_cores_msn = ecoes_props["ignore_mpan_cores_msn"]
url_prefix = ecoes_props["prefix"]
proxies = props.get("proxies", {})
s = requests.Session()
s.verify = False
r = s.get(url_prefix, proxies=proxies)
data = {
"Username": ecoes_props["user_name"],
"Password": ecoes_props["password"],
}
r = s.post(url_prefix, data=data, allow_redirects=False)
r = s.get(
f"{url_prefix}NonDomesticCustomer/ExportPortfolioMPANs?fileType=csv",
proxies=proxies,
)
_process(
sess,
r.text.splitlines(True),
exclude_mpan_cores,
ignore_mpan_cores_msn,
f,
show_ignored,
report_run,
)
report_run.update("finished")
sess.commit()
except BaseException as e:
msg = traceback.format_exc()
if report_run is not None:
report_run.update("interrupted")
report_run.insert_row(sess, "", ["error"], {"error": msg}, {})
sess.commit()
sys.stderr.write(msg)
f.write(msg)
raise e
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def run(self):
sess = None
try:
sess = Session()
batch = Batch.get_by_id(sess, self.batch_id)
bill_types = keydefaultdict(
lambda k: BillType.get_by_code(sess, k))
tprs = keydefaultdict(lambda k: None
if k is None else Tpr.get_by_code(sess, k))
read_types = keydefaultdict(
lambda k: ReadType.get_by_code(sess, k))
for bf in (sess.query(BatchFile).filter(
BatchFile.batch == batch).order_by(
BatchFile.upload_timestamp)):
self.parser = _process_batch_file(sess, bf, self._log)
for self.bill_num, raw_bill in enumerate(
self.parser.make_raw_bills()):
if "error" in raw_bill:
self.failed_bills.append(raw_bill)
else:
try:
mpan_core = raw_bill["mpan_core"]
supply = Supply.get_by_mpan_core(sess, mpan_core)
with sess.begin_nested():
bill = batch.insert_bill(
sess,
raw_bill["account"],
raw_bill["reference"],
raw_bill["issue_date"],
raw_bill["start_date"],
raw_bill["finish_date"],
raw_bill["kwh"],
raw_bill["net"],
raw_bill["vat"],
raw_bill["gross"],
bill_types[raw_bill["bill_type_code"]],
raw_bill["breakdown"],
supply,
)
for raw_read in raw_bill["reads"]:
bill.insert_read(
sess,
tprs[raw_read["tpr_code"]],
raw_read["coefficient"],
raw_read["units"],
raw_read["msn"],
raw_read["mpan"],
raw_read["prev_date"],
raw_read["prev_value"],
read_types[raw_read["prev_type_code"]],
raw_read["pres_date"],
raw_read["pres_value"],
read_types[raw_read["pres_type_code"]],
)
self.successful_bills.append(raw_bill)
except KeyError as e:
err = raw_bill.get("error", "")
raw_bill["error"] = err + " " + str(e)
self.failed_bills.append(raw_bill)
except BadRequest as e:
raw_bill["error"] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log(f"The import has finished, but there were "
f"{len(self.failed_bills)} failures, and so the "
f"whole import has been rolled back.")
except BadRequest as e:
sess.rollback()
self._log(f"Problem: {e.description}")
except BaseException:
sess.rollback()
self._log(f"I've encountered a problem: {traceback.format_exc()}")
finally:
if sess is not None:
sess.close()
def run(self):
def log_f(msg):
self.log_lines.appendleft(
f"{hh_format(utc_datetime_now())}: {msg}")
sess = None
try:
sess = Session()
zip_file = ZipFile(self.f)
znames = {}
for zname in zip_file.namelist():
log_f(f"Inspecting {zname} in ZIP file")
csv_file = StringIO(zip_file.read(zname).decode("utf-8"))
csv_reader = iter(csv.reader(csv_file))
next(csv_reader) # Skip titles
table_name = "_".join(zname.split("_")[:-1])
znames[table_name] = csv_reader
for tname, func in [
("Market_Participant", _import_Market_Participant),
("Market_Role", _import_Market_Role),
("Market_Participant_Role", _import_Market_Participant_Role),
("Line_Loss_Factor_Class", _import_Line_Loss_Factor_Class),
("Meter_Timeswitch_Class", _import_Meter_Timeswitch_Class),
("MTC_in_PES_Area", _import_MTC_in_PES_Area),
("MTC_Meter_Type", _import_MTC_Meter_Type),
(
"Standard_Settlement_Configuration",
_import_Standard_Settlement_Configuration,
),
(
"Valid_MTC_LLFC_SSC_PC_Combination",
_import_Valid_MTC_LLFC_SSC_PC_Combination,
),
]:
if tname in znames:
log_f(f"Found {tname} and will now import it.")
func(sess, znames[tname])
else:
log_f(f"Can't find {tname} in the ZIP file.")
sess.commit()
except BadRequest as e:
sess.rollback()
try:
self.rd_lock.acquire()
self.error_message = e.description
finally:
self.rd_lock.release()
except BaseException:
sess.rollback()
try:
self.rd_lock.acquire()
self.error_message = traceback.format_exc()
finally:
self.rd_lock.release()
finally:
if sess is not None:
sess.close()
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 = json.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, 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,
json.dumps(
script, indent=' ', sort_keys=True))
sess.commit()
else:
self.log(
"The automatic importer is disabled. To "
"enable it, edit the contract properties to "
"set 'enabled' to True.")
except:
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 run(self):
sess = None
try:
sess = Session()
self._log(
"Starting to parse the file with '" + self.parser_name + "'.")
set_read_write(sess)
batch = Batch.get_by_id(sess, self.batch_id)
raw_bills = self.parser.make_raw_bills()
self._log(
"Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
with sess.begin_nested():
sess.execute(
"set transaction isolation level serializable "
"read write")
bill_type = BillType.get_by_code(
sess, raw_bill['bill_type_code'])
bill = batch.insert_bill(
sess, raw_bill['account'], raw_bill['reference'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net'], raw_bill['vat'],
raw_bill['gross'],
bill_type, raw_bill['breakdown'])
sess.flush()
for raw_read in raw_bill['reads']:
tpr_code = raw_read['tpr_code']
if tpr_code is None:
tpr = None
else:
tpr = Tpr.get_by_code(sess, tpr_code)
prev_type = ReadType.get_by_code(
sess, raw_read['prev_type_code'])
pres_type = ReadType.get_by_code(
sess, raw_read['pres_type_code'])
bill.insert_read(
sess, tpr, raw_read['coefficient'],
raw_read['units'], raw_read['msn'],
raw_read['mpan'], raw_read['prev_date'],
raw_read['prev_value'], prev_type,
raw_read['pres_date'], raw_read['pres_value'],
pres_type)
self.successful_bills.append(raw_bill)
except BadRequest as e:
raw_bill['error'] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log(
"The import has finished, but there were " +
str(len(self.failed_bills)) + " failures, and so the "
"whole import has been rolled back.")
except:
sess.rollback()
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
def content(
batch_id,
bill_id,
contract_id,
start_date,
finish_date,
user,
mpan_cores,
fname_additional,
):
caches = {}
tmp_file = sess = supply_id = None
forecast_date = to_utc(Datetime.max)
try:
running_name, finished_name = chellow.dloads.make_names(
"bill_check_" + fname_additional + ".csv", user)
tmp_file = open(running_name, mode="w", newline="")
writer = csv.writer(tmp_file, lineterminator="\n")
sess = Session()
report_run = ReportRun("bill_check", user, fname_additional)
sess.add(report_run)
bills = (sess.query(Bill).order_by(
Bill.supply_id, Bill.reference).options(
joinedload(Bill.supply),
subqueryload(Bill.reads).joinedload(RegisterRead.present_type),
subqueryload(Bill.reads).joinedload(
RegisterRead.previous_type),
joinedload(Bill.batch),
))
if len(mpan_cores) > 0:
mpan_cores = list(map(parse_mpan_core, mpan_cores))
supply_ids = [
i[0] for i in sess.query(Era.supply_id).filter(
or_(
Era.imp_mpan_core.in_(mpan_cores),
Era.exp_mpan_core.in_(mpan_cores),
)).distinct()
]
bills = bills.join(Supply).filter(Supply.id.in_(supply_ids))
if batch_id is not None:
batch = Batch.get_by_id(sess, batch_id)
bills = bills.filter(Bill.batch == batch)
contract = batch.contract
elif bill_id is not None:
bill = Bill.get_by_id(sess, bill_id)
bills = bills.filter(Bill.id == bill.id)
contract = bill.batch.contract
elif contract_id is not None:
contract = Contract.get_by_id(sess, contract_id)
bills = bills.join(Batch).filter(
Batch.contract == contract,
Bill.start_date <= finish_date,
Bill.finish_date >= start_date,
)
vbf = chellow.computer.contract_func(caches, contract, "virtual_bill")
if vbf is None:
raise BadRequest("The contract " + contract.name +
" doesn't have a function virtual_bill.")
virtual_bill_titles_func = chellow.computer.contract_func(
caches, contract, "virtual_bill_titles")
if virtual_bill_titles_func is None:
raise BadRequest("The contract " + contract.name +
" doesn't have a function virtual_bill_titles.")
virtual_bill_titles = virtual_bill_titles_func()
titles = [
"batch",
"bill-reference",
"bill-type",
"bill-kwh",
"bill-net-gbp",
"bill-vat-gbp",
"bill-start-date",
"bill-finish-date",
"imp-mpan-core",
"exp-mpan-core",
"site-code",
"site-name",
"covered-from",
"covered-to",
"covered-bills",
"metered-kwh",
]
for t in virtual_bill_titles:
titles.append("covered-" + t)
titles.append("virtual-" + t)
if t.endswith("-gbp"):
titles.append("difference-" + t)
writer.writerow(titles)
bill_map = defaultdict(set, {})
for bill in bills:
bill_map[bill.supply.id].add(bill.id)
for supply_id in bill_map.keys():
_process_supply(
sess,
caches,
supply_id,
bill_map,
forecast_date,
contract,
vbf,
virtual_bill_titles,
writer,
titles,
report_run,
)
report_run.update("finished")
sess.commit()
except BadRequest as e:
if report_run is not None:
report_run.update("problem")
if supply_id is None:
prefix = "Problem: "
else:
prefix = "Problem with supply " + str(supply_id) + ":"
tmp_file.write(prefix + e.description)
except BaseException:
if report_run is not None:
report_run.update("interrupted")
if supply_id is None:
prefix = "Problem: "
else:
prefix = "Problem with supply " + str(supply_id) + ":"
msg = traceback.format_exc()
sys.stderr.write(msg + "\n")
tmp_file.write(prefix + msg)
finally:
if sess is not None:
sess.close()
tmp_file.close()
os.rename(running_name, finished_name)
def 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 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 run(self):
sess = None
try:
sess = Session()
self._log("Starting to parse the file with '" + self.parser_name +
"'.")
bill_types = keydefaultdict(
lambda k: BillType.get_by_code(sess, k))
tprs = keydefaultdict(lambda k: None
if k is None else Tpr.get_by_code(sess, k))
read_types = keydefaultdict(
lambda k: ReadType.get_by_code(sess, k))
batch = Batch.get_by_id(sess, self.batch_id)
contract = batch.contract
raw_bills = self.parser.make_raw_bills()
self._log("Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
account = raw_bill['account']
supply = sess.query(Supply).join(Era).filter(
or_(
and_(Era.imp_supplier_contract == contract,
Era.imp_supplier_account == account),
and_(Era.exp_supplier_contract == contract,
Era.exp_supplier_account == account),
and_(Era.mop_contract == contract,
Era.mop_account == account),
and_(Era.dc_contract == contract, Era.dc_account ==
account))).distinct().order_by(
Supply.id).first()
if supply is None:
raise BadRequest("Can't find an era with contract '" +
contract.name + "' and account '" +
account + "'.")
with sess.begin_nested():
bill = batch.insert_bill(
sess, account, raw_bill['reference'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net'], raw_bill['vat'],
raw_bill['gross'],
bill_types[raw_bill['bill_type_code']],
raw_bill['breakdown'], supply)
for raw_read in raw_bill['reads']:
bill.insert_read(
sess, tprs[raw_read['tpr_code']],
raw_read['coefficient'], raw_read['units'],
raw_read['msn'], raw_read['mpan'],
raw_read['prev_date'], raw_read['prev_value'],
read_types[raw_read['prev_type_code']],
raw_read['pres_date'], raw_read['pres_value'],
read_types[raw_read['pres_type_code']])
self.successful_bills.append(raw_bill)
except BadRequest as e:
raw_bill['error'] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log("The import has finished, but there were " +
str(len(self.failed_bills)) +
" failures, and so the "
"whole import has been rolled back.")
except BadRequest as e:
sess.rollback()
self._log("Problem: " + e.description)
except BaseException:
sess.rollback()
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
