import pymssql

import tkinter as tk

import tkinter.messagebox

# 数据库添加操作

def add():

     # 连接数据库

     connect = pymssql.connect(host = "127.0.0.1:1433",database = "Manager",charset="utf8")

     # 创建光标

     cursor = connect.cursor()

     # 编写 SQL 语句

     sql = "insert into Goods(Gnum,Gname,Gtype,Gprice,Gbid,Gstock,Galarm,Gplan,Vnum) values('%s','%s','%s','%s','%s','%s','%s','%s',%s)" % (v1.get(), v2.get(), v3.get(), v4.get(), v5.get(), v6.get(), v7.get(), v8.get(), v9.get())

     # 执行 SQL 语句，并且输出完成提示信息，否则回滚

     try:

         cursor.execute(sql)

         connect.commit()

         tkinter.messagebox.showinfo(" 提示 ", " 数据添加成功 ")

     except:

         connect.rollback()

     # 关闭数据库连接，防止泄露

     connect.close()

# 数据库删除操作

def delete():

     connect = pymssql.connect(host = "127.0.0.1:1433",database = "Manager",charset="utf8")

     cursor=connect.cursor()

     sql = "delete from Goods where Gnum='%s'" % (v10.get())

     try:

         cursor.execute(sql)

         connect.commit()

         tkinter.messagebox.showinfo(" 提示 "," 数据删除成功 ")

     except:

         connect.rollback()

     connect.close()

# 数据库更新操作

def update():

     connect = pymssql.connect(host = "127.0.0.1:1433",database = "Manager",charset="utf8")

     cursor = connect.cursor()

     sql="update Goods set Gstock='%s' where Gnum='%s'"%(v11.get(),v12.get())

     try:

         cursor.execute(sql)

         connect.commit()

         tkinter.messagebox.showinfo(" 提示 "," 数据更新成功！ ")

     except:

         connect.rollback()

     connect.close()

# 数据库模糊条件查询

def select():

     connect = pymssql.connect(host = "127.0.0.1:1433",database = "Manager",charset="utf8")

     cursor =跟单网gendan5.com connect.cursor()

     sql = "select Vname from Vendor,Goods where Goods.Vnum=Vendor.Vnum and Goods.Gnum like'%s'"%('%'+v13.get()+'%')

     try:

         cursor.execute(sql)

         results = cursor.fetchall()

         for row in results:

             Vname = row[0]

             tkinter.messagebox.showinfo(" 提示 ","Vname=%s" % (Vname))

     except:

         return

# 添加商品界面

def Staff_add():

     # 构建全集变量，方便上面的函数调用

     global window_function

     global v1,v2,v3,v4,v5,v6,v7,v8,v9

     # 生成窗口

     window_function=tk.Tk()

     # 窗口标题

     window_function.title(" 超市零售信息管理系统 ")

     # 窗口大小

     window_function.geometry('400x700')

     # 生成标签

     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)

     tk.Label(window_function, text=" 请输入供货商编号： ").grid(row=9, 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()

     v9 = 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)

     entry9 = tk.Entry(window_function, show=None, textvariable=v9).grid(row=9, column=1)

     # 生成按钮

     button = tk.Button(window_function, text=" 添加 ", command=add).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('500x400')

     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.5)

     button2 = tk.Button(window_function, text=" 返回 ", command=chaneg_main).place(relx=0.4,rely=0.5)

     window_function.mainloop()

# 更新商品信息界面

def Staff_update():

     global window_function

     global v11,v12

     window_function=tk.Tk()

     window_function.title(" 超市零售信息管理系统 ")

     window_function.geometry('500x400')

     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)

     v11=tk.StringVar()

     v12=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)

     button = tk.Button(window_function, text=" 更新 ", command=update).place(relx=0.3,rely=0.5)

     button2 = tk.Button(window_function, text=" 返回 ", command=chaneg_main).place(relx=0.5,rely=0.5)

     window_function.mainloop()

# 条件查找商品界面

def Staff_select():

     global window_function

     global v13

     window_function=tk.Tk()

     window_function.title(" 超市零售信息管理系统 ")

     window_function.geometry('500x400')

     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=40)

     button = tk.Button(window_function, text=" 查找 ", command=select).place(relx=0.3,rely=0.5)

     button2 = tk.Button(window_function, text=" 返回 ", command=chaneg_main).place(relx=0.5,rely=0.5)

     window_function.mainloop()

# 添加商品界面跳转

def change_add():

     # 销毁画布

     window.destroy()

     # 生成新界面

     Staff_add()

# 删除商品界面跳转

def change_delete():

     window.destroy()

     Staff_delete()

# 更新商品界面跳转

def change_update():

     window.destroy()

     Staff_update()

# 条件查询商品界面跳转

def change_select():

     window.destroy()

     Staff_select()

# 主界面跳转

def chaneg_main():

     window_function.destroy()

     mainpage()

# 主界面

def mainpage():

     global window

     window = tk.Tk()

     window.title(" 超市零售信息管理系统 ")

     window.geometry('500x400')

     # 生成画布，销毁后生成新的画布实现跳转

     page = tk.Frame(window)

     page.pack()

     tk.Label(window, text=" 欢迎使用超市零售信息管理系统 ", font=(" 黑体 ", 20)).pack(pady=10)

     button1 = tk.Button(window, text=" 添加商品信息 ", command=change_add).pack(pady=10)

     button2 = tk.Button(window, text=" 删除商品信息 ", command=change_delete).pack(pady=10)

     button3 = tk.Button(window, text=" 修改商品信息 ", command=change_update).pack(pady=10)

     button4 = tk.Button(window, text=" 查找商品供货商名称 ", command=change_select).pack(pady=10)

     window.mainloop()

# 主函数生成主界面

if __name__ == '__main__':

     mainpage()