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)) {
534 global $langs, $conf, $extrafields;
538 $sql .=
" FROM ".MAIN_DB_PREFIX.
"societe as t";
539 $sql .=
" LEFT OUTER JOIN ".MAIN_DB_PREFIX.
"societe_extrafields as te ON te.fk_object=t.rowid ";
543 $sqlwhere[] =
't.entity IN ('.getEntity(
'societe').
')';
545 if (count($arrayquery) > 0) {
546 if (array_key_exists(
'cust_saleman', $arrayquery)) {
547 $sql .=
" LEFT OUTER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as saleman ON saleman.fk_soc=t.rowid ";
549 if (array_key_exists(
'cust_categ', $arrayquery)) {
550 $sql .=
" LEFT OUTER JOIN ".MAIN_DB_PREFIX.
"categorie_societe as custcateg ON custcateg.fk_soc=t.rowid ";
553 if (!empty($arrayquery[
'cust_name'])) {
554 $sqlwhere[] = $this->
transformToSQL(
't.nom', $arrayquery[
'cust_name']);
556 if (!empty($arrayquery[
'cust_code'])) {
557 $sqlwhere[] = $this->
transformToSQL(
't.code_client', $arrayquery[
'cust_code']);
559 if (!empty($arrayquery[
'cust_adress'])) {
560 $sqlwhere[] = $this->
transformToSQL(
't.address', $arrayquery[
'cust_adress']);
562 if (!empty($arrayquery[
'cust_zip'])) {
563 $sqlwhere[] = $this->
transformToSQL(
't.zip', $arrayquery[
'cust_zip']);
565 if (!empty($arrayquery[
'cust_city'])) {
566 $sqlwhere[] = $this->
transformToSQL(
't.town', $arrayquery[
'cust_city']);
568 if (!empty($arrayquery[
'cust_mothercompany'])) {
569 $str = $this->
transformToSQL(
'nom', $arrayquery[
'cust_mothercompany']);
570 $sqlwhere[] =
" (t.parent IN (SELECT rowid FROM ".MAIN_DB_PREFIX.
"societe WHERE (".$str.
")))";
572 if (!empty($arrayquery[
'cust_status']) && count($arrayquery[
'cust_status']) > 0) {
573 $sqlwhere[] =
" (t.status IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_status'])).
"))";
575 if (!empty($arrayquery[
'cust_typecust']) && count($arrayquery[
'cust_typecust']) > 0) {
576 $sqlwhere[] =
" (t.client IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typecust'])).
"))";
578 if (!empty($arrayquery[
'cust_comm_status']) && count($arrayquery[
'cust_comm_status']) > 0) {
579 $sqlwhere[] =
" (t.fk_stcomm IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_comm_status'])).
"))";
581 if (!empty($arrayquery[
'cust_prospect_status']) && count($arrayquery[
'cust_prospect_status']) > 0) {
582 $sqlwhere[] =
" (t.fk_prospectlevel IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
'cust_prospect_status']).
"'", 1).
"))";
584 if (!empty($arrayquery[
'cust_typeent']) && count($arrayquery[
'cust_typeent']) > 0) {
585 $sqlwhere[] =
" (t.fk_typent IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_typeent'])).
"))";
587 if (!empty($arrayquery[
'cust_saleman']) && count($arrayquery[
'cust_saleman']) > 0) {
588 $sqlwhere[] =
" (saleman.fk_user IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_saleman'])).
"))";
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).
"))";
604 if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
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'])).
"))";
742 if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
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 OUTER 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 OUTER 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'])).
"))";
831 if (!empty($arrayquery[
'cust_country']) && count($arrayquery[
'cust_country']) > 0) {
832 $sqlwhere[] =
" (ts.fk_pays IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_country'])).
"))";
834 if (!empty($arrayquery[
'cust_effectif_id']) && count($arrayquery[
'cust_effectif_id']) > 0) {
835 $sqlwhere[] =
" (ts.fk_effectif IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_effectif_id'])).
"))";
837 if (!empty($arrayquery[
'cust_categ']) && count($arrayquery[
'cust_categ']) > 0) {
838 $sqlwhere[] =
" (custcateg.fk_categorie IN (".$this->db->sanitize(implode(
',', $arrayquery[
'cust_categ'])).
"))";
840 if (!empty($arrayquery[
'cust_language']) && count($arrayquery[
'cust_language']) > 0) {
841 $sqlwhere[] =
" (ts.default_lang IN ('".$this->db->sanitize(implode(
"','", $arrayquery[
'cust_language'])).
"'))";
845 if (empty($conf->global->MAIN_EXTRAFIELDS_DISABLED)) {
846 $socstatic =
new Societe($this->db);
847 $elementtype = $socstatic->table_element;
853 $extrafields->fetch_name_optionals_label($elementtype);
855 foreach ($extrafields->attributes[$elementtype][
'label'] as $key => $val) {
856 if (($extrafields->attributes[$elementtype][
'type'][$key] ==
'varchar') ||
857 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'text')) {
858 if (!empty($arrayquery[
'options_'.$key])) {
859 $sqlwhere[] =
" (tse.".$key.
" LIKE '".$this->db->escape($arrayquery[
'options_'.$key]).
"')";
861 } elseif (($extrafields->attributes[$elementtype][
'type'][$key] ==
'int') ||
862 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'double')) {
863 if (!empty($arrayquery[
'options_'.$key.
'_max'])) {
864 $sqlwhere[] =
" (tse.".$key.
" >= ".((
float) $arrayquery[
"options_".$key.
"_max"]).
" AND tse.".$key.
" <= ".((
float) $arrayquery[
"options_".$key.
"_min"]).
")";
866 } elseif (($extrafields->attributes[$elementtype][
'type'][$key] ==
'date') ||
867 ($extrafields->attributes[$elementtype][
'type'][$key] ==
'datetime')) {
868 if (!empty($arrayquery[
'options_'.$key.
'_end_dt'])) {
869 $sqlwhere[] =
" (tse.".$key.
" >= '".$this->db->idate($arrayquery[
"options_".$key.
"_st_dt"]).
"' AND tse.".$key.
" <= '".$this->db->idate($arrayquery[
"options_".$key.
"_end_dt"]).
"')";
871 } elseif ($extrafields->attributes[$elementtype][
'type'][$key] ==
'boolean') {
872 if ($arrayquery[
'options_'.$key] !=
'') {
873 $sqlwhere[] =
" (tse.".$key.
" = ".((int) $arrayquery[
"options_".$key]).
")";
876 if (is_array($arrayquery[
'options_'.$key])) {
877 $sqlwhere[] =
" (tse.".$key.
" IN (".$this->db->sanitize(
"'".implode(
"','", $arrayquery[
"options_".$key]).
"'", 1).
"))";
878 } elseif (!empty($arrayquery[
'options_'.$key])) {
879 $sqlwhere[] =
" (tse.".$key.
" LIKE '".$this->db->escape($arrayquery[
"options_".$key]).
"')";
886 if (count($sqlwhere) > 0) {
887 $sql .=
" WHERE ".implode(
" AND ", $sqlwhere);
891 dol_syslog(get_class($this).
"::query_contact", LOG_DEBUG);
892 $resql = $this->db->query(
$sql);
894 $this->contact_lines = array();
895 $num = $this->db->num_rows($resql);
900 $obj = $this->db->fetch_object($resql);
902 $this->contact_lines[$i] = $obj->rowid;
907 $this->db->free($resql);
911 $this->error =
"Error ".$this->db->lasterror();
912 dol_syslog(get_class($this).
"::query_contact ".$this->error, LOG_ERR);
929 $return_sql_criteria =
'(';
932 if (preg_match(
'/;/', $criteria)) {
933 $return_sql_not_like = array();
934 $return_sql_like = array();
936 $criteria_array = explode(
';', $criteria);
937 foreach ($criteria_array as $inter_criteria) {
938 if (preg_match(
'/!/', $inter_criteria)) {
939 $return_sql_not_like[] =
'('.$column_to_test.
' NOT LIKE \''.str_replace(
'!',
'', $inter_criteria).
'\')
';
941 $return_sql_like[] = '(
'.$column_to_test.' LIKE \
''.$inter_criteria.
'\')
';
945 if (count($return_sql_like) > 0) {
946 $return_sql_criteria .= '(
'.implode(' OR
', $return_sql_like).')
';
948 if (count($return_sql_not_like) > 0) {
949 $return_sql_criteria .= ' AND (
'.implode(' AND
', $return_sql_not_like).')
';
952 $return_sql_criteria .= $column_to_test.' LIKE \
''.$this->db->escape($criteria).
'\'';
955 $return_sql_criteria .=
')';
957 return $return_sql_criteria;
Class to manage advanced emailing target selector.
transformToSQL($column_to_test, $criteria)
Parse criteria to return a SQL qury formated.
fetch_by_element($id=0, $type_element='mailing')
Load object in memory from the database.
__construct($db)
Constructor.
fetch($id)
Load object in memory from the database.
fetch_by_mailing($id=0)
Load object in memory from the database.
update($user, $notrigger=0)
Update object into database.
savequery($user, $arrayquery)
Save query in database to retrieve it.
query_thirdparty($arrayquery)
Load object in memory from database.
query_contact($arrayquery, $withThirdpartyFilter=0)
Load object in memory from database.
create($user, $notrigger=0)
Create object into database.
Class to manage customers or prospects.
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Class to manage third parties objects (customers, suppliers, prospects...)
if(isModEnabled('facture') && $user->hasRight('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') && $user->hasRight('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.
if(!function_exists('dol_getprefix')) dol_include_once($relpath, $classname='')
Make an include_once using default root and alternate root if it fails.
dol_now($mode='auto')
Return date for now.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
div float
Buy price without taxes.
$conf db name
Only used if Module[ID]Name translation string is not found.