Running a report with parameter populated from current detail view

enti · 25 February 2023 06:01

Hi,

I am new to using SuiteCRM (but not new to dev) and I am wanting to do something that conceptually should be quite simple but I cannot seem to find a way to do it.

I have a report with a parameter that is an id from a custom module, say A. When I am viewing the detail view of a record from module A I want to be able to run the report with the parameter set to the id of the current record. This would seem to be a fairly simple use case but I suspect I will have to resort to code to do it, which seems odd. Is there a way to run a report with a parameter populated from the current record?

I am using version 7.12.8

Thanks.

pgr · 25 February 2023 11:31

Hi, welcome to the Community!

You can do that with a PDF template for simple stuff, but it won’t have all the possibilities of a full report.

Then you run it from the detail view, action buttons “generate letter”.

enti · 26 February 2023 01:57

Thanks for the reply @pgr Following your lead I looked into it and have it kind of working.

However PDF Templates is just as clunky and requiring custom code to do it as well. It is a custom module so “generate letter” or “Print PDF” are not in the action buttons and I had to create a view.detail.php file to add it. Messy, and too involved for a simple thing.

In addition, I have a One-Many relationship that does not show in the dropdown while creating the PDF Template and so I cannot add the detail. The module was originally created in Module Builder and relationships defined there.

I have a report that works fine so I think trying to figure out how to open that report and set the parameter would be better. It is just so involved for something so basic…surely I am not the alone in wanting to open a report like this?

pgr · 26 February 2023 12:14

Actually I’ve been around here for a long time and I don’t remember anyone with a similar request… but I do agree with you, it makes sense and probably should be simple.

I would do it with enhanced PDF from my own add-on PowerReplacer.

But for cost-free alternatives, you could try something like this…

Reports are good at getting data from the database. If you figure out a clever way of putting your parameter there, just saving it as a user custom field, for example, and then the report would grab that and generate the result.

you’d have to do it in two steps - one, go write down the id in the database; two, go and generate the report. Then you can try adding a bit of custom code to do this automagically.

A second idea would be try and grab the record id from the “tracker” table, filtering by your user name and sorting for “latest modified or viewed”. I’m not sure what goes into the tracker table and what doesn’t, but try looking at that table’s contents as your browse the UI and figure it out.

enti · 27 February 2023 23:15

Ok, thanks.

I think I will just have to reset my expectations on what SuiteCRM can do out of the box, it appears that its reporting module is not very sophisticated. I am setting something up for a not for profit charitable trust so they dont have funds, otherwise PowerReplacer looks good.

pgr · 28 February 2023 10:33

There are more powerful reporting features with this free integration:

enti · 3 March 2023 11:29

Thanks, I had seen SuiteCRM Analytics and thought it could be useful for future projects (in previous roles I have worked as a Business Intelligence consultant and built data warehouses and reporting solutions).

Anyway, for this requirement I dug into the source code and did some experimenting and discovered an elegant and flexible solution using just SuiteCRM out of the box. I have spent far more time on it than is warranted for the problem at hand but the upshot is I now have a much better handle on how SuiteCRM works. I will write up how I solved this requirement for future reference after I have a bit of a break from it.

enti · 5 March 2023 03:47

@pgr and anyone else who may be interested or have a similar requirement, this is how I solved it. I created a custom controller to add an action and added some commands to the extended ViewDetail to trigger the new action. Here are the relevant code snippets…

The custom controller…

<?php

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class VYG_VoyagesController extends SugarController
{
    public function action_report(){
      //$GLOBALS['log']->debug("Report called with request: ".print_r($_REQUEST,1));
      //$GLOBALS['log']->debug('Bean: '.$this->bean->name);

      if (!isset($_POST['ReportName'])) {
          $GLOBALS['log']->error('No report specified');
          //SugarApplication::redirect('index.php');
      }
      else {
          $bean = BeanFactory::GetBean('AOR_Reports');
          $reportList = $bean->get_full_list("", "aor_reports.name='".$_POST['ReportName']."'");
          
          //if (count($reportList) > 0) { //This throws an error. TODO find out replacement check
              $report = $reportList[0];
              
              //setup the base command
              $cmd = "index.php?module=AOR_Reports&action=DownloadPDF&record={$report->id}";
              
              // Add conditions as parameters
              foreach ($report->get_linked_beans('aor_conditions', 'AOR_Conditions') as $key => $condition) {
                if (!$condition->parameter) {
                        continue;
                }

                $cmd = $cmd . "&parameter_id[]={$condition->id}";
                
                $cmd = $cmd . "&parameter_value[]=";
                if (property_exists($this->bean, $condition->field ))
                    $cmd .= $this->bean->{$condition->field};
                  else
                    $cmd .= $condition->value;
                    
                $cmd = $cmd . "&parameter_type[]={$condition->value_type}&parameter_operator[]={$condition->operator}";
              }

              SugarApplication::redirect($cmd);
          //}
      }
    }
}

and the extended ViewDetail

<?php
if (!defined('sugarEntry') || !sugarEntry) { die('Not A Valid Entry Point'); }

require_once('modules/ModuleBuilder/parsers/ParserFactory.php');

class VYG_VoyagesViewDetail extends ViewDetail
{
    public function __construct()
    {
        parent::__construct();
        $this->getReports();
    }
    
