def populate_fileds(self):
connection = sqlite3.connect("ims.db")
if connection:
self.cursor = connection.cursor()
self.cursor.execute(
"select * from returns where product_name = '{0}'".format(
str(self.choose.currentText())))
rows = self.cursor.fetchall()
if rows:
self.formGroupBox.setEnabled(True)
self.productName.setText(rows[0][2])
self.productCode.setText(rows[0][1])
self.returnee.setText(str(rows[0][3]))
self.returnType.setText(str(rows[0][4]))
self.price.setText(str(rows[0][5]))
self.quantity.setText(str(rows[0][6]))
self.returnDate.setText(rows[0][7])
self.reason.setPlainText(rows[0][8])
self.btnUpdate.setEnabled(True)
sql.connectDB()
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("Select distinct product_name from returns")
model.setQuery(query)
self.choose.setModel(model)
else:
self.formGroupBox.setDisabled(True)
self.btnUpdate.setDisabled(True)
def search_sales(self):
sql.connectDB()
if not self.search.text() == '' or not self.search.text().isspace():
qry = QtSql.QSqlQuery()
# self.setWindowTitle("Search results for " + self.search.text())
qry.prepare("select * from sales where customer_name LIKE '%{0}%' or product_name LIKE '%{0}%' or "
"quantity LIKE '%{0}%' or amount_paid LIKE '%{0}%' or date_bought LIKE '%{0}%'".format(
str(self.search.text())))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
self.lblTotal.setText(str(model.rowCount()))
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Customer")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Product Name")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Quantity")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Amount Paid")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Date")
self.saleView.setModel(model)
self.saleView.horizontalHeader().setMinimumHeight(40)
self.saleView.horizontalHeader().setDefaultSectionSize(180)
self.saleView.setColumnHidden(0, True)
self.saleView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.saleView.setShowGrid(True)
self.saleView.show()
def update_view(self):
sql.connectDB()
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("Select distinct product_code, product_name from returns")
model.setQuery(query)
self.choose.setModel(model)
self.choose.setModelColumn(1)
comp = QtWidgets.QCompleter()
comp.setModel(model)
comp.setCompletionMode(QtWidgets.QCompleter.PopupCompletion)
comp.setFilterMode(QtCore.Qt.MatchContains)
comp.setCaseSensitivity(QtCore.Qt.CaseInsensitive)
self.productCode.setCompleter(comp)
complet = QtWidgets.QCompleter()
complet.setFilterMode(QtCore.Qt.MatchContains)
complet.setCaseSensitivity(QtCore.Qt.CaseInsensitive)
complet.setCompletionMode(QtWidgets.QCompleter.PopupCompletion)
qery = QtSql.QSqlQuery()
qery.exec_("select * from returns")
modal = QtSql.QSqlQueryModel()
modal.setQuery(qery)
self.search.setCompleter(complet)
complet.setModel(modal)
self.search.textChanged.connect(lambda: self.get_return())
self.formGroupBox.setDisabled(True)
def update_records(self):
sql.connectDB()
qry = QtSql.QSqlQuery()
qry.prepare("UPDATE customers SET name = ?, phone = ?, city = ?, region = ?, gender = ?, address = ?,"
"post_office = ?, email = ?, date_registered = ?, remarks = ? WHERE name = ?")
qry.bindValue(0, self.customerName.text())
qry.bindValue(1, self.phone.text())
qry.bindValue(2, self.city.text())
qry.bindValue(3, self.region.text())
qry.bindValue(4, self.gender.text())
qry.bindValue(5, self.address.toPlainText())
qry.bindValue(6, self.postalAddress.text())
qry.bindValue(7, self.email.text())
qry.bindValue(8, self.dateAdded.text())
qry.bindValue(9, self.remarks.toPlainText())
qry.bindValue(10, self.customerChoose.currentText())
qry.exec_()
if qry:
QtWidgets.QMessageBox.information(None, QtWidgets.qApp.tr("Updated Successfully"),
QtWidgets.qApp.tr("\nCustomer information has been successfully updated"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(None, QtWidgets.qApp.tr("Not Updated"),
QtWidgets.qApp.tr("Data update not successful: " + qry.lastError().text()),
QtWidgets.QMessageBox.Ok)
def update_view(self):
sql.connectDB()
self.model = QtSql.QSqlQueryModel()
self.query = QtSql.QSqlQuery()
self.query.exec_("select distinct contact_name from contacts")
self.model.setQuery(self.query)
self.selectContact.setModel(self.model)
def retranslateUi(self, EditProduct):
_translate = QtCore.QCoreApplication.translate
EditProduct.setWindowTitle(_translate("EditProduct", "Edit Product"))
self.label.setText(_translate("EditProduct", "Edit Product"))
self.label_3.setText(_translate("EditProduct", "Select product:"))
self.lblTotal.setText(_translate("EditProduct", "Total:"))
self.lblDate.setText(_translate("EditProduct", "Date:"))
self.lblRealPrice.setText(_translate("EditProduct", "Real Price:"))
self.lblSalePrice.setText(_translate("EditProduct", "Sale Price:"))
self.lblQuantity.setText(_translate("EditProduct", "Quantity:"))
self.lblProductName.setText(_translate("EditProduct", "Product Name:"))
self.btnSave.setText(_translate("EditProduct", "Save"))
self.lblSupplier.setText(_translate("EditProduct", "Supplier:"))
self.btnAddSupplier.setToolTip(
_translate("EditProduct", "Add new supplier"))
self.btnAddSupplier.setText(_translate("EditProduct", "+"))
self.lblProductCode.setText(_translate("EditProduct", "Product Code:"))
self.btnCancel.setText(_translate("EditProduct", "Cancel"))
self.label_2.setText(_translate("EditProduct", "Category:"))
sql.connectDB()
self.model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
self.hideBox.setDisabled(True)
query.prepare("SELECT DISTINCT product_name FROM products")
if query.exec_():
self.hideBox.setEnabled(True)
self.model.setQuery(query)
self.product.setModel(self.model)
self.btnRefresh.setEnabled(True)
else:
self.btnRefresh.setDisabled(True)
def update_view(self):
sql.connectDB()
model = QtSql.QSqlTableModel()
con = sqlite3.connect("ims.db")
cursor = con.cursor()
cursor.execute("select sum(amount_paid) from sales")
rows = cursor.fetchall()
if len(rows) > 0:
self.lblAmount.setText(str(rows[0][0]))
else:
self.lblAmount.setText("0")
model.setTable("sales")
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Customer")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Product Name")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Quantity")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Amount Paid")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Date")
model.select()
self.btlTotal.setText(str(model.rowCount()))
self.allSalesView.setModel(model)
self.allSalesView.horizontalHeader().setMinimumHeight(40)
self.allSalesView.horizontalHeader().setDefaultSectionSize(180)
self.allSalesView.setColumnHidden(0, True)
self.allSalesView.setColumnHidden(6, True)
self.allSalesView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.allSalesView.setShowGrid(True)
self.allSalesView.show()
def get_suppliers(self):
sql.connectDB()
model = QtSql.QSqlTableModel()
q = QtSql.QSqlQuery()
model.setTable("supplier_info")
model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Name")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Contact")
model.setHeaderData(3, QtCore.Qt.Horizontal, "City")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Region")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Fax")
model.setHeaderData(6, QtCore.Qt.Horizontal, "Address")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Postal/Zip")
model.setHeaderData(8, QtCore.Qt.Horizontal, "Email")
model.setHeaderData(9, QtCore.Qt.Horizontal, "Registration No.")
model.setHeaderData(10, QtCore.Qt.Horizontal, "No. of Products")
model.setHeaderData(11, QtCore.Qt.Horizontal, "Remarks")
model.setHeaderData(12, QtCore.Qt.Horizontal, "Date Added")
model.select()
self.supplierView.setModel(model)
self.supplierView.horizontalHeader().setMinimumHeight(40)
self.supplierView.horizontalHeader().setDefaultSectionSize(120)
self.supplierView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.supplierView.setColumnHidden(0, True)
self.supplierView.setShowGrid(True)
self.supplierView.show()
def update_records(self):
sql.connectDB()
qry = QtSql.QSqlQuery()
qry.prepare(
"UPDATE users SET name = ?, phone = ?, username = ?, password = ?, email = ?,"
"user_type = ?, status = ? WHERE username = ?")
qry.bindValue(0, self.name.text())
qry.bindValue(1, self.phone.text())
qry.bindValue(2, self.username.text())
qry.bindValue(3, self.password.text())
qry.bindValue(4, self.email.text())
qry.bindValue(5, self.user_type.currentText())
qry.bindValue(6, self.status.currentText())
qry.bindValue(7, self.select_user.currentText())
if qry.exec_():
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Updated Successfully"),
QtWidgets.qApp.tr(
"\nUser information has been successfully updated"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Not Updated"),
QtWidgets.qApp.tr("Data update not successful"),
QtWidgets.QMessageBox.Ok)
def search(self):
sql.connectDB()
if not self.search_users.text() == '' or not self.search_users.text(
).isspace():
qry = QtSql.QSqlQuery()
qry.prepare(
"select * from users where name LIKE '%{0}%' or username LIKE '%{0}%' or phone LIKE '%{0}%'"
" or email LIKE '%{0}%'".format(str(self.search_users.text())))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Full Name")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Password")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Email")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Phone")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Username")
model.setHeaderData(6, QtCore.Qt.Horizontal, "Type")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Status")
self.widgetUsers.setModel(model)
self.widgetUsers.setColumnHidden(0, True)
self.widgetUsers.setColumnHidden(2, True)
self.widgetUsers.setSelectionBehavior(
QtWidgets.QTableView.SelectRows)
self.widgetUsers.show()
def select_invoices(self):
sql.connectDB()
model = QtSql.QSqlTableModel()
q = QtSql.QSqlQuery()
model.setTable("invoices")
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Customer")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Invoice No.")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Product Code")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Product Name")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Sale Price")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Amount Paid")
model.setHeaderData(8, QtCore.Qt.Horizontal, "Balance")
model.setHeaderData(9, QtCore.Qt.Horizontal, "Quantity")
model.setHeaderData(10, QtCore.Qt.Horizontal, "Total")
model.setHeaderData(11, QtCore.Qt.Horizontal, "Date")
model.select()
self.invoiceView.setModel(model)
self.invoiceView.horizontalHeader().setMinimumHeight(40)
self.invoiceView.horizontalHeader().setDefaultSectionSize(180)
self.invoiceView.setColumnHidden(0, True)
self.invoiceView.setColumnHidden(6, True)
self.invoiceView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.invoiceView.setShowGrid(True)
self.invoiceView.show()
def update_records(self):
sql.connectDB()
qry = QtSql.QSqlQuery()
name = (str(self.select_creditor.currentText()))
qry.prepare(
"UPDATE creditors SET creditor_name = ?, phone = ?, product_name = ?, amount_paid = ?, amount_owed = ?,"
"total = ?, quantity = ?, due_date = ? WHERE creditor_name = ?")
qry.bindValue(0, self.creditor_name.text())
qry.bindValue(1, self.phone.text())
qry.bindValue(2, self.product_name.text())
qry.bindValue(3, self.amountPaid.text())
qry.bindValue(4, self.amountOwed.text())
qry.bindValue(5, self.total.text())
qry.bindValue(6, self.quantity.text())
qry.bindValue(7, self.dueDate.text())
qry.bindValue(8, name)
qry.exec_()
if qry:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Updated Successfully"),
QtWidgets.qApp.tr(
"\nCreditor information has been successfully updated"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Not Updated"),
QtWidgets.qApp.tr("Data update not successful: " +
qry.lastError().text()),
QtWidgets.QMessageBox.Ok)
def update_view(self):
model = QtSql.QSqlQueryModel()
sql.connectDB()
query = QtSql.QSqlQuery()
query.exec_("select distinct name from customers")
if query:
model.setQuery(query)
self.customerChoose.setStyleSheet("padding:2px 0 2px 5px;")
self.customerChoose.setModel(model)
def toggle_groupbox(self):
self.group.show()
sql.connectDB()
self.lbltxt = self.selected.currentText()
if self.lbltxt == "":
self.del_action.setText("")
else:
self.item = self.lbltxt.rstrip("s")
if self.item == "Categorie":
i = "category"
else:
i = self.item.lower()
self.del_action.setText("Select " + i + " to delete:")
self.del_action.show()
if self.lbltxt == "Products":
string = "products"
self.item_name = "product_name"
elif self.lbltxt == "Users":
string = "users"
self.item_name = "username"
elif self.lbltxt == "Categories":
string = "categories"
self.item_name = "name"
elif self.lbltxt == "Creditors":
string = "creditors"
self.item_name = "creditor_name"
elif self.lbltxt == "Debtors":
string = "debts"
self.item_name = "customer_name"
elif self.lbltxt == "Suppliers":
string = "suppliers"
self.item_name = "name"
elif self.lbltxt == "Supplier Information":
string = "supplier_info"
self.item_name = "name"
elif self.lbltxt == "Returns":
string = "returns"
self.item_name = "product_name"
elif self.lbltxt == "Customers":
string = "customers"
self.item_name = "name"
elif self.lbltxt == "Contacts":
string = "contacts"
self.item_name = "contact_name"
else:
string = "logs"
self.item_name = "name"
query = QtSql.QSqlQuery()
query.prepare("select {0} from {1}".format(str(self.item_name),
str(string)))
if query.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(query)
self.name.setModel(model)
def update_view(self):
self.formGroupBox.setDisabled(True)
model = QtSql.QSqlQueryModel()
sql.connectDB()
query = QtSql.QSqlQuery()
query.exec_("select distinct creditor_name from creditors")
if query:
model.setQuery(query)
self.select_creditor.setStyleSheet("padding:2px 0 2px 5px;")
self.select_creditor.setModel(model)
def update_view(self):
sql.connectDB()
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("select distinct product_name from products")
model.setQuery(query)
if model.rowCount() > 0:
self.btnDelete.setEnabled(True)
self.selectedProduct.setModel(model)
else:
self.btnDelete.setDisabled(True)
def get_category(self):
sql.connectDB()
if not self.filter.text() == '' or not self.filter.text().isspace():
qry = QtSql.QSqlQuery()
qry.prepare(
"select name from categories where name LIKE '%{0}%'".format(
str(self.filter.text())))
if qry.exec_():
self.model = QtSql.QSqlQueryModel()
self.model.setQuery(qry)
self.categoryView.setModel(self.model)
self.categoryView.show()
def new_log(user, operation):
day = strftime("%d/%m/%Y", gmtime())
time = strftime("%I:%M:%S%p")
sql.connectDB()
query = QtSql.QSqlQuery()
query.prepare(
"insert into logs(name, logged_date, operation) values(?,?,?)")
query.bindValue(0, user)
query.bindValue(1, day)
query.bindValue(2, operation + " at " + time)
if query.exec_():
print("Successfully logged...")
else:
print(query.lastError().text())
def setupUi(self, Debtors):
Debtors.setObjectName("Debtors")
Debtors.resize(929, 584)
self.groupBox = QtWidgets.QGroupBox(Debtors)
self.groupBox.setGeometry(QtCore.QRect(40, -10, 851, 91))
self.groupBox.setStyleSheet("")
self.groupBox.setTitle("")
self.groupBox.setObjectName("groupBox")
self.btnSearch = QtWidgets.QPushButton(self.groupBox)
self.btnSearch.setGeometry(QtCore.QRect(370, 40, 99, 31))
self.btnSearch.setObjectName("btnSearch")
self.btnEdit = QtWidgets.QPushButton(self.groupBox)
self.btnEdit.setGeometry(QtCore.QRect(620, 40, 111, 31))
self.btnEdit.setStyleSheet("")
self.btnEdit.setObjectName("btnEdit")
self.search = QtWidgets.QLineEdit(self.groupBox)
self.search.setGeometry(QtCore.QRect(20, 40, 351, 31))
self.search.setObjectName("search")
self.btnDelete = QtWidgets.QPushButton(self.groupBox)
self.btnDelete.setGeometry(QtCore.QRect(750, 40, 81, 31))
self.btnDelete.setStyleSheet("")
self.btnDelete.setObjectName("btnDelete")
self.btnNewDebtor = QtWidgets.QPushButton(self.groupBox)
self.btnNewDebtor.setGeometry(QtCore.QRect(480, 40, 121, 31))
self.btnNewDebtor.setStyleSheet("")
self.btnNewDebtor.setObjectName("btnNewDebtor")
self.debtorView = QtWidgets.QTableView(Debtors)
self.debtorView.setGeometry(QtCore.QRect(40, 80, 851, 441))
self.debtorView.setObjectName("debtorView")
self.btnClose = QtWidgets.QPushButton(Debtors)
self.btnClose.setGeometry(QtCore.QRect(790, 530, 99, 41))
self.btnClose.setObjectName("btnClose")
self.retranslateUi(Debtors)
self.btnClose.clicked.connect(Debtors.close)
self.btnDelete.clicked.connect(lambda: self.delete_record())
QtCore.QMetaObject.connectSlotsByName(Debtors)
sql.connectDB()
model = QtSql.QSqlQueryModel()
qry = QtSql.QSqlQuery()
qry.exec_("select distinct customer_name from debts")
model.setQuery(qry)
completer = QtWidgets.QCompleter()
completer.setModel(model)
completer.setCaseSensitivity(QtCore.Qt.CaseInsensitive)
completer.setFilterMode(QtCore.Qt.MatchContains)
completer.setCompletionMode(QtWidgets.QCompleter.PopupCompletion)
completer.setWidget(self.search)
self.search.setCompleter(completer)
def showContacts(self):
sql.connectDB()
model = QtSql.QSqlTableModel()
model.setTable("contacts")
model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Contact Name")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Phone Number")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Date Saved")
model.select()
self.contactView.setModel(model)
self.contactView.horizontalHeader().setDefaultSectionSize(200)
self.contactView.horizontalHeader().setMinimumHeight(40)
self.contactView.setColumnHidden(0, True)
self.contactView.show()
def setupUi(self, Creditors):
Creditors.setObjectName("Creditors")
Creditors.resize(931, 585)
Creditors.setMaximumSize(QtCore.QSize(931, 585))
self.groupBox = QtWidgets.QGroupBox(Creditors)
self.groupBox.setGeometry(QtCore.QRect(40, -10, 851, 91))
self.groupBox.setStyleSheet("")
self.groupBox.setTitle("")
self.groupBox.setObjectName("groupBox")
self.btnSearch = QtWidgets.QPushButton(self.groupBox)
self.btnSearch.setGeometry(QtCore.QRect(370, 40, 99, 31))
self.btnSearch.setObjectName("btnSearch")
self.btnEdit = QtWidgets.QPushButton(self.groupBox)
self.btnEdit.setGeometry(QtCore.QRect(630, 40, 111, 31))
self.btnEdit.setStyleSheet("")
self.btnEdit.setObjectName("btnEdit")
self.search_query = QtWidgets.QLineEdit(self.groupBox)
self.search_query.setGeometry(QtCore.QRect(20, 40, 351, 31))
self.search_query.setObjectName("search")
self.btnDelete = QtWidgets.QPushButton(self.groupBox)
self.btnDelete.setGeometry(QtCore.QRect(750, 40, 81, 31))
self.btnDelete.setStyleSheet("")
self.btnDelete.setObjectName("btnDelete")
self.btnNewCreditor = QtWidgets.QPushButton(self.groupBox)
self.btnNewCreditor.setGeometry(QtCore.QRect(480, 40, 121, 31))
self.btnNewCreditor.setStyleSheet("")
self.btnNewCreditor.setObjectName("btnNewCreditor")
self.creditorView = QtWidgets.QTableView(Creditors)
self.creditorView.setGeometry(QtCore.QRect(40, 80, 851, 441))
self.creditorView.setObjectName("creditorView")
self.btnClose = QtWidgets.QPushButton(Creditors)
self.btnClose.setGeometry(QtCore.QRect(790, 530, 99, 41))
self.btnClose.setObjectName("btnClose")
sql.connectDB()
complet = QtWidgets.QCompleter()
complet.setFilterMode(QtCore.Qt.MatchContains)
complet.setCaseSensitivity(QtCore.Qt.CaseInsensitive)
complet.setCompletionMode(QtWidgets.QCompleter.PopupCompletion)
qery = QtSql.QSqlQuery()
qery.exec_("select creditor_name from creditors")
modal = QtSql.QSqlQueryModel()
modal.setQuery(qery)
self.search_query.setCompleter(complet)
complet.setModel(modal)
self.retranslateUi(Creditors)
self.search_query.textChanged.connect(lambda: self.get_creditor())
self.btnClose.clicked.connect(Creditors.close)
QtCore.QMetaObject.connectSlotsByName(Creditors)
def update_records(self):
if sql.connectDB():
query = QtSql.QSqlQuery()
query.prepare(
"UPDATE products SET product_code = ?,product_name = ?,real_price = ?,sale_price = ?, total_price = ?,"
"quantity = ?, product_category = ?, date_added = ?, supplier = ? WHERE product_name = ?"
)
query.bindValue(0, self.productCode.text())
query.bindValue(1, self.productName.text())
query.bindValue(2, self.realPrice.text())
query.bindValue(3, self.salePrice.text())
query.bindValue(4, self.total.text())
query.bindValue(5, self.quantity.text())
query.bindValue(6, self.category.currentText())
query.bindValue(7, self.dateAdded.date())
query.bindValue(8, self.supplier.currentText())
query.bindValue(9, self.product.currentText())
if query.exec_():
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Updated Successfully"),
QtWidgets.qApp.tr(
"\nProduct has been successfully edited"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Not Updated"),
QtWidgets.qApp.tr("Data update not successful"),
QtWidgets.QMessageBox.Ok)
def insert_data(self):
if sql.connectDB():
query = QtSql.QSqlQuery()
query.prepare(
"INSERT INTO debts(customer_name,phone,city,area,address,product_name,"
"amount_paid, amount_owed,total, quantity,due_date) VALUES(?,?,?,?,?,?,?,?,?,?,?)"
)
query.bindValue(0, self.name.text())
query.bindValue(1, self.phone.text())
query.bindValue(2, self.town.text())
query.bindValue(3, self.area.text())
query.bindValue(4, self.address.toPlainText())
query.bindValue(5, self.productName.text())
query.bindValue(6, self.amountPaid.text())
query.bindValue(7, self.amountOwed.text())
query.bindValue(8, self.total.text())
query.bindValue(9, self.quantity.text())
query.bindValue(10, self.dueDate.text())
if query.exec_():
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Save Successful"),
QtWidgets.qApp.tr(
"\nYour debt information has been saved"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Not Successful"),
QtWidgets.qApp.tr("Data could not be saved successfully"),
QtWidgets.QMessageBox.Ok)
def selectStock(self):
if sql.connectDB():
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("select product_name from products")
model.setQuery(query)
self.productName.setModel(model)
def insert_data(self):
if sql.connectDB():
query = QtSql.QSqlQuery()
query.prepare(
"INSERT INTO returns(product_code,product_name,returnee,return_type,price,qty,date_returned,"
"reason) VALUES(?,?,?,?,?,?,?,?)")
query.bindValue(0, self.productCode.text())
query.bindValue(1, self.productName.text())
query.bindValue(2, self.returnee.text())
query.bindValue(3, self.returnType.currentText())
query.bindValue(4, self.price.text())
query.bindValue(5, self.quantity.text())
query.bindValue(6, self.dateReturned.text())
query.bindValue(7, self.reason.toPlainText())
if query.exec_():
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Save Successful"),
QtWidgets.qApp.tr("\nReturn information has been saved"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Not Successful"),
QtWidgets.qApp.tr("Data could not be saved successfully" +
query.lastError().text()),
QtWidgets.QMessageBox.Ok)
def update_stock(self):
if sql.connectDB():
query = QtSql.QSqlQuery()
if self.quantity.text() == "" or self.quantity.text().isspace():
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Quantity is empty"),
QtWidgets.qApp.tr("\nPlease enter new product quantity"),
QtWidgets.QMessageBox.Ok)
else:
query.prepare(
"UPDATE products SET quantity = ? WHERE product_name = ?")
query.bindValue(1, self.productName.currentText())
query.bindValue(0, self.quantity.text())
if query.exec_():
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Updated Successfully"),
QtWidgets.qApp.tr(
"\nProduct stock has been successfully updated"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Not Updated"),
QtWidgets.qApp.tr("Data update not successful"),
QtWidgets.QMessageBox.Ok)
def delete_record(self):
if sql.connectDB():
confirm = QtWidgets.QMessageBox.question(None, QtWidgets.qApp.tr("Sure to delete?"),
QtWidgets.qApp.tr("\nAre you sure you want to delete invoice with id <b>" +
self.selectSale.currentText() + "</b>?"),
QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.No)
if confirm == QtWidgets.QMessageBox.Yes:
product = self.selectSale.currentText()
con = sqlite3.connect("ims.db")
if con:
cursor = con.cursor()
cursor.execute("select * from sales where product_name = '" + product + "'")
row = cursor.fetchall()
if row:
query = QtSql.QSqlQuery()
query.prepare("delete from sales where product_name = ? and customer_name = ?")
print(row[0][0])
query.bindValue(0, product)
query.bindValue(1, str(row[0][1]))
if query.exec_():
self.update_view()
QtWidgets.QMessageBox.information(None, QtWidgets.qApp.tr("Sale deleted!"),
QtWidgets.qApp.tr("\nSale has been deleted"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.critical(None, QtWidgets.qApp.tr("Sale not deleted!"),
QtWidgets.qApp.tr("\nSale couldn't be deleted"),
QtWidgets.QMessageBox.Ok)
def delete_all(self):
if sql.connectDB():
confirm = QtWidgets.QMessageBox.question(
None, QtWidgets.qApp.tr("Sure to delete products?"),
QtWidgets.qApp.tr(
"Are you sure you want to delete <b>all</b> products? "
"This will delete all products data!"),
QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.No)
if confirm == QtWidgets.QMessageBox.Yes:
self.update_view()
self.selectProducts()
query = QtSql.QSqlQuery()
query.prepare("truncate products")
query.bindValue(0, self.selectedProduct.currentText())
if query.exec_():
self.update_view()
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Product deleted!"),
QtWidgets.qApp.tr("Product has been deleted"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.critical(
None, QtWidgets.qApp.tr("Product not deleted!"),
QtWidgets.qApp.tr("Product couldn't be deleted"),
QtWidgets.QMessageBox.Ok)
def delete_record(self):
if sql.connectDB():
confirm = QtWidgets.QMessageBox.question(
None, QtWidgets.qApp.tr("Sure to delete?"),
QtWidgets.qApp.tr("Are you sure you want to delete <b>" +
self.selectedProduct.currentText() +
"</b> from products?"),
QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.No)
if confirm == QtWidgets.QMessageBox.Yes:
self.update_view()
self.selectProducts()
query = QtSql.QSqlQuery()
query.prepare("delete from products where product_name = ?")
query.bindValue(0, self.selectedProduct.currentText())
if query.exec_():
self.update_view()
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Product deleted!"),
QtWidgets.qApp.tr("Product has been deleted"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.critical(
None, QtWidgets.qApp.tr("Product not deleted!"),
QtWidgets.qApp.tr("Product couldn't be deleted"),
QtWidgets.QMessageBox.Ok)
def update_records(self):
self.update_view()
if sql.connectDB():
query = QtSql.QSqlQuery()
query.prepare(
"UPDATE returns SET product_code = ?,product_name = ?, returnee = ?, return_type = ?, price = ?,"
"qty = ?, date_returned = ?, reason = ? WHERE product_name = ?"
)
query.bindValue(0, self.productCode.text())
query.bindValue(1, self.productName.text())
query.bindValue(2, self.returnee.text())
query.bindValue(3, self.returnType.text())
query.bindValue(4, self.price.text())
query.bindValue(5, self.quantity.text())
query.bindValue(6, self.returnDate.text())
query.bindValue(7, self.reason.toPlainText())
query.bindValue(8, self.choose.currentText())
if query.exec_():
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Updated Successfully"),
QtWidgets.qApp.tr(
"\nInformation has been successfully updated"),
QtWidgets.QMessageBox.Ok)
else:
QtWidgets.QMessageBox.information(
None, QtWidgets.qApp.tr("Not Updated"),
QtWidgets.qApp.tr("Data update not successful"),
QtWidgets.QMessageBox.Ok)