Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 49
0.00%
0 / 2
 CRAP
0.00%
0 / 1
ClearEmailErrors
0.00%
0 / 49
0.00%
0 / 2
 42
0.00%
0 / 1
 handle
0.00%
0 / 40
0.00%
0 / 1
 30
 update_commercial_numbers
0.00%
0 / 9
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     * Execute the console command.
29     */
30    public function handle(): void
31    {
32        try {
33
34            $startedAt = date('Y-m-d H:i:s');
35
36            $query = "SELECT 
37                            DISTINCT a.company_id
38                        FROM 
39                            tbl_quotations a 
40                        LEFT JOIN 
41                            tbl_sendgrid_webhook b 
42                        ON a.x_message_id = b.x_message_id 
43                        WHERE 
44                            a.x_status LIKE '%Error%'";
45
46            $companies = DB::select($query);
47
48            if (count($companies) > 0) {
49
50                for ($i = 0; $i < count($companies); $i++) {
51
52                    $companyId = $companies[$i]->company_id;
53
54                    $query = "SELECT 
55                            b.x_message_id 
56                        FROM 
57                            tbl_quotations a 
58                        LEFT JOIN 
59                            tbl_sendgrid_webhook b 
60                        ON a.x_message_id = b.x_message_id 
61                        WHERE 
62                            a.x_status LIKE '%Error%'
63                            AND DATE_ADD(b.updated_at, INTERVAL 7 DAY) < NOW()
64                            AND a.company_id = {$companyId}";
65
66                    $result = DB::select($query);
67
68                    $idsCollection = collect($result)->pluck('x_message_id');
69
70                    $idsString = $idsCollection->map(fn($id) => "'" . $id . "'")
71                                    ->implode(',');
72
73                    $totalCount = $idsCollection->count();
74
75                    if ($totalCount > 0) {
76                        $query = "DELETE FROM tbl_sendgrid_webhook
77                                    WHERE x_message_id IN ({$idsString})";
78
79                        $result = DB::select($query);
80
81                        $query = "UPDATE tbl_quotations 
82                                    SET x_message_id = NULL, x_status = NULL 
83                                    WHERE x_message_id IN ({$idsString})";
84
85                        $result = DB::select($query);
86                    }
87
88                    $query = "SELECT 
89                                COUNT(b.x_message_id) totalRemainingErrors
90                            FROM 
91                                tbl_quotations a 
92                            LEFT JOIN 
93                                tbl_sendgrid_webhook b 
94                            ON a.x_message_id = b.x_message_id 
95                            WHERE 
96                                a.x_status LIKE '%Error%' 
97                                AND DATE_ADD(b.updated_at, INTERVAL 7 DAY) > NOW()
98                                AND a.company_id = {$companyId}";
99
100                    $result = DB::select($query);
101
102                    TblOrdersUpdateLogs::create(
103                        [
104                            'company_id' => $companyId,
105                            'to_process' => 'Email Errors',
106                            'status' => 'success',
107                            'cleared_email_errors' => $totalCount,
108                            'remaining_email_errors' => $result[0]->totalRemainingErrors,
109                            'processed_by' => 'System',
110                            'started_at' => $startedAt,
111                            'ended_at' => date('Y-m-d H:i:s')
112                        ]
113                    );
114
115                    $this->update_commercial_numbers($companyId);
116
117                }
118
119            }
120
121            Cache::flush();
122
123        } catch (\Exception $e) {
124            Log::channel('clear_email_errors')->error($e->getMessage());
125        }
126    }
127    
128    function update_commercial_numbers($companyId): void{
129
130        $phpBinary = '/usr/bin/php';
131
132        $artisanPath = escapeshellarg(base_path('artisan'));
133
134        $command = sprintf(
135            '%s %s update:commercial-numbers %s > /dev/null 2>&1 &',
136            $phpBinary,
137            $artisanPath,
138            $companyId
139        );
140
141        exec($command, $output, $returnVar);
142
143    }
144}