gzTyBWhJ

1
2drop table if exists tmp_17726;
3
4CREATE TABLE `tmp_17726` (
5  `customers_id` INT(11) NOT NULL,
6  `min_date` TIMESTAMP NULL DEFAULT NULL,
7  `max_date` TIMESTAMP NULL DEFAULT NULL,
8  PRIMARY KEY (`customers_id`),
9  INDEX `min_date` (`min_date`),
10  INDEX `max_date` (`max_date`)
11)
12  ENGINE=InnoDB
13;
14
15insert into tmp_17726
16
17  select t.customers_id, t.min_date, coalesce(t.max_date1, t.max_date2) as max_date
18  from (
19         select c.customers_id, min(csh.date_added) as min_date, min(csh1.date_added) as max_date1, max(csh.date_added) as max_date2
20         from customers c
21           join customers_status_history csh on csh.object_id = c.customers_id and csh.status_id = 6
22           left join customers_status_history csh1 on csh1.object_id = c.customers_id and csh1.status_id != 6 and csh1.customers_status_history_id > csh.customers_status_history_id
23         group by c.customers_id
24       ) t
25;
26
27
28replace into tmp_17726
29  select c.customers_id, 0 as min_date, '2020-01-01' as max_date
30  from customers c
31  where c.customers_status = 6;
32
33drop table if exists tmp_17726_1;
34
35CREATE TABLE `tmp_17726_1` (
36  `points_log_id` INT(11) NOT NULL AUTO_INCREMENT,
37  `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
38  `number` INT(11) NOT NULL,
39  `points_change_type_id` INT(11) NOT NULL,
40  `customers_id` INT(11) NOT NULL,
41  `is_active` TINYINT(1) NOT NULL DEFAULT '1',
42  PRIMARY KEY (`points_log_id`)
43)
44  COLLATE='utf8_general_ci'
45  ENGINE=InnoDB
46;
47
48insert into tmp_17726_1
49  select rpl.*
50  from tmp_17726 as t
51    join remote_points_log rpl on rpl.customers_id = t.customers_id
52  where rpl.date_added between t.min_date and t.max_date
53;
54
55update points p
56  join(
57        select sum(t.number) as numberDel, p.*, c.customers_status
58        from tmp_17726_1 t
59          join points p on p.customers_id = t.customers_id
60          join customers c on c.customers_id = t.customers_id
61        where p.number > 0
62        group by t.customers_id
63      ) tt on p.customers_id = tt.customers_id
64set p.number = if(tt.number-tt.numberDel > 0, tt.number-tt.numberDel, 0)
65;
66
67delete p from remote_points_log p
68  join tmp_17726_1 t on t.points_log_id = p.points_log_id
69;
70
71update points p
72  join (
73         select rsv.customers_id, count(rsv.customers_id) as number
74         from tmp_17726 as t
75           join remote_site_visits rsv on rsv.customers_id = t.customers_id
76         where rsv.date_added between t.min_date and t.max_date
77         group by rsv.customers_id
78       ) t on t.customers_id = p.customers_id
79set p.number = if(p.number - t.number > 0, p.number - t.number, 0)
80;