def before_first_request():
chellow.utils.url_root = request.url_root
sess = None
try:
sess = Session()
configuration = sess.execute(
select(Contract).where(
Contract.name == "configuration")).scalar_one()
props = configuration.make_properties()
api_props = props.get("api", {})
api.description = api_props.get("description",
"Access Chellow data")
finally:
if sess is not None:
sess.close()
def content(start_date, finish_date, supply_id, mpan_cores, is_zipped, user):
if is_zipped:
file_extension = ".zip"
else:
file_extension = ".csv"
base_name = (
"hh_data_row_" + to_ct(start_date).strftime("%Y%m%d%H%M") + file_extension
)
tls = ["Site Code", "Imp MPAN Core", "Exp Mpan Core", "HH Start Clock-Time"]
for polarity in ("Import", "Export"):
for suffix in (
"ACTIVE kWh",
"ACTIVE Status",
"ACTIVE Modified",
"REACTIVE_IMP kVArh",
"REACTIVE_IMP Status",
"REACTIVE_IMP Modified",
"REACTIVE_EXP kVArh",
"REACTIVE_EXP Status",
"REACTIVE_EXP Modified",
):
tls.append(polarity + " " + suffix)
titles = csv_str(tls)
running_name, finished_name = chellow.dloads.make_names(base_name, user)
if is_zipped:
zf = zipfile.ZipFile(running_name, "w")
else:
tmp_file = open(running_name, "w")
sess = None
try:
sess = Session()
caches = {}
supplies = (
sess.query(Supply)
.join(Era)
.filter(
Era.start_date <= finish_date,
or_(Era.finish_date == null(), Era.finish_date >= start_date),
)
.order_by(Era.supply_id, Era.start_date)
.distinct()
)
if supply_id is not None:
sup = Supply.get_by_id(sess, supply_id)
supplies = supplies.filter(Era.supply == sup)
if mpan_cores is not None:
supplies = supplies.filter(
or_(
Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores)
)
)
if not is_zipped:
tmp_file.write(titles)
for supply in supplies:
site, era = (
sess.query(Site, Era)
.join(Era.site_eras)
.filter(
Era.supply == supply,
Era.start_date <= finish_date,
SiteEra.site_id == Site.id,
or_(Era.finish_date == null(), Era.finish_date >= start_date),
SiteEra.is_physical == true(),
)
.order_by(Era.id)
.first()
)
outs = []
data = iter(
sess.execute(
"""
select hh_base.start_date,
max(imp_active.value), max(imp_active.status),
max(imp_active.last_modified),
max(imp_reactive_imp.value), max(imp_reactive_imp.status),
max(imp_reactive_imp.last_modified),
max(imp_reactive_exp.value), max(imp_reactive_exp.status),
max(imp_reactive_exp.last_modified),
max(exp_active.value), max(exp_active.status),
max(exp_active.last_modified),
max(exp_reactive_imp.value), max(imp_reactive_imp.status),
max(exp_reactive_imp.last_modified),
max(exp_reactive_exp.value), max(exp_reactive_exp.status),
max(exp_reactive_exp.last_modified)
from hh_datum hh_base
join channel on hh_base.channel_id = channel.id
join era on channel.era_id = era.id
left join hh_datum imp_active
on (imp_active.id = hh_base.id and channel.imp_related is true and
channel.channel_type = 'ACTIVE')
left join hh_datum imp_reactive_imp
on (imp_reactive_imp.id = hh_base.id
and channel.imp_related is true and
channel.channel_type = 'REACTIVE_IMP')
left join hh_datum imp_reactive_exp
on (imp_reactive_exp.id = hh_base.id
and channel.imp_related is true and
channel.channel_type = 'REACTIVE_EXP')
left join hh_datum exp_active
on (exp_active.id = hh_base.id and channel.imp_related is false and
channel.channel_type = 'ACTIVE')
left join hh_datum exp_reactive_imp
on (exp_reactive_imp.id = hh_base.id
and channel.imp_related is
false and channel.channel_type = 'REACTIVE_IMP')
left join hh_datum exp_reactive_exp
on (exp_reactive_exp.id = hh_base.id
and channel.imp_related is false
and channel.channel_type = 'REACTIVE_EXP')
where supply_id = :supply_id
and hh_base.start_date between :start_date and :finish_date
group by hh_base.start_date
order by hh_base.start_date
""",
params={
"supply_id": supply.id,
"start_date": start_date,
"finish_date": finish_date,
},
)
)
datum = next(data, None)
for dt in hh_range(caches, start_date, finish_date):
row = [site.code, era.imp_mpan_core, era.exp_mpan_core, dt]
if datum is not None:
(
hh_start_date,
imp_active,
imp_active_status,
imp_active_modified,
imp_reactive_imp,
imp_reactive_imp_status,
imp_reactive_imp_modified,
imp_reactive_exp,
imp_reactive_exp_status,
imp_reactive_exp_modified,
exp_active,
exp_active_status,
exp_active_modified,
exp_reactive_imp,
exp_reactive_imp_status,
exp_reactive_imp_modified,
exp_reactive_exp,
exp_reactive_exp_status,
exp_reactive_exp_modified,
) = datum
if hh_start_date == dt:
datum = next(data, None)
row += [
imp_active,
imp_active_status,
imp_active_modified,
imp_reactive_imp,
imp_reactive_imp_status,
imp_reactive_imp_modified,
imp_reactive_exp,
imp_reactive_exp_status,
imp_reactive_exp_modified,
exp_active,
exp_active_status,
exp_active_modified,
exp_reactive_imp,
exp_reactive_imp_status,
exp_reactive_imp_modified,
exp_reactive_exp,
exp_reactive_exp_status,
exp_reactive_exp_modified,
]
outs.append(csv_str(row))
if is_zipped:
zf.writestr(
(
"hh_data_row_"
+ str(era.id)
+ "_"
+ str(era.imp_mpan_core)
+ "_"
+ str(era.exp_mpan_core)
).replace(" ", "")
+ ".csv",
titles + "".join(outs),
)
else:
tmp_file.write("".join(outs))
# Avoid a long-running transaction
sess.rollback()
except BaseException:
msg = "Problem " + traceback.format_exc()
if is_zipped:
zf.writestr("error.txt", msg)
else:
tmp_file.write(msg)
finally:
if sess is not None:
sess.close()
if is_zipped:
zf.close()
else:
tmp_file.close()
os.rename(running_name, finished_name)
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 content(running_name, finished_name, date, supply_id, mpan_cores):
sess = None
try:
sess = Session()
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
writer.writerow(
(
'Date', 'Physical Site Id', 'Physical Site Name',
'Other Site Ids', 'Other Site Names', 'Supply Id', 'Source',
'Generator Type', 'GSP Group', 'DNO Name', 'Voltage Level',
'Metering Type', 'Mandatory HH', 'PC', 'MTC', 'CoP', 'SSC',
'Number Of Registers', 'MOP Contract', 'Mop Account',
'HHDC Contract', 'HHDC Account', 'Meter Serial Number',
'Meter Installation Date', 'Latest Normal Meter Read Date',
'Latest Normal Meter Read Type', 'Latest DC Bill Date',
'Latest MOP Bill Date', 'Import ACTIVE?',
'Import REACTIVE_IMPORT?', 'Import REACTIVE_EXPORT?',
'Export ACTIVE?', 'Export REACTIVE_IMPORT?',
'Export REACTIVE_EXPORT?', 'Import MPAN core',
'Import Agreed Supply Capacity (kVA)', 'Import LLFC Code',
'Import LLFC Description', 'Import Supplier Contract',
'Import Supplier Account', 'Import Mandatory kW',
'Latest Import Supplier Bill Date', 'Export MPAN core',
'Export Agreed Supply Capacity (kVA)', 'Export LLFC Code',
'Export LLFC Description', 'Export Supplier Contract',
'Export Supplier Account', 'Export Mandatory kW',
'Latest Export Supplier Bill Date'))
NORMAL_READ_TYPES = ('N', 'C', 'N3')
year_start = date + HH - relativedelta(years=1)
eras = sess.query(Era).filter(
Era.start_date <= date,
or_(Era.finish_date == null(), Era.finish_date >= date)).order_by(
Era.supply_id)
if supply_id is not None:
supply = Supply.get_by_id(sess, supply_id)
eras = eras.filter(Era.supply == supply)
if mpan_cores is not None:
eras = eras.filter(
or_(
Era.imp_mpan_core.in_(mpan_cores),
Era.exp_mpan_core.in_(mpan_cores)))
for era in eras:
site_codes = []
site_names = []
for site_era in era.site_eras:
if site_era.is_physical:
physical_site = site_era.site
else:
site = site_era.site
site_codes.append(site.code)
site_names.append(site.name)
supply = era.supply
if era.imp_mpan_core is None:
voltage_level_code = era.exp_llfc.voltage_level.code
else:
voltage_level_code = era.imp_llfc.voltage_level.code
if supply.generator_type is None:
generator_type = ''
else:
generator_type = supply.generator_type.code
metering_type = era.make_meter_category()
if metering_type == 'nhh':
latest_prev_normal_read = sess.query(RegisterRead). \
join(Bill).join(RegisterRead.previous_type).filter(
ReadType.code.in_(NORMAL_READ_TYPES),
RegisterRead.previous_date <= date,
Bill.supply_id == supply.id).order_by(
RegisterRead.previous_date.desc()).first()
latest_pres_normal_read = sess.query(RegisterRead) \
.join(Bill).join(RegisterRead.present_type).filter(
ReadType.code.in_(NORMAL_READ_TYPES),
RegisterRead.present_date <= date,
Bill.supply == supply).order_by(
RegisterRead.present_date.desc()).first()
if latest_prev_normal_read is None and \
latest_pres_normal_read is None:
latest_normal_read_date = None
latest_normal_read_type = None
elif latest_pres_normal_read is not None and \
latest_prev_normal_read is None:
latest_normal_read_date = \
latest_pres_normal_read.present_date
latest_normal_read_type = \
latest_pres_normal_read.present_type.code
elif latest_pres_normal_read is None and \
latest_prev_normal_read is not None:
latest_normal_read_date = \
latest_prev_normal_read.previous_date
latest_normal_read_type = \
latest_prev_normal_read.previous_type.code
elif latest_pres_normal_read.present_date > \
latest_prev_normal_read.previous_date:
latest_normal_read_date = \
latest_pres_normal_read.present_date
latest_normal_read_type = \
latest_pres_normal_read.present_type.code
else:
latest_normal_read_date = \
latest_prev_normal_read.previous_date
latest_normal_read_type = \
latest_prev_normal_read.previous_type.code
if latest_normal_read_date is not None:
latest_normal_read_date = \
hh_format(latest_normal_read_date)
else:
latest_normal_read_date = metering_type
latest_normal_read_type = None
mop_contract = era.mop_contract
if mop_contract is None:
mop_contract_name = ''
mop_account = ''
latest_mop_bill_date = 'No MOP'
else:
mop_contract_name = mop_contract.name
mop_account = era.mop_account
latest_mop_bill_date = sess.query(Bill.finish_date) \
.join(Batch).filter(
Bill.start_date <= date, Bill.supply == supply,
Batch.contract == mop_contract).order_by(
Bill.finish_date.desc()).first()
if latest_mop_bill_date is not None:
latest_mop_bill_date = hh_format(latest_mop_bill_date[0])
hhdc_contract = era.hhdc_contract
if hhdc_contract is None:
hhdc_contract_name = ''
hhdc_account = ''
latest_hhdc_bill_date = 'No HHDC'
else:
hhdc_contract_name = hhdc_contract.name
hhdc_account = era.hhdc_account
latest_hhdc_bill_date = sess.query(Bill.finish_date) \
.join(Batch).filter(
Bill.start_date <= date, Bill.supply == supply,
Batch.contract == hhdc_contract).order_by(
Bill.finish_date.desc()).first()
if latest_hhdc_bill_date is not None:
latest_hhdc_bill_date = hh_format(latest_hhdc_bill_date[0])
channel_values = []
for imp_related in [True, False]:
for channel_type in CHANNEL_TYPES:
if era.find_channel(
sess, imp_related, channel_type) is None:
channel_values.append('false')
else:
channel_values.append('true')
imp_avg_months = None
exp_avg_months = None
for is_import in [True, False]:
if metering_type == 'nhh':
continue
params = {
'supply_id': supply.id, 'year_start': year_start,
'year_finish': date,
'is_import': is_import}
month_mds = tuple(
md[0] * 2 for md in sess.execute("""
select max(hh_datum.value) as md
from hh_datum join channel on (hh_datum.channel_id = channel.id)
join era on (channel.era_id = era.id)
where era.supply_id = :supply_id and hh_datum.start_date >= :year_start
and hh_datum.start_date <= :year_finish
and channel.channel_type = 'ACTIVE'
and channel.imp_related = :is_import
group by extract(month from (hh_datum.start_date at time zone 'utc'))
order by md desc
limit 3
""", params=params))
avg_months = sum(month_mds)
if len(month_mds) > 0:
avg_months /= len(month_mds)
if is_import:
imp_avg_months = avg_months
else:
exp_avg_months = avg_months
if (imp_avg_months is not None and imp_avg_months > 100) or \
(exp_avg_months is not None and exp_avg_months > 100):
mandatory_hh = 'yes'
else:
mandatory_hh = 'no'
imp_latest_supplier_bill_date = None
exp_latest_supplier_bill_date = None
for is_import in [True, False]:
if is_import:
if era.imp_mpan_core is None:
continue
else:
supplier_contract = era.imp_supplier_contract
else:
if era.exp_mpan_core is None:
continue
else:
supplier_contract = era.exp_supplier_contract
latest_supplier_bill_date = sess.query(Bill.finish_date) \
.join(Batch).filter(
Bill.start_date <= date, Bill.supply == supply,
Batch.contract == supplier_contract).order_by(
Bill.finish_date.desc()).first()
if latest_supplier_bill_date is not None:
latest_supplier_bill_date = \
latest_supplier_bill_date[0]
latest_supplier_bill_date = hh_format(
latest_supplier_bill_date)
if is_import:
imp_latest_supplier_bill_date = \
latest_supplier_bill_date
else:
exp_latest_supplier_bill_date = \
latest_supplier_bill_date
meter_installation_date = sess.query(func.min(Era.start_date)) \
.filter(Era.supply == era.supply, Era.msn == era.msn).one()[0]
if era.ssc is None:
ssc_code = num_registers = None
else:
ssc_code = era.ssc.code
num_registers = sess.query(MeasurementRequirement).filter(
MeasurementRequirement.ssc == era.ssc).count()
writer.writerow(
(
('' if value is None else str(value))) for value in [
hh_format(date), physical_site.code, physical_site.name,
', '.join(site_codes), ', '.join(site_names), supply.id,
supply.source.code, generator_type, supply.gsp_group.code,
supply.dno_contract.name, voltage_level_code,
metering_type, mandatory_hh, era.pc.code, era.mtc.code,
era.cop.code, ssc_code, num_registers, mop_contract_name,
mop_account, hhdc_contract_name, hhdc_account, era.msn,
hh_format(meter_installation_date),
latest_normal_read_date, latest_normal_read_type,
latest_hhdc_bill_date, latest_mop_bill_date] +
channel_values + [
era.imp_mpan_core, era.imp_sc,
None if era.imp_llfc is None else era.imp_llfc.code,
None if era.imp_llfc is None else era.imp_llfc.description,
None if era.imp_supplier_contract is None else
era.imp_supplier_contract.name,
era.imp_supplier_account, imp_avg_months,
imp_latest_supplier_bill_date] + [
era.exp_mpan_core, era.exp_sc,
None if era.exp_llfc is None else era.exp_llfc.code,
None if era.exp_llfc is None else era.exp_llfc.description,
None if era.exp_supplier_contract is None else
era.exp_supplier_contract.name, era.exp_supplier_account,
exp_avg_months, exp_latest_supplier_bill_date])
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 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 content(running_name, finished_name, date, supply_id, mpan_cores):
sess = None
try:
sess = Session()
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
titles = (
"Date",
"Import MPAN Core",
"Export MPAN Core",
"Physical Site Id",
"Physical Site Name",
"Other Site Ids",
"Other Site Names",
"Supply Id",
"Source",
"Generator Type",
"GSP Group",
"DNO Name",
"Voltage Level",
"Is Substations",
"Metering Type",
"Mandatory HH",
"PC",
"MTC",
"CoP",
"SSC Code",
"SSC Description",
"Energisation Status",
"Number Of Registers",
"MOP Contract",
"Mop Account",
"DC Contract",
"DC Account",
"Meter Serial Number",
"Meter Installation Date",
"Latest Normal Meter Read Date",
"Latest Normal Meter Read Type",
"Latest DC Bill Date",
"Latest MOP Bill Date",
"Supply Start Date",
"Supply Finish Date",
"Properties",
"Import ACTIVE?",
"Import REACTIVE_IMPORT?",
"Import REACTIVE_EXPORT?",
"Export ACTIVE?",
"Export REACTIVE_IMPORT?",
"Export REACTIVE_EXPORT?",
"Import Agreed Supply Capacity (kVA)",
"Import LLFC Code",
"Import LLFC Description",
"Import Supplier Contract",
"Import Supplier Account",
"Import Mandatory kW",
"Latest Import Supplier Bill Date",
"Export Agreed Supply Capacity (kVA)",
"Export LLFC Code",
"Export LLFC Description",
"Export Supplier Contract",
"Export Supplier Account",
"Export Mandatory kW",
"Latest Export Supplier Bill Date",
)
writer.writerow(titles)
NORMAL_READ_TYPES = ("N", "C", "N3")
year_start = date + HH - relativedelta(years=1)
era_ids = (sess.query(Era.id).filter(
Era.start_date <= date,
or_(Era.finish_date == null(), Era.finish_date >= date),
).order_by(Era.supply_id))
if supply_id is not None:
supply = Supply.get_by_id(sess, supply_id)
era_ids = era_ids.filter(Era.supply == supply)
if mpan_cores is not None:
era_ids = era_ids.filter(
or_(Era.imp_mpan_core.in_(mpan_cores),
Era.exp_mpan_core.in_(mpan_cores)))
for (era_id, ) in era_ids:
era, supply, generator_type = (sess.query(
Era, Supply, GeneratorType).join(
Supply, Era.supply_id == Supply.id).outerjoin(
GeneratorType,
Supply.generator_type_id == GeneratorType.id).filter(
Era.id == era_id).options(
joinedload(Era.channels),
joinedload(Era.cop),
joinedload(Era.dc_contract),
joinedload(Era.exp_llfc),
joinedload(Era.exp_supplier_contract),
joinedload(Era.imp_llfc),
joinedload(Era.imp_supplier_contract),
joinedload(Era.mop_contract),
joinedload(Era.mtc),
joinedload(Era.mtc).joinedload(Mtc.meter_type),
joinedload(Era.pc),
joinedload(Era.site_eras).joinedload(
SiteEra.site),
joinedload(Era.ssc),
joinedload(Era.energisation_status),
joinedload(Era.supply).joinedload(
Supply.source),
joinedload(Era.supply).joinedload(
Supply.gsp_group),
joinedload(Era.supply).joinedload(Supply.dno),
).one())
site_codes = []
site_names = []
for site_era in era.site_eras:
if site_era.is_physical:
physical_site = site_era.site
else:
site = site_era.site
site_codes.append(site.code)
site_names.append(site.name)
sup_eras = (sess.query(Era).filter(Era.supply == supply).order_by(
Era.start_date).all())
supply_start_date = sup_eras[0].start_date
supply_finish_date = sup_eras[-1].finish_date
if era.imp_mpan_core is None:
voltage_level_code = era.exp_llfc.voltage_level.code
is_substation = era.exp_llfc.is_substation
else:
voltage_level_code = era.imp_llfc.voltage_level.code
is_substation = era.imp_llfc.is_substation
if generator_type is None:
generator_type_str = ""
else:
generator_type_str = generator_type.code
metering_type = era.meter_category
if metering_type in ("nhh", "amr"):
latest_prev_normal_read = (
sess.query(RegisterRead).join(Bill).join(
RegisterRead.previous_type).filter(
ReadType.code.in_(NORMAL_READ_TYPES),
RegisterRead.previous_date <= date,
Bill.supply_id == supply.id,
).order_by(RegisterRead.previous_date.desc()).options(
joinedload(RegisterRead.previous_type)).first())
latest_pres_normal_read = (
sess.query(RegisterRead).join(Bill).join(
RegisterRead.present_type).filter(
ReadType.code.in_(NORMAL_READ_TYPES),
RegisterRead.present_date <= date,
Bill.supply == supply,
).order_by(RegisterRead.present_date.desc()).options(
joinedload(RegisterRead.present_type)).first())
if latest_prev_normal_read is None and latest_pres_normal_read is None:
latest_normal_read_date = None
latest_normal_read_type = None
elif (latest_pres_normal_read is not None
and latest_prev_normal_read is None):
latest_normal_read_date = latest_pres_normal_read.present_date
latest_normal_read_type = latest_pres_normal_read.present_type.code
elif (latest_pres_normal_read is None
and latest_prev_normal_read is not None):
latest_normal_read_date = latest_prev_normal_read.previous_date
latest_normal_read_type = latest_prev_normal_read.previous_type.code
elif (latest_pres_normal_read.present_date >
latest_prev_normal_read.previous_date):
latest_normal_read_date = latest_pres_normal_read.present_date
latest_normal_read_type = latest_pres_normal_read.present_type.code
else:
latest_normal_read_date = latest_prev_normal_read.previous_date
latest_normal_read_type = latest_prev_normal_read.previous_type.code
if latest_normal_read_date is not None:
latest_normal_read_date = hh_format(
latest_normal_read_date)
else:
latest_normal_read_date = metering_type
latest_normal_read_type = None
mop_contract = era.mop_contract
mop_contract_name = mop_contract.name
mop_account = era.mop_account
latest_mop_bill_date = (sess.query(
Bill.finish_date).join(Batch).filter(
Bill.start_date <= date,
Bill.supply == supply,
Batch.contract == mop_contract,
).order_by(Bill.finish_date.desc()).first())
if latest_mop_bill_date is not None:
latest_mop_bill_date = hh_format(latest_mop_bill_date[0])
dc_contract = era.dc_contract
dc_contract_name = dc_contract.name
dc_account = era.dc_account
latest_dc_bill_date = (sess.query(
Bill.finish_date).join(Batch).filter(
Bill.start_date <= date,
Bill.supply == supply,
Batch.contract == dc_contract,
).order_by(Bill.finish_date.desc()).first())
if latest_dc_bill_date is not None:
latest_dc_bill_date = hh_format(latest_dc_bill_date[0])
channel_values = []
for imp_related in [True, False]:
for channel_type in CHANNEL_TYPES:
if era.find_channel(sess, imp_related,
channel_type) is None:
channel_values.append("false")
else:
channel_values.append("true")
imp_avg_months = None
exp_avg_months = None
for is_import in [True, False]:
if metering_type == "nhh":
continue
params = {
"supply_id": supply.id,
"year_start": year_start,
"year_finish": date,
"is_import": is_import,
}
month_mds = tuple(md[0] * 2 for md in sess.execute(
"""
select max(hh_datum.value) as md
from hh_datum join channel on (hh_datum.channel_id = channel.id)
join era on (channel.era_id = era.id)
where era.supply_id = :supply_id and hh_datum.start_date >= :year_start
and hh_datum.start_date <= :year_finish
and channel.channel_type = 'ACTIVE'
and channel.imp_related = :is_import
group by extract(month from (hh_datum.start_date at time zone 'utc'))
order by md desc
limit 3
""",
params=params,
))
avg_months = sum(month_mds)
if len(month_mds) > 0:
avg_months /= len(month_mds)
if is_import:
imp_avg_months = avg_months
else:
exp_avg_months = avg_months
if (imp_avg_months is not None
and imp_avg_months > 100) or (exp_avg_months is not None
and exp_avg_months > 100):
mandatory_hh = "yes"
else:
mandatory_hh = "no"
imp_latest_supplier_bill_date = None
exp_latest_supplier_bill_date = None
for is_import in (True, False):
for er in (sess.query(Era).filter(
Era.supply == era.supply,
Era.start_date <= date).order_by(
Era.start_date.desc())):
if is_import:
if er.imp_mpan_core is None:
break
else:
supplier_contract = er.imp_supplier_contract
else:
if er.exp_mpan_core is None:
break
else:
supplier_contract = er.exp_supplier_contract
latest_bill_date = (sess.query(
Bill.finish_date).join(Batch).filter(
Bill.finish_date >= er.start_date,
Bill.finish_date <= hh_min(er.finish_date, date),
Bill.supply == supply,
Batch.contract == supplier_contract,
).order_by(Bill.finish_date.desc()).first())
if latest_bill_date is not None:
latest_bill_date = hh_format(latest_bill_date[0])
if is_import:
imp_latest_supplier_bill_date = latest_bill_date
else:
exp_latest_supplier_bill_date = latest_bill_date
break
meter_installation_date = (sess.query(func.min(
Era.start_date)).filter(Era.supply == era.supply,
Era.msn == era.msn).one()[0])
ssc = era.ssc
if ssc is None:
ssc_code = ssc_description = num_registers = None
else:
ssc_code, ssc_description = ssc.code, ssc.description
num_registers = (sess.query(MeasurementRequirement).filter(
MeasurementRequirement.ssc == ssc).count())
vals = ([
date,
era.imp_mpan_core,
era.exp_mpan_core,
physical_site.code,
physical_site.name,
", ".join(site_codes),
", ".join(site_names),
supply.id,
supply.source.code,
generator_type_str,
supply.gsp_group.code,
supply.dno.dno_code,
voltage_level_code,
is_substation,
metering_type,
mandatory_hh,
era.pc.code,
era.mtc.code,
era.cop.code,
ssc_code,
ssc_description,
era.energisation_status.code,
num_registers,
mop_contract_name,
mop_account,
dc_contract_name,
dc_account,
era.msn,
meter_installation_date,
latest_normal_read_date,
latest_normal_read_type,
latest_dc_bill_date,
latest_mop_bill_date,
supply_start_date,
supply_finish_date,
era.properties,
] + channel_values + [
era.imp_sc,
None if era.imp_llfc is None else era.imp_llfc.code,
None if era.imp_llfc is None else era.imp_llfc.description,
None if era.imp_supplier_contract is None else
era.imp_supplier_contract.name,
era.imp_supplier_account,
imp_avg_months,
imp_latest_supplier_bill_date,
] + [
era.exp_sc,
None if era.exp_llfc is None else era.exp_llfc.code,
None if era.exp_llfc is None else era.exp_llfc.description,
None if era.exp_supplier_contract is None else
era.exp_supplier_contract.name,
era.exp_supplier_account,
exp_avg_months,
exp_latest_supplier_bill_date,
])
writer.writerow([csv_make_val(v) for v in vals])
# Avoid a long-running transaction
sess.rollback()
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 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 content(user):
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"ecoes_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()
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", "ignore_mpan_cores"):
try:
ecoes_props[key]
except KeyError:
raise BadRequest(
f"The property {key} cannot be found in the 'ecoes' section of "
f"the configuration properties.")
ignore_mpan_cores = ecoes_props["ignore_mpan_cores"]
proxies = props.get("proxies", {})
s = requests.Session()
s.verify = False
r = s.get(ecoes_props["prefix"], proxies=proxies)
r = s.post(
ecoes_props["prefix"],
data={
"Username": ecoes_props["user_name"],
"Password": ecoes_props["password"],
},
allow_redirects=False,
)
imp_mpans = [
v.imp_mpan_core for (v, ) in sess.execute(
select(Era).join(Supply).join(Source).join(Supply.dno).filter(
Party.dno_code.notin_(("88", "99")),
Era.finish_date == null(),
Source.code != "3rd-party",
Era.imp_mpan_core.notin_(ignore_mpan_cores),
Era.imp_mpan_core != null(),
).distinct().order_by(Era.imp_mpan_core))
]
exp_mpans = [
v.exp_mpan_core for (v, ) in sess.execute(
select(Era).join(Supply).join(Source).join(Supply.dno).filter(
Party.dno_code.notin_(("88", "99")),
Era.finish_date == null(),
Source.code != "3rd-party",
Era.exp_mpan_core != null(),
Era.exp_mpan_core.notin_(ignore_mpan_cores),
).distinct().order_by(Era.exp_mpan_core))
]
mpans = imp_mpans + exp_mpans
r = s.get(
ecoes_props["prefix"] +
"NonDomesticCustomer/ExportPortfolioMPANs?fileType=csv",
proxies=proxies,
)
titles = (
"MPAN Core",
"MPAN Core No Spaces",
"ECOES PC",
"Chellow PC",
"ECOES MTC",
"Chellow MTC",
"ECOES LLFC",
"Chellow LLFC",
"ECOES SSC",
"Chellow SSC",
"ECOES Energisation Status",
"Chellow Energisation Status",
"ECOES Supplier",
"Chellow Supplier",
"ECOES DC",
"Chellow DC",
"ECOES MOP",
"Chellow MOP",
"ECOES GSP Group",
"Chellow GSP Group",
"ECOES MSN",
"Chellow MSN",
"ECOES Meter Type",
"Chellow Meter Type",
"Problem",
)
writer.writerow(titles)
parser = iter(csv.reader(r.text.splitlines(True)))
next(parser) # Skip titles
for values in parser:
problem = ""
ecoes_titles = [
"mpan-core",
"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",
"ssc",
"measurement-class",
"energisation-status",
"da",
"dc",
"mop",
"mop-appoint-date",
"gsp-group",
"gsp-effective-from",
"dno",
"msn",
"meter-install-date",
"meter-type",
"map-id",
]
ecoes = dict(zip(ecoes_titles, map(str.strip, values)))
mpan_spaces = " ".join((
ecoes["mpan-core"][:2],
ecoes["mpan-core"][2:6],
ecoes["mpan-core"][6:10],
ecoes["mpan-core"][-3:],
))
if mpan_spaces in ignore_mpan_cores:
continue
try:
ecoes_es = ecoes["energisation-status"]
except KeyError as e:
print(r.text)
raise e
ecoes_disconnected = ecoes_es == ""
current_chell = mpan_spaces in mpans
if ecoes_disconnected and current_chell:
problem += "Disconnected in ECOES, but current in Chellow. "
elif not ecoes_disconnected and not current_chell:
problem += f"In ECOES (as {ecoes_es}) but disconnected in Chellow. "
if current_chell:
mpans.remove(mpan_spaces)
era = sess.execute(
select(Era).filter(
Era.finish_date == null(),
or_(
Era.imp_mpan_core == mpan_spaces,
Era.exp_mpan_core == mpan_spaces,
),
).options(
joinedload(Era.supply).joinedload(Supply.gsp_group),
joinedload(Era.mop_contract).joinedload(
Contract.party).joinedload(Party.participant),
joinedload(Era.dc_contract).joinedload(
Contract.party).joinedload(Party.participant),
joinedload(Era.imp_supplier_contract).joinedload(
Contract.party).joinedload(Party.participant),
joinedload(Era.exp_supplier_contract).joinedload(
Contract.party).joinedload(Party.participant),
joinedload(Era.pc),
joinedload(Era.imp_llfc),
joinedload(Era.exp_llfc),
joinedload(Era.mtc).joinedload(Mtc.meter_type),
joinedload(Era.ssc),
joinedload(Era.energisation_status),
joinedload(Era.channels),
)).scalar()
if era.imp_mpan_core == mpan_spaces:
supplier_contract = era.imp_supplier_contract
llfc = era.imp_llfc
else:
supplier_contract = era.exp_supplier_contract
llfc = era.exp_llfc
chellow_pc = era.pc.code
try:
if int(ecoes["pc"]) != int(chellow_pc):
problem += "The PCs don't match. "
except ValueError:
problem += "Can't parse the PC. "
chellow_mtc = era.mtc.code
try:
if int(ecoes["mtc"]) != int(chellow_mtc):
problem += "The MTCs don't match. "
except ValueError:
problem += "Can't parse the MTC. "
chellow_llfc = llfc.code
if ecoes["llfc"].zfill(3) != chellow_llfc:
problem += "The LLFCs don't match. "
chellow_ssc = era.ssc
if chellow_ssc is None:
chellow_ssc = ""
chellow_ssc_int = None
else:
chellow_ssc = chellow_ssc.code
chellow_ssc_int = int(chellow_ssc)
if len(ecoes["ssc"]) > 0:
ecoes_ssc_int = int(ecoes["ssc"])
else:
ecoes_ssc_int = None
if ecoes_ssc_int != chellow_ssc_int and not (
ecoes_ssc_int is None and chellow_ssc_int is None):
problem += "The SSCs don't match. "
chellow_es = era.energisation_status.code
if ecoes_es != chellow_es:
problem += "The energisation statuses don't match. "
chellow_supplier = supplier_contract.party.participant.code
if chellow_supplier != ecoes["supplier"]:
problem += "The supplier codes don't match. "
dc_contract = era.dc_contract
if dc_contract is None:
chellow_dc = ""
else:
chellow_dc = dc_contract.party.participant.code
if chellow_dc != ecoes["dc"]:
problem += "The DC codes don't match. "
mop_contract = era.mop_contract
if mop_contract is None:
chellow_mop = ""
else:
chellow_mop = mop_contract.party.participant.code
if chellow_mop != ecoes["mop"]:
problem += "The MOP codes don't match. "
chellow_gsp_group = era.supply.gsp_group.code
if chellow_gsp_group != ecoes["gsp-group"]:
problem += "The GSP group codes don't match. "
chellow_msn = era.msn
if chellow_msn is None:
chellow_msn = ""
if chellow_msn != ecoes["msn"]:
problem += "The meter serial numbers don't match. "
chellow_meter_type = _meter_type(era)
if chellow_meter_type != ecoes["meter-type"]:
problem += (
"The meter types don't match. See "
"https://dtc.mrasco.com/DataItem.aspx?ItemCounter=0483 "
)
else:
chellow_pc = ""
chellow_mtc = ""
chellow_llfc = ""
chellow_ssc = ""
chellow_es = ""
chellow_supplier = ""
chellow_dc = ""
chellow_mop = ""
chellow_gsp_group = ""
chellow_msn = ""
chellow_meter_type = ""
if len(problem) > 0:
writer.writerow([
mpan_spaces,
ecoes["mpan-core"],
ecoes["pc"],
chellow_pc,
ecoes["mtc"],
chellow_mtc,
ecoes["llfc"],
chellow_llfc,
ecoes["ssc"],
chellow_ssc,
ecoes["energisation-status"],
chellow_es,
ecoes["supplier"],
chellow_supplier,
ecoes["dc"],
chellow_dc,
ecoes["mop"],
chellow_mop,
ecoes["gsp-group"],
chellow_gsp_group,
ecoes["msn"],
chellow_msn,
ecoes["meter-type"],
chellow_meter_type,
problem,
])
sess.expunge_all()
for mpan_core in mpans:
supply = Supply.get_by_mpan_core(sess, mpan_core)
era = supply.find_era_at(sess, None)
if era.imp_mpan_core == mpan_core:
supplier_contract = era.imp_supplier_contract
llfc = era.imp_llfc
else:
supplier_contract = era.exp_supplier_contract
llfc = era.exp_llfc
ssc = "" if era.ssc is None else era.ssc.code
es = era.energisation_status.code
dc_contract = era.dc_contract
if dc_contract is None:
dc = ""
else:
dc = dc_contract.party.participant.code
mop_contract = era.mop_contract
if mop_contract is None:
mop = ""
else:
mop = mop_contract.party.participant.code
msn = "" if era.msn is None else era.msn
meter_type = _meter_type(era)
writer.writerow([
mpan_core,
mpan_core.replace(" ", ""),
"",
era.pc.code,
"",
era.mtc.code,
"",
llfc.code,
"",
ssc,
"",
es,
"",
supplier_contract.party.participant.code,
"",
dc,
"",
mop,
"",
supply.gsp_group.code,
"",
msn,
"",
meter_type,
"In Chellow, but not in ECOES.",
])
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 content(supply_id, start_date, finish_date, user):
caches = {}
try:
sess = Session()
supply = Supply.get_by_id(sess, supply_id)
forecast_date = chellow.computer.forecast_date()
running_name, finished_name = chellow.dloads.make_names(
f"supply_virtual_bills_hh_{supply_id}.csv", user
)
f = open(running_name, mode="w", newline="")
w = csv.writer(f, lineterminator="\n")
mop_titles = []
dc_titles = []
imp_supplier_titles = []
exp_supplier_titles = []
for era in sess.execute(
select(Era).where(
Era.supply == supply,
Era.start_date <= finish_date,
or_(Era.finish_date == null(), Era.finish_date >= start_date),
)
).scalars():
ds = chellow.computer.SupplySource(
sess, era.start_date, era.start_date, forecast_date, era, True, caches
)
for t in ds.contract_func(era.mop_contract, "virtual_bill_titles")():
if t not in mop_titles:
mop_titles.append(t)
for t in ds.contract_func(era.dc_contract, "virtual_bill_titles")():
if t not in dc_titles:
dc_titles.append(t)
if era.imp_supplier_contract is not None:
for t in ds.contract_func(
era.imp_supplier_contract, "virtual_bill_titles"
)():
if t not in imp_supplier_titles:
imp_supplier_titles.append(t)
if era.exp_supplier_contract is not None:
ds = chellow.computer.SupplySource(
sess,
era.start_date,
era.start_date,
forecast_date,
era,
False,
caches,
)
for t in ds.contract_func(
era.exp_supplier_contract, "virtual_bill_titles"
)():
if t not in exp_supplier_titles:
exp_supplier_titles.append(t)
titles = [
"mpan_core",
"site_code",
"site_name",
"hh_start",
]
for pref, t in (
("mop", mop_titles),
("dc", dc_titles),
("imp_supplier", imp_supplier_titles),
("exp_supplier", exp_supplier_titles),
):
titles.append("")
titles.extend([f"{pref}_{n}" for n in t])
w.writerow(titles)
for hh_start in hh_range(caches, start_date, finish_date):
era = sess.execute(
select(Era).where(
Era.supply == supply,
Era.start_date <= hh_start,
or_(Era.finish_date == null(), Era.finish_date >= hh_start),
)
).scalar_one()
site = sess.execute(
select(Site)
.join(SiteEra)
.where(SiteEra.era == era, SiteEra.is_physical == true())
).scalar_one()
ds = chellow.computer.SupplySource(
sess, hh_start, hh_start, forecast_date, era, True, caches
)
vals = {
"mpan_core": ds.mpan_core,
"site_code": site.code,
"site_name": site.name,
"hh_start": hh_format(ds.start_date),
}
ds.contract_func(era.mop_contract, "virtual_bill")(ds)
for k, v in ds.mop_bill.items():
vals[f"mop_{k}"] = v
ds.contract_func(era.dc_contract, "virtual_bill")(ds)
for k, v in ds.dc_bill.items():
vals[f"dc_{k}"] = v
if era.imp_supplier_contract is not None:
ds.contract_func(era.imp_supplier_contract, "virtual_bill")(ds)
for k, v in ds.supplier_bill.items():
vals[f"imp_supplier_{k}"] = v
if era.exp_supplier_contract is not None:
ds = chellow.computer.SupplySource(
sess, hh_start, hh_start, forecast_date, era, False, caches
)
ds.contract_func(era.exp_supplier_contract, "virtual_bill")(ds)
for k, v in ds.supplier_bill.items():
vals[f"exp_supplier_{k}"] = v
w.writerow([csv_make_val(vals.get(t)) for t in titles])
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg)
w.writerow([msg])
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def content(
start_date, finish_date, supply_id, mpan_cores, is_zipped, user):
if is_zipped:
file_extension = ".zip"
else:
file_extension = ".csv"
base_name = "hh_data_row_" + start_date.strftime("%Y%m%d%H%M") + \
file_extension
titles = ','.join('"' + v + '"' for v in (
"Site Code", "Imp MPAN Core", "Exp Mpan Core", "Start Date",
"Import ACTIVE", "Import ACTIVE Status", "Import REACTIVE_IMP",
"Import REACTIVE_IMP Status", "Import REACTIVE_EXP",
"Import REACTIVE_EXP Status", "Export ACTIVE",
"Export ACTIVE Status", "Export REACTIVE_IMP",
"Export REACTIVE_IMP Status", "Export REACTIVE_EXP",
"Export REACTIVE_EXP Status")) + "\n"
running_name, finished_name = chellow.dloads.make_names(base_name, user)
if is_zipped:
zf = zipfile.ZipFile(running_name, 'w')
else:
tmp_file = open(running_name, "w")
sess = None
try:
sess = Session()
supplies = sess.query(Supply).join(Era).filter(
Era.start_date <= finish_date,
or_(
Era.finish_date == null(), Era.finish_date >= start_date),
).order_by(Era.supply_id, Era.start_date).distinct()
if supply_id is not None:
sup = Supply.get_by_id(sess, supply_id)
supplies = supplies.filter(Era.supply == sup)
if mpan_cores is not None:
supplies = supplies.filter(
or_(
Era.imp_mpan_core.in_(mpan_cores),
Era.exp_mpan_core.in_(mpan_cores)))
if not is_zipped:
tmp_file.write(titles)
for supply in supplies:
site, era = sess.query(
Site, Era).join(Era.site_eras).filter(
Era.supply == supply, Era.start_date <= finish_date,
SiteEra.site_id == Site.id,
or_(
Era.finish_date == null(),
Era.finish_date >= start_date),
SiteEra.is_physical == true()).order_by(Era.id).first()
outs = []
for hh_start_date, imp_active, imp_active_status, \
imp_reactive_imp, imp_reactive_imp_status, \
imp_reactive_exp, imp_reactive_exp_status, \
exp_active, exp_active_status, exp_reactive_imp, \
exp_reactive_imp_status, exp_reactive_exp, \
exp_reactive_exp_status in sess.execute("""
select hh_base.start_date, max(imp_active.value), max(imp_active.status),
max(imp_reactive_imp.value), max(imp_reactive_imp.status),
max(imp_reactive_exp.value), max(imp_reactive_exp.status),
max(exp_active.value), max(exp_active.status),
max(exp_reactive_imp.value), max(imp_reactive_imp.status),
max(exp_reactive_imp.value), max(imp_reactive_exp.status)
from hh_datum hh_base
join channel on hh_base.channel_id = channel.id
join era on channel.era_id = era.id
left join hh_datum imp_active
on (imp_active.id = hh_base.id and channel.imp_related is true and
channel.channel_type = 'ACTIVE')
left join hh_datum imp_reactive_imp
on (imp_reactive_imp.id = hh_base.id
and channel.imp_related is true and
channel.channel_type = 'REACTIVE_IMP')
left join hh_datum imp_reactive_exp
on (imp_reactive_exp.id = hh_base.id
and channel.imp_related is true and
channel.channel_type = 'REACTIVE_EXP')
left join hh_datum exp_active
on (exp_active.id = hh_base.id and channel.imp_related is false and
channel.channel_type = 'ACTIVE')
left join hh_datum exp_reactive_imp
on (exp_reactive_imp.id = hh_base.id
and channel.imp_related is
false and channel.channel_type = 'REACTIVE_IMP')
left join hh_datum exp_reactive_exp
on (exp_reactive_exp.id = hh_base.id
and channel.imp_related is false
and channel.channel_type = 'REACTIVE_EXP')
where supply_id = :supply_id
and hh_base.start_date between :start_date and :finish_date
group by hh_base.start_date
order by hh_base.start_date
""", params={
'supply_id': supply.id, 'start_date': start_date,
'finish_date': finish_date}):
outs.append(','.join(
'"' + ('' if v is None else str(v)) + '"' for v in (
site.code, era.imp_mpan_core, era.exp_mpan_core,
hh_format(hh_start_date), imp_active,
imp_active_status, imp_reactive_imp,
imp_reactive_imp_status, imp_reactive_exp,
imp_reactive_exp_status, exp_active,
exp_active_status, exp_reactive_imp,
exp_reactive_imp_status, exp_reactive_exp,
exp_reactive_exp_status)) + '\n')
if is_zipped:
zf.writestr(
(
"hh_data_row_" + str(era.id) + "_" +
str(era.imp_mpan_core) + "_" +
str(era.exp_mpan_core)).replace(' ', '') + '.csv',
titles + ''.join(outs))
else:
tmp_file.write(''.join(outs))
except:
msg = "Problem " + traceback.format_exc()
if is_zipped:
zf.writestr('error.txt', msg)
else:
tmp_file.write(msg)
finally:
if sess is not None:
sess.close()
if is_zipped:
zf.close()
else:
tmp_file.close()
os.rename(running_name, finished_name)
def run(self):
sess = None
try:
sess = Session()
self._log(
"Starting to parse the file with '" + self.parser_name + "'.")
set_read_write(sess)
batch = Batch.get_by_id(sess, self.batch_id)
raw_bills = self.parser.make_raw_bills()
self._log(
"Successfully parsed the file, and now I'm starting to "
"insert the raw bills.")
for self.bill_num, raw_bill in enumerate(raw_bills):
try:
with sess.begin_nested():
sess.execute(
"set transaction isolation level serializable "
"read write")
bill_type = BillType.get_by_code(
sess, raw_bill['bill_type_code'])
bill = batch.insert_bill(
sess, raw_bill['account'], raw_bill['reference'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net'], raw_bill['vat'],
raw_bill['gross'],
bill_type, raw_bill['breakdown'])
sess.flush()
for raw_read in raw_bill['reads']:
tpr_code = raw_read['tpr_code']
if tpr_code is None:
tpr = None
else:
tpr = Tpr.get_by_code(sess, tpr_code)
prev_type = ReadType.get_by_code(
sess, raw_read['prev_type_code'])
pres_type = ReadType.get_by_code(
sess, raw_read['pres_type_code'])
bill.insert_read(
sess, tpr, raw_read['coefficient'],
raw_read['units'], raw_read['msn'],
raw_read['mpan'], raw_read['prev_date'],
raw_read['prev_value'], prev_type,
raw_read['pres_date'], raw_read['pres_value'],
pres_type)
self.successful_bills.append(raw_bill)
except BadRequest as e:
raw_bill['error'] = str(e.description)
self.failed_bills.append(raw_bill)
if len(self.failed_bills) == 0:
sess.commit()
self._log(
"All the bills have been successfully loaded and attached "
"to the batch.")
else:
sess.rollback()
self._log(
"The import has finished, but there were " +
str(len(self.failed_bills)) + " failures, and so the "
"whole import has been rolled back.")
except:
sess.rollback()
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
def content(running_name, finished_name, date, supply_id, mpan_cores):
sess = None
try:
sess = Session()
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
writer.writerow(
('Date', 'Physical Site Id', 'Physical Site Name',
'Other Site Ids', 'Other Site Names', 'Supply Id', 'Source',
'Generator Type', 'GSP Group', 'DNO Name', 'Voltage Level',
'Metering Type', 'Mandatory HH', 'PC', 'MTC', 'CoP', 'SSC',
'Number Of Registers', 'MOP Contract', 'Mop Account',
'DC Contract', 'DC Account', 'Meter Serial Number',
'Meter Installation Date', 'Latest Normal Meter Read Date',
'Latest Normal Meter Read Type', 'Latest DC Bill Date',
'Latest MOP Bill Date', 'Supply Start Date', 'Supply Finish Date',
'Properties', 'Import ACTIVE?', 'Import REACTIVE_IMPORT?',
'Import REACTIVE_EXPORT?', 'Export ACTIVE?',
'Export REACTIVE_IMPORT?', 'Export REACTIVE_EXPORT?',
'Import MPAN core', 'Import Agreed Supply Capacity (kVA)',
'Import LLFC Code', 'Import LLFC Description',
'Import Supplier Contract', 'Import Supplier Account',
'Import Mandatory kW', 'Latest Import Supplier Bill Date',
'Export MPAN core', 'Export Agreed Supply Capacity (kVA)',
'Export LLFC Code', 'Export LLFC Description',
'Export Supplier Contract', 'Export Supplier Account',
'Export Mandatory kW', 'Latest Export Supplier Bill Date'))
NORMAL_READ_TYPES = ('N', 'C', 'N3')
year_start = date + HH - relativedelta(years=1)
era_ids = sess.query(Era.id).filter(
Era.start_date <= date,
or_(Era.finish_date == null(),
Era.finish_date >= date)).order_by(Era.supply_id)
if supply_id is not None:
supply = Supply.get_by_id(sess, supply_id)
era_ids = era_ids.filter(Era.supply == supply)
if mpan_cores is not None:
era_ids = era_ids.filter(
or_(Era.imp_mpan_core.in_(mpan_cores),
Era.exp_mpan_core.in_(mpan_cores)))
for era_id, in era_ids:
era, supply, generator_type = sess.query(
Era, Supply, GeneratorType).join(
Supply, Era.supply_id == Supply.id).outerjoin(
GeneratorType,
Supply.generator_type_id == GeneratorType.id).filter(
Era.id == era_id).options(
joinedload(Era.channels), joinedload(Era.cop),
joinedload(Era.dc_contract),
joinedload(Era.exp_llfc),
joinedload(Era.exp_supplier_contract),
joinedload(Era.imp_llfc),
joinedload(Era.imp_supplier_contract),
joinedload(Era.mop_contract),
joinedload(Era.mtc),
joinedload(Era.mtc).joinedload(Mtc.meter_type),
joinedload(Era.pc),
joinedload(Era.site_eras).joinedload(
SiteEra.site), joinedload(Era.ssc),
joinedload(Era.supply).joinedload(
Supply.source),
joinedload(Era.supply).joinedload(
Supply.gsp_group),
joinedload(Era.supply).joinedload(
Supply.dno)).one()
site_codes = []
site_names = []
for site_era in era.site_eras:
if site_era.is_physical:
physical_site = site_era.site
else:
site = site_era.site
site_codes.append(site.code)
site_names.append(site.name)
sup_eras = sess.query(Era).filter(Era.supply == supply).order_by(
Era.start_date).all()
supply_start_date = sup_eras[0].start_date
supply_finish_date = sup_eras[-1].finish_date
if era.imp_mpan_core is None:
voltage_level_code = era.exp_llfc.voltage_level.code
else:
voltage_level_code = era.imp_llfc.voltage_level.code
if generator_type is None:
generator_type_str = ''
else:
generator_type_str = generator_type.code
metering_type = era.meter_category
if metering_type == 'nhh':
latest_prev_normal_read = sess.query(RegisterRead). \
join(Bill).join(RegisterRead.previous_type).filter(
ReadType.code.in_(NORMAL_READ_TYPES),
RegisterRead.previous_date <= date,
Bill.supply_id == supply.id).order_by(
RegisterRead.previous_date.desc()).options(
joinedload(RegisterRead.previous_type)).first()
latest_pres_normal_read = sess.query(RegisterRead) \
.join(Bill).join(RegisterRead.present_type).filter(
ReadType.code.in_(NORMAL_READ_TYPES),
RegisterRead.present_date <= date,
Bill.supply == supply).order_by(
RegisterRead.present_date.desc()).options(
joinedload(RegisterRead.present_type)).first()
if latest_prev_normal_read is None and \
latest_pres_normal_read is None:
latest_normal_read_date = None
latest_normal_read_type = None
elif latest_pres_normal_read is not None and \
latest_prev_normal_read is None:
latest_normal_read_date = \
latest_pres_normal_read.present_date
latest_normal_read_type = \
latest_pres_normal_read.present_type.code
elif latest_pres_normal_read is None and \
latest_prev_normal_read is not None:
latest_normal_read_date = \
latest_prev_normal_read.previous_date
latest_normal_read_type = \
latest_prev_normal_read.previous_type.code
elif latest_pres_normal_read.present_date > \
latest_prev_normal_read.previous_date:
latest_normal_read_date = \
latest_pres_normal_read.present_date
latest_normal_read_type = \
latest_pres_normal_read.present_type.code
else:
latest_normal_read_date = \
latest_prev_normal_read.previous_date
latest_normal_read_type = \
latest_prev_normal_read.previous_type.code
if latest_normal_read_date is not None:
latest_normal_read_date = \
hh_format(latest_normal_read_date)
else:
latest_normal_read_date = metering_type
latest_normal_read_type = None
mop_contract = era.mop_contract
if mop_contract is None:
mop_contract_name = ''
mop_account = ''
latest_mop_bill_date = 'No MOP'
else:
mop_contract_name = mop_contract.name
mop_account = era.mop_account
latest_mop_bill_date = sess.query(Bill.finish_date) \
.join(Batch).filter(
Bill.start_date <= date, Bill.supply == supply,
Batch.contract == mop_contract).order_by(
Bill.finish_date.desc()).first()
if latest_mop_bill_date is not None:
latest_mop_bill_date = hh_format(latest_mop_bill_date[0])
dc_contract = era.dc_contract
if dc_contract is None:
dc_contract_name = ''
dc_account = ''
latest_dc_bill_date = 'No DC'
else:
dc_contract_name = dc_contract.name
dc_account = era.dc_account
latest_dc_bill_date = sess.query(Bill.finish_date) \
.join(Batch).filter(
Bill.start_date <= date, Bill.supply == supply,
Batch.contract == dc_contract).order_by(
Bill.finish_date.desc()).first()
if latest_dc_bill_date is not None:
latest_dc_bill_date = hh_format(latest_dc_bill_date[0])
channel_values = []
for imp_related in [True, False]:
for channel_type in CHANNEL_TYPES:
if era.find_channel(sess, imp_related,
channel_type) is None:
channel_values.append('false')
else:
channel_values.append('true')
imp_avg_months = None
exp_avg_months = None
for is_import in [True, False]:
if metering_type == 'nhh':
continue
params = {
'supply_id': supply.id,
'year_start': year_start,
'year_finish': date,
'is_import': is_import
}
month_mds = tuple(md[0] * 2
for md in sess.execute("""
select max(hh_datum.value) as md
from hh_datum join channel on (hh_datum.channel_id = channel.id)
join era on (channel.era_id = era.id)
where era.supply_id = :supply_id and hh_datum.start_date >= :year_start
and hh_datum.start_date <= :year_finish
and channel.channel_type = 'ACTIVE'
and channel.imp_related = :is_import
group by extract(month from (hh_datum.start_date at time zone 'utc'))
order by md desc
limit 3
""",
params=params))
avg_months = sum(month_mds)
if len(month_mds) > 0:
avg_months /= len(month_mds)
if is_import:
imp_avg_months = avg_months
else:
exp_avg_months = avg_months
if (imp_avg_months is not None and imp_avg_months > 100) or \
(exp_avg_months is not None and exp_avg_months > 100):
mandatory_hh = 'yes'
else:
mandatory_hh = 'no'
imp_latest_supplier_bill_date = None
exp_latest_supplier_bill_date = None
for is_import in (True, False):
for er in sess.query(Era).filter(
Era.supply == era.supply,
Era.start_date <= date).order_by(
Era.start_date.desc()):
if is_import:
if er.imp_mpan_core is None:
break
else:
supplier_contract = er.imp_supplier_contract
else:
if er.exp_mpan_core is None:
break
else:
supplier_contract = er.exp_supplier_contract
latest_bill_date = sess.query(Bill.finish_date) \
.join(Batch).filter(
Bill.finish_date >= er.start_date,
Bill.finish_date <= hh_min(er.finish_date, date),
Bill.supply == supply,
Batch.contract == supplier_contract).order_by(
Bill.finish_date.desc()).first()
if latest_bill_date is not None:
latest_bill_date = hh_format(latest_bill_date[0])
if is_import:
imp_latest_supplier_bill_date = latest_bill_date
else:
exp_latest_supplier_bill_date = latest_bill_date
break
meter_installation_date = sess.query(func.min(Era.start_date)) \
.filter(Era.supply == era.supply, Era.msn == era.msn).one()[0]
if era.ssc is None:
ssc_code = num_registers = None
else:
ssc_code = era.ssc.code
num_registers = sess.query(MeasurementRequirement).filter(
MeasurementRequirement.ssc == era.ssc).count()
writer.writerow(((
'' if value is None else str(value)
)) for value in [
hh_format(date), physical_site.code, physical_site.name,
', '.join(site_codes), ', '.join(site_names), supply.id,
supply.source.code, generator_type_str, supply.gsp_group.code,
supply.dno.dno_code, voltage_level_code, metering_type,
mandatory_hh, era.pc.code, era.mtc.code, era.cop.code,
ssc_code, num_registers, mop_contract_name, mop_account,
dc_contract_name, dc_account, era.msn,
hh_format(meter_installation_date), latest_normal_read_date,
latest_normal_read_type, latest_dc_bill_date,
latest_mop_bill_date,
hh_format(supply_start_date),
hh_format(supply_finish_date, ongoing_str=''), era.properties
] + channel_values + [
era.imp_mpan_core, era.imp_sc, None if era.imp_llfc is None
else era.imp_llfc.code, None if era.imp_llfc is None else era.
imp_llfc.description, None if era.imp_supplier_contract is None
else era.imp_supplier_contract.name, era.imp_supplier_account,
imp_avg_months, imp_latest_supplier_bill_date
] + [
era.exp_mpan_core, era.exp_sc, None if era.exp_llfc is None
else era.exp_llfc.code, None if era.exp_llfc is None else era.
exp_llfc.description, None if era.exp_supplier_contract is None
else era.exp_supplier_contract.name, era.exp_supplier_account,
exp_avg_months, exp_latest_supplier_bill_date
])
# Avoid a long-running transaction
sess.rollback()
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)
