Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 148
0.00%
0 / 1
 CRAP
0.00%
0 / 1
SendG3WEmailReminders
0.00%
0 / 148
0.00%
0 / 1
 600
0.00%
0 / 1
 handle
0.00%
0 / 148
0.00%
0 / 1
 600
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblCcG3WEmailReminders;
6use App\Models\TblNotificationLogs;
7use App\Services\SendgridLogger;
8use Illuminate\Console\Command;
9use Illuminate\Support\Facades\DB;
10use Illuminate\Support\Facades\Log;
11use SendGrid\Mail\Mail;
12
13class SendG3WEmailReminders extends Command
14{
15    /**
16     * The name and signature of the console command.
17     *
18     * @var string
19     */
20    protected $signature = 'send:g3w-email-reminders {sent_by?} {email?} {company_id?}';
21
22    /**
23     * The console command description.
24     *
25     * @var string
26     */
27    protected $description = 'Send g3w warning email reminders';
28
29    /**
30     * Execute the console command.
31     */
32    public function handle(): void
33    {
34
35        try {
36
37            $sentByArg = $this->argument('sent_by') ?? 'System';
38            $toEmailArg = $this->argument('email') ?? null;
39            $companyIdArg = (int) ($this->argument('company_id') ?? 0);
40
41            $where = '';
42
43            if ($companyIdArg != 0) {
44                $where = " AND a.company_id = {$companyIdArg} ";
45            }
46
47            $query = "SELECT 
48                        b.id,
49                        COUNT(a.commercial) total,
50                        a.commercial,
51                        b.email,
52                        CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', a.id, 'quote_id', a.quote_id, 'company_id', a.company_id)), ']') json_ids_no_budget_types
53                    FROM tbl_quotations a
54                    LEFT JOIN tbl_users b ON a.commercial = b.name
55                    WHERE a.sync_import = 1
56                        AND (a.budget_type_id IS NULL
57                            OR a.budget_type_id=16)
58                        AND a.commercial IS NOT NULL
59                        AND b.email IS NOT NULL     
60                        {$where}
61                    GROUP BY 1
62                    ORDER BY a.commercial ASC";
63
64            $json_ids_no_budget_types = DB::select($query);
65
66            $query = "SELECT 
67                        b.id,
68                        COUNT(a.commercial) total,
69                        a.commercial,
70                        b.email,
71                        CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', a.id, 'quote_id', a.quote_id, 'company_id', a.company_id)), ']') json_ids_incomplete_data
72                    FROM tbl_quotations a
73                    LEFT JOIN tbl_users b ON a.commercial = b.name
74                    WHERE a.sync_import = 1
75                        AND ((a.client IS NULL OR TRIM(a.client) = '')
76                            OR (a.email IS NULL
77                            OR TRIM(a.email) = ''))
78                        AND a.commercial IS NOT NULL
79                        AND b.email IS NOT NULL     
80                        {$where}
81                    GROUP BY 1
82                    ORDER BY a.commercial ASC";
83
84            $json_ids_incomplete_data = DB::select($query);
85
86            $combined = [];
87
88            foreach ($json_ids_no_budget_types as $item) {
89                $email = $item->email;
90
91                $combined[$email] = (object) [
92                    'id' => $item->id,
93                    'commercial' => $item->commercial,
94                    'email' => $email,
95                    'total' => $item->total,
96                    'json_ids_no_budget_types' => json_decode((string) $item->json_ids_no_budget_types),
97                    'json_ids_incomplete_data' => [],
98                ];
99            }
100
101            foreach ($json_ids_incomplete_data as $item) {
102                $email = $item->email;
103
104                if (! isset($combined[$email])) {
105                    $combined[$email] = (object) [
106                        'id' => $item->id,
107                        'commercial' => $item->commercial,
108                        'email' => $email,
109                        'total' => $item->total,
110                        'json_ids_no_budget_types' => [],
111                        'json_ids_incomplete_data' => json_decode((string) $item->json_ids_incomplete_data),
112                    ];
113                } else {
114                    $combined[$email]->total += $item->total;
115                    $combined[$email]->json_ids_incomplete_data = json_decode((string) $item->json_ids_incomplete_data);
116                }
117            }
118
119            $result = array_values($combined);
120
121            if (count($result) > 0) {
122
123                $today = date('d/m/Y');
124
125                for ($i = 0; $i < count($result); $i++) {
126                    $body = '';
127                    $companyCcIds = [];
128                    $jsonIdsNoBudgetTypes = $result[$i]->json_ids_no_budget_types;
129                    $jsonIdsIncompleteData = $result[$i]->json_ids_incomplete_data;
130                    $commercial = $result[$i]->commercial;
131
132                    $subject = "Revisión de warnings en G3W – Acción necesaria en presupuestos {$today} for {$commercial}";
133
134                    if ($toEmailArg != null) {
135                        $toEmail = $toEmailArg;
136                    } else {
137                        $toEmail = $result[$i]->email;
138                    }
139
140                    $body .= "<p>Hola <b>{$commercial}</b>,</p>";
141                    $body .= '<p>Te escribo porque he estado revisando tus <i>warnings</i> en G3W desde la integración con TITAN, y he detectado algunos puntos que necesitan tu atención:</p>';
142
143                    if ($result[$i]->total > 0) {
144
145                        if ($jsonIdsNoBudgetTypes) {
146                            $totalIdsNoBudgetTypes = count($jsonIdsNoBudgetTypes);
147                            $urlIdsNoBudgetTypes = config('app.frontend_url')."orders?commercial={$commercial}&g3w_warning=si&g3w_warning_fields=Tipo&company_id={$companyIdArg}";
148                            $body .= '<p><b>PRESUPUESTOS SIN TIPO DE PRESUPUESTO</b><p>';
149                            $body .= '<p>Los siguientes presupuestos en G3W no tienen el tipo de cliente asignado en TITAN, por lo tanto <b>no se contabilizan en los datos:</b></p>';
150                            $body .= "<p><b>#<a href='{$urlIdsNoBudgetTypes}'>{$totalIdsNoBudgetTypes}</a> IDs EN G3W:</b></p>";
151                            $body .= '<ul>';
152                            foreach ($jsonIdsNoBudgetTypes as $item) {
153                                $url = config('app.frontend_url')."orders/{$item->id}?company_id={$item->company_id}";
154                                $href = "<li><a href='{$url}'>{$item->quote_id}</a></li>";
155                                $body .= $href;
156
157                                if (! in_array($item->company_id, $companyCcIds)) {
158                                    array_push($companyCcIds, $item->company_id);
159                                }
160                            }
161
162                            $body .= '</ul>';
163
164                            $body .= '<p>Para solucionarlo:</p>';
165                            $body .= '<ol>';
166                            $body .= '<li>Accede a G3W</li>';
167                            $body .= '<li>Revisa cada presupuesto indicado.</li>';
168                            $body .= '<li>Añade el campo <b>"ORIGEN DE PRESUPUESTO"</b>.</li>';
169                            $body .= '</ol>';
170
171                            $body .= '<p>Si tienes dudas sobre qué origen asignar, puedes consultarlo en el siguiente enlace:</p>';
172                            $url = config('app.frontend_url').'normalize-g3w';
173                            $body .= "<p><a href='{$url}'><b>Apartado de normalización</b></a></p>";
174                        }
175
176                        if ($jsonIdsIncompleteData) {
177                            $totalIdsIncompleteData = count($jsonIdsIncompleteData);
178                            $urlIdsIncompleteData = config('app.frontend_url')."orders?commercial={$commercial}&g3w_warning=si&g3w_warning_fields=Email,Datos cliente&company_id={$companyIdArg}";
179                            $body .= '<p><b>DATOS DE CLIENTE INCOMPLETOS</b><p>';
180                            $body .= '<p>En algunos casos, durante el volcado desde G3W, los datos del cliente pueden no haberse trasladado correctamente (por ser un cliente nuevo o por falta de información en G3W).</p>';
181                            $body .= '<p>Por favor, <b>rellena los datos manualmente EN TITAN</b> para los siguientes presupuestos:</p>';
182                            $body .= "<p><b>#<a href='{$urlIdsIncompleteData}'>{$totalIdsIncompleteData}</a> de orden interno:</b></p>";
183                            $body .= '<ul>';
184                            foreach ($jsonIdsIncompleteData as $item) {
185                                $url = config('app.frontend_url')."orders/{$item->id}?company_id={$item->company_id}";
186                                $href = "<li><a href='{$url}'>{$item->quote_id}</a></li>";
187                                $body .= $href;
188
189                                if (! in_array($item->company_id, $companyCcIds)) {
190                                    array_push($companyCcIds, $item->company_id);
191                                }
192                            }
193                            $body .= '</ul>';
194                        }
195
196                        $body .= '<p>Si tienes cualquier duda sobre esta operativa o necesitas ayuda con alguno de los <i>warnings</i>, no dudes en escribirme.</p>';
197                        $body .= '<p>Gracias por tu atención y colaboración.</p>';
198                        $body .= '<p>Un saludo,</p>';
199
200                        $imgpath = file_get_contents(public_path('fireservicetitan.png'));
201                        $body .= '<br><p>Fire Service Titan</p>';
202                        $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
203
204                        $html = '<!DOCTYPE html>';
205                        $html .= '<html>';
206                        $html .= '<head>';
207                        $html .= '<meta charset="UTF-8">';
208                        $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
209                        $html .= '</head>';
210                        $html .= '<body>';
211                        $html .= $body;
212                        $html .= '</body>';
213                        $html .= '</html>';
214
215                        if ($toEmail != null) {
216                            $email = new Mail;
217
218                            if ($toEmailArg == null) {
219                                $companyCcIds = array_values(array_unique($companyCcIds));
220                                $ccBcc = TblCcG3WEmailReminders::whereIn('company_id', $companyCcIds)->get();
221
222                                $inCc = [];
223
224                                if (count($ccBcc) > 0) {
225                                    foreach ($ccBcc as $data) {
226                                        if ($data->email != $toEmail && ! in_array($data->email, $inCc)) {
227                                            $email->addCc($data->email);
228                                            array_push($inCc, $data->email);
229                                        }
230                                    }
231                                }
232                            }
233
234                            $email->setFrom('fire@fire.es', 'Fire Service Titan');
235                            $email->setSubject($subject);
236                            $email->addTo($toEmail);
237                            $email->addContent('text/html', $html);
238
239                            $email->addAttachment(
240                                $imgpath,
241                                'image/png',
242                                'fireservicetitan.png',
243                                'inline',
244                                'fireservicetitan'
245                            );
246
247                            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
248
249                            try {
250                                $response = $sendgrid->send($email);
251                                SendgridLogger::log($email, $response);
252                            } catch (\Throwable $sendException) {
253                                SendgridLogger::logException($email, $sendException);
254                                throw $sendException;
255                            }
256
257                            if ($response->statusCode() != 202) {
258                                Log::channel('cron_send_g3w_email_reminders')->error('ID: '.$toEmail.' - '.$response->body());
259                            } else {
260                                TblNotificationLogs::create(
261                                    [
262                                        'company_id' => $companyIdArg,
263                                        'commercial' => $commercial,
264                                        'total_g3w_warning' => $result[$i]->total,
265                                        'created_by' => $sentByArg,
266                                    ]
267                                );
268                            }
269                        }
270                    }
271                }
272            }
273
274        } catch (\Exception $e) {
275            Log::channel('cron_send_g3w_email_reminders')->error($e->getMessage());
276        }
277
278    }
279}