project/newcapec-test/k8s-rancher/1.authx-service/10.0.trans.sql

-- 10.0.trans.sql

/*
  脚本用于 认证v4 的数据迁移
*/

--执行前 TB_B_USER.UID 加索引



-- 更新老认证的密码
UPDATE user.TB_B_USER u, (
  select ACCOUNT_NAME, case when ENCODED_PASSWORD is null then PASSWORD else ENCODED_PASSWORD end as PASSWORD 
  from tmp_data.TMP_ACCOUNT
) a 
SET u.PASSWORD = a.PASSWORD
WHERE u.UID = a.ACCOUNT_NAME 



-- 更新激活状态
update user.TB_B_ACCOUNT a, (
  select TB_B_USER.ID from tmp_data.TMP_ACCOUNT
  inner join user.TB_B_USER on TMP_ACCOUNT.ACCOUNT_NAME=TB_B_USER.UID
  where TMP_ACCOUNT.IS_ACTIVATED=1
) tmp
set a.ACTIVATION=1
where a.USER_ID=tmp.ID



-- 更新老认证的安全邮箱
update user.TB_B_SAFETY s, (
  select TB_B_USER.ID, TMP_TB_ACCOUNTSECURITYEMAIL.EMAILACCOUNTID as ACCOUNTID, EMAILINFO
  from tmp_data.TMP_TB_ACCOUNTSECURITYEMAIL 
  inner join tmp_data.TMP_TB_ACCOUNT on TMP_TB_ACCOUNTSECURITYEMAIL.EMAILACCOUNTID=TMP_TB_ACCOUNT.ACCOUNTKEY
  inner join user.TB_B_USER on TMP_TB_ACCOUNT.ACCOUNTKEY=TB_B_USER.UID
  where EMAILINFO is not null and EMAILINFO!='' and EMAILINFO!='-1' and EMAILSTATUS in ('已验证', '待修改')
) email
set s.SECURE_EMAIL=email.EMAILINFO
where s.USER_ID=email.ID
;

-- 更新老认证的安全手机
update user.TB_B_SAFETY s, (
  select TB_B_USER.ID, TMP_TB_ACCOUNTSECURITYMOBILE.MOBILEACCOUNTID as ACCOUNTID, MOBILEINFO
  from tmp_data.TMP_TB_ACCOUNTSECURITYMOBILE
  inner join tmp_data.TMP_TB_ACCOUNT on TMP_TB_ACCOUNTSECURITYMOBILE.MOBILEACCOUNTID=TMP_TB_ACCOUNT.ACCOUNTKEY
  inner join user.TB_B_USER on TMP_TB_ACCOUNT.ACCOUNTKEY=TB_B_USER.UID
  where MOBILEINFO is not null and MOBILEINFO!='' and MOBILEINFO!='-1' and MOBILESTATUS in ('已验证', '待修改')
) mobile
set s.SECURE_PHONE=mobile.MOBILEINFO
where s.USER_ID=mobile.ID
;




-- 迁移 微信 绑定信息
insert into cas_server.TB_FEDERATION (ID, COMPANY_ID, DELETED, ADD_ACCOUNT, USER_NO, FEDERATED_TYPE, FEDERATED_ID)
select ID, '1', 0, 'trans', 
  ACCOUNT_NAME, 'openweixin', WECHAT_UNIONID
from tmp_data.TMP_ACCOUNT_WECHAT
;


-- 迁移 QQ 绑定信息
insert into cas_server.TB_FEDERATION (ID, COMPANY_ID, DELETED, ADD_ACCOUNT, USER_NO, FEDERATED_TYPE, FEDERATED_ID)
select ID, '1', 0, 'trans', 
  ACCOUNT_NAME, 'qq', QQ_OPENID
from tmp_data.TMP_ACCOUNT_QQ
;