保利问题修复SQL

准备工作

租户库创建polyuser表

DROP TABLE IF EXISTS polyuser;
CREATE TABLE polyuser (
id int(11) NOT NULL AUTO_INCREMENT,
user_guid varchar(50) NOT NULL DEFAULT '',
usercodeold varchar(50) NOT NULL DEFAULT '',
usercodenew varchar(50) NOT NULL DEFAULT '',
openid varchar(150) NOT NULL DEFAULT '',
PRIMARY KEY (id),
INDEX idx_user_guid USING BTREE (user_guid) comment '',
INDEX idx_openid USING BTREE (openid) comment ''
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='';

将客户清单转换成sql语句插入到polyuser中

$file = fopen("/Users/meiyi/Desktop/polyuser.csv","r");

$sql = 'insert into  polyuser (user_guid,usercodeold,usercodenew) values';
while(!feof($file)){
$line = fgetcsv($file);
$line[0]=iconv('gb2312','utf-8',$line[0]);
$line[1]=iconv('gb2312','utf-8',$line[1]);
$line[2]=iconv('gb2312','utf-8',$line[2]);
$sql=$sql."('$line[0]','$line[1]','$line[2]'),\n";
}
$sql = substr($sql,0,strlen($sql)-2);$sql.=";";
print  $sql;  fclose($file);

在租户库执行上面生成好的SQL

更新租户库polyuser的openid信息

update polyuser poly join p_user_0109 p on p.user_code = poly.usercodeold set poly.openid = p.openid;

将租户库polyuser拷贝到配置库

通知一线开始数据修复

停用作业

update task_lists set open=0 where tenant_id='my563af8a283896' and task_id='576';

禁用租户

update p_tenants set is_enable = 0 where tenant_id = 'my563af8a283896';

租户库

使用0109相关表覆盖erp_user,p_user,p_user_to_erp,p_user_to_wx

truncate table p_user;
truncate table p_user_to_erp;
truncate table p_user_to_wx;
truncate table erp_user;

insert into p_user select * from p_user_0109;
insert into p_user_to_erp select * from p_user_to_erp_0109;
insert into p_user_to_wx select * from p_user_to_wx_0109;
insert into erp_user select * from erp_user_0109;

取消p_user中user_code 的唯一性

call DropIndexIfExists('p_user','user_code');

更新erp_user

update erp_user inner join polyuser on erp_user.user_guid=polyuser.user_guid set erp_user.user_code=polyuser.usercodenew;

更新p_user

update p_user inner join polyuser on p_user.openid=polyuser.openid set p_user.user_code=polyuser.usercodenew;

更新租户库p_message

insert into p_message
select a.* from p_message_0109 a left join p_message b on a.id=b.id where b.id is null

增加p_user中user_code唯一性

call CreateIndexIfNotExists('p_user','user_code','user_code','UNIQUE');

配置库

删除wzs_user和wzs_user_to_tenant中保利的数据

delete a.*,b.* from wzs_user a inner join wzs_user_to_tenant b on a.wzs_user_id=b.wzs_user_id where b.tenant_id='my563af8a283896';

还原wzs_user中保利的数据

Insert into wzs_user select  a.*  from wzs_user_0109  a inner join wzs_user_to_tenant_0109 b on a.wzs_user_id=b.wzs_user_id where b.tenant_id='my563af8a283896';

还原保利中的 wzs_user_to_tenant

Insert into wzs_user_to_tenant select  b.*  from wzs_user_0109  a inner join wzs_user_to_tenant_0109 b on a.wzs_user_id=b.wzs_user_id where b.tenant_id='my563af8a283896';

更新wzs_user中的erp_user_code

update wzs_user a inner join wzs_user_to_tenant b on a.wzs_user_id=b.wzs_user_id inner join  polyuser c on b.openid=c.openid set a.erp_user_code= c.usercodenew  where b.tenant_id='my563af8a283896';

更新配置库wzs_user_appmsg

INSERT INTO wzs_user_appmsg SELECT a.*
FROM
wzs_user_appmsg_0109 a 
LEFT JOIN wzs_user_appmsg b ON a.wzs_user_id = b.wzs_user_id and a.app_code=b.app_code
WHERE
 a.tenant_id = 'my563af8a283896' AND b.wzs_user_id IS NULL AND a.wzs_user_id IS NOT NULL

收尾

恢复作业

update task_lists set open=1 where tenant_id='my563af8a283896' and task_id='576';

启用租户

update p_tenants set is_enable = 1 where tenant_id = 'my563af8a283896';

全量拉取一次erp用户