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