基于opengauss数据库的酒水销售管理系统

摘要

摘 要: 本实验是一个基于opengauss数据库的酒水管理系统，可以用于管理某个酒庄或酒窖，拥有会员登记、酒水登记、酒具登记、会员管理、酒水管理、酒具管理、订单生成等功能等。
关键词:opengauss；数据库；管理系统

1 系统概述

1.1 开发目的

随着酒水销售记录日益增多，如果仍然采用旧的人工方式去统计，是不能及时发现和纠正各种原因造成的错误，会造成不可估计的损失。商品的数量繁多，何时销售、销售的数量多少，影响着上层的决策。譬如，何时订购货物、订购多少，这是极为重要的。一段时期，商品销售数量的查询、统计，其工作量大，旧的人工方式很难做到没有失误。销售数量和效率的提高，与合理的管理方式有着密切的联系。所以，酒水销售管理系统的设计是必要的。

1.2 开发任务

该系统开发任务由编辑数据、信息增删改查、生成订单、退出系统四个模块组成，系统可以方便的查询酒水销售的相关信息、商品的基本信息，自动筛选出某段时间的商品销售信息，按各种方式查询数据，打印和预览商品的价格、雇员销售数量、商品销售金额汇总等，实现酒水销售管理系统的简单化，规范化，合理化，科学化。
系统用户界面设计包括系统输入设计，输出设计和人机界面设计为管理者提供简洁明了、有效使用的管理和控制信息。输出的内容根据调查和分析用户在使用信息方面的要求，输出格式满足使用者的要求和习惯，做到格式标准化，术语统一化，不仅清晰、美观，而且易于阅读，理解和用计算机实现。输入设计在保证输入信息正确性和满足输出需要的前提下，做到输入简便、迅速、经济。人机对话使用户亲自参与系统操作中来，提高用户对系统的满意程度。

1.3 开发环境

硬件系统:
（1）一台PC
（2）一台华为弹性云服务器
软件系统:
（1）PC操作系统为Window10，装有python3.10软件、psycopg2依赖包
（2）华为弹性云服务器操作系统为openEuler，装有opengauss数据库

2 需求分析

信息系统开发是一项系统工程，系统开发之前先对系统进行需求分析是必不可少的工作。需求分析是信息系统开发工作的第一阶段。在这个阶段，通过对酒水销售管理业务进行调查分析，理清原来系统的业务流程和管理模式，找出其中的问题和不足，提出对这些问题与不足的解决方案，是管理信系统开发的一个重要环节，这个阶段的目标就是对用户需求进行定义，提出新系统的逻辑解决方案。其本质就是通过对现有系统的描述和分析来回答未来系统要做什么的问题。

2.1 系统需求

本系统目标是对会员信息的管理和酒水、酒具销售商品的管理，也就是说这个系统要做的是管理酒水销售的相关工作，所以系统必须提供:商品的基本信息以及会员的相关信息、按各种方式查询数据、打印和预览商品信息、会员购买信息等。

2.2 实现目标

（1）实现对数据库数据进行添加、修改、删除等基本操作
（2）支持对数据信息的查询
（3）支持将数据信息以订单形式打印

2.3 功能设计

图1 系统的功能结构图

2.4 数据需求

基于系统需求分析，该系统需要实现以下基本功能：
(1) 会员信息的输入、查询、修改、删除，包括编号、姓名、性别、住址、联系电话。
(2) 酒水信息的输入、查询、修改、删除，包括酒水编号、酒水类型、单价、年份、产地、颜色、是否售出。
(3) 酒具信息的输入、查询、修改、删除，包括数据的产品编号、产品名称、单价、供货商。
(4) 酒水销售信息的输入、查询、修改、删除，包括订单号、销售日期、会员编号、酒水编号。
(5) 酒具销售信息，包括酒具的订单号、销售日期、会员编号、酒具编号。
(6) 管理员信息，用于登录，包括管理员账号、密码 。

2.5 数据字典

2.5.1 会员信息

表1 会员信息

2.5.2 酒水信息

表2 酒水信息

2.5.3 酒水用品信息

表3 酒水用品信息

2.5.4 酒水销售信息

表4 酒水销售信息

2.5.5 酒水用品销售信息

表5 酒具销售信息

2.5.6 管理员

表6 管理员信息

3 概念结构设计

3.1 E-R图

各实体属性如下：
会员：属性有编号、姓名、性别、住址、联系电话
酒水：属性有产品编号、产品名称、单价、供货商
酒具：属性有产品编号、产品名称、单价、供货商
实体之间的联系如下：
会员可以购买酒水，购买酒具；
E-R图如下：

4 逻辑结构设计

4.1 关系模型

根据E-R图转换成如下关系模型：
会员（编号，姓名，性别，住址，联系电话）
酒水（产品编号，产品名称，单价，供货商）
酒具（产品编号，产品名称，单价，供货商）
酒水销售（订单号，销售日期，会员编号，酒水编号）
酒具销售（订单号，销售日期，会员编号，酒具编号）
管理员（管理员账号，密码）

4.2 细化表结构

4.2.1 会员信息表
表名：client
表1 会员信息表

4.2.2 酒水信息表
表名：wine
表2 酒水信息表

4.2.3 酒水用品信息表
表名：sets
表3 酒水用品信息表

4.2.4 酒水销售信息表
表名：sale
表4 酒水销售信息表

4.2.5 酒水用品销售信息表
表名：setsale
表5 酒具销售信息表

4.2.6 管理员表
表名：admin
表6 管理员信息表

5 物理结构设计

5.1 数据库设置

openGauss是一款开源关系型数据库管理系统，采用木兰宽松许可证v2发行。openGauss内核源自PostgreSQL，深度融合华为在数据库领域多年的经验，结合企业级场景需求，持续构建竞争力特性。同时openGauss也是一个开源、免费的数据库平台，鼓励社区贡献、合作。所以我选择基于华为云鲲鹏弹性云服务器ECS部署openGauss数据库，通过一系列配置在ecs服务器上下载安装openGauss数据库。

6 数据库实施

创建Lsms数据库将所有权赋给gem（事先创建的用户）；

Create database lsms owner gem;
1

6.2 建立基本表

先在openauss数据库中建立六个表,分别是会员信息表、酒水信息表、酒具信息表、酒水销售信息表、酒具销售信息表、管理员信息表，如下图
使用SQL语句创建表

/*SQL语句*/
/*会员信息表*/
CREATE TABLE member(
  mno VARCHAR(10)  PRIMARY KEY, /*会员编号*//*con主码*/
  mname VARCHAR(45)  NOT NULL, /*姓名*/
  msex VARCHAR(10)   , /*性别*/
  maddress VARCHAR(45)   , /*住址*/
  mphone VARCHAR(12)    /*电话*/
  );
  
/*酒水信息表*/
CREATE TABLE wine(
  wno 	VARCHAR(10)	PRIMARY KEY,	/*酒水编号 pno/*主码*/*/
  wtype	 VARCHAR(10)	NOT NULL,		/*酒水类型*/
  wprice 	FLOAT  	,		/*单价*/
  wyear 	INT  	,		/*年份*/
  waddress	 VARCHAR(45)	,		/*产地*/
  wcolor	 VARCHAR(10)	,		/*颜色*/
  wsaled	 VARCHAR(12)	NOT NULL	/*酒水是否售出*/
  
  );

