def on_get(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_OFFLINE_METER_FILE_ID')
cnx = mysql.connector.connect(**config.myems_historical_db)
cursor = cnx.cursor()
query = (" SELECT id, file_name, uuid, upload_datetime_utc, status "
" FROM tbl_offline_meter_files "
" WHERE id = %s ")
cursor.execute(query, (id_,))
row = cursor.fetchone()
cursor.close()
cnx.close()
if row is None:
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
description='API.OFFLINE_METER_FILE_NOT_FOUND')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
upload_datetime_local = row[3].replace(tzinfo=timezone.utc) + timedelta(minutes=timezone_offset)
result = {"id": row[0],
"file_name": row[1],
"uuid": row[2],
"upload_datetime": upload_datetime_local.strftime('%Y-%m-%dT%H:%M:%S'),
"status": row[4]}
resp.text = json.dumps(result)
def on_delete(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_DISTRIBUTION_CIRCUIT_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(" SELECT name "
" FROM tbl_distribution_circuits "
" WHERE id = %s ", (id_,))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
description='API.DISTRIBUTION_CIRCUIT_NOT_FOUND')
# delete relation with points
cursor.execute(" DELETE FROM tbl_distribution_circuits_points "
" WHERE distribution_circuit_id = %s ", (id_,))
# delete distribution circuit itself
cursor.execute(" DELETE FROM tbl_distribution_circuits "
" WHERE id = %s ", (id_,))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_delete(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_ENERGY_FLOW_DIAGRAM_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
# delete all associated nodes
cursor.execute(
" DELETE FROM tbl_energy_flow_diagrams_nodes"
" WHERE energy_flow_diagram_id = %s ", (id_, ))
cnx.commit()
# delete all associated links
cursor.execute(
" DELETE FROM tbl_energy_flow_diagrams_links"
" WHERE energy_flow_diagram_id = %s ", (id_, ))
cnx.commit()
cursor.execute(
" DELETE FROM tbl_energy_flow_diagrams"
" WHERE id = %s ", (id_, ))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_delete(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_USER_ID')
cnx = mysql.connector.connect(**config.myems_user_db)
cursor = cnx.cursor()
cursor.execute(" SELECT name "
" FROM tbl_users "
" WHERE id = %s ", (id_,))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
description='API.USER_NOT_FOUND')
# TODO: delete associated objects
cursor.execute(" DELETE FROM tbl_users WHERE id = %s ", (id_,))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_delete(req, resp, id_):
"""Handles DELETE requests"""
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_EMAIL_SERVER_ID')
cnx = mysql.connector.connect(**config.myems_fdd_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT host "
" FROM tbl_email_servers "
" WHERE id = %s ", (id_, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.EMAIL_SERVER_NOT_FOUND')
cursor.execute(" DELETE FROM tbl_email_servers WHERE id = %s ",
(id_, ))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_get(req, resp):
access_control(req)
cnx = mysql.connector.connect(**config.myems_historical_db)
cursor = cnx.cursor()
query = (" SELECT id, file_name, uuid, upload_datetime_utc, status "
" FROM tbl_offline_meter_files "
" ORDER BY upload_datetime_utc desc ")
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
cnx.close()
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
result = list()
if rows is not None and len(rows) > 0:
for row in rows:
upload_datetime_local = row[3].replace(tzinfo=timezone.utc) + timedelta(minutes=timezone_offset)
meta_result = {"id": row[0],
"file_name": row[1],
"uuid": row[2],
"upload_datetime": upload_datetime_local.strftime('%Y-%m-%dT%H:%M:%S'),
"status": row[4]}
result.append(meta_result)
resp.text = json.dumps(result)
def on_delete(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_WECHAT_MESSAGE_ID')
cnx = mysql.connector.connect(**config.myems_fdd_db)
cursor = cnx.cursor()
cursor.execute(" SELECT id "
" FROM tbl_wechat_messages_outbox "
" WHERE id = %s ", (id_,))
row = cursor.fetchone()
if row is None:
if cursor:
cursor.close()
if cnx:
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
description='API.WECHAT_MESSAGE_NOT_FOUND')
cursor.execute(" DELETE FROM tbl_wechat_messages_outbox WHERE id = %s ", (id_,))
cnx.commit()
if cursor:
cursor.close()
if cnx:
cnx.close()
resp.status = falcon.HTTP_204
def on_get(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_DATA_SOURCE_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
query = (" SELECT id, name, uuid " " FROM tbl_gateways ")
cursor.execute(query)
rows_gateways = cursor.fetchall()
gateway_dict = dict()
if rows_gateways is not None and len(rows_gateways) > 0:
for row in rows_gateways:
gateway_dict[row[0]] = {
"id": row[0],
"name": row[1],
"uuid": row[2]
}
query = (
" SELECT id, name, uuid, gateway_id, protocol, connection, last_seen_datetime_utc "
" FROM tbl_data_sources "
" WHERE id = %s ")
cursor.execute(query, (id_, ))
row = cursor.fetchone()
cursor.close()
cnx.close()
if row is None:
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.DATA_SOURCE_NOT_FOUND')
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 isinstance(row[6], datetime):
last_seen_datetime_local = row[6].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
last_seen_datetime = last_seen_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
else:
last_seen_datetime = None
result = {
"id": row[0],
"name": row[1],
"uuid": row[2],
"gateway": gateway_dict.get(row[3]),
"protocol": row[4],
"connection": row[5],
"last_seen_datetime": last_seen_datetime
}
resp.text = json.dumps(result)
def on_get(req, resp):
"""Handles GET requests"""
access_control(req)
cnx = mysql.connector.connect(**config.myems_fdd_db)
cursor = cnx.cursor()
query = (
" SELECT id, name, uuid, "
" category, fdd_code, priority, "
" channel, expression, message_template, "
" is_enabled, last_run_datetime_utc, next_run_datetime_utc "
" FROM tbl_rules "
" ORDER BY id ")
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
cnx.close()
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
result = list()
if rows is not None and len(rows) > 0:
for row in rows:
if isinstance(row[10], datetime):
last_run_datetime_local = row[10].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
last_run_datetime = last_run_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
else:
last_run_datetime = None
if isinstance(row[11], datetime):
next_run_datetime_local = row[11].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
next_run_datetime = next_run_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
else:
next_run_datetime = None
meta_result = {
"id": row[0],
"name": row[1],
"uuid": row[2],
"category": row[3],
"fdd_code": row[4],
"priority": row[5],
"channel": row[6],
"expression": row[7],
"message_template": row[8].replace("<br>", ""),
"is_enabled": bool(row[9]),
"last_run_datetime": last_run_datetime,
"next_run_datetime": next_run_datetime,
}
result.append(meta_result)
resp.text = json.dumps(result)
def on_delete(req, resp, id_, tid):
"""Handles DELETE requests"""
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_COST_CENTER_ID')
if not tid.isdigit() or int(tid) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_TARIFF_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT name "
" FROM tbl_cost_centers "
" WHERE id = %s ", (id_, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.COST_CENTER_NOT_FOUND')
cursor.execute(" SELECT name "
" FROM tbl_tariffs "
" WHERE id = %s ", (tid, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.TARIFF_NOT_FOUND')
cursor.execute(
" SELECT id "
" FROM tbl_cost_centers_tariffs "
" WHERE cost_center_id = %s AND tariff_id = %s ", (id_, tid))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.TARIFF_IS_NOT_ASSOCIATED_WITH_COST_CENTER')
cursor.execute(
" DELETE FROM tbl_cost_centers_tariffs "
" WHERE cost_center_id = %s AND tariff_id = %s ", (id_, tid))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_post(req, resp):
"""Handles POST requests"""
access_control(req)
try:
raw_json = req.stream.read().decode('utf-8')
new_values = json.loads(raw_json)
except Exception as ex:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.EXCEPTION',
description=ex)
if 'name' not in new_values['data'] or \
not isinstance(new_values['data']['name'], str) or \
len(str.strip(new_values['data']['name'])) == 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PRIVILEGE_NAME')
name = str.strip(new_values['data']['name'])
if 'data' not in new_values['data'] or \
not isinstance(new_values['data']['data'], str) or \
len(str.strip(new_values['data']['data'])) == 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_PRIVILEGE_DATA')
data = str.strip(new_values['data']['data'])
cnx = mysql.connector.connect(**config.myems_user_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT name "
" FROM tbl_privileges "
" WHERE name = %s ", (name, ))
if cursor.fetchone() is not None:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.PRIVILEGE_NAME_IS_ALREADY_IN_USE')
add_row = (" INSERT INTO tbl_privileges "
" (name, data) "
" VALUES (%s, %s) ")
cursor.execute(add_row, (
name,
data,
))
new_id = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_201
resp.location = '/privileges/' + str(new_id)
def on_get(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_GATEWAY_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(" SELECT name "
" FROM tbl_gateways "
" WHERE id = %s ", (id_, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.GATEWAY_NOT_FOUND')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
result = list()
query_data_source = (
" SELECT id, name, uuid, "
" protocol, connection, last_seen_datetime_utc "
" FROM tbl_data_sources "
" WHERE gateway_id = %s "
" ORDER BY name ")
cursor.execute(query_data_source, (id_, ))
rows_data_source = cursor.fetchall()
if rows_data_source is not None and len(rows_data_source) > 0:
for row in rows_data_source:
if isinstance(row[5], datetime):
last_seen_datetime_local = row[5].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
last_seen_datetime = last_seen_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
else:
last_seen_datetime = None
meta_result = {
"id": row[0],
"name": row[1],
"uuid": row[2],
"protocol": row[3],
"connection": row[4],
"last_seen_datetime": last_seen_datetime,
}
result.append(meta_result)
cursor.close()
cnx.close()
resp.text = json.dumps(result)
def on_delete(req, resp, id_, lid):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_ENERGY_FLOW_DIAGRAM_ID')
if not lid.isdigit() or int(lid) <= 0:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_ENERGY_FLOW_DIAGRAM_LINK_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT name "
" FROM tbl_energy_flow_diagrams "
" WHERE id = %s ", (id_, ))
row = cursor.fetchone()
if row is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.NOT_FOUND',
description='API.ENERGY_FLOW_DIAGRAM_NOT_FOUND')
cursor.execute(
" SELECT id "
" FROM tbl_energy_flow_diagrams_links "
" WHERE energy_flow_diagram_id = %s AND id = %s ", (
id_,
lid,
))
row = cursor.fetchone()
if row is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.NOT_FOUND',
description=
'API.ENERGY_FLOW_DIAGRAM_LINK_NOT_FOUND_OR_NOT_MATCH')
cursor.execute(
" DELETE FROM tbl_energy_flow_diagrams_links "
" WHERE id = %s ", (lid, ))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_delete(req, resp, id_, pid):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_SENSOR_ID')
if not pid.isdigit() or int(pid) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_POINT_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(" SELECT name "
" FROM tbl_sensors "
" WHERE id = %s ", (id_, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.SENSOR_NOT_FOUND')
cursor.execute(" SELECT name "
" FROM tbl_points "
" WHERE id = %s ", (pid, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.POINT_NOT_FOUND')
cursor.execute(
" SELECT id "
" FROM tbl_sensors_points "
" WHERE sensor_id = %s AND point_id = %s ", (id_, pid))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.SENSOR_POINT_RELATION_NOT_FOUND')
cursor.execute(
" DELETE FROM tbl_sensors_points WHERE sensor_id = %s AND point_id = %s ",
(id_, pid))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_post(req, resp, id_):
"""Handles POST requests"""
access_control(req)
try:
raw_json = req.stream.read().decode('utf-8')
except Exception as ex:
raise falcon.HTTPError(falcon.HTTP_400, title='API.EXCEPTION', description=ex)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_DISTRIBUTION_CIRCUIT_ID')
new_values = json.loads(raw_json)
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(" SELECT name "
" from tbl_distribution_circuits "
" WHERE id = %s ", (id_,))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
description='API.DISTRIBUTION_CIRCUIT_NOT_FOUND')
cursor.execute(" SELECT name "
" FROM tbl_points "
" WHERE id = %s ", (new_values['data']['point_id'],))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
description='API.POINT_NOT_FOUND')
query = (" SELECT id "
" FROM tbl_distribution_circuits_points "
" WHERE distribution_circuit_id = %s AND point_id = %s")
cursor.execute(query, (id_, new_values['data']['point_id'],))
if cursor.fetchone() is not None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_400, title='API.ERROR',
description='API.DISTRIBUTION_CIRCUIT_POINT_RELATION_EXISTS')
add_row = (" INSERT INTO tbl_distribution_circuits_points (distribution_circuit_id, point_id) "
" VALUES (%s, %s) ")
cursor.execute(add_row, (id_, new_values['data']['point_id'],))
new_id = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_201
resp.location = '/distributioncircuits/' + str(id_) + '/points/' + str(new_values['data']['point_id'])
def on_get(req, resp):
access_control(req)
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
query = (" SELECT id, name, uuid " " FROM tbl_gateways ")
cursor.execute(query)
rows_gateways = cursor.fetchall()
gateway_dict = dict()
if rows_gateways is not None and len(rows_gateways) > 0:
for row in rows_gateways:
gateway_dict[row[0]] = {
"id": row[0],
"name": row[1],
"uuid": row[2]
}
query = (
" SELECT id, name, uuid, gateway_id, protocol, connection, last_seen_datetime_utc "
" FROM tbl_data_sources "
" ORDER BY id ")
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
cnx.close()
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(
config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
result = list()
if rows is not None and len(rows) > 0:
for row in rows:
if isinstance(row[6], datetime):
last_seen_datetime_local = row[6].replace(
tzinfo=timezone.utc) + timedelta(
minutes=timezone_offset)
last_seen_datetime = last_seen_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
else:
last_seen_datetime = None
meta_result = {
"id": row[0],
"name": row[1],
"uuid": row[2],
"gateway": gateway_dict.get(row[3]),
"protocol": row[4],
"connection": row[5],
"last_seen_datetime": last_seen_datetime
}
result.append(meta_result)
resp.text = json.dumps(result)
def on_get(req, resp, id_):
"""Handles GET requests"""
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_POINT_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
query = (" SELECT id, name, uuid " " FROM tbl_data_sources ")
cursor.execute(query)
rows_data_sources = cursor.fetchall()
data_source_dict = dict()
if rows_data_sources is not None and len(rows_data_sources) > 0:
for row in rows_data_sources:
data_source_dict[row[0]] = {
"id": row[0],
"name": row[1],
"uuid": row[2]
}
query = (
" SELECT id, name, data_source_id, object_type, units, "
" high_limit, low_limit, ratio, is_trend, is_virtual, address, description "
" FROM tbl_points "
" WHERE id = %s ")
cursor.execute(query, (id_, ))
row = cursor.fetchone()
cursor.close()
cnx.close()
if row is None:
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.POINT_NOT_FOUND')
data_source = data_source_dict.get(row[2], None)
result = {
"id": row[0],
"name": row[1],
"data_source": data_source,
"object_type": row[3],
"units": row[4],
"high_limit": row[5],
"low_limit": row[6],
"ratio": float(row[7]),
"is_trend": bool(row[8]),
"is_virtual": bool(row[9]),
"address": row[10],
"description": row[11]
}
resp.text = json.dumps(result)
def on_post(req, resp):
"""Handles POST requests"""
access_control(req)
try:
raw_json = req.stream.read().decode('utf-8')
except Exception as ex:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.ERROR',
description=ex)
new_values = json.loads(raw_json)
if 'name' not in new_values['data'].keys() or \
not isinstance(new_values['data']['name'], str) or \
len(str.strip(new_values['data']['name'])) == 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_SENSOR_NAME')
name = str.strip(new_values['data']['name'])
if 'description' in new_values['data'].keys() and \
new_values['data']['description'] is not None and \
len(str(new_values['data']['description'])) > 0:
description = str.strip(new_values['data']['description'])
else:
description = None
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT name "
" FROM tbl_sensors "
" WHERE name = %s ", (name, ))
if cursor.fetchone() is not None:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.SENSOR_NAME_IS_ALREADY_IN_USE')
add_values = (" INSERT INTO tbl_sensors "
" (name, uuid, description) "
" VALUES (%s, %s, %s) ")
cursor.execute(add_values, (name, str(uuid.uuid4()), description))
new_id = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_201
resp.location = '/sensors/' + str(new_id)
def on_get(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_EMAIL_MESSAGE_ID')
cnx = mysql.connector.connect(**config.myems_fdd_db)
cursor = cnx.cursor()
query = (
" SELECT id, recipient_name, recipient_email, "
" subject, message, attachment_file_name, "
" created_datetime_utc, scheduled_datetime_utc, status "
" FROM tbl_email_messages "
" WHERE id = %s ")
cursor.execute(query, (id_, ))
row = cursor.fetchone()
if cursor:
cursor.close()
if cnx:
cnx.close()
if row is None:
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.EMAIL_MESSAGE_NOT_FOUND')
result = {
"id":
row[0],
"recipient_name":
row[1],
"recipient_email":
row[2],
"subject":
row[3],
"message":
row[4].replace("<br>", ""),
"attachment_file_name":
row[5],
"created_datetime":
row[6].timestamp() *
1000 if isinstance(row[6], datetime) else None,
"scheduled_datetime":
row[7].timestamp() *
1000 if isinstance(row[7], datetime) else None,
"status":
row[8]
}
resp.text = json.dumps(result)
def on_get(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_DATA_SOURCE_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT name "
" FROM tbl_data_sources "
" WHERE id = %s ", (id_, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.DATA_SOURCE_NOT_FOUND')
result = list()
# Get points of the data source
# NOTE: there is no uuid in tbl_points
query_point = (
" SELECT id, name, object_type, "
" units, high_limit, low_limit, ratio, is_trend, is_virtual, address, description "
" FROM tbl_points "
" WHERE data_source_id = %s "
" ORDER BY id ")
cursor.execute(query_point, (id_, ))
rows_point = cursor.fetchall()
if rows_point is not None and len(rows_point) > 0:
for row in rows_point:
meta_result = {
"id": row[0],
"name": row[1],
"object_type": row[2],
"units": row[3],
"high_limit": row[4],
"low_limit": row[5],
"ratio": float(row[6]),
"is_trend": bool(row[7]),
"is_virtual": bool(row[8]),
"address": row[9],
"description": row[10]
}
result.append(meta_result)
cursor.close()
cnx.close()
resp.text = json.dumps(result)
def on_get(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_TEXT_MESSAGE_ID')
cnx = mysql.connector.connect(**config.myems_fdd_db)
cursor = cnx.cursor()
query = (
" SELECT id, recipient_name, recipient_mobile, "
" message, created_datetime_utc, scheduled_datetime_utc, acknowledge_code, status "
" FROM tbl_text_messages_outbox "
" WHERE id = %s ")
cursor.execute(query, (id_, ))
row = cursor.fetchone()
if cursor:
cursor.close()
if cnx:
cnx.close()
if row is None:
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.TEXT_MESSAGE_NOT_FOUND')
result = {
"id":
row[0],
"recipient_name":
row[1],
"recipient_mobile":
row[2],
"message":
row[3],
"created_datetime":
row[4].timestamp() *
1000 if isinstance(row[4], datetime) else None,
"scheduled_datetime":
row[5].timestamp() *
1000 if isinstance(row[5], datetime) else None,
"acknowledge_code":
row[6],
"status":
row[7]
}
resp.text = json.dumps(result)
def on_get(req, resp):
"""Handles GET requests"""
access_control(req)
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
query = (" SELECT id, name, uuid " " FROM tbl_data_sources ")
cursor.execute(query)
rows_data_sources = cursor.fetchall()
data_source_dict = dict()
if rows_data_sources is not None and len(rows_data_sources) > 0:
for row in rows_data_sources:
data_source_dict[row[0]] = {
"id": row[0],
"name": row[1],
"uuid": row[2]
}
query = (
" SELECT id, name, data_source_id, object_type, units, "
" high_limit, low_limit, ratio, is_trend, is_virtual, address, description "
" FROM tbl_points ")
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
cnx.close()
result = list()
if rows is not None and len(rows) > 0:
for row in rows:
data_source = data_source_dict.get(row[2], None)
meta_result = {
"id": row[0],
"name": row[1],
"data_source": data_source,
"object_type": row[3],
"units": row[4],
"high_limit": row[5],
"low_limit": row[6],
"ratio": float(row[7]),
"is_trend": bool(row[8]),
"is_virtual": bool(row[9]),
"address": row[10],
"description": row[11]
}
result.append(meta_result)
resp.text = json.dumps(result)
def on_get(req, resp, id_):
"""Handles GET requests"""
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_COST_FILE_ID')
cnx = mysql.connector.connect(**config.myems_historical_db)
cursor = cnx.cursor()
query = (" SELECT uuid, file_object "
" FROM tbl_cost_files "
" WHERE id = %s ")
cursor.execute(query, (id_, ))
row = cursor.fetchone()
cursor.close()
cnx.close()
if row is None:
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.COST_FILE_NOT_FOUND')
result = {"uuid": row[0], "file_object": row[1]}
try:
raw_blob = result["file_object"]
file_uuid = result["uuid"]
# Define file_path
file_path = os.path.join(config.upload_path, file_uuid)
# Write to a temporary file to prevent incomplete files from
# being used.
temp_file_path = file_path + '~'
open(temp_file_path, 'wb').write(raw_blob)
# Now that we know the file has been fully saved to disk
# move it into place.
os.replace(temp_file_path, file_path)
except Exception as ex:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.ERROR',
description='API.FAILED_TO_RESTORE_COST_FILE')
resp.text = json.dumps('success')
def on_get(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
description='API.INVALID_USER_ID')
cnx = mysql.connector.connect(**config.myems_user_db)
cursor = cnx.cursor()
query = (" SELECT u.id, u.name, u.display_name, u.uuid, "
" u.email, u.is_admin, p.id, p.name, "
" u.account_expiration_datetime_utc, u.password_expiration_datetime_utc,"
" u.failed_login_count "
" FROM tbl_users u "
" LEFT JOIN tbl_privileges p ON u.privilege_id = p.id "
" WHERE u.id =%s ")
cursor.execute(query, (id_,))
row = cursor.fetchone()
cursor.close()
cnx.close()
if row is None:
raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
description='API.USER_NOT_FOUND')
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
account_expiration_datetime_local = row[8].replace(tzinfo=timezone.utc) + timedelta(minutes=timezone_offset)
password_expiration_datetime_local = row[9].replace(tzinfo=timezone.utc) + timedelta(minutes=timezone_offset)
result = {"id": row[0],
"name": row[1],
"display_name": row[2],
"uuid": row[3],
"email": row[4],
"is_admin": True if row[5] else False,
"privilege": {
"id": row[6],
"name": row[7]} if row[6] is not None else None,
"account_expiration_datetime":
account_expiration_datetime_local.strftime('%Y-%m-%dT%H:%M:%S'),
"password_expiration_datetime":
password_expiration_datetime_local.strftime('%Y-%m-%dT%H:%M:%S'),
"is_locked": True if row[10] >= config.maximum_failed_login_count else False}
resp.text = json.dumps(result)
def on_delete(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_DATA_SOURCE_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT name "
" FROM tbl_data_sources "
" WHERE id = %s ", (id_, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.DATA_SOURCE_NOT_FOUND')
# check if this data source is being used by any meters
cursor.execute(
" SELECT DISTINCT(m.name) "
" FROM tbl_meters m, tbl_meters_points mp, tbl_points p, tbl_data_sources ds "
" WHERE m.id = mp.meter_id AND mp.point_id = p.id AND p.data_source_id = ds.id "
" AND ds.id = %s "
" LIMIT 1 ", (id_, ))
row_meter = cursor.fetchone()
if row_meter is not None:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.THIS_DATA_SOURCE_IS_BEING_USED_BY_A_METER' +
row_meter[0])
cursor.execute(" DELETE FROM tbl_points WHERE data_source_id = %s ",
(id_, ))
cursor.execute(" DELETE FROM tbl_data_sources WHERE id = %s ", (id_, ))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_delete(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_DISTRIBUTION_SYSTEM_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT name "
" FROM tbl_distribution_systems "
" WHERE id = %s ", (id_, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.DISTRIBUTION_SYSTEM_NOT_FOUND')
# check associated distribution circuits
cursor.execute(
" SELECT id "
" FROM tbl_distribution_circuits "
" WHERE distribution_system_id = %s ", (id_, ))
rows_distribution_circuits = cursor.fetchall()
if rows_distribution_circuits is not None and len(
rows_distribution_circuits) > 0:
cursor.close()
cnx.close()
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.THERE_IS_ASSOCIATED_DISTRIBUTION_CIRCUITS')
cursor.execute(" DELETE FROM tbl_distribution_systems WHERE id = %s ",
(id_, ))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_get(req, resp, id_):
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_GATEWAY_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
query = (" SELECT id, name, uuid, token, last_seen_datetime_utc "
" FROM tbl_gateways "
" WHERE id =%s ")
cursor.execute(query, (id_, ))
row = cursor.fetchone()
cursor.close()
cnx.close()
if row is None:
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.GATEWAY_NOT_FOUND')
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 isinstance(row[4], datetime):
last_seen_datetime_local = row[4].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
last_seen_datetime = last_seen_datetime_local.strftime(
'%Y-%m-%dT%H:%M:%S')
else:
last_seen_datetime = None
result = {
"id": row[0],
"name": row[1],
"uuid": row[2],
"token": row[3],
"last_seen_datetime": last_seen_datetime
}
resp.text = json.dumps(result)
def on_put(req, resp, id_):
"""Handles PUT requests"""
access_control(req)
try:
raw_json = req.stream.read().decode('utf-8')
except Exception as ex:
raise falcon.HTTPError(falcon.HTTP_400, 'API.ERROR', ex)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_TIMEZONE_ID')
new_values = json.loads(raw_json)
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT name "
" FROM tbl_timezones "
" WHERE id = %s ", (id_, ))
if cursor.fetchone() is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.TIMEZONE_NOT_FOUND')
update_row = (" UPDATE tbl_timezones "
" SET name = %s, description = %s, utc_offset = %s "
" WHERE id = %s ")
cursor.execute(update_row, (
new_values['data']['name'],
new_values['data']['description'],
new_values['data']['utc_offset'],
id_,
))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_200
def on_delete(req, resp, id_):
"""Handles DELETE requests"""
access_control(req)
if not id_.isdigit() or int(id_) <= 0:
raise falcon.HTTPError(falcon.HTTP_400,
title='API.BAD_REQUEST',
description='API.INVALID_KNOWLEDGE_FILE_ID')
cnx = mysql.connector.connect(**config.myems_system_db)
cursor = cnx.cursor()
cursor.execute(
" SELECT uuid "
" FROM tbl_knowledge_files "
" WHERE id = %s ", (id_, ))
row = cursor.fetchone()
if row is None:
cursor.close()
cnx.close()
raise falcon.HTTPError(falcon.HTTP_404,
title='API.NOT_FOUND',
description='API.KNOWLEDGE_FILE_NOT_FOUND')
try:
file_uuid = row[0]
# Define file_path
file_path = os.path.join(config.upload_path, file_uuid)
# remove the file from disk
os.remove(file_path)
except Exception as ex:
raise falcon.HTTPError(
falcon.HTTP_400,
title='API.ERROR',
description='API.KNOWLEDGE_FILE_CANNOT_BE_REMOVED_FROM_DISK')
cursor.execute(" DELETE FROM tbl_knowledge_files WHERE id = %s ",
(id_, ))
cnx.commit()
cursor.close()
cnx.close()
resp.status = falcon.HTTP_204
def on_get(req, resp):
access_control(req)
cnx = mysql.connector.connect(**config.myems_user_db)
cursor = cnx.cursor()
query = (" SELECT u.id, u.name, u.display_name, u.uuid, "
" u.email, u.is_admin, p.id, p.name, "
" u.account_expiration_datetime_utc, u.password_expiration_datetime_utc, u.failed_login_count "
" FROM tbl_users u "
" LEFT JOIN tbl_privileges p ON u.privilege_id = p.id "
" ORDER BY u.name ")
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
cnx.close()
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
if config.utc_offset[0] == '-':
timezone_offset = -timezone_offset
result = list()
if rows is not None and len(rows) > 0:
for row in rows:
account_expiration_datetime_local = row[8].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
password_expiration_datetime_local = row[9].replace(tzinfo=timezone.utc) + \
timedelta(minutes=timezone_offset)
meta_result = {"id": row[0],
"name": row[1],
"display_name": row[2],
"uuid": row[3],
"email": row[4],
"is_admin": True if row[5] else False,
"privilege": {
"id": row[6],
"name": row[7]} if row[6] is not None else None,
"account_expiration_datetime":
account_expiration_datetime_local.strftime('%Y-%m-%dT%H:%M:%S'),
"password_expiration_datetime":
password_expiration_datetime_local.strftime('%Y-%m-%dT%H:%M:%S'),
"is_locked": True if row[10] >= config.maximum_failed_login_count else False}
result.append(meta_result)
resp.text = json.dumps(result)
