]> git.agnieray.net Git - galette.git/blob - galette/lib/Galette/Repository/Members.php
Projects / galette.git / blob


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