    public function getReports()
    {       
        $bean = BeanFactory::GetBean('AOR_Reports');//,$report_id);//Load Report
        $beanList = $bean->get_full_list("", "aor_reports.report_module='VYG_Voyages'");
        
        $parser = ParserFactory::getParser('detailview', 'VYG_Voyages');
        $buttonArray = &$parser->_viewdefs['templateMeta']['form']['buttons'];
        $changed = false;
        foreach ($beanList as $b ) {
            if (!array_key_exists($b->name, $buttonArray)) {

                $button = array (
                    'customCode' => '<input id="'.$b->name.'" title="'.$b->name.'" class="button" type="submit" form="formDetailView" name="ReportName" value="'.$b->name.'" onclick="var _form = document.getElementById(\'formDetailView\');  _form.action.value = \'Report\';">',
                    );
                $buttonArray[$b->name] = $button;
                $changed = true;
            }
        }
        
        if ($changed) {
            $parser->handleSave(false);
        }
    }
}

A critical bit was the customCode for the button, the input button needs to be set as a submit button and the form attribute set, this then auto populates a field key/value pair (called ReportName with a value set to the report name) which is added to the request and available to the controller. I could have used the report id but then this shows on the button. This works for multiple reports for the module. There is some cleaning up of the code and error checking that could be done but the basic idea is sound and may be of use to others. I also wanted to get it to modify the layout without saving it out as a custom layout but wasn’t able to get that bit going. As it is, if I delete a report then I will need to go into the custom layout and remove the button.

pgr · 5 March 2023 18:05

Nice work, thanks for sharing.

I didn’t check what’s inside the get_ful_list function, maybe that clears up possible attacks, but if it doesn’t, you should really check the way you’re using POST parameters to feed into SQL queries. There absolutely must be some escaping before that goes into the database, otherwise you get a new critical SQL injection vulnerability in your server…

enti · 7 March 2023 02:42

Ah, good point. I had a quick look in the implementation of get_full_list and it doesn’t look like it does any escaping/protection for malicious parameters so I definitely should, or I will rework it to use the report_id. Thanks.

enti · 8 March 2023 06:19

Just posting this for completeness.
I have cleaned up the code a little and changed the action to pass a new (dynamically created) hidden input that contains the report id. I also changed it so that it no longer uses the parser to create a custom php file but adds the action buttons dynamically.

The controller code…

<?php

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class VYG_VoyagesController extends SugarController
{
    public function action_report(){

      $userData = $_POST['userData'];
      if (!isset($userData)) {
          $GLOBALS['log']->error('No userData specified');
          //SugarApplication::redirect('index.php');
      }
      else {
          $reportId = $userData;
          $report = BeanFactory::GetBean('AOR_Reports', $reportId);   

          $cmd = "index.php?module=AOR_Reports&action=DownloadPDF&record={$report->id}";
          
          // Add conditions as parameters
          foreach ($report->get_linked_beans('aor_conditions', 'AOR_Conditions') as $key => $condition) {
            if (!$condition->parameter) {
                    continue;
            }

            $cmd = $cmd . "&parameter_id[]={$condition->id}";
            
            $cmd = $cmd . "&parameter_value[]=";
            if (property_exists($this->bean, $condition->field ))
                $cmd .= $this->bean->{$condition->field};
              else
                $cmd .= $condition->value;
                
            $cmd = $cmd . "&parameter_type[]={$condition->value_type}&parameter_operator[]={$condition->operator}";
        }

        SugarApplication::redirect($cmd);
      }
      //SugarApplication::redirect('index.php');
    }
}

and the view.detail…

<?php
if (!defined('sugarEntry') || !sugarEntry) {
    die('Not A Valid Entry Point');
}

class VYG_VoyagesViewDetail extends ViewDetail
{
    public function preDisplay() {
        parent::preDisplay();
        $this->getReports();
    }

    public function getReports()
    {
        $bean = BeanFactory::GetBean('AOR_Reports');
        $beanList = $bean->get_full_list("", "aor_reports.report_module='VYG_Voyages'");
        
        echo $script=<<<EOS
         <script>
         function newHiddenInput(p, name) {
            var input = document.createElement('input');
            input.type = 'hidden';             
            input.name = name;
            p.appendChild(input);
            return input;
         }
         
         function setFormAction(el, action, data) {
            el.form.action.value=action;
            var userData = el.form.userData || newHiddenInput(el.form, 'userData');
            userData.value = data;
         }
         </script>
EOS;

        $buttonArray = &$this->dv->defs['templateMeta']['form']['buttons'];

        foreach ($beanList as $b ) {
            if (!array_key_exists($b->name, $buttonArray)) {

                $button = array (
                    'customCode' => '{literal}<input id="'.$b->name.'" title="'.$b->name.'" class="button" type="submit" form="formDetailView" name="ReportName" value="'.$b->name.'" onclick=\'setFormAction(this, "Report","'.$b->id.'")\'>{/literal}',
                    );
                $buttonArray[$b->name] = $button;
            }
        } 
    }
}

pgr · 8 March 2023 10:25

It’s better, but you should still put anything that comes in the web request (even if it is a hidden post parameter) through a $db->quoted call before passing it on.

You can probably get the db object like this:

$reportId = $GLOBALS['db']->quoted($_POST['userData']);

enti · 8 March 2023 13:28

Ultimately you are probably correct, to be safe. But it may not be necessary in this case as I only use the passed value in a call to BeanFactory::GetBean which in turn eventually calls SugarBean::retrieve which does quote it as follows …

$query .= " WHERE $this->table_name.id = " . $this->db->quoted($id);

although it does go on to use (and store) it unquoted in subsequent code…so to be safe always quote.
Thanks.