-- ----------------------------
-- Function structure for business_scope_transfer
-- ----------------------------
DROP FUNCTION IF EXISTS `business_scope_transfer`;
delimiter ;;
CREATE 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 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 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 ;

-- ----------------------------
-- 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 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
SET a.ITEM_PRINCIPAL = b.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)
WHERE
	a.ITEM_PRINCIPAL = b.id and b.id = c.tenant_user_id AND b.id = p_tenant_user_id;
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 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
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 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
WHERE
	a.ITEM_USERID = b.id and b.id=c.tenant_user_id AND b.id = p_tenant_user_id;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_sync_attendance_site_base_info_update_company_by_id
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_attendance_site_base_info_update_company_by_id`;
delimiter ;;
CREATE PROCEDURE `proc_sync_attendance_site_base_info_update_company_by_id`(IN p_company_id VARCHAR (300),p_company_name VARCHAR (300),o_company_police VARCHAR(200),o_company_police_code VARCHAR(200))
BEGIN
	#Routine body goes here...
update companyinfocollect.tlk_attendance_site_base_info c
set c.ITEM_COMPANY_POLICE = o_company_police, c.ITEM_COMPANY_POLICE_CODE = o_company_police_code,c.ITEM_COMPANY_NAME=p_company_name
where c.ITEM_DOMAIN_ID = p_company_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 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
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 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);#人数
	DECLARE attendanceSiteId VARCHAR(200);#驻勤点id
IF(p_after_id is null) 
	THEN 
	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_before_attendanceSiteId AND b.`status` = '0' into personNum ;
	UPDATE companyinfocollect.tlk_attendance_site_base_info a
	set a.ITEM_ATT_PERSON_NUM=personNum
	where a.ITEM_ATTENDANCE_ID=p_before_attendanceSiteId;
ELSEIF(p_after_id IS NOT NULL AND p_before_id IS  NULL) 
	THEN 
	select ITEM_attendanceSiteId from baibaodunflow.tlk_attendance_site_person_info where ID=p_after_id into attendanceSiteId;
	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_after_attendanceSiteId AND b.`status` = '0' into personNum ;
	UPDATE companyinfocollect.tlk_attendance_site_base_info a
	set ITEM_ATT_PERSON_NUM=personNum
	where a.ITEM_ATTENDANCE_ID=attendanceSiteId;
ELSEIF(p_after_id IS NOT NULL AND p_before_id IS NOT NULL) 
	THEN 
	select ITEM_attendanceSiteId from baibaodunflow.tlk_attendance_site_person_info where ID=p_after_id into attendanceSiteId;
	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_after_attendanceSiteId AND b.`status` = '0' into personNum ;
	UPDATE companyinfocollect.tlk_attendance_site_base_info a
	set ITEM_ATT_PERSON_NUM=personNum
	where a.ITEM_ATTENDANCE_ID=attendanceSiteId;
END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_sync_att_site_update_attPersonNum_by_employees
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_att_site_update_attPersonNum_by_employees`;
delimiter ;;
CREATE PROCEDURE `proc_sync_att_site_update_attPersonNum_by_employees`(in p_id VARCHAR(300))
BEGIN
	DECLARE personNum VARCHAR(200);#人数
	DECLARE attendanceSiteId VARCHAR(200);#驻勤点id
	select ITEM_attendanceSiteId from baibaodunflow.tlk_attendance_site_person_info a LEFT JOIN obpm2.tenant_employees b ON a.ITEM_securityId = b.id
  where b.id=p_id  into attendanceSiteId;
	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=attendanceSiteId AND b.`status` = '0' into personNum ;
	UPDATE companyinfocollect.tlk_attendance_site_base_info a
	set ITEM_ATT_PERSON_NUM=personNum
	where a.ITEM_ATTENDANCE_ID=attendanceSiteId;
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 PROCEDURE `proc_sync_commendation_detail_by_tlk_awardk_v2`(IN p_idsStr VARCHAR(1000),o_ITEM_REGIONId VARCHAR(200))
BEGIN
	#表彰奖励信息归集
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(a.ITEM_COMPANYNAME, ''),
	IFNULL(a.DOMAINID, ''),
	IFNULL(a.CREATED, ''),
	IFNULL(a.LASTMODIFIED, ''),
	(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
#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(a.ITEM_COMPANYNAME, ''),
 ITEM_DOMAIN_ID = IFNULL(a.DOMAINID, ''),
 CREATED = IFNULL(a.CREATED, ''),
 LASTMODIFIED = 	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
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_sync_employed_events_info_collect_by_employee_events_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employed_events_info_collect_by_employee_events_v2`;
delimiter ;;
CREATE PROCEDURE `proc_sync_employed_events_info_collect_by_employee_events_v2`(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)
BEGIN

/*归集智能人事入离职记录入行业信息。
只需要归集机构类型为保安公司和自招单位的入离职记录
公司的审核状态为审核通过的数据才会被展示并送入内网*/
	DECLARE p_interview VARCHAR(300) DEFAULT NULL;

	#DECLARE p_leave_time DATETIME DEFAULT NULL;
	DECLARE p_org_name VARCHAR(300) DEFAULT NULL;
	DECLARE p_org_id VARCHAR(100) DEFAULT NULL;
	DECLARE p_user_id VARCHAR(100) DEFAULT NULL;
	DECLARE p_user_name VARCHAR(300) DEFAULT NULL;
	DECLARE p_org_approved_status INT DEFAULT NULL;
	DECLARE p_org_supervise_region_code VARCHAR(300) DEFAULT NULL;
	DECLARE p_org_office_type VARCHAR(200) DEFAULT NULL; -- 机构类型，只归集保安公司+自招单位的入离职记录
	
	#如果事件类型为入职（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字段。
	SELECT  e.interview,o.name,o.id,u.id,u.name,o.approved_information_status,o.supervise_region_code,o.institutional_code  
	INTO p_interview,p_org_name,p_org_id,p_user_id,p_user_name,p_org_approved_status,p_org_supervise_region_code,p_org_office_type
	FROM obpm2.tenant_employees e 
		LEFT JOIN
		obpm2.tenant_organizations o ON e.organization_id = o.id
		LEFT JOIN obpm2.tenant_users u ON e.tenant_user_id = u.id
		WHERE e.id = p_employee_id LIMIT 1;
	
	-- 入离职记录只归集保安公司 + 自招单位
	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,
	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,
		IF(p_org_approved_status='1',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_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_employed_events_info_collect_by_employee_v2`;
delimiter ;;
CREATE 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)
	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 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 = IF(p_after_org_approved_status='1',p_org_supervise_region_code,'#'),
		e.company_name = p_after_org_name,
		e.LASTMODIFIED = IFNULL(p_org_last_update_time,CURRENT_TIMESTAMP)
	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 PROCEDURE `proc_sync_employee_contracts_info_collect_by_contract_id_v2`(IN p_contractId VARCHAR(300),IN p_organization_supervise_region_code VARCHAR(300))
