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)