def records2():
f_ingreso = str(request.args.get('f_ingreso'))
sql = """SELECT p_timestamp,
p_identification,
p_name,
p_last_name,
t_temperature_body,
t_alert,
t_image_rgb,
t_image_thermal
FROM records
WHERE t_alert={}
ORDER BY record_id DESC""".format(f_ingreso)
cur = get_db().cursor()
cur.execute(sql)
data = dictfetchall(cur)
for record in data:
image_rgb = record.get('t_image_rgb')
if image_rgb is not None:
record['t_image_rgb'] = b64encode(image_rgb).decode("utf-8")
for record in data:
image_thermal = record.get('t_image_thermal')
if image_thermal is not None:
record['t_image_thermal'] = b64encode(image_thermal).decode("utf-8")
table_builder = TableBuilder()
data = table_builder.collect_data_serverside(request, data)
return jsonify(data)
def records():
page_size = request.args.get('page_size', type=int, default=6)
page = request.args.get('page', type=int, default=1)
offset = (page - 1) * page_size
cur = get_db().cursor()
cur.execute("SELECT COUNT(1) as total FROM records")
total = dictfetchone(cur)['total']
num_pages = -(-total // page_size)
cur.execute("SELECT * FROM records ORDER BY record_id DESC LIMIT ? OFFSET ?", (page_size, offset))
data = dictfetchall(cur)
for record in data:
image_rgb = record.get('t_image_rgb')
if image_rgb is not None:
record['t_image_rgb'] = b64encode(image_rgb).decode("utf-8")
for record in data:
image_thermal = record.get('t_image_thermal')
if image_thermal is not None:
record['t_image_thermal'] = b64encode(image_thermal).decode("utf-8")
response = {
'count': total,
'next': page + 1 if page < num_pages else None,
'previous': page - 1 if page > 0 else None,
'results': data
}
return jsonify(response)
def latest_record():
now = datetime.now().isoformat()
cur = get_db().cursor()
query = """SELECT record_id, p_identification, p_timestamp, p_name, p_last_name, p_gender, p_birth_date,
p_blood_type, t_timestamp, t_temperature_p80, t_temperature_body, t_alert, p_alert
FROM records WHERE ( JULIANDAY('{}') > JULIANDAY(t_timestamp) )
AND (cast(JULIANDAY('{}') - JULIANDAY(t_timestamp) as float ) *60 * 60 * 24) < {}
ORDER BY record_id DESC LIMIT 1""".format(now, now, 5)
cur.execute(query)
data = dictfetchone(cur)
return jsonify(data)
def record_rgb():
record_id = request.args.get('record_id', type=int, default=None)
if record_id is not None:
cur = get_db().cursor()
cur.execute("""SELECT record_id, t_image_rgb FROM records WHERE record_id=? LIMIT 1""", (record_id,))
data = dictfetchone(cur)
image_rgb = data.get('t_image_rgb')
if image_rgb is not None:
data['t_image_rgb'] = b64encode(image_rgb).decode("utf-8")
return jsonify(data)
else:
return None
def records_csv():
# f_ingreso = str(request.args.get('f_ingreso'))
# sql = """SELECT t_timestamp,
# p_identification,
# p_name,
# p_last_name,
# t_temperature_body,
# CASE
# WHEN t_alert=0 THEN 'Permitido'
# WHEN t_alert=1 THEN 'Advertencia'
# WHEN t_alert=2 THEN 'Peligro'
# ELSE 'None'
# END as t_alert
# FROM records
# WHERE t_alert={}
# ORDER BY record_id DESC""".format(f_ingreso)
sql = """SELECT t_timestamp,
p_identification,
p_name,
p_last_name,
t_temperature_body,
CASE
WHEN t_alert=0 THEN 'Permitido'
WHEN t_alert=1 THEN 'Advertencia'
WHEN t_alert=2 THEN 'Peligro'
ELSE 'None'
END as t_alert
FROM records
ORDER BY record_id DESC"""
cur = get_db().cursor()
cur.execute(sql)
data = arrayfetchall(cur)
data_cols = ['FECHA Y HORA', 'IDENTIFICACION', 'NOMBRES', 'APELLIDOS', 'TEMPERATURA', 'INGRESO']
data.insert(0, data_cols)
si = StringIO()
cw = csv.writer(si, delimiter=',')
cw.writerows(data)
output = make_response(si.getvalue())
output.headers["Content-Disposition"] = "attachment; filename=records_termodeep.csv"
output.headers["Content-type"] = "text/csv"
return output
def barcode_scan():
content = request.json
data = content.get('data')
p_extra_json = data.get('extra_json')
if p_extra_json is not None:
p_extra_json = json.dumps(p_extra_json)
db = get_db()
cur = db.cursor()
cur.execute(
"""INSERT INTO records (mac_address, p_barcode_type, p_identification, p_timestamp, p_name, p_last_name,
p_gender, p_birth_date, p_blood_type, p_extra_json, p_extra_txt, p_alert) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
(MAC_ADDRESS, content.get('barcode_type'), data.get('identification'), content.get('timestamp'),
data.get('name'), data.get('last_name'), data.get('gender'), data.get('birth_date'), data.get('blood_type'),
p_extra_json, data.get('extra_txt'), data.get('alert')))
db.commit()
RecordCompleter(cur.lastrowid)
return jsonify(data)
def serverside_table():
global global_num_records
global global_records
# Get the total number of rows in the database. If its equal to the previous one, do not repeat the data query.
sql = """SELECT COUNT(p_timestamp) as total_data
FROM records;
"""
cur = get_db().cursor()
cur.execute(sql)
total_records = dictfetchall(cur)[0]["total_data"]
if total_records != global_num_records:
sql = """SELECT record_id,
p_timestamp,
p_identification,
p_name,
p_last_name,
t_temperature_body,
t_alert,
CASE
WHEN t_alert=0 THEN 'Permitido'
WHEN t_alert=1 THEN 'Advertencia'
WHEN t_alert=2 THEN 'Peligro'
ELSE 'None'
END as t_alert_2
FROM records
ORDER BY record_id DESC"""
cur = get_db().cursor()
cur.execute(sql)
data = dictfetchall(cur)
global_records = data
global_num_records = total_records
else:
data = global_records
table_builder = TableBuilder()
data_2_return = table_builder.collect_data_serverside(request, data)
# First get the list of ids in order to do a query of the images
if len(data_2_return['data']) > 0:
id_list = "("
for record in data_2_return['data']:
id_list = id_list + str(record['id']) + ", "
id_list = id_list[:-2]
id_list = id_list + ")"
print(id_list)
# Do the query of the image only if id is more than 0
sql = """SELECT t_image_thermal,
t_image_rgb
FROM records
WHERE record_id IN {}""".format(id_list)
cur = get_db().cursor()
cur.execute(sql)
image_data = dictfetchall(cur)
print(len(image_data))
# Add the images into the response
for (record, images) in zip(data_2_return['data'], image_data):
image_rgb = images.get('t_image_rgb')
if image_rgb is not None:
record['data8'] = b64encode(image_rgb).decode("utf-8")
image_thermal = images.get('t_image_thermal')
if image_thermal is not None:
record['data7'] = b64encode(image_thermal).decode("utf-8")
return jsonify(data_2_return)