def seewhatwehave(request):
smid = get_sm_id(request)
if smid is None:
return pd.DataFrame(), pd.DataFrame(), ''
df = loadDataFromDb(f"select * from sm_accounts where session_id = '{smid}' and active='1' order by type_id", returndf=True)
df['type'] = df['type_id'].map({0: 'Electricity', 1: 'Gas', 2:'Electricity Export'})
df['source_id'].fillna(0, inplace=True)
df['source'] = df['source_id'].map({0: 'n3rgy', 1: 'Octopus'})
s = f"""
select sm_accounts.account_id, sm_accounts.type_id, sm_periods.period from sm_accounts
left join sm_periods on sm_periods.period between sm_accounts.first_period and sm_accounts.last_period
left join sm_quantity on sm_quantity.period_id=sm_periods.period_id and sm_quantity.account_id= sm_accounts.account_id
where sm_quantity.quantity is null and sm_accounts.session_id='{smid}' and sm_accounts.active='1'
"""
df_gaps = loadDataFromDb(s, returndf=True)
df2 = df_gaps.groupby('account_id').count()
df['gaps'] = df.account_id.map(df2.period)
df.gaps.fillna(0, inplace=True)
df_gaps.sort_values(['type_id','period'], ascending=[True, False], inplace=True)
s3 = '<P>All times on this page are in UTC.</P><TABLE><TR><TH>ID</TH><TH>Type</TH><TH>First Period</TH><TH>Last Period</TH><TH>Gaps</TH><TH>Last Updated</TH><TH>Source</TH></TR>'
for i, j in df.iterrows():
s3a = '<TR><TD>{}</TD><TD>{}</TD><TD>{}</TD><TD>{}</TD><TD>{}</TD><TD>{}</TD><TD>{}</TD></TR>'
g = '0' if j.gaps == 0 else '{:.0f}'.format(j.gaps)
s3 += s3a.format(j.account_id, j.type, j.first_period, j.last_period, g, str(j.last_updated)[:16], j.source)
s3 += '</TABLE><BR>'
return df, df_gaps, s3
def loadSmData(request, type_id):
smid = get_sm_id(request, createifnone=True)
if ('n3rgy' in request.GET):
key = request.GET.get('n3rgy')
region = None
n3adj = int(request.GET.get('n3adj', '1'))
df = getDataFromN3RGY(key, type_id, n3adj)
source_id = 0
elif 'octopus' in request.GET:
key = request.GET.get('octopus')
df, region = octopusconsumption(key, type_id)
source_id = 1
else:
raise Exception('MAC, n3rgy or octopus keys are not provided')
if (df is None) or (df.shape[0] == 0):
estr = 'No {} data retrieved from {} - go back to Admin page, check key and try again.'
estr = estr.format(
['Electricity Consumption', 'Gas Consumption',
'Export'][type_id], ['n3rgy', 'Octopus'][source_id])
raise Exception(estr)
s = f"select * from sm_accounts where session_id='{smid}' and type_id={type_id} and active='1' limit 1"
accounts = loadDataFromDb(s, returndf=True)
if len(accounts):
account_id = accounts['account_id'].values[0]
s = "delete from sm_quantity where account_id={}".format(account_id)
_ = loadDataFromDb(s)
s = """
update sm_accounts
set first_period='{}', last_period='{}', last_updated=CURRENT_TIMESTAMP, session_id='{}'
where account_id={} and type_id={} and active='1';
"""
s = s.format(df['timestamp'].iloc[0].strftime('%Y-%m-%d %H:%M'),
df['timestamp'].iloc[-1].strftime('%Y-%m-%d %H:%M'), smid,
account_id, type_id)
_ = loadDataFromDb(s)
else:
if region is not None:
region = "'{}'".format(region)
else:
region = 'Null'
s = """
insert into sm_accounts (type_id, first_period, last_period, last_updated, source_id, region, session_id, active)
values ({}, '{}', '{}', CURRENT_TIMESTAMP, {}, {}, '{}', '1') returning account_id;"""
s = s.format(type_id,
df['timestamp'].iloc[0].strftime('%Y-%m-%d %H:%M'),
df['timestamp'].iloc[-1].strftime('%Y-%m-%d %H:%M'),
source_id, region, smid)
account_id = loadDataFromDb(s)
account_id = account_id[0][0]
s = """
insert into sm_quantity (account_id, period_id, quantity)
VALUES
"""
for i, j in df.iterrows():
s += ' ({}, {}, {}),'.format(account_id, i, j.value)
s = s[:-1] + ';'
_ = loadDataFromDb(s)
return smid
def sm_log(request, choice, smid=None):
url = request.get_full_path()
method = 0
if request.method == 'POST':
method = 1
tasks = list(request.POST.keys())
if len(tasks):
if (smid is None) and ('load' in tasks[0]):
return None
else:
url = url + '&task=' + tasks[0]
if 'octopus' in request.GET:
url = adj_url(url, [], [('octopus', '')])
elif 'n3rgy' in request.GET:
url = adj_url(url, [], [('n3rgy', '')])
if smid is None:
if isdemo(request):
smid = None
else:
smid = get_sm_id(request)
if smid is None:
smid = 'Null'
else:
smid = f"'{smid}'"
s = f"""
insert into sm_log (datetime, choice, method, session_id, url, http_user_agent) values
(CURRENT_TIMESTAMP, '{choice}', {method}, {smid}, '{url[:120]}', '{request.META.get('HTTP_USER_AGENT')[:120]}');
"""
loadDataFromDb(s)
def deleteSmData(smid):
s = f"""
update sm_accounts set active='0' where session_id='{smid}';
delete from sm_quantity
where account_id not in
(select account_id from sm_accounts where active='1');
"""
_ = loadDataFromDb(s)
def getmode(smid):
s = f"select type_id from sm_accounts where session_id = '{smid}' and active='1'"
type_ids = loadDataFromDb(s)
mode = ['0'] * 3
for j in type_ids:
mode[j[0]] = '1'
mode[0] = '1'
return ''.join(mode)
def get_prices(region, product, after_date):
s = f'''
select concat(local_date, ' ', local_time) dt, value from sm_hh_variable_vals v
inner join sm_periods on v.period_id=sm_periods.period_id
inner join sm_tariffs on sm_tariffs.var_id=v.var_id
where product='{product}' and region='{region}'
and concat(sm_periods.local_date, ' ', sm_periods.local_time)>='{after_date.strftime('%Y-%m-%d %H:%M')}'
order by dt
'''
df2 = loadDataFromDb(s, returndf=True)
#raise Exception(df2.iloc[40:60])
df2 = pd.Series(
0.5 *
(df2['value'].iloc[0::2].values + df2['value'].iloc[1::2].values),
index=df2['dt'].iloc[0::2])
return df2
def parsetariff(request, tariff, type_id, vat, **kwargs):
isfixed = tariff.replace(',', '').replace(':',
'').replace('-', '').replace(
'.', '').isnumeric()
if isfixed:
t = tariff.split(',')
if len(t) > 1:
pricestr = 'CASE '
for opt in t:
if ':' in opt:
[s, p] = opt.split(':')
[s, e] = [x[:2] + ':' + x[-2:] for x in s.split('-')]
if s < e:
pricestr += f"WHEN local_time>='{s}' and local_time<'{e}' then {float(p)/vat} "
else:
pricestr += f"WHEN local_time>='{s}' or local_time<'{e}' then {float(p)/vat} "
else:
pricestr += f' else {float(opt)/vat} end as value'
else:
pricestr = f' {float(t[0])/vat} as value'
pricestr = f"select period_id, {pricestr} from periods"
return isfixed, pricestr
else:
region = kwargs.get('region', None) or request.GET.get('region')
s = f"select var_id, granularity_id from sm_variables where product='{tariff}' and region='{region}' and type_id={type_id}"
s = loadDataFromDb(s)
if len(s):
var_id = s[0][0]
granularity_id = s[0][1]
else:
raise Exception("No data for tariff {} and region {}".format(
tariff, region))
if granularity_id == 0:
pricestr = f"select period_id, value from sm_hh_variable_vals v where v.var_id={var_id} "
elif granularity_id == 1:
pricestr = f"""
select period_id, value from periods
inner join sm_d_variable_vals v on periods.local_date=v.local_date
where v.var_id={var_id}"""
return isfixed, pricestr
def homepage(request):
urladmin = request.get_full_path().replace('home', 'admin', 1)
smid = get_sm_id(request)
if smid is not None:
sql = f"select type_id, first_period, last_period, source_id, region from sm_accounts where session_id= '{smid}' and active='1' order by type_id;"
df = loadDataFromDb(sql, returndf=True)
else:
df = pd.DataFrame()
s = """<P>This website allows UK energy customers with smart meters to access their energy consumption data,
and to perform a range of calculations on the back of this, for example reconciling bills and tracking carbon intensity. </P>"""
u = request.get_full_path().replace('home', 'getstarting')
s += f'<P>If you are new to this site, I recommend taking a look at the <A HREF="{u}">Getting Started</A> page.</P>'
if isdemo(request):
if df.shape[0] == 0:
s += """<P>You are running this in demo mode (ie without a key), which is designed to show you the functionality with
data from a made up user. Unfortunately for some reason, the server is missing the data for the made up user. Please try
again later. Alternatively, you can load your own data from Octopus or n3rgy, by selecting the option from the dropdown list below: </P>
"""
else:
s += """<P>You are running this in demo mode (ie without a key), which is designed to show you the functionality with
electricity consumption data from a made up user. You can try out the functionality using this demo data.
Alternatively, you can load your own data from Octopus or n3rgy (which can include gas consumption or electricity export),
by selecting the option from the dropdown list below. </P>
"""
s += formforkeys(request)
else:
if df.shape[0] == 0:
s += f"""<P>The server no longer has the data for this user (it is deleted within 7 days of last being accessed).
You can load new data from Octopus or n3rgy on the <A HREF="{urladmin}">Admin Page</A>. </P>
"""
else:
s += """<P>The server has the following data for this user:</P>"""
s += homepagewhatwehave(request, df)
s += '</DIV>'
output = create_sm_page(request, s, 'Welcome')
return output
def costPage(request, choice):
type_id = type_ids[choice]
prefix = ['','gas','export'][type_id]
smid = get_sm_id(request)
vat = 1 if type_id==2 else 1.05
isfixed, pricestr = parsetariff(request, request.GET.get(prefix+'tariff'), type_id, vat)
has_pricebands = isfixed & (type_id==0)
start = request.GET.get('start', '2018-01-01')
end = request.GET.get('end','2025-01-01')
if 'month' in request.GET:
month = request.GET.get('month')
if 'day' in request.GET:
day = int(request.GET.get('day'))
start = month + '-{:02d}'.format(day)
end = start
endstr = """
select local_date, local_time as local_time_start, timezone_adj, quantity as total_quantity,
value as price, quantity*value as total_cost
from fulldata order by period"""
else:
if has_pricebands:
endstr = f"""
select local_date as day, count(period_id) as numperiods, sum(quantity) as total_quantity,
sum(value)/count(value) as price, sum(quantity*value) as total_cost
from fulldata where date_trunc('month', local_date)='{month}-01'
group by local_date, value order by local_date"""
else:
endstr = f"""
select local_date as day, count(period_id) as numperiods, sum(quantity) as total_quantity,
sum(value)/count(value) as price, sum(quantity*value) as total_cost
from fulldata where date_trunc('month', local_date)='{month}-01'
group by local_date order by local_date"""
else:
if has_pricebands:
endstr = f"""
select date_trunc('month',local_date) as month, count(period_id) as numperiods,
sum(value)/count(value) as price, sum(quantity) as total_quantity, sum(quantity*value) as total_cost
from fulldata group by month, value order by month"""
else:
endstr = f"""
select date_trunc('month',local_date) as month, count(period_id) as numperiods,
sum(value)/count(value) as price, sum(quantity) as total_quantity, sum(quantity*value) as total_cost
from fulldata group by month order by month"""
s = f'''
with periods as (select * from sm_periods where local_date between '{start}' and '{end}' )
, prices as ({pricestr} )
, quantities as ({quantitystr(smid, type_id)})
, fulldata as
(select periods.*, quantities.quantity, prices.value
from periods inner join quantities on periods.period_id=quantities.period_id
inner join prices on periods.period_id=prices.period_id)
{endstr}
'''
data = loadDataFromDb(s, returndf=True)
url = request.get_full_path()
type = ['electricity cost','gas cost','export revenue'][type_id]
costrev = 'revenue' if type_id==2 else 'cost'
if data.shape[0]==0:
return nodata(request)
if type_id==1:
gasmult = float(request.GET.get('gasmult', '1'))
data['total_quantity']*=gasmult
data['total_cost']*=gasmult
if gasmult == 1:
gaswarn = '<BR><P><B>These results do not include any <A HREF="https://www.theenergyshop.com/guides/how-to-convert-gas-units-to-kwh" target="_blank">gas conversion from m3 to kwh</A>. If your meter is SMETS2, you should most likely include a parameter gasmult=11.18 or thereabouts.</B>'
elif 10<gasmult<12:
gaswarn = '<BR><P>These results are based on a gas conversion of {}kwh per m3. This factor can be adjusted in the url. This conversion should not be applied if your meter is SMETS1.</P>'.format(gasmult)
else:
gaswarn = '<BR><B>These results are based on a gas conversion of {}kwh per m3. This factor appears wrong.</B> It <A HREF="https://www.theenergyshop.com/guides/how-to-convert-gas-units-to-kwh" target="_blank"> should be around 11.18</A>, based on a volume correction factor of 1.02264, a calorific value of about 40, and dividing by the kwh to joule conversion of 3.6. Your latest bill should show the applicable conversions.'.format(gasmult)
else: gaswarn = ''
if has_pricebands:
data['price'] = data['price'].astype(float)
if 'day' not in request.GET:
if 'month' not in request.GET:
volumes_by_price = data.groupby(['month','price']).sum().total_quantity.unstack()
data = data.groupby('month').sum().reset_index()
else:
volumes_by_price = data.groupby(['day','price']).sum().total_quantity.unstack()
data = data.groupby('day').sum().reset_index()
if len(volumes_by_price.columns)>1:
volumes_by_price.rename(columns={x: x*vat for x in volumes_by_price.columns}, inplace=True)
#volumes_by_price.loc[:,:] = volumes_by_price.values / volumes_by_price.sum(axis=1).values.reshape(-1,1)
else:
has_pricebands = False
data['price'] = np.where(data.total_quantity==0, data.price, data.total_cost/data.total_quantity).astype(float)
data['total_cost'] *= vat
data['price'] *= vat
if 'month' in request.GET:
month = request.GET.get('month')
if 'day' in request.GET:
day = int(request.GET.get('day'))
heading = 'Half-hourly {} on {}-{:02d}'.format(type.title(), month, day)
navbar = getnavbar(request)
description = f'{costrev.title()} in pence for each half hour (labels are start-times, in local time). '
labels = str(data['local_time_start'].tolist())
cost = str(['{:.2f}'.format(x) for x in data['total_cost'].values])
table = '<TABLE><TR><TH>Total Quantity (kwh)</TH><TD>{:.2f}</TD></TR>'.format(data.total_quantity.sum())
table += '<TR><TH>Total {} (£)</TH><TD>{:.2f}</TD></TR>'.format(costrev.title(), data.total_cost.sum()/100)
avg = data.total_cost.sum()/data.total_quantity.sum()
table += '<TR><TH>Average Price (p/kwh)</TH><TD>{:.2f}</TD></TR></TABLE>'.format(avg)
table += f'<BR><TABLE><TR><TH>Period Start</TH><TH>Quantity</TH><TH>Price</TH><TH>{costrev.title()}</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD>{}</TD><TD>{:.3f}</TD><TD>{:.2f}</TD><TD>{:.2f}</TD></TR>'
table += t.format(j.local_time_start, j.total_quantity, j.price, j.total_cost )
table += '</TABLE>'
else:
heading = 'Daily {} for {}'.format(type.capitalize(), month)
navbar = getnavbar(request)
description = f"{costrev.title()} (in £) for each day. "
labels = str([x.strftime('%d %b') for x in data['day'].tolist()])
cost = str(['{:.2f}'.format(x/100) for x in data.total_cost.tolist()])
table = '<TABLE><TR><TH>Monthly Quantity (kwh)</TH><TD>{:.1f}</TD></TR>'.format(data.total_quantity.sum())
table += '<TR><TH>Total {} (£)</TH><TD>{:.2f}</TD></TR>'.format(costrev.title(), data.total_cost.sum()/100)
avg = 48*data.total_cost.sum()/data.numperiods.sum()/100
table += '<TR><TH>Avg Daily {} (£)</TH><TD>{:.2f}</TD></TR>'.format(costrev.title(), avg)
avg = 48*(data.total_quantity).sum()/data.numperiods.sum()
table += '<TR><TH>Avg Daily Quantity (kwh)</TH><TD>{:.2f}</TD></TR>'.format(avg)
avg = data.total_cost.sum()/data.total_quantity.sum()
table += '<TR><TH>Avg Price (p/kwh)</TH><TD>{:.2f}</TD></TR>'.format(avg)
if has_pricebands:
for p in volumes_by_price.columns:
table += f'<TR><TH>Pct Qty at {p}p/kWh</TH><TD>{volumes_by_price[p].sum()/volumes_by_price.values.sum():.0%}</TD></TR>'
table += '</TABLE>'
table += f'<BR><TABLE><TR><TH>Day</TH><TH>Quantity</TH><TH>{costrev.title()}</TH><TH>Average<BR>Price</TH>'
if has_pricebands:
for col in volumes_by_price.columns:
table += f'<TH>Qty at <BR>{col}</TH>'
table += '</TR>'
for i, j in data.iterrows():
t = '<TR><TD><A HREF="{}">{}</A></TD><TD>{:.3f}</TD><TD>{:.3f}</TD><TD>{:.3f}</TD>'
tablerow = t.format(adj_url(url, [],[('day',j.day.day)]),
j.day.strftime('%a %b %d'), j.total_quantity, j.total_cost/100, j.price)
if has_pricebands:
for col in volumes_by_price.columns:
tablerow += '<TD>{:.1%}</TD>'.format(volumes_by_price[col].iloc[i]/volumes_by_price.iloc[i].sum())
table += tablerow + '</TR>'
table += '</TABLE>'
else:
heading = 'Monthly {}'.format(type.capitalize())
navbar = getnavbar(request)
description = f"{costrev.title()} (in £) for each month. "
labels = str([x.strftime('%b-%Y') for x in data['month'].tolist()])
cost = str(['{:.2f}'.format(x/100) for x in data['total_cost'].tolist()])
table = '<TABLE><TR><TH>Total Quantity (kwh)</TH><TD>{:.1f}</TD></TR>'.format(data.total_quantity.sum())
table += '<TR><TH>Total {} (£)</TH><TD>{:.2f}</TD></TR>'.format(costrev.title(), data.total_cost.sum()/100)
avg = 48*data.total_cost.sum()/data.numperiods.sum()/100
table += '<TR><TH>Avg Daily {} (£)</TH><TD>{:.2f}</TD></TR>'.format(costrev.title(), avg)
avg = 48*(data.total_quantity).sum()/data.numperiods.sum()
table += '<TR><TH>Avg Daily Quantity (kwh)</TH><TD>{:.2f}</TD></TR>'.format(avg)
avg = data.total_cost.sum()/data.total_quantity.sum()
table += '<TR><TH>Avg Price (p/kwh)</TH><TD>{:.2f}</TD></TR>'.format(avg)
if has_pricebands:
for p in volumes_by_price.columns:
table += f'<TR><TH>Pct Qty at {p}p/kWh</TH><TD>{volumes_by_price[p].sum()/volumes_by_price.values.sum():.1%}</TD></TR>'
table += '</TABLE>'
table += f'<BR><TABLE><TR><TH>Month</TH><TH>Total<BR>Quantity</TH><TH>Total<BR>{costrev.title()}</TH>'
table += f'<TH>Daily<BR>{costrev.title()}</TH><TH>Daily<BR>Quantity</TH><TH>Average<BR>Price</TH>'
if has_pricebands:
for col in volumes_by_price.columns:
table += f'<TH>Qty at <BR>{col}</TH>'
table += '</TR>'
for i, j in data.iterrows():
t = '<TR><TD><A HREF="{}">{}</A></TD><TD>{:.3f}</TD><TD>{:.3f}</TD><TD>{:.3f}</TD><TD>{:.3f}</TD><TD>{:.3f}</TD>'
dq = 48*(j.total_quantity/j.numperiods)
dc = 48*(j.total_cost/j.numperiods)/100
tablerow = t.format(adj_url(url, [],[('month',j.month.strftime('%Y-%m'))]),
j.month.strftime('%b %Y'), j.total_quantity, j.total_cost/100, dc, dq, j.price)
if has_pricebands:
for col in volumes_by_price.columns:
tablerow += '<TD>{:.1%}</TD>'.format(volumes_by_price[col].iloc[i]/volumes_by_price.iloc[i].sum())
table += tablerow + '</TR>'
table += '</TABLE>'
if type_id!=2:
description += 'All costs include 5% VAT and exclude standing charges. '
if 'chartscale' in request.GET:
c = request.GET.get('chartscale').split(',')
chartscale = f'min: {c[0]}, '
if len(c)>1:
chartscale += f'max: {c[1]}, '
else:
chartscale = 'suggestedMin: 0'
with open(os.path.dirname(os.path.realpath(__file__)) + "/templates/chart_template.html", "r") as f:
inner = f.read()
kwargs = {'navbar': navbar + gaswarn,
'description': description,
'chartlabels': labels,
'quantity': cost,
'table': table,
'chartscale': chartscale,
}
for k, v in kwargs.items():
inner = inner.replace('{' + k + '}', v)
output = create_sm_page(request, inner, heading)
return output
def consumptionPage(request, choice):
smid = get_sm_id(request)
type_id = type_ids[choice]
start = request.GET.get('start', '2018-01-01')
end = request.GET.get('end','2025-01-01')
if 'month' in request.GET:
month = request.GET.get('month')
if 'day' in request.GET:
day = int(request.GET.get('day'))
start = month + '-{:02d}'.format(day)
end = start
endstr = """
select local_date, local_time as local_time_start, timezone_adj, quantity as total_quantity
from fulldata order by period"""
else:
endstr = f"""
select local_date as day, count(period_id) as numperiods, sum(quantity) as total_quantity
from fulldata where date_trunc('month', local_date)='{month}-01'
group by local_date order by local_date"""
else:
endstr = f"""
select date_trunc('month',local_date) as month, count(period_id) as numperiods,
sum(quantity) as total_quantity
from fulldata group by month order by month"""
s = f'''
with periods as (select * from sm_periods where local_date between '{start}' and '{end}' )
, quantities as ({quantitystr(smid, type_id)})
, fulldata as
(select periods.*, quantities.quantity
from periods inner join quantities on periods.period_id=quantities.period_id)
{endstr}
'''
data = loadDataFromDb(s, returndf=True)
url = request.get_full_path()
type = ['electricity consumption','gas consumption','electricity export'][type_id]
if data.shape[0]==0:
return nodata(request)
if type_id==1:
gasmult = float(request.GET.get('gasmult', '1'))
data['total_quantity']*=gasmult
if gasmult == 1:
gaswarn = '<BR><P><B>These results do not include any <A HREF="https://www.theenergyshop.com/guides/how-to-convert-gas-units-to-kwh" target="_blank">gas conversion from m3 to kwh</A>. If your meter is SMETS2, you should most likely include a parameter gasmult=11.18 or thereabouts.</B>'
elif 10<gasmult<12:
gaswarn = '<BR><P>These results are based on a gas conversion of {}kwh per m3. This factor can be adjusted in the url. This conversion should not be applied if your meter is SMETS1.</P>'.format(gasmult)
else:
gaswarn = '<BR><B>These results are based on a gas conversion of {}kwh per m3. This factor appears wrong.</B> It <A HREF="https://www.theenergyshop.com/guides/how-to-convert-gas-units-to-kwh" target="_blank"> should be around 11.18</A>, based on a volume correction factor of 1.02264, a calorific value of about 40, and dividing by the kwh to joule conversion of 3.6. Your latest bill should show the applicable conversions.'.format(gasmult)
else:
gaswarn = ''
if 'month' in request.GET:
month = request.GET.get('month')
if 'day' in request.GET:
day = int(request.GET.get('day'))
heading = 'Half-hourly {} on {}-{:02d}'.format(type.title(), month, day)
navbar = getnavbar(request)
description = f'{type.capitalize()} for each half hour (labels are start-times, in local time)'
labels = str(data['local_time_start'].tolist())
quantity = str(['{:.2f}'.format(x) for x in data['total_quantity'].values])
table = '<TABLE><TR><TH>Total Quantity</TH><TD>{:.3f}</TD></TR></TABLE>'.format(data.total_quantity.sum())
table += '<BR><TABLE><TR><TH>Period Start</TH><TH>Quantity</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD>{}</TD><TD>{:.3f}</TD></TR>'
table += t.format(j.local_time_start, j.total_quantity)
table += '</TABLE>'
else:
heading = 'Daily {} for {}'.format(type.title(), month)
navbar = getnavbar(request)
description = f"{type.capitalize()} (in KWh) for each day"
labels = str([x.strftime('%d %b') for x in data['day'].tolist()])
quantity = str(['{:.2f}'.format(x) for x in data.total_quantity.tolist()])
table = '<TABLE><TR><TH>Total Quantity</TH><TD>{:.3f}</TD></TR>'.format(data.total_quantity.sum())
avg = 48*(data.total_quantity).sum()/data.numperiods.sum()
table += '<TR><TH>Avg Daily Quantity</TH><TD>{:.3f}</TD></TR></TABLE>'.format(avg)
table += '<BR><TABLE><TR><TH>Day</TH><TH>Quantity</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD><A HREF="{}">{}</A></TD><TD>{:.3f}</TD></TR>'
table += t.format(adj_url(url, [],[('day',j.day.day)]),
j.day.strftime('%a %b %d'), j.total_quantity)
table += '</TABLE>'
else:
heading = 'Monthly {}'.format(type.title())
navbar = getnavbar(request)
description = f"Average daily {type.capitalize()} (in KWh) for each month:"
labels = str([x.strftime('%b-%Y') for x in data['month'].tolist()])
quantity = 48*data['total_quantity']/data['numperiods']
quantity = str(['{:.2f}'.format(x) for x in quantity.tolist()])
table = '<TABLE><TR><TH>Total Quantity</TH><TD>{:.3f}</TD></TR>'.format(data.total_quantity.sum())
avg = 48*data.total_quantity.sum()/data.numperiods.sum()
table += '<TR><TH>Avg Daily Quantity</TH><TD>{:.3f}</TD></TR></TABLE>'.format(avg)
table += '<BR><TABLE><TR><TH>Month</TH><TH>Total<BR>Quantity</TH><TH>Daily<BR>Quantity</TR>'
for _, j in data.iterrows():
t = '<TR><TD><A HREF="{}">{}</A></TD><TD>{:.3f}</TD><TD>{:.3f}</TD></TR>'
avg = 48*(j.total_quantity/j.numperiods)
table += t.format(adj_url(url, [],[('month',j.month.strftime('%Y-%m'))]),
j.month.strftime('%b %Y'), j.total_quantity, avg)
table += '</TABLE>'
if 'chartscale' in request.GET:
c = request.GET.get('chartscale').split(',')
chartscale = f'min: {c[0]}, '
if len(c)>1:
chartscale += f'max: {c[1]}, '
else:
chartscale = 'suggestedMin: 0'
with open(os.path.dirname(os.path.realpath(__file__)) + "/templates/chart_template.html", "r") as f:
inner = f.read()
kwargs = {'navbar': navbar + gaswarn,
'description': description,
'chartlabels': labels,
'quantity': quantity,
'table': table+'<BR>',
'chartscale': chartscale,
}
for k, v in kwargs.items():
inner = inner.replace('{' + k + '}', v)
output = create_sm_page(request, inner, heading)
return output
, local_date date not null
, value float(8) not null);
"""
cur.execute(s)
conn.commit()
from myutils.utils import loadDataFromDb
if False: #Creates daily tracker variables
product = 'SILVER-2017-1'
for region in ['A','B','C','D','E','F','G','H','J','K','L','M','N','P']:
s = f"""
insert into sm_d_variables (var_name) values ('{product}-{region}') returning var_id; """
var_id = loadDataFromDb(s)[0][0]
print(var_id)
s = f"""
insert into sm_tariffs (product, region, var_id, type_id, granularity_id) values
('{product}', '{region}', {var_id}, 1, 1); """
loadDataFromDb(s)
if False:
product = 'SILVER-2017-1'
for region in ['A','B','C','D','E','F','G','H','J','K','L','M','N','P']:
s = f"select var_id from sm_variables where product='{product}' and region='{region}' ;"
var_id = loadDataFromDb(s)[0][0]
r = requests.get(f'https://octopus.energy/api/v1/tracker/G-1R-SILVER-2017-1-{region}/daily/past/540/1/')
dates = [x['date'] for x in r.json()['periods']]
else:
vals = [x['intensity']['actual'] for x in j ]
return j[-1]['to'], pd.DataFrame(vals, index=[x['from'] for x in j])
try:
t = (pd.Timestamp.now() - pd.offsets.Day(2)).strftime('%Y-%m-%d')
s = f'''
delete from sm_hh_variable_vals where id in
(select v.id
from sm_periods p inner join sm_hh_variable_vals v on p.period_id=v.period_id
inner join sm_variables t on t.var_id=v.var_id and t.product='CO2_National'
where p.period> '{t}')
'''
df = loadDataFromDb(s, returndf=True)
s = """
select s.var_id, s.product, s.region, max(sm_periods.period_id) as period_id, max(period) as period
from sm_variables s
left join sm_hh_variable_vals on s.var_id=sm_hh_variable_vals.var_id
left join sm_periods on sm_periods.period_id=sm_hh_variable_vals.period_id
where s.product='CO2_National'
group by s.var_id, s.product, s.region;
"""
data = loadDataFromDb(s)[0]
latest = data[4]
var_id = data[0]
def checkRequest(request):
choice = request.path_info.split('/')[-1]
url = request.get_full_path()
smid = get_sm_id(request)
if choice == '':
if '?' in url:
url = url.replace('?', 'home?')
else:
url = url + 'home'
return redirect(url)
elif choice in ['sm', 'smartmeter']:
url = url.replace(choice, f'{choice}/home')
return redirect(url)
if 'MAC' in request.GET:
return redirect(
adj_url(url, ['MAC'], [('n3rgy', request.GET.get('MAC'))]))
if isdemo(request):
if ('tariff' not in request.GET) and (choice in ['cost']):
u = adj_url(url, [], [('tariff', '15')])
return redirect(u)
if choice in [
'home', 'admin', 'info', 'getstarting', 'checks', 'logpage',
'gastracker', 'electracker', 'other', 'memory', 'octobill',
'octoaccount', 'customprofile'
]:
return choice
type_id, type_label = get_type_id(choice)
if type_id == -1:
content = """
<P>This functionality is not yet implemented. Please try another page from the menu or check back later.</P>"""
return create_sm_page(request, content, 'Invalid Page')
if smid is not None:
s = f"select account_id from sm_accounts where session_id= '{smid}' and active='1'"
account_id = loadDataFromDb(s)
else:
account_id = []
if len(account_id) == 0:
adminurl = url.replace(choice, 'admin', 1)
heading = 'No data stored for this user'
content = f"""
<P>There is no data currently stored for you. Please visit the <A HREF="{adminurl}">Admin Page</A> to reload data. </P>"""
return create_sm_page(request, content, heading)
if choice in ['cost', 'gascost', 'exportrevenue']:
prefix = ['', 'gas', 'export'][type_id]
tariff = request.GET.get(prefix + 'tariff', None)
if tariff is None:
return getTariff(request, choice)
else:
isfixed = tariff.replace(',', '').replace(':', '').replace(
'-', '').replace('.', '').isnumeric()
if (isfixed == 0):
if 'region' not in request.GET:
return getTariff(request, choice)
region = request.GET.get('region')
s = f"select var_id, granularity_id from sm_variables where product='{tariff}' and region='{region}' and type_id={type_id}"
s = loadDataFromDb(s)
if len(s) == 0:
return getTariff(request, choice)
return choice
def inner(request):
region = request.GET.get('region', 'W').upper()
before = request.GET.get('before', pd.Timestamp.now().isoformat())
ph = ('ph' in request.GET)
s = f'''
with latest as (select datetime, max(created_on) created_on from price_forecast
where datetime>=date_trunc('day', TIMESTAMP '{before}') and created_on<'{before}'
group by datetime)
select latest.datetime, demand, solar, wind, price, latest.created_on
from price_forecast inner join latest on price_forecast.datetime=latest.datetime
and price_forecast.created_on=latest.created_on
order by latest.datetime;'''
data = loadDataFromDb(s, returndf=True)
if region == 'W':
df2 = get_prices('C', 'AGILE-18-02-21', data['datetime'].iloc[0])
df3 = get_prices('C', 'AGILE-OUTGOING-19-05-13',
data['datetime'].iloc[0])
df2 = df2 - np.where(df2.index.str[-5:-3].isin(['16', '17', '18']), 12,
0)
df2 = df2 * 10 / 2
df3 = df3 - np.where(df3.index.str[-5:-3].isin(['16', '17', '18']),
5.93 + 1.3, 1.3)
df3 = df3 * 10 / 0.95
df2 = pd.Series(np.where(df3 > df2, df3, df2), index=df2.index)
else:
df2 = get_prices(region, 'AGILE-18-02-21', data['datetime'].iloc[0])
df2 *= 1.05
if ph:
hrs = data['datetime'].astype(str).str[11:13]
data = data[(hrs >= '08') & (hrs < '20')]
#raise Exception(data['datetime'].astype(str).str[:16], df2.index)
df2 = df2[df2.index.isin(data['datetime'].astype(str).str[:16])]
#raise Exception(data['datetime'].astype(str).str[11:13])
#
with open(
os.path.dirname(os.path.realpath(__file__)) + "/template.html",
"r") as f:
template = f.read()
if region == 'W':
prices = str(['{:.2f}'.format(d.price) for _, d in data.iterrows()])
retail = "The prices are essentially estimates of day ahead hourly auction results in £/MWh."
retail2 = "Estimates of hourly day ahead prices in £/MWh"
else:
multipliers = {
'A': 2.1,
'B': 2,
'C': 2,
'D': 2.2,
'E': 2.1,
'F': 2.1,
'G': 2.1,
'H': 2.1,
'J': 2.2,
'K': 2.2,
'L': 2.3,
'M': 2,
'N': 2.1,
'P': 2.4
}
adders = {
'A': 13,
'B': 14,
'C': 12,
'D': 13,
'E': 12,
'F': 12,
'G': 12,
'H': 12,
'J': 12,
'K': 12,
'L': 11,
'M': 13,
'N': 13,
'P': 12
}
multiplier = multipliers[region]
adder = adders[region]
data.price *= multiplier / 10
data.price += np.where(
data['datetime'].astype(str).str[-8:-6].isin(['16', '17', '18']),
adder, 0)
data.price *= 1.05
prices = str(['{:.2f}'.format(d.price) for _, d in data.iterrows()])
retail = f"The prices are essentially estimates of the day ahead hourly auction results in p/kwh, converted into retail prices for region {region} by multiplying by {multiplier}, and adding {adder} from 4-7pm. Prices include 5% VAT."
retail = retail.format(region, multiplier, adder)
retail2 = f"Estimates of Octopus retail prices for region {region} in p/kwh"
url = request.build_absolute_uri()
if 'before' not in url:
if "?" in url:
url = url + '&before=2020-12-31T12:00'
else:
url = url + '?before=2020-12-31T12:00'
retail += f'''<P>If you want to know how historic forecasts performed, you can see the latest forecast before a past datetime, eg {url}</P>'''
if 'json' in request.GET:
myobj = []
for _, j in data.iterrows():
myobj.append({
'datetime': j['datetime'].strftime('%Y-%m-%dT%H:%M'),
'demand': j['demand'] / 1000,
'solar': j['solar'] / 1000,
'wind': j['wind'] / 1000,
'price': j['price']
})
return json.dumps(myobj)
kwargs = {
'asof':
data['created_on'].iloc[-1].isoformat()[:16],
'datetimes':
str([d['datetime'].strftime('%a %Hh') for _, d in data.iterrows()]),
'demand':
str(['{:.2f}'.format(d['demand'] / 1000) for _, d in data.iterrows()]),
'solar':
str(['{:.2f}'.format(d['solar'] / 1000) for _, d in data.iterrows()]),
'wind':
str(['{:.2f}'.format(d['wind'] / 1000) for _, d in data.iterrows()]),
'prices':
prices,
'actual':
str(['{:.2f}'.format(d) for d in df2.values]),
'retail':
retail,
'retail2':
retail2
}
for k, v in kwargs.items():
template = template.replace('{' + k + '}', v)
return template
def emissionsPage(request):
smid = get_sm_id(request)
url = request.get_full_path()
start = request.GET.get('start', '2018-01-01')
end = request.GET.get('end','2025-01-01')
if 'month' in request.GET:
month = request.GET.get('month')
if 'day' in request.GET:
day = int(request.GET.get('day'))
start = month + '-{:02d}'.format(day)
endstr = f"""
select period local_date, local_time, prof_qty*intensity prof_emis,
prof_qty, actual_qty*intensity actual_emis, actual_qty
from fulldata where local_date='{start}'
order by period"""
else:
endstr = f"""
select local_date, sum(prof_qty*intensity) prof_emis,
sum(prof_qty) prof_qty, sum(actual_qty*intensity) actual_emis, sum(actual_qty) actual_qty
from fulldata
where date_trunc('month', local_date)='{month}-01'
group by local_date
order by local_date"""
else:
endstr = """
select date_trunc('month', local_date) as month, sum(prof_qty*intensity) prof_emis,
sum(prof_qty) prof_qty, sum(actual_qty*intensity) actual_emis, sum(actual_qty) actual_qty
from fulldata
group by month
order by month"""
s = f'''
with periods as (select * from sm_periods where local_date between '{start}' and '{end}' )
, quantities as ({quantitystr(smid, 0)})
, emis as (select period_id, value from sm_hh_variable_vals v
inner join sm_variables var on v.var_id=var.var_id and var.product='CO2_National')
, prof as (select period_id, value from sm_hh_variable_vals v
inner join sm_variables var on v.var_id=var.var_id and var.product='Profile_1')
, fulldata as
(select periods.*, emis.value intensity, prof.value prof_qty, coalesce(quantities.quantity, 0) actual_qty
from periods inner join emis on periods.period_id=emis.period_id
inner join prof on periods.period_id=prof.period_id
left outer join quantities on periods.period_id=quantities.period_id)
{endstr}
'''
data = loadDataFromDb(s, returndf=True)
if data.shape[0]==0:
return nodata(request)
data['prof_intensity'] = data.prof_emis/data.prof_qty
data['actual_intensity'] = np.where(data.actual_qty==0, data.prof_intensity, data.actual_emis/data.actual_qty)
if 'month' in request.GET:
month = request.GET.get('month')
if 'day' in request.GET:
data['prof_emis'] = data.prof_emis*data.actual_qty.sum()/data.prof_qty.sum()
data['prof_qty'] = data.prof_qty*data.actual_qty.sum()/data.prof_qty.sum()
day = int(request.GET.get('day'))
heading = 'Half-hourly Carbon Intensity on {}-{:02d}'.format(month, day)
navbar = getnavbar(request)
description = 'Carbon intensity for average domestic profile and this customer in g/kwh for each half hour (labels are start-times, in local time). '
labels = str(data['local_time'].tolist())
profile = str(['{:.2f}'.format(x) for x in data['prof_qty'].values])
actual = str(['{:.2f}'.format(x) for x in data['actual_qty'].values])
intensity = str(['{:.2f}'.format(x) for x in data['prof_intensity'].values])
avg = data.prof_emis.sum()/data.prof_qty.sum()
table = '<TABLE><TR><TH>Average Domestic Intensity (g/kwh)</TH><TD>{:.1f}</TD></TR>'.format(avg)
if data.actual_qty.sum()>0:
avg = data.actual_emis.sum()/data.actual_qty.sum()
table += '<TR><TH>Your Intensity (g/kwh)</TH><TD>{:.1f}</TD></TR>'.format(avg)
table += '<TR><TH>Your Emissions (kg)</TH><TD>{:.2f}</TD></TR></TABLE>'.format(data.actual_emis.sum()/1000)
table += '<BR><TABLE><TR><TH>Period Start</TH><TH>Intensity</TH><TH>Avg Domestic<BR>Consumption</TH><TH>Your<BR>Consumption</TH><TH>Your<BR>Emissions</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD>{}</TD><TD>{:.0f}</TD><TD>{:.2f}</TD><TD>{:.2f}</TD><TD>{:.2f}</TD></TR>'
table += t.format(j.local_time, j.prof_intensity, j.prof_qty, j.actual_qty, j.actual_emis/1000 )
table += '</TABLE>'
else:
heading = 'Daily Carbon Intensity for {}'.format( month)
navbar = getnavbar(request)
description = "Carbon intensity for average domestic profile and this customer in g/kwh for each day."
labels = str([x.strftime('%d %b') for x in data['local_date'].tolist()])
profile = str(['{:.2f}'.format(x) for x in data['prof_intensity'].values])
actual = str(['{:.2f}'.format(x) for x in data['actual_intensity'].values])
avg = data.prof_emis.sum()/data.prof_qty.sum()
table = '<TABLE><TR><TH>Average Domestic Intensity (g/kwh)</TH><TD>{:.1f}</TD></TR>'.format(avg)
if data.actual_qty.sum()>0:
avg = data.actual_emis.sum()/data.actual_qty.sum()
table += '<TR><TH>Your Intensity (g/kwh)</TH><TD>{:.1f}</TD></TR>'.format(avg)
table += '<TR><TH>Your Consumption (kwh)</TH><TD>{:.1f}</TD></TR>'.format(data.actual_qty.sum())
table += '<TR><TH>Your Emissions (kg)</TH><TD>{:.1f}</TD></TR></TABLE>'.format(data.actual_emis.sum()/1000)
table += '<BR><TABLE><TR><TH>Day</TH><TH>Avg Domestic Intensity</TH><TH>Your Intensity</TH><TH>Your Consumption</TH><TH>Your Emissions</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD><A HREF="{}">{}</A></TD><TD>{:.1f}</TD><TD>{:.1f}</TD><TD>{:.1f}</TD><TD>{:.1f}</TD></TR>'
table += t.format(adj_url(url, [],[('day',j.local_date.day)]),
j.local_date.strftime('%a %b %d'), j.prof_intensity, j.actual_intensity, j.actual_qty, j.actual_emis/1000)
table += '</TABLE>'
else:
heading = 'Monthly Carbon Intensity'
navbar = getnavbar(request)
description = "Carbon intensity for average domestic profile and this customer in g/kwh for each month"
labels = str([x.strftime('%b-%Y') for x in data['month'].tolist()])
profile = str(['{:.1f}'.format(x) for x in data['prof_intensity'].values])
actual = str(['{:.1f}'.format(x) for x in data['actual_intensity'].values])
avg = data.prof_emis.sum()/data.prof_qty.sum()
table = '<TABLE><TR><TH>Average Domestic Intensity (g/kwh)</TH><TD>{:.1f}</TD></TR>'.format(avg)
if data.actual_qty.sum()>0:
avg = data.actual_emis.sum()/data.actual_qty.sum()
table += '<TR><TH>Your Intensity (g/kwh)</TH><TD>{:.1f}</TD></TR>'.format(avg)
table += '<TR><TH>Your Consumption (kwh)</TH><TD>{:.1f}</TD></TR>'.format(data.actual_qty.sum())
table += '<TR><TH>Your Emissions (kg)</TH><TD>{:.1f}</TD></TR></TABLE>'.format(data.actual_emis.sum()/1000)
table += '<BR><TABLE><TR><TH>Month</TH><TH>Avg Domestic<BR>Intensity</TH><TH>Your<BR>Intensity</TH>'
table += '<TH>Your<BR>Consumption</TH><TH>Your Emissions</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD><A HREF="{}">{}</A></TD><TD>{:.1f}</TD><TD>{:.1f}</TD><TD>{:.1f}</TD><TD>{:.1f}</TD></TR>'
table += t.format(adj_url(url, [],[('month',j.month.strftime('%Y-%m'))]),
j.month.strftime('%b %Y'), j.prof_intensity, j.actual_intensity, j.actual_qty, j.actual_emis/1000)
table += '</TABLE>'
kwargs = {'navbar': navbar,
'description': description,
'chartlabels': labels,
'profile': profile,
'actual': actual,
'table': table + '<BR><BR>'
}
if 'day' in request.GET:
with open(os.path.dirname(os.path.realpath(__file__)) + "/templates/chart_emis2.html", "r") as f:
inner = f.read()
kwargs['intensity'] = intensity
else:
with open(os.path.dirname(os.path.realpath(__file__)) + "/templates/chart_emis.html", "r") as f:
inner = f.read()
for k, v in kwargs.items():
inner = inner.replace('{' + k + '}', v)
output = create_sm_page(request, inner, heading)
return output
def netimportPage(request):
smid = get_sm_id(request)
start = request.GET.get('start', '2019-01-01')
end = request.GET.get('end','2025-12-01')
if 'month' in request.GET:
month = request.GET.get('month')
if 'day' in request.GET:
day = int(request.GET.get('day'))
start = month + '-{:02d}'.format(day)
end = start
endstr = """
select local_date, local_time as local_time_start, timezone_adj, import as total_import, export as total_export
from fulldata order by period"""
else:
endstr = f"""
select local_date as day, count(period_id) as numperiods, sum(import) as total_import, sum(export) as total_export
from fulldata where date_trunc('month', local_date)='{month}-01'
group by local_date order by local_date"""
else:
endstr = f"""
select date_trunc('month',local_date) as month, count(period_id) as numperiods,
sum(import) as total_import,
sum(export) as total_export
from fulldata group by month order by month"""
s = f'''
with periods as (select * from sm_periods where local_date between '{start}' and '{end}' )
, quantities1 as ({quantitystr(smid, 0)})
, quantities2 as ({quantitystr(smid, 2)})
, full1 as
(select periods.*, quantities1.quantity
from periods inner join quantities1 on periods.period_id=quantities1.period_id order by 1)
, full2 as
(select periods.*, quantities2.quantity
from periods inner join quantities2 on periods.period_id=quantities2.period_id order by 1)
, fulldata as
(select full2.*, coalesce(full1.quantity,0) as import, coalesce(full2.quantity,0) as export
from full2 left outer join full1 on full2.period_id=full1.period_id)
{endstr}
'''
data = loadDataFromDb(s, returndf=True)
url = request.get_full_path()
if data.shape[0]==0:
return nodata(request)
#raise Exception(request.GET)
if 'month' in request.GET:
month = request.GET.get('month')
if 'day' in request.GET:
day = int(request.GET.get('day'))
heading = 'Half-hourly Net Import on {}-{:02d}'.format(month, day)
navbar = getnavbar(request)
description = f'Import and export for each half hour (in kWh, labels are start-times, in local time)'
labels = str(data['local_time_start'].tolist())
imports = str(['{:.2f}'.format(x) for x in data['total_import'].values])
exports = str(['{:.2f}'.format(x) for x in data['total_export'].values])
table = '<TABLE><TR><TH>Total Import</TH><TD>{:.3f}</TD></TR>'.format(data.total_import.sum())
table += '<TR><TH>Total Export</TH><TD>{:.3f}</TD></TR>'.format(data.total_export.sum())
table += '<TR><TH>Total Net Import</TH><TD>{:.3f}</TD></TR></TABLE>'.format(data.total_import.sum()-data.total_export.sum())
table += '<BR><TABLE><TR><TH>Period Start</TH><TH>Import</TH><TH>Export</TH><TH>Net Import</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD>{}</TD><TD>{:.3f}</TD><TD>{:.3f}</TD><TD>{:.3f}</TD></TR>'
table += t.format(j.local_time_start, j.total_import, j.total_export, j.total_import-j.total_export)
table += '</TABLE>'
else:
heading = 'Daily Net Import for {}'.format(month)
navbar = getnavbar(request)
description = f"Daily import and export (in kWh) for each day"
labels = str([x.strftime('%d %b') for x in data['day'].tolist()])
imports = str(['{:.2f}'.format(x) for x in data.total_import.tolist()])
exports = str(['{:.2f}'.format(x) for x in data.total_export.tolist()])
table = '<TABLE><TR><TH>Total Import</TH><TD>{:.3f}</TD></TR>'.format(data.total_import.sum())
table += '<TR><TH>Total Export</TH><TD>{:.3f}</TD></TR>'.format(data.total_export.sum())
table += '<TR><TH>Total Net Import</TH><TD>{:.3f}</TD></TR></TABLE>'.format(data.total_import.sum()-data.total_export.sum())
table += '<BR><TABLE><TR><TH>Day</TH><TH>Import</TH><TH>Export</TH><TH>Net Import</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD><A HREF="{}">{}</A></TD><TD>{:.3f}</TD><TD>{:.3f}</TD><TD>{:.3f}</TD></TR>'
table += t.format(adj_url(url, [],[('day',j.day.day)]),
j.day.strftime('%a %b %d'), j.total_import, j.total_export, j.total_import-j.total_export)
table += '</TABLE>'
else:
heading = 'Monthly Net Import'
navbar = getnavbar(request)
description = f"Monthly import and export (in kWh) for each month:"
labels = str([x.strftime('%b-%Y') for x in data['month'].tolist()])
imports = str(['{:.2f}'.format(x) for x in data.total_import.tolist()])
exports = str(['{:.2f}'.format(x) for x in data.total_export.tolist()])
table = '<TABLE><TR><TH>Total Import</TH><TD>{:.3f}</TD></TR>'.format(data.total_import.sum())
table += '<TR><TH>Total Export</TH><TD>{:.3f}</TD></TR>'.format(data.total_export.sum())
table += '<TR><TH>Net Import</TH><TD>{:.3f}</TD></TR></TABLE>'.format(data.total_import.sum()-data.total_export.sum())
table += '<BR><TABLE><TR><TH>Month</TH><TH>Total<BR>Import</TH><TH>Total<BR>Export</TH><TH>Net<BR>Import</TH></TR>'
for _, j in data.iterrows():
t = '<TR><TD><A HREF="{}">{}</A></TD><TD>{:.3f}</TD><TD>{:.3f}</TD><TD>{:.3f}</TD></TR>'
table += t.format(adj_url(url, [],[('month',j.month.strftime('%Y-%m'))]),
j.month.strftime('%b %Y'), j.total_import, j.total_export, j.total_import-j.total_export)
table += '</TABLE>'
#raise Exception
if 'chartscale' in request.GET:
c = request.GET.get('chartscale').split(',')
chartscale = f'min: {c[0]}, '
if len(c)>1:
chartscale += f'max: {c[1]}, '
else:
chartscale = 'suggestedMin: 0'
with open(os.path.dirname(os.path.realpath(__file__)) + "/templates/chart_template3.html", "r") as f:
inner = f.read()
kwargs = {'navbar': navbar ,
'description': description,
'chartlabels': labels,
'imports': imports,
'exports': exports,
'table': table+'<BR>',
'chartscale': chartscale,
}
for k, v in kwargs.items():
inner = inner.replace('{' + k + '}', v)
output = create_sm_page(request, inner, heading)
return output
import traceback
errstr = ''
try:
s = '''
with sessions as (
select sm_accounts.account_id, sm_accounts.session_id, sm_accounts.type_id, last_updated, max(datetime) as last_called
from sm_accounts
left outer join sm_log on sm_accounts.session_id=sm_log.session_id
where sm_accounts.session_id != 'e4280c7d-9d06-4bbe-87b4-f9e106ede788' and sm_accounts.active='1'
group by account_id, last_updated)
update sm_accounts set active='0' where account_id in
(
select account_id from sessions where last_updated<CURRENT_TIMESTAMP-Interval '6 hours' or last_called<CURRENT_TIMESTAMP-Interval '3 hours' )
'''
loadDataFromDb(s, returndf=True)
#print(loadDataFromDb(s, returndf=True))
s = "delete from sm_quantity where account_id not in (select account_id from sm_accounts where active='1') "
loadDataFromDb(s, returndf=True)
#print(loadDataFromDb(s, returndf=True))
except Exception as err:
errstr += str(err)
errstr += traceback.format_exc()
email_script(errstr, 'smdelete', 0)
if len(errstr):
print(errstr)