BEGIN
			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`
			) select 
			IFNULL(otc.id,''), #contract_id
			IFNULL(otc.employee_id,''), #employee_id
			IFNULL(otc.`name`,''), #contract_name
			IFNULL(otc.b_name,''), #securityman_name
			IFNULL(otu.telephone,''), #securityman_phone 保安人员联系号码 从obpm2.tenant_users表获取
			IFNULL(otc.a_name,''), #party_a_name
			IFNULL(oto.telephone,''), #party_a_phone 甲方联系电话 从obpm2.tenant_organizations表获取
			IFNULL(otc.begin_date,''), #start_date
			otc.end_date, #end_date
			IFNULL(bts.ITEM_REGISTERPOLICEADDRESS,''), #supervise_office 监管机构名称
			IFNULL(bts.ITEM_REGISTERPOLICEADDRESSID,''), #supervise_office_code 监管机构编码
			CONCAT( '[{"name":"',SUBSTRING_INDEX(IFNULL(otc.attachment,''),'/',-1),'","path":"', IFNULL(otc.attachment,''), '"}]'), #contract
			otc.note, #note
			IFNULL(otc.`status`,''), #contract_status
			IFNULL(otc.creator_created_time,''), #create_time
			IFNULL(otc.last_modifier_created_time,now()), #LASTMODIFIED
			IFNULL(otu.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
				NULL
			END) AS domain_id, #domainid 根据参数p_organization_supervise_region_code 区分地区设置domainid
			now() #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_contractId
			ON DUPLICATE KEY UPDATE
			`contract_id` = IFNULL(otc.id,''),
			`employee_id` = IFNULL(otc.employee_id,''),
			`contract_name` = IFNULL(otc.`name`,''),
			`securityman_name` = IFNULL(otc.b_name,''),
			`securityman_phone` = IFNULL(otu.telephone,''),
			`party_a_name` = IFNULL(otc.a_name,''),
			`party_a_phone` = IFNULL(oto.telephone,''),
			`start_date` = IFNULL(otc.begin_date,''),
			`end_date` = otc.end_date,
			`supervise_office` = IFNULL(bts.ITEM_REGISTERPOLICEADDRESS,''),
			`supervise_office_code` = IFNULL(bts.ITEM_REGISTERPOLICEADDRESSID,''),
			`contract` = CONCAT( '[{"name":"',SUBSTRING_INDEX(IFNULL(otc.attachment,''),'/',-1),'","path":"', IFNULL(otc.attachment,''), '"}]'),
			`note` = otc.note,
			`contract_status` = IFNULL(otc.`status`,''),
			`create_time` = IFNULL(otc.creator_created_time,''),
			`LASTMODIFIED` = otc.last_modifier_created_time,
			`user_id` = IFNULL(otu.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
;;
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 PROCEDURE `proc_sync_employee_contracts_info_collect_by_tlk_setsupervise`(IN P_DOMAINID VARCHAR(300))
BEGIN
	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
	WHERE a.user_id = te.tenant_user_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 PROCEDURE `proc_sync_good_deeds_detail_by_tlk_goodperson_v2`(IN p_idsStr VARCHAR(1000),o_ITEM_REGIONId VARCHAR(200))
BEGIN
	#好人好事归集
INSERT INTO good_deeds_detail (
	good_deed_id,
	NAME,
	user_id,
	good_deeds_type,
	content,
	note,
	date,
	company_name,
	ITEM_DOMAIN_ID,
	CREATED,
	LASTMODIFIED,
	DOMAINID
) 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(a.ITEM_COMPANYNAME, ''),
	IFNULL(a.DOMAINID, ''),
	IFNULL(a.CREATED, ''),
	IFNULL(a.LASTMODIFIED, ''),
	(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
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
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, ''),
  LASTMODIFIED = 	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);

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 PROCEDURE `proc_sync_punishment_detail_by_tlk_punish_v2`(IN p_idsStr VARCHAR(1000),o_ITEM_REGIONId VARCHAR(200))
BEGIN
	#惩罚信息归集
INSERT INTO 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
	) 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, ''),
	(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
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 = 	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);
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 PROCEDURE `proc_sync_rd_attendance_site_base_info_collect_by_id`(IN p_id LONGTEXT)
BEGIN

DECLARE personNum VARCHAR(200);
#根据驻勤id统计驻勤点人员表中该驻勤点的人数
select count(1) from baibaodunflow.tlk_attendance_site_person_info where ITEM_attendanceSiteId=p_id 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
)
select 
a.PARENT,
a.LASTMODIFIED,
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 c.ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN c.ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN c.ITEM_REGIONId 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,
a.ITEM_USERID,
REPLACE(a.id,'__W052b1XNoLmPckF6ch1','__29MeWum6wey61qtRqF3') as id ,
b.ITEM_REGISTERPOLICEADDRESS,
a.ID as ITEM_ATTENDANCE_ID,
a.CREATED,
a.ITEM_principal,
c.ITEM_code,
personNum
from  baibaodunflow.tlk_attendance_site_base_info a LEFT JOIN baibaodunflow.tlk_SetSupervise b on a.DOMAINID=b.ITEM_domain_id
left JOIN organizationauth.tlk_DepartmentLevel c on  c.id = b.item_registerPoliceAddressID
where a.id=p_id AND a.ITEM_attendanceSiteState != '待完善' AND a.ITEM_contractFile is not NULL AND a.ITEM_contractFile != '' 
 and a.ITEM_attendanceStartDate is not null  and a.ITEM_attendanceEndDate is not  null
ON DUPLICATE KEY UPDATE
PARENT=a.PARENT,
LASTMODIFIED=a.LASTMODIFIED,
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 c.ITEM_REGIONId LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN c.ITEM_REGIONId LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN c.ITEM_REGIONId 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=a.ITEM_contractName,
ITEM_CONTRACT_IDS=a.ITEM_CONTRACTIDS,
ITEM_CONTRACT_STATE=a.ITEM_contractState,
ITEM_CONTRACT_START_DATE=a.ITEM_contractStartDate,
ITEM_CONTRACT_END_DATE=a.ITEM_contractEndDate,
ITEM_CONTRACT_FILE=a.ITEM_contractFile,
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=a.ITEM_principalName,
#ITEM_PRINCIPAL_CARD_TYPE=carType,
#ITEM_PRINCIPAL_IDCARD_NO=a.ITEM_principalIdCardNo,
#ITEM_PRINCIPAL_PHONE_NO=a.ITEM_principalPhoneNo,
#ITEM_PRINCIPAL_IS_CERTIFIED=a.ITEM_principalIsCertified,
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=a.ITEM_REALITYNAME,
#ITEM_REALITY_CARD_NO=a.ITEM_REALITYCARDNO,
#ITEM_REALITY_PHONE=a.ITEM_REALITYPHONE,
#ITEM_USERID=a.ITEM_USERID,
ITEM_COMPANY_POLICE=b.ITEM_REGISTERPOLICEADDRESS,
ITEM_CREATED=a.CREATED,
#ITEM_principal=a.ITEM_principal,
ITEM_COMPANY_POLICE_CODE=c.ITEM_code,
ITEM_ATT_PERSON_NUM=personNum;
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 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_contractFile IS NOT NULL AND s.ITEM_contractFile != '') 
	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.last_sync_time = CURRENT_TIMESTAMP
	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 PROCEDURE `proc_sync_rd_company_info_collect_by_companies`(IN p_company_id VARCHAR ( 300 ))
BEGIN
 /*从智能人事的tenant_organizations 和tenant_companies归集公司信息。
 未审核通过的不送入内网
 */
 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_city_code,
		ITEM_place_of_register_district_code,
		ITEM_place_of_business_province_code,
		ITEM_place_of_business_city_code,
		ITEM_place_of_business_district_code,
		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 
	) 
	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_city_code, t.place_of_register_district_code, 
	t.place_of_business_province_code, t.place_of_business_city_code, 
	t.place_of_business_district_code, 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
	
	FROM (SELECT
		'__2eoK1zvtxNarHlwwQg4' AS FORMID,
		'__DOoeSJp26wVqbyYYf50' AS APPLICATIONID,
		o.id AS item_domain_id,
		IF(o.approved_information_status = 1,(CASE 
		WHEN o.supervise_region_code LIKE '11%' THEN 
			'xduDIOSsi06qmfpig0A'
		WHEN o.supervise_region_code LIKE '43%' THEN 
			'__UDa4uPMdcOYgP7HETaf'
		WHEN o.supervise_region_code LIKE '64%' THEN 
			'qS6PXrA7'
		ELSE
			'#'
		END),'#') AS domain_id, #审核通过的根据区域编码指定组织域id
		IF(o.approved_information_status = 1,o.supervise_region_code,'#') AS region_id,
		CONCAT(REPLACE(o.id,'--','__'),'--__2eoK1zvtxNarHlwwQg4') AS ID,
		o.`name` AS company_name,
		o.`status` AS company_status,
		o.telephone AS company_phone,
		o.fax AS company_fax,
		c.economic_type AS economic_type,
		c.nature AS company_type,
		CONCAT(IFNULL(o.place_of_register_province_name,''),IFNULL(o.place_of_register_city_name,''),IFNULL(o.place_of_register_district_name,'')) AS register_address,
		o.place_of_register_address AS register_addr_detail,
		CONCAT(IFNULL(o.place_of_business_province_name,''),IFNULL(o.place_of_business_city_name,''),IFNULL(o.place_of_business_district_name,'')) AS office_address,
		o.place_of_business_address AS office_addr_detail,
		o.place_of_register_province_code AS place_of_register_province_code,
		o.place_of_register_city_code AS place_of_register_city_code,
		o.place_of_register_district_code AS place_of_register_district_code,
		o.place_of_business_province_code AS place_of_business_province_code,
		o.place_of_business_city_code AS place_of_business_city_code,
		o.place_of_business_district_code AS place_of_business_district_code,
		IF(o.type IN ('1','2','3'),o.type,NULL) AS is_sub,
		c.parent_name AS group_name,
		
		c.parent_legal_person_name AS group_legal_person,
		c.parent_certificate_type AS group_document_type,
		c.parent_legal_person_telephone AS group_phone,
		c.parent_certificate_number AS group_document_no,
		c.legal_person_name AS legal_person,
		c.legal_person_telephone AS legal_person_phone,
		c.legal_person_credential_type AS document_type,
		c.legal_person_nationality AS nationality,
		c.legal_person_credential_number AS document_id,
		c.security_person_name AS sec_manager,
		c.security_person_telephone AS sec_manager_phone,
		c.security_person_credential_type AS sec_manager_doc_type,
		c.security_person_credential_number AS sec_manager_id,
		IF(c.registered_capital = '', '0', c.registered_capital) AS registered_capital,
		IF(ISNULL(c.fixed_capital) OR c.fixed_capital = '', '0', c.fixed_capital) AS fixed_assets,
	  IF(ISNULL(c.annual_sales_volume) OR c.annual_sales_volume = '','0', c.annual_sales_volume) AS annual_turnover,
		IF(ISNULL(c.annual_profit_tax) OR c.annual_profit_tax = '', '0', c.annual_profit_tax) AS profit_taxes,
		c.certificate_type AS register_doc_type,
		c.unify_social_credit_code AS register_id,
		concat( '[{"name":"","path":"', c.unify_social_credit_code_file, '"}]') AS register_photo,
		o.institutional_code AS office_type,
		o.industry_detail_type AS security_type,
		c.security_approval_number AS approval_no,
		IF(o.institutional_code = '0102',c.service_permit_number,NULL) AS security_service_permit_no,
		IF(o.institutional_code = '0102',concat( '[{"name":"","path":"', c.service_permit_attachment, '"}]'),NULL) AS security_service_photo,
		IF(o.institutional_code = '0102',c.name_of_issuing_authority,NULL) AS issuing_security_service,
		IF(o.institutional_code = '0102',c.first_issue_service_permit,NULL) AS issue_service_date,
		business_scope_transfer(c.service_scope_description) AS business_scope,
		c.service_scope AS business_scope_int, -- 需要确认
		NULLIF(c.security_preparedness_rating,0) AS hr_level,
		NULLIF(c.security_armed_rating,0) AS armed_escort_level,
		NULLIF(c.security_safety_defense_rating,0) AS security_precautions,
		c.is_risk_assessment AS is_risk_assessment,
		IF(o.institutional_code = '0103',c.service_permit_number,NULL) AS security_train_permit_no,
		IF(o.institutional_code = '0103',concat( '[{"name":"","path":"', c.service_permit_attachment, '"}]'),NULL) AS security_train_photo,
		IF(o.institutional_code = '0103',c.name_of_issuing_authority,NULL) AS issuing_train_permit,
		IF(o.institutional_code = '0103',c.first_issue_service_permit,NULL) AS issue_train_date,
		c.train_content AS train_scope,
		c.number_of_security_guards AS security_nums,
		c.nature_of_self_recruited_units AS attendance_site_type,
		IFNULL(c.is_public_security_security,0) AS is_key_protection_units,
		IFNULL(c.is_property_enterprise,0) AS is_property_company,
		IFNULL((SELECT r.region_full_name FROM obpm2.sys_region r WHERE r.id = o.supervise_region_code),'') AS supervise_district,
		o.supervise_depart_name AS supervise_office,
		o.supervise_depart_id AS supervise_office_ID,
		o.created_time AS entry_date,
		(
		SELECT COUNT(1) FROM obpm2.tenant_employees te 
		WHERE te.organization_id = o.id 
		AND te.`status` = 0 
		AND te.occupation_type = 1 #保安员数
		) AS in_mycompany_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 = o.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
			) AS cert_securityman_nums, # 获取持证保安员数：在职+证书非逻辑删除+证书有效
		(
			SELECT
				count( 1 ) 
			FROM
				baibaodunflow.tlk_attendance_site_base_info bi 
			WHERE
				bi.ITEM_attendanceSiteState != '待完善' 
				AND (bi.ITEM_contractFile IS NOT NULL AND bi.ITEM_contractFile != '') 
				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 = o.id 
			) AS attendance_site_nums, # 有效驻勤点数：已完善+有合同+时间有效
		(
			SELECT
				COUNT(1)
			FROM baibaodunflow.tlk_shareholderinformation si
			WHERE si.DOMAINID = o.id
			AND si.ITEM_IS_DELETE = '0'
		) AS shareholder_nums, #股东数
		( 
			SELECT 
				count( 1 ) 
			FROM 
				baibaodunflow.tlk_managementlayer tm 
			WHERE 
				tm.DOMAINID = o.id 
				AND tm.ITEM_IS_DELETE = '0'
			) AS manager_nums, #管理层数
		IFNULL(c.last_updated_time,CURRENT_TIMESTAMP) AS last_modified,
		CURRENT_TIMESTAMP AS last_sync_time
		
		FROM
			obpm2.tenant_organizations o 
		JOIN obpm2.tenant_companies c 
		ON o.id = c.id
	
		/**JOIN obpm2.tenant_employees e 
		ON e.organization_id = o.id */
		
		WHERE o.id = p_company_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;
 
 
 /**UPDATE companyinfocollect.tlk_companyinfoquery c, obpm2.tenant_companies tcs 
 SET 
	c.ITEM_economic_type = tcs.economic_type,
	c.ITEM_company_type = tcs.nature,
	c.ITEM_group_name = tcs.parent_name,
	/**c.ITEM_group_id = (
	SELECT cs.id FROM obpm2.tenant_companies cs
	WHERE cs.certificate_number = tcs.parent_certificate_number limit 1	
	),*/
	/**c.ITEM_group_legal_person = tcs.parent_legal_person_name,
	c.ITEM_group_document_type = tcs.parent_certificate_type,
	c.ITEM_group_phone = tcs.parent_legal_person_telephone,
	c.ITEM_group_document_no = tcs.parent_certificate_number,
	c.ITEM_registered_capital = IF(tcs.registered_capital='',0,tcs.registered_capital),
	c.ITEM_fixed_assets = IF(tcs.fixed_capital = '',0,tcs.fixed_capital),
	c.ITEM_annual_turnover =IF(tcs.annual_sales_volume = '',0,tcs.annual_sales_volume) ,
	c.ITEM_profit_taxes = IF(tcs.annual_profit_tax='',0,tcs.annual_profit_tax),
	c.ITEM_register_doc_type = tcs.certificate_type,
	c.ITEM_register_id = tcs.unify_social_credit_code,
	c.ITEM_register_photo = tcs.unify_social_credit_code_file,
	c.ITEM_approval_no = tcs.security_approval_number,
	c.ITEM_security_service_permit_no = IF(c.ITEM_office_type = '0102',tcs.service_permit_number,NULL),
	c.ITEM_security_service_photo = IF(c.ITEM_office_type = '0102',tcs.service_permit_attachment,NULL),
	c.ITEM_issuing_security_service = IF(c.ITEM_office_type = '0102',tcs.name_of_issuing_authority,NULL),
	c.ITEM_issue_service_date = IF(c.ITEM_office_type = '0102',tcs.first_issue_service_permit,NULL),
	c.ITEM_business_scope = tcs.service_scope_description,
	c.ITEM_business_scope_int = tcs.service_scope, -- 需要确认
	c.ITEM_hr_level = NULLIF(tcs.security_preparedness_rating,0),
	c.ITEM_armed_escort_level = NULLIF(tcs.security_armed_rating,0),
	c.ITEM_security_precautions = NULLIF(tcs.security_safety_defense_rating,0),
	c.ITEM_is_risk_assessment = tcs.is_risk_assessment,
	c.ITEM_security_train_permit_no = IF(c.ITEM_office_type = '0103',tcs.service_permit_number,NULL),
	c.ITEM_security_train_photo = IF(c.ITEM_office_type = '0103',tcs.service_permit_attachment,NULL),
	c.ITEM_issuing_train_permit = IF(c.ITEM_office_type = '0103',tcs.name_of_issuing_authority,NULL),
	c.ITEM_issue_train_date = IF(c.ITEM_office_type = '0103',tcs.first_issue_service_permit,NULL),
	c.ITEM_train_scope = tcs.train_content,
	c.ITEM_security_nums = tcs.number_of_security_guards,
	c.ITEM_attendance_site_type = tcs.nature_of_self_recruited_units,
	c.ITEM_is_key_protection_units = tcs.is_public_security_security,
	c.ITEM_is_property_company = tcs.is_property_enterprise,
	c.ITEM_legal_person = tcs.legal_person_name,
	c.ITEM_legal_person_phone = tcs.legal_person_telephone,
	c.ITEM_document_type = tcs.legal_person_credential_type,
	c.ITEM_document_id = tcs.legal_person_credential_number,
	c.ITEM_nationality = tcs.legal_person_nationality,
	c.ITEM_sec_manager = tcs.security_person_name,
	c.ITEM_sec_manager_phone = tcs.security_person_telephone,
	c.ITEM_sec_manager_doc_type = tcs.security_person_credential_type,
	c.ITEM_sec_manager_id = tcs.security_person_credential_number,
  c.LASTMODIFIED = tcs.update_time
 WHERE c.id = tcs.id AND c.id = p_company_id;*/


END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_sync_rd_company_info_collect_by_employee_v2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_sync_rd_company_info_collect_by_employee_v2`;
delimiter ;;
CREATE PROCEDURE `proc_sync_rd_company_info_collect_by_employee_v2`(IN p_org_id VARCHAR(300),p_last_update_time DATETIME, p_before_status INT, p_after_status INT, p_before_occupation_type INT, p_after_occupation_type INT)
BEGIN
	
	IF (p_before_status IS NULL OR  p_before_status != p_after_status) OR (p_before_occupation_type IS NULL OR p_before_occupation_type != p_after_occupation_type) THEN