/*酒具信息表*/
CREATE TABLE sets (
  sno	 VARCHAR(10)  	PRIMARY KEY,/* 产品编号(uno)/*主码*/*/	
  sname	 VARCHAR(45)	NOT NULL,		/*产品名称*/
  sprice	 FLOAT	 NOT  NULL,	/*单价*/
  sprovider	 VARCHAR (45)	/*供货商*/
  );
  
/* 酒水销售信息表*/
CREATE TABLE sale(
  sano	 VARCHAR (20)	NOT NULL,	/*订单号*/
  mno 	VARCHAR (10)	NOT NULL,	/*	会员编号*/
  wno	 VARCHAR (10)	 NOT NULL,	/*酒水编号*/ 	
  sadate	 VARCHAR (20) 	NOT  NULL,		/*销售日期*/
  PRIMARY  KEY (sano,mno,wno)/*主码*/
  );
  
/*酒具销售信息表*/
CREATE TABLE salesets(
  sano 	VARCHAR(20) 	NOT NULL,	/*订单号*/ 
  mno 	VARCHAR(10)	 NOT NULL,	/*会员编号*/ 	
  sno	 VARCHAR(10)	 NOT NULL,	/*酒具编号*/ 
  sadate	 VARCHAR (20) 	NOT NULL,		/*销售日期*/
  PRIMARY KEY(sano,mno,sno)/*主码*/
  );

/*管理员信息表*/
CREATE TABLE admin(
  ruser	 INT   	PRIMARY KEY,		/*账号(ruser)/*主码*/*/ 
  rpassword 	VARCHAR(45)	 NOT NULL	/*	密码*/
  );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

查看表建立成功

7 数据库的运行和维护

数据库运行举例如下

7.1 数据增加

增加会员信息

insert into member values('4', '刘明', '男', '天津', '13695049658');
1

增加酒水信息

insert into wine values('4', '米酒', '39', '2022', '安徽', '', '否');
1

增加酒具信息

insert into sets values('4', '酒壶', '99', '南京万豪酒壶制造商');
1

增加订单信息

insert into sale values('20220624012128', '4', '4', '2022-06-24');
update wine set wsaled='是' where wno='4'       /*销售完成更新酒水信息*/
1
2

7.2 数据删除

删除会员信息

delete from member where mno='3';
1

删除酒水信息

delete from wine where wno='2';
1

删除酒具信息

delete from sets where sno='3';
1

删除订单信息

delete from sale where sano='20220623012246';
1

7.3 数据修改

修改会员信息

update member set mname='张亮', msex='男', maddress='南京', mphone='17839596173' where mno='1';
1

修改酒水信息

update wine set wtype='威士忌', wprice=1399, wyear=2021, waddress='加拿大', wcolor='棕色' where wno='1';
1

修改酒具信息

update sets set sname='迷你开瓶器', sprice='89', sprovider='永康市威武酒具长' where sno='2';
1

修改订单信息

update sale set sadate='2022-05-19', mno='1', wno='4' where sano= '20220624012128';
1

7.4 数据排序

按年份排序

SELECT * FROM wine order by wyear;
1

按单价排序

SELECT * FROM wine order by wprice;
1

7.5 数据查询

查询全部会员信息

SELECT * FROM member;
1

查询全部酒水信息

SELECT * FROM wine;
1

查询全部酒具信息

SELECT * FROM sets;
1

查询全部订单信息

select member.mno, mname, msex, maddress, mphone, wine.wno, wtype, wprice, wyear, waddress, wcolor, sano, sadate from member, wine, sale where member.mno = sale.mno and wine.wno = sale.wno
1

查询会员叫刘明的信息

SELECT * FROM member where mname like '%刘明%'
1

查询单价大于200小于2000的产品信息

SELECT * FROM member where mname like '%刘明%'
1

查询类型为威士忌的酒

select * from wine where wtype='威士忌'；
1

查询未售出的酒

SELECT * FROM wine where wsaled='否'；
1

等等… …

7.6 使用python操作

连接数据库

def get_connect():  # 获取连接
    connect = psycopg2.connect(dbname="lsms",
                            user="gem",#r_1
                            pw="XXXXXX000",#rrrr_1111
                            host="XXX.XX.XXX.XXX",
                            port="26000")
    connect.set_client_encoding('utf8')
    return connect
1
2
3
4
5
6
7
8

数据插入
例

def confirm():
        global string_time
        sql = "insert into sale values('%s', '%s', '%s', '%s');"
        print(sql)
        data = (string_time, cmb_c.get(), cmb_p.get(), time.strftime("%Y-%m-%d", time.localtime()))
        #print(sql % data)
        try:
            execute_sql(sql % data)
            tkinter.messagebox.showinfo("SUCCEED", "添加成功")
            execute_sql("update wine set wsaled='是' where wno='{}'".format(cmb_p.get()))
        except psycopg2.Error:
            tkinter.messagebox.showerror("Failed", "添加失败")
1
2
3
4
5
6
7
8
9
10
11
12

数据删除
例

def delete():  # 删除酒水信息
        if is_select():
            if tkinter.messagebox.askokcancel('警告', '确认删除该酒水信息吗？'):
                for elem in tree.selection():
                    try:
                        execute_sql("delete from wine where wno='{}'".format(tree.item(elem, 'values')[0]))
                        tkinter.messagebox.showinfo('Succeed', '删除成功！')
                        show_all()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror('Failed', '删除失败，请先删除该酒水的销售记录!')
        else:
            tkinter.messagebox.showerror('ERROR', '未选择酒水！')
1
2
3
4
5
6
7
8
9
10
11
12

数据更新
例

def confirm():
                    sql_update = "update sale set sadate='%s', mno='%s', wno='%s' where sano= '%s'"
                    data = (labels[3].get(), cmb_c.get(), cmb_p.get(), tree.item(elem, 'values')[11])
                    try:
                        execute_sql(sql_update % data)
                        tkinter.messagebox.showinfo("SUCCEED", "修改成功")
                        execute_sql("update wine set wsaled='是' where wno='{}'".format(cmb_p.get()))
                        win.destroy()
                        show_all()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror("Failed", "修改失败")
                        win.focus()
1
2
3
4
5
6
7
8
9
10
11
12

数据排序
例

def price_sort():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM sets order by sprice"):
            tree.insert('', 'end', values=new_row)
1
2
3
4

数据查询
例

def name_search(event):
        [tree.delete(item) for item in tree.get_children()]
        sql = "SELECT * FROM sets where sname like '%{}%'"
        for row_search in execute_sql(sql.format(label_list[3].get())):
            tree.insert('', 'end', values=row_search)
1
2
3
4
5

8 管理系统的运行展示

8.1 登录页面

登录后进入另一个页面

8.2 增加操作

8.2.1 增加会员

8.2.2 增加酒水

8.2.3 增加酒具

