Collections functions
General functions
Render functions
Theme permission functions
Resource functions

search_special()

Parameters

ColumnTypeDefaultDescription
$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

Location

include/search_functions.php lines 1189 to 1849

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;

    
// 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="SELECT DISTINCT r.hit_count score, $select FROM resource r " $sql_join->sql "
                    WHERE " 
$sql_filter->sql " AND file_checksum <> '' AND file_checksum IS NOT NULL 
                                      AND file_checksum = (SELECT file_checksum FROM resource WHERE ref=
$ref AND (file_checksum <> '' AND file_checksum IS NOT NULL) ) 
                    ORDER BY file_checksum, ref"
;   
                
$sql->parameters array_merge($sql_join->parameters,$sql_filter->parameters); 
                }
            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($search011) == '!collection')
        {
        global 
$userref,$ignore_collection_access;

        
$colcustperm $sql_join;
        
$colcustfilter $sql_filter// to avoid allowing this sql_filter to be modified by the $access_override search in the smart collection update below!!!
             
        # Special case if a key has been provided.
        
if($k != '')
            {
            
$sql_filter->sql 'r.ref > 0';
            
$sql_filter->parameters = [];
            }

        
# Extract the collection number
        
$collection explode(' '$search);
        
$collection str_replace('!collection'''$collection[0]);
        
$collection explode(','$collection); // just get the number
        
$collection = (int)$collection[0];

        
# 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'truefalse), '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[] = ($userref);
            
        if(
in_array($collection$validcollections) || 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,c.purchase_size,c.purchase_complete,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 (" $colcustfilter->sql ") GROUP BY r.ref ORDER BY $order_by$sql_suffix;
        
$sql->parameters array_merge($colcustperm->parameters,["i",$collection],$colcustfilter->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]);
        
$order_by=str_replace("r.","",$order_by); # UNION below doesn't like table aliases in the ORDER BY.
        
        
$sql->sql $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 (t.related=r.ref AND t.resource = ?) " $sql_join->sql " WHERE 1=1 AND " $sql_filter->sql " GROUP BY r.ref 
        UNION
        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 (t.resource=r.ref AND t.related= ?) " $sql_join->sql "  WHERE 1=1 AND " $sql_filter->sql " GROUP BY r.ref 
        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=1 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 $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=1 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" $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=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,7)=="!unused")
        {
        
// Search for resources not used in any collections
        
$sql->sql $sql_prefix "SELECT DISTINCT $select FROM resource r " $sql_join->sql " WHERE r.ref>0 AND r.ref NOT IN (SELECT c.resource FROM collection_resource c) AND " $sql_filter->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 "SELECT DISTINCT r.hit_count score, $select FROM resource r " $sql_join->sql $listsql->sql  " AND " $sql_filter->sql " ORDER BY " $order_by $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($propertyvalarray_keys($orientation_filters)))
                            {
                            break;
                            }
                        
$propertiessql->sql .= $sql_filter_properties_and .  $orientation_filters[$propertyval];
                    break;
                    }
                }
            }
        if(
$propertiessql->sql != "")
            {
            if(
strpos($sql_join->sql,"LEFT JOIN resource_dimensions rdim on r.ref=rdim.resource") === false)
                {
                
$sql_join->sql .=" LEFT 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 " $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);
        }
    else if(
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_sql1);

                
$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);
                
// printf('<br>$sql = %s', print_r($sql, true));
                
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,$fetchrows,0,$b_cache_count,$count_sql);
            
$resultcount $result["total"]  ?? 0;
            if (
$resultcount>&& 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 array_pad($result["data"],$resultcount,0);              
                }
            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 5th February 2023 08:05 Europe/London time based on the source file dated 23rd January 2023 09:10 Europe/London time.