dolibarr  20.0.0-beta
contacts1.modules.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3  * Copyright (C) 2005-2009 Laurent Destailleur <eldy@users.sourceforge.net>
4  * Copyright (C) 2005-2009 Regis Houssin <regis.houssin@inodbox.com>
5  *
6  * This program is free software; you can redistribute it and/or modify
7  * it under the terms of the GNU General Public License as published by
8  * the Free Software Foundation; either version 3 of the License, or
9  * (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program. If not, see <https://www.gnu.org/licenses/>.
18  * or see https://www.gnu.org/
19  */
20 
27 include_once DOL_DOCUMENT_ROOT.'/core/modules/mailings/modules_mailings.php';
28 
29 
34 {
35  public $name = 'ContactCompanies'; // Identifiant du module mailing
36  // This label is used if no translation is found for key XXX neither MailingModuleDescXXX where XXX=name is found
37  public $desc = 'Contacts of thirdparties (prospects, customers, suppliers...)';
38  public $require_module = array("societe"); // Module mailing actif si modules require_module actifs
39  public $require_admin = 0; // Module mailing actif pour user admin ou non
40 
41  public $enabled = 'isModEnabled("societe")';
42 
46  public $picto = 'contact';
47 
48 
54  public function __construct($db)
55  {
56  $this->db = $db;
57  }
58 
59 
68  public function getSqlArrayForStats()
69  {
70  global $langs;
71 
72  $langs->load("commercial");
73 
74  $statssql = array();
75  $statssql[0] = "SELECT '".$this->db->escape($langs->trans("NbOfCompaniesContacts"))."' as label,";
76  $statssql[0] .= " count(distinct(c.email)) as nb";
77  $statssql[0] .= " FROM ".MAIN_DB_PREFIX."socpeople as c";
78  $statssql[0] .= " WHERE c.entity IN (".getEntity('contact').")";
79  $statssql[0] .= " AND c.email <> ''"; // Note that null != '' is false
80  $statssql[0] .= " AND c.statut = 1";
81 
82  return $statssql;
83  }
84 
85 
94  public function getNbOfRecipients($sql = '')
95  {
96  global $conf;
97 
98  $sql = "SELECT count(distinct(c.email)) as nb";
99  $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as c";
100  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = c.fk_soc";
101  $sql .= " WHERE c.entity IN (".getEntity('contact').")";
102  $sql .= " AND c.email <> ''"; // Note that null != '' is false
103  if (empty($this->evenunsubscribe)) {
104  $sql .= " AND NOT EXISTS (SELECT rowid FROM ".MAIN_DB_PREFIX."mailing_unsubscribe as mu WHERE mu.email = c.email and mu.entity = ".((int) $conf->entity).")";
105  }
106  // exclude unsubscribed users
107  $sql .= " AND c.statut = 1";
108 
109  // The request must return a field called "nb" to be understandable by parent::getNbOfRecipients
110  return parent::getNbOfRecipients($sql);
111  }
112 
113 
119  public function formFilter()
120  {
121  global $conf,$langs;
122 
123  // Load translation files required by the page
124  $langs->loadLangs(array("commercial", "companies", "suppliers", "categories"));
125 
126  $s = '';
127 
128  // Add filter on job position
129  $sql = "SELECT sp.poste, count(distinct(sp.email)) AS nb";
130  $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as sp";
131  $sql .= " WHERE sp.entity IN (".getEntity('contact').")";
132  $sql .= " AND sp.email <> ''"; // Note that null != '' is false
133  $sql .= " AND sp.statut = 1";
134  $sql .= " AND (sp.poste IS NOT NULL AND sp.poste <> '')";
135  $sql .= " GROUP BY sp.poste";
136  $sql .= " ORDER BY sp.poste";
137  $resql = $this->db->query($sql);
138 
139  $s .= '<select id="filter_jobposition_contact" name="filter_jobposition" class="flat maxwidth200" placeholder="'.dol_escape_htmltag($langs->trans("PostOrFunction")).'">';
140  $s .= '<option value="-1">'.$langs->trans("PostOrFunction").'</option>';
141  if ($resql) {
142  $num = $this->db->num_rows($resql);
143  $i = 0;
144  if ($num > 0) {
145  while ($i < $num) {
146  $obj = $this->db->fetch_object($resql);
147  $s .= '<option value="'.dol_escape_htmltag($obj->poste).'">'.dol_escape_htmltag($obj->poste).' ('.$obj->nb.')</option>';
148  $i++;
149  }
150  } else {
151  $s .= '<option disabled="disabled" value="">'.$langs->trans("None").'</option>';
152  }
153  } else {
154  dol_print_error($this->db);
155  }
156  $s .= '</select>';
157  $s .= ajax_combobox("filter_jobposition_contact");
158  $s .= ' ';
159 
160  // Filter on contact category
161  $sql = "SELECT c.label, count(distinct(sp.email)) AS nb";
162  $sql .= " FROM ";
163  $sql .= " ".MAIN_DB_PREFIX."socpeople as sp,";
164  $sql .= " ".MAIN_DB_PREFIX."categorie as c,";
165  $sql .= " ".MAIN_DB_PREFIX."categorie_contact as cs";
166  $sql .= " WHERE sp.entity IN (".getEntity('contact').")";
167  $sql .= " AND sp.email <> ''"; // Note that null != '' is false
168  $sql .= " AND sp.statut = 1";
169  $sql .= " AND cs.fk_categorie = c.rowid";
170  $sql .= " AND cs.fk_socpeople = sp.rowid";
171  $sql .= " GROUP BY c.label";
172  $sql .= " ORDER BY c.label";
173  $resql = $this->db->query($sql);
174 
175  $s .= '<select id="filter_category_contact" name="filter_category" class="flat maxwidth200">';
176  $s .= '<option value="-1">'.$langs->trans("ContactCategoriesShort").'</option>';
177  if ($resql) {
178  $num = $this->db->num_rows($resql);
179  if ($num) {
180  $i = 0;
181  while ($i < $num) {
182  $obj = $this->db->fetch_object($resql);
183  $s .= '<option value="'.$obj->label.'">'.$obj->label.' ('.$obj->nb.')</option>';
184  $i++;
185  }
186  } else {
187  $s .= '<option value="-1" disabled="disabled">'.$langs->trans("NoContactWithCategoryFound").'</option>';
188  }
189  } else {
190  dol_print_error($this->db);
191  }
192  $s .= '</select>';
193  $s .= ajax_combobox("filter_category_contact");
194  $s .= '<br>';
195 
196  // Add prospect of a particular level
197  $s .= '<select id="filter_contact" name="filter" class="flat maxwidth200">';
198  $sql = "SELECT code, label";
199  $sql .= " FROM ".MAIN_DB_PREFIX."c_prospectlevel";
200  $sql .= " WHERE active > 0";
201  $sql .= " ORDER BY label";
202  $resql = $this->db->query($sql);
203  if ($resql) {
204  $num = $this->db->num_rows($resql);
205  if ($num) {
206  $s .= '<option value="-1">'.$langs->trans("NatureOfThirdParty").'</option>';
207  } else {
208  $s .= '<option value="-1">'.$langs->trans("ContactsAllShort").'</option>';
209  }
210  $s .= '<option value="prospects">'.$langs->trans("ThirdPartyProspects").'</option>';
211 
212  $i = 0;
213  while ($i < $num) {
214  $obj = $this->db->fetch_object($resql);
215  $level = $langs->trans($obj->code);
216  if ($level == $obj->code) {
217  $level = $langs->trans($obj->label);
218  }
219  $labeltoshow = $langs->trans("ThirdPartyProspects").' <span class="opacitymedium">('.$langs->trans("ProspectLevelShort").'='.$level.')</span>';
220  $s .= '<option value="prospectslevel'.$obj->code.'" data-html="'.dol_escape_htmltag($labeltoshow).'">'.$labeltoshow.'</option>';
221  $i++;
222  }
223  } else {
224  dol_print_error($this->db);
225  }
226  $s .= '<option value="customers">'.$langs->trans("ThirdPartyCustomers").'</option>';
227  //$s.='<option value="customersidprof">'.$langs->trans("ThirdPartyCustomersWithIdProf12",$langs->trans("ProfId1"),$langs->trans("ProfId2")).'</option>';
228  $s .= '<option value="suppliers">'.$langs->trans("ThirdPartySuppliers").'</option>';
229  $s .= '</select>';
230  $s .= ajax_combobox("filter_contact");
231 
232  $s .= ' ';
233 
234  // Filter on thirdparty category
235  $sql = "SELECT c.label, count(distinct(sp.email)) AS nb";
236  $sql .= " FROM ";
237  $sql .= " ".MAIN_DB_PREFIX."socpeople as sp,";
238  $sql .= " ".MAIN_DB_PREFIX."categorie as c,";
239  $sql .= " ".MAIN_DB_PREFIX."categorie_societe as cs";
240  $sql .= " WHERE sp.entity IN (".getEntity('contact').")";
241  $sql .= " AND sp.email <> ''"; // Note that null != '' is false
242  $sql .= " AND sp.statut = 1";
243  $sql .= " AND cs.fk_categorie = c.rowid";
244  $sql .= " AND cs.fk_soc = sp.fk_soc";
245  $sql .= " GROUP BY c.label";
246  $sql .= " ORDER BY c.label";
247  $resql = $this->db->query($sql);
248 
249  $s .= '<select id="filter_category_customer_contact" name="filter_category_customer" class="flat maxwidth200">';
250  $s .= '<option value="-1">'.$langs->trans("CustomersProspectsCategoriesShort").'</option>';
251  if ($resql) {
252  $num = $this->db->num_rows($resql);
253  if ($num) {
254  $i = 0;
255  while ($i < $num) {
256  $obj = $this->db->fetch_object($resql);
257  $s .= '<option value="'.$obj->label.'">'.$obj->label.' ('.$obj->nb.')</option>';
258  $i++;
259  }
260  } else {
261  $s .= '<option value="-1" disabled="disabled">'.$langs->trans("NoContactLinkedToThirdpartieWithCategoryFound").'</option>';
262  }
263  } else {
264  dol_print_error($this->db);
265  }
266  $s .= '</select>';
267  $s .= ajax_combobox("filter_category_customer_contact");
268 
269  $s .= ' ';
270 
271  // Filter on thirdparty category
272  $sql = "SELECT c.label, count(distinct(sp.email)) AS nb";
273  $sql .= " FROM ";
274  $sql .= " ".MAIN_DB_PREFIX."socpeople as sp,";
275  $sql .= " ".MAIN_DB_PREFIX."categorie as c,";
276  $sql .= " ".MAIN_DB_PREFIX."categorie_fournisseur as cs";
277  $sql .= " WHERE sp.entity IN (".getEntity('contact').")";
278  $sql .= " AND sp.email <> ''"; // Note that null != '' is false
279  $sql .= " AND sp.statut = 1";
280  $sql .= " AND cs.fk_categorie = c.rowid";
281  $sql .= " AND cs.fk_soc = sp.fk_soc";
282  $sql .= " GROUP BY c.label";
283  $sql .= " ORDER BY c.label";
284  $resql = $this->db->query($sql);
285 
286  $s .= '<select id="filter_category_supplier_contact" name="filter_category_supplier" class="flat maxwidth200">';
287  $s .= '<option value="-1">'.$langs->trans("SuppliersCategoriesShort").'</option>';
288  if ($resql) {
289  $num = $this->db->num_rows($resql);
290  if ($num) {
291  $i = 0;
292  while ($i < $num) {
293  $obj = $this->db->fetch_object($resql);
294  $s .= '<option value="'.$obj->label.'">'.$obj->label.' ('.$obj->nb.')</option>';
295  $i++;
296  }
297  } else {
298  $s .= '<option value="-1" disabled="disabled">'.$langs->trans("NoContactLinkedToThirdpartieWithCategoryFound").'</option>';
299  }
300  } else {
301  dol_print_error($this->db);
302  }
303  $s .= '</select>';
304 
305  $s .= ajax_combobox("filter_category_supplier_contact");
306 
307  // Choose language
308  if (getDolGlobalInt('MAIN_MULTILANGS')) {
309  require_once DOL_DOCUMENT_ROOT.'/core/class/html.formadmin.class.php';
310  $formadmin = new FormAdmin($this->db);
311  $s .= img_picto($langs->trans("DefaultLang"), 'language', 'class="pictofixedwidth"');
312  $s .= $formadmin->select_language(GETPOST('filter_lang', 'aZ09'), 'filter_lang', 0, null, $langs->trans("DefaultLang"), 0, 0, '', 0, 0, 0, null, 1);
313  }
314 
315  return $s;
316  }
317 
318 
325  public function url($id)
326  {
327  return '<a href="'.DOL_URL_ROOT.'/contact/card.php?id='.$id.'">'.img_object('', "contact").'</a>';
328  }
329 
330 
331  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
338  public function add_to_target($mailing_id)
339  {
340  // phpcs:enable
341  global $conf, $langs;
342 
343  $filter = GETPOST('filter', 'alpha');
344  $filter_jobposition = GETPOST('filter_jobposition', 'alpha');
345  $filter_category = GETPOST('filter_category', 'alpha');
346  $filter_category_customer = GETPOST('filter_category_customer', 'alpha');
347  $filter_category_supplier = GETPOST('filter_category_supplier', 'alpha');
348  $filter_lang = GETPOST('filter_lang', 'alpha');
349 
350  $cibles = array();
351 
352  // List prospects levels
353  $prospectlevel = array();
354  $sql = "SELECT code, label";
355  $sql .= " FROM ".MAIN_DB_PREFIX."c_prospectlevel";
356  $sql .= " WHERE active > 0";
357  $sql .= " ORDER BY label";
358  $resql = $this->db->query($sql);
359  if ($resql) {
360  $num = $this->db->num_rows($resql);
361  $i = 0;
362  while ($i < $num) {
363  $obj = $this->db->fetch_object($resql);
364  $prospectlevel[$obj->code] = $obj->label;
365  $i++;
366  }
367  } else {
368  dol_print_error($this->db);
369  }
370 
371  // Request must return: id, email, fk_contact, lastname, firstname, other
372  $sql = "SELECT sp.rowid as id, sp.email as email, sp.rowid as fk_contact, sp.lastname, sp.firstname, sp.civility as civility_id, sp.poste as jobposition,";
373  $sql .= " s.nom as companyname";
374  $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as sp";
375  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = sp.fk_soc";
376  if ($filter_category != 'all' && $filter_category != '-1') {
377  $sql .= ", ".MAIN_DB_PREFIX."categorie as c";
378  $sql .= ", ".MAIN_DB_PREFIX."categorie_contact as cs";
379  }
380  if ($filter_category_customer != 'all' && $filter_category_customer != '-1') {
381  $sql .= ", ".MAIN_DB_PREFIX."categorie as c2";
382  $sql .= ", ".MAIN_DB_PREFIX."categorie_societe as c2s";
383  }
384  if ($filter_category_supplier != 'all' && $filter_category_supplier != '-1') {
385  $sql .= ", ".MAIN_DB_PREFIX."categorie as c3";
386  $sql .= ", ".MAIN_DB_PREFIX."categorie_fournisseur as c3s";
387  }
388  $sql .= " WHERE sp.entity IN (".getEntity('contact').")";
389  $sql .= " AND sp.email <> ''";
390 
391  if (empty($this->evenunsubscribe)) {
392  $sql .= " AND NOT EXISTS (SELECT rowid FROM ".MAIN_DB_PREFIX."mailing_unsubscribe as mu WHERE mu.email = sp.email and mu.entity = ".((int) $conf->entity).")";
393  }
394  // Exclude unsubscribed email addresses
395  $sql .= " AND sp.statut = 1";
396  $sql .= " AND sp.email NOT IN (SELECT email FROM ".MAIN_DB_PREFIX."mailing_cibles WHERE fk_mailing=".((int) $mailing_id).")";
397 
398  // Filter on category
399  if ($filter_category != 'all' && $filter_category != '-1') {
400  $sql .= " AND cs.fk_categorie = c.rowid AND cs.fk_socpeople = sp.rowid";
401  $sql .= " AND c.label = '".$this->db->escape($filter_category)."'";
402  }
403  if ($filter_category_customer != 'all' && $filter_category_customer != '-1') {
404  $sql .= " AND c2s.fk_categorie = c2.rowid AND c2s.fk_soc = sp.fk_soc";
405  $sql .= " AND c2.label = '".$this->db->escape($filter_category_customer)."'";
406  }
407  if ($filter_category_supplier != 'all' && $filter_category_supplier != '-1') {
408  $sql .= " AND c3s.fk_categorie = c3.rowid AND c3s.fk_soc = sp.fk_soc";
409  $sql .= " AND c3.label = '".$this->db->escape($filter_category_supplier)."'";
410  }
411 
412  // Filter on language
413  if (!empty($filter_lang) && $filter_lang != '-1') {
414  $sql .= " AND sp.default_lang LIKE '".$this->db->escape($filter_lang)."%'";
415  }
416 
417  // Filter on nature
418  $key = $filter;
419 
420  //print "xx".$key;
421  if ($key == 'prospects') {
422  $sql .= " AND s.client = 2";
423  }
424  foreach ($prospectlevel as $codelevel => $valuelevel) {
425  if ($key == 'prospectslevel'.$codelevel) {
426  $sql .= " AND s.fk_prospectlevel = '".$this->db->escape($codelevel)."'";
427  }
428  }
429  if ($key == 'customers') {
430  $sql .= " AND s.client = 1";
431  }
432  if ($key == 'suppliers') {
433  $sql .= " AND s.fournisseur = 1";
434  }
435 
436  // Filter on job position
437  $key = $filter_jobposition;
438  if (!empty($key) && $key != 'all' && $key != '-1') {
439  $sql .= " AND sp.poste = '".$this->db->escape($key)."'";
440  }
441 
442  $sql .= " ORDER BY sp.email";
443  //print "wwwwwwx".$sql;
444 
445  // Store recipients in target tables
446  $result = $this->db->query($sql);
447  if ($result) {
448  $num = $this->db->num_rows($result);
449  $i = 0;
450  $j = 0;
451 
452  dol_syslog(get_class($this)."::add_to_target mailing ".$num." targets found");
453 
454  $old = '';
455  while ($i < $num) {
456  $obj = $this->db->fetch_object($result);
457  if ($old != $obj->email) {
458  $cibles[$j] = array(
459  'email' => $obj->email,
460  'fk_contact' => $obj->fk_contact,
461  'lastname' => $obj->lastname,
462  'firstname' => $obj->firstname,
463  'other' =>
464  ($langs->transnoentities("ThirdParty").'='.$obj->companyname).';'.
465  ($langs->transnoentities("UserTitle").'='.($obj->civility_id ? $langs->transnoentities("Civility".$obj->civility_id) : '')).';'.
466  ($langs->transnoentities("PostOrFunction").'='.$obj->jobposition),
467  'source_url' => $this->url($obj->id),
468  'source_id' => $obj->id,
469  'source_type' => 'contact'
470  );
471  $old = $obj->email;
472  $j++;
473  }
474 
475  $i++;
476  }
477  } else {
478  dol_syslog($this->db->error());
479  $this->error = $this->db->error();
480  return -1;
481  }
482 
483  return parent::addTargetsToDatabase($mailing_id, $cibles);
484  }
485 }
ajax_combobox($htmlname, $events=array(), $minLengthToAutocomplete=0, $forcefocus=0, $widthTypeOfAutocomplete='resolve', $idforemptyvalue='-1', $morecss='')
Convert a html select field into an ajax combobox.
Definition: ajax.lib.php:456
Class to generate html code for admin pages.
Parent class of emailing target selectors modules.
Class to offer a selector of emailing targets from contacts.
add_to_target($mailing_id)
Add some recipients into target table.
formFilter()
Affiche formulaire de filtre qui apparait dans page de selection des destinataires de mailings.
url($id)
Renvoie url lien vers fiche de la source du destinataire du mailing.
getNbOfRecipients($sql='')
Return here number of distinct emails returned by your selector.
__construct($db)
Constructor.
getSqlArrayForStats()
On the main mailing area, there is a box with statistics.
img_object($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0)
Show a picto called object_picto (generic function)
img_picto($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0, $alt='', $morecss='', $marginleftonlyshort=2)
Show picto whatever it's its name (generic function)
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
dol_escape_htmltag($stringtoescape, $keepb=0, $keepn=0, $noescapetags='', $escapeonlyhtmltags=0, $cleanalsojavascript=0)
Returns text escaped for inclusion in HTML alt or title or value tags, or into values of HTML input f...