dev/常用SQL语句/dcp对接文档/李珺浩/用户自定义创建流程.txt

//用户表,(数据创建时间,uuid)
select * from ums_user;
//用户密码表
select * from ums_extend_data;

//查询对应用户以及密码加密结果
select uu.id,ued.EXTEND_DATA_VALUE from ums_user uu 
left join ums_extend_data ued
on uu.id = ued.EXTEND_DATA_SOURCEID
where ued.EXTEND_DATA_SOURCEID is not null;

//原sql
INSERT INTO `dcp`.`ums_user` (`id`, `user_name`, `user_uid`, `user_nick_name`, `user_password`, `user_email`, `user_preferred_mobile`, `user_pic`, `language_setting`, `language_setting_dict`, `user_usertype`, `user_usertype_dict`, `personnel_id`, `member_id`, `unit_entity_id`, `user_email_validated_time`, `user_email_status`, `user_mobile_validated_time`, `user_mobile_status`, `user_status`, `user_status_dict`, `user_account_registertime`, `user_account_endtime`, `user_pwd_modified_date`, `last_login_time`, `password_modify_flag`, `operate_state`, `user_extensions`, `invitation_code`, `register_inviter`, `user_remark`, `create_by`, `create_date`, `update_by`, `update_date`) 
VALUES ('999554192698285419', 'G8002111', 'G8002111', 'G8002111', '{SSHA}ZZWFxnMpc5JP20ab9Qmw0E6hf+NihgkD20/OAA==', NULL, NULL, 'personnel/2021/03/01/38a83a0ce1b44ea4a02eb1ffd359ec3d.png', 'zh', 'zh', '会员账号', '2', '999554192698285419', NULL, NULL, '2020-11-30 12:27:27', '0', '2021-01-20 10:51:59', '0', '0', '0', '2020-11-30 12:27:27', NULL, '2021-03-11 16:25:38', '2021-03-11 16:26:01', '0', '0', NULL, NULL, NULL, NULL, NULL, '2020-11-30 12:27:21', '999554192698285419', '2021-03-11 16:26:02');
//需要修改点
INSERT INTO `dcp`.`ums_user` (`id`, `user_name`, `user_uid`, `user_nick_name`, `user_password`, `user_email`, `user_preferred_mobile`, `user_pic`, `language_setting`, `language_setting_dict`, `user_usertype`, `user_usertype_dict`, `personnel_id`, `member_id`, `unit_entity_id`, `user_email_validated_time`, `user_email_status`, `user_mobile_validated_time`, `user_mobile_status`, `user_status`, `user_status_dict`, `user_account_registertime`, `user_account_endtime`, `user_pwd_modified_date`, `last_login_time`, `password_modify_flag`, `operate_state`, `user_extensions`, `invitation_code`, `register_inviter`, `user_remark`, `create_by`, `create_date`, `update_by`, `update_date`) 
VALUES ('需要修改', '需要修改', '需要修改', '需要修改', '{SSHA}ZZWFxnMpc5JP20ab9Qmw0E6hf+NihgkD20/OAA==', NULL, NULL, 'personnel/2021/03/01/38a83a0ce1b44ea4a02eb1ffd359ec3d.png', 'zh', 'zh', '会员账号', '2', '999554192698285419', NULL, NULL, '2020-11-30 12:27:27', '0', '2021-01-20 10:51:59', '0', '0', '0', '2020-11-30 12:27:27', NULL, '需要修改', '需要修改', '0', '0', NULL, NULL, NULL, NULL, NULL, '需要修改', '需要修改', '需要修改');

//原sql
INSERT INTO `dcp`.`ums_extend_data` (`EXTEND_DATA_ID`, `EXTEND_DATA_TYPE_ID`, `EXTEND_DATA_SOURCEID`, `EXTEND_DATA_VALUE`, `EXTEND_DATA_DISPLAY_ORDER`, `EXTEND_DATA_REMARK`, `UPDATE_TIME`, `UPDATE_USER_ID`, `CREATE_TIME`, `CREATE_USER_ID`) 
VALUES ('8', '1', '738642926586421248', '4D27017212A50C06E6CD1C9D8B9A697D', '0', 'b7263b9ad7b111ea83188cec4bbef96b', '2021-03-11 17:35:53', '738642926586421248', '2020-08-06 14:54:47', NULL);
//需要修改的带你
INSERT INTO `dcp`.`ums_extend_data` (`EXTEND_DATA_ID`, `EXTEND_DATA_TYPE_ID`, `EXTEND_DATA_SOURCEID`, `EXTEND_DATA_VALUE`, `EXTEND_DATA_DISPLAY_ORDER`, `EXTEND_DATA_REMARK`, `UPDATE_TIME`, `UPDATE_USER_ID`, `CREATE_TIME`, `CREATE_USER_ID`) 
VALUES ('需要修改', '1', '需要修改(需要跟创建的账号id一致)', '加密结果(通过注册或者重置账号可以找到对应加密逻辑,如果注册接口修改,同时修改加密逻辑)', '0', 'b7263b9ad7b111ea83188cec4bbef96b', '2021-03-11 17:35:53', '738642926586421248', '2020-08-06 14:54:47', NULL);

//企业会员与用户id的对应关系
select unit_id,ums_id from unit_ums_user;

//原SQl
INSERT INTO `dcp`.`unit_ums_user` (`id`, `unit_id`, `ums_id`, `status`, `status_dict`, `create_by`, `create_date`, `update_by`, `update_date`) 
VALUES ('6666666666666666666666666666666666666666666', 'e8456e201c1e11eb803e0894ef7fd169', '999554192698285310', '正常', '0', '693757443114385408', '2020-09-01 11:16:28', '693757443114385408', '2020-09-01 11:16:28');

//需要修改点
INSERT INTO `dcp`.`unit_ums_user` (`id`, `unit_id`, `ums_id`, `status`, `status_dict`, `create_by`, `create_date`, `update_by`, `update_date`) 
VALUES ('需要修改', '需要修改(企业iD,来源于select id from base_unit_entity;)', '需要修改(用户ID)', '正常', '0', '693757443114385408', '2020-09-01 11:16:28', '693757443114385408', '2020-09-01 11:16:28');