· 6 years ago · Jan 23, 2020, 08:10 AM
1"use strict";
2
3const turf = require('@turf/turf')
4 , moment = require('moment')
5 , geocoder = require('node-geocoder')('openstreetmap')
6 , mailer = require(__dirname + '/../mailer')
7 , alerts = require(__dirname + '/../alerts')
8 , packages = require(config.sys.root + '/package.json');
9
10const sendEmail = (id, data)=>{
11 if (!id || !data) return;
12
13 let alertname = '';
14 alerts.map((item)=>{
15 if (item.id === id) alertname = item.name;
16 });
17
18 const qy = `SELECT realname, usermail FROM ${config.pg.schema}.members WHERE id = ${data.owner} LIMIT 1`;
19 DB.query(qy, (err, ret)=>{
20 mailer({
21 template: 'alert',
22 subject : 'Alert: ' + data.plate,
23 name : ret.rows[0].realname,
24 email : ret.rows[0].usermail,
25 baseUrl : 'https://itgps.co.id',
26 plate : data.plate,
27 alert : alertname + ' ' + data.additional.geofence,
28 waktu : moment(data.waktu).format('HH:mm:ss'),
29 lokasi : data.lokasi
30 });
31 });
32}
33
34const gateway = (data)=>{
35 let previous = {};
36
37 return new Promise((resolve, reject)=>{
38 if (!data.id || data.id === null || data.id === '') return reject({status: 400, title: 'Undefined device'});
39
40 const tzo = (new Date()).getTimezoneOffset() * 60000;
41 data.received = (new Date(Date.now() - tzo)).toISOString().slice(0,-5).replace('T',' ');
42
43 const qy = `SELECT g.owner, g.plate, v.speed_limit
44 FROM ${config.pg.schema}.gps g
45 LEFT JOIN ${config.pg.schema}.vehicle v
46 ON g.owner = v.owner AND g.plate = v.plate
47 WHERE g.id = '${data.id}' LIMIT 1`;
48 DB.query(qy, (err, ret)=>{
49 if (err) return reject({id: data.id, status: 500, title:'Server Error', detail: err});
50 if(ret.rows.length < 1) return reject({id: data.id, status: 404, title:'Unregistered device'});
51
52 data.plate = ret.rows[0].plate;
53 data.owner = parseInt(ret.rows[0].owner);
54 data.speed_limit = ret.rows[0].speed_limit;
55
56 resolve(true);
57 });
58 })
59 .then(()=>{
60 //check previous data
61 return new Promise((resolve, reject)=>{
62 const qy = `SELECT * FROM tracker."${data.id}" WHERE waktu < '${data.waktu}' ORDER BY waktu DESC LIMIT 1`;
63 DB.query(qy, (err, ret)=>{
64 if (err || ret.rows.length<1) return resolve(false);
65
66 previous = ret.rows[0];
67
68 const dari = turf.point([previous.longitude, previous.latitude]);
69 const ke = turf.point([data.longitude, data.latitude]);
70 const jarak = turf.distance(dari, ke);
71
72 if (jarak > 7e-3 && previous.lokasi) return resolve(false);
73
74 resolve(previous.lokasi);
75 });
76 });
77 })
78 .then((sebelum)=>{
79 //kalau berdekatan dengan data sebelumnya, ga usah query, langsung copas aja
80 if (sebelum) return sebelum;
81
82 //kalau tidak, query geofence dan openstreetmap
83 return new Promise((resolve, reject)=>{
84 const coordinat = `ST_SetSRID(ST_MakePoint(${data.longitude},${data.latitude}), 4326)`;
85 const buffers = `GEOMETRY(ST_Buffer(GEOGRAPHY(the_geom),radius))`;
86
87 let qy = `SELECT name FROM poi
88 WHERE owner = ANY('{0,${data.owner}}'::int[])
89 AND ST_Within(${coordinat},${buffers})
90 ORDER BY owner DESC LIMIT 1`;
91 DB.query(qy, (err, ret)=>{
92 if (err || ret.rows.length < 1) {
93
94 qy = `SELECT name FROM geofence
95 WHERE owner = ANY('{0,${data.owner}}'::int[])
96 AND ST_Within(${coordinat},the_geom)
97 AND vehicles LIKE '%${data.id}%'
98 ORDER BY owner, registered DESC LIMIT 1`;
99 DB.query(qy, (error, res)=>{
100 if (error || res.rows.length < 1) resolve('');
101 else {
102 data.additional.geofence = res.rows[0].name;
103 resolve('[' + res.rows[0].name + '] ');
104 }
105 });
106 } else {
107 data.additional.geofence = ret.rows[0].name;
108 resolve('[' + ret.rows[0].name + '] ');
109 }
110 });
111 })
112 .then((geofence)=>{
113 let lokasi = geofence || '';
114/*
115 //geocoder postgis
116 return new Promise((resolve, reject) => {
117 const jalan = `SELECT ST_Distance(the_geom, ST_SetSRID(ST_Point(${data.longitude}, ${data.latitude}), 4326))*11100 AS jarak,
118 name, maxspeed FROM jalan
119 WHERE ST_Crosses(the_geom, ST_Buffer(ST_SetSRID(ST_Point(${data.longitude}, ${data.latitude}), 4326), 0.0025))
120 AND name IS NOT NULL ORDER BY jarak LIMIT 1`;
121 DB.query(jalan, (err, res) => {
122 if (!err && res.rows.length > 0) {
123 lokasi += res.rows[0].name;
124 if (parseInt(res.rows[0].maxspeed) > 10 && parseInt(res.rows[0].maxspeed) < parseInt(data.speed)) data.alert = 19;
125 }
126
127 const area = ` SELECT type, region, province, country FROM administrasi
128 WHERE ST_Within(ST_SetSRID(ST_Point(${data.longitude}, ${data.latitude}), 4326), the_geom)
129 LIMIT 1`;
130 DB.query(area, (err, res) => {
131 if (!err && res.rows.length > 0) {
132 if (res.rows[0].type.toLowerCase() === 'kabupaten' && res.rows[0].region) lokasi += ', Kab.';
133 if (res.rows[0].region) lokasi += ' ' + res.rows[0].region;
134 if (res.rows[0].province) lokasi += ', ' + res.rows[0].province.replace('Daerah Khusus Ibukota','DKI').replace('Daerah Istimewa','');
135 //if (res.rows[0].country) lokasi += ', ' + res.rows[0].country;
136 }
137
138 resolve(lokasi);
139 });
140 });
141 });
142*/
143 //geocoder openstreetmap / google
144 return geocoder.reverse({lat:data.latitude, lon:data.longitude})
145 .then((geo)=>{
146 if (geo[0].streetName) lokasi += geo[0].streetName + (geo[0].streetNumber?' No.'+geo[0].streetNumber:'') + ', ';
147
148 //City
149 const city = geo[0].city ? geo[0].city.substr(0, 2).toUpperCase() : false;
150 if (city && city!=='RW' && city!=='RT') lokasi += geo[0].city + ', ';
151
152 //capitalize
153 lokasi = lokasi.replace(/\w\S*/g, (txt)=>txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase());
154
155 //State
156 lokasi += geo[0].state.replace('Daerah Khusus Ibukota','DKI').replace('Daerah Istimewa','');
157
158 return lokasi;
159 })
160 .catch((err)=>{
161 return lokasi;
162 });
163 });
164 })
165 .then((lokasi)=>{
166 data.lokasi = lokasi !== '' ? lokasi : previous.lokasi;
167
168 const acc = parseInt(data.input.split('')[2]) === 1;
169 if (!previous) return {};
170
171 if (!previous.mileage || previous.mileage === '' || parseInt(previous.mileage) === 0) previous.mileage = 0;
172
173 if (data.alert === 37 && data.rfid) {
174 return new Promise((resolve, reject)=>{
175 qy = `INSERT INTO ${config.pg.schema}.driver_check VALUES (DEFAULT, $1, $2, $3, $4)`;
176 DB.query(qy, [data.rfid, data.id, acc, data.waktu], (err)=>{
177 previous.rfid = (acc && !err) ? data.rfid : null;
178
179 resolve(previous);
180 });
181 });
182 }
183 else return (previous || {});
184 })
185 .then((obj)=>{
186 let nowAlert = 0, emailAlert = [20, 23, 100];
187
188 obj.additional = obj.additional || {};
189 data.additional = data.additional || {};
190
191 //mileage
192 if (!data.mileage || parseInt(data.mileage) === 0) {
193 if (!obj.latitude || !obj.longitude) return obj;
194
195 const dari = turf.point([obj.longitude, obj.latitude]);
196 const ke = turf.point([data.longitude, data.latitude]);
197 const jarak = turf.distance(dari, ke);
198
199 data.mileage = obj.mileage + parseFloat(parseFloat(jarak).toFixed(2));
200 }
201
202 //alerts
203 if ( moment(data.waktu) > moment(new Date()) + 2e6) nowAlert = 100;
204 if (data.additional.geofence && (!obj.additional.geofence || data.additional.geofence !== obj.additional.geofence)) {
205 nowAlert = 20;
206 } else if (obj.additional.geofence && !data.additional.geofence) {
207 nowAlert = 21;
208 } else if (data.speed > data.speed_limit && data.speed_limit > 5) {
209 nowAlert = -19;
210 data.additional.speed_limit = data.speed_limit;
211 }
212
213 //Acceleration
214 const dSpeed = (obj.speed - data.speed) * 1000 / 3600;
215 const dWaktu = (moment(data.waktu) - moment(obj.waktu)) / 1000;
216 data.additional.acceleration = parseFloat( (dSpeed / dWaktu).toFixed(2) );
217
218 if (data.additional.acceleration && data.additional.acceleration >= 1) nowAlert = 101;
219 else if (data.additional.acceleration && data.additional.acceleration <= -1.5) nowAlert = 30;
220
221 //email alert
222 if (nowAlert && emailAlert.indexOf(Math.abs(nowAlert)) > -1) sendEmail(nowAlert, data);
223 if (data.alert && emailAlert.indexOf(Math.abs(data.alert)) > -1) sendEmail(data.alert, data);
224
225 if ((!data.alert || parseInt(data.alert) === 0) && nowAlert > 0) {
226 data.alert = nowAlert;
227 }
228
229 if ((!data.alert || parseInt(data.alert) === 0) && data.satelit < 3) {
230 data.alert = 24;
231 }
232
233 delete data.speed_limit;
234 })
235 .then(()=>{
236 if (!data.id || data.id === null || data.id === '') return Promise.reject({status: 400, title: 'Undefined device'});
237
238 /*************************************************/
239 /*************** PostgreSQL Section **************/
240 /*************************************************/
241
242 //create new tracker history table if not exists
243 let qyt = `CREATE TABLE IF NOT EXISTS tracker."${data.id}" (LIKE ${config.pg.schema}.tracker INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);`;
244 qyt += `ALTER TABLE tracker."${data.id}" DROP COLUMN IF EXISTS id, DROP COLUMN IF EXISTS id_gps;`;
245
246 //update query
247 const qyu = [ `UPDATE ${config.pg.schema}.tracker SET`
248 , `waktu = '${data.waktu}',`
249 , `longitude = ${data.longitude},`
250 , `latitude = ${data.latitude},`
251 , `altitude = ${data.altitude},`
252 , `speed = ${data.speed},`
253 , `heading = ${data.heading},`
254 , `satelit = ${data.satelit},`
255 , `signal = ${data.signal},`
256 , `mileage = ${data.mileage},`
257 , `runtime = ${data.runtime},`
258 , `alert = ${data.alert},`
259 , `input = '${data.input}',`
260 , `output = '${data.output}',`
261 , "rfid = " + (data.rfid ? "'" + data.rfid + "'":"''") + ","
262 , `lokasi = '${(data.lokasi || '').replace("'", "''")}',`
263 , "additional = '" + (JSON.stringify(data.additional) || '{}') + "'::json,"
264 , `received = '${data.received}'`
265 , `WHERE id_gps = '${data.id}' AND waktu < '${data.waktu}'`
266 ].join(' ');
267
268 const fields = ["id_gps", "waktu", "longitude", "latitude", "speed", "heading", "altitude", "satelit", "signal",
269 "mileage", "runtime", "alert", "input", "output", "rfid", "lokasi", "received", "additional" ];
270
271 const values = [ `'${data.id}'`, `'${data.waktu}'`, data.longitude, data.latitude, data.speed, data.heading,
272 data.altitude , data.satelit, data.signal, data.mileage, data.runtime, data.alert, `'${data.input}'`,
273 `'${data.output}'`, (data.rfid ? `'${data.rfid}'`:"NULL"), `'${(data.lokasi || '').replace("'", "''")}'`,
274 `'${data.received}'`, `'${JSON.stringify(data.additional||{})}'::json`];
275
276 const qyi = `INSERT INTO ${config.pg.schema}.tracker (${fields.join(',')}) SELECT ${values.join(',')}`;
277
278 //insert or update to tracker
279 const qy = `WITH upsert AS (${qyu} RETURNING id_gps) ${qyi}
280 WHERE NOT EXISTS (SELECT * FROM upsert)
281 AND NOT EXISTS (
282 SELECT id_gps FROM ${config.pg.schema}.tracker
283 WHERE id_gps = '${data.id}'
284 )`;
285
286 //insert query into tracker_idgps table (postgresql)
287 const qyid = qyi.replace('id_gps,' , '')
288 .replace(`'${data.id}',` , '')
289 .replace(config.pg.schema + '.tracker' , `tracker."${data.id}"`) +
290 ` WHERE NOT EXISTS (
291 SELECT waktu FROM tracker."${data.id}"
292 WHERE waktu = '${data.waktu}'
293 AND alert = ${data.alert}
294 AND input = '${data.input}'
295 )`;
296
297 return [qy, /*qyt,*/ qyid];
298 })
299 .then((query)=>{
300 return new Promise((resolve, reject)=>{
301 const qy = (query.join(";") + ';').replace(';;', ';');
302
303 DB.query(qy, (err,ret)=>{
304 if (err) {
305 err.id = data.id;
306 err.status = 500;
307 reject(err);
308 } else resolve(true);
309 });
310 });
311 })
312 .then(()=>{
313 return [null, data];
314 })
315 .catch((err)=>{
316 return [[err], null];
317 });
318};
319
320module.exports = gateway;