-- ----------------------------
-- Procedure structure for proc_sync_attendance_PRINCIPAL_by_tenant_users_credentials
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_PRINCIPAL_by_tenant_users_credentials`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_attendance_PRINCIPAL_by_tenant_users_credentials`(IN p_tenant_user_id VARCHAR(300))
BEGIN

#驻勤点表负责人相关信息更新
UPDATE companyinfocollect.tlk_attendance_site_base_info a,obpm2.tenant_users b,obpm2.tenant_user_credentials c,obpm2.tenant_employees d
SET a.ITEM_PRINCIPAL = d.id,
 a.ITEM_PRINCIPAL_NAME = b.name,
 a.ITEM_PRINCIPAL_CARD_TYPE = (CASE c.credential_type when 0 THEN '居民身份证/户口簿' when 1 then '香港特区护照/身份证' 
when 2 then '澳门特区护照/身份证' when 3 then '台湾居民来往大陆通行证' when 4 then '外国护照' when 5 then '退伍军人证' 
when 6 then '警号' when 7 then '资格证' when 8 then '等级证' when 9 then '结业证' when 10 then '持枪证' END),
 a.ITEM_PRINCIPAL_IDCARD_NO = c.number,
 a.ITEM_PRINCIPAL_PHONE_NO= b.telephone,
 a.ITEM_PRINCIPAL_IS_CERTIFIED= (CASE b.checked_status when 0 then '未提交' when 1 then '已核验' when 2 then '已提交' END),a.LASTMODIFIED=NOW()
WHERE
	a.ITEM_PRINCIPAL = d.id AND d.tenant_user_id=b.id and b.selected_credential_id = c.id  and b.id=p_tenant_user_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_attendance_PRINCIPAL_update_by_tenant_users
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_PRINCIPAL_update_by_tenant_users`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_attendance_PRINCIPAL_update_by_tenant_users`(in after_id VARCHAR(300),after_name VARCHAR(300),after_phone VARCHAR(300),after_checked_status varchar(300))
BEGIN
#驻勤点表负责人相关信息更新(除身份证外)
UPDATE companyinfocollect.tlk_attendance_site_base_info a
SET a.ITEM_PRINCIPAL_NAME = after_name,
 /*a.ITEM_PRINCIPAL_CARD_TYPE = (CASE c.credential_type when 0 THEN '居民身份证/户口簿' when 1 then '香港特区护照/身份证' 
when 2 then '澳门特区护照/身份证' when 3 then '台湾居民来往大陆通行证' when 4 then '外国护照' when 5 then '退伍军人证' 
when 6 then '警号' when 7 then '资格证' when 8 then '等级证' when 9 then '结业证' when 10 then '持枪证' END),*/
#a.ITEM_PRINCIPAL_IDCARD_NO = c.number,
a.ITEM_PRINCIPAL_PHONE_NO = after_phone,
 a.ITEM_PRINCIPAL_IS_CERTIFIED = (
	CASE after_checked_status
	WHEN 0 THEN
		'未提交'
	WHEN 1 THEN
		'已核验'
	WHEN 2 THEN
		'已提交'
	END
),
 a.LASTMODIFIED = NOW()
WHERE
	a.ITEM_PRINCIPAL_USER_ID = after_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_attendance_PRINCIPAL_update_tenant_user_credentialsP
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_PRINCIPAL_update_tenant_user_credentialsP`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_attendance_PRINCIPAL_update_tenant_user_credentialsP`(in after_tenant_user_id VARCHAR(300),after_number VARCHAR(300),before_number VARCHAR(300),after_credential_type VARCHAR(300),before_credential_type VARCHAR(300))
BEGIN
#驻勤点表负责人身份证信息
IF(before_number!=after_number and before_credential_type=0)
THEN
UPDATE companyinfocollect.tlk_attendance_site_base_info a
SET a.ITEM_PRINCIPAL_CARD_TYPE = (
	CASE after_credential_type
	WHEN 0 THEN
		'居民身份证/户口簿'
	WHEN 1 THEN
		'香港特区护照/身份证'
	WHEN 2 THEN
		'澳门特区护照/身份证'
	WHEN 3 THEN
		'台湾居民来往大陆通行证'
	WHEN 4 THEN
		'外国护照'
	WHEN 5 THEN
		'退伍军人证'
	WHEN 6 THEN
		'警号'
	WHEN 7 THEN
		'资格证'
	WHEN 8 THEN
		'等级证'
	WHEN 9 THEN
		'结业证'
	WHEN 10 THEN
		'持枪证'
	END
),
 a.ITEM_PRINCIPAL_IDCARD_NO = after_number,
 a.LASTMODIFIED = NOW()
WHERE
	a.ITEM_PRINCIPAL_USER_ID = after_tenant_user_id;
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_attendance_REALITY_update_by_tenant_users
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_REALITY_update_by_tenant_users`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_attendance_REALITY_update_by_tenant_users`(in p_id varchar(300),before_name VARCHAR(300),after_name VARCHAR(300),before_phone VARCHAR(300),after_phone varchar(300))
BEGIN
-- 挂靠人姓名手机号更新
IF(before_name!=after_name or before_phone!=after_phone or (before_phone is NULL and after_phone is not null))
THEN
UPDATE companyinfocollect.tlk_attendance_site_base_info a
 #obpm2.tenant_users b,
 #obpm2.tenant_user_credentials c
SET a.ITEM_REALITY_NAME = after_name,
 #a.ITEM_REALITY_CARD_NO = c.number,
 a.ITEM_REALITY_PHONE = after_phone,
 a.LASTMODIFIED = NOW()
WHERE
	a.ITEM_USERID = p_id;
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_attendance_REALITY_update_by_tenant_user_credentials
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_REALITY_update_by_tenant_user_credentials`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_attendance_REALITY_update_by_tenant_user_credentials`(after_tenant_user_id varchar(300),before_number VARCHAR(300),after_number VARCHAR(300),before_credential_type VARCHAR(300))
BEGIN
-- 挂靠人身份证更新
IF(before_number!=after_number and before_credential_type=0)
THEN
UPDATE companyinfocollect.tlk_attendance_site_base_info a
 #obpm2.tenant_users b,
 #obpm2.tenant_user_credentials c
SET #a.ITEM_REALITY_NAME = after_name,
 a.ITEM_REALITY_CARD_NO = after_number,
 #a.ITEM_REALITY_PHONE = b.telephone,
 a.LASTMODIFIED = NOW()
WHERE
	a.ITEM_USERID = after_tenant_user_id;
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_attendance_site_base_info_by_tenant_organizations
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_site_base_info_by_tenant_organizations`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_attendance_site_base_info_by_tenant_organizations`(IN p_domain_id LONGTEXT,befor_companyName VARCHAR(300),after_companyName VARCHAR(300),befor_depart_name VARCHAR(300),after_depart_name VARCHAR(300),befor_depart_id VARCHAR(300),after_depart_id VARCHAR(300))
begin 
IF(befor_companyName!=after_companyName or befor_depart_name!=after_depart_name or befor_depart_id!=after_depart_id )
THEN
UPDATE companyinfocollect.tlk_attendance_site_base_info a,
 obpm2.tenant_organizations b
SET a.ITEM_COMPANY_NAME = b.`name`,
 a.ITEM_COMPANY_POLICE = b.supervise_depart_name,
 a.ITEM_COMPANY_POLICE_CODE = b.supervise_depart_id,
 a.LASTMODIFIED=NOW()
WHERE
	a.ITEM_DOMAIN_ID = b.id
AND b.id = p_domain_id; 
END IF;
end
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_attendance_site_base_info_by_tenant_users_credentials
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_site_base_info_by_tenant_users_credentials`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_attendance_site_base_info_by_tenant_users_credentials`(IN p_tenant_user_id VARCHAR(300))
BEGIN
#驻勤点表挂靠人相关信息更新
UPDATE companyinfocollect.tlk_attendance_site_base_info a,obpm2.tenant_users b,obpm2.tenant_user_credentials c
SET a.ITEM_REALITY_NAME = b.name,
 a.ITEM_REALITY_CARD_NO = c.number,
 a.ITEM_REALITY_PHONE = b.telephone,
 a.ITEM_USERID = b.id,a.LASTMODIFIED=NOW()
WHERE
	a.ITEM_USERID = b.id and b.selected_credential_id=c.id AND b.id = p_tenant_user_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_attendance_site_base_update_by_ContractToEnterprise
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_site_base_update_by_ContractToEnterprise`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_attendance_site_base_update_by_ContractToEnterprise`(IN p_contract_id VARCHAR (300))
BEGIN
#驻勤点表合同相关信息更新
	UPDATE companyinfocollect.tlk_attendance_site_base_info a,
	baibaodunflow.tlk_contracttoenterprise b
SET 
	a.ITEM_CONTRACT_NAME=b.ITEM_CONTRACTNAME,
	a.ITEM_CONTRACT_STATE=b.ITEM_CONTRACTSTATE,
	a.ITEM_CONTRACT_START_DATE=b.ITEM_CONTRACTSTARTDATE,
	a.ITEM_CONTRACT_END_DATE=b.ITEM_CONTRACTENDDATE,
	a.ITEM_CONTRACT_FILE=b.ITEM_CONTRACTFILE,a.LASTMODIFIED=NOW()
where 
	a.ITEM_CONTRACT_IDS=b.id and a.ITEM_CONTRACT_IDS=p_contract_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_att_site_update_attPersonNum_by_att_site_person_info
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_att_site_update_attPersonNum_by_att_site_person_info`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_att_site_update_attPersonNum_by_att_site_person_info`(in p_before_id VARCHAR(300),p_after_id VARCHAR(300),p_before_attendanceSiteId VARCHAR(300),p_after_attendanceSiteId VARCHAR(300))
BEGIN
#驻勤人员表发生变化时更新驻勤点驻勤人数
        DECLARE personNum VARCHAR(200);#人数

IF(p_after_id is null) 
        THEN 
        select count(DISTINCT ITEM_SECURITYID) from baibaodunflow.tlk_attendance_site_person_info a 
  where a.ITEM_attendanceSiteId=p_before_attendanceSiteId  into personNum ;
        UPDATE companyinfocollect.tlk_attendance_site_base_info a
        set a.ITEM_ATT_PERSON_NUM=personNum,a.LASTMODIFIED=NOW()
        where a.ITEM_ATTENDANCE_ID=p_before_attendanceSiteId;
ELSEIF(p_after_id IS NOT NULL AND p_before_id IS  NULL) 
        THEN 
        select count(DISTINCT ITEM_SECURITYID) from baibaodunflow.tlk_attendance_site_person_info a 
  where a.ITEM_attendanceSiteId=p_after_attendanceSiteId  into personNum ;
        UPDATE companyinfocollect.tlk_attendance_site_base_info a
        set ITEM_ATT_PERSON_NUM=personNum,a.LASTMODIFIED=NOW()
        where a.ITEM_ATTENDANCE_ID=p_after_attendanceSiteId;

END IF;

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_att_site_update_attPersonNum_by_employees_v3
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_att_site_update_attPersonNum_by_employees_v3`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_att_site_update_attPersonNum_by_employees_v3`(in p_before_att_site_id VARCHAR(300),p_after_att_site_id VARCHAR(300), p_before_employed int,p_after_employed int)
BEGIN
	#Routine body goes here...
IF p_before_employed IS NULL OR p_before_employed!= p_after_employed OR p_before_att_site_id IS NULL OR p_before_att_site_id = '' OR p_after_att_site_id IS NULL OR p_after_att_site_id = '' OR p_before_att_site_id != p_after_att_site_id THEN
	UPDATE companyinfocollect.tlk_attendance_site_base_info
	SET ITEM_ATT_PERSON_NUM = (SELECT COUNT(1) FROM companyinfocollect.employees e
															WHERE e.att_site_id = IFNULL(p_after_att_site_id,
															p_before_att_site_id) 
															AND e.employed = 0),
			LASTMODIFIED=NOW()
	WHERE ITEM_ATTENDANCE_ID = IFNULL(p_after_att_site_id,p_before_att_site_id);

END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_commendation_detail_by_tlk_awardk_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_commendation_detail_by_tlk_awardk_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_commendation_detail_by_tlk_awardk_v2`(IN p_idsStr VARCHAR(1000), p_id_before VARCHAR(300),o_ITEM_REGIONId VARCHAR(200))
BEGIN
	#表彰奖励信息归集
IF p_idsStr is null then 
DELETE from commendation_detail where commendation_id  = p_id_before;
INSERT INTO data_deleted_events(database_name,table_name,primarykey_name,primarykey_value,DOMAINID,LASTMODIFIED,region_code,created_time) VALUES('companyinfocollect','commendation_detail','commendation_id',p_id_before,(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),NOW(),o_ITEM_REGIONId,NOW());
ELSE
INSERT INTO companyinfocollect.commendation_detail (
		commendation_id,
		`NAME`,
		user_id,
		commendation_type,
		commend_organization,
		security_organs_commend,
		content,
		reason,
		note,
		`date`,
		company_name,
		ITEM_DOMAIN_ID,
		CREATED,
		LASTMODIFIED,
		DOMAINID,
		last_sync_time
	) SELECT
		IFNULL(a.id, ''),
		IFNULL(a.item_securityname, ''),
		IFNULL(c.id, ''),
		IFNULL(a.item_awardtype, ''),
		IFNULL(a.item_awardOrg, ''),
		CASE IFNULL(a.ITEM_isAward, '')
	WHEN '否' THEN
		'0'
	WHEN '是' THEN
		'1'
	ELSE
		IFNULL(a.ITEM_isAward, '')
	END AS ITEM_isAward,
	IFNULL(a.ITEM_AWARDCONTENT, ''),
	IFNULL(a.ITEM_AWARDREASON, ''),
	IFNULL(a.ITEM_REMARK, ''),
	IFNULL(a.ITEM_AWARDDATE, ''),
	IFNULL(f.name, ''),
	IFNULL(a.DOMAINID, ''),
	IFNULL(a.CREATED, ''),
	#IFNULL(a.LASTMODIFIED, ''),
	NOW(),
	(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END) AS domain_id,
		now()
FROM
	baibaodunflow.tlk_awardk a
LEFT JOIN obpm2.tenant_user_credentials b ON a.item_documentid = b.number
LEFT JOIN obpm2.tenant_users c ON b.tenant_user_id = c.id
LEFT JOIN baibaodunflow.tlk_setsupervise s ON a.DOMAINID = s.DOMAINID
LEFT JOIN organizationauth.tlk_DepartmentLevel d ON d.ID = s.ITEM_REGISTERPOLICEADDRESSID
LEFT JOIN obpm2.tenant_organizations f on a.DOMAINID = f.id
#WHERE find_in_set(a.id, p_idsStr)
WHERE a.id = p_idsStr
	ON DUPLICATE KEY UPDATE commendation_id = IFNULL(a.id, ''),
	NAME = IFNULL(a.item_securityname, ''),
	user_id = IFNULL(c.id, ''),
	commendation_type = IFNULL(a.item_awardtype, ''),
	commend_organization = IFNULL(a.item_awardOrg, ''),
	security_organs_commend = CASE IFNULL(a.ITEM_isAward, '')
WHEN '否' THEN
	'0'
WHEN '是' THEN
	'1'
ELSE
	IFNULL(a.ITEM_isAward, '')
END,
 content = IFNULL(a.ITEM_AWARDCONTENT, ''),
 reason = IFNULL(a.ITEM_AWARDREASON, ''),
 note = IFNULL(a.ITEM_REMARK, ''),
 date = IFNULL(a.ITEM_AWARDDATE, ''),
 company_name = IFNULL(f.name, ''),
 ITEM_DOMAIN_ID = IFNULL(a.DOMAINID, ''),
 CREATED = IFNULL(a.CREATED, ''),
 LASTMODIFIED = NOW(),	#IFNULL(a.LASTMODIFIED, ''),
 DOMAINID = (CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),
		last_sync_time = now();
		end if;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_contract_to_enterprise_info_by_organization
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_contract_to_enterprise_info_by_organization`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_contract_to_enterprise_info_by_organization`(IN p_domain_id VARCHAR(300),befor_companyName VARCHAR(300),after_companyName VARCHAR(300),befor_depart_name VARCHAR(300),after_depart_name VARCHAR(300),befor_depart_id VARCHAR(300),after_depart_id VARCHAR(300))
BEGIN
IF(befor_companyName!=after_companyName or befor_depart_name!=after_depart_name or befor_depart_id!=after_depart_id )
THEN
UPDATE companyinfocollect.tlk_contract_to_enterprise a
-- obpm2.tenant_organizations b
SET a.ITEM_BELONG_ENTERPRISE = after_companyName,
 a.ITEM_company_police = after_depart_name,
 a.ITEM_company_police_code = after_depart_id,
 a.LASTMODIFIED=NOW()
WHERE
 a.ITEM_DOMAIN_ID = p_domain_id; 
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_employed_events_info_collect_by_employee_events_v3
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employed_events_info_collect_by_employee_events_v3`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_employed_events_info_collect_by_employee_events_v3`(IN p_event_id VARCHAR(300),p_employee_id VARCHAR(300),p_event_type INT,p_note VARCHAR(2000), p_event_time DATETIME, p_created_time DATETIME, 
p_interview VARCHAR(300),
p_org_id VARCHAR(100), p_org_name VARCHAR(300), p_org_approved_status VARCHAR(100), p_org_supervise_region_code VARCHAR(300),p_org_office_type VARCHAR(200),
p_user_id VARCHAR(100),p_user_name VARCHAR(300))
BEGIN

/*归集智能人事入离职记录入行业信息。
只需要归集机构类型为保安公司和自招单位的入离职记录
公司的审核状态为审核通过的数据才会被展示并送入内网*/
	
	-- 机构类型，只归集保安公司+自招单位的入离职记录
	#如果事件类型为入职（0）和离职（1）才触发。p_event_id出现过为null的情况。这里加判断防止程序终止，之后以数据丢失来定位问题。
IF p_event_id IS NOT NULL AND (p_event_type = 0 OR p_event_type = 1) THEN
	#面谈记录来自智能人事的职员表的interview字段。离职原因来自入离职记录表的note字段
	
	-- 入离职记录只归集保安公司 + 自招单位
	IF p_org_office_type = '0102' OR p_org_office_type = '0104' THEN
	
	INSERT INTO `companyinfocollect`.`employed_events`(
	event_id,
	`employee_id`, 
	`employed`, 
	`create_time`, 
	`LASTMODIFIED`,
	`interview_notes`, 
	`leave_reason`, 
	`hire_date`, 
	`leave_date`, 
	`company_name`, 
	`company_id`, 
	`user_id`,
	`name`,
	`domainid`,
	item_domain_id,
	region_id,
	last_sync_time
	) 
	SELECT t.event_id,t.employee_id,t.employed_status,t.created_time,t.LASTMODIFIED,t.interview_note,t.leave_reason,t.hired_date,t.leave_date,t.company_name,t.company_id,t.user_id,t.user_name,t.domain_id,t.item_domain_id,t.region_id,t.last_sync_time
	FROM
	(SELECT
	p_event_id as event_id,
	IFNULL(p_employee_id,'') AS employee_id,
	p_event_type AS employed_status,
	p_created_time AS created_time,
	#p_created_time AS LASTMODIFIED,
	NOW() AS LASTMODIFIED,
	p_interview AS interview_note,
	(CASE p_event_type
	WHEN 1 THEN
		p_note
	ELSE
		''
END) AS leave_reason,
	 IF(p_event_type = 0,p_event_time,NULL)AS hired_date,
	 IF(p_event_type = 1,p_event_time,NULL)AS leave_date,
	IFNULL(p_org_name,'') AS company_name,
	
	IF(p_org_id IS NULL,'',CONCAT(REPLACE(p_org_id,'--','__'),'--__2eoK1zvtxNarHlwwQg4')) AS company_id,
	IFNULL(p_user_id,'') AS user_id,
	IFNULL(p_user_name,'') AS user_name,
	#公司审核通过的才会设置domainid，否则设为#
	IF(p_org_approved_status='1',(CASE 
		WHEN p_org_supervise_region_code LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN p_org_supervise_region_code LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN p_org_supervise_region_code LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			'#'
		END),'#') AS domain_id,
		p_org_id AS item_domain_id,
		IFNULL(p_org_supervise_region_code,'#') AS region_id,
		CURRENT_TIMESTAMP AS last_sync_time
		) AS t	
		
	ON DUPLICATE KEY UPDATE
	`employee_id` =  t.employee_id,
	`employed` = t.employed_status,
	`create_time` =  t.created_time,
	`LASTMODIFIED` = t.LASTMODIFIED,
	`interview_notes` = t.interview_note, 
	`leave_reason` = t.leave_reason,
	`hire_date` = t.hired_date, 
	`leave_date` = t.leave_date, 
	`company_name`= t.company_name, 
	`company_id` = t.company_id, 
	`user_id` = t.user_id,
	`name` = t.user_name,
	`domainid`= t.domain_id,
	item_domain_id = t.item_domain_id,
	region_id = t.region_id,
	last_sync_time = CURRENT_TIMESTAMP;
	END IF;
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_employed_events_info_collect_by_employee_events_v4
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employed_events_info_collect_by_employee_events_v4`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_employed_events_info_collect_by_employee_events_v4`(
	IN p_event_id INT,
	p_employee_id VARCHAR (300),
	p_event_type INT,
	p_note VARCHAR (2000),
	p_event_time TIMESTAMP,
	p_created_time TIMESTAMP,
	p_interview VARCHAR (300),
	p_org_id VARCHAR (100),
	p_org_name VARCHAR (300),
	p_org_approved_status VARCHAR (100),
	p_org_supervise_region_code VARCHAR (300),
	p_org_office_type VARCHAR (200),
	p_user_id VARCHAR (100),
	p_user_name VARCHAR (300),
	p_occupation_type INT
)
BEGIN
	/*归集智能人事入离职记录入行业信息。
只需要归集机构类型为保安公司和自招单位的入离职记录
公司的审核状态为审核通过的数据才会被展示并送入内网*/
	-- 机构类型，只归集保安公司+自招单位的入离职记录
	#如果事件类型为入职（0）和离职（1）才触发。p_event_id出现过为null的情况。这里加判断防止程序终止，之后以数据丢失来定位问题。
IF p_event_id IS NOT NULL
AND (
	p_event_type = 0
	OR p_event_type = 1
) THEN
	#面谈记录来自智能人事的职员表的interview字段。离职原因来自入离职记录表的note字段
	-- 入离职记录只归集保安公司 + 自招单位
IF p_org_office_type = '0102'
OR p_org_office_type = '0104' THEN
	INSERT INTO `companyinfocollect`.`employed_events` (
		event_id,
		`employee_id`,
		`employed`,
		`create_time`,
		`LASTMODIFIED`,
		`interview_notes`,
		`leave_reason`,
		`hire_date`,
		`leave_date`,
		`company_name`,
		`company_id`,
		`user_id`,
		`name`,
		`domainid`,
		item_domain_id,
		region_id,
		last_sync_time,
		occupation_type
	) SELECT
		t.event_id,
		t.employee_id,
		t.employed_status,
		t.created_time,
		t.LASTMODIFIED,
		t.interview_note,
		t.leave_reason,
		t.hired_date,
		t.leave_date,
		t.company_name,
		t.company_id,
		t.user_id,
		t.user_name,
		t.domain_id,
		t.item_domain_id,
		t.region_id,
		t.last_sync_time,
		t.occupation_type
	FROM
		(
			SELECT
				p_event_id AS event_id,
				IFNULL(p_employee_id, '') AS employee_id,
				p_event_type AS employed_status,
				p_created_time AS created_time,
				#p_created_time AS LASTMODIFIED,
				NOW() AS LASTMODIFIED,
				p_interview AS interview_note,
				(
					CASE p_event_type
					WHEN 1 THEN
						p_note
					ELSE
						''
					END
				) AS leave_reason,

			IF (
				p_event_type = 0,
				p_event_time,
				NULL
			) AS hired_date,

		IF (
			p_event_type = 1,
			p_event_time,
			NULL
		) AS leave_date,
		IFNULL(p_org_name, '') AS company_name,

	IF (
		p_org_id IS NULL,
		'',
		CONCAT(
			REPLACE (p_org_id, '--', '__'),
			'--__2eoK1zvtxNarHlwwQg4'
		)
	) AS company_id,
	IFNULL(p_user_id, '') AS user_id,
	IFNULL(p_user_name, '') AS user_name,
	#公司审核通过的才会设置domainid，否则设为#
IF (
	p_org_approved_status = '1',
	(
		CASE
		WHEN p_org_supervise_region_code LIKE '11%' THEN
			'xduDIOSsi06qmfpig0A'
		WHEN p_org_supervise_region_code LIKE '43%' THEN
			'__UDa4uPMdcOYgP7HETaf'
		WHEN p_org_supervise_region_code LIKE '64%' THEN
			'qS6PXrA7'
		ELSE
			'#'
		END
	),
	'#'
) AS domain_id,
 p_org_id AS item_domain_id,
 IFNULL(
	p_org_supervise_region_code,
	'#'
) AS region_id,
 CURRENT_TIMESTAMP AS last_sync_time,
 p_occupation_type AS occupation_type
		) AS t ON DUPLICATE KEY UPDATE `employee_id` = t.employee_id,
		`employed` = t.employed_status,
		`create_time` = t.created_time,
		`LASTMODIFIED` = t.LASTMODIFIED,
		`interview_notes` = t.interview_note,
		`leave_reason` = t.leave_reason,
		`hire_date` = t.hired_date,
		`leave_date` = t.leave_date,
		`company_name` = t.company_name,
		`company_id` = t.company_id,
		`user_id` = t.user_id,
		`name` = t.user_name,
		`domainid` = t.domain_id,
		item_domain_id = t.item_domain_id,
		region_id = t.region_id,
		occupation_type = t.occupation_type,
		last_sync_time = CURRENT_TIMESTAMP;


END
IF;


END
IF;


END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_employed_events_info_collect_by_employee_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employed_events_info_collect_by_employee_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_employed_events_info_collect_by_employee_v2`(IN p_employee_id VARCHAR(100),p_before_interview VARCHAR(300),p_after_interview VARCHAR(300), p_last_update_time DATETIME)
BEGIN
/*如果智能人事职员表的面谈记录发生变更，更新入离职记录里面的面谈记录
*/
 IF (p_before_interview IS NULL AND p_after_interview IS NOT NULL) OR (p_before_interview IS NOT NULL AND p_after_interview IS NULL)  OR (p_before_interview != p_after_interview) THEN
	UPDATE companyinfocollect.employed_events e
	SET e.interview_notes = p_after_interview,
	#e.LASTMODIFIED = IFNULL(p_last_update_time,CURRENT_TIMESTAMP)
	e.LASTMODIFIED = NOW()
	WHERE e.employee_id = p_employee_id;
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_employed_events_info_collect_by_organization_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employed_events_info_collect_by_organization_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_employed_events_info_collect_by_organization_v2`(IN p_org_id VARCHAR(50),p_before_org_name VARCHAR(300),p_after_org_name VARCHAR(300),p_org_last_update_time DATETIME, p_before_org_approved_status INT,p_after_org_approved_status INT, p_org_supervise_region_code VARCHAR(200))
BEGIN
#公司名称发生变更，同步入离职记录里面的公司名称。当公司的审核状态通过之后，才送入内网。否则domainid和区域编码设置为#。
IF p_before_org_name != p_after_org_name OR p_before_org_approved_status != p_after_org_approved_status THEN
	UPDATE companyinfocollect.employed_events e
	SET e.domainid = IF(p_after_org_approved_status='1',(CASE 
		WHEN p_org_supervise_region_code LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN p_org_supervise_region_code LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN p_org_supervise_region_code LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			'#'
		END),'#'),
		e.region_id = IFNULL(p_org_supervise_region_code,'#'),
		e.company_name = p_after_org_name,
		#e.LASTMODIFIED = IFNULL(p_org_last_update_time,CURRENT_TIMESTAMP)
		e.LASTMODIFIED = NOW()
	WHERE e.item_domain_id = p_org_id;
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_employee_contracts_info_collect_by_contract_id_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employee_contracts_info_collect_by_contract_id_v2`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_employee_contracts_info_collect_by_contract_id_v2`(IN p_beforeContractId VARCHAR(300),IN p_afterContractId VARCHAR(300),IN p_organization_supervise_region_code VARCHAR(300),IN p_user_id VARCHAR(300),IN p_userTelephone VARCHAR(300),IN p_supervise_depart_name VARCHAR(300),IN p_supervise_depart_id VARCHAR(300),IN p_orgTelephone VARCHAR(300),IN p_employee_id VARCHAR(300),IN p_name VARCHAR(300),IN p_b_name VARCHAR(300),IN p_a_name VARCHAR(300),IN p_begin_date VARCHAR(300),IN p_end_date VARCHAR(300),IN p_attachment VARCHAR(300),IN p_note VARCHAR(300),IN p_status VARCHAR(300),IN p_creator_created_time VARCHAR(300),IN
p_organization_id VARCHAR(300))
BEGIN
      # 归集保安员合同信息
		IF p_afterContractId IS NULL #删除条件
		THEN 
			INSERT INTO `companyinfocollect`.data_deleted_events (
			`database_name`, 
			`table_name`, 
			`primarykey_name`, 
			`primarykey_value`, 
			`DOMAINID`, 
			`LASTMODIFIED`, 
			`region_code`, 
			`created_time`, 
			`remark`
			) SELECT
			'companyinfocollect',
			'employee_contracts',
			'contract_id',
			 p_beforeContractId,
			 (CASE 
			WHEN p_organization_supervise_region_code LIKE '11%' THEN 
				'xduDIOSsi06qmfpig0A'
			WHEN p_organization_supervise_region_code LIKE '43%' THEN 
				'__UDa4uPMdcOYgP7HETaf'
			WHEN p_organization_supervise_region_code LIKE '64%' THEN 
				'qS6PXrA7'
			ELSE
				NULL
			END),
			 now(),
			 p_organization_supervise_region_code,
			 now(),
			 '';
			 
			 DELETE FROM `companyinfocollect`.`employee_contracts` WHERE contract_id = CONCAT(p_beforeContractId,'--__sRsEao1S9S3lcGTjJCY');
	   ELSE
			INSERT INTO `companyinfocollect`.`employee_contracts`(
			`contract_id`,
			`employee_id`,
			`contract_name`,
			`securityman_name`,
			`securityman_phone`,
			`party_a_name`,
			`party_a_phone`,
			`start_date`,
			`end_date`,
			`supervise_office`,
			`supervise_office_code`,
			`contract`,
			`note`,
			`contract_status`,
			`create_time`,
			`LASTMODIFIED`,
			`user_id`,
			`domainid`,
			`last_sync_time`,
			item_domain_id
			) VALUES(
			CONCAT(p_afterContractId,'--__sRsEao1S9S3lcGTjJCY'), #contract_id
			IFNULL(p_employee_id,''), #employee_id
			IFNULL(p_name,''), #contract_name
			IFNULL(p_b_name,''), #securityman_name
			IFNULL(p_userTelephone,''), #securityman_phone 保安人员联系号码 从obpm2.tenant_users表获取
			IFNULL(p_a_name,''), #party_a_name
			IFNULL(p_orgTelephone,''), #party_a_phone 甲方联系电话 从obpm2.tenant_organizations表获取
			p_begin_date, #start_date
			p_end_date, #end_date
			IFNULL(p_supervise_depart_name,''), #supervise_office 监管机构名称
			IFNULL(p_supervise_depart_id,''), #supervise_office_code 监管机构编码
			CONCAT( '[{"name":"',SUBSTRING_INDEX(IFNULL(p_attachment,''),'/',-1),'","path":"', IFNULL(p_attachment,''), '"}]'), #contract
			p_note, #note
			IFNULL(p_status,''), #contract_status
			p_creator_created_time, #create_time
			now(), #LASTMODIFIED
			IFNULL(p_user_id,''),# user_id 保安员用户id 从obpm2.tenant_users表获取
			(CASE 
			WHEN p_organization_supervise_region_code LIKE '11%' THEN 
				'xduDIOSsi06qmfpig0A'
			WHEN p_organization_supervise_region_code LIKE '43%' THEN 
				'__UDa4uPMdcOYgP7HETaf'
			WHEN p_organization_supervise_region_code LIKE '64%' THEN 
				'qS6PXrA7'
			ELSE
				'#'
			END), #domainid 根据参数p_organization_supervise_region_code 区分地区设置domainid
			now(),
		  p_organization_id)	#last_sync_time 数据变动时间
		 	#FROM obpm2.tenant_contracts otc
			#LEFT JOIN obpm2.tenant_employees ote ON otc.employee_id = ote.id #obpm2.tenant_employees的id为obpm2.tenant_contracts的employee_id（外键）
			#LEFT JOIN obpm2.tenant_users otu ON ote.tenant_user_id = otu.id #obpm2.tenant_users的id为obpm2.tenant_employees的tenant_user_id（外键）
			#LEFT JOIN baibaodunflow.tlk_setsupervise bts ON bts.DOMAINID = ote.organization_id #baibaodunflow.tlk_setsupervise的DOMAINID为obpm2.tenant_employees的organization_id
			#LEFT JOIN obpm2.tenant_organizations oto ON oto.`name` = otc.a_name #obpm2.tenant_organizations的name为obpm2.tenant_contracts的name
			#WHERE otc.id = p_afterContractId
			ON DUPLICATE KEY UPDATE
			 item_domain_id = p_organization_id,
			`employee_id` = IFNULL(p_employee_id,''),
			`contract_name` = IFNULL(p_name,''),
			`securityman_name` = IFNULL(p_b_name,''),
			`securityman_phone` = IFNULL(p_userTelephone,''),
			`party_a_name` = IFNULL(p_a_name,''),
			`party_a_phone` = IFNULL(p_orgTelephone,''),
			`start_date` = p_begin_date,
			`end_date` = p_end_date,
			`supervise_office` = IFNULL(p_supervise_depart_name,''),
			`supervise_office_code` = IFNULL(p_supervise_depart_id,''),
			`contract` = CONCAT( '[{"name":"',SUBSTRING_INDEX(IFNULL(p_attachment,''),'/',-1),'","path":"', IFNULL(p_attachment,''), '"}]'),
			`note` = p_note,
			`contract_status` = IFNULL(p_status,''),
			`create_time` = p_creator_created_time,
			`LASTMODIFIED` = now(),
			`user_id` = IFNULL(p_user_id,''),
			`last_sync_time` = now(),
			`domainid` = (CASE 
			WHEN p_organization_supervise_region_code LIKE '11%' THEN 
				'xduDIOSsi06qmfpig0A'
			WHEN p_organization_supervise_region_code LIKE '43%' THEN 
				'__UDa4uPMdcOYgP7HETaf'
			WHEN p_organization_supervise_region_code LIKE '64%' THEN 
				'qS6PXrA7'
			ELSE
				NULL
			END); 
		END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_employee_contracts_info_collect_by_organization
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employee_contracts_info_collect_by_organization`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_employee_contracts_info_collect_by_organization`(IN p_name VARCHAR(300),IN p_supervise_depart_name VARCHAR(300),IN p_before_supervise_depart_id VARCHAR(300),IN p_after_supervise_depart_id VARCHAR(300),IN p_before_orgTelephone VARCHAR(300),IN p_after_orgTelephone VARCHAR(300))
BEGIN
	#更新保安员合同中监管机构名称与ID信息
	IF (p_before_supervise_depart_id <> p_after_supervise_depart_id OR p_before_orgTelephone <> p_after_orgTelephone)
	THEN 
		UPDATE `companyinfocollect`.`employee_contracts` a
		SET a.`supervise_office` = p_supervise_depart_name,
				a.`supervise_office_code` = p_after_supervise_depart_id,
				#a.`contract_status` = CASE WHEN a.`start_date` <= NOW() AND a.`end_date` >= NOW() THEN '2' ELSE '3' END,
				a.`party_a_phone` = p_after_orgTelephone,
				a.`LASTMODIFIED` = now(),
				a.`last_sync_time` = now()
		WHERE a.`party_a_name` = p_name;
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_employee_contracts_info_collect_by_tlk_setsupervise
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employee_contracts_info_collect_by_tlk_setsupervise`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_employee_contracts_info_collect_by_tlk_setsupervise`(IN P_DOMAINID VARCHAR(300))
BEGIN
	#更新保安员合同中监管机构名称与ID信息
	UPDATE `companyinfocollect`.`employee_contracts` a,obpm2.tenant_employees te,baibaodunflow.tlk_setsupervise ts
	SET a.`supervise_office` = ts.ITEM_REGISTERPOLICEADDRESS,
			a.`supervise_office_code` = ts.ITEM_REGISTERPOLICEADDRESSID,
			a.`contract_status` = CASE WHEN a.`start_date` <= NOW() AND a.`end_date` >= NOW() THEN '2' ELSE '3' END,
			a.`LASTMODIFIED` = now(),
			a.`last_sync_time` = now()
	WHERE a.employee_id = te.id
		AND te.organization_id = ts.DOMAINID
		AND ts.DOMAINID = P_DOMAINID;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_good_deeds_detail_by_tlk_goodperson_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_good_deeds_detail_by_tlk_goodperson_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_good_deeds_detail_by_tlk_goodperson_v2`(IN p_idsStr VARCHAR(1000), p_id_before VARCHAR(300),o_ITEM_REGIONId VARCHAR(200))
