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