dolibarr 18.0.6
advtargetemailing.class.php
Go to the documentation of this file.
1<?php
2/* Advance Targeting Emailling 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 $tms = '';
90
94 public $select_target_type = array();
95
99 public $type_statuscommprospect = array();
100 public $thirdparty_lines;
101 public $contact_lines;
102
103
109 public function __construct($db)
110 {
111 global $langs;
112 $langs->load('customers');
113
114 $this->db = $db;
115
116 $this->select_target_type = array(
117 '2' => $langs->trans('Contacts'),
118 '1' => $langs->trans('Contacts').'+'.$langs->trans('ThirdParty'),
119 '3' => $langs->trans('ThirdParty'),
120 '4' => $langs->trans('ContactsWithThirdpartyFilter')
121 );
122
123 require_once DOL_DOCUMENT_ROOT.'/societe/class/client.class.php';
124 $customerStatic = new Client($this->db);
125 $customerStatic->loadCacheOfProspStatus();
126 if (!empty($customerStatic->cacheprospectstatus)) {
127 foreach ($customerStatic->cacheprospectstatus as $dataProspectSt) {
128 $this->type_statuscommprospect[$dataProspectSt['id']]=$dataProspectSt['label'];
129 }
130 } else {
131 $this->type_statuscommprospect = array(
132 -1 => $langs->trans("StatusProspect-1"),
133 0 => $langs->trans("StatusProspect0"),
134 1 => $langs->trans("StatusProspect1"),
135 2 => $langs->trans("StatusProspect2"),
136 3 => $langs->trans("StatusProspect3")
137 );
138 }
139 }
140
148 public function create($user, $notrigger = 0)
149 {
150 global $conf, $langs;
151 $error = 0;
152
153 // Clean parameters
154 if (isset($this->fk_element)) {
155 $this->fk_element = (int) $this->fk_element;
156 }
157 if (isset($this->type_element)) {
158 $this->type_element = trim($this->type_element);
159 }
160
161 if (isset($this->name)) {
162 $this->name = trim($this->name);
163 }
164 if (isset($this->filtervalue)) {
165 $this->filtervalue = trim($this->filtervalue);
166 }
167
168 // Check parameters
169 // Put here code to add control on parameters values
170
171 // Insert request
172 $sql = "INSERT INTO ".MAIN_DB_PREFIX."mailing_advtarget(";
173 $sql .= "name,";
174 $sql .= "entity,";
175 $sql .= "fk_element,";
176 $sql .= "type_element,";
177 $sql .= "filtervalue,";
178 $sql .= "fk_user_author,";
179 $sql .= "datec,";
180 $sql .= "fk_user_mod";
181 $sql .= ") VALUES (";
182 $sql .= " ".(!isset($this->name) ? 'NULL' : "'".$this->db->escape($this->name)."'").",";
183 $sql .= " ".$conf->entity.",";
184 $sql .= " ".(!isset($this->fk_element) ? 'NULL' : "'".$this->db->escape($this->fk_element)."'").",";
185 $sql .= " ".(!isset($this->type_element) ? 'NULL' : "'".$this->db->escape($this->type_element)."'").",";
186 $sql .= " ".(!isset($this->filtervalue) ? 'NULL' : "'".$this->db->escape($this->filtervalue)."'").",";
187 $sql .= " ".$user->id.",";
188 $sql .= " '".$this->db->idate(dol_now())."',";
189 $sql .= " ".$user->id;
190 $sql .= ")";
191
192 $this->db->begin();
193
194 dol_syslog(get_class($this)."::create", LOG_DEBUG);
195 $resql = $this->db->query($sql);
196 if (!$resql) {
197 $error++; $this->errors[] = "Error ".$this->db->lasterror();
198 }
199
200 if (!$error) {
201 $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX."mailing_advtarget");
202 }
203
204 // Commit or rollback
205 if ($error) {
206 foreach ($this->errors as $errmsg) {
207 dol_syslog(get_class($this)."::create ".$errmsg, LOG_ERR);
208 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
209 }
210 $this->db->rollback();
211 return -1 * $error;
212 } else {
213 $this->db->commit();
214 return $this->id;
215 }
216 }
217
224 public function fetch($id)
225 {
226 global $langs;
227 $sql = "SELECT";
228 $sql .= " t.rowid,";
229
230 $sql .= " t.name,";
231 $sql .= " t.entity,";
232 $sql .= " t.fk_element,";
233 $sql .= " t.type_element,";
234 $sql .= " t.filtervalue,";
235 $sql .= " t.fk_user_author,";
236 $sql .= " t.datec,";
237 $sql .= " t.fk_user_mod,";
238 $sql .= " t.tms";
239
240 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
241 $sql .= " WHERE t.rowid = ".((int) $id);
242
243 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
244 $resql = $this->db->query($sql);
245 if ($resql) {
246 if ($this->db->num_rows($resql)) {
247 $obj = $this->db->fetch_object($resql);
248
249 $this->id = $obj->rowid;
250
251 $this->name = $obj->name;
252 $this->entity = $obj->entity;
253 $this->fk_element = $obj->fk_element;
254 $this->type_element = $obj->type_element;
255 $this->filtervalue = $obj->filtervalue;
256 $this->fk_user_author = $obj->fk_user_author;
257 $this->datec = $this->db->jdate($obj->datec);
258 $this->fk_user_mod = $obj->fk_user_mod;
259 $this->tms = $this->db->jdate($obj->tms);
260 }
261 $this->db->free($resql);
262
263 return 1;
264 } else {
265 $this->error = "Error ".$this->db->lasterror();
266 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
267 return -1;
268 }
269 }
270
271 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
278 public function fetch_by_mailing($id = 0)
279 {
280 // phpcs:enable
281 global $langs;
282 $sql = "SELECT";
283 $sql .= " t.rowid,";
284
285 $sql .= " t.name,";
286 $sql .= " t.entity,";
287 $sql .= " t.fk_element,";
288 $sql .= " t.type_element,";
289 $sql .= " t.filtervalue,";
290 $sql .= " t.fk_user_author,";
291 $sql .= " t.datec,";
292 $sql .= " t.fk_user_mod,";
293 $sql .= " t.tms";
294
295 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
296 if (!empty($id)) {
297 $sql .= " WHERE t.fk_element = ".((int) $id)." AND type_element = 'mailing'";
298 } else {
299 $sql .= " WHERE t.fk_element = ".((int) $this->fk_element)." AND type_element = 'mailing'";
300 }
301
302 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
303 $resql = $this->db->query($sql);
304 if ($resql) {
305 if ($this->db->num_rows($resql)) {
306 $obj = $this->db->fetch_object($resql);
307
308 $this->id = $obj->rowid;
309
310 $this->name = $obj->name;
311 $this->entity = $obj->entity;
312 $this->fk_element = $obj->fk_element;
313 $this->type_element = $obj->type_element;
314 $this->filtervalue = $obj->filtervalue;
315 $this->fk_user_author = $obj->fk_user_author;
316 $this->datec = $this->db->jdate($obj->datec);
317 $this->fk_user_mod = $obj->fk_user_mod;
318 $this->tms = $this->db->jdate($obj->tms);
319 }
320 $this->db->free($resql);
321
322 return 1;
323 } else {
324 $this->error = "Error ".$this->db->lasterror();
325 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
326 return -1;
327 }
328 }
329
330
331
332
333 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
341 public function fetch_by_element($id = 0, $type_element = 'mailing')
342 {
343 // phpcs:enable
344 global $langs;
345 $sql = "SELECT";
346 $sql .= " t.rowid,";
347
348 $sql .= " t.name,";
349 $sql .= " t.entity,";
350 $sql .= " t.fk_element,";
351 $sql .= " t.type_element,";
352 $sql .= " t.filtervalue,";
353 $sql .= " t.fk_user_author,";
354 $sql .= " t.datec,";
355 $sql .= " t.fk_user_mod,";
356 $sql .= " t.tms";
357
358 $sql .= " FROM ".MAIN_DB_PREFIX."mailing_advtarget as t";
359 if (!empty($id)) {
360 $sql .= " WHERE t.fk_element = ".((int) $id)." AND type_element = '".$this->db->escape($type_element)."'";
361 } else {
362 $sql .= " WHERE t.fk_element = ".((int) $this->fk_element)." AND type_element = '".$this->db->escape($type_element)."'";
363 }
364
365 dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
366 $resql = $this->db->query($sql);
367 if ($resql) {
368 if ($this->db->num_rows($resql)) {
369 $obj = $this->db->fetch_object($resql);
370
371 $this->id = $obj->rowid;
372
373 $this->name = $obj->name;
374 $this->entity = $obj->entity;
375 $this->fk_element = $obj->fk_element;
376 $this->type_element = $obj->type_element;
377 $this->filtervalue = $obj->filtervalue;
378 $this->fk_user_author = $obj->fk_user_author;
379 $this->datec = $this->db->jdate($obj->datec);
380 $this->fk_user_mod = $obj->fk_user_mod;
381 $this->tms = $this->db->jdate($obj->tms);
382 }
383 $this->db->free($resql);
384
385 return 1;
386 } else {
387 $this->error = "Error ".$this->db->lasterror();
388 dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
389 return -1;
390 }
391 }
392
400 public function update($user, $notrigger = 0)
401 {
402 global $conf, $langs;
403 $error = 0;
404
405 // Clean parameters
406 if (isset($this->fk_element)) {
407 $this->fk_element = (int) $this->fk_element;
408 }
409 if (isset($this->type_element)) {
410 $this->type_element = trim($this->type_element);
411 }
412 if (isset($this->name)) {
413 $this->name = trim($this->name);
414 }
415 if (isset($this->filtervalue)) {
416 $this->filtervalue = trim($this->filtervalue);
417 }
418
419 // Check parameters
420 // Put here code to add a control on parameters values
421
422 // Update request
423 $sql = "UPDATE ".MAIN_DB_PREFIX."mailing_advtarget SET";
424
425 $sql .= " name=".(isset($this->name) ? "'".$this->db->escape($this->name)."'" : "''").",";
426 $sql .= " entity=".$conf->entity.",";
427 $sql .= " fk_element=".(isset($this->fk_element) ? $this->fk_element : "null").",";
428 $sql .= " type_element=".(isset($this->type_element) ? "'".$this->db->escape($this->type_element)."'" : "null").",";
429 $sql .= " filtervalue=".(isset($this->filtervalue) ? "'".$this->db->escape($this->filtervalue)."'" : "null").",";
430 $sql .= " fk_user_mod=".$user->id;
431
432 $sql .= " WHERE rowid=".((int) $this->id);
433
434 $this->db->begin();
435 dol_syslog(get_class($this)."::update", LOG_DEBUG);
436 $resql = $this->db->query($sql);
437 if (!$resql) {
438 $error++;
439 $this->errors[] = "Error ".$this->db->lasterror();
440 }
441
442 // Commit or rollback
443 if ($error) {
444 foreach ($this->errors as $errmsg) {
445 dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
446 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
447 }
448 $this->db->rollback();
449 return -1 * $error;
450 } else {
451 $this->db->commit();
452 return 1;
453 }
454 }
455
463 public function delete($user, $notrigger = 0)
464 {
465 global $conf, $langs;
466 $error = 0;
467
468 $this->db->begin();
469
470 if (!$error) {
471 $sql = "DELETE FROM ".MAIN_DB_PREFIX."mailing_advtarget";
472 $sql .= " WHERE rowid=".((int) $this->id);
473
474 dol_syslog(get_class($this)."::delete sql=".$sql);
475 $resql = $this->db->query($sql);
476 if (!$resql) {
477 $error++; $this->errors[] = "Error ".$this->db->lasterror();
478 }
479 }
480
481 // Commit or rollback
482 if ($error) {
483 foreach ($this->errors as $errmsg) {
484 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
485 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
486 }
487 $this->db->rollback();
488 return -1 * $error;
489 } else {
490 $this->db->commit();
491 return 1;
492 }
493 }
494
495
503 public function savequery($user, $arrayquery)
504 {
505 global $langs, $conf;
506
507 if (!empty($arrayquery)) {
508 $result = $this->fetch_by_mailing($this->fk_element);
509 $this->filtervalue = json_encode($arrayquery);
510 if ($result < 0) {
511 return -1;
512 }
513 if (!empty($this->id)) {
514 $this->update($user);
515 } else {
516 $this->create($user);
517 }
518 }
519 return -1;
520 }
521
522
523
524 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
531 public function query_thirdparty($arrayquery)
532 {
533 // phpcs:enable
534 global $langs, $conf, $extrafields;
535
536 $sql = "SELECT";
537 $sql .= " t.rowid";
538 $sql .= " FROM ".MAIN_DB_PREFIX."societe as t";
539 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_extrafields as te ON te.fk_object=t.rowid ";
540
541 $sqlwhere = array();
542
543 $sqlwhere[] = 't.entity IN ('.getEntity('societe').')';
544
545 if (count($arrayquery) > 0) {
546 if (array_key_exists('cust_saleman', $arrayquery)) {
547 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc=t.rowid ";
548 }
549 if (array_key_exists('cust_categ', $arrayquery)) {
550 $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc=t.rowid ";
551 }
552
553 if (!empty($arrayquery['cust_name'])) {
554 $sqlwhere[] = $this->transformToSQL('t.nom', $arrayquery['cust_name']);
555 }
556 if (!empty($arrayquery['cust_code'])) {
557 $sqlwhere[] = $this->transformToSQL('t.code_client', $arrayquery['cust_code']);
558 }
559 if (!empty($arrayquery['cust_adress'])) {
560 $sqlwhere[] = $this->transformToSQL('t.address', $arrayquery['cust_adress']);
561 }
562 if (!empty($arrayquery['cust_zip'])) {
563 $sqlwhere[] = $this->transformToSQL('t.zip', $arrayquery['cust_zip']);
564 }
565 if (!empty($arrayquery['cust_city'])) {
566 $sqlwhere[] = $this->transformToSQL('t.town', $arrayquery['cust_city']);
567 }
568 if (!empty($arrayquery['cust_mothercompany'])) {
569 $str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
570 $sqlwhere[] = " (t.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$str.")))";
571 }
572 if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
573 $sqlwhere[] = " (t.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
574 }
575 if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
576 $sqlwhere[] = " (t.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
577 }
578 if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']) > 0) {
579 $sqlwhere[] = " (t.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
580 }
581 if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
582 $sqlwhere[] = " (t.fk_prospectlevel IN (".$this->db->sanitize("'".implode("','", $arrayquery['cust_prospect_status'])."'", 1)."))";
583 }
584 if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
585 $sqlwhere[] = " (t.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
586 }
587 if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
588 $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
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 (empty($conf->global->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 (empty($conf->global->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 OUTER 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 OUTER 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_country']) && count($arrayquery['cust_country']) > 0) {
832 $sqlwhere[] = " (ts.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
833 }
834 if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
835 $sqlwhere[] = " (ts.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
836 }
837 if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
838 $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
839 }
840 if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
841 $sqlwhere[] = " (ts.default_lang IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_language']))."'))";
842 }
843
844 //Standard Extrafield feature
845 if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
846 $socstatic = new Societe($this->db);
847 $elementtype = $socstatic->table_element;
848
849 // fetch optionals attributes and labels
850 dol_include_once('/core/class/extrafields.class.php');
851 $extrafields = new ExtraFields($this->db);
852
853 $extrafields->fetch_name_optionals_label($elementtype);
854
855 foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
856 if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
857 ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
858 if (!empty($arrayquery['options_'.$key])) {
859 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery['options_'.$key])."')";
860 }
861 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
862 ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
863 if (!empty($arrayquery['options_'.$key.'_max'])) {
864 $sqlwhere[] = " (tse.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND tse.".$key." <= ".((float) $arrayquery["options_".$key."_min"]).")";
865 }
866 } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
867 ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
868 if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
869 $sqlwhere[] = " (tse.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND tse.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
870 }
871 } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
872 if ($arrayquery['options_'.$key] != '') {
873 $sqlwhere[] = " (tse.".$key." = ".((int) $arrayquery["options_".$key]).")";
874 }
875 } else {
876 if (is_array($arrayquery['options_'.$key])) {
877 $sqlwhere[] = " (tse.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
878 } elseif (!empty($arrayquery['options_'.$key])) {
879 $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
880 }
881 }
882 }
883 }
884 }
885 }
886 if (count($sqlwhere) > 0) {
887 $sql .= " WHERE ".implode(" AND ", $sqlwhere);
888 }
889 }
890
891 dol_syslog(get_class($this)."::query_contact", LOG_DEBUG);
892 $resql = $this->db->query($sql);
893 if ($resql) {
894 $this->contact_lines = array();
895 $num = $this->db->num_rows($resql);
896 $i = 0;
897
898 if ($num) {
899 while ($i < $num) {
900 $obj = $this->db->fetch_object($resql);
901
902 $this->contact_lines[$i] = $obj->rowid;
903
904 $i++;
905 }
906 }
907 $this->db->free($resql);
908
909 return $num;
910 } else {
911 $this->error = "Error ".$this->db->lasterror();
912 dol_syslog(get_class($this)."::query_contact ".$this->error, LOG_ERR);
913 return -1;
914 }
915 }
916
917
927 public function transformToSQL($column_to_test, $criteria)
928 {
929 $return_sql_criteria = '(';
930
931 //This is a multiple value test
932 if (preg_match('/;/', $criteria)) {
933 $return_sql_not_like = array();
934 $return_sql_like = array();
935
936 $criteria_array = explode(';', $criteria);
937 foreach ($criteria_array as $inter_criteria) {
938 if (preg_match('/!/', $inter_criteria)) {
939 $return_sql_not_like[] = '('.$column_to_test.' NOT LIKE \''.str_replace('!', '', $inter_criteria).'\')';
940 } else {
941 $return_sql_like[] = '('.$column_to_test.' LIKE \''.$inter_criteria.'\')';
942 }
943 }
944
945 if (count($return_sql_like) > 0) {
946 $return_sql_criteria .= '('.implode(' OR ', $return_sql_like).')';
947 }
948 if (count($return_sql_not_like) > 0) {
949 $return_sql_criteria .= ' AND ('.implode(' AND ', $return_sql_not_like).')';
950 }
951 } else {
952 $return_sql_criteria .= $column_to_test.' LIKE \''.$this->db->escape($criteria).'\'';
953 }
954
955 $return_sql_criteria .= ')';
956
957 return $return_sql_criteria;
958 }
959}
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 qury formated.
Definition advtargetemailing.class.php:927
AdvanceTargetingMailing\fetch_by_element
fetch_by_element($id=0, $type_element='mailing')
Load object in memory from the database.
Definition advtargetemailing.class.php:341
AdvanceTargetingMailing\fetch
fetch($id)
Load object in memory from the database.
Definition advtargetemailing.class.php:224
AdvanceTargetingMailing\fetch_by_mailing
fetch_by_mailing($id=0)
Load object in memory from the database.
Definition advtargetemailing.class.php:278
AdvanceTargetingMailing\update
update($user, $notrigger=0)
Update object into database.
Definition advtargetemailing.class.php:400
AdvanceTargetingMailing\savequery
savequery($user, $arrayquery)
Save query in database to retrieve it.
Definition advtargetemailing.class.php:503
AdvanceTargetingMailing\query_thirdparty
query_thirdparty($arrayquery)
Load object in memory from database.
Definition advtargetemailing.class.php:531
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:148
Client
Class to manage customers or prospects.
Definition client.class.php:32
CommonObject
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Definition commonobject.class.php:46
Contact
Class to manage contact/addresses.
Definition contact.class.php:43
ExtraFields
Class to manage standard extra fields.
Definition extrafields.class.php:40
Societe
Class to manage third parties objects (customers, suppliers, prospects...)
Definition societe.class.php:52
dol_now
dol_now($mode='auto')
Return date for now.
Definition functions.lib.php:3112
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:1137
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:1788
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition functions.lib.php:268
name
$conf db name
Only used if Module[ID]Name translation string is not found.
Definition repair.php:123
Generated on Sat Nov 2 2024 01:00:22 for dolibarr by Doxygen 1.11.0