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


5d79a94ff3945b0d545545b7091bf7976af5c652
[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 if (is_array($cd)) {
718 $hasCdfc = true;
719 $cdfcs[] = $k;
720 }
721 }
722 }
723 }
724
725 if ($hasDfc === true || $hasCdfc === true) {
726 $select->join(
727 array('dfc' => PREFIX_DB . DynamicFieldsHandle::TABLE),
728 'dfc.item_id=ct.' . Contribution::PK,
729 array(),
730 $select::JOIN_LEFT
731 );
732 }
733
734 // simple dynamic fields
735 if ($hasDf === true) {
736 foreach ($dfs as $df) {
737 $subselect = $zdb->select(DynamicFieldsHandle::TABLE, 'df');
738 $subselect->columns(
739 [
740 'item_id' => 'item_id',
741 'val' => 'field_val'
742 ]
743 );
744 $subselect->where(['df.field_form' => 'adh']);
745 $subselect->where(['df.field_id' => $df]);
746 $select->join(
747 array('df' . $df => $subselect),
748 'a.id_adh = df' . $df . '.item_id',
749 array(),
750 $select::JOIN_LEFT
751 );
752 }
753 }
754
755 // choice dynamic fields
756 if ($hasCdfc === true) {
757 $cdf_field = 'cdf.id';
758 if (TYPE_DB === 'pgsql') {
759 $cdf_field .= '::text';
760 }
761
762 $cdf_field = 'cdfc.id';
763 if (TYPE_DB === 'pgsql') {
764 $cdf_field .= '::text';
765 }
766 foreach ($cdfcs as $cdf) {
767 $rcdf_field = str_replace(
768 'cdfc.',
769 'cdfc' . $cdf . '.',
770 $cdf_field
771 );
772 $select->join(
773 array('cdfc' . $cdf => DynamicField::getFixedValuesTableName($cdf, true)),
774 $rcdf_field . '=dfc.field_val',
775 array(),
776 $select::JOIN_LEFT
777 );
778 }
779 }
780
781 if ($mode == self::SHOW_LIST || $mode == self::SHOW_MANAGED) {
782 if ($this->filters !== false) {
783 $this->buildWhereClause($select);
784 }
785 } elseif ($mode == self::SHOW_PUBLIC_LIST) {
786 $select->where(
787 array(
788 new PredicateSet(
789 array(
790 new Operator(
791 'a.date_echeance',
792 '>=',
793 date('Y-m-d')
794 ),
795 new Operator(
796 'a.bool_exempt_adh',
797 '=',
798 new Expression('true')
799 )
800 ),
801 PredicateSet::OP_OR
802 ),
803 new PredicateSet(
804 array(
805 new Operator(
806 'a.bool_display_info',
807 '=',
808 new Expression('true')
809 ),
810 new Operator(
811 'a.activite_adh',
812 '=',
813 new Expression('true')
814 )
815 ),
816 PredicateSet::OP_AND
817 )
818 )
819 );
820 }
821
822 if ($mode === self::SHOW_STAFF) {
823 $select->where->lessThan(
824 'status.priorite_statut',
825 self::NON_STAFF_MEMBERS
826 );
827 }
828
829 if ($count) {
830 $this->proceedCount($select);
831 }
832
833 $this->buildOrderClause($select, $fields);
834
835 return $select;
836 } catch (Throwable $e) {
837 Analog::log(
838 'Cannot build SELECT clause for members | ' . $e->getMessage(),
839 Analog::WARNING
840 );
841 throw $e;
842 }
843 }
844
845 /**
846 * Count members from the query
847 *
848 * @param Select $select Original select
849 *
850 * @return void
851 */
852 private function proceedCount(Select $select)
853 {
854 global $zdb;
855
856 try {
857 $countSelect = clone $select;
858 $countSelect->reset($countSelect::COLUMNS);
859 $countSelect->reset($countSelect::ORDER);
860 $countSelect->reset($countSelect::HAVING);
861 $joins = $countSelect->joins;
862 $countSelect->reset($countSelect::JOINS);
863 foreach ($joins as $join) {
864 $countSelect->join(
865 $join['name'],
866 $join['on'],
867 [],
868 $join['type']
869 );
870 unset($join['columns']);
871 }
872 $countSelect->columns(
873 array(
874 'count' => new Expression('count(DISTINCT a.' . self::PK . ')')
875 )
876 );
877
878 $have = $select->having;
879 if ($have->count() > 0) {
880 foreach ($have->getPredicates() as $h) {
881 $countSelect->where($h);
882 }
883 }
884
885 $results = $zdb->execute($countSelect);
886
887 $this->count = (int)$results->current()->count;
888 if (isset($this->filters) && $this->count > 0) {
889 $this->filters->setCounter($this->count);
890 }
891 } catch (Throwable $e) {
892 Analog::log(
893 'Cannot count members | ' . $e->getMessage(),
894 Analog::WARNING
895 );
896 throw $e;
897 }
898 }
899
900 /**
901 * Builds the order clause
902 *
903 * @param Select $select Original select
904 * @param array $fields Fields list to ensure ORDER clause
905 * references selected fields. Optional.
906 *
907 * @return Select
908 */
909 private function buildOrderClause(Select $select, $fields = null): Select
910 {
911 $order = array();
912
913 switch ($this->filters->orderby) {
914 case self::ORDERBY_NICKNAME:
915 if ($this->canOrderBy('pseudo_adh', $fields)) {
916 $order[] = 'pseudo_adh ' . $this->filters->getDirection();
917 }
918 break;
919 case self::ORDERBY_STATUS:
920 if ($this->canOrderBy('priorite_statut', $fields)) {
921 $order[] = 'priorite_statut ' . $this->filters->getDirection();
922 }
923 break;
924 case self::ORDERBY_MODIFDATE:
925 if ($this->canOrderBy('date_modif_adh', $fields)) {
926 $order[] = 'date_modif_adh ' . $this->filters->getDirection();
927 }
928 break;
929 case 'list_adh_contribstatus':
930 case self::ORDERBY_FEE_STATUS:
931 if ($this->canOrderBy('bool_exempt_adh', $fields)) {
932 $order[] = 'bool_exempt_adh ' . $this->filters->getDirection();
933 }
934
935 if ($this->canOrderBy('date_echeance', $fields)) {
936 $order[] = 'date_echeance ' . $this->filters->getDirection();
937 }
938 break;
939 case self::ORDERBY_ID:
940 if ($this->canOrderBy('id_adh', $fields)) {
941 $order[] = 'id_adh ' . $this->filters->getDirection();
942 }
943 break;
944 case 'list_adh_name':
945 case 'nom_adh':
946 case 'prenom_adh':
947 case self::ORDERBY_NAME:
948 //defaults
949 break;
950 default:
951 if ($this->canOrderBy($this->filters->orderby, $fields)) {
952 $order[] = 'a.' . $this->filters->orderby . ' ' . $this->filters->getDirection();
953 }
954 break;
955 }
956
957 //anyways, we want to order by firstname, lastname
958 if ($this->canOrderBy('nom_adh', $fields)) {
959 $order[] = 'nom_adh ' . $this->filters->getDirection();
960 }
961 if ($this->canOrderBy('prenom_adh', $fields)) {
962 $order[] = 'prenom_adh ' . $this->filters->getDirection();
963 }
964
965 $select->order($order);
966 return $select;
967 }
968
969 /**
970 * Is field allowed to order? it should be present in
971 * provided fields list (those that are SELECT'ed).
972 *
973 * @param string $field_name Field name to order by
974 * @param array $fields SELECTE'ed fields
975 *
976 * @return boolean
977 */
978 private function canOrderBy($field_name, $fields)
979 {
980 if ($fields === null) {
981 return true;
982 } elseif (!is_array($fields)) {
983 return false;
984 } elseif (in_array($field_name, $fields)) {
985 return true;
986 } else {
987 Analog::log(
988 'Trying to order by ' . $field_name . ' while it is not in ' .
989 'selected fields.',
990 Analog::WARNING
991 );
992 return false;
993 }
994 }
995
996 /**
997 * Builds where clause, for filtering on simple list mode
998 *
999 * @param Select $select Original select
1000 *
1001 * @return void
1002 */
1003 private function buildWhereClause(Select $select)
1004 {
1005 global $zdb, $login;
1006
1007 try {
1008 if ($this->filters->email_filter == self::FILTER_W_EMAIL) {
1009 $select->where('(a.email_adh != \'\' OR a.parent_id IS NOT NULL AND parent.email_adh != \'\')');
1010 }
1011 if ($this->filters->email_filter == self::FILTER_WO_EMAIL) {
1012 $select->where('(a.email_adh = \'\' OR a.email_adh IS NULL) AND (parent.email_adh = \'\' OR parent.email_adh IS NULL)');
1013 }
1014
1015 if ($this->filters->filter_str != '') {
1016 $token = $zdb->platform->quoteValue(
1017 '%' . strtolower($this->filters->filter_str) . '%'
1018 );
1019 switch ($this->filters->field_filter) {
1020 case self::FILTER_NAME:
1021 if (TYPE_DB === 'pgsql') {
1022 $sep = " || ' ' || ";
1023 $pre = '';
1024 $post = '';
1025 } else {
1026 $sep = ', " ", ';
1027 $pre = 'CONCAT(';
1028 $post = ')';
1029 }
1030
1031 $select->where(
1032 '(' .
1033 $pre . 'LOWER(a.nom_adh)' . $sep .
1034 'LOWER(a.prenom_adh)' . $sep .
1035 'LOWER(a.pseudo_adh)' . $post . ' LIKE ' .
1036 $token
1037 . ' OR ' .
1038 $pre . 'LOWER(a.prenom_adh)' . $sep .
1039 'LOWER(a.nom_adh)' . $sep .
1040 'LOWER(a.pseudo_adh)' . $post . ' LIKE ' .
1041 $token
1042 . ')'
1043 );
1044 break;
1045 case self::FILTER_COMPANY_NAME:
1046 $select->where(
1047 'LOWER(a.societe_adh) LIKE ' .
1048 $token
1049 );
1050 break;
1051 case self::FILTER_ADDRESS:
1052 $select->where(
1053 '(' .
1054 'LOWER(a.adresse_adh) LIKE ' . $token
1055 . ' OR ' .
1056 'a.cp_adh LIKE ' . $token
1057 . ' OR ' .
1058 'LOWER(a.ville_adh) LIKE ' . $token
1059 . ' OR ' .
1060 'LOWER(a.pays_adh) LIKE ' . $token
1061 . ')'
1062 );
1063 break;
1064 case self::FILTER_MAIL:
1065 $select->where(
1066 '(' .
1067 'LOWER(a.email_adh) LIKE ' . $token
1068 . ' OR ' .
1069 'LOWER(so.url) LIKE ' . $token
1070 . ')'
1071 );
1072 break;
1073 case self::FILTER_JOB:
1074 $select->where(
1075 'LOWER(a.prof_adh) LIKE ' . $token
1076 );
1077 break;
1078 case self::FILTER_INFOS:
1079 $more = '';
1080 if ($login->isAdmin() || $login->isStaff()) {
1081 $more = ' OR LOWER(a.info_adh) LIKE ' . $token;
1082 }
1083 $select->where(
1084 '(LOWER(a.info_public_adh) LIKE ' .
1085 $token . $more . ')'
1086 );
1087 break;
1088 case self::FILTER_NUMBER:
1089 $select->where->equalTo('a.num_adh', $this->filters->filter_str);
1090 break;
1091 case self::FILTER_ID:
1092 $select->where->equalTo('a.id_adh', $this->filters->filter_str);
1093 break;
1094 }
1095 }
1096
1097 if ($this->filters->membership_filter) {
1098 switch ($this->filters->membership_filter) {
1099 case self::MEMBERSHIP_NEARLY:
1100 $now = new \DateTime();
1101 $due_date = clone $now;
1102 $due_date->modify('+30 days');
1103 $select->where
1104 ->greaterThanOrEqualTo(
1105 'a.date_echeance',
1106 $now->format('Y-m-d')
1107 )->lessThanOrEqualTo(
1108 'a.date_echeance',
1109 $due_date->format('Y-m-d')
1110 )->equalTo('a.bool_exempt_adh', new Expression('false'));
1111 break;
1112 case self::MEMBERSHIP_LATE:
1113 $select->where
1114 ->lessThan(
1115 'a.date_echeance',
1116 date('Y-m-d', time())
1117 )->equalTo('a.bool_exempt_adh', new Expression('false'));
1118 break;
1119 case self::MEMBERSHIP_UP2DATE:
1120 $select->where(
1121 '(' . 'a.date_echeance >= \'' . date('Y-m-d', time())
1122 . '\' OR a.bool_exempt_adh=true)'
1123 );
1124 break;
1125 case self::MEMBERSHIP_NEVER:
1126 $select->where('a.date_echeance IS NULL')
1127 ->where('a.bool_exempt_adh = false');
1128 break;
1129 case self::MEMBERSHIP_STAFF:
1130 $select->where->lessThan(
1131 'status.priorite_statut',
1132 self::NON_STAFF_MEMBERS
1133 );
1134 break;
1135 case self::MEMBERSHIP_ADMIN:
1136 $select->where->equalTo('a.bool_admin_adh', true);
1137 break;
1138 case self::MEMBERSHIP_NONE:
1139 $select->where->equalTo('a.id_statut', Status::DEFAULT_STATUS);
1140 break;
1141 }
1142 }
1143
1144 if ($this->filters->filter_account) {
1145 switch ($this->filters->filter_account) {
1146 case self::ACTIVE_ACCOUNT:
1147 $select->where('a.activite_adh=true');
1148 break;
1149 case self::INACTIVE_ACCOUNT:
1150 $select->where('a.activite_adh=false');
1151 break;
1152 }
1153 }
1154
1155 if ($this->filters->group_filter) {
1156 $select->join(
1157 array('g' => PREFIX_DB . Group::GROUPSUSERS_TABLE),
1158 'a.' . Adherent::PK . '=g.' . Adherent::PK,
1159 array(),
1160 $select::JOIN_LEFT
1161 )->join(
1162 array('gs' => PREFIX_DB . Group::TABLE),
1163 'gs.' . Group::PK . '=g.' . Group::PK,
1164 array(),
1165 $select::JOIN_LEFT
1166 )->where(
1167 '(g.' . Group::PK . ' = ' . $zdb->platform->quoteValue($this->filters->group_filter) .
1168 ' OR gs.parent_group = NULL OR gs.parent_group = ' .
1169 $this->filters->group_filter . ')'
1170 );
1171 }
1172
1173 if ($this->filters instanceof AdvancedMembersList) {
1174 $this->buildAdvancedWhereClause($select);
1175 }
1176 } catch (Throwable $e) {
1177 Analog::log(
1178 __METHOD__ . ' | ' . $e->getMessage(),
1179 Analog::WARNING
1180 );
1181 throw $e;
1182 }
1183 }
1184
1185 /**
1186 * Builds where clause, for advanced filtering on simple list mode
1187 *
1188 * @param Select $select Original select
1189 *
1190 * @return void
1191 */
1192 private function buildAdvancedWhereClause(Select $select)
1193 {
1194 global $zdb;
1195
1196 // Search members who belong to any (OR) or all (AND) listed groups.
1197 // Idea is to build an array of members ID that fits groups selection
1198 // we will use in the final query.
1199 // The OR case is quite simple, AND is a bit more complex; since we must
1200 // check each member do belongs to all listed groups.
1201 if (
1202 count($this->filters->groups_search) > 0
1203 && !isset($this->filters->groups_search['empty'])
1204 ) {
1205 $groups_adh = [];
1206 $wheregroups = [];
1207
1208 foreach ($this->filters->groups_search as $gs) { // then add a row for each group
1209 $wheregroups[] = $gs['group'];
1210 }
1211
1212 $gselect = $zdb->select(Group::GROUPSUSERS_TABLE, 'gu');
1213 $gselect->columns(
1214 array('id_adh')
1215 )->join(
1216 array('g' => PREFIX_DB . Group::TABLE),
1217 'gu.id_group=g.' . Group::PK,
1218 array(),
1219 $select::JOIN_LEFT
1220 )->where(
1221 array(
1222 'g.id_group' => ':group',
1223 'g.parent_group' => ':pgroup'
1224 ),
1225 PredicateSet::OP_OR
1226 );
1227 $gselect->group(['gu.id_adh']);
1228
1229 $stmt = $zdb->sql->prepareStatementForSqlObject($gselect);
1230
1231 $mids = [];
1232 $ids = [];
1233 foreach ($this->filters->groups_search as $gs) { // then add a row for each ig/searched group pair
1234 $gresults = $stmt->execute(
1235 array(
1236 'group' => $gs['group'],
1237 'pgroup' => $gs['group']
1238 )
1239 );
1240
1241 switch ($this->filters->groups_search_log_op) {
1242 case AdvancedMembersList::OP_AND:
1243 foreach ($gresults as $gresult) {
1244 if (!isset($ids[$gresult['id_adh']])) {
1245 $ids[$gresult['id_adh']] = 0;
1246 }
1247 $ids[$gresult['id_adh']] += 1;
1248 }
1249 break;
1250 case AdvancedMembersList::OP_OR:
1251 foreach ($gresults as $gresult) {
1252 $mids[$gresult['id_adh']] = $gresult['id_adh'];
1253 }
1254 break;
1255 }
1256 }
1257
1258 if (count($ids)) {
1259 foreach ($ids as $id_adh => $count) {
1260 if ($count == count($wheregroups)) {
1261 $mids[$id_adh] = $id_adh;
1262 }
1263 }
1264 }
1265
1266 if (count($mids)) {
1267 //limit on found members
1268 $select->where->in('a.id_adh', $mids);
1269 } else {
1270 //no match in groups, end of game.
1271 $select->where('false = true');
1272 }
1273 }
1274
1275 //FIXME: should be retrieved from members_fields
1276 $dates = [
1277 'a.ddn_adh' => 'birth_date',
1278 'a.date_crea_adh' => 'creation_date',
1279 'a.date_modif_adh' => 'modif_date',
1280 'a.date_echeance' => 'due_date',
1281 'ct.date_enreg' => 'contrib_creation_date',
1282 'ct.date_debut_cotis' => 'contrib_begin_date',
1283 'ct.date_fin_cotis' => 'contrib_end_date'
1284 ];
1285
1286 foreach ($dates as $field => $property) {
1287 $bprop = "r{$property}_begin";
1288 if ($this->filters->$bprop) {
1289 $d = new \DateTime($this->filters->$bprop);
1290 $select->where->greaterThanOrEqualTo(
1291 $field,
1292 $d->format('Y-m-d')
1293 );
1294 }
1295 $eprop = "r{$property}_end";
1296 if ($this->filters->$eprop) {
1297 $d = new \DateTime($this->filters->$eprop);
1298 $select->where->lessThanOrEqualTo(
1299 $field,
1300 $d->format('Y-m-d')
1301 );
1302 }
1303 }
1304
1305 if ($this->filters->show_public_infos) {
1306 switch ($this->filters->show_public_infos) {
1307 case self::FILTER_W_PUBINFOS:
1308 $select->where('a.bool_display_info = true');
1309 break;
1310 case self::FILTER_WO_PUBINFOS:
1311 $select->where('a.bool_display_info = false');
1312 break;
1313 case self::FILTER_DC_PUBINFOS:
1314 //nothing to do here.
1315 break;
1316 }
1317 }
1318
1319 if ($this->filters->status) {
1320 $select->where->in(
1321 'a.id_statut',
1322 $this->filters->status
1323 );
1324 }
1325
1326 if (
1327 $this->filters->contrib_min_amount
1328 || $this->filters->contrib_max_amount
1329 ) {
1330 if ($this->filters->contrib_min_amount) {
1331 $select->where->greaterThanOrEqualTo(
1332 'ct.montant_cotis',
1333 $this->filters->contrib_min_amount
1334 );
1335 }
1336 if ($this->filters->contrib_max_amount) {
1337 $select->where->lessThanOrEqualTo(
1338 'ct.montant_cotis',
1339 $this->filters->contrib_max_amount
1340 );
1341 }
1342 }
1343
1344 if ($this->filters->contributions_types) {
1345 $select->where->in(
1346 'ct.id_type_cotis',
1347 $this->filters->contributions_types
1348 );
1349 }
1350
1351 if ($this->filters->payments_types) {
1352 $select->where->in(
1353 'ct.type_paiement_cotis',
1354 $this->filters->payments_types
1355 );
1356 }
1357
1358 if (
1359 count($this->filters->contrib_dynamic) > 0
1360 && !isset($this->filters->contrib_dynamic['empty'])
1361 ) {
1362 foreach ($this->filters->contrib_dynamic as $k => $cd) {
1363 $qry = '';
1364 $prefix = 'a.';
1365 $field = null;
1366 $qop = ' LIKE ';
1367
1368 if (is_array($cd)) {
1369 //dynamic choice spotted!
1370 $prefix = 'cdfc' . $k . '.';
1371 $qry = 'dfc.field_form = \'contrib\' AND ' .
1372 'dfc.field_id = ' . $k;
1373 $field = 'id';
1374 $select->where($qry);
1375 $select->where->in($prefix . $field, $cd);
1376 } else {
1377 //dynamic field spotted!
1378 $prefix = 'dfc.';
1379 $qry = 'dfc.field_form = \'contrib\' AND ' .
1380 'dfc.field_id = ' . $k . ' AND ';
1381 $field = 'field_val';
1382 $qry .= 'LOWER(' . $prefix . $field . ') ' .
1383 $qop . ' ';
1384 $select->where($qry . $zdb->platform->quoteValue('%' . strtolower($cd) . '%'));
1385 }
1386 }
1387 }
1388
1389 if (
1390 count($this->filters->free_search) > 0
1391 && !isset($this->filters->free_search['empty'])
1392 ) {
1393 foreach ($this->filters->free_search as $fs) {
1394 $fs['search'] = mb_strtolower($fs['search']);
1395 $qop = null;
1396 switch ($fs['qry_op']) {
1397 case AdvancedMembersList::OP_EQUALS:
1398 $qop = '=';
1399 break;
1400 case AdvancedMembersList::OP_CONTAINS:
1401 $qop = 'LIKE';
1402 $fs['search'] = '%' . $fs['search'] . '%';
1403 break;
1404 case AdvancedMembersList::OP_NOT_EQUALS:
1405 $qop = '!=';
1406 break;
1407 case AdvancedMembersList::OP_NOT_CONTAINS:
1408 $qop = 'NOT LIKE';
1409 $fs['search'] = '%' . $fs['search'] . '%';
1410 break;
1411 case AdvancedMembersList::OP_STARTS_WITH:
1412 $qop = 'LIKE';
1413 $fs['search'] = $fs['search'] . '%';
1414 break;
1415 case AdvancedMembersList::OP_ENDS_WITH:
1416 $qop = 'LIKE';
1417 $fs['search'] = '%' . $fs['search'];
1418 break;
1419 case AdvancedMembersList::OP_BEFORE:
1420 $qop = '<';
1421 break;
1422 case AdvancedMembersList::OP_AFTER:
1423 $qop = '>';
1424 break;
1425 default:
1426 Analog::log(
1427 'Unknown query operator: ' . $fs['qry_op'] .
1428 ' (will fallback to equals)',
1429 Analog::WARNING
1430 );
1431 $qop = '=';
1432 break;
1433 }
1434
1435 $qry = '';
1436 $prefix = 'a.';
1437 $dyn_field = false;
1438 if (strpos($fs['field'], 'dyn_') === 0) {
1439 // simple dynamic field spotted!
1440 $index = str_replace('dyn_', '', $fs['field']);
1441 $dyn_field = DynamicField::loadFieldType($zdb, (int)$index);
1442 $prefix = 'df' . $index . '.';
1443 $fs['field'] = 'val';
1444 }
1445
1446 //handle socials networks
1447 if (strpos($fs['field'], 'socials_') === 0) {
1448 //social networks
1449 $type = str_replace('socials_', '', $fs['field']);
1450 $prefix = 'so.';
1451 $fs['field'] = 'url';
1452 $select->where(['so.type' => $type]);
1453 }
1454
1455 if ($dyn_field && $dyn_field instanceof \Galette\DynamicFields\Boolean) {
1456 if ($fs['search'] != 0) {
1457 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1458 $fs['search'];
1459 } else {
1460 $qry .= $prefix . $fs['field'] . ' IS NULL';
1461 }
1462 } elseif (!strncmp($fs['field'], 'bool_', strlen('bool_'))) {
1463 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1464 $fs['search'];
1465 } elseif (
1466 $fs['qry_op'] === AdvancedMembersList::OP_BEFORE
1467 || $fs['qry_op'] === AdvancedMembersList::OP_AFTER
1468 ) {
1469 if ($prefix === 'a.') {
1470 //dates are OK in the main fields. no cast, just query!
1471 $qry .= $prefix . $fs['field'] . $qop . ' ' .
1472 $zdb->platform->quoteValue($fs['search']);
1473 } else {
1474 //dynamic dates are stored in their localized format :/
1475 //use current lang format to query for now
1476 if ($zdb->isPostgres()) {
1477 $fs['search'] = "to_date('" . $fs['search'] . "', 'YYYY-MM-DD')";
1478 $store_fmt = __("Y-m-d") === 'Y-m-d' ? 'YYYY-MM-DD' : 'DD/MM/YYYY';
1479 $qry .= "to_date('" . $prefix . $fs['field'] . "', '$store_fmt')";
1480 } else {
1481 $fs['search'] = "STR_TO_DATE('" . $fs['search'] . "', '%Y-%m-%d')";
1482 $store_fmt = __("Y-m-d") === 'Y-m-d' ? '%Y-%m-%d' : '%d/%m/%Y';
1483 $qry .= 'STR_TO_DATE(' . $prefix . $fs['field'] . ', \'' . $store_fmt . '\') ';
1484 }
1485
1486 $qry .= $qop . ' ' . $fs['search'];
1487 }
1488 } elseif ($fs['field'] == 'status_label') {
1489 $qry_pattern = '%p%field %op %value';
1490 $qry .= str_replace(
1491 [
1492 '%p',
1493 '%field',
1494 '%op',
1495 '%value'
1496 ],
1497 [
1498 'status.',
1499 'libelle_statut',
1500 $qop,
1501 $zdb->platform->quoteValue($fs['search'])
1502 ],
1503 $qry_pattern
1504 );
1505 } else {
1506 $field = $prefix . $fs['field'];
1507 if ($zdb->isPostgres()) {
1508 $field = 'CAST(' . $field . ' AS TEXT)';
1509 }
1510 $qry .= 'LOWER(' . $field . ') ' .
1511 $qop . ' ' . $zdb->platform->quoteValue($fs['search']);
1512 }
1513
1514 if ($fs['log_op'] === AdvancedMembersList::OP_AND) {
1515 $select->where($qry);
1516 } elseif ($fs['log_op'] === AdvancedMembersList::OP_OR) {
1517 $select->where($qry, PredicateSet::OP_OR);
1518 }
1519 }
1520 }
1521 }
1522
1523 /**
1524 * Login and password field cannot be empty.
1525 *
1526 * If those are not required, or if a file has been imported
1527 * (from a CSV file for example), we fill here random values.
1528 *
1529 * @return boolean
1530 */
1531 public function emptyLogins()
1532 {
1533 global $zdb;
1534
1535 try {
1536 $zdb->connection->beginTransaction();
1537 $select = $zdb->select(Adherent::TABLE);
1538 $select->columns(
1539 array('id_adh', 'login_adh', 'mdp_adh')
1540 )->where(
1541 new PredicateSet(
1542 array(
1543 new Operator(
1544 'login_adh',
1545 '=',
1546 ''
1547 ),
1548 new IsNull('login_adh'),
1549 new Operator(
1550 'mdp_adh',
1551 '=',
1552 ''
1553 ),
1554 new IsNull('mdp_adh'),
1555 ),
1556 PredicateSet::OP_OR
1557 )
1558 );
1559
1560 $results = $zdb->execute($select);
1561
1562 $processed = 0;
1563 if ($results->count() > 0) {
1564 $update = $zdb->update(Adherent::TABLE);
1565 $update->set(
1566 array(
1567 'login_adh' => ':login',
1568 'mdp_adh' => ':pass'
1569 )
1570 )->where->equalTo(Adherent::PK, ':id');
1571
1572 $stmt = $zdb->sql->prepareStatementForSqlObject($update);
1573
1574 $p = new \Galette\Core\Password($zdb);
1575
1576 foreach ($results as $m) {
1577 $dirty = false;
1578 if (
1579 $m->login_adh == ''
1580 || !isset($m->login_adh)
1581 || $m->login_adh == 'NULL'
1582 ) {
1583 $m->login_adh = $p->makeRandomPassword(15);
1584 $dirty = true;
1585 }
1586
1587 if (
1588 $m->mdp_adh == ''
1589 || !isset($m->mdp_adh)
1590 || $m->mdp_adh == 'NULL'
1591 ) {
1592 $randomp = $p->makeRandomPassword(15);
1593 $m->mdp_adh = password_hash(
1594 $randomp,
1595 PASSWORD_BCRYPT
1596 );
1597 $dirty = true;
1598 }
1599
1600 if ($dirty === true) {
1601 $stmt->execute(
1602 array(
1603 'login' => $m->login_adh,
1604 'pass' => $m->mdp_adh,
1605 'id' => $m->id_adh
1606 )
1607 );
1608 $processed++;
1609 }
1610 }
1611 }
1612 $zdb->connection->commit();
1613 $this->count = $processed;
1614 return true;
1615 } catch (Throwable $e) {
1616 $zdb->connection->rollBack();
1617 Analog::log(
1618 'An error occurred trying to retrieve members with ' .
1619 'empty logins/passwords (' . $e->getMessage(),
1620 Analog::ERROR
1621 );
1622 throw $e;
1623 }
1624 }
1625
1626 /**
1627 * Get reminders count based on members state of dues
1628 *
1629 * @return array
1630 */
1631 public function getRemindersCount()
1632 {
1633 global $zdb;
1634
1635 $reminders = array();
1636
1637 // Count close to be expired reminders
1638 $select = $zdb->select(Adherent::TABLE, 'a');
1639 $select->columns(
1640 array(
1641 'cnt' => new Expression('count(a.' . Adherent::PK . ')')
1642 )
1643 );
1644
1645 $select->join(
1646 array('parent' => PREFIX_DB . self::TABLE),
1647 'a.parent_id=parent.' . self::PK,
1648 array(),
1649 $select::JOIN_LEFT
1650 );
1651
1652 $select
1653 ->where('a.activite_adh=true')
1654 ->where('a.bool_exempt_adh=false');
1655
1656 $now = new \DateTime();
1657 $due_date = clone $now;
1658 $due_date->modify('+30 days');
1659
1660 $select->where
1661 ->greaterThanOrEqualTo('a.date_echeance', $now->format('Y-m-d'))
1662 ->lessThanOrEqualTo('a.date_echeance', $due_date->format('Y-m-d'));
1663
1664 $select_wo_mail = clone $select;
1665 //per default, limit to members who have an email address
1666 $select->where(
1667 '(a.email_adh != \'\' OR a.parent_id IS NOT NULL AND parent.email_adh != \'\')'
1668 );
1669 $select_wo_mail->where(
1670 '(a.email_adh = \'\' OR a.email_adh IS NULL) AND (parent.email_adh = \'\' OR parent.email_adh IS NULL)'
1671 );
1672
1673 $results = $zdb->execute($select);
1674 $res = $results->current();
1675 $reminders['impending'] = $res->cnt;
1676
1677 $results_wo_mail = $zdb->execute($select_wo_mail);
1678 $res_wo_mail = $results_wo_mail->current();
1679 $reminders['nomail']['impending'] = $res_wo_mail->cnt;
1680
1681 // Count late reminders
1682 $select = $zdb->select(Adherent::TABLE, 'a');
1683 $select->columns(
1684 array(
1685 'cnt' => new Expression('count(a.' . Adherent::PK . ')')
1686 )
1687 );
1688
1689 $select->join(
1690 array('parent' => PREFIX_DB . self::TABLE),
1691 'a.parent_id=parent.' . self::PK,
1692 array(),
1693 $select::JOIN_LEFT
1694 );
1695
1696 $select
1697 ->where('a.activite_adh=true')
1698 ->where('a.bool_exempt_adh=false');
1699
1700 $select->where
1701 ->lessThan('a.date_echeance', $now->format('Y-m-d'));
1702
1703 $select_wo_mail = clone $select;
1704 //per default, limit to members who have an email address
1705 $select->where(
1706 '(a.email_adh != \'\' OR a.parent_id IS NOT NULL AND parent.email_adh != \'\')'
1707 );
1708 $select_wo_mail->where(
1709 '(a.email_adh = \'\' OR a.email_adh IS NULL) AND (parent.email_adh = \'\' OR parent.email_adh IS NULL)'
1710 );
1711
1712 $results = $zdb->execute($select);
1713 $res = $results->current();
1714 $reminders['late'] = $res->cnt;
1715
1716 $results_wo_mail = $zdb->execute($select_wo_mail);
1717 $res_wo_mail = $results_wo_mail->current();
1718 $reminders['nomail']['late'] = $res_wo_mail->cnt;
1719
1720 return $reminders;
1721 }
1722
1723 /**
1724 * Get count for current query
1725 *
1726 * @return int
1727 */
1728 public function getCount()
1729 {
1730 return $this->count;
1731 }
1732
1733 /**
1734 * Get registered errors
1735 *
1736 * @return array
1737 */
1738 public function getErrors()
1739 {
1740 return $this->errors;
1741 }
1742
1743 /**
1744 * Get all existing emails
1745 *
1746 * @param Db $zdb Database instance
1747 *
1748 * @return array ['email' => 'id_adh']
1749 */
1750 public static function getEmails(Db $zdb)
1751 {
1752 $emails = [];
1753 $select = $zdb->select(self::TABLE);
1754 $select->columns([
1755 self::PK,
1756 'email_adh'
1757 ]);
1758 $select->where('email_adh != \'\' AND email_adh IS NOT NULL');
1759 $rows = $zdb->execute($select);
1760 foreach ($rows as $row) {
1761 $emails[$row->email_adh] = $row->{self::PK};
1762 }
1763 return $emails;
1764 }
1765
1766 /**
1767 * Get current filters
1768 *
1769 * @return MembersList
1770 */
1771 public function getFilters()
1772 {
1773 return $this->filters;
1774 }
1775
1776 /**
1777 * Get members list to instanciate dropdowns
1778 *
1779 * @param Db $zdb Database instance
1780 * @param Login $login Login instance
1781 * @param integer $current Current member
1782 *
1783 * @return array
1784 */
1785 public function getDropdownMembers(Db $zdb, Login $login, $current = null)
1786 {
1787 $members = [];
1788 $required_fields = array(
1789 'id_adh',
1790 'nom_adh',
1791 'prenom_adh',
1792 'pseudo_adh'
1793 );
1794
1795 $list_members = [];
1796 if ($login->isAdmin() || $login->isStaff()) {
1797 $list_members = $this->getList(false, $required_fields);
1798 } elseif ($login->isGroupManager()) {
1799 $list_members = $this->getManagedMembersList(false, $required_fields);
1800 }
1801
1802 if (count($list_members) > 0) {
1803 foreach ($list_members as $member) {
1804 $pk = Adherent::PK;
1805
1806 $members[$member->$pk] = Adherent::getNameWithCase(
1807 $member->nom_adh,
1808 $member->prenom_adh,
1809 false,
1810 $member->id_adh,
1811 $member->pseudo_adh
1812 );
1813 }
1814 }
1815
1816 //check if current attached member is part of the list
1817 if ($current !== null && !isset($members[$current])) {
1818 $members =
1819 [$current => Adherent::getSName($zdb, $current, true, true)] +
1820 $members
1821 ;
1822 }
1823
1824 return $members;
1825 }
1826 }
Galette membership management web application towards non profit (personal repository) | https://galette.eu