protected function __listTables($cache = TRUE) { if ($cache && sizeof($this->tables)) { return $this->tables; } $select = new Select('information_schema.TABLES'); $select->where(array(array('field' => 'TABLE_SCHEMA', 'value' => $this->params['name']))); return $this->tables = $select->execute('\\apf\\db\\mysql5\\Table', $smart = FALSE); }
public function testSelectComWheresComOrEAnd() { $select = new Select(); $where = new Where(); $where->set('id', '=', 1); $where->set('last_name', '=', 1, 'or'); $where->set('name', '=', 'Erik', 'or'); $select->table = 'pages'; $select->where($where); $sql = $select->sql(); $this->assertEquals('SELECT * FROM pages WHERE `id`=:id and `last_name`=:last_name or `name`=:name;', $sql); }
/** * Cláusula WHERE * * @example database::Query::where * <code> * <?php * # vide Query::factory * $query = Query::factory(..); * * # foo = 'foo' * $filter = $query->foo->equals('foo'); * * # aplica condicao a query * $query->where($filter); * ?> * </code> * * {@ineritdoc} * */ public function where(RelationalAbstract $conditional, $operator = Where::T_AND_OPERATOR) { if (NULL == $this->_where) { # a Query e' requerida para permitir o encadeamento de filtro # ex.: $query->where()->having()..->otherPossibleFilter(); $this->_where = Where::factory($this); # registra WHERE no SELECT $this->_select->where($this->_where); } # adiciona o filtro ao WHERE $this->_where->add($conditional, $operator); # retorna regerencia de WHERE para o objeto que invocou o metodo return $this; }
protected static function getAllOnWhere($table_name, $class, $where = false, $values = false, $order = false, $ask = true, $count = false, $offset = false) { $select = new Select(); $select->from($table_name, "*"); if ($where) { $select->where($where, $values); } if ($order) { $select->order($order, $ask); } else { $select->order("id"); } if ($count) { $select->limit($count, $offset); } $data = self::$db->select($select); return AbstractObjectDB::buildMultiple($class, $data); }
/** * Builds where clause, for filtering on simple list mode * * @param Select $select Original select * * @return string SQL WHERE clause */ private function _buildWhereClause($select) { global $zdb, $login; $field = 'date_debut_cotis'; switch ($this->_date_field) { case self::DATE_RECORD: $field = 'date_enreg'; break; case self::DATE_END: $field = 'date_fin_cotis'; break; case self::DATE_BEGIN: default: $field = 'date_debut_cotis'; break; } try { if ($this->_start_date_filter != null) { $d = new \DateTime($this->_start_date_filter); $select->where->greaterThanOrEqualTo($field, $d->format('Y-m-d')); } if ($this->_end_date_filter != null) { $d = new \DateTime($this->_end_date_filter); $select->where->lessThanOrEqualTo($field, $d->format('Y-m-d')); } if ($this->_payment_type_filter != null) { $select->where->equalTo('type_paiement_cotis', $this->_payment_type_filter); } if ($this->_from_transaction !== false) { $select->where->equalTo(Transaction::PK, $this->_from_transaction); } if ($this->_max_amount !== null && is_int($this->_max_amount)) { $select->where('(montant_cotis <= ' . $this->_max_amount . ' OR montant_cotis IS NULL)'); } if (!$login->isAdmin() && !$login->isStaff()) { //non staff members can only view their own contributions $select->where(array('a.' . Adherent::PK => $login->id)); } else { if ($this->_filtre_cotis_adh != null) { $select->where('a.' . Adherent::PK . ' = ' . $this->_filtre_cotis_adh); } } if ($this->_filtre_transactions === true) { $select->where('a.trans_id IS NULL'); } } catch (\Exception $e) { Analog::log(__METHOD__ . ' | ' . $e->getMessage(), Analog::WARNING); } }
/** * Builds where clause, for filtering on simple list mode * * @param Select $select Original select * * @return string SQL WHERE clause */ private function _buildWhereClause($select) { global $zdb, $login; try { if ($this->_start_date_filter != null) { $d = new \DateTime($this->_start_date_filter); $select->where->greaterThanOrEqualTo('trans_date', $d->format('Y-m-d')); } if ($this->_end_date_filter != null) { $d = new \DateTime($this->_end_date_filter); $select->where->lessThanOrEqualTo('trans_date', $d->format('Y-m-d')); } if (!$login->isAdmin() && !$login->isStaff()) { //non staff members can only view their own transactions $select->where('t.' . Adherent::PK . ' = ' . $login->id); } else { if ($this->_filtre_cotis_adh != null) { $select->where('t.' . Adherent::PK . ' = ' . $this->_filtre_cotis_adh); } } } catch (\Exception $e) { Analog::log(__METHOD__ . ' | ' . $e->getMessage(), Analog::WARNING); } }
/** * Generate WHERE clause from user-supplied string or array * * @param string|array $where OPTIONAL An SQL WHERE clause. * @return Select */ protected function _where(Select $select, $where) { $where = (array) $where; foreach ($where as $key => $val) { // is $key an int? if (is_int($key)) { // $val is the full condition $select->where($val); } else { // $key is the condition with placeholder, // and $val is quoted into the condition $select->where($key, $val); } } return $select; }
/** * Builds where clause, for filtering on simple list mode * * @param Select $select Original select * * @return string SQL WHERE clause */ private function _buildWhereClause($select) { global $zdb, $login; try { if ($this->_filters->email_filter == self::FILTER_W_EMAIL) { $select->where('email_adh != \'\''); } if ($this->_filters->email_filter == self::FILTER_WO_EMAIL) { $select->where('(email_adh = \'\' OR email_adh IS NULL)'); } if ($this->_filters->filter_str != '') { $token = $zdb->platform->quoteValue('%' . strtolower($this->_filters->filter_str) . '%'); switch ($this->_filters->field_filter) { case self::FILTER_NAME: if (TYPE_DB === 'pgsql') { $sep = " || ' ' || "; $pre = ''; $post = ''; } else { $sep = ', " ", '; $pre = 'CONCAT('; $post = ')'; } //$sep = ( TYPE_DB === 'pgsql' ) ? " || ' ' || " : ', " ", '; $select->where('(' . $pre . 'LOWER(nom_adh)' . $sep . 'LOWER(prenom_adh)' . $sep . 'LOWER(pseudo_adh)' . $post . ' LIKE ' . $token . ' OR ' . $pre . 'LOWER(prenom_adh)' . $sep . 'LOWER(nom_adh)' . $sep . 'LOWER(pseudo_adh)' . $post . ' LIKE ' . $token . ')'); break; case self::FILTER_COMPANY_NAME: $select->where('LOWER(societe_adh) LIKE ' . $token); break; case self::FILTER_ADDRESS: $select->where('(' . 'LOWER(adresse_adh) LIKE ' . $token . ' OR ' . 'LOWER(adresse2_adh) LIKE ' . $token . ' OR ' . 'cp_adh LIKE ' . $token . ' OR ' . 'LOWER(ville_adh) LIKE ' . $token . ' OR ' . 'LOWER(pays_adh) LIKE ' . $token . ')'); break; case self::FILTER_MAIL: $select->where('(' . 'LOWER(email_adh) LIKE ' . $token . ' OR ' . 'LOWER(url_adh) LIKE ' . $token . ' OR ' . 'LOWER(msn_adh) LIKE ' . $token . ' OR ' . 'LOWER(icq_adh) LIKE ' . $token . ' OR ' . 'LOWER(jabber_adh) LIKE ' . $token . ')'); break; case self::FILTER_JOB: $select->where('LOWER(prof_adh) LIKE ' . $token); break; case self::FILTER_INFOS: $more = ''; if ($login->isAdmin() || $login->isStaff()) { $more = ' OR LOWER(info_adh) LIKE ' . $token; } $select->where('(LOWER(info_public_adh) LIKE ' . $token . $more . ')'); break; case self::FILTER_NUMBER: $select->where->equalTo('a.id_adh', $this->_filters->filter_str); break; } } if ($this->_filters->membership_filter) { switch ($this->_filters->membership_filter) { case self::MEMBERSHIP_NEARLY: $now = new \DateTime(); $duedate = new \DateTime(); $duedate->modify('+1 month'); $select->where->greaterThan('date_echeance', $now->format('Y-m-d'))->lessThan('date_echeance', $duedate->format('Y-m-d')); break; case self::MEMBERSHIP_LATE: $select->where->lessThan('date_echeance', date('Y-m-d', time()))->equalTo('bool_exempt_adh', new Expression('false')); break; case self::MEMBERSHIP_UP2DATE: $select->where('(' . 'date_echeance > \'' . date('Y-m-d', time()) . '\' OR bool_exempt_adh=true)'); break; case self::MEMBERSHIP_NEVER: $select->where('date_echeance IS NULL')->where('bool_exempt_adh = false'); break; case self::MEMBERSHIP_STAFF: $select->where->lessThan('p.priorite_statut', self::NON_STAFF_MEMBERS); break; case self::MEMBERSHIP_ADMIN: $select->where->equalTo('bool_admin_adh', true); break; case self::MEMBERSHIP_NONE: $select->where->equalTo('a.id_statut', Status::DEFAULT_STATUS); break; } } if ($this->_filters->account_status_filter) { switch ($this->_filters->account_status_filter) { case self::ACTIVE_ACCOUNT: $select->where('activite_adh=true'); break; case self::INACTIVE_ACCOUNT: $select->where('activite_adh=false'); break; } } if ($this->_filters->group_filter) { $select->join(array('g' => PREFIX_DB . Group::GROUPSUSERS_TABLE), 'a.' . Adherent::PK . '=g.' . Adherent::PK, array('*'), $select::JOIN_LEFT)->join(array('gs' => PREFIX_DB . Group::TABLE), 'gs.' . Group::PK . '=g.' . Group::PK, array('*'), $select::JOIN_LEFT)->where('(g.' . Group::PK . ' = ' . $this->_filters->group_filter . ' OR gs.parent_group = NULL OR gs.parent_group = ' . $this->_filters->group_filter . ')'); } if ($this->_filters instanceof AdvancedMembersList) { 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 ($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 ($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')); } } 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->status) { $select->where->in('a.id_statut', $this->_filters->status); } 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->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->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 ($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->contributions_types) { $select->where->in('ct.id_type_cotis', $this->_filters->contributions_types); } if ($this->_filters->payments_types) { $select->where->in('ct.type_paiement_cotis', $this->_filters->payments_types); } 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'; } else { //dynamic field spotted! $prefix = 'dfc.'; $qry = 'dfc.field_form = \'contrib\' AND ' . 'dfc.field_id = ' . $k . ' AND '; $field = 'field_val'; } if (is_array($cd)) { $select->where->in($prefix . $field, $cd); } else { $qry .= 'LOWER(' . $prefix . $field . ') ' . $qop . ' '; $select->where($qry . '%' . strtolower($cd) . '%'); } } } 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; default: Analog::log('Unknown query operator: ' . $fs['qry_op'] . ' (will fallback to equals)', Analog::WARNING); $qop = '='; break; } $qry = ''; $prefix = 'a.'; if (strpos($fs['field'], 'dync_') === 0) { //dynamic choice spotted! $index = str_replace('dync_', '', $fs['field']); $prefix = 'cdf' . $index . '.'; $qry = 'df.field_form = \'adh\' AND df.field_id = ' . str_replace('dync_', '', $fs['field']) . ' AND '; $fs['field'] = 'val'; } elseif (strpos($fs['field'], 'dyn_') === 0) { //dynamic field spotted! $prefix = 'df.'; $qry = 'df.field_form = \'adh\' AND df.field_id = ' . str_replace('dyn_', '', $fs['field']) . ' AND '; $fs['field'] = 'field_val'; } if (!strncmp($fs['field'], 'bool_', strlen('bool_'))) { $qry .= $prefix . $fs['field'] . $qop . ' ' . $fs['search']; } else { $qry .= 'LOWER(' . $prefix . $fs['field'] . ') ' . $qop . ' ' . $zdb->platform->quoteValue($fs['search']); } if ($fs['log_op'] === AdvancedMembersList::OP_AND) { $select->where($qry); } elseif ($fs['log_op'] === AdvancedMembersList::OP_OR) { $select->orWhere($qry); } } } } } catch (\Exception $e) { Analog::log(__METHOD__ . ' | ' . $e->getMessage(), Analog::WARNING); } }