{ "title": "归集企业信息到商城账号-优化配置-公司信息变更", "groupId": "cdc-gathering-reader-crmeb-db-account-23", "batchSize": 500, "batchIntervalMs": 2000, "kafkaConnections": [ { "name": "kafka-collection", "bootstrapServer": "49.4.84.49:9092" } ], "jdbcConnections": [ { "name": "industryInfoCollection-jdbc", "url": "jdbc:mysql://117.78.35.63:3306/crmeb?characterEncoding=UTF-8&connectionTimeZone=GMT%2B8&allowMultiQueries=true&autoReconnect=true", "userName": "pre_prod", "password": "pre-prod#202305", "driverClassName": "com.mysql.cj.jdbc.Driver" } ], "topicSubscribers": [ { "uid": "company_tenant_organizations_topic-crmeb", "topic": "obpm2.binlog-cdc.topic.v2.tenant_organizations", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "company_tenant_organizations_eb_user_01", "title": "执行公司账号归集到商城账号", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "INSERT INTO crmeb.eb_user (account,pwd,real_name,birthday,identity_card_no,tag_id,nickname,avatar,phone,country,province,city,district,address,sex,integral,experience,now_money,brokerage_price,level,sign_num,is_wechat_public,is_wechat_routine,pay_count,is_promoter,promoter_time,spread_uid,spread_time,spread_count,register_type,add_ip,last_ip,last_login_time,status,mark,create_time,update_time,is_logoff,logoff_time,is_wechat_ios,is_wechat_android,is_binding_ios,signature,user_type,region_code,industry_code,institutional_code,identity_no) select CONCAT('org_',:after.id),'',:after.name,'9999-12-31',(select unify_social_credit_code from obpm2.tenant_companies where id = :after.id), '',:after.name,'',ifnull(:after.telephone,''),'CN','','','','','0','0','0','0','0','1','0','0','0','0','0',NULL,ifnull((SELECT ifnull(z.id, '0') FROM (SELECT id FROM crmeb.eb_user WHERE account = concat('org_' ,:after.share_organization_id)) z),'0'),NULL,'0','','','',now(),(case :after.approved_information_status when '2' then '0' else '1' end),'',now(),now(),(CASE :after.approved_information_status WHEN '2' THEN '1' ELSE '0' END),(CASE :after.approved_information_status WHEN '2' THEN NULL ELSE NOW() END),'0','0','0','','1',(case :after.industry_code when '01' then :after.supervise_region_code else :after.place_of_register_province_code end),:after.industry_code,:after.institutional_code,:after.id from dual where :after.status = '1' ON DUPLICATE KEY UPDATE real_name = :after.name,region_code = (case :after.industry_code when '01' then :after.supervise_region_code else :after.place_of_register_province_code end),industry_code = :after.industry_code,institutional_code = :after.institutional_code,identity_card_no = (select unify_social_credit_code from obpm2.tenant_companies where id = :after.id),status = (case :after.approved_information_status when '2' then '0' else '1' end),is_logoff = (CASE :after.approved_information_status WHEN '2' THEN '1' ELSE '0' END),logoff_time = (CASE :after.approved_information_status WHEN '2' THEN NULL ELSE NOW() END)" } ] }, { "uid": "rt_contract.tlk_item_conctract", "topic": "rt_contract.binlog-cdc.topic.v2.tlk_item_conctract", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "rt_contract_tlk_item_conctract", "title": "合同管理数据同步到驻勤和临保", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call rt_contract.proc_sync_rd_conctract_info_collect_by_v2(:after.id,:after.ITEM_PROJECTTYPE,:before.id)" } ] }, { "uid": "shoppingrules.tlk_package_shopping_bcx_user_rules", "topic": "shoppingrules.binlog-cdc.topic.v2.tlk_shopping_package", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "bcx_product_rules", "title": "执行V5商品包配置信息归集到商城商品规则表_商品包表", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call shoppingrules.proc_bcx_product_rules_by_tlk_shopping_package(:before.id,:after.id,:after.item_is_show,:after.item_isTrue)" } ] }, { "uid": "shoppingrules.tlk_shopping_package_bcx_user_rules", "topic": "shoppingrules.binlog-cdc.topic.v2.tlk_package_shopping", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "bcx_product_rules2", "title": "执行V5商品包配置信息归集到商城商品规则表_中间表", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call shoppingrules.proc_bcx_product_rules_by_tlk_package_shopping(:before.id,:after.id)" } ] }, { "uid": "tenant_users_topic-crmeb-2023", "topic": "obpm2.binlog-cdc.topic.v2.tenant_users", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "company_tenant_users_eb_user_01", "title": "执行用户账号归集到商城账号-user", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "INSERT INTO crmeb.eb_user (account,pwd,real_name,birthday,identity_card_no,tag_id,nickname,avatar,phone,country,province,city,district,address,sex,integral,experience,now_money,brokerage_price,level,sign_num,is_wechat_public,is_wechat_routine,pay_count,is_promoter,promoter_time,spread_uid,spread_time,spread_count,register_type,add_ip,last_ip,last_login_time,status,mark,create_time,update_time,is_logoff,logoff_time,is_wechat_ios,is_wechat_android,is_binding_ios,signature,user_type,region_code,identity_no,org_name,wx_union_id) VALUES (ifnull(CONCAT('user_',:after.id),''),'',:after.name,'9999-12-31','', '',:after.name,'','','CN','','','','','0','0','0','0','0','1','0','0','0','0','0',NULL,'0',NULL,'0','','','',now(),'1','',now(),now(),'0',NULL,'0','0','0','','0','',:after.id,:organization.name,'') ON DUPLICATE KEY UPDATE real_name = :after.name,account = ifnull(CONCAT('user_',:after.id),''),update_time = now(),org_name = :organization.name" }, { "uid": "update_eb_user_emp_01", "title": "更新职员微信id", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "update crmeb.eb_user set wx_union_id = (select replace(we_chat_json->'$.unionId','\"','') as wx_union_id from obpm2.identity_user where tenant_user_id = :after.id limit 1) where identity_no = :after.id and user_type in ('2','3')" } ] }, { "uid": "tenant_employees_topic-crmeb-2023", "topic": "obpm2.binlog-cdc.topic.v2.tenant_employees", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "company_tenant_users_eb_employee_01", "title": "执行职员账号归集到商城账号-user", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "INSERT INTO crmeb.eb_user (account,pwd,real_name,birthday,identity_card_no,tag_id,nickname,avatar,phone,country,province,city,district,address,sex,integral,experience,now_money,brokerage_price,LEVEL,sign_num,is_wechat_public,is_wechat_routine,pay_count,is_promoter,promoter_time,spread_uid,spread_time,spread_count,register_type,add_ip,last_ip,last_login_time,STATUS,mark,create_time,update_time,is_logoff,logoff_time,is_wechat_ios,is_wechat_android,is_binding_ios,signature,user_type,region_code,industry_code,institutional_code,identity_no,org_name,wx_union_id) select * from (SELECT CONCAT('emp_',:after.id) as account,'' as pwd,ifnull(:user.name,'') as real_name,'9999-12-31' as birthday,(select number from obpm2.tenant_user_credentials where tenant_user_id = :user.id and credential_type = 0) as identity_card_no,'' as tag_id,ifnull(:user.name,'') as nickname,'' as avatar,ifnull(:user.telephone,'') as phone,'CN' as country,'' as province,'' as city,'' as district,'' as address,'0' as sex,'0' as integral,'0' as experience,'0' as now_money,'0' as brokerage_price,'1' as LEVEL,'0' as sign_num,'0' as is_wechat_public,'0' as is_wechat_routine,'0' as pay_count,'0' as is_promoter,NULL as promoter_time,(select ifnull(id,'0') from crmeb.eb_user where account = CONCAT('org_',:organization.id)) as spread_uid,NULL as spread_time,'0' as spread_count,'' as register_type,'' as add_ip,'' as last_ip,now() as last_login_time,(CASE :AFTER.status WHEN '1' THEN '0' ELSE '1' END) AS STATUS,'' as mark,now() as create_time,now() as update_time,(CASE :AFTER.status WHEN '1' THEN '1' ELSE '0' END) AS is_logoff,(CASE :AFTER.status WHEN '1' THEN now() ELSE NULL END) AS logoff_time,'0' as is_wechat_ios,'0' as is_wechat_android,'0' as is_binding_ios,'' as signature,(case :after.is_domain_admin when '1' then '3' else '2' end) as user_type,(CASE :organization.industry_code WHEN '01' THEN :organization.supervise_region_code ELSE :organization.place_of_register_province_code END) as region_code,:organization.industry_code as industry_code,:organization.institutional_code as institutional_code,:user.id as identity_no,:organization.name as org_name,(SELECT d.union_id FROM obpm2.tenant_users a JOIN obpm2.identity_user b ON a.id = b.tenant_user_id JOIN obpm2.identity_principals c ON c.identity_user_id = b.id JOIN obpm2.identity_wechat d ON d.id = c.id WHERE a.id = :USER.id) as wx_union_id FROM dual where :organization.status = '1' ) k on DUPLICATE KEY UPDATE real_name = ifnull(:user.name,''),region_code = (CASE :organization.industry_code WHEN '01' THEN :organization.supervise_region_code ELSE :organization.place_of_register_province_code END),industry_code = :organization.industry_code,institutional_code = :organization.institutional_code,phone = ifnull(:user.telephone,''),org_name = :organization.name,wx_union_id = k.wx_union_id,user_type = (case :after.is_domain_admin when '1' then '3' else '2' end),status = (case :after.status when '1' then '0' else '1' end),spread_uid = k.spread_uid,identity_card_no = k.identity_card_no,status = (CASE :AFTER.status WHEN '1' THEN '0' ELSE '1' END),is_logoff = (CASE :AFTER.status WHEN '1' THEN '1' ELSE '0' END),logoff_time = (CASE :AFTER.status WHEN '1' THEN now() ELSE NULL END)" }, { "uid": "company_tenant_employees_bcx_department_member_01", "title": "执行指定部门职员到商城表作分销用-employees", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "INSERT INTO crmeb.bcx_department_member(bcx_id,name,department_id,status,create_time,update_time)select :after.id,:user.name,(select ifnull(id,0) from crmeb.bcx_department where bcx_id = :after.department_id),'1',:after.created_time,:after.last_updated_time from dual where :after.organization_id = '__iZjB3As6MOfttXkZTXo' and :after.status = '0' on DUPLICATE KEY UPDATE bcx_id = :after.id,name = :user.name,department_id = (select ifnull(id,0) from crmeb.bcx_department where bcx_id = :after.department_id),update_time = :after.last_updated_time" }, { "uid": "company_update_01", "title": "执行人员数和上级id更新", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call shoppingrules.proc_bcx_product_rules_by_update(:before.id,:after.id,:after.organization_id,:before.status,:after.status)" }, { "uid": "employees_update_employees_06", "title": "执行分销商管理离职取消分销商资格-proc_bcx_product_rules_by_tenant_employees_status", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call shoppingrules.proc_bcx_product_rules_by_tenant_employees_status(:after.organization_id,:after.status,:before.status,:after.id,:after.tenant_user_id)" } ] }, { "uid": "tenant_departments_topic-crmeb-2023", "topic": "obpm2.binlog-cdc.topic.v2.tenant_departments", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "company_tenant_departments_bcx_department_01", "title": "执行指定部门到商城表作分销用-department", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "INSERT INTO crmeb.bcx_department(bcx_id,code,parent_code,tree_code,name,status,create_time,update_time)select :after.id,:after.id,ifnull(:after.parent_id,0),:after.index_tree,:after.name,'1',:after.created_time,:after.last_updated_time from dual where :after.organization_id = '__iZjB3As6MOfttXkZTXo' and :after.is_deleted = '0' on DUPLICATE KEY UPDATE bcx_id = :after.id,code = :after.id,parent_code = ifnull(:after.parent_id,'0'),tree_code = :after.index_tree,name = :after.name,update_time = :after.last_updated_time" }, { "uid":"company_tenant_departments_bcx_department_122", "title": "薪酬管理部门归集-proc_sync_rd_department_info_collect_by_v2", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call personnel_oa.proc_sync_rd_department_info_collect_by_v2(:after.id)" } ] }, { "uid": "shoppingrules.tlk_product_eb_product_attr_value", "topic": "shoppingrules.binlog-cdc.topic.v2.tlk_product", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "eb_product_attr_value_plat_fee", "title": "执行V5配置的平台费到商城", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "update crmeb.eb_product_attr_value set plat_fee = (select item_service_charge from organizationauth.tlk_school_info where id = :after.item_sjid) where id = :after.ITEM_PRODUCT_SKU_ID" } ] }, { "uid": "shoppingrules.tlk_product_eb_product_attr_value2", "topic": "organizationauth.binlog-cdc.topic.v2.tlk_school_info", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "eb_product_attr_value_plat_fee2", "title": "执行V5配置的平台费到商城2", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "update crmeb.eb_product_attr_value set plat_fee = :after.item_service_charge where id = (select ITEM_PRODUCT_SKU_ID from shoppingrules.tlk_product where item_sjid = :after.id)" } ] }, { "uid": "company_tenant_organizations_topic-shoppingrules-2023", "topic": "crmeb.binlog-cdc.topic.v2.eb_product_attr_value", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "company_tenant_organizations_eb_product_01", "title": "执行商城商品归集到V5", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "INSERT INTO shoppingrules.tlk_product (id,item_product_sku_id,item_product_id,item_product_sku,item_is_deleted,item_attr_value,item_merchant_id,item_name,item_type,item_is_show,item_audit_status,item_image,item_price,item_merchant_name) SELECT CONCAT(UUID(),'--__5wwEvSsDIupdI4cR6Ar'),:after.id,:after.product_id,:after.sku,(case when b.is_del = '1' then '1' else :after.is_del end) as is_del,:after.attr_value,b.mer_id,b.name,a.type,b.is_show,b.audit_status,:after.image,:after.price,c.name from crmeb.eb_product_attr_value a left join crmeb.eb_product b on a.product_id = b.id left join crmeb.eb_merchant c on b.mer_id = c.id where a.id = :after.id on DUPLICATE key UPDATE item_product_sku = :after.sku,item_is_deleted = (case when b.is_del = '1' then '1' else :after.is_del end),item_attr_value = :after.attr_value,item_name = b.name,item_type = a.type,item_is_show = b.is_show,item_audit_status = b.audit_status,item_image = :after.image,item_price = :after.price,item_merchant_name = c.name" } ] }, { "uid": "company_tenant_organizations_topic-shoppingrules-product-2023", "topic": "crmeb.binlog-cdc.topic.v2.eb_product", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "company_tenant_organizations_eb_product_02", "title": "执行商城商品归集到V5-2", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "INSERT INTO shoppingrules.tlk_product (id,item_product_sku_id,item_product_id,item_product_sku,item_is_deleted,item_attr_value,item_merchant_id,item_name,item_type,item_is_show,item_audit_status,item_image,item_price,item_merchant_name) SELECT CONCAT(UUID(),'--__5wwEvSsDIupdI4cR6Ar'),a.id,a.product_id,a.sku,(case when :after.is_del = '1' then '1' else a.is_del end) as is_del,a.attr_value,:after.mer_id,:after.name,:after.type,:after.is_show,:after.audit_status,a.image,a.price,c.name from crmeb.eb_product_attr_value a left join crmeb.eb_merchant c on :after.mer_id = c.id where a.product_id = :after.id on DUPLICATE key UPDATE item_product_sku = a.sku,item_is_deleted = (case when :after.is_del = '1' then '1' else a.is_del end),item_attr_value = a.attr_value,item_name = :after.name,item_type = :after.type,item_is_show = :after.is_show,item_audit_status = :after.audit_status,item_image = a.image,item_price = a.price,item_merchant_name = c.name" } ] }, { "uid": "company_tenant_organizations_topic-tlk_activate", "topic": "shoppingrules.binlog-cdc.topic.v2.tlk_activate", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "company_tenant_organizations_eb_tlk_activate_01", "title": "执行激活分销商归集到商城", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call shoppingrules.proc_bcx_product_rules_by_tlk_activate(:after.ITEM_COMPANY_ID,:after.ITEM_ACTIVATE_TIME)" }, { "uid": "company_tenant_organizations_eb_tlk_activate_02", "title": "执行过期分销商归集到商城", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call shoppingrules.proc_bcx_product_rules_by_eb_user(:after.ITEM_END_TIME,:after.ITEM_COMPANY_ID)" } ] }, { "uid": "company_tenant_organizations_topic-tlk_authorized_goods", "topic": "shoppingrules.binlog-cdc.topic.v2.tlk_authorized_goods", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "company_tenant_organizations_eb_tlk_authorized_goods", "title": "执行授权分销商品归集到商城", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call shoppingrules.proc_bcx_product_rules_by_bcx_product_benefits(:before.id,:after.id)" } ] }, { "uid": "eb_order_detail_topic-tlk_learn_order_person", "topic": "crmeb.binlog-cdc.topic.v2.eb_order_detail", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "eb_order_detail_topic-tlk_learn_order_person_01", "title": "执行商城订单归集到V5考培", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "call shoppingrules.proc_tlk_learn_order_person_by_eb_order_detail (:after.order_no,:before.source_id,:after.source_id)" } ] }, { "uid": "crmeb_bcx_order_device_no_topic-dispatch-tlk_hardware", "topic": "crmeb.binlog-cdc.topic.v2.bcx_order_device_no", "refKafkaName": "kafka-collection", "subscriberContents": [ { "uid": "crmeb_bcx_order_device_no-tlk_hardware", "title": "执行商城硬件编号归集到V5设备表", "refJdbcName": "industryInfoCollection-jdbc", "type": "JDBC", "content": "INSERT INTO dispatch.tlk_hardware (id,item_product_pk_id,item_domain_id,item_device_no,item_device_type,item_device_model,item_hold_type,item_reference_number,formid,applicationid,created) VALUES (CONCAT(:after.id,'--__hw8pZmKQpL6F8rl2lUa'),:after.product_id,REPLACE(:after.account,'org_',''),:after.device_no,:after.biz_sub_type,:after.device_model,'0',:after.order_no,'__hw8pZmKQpL6F8rl2lUa','__KWBHssgPtqkxvqhTKlW',now())ON DUPLICATE KEY UPDATE item_product_pk_id=:after.product_id,item_domain_id=REPLACE(:after.account,'org_',''),item_device_no=:after.device_no,item_device_type=:after.biz_sub_type,item_device_model=:after.device_model,item_reference_number=:after.order_no,lastmodified=now();" } ] } ] }