def edit_report(report_id):
try:
set_read_write()
name = POST_str("name")
script = POST_str("script")
template = POST_str("template")
report = Report.query.get(report_id)
report.update(name, script, template)
db.session.commit()
return chellow_redirect(
'/chellow/reports/' + str(report.id) + '/', 303)
except:
return render_template(
'report.html', report=report, message=traceback.format_exc())
def add_report():
set_read_write()
is_core = POST_bool('is_core')
name = POST_str("name")
report = Report(None, is_core, name, "", None)
db.session.add(report)
try:
db.session.commit()
except ProgrammingError, e:
if 'duplicate key value violates unique constraint' in str(e):
return Response(
"There's already a report with that name.", status=400)
else:
raise
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 = datetime.datetime.now(pytz.utc)
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 + ".")
ct_tz = pytz.timezone('Europe/London')
next(parser, None)
month_tlms = {}
for values in parser:
hh_date_ct = ct_tz.localize(
datetime.datetime.strptime(
values[0], "%d/%m/%Y"))
hh_date = pytz.utc.normalize(
hh_date_ct.astimezone(pytz.utc))
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())) + "}"
set_read_write(sess)
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 = datetime.datetime.strptime(
line[-8:], "%Y%m%d"). \
replace(tzinfo=pytz.utc)
hols[dt.year].append(dt)
for year in sorted(hols.keys()):
set_read_write(sess)
year_start = datetime.datetime(
year, 1, 1, tzinfo=pytz.utc)
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):
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 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 run(self):
while not self.stopped.isSet():
if self.lock.acquire(False):
sess = 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)
ct_tz = pytz.timezone('Europe/London')
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 = ct_tz.localize(raw_date)
hh_date = pytz.utc.normalize(
hh_date_ct.astimezone(pytz.utc))
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]
set_read_write(sess)
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:
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()
