· 7 years ago · Jan 08, 2019, 12:42 PM
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\GeoLocation;
6use App\Notifications\SlackNotification;
7use Carbon\Carbon;
8use GuzzleHttp\Exception\RequestException;
9use Illuminate\Console\Command;
10use Illuminate\Support\Facades\DB;
11use Illuminate\Support\Facades\Mail;
12use Illuminate\Support\Facades\Schema;
13use Illuminate\Support\Facades\Storage;
14use PhpOffice\PhpSpreadsheet\Reader\Xlsx as Excel;
15use GuzzleHttp\Client as GuzzleClient;
16
17class ImportGeoStores extends Command
18{
19 public $key_2 = 2;
20 public $key_3 = 10;
21 public $key_4 = 30;
22 public $emails = [
23 "prod@mobilewater.nl",
24 "report@mobilewater.nl"
25 ];
26 public $testmail = 'dustin.van.hal@mobilewater.nl';
27 protected $storeInsert = [];
28
29 protected $geoInsert = [];
30 /**
31 * The name and signature of the console command.
32 *
33 * @var string
34 */
35 protected $signature = 'import:stores';
36
37 /**
38 * The console command description.
39 *
40 * @var string
41 */
42 protected $description = 'Command description';
43
44 protected $insertData = [];
45 protected $locationData = [];
46
47 /**
48 * Create a new command instance.
49 *
50 * @return void
51 */
52 protected $sftp;
53 public $count = 0;
54 public $rows;
55
56 public function __construct()
57 {
58 parent::__construct();
59 }
60
61 /**
62 * Execute the console command.
63 *
64 * @return mixed
65 */
66 public function handle()
67 {
68 $this->sftp = env('IMPORT_DISK', 'local');
69
70 //new_store table word alleen gemaakt wanneer file gevonden is! Hierdoor blijft de DB netjes.
71 $this->info("Import script");
72 $this->info('made by: Dustin');
73 $this->info('Searching for xlsx files on SFTP');
74
75 try {
76 $files = Storage::disk($this->sftp)->files('/');
77 } catch (\RuntimeException $exception) {
78 $maildata = [
79 'error' => $exception->getCode(),
80 'messageType' => 'sftp'
81 ];
82 Mail::send('template-mails/notification', $maildata, function ($message) {
83 //selecting target and subject of mail
84 $message->to($this->testmail)
85 ->from('report@mwater.nl', 'Mobile water report')
86 ->subject('Exception caught on Lebara store import script');
87
88 });
89 exit;
90 }
91
92 foreach ($files as $file) {
93 if (pathinfo($file, PATHINFO_EXTENSION) != 'xlsx' OR str_contains(PATHINFO_BASENAME, '~$')) {
94 continue;
95 }
96 $foundFile = $file;
97 break;
98 }
99
100 $file = null;
101 if (empty($foundFile)) {
102 $this->error('----> NO FILES FOUND!');
103 return;
104 }
105 $this->info('FILE FOUND!');
106 DB::statement('DROP TABLE IF EXISTS new_store');
107 DB::statement('CREATE TABLE IF NOT EXISTS new_store LIKE store; ');
108 $this->info('Downloading one file!');
109 try {
110 $fileStored = Storage::disk($this->sftp)->get($foundFile);
111 } catch (\RuntimeException $exception) {
112 $maildata = [
113 'error' => $exception->getCode(),
114 'messageType' => 'sftp'
115 ];
116
117 $data = [
118 'status' => 'failed',
119 'count' => $this->count,
120 'highestRow' => $this->rows,
121 'message' => strtoupper('The import script has terminated! :X:'),
122 'statusCode' => $exception->getcode(),
123 'statusMessage' => $exception->getMessage(),
124 'terminatedAt' => 'SFTP'
125 ];
126 $target = GeoLocation::all()->first();
127 $target->notify(new SlackNotification($data));
128
129 Mail::send('template-mails/notification', $maildata, function ($message) {
130 //selecting target and subject of mail
131 $message->to($this->testmail)
132 ->from('report@mwater.nl', 'Mobile water report')
133 ->subject('Exception caught on Lebara store import script');
134
135 });;
136 exit;
137 }
138
139 $local = storage_path('import/import.xlsx');
140
141 // Storage::disk('local')->put($local, $fileStored);
142
143 //overswitchen naar storage functie wanneer live ! Bug op Dustins pcs met rechten
144 $handle = fopen($local, 'w');
145 fputs($handle, $fileStored);
146 fclose($handle);
147
148 $this->info('Reading data from file');
149
150 $reader = new Excel();
151 $reader->setReadDataOnly(true);
152 $object = $reader->load($local);
153 $sheet = $object->getActiveSheet();
154 $lastRow = $sheet->getHighestRow();
155 $this->rows = $lastRow - 1;
156
157 $this->info('Reading lines & imports if needed!');
158
159 for ($row = 2; $row <= $lastRow; $row++) {
160 $this->count += 1;
161 $this->info('Row(' . $this->count . '/' . $this->rows . ')', false);
162 $hash = md5($sheet->getCell('F' . $row)->getValue() . $sheet->getCell('E' . $row)->getValue() . $sheet->getCell('G' . $row)->getValue());
163 $geoLocation = null;
164
165 $addressString = $sheet->getCell('F' . $row)->getValue() . ','
166 . $sheet->getCell('G' . $row)->getValue() . ', '
167 . $sheet->getCell('E' . $row)->getValue() . ','
168 . $sheet->getCell('H' . $row)->getValue();
169
170 if (in_array($hash, array_keys($this->geoInsert))) {
171 continue;
172 }
173
174 $geoLocation = GeoLocation::query()->where('hash', '=', $hash)->first();
175
176 if (empty($geoLocation)) {
177 $geoLocation = $this->setupGeolocation(
178 $addressString,
179 $hash,
180 ['postcode' => $sheet->getCell('E' . $row)->getValue(), 'city' => $sheet->getCell('G' . $row)->getValue()]);
181
182 if (empty($geoLocation)) {
183 continue;
184 }
185 }
186
187 $result = [
188 'y' => $geoLocation['latitude'],
189 'x' => $geoLocation['longitude'],
190 'name' => $sheet->getCell('C' . $row)->getValue(),
191 // 'D' has the same value as C
192 'company' => $sheet->getCell('C' . $row)->getValue(),
193 'postcode' => $geoLocation['postcode'],
194 'street' => $sheet->getCell('F' . $row)->getValue(),
195 'city' => $geoLocation['city'],
196 'country' => $sheet->getCell('H' . $row)->getValue(),
197 'offer_sim' => $sheet->getCell('I' . $row)->getValue(),
198 'offer_call_credit' => $sheet->getCell('J' . $row)->getValue(),
199 'offer_4g_credit' => $sheet->getCell('K' . $row)->getValue(),
200
201 // voor de api de zoom levels
202 'zoom_1' => floor($geoLocation['latitude']) . floor($geoLocation['longitude']),
203 'zoom_2' => floor($geoLocation['latitude'] * $this->key_2) . floor($geoLocation['longitude'] * $this->key_2),
204 'zoom_3' => floor($geoLocation['latitude'] * $this->key_3) . floor($geoLocation['longitude'] * $this->key_3),
205 'zoom_4' => floor($geoLocation['latitude'] * $this->key_4) . floor($geoLocation['longitude'] * $this->key_4),
206 ];
207
208 array_push($this->storeInsert, $result);
209
210 if (count($this->storeInsert) >= 100) {
211 $this->info('Inserting batch of 100 store records ');
212 try {
213 DB::table('new_store')->insert($this->storeInsert);
214 } catch (\PDOException $exception) {
215 $maildata = [
216 'error' => $exception->getCode(),
217 'messageType' => 'db1'
218 ];
219
220 $data = [
221 'status' => 'failed',
222 'count' => $this->count,
223 'highestRow' => $this->rows,
224 'message' => strtoupper('The import script has terminated! :X:'),
225 'statusCode' => $exception->getcode(),
226 'statusMessage' => $exception->getMessage(),
227 'terminatedAt' => 'Database -> new_store'
228 ];
229 $target = GeoLocation::all()->first();
230 $target->notify(new SlackNotification($data));
231
232 Mail::send('template-mails/notification', $maildata, function ($message) {
233 //selecting target and subject of mail
234 $message->to($this->testmail)
235 ->from('report@mwater.nl', 'Mobile water report')
236 ->subject('Exception caught on Lebara store import script');
237
238 });
239 exit;
240 }
241 $this->storeInsert = [];
242 }
243
244 if (count($this->geoInsert) >= 100) {
245 $this->info('Inserting batch of 100 geo location records');
246 try {
247 DB::table('geo_locations')->insert($this->geoInsert);
248 } catch (\PDOException $exception) {
249 $maildata = [
250 'error' => $exception->getCode(),
251 'messageType' => 'db2'
252 ];
253
254 $data = [
255 'status' => 'failed',
256 'count' => $this->count,
257 'highestRow' => $this->rows,
258 'message' => strtoupper('The import script has terminated! :X:'),
259 'statusCode' => $exception->getcode(),
260 'statusMessage' => $exception->getMessage(),
261 'terminatedAt' => 'Database -> geo_locations'
262 ];
263 $target = GeoLocation::all()->first();
264 $target->notify(new SlackNotification($data));
265
266 Mail::send('template-mails/notification', $maildata, function ($message) {
267 //selecting target and subject of mail
268 $message->to($this->testmail)
269 ->from('report@mwater.nl', 'Mobile water report')
270 ->subject('Exception caught on Lebara store import script');
271 });
272 exit;
273 }
274 $this->geoInsert = [];
275 }
276 }
277
278 if (count($this->storeInsert) >= 1) {
279 try {
280 DB::table('new_store')->insert($this->storeInsert);
281 $this->storeInsert = [];
282 } catch (\PDOException $exception) {
283 $maildata = [
284 'error' => $exception->getCode(),
285 'messageType' => 'db1'
286 ];
287
288 $data = [
289 'status' => 'failed',
290 'count' => $this->count,
291 'highestRow' => $this->rows,
292 'message' => strtoupper('The import script has terminated! :X:'),
293 'statusCode' => $exception->getcode(),
294 'statusMessage' => $exception->getMessage(),
295 'terminatedAt' => 'Database -> new_store'
296 ];
297 $target = GeoLocation::all()->first();
298 $target->notify(new SlackNotification($data));
299
300 Mail::send('template-mails/notification', $maildata, function ($message) {
301 //selecting target and subject of mail
302 $message->to($this->testmail)
303 ->from('report@mwater.nl', 'Mobile water report')
304 ->subject('Exception caught on Lebara store import script');
305
306 });
307 exit;
308 }
309 }
310
311 if (count($this->geoInsert) >= 1) {
312 try {
313 DB::table('geo_locations')->insert($this->geoInsert);
314 $this->geoInsert = [];
315 } catch (\PDOException $exception) {
316 $maildata = [
317 'error' => $exception->getCode(),
318 'messageType' => 'db2'
319 ];
320
321 $data = [
322 'status' => 'failed',
323 'count' => $this->count,
324 'highestRow' => $this->rows,
325 'message' => strtoupper('The import script has terminated! :X:'),
326 'statusCode' => $exception->getcode(),
327 'statusMessage' => $exception->getMessage(),
328 'terminatedAt' => 'Database -> geo_locations'
329 ];
330 $target = GeoLocation::all()->first();
331 $target->notify(new SlackNotification($data));
332
333 Mail::send('template-mails/notification', $maildata, function ($message) {
334 //selecting target and subject of mail
335 $message->to($this->testmail)
336 ->from('report@mwater.nl', 'Mobile water report')
337 ->subject('Exception caught on Lebara store import script');
338 });
339 exit;
340 }
341 }
342
343 $this->replaceTable();
344 Storage::disk('local')->delete($local);
345 try {
346 Storage::disk($this->sftp)->rename($foundFile, $foundFile . '.ready');
347 } catch (\RuntimeException $exception) {
348 $maildata = [
349 'error' => $exception->getCode(),
350 'messageType' => 'sftp'
351 ];
352
353 $data = [
354 'status' => 'failed',
355 'count' => $this->count,
356 'highestRow' => $this->rows,
357 'message' => strtoupper('The import script has terminated! :X:'),
358 'statusCode' => $exception->getcode(),
359 'statusMessage' => $exception->getMessage(),
360 'terminatedAt' => 'SFTP'
361 ];
362 $target = GeoLocation::all()->first();
363 $target->notify(new SlackNotification($data));
364
365 Mail::send('template-mails/notification', $maildata, function ($message) {
366 //selecting target and subject of mail
367 $message->to($this->testmail)
368 ->from('report@mwater.nl', 'Mobile water report')
369 ->subject('Exception caught on Lebara store import script');
370
371 });
372 exit;
373 }
374 $maildata = [
375 'rows' => $this->count,
376 'messageType' => 'succeed'
377 ];
378 Mail::send('template-mails/notification', $maildata, function ($message) {
379 //selecting target and subject of mail
380 $message->to($this->testmail)
381 ->from('report@mwater.nl', 'Mobile water report')
382 ->subject('Lebara store import script succeed');
383
384 });
385 $data = [
386 'status' => 'succeed',
387 'count' => $this->count,
388 'highestRow' => $this->rows,
389 'message' => strtoupper('The import script has been successfully completed! :heavy_check_mark:'),
390 'statusCode' => '200',
391 'statusMessage' => 'OK'
392 ];
393 $target = GeoLocation::all()->first();
394 $target->notify(new SlackNotification($data));
395 $this->info('The import script has been successfully completed');
396
397 }
398
399 public function setupGeolocation(string $addressString, $hash, array $address)
400 {
401 $headers = [];
402 $client = new GuzzleClient(['headers' => $headers]);
403 $apicall = 'https://maps.googleapis.com/maps/api/geocode/json?address=' . urlencode($addressString) . '&key=' . env('GOOGLE_API_KEY');
404
405 try {
406 $response = $client->request('GET', $apicall);
407 } catch (RequestException $exception) {
408 $maildata = [
409 'error' => $exception->getResponse()->getStatusCode(),
410 'messageType' => 'google'
411 ];;
412 $data = [
413 'status' => 'failed',
414 'count' => $this->count,
415 'highestRow' => $this->rows,
416 'message' => strtoupper('The import script has terminated! :X:'),
417 'statusCode' => $maildata['error'],
418 'statusMessage' => $exception->getResponse()->getReasonPhrase(),
419 'terminatedAt' => 'Google API'
420 ];
421 $target = GeoLocation::all()->first();
422 $target->notify(new SlackNotification($data));
423
424 Mail::send('template-mails/notification', $maildata, function ($message) {
425 //selecting target and subject of mail
426 $message->to($this->testmail)
427 ->from('report@mwater.nl', 'Mobile water report')
428 ->subject('Exception caught on Lebara store import script');
429
430 });
431 $this->error('----> HTTP ERROR:' . $maildata['error']);
432
433 //If Google returns HTTP error exit script!
434 exit;
435 }
436 $body = $response->getBody()->getContents();
437 $jsonBody = json_decode($body, true);
438
439 if (!isset($jsonBody['results'][0])) {
440 if (!$jsonBody['status'] === 'OK') {
441 return null;
442 }
443
444 return null;
445 }
446 $results = $jsonBody['results'][0];
447
448
449 $components = [
450 'name' => '',
451 'number' => '',
452 ];
453
454 foreach ($results['address_components'] as $component) {
455 if (in_array('route', array_values($component['types']))) {
456 $components['name'] = $component['long_name'];
457 } else if (in_array('street_number', array_values($component['types']))) {
458 $components['number'] = $component['long_name'];
459 }
460 }
461
462
463 $insertData = [
464 'hash' => $hash,
465 'longitude' => $results['geometry']['location']['lng'],
466 'latitude' => $results['geometry']['location']['lat'],
467 'street_name' => $components['name'],
468 'house_number' => $components['number'],
469 'postcode' => $address['postcode'],
470 'city' => $address['city'],
471 ];
472
473 $this->geoInsert[$hash] = $insertData;
474 return $insertData;
475 }
476
477
478 public function replaceTable()
479 {
480 $this->info('Replace store table with new_store table + data!');
481
482 Schema::rename('store', 'store_' . Carbon::now()->toDateTimeString());
483 Schema::rename('new_store', 'store');
484 }
485}