base_product.sql
5.58 KB
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
erp_supplier库：
1、base_product：
ALTER TABLE `erp_supplier`.`base_product`
CHANGE COLUMN `grade` `grade` char(20) DEFAULT NULL COMMENT '重点款型(是：1，否:null)',
ADD COLUMN `factory_code` varchar(100) DEFAULT '' COMMENT '厂家编号' AFTER `brand_folder`,
ADD COLUMN `age_level` varchar(50) DEFAULT '' COMMENT '年龄层(1成人 2大童 3小童 逗号分隔)' AFTER `factory_code`,
MODIFY COLUMN `goods_season` smallint(5) NULL DEFAULT 0 COMMENT '1:春2:夏3:秋4:冬5:春夏6:秋冬' AFTER `goods_years`,
ADD COLUMN `is_vip` enum('Y','N','B') NULL DEFAULT 'B' COMMENT '是否VIP Y:是N:否B:品牌设置' AFTER `age_level`,
ADD COLUMN `is_limitbuy` enum('Y','N') NULL DEFAULT 'N' COMMENT '是否限购' AFTER `is_vip`,
ADD COLUMN `limit_start_time` int(10) UNSIGNED NULL DEFAULT 0 COMMENT '限购开始时间' AFTER `is_limitbuy`,
ADD COLUMN `limit_end_time` int(10) NULL DEFAULT 0 COMMENT '限购结束时间' AFTER `limit_start_time`,
ADD COLUMN `expect_shelf_time` int(10) NULL DEFAULT 0 COMMENT '预计上架时间' AFTER `limit_end_time`,
ADD COLUMN `is_outlets` enum('Y','N','B') NULL DEFAULT 'B' COMMENT '是否奥莱 Y:是N:否B:品牌设置' AFTER `expect_shelf_time`,
ADD COLUMN `seasons` varchar(100) DEFAULT '' COMMENT '适销季 夏：summer 冬：winter 四季：seasons',
ADD COLUMN `reason` varchar(255) DEFAULT '' COMMENT '审批驳回原因',
ADD COLUMN `editId` int(10) COMMENT '修改人id',
ADD COLUMN `audit_pass_id` int(10) COMMENT '审核通过人id',
ADD COLUMN `audit_pass_time` int(10) NULL DEFAULT 0 COMMENT '审核通过时间',
ADD COLUMN `audit_fail_id` int(10) COMMENT '审核不通过人id',
ADD COLUMN `audit_fail_time` int(10) NULL DEFAULT 0 COMMENT '审核不通过时间',
ADD COLUMN `operate_info` varchar(255) DEFAULT '' COMMENT '操作信息';
/**建立索引***/
ALTER TABLE `erp_supplier`.`base_product`
ADD INDEX `brand_id` (`brand_id`) comment '',
ADD INDEX `shop_id` (`shop_id`) comment '',
ADD INDEX `supplier_id` (`supplier_id`) comment '',
ADD INDEX `is_auditing` (`is_auditing`) comment '',
ADD INDEX `attribute` (`attribute`) comment '',
ADD INDEX `is_jit` (`is_jit`) comment '',
ADD INDEX `is_advance` (`is_advance`) comment '',
ADD INDEX `is_outlets` (`is_outlets`) comment '';
2、base_goods:
ALTER TABLE `erp_supplier`.`base_goods`
ADD COLUMN `factory_goods_name` varchar(50) COMMENT '厂家颜色' AFTER `goods_name`;
3、base_single：
ALTER TABLE `erp_supplier`.`base_single`
ADD COLUMN `is_suppled` enum('N','Y') DEFAULT 'Y' COMMENT '能否补货' AFTER `size_id`,
ADD COLUMN `presale_storage_num` int(11) unsigned DEFAULT '0' COMMENT '预售库存' AFTER `bar_code`,
ADD INDEX `is_suppled` (`is_suppled`) comment '';
erp_product库：
1、product:
ALTER TABLE `erp_product`.`product`
ADD COLUMN `goods_years` smallint(5) DEFAULT 0,
ADD COLUMN `goods_season` smallint(5) DEFAULT 0 COMMENT '1:春2:夏3:秋4:冬5:春夏6:秋冬' AFTER `goods_years`,
ADD COLUMN `limit_start_time` int(10) UNSIGNED DEFAULT 0 COMMENT '限购开始时间' AFTER `brand_folder`,
ADD COLUMN `limit_end_time` int(10) UNSIGNED DEFAULT 0 COMMENT '限购结束时间' AFTER `limit_start_time`,
ADD COLUMN `expect_shelf_time` int(10) UNSIGNED DEFAULT 0 COMMENT '预计上架时间' AFTER `limit_end_time`,
ADD COLUMN `factory_code` varchar(100) DEFAULT '' COMMENT '厂家编号' AFTER `expect_shelf_time`,
ADD COLUMN `age_level` varchar(50) DEFAULT '1' COMMENT '年龄层(1成人 2大童 3小童 逗号分隔)' AFTER `factory_code`,
CHANGE COLUMN `grade` `grade` char(20) DEFAULT NULL COMMENT '重点款型(是：K，否:null)',
MODIFY COLUMN `is_outlets` enum('Y','N','B') DEFAULT 'B' COMMENT '是否奥莱 Y:是N:否B:品牌设置' AFTER `is_promotion`,
ADD COLUMN `is_promotional_gifts` enum('Y','N') DEFAULT 'N' AFTER `goods_season`,
ADD COLUMN `is_vip` enum('Y','N','B') DEFAULT 'B' COMMENT '是否VIP Y:是N:否B:品牌设置' AFTER `is_promotional_gifts`,
ADD COLUMN `is_limitbuy` enum('Y','N') DEFAULT 'N' COMMENT '是否限购' AFTER `is_vip`,
ADD COLUMN `audit_pass_id` int(10) COMMENT '审核通过人id',
ADD COLUMN `audit_pass_time` int(10) NULL DEFAULT 0 COMMENT '审核通过时间',
ADD COLUMN `audit_fail_id` int(10) COMMENT '审核不通过人id',
ADD COLUMN `audit_fail_time` int(10) NULL DEFAULT 0 COMMENT '审核不通过时间',
ADD COLUMN `operate_info` varchar(255) DEFAULT '' COMMENT '操作信息';
/***添加索引****/
ALTER TABLE `erp_product`.`product`
ADD INDEX `brand_id` (`brand_id`) comment '',
ADD INDEX `shop_id` (`shop_id`) comment '',
ADD INDEX `supplier_id` (`supplier_id`) comment '',
ADD INDEX `attribute` (`attribute`) comment '',
ADD INDEX `is_jit` (`is_jit`) comment '',
ADD INDEX `is_outlets` (`is_outlets`) comment '',
ADD INDEX `is_advance` (`is_advance`) comment '';
2、goods:
ALTER TABLE `erp_product`.`goods`
ADD COLUMN `factory_goods_name` varchar(50) COMMENT '厂家颜色' AFTER `goods_name`;
3、storage：
ALTER TABLE `erp_product`.`storage`
ADD COLUMN `is_suppled` enum('Y','N') DEFAULT 'Y' COMMENT '能否补货' AFTER `update_time`,
ADD COLUMN `presale_storage_num` int(11) unsigned DEFAULT '0' COMMENT '预售库存' AFTER `bar_code`,
ADD INDEX `is_suppled` (`is_suppled`) comment '';
ALTER TABLE `erp_product`.`storage`
DROP COLUMN `presale_storage_num`;
4、product_price:
ALTER TABLE `erp_product`.`product_price`
CHANGE COLUMN `founder` `founder` int(11) UNSIGNED DEFAULT 0 COMMENT '操作人ID',
ADD COLUMN `enable_vip` tinyint(3) UNSIGNED DEFAULT 2 COMMENT '是否享受vip折扣：1 根据品牌设置进行折扣 2 开启 3 关闭' AFTER `founder`;