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