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
-- 1. 删除表，保证是我们设计的最新的表
DROP TABLE IF EXISTS purchase;
DROP TABLE IF EXISTS sale;
DROP TABLE IF EXISTS staff;
DROP TABLE IF EXISTS client;
DROP TABLE IF EXISTS goods;

-- 2. 创建客户表
CREATE TABLE client (
    client_id CHAR(10) PRIMARY KEY NOT NULL,
    client_name VARCHAR(20) NOT NULL,
    phone_number VARCHAR(11) UNIQUE,
    address VARCHAR(10)
);

-- 3. 创建员工表
CREATE TABLE staff (
    staff_id CHAR(10) PRIMARY KEY NOT NULL,
    staff_name VARCHAR(20) NOT NULL,
    department VARCHAR(10) NOT NULL,
    salary DECIMAL(8,2) NOT NULL ,
    phone_number VARCHAR(11) UNIQUE,
    CHECK (salary>=0)
);

-- 4. 创建商品表
CREATE TABLE goods (
    goods_id CHAR(10) PRIMARY KEY NOT NULL,
    goods_name VARCHAR(20) NOT NULL,
    goods_num INT DEFAULT 0,
    CHECK (goods_num >= 0)
);

-- 5. 创建进货表
CREATE TABLE purchase (
    purchase_id CHAR(10) PRIMARY KEY NOT NULL,
    goods_id CHAR(10) NOT NULL ,
    staff_id CHAR(10) NOT NULL ,
    purchase_price DECIMAL(6,2) NOT NULL ,
    purchase_num INT NOT NULL ,
    purchase_amount DECIMAL(8,2) NOT NULL ,
    purchase_date DATE NOT NULL ,
    CONSTRAINT fk_goods1 FOREIGN KEY (goods_id) REFERENCES goods(goods_id),
    CONSTRAINT fk_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
    CHECK (purchase_price >= 0),
    CHECK (purchase_num >= 0),
    CHECK (purchase_amount = purchase_price * purchase_num)
);

-- 6. 创建销售表
CREATE TABLE sale (
    sale_id CHAR(10) PRIMARY KEY NOT NULL,
    client_id CHAR(10),
    goods_id CHAR(10),
    sale_price DECIMAL(6,2),
    sale_num INT,
    sale_amount DECIMAL(8,2),
    sale_date DATE,

    CONSTRAINT fk_client FOREIGN KEY (client_id) REFERENCES client(client_id),
    CONSTRAINT fk_goods2 FOREIGN KEY (goods_id) REFERENCES goods(goods_id),
    CHECK (sale_price >= 0),
    CHECK (sale_num >= 0),
    CHECK (sale_amount = sale_price * sale_num)
);

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
-- 触发器
-- 1. 工作部门必须是 sale or purchase
-- 限制插入
DROP TRIGGER IF EXISTS enforce_department_insert_constraint;
DELIMITER //
CREATE TRIGGER enforce_department_insert_constraint
BEFORE INSERT ON staff
FOR EACH ROW
BEGIN
IF NEW.department != 'purchase' AND NEW.department != 'sale' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insert Department must be "purchase" or "sale"';
END IF;
END //
DELIMITER ;

-- 限制更新
DROP TRIGGER IF EXISTS enforce_department_update_constraint;
DELIMITER //
CREATE TRIGGER enforce_department_update_constraint
BEFORE UPDATE ON staff
FOR EACH ROW
BEGIN
    IF NEW.department != 'purchase' AND NEW.department != 'sale' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Update Department must be "purchase" or "sale"';
    END IF;
END //
DELIMITER ;

-- 2. 插入或更新时自动给计算进货的总金额=进货单价*进货数量
DROP TRIGGER IF EXISTS calculate_insert_purchase_amount;
DELIMITER //
CREATE TRIGGER calculate_insert_purchase_amount
BEFORE INSERT ON purchase
FOR EACH ROW
BEGIN
    SET NEW.purchase_amount = NEW.purchase_price * NEW.purchase_num;
