Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00% covered (danger)
0.00%
0 / 53
0.00% covered (danger)
0.00%
0 / 3
 CRAP
0.00% covered (danger)
0.00%
0 / 1
ClearEmailErrors
0.00% covered (danger)
0.00%
0 / 53
0.00% covered (danger)
0.00%
0 / 3
 56
0.00% covered (danger)
0.00%
0 / 1
 __construct
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 1
 2
 handle
0.00% covered (danger)
0.00%
0 / 43
0.00% covered (danger)
0.00%
0 / 1
 30
 update_commercial_numbers
0.00% covered (danger)
0.00%
0 / 9
0.00% covered (danger)
0.00%
0 / 1
 2
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblOrdersUpdateLogs;
6use Illuminate\Console\Command;
7use Illuminate\Support\Facades\Cache;
8use Illuminate\Support\Facades\DB;
9use Illuminate\Support\Facades\Log;
10
11class ClearEmailErrors extends Command
12{
13    /**
14     * The name and signature of the console command.
15     *
16     * @var string
17     */
18    protected $signature = 'clear:email-errors';
19
20    /**
21     * The console command description.
22     *
23     * @var string
24     */
25    protected $description = 'Clear email errors';
26
27    /**
28     * Create a new command instance.
29     *
30     * @return void
31     */
32    public function __construct()
33    {
34        parent::__construct();
35    }
36
37    /**
38     * Execute the console command.
39     *
40     * @return int
41     */
42    public function handle()
43    {
44        try {
45
46            $startedAt = date('Y-m-d H:i:s');
47
48            $query = "SELECT 
49                            DISTINCT a.company_id
50                        FROM 
51                            tbl_quotations a 
52                        LEFT JOIN 
53                            tbl_sendgrid_webhook b 
54                        ON a.x_message_id = b.x_message_id 
55                        WHERE 
56                            a.x_status LIKE '%Error%'";
57
58            $companies = DB::select($query);
59
60            if (count($companies) > 0) {
61
62                for ($i = 0; $i < count($companies); $i++) {
63
64                    $companyId = $companies[$i]->company_id;
65
66                    $query = "SELECT 
67                            b.x_message_id 
68                        FROM 
69                            tbl_quotations a 
70                        LEFT JOIN 
71                            tbl_sendgrid_webhook b 
72                        ON a.x_message_id = b.x_message_id 
73                        WHERE 
74                            a.x_status LIKE '%Error%'
75                            AND DATE_ADD(b.updated_at, INTERVAL 7 DAY) < NOW()
76                            AND a.company_id = {$companyId}";
77
78                    $result = DB::select($query);
79
80                    $idsCollection = collect($result)->pluck('x_message_id');
81
82                    $idsString = $idsCollection->map(function ($id) {
83                        return "'".$id."'";
84                    })
85                        ->implode(',');
86
87                    $totalCount = $idsCollection->count();
88
89                    if ($totalCount > 0) {
90                        $query = "DELETE FROM tbl_sendgrid_webhook
91                                    WHERE x_message_id IN ({$idsString})";
92
93                        $result = DB::select($query);
94
95                        $query = "UPDATE tbl_quotations 
96                                    SET x_message_id = NULL, x_status = NULL 
97                                    WHERE x_message_id IN ({$idsString})";
98
99                        $result = DB::select($query);
100                    }
101
102                    $query = "SELECT 
103                                COUNT(b.x_message_id) totalRemainingErrors
104                            FROM 
105                                tbl_quotations a 
106                            LEFT JOIN 
107                                tbl_sendgrid_webhook b 
108                            ON a.x_message_id = b.x_message_id 
109                            WHERE 
110                                a.x_status LIKE '%Error%' 
111                                AND DATE_ADD(b.updated_at, INTERVAL 7 DAY) > NOW()
112                                AND a.company_id = {$companyId}";
113
114                    $result = DB::select($query);
115
116                    TblOrdersUpdateLogs::create(
117                        [
118                            'company_id' => $companyId,
119                            'to_process' => 'Email Errors',
120                            'status' => 'success',
121                            'cleared_email_errors' => $totalCount,
122                            'remaining_email_errors' => $result[0]->totalRemainingErrors,
123                            'processed_by' => 'System',
124                            'started_at' => $startedAt,
125                            'ended_at' => date('Y-m-d H:i:s'),
126                        ]
127                    );
128
129                    $this->update_commercial_numbers($companyId);
130
131                }
132
133            }
134
135            Cache::flush();
136
137        } catch (\Exception $e) {
138            Log::channel('clear_email_errors')->error($e->getMessage());
139        }
140
141        return 0;
142    }
143
144    public function update_commercial_numbers($companyId)
145    {
146
147        $phpBinary = '/usr/bin/php';
148
149        $artisanPath = escapeshellarg(base_path('artisan'));
150
151        $command = sprintf(
152            '%s %s update:commercial-numbers %s > /dev/null 2>&1 &',
153            $phpBinary,
154            $artisanPath,
155            $companyId
156        );
157
158        exec($command, $output, $returnVar);
159
160    }
161}