· 7 years ago · Jan 25, 2019, 03:02 PM
1 DROP TABLE IF EXISTS `temp_files`;
2 CREATE TABLE IF NOT EXISTS `temp_files` (
3 `r_id` INT NOT NULL ,
4 `f_id` INT NOT NULL ,
5 INDEX `r_id` (`r_id`),
6 UNIQUE `f_id` (`f_id`)
7 ) ENGINE = InnoDB;
8
9 # exact time hit
10 INSERT INTO temp_files (r_id, f_id)
11 SELECT r.ReclamationId as id, deleted_files.id as file_id
12 FROM (
13 SELECT r.* FROM reclamation r
14 LEFT JOIN identity_cards_reclamation_file as uf on uf.ReclamationId = r.ReclamationId
15 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.ReclamationId = r.ReclamationId
16 WHERE typeReclamation = 1 AND (TransportType = 'TC' OR TransportType = 'MOTO')
17 AND (uf.ReclamationId IS NULL OR crf.ReclamationId IS NULL)
18 ) r
19 JOIN (
20 SELECT MAX(time_edit) as last_time_edit, reclamation_id
21 FROM `reclamation_diff_log` diff
22 GROUP BY reclamation_id
23 ) as last_edit
24 on (
25 r.ReclamationId = last_edit.reclamation_id
26 )
27 JOIN (
28 SELECT f.* FROM
29 upload_file f
30 LEFT JOIN identity_cards_reclamation_file as uf on uf.file_id = f.id
31 LEFT JOIN certificate_files as cf on cf.file_id = f.id
32 LEFT JOIN quality_control_files as qf on qf.file_id = f.id
33 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.file_id = f.id
34 WHERE f.user_id is null AND
35 uf.file_id is NULL AND
36 cf.file_id is NULL AND
37 qf.file_id is null AND
38 crf.file_id is NULL
39 ) as deleted_files
40 on (
41 deleted_files.updated_at = last_edit.last_time_edit AND
42 deleted_files.created_at = r.ProcessingTime
43 )
44 ;
45
46 # 1 sec diff
47 INSERT IGNORE INTO temp_files (r_id, f_id)
48 SELECT r.ReclamationId as id, deleted_files.id as file_id
49 FROM (
50 SELECT r.* FROM reclamation r
51 LEFT JOIN identity_cards_reclamation_file as uf on uf.ReclamationId = r.ReclamationId
52 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.ReclamationId = r.ReclamationId
53 WHERE typeReclamation = 1 AND (TransportType = 'TC' OR TransportType = 'MOTO')
54 AND (uf.ReclamationId IS NULL OR crf.ReclamationId IS NULL)
55 ) r
56 JOIN (
57 SELECT MAX(time_edit) as last_time_edit, reclamation_id
58 FROM `reclamation_diff_log` diff
59 GROUP BY reclamation_id
60 ) as last_edit
61 on (
62 r.ReclamationId = last_edit.reclamation_id
63 )
64 JOIN (
65 SELECT f.* FROM
66 upload_file f
67 LEFT JOIN identity_cards_reclamation_file as uf on uf.file_id = f.id
68 LEFT JOIN certificate_files as cf on cf.file_id = f.id
69 LEFT JOIN quality_control_files as qf on qf.file_id = f.id
70 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.file_id = f.id
71 WHERE f.user_id is null AND
72 uf.file_id is NULL AND
73 cf.file_id is NULL AND
74 qf.file_id is null AND
75 crf.file_id is NULL
76 ) as deleted_files
77 on (
78 ABS(TIMESTAMPDIFF(SECOND, deleted_files.updated_at, last_edit.last_time_edit)) <= 1 AND
79 ABS(TIMESTAMPDIFF(SECOND, deleted_files.created_at, r.ProcessingTime)) <= 1
80 )
81 ;
82
83 # 2 sec diff
84 INSERT IGNORE INTO temp_files (r_id, f_id)
85 SELECT r.ReclamationId as id, deleted_files.id as file_id
86 FROM (
87 SELECT r.* FROM reclamation r
88 LEFT JOIN identity_cards_reclamation_file as uf on uf.ReclamationId = r.ReclamationId
89 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.ReclamationId = r.ReclamationId
90 WHERE typeReclamation = 1 AND (TransportType = 'TC' OR TransportType = 'MOTO')
91 AND (uf.ReclamationId IS NULL OR crf.ReclamationId IS NULL)
92 ) r
93 JOIN (
94 SELECT MAX(time_edit) as last_time_edit, reclamation_id
95 FROM `reclamation_diff_log` diff
96 GROUP BY reclamation_id
97 ) as last_edit
98 on (
99 r.ReclamationId = last_edit.reclamation_id
100 )
101 JOIN (
102 SELECT f.* FROM
103 upload_file f
104 LEFT JOIN identity_cards_reclamation_file as uf on uf.file_id = f.id
105 LEFT JOIN certificate_files as cf on cf.file_id = f.id
106 LEFT JOIN quality_control_files as qf on qf.file_id = f.id
107 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.file_id = f.id
108 WHERE f.user_id is null AND
109 uf.file_id is NULL AND
110 cf.file_id is NULL AND
111 qf.file_id is null AND
112 crf.file_id is NULL
113 ) as deleted_files
114 on (
115 ABS(TIMESTAMPDIFF(SECOND, deleted_files.updated_at, last_edit.last_time_edit)) <= 2 AND
116 ABS(TIMESTAMPDIFF(SECOND, deleted_files.created_at, r.ProcessingTime)) <= 2
117 )
118 ;
119
120 # 3 sec diff
121 INSERT IGNORE INTO temp_files (r_id, f_id)
122 SELECT r.ReclamationId as id, deleted_files.id as file_id
123 FROM (
124 SELECT r.* FROM reclamation r
125 LEFT JOIN identity_cards_reclamation_file as uf on uf.ReclamationId = r.ReclamationId
126 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.ReclamationId = r.ReclamationId
127 WHERE typeReclamation = 1 AND (TransportType = 'TC' OR TransportType = 'MOTO')
128 AND (uf.ReclamationId IS NULL OR crf.ReclamationId IS NULL)
129 ) r
130 JOIN (
131 SELECT MAX(time_edit) as last_time_edit, reclamation_id
132 FROM `reclamation_diff_log` diff
133 GROUP BY reclamation_id
134 ) as last_edit
135 on (
136 r.ReclamationId = last_edit.reclamation_id
137 )
138 JOIN (
139 SELECT f.* FROM
140 upload_file f
141 LEFT JOIN identity_cards_reclamation_file as uf on uf.file_id = f.id
142 LEFT JOIN certificate_files as cf on cf.file_id = f.id
143 LEFT JOIN quality_control_files as qf on qf.file_id = f.id
144 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.file_id = f.id
145 WHERE f.user_id is null AND
146 uf.file_id is NULL AND
147 cf.file_id is NULL AND
148 qf.file_id is null AND
149 crf.file_id is NULL
150 ) as deleted_files
151 on (
152 ABS(TIMESTAMPDIFF(SECOND, deleted_files.updated_at, last_edit.last_time_edit)) <= 3 AND
153 ABS(TIMESTAMPDIFF(SECOND, deleted_files.created_at, r.ProcessingTime)) <= 3
154 )
155 ;
156
157 # 4 sec diff
158 INSERT IGNORE INTO temp_files (r_id, f_id)
159 SELECT r.ReclamationId as id, deleted_files.id as file_id
160 FROM (
161 SELECT r.* FROM reclamation r
162 LEFT JOIN identity_cards_reclamation_file as uf on uf.ReclamationId = r.ReclamationId
163 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.ReclamationId = r.ReclamationId
164 WHERE typeReclamation = 1 AND (TransportType = 'TC' OR TransportType = 'MOTO')
165 AND (uf.ReclamationId IS NULL OR crf.ReclamationId IS NULL)
166 ) r
167 JOIN (
168 SELECT MAX(time_edit) as last_time_edit, reclamation_id
169 FROM `reclamation_diff_log` diff
170 GROUP BY reclamation_id
171 ) as last_edit
172 on (
173 r.ReclamationId = last_edit.reclamation_id
174 )
175 JOIN (
176 SELECT f.* FROM
177 upload_file f
178 LEFT JOIN identity_cards_reclamation_file as uf on uf.file_id = f.id
179 LEFT JOIN certificate_files as cf on cf.file_id = f.id
180 LEFT JOIN quality_control_files as qf on qf.file_id = f.id
181 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.file_id = f.id
182 WHERE f.user_id is null AND
183 uf.file_id is NULL AND
184 cf.file_id is NULL AND
185 qf.file_id is null AND
186 crf.file_id is NULL
187 ) as deleted_files
188 on (
189 ABS(TIMESTAMPDIFF(SECOND, deleted_files.updated_at, last_edit.last_time_edit)) <= 4 AND
190 ABS(TIMESTAMPDIFF(SECOND, deleted_files.created_at, r.ProcessingTime)) <= 4
191 )
192 ;
193
194 # 5 sec diff
195 INSERT IGNORE INTO temp_files (r_id, f_id)
196 SELECT r.ReclamationId as id, deleted_files.id as file_id
197 FROM (
198 SELECT r.* FROM reclamation r
199 LEFT JOIN identity_cards_reclamation_file as uf on uf.ReclamationId = r.ReclamationId
200 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.ReclamationId = r.ReclamationId
201 WHERE typeReclamation = 1 AND (TransportType = 'TC' OR TransportType = 'MOTO')
202 AND (uf.ReclamationId IS NULL OR crf.ReclamationId IS NULL)
203 ) r
204 JOIN (
205 SELECT MAX(time_edit) as last_time_edit, reclamation_id
206 FROM `reclamation_diff_log` diff
207 GROUP BY reclamation_id
208 ) as last_edit
209 on (
210 r.ReclamationId = last_edit.reclamation_id
211 )
212 JOIN (
213 SELECT f.* FROM
214 upload_file f
215 LEFT JOIN identity_cards_reclamation_file as uf on uf.file_id = f.id
216 LEFT JOIN certificate_files as cf on cf.file_id = f.id
217 LEFT JOIN quality_control_files as qf on qf.file_id = f.id
218 LEFT JOIN certificate_of_registration_reclamation_file as crf on crf.file_id = f.id
219 WHERE f.user_id is null AND
220 uf.file_id is NULL AND
221 cf.file_id is NULL AND
222 qf.file_id is null AND
223 crf.file_id is NULL
224 ) as deleted_files
225 on (
226 ABS(TIMESTAMPDIFF(SECOND, deleted_files.updated_at, last_edit.last_time_edit)) <= 5 AND
227 ABS(TIMESTAMPDIFF(SECOND, deleted_files.created_at, r.ProcessingTime)) <= 5
228 )
229 ;
230
231 INSERT INTO identity_cards_reclamation_file (ReclamationId, file_id)
232 SELECT temp_files.r_id, temp_files.f_id
233 FROM temp_files JOIN (
234 SELECT r_id, GROUP_CONCAT(f_id) grouped_files
235 FROM temp_files
236 GROUP BY r_id
237 ) group_files
238 ON temp_files.r_id = group_files.r_id AND
239 FIND_IN_SET(f_id, grouped_files) <= 2
240 ;
241
242 INSERT INTO certificate_of_registration_reclamation_file (ReclamationId, file_id)
243 SELECT temp_files.r_id, temp_files.f_id
244 FROM temp_files JOIN (
245 SELECT r_id, GROUP_CONCAT(f_id) grouped_files
246 FROM temp_files
247 GROUP BY r_id
248 ) group_files
249 ON temp_files.r_id = group_files.r_id AND
250 FIND_IN_SET(f_id, grouped_files) > 2
251 ;
252 DROP TABLE IF EXISTS temp_files;