/modules/Reports/Reports.php

Large files files are truncated, but you can click here to view the full file

<?php
/*********************************************************************************
** The contents of this file are subject to the vtiger CRM Public License Version 1.0
 * ("License"); You may not use this file except in compliance with the License
 * The Original Code is:  vtiger CRM Open Source
 * The Initial Developer of the Original Code is vtiger.
 * Portions created by vtiger are Copyright (C) vtiger.
 * All Rights Reserved.
*
 ********************************************************************************/
require_once('include/database/PearDatabase.php');
require_once('data/CRMEntity.php');
require_once('include/utils/UserInfoUtil.php');
require_once 'modules/Reports/ReportUtils.php';
global $calpath;
global $app_strings,$mod_strings;
global $app_list_strings;
global $modules;
global $blocks;
global $adv_filter_options;
global $log;

global $report_modules;
global $related_modules;
global $old_related_modules;

$adv_filter_options = array("e"=>"equals",
		            "n"=>"not equal to",
			    "s"=>"starts with",
			    "ew"=>"ends with",
			    "c"=>"contains",
			    "k"=>"does not contain",
			    "l"=>"less than",
			    "g"=>"greater than",
			    "m"=>"less or equal",
			    "h"=>"greater or equal",
			    "bw"=>"between",
			    "a"=>"after",
			    "b"=>"before",
			   );

//$report_modules = Array('Faq','Rss','Portal','Recyclebin','Emails','Reports','Dashboard','Home','Activities'
	//	       );

$old_related_modules = Array('Accounts'=>Array('Potentials','Contacts','Products','Quotes','Invoice'),
			 'Contacts'=>Array('Accounts','Potentials','Quotes','PurchaseOrder'),
			 'Potentials'=>Array('Accounts','Contacts','Quotes'),
			 'Calendar'=>Array('Leads','Accounts','Contacts','Potentials'),
			 'Products'=>Array('Accounts','Contacts'),
			 'HelpDesk'=>Array('Products'),
			 'Quotes'=>Array('Accounts','Contacts','Potentials'),
			 'PurchaseOrder'=>Array('Contacts'),
			 'Invoice'=>Array('Accounts'),
			 'Campaigns'=>Array('Products'),
			);

$related_modules =Array();

class Reports extends CRMEntity{



	/**
	 * This class has the informations for Reports and inherits class CRMEntity and
	 * has the variables required to generate,save,restore vtiger_reports
	 * and also the required functions for the same
	 * Contributor(s): ______________________________________..
	 */


	var $srptfldridjs;

	var $column_fields = Array();

	var $sort_fields = Array();
	var $sort_values = Array();

	var $id;
	var $mode;
	var $mcount;

	var $startdate;
	var $enddate;

	var $ascdescorder;

	var $stdselectedfilter;
	var $stdselectedcolumn;

	var $primodule;
	var $secmodule;
	var $columnssummary;
	var $is_editable;
	var $reporttype;
	var $reportname;
	var $reportdescription;
	var $folderid;
	var $module_blocks;

	var $pri_module_columnslist;
	var $sec_module_columnslist;

	var $advft_criteria;
	var $adv_rel_fields = Array();

	var $module_list = Array();

	/** Function to set primodule,secmodule,reporttype,reportname,reportdescription,folderid for given vtiger_reportid
	 *  This function accepts the vtiger_reportid as argument
	 *  It sets primodule,secmodule,reporttype,reportname,reportdescription,folderid for the given vtiger_reportid
	 */

	function Reports($reportid="")
	{
		global $adb,$current_user,$theme,$mod_strings;
		$this->initListOfModules();
		if($reportid != "")
		{
			// Lookup information in cache first
			$cachedInfo = VTCacheUtils::lookupReport_Info($current_user->id, $reportid);
			$subordinate_users = VTCacheUtils::lookupReport_SubordinateUsers($reportid);

			if($cachedInfo === false) {
				$ssql = "select vtiger_reportmodules.*,vtiger_report.* from vtiger_report inner join vtiger_reportmodules on vtiger_report.reportid = vtiger_reportmodules.reportmodulesid";
				$ssql .= " where vtiger_report.reportid = ?";
				$params = array($reportid);

				require_once('include/utils/GetUserGroups.php');
				require('user_privileges/user_privileges_'.$current_user->id.'.php');
				$userGroups = new GetUserGroups();
				$userGroups->getAllUserGroups($current_user->id);
				$user_groups = $userGroups->user_groups;
				if(!empty($user_groups) && $is_admin==false){
					$user_group_query = " (shareid IN (".generateQuestionMarks($user_groups).") AND setype='groups') OR";
					array_push($params, $user_groups);
				}

				$non_admin_query = " vtiger_report.reportid IN (SELECT reportid from vtiger_reportsharing WHERE $user_group_query (shareid=? AND setype='users'))";
				if($is_admin==false){
					$ssql .= " and ( (".$non_admin_query.") or vtiger_report.sharingtype='Public' or vtiger_report.owner = ? or vtiger_report.owner in(select vtiger_user2role.userid from vtiger_user2role inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid where vtiger_role.parentrole like '".$current_user_parent_role_seq."::%'))";
					array_push($params, $current_user->id);
					array_push($params, $current_user->id);
				}

				$query = $adb->pquery("select userid from vtiger_user2role inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid where vtiger_role.parentrole like '".$current_user_parent_role_seq."::%'",array());
				$subordinate_users = Array();
				for($i=0;$i<$adb->num_rows($query);$i++){
					$subordinate_users[] = $adb->query_result($query,$i,'userid');
				}

				// Update subordinate user information for re-use
				VTCacheUtils::updateReport_SubordinateUsers($reportid, $subordinate_users);

				$result = $adb->pquery($ssql, $params);
				if($result && $adb->num_rows($result)) {
					$reportmodulesrow = $adb->fetch_array($result);

					// Update information in cache now
					VTCacheUtils::updateReport_Info(
						$current_user->id, $reportid, $reportmodulesrow["primarymodule"],
						$reportmodulesrow["secondarymodules"], $reportmodulesrow["reporttype"],
						$reportmodulesrow["reportname"], $reportmodulesrow["description"],
						$reportmodulesrow["folderid"], $reportmodulesrow["owner"]
					);
				}

				// Re-look at cache to maintain code-consistency below
				$cachedInfo = VTCacheUtils::lookupReport_Info($current_user->id, $reportid);
			}

			if($cachedInfo) {
				$this->primodule = $cachedInfo["primarymodule"];
				$this->secmodule = $cachedInfo["secondarymodules"];
				$this->reporttype = $cachedInfo["reporttype"];
				$this->reportname = decode_html($cachedInfo["reportname"]);
				$this->reportdescription = decode_html($cachedInfo["description"]);
				$this->folderid = $cachedInfo["folderid"];
				if($is_admin==true || in_array($cachedInfo["owner"],$subordinate_users) || $cachedInfo["owner"]==$current_user->id)
					$this->is_editable = 'true';
				else
					$this->is_editable = 'false';
			} else {
				if($_REQUEST['mode'] != 'ajax')
				{
					include('modules/Vtiger/header.php');
				}
				echo "<table border='0' cellpadding='5' cellspacing='0' width='100%' height='450px'><tr><td align='center'>";
				echo "<div style='border: 3px solid rgb(153, 153, 153); background-color: rgb(255, 255, 255); width: 80%; position: relative; z-index: 10000000;'>

				<table border='0' cellpadding='5' cellspacing='0' width='98%'>
				<tbody><tr>
				<td rowspan='2' width='11%'><img src='". vtiger_imageurl('denied.gif', $theme) ."' ></td>
				<td style='border-bottom: 1px solid rgb(204, 204, 204);' nowrap='nowrap' width='70%'><span class='genHeaderSmall'>You are not allowed to View this Report </span></td>
				</tr>
				<tr>
				<td class='small' align='right' nowrap='nowrap'>
				<a href='javascript:window.history.back();'>$app_strings[LBL_GO_BACK]</a><br>								   		     </td>
				</tr>
				</tbody></table>
				</div>";
				echo "</td></tr></table>";
				break;

			}
		}
	}

