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