创建产品信息表和进出库表:系统可以创建数据库表格用于存储产品信息、库存数量和进出库记录。产品信息表包括产品ID、名称和价格等字段,进出库表包括记录ID、产品ID、数量、类型(入库或出库)、日期等字段。
产品入库管理:管理员可以通过系统界面填写入库单,包括选择产品、输入入库数量和日期等信息。确认入库后,系统会将相关信息记录到进出库表中,并更新产品的库存数量。
产品出库管理:管理员可以使用系统界面填写出库单,选择要出库的产品、输入出库数量和日期等信息。确认出库后,系统将相应的记录添加到进出库表中,并更新产品的库存数量。
借出管理:系统支持借出功能,管理员可以使用系统生成借条,并记录借出的产品信息。借出后,系统会相应地减少产品的库存数量。当产品归还时,管理员可以在系统中进行归还操作,系统将更新库存数量。
初始库存设置:系统允许管理员设置产品的初始库存值,并设定库存的上下警戒限。这些值可以在系统中进行配置,以便系统根据设定的阈值进行库存警报和提醒。
盘库功能:系统提供盘库功能,管理员可以选择进行月度或年度的盘库。在盘库过程中,系统会与实际库存进行对比,以便发现差异并进行调整。
查询功能:系统提供灵活的查询功能,管理员可以按照出库单、入库单、产品、时间等条件进行查询。管理员可以根据需求获取特定时间段的进出库记录,查看特定产品的入库和出库情况,或者查询当前库存情况。查询结果将在系统界面上显示或以报表形式呈现。
数据库架构:
使用合适的数据库管理系统(如SQL Server)作为后端数据库,提供稳定性和性能优化。
设计合理的表结构和关系,以便支持系统功能和查询需求。
数据表设计:
创建产品信息表(Product):包括产品ID(主键)、名称、价格字段。
创建进出库表(Inventory):包括记录ID(主键)、产品ID(外键)、数量、类型(入库或出库)、日期等字段。
import tkinter as tk
import tkinter.messagebox as messagebox
import pyodbc
server = 'LAP'
database = 'Warehouse_management'
driver = '{ODBC Driver 17 for SQL Server}'
# 建立数据库连接
conn = pyodbc.connect(f'SERVER={server};DATABASE={database};Trusted_Connection=yes;DRIVER={driver}')
# 创建产品信息表
def create_product_table():
try:
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
Name NVARCHAR(50),
Price DECIMAL(10, 2)
)
''')
conn.commit()
messagebox.showinfo('成功', '创建产品信息表成功')
except Exception as e:
messagebox.showerror('错误', f'创建产品信息表失败:{str(e)}')
# 创建进出库表
def create_inventory_table():
try:
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE Inventory (
ID INT IDENTITY(1,1) ,
ProductID INT,
Quantity INT,
Type NVARCHAR(10),
Date DATE,
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
)
''')
conn.commit()
messagebox.showin('成功', '创建进出库表成功')
except Exception as e:
messagebox.shower('错误', f'创建进出库表失败:{str(e)}')
# 产品入库管理
def add_inventory():
product_id = entry_product_id.get()
quantity = entry_quantity.get()
date = entry_date.get()
try:
cursor = conn.cursor()
cursor.execute('INSERT INTO Inventory (ProductID, Quantity, Type, Date) VALUES (?, ?, ?, ?)', (product_id, quantity, '入库', date))
conn.commit()
messagebox.showin('成功', '产品入库成功')
except Exception as e:
messagebox.showerror('错误', f'产品入库失败:{str(e)}')
# 产品出库管理
def remove_inventory():
product_id = entry_product_id.get()
quantity = entry_quantity.get()
date = entry_date.get()
try:
cursor = conn.cursor()
cursor.execute('INSERT INTO Inventory (ProductID, Quantity, Type, Date) VALUES (?, ?, ?, ?)', (product_id, quantity, '出库', date))
conn.commit()
messagebox.showinfo('成功', '产品出库成功')
except Exception as e:
messagebox.showerror('错误', f'产品出库失败:{str(e)}')
# 借出管理
def lend_product():
product_id = entry_product_id.get()
quantity = entry_quantity.get()
date = entry_date.get()
try:
cursor = conn.cursor()
cursor.execute('INSERT INTO Inventory (ProductID, Quantity, Type, Date) VALUES (?, ?, ?, ?)', (product_id, quantity, '借出', date))
conn.commit()
messagebox.showin('成功', '借出产品成功')
except Exception as e:
messagebox.showerror('错误', f'借出产品失败:{str(e)}')
# 归还管理
def return_product():
product_id = entry_product_id.get()
quantity = entry_quantity.get()
date = entry_date.get()
try:
cursor = conn.cursor()
cursor.execute('INSERT INTO Inventory (ProductID, Quantity, Type, Date) VALUES (?, ?, ?, ?)', (product_id, quantity, '归还', date))
conn.commit()
messagebox.showinfo('成功', '归还产品成功')
except Exception as e:
messagebox.showerror('错误', f'归还产品失败:{str(e)}')
# 设置初始库存
def set_initial_inventory():
product_id = entry_product_id.get()
quantity = entry_quantity.get()
try:
cursor = conn.cursor()
cursor.execute('INSERT INTO Inventory (ProductID, Quantity, Type) VALUES (?, ?, ?)', (product_id, quantity, '初始库存'))
conn.commit()
messagebox.showinfo('成功', '设置初始库存成功')
except Exception as e:
messagebox.showerror('错误', f'设置初始库存失败:{str(e)}')
# 盘库
def perform_stocktaking():
try:
cursor = conn.cursor()
cursor.execute('''
SELECT p.Name, i.Quantity
FROM Product p
INNER JOIN (
SELECT ProductID, SUM(CASE WHEN Type = '入库' THEN Quantity ELSE -Quantity END) AS Quantity
FROM Inventory
GROUP BY ProductID
) i ON p.ProductID = i.ProductID
''')
result = cursor.fetch()
messagebox.showinfo('盘库结果', '\n'.join([f'{row.Name}: {row.Quantity}' for row in result]))
except Exception as e:
messagebox.showerror('错误', f'盘库失败:{str(e)}')
# 查询产品入库情况
def view_product_in():
product_id = entry_product_id.get()
try:
cursor = conn.cursor()
cursor.execute('SELECT * FROM Inventory WHERE ProductID = ? AND Type = ?', (product_id, '入库'))
result = cursor.fetchall()
messagebox.showinfo('产品入库情况', '\n'.join([f'ID: {row.ID}, Quantity: {row.Quantity}, Date: {row.Date}' for row in result]))
except Exception as e:
messagebox.showerror('错误', f'查询产品入库情况失败:{str(e)}')
# 查询产品出库情况
def view_product_out():
product_id = entry_product_id.get()
try:
cursor = conn.cursor()
cursor.execute('SELECT * FROM Inventory WHERE ProductID = ? AND Type = ?', (product_id, '出库'))
result = cursor.fetchall()
messagebox.showinfo('产品出库情况', '\n'.join([f'ID: {row.ID}, Quantity: {row.Quantity}, Date: {row.Date}' for row in result]))
except Exception as e:
messagebox.shower('错误', f'查询产品出库情况失败:{str(e)}')
# 查询当前库存情况
def view_inventory():
try:
cursor = conn.cursor()
cursor.execute('''
SELECT p.Name, i.Quantity
FROM Product p
INNER JOIN (
SELECT ProductID, SUM(CASE WHEN Type = '入库' THEN Quantity ELSE -Quantity END) AS Quantity
FROM Inventory
GROUP BY ProductID
) i ON p.ProductID = i.ProductID
''')
result = cursor.fetchall()
messagebox.showinfo('当前库存情况', '\n'.join([f'{row.Name}: {row.Quantity}' for row in result]))
except Exception as e:
messagebox.showerror('错误', f'查询当前库存情况失败:{str(e)}')
# 创建GUI窗口
window = tk.Tk()
window.title('仓库管理系统')
# 创建产品信息表按钮
btn_create_product_table = tk.Button(window, text='创建产品信息表', command=create_product_table)
btn_create_product_table()
# 创建进出库表按钮
btn_create_inventory_table = tk.Button(window, text='创建进出库表', command=create_inventory_table)
btn_create_inventory_table.pack()
# 产品ID输入框
label_product_id = tk.Label(window, text='产品ID')
label_product_id.pack()
entry_product_id = tk.Entry(window)
entry_product_id.pack()
# 数量输入框
label_quantity = tk.Label(window, text='数量')
label_quantity.pack()
entry_quantity = tk(window)
entry_quantity.pack()
# 日期输入框
label_date = tk.Label(window, text='日期(YYYY-MM-DD)')
label_date.pack()
entry_date = tk.Entry(window)
entry_date.pack()
# 产品入库按钮
btn_add_inventory = tk.Button(window, text='产品入库',
command=add_inventory)
btn_add_inventory.pack()
# 产品出库按钮
btn_remove_inventory = tk.Button(window, text='产品出库',
command=remove_inventory)
btn_remove_inventory.pack()
# 借出产品按钮
btn_lend_product = tk.Button(window, text='借出产品',
command=lend_product)
btn_lend_product.pack()
# 设置初始库存按钮
btn_set_initial_inventory = tk.Button(window, text='设置初始库存',
command=set_initial_inventory)
btn_set_initial_inventory()
# 盘库按钮
btn_perform_stocktaking = tk.Button(window, text='盘库',
command=perform_stocktaking)
btn_perform_stocktaking.pack()
# 查询产品入库情况按钮
btn_view_product_in = tk.Button(window, text='查询产品入库情况',
command=view_product_in)
btn_view_product_in.pack()
# 查询产品出库情况按钮
btn_view_product_out = tk.Button(window, text='查询产品出库情况',
command=view_product_out)
btn_view_product_out.pack()
# 查询当前库存情况按钮
btn_view_inventory = tk.Button(window, text='查询当前库存情况',
command=view_inventory)
btn_view_inventory.pack()
# 运行GUI窗口
window.mainloop()