def update_script(self):
db = self.update_avail_db_combo.currentText() + ".db"
database_interaction = DatabaseInteraction(db)
path = self.update_path_field.text()
xml_importer = XMLImporter(database_interaction.name, path)
update_error = "Something went wrong during update"
try:
success = xml_importer.import_xml()
except Exception as ex:
success = False
update_error = ex
update_msg = QMessageBox()
if success:
update_msg.setIcon(QMessageBox.Information)
update_msg.setText("Sensor update successful")
update_msg.setInformativeText("The sensor: {0} has been updated.".format(db[:-3]))
update_msg.setWindowTitle("Sensor updated!")
update_msg.setDetailedText("The details are as follows:\Sensor name: {0} \nPath of .xml "
"files: {1}".format(db[:-3], path))
update_msg.setStandardButtons(QMessageBox.Ok)
else:
update_msg.setIcon(QMessageBox.Critical)
update_msg.setText("Sensor update failed!")
update_msg.setInformativeText("Update of the sensor: {0} failed!".format(db[:-3]))
update_msg.setWindowTitle("Sensor update failed!")
update_msg.setDetailedText("ERROR:\n {0}".format(update_error))
update_msg.setStandardButtons(QMessageBox.Abort)
update_retval = update_msg.exec_()
update_msg.show()
def run_script(self):
error = "Error parsing files or path"
db = self.db_field.text() + ".db"
db_interaction = DatabaseInteraction(db) # returns object of class DatabaseInteraction
path = self.path_field.text()
xml_importer = XMLImporter(db_interaction.name, path)
try:
success = xml_importer.import_xml()
except Exception as ex:
success = False
error = ex
msg = QMessageBox()
if success:
msg.setIcon(QMessageBox.Information)
msg.setText("Sensor declaration successful")
msg.setInformativeText("The sensor: {0} has been created.".format(self.db_field.text()))
msg.setWindowTitle("Sensor declared!")
msg.setDetailedText("The details are as follows:\nSensor name: {0} \nPath of .xml "
"files: {1}".format(self.db_field.text(), path))
msg.setStandardButtons(QMessageBox.Ok)
else:
msg.setIcon(QMessageBox.Critical)
msg.setText("Sensor declaration failed!")
msg.setInformativeText(
"Declaration of the sensor: {0} failed!".format(self.db_field.text()))
msg.setWindowTitle("Sensor declaration failed!")
msg.setDetailedText("ERROR:\n {0}".format(error))
msg.setStandardButtons(QMessageBox.Abort)
retval = msg.exec_()
msg.show()
def set_db_entry_button_script(self):
db_name_fixed = self.avail_db_combo.currentText() + ".db"
self.db = DatabaseInteraction(db_name_fixed)
extr = Extractor(self.db.name)
# Create datetime objects of the first and last strings of date
self.datetime_first = datetime.strptime(extr.select_first_row()[
0][0], "%Y-%m-%dT%H:%M:%S.%fZ")
self.datetime_last = datetime.strptime(extr.select_last_row()[
0][0], "%Y-%m-%dT%H:%M:%S.%fZ")
self.first_row = QDate.fromString(str(self.datetime_first.date()), "yyyy-MM-dd")
self.last_row = QDate.fromString(str(self.datetime_last.date()), "yyyy-MM-dd")
self.calendar.setMinimumDate(self.first_row)
self.calendar.setMaximumDate(self.last_row)
self.first_row = QDate.fromString(str(self.datetime_first.date()), "yyyy-MM-dd")
self.last_row = QDate.fromString(str(self.datetime_last.date()), "yyyy-MM-dd")
self.calendar.setMinimumDate(self.first_row)
self.calendar.setMaximumDate(self.last_row)
def import_xml(self):
dir_path = self.path
db = DatabaseInteraction(self.db.name)
db.create_table("sensor")
data = []
mode = 0
# Searches the current directory and every one below for every file that ends with .xml and parses it
for subdir, dirs, files in os.walk(dir_path):
for file in files:
filepath = subdir + os.path.sep + file
if filepath.endswith(".xml"):
prs = Parser(filepath)
if len(prs.fetch_values()[2]) == 28:
mode = 1
datetime_fixed = prs.fetch_values(
)[2][0:20] + prs.fetch_values()[2][21:28]
fetched_values = [
prs.fetch_values()[0],
prs.fetch_values()[1], datetime_fixed
]
data.append(fetched_values)
elif len(prs.fetch_values()[2]) == 27:
mode = 2
data.append(prs.fetch_values())
else:
pass
if mode == 1:
data.sort(key=lambda x: datetime.strptime(
x[2], "%Y-%m-%dT%H:%M:%S.0%fZ"))
elif mode == 2:
data.sort(
key=lambda x: datetime.strptime(x[2], "%Y-%m-%dT%H:%M:%S.%fZ"))
else:
print("ERROR : datetime format is not correct")
if len(data) == 0:
return False
for values in data:
db.insert_values("sensor", values)
return True
def __init__(self, db_name):
self.db = DatabaseInteraction(db_name)
class Extractor:
def __init__(self, db_name):
self.db = DatabaseInteraction(db_name)
def extract(self, mode):
if mode == "get_max_all":
self.get_max_all()
elif mode == "get_min_all":
self.get_min_all()
elif mode == "get_count_all":
self.get_count_all()
elif mode == "get_sum_all":
self.get_sum_all()
elif mode == "get_avg_all":
self.get_avg_all()
elif mode == "weekly_select":
weekly = []
weekly = self.weekly_select()
return weekly
elif mode == "monthly_select":
monthly = []
monthly = self.monthly_select()
return monthly
elif mode == "yearly_select":
yearly = []
yearly = self.yearly_select()
return yearly
elif mode == "daily_select":
daily = []
daily = self.daily_select()
return daily
else:
error = "Something went wrong"
return error
# Get maximum of values by unit
def get_max_all(self):
query = "SELECT MAX(VALUE) as MAX, UNIT FROM sensor GROUP BY UNIT"
max_all = self.db.select_values(query)
return max_all
# Get minimum of values by unit
def get_min_all(self):
query = "SELECT MIN(VALUE) as MIN, UNIT FROM sensor GROUP BY UNIT"
min_all = self.db.select_values(query)
return min_all
# Get min for the selected dates
def get_min(self, from_time, to_time):
query = "SELECT MIN(VALUE) as MIN FROM sensor WHERE TIME BETWEEN DATE(\"" + \
from_time + "\") AND DATE(\"" + \
to_time + "\", '+1 day')"
min = self.db.select_values(query)
return min
# Get max for the selected dates
def get_max(self, from_time, to_time):
query = "SELECT MAX(VALUE) as MAX FROM sensor WHERE TIME BETWEEN DATE(\"" + \
from_time + "\") AND DATE(\"" + \
to_time + "\", '+1 day')"
max = self.db.select_values(query)
return max
# Get avg for the selected dates
def get_avg(self, from_time, to_time):
query = "SELECT AVG(VALUE) as AVG_OF_VALUES FROM sensor WHERE TIME BETWEEN DATE(\"" + \
from_time + "\") AND DATE(\"" + \
to_time + "\", '+1 day')"
avg = self.db.select_values(query)
return avg
# Get count for the selected dates
def get_count(self, from_time, to_time):
query = "SELECT COUNT(*) as COUNT FROM sensor WHERE TIME BETWEEN DATE(\"" + \
from_time + "\") AND DATE(\"" + \
to_time + "\", '+1 day')"
count = self.db.select_values(query)
return count
# Get sum for the selected dates
def get_sum(self, from_time, to_time):
query = "SELECT SUM(VALUE) as SUM_OF_UNIT_VALUES FROM sensor WHERE TIME BETWEEN DATE(\"" + \
from_time + "\") AND DATE(\"" + \
to_time + "\", '+1 day')"
sum = self.db.select_values(query)
return sum
# Get average to max count for the selected dates
def get_avg_to_max_count(self, avg, max, from_time, to_time):
query = "SELECT COUNT(*) as COUNT FROM sensor WHERE VALUE BETWEEN \"" + \
avg + "\" AND \"" + \
max + "\" AND TIME BETWEEN DATE(\"" + \
from_time + "\") AND DATE(\"" + \
to_time + "\", '+1 day')"
avgToMax_count = self.db.select_values(query)
return avgToMax_count
# Get number of rows by unit
def get_count_all(self):
query = "SELECT COUNT(*) as COUNT, UNIT FROM sensor GROUP BY UNIT"
count_all = self.db.select_values(query)
return count_all
# Sum of all values by unit
def get_sum_all(self):
query = "SELECT SUM(VALUE) as SUM_OF_UNIT_VALUES, UNIT FROM sensor GROUP BY UNIT"
sum_all = self.db.select_values(query)
return sum_all
# Average of the values by unit
def get_avg_all(self):
# "SELECT AVG(VALUE) as AVG_OF_VALUES, UNIT FROM sensor GROUP BY UNIT"
query = "SELECT AVG(VALUE) as AVG_OF_VALUES, UNIT FROM sensor GROUP BY UNIT"
avg_all = self.db.select_values(query)
return avg_all
def select_first_row(self):
query = "SELECT TIME FROM sensor ORDER BY TIME LIMIT 1"
first_row = self.db.select_values(query)
return first_row
def select_last_row(self):
query = "SELECT TIME FROM sensor ORDER BY TIME DESC LIMIT 1"
last_row = self.db.select_values(query)
return last_row
def custom_select(self, from_time, to_time):
custom_query = "SELECT * FROM sensor WHERE TIME BETWEEN DATE(\"" + \
from_time + "\") AND DATE(\"" + \
to_time + "\", '+1 day')"
custom = self.db.select_values(custom_query)
custom_plot_x = []
custom_plot_y = []
custom_datetime_x = []
for i in range(len(custom)):
custom_plot_x.append(custom[i][2])
for i in range(len(custom)):
custom_plot_y.append(custom[i][1])
for i in range(len(custom_plot_x)):
custom_datetime_x.append(
datetime.strptime(custom_plot_x[i], "%Y-%m-%dT%H:%M:%S.%fZ"))
return custom_datetime_x, custom_plot_y, custom[0]
def weekly_select(self):
last_date_query = "SELECT TIME FROM sensor ORDER BY TIME DESC LIMIT 1"
last_date = self.db.select_values(last_date_query)
weekly_query = "SELECT * FROM sensor WHERE TIME >= DATETIME(\"" + \
last_date[0][0] + "\", '-7 days') AND TIME <= DATETIME(\"" + \
last_date[0][0] + "\", '+1 day')"
weekly = self.db.select_values(weekly_query)
weekly_plot_x = []
weekly_plot_y = []
weekly_plot = weekly
for index1 in range(len(weekly_plot)):
weekly_plot_x.append(weekly_plot[index1][2])
for index2 in range(len(weekly_plot)):
weekly_plot_y.append(int(weekly_plot[index2][1]))
weekly_datetime_x = []
for i in range(len(weekly_plot_x)):
weekly_datetime_x.append(
datetime.strptime(weekly_plot_x[i], "%Y-%m-%dT%H:%M:%S.%fZ"))
return weekly_datetime_x, weekly_plot_y, weekly_plot[0]
def monthly_select(self):
query = "SELECT TIME FROM sensor ORDER BY TIME DESC LIMIT 1"
last_date = self.db.select_values(query)
monthly_query = "SELECT * FROM sensor WHERE TIME >= DATETIME(\"" + \
last_date[0][0] + "\", 'start of month') AND TIME <= DATETIME(\"" +\
last_date[0][0] + "\",'+1 day')"
monthly = self.db.select_values(monthly_query)
# Here we create the 2 lists that are going to be plotly's arguments
# monthly
monthly_plot_x = []
monthly_plot_y = []
monthly_plot = monthly
for i in range(len(monthly_plot)):
monthly_plot_x.append(monthly_plot[i][2])
for i in range(len(monthly_plot)):
monthly_plot_y.append(int(monthly_plot[i][1]))
monthly_datetime_x = []
for i in range(len(monthly_plot_x)):
monthly_datetime_x.append(
datetime.strptime(monthly_plot_x[i], "%Y-%m-%dT%H:%M:%S.%fZ"))
return monthly_datetime_x, monthly_plot_y, monthly[0]
def yearly_select(self):
query = "SELECT TIME FROM sensor ORDER BY TIME DESC LIMIT 1"
last_date = self.db.select_values(query)
yearly_query = "SELECT * FROM sensor WHERE TIME BETWEEN DATETIME(\"" + \
last_date[0][0] + "\", 'start of year') AND DATETIME(\"" +\
last_date[0][0] + "\", '+1 day')"
yearly = self.db.select_values(yearly_query)
# yearly
yearly_plot_x = []
yearly_plot_y = []
yearly_plot = yearly
for i in range(len(yearly_plot)):
yearly_plot_x.append(yearly_plot[i][2])
for i in range(len(yearly_plot)):
yearly_plot_y.append(int(yearly_plot[i][1]))
yearly_datetime_x = []
for i in range(len(yearly_plot_x)):
yearly_datetime_x.append(
datetime.strptime(yearly_plot_x[i], "%Y-%m-%dT%H:%M:%S.%fZ"))
return yearly_datetime_x, yearly_plot_y, yearly[0]
def daily_select(self):
query = "SELECT TIME FROM sensor ORDER BY TIME DESC LIMIT 1"
last_date = self.db.select_values(query)
daily_query = "SELECT * FROM sensor WHERE TIME BETWEEN DATETIME(\"" + \
last_date[0][0] + "\") AND DATETIME(\"" +\
last_date[0][0] + "\", '+1 day')"
daily = self.db.select_values(daily_query)
# Here we create the 2 lists that are going to be plotly's arguments
# daily
daily_plot_x = []
daily_plot_y = []
daily_plot = daily
for i in range(len(daily_plot)):
daily_plot_x.append(daily_plot[i][2])
for i in range(len(daily_plot)):
daily_plot_y.append(int(daily_plot[i][1]))
daily_datetime_x = []
for i in range(len(daily_plot_x)):
daily_datetime_x.append(
datetime.strptime(daily_plot_x[i], "%Y-%m-%dT%H:%M:%S.%fZ"))
return daily_datetime_x, daily_plot_y, daily[0]
def __init__(self, db_name, path):
self.path = path
self.db = DatabaseInteraction(db_name)