dolibarr  20.0.0-beta
advtargetemailing.class.php
Go to the documentation of this file.
1 <?php
2 /* Advance Targeting Emailing for mass emailing module
3  * Copyright (C) 2013 Florian Henry <florian.henry@open-concept.pro>
4  *
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 
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 }
Class to manage advanced emailing target selector.
transformToSQL($column_to_test, $criteria)
Parse criteria to return a SQL query formatted.
fetch_by_element($id=0, $type_element='mailing')
Load object in memory from the database.
fetch($id)
Load object in memory from the database.
fetch_by_mailing($id=0)
Load object in memory from the database.
update($user, $notrigger=0)
Update object into database.
savequery($user, $arrayquery)
Save query in database to retrieve it.
query_thirdparty($arrayquery)
Load object in memory from database.
query_contact($arrayquery, $withThirdpartyFilter=0)
Load object in memory from database.
create($user, $notrigger=0)
Create object into database.
Class to manage customers or prospects.
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Class to manage contact/addresses.
Class to manage standard extra fields.
Class to manage third parties objects (customers, suppliers, prospects...)
if(isModEnabled('invoice') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&!getDolGlobalString('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') && $user->hasRight('tax', 'charges', 'lire')) if(isModEnabled('invoice') &&isModEnabled('order') && $user->hasRight("commande", "lire") &&!getDolGlobalString('WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER')) $sql
Social contributions to pay.
Definition: index.php:745
if(!function_exists('dol_getprefix')) dol_include_once($relpath, $classname='')
Make an include_once using default root and alternate root if it fails.
dol_now($mode='auto')
Return date for now.
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
div float
Buy price without taxes.
Definition: style.css.php:960
$conf db name
Only used if Module[ID]Name translation string is not found.
Definition: repair.php:126