]> git.agnieray.net Git - galette.git/blob - galette/lib/Galette/Repository/Members.php
Projects / galette.git / blob
summary | shortlog | log | commit | commitdiff | tree
history | raw | HEAD
Code quality changes
[galette.git] / galette / lib / Galette / Repository / Members.php
1 <?php
2
3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
4
5 /**
6 * Members class
7 *
8 * PHP version 5
9 *
10 * Copyright © 2009-2023 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 2009-2023 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 - 2009-02-28
35 */
36
37 namespace Galette\Repository;
38
39 use Galette\Core\Login;
40 use Galette\Entity\Social;
41 use Galette\Events\GaletteEvent;
42 use Laminas\Db\ResultSet\ResultSet;
43 use Laminas\Db\Sql\Predicate\IsNull;
44 use Throwable;
45 use Galette\DynamicFields\DynamicField;
46 use Galette\Entity\DynamicFieldsHandle;
47 use Analog\Analog;
48 use Laminas\Db\Sql\Expression;
49 use Laminas\Db\Sql\Select;
50 use Laminas\Db\Sql\Predicate\PredicateSet;
51 use Laminas\Db\Sql\Predicate\Operator;
52 use Galette\Entity\Adherent;
53 use Galette\Entity\Contribution;
54 use Galette\Entity\Transaction;
55 use Galette\Entity\Reminder;
56 use Galette\Filters\MembersList;
57 use Galette\Filters\AdvancedMembersList;
58 use Galette\Core\Picture;
59 use Galette\Entity\Group;
60 use Galette\Entity\Status;
61 use Galette\Core\Db;
62
63 /**
64 * Members class for galette
65 *
66 * @name Members
67 * @category Repository
68 * @package Galette
69 *
70 * @author Johan Cwiklinski <johan@x-tnd.be>
71 * @copyright 2009-2023 The Galette Team
72 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
73 * @link http://galette.tuxfamily.org
74 */
75 class Members
76 {
77 public const TABLE = Adherent::TABLE;
78 public const PK = Adherent::PK;
79
80 public const ALL_ACCOUNTS = 0;
81 public const ACTIVE_ACCOUNT = 1;
82 public const INACTIVE_ACCOUNT = 2;
83
84 public const SHOW_LIST = 0;
85 public const SHOW_PUBLIC_LIST = 1;
86 public const SHOW_ARRAY_LIST = 2;
87 public const SHOW_STAFF = 3;
88 public const SHOW_MANAGED = 4;
89 public const SHOW_EXPORT = 5;
90
91 public const FILTER_NAME = 0;
92 public const FILTER_ADDRESS = 1;
93 public const FILTER_MAIL = 2;
94 public const FILTER_JOB = 3;
95 public const FILTER_INFOS = 4;
96 public const FILTER_DC_EMAIL = 5;
97 public const FILTER_W_EMAIL = 6;
98 public const FILTER_WO_EMAIL = 7;
99 public const FILTER_COMPANY_NAME = 8;
100 public const FILTER_DC_PUBINFOS = 9;
101 public const FILTER_W_PUBINFOS = 10;
102 public const FILTER_WO_PUBINFOS = 11;
103 public const FILTER_ID = 12;
104 public const FILTER_NUMBER = 13;
105
106 public const MEMBERSHIP_ALL = 0;
107 public const MEMBERSHIP_UP2DATE = 3;
108 public const MEMBERSHIP_NEARLY = 1;
109 public const MEMBERSHIP_LATE = 2;
110 public const MEMBERSHIP_NEVER = 4;
111 public const MEMBERSHIP_STAFF = 5;
112 public const MEMBERSHIP_ADMIN = 6;
113 public const MEMBERSHIP_NONE = 7;
114
115 public const ORDERBY_NAME = 'name';
116 public const ORDERBY_NICKNAME = 'nickname';
117 public const ORDERBY_STATUS = 'status';
118 public const ORDERBY_FEE_STATUS = 'fee_status';
119 public const ORDERBY_MODIFDATE = 'modif_date';
120 public const ORDERBY_ID = 'id';
121
122 public const NON_STAFF_MEMBERS = 30;
123
124 private $filters = false;
125 private $count = null;
126 private $errors = array();
127
128 /**
129 * Default constructor
130 *
131 * @param MembersList $filters Filtering
132 */
133 public function __construct($filters = null)
134 {
135 if ($filters === null) {
136 $this->filters = new MembersList();
137 } else {
138 $this->filters = $filters;
139 }
140 }
141
142 /**
143 * Get staff members list
144 *
145 * @param bool $as_members return the results as an array of
146 * Member object.
147 * @param array $fields field(s) name(s) to get. Should be a string or
148 * an array. If null, all fields will be
149 * returned
150 * @param boolean $count true if we want to count members
151 * @param boolean $limit true to LIMIT query
152 *
153 * @return Adherent[]|ResultSet
154 */
155 public function getStaffMembersList(
156 $as_members = false,
157 $fields = null,
158 $count = true,
159 $limit = true
160 ) {
161 return $this->getMembersList(
162 $as_members,
163 $fields,
164 $count,
165 true,
166 false,
167 $limit
168 );
169 }
170
171 /**
172 * Get managed members list (for groups managers)
173 *
174 * @param bool $as_members return the results as an array of
175 * Member object.
176 * @param array $fields field(s) name(s) to get. Should be a string or
177 * an array. If null, all fields will be
178 * returned
179 * @param boolean $count true if we want to count members
180 * @param boolean $limit true to LIMIT query
181 *
182 * @return Adherent[]|ResultSet
183 */
184 public function getManagedMembersList(
185 $as_members = false,
186 $fields = null,
187 $count = true,
188 $limit = true
189 ) {
190 return $this->getMembersList(
191 $as_members,
192 $fields,
193 $count,
194 false,
195 true,
196 $limit
197 );
198 }
199
200 /**
201 * Get members list
202 *
203 * @param bool $as_members return the results as an array of
204 * Member object.
205 * @param array $fields field(s) name(s) to get. Should be a string or
206 * an array. If null, all fields will be
207 * returned
208 * @param boolean $count true if we want to count members
209 * @param boolean $staff true if we want only staff members
210 * @param boolean $managed true if we want only managed groups
211 * @param boolean $limit true if we want records pagination
212 * @param boolean $export true if we are exporting
213 *
214 * @return Adherent[]|ResultSet
215 */
216 public function getMembersList(
217 $as_members = false,
218 $fields = null,
219 $count = true,
220 $staff = false,
221 $managed = false,
222 $limit = true,
223 $export = false
224 ) {
225 global $zdb;
226
227 if ($limit === true) {
228 //force count if limit is active
229 $count = true;
230 }
231
232 try {
233 $_mode = self::SHOW_LIST;
234 if ($staff !== false) {
235 $_mode = self::SHOW_STAFF;
236 }
237 if ($managed !== false) {
238 $_mode = self::SHOW_MANAGED;
239 }
240 if ($export !== false) {
241 $_mode = self::SHOW_EXPORT;
242 }
243
244 $select = $this->buildSelect(
245 $_mode,
246 $fields,
247 false,
248 $count
249 );
250
251 //add limits to retrieve only relavant rows
252 if ($limit === true) {
253 $this->filters->setLimits($select);
254 }
255
256 $rows = $zdb->execute($select);
257 $this->filters->query = $zdb->query_string;
258
259 $members = array();
260 if ($as_members) {
261 $deps = array(
262 'picture' => false,
263 'groups' => false
264 );
265 foreach ($rows as $row) {
266 $members[] = new Adherent($zdb, $row, $deps);
267 }
268 } else {
269 $members = $rows;
270 }
271 return $members;
272 } catch (Throwable $e) {
273 Analog::log(
274 'Cannot list members | ' . $e->getMessage(),
275 Analog::WARNING
276 );
277 throw $e;
278 }
279 }
280
281 /**
282 * Remove specified members
283 *
284 * @param integer|array $ids Members identifiers to delete
285 *
286 * @return boolean
287 */
288 public function removeMembers($ids)
289 {
290 global $zdb, $hist, $emitter;
291
292 $processed = array();
293 $list = array();
294 if (is_array($ids)) {
295 $list = $ids;
296 } elseif (is_numeric($ids)) {
297 $list = [(int)$ids];
298 } else {
299 return false;
300 }
301
302 try {
303 $zdb->connection->beginTransaction();
304
305 //Retrieve some information
306 $select = $zdb->select(self::TABLE);
307 $select->columns(
308 array(self::PK, 'nom_adh', 'prenom_adh', 'email_adh')
309 )->where->in(self::PK, $list);
310
311 $results = $zdb->execute($select);
312
313 $infos = null;
314 foreach ($results as $member) {
315 $str_adh = $member->id_adh . ' (' . $member->nom_adh . ' ' .
316 $member->prenom_adh . ')';
317 $infos .= $str_adh . "\n";
318
319 $p = new Picture($member->id_adh);
320 if ($p->hasPicture()) {
321 if (!$p->delete(false)) {
322 Analog::log(
323 'Unable to delete picture for member ' . $str_adh,
324 Analog::ERROR
325 );
326 throw new \Exception(
327 'Unable to delete picture for member ' .
328 $str_adh
329 );
330 } else {
331 $hist->add(
332 _T("Member Picture deleted"),
333 $str_adh
334 );
335 }
336 }
337
338 $processed[] = [
339 'id_adh' => $member->id_adh,
340 'nom_adh' => $member->nom_adh,
341 'prenom_adh' => $member->prenom_adh,
342 'email_adh' => $member->email_adh
343 ];
344 }
345
346 //delete contributions
347 $del_qry = $zdb->delete(Contribution::TABLE);
348 $del_qry->where->in(
349 self::PK,
350 $list
351 );
352 $zdb->execute($del_qry);
353
354 //get transactions
355 $select = $zdb->select(Transaction::TABLE);
356 $select->where->in(self::PK, $list);
357 $results = $zdb->execute($select);
358
359 //if members has transactions;
360 //reset link with other contributions
361 //and remove them
362 if ($results->count() > 0) {
363 $transactions = [];
364 foreach ($results as $transaction) {
365 $transactions[] = $transaction[Transaction::PK];
366 }
367
368 $update = $zdb->update(Contribution::TABLE);
369 $update->set([
370 Transaction::PK => new Expression('NULL')
371 ])->where->in(
372 Transaction::PK,
373 $transactions
374 );
375 $zdb->execute($update);
376 }
377
378 //delete transactions
379 $del_qry = $zdb->delete(Transaction::TABLE);
380 $del_qry->where->in(self::PK, $list);
381 $zdb->execute($del_qry);
382
383 //delete groups membership/mamagmentship
384 Groups::removeMembersFromGroups($list);
385
386 //delete reminders
387 $del_qry = $zdb->delete(Reminder::TABLE);
388 $del_qry->where->in(
389 'reminder_dest',
390 $list
391 );
392 $zdb->execute($del_qry);
393
394 //delete dynamic fields values
395 $del_qry = $zdb->delete(DynamicFieldsHandle::TABLE);
396 $del_qry->where(['field_form' => 'adh']);
397 $del_qry->where->in('item_id', $list);
398 $zdb->execute($del_qry);
399
400 //delete members
401 $del_qry = $zdb->delete(self::TABLE);
402 $del_qry->where->in(
403 self::PK,
404 $list
405 );
406 $zdb->execute($del_qry);
407
408 //commit all changes
409 $zdb->connection->commit();
410
411 foreach ($processed as $p) {
412 $emitter->dispatch(new GaletteEvent('member.remove', $p));
413 }
414
415 //add a history entry
416 $hist->add(
417 _T("Delete members cards, transactions and dues"),
418 $infos
419 );
420
421 return true;
422 } catch (Throwable $e) {
423 if ($zdb->connection->inTransaction()) {
424 $zdb->connection->rollBack();
425 }
426 if ($e->getCode() == 23000) {
427 Analog::log(
428 'Member still have existing dependencies in the ' .
429 'database, maybe a mailing or some content from a ' .
430 'plugin. Please remove dependencies before trying ' .
431 'to remove him.',
432 Analog::ERROR
433 );
434 $this->errors[] = _T("Cannot remove a member who still have dependencies (mailings, ...)");
435 } else {
436 Analog::log(
437 'Unable to delete selected member(s) |' .
438 $e->getMessage(),
439 Analog::ERROR
440 );
441 throw $e;
442 }
443 return false;
444 }
445 }
446
447 /**
448 * Get members list
449 *
450 * @param boolean $as_members return the results as an array of
451 * Member object.
452 * @param array $fields field(s) name(s) to get. Should be a string or
453 * an array. If null, all fields will be
454 * returned
455 *
456 * @return Adherent[]|ResultSet
457 */
458 public function getList($as_members = false, $fields = null)
459 {
460 return $this->getMembersList(
461 $as_members,
462 $fields,
463 false,
464 false,
465 false,
466 true,
467 false
468 );
469 }
470
471 /**
472 * Get members list with public information available
473 *
474 * @param boolean $with_photos get only members which have uploaded a
475 * photo (for trombinoscope)
476 *
477 * @return Adherent[]
478 */
479 public function getPublicList($with_photos)
480 {
481 global $zdb;
482
483 try {
484 $select = $this->buildSelect(
485 self::SHOW_PUBLIC_LIST,
486 null,
487 $with_photos,
488 true
489 );
490
491 $this->filters->setLimits($select);
492
493 $results = $zdb->execute($select);
494 $members = array();
495 $deps = array(
496 'groups' => false,
497 'dues' => false,
498 'picture' => $with_photos
499 );
500 foreach ($results as $row) {
501 $members[] = new Adherent($zdb, $row, $deps);
502 }
503 return $members;
504 } catch (Throwable $e) {
505 Analog::log(
506 'Cannot list members with public information (photos: '
507 . $with_photos . ') | ' . $e->getMessage(),
508 Analog::WARNING
509 );
510 throw $e;
511 }
512 }
513
514 /**
515 * Get list of members that has been selected
516 *
517 * @param array $ids an array of members id that has been selected
518 * @param array $orderby SQL order clause (optional)
519 * @param boolean $with_photos Should photos be loaded?
520 * @param boolean $as_members Return Adherent[] or simple ResultSet
521 * @param array $fields Fields to use
522 * @param boolean $export True if we are exporting
523 * @param boolean $dues True if load dues as Adherent dependency
524 * @param boolean $parent True if load parent as Adherent dependency
525 *
526 * @return Adherent[]|false
527 */
528 public function getArrayList(
529 $ids,
530 $orderby = null,
531 $with_photos = false,
532 $as_members = true,
533 $fields = null,
534 $export = false,
535 $dues = false,
536 $parent = false
537 ) {
538 global $zdb;
539
540 if (!is_array($ids) || count($ids) < 1) {
541 Analog::log('No member selected for labels.', Analog::INFO);
542 return false;
543 }
544
545 try {
546 $damode = self::SHOW_ARRAY_LIST;
547 if ($export === true) {
548 $damode = self::SHOW_EXPORT;
549 }
550 $select = $this->buildSelect(
551 $damode,
552 $fields,
553 false,
554 false
555 );
556 $select->where->in('a.' . self::PK, $ids);
557 if (is_array($orderby) && count($orderby) > 0) {
558 foreach ($orderby as $o) {
559 $select->order($o);
560 }
561 }
562
563 $results = $zdb->execute($select);
564
565 $members = array();
566 $deps = array(
567 'picture' => $with_photos,
568 'groups' => false,
569 'dues' => $dues,
570 'parent' => $parent
571 );
572 foreach ($results as $o) {
573 if ($as_members === true) {
574 $members[] = new Adherent($zdb, $o, $deps);
575 } else {
576 $members[] = $o;
577 }
578 }
579 return $members;
580 } catch (Throwable $e) {
581 Analog::log(
582 'Cannot load members form ids array | ' . $e->getMessage(),
583 Analog::WARNING
584 );
585 throw $e;
586 }
587 }
588
589 /**
590 * Builds the SELECT statement
591 *
592 * @param int $mode the current mode (see self::SHOW_*)
593 * @param array $fields fields list to retrieve
594 * @param bool $photos true if we want to get only members with photos
595 * Default to false, only relevant for SHOW_PUBLIC_LIST
596 * @param bool $count true if we want to count members, defaults to false
597 *
598 * @return Select SELECT statement
599 */
600 private function buildSelect($mode, $fields, $photos, $count = false): Select
601 {
602 global $zdb, $login;
603
604 try {
605 if ($fields != null && is_array($fields) && !in_array('id_adh', $fields)) {
606 $fields[] = 'id_adh';
607 }
608 $fieldsList = ($fields != null)
609 ? ((!is_array($fields) || count($fields) < 1) ? (array)'*'
610 : $fields) : (array)'*';
611
612 $select = $zdb->select(self::TABLE, 'a');
613
614 $select->columns($fieldsList);
615
616 $select->quantifier('DISTINCT');
617
618 $select->join(
619 array('so' => PREFIX_DB . Social::TABLE),
620 'a.' . Adherent::PK . '=so.' . Adherent::PK,
621 array(),
622 $select::JOIN_LEFT
623 );
624
625 $select->join(
626 array('parent' => PREFIX_DB . self::TABLE),
627 'a.parent_id=parent.' . self::PK,
628 array(),
629 $select::JOIN_LEFT
630 );
631
632 switch ($mode) {
633 case self::SHOW_STAFF:
634 case self::SHOW_LIST:
635 case self::SHOW_ARRAY_LIST:
636 case self::SHOW_EXPORT:
637 $select->join(
638 array('status' => PREFIX_DB . Status::TABLE),
639 'a.' . Status::PK . '=status.' . Status::PK,
640 array('priorite_statut')
641 );
642 break;
643 case self::SHOW_MANAGED:
644 $select->join(
645 array('status' => PREFIX_DB . Status::TABLE),
646 'a.' . Status::PK . '=status.' . Status::PK
647 )->join(
648 array('gr' => PREFIX_DB . Group::GROUPSUSERS_TABLE),
649 'a.' . Adherent::PK . '=gr.' . Adherent::PK,
650 array()
651 )->join(
652 array('m' => PREFIX_DB . Group::GROUPSMANAGERS_TABLE),
653 'gr.' . Group::PK . '=m.' . Group::PK,
654 array()
655 )->where(['m.' . Adherent::PK => $login->id]);
656 break;
657 case self::SHOW_PUBLIC_LIST:
658 if ($photos) {
659 $select->join(
660 array('picture' => PREFIX_DB . Picture::TABLE),
661 'a.' . self::PK . '= picture.' . self::PK,
662 array()
663 );
664 }
665 break;
666 }
667
668 //check for contributions filtering
669 if (
670 $this->filters instanceof AdvancedMembersList
671 && $this->filters->withinContributions()
672 ) {
673 $select->join(
674 array('ct' => PREFIX_DB . Contribution::TABLE),
675 'ct.' . self::PK . '=a.' . self::PK,
676 array(),
677 $select::JOIN_LEFT
678 );
679 }
680
681 //check if there are dynamic fields in filter
682 $hasDf = false;
683 $dfs = array();
684 if ($this->filters instanceof AdvancedMembersList) {
685 if (
686 (bool)count($this->filters->free_search)
687 && !isset($this->filters->free_search['empty'])
688 ) {
689 $free_searches = $this->filters->free_search;
690 foreach ($free_searches as $fs) {
691 if (strpos($fs['field'], 'dyn_') === 0) {
692 // simple dynamic fields
693 $hasDf = true;
694 $dfs[] = str_replace('dyn_', '', $fs['field']);
695 }
696 }
697 }
698 }
699
700 //check if there are dynamic fields for contributions in filter
701 $hasDfc = false;
702 $hasCdfc = false;
703 $cdfcs = array();
704
705 if (
706 $this->filters instanceof AdvancedMembersList
707 && $this->filters->withinContributions()
708 ) {
709 if (
710 count($this->filters->contrib_dynamic) > 0
711 && !isset($this->filters->contrib_dynamic['empty'])
712 ) {
713 $hasDfc = true;
714
715 //check if there are dynamic fields in the filter
716 foreach ($this->filters->contrib_dynamic as $k => $cd) {
717 $dyn_field = DynamicField::loadFieldType($zdb, (int)$k);
718 if ($dyn_field instanceof \Galette\DynamicFields\Choice) {
719 $hasCdfc = true;
720 $cdfcs[] = $k;
721 }
722 }
723 }
724 }
725
726 if ($hasDfc === true || $hasCdfc === true) {
727 $select->join(
728 array('dfc' => PREFIX_DB . DynamicFieldsHandle::TABLE),
729 'dfc.item_id=ct.' . Contribution::PK,
730 array(),
731 $select::JOIN_LEFT
732 );
733 }
734
735 // simple dynamic fields
736 if ($hasDf === true) {
737 foreach ($dfs as $df) {
738 $subselect = $zdb->select(DynamicFieldsHandle::TABLE, 'df');
739 $subselect->columns(
740 [
741 'item_id' => 'item_id',
742 'val' => 'field_val'
743 ]
744 );
745 $subselect->where(['df.field_form' => 'adh']);
746 $subselect->where(['df.field_id' => $df]);
747 $select->join(
748 array('df' . $df => $subselect),
749 'a.id_adh = df' . $df . '.item_id',
750 array(),
751 $select::JOIN_LEFT
752 );
753 }
754 }
755
756 // choice dynamic fields
757 if ($hasCdfc === true) {
758 foreach ($cdfcs as $cdf) {
759 $rcdf_field = sprintf(
760 '%s.%s',
761 $zdb->platform->quoteIdentifier('cdfc' . $cdf),
762 $zdb->platform->quoteIdentifier('id')
763 );
764 if (TYPE_DB === 'pgsql') {
765 $rcdf_field = $rcdf_field . '::text';
766 }
767
768 $select->join(
769 array('cdfc' . $cdf => DynamicField::getFixedValuesTableName($cdf, true)),
770 new Expression(
771 sprintf(
772 '%s = %s.%s',
773 $rcdf_field,
774 $zdb->platform->quoteIdentifier('dfc'),
775 $zdb->platform->quoteIdentifier('field_val')
776 )
777 ),
778 array(),
779 $select::JOIN_LEFT
780 );
781 }
782 }
783
784 if ($mode == self::SHOW_LIST || $mode == self::SHOW_MANAGED) {
785 if ($this->filters !== false) {
786 $this->buildWhereClause($select);
787 }
788 } elseif ($mode == self::SHOW_PUBLIC_LIST) {
789 $select->where(
790 array(
791 new PredicateSet(
792 array(
793 new Operator(
794 'a.date_echeance',
795 '>=',
796 date('Y-m-d')
797 ),
798 new Operator(
799 'a.bool_exempt_adh',
800 '=',
801 new Expression('true')
802 )
803 ),
804 PredicateSet::OP_OR
805 ),
806 new PredicateSet(
807 array(
808 new Operator(
809 'a.bool_display_info',
810 '=',
811 new Expression('true')
812 ),
813 new Operator(
814 'a.activite_adh',
815 '=',
816 new Expression('true')
817 )
818 ),
819 PredicateSet::OP_AND
820 )
821 )
822 );
823 }
824
825 if ($mode === self::SHOW_STAFF) {
826 $select->where->lessThan(
827 'status.priorite_statut',
828 self::NON_STAFF_MEMBERS
829 );
830 }
831
832 if ($count) {
833 $this->proceedCount($select);
834 }
835
836 $this->buildOrderClause($select, $fields);
837
838 return $select;
839 } catch (Throwable $e) {
840 Analog::log(
841 'Cannot build SELECT clause for members | ' . $e->getMessage(),
842 Analog::WARNING
843 );
844 throw $e;
845 }
846 }
847
848 /**
849 * Count members from the query
850 *
851 * @param Select $select Original select
852 *
853 * @return void
854 */
855 private function proceedCount(Select $select)
856 {
857 global $zdb;
858
859 try {
860 $countSelect = clone $select;
861 $countSelect->reset($countSelect::COLUMNS);
862 $countSelect->reset($countSelect::ORDER);
863 $countSelect->reset($countSelect::HAVING);
864 $joins = $countSelect->joins;
865 $countSelect->reset($countSelect::JOINS);
866 foreach ($joins as $join) {
867 $countSelect->join(
868 $join['name'],
869 $join['on'],
870 [],
871 $join['type']
872 );
873 unset($join['columns']);
874 }
875 $countSelect->columns(
876 array(
877 'count' => new Expression('count(DISTINCT a.' . self::PK . ')')
878 )
879 );
880
881 $have = $select->having;
882 if ($have->count() > 0) {
883 foreach ($have->getPredicates() as $h) {
884 $countSelect->where($h);
885 }
886 }
887
888 $results = $zdb->execute($countSelect);
889
890 $this->count = (int)$results->current()->count;
891 if (isset($this->filters) && $this->count > 0) {
892 $this->filters->setCounter($this->count);
893 }
894 } catch (Throwable $e) {
895 Analog::log(
896 'Cannot count members | ' . $e->getMessage(),
897 Analog::WARNING
898 );
899 throw $e;
900 }
901 }
902
903 /**
904 * Builds the order clause
905 *
906 * @param Select $select Original select
907 * @param array $fields Fields list to ensure ORDER clause
908 * references selected fields. Optional.
909 *
910 * @return Select
911 */
912 private function buildOrderClause(Select $select, $fields = null): Select
913 {
914 $order = array();
915
916 switch ($this->filters->orderby) {
917 case self::ORDERBY_NICKNAME:
918 if ($this->canOrderBy('pseudo_adh', $fields)) {
919 $order[] = 'pseudo_adh ' . $this->filters->getDirection();
920 }
921 break;
922 case self::ORDERBY_STATUS:
923 if ($this->canOrderBy('priorite_statut', $fields)) {
924 $order[] = 'priorite_statut ' . $this->filters->getDirection();
925 }
926 break;
927 case self::ORDERBY_MODIFDATE:
928 if ($this->canOrderBy('date_modif_adh', $fields)) {
929 $order[] = 'date_modif_adh ' . $this->filters->getDirection();
930 }
931 break;
932 case 'list_adh_contribstatus':
933 case self::ORDERBY_FEE_STATUS:
934 if ($this->canOrderBy('bool_exempt_adh', $fields)) {
935 $order[] = 'bool_exempt_adh ' . $this->filters->getDirection();
936 }
937
938 if ($this->canOrderBy('date_echeance', $fields)) {
939 $order[] = 'date_echeance ' . $this->filters->getDirection();
940 }
941 break;
942 case self::ORDERBY_ID:
943 if ($this->canOrderBy('id_adh', $fields)) {
944 $order[] = 'id_adh ' . $this->filters->getDirection();
945 }
946 break;
947 case 'list_adh_name':
948 case 'nom_adh':
949 case 'prenom_adh':
950 case self::ORDERBY_NAME:
951 //defaults
952 break;
953 default:
954 if ($this->canOrderBy($this->filters->orderby, $fields)) {
955 $order[] = 'a.' . $this->filters->orderby . ' ' . $this->filters->getDirection();
956 }
957 break;
958 }
959
960 //anyways, we want to order by firstname, lastname
961 if ($this->canOrderBy('nom_adh', $fields)) {
962 $order[] = 'nom_adh ' . $this->filters->getDirection();
963 }
964 if ($this->canOrderBy('prenom_adh', $fields)) {
965 $order[] = 'prenom_adh ' . $this->filters->getDirection();
966 }
967
968 $select->order($order);
969 return $select;
970 }
971
972 /**
973 * Is field allowed to order? it should be present in
974 * provided fields list (those that are SELECT'ed).
975 *
976 * @param string $field_name Field name to order by
977 * @param array $fields SELECTE'ed fields
978 *
979 * @return boolean
980 */
981 private function canOrderBy($field_name, $fields)
982 {
983 if ($fields === null) {
984 return true;
985 } elseif (!is_array($fields)) {
986 return false;
987 } elseif (in_array($field_name, $fields)) {
988 return true;
989 } else {
990 Analog::log(
991 'Trying to order by ' . $field_name . ' while it is not in ' .
992 'selected fields.',
993 Analog::WARNING
994 );
995 return false;
996 }
997 }
998
999 /**
1000 * Builds where clause, for filtering on simple list mode
1001 *
1002 * @param Select $select Original select
1003 *
1004 * @return void
1005 */
1006 private function buildWhereClause(Select $select)
1007 {
1008 global $zdb, $login;
1009
1010 try {
1011 if ($this->filters->email_filter == self::FILTER_W_EMAIL) {
1012 $select->where('(a.email_adh != \'\' OR a.parent_id IS NOT NULL AND parent.email_adh != \'\')');
1013 }
1014 if ($this->filters->email_filter == self::FILTER_WO_EMAIL) {
1015 $select->where('(a.email_adh = \'\' OR a.email_adh IS NULL) AND (parent.email_adh = \'\' OR parent.email_adh IS NULL)');
1016 }
1017
1018 if ($this->filters->filter_str != '') {
1019 $token = $zdb->platform->quoteValue(
1020 '%' . strtolower($this->filters->filter_str) . '%'
1021 );
1022 switch ($this->filters->field_filter) {
1023 case self::FILTER_NAME:
1024 if (TYPE_DB === 'pgsql') {
1025 $sep = " || ' ' || ";
1026 $pre = '';
1027 $post = '';
1028 } else {
1029 $sep = ', " ", ';
1030 $pre = 'CONCAT(';
1031 $post = ')';
1032 }
1033
1034 $select->where(
1035 '(' .
1036 $pre . 'LOWER(a.nom_adh)' . $sep .
1037 'LOWER(a.prenom_adh)' . $sep .
1038 'LOWER(a.pseudo_adh)' . $post . ' LIKE ' .
1039 $token
1040 . ' OR ' .
1041 $pre . 'LOWER(a.prenom_adh)' . $sep .
1042 'LOWER(a.nom_adh)' . $sep .
1043 'LOWER(a.pseudo_adh)' . $post . ' LIKE ' .
1044 $token
1045 . ')'
1046 );
1047 break;
1048 case self::FILTER_COMPANY_NAME:
1049 $select->where(
1050 'LOWER(a.societe_adh) LIKE ' .
1051 $token
1052 );
1053 break;
1054 case self::FILTER_ADDRESS:
1055 $select->where(
1056 '(' .
1057 'LOWER(a.adresse_adh) LIKE ' . $token
1058 . ' OR ' .
1059 'a.cp_adh LIKE ' . $token
1060 . ' OR ' .
1061 'LOWER(a.ville_adh) LIKE ' . $token
1062 . ' OR ' .
1063 'LOWER(a.pays_adh) LIKE ' . $token
1064 . ')'
1065 );
1066 break;
1067 case self::FILTER_MAIL:
1068 $select->where(
1069 '(' .
1070 'LOWER(a.email_adh) LIKE ' . $token
1071 . ' OR ' .
1072 'LOWER(so.url) LIKE ' . $token
1073 . ')'
1074 );
1075 break;
1076 case self::FILTER_JOB:
1077 $select->where(
1078 'LOWER(a.prof_adh) LIKE ' . $token
1079 );
1080 break;
1081 case self::FILTER_INFOS:
1082 $more = '';
1083 if ($login->isAdmin() || $login->isStaff()) {
1084 $more = ' OR LOWER(a.info_adh) LIKE ' . $token;
1085 }
1086 $select->where(
1087 '(LOWER(a.info_public_adh) LIKE ' .
1088 $token . $more . ')'
1089 );
1090 break;
1091 case self::FILTER_NUMBER:
1092 $select->where->equalTo('a.num_adh', $this->filters->filter_str);
1093 break;
1094 case self::FILTER_ID:
1095 $select->where->equalTo('a.id_adh', $this->filters->filter_str);
1096 break;
1097 }
1098 }
1099
1100 if ($this->filters->membership_filter) {
1101 switch ($this->filters->membership_filter) {
1102 case self::MEMBERSHIP_NEARLY:
1103 $now = new \DateTime();
1104 $due_date = clone $now;
1105 $due_date->modify('+30 days');
1106 $select->where
1107 ->greaterThanOrEqualTo(
1108 'a.date_echeance',
1109 $now->format('Y-m-d')
1110 )->lessThanOrEqualTo(
1111 'a.date_echeance',
1112 $due_date->format('Y-m-d')
1113 )->equalTo('a.bool_exempt_adh', new Expression('false'));
1114 break;
1115 case self::MEMBERSHIP_LATE:
1116 $select->where
1117 ->lessThan(
1118 'a.date_echeance',
1119 date('Y-m-d', time())
1120 )->equalTo('a.bool_exempt_adh', new Expression('false'));
1121 break;
1122 case self::MEMBERSHIP_UP2DATE:
1123 $select->where(
1124 '(' . 'a.date_echeance >= \'' . date('Y-m-d', time())
1125 . '\' OR a.bool_exempt_adh=true)'
1126 );
1127 break;
1128 case self::MEMBERSHIP_NEVER:
1129 $select->where('a.date_echeance IS NULL')
1130 ->where('a.bool_exempt_adh = false');
1131 break;
1132 case self::MEMBERSHIP_STAFF:
1133 $select->where->lessThan(
1134 'status.priorite_statut',
1135 self::NON_STAFF_MEMBERS
1136 );
1137 break;
1138 case self::MEMBERSHIP_ADMIN:
1139 $select->where->equalTo('a.bool_admin_adh', true);
1140 break;
1141 case self::MEMBERSHIP_NONE:
1142 $select->where->equalTo('a.id_statut', Status::DEFAULT_STATUS);
1143 break;
1144 }
1145 }
1146
1147 if ($this->filters->filter_account) {
1148 switch ($this->filters->filter_account) {
1149 case self::ACTIVE_ACCOUNT:
1150 $select->where('a.activite_adh=true');
1151 break;
1152 case self::INACTIVE_ACCOUNT:
1153 $select->where('a.activite_adh=false');
1154 break;
1155 }
1156 }
1157
1158 if ($this->filters->group_filter) {
1159 $select->join(
1160 array('g' => PREFIX_DB . Group::GROUPSUSERS_TABLE),
1161 'a.' . Adherent::PK . '=g.' . Adherent::PK,
1162 array(),
1163 $select::JOIN_LEFT
1164 )->join(
1165 array('gs' => PREFIX_DB . Group::TABLE),
1166 'gs.' . Group::PK . '=g.' . Group::PK,
1167 array(),
1168 $select::JOIN_LEFT
1169 )->where(
1170 '(g.' . Group::PK . ' = ' . $zdb->platform->quoteValue($this->filters->group_filter) .
1171 ' OR gs.parent_group = NULL OR gs.parent_group = ' .
1172 $this->filters->group_filter . ')'
1173 );
1174 }
1175
1176 if ($this->filters instanceof AdvancedMembersList) {
1177 $this->buildAdvancedWhereClause($select);
1178 }
1179 } catch (Throwable $e) {
1180 Analog::log(
1181 __METHOD__ . ' | ' . $e->getMessage(),
1182 Analog::WARNING
1183 );
1184 throw $e;
1185 }
1186 }
1187
1188 /**
1189 * Builds where clause, for advanced filtering on simple list mode
1190 *
1191 * @param Select $select Original select
1192 *
1193 * @return void
1194 */
1195 private function buildAdvancedWhereClause(Select $select)
1196 {
1197 global $zdb;
1198
1199 // Search members who belong to any (OR) or all (AND) listed groups.
1200 // Idea is to build an array of members ID that fits groups selection
1201 // we will use in the final query.
1202 // The OR case is quite simple, AND is a bit more complex; since we must
1203 // check each member do belongs to all listed groups.
1204 if (
1205 count($this->filters->groups_search) > 0
1206 && !isset($this->filters->groups_search['empty'])
1207 ) {
1208 $wheregroups = [];
1209
1210 foreach ($this->filters->groups_search as $gs) { // then add a row for each group
1211 $wheregroups[] = $gs['group'];
1212 }
1213
1214 $gselect = $zdb->select(Group::GROUPSUSERS_TABLE, 'gu');
1215 $gselect->columns(
1216 array('id_adh')
1217 )->join(
1218 array('g' => PREFIX_DB . Group::TABLE),
1219 'gu.id_group=g.' . Group::PK,
1220 array(),
1221 $select::JOIN_LEFT
1222 )->where(
1223 array(
1224 'g.id_group' => ':group',
1225 'g.parent_group' => ':pgroup'
1226 ),
1227 PredicateSet::OP_OR
1228 );
1229 $gselect->group(['gu.id_adh']);
1230
1231 $stmt = $zdb->sql->prepareStatementForSqlObject($gselect);
1232
1233 $mids = [];
1234 $ids = [];
1235 foreach ($this->filters->groups_search as $gs) { // then add a row for each ig/searched group pair
1236 $gresults = $stmt->execute(
1237 array(
1238 'group' => $gs['group'],
1239 'pgroup' => $gs['group']
1240 )
1241 );
1242
1243 switch ($this->filters->groups_search_log_op) {
1244 case AdvancedMembersList::OP_AND:
1245 foreach ($gresults as $gresult) {
1246 if (!isset($ids[$gresult['id_adh']])) {
1247 $ids[$gresult['id_adh']] = 0;
1248 }
1249 $ids[$gresult['id_adh']] += 1;
1250 }
1251 break;
1252 case AdvancedMembersList::OP_OR:
1253 foreach ($gresults as $gresult) {
1254 $mids[$gresult['id_adh']] = $gresult['id_adh'];
1255 }
1256 break;
1257 }
1258 }
1259
1260 if (count($ids)) {
1261 foreach ($ids as $id_adh => $count) {
1262 if ($count == count($wheregroups)) {
1263 $mids[$id_adh] = $id_adh;
1264 }
1265 }
1266 }
1267
1268 if (count($mids)) {
1269 //limit on found members
1270 $select->where->in('a.id_adh', $mids);
1271 } else {
1272 //no match in groups, end of game.
1273 $select->where('false = true');
1274 }
1275 }
1276
1277 //FIXME: should be retrieved from members_fields
1278 $dates = [
1279 'a.ddn_adh' => 'birth_date',
1280 'a.date_crea_adh' => 'creation_date',
1281 'a.date_modif_adh' => 'modif_date',
1282 'a.date_echeance' => 'due_date',
1283 'ct.date_enreg' => 'contrib_creation_date',
1284 'ct.date_debut_cotis' => 'contrib_begin_date',
1285 'ct.date_fin_cotis' => 'contrib_end_date'
1286 ];
1287
1288 foreach ($dates as $field => $property) {
1289 $bprop = "r{$property}_begin";
1290 if ($this->filters->$bprop) {
1291 $d = new \DateTime($this->filters->$bprop);
1292 $select->where->greaterThanOrEqualTo(
1293 $field,
1294 $d->format('Y-m-d')
1295 );
1296 }
1297 $eprop = "r{$property}_end";
1298 if ($this->filters->$eprop) {
1299 $d = new \DateTime($this->filters->$eprop);
1300 $select->where->lessThanOrEqualTo(
1301 $field,
1302 $d->format('Y-m-d')
1303 );
1304 }
1305 }
1306
1307 if ($this->filters->show_public_infos) {
1308 switch ($this->filters->show_public_infos) {
1309 case self::FILTER_W_PUBINFOS:
1310 $select->where('a.bool_display_info = true');
1311 break;
1312 case self::FILTER_WO_PUBINFOS:
1313 $select->where('a.bool_display_info = false');
1314 break;
1315 case self::FILTER_DC_PUBINFOS:
1316 //nothing to do here.
1317 break;
1318 }
1319 }
1320
1321 if ($this->filters->status) {
1322 $select->where->in(
1323 'a.id_statut',
1324 $this->filters->status
1325 );
1326 }
1327
1328 if (
1329 $this->filters->contrib_min_amount
1330 || $this->filters->contrib_max_amount
1331 ) {
1332 if ($this->filters->contrib_min_amount) {
1333 $select->where->greaterThanOrEqualTo(
1334 'ct.montant_cotis',
1335 $this->filters->contrib_min_amount
1336 );
1337 }
1338 if ($this->filters->contrib_max_amount) {
1339 $select->where->lessThanOrEqualTo(
1340 'ct.montant_cotis',
1341 $this->filters->contrib_max_amount
1342 );
1343 }
1344 }
1345
1346 if ($this->filters->contributions_types) {
1347 $select->where->in(
1348 'ct.id_type_cotis',
1349 $this->filters->contributions_types
1350 );
1351 }
1352
1353 if ($this->filters->payments_types) {
1354 $select->where->in(
1355 'ct.type_paiement_cotis',
1356 $this->filters->payments_types
1357 );
1358 }
1359
1360 if (
1361 count($this->filters->contrib_dynamic) > 0
1362 && !isset($this->filters->contrib_dynamic['empty'])
1363 ) {
1364 foreach ($this->filters->contrib_dynamic as $k => $cd) {
1365 $qop = ' LIKE ';
1366
1367 if (is_array($cd)) {
1368 //dynamic choice spotted!
1369 $prefix = 'cdfc' . $k . '.';
1370 $qry = 'dfc.field_form = \'contrib\' AND ' .
1371 'dfc.field_id = ' . $k;
1372 $field = 'id';
1373 $select->where($qry);
1374 $select->where->in($prefix . $field, $cd);
1375 } else {
1376 //dynamic field spotted!
1377 $prefix = 'dfc.';
1378 $qry = 'dfc.field_form = \'contrib\' AND ' .
1379 'dfc.field_id = ' . $k . ' AND ';
1380 $field = 'field_val';
1381
1382 $dyn_field = DynamicField::loadFieldType($zdb, (int)$k);
1383
1384 if ($dyn_field instanceof \Galette\DynamicFields\Boolean) {
1385 if ($cd == 1) {
1386 $qry .= $field . ' = ' . (int)$cd;
1387 }
1388 $select->where($qry);
1389 } elseif ($dyn_field instanceof \Galette\DynamicFields\Date) {
1390 //dynamic dates are stored in their localized format :/
1391 //use current lang format to query for now
1392 //FIXME works with french formatted date only -_-
1393 if ($zdb->isPostgres()) {
1394 $qop = '=';
1395 $store_fmt = __("Y-m-d") === 'Y-m-d' ? 'YYYY-MM-DD' : 'DD/MM/YYYY';
1396 $cd = "to_date('" . $cd . "', '" . $store_fmt . "')";
1397 $qry .= "to_date(" . $prefix . $field . ", '$store_fmt')";
1398 } else {
1399 $store_fmt = __("Y-m-d") === 'Y-m-d' ? '%Y-%m-%d' : '%d/%m/%Y';
1400 $cd = "STR_TO_DATE('" . $cd . "', '" . $store_fmt . "')";
1401 $qry .= 'STR_TO_DATE(' . $prefix . $field . ', \'' . $store_fmt . '\') ';
1402 }
1403 $qry .= $qop . ' ' . $cd;
1404 $select->where($qry);
1405 } else {
1406 $qry .= 'LOWER(' . $prefix . $field . ') ' . $qop . ' ';
1407 $select->where($qry . $zdb->platform->quoteValue('%' . strtolower($cd) . '%'));
1408 }
1409 }
1410 }
1411 }
1412
1413 if (
1414 count($this->filters->free_search) > 0
1415 && !isset($this->filters->free_search['empty'])
1416 ) {
1417 foreach ($this->filters->free_search as $fs) {
1418 $fs['search'] = mb_strtolower($fs['search']);
1419 $qop = null;
1420 switch ($fs['qry_op']) {
1421 case AdvancedMembersList::OP_EQUALS:
1422 $qop = '=';
1423 break;
1424 case AdvancedMembersList::OP_CONTAINS:
1425 $qop = 'LIKE';
1426 $fs['search'] = '%' . $fs['search'] . '%';
1427 break;
1428 case AdvancedMembersList::OP_NOT_EQUALS:
1429 $qop = '!=';
1430 break;
1431 case AdvancedMembersList::OP_NOT_CONTAINS:
1432 $qop = 'NOT LIKE';
1433 $fs['search'] = '%' . $fs['search'] . '%';
1434 break;
1435 case AdvancedMembersList::OP_STARTS_WITH:
1436 $qop = 'LIKE';
1437 $fs['search'] = $fs['search'] . '%';
1438 break;
1439 case AdvancedMembersList::OP_ENDS_WITH:
1440 $qop = 'LIKE';
1441 $fs['search'] = '%' . $fs['search'];
1442 break;
1443 case AdvancedMembersList::OP_BEFORE:
1444 $qop = '<';
1445 break;
1446 case AdvancedMembersList::OP_AFTER:
1447 $qop = '>';
1448 break;
1449 default:
1450 Analog::log(
1451 'Unknown query operator: ' . $fs['qry_op'] .
1452 ' (will fallback to equals)',
1453 Analog::WARNING
1454 );
1455 $qop = '=';
1456 break;
1457 }
1458
1459 $qry = '';
1460 $prefix = 'a.';
1461 $dyn_field = false;
1462 if (strpos($fs['field'], 'dyn_') === 0) {
1463 // simple dynamic field spotted!
1464 $index = str_replace('dyn_', '', $fs['field']);
1465 $dyn_field = DynamicField::loadFieldType($zdb, (int)$index);
1466 $prefix = 'df' . $index . '.';
1467 $fs['field'] = 'val';
1468 }
1469
1470 //handle socials networks
1471 if (strpos($fs['field'], 'socials_') === 0) {
1472 //social networks
1473 $type = str_replace('socials_', '', $fs['field']);
1474 $prefix = 'so.';
1475 $fs['field'] = 'url';
1476 $select->where(['so.type' => $type]);
1477 }
1478
1479 if ($dyn_field && $dyn_field instanceof \Galette\DynamicFields\Boolean) {
1480 if ($fs['search'] != 0) {
1481 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1482 $fs['search'];
1483 } else {
1484 $qry .= $prefix . $fs['field'] . ' IS NULL';
1485 }
1486 } elseif (!strncmp($fs['field'], 'bool_', strlen('bool_'))) {
1487 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1488 $fs['search'];
1489 } elseif (
1490 $fs['qry_op'] === AdvancedMembersList::OP_BEFORE
1491 || $fs['qry_op'] === AdvancedMembersList::OP_AFTER
1492 ) {
1493 if ($prefix === 'a.') {
1494 //dates are OK in the main fields. no cast, just query!
1495 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1496 $zdb->platform->quoteValue($fs['search']);
1497 } else {
1498 //dynamic dates are stored in their localized format :/
1499 //use current lang format to query for now
1500 //FIXME works with french formatted date only -_-
1501 if ($zdb->isPostgres()) {
1502 $store_fmt = __("Y-m-d") === 'Y-m-d' ? 'YYYY-MM-DD' : 'DD/MM/YYYY';
1503 $fs['search'] = "to_date('" . $fs['search'] . "', '" . $store_fmt . "')";
1504 $qry .= "to_date('" . $prefix . $fs['field'] . "', '$store_fmt')";
1505 } else {
1506 $store_fmt = __("Y-m-d") === 'Y-m-d' ? '%Y-%m-%d' : '%d/%m/%Y';
1507 $fs['search'] = "STR_TO_DATE('" . $fs['search'] . "', '" . $store_fmt . "')";
1508 $qry .= 'STR_TO_DATE(' . $prefix . $fs['field'] . ', \'' . $store_fmt . '\') ';
1509 }
1510
1511 $qry .= $qop . ' ' . $fs['search'];
1512 }
1513 } elseif ($fs['field'] == 'status_label') {
1514 $qry_pattern = '%p%field %op %value';
1515 $qry .= str_replace(
1516 [
1517 '%p',
1518 '%field',
1519 '%op',
1520 '%value'
1521 ],
1522 [
1523 'status.',
1524 'libelle_statut',
1525 $qop,
1526 $zdb->platform->quoteValue($fs['search'])
1527 ],
1528 $qry_pattern
1529 );
1530 } else {
1531 $field = $prefix . $fs['field'];
1532 if ($zdb->isPostgres()) {
1533 $field = 'CAST(' . $field . ' AS TEXT)';
1534 }
1535 $qry .= 'LOWER(' . $field . ') ' .
1536 $qop . ' ' . $zdb->platform->quoteValue($fs['search']);
1537 }
1538
1539 if ($fs['log_op'] === AdvancedMembersList::OP_AND) {
1540 $select->where($qry);
1541 } elseif ($fs['log_op'] === AdvancedMembersList::OP_OR) {
1542 $select->where($qry, PredicateSet::OP_OR);
1543 }
1544 }
1545 }
1546 }
1547
1548 /**
1549 * Login and password field cannot be empty.
1550 *
1551 * If those are not required, or if a file has been imported
1552 * (from a CSV file for example), we fill here random values.
1553 *
1554 * @return boolean
1555 */
1556 public function emptyLogins()
1557 {
1558 global $zdb;
1559
1560 try {
1561 $zdb->connection->beginTransaction();
1562 $select = $zdb->select(Adherent::TABLE);
1563 $select->columns(
1564 array('id_adh', 'login_adh', 'mdp_adh')
1565 )->where(
1566 new PredicateSet(
1567 array(
1568 new Operator(
1569 'login_adh',
1570 '=',
1571 ''
1572 ),
1573 new IsNull('login_adh'),
1574 new Operator(
1575 'mdp_adh',
1576 '=',
1577 ''
1578 ),
1579 new IsNull('mdp_adh'),
1580 ),
1581 PredicateSet::OP_OR
1582 )
1583 );
1584
1585 $results = $zdb->execute($select);
1586
1587 $processed = 0;
1588 if ($results->count() > 0) {
1589 $update = $zdb->update(Adherent::TABLE);
1590 $update->set(
1591 array(
1592 'login_adh' => ':login',
1593 'mdp_adh' => ':pass'
1594 )
1595 )->where->equalTo(Adherent::PK, ':id');
1596
1597 $stmt = $zdb->sql->prepareStatementForSqlObject($update);
1598
1599 $p = new \Galette\Core\Password($zdb);
1600
1601 foreach ($results as $m) {
1602 $dirty = false;
1603 if (
1604 $m->login_adh == ''
1605 || !isset($m->login_adh)
1606 || $m->login_adh == 'NULL'
1607 ) {
1608 $m->login_adh = $p->makeRandomPassword(15);
1609 $dirty = true;
1610 }
1611
1612 if (
1613 $m->mdp_adh == ''
1614 || !isset($m->mdp_adh)
1615 || $m->mdp_adh == 'NULL'
1616 ) {
1617 $randomp = $p->makeRandomPassword(15);
1618 $m->mdp_adh = password_hash(
1619 $randomp,
1620 PASSWORD_BCRYPT
1621 );
1622 $dirty = true;
1623 }
1624
1625 if ($dirty === true) {
1626 $stmt->execute(
1627 array(
1628 'login' => $m->login_adh,
1629 'pass' => $m->mdp_adh,
1630 'id' => $m->id_adh
1631 )
1632 );
1633 $processed++;
1634 }
1635 }
1636 }
1637 $zdb->connection->commit();
1638 $this->count = $processed;
1639 return true;
1640 } catch (Throwable $e) {
1641 $zdb->connection->rollBack();
1642 Analog::log(
1643 'An error occurred trying to retrieve members with ' .
1644 'empty logins/passwords (' . $e->getMessage(),
1645 Analog::ERROR
1646 );
1647 throw $e;
1648 }
1649 }
1650
1651 /**
1652 * Get reminders count based on members state of dues
1653 *
1654 * @return array
1655 */
1656 public function getRemindersCount()
1657 {
1658 global $zdb;
1659
1660 $reminders = array();
1661
1662 // Count close to be expired reminders
1663 $select = $zdb->select(Adherent::TABLE, 'a');
1664 $select->columns(
1665 array(
1666 'cnt' => new Expression('count(a.' . Adherent::PK . ')')
1667 )
1668 );
1669
1670 $select->join(
1671 array('parent' => PREFIX_DB . self::TABLE),
1672 'a.parent_id=parent.' . self::PK,
1673 array(),
1674 $select::JOIN_LEFT
1675 );
1676
1677 $select
1678 ->where('a.activite_adh=true')
1679 ->where('a.bool_exempt_adh=false');
1680
1681 $now = new \DateTime();
1682 $due_date = clone $now;
1683 $due_date->modify('+30 days');
1684
1685 $select->where
1686 ->greaterThanOrEqualTo('a.date_echeance', $now->format('Y-m-d'))
1687 ->lessThanOrEqualTo('a.date_echeance', $due_date->format('Y-m-d'));
1688
1689 $select_wo_mail = clone $select;
1690 //per default, limit to members who have an email address
1691 $select->where(
1692 '(a.email_adh != \'\' OR a.parent_id IS NOT NULL AND parent.email_adh != \'\')'
1693 );
1694 $select_wo_mail->where(
1695 '(a.email_adh = \'\' OR a.email_adh IS NULL) AND (parent.email_adh = \'\' OR parent.email_adh IS NULL)'
1696 );
1697
1698 $results = $zdb->execute($select);
1699 $res = $results->current();
1700 $reminders['impending'] = $res->cnt;
1701
1702 $results_wo_mail = $zdb->execute($select_wo_mail);
1703 $res_wo_mail = $results_wo_mail->current();
1704 $reminders['nomail']['impending'] = $res_wo_mail->cnt;
1705
1706 // Count late reminders
1707 $select = $zdb->select(Adherent::TABLE, 'a');
1708 $select->columns(
1709 array(
1710 'cnt' => new Expression('count(a.' . Adherent::PK . ')')
1711 )
1712 );
1713
1714 $select->join(
1715 array('parent' => PREFIX_DB . self::TABLE),
1716 'a.parent_id=parent.' . self::PK,
1717 array(),
1718 $select::JOIN_LEFT
1719 );
1720
1721 $select
1722 ->where('a.activite_adh=true')
1723 ->where('a.bool_exempt_adh=false');
1724
1725 $select->where
1726 ->lessThan('a.date_echeance', $now->format('Y-m-d'));
1727
1728 $select_wo_mail = clone $select;
1729 //per default, limit to members who have an email address
1730 $select->where(
1731 '(a.email_adh != \'\' OR a.parent_id IS NOT NULL AND parent.email_adh != \'\')'
1732 );
1733 $select_wo_mail->where(
1734 '(a.email_adh = \'\' OR a.email_adh IS NULL) AND (parent.email_adh = \'\' OR parent.email_adh IS NULL)'
1735 );
1736
1737 $results = $zdb->execute($select);
1738 $res = $results->current();
1739 $reminders['late'] = $res->cnt;
1740
1741 $results_wo_mail = $zdb->execute($select_wo_mail);
1742 $res_wo_mail = $results_wo_mail->current();
1743 $reminders['nomail']['late'] = $res_wo_mail->cnt;
1744
1745 return $reminders;
1746 }
1747
1748 /**
1749 * Get count for current query
1750 *
1751 * @return int
1752 */
1753 public function getCount()
1754 {
1755 return $this->count;
1756 }
1757
1758 /**
1759 * Get registered errors
1760 *
1761 * @return array
1762 */
1763 public function getErrors()
1764 {
1765 return $this->errors;
1766 }
1767
1768 /**
1769 * Get all existing emails
1770 *
1771 * @param Db $zdb Database instance
1772 *
1773 * @return array ['email' => 'id_adh']
1774 */
1775 public static function getEmails(Db $zdb)
1776 {
1777 $emails = [];
1778 $select = $zdb->select(self::TABLE);
1779 $select->columns([
1780 self::PK,
1781 'email_adh'
1782 ]);
1783 $select->where('email_adh != \'\' AND email_adh IS NOT NULL');
1784 $rows = $zdb->execute($select);
1785 foreach ($rows as $row) {
1786 $emails[$row->email_adh] = $row->{self::PK};
1787 }
1788 return $emails;
1789 }
1790
1791 /**
1792 * Get current filters
1793 *
1794 * @return MembersList
1795 */
1796 public function getFilters()
1797 {
1798 return $this->filters;
1799 }
1800
1801 /**
1802 * Get members list to instanciate dropdowns
1803 *
1804 * @param Db $zdb Database instance
1805 * @param Login $login Login instance
1806 * @param integer $current Current member
1807 *
1808 * @return array
1809 */
1810 public function getDropdownMembers(Db $zdb, Login $login, $current = null)
1811 {
1812 $members = [];
1813 $required_fields = array(
1814 'id_adh',
1815 'nom_adh',
1816 'prenom_adh',
1817 'pseudo_adh'
1818 );
1819
1820 $list_members = [];
1821 if ($login->isAdmin() || $login->isStaff()) {
1822 $list_members = $this->getList(false, $required_fields);
1823 } elseif ($login->isGroupManager()) {
1824 $list_members = $this->getManagedMembersList(false, $required_fields);
1825 }
1826
1827 if (count($list_members) > 0) {
1828 foreach ($list_members as $member) {
1829 $pk = Adherent::PK;
1830
1831 $members[$member->$pk] = Adherent::getNameWithCase(
1832 $member->nom_adh,
1833 $member->prenom_adh,
1834 false,
1835 $member->id_adh,
1836 $member->pseudo_adh
1837 );
1838 }
1839 }
1840
1841 //check if current attached member is part of the list
1842 if ($current !== null && !isset($members[$current])) {
1843 $members =
1844 [$current => Adherent::getSName($zdb, $current, true, true)] +
1845 $members
1846 ;
1847 }
1848
1849 return $members;
1850 }
1851 }
Galette membership management web application towards non profit (personal repository) | https://galette.eu