def get_db_id():
sess = None
try:
sess = Session()
return Contract.get_non_core_by_name(sess, 'bank_holidays').id
finally:
if sess is not None:
sess.close()
def content(start_date, finish_date, contract_id, sess):
caches = {}
try:
contract = Contract.get_mop_by_id(sess, contract_id)
forecast_date = chellow.computer.forecast_date()
yield ','.join(
(
'Import MPAN Core', 'Export MPAN Core', 'Start Date',
'Finish Date'))
bill_titles = chellow.computer.contract_func(
caches, contract, 'virtual_bill_titles', None)()
for title in bill_titles:
yield ',' + title
yield '\n'
for era in sess.query(Era).filter(
or_(Era.finish_date == null(), Era.finish_date >= start_date),
Era.start_date <= finish_date,
Era.mop_contract_id == contract.id).order_by(Era.supply_id):
import_mpan_core = era.imp_mpan_core
if import_mpan_core is None:
import_mpan_core_str = ''
else:
mpan_core = import_mpan_core
is_import = True
import_mpan_core_str = mpan_core
export_mpan_core = era.exp_mpan_core
if export_mpan_core is None:
export_mpan_core_str = ''
else:
is_import = False
mpan_core = export_mpan_core
export_mpan_core_str = mpan_core
yield import_mpan_core_str + ',' + export_mpan_core_str + ',' + \
hh_format(start_date) + ',' + hh_format(finish_date) + ','
supply_source = chellow.computer.SupplySource(
sess, start_date, finish_date, forecast_date, era, is_import,
None, caches)
chellow.computer.contract_func(
caches, contract, 'virtual_bill', None)(supply_source)
bill = supply_source.mop_bill
for title in bill_titles:
if title in bill:
yield '"' + str(bill[title]) + '",'
del bill[title]
else:
yield ','
for k in sorted(bill.keys()):
yield ',"' + k + '","' + str(bill[k]) + '"'
yield '\n'
except:
yield traceback.format_exc()
def do_post(sess):
batch_id = bill_id = contract_id = start_date = finish_date = None
if "mpan_cores" in request.values:
mpan_cores = req_str("mpan_cores").splitlines()
else:
mpan_cores = []
fname_additional = ""
if "batch_id" in request.values:
batch_id = req_int("batch_id")
batch = Batch.get_by_id(sess, batch_id)
fname_additional = f"_batch_{batch.reference}"
elif "bill_id" in request.values:
bill_id = req_int("bill_id")
bill = Bill.get_by_id(sess, bill_id)
fname_additional = "bill_" + str(bill.id)
elif "contract_id" in request.values:
contract_id = req_int("contract_id")
contract = Contract.get_by_id(sess, contract_id)
start_date = req_date("start_date")
finish_date = req_date("finish_date")
s = ["contract", str(contract.id)]
for dt in (start_date, finish_date):
s.append(hh_format(dt).replace(" ", "T").replace(":", ""))
fname_additional = "_".join(s)
else:
raise BadRequest(
"The bill check needs a batch_id, a bill_id or a start_date and "
"finish_date.")
args = (
batch_id,
bill_id,
contract_id,
start_date,
finish_date,
g.user,
mpan_cores,
fname_additional,
)
threading.Thread(target=content, args=args).start()
return chellow_redirect("/downloads", 303)
def before_first_request():
try:
scheme = request.headers["X-Forwarded-Proto"]
except KeyError:
sess = Session()
try:
config_contract = Contract.get_non_core_by_name(
sess, "configuration")
props = config_contract.make_properties()
scheme = props.get("redirect_scheme", "http")
finally:
sess.close()
try:
host = request.headers["X-Forwarded-Host"]
except KeyError:
host = request.host
chellow.utils.url_root = f"{scheme}://{host}/"
def chellow_context_processor():
global_alerts = []
for task in chellow.hh_importer.tasks.values():
if task.is_error:
try:
contract = Contract.get_by_id(g.sess, task.contract_id)
global_alerts.append(
f"There's a problem with the automatic HH data "
f"importer for contract '{contract.name}'.")
except NotFound:
pass
for importer in (
chellow.bsuos.bsuos_importer,
chellow.g_cv.g_cv_importer,
chellow.bmarketidx.bmarketidx_importer,
):
if importer is not None and importer.global_alert is not None:
global_alerts.append(importer.global_alert)
return {"current_user": g.user, "global_alerts": global_alerts}
def test_http_supplier_batch_with_mpan_cores(mocker, client, sess):
market_role_X = MarketRole.insert(sess, "X", "Supplier")
participant = Participant.insert(sess, "hhak", "AK Industries")
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
batch = supplier_contract.insert_batch(sess, "005", "batch 5")
sess.commit()
MockThread = mocker.patch("chellow.reports.report_111.threading.Thread")
data = {
"batch_id": str(batch.id),
"mpan_cores": "22 1065 3921 534",
}
response = client.get("/reports/111", data=data)
match(response, 303)
expected_args = (
batch.id,
None,
None,
None,
None,
None,
["22 1065 3921 534"],
"_batch_005",
)
MockThread.assert_called_with(target=chellow.reports.report_111.content,
args=expected_args)
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 = self.global_alert = None
try:
sess = Session()
self.log("Starting to check BSUoS rates.")
contract = Contract.get_non_core_by_name(sess, "bsuos")
props = contract.make_properties()
if props.get("enabled", False):
urls = set(props.get("urls", []))
if props.get("discover_urls", False):
urls.update(_discover_urls(self.log))
url_list = sorted(urls)
self.log("List of URLs to process: " + str(url_list))
for url in url_list:
self.process_url(sess, url, contract)
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())
self.global_alert = (
"There's a problem with the BSUoS automatic importer."
)
sess.rollback()
finally:
if sess is not None:
sess.close()
self.lock.release()
self.log("Finished checking BSUoS rates.")
self.going.wait(60 * 60 * 24)
self.going.clear()
def content(user, file_like):
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"asset_comparison.csv", user)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
props = Contract.get_non_core_by_name(
sess, "configuration").make_properties()
_process_sites(sess, file_like, writer, props)
except BaseException:
msg = traceback.format_exc()
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):
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()
from dateutil.relativedelta import relativedelta
from sqlalchemy.sql.expression import null
from sqlalchemy import or_
from chellow.models import Session, Contract, RateScript
from chellow.utils import HH, hh_after, utc_datetime
import chellow.computer
import chellow.duos
sess = None
try:
sess = Session()
db_id = Contract.get_non_core_by_name(sess, 'triad').id
triad_rates_contract_id = Contract.get_non_core_by_name(
sess, 'triad_rates').id
finally:
if sess is not None:
sess.close()
def triad_calc(
hh, prefix, triad_data, financial_year_start, financial_year_finish,
data_source, month_begin):
gsp_kw = 0
for i, triad_hh in enumerate(triad_data):
triad_prefix = prefix + '-' + str(i + 1)
hh[triad_prefix + '-date'] = triad_hh['hist-start']
hh[triad_prefix + '-msp-kw'] = triad_hh['msp-kw']
hh[triad_prefix + '-status'] = triad_hh['status']
hh[triad_prefix + '-laf'] = triad_hh['laf']
hh[triad_prefix + '-gsp-kw'] = triad_hh['gsp-kw']
gsp_kw += triad_hh['gsp-kw']
def test_supply(mocker, sess, client):
site = Site.insert(sess, "22488", "Water Works")
g_dn = GDn.insert(sess, "EE", "East of England")
g_ldz = g_dn.insert_g_ldz(sess, "EA")
g_exit_zone = g_ldz.insert_g_exit_zone(sess, "EA1")
insert_g_units(sess)
g_unit_M3 = GUnit.get_by_code(sess, "M3")
participant = Participant.insert(sess, "CALB", "AK Industries")
market_role_Z = MarketRole.get_by_code(sess, "Z")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
g_cv_rate_script = {
"cvs": {
"EA": {
1: {
"applicable_at": utc_datetime(2020, 10, 3),
"cv": 39.2000
},
}
}
}
Contract.insert_non_core(sess, "g_cv", "", {}, utc_datetime(2000, 1, 1),
None, g_cv_rate_script)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
charge_script = """
import chellow.g_ccl
from chellow.g_engine import g_rates
from chellow.utils import reduce_bill_hhs
def virtual_bill_titles():
return [
'units_consumed', 'correction_factor', 'unit_code', 'unit_factor',
'calorific_value', 'kwh', 'gas_rate', 'gas_gbp', 'ccl_rate',
'standing_rate', 'standing_gbp', 'net_gbp', 'vat_gbp', 'gross_gbp',
'problem']
def virtual_bill(ds):
chellow.g_ccl.vb(ds)
for hh in ds.hh_data:
start_date = hh['start_date']
bill_hh = ds.bill_hhs[start_date]
bill_hh['units_consumed'] = hh['units_consumed']
bill_hh['correction_factor'] = {hh['correction_factor']}
bill_hh['unit_code'] = {hh['unit_code']}
bill_hh['unit_factor'] = {hh['unit_factor']}
bill_hh['calorific_value'] = {hh['calorific_value']}
kwh = hh['kwh']
bill_hh['kwh'] = kwh
gas_rate = float(
g_rates(ds.sess, ds.caches, db_id, start_date)['gas_rate'])
bill_hh['gas_rate'] = {gas_rate}
bill_hh['gas_gbp'] = gas_rate * kwh
bill_hh['ccl_kwh'] = kwh
ccl_rate = hh['ccl']
bill_hh['ccl_rate'] = {ccl_rate}
bill_hh['ccl_kwh'] = kwh
bill_hh['ccl_gbp'] = kwh * ccl_rate
if hh['utc_is_month_end']:
standing_rate = float(
g_rates(
ds.sess, ds.caches, db_id, start_date)['standing_rate'])
bill_hh['standing_rate'] = {standing_rate}
bill_hh['standing_gbp'] = standing_rate
if hh['utc_decimal_hour'] == 0:
pass
bill_hh['net_gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
bill_hh['vat_gbp'] = 0
bill_hh['gross_gbp'] = bill_hh['net_gbp'] + bill_hh['vat_gbp']
ds.bill = reduce_bill_hhs(ds.bill_hhs)
"""
g_contract_rate_script = {
"gas_rate": 0.1,
"standing_rate": 0.1,
}
g_contract = GContract.insert(
sess,
"Fusion 2020",
charge_script,
{},
utc_datetime(2000, 1, 1),
None,
g_contract_rate_script,
)
insert_g_reading_frequencies(sess)
g_reading_frequency_M = GReadingFrequency.get_by_code(sess, "M")
msn = "hgeu8rhg"
g_supply = site.insert_g_supply(
sess,
"87614362",
"main",
g_exit_zone,
utc_datetime(2010, 1, 1),
None,
msn,
1,
g_unit_M3,
g_contract,
"d7gthekrg",
g_reading_frequency_M,
)
g_batch = g_contract.insert_g_batch(sess, "b1", "Jan batch")
breakdown = {"units_consumed": 771}
insert_bill_types(sess)
bill_type_N = BillType.get_by_code(sess, "N")
insert_g_read_types(sess)
g_read_type_A = GReadType.get_by_code(sess, "A")
g_bill = g_batch.insert_g_bill(
sess,
g_supply,
bill_type_N,
"55h883",
"dhgh883",
utc_datetime(2019, 4, 3),
utc_datetime(2015, 9, 1),
utc_datetime(2015, 9, 30, 22, 30),
Decimal("45"),
Decimal("12.40"),
Decimal("1.20"),
Decimal("14.52"),
"",
breakdown,
)
g_bill.insert_g_read(
sess,
msn,
g_unit_M3,
Decimal("1"),
Decimal("37"),
Decimal("90"),
utc_datetime(2015, 9, 1),
g_read_type_A,
Decimal("890"),
utc_datetime(2015, 9, 25),
g_read_type_A,
)
sess.commit()
mock_file = BytesIO()
mock_file.close = mocker.Mock()
mocker.patch("chellow.reports.report_g_monthly_duration.open",
return_value=mock_file)
mocker.patch(
"chellow.reports.report_g_monthly_duration.chellow.dloads.make_names",
return_value=("a", "b"),
)
mocker.patch("chellow.reports.report_g_monthly_duration.os.rename")
user = mocker.Mock()
site_id = site.id
g_supply_id = g_supply.id
compression = False
finish_year = 2015
finish_month = 9
months = 1
now = utc_datetime(2020, 9, 1)
chellow.reports.report_g_monthly_duration.content(
site_id,
g_supply_id,
user,
compression,
finish_year,
finish_month,
months,
now=now,
)
sheet = odio.parse_spreadsheet(mock_file)
table = list(sheet.tables[1].rows)
expected = [
[
"creation_date",
"mprn",
"supply_name",
"exit_zone",
"msn",
"unit",
"contract",
"site_id",
"site_name",
"associated_site_ids",
"month",
"kwh",
"gbp",
"billed_kwh",
"billed_gbp",
"units_consumed",
"correction_factor",
"unit_code",
"unit_factor",
"calorific_value",
"kwh",
"gas_rate",
"gas_gbp",
"ccl_rate",
"standing_rate",
"standing_gbp",
"net_gbp",
"vat_gbp",
"gross_gbp",
"problem",
],
[
Datetime(2020, 9, 1, 1),
"87614362",
"main",
"EA1",
"hgeu8rhg",
"M3",
"Fusion 2020",
"22488",
"Water Works",
"",
Datetime(2015, 9, 30, 23, 30),
10888.888888888665,
1146.1869155555785,
45.0,
12.4,
1000.0000000000156,
1.0,
"M3",
1.0,
39.2,
10888.888888888665,
0.1,
1088.8888888888619,
0.00525288,
0.1,
0.1,
1146.1869155555785,
0.0,
1146.1869155555785,
None,
],
]
assert expected == table
def test_https_handler(mocker, sess):
site = Site.insert(sess, "CI017", "Water Works")
market_role_Z = MarketRole.insert(sess, "Z", "Non-core")
participant = Participant.insert(sess, "CALB", "AK Industries")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(sess, market_role_M, "Fusion Mop Ltd",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_C, "Fusion DC",
utc_datetime(2000, 1, 1), None, None)
mop_contract = Contract.insert_mop(sess, "Fusion", participant, "", {},
utc_datetime(2000, 1, 1), None, {})
dc_contract = Contract.insert_hhdc(sess, "Fusion DC 2000", participant, "",
{}, utc_datetime(2000, 1, 1), None, {})
pc = Pc.insert(sess, "00", "hh", utc_datetime(2000, 1, 1), None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
imp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dno = participant.insert_party(sess, market_role_R, "WPD",
utc_datetime(2000, 1, 1), None, "22")
meter_type = MeterType.insert(sess, "C5", "COP 1-5",
utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(sess, "CR", "Credit",
utc_datetime(1996, 1, 1),
None)
Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
dno.insert_llfc(
sess,
"521",
"Export (HV)",
voltage_level,
False,
False,
utc_datetime(1996, 1, 1),
None,
)
insert_sources(sess)
source = Source.get_by_code(sess, "net")
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "E")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
supply = site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2000, 1, 1),
None,
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
None,
energisation_status,
{},
"22 7867 6232 781",
"510",
imp_supplier_contract,
"7748",
361,
None,
None,
None,
None,
None,
)
era = supply.eras[0]
era.insert_channel(sess, True, "ACTIVE")
sess.commit()
mock_requests = mocker.patch("chellow.hh_importer.requests")
mock_response = mocker.Mock()
mock_requests.get.return_value = mock_response
mock_response.json.return_value = {
"DataPoints": [{
"Flags": 0,
"Time": 636188256000000000,
"Value": 21
}]
}
log = []
def log_f(msg):
log.append(msg)
properties = {
"enabled":
True,
"protocol":
"https",
"download_days":
8,
"url_template":
"https://example.com/?from="
"{{chunk_start.strftime('%d/%m/%Y')}}&to="
"{{chunk_finish.strftime('%d/%m/%Y')}}",
"url_values": {
"22 7907 4116 080": {
"api_key": "768234ht"
}
},
}
now = utc_datetime(2020, 12, 22)
https_handler(sess, log_f, properties, dc_contract, now=now)
expected_log = [
"Window start: 2020-12-14 00:00",
"Window finish: 2020-12-21 23:30",
"Looking at MPAN core 22 7867 6232 781.",
"Retrieving data from "
"https://example.com/?from=14/12/2020&to=21/12/2020.",
"Finished loading.",
]
assert log == expected_log
def ftp_handler(self, sess, properties, contract):
host_name = properties["hostname"]
user_name = properties["username"]
password = properties["password"]
try:
port = properties["port"]
except KeyError:
port = None
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()
if port is None:
ftp.connect(host=host_name, timeout=120)
else:
ftp.connect(host=host_name, port=port, timeout=120)
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] = ""
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.")
return False
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.")
f.seek(0, os.SEEK_END)
fsize = f.tell()
f.seek(0)
self.log("File size is " + str(fsize) + " bytes.")
self.log("Treating files as type " + file_type)
self.importer = HhDataImportProcess(self.contract_id, 0,
TextIOWrapper(f, "utf8"),
fpath + file_type, fsize)
self.importer.run()
for message in self.importer.messages:
self.log(message)
if len(self.importer.messages) > 0:
raise BadRequest("Problem loading file.")
contract = Contract.get_dc_by_id(sess, self.contract_id)
contract.update_state(state)
sess.commit()
self.log("Finished loading '" + fpath)
return True
def content(contract_id, end_year, end_month, months, user):
caches = {}
sess = f = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"displaced.csv", user)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
titles = [
"Site Code",
"Site Name",
"Associated Site Ids",
"From",
"To",
"Gen Types",
"CHP kWh",
"LM kWh",
"Turbine kWh",
"PV kWh",
]
month_list = list(
c_months_u(finish_year=end_year,
finish_month=end_month,
months=months))
start_date, finish_date = month_list[0][0], month_list[-1][-1]
forecast_date = chellow.computer.forecast_date()
contract = Contract.get_supplier_by_id(sess, contract_id)
sites = (sess.query(Site).join(SiteEra).join(Era).join(Supply).join(
Source).filter(
or_(Era.finish_date == null(), Era.finish_date >= start_date),
Era.start_date <= finish_date,
or_(Source.code.in_(("gen", "gen-net")),
Era.exp_mpan_core != null()),
).distinct())
bill_titles = chellow.computer.contract_func(
caches, contract, "displaced_virtual_bill_titles")()
for title in bill_titles:
if title == "total-msp-kwh":
title = "total-displaced-msp-kwh"
titles.append(title)
writer.writerow(titles)
for site in sites:
for month_start, month_finish in month_list:
displaced_era = chellow.computer.displaced_era(
sess, caches, site, month_start, month_finish,
forecast_date)
if displaced_era is None:
continue
supplier_contract = displaced_era.imp_supplier_contract
if contract is not None and contract != supplier_contract:
continue
linked_sites = set()
generator_types = set()
for era in (sess.query(Era).join(SiteEra).filter(
SiteEra.site == site,
Era.start_date <= month_finish,
or_(Era.finish_date == null(),
Era.finish_date >= month_start),
)):
for site_era in era.site_eras:
if site_era.site != site:
linked_sites.add(site_era.site.code)
supply = era.supply
if supply.generator_type is not None:
generator_types.add(supply.generator_type.code)
supply_ids = set()
for era in (sess.query(Era).join(SiteEra).filter(
SiteEra.site == site,
SiteEra.is_physical,
Era.start_date <= month_finish,
or_(Era.finish_date == null(),
Era.finish_date >= month_start),
)):
supply_ids.add(era.supply.id)
vals = [
site.code,
site.name,
", ".join(list(linked_sites)),
hh_format(month_start),
hh_format(month_finish),
", ".join(list(generator_types)),
]
total_gen_breakdown = {}
results = iter(
sess.execute(
"select supply.id, hh_datum.value, "
"hh_datum.start_date, channel.imp_related, "
"source.code, generator_type.code as "
"gen_type_code from hh_datum, channel, source, "
"era, supply left outer join generator_type on "
"supply.generator_type_id = generator_type.id "
"where hh_datum.channel_id = channel.id and "
"channel.era_id = era.id and era.supply_id = "
"supply.id and supply.source_id = source.id and "
"channel.channel_type = 'ACTIVE' and not "
"(source.code = 'net' and channel.imp_related "
"is true) and hh_datum.start_date >= "
":month_start and hh_datum.start_date "
"<= :month_finish and "
"supply.id = any(:supply_ids) order "
"by hh_datum.start_date, supply.id",
params={
"month_start": month_start,
"month_finish": month_finish,
"supply_ids": sorted(list(supply_ids)),
},
))
(
sup_id,
hh_val,
hh_start,
imp_related,
source_code,
gen_type_code,
) = next(results, (None, None, None, None, None, None))
for hh_date in hh_range(caches, month_start, month_finish):
gen_breakdown = {}
exported = 0
while hh_start == hh_date:
if not imp_related and source_code in ("net",
"gen-net"):
exported += hh_val
if (imp_related and source_code == "gen") or (
not imp_related and source_code == "gen-net"):
gen_breakdown[gen_type_code] = (
gen_breakdown.setdefault(gen_type_code, 0) +
hh_val)
if (not imp_related and source_code == "gen") or (
imp_related and source_code == "gen-net"):
gen_breakdown[gen_type_code] = (
gen_breakdown.setdefault(gen_type_code, 0) -
hh_val)
(
sup_id,
hh_val,
hh_start,
imp_related,
source_code,
gen_type_code,
) = next(results, (None, None, None, None, None, None))
displaced = sum(gen_breakdown.values()) - exported
added_so_far = 0
for key in sorted(gen_breakdown.keys()):
kwh = gen_breakdown[key]
if displaced < 0:
total_gen_breakdown[key] = (
total_gen_breakdown.get(key, 0) + kwh)
else:
if kwh + added_so_far > displaced:
total_gen_breakdown[key] = (
total_gen_breakdown.get(key, 0) +
displaced - added_so_far)
break
else:
total_gen_breakdown[key] = (
total_gen_breakdown.get(key, 0) + kwh)
added_so_far += kwh
for title in ["chp", "lm", "turb", "pv"]:
vals.append(str(total_gen_breakdown.get(title, "")))
site_ds = chellow.computer.SiteSource(
sess,
site,
month_start,
month_finish,
forecast_date,
caches,
displaced_era,
)
disp_func = chellow.computer.contract_func(
caches, supplier_contract, "displaced_virtual_bill")
disp_func(site_ds)
bill = site_ds.supplier_bill
for title in bill_titles:
if title in bill:
vals.append(to_val(bill[title]))
del bill[title]
else:
vals.append("")
for k in sorted(bill.keys()):
vals.append(k)
vals.append(str(bill[k]))
writer.writerow(vals)
except BaseException:
msg = traceback.format_exc()
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 test_general_import_era_insert(sess):
site = Site.insert(sess, "CI017", "Water Works")
market_role_Z = MarketRole.insert(sess, "Z", "Non-core")
participant = Participant.insert(sess, "CALB", "AK Industries")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(sess, market_role_M, "Fusion Mop Ltd",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_C, "Fusion DC",
utc_datetime(2000, 1, 1), None, None)
mop_contract = Contract.insert_mop(sess, "Fusion", participant, "", {},
utc_datetime(2000, 1, 1), None, {})
dc_contract = Contract.insert_dc(sess, "Fusion DC 2000", participant, "",
{}, utc_datetime(2000, 1, 1), None, {})
pc = Pc.insert(sess, "00", "hh", utc_datetime(2000, 1, 1), None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
insert_comms(sess)
comm = Comm.get_by_code(sess, "GSM")
exp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dno = participant.insert_party(sess, market_role_R, "WPD",
utc_datetime(2000, 1, 1), None, "22")
meter_type = MeterType.insert(sess, "C5", "COP 1-5",
utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(sess, "CR", "Credit",
utc_datetime(1996, 1, 1),
None)
Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
dno.insert_llfc(
sess,
"521",
"Export (HV)",
voltage_level,
False,
False,
utc_datetime(1996, 1, 1),
None,
)
insert_sources(sess)
source = Source.get_by_code(sess, "net")
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "E")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2000, 1, 1),
None,
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
comm,
None,
energisation_status,
{},
None,
None,
None,
None,
None,
"22 7867 6232 781",
"521",
exp_supplier_contract,
"7748",
361,
)
sess.commit()
action = "insert"
vals = [
"22 7867 6232 781",
"2020-10-01 00:00",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
"Fusion Supplier 2000",
"{no change}",
"{no change}",
"{no change}",
"{no change}",
]
args = []
chellow.general_import.general_import_era(sess, action, vals, args)
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 content(contract_id, end_year, end_month, months, user):
caches = {}
sess = f = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'displaced.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
titles = [
'Site Code', 'Site Name', 'Associated Site Ids', 'From', 'To',
'Gen Types', 'CHP kWh', 'LM kWh', 'Turbine kWh', 'PV kWh'
]
finish_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) + \
relativedelta(months=1) - HH
start_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) - \
relativedelta(months=months-1)
forecast_date = chellow.computer.forecast_date()
contract = Contract.get_supplier_by_id(sess, contract_id)
sites = sess.query(Site).join(SiteEra).join(Era).join(Supply). \
join(Source).filter(
or_(Era.finish_date == null(), Era.finish_date >= start_date),
Era.start_date <= finish_date,
or_(
Source.code.in_(('gen', 'gen-net')),
Era.exp_mpan_core != null())).distinct()
bill_titles = chellow.computer.contract_func(
caches, contract, 'displaced_virtual_bill_titles')()
for title in bill_titles:
if title == 'total-msp-kwh':
title = 'total-displaced-msp-kwh'
titles.append(title)
writer.writerow(titles)
for site in sites:
month_start = start_date
month_finish = month_start + relativedelta(months=1) - HH
for i in range(months):
displaced_era = chellow.computer.displaced_era(
sess, caches, site, month_start, month_finish,
forecast_date)
if displaced_era is None:
continue
supplier_contract = displaced_era.imp_supplier_contract
if contract is not None and contract != supplier_contract:
continue
linked_sites = set()
generator_types = set()
for era in sess.query(Era).join(SiteEra).filter(
SiteEra.site == site, Era.start_date <= month_finish,
or_(Era.finish_date == null(),
Era.finish_date >= month_start)):
for site_era in era.site_eras:
if site_era.site != site:
linked_sites.add(site_era.site.code)
supply = era.supply
if supply.generator_type is not None:
generator_types.add(supply.generator_type.code)
supply_ids = set()
for era in sess.query(Era).join(SiteEra).filter(
SiteEra.site == site, SiteEra.is_physical,
Era.start_date <= month_finish,
or_(Era.finish_date == null(),
Era.finish_date >= month_start)):
supply_ids.add(era.supply.id)
vals = [
site.code, site.name, ', '.join(list(linked_sites)),
hh_format(month_start),
hh_format(month_finish), ', '.join(list(generator_types))
]
total_gen_breakdown = {}
results = iter(
sess.execute(
"select supply.id, hh_datum.value, "
"hh_datum.start_date, channel.imp_related, "
"source.code, generator_type.code as "
"gen_type_code from hh_datum, channel, source, "
"era, supply left outer join generator_type on "
"supply.generator_type_id = generator_type.id "
"where hh_datum.channel_id = channel.id and "
"channel.era_id = era.id and era.supply_id = "
"supply.id and supply.source_id = source.id and "
"channel.channel_type = 'ACTIVE' and not "
"(source.code = 'net' and channel.imp_related "
"is true) and hh_datum.start_date >= "
":month_start and hh_datum.start_date "
"<= :month_finish and "
"supply.id = any(:supply_ids) order "
"by hh_datum.start_date, supply.id",
params={
'month_start': month_start,
'month_finish': month_finish,
'supply_ids': sorted(list(supply_ids))
}))
(sup_id, hh_val, hh_start, imp_related, source_code,
gen_type_code) = next(results,
(None, None, None, None, None, None))
for hh_date in hh_range(caches, month_start, month_finish):
gen_breakdown = {}
exported = 0
while hh_start == hh_date:
if not imp_related and source_code in ('net',
'gen-net'):
exported += hh_val
if (imp_related and source_code == 'gen') or \
(not imp_related and
source_code == 'gen-net'):
gen_breakdown[gen_type_code] = \
gen_breakdown.setdefault(
gen_type_code, 0) + hh_val
if (not imp_related and source_code == 'gen') or (
imp_related and source_code == 'gen-net'):
gen_breakdown[gen_type_code] = \
gen_breakdown.setdefault(
gen_type_code, 0) - hh_val
(sup_id, hh_val, hh_start, imp_related,
source_code, gen_type_code) = next(
results, (None, None, None, None, None, None))
displaced = sum(gen_breakdown.values()) - exported
added_so_far = 0
for key in sorted(gen_breakdown.keys()):
kwh = gen_breakdown[key]
if kwh + added_so_far > displaced:
total_gen_breakdown[key] = \
total_gen_breakdown.get(key, 0) + \
displaced - added_so_far
break
else:
total_gen_breakdown[key] = \
total_gen_breakdown.get(key, 0) + kwh
added_so_far += kwh
for title in ['chp', 'lm', 'turb', 'pv']:
vals.append(str(total_gen_breakdown.get(title, '')))
site_ds = chellow.computer.SiteSource(sess, site, month_start,
month_finish,
forecast_date, caches,
displaced_era)
disp_func = chellow.computer.contract_func(
caches, supplier_contract, 'displaced_virtual_bill')
disp_func(site_ds)
bill = site_ds.supplier_bill
for title in bill_titles:
if title in bill:
vals.append(to_val(bill[title]))
del bill[title]
else:
vals.append('')
for k in sorted(bill.keys()):
vals.append(k)
vals.append(str(bill[k]))
writer.writerow(vals)
month_start += relativedelta(months=1)
month_finish = month_start + relativedelta(months=1) - HH
except BaseException:
msg = traceback.format_exc()
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):
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 test_process(mocker, sess):
site = Site.insert(sess, "CI017", "Water Works")
market_role_Z = MarketRole.insert(sess, "Z", "Non-core")
participant = Participant.insert(sess, "CALB", "AK Industries")
participant.insert_party(
sess, market_role_Z, "None core", utc_datetime(2000, 1, 1), None, None
)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(
sess, market_role_M, "Fusion Mop Ltd", utc_datetime(2000, 1, 1), None, None
)
participant.insert_party(
sess, market_role_X, "Fusion Ltc", utc_datetime(2000, 1, 1), None, None
)
participant.insert_party(
sess, market_role_C, "Fusion DC", utc_datetime(2000, 1, 1), None, None
)
mop_contract = Contract.insert_mop(
sess, "Fusion", participant, "", {}, utc_datetime(2000, 1, 1), None, {}
)
dc_contract = Contract.insert_dc(
sess, "Fusion DC 2000", participant, "", {}, utc_datetime(2000, 1, 1), None, {}
)
pc = Pc.insert(sess, "00", "hh", utc_datetime(2000, 1, 1), None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
insert_comms(sess)
comm = Comm.get_by_code(sess, "GSM")
imp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dno = participant.insert_party(
sess, market_role_R, "WPD", utc_datetime(2000, 1, 1), None, "22"
)
meter_type = MeterType.insert(sess, "C5", "COP 1-5", utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(
sess, "CR", "Credit", utc_datetime(1996, 1, 1), None
)
Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
dno.insert_llfc(
sess,
"521",
"Export (HV)",
voltage_level,
False,
False,
utc_datetime(1996, 1, 1),
None,
)
insert_sources(sess)
source = Source.get_by_code(sess, "net")
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "E")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2000, 1, 1),
None,
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
comm,
None,
energisation_status,
{},
"22 7867 6232 781",
"510",
imp_supplier_contract,
"7748",
361,
None,
None,
None,
None,
None,
)
site.insert_e_supply(
sess,
source,
None,
"Dave",
utc_datetime(2000, 1, 1),
None,
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
comm,
None,
energisation_status,
{},
"22 7868 6232 789",
"510",
imp_supplier_contract,
"7748",
361,
None,
None,
None,
None,
None,
)
report_run = ReportRun.insert(
sess,
"ecoes_comparison",
None,
"ecoes_comparison",
{},
)
sess.commit()
f = StringIO()
ecoes_lines = [
"titles",
",".join(
(
"2278676232781",
"address-line-1",
"address-line-2",
"address-line-3",
"address-line-4",
"address-line-5",
"address-line-6",
"address-line-7",
"address-line-8",
"address-line-9",
"post-code",
"supplier",
"registration-from",
"mtc",
"mtc-date",
"llfc",
"llfc-from",
"pc",
"",
"measurement-class",
"energisation-status",
"da",
"dc",
"mop",
"mop-appoint-date",
"gsp-group",
"gsp-effective-from",
"dno",
"msn",
"meter-install-date",
"meter-type",
"map-id",
),
),
",".join(
(
"2278686232789",
"address-line-1",
"address-line-2",
"address-line-3",
"address-line-4",
"address-line-5",
"address-line-6",
"address-line-7",
"address-line-8",
"address-line-9",
"post-code",
"supplier",
"registration-from",
"mtc",
"mtc-date",
"llfc",
"llfc-from",
"pc",
"",
"measurement-class",
"energisation-status",
"da",
"dc",
"mop",
"mop-appoint-date",
"gsp-group",
"gsp-effective-from",
"dno",
"msn",
"meter-install-date",
"meter-type",
"map-id",
),
),
]
exclude_mpan_cores = []
ignore_mpan_cores_msn = []
show_ignored = True
_process(
sess,
ecoes_lines,
exclude_mpan_cores,
ignore_mpan_cores_msn,
f,
show_ignored,
report_run,
)
expected = [
[
"mpan_core",
"mpan_core_no_spaces",
"ecoes_pc",
"chellow_pc",
"ecoes_mtc",
"chellow_mtc",
"ecoes_llfc",
"chellow_llfc",
"ecoes_ssc",
"chellow_ssc",
"ecoes_es",
"chellow_es",
"ecoes_supplier",
"chellow_supplier",
"chellow_supplier_contract_name",
"ecoes_dc",
"chellow_dc",
"ecoes_mop",
"chellow_mop",
"ecoes_gsp_group",
"chellow_gsp_group",
"ecoes_msn",
"chellow_msn",
"ecoes_msn_install_date",
"ecoes_meter_type",
"chellow_meter_type",
"ignored",
"problem",
],
[
"22 7867 6232 781",
"2278676232781",
"pc",
"00",
"mtc",
"845",
"llfc",
"510",
"",
"",
"energisation-status",
"E",
"supplier",
"CALB",
"Fusion Supplier 2000",
"dc",
"CALB",
"mop",
"CALB",
"gsp-group",
"_L",
"msn",
"hgjeyhuw",
"meter-install-date",
"meter-type",
"H",
"False",
"The energisation statuses don't match. Can't parse the PC. Can't parse "
"the MTC. The LLFCs don't match. The supplier codes don't match. The DC "
"codes don't match. The MOP codes don't match. The GSP group codes don't "
"match. The meter serial numbers don't match. The meter types don't match. "
"See https://dtc.mrasco.com/DataItem.aspx?ItemCounter=0483 ",
],
[
"22 7868 6232 789",
"2278686232789",
"pc",
"00",
"mtc",
"845",
"llfc",
"510",
"",
"",
"energisation-status",
"E",
"supplier",
"CALB",
"Fusion Supplier 2000",
"dc",
"CALB",
"mop",
"CALB",
"gsp-group",
"_L",
"msn",
"hgjeyhuw",
"meter-install-date",
"meter-type",
"H",
"False",
"The energisation statuses don't match. Can't parse the PC. Can't parse "
"the MTC. The LLFCs don't match. The supplier codes don't match. The DC "
"codes don't match. The MOP codes don't match. The GSP group codes don't "
"match. The meter serial numbers don't match. The meter types don't match. "
"See https://dtc.mrasco.com/DataItem.aspx?ItemCounter=0483 ",
],
]
assert f.getvalue() == "\n".join(",".join(line) for line in expected) + "\n"
def check_permissions(*args, **kwargs):
g.user = None
config_contract = Contract.get_non_core_by_name(
g.sess, "configuration")
props = config_contract.make_properties()
ad_props = props.get("ad_authentication", {})
ad_auth_on = ad_props.get("on", False)
if ad_auth_on:
username = request.headers["X-Isrw-Proxy-Logon-User"].upper()
user = g.sess.query(User).filter(
User.email_address == username).first()
if user is None:
try:
username = ad_props["default_user"]
user = (g.sess.query(User).filter(
User.email_address == username).first())
except KeyError:
user = None
if user is not None:
g.user = user
else:
auth = request.authorization
if auth is None:
try:
ips = props["ips"]
if request.remote_addr in ips:
key = request.remote_addr
elif "*.*.*.*" in ips:
key = "*.*.*.*"
else:
key = None
email = ips[key]
g.user = (g.sess.query(User).filter(
User.email_address == email).first())
except KeyError:
pass
else:
user = (g.sess.query(User).filter(
User.email_address == auth.username).first())
if user is not None and user.password_matches(auth.password):
g.user = user
# Got our user
path = request.path
method = request.method
if path in (
"/health",
"/nationalgrid/sf_bsuos.xls",
"/nationalgrid/cv.csv",
"/elexonportal/file/download/BESTVIEWPRICES_FILE",
"/ecoes",
"/elexonportal/file/download/TLM_FILE",
"/elexonportal/file/download/RCRC_FILE",
"/ecoes/NonDomesticCustomer/ExportPortfolioMPANs",
"/hh_api",
):
return
if g.user is not None:
if "X-Isrw-Proxy-Logon-User" in request.headers:
g.user.proxy_username = request.headers[
"X-Isrw-Proxy-Logon-User"].upper()
role = g.user.user_role
role_code = role.code
if (role_code == "viewer"
and (method in ("GET", "HEAD")
or path in ("/reports/169", "/reports/187",
"/reports/247", "/reports/111"))
and path not in ("/system", )):
return
elif role_code == "editor":
return
elif role_code == "party-viewer":
if method in ("GET", "HEAD"):
party = g.user.party
market_role_code = party.market_role.code
if market_role_code in ("C", "D"):
dc_contract_id = request.args["dc_contract_id"]
dc_contract = Contract.get_dc_by_id(
g.sess, dc_contract_id)
if dc_contract.party == party and request.full_path.startswith(
"/channel_snags?"):
return
elif market_role_code == "X":
if path.startswith("/supplier_contracts/" + party.id):
return
if g.user is None and g.sess.query(User).count() == 0:
g.sess.rollback()
user_role = g.sess.query(UserRole).filter(
UserRole.code == "editor").one()
User.insert(g.sess, "*****@*****.**", "admin", user_role, None)
g.sess.commit()
return
if g.user is None or (not ad_auth_on and auth is None):
return Response(
"Could not verify your access level for that URL.\n"
"You have to login with proper credentials",
401,
{"WWW-Authenticate": 'Basic realm="Chellow"'},
)
config = Contract.get_non_core_by_name(g.sess, "configuration")
return make_response(
render_template("403.html", properties=config.make_properties()),
403)
import chellow.scenario
from chellow.models import Session, Contract
sess = None
try:
sess = Session()
db_id = Contract.get_non_core_by_name(sess, 'aahedc').id
finally:
if sess is not None:
sess.close()
create_future_func = chellow.scenario.make_create_future_func_simple(
'aahedc', ['aahedc_gbp_per_gsp_kwh'])
def hh(supply_source):
bill = supply_source.supplier_bill
rate_set = supply_source.supplier_rate_sets['aahedc-rate']
try:
supply_source.caches['aahedc']
except KeyError:
supply_source.caches['aahedc'] = {}
try:
future_funcs = supply_source.caches['future_funcs']
except KeyError:
future_funcs = {}
supply_source.caches['future_funcs'] = future_funcs
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 test_Supply_insert_era_at(sess):
"""Where an era is inserted in the last HH of another era, check
the template era is the one at the insertion date.
"""
site = Site.insert(sess, "CI017", "Water Works")
market_role_Z = MarketRole.insert(sess, "Z", "Non-core")
participant = Participant.insert(sess, "CALB", "AK Industries")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(sess, market_role_M, "Fusion Mop Ltd",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_C, "Fusion DC",
utc_datetime(2000, 1, 1), None, None)
mop_contract = Contract.insert_mop(sess, "Fusion", participant, "", {},
utc_datetime(2000, 1, 1), None, {})
dc_contract = Contract.insert_hhdc(sess, "Fusion DC 2000", participant, "",
{}, utc_datetime(2000, 1, 1), None, {})
pc = Pc.insert(sess, "00", "hh", utc_datetime(2000, 1, 1), None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
imp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dno = participant.insert_party(sess, market_role_R, "WPD",
utc_datetime(2000, 1, 1), None, "22")
meter_type = MeterType.insert(sess, "C5", "COP 1-5",
utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(sess, "CR", "Credit",
utc_datetime(1996, 1, 1),
None)
mtc_845 = Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
dno.insert_llfc(
sess,
"521",
"Export (HV)",
voltage_level,
False,
False,
utc_datetime(1996, 1, 1),
None,
)
insert_sources(sess)
source = Source.get_by_code(sess, "net")
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "E")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
era1_msn = "e1msn"
imp_mpan_core = "22 7867 6232 781"
supply = site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2000, 1, 1),
None,
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
era1_msn,
pc,
"845",
cop,
None,
energisation_status,
{},
imp_mpan_core,
"510",
imp_supplier_contract,
"7748",
361,
None,
None,
None,
None,
None,
)
era1 = supply.eras[0]
era2_start_date = utc_datetime(2009, 7, 31, 23, 30)
era2 = supply.insert_era_at(sess, era2_start_date)
era2_msn = "e2msn"
era2.update(
sess,
era2_start_date,
None,
mop_contract,
"379540",
dc_contract,
"547yt",
era2_msn,
pc,
mtc_845,
cop,
None,
energisation_status,
{},
imp_mpan_core,
"510",
imp_supplier_contract,
"9745y6",
361,
None,
None,
None,
None,
None,
)
sess.commit()
start_date = utc_datetime(2009, 7, 31, 23, 00)
era3 = supply.insert_era_at(sess, start_date)
assert era3.msn == era1.msn
from datetime import datetime as Datetime
import pytz
from chellow.models import Session, Contract
import chellow.computer
import chellow.scenario
from chellow.utils import HH
sess = None
try:
sess = Session()
ccl_contract_id = Contract.get_non_core_by_name(sess, 'ccl').id
finally:
if sess is not None:
sess.close()
create_future_func = chellow.scenario.make_create_future_func_simple(
'ccl', ['ccl_rate'])
def ccl(data_source):
rate_set = data_source.supplier_rate_sets['ccl-rate']
if data_source.supply.find_era_at(
data_source.sess, data_source.finish_date + HH) is None:
sup_end = data_source.finish_date
else:
sup_end = None
try:
cache = data_source.caches['ccl']
def test_bill_after_end_supply(mocker, sess):
site = Site.insert(sess, "CI017", "Water Works")
start_date = utc_datetime(2009, 7, 31, 23, 00)
months = 1
supply_id = None
market_role_Z = MarketRole.insert(sess, "Z", "Non-core")
participant = Participant.insert(sess, "CALB", "AK Industries")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(sess, market_role_M, "Fusion Mop Ltd",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_C, "Fusion DC",
utc_datetime(2000, 1, 1), None, None)
mop_charge_script = """
from chellow.utils import reduce_bill_hhs
def virtual_bill_titles():
return ['net-gbp', 'problem']
def virtual_bill(ds):
for hh in ds.hh_data:
hh_start = hh['start-date']
bill_hh = ds.supplier_bill_hhs[hh_start]
bill_hh['net-gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
ds.mop_bill = reduce_bill_hhs(ds.supplier_bill_hhs)
"""
mop_contract = Contract.insert_mop(
sess,
"Fusion Mop Contract",
participant,
mop_charge_script,
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dc_charge_script = """
from chellow.utils import reduce_bill_hhs
def virtual_bill_titles():
return ['net-gbp', 'problem']
def virtual_bill(ds):
for hh in ds.hh_data:
hh_start = hh['start-date']
bill_hh = ds.supplier_bill_hhs[hh_start]
bill_hh['net-gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
ds.dc_bill = reduce_bill_hhs(ds.supplier_bill_hhs)
"""
dc_contract = Contract.insert_dc(
sess,
"Fusion DC 2000",
participant,
dc_charge_script,
{},
utc_datetime(2000, 1, 1),
None,
{},
)
pc = Pc.insert(sess, "00", "hh", utc_datetime(2000, 1, 1), None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
insert_comms(sess)
comm = Comm.get_by_code(sess, "GSM")
supplier_charge_script = """
import chellow.ccl
from chellow.utils import HH, reduce_bill_hhs, utc_datetime
def virtual_bill_titles():
return [
'ccl-kwh', 'ccl-rate', 'ccl-gbp', 'net-gbp', 'vat-gbp', 'gross-gbp',
'sum-msp-kwh', 'sum-msp-gbp', 'problem']
def virtual_bill(ds):
for hh in ds.hh_data:
hh_start = hh['start-date']
bill_hh = ds.supplier_bill_hhs[hh_start]
bill_hh['sum-msp-kwh'] = hh['msp-kwh']
bill_hh['sum-msp-gbp'] = hh['msp-kwh'] * 0.1
bill_hh['net-gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
bill_hh['vat-gbp'] = 0
bill_hh['gross-gbp'] = bill_hh['net-gbp'] + bill_hh['vat-gbp']
ds.supplier_bill = reduce_bill_hhs(ds.supplier_bill_hhs)
"""
imp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
supplier_charge_script,
{},
utc_datetime(2000, 1, 1),
None,
{},
)
batch = imp_supplier_contract.insert_batch(sess, "a b", "")
dno = participant.insert_party(sess, market_role_R, "WPD",
utc_datetime(2000, 1, 1), None, "22")
meter_type = MeterType.insert(sess, "C5", "COP 1-5",
utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(sess, "CR", "Credit",
utc_datetime(1996, 1, 1),
None)
Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
dno.insert_llfc(
sess,
"521",
"Export (HV)",
voltage_level,
False,
False,
utc_datetime(1996, 1, 1),
None,
)
insert_sources(sess)
source = Source.get_by_code(sess, "net")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "E")
supply = site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2000, 1, 1),
utc_datetime(2000, 1, 31, 23, 30),
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
comm,
None,
energisation_status,
{},
"22 7867 6232 781",
"510",
imp_supplier_contract,
"7748",
361,
None,
None,
None,
None,
None,
)
insert_bill_types(sess)
bill_type = sess.execute(
select(BillType).where(BillType.code == "N")).scalar_one()
batch.insert_bill(
sess,
"dd",
"hjk",
start_date,
utc_datetime(2009, 7, 10),
utc_datetime(2009, 7, 10),
Decimal("10.00"),
Decimal("10.00"),
Decimal("10.00"),
Decimal("10.00"),
bill_type,
{},
supply,
)
sess.commit()
scenario_props = {
"scenario_start_year": start_date.year,
"scenario_start_month": start_date.month,
"scenario_duration": months,
"by_hh": False,
}
base_name = ["monthly_duration"]
site_id = site.id
user = mocker.Mock()
compression = False
site_codes = []
now = utc_datetime(2020, 1, 1)
mock_file = BytesIO()
mock_file.close = mocker.Mock()
mocker.patch("chellow.reports.report_247.open", return_value=mock_file)
mocker.patch("chellow.reports.report_247.chellow.dloads.make_names",
return_value=("a", "b"))
mocker.patch("chellow.reports.report_247.os.rename")
content(
scenario_props,
base_name,
site_id,
supply_id,
user,
compression,
site_codes,
now,
)
sheet = odio.parse_spreadsheet(mock_file)
table = list(sheet.tables[0].rows)
expected = [
[
"creation-date",
"site-id",
"site-name",
"associated-site-ids",
"month",
"metering-type",
"sources",
"generator-types",
"import-net-kwh",
"export-net-kwh",
"import-gen-kwh",
"export-gen-kwh",
"import-3rd-party-kwh",
"export-3rd-party-kwh",
"displaced-kwh",
"used-kwh",
"used-3rd-party-kwh",
"import-net-gbp",
"export-net-gbp",
"import-gen-gbp",
"export-gen-gbp",
"import-3rd-party-gbp",
"export-3rd-party-gbp",
"displaced-gbp",
"used-gbp",
"used-3rd-party-gbp",
"billed-import-net-kwh",
"billed-import-net-gbp",
"billed-supplier-import-net-gbp",
"billed-dc-import-net-gbp",
"billed-mop-import-net-gbp",
],
[
Datetime(2020, 1, 1, 0, 0),
"CI017",
"Water Works",
"",
Datetime(2009, 7, 31, 23, 30),
None,
"",
"",
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
10.0,
10.0,
0.0,
0.0,
],
]
assert expected == table
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 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 test_supply(mocker, sess, client):
site = Site.insert(sess, "22488", "Water Works")
g_dn = GDn.insert(sess, "EE", "East of England")
g_ldz = g_dn.insert_g_ldz(sess, "EA")
g_exit_zone = g_ldz.insert_g_exit_zone(sess, "EA1")
insert_g_units(sess)
g_unit_M3 = GUnit.get_by_code(sess, "M3")
participant = Participant.insert(sess, "CALB", "AK Industries")
market_role_Z = MarketRole.get_by_code(sess, "Z")
participant.insert_party(
sess, market_role_Z, "None core", utc_datetime(2000, 1, 1), None, None
)
g_cv_rate_script = {
"cvs": {
"EA": {
1: {"applicable_at": utc_datetime(2020, 10, 3), "cv": 39.2000},
}
}
}
Contract.insert_non_core(
sess, "g_cv", "", {}, utc_datetime(2000, 1, 1), None, g_cv_rate_script
)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
charge_script = """
import chellow.g_ccl
from chellow.g_engine import g_rates
from chellow.utils import reduce_bill_hhs
def virtual_bill_titles():
return [
'units_consumed', 'correction_factor', 'unit_code', 'unit_factor',
'calorific_value', 'kwh', 'gas_rate', 'gas_gbp', 'ccl_rate',
'standing_rate', 'standing_gbp', 'net_gbp', 'vat_gbp', 'gross_gbp',
'problem']
def virtual_bill(ds):
chellow.g_ccl.vb(ds)
for hh in ds.hh_data:
start_date = hh['start_date']
bill_hh = ds.bill_hhs[start_date]
bill_hh['units_consumed'] = hh['units_consumed']
bill_hh['correction_factor'] = {hh['correction_factor']}
bill_hh['unit_code'] = {hh['unit_code']}
bill_hh['unit_factor'] = {hh['unit_factor']}
bill_hh['calorific_value'] = {hh['calorific_value']}
kwh = hh['kwh']
bill_hh['kwh'] = kwh
gas_rate = float(
g_rates(ds.sess, ds.caches, db_id, start_date)['gas_rate'])
bill_hh['gas_rate'] = {gas_rate}
bill_hh['gas_gbp'] = gas_rate * kwh
bill_hh['ccl_kwh'] = kwh
ccl_rate = hh['ccl']
bill_hh['ccl_rate'] = {ccl_rate}
bill_hh['ccl_kwh'] = kwh
bill_hh['ccl_gbp'] = kwh * ccl_rate
if hh['utc_is_month_end']:
standing_rate = float(
g_rates(
ds.sess, ds.caches, db_id, start_date)['standing_rate'])
bill_hh['standing_rate'] = {standing_rate}
bill_hh['standing_gbp'] = standing_rate
if hh['utc_decimal_hour'] == 0:
pass
bill_hh['net_gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
bill_hh['vat_gbp'] = 0
bill_hh['gross_gbp'] = bill_hh['net_gbp'] + bill_hh['vat_gbp']
ds.bill = reduce_bill_hhs(ds.bill_hhs)
"""
g_contract_rate_script = {
"gas_rate": 0.1,
"standing_rate": 0.1,
}
g_contract = GContract.insert(
sess,
"Fusion 2020",
charge_script,
{},
utc_datetime(2000, 1, 1),
None,
g_contract_rate_script,
)
insert_g_reading_frequencies(sess)
g_reading_frequency_M = GReadingFrequency.get_by_code(sess, "M")
msn = "hgeu8rhg"
g_supply = site.insert_g_supply(
sess,
"87614362",
"main",
g_exit_zone,
utc_datetime(2010, 1, 1),
None,
msn,
1,
g_unit_M3,
g_contract,
"d7gthekrg",
g_reading_frequency_M,
)
g_batch = g_contract.insert_g_batch(sess, "b1", "Jan batch")
breakdown = {"units_consumed": 771}
insert_bill_types(sess)
bill_type_N = BillType.get_by_code(sess, "N")
insert_g_read_types(sess)
g_read_type_A = GReadType.get_by_code(sess, "A")
g_bill = g_batch.insert_g_bill(
sess,
g_supply,
bill_type_N,
"55h883",
"dhgh883",
utc_datetime(2019, 4, 3),
utc_datetime(2015, 9, 1),
utc_datetime(2015, 9, 30, 22, 30),
Decimal("45"),
Decimal("12.40"),
Decimal("1.20"),
Decimal("14.52"),
"",
breakdown,
)
g_bill.insert_g_read(
sess,
msn,
g_unit_M3,
Decimal("1"),
Decimal("37"),
Decimal("90"),
utc_datetime(2015, 9, 1),
g_read_type_A,
Decimal("890"),
utc_datetime(2015, 9, 25),
g_read_type_A,
)
sess.commit()
mock_file = StringIO()
mock_file.close = mocker.Mock()
mocker.patch(
"chellow.reports.report_g_virtual_bills_hh.open", return_value=mock_file
)
mocker.patch(
"chellow.reports.report_g_virtual_bills_hh.chellow.dloads.make_names",
return_value=("a", "b"),
)
mocker.patch("chellow.reports.report_g_virtual_bills.os.rename")
user = mocker.Mock()
g_supply_id = g_supply.id
start_date = to_utc(ct_datetime(2018, 2, 1))
finish_date = to_utc(ct_datetime(2018, 2, 1, 0, 30))
chellow.reports.report_g_virtual_bills_hh.content(
g_supply_id, start_date, finish_date, user
)
mock_file.seek(0)
table = list(csv.reader(mock_file))
expected = [
[
"MPRN",
"Site Code",
"Site Name",
"Account",
"HH Start",
"",
"",
"units_consumed",
"correction_factor",
"unit_code",
"unit_factor",
"calorific_value",
"kwh",
"gas_rate",
"gas_gbp",
"ccl_rate",
"standing_rate",
"standing_gbp",
"net_gbp",
"vat_gbp",
"gross_gbp",
"problem",
],
[
"87614362",
"22488",
"Water Works",
"d7gthekrg",
"2018-02-01 00:00",
"",
"",
"0.6944444444444444",
"1.0",
"M3",
"1.0",
"39.2",
"7.561728395061729",
"0.1",
"0.7561728395061729",
"0.00198",
"",
"",
"0.7711450617283951",
"0",
"0.7711450617283951",
"",
"ccl_gbp",
"0.014972222222222222",
"ccl_kwh",
"7.561728395061729",
],
[
"87614362",
"22488",
"Water Works",
"d7gthekrg",
"2018-02-01 00:30",
"",
"",
"0.6944444444444444",
"1.0",
"M3",
"1.0",
"39.2",
"7.561728395061729",
"0.1",
"0.7561728395061729",
"0.00198",
"",
"",
"0.7711450617283951",
"0",
"0.7711450617283951",
"",
"ccl_gbp",
"0.014972222222222222",
"ccl_kwh",
"7.561728395061729",
],
]
match_tables(table, expected)
def content(start_date, finish_date, contract_id, user):
caches = {}
sess = supply_source = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"mop_virtual_bills.csv", user)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
contract = Contract.get_mop_by_id(sess, contract_id)
forecast_date = chellow.computer.forecast_date()
header_titles = [
"Import MPAN Core",
"Export MPAN Core",
"Start Date",
"Finish Date",
]
bill_titles = chellow.computer.contract_func(caches, contract,
"virtual_bill_titles")()
writer.writerow(header_titles + bill_titles)
vb_func = chellow.computer.contract_func(caches, contract,
"virtual_bill")
for era in (sess.query(Era).filter(
or_(Era.finish_date == null(), Era.finish_date >= start_date),
Era.start_date <= finish_date,
Era.mop_contract == contract,
).order_by(Era.imp_mpan_core, Era.exp_mpan_core, Era.start_date)):
chunk_start = hh_max(era.start_date, start_date)
chunk_finish = hh_min(era.finish_date, finish_date)
import_mpan_core = era.imp_mpan_core
if import_mpan_core is None:
import_mpan_core_str = ""
else:
is_import = True
import_mpan_core_str = import_mpan_core
export_mpan_core = era.exp_mpan_core
if export_mpan_core is None:
export_mpan_core_str = ""
else:
is_import = False
export_mpan_core_str = export_mpan_core
out = [
import_mpan_core_str,
export_mpan_core_str,
hh_format(chunk_start),
hh_format(chunk_finish),
]
supply_source = chellow.computer.SupplySource(
sess, chunk_start, chunk_finish, forecast_date, era, is_import,
caches)
vb_func(supply_source)
bill = supply_source.mop_bill
for title in bill_titles:
if title in bill:
out.append(make_val(bill[title]))
del bill[title]
else:
out.append("")
for k in sorted(bill.keys()):
out.append(k)
out.append(str(bill[k]))
writer.writerow(out)
except BadRequest as e:
msg = "Problem "
if supply_source is not None:
msg += ("with supply " + supply_source.mpan_core +
" starting at " + hh_format(supply_source.start_date) +
" ")
msg += str(e)
sys.stderr.write(msg)
writer.writerow([msg])
except BaseException:
msg = traceback.format_exc()
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):
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_inner(self, sess):
self.log("Starting to check GCv rates.")
contract = Contract.get_non_core_by_name(sess, 'g_cv')
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 = utc_datetime_now()
props = contract.make_properties()
if props.get('enabled', False):
search_finish = next_month_start + relativedelta(days=1)
if now > search_finish:
url = props['url']
self.log(
"Checking to see if data is available from " +
hh_format(this_month_start) + " to " +
hh_format(search_finish) + " at " + url)
res = requests.post(
url, data={
'LatestValue': 'true',
'PublicationObjectIds':
'408:12265,+408:4636,+408:4637,+408:4639,'
'+408:4638,+408:4640,+408:4641,+408:4642,'
'+408:4643,+408:4644,+408:4645,+408:4646,'
'+408:4647,+408:4648,+408:12269,+408:12268,'
'+408:12270,+408:12266,+408:12267',
'Applicable': 'applicableFor',
'PublicationObjectCount': '19',
'FromUtcDatetime': param_format(this_month_start),
'ToUtcDateTime': param_format(search_finish),
'FileType': 'Csv'})
self.log("Received " + str(res.status_code) + " " + res.reason)
month_cv = defaultdict(dict)
cf = csv.reader(res.text.splitlines())
row = next(cf) # Skip title row
for row in cf:
applicable_for_str = row[1]
data_item = row[2]
value_str = row[3]
if 'LDZ' in data_item:
applicable_for = to_utc(
Datetime.strptime(applicable_for_str, "%d/%m/%Y"))
cvs = month_cv[applicable_for]
ldz = data_item[-3:-1]
cvs[ldz] = Decimal(value_str)
all_equal = len(set(map(len, month_cv.values()))) <= 1
last_date = max(month_cv.keys())
if last_date > next_month_start and all_equal:
for dt in tuple(month_cv.keys()):
if (dt.year, dt.month) != (
this_month_start.year, this_month_start.month):
del month_cv[dt]
self.log("The whole month's data is there.")
month_ion = [v for k, v in sorted(month_cv.items())]
script = dumps(month_ion)
contract = Contract.get_non_core_by_name(sess, 'g_cv')
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 " + hh_format(last_date) + ".")
else:
self.log(
"The automatic importer is disabled. To "
"enable it, edit the contract properties to "
"set 'enabled' to True.")
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 sftp_handler(self, sess, properties, contract):
host_name = properties["hostname"]
user_name = properties["username"]
password = properties["password"]
try:
port = properties["port"]
except KeyError:
port = None
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"] = {}
sess.rollback()
self.log("Connecting to sftp server at " + host_name + ":" +
str(port) + ".")
cnopts = pysftp.CnOpts()
cnopts.hostkeys = None
ftp = pysftp.Connection(host_name,
username=user_name,
password=password,
cnopts=cnopts)
ftp.timeout = 120
home_path = ftp.pwd
f = 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] = ""
dir_path = home_path + "/" + directory
ftp.cwd(dir_path)
files = []
for attr in ftp.listdir_attr():
fpath = dir_path + "/" + attr.filename
try:
ftp.cwd(fpath)
continue # directory
except paramiko.SFTPError:
pass
key = str(attr.st_mtime) + "_" + attr.filename
if key > last_import_key:
files.append((key, fpath))
if len(files) > 0:
f = sorted(files)[0]
last_import_keys[directory] = f[0]
break
if f is None:
self.log("No new files found.")
ftp.close()
self.log("Logged out.")
return False
else:
key, fpath = f
self.log("Attempting to download " + fpath + " with key " + key +
".")
f = tempfile.TemporaryFile()
ftp.getfo(fpath, f)
self.log("File downloaded successfully.")
ftp.close()
self.log("Logged out.")
f.seek(0, os.SEEK_END)
fsize = f.tell()
f.seek(0)
self.log("File size is " + str(fsize) + " bytes.")
self.log("Treating files as type " + file_type)
self.importer = HhDataImportProcess(self.contract_id, 0,
TextIOWrapper(f, "utf8"),
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 BadRequest("Problem loading file.")
contract = Contract.get_dc_by_id(sess, self.contract_id)
contract.update_state(state)
sess.commit()
self.log("Finished loading '" + fpath)
return True
def test_init_hh_data(sess, mocker):
"""New style channels"""
site = Site.insert(sess, "CI017", "Water Works")
market_role_Z = MarketRole.insert(sess, "Z", "Non-core")
participant = Participant.insert(sess, "CALB", "AK Industries")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(sess, market_role_M, "Fusion Mop Ltd",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_C, "Fusion DC",
utc_datetime(2000, 1, 1), None, None)
mop_contract = Contract.insert_mop(sess, "Fusion", participant, "", {},
utc_datetime(2000, 1, 1), None, {})
dc_contract = Contract.insert_hhdc(sess, "Fusion DC 2000", participant, "",
{}, utc_datetime(2000, 1, 1), None, {})
pc = Pc.insert(sess, "00", "hh", utc_datetime(2000, 1, 1), None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
imp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dno = participant.insert_party(sess, market_role_R, "WPD",
utc_datetime(2000, 1, 1), None, "22")
meter_type = MeterType.insert(sess, "C5", "COP 1-5",
utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(sess, "CR", "Credit",
utc_datetime(1996, 1, 1),
None)
Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
dno.insert_llfc(
sess,
"521",
"Export (HV)",
voltage_level,
False,
False,
utc_datetime(1996, 1, 1),
None,
)
insert_sources(sess)
source = Source.get_by_code(sess, "net")
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "E")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
supply = site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2000, 1, 1),
None,
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
None,
energisation_status,
{},
"22 7867 6232 781",
"510",
imp_supplier_contract,
"7748",
361,
None,
None,
None,
None,
None,
)
era = supply.eras[0]
channel = era.insert_channel(sess, True, "ACTIVE")
data_raw = [{
"start_date": utc_datetime(2009, 8, 10),
"value": 10,
"status": "A",
}]
channel.add_hh_data(sess, data_raw)
sess.commit()
caches = {}
chunk_start = utc_datetime(2009, 7, 31, 23, 00)
chunk_finish = utc_datetime(2009, 8, 31, 22, 30)
is_import = True
full_channels, hhd = chellow.computer._init_hh_data(
sess, caches, era, chunk_start, chunk_finish, is_import)
assert full_channels
expected_hhd = {
utc_datetime(2009, 8, 10): {
"imp-msp-kvarh": 0.0,
"imp-msp-kvar": 0.0,
"exp-msp-kvarh": 0.0,
"exp-msp-kvar": 0.0,
"status": "A",
"hist-kwh": 10.0,
"msp-kwh": 10.0,
"msp-kw": 20.0,
}
}
assert hhd == expected_hhd
def test_batch(mocker, sess, client):
site = Site.insert(sess, "22488", "Water Works")
g_dn = GDn.insert(sess, "EE", "East of England")
g_ldz = g_dn.insert_g_ldz(sess, "EA")
g_exit_zone = g_ldz.insert_g_exit_zone(sess, "EA1")
insert_g_units(sess)
g_unit_M3 = GUnit.get_by_code(sess, "M3")
participant = Participant.insert(sess, "CALB", "AK Industries")
market_role_Z = MarketRole.get_by_code(sess, "Z")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
g_cv_rate_script = {
"cvs": {
"EA": {
1: {
"applicable_at": utc_datetime(2020, 10, 3),
"cv": 39.2000
},
}
}
}
Contract.insert_non_core(sess, "g_cv", "", {}, utc_datetime(2000, 1, 1),
None, g_cv_rate_script)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
charge_script = """
import chellow.g_ccl
from chellow.g_engine import g_rates
from chellow.utils import reduce_bill_hhs
def virtual_bill_titles():
return [
'units_consumed', 'correction_factor', 'unit_code', 'unit_factor',
'calorific_value', 'kwh', 'gas_rate', 'gas_gbp', 'ccl_rate',
'standing_rate', 'standing_gbp', 'net_gbp', 'vat_gbp', 'gross_gbp',
'problem']
def virtual_bill(ds):
chellow.g_ccl.vb(ds)
for hh in ds.hh_data:
start_date = hh['start_date']
bill_hh = ds.bill_hhs[start_date]
bill_hh['units_consumed'] = hh['units_consumed']
bill_hh['correction_factor'] = {hh['correction_factor']}
bill_hh['unit_code'] = {hh['unit_code']}
bill_hh['unit_factor'] = {hh['unit_factor']}
bill_hh['calorific_value'] = {hh['calorific_value']}
kwh = hh['kwh']
bill_hh['kwh'] = kwh
gas_rate = float(
g_rates(ds.sess, ds.caches, db_id, start_date)['gas_rate'])
bill_hh['gas_rate'] = {gas_rate}
bill_hh['gas_gbp'] = gas_rate * kwh
bill_hh['ccl_kwh'] = kwh
ccl_rate = hh['ccl']
bill_hh['ccl_rate'] = {ccl_rate}
bill_hh['ccl_kwh'] = kwh
bill_hh['ccl_gbp'] = kwh * ccl_rate
if hh['utc_is_month_end']:
standing_rate = float(
g_rates(
ds.sess, ds.caches, db_id, start_date)['standing_rate'])
bill_hh['standing_rate'] = {standing_rate}
bill_hh['standing_gbp'] = standing_rate
if hh['utc_decimal_hour'] == 0:
pass
bill_hh['net_gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
bill_hh['vat_gbp'] = 0
bill_hh['gross_gbp'] = bill_hh['net_gbp'] + bill_hh['vat_gbp']
ds.bill = reduce_bill_hhs(ds.bill_hhs)
"""
g_contract_rate_script = {
"gas_rate": 0.1,
"standing_rate": 0.1,
}
g_contract = GContract.insert(
sess,
"Fusion 2020",
charge_script,
{},
utc_datetime(2000, 1, 1),
None,
g_contract_rate_script,
)
insert_g_reading_frequencies(sess)
g_reading_frequency_M = GReadingFrequency.get_by_code(sess, "M")
g_supply = site.insert_g_supply(
sess,
"87614362",
"main",
g_exit_zone,
utc_datetime(2018, 1, 1),
None,
"hgeu8rhg",
1,
g_unit_M3,
g_contract,
"d7gthekrg",
g_reading_frequency_M,
)
g_batch = g_contract.insert_g_batch(sess, "b1", "Jan batch")
breakdown = {"units_consumed": 771}
insert_bill_types(sess)
bill_type_n = BillType.get_by_code(sess, "N")
g_bill = g_batch.insert_g_bill(
sess,
g_supply,
bill_type_n,
"55h883",
"dhgh883",
utc_datetime(2019, 4, 3),
utc_datetime(2020, 1, 1),
utc_datetime(2020, 1, 31, 23, 30),
Decimal("45"),
Decimal("12.40"),
Decimal("1.20"),
Decimal("14.52"),
"",
breakdown,
)
sess.commit()
mock_file = StringIO()
mock_file.close = mocker.Mock()
mocker.patch("chellow.reports.report_429.open", return_value=mock_file)
mocker.patch("chellow.reports.report_429.chellow.dloads.make_names",
return_value=("a", "b"))
mocker.patch("chellow.reports.report_429.os.rename")
user = mocker.Mock()
chellow.reports.report_429.content(g_batch.id, g_bill.id, user)
actual = mock_file.getvalue()
expected = [
"batch,bill_reference,bill_type,bill_start_date,bill_finish_date,"
"mprn,supply_name,site_code,site_name,covered_start,covered_finish,"
"covered_bill_ids,covered_units_consumed,virtual_units_consumed,"
"covered_correction_factor,virtual_correction_factor,"
"covered_unit_code,virtual_unit_code,covered_unit_factor,"
"virtual_unit_factor,covered_calorific_value,virtual_calorific_value,"
"covered_kwh,virtual_kwh,covered_gas_rate,virtual_gas_rate,"
"covered_gas_gbp,virtual_gas_gbp,difference_gas_gbp,covered_ccl_rate,"
"virtual_ccl_rate,covered_standing_rate,virtual_standing_rate,"
"covered_standing_gbp,virtual_standing_gbp,difference_standing_gbp,"
"covered_net_gbp,virtual_net_gbp,difference_net_gbp,covered_vat_gbp,"
"virtual_vat_gbp,difference_vat_gbp,covered_gross_gbp,"
"virtual_gross_gbp,difference_gross_gbp,covered_problem,"
"virtual_problem",
"b1,55h883,N,2020-01-01 00:00,2020-01-31 23:30,87614362,main,22488,"
"Water Works,2020-01-01 00:00,2020-01-31 23:30,1,771,0,,1.0,,M3,,1.0"
",,39.2,45,0.0,,0.1,,0.0,,,0.00339,,0.1,,0.1,,12.40,0.1,12.3,1.20,0,"
"1.2,14.52,0.1,14.42,,",
]
expected_str = "\r\n".join(expected) + "\r\n"
assert actual == expected_str
def content(contract_id, days_hidden, user):
sess = f = writer = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'channel_snags.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
writer.writerow(
(
'Hidden Days', 'Chellow Id', 'Imp MPAN Core', 'Exp MPAN Core',
'Site Code', 'Site Name', 'Snag Description',
'Import Related?', 'Channel Type', 'Start Date', 'Finish Date',
'Days Since Snag Finished', 'Duration Of Snag (Days)',
'Is Ignored?'))
contract = Contract.get_hhdc_by_id(sess, contract_id)
now = Datetime.now(pytz.utc)
cutoff_date = now - relativedelta(days=days_hidden)
for snag, channel, era, supply, site_era, site in sess.query(
Snag, Channel, Era, Supply, SiteEra, Site).join(
Channel, Era, Supply, SiteEra, Site).filter(
SiteEra.is_physical == true(), Era.hhdc_contract == contract,
Snag.start_date < cutoff_date).order_by(
Site.code, Supply.id, Channel.imp_related,
Channel.channel_type, Snag.description,
Snag.start_date, Snag.id):
snag_start = snag.start_date
snag_finish = snag.finish_date
if snag_finish is None:
snag_finish_str = ''
duration = now - snag_start
age_of_snag = datetime.timedelta(0)
else:
snag_finish_str = snag_finish.strftime("%Y-%m-%d %H:%M")
duration = snag_finish - snag_start
age_of_snag = now - snag_finish
writer.writerow(
(
str(days_hidden), str(snag.id),
'' if era.imp_mpan_core is None else era.imp_mpan_core,
'' if era.exp_mpan_core is None else era.exp_mpan_core,
site.code, site.name, snag.description,
str(channel.imp_related), channel.channel_type,
snag_start.strftime("%Y-%m-%d %H:%M"), snag_finish_str,
str(age_of_snag.days + age_of_snag.seconds / (3600 * 24)),
str(duration.days + duration.seconds / (3600 * 24)),
str(snag.is_ignored)))
except:
msg = traceback.format_exc()
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)
import chellow.scenario
from chellow.models import Session, Contract
sess = None
try:
sess = Session()
contract = Contract.get_non_core_by_name(sess, 'ro')
db_id = contract.id
finally:
if sess is not None:
sess.close()
create_future_func = chellow.scenario.make_create_future_func_simple(
'ro', ['gbp_per_msp_kwh'])
def hh(supply_source):
bill = supply_source.supplier_bill
rate_set = supply_source.supplier_rate_sets['ro-rate']
try:
supply_source.caches['ro']
except KeyError:
supply_source.caches['ro'] = {}
try:
future_funcs = supply_source.caches['future_funcs']
except KeyError:
future_funcs = {}
supply_source.caches['future_funcs'] = future_funcs
def content(start_date, finish_date, contract_id, user):
caches = {}
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'mop_virtual_bills.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
contract = Contract.get_mop_by_id(sess, contract_id)
forecast_date = chellow.computer.forecast_date()
header_titles = [
'Import MPAN Core', 'Export MPAN Core', 'Start Date',
'Finish Date']
bill_titles = chellow.computer.contract_func(
caches, contract, 'virtual_bill_titles')()
writer.writerow(header_titles + bill_titles)
vb_func = chellow.computer.contract_func(
caches, contract, 'virtual_bill')
for era in sess.query(Era).filter(
or_(
Era.finish_date == null(), Era.finish_date >= start_date),
Era.start_date <= finish_date, Era.mop_contract == contract). \
order_by(Era.imp_mpan_core, Era.exp_mpan_core, Era.start_date):
chunk_start = hh_max(era.start_date, start_date)
chunk_finish = hh_min(era.finish_date, finish_date)
import_mpan_core = era.imp_mpan_core
if import_mpan_core is None:
import_mpan_core_str = ''
else:
is_import = True
import_mpan_core_str = import_mpan_core
export_mpan_core = era.exp_mpan_core
if export_mpan_core is None:
export_mpan_core_str = ''
else:
is_import = False
export_mpan_core_str = export_mpan_core
out = [
import_mpan_core_str, export_mpan_core_str,
hh_format(chunk_start), hh_format(chunk_finish)]
supply_source = chellow.computer.SupplySource(
sess, chunk_start, chunk_finish, forecast_date, era, is_import,
caches)
vb_func(supply_source)
bill = supply_source.mop_bill
for title in bill_titles:
if title in bill:
out.append(make_val(bill[title]))
del bill[title]
else:
out.append('')
for k in sorted(bill.keys()):
out.append(k)
out.append(str(bill[k]))
writer.writerow(out)
except:
msg = traceback.format_exc()
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):
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 content(contract_id, end_year, end_month, months, user):
caches = {}
sess = f = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'displaced.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
titles = [
'Site Code', 'Site Name', 'Associated Site Ids', 'From', 'To',
'Gen Types', 'CHP kWh', 'LM kWh', 'Turbine kWh', 'PV kWh']
finish_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) + \
relativedelta(months=1) - HH
start_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) - \
relativedelta(months=months-1)
forecast_date = chellow.computer.forecast_date()
contract = Contract.get_supplier_by_id(sess, contract_id)
sites = sess.query(Site).join(SiteEra).join(Era).join(Supply). \
join(Source).filter(
or_(Era.finish_date == null(), Era.finish_date >= start_date),
Era.start_date <= finish_date,
or_(
Source.code.in_(('gen', 'gen-net')),
Era.exp_mpan_core != null())).distinct()
bill_titles = chellow.computer.contract_func(
caches, contract, 'displaced_virtual_bill_titles', None)()
for title in bill_titles:
if title == 'total-msp-kwh':
title = 'total-displaced-msp-kwh'
titles.append(title)
writer.writerow(titles)
for site in sites:
month_start = start_date
month_finish = month_start + relativedelta(months=1) - HH
while not month_finish > finish_date:
for site_group in site.groups(
sess, month_start, month_finish, True):
if site_group.start_date > month_start:
chunk_start = site_group.start_date
else:
chunk_start = month_start
if site_group.finish_date > month_finish:
chunk_finish = month_finish
else:
chunk_finish = site_group.finish_date
displaced_era = chellow.computer.displaced_era(
sess, site_group, chunk_start, chunk_finish)
if displaced_era is None:
continue
supplier_contract = displaced_era.imp_supplier_contract
if contract is not None and contract != supplier_contract:
continue
linked_sites = ','.join(
a_site.code for a_site in site_group.sites
if not a_site == site)
generator_types = ' '.join(
sorted(
[
supply.generator_type.code for supply in
site_group.supplies
if supply.generator_type is not None]))
vals = [
site.code, site.name, linked_sites,
hh_format(chunk_start), hh_format(chunk_finish),
generator_types]
total_gen_breakdown = {}
results = iter(
sess.execute(
"select supply.id, hh_datum.value, "
"hh_datum.start_date, channel.imp_related, "
"source.code, generator_type.code as "
"gen_type_code from hh_datum, channel, source, "
"era, supply left outer join generator_type on "
"supply.generator_type_id = generator_type.id "
"where hh_datum.channel_id = channel.id and "
"channel.era_id = era.id and era.supply_id = "
"supply.id and supply.source_id = source.id and "
"channel.channel_type = 'ACTIVE' and not "
"(source.code = 'net' and channel.imp_related "
"is true) and hh_datum.start_date >= "
":chunk_start and hh_datum.start_date "
"<= :chunk_finish and "
"supply.id = any(:supply_ids) order "
"by hh_datum.start_date, supply.id",
params={
'chunk_start': chunk_start,
'chunk_finish': chunk_finish,
'supply_ids': [
s.id for s in site_group.supplies]}))
(
sup_id, hh_val, hh_start, imp_related, source_code,
gen_type_code) = next(
results, (None, None, None, None, None, None))
hh_date = chunk_start
while hh_date <= finish_date:
gen_breakdown = {}
exported = 0
while hh_start == hh_date:
if not imp_related and source_code in (
'net', 'gen-net'):
exported += hh_val
if (imp_related and source_code == 'gen') or \
(not imp_related and
source_code == 'gen-net'):
gen_breakdown[gen_type_code] = \
gen_breakdown.setdefault(
gen_type_code, 0) + hh_val
if (
not imp_related and
source_code == 'gen') or (
imp_related and
source_code == 'gen-net'):
gen_breakdown[gen_type_code] = \
gen_breakdown.setdefault(
gen_type_code, 0) - hh_val
(
sup_id, hh_val, hh_start, imp_related,
source_code, gen_type_code) = next(
results, (None, None, None, None, None, None))
displaced = sum(gen_breakdown.values()) - exported
added_so_far = 0
for key in sorted(gen_breakdown.keys()):
kwh = gen_breakdown[key]
if kwh + added_so_far > displaced:
total_gen_breakdown[key] = \
total_gen_breakdown.get(key, 0) + \
displaced - added_so_far
break
else:
total_gen_breakdown[key] = \
total_gen_breakdown.get(key, 0) + kwh
added_so_far += kwh
hh_date += HH
for title in ['chp', 'lm', 'turb', 'pv']:
vals.append(str(total_gen_breakdown.get(title, '')))
site_ds = chellow.computer.SiteSource(
sess, site, chunk_start, chunk_finish, forecast_date,
None, caches, displaced_era)
disp_func = chellow.computer.contract_func(
caches, supplier_contract, 'displaced_virtual_bill',
None)
disp_func(site_ds)
bill = site_ds.supplier_bill
for title in bill_titles:
if title in bill:
val = bill[title]
if isinstance(val, Datetime):
val = hh_format(val)
else:
val = str(val)
vals.append(val)
del bill[title]
else:
vals.append('')
for k in sorted(bill.keys()):
vals.append(k)
vals.append(str(bill[k]))
writer.writerow(vals)
month_start += relativedelta(months=1)
month_finish = month_start + relativedelta(months=1) - HH
except:
msg = traceback.format_exc()
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 test_get_era_bundles_bill_after_supply_end(sess, client):
"""Check that a bill starting after the end of a supply still gets
shown.
"""
site = Site.insert(sess, "22488", "Water Works")
insert_sources(sess)
source = Source.get_by_code(sess, "net")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
participant = Participant.insert(sess, "hhak", "AK Industries")
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(sess, market_role_M, "Fusion Mop Ltd",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_C, "Fusion DC",
utc_datetime(2000, 1, 1), None, None)
mop_contract = Contract.insert_mop(sess, "Fusion", participant, "", {},
utc_datetime(2000, 1, 1), None, {})
dc_contract = Contract.insert_hhdc(sess, "Fusion DC 2000", participant, "",
{}, utc_datetime(2000, 1, 1), None, {})
pc = Pc.insert(sess, "00", "hh", utc_datetime(2000, 1, 1), None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
imp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dno = participant.insert_party(sess, market_role_R, "WPD",
utc_datetime(2000, 1, 1), None, "22")
meter_type = MeterType.insert(sess, "C5", "COP 1-5",
utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(sess, "CR", "Credit",
utc_datetime(1996, 1, 1),
None)
Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "E")
supply = site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2020, 1, 1),
utc_datetime(2020, 1, 31),
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
None,
energisation_status,
{},
"22 7867 6232 781",
"510",
imp_supplier_contract,
"7748",
361,
None,
None,
None,
None,
None,
)
batch = imp_supplier_contract.insert_batch(sess, "b1", "batch 1")
insert_bill_types(sess)
bill_type_N = BillType.get_by_code(sess, "N")
batch.insert_bill(
sess,
"ytgeklf",
"s77349",
utc_datetime(2020, 2, 10),
utc_datetime(2020, 2, 2),
utc_datetime(2020, 3, 1),
Decimal(0),
Decimal("0.00"),
Decimal("0.00"),
Decimal("0.00"),
bill_type_N,
{},
supply,
)
sess.commit()
bundles = get_era_bundles(sess, supply)
assert len(bundles[0]["imp_bills"]["bill_dicts"]) == 1
def check_permissions(*args, **kwargs):
# sys.stderr.write("about to check permissions sys\n")
path = request.path
method = request.method
if method == 'GET' and path in (
'/health', '/bmreports',
'/elexonportal/file/download/BESTVIEWPRICES_FILE'):
return
g.user = None
user = None
auth = request.authorization
if auth is not None:
pword_digest = hashlib.md5(auth.password).hexdigest()
user = User.query.filter(
User.email_address == auth.username,
User.password_digest == pword_digest).first()
if user is None:
config_contract = Contract.get_non_core_by_name('configuration')
try:
email = config_contract.make_properties()['ips'][
request.remote_addr]
user = User.query.filter(User.email_address == email).first()
except KeyError:
pass
if user is not None:
g.user = user
role = user.user_role
role_code = role.code
path = request.path
if role_code == "viewer":
if path.startswith("/chellow/reports/") and \
path.endswith("/output/") and method in ("GET", "HEAD"):
return
elif role_code == "editor":
return
elif role_code == "party-viewer":
if method in ("GET", "HEAD"):
party = user.party
market_role_code = party.market_role.code
if market_role_code == 'C':
hhdc_contract_id = GET_int("hhdc-contract-id")
hhdc_contract = Contract.get_hhdc_by_id(hhdc_contract_id)
if hhdc_contract.party == party and (
request.path + "?" + request.query_string) \
.startswith(
"/chellow/reports/37/output/?"
"hhdc-contract-id=" + str(hhdc_contract.id)):
return
elif market_role_code == 'X':
if path.startswith(
"/supplier_contracts/" + party.id):
return
if user is None:
return Response(
'Could not verify your access level for that URL.\n'
'You have to login with proper credentials', 401,
{'WWW-Authenticate': 'Basic realm="Chellow"'})
else:
return Response('Forbidden', 403)
def content(contract_id, end_year, end_month, months, user):
caches = {}
sess = f = None
try:
sess = Session()
contract = Contract.get_hhdc_by_id(sess, contract_id)
finish_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) + \
relativedelta(months=1) - HH
start_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) - \
relativedelta(months=months - 1)
forecast_date = chellow.computer.forecast_date()
running_name, finished_name = chellow.dloads.make_names(
'hhdc_virtual_bills.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
bill_titles = chellow.computer.contract_func(
caches, contract, 'virtual_bill_titles')()
header_titles = [
'Import MPAN Core', 'Export MPAN Core', 'Start Date',
'Finish Date']
vb_func = chellow.computer.contract_func(
caches, contract, 'virtual_bill')
writer.writerow(header_titles + bill_titles)
for era in sess.query(Era).distinct().filter(
or_(Era.finish_date == null(), Era.finish_date >= start_date),
Era.start_date <= finish_date,
Era.hhdc_contract == contract).order_by(Era.supply_id):
imp_mpan_core = era.imp_mpan_core
if imp_mpan_core is None:
imp_mpan_core_str = ''
is_import = False
else:
is_import = True
imp_mpan_core_str = imp_mpan_core
exp_mpan_core = era.exp_mpan_core
exp_mpan_core_str = '' if exp_mpan_core is None else exp_mpan_core
chunk_start = hh_max(era.start_date, start_date)
chunk_finish = hh_min(era.finish_date, finish_date)
vals = [
imp_mpan_core_str, exp_mpan_core_str, hh_format(chunk_start),
hh_format(chunk_finish)]
supply_source = chellow.computer.SupplySource(
sess, chunk_start, chunk_finish, forecast_date, era, is_import,
caches)
vb_func(supply_source)
bill = supply_source.dc_bill
for title in bill_titles:
vals.append(str(bill.get(title, '')))
if title in bill:
del bill[title]
for k in sorted(bill.keys()):
vals.append(k)
vals.append(str(bill[k]))
writer.writerow(vals)
except BadRequest as e:
f.write("Problem " + e.description + traceback.format_exc() + '\n')
except:
msg = "Problem " + traceback.format_exc() + '\n'
f.write(msg)
finally:
f.close()
os.rename(running_name, finished_name)
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 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 content(start_date, finish_date, contract_id, user):
caches = {}
sess = f = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'virtual_bills.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
contract = Contract.get_supplier_by_id(sess, contract_id)
forecast_date = chellow.computer.forecast_date()
month_start = Datetime(
start_date.year, start_date.month, 1, tzinfo=pytz.utc)
month_finish = month_start + relativedelta(months=1) - HH
bill_titles = contract_func(
caches, contract, 'virtual_bill_titles', None)()
writer.writerow(
['MPAN Core', 'Site Code', 'Site Name', 'Account', 'From', 'To'] +
bill_titles)
while not month_start > finish_date:
period_start = start_date \
if month_start < start_date else month_start
if month_finish > finish_date:
period_finish = finish_date
else:
period_finish = month_finish
for era in sess.query(Era).distinct().filter(
or_(
Era.imp_supplier_contract_id == contract.id,
Era.exp_supplier_contract_id == contract.id),
Era.start_date <= period_finish,
or_(
Era.finish_date == null(),
Era.finish_date >= period_start)):
era_start = era.start_date
if period_start < era_start:
chunk_start = era_start
else:
chunk_start = period_start
era_finish = era.finish_date
if hh_after(period_finish, era_finish):
chunk_finish = era_finish
else:
chunk_finish = period_finish
polarities = []
if era.imp_supplier_contract == contract:
polarities.append(True)
if era.exp_supplier_contract == contract:
polarities.append(False)
for polarity in polarities:
vals = []
data_source = SupplySource(
sess, chunk_start, chunk_finish, forecast_date, era,
polarity, None, caches)
site = sess.query(Site).join(SiteEra).filter(
SiteEra.era == era,
SiteEra.is_physical == true()).one()
vals = [
data_source.mpan_core, site.code, site.name,
data_source.supplier_account,
hh_format(data_source.start_date),
hh_format(data_source.finish_date)]
contract_func(
caches, contract, 'virtual_bill', None)(data_source)
bill = data_source.supplier_bill
for title in bill_titles:
if title in bill:
val = str(bill[title])
del bill[title]
else:
val = ''
vals.append(val)
for k in sorted(bill.keys()):
vals.append(k)
vals.append(str(bill[k]))
writer.writerow(vals)
month_start += relativedelta(months=1)
month_finish = month_start + relativedelta(months=1) - HH
except BadRequest as e:
writer.writerow([e.description])
except:
writer.writerow([traceback.format_exc()])
finally:
if sess is not None:
sess.close()
f.close()
os.rename(running_name, finished_name)
def content(contract_id, end_year, end_month, months, user):
caches = {}
sess = f = supply_source = None
try:
sess = Session()
contract = Contract.get_dc_by_id(sess, contract_id)
month_list = list(
c_months_u(finish_year=end_year,
finish_month=end_month,
months=months))
start_date, finish_date = month_list[0][0], month_list[-1][-1]
forecast_date = chellow.computer.forecast_date()
running_name, finished_name = chellow.dloads.make_names(
"dc_virtual_bills.csv", user)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
bill_titles = chellow.computer.contract_func(caches, contract,
"virtual_bill_titles")()
header_titles = [
"Import MPAN Core",
"Export MPAN Core",
"Start Date",
"Finish Date",
]
vb_func = chellow.computer.contract_func(caches, contract,
"virtual_bill")
writer.writerow(header_titles + bill_titles)
for era in (sess.query(Era).distinct().filter(
or_(Era.finish_date == null(), Era.finish_date >= start_date),
Era.start_date <= finish_date,
Era.dc_contract == contract,
).options(joinedload(Era.channels)).order_by(Era.supply_id)):
imp_mpan_core = era.imp_mpan_core
if imp_mpan_core is None:
imp_mpan_core_str = ""
is_import = False
else:
is_import = True
imp_mpan_core_str = imp_mpan_core
exp_mpan_core = era.exp_mpan_core
exp_mpan_core_str = "" if exp_mpan_core is None else exp_mpan_core
chunk_start = hh_max(era.start_date, start_date)
chunk_finish = hh_min(era.finish_date, finish_date)
vals = [
imp_mpan_core_str,
exp_mpan_core_str,
hh_format(chunk_start),
hh_format(chunk_finish),
]
supply_source = chellow.computer.SupplySource(
sess, chunk_start, chunk_finish, forecast_date, era, is_import,
caches)
vb_func(supply_source)
bill = supply_source.dc_bill
for title in bill_titles:
vals.append(csv_make_val(bill.get(title)))
if title in bill:
del bill[title]
for k in sorted(bill.keys()):
vals.append(k)
vals.append(csv_make_val(bill[k]))
writer.writerow(vals)
# Avoid long-running transactions
sess.rollback()
except BadRequest as e:
msg = "Problem "
if supply_source is not None:
msg += ("with supply " + supply_source.mpan_core +
" starting at " + hh_format(supply_source.start_date) +
" ")
msg += str(e)
writer.writerow([msg])
except BaseException:
msg = "Problem " + traceback.format_exc() + "\n"
f.write(msg)
finally:
f.close()
os.rename(running_name, finished_name)
if sess is not None:
sess.close()
def test_SupplySource_init_nhh(sess, mocker):
"""Old style channels"""
site = Site.insert(sess, "CI017", "Water Works")
market_role_Z = MarketRole.insert(sess, "Z", "Non-core")
participant = Participant.insert(sess, "CALB", "AK Industries")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(sess, market_role_M, "Fusion Mop Ltd",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_C, "Fusion DC",
utc_datetime(2000, 1, 1), None, None)
mop_contract = Contract.insert_mop(sess, "Fusion", participant, "", {},
utc_datetime(2000, 1, 1), None, {})
dc_contract = Contract.insert_hhdc(sess, "Fusion DC 2000", participant, "",
{}, utc_datetime(2000, 1, 1), None, {})
pc = Pc.insert(sess, "03", "nhh", utc_datetime(2000, 1, 1), None)
ssc = Ssc.insert(sess, "0393", "unrestricted", True, utc_datetime(2000, 1),
None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
exp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dno = participant.insert_party(sess, market_role_R, "WPD",
utc_datetime(2000, 1, 1), None, "22")
meter_type = MeterType.insert(sess, "C5", "COP 1-5",
utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(sess, "CR", "Credit",
utc_datetime(1996, 1, 1),
None)
Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
dno.insert_llfc(
sess,
"521",
"Export (HV)",
voltage_level,
False,
False,
utc_datetime(1996, 1, 1),
None,
)
insert_sources(sess)
source = Source.get_by_code(sess, "net")
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "D")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
supply = site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2000, 1, 1),
None,
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
ssc,
energisation_status,
{},
None,
None,
None,
None,
None,
"22 7867 6232 781",
"521",
exp_supplier_contract,
"7748",
361,
)
era = supply.eras[0]
active_channel = era.insert_channel(sess, False, "ACTIVE")
active_data_raw = [{
"start_date": utc_datetime(2009, 8, 10),
"value": 10,
"status": "A",
}]
active_channel.add_hh_data(sess, active_data_raw)
era.insert_channel(sess, True, "REACTIVE_IMP")
sess.commit()
caches = {}
start_date = utc_datetime(2009, 7, 31, 23, 00)
finish_date = utc_datetime(2009, 8, 31, 22, 30)
forecast_date = utc_datetime(2019, 8, 31, 22, 30)
is_import = False
chellow.computer.SupplySource(sess, start_date, finish_date, forecast_date,
era, is_import, caches)
def content(scenario_props, scenario_id, base_name, site_id, supply_id, user):
now = Datetime.now(pytz.utc)
report_context = {}
future_funcs = {}
report_context['future_funcs'] = future_funcs
sess = None
try:
sess = Session()
if scenario_props is None:
scenario_contract = Contract.get_supplier_by_id(sess, scenario_id)
scenario_props = scenario_contract.make_properties()
base_name.append(scenario_contract.name)
for contract in sess.query(Contract).join(MarketRole).filter(
MarketRole.code == 'Z'):
try:
props = scenario_props[contract.name]
except KeyError:
continue
try:
rate_start = props['start_date']
except KeyError:
raise BadRequest(
"In " + scenario_contract.name + " for the rate " +
contract.name + " the start_date is missing.")
if rate_start is not None:
rate_start = rate_start.replace(tzinfo=pytz.utc)
lib = importlib.import_module('chellow.' + contract.name)
if hasattr(lib, 'create_future_func'):
future_funcs[contract.id] = {
'start_date': rate_start,
'func': lib.create_future_func(
props['multiplier'], props['constant'])}
start_date = scenario_props['scenario_start']
if start_date is None:
start_date = Datetime(
now.year, now.month, 1, tzinfo=pytz.utc)
else:
start_date = start_date.replace(tzinfo=pytz.utc)
base_name.append(
hh_format(start_date).replace(' ', '_').replace(':', '').
replace('-', ''))
months = scenario_props['scenario_duration']
base_name.append('for')
base_name.append(str(months))
base_name.append('months')
finish_date = start_date + relativedelta(months=months)
if 'kwh_start' in scenario_props:
kwh_start = scenario_props['kwh_start']
else:
kwh_start = None
if kwh_start is None:
kwh_start = chellow.computer.forecast_date()
else:
kwh_start = kwh_start.replace(tzinfo=pytz.utc)
sites = sess.query(Site).join(SiteEra).join(Era).filter(
Era.start_date <= finish_date,
or_(
Era.finish_date == null(),
Era.finish_date >= start_date)).distinct().order_by(Site.code)
if site_id is not None:
site = Site.get_by_id(sess, site_id)
sites = sites.filter(Site.id == site.id)
base_name.append('site')
base_name.append(site.code)
if supply_id is not None:
supply = Supply.get_by_id(sess, supply_id)
base_name.append('supply')
base_name.append(str(supply.id))
sites = sites.filter(Era.supply == supply)
running_name, finished_name = chellow.dloads.make_names(
'_'.join(base_name) + '.ods', user)
rf = open(running_name, "wb")
f = odswriter.writer(rf, '1.1')
group_tab = f.new_sheet("Site Level")
sup_tab = f.new_sheet("Supply Level")
changes = defaultdict(list, {})
try:
kw_changes = scenario_props['kw_changes']
except KeyError:
kw_changes = ''
for row in csv.reader(io.StringIO(kw_changes)):
if len(''.join(row).strip()) == 0:
continue
if len(row) != 4:
raise BadRequest(
"Can't interpret the row " + str(row) + " it should be of "
"the form SITE_CODE, USED / GENERATED, DATE, MULTIPLIER")
site_code, typ, date_str, kw_str = row
date = Datetime.strptime(date_str.strip(), "%Y-%m-%d").replace(
tzinfo=pytz.utc)
changes[site_code.strip()].append(
{
'type': typ.strip(), 'date': date,
'multiplier': float(kw_str)})
sup_header_titles = [
'imp-mpan-core', 'exp-mpan-core', 'metering-type', 'source',
'generator-type', 'supply-name', 'msn', 'pc', 'site-id',
'site-name', 'associated-site-ids', 'month']
site_header_titles = [
'site-id', 'site-name', 'associated-site-ids', 'month',
'metering-type', 'sources', 'generator-types']
summary_titles = [
'import-net-kwh', 'export-net-kwh', 'import-gen-kwh',
'export-gen-kwh', 'import-3rd-party-kwh', 'export-3rd-party-kwh',
'displaced-kwh', 'used-kwh', 'used-3rd-party-kwh',
'import-net-gbp', 'export-net-gbp', 'import-gen-gbp',
'export-gen-gbp', 'import-3rd-party-gbp', 'export-3rd-party-gbp',
'displaced-gbp', 'used-gbp', 'used-3rd-party-gbp',
'billed-import-net-kwh', 'billed-import-net-gbp']
title_dict = {}
for cont_type, con_attr in (
('mop', Era.mop_contract), ('dc', Era.hhdc_contract),
('imp-supplier', Era.imp_supplier_contract),
('exp-supplier', Era.exp_supplier_contract)):
titles = []
title_dict[cont_type] = titles
conts = sess.query(Contract).join(con_attr) \
.join(Era.supply).join(Source).filter(
Era.start_date <= start_date,
or_(
Era.finish_date == null(),
Era.finish_date >= start_date),
Source.code.in_(('net', '3rd-party'))
).distinct().order_by(Contract.id)
if supply_id is not None:
conts = conts.filter(Era.supply_id == supply_id)
for cont in conts:
title_func = chellow.computer.contract_func(
report_context, cont, 'virtual_bill_titles', None)
if title_func is None:
raise Exception(
"For the contract " + cont.name +
" there doesn't seem to be a "
"'virtual_bill_titles' function.")
for title in title_func():
if title not in titles:
titles.append(title)
sup_tab.writerow(
sup_header_titles + summary_titles + [None] +
['mop-' + t for t in title_dict['mop']] +
[None] + ['dc-' + t for t in title_dict['dc']] + [None] +
['imp-supplier-' + t for t in title_dict['imp-supplier']] +
[None] + ['exp-supplier-' + t for t in title_dict['exp-supplier']])
group_tab.writerow(site_header_titles + summary_titles)
sites = sites.all()
month_start = start_date
while month_start < finish_date:
month_finish = month_start + relativedelta(months=1) - HH
for site in sites:
site_changes = changes[site.code]
site_associates = set()
site_category = None
site_sources = set()
site_gen_types = set()
site_month_data = defaultdict(int)
for group in site.groups(
sess, month_start, month_finish, False):
site_associates.update(
set(
s.code for s in group.sites
if s.code != site.code))
for cand_supply in group.supplies:
site_sources.add(cand_supply.source.code)
if cand_supply.generator_type is not None:
site_gen_types.add(cand_supply.generator_type.code)
for cand_era in sess.query(Era).filter(
Era.supply == cand_supply,
Era.start_date <= group.finish_date, or_(
Era.finish_date == null(),
Era.finish_date >= group.start_date)). \
options(
joinedload(Era.channels),
joinedload(Era.pc),
joinedload(Era.mtc).joinedload(
Mtc.meter_type)):
if site_category != 'hh':
if cand_era.pc.code == '00':
site_category = 'hh'
elif site_category != 'amr':
if len(cand_era.channels) > 0:
site_category = 'amr'
elif site_category != 'nhh':
if cand_era.mtc.meter_type.code \
not in ['UM', 'PH']:
site_category = 'nhh'
else:
site_category = 'unmetered'
for group in site.groups(
sess, month_start, month_finish, True):
calcs = []
deltas = defaultdict(int)
group_associates = set(
s.code for s in group.sites if s.code != site.code)
for supply in group.supplies:
if supply_id is not None and supply.id != supply_id:
continue
for era in sess.query(Era).join(Supply) \
.join(Source).filter(
Era.supply == supply,
Era.start_date <= group.finish_date, or_(
Era.finish_date == null(),
Era.finish_date >= group.start_date)) \
.options(
joinedload(Era.ssc),
joinedload(Era.hhdc_contract),
joinedload(Era.mop_contract),
joinedload(Era.imp_supplier_contract),
joinedload(Era.exp_supplier_contract),
joinedload(Era.channels),
joinedload(Era.imp_llfc).joinedload(
Llfc.voltage_level),
joinedload(Era.exp_llfc).joinedload(
Llfc.voltage_level),
joinedload(Era.cop),
joinedload(Era.supply).joinedload(
Supply.dno_contract),
joinedload(Era.mtc).joinedload(
Mtc.meter_type)):
if era.start_date > group.start_date:
ss_start = era.start_date
else:
ss_start = group.start_date
if hh_before(era.finish_date, group.finish_date):
ss_finish = era.finish_date
else:
ss_finish = group.finish_date
if era.imp_mpan_core is None:
imp_ss = None
else:
imp_ss = SupplySource(
sess, ss_start, ss_finish, kwh_start, era,
True, None, report_context)
if era.exp_mpan_core is None:
exp_ss = None
measurement_type = imp_ss.measurement_type
else:
exp_ss = SupplySource(
sess, ss_start, ss_finish, kwh_start, era,
False, None, report_context)
measurement_type = exp_ss.measurement_type
order = meter_order[measurement_type]
calcs.append(
(
order, era.imp_mpan_core,
era.exp_mpan_core, imp_ss, exp_ss))
if imp_ss is not None and len(era.channels) == 0:
for hh in imp_ss.hh_data:
deltas[hh['start-date']] += hh['msp-kwh']
imp_net_delts = defaultdict(int)
exp_net_delts = defaultdict(int)
imp_gen_delts = defaultdict(int)
displaced_era = chellow.computer.displaced_era(
sess, group, group.start_date, group.finish_date)
site_ds = chellow.computer.SiteSource(
sess, site, group.start_date, group.finish_date,
kwh_start, None, report_context, displaced_era)
for hh in site_ds.hh_data:
try:
delta = deltas[hh['start-date']]
hh['import-net-kwh'] += delta
hh['used-kwh'] += delta
except KeyError:
pass
for hh in site_ds.hh_data:
for change in site_changes:
if change['type'] == 'used' and \
change['date'] <= hh['start-date']:
used = change['multiplier'] * hh['used-kwh']
exp_net = max(
0, hh['import-gen-kwh'] -
hh['export-gen-kwh'] -
used)
exp_net_delt = exp_net - hh['export-net-kwh']
exp_net_delts[hh['start-date']] += exp_net_delt
displaced = hh['import-gen-kwh'] - \
hh['export-gen-kwh'] - exp_net
imp_net = used - displaced
imp_delt = imp_net - hh['import-net-kwh']
imp_net_delts[hh['start-date']] += imp_delt
hh['import-net-kwh'] = imp_net
hh['used-kwh'] = used
hh['export-net-kwh'] = exp_net
hh['msp-kwh'] = displaced
elif change['type'] == 'generated' and \
change['date'] <= hh['start-date']:
imp_gen = change['multiplier'] * \
hh['import-gen-kwh']
imp_gen_delt = imp_gen - hh['import-gen-kwh']
exp_net = max(
0, imp_gen - hh['export-gen-kwh'] -
hh['used-kwh'])
exp_net_delt = exp_net - hh['export-net-kwh']
exp_net_delts[hh['start-date']] += exp_net_delt
displaced = imp_gen - hh['export-gen-kwh'] - \
exp_net
imp_net = hh['used-kwh'] - displaced
imp_net_delt = imp_net - hh['import-net-kwh']
imp_net_delts[hh['start-date']] += imp_net_delt
imp_gen_delts[hh['start-date']] += imp_gen_delt
hh['import-net-kwh'] = imp_net
hh['export-net-kwh'] = exp_net
hh['import-gen-kwh'] = imp_gen
hh['msp-kwh'] = displaced
if displaced_era is not None and supply_id is None:
month_data = {}
for sname in (
'import-net', 'export-net', 'import-gen',
'export-gen', 'import-3rd-party',
'export-3rd-party', 'msp', 'used',
'used-3rd-party', 'billed-import-net'):
for xname in ('kwh', 'gbp'):
month_data[sname + '-' + xname] = 0
month_data['used-kwh'] = \
month_data['displaced-kwh'] = \
sum(hh['msp-kwh'] for hh in site_ds.hh_data)
disp_supplier_contract = \
displaced_era.imp_supplier_contract
disp_vb_function = chellow.computer.contract_func(
report_context, disp_supplier_contract,
'displaced_virtual_bill', None)
if disp_vb_function is None:
raise BadRequest(
"The supplier contract " +
disp_supplier_contract.name +
" doesn't have the displaced_virtual_bill() "
"function.")
disp_vb_function(site_ds)
disp_supplier_bill = site_ds.supplier_bill
try:
gbp = disp_supplier_bill['net-gbp']
except KeyError:
disp_supplier_bill['problem'] += \
'For the supply ' + \
site_ds.mpan_core + \
' the virtual bill ' + \
str(disp_supplier_bill) + \
' from the contract ' + \
disp_supplier_contract.name + \
' does not contain the net-gbp key.'
month_data['used-gbp'] = \
month_data['displaced-gbp'] = \
site_ds.supplier_bill['net-gbp']
out = [
None, None, displaced_era.make_meter_category(),
'displaced', None, None, None, None, site.code,
site.name,
','.join(sorted(list(group_associates))),
month_finish] + \
[month_data[t] for t in summary_titles]
sup_tab.writerow(out)
for k, v in month_data.items():
site_month_data[k] += v
for i, (
order, imp_mpan_core, exp_mpan_core, imp_ss,
exp_ss) in enumerate(sorted(calcs, key=str)):
if imp_ss is None:
era = exp_ss.era
else:
era = imp_ss.era
supply = era.supply
source = supply.source
source_code = source.code
site_sources.add(source_code)
month_data = {}
for name in (
'import-net', 'export-net', 'import-gen',
'export-gen', 'import-3rd-party',
'export-3rd-party', 'displaced', 'used',
'used-3rd-party', 'billed-import-net'):
for sname in ('kwh', 'gbp'):
month_data[name + '-' + sname] = 0
if source_code == 'net':
delts = imp_net_delts
elif source_code == 'gen':
delts = imp_gen_delts
else:
delts = []
if len(delts) > 0 and imp_ss is not None:
for hh in imp_ss.hh_data:
diff = hh['msp-kwh'] + delts[hh['start-date']]
if diff < 0:
hh['msp-kwh'] = 0
hh['msp-kw'] = 0
delts[hh['start-date']] -= hh['msp-kwh']
else:
hh['msp-kwh'] += delts[hh['start-date']]
hh['msp-kw'] += hh['msp-kwh'] / 2
del delts[hh['start-date']]
left_kwh = sum(delts.values())
if left_kwh > 0:
first_hh = imp_ss.hh_data[0]
first_hh['msp-kwh'] += left_kwh
first_hh['msp-kw'] += left_kwh / 2
imp_supplier_contract = era.imp_supplier_contract
if imp_supplier_contract is not None:
import_vb_function = contract_func(
report_context, imp_supplier_contract,
'virtual_bill', None)
if import_vb_function is None:
raise BadRequest(
"The supplier contract " +
imp_supplier_contract.name +
" doesn't have the virtual_bill() "
"function.")
import_vb_function(imp_ss)
imp_supplier_bill = imp_ss.supplier_bill
try:
gbp = imp_supplier_bill['net-gbp']
except KeyError:
imp_supplier_bill['problem'] += \
'For the supply ' + \
imp_ss.mpan_core + \
' the virtual bill ' + \
str(imp_supplier_bill) + \
' from the contract ' + \
imp_supplier_contract.name + \
' does not contain the net-gbp key.'
if source_code in ('net', 'gen-net'):
month_data['import-net-gbp'] += gbp
month_data['used-gbp'] += gbp
elif source_code == '3rd-party':
month_data['import-3rd-party-gbp'] += gbp
month_data['used-gbp'] += gbp
elif source_code == '3rd-party-reverse':
month_data['export-3rd-party-gbp'] += gbp
month_data['used-gbp'] -= gbp
kwh = sum(
hh['msp-kwh'] for hh in imp_ss.hh_data)
if source_code in ('net', 'gen-net'):
month_data['import-net-kwh'] += kwh
month_data['used-kwh'] += kwh
elif source_code == '3rd-party':
month_data['import-3rd-party-kwh'] += kwh
month_data['used-kwh'] += kwh
elif source_code == '3rd-party-reverse':
month_data['export-3rd-party-kwh'] += kwh
month_data['used-kwh'] -= kwh
elif source_code in ('gen', 'gen-net'):
month_data['import-gen-kwh'] += kwh
exp_supplier_contract = era.exp_supplier_contract
if exp_supplier_contract is None:
kwh = sess.query(
func.coalesce(
func.sum(
cast(HhDatum.value, Float)), 0)). \
join(Channel).filter(
Channel.era == era,
Channel.channel_type == 'ACTIVE',
Channel.imp_related == false()).scalar()
if source_code == 'gen':
month_data['export-net-kwh'] += kwh
else:
export_vb_function = contract_func(
report_context, exp_supplier_contract,
'virtual_bill', None)
export_vb_function(exp_ss)
exp_supplier_bill = exp_ss.supplier_bill
try:
gbp = exp_supplier_bill['net-gbp']
except KeyError:
exp_supplier_bill['problem'] += \
'For the supply ' + \
imp_ss.mpan_core + \
' the virtual bill ' + \
str(imp_supplier_bill) + \
' from the contract ' + \
imp_supplier_contract.name + \
' does not contain the net-gbp key.'
kwh = sum(hh['msp-kwh'] for hh in exp_ss.hh_data)
if source_code in ('net', 'gen-net'):
month_data['export-net-kwh'] += kwh
month_data['export-net-gbp'] += gbp
elif source_code in \
('3rd-party', '3rd-party-reverse'):
month_data['export-3rd-party-kwh'] += kwh
month_data['export-3rd-party-gbp'] += gbp
month_data['used-kwh'] -= kwh
month_data['used-gbp'] -= gbp
elif source_code == 'gen':
month_data['export-gen-kwh'] += kwh
sss = exp_ss if imp_ss is None else imp_ss
dc_contract = era.hhdc_contract
sss.contract_func(
dc_contract, 'virtual_bill')(sss)
dc_bill = sss.dc_bill
gbp = dc_bill['net-gbp']
mop_contract = era.mop_contract
mop_bill_function = sss.contract_func(
mop_contract, 'virtual_bill')
mop_bill_function(sss)
mop_bill = sss.mop_bill
gbp += mop_bill['net-gbp']
if source_code in ('3rd-party', '3rd-party-reverse'):
month_data['import-3rd-party-gbp'] += gbp
else:
month_data['import-net-gbp'] += gbp
month_data['used-gbp'] += gbp
if source_code in ('gen', 'gen-net'):
generator_type = supply.generator_type.code
site_gen_types.add(generator_type)
else:
generator_type = None
sup_category = era.make_meter_category()
if CATEGORY_ORDER[site_category] < \
CATEGORY_ORDER[sup_category]:
site_category = sup_category
for bill in sess.query(Bill).filter(
Bill.supply == supply,
Bill.start_date <= sss.finish_date,
Bill.finish_date >= sss.start_date):
bill_start = bill.start_date
bill_finish = bill.finish_date
bill_duration = (
bill_finish - bill_start).total_seconds() + \
(30 * 60)
overlap_duration = (
min(bill_finish, sss.finish_date) -
max(bill_start, sss.start_date)
).total_seconds() + (30 * 60)
overlap_proportion = \
float(overlap_duration) / bill_duration
month_data['billed-import-net-kwh'] += \
overlap_proportion * float(bill.kwh)
month_data['billed-import-net-gbp'] += \
overlap_proportion * float(bill.net)
out = [
era.imp_mpan_core, era.exp_mpan_core,
sup_category, source_code,
generator_type, supply.name, era.msn, era.pc.code,
site.code, site.name,
','.join(sorted(list(site_associates))),
month_finish] + [
month_data[t] for t in summary_titles] + [None] + [
(mop_bill[t] if t in mop_bill else None)
for t in title_dict['mop']] + [None] + \
[(dc_bill[t] if t in dc_bill else None)
for t in title_dict['dc']]
if imp_supplier_contract is None:
out += [None] * \
(len(title_dict['imp-supplier']) + 1)
else:
out += [None] + [
(
imp_supplier_bill[t]
if t in imp_supplier_bill else None)
for t in title_dict['imp-supplier']]
if exp_supplier_contract is not None:
out += [None] + [
(
exp_supplier_bill[t]
if t in exp_supplier_bill else None)
for t in title_dict['exp-supplier']]
for k, v in month_data.items():
site_month_data[k] += v
sup_tab.writerow(out)
group_tab.writerow(
[
site.code, site.name,
''.join(sorted(list(site_associates))),
month_finish, site_category,
', '.join(sorted(list(site_sources))),
', '.join(sorted(list(site_gen_types)))] +
[site_month_data[k] for k in summary_titles])
sess.rollback()
month_start += relativedelta(months=1)
except BadRequest as e:
msg = e.description + traceback.format_exc()
sys.stderr.write(msg + '\n')
group_tab.writerow(["Problem " + msg])
except:
msg = traceback.format_exc()
sys.stderr.write(msg + '\n')
group_tab.writerow(["Problem " + msg])
finally:
if sess is not None:
sess.close()
try:
f.close()
rf.close()
os.rename(running_name, finished_name)
except:
msg = traceback.format_exc()
r_name, f_name = chellow.dloads.make_names('error.txt', user)
ef = open(r_name, "w")
ef.write(msg + '\n')
ef.close()