BEGIN
	#好人好事归集
	IF p_idsStr is null then 
DELETE from good_deeds_detail where good_deed_id  = p_id_before;
INSERT INTO data_deleted_events(database_name,table_name,primarykey_name,primarykey_value,DOMAINID,LASTMODIFIED,region_code,created_time) VALUES('companyinfocollect','good_deeds_detail','good_deed_id',p_id_before,(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),NOW(),o_ITEM_REGIONId,NOW());
ELSE
INSERT INTO companyinfocollect.good_deeds_detail (
	good_deed_id,
	NAME,
	user_id,
	good_deeds_type,
	content,
	note,
	date,
	company_name,
	ITEM_DOMAIN_ID,
	CREATED,
	LASTMODIFIED,
	DOMAINID,
	last_sync_time
) SELECT
	IFNULL(a.id, ''),
	IFNULL(a.item_securityname, ''),
	IFNULL(c.id, ''),
	IFNULL(a.ITEM_GOODPERSONTYPE, ''),
	IFNULL(a.ITEM_GOODPERSONCONTENT, ''),
	IFNULL(a.item_remark, ''),
	IFNULL(a.ITEM_GOODPERSONDATE, ''),
	IFNULL(d.name, ''),
	IFNULL(a.DOMAINID, ''),
	IFNULL(a.CREATED, ''),
	#IFNULL(a.LASTMODIFIED, ''),
	NOW(),
	(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END) AS domain_id,
		NOW()
FROM
	baibaodunflow.tlk_goodperson a
LEFT JOIN obpm2.tenant_user_credentials b ON a.item_documentid = b.number
LEFT JOIN obpm2.tenant_users c ON b.tenant_user_id = c.id
LEFT JOIN obpm2.tenant_organizations d on a.DOMAINID = d.id
WHERE find_in_set(a.id,p_idsStr) 
	ON DUPLICATE KEY UPDATE good_deed_id = IFNULL(a.id, ''),
	NAME = IFNULL(a.item_securityname, ''),
	user_id = IFNULL(c.id, ''),
	good_deeds_type = IFNULL(a.ITEM_GOODPERSONTYPE, ''),
	content = IFNULL(a.ITEM_GOODPERSONCONTENT, ''),
	note = IFNULL(a.item_remark, ''),
	date = IFNULL(a.ITEM_GOODPERSONDATE, ''),
	ITEM_DOMAIN_ID = IFNULL(a.DOMAINID, ''),
  CREATED = IFNULL(a.CREATED, ''),
	company_name = 	IFNULL(d.name, ''),
  LASTMODIFIED = NOW(),#	IFNULL(a.LASTMODIFIED, ''),
	DOMAINID = (CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),
		last_sync_time = now();
		end if;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_punishment_detail_by_tlk_punish_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_punishment_detail_by_tlk_punish_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_punishment_detail_by_tlk_punish_v2`(IN p_idsStr VARCHAR(1000), p_id_before VARCHAR(300),o_ITEM_REGIONId VARCHAR(200))
BEGIN
	#惩罚信息归集
IF p_idsStr is null then 
DELETE from punishment_detail where punishment_id  = p_id_before;
INSERT INTO data_deleted_events(database_name,table_name,primarykey_name,primarykey_value,DOMAINID,LASTMODIFIED,region_code,created_time) VALUES('companyinfocollect','punishment_detail','punishment_id',p_id_before,(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),NOW(),o_ITEM_REGIONId,NOW());
ELSE
INSERT INTO companyinfocollect.punishment_detail (
		punishment_id,
		NAME,
		user_id,
		punishment_type,
		punishing_org,
		criminal_response,
		content,
		reason,
		note,
		date,
		company_name,
		ITEM_DOMAIN_ID,
	  CREATED,
	  LASTMODIFIED,
		DOMAINID,
		last_sync_time
	) SELECT
		IFNULL(a.id, ''),
		IFNULL(a.item_securityname, ''),
		IFNULL(c.id, ''),
		IFNULL(a.ITEM_PUNISHTYPE, ''),
		IFNULL(a.ITEM_PUNISHORG, ''),
		CASE IFNULL(a.ITEM_ISPUNISH, '')
	WHEN '否' THEN
		'0'
	WHEN '是' THEN
		'1'
	ELSE
		IFNULL(a.ITEM_ISPUNISH, '')
	END AS ITEM_ISPUNISH,
	IFNULL(a.ITEM_PUNISHCONTENT, ''),
	IFNULL(a.ITEM_PUNISHREASON, ''),
	IFNULL(a.ITEM_REMARK, ''),
	IFNULL(a.ITEM_PUNISHDATE, ''),
	IFNULL(a.ITEM_COMPANYNAME, ''),
	IFNULL(a.DOMAINID, ''),
	IFNULL(a.CREATED, ''),
	#IFNULL(a.LASTMODIFIED, ''),
	NOW(),
	(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END) AS domain_id,
		NOW()
FROM
	baibaodunflow.tlk_punish a
LEFT JOIN obpm2.tenant_user_credentials b ON a.item_documentid = b.number
LEFT JOIN obpm2.tenant_users c ON b.tenant_user_id = c.id
WHERE find_in_set(a.id, p_idsStr)
	ON DUPLICATE KEY UPDATE punishment_id = IFNULL(a.id, ''),
	NAME = IFNULL(a.item_securityname, ''),
	user_id = IFNULL(c.id, ''),
	punishment_type = IFNULL(a.ITEM_PUNISHTYPE, ''),
	punishing_org = IFNULL(a.ITEM_PUNISHORG, ''),
	criminal_response = CASE IFNULL(a.ITEM_ISPUNISH, '')
WHEN '否' THEN
	'0'
WHEN '是' THEN
	'1'
ELSE
	IFNULL(a.ITEM_ISPUNISH, '')
END,
 content = IFNULL(a.ITEM_PUNISHCONTENT, ''),
 reason = IFNULL(a.ITEM_PUNISHREASON, ''),
 note = IFNULL(a.item_remark, ''),
 date = IFNULL(a.ITEM_PUNISHDATE, ''),
 company_name = IFNULL(a.ITEM_COMPANYNAME, ''),
	ITEM_DOMAIN_ID = IFNULL(a.DOMAINID, ''),
  CREATED = IFNULL(a.CREATED, ''),
  LASTMODIFIED = NOW(),#	IFNULL(a.LASTMODIFIED, ''),
	DOMAINID = (CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),
		last_sync_time = now();
		end if;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_attendance_site_base_info_collect_by_id
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_attendance_site_base_info_collect_by_id`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_attendance_site_base_info_collect_by_id`(IN p_id VARCHAR(300),befor_constractId VARCHAR(300),after_constractId VARCHAR(300),befor_PRINCIPAL VARCHAR(300),after_PRINCIPAL VARCHAR(300),befor_userid VARCHAR(300),after_userid VARCHAR(300),user_id VARCHAR(300),supervise_region_code VARCHAR(300),supervise_depart_name VARCHAR(300),supervise_depart_id VARCHAR(300),institutional_code VARCHAR(300))
BEGIN

DECLARE personNum VARCHAR(200);
DECLARE p_USERID VARCHAR(200);#挂靠人id
DECLARE p_PRINCIPAL VARCHAR(200);#负责人id

#根据驻勤id统计驻勤点人员表中该驻勤点的人数
select count(DISTINCT ITEM_SECURITYID) from baibaodunflow.tlk_attendance_site_person_info a LEFT JOIN obpm2.tenant_employees b ON a.ITEM_securityId = b.id
  where a.ITEM_attendanceSiteId=p_id AND b.`status` = '0' into personNum ;

insert into companyinfocollect.tlk_attendance_site_base_info (
PARENT,
LASTMODIFIED,
FORMNAME,
STATE,
AUDITUSER,
AUDITDATE,
AUTHOR,
AUTHORDEPTID,
AUTHOR_DEPT_INDEX,
AUTHOR_USER_INDEX,
CREATED,
FORMID,
SUBFORMIDS,
INITIATOR,
ISTMP,
VERSIONS,
APPLICATIONID,
STATEINT,
STATELABEL,
AUDITORNAMES,
LASTFLOWOPERATION,
LASTMODIFIER,
DOMAINID,
AUDITORLIST,
COAUDITORLIST,
STATELABELINFO,
PREVAUDITNODE,
PREVAUDITUSER,
OPTIONITEM,
SIGN,
KINGGRIDSIGNATURE,
ITEM_CONTRACT_NAME,
ITEM_CONTRACT_IDS,
ITEM_CONTRACT_STATE,
ITEM_CONTRACT_START_DATE,
ITEM_CONTRACT_END_DATE,
ITEM_CONTRACT_FILE,
ITEM_COMPANY_NAME,
ITEM_DOMAIN_ID,
ITEM_ATT_SITE_NAME,
ITEM_ATT_SITE_TYPE,
ITEM_ATT_SITE_CONTENT,
ITEM_ATT_SITE_ADDRESS,
ITEM_ATT_SITE_FULL_ADDRESS,
ITEM_ATT_SITE_LONGITUDE,
ITEM_ATT_SITE_LATITUDE,
ITEM_PERFORM_RANGE,
ITEM_OFFICE_POLICE_ADDRESS,
ITEM_OFFICE_POLICE_ADDRESS_ID,
ITEM_OFFICE_POLICE_IDINDEX,
ITEM_ATT_START_DATE,
ITEM_ATT_END_DATE,
ITEM_ATT_SITE_STATE,
ITEM_PRINCIPAL_NAME,
ITEM_PRINCIPAL_CARD_TYPE,
ITEM_PRINCIPAL_IDCARD_NO,
ITEM_PRINCIPAL_PHONE_NO,
ITEM_PRINCIPAL_IS_CERTIFIED,
ITEM_VERIFICATION_STATUS,
ITEM_SERVE_OBJECT_NAME,
ITEM_SERVE_OBJECT_TYPE,
ITEM_IS_SIGNIFICANCE_UNIT,
ITEM_IS_AFFILIATION,
ITEM_REALITY_NAME,
ITEM_REALITY_CARD_NO,
ITEM_REALITY_PHONE,
ITEM_USERID,
ID,
ITEM_COMPANY_POLICE,
ITEM_ATTENDANCE_ID,
ITEM_CREATED,
ITEM_principal,
ITEM_COMPANY_POLICE_CODE,
ITEM_ATT_PERSON_NUM,
last_sync_time,
ITEM_PRINCIPAL_USER_ID,
ITEM_COMPANY_OFFICE_TYPE
)
select 
a.PARENT,
#IFNULL(a.LASTMODIFIED,NOW()),
NOW(),
a.FORMNAME,
a.STATE,
a.AUDITUSER,
a.AUDITDATE,
a.AUTHOR,
a.AUTHORDEPTID,
a.AUTHOR_DEPT_INDEX,
a.AUTHOR_USER_INDEX,
a.CREATED,
'__29MeWum6wey61qtRqF3',
a.SUBFORMIDS,
a.INITIATOR,
a.ISTMP,
a.VERSIONS,
'__DOoeSJp26wVqbyYYf50',
a.STATEINT,
a.STATELABEL,
a.AUDITORNAMES,
a.LASTFLOWOPERATION,
a.LASTMODIFIER,
(CASE 
                WHEN supervise_region_code LIKE '11%' THEN 
                        'xduDIOSsi06qmfpig0A'
                WHEN supervise_region_code LIKE '43%' THEN 
                        '__UDa4uPMdcOYgP7HETaf'
                WHEN supervise_region_code LIKE '64%' THEN 
                        'qS6PXrA7'
                ELSE
                        NULL
                END) AS item_regionid,
a.AUDITORLIST,
a.COAUDITORLIST,
a.STATELABELINFO,
a.PREVAUDITNODE,
a.PREVAUDITUSER,
a.OPTIONITEM,
a.SIGN,
a.KINGGRIDSIGNATURE,
a.ITEM_contractName,
a.ITEM_CONTRACTIDS,
a.ITEM_contractState,
a.ITEM_contractStartDate,
a.ITEM_contractEndDate,
a.ITEM_contractFile,
a.ITEM_companyName,
a.DOMAINID,
a.ITEM_attendanceSiteName,
a.ITEM_attendanceSiteType,
a.ITEM_attendanceSiteContent,
a.ITEM_attendanceSiteAddress,
a.ITEM_attendanceSiteFullAddress,
a.ITEM_attendanceSiteLongitude,
a.ITEM_attendanceSiteLatitude,
a.ITEM_performRange,
a.ITEM_officePoliceAddress,
a.ITEM_officePoliceAddressID,
a.ITEM_officePoliceIDIndex,
a.ITEM_attendanceStartDate,
a.ITEM_attendanceEndDate,
a.ITEM_attendanceSiteState,
a.ITEM_principalName,
a.ITEM_principalCardType,
-- CASE a.ITEM_principalCardType when 0 THEN '居民身份证/户口簿' when 1 then '香港特区护照/身份证' 
-- when 2 then '澳门特区护照/身份证' when 3 then '台湾居民来往大陆通行证' when 4 then '外国护照' when 5 then '退伍军人证' 
-- when 6 then '警号' when 7 then '资格证' when 8 then '等级证' when 9 then '结业证' when 10 then '持枪证' ELSE a.ITEM_principalCardType END,
a.ITEM_principalIdCardNo,
a.ITEM_principalPhoneNo,
a.ITEM_principalIsCertified,
a.ITEM_princiheyan,
a.ITEM_serveObjectName,
a.ITEM_serveObjectType,
a.ITEM_isSignificanceUnit,
a.ITEM_ISAFFILIATION,
a.ITEM_REALITYNAME,
a.ITEM_REALITYCARDNO,
a.ITEM_REALITYPHONE,
IFNULL(a.ITEM_USERID,'#'),
REPLACE(a.id,'__W052b1XNoLmPckF6ch1','__29MeWum6wey61qtRqF3') as id ,
supervise_depart_name,
a.ID as ITEM_ATTENDANCE_ID,
a.CREATED,
IFNULL(a.ITEM_principal,'#'),
supervise_depart_id,
personNum,
NOW(),
IFNULL(user_id,'#'),
institutional_code
from  baibaodunflow.tlk_attendance_site_base_info a 
-- left JOIN obpm2.tenant_organizations c on  a.ITEM_DOMAIN_ID = c.id
where a.id=p_id AND a.ITEM_attendanceSiteState != '待完善'  
ON DUPLICATE KEY UPDATE
ITEM_COMPANY_OFFICE_TYPE = institutional_code,
PARENT=a.PARENT,
LASTMODIFIED=NOW(),
FORMNAME=a.FORMNAME,
STATE=a.STATE,
AUDITUSER=a.AUDITUSER,
AUDITDATE=a.AUDITDATE,
AUTHOR=a.AUTHOR,
AUTHORDEPTID=a.AUTHORDEPTID,
AUTHOR_DEPT_INDEX=a.AUTHOR_DEPT_INDEX,
AUTHOR_USER_INDEX=a.AUTHOR_USER_INDEX,
CREATED=a.CREATED,
FORMID=a.FORMID,
SUBFORMIDS=a.SUBFORMIDS,
INITIATOR=a.INITIATOR,
ISTMP=a.ISTMP,
VERSIONS=a.VERSIONS,
APPLICATIONID=a.APPLICATIONID,
STATEINT=a.STATEINT,
STATELABEL=a.STATELABEL,
AUDITORNAMES=a.AUDITORNAMES,
LASTFLOWOPERATION=a.LASTFLOWOPERATION,
LASTMODIFIER=a.LASTMODIFIER,
DOMAINID=(CASE 
                WHEN supervise_region_code LIKE '11%' THEN 
                        'xduDIOSsi06qmfpig0A'
                WHEN supervise_region_code LIKE '43%' THEN 
                        '__UDa4uPMdcOYgP7HETaf'
                WHEN supervise_region_code LIKE '64%' THEN 
                        'qS6PXrA7'
                ELSE
                        NULL
                END),
AUDITORLIST=a.AUDITORLIST,
COAUDITORLIST=a.COAUDITORLIST,
STATELABELINFO=a.STATELABELINFO,
PREVAUDITNODE=a.PREVAUDITNODE,
PREVAUDITUSER=a.PREVAUDITUSER,
OPTIONITEM=a.OPTIONITEM,
SIGN=a.SIGN,
KINGGRIDSIGNATURE=a.KINGGRIDSIGNATURE,
ITEM_CONTRACT_NAME=(case when befor_constractId=after_constractId then ITEM_CONTRACT_NAME else a.ITEM_contractName END),
ITEM_CONTRACT_IDS= (case when befor_constractId!=after_constractId then after_constractId else a.ITEM_CONTRACTIDS END),
ITEM_CONTRACT_STATE=(case when befor_constractId=after_constractId then ITEM_CONTRACT_STATE else a.ITEM_contractState END),
ITEM_CONTRACT_START_DATE=(case when befor_constractId=after_constractId then ITEM_CONTRACT_START_DATE else a.ITEM_contractStartDate END),
ITEM_CONTRACT_END_DATE=(case when befor_constractId=after_constractId then ITEM_CONTRACT_END_DATE else a.ITEM_contractEndDate END),
ITEM_CONTRACT_FILE=(case when befor_constractId=after_constractId then ITEM_CONTRACT_FILE else a.ITEM_contractFile END),
ITEM_COMPANY_NAME=a.ITEM_companyName,
ITEM_DOMAIN_ID=a.ITEM_domain_id,
ITEM_ATT_SITE_NAME=a.ITEM_attendanceSiteName,
ITEM_ATT_SITE_TYPE=a.ITEM_attendanceSiteType,
ITEM_ATT_SITE_CONTENT=a.ITEM_attendanceSiteContent,
ITEM_ATT_SITE_ADDRESS=a.ITEM_attendanceSiteAddress,
ITEM_ATT_SITE_FULL_ADDRESS=a.ITEM_attendanceSiteFullAddress,
ITEM_ATT_SITE_LONGITUDE=a.ITEM_attendanceSiteLongitude,
ITEM_ATT_SITE_LATITUDE=a.ITEM_attendanceSiteLatitude,
ITEM_PERFORM_RANGE=a.ITEM_performRange,
ITEM_OFFICE_POLICE_ADDRESS=a.ITEM_officePoliceAddress,
ITEM_OFFICE_POLICE_ADDRESS_ID=a.ITEM_officePoliceAddressID,
ITEM_OFFICE_POLICE_IDINDEX=a.ITEM_officePoliceIDIndex,
ITEM_ATT_START_DATE= a.ITEM_attendanceStartDate,
ITEM_ATT_END_DATE= a.ITEM_attendanceEndDate,
ITEM_ATT_SITE_STATE=a.ITEM_attendanceSiteState,
ITEM_PRINCIPAL_NAME=(case when befor_PRINCIPAL=after_PRINCIPAL then ITEM_PRINCIPAL_NAME else a.ITEM_principalName END),
ITEM_PRINCIPAL_CARD_TYPE=(case when befor_PRINCIPAL=after_PRINCIPAL then ITEM_PRINCIPAL_CARD_TYPE else a.ITEM_principalCardType END),
ITEM_PRINCIPAL_IDCARD_NO=(case when befor_PRINCIPAL=after_PRINCIPAL then ITEM_PRINCIPAL_IDCARD_NO else a.ITEM_principalIdCardNo END),
ITEM_PRINCIPAL_PHONE_NO=(case when befor_PRINCIPAL=after_PRINCIPAL then ITEM_PRINCIPAL_PHONE_NO else a.ITEM_principalPhoneNo END),
ITEM_PRINCIPAL_IS_CERTIFIED=(case when befor_PRINCIPAL=after_PRINCIPAL then ITEM_PRINCIPAL_IS_CERTIFIED else a.ITEM_principalIsCertified END),
ITEM_VERIFICATION_STATUS=a.ITEM_princiheyan,
ITEM_SERVE_OBJECT_NAME=a.ITEM_serveObjectName,
ITEM_SERVE_OBJECT_TYPE=a.ITEM_serveObjectType,
ITEM_IS_SIGNIFICANCE_UNIT=a.ITEM_isSignificanceUnit,
ITEM_IS_AFFILIATION=a.ITEM_ISAFFILIATION,
ITEM_REALITY_NAME=(case when befor_userid=after_userid then ITEM_REALITY_NAME else a.ITEM_REALITYNAME END),
ITEM_REALITY_CARD_NO=(case when befor_userid=after_userid then ITEM_REALITY_CARD_NO else a.ITEM_REALITYCARDNO END),
ITEM_REALITY_PHONE=(case when befor_userid=after_userid then ITEM_REALITY_PHONE else a.ITEM_REALITYPHONE END),
ITEM_USERID=IFNULL((case when befor_userid!=after_userid then after_userid else a.ITEM_USERID END),'#'),
ITEM_COMPANY_POLICE=supervise_depart_name,
ITEM_CREATED=a.CREATED,
ITEM_principal=IFNULL((case when befor_PRINCIPAL!=after_PRINCIPAL then after_PRINCIPAL else a.ITEM_principal END),'#'),
ITEM_COMPANY_POLICE_CODE=supervise_depart_id,
ITEM_ATT_PERSON_NUM=personNum,
ITEM_PRINCIPAL_USER_ID=IFNULL(user_id,'#');
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_attendance_site
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_attendance_site`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_company_info_collect_by_attendance_site`(IN b_p_domain_id VARCHAR (300), a_p_domain_id VARCHAR (300), a_lastmodified DATETIME)
BEGIN
	/*当驻勤点表发生变更，从新计算公司的有效驻勤点数.
	有效驻勤点条件：驻勤点状态！=待完善，并且合同不为空，并且起始时间<=今天，
	并且截止时间>=今天*/
	UPDATE companyinfocollect.tlk_companyinfoquery c
	SET c.ITEM_attendance_site_nums = 
	(
	SELECT count( 1 ) 
	FROM baibaodunflow.tlk_attendance_site_base_info s 
	WHERE s.DOMAINID = IFNULL(a_p_domain_id,b_p_domain_id) 
	AND s.ITEM_attendanceSiteState != '待完善' 
	AND (s.ITEM_attendanceStartDate IS NOT NULL AND DATE_FORMAT(s.ITEM_attendanceStartDate, '%Y-%m-%d' ) <= CURRENT_DATE) 	
	AND (s.ITEM_attendanceEndDate IS NOT NULL AND DATE_FORMAT(s.ITEM_attendanceEndDate, '%Y-%m-%d' ) >= CURRENT_DATE)
	),
	#c.LASTMODIFIED =IF(ISNULL(a_lastmodified),CURRENT_TIMESTAMP,a_lastmodified), -- 如果after的lastmodified是null，则表明删除，用现在的时间戳赋值。新建和修改都以after值为准
	c.LASTMODIFIED =NOW()
	WHERE
		c.item_domain_id = IFNULL(a_p_domain_id,b_p_domain_id);

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_companies
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_companies`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_company_info_collect_by_companies`(IN 
p_id varchar(300), 
p_approved_information_last_updated_time varchar(300),
p_approved_information_note varchar(300),
p_approved_information_status varchar(300),
p_code varchar(300),
p_created_time varchar(300),
p_industry_code varchar(300),
p_institutional_code varchar(300), 
p_last_updated_time varchar(300),p_logo_path varchar(300),
p_name varchar(300),
p_place_of_business_address varchar(300),
p_place_of_business_city_code varchar(300),
p_place_of_business_city_name varchar(300),
p_place_of_business_district_code varchar(300),
p_place_of_business_district_name varchar(300),
p_place_of_business_province_code varchar(300), 
p_place_of_business_province_name varchar(300), 
p_place_of_register_address varchar(300), 
p_place_of_register_city_code varchar(300),
p_place_of_register_city_name varchar(300), 
p_place_of_register_district_code varchar(300),
p_place_of_register_district_name varchar(300),
p_place_of_register_province_code varchar(300),
p_place_of_register_province_name varchar(300),
p_tenant_user_id varchar(300),
p_description varchar(300),
p_latitude DOUBLE,
p_longitude DOUBLE,
p_sys_name varchar(300),
p_lon_lat_json varchar(300),
p_status varchar(300), 
p_fax varchar(300), 
p_telephone varchar(300), 
p_type varchar(300),
p_industry_detail_type varchar(300),
p_supervise_region_code varchar(300),
p_supervise_depart_id varchar(300),
p_supervise_depart_name varchar(300),
p_unify_social_credit_code varchar(300), 
p_unify_social_credit_code_file varchar(300), 
p_parent_name varchar(300),
p_parent_certificate_type varchar(300),
p_parent_certificate_number varchar(300),
p_parent_legal_person_name varchar(300),
p_parent_legal_person_telephone varchar(300),
p_economic_type varchar(300), 
p_nature varchar(300), 
p_service_scope varchar(300),
p_service_scope_description varchar(300),
p_registered_capital varchar(300), 
p_fixed_capital varchar(300), 
p_annual_profit_tax varchar(300), 
p_annual_sales_volume varchar(300), 
p_certificate_type varchar(300), 
p_service_permit_number varchar(300),
p_security_approval_number varchar(300),
p_name_of_issuing_authority varchar(300),
p_first_issue_service_permit varchar(300),
p_service_permit_attachment varchar(300),
p_train_content varchar(300),
p_security_preparedness_rating varchar(300),
p_security_armed_rating varchar(300),
p_security_safety_defense_rating varchar(300),
p_is_risk_assessment varchar(300),
p_number_of_security_guards varchar(300),
p_is_public_security_security varchar(300),
p_is_property_enterprise varchar(300),
p_nature_of_self_recruited_units varchar(300),
p_legal_person_name varchar(300), 
p_legal_person_telephone varchar(300), 
p_legal_person_credential_type varchar(300), 
p_legal_person_credential_number varchar(300), 
p_legal_person_nationality varchar(300), 
p_security_person_name varchar(300), 
p_security_person_telephone varchar(300),  
p_security_person_credential_type varchar(300), 
p_security_person_credential_number varchar(300))
BEGIN
 /*从智能人事的tenant_organizations 和tenant_companies归集公司信息。
 宁夏有5家考培监管机构不进行归集
 未审核通过的，以及公安单位不送入内网
 */
IF p_institutional_code NOT IN ('0107','0108','0109','0110') AND p_id NOT IN ('21DkrEV6','5TGk7EJc','TciDzLZC','NUvUuF0Y','4nmoftxx') THEN
 INSERT INTO companyinfocollect.tlk_companyinfoquery (
                FORMID,
                APPLICATIONID,
                item_domain_id,
                DOMAINID,
                item_region_id,
                ID,
                ITEM_company_name,
                ITEM_company_status,
                ITEM_company_phone,
                ITEM_company_fax,
                ITEM_economic_type,
                ITEM_company_type,
                ITEM_register_address,
                ITEM_register_addr_detail,
                ITEM_office_address,
                ITEM_office_addr_detail,
                ITEM_place_of_register_province_code,
                item_place_of_register_province_name,
                ITEM_place_of_register_city_code,
                item_place_of_register_city_name,
                ITEM_place_of_register_district_code,
                item_place_of_register_district_name,
                ITEM_place_of_business_province_code,
                item_place_of_business_province_name,
                ITEM_place_of_business_city_code,
                item_place_of_business_city_name,
                ITEM_place_of_business_district_code,
                item_place_of_business_district_name,
                ITEM_is_sub,
                ITEM_group_name,
                -- ITEM_group_id,
                ITEM_group_legal_person,
                ITEM_group_document_type,
                ITEM_group_phone,
                ITEM_group_document_no,
                ITEM_legal_person,
                ITEM_legal_person_phone,
                ITEM_document_type,
                ITEM_nationality,
                ITEM_document_id,
                ITEM_sec_manager,
                ITEM_sec_manager_phone,
                ITEM_sec_manager_doc_type,
                ITEM_sec_manager_id,
                ITEM_registered_capital,
                ITEM_fixed_assets,
                ITEM_annual_turnover,
                ITEM_profit_taxes,
                ITEM_register_doc_type,
                ITEM_register_id,
                ITEM_register_photo,
                ITEM_office_type,
                ITEM_security_type,
                ITEM_approval_no,
                ITEM_security_service_permit_no,
                ITEM_security_service_photo,
                ITEM_issuing_security_service,
                ITEM_issue_service_date,
                ITEM_business_scope,
                ITEM_business_scope_int,
                ITEM_hr_level,
                ITEM_armed_escort_level,
                ITEM_security_precautions,
                ITEM_is_risk_assessment,
                ITEM_security_train_permit_no,
                ITEM_security_train_photo,
                ITEM_issuing_train_permit,
                ITEM_issue_train_date,
                ITEM_train_scope,
                ITEM_security_nums,
                ITEM_attendance_site_type,
                ITEM_is_key_protection_units,
                ITEM_is_property_company,
                ITEM_supervise_district,
                ITEM_supervise_office,
                ITEM_supervise_office_ID,
                ITEM_entry_date,
                ITEM_in_mycompany_nums,
                ITEM_cert_securityman_nums,
                ITEM_attendance_site_nums,
                ITEM_shareholder_nums,
                ITEM_manager_nums,
                LASTMODIFIED,
                last_sync_time,
                item_latitude,
                item_longitude 
        ) 
        SELECT 
        t.FORMID,
        t.APPLICATIONID, 
        t.item_domain_id,
        t.domain_id,
        t.region_id, 
        t.ID, 
        t.company_name, 
        t.company_status,
        t.company_phone,
        t.company_fax,
        t.economic_type, 
        t.company_type, 
        t.register_address,
        t.register_addr_detail,
        t.office_address, 
        t.office_addr_detail, 
        t.place_of_register_province_code, 
        t.place_of_register_province_name,
        t.place_of_register_city_code,
        t.place_of_register_city_name,
        t.place_of_register_district_code, 
        t.place_of_register_district_name,
        t.place_of_business_province_code,
        t.place_of_business_province_name,
        t.place_of_business_city_code, 
        t.place_of_business_city_name,
        t.place_of_business_district_code,
        t.place_of_business_district_name,
        t.is_sub,
        t.group_name, 
        t.group_legal_person,         
        t.group_document_type, 
        t.group_phone,
        t.group_document_no, 
        t.legal_person, 
        t.legal_person_phone, 
        t.document_type,
        t.nationality,
        t.document_id, 
        t.sec_manager,
        t.sec_manager_phone,
        t.sec_manager_doc_type,
        t.sec_manager_id,
        t.registered_capital,
        t.fixed_assets,
        t.annual_turnover,
        t.profit_taxes,
        t.register_doc_type, 
        t.register_id,
        t.register_photo,
        t.office_type, 
        t.security_type, 
        t.approval_no, 
        t.security_service_permit_no,
        t.security_service_photo,
        t.issuing_security_service,
        t.issue_service_date,
        t.business_scope,
        t.business_scope_int, 
        t.hr_level,
        t.armed_escort_level,
        t.security_precautions,
        t.is_risk_assessment,
        t.security_train_permit_no,
        t.security_train_photo,
        t.issuing_train_permit, 
        t.issue_train_date,
        t.train_scope,
        t.security_nums,
        t.attendance_site_type,
        t.is_key_protection_units, 
        t.is_property_company, 
        t.supervise_district, 
        t.supervise_office,
        t.supervise_office_ID, 
        t.entry_date,
        t.in_mycompany_nums,
        t.cert_securityman_nums,
        t.attendance_site_nums, 
        t.shareholder_nums,
        t.manager_nums,
        t.last_modified,
        t.last_sync_time,
        t.item_latitude,
        t.item_longitude
        
        FROM (SELECT
                '__2eoK1zvtxNarHlwwQg4' AS FORMID,
                '__DOoeSJp26wVqbyYYf50' AS APPLICATIONID,
                p_id AS item_domain_id,
                IF(p_approved_information_status = 1,(CASE 
                WHEN p_supervise_region_code LIKE '11%' THEN 
                        'xduDIOSsi06qmfpig0A'
                WHEN p_supervise_region_code LIKE '43%' THEN 
                        '__UDa4uPMdcOYgP7HETaf'
                WHEN p_supervise_region_code LIKE '64%' THEN 
                        'qS6PXrA7'
                ELSE
                        '#'
                END),'#') AS domain_id, #审核通过的根据区域编码指定组织域id
                IFNULL(p_supervise_region_code,'#') AS region_id,
                CONCAT(REPLACE(p_id,'--','__'),'--__2eoK1zvtxNarHlwwQg4') AS ID, #替换数据来源的表单id为新应用的表单
                p_name AS company_name,
                p_status AS company_status,
                p_telephone AS company_phone,
                p_fax AS company_fax,
                p_economic_type AS economic_type,
                p_nature AS company_type,
                CONCAT(IFNULL(p_place_of_register_province_name,''),IFNULL(p_place_of_register_city_name,''),IFNULL(p_place_of_register_district_name,'')) AS register_address,
                p_place_of_register_address AS register_addr_detail,
                CONCAT(IFNULL(p_place_of_business_province_name,''),IFNULL(p_place_of_business_city_name,''),IFNULL(p_place_of_business_district_name,'')) AS office_address,
                p_place_of_business_address AS office_addr_detail,
                p_place_of_register_province_code AS place_of_register_province_code,
                p_place_of_register_province_name AS place_of_register_province_name,
                p_place_of_register_city_code AS place_of_register_city_code,
                p_place_of_register_city_name AS place_of_register_city_name,
                p_place_of_register_district_code AS place_of_register_district_code,
                p_place_of_register_district_name AS place_of_register_district_name,
                p_place_of_business_province_code AS place_of_business_province_code,
                p_place_of_business_province_name AS place_of_business_province_name,
                p_place_of_business_city_code AS place_of_business_city_code,
                p_place_of_business_city_name AS place_of_business_city_name,
                p_place_of_business_district_code AS place_of_business_district_code,
                p_place_of_business_district_name AS place_of_business_district_name,
                IF(p_type IN ('1','2','3'),p_type,NULL) AS is_sub,
                p_parent_name AS group_name,
                
                p_parent_legal_person_name AS group_legal_person,
                p_parent_certificate_type AS group_document_type,
                p_parent_legal_person_telephone AS group_phone,
                p_parent_certificate_number AS group_document_no,
                p_legal_person_name AS legal_person,
                p_legal_person_telephone AS legal_person_phone,
                p_legal_person_credential_type AS document_type,
                p_legal_person_nationality AS nationality,
                p_legal_person_credential_number AS document_id,
                p_security_person_name AS sec_manager,
                p_security_person_telephone AS sec_manager_phone,
                p_security_person_credential_type AS sec_manager_doc_type,
                p_security_person_credential_number AS sec_manager_id,
                IF(p_registered_capital = '', '0', p_registered_capital) AS registered_capital,
                IF(ISNULL(p_fixed_capital) OR p_fixed_capital = '', '0', p_fixed_capital) AS fixed_assets,
          IF(ISNULL(p_annual_sales_volume) OR p_annual_sales_volume = '','0', p_annual_sales_volume) AS annual_turnover,
                IF(ISNULL(p_annual_profit_tax) OR p_annual_profit_tax = '', '0', p_annual_profit_tax) AS profit_taxes,
                p_certificate_type AS register_doc_type,
                p_unify_social_credit_code AS register_id,
                concat( '[{"name":"","path":"', p_unify_social_credit_code_file, '"}]') AS register_photo,
                p_institutional_code AS office_type,
                p_industry_detail_type AS security_type,
                p_security_approval_number AS approval_no,
                IF(p_institutional_code = '0102',p_service_permit_number,NULL) AS security_service_permit_no,
                IF(p_institutional_code = '0102',concat( '[{"name":"","path":"', p_service_permit_attachment, '"}]'),NULL) AS security_service_photo,
                IF(p_institutional_code = '0102',p_name_of_issuing_authority,NULL) AS issuing_security_service,
                IF(p_institutional_code = '0102',p_first_issue_service_permit,NULL) AS issue_service_date,
                business_scope_transfer(p_service_scope_description) AS business_scope,
                p_service_scope AS business_scope_int, -- 需要确认
                IFNULL(p_security_preparedness_rating,0) AS hr_level,
                IFNULL(p_security_armed_rating,0) AS armed_escort_level,
                IFNULL(p_security_safety_defense_rating,0) AS security_precautions,
                IFNULL(p_is_risk_assessment,0) AS is_risk_assessment,
                IF(p_institutional_code = '0103',p_service_permit_number,NULL) AS security_train_permit_no,
                IF(p_institutional_code = '0103',concat( '[{"name":"","path":"', p_service_permit_attachment, '"}]'),NULL) AS security_train_photo,
                IF(p_institutional_code = '0103',p_name_of_issuing_authority,NULL) AS issuing_train_permit,
                IF(p_institutional_code = '0103',p_first_issue_service_permit,NULL) AS issue_train_date,
                p_train_content AS train_scope,
                p_number_of_security_guards AS security_nums,
                p_nature_of_self_recruited_units AS attendance_site_type,
                IFNULL(p_is_public_security_security,0) AS is_key_protection_units,
                IFNULL(p_is_property_enterprise,0) AS is_property_company,
                IFNULL((SELECT r.region_full_name FROM obpm2.sys_region r WHERE r.id = p_supervise_region_code LIMIT 1),'') AS supervise_district,
                p_supervise_depart_name AS supervise_office,
                p_supervise_depart_id AS supervise_office_ID,
                p_created_time AS entry_date,
                (
                SELECT COUNT(1) 
          FROM companyinfocollect.employees e 
          WHERE e.item_domain_id = p_id
          AND e.employed = 0 
          AND e.occupation_type = 1 #保安员数
                ) AS in_mycompany_nums, 
                (
                 SELECT COUNT(1) 
           FROM companyinfocollect.employees e 
           WHERE e.item_domain_id = p_id 
           AND e.employed = 0 
           AND e.occupation_type = 1
                 AND e.with_certificate = 1        
                        ) AS cert_securityman_nums, # 获取持证保安员数：在职+证书非逻辑删除+证书有效
                (
                        SELECT
                                count( 1 ) 
                        FROM
                                baibaodunflow.tlk_attendance_site_base_info bi 
                        WHERE
                                bi.ITEM_attendanceSiteState != '待完善' 
                                AND (bi.ITEM_attendanceStartDate IS NOT NULL AND DATE_FORMAT(bi.ITEM_attendanceStartDate, '%Y-%m-%d' ) <= CURRENT_DATE)         
        AND (bi.ITEM_attendanceEndDate IS NOT NULL AND DATE_FORMAT(bi.ITEM_attendanceEndDate, '%Y-%m-%d' ) >= CURRENT_DATE)  
                                AND bi.DOMAINID = p_id 
                        ) AS attendance_site_nums, # 有效驻勤点数：已完善+有合同+时间有效
                (
                        SELECT
                                COUNT(1)
                        FROM baibaodunflow.tlk_shareholderinformation si
                        WHERE si.DOMAINID = p_id
                        AND si.ITEM_IS_DELETE = '0'
                ) AS shareholder_nums, #股东数
                ( 
                        SELECT 
                                count( 1 ) 
                        FROM 
                                baibaodunflow.tlk_managementlayer tm 
                        WHERE 
                                tm.DOMAINID = p_id 
                                AND tm.ITEM_IS_DELETE = '0'
                        ) AS manager_nums, #管理层数
                #IFNULL(c.last_updated_time,CURRENT_TIMESTAMP) AS last_modified,
                NOW() AS last_modified,
                CURRENT_TIMESTAMP AS last_sync_time,
                p_latitude AS item_latitude,
                p_longitude AS item_longitude

        
                /**JOIN obpm2.tenant_employees e 
                ON e.organization_id = o.id */
                
        ) AS t
        
                
         ON DUPLICATE KEY UPDATE
         
                ITEM_economic_type = t.economic_type,
                ITEM_company_type = t.company_type, 
                
                ITEM_group_name = t.group_name,
                -- ITEM_group_id,
                ITEM_group_legal_person = t.group_legal_person,
                ITEM_group_document_type = t.group_document_type,
                ITEM_group_phone =  t.group_phone,
                ITEM_group_document_no = t.group_document_no,
                ITEM_legal_person = t.legal_person, 
                ITEM_legal_person_phone = t.legal_person_phone,
                ITEM_document_type = t.document_type,
                ITEM_nationality =  t.nationality,
                ITEM_document_id = t.document_id,
                ITEM_sec_manager = t.sec_manager,
                ITEM_sec_manager_phone = t.sec_manager_phone,
                ITEM_sec_manager_doc_type = t.sec_manager_doc_type,
                ITEM_sec_manager_id = t.sec_manager_id,
                ITEM_registered_capital = t.registered_capital,
                ITEM_fixed_assets = t.fixed_assets,
                ITEM_annual_turnover = t.annual_turnover,
                ITEM_profit_taxes = t.profit_taxes,
                ITEM_register_doc_type = t.register_doc_type,
                ITEM_register_id = t.register_id,
                ITEM_register_photo = t.register_photo,

                ITEM_approval_no = t.approval_no,
                ITEM_security_service_permit_no = t.security_service_permit_no,
                ITEM_security_service_photo = t.security_service_photo,
                ITEM_issuing_security_service = t.issuing_security_service,
                ITEM_issue_service_date = t.issue_service_date,
                ITEM_business_scope = t.business_scope,
                ITEM_business_scope_int = t.business_scope_int, 
                ITEM_hr_level = t.hr_level,
                ITEM_armed_escort_level = t.armed_escort_level,
                ITEM_security_precautions = t.security_precautions,
                ITEM_is_risk_assessment = t.is_risk_assessment,
                ITEM_security_train_permit_no = t.security_train_permit_no,
                ITEM_security_train_photo =  t.security_train_photo,
                ITEM_issuing_train_permit = t.issuing_train_permit,
                ITEM_issue_train_date = t.issue_train_date,
                ITEM_train_scope =  t.train_scope,
                ITEM_security_nums = t.security_nums,
                ITEM_attendance_site_type = t.attendance_site_type,
                ITEM_is_key_protection_units = t.is_key_protection_units,
                ITEM_is_property_company = t.is_property_company,
                LASTMODIFIED  = t.last_modified;
 
 END IF;
 


END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_employees_v3
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_employees_v3`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_company_info_collect_by_employees_v3`(IN p_item_domain_id VARCHAR(300),p_before_employed INT, p_after_employed INT, p_before_occupation_type INT, p_after_occupation_type INT, p_before_certificate_no VARCHAR(200), p_after_certificate_no VARCHAR(200))
BEGIN
	/**监听归集后的职员表，计算在职保安员书和持证保安员数。之所以改成监听归集后的职员表，为了保证在归集应用里面，保安员数保持一致*/
	#当在职状态，职业类型以及资格证号发生变更时
	IF (p_before_employed IS NULL OR p_after_employed IS NULL OR p_before_employed != p_after_employed) OR
			(p_before_occupation_type != p_after_occupation_type) OR
			((p_before_certificate_no IS NULL AND p_after_certificate_no IS NOT NULL ) OR (p_before_certificate_no IS NOT NULL AND p_after_certificate_no IS NULL ) OR p_before_certificate_no != p_after_certificate_no) THEN
			
	UPDATE companyinfocollect.tlk_companyinfoquery c, 
	(SELECT 
   IFNULL(SUM(CASE WHEN e.with_certificate=1 THEN 1 ELSE 0 END),0) AS cert_securityman_num,
	 IFNULL(SUM(1),0) as in_company_num
 FROM companyinfocollect.employees e WHERE e.item_domain_id = p_item_domain_id 
	 AND e.employed = 0 
	 AND e.occupation_type = 1) as t 
	-- 在职保安员数
	SET c.ITEM_in_mycompany_nums = t.in_company_num,
	/* 持证保安员数：在职 + 保安员 + 持证*/
	c.ITEM_cert_securityman_nums = t.cert_securityman_num,
	#LASTMODIFIED = 	IFNULL(p_last_update_time,CURRENT_TIMESTAMP)
	c.LASTMODIFIED = 	NOW()
	WHERE c.item_domain_id = p_item_domain_id; 
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_management
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_management`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_company_info_collect_by_management`(IN b_p_domain_id VARCHAR(300), a_p_domain_id VARCHAR(300), a_lastmodified DATETIME)
BEGIN
# 管理层信息发生变更，更新管理层数。
	UPDATE companyinfocollect.tlk_companyinfoquery 
	SET ITEM_manager_nums  = 
	(
	SELECT count(1) 
	FROM baibaodunflow.tlk_managementlayer m 
	WHERE m.DOMAINID = IFNULL(b_p_domain_id, a_p_domain_id) 
	AND m.ITEM_IS_DELETE = '0'
	),
	#LASTMODIFIED = IFNULL(a_lastmodified,CURRENT_TIMESTAMP),
	LASTMODIFIED = NOW() 
	WHERE
	item_domain_id = IFNULL(b_p_domain_id, a_p_domain_id);

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_organization_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_organization_v2`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_company_info_collect_by_organization_v2`(IN p_org_id VARCHAR (50),p_org_name VARCHAR(300),p_org_status INT,p_org_phone VARCHAR(20),p_org_fax VARCHAR(20),p_org_place_of_register_province_name VARCHAR(300),p_org_place_of_register_city_name VARCHAR(300),p_org_place_of_register_district_name VARCHAR(300),p_org_place_of_register_address VARCHAR(300),p_org_place_of_business_province_name VARCHAR(300),p_org_place_of_business_city_name VARCHAR(300),p_org_place_of_business_district_name VARCHAR(300),p_org_place_of_business_address VARCHAR (300), p_org_place_of_register_province_code VARCHAR(300),p_org_place_of_register_city_code VARCHAR(300),p_org_place_of_register_district_code VARCHAR(300),p_org_place_of_business_province_code VARCHAR(300),p_org_place_of_business_city_code VARCHAR(300),p_org_place_of_business_district_code VARCHAR(300),p_org_type INT,p_org_institutional_code VARCHAR(300),p_org_industry_detail_type INT,p_org_supervise_region_code VARCHAR(200),p_org_supervise_department_name VARCHAR(200),p_org_supervise_department_id VARCHAR(200),p_org_create_time DATETIME,p_org_last_update_time DATETIME, p_org_approved_status INT, p_latitude DOUBLE, p_longitude DOUBLE)
BEGIN
/*组织表信息发生变更，信息同步到行业信息从业单位*/
        update companyinfocollect.tlk_companyinfoquery c
         SET
         c.DOMAINID = 
         IF(p_org_approved_status = 1,(CASE 
                WHEN p_org_supervise_region_code LIKE '11%' THEN 
                        'xduDIOSsi06qmfpig0A'
                WHEN p_org_supervise_region_code LIKE '43%' THEN 
                        '__UDa4uPMdcOYgP7HETaf'
                WHEN p_org_supervise_region_code LIKE '64%' THEN 
                        'qS6PXrA7'
                ELSE
                        '#'
                END),'#'),
         c.item_domain_id = p_org_id,
         c.ITEM_company_name = p_org_name,
         c.ITEM_company_status =p_org_status,
         c.ITEM_company_phone = p_org_phone,
         c.ITEM_company_fax = p_org_fax,
         c.ITEM_register_address = CONCAT(IFNULL(p_org_place_of_register_province_name,''),IFNULL(p_org_place_of_register_city_name,''),IFNULL(p_org_place_of_register_district_name,'')),
         c.ITEM_register_addr_detail = p_org_place_of_register_address,
         c.ITEM_office_address = CONCAT(IFNULL(p_org_place_of_business_province_name,''),IFNULL(p_org_place_of_business_city_name,''),IFNULL(p_org_place_of_business_district_name,'')),
         c.ITEM_office_addr_detail = p_org_place_of_business_address,
         c.ITEM_place_of_register_province_code = p_org_place_of_register_province_code,
         c.item_place_of_register_province_name = p_org_place_of_register_province_name,
         c.ITEM_place_of_register_city_code = p_org_place_of_register_city_code,
         c.item_place_of_register_city_name = p_org_place_of_register_city_name,
         c.ITEM_place_of_register_district_code = p_org_place_of_register_district_code,
         c.item_place_of_register_district_name = p_org_place_of_register_district_name,
         c.ITEM_place_of_business_province_code = p_org_place_of_business_province_code,
         c.item_place_of_business_province_name = p_org_place_of_business_province_name,
         c.ITEM_place_of_business_city_code = p_org_place_of_business_city_code,
         c.item_place_of_business_city_name = p_org_place_of_business_city_name,
         c.ITEM_place_of_business_district_code = p_org_place_of_business_district_code,
         c.item_place_of_business_district_name = p_org_place_of_business_district_name,
         c.ITEM_is_sub = IF(p_org_type IN ('1','2','3'),p_org_type,NULL),
         
         c.ITEM_office_type = p_org_institutional_code, -- 需要确认
         c.ITEM_security_type = p_org_industry_detail_type,
                c.item_region_id = IFNULL(p_org_supervise_region_code,'#'), 
                c.ITEM_supervise_district = IFNULL((SELECT r.region_full_name FROM obpm2.sys_region r WHERE r.id = p_org_supervise_region_code LIMIT 1),''),
                c.ITEM_supervise_office = p_org_supervise_department_name,
                c.ITEM_supervise_office_ID = p_org_supervise_department_id,
                c.ITEM_entry_date = p_org_create_time,
                #c.LASTMODIFIED = IFNULL(p_org_last_update_time,CURRENT_TIMESTAMP)
                c.LASTMODIFIED = NOW(),
                c.item_latitude = p_latitude,
                c.item_longitude = p_longitude
        
        WHERE c.item_domain_id = p_org_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_shareholder
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_shareholder`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_company_info_collect_by_shareholder`(IN b_p_domain_id VARCHAR(300),a_p_domain_id VARCHAR(300), a_lastmodified DATETIME)
BEGIN
/*股东信息发生变更，重新计算股东数*/
	UPDATE companyinfocollect.tlk_companyinfoquery c
	SET c.ITEM_shareholder_nums  = 
	(
	SELECT count(1) 
	FROM baibaodunflow.tlk_shareholderinformation s 
	WHERE s.DOMAINID = IFNULL(b_p_domain_id,a_p_domain_id) 
	AND s.ITEM_IS_DELETE = '0'
	),
	#c.LASTMODIFIED = IFNULL(a_lastmodified,CURRENT_TIMESTAMP),
	c.LASTMODIFIED = NOW()
	WHERE
	c.item_domain_id = IFNULL(b_p_domain_id,a_p_domain_id);
	
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_tenant_users
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_tenant_users`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_company_info_collect_by_tenant_users`(IN p_tenant_user_id VARCHAR (100), p_before_checked_status INT, p_after_checked_status INT, p_last_update_time DATETIME)
BEGIN
	#如果核验状态变为核验通过，更新企业持证保安员数和在职保安员数
	DECLARE p_org_id VARCHAR(100) DEFAULT NULL;
	IF p_before_checked_status != 1 AND p_after_checked_status = 1 THEN
	#查找这个人以保安员身份所入职公司的名称
		SELECT e.organization_id INTO p_org_id
		FROM obpm2.tenant_employees e 
		WHERE e.occupation_type = 1
		AND e.status = 0
		AND e.tenant_user_id = p_tenant_user_id LIMIT 1;
		
		IF p_org_id IS NOT NULL THEN
		UPDATE companyinfocollect.tlk_companyinfoquery 
	/* 持证保安员数：在职 + 保安员 + 证书类型为资格证 + 非逻辑删除 + 证书有效*/
	SET ITEM_cert_securityman_nums = 
	(
		SELECT
				count( DISTINCT te.id ) 
			FROM
				obpm2.tenant_employees te
			JOIN obpm2.tenant_users tu 
			ON te.tenant_user_id = tu.id
			JOIN obpm2.tenant_user_credentials tuc 
			ON tu.id = tuc.tenant_user_id 
			JOIN obpm2.tenant_user_credential_details tucd ON tuc.id = tucd.id
			WHERE te.organization_id = p_org_id
			AND te.`status` = 0 
			AND te.occupation_type = 1
			AND tu.checked_status = 1
			AND tuc.credential_type = 7 
			AND tucd.state = '1'
			AND tucd.active = 1
	),
	#LASTMODIFIED = 	IFNULL(p_last_update_time,CURRENT_TIMESTAMP)
	LASTMODIFIED = 	NOW()
	WHERE item_domain_id = p_org_id;
	END IF;
	END IF;

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_tenant_user_credential
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_tenant_user_credential`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_company_info_collect_by_tenant_user_credential`(IN  p_tenant_user_id VARCHAR(300), p_credential_type INT, a_created_time DATETIME)
BEGIN

/*证件表发生变更，更新持证保安员数。目前没有证件吊销/撤销应用。后续如果增加这个功能，需要做对应的调整。需要改为监听证件详情表，监听证件状态*/
	DECLARE p_cert_securityman_nums INT;
	DECLARE p_org_id VARCHAR(50) DEFAULT NULL;

-- 更新持证保安员数
	-- IF p_credential_type = 7 THEN	
	#获取组织id
		SELECT e.organization_id INTO  p_org_id
		FROM obpm2.tenant_employees e 
		WHERE e.tenant_user_id = p_tenant_user_id
		AND e.`status` = 0 
	  AND e.occupation_type = 1 LIMIT 1;
		
		IF p_org_id IS NOT NULL THEN #计算持证保安员数
		SELECT
				count( DISTINCT te.id ) INTO p_cert_securityman_nums
			FROM
				obpm2.tenant_employees te
			JOIN obpm2.tenant_users tu 
			ON te.tenant_user_id = tu.id
			JOIN obpm2.tenant_user_credentials tuc 
			ON tu.id = tuc.tenant_user_id 
			JOIN obpm2.tenant_user_credential_details tucd ON tuc.id = tucd.id
			WHERE te.organization_id = p_org_id
			AND te.`status` = 0 
			AND te.occupation_type = 1
			AND tu.checked_status = 1
			AND tuc.credential_type = 7 
			AND tucd.state = '1'
			AND tucd.active = 1;
		
		-- 更新持证保安员人数
		UPDATE companyinfocollect.tlk_companyinfoquery 
		SET ITEM_cert_securityman_nums = p_cert_securityman_nums,
		#LASTMODIFIED = IFNULL(a_created_time,CURRENT_TIMESTAMP),
		LASTMODIFIED = NOW()
		WHERE item_domain_id = p_org_id;
		END IF;
	-- END IF;
	
	-- 更新法人或保安负责人信息
-- call proc_sync_rd_company_info_collect_by_tenant_user(p_tenant_user_id);


END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_device_info_collect_by_company_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_device_info_collect_by_company_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_device_info_collect_by_company_v2`(IN p_company_id VARCHAR (300),o_name VARCHAR (300),o_company_police VARCHAR(200),o_company_police_code VARCHAR(200))
BEGIN
	#当公司的名称和监管地区和编码发生变化，触发更新
