39 public $element =
'advtargetemailing';
44 public $table_element =
'mailing_advtarget';
74 public $fk_user_author;
94 public $select_target_type = array();
99 public $type_statuscommprospect = array();
100 public $thirdparty_lines;
101 public $contact_lines;
112 $langs->load(
'customers');
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')
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'];
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")
148 public function create($user, $notrigger = 0)
150 global $conf, $langs;
154 if (isset($this->fk_element)) {
155 $this->fk_element = (int) $this->fk_element;
157 if (isset($this->type_element)) {
158 $this->type_element = trim($this->type_element);
161 if (isset($this->
name)) {
164 if (isset($this->filtervalue)) {
165 $this->filtervalue = trim($this->filtervalue);
172 $sql =
"INSERT INTO ".MAIN_DB_PREFIX.
"mailing_advtarget(";
175 $sql .=
"fk_element,";
176 $sql .=
"type_element,";
177 $sql .=
"filtervalue,";
178 $sql .=
"fk_user_author,";
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.
",";
189 $sql .=
" ".$user->id;
194 dol_syslog(get_class($this).
"::create", LOG_DEBUG);
195 $resql = $this->
db->query(
$sql);
197 $error++; $this->errors[] =
"Error ".$this->db->lasterror();
201 $this->
id = $this->
db->last_insert_id(MAIN_DB_PREFIX.
"mailing_advtarget");
206 foreach ($this->errors as $errmsg) {
207 dol_syslog(get_class($this).
"::create ".$errmsg, LOG_ERR);
208 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
210 $this->
db->rollback();
231 $sql .=
" t.entity,";
232 $sql .=
" t.fk_element,";
233 $sql .=
" t.type_element,";
234 $sql .=
" t.filtervalue,";
235 $sql .=
" t.fk_user_author,";
237 $sql .=
" t.fk_user_mod,";
240 $sql .=
" FROM ".MAIN_DB_PREFIX.
"mailing_advtarget as t";
241 $sql .=
" WHERE t.rowid = ".((int) $id);
243 dol_syslog(get_class($this).
"::fetch", LOG_DEBUG);
244 $resql = $this->
db->query(
$sql);
246 if ($this->
db->num_rows($resql)) {
247 $obj = $this->
db->fetch_object($resql);
249 $this->
id = $obj->rowid;
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);
261 $this->
db->free($resql);
265 $this->error =
"Error ".$this->db->lasterror();
266 dol_syslog(get_class($this).
"::fetch ".$this->error, LOG_ERR);
286 $sql .=
" t.entity,";
287 $sql .=
" t.fk_element,";
288 $sql .=
" t.type_element,";
289 $sql .=
" t.filtervalue,";
290 $sql .=
" t.fk_user_author,";
292 $sql .=
" t.fk_user_mod,";
295 $sql .=
" FROM ".MAIN_DB_PREFIX.
"mailing_advtarget as t";
297 $sql .=
" WHERE t.fk_element = ".((int) $id).
" AND type_element = 'mailing'";
299 $sql .=
" WHERE t.fk_element = ".((int) $this->fk_element).
" AND type_element = 'mailing'";
302 dol_syslog(get_class($this).
"::fetch", LOG_DEBUG);
303 $resql = $this->
db->query(
$sql);
305 if ($this->
db->num_rows($resql)) {
306 $obj = $this->
db->fetch_object($resql);
308 $this->
id = $obj->rowid;
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);
320 $this->
db->free($resql);
324 $this->error =
"Error ".$this->db->lasterror();
325 dol_syslog(get_class($this).
"::fetch ".$this->error, LOG_ERR);
349 $sql .=
" t.entity,";
350 $sql .=
" t.fk_element,";
351 $sql .=
" t.type_element,";
352 $sql .=
" t.filtervalue,";
353 $sql .=
" t.fk_user_author,";
355 $sql .=
" t.fk_user_mod,";
358 $sql .=
" FROM ".MAIN_DB_PREFIX.
"mailing_advtarget as t";
360 $sql .=
" WHERE t.fk_element = ".((int) $id).
" AND type_element = '".$this->
db->escape($type_element).
"'";
362 $sql .=
" WHERE t.fk_element = ".((int) $this->fk_element).
" AND type_element = '".$this->
db->escape($type_element).
"'";
365 dol_syslog(get_class($this).
"::fetch", LOG_DEBUG);
366 $resql = $this->
db->query(
$sql);
368 if ($this->
db->num_rows($resql)) {
369 $obj = $this->
db->fetch_object($resql);
371 $this->
id = $obj->rowid;
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);
383 $this->
db->free($resql);
387 $this->error =
"Error ".$this->db->lasterror();
388 dol_syslog(get_class($this).
"::fetch ".$this->error, LOG_ERR);
400 public function update($user, $notrigger = 0)
402 global $conf, $langs;
406 if (isset($this->fk_element)) {
407 $this->fk_element = (int) $this->fk_element;
409 if (isset($this->type_element)) {
410 $this->type_element = trim($this->type_element);
412 if (isset($this->
name)) {
415 if (isset($this->filtervalue)) {
416 $this->filtervalue = trim($this->filtervalue);
423 $sql =
"UPDATE ".MAIN_DB_PREFIX.
"mailing_advtarget SET";
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;
432 $sql .=
" WHERE rowid=".((int) $this->
id);
435 dol_syslog(get_class($this).
"::update", LOG_DEBUG);
436 $resql = $this->
db->query(
$sql);
439 $this->errors[] =
"Error ".$this->db->lasterror();
444 foreach ($this->errors as $errmsg) {
445 dol_syslog(get_class($this).
"::update ".$errmsg, LOG_ERR);
446 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
448 $this->
db->rollback();
463 public function delete($user, $notrigger = 0)
465 global $conf, $langs;
471 $sql =
"DELETE FROM ".MAIN_DB_PREFIX.
"mailing_advtarget";
472 $sql .=
" WHERE rowid=".((int) $this->
id);
475 $resql = $this->
db->query($sql);
477 $error++; $this->errors[] =
"Error ".$this->db->lasterror();
483 foreach ($this->errors as $errmsg) {
484 dol_syslog(get_class($this).
"::delete ".$errmsg, LOG_ERR);
485 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
487 $this->
db->rollback();
505 global $langs, $conf;
507 if (!empty($arrayquery)) {
509 $this->filtervalue = json_encode($arrayquery);
513 if (!empty($this->
id)) {
533 global $langs, $conf, $extrafields;
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 ";
542 $sqlwhere[] =
't.entity IN ('.getEntity(
'societe').
')';
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 ";
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 ";
552 if (!empty($arrayquery[
'cust_name'])) {
553 $sqlwhere[] = $this->
transformToSQL(
't.nom', $arrayquery[
'cust_name']);
555 if (!empty($arrayquery[
'cust_code'])) {
556 $sqlwhere[] = $this->
transformToSQL(
't.code_client', $arrayquery[
'cust_code']);
558 if (!empty($arrayquery[
'cust_adress'])) {
559 $sqlwhere[] = $this->
transformToSQL(
't.address', $arrayquery[
'cust_adress']);
561 if (!empty($arrayquery[
'cust_zip'])) {
562 $sqlwhere[] = $this->
transformToSQL(
't.zip', $arrayquery[
'cust_zip']);
564 if (!empty($arrayquery[
'cust_city'])) {
565 $sqlwhere[] = $this->
transformToSQL(
't.town', $arrayquery[
'cust_city']);
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.
")))";
571 if (!empty($arrayquery[
'cust_status']) && count($arrayquery[
'cust_status']) > 0) {
572 $sqlwhere[] =
" (t.status IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_status'])).
"))";
574 if (!empty($arrayquery[
'cust_typecust']) && count($arrayquery[
'cust_typecust']) > 0) {
575 $sqlwhere[] =
" (t.client IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typecust'])).
"))";
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'])).
"))";
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).
"))";
583 if (!empty($arrayquery[
'cust_typeent']) && count($arrayquery[
'cust_typeent']) > 0) {
584 $sqlwhere[] =
" (t.fk_typent IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typeent'])).
"))";
586 if (!empty($arrayquery[
'cust_saleman']) && count($arrayquery[
'cust_saleman']) > 0) {
587 $sqlwhere[] =
" (saleman.fk_user IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_saleman'])).
"))";
589 if (!empty($arrayquery[
'cust_country']) && count($arrayquery[
'cust_country']) > 0) {
590 $sqlwhere[] =
" (t.fk_pays IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_country'])).
"))";
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'])).
"))";
595 if (!empty($arrayquery[
'cust_categ']) && count($arrayquery[
'cust_categ']) > 0) {
596 $sqlwhere[] =
" (custcateg.fk_categorie IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_categ'])).
"))";
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).
"))";
603 if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
605 $elementtype = $socstatic->table_element;
607 $extrafields->fetch_name_optionals_label($elementtype);
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]).
"')";
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']).
")";
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"]).
"')";
625 } elseif ($extrafields->attributes[$elementtype][
'type'][$key] ==
'boolean') {
626 if ($arrayquery[
'options_'.$key] !=
'') {
627 $sqlwhere[] =
" (te.".$key.
" = ".((int) $arrayquery[
"options_".$key]).
")";
629 } elseif ($extrafields->attributes[$elementtype][
'type'][$key] ==
'link') {
630 if ($arrayquery[
'options_'.$key] > 0) {
631 $sqlwhere[]=
" (te.".$key.
" = ".((int) $arrayquery[
"options_".$key]).
")";
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]).
"')";
643 if (count($sqlwhere) > 0) {
644 $sql .=
" WHERE ".implode(
" AND ", $sqlwhere);
649 dol_syslog(get_class($this).
"::query_thirdparty", LOG_DEBUG);
650 $resql = $this->
db->query(
$sql);
652 $this->thirdparty_lines = array();
653 $num = $this->
db->num_rows($resql);
658 $obj = $this->
db->fetch_object($resql);
659 $this->thirdparty_lines[$i] = $obj->rowid;
664 $this->
db->free($resql);
668 $this->error =
"Error ".$this->db->lasterror();
669 dol_syslog(get_class($this).
"::query_thirdparty ".$this->error, LOG_ERR);
685 global $langs, $conf;
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 ";
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 ";
699 $sqlwhere[] =
't.entity IN ('.getEntity(
'contact').
')';
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 ";
706 if (!empty($arrayquery[
'contact_lastname'])) {
707 $sqlwhere[] = $this->
transformToSQL(
't.lastname', $arrayquery[
'contact_lastname']);
709 if (!empty($arrayquery[
'contact_firstname'])) {
710 $sqlwhere[] = $this->
transformToSQL(
't.firstname', $arrayquery[
'contact_firstname']);
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']))).
"))";
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']))).
"))";
718 if (!empty($arrayquery[
'contact_civility']) && count($arrayquery[
'contact_civility']) > 0) {
719 $sqlwhere[] =
" (t.civility IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
'contact_civility']).
"'", 1).
"))";
721 if ($arrayquery[
'contact_no_email'] !=
'') {
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']).
"'))";
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']).
"'))";
728 $sqlwhere[] = $tmpwhere;
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']).
"')";
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']).
"')";
736 if (!empty($arrayquery[
'contact_categ']) && count($arrayquery[
'contact_categ']) > 0) {
737 $sqlwhere[] =
" (contactcateg.fk_categorie IN (".$this->db->escape(implode(
",", $arrayquery[
'contact_categ'])).
"))";
741 if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
743 $elementtype = $contactstatic->table_element;
748 $extrafields->fetch_name_optionals_label($elementtype);
750 $extrafields->fetch_name_optionals_label($elementtype);
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"]).
"')";
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"]).
")";
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"]).
"')";
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)))";
773 $sqlwhere[] =
" (te.".$key.
" = ".((int) $arrayquery[
"options_".$key.
"_cnct"]).
")";
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"]).
"')";
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 ";
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 ";
793 if (!empty($arrayquery[
'cust_name'])) {
794 $sqlwhere[] = $this->
transformToSQL(
'ts.nom', $arrayquery[
'cust_name']);
796 if (!empty($arrayquery[
'cust_code'])) {
797 $sqlwhere[] = $this->
transformToSQL(
'ts.code_client', $arrayquery[
'cust_code']);
799 if (!empty($arrayquery[
'cust_adress'])) {
800 $sqlwhere[] = $this->
transformToSQL(
'ts.address', $arrayquery[
'cust_adress']);
802 if (!empty($arrayquery[
'cust_zip'])) {
803 $sqlwhere[] = $this->
transformToSQL(
'ts.zip', $arrayquery[
'cust_zip']);
805 if (!empty($arrayquery[
'cust_city'])) {
806 $sqlwhere[] = $this->
transformToSQL(
'ts.town', $arrayquery[
'cust_city']);
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.
")))";
812 if (!empty($arrayquery[
'cust_status']) && count($arrayquery[
'cust_status']) > 0) {
813 $sqlwhere[] =
" (ts.status IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_status'])).
"))";
815 if (!empty($arrayquery[
'cust_typecust']) && count($arrayquery[
'cust_typecust']) > 0) {
816 $sqlwhere[] =
" (ts.client IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typecust'])).
"))";
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'])).
"))";
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'])).
"'))";
824 if (!empty($arrayquery[
'cust_typeent']) && count($arrayquery[
'cust_typeent']) > 0) {
825 $sqlwhere[] =
" (ts.fk_typent IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typeent'])).
"))";
827 if (!empty($arrayquery[
'cust_saleman']) && count($arrayquery[
'cust_saleman']) > 0) {
828 $sqlwhere[] =
" (saleman.fk_user IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_saleman'])).
"))";
830 if (!empty($arrayquery[
'cust_country']) && count($arrayquery[
'cust_country']) > 0) {
831 $sqlwhere[] =
" (ts.fk_pays IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_country'])).
"))";
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'])).
"))";
836 if (!empty($arrayquery[
'cust_categ']) && count($arrayquery[
'cust_categ']) > 0) {
837 $sqlwhere[] =
" (custcateg.fk_categorie IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_categ'])).
"))";
839 if (!empty($arrayquery[
'cust_language']) && count($arrayquery[
'cust_language']) > 0) {
840 $sqlwhere[] =
" (ts.default_lang IN ('".$this->db->sanitize(implode(
"','", $arrayquery[
'cust_language'])).
"'))";
844 if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
846 $elementtype = $socstatic->table_element;
852 $extrafields->fetch_name_optionals_label($elementtype);
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]).
"')";
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"]).
")";
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"]).
"')";
870 } elseif ($extrafields->attributes[$elementtype][
'type'][$key] ==
'boolean') {
871 if ($arrayquery[
'options_'.$key] !=
'') {
872 $sqlwhere[] =
" (tse.".$key.
" = ".((int) $arrayquery[
"options_".$key]).
")";
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]).
"')";
885 if (count($sqlwhere) > 0) {
886 $sql .=
" WHERE ".implode(
" AND ", $sqlwhere);
890 dol_syslog(get_class($this).
"::query_contact", LOG_DEBUG);
891 $resql = $this->
db->query(
$sql);
893 $this->contact_lines = array();
894 $num = $this->
db->num_rows($resql);
899 $obj = $this->
db->fetch_object($resql);
901 $this->contact_lines[$i] = $obj->rowid;
906 $this->
db->free($resql);
910 $this->error =
"Error ".$this->db->lasterror();
911 dol_syslog(get_class($this).
"::query_contact ".$this->error, LOG_ERR);
928 $return_sql_criteria =
'(';
931 if (preg_match(
'/;/', $criteria)) {
932 $return_sql_not_like = array();
933 $return_sql_like = array();
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).
'\')
';
940 $return_sql_like[] = '(
'.$column_to_test.' LIKE \
''.$inter_criteria.
'\')
';
944 if (count($return_sql_like) > 0) {
945 $return_sql_criteria .= '(
'.implode(' OR
', $return_sql_like).')
';
947 if (count($return_sql_not_like) > 0) {
948 $return_sql_criteria .= ' AND (
'.implode(' AND
', $return_sql_not_like).')
';
951 $return_sql_criteria .= $column_to_test.' LIKE \
''.$this->db->escape($criteria).
'\'';
954 $return_sql_criteria .=
')';
956 return $return_sql_criteria;