contacts1.modules.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479
  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. /**
  21. * \file htdocs/core/modules/mailings/contacts1.modules.php
  22. * \ingroup mailing
  23. * \brief File of class to offer a selector of emailing targets with Rule 'Poire'.
  24. */
  25. include_once DOL_DOCUMENT_ROOT.'/core/modules/mailings/modules_mailings.php';
  26. /**
  27. * Class to offer a selector of emailing targets from contacts
  28. */
  29. class mailing_contacts1 extends MailingTargets
  30. {
  31. public $name = 'ContactCompanies'; // Identifiant du module mailing
  32. // This label is used if no translation is found for key XXX neither MailingModuleDescXXX where XXX=name is found
  33. public $desc = 'Contacts of thirdparties (prospects, customers, suppliers...)';
  34. public $require_module = array("societe"); // Module mailing actif si modules require_module actifs
  35. public $require_admin = 0; // Module mailing actif pour user admin ou non
  36. /**
  37. * @var string String with name of icon for myobject. Must be the part after the 'object_' into object_myobject.png
  38. */
  39. public $picto = 'contact';
  40. /**
  41. * @var DoliDB Database handler.
  42. */
  43. public $db;
  44. /**
  45. * Constructor
  46. *
  47. * @param DoliDB $db Database handler
  48. */
  49. public function __construct($db)
  50. {
  51. $this->db = $db;
  52. }
  53. /**
  54. * On the main mailing area, there is a box with statistics.
  55. * If you want to add a line in this report you must provide an
  56. * array of SQL request that returns two field:
  57. * One called "label", One called "nb".
  58. *
  59. * @return string[] Array with SQL requests
  60. */
  61. public function getSqlArrayForStats()
  62. {
  63. global $conf, $langs;
  64. $langs->load("commercial");
  65. $statssql = array();
  66. $statssql[0] = "SELECT '".$this->db->escape($langs->trans("NbOfCompaniesContacts"))."' as label,";
  67. $statssql[0] .= " count(distinct(c.email)) as nb";
  68. $statssql[0] .= " FROM ".MAIN_DB_PREFIX."socpeople as c";
  69. $statssql[0] .= " WHERE c.entity IN (".getEntity('socpeople').")";
  70. $statssql[0] .= " AND c.email <> ''"; // Note that null != '' is false
  71. $statssql[0] .= " AND (SELECT count(*) FROM ".MAIN_DB_PREFIX."mailing_unsubscribe WHERE email = c.email) = 0";
  72. $statssql[0] .= " AND c.statut = 1";
  73. return $statssql;
  74. }
  75. /**
  76. * Return here number of distinct emails returned by your selector.
  77. * For example if this selector is used to extract 500 different
  78. * emails from a text file, this function must return 500.
  79. *
  80. * @param string $sql Requete sql de comptage
  81. * @return int
  82. */
  83. public function getNbOfRecipients($sql = '')
  84. {
  85. global $conf;
  86. $sql = "SELECT count(distinct(c.email)) as nb";
  87. $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as c";
  88. $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = c.fk_soc";
  89. $sql .= " WHERE c.entity IN (".getEntity('socpeople').")";
  90. $sql .= " AND c.email <> ''"; // Note that null != '' is false
  91. $sql .= " AND (SELECT count(*) FROM ".MAIN_DB_PREFIX."mailing_unsubscribe WHERE email = c.email) = 0";
  92. // exclude unsubscribed users
  93. $sql .= " AND c.statut = 1";
  94. // The request must return a field called "nb" to be understandable by parent::getNbOfRecipients
  95. return parent::getNbOfRecipients($sql);
  96. }
  97. /**
  98. * Affiche formulaire de filtre qui apparait dans page de selection des destinataires de mailings
  99. *
  100. * @return string Retourne zone select
  101. */
  102. public function formFilter()
  103. {
  104. global $langs;
  105. // Load translation files required by the page
  106. $langs->loadLangs(array("commercial", "companies", "suppliers", "categories"));
  107. $s = '';
  108. // Add filter on job position
  109. $sql = "SELECT sp.poste, count(distinct(sp.email)) AS nb";
  110. $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as sp";
  111. $sql .= " WHERE sp.entity IN (".getEntity('socpeople').")";
  112. $sql .= " AND sp.email <> ''"; // Note that null != '' is false
  113. $sql .= " AND sp.statut = 1";
  114. $sql .= " AND (sp.poste IS NOT NULL AND sp.poste <> '')";
  115. $sql .= " GROUP BY sp.poste";
  116. $sql .= " ORDER BY sp.poste";
  117. $resql = $this->db->query($sql);
  118. $s .= $langs->trans("PostOrFunction").' ';
  119. $s .= '<select name="filter_jobposition" class="flat marginrightonly" placeholder="'.dol_escape_htmltag($langs->trans("PostOrFunction")).'">';
  120. $s .= '<option value="all">&nbsp;</option>';
  121. if ($resql) {
  122. $num = $this->db->num_rows($resql);
  123. $i = 0;
  124. if ($num > 0) {
  125. while ($i < $num) {
  126. $obj = $this->db->fetch_object($resql);
  127. $s .= '<option value="'.dol_escape_htmltag($obj->poste).'">'.dol_escape_htmltag($obj->poste).' ('.$obj->nb.')</option>';
  128. $i++;
  129. }
  130. } else {
  131. $s .= '<option disabled="disabled" value="">'.$langs->trans("None").'</option>';
  132. }
  133. } else {
  134. dol_print_error($this->db);
  135. }
  136. $s .= '</select>';
  137. $s .= ' ';
  138. // Filter on contact category
  139. $s .= $langs->trans("ContactCategoriesShort").' ';
  140. $sql = "SELECT c.label, count(distinct(sp.email)) AS nb";
  141. $sql .= " FROM ";
  142. $sql .= " ".MAIN_DB_PREFIX."socpeople as sp,";
  143. $sql .= " ".MAIN_DB_PREFIX."categorie as c,";
  144. $sql .= " ".MAIN_DB_PREFIX."categorie_contact as cs";
  145. $sql .= " WHERE sp.entity IN (".getEntity('socpeople').")";
  146. $sql .= " AND sp.email <> ''"; // Note that null != '' is false
  147. $sql .= " AND sp.statut = 1";
  148. $sql .= " AND cs.fk_categorie = c.rowid";
  149. $sql .= " AND cs.fk_socpeople = sp.rowid";
  150. $sql .= " GROUP BY c.label";
  151. $sql .= " ORDER BY c.label";
  152. $resql = $this->db->query($sql);
  153. $s .= '<select name="filter_category" class="flat marginrightonly">';
  154. $s .= '<option value="all">&nbsp;</option>';
  155. if ($resql) {
  156. $num = $this->db->num_rows($resql);
  157. if ($num) {
  158. $i = 0;
  159. while ($i < $num) {
  160. $obj = $this->db->fetch_object($resql);
  161. $s .= '<option value="'.$obj->label.'">'.$obj->label.' ('.$obj->nb.')</option>';
  162. $i++;
  163. }
  164. } else {
  165. $s .= '<option value="-1" disabled="disabled">'.$langs->trans("NoContactWithCategoryFound").'</option>';
  166. }
  167. } else {
  168. dol_print_error($this->db);
  169. }
  170. $s .= '</select>';
  171. $s .= '<br>';
  172. // Add prospect of a particular level
  173. $s .= $langs->trans("NatureOfThirdParty").' ';
  174. $s .= '<select name="filter" class="flat marginrightonly">';
  175. $sql = "SELECT code, label";
  176. $sql .= " FROM ".MAIN_DB_PREFIX."c_prospectlevel";
  177. $sql .= " WHERE active > 0";
  178. $sql .= " ORDER BY label";
  179. $resql = $this->db->query($sql);
  180. if ($resql) {
  181. $num = $this->db->num_rows($resql);
  182. if ($num) {
  183. $s .= '<option value="all">&nbsp;</option>';
  184. } else {
  185. $s .= '<option value="all">'.$langs->trans("ContactsAllShort").'</option>';
  186. }
  187. $s .= '<option value="prospects">'.$langs->trans("ThirdPartyProspects").'</option>';
  188. $i = 0;
  189. while ($i < $num) {
  190. $obj = $this->db->fetch_object($resql);
  191. $level = $langs->trans($obj->code);
  192. if ($level == $obj->code) {
  193. $level = $langs->trans($obj->label);
  194. }
  195. $s .= '<option value="prospectslevel'.$obj->code.'">'.$langs->trans("ThirdPartyProspects").' ('.$langs->trans("ProspectLevelShort").'='.$level.')</option>';
  196. $i++;
  197. }
  198. } else {
  199. dol_print_error($this->db);
  200. }
  201. $s .= '<option value="customers">'.$langs->trans("ThirdPartyCustomers").'</option>';
  202. //$s.='<option value="customersidprof">'.$langs->trans("ThirdPartyCustomersWithIdProf12",$langs->trans("ProfId1"),$langs->trans("ProfId2")).'</option>';
  203. $s .= '<option value="suppliers">'.$langs->trans("ThirdPartySuppliers").'</option>';
  204. $s .= '</select>';
  205. $s .= ' ';
  206. // Filter on thirdparty category
  207. $s .= $langs->trans("CustomersProspectsCategoriesShort").' ';
  208. $sql = "SELECT c.label, count(distinct(sp.email)) AS nb";
  209. $sql .= " FROM ";
  210. $sql .= " ".MAIN_DB_PREFIX."socpeople as sp,";
  211. $sql .= " ".MAIN_DB_PREFIX."categorie as c,";
  212. $sql .= " ".MAIN_DB_PREFIX."categorie_societe as cs";
  213. $sql .= " WHERE sp.entity IN (".getEntity('socpeople').")";
  214. $sql .= " AND sp.email <> ''"; // Note that null != '' is false
  215. $sql .= " AND sp.statut = 1";
  216. $sql .= " AND cs.fk_categorie = c.rowid";
  217. $sql .= " AND cs.fk_soc = sp.fk_soc";
  218. $sql .= " GROUP BY c.label";
  219. $sql .= " ORDER BY c.label";
  220. $resql = $this->db->query($sql);
  221. $s .= '<select name="filter_category_customer" class="flat marginrightonly maxwidth200">';
  222. $s .= '<option value="all">&nbsp;</option>';
  223. if ($resql) {
  224. $num = $this->db->num_rows($resql);
  225. if ($num) {
  226. $i = 0;
  227. while ($i < $num) {
  228. $obj = $this->db->fetch_object($resql);
  229. $s .= '<option value="'.$obj->label.'">'.$obj->label.' ('.$obj->nb.')</option>';
  230. $i++;
  231. }
  232. } else {
  233. $s .= '<option value="-1" disabled="disabled">'.$langs->trans("NoContactLinkedToThirdpartieWithCategoryFound").'</option>';
  234. }
  235. } else {
  236. dol_print_error($this->db);
  237. }
  238. $s .= '</select>';
  239. $s .= ' ';
  240. // Filter on thirdparty category
  241. $s .= $langs->trans("SuppliersCategoriesShort").' ';
  242. $sql = "SELECT c.label, count(distinct(sp.email)) AS nb";
  243. $sql .= " FROM ";
  244. $sql .= " ".MAIN_DB_PREFIX."socpeople as sp,";
  245. $sql .= " ".MAIN_DB_PREFIX."categorie as c,";
  246. $sql .= " ".MAIN_DB_PREFIX."categorie_fournisseur as cs";
  247. $sql .= " WHERE sp.entity IN (".getEntity('socpeople').")";
  248. $sql .= " AND sp.email <> ''"; // Note that null != '' is false
  249. $sql .= " AND sp.statut = 1";
  250. $sql .= " AND cs.fk_categorie = c.rowid";
  251. $sql .= " AND cs.fk_soc = sp.fk_soc";
  252. $sql .= " GROUP BY c.label";
  253. $sql .= " ORDER BY c.label";
  254. $resql = $this->db->query($sql);
  255. $s .= '<select name="filter_category_supplier" class="flat marginrightonly maxwidth200">';
  256. $s .= '<option value="all">&nbsp;</option>';
  257. if ($resql) {
  258. $num = $this->db->num_rows($resql);
  259. if ($num) {
  260. $i = 0;
  261. while ($i < $num) {
  262. $obj = $this->db->fetch_object($resql);
  263. $s .= '<option value="'.$obj->label.'">'.$obj->label.' ('.$obj->nb.')</option>';
  264. $i++;
  265. }
  266. } else {
  267. $s .= '<option value="-1" disabled="disabled">'.$langs->trans("NoContactLinkedToThirdpartieWithCategoryFound").'</option>';
  268. }
  269. } else {
  270. dol_print_error($this->db);
  271. }
  272. $s .= '</select>';
  273. return $s;
  274. }
  275. /**
  276. * Renvoie url lien vers fiche de la source du destinataire du mailing
  277. *
  278. * @param int $id ID
  279. * @return string Url lien
  280. */
  281. public function url($id)
  282. {
  283. return '<a href="'.DOL_URL_ROOT.'/contact/card.php?id='.$id.'">'.img_object('', "contact").'</a>';
  284. }
  285. // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  286. /**
  287. * Ajoute destinataires dans table des cibles
  288. *
  289. * @param int $mailing_id Id of emailing
  290. * @return int <0 si erreur, nb ajout si ok
  291. */
  292. public function add_to_target($mailing_id)
  293. {
  294. // phpcs:enable
  295. global $conf, $langs;
  296. $filter = GETPOST('filter', 'alpha');
  297. $filter_jobposition = GETPOST('filter_jobposition', 'alpha');
  298. $filter_category = GETPOST('filter_category', 'alpha');
  299. $filter_category_customer = GETPOST('filter_category_customer', 'alpha');
  300. $filter_category_supplier = GETPOST('filter_category_supplier', 'alpha');
  301. $cibles = array();
  302. // List prospects levels
  303. $prospectlevel = array();
  304. $sql = "SELECT code, label";
  305. $sql .= " FROM ".MAIN_DB_PREFIX."c_prospectlevel";
  306. $sql .= " WHERE active > 0";
  307. $sql .= " ORDER BY label";
  308. $resql = $this->db->query($sql);
  309. if ($resql) {
  310. $num = $this->db->num_rows($resql);
  311. $i = 0;
  312. while ($i < $num) {
  313. $obj = $this->db->fetch_object($resql);
  314. $prospectlevel[$obj->code] = $obj->label;
  315. $i++;
  316. }
  317. } else {
  318. dol_print_error($this->db);
  319. }
  320. // Request must return: id, email, fk_contact, lastname, firstname, other
  321. $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,";
  322. $sql .= " s.nom as companyname";
  323. $sql .= " FROM ".MAIN_DB_PREFIX."socpeople as sp";
  324. $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = sp.fk_soc";
  325. if ($filter_category <> 'all') {
  326. $sql .= ", ".MAIN_DB_PREFIX."categorie as c";
  327. }
  328. if ($filter_category <> 'all') {
  329. $sql .= ", ".MAIN_DB_PREFIX."categorie_contact as cs";
  330. }
  331. if ($filter_category_customer <> 'all') {
  332. $sql .= ", ".MAIN_DB_PREFIX."categorie as c2";
  333. }
  334. if ($filter_category_customer <> 'all') {
  335. $sql .= ", ".MAIN_DB_PREFIX."categorie_societe as c2s";
  336. }
  337. if ($filter_category_supplier <> 'all') {
  338. $sql .= ", ".MAIN_DB_PREFIX."categorie as c3";
  339. }
  340. if ($filter_category_supplier <> 'all') {
  341. $sql .= ", ".MAIN_DB_PREFIX."categorie_fournisseur as c3s";
  342. }
  343. $sql .= " WHERE sp.entity IN (".getEntity('socpeople').")";
  344. $sql .= " AND sp.email <> ''";
  345. $sql .= " AND (SELECT count(*) FROM ".MAIN_DB_PREFIX."mailing_unsubscribe WHERE email = sp.email) = 0";
  346. // Exclude unsubscribed email adresses
  347. $sql .= " AND sp.statut = 1";
  348. $sql .= " AND sp.email NOT IN (SELECT email FROM ".MAIN_DB_PREFIX."mailing_cibles WHERE fk_mailing=".((int) $mailing_id).")";
  349. // Filter on category
  350. if ($filter_category <> 'all') {
  351. $sql .= " AND cs.fk_categorie = c.rowid AND cs.fk_socpeople = sp.rowid";
  352. }
  353. if ($filter_category <> 'all') {
  354. $sql .= " AND c.label = '".$this->db->escape($filter_category)."'";
  355. }
  356. if ($filter_category_customer <> 'all') {
  357. $sql .= " AND c2s.fk_categorie = c2.rowid AND c2s.fk_soc = sp.fk_soc";
  358. }
  359. if ($filter_category_customer <> 'all') {
  360. $sql .= " AND c2.label = '".$this->db->escape($filter_category_customer)."'";
  361. }
  362. if ($filter_category_supplier <> 'all') {
  363. $sql .= " AND c3s.fk_categorie = c3.rowid AND c3s.fk_soc = sp.fk_soc";
  364. }
  365. if ($filter_category_supplier <> 'all') {
  366. $sql .= " AND c3.label = '".$this->db->escape($filter_category_supplier)."'";
  367. }
  368. // Filter on nature
  369. $key = $filter;
  370. {
  371. //print "xx".$key;
  372. if ($key == 'prospects') {
  373. $sql .= " AND s.client=2";
  374. }
  375. foreach ($prospectlevel as $codelevel => $valuelevel) {
  376. if ($key == 'prospectslevel'.$codelevel) {
  377. $sql .= " AND s.fk_prospectlevel='".$this->db->escape($codelevel)."'";
  378. }
  379. }
  380. if ($key == 'customers') {
  381. $sql .= " AND s.client=1";
  382. }
  383. if ($key == 'suppliers') {
  384. $sql .= " AND s.fournisseur=1";
  385. }
  386. }
  387. // Filter on job position
  388. $key = $filter_jobposition;
  389. if (!empty($key) && $key != 'all') {
  390. $sql .= " AND sp.poste ='".$this->db->escape($key)."'";
  391. }
  392. $sql .= " ORDER BY sp.email";
  393. //print "wwwwwwx".$sql;
  394. // Stocke destinataires dans cibles
  395. $result = $this->db->query($sql);
  396. if ($result) {
  397. $num = $this->db->num_rows($result);
  398. $i = 0;
  399. $j = 0;
  400. dol_syslog(get_class($this)."::add_to_target mailing ".$num." targets found");
  401. $old = '';
  402. while ($i < $num) {
  403. $obj = $this->db->fetch_object($result);
  404. if ($old <> $obj->email) {
  405. $cibles[$j] = array(
  406. 'email' => $obj->email,
  407. 'fk_contact' => $obj->fk_contact,
  408. 'lastname' => $obj->lastname,
  409. 'firstname' => $obj->firstname,
  410. 'other' =>
  411. ($langs->transnoentities("ThirdParty").'='.$obj->companyname).';'.
  412. ($langs->transnoentities("UserTitle").'='.($obj->civility_id ? $langs->transnoentities("Civility".$obj->civility_id) : '')).';'.
  413. ($langs->transnoentities("JobPosition").'='.$obj->jobposition),
  414. 'source_url' => $this->url($obj->id),
  415. 'source_id' => $obj->id,
  416. 'source_type' => 'contact'
  417. );
  418. $old = $obj->email;
  419. $j++;
  420. }
  421. $i++;
  422. }
  423. } else {
  424. dol_syslog($this->db->error());
  425. $this->error = $this->db->error();
  426. return -1;
  427. }
  428. return parent::addTargetsToDatabase($mailing_id, $cibles);
  429. }
  430. }