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!")