update companyinfocollect.tlk_device c
set c.ITEM_domain_name = o_name,c.ITEM_company_police = o_company_police, c.ITEM_company_police_code = o_company_police_code,c.LASTMODIFIED =NOW()
where c.ITEM_domain_id = p_company_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_device_info_collect_by_device_v3
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_device_info_collect_by_device_v3`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_device_info_collect_by_device_v3`(IN p_id VARCHAR(300),p_id_before VARCHAR(300),o_Name VARCHAR(200),o_company_police VARCHAR(200),o_company_police_code VARCHAR(200),o_ITEM_REGIONId VARCHAR(200))
BEGIN
	#设备信息归集，当原表删除数据时，需在归集后的表执行删除语句，并在data_deleted_events插入删除记录
IF p_id is null then 
DELETE from tlk_device where ITEM_RECORD_ID  = p_id_before;
INSERT INTO data_deleted_events(database_name,table_name,primarykey_name,primarykey_value,DOMAINID,LASTMODIFIED,region_code,created_time) VALUES('companyinfocollect','tlk_device','ITEM_RECORD_ID',p_id_before,(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),NOW(),o_ITEM_REGIONId,NOW());
ELSE 
INSERT INTO companyinfocollect.tlk_device(
    ITEM_domain_id,
		ITEM_domain_name,
		ITEM_record_id,
		ITEM_equip_type,
		ITEM_equip_no,
		ITEM_equip_name,
		ITEM_equip_total,
		ITEM_remain_quality,
		ITEM_first_storage_time,
		ITEM_company_police,
		ITEM_company_police_code,
		LASTMODIFIED,
	  CREATED,
		ID,
		DOMAINID,
		last_sync_time
)SELECT 
	c.DOMAINID,
	o_Name,
	c.ID,
	c.ITEM_EQUIPTYPE,
	c.ITEM_EQUIPNO,
	c.ITEM_EQUIPNAME,
	c.ITEM_EQUIPTOTAL,
	c.ITEM_REMAINQUALITY,
	c.ITEM_FIRSTSTORAGETIME,
	o_company_police,
	o_company_police_code,
	#c.LASTMODIFIED,
	NOW(),
	c.CREATED,
	CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__0Ow94lY3ya8iaE6wiki'),
	(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END) AS domain_id,
		NOW()
 FROM baibaodunflow.tlk_jfDevice_2 c 
 WHERE c.ID = p_id
  ON DUPLICATE KEY UPDATE  
    ITEM_domain_id = c.DOMAINID,
		ITEM_domain_name = o_Name,
		ITEM_record_id = c.ID,
		ITEM_equip_type = c.ITEM_EQUIPTYPE,
		ITEM_equip_no = c.ITEM_EQUIPNO,
		ITEM_equip_name = c.ITEM_EQUIPNAME,
		ITEM_equip_total = c.ITEM_EQUIPTOTAL,
		ITEM_remain_quality = c.ITEM_REMAINQUALITY,
		ITEM_first_storage_time = c.ITEM_FIRSTSTORAGETIME,
		ITEM_company_police = o_company_police,
		ITEM_company_police_code = o_company_police_code,
		LASTMODIFIED = NOW(),#c.LASTMODIFIED,
	  CREATED = c.CREATED,
		ID = CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__0Ow94lY3ya8iaE6wiki'),
		DOMAINID = (CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),
		last_sync_time = now();
		end if;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_attendance_site_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_attendance_site_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_attendance_site_v2`(IN p_att_site_id VARCHAR(300), p_att_site_name VARCHAR(300),p_att_site_super_office VARCHAR(200), p_att_site_super_office_id VARCHAR(200),p_last_modified DATETIME)
BEGIN
/*驻勤点名称和监管机构变更，更新保安员关联的驻勤点的信息。删除驻勤点不作变更（删除驻勤点前需要移除驻勤人员）。*/
IF p_att_site_id IS NOT NULL THEN
	UPDATE companyinfocollect.employees e 
	SET e.att_site_name = p_att_site_name,
	e.att_site_supervise_office = p_att_site_super_office,
	e.att_site_supervise_office_code = p_att_site_super_office_id,
	e.in_att_site = 1,
	#e.LASTMODIFIED = IFNULL(p_last_modified,CURRENT_TIMESTAMP),
	e.LASTMODIFIED = CURRENT_TIMESTAMP
	WHERE e.att_site_id = p_att_site_id ;
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_att_site_person
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_att_site_person`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_att_site_person`(IN b_security_id VARCHAR(300), a_security_id VARCHAR(300))
BEGIN

/*当人员关联驻勤点或者从驻勤点中移除，更新保安员的关联驻勤点的信息。取消驻勤点关联则驻勤点信息设置为空。逻辑上一个人不会同时在2个驻勤点中*/
	DECLARE p_att_site_name VARCHAR(200) DEFAULT NULL;
	DECLARE p_att_site_id VARCHAR(100) DEFAULT NULL;
	DECLARE p_att_site_supervise_office VARCHAR(200) DEFAULT NULL;
	DECLARE p_att_site_supervise_office_code VARCHAR(50) DEFAULT NULL; 
	DECLARE p_update_time TIMESTAMP DEFAULT NULL;

  -- 驻勤点解除人员目前是删除了数据
	IF a_security_id IS NULL OR a_security_id = '' THEN
		UPDATE companyinfocollect.employees 
	 SET att_site_name = NULL,att_site_id = NULL,att_site_supervise_office = NULL, att_site_supervise_office_code = NULL, LASTMODIFIED = CURRENT_TIMESTAMP, in_att_site = 0, last_sync_time = CURRENT_TIMESTAMP
	 WHERE employee_id = b_security_id;
	 
 ELSE -- 新增或者修改驻勤人员
		SELECT bi.ITEM_attendanceSiteName, bi.ID, bi.ITEM_officePoliceAddress,bi.ITEM_officePoliceAddressID, pi.LASTMODIFIED INTO p_att_site_name, p_att_site_id,p_att_site_supervise_office,p_att_site_supervise_office_code,p_update_time
	 FROM baibaodunflow.tlk_attendance_site_person_info pi 
	 JOIN baibaodunflow.tlk_attendance_site_base_info bi ON pi.ITEM_attendanceSiteId = bi.ID
	 WHERE pi.ITEM_securityId = a_security_id LIMIT 1;
	 
	 UPDATE companyinfocollect.employees 
	 SET att_site_name = p_att_site_name,att_site_id = p_att_site_id,att_site_supervise_office = p_att_site_supervise_office, att_site_supervise_office_code = p_att_site_supervise_office_code, 
	 #LASTMODIFIED = IFNULL(p_update_time,CURRENT_TIMESTAMP), 
	 LASTMODIFIED = CURRENT_TIMESTAMP,
	 in_att_site = IF(ISNULL(p_att_site_id),0,1), last_sync_time = CURRENT_TIMESTAMP
	 WHERE employee_id = a_security_id;
	
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_department_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_department_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_department_v2`(IN p_department_id VARCHAR(300),p_department_name VARCHAR(300),p_lastmodified VARCHAR(300))
BEGIN

	-- 部门名称变更,更新一般职员所在的部门名称
	UPDATE companyinfocollect.employees e 
	SET e.department = p_department_name,
	#e.LASTMODIFIED = IFNULL(p_lastmodified,CURRENT_TIMESTAMP),
	e.LASTMODIFIED = CURRENT_TIMESTAMP
	WHERE e.department_id = p_department_id;
	
	
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_employee_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_employee_v2`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_employee_v2`(IN p_employee_id VARCHAR(300), p_tenant_user_id VARCHAR(300),p_employee_before_occupation_type INT,p_employee_occupation_type INT, p_employee_hired_date DATETIME,p_employee_leave_time DATETIME, p_employee_before_status INT,p_employee_after_status INT,p_employee_insure INT,p_created_time DATETIME,p_employee_last_update_time DATETIME, p_employee_department_id VARCHAR(300),p_employee_department_name VARCHAR(200),
p_org_id VARCHAR (200), p_org_supervise_region_code VARCHAR(200), p_org_name VARCHAR(200), p_org_institutional_code VARCHAR(200), p_org_supervise_depart_name VARCHAR(200),p_org_supervise_depart_id VARCHAR(200),p_org_approved_status VARCHAR(200),p_user_checked_status VARCHAR(200),
p_att_site_id VARCHAR(300),p_att_site_name VARCHAR(300),p_att_site_supervise_office VARCHAR(300), p_att_site_supervise_office_code VARCHAR(300),
p_certNo VARCHAR(300), p_securityGrade VARCHAR(300),
p_user_name VARCHAR(200),p_user_phone VARCHAR(100),p_user_sex VARCHAR(10),p_user_nation VARCHAR(100),p_user_education VARCHAR(100),p_user_birthdate VARCHAR(100),p_user_political_status VARCHAR(100),p_user_military_status VARCHAR(100),p_user_auth_status VARCHAR(100),p_auth_time VARCHAR(100),p_auth_result VARCHAR(300),p_background_screening_status VARCHAR(100),p_user_householder_type VARCHAR(100),p_user_stature VARCHAR(100),p_user_native_place VARCHAR(300),p_user_maritial_status VARCHAR(100),p_user_emgergency_contact VARCHAR(200),p_user_emgergency_phone VARCHAR(100),p_user_license_level VARCHAR(100),p_user_work_year VARCHAR(100),p_user_disease_history VARCHAR(300),p_user_now_address VARCHAR(300),p_user_selected_credential_id VARCHAR(300),
p_employee_before_hired_operator_time DATETIME, p_employee_after_hired_operator_time DATETIME, p_after_salary VARCHAR(100))
BEGIN
DECLARE data_cnt INT DEFAULT 0; #查询该人员的记录个数
DECLARE original_employee_id VARCHAR(100) DEFAULT NULL; #原数据的职员id
DECLARE original_hired_date TIMESTAMP DEFAULT NULL; #原数据的入职时间
DECLARE original_leave_date TIMESTAMP DEFAULT NULL; #原数据的离职时间
DECLARE original_status INT DEFAULT NULL; #原数据的在职状态。0在职，1离职
DECLARE region_domainid VARCHAR(300) DEFAULT NULL; #监管地区domainid,不能通过区域编码，防止区域编码不规范