/*入离职情况发生变化，更新公司的保安员数和持证保安员数*/
	UPDATE companyinfocollect.tlk_companyinfoquery 
	-- 在职保安员数
	SET ITEM_in_mycompany_nums = 
	(
	 SELECT COUNT(1) 
	 FROM obpm2.tenant_employees te 
	 WHERE te.organization_id = p_org_id 
	 AND te.`status` = 0 
	 AND te.occupation_type = 1
	),
	/* 持证保安员数：在职 + 保安员 + 证书类型为资格证 + 非逻辑删除 + 证书有效*/
	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)
	WHERE item_domain_id = p_org_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 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),
	last_sync_time = CURRENT_TIMESTAMP 
	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 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)
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_city_code = p_org_place_of_register_city_code,
	 c.ITEM_place_of_register_district_code = p_org_place_of_register_district_code,
	 c.ITEM_place_of_business_province_code = p_org_place_of_business_province_code,
	 c.ITEM_place_of_business_city_code = p_org_place_of_business_city_code,
	 c.ITEM_place_of_business_district_code = p_org_place_of_business_district_code,
	 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 = IF(p_org_approved_status = 1,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),''),
		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)
	
	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 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.last_sync_time = CURRENT_TIMESTAMP
	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 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)
	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 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),
		last_sync_time = CURRENT_TIMESTAMP
		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 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
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 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
)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,
	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
 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 = 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);
		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 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.last_sync_time = 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 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), 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 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.last_sync_time = 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 PROCEDURE `proc_sync_rd_employee_info_collect_by_employee_v2`(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_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))
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离职

