dolibarr 21.0.3
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 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($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) ? $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 OUTER 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 $str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
577 $sqlwhere[] = " (t.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$str.")))";
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 global $langs, $conf;
718
719 $sql = "SELECT";
720 $sql .= " t.rowid";
721 $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as t";
722 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."socpeople_extrafields as te ON te.fk_object=t.rowid ";
723
724 if (!empty($withThirdpartyFilter)) {
725 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe as ts ON ts.rowid=t.fk_soc";
726 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_extrafields as tse ON tse.fk_object=ts.rowid ";
727 }
728
729 $sqlwhere = array();
730
731 $sqlwhere[] = 't.entity IN ('.getEntity('contact').')';
732
733 if (count($arrayquery) > 0) {
734 if (array_key_exists('contact_categ', $arrayquery)) {
735 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."categorie_contact as contactcateg ON contactcateg.fk_socpeople=t.rowid ";
736 }
737
738 if (!empty($arrayquery['contact_lastname'])) {
739 $sqlwhere[] = $this->transformToSQL('t.lastname', $arrayquery['contact_lastname']);
740 }
741 if (!empty($arrayquery['contact_firstname'])) {
742 $sqlwhere[] = $this->transformToSQL('t.firstname', $arrayquery['contact_firstname']);
743 }
744 if (!empty($arrayquery['contact_country']) && count($arrayquery['contact_country'])) {
745 $sqlwhere[] = " (t.fk_pays IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_country'])))."))";
746 }
747 if (!empty($arrayquery['contact_status']) && count($arrayquery['contact_status']) > 0) {
748 $sqlwhere[] = " (t.statut IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_status'])))."))";
749 }
750 if (!empty($arrayquery['contact_civility']) && count($arrayquery['contact_civility']) > 0) {
751 $sqlwhere[] = " (t.civility IN (".$this->db->sanitize("'".implode("','", $arrayquery['contact_civility'])."'", 1)."))";
752 }
753 if ($arrayquery['contact_no_email'] != '') {
754 $tmpwhere = '';
755 if (!empty($arrayquery['contact_no_email'])) {
756 $tmpwhere .= "(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'])."'))";
757 } else {
758 $tmpwhere .= "(t.email NOT IN (SELECT email FROM ".MAIN_DB_PREFIX."mailing_unsubscribe WHERE t.entity IN (".getEntity('mailing').") AND email = '".$this->db->escape($arrayquery['contact_no_email'])."'))";
759 }
760 $sqlwhere[] = $tmpwhere;
761 }
762 if ($arrayquery['contact_update_st_dt'] != '') {
763 $sqlwhere[] = " (t.tms >= '".$this->db->idate($arrayquery['contact_update_st_dt'])."' AND t.tms <= '".$this->db->idate($arrayquery['contact_update_end_dt'])."')";
764 }
765 if ($arrayquery['contact_create_st_dt'] != '') {
766 $sqlwhere[] = " (t.datec >= '".$this->db->idate($arrayquery['contact_create_st_dt'])."' AND t.datec <= '".$this->db->idate($arrayquery['contact_create_end_dt'])."')";
767 }
768 if (!empty($arrayquery['contact_categ']) && count($arrayquery['contact_categ']) > 0) {
769 $sqlwhere[] = " (contactcateg.fk_categorie IN (".$this->db->escape(implode(",", $arrayquery['contact_categ']))."))";
770 }
771
772 //Standard Extrafield feature
773 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
774 $contactstatic = new Contact($this->db);
775 $elementtype = $contactstatic->table_element;
776
777 // fetch optionals attributes and labels
778 dol_include_once('/core/class/extrafields.class.php');
779 $extrafields = new ExtraFields($this->db);
780 $extrafields->fetch_name_optionals_label($elementtype);
781
782 $extrafields->fetch_name_optionals_label($elementtype);
783
784 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
785 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
786 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
787 if (!empty($arrayquery['options_'.$key.'_cnct'])) {
788 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
789 }
790 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
791 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
792 if (!empty($arrayquery['options_'.$key.'_max_cnct'])) {
793 $sqlwhere[] = " (te.".$key." >= ".((float) $arrayquery["options_".$key."_max_cnct"])." AND te.".$key." <= ".((float) $arrayquery["options_".$key."_min_cnct"]).")";
794 }
795 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
796 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
797 if (!empty($arrayquery['options_'.$key.'_end_dt_cnct'])) {
798 $sqlwhere[] = " (te.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt_cnct"])."' AND te.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt_cnct"])."')";
799 }
800 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
801 if ($arrayquery['options_'.$key.'_cnct'] != '') {
802 if ($arrayquery['options_'.$key.'_cnct'] == 0) {
803 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"])." OR ((te.".$key." IS NULL) AND (te.fk_object IS NOT NULL)))";
804 } else {
805 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"]).")";
806 }
807 }
808 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'link') {
809 if ($arrayquery['options_'.$key."_cnct"] > 0) {
810 $sqlwhere[]= " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"]).")";
811 }
812 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'chkbxlst'
813 && is_array($arrayquery['options_'.$key.'_cnct'])) {
814 if (count($arrayquery['options_'.$key.'_cnct'])) {
815 $i2 = 0;
816 $field = "te.".$key;
817 $sqlwhereselllist="";
818 foreach ($arrayquery['options_'.$key.'_cnct'] as $data) {
819 $data = trim($data);
820 if ($data) {
821 $sqlwhereselllist .= ($i2 > 0 ? " OR (" : "(").$field." LIKE '".$this->db->escape($data).",%'";
822 $sqlwhereselllist .= " OR ".$field." = '".$this->db->escape($data)."'";
823 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data)."'";
824 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data).",%'";
825 $sqlwhereselllist .= ")";
826 $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)
827 }
828 }
829 if (!empty($sqlwhereselllist)) {
830 $sqlwhere[] = "( ".$sqlwhereselllist." )";
831 }
832 }
833 } else {
834 if (is_array($arrayquery['options_'.$key.'_cnct'])) {
835 $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key."_cnct"])."'", 1)."))";
836 } elseif (!empty($arrayquery['options_'.$key.'_cnct'])) {
837 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
838 }
839 }
840 }
841
842 if (!empty($withThirdpartyFilter)) {
843 if (array_key_exists('cust_saleman', $arrayquery)) {
844 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc = ts.rowid";
845 }
846 if (array_key_exists('cust_categ', $arrayquery)) {
847 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc = ts.rowid";
848 }
849
850 if (!empty($arrayquery['cust_name'])) {
851 $sqlwhere[] = $this->transformToSQL('ts.nom', $arrayquery['cust_name']);
852 }
853 if (!empty($arrayquery['cust_code'])) {
854 $sqlwhere[] = $this->transformToSQL('ts.code_client', $arrayquery['cust_code']);
855 }
856 if (!empty($arrayquery['cust_adress'])) {
857 $sqlwhere[] = $this->transformToSQL('ts.address', $arrayquery['cust_adress']);
858 }
859 if (!empty($arrayquery['cust_zip'])) {
860 $sqlwhere[] = $this->transformToSQL('ts.zip', $arrayquery['cust_zip']);
861 }
862 if (!empty($arrayquery['cust_city'])) {
863 $sqlwhere[] = $this->transformToSQL('ts.town', $arrayquery['cust_city']);
864 }
865 if (!empty($arrayquery['cust_mothercompany'])) {
866 $str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
867 $sqlwhere[] = " (ts.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$str.")))";
868 }
869 if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
870 $sqlwhere[] = " (ts.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
871 }
872 if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
873 $sqlwhere[] = " (ts.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
874 }
875 if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']) > 0) {
876 $sqlwhere[] = " (ts.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
877 }
878 if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
879 $sqlwhere[] = " (ts.fk_prospectlevel IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_prospect_status']))."'))";
880 }
881 if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
882 $sqlwhere[] = " (ts.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
883 }
884 if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
885 $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
886 }
887 //if (!empty($arrayquery['cust_state'])) {
888 // $sqlwhere[] = $this->transformToSQL('tsd.nom', $arrayquery['cust_state']);
889 //}
890 if (!empty($arrayquery['cust_state']) && count($arrayquery['cust_state']) > 0) {
891 $sqlwhere[] = " (t.fk_departement IN (".$this->db->sanitize(implode(',', $arrayquery['cust_state']))."))";
892 }
893 if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country']) > 0) {
894 $sqlwhere[] = " (ts.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
895 }
896 if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
897 $sqlwhere[] = " (ts.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
898 }
899 if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
900 $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
901 }
902 if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
903 $sqlwhere[] = " (ts.default_lang IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_language']))."'))";
904 }
905
906 //Standard Extrafield feature
907 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
908 $socstatic = new Societe($this->db);
909 $elementtype = $socstatic->table_element;
910
911 // fetch optionals attributes and labels
912 dol_include_once('/core/class/extrafields.class.php');
913 $extrafields = new ExtraFields($this->db);
914
915 $extrafields->fetch_name_optionals_label($elementtype);
916
917 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
918 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
919 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
920 if (!empty($arrayquery['options_'.$key])) {
921 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery['options_'.$key])."')";
922 }
923 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
924 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
925 if (!empty($arrayquery['options_'.$key.'_max'])) {
926 $sqlwhere[] = " (tse.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND tse.".$key." <= ".((float) $arrayquery["options_".$key."_min"]).")";
927 }
928 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
929 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
930 if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
931 $sqlwhere[] = " (tse.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND tse.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
932 }
933 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
934 if ($arrayquery['options_'.$key] != '') {
935 $sqlwhere[] = " (tse.".$key." = ".((int) $arrayquery["options_".$key]).")";
936 }
937 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'link') {
938 if ($arrayquery['options_'.$key] > 0) {
939 $sqlwhere[]= " (te.".$key." = ".((int) $arrayquery["options_".$key]).")";
940 }
941 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'chkbxlst'
942 && is_array($arrayquery['options_'.$key])) {
943 if (count($arrayquery['options_'.$key])) {
944 $i2 = 0;
945 $field = "tse.".$key;
946 $sqlwhereselllist="";
947 foreach ($arrayquery['options_'.$key] as $data) {
948 $data = trim($data);
949 if ($data) {
950 $sqlwhereselllist .= ($i2 > 0 ? " OR (" : "(").$field." LIKE '".$this->db->escape($data).",%'";
951 $sqlwhereselllist .= " OR ".$field." = '".$this->db->escape($data)."'";
952 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data)."'";
953 $sqlwhereselllist .= " OR ".$field." LIKE '%,".$this->db->escape($data).",%'";
954 $sqlwhereselllist .= ")";
955 $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)
956 }
957 }
958 if (!empty($sqlwhereselllist)) {
959 $sqlwhere[] = "( ".$sqlwhereselllist." )";
960 }
961 }
962 } else {
963 if (is_array($arrayquery['options_'.$key])) {
964 $sqlwhere[] = " (tse.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
965 } elseif (!empty($arrayquery['options_'.$key])) {
966 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
967 }
968 }
969 }
970 }
971 }
972 }
973 if (count($sqlwhere) > 0) {
974 $sql .= " WHERE ".implode(" AND ", $sqlwhere);
975 }
976 }
977
978 dol_syslog(get_class($this)."::query_contact", LOG_DEBUG);
979 $resql = $this->db->query($sql);
980 if ($resql) {
981 $this->contact_lines = array();
982 $num = $this->db->num_rows($resql);
983 $i = 0;
984
985 if ($num) {
986 while ($i < $num) {
987 $obj = $this->db->fetch_object($resql);
988
989 $this->contact_lines[$i] = $obj->rowid;
990
991 $i++;
992 }
993 }
994 $this->db->free($resql);
995
996 return $num;
997 } else {
998 $this->error = "Error ".$this->db->lasterror();
999 dol_syslog(get_class($this)."::query_contact ".$this->error, LOG_ERR);
1000 return -1;
1001 }
1002 }
1003
1004
1014 public function transformToSQL($column_to_test, $criteria)
1015 {
1016 $return_sql_criteria = '(';
1017
1018 //This is a multiple value test
1019 if (preg_match('/;/', $criteria)) {
1020 $return_sql_not_like = array();
1021 $return_sql_like = array();
1022
1023 $criteria_array = explode(';', $criteria);
1024 foreach ($criteria_array as $inter_criteria) {
1025 if (preg_match('/!/', $inter_criteria)) {
1026 $return_sql_not_like[] = '('.$column_to_test.' NOT LIKE \''.str_replace('!', '', $inter_criteria).'\')';
1027 } else {
1028 $return_sql_like[] = '('.$column_to_test.' LIKE \''.$inter_criteria.'\')';
1029 }
1030 }
1031
1032 if (count($return_sql_like) > 0) {
1033 $return_sql_criteria .= '('.implode(' OR ', $return_sql_like).')';
1034 }
1035 if (count($return_sql_not_like) > 0) {
1036 $return_sql_criteria .= ' AND ('.implode(' AND ', $return_sql_not_like).')';
1037 }
1038 } else {
1039 $return_sql_criteria .= $column_to_test.' LIKE \''.$this->db->escape($criteria).'\'';
1040 }
1041
1042 $return_sql_criteria .= ')';
1043
1044 return $return_sql_criteria;
1045 }
1046}
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:1014
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:33
CommonObject
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Definition commonobject.class.php:51
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:55
dol_now
dol_now($mode='auto')
Return date for now.
Definition functions.lib.php:3904
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:1409
getDolGlobalString
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
Definition functions.lib.php:228
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2382
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition functions.lib.php:448
$conf
global $conf
The following vars must be defined: $type2label $form $conf, $lang, The following vars may also be de...
Definition member.php:79
name
$conf db name
Only used if Module[ID]Name translation string is not found.
Definition repair.php:153
Generated on Wed Aug 6 2025 01:00:19 for dolibarr by Doxygen 1.11.0