3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
10 * Copyright © 2010-2014 The Galette Team
12 * This file is part of Galette (http://galette.tuxfamily.org).
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.
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.
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/>.
27 * @category Repository
30 * @author Johan Cwiklinski <johan@x-tnd.be>
31 * @copyright 2010-2014 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
34 * @link http://galette.tuxfamily.org
35 * @since Available since 0.7dev - 2010-03-11
38 namespace Galette\Repository
;
41 use Laminas\Db\Sql\Expression
;
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
;
52 * Contributions class for galette
55 * @category Repository
58 * @author Johan Cwiklinski <johan@x-tnd.be>
59 * @copyright 2009-2014 The Galette Team
60 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
61 * @link http://galette.tuxfamily.org
65 const TABLE
= Contribution
::TABLE
;
66 const PK
= Contribution
::PK
;
68 private $count = null;
75 * @param Db $zdb Database
76 * @param Login $login Login
77 * @param ContributionsList $filters Filtering
79 public function __construct(Db
$zdb, Login
$login, $filters = null)
82 $this->login
= $login;
84 if ($filters === null) {
85 $this->filters
= new ContributionsList();
87 $this->filters
= $filters;
92 * Get contributions list for a specific transaction
94 * @param int $trans_id Transaction identifier
96 * @return Contribution[]
98 public function getListFromTransaction($trans_id)
100 $this->filters
->from_transaction
= $trans_id;
101 return $this->getList(true);
105 * Get contributions list
107 * @param bool $as_contrib return the results as an array of
108 * Contribution object.
109 * @param array $fields field(s) name(s) to get. Should be a string or
110 * an array. If null, all fields will be
112 * @param boolean $count true if we want to count members
114 * @return Contribution[]|ResultSet
116 public function getList($as_contrib = false, $fields = null, $count = true)
119 $select = $this->buildSelect($fields, $count);
121 $this->filters
->setLimits($select);
123 $contributions = array();
124 $results = $this->zdb
->execute($select);
126 foreach ($results as $row) {
127 $contributions[] = new Contribution($this->zdb
, $this->login
, $row);
130 $contributions = $results;
132 return $contributions;
133 } catch (\Exception
$e) {
135 'Cannot list contributions | ' . $e->getMessage(),
143 * Builds the SELECT statement
145 * @param array $fields fields list to retrieve
146 * @param bool $count true if we want to count members
147 * (not applicable from static calls), defaults to false
149 * @return string SELECT statement
151 private function buildSelect($fields, $count = false)
154 $fieldsList = ($fields != null)
155 ?
((!is_array($fields) ||
count($fields) < 1) ?
(array)'*'
156 : implode(', ', $fields)) : (array)'*';
158 $select = $this->zdb
->select(self
::TABLE
, 'a');
159 $select->columns($fieldsList);
162 array('p' => PREFIX_DB
. Adherent
::TABLE
),
163 'a.' . Adherent
::PK
. '= p.' . Adherent
::PK
166 $this->buildWhereClause($select);
167 $select->order(self
::buildOrderClause());
169 $this->calculateSum($select);
172 $this->proceedCount($select);
176 } catch (\Exception
$e) {
178 'Cannot build SELECT clause for contributions | ' . $e->getMessage(),
186 * Count contributions from the query
188 * @param Select $select Original select
192 private function proceedCount($select)
195 $countSelect = clone $select;
196 $countSelect->reset($countSelect::COLUMNS
);
197 $countSelect->reset($countSelect::JOINS
);
198 $countSelect->reset($countSelect::ORDER
);
199 $countSelect->columns(
201 self
::PK
=> new Expression('COUNT(' . self
::PK
. ')')
205 $results = $this->zdb
->execute($countSelect);
206 $result = $results->current();
209 $this->count
= $result->$k;
211 if ($this->count
> 0) {
212 $this->filters
->setCounter($this->count
);
214 } catch (\Exception
$e) {
216 'Cannot count contributions | ' . $e->getMessage(),
224 * Calculate sum of all selected contributions
226 * @param Select $select Original select
230 private function calculateSum($select)
233 $sumSelect = clone $select;
234 $sumSelect->reset($sumSelect::COLUMNS
);
235 $sumSelect->reset($sumSelect::JOINS
);
236 $sumSelect->reset($sumSelect::ORDER
);
239 'contribsum' => new Expression('SUM(montant_cotis)')
243 $results = $this->zdb
->execute($sumSelect);
244 $result = $results->current();
246 $this->sum
= round($result->contribsum
, 2);
247 } catch (\Exception
$e) {
249 'Cannot calculate contributions sum | ' . $e->getMessage(),
257 * Builds the order clause
259 * @return string SQL ORDER clause
261 private function buildOrderClause()
265 switch ($this->filters
->orderby
) {
266 case ContributionsList
::ORDERBY_DATE
:
267 $order[] = 'date_enreg ' . $this->filters
->ordered
;
269 case ContributionsList
::ORDERBY_BEGIN_DATE
:
270 $order[] = 'date_debut_cotis ' . $this->filters
->ordered
;
272 case ContributionsList
::ORDERBY_END_DATE
:
273 $order[] = 'date_fin_cotis ' . $this->filters
->ordered
;
275 case ContributionsList
::ORDERBY_MEMBER
:
276 $order[] = 'nom_adh ' . $this->filters
->ordered
;
277 $order[] = 'prenom_adh ' . $this->filters
->ordered
;
279 case ContributionsList
::ORDERBY_TYPE
:
280 $order[] = ContributionsTypes
::PK
;
282 case ContributionsList
::ORDERBY_AMOUNT
:
283 $order[] = 'montant_cotis ' . $this->filters
->ordered
;
286 Hum... I really do not know how to sort a query with a value that
287 is calculated code side :/
288 case ContributionsList::ORDERBY_DURATION:
290 case ContributionsList
::ORDERBY_PAYMENT_TYPE
:
291 $order[] = 'type_paiement_cotis ' . $this->ordered
;
294 $order[] = $this->filters
->orderby
. ' ' . $this->filters
->ordered
;
302 * Builds where clause, for filtering on simple list mode
304 * @param Select $select Original select
306 * @return string SQL WHERE clause
308 private function buildWhereClause($select)
310 $field = 'date_debut_cotis';
312 switch ($this->filters
->date_field
) {
313 case ContributionsList
::DATE_RECORD
:
314 $field = 'date_enreg';
316 case ContributionsList
::DATE_END
:
317 $field = 'date_fin_cotis';
319 case ContributionsList
::DATE_BEGIN
:
321 $field = 'date_debut_cotis';
326 if ($this->filters
->start_date_filter
!= null) {
327 $d = new \
DateTime($this->filters
->start_date_filter
);
328 $select->where
->greaterThanOrEqualTo(
334 if ($this->filters
->end_date_filter
!= null) {
335 $d = new \
DateTime($this->filters
->end_date_filter
);
336 $select->where
->lessThanOrEqualTo(
342 if ($this->filters
->payment_type_filter
!== null) {
343 $select->where
->equalTo(
344 'type_paiement_cotis',
345 $this->filters
->payment_type_filter
349 if ($this->filters
->from_transaction
!== false) {
350 $select->where
->equalTo(
352 $this->filters
->from_transaction
356 if ($this->filters
->max_amount
!== null && is_int($this->filters
->max_amount
)) {
358 '(montant_cotis <= ' . $this->filters
->max_amount
.
359 ' OR montant_cotis IS NULL)'
363 if (!$this->login
->isAdmin() && !$this->login
->isStaff()) {
364 //non staff members can only view their own contributions
367 'a.' . Adherent
::PK
=> $this->login
->id
370 } elseif ($this->filters
->filtre_cotis_adh
!= null) {
372 'a.' . Adherent
::PK
. ' = ' . $this->filters
->filtre_cotis_adh
375 if ($this->filters
->filtre_transactions
=== true) {
376 $select->where('a.trans_id IS NULL');
378 } catch (\Exception
$e) {
380 __METHOD__
. ' | ' . $e->getMessage(),
387 * Get count for current query
391 public function getCount()
401 public function getSum()
407 * Remove specified contributions
409 * @param integer|array $ids Contributions identifiers to delete
410 * @param History $hist History
411 * @param boolean $transaction True to begin a database transaction
415 public function remove($ids, History
$hist, $transaction = true)
418 if (is_numeric($ids)) {
419 //we've got only one identifier
425 if (is_array($list)) {
429 $this->zdb
->connection
->beginTransaction();
431 $select = $this->zdb
->select(self
::TABLE
);
432 $select->where
->in(self
::PK
, $list);
433 $contributions = $this->zdb
->execute($select);
434 foreach ($contributions as $contribution) {
435 $c = new Contribution($this->zdb
, $this->login
, $contribution);
436 $res = $c->remove(false);
437 if ($res === false) {
438 throw new \Exception
;
442 $this->zdb
->connection
->commit();
447 print_r($list, true),
448 _T("Contributions deleted (%list)")
452 } catch (\Exception
$e) {
454 $this->zdb
->connection
->rollBack();
457 'An error occurred trying to remove contributions | ' .
464 //not numeric and not an array: incorrect.
466 'Asking to remove contribution, but without providing an array or a single numeric value.',