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);
}
}
