PageRenderTime 53ms CodeModel.GetById 16ms RepoModel.GetById 1ms app.codeStats 1ms

/trunk/rucrm/modules/Reports/ReportRun.php

https://code.google.com/p/vtiger-ru-fork/
PHP | 2982 lines | 2517 code | 261 blank | 204 comment | 924 complexity | a5bd9e1add0aaf621ac1d8d072e6e847 MD5 | raw file
Possible License(s): LGPL-2.1, MPL-2.0-no-copyleft-exception, GPL-2.0, LGPL-3.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. 

  21. class ReportRun extends CRMEntity
    22. 

  22. {
    23. 

  23. 

  24. 	var $primarymodule;
    25. 

  25. 	var $secondarymodule;
    26. 

  26. 	var $orderbylistsql;
    27. 

  27. 	var $orderbylistcolumns;
    28. 

  28. 

  29. 	var $selectcolumns;
    30. 

  30. 	var $groupbylist;
    31. 

  31. 	var $reporttype;
    32. 

  32. 	var $reportname;
    33. 

  33. 	var $totallist;
    34. 

  34. 	
    35. 

  35. 	var $_groupinglist  = false;
    36. 

  36. 	var $_columnslist    = false;
    37. 

  37. 	var	$_stdfilterlist = false;
    38. 

  38. 	var	$_columnstotallist = false;
    39. 

  39. 	var	$_advfiltersql = false;
    40. 

  40. 		
    41. 

  41. 	var $convert_currency = array('Potentials_Amount', 'Accounts_Annual_Revenue', 'Leads_Annual_Revenue', 'Campaigns_Budget_Cost', 
    42. 

  42. 									'Campaigns_Actual_Cost', 'Campaigns_Expected_Revenue', 'Campaigns_Actual_ROI', 'Campaigns_Expected_ROI');
    43. 

  43. 	//var $add_currency_sym_in_headers = array('Amount', 'Unit_Price', 'Total', 'Sub_Total', 'S&H_Amount', 'Discount_Amount', 'Adjustment');
    44. 

  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. 	
    52. 

  52. 	/** Function to set reportid,primarymodule,secondarymodule,reporttype,reportname, for given reportid
    53. 

  53. 	 *  This function accepts the $reportid as argument
    54. 

  54. 	 *  It sets reportid,primarymodule,secondarymodule,reporttype,reportname for the given reportid
    55. 

  55. 	 */
    56. 

  56. 	function ReportRun($reportid)
    57. 

  57. 	{
    58. 

  58. 		$oReport = new Reports($reportid);
    59. 

  59. 		$this->reportid = $reportid;
    60. 

  60. 		$this->primarymodule = $oReport->primodule;
    61. 

  61. 		$this->secondarymodule = $oReport->secmodule; 
    62. 

  62. 		$this->reporttype = $oReport->reporttype;
    63. 

  63. 		$this->reportname = $oReport->reportname;
    64. 

  64. 	}
    65. 

  65. 

  66. 	/** Function to get the columns for the reportid
    67. 

  67. 	 *  This function accepts the $reportid and $outputformat (optional)
    68. 

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

  69. 	 *					      $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 As Header value,
    70. 

  70. 	 *					      					|
    71. 

  71.  	 *					      $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen As Header value
    72. 

  72. 	 *				      	     )
    73. 

  73. 	 *
    74. 

  74. 	 */
    75. 

  75. 	function getQueryColumnsList($reportid,$outputformat='')
    76. 

  76. 	{
    77. 

  77. 		// Have we initialized information already?
    78. 

  78. 		if($this->_columnslist !== false) {
    79. 

  79. 			return $this->_columnslist;
    80. 

  80. 		}
    81. 

  81. 		
    82. 

  82. 		global $adb;
    83. 

  83. 		global $modules;
    84. 

  84. 		global $log,$current_user,$current_language;
    85. 

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

  86. 		$ssql .= " left join vtiger_selectcolumn on vtiger_selectcolumn.queryid = vtiger_selectquery.queryid";
    87. 

  87. 		$ssql .= " where vtiger_report.reportid = ?";
    88. 

  88. 		$ssql .= " order by vtiger_selectcolumn.columnindex";
    89. 

  89. 		$result = $adb->pquery($ssql, array($reportid));
    90. 

  90. 		$permitted_fields = Array();
    91. 

  91. 

  92. 		while($columnslistrow = $adb->fetch_array($result))
    93. 

  93. 		{
    94. 

  94. 			$fieldname ="";
    95. 

  95. 			$fieldcolname = $columnslistrow["columnname"];
    96. 

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

  97. 			list($module,$field) = split("_",$module_field);
    98. 

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

  99. 			$inventory_modules = array('SalesOrder','Quotes','PurchaseOrder','Invoice');
    100. 

  100. 			require('user_privileges/user_privileges_'.$current_user->id.'.php');
    101. 

  101. 			if(sizeof($permitted_fields) == 0 && $is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1)
    102. 

  102. 			{
    103. 

  103. 				list($module,$field) = split("_",$module_field);
    104. 

  104. 				$permitted_fields = $this->getaccesfield($module);
    105. 

  105. 			}
    106. 

  106. 			if(in_array($module,$inventory_modules)){
    107. 

  107. 				$permitted_fields = array_merge($permitted_fields,$inventory_fields);
    108. 

  108. 			}
    109. 

  109. 			$selectedfields = explode(":",$fieldcolname);
    110. 

  110. 			$querycolumns = $this->getEscapedColumns($selectedfields);
    111. 

  111. 			
    112. 

  112. 			$mod_strings = return_module_language($current_language,$module);
    113. 

  113. 			$fieldlabel = trim(str_replace($module," ",$selectedfields[2]));
    114. 

  114. 			$mod_arr=explode('_',$fieldlabel);
    115. 

  115. 			$mod = ($mod_arr[0] == '')?$module:$mod_arr[0];
    116. 

  116. 			$fieldlabel = trim(str_replace("_"," ",$fieldlabel));
    117. 

  117. 			//modified code to support i18n issue
    118. 

  118. 			$fld_arr = explode(" ",$fieldlabel);
    119. 

  119. 			$mod_lbl = getTranslatedString($fld_arr[0],$module); //module
    120. 

  120. 			array_shift($fld_arr);
    121. 

  121. 			$fld_lbl_str = implode(" ",$fld_arr);
    122. 

  122. 			$fld_lbl = getTranslatedString($fld_lbl_str,$module); //fieldlabel
    123. 

  123. 			$fieldlabel = $mod_lbl." ".$fld_lbl;
    124. 

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

  125. 			{
    126. 

  126. 				continue;
    127. 

  127. 			}
    128. 

  128. 			else
    129. 

  129. 			{
    130. 

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

  131. 				// To check if the field in the report is a custom field
    132. 

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

  133. 				// Asha - Reference ticket : #4906
    134. 

  134. 				
    135. 

  135. 				if($querycolumns == "")
    136. 

  136. 				{
    137. 

  137. 					if($selectedfields[4] == 'C')
    138. 

  138. 					{
    139. 

  139. 						$field_label_data = split("_",$selectedfields[2]);
    140. 

  140. 						$module= $field_label_data[0];
    141. 

  141. 						if($module!=$this->primarymodule)
    142. 

  142. 							$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]'";
    143. 

  143. 						else
    144. 

  144. 							$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]'";
    145. 

  145. 					}
    146. 

  146. 					elseif($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'status')
    147. 

  147. 					{
    148. 

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

  149. 					}
    150. 

  150. 					elseif($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'date_start')
    151. 

  151. 					{
    152. 

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

  153. 					}
    154. 

  154. 					elseif(stristr($selectedfields[0],"vtiger_users") && ($selectedfields[1] == 'user_name') && $module_field != 'Products_Handler' && $module_field!='Services_Owner')
    155. 

  155. 					{
    156. 

  156. 						$temp_module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]);
    157. 

  157. 						if($module!=$this->primarymodule){
    158. 

  158. 							$condition = "and vtiger_crmentity".$module.".crmid!=''";
    159. 

  159. 						} else {
    160. 

  160. 							$condition = "and vtiger_crmentity.crmid!=''";
    161. 

  161. 						}
    162. 

  162. 						if($temp_module_from_tablename == $module)
    163. 

  163. 							$columnslist[$fieldcolname] = " case when (".$selectedfields[0].".user_name not like '' $condition) then ".$selectedfields[0].".user_name else vtiger_groups".$module.".groupname end as '".$module."_Assigned_To'";
    164. 

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

  165. 							$columnslist[$fieldcolname] = $selectedfields[0].".user_name as '".$header_label."'";
    166. 

  166. 							
    167. 

  167. 					}
    168. 

  168. 					elseif(stristr($selectedfields[0],"vtiger_users") && ($selectedfields[1] == 'user_name') && $module_field == 'Products_Handler')//Products cannot be assiged to group only to handler so group is not included
    169. 

  169. 					{
    170. 

  170. 						$columnslist[$fieldcolname] = $selectedfields[0].".user_name as ".$module."_Handler";
    171. 

  171. 					}
    172. 

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

  173. 					{
    174. 

  174. 						$columnslist[$fieldcolname] = "vtiger_crmentity.".$selectedfields[1]." AS '".$header_label."'";
    175. 

  175. 					}
    176. 

  176. 				    elseif($selectedfields[0] == 'vtiger_invoice' && $selectedfields[1] == 'salesorderid')//handled for salesorder fields in Invoice Module Reports
    177. 

  177. 					{
    178. 

  178. 						$columnslist[$fieldcolname] = "vtiger_salesorderInvoice.subject	AS '".$selectedfields[2]."'";
    179. 

  179. 					}
    180. 

  180. 					elseif($selectedfields[0] == 'vtiger_campaign' && $selectedfields[1] == 'product_id')//handled for product fields in Campaigns Module Reports
    181. 

  181. 					{
    182. 

  182. 						$columnslist[$fieldcolname] = "vtiger_productsCampaigns.productname AS '".$header_label."'";
    183. 

  183. 					}
    184. 

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

  185. 					{
    186. 

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

  187. 					}
    188. 

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

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

  190. 					}
    191. 

  191. 					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
    192. 

  192. 					{
    193. 

  193. 						if($selectedfields[1] == 'filelocationtype'){
    194. 

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

  195. 						} else if($selectedfields[1] == 'folderid'){
    196. 

  196. 							$columnslist[$fieldcolname] = "vtiger_attachmentsfolder.foldername as '$selectedfields[2]'";
    197. 

  197. 						} elseif($selectedfields[1] == 'filestatus'){
    198. 

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

  199. 						} elseif($selectedfields[1] == 'filesize'){
    200. 

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

  201. 						}
    202. 

  202. 					}
    203. 

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

  204. 					{
    205. 

  205. 						if($selectedfields[1] == 'discount'){
    206. 

  206. 							$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 ."'";
    207. 

  207. 						} else if($selectedfields[1] == 'productid'){
    208. 

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

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

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

  211. 						} else {
    212. 

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

  213. 						}
    214. 

  214. 					}
    215. 

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

  216. 					{
    217. 

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

  218. 					}
    219. 

  219. 					else
    220. 

  220. 					{
    221. 

  221. 						$columnslist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]." AS '".$header_label."'";
    222. 

  222. 					}
    223. 

  223. 				}
    224. 

  224. 				else
    225. 

  225. 				{
    226. 

  226. 					$columnslist[$fieldcolname] = $querycolumns;
    227. 

  227. 				}
    228. 

  228. 			}
    229. 

  229. 		}
    230. 

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

  231. 		// Save the information
    232. 

  232. 		$this->_columnslist = $columnslist;
    233. 

  233. 		
    234. 

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

  235. 		return $columnslist;		
    236. 

  236. 	}
    237. 

  237. 	
    238. 

  238. 	/** Function to get field columns based on profile  
    239. 

  239. 	 *  @ param $module : Type string 
    240. 

  240. 	 *  returns permitted fields in array format	
    241. 

  241. 	 */
    242. 

  242. 	function getaccesfield($module)
    243. 

  243. 	{
    244. 

  244. 		global $current_user;
    245. 

  245. 		global $adb;
    246. 

  246. 		$access_fields = Array();
    247. 

  247. 		
    248. 

  248. 		$profileList = getCurrentUserProfileList();
    249. 

  249. 		$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";
    250. 

  250. 		$params = array();
    251. 

  251. 		if($module == "Calendar")
    252. 

  252. 		{
    253. 

  253. 			if (count($profileList) > 0) {
    254. 

  254. 				$query .= " vtiger_field.tabid in (9,16) and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .") group by vtiger_field.fieldid order by block,sequence";
    255. 

  255. 				array_push($params, $profileList);
    256. 

  256. 			} else {
    257. 

  257. 				$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 group by vtiger_field.fieldid order by block,sequence";
    258. 

  258. 			}
    259. 

  259. 		}
    260. 

  260. 		else
    261. 

  261. 		{
    262. 

  262. 			array_push($params, $this->primarymodule, $this->secondarymodule);
    263. 

  263. 			if (count($profileList) > 0) {
    264. 

  264. 				$query .= " vtiger_field.tabid in (select tabid from vtiger_tab where vtiger_tab.name in (?,?)) and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .") group by vtiger_field.fieldid order by block,sequence";
    265. 

  265. 				array_push($params, $profileList);
    266. 

  266. 			} else {
    267. 

  267. 				$query .= " vtiger_field.tabid in (select tabid from vtiger_tab where vtiger_tab.name in (?,?)) and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 group by vtiger_field.fieldid order by block,sequence";
    268. 

  268. 			}
    269. 

  269. 		}
    270. 

  270. 		$result = $adb->pquery($query, $params);
    271. 

  271. 		
    272. 

  272. 		while($collistrow = $adb->fetch_array($result))
    273. 

  273. 		{
    274. 

  274. 			$access_fields[] = $collistrow["fieldname"];
    275. 

  275. 		}
    276. 

  276. 		//added to include ticketid for Reports module in select columnlist for all users
    277. 

  277. 		if($module == "HelpDesk")
    278. 

  278. 			$access_fields[] = "ticketid";
    279. 

  279. 		return $access_fields;
    280. 

  280. 	}
    281. 

  281. 	
    282. 

  282. 	/** Function to get Escapedcolumns for the field in case of multiple parents 
    283. 

  283. 	 *  @ param $selectedfields : Type Array 
    284. 

  284. 	 *  returns the case query for the escaped columns	
    285. 

  285. 	 */
    286. 

  286. 	function getEscapedColumns($selectedfields)
    287. 

  287. 	{
    288. 

  288. 		global $current_user,$adb;
    289. 

  289. 		$fieldname = $selectedfields[3];
    290. 

  290. 		$tmp = split("_",$selectedfields[2]);
    291. 

  291. 		$module = $tmp[0];
    292. 

  292. 		
    293. 

  293. 		if($fieldname == "parent_id" && ($module == "HelpDesk" || $module == "Calendar"))
    294. 

  294. 		{
    295. 

  295. 			if($module == "HelpDesk" && $selectedfields[0] == "vtiger_crmentityRelHelpDesk")
    296. 

  296. 			{
    297. 

  297. 				$querycolumn = "case vtiger_crmentityRelHelpDesk.setype when 'Accounts' then vtiger_accountRelHelpDesk.accountname when 'Contacts' then concat(vtiger_contactdetailsRelHelpDesk.lastname,' ',vtiger_contactdetailsRelHelpDesk.firstname) End"." '".$selectedfields[2]."', vtiger_crmentityRelHelpDesk.setype 'Entity_type'";
    298. 

  298. 				return $querycolumn;
    299. 

  299. 			}
    300. 

  300. 			if($module == "Calendar") {
    301. 

  301. 				$querycolumn = "case vtiger_crmentityRelCalendar.setype when 'Accounts' then vtiger_accountRelCalendar.accountname when 'Leads' then concat(vtiger_leaddetailsRelCalendar.lastname,' ',vtiger_leaddetailsRelCalendar.firstname) when 'Potentials' then vtiger_potentialRelCalendar.potentialname when 'Quotes' then vtiger_quotesRelCalendar.subject when 'PurchaseOrder' then vtiger_purchaseorderRelCalendar.subject when 'Invoice' then vtiger_invoiceRelCalendar.subject when 'SalesOrder' then vtiger_salesorderRelCalendar.subject when 'HelpDesk' then vtiger_troubleticketsRelCalendar.title when 'Campaigns' then vtiger_campaignRelCalendar.campaignname End"." '".$selectedfields[2]."', vtiger_crmentityRelCalendar.setype 'Entity_type'";
    302. 

  302. 			}
    303. 

  303. 		} elseif($fieldname == "contact_id" && strpos($selectedfields[2],"Contact_Name")) {
    304. 

  304. 			if(($this->primarymodule == 'PurchaseOrder' || $this->primarymodule == 'SalesOrder' || $this->primarymodule == 'Quotes' || $this->primarymodule == 'Invoice' || $this->primarymodule == 'Calendar') && $module==$this->primarymodule) {
    305. 

  305. 				if (getFieldVisibilityPermission("Contacts", $current_user->id, "firstname") == '0')
    306. 

  306. 					$querycolumn = " case when vtiger_crmentity.crmid!='' then concat(vtiger_contactdetails".$this->primarymodule.".lastname,' ',vtiger_contactdetails".$this->primarymodule.".firstname) else '-' end as ".$selectedfields[2];
    307. 

  307. 				else
    308. 

  308. 					$querycolumn = " case when vtiger_crmentity.crmid!='' then vtiger_contactdetails".$this->primarymodule.".lastname else '-' end as ".$selectedfields[2];
    309. 

  309. 			}	
    310. 

  310. 			if(stristr($this->secondarymodule,$module) && ($module== 'Quotes' || $module== 'SalesOrder' || $module== 'PurchaseOrder' ||$module== 'Calendar' || $module == 'Invoice')) {
    311. 

  311. 				if (getFieldVisibilityPermission("Contacts", $current_user->id, "firstname") == '0')
    312. 

  312. 					$querycolumn = " case when vtiger_crmentity".$module.".crmid!='' then concat(vtiger_contactdetails".$module.".lastname,' ',vtiger_contactdetails".$module.".firstname) else '-' end as ".$selectedfields[2];
    313. 

  313. 				else 
    314. 

  314. 					$querycolumn = " case when vtiger_crmentity".$module.".crmid!='' then vtiger_contactdetails".$module.".lastname else '-' end as ".$selectedfields[2];
    315. 

  315. 			}
    316. 

  316. 		}
    317. 

  317. 		else{
    318. 

  318.  			if(stristr($selectedfields[0],"vtiger_crmentityRel")){
    319. 

  319.  				$module = str_replace("vtiger_crmentityRel","",$selectedfields[0]);
    320. 

  320. 				$fields_query = $adb->pquery("SELECT vtiger_field.fieldname,vtiger_field.tablename,vtiger_field.fieldid from vtiger_field INNER JOIN vtiger_tab on vtiger_tab.name = ? WHERE vtiger_tab.tabid=vtiger_field.tabid and vtiger_field.fieldname=?",array($module,$selectedfields[3]));
    321. 

  321.    			
    322. 

  322. 		        if($adb->num_rows($fields_query)>0){
    323. 

  323. 			        for($i=0;$i<$adb->num_rows($fields_query);$i++){
    324. 

  324. 			        	$field_name = $selectedfields[3];
    325. 

  325. 			        	$field_id = $adb->query_result($fields_query,$i,'fieldid');
    326. 

  326. 				        $tab_name = $selectedfields[1];
    327. 

  327. 				        $ui10_modules_query = $adb->pquery("SELECT relmodule FROM vtiger_fieldmodulerel WHERE fieldid=?",array($field_id));
    328. 

  328.   				        
    329. 

  329. 				       if($adb->num_rows($ui10_modules_query)>0){
    330. 

  330. 					        $querycolumn = " case vtiger_crmentityRel$module$field_id.setype";
    331. 

  331. 					        for($j=0;$j<$adb->num_rows($ui10_modules_query);$j++){
    332. 

  332. 					        	$rel_mod = $adb->query_result($ui10_modules_query,$j,'relmodule');
    333. 

  333. 					        	$rel_obj = CRMEntity::getInstance($rel_mod);
    334. 

  334. 					        	vtlib_setup_modulevars($rel_mod, $rel_obj);
    335. 

  335. 								
    336. 

  336. 								$rel_tab_name = $rel_obj->table_name;
    337. 

  337. 								$link_field = $rel_tab_name."Rel".$module.".".$rel_obj->list_link_field;
    338. 

  338. 								
    339. 

  339. 								if($rel_mod=="Contacts" || $rel_mod=="Leads"){
    340. 

  340. 									if(getFieldVisibilityPermission($rel_mod,$current_user->id,'firstname')==0){
    341. 

  341. 										$link_field = "concat($link_field,' ',".$rel_tab_name."Rel$module"."_via_field".$field_id.".firstname)";
    342. 

  342. 									}
    343. 

  343. 								}
    344. 

  344. 								$querycolumn.= " when '$rel_mod' then $link_field ";
    345. 

  345. 					        }
    346. 

  346. 					        $querycolumn .= "end as '".$selectedfields[2]."', vtiger_crmentityRel$module$field_id.setype as 'Entity_type'" ;
    347. 

  347. 				       }
    348. 

  348. 			        }
    349. 

  349. 		        }
    350. 

  350. 				
    351. 

  351. 			}
    352. 

  352. 			if($fieldname == 'creator'){
    353. 

  353. 				$querycolumn .= "case when (vtiger_usersModComments.user_name not like '' and vtiger_crmentity.crmid!='') then vtiger_usersModComments.user_name end as 'ModComments_Creator'";
    354. 

  354. 			}
    355. 

  355. 		}
    356. 

  356. 		return $querycolumn;
    357. 

  357. 	}
    358. 

  358. 

  359. 	/** Function to get selectedcolumns for the given reportid  
    360. 

  360. 	 *  @ param $reportid : Type Integer 
    361. 

  361. 	 *  returns the query of columnlist for the selected columns	
    362. 

  362. 	 */
    363. 

  363. 	function getSelectedColumnsList($reportid)
    364. 

  364. 	{
    365. 

  365. 

  366. 		global $adb;
    367. 

  367. 		global $modules;
    368. 

  368. 		global $log;
    369. 

  369. 

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

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

  372. 		$ssql .= " order by vtiger_selectcolumn.columnindex";
    373. 

  373. 

  374. 		$result = $adb->pquery($ssql, array($reportid));
    375. 

  375. 		$noofrows = $adb->num_rows($result);
    376. 

  376. 

  377. 		if ($this->orderbylistsql != "")
    378. 

  378. 		{
    379. 

  379. 			$sSQL .= $this->orderbylistsql.", ";	
    380. 

  380. 		}
    381. 

  381. 

  382. 		for($i=0; $i<$noofrows; $i++)
    383. 

  383. 		{
    384. 

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

  385. 			$ordercolumnsequal = true;
    386. 

  386. 			if($fieldcolname != "")
    387. 

  387. 			{
    388. 

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

  389. 				{
    390. 

  390. 					if($this->orderbylistcolumns[$j] == $fieldcolname)
    391. 

  391. 					{
    392. 

  392. 						$ordercolumnsequal = false;
    393. 

  393. 						break;
    394. 

  394. 					}else
    395. 

  395. 					{
    396. 

  396. 						$ordercolumnsequal = true;
    397. 

  397. 					}
    398. 

  398. 				}
    399. 

  399. 				if($ordercolumnsequal)
    400. 

  400. 				{
    401. 

  401. 					$selectedfields = explode(":",$fieldcolname);
    402. 

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

  403. 						$selectedfields[0] = "vtiger_crmentity";
    404. 

  404. 					$sSQLList[] = $selectedfields[0].".".$selectedfields[1]." '".$selectedfields[2]."'";
    405. 

  405. 				}
    406. 

  406. 			}
    407. 

  407. 		}
    408. 

  408. 		$sSQL .= implode(",",$sSQLList);
    409. 

  409. 

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

  411. 		return $sSQL;
    412. 

  412. 	}
    413. 

  413. 

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

  415. 	 *  @ param $comparator : Type String  
    416. 

  416. 	 *  @ param $value : Type String  
    417. 

  417. 	 *  returns the check query for the comparator 	
    418. 

  418. 	 */
    419. 

  419. 	function getAdvComparator($comparator,$value,$datatype="")
    420. 

  420. 	{
    421. 

  421. 

  422. 		global $log,$adb,$default_charset,$ogReport;
    423. 

  423. 		$value=html_entity_decode(trim($value),ENT_QUOTES,$default_charset);
    424. 

  424. 		$value_len = strlen($value);
    425. 

  425. 		$is_field = false;
    426. 

  426. 		if($value[0]=='$' && $value[$value_len-1]=='$'){
    427. 

  427. 			$temp = str_replace('$','',$value);
    428. 

  428. 			$is_field = true;
    429. 

  429. 		}
    430. 

  430. 		if($datatype=='C'){
    431. 

  431. 			$value = str_replace("yes","1",str_replace("no","0",$value));
    432. 

  432. 		}
    433. 

  433. 

  434. 		if($is_field==true){
    435. 

  435. 			$value = $this->getFilterComparedField($temp);
    436. 

  436. 		}
    437. 

  437. 		if($comparator == "e")
    438. 

  438. 		{
    439. 

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

  440. 			{
    441. 

  441. 				$rtvalue = " is NULL";
    442. 

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

  443. 			{
    444. 

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

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

  446. 			{
    447. 

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

  448. 			}else
    449. 

  449. 			{
    450. 

  450. 				$rtvalue = " is NULL";
    451. 

  451. 			}
    452. 

  452. 		}
    453. 

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

  454. 		{
    455. 

  455. 			if(trim($value) == "NULL")
    456. 

  456. 			{
    457. 

  457. 				$rtvalue = " is NOT NULL";
    458. 

  458. 			}elseif(trim($value) != "")
    459. 

  459. 			{
    460. 

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

  461. 			}elseif(trim($value) == "" && $datatype == "V")
    462. 

  462. 			{
    463. 

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

  464. 			}else
    465. 

  465. 			{
    466. 

  466. 				$rtvalue = " is NOT NULL";
    467. 

  467. 			}
    468. 

  468. 		}
    469. 

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

  470. 		{
    471. 

  471. 			$rtvalue = " like '". formatForSqlLike($value, 2,$is_field) ."'";
    472. 

  472. 		}
    473. 

  473. 		if($comparator == "ew")
    474. 

  474. 		{
    475. 

  475. 			$rtvalue = " like '". formatForSqlLike($value, 1,$is_field) ."'";
    476. 

  476. 		}			
    477. 

  477. 		if($comparator == "c")
    478. 

  478. 		{
    479. 

  479. 			$rtvalue = " like '". formatForSqlLike($value,0,$is_field) ."'";
    480. 

  480. 		}
    481. 

  481. 		if($comparator == "k")
    482. 

  482. 		{
    483. 

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

  484. 		}
    485. 

  485. 		if($comparator == "l")
    486. 

  486. 		{
    487. 

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

  488. 		}
    489. 

  489. 		if($comparator == "g")
    490. 

  490. 		{
    491. 

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

  492. 		}
    493. 

  493. 		if($comparator == "m")
    494. 

  494. 		{
    495. 

  495. 			$rtvalue = " <= ".$adb->quote($value);
    496. 

  496. 		}
    497. 

  497. 		if($comparator == "h")
    498. 

  498. 		{
    499. 

  499. 			$rtvalue = " >= ".$adb->quote($value);
    500. 

  500. 		}
    501. 

  501. 		if($comparator == "b") {
    502. 

  502. 			$rtvalue = " < ".$adb->quote($value);
    503. 

  503. 		}
    504. 

  504. 		if($comparator == "a") {
    505. 

  505. 			$rtvalue = " > ".$adb->quote($value);
    506. 

  506. 		}
    507. 

  507. 		if($is_field==true){
    508. 

  508. 			$rtvalue = str_replace("'","",$rtvalue);
    509. 

  509. 			$rtvalue = str_replace("\\","",$rtvalue);
    510. 

  510. 		}
    511. 

  511. 		$log->info("ReportRun :: Successfully returned getAdvComparator");
    512. 

  512. 		return $rtvalue;
    513. 

  513. 	}
    514. 

  514. 

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

  516. 	 *  @ param $field : field
    517. 

  517. 	 *  returns the value for the comparator 	
    518. 

  518. 	 */
    519. 

  519. 	function getFilterComparedField($field){
    520. 

  520. 		global $adb,$ogReport;
    521. 

  521. 			$field = split('#',$field);
    522. 

  522. 			$module = $field[0];
    523. 

  523. 			$fieldname = trim($field[1]);
    524. 

  524. 			$tabid = getTabId($module);
    525. 

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

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

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

  528. 			$typeofdata = $adb->query_result($field_query,0,'typeofdata'); 
    529. 

  529. 			$fieldtypeofdata=ChangeTypeOfData_Filter($fieldtablename,$fieldcolname,$typeofdata[0]);
    530. 

  530. 			$uitype = $adb->query_result($field_query,0,'uitype'); 
    531. 

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

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

  533. 			else
    534. 

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

  535. 			*/
    536. 

  536. 			if($uitype == 68 || $uitype == 59)
    537. 

  537. 			{
    538. 

  538. 				$fieldtypeofdata = 'V';
    539. 

  539. 			}
    540. 

  540. 			if($fieldtablename == "vtiger_crmentity")
    541. 

  541. 			{
    542. 

  542. 				$fieldtablename = $fieldtablename.$module;
    543. 

  543. 			}
    544. 

  544. 			if($fieldname == "assigned_user_id")
    545. 

  545. 			{
    546. 

  546. 				$fieldtablename = "vtiger_users".$module;
    547. 

  547. 				$fieldcolname = "user_name";
    548. 

  548. 			}
    549. 

  549. 			if($fieldname == "account_id")
    550. 

  550. 			{
    551. 

  551. 				$fieldtablename = "vtiger_account".$module;
    552. 

  552. 				$fieldcolname = "accountname";
    553. 

  553. 			}
    554. 

  554. 			if($fieldname == "contact_id")
    555. 

  555. 			{
    556. 

  556. 				$fieldtablename = "vtiger_contactdetails".$module;
    557. 

  557. 				$fieldcolname = "lastname";
    558. 

  558. 			}
    559. 

  559. 			if($fieldname == "parent_id")
    560. 

  560. 			{
    561. 

  561. 				$fieldtablename = "vtiger_crmentityRel".$module;
    562. 

  562. 				$fieldcolname = "setype";
    563. 

  563. 			}
    564. 

  564. 			if($fieldname == "vendor_id")
    565. 

  565. 			{
    566. 

  566. 				$fieldtablename = "vtiger_vendorRel".$module;
    567. 

  567. 				$fieldcolname = "vendorname";
    568. 

  568. 			}
    569. 

  569. 			if($fieldname == "potential_id")
    570. 

  570. 			{
    571. 

  571. 				$fieldtablename = "vtiger_potentialRel".$module;
    572. 

  572. 				$fieldcolname = "potentialname";
    573. 

  573. 			}
    574. 

  574. 			if($fieldname == "assigned_user_id1")
    575. 

  575. 			{
    576. 

  576. 				$fieldtablename = "vtiger_usersRel1";
    577. 

  577. 				$fieldcolname = "user_name";
    578. 

  578. 			}
    579. 

  579. 			if($fieldname == 'quote_id')
    580. 

  580. 			{
    581. 

  581. 				$fieldtablename = "vtiger_quotes".$module;
    582. 

  582. 				$fieldcolname = "subject";
    583. 

  583. 			}
    584. 

  584. 			if($fieldname == 'product_id' && $fieldtablename == 'vtiger_troubletickets')
    585. 

  585. 			{
    586. 

  586. 				$fieldtablename = "vtiger_productsRel";
    587. 

  587. 				$fieldcolname = "productname";
    588. 

  588. 			}
    589. 

  589. 			if($fieldname == 'product_id' && $fieldtablename == 'vtiger_campaign') 
    590. 

  590. 			{
    591. 

  591. 				$fieldtablename = "vtiger_productsCampaigns";
    592. 

  592. 				$fieldcolname = "productname";
    593. 

  593. 			}
    594. 

  594. 			if($fieldname == 'product_id' && $fieldtablename == 'vtiger_products') 
    595. 

  595. 			{
    596. 

  596. 				$fieldtablename = "vtiger_productsProducts";
    597. 

  597. 				$fieldcolname = "productname";
    598. 

  598. 			}
    599. 

  599. 			if($fieldname == 'campaignid' && $module=='Potentials')
    600. 

  600. 			{
    601. 

  601. 				$fieldtablename = "vtiger_campaign".$module;
    602. 

  602. 				$fieldcolname = "campaignname";
    603. 

  603. 			}
    604. 

  604. 			$value = $fieldtablename.".".$fieldcolname;
    605. 

  605. 		return $value;
    606. 

  606. 	}
    607. 

  607. 	/** Function to get the advanced filter columns for the reportid
    608. 

  608. 	 *  This function accepts the $reportid
    609. 

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

  610. 	 *					      $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria,
    611. 

  611. 	 *					      					|
    612. 

  612.  	 *					      $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen filtercriteria 
    613. 

  613. 	 *				      	     )
    614. 

  614. 	 *
    615. 

  615. 	 */
    616. 

  616. 

  617. 

  618. 	function getAdvFilterSql($reportid)
    619. 

  619. 	{
    620. 

  620. 		// Have we initialized information already?
    621. 

  621. 		if($this->_advfiltersql !== false) {
    622. 

  622. 			return $this->_advfiltersql;
    623. 

  623. 		}
    624. 

  624. 		
    625. 

  625. 		global $adb;
    626. 

  626. 		global $modules;
    627. 

  627. 		global $log;
    628. 

  628. 

  629. 		$advfiltersql = "";
    630. 

  630. 		
    631. 

  631. 		$advfiltergroupssql = "SELECT * FROM vtiger_relcriteria_grouping WHERE queryid = ? ORDER BY groupid";
    632. 

  632. 		$advfiltergroups = $adb->pquery($advfiltergroupssql, array($reportid));
    633. 

  633. 		$numgrouprows = $adb->num_rows($advfiltergroups);
    634. 

  634. 		$groupctr =0;
    635. 

  635. 		while($advfiltergroup = $adb->fetch_array($advfiltergroups)) {
    636. 

  636. 			$groupctr++;
    637. 

  637. 			$groupid = $advfiltergroup["groupid"];
    638. 

  638. 			$groupcondition = $advfiltergroup["group_condition"];
    639. 

  639. 			
    640. 

  640. 			$advfiltercolumnssql =  "select vtiger_relcriteria.* from vtiger_report";
    641. 

  641. 			$advfiltercolumnssql .= " inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid";
    642. 

  642. 			$advfiltercolumnssql .= " left join vtiger_relcriteria on vtiger_relcriteria.queryid = vtiger_selectquery.queryid";
    643. 

  643. 			$advfiltercolumnssql .= " where vtiger_report.reportid = ? AND vtiger_relcriteria.groupid = ?";
    644. 

  644. 			$advfiltercolumnssql .= " order by vtiger_relcriteria.columnindex";
    645. 

  645. 	
    646. 

  646. 			$result = $adb->pquery($advfiltercolumnssql, array($reportid, $groupid));
    647. 

  647. 			$noofrows = $adb->num_rows($result);
    648. 

  648. 			
    649. 

  649. 			if($noofrows > 0) {
    650. 

  650. 				
    651. 

  651. 				$advfiltergroupsql = "";
    652. 

  652. 				$columnctr = 0;
    653. 

  653. 				while($advfilterrow = $adb->fetch_array($result)) {
    654. 

  654. 					$columnctr++;
    655. 

  655. 					$fieldcolname = $advfilterrow["columnname"];
    656. 

  656. 					$comparator = $advfilterrow["comparator"];
    657. 

  657. 					$value = $advfilterrow["value"];
    658. 

  658. 					$columncondition = $advfilterrow["column_condition"];
    659. 

  659. 					
    660. 

  660. 					if($fieldcolname != "" && $comparator != "") {
    661. 

  661. 						$selectedfields = explode(":",$fieldcolname);
    662. 

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

  663. 						if($selectedfields[4] == 'C') {
    664. 

  664. 							if(strcasecmp(trim($value),"yes")==0)
    665. 

  665. 								$value="1";
    666. 

  666. 							if(strcasecmp(trim($value),"no")==0)
    667. 

  667. 								$value="0";
    668. 

  668. 						}
    669. 

  669. 						$valuearray = explode(",",trim($value));
    670. 

  670. 						$datatype = (isset($selectedfields[4])) ? $selectedfields[4] : "";
    671. 

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

  672. 							
    673. 

  673. 							$advcolumnsql = "";
    674. 

  674. 							for($n=0;$n<count($valuearray);$n++) {
    675. 

  675. 		                		
    676. 

  676. 		                		if($selectedfields[0] == 'vtiger_crmentityRelHelpDesk' && $selectedfields[1]=='setype') {
    677. 

  677. 									$advcolsql[] = "(case vtiger_crmentityRelHelpDesk.setype when 'Accounts' then vtiger_accountRelHelpDesk.accountname else concat(vtiger_contactdetailsRelHelpDesk.lastname,' ',vtiger_contactdetailsRelHelpDesk.firstname) end) ". $this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    678. 

  678. 		                        } elseif($selectedfields[0] == 'vtiger_crmentityRelCalendar' && $selectedfields[1]=='setype') {
    679. 

  679. 									$advcolsql[] = "(case vtiger_crmentityRelHelpDesk.setype when 'Accounts' then vtiger_accountRelHelpDesk.accountname else concat(vtiger_contactdetailsRelHelpDesk.lastname,' ',vtiger_contactdetailsRelHelpDesk.firstname) end) ". $this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    680. 

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

  681. 									$module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]);
    682. 

  682. 									if($this->primarymodule == 'Products') {
    683. 

  683. 										$advcolsql[] = ($selectedfields[0].".user_name ".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype));
    684. 

  684. 									} else {
    685. 

  685. 										$advcolsql[] = " ".$selectedfields[0].".user_name".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype)." or vtiger_groups".$module_from_tablename.".groupname ".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype);
    686. 

  686. 									}
    687. 

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

  688. 									if($selectedfields[2] == 'Calendar_Status')
    689. 

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

  690. 									elseif($selectedfields[2] == 'HelpDesk_Status')
    691. 

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

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

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

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

  695. 									else	
    696. 

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

  697. 								} else {
    698. 

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

  699. 								}
    700. 

  700. 							}
    701. 

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

  702. 							if($comparator == 'n' || $comparator == 'k')
    703. 

  703. 								$advcolumnsql = implode(" and ",$advcolsql);
    704. 

  704. 							else
    705. 

  705. 								$advcolumnsql = implode(" or ",$advcolsql);
    706. 

  706. 							$fieldvalue = " (".$advcolumnsql.") ";
    707. 

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

  708. 							$module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]);
    709. 

  709. 							if($this->primarymodule == 'Products') {
    710. 

  710. 								$fieldvalue = ($selectedfields[0].".user_name ".$this->getAdvComparator($comparator,trim($value),$datatype));
    711. 

  711. 							} else {
    712. 

  712. 								$fieldvalue = " case when (".$selectedfields[0].".user_name not like '') then ".$selectedfields[0].".user_name else vtiger_groups".$module_from_tablename.".groupname end ".$this->getAdvComparator($comparator,trim($value),$datatype);
    713. 

  713. 							}
    714. 

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

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

  716. 						} elseif($selectedfields[0] == 'vtiger_crmentityRelHelpDesk' && $selectedfields[1]=='setype') {
    717. 

  717. 							$fieldvalue = "(vtiger_accountRelHelpDesk.accountname ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_contactdetailsRelHelpDesk.lastname ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_contactdetailsRelHelpDesk.firstname ".$this->getAdvComparator($comparator,trim($value),$datatype).")";
    718. 

  718. 						} elseif($selectedfields[0] == 'vtiger_crmentityRelCalendar' && $selectedfields[1]=='setype') {
    719. 

  719. 							$fieldvalue = "(vtiger_accountRelCalendar.accountname ".$this->getAdvComparator($comparator,trim($value),$datatype)." or concat(vtiger_leaddetailsRelCalendar.lastname,' ',vtiger_leaddetailsRelCalendar.firstname) ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_potentialRelCalendar.potentialname ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_invoiceRelCalendar.subject ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_quotesRelCalendar.subject ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_purchaseorderRelCalendar.subject ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_salesorderRelCalendar.subject ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_troubleticketsRelCalendar.title ".$this->getAdvComparator($comparator,trim($value),$datatype)." or vtiger_campaignRelCalendar.campaignname ".$this->getAdvComparator($comparator,trim($value),$datatype).")";
    720. 

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

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

  722. 						} elseif($selectedfields[3] == "contact_id" && strpos($selectedfields[2],"Contact_Name")) {
    723. 

  723. 							if($this->primarymodule == 'PurchaseOrder' || $this->primarymodule == 'SalesOrder' || $this->primarymodule == 'Quotes' || $this->primarymodule == 'Invoice' || $this->primarymodule == 'Calendar')
    724. 

  724. 								$fieldvalue = "concat(vtiger_contactdetails". $this->primarymodule .".lastname,' ',vtiger_contactdetails". $this->primarymodule .".firstname)".$this->getAdvComparator($comparator,trim($value),$datatype);
    725. 

  725. 							if($this->secondarymodule == 'Quotes' || $this->secondarymodule == 'Invoice')
    726. 

  726. 								$fieldvalue = "concat(vtiger_contactdetails". $this->secondarymodule .".lastname,' ',vtiger_contactdetails". $this->secondarymodule .".firstname)".$this->getAdvComparator($comparator,trim($value),$datatype);
    727. 

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

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

  729. 						} elseif($comparator == 'bw' && count($valuearray) == 2) {
    730. 

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

  731. 						} else {
    732. 

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

  733. 						}
    734. 

  734. 						
    735. 

  735. 						$advfiltergroupsql .= $fieldvalue;
    736. 

  736. 						if($columncondition != NULL && $columncondition != '' && $noofrows > $columnctr ) {
    737. 

  737. 							$advfiltergroupsql .= ' '.$columncondition.' ';
    738. 

  738. 						}	
    739. 

  739. 					}
    740. 

  740. 		
    741. 

  741. 				}
    742. 

  742. 				
    743. 

  743. 				if (trim($advfiltergroupsql) != "") {
    744. 

  744. 					$advfiltergroupsql =  "( $advfiltergroupsql ) ";
    745. 

  745. 					if($groupcondition != NULL && $groupcondition != '' && $numgrouprows > $groupctr) {
    746. 

  746. 						$advfiltergroupsql .= ' '. $groupcondition . ' ';
    747. 

  747. 					}
    748. 

  748. 					
    749. 

  749. 					$advfiltersql .= $advfiltergroupsql;
    750. 

  750. 				}
    751. 

  751. 			}
    752. 

  752. 		}
    753. 

  753. 		if (trim($advfiltersql) != "") $advfiltersql = '('.$advfiltersql.')';
    754. 

  754. 		// Save the information
    755. 

  755. 		$this->_advfiltersql = $advfiltersql;
    756. 

  756. 		
    757. 

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

  758. 		return $advfiltersql;
    759. 

  759. 	}	
    760. 

  760. 

  761. 	/** Function to get the Standard filter columns for the reportid
    762. 

  762. 	 *  This function accepts the $reportid datatype Integer
    763. 

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

  764. 	 *					      $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria,
    765. 

  765. 	 *				      	     )
    766. 

  766. 	 *
    767. 

  767. 	 */
    768. 

  768. 	function getStdFilterList($reportid)
    769. 

  769. 	{
    770. 

  770. 		// Have we initialized information already?
    771. 

  771. 		if($this->_stdfilterlist !== false) {
    772. 

  772. 			return $this->_stdfilterlist;
    773. 

  773. 		}
    774. 

  774. 		
    775. 

  775. 		global $adb;
    776. 

  776. 		global $modules;
    777. 

  777. 		global $log;
    778. 

  778. 

  779. 		$stdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report";
    780. 

  780. 		$stdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid";
    781. 

  781. 		$stdfiltersql .= " where vtiger_report.reportid = ?";
    782. 

  782. 

  783. 		$result = $adb->pquery($stdfiltersql, array($reportid));
    784. 

  784. 		$stdfilterrow = $adb->fetch_array($result);
    785. 

  785. 		if(isset($stdfilterrow))
    786. 

  786. 		{
    787. 

  787. 			$fieldcolname = $stdfilterrow["datecolumnname"];
    788. 

  788. 			$datefilter = $stdfilterrow["datefilter"];
    789. 

  789. 			$startdate = $stdfilterrow["startdate"];
    790. 

  790. 			$enddate = $stdfilterrow["enddate"];
    791. 

  791. 

  792. 			if($fieldcolname != "none")
    793. 

  793. 			{
    794. 

  794. 				$selectedfields = explode(":",$fieldcolname);
    795. 

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

  796. 					$selectedfields[0] = "vtiger_crmentity";
    797. 

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

  798. 				{
    799. 

  799. 					if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $selectedfields[0] != "" && $selectedfields[1] != "")
    800. 

  800. 					{
    801. 

  801. 						$stdfilterlist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]." between '".$startdate." 00:00:00' and '".$enddate." 23:59:59'";
    802. 

  802. 					}
    803. 

  803. 				}else
    804. 

  804. 				{
    805. 

  805. 					$startenddate = $this->getStandarFiltersStartAndEndDate($datefilter);
    806. 

  806. 					if($startenddate[0] != "" && $startenddate[1] != "" && $selectedfields[0] != "" && $selectedfields[1] != "")
    807. 

  807. 					{
    808. 

  808. 						$stdfilterlist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]." between '".$startenddate[0]." 00:00:00' and '".$startenddate[1]." 23:59:59'";
    809. 

  809. 					}
    810. 

  810. 				}
    811. 

  811. 

  812. 			}		
    813. 

  813. 		}
    814. 

  814. 		// Save the information
    815. 

  815. 		$this->_stdfilterlist = $stdfilterlist;
    816. 

  816. 		
    817. 

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

  818. 		return $stdfilterlist;
    819. 

  819. 	}
    820. 

  820. 

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

  822. 	 *  @ param $filtercolumn : Type String
    823. 

  823. 	 *  @ param $filter : Type String
    824. 

  824. 	 *  @ param $startdate: Type String
    825. 

  825. 	 *  @ param $enddate : Type String
    826. 

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

  827. 	 *
    828. 

  828. 	 */
    829. 

  829. 	function RunTimeFilter($filtercolumn,$filter,$startdate,$enddate)
    830. 

  830. 	{
    831. 

  831. 		if($filtercolumn != "none")
    832. 

  832. 		{
    833. 

  833. 			$selectedfields = explode(":",$filtercolumn);
    834. 

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

  835. 				$selectedfields[0] = "vtiger_crmentity";
    836. 

  836. 			if($filter == "custom")
    837. 

  837. 			{
    838. 

  838. 				if($startdate != "" && $enddate != "" && $selectedfields[0] != "" && $selectedfields[1] != "")
    839. 

  839. 				{
    840. 

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

  841. 				}
    842. 

  842. 			}else
    843. 

  843. 			{
    844. 

  844. 				if($startdate != "" && $enddate != "")
    845. 

  845. 				{
    846. 

  846. 					$startenddate = $this->getStandarFiltersStartAndEndDate($filter);
    847. 

  847. 					if($startenddate[0] != "" && $startenddate[1] != "" && $selectedfields[0] != "" && $selectedfields[1] != "")
    848. 

  848. 					{
    849. 

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

  850. 					}
    851. 

  851. 				}
    852. 

  852. 			}
    853. 

  853. 

  854. 		}
    855. 

  855. 		return $stdfilterlist;
    856. 

  856. 

  857. 	}
    858. 

  858. 

  859. 	/** Function to get standardfilter for the given reportid  
    860. 

  860. 	 *  @ param $reportid : Type Integer 
    861. 

  861. 	 *  returns the query of columnlist for the selected columns	
    862. 

  862. 	 */
    863. 

  863. 

  864. 	function getStandardCriterialSql($reportid)
    865. 

  865. 	{
    866. 

  866. 		global $adb;
    867. 

  867. 		global $modules;
    868. 

  868. 		global $log;
    869. 

  869. 

  870. 		$sreportstdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report"; 
    871. 

  871. 		$sreportstdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid"; 
    872. 

  872. 		$sreportstdfiltersql .= " where vtiger_report.reportid = ?";
    873. 

  873. 

  874. 		$result = $adb->pquery($sreportstdfiltersql, array($reportid));
    875. 

  875. 		$noofrows = $adb->num_rows($result);
    876. 

  876. 

  877. 		for($i=0; $i<$noofrows; $i++)
    878. 

  878. 		{
    879. 

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

  880. 			$datefilter = $adb->query_result($result,$i,"datefilter");
    881. 

  881. 			$startdate = $adb->query_result($result,$i,"startdate");
    882. 

  882. 			$enddate = $adb->query_result($result,$i,"enddate");
    883. 

  883. 

  884. 			if($fieldcolname != "none")
    885. 

  885. 			{
    886. 

  886. 				$selectedfields = explode(":",$fieldcolname);
    887. 

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

  888. 					$selectedfields[0] = "vtiger_crmentity";
    889. 

  889. 				if($datefilter == "custom")
    890. 

  890. 				{
    891. 

  891. 					if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $selectedfields[0] != "" && $selectedfields[1] != "")
    892. 

  892. 					{
    893. 

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

  894. 					}
    895. 

  895. 				}else
    896. 

  896. 				{
    897. 

  897. 					$startenddate = $this->getStandarFiltersStartAndEndDate($datefilter);
    898. 

  898. 					if($startenddate[0] != "" && $startenddate[1] != "" && $selectedfields[0] != "" && $selectedfields[1] != "")
    899. 

  899. 					{
    900. 

  900. 						$sSQL .= $selectedfields[0].".".$selectedfields[1]." between '".$startenddate[0]."' and '".$startenddate[1]."'";
    901. 

  901. 					}
    902. 

  902. 				}
    903. 

  903. 			}
    904. 

  904. 		}
    905. 

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

  906. 		return $sSQL;
    907. 

  907. 	}
    908. 

  908. 

  909. 	/** Function to get standardfilter startdate and enddate for the given type   
    910. 

  910. 	 *  @ param $type : Type String 
    911. 

  911. 	 *  returns the $datevalue Array in the given format
    912. 

  912. 	 * 		$datevalue = Array(0=>$startdate,1=>$enddate)	 
    913. 

  913. 	 */
    914. 

  914. 

  915. 

  916. 	function getStandarFiltersStartAndEndDate($type)
    917. 

  917. 	{
    918. 

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

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

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

  921. 

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

  923. 		$currentmonth1 = date("Y-m-t");
    924. 

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

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

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

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

  928. 

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

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

  931. 

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

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

  934. 

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

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

  937. 

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

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

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

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

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

  943. 

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

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

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

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

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

  949. 

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

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

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

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

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

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

  956. 

  957. 		if(date("m") <= 3)
    958. 

  958. 		{
    959. 

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

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

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

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

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

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

  965. 		}else if(date("m") > 3 and date("m") <= 6)
    966. 

  966. 		{
    967. 

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

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

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

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

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

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

  973. 

  974. 		}else if(date("m") > 6 and date("m") <= 9)
    975. 

  975. 		{
    976. 

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

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

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

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

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

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

  982. 		}
    983. 

  983. 		else if(date("m") > 9 and date("m") <= 12)
    984. 

  984. 		{
    985. 

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

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

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

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

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

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

  991. 

  992. 		}
    993. 

  993. 

  994. 		if($type == "today" )
    995. 

  995. 		{
    996. 

  996. 

  997. 			$datevalue[0] = $today;
    998. 

  998. 			$datevalue[1] = $today;
    999. 

  999. 		}
    1000. 

  1000. 		elseif($type == "yesterday" )
    1001. 

  1001. 		{
    1002. 

  1002. 

  1003. 			$datevalue[0] = $yesterday;
    1004. 

  1004. 			$datevalue[1] = $yesterday;
    1005. 

  1005. 		}
    1006. 

  1006. 		elseif($type == "tomorrow" )
    1007. 

  1007. 		{
    1008. 

  1008. 

  1009. 			$datevalue[0] = $tomorrow;
    1010. 

  1010. 			$datevalue[1] = $tomorrow;
    1011. 

  1011. 		}        
    1012. 

  1012. 		elseif($type == "thisweek" )
    1013. 

  1013. 		{
    1014. 

  1014. 

  1015. 			$datevalue[0] = $thisweek0;
    1016. 

  1016. 			$datevalue[1] = $thisweek1;
    1017. 

  1017. 		}                
    1018. 

  1018. 		elseif($type == "lastweek" )
    1019. 

  1019. 		{
    1020. 

  1020. 

  1021. 			$datevalue[0] = $lastweek0;
    1022. 

  1022. 			$datevalue[1] = $lastweek1;
    1023. 

  1023. 		}                
    1024. 

  1024. 		elseif($type == "nextweek" )
    1025. 

  1025. 		{
    1026. 

  1026. 

  1027. 			$datevalue[0] = $nextweek0;
    1028. 

  1028. 			$datevalue[1] = $nextweek1;
    1029. 

  1029. 		}                
    1030. 

  1030. 		elseif($type == "thismonth" )
    1031. 

  1031. 		{
    1032. 

  1032. 

  1033. 			$datevalue[0] =$currentmonth0;
    1034. 

  1034. 			$datevalue[1] = $currentmonth1;
    1035. 

  1035. 		}                
    1036. 

  1036. 

  1037. 		elseif($type == "lastmonth" )
    1038. 

  1038. 		{
    1039. 

  1039. 

  1040. 			$datevalue[0] = $lastmonth0;
    1041. 

  1041. 			$datevalue[1] = $lastmonth1;
    1042. 

  1042. 		}             
    1043. 

  1043. 		elseif($type == "nextmonth" )
    1044. 

  1044. 		{
    1045. 

  1045. 

  1046. 			$datevalue[0] = $nextmonth0;
    1047. 

  1047. 			$datevalue[1] = $nextmonth1;
    1048. 

  1048. 		}           
    1049. 

  1049. 		elseif($type == "next7days" )
    1050. 

  1050. 		{
    1051. 

  1051. 

  1052. 			$datevalue[0] = $today;
    1053. 

  1053. 			$datevalue[1] = $next7days;
    1054. 

  1054. 		}                
    1055. 

  1055. 		elseif($type == "next30days" )
    1056. 

  1056. 		{
    1057. 

  1057. 

  1058. 			$datevalue[0] =$today;
    1059. 

  1059. 			$datevalue[1] =$next30days;
    1060. 

  1060. 		}                
    1061. 

  1061. 		elseif($type == "next60days" )
    1062. 

  1062. 		{
    1063. 

  1063. 

  1064. 			$datevalue[0] = $today;
    1065. 

  1065. 			$datevalue[1] = $next60days;
    1066. 

  1066. 		}                
    1067. 

  1067. 		elseif($type == "next90days" )
    1068. 

  1068. 		{
    1069. 

  1069. 

  1070. 			$datevalue[0] = $today;
    1071. 

  1071. 			$datevalue[1] = $next90days;
    1072. 

  1072. 		}        
    1073. 

  1073. 		elseif($type == "next120days" )
    1074. 

  1074. 		{
    1075. 

  1075. 

  1076. 			$datevalue[0] = $today;
    1077. 

  1077. 			$datevalue[1] = $next120days;
    1078. 

  1078. 		}        
    1079. 

  1079. 		elseif($type == "last7days" )
    1080. 

  1080. 		{
    1081. 

  1081. 

  1082. 			$datevalue[0] = $last7days;
    1083. 

  1083. 			$datevalue[1] = $today;
    1084. 

  1084. 		}                        
    1085. 

  1085. 		elseif($…
    1086.

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