	// Update the module list for listing columns for report creation.
	function updateModuleList($module) {
		global $adb;
		if (!isset($module)) return;
		require_once('include/utils/utils.php');
		$tabid = getTabid($module);
		if ($module == 'Calendar') {
			$tabid = array(9, 16);
		}
		$sql = "SELECT blockid, blocklabel FROM vtiger_blocks WHERE tabid IN (". generateQuestionMarks($tabid) .")";
		$res = $adb->pquery($sql, array($tabid));
		$noOfRows = $adb->num_rows($res);
		if ($noOfRows <= 0) return;
		for($index = 0; $index < $noOfRows; ++$index) {
			$blockid = $adb->query_result($res,$index,'blockid');
			if(in_array($blockid, $this->module_list[$module])) continue;
			$blockid_list[] = $blockid;
			$blocklabel = $adb->query_result($res,$index,'blocklabel');
			$this->module_list[$module][$blocklabel] = $blockid;
		}
	}

	// Initializes the module list for listing columns for report creation.
	function initListOfModules() {
		global $adb, $current_user, $old_related_modules;

		$restricted_modules = array('Emails','Events','Webmails');
		$restricted_blocks = array('LBL_IMAGE_INFORMATION','LBL_COMMENTS','LBL_COMMENT_INFORMATION');

		$this->module_id = array();
		$this->module_list = array();

		// Prefetch module info to check active or not and also get list of tabs
		$modulerows = vtlib_prefetchModuleActiveInfo(false);

		$cachedInfo = VTCacheUtils::lookupReport_ListofModuleInfos();

		if($cachedInfo !== false) {
			$this->module_list = $cachedInfo['module_list'];
			$this->related_modules = $cachedInfo['related_modules'];

		} else {

			if($modulerows) {
				foreach($modulerows as $resultrow) {
					if($resultrow['presence'] == '1') continue;      // skip disabled modules
					if($resultrow['isentitytype'] != '1') continue;  // skip extension modules
					if(in_array($resultrow['name'], $restricted_modules)) { // skip restricted modules
						continue;
					}
					if($resultrow['name']!='Calendar'){
						$this->module_id[$resultrow['tabid']] = $resultrow['name'];
					} else {
						$this->module_id[9] = $resultrow['name'];
						$this->module_id[16] = $resultrow['name'];

					}
					$this->module_list[$resultrow['name']] = array();
				}

				$moduleids = array_keys($this->module_id);
				$reportblocks =
					$adb->pquery("SELECT blockid, blocklabel, tabid FROM vtiger_blocks WHERE tabid IN (" .generateQuestionMarks($moduleids) .")",
						array($moduleids));
				$prev_block_label = '';
				if($adb->num_rows($reportblocks)) {
					while($resultrow = $adb->fetch_array($reportblocks)) {
						$blockid = $resultrow['blockid'];
						$blocklabel = $resultrow['blocklabel'];
						$module = $this->module_id[$resultrow['tabid']];

						if(in_array($blocklabel, $restricted_blocks) ||
							in_array($blockid, $this->module_list[$module]) ||
							isset($this->module_list[$module][getTranslatedString($blocklabel,$module)])
						) {
							continue;
						}

						if(!empty($blocklabel)){
							if($module == 'Calendar' && $blocklabel == 'LBL_CUSTOM_INFORMATION')
								$this->module_list[$module][$blockid] = getTranslatedString($blocklabel,$module);
							else
								$this->module_list[$module][$blockid] = getTranslatedString($blocklabel,$module);
							$prev_block_label = $blocklabel;
						} else {
							$this->module_list[$module][$blockid] = getTranslatedString($prev_block_label,$module);
						}
					}
				}

				$relatedmodules = $adb->pquery(
					"SELECT vtiger_tab.name, vtiger_relatedlists.tabid FROM vtiger_tab
					INNER JOIN vtiger_relatedlists on vtiger_tab.tabid=vtiger_relatedlists.related_tabid
					WHERE vtiger_tab.isentitytype=1
					AND vtiger_tab.name NOT IN(".generateQuestionMarks($restricted_modules).")
					AND vtiger_tab.presence = 0 AND vtiger_relatedlists.label!='Activity History'
					UNION
					SELECT module, vtiger_tab.tabid FROM vtiger_fieldmodulerel
					INNER JOIN vtiger_tab on vtiger_tab.name = vtiger_fieldmodulerel.relmodule
					WHERE vtiger_tab.isentitytype = 1
					AND vtiger_tab.name NOT IN(".generateQuestionMarks($restricted_modules).")
					AND vtiger_tab.presence = 0",
					array($restricted_modules,$restricted_modules)
				);
				if($adb->num_rows($relatedmodules)) {
					while($resultrow = $adb->fetch_array($relatedmodules)) {
						$module = $this->module_id[$resultrow['tabid']];

						if(!isset($this->related_modules[$module])) {
							$this->related_modules[$module] = array();
						}

						if($module != $resultrow['name']) {
							$this->related_modules[$module][] = $resultrow['name'];
						}

						// To achieve Backward Compatability with Report relations
						if(isset($old_related_modules[$module])){

							$rel_mod = array();
							foreach($old_related_modules[$module] as $key=>$name){
								if(vtlib_isModuleActive($name) && isPermitted($name,'index','')){
									$rel_mod[] = $name;
								}
							}
							if(!empty($rel_mod)){
								$this->related_modules[$module] = array_merge($this->related_modules[$module],$rel_mod);
								$this->related_modules[$module] = array_unique($this->related_modules[$module]);
							}
						}
					}
				}
				// Put the information in cache for re-use
				VTCacheUtils::updateReport_ListofModuleInfos($this->module_list, $this->related_modules);
			}
		}
	}
	// END


