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
;
43 use Laminas\Db\Adapter\Adapter
;
44 use Laminas\Db\Sql\Expression
;
45 use Laminas\Db\Sql\Select
;
46 use Laminas\Db\Sql\Predicate\PredicateSet
;
47 use Laminas\Db\Sql\Predicate\Operator
;
48 use Galette\Entity\Adherent
;
49 use Galette\Entity\Contribution
;
50 use Galette\Entity\Transaction
;
51 use Galette\Entity\Reminder
;
52 use Galette\Filters\MembersList
;
53 use Galette\Filters\AdvancedMembersList
;
54 use Galette\Core\Picture
;
55 use Galette\Entity\Group
;
56 use Galette\Repository\Groups
;
57 use Galette\Entity\Status
;
61 * Members class for galette
64 * @category Repository
67 * @author Johan Cwiklinski <johan@x-tnd.be>
68 * @copyright 2009-2014 The Galette Team
69 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
70 * @link http://galette.tuxfamily.org
74 const TABLE
= Adherent
::TABLE
;
75 const PK
= Adherent
::PK
;
77 const ALL_ACCOUNTS
= 0;
78 const ACTIVE_ACCOUNT
= 1;
79 const INACTIVE_ACCOUNT
= 2;
82 const SHOW_PUBLIC_LIST
= 1;
83 const SHOW_ARRAY_LIST
= 2;
85 const SHOW_MANAGED
= 4;
86 const SHOW_EXPORT
= 5;
88 const FILTER_NAME
= 0;
89 const FILTER_ADDRESS
= 1;
90 const FILTER_MAIL
= 2;
92 const FILTER_INFOS
= 4;
93 const FILTER_DC_EMAIL
= 5;
94 const FILTER_W_EMAIL
= 6;
95 const FILTER_WO_EMAIL
= 7;
96 const FILTER_COMPANY_NAME
= 8;
97 const FILTER_DC_PUBINFOS
= 9;
98 const FILTER_W_PUBINFOS
= 10;
99 const FILTER_WO_PUBINFOS
= 11;
100 const FILTER_NUMBER
= 12;
102 const MEMBERSHIP_ALL
= 0;
103 const MEMBERSHIP_UP2DATE
= 3;
104 const MEMBERSHIP_NEARLY
= 1;
105 const MEMBERSHIP_LATE
= 2;
106 const MEMBERSHIP_NEVER
= 4;
107 const MEMBERSHIP_STAFF
= 5;
108 const MEMBERSHIP_ADMIN
= 6;
109 const MEMBERSHIP_NONE
= 7;
111 const ORDERBY_NAME
= 0;
112 const ORDERBY_NICKNAME
= 1;
113 const ORDERBY_STATUS
= 2;
114 const ORDERBY_FEE_STATUS
= 3;
115 const ORDERBY_MODIFDATE
= 4;
116 const ORDERBY_ID
= 5;
118 const NON_STAFF_MEMBERS
= 30;
120 private $filters = false;
121 private $count = null;
122 private $errors = array();
125 * Default constructor
127 * @param MembersList $filters Filtering
129 public function __construct($filters = null)
131 if ($filters === null) {
132 $this->filters
= new MembersList();
134 $this->filters
= $filters;
139 * Get staff members list
141 * @param bool $as_members return the results as an array of
143 * @param array $fields field(s) name(s) to get. Should be a string or
144 * an array. If null, all fields will be
146 * @param boolean $count true if we want to count members
147 * @param boolean $limit true to LIMIT query
149 * @return Adherent[]|ResultSet
151 public function getStaffMembersList(
157 return $this->getMembersList(
168 * Get managed members list (for groups managers)
170 * @param bool $as_members return the results as an array of
172 * @param array $fields field(s) name(s) to get. Should be a string or
173 * an array. If null, all fields will be
175 * @param boolean $count true if we want to count members
176 * @param boolean $limit true to LIMIT query
178 * @return Adherent[]|ResultSet
180 public function getManagedMembersList(
186 return $this->getMembersList(
199 * @param bool $as_members return the results as an array of
201 * @param array $fields field(s) name(s) to get. Should be a string or
202 * an array. If null, all fields will be
204 * @param boolean $count true if we want to count members
205 * @param boolean $staff true if we want only staff members
206 * @param boolean $managed true if we want only managed groups
207 * @param boolean $limit true if we want records pagination
208 * @param boolean $export true if we are exporting
210 * @return Adherent[]|Laminas\Db\ResultSet
212 public function getMembersList(
223 if ($limit === true) {
224 //force count if limit is active
229 $_mode = self
::SHOW_LIST
;
230 if ($staff !== false) {
231 $_mode = self
::SHOW_STAFF
;
233 if ($managed !== false) {
234 $_mode = self
::SHOW_MANAGED
;
236 if ($export !== false) {
237 $_mode = self
::SHOW_EXPORT
;
240 $select = $this->buildSelect(
247 //add limits to retrieve only relavant rows
248 if ($limit === true) {
249 $this->filters
->setLimits($select);
252 $rows = $zdb->execute($select);
253 $this->filters
->query
= $zdb->query_string
;
261 foreach ($rows as $row) {
262 $members[] = new Adherent($zdb, $row, $deps);
268 } catch (\Exception
$e) {
270 'Cannot list members | ' . $e->getMessage(),
278 * Remove specified members
280 * @param integer|array $ids Members identifiers to delete
284 public function removeMembers($ids)
286 global $zdb, $hist, $emitter;
289 if (is_numeric($ids)) {
290 //we've got only one identifier
296 if (is_array($list)) {
298 $zdb->connection
->beginTransaction();
300 //Retrieve some information
301 $select = $zdb->select(self
::TABLE
);
303 array(self
::PK
, 'nom_adh', 'prenom_adh', 'email_adh')
304 )->where
->in(self
::PK
, $list);
306 $results = $zdb->execute($select);
309 foreach ($results as $member) {
310 $str_adh = $member->id_adh
. ' (' . $member->nom_adh
. ' ' .
311 $member->prenom_adh
. ')';
312 $infos .= $str_adh . "\n";
314 $p = new Picture($member->id_adh
);
315 if ($p->hasPicture()) {
316 if (!$p->delete(false)) {
318 'Unable to delete picture for member ' . $str_adh,
321 throw new \
Exception(
322 'Unable to delete picture for member ' .
327 _T("Member Picture deleted"),
333 $emitter->emit('member.remove', [
334 'id_adh' => $member->id_adh
,
335 'nom_adh' => $member->nom_adh
,
336 'prenom_adh' => $member->prenom_adh
,
337 'email_adh' => $member->email_adh
341 //delete contributions
342 $del_qry = $zdb->delete(Contribution
::TABLE
);
347 $zdb->execute($del_qry);
350 $select = $zdb->select(Transaction
::TABLE
);
351 $select->where
->in(self
::PK
, $list);
352 $results = $zdb->execute($select);
354 //if members has transactions;
355 //reset link with other contributions
357 if ($results->count() > 0) {
359 foreach ($results as $transaction) {
360 $transactions[] = $transaction[Transaction
::PK
];
363 $update = $zdb->update(Contribution
::TABLE
);
365 Transaction
::PK
=> new Expression('NULL')
370 $zdb->execute($update);
373 //delete transactions
374 $del_qry = $zdb->delete(Transaction
::TABLE
);
375 $del_qry->where
->in(self
::PK
, $list);
376 $zdb->execute($del_qry);
378 //delete groups membership/mamagmentship
379 Groups
::removeMembersFromGroups($list);
382 $del_qry = $zdb->delete(Reminder
::TABLE
);
387 $zdb->execute($del_qry);
389 //delete dynamic fields values
390 $del_qry = $zdb->delete(DynamicFieldsHandle
::TABLE
);
391 $del_qry->where(['field_form' => 'adh']);
392 $del_qry->where
->in('item_id', $list);
393 $zdb->execute($del_qry);
396 $del_qry = $zdb->delete(self
::TABLE
);
401 $zdb->execute($del_qry);
404 $zdb->connection
->commit();
406 //add an history entry
408 _T("Delete members cards, transactions and dues"),
413 } catch (\Exception
$e) {
414 $zdb->connection
->rollBack();
415 if ($e->getCode() == 23000) {
417 'Member still have existing dependencies in the ' .
418 'database, maybe a mailing or some content from a ' .
419 'plugin. Please remove dependencies before trying ' .
423 $this->errors
[] = _T("Cannot remove a member who still have dependencies (mailings, ...)");
426 'Unable to delete selected member(s) |' .
434 //not numeric and not an array: incorrect.
436 'Asking to remove members, but without providing an array or a single numeric value.',
446 * @param boolean $as_members return the results as an array of
448 * @param array $fields field(s) name(s) to get. Should be a string or
449 * an array. If null, all fields will be
452 * @return Adherent[]|ResultSet
454 public function getList($as_members = false, $fields = null)
456 return $this->getMembersList(
468 * Get members list with public information available
470 * @param boolean $with_photos get only members which have uploaded a
471 * photo (for trombinoscope)
475 public function getPublicList($with_photos)
480 $select = $this->buildSelect(
481 self
::SHOW_PUBLIC_LIST
,
486 $this->filters
->setLimits($select);
488 $results = $zdb->execute($select);
490 foreach ($results as $row) {
494 'picture' => $with_photos
496 $members[] = new Adherent($zdb, $row, $deps);
499 } catch (\Exception
$e) {
501 'Cannot list members with public information (photos: '
502 . $with_photos . ') | ' . $e->getMessage(),
510 * Get list of members that has been selected
512 * @param array $ids an array of members id that has been selected
513 * @param array $orderby SQL order clause (optionnal)
514 * @param boolean $with_photos Should photos be loaded?
515 * @param boolean $as_members Return Adherent[] or simple ResultSet
516 * @param array $fields Fields to use
517 * @param boolean $export True if we are exporting
518 * @param boolean $dues True if load dues as Adherent dependency
519 * @param boolean $parent True if load parent as Adherent dependency
523 public function getArrayList(
526 $with_photos = false,
535 if (!is_array($ids) ||
count($ids) < 1) {
536 Analog
::log('No member selected for labels.', Analog
::INFO
);
541 $damode = self
::SHOW_ARRAY_LIST
;
542 if ($export === true) {
543 $damode = self
::SHOW_EXPORT
;
545 $select = $this->buildSelect(
551 $select->where
->in('a.' . self
::PK
, $ids);
552 if ($orderby != null && count($orderby) > 0) {
553 if (is_array($orderby)) {
554 foreach ($orderby as $o) {
558 $select->order($orderby);
562 $results = $zdb->execute($select);
565 foreach ($results as $o) {
567 'picture' => $with_photos,
572 if ($as_members === true) {
573 $members[] = new Adherent($zdb, $o, $deps);
579 } catch (\Exception
$e) {
581 'Cannot load members form ids array | ' . $e->getMessage(),
588 * Builds the SELECT statement
590 * @param int $mode the current mode (see self::SHOW_*)
591 * @param array $fields fields list to retrieve
592 * @param bool $photos true if we want to get only members with photos
593 * Default to false, only relevant for SHOW_PUBLIC_LIST
594 * @param bool $count true if we want to count members, defaults to false
596 * @return Select SELECT statement
598 private function buildSelect($mode, $fields, $photos, $count = false): Select
603 if ($fields != null && is_array($fields) && !in_array('id_adh', $fields)) {
604 $fields[] = 'id_adh';
606 $fieldsList = ($fields != null)
607 ?
((!is_array($fields) ||
count($fields) < 1) ?
(array)'*'
608 : $fields) : (array)'*';
610 $select = $zdb->select(self
::TABLE
, 'a');
612 $select->columns($fieldsList);
614 $select->quantifier('DISTINCT');
617 case self
::SHOW_STAFF
:
618 case self
::SHOW_LIST
:
619 case self
::SHOW_ARRAY_LIST
:
621 array('p' => PREFIX_DB
. Status
::TABLE
),
622 'a.' . Status
::PK
. '=p.' . Status
::PK
,
626 case self
::SHOW_EXPORT
:
627 //basically the same as above, but without any fields
629 array('p' => PREFIX_DB
. Status
::TABLE
),
630 'a.' . Status
::PK
. '=p.' . Status
::PK
,
634 case self
::SHOW_MANAGED
:
636 array('p' => PREFIX_DB
. Status
::TABLE
),
637 'a.' . Status
::PK
. '=p.' . Status
::PK
639 array('gr' => PREFIX_DB
. Group
::GROUPSUSERS_TABLE
),
640 'a.' . Adherent
::PK
. '=gr.' . Adherent
::PK
,
643 array('m' => PREFIX_DB
. Group
::GROUPSMANAGERS_TABLE
),
644 'gr.' . Group
::PK
. '=m.' . Group
::PK
,
646 )->where('m.' . Adherent
::PK
. ' = ' . $login->id
);
648 case self
::SHOW_PUBLIC_LIST
:
651 array('p' => PREFIX_DB
. Picture
::TABLE
),
652 'a.' . self
::PK
. '= p.' . self
::PK
,
659 //check for contributions filtering
661 $this->filters
instanceof AdvancedMembersList
662 && $this->filters
->withinContributions()
665 array('ct' => PREFIX_DB
. Contribution
::TABLE
),
666 'ct.' . self
::PK
. '=a.' . self
::PK
,
672 //check if there are dynamic fields in filter
677 $this->filters
instanceof AdvancedMembersList
678 && $this->filters
->free_search
679 && count($this->filters
->free_search
) > 0
680 && !isset($this->filters
->free_search
['empty'])
682 $free_searches = $this->filters
->free_search
;
683 foreach ($free_searches as $fs) {
684 if (strpos($fs['field'], 'dyn_') === 0) {
685 // simple dynamic fields
687 $dfs[] = str_replace('dyn_', '', $fs['field']);
692 //check if there are dynamic fields for contributions in filter
698 $this->filters
instanceof AdvancedMembersList
699 && $this->filters
->withinContributions()
702 $this->filters
->contrib_dynamic
703 && count($this->filters
->contrib_dynamic
) > 0
704 && !isset($this->filters
->contrib_dynamic
['empty'])
708 //check if there are dynamic fields in the filter
709 foreach ($this->filters
->contrib_dynamic
as $k => $cd) {
718 if ($hasDfc === true ||
$hasCdfc === true) {
720 array('dfc' => PREFIX_DB
. DynamicFieldsHandle
::TABLE
),
721 'dfc.item_id=ct.' . Contribution
::PK
,
727 // simple dynamic fields
728 if ($hasDf === true) {
729 foreach ($dfs as $df) {
730 $subselect = $zdb->select(DynamicFieldsHandle
::TABLE
, 'df');
733 'item_id' => 'item_id',
737 $subselect->where('df.field_form = \'adh\'');
738 $subselect->where('df.field_id = ' . $df);
740 array('df' . $df => $subselect),
741 'a.id_adh = df' . $df . '.item_id',
748 // choice dynamic fields
749 if ($hasCdfc === true) {
750 $cdf_field = 'cdf.id';
751 if (TYPE_DB
=== 'pgsql') {
752 $cdf_field .= '::text';
755 $cdf_field = 'cdfc.id';
756 if (TYPE_DB
=== 'pgsql') {
757 $cdf_field .= '::text';
759 foreach ($cdfcs as $cdf) {
760 $rcdf_field = str_replace(
766 array('cdfc' . $cdf => DynamicField
::getFixedValuesTableName($cdf, true)),
767 $rcdf_field . '=dfc.field_val',
774 if ($mode == self
::SHOW_LIST ||
$mode == self
::SHOW_MANAGED
) {
775 if ($this->filters
!== false) {
776 $this->buildWhereClause($select);
778 } elseif ($mode == self
::SHOW_PUBLIC_LIST
) {
791 new Expression('true')
801 new Expression('true')
806 new Expression('true')
815 if ($mode === self
::SHOW_STAFF
) {
816 $select->where
->lessThan(
818 self
::NON_STAFF_MEMBERS
823 $this->proceedCount($select);
826 $this->buildOrderClause($select, $fields);
829 } catch (\Exception
$e) {
831 'Cannot build SELECT clause for members | ' . $e->getMessage(),
839 * Count members from the query
841 * @param Select $select Original select
845 private function proceedCount(Select
$select)
850 $countSelect = clone $select;
851 $countSelect->reset($countSelect::COLUMNS
);
852 $countSelect->reset($countSelect::ORDER
);
853 $countSelect->reset($countSelect::HAVING
);
854 $joins = $countSelect->joins
;
855 $countSelect->reset($countSelect::JOINS
);
856 foreach ($joins as $join) {
863 unset($join['columns']);
865 $countSelect->columns(
867 'count' => new Expression('count(DISTINCT a.' . self
::PK
. ')')
871 $have = $select->having
;
872 if ($have->count() > 0) {
873 foreach ($have->getPredicates() as $h) {
874 $countSelect->where($h);
878 $results = $zdb->execute($countSelect);
880 $this->count
= $results->current()->count
;
881 if (isset($this->filters
) && $this->count
> 0) {
882 $this->filters
->setCounter($this->count
);
884 } catch (\Exception
$e) {
886 'Cannot count members | ' . $e->getMessage(),
894 * Builds the order clause
896 * @param Select $select Original select
897 * @param array $fields Fields list to ensure ORDER clause
898 * references selected fields. Optionnal.
902 private function buildOrderClause(Select
$select, $fields = null): Select
906 switch ($this->filters
->orderby
) {
907 case self
::ORDERBY_NICKNAME
:
908 if ($this->canOrderBy('pseudo_adh', $fields)) {
909 $order[] = 'pseudo_adh ' . $this->filters
->getDirection();
912 case self
::ORDERBY_STATUS
:
913 if ($this->canOrderBy('priorite_statut', $fields)) {
914 $order[] = 'priorite_statut ' . $this->filters
->getDirection();
917 case self
::ORDERBY_MODIFDATE
:
918 if ($this->canOrderBy('date_modif_adh', $fields)) {
919 $order[] = 'date_modif_adh ' . $this->filters
->getDirection();
922 case 'list_adh_contribstatus':
923 case self
::ORDERBY_FEE_STATUS
:
924 if ($this->canOrderBy('bool_exempt_adh', $fields)) {
925 $order[] = 'bool_exempt_adh ' . $this->filters
->getDirection();
928 if ($this->canOrderBy('date_echeance', $fields)) {
929 $order[] = 'date_echeance ' . $this->filters
->getDirection();
932 case self
::ORDERBY_ID
:
933 if ($this->canOrderBy('id_adh', $fields)) {
934 $order[] = 'id_adh ' . $this->filters
->getDirection();
937 case 'list_adh_name':
943 if ($this->canOrderBy($this->filters
->orderby
, $fields)) {
944 $order[] = $this->filters
->orderby
. ' ' . $this->filters
->getDirection();
949 //anyways, we want to order by firstname, lastname
950 if ($this->canOrderBy('nom_adh', $fields)) {
951 $order[] = 'nom_adh ' . $this->filters
->getDirection();
953 if ($this->canOrderBy('prenom_adh', $fields)) {
954 $order[] = 'prenom_adh ' . $this->filters
->getDirection();
957 $select->order($order);
962 * Is field allowed to order? it shoulsd be present in
963 * provided fields list (those that are SELECT'ed).
965 * @param string $field_name Field name to order by
966 * @param array $fields SELECTE'ed fields
970 private function canOrderBy($field_name, $fields)
972 if ($fields === null) {
974 } elseif (!is_array($fields)) {
976 } elseif (in_array($field_name, $fields)) {
980 'Trying to order by ' . $field_name . ' while it is not in ' .
989 * Builds where clause, for filtering on simple list mode
991 * @param Select $select Original select
995 private function buildWhereClause(Select
$select)
1000 if ($this->filters
->email_filter
== self
::FILTER_W_EMAIL
) {
1001 $select->where('email_adh != \'\'');
1003 if ($this->filters
->email_filter
== self
::FILTER_WO_EMAIL
) {
1004 $select->where('(email_adh = \'\' OR email_adh IS NULL)');
1007 if ($this->filters
->filter_str
!= '') {
1008 $token = $zdb->platform
->quoteValue(
1009 '%' . strtolower($this->filters
->filter_str
) . '%'
1011 switch ($this->filters
->field_filter
) {
1012 case self
::FILTER_NAME
:
1013 if (TYPE_DB
=== 'pgsql') {
1014 $sep = " || ' ' || ";
1022 //$sep = ( TYPE_DB === 'pgsql' ) ? " || ' ' || " : ', " ", ';
1025 $pre . 'LOWER(nom_adh)' . $sep .
1026 'LOWER(prenom_adh)' . $sep .
1027 'LOWER(pseudo_adh)' . $post . ' LIKE ' .
1030 $pre . 'LOWER(prenom_adh)' . $sep .
1031 'LOWER(nom_adh)' . $sep .
1032 'LOWER(pseudo_adh)' . $post . ' LIKE ' .
1037 case self
::FILTER_COMPANY_NAME
:
1039 'LOWER(societe_adh) LIKE ' .
1043 case self
::FILTER_ADDRESS
:
1046 'LOWER(adresse_adh) LIKE ' . $token
1048 'LOWER(adresse2_adh) LIKE ' . $token
1050 'cp_adh LIKE ' . $token
1052 'LOWER(ville_adh) LIKE ' . $token
1054 'LOWER(pays_adh) LIKE ' . $token
1058 case self
::FILTER_MAIL
:
1061 'LOWER(email_adh) LIKE ' . $token
1063 'LOWER(url_adh) LIKE ' . $token
1065 'LOWER(msn_adh) LIKE ' . $token
1067 'LOWER(icq_adh) LIKE ' . $token
1069 'LOWER(jabber_adh) LIKE ' . $token
1073 case self
::FILTER_JOB
:
1075 'LOWER(prof_adh) LIKE ' . $token
1078 case self
::FILTER_INFOS
:
1080 if ($login->isAdmin() ||
$login->isStaff()) {
1081 $more = ' OR LOWER(info_adh) LIKE ' . $token;
1084 '(LOWER(info_public_adh) LIKE ' .
1085 $token . $more . ')'
1088 case self
::FILTER_NUMBER
:
1089 $select->where
->equalTo('a.id_adh', $this->filters
->filter_str
);
1094 if ($this->filters
->membership_filter
) {
1095 switch ($this->filters
->membership_filter
) {
1096 case self
::MEMBERSHIP_NEARLY
:
1097 $now = new \
DateTime();
1098 $duedate = new \
DateTime();
1099 $duedate->modify('+1 month');
1100 $select->where
->greaterThanOrEqualTo(
1102 $now->format('Y-m-d')
1105 $duedate->format('Y-m-d')
1108 case self
::MEMBERSHIP_LATE
:
1112 date('Y-m-d', time())
1113 )->equalTo('bool_exempt_adh', new Expression('false'));
1115 case self
::MEMBERSHIP_UP2DATE
:
1117 '(' . 'date_echeance >= \'' . date('Y-m-d', time())
1118 . '\' OR bool_exempt_adh=true)'
1121 case self
::MEMBERSHIP_NEVER
:
1122 $select->where('date_echeance IS NULL')
1123 ->where('bool_exempt_adh = false');
1125 case self
::MEMBERSHIP_STAFF
:
1126 $select->where
->lessThan(
1127 'p.priorite_statut',
1128 self
::NON_STAFF_MEMBERS
1131 case self
::MEMBERSHIP_ADMIN
:
1132 $select->where
->equalTo('bool_admin_adh', true);
1134 case self
::MEMBERSHIP_NONE
:
1135 $select->where
->equalTo('a.id_statut', Status
::DEFAULT_STATUS
);
1140 if ($this->filters
->filter_account
) {
1141 switch ($this->filters
->filter_account
) {
1142 case self
::ACTIVE_ACCOUNT
:
1143 $select->where('activite_adh=true');
1145 case self
::INACTIVE_ACCOUNT
:
1146 $select->where('activite_adh=false');
1151 if ($this->filters
->group_filter
) {
1153 array('g' => PREFIX_DB
. Group
::GROUPSUSERS_TABLE
),
1154 'a.' . Adherent
::PK
. '=g.' . Adherent
::PK
,
1158 array('gs' => PREFIX_DB
. Group
::TABLE
),
1159 'gs.' . Group
::PK
. '=g.' . Group
::PK
,
1163 '(g.' . Group
::PK
. ' = ' . $this->filters
->group_filter
.
1164 ' OR gs.parent_group = NULL OR gs.parent_group = ' .
1165 $this->filters
->group_filter
. ')'
1169 if ($this->filters
instanceof AdvancedMembersList
) {
1170 // Search members who belong to any (OR) or all (AND) listed groups.
1171 // Idea is to build an array of members ID that fits groups selection
1172 // we will use in the final query.
1173 // The OR case is quite simple, AND is a bit more complex; since we must
1174 // check each member do belongs to all listed groups.
1176 count($this->filters
->groups_search
) > 0
1177 && !isset($this->filters
->groups_search
['empty'])
1182 foreach ($this->filters
->groups_search
as $gs) { // then add a row for each group
1183 $wheregroups[] = $gs['group'];
1186 $gselect = $zdb->select(Group
::GROUPSUSERS_TABLE
, 'gu');
1190 array('g' => PREFIX_DB
. Group
::TABLE
),
1191 'gu.id_group=g.' . Group
::PK
,
1196 'g.id_group' => ':group',
1197 'g.parent_group' => ':pgroup'
1201 $gselect->group(['gu.id_adh']);
1203 $stmt = $zdb->sql
->prepareStatementForSqlObject($gselect);
1207 foreach ($this->filters
->groups_search
as $gs) { // then add a row for each ig/searched group pair
1208 /** Why where parameter is named where1 ?? */
1209 $gresults = $stmt->execute(
1211 'where1' => $gs['group'],
1212 'where2' => $gs['group']
1216 switch ($this->filters
->groups_search_log_op
) {
1217 case AdvancedMembersList
::OP_AND
:
1218 foreach ($gresults as $gresult) {
1219 if (!isset($ids[$gresult['id_adh']])) {
1220 $ids[$gresult['id_adh']] = 0;
1222 $ids[$gresult['id_adh']] +
= 1;
1225 case AdvancedMembersList
::OP_OR
:
1226 foreach ($gresults as $gresult) {
1227 $mids[$gresult['id_adh']] = $gresult['id_adh'];
1234 foreach ($ids as $id_adh => $count) {
1235 if ($count == count($wheregroups)) {
1236 $mids[$id_adh] = $id_adh;
1242 //limit on found members
1243 $select->where
->in('a.id_adh', $mids);
1245 //no match in groups, end of game.
1246 $select->where('false = true');
1251 $this->filters
->rbirth_date_begin
1252 ||
$this->filters
->rbirth_date_end
1254 if ($this->filters
->rbirth_date_begin
) {
1255 $d = new \
DateTime($this->filters
->rbirth_date_begin
);
1256 $select->where
->greaterThanOrEqualTo(
1261 if ($this->filters
->rbirth_date_end
) {
1262 $d = new \
DateTime($this->filters
->rbirth_date_end
);
1263 $select->where
->lessThanOrEqualTo(
1271 $this->filters
->rcreation_date_begin
1272 ||
$this->filters
->rcreation_date_end
1274 if ($this->filters
->rcreation_date_begin
) {
1275 $d = new \
DateTime($this->filters
->rcreation_date_begin
);
1276 $select->where
->greaterThanOrEqualTo(
1281 if ($this->filters
->rcreation_date_end
) {
1282 $d = new \
DateTime($this->filters
->rcreation_date_end
);
1283 $select->where
->lessThanOrEqualTo(
1291 $this->filters
->rmodif_date_begin
1292 ||
$this->filters
->rmodif_date_end
1294 if ($this->filters
->rmodif_date_begin
) {
1295 $d = new \
DateTime($this->filters
->rmodif_date_begin
);
1296 $select->where
->greaterThanOrEqualTo(
1301 if ($this->filters
->rmodif_date_end
) {
1302 $d = new \
DateTime($this->filters
->rmodif_date_end
);
1303 $select->where
->lessThanOrEqualTo(
1311 $this->filters
->rdue_date_begin
1312 ||
$this->filters
->rdue_date_end
1314 if ($this->filters
->rdue_date_begin
) {
1315 $d = new \
DateTime($this->filters
->rdue_date_begin
);
1316 $select->where
->greaterThanOrEqualTo(
1321 if ($this->filters
->rdue_date_end
) {
1322 $d = new \
DateTime($this->filters
->rdue_date_end
);
1323 $select->where
->lessThanOrEqualTo(
1330 if ($this->filters
->show_public_infos
) {
1331 switch ($this->filters
->show_public_infos
) {
1332 case self
::FILTER_W_PUBINFOS
:
1333 $select->where('bool_display_info = true');
1335 case self
::FILTER_WO_PUBINFOS
:
1336 $select->where('bool_display_info = false');
1338 case self
::FILTER_DC_PUBINFOS
:
1339 //nothing to do here.
1344 if ($this->filters
->status
) {
1347 $this->filters
->status
1352 $this->filters
->rcontrib_creation_date_begin
1353 ||
$this->filters
->rcontrib_creation_date_end
1355 if ($this->filters
->rcontrib_creation_date_begin
) {
1357 $this->filters
->rcontrib_creation_date_begin
1359 $select->where
->greaterThanOrEqualTo(
1364 if ($this->filters
->rcontrib_creation_date_end
) {
1366 $this->filters
->rcontrib_creation_date_end
1368 $select->where
->lessThanOrEqualTo(
1376 $this->filters
->rcontrib_begin_date_begin
1377 ||
$this->filters
->rcontrib_begin_date_end
1379 if ($this->filters
->rcontrib_begin_date_begin
) {
1381 $this->filters
->rcontrib_begin_date_begin
1383 $select->where
->greaterThanOrEqualTo(
1384 'ct.date_debut_cotis',
1388 if ($this->filters
->rcontrib_begin_date_end
) {
1390 $this->filters
->rcontrib_begin_date_end
1392 $select->where
->lessThanOrEqualTo(
1393 'ct.date_debut_cotis',
1400 $this->filters
->rcontrib_end_date_begin
1401 ||
$this->filters
->rcontrib_end_date_end
1403 if ($this->filters
->rcontrib_end_date_begin
) {
1405 $this->filters
->rcontrib_end_date_begin
1407 $select->where
->greaterThanOrEqualTo(
1408 'ct.date_fin_cotis',
1412 if ($this->filters
->rcontrib_end_date_end
) {
1414 $this->filters
->rcontrib_end_date_end
1416 $select->where
->lessThanOrEqualTo(
1417 'ct.date_fin_cotis',
1424 $this->filters
->contrib_min_amount
1425 ||
$this->filters
->contrib_max_amount
1427 if ($this->filters
->contrib_min_amount
) {
1428 $select->where
->greaterThanOrEqualTo(
1430 $this->filters
->contrib_min_amount
1433 if ($this->filters
->contrib_max_amount
) {
1434 $select->where
->lessThanOrEqualTo(
1436 $this->filters
->contrib_max_amount
1441 if ($this->filters
->contributions_types
) {
1444 $this->filters
->contributions_types
1448 if ($this->filters
->payments_types
) {
1450 'ct.type_paiement_cotis',
1451 $this->filters
->payments_types
1456 count($this->filters
->contrib_dynamic
) > 0
1457 && !isset($this->filters
->contrib_dynamic
['empty'])
1459 foreach ($this->filters
->contrib_dynamic
as $k => $cd) {
1465 if (is_array($cd)) {
1466 //dynamic choice spotted!
1467 $prefix = 'cdfc' . $k . '.';
1468 $qry = 'dfc.field_form = \'contrib\' AND ' .
1469 'dfc.field_id = ' . $k . ' AND ';
1471 $select->where
->in($prefix . $field, $cd);
1473 //dynamic field spotted!
1475 $qry = 'dfc.field_form = \'contrib\' AND ' .
1476 'dfc.field_id = ' . $k . ' AND ';
1477 $field = 'field_val';
1478 $qry .= 'LOWER(' . $prefix . $field . ') ' .
1480 $select->where($qry . $zdb->platform
->quoteValue('%' . strtolower($cd) . '%'));
1486 count($this->filters
->free_search
) > 0
1487 && !isset($this->filters
->free_search
['empty'])
1489 foreach ($this->filters
->free_search
as $fs) {
1490 $fs['search'] = mb_strtolower($fs['search']);
1492 switch ($fs['qry_op']) {
1493 case AdvancedMembersList
::OP_EQUALS
:
1496 case AdvancedMembersList
::OP_CONTAINS
:
1498 $fs['search'] = '%' . $fs['search'] . '%';
1500 case AdvancedMembersList
::OP_NOT_EQUALS
:
1503 case AdvancedMembersList
::OP_NOT_CONTAINS
:
1505 $fs['search'] = '%' . $fs['search'] . '%';
1507 case AdvancedMembersList
::OP_STARTS_WITH
:
1509 $fs['search'] = $fs['search'] . '%';
1511 case AdvancedMembersList
::OP_ENDS_WITH
:
1513 $fs['search'] = '%' . $fs['search'];
1515 case AdvancedMembersList
::OP_BEFORE
:
1518 case AdvancedMembersList
::OP_AFTER
:
1523 'Unknown query operator: ' . $fs['qry_op'] .
1524 ' (will fallback to equals)',
1534 if (strpos($fs['field'], 'dyn_') === 0) {
1535 // simple dynamic field spotted!
1536 $index = str_replace('dyn_', '', $fs['field']);
1537 $dyn_field = DynamicField
::loadFieldType($zdb, (int)$index);
1538 $prefix = 'df' . $index . '.';
1539 $fs['field'] = 'val';
1542 if ($dyn_field && $dyn_field instanceof \Galette\DynamicFields\Boolean
) {
1543 if ($fs['search'] != 0) {
1544 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1547 $qry .= $prefix . $fs['field'] . ' IS NULL';
1549 } elseif (!strncmp($fs['field'], 'bool_', strlen('bool_'))) {
1550 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1553 $fs['qry_op'] === AdvancedMembersList
::OP_BEFORE
1554 ||
$fs['qry_op'] === AdvancedMembersList
::OP_AFTER
1556 if ($prefix === 'a.') {
1557 //dates are OK in the main fields. no cast, just query!
1558 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1559 $zdb->platform
->quoteValue($fs['search']);
1561 //dynamic dates are stored in their localized format :/
1562 //use current lang format to query for now
1563 if ($zdb->isPostgres()) {
1564 $fs['search'] = "to_date('" . $fs['search'] . "', 'YYYY-MM-DD')";
1565 $store_fmt = __("Y-m-d") === 'Y-m-d' ?
'YYYY-MM-DD' : 'DD/MM/YYYY';
1566 $qry .= "to_date('" . $prefix . $fs['field'] . "', '$store_fmt')";
1568 $fs['search'] = "STR_TO_DATE('" . $fs['search'] . "', '%Y-%m-%d')";
1569 $store_fmt = __("Y-m-d") === 'Y-m-d' ?
'%Y-%m-%d' : '%d/%m/%Y';
1570 $qry .= 'STR_TO_DATE(' . $prefix . $fs['field'] . ', \'' . $store_fmt . '\') ';
1573 $qry .= $qop . ' ' . $fs['search'];
1575 } elseif ($fs['field'] == 'status_label') {
1576 $qry_pattern = '%p%field %op %value';
1577 $qry .= str_replace(
1588 $zdb->platform
->quoteValue($fs['search'])
1593 $qry .= 'LOWER(' . $prefix . $fs['field'] . ') ' .
1594 $qop . ' ' . $zdb->platform
->quoteValue($fs['search']);
1597 if ($fs['log_op'] === AdvancedMembersList
::OP_AND
) {
1598 $select->where($qry);
1599 } elseif ($fs['log_op'] === AdvancedMembersList
::OP_OR
) {
1600 $select->where($qry, PredicateSet
::OP_OR
);
1607 } catch (\Exception
$e) {
1609 __METHOD__
. ' | ' . $e->getMessage(),
1617 * Login and password field cannot be empty.
1619 * If those ones are not required, or if a file has been imported
1620 * (from a CSV file for example), we fill here random values.
1624 public function emptyLogins()
1629 $zdb->connection
->beginTransaction();
1630 $select = $zdb->select(Adherent
::TABLE
);
1632 array('id_adh', 'login_adh', 'mdp_adh')
1635 'login_adh' => new Expression('NULL'),
1637 'mdp_adh' => new Expression('NULL'),
1643 $results = $zdb->execute($select);
1646 if ($results->count() > 0) {
1647 $update = $zdb->update(Adherent
::TABLE
);
1650 'login_adh' => ':login',
1651 'mdp_adh' => ':pass'
1653 )->where
->equalTo(Adherent
::PK
, ':id');
1655 $stmt = $zdb->sql
->prepareStatementForSqlObject($update);
1657 $p = new \Galette\Core\
Password($zdb);
1659 foreach ($results as $m) {
1663 ||
!isset($m->login_adh
)
1664 ||
$m->login_adh
== 'NULL'
1666 $m->login_adh
= $p->makeRandomPassword(15);
1672 ||
!isset($m->mdp_adh
)
1673 ||
$m->mdp_adh
== 'NULL'
1675 $randomp = $p->makeRandomPassword(15);
1676 $m->mdp_adh
= password_hash(
1683 if ($dirty === true) {
1684 /** Why where parameter is named where1 ?? */
1687 'login_adh' => $m->login_adh
,
1688 'mdp_adh' => $m->mdp_adh
,
1689 'where1' => $m->id_adh
1696 $zdb->connection
->commit();
1697 $this->count
= $processed;
1699 } catch (\Exception
$e) {
1700 $zdb->connection
->rollBack();
1702 'An error occurred trying to retrieve members with ' .
1703 'empty logins/passwords (' . $e->getMessage(),
1711 * Loads data to produce a Pie chart based on members state of dues
1715 public function getRemindersCount()
1719 $reminders = array();
1721 $soon_date = new \
DateTime();
1722 $soon_date->modify('+1 month');
1724 $now = new \
DateTime();
1726 $select = $zdb->select(Adherent
::TABLE
, 'a');
1729 'cnt' => new Expression('count(a.' . Adherent
::PK
. ')')
1734 array('p' => PREFIX_DB
. self
::TABLE
),
1735 'a.parent_id=p.' . self
::PK
,
1741 ->lessThan('a.date_echeance', $soon_date->format('Y-m-d'))
1742 ->greaterThanOrEqualTo('a.date_echeance', $now->format('Y-m-d'));
1744 ->where('a.activite_adh=true')
1745 ->where('a.bool_exempt_adh=false');
1747 $select_wo_mail = clone $select;
1749 $select->where('(a.email_adh != \'\' OR p.email_adh != \'\')');
1750 $select_wo_mail->where('a.email_adh = \'\' AND p.email_adh = \'\'');
1752 $results = $zdb->execute($select);
1753 $res = $results->current();
1754 $reminders['impending'] = $res->cnt
;
1756 $results_wo_mail = $zdb->execute($select_wo_mail);
1757 $res_wo_mail = $results_wo_mail->current();
1758 $reminders['nomail']['impending'] = $res_wo_mail->cnt
;
1760 $select = $zdb->select(Adherent
::TABLE
, 'a');
1763 'cnt' => new Expression('count(a.' . Adherent
::PK
. ')')
1768 array('p' => PREFIX_DB
. self
::TABLE
),
1769 'a.parent_id=p.' . self
::PK
,
1775 ->lessThan('a.date_echeance', $now->format('Y-m-d'));
1777 ->where('a.activite_adh=true')
1778 ->where('a.bool_exempt_adh=false');
1780 $select_wo_mail = clone $select;
1782 $select->where('(a.email_adh != \'\' OR p.email_adh != \'\')');
1783 $select_wo_mail->where('a.email_adh = \'\' AND p.email_adh = \'\'');
1785 $results = $zdb->execute($select);
1786 $res = $results->current();
1787 $reminders['late'] = $res->cnt
;
1789 $results_wo_mail = $zdb->execute($select_wo_mail);
1790 $res_wo_mail = $results_wo_mail->current();
1791 $reminders['nomail']['late'] = $res_wo_mail->cnt
;
1797 * Get count for current query
1801 public function getCount()
1803 return $this->count
;
1807 * Get registered errors
1811 public function getErrors()
1813 return $this->errors
;
1817 * Get all existing emails
1819 * @param Db $zdb Database instance
1821 * @return array ['email' => 'id_adh']
1823 public static function getEmails(Db
$zdb)
1826 $select = $zdb->select(self
::TABLE
);
1831 $select->where('email_adh != \'\' AND email_adh IS NOT NULL');
1832 $rows = $zdb->execute($select);
1833 foreach ($rows as $row) {
1834 $emails[$row->email_adh
] = $row->{self
::PK
};
1840 * Get current filters
1842 * @return MembersList
1844 public function getFilters()
1846 return $this->filters
;
1850 * Get members list to instanciate dropdowns
1852 * @param Db $zdb Database instance
1853 * @param integer $current Current member
1857 public function getSelectizedMembers(Db
$zdb, $current = null)
1860 $required_fields = array(
1866 $list_members = $this->getList(false, $required_fields);
1868 if (count($list_members) > 0) {
1869 foreach ($list_members as $member) {
1872 $members[$member->$pk] = Adherent
::getNameWithCase(
1874 $member->prenom_adh
,
1882 //check if current attached member is part of the list
1883 if ($current !== null && !isset($members[$current])) {
1885 [$current => Adherent
::getSName($zdb, $current, true, true)] +