def get_fatigue_signal():
with get_connect_ms_sql() as connect:
cursor = connect.cursor()
sql = """
select
count(max_eye) as count_eve
from hackaton.dbo.screen_history
where 1 = 1
and stime >= dateadd(MINUTE, -1, GETDATE())
and max_eye < 0.5 and max_eye != -1
"""
cursor.execute(sql)
result = cursor.fetchone()[0]
if result >= 50:
cursor.execute("""
INSERT INTO hackaton.dbo.alert_history
(
[alert_time], [dev_name], [val], [is_sent]
)
VALUES
(
GETDATE(), 'fatigue', '{}', 0
)
""".format(result))
cursor.commit()
def insert_to_alert_history(name, eye):
with get_connect_ms_sql() as connect:
cursor = connect.cursor()
sql = """
insert into hackaton.dbo.screen_history (stime, sname, max_eye) values
(convert(varchar, getdate(), 120), '{}', {});
""".format(name, eye)
cursor.execute(sql)
cursor.commit()
def get_last_signals():
connect = get_connect_ms_sql()
cursor = connect.cursor()
answer_dict = dict()
for i in ('light', 'micro', 'sonar', 'humidity', 'temperature', 'fatigue'):
sql = """select top 1 * from hackaton.dbo.alert_history where is_sent = 0 and dev_name='{}'""".format(
i)
cursor.execute(sql)
for row in cursor.fetchall():
answer_dict[i] = {"timestamp": str(row[1]), "val": row[3]}
update_sig()
return answer_dict
def update_sig():
connect = get_connect_ms_sql()
cursor = connect.cursor()
sql_select_query = """select * from hackaton.dbo.alert_history where is_sent = 0"""
cursor.execute(sql_select_query)
record = cursor.fetchall()
for row in record:
id = row[0]
cursor.execute(
"""update hackaton.dbo.alert_history set is_sent = 1 where alert_id = {}"""
.format(id))
cursor.commit()
def get_last_signals():
connect = get_connect_ms_sql()
cursor = connect.cursor()
sql_select_query = """select top 1 * from hackaton.dbo.alert_history where is_sent = 0"""
cursor.execute(sql_select_query)
record = cursor.fetchall()
answer_list = list()
for row in record:
answer_dict = dict()
answer_dict["timestamp"] = row[1]
answer_dict["device"] = row[2]
answer_dict["val"] = row[3]
answer_list.append(answer_dict)
update_sig()
return answer_list
def get_all_signals():
connect = get_connect_ms_sql()
cursor = connect.cursor()
sql_select_query = """select * from hackaton.dbo.alert_base where alert_time >= cast(GETDATE() as date) order by id desc"""
cursor.execute(sql_select_query)
record = cursor.fetchall()
answer_list = list()
for row in record:
answer_dict = dict()
answer_dict["id"] = row[0]
answer_dict["head"] = row[1]
answer_dict["body"] = row[2]
answer_dict["img"] = row[3]
answer_dict["level"] = row[4]
answer_dict["datetime"] = str(row[5])
answer_list.append(answer_dict)
return answer_list
