def save_new_acc(self): self.db = db.Database().connect_db() self.cursor = self.db.cursor() if len(self.widget.fnameLE.text()) == 0: msg = 'Empty First Name Field' self.show_msg(msg) elif len(self.widget.lnameLE.text()) == 0: msg = 'Empty Last Name Field' self.show_msg(msg) elif len(self.widget.usernameLE.text()) < 5: msg = 'Short Username Field. It should be 5 characters or more' self.show_msg(msg) elif len(self.widget.mobileLE.text()) < 10: msg = 'Incomplete Mobile Number' self.show_msg(msg) elif len(self.widget.passLE.text()) < 5: msg = 'Short Password Field. It should be 5 characters or more' self.show_msg(msg) elif len(self.widget.confirmpassLE.text()) < 5: msg = 'Short username Field. It should be 5 characters or more' self.show_msg(msg) else: if self.widget.passLE.text() == self.widget.confirmpassLE.text(): fname = self.widget.fnameLE.text() lname = self.widget.lnameLE.text() uname = self.widget.usernameLE.text() if self.widget.genderCbx.currentIndex() == 0: g = 1 else: g = 0 mb = self.widget.mobileLE.text() p = self.widget.passLE.text() psw = db.PasswordActions().hash_password(p) exist = db.Database().check_if_exists('user', 'username', uname) if exist == True: msg = 'Another user exists with a similar username\nChoose a different username or login if you \nhave an account' self.show_msg(msg) else: sql = """INSERT INTO "user" (firstname,lastname,username,gender,mobile_no,password,admin) VALUES \ ('{}','{}','{}','{}','{}','{}',0)""".format( fname, lname, uname, g, mb, psw) try: self.cursor.execute(sql) self.db.commit() self.show_msg("Account Details Saved Successfully") self.close() except Exception as a: self.show_msg("Error saving user ->" + str(a)) else: msg = 'The two password fields do not match' self.show_msg(msg)
def change_password(self): self.db = db.Database().connect_db() self.cursor = self.db.cursor() un = self.widget.lineEdit_username.text() np = self.widget.lineEdit_new_pass.text() op = self.widget.lineEdit_old_password.text() np_ = self.widget.lineEdit_newpass_2.text() if len(un) > 4 and len(op) > 4 and len(np) > 4 and len(np_) > 4: ret1 = db.Database().check_if_exists("user", "username", un) if ret1 == True: if np == np_: ret2 = db.Login().login(un, op) if ret2 == True: psw = db.PasswordActions().hash_password(np) sql = """ UPDATE "user" SET password = '******' WHERE username = '******'""".format( psw, un) try: self.cursor.execute(sql) self.db.commit() print(">> Password Changed Successfully") self.message(">> Password Changed Successfully", 0) self.close() except Exception as a: txt = ">> Error changing password :: " + str(a) self.message(txt, 1) else: txt = "Wrong login details" self.message(txt, 1) else: txt = "Two New passwords do not match!" self.message(txt, 1) else: txt = "Unknown User!" self.message(txt, 1) else: pr = "One of the required field is empty or short!" self.message(pr, 1) self.db.close()
def auto_complete(self): # load completer self.cursor = db.Database().connect_db().cursor() self.ls.clear() sql_load = """SELECT cp_mobile_no, cp_firstname, cp_lastname FROM "credit_person" """ try: self.cursor.execute(sql_load) ret = self.cursor.fetchall() for row in ret: mble = row[0] fname = row[1] lname = row[2] # make string to display in the list widget word = "{} {} : +{}".format(fname, lname, mble) self.ls.append(word) compl = QtWidgets.QCompleter(self.ls) compl.setCaseSensitivity(QtCore.Qt.CaseInsensitive) compl.setCompletionMode(QtWidgets.QCompleter.PopupCompletion) self.widget.crediteenameLineEdit.setCompleter(compl) except Exception as a: print('Error Adding to list : ==>', a)
def __init__(self, table, totals, daddy, user): """ """ QtWidgets.QDialog.__init__(self) self.table = table self.daddy = daddy self.user = user self.widget = uic.loadUi("msale/forms/paymentdialog.ui", self) self.widget.totalsLbl.setText(str(totals)) self.setWindowIcon(QtGui.QIcon(":/icons/icon.png")) # Setup Icons self.widget.comboBox.currentIndexChanged.connect(self.options) self.widget.stackedWidget.setCurrentIndex(0) self.widget.transactBtn.clicked.connect(self.complete_transaction) self.widget.newcrediteeBtn.clicked.connect(self.open_newcreditee) self.db = db.Database().connect_db() self.cursor = self.db.cursor() self.ls = [] self.auto_complete() self.sum = totals self.widget.chequeDateEdit.setMinimumDate( QtCore.QDate(QtCore.QDate.currentDate())) self.widget.creditduedateDateEdit.setMinimumDate( QtCore.QDate(QtCore.QDate.currentDate()))
def add_item_to_table(self): name = self.widget.searchLineEdit.text() if len(name) == 0: print('Empty Search Input Field!') else: a = db.Database().check_if_exists('item','item_name',name) if a == True: sql = """SELECT item.item_name,stock_qty FROM "item" INNER JOIN \ "stock" ON item.item_name = stock.item_name WHERE item.item_name = '{}'""".format(name) try: self.cursor.execute(sql) ret = self.cursor.fetchall() for x in ret: name = x[0] qty = x[1] self.widget.tableWidget.setRowCount(0) rows = self.widget.tableWidget.rowCount() self.widget.tableWidget.insertRow(rows) self.widget.tableWidget.setItem(rows,0,QtWidgets.QTableWidgetItem(name)) self.widget.tableWidget.setItem(rows,1,QtWidgets.QTableWidgetItem(str(qty))) except Exception as aa: print("XOXOX :: ",aa) else: print('Item Doesn\'t Exist')
def filter_input(self): if len(self.widget.filterLineEdit.text()) == 0: self.load_all_items() else: self.mydb = db.Database().connect_db() self.cursor = self.mydb.cursor() self.widget.tableWidget.setRowCount(0) searchText = self.widget.filterLineEdit.text() sql = """SELECT item.item_name, item_sp FROM "item"\ INNER JOIN "stock" ON "item".item_name = "stock".item_name WHERE item.item_name ILIKE '%{}%'""".format( searchText) try: self.cursor.execute(sql) ret = self.cursor.fetchall() g = 0 r = len(ret) self.widget.tableWidget.setRowCount(r) for row in ret: name = row[0] sp = row[1] self.widget.tableWidget.setItem( g, 0, QtWidgets.QTableWidgetItem(name)) self.widget.tableWidget.setItem( g, 1, QtWidgets.QTableWidgetItem(str(sp))) g += 1 except Exception as aa: print('>> Error at All Items Dialog :: ', str(aa))
def check_notifications(self): # Checks for any pending notifications or reminders due today(Current Date) self.cursor = db.Database().connect_db().cursor() a = pendulum.now() tm = "{}-{}-{}".format(a.year,a.month,a.day) sql = """ SELECT count(credit_person.cp_mobile_no) from "credit_person"\ INNER JOIN "debt" ON "credit_person".cp_mobile_no = "debt".cp_mobile_no WHERE debt_due = '{}'""".format(tm) sql1 = """SELECT count(date) FROM "on_cheque" WHERE cheque_maturity_date = '{}'""".format(tm) try: self.cursor.execute(sql) ret = self.cursor.fetchone() self.cursor.execute(sql1) ret1 = self.cursor.fetchone() if (ret[0] > 0)or(ret1[0] > 0): self.widget.notificationBtn.setIcon(QtGui.QIcon(":/icons/notify_red.png")) else: self.widget.notificationBtn.setIcon(QtGui.QIcon(":/icons/notify_white.png")) except Exception as e: print(e)
def add_item(self): name = self.widget.searchLineEdit.text() if len(name) == 0: print('Empty Search Input Field!') else: a = db.Database().check_if_exists('item', 'item_name', name) if a == True: sql = """SELECT item_bp,item_sp,item_company \ FROM "item" WHERE item_name = '{}'""".format(name) try: self.cursor.execute(sql) ret = self.cursor.fetchone() bp = ret[0] sp = ret[1] company = ret[2] self.widget.companyLineEdit.setText(company) self.widget.spLineEdit.setText(str(sp)) self.widget.bpLineEdit.setText(str(bp)) self.widget.itemNameLineEdit.setText(name) except Exception as aa: print("XOXOX :: ", aa) else: print('Item Doesn\'t Exist')
def load_accounts(self): # Loads all accounts in the db sql_user = """SELECT username, firstname, lastname, mobile_no, admin FROM "user" """ cursor = db.Database().connect_db().cursor() try: cursor.execute(sql_user) ret2 = cursor.fetchall() self.widget.userTbl.setRowCount(0) rows = len(ret2)-2 self.widget.userTbl.setRowCount(rows) i = 0 for x in range(len(ret2)): uname = ret2[x][0] name = f"{ret2[x][1]} {ret2[x][2]}" no =ret2[x][3] admin = ret2[x][4] if admin == 3: continue else: tile = UserForm(uname,name,no,admin,self,self.user,self.daddy) self.widget.userTbl.setCellWidget(i,0,tile) i= i+1 except Exception as e: print(e)
def load_creditees(self): # open database read all creditess and add them to the table sql_load = """SELECT cp_mobile_no, cp_firstname, cp_lastname FROM "credit_person" """ self.db = db.Database().connect_db() self.cursor = self.db.cursor() try: self.cursor.execute(sql_load) ret = self.cursor.fetchall() for row in ret: mble = row[0] fname = row[1] lname = row[2] # make string to display in the list widget name = "{} {}".format(fname, lname) widgt = CrediteeWidget(self, name, mble) x = QtWidgets.QListWidgetItem() x.setSizeHint(QtCore.QSize(x.sizeHint().width(), 62)) self.widget.listWidget_all_creditees.addItem(x) self.widget.listWidget_all_creditees.setItemWidget(x, widgt) except Exception as a: print('Error Adding to list', a)
def add_stock(self): if self.widget.tableWidget.rowCount() != 0: qty = self.widget.spinBox.value() self.db = db.Database().connect_db() self.cursor = self.db.cursor() name = self.widget.tableWidget.item(0,0).text() querry = """SELECT stock_qty,item_name FROM "stock" WHERE item_name = '{}'""".format(name) try: a = self.cursor.execute(querry) n = self.cursor.fetchone() new_qty = n[0] + int(qty) s = pendulum.now() d_day = "{}-{}-{}".format(s.year,s.month,s.day) sql = """Update "stock" set stock_qty = '{}', stock_last_update = '{}' WHERE\ item_name = '{}'""".format(new_qty,d_day,name) self.cursor.execute(sql) self.db.commit() self.widget.tableWidget.setItem(0,1,QtWidgets.QTableWidgetItem(str(new_qty))) self.widget.spinBox.setValue(1) except Exception as a: print('Error updating stock -> '+str(a)) #self.widget.spinBox.setValue(1) self.setAutoComplete() self.daddy.reload_stock() else: print("No Item Selected!")
def delete_item(self): self.db = db.Database().connect_db() self.cursor = self.db.cursor() sender = self.sender() index = self.table.indexAt(sender.parent().pos()) row = index.row() name = self.table.item(row, 0).text() sql = """DELETE from "stock" WHERE item_name = '{}'""".format(name) sql1 = """DELETE from "item" WHERE item_name = '{}'""".format(name) try: self.cursor.execute(sql) self.cursor.execute(sql1) if self.user[2] == 3: self.db.commit() self.message('Successfully Deleted the item', '') self.daddy.filter_input() self.daddy.close() else: self.db.rollback() self.message( 'Failed to delete item, this action can be done only by Database Administrators, \nContact your administrator for assistance.', 'Error') self.daddy.close() except Exception as a: m = 'Could not complete delete operation\n' + str(a) self.message(m, 'Error!') self.db.close()
def load_debt_paid(self, mobile): # load debt data and payments done ''' "debt_pay": debt_pay_id SERIAL, cp_mobile_no BIGINT, debt_pay_amount REAL, debt_pay_date DATE, debt_balance REAL "debt": debt_id SERIAL, cp_mobile_no BIGINT, debt_amount REAL, debt_day DATE, debt_due DATE, debt_balance REAL ''' self.db = db.Database().connect_db() self.cursor = self.db.cursor() sql_debt = """SELECT debt_amount, debt_day, debt_due,debt_balance FROM "debt" \ WHERE cp_mobile_no = '{}' """.format(mobile) sql_pay = """SELECT debt_pay_amount, debt_pay_date, debt_balance FROM "debt_pay" WHERE \ cp_mobile_no = '{}' """.format(mobile) self.tableWidget_pay_debt_details_3.setRowCount(0) self.widget.tableWidget_pay_debt_details_2.setRowCount(0) try: self.cursor.execute(sql_debt) ret = self.cursor.fetchall() self.cursor.execute(sql_pay) ret1 = self.cursor.fetchall() # debt table for row in ret: d_amnt = row[0] d_day = row[1] d_due = row[2] d_bal = row[3] r = self.tableWidget_pay_debt_details_3.rowCount() self.tableWidget_pay_debt_details_3.insertRow(r) self.tableWidget_pay_debt_details_3.setItem( r, 0, QtWidgets.QTableWidgetItem(str(d_bal))) self.tableWidget_pay_debt_details_3.setItem( r, 1, QtWidgets.QTableWidgetItem(str(d_amnt))) self.tableWidget_pay_debt_details_3.setItem( r, 2, QtWidgets.QTableWidgetItem(str(d_day))) self.tableWidget_pay_debt_details_3.setItem( r, 3, QtWidgets.QTableWidgetItem(str(d_due))) # payments table for row in ret1: dp_amnt = row[0] dp_day = row[1] dp_bal = row[2] s = self.widget.tableWidget_pay_debt_details_2.rowCount() self.widget.tableWidget_pay_debt_details_2.insertRow(s) self.widget.tableWidget_pay_debt_details_2.setItem( s, 0, QtWidgets.QTableWidgetItem(str(dp_bal))) self.widget.tableWidget_pay_debt_details_2.setItem( s, 1, QtWidgets.QTableWidgetItem(str(dp_amnt))) self.widget.tableWidget_pay_debt_details_2.setItem( s, 2, QtWidgets.QTableWidgetItem(str(dp_day))) except Exception as a: print(a)
def notify(self): # Sets up the notification badges, sets RED and badge counter if any # for both debts due Current day and cheque reminders self.cursor = db.Database().connect_db().cursor() a = pendulum.now() tm = "{}-{}-{}".format(a.year, a.month, a.day) sql = """ SELECT count(credit_person.cp_mobile_no) from "credit_person"\ INNER JOIN "debt" ON "credit_person".cp_mobile_no = "debt".cp_mobile_no WHERE debt_due = '{}'""".format( tm) sql1 = """SELECT count(date) FROM "on_cheque" WHERE cheque_maturity_date = '{}'""".format( tm) try: self.cursor.execute(sql) ret = self.cursor.fetchone() self.widget.debtRemindersBadgeLabel.setText(str(ret[0])) if ret[0] > 0: self.widget.debtRemindersBadgeLabel.setStyleSheet( "background-color:#df0000;border-radius:20px;") self.father.check_notifications() self.cursor.execute(sql1) ret1 = self.cursor.fetchone() self.widget.chequeRemindersBadgeLabel.setText(str(ret1[0])) if ret1[0] > 0: self.widget.chequeRemindersBadgeLabel.setStyleSheet( "background-color:#df0000;border-radius:20px;") self.father.check_notifications() except Exception as _a: print(_a)
def load_stock(self): self.mydb = db.Database().connect_db() self.cursor = self.mydb.cursor() self.widget.stockTableWidget.setRowCount(0) sql_stock = """SELECT item.item_name, stock_qty, stock_last_update FROM "item"\ INNER JOIN "stock" ON item.item_name=stock.item_name ORDER BY item.item_name DESC""" try: self.cursor.execute(sql_stock) ret = self.cursor.fetchall() g = 0 r = len(ret) self.widget.stockTableWidget.setRowCount(r) for row in ret: name = row[0] qty = row[1] date = row[2] self.widget.stockTableWidget.setItem( g, 0, QtWidgets.QTableWidgetItem(name)) self.widget.stockTableWidget.setItem( g, 1, QtWidgets.QTableWidgetItem(str(qty))) self.widget.stockTableWidget.setItem( g, 2, QtWidgets.QTableWidgetItem(str(date))) g += 1 except Exception as aa: print('>> Error at stock :: ', str(aa))
def load_all_items(self): self.mydb = db.Database().connect_db() self.cursor = self.mydb.cursor() self.widget.tableWidget.setRowCount(0) sql = """SELECT item.item_name, item_sp FROM "item"\ INNER JOIN "stock" ON "item".item_name = "stock".item_name""" try: self.cursor.execute(sql) ret = self.cursor.fetchall() g = 0 r = len(ret) self.widget.tableWidget.setRowCount(r) for row in ret: name = row[0] sp = row[1] self.widget.tableWidget.setItem( g, 0, QtWidgets.QTableWidgetItem(name)) self.widget.tableWidget.setItem( g, 1, QtWidgets.QTableWidgetItem(str(sp))) g += 1 except Exception as aa: print('>> Error at All Items Window :: ', str(aa))
def __init__(self): QtWidgets.QWidget.__init__(self) """ Credits And Debts Window for the MS Point of sale software //Initialize dynamically the ui //set the icons not loaded dynamically by the UiLoader //Setup slot functions for the signals sent by the dynamic Ui """ QtWidgets.QWidget.__init__(self) self.widget = uic.loadUi("msale/forms/creditsform.ui", self) self.db = db.Database().connect_db() self.cursor = self.db.cursor() self.widget.viewcredititemsBtn.clicked.connect( self.open_view_details) # on_clicked self.widget.pushButton_credit_pay_update.clicked.connect( self.update_account_amount_paid) # on_clicked self.widget.pushButton_credits_back.clicked.connect( self.go_back_to_tab1) self.widget.pushButton_credits_new_creditee.clicked.connect( self.open_new_creditee) # on_clicked self.load_creditees() self.widget.stackedWidget.setCurrentIndex(0)
def add_new_item(self): i_name = self.widget.itemNameineEdit.text() i_bp = self.widget.bpLineEdit.text() i_sp = self.widget.splineEdit.text() i_qty = self.widget.stockLineEdit.text() i_company = self.widget.companyLineEdit.text() if len(i_name) == 0 or len(i_bp) == 0 or len(i_sp) == 0 or len( i_qty) == 0: print('One of required field is empty') else: b = db.Database().check_if_exists('item', 'item_name', i_name) self.mydb = db.Database().connect_db() self.cursor = self.mydb.cursor() if b == True: print( 'An Item With a similar name already Exists in the database' ) else: a = pendulum.now() i_date = '{}-{}-{}'.format(a.year, a.month, a.day) sql = ''' INSERT INTO "item" (item_name,item_bp,item_sp,item_company)\ VALUES ('{}','{}','{}','{}')'''.format( i_name, float(i_bp), float(i_sp), i_company) sql1 = '''INSERT INTO "stock" (item_name,stock_qty,stock_last_update) \ VALUES ('{}','{}','{}')'''.format(i_name, int(i_qty), i_date) try: self.cursor.execute(sql) try: self.cursor.execute(sql1) self.mydb.commit() self.display_added_to_table(i_name, i_bp, i_sp, i_qty, i_company) self.clear_fields() except Exception as b: self.mydb.rollback() print('Error2 : Changes Rollback\n' + str(b)) except Exception as b1: self.mydb.rollback() print('Error1 : Changes Rollback\n' + str(b1))
def update_acc(self): self.db = db.Database().connect_db() self.cursor = self.db.cursor() if len(self.widget.fnameLE.text()) == 0: msg = 'Empty First Name Field' self.show_msg(msg) elif len(self.widget.lnameLE.text()) == 0: msg = 'Empty Last Name Field' self.show_msg(msg) elif len(self.widget.usernameLE.text()) < 5: msg = 'Short Username Field. It should be 5 characters or more' self.show_msg(msg) elif len(self.widget.mobileLE.text()) < 10: msg = 'Incomplete Mobile Number' self.show_msg(msg) else: fname = self.widget.fnameLE.text() lname = self.widget.lnameLE.text() uname = self.widget.usernameLE.text() if self.widget.genderCbx.currentIndex() == 0: g = 1 else: g = 0 mb = self.widget.mobileLE.text() # TODO authenticate before save sql = '''UPDATE "user" SET firstname = '{}',lastname = '{}',username = '******',gender = '{}'\ ,mobile_no = '{}' WHERE username = '******' '''.format( fname, lname, uname, g, mb, self.username) sql2 = '''UPDATE "orders" SET served_by = '{}' WHERE served_by = '{}' '''.format( uname, self.username) try: self.cursor.execute(sql) self.cursor.execute(sql2) _m = AuthenticationDialog(self) if self.user[0] == self.username: _m.setUser(self.user[0]) _m.exec() status = _m.returnStatus() if status == True: self.db.commit() self.close() self.show_msg("Account Details Updated ...") if self.user[0] == self.username: self.updateUser(uname) else: self.db.rollback() self.close() self.show_msg("The changes were not saved ...") except Exception as a: self.show_msg("Error Updating Account ->" + str(a))
def load_items(self): self.widget.listWidget.clear() ind = self.widget.comboBox.currentIndex() if ind == 0: by = 'cash' elif ind == 1: by = 'mpesa' else: by = 'cashmpesa' dt = self.widget.dateEdit.date() dte = '{}-{}-{}'.format(dt.year(),dt.month(),dt.day()) sql_load = """SELECT timestamp_, order_qty FROM "orders" WHERE payment_by = '{}' AND order_date = '{}' AND\ item_name = '{}'""".format(by,dte,self.widget.itemnameLE.text()) sql_ = """SELECT count(timestamp_) FROM "orders" WHERE payment_by = '{}' AND order_date = '{}' AND item_name =\ '{}'""".format(by,dte,self.widget.itemnameLE.text()) self.db = db.Database().connect_db() self.cursor = self.db.cursor() try: self.cursor.execute(sql_) ret = self.cursor.fetchone() if ret[0] != 0: self.cursor.execute(sql_load) ret = self.cursor.fetchall() for row in ret: tme = row[0] qty = row[1] if tme.hour < 10: hr = '0{}'.format(tme.hour) else: hr = '{}'.format(tme.hour) if tme.minute < 10: min = '0{}'.format(tme.minute) else: min = '{}'.format(tme.minute) time = hr+':'+min # make string to display in the list widget widgt = SaleWidget(self,time,qty,tme,self.widget.itemnameLE.text()) x = QtWidgets.QListWidgetItem() x.setSizeHint(QtCore.QSize(x.sizeHint().width(),62)) self.widget.listWidget.addItem(x) self.widget.listWidget.setItemWidget(x,widgt) else: self.message('Empty set ... ',"No item found that satisfies the given parameters") except Exception as a: print('Error Adding to list',a)
def update_creditee(self): fname = self.widget.firstnameLE.text() lname = self.widget.lastnameLE.text() mobile_ = self.widget.phonenoLE.text() sql = """ UPDATE "credit_person" SET cp_firstname='{}',\ cp_lastname = '{}' WHERE cp_mobile_no='{}'""".format(fname,lname,self.mobile) sql1 = """ UPDATE "credit_person" SET cp_mobile_no='{}',cp_firstname='{}',\ cp_lastname = '{}' WHERE cp_mobile_no='{}'""".format(mobile_,fname,lname,self.mobile) sql2 = """UPDATE "on_credit" SET cp_mobile_no='{}' WHERE \ cp_mobile_no='{}'""".format(mobile_,self.mobile) sql3 = """UPDATE "debt_pay" SET cp_mobile_no='{}' WHERE \ cp_mobile_no='{}'""".format(mobile_,self.mobile) sql4 = """UPDATE "debt" SET cp_mobile_no = '{}' WHERE \ cp_mobile_no='{}'""".format(mobile_,self.mobile) self.db = db.Database().connect_db() self.cursor = self.db.cursor() if len(fname) != 0 and len(lname) != 0 and len(str(mobile_)) > 8: check = """SELECT count(cp_firstname) FROM "credit_person" \ WHERE cp_mobile_no = '{}' AND cp_mobile_no != '{}' """.format(mobile_,self.mobile) try: if self.mobile != mobile_: self.cursor.execute(check) ret = self.cursor.fetchone() if len(ret) == 0: x_ = 0 else: x_ = ret[0] if x_ == 0: self.cursor.execute(sql1) self.cursor.execute(sql2) self.cursor.execute(sql3) self.cursor.execute(sql4) self.db.commit() self.show_msg("Updated ...") self.close() else: txt = "The New Phone Number already exists" self.show_err(txt) else: self.cursor.execute(sql) self.db.commit() self.show_msg("Updated ...") self.close() except Exception as a: txt = "Error saving the user ::\n{}".format(str(a)) self.show_err(txt) else: self.show_err("One of the name field is empty or the mobile number is short!")
def login_account(self): user_n = self.widget.usernameInput.text() pass_w = self.widget.passwordInput.text() if len(user_n) < 5: msg = 'Short Username Entered!\n Usernames are 5 characters or more in length' self.show_msg(msg) elif len(pass_w) < 5: msg = 'Short Password Entered!\n Passwords are 5 characters or more in length' self.show_msg(msg) else: b = db.Database().check_if_exists('user', 'username', user_n) if b == False: msg = 'Invalid Login Details' self.show_msg(msg) else: val = self.validate_(user_n) if val == True: a = db.Login().login(user_n, pass_w) if a == True: sql_login = """SELECT firstname,lastname,admin FROM "user" WHERE username = '******' """.format( user_n) a = db.Database().connect_db() b = a.cursor() try: b.execute(sql_login) ret = b.fetchone() self.x.append(user_n) self.x.append(ret[0] + " " + ret[1]) self.x.append(ret[2]) self.login_into() except Exception as a_: print("Error at Login ::", a_) else: msg = 'Invalid Login Details' self.show_msg(msg) else: msg = 'Invalid Character In The Username Field\nOnly letters, numbers, @, \nfullstop and underscore(_) are allowed' self.show_msg(msg)
def save_new_creditee(self): # function to retrieve inputs, verify # and save to database # credit_person: cp_mobile_no, cp_firstname,cp_lastname,cp_balance self.db = db.Database().connect_db() self.cursor = self.db.cursor() fname = self.widget.firstnameLE.text() lname = self.widget.lastnameLE.text() mobile_ = self.widget.phonenoLE.text() if len(fname) != 0 and len(lname) != 0: mobile = int("{}".format(mobile_)) if len(str(mobile)) > 8: sql_creditee = """INSERT INTO "credit_person"(cp_mobile_no, cp_firstname,cp_lastname,cp_balance)\ VALUES('{}','{}','{}','{}')""".format( mobile, fname, lname, 0) ret = db.Database().check_if_exists('credit_person', 'cp_mobile_no', mobile) if ret == False: try: self.cursor.execute(sql_creditee) self.db.commit() txt = "New creditee account saved successfully!" self.daddy.auto_complete() self.close() except Exception as a: txt = "Error saving the user ::\n{}".format(str(a)) print(txt) else: print("Another user exists with the same mobile number!") else: print("Mobile number entered is short!") else: print("One of the name field is empty!")
def notify(self): self.cursor = db.Database().connect_db().cursor() a = pendulum.now() tm = "{}-{}-{}".format(a.year, a.month, a.day) sql = """ SELECT credit_person.cp_mobile_no,cp_firstname, cp_lastname,debt_balance from "credit_person"\ INNER JOIN "debt" ON "credit_person".cp_mobile_no = "debt".cp_mobile_no WHERE debt_due = '{}'""".format( tm) sql1 = """SELECT date,cheque_amount FROM "on_cheque" WHERE cheque_maturity_date = '{}'""".format( tm) try: flag = 0 self.cursor.execute(sql) ret = self.cursor.fetchall() for row in ret: flag = 1 self.get = True self.widget.groupBox_no.hide() self.widget.groupBox_debts.show() self.widget.groupBox_cheque.hide() a = row[0] b = row[1] c = row[2] d = row[3] txt = "{} {} (0{}) Amount : {}Ksh".format(b, c, a, d) self.widget.listView_debts.addItem(txt) self.cursor.execute(sql1) ret1 = self.cursor.fetchall() for row in ret1: self.get = True if flag == 1: self.widget.groupBox_debts.show() self.widget.groupBox_cheque.show() self.setMinimumWidth(460) else: self.widget.groupBox_debts.hide() self.widget.groupBox_no.hide() self.setMinimumWidth(460) a = row[0] b = row[1] txt = "Cheque date :{}, Cheque Amount : {}Ksh".format(a, b) self.widget.listView_cheque.addItem(txt) except Exception as _a: print(_a)
def __init__(self, daddy, name, mobile): QtWidgets.QWidget.__init__(self) self.daddy = daddy self.widget = uic.loadUi("msale/forms/crediteewidget.ui", self) self.widget.nameLbl.setText(name) self.widget.mobileBtn.setText("0" + str(mobile)) self.db = db.Database().connect_db() self.cursor = self.db.cursor() self.widget.opendetailsBtn.clicked.connect(self.openDetails)
def hide_createuser(self): sql = '''SELECT count(username) FROM "user"''' b = db.Database().connect_db().cursor() try: b.execute(sql) ret = b.fetchone() if ret[0] >= 3 : self.widget.createaccBtn.hide() except Exception as e: print(e)
def load_table_data(self, sql): sql_bal = """SELECT cp_balance FROM "credit_person" WHERE cp_mobile_no = '{}'""".format( self.no) self.cursor = db.Database().connect_db().cursor() try: self.cursor.execute(sql) ret = self.cursor.fetchall() self.cursor.execute(sql_bal) bal = self.cursor.fetchone() _totals = 0.0 for row in ret: _name = row[0] _qty = row[1] _sp = row[2] _date = row[3] _tm_stamp = row[4] _totals += _qty * _sp _rows = self.widget.tableWidget_debt_items.rowCount() self.widget.tableWidget_debt_items.insertRow(_rows) self.widget.tableWidget_debt_items.setItem( _rows, 0, QtWidgets.QTableWidgetItem(_name)) self.widget.tableWidget_debt_items.setItem( _rows, 1, QtWidgets.QTableWidgetItem(str(_sp))) self.widget.tableWidget_debt_items.setItem( _rows, 2, QtWidgets.QTableWidgetItem(str(_qty))) self.widget.tableWidget_debt_items.setItem( _rows, 3, QtWidgets.QTableWidgetItem(str(_date))) self.widget.tableWidget_debt_items.setItem( _rows, 4, QtWidgets.QTableWidgetItem(str(_qty * _sp))) self.widget.tableWidget_debt_items.setItem( _rows, 5, QtWidgets.QTableWidgetItem(str(_tm_stamp))) if len(ret) != 0: _bal = bal[0] _paid = _totals - _bal else: _bal = 0.0 _paid = 0.0 self.widget.label_totalCost.setText(str(_totals)) self.widget.label_amnt_paid.setText(str(_paid)) self.widget.label_amnt_due.setText(str(_bal)) except Exception as a: print(a)
def edit_added_row(self): # function to edit quantity added to the table # get quantity from database of the row item and compare it to entered quantity self.mydb = db.Database().connect_db() self.cursor = self.mydb.cursor() sender = self.sender() row = self.table.indexAt(sender.parent().pos()).row() c_qty = self.table.item(row, 2).text() cost = float(self.table.item(row, 1).text()) x = QtWidgets.QInputDialog() x.setStyleSheet("QLabel{},QPushButton{border:1px solid grey;},\ QSpinBox{min-height:35px;}") i, okPressed = x.getInt(self, "Enter New Quantity", "Quantity", int(c_qty), 1, 1000) if okPressed: sql_qty = """SELECT stock_qty FROM "stock" WHERE item_name = '{}'""".format( self.table.item(row, 0).text()) try: self.cursor.execute(sql_qty) ret = self.cursor.fetchall() _qty = ret[0][0] if len(ret) != 0: __qty = 0 _item_name = self.table.item(row, 0).text() for x in range(self.table.rowCount()): if _item_name == self.table.item(x, 0).text(): __qty += int(self.table.item(x, 2).text()) if _qty >= (i + __qty): self.table.setItem(row, 2, QtWidgets.QTableWidgetItem(str(i))) self.table.setItem( row, 4, QtWidgets.QTableWidgetItem(str(i * cost))) self.daddy.reload_totals() else: print( "Operation Cancelled!\nQuantity to be added exceeds item's stock quantity" ) else: print( "Fetching stock quantity of the item failed\n Check if item's stock details exist." ) except Exception as e1: print(str(e1))
def load_sales_table(self, id, dte1, dte2): # load all sold data to sales table self.widget.tableWidget_sales.setRowCount(0) if id == 2: sql = """SELECT item_name,sum(order_qty),item_bp,item_sp FROM "orders" WHERE order_date \ BETWEEN '{}' AND '{}' GROUP BY item_name,item_bp,item_sp""".format( dte1, dte2) else: sql = """SELECT item_name,sum(order_qty),item_bp,item_sp FROM "orders" WHERE order_date ='{}' \ GROUP BY item_name,item_bp,item_sp""".format(dte1) try: self.widget.tableWidget_sales.setSortingEnabled(False) self.cursor = db.Database().connect_db().cursor() self.cursor.execute(sql) ret = self.cursor.fetchall() for row in ret: name = row[0] qty = row[1] #bp = row[2] sp = row[3] #u_profit = sp - bp t_cost = qty * sp #t_profit = qty * u_profit rowAt = self.tableWidget_sales.rowCount() self.widget.tableWidget_sales.insertRow(rowAt) self.widget.tableWidget_sales.setItem( rowAt, 0, QtWidgets.QTableWidgetItem(name)) self.widget.tableWidget_sales.setItem( rowAt, 2, QtWidgets.QTableWidgetItem(str(qty))) self.widget.tableWidget_sales.setItem( rowAt, 1, QtWidgets.QTableWidgetItem(str(sp))) #self.widget.tableWidget_sales.setItem(rowAt,3,QtWidgets.QTableWidgetItem(str(u_profit))) self.widget.tableWidget_sales.setItem( rowAt, 3, QtWidgets.QTableWidgetItem(str(t_cost))) #self.widget.tableWidget_sales.setItem(rowAt,5,QtWidgets.QTableWidgetItem(str(t_profit))) self.widget.tableWidget_sales.setSortingEnabled(True) try: self.get_totals(id, dte1, dte2) except Exception as abb: txt = "Error [1E5] \n" + str(abb) self.show_msg(txt) except Exception as aa: txt = "Error [1E6] \n" + str(aa) self.show_msg(txt)
def tear_stock(self): if self.widget.tableWidget.rowCount() != 0: qty = self.widget.spinBox.value() self.db = db.Database().connect_db() self.cursor = self.db.cursor() name = self.widget.tableWidget.item(0,0).text() querry = """SELECT stock_qty,item_name FROM "stock" WHERE item_name = '{}'""".format(name) try: a = self.cursor.execute(querry) n = self.cursor.fetchone() if int(qty) > n[0]: print("Quantity Availabele Less Than Quantity to Remove!") else: new_qty = n[0] - int(qty) s = pendulum.now() d_day = "{}-{}-{}".format(s.year,s.month,s.day) sql = """Update "stock" set stock_qty = '{}', stock_last_update = '{}' WHERE\ item_name = '{}'""".format(new_qty,d_day,name) self.cursor.execute(sql) if self.user[2] == 3: self.db.commit() self.widget.tableWidget.setItem(0,1,QtWidgets.QTableWidgetItem(str(new_qty))) self.widget.spinBox.setValue(1) self.close() else: self.show_msg("Could not complete your request, login using a Database Administrator\n\ account to do this action or contact the system Administrator!") self.db.rollback() self.widget.spinBox.setValue(1) self.close() except Exception as a: print('Error updating stock -> '+str(a)) self.setAutoComplete() self.daddy.reload_stock() else: print("No Item Selected!")