search_special()
Description
Processes special searches and constructs a corresponding SQL query.This function handles various special search commands (like viewing the last resources,
resources with no downloads, duplicates, collections, etc.) and creates a prepared statement
for the query that retrieves the desired resources based on the search parameters.
It also incorporates user permissions and other configurations into the search logic.
Parameters
| Column | Type | Default | Description |
|---|---|---|---|
| $search | string | The search string indicating the type of special search. | |
| $sql_join | PreparedStatementQuery | The SQL JOIN query to be applied. | |
| $fetchrows | int | The number of rows to fetch. | |
| $sql_prefix | string | The prefix for the SQL query. | |
| $sql_suffix | string | The suffix for the SQL query. | |
| $order_by | string | The order by clause for sorting the results. | |
| $orig_order | string | The original order specified by the user. | |
| $select | string | The fields to select in the query. | |
| $sql_filter | PreparedStatementQuery | The SQL WHERE filter to apply. | |
| $archive | mixed | Archive states to filter by. | |
| $return_disk_usage | bool | Indicates whether to return disk usage information. | |
| $return_refs_only | bool | false | If true, returns only resource references. |
| $returnsql | bool | false | If true, returns the constructed SQL query instead of executing it. |
Return
| mixed | The results of the special search or false if no special search was matched. |
Location
include/search_functions.php lines 1265 to 1989
Definition
function search_special($search,$sql_join,$fetchrows,$sql_prefix,$sql_suffix,$order_by,$orig_order,$select,$sql_filter,$archive,$return_disk_usage,$return_refs_only=false, $returnsql=false)
{
# Process special searches. These return early with results.
global $FIXED_LIST_FIELD_TYPES, $lang, $k, $USER_SELECTION_COLLECTION, $date_field;
global $allow_smart_collections, $smart_collections_async;
global $config_search_for_number,$userref;
setup_search_chunks($fetchrows, $chunk_offset, $search_chunk_size);
// Don't cache special searches by default as often used for special purposes
// e.g. collection count to determine edit accesss
$b_cache_count = false;
if(!is_a($sql_join,"PreparedStatementQuery") && trim($sql_join == ""))
{
$sql_join = new PreparedStatementQuery();
}
if(!is_a($sql_filter,"PreparedStatementQuery") && trim($sql_filter == ""))
{
$sql_filter = new PreparedStatementQuery();
}
$sql = new PreparedStatementQuery();
# View Last
if (substr($search,0,5)=="!last")
{
# Replace r2.ref with r.ref for the alternative query used here.
$order_by=str_replace("r.ref","r2.ref",$order_by);
if ($orig_order=="relevance")
{
# Special case for ordering by relevance for this query.
$direction=((strpos($order_by,"DESC")===false)?"ASC":"DESC");
$order_by="r2.ref " . $direction;
}
# add date field, if access allowed, for use in $order_by
if(metadata_field_view_access($date_field) && strpos($select, "field" . $date_field) === false )
{
$select .= ", field{$date_field} ";
}
# Extract the number of records to produce
$last=explode(",",$search);
$last=str_replace("!last","",$last[0]);
# !Last must be followed by an integer. SQL injection filter.
if (is_int_loose($last))
{
$last=(int)$last;
}
else
{
$last=1000;
$search="!last1000";
}
# Fix the ORDER BY for this query (special case due to inner query)
$order_by=str_replace("r.rating","rating",$order_by);
$sql->sql = $sql_prefix . "SELECT DISTINCT *,r2.total_hit_count score FROM (SELECT $select FROM resource r " . $sql_join->sql . " WHERE " . $sql_filter->sql . " ORDER BY ref DESC LIMIT $last ) r2 ORDER BY $order_by" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
// View Resources With No Downloads
elseif (substr($search,0,12)=="!nodownloads")
{
if ($orig_order=="relevance") {$order_by="ref DESC";}
$sql->sql = $sql_prefix . "SELECT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE " . $sql_filter->sql . " AND r.ref NOT IN (SELECT DISTINCT object_ref FROM daily_stat WHERE activity_type='Resource download') GROUP BY r.ref ORDER BY $order_by" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
// Duplicate Resources (based on file_checksum)
elseif (substr($search,0,11)=="!duplicates")
{
// Extract the resource ID
$ref=explode(" ",$search);
$ref=str_replace("!duplicates","",$ref[0]);
$ref=explode(",",$ref); // just get the number
$ref=$ref[0];
if ($ref!="")
{
# Find duplicates of a given resource
if (is_int_loose($ref))
{
$sql->sql=sprintf(
"SELECT DISTINCT r.hit_count score, %s
FROM resource r %s
WHERE %s
AND file_checksum <> ''
AND file_checksum IS NOT NULL
AND file_checksum = (
SELECT file_checksum
FROM resource
WHERE ref= ?
AND (file_checksum <> '' AND file_checksum IS NOT NULL)
)
GROUP BY r.ref
ORDER BY file_checksum, ref",
$select,$sql_join->sql,$sql_filter->sql);
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters,["i",$ref]);
}
else
{
# Given resource is not a valid identifier
return array();
}
}
else
{
# Find all duplicate resources
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE " . $sql_filter->sql . " AND file_checksum IN (SELECT file_checksum FROM (SELECT file_checksum FROM resource WHERE file_checksum <> '' AND file_checksum IS NOT null GROUP BY file_checksum having count(file_checksum)>1)r2) ORDER BY file_checksum, ref" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
}
# View Collection
elseif (substr($search, 0, 11) == '!collection')
{
global $userref,$ignore_collection_access;
$colcustperm = $sql_join;
# Extract the collection number
$collection = explode(' ', $search);
$collection = str_replace('!collection', '', $collection[0]);
$collection = explode(',', $collection); // just get the number
$collection = (int)$collection[0];
if(!checkperm('a'))
{
# Check access
$validcollections = [];
if(upload_share_active() !== false)
{
$validcollections = get_session_collections(get_rs_session_id(), $userref);
}
else
{
$user_collections = array_column(get_user_collections($userref,"","name","ASC",-1,false), "ref");
$public_collections = array_column(search_public_collections('', 'name', 'ASC', true, false), 'ref');
# include collections of requested resources
$request_collections = array();
if (checkperm("R"))
{
include_once 'request_functions.php';
$request_collections = array_column(get_requests(), 'collection');
}
# include collections of research resources
$research_collections = array();
if (checkperm("r"))
{
include_once 'research_functions.php';
$research_collections = array_column(get_research_requests(), 'collection');
}
$validcollections = array_unique(array_merge($user_collections, array($USER_SELECTION_COLLECTION), $public_collections, $request_collections, $research_collections));
}
// Attach the negated user reference special collection
$validcollections[] = (0 - $userref);
if(in_array($collection, $validcollections) || (in_array($collection, array_column(get_all_featured_collections(), 'ref')) && featured_collection_check_access_control($collection)) || $ignore_collection_access)
{
if(!collection_readable($collection))
{
return array();
}
}
elseif($k == "" || upload_share_active() !== false)
{
return [];
}
}
if($allow_smart_collections)
{
global $smartsearch_ref_cache;
if(isset($smartsearch_ref_cache[$collection]))
{
$smartsearch_ref = $smartsearch_ref_cache[$collection]; // this value is pretty much constant
}
else
{
$smartsearch_ref = ps_value('SELECT savedsearch value FROM collection WHERE ref = ?',['i',$collection], '');
$smartsearch_ref_cache[$collection] = $smartsearch_ref;
}
global $php_path;
if($smartsearch_ref != '' && !$return_disk_usage)
{
if($smart_collections_async && isset($php_path) && file_exists($php_path . '/php'))
{
exec($php_path . '/php ' . dirname(__FILE__) . '/../pages/ajax/update_smart_collection.php ' . escapeshellarg($smartsearch_ref) . ' ' . '> /dev/null 2>&1 &');
}
else
{
update_smart_collection($smartsearch_ref);
}
}
}
$sql->sql = $sql_prefix . "SELECT DISTINCT c.date_added,c.comment,r.hit_count score,length(c.comment) commentset, $select FROM resource r join collection_resource c on r.ref=c.resource " . $colcustperm->sql . " WHERE c.collection = ? AND (" . $sql_filter->sql . ") GROUP BY r.ref ORDER BY $order_by" . $sql_suffix;
$sql->parameters = array_merge($colcustperm->parameters,["i",$collection],$sql_filter->parameters);
$collectionsearchsql=hook('modifycollectionsearchsql','',array($sql));
if($collectionsearchsql)
{
$sql=$collectionsearchsql;
}
}
# View Related - Pushed Metadata (for the view page)
elseif (substr($search,0,14)=="!relatedpushed")
{
# Extract the resource number
$resource=explode(" ",$search);$resource=str_replace("!relatedpushed","",$resource[0]);
if(isset($GLOBALS["related_pushed_order_by"]))
{
$related_order = is_int_loose($GLOBALS["related_pushed_order_by"]) ? "field" . $GLOBALS["related_pushed_order_by"] : $GLOBALS["related_pushed_order_by"];
$order_by = set_search_order_by($search, $related_order, "ASC");
}
$order_by=str_replace("r.","",$order_by); # UNION below doesn't like table aliases in the ORDER BY.
$relatedselect = $sql_prefix . "
SELECT DISTINCT r.hit_count score,rt.name resource_type_name, $select
FROM resource r
JOIN resource_type rt ON r.resource_type=rt.ref AND rt.push_metadata=1
JOIN resource_related t ON (%s) "
. $sql_join->sql
. " WHERE 1=1 AND " . $sql_filter->sql
. " GROUP BY r.ref";
$sql->sql = sprintf($relatedselect,"t.related=r.ref AND t.resource = ?")
. " UNION "
. sprintf($relatedselect,"t.resource=r.ref AND t.related= ?")
. " ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge(
["i",$resource],
$sql_join->parameters,
$sql_filter->parameters,
["i",$resource],
$sql_join->parameters,
$sql_filter->parameters);
}
# View Related
elseif (substr($search,0,8)=="!related")
{
# Extract the resource number
$resource=explode(" ",$search);$resource=str_replace("!related","",$resource[0]);
$order_by=str_replace("r.","",$order_by); # UNION below doesn't like table aliases in the ORDER BY.
global $pagename, $related_search_show_self;
$sql_self = new PreparedStatementQuery();
if ($related_search_show_self && $pagename == 'search')
{
$sql_self->sql = " SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.ref = ? AND " . $sql_filter->sql . " GROUP BY r.ref UNION ";
$sql_self->parameters = array_merge($sql_join->parameters,["i",$resource],$sql_filter->parameters);
}
$sql->sql = $sql_prefix . $sql_self->sql . "SELECT DISTINCT r.hit_count score, $select FROM resource r join resource_related t on (t.related=r.ref AND t.resource = ?) " . $sql_join->sql . " WHERE " . $sql_filter->sql . " GROUP BY r.ref
UNION
SELECT DISTINCT r.hit_count score, $select FROM resource r join resource_related t on (t.resource=r.ref AND t.related = ?) " . $sql_join->sql . " WHERE " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_self->parameters, ["i", $resource], $sql_join->parameters, $sql_filter->parameters, ["i", $resource], $sql_join->parameters, $sql_filter->parameters);
}
# Geographic search
elseif (substr($search,0,4)=="!geo")
{
$geo=explode("t",str_replace(array("m","p"),array("-","."),substr($search,4))); # Specially encoded string to avoid keyword splitting
if(!isset($geo[0]) || empty($geo[0]) || !isset($geo[1]) || empty($geo[1]))
{
exit($lang["geographicsearchmissing"]);
}
$bl=explode("b",$geo[0]);
$tr=explode("b",$geo[1]);
$sql->sql ="SELECT r.hit_count score, " . $select .
" FROM resource r " . $sql_join->sql .
"WHERE geo_lat > ? AND geo_lat < ? " .
"AND geo_long > ? AND geo_long < ?
AND " . $sql_filter->sql .
" GROUP BY r.ref
ORDER BY $order_by";
$sql->parameters = array_merge($sql_join->parameters,["d",$bl[0],"d",$tr[0],"d",$bl[1],"d",$tr[1]],$sql_filter->parameters);
$sql->sql = $sql_prefix . $sql->sql . $sql_suffix;
}
# Similar to a colour by key
elseif (substr($search,0,10)=="!colourkey")
{
# Extract the colour key
$colourkey=explode(" ",$search);$colourkey=str_replace("!colourkey","",$colourkey[0]);
$sql = new PreparedStatementQuery();
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE has_image > 0 AND LEFT(colour_key,4) = ? AND " . $sql_filter->sql . " GROUP BY r.ref" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,["s",$colourkey],$sql_filter->parameters);
}
# Colour search
elseif (substr($search,0,7)=="!colour")
{
$colour=explode(" ",$search);$colour=str_replace("!colour","",$colour[0]);
$sql = new PreparedStatementQuery();
$sql->sql ="SELECT r.hit_count score, " . $select .
" FROM resource r " . $sql_join->sql .
" WHERE colour_key LIKE ? " .
"OR colour_key LIKE ? " .
"AND " . $sql_filter->sql .
" GROUP BY r.ref
ORDER BY " . $order_by;
$sql->parameters = array_merge($sql_join->parameters,["s",$colour . "%","s","_" . $colour . "%"],$sql_filter->parameters);
$searchsql = $sql_prefix . $sql->sql . $sql_suffix;
$sql->sql = $searchsql;
}
# Similar to a colour
elseif (substr($search,0,4)=="!rgb")
{
$rgb=explode(":",$search);$rgb=explode(",",$rgb[1]);
$searchsql = new PreparedStatementQuery();
$searchsql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE has_image > 0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY (abs(image_red - ?)+abs(image_green - ?)+abs(image_blue - ?)) ASC LIMIT 500" . $sql_suffix;
$searchsql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters,["i",$rgb[0],"i",$rgb[1],"i",$rgb[2]]);
$sql = $searchsql;
}
elseif (substr($search,0,10)=="!nopreview")
{
$sql = new PreparedStatementQuery();
$sql->sql = $sql_prefix .
"SELECT DISTINCT r.hit_count score, $select
FROM resource r
$sql_join->sql
WHERE has_image=0
AND $sql_filter->sql
GROUP BY r.ref
ORDER BY $order_by"
. $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
elseif (($config_search_for_number && is_numeric($search)) || substr($search,0,9)=="!resource")
{
$searchref = preg_replace("/[^0-9]/","",$search);
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.ref = ? AND " . $sql_filter->sql . " GROUP BY r.ref" . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,["i",$searchref],$sql_filter->parameters);
}
elseif (substr($search,0,15)=="!archivepending")
{
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.archive=1 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
elseif (substr($search,0,12)=="!userpending")
{
if ($orig_order=="rating")
{
$order_by="request_count DESC," . $order_by;
}
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.archive=-1
AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
elseif (substr($search,0,14)=="!contributions")
{
global $userref;
# Extract the user ref
$cuser=explode(" ",$search);$cuser=str_replace("!contributions","",$cuser[0]);
// Don't filter if user is searching for their own resources and $open_access_for_contributor=true;
global $open_access_for_contributor;
if($open_access_for_contributor && $userref == $cuser)
{
$sql_filter->sql = "archive IN (" . ps_param_insert(count($archive)) . ")";
$sql_filter->parameters = ps_param_fill($archive, "i");
$sql_join->sql = " JOIN resource_type AS rty ON r.resource_type = rty.ref ";
$sql_join->parameters = array();
}
$select=str_replace(",rca.access group_access,rca2.access user_access ",",null group_access, null user_access ",$select);
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE created_by = ? AND r.ref > 0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, ["i", $cuser], $sql_filter->parameters);
}
elseif ($search=="!images")
{
// Search for resources with images
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE has_image>0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
elseif (substr($search,0,7)=="!unused")
{
// Search for resources not used in any collections
$sql->sql = $sql_prefix;
$sql->sql .= sprintf(
"SELECT DISTINCT %s
FROM resource r %s
WHERE r.ref>0
AND r.ref NOT IN (SELECT c.resource FROM collection_resource c)
AND %s
GROUP BY r.ref",
$select,$sql_join->sql,$sql_filter->sql);
$sql->sql .= $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
elseif (substr($search,0,5)=="!list")
{
// Search for a list of resources
// !listall = archive state is not applied as a filter to the list of resources.
$resources=explode(" ",$search);
if (substr($search,0,8)=="!listall")
{
$resources=str_replace("!listall","",$resources[0]);
}
else
{
$resources=str_replace("!list","",$resources[0]);
}
$resources=explode(",",$resources); // Separate out any additional keywords
$resources=array_filter(explode(":",$resources[0]),"is_int_loose");
$listsql = new PreparedStatementQuery();
if (count($resources)==0)
{
$listsql->sql = " WHERE r.ref IS NULL";
$listsql->parameters = [];
}
else
{
$listsql->sql = " WHERE r.ref IN (" . ps_param_insert(count($resources)) . ")";
$listsql->parameters = ps_param_fill($resources,"i");
}
$sql->sql = $sql_prefix;
$sql->sql .= sprintf(
"SELECT DISTINCT r.hit_count score, %s
FROM resource r %s%s
AND %s
GROUP BY r.ref
ORDER BY %s",
$select,$sql_join->sql,$listsql->sql,$sql_filter->sql,$order_by);
$sql->sql .= $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$listsql->parameters,$sql_filter->parameters);
}
elseif (substr($search,0,8)=="!hasdata")
{
// View resources that have data in the specified field reference - useful if deleting unused fields
$fieldref=intval(trim(substr($search,8)));
$sql_join->sql .=" RIGHT JOIN resource_node rn ON r.ref=rn.resource JOIN node n ON n.ref=rn.node WHERE n.resource_type_field = ?";
array_push($sql_join->parameters,"i",$fieldref);
// Cache this as it is a very slow query
$b_cache_count = true;
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
elseif (substr($search,0,11)=="!properties")
{
// Search for resource properties
// Note: in order to combine special searches with normal searches, these are separated by space (" ")
$searches_array = explode(' ', $search);
$properties = explode(';', substr($searches_array[0], 11));
// Use a new variable to ensure nothing changes $sql_filter unless this is a valid property search
$propertiessql = new PreparedStatementQuery();
foreach ($properties as $property)
{
$propertycheck=explode(":",$property);
if(count($propertycheck)==2)
{
$propertyname = $propertycheck[0];
$propertyval = $propertycheck[1];
$sql_filter_properties_and = $propertiessql->sql != "" ? " AND " : "";
switch($propertyname)
{
case "hmin":
$propertiessql->sql .= $sql_filter_properties_and . " rdim.height >= ?";
array_push($propertiessql->parameters,"i",$propertyval);
break;
case "hmax":
$propertiessql->sql .= $sql_filter_properties_and . " rdim.height <= ?";
array_push($propertiessql->parameters,"i",$propertyval);
break;
case "wmin":
$propertiessql->sql .= $sql_filter_properties_and . " rdim.width >= ?";
array_push($propertiessql->parameters,"i",$propertyval);
break;
case "wmax":
$propertiessql->sql .= $sql_filter_properties_and . " rdim.width <= ?";
array_push($propertiessql->parameters,"i",$propertyval);
break;
case "fmin":
// Need to convert MB value to bytes
$propertiessql->sql .= $sql_filter_properties_and . " r.file_size >= ?";
array_push($propertiessql->parameters,"i",floatval($propertyval) * 1024 * 1024);
break;
case "fmax":
// Need to convert MB value to bytes
$propertiessql->sql .= $sql_filter_properties_and . " r.file_size <= ?";
array_push($propertiessql->parameters,"i",floatval($propertyval) * 1024 * 1024);
break;
case "fext":
$propertyval=str_replace("*","%",$propertyval);
$propertiessql->sql .= $sql_filter_properties_and . " r.file_extension ";
if(substr($propertyval,0,1)=="-")
{
$propertyval = substr($propertyval,1);
$propertiessql->sql .=" NOT ";
}
if(substr($propertyval,0,1)==".")
{
$propertyval = substr($propertyval,1);
}
$propertiessql->sql .=" LIKE ?";
array_push($propertiessql->parameters,"s",$propertyval);
break;
case "pi":
$propertiessql->sql .= $sql_filter_properties_and . " r.has_image = ?";
array_push($propertiessql->parameters,"i",$propertyval);
break;
case "cu":
$propertiessql->sql .= $sql_filter_properties_and . " r.created_by = ?";
array_push($propertiessql->parameters,"i",$propertyval);
break;
case "orientation":
$orientation_filters = array(
"portrait" => "COALESCE(rdim.height, 0) > COALESCE(rdim.width, 0)",
"landscape" => "COALESCE(rdim.height, 0) < COALESCE(rdim.width, 0)",
"square" => "COALESCE(rdim.height, 0) = COALESCE(rdim.width, 0)",
);
if(!in_array($propertyval, array_keys($orientation_filters)))
{
break;
}
$propertiessql->sql .= $sql_filter_properties_and . $orientation_filters[$propertyval];
break;
}
}
}
if($propertiessql->sql != "")
{
if(strpos($sql_join->sql,"JOIN resource_dimensions rdim on r.ref=rdim.resource") === false)
{
$sql_join->sql .=" JOIN resource_dimensions rdim on r.ref=rdim.resource";
}
if ($sql_filter->sql == "")
{
$sql_filter->sql .= " WHERE " . $propertiessql->sql;
}
else
{
$sql_filter-> sql .= " AND " . $propertiessql->sql;
}
$sql_filter->parameters = array_merge($sql_filter->parameters,$propertiessql->parameters);
}
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE r.ref > 0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
elseif ($search=="!integrityfail")
{
// Search for resources where the file integrity has been marked as problematic or the file is missing
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE integrity_fail=1 AND no_file=0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
# Search for locked resources
elseif ($search=="!locked")
{
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource r " . $sql_join->sql . " WHERE lock_user<>0 AND " . $sql_filter->sql . " GROUP BY r.ref ORDER BY " . $order_by . $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters,$sql_filter->parameters);
}
elseif(preg_match('/^!report(\d+)(p[-1\d]+)?(d\d+)?(fy\d{4})?(fm\d{2})?(fd\d{2})?(ty\d{4})?(tm\d{2})?(td\d{2})?/i', $search, $report_search_data))
{
/*
View report as search results.
Special search "!report" can contain extra info for the reports' query period.
Syntax: !reportID[p?][d??][fy????][fm??][fd??][ty????][tm??][td??]
Where:
- ID is the actual report ref (mandatory)
- p is the selected period (see $reporting_periods_default config option)
- d is the period in specific number of days (p=0 in this case)
- fy,fm,fd (and their counter parts: ty,tm,td) represent a full date range (p=-1 in this case)
Examples for viewing as search results report #18:
- Last 7 days: !report18p7
- Last 23 days: !report18p0d23
- Between 2000-01-06 & 2023-03-16: !report18p-1fy2000fm01fd06ty2023tm03td16
*/
debug('[search_special] Running a "!report" search...');
$no_results_sql = new PreparedStatementQuery(
$sql_prefix . "SELECT DISTINCT r.hit_count score, {$select} FROM resource AS r "
. $sql_join->sql
. ' WHERE 1 = 2 AND ' . $sql_filter->sql
. ' GROUP BY r.ref ORDER BY ' . $order_by,
array_merge($sql_join->parameters, $sql_filter->parameters)
);
// Users with no access control to reports get no results back (ie []).
if(!checkperm('t'))
{
debug(sprintf('[WARNING][search_special][access control] User #%s attempted to run "%s" search without the right permissions', (int) $userref, $search));
$sql = $no_results_sql;
}
else
{
include_once 'reporting_functions.php';
$report_id = $report_search_data[1];
$all_reports = get_reports();
$reports_w_thumbnail = array_filter(array_column($all_reports, 'query', 'ref'), 'report_has_thumbnail');
$reports_w_support_non_correlated_sql = array_filter(array_column($all_reports, 'support_non_correlated_sql', 'ref'));
$reports = array_diff_key($reports_w_thumbnail, $reports_w_support_non_correlated_sql);
if(isset($reports[$report_id]))
{
$report = $reports[$report_id];
$report_period = [];
$report_period_info_idxs = range(2,9);
$report_period_info_names = array_combine($report_period_info_idxs, ['period', 'period_days', 'from-y', 'from-m', 'from-d', 'to-y', 'to-m', 'to-d']);
$report_period_info_lookups = array_combine($report_period_info_idxs, ['p', 'd', 'fy', 'fm', 'fd', 'ty', 'tm', 'td']);
foreach($report_period_info_names as $idx => $info_name)
{
if(!isset($report_search_data[$idx]))
{
continue;
}
$report_period[$info_name] = str_replace($report_period_info_lookups[$idx], '', $report_search_data[$idx]);
}
$period = report_process_period($report_period);
$report_sql = report_process_query_placeholders($report, [
'[from-y]' => $period['from_year'],
'[from-m]' => $period['from_month'],
'[from-d]' => $period['from_day'],
'[to-y]' => $period['to_year'],
'[to-m]' => $period['to_month'],
'[to-d]' => $period['to_day'],
]);
$report_sql = preg_replace('/;\s?/m', '', $report_sql, 1);
$sql->sql = $sql_prefix . "SELECT DISTINCT r.hit_count score, $select FROM resource AS r"
. " INNER JOIN ($report_sql) AS rsr ON rsr.thumbnail = r.ref "
. $sql_join->sql
. ' WHERE ' . $sql_filter->sql
. ' GROUP BY r.ref ORDER BY ' . $order_by
. $sql_suffix;
$sql->parameters = array_merge($sql_join->parameters, $sql_filter->parameters);
debug("[search_special] SQL = " . json_encode($sql));
}
else
{
debug("[search_special] Report #{$report_id} not found");
$sql = $no_results_sql;
}
}
}
# Within this hook implementation, set the value of the global $sql variable:
# Since there will only be one special search executed at a time, only one of the
# hook implementations will set the value. So, you know that the value set
# will always be the correct one (unless two plugins use the same !<type> value).
$hooksql = hook("addspecialsearch", "", array($search, $select, $sql_join , $sql_filter, $sql));
if(is_a($hooksql,'PreparedStatementQuery'))
{
debug("Addspecialsearch hook returned useful results.");
$hooksql->sql = $sql_prefix . $hooksql->sql . $sql_suffix;
$sql = $hooksql;
}
if($sql->sql != "")
{
if($returnsql)
{
return $sql;
}
else
{
$count_sql = clone $sql;
$count_sql->sql = str_replace("ORDER BY " . $order_by,"",$count_sql->sql);
$result = sql_limit_with_total_count($sql, $search_chunk_size, $chunk_offset, $b_cache_count, $count_sql);
if(is_array($fetchrows))
{
return $result;
}
$resultcount = $result["total"] ?? 0;
if($resultcount>0 && count($result["data"]) > 0)
{
if($return_refs_only)
{
// This needs to include archive and created_by columns too as often used to work out permission to edit collection
$result["data"] = array_map(function($val)
{
return [
"ref" =>$val["ref"],
"resource_type" =>$val["resource_type"],
"archive" =>$val["archive"],
"created_by" =>$val["created_by"],
"access" =>$val["access"],
];
}, $result["data"]
);
}
$return = $result['data'];
$resultcount -= count($return);
while($resultcount > 0)
{
$return = array_merge($return, array_pad([],($resultcount > 1000000?1000000:$resultcount),0));
$resultcount -= 1000000;
}
}
else
{
$return =[];
}
}
hook('beforereturnresults', '', array($result, $archive));
return $return;
}
# Arrived here? There were no special searches. Return false.
return false;
}
This article was last updated 12th December 2024 07:35 Europe/London time based on the source file dated 11th December 2024 15:55 Europe/London time.