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                 $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             $this->filters->setCounter($this->count);
 277         } catch (Throwable $e) {
 278             Analog::log(
 279                 'Cannot count history | ' . $e->getMessage(),
 280                 Analog::WARNING
 281             );
 282             throw $e;
 283         }
 284     }
 285
 286     /**
 287      * Load mailing from an existing one
 288      *
 289      * @param Db      $zdb     Database instance
 290      * @param integer $id      Model identifier
 291      * @param Mailing $mailing Mailing object
 292      * @param boolean $new     True if we create a 'new' mailing,
 293      *                         false otherwise (from preview for example)
 294      *
 295      * @return boolean
 296      */
 297     public static function loadFrom(Db $zdb, $id, $mailing, $new = true)
 298     {
 299         try {
 300             $select = $zdb->select(self::TABLE);
 301             $select->where(['mailing_id' => $id]);
 302
 303             $results = $zdb->execute($select);
 304             /** @var ArrayObject $result */
 305             $result = $results->current();
 306
 307             return $mailing->loadFromHistory($result, $new);
 308         } catch (Throwable $e) {
 309             Analog::log(
 310                 'Unable to load mailing model #' . $id . ' | ' .
 311                 $e->getMessage(),
 312                 Analog::WARNING
 313             );
 314             throw $e;
 315         }
 316     }
 317
 318     /**
 319      * Store a mailing in the history
 320      *
 321      * @param boolean $sent Defaults to false
 322      *
 323      * @return boolean
 324      */
 325     public function storeMailing($sent = false)
 326     {
 327         if ($this->mailing instanceof Mailing) {
 328             if ($this->mailing->sender_name != null) {
 329                 $this->sender_name = $this->mailing->getSenderName();
 330                 $this->sender_address = $this->mailing->getSenderAddress();
 331             }
 332             $this->sender = $this->login->id;
 333             $this->subject = $this->mailing->subject;
 334             $this->message = $this->mailing->message;
 335             $this->recipients = $this->mailing->recipients;
 336             $this->sent = $sent;
 337             $this->date = date('Y-m-d H:i:s');
 338             if (!$this->mailing->existsInHistory()) {
 339                 $this->store();
 340                 $this->mailing->id = $this->id;
 341                 $this->mailing->moveAttachments($this->id);
 342             } else {
 343                 if ($this->mailing->tmp_path !== false) {
 344                     //attachments are still in a temporary path, move them
 345                     $this->mailing->moveAttachments($this->id ?? $this->mailing->history_id);
 346                 }
 347                 //existing stored mailing. Just update row.
 348                 $this->update();
 349             }
 350             return true;
 351         } else {
 352             Analog::log(
 353                 '[' . __METHOD__ .
 354                 '] Mailing should be an instance of Mailing',
 355                 Analog::ERROR
 356             );
 357             return false;
 358         }
 359     }
 360
 361     /**
 362      * Update in the database
 363      *
 364      * @return boolean
 365      */
 366     public function update()
 367     {
 368         try {
 369             $_recipients = array();
 370             if ($this->recipients != null) {
 371                 foreach ($this->recipients as $_r) {
 372                     $_recipients[$_r->id] = $_r->sname . ' <' . $_r->email . '>';
 373                 }
 374             }
 375
 376             $sender = ($this->sender === 0) ?
 377                 new Expression('NULL') : $this->sender;
 378             $sender_name = ($this->sender_name === null) ?
 379                 new Expression('NULL') : $this->sender_name;
 380             $sender_address = ($this->sender_address === null) ?
 381                 new Expression('NULL') : $this->sender_address;
 382
 383             $values = array(
 384                 'mailing_sender'            => $sender,
 385                 'mailing_sender_name'       => $sender_name,
 386                 'mailing_sender_address'    => $sender_address,
 387                 'mailing_subject'           => $this->subject,
 388                 'mailing_body'              => $this->message,
 389                 'mailing_date'              => $this->date,
 390                 'mailing_recipients'        => serialize($_recipients),
 391                 'mailing_sent'              => ($this->sent) ?
 392                     true :
 393                     ($this->zdb->isPostgres() ? 'false' : 0)
 394             );
 395
 396             $update = $this->zdb->update(self::TABLE);
 397             $update->set($values);
 398             $update->where([self::PK => $this->mailing->history_id]);
 399             $this->zdb->execute($update);
 400             return true;
 401         } catch (Throwable $e) {
 402             Analog::log(
 403                 'An error occurend updating Mailing | ' . $e->getMessage(),
 404                 Analog::ERROR
 405             );
 406             throw $e;
 407         }
 408     }
 409
 410     /**
 411      * Store in the database
 412      *
 413      * @return boolean
 414      */
 415     public function store()
 416     {
 417         try {
 418             $_recipients = array();
 419             if ($this->recipients != null) {
 420                 foreach ($this->recipients as $_r) {
 421                     $_recipients[$_r->id] = $_r->sname . ' <' . $_r->email . '>';
 422                 }
 423             }
 424
 425             $sender = null;
 426             if ($this->sender === 0) {
 427                 $sender = new Expression('NULL');
 428             } else {
 429                 $sender = $this->sender;
 430             }
 431             $sender_name = ($this->sender_name === null) ?
 432                 new Expression('NULL') : $this->sender_name;
 433             $sender_address = ($this->sender_address === null) ?
 434                 new Expression('NULL') : $this->sender_address;
 435
 436             $values = array(
 437                 'mailing_sender'            => $sender,
 438                 'mailing_sender_name'       => $sender_name,
 439                 'mailing_sender_address'    => $sender_address,
 440                 'mailing_subject'           => $this->subject,
 441                 'mailing_body'              => $this->message,
 442                 'mailing_date'              => $this->date,
 443                 'mailing_recipients'        => serialize($_recipients),
 444                 'mailing_sent'              => ($this->sent) ?
 445                     true :
 446                     ($this->zdb->isPostgres() ? 'false' : 0)
 447             );
 448
 449             $insert = $this->zdb->insert(self::TABLE);
 450             $insert->values($values);
 451             $this->zdb->execute($insert);
 452
 453             $this->id = $this->zdb->getLastGeneratedValue($this);
 454             return true;
 455         } catch (Throwable $e) {
 456             Analog::log(
 457                 'An error occurend storing Mailing | ' . $e->getMessage(),
 458                 Analog::ERROR
 459             );
 460             throw $e;
 461         }
 462     }
 463
 464     /**
 465      * Remove specified entries
 466      *
 467      * @param integer|array $ids  Mailing history entries identifiers
 468      * @param History       $hist History instance
 469      *
 470      * @return boolean
 471      */
 472     public function removeEntries($ids, History $hist)
 473     {
 474         $list = array();
 475         if (is_numeric($ids)) {
 476             //we've got only one identifier
 477             $list[] = $ids;
 478         } else {
 479             $list = $ids;
 480         }
 481
 482         if (is_array($list)) {
 483             try {
 484                 foreach ($list as $id) {
 485                     $mailing = new Mailing($this->preferences, [], $id);
 486                     $mailing->removeAttachments();
 487                 }
 488
 489                 $this->zdb->connection->beginTransaction();
 490
 491                 //delete members
 492                 $delete = $this->zdb->delete(self::TABLE);
 493                 $delete->where->in(self::PK, $list);
 494                 $this->zdb->execute($delete);
 495
 496                 //commit all changes
 497                 $this->zdb->connection->commit();
 498
 499                 //add an history entry
 500                 $hist->add(
 501                     _T("Delete mailing entries")
 502                 );
 503
 504                 return true;
 505             } catch (Throwable $e) {
 506                 $this->zdb->connection->rollBack();
 507                 Analog::log(
 508                     'Unable to delete selected mailing history entries |' .
 509                     $e->getMessage(),
 510                     Analog::ERROR
 511                 );
 512                 return false;
 513             }
 514         } else {
 515             //not numeric and not an array: incorrect.
 516             Analog::log(
 517                 'Asking to remove mailing entries, but without ' .
 518                 'providing an array or a single numeric value.',
 519                 Analog::WARNING
 520             );
 521             return false;
 522         }
 523     }
 524
 525     /**
 526      * Get table's name
 527      *
 528      * @param boolean $prefixed Whether table name should be prefixed
 529      *
 530      * @return string
 531      */
 532     protected function getTableName($prefixed = false)
 533     {
 534         if ($prefixed === true) {
 535             return PREFIX_DB . self::TABLE;
 536         } else {
 537             return self::TABLE;
 538         }
 539     }
 540
 541     /**
 542      * Get table's PK
 543      *
 544      * @return string
 545      */
 546     protected function getPk()
 547     {
 548         return self::PK;
 549     }
 550
 551     /**
 552      * Get count for current query
 553      *
 554      * @return int
 555      */
 556     public function getCount()
 557     {
 558         return $this->count;
 559     }
 560 }