/*如果就职状态发生变化:新增，或者入离职状态修改*/
IF (p_employee_before_status IS NULL AND p_employee_after_status IS NOT NULL) OR (p_employee_before_status != p_employee_after_status) THEN
	
/*
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 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);
		#保安员的话，有记录只有一条
		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);
		ELSEIF p_employee_after_status = 0 OR (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
			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',
			original_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 <= '",original_hired_date,"'") /*如果该记录在导入内网之前又复职了，那么不能删除这个id的数据，需要用入职时间判断是否复职。如果复职则不删除，没有复职则删除*/
			);
		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);
		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 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);
		 #有记录
		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
							# 删除的数据插入数据删除事件表，用于同步到内网去删除该数据
							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',
							original_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 <= '",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);
						
					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);
								
					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);
					
				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
							# 删除的数据插入数据删除事件表，用于同步到内网去删除该数据
							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',
							original_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 <= '",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);
					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);
					
					END IF;
				ELSE /*入职了多家公司，如果这些里面有当前公司的入职记录，则是离职操作从这些公司里面删除当前公司的入职记录。如果没有，则忽略（老数据导入）*/
					DELETE FROM companyinfocollect.employees WHERE employee_id = p_employee_id;
					IF p_org_approved_status='1' THEN
						# 删除的数据插入数据删除事件表，用于同步到内网去删除该数据
						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,"'") #如果同步到内网的时候，在该公司复职了，那么入职时间肯定比当前记录晚，则不会删除该id的记录
						);
					END IF;
				END IF;
			END IF;
			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);

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 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)
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 != p_after_org_approved_status 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 = IF(p_after_org_approved_status = 1,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)
	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 PROCEDURE `proc_sync_rd_employee_info_collect_by_securitymastermanagement`(IN b_document_id VARCHAR(200), a_document_id VARCHAR(200),a_lastmodified DATETIME)
BEGIN

/*保安师表发生变更，需要更新职员的等级信息中的保安师的部分。*/
	DECLARE p_doc_id VARCHAR(200) DEFAULT NULL;
	DECLARE p_tenant_user_id VARCHAR(200) DEFAULT NULL;
	DECLARE p_security_master_grade VARCHAR(20) DEFAULT NULL; -- 保安师
	

	SET p_doc_id = IFNULL(b_document_id,a_document_id);
	
	SELECT uc.tenant_user_id INTO p_tenant_user_id
	FROM obpm2.tenant_user_credentials uc 
	WHERE uc.number = p_doc_id LIMIT 1;
	
	
	-- 获取保安师等级。查询保安师表格。等级分一级和二级，结果为‘1;2’的形式
	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 
	WHERE mm.ITEM_DOCUMENTID = p_doc_id;
	
	UPDATE companyinfocollect.employees e 
	SET  
		e.security_grade = concat_security_master_grade(e.security_grade, p_security_master_grade),
		e.security_grade_int = sum_of_powers(concat_security_master_grade(e.security_grade, p_security_master_grade), 0),
		e.LASTMODIFIED = IFNULL(a_lastmodified,CURRENT_TIMESTAMP)
		WHERE e.user_id = p_tenant_user_id;

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 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)
			WHERE e.user_id = p_tenant_user_id;
		END IF;
		
	IF p_credential_type = 7 THEN
	#获取资格证
		SELECT tuc.number INTO p_certNo
		FROM 
		obpm2.tenant_users tu 
		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 tu.id = p_tenant_user_id
		AND tu.checked_status = 1 
		AND tuc.credential_type = 7
		AND tucd.active = 1
		AND tucd.state = '1'
		ORDER BY tuc.valid_date_from DESC LIMIT 1;
		
		IF p_certNo IS NOT NULL AND p_certNo != '' THEN
		SET p_withCertificate = 1;
		ELSE
		SET p_withCertificate = 0;
		END IF;
		
		UPDATE companyinfocollect.employees e 
		SET 
		e.certificate_no = p_certNo,
		e.with_certificate = p_withCertificate,
		e.LASTMODIFIED = IFNULL(p_update_time,CURRENT_TIMESTAMP)
		WHERE e.user_id = p_tenant_user_id;
		
	ELSEIF p_credential_type = 8 THEN		
	
	-- 更新职员的等级证信息
	-- DECLARE p_security_grade VARCHAR(50) DEFAULT NULL;
		SELECT GROUP_CONCAT(tucd.appraisalGrade SEPARATOR ';') INTO p_security_grade 
		FROM 
		obpm2.tenant_users tu 
		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 tu.id = p_tenant_user_id
		AND tu.checked_status = 1 
		AND tuc.credential_type = 8
		AND tucd.active = 1
		AND tucd.state = '1'
		AND (tucd.appraisalGrade IS NOT NULL AND tucd.appraisalGrade != '');
		
		UPDATE companyinfocollect.employees e 
		SET 
		e.security_grade = concat_security_grade(e.security_grade, p_security_grade),
		e.security_grade_int = sum_of_powers(concat_security_grade(e.security_grade, p_security_grade), 0),
		e.LASTMODIFIED = IFNULL(p_update_time,CURRENT_TIMESTAMP)
		WHERE e.user_id = p_tenant_user_id;
	
	END IF;
	-- 获取保安师等级
	/**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;
	
	IF p_security_master_grade IS NOT NULL THEN
	SET p_security_grade = CONCAT(p_security_master_grade,';',p_security_grade);
	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 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_before_check_status INT,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))
BEGIN
	
/*人员信息发生变更，更新对应的职员信息*/
	DECLARE p_certNo VARCHAR(50) DEFAULT NULL;
	DECLARE p_withCertificate TINYINT default 0;
	DECLARE p_security_grade VARCHAR(50) DEFAULT NULL; -- 保安员等级

	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.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
	WHERE e.user_id = p_user_id;
	
	#如果人员的核验状态发生变更，更新对应保安员的持证状态和等级证信息
	IF p_user_before_check_status != 1 AND p_user_after_check_status = 1 THEN
	#获取资格证
		SELECT tuc.number INTO p_certNo 
		FROM obpm2.tenant_user_credentials tuc JOIN
		obpm2.tenant_user_credential_details tucd ON tuc.id = tucd.id 
		WHERE tuc.tenant_user_id = p_user_id 
		AND tuc.credential_type = 7
		AND tucd.active = 1
		AND tucd.state = '1'
		ORDER BY tuc.valid_date_from DESC LIMIT 1;
		
		IF p_certNo IS NOT NULL AND p_certNo != '' THEN
			SET p_withCertificate = 1;
		ELSE
			SET p_withCertificate = 0;
		END IF;
	
		#获取职员等级证，以类似3；4；5的形式拼接。证件类型为（8） + 生效的（active = 1，非逻辑删除数据） + 有效的（未吊销/撤销）
	SELECT GROUP_CONCAT(tucd.appraisalGrade SEPARATOR ';') INTO p_security_grade 
	FROM obpm2.tenant_user_credentials tuc JOIN
	obpm2.tenant_user_credential_details tucd ON tuc.id = tucd.id 
	WHERE tuc.tenant_user_id = p_user_id 
	AND tuc.credential_type = 8
	AND tucd.active = 1
	AND tucd.state = '1'
	AND (tucd.appraisalGrade IS NOT NULL AND tucd.appraisalGrade != '');
	
	UPDATE companyinfocollect.employees e 
		SET 
		e.certificate_no = p_certNo,
		e.with_certificate = p_withCertificate,
		e.security_grade = concat_security_grade(e.security_grade, p_security_grade),
		e.security_grade_int = sum_of_powers(concat_security_grade(e.security_grade, p_security_grade), 0),
		e.LASTMODIFIED = IFNULL(p_last_updated_time,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 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))
BEGIN
	#插入职员表子过程
