galette/lib/Galette/Core/MailingHistory.php

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