ALTER TABLE obpm2.`tenant_organizations` ADD COLUMN `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '公司状态，1-正常，2-注销，3-撤销，4-吊销' AFTER `lon_lat_json`, ADD COLUMN `fax` varchar(20) NULL DEFAULT NULL COMMENT '传真' AFTER `status`, ADD COLUMN `telephone` varchar(20) NULL DEFAULT NULL COMMENT '联系电话' AFTER `fax`, ADD COLUMN `type` tinyint(4) NOT NULL DEFAULT 1 COMMENT '总分子公司，1-总公司，2分公司，3-子公司' AFTER `telephone`, ADD COLUMN `industry_detail_type` tinyint(4) NULL DEFAULT 0 COMMENT '保安服务公司类型，0-无，1-普通保安服务公司，2-武装守护押运保安服务公司，3-外资保安服务公司' AFTER `type`, ADD COLUMN `supervise_region_code` varchar(200) NOT NULL DEFAULT '#' COMMENT '监管地区' AFTER `industry_detail_type`, ADD COLUMN `supervise_depart_id` varchar(200) NOT NULL DEFAULT '#' COMMENT '监管机构' AFTER `supervise_region_code`, ADD COLUMN `supervise_depart_name` varchar(200) NOT NULL DEFAULT '#' COMMENT '监管机构名称(冗余)' AFTER `supervise_depart_id`; ALTER TABLE obpm2.`tenant_companies` ADD COLUMN `parent_name` varchar(255) NULL DEFAULT NULL COMMENT '总公司名称' AFTER `unify_social_credit_code_file`, ADD COLUMN `parent_certificate_type` tinyint(4) NULL DEFAULT NULL COMMENT '总公司证件类型' AFTER `parent_name`, ADD COLUMN `parent_certificate_number` varchar(100) NULL DEFAULT NULL COMMENT '总公司证件号码' AFTER `parent_certificate_type`, ADD COLUMN `parent_legal_person_name` varchar(255) NULL DEFAULT NULL COMMENT '总公司法人姓名' AFTER `parent_certificate_number`, ADD COLUMN `parent_legal_person_telephone` varchar(20) NULL DEFAULT NULL COMMENT '总公司联系电话' AFTER `parent_legal_person_name`, ADD COLUMN `economic_type` tinyint(4) NOT NULL DEFAULT 1 COMMENT '经济类型，1-国有经济，2-集体经济，3-私营经济，4-个体经济，5-联营经济，6-股份制，7-外商投资，8-港澳台投资与其他经济，9-其他经济' AFTER `parent_legal_person_telephone`, ADD COLUMN `nature` tinyint(4) NULL DEFAULT NULL COMMENT '公司类型，1-国有资本，2-民营，3-外资（包括港澳台投资），4-其它' AFTER `economic_type`, ADD COLUMN `service_scope` int(11) NULL DEFAULT NULL COMMENT '服务范围' AFTER `nature`, ADD COLUMN `service_scope_description` varchar(200) NULL DEFAULT NULL COMMENT '服务范围描述' AFTER `service_scope`, ADD COLUMN `registered_capital` varchar(50) NOT NULL DEFAULT '' COMMENT '注册资本' AFTER `service_scope_description`, ADD COLUMN `fixed_capital` varchar(50) NULL DEFAULT NULL COMMENT '固定资本' AFTER `registered_capital`, ADD COLUMN `annual_profit_tax` varchar(50) NULL DEFAULT NULL COMMENT '年利税' AFTER `fixed_capital`, ADD COLUMN `annual_sales_volume` varchar(50) NULL DEFAULT NULL COMMENT '年营业额' AFTER `annual_profit_tax`, ADD COLUMN `certificate_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '证件类型' AFTER `annual_sales_volume`, ADD COLUMN `service_permit_number` varchar(100) NULL DEFAULT NULL COMMENT '保安服务许可/保安培训备案证号' AFTER `certificate_type`, ADD COLUMN `security_approval_number` varchar(100) NULL DEFAULT NULL COMMENT '批准文号' AFTER `service_permit_number`, ADD COLUMN `name_of_issuing_authority` varchar(200) NULL DEFAULT NULL COMMENT '发证机关' AFTER `security_approval_number`, ADD COLUMN `first_issue_service_permit` date NULL DEFAULT NULL COMMENT '保安服务许可证首次发证日期或者培训单位的发证日期' AFTER `name_of_issuing_authority`, ADD COLUMN `service_permit_attachment` varchar(500) NULL DEFAULT NULL COMMENT '保安服务许可证扫描件或者保安培训许可（备案）证扫描件' AFTER `first_issue_service_permit`, ADD COLUMN `train_content` varchar(500) NULL DEFAULT NULL COMMENT '培训单位的培训内容' AFTER `service_permit_attachment`, ADD COLUMN `security_preparedness_rating` tinyint(4) NULL DEFAULT NULL COMMENT '人力防范评定等级，0-无，1-一级，2-二级，3-三级，4-准三级' AFTER `train_content`, ADD COLUMN `security_armed_rating` tinyint(4) NULL DEFAULT NULL COMMENT '武装守护押运评定等级，0-无，1-一级，2-二级' AFTER `security_preparedness_rating`, ADD COLUMN `security_safety_defense_rating` tinyint(4) NULL DEFAULT NULL COMMENT '安全技术防范评定等级，0-无，1-一级，2-二级，3-三级' AFTER `security_armed_rating`, ADD COLUMN `is_risk_assessment` tinyint(4) NULL DEFAULT NULL COMMENT '是否有风险评估资质，0-否，1-是' AFTER `security_safety_defense_rating`, ADD COLUMN `number_of_security_guards` int(11) NULL DEFAULT NULL COMMENT '自行招用保安员单位.拟自招保安员数' AFTER `is_risk_assessment`, ADD COLUMN `is_public_security_security` tinyint(4) NULL DEFAULT NULL COMMENT '自行招用保安员单位.是否治安保卫重点单位，0-否，1-是' AFTER `number_of_security_guards`, ADD COLUMN `is_property_enterprise` tinyint(4) NULL DEFAULT NULL COMMENT '自行招用保安员单位.是否物业企业，0-否，1-是' AFTER `is_public_security_security`, ADD COLUMN `nature_of_self_recruited_units` varchar(10) NULL DEFAULT NULL COMMENT '自行招用保安员单位.自招保安单位性质' AFTER `is_property_enterprise`, ADD COLUMN `legal_person_name` varchar(200) NOT NULL DEFAULT '' COMMENT '法人姓名' AFTER `nature_of_self_recruited_units`, ADD COLUMN `legal_person_telephone` varchar(20) NOT NULL DEFAULT '' COMMENT '法人手机号码' AFTER `legal_person_name`, ADD COLUMN `legal_person_credential_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '法人证件类型' AFTER `legal_person_telephone`, ADD COLUMN `legal_person_credential_number` varchar(100) NOT NULL DEFAULT '' COMMENT '法人证件号码' AFTER `legal_person_credential_type`, ADD COLUMN `legal_person_nationality` varchar(30) NOT NULL DEFAULT '' COMMENT '法人国籍' AFTER `legal_person_credential_number`, ADD COLUMN `security_person_name` varchar(200) NULL DEFAULT NULL COMMENT '保安负责人姓名' AFTER `legal_person_nationality`, ADD COLUMN `security_person_telephone` varchar(20) NULL DEFAULT NULL COMMENT '保安负责人手机号码' AFTER `security_person_name`, ADD COLUMN `security_person_credential_type` tinyint(4) NULL DEFAULT NULL COMMENT '保安负责人证件类型' AFTER `security_person_telephone`, ADD COLUMN `security_person_credential_number` varchar(100) NULL DEFAULT NULL COMMENT '保安负责人证件号码' AFTER `security_person_credential_type`, ADD COLUMN `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间' AFTER `security_person_credential_number`, ADD COLUMN `last_updated_time` datetime NULL DEFAULT NULL COMMENT '更新时间' AFTER `create_time`; ALTER TABLE obpm2.`tenant_users` ADD COLUMN `nationality` varchar(30) NULL DEFAULT NULL COMMENT '国籍' AFTER `last_sync_time`, ADD COLUMN `work_year` int(11) NULL DEFAULT NULL COMMENT '从业年限' AFTER `nationality`, ADD COLUMN `diseases_history` varchar(500) NULL DEFAULT NULL COMMENT '疾病历史' AFTER `work_year`; UPDATE obpm2.`tenant_organizations` SET `industry_code`='01' WHERE `industry_code`='保安行业'; UPDATE obpm2.`tenant_organizations` SET `industry_code`='02' WHERE `industry_code`='大活行业'; UPDATE obpm2.`tenant_organizations` SET `industry_code`='03' WHERE `industry_code`='其他'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0101' WHERE `institutional_code`='保安协会'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0102' WHERE `institutional_code`='保安服务公司'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0103' WHERE `institutional_code`='保安培训单位'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0104' WHERE `institutional_code` like '自行招用保安%'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0105' WHERE `institutional_code`='省级保安协会'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0106' WHERE `institutional_code`='市级保安协会'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0107' WHERE `institutional_code`='省级保安监管机构'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0108' WHERE `institutional_code`='市级保安监管机构'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0201' WHERE `institutional_code`='活动承办方'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0202' WHERE `institutional_code`='政府单位'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0301' WHERE `institutional_code`='其他'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0201' WHERE `institutional_code`='承办单位'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0203' WHERE `institutional_code`='风评机构'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0204' WHERE `institutional_code`='服务机构'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0205' WHERE `institutional_code`='公安机构'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0108' WHERE `institutional_code`='市级保安监督机构'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0103' WHERE `institutional_code`='保安培训机构'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0102' WHERE `institutional_code`='保安服务公司及培训单位'; UPDATE obpm2.`tenant_organizations` SET `institutional_code`='0109' WHERE `institutional_code`='治安机关'; -- 从业单位处理 UPDATE baibaodunflow.tlk_companyinformation SET ITEM_officeType = '0102' WHERE `item_companyName` IN ( '永州市万全保安服务有限责任公司', '永州市金狮保安服务有限公司', '湖南东大保安服务有限公司', '湘潭正天元保安服务有限公司', '湖南辉润保安服务有限公司', '郴州市金泰保安服务有限责任公司', '耒阳市铁甲卫士保安科技有限公司', '中保华一保安服务（北京）有限公司中易分公司' ); DELETE FROM obpm2.`config_dict` WHERE code_type = 'education' and code_value = '10'; UPDATE obpm2.tenant_users u SET u.education = '初中' WHERE u.education = '其他'; UPDATE obpm2.tenant_users u SET u.education = '初中' WHERE u.education = '小学'; UPDATE obpm2.tenant_users u SET u.education = '硕士研究生及以上' WHERE u.education = '硕士研究生'; UPDATE obpm2.tenant_users u SET u.education = '硕士研究生及以上' WHERE u.education = '博士后'; UPDATE obpm2.tenant_users u SET u.education = '硕士研究生及以上' WHERE u.education = '博士'; UPDATE obpm2.tenant_users u SET u.education = '初中' WHERE u.education = '1'; UPDATE obpm2.tenant_users u SET u.education = '初中' WHERE u.education = '2'; UPDATE obpm2.tenant_users u SET u.education = '高中' WHERE u.education = '3'; UPDATE obpm2.tenant_users u SET u.education = '中专' WHERE u.education = '4'; UPDATE obpm2.tenant_users u SET u.education = '大专' WHERE u.education = '5'; UPDATE obpm2.tenant_users u SET u.education = '本科' WHERE u.education = '6'; UPDATE obpm2.tenant_users u SET u.education = '硕士研究生及以上' WHERE u.education = '7'; UPDATE obpm2.tenant_user_credentials uc SET uc.credential_type = 0 WHERE uc.credential_type IN (5, 6) AND uc.tenant_user_id IN ('2kkSAGTX','gZtTDulm','796318580716605440--__qFCcCnozYNwzoG3O6Ms','QTh9niiQ','863nSIuv','EnVv93Fx','uahg9AA2','uwzRymGGCFi6MzZk0e3','__YZtNqPYNAHL7AieaMDi','jz75sSJI0hGufGhycH6','Z5hweGbu1VvpOB9Q1Tz','__zauw4CNdCStJRKDXyAj','__tLqssUvmuFv93p2fI5f','JEEh2ecd7Lnc5KP0WAe'); -- 迁移旧数据 -- 迁移监管机构 UPDATE obpm2.tenant_organizations t_a, baibaodunflow.tlk_setsupervise t_s SET t_a.supervise_region_code = IFNULL(t_s.ITEM_REGISTERADDRESSID,""), t_a.supervise_depart_id = IFNULL((SELECT td.ITEM_CODE FROM organizationauth.tlk_departmentlevel td WHERE td.id = t_s.ITEM_REGISTERPOLICEADDRESSID),""), t_a.supervise_depart_name = IFNULL(t_s.ITEM_REGISTERPOLICEADDRESS,"") WHERE t_s.DOMAINID = t_a.id; -- 迁移公司数据 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; ALTER VIEW `obpm2`.`vw_company_supervise` AS select `obpm2`.`tenant_organizations`.`id` AS `domainId`,`obpm2`.`tenant_organizations`.`supervise_region_code` AS `areaCode` from `obpm2`.`tenant_organizations`; DROP TABLE IF EXISTS obpm2.`t_supervise_department`; CREATE TABLE obpm2.`t_supervise_department` ( `pk_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `id` varchar(32) DEFAULT NULL, `name` varchar(100) DEFAULT NULL COMMENT '名称', `superior` varchar(100) DEFAULT NULL COMMENT '上级编码', `status` varchar(10) DEFAULT NULL COMMENT '状态', `index_code` varchar(200) DEFAULT NULL COMMENT '层级', `region_id` varchar(32) DEFAULT NULL COMMENT '地区id', PRIMARY KEY (`pk_id`), UNIQUE KEY `id` (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='监管机构表'; INSERT INTO obpm2.`t_supervise_department` (`id`, `name`, `superior`, `status`, `index_code`, `region_id`) SELECT ITEM_code, ITEM_name, ITEM_superior, ITEM_status, ITEM_indexCode, ITEM_regionId FROM organizationauth.`tlk_departmentlevel`;