dolibarr 20.0.0
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 *
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation, either version 3 of the License, or
8 * (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program. If not, see <https://www.gnu.org/licenses/>.
17 */
18
29class AdvanceTargetingMailing extends CommonObject
30{
34 public $db;
35
39 public $element = 'advtargetemailing';
40
44 public $table_element = 'mailing_advtarget';
45
49 public $id;
50
54 public $name;
55
59 public $fk_element;
60
64 public $type_element;
65
69 public $filtervalue;
70
74 public $fk_user_author;
75
79 public $datec = '';
80
84 public $fk_user_mod;
85
89 public $select_target_type = array();
90
94 public $type_statuscommprospect = array();
95 public $thirdparty_lines;
96 public $contact_lines;
97
98
104 public function __construct($db)
105 {
106 global $langs;
107 $langs->load('customers');
108
109 $this->db = $db;
110
111 $this->select_target_type = array(
112 '2' => $langs->trans('Contacts'),
113 '1' => $langs->trans('Contacts').'+'.$langs->trans('ThirdParty'),
114 '3' => $langs->trans('ThirdParty'),
115 '4' => $langs->trans('ContactsWithThirdpartyFilter')
116 );
117
118 require_once DOL_DOCUMENT_ROOT.'/societe/class/client.class.php';
119 $customerStatic = new Client($this->db);
120 $customerStatic->loadCacheOfProspStatus();
121 if (!empty($customerStatic->cacheprospectstatus)) {
122 foreach ($customerStatic->cacheprospectstatus as $dataProspectSt) {
123 $this->type_statuscommprospect[$dataProspectSt['id']]=$dataProspectSt['label'];
124 }
125 } else {
126 $this->type_statuscommprospect = array(
127 -1 => $langs->trans("StatusProspect-1"),
128 0 => $langs->trans("StatusProspect0"),
129 1 => $langs->trans("StatusProspect1"),
130 2 => $langs->trans("StatusProspect2"),
131 3 => $langs->trans("StatusProspect3")
132 );
133 }
134 }
135
143 public function create($user, $notrigger = 0)
144 {
145 global $conf, $langs;
146 $error = 0;
147
148 // Clean parameters
149 if (isset($this->fk_element)) {
150 $this->fk_element = (int) $this->fk_element;
151 }
152 if (isset($this->type_element)) {
153 $this->type_element = trim($this->type_element);
154 }
155
156 if (isset($this->name)) {
157 $this->name = trim($this->name);
158 }
159 if (isset($this->filtervalue)) {
160 $this->filtervalue = trim($this->filtervalue);
161 }
162
163 // Check parameters
164 // Put here code to add control on parameters values
165
166 // Insert request
167 $sql = "INSERT INTO ".MAIN_DB_PREFIX."mailing_advtarget(";
168 $sql .= "name,";
169 $sql .= "entity,";
170 $sql .= "fk_element,";
171 $sql .= "type_element,";
172 $sql .= "filtervalue,";
173 $sql .= "fk_user_author,";
174 $sql .= "datec,";
175 $sql .= "fk_user_mod";
176 $sql .= ") VALUES (";
177 $sql .= " ".(!isset($this->name) ? 'NULL' : "'".$this->db->escape($this->name)."'").",";
178 $sql .= " ".$conf->entity.",";
179 $sql .= " ".(!isset($this->fk_element) ? 'NULL' : "'".$this->db->escape($this->fk_element)."'").",";
180 $sql .= " ".(!isset($this->type_element) ? 'NULL' : "'".$this->db->escape($this->type_element)."'").",";
181 $sql .= " ".(!isset($this->filtervalue) ? 'NULL' : "'".$this->db->escape($this->filtervalue)."'").",";
182 $sql .= " ".$user->id.",";
183 $sql .= " '".$this->db->idate(dol_now())."',";
184 $sql .= " ".$user->id;
185 $sql .= ")";
186
187 $this->db->begin();
188
189 dol_syslog(get_class($this)."::create", LOG_DEBUG);
190 $resql = $this->db->query($sql);
191 if (!$resql) {
192 $error++;
193 $this->errors[] = "Error ".$this->db->lasterror();
194 }
195
196 if (!$error) {
197 $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX."mailing_advtarget");
198 }
199
200 // Commit or rollback
201 if ($error) {
202 foreach ($this->errors as $errmsg) {
203 dol_syslog(get_class($this)."::create ".$errmsg, LOG_ERR);
204 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
205 }
206 $this->db->rollback();
207 return -1 * $error;
208 } else {
209 $this->db->commit();
210 return $this->id;
211 }
212 }
213
220 public function fetch($id)
221 {
222 global $langs;
223 $sql = "SELECT";
224 $sql .= " t.rowid,";
225
226 $sql .= " t.name,";
227 $sql .= " t.entity,";
228 $sql .= " t.fk_element,";
229 $sql .= " t.type_element,";
230 $sql .= " t.filtervalue,";
231 $sql .= " t.fk_user_author,";
232 $sql .= " t.datec,";
233 $sql .= " t.fk_user_mod,";
234 $sql .= " t.tms";
235
236 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
237 $sql .= " WHERE t.rowid = ".((int) $id);
238
239 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
240 $resql = $this->db->query($sql);
241 if ($resql) {
242 if ($this->db->num_rows($resql)) {
243 $obj = $this->db->fetch_object($resql);
244
245 $this->id = $obj->rowid;
246
247 $this->name = $obj->name;
248 $this->entity = $obj->entity;
249 $this->fk_element = $obj->fk_element;
250 $this->type_element = $obj->type_element;
251 $this->filtervalue = $obj->filtervalue;
252 $this->fk_user_author = $obj->fk_user_author;
253 $this->datec = $this->db->jdate($obj->datec);
254 $this->fk_user_mod = $obj->fk_user_mod;
255 $this->tms = $this->db->jdate($obj->tms);
256 }
257 $this->db->free($resql);
258
259 return 1;
260 } else {
261 $this->error = "Error ".$this->db->lasterror();
262 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
263 return -1;
264 }
265 }
266
267 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
274 public function fetch_by_mailing($id = 0)
275 {
276 // phpcs:enable
277 global $langs;
278 $sql = "SELECT";
279 $sql .= " t.rowid,";
280
281 $sql .= " t.name,";
282 $sql .= " t.entity,";
283 $sql .= " t.fk_element,";
284 $sql .= " t.type_element,";
285 $sql .= " t.filtervalue,";
286 $sql .= " t.fk_user_author,";
287 $sql .= " t.datec,";
288 $sql .= " t.fk_user_mod,";
289 $sql .= " t.tms";
290
291 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
292 if (!empty($id)) {
293 $sql .= " WHERE t.fk_element = ".((int) $id)." AND type_element = 'mailing'";
294 } else {
295 $sql .= " WHERE t.fk_element = ".((int) $this->fk_element)." AND type_element = 'mailing'";
296 }
297
298 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
299 $resql = $this->db->query($sql);
300 if ($resql) {
301 if ($this->db->num_rows($resql)) {
302 $obj = $this->db->fetch_object($resql);
303
304 $this->id = $obj->rowid;
305
306 $this->name = $obj->name;
307 $this->entity = $obj->entity;
308 $this->fk_element = $obj->fk_element;
309 $this->type_element = $obj->type_element;
310 $this->filtervalue = $obj->filtervalue;
311 $this->fk_user_author = $obj->fk_user_author;
312 $this->datec = $this->db->jdate($obj->datec);
313 $this->fk_user_mod = $obj->fk_user_mod;
314 $this->tms = $this->db->jdate($obj->tms);
315 }
316 $this->db->free($resql);
317
318 return 1;
319 } else {
320 $this->error = "Error ".$this->db->lasterror();
321 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
322 return -1;
323 }
324 }
325
326
327
328
329 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
337 public function fetch_by_element($id = 0, $type_element = 'mailing')
338 {
339 // phpcs:enable
340 global $langs;
341 $sql = "SELECT";
342 $sql .= " t.rowid,";
343
344 $sql .= " t.name,";
345 $sql .= " t.entity,";
346 $sql .= " t.fk_element,";
347 $sql .= " t.type_element,";
348 $sql .= " t.filtervalue,";
349 $sql .= " t.fk_user_author,";
350 $sql .= " t.datec,";
351 $sql .= " t.fk_user_mod,";
352 $sql .= " t.tms";
353
354 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
355 if (!empty($id)) {
356 $sql .= " WHERE t.fk_element = ".((int) $id)." AND type_element = '".$this->db->escape($type_element)."'";
357 } else {
358 $sql .= " WHERE t.fk_element = ".((int) $this->fk_element)." AND type_element = '".$this->db->escape($type_element)."'";
359 }
360
361 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
362 $resql = $this->db->query($sql);
363 if ($resql) {
364 if ($this->db->num_rows($resql)) {
365 $obj = $this->db->fetch_object($resql);
366
367 $this->id = $obj->rowid;
368
369 $this->name = $obj->name;
370 $this->entity = $obj->entity;
371 $this->fk_element = $obj->fk_element;
372 $this->type_element = $obj->type_element;
373 $this->filtervalue = $obj->filtervalue;
374 $this->fk_user_author = $obj->fk_user_author;
375 $this->datec = $this->db->jdate($obj->datec);
376 $this->fk_user_mod = $obj->fk_user_mod;
377 $this->tms = $this->db->jdate($obj->tms);
378 }
379 $this->db->free($resql);
380
381 return 1;
382 } else {
383 $this->error = "Error ".$this->db->lasterror();
384 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
385 return -1;
386 }
387 }
388
396 public function update($user, $notrigger = 0)
397 {
398 global $conf, $langs;
399 $error = 0;
400
401 // Clean parameters
402 if (isset($this->fk_element)) {
403 $this->fk_element = (int) $this->fk_element;
404 }
405 if (isset($this->type_element)) {
406 $this->type_element = trim($this->type_element);
407 }
408 if (isset($this->name)) {
409 $this->name = trim($this->name);
410 }
411 if (isset($this->filtervalue)) {
412 $this->filtervalue = trim($this->filtervalue);
413 }
414
415 // Check parameters
416 // Put here code to add a control on parameters values
417
418 // Update request
419 $sql = "UPDATE ".MAIN_DB_PREFIX."mailing_advtarget SET";
420
421 $sql .= " name=".(isset($this->name) ? "'".$this->db->escape($this->name)."'" : "''").",";
422 $sql .= " entity=".$conf->entity.",";
423 $sql .= " fk_element=".(isset($this->fk_element) ? $this->fk_element : "null").",";
424 $sql .= " type_element=".(isset($this->type_element) ? "'".$this->db->escape($this->type_element)."'" : "null").",";
425 $sql .= " filtervalue=".(isset($this->filtervalue) ? "'".$this->db->escape($this->filtervalue)."'" : "null").",";
426 $sql .= " fk_user_mod=".$user->id;
427
428 $sql .= " WHERE rowid=".((int) $this->id);
429
430 $this->db->begin();
431 dol_syslog(get_class($this)."::update", LOG_DEBUG);
432 $resql = $this->db->query($sql);
433 if (!$resql) {
434 $error++;
435 $this->errors[] = "Error ".$this->db->lasterror();
436 }
437
438 // Commit or rollback
439 if ($error) {
440 foreach ($this->errors as $errmsg) {
441 dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
442 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
443 }
444 $this->db->rollback();
445 return -1 * $error;
446 } else {
447 $this->db->commit();
448 return 1;
449 }
450 }
451
459 public function delete($user, $notrigger = 0)
460 {
461 global $conf, $langs;
462 $error = 0;
463
464 $this->db->begin();
465
466 if (!$error) {
467 $sql = "DELETE FROM ".MAIN_DB_PREFIX."mailing_advtarget";
468 $sql .= " WHERE rowid=".((int) $this->id);
469
470 dol_syslog(get_class($this)."::delete sql=".$sql);
471 $resql = $this->db->query($sql);
472 if (!$resql) {
473 $error++;
474 $this->errors[] = "Error ".$this->db->lasterror();
475 }
476 }
477
478 // Commit or rollback
479 if ($error) {
480 foreach ($this->errors as $errmsg) {
481 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
482 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
483 }
484 $this->db->rollback();
485 return -1 * $error;
486 } else {
487 $this->db->commit();
488 return 1;
489 }
490 }
491
492
500 public function savequery($user, $arrayquery)
501 {
502 global $langs, $conf;
503
504 if (!empty($arrayquery)) {
505 $result = $this->fetch_by_mailing($this->fk_element);
506 $this->filtervalue = json_encode($arrayquery);
507 if ($result < 0) {
508 return -1;
509 }
510 if (!empty($this->id)) {
511 $this->update($user);
512 } else {
513 $this->create($user);
514 }
515 }
516 return -1;
517 }
518
519
520
521 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
528 public function query_thirdparty($arrayquery)
529 {
530 // phpcs:enable
531 global $langs, $conf, $extrafields;
532
533 $sql = "SELECT";
534 $sql .= " t.rowid";
535 $sql .= " FROM ".MAIN_DB_PREFIX."societe as t";
536 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_extrafields as te ON te.fk_object=t.rowid ";
537
538 $sqlwhere = array();
539
540 $sqlwhere[] = 't.entity IN ('.getEntity('societe').')';
541
542 if (count($arrayquery) > 0) {
543 if (array_key_exists('cust_saleman', $arrayquery)) {
544 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc = t.rowid";
545 }
546 if (array_key_exists('cust_categ', $arrayquery)) {
547 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc = t.rowid";
548 }
549
550 if (!empty($arrayquery['cust_name'])) {
551 $sqlwhere[] = $this->transformToSQL('t.nom', $arrayquery['cust_name']);
552 }
553 if (!empty($arrayquery['cust_code'])) {
554 $sqlwhere[] = $this->transformToSQL('t.code_client', $arrayquery['cust_code']);
555 }
556 if (!empty($arrayquery['cust_adress'])) {
557 $sqlwhere[] = $this->transformToSQL('t.address', $arrayquery['cust_adress']);
558 }
559 if (!empty($arrayquery['cust_zip'])) {
560 $sqlwhere[] = $this->transformToSQL('t.zip', $arrayquery['cust_zip']);
561 }
562 if (!empty($arrayquery['cust_city'])) {
563 $sqlwhere[] = $this->transformToSQL('t.town', $arrayquery['cust_city']);
564 }
565 if (!empty($arrayquery['cust_mothercompany'])) {
566 $str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
567 $sqlwhere[] = " (t.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$str.")))";
568 }
569 if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
570 $sqlwhere[] = " (t.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
571 }
572 if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
573 $sqlwhere[] = " (t.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
574 }
575 if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']) > 0) {
576 $sqlwhere[] = " (t.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
577 }
578 if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
579 $sqlwhere[] = " (t.fk_prospectlevel IN (".$this->db->sanitize("'".implode("','", $arrayquery['cust_prospect_status'])."'", 1)."))";
580 }
581 if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
582 $sqlwhere[] = " (t.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
583 }
584 if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
585 $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
586 }
587 if (!empty($arrayquery['cust_state']) && count($arrayquery['cust_state']) > 0) {
588 $sqlwhere[] = " (t.fk_departement IN (".$this->db->sanitize(implode(',', $arrayquery['cust_state']))."))";
589 }
590 if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country']) > 0) {
591 $sqlwhere[] = " (t.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
592 }
593 if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
594 $sqlwhere[] = " (t.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
595 }
596 if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
597 $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
598 }
599 if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
600 $sqlwhere[] = " (t.default_lang IN (".$this->db->sanitize("'".implode("','", $arrayquery['cust_language'])."'", 1)."))";
601 }
602
603 //Standard Extrafield feature
604 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
605 $socstatic = new Societe($this->db);
606 $elementtype = $socstatic->table_element;
607
608 $extrafields->fetch_name_optionals_label($elementtype);
609
610 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
611 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
612 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
613 if (!empty($arrayquery['options_'.$key])) {
614 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
615 }
616 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
617 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
618 if (!empty($arrayquery['options_'.$key.'_max'])) {
619 $sqlwhere[] = " (te.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND te.".$key." <= ".((float) $arrayquery["options_".$key.'_min']).")";
620 }
621 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
622 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
623 if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
624 $sqlwhere[] = " (te.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND te.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
625 }
626 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
627 if ($arrayquery['options_'.$key] != '') {
628 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key]).")";
629 }
630 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'link') {
631 if ($arrayquery['options_'.$key] > 0) {
632 $sqlwhere[]= " (te.".$key." = ".((int) $arrayquery["options_".$key]).")";
633 }
634 } else {
635 if (is_array($arrayquery['options_'.$key])) {
636 $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
637 } elseif (!empty($arrayquery['options_'.$key])) {
638 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
639 }
640 }
641 }
642 }
643
644 if (count($sqlwhere) > 0) {
645 $sql .= " WHERE ".implode(" AND ", $sqlwhere);
646 }
647 }
648
649
650 dol_syslog(get_class($this)."::query_thirdparty", LOG_DEBUG);
651 $resql = $this->db->query($sql);
652 if ($resql) {
653 $this->thirdparty_lines = array();
654 $num = $this->db->num_rows($resql);
655 $i = 0;
656
657 if ($num) {
658 while ($i < $num) {
659 $obj = $this->db->fetch_object($resql);
660 $this->thirdparty_lines[$i] = $obj->rowid;
661
662 $i++;
663 }
664 }
665 $this->db->free($resql);
666
667 return $num;
668 } else {
669 $this->error = "Error ".$this->db->lasterror();
670 dol_syslog(get_class($this)."::query_thirdparty ".$this->error, LOG_ERR);
671 return -1;
672 }
673 }
674
675 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
683 public function query_contact($arrayquery, $withThirdpartyFilter = 0)
684 {
685 // phpcs:enable
686 global $langs, $conf;
687
688 $sql = "SELECT";
689 $sql .= " t.rowid";
690 $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as t";
691 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."socpeople_extrafields as te ON te.fk_object=t.rowid ";
692
693 if (!empty($withThirdpartyFilter)) {
694 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe as ts ON ts.rowid=t.fk_soc";
695 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_extrafields as tse ON tse.fk_object=ts.rowid ";
696 }
697
698 $sqlwhere = array();
699
700 $sqlwhere[] = 't.entity IN ('.getEntity('contact').')';
701
702 if (count($arrayquery) > 0) {
703 if (array_key_exists('contact_categ', $arrayquery)) {
704 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."categorie_contact as contactcateg ON contactcateg.fk_socpeople=t.rowid ";
705 }
706
707 if (!empty($arrayquery['contact_lastname'])) {
708 $sqlwhere[] = $this->transformToSQL('t.lastname', $arrayquery['contact_lastname']);
709 }
710 if (!empty($arrayquery['contact_firstname'])) {
711 $sqlwhere[] = $this->transformToSQL('t.firstname', $arrayquery['contact_firstname']);
712 }
713 if (!empty($arrayquery['contact_country']) && count($arrayquery['contact_country'])) {
714 $sqlwhere[] = " (t.fk_pays IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_country'])))."))";
715 }
716 if (!empty($arrayquery['contact_status']) && count($arrayquery['contact_status']) > 0) {
717 $sqlwhere[] = " (t.statut IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_status'])))."))";
718 }
719 if (!empty($arrayquery['contact_civility']) && count($arrayquery['contact_civility']) > 0) {
720 $sqlwhere[] = " (t.civility IN (".$this->db->sanitize("'".implode("','", $arrayquery['contact_civility'])."'", 1)."))";
721 }
722 if ($arrayquery['contact_no_email'] != '') {
723 $tmpwhere = '';
724 if (!empty($arrayquery['contact_no_email'])) {
725 $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'])."'))";
726 } else {
727 $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'])."'))";
728 }
729 $sqlwhere[] = $tmpwhere;
730 }
731 if ($arrayquery['contact_update_st_dt'] != '') {
732 $sqlwhere[] = " (t.tms >= '".$this->db->idate($arrayquery['contact_update_st_dt'])."' AND t.tms <= '".$this->db->idate($arrayquery['contact_update_end_dt'])."')";
733 }
734 if ($arrayquery['contact_create_st_dt'] != '') {
735 $sqlwhere[] = " (t.datec >= '".$this->db->idate($arrayquery['contact_create_st_dt'])."' AND t.datec <= '".$this->db->idate($arrayquery['contact_create_end_dt'])."')";
736 }
737 if (!empty($arrayquery['contact_categ']) && count($arrayquery['contact_categ']) > 0) {
738 $sqlwhere[] = " (contactcateg.fk_categorie IN (".$this->db->escape(implode(",", $arrayquery['contact_categ']))."))";
739 }
740
741 //Standard Extrafield feature
742 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
743 $contactstatic = new Contact($this->db);
744 $elementtype = $contactstatic->table_element;
745
746 // fetch optionals attributes and labels
747 dol_include_once('/core/class/extrafields.class.php');
748 $extrafields = new ExtraFields($this->db);
749 $extrafields->fetch_name_optionals_label($elementtype);
750
751 $extrafields->fetch_name_optionals_label($elementtype);
752
753 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
754 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
755 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
756 if (!empty($arrayquery['options_'.$key.'_cnct'])) {
757 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
758 }
759 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
760 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
761 if (!empty($arrayquery['options_'.$key.'_max_cnct'])) {
762 $sqlwhere[] = " (te.".$key." >= ".((float) $arrayquery["options_".$key."_max_cnct"])." AND te.".$key." <= ".((float) $arrayquery["options_".$key."_min_cnct"]).")";
763 }
764 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
765 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
766 if (!empty($arrayquery['options_'.$key.'_end_dt_cnct'])) {
767 $sqlwhere[] = " (te.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt_cnct"])."' AND te.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt_cnct"])."')";
768 }
769 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
770 if ($arrayquery['options_'.$key.'_cnct'] != '') {
771 if ($arrayquery['options_'.$key.'_cnct'] == 0) {
772 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"])." OR ((te.".$key." IS NULL) AND (te.fk_object IS NOT NULL)))";
773 } else {
774 $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"]).")";
775 }
776 }
777 } else {
778 if (is_array($arrayquery['options_'.$key.'_cnct'])) {
779 $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key."_cnct"])."'", 1)."))";
780 } elseif (!empty($arrayquery['options_'.$key.'_cnct'])) {
781 $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
782 }
783 }
784 }
785
786 if (!empty($withThirdpartyFilter)) {
787 if (array_key_exists('cust_saleman', $arrayquery)) {
788 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc = ts.rowid";
789 }
790 if (array_key_exists('cust_categ', $arrayquery)) {
791 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc = ts.rowid";
792 }
793
794 if (!empty($arrayquery['cust_name'])) {
795 $sqlwhere[] = $this->transformToSQL('ts.nom', $arrayquery['cust_name']);
796 }
797 if (!empty($arrayquery['cust_code'])) {
798 $sqlwhere[] = $this->transformToSQL('ts.code_client', $arrayquery['cust_code']);
799 }
800 if (!empty($arrayquery['cust_adress'])) {
801 $sqlwhere[] = $this->transformToSQL('ts.address', $arrayquery['cust_adress']);
802 }
803 if (!empty($arrayquery['cust_zip'])) {
804 $sqlwhere[] = $this->transformToSQL('ts.zip', $arrayquery['cust_zip']);
805 }
806 if (!empty($arrayquery['cust_city'])) {
807 $sqlwhere[] = $this->transformToSQL('ts.town', $arrayquery['cust_city']);
808 }
809 if (!empty($arrayquery['cust_mothercompany'])) {
810 $str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
811 $sqlwhere[] = " (ts.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$str.")))";
812 }
813 if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
814 $sqlwhere[] = " (ts.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
815 }
816 if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
817 $sqlwhere[] = " (ts.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
818 }
819 if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']) > 0) {
820 $sqlwhere[] = " (ts.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
821 }
822 if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
823 $sqlwhere[] = " (ts.fk_prospectlevel IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_prospect_status']))."'))";
824 }
825 if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
826 $sqlwhere[] = " (ts.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
827 }
828 if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
829 $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
830 }
831 //if (!empty($arrayquery['cust_state'])) {
832 // $sqlwhere[] = $this->transformToSQL('tsd.nom', $arrayquery['cust_state']);
833 //}
834 if (!empty($arrayquery['cust_state']) && count($arrayquery['cust_state']) > 0) {
835 $sqlwhere[] = " (t.fk_departement IN (".$this->db->sanitize(implode(',', $arrayquery['cust_state']))."))";
836 }
837 if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country']) > 0) {
838 $sqlwhere[] = " (ts.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
839 }
840 if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
841 $sqlwhere[] = " (ts.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
842 }
843 if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
844 $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
845 }
846 if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
847 $sqlwhere[] = " (ts.default_lang IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_language']))."'))";
848 }
849
850 //Standard Extrafield feature
851 if (!getDolGlobalString('MAIN_EXTRAFIELDS_DISABLED')) {
852 $socstatic = new Societe($this->db);
853 $elementtype = $socstatic->table_element;
854
855 // fetch optionals attributes and labels
856 dol_include_once('/core/class/extrafields.class.php');
857 $extrafields = new ExtraFields($this->db);
858
859 $extrafields->fetch_name_optionals_label($elementtype);
860
861 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
862 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
863 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
864 if (!empty($arrayquery['options_'.$key])) {
865 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery['options_'.$key])."')";
866 }
867 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
868 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
869 if (!empty($arrayquery['options_'.$key.'_max'])) {
870 $sqlwhere[] = " (tse.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND tse.".$key." <= ".((float) $arrayquery["options_".$key."_min"]).")";
871 }
872 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
873 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
874 if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
875 $sqlwhere[] = " (tse.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND tse.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
876 }
877 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
878 if ($arrayquery['options_'.$key] != '') {
879 $sqlwhere[] = " (tse.".$key." = ".((int) $arrayquery["options_".$key]).")";
880 }
881 } else {
882 if (is_array($arrayquery['options_'.$key])) {
883 $sqlwhere[] = " (tse.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
884 } elseif (!empty($arrayquery['options_'.$key])) {
885 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
886 }
887 }
888 }
889 }
890 }
891 }
892 if (count($sqlwhere) > 0) {
893 $sql .= " WHERE ".implode(" AND ", $sqlwhere);
894 }
895 }
896
897 dol_syslog(get_class($this)."::query_contact", LOG_DEBUG);
898 $resql = $this->db->query($sql);
899 if ($resql) {
900 $this->contact_lines = array();
901 $num = $this->db->num_rows($resql);
902 $i = 0;
903
904 if ($num) {
905 while ($i < $num) {
906 $obj = $this->db->fetch_object($resql);
907
908 $this->contact_lines[$i] = $obj->rowid;
909
910 $i++;
911 }
912 }
913 $this->db->free($resql);
914
915 return $num;
916 } else {
917 $this->error = "Error ".$this->db->lasterror();
918 dol_syslog(get_class($this)."::query_contact ".$this->error, LOG_ERR);
919 return -1;
920 }
921 }
922
923
933 public function transformToSQL($column_to_test, $criteria)
934 {
935 $return_sql_criteria = '(';
936
937 //This is a multiple value test
938 if (preg_match('/;/', $criteria)) {
939 $return_sql_not_like = array();
940 $return_sql_like = array();
941
942 $criteria_array = explode(';', $criteria);
943 foreach ($criteria_array as $inter_criteria) {
944 if (preg_match('/!/', $inter_criteria)) {
945 $return_sql_not_like[] = '('.$column_to_test.' NOT LIKE \''.str_replace('!', '', $inter_criteria).'\')';
946 } else {
947 $return_sql_like[] = '('.$column_to_test.' LIKE \''.$inter_criteria.'\')';
948 }
949 }
950
951 if (count($return_sql_like) > 0) {
952 $return_sql_criteria .= '('.implode(' OR ', $return_sql_like).')';
953 }
954 if (count($return_sql_not_like) > 0) {
955 $return_sql_criteria .= ' AND ('.implode(' AND ', $return_sql_not_like).')';
956 }
957 } else {
958 $return_sql_criteria .= $column_to_test.' LIKE \''.$this->db->escape($criteria).'\'';
959 }
960
961 $return_sql_criteria .= ')';
962
963 return $return_sql_criteria;
964 }
965}
AdvanceTargetingMailing
Class to manage advanced emailing target selector.
Definition advtargetemailing.class.php:30
AdvanceTargetingMailing\transformToSQL
transformToSQL($column_to_test, $criteria)
Parse criteria to return a SQL query formatted.
Definition advtargetemailing.class.php:933
AdvanceTargetingMailing\fetch_by_element
fetch_by_element($id=0, $type_element='mailing')
Load object in memory from the database.
Definition advtargetemailing.class.php:337
AdvanceTargetingMailing\fetch
fetch($id)
Load object in memory from the database.
Definition advtargetemailing.class.php:220
AdvanceTargetingMailing\fetch_by_mailing
fetch_by_mailing($id=0)
Load object in memory from the database.
Definition advtargetemailing.class.php:274
AdvanceTargetingMailing\update
update($user, $notrigger=0)
Update object into database.
Definition advtargetemailing.class.php:396
AdvanceTargetingMailing\savequery
savequery($user, $arrayquery)
Save query in database to retrieve it.
Definition advtargetemailing.class.php:500
AdvanceTargetingMailing\query_thirdparty
query_thirdparty($arrayquery)
Load object in memory from database.
Definition advtargetemailing.class.php:528
AdvanceTargetingMailing\query_contact
query_contact($arrayquery, $withThirdpartyFilter=0)
Load object in memory from database.
Definition advtargetemailing.class.php:683
AdvanceTargetingMailing\create
create($user, $notrigger=0)
Create object into database.
Definition advtargetemailing.class.php:143
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:50
Contact
Class to manage contact/addresses.
Definition contact.class.php:45
ExtraFields
Class to manage standard extra fields.
Definition extrafields.class.php:41
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:3610
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:1313
getDolGlobalString
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.
Definition functions.lib.php:215
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2120
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition functions.lib.php:406
name
$conf db name
Only used if Module[ID]Name translation string is not found.
Definition repair.php:140
Generated on Sat Oct 5 2024 01:00:20 for dolibarr by Doxygen 1.11.0