DECLARE p_certNo VARCHAR(50) DEFAULT NULL;
	DECLARE p_withCertificate TINYINT default 0;
	DECLARE p_securityGrade VARCHAR(50) DEFAULT NULL; -- 保安等级
	DECLARE p_security_master_grade VARCHAR(20) DEFAULT NULL; -- 保安师
	
	DECLARE p_att_site_id VARCHAR(200) DEFAULT NULL;
	DECLARE p_att_site_name VARCHAR(200) DEFAULT NULL;
	DECLARE p_att_site_supervise_office VARCHAR(200) DEFAULT NULL;
	DECLARE p_att_site_supervise_office_code VARCHAR(200) DEFAULT NULL;
	
	SELECT bi.ID,bi.ITEM_attendanceSiteName, bi.ITEM_officePoliceAddress,bi.ITEM_officePoliceAddressID INTO p_att_site_id,p_att_site_name,p_att_site_supervise_office,p_att_site_supervise_office_code
	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 = p_employee_id LIMIT 1;
	
	IF p_user_checked_status = '1' THEN
		SELECT tuc.number INTO p_certNo  
		FROM obpm2.tenant_user_credentials tuc 
		JOIN obpm2.tenant_user_credential_details tucd ON tuc.id = tucd.id 
		WHERE tuc.tenant_user_id = p_tenant_user_id 
		AND tuc.credential_type = 7
		AND tucd.active = 1
		AND tucd.state = '1'
		ORDER BY tuc.valid_date_from DESC LIMIT 1;
	END IF;
	
	IF p_certNo IS NOT NULL AND p_certNo != '' THEN
		SET p_withCertificate = 1;
	ELSE
		SET p_withCertificate = 0;
	END IF;
	
	IF p_user_checked_status = '1' THEN
		#获取职员等级证，以类似3；4；5的形式拼接
		SELECT GROUP_CONCAT(tucd.appraisalGrade SEPARATOR ';') 
		INTO p_securityGrade 
		FROM 
		obpm2.tenant_user_credentials tuc JOIN
		obpm2.tenant_user_credential_details tucd ON tuc.id = tucd.id 
		WHERE tuc.tenant_user_id = p_tenant_user_id 
		AND tuc.credential_type = 8
		AND tucd.active = 1
		AND tucd.state = '1'
		AND (tucd.appraisalGrade IS NOT NULL AND tucd.appraisalGrade != '');
	END IF;
	
	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;
	
	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
	)
	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,
	IF(p_org_approved_status='1',p_org_supervise_region_code,'#') AS region_id,
	p_employee_id AS employee_id,
	p_tenant_user_id AS user_id,
	IF(c.head_photo IS NULL OR c.head_photo = '','',concat( '[{"name":"","path":"', c.head_photo, '"}]')) AS profile_photo,
	IFNULL(u.name,'') AS name,
	(CASE
	WHEN ISNULL(c.credential_type) THEN
		NULL
	WHEN  c.credential_type IN ('0','1','2','3','4') THEN
		c.credential_type
	ELSE
		5
END) AS id_type,
	c.number AS id_no,
	u.telephone AS phone,
	(CASE u.sex 
	WHEN 1 THEN 1
	WHEN 2 THEN 2
	ELSE 0
	END) AS gender,
	IF(u.nation = '', NULL,u.nation) AS ethnic_group,
	(CASE 
	WHEN u.education IN ('小学', '1') THEN
		2
	WHEN u.education IN ('初中','2') THEN
		2
	WHEN u.education IN ('高中','3') THEN
		3
	WHEN u.education IN ('中专','4')THEN
		4
	WHEN u.education IN ('大专','5')THEN
		5
	WHEN u.education IN ('本科','6') THEN
		6
	WHEN u.education IN ('硕士研究生','硕士研究生及以上', '7') THEN
		7
	WHEN u.education IN ('博士','博士后','8') THEN
		7
	ELSE
		0
	END) AS education,
	u.birthdate AS birth_date,
	(
	CASE
	WHEN u.politics_status IN ('中共党员','01','1') THEN
		1
		WHEN u.politics_status IN ('中共预备党员','02','2' )THEN
		2
		WHEN u.politics_status IN ('共青团员','03', '3') THEN
		3
		WHEN u.politics_status IN('民革会员','04','4') THEN
		4
		WHEN u.politics_status IN ('民盟盟员','05','5') THEN
		5
		WHEN u.politics_status IN ('民建会员','06','6' )THEN
		6
		WHEN u.politics_status IN ('民进会员','07','7') THEN
		7
		WHEN u.politics_status IN ('农工党党员','08','8') THEN
		8
		WHEN u.politics_status IN ('致公党党员','09','9') THEN
		9
		WHEN u.politics_status IN ('九三学社社员','10') THEN
		10
		WHEN u.politics_status IN ('台盟盟员','11') THEN
		11
		WHEN u.politics_status IN('无党派民主人士', '12') THEN
		12	
		WHEN u.politics_status IN ('群众','13') THEN
		13	
	ELSE
		0
	END) AS political_status,
	(
	CASE 
	WHEN u.military_status IN ('未服','否','0') THEN
		1
	WHEN u.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
		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
	WHEN 0 THEN
		0
	ELSE
		1
	END) AS employed,
	IFNULL(p_employee_insure,0) AS insure_status,
	(CASE u.checked_status
	WHEN 1 THEN
		1
	ELSE
		0
	END) AS check_status,
	IFNULL(u.authenticated_status,0) AS auth_status,
	u.authenticated_time AS last_auth_time,
	u.authenticated_result AS auth_result,
	(CASE u.background_screening_status
	WHEN 1 THEN
		1
	WHEN 2 THEN
		2
	ELSE 0
	END) AS background_status,
	p_certNo AS certificate_no,
	p_withCertificate 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,
	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_sync_time,
	(CASE u.household_type 
	WHEN '农村' THEN 1  
	WHEN '城镇' THEN 2 
	WHEN '乡镇' THEN 2 
	WHEN '城市' THEN 2 
	ELSE 0 END)  AS residence,
	IF(u.stature='',NULL,u.stature) AS height,
	u.native_place AS birthplace,
	(CASE u.marital_status 
	WHEN '未婚' THEN 1  
	WHEN '已婚' THEN 2 
	WHEN '离异' THEN 3 
	WHEN '丧偶' THEN 4 
	WHEN '其他' THEN 5 
	ELSE 0 END) AS marriage,
	u.emergency_contact AS emergency_contact,
	 u.emergency_phone AS emergency_phone,
	 u.license_level AS driver_license,
	 u.work_year AS work_years,
		u.diseases_history AS medical_history,
		u.place_of_now_address AS address,
		c.valid_date_from AS id_issue_date,
		c.valid_date_to AS id_expire_date,
		c.address AS id_address,
		IF(c.front_photo IS NULL OR c.front_photo = '','',concat( '[{"name":"","path":"', c.front_photo, '"}]')) AS id_back_image,
		IF(c.reverse_photo IS NULL OR c.reverse_photo = '','',concat( '[{"name":"","path":"', c.reverse_photo, '"}]')) AS id_front_image,
		IF(c.head_photo IS NULL OR c.head_photo = '','',concat( '[{"name":"","path":"', c.head_photo, '"}]')) AS id_person_photo
	
	FROM obpm2.tenant_users u 
	LEFT JOIN obpm2.tenant_user_credentials c 
	ON u.selected_credential_id = c.id
	WHERE u.id = p_tenant_user_id;
	
	
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 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))
BEGIN
	#插入/更新职员表子过程
