# 1.归集保安员合同信息 
			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`,
			`last_sync_time`
			) select 
			CONCAT(otc.id,'--__sRsEao1S9S3lcGTjJCY'), #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表获取
			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
			otc.creator_created_time, #create_time
			now(), #LASTMODIFIED
			IFNULL(otu.id,''),# user_id 保安员用户id 从obpm2.tenant_users表获取
			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
			ON DUPLICATE KEY UPDATE
			`contract_id` = CONCAT(otc.id,'--__sRsEao1S9S3lcGTjJCY'),
			`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` = 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` = otc.creator_created_time,
			`LASTMODIFIED` = now(),
			`user_id` = IFNULL(otu.id,''),
			`last_sync_time` = now(); 
			

# 2.驻勤点人员详情信息归集	
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`,
				`ITEM_SECURITY_NAME`,
				`ITEM_SECURITY_ID`,
				`ITEM_ATTENDANCE_SITE_ID`,
				`ITEM_DOCUMENT_TYPE`,
				`ITEM_DOCUMENT_ID`,
				`ITEM_PHONE`,
				`ITEM_DEPT_NAME`,
				`ITEM_JOB_TYPE`,
				`ITEM_IS_CERTIFIED`,
				`ITEM_INSURANCE_STATUS`,
				`ITEM_CREATED_USER`,
				`ITEM_DOMAIN_ID`,
				`ID`,
				`ITEM_RECORD_ID`,
				`ITEM_CREATED_TIME`,
				`ITEM_ENTRY_TIME`,
				`ITEM_CERTIFICATE_STATUS`,
				`ITEM_CERTIFICATE_NUMBER`,
				`ITEM_DOC_VERIFICATION`,
				`last_sync_time`,
				`item_employment_status`,
				`ITEM_USER_ID`
			) SELECT 
				now(), #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
				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.zsbh IS NULL OR e.zsbh = '' OR c.checked_status <> '1') 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 实名状态
				CASE WHEN c.checked_status = '1' THEN '已核验'
				ELSE '未核验' END, #ITEM_DOC_VERIFICATION 实名状态
				now(), #last_sync_time 数据更新时间
				CASE WHEN b.`status` = '0' THEN '1' ELSE '0' END, #`item_employment_status` 职员就离职状态
				c.id
				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.id = b.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.idnum = d.number AND e.certificateType ='1' #obpm2.tenant_user_credentials中的id 与 obpm2.tenant_user_credential_details 的id对应
				ON DUPLICATE KEY UPDATE 
				`LASTMODIFIED` = now(),
				`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,
				`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.zsbh IS NULL OR e.zsbh = '') 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 = '1' THEN '已核验'
				ELSE '未核验' END,
				`last_sync_time` = now(),
				`item_employment_status` = CASE WHEN b.`status` = '0' THEN '1' ELSE '0' END,
				`ITEM_USER_ID` = c.id;
				
# 3.归集资格证信息
	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 
		now(),#`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
		ON DUPLICATE KEY UPDATE 
		`LASTMODIFIED` = now(),
		`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.cancelDate,
		`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;
		 
# 4.等级证信息归集（预发布环境有6万多条数据）
	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 
		now(),#`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
		ON DUPLICATE KEY UPDATE 
		`LASTMODIFIED` = now(),
		`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;