dolibarr 25.0.0-alpha
advtargetemailing.class.php
Go to the documentation of this file.
1<?php
2/* Advance Targeting Emailing for mass emailing module
3 * Copyright (C) 2013 Florian Henry <florian.henry@open-concept.pro>
4 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
5 * Copyright (C) 2024-2026 MDW <mdeweerd@users.noreply.github.com>
6 *
7 * This program is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program. If not, see <https://www.gnu.org/licenses/>.
19 */
20
31class AdvanceTargetingMailing extends CommonObject
32{
36 public $db;
37
41 public $element = 'advtargetemailing';
42
46 public $table_element = 'mailing_advtarget';
47
51 public $id;
52
56 public $name;
57
61 public $fk_element;
62
66 public $type_element;
67
71 public $filtervalue;
72
76 public $fk_user_author;
77
81 public $datec = '';
82
86 public $fk_user_mod;
87
91 public $select_target_type = array();
92
96 public $type_statuscommprospect = array();
97
101 public $thirdparty_lines;
102
106 public $contact_lines;
107
108
114 public function __construct($db)
115 {
116 global $langs;
117 $langs->load('customers');
118
119 $this->db = $db;
120
121 $this->select_target_type = array(
122 '2' => $langs->trans('Contacts'),
123 '1' => $langs->trans('Contacts').'+'.$langs->trans('ThirdParty'),
124 '3' => $langs->trans('ThirdParty'),
125 '4' => $langs->trans('ContactsWithThirdpartyFilter')
126 );
127
128 require_once DOL_DOCUMENT_ROOT.'/societe/class/client.class.php';
129 $customerStatic = new Client($this->db);
130 $customerStatic->loadCacheOfProspStatus();
131 if (!empty($customerStatic->cacheprospectstatus)) {
132 foreach ($customerStatic->cacheprospectstatus as $dataProspectSt) {
133 $this->type_statuscommprospect[$dataProspectSt['id']] = $dataProspectSt['label'];
134 }
135 } else {
136 $this->type_statuscommprospect = array(
137 -1 => $langs->trans("StatusProspect-1"),
138 0 => $langs->trans("StatusProspect0"),
139 1 => $langs->trans("StatusProspect1"),
140 2 => $langs->trans("StatusProspect2"),
141 3 => $langs->trans("StatusProspect3")
142 );
143 }
144 }
145
153 public function create($user, $notrigger = 0)
154 {
155 global $conf;
156 $error = 0;
157
158 // Clean parameters
159 if (isset($this->fk_element)) {
160 $this->fk_element = (int) $this->fk_element;
161 }
162 if (isset($this->type_element)) {
163 $this->type_element = trim($this->type_element);
164 }
165
166 if (isset($this->name)) {
167 $this->name = trim($this->name);
168 }
169 if (isset($this->filtervalue)) {
170 $this->filtervalue = trim($this->filtervalue);
171 }
172
173 // Check parameters
174 // Put here code to add control on parameters values
175
176 // Insert request
177 $sql = "INSERT INTO ".MAIN_DB_PREFIX."mailing_advtarget(";
178 $sql .= "name,";
179 $sql .= "entity,";
180 $sql .= "fk_element,";
181 $sql .= "type_element,";
182 $sql .= "filtervalue,";
183 $sql .= "fk_user_author,";
184 $sql .= "datec,";
185 $sql .= "fk_user_mod";
186 $sql .= ") VALUES (";
187 $sql .= " ".(!isset($this->name) ? 'NULL' : "'".$this->db->escape($this->name)."'").",";
188 $sql .= " ".((int) $conf->entity).",";
189 $sql .= " ".(!isset($this->fk_element) ? 'NULL' : "'".$this->db->escape((string) $this->fk_element)."'").",";
190 $sql .= " ".(!isset($this->type_element) ? 'NULL' : "'".$this->db->escape($this->type_element)."'").",";
191 $sql .= " ".(!isset($this->filtervalue) ? 'NULL' : "'".$this->db->escape($this->filtervalue)."'").",";
192 $sql .= " ".((int) $user->id).",";
193 $sql .= " '".$this->db->idate(dol_now())."',";
194 $sql .= " ".((int) $user->id);
195 $sql .= ")";
196
197 $this->db->begin();
198
199 dol_syslog(get_class($this)."::create", LOG_DEBUG);
200 $resql = $this->db->query($sql);
201 if (!$resql) {
202 $error++;
203 $this->errors[] = "Error ".$this->db->lasterror();
204 }
205
206 if (!$error) {
207 $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX."mailing_advtarget");
208 }
209
210 // Commit or rollback
211 if ($error) {
212 foreach ($this->errors as $errmsg) {
213 dol_syslog(get_class($this)."::create ".$errmsg, LOG_ERR);
214 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
215 }
216 $this->db->rollback();
217 return -1 * $error;
218 } else {
219 $this->db->commit();
220 return $this->id;
221 }
222 }
223
230 public function fetch($id)
231 {
232 global $langs;
233 $sql = "SELECT";
234 $sql .= " t.rowid,";
235
236 $sql .= " t.name,";
237 $sql .= " t.entity,";
238 $sql .= " t.fk_element,";
239 $sql .= " t.type_element,";
240 $sql .= " t.filtervalue,";
241 $sql .= " t.fk_user_author,";
242 $sql .= " t.datec,";
243 $sql .= " t.fk_user_mod,";
244 $sql .= " t.tms";
245
246 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
247 $sql .= " WHERE t.rowid = ".((int) $id);
248
249 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
250 $resql = $this->db->query($sql);
251 if ($resql) {
252 if ($this->db->num_rows($resql)) {
253 $obj = $this->db->fetch_object($resql);
254
255 $this->id = $obj->rowid;
256
257 $this->name = $obj->name;
258 $this->entity = $obj->entity;
259 $this->fk_element = $obj->fk_element;
260 $this->type_element = $obj->type_element;
261 $this->filtervalue = $obj->filtervalue;
262 $this->fk_user_author = $obj->fk_user_author;
263 $this->datec = $this->db->jdate($obj->datec);
264 $this->fk_user_mod = $obj->fk_user_mod;
265 $this->tms = $this->db->jdate($obj->tms);
266 }
267 $this->db->free($resql);
268
269 return 1;
270 } else {
271 $this->error = "Error ".$this->db->lasterror();
272 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
273 return -1;
274 }
275 }
276
277 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
284 public function fetch_by_mailing($id = 0)
285 {
286 // phpcs:enable
287 global $langs;
288 $sql = "SELECT";
289 $sql .= " t.rowid,";
290
291 $sql .= " t.name,";
292 $sql .= " t.entity,";
293 $sql .= " t.fk_element,";
294 $sql .= " t.type_element,";
295 $sql .= " t.filtervalue,";
296 $sql .= " t.fk_user_author,";
297 $sql .= " t.datec,";
298 $sql .= " t.fk_user_mod,";
299 $sql .= " t.tms";
300
301 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
302 if (!empty($id)) {
303 $sql .= " WHERE t.fk_element = ".((int) $id)." AND type_element = 'mailing'";
304 } else {
305 $sql .= " WHERE t.fk_element = ".((int) $this->fk_element)." AND type_element = 'mailing'";
306 }
307
308 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
309 $resql = $this->db->query($sql);
310 if ($resql) {
311 if ($this->db->num_rows($resql)) {
312 $obj = $this->db->fetch_object($resql);
313
314 $this->id = $obj->rowid;
315
316 $this->name = $obj->name;
317 $this->entity = $obj->entity;
318 $this->fk_element = $obj->fk_element;
319 $this->type_element = $obj->type_element;
320 $this->filtervalue = $obj->filtervalue;
321 $this->fk_user_author = $obj->fk_user_author;
322 $this->datec = $this->db->jdate($obj->datec);
323 $this->fk_user_mod = $obj->fk_user_mod;
324 $this->tms = $this->db->jdate($obj->tms);
325 }
326 $this->db->free($resql);
327
328 return 1;
329 } else {
330 $this->error = "Error ".$this->db->lasterror();
331 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
332 return -1;
333 }
334 }
335
336
337
338
339 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
347 public function fetch_by_element($id = 0, $type_element = 'mailing')
348 {
349 // phpcs:enable
350 global $langs;
351 $sql = "SELECT";
352 $sql .= " t.rowid,";
353
354 $sql .= " t.name,";
355 $sql .= " t.entity,";
356 $sql .= " t.fk_element,";
357 $sql .= " t.type_element,";
358 $sql .= " t.filtervalue,";
359 $sql .= " t.fk_user_author,";
360 $sql .= " t.datec,";
361 $sql .= " t.fk_user_mod,";
362 $sql .= " t.tms";
363
364 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
365 if (!empty($id)) {
366 $sql .= " WHERE t.fk_element = ".((int) $id)." AND type_element = '".$this->db->escape($type_element)."'";
367 } else {
368 $sql .= " WHERE t.fk_element = ".((int) $this->fk_element)." AND type_element = '".$this->db->escape($type_element)."'";
369 }
370
371 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
372 $resql = $this->db->query($sql);
373 if ($resql) {
374 if ($this->db->num_rows($resql)) {
375 $obj = $this->db->fetch_object($resql);
376
377 $this->id = $obj->rowid;
378
379 $this->name = $obj->name;
380 $this->entity = $obj->entity;
381 $this->fk_element = $obj->fk_element;
382 $this->type_element = $obj->type_element;
383 $this->filtervalue = $obj->filtervalue;
384 $this->fk_user_author = $obj->fk_user_author;
385 $this->datec = $this->db->jdate($obj->datec);
386 $this->fk_user_mod = $obj->fk_user_mod;
387 $this->tms = $this->db->jdate($obj->tms);
388 }
389 $this->db->free($resql);
390
391 return 1;
392 } else {
393 $this->error = "Error ".$this->db->lasterror();
394 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
395 return -1;
396 }
397 }
398
406 public function update($user, $notrigger = 0)
407 {
408 global $conf, $langs;
409 $error = 0;
410
411 // Clean parameters
412 if (isset($this->fk_element)) {
413 $this->fk_element = (int) $this->fk_element;
414 }
415 if (isset($this->type_element)) {
416 $this->type_element = trim($this->type_element);
417 }
418 if (isset($this->name)) {
419 $this->name = trim($this->name);
420 }
421 if (isset($this->filtervalue)) {
422 $this->filtervalue = trim($this->filtervalue);
423 }
424
425 // Check parameters
426 // Put here code to add a control on parameters values
427
428 // Update request
429 $sql = "UPDATE ".MAIN_DB_PREFIX."mailing_advtarget SET";
430
431 $sql .= " name=".(isset($this->name) ? "'".$this->db->escape($this->name)."'" : "''").",";
432 $sql .= " entity=".((int) $conf->entity).",";
433 $sql .= " fk_element=".(isset($this->fk_element) ? ((int) $this->fk_element) : "null").",";
434 $sql .= " type_element=".(isset($this->type_element) ? "'".$this->db->escape($this->type_element)."'" : "null").",";
435 $sql .= " filtervalue=".(isset($this->filtervalue) ? "'".$this->db->escape($this->filtervalue)."'" : "null").",";
436 $sql .= " fk_user_mod=".((int) $user->id);
437
438 $sql .= " WHERE rowid=".((int) $this->id);
439
440 $this->db->begin();
441 dol_syslog(get_class($this)."::update", LOG_DEBUG);
442 $resql = $this->db->query($sql);
443 if (!$resql) {
444 $error++;
445 $this->errors[] = "Error ".$this->db->lasterror();
446 }
447
448 // Commit or rollback
449 if ($error) {
450 foreach ($this->errors as $errmsg) {
451 dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
452 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
453 }
454 $this->db->rollback();
455 return -1 * $error;
456 } else {
457 $this->db->commit();
458 return 1;
459 }
460 }
461
469 public function delete($user, $notrigger = 0)
470 {
471 global $conf, $langs;
472 $error = 0;
473
474 $this->db->begin();
475
476 if (!$error) {
477 $sql = "DELETE FROM ".MAIN_DB_PREFIX."mailing_advtarget";
478 $sql .= " WHERE rowid=".((int) $this->id);
479
480 dol_syslog(get_class($this)."::delete sql=".$sql);
481 $resql = $this->db->query($sql);
482 if (!$resql) {
483 $error++;
484 $this->errors[] = "Error ".$this->db->lasterror();
485 }
486 }
487
488 // Commit or rollback
489 if ($error) {
490 foreach ($this->errors as $errmsg) {
491 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
492 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
493 }
494 $this->db->rollback();
495 return -1 * $error;
496 } else {
497 $this->db->commit();
498 return 1;
499 }
500 }
501
502
510 public function savequery($user, $arrayquery)
511 {
512 global $langs, $conf;
513
514 if (!empty($arrayquery)) {
515 $result = $this->fetch_by_mailing($this->fk_element);
516 $this->filtervalue = json_encode($arrayquery);
517 if ($result < 0) {
518 return -1;
519 }
520 if (!empty($this->id)) {
521 $this->update($user);
522 } else {
523 $this->create($user);
524 }
525 }
526 return -1;
527 }
528
529
530
531 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
538 public function query_thirdparty($arrayquery)
539 {
540 // phpcs:enable
541 global $langs, $conf, $extrafields;
542
543 $sql = "SELECT";
544 $sql .= " t.rowid";
545 $sql .= " FROM ".MAIN_DB_PREFIX."societe as t";
546 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe_extrafields as te ON te.fk_object=t.rowid ";
547
548 $sqlwhere = array();
549
550 $sqlwhere[] = 't.entity IN ('.getEntity('societe').')';
551
552 if (count($arrayquery) > 0) {
553 if (array_key_exists('cust_saleman', $arrayquery)) {
554 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc = t.rowid";
555 }
556 if (array_key_exists('cust_categ', $arrayquery)) {
557 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc = t.rowid";
558 }
559
560 if (!empty($arrayquery['cust_name'])) {
561 $sqlwhere[] = $this->transformToSQL('t.nom', $arrayquery['cust_name']);
562 }
563 if (!empty($arrayquery['cust_code'])) {
564 $sqlwhere[] = $this->transformToSQL('t.code_client', $arrayquery['cust_code']);
565 }
566 if (!empty($arrayquery['cust_adress'])) {
567 $sqlwhere[] = $this->transformToSQL('t.address', $arrayquery['cust_adress']);
568 }
569 if (!empty($arrayquery['cust_zip'])) {
570 $sqlwhere[] = $this->transformToSQL('t.zip', $arrayquery['cust_zip']);
571 }
572 if (!empty($arrayquery['cust_city'])) {
573 $sqlwhere[] = $this->transformToSQL('t.town', $arrayquery['cust_city']);
574 }
575 if (!empty($arrayquery['cust_mothercompany'])) {
576 $sqlStr = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
577 $sqlwhere[] = " (t.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$sqlStr.")))";
578 }
579 if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
580 $sqlwhere[] = " (t.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
581 }
582 if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
583 $sqlwhere[] = " (t.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
584 }
585 if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']) > 0) {
586 $sqlwhere[] = " (t.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
587 }
588 if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
589 $sqlwhere[] = " (t.fk_prospectlevel IN (".$this->db->sanitize("'".implode("','", $arrayquery['cust_prospect_status'])."'", 1)."))";
590 }
591 if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
592 $sqlwhere[] = " (t.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
593 }
594 if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
595 $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
596 }
597 if (!empty($arrayquery['cust_state']) && count($arrayquery['cust_state']) > 0) {
598 $sqlwhere[] = " (t.fk_departement IN (".$this->db->sanitize(implode(',', $arrayquery['cust_state']))."))";
599 }
600 if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country']) > 0) {
601 $sqlwhere[] = " (t.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
602 }
603 if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
604 $sqlwhere[] = " (t.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
605 }
606 if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
607 $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
608 }
609 if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
610 $sqlwhere[] = " (t.default_lang IN (".$this->db->sanitize("'".implode("','", $arrayquery['cust_language'])."'", 1)."))";
611 }
612
613 //Standard Extrafield feature
614 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
615 $socstatic = new Societe($this->db);
616 $elementtype = $socstatic->table_element;
617
618 $extrafields->fetch_name_optionals_label($elementtype);
619
620 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
621 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
622 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
623 if (!empty($arrayquery['options_'.$key])) {
624 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
625 }
626 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
627 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
628 if (!empty($arrayquery['options_'.$key.'_max'])) {
629 $sqlwhere[] = " (te.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND te.".$key." <= ".((float) $arrayquery["options_".$key.'_min']).")";
630 }
631 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
632 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
633 if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
634 $sqlwhere[] = " (te.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND te.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
635 }
636 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
637 if ($arrayquery['options_'.$key] != '') {
638 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key]).")";
639 }
640 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'link') {
641 if ($arrayquery['options_'.$key] > 0) {
642 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key]).")";
643 }
644 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'chkbxlst'
645 && is_array($arrayquery['options_'.$key])) {
646 if (count($arrayquery['options_'.$key])) {
647 $i2 = 0;
648 $field = "te.".$key;
649 $sqlwhereselllist = "";
650 foreach ($arrayquery['options_'.$key] as $data) {
651 $data = trim($data);
652 if ($data) {
653 $sqlwhereselllist .= ($i2 > 0 ? " OR (" : "(").$field." LIKE '".$this->db->escape($data).",%'";
654 $sqlwhereselllist .= " OR ".$field." = '".$this->db->escape($data)."'";
655 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data)."'";
656 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data).",%'";
657 $sqlwhereselllist .= ")";
658 $i2++; // a criteria for 1 more field was added to string (we can add several criteria for the same field as it is a multiselect search criteria)
659 }
660 }
661 if (!empty($sqlwhereselllist)) {
662 $sqlwhere[] = "( ".$sqlwhereselllist." )";
663 }
664 }
665 } else {
666 if (is_array($arrayquery['options_'.$key])) {
667 $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
668 } elseif (!empty($arrayquery['options_'.$key])) {
669 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
670 }
671 }
672 }
673 }
674
675 if (count($sqlwhere) > 0) {
676 $sql .= " WHERE ".implode(" AND ", $sqlwhere);
677 }
678 }
679
680
681 dol_syslog(get_class($this)."::query_thirdparty", LOG_DEBUG);
682 $resql = $this->db->query($sql);
683 if ($resql) {
684 $this->thirdparty_lines = array();
685 $num = $this->db->num_rows($resql);
686 $i = 0;
687
688 if ($num) {
689 while ($i < $num) {
690 $obj = $this->db->fetch_object($resql);
691 $this->thirdparty_lines[$i] = $obj->rowid;
692
693 $i++;
694 }
695 }
696 $this->db->free($resql);
697
698 return $num;
699 } else {
700 $this->error = "Error ".$this->db->lasterror();
701 dol_syslog(get_class($this)."::query_thirdparty ".$this->error, LOG_ERR);
702 return -1;
703 }
704 }
705
706 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
714 public function query_contact($arrayquery, $withThirdpartyFilter = 0)
715 {
716 // phpcs:enable
717 $sql = "SELECT";
718 $sql .= " t.rowid";
719 $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as t";
720 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."socpeople_extrafields as te ON te.fk_object=t.rowid ";
721
722 if (!empty($withThirdpartyFilter)) {
723 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe as ts ON ts.rowid=t.fk_soc";
724 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe_extrafields as tse ON tse.fk_object=ts.rowid ";
725 }
726
727 $sqlwhere = array();
728
729 $sqlwhere[] = 't.entity IN ('.getEntity('contact').')';
730
731 if (count($arrayquery) > 0) {
732 if (array_key_exists('contact_categ', $arrayquery)) {
733 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_contact as contactcateg ON contactcateg.fk_socpeople=t.rowid ";
734 }
735
736 if (!empty($arrayquery['contact_lastname'])) {
737 $sqlwhere[] = $this->transformToSQL('t.lastname', $arrayquery['contact_lastname']);
738 }
739 if (!empty($arrayquery['contact_firstname'])) {
740 $sqlwhere[] = $this->transformToSQL('t.firstname', $arrayquery['contact_firstname']);
741 }
742 if (!empty($arrayquery['contact_country']) && count($arrayquery['contact_country'])) {
743 $sqlwhere[] = " (t.fk_pays IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_country'])))."))";
744 }
745 if (!empty($arrayquery['contact_status']) && count($arrayquery['contact_status']) > 0) {
746 $sqlwhere[] = " (t.statut IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_status'])))."))";
747 }
748 if (!empty($arrayquery['contact_civility']) && count($arrayquery['contact_civility']) > 0) {
749 $sqlwhere[] = " (t.civility IN (".$this->db->sanitize("'".implode("','", $arrayquery['contact_civility'])."'", 1)."))";
750 }
751 if ($arrayquery['contact_no_email'] != '') {
752 $sqlTmpWhere = '';
753 if (!empty($arrayquery['contact_no_email'])) {
754 $sqlTmpWhere .= "(t.email IN (SELECT email FROM ".MAIN_DB_PREFIX."mailing_unsubscribe WHERE t.entity IN (".getEntity('mailing').") AND email = '".$this->db->escape($arrayquery['contact_no_email'])."'))";
755 } else {
756 $sqlTmpWhere .= "(t.email NOT IN (SELECT email FROM ".MAIN_DB_PREFIX."mailing_unsubscribe WHERE t.entity IN (".getEntity('mailing').") AND email = '".$this->db->escape((string) $arrayquery['contact_no_email'])."'))";
757 }
758 $sqlwhere[] = $sqlTmpWhere;
759 }
760 if ($arrayquery['contact_update_st_dt'] != '') {
761 $sqlwhere[] = " (t.tms >= '".$this->db->idate($arrayquery['contact_update_st_dt'])."' AND t.tms <= '".$this->db->idate($arrayquery['contact_update_end_dt'])."')";
762 }
763 if ($arrayquery['contact_create_st_dt'] != '') {
764 $sqlwhere[] = " (t.datec >= '".$this->db->idate($arrayquery['contact_create_st_dt'])."' AND t.datec <= '".$this->db->idate($arrayquery['contact_create_end_dt'])."')";
765 }
766 if (!empty($arrayquery['contact_categ']) && count($arrayquery['contact_categ']) > 0) {
767 $sqlwhere[] = " (contactcateg.fk_categorie IN (".$this->db->sanitize(implode(",", $arrayquery['contact_categ']), 0, 0, 0, 0)."))";
768 }
769
770 //Standard Extrafield feature
771 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
772 $contactstatic = new Contact($this->db);
773 $elementtype = $contactstatic->table_element;
774
775 // fetch optionals attributes and labels
776 dol_include_once('/core/class/extrafields.class.php');
777 $extrafields = new ExtraFields($this->db);
778 $extrafields->fetch_name_optionals_label($elementtype);
779
780 $extrafields->fetch_name_optionals_label($elementtype);
781
782 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
783 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
784 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
785 if (!empty($arrayquery['options_'.$key.'_cnct'])) {
786 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
787 }
788 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
789 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
790 if (!empty($arrayquery['options_'.$key.'_max_cnct'])) {
791 $sqlwhere[] = " (te.".$key." >= ".((float) $arrayquery["options_".$key."_max_cnct"])." AND te.".$key." <= ".((float) $arrayquery["options_".$key."_min_cnct"]).")";
792 }
793 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
794 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
795 if (!empty($arrayquery['options_'.$key.'_end_dt_cnct'])) {
796 $sqlwhere[] = " (te.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt_cnct"])."' AND te.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt_cnct"])."')";
797 }
798 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
799 if ($arrayquery['options_'.$key.'_cnct'] != '') {
800 if ($arrayquery['options_'.$key.'_cnct'] == 0) {
801 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"])." OR ((te.".$key." IS NULL) AND (te.fk_object IS NOT NULL)))";
802 } else {
803 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"]).")";
804 }
805 }
806 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'link') {
807 if ($arrayquery['options_'.$key."_cnct"] > 0) {
808 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"]).")";
809 }
810 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'chkbxlst'
811 && is_array($arrayquery['options_'.$key.'_cnct'])) {
812 if (count($arrayquery['options_'.$key.'_cnct'])) {
813 $i2 = 0;
814 $field = "te.".$key;
815 $sqlwhereselllist = "";
816 foreach ($arrayquery['options_'.$key.'_cnct'] as $data) {
817 $data = trim($data);
818 if ($data) {
819 $sqlwhereselllist .= ($i2 > 0 ? " OR (" : "(").$field." LIKE '".$this->db->escape($data).",%'";
820 $sqlwhereselllist .= " OR ".$field." = '".$this->db->escape($data)."'";
821 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data)."'";
822 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data).",%'";
823 $sqlwhereselllist .= ")";
824 $i2++; // a criteria for 1 more field was added to string (we can add several criteria for the same field as it is a multiselect search criteria)
825 }
826 }
827 if (!empty($sqlwhereselllist)) {
828 $sqlwhere[] = "( ".$sqlwhereselllist." )";
829 }
830 }
831 } else {
832 if (is_array($arrayquery['options_'.$key.'_cnct'])) {
833 $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key."_cnct"])."'", 1)."))";
834 } elseif (!empty($arrayquery['options_'.$key.'_cnct'])) {
835 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
836 }
837 }
838 }
839
840 if (!empty($withThirdpartyFilter)) {
841 if (array_key_exists('cust_saleman', $arrayquery)) {
842 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc = ts.rowid";
843 }
844 if (array_key_exists('cust_categ', $arrayquery)) {
845 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc = ts.rowid";
846 }
847
848 if (!empty($arrayquery['cust_name'])) {
849 $sqlwhere[] = $this->transformToSQL('ts.nom', $arrayquery['cust_name']);
850 }
851 if (!empty($arrayquery['cust_code'])) {
852 $sqlwhere[] = $this->transformToSQL('ts.code_client', $arrayquery['cust_code']);
853 }
854 if (!empty($arrayquery['cust_adress'])) {
855 $sqlwhere[] = $this->transformToSQL('ts.address', $arrayquery['cust_adress']);
856 }
857 if (!empty($arrayquery['cust_zip'])) {
858 $sqlwhere[] = $this->transformToSQL('ts.zip', $arrayquery['cust_zip']);
859 }
860 if (!empty($arrayquery['cust_city'])) {
861 $sqlwhere[] = $this->transformToSQL('ts.town', $arrayquery['cust_city']);
862 }
863 if (!empty($arrayquery['cust_mothercompany'])) {
864 $sql_str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
865 $sqlwhere[] = " (ts.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$sql_str.")))";
866 }
867 if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
868 $sqlwhere[] = " (ts.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
869 }
870 if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
871 $sqlwhere[] = " (ts.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
872 }
873 if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']) > 0) {
874 $sqlwhere[] = " (ts.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
875 }
876 if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
877 $sqlwhere[] = " (ts.fk_prospectlevel IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_prospect_status']))."'))";
878 }
879 if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
880 $sqlwhere[] = " (ts.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
881 }
882 if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
883 $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
884 }
885 //if (!empty($arrayquery['cust_state'])) {
886 // $sqlwhere[] = $this->transformToSQL('tsd.nom', $arrayquery['cust_state']);
887 //}
888 if (!empty($arrayquery['cust_state']) && count($arrayquery['cust_state']) > 0) {
889 $sqlwhere[] = " (t.fk_departement IN (".$this->db->sanitize(implode(',', $arrayquery['cust_state']))."))";
890 }
891 if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country']) > 0) {
892 $sqlwhere[] = " (ts.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
893 }
894 if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
895 $sqlwhere[] = " (ts.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
896 }
897 if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
898 $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
899 }
900 if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
901 $sqlwhere[] = " (ts.default_lang IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_language']))."'))";
902 }
903
904 //Standard Extrafield feature
905 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
906 $socstatic = new Societe($this->db);
907 $elementtype = $socstatic->table_element;
908
909 // fetch optionals attributes and labels
910 dol_include_once('/core/class/extrafields.class.php');
911 $extrafields = new ExtraFields($this->db);
912
913 $extrafields->fetch_name_optionals_label($elementtype);
914
915 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
916 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
917 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
918 if (!empty($arrayquery['options_'.$key])) {
919 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery['options_'.$key])."')";
920 }
921 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
922 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
923 if (!empty($arrayquery['options_'.$key.'_max'])) {
924 $sqlwhere[] = " (tse.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND tse.".$key." <= ".((float) $arrayquery["options_".$key."_min"]).")";
925 }
926 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
927 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
928 if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
929 $sqlwhere[] = " (tse.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND tse.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
930 }
931 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
932 if ($arrayquery['options_'.$key] != '') {
933 $sqlwhere[] = " (tse.".$key." = ".((int) $arrayquery["options_".$key]).")";
934 }
935 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'link') {
936 if ($arrayquery['options_'.$key] > 0) {
937 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key]).")";
938 }
939 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'chkbxlst'
940 && is_array($arrayquery['options_'.$key])) {
941 if (count($arrayquery['options_'.$key])) {
942 $i2 = 0;
943 $field = "tse.".$key;
944 $sqlwhereselllist = "";
945 foreach ($arrayquery['options_'.$key] as $data) {
946 $data = trim($data);
947 if ($data) {
948 $sqlwhereselllist .= ($i2 > 0 ? " OR (" : "(").$field." LIKE '".$this->db->escape($data).",%'";
949 $sqlwhereselllist .= " OR ".$field." = '".$this->db->escape($data)."'";
950 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data)."'";
951 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data).",%'";
952 $sqlwhereselllist .= ")";
953 $i2++; // a criteria for 1 more field was added to string (we can add several criteria for the same field as it is a multiselect search criteria)
954 }
955 }
956 if (!empty($sqlwhereselllist)) {
957 $sqlwhere[] = "( ".$sqlwhereselllist." )";
958 }
959 }
960 } else {
961 if (is_array($arrayquery['options_'.$key])) {
962 $sqlwhere[] = " (tse.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
963 } elseif (!empty($arrayquery['options_'.$key])) {
964 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
965 }
966 }
967 }
968 }
969 }
970 }
971 if (count($sqlwhere) > 0) {
972 $sql .= " WHERE ".implode(" AND ", $sqlwhere);
973 }
974 }
975
976 dol_syslog(get_class($this)."::query_contact", LOG_DEBUG);
977 $resql = $this->db->query($sql);
978 if ($resql) {
979 $this->contact_lines = array();
980 $num = $this->db->num_rows($resql);
981 $i = 0;
982
983 if ($num) {
984 while ($i < $num) {
985 $obj = $this->db->fetch_object($resql);
986
987 $this->contact_lines[$i] = $obj->rowid;
988
989 $i++;
990 }
991 }
992 $this->db->free($resql);
993
994 return $num;
995 } else {
996 $this->error = "Error ".$this->db->lasterror();
997 dol_syslog(get_class($this)."::query_contact ".$this->error, LOG_ERR);
998 return -1;
999 }
1000 }
1001
1002
1012 public function transformToSQL($column_to_test, $criteria)
1013 {
1014 $return_sql_criteria = "(";
1015
1016 // This is a multiple value test
1017 if (preg_match('/;/', $criteria)) {
1018 $return_sql_not_like = array();
1019 $return_sql_like = array();
1020
1021 $criteria_array = explode(';', $criteria);
1022 foreach ($criteria_array as $inter_criteria) {
1023 if (preg_match('/!/', $inter_criteria)) {
1024 $return_sql_not_like[] = "(".$this->db->sanitize($column_to_test)." NOT LIKE '".$this->db->sanitize(str_replace('!', '', $inter_criteria))."')";
1025 } else {
1026 $return_sql_like[] = "(".$this->db->sanitize($column_to_test)." LIKE '".$this->db->sanitize($inter_criteria)."')";
1027 }
1028 }
1029
1030 if (count($return_sql_like) > 0) {
1031 $return_sql_criteria .= "(".implode(" OR ", $return_sql_like).")"; // element in arrays were sanitized previously
1032 }
1033 if (count($return_sql_not_like) > 0) {
1034 $return_sql_criteria .= " AND (".implode(" AND ", $return_sql_not_like).")"; // element in arrays were sanitized previously
1035 }
1036 } else {
1037 $return_sql_criteria .= $this->db->sanitize($column_to_test)." LIKE '".$this->db->escape($criteria)."'";
1038 }
1039
1040 $return_sql_criteria .= ")";
1041
1042 return $return_sql_criteria;
1043 }
1044}
AdvanceTargetingMailing
Class to manage advanced emailing target selector.
Definition advtargetemailing.class.php:32
AdvanceTargetingMailing\transformToSQL
transformToSQL($column_to_test, $criteria)
Parse criteria to return a SQL query formatted.
Definition advtargetemailing.class.php:1012
AdvanceTargetingMailing\fetch_by_element
fetch_by_element($id=0, $type_element='mailing')
Load object in memory from the database.
Definition advtargetemailing.class.php:347
AdvanceTargetingMailing\fetch
fetch($id)
Load object in memory from the database.
Definition advtargetemailing.class.php:230
AdvanceTargetingMailing\fetch_by_mailing
fetch_by_mailing($id=0)
Load object in memory from the database.
Definition advtargetemailing.class.php:284
AdvanceTargetingMailing\update
update($user, $notrigger=0)
Update object into database.
Definition advtargetemailing.class.php:406
AdvanceTargetingMailing\savequery
savequery($user, $arrayquery)
Save query in database to retrieve it.
Definition advtargetemailing.class.php:510
AdvanceTargetingMailing\query_thirdparty
query_thirdparty($arrayquery)
Load object in memory from database.
Definition advtargetemailing.class.php:538
AdvanceTargetingMailing\query_contact
query_contact($arrayquery, $withThirdpartyFilter=0)
Load object in memory from database.
Definition advtargetemailing.class.php:714
AdvanceTargetingMailing\create
create($user, $notrigger=0)
Create object into database.
Definition advtargetemailing.class.php:153
Client
Class to manage customers or prospects.
Definition client.class.php:34
CommonObject
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Definition commonobject.class.php:55
Contact
Class to manage contact/addresses.
Definition contact.class.php:45
ExtraFields
Class to manage standard extra fields.
Definition extrafields.class.php:43
Societe
Class to manage third parties objects (customers, suppliers, prospects...)
Definition societe.class.php:57
$conf
if(!isModEnabled('ai')||!getDolGlobalString('AI_ASSISTANT_ENABLED')) global $conf
The main.inc.php has been included so the following variable are now defined:
Definition execute_tool.php:50
dol_now
dol_now($mode='gmt')
Return date for now.
Definition functions.lib.php:4314
dol_include_once
if(!function_exists( 'dol_getprefix')) dol_include_once($relpath, $classname='')
Make an include_once using default root and alternate root if it fails.
Definition functions.lib.php:1611
getDolGlobalString
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
Definition functions.lib.php:297
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2772
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition functions.lib.php:625
name
$conf db name
Only used if Module[ID]Name translation string is not found.
Definition repair.php:133
Generated on Sat Aug 1 2026 01:00:27 for dolibarr by Doxygen 1.11.0