END //

DROP TRIGGER IF EXISTS calculate_update_purchase_amount;
CREATE TRIGGER calculate_update_purchase_amount
BEFORE UPDATE ON purchase
FOR EACH ROW
BEGIN
    SET NEW.purchase_amount = NEW.purchase_price * NEW.purchase_num;
END //
DELIMITER ;

-- 3. 插入或更新时自动给计算销售的总金额=销售单价*销售数量
DROP TRIGGER IF EXISTS calculate_insert_sale_amount;
DELIMITER //
CREATE TRIGGER calculate_insert_sale_amount
BEFORE INSERT ON sale
FOR EACH ROW
BEGIN
    SET NEW.sale_amount = NEW.sale_price * NEW.sale_num;
END //

DROP TRIGGER IF EXISTS calculate_update_sale_amount;
CREATE TRIGGER calculate_update_sale_amount
BEFORE UPDATE ON sale
FOR EACH ROW
BEGIN
    SET NEW.sale_amount = NEW.sale_price * NEW.sale_num;
END //
DELIMITER ;

-- 4.插入进货表时自动更新商品信息
DELIMITER //
DROP TRIGGER IF EXISTS update_goods_num_purchase_insert;
CREATE TRIGGER update_goods_num_purchase_insert
AFTER INSERT ON purchase
FOR EACH ROW
BEGIN
    UPDATE goods
    SET goods_num = goods_num + NEW.purchase_num
    WHERE goods_id = NEW.goods_id;
END //

-- 插入销售表时自动更新商品信息
DROP TRIGGER IF EXISTS update_goods_num_sale_insert;
CREATE TRIGGER update_goods_num_sale_insert
AFTER INSERT ON sale
FOR EACH ROW
BEGIN
    UPDATE goods
    SET goods_num = goods_num - NEW.sale_num
    WHERE goods_id = NEW.goods_id;
END //

-- 更新进货表时自动更新商品信息
DROP TRIGGER IF EXISTS update_goods_num_purchase_update;
CREATE TRIGGER update_goods_num_purchase_update
AFTER UPDATE ON purchase
FOR EACH ROW
BEGIN
    UPDATE goods
    SET goods_num = goods_num + (NEW.purchase_num - OLD.purchase_num)
    WHERE goods_id = NEW.goods_id;
END //

-- 更新销售表时自动更新商品信息
DROP TRIGGER IF EXISTS update_goods_num_sale_update;
CREATE TRIGGER update_goods_num_sale_update
AFTER UPDATE ON sale
FOR EACH ROW
BEGIN
    UPDATE goods
    SET goods_num = goods_num - (NEW.sale_num - OLD.sale_num)
    WHERE goods_id = NEW.goods_id;
END //

DELIMITER ;

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
import Vue from 'vue'
import VueRouter from 'vue-router'
import Main from '../views/Main.vue'
import Purchase from '../views/purchase.vue'
import Client from '../views/Client.vue'
import Commodity from '../views/Commodity.vue'
import Login from '../views/login.vue'
import Sale from '../views/sale.vue'
import Staff from '../views/staff.vue'
import Home from '../views/Home.vue'

Vue.use(VueRouter)
//添加组件（跳转页面）
const routes = [
  {
    path: '/', 
    component: Login,
  },

  {
    path: '/main', 
    component: Main,
    children:[
      //子路由
      { path: '/purchase', component: Purchase},
      { path: '/staff', component: Staff},
      { path: '/client', component: Client},
      { path: '/commodity', component: Commodity},
      { path: '/sale', component: Sale}
    ]
  }
]

const router = new VueRouter({
  mode: 'history',
  base: process.env.BASE_URL,
  routes
})

export default router

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
# 数据库操作类
import pymysql
DB_CONFIG = {
	"host": "127.0.0.1",
	"port": 3306,
	"user": "root",
	"passwd": "123456",
	"db": "test1",
	"charset": "utf8"
}

