Skip to main content

Custom Reports

Custom folders can be created using the following API.

SDK::setReportFolder($name, $description);

$name : the name of the folder
$description : the description of the folder

The created folder will be on the reports page and will be visible to all users and cannot be changed. The name and description can be translated with the translation API.

Folders created via API can be deleted with:

SDK::unsetReportFolder($name, $delreports = true);

$name : the name of the folder to be deleted
$delreports : if true it also deletes all reports (created via SDK) in that folder (files are not deleted)

Inside the folders created via API you can insert customized reports with the following statement:

SDK::setReport($name, $description, $foldername, $reportrun, $class, $jsfunction = '');

$name : the name of the report (it can be translated via API)
$description : the description of the report (it can be translated via API)
$foldername : the name of the folder created via API where to insert the report
$reportrun : the path of the php file that contains the class that generates the report
$class : the name of the class that handles the report
$jsfunction : the name of the javascript function to run when the "Generate Report" button is pressed

The report created via API can be deleted with:

SDK::unsetReport($name);

$name : the name of the report to delete (files are not deleted)

The class specified in $class must follow the following structure:

<?php
require_once('modules/Reports/ReportRun.php');

class ReportRunAccounts extends ReportRun {
	
	var $enableExportPdf = true;
	var $enableExportXls = true;
	var $enablePrint = true;
	var $hideParamsBlock = true;
	
	function __construct($reportid) {
		$this->reports = Reports::getInstance($reportid); // crmv@172034
		$this->reportid = $reportid;
		$this->primarymodule = 'Accounts';
		$this->reporttype = '';
		$this->reportname = 'Account con sito';
		$this->reportlabel = getTranslatedString($this->reportname, 'Reports');
	}
	
	function getSDKBlock() {
		global $mod_strings;
		$sdkblock = '<p><h2>Questo report mostra le aziende con sito</h2></p>';
		// here I can also add custom inputs to filter the report or any html I need
		return $sdkblock;
	}
	
	// overridden, always hide the summary tab
	function hasSummary() {
		return false;
	}
	
	// overridden, always show the total tab
	function hasTotals() {
		return true;
	}
	
