galette/lib/Galette/Core/MailingHistory.php

   1 <?php
   2
   3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
   4
   5 /**
   6  * Mailing features
   7  *
   8  * PHP version 5
   9  *
  10  * Copyright © 2009-2021 The Galette Team
  11  *
  12  * This file is part of Galette (http://galette.tuxfamily.org).
  13  *
  14  * Galette is free software: you can redistribute it and/or modify
  15  * it under the terms of the GNU General Public License as published by
  16  * the Free Software Foundation, either version 3 of the License, or
  17  * (at your option) any later version.
  18  *
  19  * Galette is distributed in the hope that it will be useful,
  20  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  21  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  22  *  GNU General Public License for more details.
  23  *
  24  * You should have received a copy of the GNU General Public License
  25  * along with Galette. If not, see <http://www.gnu.org/licenses/>.
  26  *
  27  * @category  Core
  28  * @package   Galette
  29  *
  30  * @author    Johan Cwiklinski <johan@x-tnd.be>
  31  * @copyright 2011-2021 The Galette Team
  32  * @license   http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
  33  * @link      http://galette.tuxfamily.org
  34  * @since     Available since 0.7dev - 2011-08-27
  35  */
  36
  37 namespace Galette\Core;
  38
  39 use Throwable;
  40 use Analog\Analog;
  41 use Galette\Core\Db;
  42 use Galette\Core\Login;
  43 use Galette\Entity\Adherent;
  44 use Galette\Filters\MailingsList;
  45 use Laminas\Db\Sql\Expression;
  46
  47 /**
  48  * Mailing features
  49  *
  50  * @category  Core
  51  * @name      MailingHistory
  52  * @package   Galette
  53  * @author    Johan Cwiklinski <johan@x-tnd.be>
  54  * @copyright 2011-2021 The Galette Team
  55  * @license   http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
  56  * @link      http://galette.tuxfamily.org
  57  * @since     Available since 0.7dev - 2011-08-27
  58  */
  59 class MailingHistory extends History
  60 {
  61     public const TABLE = 'mailing_history';
  62     public const PK = 'mailing_id';
  63
  64     public const FILTER_DC_SENT = 0;
  65     public const FILTER_SENT = 1;
  66     public const FILTER_NOT_SENT = 2;
  67
  68     private $mailing = null;
  69     private $id;
  70     private $date;
  71     private $subject;
  72     private $message;
  73     private $recipients;
  74     private $sender;
  75     private $sender_name;
  76     private $sender_address;
  77     private $sent = false;
  78
  79     private $senders;
  80
  81     /**
  82      * Default constructor
  83      *
  84      * @param Db                $zdb         Database
  85      * @param Login             $login       Login
  86      * @param Preferences       $preferences Preferences
  87      * @param MailingsList|null $filters     Filtering
  88      * @param Mailing|null      $mailing     Mailing
  89      */
  90     public function __construct(Db $zdb, Login $login, Preferences $preferences, MailingsList $filters = null, Mailing $mailing = null)
  91     {
  92         parent::__construct($zdb, $login, $preferences, $filters);
  93         $this->mailing = $mailing;
  94     }
  95
  96     /**
  97      * Get the entire history list
  98      *
  99      * @return array
 100      */
 101     public function getHistory()
 102     {
 103         try {
 104             $select = $this->zdb->select($this->getTableName(), 'a');
 105             $select->join(
 106                 array('b' => PREFIX_DB . Adherent::TABLE),
 107                 'a.mailing_sender=b.' . Adherent::PK,
 108                 array('nom_adh', 'prenom_adh'),
 109                 $select::JOIN_LEFT
 110             );
 111             $this->buildWhereClause($select);
 112             $select->order($this->buildOrderClause());
 113             $this->buildLists($select);
 114             $this->proceedCount($select);
 115             //add limits to retrieve only relevant rows
 116             $this->filters->setLimits($select);
 117             $results = $this->zdb->execute($select);
 118
 119             $ret = array();
 120             foreach ($results as $r) {
 121                 if ($r['mailing_sender'] !== null && $r['mailing_sender_name'] === null) {
 122                     $r['mailing_sender_name']
 123                         = Adherent::getSName($this->zdb, $r['mailing_sender']);
 124                 }
 125                 $body_resume = $r['mailing_body'];
 126                 if (strlen($body_resume) > 150) {
 127                     $body_resume = substr($body_resume, 0, 150);
 128                     $body_resume .= '[...]';
 129                 }
 130                 if (function_exists('tidy_parse_string')) {
 131                     //if tidy extension is present, we use it to clean a bit
 132                     $tidy_config = array(
 133                         'clean'             => true,
 134                         'show-body-only'    => true,
 135                         'wrap' => 0,
 136                     );
 137                     $tidy = tidy_parse_string($body_resume, $tidy_config, 'UTF8');
 138                     $tidy->cleanRepair();
 139                     $r['mailing_body_resume'] = tidy_get_output($tidy);
 140                 } else {
 141                     //if it is not... Well, let's serve the text as it.
 142                     $r['mailing_body_resume'] = $body_resume;
 143                 }
 144
 145                 $attachments = 0;
 146                 if (file_exists(GALETTE_ATTACHMENTS_PATH . $r[self::PK])) {
 147                     $rdi = new \RecursiveDirectoryIterator(
 148                         GALETTE_ATTACHMENTS_PATH . $r[self::PK],
 149                         \FilesystemIterator::SKIP_DOTS
 150                     );
 151                     $contents = new \RecursiveIteratorIterator(
 152                         $rdi,
 153                         \RecursiveIteratorIterator::CHILD_FIRST
 154                     );
 155                     foreach ($contents as $path) {
 156                         if ($path->isFile()) {
 157                             $attachments++;
 158                         }
 159                     }
 160                 }
 161                 $r['attachments'] = $attachments;
 162                 $ret[] = $r;
 163             }
 164             return $ret;
 165         } catch (Throwable $e) {
 166             Analog::log(
 167                 'Unable to get history. | ' . $e->getMessage(),
 168                 Analog::WARNING
 169             );
 170             throw $e;
 171         }
 172     }
 173
 174     /**
 175      * Builds users and actions lists
 176      *
 177      * @param \Laminas\Db\Sql\Select $select Original select
 178      *
 179      * @return void
 180      */
 181     private function buildLists($select)
 182     {
 183         try {
 184             $select = $this->zdb->select(self::TABLE);
 185             $select->quantifier('DISTINCT')->columns(['mailing_sender']);
 186             $select->order(['mailing_sender ASC']);
 187
 188             $results = $this->zdb->execute($select);
 189
 190             $this->senders = [];
 191             foreach ($results as $result) {
 192                 $sender = $result->mailing_sender;
 193                 if ($sender != null) {
 194                     $this->senders[$sender] = Adherent::getSName($this->zdb, (int)$sender);
 195                 } elseif ($result->mailing_sender_name != null || $result->mailing_sender_address != null) {
 196                     $this->senders[$result->mailing_sender_address] = $result->mailing_sender_name;
 197                 } else {
 198                     $this->senders[-1] = _('Superadmin');
 199                 }
 200             }
 201         } catch (Throwable $e) {
 202             Analog::log(
 203                 'Cannot list senders from mailing history! | ' . $e->getMessage(),
 204                 Analog::WARNING
 205             );
 206             throw $e;
 207         }
 208     }
 209
 210     /**
 211      * Builds the order clause
 212      *
 213      * @return string SQL ORDER clause
 214      */
 215     protected function buildOrderClause()
 216     {
 217         $order = array();
 218
 219         switch ($this->filters->orderby) {
 220             case MailingsList::ORDERBY_DATE:
 221                 $order[] = 'mailing_date ' . $this->filters->ordered;
 222                 break;
 223             case MailingsList::ORDERBY_SENDER:
 224                 $order[] = 'mailing_sender ' . $this->filters->ordered;
 225                 break;
 226             case MailingsList::ORDERBY_SUBJECT:
 227                 $order[] = 'mailing_subject ' . $this->filters->ordered;
 228                 break;
 229             case MailingsList::ORDERBY_SENT:
 230                 $order[] = 'mailing_sent ' . $this->filters->ordered;
 231                 break;
 232         }
 233
 234         return $order;
 235     }
 236
 237     /**
 238      * Builds where clause, for filtering on simple list mode
 239      *
 240      * @param Select $select Original select
 241      *
 242      * @return string SQL WHERE clause
 243      */
 244     private function buildWhereClause($select)
 245     {
 246         try {
 247             if ($this->filters->start_date_filter != null) {
 248                 $d = new \DateTime($this->filters->raw_start_date_filter);
 249                 $select->where->greaterThanOrEqualTo(
 250                     'mailing_date',
 251                     $d->format('Y-m-d')
 252                 );
 253             }
 254
 255             if ($this->filters->end_date_filter != null) {
 256                 $d = new \DateTime($this->filters->raw_end_date_filter);
 257                 $select->where->lessThanOrEqualTo(
 258                     'mailing_date',
 259                     $d->format('Y-m-d')
 260                 );
 261             }
 262
 263             if ($this->filters->sender_filter != null && $this->filters->sender_filter != '0') {
 264                 $sender = $this->filters->sender_filter;
 265                 if ($sender == '-1') {
 266                     $select->where('mailing_sender IS NULL');
 267                 } else {
 268                     $select->where->equalTo(
 269                         'mailing_sender',
 270                         $sender
 271                     );
 272                 }
 273             }
 274
 275             switch ($this->filters->sent_filter) {
 276                 case self::FILTER_SENT:
 277                     $select->where('mailing_sent = true');
 278                     break;
 279                 case self::FILTER_NOT_SENT:
 280                     $select->where('mailing_sent = false');
 281                     break;
 282                 case self::FILTER_DC_SENT:
 283                     //nothing to do here.
 284                     break;
 285             }
 286
 287
 288             if ($this->filters->subject_filter != '') {
 289                 $token = $this->zdb->platform->quoteValue(
 290                     '%' . strtolower($this->filters->subject_filter) . '%'
 291                 );
 292
 293                 $select->where(
 294                     'LOWER(mailing_subject) LIKE ' .
 295                     $token
 296                 );
 297             }
 298         } catch (Throwable $e) {
 299             Analog::log(
 300                 __METHOD__ . ' | ' . $e->getMessage(),
 301                 Analog::WARNING
 302             );
 303             throw $e;
 304         }
 305     }
 306
 307     /**
 308      * Count history entries from the query
 309      *
 310      * @param Select $select Original select
 311      *
 312      * @return void
 313      */
 314     private function proceedCount($select)
 315     {
 316         try {
 317             $countSelect = clone $select;
 318             $countSelect->reset($countSelect::COLUMNS);
 319             $countSelect->reset($countSelect::JOINS);
 320             $countSelect->reset($countSelect::ORDER);
 321             $countSelect->columns(
 322                 array(
 323                     self::PK => new Expression('COUNT(' . self::PK . ')')
 324                 )
 325             );
 326
 327             $results = $this->zdb->execute($countSelect);
 328             $result = $results->current();
 329
 330             $k = self::PK;
 331             $this->count = $result->$k;
 332             if ($this->count > 0) {
 333                 $this->filters->setCounter($this->count);
 334             }
 335         } catch (Throwable $e) {
 336             Analog::log(
 337                 'Cannot count history | ' . $e->getMessage(),
 338                 Analog::WARNING
 339             );
 340             throw $e;
 341         }
 342     }
 343
 344     /**
 345      * Load mailing from an existing one
 346      *
 347      * @param Db             $zdb     Database instance
 348      * @param integer        $id      Model identifier
 349      * @param GaletteMailing $mailing Mailing object
 350      * @param boolean        $new     True if we create a 'new' mailing,
 351      *                                false otherwise (from preview for example)
 352      *
 353      * @return boolean
 354      */
 355     public static function loadFrom(Db $zdb, $id, $mailing, $new = true)
 356     {
 357         try {
 358             $select = $zdb->select(self::TABLE);
 359             $select->where('mailing_id = ' . $id);
 360
 361             $results = $zdb->execute($select);
 362             $result = $results->current();
 363
 364             return $mailing->loadFromHistory($result, $new);
 365         } catch (Throwable $e) {
 366             Analog::log(
 367                 'Unable to load mailing model #' . $id . ' | ' .
 368                 $e->getMessage(),
 369                 Analog::WARNING
 370             );
 371             throw $e;
 372         }
 373     }
 374
 375     /**
 376      * Store a mailing in the history
 377      *
 378      * @param boolean $sent Defaults to false
 379      *
 380      * @return boolean
 381      */
 382     public function storeMailing($sent = false)
 383     {
 384         if ($this->mailing instanceof Mailing) {
 385             if ($this->mailing->sender_name != null) {
 386                 $this->sender_name = $this->mailing->getSenderName();
 387                 $this->sender_address = $this->mailing->getSenderAddress();
 388             }
 389             $this->sender = $this->login->id;
 390             $this->subject = $this->mailing->subject;
 391             $this->message = $this->mailing->message;
 392             $this->recipients = $this->mailing->recipients;
 393             $this->sent = $sent;
 394             $this->date = date('Y-m-d H:i:s');
 395             if (!$this->mailing->existsInHistory()) {
 396                 $this->store();
 397                 $this->mailing->id = $this->id;
 398                 $this->mailing->moveAttachments($this->id);
 399             } else {
 400                 if ($this->mailing->tmp_path !== false) {
 401                     //attachments are still in a temporary path, move them
 402                     $this->mailing->moveAttachments($this->id ?? $this->mailing->history_id);
 403                 }
 404                 //existing stored mailing. Just update row.
 405                 $this->update();
 406             }
 407         } else {
 408             Analog::log(
 409                 '[' . __METHOD__ .
 410                 '] Mailing should be an instance of Mailing',
 411                 Analog::ERROR
 412             );
 413         }
 414     }
 415
 416     /**
 417      * Update in the database
 418      *
 419      * @return boolean
 420      */
 421     public function update()
 422     {
 423         try {
 424             $_recipients = array();
 425             if ($this->recipients != null) {
 426                 foreach ($this->recipients as $_r) {
 427                     $_recipients[$_r->id] = $_r->sname . ' <' . $_r->email . '>';
 428                 }
 429             }
 430
 431             $sender = ($this->sender === 0) ?
 432                 new Expression('NULL') : $this->sender;
 433             $sender_name = ($this->sender_name === null) ?
 434                 new Expression('NULL') : $this->sender_name;
 435             $sender_address = ($this->sender_address === null) ?
 436                 new Expression('NULL') : $this->sender_address;
 437
 438             $values = array(
 439                 'mailing_sender'            => $sender,
 440                 'mailing_sender_name'       => $sender_name,
 441                 'mailing_sender_address'    => $sender_address,
 442                 'mailing_subject'           => $this->subject,
 443                 'mailing_body'              => $this->message,
 444                 'mailing_date'              => $this->date,
 445                 'mailing_recipients'        => serialize($_recipients),
 446                 'mailing_sent'              => ($this->sent) ?
 447                     true :
 448                     ($this->zdb->isPostgres() ? 'false' : 0)
 449             );
 450
 451             $update = $this->zdb->update(self::TABLE);
 452             $update->set($values);
 453             $update->where(self::PK . ' = ' . $this->mailing->history_id);
 454             $this->zdb->execute($update);
 455             return true;
 456         } catch (Throwable $e) {
 457             Analog::log(
 458                 'An error occurend updating Mailing | ' . $e->getMessage(),
 459                 Analog::ERROR
 460             );
 461             throw $e;
 462         }
 463     }
 464
 465     /**
 466      * Store in the database
 467      *
 468      * @return boolean
 469      */
 470     public function store()
 471     {
 472         try {
 473             $_recipients = array();
 474             if ($this->recipients != null) {
 475                 foreach ($this->recipients as $_r) {
 476                     $_recipients[$_r->id] = $_r->sname . ' <' . $_r->email . '>';
 477                 }
 478             }
 479
 480             $sender = null;
 481             if ($this->sender === 0) {
 482                 $sender = new Expression('NULL');
 483             } else {
 484                 $sender = $this->sender;
 485             }
 486             $sender_name = ($this->sender_name === null) ?
 487                 new Expression('NULL') : $this->sender_name;
 488             $sender_address = ($this->sender_address === null) ?
 489                 new Expression('NULL') : $this->sender_address;
 490
 491             $values = array(
 492                 'mailing_sender'            => $sender,
 493                 'mailing_sender_name'       => $sender_name,
 494                 'mailing_sender_address'    => $sender_address,
 495                 'mailing_subject'           => $this->subject,
 496                 'mailing_body'              => $this->message,
 497                 'mailing_date'              => $this->date,
 498                 'mailing_recipients'        => serialize($_recipients),
 499                 'mailing_sent'              => ($this->sent) ?
 500                     true :
 501                     ($this->zdb->isPostgres() ? 'false' : 0)
 502             );
 503
 504             $insert = $this->zdb->insert(self::TABLE);
 505             $insert->values($values);
 506             $this->zdb->execute($insert);
 507
 508             $this->id = $this->zdb->getLastGeneratedValue($this);
 509             return true;
 510         } catch (Throwable $e) {
 511             Analog::log(
 512                 'An error occurend storing Mailing | ' . $e->getMessage(),
 513                 Analog::ERROR
 514             );
 515             throw $e;
 516         }
 517     }
 518
 519     /**
 520      * Remove specified entries
 521      *
 522      * @param integer|array $ids  Mailing history entries identifiers
 523      * @param History       $hist History instance
 524      *
 525      * @return boolean
 526      */
 527     public function removeEntries($ids, History $hist)
 528     {
 529         $list = array();
 530         if (is_numeric($ids)) {
 531             //we've got only one identifier
 532             $list[] = $ids;
 533         } else {
 534             $list = $ids;
 535         }
 536
 537         if (is_array($list)) {
 538             try {
 539                 foreach ($list as $id) {
 540                     $mailing = new Mailing($this->preferences, [], $id);
 541                     $mailing->removeAttachments();
 542                 }
 543
 544                 $this->zdb->connection->beginTransaction();
 545
 546                 //delete members
 547                 $delete = $this->zdb->delete(self::TABLE);
 548                 $delete->where->in(self::PK, $list);
 549                 $this->zdb->execute($delete);
 550
 551                 //commit all changes
 552                 $this->zdb->connection->commit();
 553
 554                 //add an history entry
 555                 $hist->add(
 556                     _T("Delete mailing entries")
 557                 );
 558
 559                 return true;
 560             } catch (Throwable $e) {
 561                 $this->zdb->connection->rollBack();
 562                 Analog::log(
 563                     'Unable to delete selected mailing history entries |' .
 564                     $e->getMessage(),
 565                     Analog::ERROR
 566                 );
 567                 return false;
 568             }
 569         } else {
 570             //not numeric and not an array: incorrect.
 571             Analog::log(
 572                 'Asking to remove mailing entries, but without ' .
 573                 'providing an array or a single numeric value.',
 574                 Analog::WARNING
 575             );
 576             return false;
 577         }
 578     }
 579
 580     /**
 581      * Get table's name
 582      *
 583      * @param boolean $prefixed Whether table name should be prefixed
 584      *
 585      * @return string
 586      */
 587     protected function getTableName($prefixed = false)
 588     {
 589         if ($prefixed === true) {
 590             return PREFIX_DB . self::TABLE;
 591         } else {
 592             return self::TABLE;
 593         }
 594     }
 595
 596     /**
 597      * Get table's PK
 598      *
 599      * @return string
 600      */
 601     protected function getPk()
 602     {
 603         return self::PK;
 604     }
 605
 606     /**
 607      * Get count for current query
 608      *
 609      * @return int
 610      */
 611     public function getCount()
 612     {
 613         return $this->count;
 614     }
 615
 616     /**
 617      * Get senders list
 618      *
 619      * @return array
 620      */
 621     public function getSendersList()
 622     {
 623         return $this->senders;
 624     }
 625 }