Coding standards
Security in ResourceSpace
Developer reference
Database
Action functions
Admin functions
Ajax functions
Annotation functions
API functions
Collections functions
Comment functions
Config functions
CSV export functions
Dash functions
Debug functions
Encryption functions
Facial recognition functions
File functions
General functions
Language functions
Log functions
Login functions
Message functions
Migration functions
Node functions
PDF functions
Plugin functions
Render functions
Reporting functions
Request functions
Research functions
Slideshow functions
Theme permission functions
User functions
Video functions
Database functions
Metadata functions
Resource functions
Search functions
Map functions
Job functions
Tab functions
Test 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 77 to 332

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);
        
        db_set_connection_mode("read_only");
        $results = ps_query($sql,$sql_parameters);
        db_clear_connection_mode();
        }
    
    $resultcount = count($results);
    if($resultcount == 0)
        {
        // No point downloading as the resultant file will be empty
        $download=false;
        }

    foreach($results as &$result) {
        foreach ($result as $key=>&$value) {
            # Merge translation strings if multiple in a single column
            if (substr($key,0,4)=="i18n") {
                $delimiter = substr($key,4,strpos($key,"_")-4);
                $value=implode("", i18n_merge_translations(explode($delimiter,(string)$value)));
            }
        }
        unset($value);
    }
    unset($result);

    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 (substr($key,0,4)=="i18n") {
                        $key = substr($key,strpos($key,"_")+1);
                    }
                    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;
                    }
                elseif ($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($results, 0, $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 {
                        if (substr($key,0,4)=="i18n") {
                            $key = substr($key,strpos($key,"_")+1);
                        }
                        $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,"",-1,1,false);
                    if (!file_exists($thm_path))
                        {
                        $thm_path = dirname(__DIR__) . "/gfx/no_preview/default.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_path, PATHINFO_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 4th December 2024 08:35 Europe/London time based on the source file dated 7th November 2024 17:40 Europe/London time.