#宁夏有5家考培监管机构不进行归集
IF p_org_institutional_code NOT IN ('0107','0108','0109') AND p_org_id NOT IN ('21DkrEV6','5TGk7EJc','TciDzLZC','NUvUuF0Y','4nmoftxx') THEN
/*如果就职状态发生变化:新增，或者入离职状态修改.这里添加了入职操作时间，为的是解决归集出现问题，没有新增成功的情况，通过修改入职操作时间来重新触发归集的新增操作*/
IF (p_employee_before_status IS NULL AND p_employee_after_status IS NOT NULL) OR (p_employee_before_status != p_employee_after_status) OR (p_employee_before_hired_operator_time IS NULL AND p_employee_after_hired_operator_time IS NOT NULL) OR (p_employee_before_hired_operator_time != p_employee_after_hired_operator_time) OR (p_employee_before_occupation_type != p_employee_occupation_type) THEN
	
	
SET region_domainid = IF(p_org_approved_status='1',(CASE 
							WHEN p_org_supervise_region_code LIKE '11%' THEN 
								'xduDIOSsi06qmfpig0A'
							WHEN p_org_supervise_region_code LIKE '43%' THEN 
								'__UDa4uPMdcOYgP7HETaf'
							WHEN p_org_supervise_region_code LIKE '64%' THEN 
								'qS6PXrA7'
							ELSE
								'#'
							END),'#'); 

/*
1.保安员：保安员记录只有一条最近的就职记录
 (1)入职：从现有表里面查找该人员的，且为保安员的记录
		如果没有记录（入职新公司 + 新导入的老数据），插入新纪录。
		如果有记录，比较employee-id是否相同。如果一样（复职），则更新。
		如果不一样（入职新公司 +新导入的老数据）则删除原纪录，插入新纪录。
（2）离职：从现在的表中查找该人员的且为保安员的记录
		新导入的老数据：
			如果没有记录，则增加一条记录；
			如果有记录（employee-id肯定不一样），且入职时间比现在要早的
			（如果原来在职必然入职时间比当前要晚，忽略。这里是原数据是离职的情况），
			则删除原纪录，增加新记录。否则忽略；
			(同一公司离职比入职晚到来，但是可能第一家的离职比第二家的晚到)
	  新数据：
			有记录，且employee-id是一样的（离职），更新数据；
		
*/
	IF p_employee_occupation_type = 1 THEN
		#当前人员就离职数据条数
	SELECT count(1), e.employee_id, e.hire_date,e.leave_date,e.employed INTO data_cnt,original_employee_id,original_hired_date,original_leave_date,original_status
		FROM companyinfocollect.employees e
		WHERE e.user_id = p_tenant_user_id AND e.occupation_type = 1 AND 
		e.DOMAINID = region_domainid LIMIT 1;
		
		
		IF data_cnt = 0 THEN #没有记录，则插入新数据
			CALL proc_sync_rd_employee_info_collect_insert_update_sub_procedure(p_employee_id, p_tenant_user_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_created_time,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_id, p_org_supervise_region_code, p_org_name, p_org_institutional_code , p_org_supervise_depart_name,p_org_supervise_depart_id,p_org_approved_status,p_user_checked_status,p_att_site_id,p_att_site_name,p_att_site_supervise_office, p_att_site_supervise_office_code,p_certNo, p_securityGrade,p_user_name,p_user_phone,p_user_sex,p_user_nation,p_user_education,p_user_birthdate,p_user_political_status,p_user_military_status,p_user_auth_status,p_auth_time,p_auth_result,p_background_screening_status,p_user_householder_type,p_user_stature,p_user_native_place,p_user_maritial_status,p_user_emgergency_contact,p_user_emgergency_phone,p_user_license_level,p_user_work_year,p_user_disease_history,p_user_now_address,p_user_selected_credential_id,p_after_salary);
		#保安员的话，有记录只有一条
		ELSEIF original_employee_id = p_employee_id THEN #有记录，且职员id一致，更新(新数据才有)
			CALL proc_sync_rd_employee_info_collect_update_sub_procedure(p_employee_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_supervise_region_code,p_org_approved_status,p_after_salary);

		ELSEIF (p_employee_after_status = 0 AND original_status = 0 AND original_hired_date <= p_employee_hired_date) OR (p_employee_after_status = 0 AND original_status = 1) OR (p_employee_after_status = 1 AND original_status = 1 AND (original_hired_date < p_employee_hired_date OR (original_hired_date = p_employee_hired_date AND original_leave_date < p_employee_leave_time))) THEN #入职或者新导入的老数据时，离职记录，取最新一条。（删除原数据，插入新数据）
		#如果原来是保安员，且现在是新入职的，删除原来的。否则保留原来的。如果原来是离职的，现在是在职的，直接替换。如果原来离职，现在也是离职，则比较入职时间。如果原来在职，现在离职则不管。
		
		DELETE FROM companyinfocollect.employees WHERE employee_id = original_employee_id;
		
		#审核通过的组织的人员才会送入内网，对于未审核的组织，不会送入内网，所以也无需进行数据删除。
		IF p_org_approved_status='1' THEN
		  CALL proc_sync_rd_employee_info_delete_employee(original_employee_id,p_org_supervise_region_code,original_hired_date);
		END IF;
		
		CALL proc_sync_rd_employee_info_collect_insert_update_sub_procedure(p_employee_id, p_tenant_user_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_created_time,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_id, p_org_supervise_region_code, p_org_name, p_org_institutional_code , p_org_supervise_depart_name,p_org_supervise_depart_id,p_org_approved_status,p_user_checked_status,p_att_site_id,p_att_site_name,p_att_site_supervise_office, p_att_site_supervise_office_code,p_certNo, p_securityGrade,p_user_name,p_user_phone,p_user_sex,p_user_nation,p_user_education,p_user_birthdate,p_user_political_status,p_user_military_status,p_user_auth_status,p_auth_time,p_auth_result,p_background_screening_status,p_user_householder_type,p_user_stature,p_user_native_place,p_user_maritial_status,p_user_emgergency_contact,p_user_emgergency_phone,p_user_license_level,p_user_work_year,p_user_disease_history,p_user_now_address,p_user_selected_credential_id,p_after_salary);
		
		ELSEIF p_employee_before_occupation_type != p_employee_occupation_type THEN
		/**如果是从一般职员转成保安员的话，如果当前这条在职，原来也在职，但是入职时间比现在这条要晚；或者当前这条离职，原来在职；或者当前这条离职，原来也是离职但是入职时间比原来的要早：
		（1）需要在归集后的表中把当前这条记录删除掉，可以直接删；
		*/
		
		DELETE FROM companyinfocollect.employees WHERE employee_id = p_employee_id;
			#审核通过的组织的人员才会送入内网，对于未审核的组织，不会送入内网，所以也无需进行数据删除。
		IF p_org_approved_status='1' THEN
		CALL proc_sync_rd_employee_info_delete_employee(p_employee_id,p_org_supervise_region_code,p_employee_hired_date);
			
		END IF;
		
		END IF;
		/**
		如果是一般职员转保安员，需要查看这个人目前在归集表中以一般职员身份是否有记录。
		如果有，则不管。
		如果没有，则要从智能人事找最后一条以一般职员身份的employee记录，然后插入一条新的
		*/

		IF p_employee_before_occupation_type != p_employee_occupation_type THEN
			
		CALL proc_sync_rd_employee_info_collect_occupation_type_0_to_1(p_tenant_user_id,region_domainid);
		
		END IF;
		
	END IF;
	
		
/*
2.一般职员：离职记录最多只有1条。一般职员可以同时入职多家公司
 (1)入职：从现有表中查找该人员的，且为一般职员的记录。
		新导入的老数据：
			如果没有数据，插入新数据。
			如果有一条数据，并且是离职的(employee-id肯定不同)，
				则删除原来记录，插入新记录
			如果有一条且在职，或者多条记录，则插入数据。

		新数据：
			如果没有记录（新公司），插入新记录；
			如果有一条记录，并且离职。比较employee-id是否相同。
				如果一样（复职），则更新数据。
				如果不一样，（入职新公司），则删除原来数据，并新增这条记录；
			如果有一条记录且在职，或者有多条记录（应该全部在职的）
		（入职新公司），则插入新数据。
	(2)离职：从现有表中查找该人员的，且为一般职员的记录。
		新导入的老数据：   
			如果没有记录，插入新记录；
			如果有一条记录，且离职（employee-id肯定不同），
				如果入职时间比当前记录早，则删除原数据，插入新数据。
			如果有一条在职记录，或者多条记录（都是在职的，
				employee肯定不同），则忽略
		新数据：	
			如果只有一条在职记录，且employee同（离职），更新数据。
			如果有多条记录（在职的），则删除原来记录中该employee-id的数据。
*/
	IF p_employee_occupation_type = 0 THEN
		#当前人员就离职数据条数
	SELECT count(1), e.employee_id, e.hire_date,e.leave_date,e.employed INTO data_cnt,original_employee_id,original_hired_date,original_leave_date,original_status
		FROM companyinfocollect.employees e
		WHERE e.user_id = p_tenant_user_id AND e.occupation_type = 0 AND e.DOMAINID = region_domainid LIMIT 1;
		
		IF data_cnt = 0 THEN #没有记录，则插入新数据
			CALL proc_sync_rd_employee_info_collect_insert_update_sub_procedure(p_employee_id, p_tenant_user_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_created_time,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_id, p_org_supervise_region_code, p_org_name, p_org_institutional_code , p_org_supervise_depart_name,p_org_supervise_depart_id,p_org_approved_status,p_user_checked_status,p_att_site_id,p_att_site_name,p_att_site_supervise_office, p_att_site_supervise_office_code,p_certNo, p_securityGrade,p_user_name,p_user_phone,p_user_sex,p_user_nation,p_user_education,p_user_birthdate,p_user_political_status,p_user_military_status,p_user_auth_status,p_auth_time,p_auth_result,p_background_screening_status,p_user_householder_type,p_user_stature,p_user_native_place,p_user_maritial_status,p_user_emgergency_contact,p_user_emgergency_phone,p_user_license_level,p_user_work_year,p_user_disease_history,p_user_now_address,p_user_selected_credential_id,p_after_salary);
		 #有记录
		ELSE
			IF p_employee_after_status = '0' THEN #入职
				IF data_cnt = 1 AND original_status = 1 THEN #原表只有一条离职记录
					IF original_employee_id != p_employee_id THEN 
					/* employee-id不相同。入职新公司或者老数据入职其他公司
					删除原来数据，插入新数据
					*/
						DELETE FROM companyinfocollect.employees WHERE employee_id = original_employee_id;
						IF p_org_approved_status='1' THEN
							# 删除的数据插入数据删除事件表，用于同步到内网去删除该数据
							CALL proc_sync_rd_employee_info_delete_employee(original_employee_id,p_org_supervise_region_code,original_hired_date);
							
						END IF;
						
						#插入当前记录
						CALL proc_sync_rd_employee_info_collect_insert_update_sub_procedure(p_employee_id, p_tenant_user_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_created_time,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_id, p_org_supervise_region_code, p_org_name, p_org_institutional_code , p_org_supervise_depart_name,p_org_supervise_depart_id,p_org_approved_status,p_user_checked_status,p_att_site_id,p_att_site_name,p_att_site_supervise_office, p_att_site_supervise_office_code,p_certNo, p_securityGrade,p_user_name,p_user_phone,p_user_sex,p_user_nation,p_user_education,p_user_birthdate,p_user_political_status,p_user_military_status,p_user_auth_status,p_auth_time,p_auth_result,p_background_screening_status,p_user_householder_type,p_user_stature,p_user_native_place,p_user_maritial_status,p_user_emgergency_contact,p_user_emgergency_phone,p_user_license_level,p_user_work_year,p_user_disease_history,p_user_now_address,p_user_selected_credential_id,p_after_salary);
						
					ELSE #原来有一条职员id相同的离职记录，则更新
						CALL proc_sync_rd_employee_info_collect_update_sub_procedure(p_employee_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_supervise_region_code,p_org_approved_status,p_after_salary);
								
					END IF;
				ELSE #只有一条且在职或者有多条记录（一定都是在职，且id不一样），插入
					CALL proc_sync_rd_employee_info_collect_insert_update_sub_procedure(p_employee_id, p_tenant_user_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_created_time,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_id, p_org_supervise_region_code, p_org_name, p_org_institutional_code , p_org_supervise_depart_name,p_org_supervise_depart_id,p_org_approved_status,p_user_checked_status,p_att_site_id,p_att_site_name,p_att_site_supervise_office, p_att_site_supervise_office_code,p_certNo, p_securityGrade,p_user_name,p_user_phone,p_user_sex,p_user_nation,p_user_education,p_user_birthdate,p_user_political_status,p_user_military_status,p_user_auth_status,p_auth_time,p_auth_result,p_background_screening_status,p_user_householder_type,p_user_stature,p_user_native_place,p_user_maritial_status,p_user_emgergency_contact,p_user_emgergency_phone,p_user_license_level,p_user_work_year,p_user_disease_history,p_user_now_address,p_user_selected_credential_id,p_after_salary);
					
				END IF;
			END IF;
			IF p_employee_after_status = '1' THEN #离职
				IF data_cnt = 1 THEN #只有一条记录
					IF original_status = 1 AND (original_hired_date < p_employee_hired_date OR (original_hired_date = p_employee_hired_date AND original_leave_date < p_employee_leave_time)) THEN/*只有一条离职记录,且入职时间小于当前记录。删除原纪录，并插入该条新纪录*/
						DELETE FROM companyinfocollect.employees WHERE employee_id = original_employee_id;
						IF p_org_approved_status='1' THEN
							# 删除的数据插入数据删除事件表，用于同步到内网去删除该数据
							CALL proc_sync_rd_employee_info_delete_employee(original_employee_id,p_org_supervise_region_code,original_hired_date);
							
						END IF;
						
						#插入当前记录
						CALL proc_sync_rd_employee_info_collect_insert_update_sub_procedure(p_employee_id, p_tenant_user_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_created_time,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_id, p_org_supervise_region_code, p_org_name, p_org_institutional_code , p_org_supervise_depart_name,p_org_supervise_depart_id,p_org_approved_status,p_user_checked_status,p_att_site_id,p_att_site_name,p_att_site_supervise_office, p_att_site_supervise_office_code,p_certNo, p_securityGrade,p_user_name,p_user_phone,p_user_sex,p_user_nation,p_user_education,p_user_birthdate,p_user_political_status,p_user_military_status,p_user_auth_status,p_auth_time,p_auth_result,p_background_screening_status,p_user_householder_type,p_user_stature,p_user_native_place,p_user_maritial_status,p_user_emgergency_contact,p_user_emgergency_phone,p_user_license_level,p_user_work_year,p_user_disease_history,p_user_now_address,p_user_selected_credential_id,p_after_salary);
					ELSEIF original_status = 0 AND original_employee_id = p_employee_id  THEN/*一条在职记录，如果id不同则忽略当前记录，如果相同，则离职更新原数据*/
						CALL proc_sync_rd_employee_info_collect_update_sub_procedure(p_employee_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_supervise_region_code,p_org_approved_status,p_after_salary);
					
					END IF;
				ELSE /*入职了多家公司，如果这些里面有当前公司的入职记录，则是离职操作从这些公司里面删除当前公司的入职记录。如果没有，则忽略（老数据导入）*/
					DELETE FROM companyinfocollect.employees WHERE employee_id = p_employee_id;
					IF p_org_approved_status='1' THEN
						# 删除的数据插入数据删除事件表，用于同步到内网去删除该数据
						CALL proc_sync_rd_employee_info_delete_employee(p_employee_id,p_org_supervise_region_code,p_employee_hired_date);
						
					END IF;
				END IF;
				
			END IF;
			
		END IF;
		/**对于由于保安员转成一般职员的情况，当前这条记录时离职的。
						如果原来只有一条离职记录，并且入职时间比这家晚：
                则删除归集表中这条记录；
            如果有一条在职记录则肯定不会是同一个职员id的，
                则归集后的表里删除掉现在的这条。*/
				IF p_employee_before_occupation_type != p_employee_occupation_type THEN
					IF (data_cnt = 1 AND original_status = 1 AND p_employee_after_status = 1 AND original_hired_date > p_employee_hired_date) OR (data_cnt = 1 AND  p_employee_after_status = 1 AND original_status = 0) THEN
					DELETE FROM companyinfocollect.employees WHERE employee_id = p_employee_id;
					IF p_org_approved_status='1' THEN
						# 删除的数据插入数据删除事件表，用于同步到内网去删除该数据
						CALL proc_sync_rd_employee_info_delete_employee(p_employee_id,p_org_supervise_region_code,p_employee_hired_date);
						
					END IF;
					
					END IF;
					
					/**需要查找该职员作为保安员的最后记录*/
					CALL proc_sync_rd_employee_info_collect_occupation_type_1_to_0(p_tenant_user_id,region_domainid);
					
				
				END IF;
	END IF;


	

ELSEIF p_employee_before_status = p_employee_after_status THEN
/*则更新*/
CALL proc_sync_rd_employee_info_collect_update_sub_procedure(p_employee_id,p_employee_occupation_type, p_employee_hired_date,p_employee_leave_time, p_employee_after_status,p_employee_insure,p_employee_last_update_time, p_employee_department_id,p_employee_department_name,
p_org_supervise_region_code,p_org_approved_status,p_after_salary);

END IF;
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_organization_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_organization_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_organization_v2`(IN p_org_id VARCHAR(300),p_before_org_name VARCHAR(300),p_after_org_name VARCHAR(300),p_org_supervise_region_code VARCHAR(300),p_org_supervise_office VARCHAR(300),p_before_org_supervise_office_code VARCHAR(300),p_after_org_supervise_office_code VARCHAR(300),p_org_institutional_code VARCHAR(300), p_last_updated_time DATETIME,  p_before_org_approved_status INT, p_after_org_approved_status INT,p_org_before_supervise_region_code VARCHAR(300))
BEGIN
#公司名称，监管机构发生变更，更新职员的对应公司信息。
IF p_before_org_name != p_after_org_name OR p_before_org_supervise_office_code != p_after_org_supervise_office_code OR (p_before_org_approved_status IS NULL OR p_before_org_approved_status != p_after_org_approved_status) OR (IFNULL(p_org_before_supervise_region_code,'') != p_org_supervise_region_code) THEN
	UPDATE companyinfocollect.employees e
	SET 
	e.DOMAINID = IF(p_after_org_approved_status = 1,(CASE 
		WHEN p_org_supervise_region_code LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN p_org_supervise_region_code LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN p_org_supervise_region_code LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			'#'
		END),'#'),
	e.company_name = p_after_org_name,
	e.item_region_id = IFNULL(p_org_supervise_region_code,'#'),
	 e.supervise_office = p_org_supervise_office,
	 e.supervise_office_code = p_after_org_supervise_office_code,
	 e.office_type = IFNULL(p_org_institutional_code,''),
	#e.LASTMODIFIED = IFNULL(p_last_updated_time,CURRENT_TIMESTAMP)
	e.LASTMODIFIED = CURRENT_TIMESTAMP
	WHERE e.item_domain_id = p_org_id;
END IF;

#如果公司原来没有设置监管归属，现在设置了监管归属，修改该组织下的所有职员的持证状态

IF p_org_before_supervise_region_code = '#' and p_org_before_supervise_region_code != p_org_supervise_region_code THEN
	UPDATE companyinfocollect.employees e
	SET 
		e.certificate_no = get_cert_no_by_region(e.certificate_no,p_org_supervise_region_code),
		e.LASTMODIFIED = CURRENT_TIMESTAMP
	WHERE e.item_domain_id = p_org_id;
	#这里暂时先写成2个update语句。之后再合并。
	UPDATE companyinfocollect.employees e
	SET 
		e.with_certificate = IF(e.certificate_no IS NULL,0,1),
		e.LASTMODIFIED = CURRENT_TIMESTAMP
	WHERE e.item_domain_id = p_org_id;
		
END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_securitymastermanagement
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_securitymastermanagement`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_securitymastermanagement`(IN b_document_id VARCHAR(200), a_document_id VARCHAR(200),a_lastmodified DATETIME,p_domainid VARCHAR(300), p_before_ITEM_DEGREE VARCHAR(200), p_after_ITEM_DEGREE VARCHAR(200))
BEGIN

/*保安师表发生变更，需要更新职员的等级信息中的保安师的部分。*/
	DECLARE p_doc_id VARCHAR(200) DEFAULT NULL;
	DECLARE p_before_tenant_user_id VARCHAR(200) DEFAULT NULL;#修改之前的user id
	DECLARE p_after_tenant_user_id VARCHAR(200) DEFAULT NULL;#修改之后的user id
	DECLARE p_before_security_grade VARCHAR(20) DEFAULT NULL; #修改前 保安师等级
	DECLARE p_after_security_grade VARCHAR(20) DEFAULT NULL; #修改后 保安师等级
	DECLARE p_region_code VARCHAR(10) DEFAULT NULL; #domainid对应的区域编码。这里用区域编码取更新查询，为的是防止公司未审核，但是职员有证书的情况。
	
	SET p_region_code = (CASE p_domainid
	WHEN 'xduDIOSsi06qmfpig0A' THEN
		'11%'
	WHEN '__UDa4uPMdcOYgP7HETaf' THEN
	  '43%'
	WHEN 'qS6PXrA7' THEN
	  '64%'
	ELSE
		'#'
  END);

	
	#新增
	IF b_document_id IS NULL AND a_document_id IS NOT NULL THEN
	
	SELECT uc.tenant_user_id INTO p_after_tenant_user_id
	FROM obpm2.tenant_user_credentials uc 
	WHERE uc.number = a_document_id LIMIT 1;
	
	SET p_after_security_grade = (CASE p_after_ITEM_DEGREE
		WHEN '一级' THEN
		1
		WHEN '二级' THEN
		2
		END);
	
	UPDATE companyinfocollect.employees e 
	SET  
		e.security_grade = concat_unique_strings(e.security_grade, p_after_security_grade,';'),
		e.security_grade_int = sum_of_powers(concat_unique_strings(e.security_grade, p_after_security_grade,';'), 0),
		#e.LASTMODIFIED = IFNULL(a_lastmodified,CURRENT_TIMESTAMP)
		e.LASTMODIFIED = CURRENT_TIMESTAMP
		WHERE e.user_id = p_after_tenant_user_id AND e.item_region_id LIKE p_region_code;
	
	#修改身份证号,或者等级证修改需要删除原来的，再增加现在的
	ELSEIF IFNULL(b_document_id,'') != IFNULL(a_document_id,'') OR IFNULL(p_before_ITEM_DEGREE,'') != IFNULL(p_after_ITEM_DEGREE,'') THEN
		#删除原来的
		SELECT uc.tenant_user_id INTO p_before_tenant_user_id
		FROM obpm2.tenant_user_credentials uc 
		WHERE uc.number = b_document_id LIMIT 1;
		
		SELECT uc.tenant_user_id INTO p_after_tenant_user_id
		FROM obpm2.tenant_user_credentials uc 
		WHERE uc.number = a_document_id LIMIT 1;
	
		SET p_before_security_grade = (CASE p_before_ITEM_DEGREE
		WHEN '一级' THEN
		1
		WHEN '二级' THEN
		2
		END);
		
		SET p_after_security_grade = (CASE p_after_ITEM_DEGREE
		WHEN '一级' THEN
		1
		WHEN '二级' THEN
		2
		END);
		
		#删除原来的
	UPDATE companyinfocollect.employees e 
	SET  
		e.security_grade = remove_specified_string(e.security_grade, p_before_security_grade,';'),
		e.security_grade_int = sum_of_powers(remove_specified_string(e.security_grade, p_before_security_grade,';'), 0),
		#e.LASTMODIFIED = IFNULL(a_lastmodified,CURRENT_TIMESTAMP)
		e.LASTMODIFIED = CURRENT_TIMESTAMP
		WHERE e.user_id = p_before_tenant_user_id AND e.item_region_id LIKE p_region_code;
	
		#增加现在的
	
	UPDATE companyinfocollect.employees e 
	SET  
		e.security_grade = concat_unique_strings(e.security_grade, p_after_security_grade,';'),
		e.security_grade_int = sum_of_powers(concat_unique_strings(e.security_grade, p_after_security_grade,';'), 0),
		#e.LASTMODIFIED = IFNULL(a_lastmodified,CURRENT_TIMESTAMP)
		e.LASTMODIFIED = CURRENT_TIMESTAMP
		WHERE e.user_id = p_after_tenant_user_id AND e.item_region_id LIKE p_region_code;
	
	
	END IF;

	

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_user_credentials_detail_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_user_credentials_detail_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_user_credentials_detail_v2`(IN p_tenant_user_id VARCHAR(300),p_certificate_type VARCHAR(10),p_after_zsbh VARCHAR(100), p_active INT, p_status CHAR(5),p_appraisalGrade VARCHAR(20),p_before_zsbh VARCHAR(100), p_before_idno VARCHAR(100), p_after_idno VARCHAR(100), p_before_status CHAR(5),p_before_user_id VARCHAR(300), p_before_areacode VARCHAR(20),p_after_areacode VARCHAR(20),p_before_appraisalGrade VARCHAR(20),p_before_mark VARCHAR(300),p_after_mark VARCHAR(300), p_credential_detail_id VARCHAR(200),p_before_credential_detail_id VARCHAR(200))
BEGIN
/**
资格证，登记证发生变更(新增和修改（撤销）等）。这里不用考虑user是否已核验的情况，因为如果是未核验的人员，tenant_user_id是null。
这里为了逻辑简单和减少计算，把等级证和资格证的情况分开。

删除证件和全量手动更新证书库的情况，不处理
*/
	DECLARE p_after_region_code VARCHAR(10) DEFAULT '#';#获取变更后区域前两位编码
	DECLARE p_before_region_code  VARCHAR(10) DEFAULT '#';#获取变更前区域前两位编码
	
	
	#IF p_credential_detail_id IS NOT NULL AND (p_before_mark IS NOT NULL OR p_after_mark IS NULL OR p_after_mark !='全量更新证书库')  THEN
	
	
	SET p_after_region_code = get_province_areacodes(p_after_areacode);
	SET p_before_region_code = get_province_areacodes(p_before_areacode);
	
	IF p_certificate_type = '1' THEN
	
		#新增资格证
		IF p_before_credential_detail_id IS NULL THEN
			IF p_active = 1 AND p_status = '1' THEN
				
				UPDATE companyinfocollect.employees e 
				SET 
				e.certificate_no = 
				IF(e.certificate_no IS NULL OR e.certificate_no='', p_after_zsbh, concat_unique_strings(e.certificate_no,p_after_zsbh,';')),
				
				e.with_certificate = 
				IF(e.certificate_no IS NULL OR e.certificate_no='', IF(p_after_zsbh IS NULL,0,1), IF(concat_unique_strings(e.certificate_no,p_after_zsbh,';') IS NULL,0,1)),
		
		e.LASTMODIFIED = CURRENT_TIMESTAMP
		WHERE e.user_id = p_tenant_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_after_region_code);
			END IF;
			
			
		#吊销资格证
		ELSEIF p_before_status != p_status THEN
		#证书无效
			IF p_status != '1' THEN
				UPDATE companyinfocollect.employees e
				SET e.certificate_no = remove_specified_string(e.certificate_no,p_after_zsbh,';'),
				e.with_certificate = IF(remove_specified_string(e.certificate_no,p_after_zsbh,';') IS NULL,0,1),
				e.LASTMODIFIED = CURRENT_TIMESTAMP
				WHERE e.user_id = p_tenant_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_after_region_code);
			END IF;
		
			/**同一个人，修改了资格证(理论上不会跨省)。去除原来的证书，新增现在的证书
			可以和下面的合并
			*/
		ELSEIF  p_before_user_id=p_tenant_user_id AND p_before_areacode = p_after_areacode AND IFNULL(p_before_zsbh,'') != IFNULL(p_after_zsbh,'')  AND p_active = 1 AND p_status = '1' THEN
			
			UPDATE companyinfocollect.employees e
			SET e.certificate_no = concat_unique_strings(remove_specified_string(e.certificate_no,p_before_zsbh,';'),p_after_zsbh,';'),
			e.with_certificate = IF(concat_unique_strings(remove_specified_string(e.certificate_no,p_before_zsbh,';'),p_after_zsbh,';') IS NULL,0,1),
			e.LASTMODIFIED = CURRENT_TIMESTAMP
			WHERE e.user_id = p_tenant_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_after_region_code);
			
			#区域发生变更，原区域人员证件清除，新区域证件增加
		  #人员绑定了错误的资格证.理论上不应该存在人员绑定错误同时又修改证件的情况,
			#不管是否是身份证变更，只关注tenant_user_id
			#原区域人员清除，新区域人员增加
		ELSEIF (IFNULL(p_before_areacode,'') != IFNULL(p_after_areacode,'')  OR IFNULL(p_before_user_id,'') != IFNULL(p_tenant_user_id,'')) AND p_active = 1 AND p_status = '1' THEN
			#原区域删除
			UPDATE companyinfocollect.employees e
			SET e.certificate_no = remove_specified_string(e.certificate_no,p_before_zsbh,';'),
			e.with_certificate = IF(remove_specified_string(e.certificate_no,p_before_zsbh,';') IS NULL,0,1),
			e.LASTMODIFIED = CURRENT_TIMESTAMP
			WHERE e.user_id = p_before_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_before_region_code);
			
			#新区域增加
				UPDATE companyinfocollect.employees e
			SET e.certificate_no = concat_unique_strings(e.certificate_no,p_after_zsbh,';'),
			e.with_certificate = IF(concat_unique_strings(e.certificate_no,p_after_zsbh,';') IS NULL,0,1),
			e.LASTMODIFIED = CURRENT_TIMESTAMP
			WHERE e.user_id = p_tenant_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_after_region_code);
			
	END IF;