8.2.4 增加订单

8.3 查询操作

8.3.1 查询会员

8.3.2 查询酒水

按单价排序

8.3.3 查询酒具

8.3.4 查询订单

按单价排序

8.4 修改操作

8.4.1 修改会员

修改之后

8.4.2 修改酒水

修改之后

修改酒具、修改订单同理

8.5 删除操作

8.5.1 删除会员

8.5.2 删除订单

删除酒具、删除酒水同理

8.6 退出界面

附录一

有图形界面的酒水销售管理系统
Lsms.py 完整代码

from ctypes import alignment
from tkinter import *
import tkinter.messagebox
from tkinter import ttk
from turtle import bgcolor
import psycopg2
import sys
import time

def center(window, w, h):  # 设置窗口大小且居中
    ws = window.winfo_screenwidth()
    hs = window.winfo_screenheight()
    x = (ws / 2) - (w / 2)
    y = (hs / 2) - (h / 2)
    window.geometry("{:.0f}x{:.0f}+{:.0f}+{:.0f}".format(w, h, x, y))
def get_connect():  # 获取连接
    connect = psycopg2.connect(dbname="lsms",
                            user="gem",#r_1
                            pw ="XXXXXXl000",#rrrr_1111
                            host="XXX.XX.XXX.XXX",
                            port="26000")
    connect.set_client_encoding('utf8')
    return connect
def execute_sql(sql):  # 执行SQL语句并返回执行结果的游标
    print(sql)
    connect = get_connect()
    cursor = connect.cursor()
    cursor.execute(sql)
    connect.commit()
    return cursor
class Main(Frame):
    """主窗口"""
    flag = None
    def __init__(self, master):
        Frame.__init__(self, master)
        self.pack(fill=BOTH, expand=YES)
        self.master.geometry("1200x800")
        self.master.title("酒水销售管理系统")
        self.frame_top = Frame(self)
        self.frame_top.pack(side="top", fill=X)
        Label(self.frame_top, text="酒水销售管理系统", font=("微软雅黑", 30), fg='black', bg='#9DC3E7').pack(fill=X)
        self.frame_bottom = Frame(self)  # 下方frame
        self.frame_bottom.pack(side="bottom", fill=BOTH, expand=YES)
        frame_left = FrameLeft(self.frame_bottom)  # 下方左边
        frame_left.pack(side="left", fill=Y)
        self.frame_right = Home(self.frame_bottom)  # 下方右边
        self.frame_right.pack(side="right", fill=BOTH, expand=YES)
def go_home(win):  # 返回主页
    win.frame_right.destroy()
    win.frame_right = Home(Main.flag.frame_bottom)
    win.frame_right.pack(side="right", fill=BOTH, expand=YES)
def search_member(window):  # 查询， 查看会员信息
    window.frame_right.destroy()
    window.frame_right = Frame(Main.flag.frame_bottom)
    window.frame_right.pack(side="right", fill=BOTH, expand=YES)
    frame_top = Frame(window.frame_right)
    frame_top.pack(side='top', fill=BOTH)
    frame_bottom = Frame(window.frame_right)
    frame_bottom.pack(side='bottom', fill=BOTH, expand=YES)
    Label(frame_top, text='查询查看会员信息：', font=('楷体', 18), fg='blue').pack(side='left', fill=BOTH, expand=YES)
    label_list = [Label(frame_top, text='会员编号:'), Entry(frame_top), Label(frame_top, text='会员姓名:'),
                  Entry(frame_top)]
    for label in label_list:
        label.pack(side="left", fill=X, expand=YES, padx=3, pady=2)
    scrollbar = Scrollbar(frame_bottom)
    scrollbar.pack(side='right', fill=Y)
    tree = ttk.Treeview(frame_bottom, columns=list(range(5)), show='headings', yscrollcommand=scrollbar.set)
    column_name = ["会员编号", "姓名", "性别", "住址", "联系电话"]
    for i in range(5):
        tree.column(str(i), anchor='w')
        tree.heading(str(i), text=column_name[i], anchor='w')
    tree.pack(fill=BOTH, expand=YES)
    for row in execute_sql("SELECT * FROM member;"):
        tree.insert('', 'end', values=row)
    scrollbar.config(command=tree.yview)
    def show_all():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM member;"):
            tree.insert('', 'end', values=new_row)
    def no_search(event):
        [tree.delete(item) for item in tree.get_children()]
        sql = "SELECT * FROM member where mno={}"
        for row_search in execute_sql(sql.format(label_list[1].get())):
            tree.insert('', 'end', values=row_search)
    def name_search(event):
        [tree.delete(item) for item in tree.get_children()]
        sql = "SELECT * FROM member where mname like '%{}%'"
        for row_search in execute_sql(sql.format(label_list[3].get())):
            tree.insert('', 'end', values=row_search)

    popup_menu = Menu(frame_bottom, tearoff=0)
    def is_select():  # 判断Treeview中是否有行被选中
        flag = False
        for elem in tree.selection():
            flag = True
        return flag
    def alter():  # 修改会员信息
        if is_select():
            for elem in tree.selection():
                win = Toplevel()
                win.grab_set()  # 模态
                win.focus()
                center(win, 300, 400)
                labels = [Label(win, text='修改会员信息', fg='blue', font=('楷体', 14)),
                          Label(win, text='会员编号：' + tree.item(elem, 'values')[0]),
                          Label(win, text="姓名"), Entry(win),
                          Label(win, text="性别"), Entry(win),
                          Label(win, text="住址"), Entry(win),
                          Label(win, text="联系电话"), Entry(win)]
                for l in labels:
                    l.pack()
                def confirm():  # 确认添加事件
                    sql = "update member set mname='%s', msex='%s', maddress='%s', mphone='%s' where mno='%s'"
                    data = []
                    for text in labels[3::2]:  # 切片 获取Entry， 再将其上面的文本内容添加到data里
                        data.append(text.get())
                    data.append(tree.item(elem, 'values')[0])
                    try:
                        execute_sql(sql % tuple(data))  # 字符串格式化
                        tkinter.messagebox.showinfo("SUCCEED", "修改成功！")
                        show_all()
                        win.destroy()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror("ERROR", "输入有误！")
                        win.focus()
                Button(win, text='确认修改', command=lambda: confirm()).pack()
        else:
            tkinter.messagebox.showerror("ERROR", "未选择会员！")
    def delete():  # 删除会员信息
        if is_select():
            if tkinter.messagebox.askokcancel('警告', '确认删除该会员信息吗？'):
                for elem in tree.selection():
                    try:
                        execute_sql("delete from member where mno='{}'".format(tree.item(elem, 'values')[0]))
                        tkinter.messagebox.showinfo('Succeed', '删除成功！')
                        show_all()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror('Failed', '删除失败，请先删除该会员的销售记录!')
        else:
            tkinter.messagebox.showerror('ERROR', '未选择会员！')
    def popup(event):  # 弹出右键菜单
        popup_menu.post(event.x_root, event.y_root)
    popup_menu.add_command(label='修改', command=lambda: alter())
    popup_menu.add_separator()
    popup_menu.add_command(label='删除', command=lambda: delete())
    label_list[1].bind('', no_search)
    label_list[3].bind('', name_search)
    button = Button(frame_top, text='显示所有', font=('楷体', 14), command=show_all)
    button.pack(side='left', fill=X, expand=YES, padx=3, pady=5)
    tree.bind("", popup)
