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-2018 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-2018 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 Analog\Analog;
  40 use Galette\Core\Db;
  41 use Galette\Core\Login;
  42 use Galette\Entity\Adherent;
  43 use Galette\Filters\MailingsList;
  44 use Laminas\Db\Sql\Expression;
  45
  46 /**
  47  * Mailing features
  48  *
  49  * @category  Core
  50  * @name      MailingHistory
  51  * @package   Galette
  52  * @author    Johan Cwiklinski <johan@x-tnd.be>
  53  * @copyright 2011-2018 The Galette Team
  54  * @license   http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
  55  * @link      http://galette.tuxfamily.org
  56  * @since     Available since 0.7dev - 2011-08-27
  57  */
  58 class MailingHistory extends History
  59 {
  60     public const TABLE = 'mailing_history';
  61     public const PK = 'mailing_id';
  62
  63     public const FILTER_DC_SENT = 0;
  64     public const FILTER_SENT = 1;
  65     public const FILTER_NOT_SENT = 2;
  66
  67     private $mailing = null;
  68     private $id;
  69     private $date;
  70     private $subject;
  71     private $message;
  72     private $recipients;
  73     private $sender;
  74     private $sender_name;
  75     private $sender_address;
  76     private $sent = false;
  77
  78     private $senders;
  79
  80     /**
  81      * Default constructor
  82      *
  83      * @param Db                $zdb         Database
  84      * @param Login             $login       Login
  85      * @param Preferences       $preferences Preferences
  86      * @param MailingsList|null $filters     Filtering
  87      * @param Mailing|null      $mailing     Mailing
  88      */
  89     public function __construct(Db $zdb, Login $login, Preferences $preferences, MailingsList $filters = null, Mailing $mailing = null)
  90     {
  91         parent::__construct($zdb, $login, $preferences, $filters);
  92         $this->mailing = $mailing;
  93     }
  94
  95     /**
  96      * Get the entire history list
  97      *
  98      * @return array
  99      */
 100     public function getHistory()
 101     {
 102         try {
 103             $select = $this->zdb->select($this->getTableName(), 'a');
 104             $select->join(
 105                 array('b' => PREFIX_DB . Adherent::TABLE),
 106                 'a.mailing_sender=b.' . Adherent::PK,
 107                 array('nom_adh', 'prenom_adh'),
 108                 $select::JOIN_LEFT
 109             );
 110             $this->buildWhereClause($select);
 111             $select->order(self::buildOrderClause());
 112             $this->buildLists($select);
 113             $this->proceedCount($select);
 114             //add limits to retrieve only relavant 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 (\Exception $e) {
 165             Analog::log(
 166                 'Unable to get history. | ' . $e->getMessage(),
 167                 Analog::WARNING
 168             );
 169             return false;
 170         }
 171     }
 172
 173     /**
 174      * Builds users and actions lists
 175      *
 176      * @param Select $select Original select
 177      *
 178      * @return void
 179      */
 180     private function buildLists($select)
 181     {
 182         try {
 183             $select = $this->zdb->select(self::TABLE);
 184             $select->quantifier('DISTINCT')->columns(['mailing_sender']);
 185             $select->order(['mailing_sender ASC']);
 186
 187             $results = $this->zdb->execute($select);
 188
 189             $this->senders = [];
 190             foreach ($results as $result) {
 191                 $sender = $result->mailing_sender;
 192                 if ($sender != null) {
 193                     $this->senders[$sender] = Adherent::getSName($this->zdb, (int)$sender);
 194                 } elseif ($result->mailing_sender_name != null || $result->mailing_sender_address != null) {
 195                     $this->senders[$result->mailing_sender_address] = $result->mailing_sender_name;
 196                 } else {
 197                     $this->senders[-1] = _('Superadmin');
 198                 }
 199             }
 200         } catch (\Exception $e) {
 201             Analog::log(
 202                 'Cannot list senders from mailing history! | ' . $e->getMessage(),
 203                 Analog::WARNING
 204             );
 205         }
 206     }
 207
 208     /**
 209      * Builds the order clause
 210      *
 211      * @return string SQL ORDER clause
 212      */
 213     protected function buildOrderClause()
 214     {
 215         $order = array();
 216
 217         switch ($this->filters->orderby) {
 218             case MailingsList::ORDERBY_DATE:
 219                 $order[] = 'mailing_date ' . $this->filters->ordered;
 220                 break;
 221             case MailingsList::ORDERBY_SENDER:
 222                 $order[] = 'mailing_sender ' . $this->filters->ordered;
 223                 break;
 224             case MailingsList::ORDERBY_SUBJECT:
 225                 $order[] = 'mailing_subject ' . $this->filters->ordered;
 226                 break;
 227             case MailingsList::ORDERBY_SENT:
 228                 $order[] = 'mailing_sent ' . $this->filters->ordered;
 229                 break;
 230         }
 231
 232         return $order;
 233     }
 234
 235     /**
 236      * Builds where clause, for filtering on simple list mode
 237      *
 238      * @param Select $select Original select
 239      *
 240      * @return string SQL WHERE clause
 241      */
 242     private function buildWhereClause($select)
 243     {
 244         try {
 245             if ($this->filters->start_date_filter != null) {
 246                 $d = new \DateTime($this->filters->raw_start_date_filter);
 247                 $select->where->greaterThanOrEqualTo(
 248                     'mailing_date',
 249                     $d->format('Y-m-d')
 250                 );
 251             }
 252
 253             if ($this->filters->end_date_filter != null) {
 254                 $d = new \DateTime($this->filters->raw_end_date_filter);
 255                 $select->where->lessThanOrEqualTo(
 256                     'mailing_date',
 257                     $d->format('Y-m-d')
 258                 );
 259             }
 260
 261             if ($this->filters->sender_filter != null && $this->filters->sender_filter != '0') {
 262                 $sender = $this->filters->sender_filter;
 263                 if ($sender == '-1') {
 264                     $select->where('mailing_sender IS NULL');
 265                 } else {
 266                     $select->where->equalTo(
 267                         'mailing_sender',
 268                         $sender
 269                     );
 270                 }
 271             }
 272
 273             switch ($this->filters->sent_filter) {
 274                 case self::FILTER_SENT:
 275                     $select->where('mailing_sent = true');
 276                     break;
 277                 case self::FILTER_NOT_SENT:
 278                     $select->where('mailing_sent = false');
 279                     break;
 280                 case self::FILTER_DC_SENT:
 281                     //nothing to do here.
 282                     break;
 283             }
 284
 285
 286             if ($this->filters->subject_filter != '') {
 287                 $token = $this->zdb->platform->quoteValue(
 288                     '%' . strtolower($this->filters->subject_filter) . '%'
 289                 );
 290
 291                 $select->where(
 292                     'LOWER(mailing_subject) LIKE ' .
 293                     $token
 294                 );
 295             }
 296         } catch (\Exception $e) {
 297             Analog::log(
 298                 __METHOD__ . ' | ' . $e->getMessage(),
 299                 Analog::WARNING
 300             );
 301         }
 302     }
 303
 304     /**
 305      * Count history entries from the query
 306      *
 307      * @param Select $select Original select
 308      *
 309      * @return void
 310      */
 311     private function proceedCount($select)
 312     {
 313         try {
 314             $countSelect = clone $select;
 315             $countSelect->reset($countSelect::COLUMNS);
 316             $countSelect->reset($countSelect::JOINS);
 317             $countSelect->reset($countSelect::ORDER);
 318             $countSelect->columns(
 319                 array(
 320                     self::PK => new Expression('COUNT(' . self::PK . ')')
 321                 )
 322             );
 323
 324             $results = $this->zdb->execute($countSelect);
 325             $result = $results->current();
 326
 327             $k = self::PK;
 328             $this->count = $result->$k;
 329             if ($this->count > 0) {
 330                 $this->filters->setCounter($this->count);
 331             }
 332         } catch (\Exception $e) {
 333             Analog::log(
 334                 'Cannot count history | ' . $e->getMessage(),
 335                 Analog::WARNING
 336             );
 337             return false;
 338         }
 339     }
 340
 341     /**
 342      * Load mailing from an existing one
 343      *
 344      * @param Db             $zdb     Database instance
 345      * @param integer        $id      Model identifier
 346      * @param GaletteMailing $mailing Mailing object
 347      * @param boolean        $new     True if we create a 'new' mailing,
 348      *                                false otherwise (from preview for example)
 349      *
 350      * @return boolean
 351      */
 352     public static function loadFrom(Db $zdb, $id, $mailing, $new = true)
 353     {
 354         try {
 355             $select = $zdb->select(self::TABLE);
 356             $select->where('mailing_id = ' . $id);
 357
 358             $results = $zdb->execute($select);
 359             $result = $results->current();
 360
 361             return $mailing->loadFromHistory($result, $new);
 362         } catch (\Exception $e) {
 363             Analog::log(
 364                 'Unable to load mailing model #' . $id . ' | ' .
 365                 $e->getMessage(),
 366                 Analog::WARNING
 367             );
 368             return false;
 369         }
 370     }
 371
 372     /**
 373      * Store a mailing in the history
 374      *
 375      * @param boolean $sent Defaults to false
 376      *
 377      * @return boolean
 378      */
 379     public function storeMailing($sent = false)
 380     {
 381         if ($this->mailing instanceof Mailing) {
 382             if ($this->mailing->sender_name != null) {
 383                 $this->sender_name = $this->mailing->getSenderName();
 384                 $this->sender_address = $this->mailing->getSenderAddress();
 385             }
 386             $this->sender = $this->login->id;
 387             $this->subject = $this->mailing->subject;
 388             $this->message = $this->mailing->message;
 389             $this->recipients = $this->mailing->recipients;
 390             $this->sent = $sent;
 391             $this->date = date('Y-m-d H:i:s');
 392             if (!$this->mailing->existsInHistory()) {
 393                 $this->store();
 394                 $this->mailing->id = $this->id;
 395                 $this->mailing->moveAttachments($this->id);
 396             } else {
 397                 if ($this->mailing->tmp_path !== false) {
 398                     //attachments are still in a temporary path, move them
 399                     $this->mailing->moveAttachments($this->id);
 400                 }
 401                 //existing stored mailing. Just update row.
 402                 $this->update();
 403             }
 404         } else {
 405             Analog::log(
 406                 '[' . __METHOD__ .
 407                 '] Mailing should be an instance of Mailing',
 408                 Analog::ERROR
 409             );
 410             return false;
 411         }
 412     }
 413
 414     /**
 415      * Update in the database
 416      *
 417      * @return boolean
 418      */
 419     public function update()
 420     {
 421         try {
 422             $_recipients = array();
 423             if ($this->recipients != null) {
 424                 foreach ($this->recipients as $_r) {
 425                     $_recipients[$_r->id] = $_r->sname . ' <' . $_r->email . '>';
 426                 }
 427             }
 428
 429             $sender = ($this->sender === 0) ?
 430                 new Expression('NULL') : $this->sender;
 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             $update = $this->zdb->update(self::TABLE);
 450             $update->set($values);
 451             $update->where(self::PK . ' = ' . $this->mailing->history_id);
 452             $this->zdb->execute($update);
 453             return true;
 454         } catch (\Exception $e) {
 455             Analog::log(
 456                 'An error occurend updating Mailing | ' . $e->getMessage(),
 457                 Analog::ERROR
 458             );
 459             return false;
 460         }
 461     }
 462
 463     /**
 464      * Store in the database
 465      *
 466      * @return boolean
 467      */
 468     public function store()
 469     {
 470         try {
 471             $_recipients = array();
 472             if ($this->recipients != null) {
 473                 foreach ($this->recipients as $_r) {
 474                     $_recipients[$_r->id] = $_r->sname . ' <' . $_r->email . '>';
 475                 }
 476             }
 477
 478             $sender = null;
 479             if ($this->sender === 0) {
 480                 $sender = new Expression('NULL');
 481             } else {
 482                 $sender = $this->sender;
 483             }
 484             $sender_name = ($this->sender_name === null) ?
 485                 new Expression('NULL') : $this->sender_name;
 486             $sender_address = ($this->sender_address === null) ?
 487                 new Expression('NULL') : $this->sender_address;
 488
 489             $values = array(
 490                 'mailing_sender'            => $sender,
 491                 'mailing_sender_name'       => $sender_name,
 492                 'mailing_sender_address'    => $sender_address,
 493                 'mailing_subject'           => $this->subject,
 494                 'mailing_body'              => $this->message,
 495                 'mailing_date'              => $this->date,
 496                 'mailing_recipients'        => serialize($_recipients),
 497                 'mailing_sent'              => ($this->sent) ?
 498                     true :
 499                     ($this->zdb->isPostgres() ? 'false' : 0)
 500             );
 501
 502             $insert = $this->zdb->insert(self::TABLE);
 503             $insert->values($values);
 504             $this->zdb->execute($insert);
 505
 506             if ($this->zdb->isPostgres()) {
 507                 $this->id = $this->zdb->driver->getLastGeneratedValue(
 508                     PREFIX_DB . 'mailing_history_id_seq'
 509                 );
 510             } else {
 511                 $this->id = $this->zdb->driver->getLastGeneratedValue();
 512             }
 513             return true;
 514         } catch (\Exception $e) {
 515             Analog::log(
 516                 'An error occurend storing Mailing | ' . $e->getMessage(),
 517                 Analog::ERROR
 518             );
 519             return false;
 520         }
 521     }
 522
 523     /**
 524      * Remove specified entries
 525      *
 526      * @param integer|array $ids Mailing history entries identifiers
 527      *
 528      * @return boolean
 529      */
 530     public function removeEntries($ids)
 531     {
 532         global $hist, $preferences;
 533
 534         $list = array();
 535         if (is_numeric($ids)) {
 536             //we've got only one identifier
 537             $list[] = $ids;
 538         } else {
 539             $list = $ids;
 540         }
 541
 542         if (is_array($list)) {
 543             try {
 544                 foreach ($list as $id) {
 545                     $mailing = new Mailing($preferences, [], $id);
 546                     $mailing->removeAttachments();
 547                 }
 548
 549                 $this->zdb->connection->beginTransaction();
 550
 551                 //delete members
 552                 $delete = $this->zdb->delete(self::TABLE);
 553                 $delete->where->in(self::PK, $list);
 554                 $this->zdb->execute($delete);
 555
 556                 //commit all changes
 557                 $this->zdb->connection->commit();
 558
 559                 //add an history entry
 560                 $hist->add(
 561                     _T("Delete mailing entries")
 562                 );
 563
 564                 return true;
 565             } catch (\Exception $e) {
 566                 $this->zdb->connection->rollBack();
 567                 Analog::log(
 568                     'Unable to delete selected mailing history entries |' .
 569                     $e->getMessage(),
 570                     Analog::ERROR
 571                 );
 572                 return false;
 573             }
 574         } else {
 575             //not numeric and not an array: incorrect.
 576             Analog::log(
 577                 'Asking to remove mailing entries, but without ' .
 578                 'providing an array or a single numeric value.',
 579                 Analog::WARNING
 580             );
 581             return false;
 582         }
 583     }
 584
 585     /**
 586      * Get table's name
 587      *
 588      * @param boolean $prefixed Whether table name should be prefixed
 589      *
 590      * @return string
 591      */
 592     protected function getTableName($prefixed = false)
 593     {
 594         if ($prefixed === true) {
 595             return PREFIX_DB . self::TABLE;
 596         } else {
 597             return self::TABLE;
 598         }
 599     }
 600
 601     /**
 602      * Get table's PK
 603      *
 604      * @return string
 605      */
 606     protected function getPk()
 607     {
 608         return self::PK;
 609     }
 610
 611     /**
 612      * Get count for current query
 613      *
 614      * @return int
 615      */
 616     public function getCount()
 617     {
 618         return $this->count;
 619     }
 620
 621     /**
 622      * Get senders list
 623      *
 624      * @return array
 625      */
 626     public function getSendersList()
 627     {
 628         return $this->senders;
 629     }
 630 }