Collections functions
General functions
Render functions
Theme permission functions
User functions
Resource functions

do_report()

Description

do_report - Runs the specified report. This is used in a number of ways:-
1) Outputs an HTML table to screen ($download = false)
2) Produces a CSV
- for direct download from team_report.php
- captured and saved as a CSV file if called by send_periodic_report_emails() and over 100 rows are returned


results and replace the '[non_correlated_sql]' placeholder with the search query.

Parameters

ColumnTypeDefaultDescription
$ref int Report ID
$from_y mixed Start year (used for reprts with date placholders)
$from_m mixed Start month
$from_d mixed Start day
$to_y mixed End year
$to_m mixed To month
$to_d mixed To day
$download mixed true Output as CSV attachment (default)/output directly to client
$add_border mixed false Optional table border (not for download)
$foremail mixed false Sending as email?
$search_params array array Search parameters - {@see get_search_params()} - will run the report on the search

Return

void | string | array Outputs CSV file, returns HTML table or returns an array with path to the CSV file, rows and filename

Location

include/reporting_functions.php lines 60 to 306

Definition

 
function do_report($ref,$from_y,$from_m,$from_d,$to_y,$to_m,$to_d,$download=true,$add_border=false,$foremail=false, array $search_params=array())
    {
    
# Run report with id $ref for the date range specified. Returns a result array.
    
global $lang$baseurl$report_rows_attachment_limit;

    
$report ps_query("SELECT ref, `name`, `query`, support_non_correlated_sql FROM report WHERE ref = ?",array("i",$ref));

    if (
count($report) < 1)
        {
        return 
$lang['error_generic'];
        }

    
$has_date_range report_has_date($report[0]["query"]);
    
$report=$report[0];
    
$report['name'] = get_report_name($report);

    if(
$download || $foremail)
        {
        if (
$has_date_range)
            {
            
$filename=str_replace(array(" ","(",")","-","/",","),"_",$report["name"]) . "_" $from_y "_" $from_m "_" $from_d "_" $lang["to"] . "_" $to_y "_" $to_m "_" $to_d ".csv";
            }
        else
            {
            
$filename=str_replace(array(" ","(",")","-","/",","),"_",$report["name"]) . ".csv";
            }
        }

    if(
$results hook("customreport""", array($ref,$from_y,$from_m,$from_d,$to_y,$to_m,$to_d,$download,$add_border$report)))
        {
        
// Hook has created the $results array
        
}
    else
        {
        
// Generate report results normally
        
$sql_parameters = array();
        
$report_placeholders = [
            
'[from-y]' => $from_y,
            
'[from-m]' => $from_m,
            
'[from-d]' => $from_d,
            
'[to-y]' => $to_y,
            
'[to-m]' => $to_m,
            
'[to-d]' => $to_d,
        ];
        if((bool)
$report['support_non_correlated_sql'] === true && !empty($search_params))
            {
            
// If report supports being run on search results, embed the non correlated sql necessary to feed the report
            
$returned_search do_search(
                
$search_params['search'],
                
$search_params['restypes'],
                
$search_params['order_by'],
                
$search_params['archive'],
                -
1# fetchrows
                
$search_params['sort'],
                
false# access_override
                
DEPRECATED_STARSEARCH,
                
false# ignore_filters
                
false# return_disk_usage
                
$search_params['recentdaylimit'],
                
false# go
                
false# stats_logging
                
true# return_refs_only
                
false# editable_only
                
true # returnsql
            
);

            if(!
is_a($returned_search,"PreparedStatementQuery") || !is_string($returned_search->sql))
                {
                
debug("Invalid SQL returned by do_search(). Report cannot be generated");
                return 
"";
                }
            
$sql_parameters array_merge($sql_parameters$returned_search->parameters);
            
$report_placeholders[REPORT_PLACEHOLDER_NON_CORRELATED_SQL] = "(SELECT ncsql.ref FROM ({$returned_search->sql}) AS ncsql)";
            }

        
$sql report_process_query_placeholders($report['query'], $report_placeholders);
        
$results ps_query($sql,$sql_parameters);
        }
    
    
$resultcount count($results);
    if(
$resultcount == 0)
        {
        
// No point downloading as the resultant file will be empty
        
$download=false;
        }            
    if (
$download)
        {
        
header("Content-type: application/octet-stream");
        
header("Content-disposition: attachment; filename=\"" $filename "\"");
        }

    if (
$download || ($foremail && $resultcount $report_rows_attachment_limit))
        {
        if(
$foremail)
            {
            
ob_clean();
            
ob_start();
            }
        for (
$n=0;$n<$resultcount;$n++)
            {
            
$result=$results[$n];
            if (
$n==0)
                {
                
$f=0;
                foreach (
$result as $key => $value)
                    {
                    
$f++;
                    if (
$f>1) {echo ",";}
                    if (
$key!="thumbnail")
                        {echo 
"\"" lang_or_i18n_get_translated($key,"columnheader-") . "\"";}
                    }
                echo 
"\n";
                }
            
$f=0;
            foreach (
$result as $key => $value)
                {
                
$f++;
                if (
$f>1) {echo ",";}
                
$custom hook('customreportfield''', array($result$key$value$download));
                if (
$custom !== false)
                    {
                    echo 
$custom;
                    }
                else if (
$key!="thumbnail")
                    {
                    
$value=lang_or_i18n_get_translated($value"usergroup-");
                    
$value=str_replace('"','""',$value); # escape double quotes
                    
if (substr($value,0,1)==",") {$value=substr($value,1);} # Remove comma prefix on dropdown / checkbox values 
                    
echo "\"" $value  "\"";
                        
                    }
                }
            echo 
"\n";
            }

        if(
$foremail)
            {
            
$output ob_get_contents();
            
ob_end_clean();
            
$unique_id=uniqid();
            
$reportfile get_temp_dir(false"Reports") . "/Report_" $unique_id ".csv";
            
file_put_contents($reportfile,$output);
            return array(
"file" => $reportfile,"filename" => $filename"rows" => $resultcount);
            }
        }
    else
        {
        
# Not downloading - output a table

        // If report results are too big, display the first rows and notify user they should download it instead
        
$output '';
        if(
$resultcount $report_rows_attachment_limit)
            {
            
$results array_slice($results0$report_rows_attachment_limit);

            
// Catch the error now and place it above the table in the output
            
render_top_page_error_style($lang['team_report__err_report_too_long']);
            
$output ob_get_contents();
            
ob_clean();
            
ob_start();
            }

        
// Pre-render process: Process nodes search syntax (e.g @@228 or @@!223) and add a new column that contains the node list and their names
        
if(isset($results[0]['search_string']))
            {
            
$results process_node_search_syntax_to_names($results'search_string');
            }
        
$border="";
        if (
$add_border) {$border="border=\"1\"";}
        
$output .= "<br /><h2>" $report['name'] . "</h2><style>.InfoTable td {padding:5px;}</style><table $border class=\"InfoTable\">";
        for (
$n=0;$n<count($results);$n++)
            {
            
$result=$results[$n];
            if (
$n==0)
                {
                
$f=0;
                
$output.="<tr>\r\n";
                foreach (
$result as $key => $value)
                    {
                    
$f++;
                    if (
$key=="thumbnail")
                        {
$output.="<td><strong>Link</strong></td>\r\n";}
                    else
                        {
                        
$output.="<td><strong>" lang_or_i18n_get_translated($key,"columnheader-") . "</strong></td>\r\n";
                        }
                    }
                
$output.="</tr>\r\n";
                }
            
$f=0;
            
$output.="<tr>\r\n";
            foreach (
$result as $key => $value)
                {
                
$f++;
                if (
$key=="thumbnail")
                    {
                    
$thm_path=get_resource_path($value,true,"thm",false,"",$scramble=-1,$page=1,false);
                    if (!
file_exists($thm_path)){
                        
$resourcedata=get_resource_data($value);
                        if(
is_array($resourcedata))
                            {
                            
$thm_path sprintf(
                                
'%s/gfx/%s',
                                
dirname(__DIR__),
                                
get_nopreview_icon($resourcedata["resource_type"],$resourcedata["file_extension"],true)
                            );
                            }
                        else
                            {
                            
$thm_path dirname(__DIR__) . "/gfx/no_preview/resource_type/type1.png";
                            }
                        }
                    else
                        {
                        
$thm_path get_resource_path($value,true,"col",false,"",-1,1,false);
                        }

                    
$output.=sprintf(
                        
"<td><a href=\"%s/?r=%s\" target=\"_blank\"><img src=\"data:image/%s;base64,%s\"></a></td>\r\n",
                        
$baseurl,
                        
$value,
                        
pathinfo($thm_pathPATHINFO_EXTENSION),
                        
base64_encode(file_get_contents($thm_path))
                    );
                    }
                else
                    {
                    
$custom hook('customreportfield''', array($result$key$value$download));
                    if (
$custom !== false)
                        {
                        
$output .= $custom;
                        }
                    else
                        {
                        
$output.="<td>" strip_tags_and_attributes(lang_or_i18n_get_translated($value"usergroup-"),array("a"),array("href","target")) . "</td>\r\n";
                        }
                    }
                }
            
$output.="</tr>\r\n";
            }
        
$output.="</table>\r\n";
        if (
count($results)==0) {$output.=$lang["reportempty"];}
        return 
$output;
        }

    exit();
    }

This article was last updated 7th December 2023 19:35 Europe/London time based on the source file dated 29th November 2023 15:50 Europe/London time.