def search_wine(window):  # 查询， 查看酒水信息
    window.frame_right.destroy()
    window.frame_right = Frame(Main.flag.frame_bottom)
    window.frame_right.pack(side="right", fill=BOTH, expand=YES)
    frame_top = Frame(window.frame_right)
    frame_top.pack(side='top', fill=BOTH)
    frame_bottom = Frame(window.frame_right)
    frame_bottom.pack(side='bottom', fill=BOTH, expand=YES)
    Label(frame_top, text='查询查看酒水信息：', font=('楷体', 18), fg='blue').pack(side='left', fill=BOTH, expand=YES)
    label_list = [Label(frame_top, text='酒水编号'), Entry(frame_top, width=7), Label(frame_top, text='选择酒水类型')]
    for label in label_list:
        label.pack(side="left", fill=X, expand=YES, padx=3, pady=2)
    scrollbar = Scrollbar(frame_bottom)
    scrollbar.pack(side='right', fill=Y)
    tree = ttk.Treeview(frame_bottom, columns=list(range(7)), show='headings', yscrollcommand=scrollbar.set)
    column_name = ["酒水编号", "酒水类型", "单价", "年份", "产地", "颜色", "是否售出"]
    for i in range(7):
        tree.column(str(i), width=100, anchor='w')
        tree.heading(str(i), text=column_name[i], anchor='w')
    tree.column('3', width=120)
    tree.pack(fill=BOTH, expand=YES)
    for row in execute_sql("SELECT * FROM wine;"):
        tree.insert('', 'end', values=row)
    scrollbar.config(command=tree.yview)
    def show_all():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM wine;"):
            tree.insert('', 'end', values=new_row)
    def no_search(event):
        [tree.delete(item) for item in tree.get_children()]
        sql = "SELECT * FROM wine where wno={}"
        for row_search in execute_sql(sql.format(label_list[1].get())):
            tree.insert('', 'end', values=row_search)
    def show_no():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM wine where wsaled='否'"):
            tree.insert('', 'end', values=new_row)
    def show_yes():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM wine where wsaled='是';"):
            tree.insert('', 'end', values=new_row)
    popup_menu = Menu(frame_bottom, tearoff=0)
    def is_select():  # 判断Treeview中是否有行被选中
        flag = False
        for elem in tree.selection():
            flag = True
        return flag
    def alter():  # 修改酒水信息
        if is_select():
            for elem in tree.selection():
                win = Toplevel()
                win.grab_set()  # 模态
                win.focus()
                center(win, 300, 400)
                labels = [Label(win, text='修改酒水信息', fg='blue', font=('楷体', 14)),
                          Label(win, text='酒水编号：' + tree.item(elem, 'values')[0]),
                          Label(win, text="类型"), Entry(win),
                          Label(win, text="单价"), Entry(win),
                          Label(win, text="年份"), Entry(win),
                          Label(win, text="产地"), Entry(win),
                          Label(win, text="颜色"), Entry(win)]
                for l in labels:
                    l.pack()
                def confirm():  # 确认添加事件
                    sql = "update wine set wtype='%s', wprice=%s, wyear=%s, waddress='%s', wcolor='%s' where wno='%s'"
                    data = []
                    for text in labels[3::2]:  # 切片 获取Entry， 再将其上面的文本内容添加到data里
                        data.append(text.get())
                    data.append(tree.item(elem, 'values')[0])
                    try:
                        execute_sql(sql % tuple(data))  # 字符串格式化
                        tkinter.messagebox.showinfo("SUCCEED", "修改成功！")
                        show_all()
                        win.destroy()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror("ERROR", "输入有误！")
                        win.focus()
                Button(win, text='确认修改', command=lambda: confirm()).pack()
        else:
            tkinter.messagebox.showerror("ERROR", "未选择酒水！")
    def delete():  # 删除酒水信息
        if is_select():
            if tkinter.messagebox.askokcancel('警告', '确认删除该酒水信息吗？'):
                for elem in tree.selection():
                    try:
                        execute_sql("delete from wine where wno='{}'".format(tree.item(elem, 'values')[0]))
                        tkinter.messagebox.showinfo('Succeed', '删除成功！')
                        show_all()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror('Failed', '删除失败，请先删除该酒水的销售记录!')
        else:
            tkinter.messagebox.showerror('ERROR', '未选择酒水！')
    def popup(event):  # 弹出右键菜单
        popup_menu.post(event.x_root, event.y_root)
    def price_sort():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM wine order by wprice"):
            tree.insert('', 'end', values=new_row)
    def age_sort():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM wine order by wyear"):
            tree.insert('', 'end', values=new_row)
    tree.heading("0", text="酒水编号", command=lambda: show_all())  # 点击表头排序
    tree.heading("2", text="单价", command=lambda: price_sort())  # 点击表头排序
    tree.heading("3", text="年份", command=lambda: age_sort())  # 点击表头排序
    popup_menu.add_command(label='修改', command=lambda: alter())
    popup_menu.add_separator()
    popup_menu.add_command(label='删除', command=lambda: delete())
    label_list[1].bind('', no_search)  # 回车 按酒水编号查询
    def show(event):
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("select * from wine where wtype='{}'".format(cmb.get())):
            tree.insert('', 'end', values=new_row)
    type_p = []
    for result in execute_sql("select distinct wtype from wine"):
        type_p.append(result)
    cmb = ttk.Combobox(frame_top, value=type_p, state='readonly', width=5)   # 添加选择酒水类型的下拉列表
    cmb.pack(side="left", fill=X, expand=YES, padx=3, pady=2)
    cmb.bind("<>", show)
    Label(frame_top, text='价格区间').pack(side="left", fill=X, expand=YES, padx=3, pady=2)
    left = Entry(frame_top, width=8)
    left.pack(side="left", fill=X, expand=YES, padx=0, pady=2)
    Label(frame_top, text='至').pack(side="left", fill=X, expand=YES, padx=0, pady=2)
    right = Entry(frame_top, width=8)
    right.pack(side="left", fill=X, expand=YES, padx=0, pady=2)
    def range_search(event):
        [tree.delete(item) for item in tree.get_children()]
        sql = "SELECT * FROM wine where wprice>={} and wprice<={};"
        for new_row in execute_sql(sql.format(left.get(), right.get())):
            tree.insert('', 'end', values=new_row)
    right.bind('', range_search)
    button_no = Button(frame_top, text='未售出', font=('楷体', 12), command=show_no)
    button_yes = Button(frame_top, text='已售出', font=('楷体', 12), command=show_yes)
    button = Button(frame_top, text='显示所有', font=('楷体', 12), command=show_all)
    button_no.pack(side='left', fill=X, expand=YES, padx=3, pady=5)
    button_yes.pack(side='left', fill=X, expand=YES, padx=3, pady=5)
    button.pack(side='left', fill=X, expand=YES, padx=3, pady=5)
    tree.bind("", popup)
