# -*- coding:gb2312 -*-
from tkinter import ttk
from tkinter import *
from tkinter import messagebox
import sqlite3
import os
class JXC(object):
def __init__(self):
self.db_path = os.path.join(os.path.dirname(__file__), 'jxc.db')
#print(self.db_path)
self.conn = self.get_conn()
self.create_table()
self.buyIndex = 1
self.sellIndex = 1
'''初始顶层框架'''
self.top = Tk()
self.top.title('简易进销存 1.0')
self.top.geometry('1366x768')
#self.top.geometry('960x720')
self.top.resizable(0, 0)
"""设置主页标签(简易进销存)"""
self.startLabel = Label(self.top, text='简易进销存 ', font=('Arial', 16, 'bold'))
self.startLabel.pack(fill=X, expand=0)
"""设置选项Buton """
self.optFrm = Frame(self.top, height=10)
self.optFrm.pack()
self.introduceBtn = Button(self.optFrm, text='使用说明', width=10, command=self.showStartFrm)
self.introduceBtn.grid(row=0, column=0, columnspan=15)
self.buyBtn = Button(self.optFrm, text='添加进货', width=10, command=self.showBuyFrm)
self.buyBtn.grid(row=0, column=15, columnspan=15)
self.sellBtn = Button(self.optFrm, text='添加出货', width=10, command=self.showSellFrm)
self.sellBtn.grid(row=0, column=30, columnspan=15)
self.SearchBtn = Button(self.optFrm, text='查询页面', width=10, command=self.showSearchFrm)
self.SearchBtn.grid(row=0, column=45, columnspan=15)
"""使用说明Frame"""
self.startFrm = Frame(self.top)
"""添加进货Frame"""
self.buyFrm = Frame(self.top)
"""添加进货Frame"""
self.sellFrm = Frame(self.top)
"""查询按钮Frame"""
self.SearchFrm = Frame(self.top)
#--------------------------------------------------
"""显示不同页面信息"""
def showStartFrm(self):
self.startFrm.pack(fill='both', expand=1)
self.buyFrm.pack_forget()
self.sellFrm.pack_forget()
self.SearchFrm.pack_forget()
def showBuyFrm(self):
self.startFrm.pack_forget()
self.buyFrm.pack(fill='both', expand=1)
self.sellFrm.pack_forget()
self.SearchFrm.pack_forget()
def showSellFrm(self):
self.startFrm.pack_forget()
self.buyFrm.pack_forget()
self.sellFrm.pack(fill='both', expand=1)
self.SearchFrm.pack_forget()
def showSearchFrm(self):
self.startFrm.pack_forget()
self.buyFrm.pack_forget()
self.sellFrm.pack_forget()
self.SearchFrm.pack(fill='both', expand=1)
#-----------------------------------------------------
"""设置使用说明页面信息及功能"""
def setStartSubFrm(self):
illustrationText = """
使用说明
添加进货
在添加进货页面操作，上方填写相应的进货信息（不需要填写的内容可以为空，物品名称不能为空），下方表格显示新添加的购货信息
添加销售
在添出货货页面操作，上方填写相应的出货信息（不需要填写的内容可以为空，物品名称不能为空），下方表格显示新添加的出货信息
查询页面
可以输入相应的查询条件（不需要的条件可以为空），输入条件后点击查询流水进行查询
不输入任何查询条件，点击查询流水可以查询历来的流水信息
点击查询库存按钮可以查询库存信息
点击清除查找条件可以清除输入的查找条件
"""
self.illustrLabel = Label(self.startFrm, text=illustrationText, font=('Arial', 14, 'bold'))
self.illustrLabel.pack()
#-----------------------------------------------------
"""设置添加进货页面信息及功能"""
def setBuySubFrm(self):
option = [ '物品名称', '物品编号','订单日期', '进货价格', '进货数量', '进货厂商']
labelText = ['添加新的进货订货单', '已添加的进货订单']
self.subBuyFrmUp = Frame(self.buyFrm)
self.subBuyFrmUp.pack(fill=X, expand=0)
self.subBuyFrmDown = Frame(self.buyFrm)
self.subBuyFrmDown.pack(fill=X, expand=0)
self.addBuyInfoLabel = Label(self.subBuyFrmUp, text=labelText[0], font=('Arial', 14, 'bold'), bg='gray')
self.addBuyInfoLabel.pack(fill=X, expand=0)
self.subBuyFrmUpOperate = Frame(self.subBuyFrmUp)
self.subBuyFrmUpOperate.pack()
self.showBuyInfoLabel = Label(self.subBuyFrmDown, text=labelText[1], font=('Arial', 14, 'bold'), bg='gray')
self.showBuyInfoLabel.pack(fill=X, expand=0)
self.subBuyFrmDownShow = Frame(self.subBuyFrmDown)
self.subBuyFrmDownShow.pack(fill=X, expand=0)
"""添加进货页面的标签"""
self.dateBuyLabel = Label(self.subBuyFrmUpOperate, text=option[0], font=('Arial', 12), width=15)
self.dateBuyLabel.grid(row=0, column=0, columnspan=15)
self.itemBuyLabel = Label(self.subBuyFrmUpOperate, text=option[1], font=('Arial', 12), width=15)
self.itemBuyLabel.grid(row=1, column=0, columnspan=15)
self.identBuyLabel = Label(self.subBuyFrmUpOperate, text=option[2], font=('Arial', 12), width=15)
self.identBuyLabel.grid(row=2, column=0, columnspan=15)
self.priBuyLabel = Label(self.subBuyFrmUpOperate, text=option[3], font=('Arial', 12), width=15)
self.priBuyLabel.grid(row=3, column=0, columnspan=15)
self.numBuyLabel = Label(self.subBuyFrmUpOperate, text=option[4], font=('Arial', 12), width=15)
self.numBuyLabel.grid(row=4, column=0, columnspan=15)
self.whereBuyLabel = Label(self.subBuyFrmUpOperate, text=option[5], font=('Arial', 12), width=15)
self.whereBuyLabel.grid(row=5, column=0, columnspan=15)
"""添加进货页面的输入框"""
self.dateBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[0], font=('Arial', 12), width=25)
self.dateBuyEntry.grid(row=0, column=15, columnspan=15)
self.itemBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[1], font=('Arial', 12), width=25)
self.itemBuyEntry.grid(row=1, column=15, columnspan=15)
self.identBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[2], font=('Arial', 12), width=25)
self.identBuyEntry.grid(row=2, column=15, columnspan=15)
self.priBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[3], font=('Arial', 12), width=25)
self.priBuyEntry.grid(row=3, column=15, columnspan=15)
self.numBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[4], font=('Arial', 12), width=25)
self.numBuyEntry.grid(row=4, column=15, columnspan=15)
self.whereBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[5], font=('Arial', 12), width=25)
self.whereBuyEntry.grid(row=5, column=15, columnspan=15)
"""添加进货页面的清楚和保存按钮"""
self.clcBuyBtn = Button(self.subBuyFrmUpOperate, text='清除', activebackground='gray', command=self.clearBuyEntry)
self.clcBuyBtn.grid(row=7, column=15, columnspan=5)
self.saveBuyBtn = Button(self.subBuyFrmUpOperate, text='保存', activebackground='gray', command=self.showBuyInfo)
self.saveBuyBtn.grid(row=7, column=26, columnspan=5)
"""显示新添加的内容"""
self.treeBuy = ttk.Treeview(self.subBuyFrmDownShow, height=20)
self.treeBuy['column'] = tuple(option)
self.treeBuy.heading("#0", text='订单序号')
self.treeBuy.column("#0", minwidth=0, width=140)
for item in option:
self.treeBuy.column(item, minwidth=0, width=140)
self.treeBuy.heading(item, text=item)
self.treeBuy.pack(fill=X, expand=0)
def getBuyInput(self):
newInput = []
newInput.append(self.dateBuyEntry.get())
newInput.append(self.itemBuyEntry.get())
newInput.append(self.identBuyEntry.get())
newInput.append(self.priBuyEntry.get())
newInput.append(self.numBuyEntry.get())
newInput.append(self.whereBuyEntry.get())
for i in range(len(newInput)):
if newInput[i] == '':
newInput[i] = None
return tuple(newInput)
def clearBuyEntry(self):
self.itemBuyEntry.delete(0, END)
self.identBuyEntry.delete(0, END)
self.priBuyEntry.delete(0, END)
self.numBuyEntry.delete(0, END)
self.whereBuyEntry.delete(0, END)
self.dateBuyEntry.delete(0, END)
def showBuyInfo(self):
val = messagebox.askyesno('警告', '保存该订单信息?')
if val:
messagebox.showinfo('提示', '订单信息保存成功!')
order = self.getBuyInput()
self.clearBuyEntry()
self.updateTree(self.treeBuy, order)
self.save_data(order, True)
#-----------------------------------------------------
"""设置添加出货页面信息及功能"""
def setSellSubFrm(self):
option = ['物品名称', '物品编号','订单日期', '销售价格', '销售数量', '客户信息']
labelText = ['添加新的销售订货单', '已添加的销售订单']
self.subSellFrmUp = Frame(self.sellFrm)
self.subSellFrmUp.pack(fill=X, expand=0)
self.subSellFrmDown = Frame(self.sellFrm)
self.subSellFrmDown.pack(fill=X, expand=0)
self.addSellInfoLabel = Label(self.subSellFrmUp, text=labelText[0], font=('Arial', 14, 'bold'), bg='gray')
self.addSellInfoLabel.pack(fill=X, expand=0)
self.subSellFrmUpOperate = Frame(self.subSellFrmUp)
self.subSellFrmUpOperate.pack()
self.showSellInfoLabel = Label(self.subSellFrmDown, text=labelText[1], font=('Arial', 14, 'bold'), bg='gray')
self.showSellInfoLabel.pack(fill=X, expand=0)
self.subSellFrmDownShow = Frame(self.subSellFrmDown)
self.subSellFrmDownShow.pack(fill=X, expand=0)
"""添加出货页面的标签"""
self.dateSellLabel = Label(self.subSellFrmUpOperate, text=option[0], font=('Arial', 12), width=15)
self.dateSellLabel.grid(row=0, column=0, columnspan=15)
self.itemSellLabel = Label(self.subSellFrmUpOperate, text=option[1], font=('Arial', 12), width=15)
self.itemSellLabel.grid(row=1, column=0, columnspan=15)
self.identSellLabel = Label(self.subSellFrmUpOperate, text=option[2], font=('Arial', 12), width=15)
self.identSellLabel.grid(row=2, column=0, columnspan=15)
self.priSellLabel = Label(self.subSellFrmUpOperate, text=option[3], font=('Arial', 12), width=15)
self.priSellLabel.grid(row=3, column=0, columnspan=15)
self.numSellLabel = Label(self.subSellFrmUpOperate, text=option[4], font=('Arial', 12), width=15)
self.numSellLabel.grid(row=4, column=0, columnspan=15)
self.whereSellLabel = Label(self.subSellFrmUpOperate, text=option[5], font=('Arial', 12), width=15)
self.whereSellLabel.grid(row=5, column=0, columnspan=15)
"""添加出货页面的输入框"""
self.dateSellEntry = Entry(self.subSellFrmUpOperate, text=option[0], font=('Arial', 12), width=25)
self.dateSellEntry.grid(row=0, column=15, columnspan=15)
self.itemSellEntry = Entry(self.subSellFrmUpOperate, text=option[1], font=('Arial', 12), width=25)
self.itemSellEntry.grid(row=1, column=15, columnspan=15)
self.identSellEntry = Entry(self.subSellFrmUpOperate, text=option[2], font=('Arial', 12), width=25)
self.identSellEntry.grid(row=2, column=15, columnspan=15)
self.priSellEntry = Entry(self.subSellFrmUpOperate, text=option[3], font=('Arial', 12), width=25)
self.priSellEntry.grid(row=3, column=15, columnspan=15)
self.numSellEntry = Entry(self.subSellFrmUpOperate, text=option[4], font=('Arial', 12), width=25)
self.numSellEntry.grid(row=4, column=15, columnspan=15)
self.whereSellEntry = Entry(self.subSellFrmUpOperate, text=option[5], font=('Arial', 12), width=25)
self.whereSellEntry.grid(row=5, column=15, columnspan=15)
"""添加出货页面的清楚和保存按钮"""
self.clcSellBtn = Button(self.subSellFrmUpOperate, text='清除', activebackground='gray',
command=self.clearSellEntry)
self.clcSellBtn.grid(row=7, column=15, columnspan=5)
self.saveSellBtn = Button(self.subSellFrmUpOperate, text='保存', activebackground='gray',
command=self.showSellInfo)
self.saveSellBtn.grid(row=7, column=26, columnspan=5)
"""显示出货页面新添加的内容"""
self.treeSell = ttk.Treeview(self.subSellFrmDownShow, height=20)
self.treeSell['column'] = tuple(option)
self.treeSell.heading("#0", text='订单序号')
self.treeSell.column("#0", minwidth=0, width=140)
for item in option:
self.treeSell.column(item, minwidth=0, width=140)
self.treeSell.heading(item, text=item)
self.treeSell.pack(fill=X, expand=0)
def getSellInput(self):
newInput = []
newInput.append(self.dateSellEntry.get())
newInput.append(self.itemSellEntry.get())
newInput.append(self.identSellEntry.get())
newInput.append(self.priSellEntry.get())
newInput.append(self.numSellEntry.get())
newInput.append(self.whereSellEntry.get())
for i in range(len(newInput)):
if newInput[i] == '':
newInput[i] = None
return tuple(newInput)
def clearSellEntry(self):
self.itemSellEntry.delete(0, END)
self.identSellEntry.delete(0, END)
self.priSellEntry.delete(0, END)
self.numSellEntry.delete(0, END)
self.whereSellEntry.delete(0, END)
self.dateSellEntry.delete(0, END)
def showSellInfo(self):
val = messagebox.askyesno('警告', '保存该订单信息?')
if val:
messagebox.showinfo('提示', '订单信息保存成功!')
order = self.getSellInput()
self.clearSellEntry()
self.updateTree(self.treeSell, order)
self.save_data(order,False)
#-----------------------------------------------------
"""设置查询页面信息及功能"""
def setSearchFrm(self):
self.SearchUpFrm = Frame(self.SearchFrm)
self.SearchUpFrm.pack(fill=X, expand=0)
self.SearchDownFrm = Frame(self.SearchFrm)
self.SearchDownFrm.pack(fill=X, expand=0)
#-----------------------------------------------------------------------
self.SearchUpInfoLabel1 = Label(self.SearchUpFrm, text='物品名称',font=('Arial', 14, 'bold'))
self.SearchUpInfoLabel1.grid(row=0, column=0, columnspan=2)
self.SearchUpEntry1 = Entry(self.SearchUpFrm)
self.SearchUpEntry1.grid(row=0, column=2, columnspan=5)
self.SearchUpInfoLabel2 = Label(self.SearchUpFrm, text='物品编号',font=('Arial', 14, 'bold'))
self.SearchUpInfoLabel2.grid(row=0, column=8, columnspan=2)
self.SearchUpEntry2 = Entry(self.SearchUpFrm)
self.SearchUpEntry2.grid(row=0, column=10, columnspan=5)
self.SearchUpInfoLabel3 = Label(self.SearchUpFrm, text='查询日期',font=('Arial', 14, 'bold'))
self.SearchUpInfoLabel3.grid(row=0, column=16, columnspan=2)
self.SearchUpEntry31 = Entry(self.SearchUpFrm)
self.SearchUpEntry31.grid(row=0, column=18, columnspan=5)
self.SearchUpInfoLabel3 = Label(self.SearchUpFrm, text='---', font=('Arial', 14, 'bold'))
self.SearchUpInfoLabel3.grid(row=0, column=23, columnspan=1)
self.SearchUpEntry32 = Entry(self.SearchUpFrm)
self.SearchUpEntry32.grid(row=0, column=24, columnspan=5)
self.SearchUpInfoLabel4 = Label(self.SearchUpFrm, text='进货厂商',font=('Arial', 14, 'bold'))
self.SearchUpInfoLabel4.grid(row=0, column=32, columnspan=2)
self.SearchUpEntry4 = Entry(self.SearchUpFrm)
self.SearchUpEntry4.grid(row=0, column=34, columnspan=5)
self.SearchUpInfoLabel5 = Label(self.SearchUpFrm, text='出货厂商',font=('Arial', 14, 'bold'))
self.SearchUpInfoLabel5.grid(row=0, column=40, columnspan=2)
self.SearchUpEntry5 = Entry(self.SearchUpFrm)
self.SearchUpEntry5.grid(row=0, column=42, columnspan=5)
self.SearchUpInfoBtn1 = Button(self.SearchUpFrm, text='查询流水', activebackground='gray', command=self.search_statement_fun)
self.SearchUpInfoBtn1.grid(row=2, column=5, columnspan=5)
self.SearchUpInfoBtn1 = Button(self.SearchUpFrm, text='清除查找条件', activebackground='gray', command=self.clearSearchBtn)
self.SearchUpInfoBtn1.grid(row=2, column=17, columnspan=5)
self.SearchUpInfoBtn2 = Button(self.SearchUpFrm, text='查询库存', activebackground='gray', command=self.search_stockList_fun)
self.SearchUpInfoBtn2.grid(row=2, column=30, columnspan=5)
self.treeSearchStatement = ttk.Treeview(self.SearchDownFrm, height=30)
optionStatement = ['物品名称', '物品编号', '日期','进货价格', '进货数量', '进货厂商', '销售价格', '销售数量', '客户信息']
self.treeSearchStatement['column'] = optionStatement
self.treeSearchStatement.heading("#0", text='序号')
self.treeSearchStatement.column("#0", minwidth=0, width=96)
for item in optionStatement:
self.treeSearchStatement.heading(item, text=item)
self.treeSearchStatement.column(item, minwidth=0, width=96)
self.treeSearchStock = ttk.Treeview(self.SearchDownFrm, height=30)
optionStock = ['物品名称', '物品编号', '库存数量']
self.treeSearchStock['column'] = optionStock
self.treeSearchStock.heading("#0", text='序号')
self.treeSearchStock.column("#0", minwidth=0, width=96)
for item in optionStock:
self.treeSearchStock.heading(item, text=item)
self.treeSearchStock.column(item, minwidth=0, width=96)
def getSearchInput(self):
newInput = []
newInput.append(self.SearchUpEntry1.get())
newInput.append(self.SearchUpEntry2.get())
newInput.append(self.SearchUpEntry31.get())
newInput.append(self.SearchUpEntry32.get())
newInput.append(self.SearchUpEntry4.get())
newInput.append(self.SearchUpEntry5.get())
for i in range(len(newInput)):
if newInput[i] == '':
newInput[i] = None
return tuple(newInput)
def search_statement_fun(self):
itemList = self.getSearchInput()
searchList = []
if itemList.count(None)==len(itemList):
sql = "SELECT * FROM statementList"
else:
sql = "SELECT * FROM statementList WHERE "
if None != itemList[0]:
sql += '物品名称=? and '
searchList.append(itemList[0])
if None != itemList[1]:
sql += '物品编号=? and '
searchList.append(itemList[1])
if None != itemList[2]:
sql += '日期>=? and '
searchList.append(itemList[2])
if None != itemList[3]:
sql += '日期<=? and '
searchList.append(itemList[3])
if None != itemList[4]:
sql += '进货厂商=? and '
searchList.append(itemList[4])
if None != itemList[5]:
sql += '出货厂商=? and '
searchList.append(itemList[5])
sql = sql+'id>=1 '
print(sql)
cu = self.get_cursor()
#print(searchList)
cu.execute(sql,tuple(searchList))
results = cu.fetchall()
self.treeSearchStatement.pack(fill=X, expand=0)
self.treeSearchStock.pack_forget()
items = self.treeSearchStatement.get_children()
[self.treeSearchStatement.delete(item) for item in items]
index = 0
print('--'*20)
for row in results:
index += 1
print(row)
self.treeSearchStatement.insert('', index,text=index , values=row[1:])
cu.close()
def search_stockList_fun(self):
sql = "SELECT * FROM stockList"
cu = self.get_cursor()
cu.execute(sql)
results = cu.fetchall()
self.treeSearchStatement.pack_forget()
self.treeSearchStock.pack(fill=X, expand=0)
items = self.treeSearchStock.get_children()
[self.treeSearchStock.delete(item) for item in items]
index = 0
for row in results:
print(row)
index += 1
self.treeSearchStock.insert('', index, text=index, values=row)
cu.close()
def clearSearchBtn(self):
self.SearchUpEntry1.delete(0, END)
self.SearchUpEntry2.delete(0, END)
self.SearchUpEntry31.delete(0, END)
self.SearchUpEntry32.delete(0, END)
self.SearchUpEntry4.delete(0, END)
self.SearchUpEntry5.delete(0, END)
def updateTree(self,root,order):
if root == self.treeBuy:
root.insert('',self.buyIndex,text=self.buyIndex,values=order)
self.buyIndex += 1
elif root == self.treeSell:
root.insert('',self.sellIndex,text=self.sellIndex,values=order)
self.buyIndex += 1
def get_conn(self):
return sqlite3.connect(self.db_path)
def get_cursor(self):
if self.conn is not None:
return self.conn.cursor()
def create_table(self):
cu = self.get_cursor()
statementSql = """CREATE TABLE IF NOT EXISTS statementList (
'id' integer PRIMARY KEY AUTOINCREMENT,
'物品名称' varchar(40) NOT NULL,
'物品编号' varchar(40) ,
'日期' varchar(40) ,
'进货价格' int(40) ,
'进货数量' int(40) ,
'进货厂商' varchar(40) ,
'出货价格' int(40) ,
'出货数量' int(40) ,
'出货厂商' varchar(40)
)"""
cu.execute(statementSql)
stockSql = """CREATE TABLE IF NOT EXISTS stockList (
'物品名称' varchar(40) PRIMARY KEY NOT NULL UNIQUE,
'物品编号' varchar(40) ,
'库存数量' int(40) default 0
)"""
cu.execute(stockSql)
cu.close()
self.conn.commit()
def save_data(self,data,INorOutFlag):
cu = self.get_cursor()
if data[0] != None:
if INorOutFlag:
sql = """INSERT INTO statementList
(物品名称,物品编号,日期,进货价格,进货数量,进货厂商)
VALUES(?,?,?,?,?,?)"""
else:
sql = """INSERT INTO statementList
(物品名称,物品编号,日期,出货价格,出货数量,出货厂商)
VALUES(?,?,?,?,?,?)"""
cu.execute(sql, data)
self.update_stockList(cu, data,INorOutFlag)
cu.close()
self.conn.commit()
def update_stockList(self, cu, data,InOrOutFlag):
if data[0] != None:
try:
insertSql = "INSERT INTO stockList VALUES (?,?,?)"
dataUsed = (data[0], data[1], data[4])
cu.execute(insertSql, dataUsed)
except sqlite3.IntegrityError:
if InOrOutFlag:
updateSql = "UPDATE stockList SET 库存数量=库存数量+? WHERE 物品名称=?"
else:
updateSql = "UPDATE stockList SET 库存数量=库存数量-? WHERE 物品名称=?"
dataUsed = (data[4], data[0])
cu.execute(updateSql, dataUsed)
def start(self):
self.setSearchFrm()
self.setStartSubFrm()
self.setBuySubFrm()
self.setSellSubFrm()
self.showStartFrm()
self.top.mainloop()
def main():
jxc = JXC()
jxc.start()
if __name__ == '__main__':
main()