END IF;

		
	IF p_certificate_type = '2'  THEN		
	#新增等级证
		IF p_before_credential_detail_id IS NULL THEN
			IF p_active = 1 THEN
				
				UPDATE companyinfocollect.employees e 
				SET 
				e.security_grade= concat_unique_strings(e.security_grade, p_appraisalGrade,';'),
				
				e.security_grade_int = sum_of_powers(concat_unique_strings(e.security_grade, p_appraisalGrade,';'), 0),
		
		e.LASTMODIFIED = CURRENT_TIMESTAMP
		WHERE e.user_id = p_tenant_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_after_region_code);
			END IF;
			
			/**同一个人，修改了等级证(理论上不会跨省)。去除原来的证书，新增现在的证书
			*/
		/**ELSEIF p_before_user_id=p_tenant_user_id AND p_before_areacode = p_after_areacode AND ((p_appraisalGrade IS NULL AND p_before_appraisalGrade IS NOT NULL) OR p_appraisalGrade != p_before_appraisalGrade)  AND p_active = 1 AND p_status = '1' THEN
			
			UPDATE companyinfocollect.employees e
			SET e.security_grade = concat_unique_strings(remove_specified_string(e.security_grade,p_before_appraisalGrade,';'), p_appraisalGrade,';'),
			e.security_grade_int = sum_of_powers(concat_unique_strings(remove_specified_string(e.security_grade,p_before_appraisalGrade,';'), p_appraisalGrade,';'), 0),
			e.LASTMODIFIED = CURRENT_TIMESTAMP
			WHERE e.user_id = p_tenant_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_after_region_code);*/
			
			#区域发生变更，原区域人员证件清除，新区域证件增加
		  #人员绑定了错误的资格证.理论上不应该存在人员绑定错误同时又修改证件的情况,
			#原区域人员清除，新区域人员增加
		ELSEIF (IFNULL(p_before_areacode,'') != IFNULL(p_after_areacode,'')  OR IFNULL(p_before_user_id,'') != IFNULL(p_tenant_user_id,'') OR IFNULL(p_appraisalGrade,'')  != IFNULL(p_before_appraisalGrade,'')) AND p_active = 1 THEN
			#原区域删除
			UPDATE companyinfocollect.employees e
			SET e.security_grade = remove_specified_string(e.security_grade,p_before_appraisalGrade,';'),
			e.security_grade_int = sum_of_powers(remove_specified_string(e.security_grade,p_before_appraisalGrade,';'), 0),
			e.LASTMODIFIED = CURRENT_TIMESTAMP
			WHERE e.user_id = p_before_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_before_region_code);
			
			#新区域增加
				UPDATE companyinfocollect.employees e
			SET e.security_grade = concat_unique_strings(e.security_grade,p_appraisalGrade,';'),
			e.security_grade_int = sum_of_powers(concat_unique_strings(e.security_grade,p_appraisalGrade,';'), 0),
			e.LASTMODIFIED = CURRENT_TIMESTAMP
			WHERE e.user_id = p_tenant_user_id AND FIND_IN_SET(LEFT(e.item_region_id,2),p_after_region_code);
			
	END IF;
	
	
	END IF;
	#END IF;
	
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_user_credentials_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_user_credentials_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_user_credentials_v2`(IN p_selected INT, p_tenant_user_id VARCHAR(300),p_credential_type INT, p_update_time DATETIME,
p_id_no VARCHAR(300), p_id_issue_date date,p_id_expire_date date,p_id_address VARCHAR(500),p_id_back_img VARCHAR(500),p_id_front_img VARCHAR(500), p_id_person_photo VARCHAR(500))
BEGIN
/*证件表发生变更，根据类型更新对应的身份证，资格证，等级证等信息。等级证需要保留保安师等级信息*/
-- DECLARE p_tenant_user_id VARCHAR(300) DEFAULT NULL;
-- DECLARE p_credential_type INT DEFAULT NULL;
-- DECLARE p_id_type INT DEFAULT NULL; -- 选中的证件
-- DECLARE p_id_number VARCHAR(200) DEFAULT NULL;

#DECLARE p_certNo VARCHAR(50) DEFAULT NULL;
#	DECLARE p_withCertificate TINYINT default 0;
#	DECLARE p_security_grade VARCHAR(50) DEFAULT NULL; -- 保安员等级
	-- DECLARE p_security_master_grade VARCHAR(50) DEFAULT NULL; -- 保安师等级
	
	-- SET p_tenant_user_id = IFNULL(b_tenant_user_id,a_tenant_user_id);
	-- SET p_credential_type = IFNULL(b_credential_type,a_credential_type);
	
	-- 更新身份证
	 -- IF p_credential_type = 0 THEN
		/**SELECT tuc.credential_type, tuc.number INTO  p_id_type,p_id_number
		FROM obpm2.tenant_users u JOIN 
		obpm2.tenant_user_credentials tuc ON u.selected_credential_id = tuc.id
		WHERE u.id = p_tenant_user_id LIMIT 1;
		
		UPDATE companyinfocollect.employees e 
			SET e.id_type = p_id_type,
			e.id_no = p_id_number,
			e.LASTMODIFIED = IFNULL(p_update_time,CURRENT_TIMESTAMP)
			WHERE e.user_id = p_tenant_user_id;*/
			
			
		# 更新默认证件信息
		IF p_selected = 1 THEN
			UPDATE companyinfocollect.employees e
			SET e.id_type = 
			(CASE
				WHEN ISNULL(p_credential_type) THEN
					NULL
				WHEN  p_credential_type IN ('0','1','2','3','4') THEN
					p_credential_type
				ELSE
					5
			END),
			e.id_no = p_id_no,
			e.id_issue_date = p_id_issue_date,
			e.id_expire_date = p_id_expire_date,
			e.id_address = p_id_address,
			e.id_back_img = IF(p_id_back_img IS NULL OR p_id_back_img = '','',concat( '[{"name":"","path":"', p_id_back_img, '"}]')),
			e.id_front_img = IF(p_id_front_img IS NULL OR p_id_front_img = '','',concat( '[{"name":"","path":"', p_id_front_img, '"}]')),
			e.id_person_photo = IF(p_id_person_photo IS NULL OR p_id_person_photo = '','',concat( '[{"name":"","path":"', p_id_person_photo, '"}]')),
			e.profile_photo = IF(p_id_person_photo IS NULL OR p_id_person_photo = '','',concat( '[{"name":"","path":"', p_id_person_photo, '"}]')),
			#e.LASTMODIFIED = IFNULL(p_update_time,CURRENT_TIMESTAMP)
			e.LASTMODIFIED = CURRENT_TIMESTAMP
			WHERE e.user_id = p_tenant_user_id;
		END IF;
	

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_by_user_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_by_user_v2`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_by_user_v2`(IN p_user_id VARCHAR(300), p_user_name VARCHAR(255),p_user_phone VARCHAR(50),p_user_sex INT,p_user_nation VARCHAR(100),p_user_education VARCHAR(100),p_user_birthdate DATE,p_user_political_status VARCHAR(100),p_user_military_status VARCHAR(200),p_user_after_check_status INT,p_user_auth_status INT,p_user_auth_time DATETIME,p_user_auth_result VARCHAR(300),p_user_background_screen_status INT,p_last_updated_time DATETIME, p_user_residence VARCHAR(200),p_user_height VARCHAR(200),p_user_birthplace VARCHAR(200),p_user_marriage VARCHAR(200),p_user_emergency_contact VARCHAR(200), p_user_emergency_phone VARCHAR(200),p_user_driver_license VARCHAR(200),p_user_work_years VARCHAR(10), p_user_medical_history VARCHAR(500),p_user_address VARCHAR(200),
p_before_user_id VARCHAR(300), p_before_user_name VARCHAR(255),p_before_user_phone VARCHAR(50),p_before_user_sex INT,p_before_user_nation VARCHAR(100),p_before_user_education VARCHAR(100),p_before_user_birthdate DATE,p_before_user_political_status VARCHAR(100),p_before_user_military_status VARCHAR(200),p_before_user_check_status INT,p_before_user_auth_status INT,p_before_user_auth_time DATETIME,p_before_user_auth_result VARCHAR(300),p_before_user_background_screen_status INT, p_before_user_residence VARCHAR(200),p_before_user_height VARCHAR(200),p_before_user_birthplace VARCHAR(200),p_before_user_marriage VARCHAR(200),p_before_user_emergency_contact VARCHAR(200), p_before_user_emergency_phone VARCHAR(200),p_before_user_driver_license VARCHAR(200),p_before_user_work_years VARCHAR(10), p_before_user_medical_history VARCHAR(500),p_before_user_address VARCHAR(200))
BEGIN
	
/*人员信息发生变更，更新对应的职员信息*/
	#DECLARE p_certNo VARCHAR(50) DEFAULT NULL;
	#DECLARE p_withCertificate TINYINT default 0;
	#DECLARE p_security_grade VARCHAR(50) DEFAULT NULL; -- 保安员等级
	/**DECLARE checked_status_changed INT DEFAULT 0; #判断核验状态是否发证了变更
	IF (p_user_before_check_status IS NULL) OR p_user_before_check_status != p_user_after_check_status THEN
	SET checked_status_changed = 1;
	END IF;
*/
	
	IF (IFNULL(p_before_user_id,'') != IFNULL(p_user_id,'') || IFNULL(p_before_user_name,'') != IFNULL(p_user_name,'') || IFNULL(p_before_user_phone,'') != IFNULL(p_user_phone,'') || IFNULL(p_before_user_sex,'') != IFNULL(p_user_sex,'') || IFNULL(p_before_user_nation,'') != IFNULL(p_user_nation,'') || IFNULL(p_before_user_education,'') != IFNULL(p_user_education,'') || IFNULL(p_before_user_birthdate,'') != IFNULL(p_user_birthdate,'') || IFNULL(p_before_user_political_status,'') != IFNULL(p_user_political_status,'') || IFNULL(p_before_user_military_status,'') != IFNULL(p_user_military_status,'') || IFNULL(p_before_user_check_status,'') != IFNULL(p_user_after_check_status,'') || IFNULL(p_before_user_auth_status,'') != IFNULL(p_user_auth_status,'') || IFNULL(p_before_user_auth_time,'') != IFNULL(p_user_auth_time,'') || IFNULL(p_before_user_auth_result,'') != IFNULL(p_user_auth_result,'') || IFNULL(p_before_user_background_screen_status,'') != IFNULL(p_user_background_screen_status,'') || IFNULL(p_before_user_residence,'') != IFNULL(p_user_residence,'') || IFNULL(p_before_user_height,'') != IFNULL(p_user_height,'') || IFNULL(p_before_user_birthplace,'') != IFNULL(p_user_birthplace,'') || IFNULL(p_before_user_marriage,'') != IFNULL(p_user_marriage,'') || IFNULL(p_before_user_emergency_contact,'') != IFNULL(p_user_emergency_contact,'') || IFNULL(p_before_user_emergency_phone,'') != IFNULL(p_user_emergency_phone,'') || IFNULL(p_before_user_driver_license,'') != IFNULL(p_user_driver_license,'') || IFNULL(p_before_user_work_years,'') != IFNULL(p_user_work_years,'') || IFNULL(p_before_user_medical_history,'') != IFNULL(p_user_medical_history,'') || IFNULL(p_before_user_address,'') != IFNULL(p_user_address,'')) THEN
	
	
	UPDATE companyinfocollect.employees e 
	
	SET 
	e.`name` = IFNULL(p_user_name,''),
	e.phone = p_user_phone,
	e.gender = (CASE p_user_sex 
	WHEN 1 THEN 1
	WHEN 2 THEN 2
	ELSE 0
	END),
	e.ethnic_group = p_user_nation,
	e.education = (CASE 
	WHEN p_user_education IN ('小学', '1') THEN
		2
	WHEN p_user_education IN ('初中','2') THEN
		2
	WHEN p_user_education IN ('高中','3') THEN
		3
	WHEN p_user_education IN ('中专','4')THEN
		4
	WHEN p_user_education IN ('大专','5')THEN
		5
	WHEN p_user_education IN ('本科','6') THEN
		6
	WHEN p_user_education IN ('硕士研究生','硕士研究生及以上', '7') THEN
		7
	WHEN p_user_education IN ('博士','博士后','8') THEN
		7
	ELSE
		0
	END),
	e.birth_date = p_user_birthdate,
	e.political_status = (
	CASE
	WHEN p_user_political_status IN ('中共党员','01','1') THEN
		1
		WHEN p_user_political_status IN ('中共预备党员','02','2' )THEN
		2
		WHEN p_user_political_status IN ('共青团员','03', '3') THEN
		3
		WHEN p_user_political_status IN('民革会员','04','4') THEN
		4
		WHEN p_user_political_status IN ('民盟盟员','05','5') THEN
		5
		WHEN p_user_political_status IN ('民建会员','06','6' )THEN
		6
		WHEN p_user_political_status IN ('民进会员','07','7') THEN
		7
		WHEN p_user_political_status IN ('农工党党员','08','8') THEN
		8
		WHEN p_user_political_status IN ('致公党党员','09','9') THEN
		9
		WHEN p_user_political_status IN ('九三学社社员','10') THEN
		10
		WHEN p_user_political_status IN ('台盟盟员','11') THEN
		11
		WHEN p_user_political_status IN('无党派民主人士', '12') THEN
		12	
		WHEN p_user_political_status IN ('群众','13') THEN
		13	
	ELSE
		0
	END),
	e.military = (
	CASE 
	WHEN p_user_military_status IN ('未服','否','0') THEN
		1
	WHEN p_user_military_status IN ('服过','是','1') THEN
		2
	ELSE
		0
	END
	),
	e.check_status = (CASE p_user_after_check_status
	WHEN 1 THEN
		1
	ELSE
		0
	END),
	e.auth_status = p_user_auth_status,
	e.last_auth_time = p_user_auth_time,
	e.auth_result = p_user_auth_result,
	e.background_status = (CASE p_user_background_screen_status
	WHEN 1 THEN
		1
	WHEN 2 THEN
		2
	ELSE 0
	END),
	#e.LASTMODIFIED = IFNULL(p_last_updated_time,CURRENT_TIMESTAMP),
	e.LASTMODIFIED = CURRENT_TIMESTAMP,
	e.last_sync_time = CURRENT_TIMESTAMP,
	e.residence = (CASE p_user_residence 
	WHEN '农村' THEN 1  
	WHEN '城镇' THEN 2 
	WHEN '乡镇' THEN 2 
	WHEN '城市' THEN 2 
	ELSE 0 END),
	e.height = IF(p_user_height='',NULL,p_user_height),
	e.birth_place = p_user_birthplace,
	e.marriage = (CASE p_user_marriage 
	WHEN '未婚' THEN 1  
	WHEN '已婚' THEN 2 
	WHEN '离异' THEN 3 
	WHEN '丧偶' THEN 4 
	WHEN '其他' THEN 5 
	ELSE 0 END),
	e.emergency_contact = p_user_emergency_contact,
	e.emergency_phone = p_user_emergency_phone,
	e.driver_license = p_user_driver_license,
	e.work_years = p_user_work_years,
	e.medical_history = p_user_medical_history,
	e.address = p_user_address,
		#e.certificate_no = IF(checked_status_changed = 0,e.certificate_no,get_cert_no_by_region(p_certNo,e.item_region_id)),
		#e.with_certificate = IF(checked_status_changed = 0,e.with_certificate,IF(get_cert_no_by_region(p_certNo,e.item_region_id) IS NULL,0,1)) ,
		#e.security_grade = IF(checked_status_changed = 0,e.security_grade,concat_security_grade(e.security_grade, get_cert_no_by_region(p_security_grade, e.item_region_id))),
		#e.security_grade_int = IF(checked_status_changed = 0,e.security_grade_int,sum_of_powers(concat_security_grade(e.security_grade, get_cert_no_by_region(p_security_grade, e.item_region_id)), 0)),
			#e.LASTMODIFIED = IFNULL(p_last_updated_time,CURRENT_TIMESTAMP)
		e.LASTMODIFIED = CURRENT_TIMESTAMP
	WHERE e.user_id = p_user_id;
	
	END IF;
	
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_insert_sub_procedure
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_insert_sub_procedure`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_insert_sub_procedure`(IN p_employee_id VARCHAR(300), p_tenant_user_id VARCHAR(300),p_employee_occupation_type INT, p_employee_hired_date DATETIME,p_employee_leave_time DATETIME, p_employee_status INT,p_employee_insure INT,p_created_time DATETIME,p_employee_last_update_time DATETIME, p_employee_department_id VARCHAR(300),p_employee_department_name VARCHAR(200),
p_org_id VARCHAR (200), p_org_supervise_region_code VARCHAR(200), p_org_name VARCHAR(200), p_org_institutional_code VARCHAR(200), p_org_supervise_depart_name VARCHAR(200),p_org_supervise_depart_id VARCHAR(200),p_org_approved_status VARCHAR(200),p_user_checked_status VARCHAR(200),
p_att_site_id VARCHAR(300),p_att_site_name VARCHAR(300),p_att_site_supervise_office VARCHAR(300), p_att_site_supervise_office_code VARCHAR(300),
p_certNo VARCHAR(300), p_securityGrade VARCHAR(300),
p_user_name VARCHAR(200),p_user_phone VARCHAR(100),p_user_sex VARCHAR(10),p_user_nation VARCHAR(100),p_user_education VARCHAR(100),p_user_birthdate VARCHAR(100),p_user_political_status VARCHAR(100),p_user_military_status VARCHAR(100),p_user_auth_status VARCHAR(100),p_auth_time VARCHAR(100),p_auth_result VARCHAR(300),p_background_screening_status VARCHAR(100),p_user_householder_type VARCHAR(100),p_user_stature VARCHAR(100),p_user_native_place VARCHAR(300),p_user_maritial_status VARCHAR(100),p_user_emgergency_contact VARCHAR(200),p_user_emgergency_phone VARCHAR(200),p_user_license_level VARCHAR(100),p_user_work_year VARCHAR(100),p_user_disease_history VARCHAR(300),p_user_now_address VARCHAR(300),p_user_selected_credential_id VARCHAR(300), p_after_salary VARCHAR(100))
BEGIN
	#插入职员表子过程
	DECLARE p_credential_head_photo VARCHAR(300) DEFAULT NULL; #证件头像
	DECLARE p_credential_type INT DEFAULT NULL; #证件类型
	DECLARE p_credential_num VARCHAR(300) DEFAULT NULL; #证件号码
	DECLARE p_credential_validate_from DATE DEFAULT NULL; #证件起始日期
	DECLARE p_credential_validate_to DATE DEFAULT NULL; #证件截至日期
	DECLARE p_credential_address VARCHAR (500) DEFAULT NULL; #证件地址
	DECLARE p_credential_front_photo VARCHAR(500) DEFAULT NULL;#证件头像面
	DECLARE p_credential_reverse_photo VARCHAR(500) DEFAULT NULL;#证件国徽面 
	DECLARE p_security_master_grade VARCHAR(20) DEFAULT NULL; -- 保安师
	DECLARE p_domain_id VARCHAR(200) DEFAULT '#'; #获取保安师等级时使用的DOMAINID
	SET p_domain_id = (CASE 
							WHEN p_org_supervise_region_code LIKE '11%' THEN 
								'xduDIOSsi06qmfpig0A'
							WHEN p_org_supervise_region_code LIKE '43%' THEN 
								'__UDa4uPMdcOYgP7HETaf'
							WHEN p_org_supervise_region_code LIKE '64%' THEN 
								'qS6PXrA7'
							ELSE
								'#'
							END);
	#按照区域获取保安员等级
	SET p_securityGrade = get_cert_no_by_region(p_securityGrade, p_org_supervise_region_code);
	 
	#获取证件信息
	SELECT 
		tuc.head_photo, 
		tuc.credential_type, 
		tuc.number,
		tuc.valid_date_from,
		tuc.valid_date_to,
		tuc.address,
		tuc.front_photo,
		tuc.reverse_photo
	INTO 
		p_credential_head_photo,
		p_credential_type,
		p_credential_num,
		p_credential_validate_from,
		p_credential_validate_to,
		p_credential_address,
		p_credential_front_photo,
		p_credential_reverse_photo
	FROM obpm2.tenant_user_credentials tuc
	WHERE tuc.id = p_user_selected_credential_id LIMIT 1 ;
	
	#获取保安师的等级，并和等级证拼接到一起，以类似1；2；3；4；5的形式展示
	SELECT GROUP_CONCAT(DISTINCT(CASE mm.ITEM_DEGREE
	WHEN '一级' THEN
		1
	WHEN '二级' THEN
		2
END
) SEPARATOR ';') INTO p_security_master_grade
	FROM baibaodunflow.tlk_securitymastermanagement mm 
	JOIN obpm2.tenant_user_credentials uc ON mm.ITEM_DOCUMENTID = uc.number
	WHERE uc.tenant_user_id = p_tenant_user_id AND mm.DOMAINID = p_domain_id;
	
	IF p_security_master_grade IS NOT NULL AND p_security_master_grade!= '' THEN
		IF p_securityGrade IS NULL OR p_securityGrade = '' THEN
			SET p_securityGrade = p_security_master_grade;
		ELSE
			SET p_securityGrade = CONCAT(p_security_master_grade,';',p_securityGrade);
		END IF;
	END IF;
	
	
	
	INSERT INTO companyinfocollect.employees (
	DOMAINID,
	item_region_id,
	employee_id,
	user_id,
	profile_photo,
	name,
	id_type,
	id_no,
	phone,
	gender,
	ethnic_group,
	education,
	birth_date,
	political_status,
	military,
	occupation_type,
	hire_date,
	leave_date,
	employed,
	insure_status,
	check_status,
	auth_status,
	last_auth_time,
	auth_result,
	background_status,
	certificate_no,
	with_certificate,
	security_grade,
	security_grade_int,
	record_date,
	company_name,
	office_type,
	company_id,
	item_domain_id,
	department,
	department_id,
	supervise_office,
	supervise_office_code,
	att_site_name,
	att_site_id,
	att_site_supervise_office,
	att_site_supervise_office_code,
	in_att_site,
	LASTMODIFIED,
	last_sync_time,
	residence,
	height,
	birth_place,
	marriage,
	emergency_contact,
	emergency_phone,
	driver_license,
	work_years,
	medical_history,
	address,
	id_issue_date,
	id_expire_date,
	id_address,
	id_back_img,
	id_front_img,
	id_person_photo,
	salary
	)
	SELECT 
	
	IF(p_org_approved_status='1',(CASE 
		WHEN p_org_supervise_region_code LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN p_org_supervise_region_code LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN p_org_supervise_region_code LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			'#'
		END),'#') AS domain_id,
	IFNULL(p_org_supervise_region_code,'#') AS region_id,
	p_employee_id AS employee_id,
	p_tenant_user_id AS user_id,
	IF(p_credential_head_photo IS NULL OR p_credential_head_photo = '','',concat( '[{"name":"","path":"', p_credential_head_photo, '"}]')) AS profile_photo,
	IFNULL(p_user_name,'') AS name,
	(CASE
	WHEN ISNULL(p_credential_type) THEN
		NULL
	WHEN  p_credential_type IN ('0','1','2','3','4') THEN
		p_credential_type
	ELSE
		5
END) AS id_type,
	p_credential_num AS id_no,
	p_user_phone AS phone,
	(CASE p_user_sex 
	WHEN 1 THEN 1
	WHEN 2 THEN 2
	ELSE 0
	END) AS gender,
	IF(p_user_nation = '', NULL,p_user_nation) AS ethnic_group,
	(CASE 
	WHEN p_user_education IN ('小学', '1') THEN
		2
	WHEN p_user_education IN ('初中','2') THEN
		2
	WHEN p_user_education IN ('高中','3') THEN
		3
	WHEN p_user_education IN ('中专','4')THEN
		4
	WHEN p_user_education IN ('大专','5')THEN
		5
	WHEN p_user_education IN ('本科','6') THEN
		6
	WHEN p_user_education IN ('硕士研究生','硕士研究生及以上', '7') THEN
		7
	WHEN p_user_education IN ('博士','博士后','8') THEN
		7
	ELSE
		0
	END) AS education,
	p_user_birthdate AS birth_date,
	(
	CASE
	WHEN p_user_political_status IN ('中共党员','01','1') THEN
		1
		WHEN p_user_political_status IN ('中共预备党员','02','2' )THEN
		2
		WHEN p_user_political_status IN ('共青团员','03', '3') THEN
		3
		WHEN p_user_political_status IN('民革会员','04','4') THEN
		4
		WHEN p_user_political_status IN ('民盟盟员','05','5') THEN
		5
		WHEN p_user_political_status IN ('民建会员','06','6' )THEN
		6
		WHEN p_user_political_status IN ('民进会员','07','7') THEN
		7
		WHEN p_user_political_status IN ('农工党党员','08','8') THEN
		8
		WHEN p_user_political_status IN ('致公党党员','09','9') THEN
		9
		WHEN p_user_political_status IN ('九三学社社员','10') THEN
		10
		WHEN p_user_political_status IN ('台盟盟员','11') THEN
		11
		WHEN p_user_political_status IN('无党派民主人士', '12') THEN
		12	
		WHEN p_user_political_status IN ('群众','13') THEN
		13	
	ELSE
		0
	END) AS political_status,
	(
	CASE 
	WHEN p_user_military_status IN ('未服','否','0') THEN
		1
	WHEN p_user_military_status IN ('服过','是','1') THEN
		2
	ELSE
		0
	END
	) AS military,
	IFNULL(p_employee_occupation_type,0) AS occupation_type,
	(CASE 
	WHEN p_employee_hired_date > NOW() THEN #正常入离职时间在timestamp时间范围之内。如果错误数据，则进行转换。
		CURRENT_TIMESTAMP
		WHEN p_employee_hired_date < '1970-01-01 00:00:00' THEN
		'1970-01-02'
	ELSE
		p_employee_hired_date
END ) AS hire_date,
(CASE 
	WHEN p_employee_leave_time > NOW() THEN
		CURRENT_TIMESTAMP
		WHEN p_employee_leave_time < '1970-01-01 00:00:00' THEN
		'1970-01-02'
	ELSE
		p_employee_leave_time
END ) AS leave_date,
	(CASE p_employee_status #0在职，1离职
	WHEN 0 THEN
		0
	ELSE
		1
	END) AS employed,
	IFNULL(p_employee_insure,0) AS insure_status,
	(CASE p_user_checked_status #1已核验，0未核验
	WHEN 1 THEN
		1
	ELSE
		0
	END) AS check_status,
	IFNULL(p_user_auth_status,0) AS auth_status,
	p_auth_time AS last_auth_time,
	p_auth_result AS auth_result,
	(CASE p_background_screening_status #1背筛正常，2失败，0未背筛
	WHEN 1 THEN
		1
	WHEN 2 THEN
		2
	ELSE 0
	END) AS background_status,
	get_cert_no_by_region(p_certNo,p_org_supervise_region_code) AS certificate_no,
	IF(get_cert_no_by_region(p_certNo,p_org_supervise_region_code) IS NULL,0,1) AS with_certificate,
	p_securityGrade AS security_grade,
	sum_of_powers(p_securityGrade, 0) AS security_grade_int,
	IFNULL(p_created_time,CURRENT_TIMESTAMP) AS record_date,
	IFNULL(p_org_name,'') AS company_name,
	p_org_institutional_code AS office_type,
	IFNULL(CONCAT(REPLACE(p_org_id,'--','__'),'--__2eoK1zvtxNarHlwwQg4'),'') AS company_id, #替换数据来源表的表单id，以便在本应用可以使用
	p_org_id AS item_domain_id,
	p_employee_department_name AS department,
	p_employee_department_id AS department_id,
	IFNULL(p_org_supervise_depart_name,'#') AS supervise_office,
	IFNULL(p_org_supervise_depart_id,'#') AS supervise_office_code,
	p_att_site_name AS att_site_name, 
	p_att_site_id AS att_site_id,
	p_att_site_supervise_office AS att_site_supervise_office,
	p_att_site_supervise_office_code AS att_site_supervise_office_code,
	IF(p_att_site_id IS NULL OR p_att_site_id = '',0,1) AS in_att_site,
	#IFNULL(p_employee_last_update_time,CURRENT_TIMESTAMP) AS last_modified,
	CURRENT_TIMESTAMP AS last_modified,
	CURRENT_TIMESTAMP AS last_sync_time,
	(CASE p_user_householder_type 
	WHEN '农村' THEN 1  
	WHEN '城镇' THEN 2 
	WHEN '乡镇' THEN 2 
	WHEN '城市' THEN 2 
	ELSE 0 END)  AS residence,
	IF(p_user_stature='',NULL,p_user_stature) AS height,
	p_user_native_place AS birthplace,
	(CASE p_user_maritial_status 
	WHEN '未婚' THEN 1  
	WHEN '已婚' THEN 2 
	WHEN '离异' THEN 3 
	WHEN '丧偶' THEN 4 
	WHEN '其他' THEN 5 
	ELSE 0 END) AS marriage,
	p_user_emgergency_contact AS emergency_contact,
	 p_user_emgergency_phone AS emergency_phone,
	 p_user_license_level AS driver_license,
	 p_user_work_year AS work_years,
		p_user_disease_history AS medical_history,
		p_user_now_address AS address,
		p_credential_validate_from AS id_issue_date,
		p_credential_validate_to AS id_expire_date,
		p_credential_address AS id_address,
		IF(p_credential_front_photo IS NULL OR p_credential_front_photo = '','',concat( '[{"name":"","path":"', p_credential_front_photo, '"}]')) AS id_back_image,
		IF(p_credential_reverse_photo IS NULL OR p_credential_reverse_photo = '','',concat( '[{"name":"","path":"', p_credential_reverse_photo, '"}]')) AS id_front_image,
		IF(p_credential_head_photo IS NULL OR p_credential_head_photo = '','',concat( '[{"name":"","path":"', p_credential_head_photo, '"}]')) AS id_person_photo,
		p_after_salary as salary;
	
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_insert_update_sub_procedure
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_insert_update_sub_procedure`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_insert_update_sub_procedure`(IN p_employee_id VARCHAR(300), p_tenant_user_id VARCHAR(300),p_employee_occupation_type INT, p_employee_hired_date DATETIME,p_employee_leave_time DATETIME, p_employee_status INT,p_employee_insure INT,p_created_time DATETIME,p_employee_last_update_time DATETIME, p_employee_department_id VARCHAR(300),p_employee_department_name VARCHAR(200),
p_org_id VARCHAR (200), p_org_supervise_region_code VARCHAR(200), p_org_name VARCHAR(200), p_org_institutional_code VARCHAR(200), p_org_supervise_depart_name VARCHAR(200),p_org_supervise_depart_id VARCHAR(200),p_org_approved_status VARCHAR(200),p_user_checked_status VARCHAR(200),
p_att_site_id VARCHAR(300),p_att_site_name VARCHAR(300),p_att_site_supervise_office VARCHAR(300), p_att_site_supervise_office_code VARCHAR(300),
p_certNo VARCHAR(300), p_securityGrade VARCHAR(300),
p_user_name VARCHAR(200),p_user_phone VARCHAR(100),p_user_sex VARCHAR(10),p_user_nation VARCHAR(100),p_user_education VARCHAR(100),p_user_birthdate VARCHAR(100),p_user_political_status VARCHAR(100),p_user_military_status VARCHAR(100),p_user_auth_status VARCHAR(100),p_auth_time VARCHAR(100),p_auth_result VARCHAR(300),p_background_screening_status VARCHAR(100),p_user_householder_type VARCHAR(100),p_user_stature VARCHAR(100),p_user_native_place VARCHAR(300),p_user_maritial_status VARCHAR(100),p_user_emgergency_contact VARCHAR(200),p_user_emgergency_phone VARCHAR(200),p_user_license_level VARCHAR(100),p_user_work_year VARCHAR(100),p_user_disease_history VARCHAR(300),p_user_now_address VARCHAR(300),p_user_selected_credential_id VARCHAR(300), p_after_salary VARCHAR(100))
BEGIN
	#插入职员表子过程
	DECLARE p_credential_head_photo VARCHAR(300) DEFAULT NULL; #证件头像
	DECLARE p_credential_type INT DEFAULT NULL; #证件类型
	DECLARE p_credential_num VARCHAR(300) DEFAULT NULL; #证件号码
	DECLARE p_credential_validate_from DATE DEFAULT NULL; #证件起始日期
	DECLARE p_credential_validate_to DATE DEFAULT NULL; #证件截至日期
	DECLARE p_credential_address VARCHAR (500) DEFAULT NULL; #证件地址
	DECLARE p_credential_front_photo VARCHAR(500) DEFAULT NULL;#证件头像面
	DECLARE p_credential_reverse_photo VARCHAR(500) DEFAULT NULL;#证件国徽面 
	DECLARE p_security_master_grade VARCHAR(20) DEFAULT NULL; -- 保安师
	DECLARE p_domain_id VARCHAR(200) DEFAULT '#'; #获取保安师等级时使用的DOMAINID
	SET p_domain_id = (CASE 
							WHEN p_org_supervise_region_code LIKE '11%' THEN 
								'xduDIOSsi06qmfpig0A'
							WHEN p_org_supervise_region_code LIKE '43%' THEN 
								'__UDa4uPMdcOYgP7HETaf'
							WHEN p_org_supervise_region_code LIKE '64%' THEN 
								'qS6PXrA7'
							ELSE
								'#'
							END);
	#按照区域获取保安员等级
	SET p_securityGrade = get_cert_no_by_region(p_securityGrade, p_org_supervise_region_code);
	 
	#获取证件信息
	SELECT 
		tuc.head_photo, 
		tuc.credential_type, 
		tuc.number,
		tuc.valid_date_from,
		tuc.valid_date_to,
		tuc.address,
		tuc.front_photo,
		tuc.reverse_photo
	INTO 
		p_credential_head_photo,
		p_credential_type,
		p_credential_num,
		p_credential_validate_from,
		p_credential_validate_to,
		p_credential_address,
		p_credential_front_photo,
		p_credential_reverse_photo
	FROM obpm2.tenant_user_credentials tuc
	WHERE tuc.id = p_user_selected_credential_id LIMIT 1 ;
	
	#获取保安师的等级，并和等级证拼接到一起，以类似1；2；3；4；5的形式展示
	SELECT GROUP_CONCAT(DISTINCT(CASE mm.ITEM_DEGREE
	WHEN '一级' THEN
		1
	WHEN '二级' THEN
		2
END
) SEPARATOR ';') INTO p_security_master_grade
	FROM baibaodunflow.tlk_securitymastermanagement mm 
	JOIN obpm2.tenant_user_credentials uc ON mm.ITEM_DOCUMENTID = uc.number
	WHERE uc.tenant_user_id = p_tenant_user_id AND mm.DOMAINID = p_domain_id;
	
	IF p_security_master_grade IS NOT NULL AND p_security_master_grade!= '' THEN
		IF p_securityGrade IS NULL OR p_securityGrade = '' THEN
			SET p_securityGrade = p_security_master_grade;
		ELSE
			SET p_securityGrade = CONCAT(p_security_master_grade,';',p_securityGrade);
		END IF;
	END IF;
	
	
	
	INSERT INTO companyinfocollect.employees (
	DOMAINID,
	item_region_id,
	employee_id,
	user_id,
	profile_photo,
	name,
	id_type,
	id_no,
	phone,
	gender,
	ethnic_group,
	education,
	birth_date,
	political_status,
	military,
	occupation_type,
	hire_date,
	leave_date,
	employed,
	insure_status,
	check_status,
	auth_status,
	last_auth_time,
	auth_result,
	background_status,
	certificate_no,
	with_certificate,
	security_grade,
	security_grade_int,
	record_date,
	company_name,
	office_type,
	company_id,
	item_domain_id,
	department,
	department_id,
	supervise_office,
	supervise_office_code,
	att_site_name,
	att_site_id,
	att_site_supervise_office,
	att_site_supervise_office_code,
	in_att_site,
	LASTMODIFIED,
	last_sync_time,
	residence,
	height,
	birth_place,
	marriage,
	emergency_contact,
	emergency_phone,
	driver_license,
	work_years,
	medical_history,
	address,
	id_issue_date,
	id_expire_date,
	id_address,
	id_back_img,
	id_front_img,
	id_person_photo,
	salary
	)
	SELECT 
	t.domain_id, t.region_id, t.employee_id, t.user_id, t.profile_photo, t.name, t.id_type, 
	t.id_no, t.phone, t.gender, t.ethnic_group, t.education, t.birth_date, t.political_status, t.military, t.occupation_type, t.hire_date, t.leave_date,
	t.employed, t.insure_status, t.check_status, t.auth_status, t.last_auth_time, t.auth_result, t.background_status, t.certificate_no, t.with_certificate, t.security_grade, t.security_grade_int, t.record_date, t.company_name, 
	t.office_type, t.company_id, t.item_domain_id, t.department, t.department_id, t.supervise_office, t.supervise_office_code, t.att_site_name, t.att_site_id, t.att_site_supervise_office, t.att_site_supervise_office_code, t.in_att_site, t.last_modified, t.last_sync_time,
	t.residence,t.height,t.birthplace,t.marriage,t.emergency_contact,t.emergency_phone,t.driver_license,t.work_years,t.medical_history,t.address,
	t.id_issue_date,t.id_expire_date,t.id_address,t.id_back_image,t.id_front_image,t.id_person_photo,t.salary
	
	
	FROM (SELECT 
	#审核过的公司的职员才送入内网
	IF(p_org_approved_status='1',(CASE 
		WHEN p_org_supervise_region_code LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN p_org_supervise_region_code LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN p_org_supervise_region_code LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			'#'
		END),'#') AS domain_id,
	IFNULL(p_org_supervise_region_code,'#') AS region_id,
	p_employee_id AS employee_id,
	p_tenant_user_id AS user_id,
	IF(p_credential_head_photo IS NULL OR p_credential_head_photo = '','',concat( '[{"name":"","path":"', p_credential_head_photo, '"}]')) AS profile_photo,
	IFNULL(p_user_name,'') AS name,
	(CASE
	WHEN ISNULL(p_credential_type) THEN
		NULL
	WHEN  p_credential_type IN ('0','1','2','3','4') THEN
		p_credential_type
	ELSE
		5
END) AS id_type,
	p_credential_num AS id_no,
	p_user_phone AS phone,
	(CASE p_user_sex 
	WHEN 1 THEN 1
	WHEN 2 THEN 2
	ELSE 0
	END) AS gender,
	IF(p_user_nation = '', NULL,p_user_nation) AS ethnic_group,
	(CASE 
	WHEN p_user_education IN ('小学', '1') THEN
		2
	WHEN p_user_education IN ('初中','2') THEN
		2
	WHEN p_user_education IN ('高中','3') THEN
		3
	WHEN p_user_education IN ('中专','4')THEN
		4
	WHEN p_user_education IN ('大专','5')THEN
		5
	WHEN p_user_education IN ('本科','6') THEN
		6
	WHEN p_user_education IN ('硕士研究生','硕士研究生及以上', '7') THEN
		7
	WHEN p_user_education IN ('博士','博士后','8') THEN
		7
	ELSE
		0
	END) AS education,
	p_user_birthdate AS birth_date,
	(
	CASE
	WHEN p_user_political_status IN ('中共党员','01','1') THEN
		1
		WHEN p_user_political_status IN ('中共预备党员','02','2' )THEN
		2
		WHEN p_user_political_status IN ('共青团员','03', '3') THEN
		3
		WHEN p_user_political_status IN('民革会员','04','4') THEN
		4
		WHEN p_user_political_status IN ('民盟盟员','05','5') THEN
		5
		WHEN p_user_political_status IN ('民建会员','06','6' )THEN
		6
		WHEN p_user_political_status IN ('民进会员','07','7') THEN
		7
		WHEN p_user_political_status IN ('农工党党员','08','8') THEN
		8
		WHEN p_user_political_status IN ('致公党党员','09','9') THEN
		9
		WHEN p_user_political_status IN ('九三学社社员','10') THEN
		10
		WHEN p_user_political_status IN ('台盟盟员','11') THEN
		11
		WHEN p_user_political_status IN('无党派民主人士', '12') THEN
		12	
		WHEN p_user_political_status IN ('群众','13') THEN
		13	
	ELSE
		0
	END) AS political_status,
	(
	CASE 
	WHEN p_user_military_status IN ('未服','否','0') THEN
		1
	WHEN p_user_military_status IN ('服过','是','1') THEN
		2
	ELSE
		0
	END
	) AS military,
	IFNULL(p_employee_occupation_type,0) AS occupation_type,
	(CASE 
	WHEN p_employee_hired_date > NOW() THEN #正常入离职时间在timestamp时间范围之内。如果错误数据，则进行转换。
		CURRENT_TIMESTAMP
		WHEN p_employee_hired_date < '1970-01-01 00:00:00' THEN
		'1970-01-02'
	ELSE
		p_employee_hired_date
END ) AS hire_date,
(CASE 
	WHEN p_employee_leave_time > NOW() THEN
		CURRENT_TIMESTAMP
		WHEN p_employee_leave_time < '1970-01-01 00:00:00' THEN
		'1970-01-02'
	ELSE
		p_employee_leave_time
END ) AS leave_date,
	(CASE p_employee_status #0在职，1离职
	WHEN 0 THEN
		0
	ELSE
		1
	END) AS employed,
	IFNULL(p_employee_insure,0) AS insure_status,
	(CASE p_user_checked_status #1已核验，0未核验
	WHEN 1 THEN
		1
	ELSE
		0
	END) AS check_status,
	IFNULL(p_user_auth_status,0) AS auth_status,
	p_auth_time AS last_auth_time,
	p_auth_result AS auth_result,
	(CASE p_background_screening_status #1背筛正常，2失败，0未背筛
	WHEN 1 THEN
		1
	WHEN 2 THEN
		2
	ELSE 0
	END) AS background_status,
	get_cert_no_by_region(p_certNo,p_org_supervise_region_code) AS certificate_no,
	IF(get_cert_no_by_region(p_certNo,p_org_supervise_region_code) IS NULL,0,1) AS with_certificate,
	p_securityGrade AS security_grade,
	sum_of_powers(p_securityGrade, 0) AS security_grade_int,
	IFNULL(p_created_time,CURRENT_TIMESTAMP) AS record_date,
	IFNULL(p_org_name,'') AS company_name,
	p_org_institutional_code AS office_type,
	IFNULL(CONCAT(REPLACE(p_org_id,'--','__'),'--__2eoK1zvtxNarHlwwQg4'),'') AS company_id, #替换数据来源表的表单id，以便在本应用可以使用
	p_org_id AS item_domain_id,
	p_employee_department_name AS department,
	p_employee_department_id AS department_id,
	IFNULL(p_org_supervise_depart_name,'#') AS supervise_office,
	IFNULL(p_org_supervise_depart_id,'#') AS supervise_office_code,
	p_att_site_name AS att_site_name, 
	p_att_site_id AS att_site_id,
	p_att_site_supervise_office AS att_site_supervise_office,
	p_att_site_supervise_office_code AS att_site_supervise_office_code,
	IF(p_att_site_id IS NULL OR p_att_site_id = '',0,1) AS in_att_site,
	#IFNULL(p_employee_last_update_time,CURRENT_TIMESTAMP) AS last_modified,
	CURRENT_TIMESTAMP AS last_modified,
	CURRENT_TIMESTAMP AS last_sync_time,
	(CASE p_user_householder_type 
	WHEN '农村' THEN 1  
	WHEN '城镇' THEN 2 
	WHEN '乡镇' THEN 2 
	WHEN '城市' THEN 2 
	ELSE 0 END)  AS residence,
	IF(p_user_stature='',NULL,p_user_stature) AS height,
	p_user_native_place AS birthplace,
	(CASE p_user_maritial_status 
	WHEN '未婚' THEN 1  
	WHEN '已婚' THEN 2 
	WHEN '离异' THEN 3 
	WHEN '丧偶' THEN 4 
	WHEN '其他' THEN 5 
	ELSE 0 END) AS marriage,
	p_user_emgergency_contact AS emergency_contact,
	 p_user_emgergency_phone AS emergency_phone,
	 p_user_license_level AS driver_license,
	 p_user_work_year AS work_years,
		p_user_disease_history AS medical_history,
		p_user_now_address AS address,
		p_credential_validate_from AS id_issue_date,
		p_credential_validate_to AS id_expire_date,
		p_credential_address AS id_address,
		IF(p_credential_front_photo IS NULL OR p_credential_front_photo = '','',concat( '[{"name":"","path":"', p_credential_front_photo, '"}]')) AS id_back_image,
		IF(p_credential_reverse_photo IS NULL OR p_credential_reverse_photo = '','',concat( '[{"name":"","path":"', p_credential_reverse_photo, '"}]')) AS id_front_image,
		IF(p_credential_head_photo IS NULL OR p_credential_head_photo = '','',concat( '[{"name":"","path":"', p_credential_head_photo, '"}]')) AS id_person_photo,
		p_after_salary as salary
	) AS t
	
	ON DUPLICATE KEY UPDATE
	DOMAINID = t.domain_id,
	item_region_id = t.region_id,
	occupation_type = t.occupation_type,
	hire_date = t.hire_date,
	leave_date =  t.leave_date,
	employed = t.employed,
	insure_status = t.insure_status, 
	department = t.department,
	department_id = t.department_id,
  salary = t.salary,
	LASTMODIFIED = t.last_modified,
	last_sync_time = CURRENT_TIMESTAMP;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_occupation_type_0_to_1
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_occupation_type_0_to_1`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_occupation_type_0_to_1`(IN p_tenant_user_id VARCHAR(300),p_region_domainid VARCHAR(300))
BEGIN
	/**
		如果是一般职员转保安员，需要查看这个人目前在归集表中以一般职员身份是否有记录。
		如果有，则不管。
		如果没有，则要从智能人事找最后一条以一般职员身份的employee记录，然后插入一条新的
		*/
		DECLARE data_cnt INT DEFAULT 0; #查询该人员的记录个数
		DECLARE region_id VARCHAR(10) DEFAULT '#';#区域编码前两位
		
		SET region_id = (CASE 
							WHEN p_region_domainid = 'xduDIOSsi06qmfpig0A' THEN 
								'11%'
							WHEN p_region_domainid =  '__UDa4uPMdcOYgP7HETaf' THEN 
								'43%'
							WHEN p_region_domainid = 'qS6PXrA7' THEN 
								'64%'
							ELSE
								'#'
							END); 


		SELECT count(1) INTO data_cnt
		FROM companyinfocollect.employees e
		WHERE e.user_id = p_tenant_user_id AND e.occupation_type = 0 AND 
		e.DOMAINID = p_region_domainid;
		
	
	IF data_cnt = 0 THEN
		
		INSERT INTO companyinfocollect.employees (
	DOMAINID,
	item_region_id,
	employee_id,
	user_id,
	occupation_type,
	hire_date,
	leave_date,
	employed,
	insure_status,
	record_date,
	company_name,
	office_type,
	company_id,
	item_domain_id,
	department,
	department_id,
	supervise_office,
	supervise_office_code,
	LASTMODIFIED,
	last_sync_time,
	salary
	)
	SELECT 
	#审核过的公司的职员才送入内网
	p_region_domainid AS domain_id,
	o.supervise_region_code AS region_id,
	e.id AS employee_id,
	e.tenant_user_id AS user_id,
	0 AS occupation_type,
	(CASE 
	WHEN e.hired_date > NOW() THEN
		CURRENT_TIMESTAMP
		WHEN e.hired_date < '1970-01-01 00:00:00' THEN
		'1970-01-02'
	ELSE
		e.hired_date
END ) AS hire_date,
(CASE 
	WHEN e.leave_time > NOW() THEN
		CURRENT_TIMESTAMP
		WHEN e.leave_time < '1970-01-01 00:00:00' THEN
		'1970-01-02'
	ELSE
		e.leave_time
END ) AS leave_date,
	(CASE e.status # 0在职，1离职
	WHEN 0 THEN
		0
	ELSE
		1
	END) AS employed,
	IFNULL(e.insure,0) AS insure_status,
	IFNULL(e.created_time,CURRENT_TIMESTAMP) AS record_date,
	IFNULL(o.name,'') AS company_name,
	o.institutional_code AS office_type,
	IFNULL(CONCAT(REPLACE(o.id,'--','__'),'--__2eoK1zvtxNarHlwwQg4'),'') AS company_id,
	o.id AS item_domain_id,
	(select d.name FROM obpm2.tenant_departments d WHERE d.id = e.department_id) AS department,
	e.department_id AS department_id,
	IFNULL(o.supervise_depart_name,'#') AS supervise_office,
	IFNULL(o.supervise_depart_id,'#') AS supervise_office_code,
	CURRENT_TIMESTAMP AS last_modified,
	CURRENT_TIMESTAMP AS last_sync_time,
	e.salary as salary

	FROM obpm2.tenant_employees e 
	JOIN obpm2.tenant_organizations o ON e.organization_id = o.id
#只找有效的一条。
	WHERE e.tenant_user_id = p_tenant_user_id AND e.occupation_type = 0 AND o.institutional_code NOT IN ('0107','0108','0109') AND o.supervise_region_code like region_id AND o.approved_information_status = 1
	ORDER BY e.hired_date DESC
	LIMIT 1

ON DUPLICATE KEY UPDATE
LASTMODIFIED = CURRENT_TIMESTAMP; 

#这个人在归集后的表里肯定有以保安员身份的数据
	UPDATE companyinfocollect.employees e, (
	SELECT * FROM companyinfocollect.employees e1 
	WHERE e1.user_id = p_tenant_user_id AND e1.occupation_type = 1 AND e1.DOMAINID = p_region_domainid LIMIT 1
	) AS t
	
	SET 
	e.profile_photo = t.profile_photo,
	e.`name` = t.`name`,
	e.id_type = t.id_type,
	e.id_no = t.id_no,
	e.phone = t.phone,
	e.gender = t.gender,
	e.ethnic_group = t.ethnic_group,
	e.education = t.education,
	e.birth_date = t.birth_date,
	e.political_status = t.political_status,
	e.military = t.military,
	e.check_status = t.check_status,
	e.auth_status = t.auth_status,
	e.last_auth_time = t.last_auth_time,
	e.auth_result = t.auth_result,
	e.background_status = t.background_status,
	e.residence = t.residence,
	e.height = t.height,
	e.birth_place = t.birth_place,
	e.marriage = t.marriage,
	e.emergency_contact = t.emergency_contact,
	e.emergency_phone = t.emergency_phone,
	e.driver_license = t.driver_license,
	e.work_years = t.work_years,
	e.medical_history = t.medical_history,
	e.address = t.address,
	e.certificate_no = t.certificate_no,
	e.with_certificate = t.with_certificate ,
	e.security_grade = t.security_grade,
	e.security_grade_int = t.security_grade_int,
	e.id_issue_date = t.id_issue_date,
	e.id_expire_date = t.id_expire_date,
	e.id_address = t.id_address,
	e.id_back_img = t.id_back_img,
	e.id_front_img = t.id_front_img,
	e.id_person_photo = t.id_person_photo,
	e.LASTMODIFIED = CURRENT_TIMESTAMP
	
	WHERE e.user_id = p_tenant_user_id AND e.occupation_type = 0 AND 
		e.DOMAINID = p_region_domainid;

		END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_occupation_type_1_to_0
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_occupation_type_1_to_0`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_occupation_type_1_to_0`(IN p_tenant_user_id VARCHAR(300),p_region_domainid VARCHAR(300))
BEGIN
	/**目前这个保安员还需要找到他上一条的以保安员身份的就职记录，从归集后表查找:
		如果找不到该保安员的记录，则需要从智能人事去查找
		如果有，则不用管
	*/
		DECLARE data_cnt INT DEFAULT 0; #查询该人员的记录个数
		DECLARE region_id VARCHAR(10) DEFAULT '#';#区域编码前两位
		
		SET region_id = (CASE 
							WHEN p_region_domainid = 'xduDIOSsi06qmfpig0A' THEN 
								'11%'
							WHEN p_region_domainid =  '__UDa4uPMdcOYgP7HETaf' THEN 
								'43%'
							WHEN p_region_domainid = 'qS6PXrA7' THEN 
								'64%'
							ELSE
								'#'
							END); 


		SELECT count(1) INTO data_cnt
		FROM companyinfocollect.employees e
		WHERE e.user_id = p_tenant_user_id AND e.occupation_type = 1 AND 
		e.DOMAINID = p_region_domainid;
		
		IF data_cnt = 0 THEN
		INSERT INTO companyinfocollect.employees (
	DOMAINID,
	item_region_id,
	employee_id,
	user_id,
	occupation_type,
	hire_date,
	leave_date,
	employed,
	insure_status,
	record_date,
	company_name,
	office_type,
	company_id,
	item_domain_id,
	department,
	department_id,
	supervise_office,
	supervise_office_code,
	LASTMODIFIED,
	last_sync_time,
	salary
	)
	SELECT 
	#审核过的公司的职员才送入内网
	p_region_domainid AS domain_id,
	o.supervise_region_code AS region_id,
	e.id AS employee_id,
	e.tenant_user_id AS user_id,
	1 AS occupation_type,
	(CASE 
	WHEN e.hired_date > NOW() THEN
		CURRENT_TIMESTAMP
		WHEN e.hired_date < '1970-01-01 00:00:00' THEN
		'1970-01-02'
	ELSE
		e.hired_date
END ) AS hire_date,
(CASE 
	WHEN e.leave_time > NOW() THEN
		CURRENT_TIMESTAMP
		WHEN e.leave_time < '1970-01-01 00:00:00' THEN
		'1970-01-02'
	ELSE
		e.leave_time
END ) AS leave_date,
	(CASE e.status # 0在职，1离职
	WHEN 0 THEN
		0
	ELSE
		1
	END) AS employed,
	IFNULL(e.insure,0) AS insure_status,
	IFNULL(e.created_time,CURRENT_TIMESTAMP) AS record_date,
	IFNULL(o.name,'') AS company_name,
	o.institutional_code AS office_type,
	IFNULL(CONCAT(REPLACE(o.id,'--','__'),'--__2eoK1zvtxNarHlwwQg4'),'') AS company_id,
	o.id AS item_domain_id,
	(select d.name FROM obpm2.tenant_departments d WHERE d.id = e.department_id) AS department,
	e.department_id AS department_id,
	IFNULL(o.supervise_depart_name,'#') AS supervise_office,
	IFNULL(o.supervise_depart_id,'#') AS supervise_office_code,
	CURRENT_TIMESTAMP AS last_modified,
	CURRENT_TIMESTAMP AS last_sync_time,
	e.salary as salary

	FROM obpm2.tenant_employees e 
	JOIN obpm2.tenant_organizations o ON e.organization_id = o.id
#只找有效的一条。
	WHERE e.tenant_user_id = p_tenant_user_id AND e.occupation_type = 1 AND o.institutional_code NOT IN ('0107','0108','0109') AND o.supervise_region_code like region_id AND o.approved_information_status = 1
	ORDER BY e.hired_date DESC
	LIMIT 1

ON DUPLICATE KEY UPDATE
LASTMODIFIED = CURRENT_TIMESTAMP; 

#这个人在归集后的表里肯定有以一般职员身份的数据
	UPDATE companyinfocollect.employees e, (
	SELECT * FROM companyinfocollect.employees e1 
	WHERE e1.user_id = p_tenant_user_id AND e1.occupation_type = 0 AND e1.DOMAINID = p_region_domainid LIMIT 1
	) AS t
	
	SET 
	e.profile_photo = t.profile_photo,
	e.`name` = t.`name`,
	e.id_type = t.id_type,
	e.id_no = t.id_no,
	e.phone = t.phone,
	e.gender = t.gender,
	e.ethnic_group = t.ethnic_group,
	e.education = t.education,
	e.birth_date = t.birth_date,
	e.political_status = t.political_status,
	e.military = t.military,
	e.check_status = t.check_status,
	e.auth_status = t.auth_status,
	e.last_auth_time = t.last_auth_time,
	e.auth_result = t.auth_result,
	e.background_status = t.background_status,
	e.residence = t.residence,
	e.height = t.height,
	e.birth_place = t.birth_place,
	e.marriage = t.marriage,
	e.emergency_contact = t.emergency_contact,
	e.emergency_phone = t.emergency_phone,
	e.driver_license = t.driver_license,
	e.work_years = t.work_years,
	e.medical_history = t.medical_history,
	e.address = t.address,
	e.certificate_no = t.certificate_no,
	e.with_certificate = t.with_certificate ,
	e.security_grade = t.security_grade,
	e.security_grade_int = t.security_grade_int,
	e.id_issue_date = t.id_issue_date,
	e.id_expire_date = t.id_expire_date,
	e.id_address = t.id_address,
	e.id_back_img = t.id_back_img,
	e.id_front_img = t.id_front_img,
	e.id_person_photo = t.id_person_photo,
	e.LASTMODIFIED = CURRENT_TIMESTAMP
	
	WHERE e.user_id = p_tenant_user_id AND e.occupation_type = 1 AND 
		e.DOMAINID = p_region_domainid;
		
		END IF;
			
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_collect_update_sub_procedure
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_collect_update_sub_procedure`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_employee_info_collect_update_sub_procedure`(IN p_employee_id VARCHAR(300), p_employee_occupation_type INT, p_employee_hired_date DATETIME,p_employee_leave_time DATETIME, p_employee_status INT,p_employee_insure INT,p_employee_last_update_time DATETIME, p_employee_department_id VARCHAR(300),p_employee_department_name VARCHAR(200),
p_org_supervise_region_code VARCHAR(200),p_org_approved_status VARCHAR(200), p_after_salary VARCHAR(100))
BEGIN
	#更新职员
	UPDATE companyinfocollect.employees 
	SET 
	DOMAINID = IF(p_org_approved_status='1',(CASE 
		WHEN p_org_supervise_region_code LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN p_org_supervise_region_code LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN p_org_supervise_region_code LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			'#'
		END),'#'),
	item_region_id = 
			IFNULL(p_org_supervise_region_code,'#'),
	occupation_type = IFNULL(p_employee_occupation_type,0),
	hire_date = 
			(CASE 
			WHEN p_employee_hired_date > NOW() THEN
				CURRENT_TIMESTAMP
				WHEN p_employee_hired_date < '1970-01-01 00:00:00' THEN
				'1970-01-02'
			ELSE
				p_employee_hired_date
			END ),
	leave_date =  
			(CASE 
			WHEN p_employee_leave_time > NOW() THEN
				CURRENT_TIMESTAMP
				WHEN p_employee_leave_time < '1970-01-01 00:00:00' THEN
				'1970-01-02'
			ELSE
				p_employee_leave_time
			END ),
	employed = 
			(CASE p_employee_status #0在职，1离职
			WHEN 0 THEN
				0
			ELSE
				1
			END),
	insure_status = IFNULL(p_employee_insure,0), 
	department = p_employee_department_name,
	department_id = p_employee_department_id,
  salary = p_after_salary,
	#LASTMODIFIED = IFNULL(p_employee_last_update_time,CURRENT_TIMESTAMP),
	LASTMODIFIED = CURRENT_TIMESTAMP
	WHERE employee_id = p_employee_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_employee_info_delete_employee
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_employee_info_delete_employee`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_employee_info_delete_employee`(IN p_employee_id VARCHAR(300),p_org_supervise_region_code VARCHAR(200),p_employee_hired_date DATETIME)
BEGIN
	INSERT INTO companyinfocollect.data_deleted_events(
			database_name,
			table_name,
			primarykey_name,
			primarykey_value,
			DOMAINID,
			LASTMODIFIED,
			region_code,
			created_time,
			remark
			) VALUES(
			'companyinfocollect',
			'employees',
			'employee_id',
			p_employee_id,
			(CASE 
			WHEN p_org_supervise_region_code LIKE '11%' THEN 
				'xduDIOSsi06qmfpig0A'
			WHEN p_org_supervise_region_code LIKE '43%' THEN 
				'__UDa4uPMdcOYgP7HETaf'
			WHEN p_org_supervise_region_code LIKE '64%' THEN 
				'qS6PXrA7'
			ELSE
				'#'
			END),
			CURRENT_TIMESTAMP,
			p_org_supervise_region_code,
			CURRENT_TIMESTAMP,
			CONCAT('AND hire_date <= "',p_employee_hired_date,'"') 
			);

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_management_info_collect_by_company_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_management_info_collect_by_company_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_management_info_collect_by_company_v2`(IN p_company_id VARCHAR (300),o_Name VARCHAR(200))
BEGIN
	#当公司名称发生变化，触发更新
 UPDATE companyinfocollect.tlk_managementinfo i
	SET i.ITEM_COMPANY_NAME =  o_Name,i.LASTMODIFIED =NOW()
	where i.ITEM_COMPANY_ID = p_company_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_management_info_collect_by_employee
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_management_info_collect_by_employee`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_management_info_collect_by_employee`(IN p_employee_id VARCHAR(300),IN p_status VARCHAR(300))
BEGIN
	#Routine body goes here...