DECLARE p_certNo VARCHAR(50) DEFAULT NULL;
	DECLARE p_withCertificate TINYINT default 0;
	DECLARE p_securityGrade VARCHAR(50) DEFAULT NULL; -- 保安等级
	DECLARE p_security_master_grade VARCHAR(20) DEFAULT NULL; -- 保安师
	
	DECLARE p_att_site_id VARCHAR(200) DEFAULT NULL;
	DECLARE p_att_site_name VARCHAR(200) DEFAULT NULL;
	DECLARE p_att_site_supervise_office VARCHAR(200) DEFAULT NULL;
	DECLARE p_att_site_supervise_office_code VARCHAR(200) DEFAULT NULL;
	# 获取保安员的驻勤点信息
	SELECT bi.ID,bi.ITEM_attendanceSiteName, bi.ITEM_officePoliceAddress,bi.ITEM_officePoliceAddressID INTO p_att_site_id,p_att_site_name,p_att_site_supervise_office,p_att_site_supervise_office_code
	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 = p_employee_id LIMIT 1;
	
	# 获取保安员的资格证。类型为资格证（7）+ 生效的（active = 1，非逻辑删除数据） + 有效的（未吊销/撤销）
	IF p_user_checked_status = '1' THEN
		SELECT tuc.number INTO p_certNo  
		FROM obpm2.tenant_user_credentials tuc 
		JOIN obpm2.tenant_user_credential_details tucd ON tuc.id = tucd.id 
		WHERE tuc.tenant_user_id = p_tenant_user_id 
		AND tuc.credential_type = 7
		AND tucd.active = 1
		AND tucd.state = '1'
		ORDER BY tuc.valid_date_from DESC LIMIT 1;
	END IF;
	
	IF p_certNo IS NOT NULL AND p_certNo != '' THEN
		SET p_withCertificate = 1;
	ELSE
		SET p_withCertificate = 0;
	END IF;
	
	
	IF p_user_checked_status = '1' THEN
		#获取职员等级证，以类似3；4；5的形式拼接
		SELECT GROUP_CONCAT(tucd.appraisalGrade SEPARATOR ';') 
		INTO p_securityGrade 
		FROM 
		obpm2.tenant_user_credentials tuc JOIN
		obpm2.tenant_user_credential_details tucd ON tuc.id = tucd.id 
		WHERE tuc.tenant_user_id = p_tenant_user_id 
		AND tuc.credential_type = 8
		AND tucd.active = 1
		AND tucd.state = '1'
		AND (tucd.appraisalGrade IS NOT NULL AND tucd.appraisalGrade != '');
	END IF;
	
	#获取保安师的等级，并和等级证拼接到一起，以类似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;
	
	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
	)
	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
	
	
	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,
	IF(p_org_approved_status='1',p_org_supervise_region_code,'#') AS region_id,
	p_employee_id AS employee_id,
	p_tenant_user_id AS user_id,
	IF(c.head_photo IS NULL OR c.head_photo = '','',concat( '[{"name":"","path":"', c.head_photo, '"}]')) AS profile_photo,
	IFNULL(u.name,'') AS name,
	(CASE
	WHEN ISNULL(c.credential_type) THEN
		NULL
	WHEN  c.credential_type IN ('0','1','2','3','4') THEN
		c.credential_type
	ELSE
		5
END) AS id_type,
	c.number AS id_no,
	u.telephone AS phone,
	(CASE u.sex 
	WHEN 1 THEN 1
	WHEN 2 THEN 2
	ELSE 0
	END) AS gender,
	IF(u.nation = '', NULL,u.nation) AS ethnic_group,
	(CASE 
	WHEN u.education IN ('小学', '1') THEN
		2
	WHEN u.education IN ('初中','2') THEN
		2
	WHEN u.education IN ('高中','3') THEN
		3
	WHEN u.education IN ('中专','4')THEN
		4
	WHEN u.education IN ('大专','5')THEN
		5
	WHEN u.education IN ('本科','6') THEN
		6
	WHEN u.education IN ('硕士研究生','硕士研究生及以上', '7') THEN
		7
	WHEN u.education IN ('博士','博士后','8') THEN
		7
	ELSE
		0
	END) AS education,
	u.birthdate AS birth_date,
	(
	CASE
	WHEN u.politics_status IN ('中共党员','01','1') THEN
		1
		WHEN u.politics_status IN ('中共预备党员','02','2' )THEN
		2
		WHEN u.politics_status IN ('共青团员','03', '3') THEN
		3
		WHEN u.politics_status IN('民革会员','04','4') THEN
		4
		WHEN u.politics_status IN ('民盟盟员','05','5') THEN
		5
		WHEN u.politics_status IN ('民建会员','06','6' )THEN
		6
		WHEN u.politics_status IN ('民进会员','07','7') THEN
		7
		WHEN u.politics_status IN ('农工党党员','08','8') THEN
		8
		WHEN u.politics_status IN ('致公党党员','09','9') THEN
		9
		WHEN u.politics_status IN ('九三学社社员','10') THEN
		10
		WHEN u.politics_status IN ('台盟盟员','11') THEN
		11
		WHEN u.politics_status IN('无党派民主人士', '12') THEN
		12	
		WHEN u.politics_status IN ('群众','13') THEN
		13	
	ELSE
		0
	END) AS political_status,
	(
	CASE 
	WHEN u.military_status IN ('未服','否','0') THEN
		1
	WHEN u.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
		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
	WHEN 0 THEN
		0
	ELSE
		1
	END) AS employed,
	IFNULL(p_employee_insure,0) AS insure_status,
	(CASE u.checked_status
	WHEN 1 THEN
		1
	ELSE
		0
	END) AS check_status,
	IFNULL(u.authenticated_status,0) AS auth_status,
	u.authenticated_time AS last_auth_time,
	u.authenticated_result AS auth_result,
	(CASE u.background_screening_status
	WHEN 1 THEN
		1
	WHEN 2 THEN
		2
	ELSE 0
	END) AS background_status,
	p_certNo AS certificate_no,
	p_withCertificate 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,
	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_sync_time,
	(CASE u.household_type 
	WHEN '农村' THEN 1  
	WHEN '城镇' THEN 2 
	WHEN '乡镇' THEN 2 
	WHEN '城市' THEN 2 
	ELSE 0 END)  AS residence,
	IF(u.stature='',NULL,u.stature) AS height,
	u.native_place AS birthplace,
	(CASE u.marital_status 
	WHEN '未婚' THEN 1  
	WHEN '已婚' THEN 2 
	WHEN '离异' THEN 3 
	WHEN '丧偶' THEN 4 
	WHEN '其他' THEN 5 
	ELSE 0 END) AS marriage,
	u.emergency_contact AS emergency_contact,
	 u.emergency_phone AS emergency_phone,
	 u.license_level AS driver_license,
	 u.work_year AS work_years,
		u.diseases_history AS medical_history,
		u.place_of_now_address AS address,
		c.valid_date_from AS id_issue_date,
		c.valid_date_to AS id_expire_date,
		c.address AS id_address,
		IF(c.front_photo IS NULL OR c.front_photo = '','',concat( '[{"name":"","path":"', c.front_photo, '"}]')) AS id_back_image,
		IF(c.reverse_photo IS NULL OR c.reverse_photo = '','',concat( '[{"name":"","path":"', c.reverse_photo, '"}]')) AS id_front_image,
		IF(c.head_photo IS NULL OR c.head_photo = '','',concat( '[{"name":"","path":"', c.head_photo, '"}]')) AS id_person_photo
	
	FROM obpm2.tenant_users u 
	LEFT JOIN obpm2.tenant_user_credentials c 
	ON u.selected_credential_id = c.id
	WHERE u.id = p_tenant_user_id
	) AS t
	
	ON DUPLICATE KEY UPDATE
	
	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,

	LASTMODIFIED = t.last_modified,
	last_sync_time = CURRENT_TIMESTAMP;
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 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))
BEGIN
	#更新职员
	UPDATE companyinfocollect.employees 
	SET item_region_id = 
			IF(p_org_approved_status='1',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
			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,

	LASTMODIFIED = IFNULL(p_employee_last_update_time,CURRENT_TIMESTAMP),
	last_sync_time = CURRENT_TIMESTAMP
	WHERE employee_id = p_employee_id;
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 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
	where i.ITEM_COMPANY_ID = p_company_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 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
)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,
	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
 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'),
		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 = 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);
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 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
	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 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
)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,
	 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
 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'),
	 ITEM_ADDRESS = c.ITEM_ADDRESS,
	 ITEM_NATIONALITY = (SELECT itemvalue from baibaodunflow.sys_dictionary where itemKey = c.ITEM_NATIONALITY and codeType = 'nationality'),
	 item_household = c.ITEM_HOUSEHOLD,
	 ITEM_COMPANY_NAME = o_Name,
	 LASTMODIFIED = 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);
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 PROCEDURE `proc_sync_rd_subcompany_info_collect_by_companytel`(IN p_company_id VARCHAR (300))
BEGIN
	#当总公司的法人和电话发生变化时，触发更新
UPDATE companyinfocollect.sub_company i,obpm2.tenant_companies m
SET i.item_group_legal_person =  m.legal_person_name,
i.item_group_phone = m.legal_person_telephone
where i.item_group_company_id = m.ID and  m.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 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))
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
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 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))
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
)SELECT 
    CONCAT(SUBSTRING_INDEX(c.ID,'--',1),'--__rn71vIqC8w4NLHgXuVu'),
		o_Name,
		a.legal_person_name,
		a.legal_person_telephone,
		o_place_of_register_province_name,
		o_place_of_register_address,
		o_place_of_business_province_name,
		o_place_of_business_address,
		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,
		(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
	FROM baibaodunflow.tlk_subcompanyinformation c left join obpm2.tenant_companies a on c.DOMAINID = a.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 = a.legal_person_telephone,
   item_group_register_address = o_place_of_register_province_name,
   item_group_register_addr_detail = o_place_of_register_address,
   item_group_office_address = 	o_place_of_business_province_name,
   item_group_office_addr_detail = o_place_of_business_address,
   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 = 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);
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 PROCEDURE `proc_sync_rd_vehicle_info_collect_by_company_v2`(IN p_company_id VARCHAR (300),o_company_police VARCHAR(200),o_company_police_code VARCHAR(200))
BEGIN
	#当公司的监管机构和编码发生变化时，触发更新