class SQLManager(object):

	# 初始化实例方法
	def __init__(self):
		self.conn = None	# 连接对象，建立与MySQL数据库的连接
		self.cursor = None 	# 游标对象，主要负责执行SQL语句
		self.connect()

	# 连接数据库
	def connect(self):
		# 建立连接
		self.conn = pymysql.connect(
			host=DB_CONFIG["host"],
			port=DB_CONFIG["port"],
			user=DB_CONFIG["user"],
			passwd=DB_CONFIG["passwd"],
			db=DB_CONFIG["db"],
			charset=DB_CONFIG["charset"]
		)
		# 获取游标对象；pymysql.cursors.DictCursor指定返回的结果类型为字典，默认是元组类型
		# 在连接没有关闭之前，游标对象可以反复使用
		self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

	# 查询多条数据
	def get_list(self, sql, args=None):
		self.cursor.execute(sql, args)	# 执行SQL语句，返回受影响的行数
		return self.cursor.fetchall()	# 返回所有查询结果

	# 查询单条数据
	def get_one(self, sql, args=None):
		self.cursor.execute(sql, args)
		return self.cursor.fetchone()	# 返回单条查询结果

	# 执行单条SQL语句
	def modify(self, sql, args=None):
		row = self.cursor.execute(sql, args)
		self.conn.commit()	# 对数据库的增、删、改操作需要提交事务，否则操作不生效
		return row > 0	# 若影响的行数>0，说明执行成功，返回True；否则返回False

	# 执行多条SQL语句
	def multi_modify(self, sql, args=None):
		rows = self.cursor.executemany(sql, args)
		self.conn.commit()
		return rows > 0

	# 关闭数据库cursor和连接
	def close(self):
		self.cursor.close()
		self.conn.close()

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
# 创建视图应用，使用改造后的JsonFlask对象
app = JsonFlask(__name__)

# 解决跨域
CORS(app, supports_credentials=True)

# 数据库连接对象
db = SQLManager()

# 编写视图函数，绑定路由
# client表
@app.route("/all_client", methods=["GET"])  # 查询（全部）
def all_client():
    result = db.get_list(sql='select * from client')
    return JsonResponse.success(msg='查询成功', data=result)

@app.route("/add_client", methods=["POST"])  # 添加（单个）
def add_client():
    data = json.loads(request.data)  # 将json字符串转为dict
    isOk = db.modify(sql='insert into client(client_id,client_name,phone_number,address) values(%s,%s,%s,%s)',
                     args=[data['client_id'], data['client_name'], data['phone_number'], data['address']])
    # python三元表达式
    return JsonResponse.success(msg='添加成功') if isOk else JsonResponse.fail(msg='添加失败')

@app.route("/update_client", methods=["PUT"])  # 修改（单个）
def update_client():
    # request.data获取请求体数据
    # 前端在发送请求时，由于指定了Content-Type为application/json；故request.data获取到的就是json数据
    data = json.loads(request.data)  # 将json字符串转为dict
    if 'client_id' not in data:  # 改为form里对应的xx_id
        return JsonResponse.fail(msg='需要传入client_id')
    isOk = db.modify(sql='update client set client_name=%s,phone_number=%s,address=%s where client_id=%s',  # 改为
                     args=[data['client_name'], data['phone_number'], data['address'], data['client_id']])
    return JsonResponse.success(msg='修改成功') if isOk else JsonResponse.fail(msg='修改失败')

@app.route("/delete_client", methods=["DELETE"])  # 删除（单个）
def delete_client():
    # request.args获取请求链接中 ? 后面的所有参数；以字典的方式存储
    if 'client_id' not in request.args:
        return JsonResponse.fail(msg='需要传入client_id')
    isOk = db.modify(sql='delete from client where client_id=%s', args=[request.args['client_id']])
    return JsonResponse.success(msg='删除成功') if isOk else JsonResponse.fail(msg='删除失败')