def on_get(req, resp):
print(req.params)
shopfloor_id = req.params.get('shopfloorid')
period_type = req.params.get('periodtype')
base_start_datetime_local = req.params.get('baseperiodstartdatetime')
base_end_datetime_local = req.params.get('baseperiodenddatetime')
reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if shopfloor_id is None:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SHOPFLOOR_ID')
else:
shopfloor_id = str.strip(shopfloor_id)
if not shopfloor_id.isdigit() or int(shopfloor_id) <= 0:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SHOPFLOOR_ID')
if period_type is None:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
else:
period_type = str.strip(period_type)
if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
base_start_datetime_utc = None
if base_start_datetime_local is not None and len(str.strip(base_start_datetime_local)) > 0:
base_start_datetime_local = str.strip(base_start_datetime_local)
try:
base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_BEGINS_DATETIME")
base_end_datetime_utc = None
if base_end_datetime_local is not None and len(str.strip(base_end_datetime_local)) > 0:
base_end_datetime_local = str.strip(base_end_datetime_local)
try:
base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_ENDS_DATETIME")
if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
base_start_datetime_utc >= base_end_datetime_utc:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_BASE_PERIOD_ENDS_DATETIME')
if reporting_start_datetime_local is None:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_BEGINS_DATETIME")
else:
reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
try:
reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_BEGINS_DATETIME")
if reporting_end_datetime_local is None:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_ENDS_DATETIME")
else:
reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
try:
reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_ENDS_DATETIME")
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_ENDS_DATETIME')
################################################################################################################
# Step 2: query the shopfloor
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_energy = mysql.connector.connect(**config.myems_energy_db)
cursor_energy = cnx_energy.cursor()
cnx_energy_baseline = mysql.connector.connect(**config.myems_energy_baseline_db)
cursor_energy_baseline = cnx_energy_baseline.cursor()
cnx_historical = mysql.connector.connect(**config.myems_historical_db)
cursor_historical = cnx_historical.cursor()
cursor_system.execute(" SELECT id, name, area, cost_center_id "
" FROM tbl_shopfloors "
" WHERE id = %s ", (shopfloor_id,))
row_shopfloor = cursor_system.fetchone()
if row_shopfloor is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cursor_energy_baseline:
cursor_energy_baseline.close()
if cnx_energy_baseline:
cnx_energy_baseline.disconnect()
if cnx_historical:
cnx_historical.close()
if cursor_historical:
cursor_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.SHOPFLOOR_NOT_FOUND')
shopfloor = dict()
shopfloor['id'] = row_shopfloor[0]
shopfloor['name'] = row_shopfloor[1]
shopfloor['area'] = row_shopfloor[2]
shopfloor['cost_center_id'] = row_shopfloor[3]
################################################################################################################
# Step 3: query energy categories
################################################################################################################
energy_category_set = set()
# query energy categories in base period
cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
" FROM tbl_shopfloor_input_category_hourly "
" WHERE shopfloor_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(shopfloor['id'], base_start_datetime_utc, base_end_datetime_utc))
rows_energy_categories = cursor_energy.fetchall()
if rows_energy_categories is not None or len(rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set.add(row_energy_category[0])
# query energy categories in reporting period
cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
" FROM tbl_shopfloor_input_category_hourly "
" WHERE shopfloor_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(shopfloor['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
rows_energy_categories = cursor_energy.fetchall()
if rows_energy_categories is not None or len(rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set.add(row_energy_category[0])
# query all energy categories in base period and reporting period
cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
" FROM tbl_energy_categories "
" ORDER BY id ", )
rows_energy_categories = cursor_system.fetchall()
if rows_energy_categories is None or len(rows_energy_categories) == 0:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cursor_energy_baseline:
cursor_energy_baseline.close()
if cnx_energy_baseline:
cnx_energy_baseline.disconnect()
if cnx_historical:
cnx_historical.close()
if cursor_historical:
cursor_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.ENERGY_CATEGORY_NOT_FOUND')
energy_category_dict = dict()
for row_energy_category in rows_energy_categories:
if row_energy_category[0] in energy_category_set:
energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
"unit_of_measure": row_energy_category[2],
"kgce": row_energy_category[3],
"kgco2e": row_energy_category[4]}
################################################################################################################
# Step 4: query associated sensors
################################################################################################################
point_list = list()
cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type "
" FROM tbl_shopfloors st, tbl_sensors se, tbl_shopfloors_sensors ss, "
" tbl_points p, tbl_sensors_points sp "
" WHERE st.id = %s AND st.id = ss.shopfloor_id AND ss.sensor_id = se.id "
" AND se.id = sp.sensor_id AND sp.point_id = p.id "
" ORDER BY p.id ", (shopfloor['id'],))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
################################################################################################################
# Step 5: query associated points
################################################################################################################
cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type "
" FROM tbl_shopfloors s, tbl_shopfloors_points sp, tbl_points p "
" WHERE s.id = %s AND s.id = sp.shopfloor_id AND sp.point_id = p.id "
" ORDER BY p.id ", (shopfloor['id'],))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
################################################################################################################
# Step 6: query base period energy saving
################################################################################################################
base = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
kgce = energy_category_dict[energy_category_id]['kgce']
kgco2e = energy_category_dict[energy_category_id]['kgco2e']
base[energy_category_id] = dict()
base[energy_category_id]['timestamps'] = list()
base[energy_category_id]['values_baseline'] = list()
base[energy_category_id]['values_actual'] = list()
base[energy_category_id]['values_saving'] = list()
base[energy_category_id]['subtotal_baseline'] = Decimal(0.0)
base[energy_category_id]['subtotal_actual'] = Decimal(0.0)
base[energy_category_id]['subtotal_saving'] = Decimal(0.0)
base[energy_category_id]['subtotal_in_kgce_baseline'] = Decimal(0.0)
base[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0)
base[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0)
base[energy_category_id]['subtotal_in_kgco2e_baseline'] = Decimal(0.0)
base[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0)
base[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0)
# query base period's energy baseline
cursor_energy_baseline.execute(" SELECT start_datetime_utc, actual_value "
" FROM tbl_shopfloor_input_category_hourly "
" WHERE shopfloor_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(shopfloor['id'],
energy_category_id,
base_start_datetime_utc,
base_end_datetime_utc))
rows_shopfloor_hourly = cursor_energy_baseline.fetchall()
rows_shopfloor_periodically = utilities.aggregate_hourly_data_by_period(rows_shopfloor_hourly,
base_start_datetime_utc,
base_end_datetime_utc,
period_type)
for row_shopfloor_periodically in rows_shopfloor_periodically:
current_datetime_local = row_shopfloor_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
baseline_value = Decimal(0.0) if row_shopfloor_periodically[1] is None \
else row_shopfloor_periodically[1]
base[energy_category_id]['timestamps'].append(current_datetime)
base[energy_category_id]['values_baseline'].append(baseline_value)
base[energy_category_id]['subtotal_baseline'] += baseline_value
base[energy_category_id]['subtotal_in_kgce_baseline'] += baseline_value * kgce
base[energy_category_id]['subtotal_in_kgco2e_baseline'] += baseline_value * kgco2e
# query base period's energy actual
cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
" FROM tbl_shopfloor_input_category_hourly "
" WHERE shopfloor_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(shopfloor['id'],
energy_category_id,
base_start_datetime_utc,
base_end_datetime_utc))
rows_shopfloor_hourly = cursor_energy.fetchall()
rows_shopfloor_periodically = utilities.aggregate_hourly_data_by_period(rows_shopfloor_hourly,
base_start_datetime_utc,
base_end_datetime_utc,
period_type)
for row_shopfloor_periodically in rows_shopfloor_periodically:
current_datetime_local = row_shopfloor_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
actual_value = Decimal(0.0) if row_shopfloor_periodically[1] is None \
else row_shopfloor_periodically[1]
base[energy_category_id]['values_actual'].append(actual_value)
base[energy_category_id]['subtotal_actual'] += actual_value
base[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce
base[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e
# calculate base period's energy savings
for i in range(len(base[energy_category_id]['values_baseline'])):
base[energy_category_id]['values_saving'].append(
base[energy_category_id]['values_baseline'][i] -
base[energy_category_id]['values_actual'][i])
base[energy_category_id]['subtotal_saving'] = \
base[energy_category_id]['subtotal_baseline'] - \
base[energy_category_id]['subtotal_actual']
base[energy_category_id]['subtotal_in_kgce_saving'] = \
base[energy_category_id]['subtotal_in_kgce_baseline'] - \
base[energy_category_id]['subtotal_in_kgce_actual']
base[energy_category_id]['subtotal_in_kgco2e_saving'] = \
base[energy_category_id]['subtotal_in_kgco2e_baseline'] - \
base[energy_category_id]['subtotal_in_kgco2e_actual']
################################################################################################################
# Step 7: query reporting period energy saving
################################################################################################################
reporting = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
kgce = energy_category_dict[energy_category_id]['kgce']
kgco2e = energy_category_dict[energy_category_id]['kgco2e']
reporting[energy_category_id] = dict()
reporting[energy_category_id]['timestamps'] = list()
reporting[energy_category_id]['values_baseline'] = list()
reporting[energy_category_id]['values_actual'] = list()
reporting[energy_category_id]['values_saving'] = list()
reporting[energy_category_id]['subtotal_baseline'] = Decimal(0.0)
reporting[energy_category_id]['subtotal_actual'] = Decimal(0.0)
reporting[energy_category_id]['subtotal_saving'] = Decimal(0.0)
reporting[energy_category_id]['subtotal_in_kgce_baseline'] = Decimal(0.0)
reporting[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0)
reporting[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0)
reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] = Decimal(0.0)
reporting[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0)
reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0)
# query reporting period's energy baseline
cursor_energy_baseline.execute(" SELECT start_datetime_utc, actual_value "
" FROM tbl_shopfloor_input_category_hourly "
" WHERE shopfloor_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(shopfloor['id'],
energy_category_id,
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_shopfloor_hourly = cursor_energy_baseline.fetchall()
rows_shopfloor_periodically = utilities.aggregate_hourly_data_by_period(rows_shopfloor_hourly,
reporting_start_datetime_utc,
reporting_end_datetime_utc,
period_type)
for row_shopfloor_periodically in rows_shopfloor_periodically:
current_datetime_local = row_shopfloor_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
baseline_value = Decimal(0.0) if row_shopfloor_periodically[1] is None \
else row_shopfloor_periodically[1]
reporting[energy_category_id]['timestamps'].append(current_datetime)
reporting[energy_category_id]['values_baseline'].append(baseline_value)
reporting[energy_category_id]['subtotal_baseline'] += baseline_value
reporting[energy_category_id]['subtotal_in_kgce_baseline'] += baseline_value * kgce
reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] += baseline_value * kgco2e
# query reporting period's energy actual
cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
" FROM tbl_shopfloor_input_category_hourly "
" WHERE shopfloor_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(shopfloor['id'],
energy_category_id,
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_shopfloor_hourly = cursor_energy.fetchall()
rows_shopfloor_periodically = utilities.aggregate_hourly_data_by_period(rows_shopfloor_hourly,
reporting_start_datetime_utc,
reporting_end_datetime_utc,
period_type)
for row_shopfloor_periodically in rows_shopfloor_periodically:
current_datetime_local = row_shopfloor_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
actual_value = Decimal(0.0) if row_shopfloor_periodically[1] is None \
else row_shopfloor_periodically[1]
reporting[energy_category_id]['values_actual'].append(actual_value)
reporting[energy_category_id]['subtotal_actual'] += actual_value
reporting[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce
reporting[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e
# calculate reporting period's energy savings
for i in range(len(reporting[energy_category_id]['values_baseline'])):
reporting[energy_category_id]['values_saving'].append(
reporting[energy_category_id]['values_baseline'][i] -
reporting[energy_category_id]['values_actual'][i])
reporting[energy_category_id]['subtotal_saving'] = \
reporting[energy_category_id]['subtotal_baseline'] - \
reporting[energy_category_id]['subtotal_actual']
reporting[energy_category_id]['subtotal_in_kgce_saving'] = \
reporting[energy_category_id]['subtotal_in_kgce_baseline'] - \
reporting[energy_category_id]['subtotal_in_kgce_actual']
reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = \
reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] - \
reporting[energy_category_id]['subtotal_in_kgco2e_actual']
################################################################################################################
# Step 8: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
energy_category_tariff_dict = utilities.get_energy_category_tariffs(shopfloor['cost_center_id'],
energy_category_id,
reporting_start_datetime_utc,
reporting_end_datetime_utc)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in energy_category_tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
tariff_value_list.append(v)
parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 9: query associated sensors and points data
################################################################################################################
for point in point_list:
point_values = []
point_timestamps = []
if point['object_type'] == 'ANALOG_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_analog_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(query, (point['id'],
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'ENERGY_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_energy_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(query, (point['id'],
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'DIGITAL_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_digital_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s ")
cursor_historical.execute(query, (point['id'],
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
parameters_data['timestamps'].append(point_timestamps)
parameters_data['values'].append(point_values)
################################################################################################################
# Step 10: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cursor_energy_baseline:
cursor_energy_baseline.close()
if cnx_energy_baseline:
cnx_energy_baseline.disconnect()
result = dict()
result['shopfloor'] = dict()
result['shopfloor']['name'] = shopfloor['name']
result['shopfloor']['area'] = shopfloor['area']
result['base_period'] = dict()
result['base_period']['names'] = list()
result['base_period']['units'] = list()
result['base_period']['timestamps'] = list()
result['base_period']['values_saving'] = list()
result['base_period']['subtotals_saving'] = list()
result['base_period']['subtotals_in_kgce_saving'] = list()
result['base_period']['subtotals_in_kgco2e_saving'] = list()
result['base_period']['total_in_kgce_saving'] = Decimal(0.0)
result['base_period']['total_in_kgco2e_saving'] = Decimal(0.0)
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['base_period']['names'].append(energy_category_dict[energy_category_id]['name'])
result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
result['base_period']['timestamps'].append(base[energy_category_id]['timestamps'])
result['base_period']['values_saving'].append(base[energy_category_id]['values_saving'])
result['base_period']['subtotals_saving'].append(base[energy_category_id]['subtotal_saving'])
result['base_period']['subtotals_in_kgce_saving'].append(
base[energy_category_id]['subtotal_in_kgce_saving'])
result['base_period']['subtotals_in_kgco2e_saving'].append(
base[energy_category_id]['subtotal_in_kgco2e_saving'])
result['base_period']['total_in_kgce_saving'] += base[energy_category_id]['subtotal_in_kgce_saving']
result['base_period']['total_in_kgco2e_saving'] += base[energy_category_id]['subtotal_in_kgco2e_saving']
result['reporting_period'] = dict()
result['reporting_period']['names'] = list()
result['reporting_period']['energy_category_ids'] = list()
result['reporting_period']['units'] = list()
result['reporting_period']['timestamps'] = list()
result['reporting_period']['values_saving'] = list()
result['reporting_period']['subtotals_saving'] = list()
result['reporting_period']['subtotals_in_kgce_saving'] = list()
result['reporting_period']['subtotals_in_kgco2e_saving'] = list()
result['reporting_period']['subtotals_per_unit_area_saving'] = list()
result['reporting_period']['increment_rates_saving'] = list()
result['reporting_period']['total_in_kgce_saving'] = Decimal(0.0)
result['reporting_period']['total_in_kgco2e_saving'] = Decimal(0.0)
result['reporting_period']['increment_rate_in_kgce_saving'] = Decimal(0.0)
result['reporting_period']['increment_rate_in_kgco2e_saving'] = Decimal(0.0)
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
result['reporting_period']['energy_category_ids'].append(energy_category_id)
result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps'])
result['reporting_period']['values_saving'].append(reporting[energy_category_id]['values_saving'])
result['reporting_period']['subtotals_saving'].append(reporting[energy_category_id]['subtotal_saving'])
result['reporting_period']['subtotals_in_kgce_saving'].append(
reporting[energy_category_id]['subtotal_in_kgce_saving'])
result['reporting_period']['subtotals_in_kgco2e_saving'].append(
reporting[energy_category_id]['subtotal_in_kgco2e_saving'])
result['reporting_period']['subtotals_per_unit_area_saving'].append(
reporting[energy_category_id]['subtotal_saving'] / shopfloor['area'] if shopfloor['area'] > 0.0
else None)
result['reporting_period']['increment_rates_saving'].append(
(reporting[energy_category_id]['subtotal_saving'] - base[energy_category_id]['subtotal_saving']) /
base[energy_category_id]['subtotal_saving']
if base[energy_category_id]['subtotal_saving'] > 0.0 else None)
result['reporting_period']['total_in_kgce_saving'] += \
reporting[energy_category_id]['subtotal_in_kgce_saving']
result['reporting_period']['total_in_kgco2e_saving'] += \
reporting[energy_category_id]['subtotal_in_kgco2e_saving']
result['reporting_period']['total_in_kgco2e_per_unit_area_saving'] = \
result['reporting_period']['total_in_kgce_saving'] / shopfloor['area'] if shopfloor['area'] > 0.0 else None
result['reporting_period']['increment_rate_in_kgce_saving'] = \
(result['reporting_period']['total_in_kgce_saving'] - result['base_period']['total_in_kgce_saving']) / \
result['base_period']['total_in_kgce_saving'] \
if result['base_period']['total_in_kgce_saving'] > Decimal(0.0) else None
result['reporting_period']['total_in_kgce_per_unit_area_saving'] = \
result['reporting_period']['total_in_kgco2e_saving'] / shopfloor['area'] \
if shopfloor['area'] > 0.0 else None
result['reporting_period']['increment_rate_in_kgco2e_saving'] = \
(result['reporting_period']['total_in_kgco2e_saving'] - result['base_period']['total_in_kgco2e_saving']) / \
result['base_period']['total_in_kgco2e_saving'] \
if result['base_period']['total_in_kgco2e_saving'] > Decimal(0.0) else None
result['parameters'] = {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
}
resp.body = json.dumps(result)
def on_get(req, resp):
print(req.params)
combined_equipment_id = req.params.get('combinedequipmentid')
combined_equipment_uuid = req.params.get('combinedequipmentuuid')
period_type = req.params.get('periodtype')
base_start_datetime_local = req.params.get('baseperiodstartdatetime')
base_end_datetime_local = req.params.get('baseperiodenddatetime')
reporting_start_datetime_local = req.params.get(
'reportingperiodstartdatetime')
reporting_end_datetime_local = req.params.get(
'reportingperiodenddatetime')
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if combined_equipment_id is None and combined_equipment_uuid is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_COMBINED_EQUIPMENT_ID')
if combined_equipment_id is not None:
combined_equipment_id = str.strip(combined_equipment_id)
if not combined_equipment_id.isdigit() or int(
combined_equipment_id) <= 0:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_COMBINED_EQUIPMENT_ID')
if combined_equipment_uuid is not None:
regex = re.compile(
'^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z',
re.I)
match = regex.match(str.strip(combined_equipment_uuid))
if not bool(match):
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_COMBINED_EQUIPMENT_UUID')
if period_type is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
else:
period_type = str.strip(period_type)
if period_type not in [
'hourly', 'daily', 'weekly', 'monthly', 'yearly'
]:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
base_start_datetime_utc = None
if base_start_datetime_local is not None and len(
str.strip(base_start_datetime_local)) > 0:
base_start_datetime_local = str.strip(base_start_datetime_local)
try:
base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_START_DATETIME")
base_end_datetime_utc = None
if base_end_datetime_local is not None and len(
str.strip(base_end_datetime_local)) > 0:
base_end_datetime_local = str.strip(base_end_datetime_local)
try:
base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_END_DATETIME")
if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
base_start_datetime_utc >= base_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_BASE_PERIOD_END_DATETIME')
if reporting_start_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
else:
reporting_start_datetime_local = str.strip(
reporting_start_datetime_local)
try:
reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
if reporting_end_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
else:
reporting_end_datetime_local = str.strip(
reporting_end_datetime_local)
try:
reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
################################################################################################################
# Step 2: query the combined equipment
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_energy = mysql.connector.connect(**config.myems_energy_db)
cursor_energy = cnx_energy.cursor()
cnx_historical = mysql.connector.connect(**config.myems_historical_db)
cursor_historical = cnx_historical.cursor()
if combined_equipment_id is not None:
cursor_system.execute(
" SELECT id, name, cost_center_id "
" FROM tbl_combined_equipments "
" WHERE id = %s ", (combined_equipment_id, ))
row_combined_equipment = cursor_system.fetchone()
elif combined_equipment_uuid is not None:
cursor_system.execute(
" SELECT id, name, cost_center_id "
" FROM tbl_combined_equipments "
" WHERE uuid = %s ", (combined_equipment_uuid, ))
row_combined_equipment = cursor_system.fetchone()
if row_combined_equipment is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.close()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.close()
if cursor_historical:
cursor_historical.close()
if cnx_historical:
cnx_historical.close()
raise falcon.HTTPError(
falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.COMBINED_EQUIPMENT_NOT_FOUND')
combined_equipment = dict()
combined_equipment['id'] = row_combined_equipment[0]
combined_equipment['name'] = row_combined_equipment[1]
combined_equipment['cost_center_id'] = row_combined_equipment[2]
################################################################################################################
# Step 3: query energy categories
################################################################################################################
energy_category_set = set()
# query energy categories in base period
cursor_energy.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_combined_equipment_input_category_hourly "
" WHERE combined_equipment_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(combined_equipment['id'], base_start_datetime_utc,
base_end_datetime_utc))
rows_energy_categories = cursor_energy.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set.add(row_energy_category[0])
# query energy categories in reporting period
cursor_energy.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_combined_equipment_input_category_hourly "
" WHERE combined_equipment_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(combined_equipment['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_energy_categories = cursor_energy.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set.add(row_energy_category[0])
# query all energy categories in base period and reporting period
cursor_system.execute(
" SELECT id, name, unit_of_measure, kgce, kgco2e "
" FROM tbl_energy_categories "
" ORDER BY id ", )
rows_energy_categories = cursor_system.fetchall()
if rows_energy_categories is None or len(rows_energy_categories) == 0:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.close()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.close()
if cursor_historical:
cursor_historical.close()
if cnx_historical:
cnx_historical.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.ENERGY_CATEGORY_NOT_FOUND')
energy_category_dict = dict()
for row_energy_category in rows_energy_categories:
if row_energy_category[0] in energy_category_set:
energy_category_dict[row_energy_category[0]] = {
"name": row_energy_category[1],
"unit_of_measure": row_energy_category[2],
"kgce": row_energy_category[3],
"kgco2e": row_energy_category[4]
}
################################################################################################################
# Step 4: query associated points
################################################################################################################
point_list = list()
cursor_system.execute(
" SELECT p.id, ep.name, p.units, p.object_type "
" FROM tbl_combined_equipments e, tbl_combined_equipments_parameters ep, tbl_points p "
" WHERE e.id = %s AND e.id = ep.combined_equipment_id AND ep.parameter_type = 'point' "
" AND ep.point_id = p.id "
" ORDER BY p.id ", (combined_equipment['id'], ))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({
"id": row[0],
"name": row[1],
"units": row[2],
"object_type": row[3]
})
################################################################################################################
# Step 5: query associated equipments
################################################################################################################
associated_equipment_list = list()
cursor_system.execute(
" SELECT e.id, e.name "
" FROM tbl_equipments e,tbl_combined_equipments_equipments ee"
" WHERE ee.combined_equipment_id = %s AND e.id = ee.equipment_id"
" ORDER BY id ", (combined_equipment['id'], ))
rows_associated_equipments = cursor_system.fetchall()
if rows_associated_equipments is not None and len(
rows_associated_equipments) > 0:
for row in rows_associated_equipments:
associated_equipment_list.append({
"id": row[0],
"name": row[1]
})
################################################################################################################
# Step 6: query base period energy input
################################################################################################################
base = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
base[energy_category_id] = dict()
base[energy_category_id]['timestamps'] = list()
base[energy_category_id]['sub_averages'] = list()
base[energy_category_id]['sub_maximums'] = list()
base[energy_category_id]['average'] = None
base[energy_category_id]['maximum'] = None
base[energy_category_id]['factor'] = None
cursor_energy.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_combined_equipment_input_category_hourly "
" WHERE combined_equipment_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(combined_equipment['id'], energy_category_id,
base_start_datetime_utc, base_end_datetime_utc))
rows_combined_equipment_hourly = cursor_energy.fetchall()
rows_combined_equipment_periodically, \
base[energy_category_id]['average'], \
base[energy_category_id]['maximum'] = \
utilities.averaging_hourly_data_by_period(rows_combined_equipment_hourly,
base_start_datetime_utc,
base_end_datetime_utc,
period_type)
base[energy_category_id]['factor'] = \
(base[energy_category_id]['average'] / base[energy_category_id]['maximum']
if (base[energy_category_id]['average'] is not None and
base[energy_category_id]['maximum'] is not None and
base[energy_category_id]['maximum'] > Decimal(0.0))
else None)
for row_combined_equipment_periodically in rows_combined_equipment_periodically:
current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'weekly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
base[energy_category_id]['timestamps'].append(
current_datetime)
base[energy_category_id]['sub_averages'].append(
row_combined_equipment_periodically[1])
base[energy_category_id]['sub_maximums'].append(
row_combined_equipment_periodically[2])
################################################################################################################
# Step 7: query reporting period energy input
################################################################################################################
reporting = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
reporting[energy_category_id] = dict()
reporting[energy_category_id]['timestamps'] = list()
reporting[energy_category_id]['sub_averages'] = list()
reporting[energy_category_id]['sub_maximums'] = list()
reporting[energy_category_id]['average'] = None
reporting[energy_category_id]['maximum'] = None
reporting[energy_category_id]['factor'] = None
cursor_energy.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_combined_equipment_input_category_hourly "
" WHERE combined_equipment_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(combined_equipment['id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc))
rows_combined_equipment_hourly = cursor_energy.fetchall()
rows_combined_equipment_periodically, \
reporting[energy_category_id]['average'], \
reporting[energy_category_id]['maximum'] = \
utilities.averaging_hourly_data_by_period(rows_combined_equipment_hourly,
reporting_start_datetime_utc,
reporting_end_datetime_utc,
period_type)
reporting[energy_category_id]['factor'] = \
(reporting[energy_category_id]['average'] / reporting[energy_category_id]['maximum']
if (reporting[energy_category_id]['average'] is not None and
reporting[energy_category_id]['maximum'] is not None and
reporting[energy_category_id]['maximum'] > Decimal(0.0))
else None)
for row_combined_equipment_periodically in rows_combined_equipment_periodically:
current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'weekly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
reporting[energy_category_id]['timestamps'].append(
current_datetime)
reporting[energy_category_id]['sub_averages'].append(
row_combined_equipment_periodically[1])
reporting[energy_category_id]['sub_maximums'].append(
row_combined_equipment_periodically[2])
################################################################################################################
# Step 8: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
energy_category_tariff_dict = \
utilities.get_energy_category_tariffs(combined_equipment['cost_center_id'],
energy_category_id,
reporting_start_datetime_utc,
reporting_end_datetime_utc)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in energy_category_tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
tariff_value_list.append(v)
parameters_data['names'].append(
'TARIFF-' +
energy_category_dict[energy_category_id]['name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 9: query associated points data
################################################################################################################
for point in point_list:
point_values = []
point_timestamps = []
if point['object_type'] == 'ANALOG_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_analog_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'ENERGY_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_energy_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'DIGITAL_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_digital_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
parameters_data['names'].append(point['name'] + ' (' +
point['units'] + ')')
parameters_data['timestamps'].append(point_timestamps)
parameters_data['values'].append(point_values)
################################################################################################################
# Step 10: query associated equipments energy input
################################################################################################################
associated_equipment_data = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
associated_equipment_data[energy_category_id] = dict()
associated_equipment_data[energy_category_id][
'associated_equipment_names'] = list()
associated_equipment_data[energy_category_id][
'average'] = list()
associated_equipment_data[energy_category_id][
'maximum'] = list()
associated_equipment_data[energy_category_id][
'sub_averages'] = list()
associated_equipment_data[energy_category_id][
'sub_maximums'] = list()
for associated_equipment in associated_equipment_list:
associated_equipment_data[energy_category_id][
'associated_equipment_names'].append(
associated_equipment['name'])
cursor_energy.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_equipment_input_category_hourly "
" WHERE equipment_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(associated_equipment['id'], energy_category_id,
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_associated_equipments_hourly = cursor_energy.fetchall(
)
rows_associated_equipment_periodically, \
associated_equipment_data[energy_category_id]['average'], \
associated_equipment_data[energy_category_id]['maximum'] = \
utilities.averaging_hourly_data_by_period(rows_associated_equipments_hourly,
reporting_start_datetime_utc,
reporting_end_datetime_utc,
period_type)
associated_equipment_data[energy_category_id][
'sub_averages'].append(
associated_equipment_data[energy_category_id]
['average'])
associated_equipment_data[energy_category_id][
'sub_maximums'].append(
associated_equipment_data[energy_category_id]
['maximum'])
################################################################################################################
# Step 11: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.close()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.close()
if cursor_historical:
cursor_historical.close()
if cnx_historical:
cnx_historical.close()
result = dict()
result['combined_equipment'] = dict()
result['combined_equipment']['name'] = combined_equipment['name']
result['base_period'] = dict()
result['base_period']['names'] = list()
result['base_period']['units'] = list()
result['base_period']['timestamps'] = list()
result['base_period']['sub_averages'] = list()
result['base_period']['sub_maximums'] = list()
result['base_period']['averages'] = list()
result['base_period']['maximums'] = list()
result['base_period']['factors'] = list()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['base_period']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['base_period']['units'].append(
energy_category_dict[energy_category_id]
['unit_of_measure'])
result['base_period']['timestamps'].append(
base[energy_category_id]['timestamps'])
result['base_period']['sub_averages'].append(
base[energy_category_id]['sub_averages'])
result['base_period']['sub_maximums'].append(
base[energy_category_id]['sub_maximums'])
result['base_period']['averages'].append(
base[energy_category_id]['average'])
result['base_period']['maximums'].append(
base[energy_category_id]['maximum'])
result['base_period']['factors'].append(
base[energy_category_id]['factor'])
result['reporting_period'] = dict()
result['reporting_period']['names'] = list()
result['reporting_period']['energy_category_ids'] = list()
result['reporting_period']['units'] = list()
result['reporting_period']['timestamps'] = list()
result['reporting_period']['sub_averages'] = list()
result['reporting_period']['sub_maximums'] = list()
result['reporting_period']['averages'] = list()
result['reporting_period']['averages_increment_rate'] = list()
result['reporting_period']['maximums'] = list()
result['reporting_period']['maximums_increment_rate'] = list()
result['reporting_period']['factors'] = list()
result['reporting_period']['factors_increment_rate'] = list()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['reporting_period']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['reporting_period']['energy_category_ids'].append(
energy_category_id)
result['reporting_period']['units'].append(
energy_category_dict[energy_category_id]
['unit_of_measure'])
result['reporting_period']['timestamps'].append(
reporting[energy_category_id]['timestamps'])
result['reporting_period']['sub_averages'].append(
reporting[energy_category_id]['sub_averages'])
result['reporting_period']['sub_maximums'].append(
reporting[energy_category_id]['sub_maximums'])
result['reporting_period']['averages'].append(
reporting[energy_category_id]['average'])
result['reporting_period']['averages_increment_rate'].append(
(reporting[energy_category_id]['average'] -
base[energy_category_id]['average']) /
base[energy_category_id]['average'] if (
reporting[energy_category_id]['average'] is not None
and base[energy_category_id]['average'] is not None
and base[energy_category_id]['average'] > Decimal(0.0)
) else None)
result['reporting_period']['maximums'].append(
reporting[energy_category_id]['maximum'])
result['reporting_period']['maximums_increment_rate'].append(
(reporting[energy_category_id]['maximum'] -
base[energy_category_id]['maximum']) /
base[energy_category_id]['maximum'] if (
reporting[energy_category_id]['maximum'] is not None
and base[energy_category_id]['maximum'] is not None
and base[energy_category_id]['maximum'] > Decimal(0.0)
) else None)
result['reporting_period']['factors'].append(
reporting[energy_category_id]['factor'])
result['reporting_period']['factors_increment_rate'].append(
(reporting[energy_category_id]['factor'] -
base[energy_category_id]['factor']) /
base[energy_category_id]['factor'] if (
reporting[energy_category_id]['factor'] is not None
and base[energy_category_id]['factor'] is not None
and base[energy_category_id]['factor'] > Decimal(0.0)
) else None)
result['parameters'] = {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
}
result['associated_equipment'] = dict()
result['associated_equipment']['energy_category_names'] = list()
result['associated_equipment']['units'] = list()
result['associated_equipment'][
'associated_equipment_names_array'] = list()
result['associated_equipment']['sub_averages_array'] = list()
result['associated_equipment']['sub_maximums_array'] = list()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['associated_equipment']['energy_category_names'].append(
energy_category_dict[energy_category_id]['name'])
result['associated_equipment']['units'].append(
energy_category_dict[energy_category_id]
['unit_of_measure'])
result['associated_equipment'][
'associated_equipment_names_array'].append(
associated_equipment_data[energy_category_id]
['associated_equipment_names'])
result['associated_equipment']['sub_averages_array'].append(
associated_equipment_data[energy_category_id]
['sub_averages'])
result['associated_equipment']['sub_maximums_array'].append(
associated_equipment_data[energy_category_id]
['sub_maximums'])
# export result to Excel file and then encode the file to base64 string
result[
'excel_bytes_base64'] = excelexporters.combinedequipmentload.export(
result, combined_equipment['name'],
reporting_start_datetime_local, reporting_end_datetime_local,
period_type)
resp.text = json.dumps(result)
def on_get(req, resp):
print(req.params)
tenant_id = req.params.get('tenantid')
reporting_start_datetime_local = req.params.get(
'reportingperiodstartdatetime')
reporting_end_datetime_local = req.params.get(
'reportingperiodenddatetime')
period_type = 'daily'
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if tenant_id is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_TENANT_ID')
else:
tenant_id = str.strip(tenant_id)
if not tenant_id.isdigit() or int(tenant_id) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_TENANT_ID')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
if reporting_start_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_BEGINS_DATETIME")
else:
reporting_start_datetime_local = str.strip(
reporting_start_datetime_local)
try:
reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_BEGINS_DATETIME")
if reporting_end_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_ENDS_DATETIME")
else:
reporting_end_datetime_local = str.strip(
reporting_end_datetime_local)
try:
reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_ENDS_DATETIME")
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_ENDS_DATETIME')
################################################################################################################
# Step 2: query the tenant
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_energy = mysql.connector.connect(**config.myems_energy_db)
cursor_energy = cnx_energy.cursor()
cnx_billing = mysql.connector.connect(**config.myems_billing_db)
cursor_billing = cnx_billing.cursor()
cursor_system.execute(
" SELECT t.id, t.name, t.buildings, t.floors, t.rooms, t.lease_number, "
" c.email, c.phone, cost_center_id "
" FROM tbl_tenants t, tbl_contacts c "
" WHERE t.id = %s AND t.contact_id = c.id ", (tenant_id, ))
row_tenant = cursor_system.fetchone()
if row_tenant is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cursor_billing:
cursor_billing.close()
if cnx_billing:
cnx_billing.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.TENANT_NOT_FOUND')
tenant = dict()
tenant['id'] = row_tenant[0]
tenant['name'] = row_tenant[1]
tenant['buildings'] = row_tenant[2]
tenant['floors'] = row_tenant[3]
tenant['rooms'] = row_tenant[4]
tenant['lease_number'] = row_tenant[5]
tenant['email'] = row_tenant[6]
tenant['phone'] = row_tenant[7]
tenant['cost_center_id'] = row_tenant[8]
################################################################################################################
# Step 3: query energy categories
################################################################################################################
energy_category_set = set()
# query energy categories in reporting period
cursor_billing.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_tenant_input_category_hourly "
" WHERE tenant_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(tenant['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_energy_categories = cursor_billing.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set.add(row_energy_category[0])
# query all energy categories
cursor_system.execute(
" SELECT id, name, unit_of_measure, kgce, kgco2e "
" FROM tbl_energy_categories "
" ORDER BY id ", )
rows_energy_categories = cursor_system.fetchall()
if rows_energy_categories is None or len(rows_energy_categories) == 0:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cursor_billing:
cursor_billing.close()
if cnx_billing:
cnx_billing.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.ENERGY_CATEGORY_NOT_FOUND')
energy_category_dict = dict()
for row_energy_category in rows_energy_categories:
if row_energy_category[0] in energy_category_set:
energy_category_dict[row_energy_category[0]] = {
"name": row_energy_category[1],
"unit_of_measure": row_energy_category[2],
"kgce": row_energy_category[3],
"kgco2e": row_energy_category[4]
}
################################################################################################################
# Step 4: query reporting period energy input
################################################################################################################
reporting_input = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
reporting_input[energy_category_id] = dict()
reporting_input[energy_category_id]['timestamps'] = list()
reporting_input[energy_category_id]['values'] = list()
reporting_input[energy_category_id]['subtotal'] = Decimal(0.0)
cursor_energy.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_tenant_input_category_hourly "
" WHERE tenant_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(tenant['id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc))
rows_tenant_hourly = cursor_energy.fetchall()
rows_tenant_periodically = utilities.aggregate_hourly_data_by_period(
rows_tenant_hourly, reporting_start_datetime_utc,
reporting_end_datetime_utc, period_type)
for row_tenant_periodically in rows_tenant_periodically:
current_datetime_local = row_tenant_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
actual_value = Decimal(0.0) if row_tenant_periodically[
1] is None else row_tenant_periodically[1]
reporting_input[energy_category_id]['timestamps'].append(
current_datetime)
reporting_input[energy_category_id]['values'].append(
actual_value)
reporting_input[energy_category_id][
'subtotal'] += actual_value
################################################################################################################
# Step 5: query reporting period energy cost
################################################################################################################
reporting_cost = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
reporting_cost[energy_category_id] = dict()
reporting_cost[energy_category_id]['timestamps'] = list()
reporting_cost[energy_category_id]['values'] = list()
reporting_cost[energy_category_id]['subtotal'] = Decimal(0.0)
cursor_billing.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_tenant_input_category_hourly "
" WHERE tenant_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(tenant['id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc))
rows_tenant_hourly = cursor_billing.fetchall()
rows_tenant_periodically = utilities.aggregate_hourly_data_by_period(
rows_tenant_hourly, reporting_start_datetime_utc,
reporting_end_datetime_utc, period_type)
for row_tenant_periodically in rows_tenant_periodically:
current_datetime_local = row_tenant_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
actual_value = Decimal(0.0) if row_tenant_periodically[
1] is None else row_tenant_periodically[1]
reporting_cost[energy_category_id]['timestamps'].append(
current_datetime)
reporting_cost[energy_category_id]['values'].append(
actual_value)
reporting_cost[energy_category_id][
'subtotal'] += actual_value
################################################################################################################
# Step 6: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
energy_category_tariff_dict = utilities.get_energy_category_tariffs(
tenant['cost_center_id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in energy_category_tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
tariff_value_list.append(v)
parameters_data['names'].append(
'TARIFF-' +
energy_category_dict[energy_category_id]['name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 7: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cursor_billing:
cursor_billing.close()
if cnx_billing:
cnx_billing.disconnect()
result = dict()
result['tenant'] = dict()
result['tenant']['name'] = tenant['name']
result['tenant']['buildings'] = tenant['buildings']
result['tenant']['floors'] = tenant['floors']
result['tenant']['rooms'] = tenant['rooms']
result['tenant']['lease_number'] = tenant['lease_number']
result['tenant']['email'] = tenant['email']
result['tenant']['phone'] = tenant['phone']
result['reporting_period'] = dict()
result['reporting_period']['names'] = list()
result['reporting_period']['energy_category_ids'] = list()
result['reporting_period']['units'] = list()
result['reporting_period']['subtotals_input'] = list()
result['reporting_period']['subtotals_cost'] = list()
result['reporting_period']['total_cost'] = Decimal(0.0)
result['reporting_period']['currency_unit'] = config.currency_unit
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['reporting_period']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['reporting_period']['energy_category_ids'].append(
energy_category_id)
result['reporting_period']['units'].append(
energy_category_dict[energy_category_id]
['unit_of_measure'])
result['reporting_period']['subtotals_input'].append(
reporting_input[energy_category_id]['subtotal'])
result['reporting_period']['subtotals_cost'].append(
reporting_cost[energy_category_id]['subtotal'])
result['reporting_period']['total_cost'] += reporting_cost[
energy_category_id]['subtotal']
result['parameters'] = {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
}
resp.body = json.dumps(result)
def on_get(req, resp):
print(req.params)
tenant_id = req.params.get('tenantid')
period_type = req.params.get('periodtype')
base_start_datetime_local = req.params.get('baseperiodstartdatetime')
base_end_datetime_local = req.params.get('baseperiodenddatetime')
reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if tenant_id is None:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_TENANT_ID')
else:
tenant_id = str.strip(tenant_id)
if not tenant_id.isdigit() or int(tenant_id) <= 0:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_TENANT_ID')
if period_type is None:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
else:
period_type = str.strip(period_type)
if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
base_start_datetime_utc = None
if base_start_datetime_local is not None and len(str.strip(base_start_datetime_local)) > 0:
base_start_datetime_local = str.strip(base_start_datetime_local)
try:
base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_START_DATETIME")
base_end_datetime_utc = None
if base_end_datetime_local is not None and len(str.strip(base_end_datetime_local)) > 0:
base_end_datetime_local = str.strip(base_end_datetime_local)
try:
base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_END_DATETIME")
if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
base_start_datetime_utc >= base_end_datetime_utc:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_BASE_PERIOD_END_DATETIME')
if reporting_start_datetime_local is None:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
else:
reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
try:
reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
if reporting_end_datetime_local is None:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
else:
reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
try:
reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
################################################################################################################
# Step 2: query the tenant
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_energy = mysql.connector.connect(**config.myems_energy_db)
cursor_energy = cnx_energy.cursor()
cnx_historical = mysql.connector.connect(**config.myems_historical_db)
cursor_historical = cnx_historical.cursor()
cursor_system.execute(" SELECT id, name, area, cost_center_id "
" FROM tbl_tenants "
" WHERE id = %s ", (tenant_id,))
row_tenant = cursor_system.fetchone()
if row_tenant is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cnx_historical:
cnx_historical.close()
if cursor_historical:
cursor_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.TENANT_NOT_FOUND')
tenant = dict()
tenant['id'] = row_tenant[0]
tenant['name'] = row_tenant[1]
tenant['area'] = row_tenant[2]
tenant['cost_center_id'] = row_tenant[3]
################################################################################################################
# Step 3: query energy items
################################################################################################################
energy_item_set = set()
# query energy items in base period
cursor_energy.execute(" SELECT DISTINCT(energy_item_id) "
" FROM tbl_tenant_input_item_hourly "
" WHERE tenant_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(tenant['id'], base_start_datetime_utc, base_end_datetime_utc))
rows_energy_items = cursor_energy.fetchall()
if rows_energy_items is not None or len(rows_energy_items) > 0:
for row_item in rows_energy_items:
energy_item_set.add(row_item[0])
# query energy items in reporting period
cursor_energy.execute(" SELECT DISTINCT(energy_item_id) "
" FROM tbl_tenant_input_item_hourly "
" WHERE tenant_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(tenant['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
rows_energy_items = cursor_energy.fetchall()
if rows_energy_items is not None or len(rows_energy_items) > 0:
for row_item in rows_energy_items:
energy_item_set.add(row_item[0])
# query all energy items in base period and reporting period
cursor_system.execute(" SELECT ei.id, ei.name, ei.energy_category_id, "
" ec.name AS energy_category_name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
" FROM tbl_energy_items ei, tbl_energy_categories ec "
" WHERE ei.energy_category_id = ec.id "
" ORDER BY ei.id ", )
rows_energy_items = cursor_system.fetchall()
if rows_energy_items is None or len(rows_energy_items) == 0:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cnx_historical:
cnx_historical.close()
if cursor_historical:
cursor_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.ENERGY_ITEM_NOT_FOUND')
energy_item_dict = dict()
for row_energy_item in rows_energy_items:
if row_energy_item[0] in energy_item_set:
energy_item_dict[row_energy_item[0]] = {"name": row_energy_item[1],
"energy_category_id": row_energy_item[2],
"energy_category_name": row_energy_item[3],
"unit_of_measure": row_energy_item[4],
"kgce": row_energy_item[5],
"kgco2e": row_energy_item[6]}
################################################################################################################
# Step 4: query associated sensors
################################################################################################################
point_list = list()
cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type "
" FROM tbl_tenants t, tbl_sensors s, tbl_tenants_sensors ts, "
" tbl_points p, tbl_sensors_points sp "
" WHERE t.id = %s AND t.id = ts.tenant_id AND ts.sensor_id = s.id "
" AND s.id = sp.sensor_id AND sp.point_id = p.id "
" ORDER BY p.id ", (tenant['id'], ))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
################################################################################################################
# Step 5: query associated points
################################################################################################################
cursor_system.execute(" SELECT po.id, po.name, po.units, po.object_type "
" FROM tbl_tenants sp, tbl_tenants_points sppo, tbl_points po "
" WHERE sp.id = %s AND sp.id = sppo.tenant_id AND sppo.point_id = po.id "
" ORDER BY po.id ", (tenant['id'], ))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
################################################################################################################
# Step 6: query base period energy input
################################################################################################################
base = dict()
if energy_item_set is not None and len(energy_item_set) > 0:
for energy_item_id in energy_item_set:
base[energy_item_id] = dict()
base[energy_item_id]['timestamps'] = list()
base[energy_item_id]['values'] = list()
base[energy_item_id]['subtotal'] = Decimal(0.0)
cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
" FROM tbl_tenant_input_item_hourly "
" WHERE tenant_id = %s "
" AND energy_item_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(tenant['id'],
energy_item_id,
base_start_datetime_utc,
base_end_datetime_utc))
rows_tenant_hourly = cursor_energy.fetchall()
rows_tenant_periodically = utilities.aggregate_hourly_data_by_period(rows_tenant_hourly,
base_start_datetime_utc,
base_end_datetime_utc,
period_type)
for row_tenant_periodically in rows_tenant_periodically:
current_datetime_local = row_tenant_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
actual_value = Decimal(0.0) if row_tenant_periodically[1] is None else row_tenant_periodically[1]
base[energy_item_id]['timestamps'].append(current_datetime)
base[energy_item_id]['values'].append(actual_value)
base[energy_item_id]['subtotal'] += actual_value
################################################################################################################
# Step 7: query reporting period energy input
################################################################################################################
reporting = dict()
if energy_item_set is not None and len(energy_item_set) > 0:
for energy_item_id in energy_item_set:
reporting[energy_item_id] = dict()
reporting[energy_item_id]['timestamps'] = list()
reporting[energy_item_id]['values'] = list()
reporting[energy_item_id]['subtotal'] = Decimal(0.0)
reporting[energy_item_id]['toppeak'] = Decimal(0.0)
reporting[energy_item_id]['onpeak'] = Decimal(0.0)
reporting[energy_item_id]['midpeak'] = Decimal(0.0)
reporting[energy_item_id]['offpeak'] = Decimal(0.0)
cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
" FROM tbl_tenant_input_item_hourly "
" WHERE tenant_id = %s "
" AND energy_item_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(tenant['id'],
energy_item_id,
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_tenant_hourly = cursor_energy.fetchall()
rows_tenant_periodically = utilities.aggregate_hourly_data_by_period(rows_tenant_hourly,
reporting_start_datetime_utc,
reporting_end_datetime_utc,
period_type)
for row_tenant_periodically in rows_tenant_periodically:
current_datetime_local = row_tenant_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
actual_value = Decimal(0.0) if row_tenant_periodically[1] is None else row_tenant_periodically[1]
reporting[energy_item_id]['timestamps'].append(current_datetime)
reporting[energy_item_id]['values'].append(actual_value)
reporting[energy_item_id]['subtotal'] += actual_value
energy_category_tariff_dict = \
utilities.get_energy_category_peak_types(tenant['cost_center_id'],
energy_item_dict[energy_item_id]['energy_category_id'],
reporting_start_datetime_utc,
reporting_end_datetime_utc)
for row in rows_tenant_hourly:
peak_type = energy_category_tariff_dict.get(row[0], None)
if peak_type == 'toppeak':
reporting[energy_item_id]['toppeak'] += row[1]
elif peak_type == 'onpeak':
reporting[energy_item_id]['onpeak'] += row[1]
elif peak_type == 'midpeak':
reporting[energy_item_id]['midpeak'] += row[1]
elif peak_type == 'offpeak':
reporting[energy_item_id]['offpeak'] += row[1]
################################################################################################################
# Step 8: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
if energy_item_set is not None and len(energy_item_set) > 0:
for energy_item_id in energy_item_set:
energy_category_tariff_dict = \
utilities.get_energy_category_tariffs(tenant['cost_center_id'],
energy_item_dict[energy_item_id]['energy_category_id'],
reporting_start_datetime_utc,
reporting_end_datetime_utc)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in energy_category_tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
tariff_value_list.append(v)
parameters_data['names'].append('TARIFF-' + energy_item_dict[energy_item_id]['name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 9: query associated sensors and points data
################################################################################################################
for point in point_list:
point_values = []
point_timestamps = []
if point['object_type'] == 'ANALOG_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_analog_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(query, (point['id'],
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'ENERGY_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_energy_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(query, (point['id'],
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'DIGITAL_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_digital_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s ")
cursor_historical.execute(query, (point['id'],
reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
parameters_data['timestamps'].append(point_timestamps)
parameters_data['values'].append(point_values)
################################################################################################################
# Step 10: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
result = dict()
result['tenant'] = dict()
result['tenant']['name'] = tenant['name']
result['tenant']['area'] = tenant['area']
result['base_period'] = dict()
result['base_period']['names'] = list()
result['base_period']['units'] = list()
result['base_period']['timestamps'] = list()
result['base_period']['values'] = list()
result['base_period']['subtotals'] = list()
if energy_item_set is not None and len(energy_item_set) > 0:
for energy_item_id in energy_item_set:
result['base_period']['names'].append(energy_item_dict[energy_item_id]['name'])
result['base_period']['units'].append(energy_item_dict[energy_item_id]['unit_of_measure'])
result['base_period']['timestamps'].append(base[energy_item_id]['timestamps'])
result['base_period']['values'].append(base[energy_item_id]['values'])
result['base_period']['subtotals'].append(base[energy_item_id]['subtotal'])
result['reporting_period'] = dict()
result['reporting_period']['names'] = list()
result['reporting_period']['energy_item_ids'] = list()
result['reporting_period']['energy_category_names'] = list()
result['reporting_period']['energy_category_ids'] = list()
result['reporting_period']['units'] = list()
result['reporting_period']['timestamps'] = list()
result['reporting_period']['values'] = list()
result['reporting_period']['subtotals'] = list()
result['reporting_period']['subtotals_per_unit_area'] = list()
result['reporting_period']['toppeaks'] = list()
result['reporting_period']['onpeaks'] = list()
result['reporting_period']['midpeaks'] = list()
result['reporting_period']['offpeaks'] = list()
result['reporting_period']['increment_rates'] = list()
if energy_item_set is not None and len(energy_item_set) > 0:
for energy_item_id in energy_item_set:
result['reporting_period']['names'].append(energy_item_dict[energy_item_id]['name'])
result['reporting_period']['energy_item_ids'].append(energy_item_id)
result['reporting_period']['energy_category_names'].append(
energy_item_dict[energy_item_id]['energy_category_name'])
result['reporting_period']['energy_category_ids'].append(
energy_item_dict[energy_item_id]['energy_category_id'])
result['reporting_period']['units'].append(energy_item_dict[energy_item_id]['unit_of_measure'])
result['reporting_period']['timestamps'].append(reporting[energy_item_id]['timestamps'])
result['reporting_period']['values'].append(reporting[energy_item_id]['values'])
result['reporting_period']['subtotals'].append(reporting[energy_item_id]['subtotal'])
result['reporting_period']['subtotals_per_unit_area'].append(
reporting[energy_item_id]['subtotal'] / tenant['area'] if tenant['area'] > 0.0 else None)
result['reporting_period']['toppeaks'].append(reporting[energy_item_id]['toppeak'])
result['reporting_period']['onpeaks'].append(reporting[energy_item_id]['onpeak'])
result['reporting_period']['midpeaks'].append(reporting[energy_item_id]['midpeak'])
result['reporting_period']['offpeaks'].append(reporting[energy_item_id]['offpeak'])
result['reporting_period']['increment_rates'].append(
(reporting[energy_item_id]['subtotal'] - base[energy_item_id]['subtotal']) /
base[energy_item_id]['subtotal']
if base[energy_item_id]['subtotal'] > 0.0 else None)
result['parameters'] = {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
}
resp.body = json.dumps(result)
def on_get(req, resp):
print(req.params)
meter_id = req.params.get('meterid')
reporting_period_start_datetime_local = req.params.get(
'reportingperiodstartdatetime')
reporting_period_end_datetime_local = req.params.get(
'reportingperiodenddatetime')
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if meter_id is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_METER_ID')
else:
meter_id = str.strip(meter_id)
if not meter_id.isdigit() or int(meter_id) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_METER_ID')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
if reporting_period_start_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_BEGINS_DATETIME")
else:
reporting_period_start_datetime_local = str.strip(
reporting_period_start_datetime_local)
try:
reporting_start_datetime_utc = datetime.strptime(
reporting_period_start_datetime_local, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_BEGINS_DATETIME")
reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
if reporting_period_end_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_ENDS_DATETIME")
else:
reporting_period_end_datetime_local = str.strip(
reporting_period_end_datetime_local)
try:
reporting_end_datetime_utc = datetime.strptime(
reporting_period_end_datetime_local, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_ENDS_DATETIME")
reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_ENDS_DATETIME')
################################################################################################################
# Step 2: query the meter and energy category
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_historical = mysql.connector.connect(**config.myems_historical_db)
cursor_historical = cnx_historical.cursor()
cursor_system.execute(
" SELECT m.id, m.name, m.cost_center_id, m.energy_category_id, "
" ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
" FROM tbl_meters m, tbl_energy_categories ec "
" WHERE m.id = %s AND m.energy_category_id = ec.id ", (meter_id, ))
row_meter = cursor_system.fetchone()
if row_meter is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_historical:
cursor_historical.close()
if cnx_historical:
cnx_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.METER_NOT_FOUND')
meter = dict()
meter['id'] = row_meter[0]
meter['name'] = row_meter[1]
meter['cost_center_id'] = row_meter[2]
meter['energy_category_id'] = row_meter[3]
meter['energy_category_name'] = row_meter[4]
meter['unit_of_measure'] = row_meter[5]
meter['kgce'] = row_meter[6]
meter['kgco2e'] = row_meter[7]
################################################################################################################
# Step 3: query associated points
################################################################################################################
point_list = list()
cursor_system.execute(
" SELECT p.id, p.name, p.units, p.object_type "
" FROM tbl_meters m, tbl_meters_points mp, tbl_points p "
" WHERE m.id = %s AND m.id = mp.meter_id AND mp.point_id = p.id "
" ORDER BY p.id ", (meter['id'], ))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({
"id": row[0],
"name": row[1],
"units": row[2],
"object_type": row[3]
})
################################################################################################################
# Step 4: query reporting period points trends
################################################################################################################
reporting = dict()
reporting['names'] = list()
reporting['timestamps'] = list()
reporting['values'] = list()
for point in point_list:
point_values = []
point_timestamps = []
if point['object_type'] == 'ANALOG_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_analog_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'ENERGY_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_energy_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'DIGITAL_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_digital_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
reporting['names'].append(point['name'] + ' (' + point['units'] +
')')
reporting['timestamps'].append(point_timestamps)
reporting['values'].append(point_values)
################################################################################################################
# Step 5: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
tariff_dict = utilities.get_energy_category_tariffs(
meter['cost_center_id'], meter['energy_category_id'],
reporting_start_datetime_utc, reporting_end_datetime_utc)
print(tariff_dict)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19])
tariff_value_list.append(v)
parameters_data['names'].append('TARIFF-' +
meter['energy_category_name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 6: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_historical:
cursor_historical.close()
if cnx_historical:
cnx_historical.disconnect()
result = {
"meter": {
"cost_center_id": meter['cost_center_id'],
"energy_category_id": meter['energy_category_id'],
"energy_category_name": meter['energy_category_name'],
"unit_of_measure": meter['unit_of_measure'],
"kgce": meter['kgce'],
"kgco2e": meter['kgco2e'],
},
"reporting_period": {
"names": reporting['names'],
"timestamps": reporting['timestamps'],
"values": reporting['values'],
},
"parameters": {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
},
}
# export result to Excel file and then encode the file to base64 string
result['excel_bytes_base64'] = excelexporters.metertrend.export(
result, meter['name'], reporting_period_start_datetime_local,
reporting_period_end_datetime_local, None)
resp.body = json.dumps(result)
def on_get(req, resp):
print(req.params)
offline_meter_id = req.params.get('offlinemeterid')
period_type = req.params.get('periodtype')
base_period_start_datetime = req.params.get('baseperiodstartdatetime')
base_period_end_datetime = req.params.get('baseperiodenddatetime')
reporting_period_start_datetime = req.params.get(
'reportingperiodstartdatetime')
reporting_period_end_datetime = req.params.get(
'reportingperiodenddatetime')
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if offline_meter_id is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_OFFLINE_METER_ID')
else:
offline_meter_id = str.strip(offline_meter_id)
if not offline_meter_id.isdigit() or int(offline_meter_id) <= 0:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_OFFLINE_METER_ID')
if period_type is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
else:
period_type = str.strip(period_type)
if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
base_start_datetime_utc = None
if base_period_start_datetime is not None and len(
str.strip(base_period_start_datetime)) > 0:
base_period_start_datetime = str.strip(base_period_start_datetime)
try:
base_start_datetime_utc = datetime.strptime(
base_period_start_datetime, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_START_DATETIME")
base_start_datetime_utc = base_start_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
base_end_datetime_utc = None
if base_period_end_datetime is not None and len(
str.strip(base_period_end_datetime)) > 0:
base_period_end_datetime = str.strip(base_period_end_datetime)
try:
base_end_datetime_utc = datetime.strptime(
base_period_end_datetime, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_END_DATETIME")
base_end_datetime_utc = base_end_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
base_start_datetime_utc >= base_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_BASE_PERIOD_END_DATETIME')
if reporting_period_start_datetime is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
else:
reporting_period_start_datetime = str.strip(
reporting_period_start_datetime)
try:
reporting_start_datetime_utc = datetime.strptime(
reporting_period_start_datetime, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
if reporting_period_end_datetime is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
else:
reporting_period_end_datetime = str.strip(
reporting_period_end_datetime)
try:
reporting_end_datetime_utc = datetime.strptime(
reporting_period_end_datetime, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
################################################################################################################
# Step 2: query the offline meter and energy category
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_energy = mysql.connector.connect(**config.myems_energy_db)
cursor_energy = cnx_energy.cursor()
cursor_system.execute(
" SELECT m.id, m.name, m.cost_center_id, m.energy_category_id, "
" ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
" FROM tbl_offline_meters m, tbl_energy_categories ec "
" WHERE m.id = %s AND m.energy_category_id = ec.id ",
(offline_meter_id, ))
row_offline_meter = cursor_system.fetchone()
if row_offline_meter is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.OFFLINE_METER_NOT_FOUND')
offline_meter = dict()
offline_meter['id'] = row_offline_meter[0]
offline_meter['name'] = row_offline_meter[1]
offline_meter['cost_center_id'] = row_offline_meter[2]
offline_meter['energy_category_id'] = row_offline_meter[3]
offline_meter['energy_category_name'] = row_offline_meter[4]
offline_meter['unit_of_measure'] = row_offline_meter[5]
offline_meter['kgce'] = row_offline_meter[6]
offline_meter['kgco2e'] = row_offline_meter[7]
################################################################################################################
# Step 3: query base period energy consumption
################################################################################################################
query = (" SELECT start_datetime_utc, actual_value "
" FROM tbl_offline_meter_hourly "
" WHERE offline_meter_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ")
cursor_energy.execute(query,
(offline_meter['id'], base_start_datetime_utc,
base_end_datetime_utc))
rows_offline_meter_hourly = cursor_energy.fetchall()
rows_offline_meter_periodically = utilities.aggregate_hourly_data_by_period(
rows_offline_meter_hourly, base_start_datetime_utc,
base_end_datetime_utc, period_type)
base = dict()
base['timestamps'] = list()
base['values'] = list()
base['total_in_category'] = Decimal(0.0)
base['total_in_kgce'] = Decimal(0.0)
base['total_in_kgco2e'] = Decimal(0.0)
for row_offline_meter_periodically in rows_offline_meter_periodically:
current_datetime_local = row_offline_meter_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
actual_value = Decimal(0.0) if row_offline_meter_periodically[1] is None \
else row_offline_meter_periodically[1]
base['timestamps'].append(current_datetime)
base['values'].append(actual_value)
base['total_in_category'] += actual_value
base['total_in_kgce'] += actual_value * offline_meter['kgce']
base['total_in_kgco2e'] += actual_value * offline_meter['kgco2e']
################################################################################################################
# Step 4: query reporting period energy consumption
################################################################################################################
query = (" SELECT start_datetime_utc, actual_value "
" FROM tbl_offline_meter_hourly "
" WHERE offline_meter_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ")
cursor_energy.execute(
query, (offline_meter['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_offline_meter_hourly = cursor_energy.fetchall()
rows_offline_meter_periodically = utilities.aggregate_hourly_data_by_period(
rows_offline_meter_hourly, reporting_start_datetime_utc,
reporting_end_datetime_utc, period_type)
reporting = dict()
reporting['timestamps'] = list()
reporting['values'] = list()
reporting['total_in_category'] = Decimal(0.0)
reporting['total_in_kgce'] = Decimal(0.0)
reporting['total_in_kgco2e'] = Decimal(0.0)
for row_offline_meter_periodically in rows_offline_meter_periodically:
current_datetime_local = row_offline_meter_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
actual_value = Decimal(0.0) if row_offline_meter_periodically[1] is None \
else row_offline_meter_periodically[1]
reporting['timestamps'].append(current_datetime)
reporting['values'].append(actual_value)
reporting['total_in_category'] += actual_value
reporting['total_in_kgce'] += actual_value * offline_meter['kgce']
reporting[
'total_in_kgco2e'] += actual_value * offline_meter['kgco2e']
################################################################################################################
# Step 5: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
tariff_dict = utilities.get_energy_category_tariffs(
offline_meter['cost_center_id'],
offline_meter['energy_category_id'], reporting_start_datetime_utc,
reporting_end_datetime_utc)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19])
tariff_value_list.append(v)
parameters_data['names'].append('TARIFF-' +
offline_meter['energy_category_name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 6: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
result = {
"offline_meter": {
"cost_center_id": offline_meter['cost_center_id'],
"energy_category_id": offline_meter['energy_category_id'],
"energy_category_name": offline_meter['energy_category_name'],
"unit_of_measure": offline_meter['unit_of_measure'],
"kgce": offline_meter['kgce'],
"kgco2e": offline_meter['kgco2e'],
},
"base_period": {
"total_in_category": base['total_in_category'],
"total_in_kgce": base['total_in_kgce'],
"total_in_kgco2e": base['total_in_kgco2e'],
"timestamps": base['timestamps'],
"values": base['values'],
},
"reporting_period": {
"increment_rate":
(reporting['total_in_category'] - base['total_in_category']) /
base['total_in_category']
if base['total_in_category'] > 0 else None,
"total_in_category":
reporting['total_in_category'],
"total_in_kgce":
reporting['total_in_kgce'],
"total_in_kgco2e":
reporting['total_in_kgco2e'],
"timestamps":
reporting['timestamps'],
"values":
reporting['values'],
},
"parameters": {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
},
}
# export result to Excel file and then encode the file to base64 string
result['excel_bytes_base64'] = \
excelexporters.offlinemeterenergy.export(result,
offline_meter['name'],
reporting_period_start_datetime,
reporting_period_end_datetime,
period_type)
resp.body = json.dumps(result)
def on_get(req, resp):
print(req.params)
equipment_id = req.params.get('equipmentid')
period_type = req.params.get('periodtype')
base_start_datetime_local = req.params.get('baseperiodstartdatetime')
base_end_datetime_local = req.params.get('baseperiodenddatetime')
reporting_start_datetime_local = req.params.get(
'reportingperiodstartdatetime')
reporting_end_datetime_local = req.params.get(
'reportingperiodenddatetime')
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if equipment_id is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_EQUIPMENT_ID')
else:
equipment_id = str.strip(equipment_id)
if not equipment_id.isdigit() or int(equipment_id) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_EQUIPMENT_ID')
if period_type is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
else:
period_type = str.strip(period_type)
if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
base_start_datetime_utc = None
if base_start_datetime_local is not None and len(
str.strip(base_start_datetime_local)) > 0:
base_start_datetime_local = str.strip(base_start_datetime_local)
try:
base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_START_DATETIME")
base_end_datetime_utc = None
if base_end_datetime_local is not None and len(
str.strip(base_end_datetime_local)) > 0:
base_end_datetime_local = str.strip(base_end_datetime_local)
try:
base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_END_DATETIME")
if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
base_start_datetime_utc >= base_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_BASE_PERIOD_END_DATETIME')
if reporting_start_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
else:
reporting_start_datetime_local = str.strip(
reporting_start_datetime_local)
try:
reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
if reporting_end_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
else:
reporting_end_datetime_local = str.strip(
reporting_end_datetime_local)
try:
reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
################################################################################################################
# Step 2: query the equipment
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_energy = mysql.connector.connect(**config.myems_energy_db)
cursor_energy = cnx_energy.cursor()
cnx_historical = mysql.connector.connect(**config.myems_historical_db)
cursor_historical = cnx_historical.cursor()
cursor_system.execute(
" SELECT id, name, cost_center_id "
" FROM tbl_equipments "
" WHERE id = %s ", (equipment_id, ))
row_equipment = cursor_system.fetchone()
if row_equipment is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cnx_historical:
cnx_historical.close()
if cursor_historical:
cursor_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.EQUIPMENT_NOT_FOUND')
equipment = dict()
equipment['id'] = row_equipment[0]
equipment['name'] = row_equipment[1]
equipment['cost_center_id'] = row_equipment[2]
################################################################################################################
# Step 3: query input energy categories and output energy categories
################################################################################################################
energy_category_set_input = set()
energy_category_set_output = set()
# query input energy categories in base period
cursor_energy.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_equipment_input_category_hourly "
" WHERE equipment_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
rows_energy_categories = cursor_energy.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set_input.add(row_energy_category[0])
# query input energy categories in reporting period
cursor_energy.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_equipment_input_category_hourly "
" WHERE equipment_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(equipment['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_energy_categories = cursor_energy.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set_input.add(row_energy_category[0])
# query output energy categories in base period
cursor_energy.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_equipment_output_category_hourly "
" WHERE equipment_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
rows_energy_categories = cursor_energy.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set_output.add(row_energy_category[0])
# query output energy categories in reporting period
cursor_energy.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_equipment_output_category_hourly "
" WHERE equipment_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(equipment['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_energy_categories = cursor_energy.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set_output.add(row_energy_category[0])
# query properties of all energy categories above
cursor_system.execute(
" SELECT id, name, unit_of_measure, kgce, kgco2e "
" FROM tbl_energy_categories "
" ORDER BY id ", )
rows_energy_categories = cursor_system.fetchall()
if rows_energy_categories is None or len(rows_energy_categories) == 0:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
if cnx_historical:
cnx_historical.close()
if cursor_historical:
cursor_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.ENERGY_CATEGORY_NOT_FOUND')
energy_category_dict = dict()
for row_energy_category in rows_energy_categories:
if row_energy_category[0] in energy_category_set_input or \
row_energy_category[0] in energy_category_set_output:
energy_category_dict[row_energy_category[0]] = {
"name": row_energy_category[1],
"unit_of_measure": row_energy_category[2],
"kgce": row_energy_category[3],
"kgco2e": row_energy_category[4]
}
################################################################################################################
# Step 4: query associated points
################################################################################################################
point_list = list()
cursor_system.execute(
" SELECT p.id, p.name, p.units, p.object_type "
" FROM tbl_equipments e, tbl_equipments_parameters ep, tbl_points p "
" WHERE e.id = %s AND e.id = ep.equipment_id AND ep.parameter_type = 'point' "
" AND ep.point_id = p.id "
" ORDER BY p.id ", (equipment['id'], ))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({
"id": row[0],
"name": row[1],
"units": row[2],
"object_type": row[3]
})
################################################################################################################
# Step 5: query base period energy input
################################################################################################################
base_input = dict()
if energy_category_set_input is not None and len(
energy_category_set_input) > 0:
for energy_category_id in energy_category_set_input:
base_input[energy_category_id] = dict()
base_input[energy_category_id]['timestamps'] = list()
base_input[energy_category_id]['values'] = list()
base_input[energy_category_id]['subtotal'] = Decimal(0.0)
cursor_energy.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_equipment_input_category_hourly "
" WHERE equipment_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(equipment['id'], energy_category_id,
base_start_datetime_utc, base_end_datetime_utc))
rows_equipment_hourly = cursor_energy.fetchall()
rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(
rows_equipment_hourly, base_start_datetime_utc,
base_end_datetime_utc, period_type)
for row_equipment_periodically in rows_equipment_periodically:
current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
else row_equipment_periodically[1]
base_input[energy_category_id]['timestamps'].append(
current_datetime)
base_input[energy_category_id]['values'].append(
actual_value)
base_input[energy_category_id]['subtotal'] += actual_value
################################################################################################################
# Step 6: query base period energy output
################################################################################################################
base_output = dict()
if energy_category_set_output is not None and len(
energy_category_set_output) > 0:
for energy_category_id in energy_category_set_output:
base_output[energy_category_id] = dict()
base_output[energy_category_id]['timestamps'] = list()
base_output[energy_category_id]['values'] = list()
base_output[energy_category_id]['subtotal'] = Decimal(0.0)
cursor_energy.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_equipment_output_category_hourly "
" WHERE equipment_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(equipment['id'], energy_category_id,
base_start_datetime_utc, base_end_datetime_utc))
rows_equipment_hourly = cursor_energy.fetchall()
rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(
rows_equipment_hourly, base_start_datetime_utc,
base_end_datetime_utc, period_type)
for row_equipment_periodically in rows_equipment_periodically:
current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
else row_equipment_periodically[1]
base_output[energy_category_id]['timestamps'].append(
current_datetime)
base_output[energy_category_id]['values'].append(
actual_value)
base_output[energy_category_id]['subtotal'] += actual_value
################################################################################################################
# Step 7: query reporting period energy input
################################################################################################################
reporting_input = dict()
if energy_category_set_input is not None and len(
energy_category_set_input) > 0:
for energy_category_id in energy_category_set_input:
reporting_input[energy_category_id] = dict()
reporting_input[energy_category_id]['timestamps'] = list()
reporting_input[energy_category_id]['values'] = list()
reporting_input[energy_category_id]['subtotal'] = Decimal(0.0)
cursor_energy.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_equipment_input_category_hourly "
" WHERE equipment_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(equipment['id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc))
rows_equipment_hourly = cursor_energy.fetchall()
rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(
rows_equipment_hourly, reporting_start_datetime_utc,
reporting_end_datetime_utc, period_type)
for row_equipment_periodically in rows_equipment_periodically:
current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
else row_equipment_periodically[1]
reporting_input[energy_category_id]['timestamps'].append(
current_datetime)
reporting_input[energy_category_id]['values'].append(
actual_value)
reporting_input[energy_category_id][
'subtotal'] += actual_value
################################################################################################################
# Step 8: query reporting period energy output
################################################################################################################
reporting_output = dict()
if energy_category_set_output is not None and len(
energy_category_set_output) > 0:
for energy_category_id in energy_category_set_output:
reporting_output[energy_category_id] = dict()
reporting_output[energy_category_id]['timestamps'] = list()
reporting_output[energy_category_id]['values'] = list()
reporting_output[energy_category_id]['subtotal'] = Decimal(0.0)
cursor_energy.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_equipment_output_category_hourly "
" WHERE equipment_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(equipment['id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc))
rows_equipment_hourly = cursor_energy.fetchall()
rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(
rows_equipment_hourly, reporting_start_datetime_utc,
reporting_end_datetime_utc, period_type)
for row_equipment_periodically in rows_equipment_periodically:
current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
else row_equipment_periodically[1]
reporting_output[energy_category_id]['timestamps'].append(
current_datetime)
reporting_output[energy_category_id]['values'].append(
actual_value)
reporting_output[energy_category_id][
'subtotal'] += actual_value
################################################################################################################
# Step 9: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
if energy_category_set_input is not None and len(
energy_category_set_input) > 0:
for energy_category_id in energy_category_set_input:
energy_category_tariff_dict = utilities.get_energy_category_tariffs(
equipment['cost_center_id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in energy_category_tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
tariff_value_list.append(v)
parameters_data['names'].append(
'TARIFF-' +
energy_category_dict[energy_category_id]['name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 10: query associated sensors and points data
################################################################################################################
for point in point_list:
point_values = []
point_timestamps = []
if point['object_type'] == 'ANALOG_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_analog_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'ENERGY_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_energy_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'DIGITAL_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_digital_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
parameters_data['names'].append(point['name'] + ' (' +
point['units'] + ')')
parameters_data['timestamps'].append(point_timestamps)
parameters_data['values'].append(point_values)
################################################################################################################
# Step 11: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.disconnect()
result = dict()
result['equipment'] = dict()
result['equipment']['name'] = equipment['name']
result['base_period_input'] = dict()
result['base_period_input']['names'] = list()
result['base_period_input']['units'] = list()
result['base_period_input']['timestamps'] = list()
result['base_period_input']['values'] = list()
result['base_period_input']['subtotals'] = list()
if energy_category_set_input is not None and len(
energy_category_set_input) > 0:
for energy_category_id in energy_category_set_input:
result['base_period_input']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['base_period_input']['units'].append(
energy_category_dict[energy_category_id]
['unit_of_measure'])
result['base_period_input']['timestamps'].append(
base_input[energy_category_id]['timestamps'])
result['base_period_input']['values'].append(
base_input[energy_category_id]['values'])
result['base_period_input']['subtotals'].append(
base_input[energy_category_id]['subtotal'])
result['base_period_output'] = dict()
result['base_period_output']['names'] = list()
result['base_period_output']['units'] = list()
result['base_period_output']['timestamps'] = list()
result['base_period_output']['values'] = list()
result['base_period_output']['subtotals'] = list()
if energy_category_set_output is not None and len(
energy_category_set_output) > 0:
for energy_category_id in energy_category_set_output:
result['base_period_output']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['base_period_output']['units'].append(
energy_category_dict[energy_category_id]
['unit_of_measure'])
result['base_period_output']['timestamps'].append(
base_output[energy_category_id]['timestamps'])
result['base_period_output']['values'].append(
base_output[energy_category_id]['values'])
result['base_period_output']['subtotals'].append(
base_output[energy_category_id]['subtotal'])
result['base_period_efficiency'] = dict()
result['base_period_efficiency']['names'] = list()
result['base_period_efficiency']['units'] = list()
result['base_period_efficiency']['timestamps'] = list()
result['base_period_efficiency']['values'] = list()
result['base_period_efficiency']['cumulations'] = list()
if energy_category_set_output is not None and len(
energy_category_set_output) > 0:
for energy_category_id_output in energy_category_set_output:
for energy_category_id_input in energy_category_set_input:
result['base_period_efficiency']['names'].append(
energy_category_dict[energy_category_id_output]['name']
+ '/' +
energy_category_dict[energy_category_id_input]['name'])
result['base_period_efficiency']['units'].append(
energy_category_dict[energy_category_id_output]
['unit_of_measure'] + '/' +
energy_category_dict[energy_category_id_input]
['unit_of_measure'])
result['base_period_efficiency']['timestamps'].append(
base_output[energy_category_id_output]['timestamps'])
efficiency_values = list()
for i in range(
len(base_output[energy_category_id_output]
['timestamps'])):
efficiency_values.append((
base_output[energy_category_id_output]['values'][i]
/ base_input[energy_category_id_input]['values'][i]
) if base_input[energy_category_id_input]['values'][i]
> Decimal(0.0) else None)
result['base_period_efficiency']['values'].append(
efficiency_values)
base_cumulation = (base_output[energy_category_id_output]['subtotal'] /
base_input[energy_category_id_input]['subtotal']) if \
base_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
result['base_period_efficiency']['cumulations'].append(
base_cumulation)
result['reporting_period_input'] = dict()
result['reporting_period_input']['names'] = list()
result['reporting_period_input']['energy_category_ids'] = list()
result['reporting_period_input']['units'] = list()
result['reporting_period_input']['timestamps'] = list()
result['reporting_period_input']['values'] = list()
result['reporting_period_input']['subtotals'] = list()
result['reporting_period_input']['increment_rates'] = list()
if energy_category_set_input is not None and len(
energy_category_set_input) > 0:
for energy_category_id in energy_category_set_input:
result['reporting_period_input']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['reporting_period_input']['energy_category_ids'].append(
energy_category_id)
result['reporting_period_input']['units'].append(
energy_category_dict[energy_category_id]
['unit_of_measure'])
result['reporting_period_input']['timestamps'].append(
reporting_input[energy_category_id]['timestamps'])
result['reporting_period_input']['values'].append(
reporting_input[energy_category_id]['values'])
result['reporting_period_input']['subtotals'].append(
reporting_input[energy_category_id]['subtotal'])
result['reporting_period_input']['increment_rates'].append(
(reporting_input[energy_category_id]['subtotal'] -
base_input[energy_category_id]['subtotal']) /
base_input[energy_category_id]['subtotal'] if
base_input[energy_category_id]['subtotal'] > 0.0 else None)
result['reporting_period_output'] = dict()
result['reporting_period_output']['names'] = list()
result['reporting_period_output']['energy_category_ids'] = list()
result['reporting_period_output']['units'] = list()
result['reporting_period_output']['timestamps'] = list()
result['reporting_period_output']['values'] = list()
result['reporting_period_output']['subtotals'] = list()
result['reporting_period_output']['increment_rates'] = list()
if energy_category_set_output is not None and len(
energy_category_set_output) > 0:
for energy_category_id in energy_category_set_output:
result['reporting_period_output']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['reporting_period_output'][
'energy_category_ids'].append(energy_category_id)
result['reporting_period_output']['units'].append(
energy_category_dict[energy_category_id]
['unit_of_measure'])
result['reporting_period_output']['timestamps'].append(
reporting_output[energy_category_id]['timestamps'])
result['reporting_period_output']['values'].append(
reporting_output[energy_category_id]['values'])
result['reporting_period_output']['subtotals'].append(
reporting_output[energy_category_id]['subtotal'])
result['reporting_period_output']['increment_rates'].append(
(reporting_output[energy_category_id]['subtotal'] -
base_output[energy_category_id]['subtotal']) /
base_output[energy_category_id]['subtotal'] if
base_output[energy_category_id]['subtotal'] > 0.0 else None
)
result['reporting_period_efficiency'] = dict()
result['reporting_period_efficiency']['names'] = list()
result['reporting_period_efficiency']['units'] = list()
result['reporting_period_efficiency']['timestamps'] = list()
result['reporting_period_efficiency']['values'] = list()
result['reporting_period_efficiency']['cumulations'] = list()
result['reporting_period_efficiency']['increment_rates'] = list()
if energy_category_set_output is not None and len(
energy_category_set_output) > 0:
for energy_category_id_output in energy_category_set_output:
for energy_category_id_input in energy_category_set_input:
result['reporting_period_efficiency']['names'].append(
energy_category_dict[energy_category_id_output]['name']
+ '/' +
energy_category_dict[energy_category_id_input]['name'])
result['reporting_period_efficiency']['units'].append(
energy_category_dict[energy_category_id_output]
['unit_of_measure'] + '/' +
energy_category_dict[energy_category_id_input]
['unit_of_measure'])
result['reporting_period_efficiency']['timestamps'].append(
reporting_output[energy_category_id_output]
['timestamps'])
efficiency_values = list()
for i in range(
len(reporting_output[energy_category_id_output]
['timestamps'])):
efficiency_values.append(
(reporting_output[energy_category_id_output]
['values'][i] /
reporting_input[energy_category_id_input]
['values'][i]
) if reporting_input[energy_category_id_input]
['values'][i] > Decimal(0.0) else None)
result['reporting_period_efficiency']['values'].append(
efficiency_values)
base_cumulation = (base_output[energy_category_id_output]['subtotal'] /
base_input[energy_category_id_input]['subtotal']) if \
base_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
reporting_cumulation = (reporting_output[energy_category_id_output]['subtotal'] /
reporting_input[energy_category_id_input]['subtotal']) if \
reporting_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
result['reporting_period_efficiency'][
'cumulations'].append(reporting_cumulation)
result['reporting_period_efficiency'][
'increment_rates'].append(
((reporting_cumulation - base_cumulation) /
base_cumulation if
(base_cumulation is not None
and base_cumulation > Decimal(0.0)) else None))
result['parameters'] = {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
}
resp.body = json.dumps(result)
def on_get(req, resp):
print(req.params)
space_id = req.params.get('spaceid')
period_type = req.params.get('periodtype')
base_start_datetime_local = req.params.get('baseperiodstartdatetime')
base_end_datetime_local = req.params.get('baseperiodenddatetime')
reporting_start_datetime_local = req.params.get(
'reportingperiodstartdatetime')
reporting_end_datetime_local = req.params.get(
'reportingperiodenddatetime')
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if space_id is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_SPACE_ID')
else:
space_id = str.strip(space_id)
if not space_id.isdigit() or int(space_id) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_SPACE_ID')
if period_type is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
else:
period_type = str.strip(period_type)
if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
base_start_datetime_utc = None
if base_start_datetime_local is not None and len(
str.strip(base_start_datetime_local)) > 0:
base_start_datetime_local = str.strip(base_start_datetime_local)
try:
base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_START_DATETIME")
base_end_datetime_utc = None
if base_end_datetime_local is not None and len(
str.strip(base_end_datetime_local)) > 0:
base_end_datetime_local = str.strip(base_end_datetime_local)
try:
base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_END_DATETIME")
if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
base_start_datetime_utc >= base_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_BASE_PERIOD_END_DATETIME')
if reporting_start_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
else:
reporting_start_datetime_local = str.strip(
reporting_start_datetime_local)
try:
reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
if reporting_end_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
else:
reporting_end_datetime_local = str.strip(
reporting_end_datetime_local)
try:
reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
################################################################################################################
# Step 2: query the space
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_billing = mysql.connector.connect(**config.myems_billing_db)
cursor_billing = cnx_billing.cursor()
cnx_historical = mysql.connector.connect(**config.myems_historical_db)
cursor_historical = cnx_historical.cursor()
cursor_system.execute(
" SELECT id, name, area, cost_center_id "
" FROM tbl_spaces "
" WHERE id = %s ", (space_id, ))
row_space = cursor_system.fetchone()
if row_space is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_billing:
cursor_billing.close()
if cnx_billing:
cnx_billing.disconnect()
if cnx_historical:
cnx_historical.close()
if cursor_historical:
cursor_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.SPACE_NOT_FOUND')
space = dict()
space['id'] = row_space[0]
space['name'] = row_space[1]
space['area'] = row_space[2]
space['cost_center_id'] = row_space[3]
################################################################################################################
# Step 3: query energy categories
################################################################################################################
energy_category_set = set()
# query energy categories in base period
cursor_billing.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_space_input_category_hourly "
" WHERE space_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(space['id'], base_start_datetime_utc, base_end_datetime_utc))
rows_energy_categories = cursor_billing.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set.add(row_energy_category[0])
# query energy categories in reporting period
cursor_billing.execute(
" SELECT DISTINCT(energy_category_id) "
" FROM tbl_space_input_category_hourly "
" WHERE space_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s ",
(space['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_energy_categories = cursor_billing.fetchall()
if rows_energy_categories is not None or len(
rows_energy_categories) > 0:
for row_energy_category in rows_energy_categories:
energy_category_set.add(row_energy_category[0])
# query all energy categories in base period and reporting period
cursor_system.execute(
" SELECT id, name, unit_of_measure, kgce, kgco2e "
" FROM tbl_energy_categories "
" ORDER BY id ", )
rows_energy_categories = cursor_system.fetchall()
if rows_energy_categories is None or len(rows_energy_categories) == 0:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_billing:
cursor_billing.close()
if cnx_billing:
cnx_billing.disconnect()
if cnx_historical:
cnx_historical.close()
if cursor_historical:
cursor_historical.disconnect()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.ENERGY_CATEGORY_NOT_FOUND')
energy_category_dict = dict()
for row_energy_category in rows_energy_categories:
if row_energy_category[0] in energy_category_set:
energy_category_dict[row_energy_category[0]] = {
"name": row_energy_category[1],
"unit_of_measure": row_energy_category[2],
"kgce": row_energy_category[3],
"kgco2e": row_energy_category[4]
}
################################################################################################################
# Step 4: query associated sensors
################################################################################################################
point_list = list()
cursor_system.execute(
" SELECT po.id, po.name, po.units, po.object_type "
" FROM tbl_spaces sp, tbl_sensors se, tbl_spaces_sensors spse, "
" tbl_points po, tbl_sensors_points sepo "
" WHERE sp.id = %s AND sp.id = spse.space_id AND spse.sensor_id = se.id "
" AND se.id = sepo.sensor_id AND sepo.point_id = po.id "
" ORDER BY po.id ", (space['id'], ))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({
"id": row[0],
"name": row[1],
"units": row[2],
"object_type": row[3]
})
################################################################################################################
# Step 5: query associated points
################################################################################################################
cursor_system.execute(
" SELECT po.id, po.name, po.units, po.object_type "
" FROM tbl_spaces sp, tbl_spaces_points sppo, tbl_points po "
" WHERE sp.id = %s AND sp.id = sppo.space_id AND sppo.point_id = po.id "
" ORDER BY po.id ", (space['id'], ))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({
"id": row[0],
"name": row[1],
"units": row[2],
"object_type": row[3]
})
################################################################################################################
# Step 6: query child spaces
################################################################################################################
child_space_list = list()
cursor_system.execute(
" SELECT id, name "
" FROM tbl_spaces "
" WHERE parent_space_id = %s "
" ORDER BY id ", (space['id'], ))
rows_child_spaces = cursor_system.fetchall()
if rows_child_spaces is not None and len(rows_child_spaces) > 0:
for row in rows_child_spaces:
child_space_list.append({"id": row[0], "name": row[1]})
################################################################################################################
# Step 7: query base period energy cost
################################################################################################################
base = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
base[energy_category_id] = dict()
base[energy_category_id]['timestamps'] = list()
base[energy_category_id]['values'] = list()
base[energy_category_id]['subtotal'] = Decimal(0.0)
cursor_billing.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_space_input_category_hourly "
" WHERE space_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(space['id'], energy_category_id, base_start_datetime_utc,
base_end_datetime_utc))
rows_space_hourly = cursor_billing.fetchall()
rows_space_periodically = utilities.aggregate_hourly_data_by_period(
rows_space_hourly, base_start_datetime_utc,
base_end_datetime_utc, period_type)
for row_space_periodically in rows_space_periodically:
current_datetime_local = row_space_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
actual_value = Decimal(0.0) if row_space_periodically[
1] is None else row_space_periodically[1]
base[energy_category_id]['timestamps'].append(
current_datetime)
base[energy_category_id]['values'].append(actual_value)
base[energy_category_id]['subtotal'] += actual_value
################################################################################################################
# Step 8: query reporting period energy cost
################################################################################################################
reporting = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
reporting[energy_category_id] = dict()
reporting[energy_category_id]['timestamps'] = list()
reporting[energy_category_id]['values'] = list()
reporting[energy_category_id]['subtotal'] = Decimal(0.0)
reporting[energy_category_id]['toppeak'] = Decimal(0.0)
reporting[energy_category_id]['onpeak'] = Decimal(0.0)
reporting[energy_category_id]['midpeak'] = Decimal(0.0)
reporting[energy_category_id]['offpeak'] = Decimal(0.0)
cursor_billing.execute(
" SELECT start_datetime_utc, actual_value "
" FROM tbl_space_input_category_hourly "
" WHERE space_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(space['id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc))
rows_space_hourly = cursor_billing.fetchall()
rows_space_periodically = utilities.aggregate_hourly_data_by_period(
rows_space_hourly, reporting_start_datetime_utc,
reporting_end_datetime_utc, period_type)
for row_space_periodically in rows_space_periodically:
current_datetime_local = row_space_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime(
'%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime(
'%Y')
actual_value = Decimal(0.0) if row_space_periodically[
1] is None else row_space_periodically[1]
reporting[energy_category_id]['timestamps'].append(
current_datetime)
reporting[energy_category_id]['values'].append(
actual_value)
reporting[energy_category_id]['subtotal'] += actual_value
energy_category_tariff_dict = utilities.get_energy_category_peak_types(
space['cost_center_id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc)
for row in rows_space_hourly:
peak_type = energy_category_tariff_dict.get(row[0], None)
if peak_type == 'toppeak':
reporting[energy_category_id]['toppeak'] += row[1]
elif peak_type == 'onpeak':
reporting[energy_category_id]['onpeak'] += row[1]
elif peak_type == 'midpeak':
reporting[energy_category_id]['midpeak'] += row[1]
elif peak_type == 'offpeak':
reporting[energy_category_id]['offpeak'] += row[1]
################################################################################################################
# Step 9: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
energy_category_tariff_dict = utilities.get_energy_category_tariffs(
space['cost_center_id'], energy_category_id,
reporting_start_datetime_utc, reporting_end_datetime_utc)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in energy_category_tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
tariff_value_list.append(v)
parameters_data['names'].append(
'TARIFF-' +
energy_category_dict[energy_category_id]['name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 10: query associated sensors and points data
################################################################################################################
for point in point_list:
point_values = []
point_timestamps = []
if point['object_type'] == 'ANALOG_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_analog_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'ENERGY_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_energy_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'DIGITAL_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_digital_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
parameters_data['names'].append(point['name'] + ' (' +
point['units'] + ')')
parameters_data['timestamps'].append(point_timestamps)
parameters_data['values'].append(point_values)
################################################################################################################
# Step 11: query child spaces energy cost
################################################################################################################
child_space_data = dict()
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
child_space_data[energy_category_id] = dict()
child_space_data[energy_category_id][
'child_space_names'] = list()
child_space_data[energy_category_id]['subtotals'] = list()
for child_space in child_space_list:
child_space_data[energy_category_id][
'child_space_names'].append(child_space['name'])
cursor_billing.execute(
" SELECT SUM(actual_value) "
" FROM tbl_space_input_category_hourly "
" WHERE space_id = %s "
" AND energy_category_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ",
(child_space['id'], energy_category_id,
reporting_start_datetime_utc,
reporting_end_datetime_utc))
row_subtotal = cursor_billing.fetchone()
subtotal = Decimal(0.0) if (
row_subtotal is None
or row_subtotal[0] is None) else row_subtotal[0]
child_space_data[energy_category_id]['subtotals'].append(
subtotal)
################################################################################################################
# Step 12: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.disconnect()
if cursor_billing:
cursor_billing.close()
if cnx_billing:
cnx_billing.disconnect()
result = dict()
result['space'] = dict()
result['space']['name'] = space['name']
result['space']['area'] = space['area']
result['base_period'] = dict()
result['base_period']['names'] = list()
result['base_period']['units'] = list()
result['base_period']['timestamps'] = list()
result['base_period']['values'] = list()
result['base_period']['subtotals'] = list()
result['base_period']['total'] = Decimal(0.0)
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['base_period']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['base_period']['units'].append(config.currency_unit)
result['base_period']['timestamps'].append(
base[energy_category_id]['timestamps'])
result['base_period']['values'].append(
base[energy_category_id]['values'])
result['base_period']['subtotals'].append(
base[energy_category_id]['subtotal'])
result['base_period']['total'] += base[energy_category_id][
'subtotal']
result['reporting_period'] = dict()
result['reporting_period']['names'] = list()
result['reporting_period']['energy_category_ids'] = list()
result['reporting_period']['units'] = list()
result['reporting_period']['timestamps'] = list()
result['reporting_period']['values'] = list()
result['reporting_period']['subtotals'] = list()
result['reporting_period']['subtotals_per_unit_area'] = list()
result['reporting_period']['toppeaks'] = list()
result['reporting_period']['onpeaks'] = list()
result['reporting_period']['midpeaks'] = list()
result['reporting_period']['offpeaks'] = list()
result['reporting_period']['increment_rates'] = list()
result['reporting_period']['total'] = Decimal(0.0)
result['reporting_period']['total_per_unit_area'] = Decimal(0.0)
result['reporting_period']['total_increment_rate'] = Decimal(0.0)
result['reporting_period']['total_unit'] = config.currency_unit
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['reporting_period']['names'].append(
energy_category_dict[energy_category_id]['name'])
result['reporting_period']['energy_category_ids'].append(
energy_category_id)
result['reporting_period']['units'].append(
config.currency_unit)
result['reporting_period']['timestamps'].append(
reporting[energy_category_id]['timestamps'])
result['reporting_period']['values'].append(
reporting[energy_category_id]['values'])
result['reporting_period']['subtotals'].append(
reporting[energy_category_id]['subtotal'])
result['reporting_period']['subtotals_per_unit_area'].append(
reporting[energy_category_id]['subtotal'] /
space['area'] if space['area'] > 0.0 else None)
result['reporting_period']['toppeaks'].append(
reporting[energy_category_id]['toppeak'])
result['reporting_period']['onpeaks'].append(
reporting[energy_category_id]['onpeak'])
result['reporting_period']['midpeaks'].append(
reporting[energy_category_id]['midpeak'])
result['reporting_period']['offpeaks'].append(
reporting[energy_category_id]['offpeak'])
result['reporting_period']['increment_rates'].append(
(reporting[energy_category_id]['subtotal'] -
base[energy_category_id]['subtotal']) /
base[energy_category_id]['subtotal']
if base[energy_category_id]['subtotal'] > 0.0 else None)
result['reporting_period']['total'] += reporting[
energy_category_id]['subtotal']
result['reporting_period']['total_per_unit_area'] = \
result['reporting_period']['total'] / space['area'] if space['area'] > 0.0 else None
result['reporting_period']['total_increment_rate'] = \
(result['reporting_period']['total'] - result['base_period']['total']) / \
result['base_period']['total'] \
if result['base_period']['total'] > Decimal(0.0) else None
result['parameters'] = {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
}
result['child_space'] = dict()
result['child_space']['energy_category_names'] = list(
) # 1D array [energy category]
result['child_space']['units'] = list() # 1D array [energy category]
result['child_space']['child_space_names_array'] = list(
) # 2D array [energy category][child space]
result['child_space']['subtotals_array'] = list(
) # 2D array [energy category][child space]
result['child_space']['total_unit'] = config.currency_unit
if energy_category_set is not None and len(energy_category_set) > 0:
for energy_category_id in energy_category_set:
result['child_space']['energy_category_names'].append(
energy_category_dict[energy_category_id]['name'])
result['child_space']['units'].append(config.currency_unit)
result['child_space']['child_space_names_array'].append(
child_space_data[energy_category_id]['child_space_names'])
result['child_space']['subtotals_array'].append(
child_space_data[energy_category_id]['subtotals'])
# export result to Excel file and then encode the file to base64 string
result['excel_bytes_base64'] = excelexporters.spacecost.export(
result, space['name'], reporting_start_datetime_local,
reporting_end_datetime_local, period_type)
resp.body = json.dumps(result)
def on_get(req, resp):
print(req.params)
# this procedure accepts meter id or meter uuid to identify a meter
meter_id = req.params.get('meterid')
meter_uuid = req.params.get('meteruuid')
period_type = req.params.get('periodtype')
base_period_start_datetime = req.params.get('baseperiodstartdatetime')
base_period_end_datetime = req.params.get('baseperiodenddatetime')
reporting_period_start_datetime_local = req.params.get(
'reportingperiodstartdatetime')
reporting_period_end_datetime_local = req.params.get(
'reportingperiodenddatetime')
################################################################################################################
# Step 1: valid parameters
################################################################################################################
if meter_id is None and meter_uuid is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_METER_ID')
if meter_id is not None:
meter_id = str.strip(meter_id)
if not meter_id.isdigit() or int(meter_id) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_METER_ID')
if meter_uuid is not None:
regex = re.compile(
'^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z',
re.I)
match = regex.match(str.strip(meter_uuid))
if not bool(match):
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_METER_UUID')
if period_type is None:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
else:
period_type = str.strip(period_type)
if period_type not in [
'hourly', 'daily', 'weekly', 'monthly', 'yearly'
]:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PERIOD_TYPE')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
base_start_datetime_utc = None
if base_period_start_datetime is not None and len(
str.strip(base_period_start_datetime)) > 0:
base_period_start_datetime = str.strip(base_period_start_datetime)
try:
base_start_datetime_utc = datetime.strptime(
base_period_start_datetime, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_START_DATETIME")
base_start_datetime_utc = base_start_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
base_end_datetime_utc = None
if base_period_end_datetime is not None and len(
str.strip(base_period_end_datetime)) > 0:
base_period_end_datetime = str.strip(base_period_end_datetime)
try:
base_end_datetime_utc = datetime.strptime(
base_period_end_datetime, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_BASE_PERIOD_END_DATETIME")
base_end_datetime_utc = base_end_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
base_start_datetime_utc >= base_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_BASE_PERIOD_END_DATETIME')
if reporting_period_start_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
else:
reporting_period_start_datetime_local = str.strip(
reporting_period_start_datetime_local)
try:
reporting_start_datetime_utc = datetime.strptime(
reporting_period_start_datetime_local, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
if reporting_period_end_datetime_local is None:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
else:
reporting_period_end_datetime_local = str.strip(
reporting_period_end_datetime_local)
try:
reporting_end_datetime_utc = datetime.strptime(
reporting_period_end_datetime_local, '%Y-%m-%dT%H:%M:%S')
except ValueError:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
timedelta(minutes=timezone_offset)
if reporting_start_datetime_utc >= reporting_end_datetime_utc:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
################################################################################################################
# Step 2: query the meter and energy category
################################################################################################################
cnx_system = mysql.connector.connect(**config.myems_system_db)
cursor_system = cnx_system.cursor()
cnx_energy = mysql.connector.connect(**config.myems_energy_db)
cursor_energy = cnx_energy.cursor()
cnx_historical = mysql.connector.connect(**config.myems_historical_db)
cursor_historical = cnx_historical.cursor()
if meter_id is not None:
cursor_system.execute(
" SELECT m.id, m.name, m.cost_center_id, m.energy_category_id, "
" ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
" FROM tbl_meters m, tbl_energy_categories ec "
" WHERE m.id = %s AND m.energy_category_id = ec.id ",
(meter_id, ))
row_meter = cursor_system.fetchone()
elif meter_uuid is not None:
cursor_system.execute(
" SELECT m.id, m.name, m.cost_center_id, m.energy_category_id, "
" ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
" FROM tbl_meters m, tbl_energy_categories ec "
" WHERE m.uuid = %s AND m.energy_category_id = ec.id ",
(meter_uuid, ))
row_meter = cursor_system.fetchone()
if row_meter is None:
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.close()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.close()
if cursor_historical:
cursor_historical.close()
if cnx_historical:
cnx_historical.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.METER_NOT_FOUND')
meter = dict()
meter['id'] = row_meter[0]
meter['name'] = row_meter[1]
meter['cost_center_id'] = row_meter[2]
meter['energy_category_id'] = row_meter[3]
meter['energy_category_name'] = row_meter[4]
meter['unit_of_measure'] = row_meter[5]
meter['kgce'] = row_meter[6]
meter['kgco2e'] = row_meter[7]
################################################################################################################
# Step 3: query associated points
################################################################################################################
point_list = list()
cursor_system.execute(
" SELECT p.id, p.name, p.units, p.object_type "
" FROM tbl_meters m, tbl_meters_points mp, tbl_points p "
" WHERE m.id = %s AND m.id = mp.meter_id AND mp.point_id = p.id "
" ORDER BY p.id ", (meter['id'], ))
rows_points = cursor_system.fetchall()
if rows_points is not None and len(rows_points) > 0:
for row in rows_points:
point_list.append({
"id": row[0],
"name": row[1],
"units": row[2],
"object_type": row[3]
})
################################################################################################################
# Step 4: query base period energy consumption
################################################################################################################
cnx_energy = mysql.connector.connect(**config.myems_energy_db)
cursor_energy = cnx_energy.cursor()
query = (" SELECT start_datetime_utc, actual_value "
" FROM tbl_meter_hourly "
" WHERE meter_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ")
cursor_energy.execute(
query,
(meter['id'], base_start_datetime_utc, base_end_datetime_utc))
rows_meter_hourly = cursor_energy.fetchall()
rows_meter_periodically = utilities.aggregate_hourly_data_by_period(
rows_meter_hourly, base_start_datetime_utc, base_end_datetime_utc,
period_type)
base = dict()
base['timestamps'] = list()
base['values'] = list()
base['total_in_category'] = Decimal(0.0)
base['total_in_kgce'] = Decimal(0.0)
base['total_in_kgco2e'] = Decimal(0.0)
for row_meter_periodically in rows_meter_periodically:
current_datetime_local = row_meter_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'weekly':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
actual_value = Decimal(0.0) if row_meter_periodically[
1] is None else row_meter_periodically[1]
base['timestamps'].append(current_datetime)
base['values'].append(actual_value)
base['total_in_category'] += actual_value
base['total_in_kgce'] += actual_value * meter['kgce']
base['total_in_kgco2e'] += actual_value * meter['kgco2e']
################################################################################################################
# Step 5: query reporting period energy consumption
################################################################################################################
query = (" SELECT start_datetime_utc, actual_value "
" FROM tbl_meter_hourly "
" WHERE meter_id = %s "
" AND start_datetime_utc >= %s "
" AND start_datetime_utc < %s "
" ORDER BY start_datetime_utc ")
cursor_energy.execute(query,
(meter['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows_meter_hourly = cursor_energy.fetchall()
rows_meter_periodically = utilities.aggregate_hourly_data_by_period(
rows_meter_hourly, reporting_start_datetime_utc,
reporting_end_datetime_utc, period_type)
reporting = dict()
reporting['timestamps'] = list()
reporting['values'] = list()
reporting['total_in_category'] = Decimal(0.0)
reporting['total_in_kgce'] = Decimal(0.0)
reporting['total_in_kgco2e'] = Decimal(0.0)
for row_meter_periodically in rows_meter_periodically:
current_datetime_local = row_meter_periodically[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
if period_type == 'hourly':
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
elif period_type == 'daily':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'weekly':
current_datetime = current_datetime_local.strftime('%Y-%m-%d')
elif period_type == 'monthly':
current_datetime = current_datetime_local.strftime('%Y-%m')
elif period_type == 'yearly':
current_datetime = current_datetime_local.strftime('%Y')
actual_value = Decimal(0.0) if row_meter_periodically[
1] is None else row_meter_periodically[1]
reporting['timestamps'].append(current_datetime)
reporting['values'].append(actual_value)
reporting['total_in_category'] += actual_value
reporting['total_in_kgce'] += actual_value * meter['kgce']
reporting['total_in_kgco2e'] += actual_value * meter['kgco2e']
################################################################################################################
# Step 6: query tariff data
################################################################################################################
parameters_data = dict()
parameters_data['names'] = list()
parameters_data['timestamps'] = list()
parameters_data['values'] = list()
tariff_dict = utilities.get_energy_category_tariffs(
meter['cost_center_id'], meter['energy_category_id'],
reporting_start_datetime_utc, reporting_end_datetime_utc)
print(tariff_dict)
tariff_timestamp_list = list()
tariff_value_list = list()
for k, v in tariff_dict.items():
# convert k from utc to local
k = k + timedelta(minutes=timezone_offset)
tariff_timestamp_list.append(k.isoformat()[0:19])
tariff_value_list.append(v)
parameters_data['names'].append('TARIFF-' +
meter['energy_category_name'])
parameters_data['timestamps'].append(tariff_timestamp_list)
parameters_data['values'].append(tariff_value_list)
################################################################################################################
# Step 7: query associated points data
################################################################################################################
for point in point_list:
point_values = []
point_timestamps = []
if point['object_type'] == 'ANALOG_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_analog_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'ENERGY_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_energy_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
elif point['object_type'] == 'DIGITAL_VALUE':
query = (" SELECT utc_date_time, actual_value "
" FROM tbl_digital_value "
" WHERE point_id = %s "
" AND utc_date_time BETWEEN %s AND %s "
" ORDER BY utc_date_time ")
cursor_historical.execute(
query, (point['id'], reporting_start_datetime_utc,
reporting_end_datetime_utc))
rows = cursor_historical.fetchall()
if rows is not None and len(rows) > 0:
for row in rows:
current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
current_datetime = current_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
point_timestamps.append(current_datetime)
point_values.append(row[1])
parameters_data['names'].append(point['name'] + ' (' +
point['units'] + ')')
parameters_data['timestamps'].append(point_timestamps)
parameters_data['values'].append(point_values)
################################################################################################################
# Step 8: construct the report
################################################################################################################
if cursor_system:
cursor_system.close()
if cnx_system:
cnx_system.close()
if cursor_energy:
cursor_energy.close()
if cnx_energy:
cnx_energy.close()
if cursor_historical:
cursor_historical.close()
if cnx_historical:
cnx_historical.close()
result = {
"meter": {
"cost_center_id": meter['cost_center_id'],
"energy_category_id": meter['energy_category_id'],
"energy_category_name": meter['energy_category_name'],
"unit_of_measure": meter['unit_of_measure'],
"kgce": meter['kgce'],
"kgco2e": meter['kgco2e'],
},
"base_period": {
"total_in_category": base['total_in_category'],
"total_in_kgce": base['total_in_kgce'],
"total_in_kgco2e": base['total_in_kgco2e'],
"timestamps": base['timestamps'],
"values": base['values']
},
"reporting_period": {
"increment_rate":
(reporting['total_in_category'] - base['total_in_category']) /
base['total_in_category']
if base['total_in_category'] > 0 else None,
"total_in_category":
reporting['total_in_category'],
"total_in_kgce":
reporting['total_in_kgce'],
"total_in_kgco2e":
reporting['total_in_kgco2e'],
"timestamps":
reporting['timestamps'],
"values":
reporting['values'],
},
"parameters": {
"names": parameters_data['names'],
"timestamps": parameters_data['timestamps'],
"values": parameters_data['values']
},
}
# export result to Excel file and then encode the file to base64 string
result['excel_bytes_base64'] = \
excelexporters.meterenergy.export(result,
meter['name'],
reporting_period_start_datetime_local,
reporting_period_end_datetime_local,
period_type)
resp.text = json.dumps(result)