def get_total(table):
if sql.connectDB():
query = QtSql.QSqlQuery()
query.exec_("SELECT id FROM {0}".format(str(table)))
if num_rows(query) == -1:
rows = 0
else:
rows = num_rows(query)
return rows
def get_outward():
if sql.connectDB():
query = QtSql.QSqlQuery()
query.exec_("SELECT * FROM returns WHERE return_type = 'Outward'")
if not num_rows(query):
rows = 0
else:
rows = num_rows(query)
return rows
def get_constraint_total(table, key, value):
if sql.connectDB():
query = QtSql.QSqlQuery()
query.exec_("SELECT * FROM {0} WHERE {1} = '{2}'".format(
str(table), str(key), str(value)))
if num_rows(query) == -1:
rows = 0
else:
rows = num_rows(query)
return rows
def get_lesser_total(table, constraint, value):
if sql.connectDB():
query = QtSql.QSqlQuery()
query.exec_("SELECT * FROM {0} WHERE {1} < {2}".format(
str(table), str(constraint), value))
if num_rows(query) == -1:
rows = 0
else:
rows = num_rows(query)
return rows
def saveNow(self):
if sql.connectDB():
query = QtSql.QSqlQuery()
error = 0
if self.contactName.text() == "" or self.contactName.text(
).isspace():
error = 1
if self.contactPhone.text() == "" or self.contactPhone.text(
).isspace():
error = 2
if error > 0:
QtWidgets.QMessageBox.critical(
None, QtWidgets.qApp.tr("Error! All fields required!!"),
QtWidgets.qApp.tr("Contact name and Phone are required!"),
QtWidgets.QMessageBox.Close)
else:
qery = QtSql.QSqlQuery()
qery.prepare(
"select contact_name from contacts where contact_phone = ?"
)
qery.bindValue(0, self.contactPhone.text())
qery.exec_()
if num_rows(qery) == 0:
day = QtCore.QDate.currentDate().toString()
query.prepare(
"insert into contacts(contact_name, contact_phone, date_added) values(?,?,?)"
)
query.bindValue(0, str(self.contactName.text()))
query.bindValue(1, str(self.contactPhone.text()))
query.bindValue(2, day)
query.exec_()
if query.numRowsAffected():
self.contactName.setText("")
self.contactPhone.setText("")
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Contact added!"),
QtWidgets.qApp.tr("Contact has been added!"),
QtWidgets.QMessageBox.Close)
else:
QtWidgets.QMessageBox.critical(
None, QtWidgets.qApp.tr("Contact exists!"),
QtWidgets.qApp.tr(
"The contact already exists in the system"),
QtWidgets.QMessageBox.Close)
def fill_model(self):
if sql.connectDB():
mod = QtSql.QSqlQueryModel()
querry = QtSql.QSqlQuery()
querry.exec_("select distinct name from customers")
mod.setQuery(querry)
print(mod.rowCount())
complet = QtWidgets.QCompleter()
complet.setFilterMode(QtCore.Qt.MatchContains)
complet.setCaseSensitivity(QtCore.Qt.CaseInsensitive)
complet.setCompletionMode(QtWidgets.QCompleter.PopupCompletion)
self.name_enter.setCompleter(complet)
complet.setModel(mod)
self.name_enter.setFocus(QtCore.Qt.PopupFocusReason)
if mod.rowCount() > 0:
self.customer.setModel(mod)
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("Select distinct product_name from products")
model.setQuery(query)
if num_rows(query) > 0:
self.select_product.setModel(model)
qury = QtSql.QSqlQuery()
modal = QtSql.QSqlQueryModel()
qury.exec_("select * from invoices where invoice_number = ? and customer_name = ? and date_bought = ?")
qury.bindValue(0, self.invoice_no.text())
qury.bindValue(1, self.customer.currentText())
qury.bindValue(2,strftime("%d/%m/Y", gmtime()))
qury.exec_()
modal.setHeaderData(1, QtCore.Qt.Horizontal, "Customer Name")
modal.setHeaderData(2, QtCore.Qt.Horizontal, "Invoice No")
modal.setHeaderData(3, QtCore.Qt.Horizontal, "Product Code")
modal.setHeaderData(4, QtCore.Qt.Horizontal, "Product Name")
modal.setHeaderData(5, QtCore.Qt.Horizontal, "Price")
modal.setHeaderData(6, QtCore.Qt.Horizontal, "Real Price")
modal.setHeaderData(7, QtCore.Qt.Horizontal, "Amount")
modal.setHeaderData(8, QtCore.Qt.Horizontal, "Balance")
modal.setHeaderData(8, QtCore.Qt.Horizontal, "Quantity")
modal.setHeaderData(10, QtCore.Qt.Horizontal, "Total")
modal.setHeaderData(11, QtCore.Qt.Horizontal, "Date")
self.productsView.setModel(modal)
self.productsView.horizontalHeader().setMinimumHeight(40)
self.productsView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.productsView.setShowGrid(True)
self.productsView.show()
def delete_all(self):
sql.connectDB()
querry = QtSql.QSqlQuery()
querry.exec_("select name from logs")
if num_rows(querry) > 0:
ask = QtWidgets.QMessageBox.question(None, QtWidgets.qApp.tr("Confirm"),
QtWidgets.qApp.tr("\nAre you sure you want to delete all logs?"),
QtWidgets.QMessageBox.No, QtWidgets.QMessageBox.Yes)
if ask == QtWidgets.QMessageBox.Yes:
query = QtSql.QSqlQuery()
if query.exec_("delete from logs"):
QtWidgets.QMessageBox.information(None, QtWidgets.qApp.tr("Deleted successfully"),
QtWidgets.qApp.tr("\nAll logs have been deleted!"),QtWidgets.QMessageBox.Close)
self.get_logs()
else:
QtWidgets.QMessageBox.information(None, QtWidgets.qApp.tr("Logs empty"),
QtWidgets.qApp.tr("\nThere are no logs available to delete"),
QtWidgets.QMessageBox.Ok)
def validateForm():
error = 0
if (ui.username.text().isspace() or ui.username.text()
== '') and (ui.password.text().isspace()
or ui.password.text() == ''):
ui.lbl_error.setText(
"Please enter your username and password.\n")
error = 1
else:
if ui.username.text().isspace() or ui.username.text(
) == '':
ui.lbl_error.setText("Please enter your username")
error = 2
else:
username = ui.username.text()
if ui.password.text().isspace() or ui.password.text(
) == '':
ui.lbl_error.setText("Please enter your password")
error = 3
else:
ui.lbl_error.clear()
password = ui.password.text()
if error == 0:
query = QtSql.QSqlQuery()
query.prepare(
"SELECT * from users where username = ? and password = ?"
)
query.bindValue(0, username)
query.bindValue(1, password)
if num_rows(query) > 0:
Login.close()
else:
QtWidgets.QMessageBox.critical(
None, QtWidgets.qApp.tr("Not registered"),
QtWidgets.qApp.tr(
"There is no user with those details\n"
"Ensure you're registered before trying!\n"
"to login!"), QtWidgets.QMessageBox.Ok,
QtWidgets.QMessageBox.Close)
def validateForm(self):
sql.connectDB()
self.lbl_error.setText("")
error = 0
if (self.username.text().isspace() or self.username.text() == '') and (
self.password.text().isspace() or self.password.text() == ''):
self.lbl_error.setText("Please enter your username and password.\n")
error = 1
else:
if self.username.text().isspace() or self.username.text() == '':
self.lbl_error.setText("Please enter your username")
error = 2
else:
username = self.username.text()
if self.password.text().isspace() or self.password.text() == '':
self.lbl_error.setText("Please enter your password")
error = 3
else:
self.lbl_error.clear()
password = self.password.text()
if error == 0:
query = QtSql.QSqlQuery()
query.prepare("SELECT username, password from users where username = ? and password = ?")
query.bindValue(0, username)
query.bindValue(1, password)
query.exec_()
if num_rows(query) > 0:
res = QtWidgets.QMessageBox.critical(None, QtWidgets.qApp.tr("Login Success"),
QtWidgets.qApp.tr("\nYou have logged in successfully!"),
QtWidgets.QMessageBox.Ok)
return res
else:
res = QtWidgets.QMessageBox.critical(None, QtWidgets.qApp.tr("Not registered"),
QtWidgets.qApp.tr("\nInvalid user details! Please try again!!"),
QtWidgets.QMessageBox.Abort)
return res
def main():
if sql.connectDB():
query = QSqlQuery()
query.exec_("SELECT * from users")
if num_rows(query) < 1:
import sys
app = QtWidgets.QApplication(sys.argv)
signup = QtWidgets.QFrame()
u = Ui_Register()
u.setupUi(signup)
name = u.name.text()
username = u.username.text()
email = u.email.text()
password = u.password.text()
phone = u.phone.text()
typ = u.userType.currentText()
status = u.status.currentText()
def register_user():
error = 0
if u.name.text() == '' or u.name.text().isspace():
u.lblError.setText("Please enter name")
error = 1
if u.username.text() == '' or u.username.text().isspace():
u.lblError.setText("Please enter username")
error = 2
if u.password.text() == '' or u.password.text().isspace():
u.lblError.setText("Please enter password")
error = 3
if u.email.text() == '' or u.email.text().isspace():
u.lblError.setText("Please enter email")
error = 4
if u.phone.text() == '' or u.phone.text().isspace():
u.lblError.setText("Please enter phone number")
error = 5
if (u.name.text() == '' or u.name.text().isspace()) and (
u.username.text() == '' or u.username.text().isspace()
) and (u.email.text() == '' or u.email.text().isspace()) and (
u.password.text() == '' or u.password.text().isspace()
) and (u.phone.text() == '' or u.phone.text().isspace()):
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Correct Errors!"),
QtWidgets.qApp.tr("Please enter all details "),
QtWidgets.QMessageBox.Close, QtWidgets.QMessageBox.Ok)
u.lblError.setText("")
if error == 0:
insert_data()
def insert_data():
query.prepare(
"INSERT INTO users(name,password,username,email,phone,user_type,status) "
"VALUES(?,?,?,?,?,?,?)")
query.bindValue(0, u.name.text())
query.bindValue(1, u.password.text())
query.bindValue(2, u.username.text())
query.bindValue(3, u.email.text())
query.bindValue(4, u.phone.text())
query.bindValue(5, u.userType.currentText())
query.bindValue(6, u.status.currentText())
if query.exec_():
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Registration Successful"),
QtWidgets.qApp.
tr("Your user details have been saved\n"
"You can now login using your username and password"
), QtWidgets.QMessageBox.Close,
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Registration Not Successful"),
QtWidgets.qApp.
tr("Registration could not be processed successfully"),
QtWidgets.QMessageBox.Close, QtWidgets.QMessageBox.Ok)
signup.setGeometry(500, 150, 750, 500)
signup.setMaximumWidth(750)
signup.setWindowTitle("Register now")
signup.show()
u.btnCancel.clicked.connect(quit)
u.btnRegister.clicked.connect(register_user)
sys.exit(app.exec_())
else:
import sys
app = QtWidgets.QApplication(sys.argv)
Login = QtWidgets.QFrame()
ui = Ui_Login()
ui.setupUi(Login)
def validateForm():
error = 0
if (ui.username.text().isspace() or ui.username.text()
== '') and (ui.password.text().isspace()
or ui.password.text() == ''):
ui.lbl_error.setText(
"Please enter your username and password.\n")
error = 1
else:
if ui.username.text().isspace() or ui.username.text(
) == '':
ui.lbl_error.setText("Please enter your username")
error = 2
else:
username = ui.username.text()
if ui.password.text().isspace() or ui.password.text(
) == '':
ui.lbl_error.setText("Please enter your password")
error = 3
else:
ui.lbl_error.clear()
password = ui.password.text()
if error == 0:
query = QtSql.QSqlQuery()
query.prepare(
"SELECT * from users where username = ? and password = ?"
)
query.bindValue(0, username)
query.bindValue(1, password)
if num_rows(query) > 0:
Login.close()
else:
QtWidgets.QMessageBox.critical(
None, QtWidgets.qApp.tr("Not registered"),
QtWidgets.qApp.tr(
"There is no user with those details\n"
"Ensure you're registered before trying!\n"
"to login!"), QtWidgets.QMessageBox.Ok,
QtWidgets.QMessageBox.Close)
Login.setGeometry(500, 150, 750, 500)
Login.setMaximumWidth(750)
Login.setWindowTitle("Enter Your Details To Login")
Login.show()
ui.btn_cancel.clicked.connect(quit)
ui.btn_login.clicked.connect(validateForm)
sys.exit(app.exec_())
def new_user(self):
if sql.connectDB():
query = QtSql.QSqlQuery()
error = 0
if self.name.text() == '' or self.name.text().isspace():
error = 1
if self.username.text() == '' or self.username.text().isspace():
error = 2
if self.password.text() == '' or self.password.text().isspace():
error = 3
if self.email.text() == '' or self.email.text().isspace():
error = 4
if self.phone.text() == '' or self.phone.text().isspace():
error = 5
if (self.name.text() == '' or self.name.text().isspace()) and (
self.username.text() == ''
or self.username.text().isspace()
) and (self.email.text() == '' or self.email.text().isspace()
) and (self.password.text() == '' or self.phone.text() == ''
or self.phone.text().isspace()):
error = 6
query.prepare(
"SELECT * from users where username = ? and user_type = ? or phone = ? or email = ?"
)
query.bindValue(0, self.username.text())
query.bindValue(1, self.userType.currentText())
query.bindValue(2, self.phone.text())
query.bindValue(3, self.email.text())
query.exec_()
if num_rows(query) > 0:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("User exits already"),
QtWidgets.qApp.tr(
"\nThe user already exists in the system!"),
QtWidgets.QMessageBox.Ok)
else:
if error == 0:
query.prepare(
"INSERT INTO users(name,password,username,email,phone,user_type,status) "
"VALUES(?,?,?,?,?,?,?)")
query.bindValue(0, self.name.text())
query.bindValue(1, self.password.text())
query.bindValue(2, self.username.text())
query.bindValue(3, self.email.text())
query.bindValue(4, self.phone.text())
query.bindValue(5, self.userType.currentText())
query.bindValue(6, self.status.currentText())
if query.exec_():
self.phone.setText("")
self.password.setText("")
self.name.setText("")
self.email.setText("")
self.username.setText("")
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Registration Successful"),
QtWidgets.qApp.tr(
"\nThe user's details have been saved"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None,
QtWidgets.qApp.tr("Registration Not Successful"),
QtWidgets.qApp.
tr("\nThe user could not be registered successfully"
), QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Correct Errors!"),
QtWidgets.qApp.tr(
"\nPlease enter all required fields "),
QtWidgets.QMessageBox.Ok)
