批量刷数据

DROP TABLE IF EXISTS `mgt_emp_resign_order_copy`;
CREATE TABLE `mgt_emp_resign_order_copy`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '订单编号',
  `old_cust_mgr_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '原客户经理编号',
  `now_cust_mgr_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '现客户经理编号',
  `cust_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '客户编号',
  `lead_code` varchar(32) DEFAULT NULL COMMENT '商机编码',
  `project_code` varchar(32) DEFAULT NULL COMMENT '工程编码',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


INSERT IGNORE INTO `vip`.`mgt_emp_resign_order_copy` ( `id`, `order_code`, `old_cust_mgr_code`, `now_cust_mgr_code`, `cust_code`,`create_time` )
select  `id`, `order_code`, `old_cust_mgr_code`, `now_cust_mgr_code`, `cust_code`,`create_time`  from mgt_emp_resign_order;

update IGNORE vip.mgt_emp_resign_order_copy o1 set o1.lead_code=(select o2.lead_code from crm.crm_lead_order_rel o2 where o1.order_code = o2.order_code)  where EXISTS (select o3.id from crm.crm_lead_order_rel o3 where o1.order_code = o3.order_code) and o1.old_cust_mgr_code in ('USER180301022');

update IGNORE vip.mgt_emp_resign_order_copy o1 set o1.project_code=(select o2.project_code from vip.oms_order_awp o2 where o1.order_code = o2.order_code)  where EXISTS (select o3.id from vip.oms_order_awp o3 where o1.order_code = o3.order_code) and o1.old_cust_mgr_code in ('USER180301022');

UPDATE IGNORE crm.crm_lead l, vip.mgt_emp_resign_order_copy c, vip.vip_department b, vip.vip_department_user_rel a, vip.vip_user u SET l.follower_code = c.now_cust_mgr_code, l.follower_name = u.chinese_name,l.follower_store_code=b.dept_code,l.follower_store_name = b.dept_name ,l.update_time="2020-09-04 11:11:11" WHERE b.dept_code = a.dept_code AND a.user_code = u.user_code AND b.dept_type = 1 and a.is_part_time = 0 and c.lead_code = l.lead_code and c.now_cust_mgr_code = u.user_code AND l.follower_code IN ( "USER180301022" );

UPDATE IGNORE crm.crm_project_user_rel r, vip.mgt_emp_resign_order_copy c, vip.vip_department b, vip.vip_department_user_rel a, vip.vip_user u SET r.user_code = c.now_cust_mgr_code , r.user_name = u.chinese_name, r.store_code = b.dept_code, r.store_name = b.dept_name,r.update_time="2020-09-04 11:11:11" WHERE b.dept_code = a.dept_code AND a.user_code = u.user_code AND b.dept_type = 1 and a.is_part_time = 0 and c.now_cust_mgr_code = u.user_code and r.project_code = c.project_code AND r.user_code IN ( "USER180301022" );

INSERT IGNORE INTO crm.crm_customer_public ( `cust_code`, `user_code`, `user_name`, `store_code`, `store_name`, `type`, `fell_time` ) SELECT l.cust_code, d.user_code, d.chinese_name, d.dept_code, d.dept_name, 1, "2020-09-04 12:12:12" FROM crm.crm_customer_user_rel l ,(select a.user_code,u.chinese_name,b.dept_code,b.dept_name from vip.vip_department b, vip.vip_department_user_rel a , vip.vip_user u , vip.sys_role r ,vip.sys_user_role_rela rr where b.dept_code=a.dept_code and a.user_code=u.user_code and u.user_code=rr.user_no and rr.role_no=r.role_no and r.role_no="ROLE180300039" and b.dept_type=1 group by b.dept_code) d WHERE l.user_code = 'USER180301022' AND d.dept_name LIKE "成都店%" AND NOT EXISTS ( SELECT c1.cust_code FROM vip.mgt_emp_resign_order_copy c1 WHERE c1.cust_code = l.cust_code );

update IGNORE crm.crm_customer_user_rel l set user_role = 2,cust_type=2,cust_user_status=0,update_time = "2020-09-04 12:12:12" where l.user_code = 'USER180301022' and NOT EXISTS (select c1.cust_code from vip.mgt_emp_resign_order_copy c1 where c1.cust_code = l.cust_code);

UPDATE IGNORE crm.crm_lead l, (select a.user_code,u.chinese_name,b.dept_code,b.dept_name from vip.vip_department b, vip.vip_department_user_rel a , vip.vip_user u , vip.sys_role r ,vip.sys_user_role_rela rr where b.dept_code=a.dept_code and a.user_code=u.user_code and u.user_code=rr.user_no and rr.role_no=r.role_no and r.role_no="ROLE180300039" and b.dept_type=1 group by b.dept_code) d SET l.follower_code = d.user_code,l.follower_name = d.chinese_name,l.follower_store_code=d.dept_code,l.follower_store_name = d.dept_name ,l.update_time="2020-09-04 12:12:12" WHERE l.follower_code IN ( "USER180301022" ) AND d.dept_name LIKE "成都店%" AND not exists (select id from vip.mgt_emp_resign_order_copy c1 where c1.lead_code = l.lead_code);

UPDATE IGNORE crm.crm_project_user_rel r,(select a.user_code,u.chinese_name,b.dept_code,b.dept_name from vip.vip_department b, vip.vip_department_user_rel a , vip.vip_user u , vip.sys_role r ,vip.sys_user_role_rela rr where b.dept_code=a.dept_code and a.user_code=u.user_code and u.user_code=rr.user_no and rr.role_no=r.role_no and r.role_no="ROLE180300039" and b.dept_type=1 group by b.dept_code) d SET r.user_code = d.user_code, r.user_name = d.chinese_name, r.store_code = d.dept_code, r.store_name = d.dept_name, r.update_time = "2020-09-04 12:12:12" WHERE r.user_code in ( "USER180301022") AND d.dept_name LIKE "成都店%" and not exists ( SELECT id FROM vip.mgt_emp_resign_order_copy c1 WHERE c1.project_code = r.project_code );

#################
-- --商机
UPDATE IGNORE crm.crm_lead l, vip.mgt_emp_resign_order_copy c, vip.vip_department b, vip.vip_department_user_rel a, vip.vip_user u SET l.follower_name = u.chinese_name, l.follower_store_code = b.dept_code, l.follower_store_name = b.dept_name, l.update_time = "2020-09-04 11:11:11" WHERE b.dept_code = a.dept_code AND a.user_code = u.user_code AND b.dept_type = 1 AND a.is_part_time = 0 AND c.lead_code = l.lead_code AND c.now_cust_mgr_code = u.user_code AND c.old_cust_mgr_code = "USER180301022";

-- --工程
UPDATE IGNORE crm.crm_project_user_rel r, vip.mgt_emp_resign_order_copy c, vip.vip_department b, vip.vip_department_user_rel a, vip.vip_user u SET r.user_name = u.chinese_name, r.store_code = b.dept_code, r.store_name = b.dept_name, r.update_time = "2020-09-04 11:11:11" WHERE b.dept_code = a.dept_code AND b.dept_type = 1 AND a.is_part_time = 0 AND a.user_code = u.user_code AND c.old_cust_mgr_code IN ( "USER180301022" ) AND c.now_cust_mgr_code = u.user_code AND r.project_code = c.project_code and r.user_code = c.now_cust_mgr_code;