UPDATE obpm2.tenant_organizations o, baibaodunflow.tlk_companyinformation c
SET o.`code`=c.DOMAINID,
o.`name`=c.ITEM_companyName,
o.place_of_business_address=c.ITEM_officeAddressDetail,
o.place_of_business_city_code=c.ITEM_place_of_business_city_code,
o.place_of_business_district_code=c.ITEM_place_of_business_district_code,
o.place_of_business_province_code=c.ITEM_place_of_business_province_code,
o.place_of_business_city_name=(select ct.ITEM_NAME from baibaodunflow.tlk_citylevel ct where ct.ITEM_NO = c.ITEM_place_of_business_city_code),
o.place_of_business_district_name=(select ct.ITEM_NAME from baibaodunflow.tlk_citylevel ct where ct.ITEM_NO = c.ITEM_place_of_business_district_code),
o.place_of_business_province_name=(select ct.ITEM_NAME from baibaodunflow.tlk_citylevel ct where ct.ITEM_NO = c.ITEM_place_of_business_province_code),
o.place_of_register_address=c.ITEM_registerAddressDetail,
o.place_of_register_city_code=c.ITEM_place_of_register_city_code,
o.place_of_register_district_code=c.ITEM_place_of_register_district_code,
o.place_of_register_province_code=c.ITEM_place_of_register_province_code,
o.place_of_register_city_name=(select ct.ITEM_NAME from baibaodunflow.tlk_citylevel ct where ct.ITEM_NO = c.ITEM_place_of_register_city_code),
o.place_of_register_district_name=(select ct.ITEM_NAME from baibaodunflow.tlk_citylevel ct where ct.ITEM_NO = c.ITEM_place_of_register_district_code),
o.place_of_register_province_name=(select ct.ITEM_NAME from baibaodunflow.tlk_citylevel ct where ct.ITEM_NO = c.ITEM_place_of_register_province_code),
o.industry_detail_type=if(c.ITEM_securityType='',null,c.ITEM_securityType),
o.type=c.ITEM_isSub,
o.fax=c.ITEM_companyFax,
o.telephone=c.ITEM_companyPhone,
o.`status`=IFNULL(( 
		SELECT 
			case 
					when c.ITEM_companyStatus = '正常' then 1 
					when c.ITEM_companyStatus = '注销' then 2 
					when c.ITEM_companyStatus = '撤销' then 3
					when c.ITEM_companyStatus = '吊销' then 4
			end		 
	),1)
where o.id=c.DOMAINID;

UPDATE obpm2.tenant_companies os, baibaodunflow.tlk_companyinformation c
SET 
os.unify_social_credit_code=IFNULL(c.ITEM_registerID,os.unify_social_credit_code),
os.unify_social_credit_code_file=IF(JSON_VALID(c.ITEM_registerPhoto) = 0, os.unify_social_credit_code_file, IF(REPLACE(JSON_EXTRACT(c.ITEM_registerPhoto, '$[0].path'), '"', '') = 'null', os.unify_social_credit_code_file, REPLACE(JSON_EXTRACT(c.ITEM_registerPhoto, '$[0].path'), '"', ''))),
os.parent_name=c.ITEM_groupName,
os.parent_certificate_type=IF(c.ITEM_groupDocumentType is not null, 0, null),
os.parent_certificate_number=c.ITEM_groupDocumentNO,
os.parent_legal_person_name=c.ITEM_groupLegalPerson,
os.parent_legal_person_telephone=c.ITEM_groupPhone,
os.economic_type=IFNULL(if(c.ITEM_economicType='',9,c.ITEM_economicType),9),
os.nature=IFNULL(if(c.ITEM_companyType='',4,c.ITEM_companyType),4),
os.service_scope=companyinfocollect.sum_of_powers(companyinfocollect.business_scope_transfer(c.ITEM_businessScope), 1),
os.service_scope_description=c.ITEM_businessScope,
os.registered_capital=IFNULL(c.ITEM_registeredCapital,os.registered_capital),
os.fixed_capital=c.ITEM_fixedAssets,
os.annual_profit_tax=c.ITEM_profitTaxes,
os.annual_sales_volume=c.ITEM_annuaTurnover,
os.certificate_type=0,
os.service_permit_number=IF(c.ITEM_officeType = '0102', c.ITEM_securityServicePermitNo, if(c.ITEM_officeType = '0103', c.ITEM_securityTrainPermitNo, os.service_permit_number)),
os.service_permit_attachment=IF(c.ITEM_officeType = '0102', IF(JSON_VALID(c.ITEM_securityServicePhoto) = 0, os.service_permit_attachment, IF(REPLACE(JSON_EXTRACT(c.ITEM_securityServicePhoto, '$[0].path'), '"', '') = 'null', os.service_permit_attachment, REPLACE(JSON_EXTRACT(c.ITEM_securityServicePhoto, '$[0].path'), '"', ''))), if(c.ITEM_officeType = '0103', IF(JSON_VALID(c.ITEM_securityTrainPhoto) = 0, os.service_permit_attachment, IF(REPLACE(JSON_EXTRACT(c.ITEM_securityTrainPhoto, '$[0].path'), '"', '') = 'null', os.service_permit_attachment, REPLACE(JSON_EXTRACT(c.ITEM_securityTrainPhoto, '$[0].path'), '"', ''))), os.service_permit_attachment)),
os.name_of_issuing_authority=IF(c.ITEM_officeType = '0102', c.ITEM_issuingSecurityService, if(c.ITEM_officeType = '0103', c.ITEM_issuingTrainPermit, os.name_of_issuing_authority)),
os.first_issue_service_permit=IF(c.ITEM_officeType = '0102', c.ITEM_issueServiceDate, if(c.ITEM_officeType = '0103', c.ITEM_issueTrainDate, os.first_issue_service_permit)),
os.security_approval_number=c.ITEM_approval_number,
os.train_content=c.ITEM_trainScope,
os.security_preparedness_rating=( 
		SELECT 
			case 
					when c.ITEM_HRLevel = '0' then 0 
					when c.ITEM_HRLevel = '1' then 1 
					when c.ITEM_HRLevel = '2' then 2
					when c.ITEM_HRLevel = '3' then 3
					when c.ITEM_HRLevel = '4' then 4
					when c.ITEM_HRLevel = '无' then 0 
					when c.ITEM_HRLevel = '一级' then 1 
					when c.ITEM_HRLevel = '二级' then 2
					when c.ITEM_HRLevel = '三级' then 3
					when c.ITEM_HRLevel = '准三级' then 4
			end		 
	),
