def checkbal(u):
con = db.getCon()
cur = con.cursor()
cur.execute("select bal from useraccount where username=%s", (u, ))
tup = cur.fetchone()
messagebox.showinfo('Check bal', f"Your Available Balance:{tup[0]}")
con.close()
def login(frm, e1, e2, cb):
u = e1.get()
p = e2.get()
ut = cb.get()
if (len(u) == 0 or len(p) == 0):
msg.showwarning('Validation Problem', 'Please fill all fields')
elif (ut == '--Select--'):
msg.showwarning('Validation Problem', 'Please select user type')
else:
if (ut == 'Admin'):
if (u == 'Admin' and p == 'Admin'):
msg.showinfo('Login', 'Welcome Admin')
frm.destroy()
welcomeAdmin()
else:
msg.showerror('Login Failed', 'Invalid username or password')
elif (ut == 'Salesman'):
con = db.getCon()
cur = con.cursor()
cur.execute("select * from salesman where s_id=%s and s_pass=%s",
(u, p))
row = cur.fetchone()
if (row == None):
msg.showerror('Login Failed', 'Invalid username or password')
else:
msg.showinfo('Login', f'Welcome:{row[2]}')
frm.destroy()
welcomeUser(row)
con.close()
def addSalDb(frm, e1, e2, e3, e4):
con = db.getCon()
cur = con.cursor()
cur.execute("select max(s_id) from salesman")
sid = cur.fetchone()
if (sid[0] != None):
newid = sid[0] + 1
else:
newid = 1
cur.execute("insert into salesman values(%s,%s,%s,%s,%s)",
(newid, e2.get(), e1.get(), e4.get(), e3.get()))
con.commit()
con.close()
msg.showinfo('New Account', f'Account Created with id:{newid}')
server = SMTP('smtp.gmail.com', 587)
server.starttls()
server.login('*****@*****.**', 'Ducat@2020')
msgg = EmailMessage()
list = [e3.get()]
msgg['TO'] = list
msgg['SUBJECT'] = 'New Account in Stock mgt'
msgg['FROM'] = '*****@*****.**'
msgg.set_content(
f'Hello Dear,\n Your Salesman id={newid} and Password={e2.get()}')
server.send_message(msgg)
print('mail gaya')
server.quit()
frm.destroy()
welcomeAdmin()
def validatenewuser(frm, eu, ep, ee, em, et, eb):
u = eu.get()
p = ep.get()
e = ee.get()
m = em.get()
b = eb.get()
t = et.get()
if (len(u) == 0 or len(p) == 0 or len(e) == 0 or len(m) == 0
or len(b) == 0):
messagebox.showwarning('Validation Failed', "Fields can't be empty")
return
else:
con = db.getCon()
cur = con.cursor()
cur.execute("select max(acno) from useraccount")
acno = cur.fetchone()[0]
acno = acno + 1
try:
cur.execute("insert into useraccount values(%s,%s,%s,%s,%s,%s,%s)",
(u, p, e, m, acno, b, t))
con.commit()
messagebox.showinfo('Success', f"Account opened with acno:{acno}")
homescreen(frm)
except Exception as e:
messagebox.showwarning('User name exists', str(e))
con.close()
def changePass(sid):
newpass = sd.askstring('Change Password', 'Enter new password:'******'update salesman set s_pass=%s where s_id=%s', (newpass, sid))
con.commit()
con.close()
msg.showinfo('Success', 'Password changed successfully')
def searchPro(frm):
p_name = sd.askstring('', 'Enter product name:')
con = db.getCon()
cur = con.cursor()
cur.execute('select * from product where p_name=%s', (p_name, ))
row = cur.fetchone()
if (row != None):
msg.showinfo('Product Found', str(row))
else:
msg.showwarning('', 'Product Not found')
con.close()
def viewSal(frm):
con = db.getCon()
cur = con.cursor()
cur.execute('select * from salesman')
msg1 = ''
for row in cur:
for i in row:
msg1 = msg1 + str(i) + '\t'
msg1 = msg1 + '\n'
s = cur.fetchall()
msg.showinfo('All Salesman', msg1)
con.close()
def delSal(frm):
sid = sd.askstring("Delete Account", "Enter Salesman id")
sid = int(sid)
con = db.getCon()
cur = con.cursor()
cur.execute("delete from salesman where s_id=%s", (sid, ))
con.commit()
if (cur.rowcount == 1):
msg.showinfo('Delete Account', 'Account Deleted')
else:
msg.showwarning('Delete Account', 'Account not found')
con.close()
def billDb(frm, cb, e):
p_n = cb.get()
u_qty = int(e.get())
con = db.getCon()
cur = con.cursor()
cur.execute('select p_qty from product where p_name=%s', (p_n, ))
db_qty = cur.fetchone()[0]
if (db_qty >= u_qty):
cur.execute('update product set p_qty=p_qty-%s where p_name=%s',
(u_qty, p_n))
con.commit()
msg.showinfo('', 'Billing done')
else:
msg.showwarning('', 'Insufficient quantity')
def txnhistory(u):
con = db.getCon()
cur = con.cursor()
cur.execute("select acno from useraccount where username=%s", (u, ))
acno = cur.fetchone()[0]
cur.execute(
"select txndate,amt,txntype,updatebal from txnhistory where acno=%s",
(acno, ))
history = cur.fetchall()
msg = "Date\t Time\t\tAmount\tType\tUpdatebal\n"
for row in history:
msg = msg + str(row[0]) + '\t ' + str(
row[1]) + '\t' + (row[2]) + '\t' + str(row[3]) + '\n'
messagebox.showinfo("Txn History", msg)
def updatepass(u):
np = simpledialog.askstring('Update Password', 'Enter New Password:'******'Update Password', 'Enter Confirm Password:'******'Update Password', "Password Changed Successfully")
else:
messagebox.showwarning('Update Password',
"New and Confirm Password do not match")
def addProDb(frm, e1, e2, e3):
con = db.getCon()
cur = con.cursor()
cur.execute("select max(p_id) from product")
sid = cur.fetchone()
if (sid[0] != None):
newid = sid[0] + 1
else:
newid = 1
cur.execute("insert into product values(%s,%s,%s,%s)",
(newid, e1.get(), e2.get(), e3.get()))
con.commit()
con.close()
msg.showinfo('New Product', f'Product Added with id:{newid}')
frm.destroy()
welcomeAdmin()
def recoverpass(frm, eu, ee, em):
u = eu.get()
e = ee.get()
m = em.get()
con = db.getCon()
cur = con.cursor()
cur.execute(
"select password from useraccount where username=%s and email=%s and mob=%s",
(u, e, m))
tup = cur.fetchone()
if (tup != None):
p = tup[0]
messagebox.showinfo('Password Recovery', f"Your Password is:{p}")
homescreen(frm)
else:
messagebox.showinfo('Password Recovery', "Invalid details")
def deposit(u):
amt = simpledialog.askinteger('Deposit', 'Enter Amount:')
con = db.getCon()
cur = con.cursor()
cur.execute("select bal,acno from useraccount where username=%s", (u, ))
tup = cur.fetchone()
avlbal = tup[0]
acno = tup[1]
dt = datetime.now()
if (amt >= 0):
cur.execute("update useraccount set bal=bal+%s where username=%s",
(amt, u))
con.commit()
messagebox.showinfo('Deposit', "Txn Done...")
cur.execute("insert into txnhistory values(%s,%s,%s,%s,%s)",
(acno, dt, amt, 'Credit', avlbal + amt))
con.commit()
con.close()
else:
messagebox.showinfo('Deposit', "Invalid amount")
def withdraw(u):
amt = simpledialog.askinteger('Withdraw', 'Enter Amount:')
con = db.getCon()
cur = con.cursor()
cur.execute("select bal,acno from useraccount where username=%s", (u, ))
tup = cur.fetchone()
avlbal = tup[0]
acno = tup[1]
dt = datetime.now()
if (avlbal >= amt):
cur.execute("update useraccount set bal=bal-%s where username=%s",
(amt, u))
con.commit()
messagebox.showinfo('Withdraw', "Txn Done...")
cur.execute("insert into txnhistory values(%s,%s,%s,%s,%s)",
(acno, dt, amt, 'Debit', avlbal - amt))
con.commit()
con.close()
else:
messagebox.showinfo('Withdraw', "Insufficient Bal")
def validatehomescreen(frm, eu, ep, et):
u = eu.get()
p = ep.get()
t = et.get()
if (len(u) == 0 or len(p) == 0):
messagebox.showwarning('Validation Failed',
"Username/Password can't be empty")
return
else:
con = db.getCon()
cur = con.cursor()
cur.execute(
"select * from useraccount where username=%s and password=%s",
(u, p))
tup = cur.fetchone()
if (tup != None):
if (t == 'User'):
messagebox.showinfo('Login Success', f"Welcome,{u}")
welcomeuser(frm, u)
else:
messagebox.showerror('Login Failed', "Invalid User type")
else:
messagebox.showerror('Login Failed', "Invalid Username/Password")
def billing(frm, row):
frm.destroy()
login_frm = Frame(root, bg=bgclr)
login_frm.place(x=0,
y=100,
width=root.winfo_width(),
height=root.winfo_height())
lbl_user = Label(login_frm,
bg=bgclr,
font=('', 15, ''),
fg='green',
text=f'Welcome:{row[2]}')
lbl_user.place(x=10, y=100)
back_btn = Button(login_frm,
width=10,
command=lambda: backUser(login_frm, row),
font=('', 12, 'bold'),
text='Back',
bd=5)
back_btn.place(relx=.03, y=150)
logout_btn = Button(login_frm,
width=15,
command=lambda: logout(login_frm),
font=('', 12, 'bold'),
text='Logout',
bd=5)
logout_btn.place(relx=.85, y=100)
lbl_name = Label(login_frm,
bg=bgclr,
font=('', 15, ''),
fg='blue',
text='Select Product')
lbl_name.place(x=300, y=200)
lbl_qty = Label(login_frm,
bg=bgclr,
font=('', 15, ''),
fg='blue',
text='Qty')
lbl_qty.place(x=300, y=250)
con = db.getCon()
cur = con.cursor()
cur.execute('select * from product')
product = []
for rowp in cur:
product.append(rowp[1])
con.close()
cb = ttk.Combobox(login_frm, values=product)
cb.current(0)
cb.place(x=490, y=205)
ent_qty = Entry(login_frm, bd=5, font=('', 15, 'bold'))
ent_qty.place(x=490, y=255)
sub_btn = Button(login_frm,
width=5,
text='Bill',
command=lambda: billDb(login_frm, cb, ent_qty),
bd=5,
font=('', 12, 'bold'))
sub_btn.place(x=520, y=405)
rst_btn = Button(
login_frm,
width=5,
command=lambda: reset(ent_name, ent_pass, None, ent_email, ent_pass),
text='reset',
bd=5,
font=('', 12, 'bold'))
rst_btn.place(x=600, y=405)
