/**
* Query for uuser list
* @param Doctrine_Query $q
* @return Doctrine_Query
*/
public function retrieveBackendUuserList(Doctrine_Query $q)
{
$rootAlias = $q->getRootAlias();
$q->leftJoin($rootAlias . '.BalanceUser bu');
$q->andWhere($rootAlias . '.utype=?', 'uuser');
$q->andWhere($rootAlias . '.password IS NOT NULL');
return $q;
}
protected function addStatutColumnQuery(Doctrine_Query $query, $field, $value)
{
if ($value == 0) {
$query->andWhere($query->getRootAlias() . '.statut <> ?', $value);
}
if ($value != 0) {
$query->andWhere($query->getRootAlias() . '.statut = ?', $value);
}
}
public function addRangeQuery(Doctrine_Query $query, $field, $values)
{
$fieldName = $this->getFieldName($field);
if ($values['from']) {
$query->andWhere(sprintf('%s.%s >= ?', $query->getRootAlias(), $fieldName), $values['from']);
}
if ($values['to']) {
$query->andWhere(sprintf('%s.%s <= ?', $query->getRootAlias(), $fieldName), $values['to']);
}
}
protected function addScheduledTimeColumnQuery(Doctrine_Query $query, $field, $values)
{
switch ($values) {
case 'from_now':
$query->andWhere('r.scheduled_time >= ?', date('Y-m-d'));
break;
case 'all':
break;
default:
$values = str_replace('.', '-', $values);
$day_start = date('Y-m-d', strtotime($values));
$day_end = date('Y-m-d 23:59', strtotime($values) + 24 * 60 * 60);
$query->andWhere("r.scheduled_time >= ? AND r.scheduled_time <= ?", array($day_start, $day_end));
break;
}
}
public function addClassificationColumnQuery(Doctrine_Query $query, $field, $values)
{
if ($values != "") {
$query->andWhere(" classification = ? ", $values);
}
return $query;
}
public function _setProgramFilterCallback(Doctrine_Query $query, $value)
{
if (!empty($value)) {
$query->andWhere('pI.program_id = ?', $value);
}
return $query;
}
public function addSymplistIndexColumnQuery(Doctrine_Query $query, $field, $value)
{
if ($value) {
$query->andWhere('symplist_index IS NOT NULL');
}
return $query;
}
function buildQuery(Doctrine_Query $query)
{
if ($this->getValue('name')) {
$query->andWhere('name LIKE ?', '%' . $this->getValue('name') . '%');
}
return $query;
}
/**
* Apply user restriction to doctrine query
* @param Doctrine_Query $query
*/
private function applyQueryToDoctrineQuery(Doctrine_Query $query)
{
if ($this->getQuery()) {
$queryVal = '%' . $this->getQuery() . '%';
$queryParams = array($queryVal, $queryVal, $queryVal, $queryVal);
$query->andWhere('(user_name LIKE ? OR user_firstname LIKE ?' . ' OR user_lastname LIKE ? OR user_email LIKE ?)', $queryParams);
}
}
private function applyQueryToDoctrineQuery(Doctrine_Query $query)
{
if ($this->getQuery()) {
$searchQuery = '%' . $this->getQuery() . '%';
$searchParams = array($searchQuery, $searchQuery);
$query->andWhere('(role_name LIKE ? OR role_description LIKE ?)', $searchParams);
}
}
public function addIgNumColumnQuery(Doctrine_Query $query, $field, $values)
{
if ($values != "") {
$conn_MGR = Doctrine_Manager::connection();
$query->andWhere("ig_num_indexed like concat(fullToIndex(" . $conn_MGR->quote($values, 'string') . "), '%') ");
}
return $query;
}
public function filterScope(Doctrine_Query $query, sfUser $user)
{
//@todo: fix this shit!!!!!
//this is very ugly
$eventFilter = Doctrine_Query::create()->from('FishEvent fe')->select('fe.id')->where("fe.location_id in (" . Doctrine::getTable('Location')->getVisibleLocationsQuery($user, "lc1")->select('lc1.id') . ")");
$profitFilter = Doctrine_Query::create()->from('Profit pr')->select('pr.id')->where("pr.location_id in (" . Doctrine::getTable('Location')->getVisibleLocationsQuery($user, "lc2")->select('lc2.id') . ")");
return $query->andWhere("c.location_id in (" . Doctrine::getTable('Location')->getVisibleLocationsQuery($user, "lc3")->select('lc3.id') . ") or c.location_id is null")->andWhere("c.profit_id in ({$profitFilter}) or c.profit_id is null")->andWhere("c.fish_event_id in ({$eventFilter}) or c.fish_event_id is null");
}
public function addCallerIdColumnQuery(Doctrine_Query $query, $field, $values)
{
if ($values != "") {
$alias = $query->getRootAlias();
$query->andWhere($alias . '.id != ?', $values);
}
return $query;
}
public function retrieveAdminChargeList(Doctrine_Query $q)
{
$rootAlias = $q->getRootAlias();
$q->leftJoin($rootAlias . '.Category ct');
$q->leftJoin($rootAlias . '.Vehicle v');
$q->andWhere('v.is_archived = ?', false);
return $q;
}
public function addVisiteurIdColumnQuery(Doctrine_Query $query, $field, $value)
{
//Pour le xxxx tu peux utiliser $query->getRootAlias() pour récupérer l'alias racine de ta requete
if ($value['text']) {
$query->leftjoin($query->getRootAlias() . ".Visiteur v");
$query->andWhere('v.pseudo_son like ?', "%" . $value['text'] . "%");
}
}
protected function addDateQuery(Doctrine_Query $query, $fieldName, $values)
{
if (isset($values['is_empty']) && $values['is_empty']) {
$query->andWhere(sprintf('%s.%s IS NULL', $query->getRootAlias(), $fieldName));
} else {
if (null !== $values['from'] && null !== $values['to']) {
$query->andWhere(sprintf('%s.%s >= ? AND %s.%s <= ?', $query->getRootAlias(), $fieldName, $query->getRootAlias(), $fieldName), array($values['from'], $values['to']));
} else {
if (null !== $values['from']) {
$query->andWhere(sprintf('%s.%s >= ?', $query->getRootAlias(), $fieldName), $values['from']);
} else {
if (null !== $values['to']) {
$query->andWhere(sprintf('%s.%s <= ?', $query->getRootAlias(), $fieldName), $values['to']);
}
}
}
}
}
function buildQuery(Doctrine_Query $query)
{
if ($this->getValue('folder_id')) {
$query->andWhere('folder_id = ?', $this->getValue('folder_id'));
}
if ($this->getValue('filename')) {
$query->andWhere('filename LIKE ?', '%' . $this->getValue('filename') . '%');
}
if ($this->getValue('author')) {
$query->andWhere('author LIKE ?', '%' . $this->getValue('author') . '%');
}
if ($this->getValue('description')) {
$query->andWhere('description LIKE ?', '%' . $this->getValue('description') . '%');
}
if ($this->getValue('copyright')) {
$query->andWhere('copyright LIKE ?', '%' . $this->getValue('copyright') . '%');
}
return $query;
}
/**
*
* @param Doctrine_Query $query
* @param FilterContactForm $filter
* @return Doctrine_Query
*/
public function filter(Doctrine_Query $query, $filter)
{
if (!$filter) {
return $query;
}
$alias = $query->getRootAlias();
$search = trim($filter->getValue(self::FILTER_SEARCH));
if ($search) {
$query->andWhere('concat(' . $alias . '.email, " ", ' . $alias . '.firstname, " ", ' . $alias . '.lastname) LIKE ?', '%' . $search . '%');
}
return $query;
}
/**
* @param $filter
*/
protected function published($filter)
{
switch ($filter->operator) {
case 'is':
$this->_query->andWhere('f.published = ?', 1);
break;
case 'is_not':
$this->_query->andWhere('f.published = ?', 0);
break;
break;
default:
}
}
/**
* @param $filter
*/
protected function ysize($filter)
{
switch ($filter->operator) {
case 'is':
$this->_query->andWhere('f.ysize = ?', (int) $filter->query);
break;
case 'greater_than':
$this->_query->andWhere('f.ysize >= ?', (int) $filter->query);
break;
case 'lesser_than':
$this->_query->andWhere('f.ysize <= ?', (int) $filter->query);
break;
default:
}
}
public function filterByEditable(Doctrine_Query $q, $user_id = null)
{
if (is_null($user_id)) {
$user_id = sfContext::getInstance()->getUser()->getGuardUser()->getId();
if (sfContext::getInstance()->getUser()->hasCredential('admin')) {
return;
}
}
$rootAlias = $q->getRootAlias();
$q->leftJoin($rootAlias . '.Editors e');
$q->leftJoin($rootAlias . '.Categories c');
$q->leftJoin('c.Users u');
$q->leftJoin('c.Groups g');
$q->leftJoin('g.Users gu');
$q->andWhere('author_id = ? OR e.id = ? OR u.id = ? OR gu.id = ?', array($user_id, $user_id, $user_id, $user_id));
}
protected function addSearchColumnQuery(Doctrine_Query $query, $field, $values)
{
$where = array();
$params = array();
$table = Doctrine_Core::getTable($this->_modelName);
$columns = $table->getColumns();
foreach ($columns as $name => $column) {
if (isset($column['primary']) || $column['type'] == 'integer') {
continue;
}
$operator = strpos($values, '*') !== false ? 'LIKE' : '=';
$where[] = $name . ' ' . $operator . ' ?';
$params[] = str_replace('*', '%', $values);
}
$query->andWhere('(' . implode(' OR ', $where) . ')', $params);
}
protected function addDateQuery(Doctrine_Query $query, $field, $values)
{
$fieldName = $this->getFieldName($field);
switch ($values) {
case null:
case '':
break;
case 'today':
$query->andWhere(sprintf('%s.%s >= ?', $this->getRootAlias($query, $fieldName), $fieldName), date('Y-m-d H:i:s', strtotime('-1 day')));
break;
case 'week':
$query->andWhere(sprintf('%s.%s >= ?', $this->getRootAlias($query, $fieldName), $fieldName), date('Y-m-d H:i:s', strtotime('-1 week')));
break;
case 'month':
$query->andWhere(sprintf('%s.%s >= ?', $this->getRootAlias($query, $fieldName), $fieldName), date('Y-m-d H:i:s', strtotime('-1 month')));
break;
case 'year':
$query->andWhere(sprintf('%s.%s >= ?', $this->getRootAlias($query, $fieldName), $fieldName), date('Y-m-d H:i:s', strtotime('-1 year')));
break;
}
}
protected function addSearchKeywordQuery(Doctrine_Query $q, $keywords)
{
foreach ($keywords as $keyword) {
$q->andWhere('title LIKE ? OR body LIKE ?', array('%' . $keyword . '%', '%' . $keyword . '%'));
}
}
protected function addIdColumnQuery(Doctrine_Query $query, $value)
{
if (!empty($value)) {
$query->andWhere('id = ?', $value);
}
}
public function retrieveActiveJob(Doctrine_Query $query)
{
$query->andWhere('a.expires_at > ?', date('Y-m-d h:i:s', time()))->limit(1);
return $query->execute();
}
protected function addSearchKeywordQuery(Doctrine_Query $q, $keywords)
{
foreach ($keywords as $keyword) {
$q->andWhere('description LIKE ?', array('%' . $keyword . '%'));
}
}
/**
* Create the query for selecting objects, eventually along with related
* objects
*
* @param array $params an array of criterions for the selection
*/
public function addToQuery(Doctrine_Query $query, $params)
{
if (isset($sort)) {
$query->orderBy($sort);
}
if (isset($params['id'])) {
$values = explode(',', $params['id']);
if (count($values) == 1) {
$query->andWhere($this->model . '.id = ?', $values[0]);
} else {
$query->whereIn($this->model . '.id', $values);
}
unset($params['id']);
}
foreach ($params as $name => $value) {
$query->andWhere($this->model . '.' . $name . ' = ?', $value);
}
return $query;
}
/**
* Appends the necessary clauses to an existing query in order to restrict the query.
*
* @param Doctrine_Query $query
*
* @return void
*/
protected function appendQuery(Doctrine_Query $query)
{
if (!self::isActive()) {
return;
}
$target = $this->getTarget();
// null means unlimited access
if ($target === null) {
return;
}
$field = $this->getField();
// if we are dealing with an array, generate an IN statement
if (is_array($target)) {
// just kill the query if target is empty
if (empty($target)) {
$query->andWhere('true=false');
return;
}
$query->andWhereIn($field, $target);
return;
}
// otherwise it is a scalar and we generate a normal where AND statement
$query->andWhere($field . ' = ?', $target);
}
protected function addDateColumnQuery(Doctrine_Query $query, $field, $values)
{
$fieldName = $this->getFieldName($field);
//var_dump($query->getTableAliasMap());
//$rootalias = $query->getSqlTableAlias('dsLesson');
$rootalias = 'l';
/*
$alias = 'cf';
$query->getSqlQuery();
$component = $query->getQueryComponent($alias);
$recordClass = get_class($component['table']->getRecordInstance());
*/
//$query->innerJoin($query->getRootAlias() . '.Lesson l');
// ->addWhere($rootalias.'.date = ?', $value);
if (isset($values['is_empty']) && $values['is_empty'])
{
$query->addWhere(sprintf('%s.%s IS NULL', $rootalias, $fieldName));
}
else
{
if (null !== $values['from'] && null !== $values['to'])
{
$query->andWhere(sprintf('%s.%s >= ?', $rootalias, $fieldName), $values['from']);
$query->andWhere(sprintf('%s.%s <= ?', $rootalias, $fieldName), $values['to']);
}
else if (null !== $values['from'])
{
$query->andWhere(sprintf('%s.%s >= ?', $rootalias, $fieldName), $values['from']);
}
else if (null !== $values['to'])
{
$query->andWhere(sprintf('%s.%s <= ?', $rootalias, $fieldName), $values['to']);
}
}
}
