def removeSale(ea,buton):
table=ea[0].get()
subtable=ea[1].get()
quantityValue=ea[2].get()
price=Fetch.getPrice(str(table), str(subtable),cursor,"salesprice")
categoryList.append(table)
nameList.append(subtable)
cursor.execute("USE archa")
quantity=Fetch.getQuantity(str(table),str(subtable),cursor)
qtyList.append(quantityValue)
cursor.execute("USE archa")
attDict=dict()
conditionDict=dict()
attDict={'qty':str(quantity[0]+int(quantityValue))}
conditionDict={'Name':str(subtable)}
DbUtils.updateDB(str(table),attDict,conditionDict,cursor)
priceFinal=int(price*int(quantityValue))
w.config(text=str(int(w.cget("text"))-priceFinal)) #update the price in the totalsale as subtract whatever is removed
qtyArray=[]
for i in range(int(quantity[0]+int(quantityValue))): #update the qty availalbein the quantity box again as available qty+whatever was removed
qtyArray.append(i+1)
i+=1
qtyTable['values']=qtyArray
#updateSaleOut(table,subtable,quantity,price)
for i in range(len(ea)):
ea[i].destroy()
buton.destroy()
def putPrice(
event
): #this function populated price if trying to add existing stock
price = Fetch.getPrice(str(categoty.get()), str(nameBox.get()),
cursor, "salesprice")
priceText.delete(1.0, "end")
priceText.insert(1.0, str(price))
price = Fetch.getPrice(str(categoty.get()), str(nameBox.get()),
cursor, "costprice")
costprice.delete(1.0, "end")
costprice.insert(1.0, str(price))
def updateSaleOut(table,subtable,quantity):
cursor.execute("USE archa")
formatted_date = Fetch.getFormattedDate()
for i in range (len(table)):
querry="delete from saleout where category='"+str(table[i])+"' AND Name='"+str(subtable[i])+"' AND qty='"+str(quantity[i])+"' AND date='"+formatted_date+"' LIMIT 1"
cursor.execute(querry)
connection.commit()
def clicked(tablename):
#this function displays the availble stock of a product
if (str(tablename) != ''):
querry = "SELECT * FROM "
querry = querry + str(tablename)
cursor.execute(querry)
field_names = Fetch.getFields(str(tablename))
column = 0
stock = tk.ThemedTk()
stock.title("Stock for " + tablename)
for j in range(len(field_names) -
1): #populate upper row with column names
e = ttk.Entry(stock, width=20)
e.grid(row=0, column=j)
e.insert(END, field_names[j])
i = 0
for bags in cursor: #popultate topmost row
for j in range(len(bags) - 1):
e = ttk.Entry(stock, width=20)
e.grid(row=i + 1, column=j)
e.insert(END, bags[j])
i = i + 1
else:
print('as')
def DrawReportIntoPdf(tableString,bool): #this function formats the DB date into table, passes the same to pdfwriter
field_names =Fetch.getFields(tableString)
list1=[]
list2=[]
totalQty=0
totalPrice=0
priceIn=0
if(int(len(data))==0):
for j in range(len(field_names)): #append columns names to data list,only if data list is not empty
list1.append(field_names[j])
data.append(list1)
for bags in cursor: #ciculate through data, ading qty and price for the final values, and appending the rows
for j in range(len(bags)): #individually to the data if its not a net report
list2.append(str(bags[j]))
if (j==2):
totalQty+=int(bags[j])
if (j==3):
totalPrice+=int(bags[j])
if (j==6 and tableString=="newstock"):
priceIn+=int(bags[j])
if(bool):
data.append(list2.copy())
list2.clear()
if(tableString=="saleout"):
data.append(['Total Price and Qty sold','',str(totalQty),str(totalPrice),''])
if(tableString=="newstock"):
qtyOut=int(data[1][2])
priceOut=int(data[1][3])
#Fetch.getPrice(str(categoty.get()),str(nameBox.get()),cursor,"costprice")
data.append(['Total Price and Qty bought','',str(totalQty),str(priceIn),''])
data.append(['net profit/loss','','',"Rs."+str((priceOut-priceIn)),''])
def saleOut(price):
attrList={}
formatted_date = Fetch.getFormattedDate()
attrList[0]=str(table.get())
attrList[1]=str(subTable.get())
attrList[2]=str(qtyTable.get())
attrList[3]=str(int(price)*int(qtyTable.get()))
attrList[4]=formatted_date
DbUtils.insertIntoDB("saleout",attrList,cursor)
def updateQuantity(event):
qtyArray=[]
cursor.execute("USE archa")
quantity=Fetch.getQuantity(str(table.get()),str(subTable.get()),cursor)
j=0
for i in range(int(quantity[0])):
qtyArray.append(i+1)
i+=1
qtyTable['values']=qtyArray #update the qty availalbe in the quantity box again as available qty-whatever was added to sale
def isAvailable():
qtyArray=[] #this function checks if selected item is available and populates the qty box with available qty
cursor.execute("USE archa")
quantity=Fetch.getQuantity(str(table.get()),str(subTable.get()),cursor)
if(quantity[0]==0):
return False
else:
cursor.execute("USE archa")
attDict=dict()
conditionDict=dict()
attDict={'qty':str(int(quantity[0])-int(qtyTable.get()))}
conditionDict={'Name':str(subTable.get())}
DbUtils.updateDB(str(table.get()),attDict,conditionDict,cursor)
for i in range(int(quantity[0]-int(qtyTable.get()))):
qtyArray.append(i+1)
i+=1
qtyTable['values']=qtyArray # populates the qty box with available qty
saleOut(Fetch.getPrice(str(table.get()),str(subTable.get()),cursor,"salesprice"))
return True
def addStock():
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
if ((str(nameBox.get()) == '')):
attrList = {}
attrList[0] = str(nameLabel.get("1.0", 'end-1c'))
attrList[2] = str(priceText.get("1.0", 'end-1c'))
attrList[1] = str(qtyText.get("1.0", 'end-1c'))
attrList[3] = str(costprice.get("1.0", 'end-1c'))
DbUtils.insertIntoDB(str(categoty.get()), attrList, cursor)
connection.commit()
else:
quantity = 0
quantity = getcurrentQty()
quantity += int(qtyText.get("1.0", 'end-1c'))
querry = "UPDATE " + str(categoty.get(
)) + " SET `qty` =" + str(quantity) + " WHERE `" + str(
categoty.get()) + "`.`Name`= '" + str(nameBox.get()) + "'"
cursor.execute(querry)
connection.commit()
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
formatted_date = Fetch.getFormattedDate()
if (str(nameLabel.get("1.0", 'end-1c')) == ''):
name = str(nameBox.get())
else:
name = str(nameLabel.get("1.0", 'end-1c'))
attrList = {}
attrList[0] = str(categoty.get())
attrList[1] = name
attrList[2] = str(qtyText.get("1.0", 'end-1c'))
attrList[3] = str(costprice.get("1.0", 'end-1c'))
attrList[4] = str(priceText.get("1.0", 'end-1c'))
attrList[5] = formatted_date
attrList[6] = int(qtyText.get("1.0", 'end-1c')) * int(
costprice.get("1.0", 'end-1c'))
DbUtils.insertIntoDB('newstock', attrList, cursor)
connection.commit()
messagebox.showinfo(
"", "The item has been successfully added to the stock")
newStock.destroy()
def insertIntoDB(
tableName, attList, cursor
): #This function inserts into DB dynamically. takes table attributes names from @param tableName
field_names = Fetch.getFields(
tableName
) #Takes cell values from attList.attList mst contain values in the same order as column names in the DB
querry = "insert into " + str(
tableName
) + "(" #else, there will be inconsistencies and sometimes querry execution may fail due to type constraints
for j in range(len(field_names)):
if (j != len(field_names) - 1):
querry = querry + "`" + field_names[j] + "`,"
else:
querry = querry + "`" + field_names[j] + "`"
querry = querry + ") VALUES ("
for i in range(len(attList)):
if (i != len(attList) - 1):
querry = querry + "'" + str(attList[i]) + "',"
else:
querry = querry + "'" + str(attList[i]) + "' )"
cursor.execute(querry)
def addSale():
#this function is the main function that adds sale to the totalsale, updates price, updates qty
if(isAvailable()):
price=Fetch.getPrice(str(table.get()), str(subTable.get()),cursor,"salesprice")
individualprice=int(price)
prevprice=int(w.cget("text"))
items=int(totalItems .cget("text"))
items+=1
totalItems.config(text=str(items))
totalprice=prevprice+(int(qtyTable.get())*individualprice)
w.config(text=str(totalprice))
List=[]
for i in range(3):
ea = ttk.Entry(saleFrame, width=10)
ea.grid(row=items+7,column=i)
ea.insert(END, str(table.get()))if i==0 else ea.insert(END, str(subTable.get())) if i==1 else ea.insert(END, str(qtyTable.get()))
List.append(ea)
makeSaleButton = Button(saleFrame, text = "remove", width=10,command=lambda:removeSale(List,makeSaleButton))
makeSaleButton.grid(row = items+7, column= i+1)
else:
messagebox.showerror("out of stock","Sorry, the item"+str(subTable.get())+"is out of stock")
def getcurrentQty():
quantity = Fetch.getQuantity(str(categoty.get()),
str(nameBox.get()), cursor)
return quantity[0]
def insertStock(self):
def loadNames(event):
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
cursor.execute(
"USE archa"
) #this function is called to display products under a category
querry = " select Name from " + str(categoty.get())
cursor.execute(querry)
subTables = cursor.fetchall()
nameBox['values'] = subTables
def addNewStock():
nameBox.forget()
nameLabel.grid(row=1, column=1)
def getcurrentQty():
quantity = Fetch.getQuantity(str(categoty.get()),
str(nameBox.get()), cursor)
return quantity[0]
def addStock():
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
if ((str(nameBox.get()) == '')):
attrList = {}
attrList[0] = str(nameLabel.get("1.0", 'end-1c'))
attrList[2] = str(priceText.get("1.0", 'end-1c'))
attrList[1] = str(qtyText.get("1.0", 'end-1c'))
attrList[3] = str(costprice.get("1.0", 'end-1c'))
DbUtils.insertIntoDB(str(categoty.get()), attrList, cursor)
connection.commit()
else:
quantity = 0
quantity = getcurrentQty()
quantity += int(qtyText.get("1.0", 'end-1c'))
querry = "UPDATE " + str(categoty.get(
)) + " SET `qty` =" + str(quantity) + " WHERE `" + str(
categoty.get()) + "`.`Name`= '" + str(nameBox.get()) + "'"
cursor.execute(querry)
connection.commit()
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
formatted_date = Fetch.getFormattedDate()
if (str(nameLabel.get("1.0", 'end-1c')) == ''):
name = str(nameBox.get())
else:
name = str(nameLabel.get("1.0", 'end-1c'))
attrList = {}
attrList[0] = str(categoty.get())
attrList[1] = name
attrList[2] = str(qtyText.get("1.0", 'end-1c'))
attrList[3] = str(costprice.get("1.0", 'end-1c'))
attrList[4] = str(priceText.get("1.0", 'end-1c'))
attrList[5] = formatted_date
attrList[6] = int(qtyText.get("1.0", 'end-1c')) * int(
costprice.get("1.0", 'end-1c'))
DbUtils.insertIntoDB('newstock', attrList, cursor)
connection.commit()
messagebox.showinfo(
"", "The item has been successfully added to the stock")
newStock.destroy()
def putPrice(
event
): #this function populated price if trying to add existing stock
price = Fetch.getPrice(str(categoty.get()), str(nameBox.get()),
cursor, "salesprice")
priceText.delete(1.0, "end")
priceText.insert(1.0, str(price))
price = Fetch.getPrice(str(categoty.get()), str(nameBox.get()),
cursor, "costprice")
costprice.delete(1.0, "end")
costprice.insert(1.0, str(price))
newStock = tk.ThemedTk()
newStock.get_themes()
newStock.set_theme("scidgreen")
newStock.geometry('350x350')
newStock.title("Stock Entry")
cat = tkinter.StringVar()
subcat = tkinter.StringVar()
categoty = ttk.Combobox(newStock, width=12,
textvariable=cat) # Adding Values
nameBox = ttk.Combobox(newStock, width=12, textvariable=subcat)
qtyText = tkinter.Text(newStock, width=11, height=1)
priceText = tkinter.Text(newStock, width=11, height=1)
addStockButton = ttk.Button(newStock,
text="add",
width=13,
command=addStock)
nameLabel = tkinter.Text(newStock, width=11, height=1)
costprice = tkinter.Text(newStock, width=11, height=1)
addNewStockButton = ttk.Button(newStock,
text="add new item",
width=13,
command=addNewStock)
addStockButton.grid(row=6, column=1)
addNewStockButton.grid(row=1, column=2)
qtyText.grid(row=2, column=1)
priceText.grid(row=4, column=1)
costprice.grid(row=3, column=1)
categoty.grid(column=1, row=0)
nameBox.grid(column=1, row=1)
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
cursor.execute("USE archa")
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()
tables2 = []
for i in range(len(tables)):
if tables[i][0] == 'newstock':
continue
else:
tables2.append(tables[i][0])
categoty['values'] = tables2
categoty.bind("<<ComboboxSelected>>", loadNames)
nameBox.bind("<<ComboboxSelected>>", putPrice)
field_names = Fetch.getFields('newstock')
for j in range(len(field_names) - 1):
label = tkinter.Label(newStock, width=10, text=field_names[j])
label.grid(row=j, column=0)
newStock.mainloop()