	// generate the report
	function GenerateReport($outputformat = "", $filterlist = null, $directOutput=false) {
		global $adb;
		
		// compatibility, please use set them with the proper methods
		if (!empty($outputformat)) {
			$format = "HTML";
			$tab = "MAIN";
		
			if (strpos($outputformat, 'HTML') !== false) $format = "HTML";
			if (strpos($outputformat, 'PRINT') !== false) $format = "PRINT";
			if (strpos($outputformat, 'PDF') !== false) $format = "PDF";
			if (strpos($outputformat, 'XLS') !== false) $format = "XLS";
			if (strpos($outputformat, 'JSON') !== false) $format = "JSON";
			if (strpos($outputformat, 'CV') !== false) $format = "NULL";
			
			if (strpos($outputformat, 'COUNT') !== false) $tab = "COUNT";
			if (strpos($outputformat, 'TOTAL') !== false) $tab = "TOTAL";
			if (strpos($outputformat, 'CV') !== false) $tab = "CV";
			
			$this->setOutputFormat($format, $directOutput);
			$this->setReportTab($tab);
		} else {
			$format = $this->outputFormat;
			$tab = $this->reportTab;
		}
		
		$format = $this->outputFormat;
		$direct = $this->directOutput;
		$tab = $this->reportTab;

		// prepare the output class
		$output = $this->getOutputClass();
		$output->clearAll();
		
		$return_data = array();
	
		
		if ($tab == 'COUNT' && $this->hasSummary()) {

			// no summary for this custom report
			
		} elseif ($tab == 'CV') {
		
			// no customview for this report
		
		} elseif ($tab == 'MAIN') {

			$sSQL = $this->getReportQuery($outputformat, $filterlist);

			$result = $adb->query($sSQL);
			$this->total_count = $adb->num_rows($result);
			
			$error_msg = $adb->database->ErrorMsg();
			if(!$result && $error_msg!=''){
				// Performance Optimization: If direct output is requried
				if($direct) {
					echo getTranslatedString('LBL_REPORT_GENERATION_FAILED', 'Reports') . "<br>" . $error_msg;
					$error_msg = false;
				}
				// END
				return $error_msg;
			}
			
			if($result) {
			
				$this->generateHeader($result, $output);
			
				while ($row = $adb->fetchByAssoc($result)) {
					$colcount = count($row);
					foreach ($row as $column => $value) {
						$cell = array(
							'value' => $value,
							'column' => $column,
							'class' => 'rptData',
						);
						$output->addCell($cell);
					}
					$output->endCurrentRow();
					
				}
				
				$output->countTotal = $this->total_count;
				$output->countFiltered = $this->total_count;
				
				if ($format == 'XLS') {
					$head = $output->getSimpleHeaderArray();
					$data = $output->getSimpleDataArray();
					foreach ($data as $row) {
						$return_data[] = array_combine($head, $row);
					}
				} else {
					$return_data[] = $output->output(!$direct);
					$return_data[] = $this->total_count;
					$return_data[] = $sSQL;
					$return_data[] = $colcount;
				}
				
			}

		} elseif ($tab == "TOTAL" && $this->hasTotals()) {
			
			$output->addHeader(array('column' => 'fieldname', 'label' => getTranslatedString('Totals')));
			$output->addHeader(array('column' => 'sum', 'label' => getTranslatedString('SUM')));
			$output->addHeader(array('column' => 'avg', 'label' => getTranslatedString('AVG')));
			$output->addHeader(array('column' => 'min', 'label' => getTranslatedString('MIN')));
			$output->addHeader(array('column' => 'max', 'label' => getTranslatedString('MAX')));
			
			// fixed totals
			$rows = array(
				array(
					array('column'=> 'fieldname', 'value' => 'Fatturato totale', 'class' => 'rptData'),
					array('column'=> 'sum', 'value' => 2000, 'class' => 'rptTotal'),
					array('column'=> 'avg', 'value' => null, 'class' => 'rptTotal'),	// not used
					array('column'=> 'min', 'value' => 850, 'class' => 'rptTotal'),
					array('column'=> 'max', 'value' => null, 'class' => 'rptTotal'),	// not used
				),
			);
			
			// add them to the output class
			foreach ($rows as $row) {
				foreach ($row as $cell) {
					$output->addCell($cell);
				}
				$output->endCurrentRow();
			}
			
			// format for xls or html
			if ($format == "XLS") {
				
				// change the output array to match the expected format for XLS export
				$return_data = array();
				$data = $output->getSimpleDataArray();
				$fieldName = '';
				foreach ($data as $row) {
					$nrow = array();
					foreach ($row as $key => $value) {
						if ($key == 'fieldname') {
							$fieldName = $value;
							continue;
						}
						$klabel = $fieldName.'_'.strtoupper($key);
						$nrow[$klabel] = $value;
					}
					$return_data[] = $nrow;
				}
			
			} else {
				$return_data = $output->output(!$direct);
			}

		}
		
		return $return_data;
	}
	
	// generate a fixed header for the report
	function generateHeader($result, $output, $options = array()) {
		global $adb, $table_prefix;
		
		$module = 'Accounts';
		$tabid = getTabid($module);
		$count = $adb->num_fields($result);

		for ($x=0; $x<$count; ++$x) {
			$fld = $adb->field_name($result, $x);
			
			// get the field label from the column (if possible)
			$res = $adb->pquery("SELECT fieldlabel FROM {$table_prefix}_field WHERE columnname = ? and tabid = ?", array($fld->name, $tabid));
			if ($res && $adb->num_rows($res) > 0) {
				$fieldlabel = $adb->query_result_no_html($res, 0, 'fieldlabel');
				$headerLabel =  getTranslatedString($fieldlabel, $module);
			} else {
				$headerLabel = $fld->name;
			}
			$hcell = array(
				'column' => $fld->name,
				'label' => $headerLabel,
				'orderable' => false,
				'searchable' => false,
			);
			$output->addHeader($hcell);
		}
		
	}
	
