From 1d26be43535c34b05513c92e700d5cc92610fa63 Mon Sep 17 00:00:00 2001 From: Louie S Date: Mon, 18 Sep 2023 13:46:56 -0400 Subject: Functional rules dialog and backend --- src/db_sqlite.py | 145 ++++++++++++++++++++++++++++++++++++++++++++++++++++ src/globals.py | 3 +- src/main.py | 16 +++++- src/rule.py | 40 +++++++++++++++ src/rules_dialog.py | 128 ++++++++++++++++++++++++++++++++++++++++++++++ 5 files changed, 329 insertions(+), 3 deletions(-) create mode 100644 src/rule.py create mode 100644 src/rules_dialog.py diff --git a/src/db_sqlite.py b/src/db_sqlite.py index fd06b5a..00f4edb 100644 --- a/src/db_sqlite.py +++ b/src/db_sqlite.py @@ -6,6 +6,7 @@ from PyQt5.QtSql import QSqlDatabase, QSqlQuery import src.globals as Globals from src.group import Group from src.entry import Entry +from src.rule import Rule def initDB(): """ @@ -40,6 +41,7 @@ def createTables(): # Erase database contents so that we don't have duplicates query.exec_("DROP TABLE groups") query.exec_("DROP TABLE entries") + query.exec_("DROP TABLE rules") query.exec_(""" CREATE TABLE groups ( @@ -65,6 +67,19 @@ def createTables(): hidden TINYINT(1) DEFAULT FALSE ) """) + + query.exec_(""" + CREATE TABLE rules ( + id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, + entry_id REFERENCES entries (id), + before_after TINYINT(1) DEFAULT TRUE, + date TEXT NOT NULL, + color VARCHAR(255) DEFAULT NULL, + highlight VARCHAR(255) DEFAULT NULL + ) + """) + + print(database.lastError().text()) database.close() @@ -118,6 +133,22 @@ def loadFromTables(): record.field("done").value(), record.field("hidden").value())) + # Load rules + Globals.rules = [] # Reset local rules array + query.exec_("SELECT * FROM rules") + while query.next(): + record = query.record() + date_struct = strptime(record.field("date").value(), "%Y-%m-%d") + date = QDate(date_struct.tm_year, date_struct.tm_mon, date_struct.tm_mday) + Globals.rules.append( + Rule( + record.field("id").value(), + record.field("entry_id").value(), + "before" if record.field("before_after").value() == 0 else "after", + date, + record.field("color").value(), + record.field("highlight").value())) + database.close() def insertGroup(new_group): @@ -195,6 +226,47 @@ def insertEntry(new_entry): return output +def insertRule(new_rule): + """ + Insert rule to the database at Globals.db_path + """ + output = -1 + + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + query.prepare(""" + INSERT INTO rules (entry_id, before_after, date, color, highlight) VALUES (:e_id, :when, :date, :color, :highlight) + """) + query.bindValue(":e_id", new_rule.entry_id) + query.bindValue(":when", 0 if new_rule.when.lower() == "before" else 1) + query.bindValue(":date", "{0}-{1}-{2}".format( + new_rule.date.year(), + new_rule.date.month(), + new_rule.date.day())) + query.bindValue(":color", new_rule.color) + query.bindValue(":highlight", new_rule.highlight) + success = query.exec_() + # DEBUG + #print(query.lastError().text()) + #print(query.boundValues()) + #if success: + # print("Query succeeded") + #else: + # print("Query failed") + + output = query.lastInsertId() + + database.close() + + return output + def updateGroup(group): """ Update group by its id @@ -264,6 +336,46 @@ def updateEntry(entry): database.close() +def updateRule(rule): + """ + Update rule by its id + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + query.prepare(""" + UPDATE rules SET + before_after = :when, + date = :date, + color = :color, + highlight = :highlight + WHERE id = :id + """) + query.bindValue(":when", 0 if rule.when.lower() == "before" else 1) + query.bindValue(":date", "{0}-{1}-{2}".format( + rule.date.year(), + rule.date.month(), + rule.date.day())) + query.bindValue(":color", rule.color) + query.bindValue(":highlight", rule.highlight) + query.bindValue(":id", rule.id) + success = query.exec_() + # DEBUG + #print(query.lastError().text()) + #print(query.boundValues()) + #if success: + # print("Query succeeded") + #else: + # print("Query failed") + + database.close() + def removeGroup(group_id): """ Remove a group by id from the database @@ -322,6 +434,31 @@ def removeEntry(entry_id): database.close() return output +def removeRule(rule_id): + """ + Remove a rule by id from the database + (we do not preserve rules, unlike groups and entries) + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + # Set entry to hidden + query.prepare(""" + DELETE FROM rules WHERE id = ? + """) + query.addBindValue(rule_id) + query.exec_() + + output = query.numRowsAffected() + database.close() + return output + def cleanHidden(): """ Permanently delete removed/hidden groups and entries @@ -335,6 +472,14 @@ def cleanHidden(): query = QSqlQuery() + # Remove rules associated with hidden entries + query.exec_(""" + DELETE FROM rules WHERE id IN ( + SELECT rules.id FROM rules + INNER JOIN entries ON rules.entry_id = entries.id + WHERE entries.hidden = 1 + )""") + # Remove hidden entries query.exec_(""" DELETE FROM entries WHERE hidden = 1 diff --git a/src/globals.py b/src/globals.py index 61c6156..bb6f717 100644 --- a/src/globals.py +++ b/src/globals.py @@ -1,3 +1,4 @@ groups = [] entries = [] -db_path = "./test.db" +rules = [] +db_path = "" diff --git a/src/main.py b/src/main.py index 1b82204..008e310 100644 --- a/src/main.py +++ b/src/main.py @@ -12,6 +12,7 @@ from src.add_entry_form import addEntryForm from src.edit_entry_form import editEntryForm import src.globals as Globals import src.db_sqlite as DB +from src.rules_dialog import RulesDialog class AssignmentList(QMainWindow): def __init__(self): @@ -39,7 +40,6 @@ class AssignmentList(QMainWindow): self.preferences_act.setShortcut("Alt+Return") self.preferences_act.triggered.connect(self.preferences) file_menu.addAction(self.preferences_act) - # TODO implement reload of DB that works self.reload_act = QAction("Reload", self) self.reload_act.setShortcut("F5") self.reload_act.triggered.connect(self.reload) @@ -184,6 +184,12 @@ class AssignmentList(QMainWindow): Globals.entries = list(filter(lambda e: e.id != id, Globals.entries)) self.drawGroups() + def editRules(self, id): + pass + need_redraw = RulesDialog(id) + if need_redraw: + self.drawGroups() + def entryContextMenu(self, entry_id): menu = QMenu() @@ -191,6 +197,10 @@ class AssignmentList(QMainWindow): edit_entry_act.triggered.connect((lambda id: lambda: self.editEntry(id))(entry_id)) menu.addAction(edit_entry_act) + rules_act = QAction("Rules") + rules_act.triggered.connect((lambda id: lambda: self.editRules(id))(entry_id)) + menu.addAction(rules_act) + mark_done_act = QAction("Done", checkable=True) if list(filter(lambda e: e.id == entry_id, Globals.entries))[0].done: mark_done_act.setChecked(True) @@ -206,7 +216,6 @@ class AssignmentList(QMainWindow): def preferences(self): # TODO not sure if this is working exactly how I think it does, but it works need_reload = PreferencesDialog() - print(need_reload) if need_reload: self.reload() @@ -238,6 +247,9 @@ class AssignmentList(QMainWindow): # Sort the entries Globals.entries = sorted(Globals.entries, key=lambda e: (e.parent_id, (e.due if e.due else QDate.currentDate()), e.done, e.id)) + # Sort the rules + Globals.rules = sorted(Globals.rules, key=lambda r: (r.id)) + # Create columns as vertical boxes column_left = QVBoxLayout() column_right = QVBoxLayout() diff --git a/src/rule.py b/src/rule.py new file mode 100644 index 0000000..6ecf7d7 --- /dev/null +++ b/src/rule.py @@ -0,0 +1,40 @@ + +from PyQt5.QtCore import QDate +from PyQt5.QtWidgets import QComboBox, QDateTimeEdit, QHBoxLayout, QLineEdit + + +class Rule: + def __init__(self, id, entry_id, when, date, color = "", highlight = ""): + self.id = id + self.entry_id = entry_id + self.when = when + self.date = date + self.color = color + self.highlight = highlight + + def buildLayout(self): + output = QHBoxLayout() + + when_widget = QComboBox() + when_widget.addItems(["Before", "After"]) + when_widget.setCurrentIndex(0 if self.when.lower() == "before" else 1) + output.addWidget(when_widget) + + date_widget = QDateTimeEdit(QDate.currentDate()) + date_widget.setDisplayFormat("MM/dd/yyyy") + date_widget.setDate(self.date) + output.addWidget(date_widget) + + output.addStretch() + + # TODO Consider making this a color selector widget + color_widget = QLineEdit() + color_widget.setPlaceholderText("Color") + output.addWidget(color_widget) + + # TODO Consider making this a color selector widget + highlight_widget = QLineEdit() + highlight_widget.setPlaceholderText("Highlight") + output.addWidget(highlight_widget) + + return output diff --git a/src/rules_dialog.py b/src/rules_dialog.py new file mode 100644 index 0000000..0980fdd --- /dev/null +++ b/src/rules_dialog.py @@ -0,0 +1,128 @@ +import sys +from PyQt5.QtCore import QDate +from PyQt5.QtWidgets import QApplication, QDialog, QHBoxLayout, QPushButton, QScrollArea, QVBoxLayout +from src.config import Config +from src.rule import Rule +import src.db_sqlite as DB +import src.globals as Globals + +class RulesDialog(QDialog): + """ + Show the list of rules associated with an entry + """ + def __init__(self, entry_id): + super().__init__() + + self.entry_id = entry_id + # class globals + self.config = Config() + self.relevant_rules = self.getRelevantRules() + + self.initializeUI() + + def initializeUI(self): + self.resize(500, 320) + self.setWindowTitle("Rules") + self.displayWidgets() + self.exec() + + def displayWidgets(self): + main_layout = QVBoxLayout() + main_layout_scroll_area = QScrollArea() + main_layout_scroll_area.setWidgetResizable(True) + main_layout_scroll_area.setLayout(main_layout) + + self.rules_layout = QVBoxLayout() + self.drawRules() + main_layout.addLayout(self.rules_layout) + + main_layout.addStretch() + # Create Close and Save buttons + buttons_hbox = QHBoxLayout() + buttons_hbox.addStretch() + + close_button = QPushButton("Close", self) + close_button.clicked.connect(self.close) + buttons_hbox.addWidget(close_button) + + save_button = QPushButton("Save", self) + save_button.clicked.connect(self.save) + buttons_hbox.addWidget(save_button) + + main_layout.addLayout(buttons_hbox) + self.setLayout(main_layout) + + def drawRules(self): + # Remove all children from layout + def recursiveClear(layout): + while layout.count(): + child = layout.takeAt(0) + if child.widget(): + child.widget().deleteLater() + elif child.layout(): + recursiveClear(child) + + recursiveClear(self.rules_layout) + + # Draw each rule + self.r_layouts_dict = {} # Use to help update things in the save() function + for r in self.relevant_rules: + r_layout = r.buildLayout() + self.r_layouts_dict[r.id] = r_layout + del_button = QPushButton("Delete", self) + del_button.clicked.connect((lambda id: lambda: self.deleteRule(id))(r.id)) + r_layout.addWidget(del_button) + self.rules_layout.addLayout(r_layout) + + # Draw a button to add rules + rules_buttons_hbox = QHBoxLayout() + add_rule_button = QPushButton("Add Rule", self) + add_rule_button.clicked.connect(self.addRule) + rules_buttons_hbox.addWidget(add_rule_button) + rules_buttons_hbox.addStretch() + self.rules_layout.addLayout(rules_buttons_hbox) + + def addRule(self): + self.apply() + + new_rule = Rule(0, self.entry_id, "before", QDate.currentDate()) + new_rule_id = DB.insertRule(new_rule) + new_rule.id = new_rule_id + Globals.rules.append(new_rule) + self.relevant_rules = self.getRelevantRules() + self.drawRules() + + def deleteRule(self, rule_id): + DB.removeRule(rule_id) + Globals.rules = list(filter(lambda r: r.id != rule_id, Globals.rules)) + self.relevant_rules = self.getRelevantRules() + self.drawRules() + + def getRelevantRules(self): + return list(filter(lambda r: r.entry_id == self.entry_id, Globals.rules)) + + def apply(self): + for id, layout in self.r_layouts_dict.items(): + updated_rule = Rule( + id, + self.entry_id, + layout.itemAt(0).widget().currentText(), + layout.itemAt(1).widget().date(), + layout.itemAt(3).widget().text(), + layout.itemAt(4).widget().text()) + DB.updateRule(updated_rule) + Globals.rules = list(filter(lambda r: r.id != id, Globals.rules)) + Globals.rules.append(updated_rule) + + def save(self): + """ + Save any existing rules. Added rules are automatically saved, + but changing rules is not, hence the need for a manual save + """ + self.apply() + self.done(1) + +if __name__ == "__main__": + app = QApplication(sys.argv) + window = RulesDialog() + sys.exit(app.exec_()) -- cgit