From 0c2baa8b651e66bf864949cf28f2fedec5cb5859 Mon Sep 17 00:00:00 2001 From: Johan Cwiklinski Date: Mon, 15 Jun 2020 13:20:52 +0200 Subject: [PATCH] Handle advanced filters in their own method, factorize date search Also fix few issues on fixtures --- galette/lib/Galette/Repository/Members.php | 802 ++++++++---------- .../Repository/tests/units/Members.php | 36 + tests/fixtures/tests_members.json | 40 +- 3 files changed, 404 insertions(+), 474 deletions(-) diff --git a/galette/lib/Galette/Repository/Members.php b/galette/lib/Galette/Repository/Members.php index 16e6e82a8..84a65805b 100644 --- a/galette/lib/Galette/Repository/Members.php +++ b/galette/lib/Galette/Repository/Members.php @@ -70,51 +70,51 @@ use Galette\Core\Db; */ class Members { - const TABLE = Adherent::TABLE; - const PK = Adherent::PK; - - const ALL_ACCOUNTS = 0; - const ACTIVE_ACCOUNT = 1; - const INACTIVE_ACCOUNT = 2; - - const SHOW_LIST = 0; - const SHOW_PUBLIC_LIST = 1; - const SHOW_ARRAY_LIST = 2; - const SHOW_STAFF = 3; - const SHOW_MANAGED = 4; - const SHOW_EXPORT = 5; - - const FILTER_NAME = 0; - const FILTER_ADDRESS = 1; - const FILTER_MAIL = 2; - const FILTER_JOB = 3; - const FILTER_INFOS = 4; - const FILTER_DC_EMAIL = 5; - const FILTER_W_EMAIL = 6; - const FILTER_WO_EMAIL = 7; - const FILTER_COMPANY_NAME = 8; - const FILTER_DC_PUBINFOS = 9; - const FILTER_W_PUBINFOS = 10; - const FILTER_WO_PUBINFOS = 11; - const FILTER_NUMBER = 12; - - const MEMBERSHIP_ALL = 0; - const MEMBERSHIP_UP2DATE = 3; - const MEMBERSHIP_NEARLY = 1; - const MEMBERSHIP_LATE = 2; - const MEMBERSHIP_NEVER = 4; - const MEMBERSHIP_STAFF = 5; - const MEMBERSHIP_ADMIN = 6; - const MEMBERSHIP_NONE = 7; - - const ORDERBY_NAME = 0; - const ORDERBY_NICKNAME = 1; - const ORDERBY_STATUS = 2; - const ORDERBY_FEE_STATUS = 3; - const ORDERBY_MODIFDATE = 4; - const ORDERBY_ID = 5; - - const NON_STAFF_MEMBERS = 30; + public const TABLE = Adherent::TABLE; + public const PK = Adherent::PK; + + public const ALL_ACCOUNTS = 0; + public const ACTIVE_ACCOUNT = 1; + public const INACTIVE_ACCOUNT = 2; + + public const SHOW_LIST = 0; + public const SHOW_PUBLIC_LIST = 1; + public const SHOW_ARRAY_LIST = 2; + public const SHOW_STAFF = 3; + public const SHOW_MANAGED = 4; + public const SHOW_EXPORT = 5; + + public const FILTER_NAME = 0; + public const FILTER_ADDRESS = 1; + public const FILTER_MAIL = 2; + public const FILTER_JOB = 3; + public const FILTER_INFOS = 4; + public const FILTER_DC_EMAIL = 5; + public const FILTER_W_EMAIL = 6; + public const FILTER_WO_EMAIL = 7; + public const FILTER_COMPANY_NAME = 8; + public const FILTER_DC_PUBINFOS = 9; + public const FILTER_W_PUBINFOS = 10; + public const FILTER_WO_PUBINFOS = 11; + public const FILTER_NUMBER = 12; + + public const MEMBERSHIP_ALL = 0; + public const MEMBERSHIP_UP2DATE = 3; + public const MEMBERSHIP_NEARLY = 1; + public const MEMBERSHIP_LATE = 2; + public const MEMBERSHIP_NEVER = 4; + public const MEMBERSHIP_STAFF = 5; + public const MEMBERSHIP_ADMIN = 6; + public const MEMBERSHIP_NONE = 7; + + public const ORDERBY_NAME = 0; + public const ORDERBY_NICKNAME = 1; + public const ORDERBY_STATUS = 2; + public const ORDERBY_FEE_STATUS = 3; + public const ORDERBY_MODIFDATE = 4; + public const ORDERBY_ID = 5; + + public const NON_STAFF_MEMBERS = 30; private $filters = false; private $count = null; @@ -1018,7 +1018,7 @@ class Members $pre = 'CONCAT('; $post = ')'; } - //$sep = ( TYPE_DB === 'pgsql' ) ? " || ' ' || " : ', " ", '; + $select->where( '(' . $pre . 'LOWER(nom_adh)' . $sep . @@ -1166,450 +1166,344 @@ class Members } if ($this->filters instanceof AdvancedMembersList) { - // Search members who belong to any (OR) or all (AND) listed groups. - // Idea is to build an array of members ID that fits groups selection - // we will use in the final query. - // The OR case is quite simple, AND is a bit more complex; since we must - // check each member do belongs to all listed groups. - if ( - count($this->filters->groups_search) > 0 - && !isset($this->filters->groups_search['empty']) - ) { - $groups_adh = []; - $wheregroups = []; + $this->buildAdvancedWhereClause($select); + } - foreach ($this->filters->groups_search as $gs) { // then add a row for each group - $wheregroups[] = $gs['group']; - } + return $select; + } catch (\Exception $e) { + Analog::log( + __METHOD__ . ' | ' . $e->getMessage(), + Analog::WARNING + ); + throw $e; + } + } - $gselect = $zdb->select(Group::GROUPSUSERS_TABLE, 'gu'); - $gselect->columns( - array('id_adh') - )->join( - array('g' => PREFIX_DB . Group::TABLE), - 'gu.id_group=g.' . Group::PK, - array(), - $select::JOIN_LEFT - )->where( - array( - 'g.id_group' => ':group', - 'g.parent_group' => ':pgroup' - ), - PredicateSet::OP_OR - ); - $gselect->group(['gu.id_adh']); + /** + * Builds where clause, for advanced filtering on simple list mode + * + * @param Select $select Original select + * + * @return void + */ + private function buildAdvancedWhereClause(Select $select) + { + global $zdb, $login; - $stmt = $zdb->sql->prepareStatementForSqlObject($gselect); + // Search members who belong to any (OR) or all (AND) listed groups. + // Idea is to build an array of members ID that fits groups selection + // we will use in the final query. + // The OR case is quite simple, AND is a bit more complex; since we must + // check each member do belongs to all listed groups. + if ( + count($this->filters->groups_search) > 0 + && !isset($this->filters->groups_search['empty']) + ) { + $groups_adh = []; + $wheregroups = []; + + foreach ($this->filters->groups_search as $gs) { // then add a row for each group + $wheregroups[] = $gs['group']; + } - $mids = []; - $ids = []; - foreach ($this->filters->groups_search as $gs) { // then add a row for each ig/searched group pair - /** Why where parameter is named where1 ?? */ - $gresults = $stmt->execute( - array( - 'where1' => $gs['group'], - 'where2' => $gs['group'] - ) - ); + $gselect = $zdb->select(Group::GROUPSUSERS_TABLE, 'gu'); + $gselect->columns( + array('id_adh') + )->join( + array('g' => PREFIX_DB . Group::TABLE), + 'gu.id_group=g.' . Group::PK, + array(), + $select::JOIN_LEFT + )->where( + array( + 'g.id_group' => ':group', + 'g.parent_group' => ':pgroup' + ), + PredicateSet::OP_OR + ); + $gselect->group(['gu.id_adh']); - switch ($this->filters->groups_search_log_op) { - case AdvancedMembersList::OP_AND: - foreach ($gresults as $gresult) { - if (!isset($ids[$gresult['id_adh']])) { - $ids[$gresult['id_adh']] = 0; - } - $ids[$gresult['id_adh']] += 1; - } - break; - case AdvancedMembersList::OP_OR: - foreach ($gresults as $gresult) { - $mids[$gresult['id_adh']] = $gresult['id_adh']; - } - break; - } - } + $stmt = $zdb->sql->prepareStatementForSqlObject($gselect); - if (count($ids)) { - foreach ($ids as $id_adh => $count) { - if ($count == count($wheregroups)) { - $mids[$id_adh] = $id_adh; + $mids = []; + $ids = []; + foreach ($this->filters->groups_search as $gs) { // then add a row for each ig/searched group pair + /** Why where parameter is named where1 ?? */ + $gresults = $stmt->execute( + array( + 'where1' => $gs['group'], + 'where2' => $gs['group'] + ) + ); + + switch ($this->filters->groups_search_log_op) { + case AdvancedMembersList::OP_AND: + foreach ($gresults as $gresult) { + if (!isset($ids[$gresult['id_adh']])) { + $ids[$gresult['id_adh']] = 0; } + $ids[$gresult['id_adh']] += 1; } - } - - if (count($mids)) { - //limit on found members - $select->where->in('a.id_adh', $mids); - } else { - //no match in groups, end of game. - $select->where('false = true'); - } + break; + case AdvancedMembersList::OP_OR: + foreach ($gresults as $gresult) { + $mids[$gresult['id_adh']] = $gresult['id_adh']; + } + break; } + } - if ( - $this->filters->rbirth_date_begin - || $this->filters->rbirth_date_end - ) { - if ($this->filters->rbirth_date_begin) { - $d = new \DateTime($this->filters->rbirth_date_begin); - $select->where->greaterThanOrEqualTo( - 'ddn_adh', - $d->format('Y-m-d') - ); - } - if ($this->filters->rbirth_date_end) { - $d = new \DateTime($this->filters->rbirth_date_end); - $select->where->lessThanOrEqualTo( - 'ddn_adh', - $d->format('Y-m-d') - ); + if (count($ids)) { + foreach ($ids as $id_adh => $count) { + if ($count == count($wheregroups)) { + $mids[$id_adh] = $id_adh; } } + } - if ( - $this->filters->rcreation_date_begin - || $this->filters->rcreation_date_end - ) { - if ($this->filters->rcreation_date_begin) { - $d = new \DateTime($this->filters->rcreation_date_begin); - $select->where->greaterThanOrEqualTo( - 'date_crea_adh', - $d->format('Y-m-d') - ); - } - if ($this->filters->rcreation_date_end) { - $d = new \DateTime($this->filters->rcreation_date_end); - $select->where->lessThanOrEqualTo( - 'date_crea_adh', - $d->format('Y-m-d') - ); - } - } + if (count($mids)) { + //limit on found members + $select->where->in('a.id_adh', $mids); + } else { + //no match in groups, end of game. + $select->where('false = true'); + } + } - if ( - $this->filters->rmodif_date_begin - || $this->filters->rmodif_date_end - ) { - if ($this->filters->rmodif_date_begin) { - $d = new \DateTime($this->filters->rmodif_date_begin); - $select->where->greaterThanOrEqualTo( - 'date_modif_adh', - $d->format('Y-m-d') - ); - } - if ($this->filters->rmodif_date_end) { - $d = new \DateTime($this->filters->rmodif_date_end); - $select->where->lessThanOrEqualTo( - 'date_modif_adh', - $d->format('Y-m-d') - ); - } - } + //shoudl be retrieved from members_fields + $dates = [ + 'ddn_adh' => 'birth_date', + 'date_crea_adh' => 'creation_date', + 'date_modif_adh' => 'modif_date', + 'date_echeance' => 'due_date', + 'ct.date_enreg' => 'contrib_creation_date', + 'ct.date_debut_cotis' => 'contrib_begin_date', + 'ct.date_fin_cotis' => 'contrib_end_date' + ]; + + foreach ($dates as $field => $property) { + $bprop = "r{$property}_begin"; + if ($this->filters->$bprop) { + $d = new \DateTime($this->filters->$bprop); + $select->where->greaterThanOrEqualTo( + $field, + $d->format('Y-m-d') + ); + } + $eprop = "r{$property}_end"; + if ($this->filters->$eprop) { + $d = new \DateTime($this->filters->$eprop); + $select->where->lessThanOrEqualTo( + $field, + $d->format('Y-m-d') + ); + } + } - if ( - $this->filters->rdue_date_begin - || $this->filters->rdue_date_end - ) { - if ($this->filters->rdue_date_begin) { - $d = new \DateTime($this->filters->rdue_date_begin); - $select->where->greaterThanOrEqualTo( - 'date_echeance', - $d->format('Y-m-d') - ); - } - if ($this->filters->rdue_date_end) { - $d = new \DateTime($this->filters->rdue_date_end); - $select->where->lessThanOrEqualTo( - 'date_echeance', - $d->format('Y-m-d') - ); - } - } + if ($this->filters->show_public_infos) { + switch ($this->filters->show_public_infos) { + case self::FILTER_W_PUBINFOS: + $select->where('bool_display_info = true'); + break; + case self::FILTER_WO_PUBINFOS: + $select->where('bool_display_info = false'); + break; + case self::FILTER_DC_PUBINFOS: + //nothing to do here. + break; + } + } - if ($this->filters->show_public_infos) { - switch ($this->filters->show_public_infos) { - case self::FILTER_W_PUBINFOS: - $select->where('bool_display_info = true'); - break; - case self::FILTER_WO_PUBINFOS: - $select->where('bool_display_info = false'); - break; - case self::FILTER_DC_PUBINFOS: - //nothing to do here. - break; - } - } + if ($this->filters->status) { + $select->where->in( + 'a.id_statut', + $this->filters->status + ); + } - if ($this->filters->status) { - $select->where->in( - 'a.id_statut', - $this->filters->status - ); - } + if ( + $this->filters->contrib_min_amount + || $this->filters->contrib_max_amount + ) { + if ($this->filters->contrib_min_amount) { + $select->where->greaterThanOrEqualTo( + 'ct.montant_cotis', + $this->filters->contrib_min_amount + ); + } + if ($this->filters->contrib_max_amount) { + $select->where->lessThanOrEqualTo( + 'ct.montant_cotis', + $this->filters->contrib_max_amount + ); + } + } - if ( - $this->filters->rcontrib_creation_date_begin - || $this->filters->rcontrib_creation_date_end - ) { - if ($this->filters->rcontrib_creation_date_begin) { - $d = new \DateTime( - $this->filters->rcontrib_creation_date_begin - ); - $select->where->greaterThanOrEqualTo( - 'ct.date_enreg', - $d->format('Y-m-d') - ); - } - if ($this->filters->rcontrib_creation_date_end) { - $d = new \DateTime( - $this->filters->rcontrib_creation_date_end - ); - $select->where->lessThanOrEqualTo( - 'ct.date_enreg', - $d->format('Y-m-d') - ); - } - } + if ($this->filters->contributions_types) { + $select->where->in( + 'ct.id_type_cotis', + $this->filters->contributions_types + ); + } - if ( - $this->filters->rcontrib_begin_date_begin - || $this->filters->rcontrib_begin_date_end - ) { - if ($this->filters->rcontrib_begin_date_begin) { - $d = new \DateTime( - $this->filters->rcontrib_begin_date_begin - ); - $select->where->greaterThanOrEqualTo( - 'ct.date_debut_cotis', - $d->format('Y-m-d') - ); - } - if ($this->filters->rcontrib_begin_date_end) { - $d = new \DateTime( - $this->filters->rcontrib_begin_date_end - ); - $select->where->lessThanOrEqualTo( - 'ct.date_debut_cotis', - $d->format('Y-m-d') - ); - } - } + if ($this->filters->payments_types) { + $select->where->in( + 'ct.type_paiement_cotis', + $this->filters->payments_types + ); + } - if ( - $this->filters->rcontrib_end_date_begin - || $this->filters->rcontrib_end_date_end - ) { - if ($this->filters->rcontrib_end_date_begin) { - $d = new \DateTime( - $this->filters->rcontrib_end_date_begin - ); - $select->where->greaterThanOrEqualTo( - 'ct.date_fin_cotis', - $d->format('Y-m-d') - ); - } - if ($this->filters->rcontrib_end_date_end) { - $d = new \DateTime( - $this->filters->rcontrib_end_date_end - ); - $select->where->lessThanOrEqualTo( - 'ct.date_fin_cotis', - $d->format('Y-m-d') - ); - } + if ( + count($this->filters->contrib_dynamic) > 0 + && !isset($this->filters->contrib_dynamic['empty']) + ) { + foreach ($this->filters->contrib_dynamic as $k => $cd) { + $qry = ''; + $prefix = 'a.'; + $field = null; + $qop = ' LIKE '; + + if (is_array($cd)) { + //dynamic choice spotted! + $prefix = 'cdfc' . $k . '.'; + $qry = 'dfc.field_form = \'contrib\' AND ' . + 'dfc.field_id = ' . $k . ' AND '; + $field = 'id'; + $select->where->in($prefix . $field, $cd); + } else { + //dynamic field spotted! + $prefix = 'dfc.'; + $qry = 'dfc.field_form = \'contrib\' AND ' . + 'dfc.field_id = ' . $k . ' AND '; + $field = 'field_val'; + $qry .= 'LOWER(' . $prefix . $field . ') ' . + $qop . ' '; + $select->where($qry . $zdb->platform->quoteValue('%' . strtolower($cd) . '%')); } + } + } - if ( - $this->filters->contrib_min_amount - || $this->filters->contrib_max_amount - ) { - if ($this->filters->contrib_min_amount) { - $select->where->greaterThanOrEqualTo( - 'ct.montant_cotis', - $this->filters->contrib_min_amount - ); - } - if ($this->filters->contrib_max_amount) { - $select->where->lessThanOrEqualTo( - 'ct.montant_cotis', - $this->filters->contrib_max_amount + if ( + count($this->filters->free_search) > 0 + && !isset($this->filters->free_search['empty']) + ) { + foreach ($this->filters->free_search as $fs) { + $fs['search'] = mb_strtolower($fs['search']); + $qop = null; + switch ($fs['qry_op']) { + case AdvancedMembersList::OP_EQUALS: + $qop = '='; + break; + case AdvancedMembersList::OP_CONTAINS: + $qop = 'LIKE'; + $fs['search'] = '%' . $fs['search'] . '%'; + break; + case AdvancedMembersList::OP_NOT_EQUALS: + $qop = '!='; + break; + case AdvancedMembersList::OP_NOT_CONTAINS: + $qop = 'NOT LIKE'; + $fs['search'] = '%' . $fs['search'] . '%'; + break; + case AdvancedMembersList::OP_STARTS_WITH: + $qop = 'LIKE'; + $fs['search'] = $fs['search'] . '%'; + break; + case AdvancedMembersList::OP_ENDS_WITH: + $qop = 'LIKE'; + $fs['search'] = '%' . $fs['search']; + break; + case AdvancedMembersList::OP_BEFORE: + $qop = '<'; + break; + case AdvancedMembersList::OP_AFTER: + $qop = '>'; + break; + default: + Analog::log( + 'Unknown query operator: ' . $fs['qry_op'] . + ' (will fallback to equals)', + Analog::WARNING ); - } - } - - if ($this->filters->contributions_types) { - $select->where->in( - 'ct.id_type_cotis', - $this->filters->contributions_types - ); + $qop = '='; + break; } - if ($this->filters->payments_types) { - $select->where->in( - 'ct.type_paiement_cotis', - $this->filters->payments_types - ); + $qry = ''; + $prefix = 'a.'; + $dyn_field = false; + if (strpos($fs['field'], 'dyn_') === 0) { + // simple dynamic field spotted! + $index = str_replace('dyn_', '', $fs['field']); + $dyn_field = DynamicField::loadFieldType($zdb, (int)$index); + $prefix = 'df' . $index . '.'; + $fs['field'] = 'val'; } - if ( - count($this->filters->contrib_dynamic) > 0 - && !isset($this->filters->contrib_dynamic['empty']) - ) { - foreach ($this->filters->contrib_dynamic as $k => $cd) { - $qry = ''; - $prefix = 'a.'; - $field = null; - $qop = ' LIKE '; - - if (is_array($cd)) { - //dynamic choice spotted! - $prefix = 'cdfc' . $k . '.'; - $qry = 'dfc.field_form = \'contrib\' AND ' . - 'dfc.field_id = ' . $k . ' AND '; - $field = 'id'; - $select->where->in($prefix . $field, $cd); - } else { - //dynamic field spotted! - $prefix = 'dfc.'; - $qry = 'dfc.field_form = \'contrib\' AND ' . - 'dfc.field_id = ' . $k . ' AND '; - $field = 'field_val'; - $qry .= 'LOWER(' . $prefix . $field . ') ' . - $qop . ' '; - $select->where($qry . $zdb->platform->quoteValue('%' . strtolower($cd) . '%')); - } + if ($dyn_field && $dyn_field instanceof \Galette\DynamicFields\Boolean) { + if ($fs['search'] != 0) { + $qry .= $prefix . $fs['field'] . $qop . ' ' . + $fs['search']; + } else { + $qry .= $prefix . $fs['field'] . ' IS NULL'; } - } - - if ( - count($this->filters->free_search) > 0 - && !isset($this->filters->free_search['empty']) + } elseif (!strncmp($fs['field'], 'bool_', strlen('bool_'))) { + $qry .= $prefix . $fs['field'] . $qop . ' ' . + $fs['search']; + } elseif ( + $fs['qry_op'] === AdvancedMembersList::OP_BEFORE + || $fs['qry_op'] === AdvancedMembersList::OP_AFTER ) { - foreach ($this->filters->free_search as $fs) { - $fs['search'] = mb_strtolower($fs['search']); - $qop = null; - switch ($fs['qry_op']) { - case AdvancedMembersList::OP_EQUALS: - $qop = '='; - break; - case AdvancedMembersList::OP_CONTAINS: - $qop = 'LIKE'; - $fs['search'] = '%' . $fs['search'] . '%'; - break; - case AdvancedMembersList::OP_NOT_EQUALS: - $qop = '!='; - break; - case AdvancedMembersList::OP_NOT_CONTAINS: - $qop = 'NOT LIKE'; - $fs['search'] = '%' . $fs['search'] . '%'; - break; - case AdvancedMembersList::OP_STARTS_WITH: - $qop = 'LIKE'; - $fs['search'] = $fs['search'] . '%'; - break; - case AdvancedMembersList::OP_ENDS_WITH: - $qop = 'LIKE'; - $fs['search'] = '%' . $fs['search']; - break; - case AdvancedMembersList::OP_BEFORE: - $qop = '<'; - break; - case AdvancedMembersList::OP_AFTER: - $qop = '>'; - break; - default: - Analog::log( - 'Unknown query operator: ' . $fs['qry_op'] . - ' (will fallback to equals)', - Analog::WARNING - ); - $qop = '='; - break; - } - - $qry = ''; - $prefix = 'a.'; - $dyn_field = false; - if (strpos($fs['field'], 'dyn_') === 0) { - // simple dynamic field spotted! - $index = str_replace('dyn_', '', $fs['field']); - $dyn_field = DynamicField::loadFieldType($zdb, (int)$index); - $prefix = 'df' . $index . '.'; - $fs['field'] = 'val'; - } - - if ($dyn_field && $dyn_field instanceof \Galette\DynamicFields\Boolean) { - if ($fs['search'] != 0) { - $qry .= $prefix . $fs['field'] . $qop . ' ' . - $fs['search']; - } else { - $qry .= $prefix . $fs['field'] . ' IS NULL'; - } - } elseif (!strncmp($fs['field'], 'bool_', strlen('bool_'))) { - $qry .= $prefix . $fs['field'] . $qop . ' ' . - $fs['search']; - } elseif ( - $fs['qry_op'] === AdvancedMembersList::OP_BEFORE - || $fs['qry_op'] === AdvancedMembersList::OP_AFTER - ) { - if ($prefix === 'a.') { - //dates are OK in the main fields. no cast, just query! - $qry .= $prefix . $fs['field'] . $qop . ' ' . - $zdb->platform->quoteValue($fs['search']); - } else { - //dynamic dates are stored in their localized format :/ - //use current lang format to query for now - if ($zdb->isPostgres()) { - $fs['search'] = "to_date('" . $fs['search'] . "', 'YYYY-MM-DD')"; - $store_fmt = __("Y-m-d") === 'Y-m-d' ? 'YYYY-MM-DD' : 'DD/MM/YYYY'; - $qry .= "to_date('" . $prefix . $fs['field'] . "', '$store_fmt')"; - } else { - $fs['search'] = "STR_TO_DATE('" . $fs['search'] . "', '%Y-%m-%d')"; - $store_fmt = __("Y-m-d") === 'Y-m-d' ? '%Y-%m-%d' : '%d/%m/%Y'; - $qry .= 'STR_TO_DATE(' . $prefix . $fs['field'] . ', \'' . $store_fmt . '\') '; - } - - $qry .= $qop . ' ' . $fs['search']; - } - } elseif ($fs['field'] == 'status_label') { - $qry_pattern = '%p%field %op %value'; - $qry .= str_replace( - [ - '%p', - '%field', - '%op', - '%value' - ], - [ - 'p.', - 'libelle_statut', - $qop, - $zdb->platform->quoteValue($fs['search']) - ], - $qry_pattern - ); + if ($prefix === 'a.') { + //dates are OK in the main fields. no cast, just query! + $qry .= $prefix . $fs['field'] . $qop . ' ' . + $zdb->platform->quoteValue($fs['search']); + } else { + //dynamic dates are stored in their localized format :/ + //use current lang format to query for now + if ($zdb->isPostgres()) { + $fs['search'] = "to_date('" . $fs['search'] . "', 'YYYY-MM-DD')"; + $store_fmt = __("Y-m-d") === 'Y-m-d' ? 'YYYY-MM-DD' : 'DD/MM/YYYY'; + $qry .= "to_date('" . $prefix . $fs['field'] . "', '$store_fmt')"; } else { - $qry .= 'LOWER(' . $prefix . $fs['field'] . ') ' . - $qop . ' ' . $zdb->platform->quoteValue($fs['search']); + $fs['search'] = "STR_TO_DATE('" . $fs['search'] . "', '%Y-%m-%d')"; + $store_fmt = __("Y-m-d") === 'Y-m-d' ? '%Y-%m-%d' : '%d/%m/%Y'; + $qry .= 'STR_TO_DATE(' . $prefix . $fs['field'] . ', \'' . $store_fmt . '\') '; } - if ($fs['log_op'] === AdvancedMembersList::OP_AND) { - $select->where($qry); - } elseif ($fs['log_op'] === AdvancedMembersList::OP_OR) { - $select->where($qry, PredicateSet::OP_OR); - } + $qry .= $qop . ' ' . $fs['search']; } + } elseif ($fs['field'] == 'status_label') { + $qry_pattern = '%p%field %op %value'; + $qry .= str_replace( + [ + '%p', + '%field', + '%op', + '%value' + ], + [ + 'p.', + 'libelle_statut', + $qop, + $zdb->platform->quoteValue($fs['search']) + ], + $qry_pattern + ); + } else { + $qry .= 'LOWER(' . $prefix . $fs['field'] . ') ' . + $qop . ' ' . $zdb->platform->quoteValue($fs['search']); } - } - return $select; - } catch (\Exception $e) { - Analog::log( - __METHOD__ . ' | ' . $e->getMessage(), - Analog::WARNING - ); - throw $e; + if ($fs['log_op'] === AdvancedMembersList::OP_AND) { + $select->where($qry); + } elseif ($fs['log_op'] === AdvancedMembersList::OP_OR) { + $select->where($qry, PredicateSet::OP_OR); + } + } } + + return $select; } /** diff --git a/tests/Galette/Repository/tests/units/Members.php b/tests/Galette/Repository/tests/units/Members.php index 0cbfcee80..d609779d6 100644 --- a/tests/Galette/Repository/tests/units/Members.php +++ b/tests/Galette/Repository/tests/units/Members.php @@ -318,6 +318,41 @@ class Members extends atoum $this->integer($list->count())->isIdenticalTo(2); + //search on email + $filters = new \Galette\Filters\MembersList(); + $filters->filter_str = '.fr'; + $filters->field_filter = \Galette\Repository\Members::FILTER_MAIL; + $members = new \Galette\Repository\Members($filters); + $list = $members->getList(); + + $this->integer($list->count())->isIdenticalTo(6); + + //search on name + $filters = new \Galette\Filters\MembersList(); + $filters->filter_str = 'marc'; + $filters->field_filter = \Galette\Repository\Members::FILTER_NAME; + $members = new \Galette\Repository\Members($filters); + $list = $members->getList(); + + $this->integer($list->count())->isIdenticalTo(4); + + //serch on contribution date + $filters = new \Galette\Filters\AdvancedMembersList(); + $contribdate = new \DateTime(); + $contribdate->modify('+2 days'); + $filters->contrib_begin_date_begin = $contribdate->format('Y-m-d'); + $members = new \Galette\Repository\Members($filters); + $list = $members->getList(); + + $this->integer($list->count())->isIdenticalTo(0); + + $contribdate->modify('-5 days'); + $filters->contrib_begin_date_begin = $contribdate->format('Y-m-d'); + $members = new \Galette\Repository\Members($filters); + $list = $members->getList(); + $this->integer($list->count())->isIdenticalTo(1); + + //not filtered list $members = new \Galette\Repository\Members(); $list = $members->getList(true); @@ -325,6 +360,7 @@ class Members extends atoum ->hasSize(10) ->object[0]->isInstanceOf('\Galette\Entity\Adherent'); + //get list with specified fields $members = new \Galette\Repository\Members(); $list = $members->getList(false, ['nom_adh', 'ville_adh']); $this->integer($list->count())->isIdenticalTo(10); diff --git a/tests/fixtures/tests_members.json b/tests/fixtures/tests_members.json index 47b10dfea..ab3432385 100644 --- a/tests/fixtures/tests_members.json +++ b/tests/fixtures/tests_members.json @@ -17,7 +17,7 @@ "titre_adh": null, "ddn_adh": "2016-01-19", "lieu_naissance": "PaulVille", - "pseudo_adh": "etienne25", + "pseudo_adh": "marcetienne25", "pays_adh": null, "tel_adh": "+33 2 93 73 14 36", "url_adh": "http:\/\/www.perrin.net\/", @@ -28,8 +28,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "Veule", + "prenom_adh": "Marc", "ville_adh": "Deschampsdan", "cp_adh": "95 474", "adresse_adh": "3, rue de Delaunay", @@ -56,8 +56,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "Doe", + "prenom_adh": "John", "ville_adh": "Mahe", "cp_adh": "41657", "adresse_adh": "374, chemin de Muller", @@ -84,8 +84,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "Smith", + "prenom_adh": "Andre-Marc", "ville_adh": "Loiseau", "cp_adh": "53 280", "adresse_adh": "76, rue Daniel Collin", @@ -112,8 +112,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "Dupont", + "prenom_adh": "Vincent", "ville_adh": "Loiseau", "cp_adh": "53 280", "adresse_adh": "rue Collin", @@ -140,8 +140,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "Montmarc", + "prenom_adh": "Jules", "ville_adh": "Chretienboeuf", "cp_adh": "62 309", "adresse_adh": "1, avenue Marc Bourgeois", @@ -168,8 +168,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "Satto", + "prenom_adh": "Joseph", "ville_adh": "Voisin", "cp_adh": "50 542", "adresse_adh": "862, boulevard de Fleury", @@ -196,8 +196,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "De Volder", + "prenom_adh": "Patrice", "ville_adh": "Gillet", "cp_adh": "92 731", "adresse_adh": "35, chemin Emmanuelle Bazin", @@ -224,8 +224,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "Lecoq", + "prenom_adh": "Serge", "ville_adh": "Mace", "cp_adh": "83 183", "adresse_adh": "98, rue de Gillet", @@ -252,8 +252,8 @@ "fingerprint": "FAKER335689" }, { - "nom_adh": "Bertrand", - "prenom_adh": "R\u00e9my", + "nom_adh": "Wockykowski", + "prenom_adh": "Bronilsaw", "ville_adh": "Coulon", "cp_adh": "70343", "adresse_adh": "483, avenue de Paul", @@ -279,4 +279,4 @@ "pref_lang": "ca", "fingerprint": "FAKER335689" } -] \ No newline at end of file +] -- 2.39.2