def fill_reference_date_table():
'''Fonction remplissant la table de référence des dates, celle-ci ne sert pas mais permet de "traduire" lorsque l'on fait un appel à la date key_dates '''
QueryScript("DROP TABLE IF EXISTS reference_key_date").execute(admin=True)
reference_date_table = QueryScript(
f"CREATE TABLE reference_key_date (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), step INT(11), barrel VARCHAR(255), version INT);"
)
reference_date_table.execute(admin=True)
SQL_request = f" INSERT INTO reference_key_date (name, step, barrel, version) VALUES (%s, %s, %s, %s)"
values = [("Transplantation Alimentation", 50, "R0"),
("Recuperation Alimentation", 60, "R7"),
("Lancement reprotoxicite", 20, None),
("Recuperation reprotoxicite", 140, "RN"),
("Arret Reprotoxicite", 170, None),
("Lancement Chimie", 50, "C0"),
("Recuperation Chimie", 100, "R21")]
reference_date_table.setScript(SQL_request)
reference_date_table.setRows(values)
reference_date_table.executemany()
def fill_reference_tables(cas, xl_path="reference_clean.xlsx"):
# To open Workbook
wb = xlrd.open_workbook(xl_path)
# Constants FROM {env.DATABASE_TREATED}.r1 sheet
sheet = wb.sheet_by_name('r1')
if cas == 1:
QueryScript(f" DROP TABLE IF EXISTS r1").execute(admin=True)
r1_table = QueryScript(
f" CREATE TABLE r1 (id INT AUTO_INCREMENT PRIMARY KEY, parameter VARCHAR(255), min FLOAT, max FLOAT, version INT)"
)
r1_table.execute(admin=True)
SQL_request = QueryScript(
f" INSERT INTO r1 (parameter, min, max, version) VALUES (%s, %s, %s, %s)"
)
values = []
index = 1
is_readable = True
while is_readable:
try:
if sheet.cell_value(index, 0):
values.append(
(sheet.cell_value(index, 0),
float(sheet.cell_value(index, 1)) if sheet.cell_value(
index, 1) else None, float(sheet.cell_value(index, 2))
if sheet.cell_value(index, 2) else None))
index += 1
else:
index += 1
except IndexError:
is_readable = False
SQL_request.setRows(values)
SQL_request.executemany()
# Constants FROM {env.DATABASE_TREATED}.r2 sheet
sheet = wb.sheet_by_name('r2_constant')
if cas == 1:
QueryScript(f" DROP TABLE IF EXISTS r2_constant").execute(admin=True)
r2_constant_table = QueryScript(
f" CREATE TABLE r2_constant (id INT AUTO_INCREMENT PRIMARY KEY, nature VARCHAR(255), name VARCHAR(255), value FLOAT, version INT)"
)
r2_constant_table.execute(admin=True)
SQL_request = QueryScript(
f" INSERT INTO r2_constant (nature, name, value, version) VALUES (%s, %s, %s, %s)"
)
values = []
index = 0
current_nature = ''
is_readable = True
while is_readable:
try:
if (sheet.cell_value(index, 0)):
current_nature = sheet.cell_value(index, 0)
index += 1
elif (sheet.cell_value(index, 1)):
values.append((current_nature, str(sheet.cell_value(index, 1)),
float(sheet.cell_value(index, 2))
if sheet.cell_value(index, 2) else None))
index += 1
else:
index += 1
except IndexError:
is_readable = False
SQL_request.setRows(values)
SQL_request.executemany()
# Threshold FROM {env.DATABASE_TREATED}.r2 sheet
sheet = wb.sheet_by_name('r2_threshold')
if cas == 1:
QueryScript(f" DROP TABLE IF EXISTS r2_threshold").execute(admin=True)
r2_threshold_table = QueryScript(
f" CREATE TABLE r2_threshold (id INT AUTO_INCREMENT PRIMARY KEY, parameter VARCHAR(255), population VARCHAR(255), type VARCHAR(255), time VARCHAR(255), threshold FLOAT, unit VARCHAR(255), rule VARCHAR(255), meaning VARCHAR(255), version INT)"
)
r2_threshold_table.execute(admin=True)
SQL_request = QueryScript(
f" INSERT INTO r2_threshold (parameter, population, type, time, threshold, unit, rule, meaning, version) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
)
values = []
index = 1
is_readable = True
while is_readable:
try:
if (sheet.cell_value(index, 1)):
values.append(
(sheet.cell_value(index, 0), sheet.cell_value(index, 1),
sheet.cell_value(index, 2), sheet.cell_value(index, 3),
float(sheet.cell_value(index, 4)) if isinstance(
sheet.cell_value(index, 4), float) else None,
sheet.cell_value(index, 5), sheet.cell_value(index, 6),
sheet.cell_value(index, 10)))
index += 1
else:
index += 1
except IndexError:
is_readable = False
SQL_request.setRows(values)
SQL_request.executemany()
# Information FROM {env.DATABASE_TREATED}.r3 sheet
sheet = wb.sheet_by_name('r3')
if cas == 1:
QueryScript(f" DROP TABLE IF EXISTS r3").execute(admin=True)
r3_table = QueryScript(
f" CREATE TABLE r3 (id INT AUTO_INCREMENT PRIMARY KEY, unit VARCHAR(255), sandre VARCHAR(255), parameter VARCHAR(255), NQE VARCHAR(255), 7j_threshold FLOAT, 7j_graduate_25 FLOAT, 7j_graduate_50 FLOAT, 7j_graduate_75 FLOAT, 21j_threshold FLOAT, 21j_graduate_25 FLOAT, 21j_graduate_50 FLOAT, 21j_graduate_75 FLOAT, case_number VARCHAR(255), familly VARCHAR(255), maximum FLOAT, freq_quanti FLOAT, concentration_t0_max FLOAT, version INT)"
)
r3_table.execute(admin=True)
SQL_request = QueryScript(
f" INSERT INTO r3 (unit, sandre, parameter, NQE, 7j_threshold, 7j_graduate_25, 7j_graduate_50, 7j_graduate_75, 21j_threshold, 21j_graduate_25, 21j_graduate_50, 21j_graduate_75, case_number, familly, maximum, freq_quanti, concentration_t0_max, version) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
)
values = []
index = 1
is_readable = True
while is_readable:
try:
if (sheet.cell_value(index, 1)):
values.append(
(sheet.cell_value(index, 0), int(sheet.cell_value(
index, 1)) if isinstance(sheet.cell_value(
index, 1), float) else sheet.cell_value(index, 1),
sheet.cell_value(index, 2), sheet.cell_value(index, 3),
float(sheet.cell_value(index, 4)) if isinstance(
sheet.cell_value(index, 4), float) else None,
float(sheet.cell_value(index, 5)) if isinstance(
sheet.cell_value(index, 5), float) else None,
float(sheet.cell_value(index, 6)) if isinstance(
sheet.cell_value(index, 6), float) else None,
float(sheet.cell_value(index, 7)) if isinstance(
sheet.cell_value(index, 7), float) else None,
float(sheet.cell_value(index, 8)) if isinstance(
sheet.cell_value(index, 8), float) else None,
float(sheet.cell_value(index, 9)) if isinstance(
sheet.cell_value(index, 9), float) else None,
float(sheet.cell_value(index, 10)) if isinstance(
sheet.cell_value(index, 10), float) else None,
float(sheet.cell_value(index, 11)) if isinstance(
sheet.cell_value(index, 11), float) else None,
sheet.cell_value(index, 12), sheet.cell_value(index, 13),
0.0 if isinstance(sheet.cell_value(index, 14),
str) else sheet.cell_value(index, 14),
0.0 if isinstance(sheet.cell_value(index, 15),
str) else sheet.cell_value(index, 15),
sheet.cell_value(index, 16) if sheet.ncols == 17 and
isinstance(sheet.cell_value(index, 16), float) else None))
index += 1
else:
index += 1
except IndexError:
is_readable = False
SQL_request.setRows(values)
SQL_request.executemany()
def insert_average_temperature(cas, global_dict):
if cas == 1:
QueryScript(f"DROP TABLE IF EXISTS average_temperature").execute(
admin=True)
average_temperature_table = QueryScript(
"CREATE TABLE average_temperature (id INT AUTO_INCREMENT PRIMARY KEY, measurepoint_id INT(11), sensor1_average DOUBLE, sensor1_min DOUBLE, sensor1_max DOUBLE, sensor2_average DOUBLE, sensor2_min DOUBLE, sensor2_max DOUBLE, sensor3_average DOUBLE, sensor3_min DOUBLE, sensor3_max DOUBLE, sensor2_average_labo DOUBLE, all_sensor_average DOUBLE, version INT );"
)
average_temperature_table.execute(admin=True)
if cas == 2:
need_update = global_dict['need_update'] if len(
global_dict['need_update']) else [0]
QueryScript(
f"DELETE FROM {env.DATABASE_TREATED}.average_temperature WHERE version = {env.CHOSEN_VERSION()} and measurepoint_id in {tuple(need_update) if len(need_update)>1 else '('+(str(need_update[0]) if len(need_update) else '0')+')'};"
).execute(admin=True)
insertion = QueryScript(
f" INSERT INTO average_temperature (measurepoint_id, sensor1_average, sensor1_min, sensor1_max, sensor2_average, sensor2_min, sensor2_max, sensor3_average, sensor3_min, sensor3_max, sensor2_average_labo, all_sensor_average, version) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
)
output = []
for measurepoint_id in global_dict["data"]:
if measurepoint_id != "None" and (
cas in [1, 3] or
('need_update' in global_dict
and measurepoint_id in global_dict["need_update"])):
row = [measurepoint_id]
if "sensor1" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["sensor1"]) /
len(global_dict["data"][measurepoint_id]["sensor1"]))
row.append(min(
global_dict["data"][measurepoint_id]["sensor1"]))
row.append(max(
global_dict["data"][measurepoint_id]["sensor1"]))
else:
row += [None] * 3
if "sensor2" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["sensor2"]) /
len(global_dict["data"][measurepoint_id]["sensor2"]))
row.append(min(
global_dict["data"][measurepoint_id]["sensor2"]))
row.append(max(
global_dict["data"][measurepoint_id]["sensor2"]))
else:
row += [None] * 3
if "sensor3" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["sensor3"]) /
len(global_dict["data"][measurepoint_id]["sensor3"]))
row.append(min(
global_dict["data"][measurepoint_id]["sensor3"]))
row.append(max(
global_dict["data"][measurepoint_id]["sensor3"]))
else:
row += [None] * 3
if "sensor2lab" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["sensor2lab"]) /
len(global_dict["data"][measurepoint_id]["sensor2lab"]))
else:
row.append(None)
if "all" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["all"]) /
len(global_dict["data"][measurepoint_id]["all"]))
else:
row.append(None)
output.append(tuple(row))
if len(output):
insertion.setRows(output)
insertion.executemany()
def fill_temperature_repro(cas, temperatures):
## Cas 1: Création et remplissage de la base de données
if cas == 1:
QueryScript(
f'DROP TABLE IF EXISTS {env.DATABASE_TREATED}.temperature_repro'
).execute(admin=True)
temperature_repro_table = QueryScript(
f"CREATE TABLE {env.DATABASE_TREATED}.temperature_repro (id INT AUTO_INCREMENT PRIMARY KEY, measurepoint_id INT(11), av_cycle_BCD1 DOUBLE, expected_C2 DOUBLE, expected_D1 DOUBLE, expected_D2 DOUBLE, av_cycle_1234 DOUBLE, expected_st3 DOUBLE, expected_st4 DOUBLE, expected_st5 DOUBLE, version INT );"
)
temperature_repro_table.execute(admin=True)
## Cas 2: Mise à jour de la dernière version connue
if cas == 2:
need_update = temperatures['need_update'] if len(
temperatures['need_update']) else [0]
QueryScript(
f"DELETE FROM {env.DATABASE_TREATED}.temperature_repro WHERE version = {env.CHOSEN_VERSION()} and measurepoint_id in {tuple(need_update) if len(need_update)>1 else '('+(str(need_update[0]) if len(need_update) else '0')+')'};"
).execute(admin=True)
SQL_request = QueryScript(
f" INSERT INTO {env.DATABASE_TREATED}.temperature_repro (measurepoint_id, av_cycle_BCD1, expected_C2, expected_D1, expected_D2, av_cycle_1234, expected_st3, expected_st4, expected_st5, version) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
)
values = []
liste_id = QueryScript(
f" SELECT measurepoint_id FROM {env.DATABASE_TREATED}.average_temperature WHERE version= {env.CHOSEN_VERSION()}"
).execute()
constantes = QueryScript(
f" SELECT name,value FROM {env.DATABASE_TREATED}.r2_constant WHERE nature='Temperature repro' and version= {env.CHOSEN_VERSION()}"
).execute()
constantes = list_to_dict(constantes)
constante_duree_femelle = int(
QueryScript(
f" SELECT value FROM {env.DATABASE_TREATED}.r2_constant WHERE name='FEMELLES'"
).execute()[0])
for elt_mp_id in liste_id:
if (cas == 2 and elt_mp_id
in temperatures['need_update']) or cas == 1 or cas == 3:
elt_insert = [elt_mp_id]
if "sensor2lab" in temperatures["data"][elt_mp_id]:
liste_tempe = temperatures["data"][elt_mp_id]["sensor2lab"]
else:
liste_tempe = []
if len(liste_tempe) > 0:
# Valeurs associées à %BCD1
av_cycle_BCD1 = calcul_av_cycle(
constante_duree_femelle, constantes['Constante %BCD1-1'],
constantes['Constante %BCD1-2'],
constantes['Constante %BCD1-3'],
constantes['Constante %BCD1-4'], liste_tempe)
expected_C2 = fct_aux_expected_percent(
constantes["Constante %attendu C2-1"],
constantes["Constante %attendu C2-2"], av_cycle_BCD1)
expected_D1 = fct_aux_expected_percent(
constantes["Constante %attendu D1-1"],
constantes["Constante %attendu D1-2"], av_cycle_BCD1)
expected_D2 = fct_aux_expected_percent(
constantes["Constante %attendu D2-1"],
constantes["Constante %attendu D2-2"], av_cycle_BCD1)
# Valeurs associées à %1234
av_cycle_1234 = calcul_av_cycle(
constante_duree_femelle, constantes['Constante %1234-1'],
constantes['Constante %1234-2'],
constantes['Constante %1234-3'],
constantes['Constante %1234-4'], liste_tempe)
expected_st3 = fct_aux_expected_percent(
constantes["Constante %attendu st3-1"],
constantes["Constante %attendu st3-2"], av_cycle_1234)
expected_st4 = fct_aux_expected_percent(
constantes["Constante %attendu st4-1"],
constantes["Constante %attendu st4-2"], av_cycle_1234)
expected_st5 = fct_aux_expected_percent(
constantes["Constante %attendu st5-1"],
constantes["Constante %attendu st5-2"], av_cycle_1234)
elt_insert += [
av_cycle_BCD1, expected_C2, expected_D1, expected_D2,
av_cycle_1234, expected_st3, expected_st4, expected_st5
]
values.append(tuple(elt_insert))
if len(values):
SQL_request.setRows(values)
SQL_request.executemany()