update companyinfocollect.tlk_managementinfo
set item_status = p_status
where item_employee_id = p_employee_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_management_info_collect_by_management_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_management_info_collect_by_management_v2`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_rd_management_info_collect_by_management_v2`(IN p_domain_id VARCHAR(300),o_ITEM_REGIONId VARCHAR(200),o_Name VARCHAR(200))
BEGIN
	#管理层信息归集
INSERT INTO companyinfocollect.tlk_managementinfo(
    ID,
		ITEM_NAME,
		ITEM_COMPANY_ID,
		ITEM_DOCUMENT_ID,
		ITEM_DOCUMENT_TYPE,
		ITEM_GENDER,
		ITEM_POST,
		ITEM_DEPARTMENT,
		ITEM_MILITARY,
		ITEM_GROUP,
		ITEM_RESIDENCE,
		ITEM_EDUCATION,
		ITEM_SKILL_LEVEL,
		ITEM_PHONE,
		ITEM_ADDRESS,
		ITEM_COMPANY_NAME,
		LASTMODIFIED,
		ITEM_DOMAIN_ID,
	  CREATED,
		DOMAINID,
		ITEM_IS_DELETE,
		last_sync_time,
		ITEM_JOBTYPEDETAIL,
		item_employee_id,
		item_status
)SELECT 
	c.ID,
	c.ITEM_NAME,
	c.DOMAINID,
	c.ITEM_DOCUMENTID,
	c.ITEM_DOCUMENTTYPE,
	c.ITEM_SEX,
	c.item_jobtype,
	c.ITEM_DEPTNAME,
	case c.ITEM_ISVETERAN  when 0 then '否' when 1 then '是' when '是' then '是' when '否' then '否' end as ISVETERAN,
	(SELECT itemvalue from baibaodunflow.sys_dictionary where itemKey = c.ITEM_NATION and codeType = 'nation' LIMIT 1) as ITEM_NATION,
  c.ITEM_HOUSEHOLD,
	(CASE 
	WHEN c.ITEM_EDUCATION IN ('小学', '1') THEN
		2
	WHEN c.ITEM_EDUCATION IN ('初中','2') THEN
		2
	WHEN c.ITEM_EDUCATION IN ('高中','3') THEN
		3
	WHEN c.ITEM_EDUCATION IN ('中专','4')THEN
		4
	WHEN c.ITEM_EDUCATION IN ('大专','5')THEN
		5
	WHEN c.ITEM_EDUCATION IN ('本科','6') THEN
		6
	WHEN c.ITEM_EDUCATION IN ('硕士研究生','硕士研究生及以上', '7') THEN
		7
	WHEN c.ITEM_EDUCATION IN ('博士','博士后','8') THEN
		7
	ELSE
		0
	END) as EDUCATION,
	c.ITEM_SKILLLEVEL,
	c.ITEM_PHONE,
	c.ITEM_ADDRESS,
	o_Name,
	#c.LASTMODIFIED,
	NOW(),
	c.ITEM_DOMAIN_ID,
	c.CREATED,
	(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END) AS domain_id,
	c.ITEM_IS_DELETE,
	NOW(),
	c.ITEM_JOBTYPEDETAIL,
	c.ITEM_USERTOLIST,
	0
 FROM baibaodunflow.tlk_managementlayer c left join obpm2.tenant_organizations a on c.DOMAINID = a.id
 WHERE c.ID = p_domain_id
  ON DUPLICATE KEY UPDATE  
    ID = c.ID,
		ITEM_NAME = c.ITEM_NAME,
		ITEM_COMPANY_ID = c.DOMAINID,
		ITEM_DOCUMENT_ID = c.ITEM_DOCUMENTID,
		ITEM_DOCUMENT_TYPE = c.ITEM_DOCUMENTTYPE,
		ITEM_GENDER = c.ITEM_SEX,
		ITEM_POST = c.item_jobtype,
		ITEM_DEPARTMENT = c.ITEM_DEPTNAME,
		ITEM_MILITARY = (case c.ITEM_ISVETERAN  when 0 then '否' when 1 then '是' when '是' then '是' when '否' then '否' end),
		ITEM_GROUP = (SELECT itemvalue from baibaodunflow.sys_dictionary where itemKey = c.ITEM_NATION and codeType = 'nation' LIMIT 1),
		ITEM_RESIDENCE =  c.ITEM_HOUSEHOLD,
		ITEM_EDUCATION = 	(CASE 
	WHEN c.ITEM_EDUCATION IN ('小学', '1') THEN
		2
	WHEN c.ITEM_EDUCATION IN ('初中','2') THEN
		2
	WHEN c.ITEM_EDUCATION IN ('高中','3') THEN
		3
	WHEN c.ITEM_EDUCATION IN ('中专','4')THEN
		4
	WHEN c.ITEM_EDUCATION IN ('大专','5')THEN
		5
	WHEN c.ITEM_EDUCATION IN ('本科','6') THEN
		6
	WHEN c.ITEM_EDUCATION IN ('硕士研究生','硕士研究生及以上', '7') THEN
		7
	WHEN c.ITEM_EDUCATION IN ('博士','博士后','8') THEN
		7
	ELSE
		0
	END),
		ITEM_SKILL_LEVEL = c.ITEM_SKILLLEVEL,
		ITEM_PHONE = c.ITEM_PHONE,
		ITEM_ADDRESS = c.ITEM_ADDRESS,
		ITEM_COMPANY_NAME = o_Name,
	  LASTMODIFIED = NOW(), #c.LASTMODIFIED,
	  CREATED = c.CREATED,
		ITEM_DOMAIN_ID = c.ITEM_DOMAIN_ID,
		ITEM_IS_DELETE = c.ITEM_IS_DELETE,
		DOMAINID = (CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),
		ITEM_JOBTYPEDETAIL = c.ITEM_JOBTYPEDETAIL,
		last_sync_time = now();
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_shareholder_info_collect_by_company_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_shareholder_info_collect_by_company_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_shareholder_info_collect_by_company_v2`(IN p_company_id VARCHAR (300),o_place_of_business_address VARCHAR(200),o_Name VARCHAR(200))
BEGIN
	#当公司名称发生变化，触发更新
update companyinfocollect.tlk_shareholderinfo i
	 set i.ITEM_COMPANY_NAME = o_Name,i.LASTMODIFIED =NOW()
	where i.ITEM_COMPANY_ID=p_company_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_shareholder_info_collect_by_shareholder_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_shareholder_info_collect_by_shareholder_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_shareholder_info_collect_by_shareholder_v2`(IN p_document_id VARCHAR(300),o_ITEM_REGIONId VARCHAR(200),o_place_of_business_address VARCHAR(200),o_Name VARCHAR(200))
BEGIN
	#股东信息归集
INSERT INTO companyinfocollect.tlk_shareholderinfo(
   ID,
	 ITEM_NAME,
	 ITEM_COMPANY_ID,
	 ITEM_DOCUMENT_ID,
	 ITEM_DOCUMENT_TYPE,
	 ITEM_TYPE,
	 ITEM_CAPITAL_PROPORTION,
	 ITEM_CAPITAL_CONTRIBUTION,
	 ITEM_PHONE,
	 ITEM_BUS_ADDRESS,
	 ITEM_GENDER,
	 ITEM_GROUP,
	 ITEM_ADDRESS,
	 ITEM_NATIONALITY,
	 item_household,
	 ITEM_COMPANY_NAME,
	 LASTMODIFIED,
	 ITEM_DOMAIN_ID,
	 CREATED,
	 DOMAINID,
	 ITEM_IS_DELETE,
	 last_sync_time
)SELECT 
   CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__16wbtuVlOKLmio734tW'),
	 c.ITEM_SHAREHOLDER,
	 c.DOMAINID,
	 c.ITEM_DOCUMENTID,
	 c.ITEM_DOCUMENTTYPE,
	 c.ITEM_SHAREHOLDERTYPE,
	 c.ITEM_STOCKPROPORTION,
	 c.ITEM_CAPITALCONTRIBUTION,
	 c.item_telephone,
	 c.ITEM_ADDRESS,
	 c.ITEM_SEX,
	 (SELECT itemvalue from baibaodunflow.sys_dictionary where itemKey = c.ITEM_NATION and codeType = 'nation' LIMIT 1) as ITEM_NATION,
   c.ITEM_ADDRESS,	 
   (SELECT itemvalue from baibaodunflow.sys_dictionary where itemKey = c.ITEM_NATIONALITY and codeType = 'nationality' LIMIT 1) as ITEM_NATIONALITY, 
	 c.ITEM_HOUSEHOLD,
	 o_Name,
	 #c.LASTMODIFIED,
	 NOW(),
	 c.ITEM_DOMAIN_ID,
	 c.CREATED,
	(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END) AS domain_id,
		c.ITEM_IS_DELETE,
		NOW()
 FROM baibaodunflow.tlk_shareholderinformation c 
 WHERE c.ID = p_document_id
 ON DUPLICATE KEY UPDATE  
   ID = CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__16wbtuVlOKLmio734tW'),
	 ITEM_NAME = c.ITEM_SHAREHOLDER,
	 ITEM_COMPANY_ID = c.DOMAINID,
	 ITEM_DOCUMENT_ID = c.ITEM_DOCUMENTID,
	 ITEM_DOCUMENT_TYPE = c.ITEM_DOCUMENTTYPE,
	 ITEM_TYPE = c.ITEM_SHAREHOLDERTYPE,
	 ITEM_CAPITAL_PROPORTION = c.ITEM_STOCKPROPORTION,
	 ITEM_CAPITAL_CONTRIBUTION = c.ITEM_CAPITALCONTRIBUTION,
	 ITEM_PHONE =  c.item_telephone,
	 ITEM_BUS_ADDRESS = c.ITEM_ADDRESS,
	 ITEM_GENDER = c.ITEM_SEX,
	 ITEM_GROUP = (SELECT itemvalue from baibaodunflow.sys_dictionary where itemKey = c.ITEM_NATION and codeType = 'nation' LIMIT 1),
	 ITEM_ADDRESS = c.ITEM_ADDRESS,
	 ITEM_NATIONALITY = (SELECT itemvalue from baibaodunflow.sys_dictionary where itemKey = c.ITEM_NATIONALITY and codeType = 'nationality' LIMIT 1),
	 item_household = c.ITEM_HOUSEHOLD,
	 ITEM_COMPANY_NAME = o_Name,
	 LASTMODIFIED = NOW(),#c.LASTMODIFIED,
	 CREATED = c.CREATED,
	 ITEM_DOMAIN_ID = c.ITEM_DOMAIN_ID,
	 ITEM_IS_DELETE = c.ITEM_IS_DELETE,
	 DOMAINID = (CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),
		last_sync_time = now();
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_subcompany_info_collect_by_companytel
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_subcompany_info_collect_by_companytel`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_subcompany_info_collect_by_companytel`(IN p_company_id VARCHAR (300),p_person_name VARCHAR (300))
BEGIN
	#当总公司的法人和电话发生变化时，触发更新
