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