#注意在obpm2数据库下执行
alter table obpm2.tenant_users add COLUMN certificate_image varchar(255);
alter view obpm2.sync_bg_user_view as select replace(if((locate('/uploads/',replace(`e`.`id_back_img`,'%2F','/')) > 0),substr(replace(`e`.`id_back_img`,'%2F','/'),locate('/uploads/',replace(`e`.`id_back_img`,'%2F','/'))),if((locate('/upload/',replace(`e`.`id_back_img`,'%2F','/')) > 0),substr(replace(`e`.`id_back_img`,'%2F','/'),locate('/upload/',replace(`e`.`id_back_img`,'%2F','/'))),replace(replace(`e`.`id_back_img`,'%2F','/'),substring_index(replace(`e`.`id_back_img`,'%2F','/'),'/',3),'/uploads'))),'"}]','') AS `id_face_photo`,replace(if((locate('/uploads/',replace(`e`.`id_front_img`,'%2F','/')) > 0),substr(replace(`e`.`id_front_img`,'%2F','/'),locate('/uploads/',replace(`e`.`id_front_img`,'%2F','/'))),if((locate('/upload/',replace(`e`.`id_front_img`,'%2F','/')) > 0),substr(replace(`e`.`id_front_img`,'%2F','/'),locate('/upload/',replace(`e`.`id_front_img`,'%2F','/'))),replace(replace(`e`.`id_front_img`,'%2F','/'),substring_index(replace(`e`.`id_front_img`,'%2F','/'),'/',3),'/uploads'))),'"}]','') AS `id_nation_photo`,`e`.`last_auth_time` AS `authenticated_time`,`e`.`name` AS `name`,`e`.`LASTMODIFIED` AS `LASTMODIFIED`,`e`.`item_domain_id` AS `domainid`,`e`.`phone` AS `telephone`,`e`.`user_id` AS `id`,`e`.`gender` AS `sex`,`e`.`birth_place` AS `nationality`,`e`.`height` AS `stature`,`e`.`ethnic_group` AS `nation`,`e`.`education` AS `education`,`e`.`political_status` AS `politics_status`,`e`.`marriage` AS `marital_status`,`e`.`residence` AS `household_type`,`e`.`emergency_contact` AS `emergency_contact`,`e`.`emergency_phone` AS `emergency_phone`,`e`.`military` AS `military_status`,`e`.`item_domain_id` AS `organization_id`,`e`.`auth_status` AS `authenticated_status`,`e`.`check_status` AS `is_certified`,`e`.`id_no` AS `credential_number`,`e`.`id_type` AS `credential_type`,`e`.`address` AS `home_address`,`e`.`id_issue_date` AS `id_validation_s`,`e`.`id_expire_date` AS `id_validation_e`,`e`.`id_address` AS `id_address`,(case when (ifnull(replace(if((locate('/uploads/',replace(`u`.`certificate_image`,'%2F','/')) > 0),substr(replace(`u`.`certificate_image`,'%2F','/'),locate('/uploads/',replace(`u`.`certificate_image`,'%2F','/'))),if((locate('/upload/',replace(`u`.`certificate_image`,'%2F','/')) > 0),substr(replace(`u`.`certificate_image`,'%2F','/'),locate('/upload/',replace(`u`.`certificate_image`,'%2F','/'))),replace(replace(`u`.`certificate_image`,'%2F','/'),substring_index(replace(`u`.`certificate_image`,'%2F','/'),'/',3),'/uploads'))),'"}]',''),'') = '') then replace(if((locate('/uploads/',replace(`u`.`one_inch_color_white_photo`,'%2F','/')) > 0),substr(replace(`u`.`one_inch_color_white_photo`,'%2F','/'),locate('/uploads/',replace(`u`.`one_inch_color_white_photo`,'%2F','/'))),if((locate('/upload/',replace(`u`.`one_inch_color_white_photo`,'%2F','/')) > 0),substr(replace(`u`.`one_inch_color_white_photo`,'%2F','/'),locate('/upload/',replace(`u`.`one_inch_color_white_photo`,'%2F','/'))),replace(replace(`u`.`one_inch_color_white_photo`,'%2F','/'),substring_index(replace(`u`.`one_inch_color_white_photo`,'%2F','/'),'/',3),'/uploads'))),'"}]','') else replace(if((locate('/uploads/',replace(`u`.`certificate_image`,'%2F','/')) > 0),substr(replace(`u`.`certificate_image`,'%2F','/'),locate('/uploads/',replace(`u`.`certificate_image`,'%2F','/'))),if((locate('/upload/',replace(`u`.`certificate_image`,'%2F','/')) > 0),substr(replace(`u`.`certificate_image`,'%2F','/'),locate('/upload/',replace(`u`.`certificate_image`,'%2F','/'))),replace(replace(`u`.`certificate_image`,'%2F','/'),substring_index(replace(`u`.`certificate_image`,'%2F','/'),'/',3),'/uploads'))),'"}]','') end) AS `one_inch_color_white_photo`,`u`.`created_time` AS `created_time`,(case `u`.`background_screening_status` when '1' then 0 when '2' then 1 else NULL end) AS `background_screening_status`,`u`.`last_background_screening_time` AS `last_background_screening_time`,`e`.`hire_date` AS `hired_date` from (`companyinfocollect`.`employees` `e` join `obpm2`.`tenant_users` `u` on((`e`.`user_id` = `u`.`id`)));