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