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