update companyinfocollect.tlk_vehicle c
set c.ITEM_company_police = o_company_police, c.ITEM_company_police_code = o_company_police_code
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 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
)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,
	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
 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 = 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);
		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 PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_credentials`(IN p_id VARCHAR(300))
BEGIN 
	UPDATE companyinfocollect.tlk_attendance_site_person_info a,obpm2.tenant_employees b,obpm2.tenant_users c,obpm2.tenant_user_credentials d
	SET 
	a.ITEM_DOCUMENT_ID = d.number
	WHERE a.ITEM_SECURITY_ID = b.id AND b.tenant_user_id = c.id AND c.id = d.tenant_user_id AND d.selected = '1'
	AND d.id = p_id;
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 PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_details`(IN p_id VARCHAR(300))
BEGIN
	UPDATE companyinfocollect.tlk_attendance_site_person_info a,obpm2.tenant_employees b,obpm2.tenant_user_credential_details c
	SET a.ITEM_CERTIFICATE_STATUS = CASE WHEN (c.state IS NULL OR c.state = '') THEN '未持证' ELSE '已持证' END,
	a.ITEM_CERTIFICATE_NUMBER = CASE WHEN (c.zsbh IS NULL OR c.zsbh = '') THEN '无' ELSE c.zsbh END
	WHERE a.ITEM_SECURITY_ID = b.id AND b.tenant_user_id = c.tenant_user_id
	AND c.id = p_id;
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 PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_employees`(IN p_id VARCHAR(300))
BEGIN
	UPDATE companyinfocollect.tlk_attendance_site_person_info a,obpm2.tenant_employees b,obpm2.tenant_departments c
	SET a.ITEM_ENTRY_TIME = b.hired_date,
	a.ITEM_INSURANCE_STATUS = CASE WHEN b.insure = '0' THEN '否' ELSE '是' END,
	a.ITEM_DEPT_NAME = c.`name`
	WHERE a.ITEM_SECURITY_ID = b.id AND b.department_id = c.id AND b.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 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))
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`
		) SELECT
		'companyinfocollect',
		'tlk_attendance_site_person_info',
		'ID',
		 ID,
		 DOMAINID,
		 LASTMODIFIED,
		 p_organization_supervise_region_code,
		 now(),
		 p_before_id
		 FROM `companyinfocollect`.`tlk_attendance_site_person_info`
		 WHERE ITEM_RECORD_ID = p_before_id;
		 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`
			) SELECT 
				a.LASTMODIFIED, #LASTMODIFIED
				a.FORMNAME, #FORMNAME
				a.AUTHOR, #AUTHOR
				a.AUTHORDEPTID, #AUTHORDEPTID
				a.AUTHOR_DEPT_INDEX, #AUTHOR_DEPT_INDEX
				a.AUTHOR_USER_INDEX, #AUTHOR_USER_INDEX
				now(), #CREATED 此处为这条数据创建的时间
				'__ZgEvBwBNMrRz3xT0OvU', #FORMID 固定为行业信息查询中的formId
				a.SUBFORMIDS, #SUBFORMIDS
				a.INITIATOR, #SUBFORMIDS
				a.ISTMP, #ISTMP
				a.VERSIONS, #VERSIONS
				'__DOoeSJp26wVqbyYYf50', #APPLICATIONID 固定为行业信息查询的应用ID
				a.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) AS domain_id, #根据参数p_organization_supervise_region_code 来区别地区
				a.ITEM_SECURITYNAME, #ITEM_SECURITY_NAME
				a.ITEM_SECURITYID, #ITEM_SECURITY_ID
				a.ITEM_ATTENDANCESITEID, #ITEM_ATTENDANCE_SITE_ID
				a.ITEM_DOCUMENTTYPE, #ITEM_DOCUMENT_TYPE
				a.ITEM_DOCUMENTID, #ITEM_DOCUMENT_ID
				a.ITEM_PHONE, #ITEM_PHONE
				a.ITEM_DEPTNAME, #ITEM_DEPT_NAME
				a.ITEM_JOBTYPE, #ITEM_JOB_TYPE
				CASE WHEN c.authenticated_status = '0' THEN '未认证' 
				WHEN c.authenticated_status = '1' THEN '认证中'
				WHEN c.authenticated_status = '2' THEN '认证失败'
				WHEN c.authenticated_status = '3' THEN '已认证' END, #ITEM_IS_CERTIFIED 认证状态
				CASE WHEN b.insure = '0' THEN '否' ELSE '是' END, #ITEM_INSURANCE_STATUS 投保状态
				a.ITEM_CREATEDUSER, #ITEM_CREATED_USER
				a.ITEM_DOMAIN_ID, #ITEM_DOMAIN_ID
				REPLACE(a.ID,'__sgTQqGCJDqTyPf6vN1Q','__ZgEvBwBNMrRz3xT0OvU'), #ID 将原来应用的formId 替换成 行业信息查询中相应的formId
				a.ID, #ITEM_RECORD_ID 来源表中的ID
				a.CREATED,#ITEM_CREATED_TIME 
				b.hired_date,#ITEM_ENTRY_TIME
				CASE WHEN (e.state IS NULL OR e.state = '') THEN '未持证' ELSE '已持证' END, #ITEM_CERTIFICATE_STATUS 持证状态
				CASE WHEN (e.zsbh IS NULL OR e.zsbh = '') THEN '无' ELSE e.zsbh END, #ITEM_CERTIFICATE_NUMBER 证书编号
				CASE WHEN c.checked_status = '0' THEN '未提交'
				WHEN c.checked_status = '1' THEN '已核验'
				WHEN c.checked_status = '2' THEN '已提交' END, #ITEM_DOC_VERIFICATION 实名状态
				now() #last_sync_time 数据更新时间
				FROM baibaodunflow.tlk_attendance_site_person_info a
				LEFT JOIN obpm2.tenant_employees b ON a.ITEM_SECURITYID = b.id #baibaodunflow.tlk_attendance_site_person_info中的ITEM_SECURITYID 与 obpm2.tenant_employees 的id对应
				LEFT JOIN obpm2.tenant_users c ON b.tenant_user_id = c.id #obpm2.tenant_employees中的tenant_user_id 与 obpm2.tenant_users 的id对应
				LEFT JOIN obpm2.tenant_user_credentials d ON d.tenant_user_id = b.tenant_user_id AND d.selected = 1 #obpm2.tenant_user_credentials中的tenant_user_id 与 obpm2.tenant_employees的tenant_user_id对应
				LEFT JOIN obpm2.tenant_user_credential_details e ON e.id = d.id #obpm2.tenant_user_credentials中的id 与 obpm2.tenant_user_credential_details 的id对应
				WHERE a.ID = p_after_id #AND b.tenant_user_id IS NOT NULL
				ON DUPLICATE KEY UPDATE 
				`LASTMODIFIED` = a.LASTMODIFIED,
				`FORMNAME` = a.FORMNAME,
				`AUTHOR` = a.AUTHOR,
				`AUTHORDEPTID` = a.AUTHORDEPTID,
				`AUTHOR_DEPT_INDEX` = a.AUTHOR_DEPT_INDEX,
				`AUTHOR_USER_INDEX` = a.AUTHOR_USER_INDEX,
				`CREATED` = a.CREATED,
				`FORMID` = '__ZgEvBwBNMrRz3xT0OvU',
				`SUBFORMIDS` = a.SUBFORMIDS,
				`INITIATOR` = a.INITIATOR,
				`ISTMP` = a.ISTMP,
				`VERSIONS` = a.VERSIONS,
				`APPLICATIONID` = '__DOoeSJp26wVqbyYYf50',
				`LASTMODIFIER` = a.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` = a.ITEM_SECURITYNAME,
				`ITEM_SECURITY_ID` = a.ITEM_SECURITYID,
				`ITEM_ATTENDANCE_SITE_ID` = a.ITEM_ATTENDANCESITEID,
				`ITEM_DOCUMENT_TYPE` = a.ITEM_DOCUMENTTYPE,
				`ITEM_DOCUMENT_ID` = a.ITEM_DOCUMENTID,
				`ITEM_PHONE` = a.ITEM_PHONE,
				`ITEM_DEPT_NAME` = a.ITEM_DEPTNAME,
				`ITEM_JOB_TYPE` = a.ITEM_JOBTYPE,
				`ITEM_IS_CERTIFIED` = CASE WHEN c.authenticated_status = '0' THEN '未认证' 
				WHEN c.authenticated_status = '1' THEN '认证中'
				WHEN c.authenticated_status = '2' THEN '认证失败'
				WHEN c.authenticated_status = '3' THEN '已认证' END,
				`ITEM_INSURANCE_STATUS` = CASE WHEN b.insure = '0' THEN '否' ELSE '是' END,
				`ITEM_CREATED_USER` = a.ITEM_CREATEDUSER,
				`ITEM_DOMAIN_ID` = a.ITEM_DOMAIN_ID,
				`ID` = REPLACE(a.ID,'__sgTQqGCJDqTyPf6vN1Q','__ZgEvBwBNMrRz3xT0OvU'),
				`ITEM_RECORD_ID` = a.ID,
				`ITEM_CREATED_TIME` = a.CREATED,
				`ITEM_ENTRY_TIME` = b.hired_date,
				`ITEM_CERTIFICATE_STATUS` = CASE WHEN (e.state IS NULL OR e.state = '') THEN '未持证' ELSE '已持证' END,
				`ITEM_CERTIFICATE_NUMBER` = CASE WHEN (e.zsbh IS NULL OR e.zsbh = '') THEN '无' ELSE e.zsbh END,
				`ITEM_DOC_VERIFICATION` = CASE WHEN c.checked_status = '0' THEN '未提交'
				WHEN c.checked_status = '1' THEN '已核验'
				WHEN c.checked_status = '2' THEN '已提交' END,
				`last_sync_time` = now();
		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 PROCEDURE `proc_sync_tlk_attendance_site_person_info_by_users`(IN p_id VARCHAR(300))
BEGIN
	UPDATE companyinfocollect.tlk_attendance_site_person_info a,obpm2.tenant_employees b,obpm2.tenant_users c
	SET a.ITEM_DOC_VERIFICATION = CASE WHEN c.checked_status = '0' THEN '未提交'
				WHEN c.checked_status = '1' THEN '已核验'
				WHEN c.checked_status = '2' THEN '已提交' END,
	a.ITEM_IS_CERTIFIED = CASE WHEN c.authenticated_status = '0' THEN '未认证' 
				WHEN c.authenticated_status = '1' THEN '认证中'
				WHEN c.authenticated_status = '2' THEN '认证失败'
				WHEN c.authenticated_status = '3' THEN '已认证' END,
	a.item_phone = c.telephone
	WHERE a.ITEM_SECURITY_ID = b.id AND b.tenant_user_id = c.id 
	AND c.id = p_id;
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 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;
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,
		a.LASTMODIFIED,
		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 = a.LASTMODIFIED,
		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 PROCEDURE `proc_sync_tlk_grade_certificate_by_user_credentials`(IN p_id VARCHAR(300))
