def content(start_date, finish_date, g_contract_id, user):
report_context = {}
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"gas_virtual_bills.csv", user
)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
g_contract = GContract.get_by_id(sess, g_contract_id)
forecast_dt = forecast_date()
month_start = utc_datetime(start_date.year, start_date.month, 1)
month_finish = month_start + relativedelta(months=1) - HH
bill_titles = contract_func(report_context, g_contract, "virtual_bill_titles")()
writer.writerow(
["MPRN", "Site Code", "Site Name", "Account", "From", "To"] + bill_titles
)
while not month_start > finish_date:
period_start = hh_max(start_date, month_start)
period_finish = hh_min(finish_date, month_finish)
for g_era in (
sess.query(GEra)
.distinct()
.filter(
GEra.g_contract == g_contract,
GEra.start_date <= period_finish,
or_(GEra.finish_date == null(), GEra.finish_date >= period_start),
)
):
chunk_start = hh_max(g_era.start_date, period_start)
chunk_finish = hh_min(g_era.finish_date, period_finish)
data_source = GDataSource(
sess,
chunk_start,
chunk_finish,
forecast_dt,
g_era,
report_context,
None,
)
site = (
sess.query(Site)
.join(SiteGEra)
.filter(SiteGEra.g_era == g_era, SiteGEra.is_physical == true())
.one()
)
vals = [
data_source.mprn,
site.code,
site.name,
data_source.account,
hh_format(data_source.start_date),
hh_format(data_source.finish_date),
]
contract_func(report_context, g_contract, "virtual_bill")(data_source)
bill = data_source.bill
for title in bill_titles:
if title in bill:
val = make_val(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(["Problem: " + e.description])
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(g_supply_id, file_name, start_date, finish_date, user):
caches = {}
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
'g_supply_virtual_bill_' + str(g_supply_id) + '.csv', user)
f = open(running_name, mode='w', newline='')
writer = csv.writer(f, lineterminator='\n')
g_supply = GSupply.get_by_id(sess, g_supply_id)
forecast_dt = forecast_date()
prev_titles = None
for g_era in sess.query(GEra).filter(
GEra.g_supply == g_supply, GEra.start_date < finish_date,
or_(GEra.finish_date == null(),
GEra.finish_date > start_date)).order_by(GEra.start_date):
chunk_start = hh_max(g_era.start_date, start_date)
chunk_finish = hh_min(g_era.finish_date, finish_date)
site = sess.query(Site).join(SiteGEra).filter(
SiteGEra.g_era == g_era, SiteGEra.is_physical == true()).one()
ds = GDataSource(sess, chunk_start, chunk_finish, forecast_dt,
g_era, caches, None)
titles = [
'MPRN', 'Site Code', 'Site Name', 'Account', 'From', 'To', ''
]
output_line = [
g_supply.mprn, site.code, site.name, ds.account,
hh_format(ds.start_date),
hh_format(ds.finish_date), ''
]
contract_titles = g_contract_func(caches, g_era.g_contract,
'virtual_bill_titles')()
titles.extend(contract_titles)
g_contract_func(caches, g_era.g_contract, 'virtual_bill')(ds)
bill = ds.bill
for title in contract_titles:
if title in bill:
output_line.append(csv_make_val(bill[title]))
del bill[title]
else:
output_line.append('')
for k in sorted(bill.keys()):
output_line.extend([k, bill[k]])
if titles != prev_titles:
prev_titles = titles
writer.writerow([str(v) for v in titles])
writer.writerow(output_line)
except BadRequest as e:
writer.writerow(["Problem: " + e.description])
except BaseException:
writer.writerow([traceback.format_exc()])
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def content(base_name, site_id, g_supply_id, user, compression, start_date,
months):
now = utc_datetime_now()
report_context = {}
sess = None
try:
sess = Session()
base_name.append(
hh_format(start_date).replace(' ',
'_').replace(':',
'').replace('-', ''))
base_name.append('for')
base_name.append(str(months))
base_name.append('months')
finish_date = start_date + relativedelta(months=months)
forecast_from = chellow.computer.forecast_date()
sites = sess.query(Site).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 g_supply_id is not None:
g_supply = GSupply.get_by_id(sess, g_supply_id)
base_name.append('g_supply')
base_name.append(str(g_supply.id))
sites = sites.join(SiteGEra).join(GEra).filter(
GEra.g_supply == g_supply)
running_name, finished_name = chellow.dloads.make_names(
'_'.join(base_name) + '.ods', user)
rf = open(running_name, "wb")
site_rows = []
g_era_rows = []
era_header_titles = [
'creation_date', 'mprn', 'supply_name', 'exit_zone', 'msn', 'unit',
'contract', 'site_id', 'site_name', 'associated_site_ids', 'month'
]
site_header_titles = [
'creation_date', 'site_id', 'site_name', 'associated_site_ids',
'month'
]
summary_titles = ['kwh', 'gbp', 'billed_kwh', 'billed_gbp']
vb_titles = []
conts = sess.query(GContract).join(GEra).join(GSupply).filter(
GEra.start_date <= finish_date,
or_(GEra.finish_date == null(),
GEra.finish_date >= start_date)).distinct().order_by(
GContract.id)
if g_supply_id is not None:
conts = conts.filter(GEra.g_supply_id == g_supply_id)
for cont in conts:
title_func = chellow.computer.contract_func(
report_context, cont, 'virtual_bill_titles')
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 vb_titles:
vb_titles.append(title)
g_era_rows.append(era_header_titles + summary_titles + vb_titles)
site_rows.append(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_kwh = site_gbp = site_billed_kwh = site_billed_gbp = 0
for g_era in sess.query(GEra).join(SiteGEra).filter(
SiteGEra.site == site, SiteGEra.is_physical == true(),
GEra.start_date <= month_finish,
or_(GEra.finish_date == null(),
GEra.finish_date >= month_start)).options(
joinedload(GEra.g_contract),
joinedload(GEra.g_supply),
joinedload(GEra.g_supply).joinedload(
GSupply.g_exit_zone)).order_by(GEra.id):
g_supply = g_era.g_supply
if g_supply_id is not None and g_supply.id != g_supply_id:
continue
ss_start = hh_max(g_era.start_date, month_start)
ss_finish = hh_min(g_era.finish_date, month_finish)
ss = GDataSource(sess, ss_start, ss_finish, forecast_from,
g_era, report_context, None)
contract = g_era.g_contract
vb_function = contract_func(report_context, contract,
'virtual_bill')
if vb_function is None:
raise BadRequest(
"The contract " + contract.name +
" doesn't have the virtual_bill() function.")
vb_function(ss)
bill = ss.bill
try:
gbp = bill['net_gbp']
except KeyError:
gbp = 0
bill['problem'] += 'For the supply ' + ss.mprn + \
' the virtual bill ' + str(bill) + \
' from the contract ' + contract.name + \
' does not contain the net_gbp key.'
try:
kwh = bill['kwh']
except KeyError:
kwh = 0
bill['problem'] += "For the supply " + ss.mprn + \
" the virtual bill " + str(bill) + \
" from the contract " + contract.name + \
" does not contain the 'kwh' key."
billed_kwh = billed_gbp = 0
g_era_associates = {
s.site.code
for s in g_era.site_g_eras if not s.is_physical
}
for g_bill in sess.query(GBill).filter(
GBill.g_supply == g_supply,
GBill.start_date <= ss_finish,
GBill.finish_date >= ss_start):
bill_start = g_bill.start_date
bill_finish = g_bill.finish_date
bill_duration = (
bill_finish - bill_start).total_seconds() + \
(30 * 60)
overlap_duration = (min(bill_finish, ss_finish) - max(
bill_start, ss_start)).total_seconds() + (30 * 60)
overlap_proportion = overlap_duration / bill_duration
billed_kwh += overlap_proportion * float(g_bill.kwh)
billed_gbp += overlap_proportion * float(g_bill.net)
associated_site_ids = ','.join(sorted(g_era_associates))
g_era_rows.append([
now, g_supply.mprn, g_supply.name, g_supply.g_exit_zone
.code, g_era.msn, g_era.g_unit.code, contract.name,
site.code, site.name, associated_site_ids,
month_finish, kwh, gbp, billed_kwh, billed_gbp
] + [make_val(bill.get(t)) for t in vb_titles])
site_kwh += kwh
site_gbp += gbp
site_billed_kwh += billed_kwh
site_billed_gbp += billed_gbp
linked_sites = ', '.join(s.code
for s in site.find_linked_sites(
sess, month_start, month_finish))
site_rows.append([
now, site.code, site.name, linked_sites, month_finish,
site_kwh, site_gbp, site_billed_kwh, site_billed_gbp
])
sess.rollback()
write_spreadsheet(rf, compression, site_rows, g_era_rows)
month_start += relativedelta(months=1)
except BadRequest as e:
msg = e.description + traceback.format_exc()
sys.stderr.write(msg + '\n')
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, g_era_rows)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg + '\n')
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, g_era_rows)
finally:
if sess is not None:
sess.close()
try:
rf.close()
os.rename(running_name, finished_name)
except BaseException:
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()
def content(g_supply_id, start_date, finish_date, user):
caches = {}
try:
sess = Session()
g_supply = GSupply.get_by_id(sess, g_supply_id)
forecast_date = chellow.computer.forecast_date()
prev_titles = None
running_name, finished_name = chellow.dloads.make_names(
'g_supply_virtual_bills_hh_' + str(g_supply_id) + '.csv', user)
f = open(running_name, mode='w', newline='')
w = csv.writer(f, lineterminator='\n')
for hh_start in hh_range(caches, start_date, finish_date):
g_era = sess.query(GEra).filter(
GEra.g_supply == g_supply, GEra.start_date <= hh_start,
or_(GEra.finish_date == null(),
GEra.finish_date >= hh_start)).one()
site = sess.query(Site).join(SiteGEra).filter(
SiteGEra.g_era == g_era, SiteGEra.is_physical == true()).one()
ds = GDataSource(sess, hh_start, hh_start, forecast_date, g_era,
caches, None)
titles = [
'MPRN', 'Site Code', 'Site Name', 'Account', 'HH Start', ''
]
output_line = [
ds.mprn, site.code, site.name, ds.account,
hh_format(ds.start_date), ''
]
contract = g_era.g_contract
output_line.append('')
contract_titles = contract_func(caches, contract,
'virtual_bill_titles')()
titles.append('')
titles.extend(contract_titles)
contract_func(caches, contract, 'virtual_bill')(ds)
bill = ds.bill
for title in contract_titles:
output_line.append(csv_make_val(bill.get(title, '')))
if title in bill:
del bill[title]
for k in sorted(bill.keys()):
output_line.extend([k, csv_make_val(bill[k])])
if titles != prev_titles:
prev_titles = titles
w.writerow(titles)
w.writerow(output_line)
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(site_id,
g_supply_id,
user,
compression,
finish_year,
finish_month,
months,
now=None):
if now is None:
now = ct_datetime_now()
report_context = {}
sess = None
month_list = list(
c_months_u(finish_year=finish_year,
finish_month=finish_month,
months=months))
start_date, finish_date = month_list[0][0], month_list[-1][-1]
try:
sess = Session()
base_name = [
"g_monthly_duration",
hh_format(start_date).replace(" ",
"_").replace(":",
"").replace("-", ""),
"for",
str(months),
"months",
]
forecast_from = chellow.computer.forecast_date()
sites = (sess.query(Site).join(SiteGEra).join(GEra).filter(
SiteGEra.is_physical == true()).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 g_supply_id is not None:
g_supply = GSupply.get_by_id(sess, g_supply_id)
base_name.append("g_supply")
base_name.append(str(g_supply.id))
sites = sites.filter(GEra.g_supply == g_supply)
running_name, finished_name = chellow.dloads.make_names(
"_".join(base_name) + ".ods", user)
rf = open(running_name, "wb")
site_rows = []
g_era_rows = []
era_header_titles = [
"creation_date",
"mprn",
"supply_name",
"exit_zone",
"msn",
"unit",
"contract",
"site_id",
"site_name",
"associated_site_ids",
"month",
]
site_header_titles = [
"creation_date",
"site_id",
"site_name",
"associated_site_ids",
"month",
]
summary_titles = ["kwh", "gbp", "billed_kwh", "billed_gbp"]
vb_titles = []
conts = (sess.query(GContract).join(GEra).join(GSupply).filter(
GEra.start_date <= finish_date,
or_(GEra.finish_date == null(), GEra.finish_date >= start_date),
).distinct().order_by(GContract.id))
if g_supply_id is not None:
conts = conts.filter(GEra.g_supply_id == g_supply_id)
for cont in conts:
title_func = chellow.computer.contract_func(
report_context, cont, "virtual_bill_titles")
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 vb_titles:
vb_titles.append(title)
g_era_rows.append(era_header_titles + summary_titles + vb_titles)
site_rows.append(site_header_titles + summary_titles)
for month_start, month_finish in month_list:
for site in sites.filter(
GEra.start_date <= month_finish,
or_(GEra.finish_date == null(),
GEra.finish_date >= month_start),
):
site_kwh = site_gbp = site_billed_kwh = site_billed_gbp = 0
for g_era in (sess.query(GEra).join(SiteGEra).filter(
SiteGEra.site == site,
SiteGEra.is_physical == true(),
GEra.start_date <= month_finish,
or_(GEra.finish_date == null(),
GEra.finish_date >= month_start),
).options(
joinedload(GEra.g_contract),
joinedload(GEra.g_supply),
joinedload(GEra.g_supply).joinedload(
GSupply.g_exit_zone),
).order_by(GEra.id)):
g_supply = g_era.g_supply
if g_supply_id is not None and g_supply.id != g_supply_id:
continue
ss_start = hh_max(g_era.start_date, month_start)
ss_finish = hh_min(g_era.finish_date, month_finish)
ss = GDataSource(
sess,
ss_start,
ss_finish,
forecast_from,
g_era,
report_context,
None,
)
contract = g_era.g_contract
vb_function = contract_func(report_context, contract,
"virtual_bill")
if vb_function is None:
raise BadRequest(
"The contract " + contract.name +
" doesn't have the virtual_bill() function.")
vb_function(ss)
bill = ss.bill
try:
gbp = bill["net_gbp"]
except KeyError:
gbp = 0
bill["problem"] += (
"For the supply " + ss.mprn +
" the virtual bill " + str(bill) +
" from the contract " + contract.name +
" does not contain the net_gbp key.")
try:
kwh = bill["kwh"]
except KeyError:
kwh = 0
bill["problem"] += ("For the supply " + ss.mprn +
" the virtual bill " + str(bill) +
" from the contract " +
contract.name +
" does not contain the 'kwh' key.")
billed_kwh = billed_gbp = 0
g_era_associates = {
s.site.code
for s in g_era.site_g_eras if not s.is_physical
}
for g_bill in sess.query(GBill).filter(
GBill.g_supply == g_supply,
GBill.start_date <= ss_finish,
GBill.finish_date >= ss_start,
):
bill_start = g_bill.start_date
bill_finish = g_bill.finish_date
bill_duration = (bill_finish - bill_start
).total_seconds() + (30 * 60)
overlap_duration = (min(bill_finish, ss_finish) - max(
bill_start, ss_start)).total_seconds() + (30 * 60)
overlap_proportion = overlap_duration / bill_duration
billed_kwh += overlap_proportion * float(g_bill.kwh)
billed_gbp += overlap_proportion * float(g_bill.net)
associated_site_ids = ",".join(sorted(g_era_associates))
g_era_rows.append([
make_val(v) for v in [
now,
g_supply.mprn,
g_supply.name,
g_supply.g_exit_zone.code,
g_era.msn,
g_era.g_unit.code,
contract.name,
site.code,
site.name,
associated_site_ids,
month_finish,
kwh,
gbp,
billed_kwh,
billed_gbp,
]
] + [make_val(bill.get(t)) for t in vb_titles])
site_kwh += kwh
site_gbp += gbp
site_billed_kwh += billed_kwh
site_billed_gbp += billed_gbp
linked_sites = ", ".join(s.code
for s in site.find_linked_sites(
sess, month_start, month_finish))
site_rows.append([
make_val(v) for v in [
now,
site.code,
site.name,
linked_sites,
month_finish,
site_kwh,
site_gbp,
site_billed_kwh,
site_billed_gbp,
]
])
sess.rollback()
write_spreadsheet(rf, compression, site_rows, g_era_rows)
except BadRequest as e:
site_rows.append(["Problem " + e.description])
write_spreadsheet(rf, compression, site_rows, g_era_rows)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg + "\n")
site_rows.append(["Problem " + msg])
write_spreadsheet(rf, compression, site_rows, g_era_rows)
finally:
if sess is not None:
sess.close()
try:
rf.close()
os.rename(running_name, finished_name)
except BaseException:
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()