public function doSearch($data, $form)
{
$list = $this->context->getQuery($data, false, false, $this->getSearchList());
$list = $list->subtract($this->grid->getList());
$list = new PaginatedList($list, $this->request);
$data = $this->customise(array('SearchForm' => $form, 'Items' => $list));
return $data->index();
}
public function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null)
{
$dataList = parent::getQuery($searchParams, $sort, $limit, $existingQuery);
$params = is_object($searchParams) ? $searchParams->getVars() : $searchParams;
$query = $dataList->dataQuery();
if (!is_object($searchParams)) {
if (isset($params['Locale']) && !empty($params['Locale'])) {
$query->where('"Locale" = \'' . Convert::raw2sql($params['Locale']) . '\'');
}
if (isset($params['Name']) && !empty($params['Name'])) {
$query->where('"FirstName" LIKE \'%' . Convert::raw2sql($params['Name']) . '%\' OR "Surname" LIKE \'%' . Convert::raw2sql($params['Name']) . '%\'');
}
if (isset($params['Status'])) {
$query->where('EXISTS ( SELECT 1 FROM "' . $this->modelClass . '_Statuses"
WHERE "' . $this->modelClass . 'ID" = "' . $this->modelClass . '"."ID"
AND "' . $this->modelClass . '_Statuses"."CustomerStatusID" IN (' . implode(',', $params['Status']) . ')
)');
}
if (isset($params['Tags'])) {
$query->where('EXISTS ( SELECT 1 FROM "' . $this->modelClass . '_Tags"
WHERE "' . $this->modelClass . 'ID" = "' . $this->modelClass . '"."ID"
AND "' . $this->modelClass . '_Tags"."CustomerTagID" IN (' . implode(',', $params['Tags']) . ')
)');
}
$this->extend('updateGetQuery', $query, $params);
}
return $dataList->setDataQuery($query);
}
/**
* Alter the existing SQL query object by adding some filters for the search
* so that the query finds Orders between two dates min and max.
*
* @see SearchContext::getQuery()
* @return SQLQuery Query with filters applied for search
*/
public function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null)
{
$query = parent::getQuery($searchParams, $sort, $limit, $existingQuery);
$searchParamArray = is_object($searchParams) ? $searchParams->getVars() : $searchParams;
foreach ($searchParamArray as $key => $value) {
if ($key == 'OrderedOn') {
$filter = $this->getFilter($key);
if ($filter && get_class($filter) == "DateRangeSearchFilter") {
$filter->setModel($this->modelClass);
preg_match('/([^\\s]*)(\\s-\\s(.*))?/i', $value, $matches);
$min_val = isset($matches[1]) ? $matches[1] : null;
$max_val = null;
if (isset($matches[3])) {
$max_val = date('Y-m-d', strtotime("+1 day", strtotime($matches[3])));
} elseif (isset($min_val)) {
$max_val = date('Y-m-d', strtotime("+1 day", strtotime($min_val)));
}
if ($min_val && $max_val) {
$filter->setMin($min_val);
$filter->setMax($max_val);
$filter->apply($query);
}
}
}
}
return $query;
}
public function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null) {
$query = parent::getQuery($searchParams, $sort, $limit, $existingQuery);
if (is_object($searchParams)) {
$searchParamArray = $searchParams->getVars();
}
else {
$searchParamArray = $searchParams;
}
foreach($searchParamArray as $key => $value) {
$min = (stristr($key,"_min") !== false);
if($min) {
$date_field = str_replace('_min','', $key);
if($filter = $this->getFilter($date_field)) {
if(get_class($filter) == "DateRangeFilter") {
$filter->setModel($this->modelClass);
$min_val = $searchParamArray[$date_field."_min"];
$max_val = $searchParamArray[$date_field."_max"];
if($min_val && $max_val) {
$filter->setMin($min_val);
$filter->setMax($max_val);
$filter->apply($query);
}
}
}
}
}
return $query;
}
public function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null)
{
$dataList = parent::getQuery($searchParams, $sort, $limit, $existingQuery);
$params = is_object($searchParams) ? $searchParams->getVars() : $searchParams;
$query = $dataList->dataQuery();
if (!is_object($searchParams)) {
if (isset($params['Search']) && !empty($params['Search'])) {
$customerClass = Config::inst()->get('PostmarkAdmin', 'member_class');
$strFilter = "'%" . Convert::raw2sql($params['Search']) . "%'";
$query->where("\n\t\t\t\t\tSubject LIKE {$strFilter}\n\t\t\t\t\tOR Message LIKE {$strFilter}\n\t\t\t\t\tOR FromCustomerID IN ( SELECT ID FROM {$customerClass} WHERE\n\t\t\t\t\t \tFirstName LIKE {$strFilter}\n\t\t\t\t\t \tOR Surname LIKE {$strFilter}\n\t\t\t\t\t \tOR Email LIKE {$strFilter}\n\t\t\t\t \t)\n\t\t\t\t \tOR ToID IN ( SELECT ID FROM {$customerClass} WHERE\n\t\t\t\t\t \tFirstName LIKE {$strFilter}\n\t\t\t\t\t \tOR Surname LIKE {$strFilter}\n\t\t\t\t\t \tOR Email LIKE {$strFilter}\n\t\t\t\t \t)\n\n\t\t\t\t");
}
}
return $dataList->setDataQuery($query);
}
public function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null)
{
$dataList = parent::getQuery($searchParams, $sort, $limit, $existingQuery);
$params = is_object($searchParams) ? $searchParams->getVars() : $searchParams;
if (!is_object($searchParams)) {
if (isset($params['Types'])) {
$dataList = $dataList->filter('ClassName', $params['Types']);
}
if (isset($params['Categories']) && is_array($params['Categories']) && !empty($params['Categories'])) {
$dataList = $dataList->where('EXISTS ( SELECT 1 FROM `NewsPost_Categories` np_c
WHERE `np_c`.`NewsCategoryID` IN (' . implode(',', $params['Categories']) . ')
AND `np_c`.`NewsPostID` = `NewsPost`.`ID`
LIMIT 1
)');
}
}
return $dataList;
}
/**
* Alter the existing SQL query object by adding some filters for the search
* so that the query finds objects between two dates min and max
*
* @param array $searchParams The search parameters
* @param mixed $sort The SQL sort statement
* @param mixed $limit The SQL limit statement
* @param mixed $existingQuery The existing query
*
* @return SQLQuery Query with filters applied for search
*
* @author Sascha Koehler <*****@*****.**>
* @since 11.03.2012
*/
public function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null)
{
$query = parent::getQuery($searchParams, $sort, $limit, $existingQuery);
$searchParamArray = is_object($searchParams) ? $searchParams->getVars() : $searchParams;
foreach ($searchParamArray as $fieldName => $value) {
if ($fieldName == 'Created') {
$filter = $this->getFilter($fieldName);
if ($filter && get_class($filter) == "DateRangeSearchFilter") {
$min_val = null;
$max_val = null;
$filter->setModel($this->modelClass);
if (strpos($value, '-') === false) {
$min_val = $value;
} else {
preg_match('/([^\\s]*)(\\s-\\s(.*))?/i', $value, $matches);
$min_val = isset($matches[1]) ? $matches[1] : null;
if (isset($matches[3])) {
$max_val = $matches[3];
}
}
if ($min_val && $max_val) {
$filter->setMin($min_val);
$filter->setMax($max_val);
$query = $query->alterDataQuery(array($filter, 'apply'));
} else {
if ($min_val) {
$filter->setMin($min_val);
$query = $query->alterDataQuery(array($filter, 'apply'));
} else {
if ($max_val) {
$filter->setMax($max_val);
$query = $query->alterDataQuery(array($filter, 'apply'));
}
}
}
}
}
}
return $query;
}
