招商(cmsk)还原微信关联关系，绑定手机号，报表授权

数据还原

1.还原租户库及报表库

责任人：刘明
时间点：2019-03-29 23:00之前的数据
库：租户库+报表库

2.导入表

责任人：刘明
表(租户库)：p_user_20190329,p_user_to_wx_20190329
表(报表库)：rp_role_user_20190329
说明：将步骤1中的表还原到线上租户库(cmsk)和报表库

3.生成临时表

责任人：梅义
说明：通过步骤2中的备份表生成临时表，供数据还原使用

创建临时表（cmsk租户库）:

DROP TABLE IF EXISTS `erp_user_p_user_temp`;
CREATE TABLE  `erp_user_p_user_temp`(
    `user_code` VARCHAR(100) NOT NULL ,
    `old_user_id` INT(11) NOT NULL ,
    `new_user_id` INT(11) NOT NULL ,
    `new_openid` varchar(50) NOT NULL DEFAULT '',
    `wx_user_id` varchar(100) DEFAULT NULL,
    `user_tel` varchar(30) DEFAULT '' COMMENT '绑定手机号',
    INDEX `idx_openid` USING BTREE (`new_openid`) comment '',
    INDEX `old_user_id` USING BTREE(`old_user_id`) COMMENT '' ,
    INDEX `new_user_id` USING BTREE(`new_user_id`) COMMENT '' ,
    INDEX `wx_user_id` USING BTREE (`wx_user_id`) comment ''
    ) ENGINE = `InnoDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT = COMPACT COMMENT = '';

临时表插入数据:

DELETE
FROM
    p_user_to_wx_20190329
WHERE
    user_id NOT IN(
        SELECT
            user_id
        FROM
            p_user_20190329
    );
TRUNCATE table erp_user_p_user_temp;
INSERT INTO erp_user_p_user_temp(
    user_code ,
    old_user_id ,
    new_user_id,
    new_openid,
    wx_user_id,
    user_tel
) SELECT
    pu.user_code AS user_code ,
    pu0329.user_id AS old_user_id ,
    pu.user_id AS new_user_id,
    pu.openid AS new_openid,
    ptwx0329.wx_user_id AS wx_user_id,
    pu0329.user_tel AS user_tel
FROM
    p_user pu
JOIN p_user_20190329 pu0329 ON  pu0329.user_code = pu.user_code
LEFT JOIN p_user_to_wx_20190329 ptwx0329 ON ptwx0329.user_id = pu0329.user_id

4.将临时表导入到报表库和配置库

责任人：刘明
说明：将处理过后的erp_user_p_user_temp导入到报表库和配置库，表名不变

5.还原p_user.user_tel

责任人：初良祥
说明：通过erp_user_p_user_temp,p_user还原p_user中user_tel

update p_user a inner join erp_user_p_user_temp b on a.user_id=b.new_user_id set a.user_tel=b.user_tel;

6.还原p_user_to_wx

责任人：初良祥
说明：清空p_user_to_wx，通过erp_user_p_user_temp还原p_user_to_wx，还原后做一下“增量同步”

TRUNCATE table p_user_to_wx;
INSERT INTO p_user_to_wx (
    user_id,
    wx_user_id,
    create_time
) SELECT
    new_user_id,
    wx_user_id,
    now()
FROM
    erp_user_p_user_temp
WHERE
    wx_user_id != '' and wx_user_id is not null

7.还原wzs_user.user_tel

责任人：初良祥
说明：通过erp_user_p_user_temp还原wzs_user中user_tel

1、备份配置库wzs_user为wzs_user_20190401(刘明)

update wzs_user a inner join wzs_user_to_tenant b on a.wzs_user_id=b.wzs_user_id
inner join erp_user_p_user_temp c on b.openid=c.new_openid set a.user_tel=c.user_tel

8.还原rp_role_user

责任人：陆磊
说明：通过erp_user_p_user_temp,rp_role_user_20190329还原rp_role_user。注意只还原用户授权部门，部门授权不动

1、报表库rp_role_user 备份为 rp_role_user_20190401(刘明)
2、报表库rp_role_user_20190329 重命名为 rp_role_user(刘明)

3、执行SQL：
update `rp_role_user` as role_user, erp_user_p_user_temp as temp 
set user_id = temp.new_user_id
where role_user.user_id = temp.old_user_id and role_user.member_type = 1;
4、客户检测数据无误，删除 rp_role_user_20190401 表