os.security_armed_rating=( 
		SELECT 
			case 
					when c.ITEM_armedEscortLevel = '0' then 0 
					when c.ITEM_armedEscortLevel = '1' then 1 
					when c.ITEM_armedEscortLevel = '2' then 2
					when c.ITEM_armedEscortLevel = '无' then 0 
					when c.ITEM_armedEscortLevel = '一级' then 1 
					when c.ITEM_armedEscortLevel = '二级' then 2
			end		 
	),
os.security_safety_defense_rating=( 
		SELECT 
			case 
					when c.ITEM_securityPrecautions = '0' then 0 
					when c.ITEM_securityPrecautions = '1' then 1 
					when c.ITEM_securityPrecautions = '2' then 2
					when c.ITEM_securityPrecautions = '3' then 3
					when c.ITEM_securityPrecautions = '无' then 0 
					when c.ITEM_securityPrecautions = '一级' then 1 
					when c.ITEM_securityPrecautions = '二级' then 2
					when c.ITEM_securityPrecautions = '三级' then 3
			end		 
	),
os.is_risk_assessment=( 
		SELECT 
			case 
					when c.ITEM_isRiskAssessment = '0' then 0 
					when c.ITEM_isRiskAssessment = '1' then 1 
					when c.ITEM_isRiskAssessment = '是' then 1
					when c.ITEM_isRiskAssessment = '否' then 0
			end		 
	),
os.number_of_security_guards=c.ITEM_SecurityNums,
os.is_public_security_security=( 
		SELECT 
			case 
					when c.ITEM_isKeyProtectionUnits = '0' then 0 
					when c.ITEM_isKeyProtectionUnits = '1' then 1 
					when c.ITEM_isKeyProtectionUnits = '是' then 1
					when c.ITEM_isKeyProtectionUnits = '否' then 0
			end		 
	),
os.nature_of_self_recruited_units=c.ITEM_attendanceSiteType,
os.legal_person_name=IFNULL(c.ITEM_legalPerson,os.legal_person_name),
os.legal_person_telephone=IFNULL(c.ITEM_legalPersonPhone,os.legal_person_telephone),
os.legal_person_credential_type=IFNULL(if(c.ITEM_documentType='',0,c.ITEM_documentType),0),
os.legal_person_credential_number=c.ITEM_documentID,
os.legal_person_nationality=IFNULL(c.ITEM_nationality,os.legal_person_nationality),
os.security_person_name=c.ITEM_sec_manager,
os.security_person_telephone=c.ITEM_sec_manager_phone,
os.security_person_credential_type=IFNULL(if(c.ITEM_sec_manager_doc_type='',0,c.ITEM_sec_manager_doc_type),0),
os.security_person_credential_number=c.ITEM_sec_manager_id,
os.last_updated_time=c.LASTMODIFIED
where os.id=c.DOMAINID;