def run(self):
sess = None
try:
self._log(
"Starting to parse the file with '" +
self.contract_name + "'.")
sess = db.session()
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:
db.set_read_write(sess)
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'])
read = 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)
sess.expunge(read)
sess.commit()
self.successful_bills.append(raw_bill)
sess.expunge(bill)
except UserException, e:
sess.rollback()
raw_bill['error'] = str(e)
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.")
eras = sess.query(Era).filter(
Era.supply == supply).order_by(Era.start_date.desc())
return {
'supply': supply, 'messages': messages, 'sources': sources,
'generator_types': generator_types, 'gsp_groups': gsp_groups,
'eras': eras}
sess = None
try:
sess = db.session()
if inv.getRequest().getMethod() == 'GET':
supply_id = inv.getLong('supply_id')
supply = Supply.get_by_id(sess, supply_id)
render(inv, template, make_fields(sess, supply))
else:
db.set_read_write(sess)
supply_id = inv.getLong('supply_id')
supply = Supply.get_by_id(sess, supply_id)
if inv.hasParameter("delete"):
supply.delete(sess)
sess.commit()
inv.sendSeeOther("/reports/99/output/")
elif inv.hasParameter("insert_era"):
start_date = form_date(inv, 'start')
supply.insert_era_at(sess, start_date)
sess.commit()
inv.sendSeeOther("/reports/7/output/?supply_id=" + str(supply.id))
else:
name = inv.getString("name")
source_id = inv.getLong("source_id")
def run(self):
while not self.stopped.isSet():
if self.lock.acquire(False):
sess = None
try:
sess = db.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.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 UserException(
"The property " +
ELEXON_PORTAL_SCRIPTING_KEY_KEY +
" cannot be found in the configuration "
"properties.")
data = urllib2.urlopen(
'https://downloads.elexonportal.co.uk/file/'
'download/RCRC_FILE?key=' + scripting_key)
parser = csv.reader(data, delimiter=',', quotechar='"')
piterator = iter(parser)
values = piterator.next()
values = piterator.next()
month_rcrcs = {}
for values in piterator:
hh_date = datetime.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())) + "}"
db.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:
self.log(
"There isn't a whole month there yet. The "
"last date is " +
sorted(month_rcrcs.keys())[-1])
except:
self.log("Outer problem " + traceback.format_exc())
if sess is not None:
sess.rollback()
finally:
try:
if sess is not None:
sess.close()
finally:
self.lock.release()
self.log("Finished checking RCRC rates.")
self.going.wait(30 * 60)
self.going.clear()
def import_now(self):
if self.lock.acquire(False):
try:
found_new = True
while found_new:
found_new = False
sess = None
ftp = None
try:
sess = db.session()
contract = Contract.get_hhdc_by_id(
sess, self.contract_id)
properties = contract.make_properties()
host_name = properties["hostname"]
user_name = properties["username"]
password = properties["password"]
try:
port = properties["port"]
except KeyError:
port = 21
file_type = properties["file_type"]
directories = properties['directories']
state = contract.make_state()
try:
last_import_keys = state['last_import_keys']
except KeyError:
last_import_keys = {}
state['last_import_keys'] = last_import_keys
sess.rollback()
self.log(
"Connecting to ftp server at " + host_name +
":" + str(port) + ".")
ftp = ftplib.FTP()
ftp.connect(host_name, port)
ftp.login(user_name, password)
home_path = ftp.pwd()
file = None
for directory in directories:
self.log(
"Checking the directory '" + directory + "'.")
try:
last_import_key = last_import_keys[directory]
except KeyError:
last_import_key = ''
last_import_keys[directory] = last_import_key
dir_path = home_path + "/" + directory
ftp.cwd(dir_path)
files = []
for fname in ftp.nlst():
fpath = dir_path + "/" + fname
try:
ftp.cwd(fpath)
continue # directory
except ftplib.error_perm:
pass
key = ftp.sendcmd(
"MDTM " + fpath).split()[1] + '_' + fname
if key > last_import_key:
files.append((key, fpath))
if len(files) > 0:
file = sorted(files)[0]
last_import_keys[directory] = file[0]
break
if file is None:
self.log("No new files found.")
ftp.quit()
self.log("Logged out.")
else:
key, fpath = file
self.log(
"Attempting to download " + fpath +
" with key " + key + ".")
f = tempfile.TemporaryFile()
ftp.retrbinary("RETR " + fpath, f.write)
self.log("File downloaded successfully.")
ftp.quit()
self.log("Logged out.")
self.log("Treating files as type " + file_type)
f.seek(0, os.SEEK_END)
fsize = f.tell()
f.seek(0)
self.importer = HhDataImportProcess(
self.contract_id, 0, f, fpath + file_type,
fsize)
self.importer.run()
messages = self.importer.messages
self.importer = None
for message in messages:
self.log(message)
if len(messages) > 0:
raise UserException("Problem loading file.")
db.set_read_write(sess)
contract = Contract.get_hhdc_by_id(
sess, self.contract_id)
contract.update_state(state)
sess.commit()
self.log("Finished loading '" + fpath)
found_new = True
except UserException, e:
self.log("Problem " + str(e))
sess.rollback()
except Exception:
self.log("Unknown Exception " + traceback.format_exc())
sess.rollback()
finally:
try:
if sess is not None:
sess.close()
except:
self.log(
"Unknown Exception II" +
traceback.format_exc())
def run(self):
while not self.stopped.isSet():
if self.lock.acquire(False):
sess = None
try:
sess = db.session()
self.log("Starting to check bank holidays")
contract = Contract.get_non_core_by_id(sess, db_id)
contract_props = contract.make_properties()
if contract_props.get('enabled', False):
url_str = contract_props['url']
self.log("Downloading from " + url_str + ".")
url = urlparse.urlparse(url_str)
if url.scheme == 'https':
conn = httplib.HTTPSConnection(
url.hostname, url.port)
else:
conn = httplib.HTTPConnection(
url.hostname, url.port)
conn.request("GET", url.path)
res = conn.getresponse()
self.log(
"Received " + str(res.status) + " " + res.reason)
PREFIX = 'DTSTART;VALUE=DATE:'
hols = collections.defaultdict(list)
for line in res.read().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()):
db.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())
if sess is not None:
sess.rollback()
finally:
try:
if sess is not None:
sess.close()
finally:
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 = db.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.datetime.now(pytz.utc)
if contract.make_properties().get('enabled', False):
if now > next_month_start:
self.log(
"Checking to see if data is available from " +
str(this_month_start) + " to " +
str(next_month_start - HH) +
" on Elexon Portal.")
conn = httplib.HTTPConnection(
"www2.nationalgrid.com")
conn.request(
"GET", "/WorkArea/DownloadAsset.aspx?id=32719")
res = conn.getresponse()
self.log(
"Received " + str(res.status) + " " +
res.reason)
data = res.read()
book = xlrd.open_workbook(file_contents=data)
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.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())) + "}"
db.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())
if sess is not None:
sess.rollback()
finally:
try:
if sess is not None:
sess.close()
finally:
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 = db.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 UserException(
"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 = urlparse.urlparse(url_str)
if url.scheme == 'https':
conn = httplib.HTTPSConnection(
url.hostname, url.port)
else:
conn = httplib.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]
db.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.iteritems())}
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())
if sess is not None:
sess.rollback()
finally:
try:
if sess is not None:
sess.close()
finally:
self.lock.release()
self.log("Finished checking System Price rates.")
self.going.wait(24 * 60 * 60)
self.going.clear()