def search_sets(window):  # 查询， 查看酒具信息
    window.frame_right.destroy()
    window.frame_right = Frame(Main.flag.frame_bottom)
    window.frame_right.pack(side="right", fill=BOTH, expand=YES)
    frame_top = Frame(window.frame_right)
    frame_top.pack(side='top', fill=BOTH)
    frame_bottom = Frame(window.frame_right)
    frame_bottom.pack(side='bottom', fill=BOTH, expand=YES)
    Label(frame_top, text='查询查看酒具信息：', font=('楷体', 18), fg='blue').pack(side='left', fill=BOTH, expand=YES)
    label_list = [Label(frame_top, text='产品编号:'), Entry(frame_top), Label(frame_top, text='产品名称:'),
                  Entry(frame_top)]
    for label in label_list:
        label.pack(side="left", fill=X, expand=YES, padx=3, pady=2)
    scrollbar = Scrollbar(frame_bottom)
    scrollbar.pack(side='right', fill=Y)
    tree = ttk.Treeview(frame_bottom, columns=list(range(4)), show='headings', yscrollcommand=scrollbar.set)
    column_name = ["产品编号", "产品名称", "单价", "供货商"]
    for i in range(4):
        tree.column(str(i), anchor='w')
        tree.heading(str(i), text=column_name[i], anchor='w')
    tree.pack(fill=BOTH, expand=YES)
    for row in execute_sql("SELECT * FROM sets;"):
        tree.insert('', 'end', values=row)
    scrollbar.config(command=tree.yview)
    def show_all():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM sets;"):
            tree.insert('', 'end', values=new_row)
    def no_search(event):
        [tree.delete(item) for item in tree.get_children()]
        sql = "SELECT * FROM sets where sno={}"
        for row_search in execute_sql(sql.format(label_list[1].get())):
            tree.insert('', 'end', values=row_search)
    def name_search(event):
        [tree.delete(item) for item in tree.get_children()]
        sql = "SELECT * FROM sets where sname like '%{}%'"
        for row_search in execute_sql(sql.format(label_list[3].get())):
            tree.insert('', 'end', values=row_search)
    popup_menu = Menu(frame_bottom, tearoff=0)
    def is_select():  # 判断Treeview中是否有行被选中
        flag = False
        for elem in tree.selection():
            flag = True
        return flag
    def alter():  # 修改酒具信息
        if is_select():
            for elem in tree.selection():
                win = Toplevel()
                win.grab_set()  # 模态
                win.focus()
                center(win, 300, 400)
                labels = [Label(win, text='修改酒具信息', fg='blue', font=('楷体', 14)),
                          Label(win, text='产品编号：' + tree.item(elem, 'values')[0]),
                          Label(win, text="产品名称"), Entry(win),
                          Label(win, text="价格"), Entry(win),
                          Label(win, text="供货商"), Entry(win)]
                for l in labels:
                    l.pack()
                def confirm():  # 确认添加事件
                    sql = "update sets set sname='%s', sprice='%s', sprovider='%s' where sno='%s'"
                    data = []
                    for text in labels[3::2]:  # 切片 获取Entry， 再将其上面的文本内容添加到data里
                        data.append(text.get())
                    data.append(tree.item(elem, 'values')[0])
                    try:
                        execute_sql(sql % tuple(data))  # 字符串格式化
                        tkinter.messagebox.showinfo("SUCCEED", "修改成功！")
                        show_all()
                        win.destroy()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror("ERROR", "输入有误！")
                        win.focus()
                Button(win, text='确认修改', command=lambda: confirm()).pack()
        else:
            tkinter.messagebox.showerror("ERROR", "未选择酒具！")
    def delete():  # 删除酒具信息
        if is_select():
            if tkinter.messagebox.askokcancel('警告', '确认删除该酒具吗？'):
                for elem in tree.selection():
                    try:
                        execute_sql("delete from sets where sno='{}'".format(tree.item(elem, 'values')[0]))
                        tkinter.messagebox.showinfo('Succeed', '删除成功！')
                        show_all()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror('Failed', '删除失败，请先删除该酒具的销售记录!')
        else:
            tkinter.messagebox.showerror('ERROR', '未选择酒具！')
    def popup(event):  # 弹出右键菜单
        popup_menu.post(event.x_root, event.y_root)
    def price_sort():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql("SELECT * FROM sets order by sprice"):
            tree.insert('', 'end', values=new_row)
    tree.heading("0", text="产品编号", command=lambda: show_all())  # 点击表头排序
    tree.heading("2", text="价格", command=lambda: price_sort())  # 点击表头排序
    popup_menu.add_command(label='修改', command=lambda: alter())
    popup_menu.add_separator()
    popup_menu.add_command(label='删除', command=lambda: delete())
    label_list[1].bind('', no_search)
    label_list[3].bind('', name_search)
    button = Button(frame_top, text='显示所有', font=('楷体', 14), command=show_all)
    button.pack(side='left', fill=X, expand=YES, padx=3, pady=5)
    tree.bind("", popup)
