PageRenderTime 58ms CodeModel.GetById 16ms RepoModel.GetById 1ms app.codeStats 0ms

/modules/Reports/ReportRun.php

https://bitbucket.org/thomashii/vtigercrm-5.4-for-postgresql
PHP | 3344 lines | 2805 code | 320 blank | 219 comment | 991 complexity | a01cbae349da2aec8510fc908a835a58 MD5 | raw file
Possible License(s): LGPL-2.1, GPL-2.0

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

    

  1. <?php
    2. 

  2. /*+********************************************************************************
    3. 

  3.  * The contents of this file are subject to the vtiger CRM Public License Version 1.0
    4. 

  4.  * ("License"); You may not use this file except in compliance with the License
    5. 

  5.  * The Original Code is:  vtiger CRM Open Source
    6. 

  6.  * The Initial Developer of the Original Code is vtiger.
    7. 

  7.  * Portions created by vtiger are Copyright (C) vtiger.
    8. 

  8.  * All Rights Reserved.
    9. 

  9.  ********************************************************************************/
    10. 

  10. global $calpath;
    11. 

  11. global $app_strings,$mod_strings;
    12. 

  12. global $theme;
    13. 

  13. global $log;
    14. 

  14. 

  15. $theme_path="themes/".$theme."/";
    16. 

  16. $image_path=$theme_path."images/";
    17. 

  17. require_once('include/database/PearDatabase.php');
    18. 

  18. require_once('include/database/SqlQueryFix.php');
    19. 

  19. require_once('data/CRMEntity.php');
    20. 

  20. require_once("modules/Reports/Reports.php");
    21. 

  21. require_once 'modules/Reports/ReportUtils.php';
    22. 

  22. require_once("vtlib/Vtiger/Module.php");
    23. 

  23. 

  24. class ReportRun extends CRMEntity
    25. 

  25. {
    26. 

  26. 

  27. 	var $primarymodule;
    28. 

  28. 	var $secondarymodule;
    29. 

  29. 	var $orderbylistsql;
    30. 

  30. 	var $orderbylistcolumns;
    31. 

  31. 

  32. 	var $selectcolumns;
    33. 

  33. 	var $groupbylist;
    34. 

  34. 	var $reporttype;
    35. 

  35. 	var $reportname;
    36. 

  36. 	var $totallist;
    37. 

  37. 

  38. 	var $_groupinglist  = false;
    39. 

  39. 	var $_columnslist    = false;
    40. 

  40. 	var	$_stdfilterlist = false;
    41. 

  41. 	var	$_columnstotallist = false;
    42. 

  42. 	var	$_advfiltersql = false;
    43. 

  43. 

  44. 	var $append_currency_symbol_to_value = array('Products_Unit_Price','Services_Price',
    45. 

  45. 						'Invoice_Total', 'Invoice_Sub_Total', 'Invoice_S&H_Amount', 'Invoice_Discount_Amount', 'Invoice_Adjustment',
    46. 

  46. 						'Quotes_Total', 'Quotes_Sub_Total', 'Quotes_S&H_Amount', 'Quotes_Discount_Amount', 'Quotes_Adjustment',
    47. 

  47. 						'SalesOrder_Total', 'SalesOrder_Sub_Total', 'SalesOrder_S&H_Amount', 'SalesOrder_Discount_Amount', 'SalesOrder_Adjustment',
    48. 

  48. 						'PurchaseOrder_Total', 'PurchaseOrder_Sub_Total', 'PurchaseOrder_S&H_Amount', 'PurchaseOrder_Discount_Amount', 'PurchaseOrder_Adjustment'
    49. 

  49. 						);
    50. 

  50. 	var $ui10_fields = array();
    51. 

  51. 	var $ui101_fields = array();
    52. 

  52. 	var $groupByTimeParent = array( 'Quarter'=>array('Year'),
    53. 

  53. 									'Month'=>array('Year')
    54. 

  54. 								);
    55. 

  55. 

  56. 	/** Function to set reportid,primarymodule,secondarymodule,reporttype,reportname, for given reportid
    57. 

  57. 	 *  This function accepts the $reportid as argument
    58. 

  58. 	 *  It sets reportid,primarymodule,secondarymodule,reporttype,reportname for the given reportid
    59. 

  59. 	 */
    60. 

  60. 	function ReportRun($reportid)
    61. 

  61. 	{
    62. 

  62. 		$oReport = new Reports($reportid);
    63. 

  63. 		$this->reportid = $reportid;
    64. 

  64. 		$this->primarymodule = $oReport->primodule;
    65. 

  65. 		$this->secondarymodule = $oReport->secmodule;
    66. 

  66. 		$this->reporttype = $oReport->reporttype;
    67. 

  67. 		$this->reportname = $oReport->reportname;
    68. 

  68. 	}
    69. 

  69. 

  70. 	/** Function to get the columns for the reportid
    71. 

  71. 	 *  This function accepts the $reportid and $outputformat (optional)
    72. 

  72. 	 *  This function returns  $columnslist Array($tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname As Header value,
    73. 

  73. 	 *					      $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 As Header value,
    74. 

  74. 	 *					      					|
    75. 

  75.  	 *					      $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen As Header value
    76. 

  76. 	 *				      	     )
    77. 

  77. 	 *
    78. 

  78. 	 */
    79. 

  79. 	function getQueryColumnsList($reportid,$outputformat='')
    80. 

  80. 	{
    81. 

  81. 		// Have we initialized information already?
    82. 

  82. 		if($this->_columnslist !== false) {
    83. 

  83. 			return $this->_columnslist;
    84. 

  84. 		}
    85. 

  85. 

  86. 		global $adb;
    87. 

  87. 		global $modules;
    88. 

  88. 		global $log,$current_user,$current_language;
    89. 

  89. 		$ssql = "select vtiger_selectcolumn.* from vtiger_report inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid";
    90. 

  90. 		$ssql .= " left join vtiger_selectcolumn on vtiger_selectcolumn.queryid = vtiger_selectquery.queryid";
    91. 

  91. 		$ssql .= " where vtiger_report.reportid = ?";
    92. 

  92. 		$ssql .= " order by vtiger_selectcolumn.columnindex";
    93. 

  93. 		$result = $adb->pquery($ssql, array($reportid));
    94. 

  94. 		$permitted_fields = Array();
    95. 

  95. 

  96. 		while($columnslistrow = $adb->fetch_array($result))
    97. 

  97. 		{
    98. 

  98. 			$fieldname ="";
    99. 

  99. 			$fieldcolname = $columnslistrow["columnname"];
    100. 

  100. 			list($tablename,$colname,$module_field,$fieldname,$single) = split(":",$fieldcolname);
    101. 

  101. 			list($module,$field) = split("_",$module_field,2);
    102. 

  102. 			$inventory_fields = array('quantity','listprice','serviceid','productid','discount','comment');
    103. 

  103. 			$inventory_modules = array('SalesOrder','Quotes','PurchaseOrder','Invoice');
    104. 

  104. 			require('user_privileges/user_privileges_'.$current_user->id.'.php');
    105. 

  105. 			if(sizeof($permitted_fields[$module]) == 0 && $is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1)
    106. 

  106. 			{
    107. 

  107. 				$permitted_fields[$module] = $this->getaccesfield($module);
    108. 

  108. 			}
    109. 

  109. 			if(in_array($module,$inventory_modules)){
    110. 

  110. 				$permitted_fields = array_merge($permitted_fields,$inventory_fields);
    111. 

  111. 			}
    112. 

  112. 			$selectedfields = explode(":",$fieldcolname);
    113. 

  113. 			if($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1
    114. 

  114. 					&& !in_array($selectedfields[3], $permitted_fields[$module])) {
    115. 

  115. 				//user has no access to this field, skip it.
    116. 

  116. 				continue;
    117. 

  117. 			}
    118. 

  118. 			$concatSql = getSqlForNameInDisplayFormat(array('first_name'=>$selectedfields[0].".first_name",'last_name'=>$selectedfields[0].".last_name"), 'Users');
    119. 

  119. 			$querycolumns = $this->getEscapedColumns($selectedfields);
    120. 

  120. 

  121. 			if(isset($module) && $module!="") {
    122. 

  122. 				$mod_strings = return_module_language($current_language,$module);
    123. 

  123. 			}
    124. 

  124. 

  125. 			$fieldlabel = trim(preg_replace("/$module/"," ",$selectedfields[2],1));
    126. 

  126. 			$mod_arr=explode('_',$fieldlabel);
    127. 

  127. 			$fieldlabel = trim(str_replace("_"," ",$fieldlabel));
    128. 

  128. 			//modified code to support i18n issue
    129. 

  129. 			$fld_arr = explode(" ",$fieldlabel);
    130. 

  130. 			if(($mod_arr[0] == '')) {
    131. 

  131. 				$mod = $module;
    132. 

  132. 				$mod_lbl = getTranslatedString($module,$module); //module
    133. 

  133. 			} else {
    134. 

  134. 				$mod = $mod_arr[0];
    135. 

  135. 				array_shift($fld_arr);
    136. 

  136. 				$mod_lbl = getTranslatedString($fld_arr[0],$mod); //module
    137. 

  137. 			}
    138. 

  138. 			$fld_lbl_str = implode(" ",$fld_arr);
    139. 

  139. 			$fld_lbl = getTranslatedString($fld_lbl_str,$module); //fieldlabel
    140. 

  140. 			$fieldlabel = $mod_lbl." ".$fld_lbl;
    141. 

  141. 			if(($selectedfields[0] == "vtiger_usersRel1")  && ($selectedfields[1] == 'user_name') && ($selectedfields[2] == 'Quotes_Inventory_Manager')){
    142. 

  142. 				$columnslist[$fieldcolname] = "trim( $concatSql ) as ".$module."_Inventory_Manager";
    143. 

  143. 				continue;
    144. 

  144. 			}
    145. 

  145. 

  146. 			if((CheckFieldPermission($fieldname,$mod) != 'true' && $colname!="crmid" && (!in_array($fieldname,$inventory_fields) && in_array($module,$inventory_modules))) || empty($fieldname))
    147. 

  147. 			{
    148. 

  148. 				continue;
    149. 

  149. 			}
    150. 

  150. 			else
    151. 

  151. 			{
    152. 

  152. 				$this->labelMapping[$selectedfields[2]] = str_replace(" ","_",$fieldlabel);
    153. 

  153. 				$header_label = $selectedfields[2]; // Header label to be displayed in the reports table
    154. 

  154. 				// To check if the field in the report is a custom field
    155. 

  155. 				// and if yes, get the label of this custom field freshly from the vtiger_field as it would have been changed.
    156. 

  156. 				// Asha - Reference ticket : #4906
    157. 

  157. 

  158. 				if($querycolumns == "")
    159. 

  159. 				{
    160. 

  160. 					if($selectedfields[4] == 'C')
    161. 

  161. 					{
    162. 

  162. 						$field_label_data = split("_",$selectedfields[2]);
    163. 

  163. 						$module= $field_label_data[0];
    164. 

  164. 						if($module!=$this->primarymodule)
    165. 

  165. 							$columnslist[$fieldcolname] = "case when (".$selectedfields[0].".".$selectedfields[1]."='1')then 'yes' else case when (vtiger_crmentity$module.crmid is not null) then 'no' else '-' end end as '$selectedfields[2]'";
    166. 

  166. 						else
    167. 

  167. 							$columnslist[$fieldcolname] = "case when (".$selectedfields[0].".".$selectedfields[1]."='1')then 'yes' else case when (vtiger_crmentity.crmid is not null) then 'no' else '-' end end as '$selectedfields[2]'";
    168. 

  168. 					}
    169. 

  169. 					elseif($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'status')
    170. 

  170. 					{
    171. 

  171. 						$columnslist[$fieldcolname] = " case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end as Calendar_Status";
    172. 

  172. 					}
    173. 

  173. 					elseif($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'date_start')
    174. 

  174. 					{
    175. 

  175.                                             $columnslist[$fieldcolname] = "to_timestamp(vtiger_activity.date_start ||'  ' || vtiger_activity.time_start,'YYYY-MM-DD HH24:MI:SS') as Calendar_Start_Date_and_Time";
    176. 

  176. 					}
    177. 

  177. 					elseif(stristr($selectedfields[0],"vtiger_users") && ($selectedfields[1] == 'user_name'))
    178. 

  178. 					{
    179. 

  179. 						$temp_module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]);
    180. 

  180. 						if($module!=$this->primarymodule){
    181. 

  181. 							$condition = "and vtiger_crmentity".$module.".crmid is not null ";
    182. 

  182. 						} else {
    183. 

  183. 							$condition = "and vtiger_crmentity.crmid is not null ";
    184. 

  184. 						}
    185. 

  185. 						if($temp_module_from_tablename == $module)
    186. 

  186. 							$columnslist[$fieldcolname] = " case when(".$selectedfields[0].".last_name NOT LIKE '' $condition ) THEN ".$concatSql." else vtiger_groups".$module.".groupname end as '".$module."_$field'";
    187. 

  187. 						else//Some Fields can't assigned to groups so case avoided (fields like inventory manager)
    188. 

  188. 							$columnslist[$fieldcolname] = $selectedfields[0].".user_name as ".$header_label." ";
    189. 

  189. 

  190. 					}
    191. 

  191.                     elseif(stristr($selectedfields[0],"vtiger_crmentity") && ($selectedfields[1] == 'modifiedby')) {
    192. 

  192.                         $concatSql = getSqlForNameInDisplayFormat(array('last_name'=>'vtiger_lastModifiedBy'.$module.'.last_name', 'first_name'=>'vtiger_lastModifiedBy'.$module.'.first_name'), 'Users');
    193. 

  193. 						$columnslist[$fieldcolname] = "trim($concatSql) as $header_label";
    194. 

  194. 					}
    195. 

  195. 					elseif($selectedfields[0] == "vtiger_crmentity".$this->primarymodule)
    196. 

  196. 					{
    197. 

  197. 						$columnslist[$fieldcolname] = "vtiger_crmentity.".$selectedfields[1]." AS ".$header_label." ";
    198. 

  198. 					}
    199. 

  199. 				    elseif($selectedfields[0] == 'vtiger_products' && $selectedfields[1] == 'unit_price')//handled for product fields in Campaigns Module Reports
    200. 

  200. 					{
    201. 

  201. 						$columnslist[$fieldcolname] = $selectedfields[0].".currency_id || '::' || innerProduct.actual_unit_price as ". $header_label ." ";
    202. 

  202. 					}
    203. 

  203. 					elseif(in_array($selectedfields[2], $this->append_currency_symbol_to_value)) {
    204. 

  204. 						$columnslist[$fieldcolname] = $selectedfields[0].".currency_id || '::' || ".$selectedfields[0].".".$selectedfields[1]." as " . $header_label ." ";
    205. 

  205. 					}
    206. 

  206. 					elseif($selectedfields[0] == 'vtiger_notes' && ($selectedfields[1] == 'filelocationtype' || $selectedfields[1] == 'filesize' || $selectedfields[1] == 'folderid' || $selectedfields[1]=='filestatus'))//handled for product fields in Campaigns Module Reports
    207. 

  207. 					{
    208. 

  208. 						if($selectedfields[1] == 'filelocationtype'){
    209. 

  209. 							$columnslist[$fieldcolname] = "case ".$selectedfields[0].".".$selectedfields[1]." when 'I' then 'Internal' when 'E' then 'External' else '-' end as $selectedfields[2] ";
    210. 

  210. 						} else if($selectedfields[1] == 'folderid'){
    211. 

  211. 							$columnslist[$fieldcolname] = "vtiger_attachmentsfolder.foldername as  $selectedfields[2] ";
    212. 

  212. 						} elseif($selectedfields[1] == 'filestatus'){
    213. 

  213. 							$columnslist[$fieldcolname] = "case ".$selectedfields[0].".".$selectedfields[1]." when '1' then 'yes' when '0' then 'no' else '-' end as  $selectedfields[2] ";
    214. 

  214. 						} elseif($selectedfields[1] == 'filesize'){
    215. 

  215. 							$columnslist[$fieldcolname] = "case ".$selectedfields[0].".".$selectedfields[1]." when '' then '-' else ".$selectedfields[0].".".$selectedfields[1]."/1024 || '  ' || 'KB' end as  $selectedfields[2] ";
    216. 

  216. 						}
    217. 

  217. 					}
    218. 

  218. 					elseif($selectedfields[0] == 'vtiger_inventoryproductrel')//handled for product fields in Campaigns Module Reports
    219. 

  219. 					{
    220. 

  220. 						if($selectedfields[1] == 'discount'){
    221. 

  221. 							$columnslist[$fieldcolname] = " case when (vtiger_inventoryproductrel{$module}.discount_amount is not null) then vtiger_inventoryproductrel{$module}.discount_amount else ROUND((vtiger_inventoryproductrel{$module}.listprice * vtiger_inventoryproductrel{$module}.quantity * (vtiger_inventoryproductrel{$module}.discount_percent/100)),3) end as " . $header_label ." ";
    222. 

  222. 						} else if($selectedfields[1] == 'productid'){
    223. 

  223. 							$columnslist[$fieldcolname] = "vtiger_products{$module}.productname as " . $header_label ." ";
    224. 

  224. 						} else if($selectedfields[1] == 'serviceid'){
    225. 

  225. 							$columnslist[$fieldcolname] = "vtiger_service{$module}.servicename as " . $header_label ." ";
    226. 

  226. 						} else {
    227. 

  227. 							$columnslist[$fieldcolname] = $selectedfields[0].$module.".".$selectedfields[1]." as ".$header_label." ";
    228. 

  228. 						}
    229. 

  229. 					}
    230. 

  230. 					elseif(stristr($selectedfields[1],'cf_')==true && stripos($selectedfields[1],'cf_')==0)
    231. 

  231. 					{
    232. 

  232. 						$columnslist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]." AS ".$adb->sql_escape_string(decode_html($header_label))." ";
    233. 

  233. 					}
    234. 

  234. 					else
    235. 

  235. 					{
    236. 

  236. 						$columnslist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]." AS ".$header_label." ";
    237. 

  237. 					}
    238. 

  238. 				}
    239. 

  239. 				else
    240. 

  240. 				{
    241. 

  241. 					$columnslist[$fieldcolname] = $querycolumns;
    242. 

  242. 				}
    243. 

  243. 			}
    244. 

  244. 		}
    245. 

  245. 		if ($outputformat == "HTML") $columnslist['vtiger_crmentity:crmid:LBL_ACTION:crmid:I'] = "vtiger_crmentity.crmid AS LBL_ACTION" ;
    246. 

  246. 		// Save the information
    247. 

  247. 		$this->_columnslist = $columnslist;
    248. 

  248. 

  249. 		$log->info("ReportRun :: Successfully returned getQueryColumnsList".$reportid);
    250. 

  250. 		return $columnslist;
    251. 

  251. 	}
    252. 

  252. 

  253. 	/** Function to get field columns based on profile
    254. 

  254. 	 *  @ param $module : Type string
    255. 

  255. 	 *  returns permitted fields in array format
    256. 

  256. 	 */
    257. 

  257. 	function getaccesfield($module) {
    258. 

  258. 		global $current_user;
    259. 

  259. 		global $adb;
    260. 

  260. 		$access_fields = Array();
    261. 

  261. 

  262. 		$profileList = getCurrentUserProfileList();
    263. 

  263. 		$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";
    264. 

  264. 		$params = array();
    265. 

  265. 		if($module == "Calendar")
    266. 

  266. 		{
    267. 

  267. 			if (count($profileList) > 0) {
    268. 

  268. 				$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
    269. 

  269. 								and vtiger_field.presence IN (0,2) and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .") group by vtiger_field.fieldid order by block,sequence";
    270. 

  270. 				array_push($params, $profileList);
    271. 

  271. 			} else {
    272. 

  272. 				$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
    273. 

  273. 								and vtiger_field.presence IN (0,2) group by vtiger_field.fieldid order by block,sequence";
    274. 

  274. 			}
    275. 

  275. 		}
    276. 

  276. 		else
    277. 

  277. 		{
    278. 

  278. 			array_push($params, $module);
    279. 

  279. 			if (count($profileList) > 0) {
    280. 

  280. 				$query .= " vtiger_field.tabid in (select tabid from vtiger_tab where vtiger_tab.name in (?)) and vtiger_field.displaytype in (1,2,3,5) and vtiger_profile2field.visible=0
    281. 

  281. 								and vtiger_field.presence IN (0,2) and vtiger_def_org_field.visible=0 and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .") group by vtiger_field.fieldid order by block,sequence";
    282. 

  282. 				array_push($params, $profileList);
    283. 

  283. 			} else {
    284. 

  284. 				$query .= " vtiger_field.tabid in (select tabid from vtiger_tab where vtiger_tab.name in (?)) and vtiger_field.displaytype in (1,2,3,5) and vtiger_profile2field.visible=0
    285. 

  285. 								and vtiger_field.presence IN (0,2) and vtiger_def_org_field.visible=0 group by vtiger_field.fieldid order by block,sequence";
    286. 

  286. 			}
    287. 

  287. 		}
    288. 

  288. 		$result = $adb->pquery($query, $params);
    289. 

  289. 

  290. 		while($collistrow = $adb->fetch_array($result))
    291. 

  291. 		{
    292. 

  292. 			$access_fields[] = $collistrow["fieldname"];
    293. 

  293. 		}
    294. 

  294. 		//added to include ticketid for Reports module in select columnlist for all users
    295. 

  295. 		if($module == "HelpDesk")
    296. 

  296. 			$access_fields[] = "ticketid";
    297. 

  297. 		return $access_fields;
    298. 

  298. 	}
    299. 

  299. 

  300. 	/** Function to get Escapedcolumns for the field in case of multiple parents
    301. 

  301. 	 *  @ param $selectedfields : Type Array
    302. 

  302. 	 *  returns the case query for the escaped columns
    303. 

  303. 	 */
    304. 

  304. 	function getEscapedColumns($selectedfields) {
    305. 

  305. 

  306. 		$tableName = $selectedfields[0];
    307. 

  307. 		$columnName = $selectedfields[1];
    308. 

  308. 		$moduleFieldLabel = $selectedfields[2];
    309. 

  309. 		$fieldName = $selectedfields[3];
    310. 

  310. 		list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
    311. 

  311. 		$fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel);
    312. 

  312. 

  313. 		if($moduleName == 'ModComments' && $fieldName == 'creator') {
    314. 

  314. 			$concatSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_usersModComments.first_name',
    315. 

  315. 															'last_name' => 'vtiger_usersModComments.last_name'), 'Users');
    316. 

  316. 			$queryColumn = "trim(case when (vtiger_usersModComments.user_name not like '' and vtiger_crmentity.crmid!=0) then $concatSql end) as ModComments_Creator ";
    317. 

  317. 

  318. 		} elseif(($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype']))
    319. 

  319. 				&& $fieldInfo['uitype'] != '52' && $fieldInfo['uitype'] != '53') {
    320. 

  320. 			$fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo);
    321. 

  321. 			if(count($fieldSqlColumns) > 0) {
    322. 

  322. 				if(containsIdField($columnName)) {
    323. 

  323. 					$log->debug("Has id field : ".$columnName );
    324. 

  324. 					$queryColumn = "(CASE WHEN $tableName.$columnName IS NOT NULL THEN (CASE";		
    325. 

  325. 				} else {
    326. 

  326. 					$log->debug("No id field : ".$columnName );
    327. 

  327. 					$queryColumn = "(CASE WHEN $tableName.$columnName NOT LIKE '' THEN (CASE";					
    328. 

  328. 				}
    329. 

  329. 				foreach($fieldSqlColumns as $columnSql) {
    330. 

  330. 					if(containsIdField($columnSql)) {
    331. 

  331. 						$log->debug("Has id field : ".$columnSql );
    332. 

  332. 						$queryColumn .= " WHEN $columnSql IS NOT NULL THEN $columnSql";
    333. 

  333. 					} else {
    334. 

  334. 						$log->debug("No id field : ".$columnSql );
    335. 

  335. 						$queryColumn .= " WHEN $columnSql NOT LIKE '' THEN $columnSql";
    336. 

  336. 					}
    337. 

  337. 				}
    338. 

  338. 				$queryColumn .= " ELSE '' END) ELSE '' END) AS $moduleFieldLabel";
    339. 

  339. 			}
    340. 

  340. 		}
    341. 

  341. 		return $queryColumn;
    342. 

  342. 	}
    343. 

  343. 

  344. 	/** Function to get selectedcolumns for the given reportid
    345. 

  345. 	 *  @ param $reportid : Type Integer
    346. 

  346. 	 *  returns the query of columnlist for the selected columns
    347. 

  347. 	 */
    348. 

  348. 	function getSelectedColumnsList($reportid)
    349. 

  349. 	{
    350. 

  350. 

  351. 		global $adb;
    352. 

  352. 		global $modules;
    353. 

  353. 		global $log;
    354. 

  354. 

  355. 		$ssql = "select vtiger_selectcolumn.* from vtiger_report inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid";
    356. 

  356. 		$ssql .= " left join vtiger_selectcolumn on vtiger_selectcolumn.queryid = vtiger_selectquery.queryid where vtiger_report.reportid = ? ";
    357. 

  357. 		$ssql .= " order by vtiger_selectcolumn.columnindex";
    358. 

  358. 

  359. 		$result = $adb->pquery($ssql, array($reportid));
    360. 

  360. 		$noofrows = $adb->num_rows($result);
    361. 

  361. 

  362. 		if ($this->orderbylistsql != "")
    363. 

  363. 		{
    364. 

  364. 			$sSQL .= $this->orderbylistsql.", ";
    365. 

  365. 		}
    366. 

  366. 

  367. 		for($i=0; $i<$noofrows; $i++)
    368. 

  368. 		{
    369. 

  369. 			$fieldcolname = $adb->query_result($result,$i,"columnname");
    370. 

  370. 			$ordercolumnsequal = true;
    371. 

  371. 			if($fieldcolname != "")
    372. 

  372. 			{
    373. 

  373. 				for($j=0;$j<count($this->orderbylistcolumns);$j++)
    374. 

  374. 				{
    375. 

  375. 					if($this->orderbylistcolumns[$j] == $fieldcolname)
    376. 

  376. 					{
    377. 

  377. 						$ordercolumnsequal = false;
    378. 

  378. 						break;
    379. 

  379. 					}else
    380. 

  380. 					{
    381. 

  381. 						$ordercolumnsequal = true;
    382. 

  382. 					}
    383. 

  383. 				}
    384. 

  384. 				if($ordercolumnsequal)
    385. 

  385. 				{
    386. 

  386. 					$selectedfields = explode(":",$fieldcolname);
    387. 

  387. 					if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule)
    388. 

  388. 						$selectedfields[0] = "vtiger_crmentity";
    389. 

  389. 					$sSQLList[] = $selectedfields[0].".".$selectedfields[1]." '".$selectedfields[2]."'";
    390. 

  390. 				}
    391. 

  391. 			}
    392. 

  392. 		}
    393. 

  393. 		$sSQL .= implode(",",$sSQLList);
    394. 

  394. 

  395. 		$log->info("ReportRun :: Successfully returned getSelectedColumnsList".$reportid);
    396. 

  396. 		return $sSQL;
    397. 

  397. 	}
    398. 

  398. 

  399. 	/** Function to get advanced comparator in query form for the given Comparator and value
    400. 

  400. 	 *  @ param $comparator : Type String
    401. 

  401. 	 *  @ param $value : Type String
    402. 

  402. 	 *  returns the check query for the comparator
    403. 

  403. 	 */
    404. 

  404. 	function getAdvComparator($comparator,$value,$datatype="")
    405. 

  405. 	{
    406. 

  406. 

  407. 		global $log,$adb,$default_charset,$ogReport;
    408. 

  408. 		$value=html_entity_decode(trim($value),ENT_QUOTES,$default_charset);
    409. 

  409. 		$value_len = strlen($value);
    410. 

  410. 		$is_field = false;
    411. 

  411. 		if($value_len > 1 && $value[0]=='$' && $value[$value_len-1]=='$'){
    412. 

  412. 			$temp = str_replace('$','',$value);
    413. 

  413. 			$is_field = true;
    414. 

  414. 		}
    415. 

  415. 		if($datatype=='C'){
    416. 

  416. 			$value = str_replace("yes","1",str_replace("no","0",$value));
    417. 

  417. 		}
    418. 

  418. 

  419. 		if($is_field==true){
    420. 

  420. 			$value = $this->getFilterComparedField($temp);
    421. 

  421. 		}
    422. 

  422. 		if($comparator == "e")
    423. 

  423. 		{
    424. 

  424. 			if(trim($value) == "NULL")
    425. 

  425. 			{
    426. 

  426. 				$rtvalue = " is NULL";
    427. 

  427. 			}elseif(trim($value) != "")
    428. 

  428. 			{
    429. 

  429. 				$rtvalue = " = ".$adb->quote($value);
    430. 

  430. 			}elseif(trim($value) == "" && $datatype == "V")
    431. 

  431. 			{
    432. 

  432. 				$rtvalue = " = ".$adb->quote($value);
    433. 

  433. 			}else
    434. 

  434. 			{
    435. 

  435. 				$rtvalue = " is NULL";
    436. 

  436. 			}
    437. 

  437. 		}
    438. 

  438. 		if($comparator == "n")
    439. 

  439. 		{
    440. 

  440. 			if(trim($value) == "NULL")
    441. 

  441. 			{
    442. 

  442. 				$rtvalue = " is NOT NULL";
    443. 

  443. 			}elseif(trim($value) != "")
    444. 

  444. 			{
    445. 

  445. 				$rtvalue = " <> ".$adb->quote($value);
    446. 

  446. 			}elseif(trim($value) == "" && $datatype == "V")
    447. 

  447. 			{
    448. 

  448. 				$rtvalue = " <> ".$adb->quote($value);
    449. 

  449. 			}else
    450. 

  450. 			{
    451. 

  451. 				$rtvalue = " is NOT NULL";
    452. 

  452. 			}
    453. 

  453. 		}
    454. 

  454. 		if($comparator == "s")
    455. 

  455. 		{
    456. 

  456. 			$rtvalue = " like '". formatForSqlLike($value, 2,$is_field) ."'";
    457. 

  457. 		}
    458. 

  458. 		if($comparator == "ew")
    459. 

  459. 		{
    460. 

  460. 			$rtvalue = " like '". formatForSqlLike($value, 1,$is_field) ."'";
    461. 

  461. 		}
    462. 

  462. 		if($comparator == "c")
    463. 

  463. 		{
    464. 

  464. 			$rtvalue = " like '". formatForSqlLike($value,0,$is_field) ."'";
    465. 

  465. 		}
    466. 

  466. 		if($comparator == "k")
    467. 

  467. 		{
    468. 

  468. 			$rtvalue = " not like '". formatForSqlLike($value,0,$is_field) ."'";
    469. 

  469. 		}
    470. 

  470. 		if($comparator == "l")
    471. 

  471. 		{
    472. 

  472. 			$rtvalue = " < ".$adb->quote($value);
    473. 

  473. 		}
    474. 

  474. 		if($comparator == "g")
    475. 

  475. 		{
    476. 

  476. 			$rtvalue = " > ".$adb->quote($value);
    477. 

  477. 		}
    478. 

  478. 		if($comparator == "m")
    479. 

  479. 		{
    480. 

  480. 			$rtvalue = " <= ".$adb->quote($value);
    481. 

  481. 		}
    482. 

  482. 		if($comparator == "h")
    483. 

  483. 		{
    484. 

  484. 			$rtvalue = " >= ".$adb->quote($value);
    485. 

  485. 		}
    486. 

  486. 		if($comparator == "b") {
    487. 

  487. 			$rtvalue = " < ".$adb->quote($value);
    488. 

  488. 		}
    489. 

  489. 		if($comparator == "a") {
    490. 

  490. 			$rtvalue = " > ".$adb->quote($value);
    491. 

  491. 		}
    492. 

  492. 		if($is_field==true){
    493. 

  493. 			$rtvalue = str_replace("'","",$rtvalue);
    494. 

  494. 			$rtvalue = str_replace("\\","",$rtvalue);
    495. 

  495. 		}
    496. 

  496. 		$log->info("ReportRun :: Successfully returned getAdvComparator");
    497. 

  497. 		return $rtvalue;
    498. 

  498. 	}
    499. 

  499. 

  500. 	/** Function to get field that is to be compared in query form for the given Comparator and field
    501. 

  501. 	 *  @ param $field : field
    502. 

  502. 	 *  returns the value for the comparator
    503. 

  503. 	 */
    504. 

  504. 	function getFilterComparedField($field){
    505. 

  505. 		global $adb,$ogReport;
    506. 

  506. 			$field = split('#',$field);
    507. 

  507. 			$module = $field[0];
    508. 

  508. 			$fieldname = trim($field[1]);
    509. 

  509. 			$tabid = getTabId($module);
    510. 

  510. 			$field_query = $adb->pquery("SELECT tablename,columnname,typeofdata,fieldname,uitype FROM vtiger_field WHERE tabid = ? AND fieldname= ?",array($tabid,$fieldname));
    511. 

  511. 			$fieldtablename = $adb->query_result($field_query,0,'tablename');
    512. 

  512. 			$fieldcolname = $adb->query_result($field_query,0,'columnname');
    513. 

  513. 			$typeofdata = $adb->query_result($field_query,0,'typeofdata');
    514. 

  514. 			$fieldtypeofdata=ChangeTypeOfData_Filter($fieldtablename,$fieldcolname,$typeofdata[0]);
    515. 

  515. 			$uitype = $adb->query_result($field_query,0,'uitype');
    516. 

  516. 			/*if($tr[0]==$ogReport->primodule)
    517. 

  517. 				$value = $adb->query_result($field_query,0,'tablename').".".$adb->query_result($field_query,0,'columnname');
    518. 

  518. 			else
    519. 

  519. 				$value = $adb->query_result($field_query,0,'tablename').$tr[0].".".$adb->query_result($field_query,0,'columnname');
    520. 

  520. 			*/
    521. 

  521. 			if($uitype == 68 || $uitype == 59)
    522. 

  522. 			{
    523. 

  523. 				$fieldtypeofdata = 'V';
    524. 

  524. 			}
    525. 

  525. 			if($fieldtablename == "vtiger_crmentity")
    526. 

  526. 			{
    527. 

  527. 				$fieldtablename = $fieldtablename.$module;
    528. 

  528. 			}
    529. 

  529. 			if($fieldname == "assigned_user_id")
    530. 

  530. 			{
    531. 

  531. 				$fieldtablename = "vtiger_users".$module;
    532. 

  532. 				$fieldcolname = "user_name";
    533. 

  533. 			}
    534. 

  534.             if($fieldtablename == "vtiger_crmentity" && $fieldname == "modifiedby")
    535. 

  535. 			{
    536. 

  536. 				$fieldtablename = "vtiger_lastModifiedBy".$module;
    537. 

  537. 				$fieldcolname = "user_name";
    538. 

  538. 			}
    539. 

  539. 			if($fieldname == "assigned_user_id1")
    540. 

  540. 			{
    541. 

  541. 				$fieldtablename = "vtiger_usersRel1";
    542. 

  542. 				$fieldcolname = "user_name";
    543. 

  543. 			}
    544. 

  544. 			$value = $fieldtablename.".".$fieldcolname;
    545. 

  545. 		return $value;
    546. 

  546. 	}
    547. 

  547. 	/** Function to get the advanced filter columns for the reportid
    548. 

  548. 	 *  This function accepts the $reportid
    549. 

  549. 	 *  This function returns  $columnslist Array($columnname => $tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname filtercriteria,
    550. 

  550. 	 *					      $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria,
    551. 

  551. 	 *					      					|
    552. 

  552.  	 *					      $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen filtercriteria
    553. 

  553. 	 *				      	     )
    554. 

  554. 	 *
    555. 

  555. 	 */
    556. 

  556. 	 function getAdvFilterList($reportid) {
    557. 

  557. 		global $adb, $log;
    558. 

  558. 

  559. 		$advft_criteria = array();
    560. 

  560. 

  561. 		$sql = 'SELECT * FROM vtiger_relcriteria_grouping WHERE queryid = ? ORDER BY groupid';
    562. 

  562. 		$groupsresult = $adb->pquery($sql, array($reportid));
    563. 

  563. 

  564. 		$i = 1;
    565. 

  565. 		$j = 0;
    566. 

  566. 		while($relcriteriagroup = $adb->fetch_array($groupsresult)) {
    567. 

  567. 			$groupId = $relcriteriagroup["groupid"];
    568. 

  568. 			$groupCondition = $relcriteriagroup["group_condition"];
    569. 

  569. 

  570. 			$ssql = 'select vtiger_relcriteria.* from vtiger_report
    571. 

  571. 						inner join vtiger_relcriteria on vtiger_relcriteria.queryid = vtiger_report.queryid
    572. 

  572. 						left join vtiger_relcriteria_grouping on vtiger_relcriteria.queryid = vtiger_relcriteria_grouping.queryid
    573. 

  573. 								and vtiger_relcriteria.groupid = vtiger_relcriteria_grouping.groupid';
    574. 

  574. 			$ssql.= " where vtiger_report.reportid = ? AND vtiger_relcriteria.groupid = ? order by vtiger_relcriteria.columnindex";
    575. 

  575. 

  576. 			$result = $adb->pquery($ssql, array($reportid, $groupId));
    577. 

  577. 			$noOfColumns = $adb->num_rows($result);
    578. 

  578. 			if($noOfColumns <= 0) continue;
    579. 

  579. 

  580. 			while($relcriteriarow = $adb->fetch_array($result)) {
    581. 

  581. 				$columnIndex = $relcriteriarow["columnindex"];
    582. 

  582. 				$criteria = array();
    583. 

  583. 				$criteria['columnname'] = html_entity_decode($relcriteriarow["columnname"]);
    584. 

  584. 				$criteria['comparator'] = $relcriteriarow["comparator"];
    585. 

  585. 				$advfilterval = $relcriteriarow["value"];
    586. 

  586. 				$col = explode(":",$relcriteriarow["columnname"]);
    587. 

  587. 				$criteria['value'] = $advfilterval;
    588. 

  588. 				$criteria['column_condition'] = $relcriteriarow["column_condition"];
    589. 

  589. 

  590. 				$advft_criteria[$i]['columns'][$j] = $criteria;
    591. 

  591. 				$advft_criteria[$i]['condition'] = $groupCondition;
    592. 

  592. 				$j++;
    593. 

  593. 			}
    594. 

  594. 			if(!empty($advft_criteria[$i]['columns'][$j-1]['column_condition'])) {
    595. 

  595. 				$advft_criteria[$i]['columns'][$j-1]['column_condition'] = '';
    596. 

  596. 			}
    597. 

  597. 			$i++;
    598. 

  598. 		}
    599. 

  599. 		// Clear the condition (and/or) for last group, if any.
    600. 

  600. 		if(!empty($advft_criteria[$i-1]['condition'])) $advft_criteria[$i-1]['condition'] = '';
    601. 

  601. 		return $advft_criteria;
    602. 

  602. 	}
    603. 

  603. 

  604. 	function generateAdvFilterSql($advfilterlist) {
    605. 

  605. 

  606. 		global $adb;
    607. 

  607. 

  608. 		$advfiltersql = "";
    609. 

  609. 

  610. 		foreach($advfilterlist as $groupindex => $groupinfo) {
    611. 

  611. 			$groupcondition = $groupinfo['condition'];
    612. 

  612. 			$groupcolumns = $groupinfo['columns'];
    613. 

  613. 

  614. 			if(count($groupcolumns) > 0) {
    615. 

  615. 

  616. 				$advfiltergroupsql = "";
    617. 

  617. 				foreach($groupcolumns as $columnindex => $columninfo) {
    618. 

  618. 					$fieldcolname = $columninfo["columnname"];
    619. 

  619. 					$comparator = $columninfo["comparator"];
    620. 

  620. 					$value = $columninfo["value"];
    621. 

  621. 					$columncondition = $columninfo["column_condition"];
    622. 

  622. 

  623. 					if($fieldcolname != "" && $comparator != "") {
    624. 

  624. 						$selectedfields = explode(":",$fieldcolname);
    625. 

  625. 						$moduleFieldLabel = $selectedfields[2];
    626. 

  626. 						list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
    627. 

  627. 						$fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel);
    628. 

  628.                         $concatSql = getSqlForNameInDisplayFormat(array('first_name'=>$selectedfields[0].".first_name",'last_name'=>$selectedfields[0].".last_name"), 'Users');
    629. 

  629. 						// Added to handle the crmentity table name for Primary module
    630. 

  630.                         if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) {
    631. 

  631.                             $selectedfields[0] = "vtiger_crmentity";
    632. 

  632.                         }
    633. 

  633. 						//Added to handle yes or no for checkbox  field in reports advance filters. -shahul
    634. 

  634. 						if($selectedfields[4] == 'C') {
    635. 

  635. 							if(strcasecmp(trim($value),"yes")==0)
    636. 

  636. 								$value="1";
    637. 

  637. 							if(strcasecmp(trim($value),"no")==0)
    638. 

  638. 								$value="0";
    639. 

  639. 						}
    640. 

  640. 						$valuearray = explode(",",trim($value));
    641. 

  641. 						$datatype = (isset($selectedfields[4])) ? $selectedfields[4] : "";
    642. 

  642. 						if(isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') {
    643. 

  643. 

  644. 							$advcolumnsql = "";
    645. 

  645. 							for($n=0;$n<count($valuearray);$n++) {
    646. 

  646. 

  647. 		                		if(($selectedfields[0] == "vtiger_users".$this->primarymodule || $selectedfields[0] == "vtiger_users".$this->secondarymodule) && $selectedfields[1] == 'user_name') {
    648. 

  648. 									$module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]);
    649. 

  649. 									$advcolsql[] = " trim($concatSql)".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype)." or vtiger_groups".$module_from_tablename.".groupname ".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    650. 

  650. 								} elseif($selectedfields[1] == 'status') {//when you use comma seperated values.
    651. 

  651. 									if($selectedfields[2] == 'Calendar_Status')
    652. 

  652. 									$advcolsql[] = "(case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end)".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    653. 

  653. 									elseif($selectedfields[2] == 'HelpDesk_Status')
    654. 

  654. 									$advcolsql[] = "vtiger_troubletickets.status".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    655. 

  655. 								} elseif($selectedfields[1] == 'description') {//when you use comma seperated values.
    656. 

  656. 									if($selectedfields[0]=='vtiger_crmentity'.$this->primarymodule)
    657. 

  657. 										$advcolsql[] = "vtiger_crmentity.description".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    658. 

  658. 									else
    659. 

  659. 										$advcolsql[] = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    660. 

  660. 								} elseif($selectedfields[2] == 'Quotes_Inventory_Manager'){
    661. 

  661. 									$advcolsql[] = ("trim($concatSql)".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype));
    662. 

  662. 								} else {
    663. 

  663. 									$advcolsql[] = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    664. 

  664. 								}
    665. 

  665. 							}
    666. 

  666. 							//If negative logic filter ('not equal to', 'does not contain') is used, 'and' condition should be applied instead of 'or'
    667. 

  667. 							if($comparator == 'n' || $comparator == 'k')
    668. 

  668. 								$advcolumnsql = implode(" and ",$advcolsql);
    669. 

  669. 							else
    670. 

  670. 								$advcolumnsql = implode(" or ",$advcolsql);
    671. 

  671. 							$fieldvalue = " (".$advcolumnsql.") ";
    672. 

  672. 						} elseif(($selectedfields[0] == "vtiger_users".$this->primarymodule || $selectedfields[0] == "vtiger_users".$this->secondarymodule) && $selectedfields[1] == 'user_name') {
    673. 

  673. 							$module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]);
    674. 

  674. 							$fieldvalue = " trim(case when (".$selectedfields[0].".last_name NOT LIKE '') then ".$concatSql." else vtiger_groups".$module_from_tablename.".groupname end) ".$this->getAdvComparator($comparator,trim($value),$datatype);
    675. 

  675. 						} elseif($comparator == 'bw' && count($valuearray) == 2) {
    676. 

  676. 							if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) {
    677. 

  677. 								$fieldvalue = "("."vtiger_crmentity.".$selectedfields[1]." between '".trim($valuearray[0])."' and '".trim($valuearray[1])."')";
    678. 

  678. 							} else {
    679. 

  679. 								$fieldvalue = "(".$selectedfields[0].".".$selectedfields[1]." between '".trim($valuearray[0])."' and '".trim($valuearray[1])."')";
    680. 

  680. 							}
    681. 

  681. 						} elseif($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) {
    682. 

  682. 							$fieldvalue = "vtiger_crmentity.".$selectedfields[1]." ".$this->getAdvComparator($comparator,trim($value),$datatype);
    683. 

  683. 						} elseif($selectedfields[2] == 'Quotes_Inventory_Manager'){
    684. 

  684. 							$fieldvalue = ("trim($concatSql)" . $this->getAdvComparator($comparator,trim($value),$datatype));
    685. 

  685. 						} elseif($selectedfields[1]=='modifiedby') {
    686. 

  686.                             $module_from_tablename = str_replace("vtiger_crmentity","",$selectedfields[0]);
    687. 

  687.                             if($module_from_tablename != '') {
    688. 

  688. 								$tableName = 'vtiger_lastModifiedBy'.$module_from_tablename;
    689. 

  689. 							} else {
    690. 

  690. 								$tableName = 'vtiger_lastModifiedBy'.$this->primarymodule;
    691. 

  691. 							}
    692. 

  692. 							$fieldvalue = getSqlForNameInDisplayFormat(array('last_name'=>"$tableName.last_name",'first_name'=>"$tableName.first_name"), 'Users').
    693. 

  693. 									$this->getAdvComparator($comparator,trim($value),$datatype);
    694. 

  694. 						} elseif($selectedfields[0] == "vtiger_activity" && $selectedfields[1] == 'status') {
    695. 

  695. 							$fieldvalue = "(case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end)".$this->getAdvComparator($comparator,trim($value),$datatype);
    696. 

  696. 						} elseif($comparator == 'e' && (trim($value) == "NULL" || trim($value) == '')) {
    697. 

  697. 							$fieldvalue = "(".$selectedfields[0].".".$selectedfields[1]." IS NULL OR ".$selectedfields[0].".".$selectedfields[1]." = '')";
    698. 

  698. 						} elseif($selectedfields[0] == 'vtiger_inventoryproductrel' && ($selectedfields[1] == 'productid' || $selectedfields[1] == 'serviceid')) {
    699. 

  699. 							if($selectedfields[1] == 'productid'){
    700. 

  700. 								$fieldvalue = "vtiger_products{$this->primarymodule}.productname ".$this->getAdvComparator($comparator,trim($value),$datatype);
    701. 

  701. 							} else if($selectedfields[1] == 'serviceid'){
    702. 

  702. 								$fieldvalue = "vtiger_service{$this->primarymodule}.servicename ".$this->getAdvComparator($comparator,trim($value),$datatype);
    703. 

  703. 							}
    704. 

  704. 						} elseif($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) {
    705. 

  705. 

  706. 							$comparatorValue = $this->getAdvComparator($comparator,trim($value),$datatype);
    707. 

  707. 							$fieldSqls = array();
    708. 

  708. 							$fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo);
    709. 

  709. 							foreach($fieldSqlColumns as $columnSql) {
    710. 

  710. 							 	$fieldSqls[] = $columnSql.$comparatorValue;
    711. 

  711. 							}
    712. 

  712. 							$fieldvalue = ' ('. implode(' OR ', $fieldSqls).') ';
    713. 

  713. 							} else {
    714. 

  714. 							$fieldvalue = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($value),$datatype);
    715. 

  715. 						}
    716. 

  716. 

  717. 						$advfiltergroupsql .= $fieldvalue;
    718. 

  718. 						if(!empty($columncondition)) {
    719. 

  719. 							$advfiltergroupsql .= ' '.$columncondition.' ';
    720. 

  720. 						}
    721. 

  721. 					}
    722. 

  722. 

  723. 				}
    724. 

  724. 

  725. 				if (trim($advfiltergroupsql) != "") {
    726. 

  726. 					$advfiltergroupsql =  "( $advfiltergroupsql ) ";
    727. 

  727. 					if(!empty($groupcondition)) {
    728. 

  728. 						$advfiltergroupsql .= ' '. $groupcondition . ' ';
    729. 

  729. 					}
    730. 

  730. 

  731. 					$advfiltersql .= $advfiltergroupsql;
    732. 

  732. 				}
    733. 

  733. 			}
    734. 

  734. 		}
    735. 

  735. 		if (trim($advfiltersql) != "") $advfiltersql = '('.$advfiltersql.')';
    736. 

  736. 

  737. 		return $advfiltersql;
    738. 

  738. 	}
    739. 

  739. 

  740. 	function getAdvFilterSql($reportid) {
    741. 

  741. 		// Have we initialized information already?
    742. 

  742. 		if($this->_advfiltersql !== false) {
    743. 

  743. 			return $this->_advfiltersql;
    744. 

  744. 		}
    745. 

  745. 		global $log;
    746. 

  746. 

  747. 		$advfilterlist = $this->getAdvFilterList($reportid);
    748. 

  748. 		$advfiltersql = $this->generateAdvFilterSql($advfilterlist);
    749. 

  749. 

  750. 		// Save the information
    751. 

  751. 		$this->_advfiltersql = $advfiltersql;
    752. 

  752. 

  753. 		$log->info("ReportRun :: Successfully returned getAdvFilterSql".$reportid);
    754. 

  754. 		return $advfiltersql;
    755. 

  755. 	}
    756. 

  756. 

  757. 	/** Function to get the Standard filter columns for the reportid
    758. 

  758. 	 *  This function accepts the $reportid datatype Integer
    759. 

  759. 	 *  This function returns  $stdfilterlist Array($columnname => $tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname filtercriteria,
    760. 

  760. 	 *					      $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria,
    761. 

  761. 	 *				      	     )
    762. 

  762. 	 *
    763. 

  763. 	 */
    764. 

  764. 	function getStdFilterList($reportid)
    765. 

  765. 	{
    766. 

  766. 		// Have we initialized information already?
    767. 

  767. 		if($this->_stdfilterlist !== false) {
    768. 

  768. 			return $this->_stdfilterlist;
    769. 

  769. 		}
    770. 

  770. 

  771. 		global $adb, $log;
    772. 

  772. 		$stdfilterlist = array();
    773. 

  773. 

  774. 		$stdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report";
    775. 

  775. 		$stdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid";
    776. 

  776. 		$stdfiltersql .= " where vtiger_report.reportid = ?";
    777. 

  777. 

  778. 		$result = $adb->pquery($stdfiltersql, array($reportid));
    779. 

  779. 		$stdfilterrow = $adb->fetch_array($result);
    780. 

  780. 		if(isset($stdfilterrow)) {
    781. 

  781. 			$fieldcolname = $stdfilterrow["datecolumnname"];
    782. 

  782. 			$datefilter = $stdfilterrow["datefilter"];
    783. 

  783. 			$startdate = $stdfilterrow["startdate"];
    784. 

  784. 			$enddate = $stdfilterrow["enddate"];
    785. 

  785. 

  786. 			if($fieldcolname != "none") {
    787. 

  787. 				$selectedfields = explode(":",$fieldcolname);
    788. 

  788. 				if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule)
    789. 

  789. 					$selectedfields[0] = "vtiger_crmentity";
    790. 

  790. 

  791. 				$moduleFieldLabel = $selectedfields[3];
    792. 

  792. 				list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
    793. 

  793. 				$fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel);
    794. 

  794. 				$typeOfData = $fieldInfo['typeofdata'];
    795. 

  795. 				list($type, $typeOtherInfo) = explode('~', $typeOfData, 2);
    796. 

  796. 

  797. 				if($datefilter != "custom") {
    798. 

  798. 					$startenddate = $this->getStandarFiltersStartAndEndDate($datefilter);
    799. 

  799. 					$startdate = $startenddate[0];
    800. 

  800. 					$enddate = $startenddate[1];
    801. 

  801. 				}
    802. 

  802. 

  803. 				if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $startdate != "" && $enddate != ""
    804. 

  804. 						&& $selectedfields[0] != "" && $selectedfields[1] != "") {
    805. 

  805. 

  806. 					$startDateTime = new DateTimeField($startdate.' '. date('H:i:s'));
    807. 

  807. 					$userStartDate = $startDateTime->getDisplayDate();
    808. 

  808. 					if($type == 'DT') {
    809. 

  809. 						$userStartDate = $userStartDate.' 00:00:00';
    810. 

  810. 					}
    811. 

  811. 					$startDateTime = getValidDBInsertDateTimeValue($userStartDate);
    812. 

  812. 

  813. 					$endDateTime = new DateTimeField($enddate.' '. date('H:i:s'));
    814. 

  814. 					$userEndDate = $endDateTime->getDisplayDate();
    815. 

  815. 					if($type == 'DT') {
    816. 

  816. 						$userEndDate = $userEndDate.' 23:59:00';
    817. 

  817. 					}
    818. 

  818. 					$endDateTime = getValidDBInsertDateTimeValue($userEndDate);
    819. 

  819. 

  820. 					if ($selectedfields[1] == 'birthday') {
    821. 

  821. 						$tableColumnSql = "to_char(".$selectedfields[0].".".$selectedfields[1].", '%m%d')";
    822. 

  822. 						$startDateTime = "to_char('$startDateTime', '%m%d')";
    823. 

  823. 						$endDateTime = "to_char('$endDateTime', '%m%d')";
    824. 

  824. 					} else {
    825. 

  825. 						if($selectedfields[0] == 'vtiger_activity' && ($selectedfields[1] == 'date_start' || $selectedfields[1] == 'due_date')) {
    826. 

  826. 							$tableColumnSql = '';
    827. 

  827. 							if($selectedfields[1] == 'date_start') {
    828. 

  828. 								$tableColumnSql = "to_timestamp(date_start || ' ' || time_start, 'YYYY-MM-DD HH24:MI:SS')";
    829. 

  829. 							} else {
    830. 

  830. 								$tableColumnSql = "to_timestamp(due_date || ' ' || time_end, 'YYYY-MM-DD HH24:MI:SS')";
    831. 

  831. 							}
    832. 

  832. 						} else {
    833. 

  833. 							$tableColumnSql = $selectedfields[0].".".$selectedfields[1];
    834. 

  834. 						}
    835. 

  835. 						$startDateTime = "'$startDateTime'";
    836. 

  836. 						$endDateTime = "'$endDateTime'";
    837. 

  837. 					}
    838. 

  838. 

  839. 					$stdfilterlist[$fieldcolname] = $tableColumnSql." between ".$startDateTime." and ".$endDateTime;
    840. 

  840. 				}
    841. 

  841. 			}
    842. 

  842. 		}
    843. 

  843. 		// Save the information
    844. 

  844. 		$this->_stdfilterlist = $stdfilterlist;
    845. 

  845. 

  846. 		$log->info("ReportRun :: Successfully returned getStdFilterList".$reportid);
    847. 

  847. 		return $stdfilterlist;
    848. 

  848. 	}
    849. 

  849. 

  850. 	/** Function to get the RunTime filter columns for the given $filtercolumn,$filter,$startdate,$enddate
    851. 

  851. 	 *  @ param $filtercolumn : Type String
    852. 

  852. 	 *  @ param $filter : Type String
    853. 

  853. 	 *  @ param $startdate: Type String
    854. 

  854. 	 *  @ param $enddate : Type String
    855. 

  855. 	 *  This function returns  $stdfilterlist Array($columnname => $tablename:$columnname:$fieldlabel=>$tablename.$columnname 'between' $startdate 'and' $enddate)
    856. 

  856. 	 *
    857. 

  857. 	 */
    858. 

  858. 	function RunTimeFilter($filtercolumn,$filter,$startdate,$enddate)
    859. 

  859. 	{
    860. 

  860. 		if($filtercolumn != "none")
    861. 

  861. 		{
    862. 

  862. 			$selectedfields = explode(":",$filtercolumn);
    863. 

  863. 			if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule)
    864. 

  864. 				$selectedfields[0] = "vtiger_crmentity";
    865. 

  865. 			if($filter == "custom")
    866. 

  866. 			{
    867. 

  867. 				if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $startdate != "" &&
    868. 

  868. 						$enddate != "" && $selectedfields[0] != "" && $selectedfields[1] != "") {
    869. 

  869. 					$stdfilterlist[$filtercolumn] = $selectedfields[0].".".$selectedfields[1]." between '".$startdate." 00:00:00' and '".$enddate." 23:59:00'";
    870. 

  870. 				}
    871. 

  871. 			}else
    872. 

  872. 			{
    873. 

  873. 				if($startdate != "" && $enddate != "")
    874. 

  874. 				{
    875. 

  875. 					$startenddate = $this->getStandarFiltersStartAndEndDate($filter);
    876. 

  876. 					if($startenddate[0] != "" && $startenddate[1] != "" && $selectedfields[0] != "" && $selectedfields[1] != "")
    877. 

  877. 					{
    878. 

  878. 						$stdfilterlist[$filtercolumn] = $selectedfields[0].".".$selectedfields[1]." between '".$startenddate[0]." 00:00:00' and '".$startenddate[1]." 23:59:00'";
    879. 

  879. 					}
    880. 

  880. 				}
    881. 

  881. 			}
    882. 

  882. 

  883. 		}
    884. 

  884. 		return $stdfilterlist;
    885. 

  885. 

  886. 	}
    887. 

  887. 

  888. 	/** Function to get the RunTime Advanced filter conditions
    889. 

  889. 	 *  @ param $advft_criteria : Type Array
    890. 

  890. 	 *  @ param $advft_criteria_groups : Type Array
    891. 

  891. 	 *  This function returns  $advfiltersql
    892. 

  892. 	 *
    893. 

  893. 	 */
    894. 

  894. 	function RunTimeAdvFilter($advft_criteria,$advft_criteria_groups) {
    895. 

  895. 		$adb = PearDatabase::getInstance();
    896. 

  896. 

  897. 		$advfilterlist = array();
    898. 

  898. 

  899. 		if(!empty($advft_criteria)) {
    900. 

  900. 			foreach($advft_criteria as $column_index => $column_condition) {
    901. 

  901. 

  902. 				if(empty($column_condition)) continue;
    903. 

  903. 

  904. 				$adv_filter_column = $column_condition["columnname"];
    905. 

  905. 				$adv_filter_comparator = $column_condition["comparator"];
    906. 

  906. 				$adv_filter_value = $column_condition["value"];
    907. 

  907. 				$adv_filter_column_condition = $column_condition["columncondition"];
    908. 

  908. 				$adv_filter_groupid = $column_condition["groupid"];
    909. 

  909. 

  910. 				$column_info = explode(":",$adv_filter_column);
    911. 

  911. 

  912. 				$moduleFieldLabel = $column_info[2];
    913. 

  913. 				$fieldName = $column_info[3];
    914. 

  914. 				list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
    915. 

  915. 				$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
    916. 

  916. 				$fieldType = null;
    917. 

  917. 				if(!empty($fieldInfo)) {
    918. 

  918. 					$field = WebserviceField::fromArray($adb, $fieldInfo);
    919. 

  919. 					$fieldType = $field->getFieldDataType();
    920. 

  920. 				}
    921. 

  921. 

  922. 				if($fieldType == 'currency') {
    923. 

  923. 					// Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion
    924. 

  924. 					if($field->getUIType() == '72') {
    925. 

  925. 						$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true);
    926. 

  926. 					} else {
    927. 

  927. 						$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value);
    928. 

  928. 					}
    929. 

  929. 				}
    930. 

  930. 

  931.                 $temp_val = explode(",",$adv_filter_value);
    932. 

  932.                 if(($column_info[4] == 'D' || ($column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end')
    933. 

  933.                         || ($column_info[4] == 'DT'))
    934. 

  934.                     && ($column_info[4] != '' && $adv_filter_value != '' )) {
    935. 

  935.                     $val = Array();
    936. 

  936.                     for($x=0;$x<count($temp_val);$x++) {
    937. 

  937.                         if($column_info[4] == 'D') {
    938. 

  938. 							$date = new DateTimeField(trim($temp_val[$x]));
    939. 

  939. 							$val[$x] = $date->getDBInsertDateValue();
    940. 

  940. 						} elseif($column_info[4] == 'DT') {
    941. 

  941. 							$date = new DateTimeField(trim($temp_val[$x]));
    942. 

  942. 							$val[$x] = $date->getDBInsertDateTimeValue();
    943. 

  943. 						} else {
    944. 

  944. 							$date = new DateTimeField(trim($temp_val[$x]));
    945. 

  945. 							$val[$x] = $date->getDBInsertTimeValue();
    946. 

  946. 						}
    947. 

  947.                     }
    948. 

  948.                     $adv_filter_value = implode(",",$val);
    949. 

  949.                 }
    950. 

  950. 				$criteria = array();
    951. 

  951. 				$criteria['columnname'] = $adv_filter_column;
    952. 

  952. 				$criteria['comparator'] = $adv_filter_comparator;
    953. 

  953. 				$criteria['value'] = $adv_filter_value;
    954. 

  954. 				$criteria['column_condition'] = $adv_filter_column_condition;
    955. 

  955. 

  956. 				$advfilterlist[$adv_filter_groupid]['columns'][] = $criteria;
    957. 

  957. 			}
    958. 

  958. 

  959. 			foreach($advft_criteria_groups as $group_index => $group_condition_info) {
    960. 

  960. 				if(empty($group_condition_info)) continue;
    961. 

  961. 				if(empty($advfilterlist[$group_index])) continue;
    962. 

  962. 				$advfilterlist[$group_index]['condition'] = $group_condition_info["groupcondition"];
    963. 

  963. 				$noOfGroupColumns = count($advfilterlist[$group_index]['columns']);
    964. 

  964. 				if(!empty($advfilterlist[$group_index]['columns'][$noOfGroupColumns-1]['column_condition'])) {
    965. 

  965. 					$advfilterlist[$group_index]['columns'][$noOfGroupColumns-1]['column_condition'] = '';
    966. 

  966. 				}
    967. 

  967. 			}
    968. 

  968. 			$noOfGroups = count($advfilterlist);
    969. 

  969. 			if(!empty($advfilterlist[$noOfGroups]['condition'])) {
    970. 

  970. 				$advfilterlist[$noOfGroups]['condition'] = '';
    971. 

  971. 			}
    972. 

  972. 

  973. 			$advfiltersql = $this->generateAdvFilterSql($advfilterlist);
    974. 

  974. 		}
    975. 

  975. 		return $advfiltersql;
    976. 

  976. 

  977. 	}
    978. 

  978. 

  979. 	/** Function to get standardfilter for the given reportid
    980. 

  980. 	 *  @ param $reportid : Type Integer
    981. 

  981. 	 *  returns the query of columnlist for the selected columns
    982. 

  982. 	 */
    983. 

  983. 

  984. 	function getStandardCriterialSql($reportid)
    985. 

  985. 	{
    986. 

  986. 		global $adb;
    987. 

  987. 		global $modules;
    988. 

  988. 		global $log;
    989. 

  989. 

  990. 		$sreportstdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report";
    991. 

  991. 		$sreportstdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid";
    992. 

  992. 		$sreportstdfiltersql .= " where vtiger_report.reportid = ?";
    993. 

  993. 

  994. 		$result = $adb->pquery($sreportstdfiltersql, array($reportid));
    995. 

  995. 		$noofrows = $adb->num_rows($result);
    996. 

  996. 

  997. 		for($i=0; $i<$noofrows; $i++) {
    998. 

  998. 			$fieldcolname = $adb->query_result($result,$i,"datecolumnname");
    999. 

  999. 			$datefilter = $adb->query_result($result,$i,"datefilter");
    1000. 

  1000. 			$startdate = $adb->query_result($result,$i,"startdate");
    1001. 

  1001. 			$enddate = $adb->query_result($result,$i,"enddate");
    1002. 

  1002. 

  1003. 			if($fieldcolname != "none") {
    1004. 

  1004. 				$selectedfields = explode(":",$fieldcolname);
    1005. 

  1005. 				if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule)
    1006. 

  1006. 					$selectedfields[0] = "vtiger_crmentity";
    1007. 

  1007. 				if($datefilter == "custom") {
    1008. 

  1008. 

  1009. 					if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $selectedfields[0] != "" && $selectedfields[1] != ""
    1010. 

  1010. 							&& $startdate != '' && $enddate != '') {
    1011. 

  1011. 

  1012. 						$startDateTime = new DateTimeField($startdate.' '. date('H:i:s'));
    1013. 

  1013. 						$startdate = $startDateTime->getDisplayDate();
    1014. 

  1014. 						$endDateTime = new DateTimeField($enddate.' '. date('H:i:s'));
    1015. 

  1015. 						$enddate = $endDateTime->getDisplayDate();
    1016. 

  1016. 

  1017. 						$sSQL .= $selectedfields[0].".".$selectedfields[1]." between '".$startdate."' and '".$enddate."'";
    1018. 

  1018. 					}
    1019. 

  1019. 				} else {
    1020. 

  1020. 

  1021. 					$startenddate = $this->getStandarFiltersStartAndEndDate($datefilter);
    1022. 

  1022. 

  1023. 					$startDateTime = new DateTimeField($startenddate[0].' '. date('H:i:s'));
    1024. 

  1024. 					$startdate = $startDateTime->getDisplayDate();
    1025. 

  1025. 					$endDateTime = new DateTimeField($startenddate[1].' '. date('H:i:s'));
    1026. 

  1026. 					$enddate = $endDateTime->getDisplayDate();
    1027. 

  1027. 

  1028. 					if($startenddate[0] != "" && $startenddate[1] != "" && $selectedfields[0] != "" && $selectedfields[1] != "") {
    1029. 

  1029. 						$sSQL .= $selectedfields[0].".".$selectedfields[1]." between '".$startdate."' and '".$enddate."'";
    1030. 

  1030. 					}
    1031. 

  1031. 				}
    1032. 

  1032. 			}
    1033. 

  1033. 		}
    1034. 

  1034. 		$log->info("ReportRun :: Successfully returned getStandardCriterialSql".$reportid);
    1035. 

  1035. 		return $sSQL;
    1036. 

  1036. 	}
    1037. 

  1037. 

  1038. 	/** Function to get standardfilter startdate and enddate for the given type
    1039. 

  1039. 	 *  @ param $type : Type String
    1040. 

  1040. 	 *  returns the $datevalue Array in the given format
    1041. 

  1041. 	 * 		$datevalue = Array(0=>$startdate,1=>$enddate)
    1042. 

  1042. 	 */
    1043. 

  1043. 

  1044. 

  1045. 	function getStandarFiltersStartAndEndDate($type)
    1046. 

  1046. 	{
    1047. 

  1047. 		$today = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d"), date("Y")));
    1048. 

  1048. 		$tomorrow  = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+1, date("Y")));
    1049. 

  1049. 		$yesterday  = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-1, date("Y")));
    1050. 

  1050. 

  1051. 		$currentmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m"), "01",   date("Y")));
    1052. 

  1052. 		$currentmonth1 = date("Y-m-t");
    1053. 

  1053. 		$lastmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")-1, "01",   date("Y")));
    1054. 

  1054. 		$lastmonth1 = date("Y-m-t", strtotime("-1 Month"));
    1055. 

  1055. 		$nextmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")+1, "01",   date("Y")));
    1056. 

  1056. 		$nextmonth1 = date("Y-m-t", strtotime("+1 Month"));
    1057. 

  1057. 

  1058. 		$lastweek0 = date("Y-m-d",strtotime("-2 week Sunday"));
    1059. 

  1059. 		$lastweek1 = date("Y-m-d",strtotime("-1 week Saturday"));
    1060. 

  1060. 

  1061. 		$thisweek0 = date("Y-m-d",strtotime("-1 week Sunday"));
    1062. 

  1062. 		$thisweek1 = date("Y-m-d",strtotime("this Saturday"));
    1063. 

  1063. 

  1064. 		$nextweek0 = date("Y-m-d",strtotime("this Sunday"));
    1065. 

  1065. 		$nextweek1 = date("Y-m-d",strtotime("+1 week Saturday"));
    1066. 

  1066. 

  1067. 		$next7days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+6, date("Y")));
    1068. 

  1068. 		$next30days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+29, date("Y")));
    1069. 

  1069. 		$next60days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+59, date("Y")));
    1070. 

  1070. 		$next90days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+89, date("Y")));
    1071. 

  1071. 		$next120days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+119, date("Y")));
    1072. 

  1072. 

  1073. 		$last7days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-6, date("Y")));
    1074. 

  1074. 		$last30days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-29, date("Y")));
    1075. 

  1075. 		$last60days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-59, date("Y")));
    1076. 

  1076. 		$last90days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-89, date("Y")));
    1077. 

  1077. 		$last120days = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")-119, date("Y")));
    1078. 

  1078. 

  1079. 		$currentFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01",   date("Y")));
    1080. 

  1080. 		$currentFY1 = date("Y-m-t",mktime(0, 0, 0, "12", date("d"),   date("Y")));
    1081. 

  1081. 		$lastFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01",   date("Y")-1));
    1082. 

  1082. 		$lastFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")-1));
    1083. 

  1083. 		$nextFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01",   date("Y")+1));
    1084. 

  1084. 		$nextFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")+1));
    1085. 

  1085. 

  1086. 		if(date("m") <= 3)
    1087. 

  1087. 		{
    1088. 

  1088. 			$cFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y")));
    1089. 

  1089. 			$cFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y")));
    1090. 

  1090. 			$nFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y")));
    1091. 

  1091. 			$nFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y")));
    1092. 

  1092. 			$pFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y")-1));
    1093. 

  1093. 			$pFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y")-1));
    1094. 

  1094. 		}else if(date("m") > 3 and date("m") <= 6)
    1095. 

  1095. 		{
    1096. 

  1096. 			$pFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y")));
    1097. 

  1097. 			$pFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y")));
    1098. 

  1098. 			$cFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y")));
    1099. 

  1099. 			$cFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y")));
    1100. 

  1100. 			$nFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y")));
    1101. 

  1101. 			$nFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y")));
    1102. 

  1102. 

  1103. 		}else if(date("m") > 6 and date("m") <= 9)
    1104. 

  1104. 		{
    1105. 

  1105. 			$nFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y")));
    1106. 

  1106. 			$nFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y")));
    1107. 

  1107. 			$pFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y")));
    1108. 

  1108. 			$pFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y")));
    1109. 

  1109. 			$cFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y")));
    1110. 

  1110. 			$cFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y")));
    1111. 

  1111. 		}
    1112. 

  1112. 		else if(date("m") > 9 and date("m") <= 12)
    1113. 

  1113. 		{
    1114. 

  1114. 			$nFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y")+1));
    1115. 

  1115. 			$nFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y")+1));
    1116. 

  1116. 			$pFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y")));
    1117. 

  1117. 			$pFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y")));
    1118. 

  1118. 			$cFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y")));
    1119. 

  1119. 			$cFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y")));
    1120. 

  1120. 

  1121. 		}
    1122. 

  1122. 

  1123. 		if($type == "today" )
    1124. 

  1124. 		{
    1125. 

  1125. 

  1126. 			$datevalue[0] = $…
    1127.

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