BEGIN
	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
	)SELECT 
		b.update_time,#`LASTMODIFIED` 证书详情表数据的更新时间
		a.created_time,#`CREATED` 证书表数据的创建时间
		'__TnCvHMSNYTBlxqU5uc0',#`FORMID` 固定为行业信息查询页面表单的formid
		'__DOoeSJp26wVqbyYYf50',#`APPLICATIONID` 固定为行业信息应用id
		(CASE 
			WHEN b.areaCode LIKE '11%' THEN 
				'xduDIOSsi06qmfpig0A'
			WHEN b.areaCode LIKE '43%' THEN 
				'__UDa4uPMdcOYgP7HETaf'
			WHEN b.areaCode LIKE '64%' THEN 
				'qS6PXrA7'
			ELSE
				NULL
			END), #`DOMAINID` 根据证书详情表中地区码区别地区
			b.companyId,#`ITEM_domain_id` 证书详情表中的公司id
			a.id,#`ITEM_record_id` 
			a.`name`,#`ITEM_security_name`
			b.idnum,#ITEM_document_id 
			b.zsbh,#ITEM_certificate_no 证书详情表中的证书编号
			b.companyName,#`ITEM_company_name_to_get` 获取证书的公司名称
			b.trainName,#`ITEM_cultivate_company`, 训练公司的名称
			(CASE 
			WHEN a.id LIKE '%__jlNfVcjppSliMYH6FkJ%' THEN 
				REPLACE(a.id,'__jlNfVcjppSliMYH6FkJ','__TnCvHMSNYTBlxqU5uc0')
			ELSE
				CONCAT(a.id,'--__TnCvHMSNYTBlxqU5uc0')
			END),#`ID`
			CASE WHEN c.`status` = '0' THEN '在职' ELSE '离职' END,#ITEM_EMPLOYMENT_STATUS
			b.trainStartTime,# ITEM_TRAINING_TIME_START
			b.trainEndTime,# ITEM_TRAINING_TIME_END
			b.appraisalTime,# ITEM_APPRAISAL_TIME
			b.appraisalGrade,# ITEM_CERTIFICATE_LEVEL
			b.create_time #ITEM_CREATED_TIME
		FROM obpm2.tenant_user_credentials a LEFT JOIN obpm2.tenant_user_credential_details b ON a.id = b.id
		LEFT JOIN obpm2.tenant_employees c ON a.tenant_user_id = c.tenant_user_id
		WHERE a.credential_type = '8' AND b.certificateType = '2' and b.active = 1  AND a.id = p_id
		ON DUPLICATE KEY UPDATE 
		`LASTMODIFIED` = b.update_time,
		`CREATED` = a.created_time,
		`FORMID` = '__TnCvHMSNYTBlxqU5uc0',
		`APPLICATIONID` = '__DOoeSJp26wVqbyYYf50',
		`DOMAINID` = (CASE 
			WHEN b.areaCode LIKE '11%' THEN 
				'xduDIOSsi06qmfpig0A'
			WHEN b.areaCode LIKE '43%' THEN 
				'__UDa4uPMdcOYgP7HETaf'
			WHEN b.areaCode LIKE '64%' THEN 
				'qS6PXrA7'
			ELSE
				NULL
			END),
		`ITEM_domain_id` = b.companyId,
		`ITEM_record_id` = a.id,
		`ITEM_security_name` = a.`name`,
		`ITEM_document_id` = b.idnum,
		`ITEM_certificate_no` = b.zsbh,
		`ITEM_company_name_to_get` = b.companyName,
		`ITEM_cultivate_company` = b.trainName,
		`ID` = (CASE 
			WHEN a.id LIKE '%__jlNfVcjppSliMYH6FkJ%' THEN 
				REPLACE(a.id,'__jlNfVcjppSliMYH6FkJ','__TnCvHMSNYTBlxqU5uc0')
			ELSE
				CONCAT(a.id,'--__TnCvHMSNYTBlxqU5uc0')
			END),
		 ITEM_EMPLOYMENT_STATUS = CASE WHEN c.`status` = '0' THEN '在职' ELSE '离职' END,
		 ITEM_TRAINING_TIME_START = b.trainStartTime,
		 ITEM_TRAINING_TIME_END = b.trainEndTime,
		 ITEM_APPRAISAL_TIME = b.appraisalTime,
		 ITEM_CERTIFICATE_LEVEL = b.appraisalGrade,
		 ITEM_CREATED_TIME = b.create_time;
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 PROCEDURE `proc_sync_tlk_qualification_certificate_by_user_credentials`(IN p_id VARCHAR(300))
BEGIN
	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
	)SELECT 
		b.update_time,#`LASTMODIFIED`
		a.created_time,#`CREATED`
		'__GCpKdHqjqaN6yphC9xA',#`FORMID`
		'__DOoeSJp26wVqbyYYf50',#`APPLICATIONID`
		(CASE 
			WHEN b.areaCode LIKE '11%' THEN 
				'xduDIOSsi06qmfpig0A'
			WHEN b.areaCode LIKE '43%' THEN 
				'__UDa4uPMdcOYgP7HETaf'
			WHEN b.areaCode LIKE '64%' THEN 
				'qS6PXrA7'
			ELSE
				NULL
			END), #`DOMAINID`
			b.companyId,#`ITEM_domain_id`
			a.id,#`ITEM_record_id`
			#c.`status`,#`ITEM_employment_status`
			CASE WHEN b.isOldData = '1' THEN b.isOldData ELSE '0' END,#`ITEM_is_history`
			a.`name`,#`ITEM_security_name`
			b.idnum,#ITEM_document_id
			b.sex,#ITEM_sex
			b.phone,#`ITEM_phone`
			b.state,#ITEM_status
			b.cancelReason,#ITEM_cancel_remark
			b.zsbh,#ITEM_certificate_no
			b.cancelDate,#ITEM_cancel_date
			b.fzjgmc,#ITEM_certificate_from
			b.fzrq,#ITEM_certificate_date
			b.companyName,#`ITEM_company_name_to_get`
			b.trainName,#`ITEM_cultivate_company`,
			(CASE 
			WHEN a.id LIKE '%__jlNfVcjppSliMYH6FkJ%' THEN 
				REPLACE(a.id,'__jlNfVcjppSliMYH6FkJ','__GCpKdHqjqaN6yphC9xA')
			ELSE
				CONCAT(a.id,'--__GCpKdHqjqaN6yphC9xA')
			END),#`ID`
			b.cancelOrgName #ITEM_CANCEL_INSTITUTION
		FROM obpm2.tenant_user_credentials a LEFT JOIN obpm2.tenant_user_credential_details b ON a.id = b.id
		#LEFT JOIN obpm2.tenant_employees c ON a.tenant_user_id = c.tenant_user_id
		WHERE a.credential_type = '7' AND b.certificateType = '1'  and b.active = 1 AND a.id = p_id
		ON DUPLICATE KEY UPDATE 
		`LASTMODIFIED` = b.update_time,
		`CREATED` = a.created_time,
		`ITEM_domain_id` = b.companyId,
		`ITEM_record_id`= a.id,
		#`ITEM_employment_status` = c.`status`,
		`ITEM_is_history` = CASE WHEN b.isOldData = '1' THEN b.isOldData ELSE '0' END,
		`ITEM_security_name` = a.`name`,
		`ITEM_document_id` = b.idnum,
		`ITEM_sex` = b.sex,
		`ITEM_phone` = b.phone,
		`ITEM_status` = b.state,
		`ITEM_cancel_remark` = b.cancelReason,
		`ITEM_certificate_no` = b.zsbh,
		`ITEM_cancel_date` = b.cancelReason,
		`ITEM_certificate_from` = b.fzjgmc,
		`ITEM_certificate_date` = b.fzrq,
		`ITEM_company_name_to_get` = b.companyName,
		`ITEM_cultivate_company` = b.trainName,
		 ITEM_CANCEL_INSTITUTION = b.cancelOrgName;
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 PROCEDURE `proc_sync_tlk_qualification_grade_certificate_by_details`(IN p_id VARCHAR(300),IN credentialType VARCHAR(300))
BEGIN
	IF credentialType = '1'
	THEN 
		UPDATE companyinfocollect.tlk_qualification_certificate a,obpm2.tenant_user_credential_details b
		SET a.ITEM_COMPANY_NAME_TO_GET = b.companyName,
				a.ITEM_CULTIVATE_COMPANY = b.trainName
		WHERE a.ITEM_record_id = b.id AND b.id = p_id;
	ELSEIF credentialType = '2'
	THEN 
		UPDATE companyinfocollect.tlk_grade_certificate a,obpm2.tenant_user_credential_details b
		SET a.ITEM_COMPANY_NAME_TO_GET = b.companyName,
				a.ITEM_CULTIVATE_COMPANY = b.trainName
		WHERE a.ITEM_record_id = b.id AND b.id = p_id;
	END IF;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for sum_of_powers
-- ----------------------------
DROP FUNCTION IF EXISTS `sum_of_powers`;
delimiter ;;
CREATE FUNCTION `sum_of_powers`(str VARCHAR(50), 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 ;

SET FOREIGN_KEY_CHECKS = 1;