def search_sale(window):  # 查询， 查看销售信息
    window.frame_right.destroy()
    window.frame_right = Frame(Main.flag.frame_bottom)
    window.frame_right.pack(side="right", fill=BOTH, expand=YES)
    frame_top = Frame(window.frame_right)
    frame_top.pack(side='top', fill=BOTH)
    frame_bottom = Frame(window.frame_right)
    frame_bottom.pack(side='bottom', fill=BOTH, expand=YES)
    Label(frame_top, text='查询查看销售信息：', font=('楷体', 18), fg='blue').pack(side='left', fill=BOTH, expand=YES)
    label_list = [Label(frame_top, text='会员编号'), Entry(frame_top, width=10),
                  Label(frame_top, text='会员姓名'), Entry(frame_top, width=10),
                  Label(frame_top, text='酒水编号'), Entry(frame_top, width=10),
                  Label(frame_top, text='选择酒水类型')]
    for label in label_list:
        label.pack(side="left", fill=X, expand=YES, padx=3, pady=2)
    scrollbar = Scrollbar(frame_bottom)
    scrollbar.pack(side='right', fill=Y)
    tree = ttk.Treeview(frame_bottom, columns=list(range(13)), show='headings', yscrollcommand=scrollbar.set)
    column_name = ["会员编号", "姓名", "性别", "住址", "联系电话", "酒水编号",
                   "酒水类型", "单价", "年份", "产地", "颜色", "订单号", "销售日期"]
    for i in range(13):
        tree.column(str(i), width=50, anchor='w')
        tree.heading(str(i), text=column_name[i], anchor='w')
    tree.column('4', width=100)
    tree.column('3', width=80)
    tree.column('11', width=120)
    tree.column('12', width=80)
    tree.pack(fill=BOTH, expand=YES)
    select_all = "select member.mno, mname, msex, maddress, mphone, wine.wno, wtype, wprice, wyear, waddress, " \
                 "wcolor, sano, sadate from member, wine, sale where member.mno = sale.mno and wine.wno = sale.wno"
    for row in execute_sql(select_all):
        tree.insert('', 'end', values=row)
    scrollbar.config(command=tree.yview)
    def show_all(): # 显示所有销售信息
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql(select_all):
            tree.insert('', 'end', values=new_row)
    def mno_search(event):  # 按会员编号查找
        [tree.delete(item) for item in tree.get_children()]
        sql = "select member.mno, mname, msex, maddress, mphone, wine.wno, wtype, wprice, wyear, waddress, " \
                 "wcolor, sano, sadate from member, wine, sale where member.mno = sale.mno and " \
              "wine.wno = sale.wno and member.mno='{}'"
        for row_search in execute_sql(sql.format(label_list[1].get())):
            tree.insert('', 'end', values=row_search)
    def mname_search(event):  # 按会员姓名查找
        [tree.delete(item) for item in tree.get_children()]
        sql = "select member.mno, mname, msex, maddress, mphone, wine.wno, wtype, wprice, wyear, waddress, " \
                 "wcolor, sano, sadate from member, wine, sale where member.mno = sale.mno and " \
              "wine.wno = sale.wno and member.mname like '%{}%'"
        for row_search in execute_sql(sql.format(label_list[3].get())):
            tree.insert('', 'end', values=row_search)
    def wno_search(event):  # 按酒水编号查找
        [tree.delete(item) for item in tree.get_children()]
        sql = "select member.mno, mname, msex, maddress, mphone, wine.wno, wtype, wprice, wyear, waddress, " \
                 "wcolor, sano, sadate from member, wine, sale where member.mno = sale.mno and " \
              "wine.wno = sale.wno and wine.wno='{}'"
        for row_search in execute_sql(sql.format(label_list[5].get())):
            tree.insert('', 'end', values=row_search)
    popup_menu = Menu(frame_bottom, tearoff=0)
    def is_select():  # 判断Treeview中是否有行被选中
        flag = False
        for elem in tree.selection():
            flag = True
        return flag
    def alter():  # 修改销售信息
        if is_select():
            for elem in tree.selection():
                win = Toplevel()
                win.grab_set()  # 模态
                win.focus()
                center(win, 300, 600)
                labels = [Label(win, text='修改销售信息', fg='blue', font=('楷体', 14)),
                          Label(win, text='订单编号：' + tree.item(elem, 'values')[0]),
                          Label(win, text="销售日期"), Entry(win)]
                for l in labels:
                    l.pack()
                Label(win, text="选择会员编号：", fg='MediumBlue', font=("楷体", 12)).pack(pady=10)
                cursor_c = execute_sql("SELECT mno FROM member;")
                no_c = []
                for result in cursor_c.fetchall():  # 查询所有用户编号添加到下拉列表供用户选择
                    no_c.append(result[0])
                cmb_c = ttk.Combobox(win, value=no_c, state='readonly')
                cmb_c.pack(pady=2)
                string_show_c = [StringVar(), StringVar(), StringVar(), StringVar()]
                label_show_c = [Label(win), Label(win), Label(win), Label(win)]
                init_name_c = ['姓名:', '性别:', '家庭住址:', '电话:']
                for i in range(4):
                    string_show_c[i].set(init_name_c[i])
                    label_show_c[i]['textvariable'] = string_show_c[i]
                    label_show_c[i].pack(pady=2)
                def show_c(event):
                    sql_select = "SELECT * FROM member where mno={};"
                    result_row = execute_sql(sql_select.format(cmb_c.get())).fetchone()
                    for j in range(4):
                        string_show_c[j].set(init_name_c[j] + result_row[j + 1])
                cmb_c.bind("<>", show_c)
                Label(win, fg='MediumBlue', font=("楷体", 12), text="选择酒水编号：").pack(pady=10)
                cursor_p = execute_sql("SELECT wno FROM wine where wsaled='否'")
                no_p = list()
                no_p.append(tree.item(elem, 'values')[5])
                for result in cursor_p.fetchall():  # 查询所有酒水编号名添加到下拉列表供用户选择
                    no_p.append(result[0])
                cmb_p = ttk.Combobox(win, value=no_p, state='readonly')
                cmb_p.pack(pady=2)
                string_show_p = [StringVar(), StringVar(), StringVar(), StringVar(), StringVar()]
                label_show_p = [Label(win), Label(win), Label(win), Label(win), Label(win)]
                init_name_p = ['酒水类型:', '单价:', '年份:', '产地:', '颜色:']
                for i in range(5):
                    string_show_p[i].set(init_name_p[i])
                    label_show_p[i]['textvariable'] = string_show_p[i]
                    label_show_p[i].pack(pady=2)
                def show_p(event):
                    sql_select = "SELECT * FROM wine where wno={};"
                    result_row = execute_sql(sql_select.format(cmb_p.get())).fetchone()
                    for j in range(5):
                        string_show_p[j].set(init_name_p[j] + str(result_row[j + 1]))
                cmb_p.bind("<>", show_p)
                def confirm():
                    sql_update = "update sale set sadate='%s', mno='%s', wno='%s' where sano= '%s'"
                    data = (labels[3].get(), cmb_c.get(), cmb_p.get(), tree.item(elem, 'values')[11])
                    try:
                        execute_sql(sql_update % data)
                        tkinter.messagebox.showinfo("SUCCEED", "修改成功")
                        execute_sql("update wine set wsaled='是' where wno='{}'".format(cmb_p.get()))
                        win.destroy()
                        show_all()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror("Failed", "修改失败")
                        win.focus()

                Button(win, text='确认修改', command=lambda: confirm()).pack(pady=20)
        else:
            tkinter.messagebox.showerror("ERROR", "未选择销售信息！")
    def delete():  # 删除酒水信息
        if is_select():
            if tkinter.messagebox.askokcancel('警告', '确认删除该销售信息吗？'):
                for elem in tree.selection():
                    try:
                        execute_sql("delete from sale where sano='{}'".format(tree.item(elem, 'values')[11]))
                        tkinter.messagebox.showinfo('Succeed', '删除成功！')
                        show_all()
                    except psycopg2.Error:
                        tkinter.messagebox.showerror('Failed', '删除失败!')
        else:
            tkinter.messagebox.showerror('ERROR', '未选择销售信息！')
    def popup(event):  # 弹出右键菜单
        popup_menu.post(event.x_root, event.y_root)
    def price_sort():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql(select_all + " order by wprice"):
            tree.insert('', 'end', values=new_row)
    def date_sort():
        [tree.delete(item) for item in tree.get_children()]
        for new_row in execute_sql(select_all + " order by sadate"):
            tree.insert('', 'end', values=new_row)
    tree.heading("7", text="单价", command=lambda: price_sort())  # 点击表头排序
    tree.heading("12", text="销售日期", command=lambda: date_sort())  # 点击表头排序
    popup_menu.add_command(label='修改', command=lambda: alter())
    popup_menu.add_separator()
    popup_menu.add_command(label='删除', command=lambda: delete())
    label_list[1].bind('', mno_search)  # 回车 按会员编号查询
    label_list[3].bind('', mname_search)  # 回车 按会员姓名查询
    label_list[5].bind('', wno_search)  # 回车 按酒水编号查询
    def show(event):
        [tree.delete(item) for item in tree.get_children()]
        sql = "select member.mno, mname, msex, maddress, mphone, wine.wno, wtype, wprice, wyear, waddress, " \
                 "wcolor, sano, sadate from member, wine, sale " \
              "where member.mno = sale.mno and wine.wno = sale.wno and wine.wtype='{}'"
        for new_row in execute_sql(sql.format(cmb.get())):
            tree.insert('', 'end', values=new_row)
    type_p = []
    sql = "select distinct wtype from member,wine,sale where member.mno=sale.mno and wine.wno=sale.wno"
    for result in execute_sql(sql):
        type_p.append(result)
    cmb = ttk.Combobox(frame_top, value=type_p, state='readonly', width=5)   # 添加选择酒水类型的下拉列表
    cmb.pack(side="left", fill=X, expand=YES, padx=3, pady=2)
    cmb.bind("<>", show)
    button = Button(frame_top, text='显示所有', font=('楷体', 14), command=show_all)
    button.pack(side='left', fill=X, expand=YES, padx=3, pady=5)
    tree.bind("", popup)
