def get_list(): sqliteConnection = get_db() cursor = sqliteConnection.cursor() query = """SELECT pid, student_check, professor_check, professor_need, dicom_need from samples ORDER BY priority DESC, pid ASC;""" cursor.execute(query) plist = [] for ( pid, student_check, professor_check, professor_need, dicom_need, ) in cursor.fetchall(): plist.append( { "pid": pid, "professor_need": professor_need, "dicom_need": dicom_need, "student": student_check, "professor": professor_check, } ) cursor.close() return plist
def check_limit(): check_pass = True sqliteConnection = get_db() cursor = sqliteConnection.cursor() query = """SELECT COUNT(pid) from log_send WHERE uid = ? AND type = 'AdditionalSlice' AND CAST(send_time AS INTEGER)>?;""" cursor.execute(query, (g.user["id"], int(time.time()) - 24 * 60 * 60)) res = cursor.fetchone() if (res is not None) and (res[0] is not None): check_pass &= res[0] < current_app.config["MAX_ADDITIONAL_SLICE_PER_DAY"] else: check_pass = False query = """SELECT COUNT(pid) from log_send WHERE uid = ? AND type = 'NormalSlices' AND CAST(send_time AS INTEGER)>?;""" cursor.execute(query, (g.user["id"], int(time.time()) - 24 * 60 * 60)) res = cursor.fetchone() if (res is not None) and (res[0] is not None): check_pass &= res[0] < current_app.config["MAX_PATIENT_PER_DAY"] else: check_pass = False cursor.close() if not check_pass: raise BadRequest() return check_pass
def prepare_additional_slice(pid, wl, ww, z_list): assert check_limit() sqliteConnection = get_db() cursor = sqliteConnection.cursor() # load path query = """SELECT zs_path from samples where pid = ?;""" cursor.execute(query, (pid,)) zs_path = cursor.fetchone()[0] zs_path = int_key_json_load(zs_path) # new form values rnd = random.getrandbits(32) send_time = time.time() mini_slices = [] slices = [] ct = get_ct(zs_path, wl=wl, ww=ww, z_list=z_list) for z, s in zip(z_list, ct): img, thumbnail = utils.encode(s) slices.append({"z": z, "hmac": utils.zhmac(pid, z), "img": img}) mini_slices.append({"z": z, "thumbnail": thumbnail}) details = json.dumps({"slices": mini_slices, "wl": wl, "ww": ww, "IP": request.remote_addr}) # save log query = """INSERT INTO log_send (pid, uid, rnd, send_time, type, details) VALUES (?, ?, ?, ?, ?, ?);""" data_tuple = (pid, g.user["id"], rnd, send_time, "AdditionalSlice", details) cursor.execute(query, data_tuple) sqliteConnection.commit() cursor.close() return slices, send_time, rnd
def prepare_normal_slices(pid, wl, ww): assert check_limit() sqliteConnection = get_db() cursor = sqliteConnection.cursor() # load old values query = """SELECT zs_path, zs_init, professor_need, dicom_need, zs_result from samples where pid = ?;""" cursor.execute(query, (pid,)) zs_path, zs_init, professor_need, dicom_need, zs_result = cursor.fetchone() zs_path = int_key_json_load(zs_path) zs_init = int_key_json_load(zs_init) try: zs_result = int_key_json_load(zs_result) except: zs_result = {} # new form values rnd = random.getrandbits(32) send_time = time.time() mini_slices = [] slices = [] final_z = sorted(list(set(zs_result.keys()) | set(zs_init))) ct = get_ct(zs_path, wl=wl, ww=ww, z_list=final_z) for z, s in zip(final_z, ct): img, thumbnail = utils.encode(s) row = {"z": z, "hmac": utils.zhmac(pid, z), "img": img} if z in zs_result: if zs_result[z]: row["positive"] = True else: row["negative"] = True slices.append(row) mini_slices.append({"z": z, "thumbnail": thumbnail, "status": zs_result.get(z, None)}) details = json.dumps({"slices": mini_slices, "wl": wl, "ww": ww, "IP": request.remote_addr}) # save log query = """INSERT INTO log_send (pid, uid, rnd, send_time, type, details) VALUES (?, ?, ?, ?, ?, ?);""" data_tuple = (pid, g.user["id"], rnd, send_time, "NormalSlices", details) cursor.execute(query, data_tuple) sqliteConnection.commit() cursor.close() for z in range(min(final_z) - 1, max(final_z) + 2): if z not in final_z: slices.append({"z": z, "hmac": "", "img": ""}) slices = sorted(slices, key=lambda i: i['z']) return slices, send_time, rnd, professor_need, dicom_need
def receive(form): sqlite_connection = get_db() cursor = sqlite_connection.cursor() pid = int(form["pid"]) role = g.user["role"] send_time = form["send_time"] receive_time = time.time() rnd = form["rnd"] details = json.dumps({"form": form, "IP": request.remote_addr}) # save log query = """INSERT INTO log_receive (pid, uid, rnd, send_time, receive_time, details) VALUES (?, ?, ?, ?, ?, ?);""" data_tuple = (pid, g.user["id"], rnd, send_time, receive_time, details) cursor.execute(query, data_tuple) sqlite_connection.commit() # update samples zs_result = {} for name in form.keys(): if name.startswith("z") and "_" in name: tmp = name.split("_") assert (len(tmp) == 3) and (form[name] in ["positive", "negative"]) z = int(tmp[1]) hmac = tmp[2] assert utils.zhmac(pid, z) == hmac zs_result[z] = (form[name] == "positive") zs_result = json.dumps(zs_result) # keys will cast to str if ("professor_need" in form) and form["professor_need"] == "1": professor_need = 1 else: professor_need = 0 if ("dicom_need" in form) and form["dicom_need"] == "1": dicom_need = 1 else: dicom_need = 0 if role == 1: query = """Update samples set student_check = 1, professor_need = ?, dicom_need = ?, zs_result = ? where pid = ?""" else: query = """Update samples set professor_check = 1, professor_need = ?, dicom_need = ?, zs_result = ? where pid = ?""" cursor.execute(query, (professor_need, dicom_need, zs_result, pid)) sqlite_connection.commit() cursor.close()
def next_pids(pid): role = g.user["role"] sqliteConnection = get_db() cursor = sqliteConnection.cursor() if sqlite3.sqlite_version >= '3.25': if role == 1: query_next_pid = """select next from (SELECT pid, professor_check, student_check, lead(pid) OVER (ORDER BY priority DESC, pid ASC) as next from samples where (professor_check = 0 AND student_check = 0) OR pid = ?) where pid=?;""" else: query_next_pid = """select next from (SELECT pid, professor_check, student_check, lead(pid) OVER (ORDER BY priority DESC, pid ASC) as next from samples where professor_need = 1 OR pid = ?) where pid=?;""" cursor.execute(query_next_pid, (pid, pid)) res = cursor.fetchone() if (res is None) or (res[0] is None): next_pid = -1 else: next_pid = res[0] else: query_next_pid = """SELECT MAX(pid) from samples""" cursor.execute(query_next_pid) res = cursor.fetchone() next_pid = pid + 1 if next_pid > res[0]: next_pid = -1 if role == 1: query_first_pid = """SELECT pid from samples WHERE professor_check = 0 AND student_check = 0 ORDER BY priority DESC, pid ASC LIMIT 1;""" else: query_first_pid = """SELECT pid from samples WHERE professor_need = 1 ORDER BY priority DESC, pid ASC LIMIT 1;""" cursor.execute(query_first_pid) res = cursor.fetchone() if (res is None) or (res[0] is None): first_pid = -1 else: first_pid = res[0] cursor.close() return next_pid, first_pid