| [ Index ] |
PHP Cross Reference of vtigercrm-6.1.0 |
[Summary view] [Print] [Text view]
1 <?php 2 /********************************************************************************* 3 ** The contents of this file are subject to the vtiger CRM Public License Version 1.0 4 * ("License"); You may not use this file except in compliance with the License 5 * The Original Code is: vtiger CRM Open Source 6 * The Initial Developer of the Original Code is vtiger. 7 * Portions created by vtiger are Copyright (C) vtiger. 8 * All Rights Reserved. 9 * 10 ********************************************************************************/ 11 require_once ('include/database/PearDatabase.php'); 12 require_once ('data/CRMEntity.php'); 13 require_once ('include/utils/UserInfoUtil.php'); 14 require_once 'modules/Reports/ReportUtils.php'; 15 global $calpath; 16 global $app_strings,$mod_strings; 17 global $app_list_strings; 18 global $modules; 19 global $blocks; 20 global $adv_filter_options; 21 global $log; 22 23 global $report_modules; 24 global $related_modules; 25 global $old_related_modules; 26 27 $adv_filter_options = array("e"=>"equals", 28 "n"=>"not equal to", 29 "s"=>"starts with", 30 "ew"=>"ends with", 31 "c"=>"contains", 32 "k"=>"does not contain", 33 "l"=>"less than", 34 "g"=>"greater than", 35 "m"=>"less or equal", 36 "h"=>"greater or equal", 37 "bw"=>"between", 38 "a"=>"after", 39 "b"=>"before", 40 "y"=>"is empty", 41 ); 42 43 //$report_modules = Array('Faq','Rss','Portal','Recyclebin','Emails','Reports','Dashboard','Home','Activities' 44 // ); 45 46 $old_related_modules = Array('Accounts'=>Array('Potentials','Contacts','Products','Quotes','Invoice'), 47 'Contacts'=>Array('Accounts','Potentials','Quotes','PurchaseOrder'), 48 'Potentials'=>Array('Accounts','Contacts','Quotes'), 49 'Calendar'=>Array('Leads','Accounts','Contacts','Potentials'), 50 'Products'=>Array('Accounts','Contacts'), 51 'HelpDesk'=>Array('Products'), 52 'Quotes'=>Array('Accounts','Contacts','Potentials'), 53 'PurchaseOrder'=>Array('Contacts'), 54 'Invoice'=>Array('Accounts'), 55 'Campaigns'=>Array('Products'), 56 ); 57 58 $related_modules =Array(); 59 60 class Reports extends CRMEntity{ 61 62 63 64 /** 65 * This class has the informations for Reports and inherits class CRMEntity and 66 * has the variables required to generate,save,restore vtiger_reports 67 * and also the required functions for the same 68 * Contributor(s): ______________________________________.. 69 */ 70 71 72 var $srptfldridjs; 73 74 var $column_fields = Array(); 75 76 var $sort_fields = Array(); 77 var $sort_values = Array(); 78 79 var $id; 80 var $mode; 81 var $mcount; 82 83 var $startdate; 84 var $enddate; 85 86 var $ascdescorder; 87 88 var $stdselectedfilter; 89 var $stdselectedcolumn; 90 91 var $primodule; 92 var $secmodule; 93 var $columnssummary; 94 var $is_editable; 95 var $reporttype; 96 var $reportname; 97 var $reportdescription; 98 var $folderid; 99 var $module_blocks; 100 101 var $pri_module_columnslist; 102 var $sec_module_columnslist; 103 104 var $advft_criteria; 105 var $adv_rel_fields = Array(); 106 107 var $module_list = Array(); 108 109 /** Function to set primodule,secmodule,reporttype,reportname,reportdescription,folderid for given vtiger_reportid 110 * This function accepts the vtiger_reportid as argument 111 * It sets primodule,secmodule,reporttype,reportname,reportdescription,folderid for the given vtiger_reportid 112 */ 113 114 function Reports($reportid="") 115 { 116 global $adb,$current_user,$theme,$mod_strings; 117 $this->initListOfModules(); 118 if($reportid != "") 119 { 120 // Lookup information in cache first 121 $cachedInfo = VTCacheUtils::lookupReport_Info($current_user->id, $reportid); 122 $subordinate_users = VTCacheUtils::lookupReport_SubordinateUsers($reportid); 123 124 if($cachedInfo === false) { 125 $ssql = "select vtiger_reportmodules.*,vtiger_report.* from vtiger_report inner join vtiger_reportmodules on vtiger_report.reportid = vtiger_reportmodules.reportmodulesid"; 126 $ssql .= " where vtiger_report.reportid = ?"; 127 $params = array($reportid); 128 129 require_once ('include/utils/GetUserGroups.php'); 130 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 131 $userGroups = new GetUserGroups(); 132 $userGroups->getAllUserGroups($current_user->id); 133 $user_groups = $userGroups->user_groups; 134 if(!empty($user_groups) && $is_admin==false){ 135 $user_group_query = " (shareid IN (".generateQuestionMarks($user_groups).") AND setype='groups') OR"; 136 array_push($params, $user_groups); 137 } 138 139 $non_admin_query = " vtiger_report.reportid IN (SELECT reportid from vtiger_reportsharing WHERE $user_group_query (shareid=? AND setype='users'))"; 140 if($is_admin==false){ 141 $ssql .= " and ( (".$non_admin_query.") or vtiger_report.sharingtype='Public' or vtiger_report.owner = ? or vtiger_report.owner in(select vtiger_user2role.userid from vtiger_user2role inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid where vtiger_role.parentrole like '".$current_user_parent_role_seq."::%'))"; 142 array_push($params, $current_user->id); 143 array_push($params, $current_user->id); 144 } 145 146 $query = $adb->pquery("select userid from vtiger_user2role inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid where vtiger_role.parentrole like '".$current_user_parent_role_seq."::%'",array()); 147 $subordinate_users = Array(); 148 for($i=0;$i<$adb->num_rows($query);$i++){ 149 $subordinate_users[] = $adb->query_result($query,$i,'userid'); 150 } 151 152 // Update subordinate user information for re-use 153 VTCacheUtils::updateReport_SubordinateUsers($reportid, $subordinate_users); 154 155 $result = $adb->pquery($ssql, $params); 156 if($result && $adb->num_rows($result)) { 157 $reportmodulesrow = $adb->fetch_array($result); 158 159 // Update information in cache now 160 VTCacheUtils::updateReport_Info( 161 $current_user->id, $reportid, $reportmodulesrow["primarymodule"], 162 $reportmodulesrow["secondarymodules"], $reportmodulesrow["reporttype"], 163 $reportmodulesrow["reportname"], $reportmodulesrow["description"], 164 $reportmodulesrow["folderid"], $reportmodulesrow["owner"] 165 ); 166 } 167 168 // Re-look at cache to maintain code-consistency below 169 $cachedInfo = VTCacheUtils::lookupReport_Info($current_user->id, $reportid); 170 } 171 172 if($cachedInfo) { 173 $this->primodule = $cachedInfo["primarymodule"]; 174 $this->secmodule = $cachedInfo["secondarymodules"]; 175 $this->reporttype = $cachedInfo["reporttype"]; 176 $this->reportname = decode_html($cachedInfo["reportname"]); 177 $this->reportdescription = decode_html($cachedInfo["description"]); 178 $this->folderid = $cachedInfo["folderid"]; 179 if($is_admin==true || in_array($cachedInfo["owner"],$subordinate_users) || $cachedInfo["owner"]==$current_user->id) 180 $this->is_editable = 'true'; 181 else 182 $this->is_editable = 'false'; 183 } else { 184 if($_REQUEST['mode'] != 'ajax') 185 { 186 include('modules/Vtiger/header.php'); 187 } 188 echo "<table border='0' cellpadding='5' cellspacing='0' width='100%' height='450px'><tr><td align='center'>"; 189 echo "<div style='border: 3px solid rgb(153, 153, 153); background-color: rgb(255, 255, 255); width: 80%; position: relative; z-index: 10000000;'> 190 191 <table border='0' cellpadding='5' cellspacing='0' width='98%'> 192 <tbody><tr> 193 <td rowspan='2' width='11%'><img src='". vtiger_imageurl('denied.gif', $theme) ."' ></td> 194 <td style='border-bottom: 1px solid rgb(204, 204, 204);' nowrap='nowrap' width='70%'><span class='genHeaderSmall'>You are not allowed to View this Report </span></td> 195 </tr> 196 <tr> 197 <td class='small' align='right' nowrap='nowrap'> 198 <a href='javascript:window.history.back();'>$app_strings[LBL_GO_BACK]</a><br> </td> 199 </tr> 200 </tbody></table> 201 </div>"; 202 echo "</td></tr></table>"; 203 exit; 204 } 205 } 206 } 207 208 // Update the module list for listing columns for report creation. 209 function updateModuleList($module) { 210 global $adb; 211 if (!isset($module)) return; 212 require_once ('include/utils/utils.php'); 213 $tabid = getTabid($module); 214 if ($module == 'Calendar') { 215 $tabid = array(9, 16); 216 } 217 $sql = "SELECT blockid, blocklabel FROM vtiger_blocks WHERE tabid IN (". generateQuestionMarks($tabid) .")"; 218 $res = $adb->pquery($sql, array($tabid)); 219 $noOfRows = $adb->num_rows($res); 220 if ($noOfRows <= 0) return; 221 for($index = 0; $index < $noOfRows; ++$index) { 222 $blockid = $adb->query_result($res,$index,'blockid'); 223 if(in_array($blockid, $this->module_list[$module])) continue; 224 $blockid_list[] = $blockid; 225 $blocklabel = $adb->query_result($res,$index,'blocklabel'); 226 $this->module_list[$module][$blocklabel] = $blockid; 227 } 228 } 229 230 // Initializes the module list for listing columns for report creation. 231 function initListOfModules() { 232 global $adb, $current_user, $old_related_modules; 233 234 $restricted_modules = array('Events','Webmails'); 235 $restricted_blocks = array('LBL_COMMENTS','LBL_COMMENT_INFORMATION'); 236 237 $this->module_id = array(); 238 $this->module_list = array(); 239 240 // Prefetch module info to check active or not and also get list of tabs 241 $modulerows = vtlib_prefetchModuleActiveInfo(false); 242 243 $cachedInfo = VTCacheUtils::lookupReport_ListofModuleInfos(); 244 245 if($cachedInfo !== false) { 246 $this->module_list = $cachedInfo['module_list']; 247 $this->related_modules = $cachedInfo['related_modules']; 248 249 } else { 250 251 if($modulerows) { 252 foreach($modulerows as $resultrow) { 253 if($resultrow['presence'] == '1') continue; // skip disabled modules 254 if($resultrow['isentitytype'] != '1') continue; // skip extension modules 255 if(in_array($resultrow['name'], $restricted_modules)) { // skip restricted modules 256 continue; 257 } 258 if($resultrow['name']!='Calendar'){ 259 $this->module_id[$resultrow['tabid']] = $resultrow['name']; 260 } else { 261 $this->module_id[9] = $resultrow['name']; 262 $this->module_id[16] = $resultrow['name']; 263 264 } 265 $this->module_list[$resultrow['name']] = array(); 266 } 267 268 $moduleids = array_keys($this->module_id); 269 $reportblocks = 270 $adb->pquery("SELECT blockid, blocklabel, tabid FROM vtiger_blocks WHERE tabid IN (" .generateQuestionMarks($moduleids) .")", 271 array($moduleids)); 272 $prev_block_label = ''; 273 if($adb->num_rows($reportblocks)) { 274 while($resultrow = $adb->fetch_array($reportblocks)) { 275 $blockid = $resultrow['blockid']; 276 $blocklabel = $resultrow['blocklabel']; 277 $module = $this->module_id[$resultrow['tabid']]; 278 279 if(in_array($blocklabel, $restricted_blocks) || 280 in_array($blockid, $this->module_list[$module]) || 281 isset($this->module_list[$module][getTranslatedString($blocklabel,$module)]) 282 ) { 283 continue; 284 } 285 286 if(!empty($blocklabel)){ 287 if($module == 'Calendar' && $blocklabel == 'LBL_CUSTOM_INFORMATION') 288 $this->module_list[$module][$blockid] = getTranslatedString($blocklabel,$module); 289 else 290 $this->module_list[$module][$blockid] = getTranslatedString($blocklabel,$module); 291 $prev_block_label = $blocklabel; 292 } else { 293 $this->module_list[$module][$blockid] = getTranslatedString($prev_block_label,$module); 294 } 295 } 296 } 297 298 $relatedmodules = $adb->pquery( 299 "SELECT vtiger_tab.name, vtiger_relatedlists.tabid FROM vtiger_tab 300 INNER JOIN vtiger_relatedlists on vtiger_tab.tabid=vtiger_relatedlists.related_tabid 301 WHERE vtiger_tab.isentitytype=1 302 AND vtiger_tab.name NOT IN(".generateQuestionMarks($restricted_modules).") 303 AND vtiger_tab.presence = 0 AND vtiger_relatedlists.label!='Activity History' 304 UNION 305 SELECT module, vtiger_tab.tabid FROM vtiger_fieldmodulerel 306 INNER JOIN vtiger_tab on vtiger_tab.name = vtiger_fieldmodulerel.relmodule 307 WHERE vtiger_tab.isentitytype = 1 308 AND vtiger_tab.name NOT IN(".generateQuestionMarks($restricted_modules).") 309 AND vtiger_tab.presence = 0", 310 array($restricted_modules,$restricted_modules) 311 ); 312 if($adb->num_rows($relatedmodules)) { 313 while($resultrow = $adb->fetch_array($relatedmodules)) { 314 $module = $this->module_id[$resultrow['tabid']]; 315 316 if(!isset($this->related_modules[$module])) { 317 $this->related_modules[$module] = array(); 318 } 319 320 if($module != $resultrow['name']) { 321 $this->related_modules[$module][] = $resultrow['name']; 322 } 323 324 // To achieve Backward Compatability with Report relations 325 if(isset($old_related_modules[$module])){ 326 327 $rel_mod = array(); 328 foreach($old_related_modules[$module] as $key=>$name){ 329 if(vtlib_isModuleActive($name) && isPermitted($name,'index','')){ 330 $rel_mod[] = $name; 331 } 332 } 333 if(!empty($rel_mod)){ 334 $this->related_modules[$module] = array_merge($this->related_modules[$module],$rel_mod); 335 $this->related_modules[$module] = array_unique($this->related_modules[$module]); 336 } 337 } 338 } 339 } 340 foreach($this->related_modules as $module=>$related_modules) { 341 if($module == 'Emails') { 342 $this->related_modules[$module] = getEmailRelatedModules(); 343 } 344 } 345 // Put the information in cache for re-use 346 VTCacheUtils::updateReport_ListofModuleInfos($this->module_list, $this->related_modules); 347 } 348 } 349 } 350 // END 351 352 353 /** Function to get the Listview of Reports 354 * This function accepts no argument 355 * This generate the Reports view page and returns a string 356 * contains HTML 357 */ 358 359 function sgetRptFldr($mode='') 360 { 361 362 global $adb,$log,$mod_strings; 363 $returndata = Array(); 364 $sql = "select * from vtiger_reportfolder order by folderid"; 365 $result = $adb->pquery($sql, array()); 366 $reportfldrow = $adb->fetch_array($result); 367 if($mode != '') 368 { 369 // Fetch detials of all reports of folder at once 370 $reportsInAllFolders = $this->sgetRptsforFldr(false); 371 372 do 373 { 374 if($reportfldrow["state"] == $mode) 375 { 376 $details = Array(); 377 $details['state'] = $reportfldrow["state"]; 378 $details['id'] = $reportfldrow["folderid"]; 379 $details['name'] = ($mod_strings[$reportfldrow["foldername"]] == '' ) ? $reportfldrow["foldername"]:$mod_strings[$reportfldrow["foldername"]]; 380 $details['description'] = $reportfldrow["description"]; 381 $details['fname'] = popup_decode_html($details['name']); 382 $details['fdescription'] = popup_decode_html($reportfldrow["description"]); 383 $details['details'] = $reportsInAllFolders[$reportfldrow["folderid"]]; 384 $returndata[] = $details; 385 } 386 }while($reportfldrow = $adb->fetch_array($result)); 387 }else 388 { 389 do 390 { 391 $details = Array(); 392 $details['state'] = $reportfldrow["state"]; 393 $details['id'] = $reportfldrow["folderid"]; 394 $details['name'] = ($mod_strings[$reportfldrow["foldername"]] == '' ) ? $reportfldrow["foldername"]:$mod_strings[$reportfldrow["foldername"]]; 395 $details['description'] = $reportfldrow["description"]; 396 $details['fname'] = popup_decode_html($details['name']); 397 $details['fdescription'] = popup_decode_html($reportfldrow["description"]); 398 $returndata[] = $details; 399 }while($reportfldrow = $adb->fetch_array($result)); 400 } 401 402 $log->info("Reports :: ListView->Successfully returned vtiger_report folder HTML"); 403 return $returndata; 404 } 405 406 /** Function to get all Reports when in list view 407 * This function accepts the folderid,paramslist 408 * This Generates the Reports under each Reports module 409 * This Returns a HTML sring 410 */ 411 412 function sgetAllRpt($fldrId,$paramsList) 413 { 414 global $adb; 415 global $log; 416 $returndata=Array(); 417 $sql ="select vtiger_report.*, vtiger_reportmodules.*, vtiger_reportfolder.folderid from vtiger_report inner join vtiger_reportfolder on vtiger_reportfolder.folderid = vtiger_report.folderid"; 418 $sql.=" inner join vtiger_reportmodules on vtiger_reportmodules.reportmodulesid = vtiger_report.reportid"; 419 if($paramsList){ 420 $startIndex = $paramsList['startIndex']; 421 $pageLimit = $paramsList['pageLimit']; 422 $orderBy = $paramsList['orderBy']; 423 $sortBy = $paramsList['sortBy']; 424 if ($orderBy) { 425 $sql .= " ORDER BY $orderBy $sortBy"; 426 } 427 $sql .= " LIMIT $startIndex,".($pageLimit+1); 428 } 429 $result = $adb->pquery($sql,$params); 430 $report = $adb->fetch_array($result); 431 if(count($report)>0) 432 { 433 do 434 { 435 $report_details = Array(); 436 $report_details ['customizable'] = $report["customizable"]; 437 $report_details ['reportid'] = $report["reportid"]; 438 $report_details ['primarymodule'] = $report["primarymodule"]; 439 $report_details ['secondarymodules'] = $report["secondarymodules"]; 440 $report_details ['state'] = $report["state"]; 441 $report_details ['description'] = $report["description"]; 442 $report_details ['reportname'] = $report["reportname"]; 443 $report_details ['sharingtype'] = $report["sharingtype"]; 444 $report_details['folderid']=$report["folderid"]; 445 if($is_admin==true) 446 $report_details ['editable'] = 'true'; 447 else 448 $report_details['editable'] = 'false'; 449 450 if(isPermitted($report["primarymodule"],'index') == "yes") 451 $returndata[] = $report_details; 452 }while($report = $adb->fetch_array($result)); 453 } 454 $log->info("Reports :: ListView->Successfully returned vtiger_report details HTML"); 455 return $returndata; 456 } 457 458 459 /** Function to get the Reports inside each modules 460 * This function accepts the folderid 461 * This Generates the Reports under each Reports module 462 * This Returns a HTML sring 463 */ 464 465 function sgetRptsforFldr($rpt_fldr_id, $paramsList=false) 466 { 467 $srptdetails=""; 468 global $adb; 469 global $log; 470 global $mod_strings,$current_user; 471 $returndata = Array(); 472 473 require_once ('include/utils/UserInfoUtil.php'); 474 475 $sql = "select vtiger_report.*, vtiger_reportmodules.*, vtiger_reportfolder.folderid from vtiger_report inner join vtiger_reportfolder on vtiger_reportfolder.folderid = vtiger_report.folderid"; 476 $sql .= " inner join vtiger_reportmodules on vtiger_reportmodules.reportmodulesid = vtiger_report.reportid"; 477 478 $params = array(); 479 480 // If information is required only for specific report folder? 481 if($rpt_fldr_id !== false) { 482 $sql .= " where vtiger_reportfolder.folderid=?"; 483 $params[] = $rpt_fldr_id; 484 } 485 486 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 487 require_once ('include/utils/GetUserGroups.php'); 488 $userGroups = new GetUserGroups(); 489 $userGroups->getAllUserGroups($current_user->id); 490 $user_groups = $userGroups->user_groups; 491 if(!empty($user_groups) && $is_admin==false){ 492 $user_group_query = " (shareid IN (".generateQuestionMarks($user_groups).") AND setype='groups') OR"; 493 array_push($params, $user_groups); 494 } 495 496 $non_admin_query = " vtiger_report.reportid IN (SELECT reportid from vtiger_reportsharing WHERE $user_group_query (shareid=? AND setype='users'))"; 497 if($is_admin==false){ 498 $sql .= " and ( (".$non_admin_query.") or vtiger_report.sharingtype='Public' or vtiger_report.owner = ? or vtiger_report.owner in(select vtiger_user2role.userid from vtiger_user2role inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid where vtiger_role.parentrole like '".$current_user_parent_role_seq."::%'))"; 499 array_push($params, $current_user->id); 500 array_push($params, $current_user->id); 501 } 502 if ($paramsList) { 503 $startIndex = $paramsList['startIndex']; 504 $pageLimit = $paramsList['pageLimit']; 505 $orderBy = $paramsList['orderBy']; 506 $sortBy = $paramsList['sortBy']; 507 if ($orderBy) { 508 $sql .= " ORDER BY $orderBy $sortBy"; 509 } 510 $sql .= " LIMIT $startIndex,".($pageLimit+1); 511 } 512 $query = $adb->pquery("select userid from vtiger_user2role inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid where vtiger_role.parentrole like '".$current_user_parent_role_seq."::%'",array()); 513 $subordinate_users = Array(); 514 for($i=0;$i<$adb->num_rows($query);$i++){ 515 $subordinate_users[] = $adb->query_result($query,$i,'userid'); 516 } 517 $result = $adb->pquery($sql, $params); 518 519 $report = $adb->fetch_array($result); 520 if(count($report)>0) 521 { 522 do 523 { 524 $report_details = Array(); 525 $report_details ['customizable'] = $report["customizable"]; 526 $report_details ['reportid'] = $report["reportid"]; 527 $report_details ['primarymodule'] = $report["primarymodule"]; 528 $report_details ['secondarymodules'] = $report["secondarymodules"]; 529 $report_details ['state'] = $report["state"]; 530 $report_details ['description'] = $report["description"]; 531 $report_details ['reportname'] = $report["reportname"]; 532 $report_details ['reporttype'] = $report["reporttype"]; 533 $report_details ['sharingtype'] = $report["sharingtype"]; 534 if($is_admin==true || in_array($report["owner"],$subordinate_users) || $report["owner"]==$current_user->id) 535 $report_details ['editable'] = 'true'; 536 else 537 $report_details['editable'] = 'false'; 538 539 if(isPermitted($report["primarymodule"],'index') == "yes") 540 $returndata [$report["folderid"]][] = $report_details; 541 }while($report = $adb->fetch_array($result)); 542 } 543 544 if($rpt_fldr_id !== false) { 545 $returndata = $returndata[$rpt_fldr_id]; 546 } 547 548 $log->info("Reports :: ListView->Successfully returned vtiger_report details HTML"); 549 return $returndata; 550 } 551 552 /** Function to get the array of ids 553 * This function forms the array for the ExpandCollapse 554 * Javascript 555 * It returns the array of ids 556 * Array('1RptFldr','2RptFldr',........,'9RptFldr','10RptFldr') 557 */ 558 559 function sgetJsRptFldr() 560 { 561 $srptfldr_js = "var ReportListArray=new Array(".$this->srptfldridjs.") 562 setExpandCollapse()"; 563 return $srptfldr_js; 564 } 565 566 /** Function to set the Primary module vtiger_fields for the given Report 567 * This function sets the primary module columns for the given Report 568 * It accepts the Primary module as the argument and set the vtiger_fields of the module 569 * to the varialbe pri_module_columnslist and returns true if sucess 570 */ 571 572 function getPriModuleColumnsList($module) 573 { 574 //$this->updateModuleList($module); 575 $allColumnsListByBlocks =& $this->getColumnsListbyBlock($module, array_keys($this->module_list[$module]), true); 576 foreach($this->module_list[$module] as $key=>$value) { 577 $temp = $allColumnsListByBlocks[$key]; 578 $this->fixGetColumnsListbyBlockForInventory($module, $key, $temp); 579 580 if (!empty($ret_module_list[$module][$value])) { 581 if (!empty($temp)) { 582 $ret_module_list[$module][$value] = array_merge($ret_module_list[$module][$value], $temp); 583 } 584 } else { 585 $ret_module_list[$module][$value] = $temp; 586 } 587 } 588 if($module == 'Emails') { 589 foreach($ret_module_list[$module] as $key => $value) { 590 foreach($value as $key1 => $value1) { 591 if($key1 == 'vtiger_activity:time_start:Emails_Time_Start:time_start:T') { 592 unset($ret_module_list[$module][$key][$key1]); 593 } 594 } 595 } 596 } 597 $this->pri_module_columnslist = $ret_module_list; 598 return true; 599 } 600 601 /** Function to set the Secondary module fileds for the given Report 602 * This function sets the secondary module columns for the given module 603 * It accepts the module as the argument and set the vtiger_fields of the module 604 * to the varialbe sec_module_columnslist and returns true if sucess 605 */ 606 607 function getSecModuleColumnsList($module) 608 { 609 if($module != "") 610 { 611 $secmodule = explode(":",$module); 612 for($i=0;$i < count($secmodule) ;$i++) 613 { 614 //$this->updateModuleList($secmodule[$i]); 615 if($this->module_list[$secmodule[$i]]){ 616 $this->sec_module_columnslist[$secmodule[$i]] = $this->getModuleFieldList( 617 $secmodule[$i]); 618 if($this->module_list[$secmodule[$i]] == 'Calendar') { 619 if($this->module_list['Events']){ 620 $this->sec_module_columnslist['Events'] = $this->getModuleFieldList( 621 'Events'); 622 } 623 } 624 } 625 } 626 if($module == 'Emails') { 627 foreach($this->sec_module_columnslist[$module] as $key => $value) { 628 foreach($value as $key1 => $value1) { 629 if($key1 == 'vtiger_activity:time_start:Emails_Time_Start:time_start:T') { 630 unset($this->sec_module_columnslist[$module][$key][$key1]); 631 } 632 } 633 } 634 } 635 } 636 return true; 637 } 638 639 /** 640 * 641 * @param String $module 642 * @param type $blockIdList 643 * @param Array $currentFieldList 644 * @return Array 645 */ 646 public function getBlockFieldList($module, $blockIdList, $currentFieldList,$allColumnsListByBlocks) { 647 $temp = $allColumnsListByBlocks[$blockIdList]; 648 $this->fixGetColumnsListbyBlockForInventory($module, $blockIdList, $temp); 649 if(!empty($currentFieldList)){ 650 if(!empty($temp)){ 651 $currentFieldList = array_merge($currentFieldList,$temp); 652 } 653 } else { 654 $currentFieldList = $temp; 655 } 656 return $currentFieldList; 657 } 658 659 public function getModuleFieldList($module) { 660 $allColumnsListByBlocks =& $this->getColumnsListbyBlock($module, array_keys($this->module_list[$module]), true); 661 foreach($this->module_list[$module] as $key=>$value) { 662 $ret_module_list[$module][$value] = $this->getBlockFieldList( 663 $module, $key, $ret_module_list[$module][$value],$allColumnsListByBlocks); 664 } 665 return $ret_module_list[$module]; 666 } 667 668 /** Function to get vtiger_fields for the given module and block 669 * This function gets the vtiger_fields for the given module 670 * It accepts the module and the block as arguments and 671 * returns the array column lists 672 * Array module_columnlist[ vtiger_fieldtablename:fieldcolname:module_fieldlabel1:fieldname:fieldtypeofdata]=fieldlabel 673 */ 674 675 function getColumnsListbyBlock($module,$block,$group_res_by_block=false) 676 { 677 global $adb; 678 global $log; 679 global $current_user; 680 681 if(is_string($block)) $block = explode(",", $block); 682 $skipTalbes = array('vtiger_emaildetails','vtiger_attachments'); 683 684 $tabid = getTabid($module); 685 if ($module == 'Calendar') { 686 $tabid = array('9','16'); 687 } 688 $params = array($tabid, $block); 689 690 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 691 //Security Check 692 if($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] ==0) 693 { 694 $sql = "select * from vtiger_field where vtiger_field.tabid in (". generateQuestionMarks($tabid) .") and vtiger_field.block in (". generateQuestionMarks($block) .") and vtiger_field.displaytype in (1,2,3) and vtiger_field.presence in (0,2) AND tablename NOT IN (".generateQuestionMarks($skipTalbes).") "; 695 696 //fix for Ticket #4016 697 if($module == "Calendar") 698 $sql.=" group by vtiger_field.fieldlabel order by sequence"; 699 else 700 $sql.=" order by sequence"; 701 } 702 else 703 { 704 705 $profileList = getCurrentUserProfileList(); 706 $sql = "select * from vtiger_field inner join vtiger_profile2field on vtiger_profile2field.fieldid=vtiger_field.fieldid inner join vtiger_def_org_field on vtiger_def_org_field.fieldid=vtiger_field.fieldid where vtiger_field.tabid in (". generateQuestionMarks($tabid) .") and vtiger_field.block in (". generateQuestionMarks($block) .") and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.presence in (0,2)"; 707 if (count($profileList) > 0) { 708 $sql .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")"; 709 array_push($params, $profileList); 710 } 711 $sql .= ' and tablename NOT IN ('.generateQuestionMarks($skipTalbes).') '; 712 713 //fix for Ticket #4016 714 if($module == "Calendar") 715 $sql.=" group by vtiger_field.fieldlabel order by sequence"; 716 else 717 $sql.=" group by vtiger_field.fieldid order by sequence"; 718 } 719 array_push($params, $skipTalbes); 720 721 $result = $adb->pquery($sql, $params); 722 $noofrows = $adb->num_rows($result); 723 for($i=0; $i<$noofrows; $i++) 724 { 725 $fieldtablename = $adb->query_result($result,$i,"tablename"); 726 $fieldcolname = $adb->query_result($result,$i,"columnname"); 727 $fieldname = $adb->query_result($result,$i,"fieldname"); 728 $fieldtype = $adb->query_result($result,$i,"typeofdata"); 729 $uitype = $adb->query_result($result,$i,"uitype"); 730 $fieldtype = explode("~",$fieldtype); 731 $fieldtypeofdata = $fieldtype[0]; 732 $blockid = $adb->query_result($result, $i, "block"); 733 734 //Here we Changing the displaytype of the field. So that its criteria will be displayed correctly in Reports Advance Filter. 735 $fieldtypeofdata=ChangeTypeOfData_Filter($fieldtablename,$fieldcolname,$fieldtypeofdata); 736 737 if($uitype == 68 || $uitype == 59) 738 { 739 $fieldtypeofdata = 'V'; 740 } 741 if($fieldtablename == "vtiger_crmentity") 742 { 743 $fieldtablename = $fieldtablename.$module; 744 } 745 if($fieldname == "assigned_user_id") 746 { 747 $fieldtablename = "vtiger_users".$module; 748 $fieldcolname = "user_name"; 749 } 750 if($fieldname == "assigned_user_id1") 751 { 752 $fieldtablename = "vtiger_usersRel1"; 753 $fieldcolname = "user_name"; 754 } 755 756 $fieldlabel = $adb->query_result($result,$i,"fieldlabel"); 757 if ($module == 'Emails' and $fieldlabel == 'Date & Time Sent') { 758 $fieldlabel = 'Date Sent'; 759 $fieldtypeofdata = 'D'; 760 } 761 $fieldlabel1 = str_replace(" ","_",$fieldlabel); 762 $optionvalue = $fieldtablename.":".$fieldcolname.":".$module."_".$fieldlabel1.":".$fieldname.":".$fieldtypeofdata; 763 764 $adv_rel_field_tod_value = '$'.$module.'#'.$fieldname.'$'."::".getTranslatedString($module,$module)." ".getTranslatedString($fieldlabel,$module); 765 if (!is_array($this->adv_rel_fields[$fieldtypeofdata]) || 766 !in_array($adv_rel_field_tod_value, $this->adv_rel_fields[$fieldtypeofdata])) { 767 $this->adv_rel_fields[$fieldtypeofdata][] = $adv_rel_field_tod_value; 768 } 769 //added to escape attachments fields in Reports as we have multiple attachments 770 if($module == 'HelpDesk' && $fieldname =='filename') continue; 771 772 if (is_string($block) || $group_res_by_block == false) { 773 $module_columnlist[$optionvalue] = $fieldlabel; 774 } else { 775 $module_columnlist[$blockid][$optionvalue] = $fieldlabel; 776 } 777 } 778 if (is_string($block)) { 779 $this->fixGetColumnsListbyBlockForInventory($module, $block, $module_columnlist); 780 } 781 return $module_columnlist; 782 } 783 784 function fixGetColumnsListbyBlockForInventory($module, $blockid, &$module_columnlist) { 785 global $log; 786 787 $blockname = getBlockName($blockid); 788 if($blockname == 'LBL_RELATED_PRODUCTS' && ($module=='PurchaseOrder' || $module=='SalesOrder' || $module=='Quotes' || $module=='Invoice')){ 789 $fieldtablename = 'vtiger_inventoryproductrel'; 790 $fields = array('productid'=>getTranslatedString('Product Name',$module), 791 'serviceid'=>getTranslatedString('Service Name',$module), 792 'listprice'=>getTranslatedString('List Price',$module), 793 'discount_amount'=>getTranslatedString('Discount',$module), 794 'quantity'=>getTranslatedString('Quantity',$module), 795 'comment'=>getTranslatedString('Comments',$module), 796 ); 797 $fields_datatype = array('productid'=>'V', 798 'serviceid'=>'V', 799 'listprice'=>'I', 800 'discount_amount'=>'I', 801 'quantity'=>'I', 802 'comment'=>'V', 803 ); 804 foreach($fields as $fieldcolname=>$label){ 805 $column_name = str_replace(' ', '_', $label); 806 $fieldtypeofdata = $fields_datatype[$fieldcolname]; 807 $optionvalue = $fieldtablename.":".$fieldcolname.":".$module."_".$column_name.":".$fieldcolname.":".$fieldtypeofdata; 808 $module_columnlist[$optionvalue] = $label; 809 } 810 } 811 $log->info("Reports :: FieldColumns->Successfully returned ColumnslistbyBlock".$module.$block); 812 return $module_columnlist; 813 } 814 815 /** Function to set the standard filter vtiger_fields for the given vtiger_report 816 * This function gets the standard filter vtiger_fields for the given vtiger_report 817 * and set the values to the corresponding variables 818 * It accepts the repordid as argument 819 */ 820 821 function getSelectedStandardCriteria($reportid) { 822 global $adb; 823 $sSQL = "select vtiger_reportdatefilter.* from vtiger_reportdatefilter inner join vtiger_report on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid where vtiger_report.reportid=?"; 824 $result = $adb->pquery($sSQL, array($reportid)); 825 $selectedstdfilter = $adb->fetch_array($result); 826 827 $this->stdselectedcolumn = $selectedstdfilter["datecolumnname"]; 828 $this->stdselectedfilter = $selectedstdfilter["datefilter"]; 829 830 if($selectedstdfilter["datefilter"] == "custom") { 831 if($selectedstdfilter["startdate"] != "0000-00-00") { 832 $startDateTime = new DateTimeField($selectedstdfilter["startdate"].' '. date('H:i:s')); 833 $this->startdate = $startDateTime->getDisplayDate(); 834 } 835 if($selectedstdfilter["enddate"] != "0000-00-00") { 836 $endDateTime = new DateTimeField($selectedstdfilter["enddate"].' '. date('H:i:s')); 837 $this->enddate = $endDateTime->getDisplayDate(); 838 } 839 } 840 } 841 842 /** Function to get the combo values for the standard filter 843 * This function get the combo values for the standard filter for the given vtiger_report 844 * and return a HTML string 845 */ 846 847 function getSelectedStdFilterCriteria($selecteddatefilter = "") 848 { 849 global $mod_strings; 850 851 $datefiltervalue = Array("custom","prevfy","thisfy","nextfy","prevfq","thisfq","nextfq", 852 "yesterday","today","tomorrow","lastweek","thisweek","nextweek","lastmonth","thismonth", 853 "nextmonth","last7days","last30days", "last60days","last90days","last120days", 854 "next30days","next60days","next90days","next120days" 855 ); 856 857 $datefilterdisplay = Array("Custom","Previous FY", "Current FY","Next FY","Previous FQ","Current FQ","Next FQ","Yesterday", 858 "Today","Tomorrow","Last Week","Current Week","Next Week","Last Month","Current Month", 859 "Next Month","Last 7 Days","Last 30 Days","Last 60 Days","Last 90 Days","Last 120 Days", 860 "Next 7 Days","Next 30 Days","Next 60 Days","Next 90 Days","Next 120 Days" 861 ); 862 863 for($i=0;$i<count($datefiltervalue);$i++) 864 { 865 if($selecteddatefilter == $datefiltervalue[$i]) 866 { 867 $sshtml .= "<option selected value='".$datefiltervalue[$i]."'>".$mod_strings[$datefilterdisplay[$i]]."</option>"; 868 }else 869 { 870 $sshtml .= "<option value='".$datefiltervalue[$i]."'>".$mod_strings[$datefilterdisplay[$i]]."</option>"; 871 } 872 } 873 874 return $sshtml; 875 } 876 877 /** Function to get the selected standard filter columns 878 * This function returns the selected standard filter criteria 879 * which is selected for vtiger_reports as an array 880 * Array stdcriteria_list[fieldtablename:fieldcolname:module_fieldlabel1]=fieldlabel 881 */ 882 883 function getStdCriteriaByModule($module) 884 { 885 global $adb; 886 global $log; 887 global $current_user; 888 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 889 890 $tabid = getTabid($module); 891 foreach($this->module_list[$module] as $key=>$blockid) 892 { 893 $blockids[] = $blockid; 894 } 895 $blockids = implode(",",$blockids); 896 897 $params = array($tabid, $blockids); 898 if($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] == 0) 899 { 900 //uitype 6 and 23 added for start_date,EndDate,Expected Close Date 901 $sql = "select * from vtiger_field where vtiger_field.tabid=? and (vtiger_field.uitype =5 or vtiger_field.uitype = 6 or vtiger_field.uitype = 23 or vtiger_field.displaytype=2) and vtiger_field.block in (". generateQuestionMarks($block) .") and vtiger_field.presence in (0,2) order by vtiger_field.sequence"; 902 } 903 else 904 { 905 $profileList = getCurrentUserProfileList(); 906 $sql = "select * from vtiger_field inner join vtiger_tab on vtiger_tab.tabid = vtiger_field.tabid inner join vtiger_profile2field on vtiger_profile2field.fieldid=vtiger_field.fieldid inner join vtiger_def_org_field on vtiger_def_org_field.fieldid=vtiger_field.fieldid where vtiger_field.tabid=? and (vtiger_field.uitype =5 or vtiger_field.displaytype=2) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.block in (". generateQuestionMarks($block) .") and vtiger_field.presence in (0,2)"; 907 if (count($profileList) > 0) { 908 $sql .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")"; 909 array_push($params, $profileList); 910 } 911 $sql .= " order by vtiger_field.sequence"; 912 } 913 914 $result = $adb->pquery($sql, $params); 915 916 while($criteriatyperow = $adb->fetch_array($result)) 917 { 918 $fieldtablename = $criteriatyperow["tablename"]; 919 $fieldcolname = $criteriatyperow["columnname"]; 920 $fieldlabel = $criteriatyperow["fieldlabel"]; 921 922 if($fieldtablename == "vtiger_crmentity") 923 { 924 $fieldtablename = $fieldtablename.$module; 925 } 926 $fieldlabel1 = str_replace(" ","_",$fieldlabel); 927 $optionvalue = $fieldtablename.":".$fieldcolname.":".$module."_".$fieldlabel1; 928 $stdcriteria_list[$optionvalue] = $fieldlabel; 929 } 930 931 $log->info("Reports :: StdfilterColumns->Successfully returned Stdfilter for".$module); 932 return $stdcriteria_list; 933 934 } 935 936 /** Function to form a javascript to determine the start date and end date for a standard filter 937 * This function is to form a javascript to determine 938 * the start date and End date from the value selected in the combo lists 939 */ 940 941 function getCriteriaJS() 942 { 943 944 $todayDateTime = new DateTimeField(date('Y-m-d H:i:s')); 945 946 $tomorrow = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+1, date("Y"))); 947 $tomorrowDateTime = new DateTimeField($tomorrow.' '. date('H:i:s')); 948 949 $yesterday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-1, date("Y"))); 950 $yesterdayDateTime = new DateTimeField($yesterday.' '. date('H:i:s')); 951 952 $currentmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m"), "01", date("Y"))); 953 $currentMonthStartDateTime = new DateTimeField($currentmonth0.' '. date('H:i:s')); 954 $currentmonth1 = date("Y-m-t"); 955 $currentMonthEndDateTime = new DateTimeField($currentmonth1.' '. date('H:i:s')); 956 957 $lastmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")-1, "01", date("Y"))); 958 $lastMonthStartDateTime = new DateTimeField($lastmonth0.' '. date('H:i:s')); 959 $lastmonth1 = date("Y-m-t", strtotime("last day of previous month")); 960 $lastMonthEndDateTime = new DateTimeField($lastmonth1.' '. date('H:i:s')); 961 962 $nextmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")+1, "01", date("Y"))); 963 $nextMonthStartDateTime = new DateTimeField($nextmonth0.' '. date('H:i:s')); 964 $nextmonth1 = date("Y-m-t", strtotime("last day of next month")); 965 $nextMonthEndDateTime = new DateTimeField($nextmonth1.' '. date('H:i:s')); 966 967 $lastweek0 = date("Y-m-d",strtotime("-2 week Monday")); 968 $lastWeekStartDateTime = new DateTimeField($lastweek0.' '. date('H:i:s')); 969 $lastweek1 = date("Y-m-d",strtotime("-1 week Sunday")); 970 $lastWeekEndDateTime = new DateTimeField($lastweek1.' '. date('H:i:s')); 971 972 $thisweek0 = date("Y-m-d",strtotime("-1 week Monday")); 973 $thisWeekStartDateTime = new DateTimeField($thisweek0.' '. date('H:i:s')); 974 $thisweek1 = date("Y-m-d",strtotime("this Sunday")); 975 $thisWeekEndDateTime = new DateTimeField($thisweek1.' '. date('H:i:s')); 976 977 $nextweek0 = date("Y-m-d",strtotime("this Monday")); 978 $nextWeekStartDateTime = new DateTimeField($nextweek0.' '. date('H:i:s')); 979 $nextweek1 = date("Y-m-d",strtotime("+1 week Sunday")); 980 $nextWeekEndDateTime = new DateTimeField($nextweek1.' '. date('H:i:s')); 981 982 $next7days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+6, date("Y"))); 983 $next7DaysDateTime = new DateTimeField($next7days.' '. date('H:i:s')); 984 985 $next30days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+29, date("Y"))); 986 $next30DaysDateTime = new DateTimeField($next30days.' '. date('H:i:s')); 987 988 $next60days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+59, date("Y"))); 989 $next60DaysDateTime = new DateTimeField($next60days.' '. date('H:i:s')); 990 991 $next90days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+89, date("Y"))); 992 $next90DaysDateTime = new DateTimeField($next90days.' '. date('H:i:s')); 993 994 $next120days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+119, date("Y"))); 995 $next120DaysDateTime = new DateTimeField($next120days.' '. date('H:i:s')); 996 997 $last7days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-6, date("Y"))); 998 $last7DaysDateTime = new DateTimeField($last7days.' '. date('H:i:s')); 999 1000 $last30days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-29, date("Y"))); 1001 $last30DaysDateTime = new DateTimeField($last30days.' '. date('H:i:s')); 1002 1003 $last60days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-59, date("Y"))); 1004 $last60DaysDateTime = new DateTimeField($last60days.' '. date('H:i:s')); 1005 1006 $last90days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-89, date("Y"))); 1007 $last90DaysDateTime = new DateTimeField($last90days.' '. date('H:i:s')); 1008 1009 $last120days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-119, date("Y"))); 1010 $last120DaysDateTime = new DateTimeField($last120days.' '. date('H:i:s')); 1011 1012 $currentFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y"))); 1013 $currentFYStartDateTime = new DateTimeField($currentFY0.' '. date('H:i:s')); 1014 $currentFY1 = date("Y-m-t",mktime(0, 0, 0, "12", date("d"), date("Y"))); 1015 $currentFYEndDateTime = new DateTimeField($currentFY1.' '. date('H:i:s')); 1016 1017 $lastFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y")-1)); 1018 $lastFYStartDateTime = new DateTimeField($lastFY0.' '. date('H:i:s')); 1019 $lastFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")-1)); 1020 $lastFYEndDateTime = new DateTimeField($lastFY1.' '. date('H:i:s')); 1021 1022 $nextFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y")+1)); 1023 $nextFYStartDateTime = new DateTimeField($nextFY0.' '. date('H:i:s')); 1024 $nextFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")+1)); 1025 $nextFYEndDateTime = new DateTimeField($nextFY1.' '. date('H:i:s')); 1026 1027 if(date("m") <= 3) { 1028 $cFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y"))); 1029 $cFqStartDateTime = new DateTimeField($cFq.' '. date('H:i:s')); 1030 $cFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y"))); 1031 $cFqEndDateTime = new DateTimeField($cFq1.' '. date('H:i:s')); 1032 1033 $nFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y"))); 1034 $nFqStartDateTime = new DateTimeField($nFq.' '. date('H:i:s')); 1035 $nFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y"))); 1036 $nFqEndDateTime = new DateTimeField($nFq1.' '. date('H:i:s')); 1037 1038 $pFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y")-1)); 1039 $pFqStartDateTime = new DateTimeField($pFq.' '. date('H:i:s')); 1040 $pFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y")-1)); 1041 $pFqEndDateTime = new DateTimeField($pFq1.' '. date('H:i:s')); 1042 1043 } else if(date("m") > 3 and date("m") <= 6) { 1044 1045 $pFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y"))); 1046 $pFqStartDateTime = new DateTimeField($pFq.' '. date('H:i:s')); 1047 $pFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y"))); 1048 $pFqEndDateTime = new DateTimeField($pFq1.' '. date('H:i:s')); 1049 1050 $cFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y"))); 1051 $cFqStartDateTime = new DateTimeField($cFq.' '. date('H:i:s')); 1052 $cFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y"))); 1053 $cFqEndDateTime = new DateTimeField($cFq1.' '. date('H:i:s')); 1054 1055 $nFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y"))); 1056 $nFqStartDateTime = new DateTimeField($nFq.' '. date('H:i:s')); 1057 $nFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y"))); 1058 $nFqEndDateTime = new DateTimeField($nFq1.' '. date('H:i:s')); 1059 1060 } else if(date("m") > 6 and date("m") <= 9) { 1061 1062 $nFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y"))); 1063 $nFqStartDateTime = new DateTimeField($nFq.' '. date('H:i:s')); 1064 $nFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y"))); 1065 $nFqEndDateTime = new DateTimeField($nFq1.' '. date('H:i:s')); 1066 1067 $pFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y"))); 1068 $pFqStartDateTime = new DateTimeField($pFq.' '. date('H:i:s')); 1069 $pFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y"))); 1070 $pFqEndDateTime = new DateTimeField($pFq1.' '. date('H:i:s')); 1071 1072 $cFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y"))); 1073 $cFqStartDateTime = new DateTimeField($cFq.' '. date('H:i:s')); 1074 $cFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y"))); 1075 $cFqEndDateTime = new DateTimeField($cFq1.' '. date('H:i:s')); 1076 1077 } else if(date("m") > 9 and date("m") <= 12) { 1078 $nFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y")+1)); 1079 $nFqStartDateTime = new DateTimeField($nFq.' '. date('H:i:s')); 1080 $nFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y")+1)); 1081 $nFqEndDateTime = new DateTimeField($nFq1.' '. date('H:i:s')); 1082 1083 $pFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y"))); 1084 $pFqStartDateTime = new DateTimeField($pFq.' '. date('H:i:s')); 1085 $pFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y"))); 1086 $pFqEndDateTime = new DateTimeField($pFq1.' '. date('H:i:s')); 1087 1088 $cFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y"))); 1089 $cFqStartDateTime = new DateTimeField($cFq.' '. date('H:i:s')); 1090 $cFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y"))); 1091 $cFqEndDateTime = new DateTimeField($cFq1.' '. date('H:i:s')); 1092 } 1093 1094 $sjsStr = '<script language="JavaScript" type="text/javaScript"> 1095 function showDateRange( type ) { 1096 if (type!="custom") { 1097 document.NewReport.startdate.readOnly=true 1098 document.NewReport.enddate.readOnly=true 1099 getObj("jscal_trigger_date_start").style.visibility="hidden" 1100 getObj("jscal_trigger_date_end").style.visibility="hidden" 1101 } else { 1102 document.NewReport.startdate.readOnly=false 1103 document.NewReport.enddate.readOnly=false 1104 getObj("jscal_trigger_date_start").style.visibility="visible" 1105 getObj("jscal_trigger_date_end").style.visibility="visible" 1106 } 1107 if( type == "today" ) { 1108 document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'"; 1109 document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'"; 1110 1111 } else if( type == "yesterday" ) { 1112 document.NewReport.startdate.value = "'.$yesterdayDateTime->getDisplayDate().'"; 1113 document.NewReport.enddate.value = "'.$yesterdayDateTime->getDisplayDate().'"; 1114 1115 } else if( type == "tomorrow" ) { 1116 document.NewReport.startdate.value = "'.$tomorrowDateTime->getDisplayDate().'"; 1117 document.NewReport.enddate.value = "'.$tomorrowDateTime->getDisplayDate().'"; 1118 1119 } else if( type == "thisweek" ) { 1120 document.NewReport.startdate.value = "'.$thisWeekStartDateTime->getDisplayDate().'"; 1121 document.NewReport.enddate.value = "'.$thisWeekEndDateTime->getDisplayDate().'"; 1122 1123 } else if( type == "lastweek" ) { 1124 document.NewReport.startdate.value = "'.$lastWeekStartDateTime->getDisplayDate().'"; 1125 document.NewReport.enddate.value = "'.$lastWeekEndDateTime->getDisplayDate().'"; 1126 1127 } else if( type == "nextweek" ) { 1128 document.NewReport.startdate.value = "'.$nextWeekStartDateTime->getDisplayDate().'"; 1129 document.NewReport.enddate.value = "'.$nextWeekEndDateTime->getDisplayDate().'"; 1130 1131 } else if( type == "thismonth" ) { 1132 document.NewReport.startdate.value = "'.$currentMonthStartDateTime->getDisplayDate().'"; 1133 document.NewReport.enddate.value = "'.$currentMonthEndDateTime->getDisplayDate().'"; 1134 1135 } else if( type == "lastmonth" ) { 1136 document.NewReport.startdate.value = "'.$lastMonthStartDateTime->getDisplayDate().'"; 1137 document.NewReport.enddate.value = "'.$lastMonthEndDateTime->getDisplayDate().'"; 1138 1139 } else if( type == "nextmonth" ) { 1140 document.NewReport.startdate.value = "'.$nextMonthStartDateTime->getDisplayDate().'"; 1141 document.NewReport.enddate.value = "'.$nextMonthEndDateTime->getDisplayDate().'"; 1142 1143 } else if( type == "next7days" ) { 1144 document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'"; 1145 document.NewReport.enddate.value = "'.$next7DaysDateTime->getDisplayDate().'"; 1146 1147 } else if( type == "next30days" ) { 1148 document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'"; 1149 document.NewReport.enddate.value = "'.$next30DaysDateTime->getDisplayDate().'"; 1150 1151 } else if( type == "next60days" ) { 1152 document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'"; 1153 document.NewReport.enddate.value = "'.$next60DaysDateTime->getDisplayDate().'"; 1154 1155 } else if( type == "next90days" ) { 1156 document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'"; 1157 document.NewReport.enddate.value = "'.$next90DaysDateTime->getDisplayDate().'"; 1158 1159 } else if( type == "next120days" ) { 1160 document.NewReport.startdate.value = "'.$todayDateTime->getDisplayDate().'"; 1161 document.NewReport.enddate.value = "'.$next120DaysDateTime->getDisplayDate().'"; 1162 1163 } else if( type == "last7days" ) { 1164 document.NewReport.startdate.value = "'.$last7DaysDateTime->getDisplayDate().'"; 1165 document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'"; 1166 1167 } else if( type == "last30days" ) { 1168 document.NewReport.startdate.value = "'.$last30DaysDateTime->getDisplayDate().'"; 1169 document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'"; 1170 1171 } else if( type == "last60days" ) { 1172 document.NewReport.startdate.value = "'.$last60DaysDateTime->getDisplayDate().'"; 1173 document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'"; 1174 1175 } else if( type == "last90days" ) { 1176 document.NewReport.startdate.value = "'.$last90DaysDateTime->getDisplayDate().'"; 1177 document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'"; 1178 1179 } else if( type == "last120days" ) { 1180 document.NewReport.startdate.value = "'.$last120DaysDateTime->getDisplayDate().'"; 1181 document.NewReport.enddate.value = "'.$todayDateTime->getDisplayDate().'"; 1182 1183 } else if( type == "thisfy" ) { 1184 document.NewReport.startdate.value = "'.$currentFYStartDateTime->getDisplayDate().'"; 1185 document.NewReport.enddate.value = "'.$currentFYEndDateTime->getDisplayDate().'"; 1186 1187 } else if( type == "prevfy" ) { 1188 document.NewReport.startdate.value = "'.$lastFYStartDateTime->getDisplayDate().'"; 1189 document.NewReport.enddate.value = "'.$lastFYEndDateTime->getDisplayDate().'"; 1190 1191 } else if( type == "nextfy" ) { 1192 document.NewReport.startdate.value = "'.$nextFYStartDateTime->getDisplayDate().'"; 1193 document.NewReport.enddate.value = "'.$nextFYEndDateTime->getDisplayDate().'"; 1194 1195 } else if( type == "nextfq" ) { 1196 document.NewReport.startdate.value = "'.$nFqStartDateTime->getDisplayDate().'"; 1197 document.NewReport.enddate.value = "'.$nFqEndDateTime->getDisplayDate().'"; 1198 1199 } else if( type == "prevfq" ) { 1200 document.NewReport.startdate.value = "'.$pFqStartDateTime->getDisplayDate().'"; 1201 document.NewReport.enddate.value = "'.$pFqEndDateTime->getDisplayDate().'"; 1202 1203 } else if( type == "thisfq" ) { 1204 document.NewReport.startdate.value = "'.$cFqStartDateTime->getDisplayDate().'"; 1205 document.NewReport.enddate.value = "'.$cFqEndDateTime->getDisplayDate().'"; 1206 1207 } else { 1208 document.NewReport.startdate.value = ""; 1209 document.NewReport.enddate.value = ""; 1210 } 1211 } 1212 </script>'; 1213 1214 return $sjsStr; 1215 } 1216 function getEscapedColumns($selectedfields) 1217 { 1218 $fieldname = $selectedfields[3]; 1219 if($fieldname == "parent_id") 1220 { 1221 if($this->primarymodule == "HelpDesk" && $selectedfields[0] == "vtiger_crmentityRelHelpDesk") 1222 { 1223 $querycolumn = "case vtiger_crmentityRelHelpDesk.setype when 'Accounts' then vtiger_accountRelHelpDesk.accountname when 'Contacts' then vtiger_contactdetailsRelHelpDesk.lastname End"." '".$selectedfields[2]."', vtiger_crmentityRelHelpDesk.setype 'Entity_type'"; 1224 return $querycolumn; 1225 } 1226 if($this->primarymodule == "Products" || $this->secondarymodule == "Products") 1227 { 1228 $querycolumn = "case vtiger_crmentityRelProducts.setype when 'Accounts' then vtiger_accountRelProducts.accountname when 'Leads' then vtiger_leaddetailsRelProducts.lastname when 'Potentials' then vtiger_potentialRelProducts.potentialname End"." '".$selectedfields[2]."', vtiger_crmentityRelProducts.setype 'Entity_type'"; 1229 } 1230 if($this->primarymodule == "Calendar" || $this->secondarymodule == "Calendar") 1231 { 1232 $querycolumn = "case vtiger_crmentityRelCalendar.setype when 'Accounts' then vtiger_accountRelCalendar.accountname when 'Leads' then vtiger_leaddetailsRelCalendar.lastname when 'Potentials' then vtiger_potentialRelCalendar.potentialname when 'Quotes' then vtiger_quotesRelCalendar.subject when 'PurchaseOrder' then vtiger_purchaseorderRelCalendar.subject when 'Invoice' then vtiger_invoiceRelCalendar.subject End"." '".$selectedfields[2]."', vtiger_crmentityRelCalendar.setype 'Entity_type'"; 1233 } 1234 } 1235 return $querycolumn; 1236 } 1237 function getaccesfield($module) 1238 { 1239 global $current_user; 1240 global $adb; 1241 $access_fields = Array(); 1242 1243 $profileList = getCurrentUserProfileList(); 1244 $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"; 1245 $params = array(); 1246 if($module == "Calendar") 1247 { 1248 $query .= " vtiger_field.tabid in (9,16) and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.presence in (0,2)"; 1249 if (count($profileList) > 0) { 1250 $query .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")"; 1251 array_push($params, $profileList); 1252 } 1253 $query .= " group by vtiger_field.fieldid order by block,sequence"; 1254 } 1255 else 1256 { 1257 array_push($params, $this->primodule, $this->secmodule); 1258 $query .= " vtiger_field.tabid in (select tabid from vtiger_tab where vtiger_tab.name in (?,?)) and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.presence in (0,2)"; 1259 if (count($profileList) > 0) { 1260 $query .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")"; 1261 array_push($params, $profileList); 1262 } 1263 $query .= " group by vtiger_field.fieldid order by block,sequence"; 1264 } 1265 $result = $adb->pquery($query, $params); 1266 1267 1268 while($collistrow = $adb->fetch_array($result)) 1269 { 1270 $access_fields[] = $collistrow["fieldname"]; 1271 } 1272 return $access_fields; 1273 } 1274 1275 /** Function to set the order of grouping and to find the columns responsible 1276 * to the grouping 1277 * This function accepts the vtiger_reportid as variable,sets the variable ascdescorder[] to the sort order and 1278 * returns the array array_list which has the column responsible for the grouping 1279 * Array array_list[0]=columnname 1280 */ 1281 1282 1283 function getSelctedSortingColumns($reportid) 1284 { 1285 1286 global $adb; 1287 global $log; 1288 1289 $sreportsortsql = "select vtiger_reportsortcol.* from vtiger_report"; 1290 $sreportsortsql .= " inner join vtiger_reportsortcol on vtiger_report.reportid = vtiger_reportsortcol.reportid"; 1291 $sreportsortsql .= " where vtiger_report.reportid =? order by vtiger_reportsortcol.sortcolid"; 1292 1293 $result = $adb->pquery($sreportsortsql, array($reportid)); 1294 $noofrows = $adb->num_rows($result); 1295 1296 for($i=0; $i<$noofrows; $i++) 1297 { 1298 $fieldcolname = $adb->query_result($result,$i,"columnname"); 1299 $sort_values = $adb->query_result($result,$i,"sortorder"); 1300 $this->ascdescorder[] = $sort_values; 1301 $array_list[] = $fieldcolname; 1302 } 1303 1304 $log->info("Reports :: Successfully returned getSelctedSortingColumns"); 1305 return $array_list; 1306 } 1307 1308 /** Function to get the selected columns list for a selected vtiger_report 1309 * This function accepts the vtiger_reportid as the argument and get the selected columns 1310 * for the given vtiger_reportid and it forms a combo lists and returns 1311 * HTML of the combo values 1312 */ 1313 1314 function getSelectedColumnsList($reportid) 1315 { 1316 global $adb; 1317 global $modules; 1318 global $log,$current_user; 1319 1320 $ssql = "select vtiger_selectcolumn.* from vtiger_report inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid"; 1321 $ssql .= " left join vtiger_selectcolumn on vtiger_selectcolumn.queryid = vtiger_selectquery.queryid"; 1322 $ssql .= " where vtiger_report.reportid = ?"; 1323 $ssql .= " order by vtiger_selectcolumn.columnindex"; 1324 $result = $adb->pquery($ssql, array($reportid)); 1325 $permitted_fields = Array(); 1326 1327 $selected_mod = split(":",$this->secmodule); 1328 array_push($selected_mod,$this->primodule); 1329 1330 $inventoryModules = getInventoryModules(); 1331 while($columnslistrow = $adb->fetch_array($result)) 1332 { 1333 $fieldname =""; 1334 $fieldcolname = $columnslistrow["columnname"]; 1335 1336 $selmod_field_disabled = true; 1337 foreach($selected_mod as $smod){ 1338 if((stripos($fieldcolname,":".$smod."_")>-1) && vtlib_isModuleActive($smod)){ 1339 $selmod_field_disabled = false; 1340 break; 1341 } 1342 } 1343 if($selmod_field_disabled==false){ 1344 list($tablename,$colname,$module_field,$fieldname,$single) = split(":",$fieldcolname); 1345 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 1346 list($module,$field) = split("_",$module_field); 1347 if(sizeof($permitted_fields) == 0 && $is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) 1348 { 1349 $permitted_fields = $this->getaccesfield($module); 1350 } 1351 $querycolumns = $this->getEscapedColumns($selectedfields); 1352 $fieldlabel = trim(str_replace($module," ",$module_field)); 1353 $mod_arr=explode('_',$fieldlabel); 1354 $mod = ($mod_arr[0] == '')?$module:$mod_arr[0]; 1355 $fieldlabel = trim(str_replace("_"," ",$fieldlabel)); 1356 //modified code to support i18n issue 1357 $mod_lbl = getTranslatedString($mod,$module); //module 1358 $fld_lbl = getTranslatedString($fieldlabel,$module); //fieldlabel 1359 $fieldlabel = $mod_lbl." ".$fld_lbl; 1360 if (in_array($mod, $inventoryModules) && $fieldname == 'serviceid') { 1361 $shtml .= "<option permission='yes' value=\"".$fieldcolname."\">".$fieldlabel."</option>"; 1362 } 1363 else if(CheckFieldPermission($fieldname,$mod) != 'true' && $colname!="crmid") 1364 { 1365 $shtml .= "<option permission='no' value=\"".$fieldcolname."\" disabled = 'true'>".$fieldlabel."</option>"; 1366 } 1367 else 1368 { 1369 $shtml .= "<option permission='yes' value=\"".$fieldcolname."\">".$fieldlabel."</option>"; 1370 } 1371 } 1372 //end 1373 } 1374 $log->info("ReportRun :: Successfully returned getQueryColumnsList".$reportid); 1375 return $shtml; 1376 } 1377 function getAdvancedFilterList($reportid) 1378 { 1379 global $adb; 1380 global $modules; 1381 global $log; 1382 global $current_user; 1383 1384 $advft_criteria = array(); 1385 1386 $sql = 'SELECT * FROM vtiger_relcriteria_grouping WHERE queryid = ? ORDER BY groupid'; 1387 $groupsresult = $adb->pquery($sql, array($reportid)); 1388 1389 $i = 1; 1390 $j = 0; 1391 while($relcriteriagroup = $adb->fetch_array($groupsresult)) { 1392 $groupId = $relcriteriagroup["groupid"]; 1393 $groupCondition = $relcriteriagroup["group_condition"]; 1394 1395 $ssql = 'select vtiger_relcriteria.* from vtiger_report 1396 inner join vtiger_relcriteria on vtiger_relcriteria.queryid = vtiger_report.queryid 1397 left join vtiger_relcriteria_grouping on vtiger_relcriteria.queryid = vtiger_relcriteria_grouping.queryid 1398 and vtiger_relcriteria.groupid = vtiger_relcriteria_grouping.groupid'; 1399 $ssql.= " where vtiger_report.reportid = ? AND vtiger_relcriteria.groupid = ? order by vtiger_relcriteria.columnindex"; 1400 1401 $result = $adb->pquery($ssql, array($reportid, $groupId)); 1402 $noOfColumns = $adb->num_rows($result); 1403 if($noOfColumns <= 0) continue; 1404 1405 while($relcriteriarow = $adb->fetch_array($result)) { 1406 $columnIndex = $relcriteriarow["columnindex"]; 1407 $criteria = array(); 1408 $criteria['columnname'] = $relcriteriarow["columnname"]; 1409 $criteria['comparator'] = $relcriteriarow["comparator"]; 1410 $advfilterval = $relcriteriarow["value"]; 1411 $col = explode(":",$relcriteriarow["columnname"]); 1412 1413 $moduleFieldLabel = $col[2]; 1414 $fieldName = $col[3]; 1415 1416 list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2); 1417 $fieldInfo = getFieldByReportLabel($module, $fieldLabel); 1418 $fieldType = null; 1419 if(!empty($fieldInfo)) { 1420 $field = WebserviceField::fromArray($adb, $fieldInfo); 1421 $fieldType = $field->getFieldDataType(); 1422 } 1423 if($fieldType == 'currency') { 1424 if($field->getUIType() == '71') { 1425 $advfilterval = CurrencyField::convertToUserFormat($advfilterval,$current_user); 1426 } else if($field->getUIType() == '72') { 1427 $advfilterval = CurrencyField::convertToUserFormat($advfilterval,$current_user,true); 1428 } 1429 } 1430 1431 $temp_val = explode(",",$relcriteriarow["value"]); 1432 if($col[4] == 'D' || ($col[4] == 'T' && $col[1] != 'time_start' && $col[1] != 'time_end') || ($col[4] == 'DT')) { 1433 $val = Array(); 1434 for($x=0;$x<count($temp_val);$x++) { 1435 if($col[4] == 'D') { 1436 $date = new DateTimeField(trim($temp_val[$x])); 1437 $val[$x] = $date->getDisplayDate(); 1438 } elseif($col[4] == 'DT') { 1439 $date = new DateTimeField(trim($temp_val[$x])); 1440 $val[$x] = $date->getDisplayDateTimeValue(); 1441 } else { 1442 $date = new DateTimeField(trim($temp_val[$x])); 1443 $val[$x] = $date->getDisplayTime(); 1444 } 1445 } 1446 $advfilterval = implode(",",$val); 1447 } 1448 1449 //In vtiger6 report filter conditions, if the value has "(double quotes) then it is failed. 1450 $criteria['value'] = Vtiger_Util_Helper::toSafeHTML(decode_html($advfilterval)); 1451 $criteria['column_condition'] = $relcriteriarow["column_condition"]; 1452 1453 $advft_criteria[$relcriteriarow['groupid']]['columns'][$j] = $criteria; 1454 $advft_criteria[$relcriteriarow['groupid']]['condition'] = $groupCondition; 1455 $j++; 1456 } 1457 $i++; 1458 } 1459 // Clear the condition (and/or) for last group, if any. 1460 if(!empty($advft_criteria[$i-1]['condition'])) $advft_criteria[$i-1]['condition'] = ''; 1461 $this->advft_criteria = $advft_criteria; 1462 $log->info("Reports :: Successfully returned getAdvancedFilterList"); 1463 return true; 1464 } 1465 //<<<<<<<<advanced filter>>>>>>>>>>>>>> 1466 1467 /** Function to get the list of vtiger_report folders when Save and run the vtiger_report 1468 * This function gets the vtiger_report folders from database and form 1469 * a combo values of the folders and return 1470 * HTML of the combo values 1471 */ 1472 1473 function sgetRptFldrSaveReport() 1474 { 1475 global $adb; 1476 global $log; 1477 1478 $sql = "select * from vtiger_reportfolder order by folderid"; 1479 $result = $adb->pquery($sql, array()); 1480 $reportfldrow = $adb->fetch_array($result); 1481 $x = 0; 1482 do 1483 { 1484 $shtml .= "<option value='".$reportfldrow['folderid']."'>".$reportfldrow['foldername']."</option>"; 1485 }while($reportfldrow = $adb->fetch_array($result)); 1486 1487 $log->info("Reports :: Successfully returned sgetRptFldrSaveReport"); 1488 return $shtml; 1489 } 1490 1491 /** Function to get the column to total vtiger_fields in Reports 1492 * This function gets columns to total vtiger_field 1493 * and generated the html for that vtiger_fields 1494 * It returns the HTML of the vtiger_fields along with the check boxes 1495 */ 1496 1497 function sgetColumntoTotal($primarymodule,$secondarymodule) 1498 { 1499 $options = Array(); 1500 $options []= $this->sgetColumnstoTotalHTML($primarymodule,0); 1501 if(!empty($secondarymodule)) 1502 { 1503 //$secondarymodule = explode(":",$secondarymodule); 1504 for($i=0;$i < count($secondarymodule) ;$i++) 1505 { 1506 $options []= $this->sgetColumnstoTotalHTML($secondarymodule[$i],($i+1)); 1507 } 1508 } 1509 return $options; 1510 } 1511 1512 /** Function to get the selected columns of total vtiger_fields in Reports 1513 * This function gets selected columns of total vtiger_field 1514 * and generated the html for that vtiger_fields 1515 * It returns the HTML of the vtiger_fields along with the check boxes 1516 */ 1517 1518 1519 function sgetColumntoTotalSelected($primarymodule,$secondarymodule,$reportid) 1520 { 1521 global $adb; 1522 global $log; 1523 $options = Array(); 1524 if($reportid != "") 1525 { 1526 $ssql = "select vtiger_reportsummary.* from vtiger_reportsummary inner join vtiger_report on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid where vtiger_report.reportid=?"; 1527 $result = $adb->pquery($ssql, array($reportid)); 1528 if($result) 1529 { 1530 $reportsummaryrow = $adb->fetch_array($result); 1531 1532 do 1533 { 1534 $this->columnssummary[] = $reportsummaryrow["columnname"]; 1535 1536 }while($reportsummaryrow = $adb->fetch_array($result)); 1537 } 1538 } 1539 $options []= $this->sgetColumnstoTotalHTML($primarymodule,0); 1540 if($secondarymodule != "") 1541 { 1542 $secondarymodule = explode(":",$secondarymodule); 1543 for($i=0;$i < count($secondarymodule) ;$i++) 1544 { 1545 $options []= $this->sgetColumnstoTotalHTML($secondarymodule[$i],($i+1)); 1546 } 1547 } 1548 1549 $log->info("Reports :: Successfully returned sgetColumntoTotalSelected"); 1550 return $options; 1551 1552 } 1553 1554 /** Function to form the HTML for columns to total 1555 * This function formulates the HTML format of the 1556 * vtiger_fields along with four checkboxes 1557 * It returns the HTML of the vtiger_fields along with the check boxes 1558 */ 1559 1560 1561 function sgetColumnstoTotalHTML($module) 1562 { 1563 //retreive the vtiger_tabid 1564 global $adb; 1565 global $log; 1566 global $current_user; 1567 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 1568 $tabid = getTabid($module); 1569 $escapedchars = Array('_SUM','_AVG','_MIN','_MAX'); 1570 $sparams = array($tabid); 1571 if($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] ==0) 1572 { 1573 $ssql = "select * from vtiger_field inner join vtiger_tab on vtiger_tab.tabid = vtiger_field.tabid where vtiger_field.uitype != 50 and vtiger_field.tabid=? and vtiger_field.displaytype in (1,2,3) and vtiger_field.presence in (0,2) "; 1574 } 1575 else 1576 { 1577 $profileList = getCurrentUserProfileList(); 1578 $ssql = "select * from vtiger_field inner join vtiger_tab on vtiger_tab.tabid = vtiger_field.tabid inner join vtiger_def_org_field on vtiger_def_org_field.fieldid=vtiger_field.fieldid inner join vtiger_profile2field on vtiger_profile2field.fieldid=vtiger_field.fieldid where vtiger_field.uitype != 50 and vtiger_field.tabid=? and vtiger_field.displaytype in (1,2,3) and vtiger_def_org_field.visible=0 and vtiger_profile2field.visible=0 and vtiger_field.presence in (0,2)"; 1579 if (count($profileList) > 0) { 1580 $ssql .= " and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .")"; 1581 array_push($sparams, $profileList); 1582 } 1583 } 1584 1585 //Added to avoid display the Related fields (Account name,Vandor name,product name, etc) in Report Calculations(SUM,AVG..) 1586 switch($tabid) 1587 { 1588 case 2://Potentials 1589 //ie. Campaign name will not displayed in Potential's report calcullation 1590 $ssql.= " and vtiger_field.fieldname not in ('campaignid')"; 1591 break; 1592 case 4://Contacts 1593 $ssql.= " and vtiger_field.fieldname not in ('account_id')"; 1594 break; 1595 case 6://Accounts 1596 $ssql.= " and vtiger_field.fieldname not in ('account_id')"; 1597 break; 1598 case 9://Calandar 1599 $ssql.= " and vtiger_field.fieldname not in ('parent_id','contact_id')"; 1600 break; 1601 case 13://Trouble tickets(HelpDesk) 1602 $ssql.= " and vtiger_field.fieldname not in ('parent_id','product_id')"; 1603 break; 1604 case 14://Products 1605 $ssql.= " and vtiger_field.fieldname not in ('vendor_id','product_id')"; 1606 break; 1607 case 20://Quotes 1608 $ssql.= " and vtiger_field.fieldname not in ('potential_id','assigned_user_id1','account_id','currency_id')"; 1609 break; 1610 case 21://Purchase Order 1611 $ssql.= " and vtiger_field.fieldname not in ('contact_id','vendor_id','currency_id')"; 1612 break; 1613 case 22://SalesOrder 1614 $ssql.= " and vtiger_field.fieldname not in ('potential_id','account_id','contact_id','quote_id','currency_id')"; 1615 break; 1616 case 23://Invoice 1617 $ssql.= " and vtiger_field.fieldname not in ('salesorder_id','contact_id','account_id','currency_id')"; 1618 break; 1619 case 26://Campaigns 1620 $ssql.= " and vtiger_field.fieldname not in ('product_id')"; 1621 break; 1622 1623 } 1624 1625 $ssql.= " order by sequence"; 1626 1627 $result = $adb->pquery($ssql, $sparams); 1628 $columntototalrow = $adb->fetch_array($result); 1629 $options_list = Array(); 1630 do 1631 { 1632 $typeofdata = explode("~",$columntototalrow["typeofdata"]); 1633 1634 if($typeofdata[0] == "N" || $typeofdata[0] == "I" || ($typeofdata[0] == "NN" && !empty($typeofdata[2]))) 1635 { 1636 $options = Array(); 1637 if(isset($this->columnssummary)) 1638 { 1639 $selectedcolumn = ""; 1640 $selectedcolumn1 = ""; 1641 1642 for($i=0;$i < count($this->columnssummary) ;$i++) 1643 { 1644 $selectedcolumnarray = explode(":",$this->columnssummary[$i]); 1645 $selectedcolumn = $selectedcolumnarray[1].":".$selectedcolumnarray[2].":". 1646 str_replace($escapedchars,"",$selectedcolumnarray[3]); 1647 1648 if ($selectedcolumn != $columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.str_replace(" ","_",$columntototalrow['fieldlabel'])) 1649 { 1650 $selectedcolumn = ""; 1651 }else 1652 { 1653 $selectedcolumn1[$selectedcolumnarray[4]] = $this->columnssummary[$i]; 1654 } 1655 1656 } 1657 if(isset($_REQUEST["record"]) && $_REQUEST["record"] != '') 1658 { 1659 $options['label'][] = getTranslatedString($columntototalrow['tablabel'],$columntototalrow['tablabel']).' -'.getTranslatedString($columntototalrow['fieldlabel'],$columntototalrow['tablabel']); 1660 } 1661 1662 $columntototalrow['fieldlabel'] = str_replace(" ","_",$columntototalrow['fieldlabel']); 1663 $options []= getTranslatedString($columntototalrow['tablabel'],$columntototalrow['tablabel']).' - '.getTranslatedString($columntototalrow['fieldlabel'],$columntototalrow['tablabel']); 1664 if($selectedcolumn1[2] == "cb:".$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel']."_SUM:2") 1665 { 1666 $options []= '<input checked name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_SUM:2" type="checkbox" value="">'; 1667 }else 1668 { 1669 $options []= '<input name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_SUM:2" type="checkbox" value="">'; 1670 } 1671 if($selectedcolumn1[3] == "cb:".$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel']."_AVG:3") 1672 { 1673 $options []= '<input checked name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_AVG:3" type="checkbox" value="">'; 1674 }else 1675 { 1676 $options []= '<input name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_AVG:3" type="checkbox" value="">'; 1677 } 1678 1679 if($selectedcolumn1[4] == "cb:".$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel']."_MIN:4") 1680 { 1681 $options []= '<input checked name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_MIN:4" type="checkbox" value="">'; 1682 }else 1683 { 1684 $options []= '<input name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_MIN:4" type="checkbox" value="">'; 1685 } 1686 1687 if($selectedcolumn1[5] == "cb:".$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel']."_MAX:5") 1688 { 1689 $options []= '<input checked name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_MAX:5" type="checkbox" value="">'; 1690 }else 1691 { 1692 $options []= '<input name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_MAX:5" type="checkbox" value="">'; 1693 } 1694 }else 1695 { 1696 $options []= getTranslatedString($columntototalrow['tablabel'],$columntototalrow['tablabel']).' - '.getTranslatedString($columntototalrow['fieldlabel'],$columntototalrow['tablabel']); 1697 $options []= '<input name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_SUM:2" type="checkbox" value="">'; 1698 $options []= '<input name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_AVG:3" type="checkbox" value="" >'; 1699 $options []= '<input name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_MIN:4"type="checkbox" value="" >'; 1700 $options [] ='<input name="cb:'.$columntototalrow['tablename'].':'.$columntototalrow['columnname'].':'.$columntototalrow['fieldlabel'].'_MAX:5" type="checkbox" value="" >'; 1701 } 1702 $options_list [] = $options; 1703 } 1704 }while($columntototalrow = $adb->fetch_array($result)); 1705 1706 $log->info("Reports :: Successfully returned sgetColumnstoTotalHTML"); 1707 return $options_list; 1708 } 1709 1710 /** Function to get the advanced filter criteria for an option 1711 * This function accepts The option in the advenced filter as an argument 1712 * This generate filter criteria for the advanced filter 1713 * It returns a HTML string of combo values 1714 */ 1715 public static function getAdvCriteriaHTML($selected="") { 1716 global $adv_filter_options; 1717 1718 foreach($adv_filter_options as $key=>$value) { 1719 if($selected == $key) { 1720 $shtml .= "<option selected value=\"".$key."\">".$value."</option>"; 1721 } else { 1722 $shtml .= "<option value=\"".$key."\">".$value."</option>"; 1723 } 1724 } 1725 return $shtml; 1726 } 1727 } 1728 1729 /** Function to get the primary module list in vtiger_reports 1730 * This function generates the list of primary modules in vtiger_reports 1731 * and returns an array of permitted modules 1732 */ 1733 1734 function getReportsModuleList($focus) 1735 { 1736 global $adb; 1737 global $app_list_strings; 1738 //global $report_modules; 1739 global $mod_strings; 1740 $modules = Array(); 1741 foreach($focus->module_list as $key=>$value) { 1742 if(isPermitted($key,'index') == "yes") { 1743 $count_flag = 1; 1744 $modules [$key] = getTranslatedString($key,$key); 1745 } 1746 } 1747 asort($modules); 1748 return $modules; 1749 } 1750 /** Function to get the Related module list in vtiger_reports 1751 * This function generates the list of secondary modules in vtiger_reports 1752 * and returns the related module as an Array 1753 */ 1754 1755 function getReportRelatedModules($module,$focus) 1756 { 1757 global $app_list_strings; 1758 global $related_modules; 1759 global $mod_strings; 1760 $optionhtml = Array(); 1761 if(vtlib_isModuleActive($module)){ 1762 if(!empty($focus->related_modules[$module])) { 1763 foreach($focus->related_modules[$module] as $rel_modules) 1764 { 1765 if(isPermitted($rel_modules,'index') == "yes") 1766 { 1767 $optionhtml []= $rel_modules; 1768 } 1769 } 1770 } 1771 } 1772 1773 1774 return $optionhtml; 1775 } 1776 1777 function updateAdvancedCriteria($reportid, $advft_criteria, $advft_criteria_groups) { 1778 1779 global $adb, $log; 1780 1781 $idelrelcriteriasql = "delete from vtiger_relcriteria where queryid=?"; 1782 $idelrelcriteriasqlresult = $adb->pquery($idelrelcriteriasql, array($reportid)); 1783 1784 $idelrelcriteriagroupsql = "delete from vtiger_relcriteria_grouping where queryid=?"; 1785 $idelrelcriteriagroupsqlresult = $adb->pquery($idelrelcriteriagroupsql, array($reportid)); 1786 1787 if(empty($advft_criteria)) return; 1788 1789 foreach($advft_criteria as $column_index => $column_condition) { 1790 1791 if(empty($column_condition)) continue; 1792 1793 $adv_filter_column = $column_condition["columnname"]; 1794 $adv_filter_comparator = $column_condition["comparator"]; 1795 $adv_filter_value = $column_condition["value"]; 1796 $adv_filter_column_condition = $column_condition["columncondition"]; 1797 $adv_filter_groupid = $column_condition["groupid"]; 1798 1799 $column_info = explode(":",$adv_filter_column); 1800 $moduleFieldLabel = $column_info[2]; 1801 $fieldName = $column_info[3]; 1802 1803 list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2); 1804 $fieldInfo = getFieldByReportLabel($module, $fieldLabel); 1805 $fieldType = null; 1806 if(!empty($fieldInfo)) { 1807 $field = WebserviceField::fromArray($adb, $fieldInfo); 1808 $fieldType = $field->getFieldDataType(); 1809 } 1810 if($fieldType == 'currency') { 1811 // Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion 1812 if($field->getUIType() == '72') { 1813 $adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true); 1814 } else { 1815 $adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value); 1816 } 1817 } 1818 1819 $temp_val = explode(",",$adv_filter_value); 1820 if(($column_info[4] == 'D' || ($column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end') || ($column_info[4] == 'DT')) && ($column_info[4] != '' && $adv_filter_value != '' )) 1821 { 1822 $val = Array(); 1823 for($x=0;$x<count($temp_val);$x++) { 1824 if(trim($temp_val[$x]) != '') { 1825 $date = new DateTimeField(trim($temp_val[$x])); 1826 if($column_info[4] == 'D') { 1827 $val[$x] = DateTimeField::convertToUserFormat( 1828 trim($temp_val[$x])); 1829 } elseif($column_info[4] == 'DT') { 1830 $val[$x] = $date->getDBInsertDateTimeValue(); 1831 } else { 1832 $val[$x] = $date->getDBInsertTimeValue(); 1833 } 1834 } 1835 } 1836 $adv_filter_value = implode(",",$val); 1837 } 1838 1839 $irelcriteriasql = "insert into vtiger_relcriteria(QUERYID,COLUMNINDEX,COLUMNNAME,COMPARATOR,VALUE,GROUPID,COLUMN_CONDITION) values (?,?,?,?,?,?,?)"; 1840 $irelcriteriaresult = $adb->pquery($irelcriteriasql, array($reportid, $column_index, $adv_filter_column, $adv_filter_comparator, $adv_filter_value, $adv_filter_groupid, $adv_filter_column_condition)); 1841 1842 // Update the condition expression for the group to which the condition column belongs 1843 $groupConditionExpression = ''; 1844 if(!empty($advft_criteria_groups[$adv_filter_groupid]["conditionexpression"])) { 1845 $groupConditionExpression = $advft_criteria_groups[$adv_filter_groupid]["conditionexpression"]; 1846 } 1847 $groupConditionExpression = $groupConditionExpression .' '. $column_index .' '. $adv_filter_column_condition; 1848 $advft_criteria_groups[$adv_filter_groupid]["conditionexpression"] = $groupConditionExpression; 1849 } 1850 1851 foreach($advft_criteria_groups as $group_index => $group_condition_info) { 1852 1853 if(empty($group_condition_info)) continue; 1854 if(empty($group_condition_info["conditionexpression"])) continue; // Case when the group doesn't have any column criteria 1855 1856 $irelcriteriagroupsql = "insert into vtiger_relcriteria_grouping(GROUPID,QUERYID,GROUP_CONDITION,CONDITION_EXPRESSION) values (?,?,?,?)"; 1857 $irelcriteriagroupresult = $adb->pquery($irelcriteriagroupsql, array($group_index, $reportid, $group_condition_info["groupcondition"], $group_condition_info["conditionexpression"])); 1858 } 1859 } 1860 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:08:37 2014 | Cross-referenced by PHPXref 0.7.1 |