def add_sale(win):  # 添加订单信息
    win.frame_right.destroy()
    win.frame_right = Frame(Main.flag.frame_bottom)
    win.frame_right.pack(side='right', fill=BOTH, expand=YES)
    Label(win.frame_right, text="添  加   订   单   信   息：", fg='blue', font=('华文彩云', 16)).pack(pady=10)
    string_time = time.strftime("%Y%m%d%H%M%S", time.localtime())
    Label(win.frame_right, text='订单号：' + string_time).pack(pady=10)
    Label(win.frame_right, text="销售日期：" + time.strftime("%Y-%m-%d", time.localtime())).pack(pady=10)
    Label(win.frame_right, text="选择会员编号：", fg='MediumBlue', font=("楷体", 14)).pack(pady=10)
    cursor_c = execute_sql("SELECT mno FROM member;")
    no_c = []
    for result in cursor_c.fetchall():  # 查询所有用户编号添加到下拉列表供用户选择
        no_c.append(result[0])
    cmb_c = ttk.Combobox(win.frame_right, value=no_c, state='readonly')
    cmb_c.pack(pady=2)
    string_show_c = [StringVar(), StringVar(), StringVar(), StringVar()]
    label_show_c = [Label(win.frame_right), Label(win.frame_right), Label(win.frame_right), Label(win.frame_right)]
    init_name_c = ['姓名:', '性别:', '家庭住址:', '电话:']
    for i in range(4):
        string_show_c[i].set(init_name_c[i])
        label_show_c[i]['textvariable'] = string_show_c[i]
        label_show_c[i].pack(pady=2)
    def show_c(event):
        global string_time
        string_time = time.strftime("%Y%m%d%H%M%S", time.localtime())
        sql_select = "SELECT * FROM member where mno={};"
        result_row = execute_sql(sql_select.format(cmb_c.get())).fetchone()
        for j in range(4):
            string_show_c[j].set(init_name_c[j] + result_row[j + 1])
            print(string_show_c[j])
    cmb_c.bind("<>", show_c)
    Label(win.frame_right, fg='MediumBlue', font=("楷体", 14), text="选择酒水编号：").pack(pady=10)
    cursor_p = execute_sql("SELECT wno FROM wine where wsaled='否';")
    no_p = []
    for result in cursor_p.fetchall():  # 查询所有未售出酒水编号名添加到下拉列表供用户选择
        no_p.append(result[0])
    cmb_p = ttk.Combobox(win.frame_right, value=no_p, state='readonly')
    cmb_p.pack(pady=2)
    string_show_p = [StringVar(), StringVar(), StringVar(), StringVar(), StringVar()]
    label_show_p = [Label(win.frame_right), Label(win.frame_right),
                    Label(win.frame_right), Label(win.frame_right), Label(win.frame_right)]
    init_name_p = ['酒水类型:', '单价:', '年份:', '产地:', '颜色:']
    for i in range(5):
        string_show_p[i].set(init_name_p[i])
        label_show_p[i]['textvariable'] = string_show_p[i]
        label_show_p[i].pack(pady=2)
    def show_p(event):
        global string_time
        string_time = time.strftime("%Y%m%d%H%M%S", time.localtime())
        sql_select = "SELECT * FROM wine where wno={};"
        result_row = execute_sql(sql_select.format(cmb_p.get())).fetchone()
        for j in range(5):
            string_show_p[j].set(init_name_p[j] + str(result_row[j + 1]))
    cmb_p.bind("<>", show_p)
    def confirm():
        global string_time
        sql = "insert into sale values('%s', '%s', '%s', '%s');"
        print(sql)
        data = (string_time, cmb_c.get(), cmb_p.get(), time.strftime("%Y-%m-%d", time.localtime()))
        #print(sql % data)
        try:
            execute_sql(sql % data)
            tkinter.messagebox.showinfo("SUCCEED", "添加成功")
            execute_sql("update wine set wsaled='是' where wno='{}'".format(cmb_p.get()))
        except psycopg2.Error:
            tkinter.messagebox.showerror("Failed", "添加失败")
    Button(win.frame_right, text='确认添加', command=lambda: confirm()).pack(pady=20)
