protected function addIsPublishedColumnQuery(Doctrine_Query $query, $field, $values)
{
if ($values) {
$query->addWhere('r.date_published < NOW()');
} else {
$query->addWhere('r.date_published >= NOW() OR r.date_published IS NULL');
}
}
function buildQuery(Doctrine_Query $query)
{
$query->leftJoin('swBlogPost.Translation');
$query->leftJoin('swBlogPost.swBlogPostTags t');
$query->leftJoin('t.Translation tt');
if (strlen($this->getValue('tag')) > 0) {
$query->addWhere('tt.name = ? and tt.lang = ?', array($this->getValue('tag'), sfContext::getInstance()->getUser()->getCulture()));
}
if (!is_null($this->getValue('published'))) {
$query->addWhere('swBlogPost.published = ?', $this->getValue('published'));
}
$query->orderBy('created_at DESC');
return $query;
}
protected function addNameColumnQuery(Doctrine_Query $query, $value)
{
if (!empty($value)) {
if (is_array($value)) {
foreach ($value as $v) {
$query->addWhere('name LIKE ?', '%' . $v . '%');
}
} else {
if (!empty($value)) {
$query->addWhere('name LIKE ?', '%' . $values . '%');
}
}
}
}
/**
* getSetting
* pulls the sfSetting object for a given setting
*
* @param string $setting
* @static
* @access public
* @return object sfSetting
*/
static function getSetting($setting)
{
if (!is_string($setting) || empty($setting)) {
return 0;
}
// If all the settings have been requested, there's no need to check for
// individuals. This avoids additional queries later on.
if (sfContext::getInstance()->getRequest()->hasAttribute('AllsfSettings')) {
$settings = sfContext::getInstance()->getRequest()->getAttribute('AllsfSettings');
} else {
$settings = sfContext::getInstance()->getRequest()->getAttribute('sfSettings');
}
if (isset($settings[$setting])) {
$obj = $settings[$setting];
return $obj;
} else {
// Setting was not pre-loaded via ->load() but we'll be nice and retrieve
// the setting anyhow:
$query = new Doctrine_Query();
$query->addSelect("s.*");
$query->addFrom("sfSetting s");
$query->addWhere("s.name = :name", array(":name" => $setting));
if ($obj = $query->limit(1)->execute()->getFirst()) {
// Store this setting in memory for later retrieval to avoid a second
// query for the same setting:
$settings[$obj->getName()] = $obj;
sfContext::getInstance()->getRequest()->setAttribute('sfSettings', $settings);
// return it:
return $obj;
} else {
return 0;
}
}
}
/**
* hookWhere
* builds DQL query where part from given parameter array
*
* @param array $params an associative array containing field
* names and their values
* @return boolean whether or not the hooking was
*/
public function hookWhere($params)
{
if (!is_array($params)) {
return false;
}
foreach ($params as $name => $value) {
if ($value === '' || $value === '-') {
continue;
}
$e = explode('.', $name);
if (count($e) == 2) {
list($alias, $column) = $e;
$map = $this->query->getAliasDeclaration($alias);
$table = $map['table'];
if (!$table) {
throw new Doctrine_Exception('Unknown alias ' . $alias);
}
if ($def = $table->getDefinitionOf($column)) {
$def[0] = gettype($value);
if (isset($this->typeParsers[$def[0]])) {
$name = $this->typeParsers[$def[0]];
$parser = new $name();
}
$parser->parse($alias, $column, $value);
$this->query->addWhere($parser->getCondition(), $parser->getParams());
}
}
}
return true;
}
public function addUpcoming(Doctrine_Query $q, $limit = null)
{
$q->orderBy('start_date');
$q->addWhere('DATE(start_date) >= DATE(NOW())');
if (!is_null($limit)) {
$q->limit($limit);
}
}
protected function addNameColumnQuery(Doctrine_Query $query, $field, $values)
{
$fieldName = $this->getFieldName($field);
if (is_array($values)) {
foreach ($values as $value) {
$query->addWhere('r.' . $fieldName . ' LIKE ?', '%' . $value . '%');
}
}
}
/**
*
* @param Doctrine_Query $query
* @param sfUser $user
* @param type $alias
* @return Doctrine_Query
*/
public function filterScope(Doctrine_Query $query, sfUser $user, $alias = 'l')
{
$where = "{$alias}.location_scope_id = 5";
if (!$user->isAnonymous()) {
$uniq = self::$uniq++;
$where .= "\r\n or {$alias}.location_scope_id = 1\r\n or {$alias}.location_scope_id = 3\r\n or {$alias}.created_by = " . $user->getProfile()->id . "\r\n or {$alias}.created_by in (SELECT fa{$uniq}.accepter_id FROM friend fa{$uniq} WHERE fa{$uniq}.accepted = 1 and fa{$uniq}.requester_id = " . $user->getProfile()->id . ")\r\n or {$alias}.created_by in (SELECT fr{$uniq}.requester_id FROM friend fr{$uniq} WHERE fr{$uniq}.accepted = 1 and fr{$uniq}.accepter_id = " . $user->getProfile()->id . ")";
}
$query->addWhere($where);
return $query;
}
/**
* DOCUMENT ME
* @param Doctrine_Query $query
* @param mixed $field
* @param mixed $value
* @return mixed
*/
public function addUsernameColumnQuery(Doctrine_Query $query, $field, $value)
{
// You get an associative array with an sfWidgetFormFilterInput
if (!isset($value['text']) || !strlen($value['text'])) {
return;
}
$like = '%' . $value['text'] . '%';
$r = $query->getRootAlias();
$query->addWhere("({$r}.username LIKE ?) OR concat({$r}.first_name, {$r}.last_name) LIKE ?", array($like, $like));
}
/**
* Проверить, что выборка принадлежит текущему авторизованному пользователю
*
* @param Doctrine_Query $q
* @param string $ownerColumn
* @return Doctrine_Query
*/
public function withOwnerCheck(Doctrine_Query $q, $ownerColumn = 'user_id')
{
$user = sfContext::getInstance()->getUser();
if ($user->isAuthenticated()) {
$userId = $user->getGuardUser()->getId();
} else {
$userId = 0;
}
$rootAlias = $q->getRootAlias();
$q->addWhere("{$rootAlias}.{$ownerColumn} = ?", $userId);
return $q;
}
/**
* Get a record detail based the current query
*
* <code>
* $where = array(
* array('columnName' => 'searchValue')
* )
* </code>
*
* @param array $where
* @return array
*/
public function fetchDetail(array $where)
{
/**
* Remove these since we are trying to retrieve
* a specific row
*/
$this->_query->removeDqlQueryPart('limit')->removeDqlQueryPart('offset');
foreach ($where as $column => $value) {
$this->_query->addWhere($column . ' = ?', $value);
}
return $this->execute();
}
function buildQuery(Doctrine_Query $query)
{
$query->orderBy('swBlogComment.created_at ASC');
$query->leftJoin('swBlogComment.swBlogPost');
if ($this->getValue('moderated') == -2) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_NONE, swBlogComment::MODERATED_OK));
}
if ($this->getValue('moderated') == -1) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_NONE));
}
if ($this->getValue('moderated') == -3) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_KO, swBlogComment::MODERATED_OK));
}
if ($this->getValue('moderated') == 1) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_OK));
}
if ($this->getValue('moderated') === 0) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_KO));
}
if (is_numeric($this->getValue('post_id'))) {
$query->addWhere('swBlogComment.post_id = ?', $this->getValue('post_id'));
}
return $query;
}
/**
* Write - owner
*
* @param Doctrine_Query $q
* @param User $u
* @param string $alias (optional)
*/
public static function query_may_write($q, $u, $alias = null)
{
if ($u->is_system()) {
return;
}
$a = $alias ? "{$alias}." : "";
$q->addWhere("{$a}bin_user_id = ?", $u->user_id);
}
protected function addBooleanQuery(Doctrine_Query $query, $field, $value)
{
$fieldName = $this->getFieldName($field);
$query->addWhere(sprintf('%s.%s = ?', $this->getRootAlias($query, $fieldName), $fieldName), $value);
}
/**
* @access private
*
* @author Jannis Moßhammer <*****@*****.**>
**/
protected function modifyImpl(Doctrine_Query &$o)
{
$o->setAliasDefs($this->mainAlias, $this->aliasDefs);
$o->setDefaultJoinType($this->defaultJoinType);
foreach ($this->fields as $field) {
$o->addSelect($field);
}
$o->distinct($this->isDistinct());
$o->from($this->target . " " . $this->mainAlias);
foreach ($this->staticWhereConditions as $cond) {
if (isset($cond[1]) && $cond[1] != null) {
$o->addWhere($cond[0], $cond[1]);
} else {
$o->addWhere($cond[0]);
}
}
}
public function testTableMethod2(Doctrine_Query $q)
{
$q->addWhere('a.id > 0');
return $q;
}
protected function addDateQuery(Doctrine_Query $query, $field, $values)
{
$fieldName = $this->getFieldName($field);
if (isset($values['is_empty']) && $values['is_empty']) {
$query->addWhere(sprintf('%s.%s IS NULL', $query->getRootAlias(), $fieldName));
} else {
if (null !== $values['from'] && null !== $values['to']) {
$query->andWhere(sprintf('%s.%s >= ?', $query->getRootAlias(), $fieldName), $values['from']);
$query->andWhere(sprintf('%s.%s <= ?', $query->getRootAlias(), $fieldName), $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']);
}
}
}
}
}
protected function addDateQuery(Doctrine_Query $query, $field, $values)
{
if (is_array($values)) {
$fieldName = $this->getFieldName($field);
if (isset($values['is_empty']) && $values['is_empty']) {
$query->addWhere(sprintf('%s.%s IS NULL', $query->getRootAlias($query, $fieldName), $fieldName));
} else {
if (null !== $values['from'] && null !== $values['to']) {
$query->andWhere(sprintf('%s.%s >= ?', $this->getRootAlias($query, $fieldName), $fieldName), $values['from']);
$query->andWhere(sprintf('%s.%s <= ?', $this->getRootAlias($query, $fieldName), $fieldName), $values['to']);
} else {
if (null !== $values['from']) {
$query->andWhere(sprintf('%s.%s >= ?', $this->getRootAlias($query, $fieldName), $fieldName), $values['from']);
} else {
if (null !== $values['to']) {
$query->andWhere(sprintf('%s.%s <= ?', $this->getRootAlias($query, $fieldName), $fieldName), $values['to']);
}
}
}
}
} else {
$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;
}
}
}
static function addConcurrence(Doctrine_Query $q, $alias1 = 'r1', $alias2 = 'r2')
{
$whereStr = $alias1 . '.start_date IS NOT NULL AND ' . $alias2 . '.start_date IS NOT NULL AND ' . '((' . $alias1 . '.end_date IS NULL AND ' . $alias2 . '.end_date IS NULL) OR ' . '(' . $alias1 . '.end_date IS NOT NULL AND ' . $alias1 . '.start_date < ' . $alias2 . '.end_date AND ' . $alias1 . '.end_date > ' . $alias2 . '.start_date) OR ' . '(' . $alias2 . '.end_date IS NOT NULL AND ' . $alias2 . '.start_date < ' . $alias1 . '.end_date AND ' . $alias2 . '.end_date > ' . $alias1 . '.start_date))';
return $q->addWhere($whereStr);
}
public function addPublished(Doctrine_Query $q)
{
$rootAlias = $q->getRootAlias();
$q->addWhere($rootAlias . '.status = ? AND ' . $rootAlias . '.published_at <= NOW()', 'published');
}
public static function listenToBuildQuery(sfEvent $event, Doctrine_Query $query)
{
$subject = $event->getSubject();
$root = $query->getRootAlias();
return $query->addWhere($root . '.poll_id = ?', $subject->getRequestParameter('id'));
}
/**
* Write - bin_source writable
*
* @param Doctrine_Query $q
* @param User $u
* @param string $alias (optional)
*/
public static function query_may_write($q, $u, $alias = null)
{
if ($u->is_system()) {
return;
}
$a = $alias ? "{$alias}." : "";
$write_bin_ids = "select bin_id from bin where bin_user_id=?";
$q->addWhere("{$a}bsrs_bin_id in ({$write_bin_ids})", $u->user_id);
}
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']);
}
}
}
/**
*
* This function will formate a conditional statement for the query based on
* a response from the grid (user)
*
* TODO: THIS IS CURRENTLY A SEVERE SECURITY RISK!!!! Unless doctrine is escaping SQL, or maybe Kohana
*
* @return void
* @param Doctrine_Query $q The doctrine query that the where clause should be applied to
*/
private function _search($q)
{
// Be sure all the necessary elements exists
if (!empty($_REQUEST['searchField']) && !empty($_REQUEST['searchOper']) && !empty($_REQUEST['searchString'])) {
// Get the search criteria
$searchField = $_REQUEST['searchField'];
$searchOper = $_REQUEST['searchOper'];
$searchString = $_REQUEST['searchString'];
// Build an array to link jqgrid short hand to actual sql operators
$convertOper = array('bw' => 'LIKE ?', 'eq' => '= ?', 'lt' => '< ?', 'le' => '<= ?', 'gt' => '> ?', 'ge' => '>= ?', 'ew' => 'LIKE ?', 'cn' => 'LIKE ?');
// If we are building a LIKE clause then append a % where appropriate
switch ($searchOper) {
case 'cn':
$searchString = '%' . $searchString . '%';
break;
case 'ew':
$searchString = '%' . $searchString;
break;
case 'bw':
$searchString = $searchString . '%';
break;
}
// Add this where clause to the query
$q->addWhere($searchField . ' ' . $convertOper[$searchOper], $searchString);
}
}
/**
* Enable filters selected in customize action from user
*
* @param Doctrine_Query $query
* @param String $alias
* @return Doctrine_Query $query
*/
public static function filterOn($preference, Doctrine_Query $query, $alias = null)
{
switch ($preference) {
case 'activity':
$filtered_preferences = c2cPersonalization::getInstance()->getActivitiesFilter();
$log_msg = 'filtering on activities';
$query_string = self::getActivitiesQueryString($filtered_preferences);
break;
case 'language':
$filtered_preferences = c2cPersonalization::getInstance()->getLanguagesFilter();
$log_msg = 'filtering on languages';
$query_string = self::getLanguagesQueryString($filtered_preferences, $alias);
break;
case 'region':
$filtered_preferences = c2cPersonalization::getInstance()->getPlacesFilter();
$log_msg = 'filtering on regions';
$query_string = self::getAreasQueryString($filtered_preferences, $alias);
break;
default:
$filtered_preferences = array();
}
if (count($filtered_preferences) > 0) {
c2cTools::log($log_msg);
return $query->addWhere($query_string, $filtered_preferences);
}
return $query;
}
/**
*
* @param type $operationalCountryId
* @return Doctrine_Query
*/
protected function addOperationalCountryFilter(Doctrine_Query $query, $operationalCountryId)
{
if (is_null($operationalCountryId)) {
$query->addWhere('id = ?', WorkWeek::DEFAULT_WORK_WEEK_ID);
} else {
$query->where('operational_country_id = ?', $operationalCountryId);
}
return $query;
}
public function retrieveActiveJob(Doctrine_Query $query)
{
$query->addWhere('a.expires_at > ?', date('Y-m-d H:i:s', time() - 86400 * sfConfig::get('app_active_days')));
return $query->fetchOne();
}
protected function addDateQuery(Doctrine_Query $query, $field, $values)
{
$fieldName = $this->getFieldName($field);
if (isset($values['is_empty']) && $values['is_empty']) {
$query->addWhere('r.' . $fieldName . ' IS NULL');
} else {
$criterion = null;
if (!is_null($values['from']) && !is_null($values['to'])) {
$query->andWhere('r.' . $fieldName . ' >= ?', $values['from']);
$query->andWhere('r.' . $fieldName . ' <= ?', $values['to']);
} else {
if (!is_null($values['from'])) {
$query->andWhere('r.' . $fieldName . ' >= ?', $values['from']);
} else {
if (!is_null($values['to'])) {
$query->andWhere('r.' . $fieldName . ' <= ?', $values['to']);
}
}
}
}
}
public function addWhere($where, $params = array())
{
$fixed = self::_fixWhere($where, $params);
return parent::addWhere($fixed['where'], $fixed['params']);
}
/**
* make sure the given record is NOT returned by the query by adding its ID value(s) to the WHERE clause (where id != {this_id} or similar)
* NOTE: the query needs to be querying the same table as the record
* @param $query
* @param $record
* @return null
*/
private function restrictRecordFromQuery(Doctrine_Query &$query, Doctrine_Record &$record)
{
// restrict query to not return the row that we're working on. This prevents updates from triggering unnecessary nesting
$id = $record->identifier();
if (!$id) {
return;
}
$where_text = '(' . implode(' != ? OR ', array_keys($id)) . ' != ?)';
$query->addWhere($where_text, array_values($id));
}