	// generate the report query
	function getReportQuery($outputformat, $filterlist) {
		global $table_prefix;
		$query = 'SELECT accountid, accountname, website, phone FROM '.$table_prefix.'_account WHERE website <> "" ';
		return $query;
	}
}

The javascript function specified in $jsfunction must already be declared and return a string to be added to the request.

function preRunReport(id) {
	var params = "";
	var select = getObj('picklist1’);

	if (select) {
		params += "&picklist1="+select.options[select.selectedIndex].value;
	}

	var selectuser = getObj('picklist2’);
	if (selectuser) {
		params += "&picklist2="+selectuser.options[selectuser.selectedIndex].value;
	}

	return params;
}

QuandoWhen siupdating aggiorna una VTE allato versioneversion 16.09 (oor successiva)later), alcunesome funzionalitàfeatures deiof reportthe SDK vannoreports verificatemust inbe quanto sono cambiate alcune funzioni.
Se si aggiorna ad un VTE con revisione minore di 1576 è necessario riportare la patch identificata da crmv@140813 (files ReportRun.php e SDKSaveAndRun.php).verified.

UnaOne delleof partithe cambiatechanged èparts lais gestionethe deimanagement filtriof temporali,time che in precedenza si poteva modificare estendendo le funzioni getPrimaryStdFilterHTML e getSecondaryStdFilterHTMLfilters, chewhich orapreviously noncould vengonobe piùmodified usate.
Perby ottenereextending lothe stessogetPrimaryStdFilterHTML risultatoand bisognagetSecondaryStdFilterHTML estenderemethods, lawhich funzioneare no longer used. To obtain the same result, you need to extend the getStdFilterFields chefunction restituiscewhich unreturns an array diof campiavailable disponibili,fields, adto esempio:example:

<?php
function getStdFilterFields() {
	// See the method Reports::getStdFilterFields for the standard implementation
	
	// this example just loads standard fields for the Potential module
	$list = $this->reports->getStdFiltersFieldsListForChain(0, array('Potentials'));
	
	return $list;
}

QuandoWhen ilthe report vieneis generato,generated nonthe va più letta la variabile $filterlist, bensìvariable $this->stdfilters,stdfilters checontains contienethe iltime filtrofilter dato usare.be used.

SeIf lathe report query delis reportcompletely ècustomized, completamentethe personalizzata,generation vaof gestistathe manualmentetime anchefilter lamust generazionealso delbe filtromanaged temporale,manually, adfor esempioexample usandousing unaa funzionemethod comelike questa:this:

<?php
function addStdFilters() {
	global $current_user;
	$sql = '';
	
	if (is_array($this->stdfilters)) {
		foreach ($this->stdfilters as $flt) {
			if ($flt['fieldid'] > 0) {
				// get field informations
				$finfo = $this->getFieldInfoById($flt['fieldid']);
				$table = $finfo['tablename'];
				$qgen = QueryGenerator::getInstance($finfo['module'], $current_user);
				$operator = 'BETWEEN';
				if ($flt['value'] == 'custom') {
					$value = array($flt['startdate'], $flt['enddate']);
				} else {
					$cv = CRMEntity::getInstance('CustomView');
					$value = $cv->getDateforStdFilterBytype($flt['value']);
				}
				// adjust for timezone
				$value[0] = $this->fixDateTimeValue(
					$qgen, $finfo['fieldname'], $value[0]
				);
				$value[1] = $this→fixDateTimeValue(
					$qgen, $finfo['fieldname'], $value[1], false
				);
				// add the condition
				$sql .= ' AND '.$table.'.'.$finfo['columnname'].' '.
					$operator.' '.$value[0].' AND '.$value[1];
			}
		}
	}
	
	return $sql;
}

Hooks
modules/Reports/Listview.php
modules/SaveAndRun.php
modules/Reports/Reports.php
modules/Reports/CreatePDF.php
modules/Reports/CreateXL.php
modules/Reports/PrintReport.php
Smarty/templates/ReportContents.tpl
Smarty/templates/ReportRunContents.tpl
Smarty/templates/ReportRun.tpl

Back to top