class FrameLeft(Frame):  # 左侧菜单栏
    def __init__(self, master):
        Frame.__init__(self, master, bg='#9DC3E7', width=180, borderwidth=2)
        self.pack_propagate(False)  # 如果Frame中添加了其它组件，frame大小会变化， 用此方法固定frame大小
        self.create()
    def create(self):
        Button(self, text="主      页", bg='#5F97D2', font=('华文琥珀', 16), command=lambda: go_home(Main.flag)).pack(fill=X)
        Label(self, text="查看信息", bg='#9DC3E7', fg='blue', font=("楷体", 16)).pack(fill=X)
        Button(self, text="查看会员信息",bg='#5F97D2', command=lambda: search_member(Main.flag)).pack(fill=X)
        Button(self, text="查看酒水信息",bg='#5F97D2', command=lambda: search_wine(Main.flag)).pack(fill=X)
        Button(self, text="查看酒具信息", bg='#5F97D2',command=lambda: search_sets(Main.flag)).pack(fill=X)
        Button(self, text="查看销售信息",bg='#5F97D2', command=lambda: search_sale(Main.flag)).pack(fill=X)
        Label(self, text="录入信息", bg='#9DC3E7', fg='blue', font=("楷体", 16)).pack(fill=X)
        Button(self, text="添加会员", bg='#5F97D2',command=lambda: self.add_setsr()).pack(fill=X)
        Button(self, text="添加酒水", bg='#5F97D2',command=lambda: self.add_wine()).pack(fill=X)
        Button(self, text="添加酒具", bg='#5F97D2',command=lambda: self.add_sets()).pack(fill=X)
        Button(self, text="添加销售信息", bg='#5F97D2',command=lambda: add_sale(Main.flag)).pack(fill=X)
        def quit_sys():
            if tkinter.messagebox.askokcancel('提示', '确认退出吗？'):
                sys.exit(0)
        Label(self, bg='#9DC3E7',font=("楷体", 16)).pack(fill=X)
        Button(self, text="退出系统",bg='#5F97D2', font=('楷体', 14), command=lambda: quit_sys()).pack(fill=X)
    @staticmethod
    def add_setsr():  # 添加用户信息事件
        win = Toplevel()
        win.grab_set()
        win.focus()
        center(win, 300, 400)
        labels = [Label(win, text='会员编号'), Entry(win),
                  Label(win, text="姓名"), Entry(win),
                  Label(win, text="性别"), Entry(win),
                  Label(win, text="住址"), Entry(win),
                  Label(win, text="联系电话"), Entry(win)]
        for label in labels:
            label.pack()
        def confirm():  # 确认添加事件
            sql = "insert into member values('%s', '%s', '%s', '%s', '%s');"
            data = []
            for text in labels[1::2]:  # 切片 获取Entry， 再将其上面的文本内容添加到data里
                data.append(text.get())
            try:
                execute_sql(sql % tuple(data))  # 字符串格式化
                tkinter.messagebox.showinfo("SUCCEED", "录入成功！")
                win.destroy()
            except psycopg2.Error:
                tkinter.messagebox.showerror("ERROR", "输入有误！")
                win.focus()
        Button(win, text='确认', command=lambda: confirm()).pack()
    @staticmethod
    def add_wine():  # 添加酒水事件
        win = Toplevel()
        win.grab_set()
        win.focus()
        center(win, 300, 400)
        labels = [Label(win, text='酒水编号'), Entry(win),
                  Label(win, text="酒水类型"), Entry(win),
                  Label(win, text="单价"), Entry(win),
                  Label(win, text="年份"), Entry(win),
                  Label(win, text="产地"), Entry(win),
                  Label(win, text="颜色"), Entry(win)]
        for label in labels:
            label.pack()
        def confirm():  # 确认添加事件
            sql = "insert into wine values('%s', '%s', '%s', '%s', '%s', '%s', '否');"
            data = []
            for text in labels[1::2]:
                data.append(text.get())
            try:
                execute_sql(sql % tuple(data))
                tkinter.messagebox.showinfo("SUCCEED", "录入成功！")
                win.destroy()
            except psycopg2.Error:
                tkinter.messagebox.showerror("ERROR", "输入有误！")
                win.focus()
        Button(win, text='确认', command=lambda: confirm()).pack()
    @staticmethod
    def add_sets():  # 添加酒具事件
        win = Toplevel()
        win.grab_set()
        win.focus()
        center(win, 300, 360)
        labels = [Label(win, text='产品编号'), Entry(win),
                  Label(win, text="产品名称"), Entry(win),
                  Label(win, text="单价"), Entry(win),
                  Label(win, text="供货商"), Entry(win)]
        for label in labels:
            label.pack()
        def confirm():  # 确认添加事件
            sql = "insert into sets values('%s', '%s', '%s', '%s');"
            data = []
            for text in labels[1::2]:
                data.append(text.get())
            try:
                execute_sql(sql % tuple(data))
                tkinter.messagebox.showinfo("SUCCEED", "录入成功！")
                win.destroy()
            except psycopg2.Error:
                tkinter.messagebox.showerror("ERROR", "输入有误！")
                win.focus()
        Button(win, text='确认', command=lambda: confirm()).pack()
class Home(Frame):  # 主页
    def __init__(self, master):
        Frame.__init__(self, master)
        self.label1 = Label(self,text=time.strftime('%Y-%m-%d %H:%M:%S %A', time.localtime(time.time()))
                           , font=("DBLCDTemp Black", 24),compound="center")
        self.label1.after(1000, self.trickit)
        self.label1.pack(pady=20)
        self.label2 = Label(self,text = "   传统的酒庄管理靠人为记忆，酒水的信息记录不够全面、丰富，不能直观展示\
奢侈酒水的魅力，更不能充分提升酒水给工作和生活带来的快乐和享受。\
在酒水数据方面，传统方式采用纸笔登记、粘贴纸质酒水标记、时常需要组织相\
关人员进行实物手工盘点，不仅工作效率低，浪费了大量人力、物力，而且由于\
采用手工方式录入数据，极容易产生错误传统酒水管理的细粒度非常差，例如：\
酒水什么时间销售的？产地在哪里？是否售出？销售频率如\
何？会员有哪些？酒的类型等等这些，传统酒水管理都无法追溯\
到细节，酒水生命周期管理非常粗放。",font=("DBLCDTemp Black", 14),wraplength = 580,)
        self.label2.pack(pady=90)
    def trickit(self):
        currentTime = time.strftime('%Y-%m-%d %H:%M:%S %A', time.localtime(time.time()))
        summary_text='欢迎!!!\n'+'智慧酒水销售管理系统从每一个细节帮您管理酒水。\n'+currentTime
        self.label1.config(text=summary_text)
        self.update()
        self.label1.after(1000, self.trickit)
root = Tk()  # 登录界面
center(root, 300, 220)
root.resizable(0, 0)
root.title("酒水销售管理系统")
frame = Frame(root)
frame.place(x=30, y=70)
labletext = Label(frame, text="请输入管理员账户密码", font=("宋体", 15))
labletext.grid(row=0, column=1)
label_1 = Label(frame, text="账号：", font=("宋体", 12))
label_1.grid(row=1, column=0)
entry = Entry(frame, show=None, font=("Arial", 12))
entry.grid(row=1, column=1)
entry.focus()
label_2 = Label(frame, text="密码：", font=("宋体", 12))
label_2.grid(row=2, column=0)
entry_password = Entry(frame, show="*", font=("Arial", 12))
entry_password.grid(row=2, column=1)
def sign_in(event):  # 登录
    cursor = get_connect().cursor()
    #获得输入
    sql = "select rpassword from admin where ruser=" + entry.get()
    try:
        cursor.execute(sql)
        password_input = entry_password.get()  # 获取用户输入的密码
        password_db = (cursor.fetchall())[0][0]  # 获取数据中的密码
        print(password_db)
        if password_input == password_db:  # 判断用户输入的密码与数据库中的是否一致
            tkinter.messagebox.showinfo(title="succeed", message="登录成功")
            root.destroy()  # 销毁当前窗口
            new_win = Tk()  # 进入主界面
            # center(root, root.winfo_screenwidth(), root.winfo_screenheight())  # 最大化，效果不好
            center(new_win, 1200, 800)
            new_win.title("酒水销售管理系统")
            app = Main(new_win)
            Main.flag = app
            center(app.master, 1200, 800)
        else:
            tkinter.messagebox.showerror(title="failed", message="密码错误")
    except (psycopg2.Error, TypeError):
        tkinter.messagebox.showerror(title="failed", message="密码错误")
entry_password.bind('', sign_in)  # 密码输入框回车登录
button_sign_in = Button(frame, text='     登        录      ', fg='black', command=lambda: sign_in(None))
button_sign_in.grid(row=3, column=1)
root.mainloop()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805

完整报告下载