给学数据库做课程设计的参考一下,分享一下,不喜勿喷,水平不高,项目比较一般,但是数据库设计肯定是符合第三范式的,可以只参考数据库设计。
1 需求分析
1.1 需求分析
伴随着连锁超市的不断增加,自动化管理水平不断提高,连锁超市管理系统的重要性也日益体现。为了准确掌握仓库信息,职工信息,销售信息,商品信息,保证商店工作效率,本课程设计连锁超市管理系统。
1.2 设计任务
系统要处理的对象包括商店基本信息,商品基本信息,职工基本信息,仓库基本信息,管理员信息,各个对象包括信息如下所示:
(1) 商店基本信息:商店编号、商店名、商店地址
(2) 商品基本信息:商品编号、商品名、销售单价、成本
(3) 职工基本信息:职工编号、姓名、性别、工龄、电话号码
(4) 仓库基本信息:仓库编号,仓库地址
(5) 管理员基本信息:用户名、账号密码、姓名、性别
2 数据库概念设计
2.1 确定局部范围
本系统局部范围为连锁超市管理系统
2.2 局部概念结构设计
2.2.1识别实体与实体的主键
商店(商店编号)
商品(商品编号)
职工(职工编号)
仓库(仓库编号)
管理员(用户名)
2.2.2定义实体间的联系
在连锁超市管理系统中,一个商店可以聘用多名职工,多个商品可以被多个商店销售,是多对多的联系。多个商品可以被多个仓库存放,是多对多的联系。
2.2.3 给实体及联系加上描述属性并优化ER
图1-连锁超市管理系统全局E-R图
3 数据库逻辑设计
3.1 实体转化为关系模式
商店(商店编号、商店名、商店地址)
商品(商品编号、商品名、销售单价、成本)
职工(职工编号、姓名、性别、工龄、电话号码,商店编号,月薪,聘期,)
仓库(仓库编号,仓库地址)
管理员(用户名、账号密码、姓名、性别)
3.1 联系转化为关系模式
销售信息(商店编号、商品编号、销售日期、销售数量)
库存信息(仓库编号、商品编号、库存量)
4 数据库物理设计
表1.Shop表
序号 | 中文字段名 | 英文字段名 | 字段类型 | 完整性约束 |
1 | 商店编号 | shno | varchar(20) | 主键 |
2 | 商店名 | shname | varchar(20) |
|
3 | 商店地址 | shaddress | varchar(30) |
|
表2.Good表
序号 | 中文字段名 | 英文字段名 | 字段类型 | 完整性约束 |
1 | 商品编号 | gno | varchar(20) | 主键 |
2 | 商品名 | gname | varchar(20) |
|
3 | 销售单价 | salprice | int |
|
4 | 成本 | inprice | int |
|
表3.Staff表
序号 | 中文字段名 | 英文字段名 | 字段类型 | 完整性约束 |
1 | 职工编号 | stno | varchar(20) | 主键 |
2 | 姓名 | stname | char(20) |
|
3 | 性别 | sex | char(10) |
|
4 | 工龄 | wage | int |
|
5 | 电话号码 | pnum | varchar(11) |
|
6 | 商店编号 | shno | varchar(20) | 外键 |
7 | 聘期 | wtime | varchar(30) |
|
8 | 月薪 | wsalary | int |
|
表4.Shouse表
序号 | 中文字段名 | 英文字段名 | 字段类型 | 完整性约束 |
1 | 仓库编号 | hno | varchar(20) | 主键 |
2 | 仓库地址 | saddress | varchar(30) |
|
表5.Admins表
序号 | 中文字段名 | 英文字段名 | 字段类型 | 完整性约束 |
1 | 用户名 | Id | varchar(20) | 主键 |
2 | 账号密码 | password | varchar(20) |
|
3 | 姓名 | name | char(20) |
|
4 | 性别 | sex | char(10) |
|
表7. SS表
序号 | 中文字段名 | 英文字段名 | 字段类型 | 完整性约束 |
1 | ID | ID | int | 标识、主键 |
2 | 商店编号 | shno | varchar(20) | 主键、外键 |
3 | 商品编号 | gno | varchar(20) | 主键、外键 |
4 | 销售日期 | sdate | varchar(20) |
|
5 | 销售数量 | snumb | int |
|
表7. SH表
序号 | 中文字段名 | 英文字段名 | 字段类型 | 完整性约束 |
1 | 商品编号 | gno | varchar(20) | 主键、外键 |
2 | 仓库编号 | hno | varchar(20) | 主键、外键 |
3 | 库存量 | shnumb | int |
|
5 系统实现
5.1 管理员登录和注册
图1.1管理员登录
图1.2管理员注册
5.2 员工管理
图2-1员工管理
图2-2添加员工
5.3 仓库管理
图3-1仓库管理
5.4 销售管理
图4-1销售管理
4-2历史订单记录
图4-3 SH表对库存进行由生产时间的先后进行出库的触发器
图4-4 SS表对库存进行由生产时间的先后进行出库的触发器
5.5 商品、库存信息管理
图5-1商品、库存信息管理
图5-2查找商品信息和库存信息
代码(仅部分代码,全代码可以去我主页资源里找)
建库代码
import pymssql
connect = pymssql.connect(host = "127.0.0.1:1483",database = "shopclub",charset="utf8") # 建立连接
if connect:
print("连接成功!")
cursor = connect.cursor() # 创建一个游标对象,python里的sql语句都要通过cursor来执行
cursor.execute("create table Shop(shno varchar(20) primary key,shname varchar(20) not null,shaddress varchar(30) not null)")
cursor.execute("create table Good(gno varchar(20) primary key,gname varchar(20) not null,salprice int not null,inprice int not null)")
cursor.execute("create table Staff(stno varchar(20) primary key,stname char(20) not null,sex char(10) not null,wage int not null,pnum varchar(11) not null,shno varchar(20),wtime varchar(20),wsalary int,foreign key(shno) references shop(shno))")
cursor.execute("create table Shouse(hno varchar(20) primary key,saddress varchar(30) not null)")
cursor.execute("create table Admins(Id varchar(20) primary key,password varchar(20) not null,name char(10) not null,sex char(10) not null) ")
cursor.execute("create table SS(Id int not null,shno varchar(20),gno varchar(20),sdate varchar(20) not null,snumb int not null,primary key(Id,shno,gno),foreign key(shno) references Shop(shno),foreign key(gno) references Good(gno))")
cursor.execute("create table SH(gno varchar(20),hno varchar(20),shnumb int not null,primary key(gno,hno),foreign key(gno) references Good(gno),foreign key(hno) references Shouse(hno))")
connect.commit() #提交
cursor.close() # 关闭游标
connect.close() # 关闭连接
功能选择代码
import tkinter as tk
from mainpage import mainpage
from staffpage import staffpage
from sellpage import sellpage
from housepage import housepage
def housepp():
window1.destroy()
housepage()
def sellpp():
window1.destroy()
sellpage()
def staffpp():
window1.destroy()
staffpage()
def mainpp():
window1.destroy()
mainpage()
def chan_main():
window1.destroy()
def choicepage():
global window1
window1 = tk.Tk()
window1.title("连锁超市管理系统")
window1.geometry('600x600')
# 生成画布,销毁后生成新的画布实现跳转
page = tk.Frame(window1)
page.pack()
tk.Label(window1, text="欢迎使用连锁超市信息管理系统", font=("黑体", 20)).pack(pady=10)
button1 = tk.Button(window1, text="商品、库存信息管理", command=mainpp,height=5,width=30).pack(pady=10)
button2 = tk.Button(window1, text="仓库信息管理", command=housepp, height=5, width=30).pack(pady=10)
button2 = tk.Button(window1, text="职工信息管理", command=staffpp,height=5,width=30).pack(pady=10)
button3 = tk.Button(window1, text="销售管理", command=sellpp,height=5,width=30).pack(pady=10)
button4 = tk.Button(window1, text="退出", command=chan_main).pack(pady=10)
window1.mainloop()
if __name__ == '__main__':
choicepage()
主页
import datetime
from tkinter import ttk
import pymssql
import tkinter as tk
import tkinter.messagebox
#数据库库存添加操作
def kadd():
# 连接数据库
date=datetime.date.today()
print(date)
connect = pymssql.connect(host = "127.0.0.1:1483",database = "shopclub",charset="utf8")
# 创建光标
cursor = connect.cursor()
# 编写SQL语句
sql1 = "insert into SH(gno,hno,shnumb,SHTIME,intime) values('%s','%s','%s','%s','%s')" % (v91.get(), v92.get(),v93.get(),v94.get(),date)
# 执行SQL语句,并且输出完成提示信息,否则回滚
try:
cursor.execute(sql1)
connect.commit()
tkinter.messagebox.showinfo("提示", "数据添加成功")
except:
connect.rollback()
tkinter.messagebox.showinfo("提示", "商品编号不存在或仓库编号不存在")
# 关闭数据库连接,防止泄露
cursor.close()
connect.close()
#数据库仓库添加操作
def hadd():
# 连接数据库
connect = pymssql.connect(host = "127.0.0.1:1483",database = "shopclub",charset="utf8")
# 创建光标
cursor = connect.cursor()
# 编写SQL语句
sql2 = "insert into Shouse(hno,saddress) values('%s','%s')" % (v20.get(), v21.get())
# 执行SQL语句,并且输出完成提示信息,否则回滚
try:
cursor.execute(sql2)
connect.commit()
tkinter.messagebox.showinfo("提示", "数据添加成功")
except:
connect.rollback()
print(110)
# 关闭数据库连接,防止泄露
cursor.close()
connect.close()
#数据库添加操作
def gadd():
# 连接数据库
connect = pymssql.connect(host = "127.0.0.1:1483",database = "shopclub",charset="utf8")
# 创建光标
cursor = connect.cursor()
# 编写SQL语句
sql3 = "insert into Good(gno,gname,salprice,inprice) values('%s','%s','%s','%s')" % (v1.get(), v2.get(), v3.get(), v4.get())
# 执行SQL语句,并且输出完成提示信息,否则回滚
try:
cursor.execute(sql3)
connect.commit()
tkinter.messagebox.showinfo("提示", "数据添加成功")
except:
connect.rollback()
# 关闭数据库连接,防止泄露
cursor.close()
connect.close()
#数据库删除操作
def delete():
connect = pymssql.connect(host = "127.0.0.1:1483",database = "shopclub",charset="utf8")
cursor=connect.cursor()
sql4 = "delete from Good where gno='%s'" % (v10.get())
try:
cursor.execute(sql4)
connect.commit()
tkinter.messagebox.showinfo("提示","数据删除成功")
except:
connect.rollback()
tkinter.messagebox.showinfo("提示", "所删除的商品不存在")
cursor.close()
connect.close()
def update_good():
connect = pymssql.connect(host = "127.0.0.1:1483",database = "shopclub",charset="utf8")
cursor = connect.cursor()
sql33="update Good set gname='%s',salprice='%s',inprice='%s'where gno='%s'"%(v55.get(),v56.get(),v57.get(),v54.get())
try:
cursor.execute(sql33)
connect.commit()
tkinter.messagebox.showinfo("提示","数据更新成功!")
except:
connect.rollback()
tkinter.messagebox.showinfo("提示", "所更新的商品不存在")
cursor.close()
connect.close()
#查询商品信息和商品的库存信息查询
def select():
window_function.destroy()
global window_function2
connect1 = pymssql.connect(host = "127.0.0.1:1483",database = "shopclub",charset="cp936")
cursor = connect1.cursor()
sql6 = "select Good.gno,gname,salprice,SH.hno,shnumb,saddress,SHTIME,intime from Good join SH on Good.gno=SH.gno join Shouse on SH.hno=Shouse.hno and good.gno='%s' order by SHTIME ASC"%(v13.get())
try:
cursor.execute(sql6)
results = cursor.fetchall()
print(results)
fields = [field[0] for field in cursor.description]
res = [dict(zip(fields, result)) for result in results]
print(res)
index = len(res)
except:
return
cursor.close()
connect1.close()
# 生成窗口
window_function2 = tk.Tk()
# 窗口标题
window_function2.title("商品信息和商品的库存信息")
# 窗口大小
window_function2.geometry('1200x400')
def creat_page(self):
columns = ("gno", "gname", "salprice", "hno", "shnumb", "saddress","SHTIME","intime")
columns_values = ("商品编号", "商品名", "售价", "仓库编号", "库存", "仓库地址","生产时间","入库时间")
self.tree_view = ttk.Treeview(self,show='headings', columns=columns)
self.tree_view.column('gno', width=80, anchor='center')
self.tree_view.column('gname', width=80, anchor='center')
self.tree_view.column('salprice', width=80, anchor='center')
self.tree_view.column('hno', width=80, anchor='center')
self.tree_view.column('shnumb', width=80, anchor='center')
self.tree_view.column('saddress', width=80, anchor='center')
self.tree_view.column('SHTIME', width=80, anchor='center')
self.tree_view.column('intime', width=80, anchor='center')
self.tree_view.heading('gno', text='商品编号')
self.tree_view.heading('gname', text='商品名')
self.tree_view.heading('salprice', text='售价')
self.tree_view.heading('hno', text='仓库编号')
self.tree_view.heading('shnumb', text='库存')
self.tree_view.heading('saddress', text='仓库地址')
self.tree_view.heading('SHTIME', text='生产时间')
self.tree_view.heading('intime', text='入库时间')
self.tree_view.pack(fill=tk.BOTH, expand=True)
num = 0
while num < index:
self.tree_view.insert('', 'end', values=(
res[num]['gno'], res[num]['gname'], res[num]['salprice'], res[num]['hno'], res[num]['shnumb'], res[num]['saddress'], res[num]['SHTIME'],res[num]['intime']))
num = num + 1
button = tk.Button(window_function2, text="返回", command=cahn_main).place(relx=0.5, rely=0.8)
creat_page(self=window_function2)
def cahn_main():
window_function2.destroy()
Staff_select()
#添加仓库界面
def Staff_hadd():
#构建全集变量,方便上面的函数调用
global window_function
global v20,v21
#生成窗口
window_function=tk.Tk()
#窗口标题
window_function.title("连锁超市管理系统")
#窗口大小
window_function.geometry('400x300')
#生成标签
tk.Label(window_function, text="添加新仓库", font=("黑体", 20)).grid(row=0,column=1,pady=10)
tk.Label(window_function, text="请输入仓库编号:").grid(row=1, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入仓库地址:").grid(row=2, column=0, padx=20, pady=20)
# 定义变量记录输入信息
v20 = tk.StringVar()
v21 = tk.StringVar()
#输入框
entry1 = tk.Entry(window_function, show=None, textvariable=v20).grid(row=1, column=1)
entry2 = tk.Entry(window_function, show=None, textvariable=v21).grid(row=2, column=1)
#生成按钮
button = tk.Button(window_function, text="添加", command=hadd).place(relx=0.3,rely=0.9)
button2 = tk.Button(window_function, text="返回", command=chaneg_main).place(relx=0.5,rely=0.9)
#显示窗口
window_function.mainloop()
# 添加库存界面
def Staff_kadd():
# 构建全集变量,方便上面的函数调用
global window_function
global v91, v92, v93,v94
# 生成窗口
window_function = tk.Tk()
# 窗口标题
window_function.title("连锁超市管理系统")
# 窗口大小
window_function.geometry('400x500')
# 生成标签
tk.Label(window_function, text="添加库存", font=("黑体", 20)).grid(row=0, column=1, pady=10)
tk.Label(window_function, text="请输入商品编号:").grid(row=1, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入仓库编号:").grid(row=2, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入库存量:").grid(row=3, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入生成日期:").grid(row=4, column=0, padx=20, pady=20)
# 定义变量记录输入信息
v91 = tk.StringVar()
v92 = tk.StringVar()
v93 = tk.StringVar()
v94 = tk.StringVar()
# 生成输入框
entry1 = tk.Entry(window_function, show=None, textvariable=v91).grid(row=1, column=1)
entry2 = tk.Entry(window_function, show=None, textvariable=v92).grid(row=2, column=1)
entry3 = tk.Entry(window_function, show=None, textvariable=v93).grid(row=3, column=1)
entry4 = tk.Entry(window_function, show=None, textvariable=v94).grid(row=4, column=1)
# 生成按钮
button = tk.Button(window_function, text="添加", command=kadd).place(relx=0.3, rely=0.9)
button2 = tk.Button(window_function, text="返回", command=chaneg_main).place(relx=0.5, rely=0.9)
# 显示窗口
window_function.mainloop()
#添加商品界面
def Staff_gadd():
#构建全集变量,方便上面的函数调用
global window_function
global v1,v2,v3,v4
#生成窗口
window_function=tk.Tk()
#窗口标题
window_function.title("连锁超市管理系统")
#窗口大小
window_function.geometry('400x400')
#生成标签
tk.Label(window_function, text="添加新商品", font=("黑体", 20)).grid(row=0,column=1,pady=10)
tk.Label(window_function, text="请输入商品编号:").grid(row=1, column=0, padx=20, pady=20)
tk.Label(window_function,text="请输入商品名:").grid(row = 2,column =0,padx=20,pady=20)
tk.Label(window_function,text="请输入销售单价:").grid(row = 3,column =0,padx=20,pady=20)
tk.Label(window_function,text="请输入商品成本:").grid(row = 4,column =0,padx=20,pady=20)
#定义变量记录输入信息
v1 = tk.StringVar()
v2 = tk.StringVar()
v3 = tk.StringVar()
v4 = tk.StringVar()
#生成输入框
entry1 = tk.Entry(window_function,show=None,textvariable=v1).grid(row = 1,column =1)
entry2 = tk.Entry(window_function,show=None,textvariable=v2).grid(row = 2,column =1)
entry3 = tk.Entry(window_function,show=None,textvariable=v3).grid(row = 3,column =1)
entry4 = tk.Entry(window_function, show=None, textvariable=v4).grid(row=4, column=1)
#生成按钮
button = tk.Button(window_function, text="添加", command=gadd).place(relx=0.3,rely=0.9)
button2 = tk.Button(window_function, text="返回", command=chaneg_main).place(relx=0.5,rely=0.9)
#显示窗口
window_function.mainloop()
#删除商品界面
def Staff_delete():
global window_function
global v10
window_function=tk.Tk()
window_function.title("连锁超市管理系统")
window_function.geometry('400x250')
tk.Label(window_function, text="删除商品", font=("黑体", 20)).grid(row=0,column=1,pady=20)
tk.Label(window_function,text="请输入商品编号:").grid(row = 1,column =0,padx=20)
v10 =tk.StringVar()
entry1=tk.Entry(window_function,show=None,textvariable=v10).grid(row = 1,column =1,pady=40)
button = tk.Button(window_function, text="删除", command=delete,anchor = 's').place(relx=0.2,rely=0.7)
button2 = tk.Button(window_function, text="返回", command=chaneg_main).place(relx=0.4,rely=0.7)
window_function.mainloop()
def Staff_update():
global window_function
global v54,v55,v56,v57
window_function=tk.Tk()
window_function.title("连锁超市管理系统")
window_function.geometry('500x350')
tk.Label(window_function, text="更新商品信息", font=("黑体", 20)).grid(row=0,column=1,pady=20)
tk.Label(window_function,text="请输入商品编号:").grid(row = 1,column =0,padx=20,pady=20)
tk.Label(window_function,text="请输入商品名:").grid(row = 2,column =0,padx=20,pady=20)
tk.Label(window_function, text="请输入售价:").grid(row=3, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入成本:").grid(row=4, column=0, padx=20, pady=20)
v54=tk.StringVar()
v55=tk.StringVar()
v56=tk.StringVar()
v57=tk.StringVar()
entry1=tk.Entry(window_function,show=None,textvariable=v54).grid(row = 1,column =1)
entry2=tk.Entry(window_function,show=None,textvariable=v55).grid(row = 2,column =1)
entry3 = tk.Entry(window_function, show=None, textvariable=v56).grid(row=3, column=1)
entry4 = tk.Entry(window_function, show=None, textvariable=v57).grid(row=4, column=1)
button = tk.Button(window_function, text="更新", command=update_good).place(relx=0.2,rely=0.9)
button2 = tk.Button(window_function, text="返回", command=chaneg_main).place(relx=0.6,rely=0.9)
window_function.mainloop()
#条件查找商品信息和库存信息
def Staff_select():
global window_function
global v13
window_function=tk.Tk()
window_function.title("连锁超市管理系统")
window_function.geometry('600x200')
tk.Label(window_function, text="查找商品的商品的信息和库存信息", font=("黑体", 20)).grid(row=0,column=1,pady=20)
tk.Label(window_function,text="请输入商品编号:").grid(row = 1,column =0,padx=20)
v13 =tk.StringVar()
entry1=tk.Entry(window_function,show=None,textvariable=v13).grid(row = 1,column =1,pady=20)
button = tk.Button(window_function, text="查找", command=select).place(relx=0.3,rely=0.8)
button2 = tk.Button(window_function, text="返回", command=chaneg_main).place(relx=0.5,rely=0.8)
window_function.mainloop()
#添加库存界面跳转
def change_kadd():
#销毁画布
window.destroy()
#生成新界面
Staff_kadd()
#添加仓库界面跳转
def change_hadd():
#销毁画布
window.destroy()
#生成新界面
Staff_hadd()
#添加商品界面跳转
def change_gadd():
#销毁画布
window.destroy()
#生成新界面
Staff_gadd()
#删除商品界面跳转
def change_delete():
window.destroy()
Staff_delete()
#更新商品界面跳转
def changeg_update():
window.destroy()
Staff_update()
#条件查询商品界面跳转
def change_select():
window.destroy()
Staff_select()
#主界面跳转
def chaneg_main():
window_function.destroy()
mainpage()
def chan_main():
from choicepage import choicepage
window.destroy()
choicepage()
#主界面
def mainpage():
global window
window = tk.Tk()
window.title("连锁超市管理系统")
window.geometry('500x450')
#生成画布,销毁后生成新的画布实现跳转
page = tk.Frame(window)
page.pack()
tk.Label(window, text="欢迎使用连锁超市信息管理系统", font=("黑体", 20)).pack(pady=10)
button1 = tk.Button(window, text="添加商品信息", command=change_gadd).pack(pady=10)
button2 = tk.Button(window, text="删除商品信息", command=change_delete).pack(pady=10)
button2 = tk.Button(window, text="更新商品信息", command=changeg_update).pack(pady=10)
button3 = tk.Button(window, text="商品入库", command=change_kadd).pack(pady=10)
button5 = tk.Button(window, text="查找商品信息和库存信息", command=change_select).pack(pady=10)
button6 = tk.Button(window, text="退出", command=chan_main).pack(pady=10)
window.mainloop()
#主函数生成主界面
if __name__ == '__main__':
mainpage()
商品界面代码
from tkinter import ttk
import pymssql
import tkinter as tk
import tkinter.messagebox
def s_update():
connect = pymssql.connect(host="127.0.0.1:1483", database="shopclub", charset="utf8")
# 创建光标
cursor = connect.cursor()
# 编写SQL语句
sql = "update Staff set stname='%s',sex='%s',wage='%s',pnum='%s', shno='%s',wtime='%s',wsalary='%s'where stno='%s'" % ( v12.get(), v13.get(),v14.get(), v15.get(), v16.get(),v17.get(), v18.get(),v11.get())
# 执行SQL语句,并且输出完成提示信息,否则回滚
try:
cursor.execute(sql)
connect.commit()
tkinter.messagebox.showinfo("提示", "数据修改成功")
except:
connect.rollback()
print("error")
# 关闭数据库连接,防止泄露
cursor.close()
connect.close()
def add_update():
window.destroy()
global window_function
global v11, v12, v13, v14, v15, v16, v17, v18
# 生成窗口
window_function = tk.Tk()
# 窗口标题
window_function.title("连锁超市管理系统")
# 窗口大小
window_function.geometry('400x600')
# 生成标签
tk.Label(window_function, text="添加职工", font=("黑体", 20)).grid(row=0, column=1, pady=10)
tk.Label(window_function, text="请输入职工编号:").grid(row=1, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入姓名:").grid(row=2, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入性别:").grid(row=3, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入工龄:").grid(row=4, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入电话号码:").grid(row=5, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入商店编号:").grid(row=6, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入聘期:").grid(row=7, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入月薪:").grid(row=8, column=0, padx=20, pady=20)
# 定义变量记录输入信
v11 = tk.StringVar()
v12 = tk.StringVar()
v13 = tk.StringVar()
v14 = tk.StringVar()
v15 = tk.StringVar()
v16 = tk.StringVar()
v17 = tk.StringVar()
v18 = tk.StringVar()
# 生成输入框
entry1 = tk.Entry(window_function, show=None, textvariable=v11).grid(row=1, column=1)
entry2 = tk.Entry(window_function, show=None, textvariable=v12).grid(row=2, column=1)
entry3 = tk.Entry(window_function, show=None, textvariable=v13).grid(row=3, column=1)
entry4 = tk.Entry(window_function, show=None, textvariable=v14).grid(row=4, column=1)
entry5 = tk.Entry(window_function, show=None, textvariable=v15).grid(row=5, column=1)
entry6 = tk.Entry(window_function, show=None, textvariable=v16).grid(row=6, column=1)
entry7 = tk.Entry(window_function, show=None, textvariable=v17).grid(row=7, column=1)
entry8 = tk.Entry(window_function, show=None, textvariable=v18).grid(row=8, column=1)
# 生成按钮
button = tk.Button(window_function, text="修改", command=s_update).place(relx=0.3, rely=0.9)
button2 = tk.Button(window_function, text="返回", command=cahne_main).place(relx=0.5, rely=0.9)
# 显示窗口
window_function.mainloop()
def s_delet():
connect = pymssql.connect(host="127.0.0.1:1483", database="shopclub", charset="utf8")
cursor = connect.cursor()
sql4 = "delete from Staff where stno='%s'" % (v10.get())
try:
cursor.execute(sql4)
connect.commit()
tkinter.messagebox.showinfo("提示", "数据删除成功")
except:
connect.rollback()
cursor.close()
connect.close()
def Staff_delete():
window.destroy()
global window_function
global v10
window_function=tk.Tk()
window_function.title("连锁超市管理系统")
window_function.geometry('400x250')
tk.Label(window_function, text="删除员工", font=("黑体", 20)).grid(row=0,column=1,pady=20)
tk.Label(window_function,text="请输入员工编号:").grid(row = 1,column =0,padx=20)
v10 =tk.StringVar()
entry1=tk.Entry(window_function,show=None,textvariable=v10).grid(row = 1,column =1,pady=40)
button = tk.Button(window_function, text="删除", command=s_delet,anchor = 's').place(relx=0.2,rely=0.7)
button2 = tk.Button(window_function, text="返回", command=cahne_main).place(relx=0.4,rely=0.7)
window_function.mainloop()
def s_add():
connect = pymssql.connect(host="127.0.0.1:1483", database="shopclub", charset="utf8")
# 创建光标
cursor = connect.cursor()
# 编写SQL语句
sql = "insert into Staff(stno, stname, sex, wage, pnum, shno,wtime,wsalary) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (v1.get(), v2.get(), v3.get(),v4.get(), v5.get(), v6.get(),v7.get(), v8.get())
# 执行SQL语句,并且输出完成提示信息,否则回滚
try:
cursor.execute(sql)
connect.commit()
tkinter.messagebox.showinfo("提示", "数据添加成功")
except:
connect.rollback()
print("error")
# 关闭数据库连接,防止泄露
cursor.close()
connect.close()
def add_staf():
window.destroy()
global window_function
global v1, v2, v3, v4, v5, v6, v7, v8
# 生成窗口
window_function = tk.Tk()
# 窗口标题
window_function.title("连锁超市管理系统")
# 窗口大小
window_function.geometry('400x600')
# 生成标签
tk.Label(window_function, text="添加职工", font=("黑体", 20)).grid(row=0, column=1, pady=10)
tk.Label(window_function, text="请输入职工编号:").grid(row=1, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入姓名:").grid(row=2, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入性别:").grid(row=3, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入工龄:").grid(row=4, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入电话号码:").grid(row=5, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入商店编号:").grid(row=6, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入聘期:").grid(row=7, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入月薪:").grid(row=8, column=0, padx=20, pady=20)
# 定义变量记录输入信
v1 = tk.StringVar()
v2 = tk.StringVar()
v3 = tk.StringVar()
v4 = tk.StringVar()
v5 = tk.StringVar()
v6 = tk.StringVar()
v7 = tk.StringVar()
v8 = tk.StringVar()
# 生成输入框
entry1 = tk.Entry(window_function, show=None, textvariable=v1).grid(row=1, column=1)
entry2 = tk.Entry(window_function, show=None, textvariable=v2).grid(row=2, column=1)
entry3 = tk.Entry(window_function, show=None, textvariable=v3).grid(row=3, column=1)
entry4 = tk.Entry(window_function, show=None, textvariable=v4).grid(row=4, column=1)
entry5 = tk.Entry(window_function, show=None, textvariable=v5).grid(row=5, column=1)
entry6 = tk.Entry(window_function, show=None, textvariable=v6).grid(row=6, column=1)
entry7 = tk.Entry(window_function, show=None, textvariable=v7).grid(row=7, column=1)
entry8 = tk.Entry(window_function, show=None, textvariable=v8).grid(row=8, column=1)
# 生成按钮
button = tk.Button(window_function, text="添加", command=s_add).place(relx=0.3, rely=0.9)
button2 = tk.Button(window_function, text="返回", command=cahne_main).place(relx=0.5, rely=0.9)
# 显示窗口
window_function.mainloop()
def cahne_main():
window_function.destroy()
staffpage()
def chan_main():
from choicepage import choicepage
window.destroy()
choicepage()
def staffpage():
connect1 = pymssql.connect(host="127.0.0.1:1483", database="shopclub", charset="cp936")
cursor1 = connect1.cursor()
sql6 = "select stno, stname, sex, wage, pnum, shno,wtime,wsalary from Staff"
try:
cursor1.execute(sql6)
results = cursor1.fetchall()
print(results)
fields = [field[0] for field in cursor1.description]
res = [dict(zip(fields, result)) for result in results]
print(res)
index=len(res)
except:
return
cursor1.close()
connect1.close()
global window
window = tk.Tk()
window.title("连锁超市管理系统")
window.geometry('1000x600')
# 生成画布,销毁后生成新的画布实现跳转
page = tk.Frame(window)
page.pack()
tk.Label(window, text="员工管理", font=("黑体", 20)).pack(pady=10)
def creat_pagee(self):
columns = ("stno", "stname", "sex", "wage", "pnum", "shno","wtime","wsalary")
columns_values = ("职工编号", "姓名", "性别", "工龄", "电话号码", "商店编号","聘期","月薪")
self.tree_view = ttk.Treeview(self, show='headings', columns=columns)
self.tree_view.column('stno', width=80, anchor='center')
self.tree_view.column('stname', width=80, anchor='center')
self.tree_view.column('sex', width=80, anchor='center')
self.tree_view.column('wage', width=80, anchor='center')
self.tree_view.column('pnum', width=80, anchor='center')
self.tree_view.column('shno', width=80, anchor='center')
self.tree_view.column('wtime', width=80, anchor='center')
self.tree_view.column('wsalary', width=80, anchor='center')
self.tree_view.heading('stno', text='职工编号')
self.tree_view.heading('stname', text='姓名')
self.tree_view.heading('sex', text='性别')
self.tree_view.heading('wage', text='工龄')
self.tree_view.heading('pnum', text='电话号码')
self.tree_view.heading('shno', text='商店编号')
self.tree_view.heading('wtime', text='聘期')
self.tree_view.heading('wsalary', text='月薪')
self.tree_view.pack(fill=tk.BOTH, expand=True)
num=0
while num<index:
self.tree_view.insert('', 'end', values=(
res[num]['stno'], res[num]['stname'], res[num]['sex'], res[num]['wage'], res[num]['pnum'], res[num]['shno'],res[num]['wtime'], res[num]['wsalary']))
num=num+1
button = tk.Button(window, text="退出", command=chan_main).place(relx=0.9, rely=0.03)
button = tk.Button(window, text="添加员工", command=add_staf).place(relx=0.02, rely=0.03)
button = tk.Button(window, text="更新员工信息", command=add_update).place(relx=0.2, rely=0.03)
button = tk.Button(window, text="删除员工", command=Staff_delete).place(relx=0.1, rely=0.03)
creat_pagee(self=window)
if __name__ == '__main__':
staffpage()
销售界面代码
from tkinter import ttk
import pymssql
import tkinter as tk
import tkinter.messagebox
def s_add():
connect = pymssql.connect(host="127.0.0.1:1483", database="shopclub", charset="utf8")
# 创建光标
cursor = connect.cursor()
# 编写SQL语句
sql = "insert into SS(shno, gno, sdate, snumb) values('%s','%s','%s','%s')" % (v1.get(), v2.get(), v3.get(),v4.get())
# 执行SQL语句,并且输出完成提示信息,否则回滚
try:
cursor.execute(sql)
connect.commit()
tkinter.messagebox.showinfo("提示", "数据添加成功")
except:
connect.rollback()
tkinter.messagebox.showinfo("提示", "库存数量无法满足订单需求")
print("error")
# 关闭数据库连接,防止泄露
cursor.close()
connect.close()
def SELECT_hist():
window.destroy()
connect1 = pymssql.connect(host="127.0.0.1:1483", database="shopclub", charset="cp936")
cursor1 = connect1.cursor()
sql = "select ID,shno, gno, sdate, snumb from SS "
try:
cursor1.execute(sql)
results = cursor1.fetchall()
print(results)
fields = [field[0] for field in cursor1.description]
res = [dict(zip(fields, result)) for result in results]
print(res)
index = len(res)
except:
return
cursor1.close()
connect1.close()
global window_function
window_function= tk.Tk()
window_function.title("连锁超市管理系统")
window_function.geometry('600x400')
tk.Label(window_function, text="历史订单记录", font=("黑体", 20)).pack(pady=10)
def creat_page(self):
columns = ("ID", "shno", "gno", "sdate", "snumb")
columns_values = ("ID", "商店编号", "商品编号", "交易日期", "交易数量")
self.tree_view = ttk.Treeview(self, show='headings', columns=columns)
self.tree_view.column('ID', width=80, anchor='center')
self.tree_view.column('shno', width=80, anchor='center')
self.tree_view.column('gno', width=80, anchor='center')
self.tree_view.column('sdate', width=80, anchor='center')
self.tree_view.column('snumb', width=80, anchor='center')
self.tree_view.heading('ID', text='ID')
self.tree_view.heading('shno', text='商店编号')
self.tree_view.heading('gno', text='商品编号')
self.tree_view.heading('sdate', text='交易日期')
self.tree_view.heading('snumb', text='交易数量')
self.tree_view.pack(fill=tk.BOTH, expand=True)
num = 0
while num < index:
self.tree_view.insert('', 'end', values=(
res[num]['ID'], res[num]['shno'], res[num]['gno'], res[num]['sdate'], res[num]['snumb']))
num = num + 1
button9 = tk.Button(window_function, text="退出", command=cahne_main).place(relx=0.9, rely=0.03)
creat_page(self=window_function)
def add_sell():
window.destroy()
global window_function
global v1, v2, v3, v4
# 生成窗口
window_function = tk.Tk()
# 窗口标题
window_function.title("连锁超市管理系统")
# 窗口大小
window_function.geometry('500x400')
# 生成标签
tk.Label(window_function, text="销售商品", font=("黑体", 20)).grid(row=0, column=1, pady=10)
tk.Label(window_function, text="请选择销售商店:").grid(row=1, column=0, padx=20, pady=20)
tk.Label(window_function, text="请选择销售商品:").grid(row=2, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入销售日期:").grid(row=3, column=0, padx=20, pady=20)
tk.Label(window_function, text="请输入销售数量:").grid(row=4, column=0, padx=20, pady=20)
# 定义变量记录输入信
v1 = tk.StringVar()
v2 = tk.StringVar()
v3 = tk.StringVar()
v4 = tk.StringVar()
# 生成输入框
entry1 = tk.Entry(window_function, show=None, textvariable=v1).grid(row=1, column=1)
entry2 = tk.Entry(window_function, show=None, textvariable=v2).grid(row=2, column=1)
entry3 = tk.Entry(window_function, show=None, textvariable=v3).grid(row=3, column=1)
entry4 = tk.Entry(window_function, show=None, textvariable=v4).grid(row=4, column=1)
# 生成按钮
button = tk.Button(window_function, text="添加", command=s_add).place(relx=0.3, rely=0.9)
button2 = tk.Button(window_function, text="返回", command=cahne_main).place(relx=0.5, rely=0.9)
# 显示窗口
window_function.mainloop()
def cahne_main():
window_function.destroy()
sellpage()
def chan_main():
from choicepage import choicepage
window.destroy()
choicepage()
def sellpage():
connect1 = pymssql.connect(host="127.0.0.1:1483", database="shopclub", charset="cp936")
cursor1 = connect1.cursor()
sql6 = "select A1.gno,A1.hno,SUM(A2.shnumb) AS SHNUMB from SH AS A1,SH AS A2 WHERE A1.gno=A2.gno AND A1.hno=A2.hno AND A1.SHID=A2.SHID GROUP BY A1.gno,A1.hno "
try:
cursor1.execute(sql6)
results = cursor1.fetchall()
print(results)
fields = [field[0] for field in cursor1.description]
res = [dict(zip(fields, result)) for result in results]
print(res)
index = len(res)
except:
return
cursor1.close()
connect1.close()
global window
window = tk.Tk()
window.title("连锁超市管理系统")
window.geometry('500x400')
tk.Label(window, text="销售管理", font=("黑体", 20)).pack(pady=10)
def creat_page(self):
columns = ("gno", "hno", "SHNUMB")
columns_values = ("商品编号" "仓库编号", "库存")
self.tree_view = ttk.Treeview(self,show='headings', columns=columns)
self.tree_view.column('gno', width=80, anchor='center')
self.tree_view.column('hno', width=80, anchor='center')
self.tree_view.column('SHNUMB', width=80, anchor='center')
self.tree_view.heading('gno', text='商品编号')
self.tree_view.heading('hno', text='仓库编号')
self.tree_view.heading('SHNUMB', text='库存')
self.tree_view.pack(fill=tk.BOTH, expand=True)
num = 0
while num < index:
self.tree_view.insert('', 'end', values=(
res[num]['gno'], res[num]['hno'], res[num]['SHNUMB']))
num = num + 1
button = tk.Button(window, text="退出", command=chan_main).place(relx=0.9, rely=0.03)
button2 = tk.Button(window, text="销售商品", command=add_sell).place(relx=0.02, rely=0.03)
button3 = tk.Button(window, text="查询历史订单", command=SELECT_hist).place(relx=0.2, rely=0.03)
creat_page(self=window)
if __name__ == '__main__':
sellpage()