UPDATE companyinfocollect.sub_company i
SET i.item_group_legal_person =  p_person_name,i.LASTMODIFIED =NOW()
where i.item_group_company_id  = p_company_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_subcompany_info_collect_by_company_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_subcompany_info_collect_by_company_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_subcompany_info_collect_by_company_v2`(IN p_company_id VARCHAR (300),o_place_of_business_address VARCHAR(200),o_Name VARCHAR(200),o_place_of_register_province_name VARCHAR(200),o_place_of_register_address VARCHAR(200),o_place_of_business_province_name VARCHAR(200),o_group_company_phone VARCHAR(200))
BEGIN
	#当公司的注册地址和地区，经营地址和地区发生变化时，触发更新
UPDATE companyinfocollect.sub_company i
SET i.item_group_name =  o_Name,
i.item_group_register_address =  o_place_of_register_province_name,
i.item_group_register_addr_detail = o_place_of_register_address,
i.item_group_office_address = o_place_of_business_province_name,
i.item_group_office_addr_detail = o_place_of_business_address,
i.item_group_phone = o_group_company_phone,
i.LASTMODIFIED =NOW()
where i.item_group_company_id = p_company_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_subcompany_info_collect_by_subcompany_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_subcompany_info_collect_by_subcompany_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_subcompany_info_collect_by_subcompany_v2`(IN p_document_id VARCHAR (300),o_ITEM_REGIONId VARCHAR(200),o_place_of_business_address VARCHAR(200),o_Name VARCHAR(200),o_place_of_register_province_name VARCHAR(200),o_place_of_register_address VARCHAR(200),o_place_of_business_province_name VARCHAR(200),o_group_company_phone VARCHAR(200))
BEGIN
	#外地分子公司归集
INSERT INTO companyinfocollect.sub_company(
   ID,
	 item_group_name,
   item_group_legal_person,
   item_group_phone,
   item_group_register_address,
   item_group_register_addr_detail,
   item_group_office_address,
   item_group_office_addr_detail,
   item_is_sub,
   item_company_name,
   item_foundation_date,
   item_company_region,
   item_register_address,
   item_register_addr_detail,
   item_office_address,
   item_office_addr_detail,
   item_company_phone,
   item_legal_person,
   item_legal_person_phone,
   item_document_type,
   created,
   item_document_id,
   item_group_company_id,
	 ITEM_DOMAIN_ID,
   LASTMODIFIED,
	 DOMAINID,
	 ITEM_IS_DELETE,
	 ITEM_SECURITY_NUM,
	 last_sync_time
)SELECT 
    CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__rn71vIqC8w4NLHgXuVu'),
		o_Name,
		a.legal_person_name,
		o_group_company_phone,
		b.registerAddress,
		b.registerAddressDetail,
		b.officeAddress,
		b.officeAddressDetail,
		c.ITEM_COMPANY_TYPE,
		c.ITEM_COMPANY_NAME,
		c.ITEM_FOUNDATION_DATE,
		c.ITEM_SUPERVISORY_ADDRESS,
		c.ITEM_REGISTER_ADDRESS,
		c.ITEM_REGISTER_ADDRESS_DETAIL,
		c.ITEM_BUSINESS_ADDRESS,
		c.ITEM_BUSINESS_ADDRESS_DETAIL,
		c.ITEM_TELEPHONE,
		c.ITEM_LEGALPERSON,
		c.ITEM_LEGAL_TELEPHONE,
		c.ITEM_DOCUMENT_TYPE,
		c.CREATED,
		c.ITEM_DOCUMENT_ID,
		c.DOMAINID,
		c.DOMAINID,
		#c.LASTMODIFIED,
		NOW(),
		(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END) AS domain_id,
		c.ITEM_IS_DELETE,
		c.ITEM_SECURITY_NUM,
		NOW()
	FROM baibaodunflow.tlk_subcompanyinformation c left join obpm2.tenant_companies a on c.DOMAINID = a.id left join obpm2.v_company_info b 
	on c.DOMAINID = b.id
	where c.ID = p_document_id
	 ON DUPLICATE KEY UPDATE  
   ID = CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__rn71vIqC8w4NLHgXuVu'),
	 item_group_name = o_Name,
   item_group_legal_person = a.legal_person_name,
   item_group_phone = o_group_company_phone,
   item_group_register_address = b.registerAddress,
   item_group_register_addr_detail = b.registerAddressDetail,
   item_group_office_address = 	b.officeAddress,
   item_group_office_addr_detail = b.officeAddressDetail,
   item_is_sub = c.ITEM_COMPANY_TYPE,
   item_company_name = c.ITEM_COMPANY_NAME,
   item_foundation_date = c.ITEM_FOUNDATION_DATE,
   item_company_region = c.ITEM_SUPERVISORY_ADDRESS,
   item_register_address = c.ITEM_REGISTER_ADDRESS,
   item_register_addr_detail = c.ITEM_REGISTER_ADDRESS_DETAIL,
   item_office_address = c.ITEM_BUSINESS_ADDRESS,
   item_office_addr_detail = c.ITEM_BUSINESS_ADDRESS_DETAIL,
   item_company_phone =c.ITEM_TELEPHONE,
   item_legal_person = c.ITEM_LEGALPERSON,
   item_legal_person_phone = c.ITEM_LEGAL_TELEPHONE,
   item_document_type = c.ITEM_DOCUMENT_TYPE,
   created = c.CREATED,
   item_document_id = c.ITEM_DOCUMENT_ID,
   item_group_company_id = c.DOMAINID,
	 LASTMODIFIED = NOW(), #c.LASTMODIFIED,
	 ITEM_DOMAIN_ID = c.DOMAINID,
	 ITEM_IS_DELETE = c.ITEM_IS_DELETE,
	 ITEM_SECURITY_NUM = c.ITEM_SECURITY_NUM,
	 DOMAINID = (CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),
		last_sync_time = now();
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_vehicle_info_collect_by_company_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_vehicle_info_collect_by_company_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_vehicle_info_collect_by_company_v2`(IN p_company_id VARCHAR (300),o_name VARCHAR (300),o_company_police VARCHAR(200),o_company_police_code VARCHAR(200))
BEGIN
	#当公司的监管机构和编码发生变化时，触发更新
update companyinfocollect.tlk_vehicle c
set c.ITEM_domain_name = o_name,c.ITEM_company_police = o_company_police, c.ITEM_company_police_code = o_company_police_code,c.LASTMODIFIED =NOW()
where c.ITEM_domain_id = p_company_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_vehicle_info_collect_by_vehicle_v3
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_vehicle_info_collect_by_vehicle_v3`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_rd_vehicle_info_collect_by_vehicle_v3`(IN p_id VARCHAR(300), p_id_before VARCHAR(300),o_company_police VARCHAR(200),o_company_police_code VARCHAR(200),o_ITEM_REGIONId VARCHAR(200))
BEGIN
	#车辆信息归集，当原表删除数据时，需在归集后的表执行删除语句，并在data_deleted_events插入删除记录
	IF p_id is null then 
DELETE from tlk_vehicle where ITEM_RECORD_ID  = p_id_before;
INSERT INTO data_deleted_events(database_name,table_name,primarykey_name,primarykey_value,DOMAINID,LASTMODIFIED,region_code,created_time) VALUES('companyinfocollect','tlk_vehicle','ITEM_RECORD_ID',p_id_before,(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),NOW(),o_ITEM_REGIONId,NOW());
ELSE 
INSERT INTO companyinfocollect.tlk_vehicle(
    ITEM_company_name,
		ITEM_record_id,
		ITEM_vehicle_num,
		ITEM_vehicle_type,
		ITEM_vehicle_brand,
		ITEM_vehicle_model,
		ITEM_engine_num,
		ITEM_frame_num,
		ITEM_clstate,
		ITEM_vehicle_color,
		ITEM_vehicle_source,
		ITEM_fuel_type,
		ITEM_displacement,
		ITEM_reg_date,
		ITEM_regorg,
		ITEM_production_date,
		ITEM_buy_accout,
		ITEM_buy_date,
		ITEM_charge_person,
		ITEM_load_number,
		ITEM_annotation,
		ITEM_domain_id,
		ITEM_domain_name,
		ITEM_company_police,
		ITEM_company_police_code,
		LASTMODIFIED,
	  CREATED,
		ID,
		DOMAINID,
		last_sync_time
)SELECT 
	c.ITEM_COMPANYNAME,
	c.ID,
	c.ITEM_VEHICLENUM,
  c.ITEM_VEHICLETYPE,
	c.ITEM_VEHICLEBRAND,
	c.ITEM_VEHICLEMODEL,
	c.ITEM_ENGINENUM,
	c.ITEM_FRAMENUM,
	c.ITEM_CLSTATE,
	c.ITEM_VEHICLECOLOR,
	c.ITEM_VEHICLESOURCE,
	c.ITEM_FUELTYPE,
	c.ITEM_DISPLACEMENT,
	c.ITEM_REGDATE,
	c.ITEM_REGORG,
	c.ITEM_PRODUCTIONDATE,
	c.ITEM_BUYACCOUT,
	c.ITEM_BUYDATE,
	c.ITEM_CHARGEPERSON,
	c.ITEM_load_number,
	c.ITEM_ANNOTATION,
	c.DOMAINID,
	c.ITEM_DOMAINNAME,
	o_company_police,
	o_company_police_code,
	NOW(),#c.LASTMODIFIED,
	c.CREATED,
	CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__IMphNKsRrS67LF1Mvbb'),
	(CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END) AS domain_id,
		NOW()
 FROM baibaodunflow.tlk_vehicle c 
 WHERE c.ID = p_id
  ON DUPLICATE KEY UPDATE  
	  ID = CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__IMphNKsRrS67LF1Mvbb'),
    ITEM_company_name = c.ITEM_COMPANYNAME,
		ITEM_record_id = c.ID,
		ITEM_vehicle_num = c.ITEM_VEHICLENUM,
		ITEM_vehicle_type = c.ITEM_VEHICLETYPE,
		ITEM_vehicle_brand = c.ITEM_VEHICLEBRAND,
		ITEM_vehicle_model = c.ITEM_VEHICLEMODEL,
		ITEM_engine_num = c.ITEM_ENGINENUM,
		ITEM_frame_num = c.ITEM_FRAMENUM,
		ITEM_clstate = c.ITEM_CLSTATE,
		ITEM_vehicle_color = c.ITEM_VEHICLECOLOR,
		ITEM_vehicle_source = c.ITEM_VEHICLESOURCE,
		ITEM_fuel_type = c.ITEM_FUELTYPE,
		ITEM_displacement = c.ITEM_DISPLACEMENT,
		ITEM_reg_date = c.ITEM_REGDATE,
		ITEM_regorg = c.ITEM_REGORG,
		ITEM_production_date = c.ITEM_PRODUCTIONDATE,
		ITEM_buy_accout = c.ITEM_BUYACCOUT,
		ITEM_buy_date = c.ITEM_BUYDATE,
		ITEM_charge_person = c.ITEM_CHARGEPERSON ,
		ITEM_load_number = c.ITEM_load_number,
		ITEM_annotation = c.ITEM_ANNOTATION,
		ITEM_domain_id = c.DOMAINID,
		ITEM_domain_name = c.ITEM_DOMAINNAME,
		ITEM_company_police = o_company_police,
		ITEM_company_police_code = o_company_police_code,
		LASTMODIFIED = NOW(),#c.LASTMODIFIED,
	  CREATED = c.CREATED,
		DOMAINID = (CASE 
		WHEN o_ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o_ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o_ITEM_REGIONId LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			NULL
		END),
		last_sync_time = now();
		end if;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_attendance_site_person_info_by_credentials
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_attendance_site_person_info_by_credentials`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_credentials`(IN p_number VARCHAR(300),IN p_tenant_user_id VARCHAR(300),IN p_credential_type VARCHAR(300))
BEGIN 
	#更新驻勤人员身份证号
	IF p_credential_type = '0'
	THEN
		UPDATE companyinfocollect.tlk_attendance_site_person_info a
		SET 
		a.ITEM_DOCUMENT_ID = p_number,
		a.LASTMODIFIED = now(),
		a.`last_sync_time` = now()
		WHERE a.ITEM_USER_ID = p_tenant_user_id;
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_attendance_site_person_info_by_details
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_attendance_site_person_info_by_details`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_details`(IN p_zsbh VARCHAR(300),IN p_tenant_user_id VARCHAR(300),IN p_checked_status VARCHAR(300),IN p_certificateType VARCHAR(300), p_before_zsbh VARCHAR(300))
BEGIN
	# 更新驻勤人员持证状态、资格证编号
	IF(p_certificateType = '1' AND IFNULL(p_before_zsbh,'') != IFNULL(p_zsbh,''))
	THEN
		UPDATE companyinfocollect.tlk_attendance_site_person_info a
		SET a.ITEM_CERTIFICATE_STATUS = CASE WHEN (p_zsbh IS NULL OR p_zsbh = '') THEN '未持证' ELSE '已持证' END,
		a.ITEM_CERTIFICATE_NUMBER = CASE WHEN (p_zsbh IS NULL OR p_zsbh = '') THEN '无' ELSE p_zsbh END,
		a.LASTMODIFIED = now(),
		a.`last_sync_time` = now()
		WHERE a.ITEM_USER_ID = p_tenant_user_id;
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_attendance_site_person_info_by_employees
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_attendance_site_person_info_by_employees`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_employees`(IN p_id VARCHAR(300),IN p_hired_date VARCHAR(300),IN p_insure VARCHAR(300),IN p_status VARCHAR(300),IN p_departmentName VARCHAR(300))
BEGIN
	# 人员入职时间、投保状态、部门名称、就离职状态更新
	UPDATE companyinfocollect.tlk_attendance_site_person_info a
	SET a.ITEM_ENTRY_TIME = p_hired_date,
	a.ITEM_INSURANCE_STATUS = CASE WHEN p_insure = '0' THEN '否' ELSE '是' END,
	a.ITEM_DEPT_NAME = p_departmentName,
	a.LASTMODIFIED = now(),
	a.`last_sync_time` = now(),
	a.item_employment_status = CASE WHEN p_status = '0' THEN '1' ELSE '0' END
	WHERE a.ITEM_SECURITY_ID = p_id;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_attendance_site_person_info_by_id_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_attendance_site_person_info_by_id_v2`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_id_v2`(IN p_before_id VARCHAR(300),IN p_after_id VARCHAR(300),IN p_organization_supervise_region_code VARCHAR(300),IN p_user_id VARCHAR(300),IN p_insure VARCHAR(300),IN p_hired_date VARCHAR(300),IN p_authenticated_status VARCHAR(300),IN p_checked_status VARCHAR(300),IN p_status VARCHAR(300),IN p_FORMNAME VARCHAR(300),IN p_AUTHOR VARCHAR(300),IN p_AUTHORDEPTID VARCHAR(300),IN p_AUTHOR_DEPT_INDEX VARCHAR(300),IN p_AUTHOR_USER_INDEX VARCHAR(300),IN p_SUBFORMIDS VARCHAR(300),IN p_INITIATOR VARCHAR(300),IN p_ISTMP BIT(1),IN p_VERSIONS VARCHAR(300),IN p_LASTMODIFIER VARCHAR(300),IN p_ITEM_SECURITYNAME VARCHAR(300),IN p_ITEM_SECURITYID VARCHAR(300),IN p_ITEM_ATTENDANCESITEID VARCHAR(300),IN p_ITEM_DOCUMENTTYPE VARCHAR(300),IN p_ITEM_DOCUMENTID VARCHAR(300),IN p_ITEM_PHONE VARCHAR(300),IN p_ITEM_DEPTNAME VARCHAR(300),IN p_ITEM_JOBTYPE VARCHAR(300),IN p_ITEM_CREATEDUSER VARCHAR(300),IN p_ITEM_DOMAIN_ID VARCHAR(300),IN p_CREATED VARCHAR(300),IN p_zsbh VARCHAR(300))
BEGIN 
	#驻勤点人员详情信息归集
	IF p_after_id IS NULL #删除条件
	THEN 
		INSERT INTO `companyinfocollect`.data_deleted_events (
		`database_name`, 
		`table_name`, 
		`primarykey_name`, 
		`primarykey_value`, 
		`DOMAINID`, 
		`LASTMODIFIED`, 
		`region_code`, 
		`created_time`, 
		`remark`
		) VALUES(
		'companyinfocollect',
		'tlk_attendance_site_person_info',
		'ID',
		 p_before_id,
		 (CASE 
					WHEN p_organization_supervise_region_code LIKE '11%' THEN 
						'xduDIOSsi06qmfpig0A'
					WHEN p_organization_supervise_region_code LIKE '43%' THEN 
						'__UDa4uPMdcOYgP7HETaf'
					WHEN p_organization_supervise_region_code LIKE '64%' THEN 
						'qS6PXrA7'
					ELSE
						NULL
					END),
		 now(),
		 p_organization_supervise_region_code,
		 now(),
		 '');
		 
		 DELETE FROM `companyinfocollect`.`tlk_attendance_site_person_info` WHERE ITEM_RECORD_ID = p_before_id;
	ELSE
		INSERT INTO `companyinfocollect`.`tlk_attendance_site_person_info` (
				`LASTMODIFIED`,
				`FORMNAME`,
				`AUTHOR`,
				`AUTHORDEPTID`,
				`AUTHOR_DEPT_INDEX`,
				`AUTHOR_USER_INDEX`,
				`CREATED`,
				`FORMID`,
				`SUBFORMIDS`,
				`INITIATOR`,
				`ISTMP`,
				`VERSIONS`,
				`APPLICATIONID`,
				`LASTMODIFIER`,
				`DOMAINID`,
				`ITEM_SECURITY_NAME`,
				`ITEM_SECURITY_ID`,
				`ITEM_ATTENDANCE_SITE_ID`,
				`ITEM_DOCUMENT_TYPE`,
				`ITEM_DOCUMENT_ID`,
				`ITEM_PHONE`,
				`ITEM_DEPT_NAME`,
				`ITEM_JOB_TYPE`,
				`ITEM_IS_CERTIFIED`,
				`ITEM_INSURANCE_STATUS`,
				`ITEM_CREATED_USER`,
				`ITEM_DOMAIN_ID`,
				`ID`,
				`ITEM_RECORD_ID`,
				`ITEM_CREATED_TIME`,
				`ITEM_ENTRY_TIME`,
				`ITEM_CERTIFICATE_STATUS`,
				`ITEM_CERTIFICATE_NUMBER`,
				`ITEM_DOC_VERIFICATION`,
				`last_sync_time`,
				`item_employment_status`,
				`ITEM_USER_ID`
			) VALUES( 
				now(), #LASTMODIFIED
				p_FORMNAME, #FORMNAME
				p_AUTHOR, #AUTHOR
				p_AUTHORDEPTID, #AUTHORDEPTID
				p_AUTHOR_DEPT_INDEX, #AUTHOR_DEPT_INDEX
				p_AUTHOR_USER_INDEX, #AUTHOR_USER_INDEX
				now(), #CREATED 此处为这条数据创建的时间
				'__ZgEvBwBNMrRz3xT0OvU', #FORMID 固定为行业信息查询中的formId
				p_SUBFORMIDS, #SUBFORMIDS
				p_INITIATOR, #SUBFORMIDS
				p_ISTMP, #ISTMP
				p_VERSIONS, #VERSIONS
				'__DOoeSJp26wVqbyYYf50', #APPLICATIONID 固定为行业信息查询的应用ID
				p_LASTMODIFIER, #LASTMODIFIER
				(CASE 
					WHEN p_organization_supervise_region_code LIKE '11%' THEN 
						'xduDIOSsi06qmfpig0A'
					WHEN p_organization_supervise_region_code LIKE '43%' THEN 
						'__UDa4uPMdcOYgP7HETaf'
					WHEN p_organization_supervise_region_code LIKE '64%' THEN 
						'qS6PXrA7'
					ELSE
						NULL
					END), #根据参数p_organization_supervise_region_code 来区别地区
				p_ITEM_SECURITYNAME, #ITEM_SECURITY_NAME
				p_ITEM_SECURITYID, #ITEM_SECURITY_ID
				p_ITEM_ATTENDANCESITEID, #ITEM_ATTENDANCE_SITE_ID
				p_ITEM_DOCUMENTTYPE, #ITEM_DOCUMENT_TYPE
				p_ITEM_DOCUMENTID, #ITEM_DOCUMENT_ID
				p_ITEM_PHONE, #ITEM_PHONE
				p_ITEM_DEPTNAME, #ITEM_DEPT_NAME
				p_ITEM_JOBTYPE, #ITEM_JOB_TYPE
				CASE WHEN p_authenticated_status = '0' THEN '未认证' 
				WHEN p_authenticated_status = '1' THEN '认证中'
				WHEN p_authenticated_status = '2' THEN '认证失败'
				WHEN p_authenticated_status = '3' THEN '已认证' END, #ITEM_IS_CERTIFIED 认证状态
				CASE WHEN p_insure = '0' THEN '否' ELSE '是' END, #ITEM_INSURANCE_STATUS 投保状态
				p_ITEM_CREATEDUSER, #ITEM_CREATED_USER
				p_ITEM_DOMAIN_ID, #ITEM_DOMAIN_ID
				REPLACE(p_after_id,'__sgTQqGCJDqTyPf6vN1Q','__ZgEvBwBNMrRz3xT0OvU'), #ID 将原来应用的formId 替换成 行业信息查询中相应的formId
				p_after_id, #ITEM_RECORD_ID 来源表中的ID
				p_CREATED,#ITEM_CREATED_TIME 
				p_hired_date,#ITEM_ENTRY_TIME
				CASE WHEN (p_zsbh IS NULL OR p_zsbh = '') THEN '未持证' ELSE '已持证' END, #ITEM_CERTIFICATE_STATUS 持证状态
				CASE WHEN (p_zsbh IS NULL OR p_zsbh = '') THEN '无' ELSE p_zsbh END, #ITEM_CERTIFICATE_NUMBER 证书编号
-- 				CASE WHEN p_checked_status = '0' THEN '未提交'
-- 				WHEN p_checked_status = '1' THEN '已核验'
-- 				WHEN p_checked_status = '2' THEN '已提交' END, #ITEM_DOC_VERIFICATION 实名状态
				CASE WHEN p_checked_status = '1' THEN '已核验'
				ELSE '未核验' END, #ITEM_DOC_VERIFICATION 实名状态
				now(), #last_sync_time 数据更新时间
				CASE WHEN p_status = '0' THEN '1' ELSE '0' END, #`item_employment_status` 职员就离职状态
				CASE WHEN p_user_id = '' OR p_user_id IS NULL THEN '#' ELSE p_user_id END) #`ITEM_USER_ID`
-- 				FROM baibaodunflow.tlk_attendance_site_person_info a
-- 				LEFT JOIN obpm2.tenant_user_credential_details e ON e.idnum = a.ITEM_DOCUMENTID AND e.certificateType ='1'
-- 				WHERE a.ID = p_after_id #AND b.tenant_user_id IS NOT NULL
				ON DUPLICATE KEY UPDATE 
				`LASTMODIFIED` = now(),
				`FORMNAME` = p_FORMNAME,
				`AUTHOR` = p_AUTHOR,
				`AUTHORDEPTID` = p_AUTHORDEPTID,
				`AUTHOR_DEPT_INDEX` = p_AUTHOR_DEPT_INDEX,
				`AUTHOR_USER_INDEX` = p_AUTHOR_USER_INDEX,
				`CREATED` = p_CREATED,
				`FORMID` = '__ZgEvBwBNMrRz3xT0OvU',
				`SUBFORMIDS` = p_SUBFORMIDS,
				`INITIATOR` = p_INITIATOR,
				`ISTMP` = p_ISTMP,
				`VERSIONS` = p_VERSIONS,
				`APPLICATIONID` = '__DOoeSJp26wVqbyYYf50',
				`LASTMODIFIER` = p_LASTMODIFIER,
				`DOMAINID` = (CASE 
					WHEN p_organization_supervise_region_code LIKE '11%' THEN 
						'xduDIOSsi06qmfpig0A'
					WHEN p_organization_supervise_region_code LIKE '43%' THEN 
						'__UDa4uPMdcOYgP7HETaf'
					WHEN p_organization_supervise_region_code LIKE '64%' THEN 
						'qS6PXrA7'
					ELSE
						NULL
					END),
				`ITEM_SECURITY_NAME` = p_ITEM_SECURITYNAME,
				`ITEM_SECURITY_ID` = p_ITEM_SECURITYID,
				`ITEM_ATTENDANCE_SITE_ID` = p_ITEM_ATTENDANCESITEID,
				`ITEM_DOCUMENT_TYPE` = p_ITEM_DOCUMENTTYPE,
				`ITEM_DOCUMENT_ID` = p_ITEM_DOCUMENTID,
				`ITEM_PHONE` = p_ITEM_PHONE,
				`ITEM_DEPT_NAME` = p_ITEM_DEPTNAME,
				`ITEM_JOB_TYPE` = p_ITEM_JOBTYPE,
				`ITEM_IS_CERTIFIED` = CASE WHEN p_authenticated_status = '0' THEN '未认证' 
				WHEN p_authenticated_status = '1' THEN '认证中'
				WHEN p_authenticated_status = '2' THEN '认证失败'
				WHEN p_authenticated_status = '3' THEN '已认证' END,
				`ITEM_INSURANCE_STATUS` = CASE WHEN p_insure = '0' THEN '否' ELSE '是' END,
				`ITEM_CREATED_USER` = p_ITEM_CREATEDUSER,
				`ITEM_DOMAIN_ID` = p_ITEM_DOMAIN_ID,
				`ID` = REPLACE(p_after_id,'__sgTQqGCJDqTyPf6vN1Q','__ZgEvBwBNMrRz3xT0OvU'),
				`ITEM_RECORD_ID` = p_after_id,
				`ITEM_CREATED_TIME` = p_CREATED,
				`ITEM_ENTRY_TIME` = p_hired_date,
				`ITEM_CERTIFICATE_STATUS` = CASE WHEN (p_zsbh IS NULL OR p_zsbh = '') THEN '未持证' ELSE '已持证' END,
				`ITEM_CERTIFICATE_NUMBER` = CASE WHEN (p_zsbh IS NULL OR p_zsbh = '') THEN '无' ELSE p_zsbh END,
				`ITEM_DOC_VERIFICATION` = CASE WHEN p_checked_status = '1' THEN '已核验'
				ELSE '未核验' END,
				`last_sync_time` = now(),
				`item_employment_status` = CASE WHEN p_status = '0' THEN '1' ELSE '0' END,
				`ITEM_USER_ID` = CASE WHEN p_user_id = '' OR p_user_id IS NULL THEN '#' ELSE p_user_id END;
		END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_attendance_site_person_info_by_users
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_attendance_site_person_info_by_users`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_users`(IN p_id VARCHAR(300),IN p_checked_status VARCHAR(300),IN p_authenticated_status VARCHAR(300),IN p_telephone VARCHAR(300),IN p_checked_status_before VARCHAR(300),IN p_authenticated_status_before VARCHAR(300),IN p_telephone_before VARCHAR(300))
BEGIN
	# 驻勤人员核验状态、认证状态、联系号码更新
	IF (p_checked_status <> p_checked_status_before OR p_authenticated_status <> p_authenticated_status_before OR p_telephone <> p_telephone_before)
	THEN
	UPDATE companyinfocollect.tlk_attendance_site_person_info a
	SET a.ITEM_DOC_VERIFICATION = CASE WHEN p_checked_status = '1' THEN '已核验'
				ELSE '未核验' END,
	a.ITEM_IS_CERTIFIED = CASE WHEN p_authenticated_status = '0' THEN '未认证' 
				WHEN p_authenticated_status = '1' THEN '认证中'
				WHEN p_authenticated_status = '2' THEN '认证失败'
				WHEN p_authenticated_status = '3' THEN '已认证' END,
	a.item_phone = p_telephone,
	a.LASTMODIFIED = now(),
	a.`last_sync_time` = now()
	WHERE a.ITEM_USER_ID = p_id;
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_contract_to_enterprise_by_tlk_ContractToEnterprise
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_contract_to_enterprise_by_tlk_ContractToEnterprise`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_tlk_contract_to_enterprise_by_tlk_ContractToEnterprise`(IN p_before_id VARCHAR(300),IN idsStr VARCHAR(1000),p_supervise_region_code VARCHAR(300),p_supervise_depart_name VARCHAR(300),p_supervise_depart_id VARCHAR(300))
BEGIN
IF (idsStr IS NULL AND p_before_id IS NOT NULL) 
THEN
DELETE FROM `companyinfocollect`.`tlk_contract_to_enterprise` WHERE ITEM_CONTRACT_ID = p_before_id;
insert into data_deleted_events (database_name,table_name,primarykey_name,primarykey_value,LASTMODIFIED,created_time)
VALUES
('companyinfocollect','tlk_contract_to_enterprise','ITEM_CONTRACT_ID',p_before_id,NOW(),NOW());
ELSE
	INSERT INTO tlk_contract_to_enterprise (
		PARENT,
		LASTMODIFIED,
		FORMNAME,
		STATE,
		AUDITUSER,
		AUDITDATE,
		AUTHOR,
		AUTHORDEPTID,
		AUTHOR_DEPT_INDEX,
		AUTHOR_USER_INDEX,
		CREATED,
		FORMID,
		SUBFORMIDS,
		INITIATOR,
		ISTMP,
		VERSIONS,
		APPLICATIONID,
		STATEINT,
		STATELABEL,
		AUDITORNAMES,
		LASTFLOWOPERATION,
		LASTMODIFIER,
		DOMAINID,
		AUDITORLIST,
		COAUDITORLIST,
		STATELABELINFO,
		PREVAUDITNODE,
		PREVAUDITUSER,
		OPTIONITEM,
		SIGN,
		KINGGRIDSIGNATURE,
		ITEM_CONTRACT_NAME,
		ITEM_BELONG_ENTERPRISE,
		ITEM_PARTYA_NAME,
		ITEM_PARTYA_PHONE,
		ITEM_PARTYB_NAME,
		ITEM_PARTYB_PHONE,
		ITEM_PARTYC_NAME,
		ITEM_PARTYC_PHONE,
		ITEM_CONTRACT_START_DATE,
		ITEM_IS_NOT_END_DATE,
		ITEM_CONTRACT_END_DATE,
		ITEM_CONTRACT_STATE,
		ITEM_CONTRACT_FILE,
		ITEM_DOMAIN_ID,
		ITEM_SAAS_COMCONTRACTID,
		ID,
		ITEM_CONTRACT_ID,
    ITEM_COMPANY_POLICE,
    ITEM_LAST_MODIFIED,
		ITEM_company_police_code,
		last_sync_time
	) SELECT
		a.PARENT,
		#IFNULL(a.LASTMODIFIED,NOW()),
		NOW(),
		a.FORMNAME,
		a.STATE,
		a.AUDITUSER,
		a.AUDITDATE,
		a.AUTHOR,
		a.AUTHORDEPTID,
		a.AUTHOR_DEPT_INDEX,
		a.AUTHOR_USER_INDEX,
		a.CREATED,
		'__gGmejObNIkZ5p02BW0B' AS FORMID,
		a.SUBFORMIDS,
		a.INITIATOR,
		a.ISTMP,
		a.VERSIONS,
		'__DOoeSJp26wVqbyYYf50' AS APPLICATIONID,
		a.STATEINT,
		a.STATELABEL,
		a.AUDITORNAMES,
		a.LASTFLOWOPERATION,
		a.LASTMODIFIER,
		(CASE 
			WHEN p_supervise_region_code LIKE '11%' THEN 
				'xduDIOSsi06qmfpig0A'
			WHEN p_supervise_region_code LIKE '43%' THEN 
				'__UDa4uPMdcOYgP7HETaf'
			WHEN p_supervise_region_code LIKE '64%' THEN 
				'qS6PXrA7'
			ELSE
				NULL
			END) AS domain_id,
		a.AUDITORLIST,
		a.COAUDITORLIST,
		a.STATELABELINFO,
		a.PREVAUDITNODE,
		a.PREVAUDITUSER,
		a.OPTIONITEM,
		a.SIGN,
		a.KINGGRIDSIGNATURE,
		a.ITEM_CONTRACTNAME,
		a.ITEM_BELONGENTERPRISE,
		a.ITEM_PARTYANAME,
		a.ITEM_PARTYAPHONE,
		a.ITEM_PARTYBNAME,
		a.ITEM_PARTYBPHONE,
		a.ITEM_PARTYCNAME,
		a.ITEM_PARTYCPHONE,
		a.ITEM_CONTRACTSTARTDATE,
		a.ITEM_ISNOTENDDATE,
		a.ITEM_CONTRACTENDDATE,
		a.ITEM_CONTRACTSTATE,
		a.ITEM_CONTRACTFILE,
		a.ITEM_DOMAIN_ID,
		a.ITEM_SAAS_COMCONTRACTID,
		REPLACE (
			a.ID,
			'__rlthMmycTu5UZcanPbD',
			'__gGmejObNIkZ5p02BW0B'
		),
		a.ID,
    p_supervise_depart_name,
    a.LASTMODIFIED,
    p_supervise_depart_id,
		NOW()
	FROM
		baibaodunflow.tlk_ContractToEnterprise a 
#left join baibaodunflow.tlk_SetSupervise b on a.domainid = b.domainid left join organizationauth.tlk_DepartmentLevel c on c.id = b.item_registerPoliceAddressID
	WHERE
		a.id=idsStr ORDER BY a.id
 ON DUPLICATE KEY UPDATE LASTMODIFIED = now(),
		ITEM_CONTRACT_NAME = ITEM_CONTRACTNAME,
		ITEM_BELONG_ENTERPRISE = ITEM_BELONGENTERPRISE,
		ITEM_PARTYA_NAME = ITEM_PARTYANAME,
		ITEM_PARTYA_PHONE = ITEM_PARTYAPHONE,
		ITEM_PARTYB_NAME = ITEM_PARTYBNAME,
		ITEM_PARTYB_PHONE = ITEM_PARTYBPHONE,
		ITEM_PARTYC_NAME = ITEM_PARTYCNAME,
		ITEM_PARTYC_PHONE = ITEM_PARTYCPHONE,
		ITEM_CONTRACT_START_DATE = ITEM_CONTRACTSTARTDATE,
		ITEM_IS_NOT_END_DATE = ITEM_ISNOTENDDATE,
		ITEM_CONTRACT_END_DATE = ITEM_CONTRACTENDDATE,
		ITEM_CONTRACT_STATE = ITEM_CONTRACTSTATE,
		ITEM_CONTRACT_FILE = a.ITEM_CONTRACTFILE,
		ITEM_DOMAIN_ID = a.ITEM_DOMAIN_ID,
		ITEM_SAAS_COMCONTRACTID = a.ITEM_SAAS_COMCONTRACTID,
    ITEM_COMPANY_POLICE = p_supervise_depart_name,
    ITEM_LAST_MODIFIED = a.LASTMODIFIED,
		ITEM_company_police_code=p_supervise_depart_id,
		last_sync_time=NOW();
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_grade_certificate_by_user_credentials
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_grade_certificate_by_user_credentials`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_tlk_grade_certificate_by_user_credentials`(IN p_created_time VARCHAR(300),IN p_areaCode VARCHAR(300),IN p_companyId VARCHAR(300),IN p_id VARCHAR(300),IN p_xm VARCHAR(300),IN p_idnum VARCHAR(300),IN p_zsbh VARCHAR(300),IN p_companyName VARCHAR(300),IN p_trainName VARCHAR(300),IN p_trainStartTime VARCHAR(300),IN p_trainEndTime VARCHAR(300),IN p_appraisalTime VARCHAR(300),IN p_appraisalGrade VARCHAR(300),IN p_certificateType VARCHAR(300),IN p_active VARCHAR(300),IN p_mark VARCHAR(300),IN p_state VARCHAR(300),IN p_cancelReason VARCHAR(300),IN p_cancelDate VARCHAR(300),IN p_cancelOrgName VARCHAR(300),IN p_sex VARCHAR(300),IN p_phone VARCHAR(300))
BEGIN
	#等级证信息归集
	IF (p_certificateType='2' AND p_active='1' AND p_id IS NOT NULL)
	THEN 
		INSERT INTO companyinfocollect.tlk_grade_certificate (
			`LASTMODIFIED`,
			`CREATED`,
			`FORMID`,
			`APPLICATIONID`,
			`DOMAINID`,
			`ITEM_domain_id`,
			`ITEM_record_id`,
			`ITEM_security_name`,
			`ITEM_document_id`,
			`ITEM_certificate_no`,
			`ITEM_company_name_to_get`,
			`ITEM_cultivate_company`,
			`ID`,
			 #ITEM_EMPLOYMENT_STATUS,
			 ITEM_TRAINING_TIME_START,
			 ITEM_TRAINING_TIME_END,
			 ITEM_APPRAISAL_TIME,
			 ITEM_CERTIFICATE_LEVEL,
			 ITEM_CREATED_TIME,
			 ITEM_STATUS,
			 ITEM_CANCEL_REMARK,
			 ITEM_CANCEL_DATE,
			 ITEM_CANCEL_INSTITUTION,
			 item_sex,
			 item_phone
		)VALUES( 
			now(),#`LASTMODIFIED` 证书详情表数据的更新时间
			p_created_time,#`CREATED` 证书表数据的创建时间
			'__TnCvHMSNYTBlxqU5uc0',#`FORMID` 固定为行业信息查询页面表单的formid
			'__DOoeSJp26wVqbyYYf50',#`APPLICATIONID` 固定为行业信息应用id
			(CASE 
				WHEN p_areaCode LIKE '11%' THEN 
					'xduDIOSsi06qmfpig0A'
				WHEN p_areaCode LIKE '43%' THEN 
					'__UDa4uPMdcOYgP7HETaf'
				WHEN p_areaCode LIKE '64%' THEN 
					'qS6PXrA7'
				ELSE
					NULL
				END), #`DOMAINID` 根据证书详情表中地区码区别地区
				p_companyId,#`ITEM_domain_id` 证书详情表中的公司id
				p_id,#`ITEM_record_id` 
				p_xm,#`ITEM_security_name`
				p_idnum,#ITEM_document_id 
				p_zsbh,#ITEM_certificate_no 证书详情表中的证书编号
				p_companyName,#`ITEM_company_name_to_get` 获取证书的公司名称
				p_trainName,#`ITEM_cultivate_company`, 训练公司的名称
				(CASE 
				WHEN p_id LIKE '%__jlNfVcjppSliMYH6FkJ%' THEN 
					REPLACE(p_id,'__jlNfVcjppSliMYH6FkJ','__TnCvHMSNYTBlxqU5uc0')
				ELSE
					CONCAT(p_id,'--__TnCvHMSNYTBlxqU5uc0')
				END),#`ID`
				#CASE WHEN c.`status` = '0' THEN '在职' ELSE '离职' END,#ITEM_EMPLOYMENT_STATUS
				p_trainStartTime,# ITEM_TRAINING_TIME_START
				p_trainEndTime,# ITEM_TRAINING_TIME_END
				p_appraisalTime,# ITEM_APPRAISAL_TIME
				p_appraisalGrade,# ITEM_CERTIFICATE_LEVEL
				p_created_time, #ITEM_CREATED_TIME
				p_state,
				p_cancelReason,
				p_cancelDate,
				p_cancelOrgName,
				p_sex,
				p_phone
			)
			ON DUPLICATE KEY UPDATE 
			`LASTMODIFIED` = now(),
			`CREATED` = p_created_time,
			`FORMID` = '__TnCvHMSNYTBlxqU5uc0',
			`APPLICATIONID` = '__DOoeSJp26wVqbyYYf50',
			`DOMAINID` = (CASE 
				WHEN p_areaCode LIKE '11%' THEN 
					'xduDIOSsi06qmfpig0A'
				WHEN p_areaCode LIKE '43%' THEN 
					'__UDa4uPMdcOYgP7HETaf'
				WHEN p_areaCode LIKE '64%' THEN 
					'qS6PXrA7'
				ELSE
					NULL
				END),
			`ITEM_domain_id` = p_companyId,
			`ITEM_record_id` = p_id,
			`ITEM_security_name` = p_xm,
			`ITEM_document_id` = p_idnum,
			`ITEM_certificate_no` = p_zsbh,
			`ITEM_company_name_to_get` = p_companyName,
			`ITEM_cultivate_company` = p_trainName,
			`ID` = (CASE 
				WHEN p_id LIKE '%__jlNfVcjppSliMYH6FkJ' THEN 
					REPLACE(p_id,'__jlNfVcjppSliMYH6FkJ','__TnCvHMSNYTBlxqU5uc0')
				ELSE
					CONCAT(p_id,'--__TnCvHMSNYTBlxqU5uc0')
				END),
			 #ITEM_EMPLOYMENT_STATUS = CASE WHEN c.`status` = '0' THEN '在职' ELSE '离职' END,
			 ITEM_TRAINING_TIME_START = p_trainStartTime,
			 ITEM_TRAINING_TIME_END = p_trainEndTime,
			 ITEM_APPRAISAL_TIME = p_appraisalTime,
			 ITEM_CERTIFICATE_LEVEL = p_appraisalGrade,
			 ITEM_CREATED_TIME = p_created_time,
			 ITEM_STATUS = p_state,
			 ITEM_CANCEL_REMARK = p_cancelReason,
			 ITEM_CANCEL_DATE = p_cancelDate,
			 item_sex = p_sex,
			 item_phone = p_phone,
			 ITEM_CANCEL_INSTITUTION = p_cancelOrgName;
		 END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_qualification_certificate_by_user_credentials
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_qualification_certificate_by_user_credentials`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_tlk_qualification_certificate_by_user_credentials`(IN p_created_time VARCHAR(300),IN p_areaCode VARCHAR(300),IN p_companyId VARCHAR(300),IN p_id VARCHAR(300),IN p_isOldData VARCHAR(300),IN p_xm VARCHAR(300),IN p_idnum VARCHAR(300),IN p_sex VARCHAR(300),IN p_phone VARchAR(300),IN p_state VARCHAR(300),IN p_cancelReason VARCHAR(300),IN p_zsbh VARCHAR(300),IN p_cancelDate VARCHAR(300),IN p_fzjgmc VARCHAR(300),IN p_fzrq VARCHAR(300),IN p_companyName VARCHAR(300),IN p_trainName VARCHAR(300),IN p_cancelOrgName VARCHAR(300),IN p_certificateType VARCHAR(300),IN p_active VARCHAR(300),IN p_mark VARCHAR(300))
BEGIN
  # 归集资格证信息
	IF(p_certificateType='1' AND p_active = '1' AND p_id IS NOT NULL)
	THEN
		INSERT INTO companyinfocollect.tlk_qualification_certificate (
			`LASTMODIFIED`,
			`CREATED`,
			`FORMID`,
			`APPLICATIONID`,
			`DOMAINID`,
			`ITEM_domain_id`,
			`ITEM_record_id`,
			#`ITEM_employment_status`,
			`ITEM_is_history`,
			`ITEM_security_name`,
			`ITEM_document_id`,
			`ITEM_sex`,
			`ITEM_phone`,
			`ITEM_status`,
			`ITEM_cancel_remark`,
			`ITEM_certificate_no`,
			`ITEM_cancel_date`,
			`ITEM_certificate_from`,
			`ITEM_certificate_date`,
			`ITEM_company_name_to_get`,
			`ITEM_cultivate_company`,
			`ID`,
			 ITEM_CANCEL_INSTITUTION
		)VALUES( 
			now(),#`LASTMODIFIED`
			p_created_time,#`CREATED`
			'__GCpKdHqjqaN6yphC9xA',#`FORMID`
			'__DOoeSJp26wVqbyYYf50',#`APPLICATIONID`
			(CASE 
				WHEN p_areaCode LIKE '11%' THEN 
					'xduDIOSsi06qmfpig0A'
				WHEN p_areaCode LIKE '43%' THEN 
					'__UDa4uPMdcOYgP7HETaf'
				WHEN p_areaCode LIKE '64%' THEN 
					'qS6PXrA7'
				ELSE
					NULL
				END), #`DOMAINID`
				p_companyId,#`ITEM_domain_id`
				p_id,#`ITEM_record_id`
				#c.`status`,#`ITEM_employment_status`
				CASE WHEN p_isOldData = '1' THEN p_isOldData ELSE '0' END,#`ITEM_is_history`
				p_xm,#`ITEM_security_name`
				p_idnum,#ITEM_document_id
				p_sex,#ITEM_sex
				p_phone,#`ITEM_phone`
				p_state,#ITEM_status
				p_cancelReason,#ITEM_cancel_remark
				p_zsbh,#ITEM_certificate_no
				p_cancelDate,#ITEM_cancel_date
				p_fzjgmc,#ITEM_certificate_from
				p_fzrq,#ITEM_certificate_date
				p_companyName,#`ITEM_company_name_to_get`
				p_trainName,#`ITEM_cultivate_company`,
				(CASE 
				WHEN p_id LIKE '%__jlNfVcjppSliMYH6FkJ' THEN 
					REPLACE(p_id,'__jlNfVcjppSliMYH6FkJ','__GCpKdHqjqaN6yphC9xA')
				ELSE
					CONCAT(p_id,'--__GCpKdHqjqaN6yphC9xA')
				END),#`ID`
				p_cancelOrgName #ITEM_CANCEL_INSTITUTION
			)
			ON DUPLICATE KEY UPDATE 
			`LASTMODIFIED` = now(),
			`CREATED` = p_created_time,
			`ITEM_domain_id` = p_companyId,
			`ITEM_record_id`= p_id,
			#`ITEM_employment_status` = c.`status`,
			`ITEM_is_history` = CASE WHEN p_isOldData = '1' THEN p_isOldData ELSE '0' END,
			`ITEM_security_name` = p_xm,
			`ITEM_document_id` = p_idnum,
			`ITEM_sex` = p_sex,
			`ITEM_phone` = p_phone,
			`ITEM_status` = p_state,
			`ITEM_cancel_remark` = p_cancelReason,
			`ITEM_certificate_no` = p_zsbh,
			`ITEM_cancel_date` = p_cancelDate,
			`ITEM_certificate_from` = p_fzjgmc,
			`ITEM_certificate_date` = p_fzrq,
			`ITEM_company_name_to_get` = p_companyName,
			`ITEM_cultivate_company` = p_trainName,
			 ITEM_CANCEL_INSTITUTION = p_cancelOrgName,
			 DOMAINID = (CASE 
				WHEN p_areaCode LIKE '11%' THEN 
					'xduDIOSsi06qmfpig0A'
				WHEN p_areaCode LIKE '43%' THEN 
					'__UDa4uPMdcOYgP7HETaf'
				WHEN p_areaCode LIKE '64%' THEN 
					'qS6PXrA7'
				ELSE
					NULL
				END);
		 END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc_sync_tlk_qualification_grade_certificate_by_details
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_tlk_qualification_grade_certificate_by_details`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `proc_sync_tlk_qualification_grade_certificate_by_details`(IN p_id VARCHAR(300),IN p_companyName VARCHAR(300),IN p_trainName VARcHAR(300), IN p_certificateType VARCHAR(300),IN p_mark VARCHAR(300))
BEGIN
	# 资格证等级证信息中的保安公司和培训公司名称变更更新
	IF (p_certificateType = '1' AND p_id IS NOT NULL)
	THEN 
		UPDATE companyinfocollect.tlk_qualification_certificate a
		SET a.ITEM_COMPANY_NAME_TO_GET = p_companyName,
				a.ITEM_CULTIVATE_COMPANY = p_trainName,
				a.LASTMODIFIED = now()
		WHERE a.ITEM_record_id = p_id;
	ELSEIF (p_certificateType = '2' AND p_id IS NOT NULL)
	THEN 
		UPDATE companyinfocollect.tlk_grade_certificate a
		SET a.ITEM_COMPANY_NAME_TO_GET = p_companyName,
				a.ITEM_CULTIVATE_COMPANY = p_trainName,
				a.LASTMODIFIED = now()
		WHERE a.ITEM_record_id = p_id;
	END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for update_lastmodified
