PageRenderTime 74ms CodeModel.GetById 22ms RepoModel.GetById 1ms app.codeStats 1ms

/modules/Reports/ReportRun.php

https://bitbucket.org/yousef_fadila/vtiger
PHP | 3331 lines | 2792 code | 320 blank | 219 comment | 977 complexity | e66695f58dc94f4507e027cf05742c8f 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('data/CRMEntity.php');
    19. 

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

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

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

  22. 

  23. class ReportRun extends CRMEntity
    24. 

  24. {
    25. 

  25. 

  26. 	var $primarymodule;
    27. 

  27. 	var $secondarymodule;
    28. 

  28. 	var $orderbylistsql;
    29. 

  29. 	var $orderbylistcolumns;
    30. 

  30. 

  31. 	var $selectcolumns;
    32. 

  32. 	var $groupbylist;
    33. 

  33. 	var $reporttype;
    34. 

  34. 	var $reportname;
    35. 

  35. 	var $totallist;
    36. 

  36. 

  37. 	var $_groupinglist  = false;
    38. 

  38. 	var $_columnslist    = false;
    39. 

  39. 	var	$_stdfilterlist = false;
    40. 

  40. 	var	$_columnstotallist = false;
    41. 

  41. 	var	$_advfiltersql = false;
    42. 

  42. 

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

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

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

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

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

  48. 						);
    49. 

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

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

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

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

  53. 								);
    54. 

  54. 

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

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

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

  58. 	 */
    59. 

  59. 	function ReportRun($reportid)
    60. 

  60. 	{
    61. 

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

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

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

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

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

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

  67. 	}
    68. 

  68. 

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

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

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

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

  73. 	 *					      					|
    74. 

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

  75. 	 *				      	     )
    76. 

  76. 	 *
    77. 

  77. 	 */
    78. 

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

  79. 	{
    80. 

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

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

  82. 			return $this->_columnslist;
    83. 

  83. 		}
    84. 

  84. 

  85. 		global $adb;
    86. 

  86. 		global $modules;
    87. 

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

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

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

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

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

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

  93. 		$permitted_fields = Array();
    94. 

  94. 

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

  96. 		{
    97. 

  97. 			$fieldname ="";
    98. 

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

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

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

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

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

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

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

  105. 			{
    106. 

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

  107. 			}
    108. 

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

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

  110. 			}
    111. 

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

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

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

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

  115. 				continue;
    116. 

  116. 			}
    117. 

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

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

  119. 

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

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

  122. 			}
    123. 

  123. 

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

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

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

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

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

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

  130. 				$mod = $module;
    131. 

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

  132. 			} else {
    133. 

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

  134. 				array_shift($fld_arr);
    135. 

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

  136. 			}
    137. 

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

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

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

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

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

  142. 				continue;
    143. 

  143. 			}
    144. 

  144. 

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

  146. 			{
    147. 

  147. 				continue;
    148. 

  148. 			}
    149. 

  149. 			else
    150. 

  150. 			{
    151. 

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

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

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

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

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

  156. 

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

  158. 				{
    159. 

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

  160. 					{
    161. 

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

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

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

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

  165. 						else
    166. 

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

  167. 					}
    168. 

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

  169. 					{
    170. 

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

  171. 					}
    172. 

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

  173. 					{
    174. 

  174.                                             $columnslist[$fieldcolname] = "cast(concat(vtiger_activity.date_start,'  ',vtiger_activity.time_start) as DATETIME) as Calendar_Start_Date_and_Time";
    175. 

  175. 					}
    176. 

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

  177. 					{
    178. 

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

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

  180. 							$condition = "and vtiger_crmentity".$module.".crmid!=''";
    181. 

  181. 						} else {
    182. 

  182. 							$condition = "and vtiger_crmentity.crmid!=''";
    183. 

  183. 						}
    184. 

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

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

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

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

  188. 

  189. 					}
    190. 

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

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

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

  193. 					}
    194. 

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

  195. 					{
    196. 

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

  197. 					}
    198. 

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

  199. 					{
    200. 

  200. 						$columnslist[$fieldcolname] = "concat(".$selectedfields[0].".currency_id,'::',innerProduct.actual_unit_price) as '". $header_label ."'";
    201. 

  201. 					}
    202. 

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

  203. 						$columnslist[$fieldcolname] = "concat(".$selectedfields[0].".currency_id,'::',".$selectedfields[0].".".$selectedfields[1].") as '" . $header_label ."'";
    204. 

  204. 					}
    205. 

  205. 					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
    206. 

  206. 					{
    207. 

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

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

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

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

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

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

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

  214. 							$columnslist[$fieldcolname] = "case ".$selectedfields[0].".".$selectedfields[1]." when '' then '-' else concat(".$selectedfields[0].".".$selectedfields[1]."/1024,'  ','KB') end as '$selectedfields[2]'";
    215. 

  215. 						}
    216. 

  216. 					}
    217. 

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

  218. 					{
    219. 

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

  220. 							$columnslist[$fieldcolname] = " case when (vtiger_inventoryproductrel{$module}.discount_amount != '') 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 ."'";
    221. 

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

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

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

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

  225. 						} else {
    226. 

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

  227. 						}
    228. 

  228. 					}
    229. 

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

  230. 					{
    231. 

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

  232. 					}
    233. 

  233. 					else
    234. 

  234. 					{
    235. 

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

  236. 					}
    237. 

  237. 				}
    238. 

  238. 				else
    239. 

  239. 				{
    240. 

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

  241. 				}
    242. 

  242. 			}
    243. 

  243. 		}
    244. 

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

  245. 		// Save the information
    246. 

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

  247. 

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

  249. 		return $columnslist;
    250. 

  250. 	}
    251. 

  251. 

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

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

  254. 	 *  returns permitted fields in array format
    255. 

  255. 	 */
    256. 

  256. 	function getaccesfield($module) {
    257. 

  257. 		global $current_user;
    258. 

  258. 		global $adb;
    259. 

  259. 		$access_fields = Array();
    260. 

  260. 

  261. 		$profileList = getCurrentUserProfileList();
    262. 

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

  263. 		$params = array();
    264. 

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

  265. 		{
    266. 

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

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

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

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

  270. 			} else {
    271. 

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

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

  273. 			}
    274. 

  274. 		}
    275. 

  275. 		else
    276. 

  276. 		{
    277. 

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

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

  279. 				$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
    280. 

  280. 								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";
    281. 

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

  282. 			} else {
    283. 

  283. 				$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
    284. 

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

  285. 			}
    286. 

  286. 		}
    287. 

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

  288. 

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

  290. 		{
    291. 

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

  292. 		}
    293. 

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

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

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

  296. 		return $access_fields;
    297. 

  297. 	}
    298. 

  298. 

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

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

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

  302. 	 */
    303. 

  303. 	function getEscapedColumns($selectedfields) {
    304. 

  304. 

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

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

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

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

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

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

  311. 

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

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

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

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

  316. 

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

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

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

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

  321. 				$queryColumn = "(CASE WHEN $tableName.$columnName NOT LIKE '' THEN (CASE";
    322. 

  322. 				foreach($fieldSqlColumns as $columnSql) {
    323. 

  323. 					$queryColumn .= " WHEN $columnSql NOT LIKE '' THEN $columnSql";
    324. 

  324. 				}
    325. 

  325. 				$queryColumn .= " ELSE '' END) ELSE '' END) AS $moduleFieldLabel";
    326. 

  326. 			}
    327. 

  327. 		}
    328. 

  328. 		return $queryColumn;
    329. 

  329. 	}
    330. 

  330. 

  331. 	/** Function to get selectedcolumns for the given reportid
    332. 

  332. 	 *  @ param $reportid : Type Integer
    333. 

  333. 	 *  returns the query of columnlist for the selected columns
    334. 

  334. 	 */
    335. 

  335. 	function getSelectedColumnsList($reportid)
    336. 

  336. 	{
    337. 

  337. 

  338. 		global $adb;
    339. 

  339. 		global $modules;
    340. 

  340. 		global $log;
    341. 

  341. 

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

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

  344. 		$ssql .= " order by vtiger_selectcolumn.columnindex";
    345. 

  345. 

  346. 		$result = $adb->pquery($ssql, array($reportid));
    347. 

  347. 		$noofrows = $adb->num_rows($result);
    348. 

  348. 

  349. 		if ($this->orderbylistsql != "")
    350. 

  350. 		{
    351. 

  351. 			$sSQL .= $this->orderbylistsql.", ";
    352. 

  352. 		}
    353. 

  353. 

  354. 		for($i=0; $i<$noofrows; $i++)
    355. 

  355. 		{
    356. 

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

  357. 			$ordercolumnsequal = true;
    358. 

  358. 			if($fieldcolname != "")
    359. 

  359. 			{
    360. 

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

  361. 				{
    362. 

  362. 					if($this->orderbylistcolumns[$j] == $fieldcolname)
    363. 

  363. 					{
    364. 

  364. 						$ordercolumnsequal = false;
    365. 

  365. 						break;
    366. 

  366. 					}else
    367. 

  367. 					{
    368. 

  368. 						$ordercolumnsequal = true;
    369. 

  369. 					}
    370. 

  370. 				}
    371. 

  371. 				if($ordercolumnsequal)
    372. 

  372. 				{
    373. 

  373. 					$selectedfields = explode(":",$fieldcolname);
    374. 

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

  375. 						$selectedfields[0] = "vtiger_crmentity";
    376. 

  376. 					$sSQLList[] = $selectedfields[0].".".$selectedfields[1]." '".$selectedfields[2]."'";
    377. 

  377. 				}
    378. 

  378. 			}
    379. 

  379. 		}
    380. 

  380. 		$sSQL .= implode(",",$sSQLList);
    381. 

  381. 

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

  383. 		return $sSQL;
    384. 

  384. 	}
    385. 

  385. 

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

  387. 	 *  @ param $comparator : Type String
    388. 

  388. 	 *  @ param $value : Type String
    389. 

  389. 	 *  returns the check query for the comparator
    390. 

  390. 	 */
    391. 

  391. 	function getAdvComparator($comparator,$value,$datatype="")
    392. 

  392. 	{
    393. 

  393. 

  394. 		global $log,$adb,$default_charset,$ogReport;
    395. 

  395. 		$value=html_entity_decode(trim($value),ENT_QUOTES,$default_charset);
    396. 

  396. 		$value_len = strlen($value);
    397. 

  397. 		$is_field = false;
    398. 

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

  399. 			$temp = str_replace('$','',$value);
    400. 

  400. 			$is_field = true;
    401. 

  401. 		}
    402. 

  402. 		if($datatype=='C'){
    403. 

  403. 			$value = str_replace("yes","1",str_replace("no","0",$value));
    404. 

  404. 		}
    405. 

  405. 

  406. 		if($is_field==true){
    407. 

  407. 			$value = $this->getFilterComparedField($temp);
    408. 

  408. 		}
    409. 

  409. 		if($comparator == "e")
    410. 

  410. 		{
    411. 

  411. 			if(trim($value) == "NULL")
    412. 

  412. 			{
    413. 

  413. 				$rtvalue = " is NULL";
    414. 

  414. 			}elseif(trim($value) != "")
    415. 

  415. 			{
    416. 

  416. 				$rtvalue = " = ".$adb->quote($value);
    417. 

  417. 			}elseif(trim($value) == "" && $datatype == "V")
    418. 

  418. 			{
    419. 

  419. 				$rtvalue = " = ".$adb->quote($value);
    420. 

  420. 			}else
    421. 

  421. 			{
    422. 

  422. 				$rtvalue = " is NULL";
    423. 

  423. 			}
    424. 

  424. 		}
    425. 

  425. 		if($comparator == "n")
    426. 

  426. 		{
    427. 

  427. 			if(trim($value) == "NULL")
    428. 

  428. 			{
    429. 

  429. 				$rtvalue = " is NOT NULL";
    430. 

  430. 			}elseif(trim($value) != "")
    431. 

  431. 			{
    432. 

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

  433. 			}elseif(trim($value) == "" && $datatype == "V")
    434. 

  434. 			{
    435. 

  435. 				$rtvalue = " <> ".$adb->quote($value);
    436. 

  436. 			}else
    437. 

  437. 			{
    438. 

  438. 				$rtvalue = " is NOT NULL";
    439. 

  439. 			}
    440. 

  440. 		}
    441. 

  441. 		if($comparator == "s")
    442. 

  442. 		{
    443. 

  443. 			$rtvalue = " like '". formatForSqlLike($value, 2,$is_field) ."'";
    444. 

  444. 		}
    445. 

  445. 		if($comparator == "ew")
    446. 

  446. 		{
    447. 

  447. 			$rtvalue = " like '". formatForSqlLike($value, 1,$is_field) ."'";
    448. 

  448. 		}
    449. 

  449. 		if($comparator == "c")
    450. 

  450. 		{
    451. 

  451. 			$rtvalue = " like '". formatForSqlLike($value,0,$is_field) ."'";
    452. 

  452. 		}
    453. 

  453. 		if($comparator == "k")
    454. 

  454. 		{
    455. 

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

  456. 		}
    457. 

  457. 		if($comparator == "l")
    458. 

  458. 		{
    459. 

  459. 			$rtvalue = " < ".$adb->quote($value);
    460. 

  460. 		}
    461. 

  461. 		if($comparator == "g")
    462. 

  462. 		{
    463. 

  463. 			$rtvalue = " > ".$adb->quote($value);
    464. 

  464. 		}
    465. 

  465. 		if($comparator == "m")
    466. 

  466. 		{
    467. 

  467. 			$rtvalue = " <= ".$adb->quote($value);
    468. 

  468. 		}
    469. 

  469. 		if($comparator == "h")
    470. 

  470. 		{
    471. 

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

  472. 		}
    473. 

  473. 		if($comparator == "b") {
    474. 

  474. 			$rtvalue = " < ".$adb->quote($value);
    475. 

  475. 		}
    476. 

  476. 		if($comparator == "a") {
    477. 

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

  478. 		}
    479. 

  479. 		if($is_field==true){
    480. 

  480. 			$rtvalue = str_replace("'","",$rtvalue);
    481. 

  481. 			$rtvalue = str_replace("\\","",$rtvalue);
    482. 

  482. 		}
    483. 

  483. 		$log->info("ReportRun :: Successfully returned getAdvComparator");
    484. 

  484. 		return $rtvalue;
    485. 

  485. 	}
    486. 

  486. 

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

  488. 	 *  @ param $field : field
    489. 

  489. 	 *  returns the value for the comparator
    490. 

  490. 	 */
    491. 

  491. 	function getFilterComparedField($field){
    492. 

  492. 		global $adb,$ogReport;
    493. 

  493. 			$field = split('#',$field);
    494. 

  494. 			$module = $field[0];
    495. 

  495. 			$fieldname = trim($field[1]);
    496. 

  496. 			$tabid = getTabId($module);
    497. 

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

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

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

  500. 			$typeofdata = $adb->query_result($field_query,0,'typeofdata');
    501. 

  501. 			$fieldtypeofdata=ChangeTypeOfData_Filter($fieldtablename,$fieldcolname,$typeofdata[0]);
    502. 

  502. 			$uitype = $adb->query_result($field_query,0,'uitype');
    503. 

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

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

  505. 			else
    506. 

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

  507. 			*/
    508. 

  508. 			if($uitype == 68 || $uitype == 59)
    509. 

  509. 			{
    510. 

  510. 				$fieldtypeofdata = 'V';
    511. 

  511. 			}
    512. 

  512. 			if($fieldtablename == "vtiger_crmentity")
    513. 

  513. 			{
    514. 

  514. 				$fieldtablename = $fieldtablename.$module;
    515. 

  515. 			}
    516. 

  516. 			if($fieldname == "assigned_user_id")
    517. 

  517. 			{
    518. 

  518. 				$fieldtablename = "vtiger_users".$module;
    519. 

  519. 				$fieldcolname = "user_name";
    520. 

  520. 			}
    521. 

  521.             if($fieldtablename == "vtiger_crmentity" && $fieldname == "modifiedby")
    522. 

  522. 			{
    523. 

  523. 				$fieldtablename = "vtiger_lastModifiedBy".$module;
    524. 

  524. 				$fieldcolname = "user_name";
    525. 

  525. 			}
    526. 

  526. 			if($fieldname == "assigned_user_id1")
    527. 

  527. 			{
    528. 

  528. 				$fieldtablename = "vtiger_usersRel1";
    529. 

  529. 				$fieldcolname = "user_name";
    530. 

  530. 			}
    531. 

  531. 			$value = $fieldtablename.".".$fieldcolname;
    532. 

  532. 		return $value;
    533. 

  533. 	}
    534. 

  534. 	/** Function to get the advanced filter columns for the reportid
    535. 

  535. 	 *  This function accepts the $reportid
    536. 

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

  537. 	 *					      $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria,
    538. 

  538. 	 *					      					|
    539. 

  539.  	 *					      $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen filtercriteria
    540. 

  540. 	 *				      	     )
    541. 

  541. 	 *
    542. 

  542. 	 */
    543. 

  543. 	 function getAdvFilterList($reportid) {
    544. 

  544. 		global $adb, $log;
    545. 

  545. 

  546. 		$advft_criteria = array();
    547. 

  547. 

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

  549. 		$groupsresult = $adb->pquery($sql, array($reportid));
    550. 

  550. 

  551. 		$i = 1;
    552. 

  552. 		$j = 0;
    553. 

  553. 		while($relcriteriagroup = $adb->fetch_array($groupsresult)) {
    554. 

  554. 			$groupId = $relcriteriagroup["groupid"];
    555. 

  555. 			$groupCondition = $relcriteriagroup["group_condition"];
    556. 

  556. 

  557. 			$ssql = 'select vtiger_relcriteria.* from vtiger_report
    558. 

  558. 						inner join vtiger_relcriteria on vtiger_relcriteria.queryid = vtiger_report.queryid
    559. 

  559. 						left join vtiger_relcriteria_grouping on vtiger_relcriteria.queryid = vtiger_relcriteria_grouping.queryid
    560. 

  560. 								and vtiger_relcriteria.groupid = vtiger_relcriteria_grouping.groupid';
    561. 

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

  562. 

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

  564. 			$noOfColumns = $adb->num_rows($result);
    565. 

  565. 			if($noOfColumns <= 0) continue;
    566. 

  566. 

  567. 			while($relcriteriarow = $adb->fetch_array($result)) {
    568. 

  568. 				$columnIndex = $relcriteriarow["columnindex"];
    569. 

  569. 				$criteria = array();
    570. 

  570. 				$criteria['columnname'] = html_entity_decode($relcriteriarow["columnname"]);
    571. 

  571. 				$criteria['comparator'] = $relcriteriarow["comparator"];
    572. 

  572. 				$advfilterval = $relcriteriarow["value"];
    573. 

  573. 				$col = explode(":",$relcriteriarow["columnname"]);
    574. 

  574. 				$criteria['value'] = $advfilterval;
    575. 

  575. 				$criteria['column_condition'] = $relcriteriarow["column_condition"];
    576. 

  576. 

  577. 				$advft_criteria[$i]['columns'][$j] = $criteria;
    578. 

  578. 				$advft_criteria[$i]['condition'] = $groupCondition;
    579. 

  579. 				$j++;
    580. 

  580. 			}
    581. 

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

  582. 				$advft_criteria[$i]['columns'][$j-1]['column_condition'] = '';
    583. 

  583. 			}
    584. 

  584. 			$i++;
    585. 

  585. 		}
    586. 

  586. 		// Clear the condition (and/or) for last group, if any.
    587. 

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

  588. 		return $advft_criteria;
    589. 

  589. 	}
    590. 

  590. 

  591. 	function generateAdvFilterSql($advfilterlist) {
    592. 

  592. 

  593. 		global $adb;
    594. 

  594. 

  595. 		$advfiltersql = "";
    596. 

  596. 

  597. 		foreach($advfilterlist as $groupindex => $groupinfo) {
    598. 

  598. 			$groupcondition = $groupinfo['condition'];
    599. 

  599. 			$groupcolumns = $groupinfo['columns'];
    600. 

  600. 

  601. 			if(count($groupcolumns) > 0) {
    602. 

  602. 

  603. 				$advfiltergroupsql = "";
    604. 

  604. 				foreach($groupcolumns as $columnindex => $columninfo) {
    605. 

  605. 					$fieldcolname = $columninfo["columnname"];
    606. 

  606. 					$comparator = $columninfo["comparator"];
    607. 

  607. 					$value = $columninfo["value"];
    608. 

  608. 					$columncondition = $columninfo["column_condition"];
    609. 

  609. 

  610. 					if($fieldcolname != "" && $comparator != "") {
    611. 

  611. 						$selectedfields = explode(":",$fieldcolname);
    612. 

  612. 						$moduleFieldLabel = $selectedfields[2];
    613. 

  613. 						list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
    614. 

  614. 						$fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel);
    615. 

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

  616. 						// Added to handle the crmentity table name for Primary module
    617. 

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

  618.                             $selectedfields[0] = "vtiger_crmentity";
    619. 

  619.                         }
    620. 

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

  621. 						if($selectedfields[4] == 'C') {
    622. 

  622. 							if(strcasecmp(trim($value),"yes")==0)
    623. 

  623. 								$value="1";
    624. 

  624. 							if(strcasecmp(trim($value),"no")==0)
    625. 

  625. 								$value="0";
    626. 

  626. 						}
    627. 

  627. 						$valuearray = explode(",",trim($value));
    628. 

  628. 						$datatype = (isset($selectedfields[4])) ? $selectedfields[4] : "";
    629. 

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

  630. 

  631. 							$advcolumnsql = "";
    632. 

  632. 							for($n=0;$n<count($valuearray);$n++) {
    633. 

  633. 

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

  635. 									$module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]);
    636. 

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

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

  638. 									if($selectedfields[2] == 'Calendar_Status')
    639. 

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

  640. 									elseif($selectedfields[2] == 'HelpDesk_Status')
    641. 

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

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

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

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

  645. 									else
    646. 

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

  647. 								} elseif($selectedfields[2] == 'Quotes_Inventory_Manager'){
    648. 

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

  649. 								} else {
    650. 

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

  651. 								}
    652. 

  652. 							}
    653. 

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

  654. 							if($comparator == 'n' || $comparator == 'k')
    655. 

  655. 								$advcolumnsql = implode(" and ",$advcolsql);
    656. 

  656. 							else
    657. 

  657. 								$advcolumnsql = implode(" or ",$advcolsql);
    658. 

  658. 							$fieldvalue = " (".$advcolumnsql.") ";
    659. 

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

  660. 							$module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]);
    661. 

  661. 							$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);
    662. 

  662. 						} elseif($comparator == 'bw' && count($valuearray) == 2) {
    663. 

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

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

  665. 							} else {
    666. 

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

  667. 							}
    668. 

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

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

  670. 						} elseif($selectedfields[2] == 'Quotes_Inventory_Manager'){
    671. 

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

  672. 						} elseif($selectedfields[1]=='modifiedby') {
    673. 

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

  674.                             if($module_from_tablename != '') {
    675. 

  675. 								$tableName = 'vtiger_lastModifiedBy'.$module_from_tablename;
    676. 

  676. 							} else {
    677. 

  677. 								$tableName = 'vtiger_lastModifiedBy'.$this->primarymodule;
    678. 

  678. 							}
    679. 

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

  680. 									$this->getAdvComparator($comparator,trim($value),$datatype);
    681. 

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

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

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

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

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

  686. 							if($selectedfields[1] == 'productid'){
    687. 

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

  688. 							} else if($selectedfields[1] == 'serviceid'){
    689. 

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

  690. 							}
    691. 

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

  692. 

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

  694. 							$fieldSqls = array();
    695. 

  695. 							$fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo);
    696. 

  696. 							foreach($fieldSqlColumns as $columnSql) {
    697. 

  697. 							 	$fieldSqls[] = $columnSql.$comparatorValue;
    698. 

  698. 							}
    699. 

  699. 							$fieldvalue = ' ('. implode(' OR ', $fieldSqls).') ';
    700. 

  700. 							} else {
    701. 

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

  702. 						}
    703. 

  703. 

  704. 						$advfiltergroupsql .= $fieldvalue;
    705. 

  705. 						if(!empty($columncondition)) {
    706. 

  706. 							$advfiltergroupsql .= ' '.$columncondition.' ';
    707. 

  707. 						}
    708. 

  708. 					}
    709. 

  709. 

  710. 				}
    711. 

  711. 

  712. 				if (trim($advfiltergroupsql) != "") {
    713. 

  713. 					$advfiltergroupsql =  "( $advfiltergroupsql ) ";
    714. 

  714. 					if(!empty($groupcondition)) {
    715. 

  715. 						$advfiltergroupsql .= ' '. $groupcondition . ' ';
    716. 

  716. 					}
    717. 

  717. 

  718. 					$advfiltersql .= $advfiltergroupsql;
    719. 

  719. 				}
    720. 

  720. 			}
    721. 

  721. 		}
    722. 

  722. 		if (trim($advfiltersql) != "") $advfiltersql = '('.$advfiltersql.')';
    723. 

  723. 

  724. 		return $advfiltersql;
    725. 

  725. 	}
    726. 

  726. 

  727. 	function getAdvFilterSql($reportid) {
    728. 

  728. 		// Have we initialized information already?
    729. 

  729. 		if($this->_advfiltersql !== false) {
    730. 

  730. 			return $this->_advfiltersql;
    731. 

  731. 		}
    732. 

  732. 		global $log;
    733. 

  733. 

  734. 		$advfilterlist = $this->getAdvFilterList($reportid);
    735. 

  735. 		$advfiltersql = $this->generateAdvFilterSql($advfilterlist);
    736. 

  736. 

  737. 		// Save the information
    738. 

  738. 		$this->_advfiltersql = $advfiltersql;
    739. 

  739. 

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

  741. 		return $advfiltersql;
    742. 

  742. 	}
    743. 

  743. 

  744. 	/** Function to get the Standard filter columns for the reportid
    745. 

  745. 	 *  This function accepts the $reportid datatype Integer
    746. 

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

  747. 	 *					      $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria,
    748. 

  748. 	 *				      	     )
    749. 

  749. 	 *
    750. 

  750. 	 */
    751. 

  751. 	function getStdFilterList($reportid)
    752. 

  752. 	{
    753. 

  753. 		// Have we initialized information already?
    754. 

  754. 		if($this->_stdfilterlist !== false) {
    755. 

  755. 			return $this->_stdfilterlist;
    756. 

  756. 		}
    757. 

  757. 

  758. 		global $adb, $log;
    759. 

  759. 		$stdfilterlist = array();
    760. 

  760. 

  761. 		$stdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report";
    762. 

  762. 		$stdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid";
    763. 

  763. 		$stdfiltersql .= " where vtiger_report.reportid = ?";
    764. 

  764. 

  765. 		$result = $adb->pquery($stdfiltersql, array($reportid));
    766. 

  766. 		$stdfilterrow = $adb->fetch_array($result);
    767. 

  767. 		if(isset($stdfilterrow)) {
    768. 

  768. 			$fieldcolname = $stdfilterrow["datecolumnname"];
    769. 

  769. 			$datefilter = $stdfilterrow["datefilter"];
    770. 

  770. 			$startdate = $stdfilterrow["startdate"];
    771. 

  771. 			$enddate = $stdfilterrow["enddate"];
    772. 

  772. 

  773. 			if($fieldcolname != "none") {
    774. 

  774. 				$selectedfields = explode(":",$fieldcolname);
    775. 

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

  776. 					$selectedfields[0] = "vtiger_crmentity";
    777. 

  777. 

  778. 				$moduleFieldLabel = $selectedfields[3];
    779. 

  779. 				list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
    780. 

  780. 				$fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel);
    781. 

  781. 				$typeOfData = $fieldInfo['typeofdata'];
    782. 

  782. 				list($type, $typeOtherInfo) = explode('~', $typeOfData, 2);
    783. 

  783. 

  784. 				if($datefilter != "custom") {
    785. 

  785. 					$startenddate = $this->getStandarFiltersStartAndEndDate($datefilter);
    786. 

  786. 					$startdate = $startenddate[0];
    787. 

  787. 					$enddate = $startenddate[1];
    788. 

  788. 				}
    789. 

  789. 

  790. 				if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $startdate != "" && $enddate != ""
    791. 

  791. 						&& $selectedfields[0] != "" && $selectedfields[1] != "") {
    792. 

  792. 

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

  794. 					$userStartDate = $startDateTime->getDisplayDate();
    795. 

  795. 					if($type == 'DT') {
    796. 

  796. 						$userStartDate = $userStartDate.' 00:00:00';
    797. 

  797. 					}
    798. 

  798. 					$startDateTime = getValidDBInsertDateTimeValue($userStartDate);
    799. 

  799. 

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

  801. 					$userEndDate = $endDateTime->getDisplayDate();
    802. 

  802. 					if($type == 'DT') {
    803. 

  803. 						$userEndDate = $userEndDate.' 23:59:00';
    804. 

  804. 					}
    805. 

  805. 					$endDateTime = getValidDBInsertDateTimeValue($userEndDate);
    806. 

  806. 

  807. 					if ($selectedfields[1] == 'birthday') {
    808. 

  808. 						$tableColumnSql = "DATE_FORMAT(".$selectedfields[0].".".$selectedfields[1].", '%m%d')";
    809. 

  809. 						$startDateTime = "DATE_FORMAT('$startDateTime', '%m%d')";
    810. 

  810. 						$endDateTime = "DATE_FORMAT('$endDateTime', '%m%d')";
    811. 

  811. 					} else {
    812. 

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

  813. 							$tableColumnSql = '';
    814. 

  814. 							if($selectedfields[1] == 'date_start') {
    815. 

  815. 								$tableColumnSql = "CAST((CONCAT(date_start,' ',time_start)) AS DATETIME)";
    816. 

  816. 							} else {
    817. 

  817. 								$tableColumnSql = "CAST((CONCAT(due_date,' ',time_end)) AS DATETIME)";
    818. 

  818. 							}
    819. 

  819. 						} else {
    820. 

  820. 							$tableColumnSql = $selectedfields[0].".".$selectedfields[1];
    821. 

  821. 						}
    822. 

  822. 						$startDateTime = "'$startDateTime'";
    823. 

  823. 						$endDateTime = "'$endDateTime'";
    824. 

  824. 					}
    825. 

  825. 

  826. 					$stdfilterlist[$fieldcolname] = $tableColumnSql." between ".$startDateTime." and ".$endDateTime;
    827. 

  827. 				}
    828. 

  828. 			}
    829. 

  829. 		}
    830. 

  830. 		// Save the information
    831. 

  831. 		$this->_stdfilterlist = $stdfilterlist;
    832. 

  832. 

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

  834. 		return $stdfilterlist;
    835. 

  835. 	}
    836. 

  836. 

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

  838. 	 *  @ param $filtercolumn : Type String
    839. 

  839. 	 *  @ param $filter : Type String
    840. 

  840. 	 *  @ param $startdate: Type String
    841. 

  841. 	 *  @ param $enddate : Type String
    842. 

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

  843. 	 *
    844. 

  844. 	 */
    845. 

  845. 	function RunTimeFilter($filtercolumn,$filter,$startdate,$enddate)
    846. 

  846. 	{
    847. 

  847. 		if($filtercolumn != "none")
    848. 

  848. 		{
    849. 

  849. 			$selectedfields = explode(":",$filtercolumn);
    850. 

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

  851. 				$selectedfields[0] = "vtiger_crmentity";
    852. 

  852. 			if($filter == "custom")
    853. 

  853. 			{
    854. 

  854. 				if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $startdate != "" &&
    855. 

  855. 						$enddate != "" && $selectedfields[0] != "" && $selectedfields[1] != "") {
    856. 

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

  857. 				}
    858. 

  858. 			}else
    859. 

  859. 			{
    860. 

  860. 				if($startdate != "" && $enddate != "")
    861. 

  861. 				{
    862. 

  862. 					$startenddate = $this->getStandarFiltersStartAndEndDate($filter);
    863. 

  863. 					if($startenddate[0] != "" && $startenddate[1] != "" && $selectedfields[0] != "" && $selectedfields[1] != "")
    864. 

  864. 					{
    865. 

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

  866. 					}
    867. 

  867. 				}
    868. 

  868. 			}
    869. 

  869. 

  870. 		}
    871. 

  871. 		return $stdfilterlist;
    872. 

  872. 

  873. 	}
    874. 

  874. 

  875. 	/** Function to get the RunTime Advanced filter conditions
    876. 

  876. 	 *  @ param $advft_criteria : Type Array
    877. 

  877. 	 *  @ param $advft_criteria_groups : Type Array
    878. 

  878. 	 *  This function returns  $advfiltersql
    879. 

  879. 	 *
    880. 

  880. 	 */
    881. 

  881. 	function RunTimeAdvFilter($advft_criteria,$advft_criteria_groups) {
    882. 

  882. 		$adb = PearDatabase::getInstance();
    883. 

  883. 

  884. 		$advfilterlist = array();
    885. 

  885. 

  886. 		if(!empty($advft_criteria)) {
    887. 

  887. 			foreach($advft_criteria as $column_index => $column_condition) {
    888. 

  888. 

  889. 				if(empty($column_condition)) continue;
    890. 

  890. 

  891. 				$adv_filter_column = $column_condition["columnname"];
    892. 

  892. 				$adv_filter_comparator = $column_condition["comparator"];
    893. 

  893. 				$adv_filter_value = $column_condition["value"];
    894. 

  894. 				$adv_filter_column_condition = $column_condition["columncondition"];
    895. 

  895. 				$adv_filter_groupid = $column_condition["groupid"];
    896. 

  896. 

  897. 				$column_info = explode(":",$adv_filter_column);
    898. 

  898. 

  899. 				$moduleFieldLabel = $column_info[2];
    900. 

  900. 				$fieldName = $column_info[3];
    901. 

  901. 				list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
    902. 

  902. 				$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
    903. 

  903. 				$fieldType = null;
    904. 

  904. 				if(!empty($fieldInfo)) {
    905. 

  905. 					$field = WebserviceField::fromArray($adb, $fieldInfo);
    906. 

  906. 					$fieldType = $field->getFieldDataType();
    907. 

  907. 				}
    908. 

  908. 

  909. 				if($fieldType == 'currency') {
    910. 

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

  911. 					if($field->getUIType() == '72') {
    912. 

  912. 						$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true);
    913. 

  913. 					} else {
    914. 

  914. 						$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value);
    915. 

  915. 					}
    916. 

  916. 				}
    917. 

  917. 

  918.                 $temp_val = explode(",",$adv_filter_value);
    919. 

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

  920.                         || ($column_info[4] == 'DT'))
    921. 

  921.                     && ($column_info[4] != '' && $adv_filter_value != '' )) {
    922. 

  922.                     $val = Array();
    923. 

  923.                     for($x=0;$x<count($temp_val);$x++) {
    924. 

  924.                         if($column_info[4] == 'D') {
    925. 

  925. 							$date = new DateTimeField(trim($temp_val[$x]));
    926. 

  926. 							$val[$x] = $date->getDBInsertDateValue();
    927. 

  927. 						} elseif($column_info[4] == 'DT') {
    928. 

  928. 							$date = new DateTimeField(trim($temp_val[$x]));
    929. 

  929. 							$val[$x] = $date->getDBInsertDateTimeValue();
    930. 

  930. 						} else {
    931. 

  931. 							$date = new DateTimeField(trim($temp_val[$x]));
    932. 

  932. 							$val[$x] = $date->getDBInsertTimeValue();
    933. 

  933. 						}
    934. 

  934.                     }
    935. 

  935.                     $adv_filter_value = implode(",",$val);
    936. 

  936.                 }
    937. 

  937. 				$criteria = array();
    938. 

  938. 				$criteria['columnname'] = $adv_filter_column;
    939. 

  939. 				$criteria['comparator'] = $adv_filter_comparator;
    940. 

  940. 				$criteria['value'] = $adv_filter_value;
    941. 

  941. 				$criteria['column_condition'] = $adv_filter_column_condition;
    942. 

  942. 

  943. 				$advfilterlist[$adv_filter_groupid]['columns'][] = $criteria;
    944. 

  944. 			}
    945. 

  945. 

  946. 			foreach($advft_criteria_groups as $group_index => $group_condition_info) {
    947. 

  947. 				if(empty($group_condition_info)) continue;
    948. 

  948. 				if(empty($advfilterlist[$group_index])) continue;
    949. 

  949. 				$advfilterlist[$group_index]['condition'] = $group_condition_info["groupcondition"];
    950. 

  950. 				$noOfGroupColumns = count($advfilterlist[$group_index]['columns']);
    951. 

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

  952. 					$advfilterlist[$group_index]['columns'][$noOfGroupColumns-1]['column_condition'] = '';
    953. 

  953. 				}
    954. 

  954. 			}
    955. 

  955. 			$noOfGroups = count($advfilterlist);
    956. 

  956. 			if(!empty($advfilterlist[$noOfGroups]['condition'])) {
    957. 

  957. 				$advfilterlist[$noOfGroups]['condition'] = '';
    958. 

  958. 			}
    959. 

  959. 

  960. 			$advfiltersql = $this->generateAdvFilterSql($advfilterlist);
    961. 

  961. 		}
    962. 

  962. 		return $advfiltersql;
    963. 

  963. 

  964. 	}
    965. 

  965. 

  966. 	/** Function to get standardfilter for the given reportid
    967. 

  967. 	 *  @ param $reportid : Type Integer
    968. 

  968. 	 *  returns the query of columnlist for the selected columns
    969. 

  969. 	 */
    970. 

  970. 

  971. 	function getStandardCriterialSql($reportid)
    972. 

  972. 	{
    973. 

  973. 		global $adb;
    974. 

  974. 		global $modules;
    975. 

  975. 		global $log;
    976. 

  976. 

  977. 		$sreportstdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report";
    978. 

  978. 		$sreportstdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid";
    979. 

  979. 		$sreportstdfiltersql .= " where vtiger_report.reportid = ?";
    980. 

  980. 

  981. 		$result = $adb->pquery($sreportstdfiltersql, array($reportid));
    982. 

  982. 		$noofrows = $adb->num_rows($result);
    983. 

  983. 

  984. 		for($i=0; $i<$noofrows; $i++) {
    985. 

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

  986. 			$datefilter = $adb->query_result($result,$i,"datefilter");
    987. 

  987. 			$startdate = $adb->query_result($result,$i,"startdate");
    988. 

  988. 			$enddate = $adb->query_result($result,$i,"enddate");
    989. 

  989. 

  990. 			if($fieldcolname != "none") {
    991. 

  991. 				$selectedfields = explode(":",$fieldcolname);
    992. 

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

  993. 					$selectedfields[0] = "vtiger_crmentity";
    994. 

  994. 				if($datefilter == "custom") {
    995. 

  995. 

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

  997. 							&& $startdate != '' && $enddate != '') {
    998. 

  998. 

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

  1000. 						$startdate = $startDateTime->getDisplayDate();
    1001. 

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

  1002. 						$enddate = $endDateTime->getDisplayDate();
    1003. 

  1003. 

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

  1005. 					}
    1006. 

  1006. 				} else {
    1007. 

  1007. 

  1008. 					$startenddate = $this->getStandarFiltersStartAndEndDate($datefilter);
    1009. 

  1009. 

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

  1011. 					$startdate = $startDateTime->getDisplayDate();
    1012. 

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

  1013. 					$enddate = $endDateTime->getDisplayDate();
    1014. 

  1014. 

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

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

  1017. 					}
    1018. 

  1018. 				}
    1019. 

  1019. 			}
    1020. 

  1020. 		}
    1021. 

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

  1022. 		return $sSQL;
    1023. 

  1023. 	}
    1024. 

  1024. 

  1025. 	/** Function to get standardfilter startdate and enddate for the given type
    1026. 

  1026. 	 *  @ param $type : Type String
    1027. 

  1027. 	 *  returns the $datevalue Array in the given format
    1028. 

  1028. 	 * 		$datevalue = Array(0=>$startdate,1=>$enddate)
    1029. 

  1029. 	 */
    1030. 

  1030. 

  1031. 

  1032. 	function getStandarFiltersStartAndEndDate($type)
    1033. 

  1033. 	{
    1034. 

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

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

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

  1037. 

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

  1039. 		$currentmonth1 = date("Y-m-t");
    1040. 

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

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

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

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

  1044. 

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

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

  1047. 

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

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

  1050. 

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

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

  1053. 

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

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

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

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

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

  1059. 

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

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

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

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

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

  1065. 

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

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

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

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

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

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

  1072. 

  1073. 		if(date("m") <= 3)
    1074. 

  1074. 		{
    1075. 

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

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

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

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

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

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

  1081. 		}else if(date("m") > 3 and date("m") <= 6)
    1082. 

  1082. 		{
    1083. 

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

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

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

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

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

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

  1089. 

  1090. 		}else if(date("m") > 6 and date("m") <= 9)
    1091. 

  1091. 		{
    1092. 

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

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

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

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

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

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

  1098. 		}
    1099. 

  1099. 		else if(date("m") > 9 and date("m") <= 12)
    1100. 

  1100. 		{
    1101. 

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

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

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

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

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

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

  1107. 

  1108. 		}
    1109. 

  1109. 

  1110. 		if($type == "today" )
    1111. 

  1111. 		{
    1112. 

  1112. 

  1113. 			$datevalue[0] = $today;
    1114. 

  1114. 			$datevalue[1] = $today;
    1115. 

  1115. 		}
    1116. 

  1116. 		elseif($type == "yesterday" )
    1117. 

  1117. 		{
    1118. 

  1118. 

  1119. 			$datevalue[0] = $yesterday;
    1120. 

  1120. 			$datevalue[1] = $yesterday;
    1121. 

  1121. 		}
    1122. 

  1122. 		elseif($type == "tomorrow" )
    1123. 

  1123. 		{
    1124. 

  1124. 

  1125. 			$datevalue[0] = $tomorrow;
    1126. 

  1126. 			$datevalue[1] = $tomorrow;
    1127. 

  1127. 		}
    1128. 

  1128. 		elseif($type == "thisweek" )
    1129. 

  1129. 		{
    1130. 

  1130. 

  1131. 			$datevalue[0] = $thisweek0;
    1132. 

  1132. 			$datevalue[1] = $thisweek1;
    1133. 

  1133. 		}
    1134. 

  1134. 		elseif($type == "lastweek" )
    1135. 

  1135. 		{
    1136. 

  1136. 

  1137. 			$datevalue[0] = $lastweek0;
    1138. 

  1138. 			$datevalue[1] = $lastweek1;
    1139. 

  1139. 		}
    1140. 

  1140. 		elseif($type == "nextweek" )
    1141. 

  1141. 		{
    1142. 

  1142. 

  1143. 			$datevalue[0] = $nextweek0;
    1144. 

  1144. 			$datevalue[1] = $nextweek1;
    1145. 

  1145. 		}
    1146. 

  1146. 		elseif($type == "thismonth…
    1147.

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