306 lines
17 KiB
Python
Executable File
306 lines
17 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
import sys
|
|
import os
|
|
from mysql.connector import MySQLConnection, Error
|
|
from dbconfig import read_db_config
|
|
from decimal import Decimal
|
|
from datetime import datetime
|
|
|
|
def insertData(dati):
|
|
#print(dati)
|
|
#print(len(dati))
|
|
if(len(dati) > 0):
|
|
db_config = read_db_config()
|
|
conn = MySQLConnection(**db_config)
|
|
cursor = conn.cursor()
|
|
if(len(dati) == 2):
|
|
u = ""
|
|
t = ""
|
|
rawdata = dati[0]
|
|
elabdata = dati[1]
|
|
if(len(rawdata) > 0):
|
|
for r in rawdata:
|
|
#print(r)
|
|
#print(len(r))
|
|
if(len(r) == 6):#nodo1
|
|
unitname = r[0]
|
|
toolname = r[1]
|
|
nodenum = r[2]
|
|
pressure = Decimal(r[3])*100
|
|
date = r[4]
|
|
time = r[5]
|
|
query = "SELECT * from RAWDATACOR WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s ORDER BY EventDate desc,EventTime desc limit 1"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum])
|
|
result = cursor.fetchall()
|
|
if(result):
|
|
if(result[0][8] is None):
|
|
datetimeOld = datetime.strptime(str(result[0][4]) + " " + str(result[0][5]), "%Y-%m-%d %H:%M:%S")
|
|
datetimeNew = datetime.strptime(str(date) + " " + str(time), "%Y-%m-%d %H:%M:%S")
|
|
dateDiff = datetimeNew - datetimeOld
|
|
if(dateDiff.total_seconds() / 3600 >= 5):
|
|
query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, pressure, -1, -273])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
else:
|
|
query = "UPDATE RAWDATACOR SET val0=%s, EventDate=%s, EventTime=%s WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s AND val0 is NULL ORDER BY EventDate desc,EventTime desc limit 1"
|
|
try:
|
|
cursor.execute(query, [pressure, date, time, unitname, toolname, nodenum])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
elif(result[0][8] is not None):
|
|
query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, pressure, -1, -273])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
else:
|
|
query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, pressure, -1, -273])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
except Error as e:
|
|
print('Error:', e)
|
|
else:#altri 2->5
|
|
unitname = r[0]
|
|
toolname = r[1]
|
|
nodenum = r[2]
|
|
freqinhz = r[3]
|
|
therminohms = r[4]
|
|
freqindigit = r[5]
|
|
date = r[6]
|
|
time = r[7]
|
|
query = "SELECT * from RAWDATACOR WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s ORDER BY EventDate desc,EventTime desc limit 1"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum])
|
|
result = cursor.fetchall()
|
|
if(result):
|
|
if(result[0][8] is None):
|
|
query = "UPDATE RAWDATACOR SET val0=%s, val1=%s, val2=%s, EventDate=%s, EventTime=%s WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s AND val0 is NULL ORDER BY EventDate desc,EventTime desc limit 1"
|
|
try:
|
|
cursor.execute(query, [freqinhz, therminohms, freqindigit, date, time, unitname, toolname, nodenum])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
elif(result[0][8] is not None):
|
|
query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, val1, val2, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, freqinhz, therminohms, freqindigit, -1, -273])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
else:
|
|
query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, val1, val2, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, freqinhz, therminohms, freqindigit, -1, -273])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
except Error as e:
|
|
print('Error:', e)
|
|
|
|
if(len(elabdata) > 0):
|
|
for e in elabdata:
|
|
#print(e)
|
|
#print(len(e))
|
|
if(len(e) == 6):#nodo1
|
|
unitname = e[0]
|
|
toolname = e[1]
|
|
nodenum = e[2]
|
|
pressure = Decimal(e[3])*100
|
|
date = e[4]
|
|
time = e[5]
|
|
try:
|
|
query = "INSERT INTO ELABDATADISP(UnitName, ToolNameID, NodeNum, EventDate, EventTime, pressure) VALUES(%s,%s,%s,%s,%s,%s)"
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, pressure])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
else:#altri 2->5
|
|
unitname = e[0]
|
|
toolname = e[1]
|
|
u = unitname
|
|
t = toolname
|
|
nodenum = e[2]
|
|
pch = e[3]
|
|
tch = e[4]
|
|
date = e[5]
|
|
time = e[6]
|
|
try:
|
|
query = "INSERT INTO ELABDATADISP(UnitName, ToolNameID, NodeNum, EventDate, EventTime, XShift, T_node) VALUES(%s,%s,%s,%s,%s,%s,%s)"
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, pch, tch])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
#os.system("cd /usr/local/matlab_func/; ./run_ATD_lnx.sh /usr/local/MATLAB/MATLAB_Runtime/v93/ "+u+" "+t+"")
|
|
else:
|
|
for r in dati:
|
|
#print(r)
|
|
unitname = r[0]
|
|
toolname = r[1]
|
|
nodenum = r[2]
|
|
date = r[3]
|
|
time = r[4]
|
|
battery = r[5]
|
|
temperature = r[6]
|
|
query = "SELECT * from RAWDATACOR WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s ORDER BY EventDate desc,EventTime desc limit 1"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum])
|
|
result = cursor.fetchall()
|
|
if(result):
|
|
if(result[0][25] is None or result[0][25] == -1.00):
|
|
datetimeOld = datetime.strptime(str(result[0][4]) + " " + str(result[0][5]), "%Y-%m-%d %H:%M:%S")
|
|
datetimeNew = datetime.strptime(str(date) + " " + str(time), "%Y-%m-%d %H:%M:%S")
|
|
dateDiff = datetimeNew - datetimeOld
|
|
#print(dateDiff.total_seconds() / 3600)
|
|
if(dateDiff.total_seconds() / 3600 >= 5):
|
|
query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, battery, temperature])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
else:
|
|
query = "UPDATE RAWDATACOR SET BatLevelModule=%s, TemperatureModule=%s WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s AND (BatLevelModule is NULL or BatLevelModule = -1.00) ORDER BY EventDate desc,EventTime desc limit 1"
|
|
try:
|
|
cursor.execute(query, [battery, temperature, unitname, toolname, nodenum])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
elif(result[0][25] is not None and result[0][25] != -1.00):
|
|
query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, battery, temperature])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
else:
|
|
query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
try:
|
|
cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, battery, temperature])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
except Error as e:
|
|
print('Error:', e)
|
|
cursor.close()
|
|
conn.close()
|
|
|
|
def getDataFromCsv(pathFile):
|
|
with open(pathFile, 'r') as file:
|
|
data = file.readlines()
|
|
data = [row.rstrip() for row in data]
|
|
serial_number = data[0].split(",")[1]
|
|
data = data[10:] #rimuove righe header
|
|
dati = []
|
|
rawDatiReadings = []#tmp
|
|
elabDatiReadings = []#tmp
|
|
datiReadings = []
|
|
i = 0
|
|
unit = ""
|
|
tool = ""
|
|
#row = data[0]#quando non c'era il for solo 1 riga
|
|
for row in data:#se ci sono righe multiple
|
|
row = row.split(",")
|
|
if i == 0:
|
|
query = "SELECT unit_name, tool_name FROM sisgeo_tools WHERE serial_number='"+serial_number+"'"
|
|
try:
|
|
db_config = read_db_config()
|
|
conn = MySQLConnection(**db_config)
|
|
cursor = conn.cursor()
|
|
cursor.execute(query)
|
|
result = cursor.fetchall()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
unit = result[0][0]
|
|
tool = result[0][1]
|
|
#print(result[0][0])
|
|
#print(result[0][1])
|
|
if("health" in pathFile):
|
|
datetime = str(row[0]).replace("\"", "").split(" ")
|
|
date = datetime[0]
|
|
time = datetime[1]
|
|
battery = row[1]
|
|
temperature = row[2]
|
|
dati.append((unit, tool, 1, date, time, battery, temperature))
|
|
dati.append((unit, tool, 2, date, time, battery, temperature))
|
|
dati.append((unit, tool, 3, date, time, battery, temperature))
|
|
dati.append((unit, tool, 4, date, time, battery, temperature))
|
|
dati.append((unit, tool, 5, date, time, battery, temperature))
|
|
else:
|
|
datetime = str(row[0]).replace("\"", "").split(" ")
|
|
date = datetime[0]
|
|
time = datetime[1]
|
|
atmpressure = row[1]#nodo1
|
|
#raw
|
|
freqinhzch1 = row[2]#nodo2
|
|
freqindigitch1 = row[3]#nodo2
|
|
thermResInOhmsch1 = row[4]#nodo2
|
|
freqinhzch2 = row[5]#nodo3
|
|
freqindigitch2 = row[6]#nodo3
|
|
thermResInOhmsch2 = row[7]#nodo3
|
|
freqinhzch3 = row[8]#nodo4
|
|
freqindigitch3 = row[9]#nodo4
|
|
thermResInOhmsch3 = row[10]#nodo4
|
|
freqinhzch4 = row[11]#nodo5
|
|
freqindigitch4 = row[12]#nodo5
|
|
thermResInOhmsch4 = row[13]#nodo5
|
|
#elab
|
|
pch1 = row[18]#nodo2
|
|
tch1 = row[19]#nodo2
|
|
pch2 = row[20]#nodo3
|
|
tch2 = row[21]#nodo3
|
|
pch3 = row[22]#nodo4
|
|
tch3 = row[23]#nodo4
|
|
pch4 = row[24]#nodo5
|
|
tch4 = row[25]#nodo5
|
|
|
|
rawDatiReadings.append((unit, tool, 1, atmpressure, date, time))
|
|
rawDatiReadings.append((unit, tool, 2, freqinhzch1, thermResInOhmsch1, freqindigitch1, date, time))
|
|
rawDatiReadings.append((unit, tool, 3, freqinhzch2, thermResInOhmsch2, freqindigitch2, date, time))
|
|
rawDatiReadings.append((unit, tool, 4, freqinhzch3, thermResInOhmsch3, freqindigitch3, date, time))
|
|
rawDatiReadings.append((unit, tool, 5, freqinhzch4, thermResInOhmsch4, freqindigitch4, date, time))
|
|
|
|
elabDatiReadings.append((unit, tool, 1, atmpressure, date, time))
|
|
elabDatiReadings.append((unit, tool, 2, pch1, tch1, date, time))
|
|
elabDatiReadings.append((unit, tool, 3, pch2, tch2, date, time))
|
|
elabDatiReadings.append((unit, tool, 4, pch3, tch3, date, time))
|
|
elabDatiReadings.append((unit, tool, 5, pch4, tch4, date, time))
|
|
|
|
#[ram],[elab]#quando c'era solo 1 riga
|
|
#dati = [
|
|
# [
|
|
# (unit, tool, 1, atmpressure, date, time),
|
|
# (unit, tool, 2, freqinhzch1, thermResInOhmsch1, freqindigitch1, date, time),
|
|
# (unit, tool, 3, freqinhzch2, thermResInOhmsch2, freqindigitch2, date, time),
|
|
# (unit, tool, 4, freqinhzch3, thermResInOhmsch3, freqindigitch3, date, time),
|
|
# (unit, tool, 5, freqinhzch4, thermResInOhmsch4, freqindigitch4, date, time),
|
|
# ], [
|
|
# (unit, tool, 1, atmpressure, date, time),
|
|
# (unit, tool, 2, pch1, tch1, date, time),
|
|
# (unit, tool, 3, pch2, tch2, date, time),
|
|
# (unit, tool, 4, pch3, tch3, date, time),
|
|
# (unit, tool, 5, pch4, tch4, date, time),
|
|
# ]
|
|
# ]
|
|
i+=1
|
|
#print(dati)
|
|
if(len(rawDatiReadings) > 0 or len(elabDatiReadings) > 0):
|
|
datiReadings = [rawDatiReadings, elabDatiReadings]
|
|
if(len(datiReadings) > 0):
|
|
return datiReadings
|
|
return dati
|
|
|
|
def main():
|
|
insertData(getDataFromCsv(sys.argv[1]))
|
|
|
|
if __name__ == '__main__':
|
|
main()
|