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()