3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
10 * Copyright © 2009-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 2009-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 - 2009-02-28
38 namespace Galette\Repository
;
40 use Galette\DynamicFields\DynamicField
;
41 use Galette\Entity\DynamicFieldsHandle
;
44 use Zend\Db\Sql\Expression
;
45 use Zend\Db\Sql\Predicate\PredicateSet
;
46 use Zend\Db\Sql\Predicate\Operator
;
47 use Galette\Entity\Adherent
;
48 use Galette\Entity\Contribution
;
49 use Galette\Entity\Transaction
;
50 use Galette\Entity\Reminder
;
51 use Galette\Filters\MembersList
;
52 use Galette\Filters\AdvancedMembersList
;
53 use Galette\Core\Picture
;
54 use Galette\Entity\Group
;
55 use Galette\Repository\Groups
;
56 use Galette\Entity\Status
;
60 * Members class for galette
63 * @category Repository
66 * @author Johan Cwiklinski <johan@x-tnd.be>
67 * @copyright 2009-2014 The Galette Team
68 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
69 * @link http://galette.tuxfamily.org
73 const TABLE
= Adherent
::TABLE
;
74 const PK
= Adherent
::PK
;
76 const ALL_ACCOUNTS
= 0;
77 const ACTIVE_ACCOUNT
= 1;
78 const INACTIVE_ACCOUNT
= 2;
81 const SHOW_PUBLIC_LIST
= 1;
82 const SHOW_ARRAY_LIST
= 2;
84 const SHOW_MANAGED
= 4;
85 const SHOW_EXPORT
= 5;
87 const FILTER_NAME
= 0;
88 const FILTER_ADDRESS
= 1;
89 const FILTER_MAIL
= 2;
91 const FILTER_INFOS
= 4;
92 const FILTER_DC_EMAIL
= 5;
93 const FILTER_W_EMAIL
= 6;
94 const FILTER_WO_EMAIL
= 7;
95 const FILTER_COMPANY_NAME
= 8;
96 const FILTER_DC_PUBINFOS
= 9;
97 const FILTER_W_PUBINFOS
= 10;
98 const FILTER_WO_PUBINFOS
= 11;
99 const FILTER_NUMBER
= 12;
101 const MEMBERSHIP_ALL
= 0;
102 const MEMBERSHIP_UP2DATE
= 3;
103 const MEMBERSHIP_NEARLY
= 1;
104 const MEMBERSHIP_LATE
= 2;
105 const MEMBERSHIP_NEVER
= 4;
106 const MEMBERSHIP_STAFF
= 5;
107 const MEMBERSHIP_ADMIN
= 6;
108 const MEMBERSHIP_NONE
= 7;
110 const ORDERBY_NAME
= 0;
111 const ORDERBY_NICKNAME
= 1;
112 const ORDERBY_STATUS
= 2;
113 const ORDERBY_FEE_STATUS
= 3;
114 const ORDERBY_MODIFDATE
= 4;
115 const ORDERBY_ID
= 5;
117 const NON_STAFF_MEMBERS
= 30;
119 private $filters = false;
120 private $count = null;
121 private $errors = array();
124 * Default constructor
126 * @param MembersList $filters Filtering
128 public function __construct($filters = null)
130 if ($filters === null) {
131 $this->filters
= new MembersList();
133 $this->filters
= $filters;
138 * Get staff members list
140 * @param bool $as_members return the results as an array of
142 * @param array $fields field(s) name(s) to get. Should be a string or
143 * an array. If null, all fields will be
145 * @param boolean $count true if we want to count members
146 * @param boolean $limit true to LIMIT query
148 * @return Adherent[]|ResultSet
150 public function getStaffMembersList(
156 return $this->getMembersList(
167 * Get managed members list (for groups managers)
169 * @param bool $as_members return the results as an array of
171 * @param array $fields field(s) name(s) to get. Should be a string or
172 * an array. If null, all fields will be
174 * @param boolean $count true if we want to count members
175 * @param boolean $limit true to LIMIT query
177 * @return Adherent[]|ResultSet
179 public function getManagedMembersList(
185 return $this->getMembersList(
198 * @param bool $as_members return the results as an array of
200 * @param array $fields field(s) name(s) to get. Should be a string or
201 * an array. If null, all fields will be
203 * @param boolean $count true if we want to count members
204 * @param boolean $staff true if we want only staff members
205 * @param boolean $managed true if we want only managed groups
206 * @param boolean $limit true if we want records pagination
207 * @param boolean $export true if we are exporting
209 * @return Adherent[]|ResultSet
211 public function getMembersList(
222 if ($limit === true) {
223 //force count if limit is active
228 $_mode = self
::SHOW_LIST
;
229 if ($staff !== false) {
230 $_mode = self
::SHOW_STAFF
;
232 if ($managed !== false) {
233 $_mode = self
::SHOW_MANAGED
;
235 if ($export !== false) {
236 $_mode = self
::SHOW_EXPORT
;
239 $select = $this->buildSelect(
246 //add limits to retrieve only relavant rows
247 if ($limit === true) {
248 $this->filters
->setLimits($select);
251 $rows = $zdb->execute($select);
252 $this->filters
->query
= $zdb->query_string
;
260 foreach ($rows as $row) {
261 $members[] = new Adherent($zdb, $row, $deps);
267 } catch (\Exception
$e) {
269 'Cannot list members | ' . $e->getMessage(),
276 * Remove specified members
278 * @param integer|array $ids Members identifiers to delete
282 public function removeMembers($ids)
287 if (is_numeric($ids)) {
288 //we've got only one identifier
294 if (is_array($list)) {
296 $zdb->connection
->beginTransaction();
298 //Retrieve some informations
299 $select = $zdb->select(self
::TABLE
);
301 array(self
::PK
, 'nom_adh', 'prenom_adh')
302 )->where
->in(self
::PK
, $list);
304 $results = $zdb->execute($select);
307 foreach ($results as $member) {
308 $str_adh = $member->id_adh
. ' (' . $member->nom_adh
. ' ' .
309 $member->prenom_adh
. ')';
310 $infos .= $str_adh . "\n";
312 $p = new Picture($member->id_adh
);
313 if ($p->hasPicture()) {
314 if (!$p->delete(false)) {
316 'Unable to delete picture for member ' . $str_adh,
319 throw new \
Exception(
320 'Unable to delete picture for member ' .
325 _T("Member Picture deleted"),
332 //delete contributions
333 $del_qry = $zdb->delete(Contribution
::TABLE
);
338 $zdb->execute($del_qry);
341 $select = $zdb->select(Transaction
::TABLE
);
342 $select->where
->in(self
::PK
, $list);
343 $results = $zdb->execute($select);
345 //if members has transactions;
346 //reset link with other contributions
348 if ($results->count() > 0) {
350 foreach ($results as $transaction) {
351 $transactions[] = $transaction[Transaction
::PK
];
354 $update = $zdb->update(Contribution
::TABLE
);
356 Transaction
::PK
=> new Expression('NULL')
361 $zdb->execute($update);
364 //delete transactions
365 $del_qry = $zdb->delete(Transaction
::TABLE
);
366 $del_qry->where
->in(self
::PK
, $list);
367 $zdb->execute($del_qry);
369 //delete groups membership/mamagmentship
370 Groups
::removeMembersFromGroups($list);
373 $del_qry = $zdb->delete(Reminder
::TABLE
);
378 $zdb->execute($del_qry);
380 //delete dynamic fields values
381 $del_qry = $zdb->delete(DynamicFieldsHandle
::TABLE
);
382 $del_qry->where(['field_form' => 'adh']);
383 $del_qry->where
->in('item_id', $list);
384 $zdb->execute($del_qry);
387 $del_qry = $zdb->delete(self
::TABLE
);
392 $zdb->execute($del_qry);
395 $zdb->connection
->commit();
397 //add an history entry
399 _T("Delete members cards, transactions and dues"),
404 } catch (\Exception
$e) {
405 $zdb->connection
->rollBack();
406 if ($e instanceof \Zend_Db_Statement_Exception
407 && $e->getCode() == 23000
410 'Member still have existing dependencies in the ' .
411 'database, maybe a mailing or some content from a ' .
412 'plugin. Please remove dependencies before trying ' .
416 $this->errors
[] = _T("Cannot remove a member who still have dependencies (mailings, ...)");
419 'Unable to delete selected member(s) |' .
427 //not numeric and not an array: incorrect.
429 'Asking to remove members, but without providing an array or a single numeric value.',
439 * @param boolean $as_members return the results as an array of
441 * @param array $fields field(s) name(s) to get. Should be a string or
442 * an array. If null, all fields will be
445 * @return Adherent[]|ResultSet
447 public function getList($as_members = false, $fields = null)
449 return $this->getMembersList(
461 * Get members list with public informations available
463 * @param boolean $with_photos get only members which have uploaded a
464 * photo (for trombinoscope)
468 public function getPublicList($with_photos)
473 $select = $this->buildSelect(
474 self
::SHOW_PUBLIC_LIST
,
479 if ($this->filters
) {
480 $select->order($this->buildOrderClause(null));
483 $this->proceedCount($select);
485 $this->filters
->setLimits($select);
487 $results = $zdb->execute($select);
489 foreach ($results as $row) {
493 'picture' => $with_photos
495 $members[] = new Adherent($zdb, $row, $deps);
498 } catch (\Exception
$e) {
500 'Cannot list members with public informations (photos: '
501 . $with_photos . ') | ' . $e->getMessage(),
509 * Get list of members that has been selected
511 * @param array $ids an array of members id that has been selected
512 * @param array $orderby SQL order clause (optionnal)
513 * @param boolean $with_photos Should photos be loaded?
514 * @param boolean $as_members Return Adherent[] or simple ResultSet
515 * @param array $fields Fields to use
516 * @param boolean $export True if we are exporting
517 * @param boolean $dues True if load dues as Adherent dependency
518 * @param boolean $parent True if load parent as Adherent dependency
522 public function getArrayList(
525 $with_photos = false,
534 if (!is_array($ids) ||
count($ids) < 1) {
535 Analog
::log('No member selected for labels.', Analog
::INFO
);
540 $damode = self
::SHOW_ARRAY_LIST
;
541 if ($export === true) {
542 $damode = self
::SHOW_EXPORT
;
544 $select = $this->buildSelect(
550 $select->where
->in('a.' . self
::PK
, $ids);
551 if ($orderby != null && count($orderby) > 0) {
552 if (is_array($orderby)) {
553 foreach ($orderby as $o) {
557 $select->order($orderby);
561 $results = $zdb->execute($select);
564 foreach ($results as $o) {
566 'picture' => $with_photos,
571 if ($as_members === true) {
572 $members[] = new Adherent($zdb, $o, $deps);
578 } catch (\Exception
$e) {
580 'Cannot load members form ids array | ' . $e->getMessage(),
587 * Builds the SELECT statement
589 * @param int $mode the current mode (see self::SHOW_*)
590 * @param array $fields fields list to retrieve
591 * @param bool $photos true if we want to get only members with photos
592 * Default to false, only relevant for SHOW_PUBLIC_LIST
593 * @param bool $count true if we want to count members, defaults to false
595 * @return Select SELECT statement
597 private function buildSelect($mode, $fields, $photos, $count = false)
602 if ($fields != null && is_array($fields) && !in_array('id_adh', $fields)) {
603 $fields[] = 'id_adh';
605 $fieldsList = ( $fields != null )
606 ?
(( !is_array($fields) ||
count($fields) < 1 ) ?
(array)'*'
607 : $fields) : (array)'*';
609 $select = $zdb->select(self
::TABLE
, 'a');
611 $select->columns($fieldsList);
613 $select->quantifier('DISTINCT');
616 case self
::SHOW_STAFF
:
617 case self
::SHOW_LIST
:
618 case self
::SHOW_ARRAY_LIST
:
620 array('p' => PREFIX_DB
. Status
::TABLE
),
621 'a.' . Status
::PK
. '=p.' . Status
::PK
,
625 case self
::SHOW_EXPORT
:
626 //basically the same as above, but without any fields
628 array('p' => PREFIX_DB
. Status
::TABLE
),
629 'a.' . Status
::PK
. '=p.' . Status
::PK
,
633 case self
::SHOW_MANAGED
:
635 array('p' => PREFIX_DB
. Status
::TABLE
),
636 'a.' . Status
::PK
. '=p.' . Status
::PK
638 array('gr' => PREFIX_DB
. Group
::GROUPSUSERS_TABLE
),
639 'a.' . Adherent
::PK
. '=gr.' . Adherent
::PK
,
642 array('m' => PREFIX_DB
. Group
::GROUPSMANAGERS_TABLE
),
643 'gr.' . Group
::PK
. '=m.' . Group
::PK
,
645 )->where('m.' . Adherent
::PK
. ' = ' . $login->id
);
647 case self
::SHOW_PUBLIC_LIST
:
650 array('p' => PREFIX_DB
. Picture
::TABLE
),
651 'a.' . self
::PK
. '= p.' . self
::PK
657 //check for contributions filtering
658 if ($this->filters
instanceof AdvancedMembersList
659 && $this->filters
->withinContributions()
662 array('ct' => PREFIX_DB
. Contribution
::TABLE
),
663 'ct.' . self
::PK
. '=a.' . self
::PK
,
669 //check if there are dynamic fields in filter
673 if ($this->filters
instanceof AdvancedMembersList
674 && $this->filters
->free_search
675 && count($this->filters
->free_search
) > 0
676 && !isset($this->filters
->free_search
['empty'])
678 $free_searches = $this->filters
->free_search
;
679 foreach ($free_searches as $fs) {
680 if (strpos($fs['field'], 'dyn_') === 0) {
681 // simple dynamic fields
683 $dfs[] = str_replace('dyn_', '', $fs['field']);
688 //check if there are dynamic fields for contributions in filter
693 if ($this->filters
instanceof AdvancedMembersList
694 && $this->filters
->withinContributions()
696 if ($this->filters
->contrib_dynamic
697 && count($this->filters
->contrib_dynamic
) > 0
698 && !isset($this->filters
->contrib_dynamic
['empty'])
702 //check if there are dynamic fields in the filter
703 foreach ($this->filters
->contrib_dynamic
as $k => $cd) {
712 if ($hasDfc === true ||
$hasCdfc === true) {
714 array('dfc' => PREFIX_DB
. DynamicFieldsHandle
::TABLE
),
715 'dfc.item_id=ct.' . Contribution
::PK
,
721 // simple dynamic fields
722 if ($hasDf === true) {
723 foreach ($dfs as $df) {
724 $subselect = $zdb->select(DynamicFieldsHandle
::TABLE
, 'df');
727 'item_id' => 'item_id',
731 $subselect->where('df.field_form = \'adh\'');
732 $subselect->where('df.field_id = ' . $df);
734 array('df' . $df => $subselect),
735 'a.id_adh = df' . $df . '.item_id',
742 // choice dynamic fields
743 if ($hasCdfc === true) {
744 $cdf_field = 'cdf.id';
745 if (TYPE_DB
=== 'pgsql') {
746 $cdf_field .= '::text';
749 $cdf_field = 'cdfc.id';
750 if (TYPE_DB
=== 'pgsql') {
751 $cdf_field .= '::text';
753 foreach ($cdfcs as $cdf) {
754 $rcdf_field = str_replace(
760 array('cdfc' . $cdf => DynamicField
::getFixedValuesTableName($cdf, true)),
761 $rcdf_field . '=dfc.field_val',
768 if ($mode == self
::SHOW_LIST ||
$mode == self
::SHOW_MANAGED
) {
769 if ($this->filters
!== false) {
770 $this->buildWhereClause($select);
772 $select->order($this->buildOrderClause($fields));
773 } elseif ($mode == self
::SHOW_PUBLIC_LIST
) {
786 new Expression('true')
796 new Expression('true')
801 new Expression('true')
810 if ($mode === self
::SHOW_STAFF
) {
811 $select->where
->lessThan(
813 self
::NON_STAFF_MEMBERS
818 $this->proceedCount($select);
822 } catch (\Exception
$e) {
824 'Cannot build SELECT clause for members | ' . $e->getMessage(),
832 * Count members from the query
834 * @param Select $select Original select
838 private function proceedCount($select)
843 $countSelect = clone $select;
844 $countSelect->reset($countSelect::COLUMNS
);
845 $countSelect->reset($countSelect::ORDER
);
846 $countSelect->reset($countSelect::HAVING
);
847 $countSelect->columns(
849 'count' => new Expression('count(DISTINCT a.' . self
::PK
. ')')
853 $have = $select->having
;
854 if ($have->count() > 0) {
855 foreach ($have->getPredicates() as $h) {
856 $countSelect->where($h);
860 $results = $zdb->execute($countSelect);
862 $this->count
= $results->current()->count
;
863 if (isset($this->filters
) && $this->count
> 0) {
864 $this->filters
->setCounter($this->count
);
866 } catch (\Exception
$e) {
868 'Cannot count members | ' . $e->getMessage(),
876 * Builds the order clause
878 * @param array $fields Fields list to ensure ORDER clause
879 * references selected fields. Optionnal.
881 * @return string SQL ORDER clause
883 private function buildOrderClause($fields = null)
887 switch ($this->filters
->orderby
) {
888 case self
::ORDERBY_NICKNAME
:
889 if ($this->canOrderBy('pseudo_adh', $fields)) {
890 $order[] = 'pseudo_adh ' . $this->filters
->getDirection();
893 case self
::ORDERBY_STATUS
:
894 if ($this->canOrderBy('priorite_statut', $fields)) {
895 $order[] = 'priorite_statut ' . $this->filters
->getDirection();
898 case self
::ORDERBY_MODIFDATE
:
899 if ($this->canOrderBy('date_modif_adh', $fields)) {
900 $order[] = 'date_modif_adh ' . $this->filters
->getDirection();
903 case self
::ORDERBY_FEE_STATUS
:
904 if ($this->canOrderBy('bool_exempt_adh', $fields)) {
905 $order[] = 'bool_exempt_adh ' . $this->filters
->getDirection();
908 if ($this->canOrderBy('date_echeance', $fields)) {
909 $order[] = 'date_echeance ' . $this->filters
->getDirection();
912 case self
::ORDERBY_ID
:
913 if ($this->canOrderBy('id_adh', $fields)) {
914 $order[] = 'id_adh ' . $this->filters
->getDirection();
919 //anyways, we want to order by firstname, lastname
920 if ($this->canOrderBy('nom_adh', $fields)) {
921 $order[] = 'nom_adh ' . $this->filters
->getDirection();
923 if ($this->canOrderBy('prenom_adh', $fields)) {
924 $order[] = 'prenom_adh ' . $this->filters
->getDirection();
930 * Is field allowed to order? it shoulsd be present in
931 * provided fields list (those that are SELECT'ed).
933 * @param string $field_name Field name to order by
934 * @param array $fields SELECTE'ed fields
938 private function canOrderBy($field_name, $fields)
940 if (!is_array($fields)) {
942 } elseif (in_array($field_name, $fields)) {
946 'Trying to order by ' . $field_name . ' while it is not in ' .
955 * Builds where clause, for filtering on simple list mode
957 * @param Select $select Original select
959 * @return string SQL WHERE clause
961 private function buildWhereClause($select)
966 if ($this->filters
->email_filter
== self
::FILTER_W_EMAIL
) {
967 $select->where('email_adh != \'\'');
969 if ($this->filters
->email_filter
== self
::FILTER_WO_EMAIL
) {
970 $select->where('(email_adh = \'\' OR email_adh IS NULL)');
973 if ($this->filters
->filter_str
!= '') {
974 $token = $zdb->platform
->quoteValue(
975 '%' . strtolower($this->filters
->filter_str
) . '%'
977 switch ($this->filters
->field_filter
) {
978 case self
::FILTER_NAME
:
979 if (TYPE_DB
=== 'pgsql') {
980 $sep = " || ' ' || ";
988 //$sep = ( TYPE_DB === 'pgsql' ) ? " || ' ' || " : ', " ", ';
991 $pre . 'LOWER(nom_adh)' . $sep .
992 'LOWER(prenom_adh)' . $sep .
993 'LOWER(pseudo_adh)' . $post . ' LIKE ' .
996 $pre . 'LOWER(prenom_adh)' . $sep .
997 'LOWER(nom_adh)' . $sep .
998 'LOWER(pseudo_adh)' . $post . ' LIKE ' .
1003 case self
::FILTER_COMPANY_NAME
:
1005 'LOWER(societe_adh) LIKE ' .
1009 case self
::FILTER_ADDRESS
:
1012 'LOWER(adresse_adh) LIKE ' . $token
1014 'LOWER(adresse2_adh) LIKE ' . $token
1016 'cp_adh LIKE ' . $token
1018 'LOWER(ville_adh) LIKE ' . $token
1020 'LOWER(pays_adh) LIKE ' . $token
1024 case self
::FILTER_MAIL
:
1027 'LOWER(email_adh) LIKE ' . $token
1029 'LOWER(url_adh) LIKE ' . $token
1031 'LOWER(msn_adh) LIKE ' . $token
1033 'LOWER(icq_adh) LIKE ' . $token
1035 'LOWER(jabber_adh) LIKE ' . $token
1039 case self
::FILTER_JOB
:
1041 'LOWER(prof_adh) LIKE ' . $token
1044 case self
::FILTER_INFOS
:
1046 if ($login->isAdmin() ||
$login->isStaff()) {
1047 $more = ' OR LOWER(info_adh) LIKE ' . $token;
1050 '(LOWER(info_public_adh) LIKE ' .
1051 $token . $more . ')'
1054 case self
::FILTER_NUMBER
:
1055 $select->where
->equalTo('a.id_adh', $this->filters
->filter_str
);
1060 if ($this->filters
->membership_filter
) {
1061 switch ($this->filters
->membership_filter
) {
1062 case self
::MEMBERSHIP_NEARLY
:
1063 $now = new \
DateTime();
1064 $duedate = new \
DateTime();
1065 $duedate->modify('+1 month');
1066 $select->where
->greaterThanOrEqualTo(
1068 $now->format('Y-m-d')
1071 $duedate->format('Y-m-d')
1074 case self
::MEMBERSHIP_LATE
:
1078 date('Y-m-d', time())
1079 )->equalTo('bool_exempt_adh', new Expression('false'));
1081 case self
::MEMBERSHIP_UP2DATE
:
1083 '(' . 'date_echeance >= \'' . date('Y-m-d', time())
1084 . '\' OR bool_exempt_adh=true)'
1087 case self
::MEMBERSHIP_NEVER
:
1088 $select->where('date_echeance IS NULL')
1089 ->where('bool_exempt_adh = false');
1091 case self
::MEMBERSHIP_STAFF
:
1092 $select->where
->lessThan(
1093 'p.priorite_statut',
1094 self
::NON_STAFF_MEMBERS
1097 case self
::MEMBERSHIP_ADMIN
:
1098 $select->where
->equalTo('bool_admin_adh', true);
1100 case self
::MEMBERSHIP_NONE
:
1101 $select->where
->equalTo('a.id_statut', Status
::DEFAULT_STATUS
);
1106 if ($this->filters
->filter_account
) {
1107 switch ($this->filters
->filter_account
) {
1108 case self
::ACTIVE_ACCOUNT
:
1109 $select->where('activite_adh=true');
1111 case self
::INACTIVE_ACCOUNT
:
1112 $select->where('activite_adh=false');
1117 if ($this->filters
->group_filter
) {
1119 array('g' => PREFIX_DB
. Group
::GROUPSUSERS_TABLE
),
1120 'a.' . Adherent
::PK
. '=g.' . Adherent
::PK
,
1124 array('gs' => PREFIX_DB
. Group
::TABLE
),
1125 'gs.' . Group
::PK
. '=g.' . Group
::PK
,
1129 '(g.' . Group
::PK
. ' = ' . $this->filters
->group_filter
.
1130 ' OR gs.parent_group = NULL OR gs.parent_group = ' .
1131 $this->filters
->group_filter
. ')'
1135 if ($this->filters
instanceof AdvancedMembersList
) {
1136 if ($this->filters
->rbirth_date_begin
1137 ||
$this->filters
->rbirth_date_end
1139 if ($this->filters
->rbirth_date_begin
) {
1140 $d = new \
DateTime($this->filters
->rbirth_date_begin
);
1141 $select->where
->greaterThanOrEqualTo(
1146 if ($this->filters
->rbirth_date_end
) {
1147 $d = new \
DateTime($this->filters
->rbirth_date_end
);
1148 $select->where
->lessThanOrEqualTo(
1155 if ($this->filters
->rcreation_date_begin
1156 ||
$this->filters
->rcreation_date_end
1158 if ($this->filters
->rcreation_date_begin
) {
1159 $d = new \
DateTime($this->filters
->rcreation_date_begin
);
1160 $select->where
->greaterThanOrEqualTo(
1165 if ($this->filters
->rcreation_date_end
) {
1166 $d = new \
DateTime($this->filters
->rcreation_date_end
);
1167 $select->where
->lessThanOrEqualTo(
1174 if ($this->filters
->rmodif_date_begin
1175 ||
$this->filters
->rmodif_date_end
1177 if ($this->filters
->rmodif_date_begin
) {
1178 $d = new \
DateTime($this->filters
->rmodif_date_begin
);
1179 $select->where
->greaterThanOrEqualTo(
1184 if ($this->filters
->rmodif_date_end
) {
1185 $d = new \
DateTime($this->filters
->rmodif_date_end
);
1186 $select->where
->lessThanOrEqualTo(
1193 if ($this->filters
->rdue_date_begin
1194 ||
$this->filters
->rdue_date_end
1196 if ($this->filters
->rdue_date_begin
) {
1197 $d = new \
DateTime($this->filters
->rdue_date_begin
);
1198 $select->where
->greaterThanOrEqualTo(
1203 if ($this->filters
->rdue_date_end
) {
1204 $d = new \
DateTime($this->filters
->rdue_date_end
);
1205 $select->where
->lessThanOrEqualTo(
1212 if ($this->filters
->show_public_infos
) {
1213 switch ($this->filters
->show_public_infos
) {
1214 case self
::FILTER_W_PUBINFOS
:
1215 $select->where('bool_display_info = true');
1217 case self
::FILTER_WO_PUBINFOS
:
1218 $select->where('bool_display_info = false');
1220 case self
::FILTER_DC_PUBINFOS
:
1221 //nothing to do here.
1226 if ($this->filters
->status
) {
1229 $this->filters
->status
1233 if ($this->filters
->rcontrib_creation_date_begin
1234 ||
$this->filters
->rcontrib_creation_date_end
1236 if ($this->filters
->rcontrib_creation_date_begin
) {
1238 $this->filters
->rcontrib_creation_date_begin
1240 $select->where
->greaterThanOrEqualTo(
1245 if ($this->filters
->rcontrib_creation_date_end
) {
1247 $this->filters
->rcontrib_creation_date_end
1249 $select->where
->lessThanOrEqualTo(
1256 if ($this->filters
->rcontrib_begin_date_begin
1257 ||
$this->filters
->rcontrib_begin_date_end
1259 if ($this->filters
->rcontrib_begin_date_begin
) {
1261 $this->filters
->rcontrib_begin_date_begin
1263 $select->where
->greaterThanOrEqualTo(
1264 'ct.date_debut_cotis',
1268 if ($this->filters
->rcontrib_begin_date_end
) {
1270 $this->filters
->rcontrib_begin_date_end
1272 $select->where
->lessThanOrEqualTo(
1273 'ct.date_debut_cotis',
1279 if ($this->filters
->rcontrib_end_date_begin
1280 ||
$this->filters
->rcontrib_end_date_end
1282 if ($this->filters
->rcontrib_end_date_begin
) {
1284 $this->filters
->rcontrib_end_date_begin
1286 $select->where
->greaterThanOrEqualTo(
1287 'ct.date_fin_cotis',
1291 if ($this->filters
->rcontrib_end_date_end
) {
1293 $this->filters
->rcontrib_end_date_end
1295 $select->where
->lessThanOrEqualTo(
1296 'ct.date_fin_cotis',
1302 if ($this->filters
->contrib_min_amount
1303 ||
$this->filters
->contrib_max_amount
1305 if ($this->filters
->contrib_min_amount
) {
1306 $select->where
->greaterThanOrEqualTo(
1308 $this->filters
->contrib_min_amount
1311 if ($this->filters
->contrib_max_amount
) {
1312 $select->where
->lessThanOrEqualTo(
1314 $this->filters
->contrib_max_amount
1319 if ($this->filters
->contributions_types
) {
1322 $this->filters
->contributions_types
1326 if ($this->filters
->payments_types
) {
1328 'ct.type_paiement_cotis',
1329 $this->filters
->payments_types
1333 if (count($this->filters
->contrib_dynamic
) > 0
1334 && !isset($this->filters
->contrib_dynamic
['empty'])
1336 foreach ($this->filters
->contrib_dynamic
as $k => $cd) {
1342 if (is_array($cd)) {
1343 //dynamic choice spotted!
1344 $prefix = 'cdfc' . $k . '.';
1345 $qry = 'dfc.field_form = \'contrib\' AND ' .
1346 'dfc.field_id = ' . $k . ' AND ';
1348 $select->where
->in($prefix . $field, $cd);
1350 //dynamic field spotted!
1352 $qry = 'dfc.field_form = \'contrib\' AND ' .
1353 'dfc.field_id = ' . $k . ' AND ';
1354 $field = 'field_val';
1355 $qry .= 'LOWER(' . $prefix . $field . ') ' .
1357 $select->where($qry . $zdb->platform
->quoteValue('%' .strtolower($cd) . '%'));
1362 if (count($this->filters
->free_search
) > 0
1363 && !isset($this->filters
->free_search
['empty'])
1365 foreach ($this->filters
->free_search
as $fs) {
1366 $fs['search'] = mb_strtolower($fs['search']);
1368 switch ($fs['qry_op']) {
1369 case AdvancedMembersList
::OP_EQUALS
:
1372 case AdvancedMembersList
::OP_CONTAINS
:
1374 $fs['search'] = '%' . $fs['search'] . '%';
1376 case AdvancedMembersList
::OP_NOT_EQUALS
:
1379 case AdvancedMembersList
::OP_NOT_CONTAINS
:
1381 $fs['search'] = '%' . $fs['search'] . '%';
1383 case AdvancedMembersList
::OP_STARTS_WITH
:
1385 $fs['search'] = $fs['search'] . '%';
1387 case AdvancedMembersList
::OP_ENDS_WITH
:
1389 $fs['search'] = '%' . $fs['search'];
1391 case AdvancedMembersList
::OP_BEFORE
:
1394 case AdvancedMembersList
::OP_AFTER
:
1399 'Unknown query operator: ' . $fs['qry_op'] .
1400 ' (will fallback to equals)',
1410 if (strpos($fs['field'], 'dyn_') === 0) {
1411 // simple dynamic field spotted!
1412 $index = str_replace('dyn_', '', $fs['field']);
1413 $dyn_field = DynamicField
::loadFieldType($zdb, (int)$index);
1414 $prefix = 'df' . $index . '.';
1415 $fs['field'] = 'val';
1418 if ($dyn_field && $dyn_field instanceof \Galette\DynamicFields\Boolean
) {
1419 if ($fs['search'] != 0) {
1420 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1423 $qry .= $prefix . $fs['field'] . ' IS NULL';
1425 } elseif (!strncmp($fs['field'], 'bool_', strlen('bool_'))) {
1426 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1428 } elseif ($fs['qry_op'] === AdvancedMembersList
::OP_BEFORE
1429 ||
$fs['qry_op'] === AdvancedMembersList
::OP_AFTER
1431 if ($prefix === 'a.') {
1432 //dates are OK in the main fields. no cast, just query!
1433 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1434 $zdb->platform
->quoteValue($fs['search']);
1436 //dynamic dates are stored in their localized format :/
1437 //use current lang format to query for now
1438 if ($zdb->isPostgres()) {
1439 $fs['search'] = "to_date('" . $fs['search'] . "', 'YYYY-MM-DD')";
1440 $store_fmt = __("Y-m-d") === 'Y-m-d' ?
'YYYY-MM-DD' : 'DD/MM/YYYY';
1441 $qry .= "to_date('" . $prefix . $fs['field'] . "', '$store_fmt')";
1443 $fs['search'] = "STR_TO_DATE('" . $fs['search'] . "', '%Y-%m-%d')";
1444 $store_fmt = __("Y-m-d") === 'Y-m-d' ?
'%Y-%m-%d' : '%d/%m/%Y';
1445 $qry .= 'STR_TO_DATE(' . $prefix . $fs['field'] . ', \'' . $store_fmt . '\') ';
1448 $qry .= $qop . ' ' . $fs['search'] ;
1450 } elseif ($fs['field'] == 'status_label') {
1451 $qry_pattern = '%p%field %op %value';
1452 $qry .= str_replace(
1463 $zdb->platform
->quoteValue($fs['search'])
1468 $qry .= 'LOWER(' . $prefix . $fs['field'] . ') ' .
1469 $qop . ' ' . $zdb->platform
->quoteValue($fs['search']);
1472 if ($fs['log_op'] === AdvancedMembersList
::OP_AND
) {
1473 $select->where($qry);
1474 } elseif ($fs['log_op'] === AdvancedMembersList
::OP_OR
) {
1475 $select->where($qry, PredicateSet
::OP_OR
);
1480 } catch (\Exception
$e) {
1482 __METHOD__
. ' | ' . $e->getMessage(),
1489 * Login and password field cannot be empty.
1491 * If those ones are not required, or if a file has been imported
1492 * (from a CSV file for example), we fill here random values.
1496 public function emptyLogins()
1501 $zdb->connection
->beginTransaction();
1502 $select = $zdb->select(Adherent
::TABLE
);
1504 array('id_adh', 'login_adh', 'mdp_adh')
1507 'login_adh' => new Expression('NULL'),
1509 'mdp_adh' => new Expression('NULL'),
1515 $results = $zdb->execute($select);
1518 if ($results->count() > 0) {
1519 $update = $zdb->update(Adherent
::TABLE
);
1522 'login_adh' => ':login',
1523 'mdp_adh' => ':pass'
1525 )->where
->equalTo(Adherent
::PK
, ':id');
1527 $stmt = $zdb->sql
->prepareStatementForSqlObject($update);
1529 $p = new \Galette\Core\
Password($zdb);
1531 foreach ($results as $m) {
1533 if ($m->login_adh
== ''
1534 ||
!isset($m->login_adh
)
1535 ||
$m->login_adh
== 'NULL'
1537 $m->login_adh
= $p->makeRandomPassword(15);
1541 if ($m->mdp_adh
== ''
1542 ||
!isset($m->mdp_adh
)
1543 ||
$m->mdp_adh
== 'NULL'
1545 $randomp = $p->makeRandomPassword(15);
1546 $m->mdp_adh
= password_hash(
1553 if ($dirty === true) {
1554 /** Why where parameter is named where1 ?? */
1557 'login_adh' => $m->login_adh
,
1558 'mdp_adh' => $m->mdp_adh
,
1559 'where1' => $m->id_adh
1566 $zdb->connection
->commit();
1567 $this->count
= $processed;
1569 } catch (\Exception
$e) {
1570 $zdb->connection
->rollBack();
1572 'An error occurred trying to retrieve members with ' .
1573 'empty logins/passwords (' . $e->getMessage(),
1581 * Loads data to produce a Pie chart based on members state of dues
1585 public function getRemindersCount()
1589 $reminders = array();
1591 $soon_date = new \
DateTime();
1592 $soon_date->modify('+1 month');
1594 $now = new \
DateTime();
1596 $select = $zdb->select(Adherent
::TABLE
, 'a');
1599 'cnt' => new Expression('count(a.' . Adherent
::PK
. ')')
1604 array('p' => PREFIX_DB
. self
::TABLE
),
1605 'a.parent_id=p.' . self
::PK
,
1611 ->lessThan('a.date_echeance', $soon_date->format('Y-m-d'))
1612 ->greaterThanOrEqualTo('a.date_echeance', $now->format('Y-m-d'));
1614 ->where('a.activite_adh=true')
1615 ->where('a.bool_exempt_adh=false');
1617 $select_wo_mail = clone $select;
1619 $select->where('(a.email_adh != \'\' OR p.email_adh != \'\')');
1620 $select_wo_mail->where('a.email_adh = \'\' AND p.email_adh = \'\'');
1622 $results = $zdb->execute($select);
1623 $res = $results->current();
1624 $reminders['impending'] = $res->cnt
;
1626 $results_wo_mail = $zdb->execute($select_wo_mail);
1627 $res_wo_mail = $results_wo_mail->current();
1628 $reminders['nomail']['impending'] = $res_wo_mail->cnt
;
1630 $select = $zdb->select(Adherent
::TABLE
, 'a');
1633 'cnt' => new Expression('count(a.' . Adherent
::PK
. ')')
1638 array('p' => PREFIX_DB
. self
::TABLE
),
1639 'a.parent_id=p.' . self
::PK
,
1645 ->lessThan('a.date_echeance', $now->format('Y-m-d'));
1647 ->where('a.activite_adh=true')
1648 ->where('a.bool_exempt_adh=false');
1650 $select_wo_mail = clone $select;
1652 $select->where('(a.email_adh != \'\' OR p.email_adh != \'\')');
1653 $select_wo_mail->where('a.email_adh = \'\' AND p.email_adh = \'\'');
1655 $results = $zdb->execute($select);
1656 $res = $results->current();
1657 $reminders['late'] = $res->cnt
;
1659 $results_wo_mail = $zdb->execute($select_wo_mail);
1660 $res_wo_mail = $results_wo_mail->current();
1661 $reminders['nomail']['late'] = $res_wo_mail->cnt
;
1667 * Get count for current query
1671 public function getCount()
1673 return $this->count
;
1677 * Get registered errors
1681 public function getErrors()
1683 return $this->errors
;
1687 * Get all existing emails
1689 * @param Db $zdb Database instance
1691 * @return array ['email' => 'id_adh']
1693 public static function getEmails(Db
$zdb)
1696 $select = $zdb->select(self
::TABLE
);
1701 $select->where('email_adh != \'\' AND email_adh IS NOT NULL');
1702 $rows = $zdb->execute($select);
1703 foreach ($rows as $row) {
1704 $emails[$row->email_adh
] = $row->{self
::PK
};
1710 * Get current filters
1712 * @return MembersList
1714 public function getFilters()
1716 return $this->filters
;