def new_room_dialog(self, db):
ui = Ui_Room()
new_rm = QDialog()
ui.setupUi(new_rm)
model = QSqlTableModel(new_rm, db)
#Threading
thrd = QThreadPool().globalInstance()
hlist = ['Reserv. ID','Customer ID','Room #','From','To','Discount','Extension','Net Total']
worker = TableWorker(update_table("CurrentReservation", hlist, ui.tableView, db, model, where=f"RmNumber={ui.lineEdit.text()}")) #We pass a function for the worker to execute
thrd.tryStart(worker)
#Setup Signals and other UI elements
ui.lineEdit.setFocus()
# TODO find a better signal than textChanged because it sucks bad
ui.lineEdit.textChanged.connect(lambda: update_table_onEnter(new_rm, hlist, ui, db, thrd, model))
ui.pushButton_3.clicked.connect(lambda: thrd.tryStart(TableWorker(add_DB(ui, new_rm, db,
"Room",
[ui.lineEdit.text(),ui.comboBox.currentText(),ui.spinBox.value(),0],
"?, ?, ?, ?",
[ui.lineEdit,ui.spinBox]))))
ui.pushButton_2.clicked.connect(lambda: thrd.tryStart(TableWorker(edit_DB(ui, new_rm, db,
"Room",
[ui.lineEdit.text(),ui.comboBox.currentText(),ui.spinBox.value(),ui.lineEdit.text()],
"Number = ?, Type = ?, Price = ?",
"Number",
[ui.lineEdit,ui.spinBox]))))
ui.pushButton.clicked.connect(lambda: thrd.tryStart(TableWorker(del_DB(ui, new_rm, db,
"Room", "Number = ?", [ui.lineEdit.text()],
[ui.lineEdit,ui.spinBox]))))
new_rm.setWindowTitle('Create, edit, or delete a Room')
new_rm.exec()
def new_cancel_dialog(self, window, db, res_index, thrd, model, hlist, widget):
new_cancel = QMessageBox(window)
new_cancel.setWindowTitle('Cancel Reservation')
new_cancel.setText('Are you sure you want to cancel the select Reservation')
new_cancel.setStandardButtons(QMessageBox.Yes | QMessageBox.No)
#TODO Add a boolean column for cancelation
if new_cancel.exec_() == QMessageBox.Yes:
db.open()
query = QSqlQuery(db)
db.transaction()
query.prepare('INSERT INTO CancelledReservation VALUES(?, ?, ?)')
query.addBindValue(res_index.siblingAtColumn(0).data())
query.addBindValue(res_index.siblingAtColumn(1).data())
query.addBindValue(res_index.siblingAtColumn(2).data())
query.exec_()
query.prepare('DELETE FROM CurrentReservation WHERE ResID = ?')
query.addBindValue(res_index.siblingAtColumn(0).data())
query.exec_()
db.commit()
db.close()
worker = TableWorker(update_table("CurrentReservation", hlist, widget, db, model))
thrd.tryStart(worker)
else:
new_cancel.close()
def new_customer_dialog(self, db):
ui = Ui_Customer()
new_cust = QDialog()
ui.setupUi(new_cust)
model = QSqlTableModel(new_cust, db)
#Setup Threading
thrd = QThreadPool().globalInstance()
hlist = ['Customer ID','Name','Phone #','Date of Birth','# Reservations']
worker = TableWorker(update_table("Customer", hlist, ui.tableView, db, model)) #We pass a function for the worker to execute
thrd.tryStart(worker)
#? Consider using, instead of QSqlQuery, A QSqlTableModel and insert or delete from it
ui.lineEdit_2.textEdited.connect(lambda: update_custTable_onEnter(new_cust, hlist, ui, db, thrd, model))
ui.pushButton_3.clicked.connect(lambda: thrd.tryStart(TableWorker(add_DB(ui, new_cust, db,
"Customer",
[ui.lineEdit_2.text(),ui.lineEdit.text(),ui.spinBox.value(),ui.dateEdit.date().toString("yyyy-MM-dd"),ui.comboBox.currentText(),0],
"?,?,?,?,?,?",
[ui.lineEdit_2,ui.lineEdit,ui.spinBox,ui.dateEdit]))))
ui.pushButton_2.clicked.connect(lambda: thrd.tryStart(TableWorker(edit_DB(ui, new_cust, db,
"Customer",
[ui.lineEdit_2.text(),ui.lineEdit.text(),ui.spinBox.value(),ui.dateEdit.date().toString("yyyy-MM-dd"),ui.comboBox.currentText(),ui.lineEdit_2.text()],
"ID = ?, Name = ?, Phone = ?, DoB = ?, Sex = ?",
"ID",
[ui.lineEdit_2,ui.lineEdit,ui.spinBox,ui.dateEdit]))))
ui.pushButton.clicked.connect(lambda: thrd.tryStart(TableWorker(del_DB(ui, new_cust, db,
"Customer", "ID = ?", [ui.lineEdit_2.text()],
[ui.lineEdit_2,ui.lineEdit,ui.spinBox,ui.dateEdit]))))
ui.lineEdit_2.setText("CTMR" + str(randrange(100, 999, 10)))
new_cust.setWindowTitle('Create, edit, or delete a Customer')
new_cust.exec()
def update_table_onEnter(window, hlist, ui, db, thrd, model):
thrd.tryStart(
TableWorker(
update_table("CurrentReservation",
hlist,
ui.tableView,
db,
model,
where=f"RmNumber={ui.lineEdit.text()}")))
db.open()
if ui.lineEdit.text() != '':
query = QSqlQuery(db)
query.setForwardOnly(True)
query.prepare('SELECT Type, Price FROM Room WHERE Number = ?')
query.addBindValue(ui.lineEdit.text())
query.exec_()
try:
query.next()
ui.comboBox.setCurrentIndex(ui.comboBox.findText(query.value(0)))
ui.spinBox.setValue(query.value(1))
db.close()
except Exception:
QtWidgets.QMessageBox.warning(
window, 'Warning',
f"Room with Number: {ui.lineEdit.text()} was not found",
QtWidgets.QMessageBox.NoButton)
if ui.comboBox.currentText() != '':
ui.lineEdit.clear()
ui.spinBox.setValue(0)
def new_reservation(ui, window, db, discount, thrd, mainui, model):
db.open()
query = QSqlQuery(db)
#Retrieve room price from DB
price_query = QSqlQuery(db)
price_query.prepare('SELECT Price FROM Room WHERE Number = ?')
price_query.addBindValue(ui.comboBox_2.currentText())
price_query.exec_()
price_query.next()
price = price_query.value(0)
#Prepare a query and add all values then execute and commit to DB
db.transaction()
query.prepare('INSERT INTO CurrentReservation VALUES (?,?,?,?,?,?,?,?)')
query.addBindValue(ui.lineEdit.text())
query.addBindValue(ui.treeView.currentIndex().siblingAtColumn(1).data())
query.addBindValue(ui.comboBox_2.currentText())
query.addBindValue(ui.dateEdit.date().toString("yyyy-MM-dd"))
query.addBindValue(ui.dateEdit_2.date().toString("yyyy-MM-dd"))
#Check if discount is applied
if discount:
query.addBindValue(ui.spinBox.value())
query.addBindValue(0)
query.addBindValue(price - (price * (ui.spinBox.value() / 100)))
else:
query.addBindValue(ui.spinBox.value())
query.addBindValue(0)
query.addBindValue(price)
query.exec_()
db.commit()
db.close()
if query.lastError().text() == '':
QtWidgets.QMessageBox.information(
window, 'New Reservation',
'New Reservation has been successfully created',
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(window, 'New Reservation',
query.lastError().text(),
QtWidgets.QMessageBox.Ok)
ui.lineEdit.clear()
ui.checkBox.setCheckState(QtCore.Qt.Unchecked)
hlist = [
'Reserv. ID', 'Customer ID', 'Room #', 'From', 'To', 'Discount',
'Extension', 'Net Total'
]
worker = TableWorker(
update_table("CurrentReservation", hlist, mainui.current_res, db,
model))
thrd.tryStart(worker)
def run(self): # 2. Implement run()
ui = Ui_MainWindow()
window = QMainWindow()
version = self.build_settings['version']
ui.setupUi(window)
window.setWindowTitle("HotelManagementSystem v" + version)
#Setup Charts for isolated UI editing
ui.chartView = QtChart.QChartView(window)
ui.chartView_2 = QtChart.QChartView(window)
ui.gridLayout_3 = QtWidgets.QGridLayout(ui.groupBox_2)
ui.gridLayout_3.addWidget(ui.chartView, 0, 0, 1, 1)
ui.gridLayout_4 = QtWidgets.QGridLayout(ui.groupBox_3)
ui.gridLayout_4.addWidget(ui.chartView_2, 0, 0, 1, 1)
window.showMaximized()
#Database connection, instead of sqlite3
db = QSqlDatabase('QSQLITE')
db.setDatabaseName(self.get_resource('hotel.db'))
model = QSqlTableModel(self.app, db)
#Threading
thrd = QThreadPool().globalInstance()
thrd.setExpiryTimeout(5)
hlist = ['Reserv. ID','Customer ID','Room #','From','To','Discount','Extension','Net Total']
worker = TableWorker(update_table("CurrentReservation", hlist, ui.current_res, db, model)) #We pass a function for the worker to execute
thrd.tryStart(worker)
#Setup Signals
ui.newRes.triggered.connect(lambda: self.new_res_dialog(db, ui, model))
ui.newRoom.triggered.connect(lambda: self.new_room_dialog(db))
ui.newService.triggered.connect(lambda: self.new_srv_dialog(db))
ui.newCustomer.triggered.connect(lambda: self.new_customer_dialog(db))
ui.cancelRes.triggered.connect(lambda: self.new_cancel_dialog(window, db,
ui.current_res.currentIndex(),
thrd, model, hlist, ui.current_res))
#TODO Add new dialog for adding/deleting services to a current Reservation
ui.current_res.doubleClicked.connect(lambda: self.new_addservice_dialog(window, db,
ui.current_res.currentIndex(), thrd))
return self.app.exec_() # 3. End run() with this line
def new_srv_dialog(self, db):
#Setup UI
ui = Ui_Service()
new_srv = QDialog()
ui.setupUi(new_srv)
model = QSqlTableModel(new_srv, db)
#Setup Threading
thrd = QThreadPool().globalInstance()
worker = TableWorker(update_table("Service", ['Service ID','Name','Price'], ui.tableView, db, model)) #We pass a function for the worker to execute
thrd.tryStart(worker)
#Setup Signals and other UI elements
ui.pushButton.clicked.connect(lambda: thrd.tryStart(TableWorker(add_DB(ui, new_srv, db,
"Service",
[ui.lineEdit_2.text(),ui.lineEdit.text(),ui.doubleSpinBox.value()],
"?,?,?",
[ui.lineEdit_2,ui.lineEdit,ui.doubleSpinBox]))))
ui.pushButton_2.clicked.connect(lambda: thrd.tryStart(TableWorker(edit_DB(ui, new_srv, db,
"Service",
[ui.lineEdit_2.text(),ui.lineEdit.text(),ui.doubleSpinBox.value(),ui.lineEdit_2.text()],
"ID = ?, Name = ?, Price = ?",
"ID",
[ui.lineEdit_2,ui.lineEdit,ui.doubleSpinBox]))))
ui.pushButton_3.clicked.connect(lambda: thrd.tryStart(TableWorker(del_DB(ui, new_srv, db,
"Service", "ID = ?", [ui.lineEdit_2.text()],
[ui.lineEdit_2,ui.lineEdit,ui.doubleSpinBox]))))
#When an item in the tableView is selected update lineEdit and lineEdit_2 for better workflow
#You can just repeat the connect() method and it wouldn't override the previous one
ui.tableView.clicked.connect(lambda index: ui.lineEdit_2.setText(index.siblingAtColumn(0).data()))
ui.tableView.clicked.connect(lambda index: ui.lineEdit.setText(index.siblingAtColumn(1).data()))
ui.tableView.clicked.connect(lambda index: ui.doubleSpinBox.setValue(index.siblingAtColumn(2).data()))
ui.lineEdit_2.setText("SRVC" + str(randrange(100, 999, 10)))
#execute
new_srv.setWindowTitle('Create, edit, or delete a Service')
new_srv.exec()
def update_custTable_onEnter(window, hlist, ui, db, thrd, model):
db.open()
if ui.lineEdit_2.text() != '':
query = QSqlQuery(db)
query.setForwardOnly(True)
query.prepare(
'SELECT Name, Phone, DoB, Sex FROM Customer WHERE ID = ?')
query.addBindValue(ui.lineEdit_2.text())
query.exec_()
if query.record().count() > 0:
query.next()
ui.lineEdit.setText(query.value(0))
try:
ui.spinBox.setValue(int(query.value(1)))
except TypeError:
ui.spinBox.setValue(0)
ui.dateEdit.setDate(ui.dateEdit.date().fromString(
query.value(2), "yyyy-M-d"))
ui.comboBox.setCurrentIndex(ui.comboBox.findText(query.value(3)))
db.close()
else:
QtWidgets.QMessageBox.warning(
window, 'Warning',
f"Customer with ID: {ui.lineEdit_2.text()} was not found",
QtWidgets.QMessageBox.NoButton)
ui.lineEdit.clear()
ui.lineEdit_2.clear()
ui.spinBox.setValue(0)
ui.dateEdit.setDate(ui.dateEdit.date().currentDate())
elif ui.lineEdit_2.text() == '':
thrd.tryStart(
TableWorker(
update_table("CurrentReservation",
hlist,
ui.tableView,
db,
model,
where=f"CtmrID={ui.lineEdit_2.text()}")))