-- ----------------------------
DROP PROCEDURE IF EXISTS `update_lastmodified`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `update_lastmodified`()
BEGIN
	DECLARE t INT DEFAULT 0;
	DECLARE off_set INT DEFAULT 0;
	
	while t < 49 DO
		SET off_set = t*100000;
		UPDATE companyinfocollect.employed_events 
		SET LASTMODIFIED = DATE_ADD('2018-05-01', INTERVAL t DAY)
		WHERE id BETWEEN off_set AND (off_set+100000);
		SET t = t+1;
	
END WHILE;


END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for update_lastmodified_limit
-- ----------------------------
DROP PROCEDURE IF EXISTS `update_lastmodified_limit`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` PROCEDURE `update_lastmodified_limit`()
BEGIN
	DECLARE t INT DEFAULT 0;
	DECLARE off_set INT DEFAULT 0;
	DECLARE result TEXT DEFAULT '';
	
	while t < 3 DO
		SET off_set = t*1;
		select CONCAT("UPDATE companyinfocollect.tlk_qualification_certificate 
		SET LASTMODIFIED = DATE_ADD('2023-06-20', INTERVAL ",t," DAY) WHERE id='",id,"'") from companyinfocollect.tlk_qualification_certificate LIMIT off_set,1;
		SET t = t+1;
	
END WHILE;


END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for business_scope_mapping
-- ----------------------------
DROP FUNCTION IF EXISTS `business_scope_mapping`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `business_scope_mapping`(str VARCHAR(100)) RETURNS varchar(100) CHARSET utf8
BEGIN
	# 运营数据管理中组织管理里面经营范围key-value映射
	DECLARE i INT DEFAULT 1;
	DECLARE tempStr VARCHAR(100);
	DECLARE resultStr VARCHAR(100);
	WHILE ((LENGTH(str)+1)/2)>=i DO
		SET tempStr = SUBSTRING_INDEX(SUBSTRING_INDEX(str,';',i),';',-1);
		IF tempStr = '1' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'门卫;'); END IF;
		 IF tempStr = '2' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'巡逻;'); END IF;
		 IF tempStr = '3' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'守护;'); END IF;
		 IF tempStr = '4' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'押运;'); END IF;
		 IF tempStr = '5' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'随身护卫;'); END IF;
		 IF tempStr = '6' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'安全检查;'); END IF;
		 IF tempStr = '7' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'区域秩序维护;'); END IF;
		 IF tempStr = '8' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'安全风险评估;'); END IF;
		 IF tempStr = '9' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'安全技术防范;'); END IF;
		 IF tempStr = '10' THEN SET resultStr = CONCAT(IFNULL(resultStr,''),'其他;'); END IF;
		SET i = i+1;
	END WHILE;
	RETURN resultStr;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for business_scope_transfer
-- ----------------------------
DROP FUNCTION IF EXISTS `business_scope_transfer`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `business_scope_transfer`(scope_str VARCHAR(50)) RETURNS varchar(50) CHARSET utf8
BEGIN
# 对01;02;3;05;11;12这种字符串统一转化成 1;2;3;4;5的形式。有效值是1-10
	DECLARE result VARCHAR(50) DEFAULT '';
	DECLARE i INT DEFAULT 1;
	DECLARE num_str VARCHAR(10);
	DECLARE has_invalid_value TINYINT DEFAULT 0;
	
	WHILE i <= LENGTH(scope_str) AND has_invalid_value = 0 DO
   IF SUBSTRING(scope_str,i,1) = ';' THEN
	   SET i = i + 1;
	 ELSE
		SET num_str = TRIM(SUBSTRING_INDEX(SUBSTRING(scope_str,i),';',1));
			IF CAST(num_str AS UNSIGNED) IS NOT NULL AND CAST(num_str AS UNSIGNED) != 0 AND CAST(num_str AS UNSIGNED) <= 10 THEN 
				IF (result = '') THEN
				 SET result = CONCAT(CAST(num_str AS UNSIGNED),'');
				ELSE
				 SET result = CONCAT(result,';',CAST(num_str AS UNSIGNED));
				END IF;
			END IF;
		SET i = i + LENGTH(num_str)	+ 1;
		END IF;
  END WHILE;
	/**IF(has_invalid_value = 1) THEN
		SET result = '';
	END IF;*/
	RETURN result;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for concat_security_grade
-- ----------------------------
DROP FUNCTION IF EXISTS `concat_security_grade`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `concat_security_grade`(security_master_grade VARCHAR(20), security_grade VARCHAR(20)) RETURNS varchar(20) CHARSET utf8
BEGIN
#如果保安员等级证发生了变更，则更新登记证的部分，保留原保安师等级
#比如原来值为1;4，1表示高级保安师，4为中级保安员。现在获得了高级保安员等级证（编码为3）；则原来的值变更为1;3;4
	DECLARE result VARCHAR(20) DEFAULT NULL;
	IF locate('1',security_master_grade) > 0 THEN 
		 SET result = '1';
	END IF;
	IF locate('2',security_master_grade) > 0 THEN 
		IF ISNULL(result) THEN SET result = '2';
		ELSE SET result = CONCAT(result,';','2');
		END IF;
	END IF;
	
	IF ISNULL(result) THEN 
		SET result = security_grade;
	ELSEIF security_grade IS NOT NULL AND security_grade!='' THEN
		SET result = CONCAT(result,';',security_grade);
	END IF;
		
	RETURN result;
	
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for concat_security_master_grade
-- ----------------------------
DROP FUNCTION IF EXISTS `concat_security_master_grade`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `concat_security_master_grade`(cur_grads VARCHAR(20), security_master_grade VARCHAR(20)) RETURNS varchar(20) CHARSET utf8
BEGIN
#如果保安师信息发生了变更，则更新保安师等级的部分，保留原保安员等级信息
#比如原来值为2;3;4，2表示保安师，4为中级保安员。3 高级保安员；现在保安师新增了高级保安师信息（编码为1）,则原来的值变更为1;2;3;4
	DECLARE result VARCHAR(20) DEFAULT NULL;
	IF locate('3',cur_grads) > 0 THEN 
		 SET result = '3';
	END IF;
	IF locate('4',cur_grads) > 0 THEN 
		IF ISNULL(result) THEN SET result = '4';
		ELSE SET result = CONCAT(result,';','4');
		END IF;
	END IF;
	IF locate('5',cur_grads) > 0 THEN 
		IF ISNULL(result) THEN SET result = '5';
		ELSE SET result = CONCAT(result,';','5');
		END IF;
	END IF;
	
	IF ISNULL(result) THEN 
		SET result = security_master_grade;
	ELSEIF security_master_grade IS NOT NULL AND security_master_grade!='' THEN
		SET result = CONCAT(security_master_grade,';',result);
	END IF;
		
	RETURN result;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for concat_strings_security_grad
-- ----------------------------
DROP FUNCTION IF EXISTS `concat_strings_security_grad`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `concat_strings_security_grad`(p_securityGrade VARCHAR(20), p_security_master_grade VARCHAR(20)) RETURNS varchar(20) CHARSET utf8
BEGIN
	#Routine body goes here...
	IF p_security_master_grade IS NOT NULL AND p_security_master_grade!= '' THEN
		IF p_securityGrade IS NULL OR p_securityGrade = '' THEN
			SET p_securityGrade = p_security_master_grade;
		ELSE
			SET p_securityGrade = CONCAT(p_security_master_grade,';',p_securityGrade);
		END IF;
	END IF;
	RETURN p_securityGrade;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for concat_unique_strings
-- ----------------------------
DROP FUNCTION IF EXISTS `concat_unique_strings`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `concat_unique_strings`(original_string VARCHAR(300), new_string VARCHAR(300),delimiter VARCHAR(10)) RETURNS varchar(300) CHARSET utf8
BEGIN
	DECLARE result VARCHAR(300) DEFAULT NULL;
	IF original_string IS NULL OR original_string = '' THEN
		SET result = IF(new_string='',NULL,new_string);
	ELSEIF new_string is null OR new_string = '' OR SUBSTRING_INDEX(original_string,';',1) = new_string OR LOCATE(CONCAT(';',new_string,';'),original_string) > 0 OR SUBSTRING_INDEX(original_string,';',-1) = new_string THEN
		SET result =  original_string;
	ELSE
		SET result = CONCAT(original_string,delimiter,new_string);
	END IF;
	RETURN result;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for get_cert_no_by_region
-- ----------------------------
DROP FUNCTION IF EXISTS `get_cert_no_by_region`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `get_cert_no_by_region`(cert_no_str VARCHAR(300), supervise_region_code VARCHAR(100)) RETURNS varchar(300) CHARSET utf8
BEGIN
#cert_no_str,人员所持有的所有证书，证书以$区域编码$证书编号的形式表示。不同证书之间以';'进行拼接。根据区域编码截取该区域的所有证书编号。
#同样适用于资格证

	#地区的起始编码，北京：$11,湖南：$43,宁夏：$64
	DECLARE cert_start_char VARCHAR(10) DEFAULT NULL; 
	
	#所有符合条件的资格证号用分号(;)拼接结果
	DECLARE result VARCHAR(300) DEFAULT NULL; 
	
	#证书起始位置
	DECLARE start_pos INT DEFAULT 1; 
	
	#证书截至位置
	DECLARE end_pos INT DEFAULT 0; 
	
	#当前证书包含区域编码的位置
	DECLARE curr_pos INT DEFAULT 0;
	
	#当前证书编号
	DECLARE curr_cert_no VARCHAR(200) DEFAULT NULL;
	#当前证书字符串：区域编码+证书
	DECLARE curr_cert_no_str VARCHAR(200) DEFAULT NULL;
	
	#所有证书字符串的长度
	DECLARE cert_str_length INT DEFAULT 0;
	
	
	SET cert_str_length = LENGTH(cert_no_str);
	SET cert_start_char = (CASE 
		WHEN supervise_region_code LIKE '11%' THEN 
			'11'
		WHEN supervise_region_code LIKE '43%' THEN 
			'43'
		WHEN supervise_region_code LIKE '64%' THEN 
			'64'
		ELSE
			'#'
		END);
		
		#没有监管的地区，直接认定为持证。
		IF cert_start_char = '#' THEN
		  SET result = IF(cert_no_str IS NULL OR cert_no_str='' OR REPLACE(cert_no_str,';','')='',NULL,cert_no_str);
		ELSEIF LOCATE(cert_start_char,cert_no_str,start_pos) > 0 THEN
			WHILE start_pos <= cert_str_length DO 
				SET curr_pos = LOCATE(cert_start_char,cert_no_str,start_pos);
				IF curr_pos != start_pos THEN
					SET curr_pos = LOCATE(CONCAT(',',cert_start_char),cert_no_str,start_pos);
				END IF;

				#当前证书的截至位置
				SET end_pos = LOCATE(';',cert_no_str,start_pos);
				#如果这是最后一个证书，则截至位置是字符串长度+1
				SET end_pos = IF(end_pos > 0,end_pos,cert_str_length+1);
				
				IF curr_pos = 0 OR curr_pos >= end_pos THEN
				#如果当前没有证书信息，直接到下一个
					SET start_pos = end_pos + 1;
				ELSE 
					#当前证书的起始位置
					SET start_pos = LOCATE('$',cert_no_str,curr_pos);
					#如果没有找到，则表示没有证书
					SET start_pos = IF(start_pos > 0 ,start_pos + 1, end_pos);
					SET curr_cert_no = SUBSTRING(cert_no_str,start_pos,IF(end_pos-start_pos < 0,0,end_pos-start_pos));
						#如果长度为0，则表明已经结束
						#去重
						IF curr_cert_no != ''  THEN
							IF result IS NULL THEN
								SET result = curr_cert_no;
							ELSEIF SUBSTRING_INDEX(result,';',1) != curr_cert_no AND LOCATE(CONCAT(';',curr_cert_no,';'),result) = 0 AND SUBSTRING_INDEX(result,';',-1) != curr_cert_no THEN
							
							SET result = CONCAT(result,';',curr_cert_no);
							END IF;
						END IF;
					
					SET start_pos = end_pos + 1;						
					
				END IF;
							
			END WHILE;
		
		END IF;

	RETURN result;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for get_province_areacodes
-- ----------------------------
DROP FUNCTION IF EXISTS `get_province_areacodes`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `get_province_areacodes`(areacodes VARCHAR(300)) RETURNS varchar(100) CHARSET utf8
BEGIN
	DECLARE result VARCHAR(100) DEFAULT NULL;#
	DECLARE start_pos INT DEFAULT 1;
	DECLARE end_pos INT DEFAULT 1;
	DECLARE sub_str VARCHAR(20) DEFAULT NULL;
	
	WHILE start_pos <= LENGTH(areacodes) DO
		SET end_pos = LOCATE(',',areacodes,start_pos);
		IF end_pos = 0 THEN
			SET end_pos = LENGTH(areacodes) + 1;
		END IF;
		SET sub_str = SUBSTRING(areacodes,start_pos,end_pos-start_pos);
		IF sub_str != '' THEN
			SET sub_str = (CASE 
		WHEN sub_str LIKE '11%' THEN 
			'11'
		WHEN sub_str LIKE '43%' THEN 
			'43'
		WHEN sub_str LIKE '64%' THEN 
			'64'
		ELSE
			'#'
		END);
			SET result = IF(result IS NULL, sub_str,CONCAT(result,',',sub_str));
		END IF;
		SET start_pos = end_pos + 1;
	
	END WHILE;
	
	RETURN result;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for remove_specified_string
-- ----------------------------
DROP FUNCTION IF EXISTS `remove_specified_string`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `remove_specified_string`(original_string VARCHAR(300), new_string VARCHAR(300),delimiter VARCHAR(10)) RETURNS varchar(300) CHARSET utf8
BEGIN
	#移除指定的字符串。去重
	DECLARE result VARCHAR(300) DEFAULT NULL;
	DECLARE start_pos INT DEFAULT 1;
	DECLARE end_pos INT DEFAULT 1;
	DECLARE cur_string VARCHAR(300) DEFAULT NULL;
	IF original_string IS NULL OR original_string = '' OR original_string = new_string THEN
		SET result = NULL;
	ELSEIF new_string IS NULL OR new_string = '' THEN
		SET result = original_string;
	ELSE
		WHILE start_pos <= LENGTH(original_string) DO
			SET end_pos = LOCATE(delimiter,original_string,start_pos);
			IF end_pos = 0 THEN
			#到结尾
				SET end_pos = LENGTH(original_string) + 1;
			END IF;
			SET cur_string = SUBSTRING(original_string,start_pos,end_pos-start_pos);
			IF cur_string!= '' AND cur_string != new_string THEN
				SET result = IF(result IS NULL,cur_string,CONCAT(result,delimiter,cur_string));
			END IF;
			SET start_pos = end_pos+1;
				
		END WHILE;
		
	END IF;	
	
	RETURN result;

END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for sum_of_powers
-- ----------------------------
DROP FUNCTION IF EXISTS `sum_of_powers`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `sum_of_powers`(str VARCHAR(100), last_bit TINYINT(1)) RETURNS int(11)
BEGIN

/*根据二进制的位置字符串计算整数值。比如传入字符串1;2;3
如果最后一位表示1，则1;2;3的二进制形式为
 b 111。如果最后一位表示0，则1;2;3的二进制形式为b 1110 */
DECLARE i INT DEFAULT 1;
	DECLARE num_str VARCHAR(10);
	DECLARE result int(11) DEFAULT 0;
	
	
	-- SET str = business_scope_transfer(str);
	
	WHILE i <= LENGTH(str) DO
   IF SUBSTRING(str,i,1) = ';' THEN
	   SET i = i + 1;
	 ELSE
		SET num_str = TRIM(SUBSTRING_INDEX(SUBSTRING(str,i),';',1));
		 IF (CAST(num_str AS UNSIGNED) IS NOT NULL AND CAST(num_str AS UNSIGNED) > 0 AND CAST(num_str AS UNSIGNED) <= 10) THEN
			SET result = result | POWER(2,CAST(num_str AS UNSIGNED)-last_bit);
		 END IF;
		SET i = i + LENGTH(num_str)	+ 1;
		END IF;
  END WHILE;
	RETURN result;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for transform_attachment_url_outer_to_inner
-- ----------------------------
DROP FUNCTION IF EXISTS `transform_attachment_url_outer_to_inner`;
DELIMITER ;;
CREATE DEFINER=`v5_enterprise2021`@`%` FUNCTION `transform_attachment_url_outer_to_inner`(originalPathStr VARCHAR(5000)) RETURNS varchar(5000) CHARSET utf8
BEGIN
	DECLARE filelevel INT DEFAULT 0; #目前假设附件字段里面的值最多有10个附件。
	
	DECLARE fileJson VARCHAR(1000) DEFAULT NULL; #当前解析出来的这个文件的json字符串
	
	DECLARE originalPath VARCHAR(1000) DEFAULT NULL;#附件在外网的path路径
	#DECLARE path VARCHAR(1000) DEFAULT NULL;#附件在外网的path路径,用于拼接用
	
	DECLARE newPath VARCHAR(1000) DEFAULT NULL; #附件内网的path路径
	
	DECLARE fileName VARCHAR(1000) DEFAULT NULL; #文件名称
	
	DECLARE size VARCHAR(100) DEFAULT NULL;
	
	DECLARE uid VARCHAR(1000) DEFAULT NULL; #uid
	
	DECLARE pos INT DEFAULT 0; #一个位置索引的变量
	
	DECLARE jsonStr VARCHAR(2000) DEFAULT NULL;#当前文件在内网的json格式
	
	DECLARE result VARCHAR(5000) DEFAULT NULL; #结果
	
	
	IF originalPathStr IS NULL OR originalPathStr = '' OR LOCATE('[',originalPathStr) = 0 OR JSON_VALID(originalPathStr) = 0 THEN
		SET result = originalPathStr;
	ELSE
		WHILE filelevel < 10 AND JSON_EXTRACT(originalPathStr, CONCAT('$[',filelevel,']')) IS NOT NULL DO
		
			SET fileJson = JSON_EXTRACT(originalPathStr, CONCAT('$[',filelevel,']'));
			
			SET originalPath = REPLACE(JSON_EXTRACT(fileJson, '$.path'),'"','');
			SET newPath = originalPath;
			SET size = REPLACE(JSON_EXTRACT(fileJson, '$.size'),'"','');
			SET uid = REPLACE(JSON_EXTRACT(fileJson, '$.uid'),'"','');
			SET fileName = NULL;
			IF originalPath IS NOT NULL AND originalPath != '' AND originalPath != 'null' THEN
				
				SET fileName = SUBSTRING_INDEX(originalPath,'/',-1);
				
				IF LOCATE('http',originalPath) = 1 THEN
					IF LOCATE('/uploads',originalPath) > 0 THEN
						SET pos = LOCATE('/uploads',originalPath);
						SET newPath = SUBSTRING(originalPath,pos,LENGTH(originalPath) - pos + 1);
					
					ELSEIF LOCATE('/upload',originalPath) > 0 THEN
					#把第一个‘/upload’换成‘/uploads’
						SET pos = LOCATE('/upload',originalPath) + 7;
						SET newPath = CONCAT('/uploads', SUBSTRING(originalPath,pos,LENGTH(originalPath) - pos + 1));
						
					ELSE
						SET newPath = REPLACE(REPLACE(originalPath,'http://',''),'https://','');
						SET pos = LOCATE('/',newPath);
						SET newPath = CONCAT('/uploads',SUBSTRING(newPath,pos,LENGTH(newPath) - pos + 1));
					END IF;
					
					IF LOCATE('%2F',newPath) > 0 THEN
						SET newPath = REPLACE(newPath,'%2F','/');				
					END IF;
				
				END IF;
				#最后一个'/'的位置
				SET pos = LENGTH(originalPath) - LENGTH(SUBSTRING_INDEX(originalPath,'/',-1));
				SET originalPath = CONCAT('/v2/sync/file/download?f=',SUBSTRING(originalPath,1,pos),CONVERT(SUBSTRING(originalPath,pos + 1,LENGTH(originalPath) - pos) USING utf8));
				
			END IF;
			
			IF fileName IS NULL OR fileName = '' THEN
				SET fileName = UUID();			
			END IF;
			IF uid IS NULL OR uid = '' THEN
				SET uid = CONCAT('manually',fileName);
			END IF;
			
			
			SET jsonStr = CONCAT('{',
				'"name":"', fileName, '",',
        '"originalPath":"', originalPath, '",',
        '"path":"', newPath, '",',
				IF(size IS NULL,'',CONCAT('"size":"', size, '",')),
        '"uid":"', uid, '"'
    ,'}');
			SET result = IF(result IS NULL,jsonStr,CONCAT(result,',',jsonStr));
		
			SET filelevel = filelevel + 1;
		END WHILE;
		
		SET result = IF(result IS NULL,result,CONCAT('[',result,']'));
	END IF;
	
	RETURN result;
END
;;
DELIMITER ;