	/** Function to get the Listview of Reports
	 *  This function accepts no argument
	 *  This generate the Reports view page and returns a string
	 *  contains HTML
	 */

	function sgetRptFldr($mode='')
	{

		global $adb,$log,$mod_strings;
		$returndata = Array();
		$sql = "select * from vtiger_reportfolder order by folderid";
		$result = $adb->pquery($sql, array());
		$reportfldrow = $adb->fetch_array($result);
		if($mode != '')
		{
			// Fetch detials of all reports of folder at once
			$reportsInAllFolders = $this->sgetRptsforFldr(false);

			do
			{
				if($reportfldrow["state"] == $mode)
				{
					$details = Array();
					$details['state'] = $reportfldrow["state"];
					$details['id'] = $reportfldrow["folderid"];
					$details['name'] = ($mod_strings[$reportfldrow["foldername"]] == '' ) ? $reportfldrow["foldername"]:$mod_strings[$reportfldrow["foldername"]];
					$details['description'] = $reportfldrow["description"];
					$details['fname'] = popup_decode_html($details['name']);
					$details['fdescription'] = popup_decode_html($reportfldrow["description"]);
					$details['details'] = $reportsInAllFolders[$reportfldrow["folderid"]];
					$returndata[] = $details;
				}
			}while($reportfldrow = $adb->fetch_array($result));
		}else
		{
			do
			{
				$details = Array();
				$details['state'] = $reportfldrow["state"];
				$details['id'] = $reportfldrow["folderid"];
				$details['name'] = ($mod_strings[$reportfldrow["foldername"]] == '' ) ? $reportfldrow["foldername"]:$mod_strings[$reportfldrow["foldername"]];
				$details['description'] = $reportfldrow["description"];
				$details['fname'] = popup_decode_html($details['name']);
				$details['fdescription'] = popup_decode_html($reportfldrow["description"]);
				$returndata[] = $details;
			}while($reportfldrow = $adb->fetch_array($result));
		}

		$log->info("Reports :: ListView->Successfully returned vtiger_report folder HTML");
		return $returndata;
	}

	/** Function to get the Reports inside each modules
	 *  This function accepts the folderid
	 *  This Generates the Reports under each Reports module
	 *  This Returns a HTML sring
	 */

	function sgetRptsforFldr($rpt_fldr_id)
	{
		$srptdetails="";
		global $adb;
		global $log;
		global $mod_strings,$current_user;
		$returndata = Array();

		require_once('include/utils/UserInfoUtil.php');

		$sql = "select vtiger_report.*, vtiger_reportmodules.*, vtiger_reportfolder.folderid from vtiger_report inner join vtiger_reportfolder on vtiger_reportfolder.folderid = vtiger_report.folderid";
		$sql .= " inner join vtiger_reportmodules on vtiger_reportmodules.reportmodulesid = vtiger_report.reportid";

		$params = array();

		// If information is required only for specific report folder?
		if($rpt_fldr_id !== false) {
			$sql .= " where vtiger_reportfolder.folderid=?";
			$params[] = $rpt_fldr_id;
		}

		require('user_privileges/user_privileges_'.$current_user->id.'.php');
		require_once('include/utils/GetUserGroups.php');
		$userGroups = new GetUserGroups();
		$userGroups->getAllUserGroups($current_user->id);
		$user_groups = $userGroups->user_groups;
		if(!empty($user_groups) && $is_admin==false){
			$user_group_query = " (shareid IN (".generateQuestionMarks($user_groups).") AND setype='groups') OR";
			array_push($params, $user_groups);
		}

		$non_admin_query = " vtiger_report.reportid IN (SELECT reportid from vtiger_reportsharing WHERE $user_group_query (shareid=? AND setype='users'))";
		if($is_admin==false){
			$sql .= " and ( (".$non_admin_query.") or vtiger_report.sharingtype='Public' or vtiger_report.owner = ? or vtiger_report.owner in(select vtiger_user2role.userid from vtiger_user2role inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid where vtiger_role.parentrole like '".$current_user_parent_role_seq."::%'))";
			array_push($params, $current_user->id);
			array_push($params, $current_user->id);
		}
		$query = $adb->pquery("select userid from vtiger_user2role inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid where vtiger_role.parentrole like '".$current_user_parent_role_seq."::%'",array());
		$subordinate_users = Array();
		for($i=0;$i<$adb->num_rows($query);$i++){
			$subordinate_users[] = $adb->query_result($query,$i,'userid');
		}
		$result = $adb->pquery($sql, $params);

		$report = $adb->fetch_array($result);
		if(count($report)>0)
		{
			do
			{
				$report_details = Array();
				$report_details ['customizable'] = $report["customizable"];
				$report_details ['reportid'] = $report["reportid"];
				$report_details ['primarymodule'] = $report["primarymodule"];
				$report_details ['secondarymodules'] = $report["secondarymodules"];
				$report_details ['state'] = $report["state"];
				$report_details ['description'] = $report["description"];
				$report_details ['reportname'] = $report["reportname"];
				$report_details ['sharingtype'] = $report["sharingtype"];
				if($is_admin==true || in_array($report["owner"],$subordinate_users) || $report["owner"]==$current_user->id)
					$report_details ['editable'] = 'true';
				else
					$report_details['editable'] = 'false';

				if(isPermitted($report["primarymodule"],'index') == "yes")
					$returndata [$report["folderid"]][] = $report_details;
			}while($report = $adb->fetch_array($result));
		}

		if($rpt_fldr_id !== false) {
			$returndata = $returndata[$rpt_fldr_id];
		}

		$log->info("Reports :: ListView->Successfully returned vtiger_report details HTML");
		return $returndata;
	}

