· 10 years ago · Sep 13, 2016, 02:24 PM
1DROP TRIGGER IF EXISTS `trigger_gps_track`;
2DELIMITER //
3CREATE TRIGGER `trigger_gps_track` BEFORE INSERT ON `gps_track`
4 FOR EACH ROW BEGIN
5 DECLARE device_id int;
6 DECLARE oldID int;
7
8 DECLARE newID int;
9
10 DECLARE distance float;
11
12 DECLARE reportID int;
13 DECLARE localtimes datetime;
14 DECLARE localTimeCurrent datetime;
15
16 DECLARE unitID varchar(50);
17 DECLARE landmarkName varchar(255);
18
19 DECLARE location varchar(255);
20
21 DECLARE initialMileage float;
22
23 DECLARE analog_input1 varchar(50);
24
25 DECLARE analog_input2 varchar(50);
26
27 DECLARE analog_input1_method varchar(50);
28
29 DECLARE analog_input2_method varchar(50);
30
31 DECLARE analog_input1_highvolt varchar(50);
32 DECLARE analog_input2_highvolt varchar(50);
33 DECLARE analog_input1_variance varchar(50);
34 DECLARE analog_input2_variance varchar(50);
35 DECLARE analog_input1_highvolt_value varchar(50);
36 DECLARE analog_input2_highvolt_value varchar(50);
37 DECLARE analog_input1_value varchar(50);
38
39 DECLARE analog_input2_value varchar(50);
40
41 DECLARE sos_status varchar(50);
42
43 DECLARE acc_status varchar(50);
44
45 DECLARE input_flags varchar(8);
46
47 DECLARE output_flags varchar(8);
48
49 DECLARE i1 bit;
50
51 DECLARE i2 bit;
52
53 DECLARE i3 bit;
54
55 DECLARE i4 bit;
56
57 DECLARE i5 bit;
58
59 DECLARE i6 bit;
60
61 DECLARE i7 bit;
62
63 DECLARE i8 bit;
64
65 DECLARE o1 bit;
66
67 DECLARE o2 bit;
68 DECLARE o3 bit;
69
70 DECLARE o4 bit;
71
72 DECLARE o5 bit;
73
74 DECLARE o6 bit;
75
76 DECLARE o7 bit;
77
78 DECLARE o8 bit;
79
80 DECLARE driver_id int;
81 DECLARE alertIn int;
82
83 DECLARE numberTelp varchar(20);
84 DECLARE smsActive varchar(20);
85 DECLARE isSMS int;
86 DECLARE isEvent int;
87 DECLARE CustId int;
88 DECLARE reportDescription text;
89
90 DECLARE remarks varchar(255);
91 DECLARE DeviceName varchar(100);
92
93 /**********************END DECLARE VARIABLE********************/
94 SELECT device_id, devices.unit_id,devices.device_name, devices.initial_mileage,report_id,
95 ISNULL(NEW.mileage) - ISNULL(gps_track_active.mileage),
96 gps_track_active.local_time,NEW.analog_input1,devices_features.analog_input1_method,devices_features.analog_input1_highvolt,
97 devices_features.analog_input1_variance,devices_features.analog_input1_highvolt_value, NEW.analog_input2,
98
99 devices_features.analog_input2_method,devices_features.analog_input2_highvolt,devices_features.analog_input2_variance,
100 devices_features.analog_input2_highvolt_value,
101 getInputData(devices_features.sos_input, devices_features.sos_input_enable, NEW.input_flags),
102 getInputData(devices_features.acc_input, devices_features.acc_input_enable, NEW.input_flags),
103 NEW.input_flags,NEW.output_flags,
104 NEW.driver_id,
105 DATE_ADD(gps_time, INTERVAL '7' HOUR),SPLIT_STR(getLandmarkByLatLng(NEW.device_id,NEW.latitude,NEW.longitude),'|',1),
106 SPLIT_STR(getLandmarkByLatLng(NEW.device_id,NEW.latitude,NEW.longitude),'|',2),
107 NEW.id, gps_track_active.current_id
108
109 INTO
110 device_id, unitID,DeviceName, initialMileage,reportID,distance,localTimeCurrent,analog_input1,analog_input1_method,
111 analog_input1_highvolt,analog_input1_variance,analog_input1_highvolt_value,analog_input2,
112 analog_input2_method,analog_input2_highvolt,analog_input2_variance,analog_input2_highvolt_value,sos_status,acc_status,
113 input_flags,output_flags,driver_id,localtimes,landmarkName,alertIn,newID, oldID
114 FROM gps_track_active
115 INNER JOIN (SELECT * FROM devices WHERE id = NEW.device_id) AS devices
116 ON gps_track_active.device_id = devices.id
117 INNER JOIN devices_features
118 ON devices.id = devices_features.id;
119
120 IF SUBSTRING(input_flags, 8, 1) = 1 THEN
121 SET i1 = 1;
122 ELSE
123 SET i1 = 0;
124
125 END IF;
126 IF SUBSTRING(input_flags, 7, 1) = 1 THEN
127 SET i2 = 1;
128 ELSE
129 SET i2 = 0;
130
131
132 END IF;
133 IF SUBSTRING(input_flags, 6, 1) = 1 THEN
134 SET i3 = 1;
135 ELSE
136 SET i3 = 0;
137
138 END IF;
139 IF SUBSTRING(input_flags, 5, 1) = 1 THEN
140 SET i4 = 1;
141 ELSE
142 SET i4 = 0;
143
144
145 END IF;
146 IF SUBSTRING(input_flags, 4, 1) = 1 THEN
147 SET i5 = 1;
148 ELSE
149 SET i5 = 0;
150
151 END IF;
152 IF SUBSTRING(input_flags, 3, 1) = 1 THEN
153 SET i6 = 1;
154 ELSE
155 SET i6 = 0;
156
157 END IF;
158 IF SUBSTRING(input_flags, 2, 1) = 1 THEN
159 SET i7 = 1;
160 ELSE
161 SET i7 = 0;
162 END IF;
163 IF SUBSTRING(input_flags, 1, 1) = 1 THEN
164 SET i8 = 1;
165 ELSE
166 SET i8 = 0;
167 END IF;
168
169
170 IF SUBSTRING(output_flags, 8, 1) = 1 THEN
171 SET o1 = 1;
172 ELSE
173 SET o1 = 0;
174 END IF;
175
176 IF SUBSTRING(output_flags, 7, 1) = 1 THEN
177 SET o2 = 1;
178 ELSE
179 SET o2 = 0;
180 END IF;
181 IF SUBSTRING(output_flags, 6, 1) = 1 THEN
182 SET o3 = 1;
183 ELSE
184 SET o3 = 0;
185 END IF;
186 IF SUBSTRING(output_flags, 5, 1) = 1 THEN
187 SET o4 = 1;
188 ELSE
189 SET o4 = 0;
190 END IF;
191
192 IF SUBSTRING(output_flags, 4, 1) = 1 THEN
193 SET o5 = 1;
194 ELSE
195 SET o5 = 0;
196 END IF;
197 IF SUBSTRING(output_flags, 3, 1) = 1 THEN
198 SET o6 = 1;
199 ELSE
200 SET o6 = 0;
201 END IF;
202 IF SUBSTRING(output_flags, 2, 1) = 1 THEN
203 SET o7 = 1;
204 ELSE
205 SET o7 = 0;
206 END IF;
207 IF SUBSTRING(output_flags, 1, 1) = 1 THEN
208 SET o8 = 1;
209 ELSE
210 SET o8 = 0;
211 END IF;
212
213
214 IF distance < 0 THEN
215 SET distance = 0;
216 END IF;
217 IF distance IS NULL THEN
218 SET distance = 0;
219 END IF;
220
221
222 SELECT is_sms, is_event,report_description INTO isSMS, isEvent,reportDescription FROM reports WHERE report_id = NEW.report_id;
223
224
225 IF NOT oldID IS NULL THEN
226
227 UPDATE gps_track_active
228
229 SET
230
231 unit_id = unitID,
232
233 gps_time = NEW.gps_time,
234
235 latitude = NEW.latitude,
236
237 longitude = NEW.longitude,
238
239 speed = NEW.speed,
240
241 heading = NEW.heading,
242
243 altitude = NEW.altitude,
244
245 satellite = NEW.satellite,
246
247 mileage = ISNULL(NEW.mileage),
248
249 report_id = NEW.report_id,
250
251 input_flags = NEW.input_flags,
252
253 output_flags = NEW.output_flags,
254
255 device_status = NEW.device_status,
256
257 is_from_sms = NEW.is_from_sms,
258
259 driver_id = driver_id,
260
261 current_id = NEW.id,
262
263 mileage_distance = distance,
264
265 analog_input1 = NEW.analog_input1,
266
267 analog_input2 = NEW.analog_input2,
268
269 rfid_tag = NEW.rfid_tag,
270
271 barcode = NEW.barcode,
272
273 last_activity = NOW(),
274
275 local_time = localtimes,
276
277 landmark_name = landmarkName,
278
279 location = NEW.location,
280
281 initial_mileage = initialMileage,
282
283 analog_input1_value = analog_input1_value,
284
285 analog_input2_value = analog_input2_value,
286
287 sos_status = sos_status,
288
289 acc_status = acc_status,
290
291 i1 = i1, i2 = i2, i3 = i3, i4 = i4, i5 = i5, i6 = i6, i7 = i7, i8 = i8,
292
293 o1 = o1, o2 = o2, o3 = o3, o4 = o4, o5 = o5, o6 = o6, o7 = o7, o8 = o8,
294
295 is_sms = isSMS
296
297 WHERE gps_track_active.device_id = NEW.device_id;
298
299 ELSE
300 select unit_id into unitID from devices where id = new.device_id;
301
302 INSERT INTO gps_track_active
303 (device_id, unit_id, gps_time, latitude, longitude,
304 speed, heading, altitude, satellite,
305 mileage, report_id, input_flags, output_flags,
306 device_status, is_from_sms, driver_id, mileage_distance, current_id,
307 analog_input1, analog_input2, rfid_tag, barcode, last_activity, local_time, landmark_name,
308 location,
309 initial_mileage,
310 analog_input1_value,
311 analog_input2_value,
312 sos_status,
313 acc_status,
314 i1, i2, i3, i4, i5, i6, i7, i8, o1, o2, o3, o4, o5, o6, o7, o8, is_sms
315 ) VALUES(NEW.device_id,unitID,NEW.gps_time,NEW.latitude,NEW.longitude,NEW.speed,NEW.heading,
316 NEW.altitude,NEW.satellite,ISNULL(NEW.mileage),NEW.report_id,NEW.input_flags,NEW.output_flags,
317 NEW.device_status,NEW.is_from_sms,driver_id,distance,NEW.id,
318 NEW.analog_input1,NEW.analog_input2,NEW.rfid_tag,NEW.barcode,NOW(),localtimes,landmarkName,NEW.location,
319 initialMileage,analog_input1_value,analog_input2_value,sos_status,acc_status,
320 i1, i2, i3, i4, i5, i6, i7, i8, o1, o2, o3, o4, o5, o6, o7, o8, isSMS
321 );
322 END IF; /*IF NOT oldID IS NULL THEN*/
323
324 SET NEW.prev_id = oldID, NEW.mileage_distance = distance, NEW.local_time = localtimes, NEW.landmark_name = landmarkName,
325 NEW.initial_mileage = initialMileage,
326 NEW.analog_input1_value = analog_input1_value,
327 NEW.analog_input2_value = analog_input2_value,
328 NEW.sos_status = sos_status,
329 NEW.acc_status = acc_status,
330 NEW.i1 = i1, NEW.i2 = i2, NEW.i3 = i3, NEW.i4 = i4, NEW.i5 = i5, NEW.i6 = i6, NEW.i7 = i7, NEW.i8 = i8,
331 NEW.o1 = o1, NEW.o2 = o2, NEW.o3 = o3, NEW.o4 = o4, NEW.o5 = o5, NEW.o6 = o6, NEW.o7 = o7, NEW.o8 = o8,
332 NEW.unit_id = unitID;
333
334 SELECT t2.mobile_number,t2.sms_active,t1.customer_id INTO numberTelp,smsActive,CustId
335 FROM (SELECT * FROM devices WHERE id=NEW.device_id) as t1
336 INNER JOIN
337 (SELECT id, customer_code, sms_active, mobile_number FROM customers) as t2
338 ON t2.id = t1.customer_id;
339
340
341 IF isEvent = 1 OR (landmarkName IS NOT NULL AND alertIn = 1) THEN
342 INSERT INTO gps_events
343 (device_id, unit_id, gps_time, latitude, longitude,
344 speed, heading, altitude, satellite,
345 mileage, report_id, input_flags, output_flags,
346 device_status, is_from_sms, driver_id, mileage_distance, status,
347 analog_input1, analog_input2, rfid_tag, barcode, local_time, landmark_name, location,
348 initial_mileage,
349 analog_input1_value,
350 analog_input2_value,
351 sos_status,
352 acc_status,
353 i1, i2, i3, i4, i5, i6, i7, i8, o1, o2, o3, o4, o5, o6, o7, o8, is_sms
354 )
355 VALUES(NEW.device_id,unitID,NEW.gps_time,NEW.latitude,NEW.longitude,NEW.speed,NEW.heading,
356 NEW.altitude,NEW.satellite,ISNULL(NEW.mileage),NEW.report_id,NEW.input_flags,NEW.output_flags,
357 NEW.device_status,NEW.is_from_sms,driver_id,distance,0,
358 NEW.analog_input1,NEW.analog_input2,NEW.rfid_tag,NEW.barcode,localtimes,landmarkName,NEW.location,
359 initialMileage,analog_input1_value,analog_input2_value,sos_status,acc_status,
360 i1, i2, i3, i4, i5, i6, i7, i8, o1, o2, o3, o4, o5, o6, o7, o8, isSMS
361 );
362
363 /*insert table outbox apabila isSMS = 1*/
364
365 IF isSMS = 1 AND smsActive = 1 THEN
366
367 INSERT INTO outbox (`UpdatedInDB`, `InsertIntoDB`, `SendingDateTime`, `SendBefore`, `SendAfter`, `Text`, `DestinationNumber`, `Coding`, `UDH`, `Class`, `TextDecoded`, `ID`, `MultiPart`, `RelativeValidity`, `SenderID`, `SendingTimeOut`, `DeliveryReport`, `CreatorID`)
368 VALUES (CURRENT_TIMESTAMP, '0000-00-00 00:00:00', '0000-00-00 00:00:00', '23:59:59', '00:00:00', NULL,
369 numberTelp, 'Default_No_Compression', NULL, '-1',
370 CONCAT(NOW(),', Device Name: ',CONCAT(DeviceName,', Status : ',CONCAT(reportDescription,', Posisi : ',NEW.latitude,NEW.longitude))), NULL, 'false', '-1', NULL, '0000-00-00 00:00:00', 'default', '');
371
372 END IF;
373
374 END IF;
375
376END
377//
378DELIMITER ;