dolibarr  18.0.0-alpha
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  }
520 
521 
522 
523  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
530  public function query_thirdparty($arrayquery)
531  {
532  // phpcs:enable
533  global $langs, $conf, $extrafields;
534 
535  $sql = "SELECT";
536  $sql .= " t.rowid";
537  $sql .= " FROM ".MAIN_DB_PREFIX."societe as t";
538  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_extrafields as te ON te.fk_object=t.rowid ";
539 
540  $sqlwhere = array();
541 
542  $sqlwhere[] = 't.entity IN ('.getEntity('societe').')';
543 
544  if (count($arrayquery) > 0) {
545  if (array_key_exists('cust_saleman', $arrayquery)) {
546  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc=t.rowid ";
547  }
548  if (array_key_exists('cust_categ', $arrayquery)) {
549  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc=t.rowid ";
550  }
551 
552  if (!empty($arrayquery['cust_name'])) {
553  $sqlwhere[] = $this->transformToSQL('t.nom', $arrayquery['cust_name']);
554  }
555  if (!empty($arrayquery['cust_code'])) {
556  $sqlwhere[] = $this->transformToSQL('t.code_client', $arrayquery['cust_code']);
557  }
558  if (!empty($arrayquery['cust_adress'])) {
559  $sqlwhere[] = $this->transformToSQL('t.address', $arrayquery['cust_adress']);
560  }
561  if (!empty($arrayquery['cust_zip'])) {
562  $sqlwhere[] = $this->transformToSQL('t.zip', $arrayquery['cust_zip']);
563  }
564  if (!empty($arrayquery['cust_city'])) {
565  $sqlwhere[] = $this->transformToSQL('t.town', $arrayquery['cust_city']);
566  }
567  if (!empty($arrayquery['cust_mothercompany'])) {
568  $str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
569  $sqlwhere[] = " (t.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$str.")))";
570  }
571  if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
572  $sqlwhere[] = " (t.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
573  }
574  if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
575  $sqlwhere[] = " (t.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
576  }
577  if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status'] > 0)) {
578  $sqlwhere[] = " (t.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
579  }
580  if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
581  $sqlwhere[] = " (t.fk_prospectlevel IN (".$this->db->sanitize("'".implode("','", $arrayquery['cust_prospect_status'])."'", 1)."))";
582  }
583  if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
584  $sqlwhere[] = " (t.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
585  }
586  if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
587  $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
588  }
589  if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country']) > 0) {
590  $sqlwhere[] = " (t.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
591  }
592  if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
593  $sqlwhere[] = " (t.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
594  }
595  if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
596  $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
597  }
598  if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
599  $sqlwhere[] = " (t.default_lang IN (".$this->db->sanitize("'".implode("','", $arrayquery['cust_language'])."'", 1)."))";
600  }
601 
602  //Standard Extrafield feature
603  if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
604  $socstatic = new Societe($this->db);
605  $elementtype = $socstatic->table_element;
606 
607  $extrafields->fetch_name_optionals_label($elementtype);
608 
609  foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
610  if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
611  ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
612  if (!empty($arrayquery['options_'.$key])) {
613  $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
614  }
615  } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
616  ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
617  if (!empty($arrayquery['options_'.$key.'_max'])) {
618  $sqlwhere[] = " (te.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND te.".$key." <= ".((float) $arrayquery["options_".$key.'_min']).")";
619  }
620  } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
621  ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
622  if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
623  $sqlwhere[] = " (te.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND te.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
624  }
625  } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
626  if ($arrayquery['options_'.$key] != '') {
627  $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key]).")";
628  }
629  } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'link') {
630  if ($arrayquery['options_'.$key] > 0) {
631  $sqlwhere[]= " (te.".$key." = ".((int) $arrayquery["options_".$key]).")";
632  }
633  } else {
634  if (is_array($arrayquery['options_'.$key])) {
635  $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
636  } elseif (!empty($arrayquery['options_'.$key])) {
637  $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
638  }
639  }
640  }
641  }
642 
643  if (count($sqlwhere) > 0) {
644  $sql .= " WHERE ".implode(" AND ", $sqlwhere);
645  }
646  }
647 
648 
649  dol_syslog(get_class($this)."::query_thirdparty", LOG_DEBUG);
650  $resql = $this->db->query($sql);
651  if ($resql) {
652  $this->thirdparty_lines = array();
653  $num = $this->db->num_rows($resql);
654  $i = 0;
655 
656  if ($num) {
657  while ($i < $num) {
658  $obj = $this->db->fetch_object($resql);
659  $this->thirdparty_lines[$i] = $obj->rowid;
660 
661  $i++;
662  }
663  }
664  $this->db->free($resql);
665 
666  return $num;
667  } else {
668  $this->error = "Error ".$this->db->lasterror();
669  dol_syslog(get_class($this)."::query_thirdparty ".$this->error, LOG_ERR);
670  return -1;
671  }
672  }
673 
674  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
682  public function query_contact($arrayquery, $withThirdpartyFilter = 0)
683  {
684  // phpcs:enable
685  global $langs, $conf;
686 
687  $sql = "SELECT";
688  $sql .= " t.rowid";
689  $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as t";
690  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."socpeople_extrafields as te ON te.fk_object=t.rowid ";
691 
692  if (!empty($withThirdpartyFilter)) {
693  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe as ts ON ts.rowid=t.fk_soc";
694  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_extrafields as tse ON tse.fk_object=ts.rowid ";
695  }
696 
697  $sqlwhere = array();
698 
699  $sqlwhere[] = 't.entity IN ('.getEntity('contact').')';
700 
701  if (count($arrayquery) > 0) {
702  if (array_key_exists('contact_categ', $arrayquery)) {
703  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."categorie_contact as contactcateg ON contactcateg.fk_socpeople=t.rowid ";
704  }
705 
706  if (!empty($arrayquery['contact_lastname'])) {
707  $sqlwhere[] = $this->transformToSQL('t.lastname', $arrayquery['contact_lastname']);
708  }
709  if (!empty($arrayquery['contact_firstname'])) {
710  $sqlwhere[] = $this->transformToSQL('t.firstname', $arrayquery['contact_firstname']);
711  }
712  if (!empty($arrayquery['contact_country']) && count($arrayquery['contact_country'])) {
713  $sqlwhere[] = " (t.fk_pays IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_country'])))."))";
714  }
715  if (!empty($arrayquery['contact_status']) && count($arrayquery['contact_status']) > 0) {
716  $sqlwhere[] = " (t.statut IN (".$this->db->sanitize($this->db->escape(implode(',', $arrayquery['contact_status'])))."))";
717  }
718  if (!empty($arrayquery['contact_civility']) && count($arrayquery['contact_civility']) > 0) {
719  $sqlwhere[] = " (t.civility IN (".$this->db->sanitize("'".implode("','", $arrayquery['contact_civility'])."'", 1)."))";
720  }
721  if ($arrayquery['contact_no_email'] != '') {
722  $tmpwhere = '';
723  if (!empty($arrayquery['contact_no_email'])) {
724  $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'])."'))";
725  } else {
726  $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'])."'))";
727  }
728  $sqlwhere[] = $tmpwhere;
729  }
730  if ($arrayquery['contact_update_st_dt'] != '') {
731  $sqlwhere[] = " (t.tms >= '".$this->db->idate($arrayquery['contact_update_st_dt'])."' AND t.tms <= '".$this->db->idate($arrayquery['contact_update_end_dt'])."')";
732  }
733  if ($arrayquery['contact_create_st_dt'] != '') {
734  $sqlwhere[] = " (t.datec >= '".$this->db->idate($arrayquery['contact_create_st_dt'])."' AND t.datec <= '".$this->db->idate($arrayquery['contact_create_end_dt'])."')";
735  }
736  if (!empty($arrayquery['contact_categ']) && count($arrayquery['contact_categ']) > 0) {
737  $sqlwhere[] = " (contactcateg.fk_categorie IN (".$this->db->escape(implode(",", $arrayquery['contact_categ']))."))";
738  }
739 
740  //Standard Extrafield feature
741  if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
742  $contactstatic = new Contact($this->db);
743  $elementtype = $contactstatic->table_element;
744 
745  // fetch optionals attributes and labels
746  dol_include_once('/core/class/extrafields.class.php');
747  $extrafields = new ExtraFields($this->db);
748  $extrafields->fetch_name_optionals_label($elementtype);
749 
750  $extrafields->fetch_name_optionals_label($elementtype);
751 
752  foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
753  if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
754  ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
755  if (!empty($arrayquery['options_'.$key.'_cnct'])) {
756  $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
757  }
758  } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
759  ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
760  if (!empty($arrayquery['options_'.$key.'_max_cnct'])) {
761  $sqlwhere[] = " (te.".$key." >= ".((float) $arrayquery["options_".$key."_max_cnct"])." AND te.".$key." <= ".((float) $arrayquery["options_".$key."_min_cnct"]).")";
762  }
763  } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
764  ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
765  if (!empty($arrayquery['options_'.$key.'_end_dt_cnct'])) {
766  $sqlwhere[] = " (te.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt_cnct"])."' AND te.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt_cnct"])."')";
767  }
768  } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
769  if ($arrayquery['options_'.$key.'_cnct'] != '') {
770  if ($arrayquery['options_'.$key.'_cnct'] == 0) {
771  $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"])." OR ((te.".$key." IS NULL) AND (te.fk_object IS NOT NULL)))";
772  } else {
773  $sqlwhere[] = " (te.".$key." = ".((int) $arrayquery["options_".$key."_cnct"]).")";
774  }
775  }
776  } else {
777  if (is_array($arrayquery['options_'.$key.'_cnct'])) {
778  $sqlwhere[] = " (te.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key."_cnct"])."'", 1)."))";
779  } elseif (!empty($arrayquery['options_'.$key.'_cnct'])) {
780  $sqlwhere[] = " (te.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key."_cnct"])."')";
781  }
782  }
783  }
784 
785  if (!empty($withThirdpartyFilter)) {
786  if (array_key_exists('cust_saleman', $arrayquery)) {
787  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as saleman ON saleman.fk_soc=ts.rowid ";
788  }
789  if (array_key_exists('cust_categ', $arrayquery)) {
790  $sql .= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."categorie_societe as custcateg ON custcateg.fk_soc=ts.rowid ";
791  }
792 
793  if (!empty($arrayquery['cust_name'])) {
794  $sqlwhere[] = $this->transformToSQL('ts.nom', $arrayquery['cust_name']);
795  }
796  if (!empty($arrayquery['cust_code'])) {
797  $sqlwhere[] = $this->transformToSQL('ts.code_client', $arrayquery['cust_code']);
798  }
799  if (!empty($arrayquery['cust_adress'])) {
800  $sqlwhere[] = $this->transformToSQL('ts.address', $arrayquery['cust_adress']);
801  }
802  if (!empty($arrayquery['cust_zip'])) {
803  $sqlwhere[] = $this->transformToSQL('ts.zip', $arrayquery['cust_zip']);
804  }
805  if (!empty($arrayquery['cust_city'])) {
806  $sqlwhere[] = $this->transformToSQL('ts.town', $arrayquery['cust_city']);
807  }
808  if (!empty($arrayquery['cust_mothercompany'])) {
809  $str = $this->transformToSQL('nom', $arrayquery['cust_mothercompany']);
810  $sqlwhere[] = " (ts.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX."societe WHERE (".$str.")))";
811  }
812  if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status']) > 0) {
813  $sqlwhere[] = " (ts.status IN (".$this->db->sanitize(implode(',', $arrayquery['cust_status']))."))";
814  }
815  if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust']) > 0) {
816  $sqlwhere[] = " (ts.client IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typecust']))."))";
817  }
818  if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status'] > 0)) {
819  $sqlwhere[] = " (ts.fk_stcomm IN (".$this->db->sanitize(implode(',', $arrayquery['cust_comm_status']))."))";
820  }
821  if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status']) > 0) {
822  $sqlwhere[] = " (ts.fk_prospectlevel IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_prospect_status']))."'))";
823  }
824  if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent']) > 0) {
825  $sqlwhere[] = " (ts.fk_typent IN (".$this->db->sanitize(implode(',', $arrayquery['cust_typeent']))."))";
826  }
827  if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman']) > 0) {
828  $sqlwhere[] = " (saleman.fk_user IN (".$this->db->sanitize(implode(',', $arrayquery['cust_saleman']))."))";
829  }
830  if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country']) > 0) {
831  $sqlwhere[] = " (ts.fk_pays IN (".$this->db->sanitize(implode(',', $arrayquery['cust_country']))."))";
832  }
833  if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id']) > 0) {
834  $sqlwhere[] = " (ts.fk_effectif IN (".$this->db->sanitize(implode(',', $arrayquery['cust_effectif_id']))."))";
835  }
836  if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ']) > 0) {
837  $sqlwhere[] = " (custcateg.fk_categorie IN (".$this->db->sanitize(implode(',', $arrayquery['cust_categ']))."))";
838  }
839  if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language']) > 0) {
840  $sqlwhere[] = " (ts.default_lang IN ('".$this->db->sanitize(implode("','", $arrayquery['cust_language']))."'))";
841  }
842 
843  //Standard Extrafield feature
844  if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
845  $socstatic = new Societe($this->db);
846  $elementtype = $socstatic->table_element;
847 
848  // fetch optionals attributes and labels
849  dol_include_once('/core/class/extrafields.class.php');
850  $extrafields = new ExtraFields($this->db);
851 
852  $extrafields->fetch_name_optionals_label($elementtype);
853 
854  foreach ($extrafields->attributes[$elementtype]['label'] as $key => $val) {
855  if (($extrafields->attributes[$elementtype]['type'][$key] == 'varchar') ||
856  ($extrafields->attributes[$elementtype]['type'][$key] == 'text')) {
857  if (!empty($arrayquery['options_'.$key])) {
858  $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery['options_'.$key])."')";
859  }
860  } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'int') ||
861  ($extrafields->attributes[$elementtype]['type'][$key] == 'double')) {
862  if (!empty($arrayquery['options_'.$key.'_max'])) {
863  $sqlwhere[] = " (tse.".$key." >= ".((float) $arrayquery["options_".$key."_max"])." AND tse.".$key." <= ".((float) $arrayquery["options_".$key."_min"]).")";
864  }
865  } elseif (($extrafields->attributes[$elementtype]['type'][$key] == 'date') ||
866  ($extrafields->attributes[$elementtype]['type'][$key] == 'datetime')) {
867  if (!empty($arrayquery['options_'.$key.'_end_dt'])) {
868  $sqlwhere[] = " (tse.".$key." >= '".$this->db->idate($arrayquery["options_".$key."_st_dt"])."' AND tse.".$key." <= '".$this->db->idate($arrayquery["options_".$key."_end_dt"])."')";
869  }
870  } elseif ($extrafields->attributes[$elementtype]['type'][$key] == 'boolean') {
871  if ($arrayquery['options_'.$key] != '') {
872  $sqlwhere[] = " (tse.".$key." = ".((int) $arrayquery["options_".$key]).")";
873  }
874  } else {
875  if (is_array($arrayquery['options_'.$key])) {
876  $sqlwhere[] = " (tse.".$key." IN (".$this->db->sanitize("'".implode("','", $arrayquery["options_".$key])."'", 1)."))";
877  } elseif (!empty($arrayquery['options_'.$key])) {
878  $sqlwhere[] = " (tse.".$key." LIKE '".$this->db->escape($arrayquery["options_".$key])."')";
879  }
880  }
881  }
882  }
883  }
884  }
885  if (count($sqlwhere) > 0) {
886  $sql .= " WHERE ".implode(" AND ", $sqlwhere);
887  }
888  }
889 
890  dol_syslog(get_class($this)."::query_contact", LOG_DEBUG);
891  $resql = $this->db->query($sql);
892  if ($resql) {
893  $this->contact_lines = array();
894  $num = $this->db->num_rows($resql);
895  $i = 0;
896 
897  if ($num) {
898  while ($i < $num) {
899  $obj = $this->db->fetch_object($resql);
900 
901  $this->contact_lines[$i] = $obj->rowid;
902 
903  $i++;
904  }
905  }
906  $this->db->free($resql);
907 
908  return $num;
909  } else {
910  $this->error = "Error ".$this->db->lasterror();
911  dol_syslog(get_class($this)."::query_contact ".$this->error, LOG_ERR);
912  return -1;
913  }
914  }
915 
916 
926  public function transformToSQL($column_to_test, $criteria)
927  {
928  $return_sql_criteria = '(';
929 
930  //This is a multiple value test
931  if (preg_match('/;/', $criteria)) {
932  $return_sql_not_like = array();
933  $return_sql_like = array();
934 
935  $criteria_array = explode(';', $criteria);
936  foreach ($criteria_array as $inter_criteria) {
937  if (preg_match('/!/', $inter_criteria)) {
938  $return_sql_not_like[] = '('.$column_to_test.' NOT LIKE \''.str_replace('!', '', $inter_criteria).'\')';
939  } else {
940  $return_sql_like[] = '('.$column_to_test.' LIKE \''.$inter_criteria.'\')';
941  }
942  }
943 
944  if (count($return_sql_like) > 0) {
945  $return_sql_criteria .= '('.implode(' OR ', $return_sql_like).')';
946  }
947  if (count($return_sql_not_like) > 0) {
948  $return_sql_criteria .= ' AND ('.implode(' AND ', $return_sql_not_like).')';
949  }
950  } else {
951  $return_sql_criteria .= $column_to_test.' LIKE \''.$this->db->escape($criteria).'\'';
952  }
953 
954  $return_sql_criteria .= ')';
955 
956  return $return_sql_criteria;
957  }
958 }
Societe
Class to manage third parties objects (customers, suppliers, prospects...)
Definition: societe.class.php:50
db
$conf db
API class for accounts.
Definition: inc.php:41
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
$sql
if(isModEnabled('facture') &&!empty($user->rights->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') &&!empty($user->rights->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:745
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:1043
AdvanceTargetingMailing\savequery
savequery($user, $arrayquery)
Save query in database to retrieve it.
Definition: advtargetemailing.class.php:503
name
$conf db name
Definition: repair.php:122
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:190
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1639
Contact
Class to manage contact/addresses.
Definition: contact.class.php:41
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:2947
AdvanceTargetingMailing\query_thirdparty
query_thirdparty($arrayquery)
Load object in memory from database.
Definition: advtargetemailing.class.php:530
AdvanceTargetingMailing\transformToSQL
transformToSQL($column_to_test, $criteria)
Parse criteria to return a SQL qury formated.
Definition: advtargetemailing.class.php:926
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:682
Client
Class to manage customers or prospects.
Definition: client.class.php:31
float
div float
Buy price without taxes.
Definition: style.css.php:920
Generated on Wed Mar 1 2023 01:00:56 for dolibarr by Doxygen 1.8.17