//============================已执行=======================
|
|
*****************************员工表迁移*************************************
|
|
alter table sys_users
|
add SIGN_ORDER int null;
|
|
alter table sys_users
|
add sign_date datetime null;
|
|
|
ALTER TABLE `sys_users` ADD COLUMN `degree` varchar(255) DEFAULT NULL;
|
ALTER TABLE `sys_users` ADD COLUMN `work_exp` varchar(1000) DEFAULT NULL;
|
ALTER TABLE `sys_users` ADD COLUMN `entry_date` datetime DEFAULT NULL;
|
ALTER TABLE `sys_users` ADD COLUMN `wx_no` varchar(100) DEFAULT NULL;
|
ALTER TABLE `sys_users` ADD COLUMN `en_name` varchar(50) DEFAULT NULL;
|
alter table sys_users
|
add emerg_tel varchar(20) null;
|
|
alter table sys_users
|
add service_signature varchar(200) null;
|
|
|
update sys_users a set a.su_id=(select id from sys_shopstaff_info b where a.su_name=b.STAFF_NAME )
|
where a.su_id not in (1001,1002,65,75,79,78);
|
|
|
|
*******************************修改字段**********************************
|
|
ALTER TABLE `sys_role` ADD COLUMN `is_default` int(1) NULL COMMENT '是否默认' AFTER `plates`;
|
|
|
ALTER TABLE `sys_beautician_state` ADD COLUMN `puse_id` bigint(20) NULL DEFAULT NULL COMMENT '项目使用情况id' AFTER `extract`;
|
|
alter table sys_shop_info add company_id bigint null comment '公司id';
|
|
|
ALTER TABLE `sys_beautician_state` ADD COLUMN `count` int NULL COMMENT '项目次数' AFTER `puse_id`;
|
|
|
ALTER TABLE `sys_beautician_state` ADD COLUMN `proj_id` bigint NULL COMMENT '项目id' AFTER `count`;
|
|
ALTER TABLE `sys_proj_services` DROP COLUMN `PROJ_USE_ID`;
|
|
ALTER TABLE `sys_beautician_state` DROP COLUMN `STAFF_COMMENT`;
|
|
|
alter table sys_proj_services
|
add consume_time datetime null comment '划扣时间';
|
|
alter table sys_order
|
add pay_time datetime null comment '收款时间';
|
|
|
/**
|
* 经营数据
|
* @author
|
* @date 2020-02-08 10:18
|
*/
|
CREATE TABLE sys_business_data
|
(
|
create_by varchar(100) NOT NULL COMMENT '创建人',
|
create_time datetime NOT NULL COMMENT '创建时间',
|
update_by varchar(100) NOT NULL COMMENT '更新人',
|
update_time datetime NOT NULL COMMENT '更新时间',
|
id int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
|
time date COMMENT '日期',
|
|
company_id int COMMENT '公司',
|
|
shop_id int COMMENT '门店',
|
|
name varchar(50) COMMENT '字段名称',
|
|
value varchar(1000) COMMENT '值',
|
|
code varchar(50) COMMENT '唯一编码',
|
|
sequence int COMMENT '排序角标',
|
|
data_type int COMMENT '数据类型',
|
|
t1 varchar(50) COMMENT '扩展字段1',
|
|
t2 varchar(50) COMMENT '扩展字段2',
|
|
t3 varchar(50) COMMENT '扩展字段3',
|
|
t4 varchar(50) COMMENT '扩展字段4',
|
|
t5 varchar(50) COMMENT '扩展字段5',
|
|
t6 varchar(50) COMMENT '扩展字段6',
|
|
t7 varchar(50) COMMENT '扩展字段7',
|
|
t8 varchar(50) COMMENT '扩展字段8',
|
|
t9 varchar(50) COMMENT '扩展字段9',
|
|
t10 varchar(50) COMMENT '扩展字段10',
|
|
t11 varchar(50) COMMENT '扩展字段11',
|
|
t12 varchar(50) COMMENT '扩展字段12',
|
|
t13 varchar(50) COMMENT '扩展字段13',
|
|
t14 varchar(50) COMMENT '扩展字段14',
|
|
t15 varchar(50) COMMENT '扩展字段15',
|
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8 COMMENT ='经营数据';
|
|
*************库存表修改**************
|
|
alter table sys_goods
|
add alarm_num int null;
|
|
alter table sys_goods
|
add volume int null;
|
|
alter table sys_goods
|
add price decimal(10,2) null;
|
|
alter table sys_goods
|
add wholesale decimal(10,2) null;
|
|
库存产品数据迁移
|
|
update sys_goods b set alarm_num=(select alarm_num from sku a where a.goods_id=b.ID);
|
update sys_goods b set volume=(select volume from sku a where a.goods_id=b.ID);
|
update sys_goods b set price=(select price from sku a where a.goods_id=b.ID);
|
update sys_goods b set wholesale=(select wholesale from sku a where a.goods_id=b.ID);
|
|
把产品主键替换成了sku主键，保障库存的正确性
|
update sys_store_info a set a.SKU_ID=(
|
select b.id from sys_goods b
|
left join sku c on c.goods_id=b.ID
|
where c.id=a.SKU_ID
|
);
|
|
|
|
|
update sys_goods a set a.NAME=
|
(select b.name from sku b where b.goods_id=a.ID );
|
|
要切换主键才能执行成功
|
update sys_goods a set a.id=
|
(select b.id from sku b where b.goods_id=a.ID );
|
|
|
********************服务单*******************************
|
ALTER TABLE `sys_beautician_state`
|
DROP COLUMN `STAFF_COMMENT`;
|
|
|
ALTER TABLE `service_bea_proj`
|
ADD COLUMN `STAFF_ID` bigint(20) NULL AFTER `ser_proj_id`,
|
ADD COLUMN `STATE` varchar(20) NULL AFTER `STAFF_ID`,
|
ADD COLUMN `BEGIN_TIME` datetime NULL AFTER `STATE`,
|
ADD COLUMN `END_TIME` datetime NULL AFTER `BEGIN_TIME`,
|
ADD COLUMN `SERVICES_ID` bigint NULL AFTER `END_TIME`,
|
ADD COLUMN `SHOP_ID` bigint NULL AFTER `SERVICES_ID`,
|
ADD COLUMN `EXC_TIME` int NULL AFTER `SHOP_ID`,
|
ADD COLUMN `extract` varchar(255) NULL AFTER `EXC_TIME`,
|
ADD COLUMN `puse_id` bigint(20) NULL AFTER `extract`,
|
ADD COLUMN `count` int(255) NULL AFTER `puse_id`;
|
|
|
|
|
update service_bea_proj a , sys_ser_proj b
|
set a.puse_id=b.PRO_USEID,
|
a.count=1
|
where a.ser_proj_id=b.id;
|
|
update service_bea_proj a , sys_proj_use b
|
set a.proj_id=b.PROJ_ID,
|
a.count=1
|
where a.puse_id=b.id;
|
|
DELETE from sys_beautician_state;
|
update service_bea_proj a , sys_beautician_state b
|
set a.STAFF_ID=b.STAFF_ID,
|
a.STATE=b.STATE,
|
a.BEGIN_TIME=b.BEGIN_TIME,
|
a.END_TIME=b.END_TIME,
|
a.SERVICES_ID=b.SERVICES_ID,
|
a.SHOP_ID=b.SHOP_ID,
|
a.EXC_TIME=b.EXC_TIME,
|
a.extract=b.extract,
|
a.count=1
|
where a.bea_state_id=b.id;
|
|
|
INSERT INTO sys_beautician_state (STAFF_ID,
|
STATE,
|
BEGIN_TIME,
|
END_TIME,
|
SERVICES_ID,
|
EXC_TIME,
|
extract,
|
puse_id,
|
count,
|
proj_id,
|
SHOP_ID)
|
select STAFF_ID,
|
STATE,
|
BEGIN_TIME,
|
END_TIME,
|
SERVICES_ID,
|
EXC_TIME,
|
extract,
|
puse_id,
|
count,
|
proj_id,
|
SHOP_ID
|
from service_bea_proj;
|
|
update sys_proj_services a set a.consume_time=a.CREATE_TIME;
|
update sys_order set pay_time=ORDER_TIME;
|
|
******************************删除多余表*************************************
|
drop table sys_shopstaff_info;
|
