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


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