def get_coldest_day(self, house_id):
""" Get and store dict of coldest day from database. """
self.base_query = db_session.query(label('hdd', func.max(HDDDaily.hdd))).\
filter(HDDDaily.house_id == house_id)
sub_query = self.filter_query_by_date_range(HDDDaily)
min_hdd_day_query = db_session.query(HDDDaily.date, HDDDaily.hdd).\
filter(HDDDaily.hdd == sub_query).one()
self.json_coldest_day = {
'date': str(min_hdd_day_query.date),
'temperature': str(min_hdd_day_query.hdd)
}
def get_asof_date(house_id):
""" Return latest date of data as string for house X. """
asof = db_session.query(LimitsHourly.end_date). \
filter(LimitsHourly.house_id == house_id).one()
return str(asof.end_date.strftime("%Y-%m-%d"))
def get_totals(self, house_id):
""" Get and store totals from database. """
totals = db_session.query("cold", "hot", "main", "water_heater",
"water_pump")
date_range = self.filter_query_by_date_range_sql()
sql = """SELECT SUM(main.gallons) - SUM(hot.gallons) AS 'cold',
SUM(hot.gallons) AS 'hot', SUM(main.gallons) AS 'main',
SUM(e.water_heater) AS 'water_heater',
SUM(e.water_pump) AS 'water_pump'
FROM energy_monthly e
LEFT JOIN (SELECT house_id, date, gallons FROM water_monthly
WHERE device_id = 6) main ON e.date = main.date
AND main.house_id = e.house_id
LEFT JOIN (SELECT house_id, date, gallons FROM water_monthly
WHERE device_id = 7) hot ON e.date = hot.date
AND hot.house_id = e.house_id
WHERE e.house_id = :house_id
%s
""" % date_range
totals = totals.from_statement(text(sql))
totals = totals.params(house_id=house_id,
start=self.args['start'],
end=self.args['end']).one()
self.json_totals = {
'cold': str(totals.cold),
'hot': str(totals.hot),
'main': str(totals.main),
'water_heater': str(totals.water_heater),
'water_pump': str(totals.water_pump)
}
def get_circuit_all_year_month(self, house_id):
""" Get and store all circuit usage total for yearly or monthly. """
self.base_query = db_session.query(label('date',
func.min(EnergyMonthly.date)),
label('actual',
func.sum(EnergyMonthly.used)),
label('budget',
func.sum(EstimatedMonthly.used))
). \
outerjoin(EstimatedMonthly, and_(EnergyMonthly.date == EstimatedMonthly.date,
EnergyMonthly.house_id == EstimatedMonthly.house_id)).\
filter(EnergyMonthly.house_id == house_id)
self.filter_query_by_date_range(EnergyMonthly)
totals = self.base_query.one()
self.json_totals = {'actual': str(totals.actual),
'budget': str(totals.budget)}
items = self.group_query_by_interval(EnergyMonthly)
self.json_items = []
for item in items:
data = {'date': str(item.date),
'actual': str(item.actual),
'budget': str(item.budget)}
self.json_items.append(data)
self.json_circuit = {'circuit_id': 'all',
'name': self.get_circuit_info('all')['name'],
'description': self.get_circuit_info('all')['description']}
def get_asof_date(house_id):
""" Return latest date of data as string for house X. """
asof = db_session.query(LimitsHourly.end_date). \
filter(LimitsHourly.house_id == house_id).one()
return str(asof.end_date.strftime("%Y-%m-%d"))
def get_circuit_all_day_hour(self, house_id):
""" Get and store all circuit usage total for daily or hourly. """
self.base_query = db_session.query(label('actual',
func.sum(EnergyHourly.used)/1000)).\
filter(EnergyHourly.house_id == house_id)
self.base_query = self.base_query.\
add_columns(label('date', func.min(EnergyHourly.date)))
self.filter_query_by_date_range(EnergyHourly)
totals = self.base_query.one()
self.json_totals = {'actual': str(totals.actual)}
items = self.group_query_by_interval(EnergyHourly)
self.json_items = []
for item in items:
data = {'date': self.format_date(item.date),
'actual': str(item.actual)}
self.json_items.append(data)
self.json_circuit = {'circuit_id': 'all',
'name': self.get_circuit_info('all')['name'],
'description': self.get_circuit_info('all')['description']}
def get_coldest_hour(self, house_id):
""" Get and store dict of coldest hour from database. """
self.base_query = db_session.query(label('temperature',
func.min(TemperatureHourly.temperature))).\
filter(TemperatureHourly.house_id == house_id).\
filter(TemperatureHourly.device_id == 0) # outdoor device_id = 0
sub_query = self.filter_query_by_date_range(TemperatureHourly)
min_temperature_hour_query = db_session.query(TemperatureHourly.date,
TemperatureHourly.temperature).\
filter(TemperatureHourly.temperature == sub_query).first()
self.json_coldest_hour = {
'date': str(min_temperature_hour_query.date),
'temperature': str(min_temperature_hour_query.temperature)
}
def get_house_details(house_id):
""" Return details for house X in json. """
house = db_session.query(Houses). \
filter(Houses.house_id == house_id).one()
return {'name': house.name,
'sname': house.sname,
'id': house.house_id,
'devices': url_for('get_devices', house_id=house.house_id, _external=True),
'circuits': url_for('get_circuits', house_id=house.house_id, _external=True)}
def get_fields(self, house_id):
""" Get and store additional generation fields from database. """
tables = [EnergyHourly, EnergyDaily]
self.max_solar = []
for table in tables:
self.base_query = db_session.query(label('max_solar',
func.min(table.solar))).\
filter(table.house_id == house_id) # nested query
sub_query = self.filter_query_by_date_range(table)
max_solar_query = db_session.query(table.date,
label('max_solar', table.solar)).\
filter(table.solar == sub_query).first()
self.max_solar.append({
'date': str(max_solar_query.date),
'solar': str(max_solar_query.max_solar)
})
def get_devices_all(house_id):
""" Return array of devices for house X. """
devices = db_session.query(MonitorDevices). \
filter(Houses.house_id == house_id).all()
json_items = []
for device in devices:
data = {'name': device.name, 'id': device.device_id}
json_items.append(data)
return json_items
def get_years(house_id):
""" Return array of valid years of energy data for house X. """
years = db_session.query(EnergyMonthly.date). \
filter(EnergyMonthly.house_id == house_id). \
group_by(func.year(EnergyMonthly.date)). \
order_by(EnergyMonthly.date)
json_items = []
for year in years:
json_items.append(str(year[0].year))
return json_items
def get_limits(house_id):
""" Return deails of data limits for house X. Used mainlt in chart view. """
limits = db_session.query(LimitsHourly). \
filter(EnergyHourly.house_id == house_id).one()
return {'used_max': limits.used_max,
'solar_min': limits.solar_min,
'outdoor_deg_min': limits.outdoor_deg_min,
'outdoor_deg_max': limits.outdoor_deg_max,
'hdd_max': limits.hdd_max,
'start_date': str(limits.start_date),
'end_date': str(limits.end_date)}
def get_years(house_id):
""" Return array of valid years of energy data for house X. """
years = db_session.query(EnergyMonthly.date). \
filter(EnergyMonthly.house_id == house_id). \
group_by(func.year(EnergyMonthly.date)). \
order_by(EnergyMonthly.date)
json_items = []
for year in years:
json_items.append(str(year[0].year))
return json_items
def get_devices_all(house_id):
""" Return array of devices for house X. """
devices = db_session.query(MonitorDevices). \
filter(Houses.house_id == house_id).all()
json_items = []
for device in devices:
data = {'name': device.name,
'id': device.device_id}
json_items.append(data)
return json_items
def get_totals_day_hour(self, house_id):
""" Get and store daily or hourly totals. """
self.base_query = db_session.query(label('date', func.min(EnergyHourly.date)),
label('sum_actual',
func.sum(EnergyHourly.solar)/1000)).\
filter(EnergyHourly.house_id == house_id)
self.filter_query_by_date_range(EnergyHourly)
totals = self.base_query.one()
self.json_totals = {'actual': str(totals.sum_actual)}
def get_houses_all():
""" Return array of houses. """
houses = db_session.query(Houses).all()
json_items = []
for house in houses:
data = {'name': house.name,
'sname': house.sname,
'house_id': house.house_id,
'url': url_for('get_house', house_id=house.house_id, _external=True)}
json_items.append(data)
return json_items
def get_heating_season(self, house_id):
""" Set base query for heating season. """
self.base_query = db_session.query(label('total_ashp',
func.sum(EnergyDaily.ashp)),
label('total_hdd',
func.sum(HDDDaily.hdd))).\
filter(EnergyDaily.date == HDDDaily.date).\
filter(EnergyDaily.house_id == house_id).\
filter(EnergyDaily.ashp != None)
self.filter_query_by_date_range(EnergyDaily)
self.total_hdd_and_ashp_in_heating_season_query = \
self.filter_query_remove_summer_months(EnergyDaily).one()
def get_limits(house_id):
""" Return deails of data limits for house X. Used mainlt in chart view. """
limits = db_session.query(LimitsHourly). \
filter(EnergyHourly.house_id == house_id).one()
return {
'used_max': limits.used_max,
'solar_min': limits.solar_min,
'outdoor_deg_min': limits.outdoor_deg_min,
'outdoor_deg_max': limits.outdoor_deg_max,
'hdd_max': limits.hdd_max,
'start_date': str(limits.start_date),
'end_date': str(limits.end_date)
}
def get_items(self, house_id):
""" Get and store rows from database. """
items = db_session.query("date", "cold", "hot", "main", "water_heater",
"water_pump")
date_range = self.filter_query_by_date_range_sql()
sel = "MIN(e.date)"
grp = "YEAR(e.date)"
if 'month' in self.args['interval']:
grp = "MONTH(e.date) "
sql = """SELECT %s AS 'date', SUM(main.gallons) -
SUM(hot.gallons) AS 'cold', SUM(hot.gallons) AS 'hot',
SUM(main.gallons) AS 'main',
SUM(e.water_heater) AS 'water_heater',
SUM(e.water_pump) AS 'water_pump'
FROM energy_monthly e
LEFT JOIN (SELECT house_id, date, gallons FROM water_monthly
WHERE device_id = 6) main ON e.date = main.date
AND main.house_id = e.house_id
LEFT JOIN (SELECT house_id, date, gallons FROM water_monthly
WHERE device_id = 7) hot ON e.date = hot.date
AND hot.house_id = e.house_id
WHERE e.house_id = :house_id
%s
GROUP BY %s
ORDER BY %s
""" % (sel, date_range, grp, grp)
items = items.from_statement(text(sql))
items = items.params(house_id=house_id,
start=self.args['start'],
end=self.args['end']).all()
self.json_items = []
for item in items:
data = {
'date': str(item.date),
'cold': str(item.cold),
'hot': str(item.hot),
'main': str(item.main),
'water_heater': str(item.water_heater),
'water_pump': str(item.water_pump)
}
self.json_items.append(data)
def get_circuits(self, house_id):
""" Get, store and return list of circuits from database. """
circuits = db_session.query(Circuits). \
filter(Circuits.house_id == house_id).all()
self.json_items = []
for circuit in circuits:
data = {
'circuit_id': circuit.circuit_id,
'name': circuit.name,
'description': circuit.description
}
self.json_items.append(data)
return circuits
def get_house_details(house_id):
""" Return details for house X in json. """
house = db_session.query(Houses). \
filter(Houses.house_id == house_id).one()
return {
'name':
house.name,
'sname':
house.sname,
'id':
house.house_id,
'devices':
url_for('get_devices', house_id=house.house_id, _external=True),
'circuits':
url_for('get_circuits', house_id=house.house_id, _external=True)
}
def get_houses_all():
""" Return array of houses. """
houses = db_session.query(Houses).all()
json_items = []
for house in houses:
data = {
'name': house.name,
'sname': house.sname,
'house_id': house.house_id,
'url': url_for('get_house',
house_id=house.house_id,
_external=True)
}
json_items.append(data)
return json_items
def get_totals_year_month(self, house_id):
""" Get and store yearly or monthly totals. """
self.base_query = db_session.query(label('date', func.min(EnergyMonthly.date)),
label('sum_actual',
func.sum(EnergyMonthly.solar)),
label('sum_estimated',
func.sum(EstimatedMonthly.solar))).\
outerjoin(EstimatedMonthly,
and_(EnergyMonthly.date == EstimatedMonthly.date,
EnergyMonthly.house_id == EstimatedMonthly.house_id)).\
filter(EnergyMonthly.house_id == house_id)
self.filter_query_by_date_range(EnergyMonthly)
totals = self.base_query.one()
self.json_totals = {
'actual': str(totals.sum_actual),
'estimated': str(totals.sum_estimated)
}
def get_items(self, house_id):
""" Get and store points (primarily hdd and ashp values) from database. """
items = db_session.query("hdd", "ashp", "temperature", "date", "solar")
if self.args['start'] is not None and self.args['end'] is not None:
date_range = " AND date BETWEEN :start AND :end "
elif self.args['start'] is not None:
date_range = " AND date >= :start "
elif self.args['end'] is not None:
date_range = " AND date < :end "
elif self.args['start'] is None and self.args['end'] is None:
date_range = ""
if 'hour' in self.args['interval']:
sql = """SELECT e.date AS 'date',
t.hdd AS 'hdd',
e.ashp/1000.0 AS 'ashp',
t.temperature AS 'temperature',
e.solar/1000.0 AS 'solar'
"""
else:
sql = """SELECT MIN(e.date) AS 'date',
SUM(t.hdd) AS 'hdd',
SUM(e.ashp)/1000.0 AS 'ashp',
AVG(t.temperature) AS 'temperature',
SUM(e.solar)/1000.0 AS 'solar'
"""
sql = sql + \
"""FROM (SELECT date, solar, ashp
FROM energy_hourly
WHERE house_id = :house_id
AND solar > -500
AND ashp > 50 %s) e
LEFT JOIN
(SELECT date, temperature, (:base - temperature) / 24.0 AS 'hdd'
FROM temperature_hourly
WHERE house_id = :house_id
AND device_id = 0
AND temperature <= :base %s) t ON e.date = t.date
WHERE t.date = e.date
""" % (date_range, date_range)
if self.args['interval'] == 'year':
sql = sql + "GROUP BY YEAR(t.date)"
if self.args['interval'] == 'month':
sql = sql + "GROUP BY YEAR(t.date), MONTH(t.date)"
if self.args['interval'] == 'day':
sql = sql + "GROUP BY CAST(t.date AS DATE)"
items = items.from_statement(text(sql))
items = items.params(house_id=house_id,
base=self.args['base'],
start=self.args['start'],
end=self.args['end']).all()
self.json_items = []
for item in items:
data = {
'date': str(self.format_date(item.date)),
'solar': str(item.solar),
'ashp': str(item.ashp),
'temperature': str(item.temperature),
'hdd': str(item.hdd)
}
self.json_items.append(data)
def get_summary(self, house_id):
""" Get and store summary usage values from database. """
self.base_query = db_session.query(label('used',
func.sum(EnergyHourly.used)/1000),
label('water_heater',
func.sum(EnergyHourly.water_heater)/1000),
label('ashp',
func.sum(EnergyHourly.ashp)/1000),
label('water_pump',
func.sum(EnergyHourly.water_pump)/1000),
label('dryer',
func.sum(EnergyHourly.dryer)/1000),
label('washer',
func.sum(EnergyHourly.washer)/1000),
label('dishwasher',
func.sum(EnergyHourly.dishwasher)/1000),
label('stove',
func.sum(EnergyHourly.stove)/1000),
label('refrigerator',
func.sum(EnergyHourly.refrigerator)/1000),
label('living_room',
func.sum(EnergyHourly.living_room)/1000),
label('aux_heat_bedrooms',
func.sum(EnergyHourly.aux_heat_bedrooms)/1000),
label('aux_heat_living',
func.sum(EnergyHourly.aux_heat_living)/1000),
label('study',
func.sum(EnergyHourly.study)/1000),
label('barn',
func.sum(EnergyHourly.barn)/1000),
label('basement_west',
func.sum(EnergyHourly.basement_west)/1000),
label('basement_east',
func.sum(EnergyHourly.basement_east)/1000),
label('ventilation',
func.sum(EnergyHourly.ventilation)/1000),
label('ventilation_preheat',
func.sum(EnergyHourly.ventilation_preheat)/1000),
label('kitchen_recept_rt',
func.sum(EnergyHourly.kitchen_recept_rt)/1000)).\
filter(EnergyHourly.house_id == house_id).\
filter(or_(EnergyHourly.device_id == 5,
EnergyHourly.device_id == 10))
self.filter_query_by_date_range(EnergyHourly)
totals = self.base_query.one()
self.json_circuits = []
subtotal = 0
for column in self.base_query.column_descriptions:
actual = getattr(totals, column['name'])
if actual is None:
actual = 0
if column['name'] == 'used':
subtotal = subtotal + actual
else:
subtotal = subtotal - actual
self.json_circuits.append({'circuit_id': column['name'],
'actual': str(actual),
'name': self.get_circuit_info(column['name'])['name']
})
self.json_circuits.append({'circuit_id': 'all_other',
'actual': str(subtotal),
'name': self.get_circuit_info('all_other')['name']
})
if 'year' in self.args['interval']:
self.json_circuit = {'circuit_id': 'summary',
'name': 'Total'}
else:
self.json_circuit = {'circuit_id': 'summary',
'startdate': self.args['start'].strftime("%Y-%m-%d"),
'enddate': self.args['end'].strftime("%Y-%m-%d"),
'name': 'Total'}
def get_circuit_ashp(self, house_id):
""" Get and store ashp usage total and by interval from database. """
session = db_session.query("date", "actual", "hdd")
date_range = self.filter_query_by_date_range_sql()
date_column_format = "MIN(e.date)"
if self.args['interval'] is not 'hour':
date_column_format = "MIN(DATE(e.date))"
sql = """SELECT %s AS 'date', SUM(e.ashp)/1000.0 AS 'actual',
SUM( IF( ((:base - t.temperature) / 24) > 0,
((:base - t.temperature) / 24), 0) ) AS 'hdd'
FROM temperature_hourly t, energy_hourly e
WHERE t.device_id = 0
AND t.house_id = :house_id
AND e.house_id = :house_id
AND (e.device_id = 5 OR e.device_id = 10)
%s
AND e.date = t.date
""" % (date_column_format, date_range)
totals = session.from_statement(text(sql))
totals = totals.params(house_id=house_id,
start=self.args['start'],
end=self.args['end'],
base=self.args['base']).one()
self.json_totals = {'actual': str(totals.actual),
'hdd': str(totals.hdd)}
grp = ""
if 'month' in self.args['interval']:
grp = ", MONTH(e.date) "
elif 'day' in self.args['interval']:
grp = ", MONTH(e.date), DAY(e.date) "
elif 'hour' in self.args['interval']:
grp = ", MONTH(e.date), DAY(e.date), HOUR(e.date) "
sql = sql + " GROUP BY YEAR(e.date)" + grp
items = session.from_statement(text(sql))
items = items.params(house_id=house_id,
start=self.args['start'],
end=self.args['end'],
base=self.args['base']).all()
self.json_items = []
for item in items:
data = {'date': self.format_date(item.date),
'actual': str(item.actual),
'hdd': str(item.hdd)}
self.json_items.append(data)
self.json_circuit = {'circuit_id': 'ashp',
'name': self.get_circuit_info('ashp')['name'],
'description': self.get_circuit_info('ashp')['description']}
def get_iga(self, house_id):
""" Get and store internal gross area value. """
self.iga_query = db_session.query(Houses).\
filter(Houses.house_id == house_id).one()
def get_items(self, house_id):
""" Get and store hourly values from database. """
items = db_session.query("date", "net", "solar", "used",
"first_floor_temp", "second_floor_temp",
"basement_temp", "outdoor_temp", "hdd",
"water_heater", "ashp", "water_pump",
"dryer", "washer", "dishwasher", "stove",
"refrigerator", "living_room",
"aux_heat_bedrooms", "aux_heat_living",
"study", "barn", "basement_west",
"basement_east", "ventilation",
"ventilation_preheat", "kitchen_recept_rt",
"all_other")
sql = """SELECT ti1.date AS 'date', e.adjusted_load AS 'net',
e.solar AS 'solar', e.used AS 'used',
ti1.indoor1_deg AS 'first_floor_temp',
ti2.indoor2_deg AS 'second_floor_temp',
ti0.indoor0_deg AS 'basement_temp',
tu.outdoor_deg AS 'outdoor_temp', th.hdd AS 'hdd',
e.water_heater AS 'water_heater', e.ashp AS 'ashp',
e.water_pump AS 'water_pump', e.dryer AS 'dryer',
e.washer AS 'washer', e.dishwasher AS 'dishwasher',
e.stove AS 'stove', e.refrigerator AS 'refrigerator',
e.living_room AS 'living_room',
e.aux_heat_bedrooms AS 'aux_heat_bedrooms',
e.aux_heat_living AS 'aux_heat_living', e.study AS 'study',
e.barn AS 'barn', e.basement_west AS 'basement_west',
e.basement_east AS 'basement_east', e.ventilation AS 'ventilation',
e.ventilation_preheat AS 'ventilation_preheat',
e.kitchen_recept_rt AS 'kitchen_recept_rt',
e.used-(e.water_heater + e.ashp + e.water_pump + e.dryer +
e.washer + e.dishwasher + e.stove + e.refrigerator + e.living_room +
e.aux_heat_bedrooms + e.aux_heat_living + e.study + e.barn +
e.basement_west + e.basement_east + e.ventilation +
e.ventilation_preheat + e.kitchen_recept_rt) AS 'all_other'
FROM (SELECT house_id, date, temperature AS 'indoor1_deg'
FROM temperature_hourly
WHERE device_id = 1) ti1
LEFT JOIN (SELECT house_id, date, temperature AS 'indoor2_deg'
FROM temperature_hourly WHERE device_id = 2) ti2
ON CAST(LEFT(ti2.date,13) AS DATETIME) = CAST(LEFT(ti1.date,13) AS DATETIME)
AND ti2.house_id = ti1.house_id
LEFT JOIN (SELECT house_id, date, temperature AS 'indoor0_deg'
FROM temperature_hourly WHERE device_id = 3) ti0
ON CAST(LEFT(ti0.date,13) AS DATETIME) = CAST(LEFT(ti1.date,13) AS DATETIME)
AND ti0.house_id = ti1.house_id
LEFT JOIN (SELECT house_id, date, temperature AS 'outdoor_deg'
FROM temperature_hourly WHERE device_id = 0) tu
ON CAST(LEFT(tu.date,13) AS DATETIME) = CAST(LEFT(ti1.date,13) AS DATETIME)
AND tu.house_id = ti1.house_id
LEFT JOIN (SELECT house_id, date, hdd
FROM hdd_hourly) th
ON CAST(LEFT(th.date,13) AS DATETIME) = CAST(LEFT(ti1.date,13) AS DATETIME)
AND th.house_id = ti1.house_id
LEFT JOIN energy_hourly e
ON CAST(LEFT(e.date,13) AS DATETIME) = CAST(LEFT(ti1.date,13) AS DATETIME)
AND e.house_id = ti1.house_id
WHERE CAST(ti1.date AS DATE) = DATE(:start)
AND ti1.house_id = :house_id
ORDER BY e.date
"""
items = items.from_statement(text(sql))
items = items.params(house_id=house_id,
start=self.args['start'],
end=self.args['end']).all()
self.json_items = []
for item in items:
data = {'date': str(item.date),
'net': str(item.net),
'solar': str(item.solar),
'used': str(item.used),
'first_floor_temp': str(item.first_floor_temp),
'second_floor_temp': str(item.second_floor_temp),
'basement_temp': str(item.basement_temp),
'outdoor_temp': str(item.outdoor_temp),
'hdd': str(item.hdd),
'water_heater': str(item.water_heater),
'ashp': str(item.ashp),
'water_pump': str(item.water_pump),
'dryer': str(item.dryer),
'washer': str(item.washer),
'dishwasher': str(item.dishwasher),
'stove': str(item.stove),
'refrigerator': str(item.refrigerator),
'living_room': str(item.living_room),
'aux_heat_bedrooms': str(item.aux_heat_bedrooms),
'aux_heat_living': str(item.aux_heat_living),
'study': str(item.study),
'barn': str(item.barn),
'basement_west': str(item.basement_west),
'basement_east': str(item.basement_east),
'ventilation': str(item.ventilation),
'ventilation_preheat': str(item.ventilation_preheat),
'kitchen_recept_rt': str(item.kitchen_recept_rt),
'all_other': str(item.all_other)}
self.json_items.append(data)
def get_items(self, house_id):
""" Get and store daily values from database. """
items = db_session.query(
"date", "net", "solar", "used", "outdoor_deg_min",
"outdoor_deg_max", "hdd", "water_heater", "ashp", "water_pump",
"dryer", "washer", "dishwasher", "stove", "refrigerator",
"living_room", "aux_heat_bedrooms", "aux_heat_living", "study",
"barn", "basement_west", "basement_east", "ventilation",
"ventilation_preheat", "kitchen_recept_rt", "all_other")
if self.args['start'] is not None and self.args['end'] is not None:
date_range = " tu.date BETWEEN :start AND :end "
elif self.args['start'] is not None:
date_range = " tu.date >= :start "
elif self.args['end'] is not None:
date_range = " tu.date < :end "
sql = """SELECT tu.date AS 'date', e.adjusted_load AS 'net',
e.solar AS 'solar', e.used AS 'used',
tu.outdoor_deg_min AS 'outdoor_deg_min',
tu.outdoor_deg_max AS 'outdoor_deg_max',
th.hdd AS 'hdd', e.water_heater AS 'water_heater',
e.ashp AS 'ashp', e.water_pump AS 'water_pump',
e.dryer AS 'dryer', e.washer AS 'washer',
e.dishwasher AS 'dishwasher', e.stove AS 'stove',
e.refrigerator AS 'refrigerator',
e.living_room AS 'living_room',
e.aux_heat_bedrooms AS 'aux_heat_bedrooms',
e.aux_heat_living AS 'aux_heat_living',
e.study AS 'study', e.barn AS 'barn',
e.basement_west AS 'basement_west',
e.basement_east AS 'basement_east',
e.ventilation AS 'ventilation',
e.ventilation_preheat AS 'ventilation_preheat',
e.kitchen_recept_rt AS 'kitchen_recept_rt',
e.used-(e.water_heater + e.ashp + e.water_pump +
e.dryer + e.washer + e.dishwasher + e.stove +
e.refrigerator + e.living_room +
e.aux_heat_bedrooms + e.aux_heat_living + e.study +
e.barn + e.basement_west + e.basement_east +
e.ventilation + e.ventilation_preheat +
e.kitchen_recept_rt) AS 'all_other'
FROM (SELECT house_id, date,
temperature_min AS 'outdoor_deg_min',
temperature_max AS 'outdoor_deg_max'
FROM temperature_daily
WHERE device_id = 0) tu
LEFT JOIN (SELECT house_id, date, hdd FROM hdd_daily) th
ON th.date = tu.date AND th.house_id = tu.house_id
LEFT JOIN energy_daily e ON e.date = tu.date
AND e.house_id = tu.house_id
WHERE tu.house_id = :house_id
AND %s
ORDER BY e.date
""" % date_range
items = items.from_statement(text(sql))
items = items.params(house_id=house_id,
base=self.args['base'],
start=self.args['start'],
end=self.args['end']).all()
self.json_items = []
for item in items:
data = {
'date': str(item.date),
'net': str(item.net),
'solar': str(item.solar),
'used': str(item.used),
'outdoor_deg_min': str(item.outdoor_deg_min),
'outdoor_deg_max': str(item.outdoor_deg_max),
'hdd': str(item.hdd),
'water_heater': str(item.water_heater),
'ashp': str(item.ashp),
'water_pump': str(item.water_pump),
'dryer': str(item.dryer),
'washer': str(item.washer),
'dishwasher': str(item.dishwasher),
'stove': str(item.stove),
'refrigerator': str(item.refrigerator),
'living_room': str(item.living_room),
'aux_heat_bedrooms': str(item.aux_heat_bedrooms),
'aux_heat_living': str(item.aux_heat_living),
'study': str(item.study),
'barn': str(item.barn),
'basement_west': str(item.basement_west),
'basement_east': str(item.basement_east),
'ventilation': str(item.ventilation),
'ventilation_preheat': str(item.ventilation_preheat),
'kitchen_recept_rt': str(item.kitchen_recept_rt),
'all_other': str(item.all_other)
}
self.json_items.append(data)