	/** Function to get the array of ids
	 *  This function forms the array for the ExpandCollapse
	 *  Javascript
	 *  It returns the array of ids
	 *  Array('1RptFldr','2RptFldr',........,'9RptFldr','10RptFldr')
	 */

	function sgetJsRptFldr()
	{
		$srptfldr_js = "var ReportListArray=new Array(".$this->srptfldridjs.")
			setExpandCollapse()";
		return $srptfldr_js;
	}

	/** Function to set the Primary module vtiger_fields for the given Report
	 *  This function sets the primary module columns for the given Report
	 *  It accepts the Primary module as the argument and set the vtiger_fields of the module
	 *  to the varialbe pri_module_columnslist and returns true if sucess
	 */

	function getPriModuleColumnsList($module)
	{
		//$this->updateModuleList($module);
		foreach($this->module_list[$module] as $key=>$value)
		{
			$temp = $this->getColumnsListbyBlock($module,$key);
			if(!empty($ret_module_list[$module][$value])){
				if(!empty($temp)){
					$ret_module_list[$module][$value] = array_merge($ret_module_list[$module][$value],$temp);
				}
			} else {
				$ret_module_list[$module][$value] = $this->getColumnsListbyBlock($module,$key);
			}
		}
		$this->pri_module_columnslist = $ret_module_list;
		return true;
	}

	/** Function to set the Secondary module fileds for the given Report
	 *  This function sets the secondary module columns for the given module
	 *  It accepts the module as the argument and set the vtiger_fields of the module
	 *  to the varialbe sec_module_columnslist and returns true if sucess
	 */

	function getSecModuleColumnsList($module)
	{
		if($module != "")
		{
			$secmodule = explode(":",$module);
			for($i=0;$i < count($secmodule) ;$i++)
			{
				//$this->updateModuleList($secmodule[$i]);
				if($this->module_list[$secmodule[$i]]){
					$this->sec_module_columnslist[$secmodule[$i]] = $this->getModuleFieldList(
							$secmodule[$i]);
					if($this->module_list[$secmodule[$i]] == 'Calendar') {
						if($this->module_list['Events']){
							$this->sec_module_columnslist['Events'] = $this->getModuleFieldList(
									'Events');
						}
					}
				}
			}
		}
		return true;
	}

	/**
	 *
	 * @param String $module
	 * @param type $blockIdList
	 * @param Array $currentFieldList
	 * @return Array
	 */
	public function getBlockFieldList($module, $blockIdList, $currentFieldList) {
		if(!empty($currentFieldList)){
			$temp = $this->getColumnsListbyBlock($module,$blockIdList);
			if(!empty($temp)){
				$currentFieldList = array_merge($currentFieldList,$temp);
			}
		} else {
			$currentFieldList = $this->getColumnsListbyBlock($module,$blockIdList);
		}
		return $currentFieldList;
	}

	public function getModuleFieldList($module) {
		foreach($this->module_list[$module] as $key=>$value) {
			$ret_module_list[$module][$value] = $this->getBlockFieldList(
					$module, $key, $ret_module_list[$module][$value]);
		}
		return $ret_module_list[$module];
	}

	/** Function to get vtiger_fields for the given module and block
	 *  This function gets the vtiger_fields for the given module
	 *  It accepts the module and the block as arguments and
	 *  returns the array column lists
	 *  Array module_columnlist[ vtiger_fieldtablename:fieldcolname:module_fieldlabel1:fieldname:fieldtypeofdata]=fieldlabel
	 */

	function getColumnsListbyBlock($module,$block)
	{
		global $adb;
		global $log;
		global $current_user;

		if(is_string($block)) $block = explode(",", $block);

		$tabid = getTabid($module);
		if ($module == 'Calendar') {
			$tabid = array('9','16');
		}
		$params = array($tabid, $block);

		require('user_privileges/user_privileges_'.$current_user->id.'.php');
		//Security Check
		if($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] ==0)
		{
			$sql = "select * from vtiger_field where vtiger_field.tabid in (". generateQuestionMarks($tabid) .") and vtiger_field.block in (". generateQuestionMarks($block) .") and vtiger_field.displaytype in (1,2,3) and vtiger_field.presence in (0,2) ";

			//fix for Ticket #4016
			if($module == "Calendar")
				$sql.=" group by vtiger_field.fieldlabel order by sequence";
			else
			$sql.=" order by sequence";
		}
		else
		{

			$profileList = getCurrentUserProfileList();
			$sql = "select * from vtiger_field inner join vtiger_profile2field on vtiger_profile2field.fieldid=vtiger_field.fieldid inner join vtiger_def_org_field on vtiger_def_org_field.fieldid=vtiger_field.fieldid where vtiger_field.tabid in (". generateQuestionMarks($tabid) .")  and vtiger_field.block in (". generateQuestionMarks($block) .") and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.presence in (0,2)";
			if (count($profileList) > 0) {
				$sql .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")";
				array_push($params, $profileList);
			}

			//fix for Ticket #4016
			if($module == "Calendar")
				$sql.=" group by vtiger_field.fieldlabel order by sequence";
			else
				$sql.=" group by vtiger_field.fieldid order by sequence";
		}

