dolibarr  9.0.0
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 <http://www.gnu.org/licenses/>.
17 */
18 
30 {
34  public $db;
35 
39  public $error='';
40 
44  public $errors = array();
45 
49  public $element='advtargetemailing';
50 
54  public $table_element='advtargetemailing';
55 
59  public $id;
60 
61  var $name;
62  var $entity;
63  var $fk_element;
64  var $type_element;
65  var $filtervalue;
66  var $fk_user_author;
67  var $datec='';
68  var $fk_user_mod;
69  var $tms='';
70 
71  var $select_target_type = array();
72  var $type_statuscommprospect=array();
73  var $thirdparty_lines;
74  var $contact_lines;
75 
76 
82  function __construct($db)
83  {
84  global $langs;
85  $langs->load('customers');
86 
87  $this->db = $db;
88 
89  $this->select_target_type = array(
90  '2' => $langs->trans('Contacts'),
91  '1' => $langs->trans('Contacts') . '+' . $langs->trans('ThirdParty'),
92  '3' => $langs->trans('ThirdParty'),
93  '4' => $langs->trans('ContactsWithThirdpartyFilter')
94  );
95  $this->type_statuscommprospect = array(
96  - 1 => $langs->trans("StatusProspect-1"),
97  0 => $langs->trans("StatusProspect0"),
98  1 => $langs->trans("StatusProspect1"),
99  2 => $langs->trans("StatusProspect2"),
100  3 => $langs->trans("StatusProspect3")
101  );
102  }
103 
111  function create($user, $notrigger=0)
112  {
113  global $conf, $langs;
114  $error=0;
115 
116  // Clean parameters
117  if (isset($this->fk_element)) $this->fk_element=trim($this->fk_element);
118  if (isset($this->type_element)) $this->type_element=trim($this->type_element);
119 
120  if (isset($this->name)) $this->name=trim($this->name);
121  if (isset($this->filtervalue)) $this->filtervalue=trim($this->filtervalue);
122  if (isset($this->fk_user_author)) $this->fk_user_author=trim($this->fk_user_author);
123  if (isset($this->fk_user_mod)) $this->fk_user_mod=trim($this->fk_user_mod);
124 
125 
126 
127  // Check parameters
128  // Put here code to add control on parameters values
129 
130  // Insert request
131  $sql = "INSERT INTO ".MAIN_DB_PREFIX."advtargetemailing(";
132 
133  $sql.= "name,";
134  $sql.= "entity,";
135  $sql.= "fk_element,";
136  $sql.= "type_element,";
137  $sql.= "filtervalue,";
138  $sql.= "fk_user_author,";
139  $sql.= "datec,";
140  $sql.= "fk_user_mod";
141 
142 
143  $sql.= ") VALUES (";
144 
145  $sql.= " ".(! isset($this->name)?'NULL':"'".$this->db->escape($this->name)."'").",";
146  $sql.= " ".$conf->entity.",";
147  $sql.= " ".(! isset($this->fk_element)?'NULL':"'".$this->db->escape($this->fk_element)."'").",";
148  $sql.= " ".(! isset($this->type_element)?'NULL':"'".$this->db->escape($this->type_element)."'").",";
149  $sql.= " ".(! isset($this->filtervalue)?'NULL':"'".$this->db->escape($this->filtervalue)."'").",";
150  $sql.= " ".$user->id.",";
151  $sql.= " '".$this->db->idate(dol_now())."',";
152  $sql.= " ".$user->id;
153 
154 
155  $sql.= ")";
156 
157  $this->db->begin();
158 
159  dol_syslog(get_class($this)."::create sql=".$sql, LOG_DEBUG);
160  $resql=$this->db->query($sql);
161  if (! $resql) { $error++; $this->errors[]="Error ".$this->db->lasterror(); }
162 
163  if (! $error)
164  {
165  $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX."advtargetemailing");
166 
167  if (! $notrigger)
168  {
169  // Uncomment this and change MYOBJECT to your own tag if you
170  // want this action calls a trigger.
171 
173  //include_once DOL_DOCUMENT_ROOT . '/core/class/interfaces.class.php';
174  //$interface=new Interfaces($this->db);
175  //$result=$interface->run_triggers('MYOBJECT_CREATE',$this,$user,$langs,$conf);
176  //if ($result < 0) { $error++; $this->errors=$interface->errors; }
178  }
179  }
180 
181  // Commit or rollback
182  if ($error)
183  {
184  foreach($this->errors as $errmsg)
185  {
186  dol_syslog(get_class($this)."::create ".$errmsg, LOG_ERR);
187  $this->error.=($this->error?', '.$errmsg:$errmsg);
188  }
189  $this->db->rollback();
190  return -1*$error;
191  }
192  else
193  {
194  $this->db->commit();
195  return $this->id;
196  }
197  }
198 
205  function fetch($id)
206  {
207  global $langs;
208  $sql = "SELECT";
209  $sql.= " t.rowid,";
210 
211  $sql.= " t.name,";
212  $sql.= " t.entity,";
213  $sql.= " t.fk_element,";
214  $sql.= " t.type_element,";
215  $sql.= " t.filtervalue,";
216  $sql.= " t.fk_user_author,";
217  $sql.= " t.datec,";
218  $sql.= " t.fk_user_mod,";
219  $sql.= " t.tms";
220 
221  $sql.= " FROM ".MAIN_DB_PREFIX."advtargetemailing as t";
222  $sql.= " WHERE t.rowid = ".$id;
223 
224  dol_syslog(get_class($this)."::fetch sql=".$sql, LOG_DEBUG);
225  $resql=$this->db->query($sql);
226  if ($resql)
227  {
228  if ($this->db->num_rows($resql))
229  {
230  $obj = $this->db->fetch_object($resql);
231 
232  $this->id = $obj->rowid;
233 
234  $this->name = $obj->name;
235  $this->entity = $obj->entity;
236  $this->fk_element = $obj->fk_element;
237  $this->type_element = $obj->type_element;
238  $this->filtervalue = $obj->filtervalue;
239  $this->fk_user_author = $obj->fk_user_author;
240  $this->datec = $this->db->jdate($obj->datec);
241  $this->fk_user_mod = $obj->fk_user_mod;
242  $this->tms = $this->db->jdate($obj->tms);
243  }
244  $this->db->free($resql);
245 
246  return 1;
247  }
248  else
249  {
250  $this->error="Error ".$this->db->lasterror();
251  dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
252  return -1;
253  }
254  }
255 
256  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
263  function fetch_by_mailing($id=0)
264  {
265  // phpcs:enable
266  global $langs;
267  $sql = "SELECT";
268  $sql.= " t.rowid,";
269 
270  $sql.= " t.name,";
271  $sql.= " t.entity,";
272  $sql.= " t.fk_element,";
273  $sql.= " t.type_element,";
274  $sql.= " t.filtervalue,";
275  $sql.= " t.fk_user_author,";
276  $sql.= " t.datec,";
277  $sql.= " t.fk_user_mod,";
278  $sql.= " t.tms";
279 
280  $sql.= " FROM ".MAIN_DB_PREFIX."advtargetemailing as t";
281  if (!empty($id)) {
282  $sql.= " WHERE t.fk_element = ".$id." AND type_element='mailing'";
283  }else {
284  $sql.= " WHERE t.fk_element = ".$this->fk_element." AND type_element='mailing'";
285  }
286 
287  dol_syslog(get_class($this)."::fetch sql=".$sql, LOG_DEBUG);
288  $resql=$this->db->query($sql);
289  if ($resql)
290  {
291  if ($this->db->num_rows($resql))
292  {
293  $obj = $this->db->fetch_object($resql);
294 
295  $this->id = $obj->rowid;
296 
297  $this->name = $obj->name;
298  $this->entity = $obj->entity;
299  $this->fk_element = $obj->fk_element;
300  $this->type_element = $obj->type_element;
301  $this->filtervalue = $obj->filtervalue;
302  $this->fk_user_author = $obj->fk_user_author;
303  $this->datec = $this->db->jdate($obj->datec);
304  $this->fk_user_mod = $obj->fk_user_mod;
305  $this->tms = $this->db->jdate($obj->tms);
306  }
307  $this->db->free($resql);
308 
309  return 1;
310  }
311  else
312  {
313  $this->error="Error ".$this->db->lasterror();
314  dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
315  return -1;
316  }
317  }
318 
319 
320 
321 
322  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
330  function fetch_by_element($id=0, $type_element='mailing')
331  {
332  // phpcs:enable
333  global $langs;
334  $sql = "SELECT";
335  $sql.= " t.rowid,";
336 
337  $sql.= " t.name,";
338  $sql.= " t.entity,";
339  $sql.= " t.fk_element,";
340  $sql.= " t.type_element,";
341  $sql.= " t.filtervalue,";
342  $sql.= " t.fk_user_author,";
343  $sql.= " t.datec,";
344  $sql.= " t.fk_user_mod,";
345  $sql.= " t.tms";
346 
347  $sql.= " FROM ".MAIN_DB_PREFIX."advtargetemailing as t";
348  if (!empty($id)) {
349  $sql.= " WHERE t.fk_element = ".$id." AND type_element='$type_element'";
350  }else {
351  $sql.= " WHERE t.fk_element = ".$this->fk_element." AND type_element='$type_element'";
352  }
353 
354  dol_syslog(get_class($this)."::fetch sql=".$sql, LOG_DEBUG);
355  $resql=$this->db->query($sql);
356  if ($resql)
357  {
358  if ($this->db->num_rows($resql))
359  {
360  $obj = $this->db->fetch_object($resql);
361 
362  $this->id = $obj->rowid;
363 
364  $this->name = $obj->name;
365  $this->entity = $obj->entity;
366  $this->fk_element = $obj->fk_element;
367  $this->type_element = $obj->type_element;
368  $this->filtervalue = $obj->filtervalue;
369  $this->fk_user_author = $obj->fk_user_author;
370  $this->datec = $this->db->jdate($obj->datec);
371  $this->fk_user_mod = $obj->fk_user_mod;
372  $this->tms = $this->db->jdate($obj->tms);
373  }
374  $this->db->free($resql);
375 
376  return 1;
377  }
378  else
379  {
380  $this->error="Error ".$this->db->lasterror();
381  dol_syslog(get_class($this)."::fetch ".$this->error, LOG_ERR);
382  return -1;
383  }
384  }
385 
393  function update($user, $notrigger=0)
394  {
395  global $conf, $langs;
396  $error=0;
397 
398  // Clean parameters
399  if (isset($this->fk_element)) $this->fk_element=trim($this->fk_element);
400  if (isset($this->type_element)) $this->type_element=trim($this->type_element);
401  if (isset($this->name)) $this->name=trim($this->name);
402  if (isset($this->filtervalue)) $this->filtervalue=trim($this->filtervalue);
403  if (isset($this->fk_user_author)) $this->fk_user_author=trim($this->fk_user_author);
404  if (isset($this->fk_user_mod)) $this->fk_user_mod=trim($this->fk_user_mod);
405 
406 
407 
408  // Check parameters
409  // Put here code to add a control on parameters values
410 
411  // Update request
412  $sql = "UPDATE ".MAIN_DB_PREFIX."advtargetemailing SET";
413 
414  $sql.= " name=".(isset($this->name)?"'".$this->db->escape($this->name)."'":"''").",";
415  $sql.= " entity=".$conf->entity.",";
416  $sql.= " fk_element=".(isset($this->fk_element)?$this->fk_element:"null").",";
417  $sql.= " type_element=".(isset($this->type_element)?"'".$this->db->escape($this->type_element)."'":"null").",";
418  $sql.= " filtervalue=".(isset($this->filtervalue)?"'".$this->db->escape($this->filtervalue)."'":"null").",";
419  $sql.= " fk_user_mod=".$user->id;
420 
421  $sql.= " WHERE rowid=".$this->id;
422 
423  $this->db->begin();
424  dol_syslog(get_class($this)."::update sql=".$sql, LOG_DEBUG);
425  $resql = $this->db->query($sql);
426  if (! $resql) { $error++; $this->errors[]="Error ".$this->db->lasterror(); }
427 
428  if (! $error)
429  {
430  if (! $notrigger)
431  {
432  // Uncomment this and change MYOBJECT to your own tag if you
433  // want this action calls a trigger.
434 
436  //include_once DOL_DOCUMENT_ROOT . '/core/class/interfaces.class.php';
437  //$interface=new Interfaces($this->db);
438  //$result=$interface->run_triggers('MYOBJECT_MODIFY',$this,$user,$langs,$conf);
439  //if ($result < 0) { $error++; $this->errors=$interface->errors; }
441  }
442  }
443 
444  // Commit or rollback
445  if ($error)
446  {
447  foreach($this->errors as $errmsg)
448  {
449  dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
450  $this->error.=($this->error?', '.$errmsg:$errmsg);
451  }
452  $this->db->rollback();
453  return -1*$error;
454  }
455  else
456  {
457  $this->db->commit();
458  return 1;
459  }
460  }
461 
469  function delete($user, $notrigger=0)
470  {
471  global $conf, $langs;
472  $error=0;
473 
474  $this->db->begin();
475 
476  if (! $error)
477  {
478  if (! $notrigger)
479  {
480  // Uncomment this and change MYOBJECT to your own tag if you
481  // want this action calls a trigger.
482 
484  //include_once DOL_DOCUMENT_ROOT . '/core/class/interfaces.class.php';
485  //$interface=new Interfaces($this->db);
486  //$result=$interface->run_triggers('MYOBJECT_DELETE',$this,$user,$langs,$conf);
487  //if ($result < 0) { $error++; $this->errors=$interface->errors; }
489  }
490  }
491 
492  if (! $error)
493  {
494  $sql = "DELETE FROM ".MAIN_DB_PREFIX."advtargetemailing";
495  $sql.= " WHERE rowid=".$this->id;
496 
497  dol_syslog(get_class($this)."::delete sql=".$sql);
498  $resql = $this->db->query($sql);
499  if (! $resql) { $error++; $this->errors[]="Error ".$this->db->lasterror(); }
500  }
501 
502  // Commit or rollback
503  if ($error)
504  {
505  foreach($this->errors as $errmsg)
506  {
507  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
508  $this->error.=($this->error?', '.$errmsg:$errmsg);
509  }
510  $this->db->rollback();
511  return -1*$error;
512  }
513  else
514  {
515  $this->db->commit();
516  return 1;
517  }
518  }
519 
520 
528  function savequery($user,$arrayquery)
529  {
530  global $langs,$conf;
531 
532  if (!empty($arrayquery)) {
533  $result=$this->fetch_by_mailing($this->fk_element);
534  $this->filtervalue=json_encode($arrayquery);
535  if ($result<0) {
536  return -1;
537  }
538  if (!empty($this->id)) {
539  $this->update($user);
540  }else {
541  $this->create($user);
542  }
543  }
544  }
545 
546 
547 
548  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
555  function query_thirdparty($arrayquery)
556  {
557  // phpcs:enable
558  global $langs,$conf;
559 
560  $sql = "SELECT";
561  $sql.= " t.rowid";
562  $sql.= " FROM " . MAIN_DB_PREFIX . "societe as t";
563  $sql.= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "societe_extrafields as te ON te.fk_object=t.rowid ";
564 
565  $sqlwhere=array();
566 
567  $sqlwhere[]= 't.entity IN ('.getEntity('societe').')';
568 
569  if (count($arrayquery)>0) {
570 
571  if (array_key_exists('cust_saleman', $arrayquery)) {
572  $sql.= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as saleman ON saleman.fk_soc=t.rowid ";
573  }
574  if (array_key_exists('cust_categ', $arrayquery)) {
575  $sql.= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "categorie_societe as custcateg ON custcateg.fk_soc=t.rowid ";
576  }
577 
578  if (!empty($arrayquery['cust_name'])) {
579 
580  $sqlwhere[]= $this->transformToSQL('t.nom',$arrayquery['cust_name']);
581  }
582  if (!empty($arrayquery['cust_code'])) {
583  $sqlwhere[]= $this->transformToSQL('t.code_client',$arrayquery['cust_code']);
584  }
585  if (!empty($arrayquery['cust_adress'])) {
586  $sqlwhere[]= $this->transformToSQL('t.address',$arrayquery['cust_adress']);
587  }
588  if (!empty($arrayquery['cust_zip'])) {
589  $sqlwhere[]= $this->transformToSQL('t.zip',$arrayquery['cust_zip']);
590  }
591  if (!empty($arrayquery['cust_city'])) {
592  $sqlwhere[]= $this->transformToSQL('t.town',$arrayquery['cust_city']);
593  }
594  if (!empty($arrayquery['cust_mothercompany'])) {
595  $str=$this->transformToSQL('nom',$arrayquery['cust_mothercompany']);
596  $sqlwhere[]= " (t.parent IN (SELECT rowid FROM " . MAIN_DB_PREFIX . "societe WHERE (".$str.")))";
597  }
598  if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status'])>0) {
599  $sqlwhere[]= " (t.status IN (".implode(',',$arrayquery['cust_status'])."))";
600  }
601  if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust'])>0) {
602  $sqlwhere[]= " (t.client IN (".implode(',',$arrayquery['cust_typecust'])."))";
603  }
604  if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']>0)) {
605  $sqlwhere[]= " (t.fk_stcomm IN (".implode(',',$arrayquery['cust_comm_status'])."))";
606  }
607  if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status'])>0) {
608  $sqlwhere[]= " (t.fk_prospectlevel IN ('".implode("','",$arrayquery['cust_prospect_status'])."'))";
609  }
610  if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent'])>0) {
611  $sqlwhere[]= " (t.fk_typent IN (".implode(',',$arrayquery['cust_typeent'])."))";
612  }
613  if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman'])>0) {
614  $sqlwhere[]= " (saleman.fk_user IN (".implode(',',$arrayquery['cust_saleman'])."))";
615  }
616  if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country'])>0) {
617  $sqlwhere[]= " (t.fk_pays IN (".implode(',',$arrayquery['cust_country'])."))";
618  }
619  if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id'])>0) {
620  $sqlwhere[]= " (t.fk_effectif IN (".implode(',',$arrayquery['cust_effectif_id'])."))";
621  }
622  if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ'])>0) {
623  $sqlwhere[]= " (custcateg.fk_categorie IN (".implode(',',$arrayquery['cust_categ'])."))";
624  }
625  if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language'])>0) {
626  $sqlwhere[]= " (t.default_lang IN ('".implode("','",$arrayquery['cust_language'])."'))";
627  }
628 
629  //Standard Extrafield feature
630  if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
631  // fetch optionals attributes and labels
632  dol_include_once('/core/class/extrafields.class.php');
633  $extrafields = new ExtraFields($this->db);
634  $extralabels=$extrafields->fetch_name_optionals_label('societe');
635 
636  foreach($extralabels as $key=>$val) {
637 
638  if (($extrafields->attribute_type[$key] == 'varchar') ||
639  ($extrafields->attribute_type[$key] == 'text')) {
640  if (!empty($arrayquery['options_'.$key])) {
641  $sqlwhere[]= " (te.".$key." LIKE '".$arrayquery['options_'.$key]."')";
642  }
643  } elseif (($extrafields->attribute_type[$key] == 'int') ||
644  ($extrafields->attribute_type[$key] == 'double')) {
645  if (!empty($arrayquery['options_'.$key.'_max'])) {
646  $sqlwhere[]= " (te.".$key." >= ".$arrayquery['options_'.$key.'_max']." AND te.".$key." <= ".$arrayquery['options_'.$key.'_min'].")";
647  }
648  } elseif (($extrafields->attribute_type[$key] == 'date') ||
649  ($extrafields->attribute_type[$key] == 'datetime')) {
650  if (!empty($arrayquery['options_'.$key.'_end_dt'])){
651  $sqlwhere[]= " (te.".$key." >= '".$this->db->idate($arrayquery['options_'.$key.'_st_dt'])."' AND te.".$key." <= '".$this->db->idate($arrayquery['options_'.$key.'_end_dt'])."')";
652  }
653  } elseif ($extrafields->attribute_type[$key] == 'boolean') {
654  if ($arrayquery['options_'.$key]!=''){
655  $sqlwhere[]= " (te.".$key." = ".$arrayquery['options_'.$key].")";
656  }
657  } else {
658  if (is_array($arrayquery['options_'.$key])) {
659  $sqlwhere[]= " (te.".$key." IN ('".implode("','",$arrayquery['options_'.$key])."'))";
660  } elseif (!empty($arrayquery['options_'.$key])) {
661  $sqlwhere[]= " (te.".$key." LIKE '".$arrayquery['options_'.$key]."')";
662  }
663  }
664  }
665  }
666 
667  if (count($sqlwhere)>0) $sql.= " WHERE ".implode(" AND ",$sqlwhere);
668  }
669 
670 
671  dol_syslog(get_class($this) . "::query_thirdparty sql=" . $sql, LOG_DEBUG);
672  $resql = $this->db->query($sql);
673  if ($resql) {
674  $this->thirdparty_lines = array();
675  $num = $this->db->num_rows($resql);
676  $i = 0;
677 
678  if ($num)
679  {
680  while( $i < $num)
681  {
682  $obj = $this->db->fetch_object($resql);
683 
684  $this->thirdparty_lines[$i] = $obj->rowid;
685 
686  $i++;
687  }
688  }
689  $this->db->free($resql);
690 
691  return $num;
692  } else {
693  $this->error = "Error " . $this->db->lasterror();
694  dol_syslog(get_class($this) . "::query_thirdparty " . $this->error, LOG_ERR);
695  return -1;
696  }
697  }
698 
699  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
707  function query_contact($arrayquery, $withThirdpartyFilter = 0)
708  {
709  // phpcs:enable
710  global $langs,$conf;
711 
712  $sql = "SELECT";
713  $sql.= " t.rowid";
714  $sql.= " FROM " . MAIN_DB_PREFIX . "socpeople as t";
715  $sql.= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "socpeople_extrafields as te ON te.fk_object=t.rowid ";
716 
717  if (! empty($withThirdpartyFilter)) {
718  $sql .= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "societe as ts ON ts.rowid=t.fk_soc";
719  $sql .= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "societe_extrafields as tse ON tse.fk_object=ts.rowid ";
720  }
721 
722  $sqlwhere=array();
723 
724  $sqlwhere[]= 't.entity IN ('.getEntity('socpeople').')';
725 
726  if (count($arrayquery)>0) {
727 
728  if (array_key_exists('contact_categ', $arrayquery)) {
729  $sql.= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "categorie_contact as contactcateg ON contactcateg.fk_socpeople=t.rowid ";
730  }
731 
732  if (!empty($arrayquery['contact_lastname'])) {
733  $sqlwhere[]=$this->transformToSQL('t.lastname',$arrayquery['contact_lastname']);
734  }
735  if (!empty($arrayquery['contact_firstname'])) {
736  $sqlwhere[]=$this->transformToSQL('t.firstname',$arrayquery['contact_firstname']);
737  }
738  if (!empty($arrayquery['contact_country']) && count($arrayquery['contact_country'])) {
739  $sqlwhere[]= " (t.fk_pays IN (".$this->db->escape(implode(',',$arrayquery['contact_country']))."))";
740  }
741  if (!empty($arrayquery['contact_status']) && count($arrayquery['contact_status'])>0) {
742  $sqlwhere[]= " (t.statut IN (".$this->db->escape(implode(',',$arrayquery['contact_status']))."))";
743  }
744  if (!empty($arrayquery['contact_civility']) && count($arrayquery['contact_civility'])>0) {
745  $sqlwhere[]= " (t.civility IN ('".$this->db->escape(implode("','",$arrayquery['contact_civility']))."'))";
746  }
747  if ($arrayquery['contact_no_email']!='') {
748  $sqlwhere[]= " (t.no_email='".$this->db->escape($arrayquery['contact_no_email'])."')";
749  }
750  if ($arrayquery['contact_update_st_dt']!='') {
751  $sqlwhere[]= " (t.tms >= '".$this->db->idate($arrayquery['contact_update_st_dt'])."' AND t.tms <= '".$this->db->idate($arrayquery['contact_update_end_dt'])."')";
752  }
753  if ($arrayquery['contact_create_st_dt']!='') {
754  $sqlwhere[]= " (t.datec >= '".$this->db->idate($arrayquery['contact_create_st_dt'])."' AND t.datec <= '".$this->db->idate($arrayquery['contact_create_end_dt'])."')";
755  }
756  if (!empty($arrayquery['contact_categ']) && count($arrayquery['contact_categ'])>0) {
757  $sqlwhere[]= " (contactcateg.fk_categorie IN (".$this->db->escape(implode(",",$arrayquery['contact_categ']))."))";
758  }
759 
760  //Standard Extrafield feature
761  if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
762  // fetch optionals attributes and labels
763  dol_include_once('/core/class/extrafields.class.php');
764  $extrafields = new ExtraFields($this->db);
765  $extralabels=$extrafields->fetch_name_optionals_label('socpeople');
766 
767  foreach($extralabels as $key=>$val) {
768 
769  if (($extrafields->attribute_type[$key] == 'varchar') ||
770  ($extrafields->attribute_type[$key] == 'text')) {
771  if (!empty($arrayquery['options_'.$key.'_cnct'])) {
772  $sqlwhere[]= " (te.".$key." LIKE '".$arrayquery['options_'.$key.'_cnct']."')";
773  }
774  } elseif (($extrafields->attribute_type[$key] == 'int') ||
775  ($extrafields->attribute_type[$key] == 'double')) {
776  if (!empty($arrayquery['options_'.$key.'_max'.'_cnct'])) {
777  $sqlwhere[]= " (te.".$key." >= ".$arrayquery['options_'.$key.'_max'.'_cnct']." AND te.".$key." <= ".$arrayquery['options_'.$key.'_min'.'_cnct'].")";
778  }
779  } else if (($extrafields->attribute_type[$key] == 'date') ||
780  ($extrafields->attribute_type[$key] == 'datetime')) {
781  if (!empty($arrayquery['options_'.$key.'_end_dt'.'_cnct'])){
782  $sqlwhere[]= " (te.".$key." >= '".$this->db->idate($arrayquery['options_'.$key.'_st_dt'.'_cnct'])."' AND te.".$key." <= '".$this->db->idate($arrayquery['options_'.$key.'_end_dt'.'_cnct'])."')";
783  }
784  }else if ($extrafields->attribute_type[$key] == 'boolean') {
785  if ($arrayquery['options_'.$key.'_cnct']!=''){
786  if ($arrayquery['options_'.$key.'_cnct']==0) {
787  $sqlwhere[]= " (te.".$key." = ".$arrayquery['options_'.$key.'_cnct']." OR ((te.".$key." IS NULL) AND (te.fk_object IS NOT NULL)))";
788  }else {
789  $sqlwhere[]= " (te.".$key." = ".$arrayquery['options_'.$key.'_cnct'].")";
790  }
791  }
792  }else{
793  if (is_array($arrayquery['options_'.$key.'_cnct'])) {
794  $sqlwhere[]= " (te.".$key." IN ('".implode("','",$arrayquery['options_'.$key.'_cnct'])."'))";
795  } elseif (!empty($arrayquery['options_'.$key.'_cnct'])) {
796  $sqlwhere[]= " (te.".$key." LIKE '".$arrayquery['options_'.$key.'_cnct']."')";
797  }
798  }
799  }
800 
801  if (! empty($withThirdpartyFilter)) {
802  if (array_key_exists('cust_saleman', $arrayquery)) {
803  $sql.= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as saleman ON saleman.fk_soc=ts.rowid ";
804  }
805  if (array_key_exists('cust_categ', $arrayquery)) {
806  $sql.= " LEFT OUTER JOIN " . MAIN_DB_PREFIX . "categorie_societe as custcateg ON custcateg.fk_soc=ts.rowid ";
807  }
808 
809  if (!empty($arrayquery['cust_name'])) {
810 
811  $sqlwhere[]= $this->transformToSQL('ts.nom',$arrayquery['cust_name']);
812  }
813  if (!empty($arrayquery['cust_code'])) {
814  $sqlwhere[]= $this->transformToSQL('ts.code_client',$arrayquery['cust_code']);
815  }
816  if (!empty($arrayquery['cust_adress'])) {
817  $sqlwhere[]= $this->transformToSQL('ts.address',$arrayquery['cust_adress']);
818  }
819  if (!empty($arrayquery['cust_zip'])) {
820  $sqlwhere[]= $this->transformToSQL('ts.zip',$arrayquery['cust_zip']);
821  }
822  if (!empty($arrayquery['cust_city'])) {
823  $sqlwhere[]= $this->transformToSQL('ts.town',$arrayquery['cust_city']);
824  }
825  if (!empty($arrayquery['cust_mothercompany'])) {
826  $str=$this->transformToSQL('nom',$arrayquery['cust_mothercompany']);
827  $sqlwhere[]= " (ts.parent IN (SELECT rowid FROM " . MAIN_DB_PREFIX . "societe WHERE (".$str.")))";
828  }
829  if (!empty($arrayquery['cust_status']) && count($arrayquery['cust_status'])>0) {
830  $sqlwhere[]= " (ts.status IN (".implode(',',$arrayquery['cust_status'])."))";
831  }
832  if (!empty($arrayquery['cust_typecust']) && count($arrayquery['cust_typecust'])>0) {
833  $sqlwhere[]= " (ts.client IN (".implode(',',$arrayquery['cust_typecust'])."))";
834  }
835  if (!empty($arrayquery['cust_comm_status']) && count($arrayquery['cust_comm_status']>0)) {
836  $sqlwhere[]= " (ts.fk_stcomm IN (".implode(',',$arrayquery['cust_comm_status'])."))";
837  }
838  if (!empty($arrayquery['cust_prospect_status']) && count($arrayquery['cust_prospect_status'])>0) {
839  $sqlwhere[]= " (ts.fk_prospectlevel IN ('".implode("','",$arrayquery['cust_prospect_status'])."'))";
840  }
841  if (!empty($arrayquery['cust_typeent']) && count($arrayquery['cust_typeent'])>0) {
842  $sqlwhere[]= " (ts.fk_typent IN (".implode(',',$arrayquery['cust_typeent'])."))";
843  }
844  if (!empty($arrayquery['cust_saleman']) && count($arrayquery['cust_saleman'])>0) {
845  $sqlwhere[]= " (saleman.fk_user IN (".implode(',',$arrayquery['cust_saleman'])."))";
846  }
847  if (!empty($arrayquery['cust_country']) && count($arrayquery['cust_country'])>0) {
848  $sqlwhere[]= " (ts.fk_pays IN (".implode(',',$arrayquery['cust_country'])."))";
849  }
850  if (!empty($arrayquery['cust_effectif_id']) && count($arrayquery['cust_effectif_id'])>0) {
851  $sqlwhere[]= " (ts.fk_effectif IN (".implode(',',$arrayquery['cust_effectif_id'])."))";
852  }
853  if (!empty($arrayquery['cust_categ']) && count($arrayquery['cust_categ'])>0) {
854  $sqlwhere[]= " (custcateg.fk_categorie IN (".implode(',',$arrayquery['cust_categ'])."))";
855  }
856  if (!empty($arrayquery['cust_language']) && count($arrayquery['cust_language'])>0) {
857  $sqlwhere[]= " (ts.default_lang IN ('".implode("','",$arrayquery['cust_language'])."'))";
858  }
859 
860  //Standard Extrafield feature
861  if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
862  // fetch optionals attributes and labels
863  dol_include_once('/core/class/extrafields.class.php');
864  $extrafields = new ExtraFields($this->db);
865  $extralabels=$extrafields->fetch_name_optionals_label('societe');
866 
867  foreach($extralabels as $key=>$val) {
868 
869  if (($extrafields->attribute_type[$key] == 'varchar') ||
870  ($extrafields->attribute_type[$key] == 'text')) {
871  if (!empty($arrayquery['options_'.$key])) {
872  $sqlwhere[]= " (tse.".$key." LIKE '".$arrayquery['options_'.$key]."')";
873  }
874  } elseif (($extrafields->attribute_type[$key] == 'int') ||
875  ($extrafields->attribute_type[$key] == 'double')) {
876  if (!empty($arrayquery['options_'.$key.'_max'])) {
877  $sqlwhere[]= " (tse.".$key." >= ".$arrayquery['options_'.$key.'_max']." AND tse.".$key." <= ".$arrayquery['options_'.$key.'_min'].")";
878  }
879  } else if (($extrafields->attribute_type[$key] == 'date') ||
880  ($extrafields->attribute_type[$key] == 'datetime')) {
881  if (!empty($arrayquery['options_'.$key.'_end_dt'])){
882  $sqlwhere[]= " (tse.".$key." >= '".$this->db->idate($arrayquery['options_'.$key.'_st_dt'])."' AND tse.".$key." <= '".$this->db->idate($arrayquery['options_'.$key.'_end_dt'])."')";
883  }
884  }else if ($extrafields->attribute_type[$key] == 'boolean') {
885  if ($arrayquery['options_'.$key]!=''){
886  $sqlwhere[]= " (tse.".$key." = ".$arrayquery['options_'.$key].")";
887  }
888  }else{
889  if (is_array($arrayquery['options_'.$key])) {
890  $sqlwhere[]= " (tse.".$key." IN ('".implode("','",$arrayquery['options_'.$key])."'))";
891  } elseif (!empty($arrayquery['options_'.$key])) {
892  $sqlwhere[]= " (tse.".$key." LIKE '".$arrayquery['options_'.$key]."')";
893  }
894  }
895  }
896  }
897  }
898  }
899  if (count($sqlwhere)>0) $sql.= " WHERE ".implode(" AND ",$sqlwhere);
900  }
901 
902  dol_syslog(get_class($this) . "::query_contact sql=" . $sql, LOG_DEBUG);
903  $resql = $this->db->query($sql);
904  if ($resql) {
905  $this->contact_lines = array();
906  $num = $this->db->num_rows($resql);
907  $i = 0;
908 
909  if ($num)
910  {
911  while( $i < $num)
912  {
913  $obj = $this->db->fetch_object($resql);
914 
915  $this->contact_lines[$i] = $obj->rowid;
916 
917  $i++;
918  }
919  }
920  $this->db->free($resql);
921 
922  return $num;
923  } else {
924  $this->error = "Error " . $this->db->lasterror();
925  dol_syslog(get_class($this) . "::query_contact " . $this->error, LOG_ERR);
926  return -1;
927  }
928  }
929 
930 
940  public function transformToSQL($column_to_test,$criteria)
941  {
942  $return_sql_criteria = '(';
943 
944  //This is a multiple value test
945  if (preg_match('/;/',$criteria)) {
946  $return_sql_not_like=array();
947  $return_sql_like=array();
948 
949  $criteria_array=explode(';',$criteria);
950  foreach($criteria_array as $inter_criteria) {
951  if (preg_match('/!/',$inter_criteria)) {
952  $return_sql_not_like[]= '('.$column_to_test.' NOT LIKE \''.str_replace('!', '', $inter_criteria).'\')';
953  } else {
954  $return_sql_like[]= '('.$column_to_test.' LIKE \''.$inter_criteria.'\')';
955  }
956  }
957 
958  if (count($return_sql_like)>0) {
959  $return_sql_criteria .= '(' . implode (' OR ', $return_sql_like) .')';
960  }
961  if (count($return_sql_not_like)>0) {
962  $return_sql_criteria .= ' AND (' . implode (' AND ', $return_sql_not_like).')';
963  }
964  }else {
965  $return_sql_criteria .= $column_to_test . ' LIKE \''.$this->db->escape($criteria).'\'';
966  }
967 
968  $return_sql_criteria .= ')';
969 
970  return $return_sql_criteria;
971  }
972 }
if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) if(! empty($conf->don->enabled) && $user->rights->societe->lire) if(! empty($conf->tax->enabled) && $user->rights->tax->charges->lire) if(! empty($conf->facture->enabled) &&! empty($conf->commande->enabled) && $user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) $resql
Social contributions to pay.
Definition: index.php:1053
fetch($id)
Load object in memory from the database.
query_contact($arrayquery, $withThirdpartyFilter=0)
Load object in memory from database.
fetch_by_element($id=0, $type_element='mailing')
Load object in memory from the database.
query_thirdparty($arrayquery)
Load object in memory from database.
$conf db name
Only used if Module[ID]Name translation string is not found.
Definition: repair.php:103
Class to manage standard extra fields.
savequery($user, $arrayquery)
Save query in database to retreive it.
fetch_by_mailing($id=0)
Load object in memory from the database.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
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='gmt')
Return date for now.
transformToSQL($column_to_test, $criteria)
Parse criteria to return a SQL qury formated.
Class to manage advanced emailing target selector.
Parent class of all other business classes (invoices, contracts, proposals, orders, ...)
create($user, $notrigger=0)
Create object into database.
update($user, $notrigger=0)
Update object into database.