//============================已执行=======================

*****************************员工表迁移*************************************

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;