		$result = $adb->pquery($sql, $params);
		$noofrows = $adb->num_rows($result);
		for($i=0; $i<$noofrows; $i++)
		{
			$fieldtablename = $adb->query_result($result,$i,"tablename");
			$fieldcolname = $adb->query_result($result,$i,"columnname");
			$fieldname = $adb->query_result($result,$i,"fieldname");
			$fieldtype = $adb->query_result($result,$i,"typeofdata");
			$uitype = $adb->query_result($result,$i,"uitype");
			$fieldtype = explode("~",$fieldtype);
			$fieldtypeofdata = $fieldtype[0];

			//Here we Changing the displaytype of the field. So that its criteria will be displayed correctly in Reports Advance Filter.
			$fieldtypeofdata=ChangeTypeOfData_Filter($fieldtablename,$fieldcolname,$fieldtypeofdata);

			if($uitype == 68 || $uitype == 59)
			{
				$fieldtypeofdata = 'V';
			}
			if($fieldtablename == "vtiger_crmentity")
			{
				$fieldtablename = $fieldtablename.$module;
			}
			if($fieldname == "assigned_user_id")
			{
				$fieldtablename = "vtiger_users".$module;
				$fieldcolname = "user_name";
			}
			if($fieldname == "assigned_user_id1")
			{
				$fieldtablename = "vtiger_usersRel1";
				$fieldcolname = "user_name";
			}

			$fieldlabel = $adb->query_result($result,$i,"fieldlabel");
			$fieldlabel1 = str_replace(" ","_",$fieldlabel);
			$optionvalue = $fieldtablename.":".$fieldcolname.":".$module."_".$fieldlabel1.":".$fieldname.":".$fieldtypeofdata;
			$this->adv_rel_fields[$fieldtypeofdata][] = '$'.$module.'#'.$fieldname.'$'."::".getTranslatedString($module,$module)." ".getTranslatedString($fieldlabel,$module);
			//added to escape attachments fields in Reports as we have multiple attachments
                        if($module != 'HelpDesk' || $fieldname !='filename')
				$module_columnlist[$optionvalue] = $fieldlabel;
		}
		$blockname = getBlockName($block);
		if($blockname == 'LBL_RELATED_PRODUCTS' && ($module=='PurchaseOrder' || $module=='SalesOrder' || $module=='Quotes' || $module=='Invoice')){
			$fieldtablename = 'vtiger_inventoryproductrel';
			$fields = array('productid'=>getTranslatedString('Product Name',$module),
							'serviceid'=>getTranslatedString('Service Name',$module),
							'listprice'=>getTranslatedString('List Price',$module),
							'discount'=>getTranslatedString('Discount',$module),
							'quantity'=>getTranslatedString('Quantity',$module),
							'comment'=>getTranslatedString('Comments',$module),
			);
			$fields_datatype = array('productid'=>'V',
							'serviceid'=>'V',
							'listprice'=>'I',
							'discount'=>'I',
							'quantity'=>'I',
							'comment'=>'V',
			);
			foreach($fields as $fieldcolname=>$label){
				$fieldtypeofdata = $fields_datatype[$fieldcolname];
				$optionvalue =  $fieldtablename.":".$fieldcolname.":".$module."_".$label.":".$fieldcolname.":".$fieldtypeofdata;
				$module_columnlist[$optionvalue] = $label;
			}
		}
		$log->info("Reports :: FieldColumns->Successfully returned ColumnslistbyBlock".$module.$block);
		return $module_columnlist;
	}

	/** Function to set the standard filter vtiger_fields for the given vtiger_report
	 *  This function gets the standard filter vtiger_fields for the given vtiger_report
	 *  and set the values to the corresponding variables
	 *  It accepts the repordid as argument
	 */

	function getSelectedStandardCriteria($reportid) {
		global $adb;
		$sSQL = "select vtiger_reportdatefilter.* from vtiger_reportdatefilter inner join vtiger_report on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid where vtiger_report.reportid=?";
		$result = $adb->pquery($sSQL, array($reportid));
		$selectedstdfilter = $adb->fetch_array($result);

		$this->stdselectedcolumn = $selectedstdfilter["datecolumnname"];
		$this->stdselectedfilter = $selectedstdfilter["datefilter"];

		if($selectedstdfilter["datefilter"] == "custom") {
			if($selectedstdfilter["startdate"] != "0000-00-00") {
				$startDateTime = new DateTimeField($selectedstdfilter["startdate"].' '. date('H:i:s'));
				$this->startdate = $startDateTime->getDisplayDate();
			}
			if($selectedstdfilter["enddate"] != "0000-00-00") {
				$endDateTime = new DateTimeField($selectedstdfilter["enddate"].' '. date('H:i:s'));
				$this->enddate = $endDateTime->getDisplayDate();
			}
		}
	}

	/** Function to get the combo values for the standard filter
	 *  This function get the combo values for the standard filter for the given vtiger_report
	 *  and return a HTML string
	 */

	function getSelectedStdFilterCriteria($selecteddatefilter = "")
	{
		global $mod_strings;

		$datefiltervalue = Array("custom","prevfy","thisfy","nextfy","prevfq","thisfq","nextfq",
				"yesterday","today","tomorrow","lastweek","thisweek","nextweek","lastmonth","thismonth",
				"nextmonth","last7days","last30days", "last60days","last90days","last120days",
				"next30days","next60days","next90days","next120days"
				);

		$datefilterdisplay = Array("Custom","Previous FY", "Current FY","Next FY","Previous FQ","Current FQ","Next FQ","Yesterday",
				"Today","Tomorrow","Last Week","Current Week","Next Week","Last Month","Current Month",
				"Next Month","Last 7 Days","Last 30 Days","Last 60 Days","Last 90 Days","Last 120 Days",
				"Next 7 Days","Next 30 Days","Next 60 Days","Next 90 Days","Next 120 Days"
				);

		for($i=0;$i<count($datefiltervalue);$i++)
		{
			if($selecteddatefilter == $datefiltervalue[$i])
			{
				$sshtml .= "<option selected value='".$datefiltervalue[$i]."'>".$mod_strings[$datefilterdisplay[$i]]."</option>";
			}else
			{
				$sshtml .= "<option value='".$datefiltervalue[$i]."'>".$mod_strings[$datefilterdisplay[$i]]."</option>";
			}
		}

		return $sshtml;
	}

	/** Function to get the selected standard filter columns
	 *  This function returns the selected standard filter criteria
	 *  which is selected for vtiger_reports as an array
	 *  Array stdcriteria_list[fieldtablename:fieldcolname:module_fieldlabel1]=fieldlabel
	 */

	function getStdCriteriaByModule($module)
	{
		global $adb;
		global $log;
		global $current_user;
		require('user_privileges/user_privileges_'.$current_user->id.'.php');

		$tabid = getTabid($module);
		foreach($this->module_list[$module] as $key=>$blockid)
		{
			$blockids[] = $blockid;
		}
		$blockids = implode(",",$blockids);

		$params = array($tabid, $blockids);
		if($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] == 0)
		{
			//uitype 6 and 23 added for start_date,EndDate,Expected Close Date
			$sql = "select * from vtiger_field where vtiger_field.tabid=? and (vtiger_field.uitype =5 or vtiger_field.uitype = 6 or vtiger_field.uitype = 23 or vtiger_field.displaytype=2) and vtiger_field.block in (". generateQuestionMarks($block) .") and vtiger_field.presence in (0,2) order by vtiger_field.sequence";
		}
		else
		{
			$profileList = getCurrentUserProfileList();
			$sql = "select * from vtiger_field inner join vtiger_tab on vtiger_tab.tabid = vtiger_field.tabid inner join vtiger_profile2field on vtiger_profile2field.fieldid=vtiger_field.fieldid inner join vtiger_def_org_field on vtiger_def_org_field.fieldid=vtiger_field.fieldid  where vtiger_field.tabid=? and (vtiger_field.uitype =5 or vtiger_field.displaytype=2) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.block in (". generateQuestionMarks($block) .") and vtiger_field.presence in (0,2)";
			if (count($profileList) > 0) {
				$sql .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")";
				array_push($params, $profileList);
			}
			$sql .= " order by vtiger_field.sequence";
		}

		$result = $adb->pquery($sql, $params);

		while($criteriatyperow = $adb->fetch_array($result))
		{
			$fieldtablename = $criteriatyperow["tablename"];
			$fieldcolname = $criteriatyperow["columnname"];
			$fieldlabel = $criteriatyperow["fieldlabel"];

			if($fieldtablename == "vtiger_crmentity")
			{
				$fieldtablename = $fieldtablename.$module;
			}
			$fieldlabel1 = str_replace(" ","_",$fieldlabel);
			$optionvalue = $fieldtablename.":".$fieldcolname.":".$module."_".$fieldlabel1;
			$stdcriteria_list[$optionvalue] = $fieldlabel;
		}

		$log->info("Reports :: StdfilterColumns->Successfully returned Stdfilter for".$module);
		return $stdcriteria_list;

	}

	/** Function to form a javascript to determine the start date and end date for a standard filter
	 *  This function is to form a javascript to determine
	 *  the start date and End date from the value selected in the combo lists
	 */

	function getCriteriaJS()
	{

		$todayDateTime = new DateTimeField(date('Y-m-d H:i:s'));

		$tomorrow  = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+1, date("Y")));
		$tomorrowDateTime = new DateTimeField($tomorrow.' '. date('H:i:s'));

		$yesterday  = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-1, date("Y")));
		$yesterdayDateTime = new DateTimeField($yesterday.' '. date('H:i:s'));

		$currentmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m"), "01",   date("Y")));
		$currentMonthStartDateTime = new DateTimeField($currentmonth0.' '. date('H:i:s'));
		$currentmonth1 = date("Y-m-t");
		$currentMonthEndDateTime = new DateTimeField($currentmonth1.' '. date('H:i:s'));

		$lastmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")-1, "01",   date("Y")));
		$lastMonthStartDateTime = new DateTimeField($lastmonth0.' '. date('H:i:s'));
		$lastmonth1 = date("Y-m-t", strtotime("-1 Month"));
		$lastMonthEndDateTime = new DateTimeField($lastmonth1.' '. date('H:i:s'));

		$nextmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")+1, "01",   date("Y")));
		$nextMonthStartDateTime = new DateTimeField($nextmonth0.' '. date('H:i:s'));
		$nextmonth1 = date("Y-m-t", strtotime("+1 Month"));
		$nextMonthEndDateTime = new DateTimeField($nextmonth1.' '. date('H:i:s'));

		$lastweek0 = date("Y-m-d",strtotime("-2 week Sunday"));
		$lastWeekStartDateTime = new DateTimeField($lastweek0.' '. date('H:i:s'));
		$lastweek1 = date("Y-m-d",strtotime("-1 week Saturday"));
		$lastWeekEndDateTime = new DateTimeField($lastweek1.' '. date('H:i:s'));

		$thisweek0 = date("Y-m-d",strtotime("-1 week Sunday"));
		$thisWeekStartDateTime = new DateTimeField($thisweek0.' '. date('H:i:s'));
		$thisweek1 = date("Y-m-d",strtotime("this Saturday"));
		$thisWeekEndDateTime = new DateTimeField($thisweek1.' '. date('H:i:s'));

		$nextweek0 = date("Y-m-d",strtotime("this Sunday"));
		$nextWeekStartDateTime = new DateTimeField($nextweek0.' '. date('H:i:s'));
		$nextweek1 = date("Y-m-d",strtotime("+1 week Saturday"));
		$nextWeekEndDateTime = new DateTimeField($nextweek1.' '. date('H:i:s'));

		$next7days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+6, date("Y")));
		$next7DaysDateTime = new DateTimeField($next7days.' '. date('H:i:s'));

		$next30days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+29, date("Y")));
		$next30DaysDateTime = new DateTimeField($next30days.' '. date('H:i:s'));

		$next60days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+59, date("Y")));
		$next60DaysDateTime = new DateTimeField($next60days.' '. date('H:i:s'));

		$next90days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+89, date("Y")));
		$next90DaysDateTime = new DateTimeField($next90days.' '. date('H:i:s'));

		$next120days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+119, date("Y")));
		$next120DaysDateTime = new DateTimeField($next120days.' '. date('H:i:s'));

		$last7days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-6, date("Y")));
		$last7DaysDateTime = new DateTimeField($last7days.' '. date('H:i:s'));

		$last30days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-29, date("Y")));
		$last30DaysDateTime = new DateTimeField($last30days.' '. date('H:i:s'));

		$last60days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-59, date("Y")));
		$last60DaysDateTime = new DateTimeField($last60days.' '. date('H:i:s'));

		$last90days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-89, date("Y")));
		$last90DaysDateTime = new DateTimeField($last90days.' '. date('H:i:s'));

		$last120days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-119, date("Y")));
		$last120DaysDateTime = new DateTimeField($last120days.' '. date('H:i:s'));

		$currentFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01",   date("Y")));
		$currentFYStartDateTime = new DateTimeField($currentFY0.' '. date('H:i:s'));
		$currentFY1 = date("Y-m-t",mktime(0, 0, 0, "12", date("d"),   date("Y")));
		$currentFYEndDateTime = new DateTimeField($currentFY1.' '. date('H:i:s'));

		$lastFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01",   date("Y")-1));
		$lastFYStartDateTime = new DateTimeField($lastFY0.' '. date('H:i:s'));
		$lastFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")-1));
		$lastFYEndDateTime = new DateTimeField($lastFY1.' '. date('H:i:s'));

		$nextFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01",   date("Y")+1));
		$nextFYStartDateTime = new DateTimeField($nextFY0.' '. date('H:i:s'));
		$nextFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")+1));
		$nextFYEndDateTime = new DateTimeField($nextFY1.' '. date('H:i:s'));

		if(date("m") <= 3) {
			$cFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y")));
			$cFqStartDateTime = new DateTimeField($cFq.' '. date('H:i:s'));
			$cFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y")));
			$cFqEndDateTime = new DateTimeField($cFq1.' '. date('H:i:s'));

			$nFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y")));
			$nFqStartDateTime = new DateTimeField($nFq.' '. date('H:i:s'));
			$nFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y")));
			$nFqEndDateTime = new DateTimeField($nFq1.' '. date('H:i:s'));

			$pFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y")-1));
			$pFqStartDateTime = new DateTimeField($pFq.' '. date('H:i:s'));
			$pFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y")-1));
			$pFqEndDateTime = new DateTimeField($pFq1.' '. date('H:i:s'));

		} else if(date("m") > 3 and date("m") <= 6) {

			$pFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y")));
			$pFqStartDateTime = new DateTimeField($pFq.' '. date('H:i:s'));
			$pFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y")));
			$pFqEndDateTime = new DateTimeField($pFq1.' '. date('H:i:s'));

			$cFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y")));
			$cFqStartDateTime = new DateTimeField($cFq.' '. date('H:i:s'));
			$cFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y")));
			$cFqEndDateTime = new DateTimeField($cFq1.' '. date('H:i:s'));

			$nFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y")));
			$nFqStartDateTime = new DateTimeField($nFq.' '. date('H:i:s'));
			$nFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y")));
			$nFqEndDateTime = new DateTimeField($nFq1.' '. date('H:i:s'));

		} else if(date("m") > 6 and date("m") <= 9) {

			$nFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y")));
			$nFqStartDateTime = new DateTimeField($nFq.' '. date('H:i:s'));
			$nFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y")));
			$nFqEndDateTime = new DateTimeField($nFq1.' '. date('H:i:s'));

			$pFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y")));
			$pFqStartDateTime = new DateTimeField($pFq.' '. date('H:i:s'));
			$pFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y")));
			$pFqEndDateTime = new DateTimeField($pFq1.' '. date('H:i:s'));

			$cFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y")));
			$cFqStartDateTime = new DateTimeField($cFq.' '. date('H:i:s'));
			$cFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y")));
			$cFqEndDateTime = new DateTimeField($cFq1.' '. date('H:i:s'));

		} else if(date("m") > 9 and date("m") <= 12) {
			$nFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y")+1));
			$nFqStartDateTime = new DateTimeField($nFq.' '. date('H:i:s'));
			$nFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y")+1));
			$nFqEndDateTime = new DateTimeField($nFq1.' '. date('H:i:s'));

			$pFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y")));
			$pFqStartDateTime = new DateTimeField($pFq.' '. date('H:i:s'));
			$pFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y")));
			$pFqEndDateTime = new DateTimeField($pFq1.' '. date('H:i:s'));

			$cFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y")));
			$cFqStartDateTime = new DateTimeField($cFq.' '. date('H:i:s'));
			$cFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y")));
			$cFqEndDateTime = new DateTimeField($cFq1.' '. date('H:i:s'));
		}

		$sjsStr = '<script language="JavaScript" type="text/javaScript">
			function showDateRange( type ) {
				if (type!="custom") {
					document.NewReport.startdate.readOnly=true
					document.NewReport.enddate.readOnly=true
					getObj("jscal_trigger_date_start").style.visibility="hidden"
					getObj("jscal_trigger_date_end").style.visibility="hidden"
				} else {
					document.NewReport.startdate.readOnly=false
					document.NewReport.enddate.readOnly=false
					getObj("jscal_trigger_date_start").style.visibility="visible"
					getObj("jscal_trigger_date_end").style.visibility="visible"
				}
				if( type == "today" ) {
					document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'";

				} else if( type == "yesterday" ) {
					document.NewReport.startdate.value = "'.$yesterdayDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$yesterdayDateTime->getDisplayDate().'";

				} else if( type == "tomorrow" ) {
					document.NewReport.startdate.value = "'.$tomorrowDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$tomorrowDateTime->getDisplayDate().'";

				} else if( type == "thisweek" ) {
					document.NewReport.startdate.value = "'.$thisWeekStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$thisWeekEndDateTime->getDisplayDate().'";

				} else if( type == "lastweek" ) {
					document.NewReport.startdate.value = "'.$lastWeekStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$lastWeekEndDateTime->getDisplayDate().'";

				} else if( type == "nextweek" ) {
					document.NewReport.startdate.value = "'.$nextWeekStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$nextWeekEndDateTime->getDisplayDate().'";

				} else if( type == "thismonth" ) {
					document.NewReport.startdate.value = "'.$currentMonthStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$currentMonthEndDateTime->getDisplayDate().'";

				} else if( type == "lastmonth" ) {
					document.NewReport.startdate.value = "'.$lastMonthStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$lastMonthEndDateTime->getDisplayDate().'";

				} else if( type == "nextmonth" ) {
					document.NewReport.startdate.value = "'.$nextMonthStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$nextMonthEndDateTime->getDisplayDate().'";

				} else if( type == "next7days" ) {
					document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$next7DaysDateTime->getDisplayDate().'";

				} else if( type == "next30days" ) {
					document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$next30DaysDateTime->getDisplayDate().'";

				} else if( type == "next60days" ) {
					document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$next60DaysDateTime->getDisplayDate().'";

				} else if( type == "next90days" ) {
					document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$next90DaysDateTime->getDisplayDate().'";

				} else if( type == "next120days" ) {
					document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$next120DaysDateTime->getDisplayDate().'";

				} else if( type == "last7days" ) {
					document.NewReport.startdate.value = "'.$last7DaysDateTime->getDisplayDate().'";
					document.NewReport.enddate.value =  "'.$todayDateTime->getDisplayDate().'";

				} else if( type == "last30days" ) {
					document.NewReport.startdate.value = "'.$last30DaysDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'";

				} else if( type == "last60days" ) {
					document.NewReport.startdate.value = "'.$last60DaysDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'";

				} else if( type == "last90days" ) {
					document.NewReport.startdate.value = "'.$last90DaysDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'";

				} else if( type == "last120days" ) {
					document.NewReport.startdate.value = "'.$last120DaysDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'";

				} else if( type == "thisfy" ) {
					document.NewReport.startdate.value = "'.$currentFYStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$currentFYEndDateTime->getDisplayDate().'";

				} else if( type == "prevfy" ) {
					document.NewReport.startdate.value = "'.$lastFYStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$lastFYEndDateTime->getDisplayDate().'";

				} else if( type == "nextfy" ) {
					document.NewReport.startdate.value = "'.$nextFYStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$nextFYEndDateTime->getDisplayDate().'";

				} else if( type == "nextfq" ) {
					document.NewReport.startdate.value = "'.$nFqStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$nFqEndDateTime->getDisplayDate().'";

				} else if( type == "prevfq" ) {
					document.NewReport.startdate.value = "'.$pFqStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$pFqEndDateTime->getDisplayDate().'";

				} else if( type == "thisfq" ) {
					document.NewReport.startdate.value = "'.$cFqStartDateTime->getDisplayDate().'";
					document.NewReport.enddate.value = "'.$cFqEndDateTime->getDisplayDate().'";

				} else {
					document.NewReport.startdate.value = "";
					document.NewReport.enddate.value = "";
				}
			}
		</script>';

		return $sjsStr;
	}
