class InsertDataFrames(QThread):
data_frames = Queue()
def __init__(self):
super().__init__()
self.database_session = Session()
self.operate: bool = True
def add_new_frame(self, frame: DataFrame):
self.data_frames.put_nowait(frame)
def run(self):
self.operate = True
time_out = 7
while self.operate:
print("committing")
frame: DataFrame = self.data_frames.get(timeout=time_out)
if frame is None:
continue
self.database_session.add(frame)
self.database_session.commit()
# when the serial stops all the data in the queue should be flushed and the session is closed
while self.data_frames.qsize() != 0:
frame: DataFrame = self.data_frames.get_nowait()
self.database_session.add(frame)
self.database_session.commit()
self.database_session.close()
def stop(self):
self.operate = False
def get_users():
session = Session()
users = session.query(tables.User).all()
session.close()
return users
def find_debs(user_id):
session = Session()
debs = session.query(tables.Debs).filter(
or_(tables.Debs.user_id == user_id,
tables.Debs.foreign_id == user_id)).all()
session.close()
return debs
def is_user_exist_by_username(user_name):
session = Session()
user = session.query(
tables.User).filter(tables.User.user_name == user_name).first()
session.close()
if user:
return user
else:
return False
def is_user_exist(user_id):
session = Session()
user = session.query(
tables.User).filter(tables.User.user_id == user_id).first()
session.close()
if user:
return user
else:
return False
def create_debs(debs):
session = Session()
try:
session.add_all(debs)
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def check_access(user_id, foreign_id):
session = Session()
access = session.query(
tables.Access).filter(tables.Access.user_id == user_id).filter(
tables.Access.access == foreign_id).first()
session.close()
if access:
return access
else:
return False
def change_settings(user_id, value):
session = Session()
try:
session.query(tables.Settings).filter(
tables.Settings.user_id == user_id).update({'lite': value})
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def update_username(user_id, user_name):
session = Session()
try:
session.query(tables.User).filter(
tables.User.user_id == user_id).update({'user_name': user_name})
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def order_up(order_id, text):
session = Session()
try:
session.query(tables.Orders).filter(
tables.Orders.order_id == order_id).update(
{'text': text})
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def order_price_up(order_id, price):
session = Session()
try:
session.query(tables.Orders).filter(
tables.Orders.order_id == order_id).update(
{'price': price})
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def create_access(user_id, foreign_id):
session = Session()
access = tables.Access(user_id=user_id, access=foreign_id)
try:
session.add(access)
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def update_order_status(order_id, status, date):
session = Session()
try:
session.query(tables.Orders).filter(
tables.Orders.order_id == order_id).update(
{'status': status,
'date': date})
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def get_latest_sigfox_messages():
"""
Returns a 200 OK with the 'latest_sigfox_messages' table content (JSON formatted)
Returns:
json: A json representation of the content of table sigfox_messages with HTTP 200 OK.
"""
db_session = Session()
qryresult = db_session.query(LatestSigFoxMessages).all()
resp_json = jsonify(json_list=[i.serialize for i in qryresult])
db_session.close()
return resp_json
def hide_all(user_id):
session = Session()
access = session.query(tables.Access).filter(tables.Access.user_id == user_id).all()
foreign_ids = []
for ac in access:
foreign_ids.append(ac.access)
session.query(tables.Orders).filter(tables.Orders.user_id.in_(foreign_ids)).update({'hide': 1})
session.commit()
session.close()
def get_latest_weight(device_id):
"""
Returns a HTTP 200 OK with the latest weight for the given device (JSON formatted)
Returns:
json: A json representation of the latest device stored weight with HTTP 200 OK.
"""
db_session = Session()
qryresult = db_session.query(LatestSigFoxMessages).filter(
LatestSigFoxMessages.device_id == device_id).one()
weight = qryresult.weight
resp_json = jsonify({"weight": weight})
db_session.close()
return resp_json
def get_to_split(user_id):
session = Session()
access = session.query(tables.Access).filter(tables.Access.user_id == user_id).all()
foreign_ids = []
for ac in access:
foreign_ids.append(ac.access)
orders = session.query(tables.Orders).filter(tables.Orders.user_id.in_(foreign_ids)).filter(
tables.Orders.hide == 0).filter(tables.Orders.status == 1).all()
debs = session.query(tables.Debs).distinct().filter(or_(tables.Debs.user_id == user_id, tables.Debs.foreign_id == user_id)).all()
session.close()
splited = []
for deb in debs:
splited.append(deb.order_id)
out = []
for order in orders:
if order.order_id not in splited:
out.append(order)
return out
def get_sigfox_messages_for_device(device_id):
"""
Returns a 200 OK with the 'sigfox_messages' table content for a given device (JSON formatted)
Args:
device_id (str): the SigFox device id that has triggered the callback
Returns:
json: A json representation of the content of table sigfox_messages (for one device) with HTTP 200 OK.
"""
db_session = Session()
qryresult = db_session.query(SigFoxMessages).filter(
SigFoxMessages.device_id == device_id)
resp_json = jsonify(json_list=[i.serialize for i in qryresult])
db_session.close()
return resp_json
def execute(self) -> None:
# Properly stops reading, closes the port, and stops executing the thread
self.dispatcher.serial_reader.stop()
# Stopping Adding new frames to the database
insert_data_frames.stop()
insert_data_frames.wait()
self.dispatcher.current_lap.finish_lap()
database_session = Session()
database_session.add(self.dispatcher.current_lap)
database_session.commit()
database_session.close()
def execute(self) -> None:
# Start the serial reading thread
self.dispatcher.current_lap = Lap(self.lap_name)
database_session = Session()
database_session.add(self.dispatcher.current_lap)
database_session.commit()
database_session.close()
# Starting the thread that insert new frames to the database
insert_data_frames.start()
# Start the serial reader
if self.connection_type == ConnectionTypes.USB:
self.dispatcher.serial_reader.start()
elif self.connection_type == ConnectionTypes.WIFI:
self.dispatcher.wifi_reader.start()
def get_orders(user_id):
session = Session()
access = session.query(tables.Access).filter(tables.Access.user_id == user_id).all()
foreign_ids = []
for ac in access:
foreign_ids.append(ac.access)
orders = session.query(tables.Orders).filter(tables.Orders.user_id.in_(foreign_ids)).filter(tables.Orders.hide == 0).all()
session.close()
return orders
def get_access(user_id):
session = Session()
access1 = session.query(
tables.Access).filter(tables.Access.user_id == user_id).all()
access2 = session.query(
tables.Access).filter(tables.Access.access == user_id).all()
ids = []
for ac in access1:
if user_id != ac.access:
ids.append(ac.access)
for ac in access2:
if user_id != ac.user_id:
ids.append(ac.user_id)
users = session.query(tables.User).filter(
tables.User.user_id.in_(ids)).all()
session.close()
return users
def get_words(types):
session = Session()
words = session.query(Word).filter(Word.type.in_(types)).all()
session.close()
return words
def delete_access(access_id):
session = Session()
session.query(
tables.Access).filter(tables.Access.access_id == access_id).delete()
session.commit()
session.close()
def create_order(user_id, text):
session = Session()
user = session.query(tables.User).filter(
tables.User.user_id == user_id).first()
date = create_date()
order = tables.Orders(user_id=user_id,
user_name=user.user_name,
text=text,
created=date)
try:
session.add(order)
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def hide_order(order_id):
session = Session()
session.query(tables.Orders).filter(tables.Orders.order_id == order_id).update({'hide': 1})
session.commit()
session.close()
def order_status(order_id):
session = Session()
order = session.query(tables.Orders).filter(tables.Orders.order_id == order_id).first()
session.close()
return order
def __init__(self):
super().__init__()
self.database_session = Session()
self.operate: bool = True
def callback_handler(device_id):
"""
Receives the json formatted callback message from a given sigfox device.
It stores the information into the database.
Args:
device_id (str): the SigFox device id that has triggered the callback
Returns:
json: The device_id as a response with HTTP 200 OK.
"""
json_msg = request.json
db_session = Session()
# Append message to 'sigfox_messages' table
db_msg = SigFoxMessages()
db_msg.device_id = json_msg["device_id"]
db_msg.seq_num = json_msg["seq_num"]
db_msg.weight = json_msg["weight"]
db_session.add(db_msg)
# Create 'latest_sigfox_messages' table entry or update it if existing for the current device_id
if db_session.query(LatestSigFoxMessages).filter(
LatestSigFoxMessages.device_id == device_id).count() == 0:
latest_db_msg = LatestSigFoxMessages()
latest_db_msg.device_id = json_msg["device_id"]
latest_db_msg.seq_num = json_msg["seq_num"]
latest_db_msg.weight = json_msg["weight"]
db_session.add(latest_db_msg)
else:
entry_to_update = db_session.query(LatestSigFoxMessages).filter(
LatestSigFoxMessages.device_id == device_id).one()
entry_to_update.seq_num = json_msg["seq_num"]
entry_to_update.weight = json_msg["weight"]
db_session.commit()
db_session.close()
# Notify the Angular2 client that the database entry has been updated
socket_io.emit('weight_update', {
'device_id': json_msg["device_id"],
'seq_num': json_msg["seq_num"],
'weight': json_msg["weight"]
},
namespace='/')
return jsonify({"device_id": device_id})
def add_word(word):
session = Session()
session.add(word)
session.commit()
session.close()
