dolibarr 21.0.0-beta
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 } else {
645 if (is_array($arrayquery['options_'.$key])) {
646 $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
647 } elseif (!empty($arrayquery['options_'.$key])) {
648 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
649 }
650 }
651 }
652 }
653
654 if (count($sqlwhere) > 0) {
655 $sql .= " WHERE ".implode(" AND ", $sqlwhere);
656 }
657 }
658
659
660 dol_syslog(get_class($this)."::query_thirdparty", LOG_DEBUG);
661 $resql = $this->db->query($sql);
662 if ($resql) {
663 $this->thirdparty_lines = array();
664 $num = $this->db->num_rows($resql);
665 $i = 0;
666
667 if ($num) {
668 while ($i < $num) {
669 $obj = $this->db->fetch_object($resql);
670 $this->thirdparty_lines[$i] = $obj->rowid;
671
672 $i++;
673 }
674 }
675 $this->db->free($resql);
676
677 return $num;
678 } else {
679 $this->error = "Error ".$this->db->lasterror();
680 dol_syslog(get_class($this)."::query_thirdparty ".$this->error, LOG_ERR);
681 return -1;
682 }
683 }
684
685 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
693 public function query_contact($arrayquery, $withThirdpartyFilter = 0)
694 {
695 // phpcs:enable
696 global $langs, $conf;
697
698 $sql = "SELECT";
699 $sql .= " t.rowid";
700 $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as t";
701 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."socpeople_extrafields as te ON te.fk_object=t.rowid ";
702
703 if (!empty($withThirdpartyFilter)) {
704 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe as ts ON ts.rowid=t.fk_soc";
705 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_extrafields as tse ON tse.fk_object=ts.rowid ";
706 }
707
708 $sqlwhere = array();
709
710 $sqlwhere[] = 't.entity IN ('.getEntity('contact').')';
711
712 if (count($arrayquery) > 0) {
713 if (array_key_exists('contact_categ', $arrayquery)) {
714 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."categorie_contact as contactcateg ON contactcateg.fk_socpeople=t.rowid ";
715 }
716
717 if (!empty($arrayquery['contact_lastname'])) {
718 $sqlwhere[] = $this->transformToSQL('t.lastname', $arrayquery['contact_lastname']);
719 }
720 if (!empty($arrayquery['contact_firstname'])) {
721 $sqlwhere[] = $this->transformToSQL('t.firstname', $arrayquery['contact_firstname']);
722 }
723 if (!empty($arrayquery['contact_country']) && count($arrayquery['contact_country'])) {
724 $sqlwhere[] = " (t.fk_pays IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_country'])))."))";
725 }
726 if (!empty($arrayquery['contact_status']) && count($arrayquery['contact_status']) > 0) {
727 $sqlwhere[] = " (t.statut IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_status'])))."))";
728 }
729 if (!empty($arrayquery['contact_civility']) && count($arrayquery['contact_civility']) > 0) {
730 $sqlwhere[] = " (t.civility IN (".$this->db->sanitize("'".implode("','", $arrayquery['contact_civility'])."'", 1)."))";
731 }
732 if ($arrayquery['contact_no_email'] != '') {
733 $tmpwhere = '';
734 if (!empty($arrayquery['contact_no_email'])) {
735 $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'])."'))";
736 } else {
737 $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'])."'))";
738 }
739 $sqlwhere[] = $tmpwhere;
740 }
741 if ($arrayquery['contact_update_st_dt'] != '') {
742 $sqlwhere[] = " (t.tms >= '".$this->db->idate($arrayquery['contact_update_st_dt'])."' AND t.tms <= '".$this->db->idate($arrayquery['contact_update_end_dt'])."')";
743 }
744 if ($arrayquery['contact_create_st_dt'] != '') {
745 $sqlwhere[] = " (t.datec >= '".$this->db->idate($arrayquery['contact_create_st_dt'])."' AND t.datec <= '".$this->db->idate($arrayquery['contact_create_end_dt'])."')";
746 }
747 if (!empty($arrayquery['contact_categ']) && count($arrayquery['contact_categ']) > 0) {
748 $sqlwhere[] = " (contactcateg.fk_categorie IN (".$this->db->escape(implode(",", $arrayquery['contact_categ']))."))";
749 }
750
751 //Standard Extrafield feature
752 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
753 $contactstatic = new Contact($this->db);
754 $elementtype = $contactstatic->table_element;
755
756 // fetch optionals attributes and labels
757 dol_include_once('/core/class/extrafields.class.php');
758 $extrafields = new ExtraFields($this->db);
759 $extrafields->fetch_name_optionals_label($elementtype);
760
761 $extrafields->fetch_name_optionals_label($elementtype);
762
763 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
764 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
765 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
766 if (!empty($arrayquery['options_'.$key.'_cnct'])) {
767 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
768 }
769 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
770 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
771 if (!empty($arrayquery['options_'.$key.'_max_cnct'])) {
772 $sqlwhere[] = " (te.".$key." >= ".((float) $arrayquery["options_".$key."_max_cnct"])." AND te.".$key." <= ".((float) $arrayquery["options_".$key."_min_cnct"]).")";
773 }
774 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
775 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
776 if (!empty($arrayquery['options_'.$key.'_end_dt_cnct'])) {
777 $sqlwhere[] = " (te.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt_cnct"])."' AND te.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt_cnct"])."')";
778 }
779 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
780 if ($arrayquery['options_'.$key.'_cnct'] != '') {
781 if ($arrayquery['options_'.$key.'_cnct'] == 0) {
782 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"])." OR ((te.".$key." IS NULL) AND (te.fk_object IS NOT NULL)))";
783 } else {
784 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"]).")";
785 }
786 }
787 } else {
788 if (is_array($arrayquery['options_'.$key.'_cnct'])) {
789 $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key."_cnct"])."'", 1)."))";
790 } elseif (!empty($arrayquery['options_'.$key.'_cnct'])) {
791 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
792 }
793 }
794 }
795
796 if (!empty($withThirdpartyFilter)) {
797 if (array_key_exists('cust_saleman', $arrayquery)) {
798 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc = ts.rowid";
799 }
800 if (array_key_exists('cust_categ', $arrayquery)) {
801 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc = ts.rowid";
802 }
803
804 if (!empty($arrayquery['cust_name'])) {
805 $sqlwhere[] = $this->transformToSQL('ts.nom', $arrayquery['cust_name']);
806 }
807 if (!empty($arrayquery['cust_code'])) {
808 $sqlwhere[] = $this->transformToSQL('ts.code_client', $arrayquery['cust_code']);
809 }
810 if (!empty($arrayquery['cust_adress'])) {
811 $sqlwhere[] = $this->transformToSQL('ts.address', $arrayquery['cust_adress']);
812 }
813 if (!empty($arrayquery['cust_zip'])) {
814 $sqlwhere[] = $this->transformToSQL('ts.zip', $arrayquery['cust_zip']);
815 }
816 if (!empty($arrayquery['cust_city'])) {
817 $sqlwhere[] = $this->transformToSQL('ts.town', $arrayquery['cust_city']);
818 }
819 if (!empty($arrayquery['cust_mothercompany'])) {
820 $str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
821 $sqlwhere[] = " (ts.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$str.")))";
822 }
823 if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
824 $sqlwhere[] = " (ts.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
825 }
826 if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
827 $sqlwhere[] = " (ts.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
828 }
829 if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']) > 0) {
830 $sqlwhere[] = " (ts.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
831 }
832 if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
833 $sqlwhere[] = " (ts.fk_prospectlevel IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_prospect_status']))."'))";
834 }
835 if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
836 $sqlwhere[] = " (ts.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
837 }
838 if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
839 $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
840 }
841 //if (!empty($arrayquery['cust_state'])) {
842 // $sqlwhere[] = $this->transformToSQL('tsd.nom', $arrayquery['cust_state']);
843 //}
844 if (!empty($arrayquery['cust_state']) && count($arrayquery['cust_state']) > 0) {
845 $sqlwhere[] = " (t.fk_departement IN (".$this->db->sanitize(implode(',', $arrayquery['cust_state']))."))";
846 }
847 if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country']) > 0) {
848 $sqlwhere[] = " (ts.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
849 }
850 if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
851 $sqlwhere[] = " (ts.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
852 }
853 if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
854 $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
855 }
856 if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
857 $sqlwhere[] = " (ts.default_lang IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_language']))."'))";
858 }
859
860 //Standard Extrafield feature
861 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
862 $socstatic = new Societe($this->db);
863 $elementtype = $socstatic->table_element;
864
865 // fetch optionals attributes and labels
866 dol_include_once('/core/class/extrafields.class.php');
867 $extrafields = new ExtraFields($this->db);
868
869 $extrafields->fetch_name_optionals_label($elementtype);
870
871 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
872 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
873 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
874 if (!empty($arrayquery['options_'.$key])) {
875 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery['options_'.$key])."')";
876 }
877 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
878 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
879 if (!empty($arrayquery['options_'.$key.'_max'])) {
880 $sqlwhere[] = " (tse.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND tse.".$key." <= ".((float) $arrayquery["options_".$key."_min"]).")";
881 }
882 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
883 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
884 if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
885 $sqlwhere[] = " (tse.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND tse.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
886 }
887 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
888 if ($arrayquery['options_'.$key] != '') {
889 $sqlwhere[] = " (tse.".$key." = ".((int) $arrayquery["options_".$key]).")";
890 }
891 } else {
892 if (is_array($arrayquery['options_'.$key])) {
893 $sqlwhere[] = " (tse.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
894 } elseif (!empty($arrayquery['options_'.$key])) {
895 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
896 }
897 }
898 }
899 }
900 }
901 }
902 if (count($sqlwhere) > 0) {
903 $sql .= " WHERE ".implode(" AND ", $sqlwhere);
904 }
905 }
906
907 dol_syslog(get_class($this)."::query_contact", LOG_DEBUG);
908 $resql = $this->db->query($sql);
909 if ($resql) {
910 $this->contact_lines = array();
911 $num = $this->db->num_rows($resql);
912 $i = 0;
913
914 if ($num) {
915 while ($i < $num) {
916 $obj = $this->db->fetch_object($resql);
917
918 $this->contact_lines[$i] = $obj->rowid;
919
920 $i++;
921 }
922 }
923 $this->db->free($resql);
924
925 return $num;
926 } else {
927 $this->error = "Error ".$this->db->lasterror();
928 dol_syslog(get_class($this)."::query_contact ".$this->error, LOG_ERR);
929 return -1;
930 }
931 }
932
933
943 public function transformToSQL($column_to_test, $criteria)
944 {
945 $return_sql_criteria = '(';
946
947 //This is a multiple value test
948 if (preg_match('/;/', $criteria)) {
949 $return_sql_not_like = array();
950 $return_sql_like = array();
951
952 $criteria_array = explode(';', $criteria);
953 foreach ($criteria_array as $inter_criteria) {
954 if (preg_match('/!/', $inter_criteria)) {
955 $return_sql_not_like[] = '('.$column_to_test.' NOT LIKE \''.str_replace('!', '', $inter_criteria).'\')';
956 } else {
957 $return_sql_like[] = '('.$column_to_test.' LIKE \''.$inter_criteria.'\')';
958 }
959 }
960
961 if (count($return_sql_like) > 0) {
962 $return_sql_criteria .= '('.implode(' OR ', $return_sql_like).')';
963 }
964 if (count($return_sql_not_like) > 0) {
965 $return_sql_criteria .= ' AND ('.implode(' AND ', $return_sql_not_like).')';
966 }
967 } else {
968 $return_sql_criteria .= $column_to_test.' LIKE \''.$this->db->escape($criteria).'\'';
969 }
970
971 $return_sql_criteria .= ')';
972
973 return $return_sql_criteria;
974 }
975}
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:943
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:693
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:42
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:3746
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:1391
getDolGlobalString
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
Definition functions.lib.php:217
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2253
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition functions.lib.php:437
$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:152
Generated on Sun Dec 1 2024 01:00:24 for dolibarr by Doxygen 1.11.0