function getEscapedColumns($selectedfields)
	{
		$fieldname = $selectedfields[3];
		if($fieldname == "parent_id")
		{
			if($this->primarymodule == "HelpDesk" && $selectedfields[0] == "vtiger_crmentityRelHelpDesk")
			{
				$querycolumn = "case vtiger_crmentityRelHelpDesk.setype when 'Accounts' then vtiger_accountRelHelpDesk.accountname when 'Contacts' then vtiger_contactdetailsRelHelpDesk.lastname End"." '".$selectedfields[2]."', vtiger_crmentityRelHelpDesk.setype 'Entity_type'";
				return $querycolumn;
			}
			if($this->primarymodule == "Products" || $this->secondarymodule == "Products")
			{
				$querycolumn = "case vtiger_crmentityRelProducts.setype when 'Accounts' then vtiger_accountRelProducts.accountname when 'Leads' then vtiger_leaddetailsRelProducts.lastname when 'Potentials' then vtiger_potentialRelProducts.potentialname End"." '".$selectedfields[2]."', vtiger_crmentityRelProducts.setype 'Entity_type'";
			}
			if($this->primarymodule == "Calendar" || $this->secondarymodule == "Calendar")
			{
				$querycolumn = "case vtiger_crmentityRelCalendar.setype when 'Accounts' then vtiger_accountRelCalendar.accountname when 'Leads' then vtiger_leaddetailsRelCalendar.lastname when 'Potentials' then vtiger_potentialRelCalendar.potentialname when 'Quotes' then vtiger_quotesRelCalendar.subject when 'PurchaseOrder' then vtiger_purchaseorderRelCalendar.subject when 'Invoice' then vtiger_invoiceRelCalendar.subject End"." '".$selectedfields[2]."', vtiger_crmentityRelCalendar.setype 'Entity_type'";
			}
		}
		return $querycolumn;
	}
	function getaccesfield($module)
	{
		global $current_user;
		global $adb;
		$access_fields = Array();

		$profileList = getCurrentUserProfileList();
		$query = "select vtiger_field.fieldname from vtiger_field inner join vtiger_profile2field on vtiger_profile2field.fieldid=vtiger_field.fieldid inner join vtiger_def_org_field on vtiger_def_org_field.fieldid=vtiger_field.fieldid where";
		$params = array();
		if($module == "Calendar")
		{
			$query .= " vtiger_field.tabid in (9,16) and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.presence in (0,2)";
			if (count($profileList) > 0) {
				$query .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")";
				array_push($params, $profileList);
			}
			$query .= " group by vtiger_field.fieldid order by block,sequence";
		}
		else
		{
			array_push($params, $this->primodule, $this->secmodule);
			$query .= " vtiger_field.tabid in (select tabid from vtiger_tab where vtiger_tab.name in (?,?)) and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.presence in (0,2)";
			if (count($profileList) > 0) {
				$query .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")";
				array_push($params, $profileList);
			}
			$query .= " group by vtiger_field.fieldid order by block,sequence";
		}
		$result = $adb->pquery($query, $params);


		while($collistrow = $adb->fetch_array($result))
		{
			$access_fields[] = $collistrow["fieldname"];
		}
		return $access_fields;
	}

	/** Function to set the order of grouping and to find the columns responsible
	 *  to the grouping
	 *  This function accepts the vtiger_reportid as variable,sets the variable ascdescorder[] to the sort order and
	 *  returns the array array_list which has the column responsible for the grouping
	 *  Array array_list[0]=columnname
	 */


	function getSelctedSortingColumns($reportid)
	{

		global $adb;
		global $log;

		$sreportsortsql = "select vtiger_reportsortcol.* from vtiger_report";
		$sreportsortsql .= " inner join vtiger_reportsortcol on vtiger_report.reportid = vtiger_reportsortcol.reportid";
		$sreportsortsql .= " where vtiger_report.reportid =? order by vtiger_reportsortcol.sortcolid";

		$result = $adb->pquery($sreportsortsql, array($reportid));
		$noofrows = $adb->num_row…