galette/lib/Galette/Repository/Contributions.php

   1 <?php
   2
   3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
   4
   5 /**
   6  * Contributions class
   7  *
   8  * PHP version 5
   9  *
  10  * Copyright © 2010-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  Repository
  28  * @package   Galette
  29  *
  30  * @author    Johan Cwiklinski <johan@x-tnd.be>
  31  * @copyright 2010-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 - 2010-03-11
  35  */
  36
  37 namespace Galette\Repository;
  38
  39 use Throwable;
  40 use Analog\Analog;
  41 use Laminas\Db\Sql\Expression;
  42 use Galette\Core\Db;
  43 use Galette\Core\Login;
  44 use Galette\Core\History;
  45 use Galette\Entity\Contribution;
  46 use Galette\Entity\Adherent;
  47 use Galette\Entity\Transaction;
  48 use Galette\Entity\ContributionsTypes;
  49 use Galette\Filters\ContributionsList;
  50 use Laminas\Db\Sql\Select;
  51
  52 /**
  53  * Contributions class for galette
  54  *
  55  * @name Contributions
  56  * @category  Repository
  57  * @package   Galette
  58  *
  59  * @author    Johan Cwiklinski <johan@x-tnd.be>
  60  * @copyright 2009-2021 The Galette Team
  61  * @license   http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
  62  * @link      http://galette.tuxfamily.org
  63  */
  64 class Contributions
  65 {
  66     public const TABLE = Contribution::TABLE;
  67     public const PK = Contribution::PK;
  68
  69     private $count = null;
  70
  71     private $sum;
  72
  73     /**
  74      * Default constructor
  75      *
  76      * @param Db                $zdb     Database
  77      * @param Login             $login   Login
  78      * @param ContributionsList $filters Filtering
  79      */
  80     public function __construct(Db $zdb, Login $login, $filters = null)
  81     {
  82         $this->zdb = $zdb;
  83         $this->login = $login;
  84
  85         if ($filters === null) {
  86             $this->filters = new ContributionsList();
  87         } else {
  88             $this->filters = $filters;
  89         }
  90     }
  91
  92     /**
  93      * Get contributions list for a specific transaction
  94      *
  95      * @param int $trans_id Transaction identifier
  96      *
  97      * @return Contribution[]
  98      */
  99     public function getListFromTransaction($trans_id)
 100     {
 101         $this->filters->from_transaction = $trans_id;
 102         return $this->getList(true);
 103     }
 104
 105     /**
 106      * Get contributions list
 107      *
 108      * @param bool    $as_contrib return the results as an array of
 109      *                            Contribution object.
 110      * @param array   $fields     field(s) name(s) to get. Should be a string or
 111      *                            an array. If null, all fields will be
 112      *                            returned
 113      * @param boolean $count      true if we want to count members
 114      *
 115      * @return Contribution[]|ResultSet
 116      */
 117     public function getList($as_contrib = false, $fields = null, $count = true)
 118     {
 119         try {
 120             $select = $this->buildSelect($fields, $count);
 121
 122             $this->filters->setLimits($select);
 123
 124             $contributions = array();
 125             $results = $this->zdb->execute($select);
 126             if ($as_contrib) {
 127                 foreach ($results as $row) {
 128                     $contributions[] = new Contribution($this->zdb, $this->login, $row);
 129                 }
 130             } else {
 131                 $contributions = $results;
 132             }
 133             return $contributions;
 134         } catch (Throwable $e) {
 135             Analog::log(
 136                 'Cannot list contributions | ' . $e->getMessage(),
 137                 Analog::WARNING
 138             );
 139             throw $e;
 140         }
 141     }
 142
 143     /**
 144      * Builds the SELECT statement
 145      *
 146      * @param array $fields fields list to retrieve
 147      * @param bool  $count  true if we want to count members
 148      *                      (not applicable from static calls), defaults to false
 149      *
 150      * @return string SELECT statement
 151      */
 152     private function buildSelect($fields, $count = false)
 153     {
 154         try {
 155             $fieldsList = ($fields != null)
 156                             ? ((!is_array($fields) || count($fields) < 1) ? (array)'*'
 157                             : implode(', ', $fields)) : (array)'*';
 158
 159             $select = $this->zdb->select(self::TABLE, 'a');
 160             $select->columns($fieldsList);
 161
 162             $select->join(
 163                 array('p' => PREFIX_DB . Adherent::TABLE),
 164                 'a.' . Adherent::PK . '= p.' . Adherent::PK
 165             );
 166
 167             $this->buildWhereClause($select);
 168             $select->order(self::buildOrderClause());
 169
 170             $this->calculateSum($select);
 171
 172             if ($count) {
 173                 $this->proceedCount($select);
 174             }
 175
 176             return $select;
 177         } catch (Throwable $e) {
 178             Analog::log(
 179                 'Cannot build SELECT clause for contributions | ' . $e->getMessage(),
 180                 Analog::WARNING
 181             );
 182             throw $e;
 183         }
 184     }
 185
 186     /**
 187      * Count contributions from the query
 188      *
 189      * @param Select $select Original select
 190      *
 191      * @return void
 192      */
 193     private function proceedCount(Select $select)
 194     {
 195         try {
 196             $countSelect = clone $select;
 197             $countSelect->reset($countSelect::COLUMNS);
 198             $countSelect->reset($countSelect::JOINS);
 199             $countSelect->reset($countSelect::ORDER);
 200             $countSelect->columns(
 201                 array(
 202                     self::PK => new Expression('COUNT(' . self::PK . ')')
 203                 )
 204             );
 205
 206             $results = $this->zdb->execute($countSelect);
 207             $result = $results->current();
 208
 209             $k = self::PK;
 210             $this->count = $result->$k;
 211
 212             if ($this->count > 0) {
 213                 $this->filters->setCounter($this->count);
 214             }
 215         } catch (Throwable $e) {
 216             Analog::log(
 217                 'Cannot count contributions | ' . $e->getMessage(),
 218                 Analog::WARNING
 219             );
 220             throw $e;
 221         }
 222     }
 223
 224     /**
 225      * Calculate sum of all selected contributions
 226      *
 227      * @param Select $select Original select
 228      *
 229      * @return void
 230      */
 231     private function calculateSum(Select $select)
 232     {
 233         try {
 234             $sumSelect = clone $select;
 235             $sumSelect->reset($sumSelect::COLUMNS);
 236             $sumSelect->reset($sumSelect::JOINS);
 237             $sumSelect->reset($sumSelect::ORDER);
 238             $sumSelect->columns(
 239                 array(
 240                     'contribsum' => new Expression('SUM(montant_cotis)')
 241                 )
 242             );
 243
 244             $results = $this->zdb->execute($sumSelect);
 245             $result = $results->current();
 246
 247             $this->sum = round($result->contribsum, 2);
 248         } catch (Throwable $e) {
 249             Analog::log(
 250                 'Cannot calculate contributions sum | ' . $e->getMessage(),
 251                 Analog::WARNING
 252             );
 253             throw $e;
 254         }
 255     }
 256
 257     /**
 258      * Builds the order clause
 259      *
 260      * @return string SQL ORDER clause
 261      */
 262     private function buildOrderClause()
 263     {
 264         $order = array();
 265
 266         switch ($this->filters->orderby) {
 267             case ContributionsList::ORDERBY_ID:
 268                 $order[] = Contribution::PK . ' ' . $this->filters->ordered;
 269                 break;
 270             case ContributionsList::ORDERBY_DATE:
 271                 $order[] = 'date_enreg ' . $this->filters->ordered;
 272                 break;
 273             case ContributionsList::ORDERBY_BEGIN_DATE:
 274                 $order[] = 'date_debut_cotis ' . $this->filters->ordered;
 275                 break;
 276             case ContributionsList::ORDERBY_END_DATE:
 277                 $order[] = 'date_fin_cotis ' . $this->filters->ordered;
 278                 break;
 279             case ContributionsList::ORDERBY_MEMBER:
 280                 $order[] = 'nom_adh ' . $this->filters->ordered;
 281                 $order[] = 'prenom_adh ' . $this->filters->ordered;
 282                 break;
 283             case ContributionsList::ORDERBY_TYPE:
 284                 $order[] = ContributionsTypes::PK;
 285                 break;
 286             case ContributionsList::ORDERBY_AMOUNT:
 287                 $order[] = 'montant_cotis ' . $this->filters->ordered;
 288                 break;
 289             /*
 290             Hum... I really do not know how to sort a query with a value that
 291             is calculated code side :/
 292             case ContributionsList::ORDERBY_DURATION:
 293                 break;*/
 294             case ContributionsList::ORDERBY_PAYMENT_TYPE:
 295                 $order[] = 'type_paiement_cotis ' . $this->filters->ordered;
 296                 break;
 297             default:
 298                 $order[] = $this->filters->orderby . ' ' . $this->filters->ordered;
 299                 break;
 300         }
 301
 302         return $order;
 303     }
 304
 305     /**
 306      * Builds where clause, for filtering on simple list mode
 307      *
 308      * @param Select $select Original select
 309      *
 310      * @return string SQL WHERE clause
 311      */
 312     private function buildWhereClause(Select $select)
 313     {
 314         $field = 'date_debut_cotis';
 315
 316         switch ($this->filters->date_field) {
 317             case ContributionsList::DATE_RECORD:
 318                 $field = 'date_enreg';
 319                 break;
 320             case ContributionsList::DATE_END:
 321                 $field = 'date_fin_cotis';
 322                 break;
 323             case ContributionsList::DATE_BEGIN:
 324             default:
 325                 $field = 'date_debut_cotis';
 326                 break;
 327         }
 328
 329         try {
 330             if ($this->filters->start_date_filter != null) {
 331                 $d = new \DateTime($this->filters->rstart_date_filter);
 332                 $select->where->greaterThanOrEqualTo(
 333                     $field,
 334                     $d->format('Y-m-d')
 335                 );
 336             }
 337
 338             if ($this->filters->end_date_filter != null) {
 339                 $d = new \DateTime($this->filters->rend_date_filter);
 340                 $select->where->lessThanOrEqualTo(
 341                     $field,
 342                     $d->format('Y-m-d')
 343                 );
 344             }
 345
 346             if ($this->filters->payment_type_filter !== null) {
 347                 $select->where->equalTo(
 348                     'type_paiement_cotis',
 349                     $this->filters->payment_type_filter
 350                 );
 351             }
 352
 353             if ($this->filters->from_transaction !== false) {
 354                 $select->where->equalTo(
 355                     Transaction::PK,
 356                     $this->filters->from_transaction
 357                 );
 358             }
 359
 360             if ($this->filters->max_amount !== null && is_int($this->filters->max_amount)) {
 361                 $select->where(
 362                     '(montant_cotis <= ' . $this->filters->max_amount .
 363                     ' OR montant_cotis IS NULL)'
 364                 );
 365             }
 366
 367             $member_clause = null;
 368             if ($this->filters->filtre_cotis_adh != null) {
 369                 $member_clause = [$this->filters->filtre_cotis_adh];
 370                 if (!$this->login->isAdmin() && !$this->login->isStaff() && $this->filters->filtre_cotis_adh != $this->login->id) {
 371                     $member = new Adherent(
 372                         $this->zdb,
 373                         (int)$this->filters->filtre_cotis_adh,
 374                         [
 375                             'picture' => false,
 376                             'groups' => false,
 377                             'dues' => false,
 378                             'parent' => true
 379                         ]
 380                     );
 381                     if (
 382                         !$member->hasParent() ||
 383                         $member->hasParent() && $member->parent->id != $this->login->id
 384                     ) {
 385                         Analog::log(
 386                             'Trying to display contributions for member #' . $member->id .
 387                             ' without appropriate ACLs',
 388                             Analog::WARNING
 389                         );
 390                         $this->filters->filtre_cotis_adh = $this->login->id;
 391                         $member_clause = [$this->login->id];
 392                     }
 393                 }
 394             } elseif ($this->filters->filtre_cotis_children !== false) {
 395                 $member_clause = [$this->login->id];
 396                 $member = new Adherent(
 397                     $this->zdb,
 398                     (int)$this->filters->filtre_cotis_children,
 399                     [
 400                         'picture'   => false,
 401                         'groups'    => false,
 402                         'dues'      => false,
 403                         'children'  => true
 404                     ]
 405                 );
 406                 foreach ($member->children as $child) {
 407                     $member_clause[] = $child->id;
 408                 }
 409             } elseif (!$this->login->isAdmin() && !$this->login->isStaff()) {
 410                 //non staff members can only view their own contributions
 411                 $member_clause = $this->login->id;
 412             }
 413
 414             if ($member_clause !== null) {
 415                 $select->where(
 416                     array(
 417                         'a.' . Adherent::PK => $member_clause
 418                     )
 419                 );
 420             }
 421
 422             if ($this->filters->filtre_transactions === true) {
 423                 $select->where('a.trans_id IS NULL');
 424             }
 425         } catch (Throwable $e) {
 426             Analog::log(
 427                 __METHOD__ . ' | ' . $e->getMessage(),
 428                 Analog::WARNING
 429             );
 430             throw $e;
 431         }
 432     }
 433
 434     /**
 435      * Get count for current query
 436      *
 437      * @return int
 438      */
 439     public function getCount()
 440     {
 441         return $this->count;
 442     }
 443
 444     /**
 445      * Get sum
 446      *
 447      * @return int
 448      */
 449     public function getSum()
 450     {
 451         return $this->sum;
 452     }
 453
 454     /**
 455      * Remove specified contributions
 456      *
 457      * @param integer|array $ids         Contributions identifiers to delete
 458      * @param History       $hist        History
 459      * @param boolean       $transaction True to begin a database transaction
 460      *
 461      * @return boolean
 462      */
 463     public function remove($ids, History $hist, $transaction = true)
 464     {
 465         $list = array();
 466         if (is_numeric($ids)) {
 467             //we've got only one identifier
 468             $list[] = $ids;
 469         } else {
 470             $list = $ids;
 471         }
 472
 473         if (is_array($list)) {
 474             $res = true;
 475             try {
 476                 if ($transaction) {
 477                     $this->zdb->connection->beginTransaction();
 478                 }
 479                 $select = $this->zdb->select(self::TABLE);
 480                 $select->where->in(self::PK, $list);
 481                 $contributions = $this->zdb->execute($select);
 482                 foreach ($contributions as $contribution) {
 483                     $c = new Contribution($this->zdb, $this->login, $contribution);
 484                     $res = $c->remove(false);
 485                     if ($res === false) {
 486                         throw new \Exception();
 487                     }
 488                 }
 489                 if ($transaction) {
 490                     $this->zdb->connection->commit();
 491                 }
 492                 $hist->add(
 493                     str_replace(
 494                         '%list',
 495                         print_r($list, true),
 496                         _T("Contributions deleted (%list)")
 497                     )
 498                 );
 499                 return true;
 500             } catch (Throwable $e) {
 501                 if ($transaction) {
 502                     $this->zdb->connection->rollBack();
 503                 }
 504                 Analog::log(
 505                     'An error occurred trying to remove contributions | ' .
 506                     $e->getMessage(),
 507                     Analog::ERROR
 508                 );
 509                 throw $e;
 510             }
 511         } else {
 512             //not numeric and not an array: incorrect.
 513             Analog::log(
 514                 'Asking to remove contribution, but without providing an array or a single numeric value.',
 515                 Analog::WARNING
 516             );
 517             return false;
 518         }
 519     }
 520 }