143 public function create($user, $notrigger = 0)
145 global $conf, $langs;
149 if (isset($this->fk_element)) {
150 $this->fk_element = (int) $this->fk_element;
152 if (isset($this->type_element)) {
153 $this->type_element = trim($this->type_element);
156 if (isset($this->
name)) {
159 if (isset($this->filtervalue)) {
160 $this->filtervalue = trim($this->filtervalue);
167 $sql =
"INSERT INTO ".MAIN_DB_PREFIX.
"mailing_advtarget(";
170 $sql .=
"fk_element,";
171 $sql .=
"type_element,";
172 $sql .=
"filtervalue,";
173 $sql .=
"fk_user_author,";
175 $sql .=
"fk_user_mod";
176 $sql .=
") VALUES (";
177 $sql .=
" ".(!isset($this->
name) ?
'NULL' :
"'".$this->db->escape($this->
name).
"'").
",";
178 $sql .=
" ".$conf->entity.
",";
179 $sql .=
" ".(!isset($this->fk_element) ?
'NULL' :
"'".$this->db->escape($this->fk_element).
"'").
",";
180 $sql .=
" ".(!isset($this->type_element) ?
'NULL' :
"'".$this->db->escape($this->type_element).
"'").
",";
181 $sql .=
" ".(!isset($this->filtervalue) ?
'NULL' :
"'".$this->db->escape($this->filtervalue).
"'").
",";
182 $sql .=
" ".$user->id.
",";
183 $sql .=
" '".$this->db->idate(
dol_now()).
"',";
184 $sql .=
" ".$user->id;
189 dol_syslog(get_class($this).
"::create", LOG_DEBUG);
190 $resql = $this->db->query($sql);
193 $this->errors[] =
"Error ".$this->db->lasterror();
197 $this->
id = $this->db->last_insert_id(MAIN_DB_PREFIX.
"mailing_advtarget");
202 foreach ($this->errors as $errmsg) {
203 dol_syslog(get_class($this).
"::create ".$errmsg, LOG_ERR);
204 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
206 $this->db->rollback();
227 $sql .=
" t.entity,";
228 $sql .=
" t.fk_element,";
229 $sql .=
" t.type_element,";
230 $sql .=
" t.filtervalue,";
231 $sql .=
" t.fk_user_author,";
233 $sql .=
" t.fk_user_mod,";
236 $sql .=
" FROM ".MAIN_DB_PREFIX.
"mailing_advtarget as t";
237 $sql .=
" WHERE t.rowid = ".((int) $id);
239 dol_syslog(get_class($this).
"::fetch", LOG_DEBUG);
240 $resql = $this->db->query($sql);
242 if ($this->db->num_rows($resql)) {
243 $obj = $this->db->fetch_object($resql);
245 $this->
id = $obj->rowid;
247 $this->
name = $obj->name;
248 $this->entity = $obj->entity;
249 $this->fk_element = $obj->fk_element;
250 $this->type_element = $obj->type_element;
251 $this->filtervalue = $obj->filtervalue;
252 $this->fk_user_author = $obj->fk_user_author;
253 $this->datec = $this->db->jdate($obj->datec);
254 $this->fk_user_mod = $obj->fk_user_mod;
255 $this->tms = $this->db->jdate($obj->tms);
257 $this->db->free($resql);
261 $this->error =
"Error ".$this->db->lasterror();
262 dol_syslog(get_class($this).
"::fetch ".$this->error, LOG_ERR);
282 $sql .=
" t.entity,";
283 $sql .=
" t.fk_element,";
284 $sql .=
" t.type_element,";
285 $sql .=
" t.filtervalue,";
286 $sql .=
" t.fk_user_author,";
288 $sql .=
" t.fk_user_mod,";
291 $sql .=
" FROM ".MAIN_DB_PREFIX.
"mailing_advtarget as t";
293 $sql .=
" WHERE t.fk_element = ".((int) $id).
" AND type_element = 'mailing'";
295 $sql .=
" WHERE t.fk_element = ".((int) $this->fk_element).
" AND type_element = 'mailing'";
298 dol_syslog(get_class($this).
"::fetch", LOG_DEBUG);
299 $resql = $this->db->query($sql);
301 if ($this->db->num_rows($resql)) {
302 $obj = $this->db->fetch_object($resql);
304 $this->
id = $obj->rowid;
306 $this->
name = $obj->name;
307 $this->entity = $obj->entity;
308 $this->fk_element = $obj->fk_element;
309 $this->type_element = $obj->type_element;
310 $this->filtervalue = $obj->filtervalue;
311 $this->fk_user_author = $obj->fk_user_author;
312 $this->datec = $this->db->jdate($obj->datec);
313 $this->fk_user_mod = $obj->fk_user_mod;
314 $this->tms = $this->db->jdate($obj->tms);
316 $this->db->free($resql);
320 $this->error =
"Error ".$this->db->lasterror();
321 dol_syslog(get_class($this).
"::fetch ".$this->error, LOG_ERR);
345 $sql .=
" t.entity,";
346 $sql .=
" t.fk_element,";
347 $sql .=
" t.type_element,";
348 $sql .=
" t.filtervalue,";
349 $sql .=
" t.fk_user_author,";
351 $sql .=
" t.fk_user_mod,";
354 $sql .=
" FROM ".MAIN_DB_PREFIX.
"mailing_advtarget as t";
356 $sql .=
" WHERE t.fk_element = ".((int) $id).
" AND type_element = '".$this->db->escape($type_element).
"'";
358 $sql .=
" WHERE t.fk_element = ".((int) $this->fk_element).
" AND type_element = '".$this->db->escape($type_element).
"'";
361 dol_syslog(get_class($this).
"::fetch", LOG_DEBUG);
362 $resql = $this->db->query($sql);
364 if ($this->db->num_rows($resql)) {
365 $obj = $this->db->fetch_object($resql);
367 $this->
id = $obj->rowid;
369 $this->
name = $obj->name;
370 $this->entity = $obj->entity;
371 $this->fk_element = $obj->fk_element;
372 $this->type_element = $obj->type_element;
373 $this->filtervalue = $obj->filtervalue;
374 $this->fk_user_author = $obj->fk_user_author;
375 $this->datec = $this->db->jdate($obj->datec);
376 $this->fk_user_mod = $obj->fk_user_mod;
377 $this->tms = $this->db->jdate($obj->tms);
379 $this->db->free($resql);
383 $this->error =
"Error ".$this->db->lasterror();
384 dol_syslog(get_class($this).
"::fetch ".$this->error, LOG_ERR);
396 public function update($user, $notrigger = 0)
398 global $conf, $langs;
402 if (isset($this->fk_element)) {
403 $this->fk_element = (int) $this->fk_element;
405 if (isset($this->type_element)) {
406 $this->type_element = trim($this->type_element);
408 if (isset($this->
name)) {
411 if (isset($this->filtervalue)) {
412 $this->filtervalue = trim($this->filtervalue);
419 $sql =
"UPDATE ".MAIN_DB_PREFIX.
"mailing_advtarget SET";
421 $sql .=
" name=".(isset($this->
name) ?
"'".$this->db->escape($this->
name).
"'" :
"''").
",";
422 $sql .=
" entity=".$conf->entity.
",";
423 $sql .=
" fk_element=".(isset($this->fk_element) ? $this->fk_element :
"null").
",";
424 $sql .=
" type_element=".(isset($this->type_element) ?
"'".$this->db->escape($this->type_element).
"'" :
"null").
",";
425 $sql .=
" filtervalue=".(isset($this->filtervalue) ?
"'".$this->db->escape($this->filtervalue).
"'" :
"null").
",";
426 $sql .=
" fk_user_mod=".$user->id;
428 $sql .=
" WHERE rowid=".((int) $this->
id);
431 dol_syslog(get_class($this).
"::update", LOG_DEBUG);
432 $resql = $this->db->query($sql);
435 $this->errors[] =
"Error ".$this->db->lasterror();
440 foreach ($this->errors as $errmsg) {
441 dol_syslog(get_class($this).
"::update ".$errmsg, LOG_ERR);
442 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
444 $this->db->rollback();
531 global $langs, $conf, $extrafields;
535 $sql .=
" FROM ".MAIN_DB_PREFIX.
"societe as t";
536 $sql .=
" LEFT OUTER JOIN ".MAIN_DB_PREFIX.
"societe_extrafields as te ON te.fk_object=t.rowid ";
540 $sqlwhere[] =
't.entity IN ('.getEntity(
'societe').
')';
542 if (count($arrayquery) > 0) {
543 if (array_key_exists(
'cust_saleman', $arrayquery)) {
544 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as saleman ON saleman.fk_soc = t.rowid";
546 if (array_key_exists(
'cust_categ', $arrayquery)) {
547 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"categorie_societe as custcateg ON custcateg.fk_soc = t.rowid";
550 if (!empty($arrayquery[
'cust_name'])) {
551 $sqlwhere[] = $this->
transformToSQL(
't.nom', $arrayquery[
'cust_name']);
553 if (!empty($arrayquery[
'cust_code'])) {
554 $sqlwhere[] = $this->
transformToSQL(
't.code_client', $arrayquery[
'cust_code']);
556 if (!empty($arrayquery[
'cust_adress'])) {
557 $sqlwhere[] = $this->
transformToSQL(
't.address', $arrayquery[
'cust_adress']);
559 if (!empty($arrayquery[
'cust_zip'])) {
560 $sqlwhere[] = $this->
transformToSQL(
't.zip', $arrayquery[
'cust_zip']);
562 if (!empty($arrayquery[
'cust_city'])) {
563 $sqlwhere[] = $this->
transformToSQL(
't.town', $arrayquery[
'cust_city']);
565 if (!empty($arrayquery[
'cust_mothercompany'])) {
566 $str = $this->
transformToSQL(
'nom', $arrayquery[
'cust_mothercompany']);
567 $sqlwhere[] =
" (t.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX.
"societe WHERE (".$str.
")))";
569 if (!empty($arrayquery[
'cust_status']) && count($arrayquery[
'cust_status']) > 0) {
570 $sqlwhere[] =
" (t.status IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_status'])).
"))";
572 if (!empty($arrayquery[
'cust_typecust']) && count($arrayquery[
'cust_typecust']) > 0) {
573 $sqlwhere[] =
" (t.client IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typecust'])).
"))";
575 if (!empty($arrayquery[
'cust_comm_status']) && count($arrayquery[
'cust_comm_status']) > 0) {
576 $sqlwhere[] =
" (t.fk_stcomm IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_comm_status'])).
"))";
578 if (!empty($arrayquery[
'cust_prospect_status']) && count($arrayquery[
'cust_prospect_status']) > 0) {
579 $sqlwhere[] =
" (t.fk_prospectlevel IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
'cust_prospect_status']).
"'", 1).
"))";
581 if (!empty($arrayquery[
'cust_typeent']) && count($arrayquery[
'cust_typeent']) > 0) {
582 $sqlwhere[] =
" (t.fk_typent IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typeent'])).
"))";
584 if (!empty($arrayquery[
'cust_saleman']) && count($arrayquery[
'cust_saleman']) > 0) {
585 $sqlwhere[] =
" (saleman.fk_user IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_saleman'])).
"))";
587 if (!empty($arrayquery[
'cust_state']) && count($arrayquery[
'cust_state']) > 0) {
588 $sqlwhere[] =
" (t.fk_departement IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_state'])).
"))";
590 if (!empty($arrayquery[
'cust_country']) && count($arrayquery[
'cust_country']) > 0) {
591 $sqlwhere[] =
" (t.fk_pays IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_country'])).
"))";
593 if (!empty($arrayquery[
'cust_effectif_id']) && count($arrayquery[
'cust_effectif_id']) > 0) {
594 $sqlwhere[] =
" (t.fk_effectif IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_effectif_id'])).
"))";
596 if (!empty($arrayquery[
'cust_categ']) && count($arrayquery[
'cust_categ']) > 0) {
597 $sqlwhere[] =
" (custcateg.fk_categorie IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_categ'])).
"))";
599 if (!empty($arrayquery[
'cust_language']) && count($arrayquery[
'cust_language']) > 0) {
600 $sqlwhere[] =
" (t.default_lang IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
'cust_language']).
"'", 1).
"))";
605 $socstatic =
new Societe($this->db);
606 $elementtype = $socstatic->table_element;
608 $extrafields->fetch_name_optionals_label($elementtype);
610 foreach ($extrafields->attributes[$elementtype][
'label'] as $key => $val) {
611 if (($extrafields->attributes[$elementtype][
'type'][$key] ==
'varchar') ||
612 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'text')) {
613 if (!empty($arrayquery[
'options_'.$key])) {
614 $sqlwhere[] =
" (te.".$key.
" LIKE '".$this->db->escape($arrayquery[
"options_".$key]).
"')";
616 } elseif (($extrafields->attributes[$elementtype][
'type'][$key] ==
'int') ||
617 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'double')) {
618 if (!empty($arrayquery[
'options_'.$key.
'_max'])) {
619 $sqlwhere[] =
" (te.".$key.
" >= ".((float) $arrayquery[
"options_".$key.
"_max"]).
" AND te.".$key.
" <= ".((float) $arrayquery[
"options_".$key.
'_min']).
")";
621 } elseif (($extrafields->attributes[$elementtype][
'type'][$key] ==
'date') ||
622 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'datetime')) {
623 if (!empty($arrayquery[
'options_'.$key.
'_end_dt'])) {
624 $sqlwhere[] =
" (te.".$key.
" >= '".$this->db->idate($arrayquery[
"options_".$key.
"_st_dt"]).
"' AND te.".$key.
" <= '".$this->db->idate($arrayquery[
"options_".$key.
"_end_dt"]).
"')";
626 } elseif ($extrafields->attributes[$elementtype][
'type'][$key] ==
'boolean') {
627 if ($arrayquery[
'options_'.$key] !=
'') {
628 $sqlwhere[] =
" (te.".$key.
" = ".((int) $arrayquery[
"options_".$key]).
")";
630 } elseif ($extrafields->attributes[$elementtype][
'type'][$key] ==
'link') {
631 if ($arrayquery[
'options_'.$key] > 0) {
632 $sqlwhere[]=
" (te.".$key.
" = ".((int) $arrayquery[
"options_".$key]).
")";
635 if (is_array($arrayquery[
'options_'.$key])) {
636 $sqlwhere[] =
" (te.".$key.
" IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
"options_".$key]).
"'", 1).
"))";
637 } elseif (!empty($arrayquery[
'options_'.$key])) {
638 $sqlwhere[] =
" (te.".$key.
" LIKE '".$this->db->escape($arrayquery[
"options_".$key]).
"')";
644 if (count($sqlwhere) > 0) {
645 $sql .=
" WHERE ".implode(
" AND ", $sqlwhere);
650 dol_syslog(get_class($this).
"::query_thirdparty", LOG_DEBUG);
651 $resql = $this->db->query($sql);
653 $this->thirdparty_lines = array();
654 $num = $this->db->num_rows($resql);
659 $obj = $this->db->fetch_object($resql);
660 $this->thirdparty_lines[$i] = $obj->rowid;
665 $this->db->free($resql);
669 $this->error =
"Error ".$this->db->lasterror();
670 dol_syslog(get_class($this).
"::query_thirdparty ".$this->error, LOG_ERR);
686 global $langs, $conf;
690 $sql .=
" FROM ".MAIN_DB_PREFIX.
"socpeople as t";
691 $sql .=
" LEFT OUTER JOIN ".MAIN_DB_PREFIX.
"socpeople_extrafields as te ON te.fk_object=t.rowid ";
693 if (!empty($withThirdpartyFilter)) {
694 $sql .=
" LEFT OUTER JOIN ".MAIN_DB_PREFIX.
"societe as ts ON ts.rowid=t.fk_soc";
695 $sql .=
" LEFT OUTER JOIN ".MAIN_DB_PREFIX.
"societe_extrafields as tse ON tse.fk_object=ts.rowid ";
700 $sqlwhere[] =
't.entity IN ('.getEntity(
'contact').
')';
702 if (count($arrayquery) > 0) {
703 if (array_key_exists(
'contact_categ', $arrayquery)) {
704 $sql .=
" LEFT OUTER JOIN ".MAIN_DB_PREFIX.
"categorie_contact as contactcateg ON contactcateg.fk_socpeople=t.rowid ";
707 if (!empty($arrayquery[
'contact_lastname'])) {
708 $sqlwhere[] = $this->
transformToSQL(
't.lastname', $arrayquery[
'contact_lastname']);
710 if (!empty($arrayquery[
'contact_firstname'])) {
711 $sqlwhere[] = $this->
transformToSQL(
't.firstname', $arrayquery[
'contact_firstname']);
713 if (!empty($arrayquery[
'contact_country']) && count($arrayquery[
'contact_country'])) {
714 $sqlwhere[] =
" (t.fk_pays IN (".$this->db->sanitize($this->db->escape(implode(
',', $arrayquery[
'contact_country']))).
"))";
716 if (!empty($arrayquery[
'contact_status']) && count($arrayquery[
'contact_status']) > 0) {
717 $sqlwhere[] =
" (t.statut IN (".$this->db->sanitize($this->db->escape(implode(
',', $arrayquery[
'contact_status']))).
"))";
719 if (!empty($arrayquery[
'contact_civility']) && count($arrayquery[
'contact_civility']) > 0) {
720 $sqlwhere[] =
" (t.civility IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
'contact_civility']).
"'", 1).
"))";
722 if ($arrayquery[
'contact_no_email'] !=
'') {
724 if (!empty($arrayquery[
'contact_no_email'])) {
725 $tmpwhere .=
"(t.email IN (SELECT email FROM ".MAIN_DB_PREFIX.
"mailing_unsubscribe WHERE t.entity IN (".
getEntity(
'mailing').
") AND email = '".$this->db->escape($arrayquery[
'contact_no_email']).
"'))";
727 $tmpwhere .=
"(t.email NOT IN (SELECT email FROM ".MAIN_DB_PREFIX.
"mailing_unsubscribe WHERE t.entity IN (".
getEntity(
'mailing').
") AND email = '".$this->db->escape($arrayquery[
'contact_no_email']).
"'))";
729 $sqlwhere[] = $tmpwhere;
731 if ($arrayquery[
'contact_update_st_dt'] !=
'') {
732 $sqlwhere[] =
" (t.tms >= '".$this->db->idate($arrayquery[
'contact_update_st_dt']).
"' AND t.tms <= '".$this->db->idate($arrayquery[
'contact_update_end_dt']).
"')";
734 if ($arrayquery[
'contact_create_st_dt'] !=
'') {
735 $sqlwhere[] =
" (t.datec >= '".$this->db->idate($arrayquery[
'contact_create_st_dt']).
"' AND t.datec <= '".$this->db->idate($arrayquery[
'contact_create_end_dt']).
"')";
737 if (!empty($arrayquery[
'contact_categ']) && count($arrayquery[
'contact_categ']) > 0) {
738 $sqlwhere[] =
" (contactcateg.fk_categorie IN (".$this->db->escape(implode(
",", $arrayquery[
'contact_categ'])).
"))";
743 $contactstatic =
new Contact($this->db);
744 $elementtype = $contactstatic->table_element;
749 $extrafields->fetch_name_optionals_label($elementtype);
751 $extrafields->fetch_name_optionals_label($elementtype);
753 foreach ($extrafields->attributes[$elementtype][
'label'] as $key => $val) {
754 if (($extrafields->attributes[$elementtype][
'type'][$key] ==
'varchar') ||
755 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'text')) {
756 if (!empty($arrayquery[
'options_'.$key.
'_cnct'])) {
757 $sqlwhere[] =
" (te.".$key.
" LIKE '".$this->db->escape($arrayquery[
"options_".$key.
"_cnct"]).
"')";
759 } elseif (($extrafields->attributes[$elementtype][
'type'][$key] ==
'int') ||
760 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'double')) {
761 if (!empty($arrayquery[
'options_'.$key.
'_max_cnct'])) {
762 $sqlwhere[] =
" (te.".$key.
" >= ".((float) $arrayquery[
"options_".$key.
"_max_cnct"]).
" AND te.".$key.
" <= ".((float) $arrayquery[
"options_".$key.
"_min_cnct"]).
")";
764 } elseif (($extrafields->attributes[$elementtype][
'type'][$key] ==
'date') ||
765 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'datetime')) {
766 if (!empty($arrayquery[
'options_'.$key.
'_end_dt_cnct'])) {
767 $sqlwhere[] =
" (te.".$key.
" >= '".$this->db->idate($arrayquery[
"options_".$key.
"_st_dt_cnct"]).
"' AND te.".$key.
" <= '".$this->db->idate($arrayquery[
"options_".$key.
"_end_dt_cnct"]).
"')";
769 } elseif ($extrafields->attributes[$elementtype][
'type'][$key] ==
'boolean') {
770 if ($arrayquery[
'options_'.$key.
'_cnct'] !=
'') {
771 if ($arrayquery[
'options_'.$key.
'_cnct'] == 0) {
772 $sqlwhere[] =
" (te.".$key.
" = ".((int) $arrayquery[
"options_".$key.
"_cnct"]).
" OR ((te.".$key.
" IS NULL) AND (te.fk_object IS NOT NULL)))";
774 $sqlwhere[] =
" (te.".$key.
" = ".((int) $arrayquery[
"options_".$key.
"_cnct"]).
")";
778 if (is_array($arrayquery[
'options_'.$key.
'_cnct'])) {
779 $sqlwhere[] =
" (te.".$key.
" IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
"options_".$key.
"_cnct"]).
"'", 1).
"))";
780 } elseif (!empty($arrayquery[
'options_'.$key.
'_cnct'])) {
781 $sqlwhere[] =
" (te.".$key.
" LIKE '".$this->db->escape($arrayquery[
"options_".$key.
"_cnct"]).
"')";
786 if (!empty($withThirdpartyFilter)) {
787 if (array_key_exists(
'cust_saleman', $arrayquery)) {
788 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as saleman ON saleman.fk_soc = ts.rowid";
790 if (array_key_exists(
'cust_categ', $arrayquery)) {
791 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"categorie_societe as custcateg ON custcateg.fk_soc = ts.rowid";
794 if (!empty($arrayquery[
'cust_name'])) {
795 $sqlwhere[] = $this->
transformToSQL(
'ts.nom', $arrayquery[
'cust_name']);
797 if (!empty($arrayquery[
'cust_code'])) {
798 $sqlwhere[] = $this->
transformToSQL(
'ts.code_client', $arrayquery[
'cust_code']);
800 if (!empty($arrayquery[
'cust_adress'])) {
801 $sqlwhere[] = $this->
transformToSQL(
'ts.address', $arrayquery[
'cust_adress']);
803 if (!empty($arrayquery[
'cust_zip'])) {
804 $sqlwhere[] = $this->
transformToSQL(
'ts.zip', $arrayquery[
'cust_zip']);
806 if (!empty($arrayquery[
'cust_city'])) {
807 $sqlwhere[] = $this->
transformToSQL(
'ts.town', $arrayquery[
'cust_city']);
809 if (!empty($arrayquery[
'cust_mothercompany'])) {
810 $str = $this->
transformToSQL(
'nom', $arrayquery[
'cust_mothercompany']);
811 $sqlwhere[] =
" (ts.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX.
"societe WHERE (".$str.
")))";
813 if (!empty($arrayquery[
'cust_status']) && count($arrayquery[
'cust_status']) > 0) {
814 $sqlwhere[] =
" (ts.status IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_status'])).
"))";
816 if (!empty($arrayquery[
'cust_typecust']) && count($arrayquery[
'cust_typecust']) > 0) {
817 $sqlwhere[] =
" (ts.client IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typecust'])).
"))";
819 if (!empty($arrayquery[
'cust_comm_status']) && count($arrayquery[
'cust_comm_status']) > 0) {
820 $sqlwhere[] =
" (ts.fk_stcomm IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_comm_status'])).
"))";
822 if (!empty($arrayquery[
'cust_prospect_status']) && count($arrayquery[
'cust_prospect_status']) > 0) {
823 $sqlwhere[] =
" (ts.fk_prospectlevel IN ('".$this->db->sanitize(implode(
"','", $arrayquery[
'cust_prospect_status'])).
"'))";
825 if (!empty($arrayquery[
'cust_typeent']) && count($arrayquery[
'cust_typeent']) > 0) {
826 $sqlwhere[] =
" (ts.fk_typent IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typeent'])).
"))";
828 if (!empty($arrayquery[
'cust_saleman']) && count($arrayquery[
'cust_saleman']) > 0) {
829 $sqlwhere[] =
" (saleman.fk_user IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_saleman'])).
"))";
834 if (!empty($arrayquery[
'cust_state']) && count($arrayquery[
'cust_state']) > 0) {
835 $sqlwhere[] =
" (t.fk_departement IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_state'])).
"))";
837 if (!empty($arrayquery[
'cust_country']) && count($arrayquery[
'cust_country']) > 0) {
838 $sqlwhere[] =
" (ts.fk_pays IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_country'])).
"))";
840 if (!empty($arrayquery[
'cust_effectif_id']) && count($arrayquery[
'cust_effectif_id']) > 0) {
841 $sqlwhere[] =
" (ts.fk_effectif IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_effectif_id'])).
"))";
843 if (!empty($arrayquery[
'cust_categ']) && count($arrayquery[
'cust_categ']) > 0) {
844 $sqlwhere[] =
" (custcateg.fk_categorie IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_categ'])).
"))";
846 if (!empty($arrayquery[
'cust_language']) && count($arrayquery[
'cust_language']) > 0) {
847 $sqlwhere[] =
" (ts.default_lang IN ('".$this->db->sanitize(implode(
"','", $arrayquery[
'cust_language'])).
"'))";
852 $socstatic =
new Societe($this->db);
853 $elementtype = $socstatic->table_element;
859 $extrafields->fetch_name_optionals_label($elementtype);
861 foreach ($extrafields->attributes[$elementtype][
'label'] as $key => $val) {
862 if (($extrafields->attributes[$elementtype][
'type'][$key] ==
'varchar') ||
863 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'text')) {
864 if (!empty($arrayquery[
'options_'.$key])) {
865 $sqlwhere[] =
" (tse.".$key.
" LIKE '".$this->db->escape($arrayquery[
'options_'.$key]).
"')";
867 } elseif (($extrafields->attributes[$elementtype][
'type'][$key] ==
'int') ||
868 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'double')) {
869 if (!empty($arrayquery[
'options_'.$key.
'_max'])) {
870 $sqlwhere[] =
" (tse.".$key.
" >= ".((float) $arrayquery[
"options_".$key.
"_max"]).
" AND tse.".$key.
" <= ".((float) $arrayquery[
"options_".$key.
"_min"]).
")";
872 } elseif (($extrafields->attributes[$elementtype][
'type'][$key] ==
'date') ||
873 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'datetime')) {
874 if (!empty($arrayquery[
'options_'.$key.
'_end_dt'])) {
875 $sqlwhere[] =
" (tse.".$key.
" >= '".$this->db->idate($arrayquery[
"options_".$key.
"_st_dt"]).
"' AND tse.".$key.
" <= '".$this->db->idate($arrayquery[
"options_".$key.
"_end_dt"]).
"')";
877 } elseif ($extrafields->attributes[$elementtype][
'type'][$key] ==
'boolean') {
878 if ($arrayquery[
'options_'.$key] !=
'') {
879 $sqlwhere[] =
" (tse.".$key.
" = ".((int) $arrayquery[
"options_".$key]).
")";
882 if (is_array($arrayquery[
'options_'.$key])) {
883 $sqlwhere[] =
" (tse.".$key.
" IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
"options_".$key]).
"'", 1).
"))";
884 } elseif (!empty($arrayquery[
'options_'.$key])) {
885 $sqlwhere[] =
" (tse.".$key.
" LIKE '".$this->db->escape($arrayquery[
"options_".$key]).
"')";
892 if (count($sqlwhere) > 0) {
893 $sql .=
" WHERE ".implode(
" AND ", $sqlwhere);
897 dol_syslog(get_class($this).
"::query_contact", LOG_DEBUG);
898 $resql = $this->db->query($sql);
900 $this->contact_lines = array();
901 $num = $this->db->num_rows($resql);
906 $obj = $this->db->fetch_object($resql);
908 $this->contact_lines[$i] = $obj->rowid;
913 $this->db->free($resql);
917 $this->error =
"Error ".$this->db->lasterror();
918 dol_syslog(get_class($this).
"::query_contact ".$this->error, LOG_ERR);