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-2023 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-2023 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 ArrayObject;
  40 use Laminas\Db\Sql\Select;
  41 use Throwable;
  42 use Analog\Analog;
  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-2023 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  * @property MailingsList $filters
  60  */
  61 class MailingHistory extends History
  62 {
  63     public const TABLE = 'mailing_history';
  64     public const PK = 'mailing_id';
  65
  66     public const FILTER_DC_SENT = 0;
  67     public const FILTER_SENT = 1;
  68     public const FILTER_NOT_SENT = 2;
  69
  70     private $mailing = null;
  71     private $id;
  72     private $date;
  73     private $subject;
  74     private $message;
  75     private $recipients;
  76     private $sender;
  77     private $sender_name;
  78     private $sender_address;
  79     private $sent = false;
  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->proceedCount($select);
 114             //add limits to retrieve only relevant rows
 115             $this->filters->setLimits($select);
 116             $results = $this->zdb->execute($select);
 117
 118             $ret = array();
 119             foreach ($results as $r) {
 120                 if ($r['mailing_sender'] !== null && $r['mailing_sender_name'] === null) {
 121                     $r['mailing_sender_name']
 122                         = Adherent::getSName($this->zdb, $r['mailing_sender']);
 123                 }
 124
 125                 $recipients = [];
 126                 if ($r['mailing_recipients'] != null) {
 127                     //FIXME: error suppression with @ must be removed, see https://bugs.galette.eu/issues/1744
 128                     $recipients = @unserialize($r['mailing_recipients']);
 129                 }
 130                 $r['mailing_recipients'] = $recipients;
 131
 132                 $attachments = 0;
 133                 if (file_exists(GALETTE_ATTACHMENTS_PATH . $r[self::PK])) {
 134                     $rdi = new \RecursiveDirectoryIterator(
 135                         GALETTE_ATTACHMENTS_PATH . $r[self::PK],
 136                         \FilesystemIterator::SKIP_DOTS
 137                     );
 138                     $contents = new \RecursiveIteratorIterator(
 139                         $rdi,
 140                         \RecursiveIteratorIterator::CHILD_FIRST
 141                     );
 142                     foreach ($contents as $path) {
 143                         if ($path->isFile()) {
 144                             $attachments++;
 145                         }
 146                     }
 147                 }
 148                 $r['attachments'] = $attachments;
 149                 $ret[] = $r;
 150             }
 151             return $ret;
 152         } catch (Throwable $e) {
 153             Analog::log(
 154                 'Unable to get history. | ' . $e->getMessage(),
 155                 Analog::WARNING
 156             );
 157             throw $e;
 158         }
 159     }
 160
 161     /**
 162      * Builds the order clause
 163      *
 164      * @return array SQL ORDER clauses
 165      */
 166     protected function buildOrderClause()
 167     {
 168         $order = array();
 169
 170         switch ($this->filters->orderby) {
 171             case MailingsList::ORDERBY_DATE:
 172                 $order[] = 'mailing_date ' . $this->filters->ordered;
 173                 break;
 174             case MailingsList::ORDERBY_SENDER:
 175                 $order[] = 'mailing_sender ' . $this->filters->ordered;
 176                 break;
 177             case MailingsList::ORDERBY_SUBJECT:
 178                 $order[] = 'mailing_subject ' . $this->filters->ordered;
 179                 break;
 180             case MailingsList::ORDERBY_SENT:
 181                 $order[] = 'mailing_sent ' . $this->filters->ordered;
 182                 break;
 183         }
 184
 185         return $order;
 186     }
 187
 188     /**
 189      * Builds where clause, for filtering on simple list mode
 190      *
 191      * @param Select $select Original select
 192      *
 193      * @return void
 194      */
 195     private function buildWhereClause($select)
 196     {
 197         try {
 198             if ($this->filters->start_date_filter != null) {
 199                 $d = new \DateTime($this->filters->raw_start_date_filter);
 200                 $select->where->greaterThanOrEqualTo(
 201                     'mailing_date',
 202                     $d->format('Y-m-d')
 203                 );
 204             }
 205
 206             if ($this->filters->end_date_filter != null) {
 207                 $d = new \DateTime($this->filters->raw_end_date_filter);
 208                 $select->where->lessThanOrEqualTo(
 209                     'mailing_date',
 210                     $d->format('Y-m-d')
 211                 );
 212             }
 213
 214             if ($this->filters->sender_filter != null && $this->filters->sender_filter != '0') {
 215                 $sender = $this->filters->sender_filter;
 216                 if ($sender == '-1') {
 217                     $select->where('mailing_sender IS NULL');
 218                 } else {
 219                     $select->where->equalTo(
 220                         'mailing_sender',
 221                         $sender
 222                     );
 223                 }
 224             }
 225
 226             switch ($this->filters->sent_filter) {
 227                 case self::FILTER_SENT:
 228                     $select->where('mailing_sent = true');
 229                     break;
 230                 case self::FILTER_NOT_SENT:
 231                     $select->where('mailing_sent = false');
 232                     break;
 233                 case self::FILTER_DC_SENT:
 234                     //nothing to do here.
 235                     break;
 236             }
 237
 238
 239             if ($this->filters->subject_filter != '') {
 240                 $token = $this->zdb->platform->quoteValue(
 241                     '%' . strtolower($this->filters->subject_filter) . '%'
 242                 );
 243
 244                 $select->where(
 245                     'LOWER(mailing_subject) LIKE ' .
 246                     $token
 247                 );
 248             }
 249         } catch (Throwable $e) {
 250             Analog::log(
 251                 __METHOD__ . ' | ' . $e->getMessage(),
 252                 Analog::WARNING
 253             );
 254             throw $e;
 255         }
 256     }
 257
 258     /**
 259      * Count history entries from the query
 260      *
 261      * @param Select $select Original select
 262      *
 263      * @return void
 264      */
 265     private function proceedCount($select)
 266     {
 267         try {
 268             $countSelect = clone $select;
 269             $countSelect->reset($countSelect::COLUMNS);
 270             $countSelect->reset($countSelect::JOINS);
 271             $countSelect->reset($countSelect::ORDER);
 272             $countSelect->columns(
 273                 array(
 274                     self::PK => new Expression('COUNT(' . self::PK . ')')
 275                 )
 276             );
 277
 278             $results = $this->zdb->execute($countSelect);
 279             $result = $results->current();
 280
 281             $k = self::PK;
 282             $this->count = $result->$k;
 283             $this->filters->setCounter($this->count);
 284         } catch (Throwable $e) {
 285             Analog::log(
 286                 'Cannot count history | ' . $e->getMessage(),
 287                 Analog::WARNING
 288             );
 289             throw $e;
 290         }
 291     }
 292
 293     /**
 294      * Load mailing from an existing one
 295      *
 296      * @param Db      $zdb     Database instance
 297      * @param integer $id      Model identifier
 298      * @param Mailing $mailing Mailing object
 299      * @param boolean $new     True if we create a 'new' mailing,
 300      *                         false otherwise (from preview for example)
 301      *
 302      * @return boolean
 303      */
 304     public static function loadFrom(Db $zdb, $id, $mailing, $new = true)
 305     {
 306         try {
 307             $select = $zdb->select(self::TABLE);
 308             $select->where(['mailing_id' => $id]);
 309
 310             $results = $zdb->execute($select);
 311             /** @var ArrayObject $result */
 312             $result = $results->current();
 313
 314             return $mailing->loadFromHistory($result, $new);
 315         } catch (Throwable $e) {
 316             Analog::log(
 317                 'Unable to load mailing model #' . $id . ' | ' .
 318                 $e->getMessage(),
 319                 Analog::WARNING
 320             );
 321             throw $e;
 322         }
 323     }
 324
 325     /**
 326      * Store a mailing in the history
 327      *
 328      * @param boolean $sent Defaults to false
 329      *
 330      * @return boolean
 331      */
 332     public function storeMailing($sent = false)
 333     {
 334         if ($this->mailing instanceof Mailing) {
 335             if ($this->mailing->sender_name != null) {
 336                 $this->sender_name = $this->mailing->getSenderName();
 337                 $this->sender_address = $this->mailing->getSenderAddress();
 338             }
 339             $this->sender = $this->login->id;
 340             $this->subject = $this->mailing->subject;
 341             $this->message = $this->mailing->message;
 342             $this->recipients = $this->mailing->recipients;
 343             $this->sent = $sent;
 344             $this->date = date('Y-m-d H:i:s');
 345             if (!$this->mailing->existsInHistory()) {
 346                 $this->store();
 347                 $this->mailing->id = $this->id;
 348                 $this->mailing->moveAttachments($this->id);
 349             } else {
 350                 if ($this->mailing->tmp_path !== false) {
 351                     //attachments are still in a temporary path, move them
 352                     $this->mailing->moveAttachments($this->id ?? $this->mailing->history_id);
 353                 }
 354                 //existing stored mailing. Just update row.
 355                 $this->update();
 356             }
 357             return true;
 358         } else {
 359             Analog::log(
 360                 '[' . __METHOD__ .
 361                 '] Mailing should be an instance of Mailing',
 362                 Analog::ERROR
 363             );
 364             return false;
 365         }
 366     }
 367
 368     /**
 369      * Update in the database
 370      *
 371      * @return boolean
 372      */
 373     public function update()
 374     {
 375         try {
 376             $_recipients = array();
 377             if ($this->recipients != null) {
 378                 foreach ($this->recipients as $_r) {
 379                     $_recipients[$_r->id] = $_r->sname . ' <' . $_r->email . '>';
 380                 }
 381             }
 382
 383             $sender = ($this->sender === 0) ?
 384                 new Expression('NULL') : $this->sender;
 385             $sender_name = ($this->sender_name === null) ?
 386                 new Expression('NULL') : $this->sender_name;
 387             $sender_address = ($this->sender_address === null) ?
 388                 new Expression('NULL') : $this->sender_address;
 389
 390             $values = array(
 391                 'mailing_sender'            => $sender,
 392                 'mailing_sender_name'       => $sender_name,
 393                 'mailing_sender_address'    => $sender_address,
 394                 'mailing_subject'           => $this->subject,
 395                 'mailing_body'              => $this->message,
 396                 'mailing_date'              => $this->date,
 397                 'mailing_recipients'        => serialize($_recipients),
 398                 'mailing_sent'              => ($this->sent) ?
 399                     true :
 400                     ($this->zdb->isPostgres() ? 'false' : 0)
 401             );
 402
 403             $update = $this->zdb->update(self::TABLE);
 404             $update->set($values);
 405             $update->where([self::PK => $this->mailing->history_id]);
 406             $this->zdb->execute($update);
 407             return true;
 408         } catch (Throwable $e) {
 409             Analog::log(
 410                 'An error occurend updating Mailing | ' . $e->getMessage(),
 411                 Analog::ERROR
 412             );
 413             throw $e;
 414         }
 415     }
 416
 417     /**
 418      * Store in the database
 419      *
 420      * @return boolean
 421      */
 422     public function store()
 423     {
 424         try {
 425             $_recipients = array();
 426             if ($this->recipients != null) {
 427                 foreach ($this->recipients as $_r) {
 428                     $_recipients[$_r->id] = $_r->sname . ' <' . $_r->email . '>';
 429                 }
 430             }
 431
 432             $sender = null;
 433             if ($this->sender === 0) {
 434                 $sender = new Expression('NULL');
 435             } else {
 436                 $sender = $this->sender;
 437             }
 438             $sender_name = ($this->sender_name === null) ?
 439                 new Expression('NULL') : $this->sender_name;
 440             $sender_address = ($this->sender_address === null) ?
 441                 new Expression('NULL') : $this->sender_address;
 442
 443             $values = array(
 444                 'mailing_sender'            => $sender,
 445                 'mailing_sender_name'       => $sender_name,
 446                 'mailing_sender_address'    => $sender_address,
 447                 'mailing_subject'           => $this->subject,
 448                 'mailing_body'              => $this->message,
 449                 'mailing_date'              => $this->date,
 450                 'mailing_recipients'        => serialize($_recipients),
 451                 'mailing_sent'              => ($this->sent) ?
 452                     true :
 453                     ($this->zdb->isPostgres() ? 'false' : 0)
 454             );
 455
 456             $insert = $this->zdb->insert(self::TABLE);
 457             $insert->values($values);
 458             $this->zdb->execute($insert);
 459
 460             $this->id = $this->zdb->getLastGeneratedValue($this);
 461             return true;
 462         } catch (Throwable $e) {
 463             Analog::log(
 464                 'An error occurend storing Mailing | ' . $e->getMessage(),
 465                 Analog::ERROR
 466             );
 467             throw $e;
 468         }
 469     }
 470
 471     /**
 472      * Remove specified entries
 473      *
 474      * @param integer|array $ids  Mailing history entries identifiers
 475      * @param History       $hist History instance
 476      *
 477      * @return boolean
 478      */
 479     public function removeEntries($ids, History $hist)
 480     {
 481         $list = array();
 482         if (is_array($ids)) {
 483             $list = $ids;
 484         } elseif (is_numeric($ids)) {
 485             $list = [(int)$ids];
 486         } else {
 487             //not numeric and not an array: incorrect.
 488             Analog::log(
 489                 'Asking to remove mailing entries, but without ' .
 490                 'providing an array or a single numeric value.',
 491                 Analog::WARNING
 492             );
 493             return false;
 494         }
 495
 496         try {
 497             foreach ($list as $id) {
 498                 $mailing = new Mailing($this->preferences, [], $id);
 499                 $mailing->removeAttachments();
 500             }
 501
 502             $this->zdb->connection->beginTransaction();
 503
 504             //delete members
 505             $delete = $this->zdb->delete(self::TABLE);
 506             $delete->where->in(self::PK, $list);
 507             $this->zdb->execute($delete);
 508
 509             //commit all changes
 510             $this->zdb->connection->commit();
 511
 512             //add an history entry
 513             $hist->add(
 514                 _T("Delete mailing entries")
 515             );
 516
 517             return true;
 518         } catch (Throwable $e) {
 519             $this->zdb->connection->rollBack();
 520             Analog::log(
 521                 'Unable to delete selected mailing history entries |' .
 522                 $e->getMessage(),
 523                 Analog::ERROR
 524             );
 525             return false;
 526         }
 527     }
 528
 529     /**
 530      * Get table's name
 531      *
 532      * @param boolean $prefixed Whether table name should be prefixed
 533      *
 534      * @return string
 535      */
 536     protected function getTableName($prefixed = false)
 537     {
 538         if ($prefixed === true) {
 539             return PREFIX_DB . self::TABLE;
 540         } else {
 541             return self::TABLE;
 542         }
 543     }
 544
 545     /**
 546      * Get table's PK
 547      *
 548      * @return string
 549      */
 550     protected function getPk()
 551     {
 552         return self::PK;
 553     }
 554
 555     /**
 556      * Get count for current query
 557      *
 558      * @return int
 559      */
 560     public function getCount()
 561     {
 562         return $this->count;
 563     }
 564 }