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


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