dolibarr  19.0.0-dev
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 
29 class 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 }
Societe
Class to manage third parties objects (customers, suppliers, prospects...)
Definition: societe.class.php:51
AdvanceTargetingMailing\create
create($user, $notrigger=0)
Create object into database.
Definition: advtargetemailing.class.php:148
AdvanceTargetingMailing\update
update($user, $notrigger=0)
Update object into database.
Definition: advtargetemailing.class.php:400
AdvanceTargetingMailing\fetch
fetch($id)
Load object in memory from the database.
Definition: advtargetemailing.class.php:224
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:1129
AdvanceTargetingMailing\savequery
savequery($user, $arrayquery)
Save query in database to retrieve it.
Definition: advtargetemailing.class.php:503
name
$conf db name
Definition: repair.php:123
CommonObject
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Definition: commonobject.class.php:45
AdvanceTargetingMailing\fetch_by_mailing
fetch_by_mailing($id=0)
Load object in memory from the database.
Definition: advtargetemailing.class.php:278
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition: functions.lib.php:266
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1741
Contact
Class to manage contact/addresses.
Definition: contact.class.php:42
$sql
if(isModEnabled('facture') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $sql
Social contributions to pay.
Definition: index.php:746
ExtraFields
Class to manage standard extra fields.
Definition: extrafields.class.php:39
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\__construct
__construct($db)
Constructor.
Definition: advtargetemailing.class.php:109
dol_now
dol_now($mode='auto')
Return date for now.
Definition: functions.lib.php:3056
AdvanceTargetingMailing\query_thirdparty
query_thirdparty($arrayquery)
Load object in memory from database.
Definition: advtargetemailing.class.php:531
AdvanceTargetingMailing\transformToSQL
transformToSQL($column_to_test, $criteria)
Parse criteria to return a SQL qury formated.
Definition: advtargetemailing.class.php:927
AdvanceTargetingMailing
Class to manage advanced emailing target selector.
Definition: advtargetemailing.class.php:29
AdvanceTargetingMailing\query_contact
query_contact($arrayquery, $withThirdpartyFilter=0)
Load object in memory from database.
Definition: advtargetemailing.class.php:683
Client
Class to manage customers or prospects.
Definition: client.class.php:31
float
div float
Buy price without taxes.
Definition: style.css.php:921
Generated on Fri Dec 1 2023 01:01:02 for dolibarr by Doxygen 1.8.17