| [ 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 global $calpath; 11 global $app_strings,$mod_strings; 12 global $theme; 13 global $log; 14 15 $theme_path="themes/".$theme."/"; 16 $image_path=$theme_path."images/"; 17 require_once ('include/database/PearDatabase.php'); 18 require_once ('data/CRMEntity.php'); 19 require_once ("modules/Reports/Reports.php"); 20 require_once 'modules/Reports/ReportUtils.php'; 21 require_once ("vtlib/Vtiger/Module.php"); 22 require_once ('modules/Vtiger/helpers/Util.php'); 23 require_once ('include/RelatedListView.php'); 24 25 /* 26 * Helper class to determine the associative dependency between tables. 27 */ 28 class ReportRunQueryDependencyMatrix { 29 protected $matrix = array(); 30 protected $computedMatrix = null; 31 32 function setDependency($table, array $dependents) { 33 $this->matrix[$table] = $dependents; 34 } 35 36 function addDependency($table, $dependent) { 37 if (isset($this->matrix[$table]) && !in_array($dependent, $this->matrix[$table])) { 38 $this->matrix[$table][] = $dependent; 39 } else { 40 $this->setDependency($table, array($dependent)); 41 } 42 } 43 44 function getDependents($table) { 45 $this->computeDependencies(); 46 return isset($this->computedMatrix[$table])? $this->computedMatrix[$table] : array(); 47 } 48 49 protected function computeDependencies() { 50 if ($this->computedMatrix !== null) return; 51 52 $this->computedMatrix = array(); 53 foreach ($this->matrix as $key => $values) { 54 $this->computedMatrix[$key] = 55 $this->computeDependencyForKey($key, $values); 56 } 57 } 58 protected function computeDependencyForKey($key, $values) { 59 $merged = array(); 60 foreach ($values as $value) { 61 $merged[] = $value; 62 if (isset($this->matrix[$value])) { 63 $merged = array_merge($merged, $this->matrix[$value]); 64 } 65 } 66 return $merged; 67 } 68 } 69 70 class ReportRunQueryPlanner { 71 // Turn-off the query planning to revert back - backward compatiblity 72 protected $disablePlanner = false; 73 74 protected $tables = array(); 75 protected $tempTables = array(); 76 protected $tempTablesInitialized = false; 77 78 // Turn-off in case the query result turns-out to be wrong. 79 protected $allowTempTables = true; 80 protected $tempTablePrefix = 'vtiger_reptmptbl_'; 81 protected static $tempTableCounter = 0; 82 protected $registeredCleanup = false; 83 84 85 function addTable($table) { 86 if(!empty($table)) 87 $this->tables[$table] = $table; 88 } 89 90 function requireTable($table, $dependencies=null) { 91 92 if ($this->disablePlanner) { 93 return true; 94 } 95 96 if (isset($this->tables[$table])) { 97 return true; 98 } 99 if (is_array($dependencies)) { 100 foreach ($dependencies as $dependentTable) { 101 if (isset($this->tables[$dependentTable])) { 102 return true; 103 } 104 } 105 } else if ($dependencies instanceof ReportRunQueryDependencyMatrix) { 106 $dependents = $dependencies->getDependents($table); 107 if ($dependents) { 108 return count(array_intersect($this->tables, $dependents)) > 0; 109 } 110 } 111 return false; 112 } 113 114 function getTables() { 115 return $this->tables; 116 } 117 118 function newDependencyMatrix() { 119 return new ReportRunQueryDependencyMatrix(); 120 } 121 122 function registerTempTable($query, $keyColumns) { 123 if ($this->allowTempTables && !$this->disablePlanner) { 124 global $current_user; 125 126 $keyColumns = is_array($keyColumns)? array_unique($keyColumns) : array($keyColumns); 127 128 // Minor optimization to avoid re-creating similar temporary table. 129 $uniqueName = NULL; 130 foreach ($this->tempTables as $tmpUniqueName => $tmpTableInfo) { 131 if (strcasecmp($query, $tmpTableInfo['query']) === 0) { 132 // Capture any additional key columns 133 $tmpTableInfo['keycolumns'] = array_unique(array_merge($tmpTableInfo['keycolumns'], $keyColumns)); 134 $uniqueName = $tmpUniqueName; 135 break; 136 } 137 } 138 139 // Nothing found? 140 if ($uniqueName === NULL) { 141 // TODO Adding randomness in name to avoid concurrency 142 // even when same-user opens the report multiple instances at same-time. 143 $uniqueName = $this->tempTablePrefix . 144 str_replace('.', '', uniqid($current_user->id , true)) . (self::$tempTableCounter++); 145 146 $this->tempTables[$uniqueName] = array( 147 'query' => $query, 148 'keycolumns' => is_array($keyColumns)? array_unique($keyColumns) : array($keyColumns), 149 ); 150 } 151 152 return $uniqueName; 153 } 154 return "($query)"; 155 } 156 157 function initializeTempTables() { 158 global $adb; 159 160 $oldDieOnError = $adb->dieOnError; 161 $adb->dieOnError = false; // If query planner is re-used there could be attempt for temp table... 162 foreach ($this->tempTables as $uniqueName => $tempTableInfo) { 163 $query1 = sprintf('CREATE TEMPORARY TABLE %s AS %s', $uniqueName, $tempTableInfo['query']); 164 $adb->pquery($query1, array()); 165 166 $keyColumns = $tempTableInfo['keycolumns']; 167 foreach ($keyColumns as $keyColumn) { 168 $query2 = sprintf('ALTER TABLE %s ADD INDEX (%s)', $uniqueName, $keyColumn); 169 $adb->pquery($query2, array()); 170 } 171 } 172 173 $adb->dieOnError = $oldDieOnError; 174 175 // Trigger cleanup of temporary tables when the execution of the request ends. 176 // NOTE: This works better than having in __destruct 177 // (as the reference to this object might end pre-maturely even before query is executed) 178 if (!$this->registeredCleanup) { 179 register_shutdown_function(array($this, 'cleanup')); 180 // To avoid duplicate registration on this instance. 181 $this->registeredCleanup = true; 182 } 183 184 } 185 186 function cleanup() { 187 global $adb; 188 189 $oldDieOnError = $adb->dieOnError; 190 $adb->dieOnError = false; // To avoid abnormal termination during shutdown... 191 foreach ($this->tempTables as $uniqueName => $tempTableInfo) { 192 $adb->pquery('DROP TABLE ' . $uniqueName, array()); 193 } 194 $adb->dieOnError = $oldDieOnError; 195 196 $this->tempTables = array(); 197 } 198 } 199 200 class ReportRun extends CRMEntity 201 { 202 // Maximum rows that should be emitted in HTML view. 203 static $HTMLVIEW_MAX_ROWS = 1000; 204 205 var $reportid; 206 var $primarymodule; 207 var $secondarymodule; 208 var $orderbylistsql; 209 var $orderbylistcolumns; 210 211 var $selectcolumns; 212 var $groupbylist; 213 var $reporttype; 214 var $reportname; 215 var $totallist; 216 217 var $_groupinglist = false; 218 var $_columnslist = false; 219 var $_stdfilterlist = false; 220 var $_columnstotallist = false; 221 var $_advfiltersql = false; 222 223 // All UItype 72 fields are added here so that in reports the values are append currencyId::value 224 var $append_currency_symbol_to_value = array('Products_Unit_Price','Services_Price', 225 'Invoice_Total', 'Invoice_Sub_Total', 'Invoice_Pre_Tax_Total', 'Invoice_S&H_Amount', 'Invoice_Discount_Amount', 'Invoice_Adjustment', 226 'Quotes_Total', 'Quotes_Sub_Total', 'Quotes_Pre_Tax_Total', 'Quotes_S&H_Amount', 'Quotes_Discount_Amount', 'Quotes_Adjustment', 227 'SalesOrder_Total', 'SalesOrder_Sub_Total', 'SalesOrder_Pre_Tax_Total', 'SalesOrder_S&H_Amount', 'SalesOrder_Discount_Amount', 'SalesOrder_Adjustment', 228 'PurchaseOrder_Total', 'PurchaseOrder_Sub_Total', 'PurchaseOrder_Pre_Tax_Total', 'PurchaseOrder_S&H_Amount', 'PurchaseOrder_Discount_Amount', 'PurchaseOrder_Adjustment', 229 'Invoice_Received','PurchaseOrder_Paid','Invoice_Balance','PurchaseOrder_Balance' 230 ); 231 var $ui10_fields = array(); 232 var $ui101_fields = array(); 233 var $groupByTimeParent = array( 'Quarter'=>array('Year'), 234 'Month'=>array('Year') 235 ); 236 237 var $queryPlanner = null; 238 239 240 protected static $instances = false; 241 // Added to support line item fields calculation, if line item fields 242 // are selected then module fields cannot be selected and vice versa 243 var $lineItemFieldsInCalculation = false; 244 245 /** Function to set reportid,primarymodule,secondarymodule,reporttype,reportname, for given reportid 246 * This function accepts the $reportid as argument 247 * It sets reportid,primarymodule,secondarymodule,reporttype,reportname for the given reportid 248 * To ensure single-instance is present for $reportid 249 * as we optimize using ReportRunPlanner and setup temporary tables. 250 */ 251 252 function ReportRun($reportid) 253 { 254 $oReport = new Reports($reportid); 255 $this->reportid = $reportid; 256 $this->primarymodule = $oReport->primodule; 257 $this->secondarymodule = $oReport->secmodule; 258 $this->reporttype = $oReport->reporttype; 259 $this->reportname = $oReport->reportname; 260 $this->queryPlanner = new ReportRunQueryPlanner(); 261 } 262 263 public static function getInstance($reportid) { 264 if (!isset(self::$instances[$reportid])) { 265 self::$instances[$reportid] = new ReportRun($reportid); 266 } 267 return self::$instances[$reportid]; 268 } 269 270 /** Function to get the columns for the reportid 271 * This function accepts the $reportid and $outputformat (optional) 272 * This function returns $columnslist Array($tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname As Header value, 273 * $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 As Header value, 274 * | 275 * $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen As Header value 276 * ) 277 * 278 */ 279 function getQueryColumnsList($reportid,$outputformat='') 280 { 281 // Have we initialized information already? 282 if($this->_columnslist !== false) { 283 return $this->_columnslist; 284 } 285 286 global $adb; 287 global $modules; 288 global $log,$current_user,$current_language; 289 $ssql = "select vtiger_selectcolumn.* from vtiger_report inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid"; 290 $ssql .= " left join vtiger_selectcolumn on vtiger_selectcolumn.queryid = vtiger_selectquery.queryid"; 291 $ssql .= " where vtiger_report.reportid = ?"; 292 $ssql .= " order by vtiger_selectcolumn.columnindex"; 293 $result = $adb->pquery($ssql, array($reportid)); 294 $permitted_fields = Array(); 295 296 while($columnslistrow = $adb->fetch_array($result)) 297 { 298 $fieldname =""; 299 $fieldcolname = $columnslistrow["columnname"]; 300 list($tablename,$colname,$module_field,$fieldname,$single) = split(":",$fieldcolname); 301 list($module,$field) = split("_",$module_field,2); 302 $inventory_fields = array('serviceid'); 303 $inventory_modules = getInventoryModules(); 304 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 305 if(sizeof($permitted_fields[$module]) == 0 && $is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) 306 { 307 $permitted_fields[$module] = $this->getaccesfield($module); 308 } 309 if(in_array($module,$inventory_modules)){ 310 if (!empty ($permitted_fields)) { 311 foreach ($inventory_fields as $value) { 312 array_push($permitted_fields[$module], $value); 313 } 314 } 315 } 316 $selectedfields = explode(":",$fieldcolname); 317 if($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 318 && !in_array($selectedfields[3], $permitted_fields[$module])) { 319 //user has no access to this field, skip it. 320 continue; 321 } 322 $querycolumns = $this->getEscapedColumns($selectedfields); 323 if(isset($module) && $module!="") { 324 $mod_strings = return_module_language($current_language,$module); 325 } 326 327 $targetTableName = $tablename; 328 329 $fieldlabel = trim(preg_replace("/$module/"," ",$selectedfields[2],1)); 330 $mod_arr=explode('_',$fieldlabel); 331 $fieldlabel = trim(str_replace("_"," ",$fieldlabel)); 332 //modified code to support i18n issue 333 $fld_arr = explode(" ",$fieldlabel); 334 if(($mod_arr[0] == '')) { 335 $mod = $module; 336 $mod_lbl = getTranslatedString($module,$module); //module 337 } else { 338 $mod = $mod_arr[0]; 339 array_shift($fld_arr); 340 $mod_lbl = getTranslatedString($fld_arr[0],$mod); //module 341 } 342 $fld_lbl_str = implode(" ",$fld_arr); 343 $fld_lbl = getTranslatedString($fld_lbl_str,$module); //fieldlabel 344 $fieldlabel = $mod_lbl." ".$fld_lbl; 345 if(($selectedfields[0] == "vtiger_usersRel1") && ($selectedfields[1] == 'user_name') && ($selectedfields[2] == 'Quotes_Inventory_Manager')){ 346 $concatSql = getSqlForNameInDisplayFormat(array('first_name'=>$selectedfields[0].".first_name",'last_name'=>$selectedfields[0].".last_name"), 'Users'); 347 $columnslist[$fieldcolname] = "trim( $concatSql ) as ".$module."_Inventory_Manager"; 348 $this->queryPlanner->addTable($selectedfields[0]); 349 continue; 350 } 351 if((CheckFieldPermission($fieldname,$mod) != 'true' && $colname!="crmid" && (!in_array($fieldname,$inventory_fields) && in_array($module,$inventory_modules))) || empty($fieldname)) 352 { 353 continue; 354 } 355 else 356 { 357 $this->labelMapping[$selectedfields[2]] = str_replace(" ","_",$fieldlabel); 358 359 // To check if the field in the report is a custom field 360 // and if yes, get the label of this custom field freshly from the vtiger_field as it would have been changed. 361 // Asha - Reference ticket : #4906 362 363 if($querycolumns == "") { 364 $columnslist[$fieldcolname] = $this->getColumnSQL($selectedfields); 365 } else { 366 $columnslist[$fieldcolname] = $querycolumns; 367 } 368 369 $this->queryPlanner->addTable($targetTableName); 370 } 371 } 372 373 if ($outputformat == "HTML" || $outputformat == "PDF" || $outputformat == "PRINT") { 374 $columnslist['vtiger_crmentity:crmid:LBL_ACTION:crmid:I'] = 'vtiger_crmentity.crmid AS "'.$this->primarymodule.'_LBL_ACTION"' ; 375 } 376 377 // Save the information 378 $this->_columnslist = $columnslist; 379 380 $log->info("ReportRun :: Successfully returned getQueryColumnsList".$reportid); 381 return $columnslist; 382 } 383 384 385 function getColumnSQL($selectedfields) { 386 global $adb; 387 $header_label = $selectedfields[2]; // Header label to be displayed in the reports table 388 389 list($module,$field) = split("_",$selectedfields[2]); 390 $concatSql = getSqlForNameInDisplayFormat(array('first_name'=>$selectedfields[0].".first_name",'last_name'=>$selectedfields[0].".last_name"), 'Users'); 391 392 if ($selectedfields[4] == 'C') { 393 $field_label_data = split("_", $selectedfields[2]); 394 $module = $field_label_data[0]; 395 if ($module != $this->primarymodule) { 396 $columnSQL = "case when (" . $selectedfields[0] . "." . $selectedfields[1] . "='1')then 'yes' else case when (vtiger_crmentity$module.crmid !='') then 'no' else '-' end end AS '".decode_html($selectedfields[2])."'"; 397 $this->queryPlanner->addTable("vtiger_crmentity$module"); 398 } else { 399 $columnSQL = "case when (" . $selectedfields[0] . "." . $selectedfields[1] . "='1')then 'yes' else case when (vtiger_crmentity.crmid !='') then 'no' else '-' end end AS '".decode_html($selectedfields[2])."'"; 400 $this->queryPlanner->addTable($selectedfields[0]); 401 } 402 } elseif ($selectedfields[4] == 'D' || $selectedfields[4] == 'DT') { 403 if ($selectedfields[5] == 'Y') { 404 if ($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'date_start') { 405 if($module == 'Emails') { 406 $columnSQL = "YEAR(cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATE)) AS Emails_Date_Sent_Year"; 407 }else{ 408 $columnSQL = "YEAR(cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATETIME)) AS Calendar_Start_Date_and_Time_Year"; 409 } 410 } else if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) { 411 $columnSQL = "YEAR(vtiger_crmentity." . $selectedfields[1] . ") AS '" . decode_html($header_label) . "_Year'"; 412 } else { 413 $columnSQL = 'YEAR(' . $selectedfields[0] . "." . $selectedfields[1] . ") AS '" . decode_html($header_label) . "_Year'"; 414 } 415 $this->queryPlanner->addTable($selectedfields[0]); 416 } elseif ($selectedfields[5] == 'M') { 417 if ($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'date_start') { 418 if($module == 'Emails') { 419 $columnSQL = "MONTHNAME(cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATE)) AS Emails_Date_Sent_Month"; 420 }else{ 421 $columnSQL = "MONTHNAME(cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATETIME)) AS Calendar_Start_Date_and_Time_Month"; 422 } 423 } else if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) { 424 $columnSQL = "MONTHNAME(vtiger_crmentity." . $selectedfields[1] . ") AS '" . decode_html($header_label) . "_Month'"; 425 } else { 426 $columnSQL = 'MONTHNAME(' . $selectedfields[0] . "." . $selectedfields[1] . ") AS '" . decode_html($header_label) . "_Month'"; 427 } 428 $this->queryPlanner->addTable($selectedfields[0]); 429 } elseif ($selectedfields[5] == 'MY') { // used in charts to get the year also, which will be used for click throughs 430 if ($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'date_start') { 431 if($module == 'Emails') { 432 $columnSQL = "date_format(cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATE), '%M %Y') AS Emails_Date_Sent_Month"; 433 }else{ 434 $columnSQL = "date_format(cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATETIME), '%M %Y') AS Calendar_Start_Date_and_Time_Month"; 435 } 436 } else if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) { 437 $columnSQL = "date_format(vtiger_crmentity." . $selectedfields[1] . ", '%M %Y') AS '" . decode_html($header_label) . "_Month'"; 438 } else { 439 $columnSQL = 'date_format(' . $selectedfields[0] . "." . $selectedfields[1] . ", '%M %Y') AS '" . decode_html($header_label) . "_Month'"; 440 } 441 $this->queryPlanner->addTable($selectedfields[0]); 442 } else { 443 if ($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'date_start') { 444 if($module == 'Emails') { 445 $columnSQL = "cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATE) AS Emails_Date_Sent"; 446 }else{ 447 $columnSQL = "cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATETIME) AS Calendar_Start_Date_and_Time"; 448 } 449 } else if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) { 450 $columnSQL = "vtiger_crmentity." . $selectedfields[1] . " AS '" . decode_html($header_label) . "'"; 451 } else { 452 $columnSQL = $selectedfields[0] . "." . $selectedfields[1] . " AS '" . decode_html($header_label) . "'"; 453 } 454 $this->queryPlanner->addTable($selectedfields[0]); 455 } 456 } elseif ($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'status') { 457 $columnSQL = " case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end AS Calendar_Status"; 458 } elseif($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'date_start') { 459 if($module == 'Emails') { 460 $columnSQL = "cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATE) AS Emails_Date_Sent"; 461 } else { 462 $columnSQL = "cast(concat(vtiger_activity.date_start,' ',vtiger_activity.time_start) as DATETIME) AS Calendar_Start_Date_and_Time"; 463 } 464 } elseif(stristr($selectedfields[0],"vtiger_users") && ($selectedfields[1] == 'user_name')) { 465 $temp_module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]); 466 if($module != $this->primarymodule) { 467 $condition = "and vtiger_crmentity".$module.".crmid!=''"; 468 $this->queryPlanner->addTable("vtiger_crmentity$module"); 469 } else { 470 $condition = "and vtiger_crmentity.crmid!=''"; 471 } 472 if($temp_module_from_tablename == $module) { 473 $concatSql = getSqlForNameInDisplayFormat(array('first_name'=>$selectedfields[0].".first_name",'last_name'=>$selectedfields[0].".last_name"), 'Users'); 474 $columnSQL = " case when(".$selectedfields[0].".last_name NOT LIKE '' $condition ) THEN ".$concatSql." else vtiger_groups".$module.".groupname end AS '".decode_html($header_label)."'"; 475 $this->queryPlanner->addTable('vtiger_groups'.$module); // Auto-include the dependent module table. 476 } else {//Some Fields can't assigned to groups so case avoided (fields like inventory manager) 477 $columnSQL = $selectedfields[0].".user_name AS '".decode_html($header_label)."'"; 478 } 479 $this->queryPlanner->addTable($selectedfields[0]); 480 } elseif(stristr($selectedfields[0],"vtiger_crmentity") && ($selectedfields[1] == 'modifiedby')) { 481 $targetTableName = 'vtiger_lastModifiedBy'.$module; 482 $concatSql = getSqlForNameInDisplayFormat(array('last_name'=>$targetTableName.'.last_name', 'first_name'=>$targetTableName.'.first_name'), 'Users'); 483 $columnSQL = "trim($concatSql) AS $header_label"; 484 $this->queryPlanner->addTable("vtiger_crmentity$module"); 485 $this->queryPlanner->addTable($targetTableName); 486 487 // Added when no fields from the secondary module is selected but lastmodifiedby field is selected 488 $moduleInstance = CRMEntity::getInstance($module); 489 $this->queryPlanner->addTable($moduleInstance->table_name); 490 } else if(stristr($selectedfields[0],"vtiger_crmentity") && ($selectedfields[1] == 'smcreatorid')){ 491 $targetTableName = 'vtiger_createdby'.$module; 492 $concatSql = getSqlForNameInDisplayFormat(array('last_name'=>$targetTableName.'.last_name', 'first_name'=>$targetTableName.'.first_name'), 'Users'); 493 $columnSQL = "trim($concatSql) AS ".decode_html($header_label).""; 494 $this->queryPlanner->addTable("vtiger_crmentity$module"); 495 $this->queryPlanner->addTable($targetTableName); 496 497 // Added when no fields from the secondary module is selected but creator field is selected 498 $moduleInstance = CRMEntity::getInstance($module); 499 $this->queryPlanner->addTable($moduleInstance->table_name); 500 } elseif($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) { 501 $columnSQL = "vtiger_crmentity.".$selectedfields[1]." AS '".decode_html($header_label)."'"; 502 } elseif($selectedfields[0] == 'vtiger_products' && $selectedfields[1] == 'unit_price') { 503 $columnSQL = "concat(".$selectedfields[0].".currency_id,'::',innerProduct.actual_unit_price) AS '". decode_html($header_label) ."'"; 504 $this->queryPlanner->addTable("innerProduct"); 505 } elseif(in_array($selectedfields[2], $this->append_currency_symbol_to_value)) { 506 if($selectedfields[1] == 'discount_amount') { 507 $columnSQL = "CONCAT(".$selectedfields[0].".currency_id,'::', IF(".$selectedfields[0].".discount_amount != '',".$selectedfields[0].".discount_amount, (".$selectedfields[0].".discount_percent/100) * ".$selectedfields[0].".subtotal)) AS ".decode_html($header_label); 508 } else { 509 $columnSQL = "concat(".$selectedfields[0].".currency_id,'::',".$selectedfields[0].".".$selectedfields[1].") AS '" . decode_html($header_label) ."'"; 510 } 511 } elseif($selectedfields[0] == 'vtiger_notes' && ($selectedfields[1] == 'filelocationtype' || $selectedfields[1] == 'filesize' || $selectedfields[1] == 'folderid' || $selectedfields[1]=='filestatus')) { 512 if($selectedfields[1] == 'filelocationtype'){ 513 $columnSQL = "case ".$selectedfields[0].".".$selectedfields[1]." when 'I' then 'Internal' when 'E' then 'External' else '-' end AS '".decode_html($selectedfields[2])."'"; 514 } else if($selectedfields[1] == 'folderid'){ 515 $columnSQL = "vtiger_attachmentsfolder.foldername AS '$selectedfields[2]'"; 516 $this->queryPlanner->addTable("vtiger_attachmentsfolder"); 517 } elseif($selectedfields[1] == 'filestatus'){ 518 $columnSQL = "case ".$selectedfields[0].".".$selectedfields[1]." when '1' then 'yes' when '0' then 'no' else '-' end AS '". decode_html($selectedfields[2]) ."'"; 519 } elseif($selectedfields[1] == 'filesize'){ 520 $columnSQL = "case ".$selectedfields[0].".".$selectedfields[1]." when '' then '-' else concat(".$selectedfields[0].".".$selectedfields[1]."/1024,' ','KB') end AS '".decode_html($selectedfields[2])."'"; 521 } 522 } elseif($selectedfields[0] == 'vtiger_inventoryproductrel') { 523 if($selectedfields[1] == 'discount_amount'){ 524 $columnSQL = " 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 '" . decode_html($header_label) ."'"; 525 $this->queryPlanner->addTable($selectedfields[0].$module); 526 } else if($selectedfields[1] == 'productid'){ 527 $columnSQL = "vtiger_products{$module}.productname AS '" . decode_html($header_label) ."'"; 528 $this->queryPlanner->addTable("vtiger_products{$module}"); 529 } else if($selectedfields[1] == 'serviceid'){ 530 $columnSQL = "vtiger_service{$module}.servicename AS '" . decode_html($header_label) ."'"; 531 $this->queryPlanner->addTable("vtiger_service{$module}"); 532 } else if($selectedfields[1] == 'listprice') { 533 $moduleInstance = CRMEntity::getInstance($module); 534 $columnSQL = $selectedfields[0].$module.".".$selectedfields[1]."/".$moduleInstance->table_name.".conversion_rate AS '". decode_html($header_label) ."'"; 535 $this->queryPlanner->addTable($selectedfields[0].$module); 536 } else { 537 $columnSQL = $selectedfields[0].$module.".".$selectedfields[1]." AS '". decode_html($header_label) ."'"; 538 $this->queryPlanner->addTable($selectedfields[0].$module); 539 } 540 } else { 541 $columnSQL = $selectedfields[0].".".$selectedfields[1]." AS '". decode_html($header_label) ."'"; 542 $this->queryPlanner->addTable($selectedfields[0]); 543 } 544 return $columnSQL; 545 } 546 547 548 /** Function to get field columns based on profile 549 * @ param $module : Type string 550 * returns permitted fields in array format 551 */ 552 function getaccesfield($module) { 553 global $current_user; 554 global $adb; 555 $access_fields = Array(); 556 557 $profileList = getCurrentUserProfileList(); 558 $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"; 559 $params = array(); 560 if($module == "Calendar") 561 { 562 if (count($profileList) > 0) { 563 $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 564 and vtiger_field.presence IN (0,2) and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .") group by vtiger_field.fieldid order by block,sequence"; 565 array_push($params, $profileList); 566 } else { 567 $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 568 and vtiger_field.presence IN (0,2) group by vtiger_field.fieldid order by block,sequence"; 569 } 570 } 571 else 572 { 573 array_push($params, $module); 574 if (count($profileList) > 0) { 575 $query .= " vtiger_field.tabid in (select tabid from vtiger_tab where vtiger_tab.name in (?)) and vtiger_field.displaytype in (1,2,3,5) and vtiger_profile2field.visible=0 576 and vtiger_field.presence IN (0,2) and vtiger_def_org_field.visible=0 and vtiger_profile2field.profileid in (". generateQuestionMarks($profileList) .") group by vtiger_field.fieldid order by block,sequence"; 577 array_push($params, $profileList); 578 } else { 579 $query .= " vtiger_field.tabid in (select tabid from vtiger_tab where vtiger_tab.name in (?)) and vtiger_field.displaytype in (1,2,3,5) and vtiger_profile2field.visible=0 580 and vtiger_field.presence IN (0,2) and vtiger_def_org_field.visible=0 group by vtiger_field.fieldid order by block,sequence"; 581 } 582 } 583 $result = $adb->pquery($query, $params); 584 585 while($collistrow = $adb->fetch_array($result)) 586 { 587 $access_fields[] = $collistrow["fieldname"]; 588 } 589 //added to include ticketid for Reports module in select columnlist for all users 590 if($module == "HelpDesk") 591 $access_fields[] = "ticketid"; 592 return $access_fields; 593 } 594 595 /** Function to get Escapedcolumns for the field in case of multiple parents 596 * @ param $selectedfields : Type Array 597 * returns the case query for the escaped columns 598 */ 599 function getEscapedColumns($selectedfields) { 600 601 $tableName = $selectedfields[0]; 602 $columnName = $selectedfields[1]; 603 $moduleFieldLabel = $selectedfields[2]; 604 $fieldName = $selectedfields[3]; 605 list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2); 606 $fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel); 607 608 if($moduleName == 'ModComments' && $fieldName == 'creator') { 609 $concatSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_usersModComments.first_name', 610 'last_name' => 'vtiger_usersModComments.last_name'), 'Users'); 611 $queryColumn = "trim(case when (vtiger_usersModComments.user_name not like '' and vtiger_crmentity.crmid!='') then $concatSql end) AS ModComments_Creator"; 612 $this->queryPlanner->addTable('vtiger_usersModComments'); 613 $this->queryPlanner->addTable("vtiger_usersModComments"); 614 } elseif(($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) 615 && $fieldInfo['uitype'] != '52' && $fieldInfo['uitype'] != '53') { 616 $fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo); 617 if(count($fieldSqlColumns) > 0) { 618 $queryColumn = "(CASE WHEN $tableName.$columnName NOT LIKE '' THEN (CASE"; 619 foreach($fieldSqlColumns as $columnSql) { 620 $queryColumn .= " WHEN $columnSql NOT LIKE '' THEN $columnSql"; 621 } 622 $queryColumn .= " ELSE '' END) ELSE '' END) AS $moduleFieldLabel"; 623 $this->queryPlanner->addTable($tableName); 624 } 625 } 626 return $queryColumn; 627 } 628 629 /** Function to get selectedcolumns for the given reportid 630 * @ param $reportid : Type Integer 631 * returns the query of columnlist for the selected columns 632 */ 633 function getSelectedColumnsList($reportid) 634 { 635 636 global $adb; 637 global $modules; 638 global $log; 639 640 $ssql = "select vtiger_selectcolumn.* from vtiger_report inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid"; 641 $ssql .= " left join vtiger_selectcolumn on vtiger_selectcolumn.queryid = vtiger_selectquery.queryid where vtiger_report.reportid = ? "; 642 $ssql .= " order by vtiger_selectcolumn.columnindex"; 643 644 $result = $adb->pquery($ssql, array($reportid)); 645 $noofrows = $adb->num_rows($result); 646 647 if ($this->orderbylistsql != "") 648 { 649 $sSQL .= $this->orderbylistsql.", "; 650 } 651 652 for($i=0; $i<$noofrows; $i++) 653 { 654 $fieldcolname = $adb->query_result($result,$i,"columnname"); 655 $ordercolumnsequal = true; 656 if($fieldcolname != "") 657 { 658 for($j=0;$j<count($this->orderbylistcolumns);$j++) 659 { 660 if($this->orderbylistcolumns[$j] == $fieldcolname) 661 { 662 $ordercolumnsequal = false; 663 break; 664 }else 665 { 666 $ordercolumnsequal = true; 667 } 668 } 669 if($ordercolumnsequal) 670 { 671 $selectedfields = explode(":",$fieldcolname); 672 if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) 673 $selectedfields[0] = "vtiger_crmentity"; 674 $sSQLList[] = $selectedfields[0].".".$selectedfields[1]." '".$selectedfields[2]."'"; 675 } 676 } 677 } 678 $sSQL .= implode(",",$sSQLList); 679 680 $log->info("ReportRun :: Successfully returned getSelectedColumnsList".$reportid); 681 return $sSQL; 682 } 683 684 /** Function to get advanced comparator in query form for the given Comparator and value 685 * @ param $comparator : Type String 686 * @ param $value : Type String 687 * returns the check query for the comparator 688 */ 689 function getAdvComparator($comparator,$value,$datatype="",$columnName='') 690 { 691 692 global $log,$adb,$default_charset,$ogReport; 693 $value=html_entity_decode(trim($value),ENT_QUOTES,$default_charset); 694 $value_len = strlen($value); 695 $is_field = false; 696 if($value_len > 1 && $value[0]=='$' && $value[$value_len-1]=='$'){ 697 $temp = str_replace('$','',$value); 698 $is_field = true; 699 } 700 if($datatype=='C'){ 701 $value = str_replace("yes","1",str_replace("no","0",$value)); 702 } 703 704 if($is_field==true){ 705 $value = $this->getFilterComparedField($temp); 706 } 707 if($comparator == "e") 708 { 709 if(trim($value) == "NULL") 710 { 711 $rtvalue = " is NULL"; 712 }elseif(trim($value) != "") 713 { 714 $rtvalue = " = ".$adb->quote($value); 715 }elseif(trim($value) == "" && $datatype == "V") 716 { 717 $rtvalue = " = ".$adb->quote($value); 718 }else 719 { 720 $rtvalue = " is NULL"; 721 } 722 } 723 if($comparator == "n") 724 { 725 if(trim($value) == "NULL") 726 { 727 $rtvalue = " is NOT NULL"; 728 }elseif(trim($value) != "") 729 { 730 if($columnName) 731 $rtvalue = " <> ".$adb->quote($value)." OR ".$columnName." IS NULL "; 732 else 733 $rtvalue = " <> ".$adb->quote($value); 734 }elseif(trim($value) == "" && $datatype == "V") 735 { 736 $rtvalue = " <> ".$adb->quote($value); 737 }else 738 { 739 $rtvalue = " is NOT NULL"; 740 } 741 } 742 if($comparator == "s") 743 { 744 $rtvalue = " like '". formatForSqlLike($value, 2,$is_field) ."'"; 745 } 746 if($comparator == "ew") 747 { 748 $rtvalue = " like '". formatForSqlLike($value, 1,$is_field) ."'"; 749 } 750 if($comparator == "c") 751 { 752 $rtvalue = " like '". formatForSqlLike($value,0,$is_field) ."'"; 753 } 754 if($comparator == "k") 755 { 756 $rtvalue = " not like '". formatForSqlLike($value,0,$is_field) ."'"; 757 } 758 if($comparator == "l") 759 { 760 $rtvalue = " < ".$adb->quote($value); 761 } 762 if($comparator == "g") 763 { 764 $rtvalue = " > ".$adb->quote($value); 765 } 766 if($comparator == "m") 767 { 768 $rtvalue = " <= ".$adb->quote($value); 769 } 770 if($comparator == "h") 771 { 772 $rtvalue = " >= ".$adb->quote($value); 773 } 774 if($comparator == "b") { 775 $rtvalue = " < ".$adb->quote($value); 776 } 777 if($comparator == "a") { 778 $rtvalue = " > ".$adb->quote($value); 779 } 780 if($is_field==true){ 781 $rtvalue = str_replace("'","",$rtvalue); 782 $rtvalue = str_replace("\\","",$rtvalue); 783 } 784 $log->info("ReportRun :: Successfully returned getAdvComparator"); 785 return $rtvalue; 786 } 787 788 /** Function to get field that is to be compared in query form for the given Comparator and field 789 * @ param $field : field 790 * returns the value for the comparator 791 */ 792 function getFilterComparedField($field){ 793 global $adb,$ogReport; 794 if(!empty ($this->secondarymodule)){ 795 $secModules = explode(':',$this->secondarymodule); 796 foreach ($secModules as $secModule){ 797 $secondary = CRMEntity::getInstance($secModule); 798 $this->queryPlanner->addTable($secondary->table_name); 799 } 800 } 801 $field = split('#',$field); 802 $module = $field[0]; 803 $fieldname = trim($field[1]); 804 $tabid = getTabId($module); 805 $field_query = $adb->pquery("SELECT tablename,columnname,typeofdata,fieldname,uitype FROM vtiger_field WHERE tabid = ? AND fieldname= ?",array($tabid,$fieldname)); 806 $fieldtablename = $adb->query_result($field_query,0,'tablename'); 807 $fieldcolname = $adb->query_result($field_query,0,'columnname'); 808 $typeofdata = $adb->query_result($field_query,0,'typeofdata'); 809 $fieldtypeofdata=ChangeTypeOfData_Filter($fieldtablename,$fieldcolname,$typeofdata[0]); 810 $uitype = $adb->query_result($field_query,0,'uitype'); 811 /*if($tr[0]==$ogReport->primodule) 812 $value = $adb->query_result($field_query,0,'tablename').".".$adb->query_result($field_query,0,'columnname'); 813 else 814 $value = $adb->query_result($field_query,0,'tablename').$tr[0].".".$adb->query_result($field_query,0,'columnname'); 815 */ 816 if($uitype == 68 || $uitype == 59) 817 { 818 $fieldtypeofdata = 'V'; 819 } 820 if($fieldtablename == "vtiger_crmentity" && $module != $this->primarymodule) 821 { 822 $fieldtablename = $fieldtablename.$module; 823 } 824 if($fieldname == "assigned_user_id") 825 { 826 $fieldtablename = "vtiger_users".$module; 827 $fieldcolname = "user_name"; 828 } 829 if($fieldtablename == "vtiger_crmentity" && $fieldname == "modifiedby") 830 { 831 $fieldtablename = "vtiger_lastModifiedBy".$module; 832 $fieldcolname = "user_name"; 833 } 834 if($fieldname == "assigned_user_id1") 835 { 836 $fieldtablename = "vtiger_usersRel1"; 837 $fieldcolname = "user_name"; 838 } 839 840 $value = $fieldtablename.".".$fieldcolname; 841 842 $this->queryPlanner->addTable($fieldtablename); 843 return $value; 844 } 845 /** Function to get the advanced filter columns for the reportid 846 * This function accepts the $reportid 847 * This function returns $columnslist Array($columnname => $tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname filtercriteria, 848 * $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria, 849 * | 850 * $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen filtercriteria 851 * ) 852 * 853 */ 854 function getAdvFilterList($reportid) { 855 global $adb, $log; 856 857 $advft_criteria = array(); 858 859 $sql = 'SELECT * FROM vtiger_relcriteria_grouping WHERE queryid = ? ORDER BY groupid'; 860 $groupsresult = $adb->pquery($sql, array($reportid)); 861 862 $i = 1; 863 $j = 0; 864 while($relcriteriagroup = $adb->fetch_array($groupsresult)) { 865 $groupId = $relcriteriagroup["groupid"]; 866 $groupCondition = $relcriteriagroup["group_condition"]; 867 868 $ssql = 'select vtiger_relcriteria.* from vtiger_report 869 inner join vtiger_relcriteria on vtiger_relcriteria.queryid = vtiger_report.queryid 870 left join vtiger_relcriteria_grouping on vtiger_relcriteria.queryid = vtiger_relcriteria_grouping.queryid 871 and vtiger_relcriteria.groupid = vtiger_relcriteria_grouping.groupid'; 872 $ssql.= " where vtiger_report.reportid = ? AND vtiger_relcriteria.groupid = ? order by vtiger_relcriteria.columnindex"; 873 874 $result = $adb->pquery($ssql, array($reportid, $groupId)); 875 $noOfColumns = $adb->num_rows($result); 876 if($noOfColumns <= 0) continue; 877 878 while($relcriteriarow = $adb->fetch_array($result)) { 879 $columnIndex = $relcriteriarow["columnindex"]; 880 $criteria = array(); 881 $criteria['columnname'] = html_entity_decode($relcriteriarow["columnname"]); 882 $criteria['comparator'] = $relcriteriarow["comparator"]; 883 $advfilterval = $relcriteriarow["value"]; 884 $col = explode(":",$relcriteriarow["columnname"]); 885 $criteria['value'] = $advfilterval; 886 $criteria['column_condition'] = $relcriteriarow["column_condition"]; 887 888 $advft_criteria[$i]['columns'][$j] = $criteria; 889 $advft_criteria[$i]['condition'] = $groupCondition; 890 $j++; 891 892 $this->queryPlanner->addTable($col[0]); 893 } 894 if(!empty($advft_criteria[$i]['columns'][$j-1]['column_condition'])) { 895 $advft_criteria[$i]['columns'][$j-1]['column_condition'] = ''; 896 } 897 $i++; 898 } 899 // Clear the condition (and/or) for last group, if any. 900 if(!empty($advft_criteria[$i-1]['condition'])) $advft_criteria[$i-1]['condition'] = ''; 901 return $advft_criteria; 902 } 903 904 function generateAdvFilterSql($advfilterlist) { 905 906 global $adb; 907 908 $advfiltersql = ""; 909 $customView = new CustomView(); 910 $dateSpecificConditions = $customView->getStdFilterConditions(); 911 912 foreach($advfilterlist as $groupindex => $groupinfo) { 913 $groupcondition = $groupinfo['condition']; 914 $groupcolumns = $groupinfo['columns']; 915 916 if(count($groupcolumns) > 0) { 917 918 $advfiltergroupsql = ""; 919 foreach($groupcolumns as $columnindex => $columninfo) { 920 $fieldcolname = $columninfo["columnname"]; 921 $comparator = $columninfo["comparator"]; 922 $value = $columninfo["value"]; 923 $columncondition = $columninfo["column_condition"]; 924 $advcolsql = array(); 925 926 if($fieldcolname != "" && $comparator != "") { 927 if(in_array($comparator, $dateSpecificConditions)) { 928 if($fieldcolname != 'none') { 929 $selectedFields = explode(':',$fieldcolname); 930 if($selectedFields[0] == 'vtiger_crmentity'.$this->primarymodule) { 931 $selectedFields[0] = 'vtiger_crmentity'; 932 } 933 934 if($comparator != 'custom') { 935 list($startDate, $endDate) = $this->getStandarFiltersStartAndEndDate($comparator); 936 } else { 937 list($startDateTime, $endDateTime) = explode(',', $value); 938 list($startDate, $startTime) = explode(' ', $startDateTime); 939 list($endDate, $endTime) = explode(' ', $endDateTime); 940 } 941 942 $type = $selectedFields[4]; 943 if($startDate != '0000-00-00' && $endDate != '0000-00-00' && $startDate != '' && $endDate != '') { 944 $startDateTime = new DateTimeField($startDate. ' ' .date('H:i:s')); 945 $userStartDate = $startDateTime->getDisplayDate(); 946 if($type == 'DT') { 947 $userStartDate = $userStartDate.' 00:00:00'; 948 } 949 $startDateTime = getValidDBInsertDateTimeValue($userStartDate); 950 951 $endDateTime = new DateTimeField($endDate. ' ' .date('H:i:s')); 952 $userEndDate = $endDateTime->getDisplayDate(); 953 if($type == 'DT') { 954 $userEndDate = $userEndDate.' 23:59:59'; 955 } 956 $endDateTime = getValidDBInsertDateTimeValue($userEndDate); 957 958 if ($selectedFields[1] == 'birthday') { 959 $tableColumnSql = 'DATE_FORMAT(' . $selectedFields[0] . '.' . $selectedFields[1] . ', "%m%d")'; 960 $startDateTime = "DATE_FORMAT('$startDateTime', '%m%d')"; 961 $endDateTime = "DATE_FORMAT('$endDateTime', '%m%d')"; 962 } else { 963 if($selectedFields[0] == 'vtiger_activity' && ($selectedFields[1] == 'date_start')) { 964 $tableColumnSql = 'CAST((CONCAT(date_start, " ", time_start)) AS DATETIME)'; 965 } else { 966 $tableColumnSql = $selectedFields[0]. '.' .$selectedFields[1]; 967 } 968 $startDateTime = "'$startDateTime'"; 969 $endDateTime = "'$endDateTime'"; 970 } 971 972 $advfiltergroupsql .= "$tableColumnSql BETWEEN $startDateTime AND $endDateTime"; 973 if(!empty($columncondition)) { 974 $advfiltergroupsql .= ' '.$columncondition.' '; 975 } 976 977 $this->queryPlanner->addTable($selectedFields[0]); 978 } 979 } 980 continue; 981 } 982 $selectedFields = explode(":",$fieldcolname); 983 $tempComparators = array('e','n','bw','a','b'); 984 if($selectedFields[4] == 'DT' && in_array($comparator, $tempComparators)){ 985 if($selectedFields[0] == 'vtiger_crmentity'.$this->primarymodule) { 986 $selectedFields[0] = 'vtiger_crmentity'; 987 } 988 989 if($selectedFields[0] == 'vtiger_activity' && ($selectedFields[1] == 'date_start')) { 990 $tableColumnSql = 'CAST((CONCAT(date_start, " ", time_start)) AS DATETIME)'; 991 } else { 992 $tableColumnSql = $selectedFields[0]. '.' .$selectedFields[1]; 993 } 994 995 if($value != null && $value != ''){ 996 if($comparator == 'e' || $comparator == 'n'){ 997 $dateTimeComponents = explode(' ', $value); 998 $dateTime = new DateTime($dateTimeComponents[0].' '.'00:00:00'); 999 $date1 = $dateTime->format('Y-m-d H:i:s'); 1000 $dateTime->modify("+1 days"); 1001 $date2 = $dateTime->format('Y-m-d H:i:s'); 1002 $tempDate = strtotime($date2)-1; 1003 $date2 = date('Y-m-d H:i:s', $tempDate); 1004 1005 $start = getValidDBInsertDateTimeValue($date1); 1006 $end = getValidDBInsertDateTimeValue($date2); 1007 $start = "'$start'"; 1008 $end = "'$end'"; 1009 if($comparator == 'e') 1010 $advfiltergroupsql .= "$tableColumnSql BETWEEN $start AND $end"; 1011 else 1012 $advfiltergroupsql .= "$tableColumnSql NOT BETWEEN $start AND $end"; 1013 1014 }else if($comparator == 'bw'){ 1015 $values = explode(',',$value); 1016 $startDateTime = explode(' ',$values[0]); 1017 $endDateTime = explode(' ',$values[1]); 1018 1019 $startDateTime = new DateTimeField($startDateTime[0]. ' ' .date('H:i:s')); 1020 $userStartDate = $startDateTime->getDisplayDate(); 1021 $userStartDate = $userStartDate.' 00:00:00'; 1022 $start = getValidDBInsertDateTimeValue($userStartDate); 1023 1024 $endDateTime = new DateTimeField($endDateTime[0]. ' ' .date('H:i:s')); 1025 $userEndDate = $endDateTime->getDisplayDate(); 1026 $userEndDate = $userEndDate.' 23:59:59'; 1027 $end = getValidDBInsertDateTimeValue($userEndDate); 1028 1029 $advfiltergroupsql .= "$tableColumnSql BETWEEN '$start' AND '$end'"; 1030 }else if($comparator == 'a' || $comparator == 'b'){ 1031 $value = explode(' ', $value); 1032 $dateTime = new DateTime($value[0]); 1033 if($comparator == 'a'){ 1034 $modifiedDate = $dateTime->modify('+1 days'); 1035 $nextday = $modifiedDate->format('Y-m-d H:i:s'); 1036 $temp = strtotime($nextday)-1; 1037 $date = date('Y-m-d H:i:s', $temp); 1038 $value = getValidDBInsertDateTimeValue($date); 1039 $advfiltergroupsql .= "$tableColumnSql > '$value'"; 1040 }else{ 1041 $prevday = $dateTime->format('Y-m-d H:i:s'); 1042 $temp = strtotime($prevday)-1; 1043 $date = date('Y-m-d H:i:s', $temp); 1044 $value = getValidDBInsertDateTimeValue($date); 1045 $advfiltergroupsql .= "$tableColumnSql < '$value'"; 1046 } 1047 } 1048 if(!empty($columncondition)) { 1049 $advfiltergroupsql .= ' '.$columncondition.' '; 1050 } 1051 $this->queryPlanner->addTable($selectedFields[0]); 1052 } 1053 continue; 1054 } 1055 $selectedfields = explode(":",$fieldcolname); 1056 $moduleFieldLabel = $selectedfields[2]; 1057 list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2); 1058 $fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel); 1059 $concatSql = getSqlForNameInDisplayFormat(array('first_name'=>$selectedfields[0].".first_name",'last_name'=>$selectedfields[0].".last_name"), 'Users'); 1060 // Added to handle the crmentity table name for Primary module 1061 if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) { 1062 $selectedfields[0] = "vtiger_crmentity"; 1063 } 1064 //Added to handle yes or no for checkbox field in reports advance filters. -shahul 1065 if($selectedfields[4] == 'C') { 1066 if(strcasecmp(trim($value),"yes")==0) 1067 $value="1"; 1068 if(strcasecmp(trim($value),"no")==0) 1069 $value="0"; 1070 } 1071 if(in_array($comparator,$dateSpecificConditions)) { 1072 $customView = new CustomView($moduleName); 1073 $columninfo['stdfilter'] = $columninfo['comparator']; 1074 $valueComponents = explode(',',$columninfo['value']); 1075 if($comparator == 'custom') { 1076 if($selectedfields[4] == 'DT') { 1077 $startDateTimeComponents = explode(' ',$valueComponents[0]); 1078 $endDateTimeComponents = explode(' ',$valueComponents[1]); 1079 $columninfo['startdate'] = DateTimeField::convertToDBFormat($startDateTimeComponents[0]); 1080 $columninfo['enddate'] = DateTimeField::convertToDBFormat($endDateTimeComponents[0]); 1081 } else { 1082 $columninfo['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]); 1083 $columninfo['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]); 1084 } 1085 } 1086 $dateFilterResolvedList = $customView->resolveDateFilterValue($columninfo); 1087 $startDate = DateTimeField::convertToDBFormat($dateFilterResolvedList['startdate']); 1088 $endDate = DateTimeField::convertToDBFormat($dateFilterResolvedList['enddate']); 1089 $columninfo['value'] = $value = implode(',', array($startDate,$endDate)); 1090 $comparator = 'bw'; 1091 } 1092 $valuearray = explode(",",trim($value)); 1093 $datatype = (isset($selectedfields[4])) ? $selectedfields[4] : ""; 1094 if(isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') { 1095 1096 $advcolumnsql = ""; 1097 for($n=0;$n<count($valuearray);$n++) { 1098 1099 if(($selectedfields[0] == "vtiger_users".$this->primarymodule || $selectedfields[0] == "vtiger_users".$this->secondarymodule) && $selectedfields[1] == 'user_name') { 1100 $module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]); 1101 $advcolsql[] = " (trim($concatSql)".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype)." or vtiger_groups".$module_from_tablename.".groupname ".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype).")"; 1102 $this->queryPlanner->addTable("vtiger_groups".$module_from_tablename); 1103 } elseif($selectedfields[1] == 'status') {//when you use comma seperated values. 1104 if($selectedfields[2] == 'Calendar_Status') { 1105 $advcolsql[] = "(case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end)".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 1106 } else if($selectedfields[2] == 'HelpDesk_Status') { 1107 $advcolsql[] = "vtiger_troubletickets.status".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 1108 } else if($selectedfields[2] == 'Faq_Status') { 1109 $advcolsql[] = "vtiger_faq.status".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 1110 } 1111 else 1112 $advcolsql[] = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 1113 } elseif($selectedfields[1] == 'description') {//when you use comma seperated values. 1114 if($selectedfields[0]=='vtiger_crmentity'.$this->primarymodule) 1115 $advcolsql[] = "vtiger_crmentity.description".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 1116 else 1117 $advcolsql[] = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 1118 } elseif($selectedfields[2] == 'Quotes_Inventory_Manager'){ 1119 $advcolsql[] = ("trim($concatSql)".$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype)); 1120 } elseif($selectedfields[1] == 'modifiedby'){ 1121 $module_from_tablename = str_replace("vtiger_crmentity","",$selectedfields[0]); 1122 if($module_from_tablename != '') { 1123 $tableName = 'vtiger_lastModifiedBy'.$module_from_tablename; 1124 } else { 1125 $tableName = 'vtiger_lastModifiedBy'.$this->primarymodule; 1126 } 1127 $advcolsql[] = 'trim('.getSqlForNameInDisplayFormat(array('last_name'=>"$tableName.last_name",'first_name'=>"$tableName.first_name"), 'Users').')'. 1128 $this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 1129 } 1130 else { 1131 $advcolsql[] = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 1132 } 1133 } 1134 //If negative logic filter ('not equal to', 'does not contain') is used, 'and' condition should be applied instead of 'or' 1135 if($comparator == 'n' || $comparator == 'k') 1136 $advcolumnsql = implode(" and ",$advcolsql); 1137 else 1138 $advcolumnsql = implode(" or ",$advcolsql); 1139 $fieldvalue = " (".$advcolumnsql.") "; 1140 } elseif($selectedfields[1] == 'user_name') { 1141 if($selectedfields[0] == "vtiger_users".$this->primarymodule) { 1142 $module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]); 1143 $fieldvalue = " trim(case when (".$selectedfields[0].".last_name NOT LIKE '') then ".$concatSql." else vtiger_groups".$module_from_tablename.".groupname end) ".$this->getAdvComparator($comparator,trim($value),$datatype); 1144 $this->queryPlanner->addTable("vtiger_groups".$module_from_tablename); 1145 } else { 1146 $secondaryModules = explode(':', $this->secondarymodule); 1147 $firstSecondaryModule = "vtiger_users".$secondaryModules[0]; 1148 $secondSecondaryModule = "vtiger_users".$secondaryModules[1]; 1149 if(($firstSecondaryModule && $firstSecondaryModule == $selectedfields[0]) || ($secondSecondaryModule && $secondSecondaryModule == $selectedfields[0])) { 1150 $module_from_tablename = str_replace("vtiger_users","",$selectedfields[0]); 1151 $moduleInstance = CRMEntity::getInstance($module_from_tablename); 1152 $fieldvalue = " trim(case when (".$selectedfields[0].".last_name NOT LIKE '') then ".$concatSql." else vtiger_groups".$module_from_tablename.".groupname end) ".$this->getAdvComparator($comparator,trim($value),$datatype); 1153 $this->queryPlanner->addTable("vtiger_groups".$module_from_tablename); 1154 $this->queryPlanner->addTable($moduleInstance->table_name); 1155 } 1156 } 1157 } elseif($comparator == 'bw' && count($valuearray) == 2) { 1158 if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) { 1159 $fieldvalue = "("."vtiger_crmentity.".$selectedfields[1]." between '".trim($valuearray[0])."' and '".trim($valuearray[1])."')"; 1160 } else { 1161 $fieldvalue = "(".$selectedfields[0].".".$selectedfields[1]." between '".trim($valuearray[0])."' and '".trim($valuearray[1])."')"; 1162 } 1163 } elseif($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) { 1164 $fieldvalue = "vtiger_crmentity.".$selectedfields[1]." ".$this->getAdvComparator($comparator,trim($value),$datatype); 1165 } elseif($selectedfields[2] == 'Quotes_Inventory_Manager'){ 1166 $fieldvalue = ("trim($concatSql)" . $this->getAdvComparator($comparator,trim($value),$datatype)); 1167 } elseif($selectedfields[1]=='modifiedby') { 1168 $module_from_tablename = str_replace("vtiger_crmentity","",$selectedfields[0]); 1169 if($module_from_tablename != '') { 1170 $tableName = 'vtiger_lastModifiedBy'.$module_from_tablename; 1171 } else { 1172 $tableName = 'vtiger_lastModifiedBy'.$this->primarymodule; 1173 } 1174 $this->queryPlanner->addTable($tableName); 1175 $fieldvalue = 'trim('.getSqlForNameInDisplayFormat(array('last_name'=>"$tableName.last_name",'first_name'=>"$tableName.first_name"), 'Users').')'. 1176 $this->getAdvComparator($comparator,trim($value),$datatype); 1177 } elseif($selectedfields[1]=='smcreatorid'){ 1178 $module_from_tablename = str_replace("vtiger_crmentity","",$selectedfields[0]); 1179 if($module_from_tablename != '') { 1180 $tableName = 'vtiger_createdby'.$module_from_tablename; 1181 } else { 1182 $tableName = 'vtiger_createdby'.$this->primarymodule; 1183 } 1184 if($moduleName == 'ModComments') { 1185 $tableName = 'vtiger_users'.$moduleName; 1186 } 1187 $this->queryPlanner->addTable($tableName); 1188 $fieldvalue = 'trim('.getSqlForNameInDisplayFormat(array('last_name'=>"$tableName.last_name",'first_name'=>"$tableName.first_name"), 'Users').')'. 1189 $this->getAdvComparator($comparator,trim($value),$datatype); 1190 } elseif($selectedfields[0] == "vtiger_activity" && ($selectedfields[1] == 'status' || $selectedfields[1] == 'eventstatus')) { 1191 // for "Is Empty" condition we need to check with "value NOT NULL" OR "value = ''" conditions 1192 if($comparator == 'y'){ 1193 $fieldvalue = "(case when (vtiger_activity.status not like '') then vtiger_activity.status 1194 else vtiger_activity.eventstatus end) IS NULL OR (case when (vtiger_activity.status not like '') 1195 then vtiger_activity.status else vtiger_activity.eventstatus end) = ''"; 1196 }else{ 1197 $fieldvalue = "(case when (vtiger_activity.status not like '') then vtiger_activity.status 1198 else vtiger_activity.eventstatus end)".$this->getAdvComparator($comparator,trim($value),$datatype); 1199 } 1200 }else if($comparator == 'ny'){ 1201 if($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) 1202 $fieldvalue = "(".$selectedfields[0].".".$selectedfields[1]." IS NOT NULL AND ".$selectedfields[0].".".$selectedfields[1]." != '' AND ".$selectedfields[0].".".$selectedfields[1]." != '0')"; 1203 else 1204 $fieldvalue = "(".$selectedfields[0].".".$selectedfields[1]." IS NOT NULL AND ".$selectedfields[0].".".$selectedfields[1]." != '')"; 1205 }elseif($comparator == 'y' || ($comparator == 'e' && (trim($value) == "NULL" || trim($value) == ''))) { 1206 if($selectedfields[0] == 'vtiger_inventoryproductrel') { 1207 $selectedfields[0]='vtiger_inventoryproductrel'.$moduleName; 1208 } 1209 if($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) 1210 $fieldvalue = "(".$selectedfields[0].".".$selectedfields[1]." IS NULL OR ".$selectedfields[0].".".$selectedfields[1]." = '' OR ".$selectedfields[0].".".$selectedfields[1]." = '0')"; 1211 else 1212 $fieldvalue = "(".$selectedfields[0].".".$selectedfields[1]." IS NULL OR ".$selectedfields[0].".".$selectedfields[1]." = '')"; 1213 } elseif($selectedfields[0] == 'vtiger_inventoryproductrel' ) { 1214 if($selectedfields[1] == 'productid'){ 1215 $fieldvalue = "vtiger_products$moduleName.productname ".$this->getAdvComparator($comparator,trim($value),$datatype); 1216 $this->queryPlanner->addTable("vtiger_products$moduleName"); 1217 } else if($selectedfields[1] == 'serviceid'){ 1218 $fieldvalue = "vtiger_service$moduleName.servicename ".$this->getAdvComparator($comparator,trim($value),$datatype); 1219 $this->queryPlanner->addTable("vtiger_service$moduleName"); 1220 } 1221 else{ 1222 //for inventory module table should be follwed by the module name 1223 $selectedfields[0]='vtiger_inventoryproductrel'.$moduleName; 1224 $fieldvalue = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator, $value, $datatype); 1225 } 1226 } elseif($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) { 1227 1228 $fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo); 1229 $comparatorValue = $this->getAdvComparator($comparator,trim($value),$datatype,$fieldSqlColumns[0]); 1230 $fieldSqls = array(); 1231 1232 foreach($fieldSqlColumns as $columnSql) { 1233 $fieldSqls[] = $columnSql.$comparatorValue; 1234 } 1235 $fieldvalue = ' ('. implode(' OR ', $fieldSqls).') '; 1236 } else { 1237 $fieldvalue = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($value),$datatype); 1238 } 1239 1240 $advfiltergroupsql .= $fieldvalue; 1241 if(!empty($columncondition)) { 1242 $advfiltergroupsql .= ' '.$columncondition.' '; 1243 } 1244 1245 $this->queryPlanner->addTable($selectedfields[0]); 1246 } 1247 1248 } 1249 1250 if (trim($advfiltergroupsql) != "") { 1251 $advfiltergroupsql = "( $advfiltergroupsql ) "; 1252 if(!empty($groupcondition)) { 1253 $advfiltergroupsql .= ' '. $groupcondition . ' '; 1254 } 1255 1256 $advfiltersql .= $advfiltergroupsql; 1257 } 1258 } 1259 } 1260 if (trim($advfiltersql) != "") $advfiltersql = '('.$advfiltersql.')'; 1261 1262 return $advfiltersql; 1263 } 1264 1265 function getAdvFilterSql($reportid) { 1266 // Have we initialized information already? 1267 if($this->_advfiltersql !== false) { 1268 return $this->_advfiltersql; 1269 } 1270 global $log; 1271 1272 $advfilterlist = $this->getAdvFilterList($reportid); 1273 $advfiltersql = $this->generateAdvFilterSql($advfilterlist); 1274 1275 // Save the information 1276 $this->_advfiltersql = $advfiltersql; 1277 1278 $log->info("ReportRun :: Successfully returned getAdvFilterSql".$reportid); 1279 return $advfiltersql; 1280 } 1281 1282 /** Function to get the Standard filter columns for the reportid 1283 * This function accepts the $reportid datatype Integer 1284 * This function returns $stdfilterlist Array($columnname => $tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname filtercriteria, 1285 * $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria, 1286 * ) 1287 * 1288 */ 1289 function getStdFilterList($reportid) 1290 { 1291 // Have we initialized information already? 1292 if($this->_stdfilterlist !== false) { 1293 return $this->_stdfilterlist; 1294 } 1295 1296 global $adb, $log; 1297 $stdfilterlist = array(); 1298 1299 $stdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report"; 1300 $stdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid"; 1301 $stdfiltersql .= " where vtiger_report.reportid = ?"; 1302 1303 $result = $adb->pquery($stdfiltersql, array($reportid)); 1304 $stdfilterrow = $adb->fetch_array($result); 1305 if(isset($stdfilterrow)) { 1306 $fieldcolname = $stdfilterrow["datecolumnname"]; 1307 $datefilter = $stdfilterrow["datefilter"]; 1308 $startdate = $stdfilterrow["startdate"]; 1309 $enddate = $stdfilterrow["enddate"]; 1310 1311 if($fieldcolname != "none") { 1312 $selectedfields = explode(":",$fieldcolname); 1313 if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) 1314 $selectedfields[0] = "vtiger_crmentity"; 1315 1316 $moduleFieldLabel = $selectedfields[3]; 1317 list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2); 1318 $fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel); 1319 $typeOfData = $fieldInfo['typeofdata']; 1320 list($type, $typeOtherInfo) = explode('~', $typeOfData, 2); 1321 1322 if($datefilter != "custom") { 1323 $startenddate = $this->getStandarFiltersStartAndEndDate($datefilter); 1324 $startdate = $startenddate[0]; 1325 $enddate = $startenddate[1]; 1326 } 1327 1328 if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $startdate != "" && $enddate != "" 1329 && $selectedfields[0] != "" && $selectedfields[1] != "") { 1330 1331 $startDateTime = new DateTimeField($startdate.' '. date('H:i:s')); 1332 $userStartDate = $startDateTime->getDisplayDate(); 1333 if($type == 'DT') { 1334 $userStartDate = $userStartDate.' 00:00:00'; 1335 } 1336 $startDateTime = getValidDBInsertDateTimeValue($userStartDate); 1337 1338 $endDateTime = new DateTimeField($enddate.' '. date('H:i:s')); 1339 $userEndDate = $endDateTime->getDisplayDate(); 1340 if($type == 'DT') { 1341 $userEndDate = $userEndDate.' 23:59:00'; 1342 } 1343 $endDateTime = getValidDBInsertDateTimeValue($userEndDate); 1344 1345 if ($selectedfields[1] == 'birthday') { 1346 $tableColumnSql = "DATE_FORMAT(".$selectedfields[0].".".$selectedfields[1].", '%m%d')"; 1347 $startDateTime = "DATE_FORMAT('$startDateTime', '%m%d')"; 1348 $endDateTime = "DATE_FORMAT('$endDateTime', '%m%d')"; 1349 } else { 1350 if($selectedfields[0] == 'vtiger_activity' && ($selectedfields[1] == 'date_start')) { 1351 $tableColumnSql = ''; 1352 $tableColumnSql = "CAST((CONCAT(date_start,' ',time_start)) AS DATETIME)"; 1353 } else { 1354 $tableColumnSql = $selectedfields[0].".".$selectedfields[1]; 1355 } 1356 $startDateTime = "'$startDateTime'"; 1357 $endDateTime = "'$endDateTime'"; 1358 } 1359 1360 $stdfilterlist[$fieldcolname] = $tableColumnSql." between ".$startDateTime." and ".$endDateTime; 1361 $this->queryPlanner->addTable($selectedfields[0]); 1362 } 1363 } 1364 } 1365 // Save the information 1366 $this->_stdfilterlist = $stdfilterlist; 1367 1368 $log->info("ReportRun :: Successfully returned getStdFilterList".$reportid); 1369 return $stdfilterlist; 1370 } 1371 1372 /** Function to get the RunTime filter columns for the given $filtercolumn,$filter,$startdate,$enddate 1373 * @ param $filtercolumn : Type String 1374 * @ param $filter : Type String 1375 * @ param $startdate: Type String 1376 * @ param $enddate : Type String 1377 * This function returns $stdfilterlist Array($columnname => $tablename:$columnname:$fieldlabel=>$tablename.$columnname 'between' $startdate 'and' $enddate) 1378 * 1379 */ 1380 function RunTimeFilter($filtercolumn,$filter,$startdate,$enddate) 1381 { 1382 if($filtercolumn != "none") 1383 { 1384 $selectedfields = explode(":",$filtercolumn); 1385 if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) 1386 $selectedfields[0] = "vtiger_crmentity"; 1387 if($filter == "custom") 1388 { 1389 if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $startdate != "" && 1390 $enddate != "" && $selectedfields[0] != "" && $selectedfields[1] != "") { 1391 $stdfilterlist[$filtercolumn] = $selectedfields[0].".".$selectedfields[1]." between '".$startdate." 00:00:00' and '".$enddate." 23:59:00'"; 1392 } 1393 }else 1394 { 1395 if($startdate != "" && $enddate != "") 1396 { 1397 $startenddate = $this->getStandarFiltersStartAndEndDate($filter); 1398 if($startenddate[0] != "" && $startenddate[1] != "" && $selectedfields[0] != "" && $selectedfields[1] != "") 1399 { 1400 $stdfilterlist[$filtercolumn] = $selectedfields[0].".".$selectedfields[1]." between '".$startenddate[0]." 00:00:00' and '".$startenddate[1]." 23:59:00'"; 1401 } 1402 } 1403 } 1404 1405 } 1406 return $stdfilterlist; 1407 1408 } 1409 1410 /** Function to get the RunTime Advanced filter conditions 1411 * @ param $advft_criteria : Type Array 1412 * @ param $advft_criteria_groups : Type Array 1413 * This function returns $advfiltersql 1414 * 1415 */ 1416 function RunTimeAdvFilter($advft_criteria,$advft_criteria_groups) { 1417 $adb = PearDatabase::getInstance(); 1418 1419 $advfilterlist = array(); 1420 $advfiltersql = ''; 1421 if(!empty($advft_criteria)) { 1422 foreach($advft_criteria as $column_index => $column_condition) { 1423 1424 if(empty($column_condition)) continue; 1425 1426 $adv_filter_column = $column_condition["columnname"]; 1427 $adv_filter_comparator = $column_condition["comparator"]; 1428 $adv_filter_value = $column_condition["value"]; 1429 $adv_filter_column_condition = $column_condition["columncondition"]; 1430 $adv_filter_groupid = $column_condition["groupid"]; 1431 1432 $column_info = explode(":",$adv_filter_column); 1433 1434 $moduleFieldLabel = $column_info[2]; 1435 $fieldName = $column_info[3]; 1436 list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2); 1437 $fieldInfo = getFieldByReportLabel($module, $fieldLabel); 1438 $fieldType = null; 1439 if(!empty($fieldInfo)) { 1440 $field = WebserviceField::fromArray($adb, $fieldInfo); 1441 $fieldType = $field->getFieldDataType(); 1442 } 1443 1444 if($fieldType == 'currency') { 1445 // Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion 1446 if($field->getUIType() == '72') { 1447 $adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true); 1448 } else { 1449 $adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value); 1450 } 1451 } 1452 1453 $temp_val = explode(",",$adv_filter_value); 1454 if(($column_info[4] == 'D' || ($column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end') 1455 || ($column_info[4] == 'DT')) 1456 && ($column_info[4] != '' && $adv_filter_value != '' )) { 1457 $val = Array(); 1458 for($x=0;$x<count($temp_val);$x++) { 1459 if($column_info[4] == 'D') { 1460 $date = new DateTimeField(trim($temp_val[$x])); 1461 $val[$x] = $date->getDBInsertDateValue(); 1462 } elseif($column_info[4] == 'DT') { 1463 $date = new DateTimeField(trim($temp_val[$x])); 1464 $val[$x] = $date->getDBInsertDateTimeValue(); 1465 } else { 1466 $date = new DateTimeField(trim($temp_val[$x])); 1467 $val[$x] = $date->getDBInsertTimeValue(); 1468 } 1469 } 1470 $adv_filter_value = implode(",",$val); 1471 } 1472 $criteria = array(); 1473 $criteria['columnname'] = $adv_filter_column; 1474 $criteria['comparator'] = $adv_filter_comparator; 1475 $criteria['value'] = $adv_filter_value; 1476 $criteria['column_condition'] = $adv_filter_column_condition; 1477 1478 $advfilterlist[$adv_filter_groupid]['columns'][] = $criteria; 1479 } 1480 1481 foreach($advft_criteria_groups as $group_index => $group_condition_info) { 1482 if(empty($group_condition_info)) continue; 1483 if(empty($advfilterlist[$group_index])) continue; 1484 $advfilterlist[$group_index]['condition'] = $group_condition_info["groupcondition"]; 1485 $noOfGroupColumns = count($advfilterlist[$group_index]['columns']); 1486 if(!empty($advfilterlist[$group_index]['columns'][$noOfGroupColumns-1]['column_condition'])) { 1487 $advfilterlist[$group_index]['columns'][$noOfGroupColumns-1]['column_condition'] = ''; 1488 } 1489 } 1490 $noOfGroups = count($advfilterlist); 1491 if(!empty($advfilterlist[$noOfGroups]['condition'])) { 1492 $advfilterlist[$noOfGroups]['condition'] = ''; 1493 } 1494 1495 $advfiltersql = $this->generateAdvFilterSql($advfilterlist); 1496 } 1497 return $advfiltersql; 1498 1499 } 1500 1501 /** Function to get standardfilter for the given reportid 1502 * @ param $reportid : Type Integer 1503 * returns the query of columnlist for the selected columns 1504 */ 1505 1506 function getStandardCriterialSql($reportid) 1507 { 1508 global $adb; 1509 global $modules; 1510 global $log; 1511 1512 $sreportstdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report"; 1513 $sreportstdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid"; 1514 $sreportstdfiltersql .= " where vtiger_report.reportid = ?"; 1515 1516 $result = $adb->pquery($sreportstdfiltersql, array($reportid)); 1517 $noofrows = $adb->num_rows($result); 1518 1519 for($i=0; $i<$noofrows; $i++) { 1520 $fieldcolname = $adb->query_result($result,$i,"datecolumnname"); 1521 $datefilter = $adb->query_result($result,$i,"datefilter"); 1522 $startdate = $adb->query_result($result,$i,"startdate"); 1523 $enddate = $adb->query_result($result,$i,"enddate"); 1524 1525 if($fieldcolname != "none") { 1526 $selectedfields = explode(":",$fieldcolname); 1527 if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) 1528 $selectedfields[0] = "vtiger_crmentity"; 1529 if($datefilter == "custom") { 1530 1531 if($startdate != "0000-00-00" && $enddate != "0000-00-00" && $selectedfields[0] != "" && $selectedfields[1] != "" 1532 && $startdate != '' && $enddate != '') { 1533 1534 $startDateTime = new DateTimeField($startdate.' '. date('H:i:s')); 1535 $startdate = $startDateTime->getDisplayDate(); 1536 $endDateTime = new DateTimeField($enddate.' '. date('H:i:s')); 1537 $enddate = $endDateTime->getDisplayDate(); 1538 1539 $sSQL .= $selectedfields[0].".".$selectedfields[1]." between '".$startdate."' and '".$enddate."'"; 1540 } 1541 } else { 1542 1543 $startenddate = $this->getStandarFiltersStartAndEndDate($datefilter); 1544 1545 $startDateTime = new DateTimeField($startenddate[0].' '. date('H:i:s')); 1546 $startdate = $startDateTime->getDisplayDate(); 1547 $endDateTime = new DateTimeField($startenddate[1].' '. date('H:i:s')); 1548 $enddate = $endDateTime->getDisplayDate(); 1549 1550 if($startenddate[0] != "" && $startenddate[1] != "" && $selectedfields[0] != "" && $selectedfields[1] != "") { 1551 $sSQL .= $selectedfields[0].".".$selectedfields[1]." between '".$startdate."' and '".$enddate."'"; 1552 } 1553 } 1554 } 1555 } 1556 $log->info("ReportRun :: Successfully returned getStandardCriterialSql".$reportid); 1557 return $sSQL; 1558 } 1559 1560 /** Function to get standardfilter startdate and enddate for the given type 1561 * @ param $type : Type String 1562 * returns the $datevalue Array in the given format 1563 * $datevalue = Array(0=>$startdate,1=>$enddate) 1564 */ 1565 1566 1567 function getStandarFiltersStartAndEndDate($type) 1568 { 1569 $today = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d"), date("Y"))); 1570 $todayName = date('l', strtotime( $today)); 1571 1572 $tomorrow = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+1, date("Y"))); 1573 $yesterday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-1, date("Y"))); 1574 1575 $currentmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m"), "01", date("Y"))); 1576 $currentmonth1 = date("Y-m-t"); 1577 $lastmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")-1, "01", date("Y"))); 1578 $lastmonth1 = date("Y-m-t", strtotime("-1 Month")); 1579 $nextmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")+1, "01", date("Y"))); 1580 $nextmonth1 = date("Y-m-t", strtotime("+1 Month")); 1581 1582 // (Last Week) If Today is "Sunday" then "-2 week Sunday" will give before last week Sunday date 1583 if($todayName == "Sunday") 1584 $lastweek0 = date("Y-m-d",strtotime("-1 week Sunday")); 1585 else 1586 $lastweek0 = date("Y-m-d",strtotime("-2 week Sunday")); 1587 $lastweek1 = date("Y-m-d",strtotime("-1 week Saturday")); 1588 1589 // (This Week) If Today is "Sunday" then "-1 week Sunday" will give last week Sunday date 1590 if($todayName == "Sunday") 1591 $thisweek0 = date("Y-m-d",strtotime("-0 week Sunday")); 1592 else 1593 $thisweek0 = date("Y-m-d",strtotime("-1 week Sunday")); 1594 $thisweek1 = date("Y-m-d",strtotime("this Saturday")); 1595 1596 // (Next Week) If Today is "Sunday" then "this Sunday" will give Today's date 1597 if($todayName == "Sunday") 1598 $nextweek0 = date("Y-m-d",strtotime("+1 week Sunday")); 1599 else 1600 $nextweek0 = date("Y-m-d",strtotime("this Sunday")); 1601 $nextweek1 = date("Y-m-d",strtotime("+1 week Saturday")); 1602 1603 $next7days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+6, date("Y"))); 1604 $next30days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+29, date("Y"))); 1605 $next60days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+59, date("Y"))); 1606 $next90days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+89, date("Y"))); 1607 $next120days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+119, date("Y"))); 1608 1609 $last7days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-6, date("Y"))); 1610 $last30days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-29, date("Y"))); 1611 $last60days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-59, date("Y"))); 1612 $last90days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-89, date("Y"))); 1613 $last120days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-119, date("Y"))); 1614 1615 $currentFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y"))); 1616 $currentFY1 = date("Y-m-t",mktime(0, 0, 0, "12", date("d"), date("Y"))); 1617 $lastFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y")-1)); 1618 $lastFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")-1)); 1619 $nextFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y")+1)); 1620 $nextFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")+1)); 1621 1622 if(date("m") <= 3) 1623 { 1624 $cFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y"))); 1625 $cFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y"))); 1626 $nFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y"))); 1627 $nFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y"))); 1628 $pFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y")-1)); 1629 $pFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y")-1)); 1630 }else if(date("m") > 3 and date("m") <= 6) 1631 { 1632 $pFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y"))); 1633 $pFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y"))); 1634 $cFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y"))); 1635 $cFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y"))); 1636 $nFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y"))); 1637 $nFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y"))); 1638 1639 }else if(date("m") > 6 and date("m") <= 9) 1640 { 1641 $nFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y"))); 1642 $nFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y"))); 1643 $pFq = date("Y-m-d",mktime(0, 0, 0, "04","01",date("Y"))); 1644 $pFq1 = date("Y-m-d",mktime(0, 0, 0, "06","30",date("Y"))); 1645 $cFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y"))); 1646 $cFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y"))); 1647 } 1648 else if(date("m") > 9 and date("m") <= 12) 1649 { 1650 $nFq = date("Y-m-d",mktime(0, 0, 0, "01","01",date("Y")+1)); 1651 $nFq1 = date("Y-m-d",mktime(0, 0, 0, "03","31",date("Y")+1)); 1652 $pFq = date("Y-m-d",mktime(0, 0, 0, "07","01",date("Y"))); 1653 $pFq1 = date("Y-m-d",mktime(0, 0, 0, "09","30",date("Y"))); 1654 $cFq = date("Y-m-d",mktime(0, 0, 0, "10","01",date("Y"))); 1655 $cFq1 = date("Y-m-d",mktime(0, 0, 0, "12","31",date("Y"))); 1656 1657 } 1658 1659 if($type == "today" ) 1660 { 1661 1662 $datevalue[0] = $today; 1663 $datevalue[1] = $today; 1664 } 1665 elseif($type == "yesterday" ) 1666 { 1667 1668 $datevalue[0] = $yesterday; 1669 $datevalue[1] = $yesterday; 1670 } 1671 elseif($type == "tomorrow" ) 1672 { 1673 1674 $datevalue[0] = $tomorrow; 1675 $datevalue[1] = $tomorrow; 1676 } 1677 elseif($type == "thisweek" ) 1678 { 1679 1680 $datevalue[0] = $thisweek0; 1681 $datevalue[1] = $thisweek1; 1682 } 1683 elseif($type == "lastweek" ) 1684 { 1685 1686 $datevalue[0] = $lastweek0; 1687 $datevalue[1] = $lastweek1; 1688 } 1689 elseif($type == "nextweek" ) 1690 { 1691 1692 $datevalue[0] = $nextweek0; 1693 $datevalue[1] = $nextweek1; 1694 } 1695 elseif($type == "thismonth" ) 1696 { 1697 1698 $datevalue[0] =$currentmonth0; 1699 $datevalue[1] = $currentmonth1; 1700 } 1701 1702 elseif($type == "lastmonth" ) 1703 { 1704 1705 $datevalue[0] = $lastmonth0; 1706 $datevalue[1] = $lastmonth1; 1707 } 1708 elseif($type == "nextmonth" ) 1709 { 1710 1711 $datevalue[0] = $nextmonth0; 1712 $datevalue[1] = $nextmonth1; 1713 } 1714 elseif($type == "next7days" ) 1715 { 1716 1717 $datevalue[0] = $today; 1718 $datevalue[1] = $next7days; 1719 } 1720 elseif($type == "next30days" ) 1721 { 1722 1723 $datevalue[0] =$today; 1724 $datevalue[1] =$next30days; 1725 } 1726 elseif($type == "next60days" ) 1727 { 1728 1729 $datevalue[0] = $today; 1730 $datevalue[1] = $next60days; 1731 } 1732 elseif($type == "next90days" ) 1733 { 1734 1735 $datevalue[0] = $today; 1736 $datevalue[1] = $next90days; 1737 } 1738 elseif($type == "next120days" ) 1739 { 1740 1741 $datevalue[0] = $today; 1742 $datevalue[1] = $next120days; 1743 } 1744 elseif($type == "last7days" ) 1745 { 1746 1747 $datevalue[0] = $last7days; 1748 $datevalue[1] = $today; 1749 } 1750 elseif($type == "last30days" ) 1751 { 1752 1753 $datevalue[0] = $last30days; 1754 $datevalue[1] = $today; 1755 } 1756 elseif($type == "last60days" ) 1757 { 1758 1759 $datevalue[0] = $last60days; 1760 $datevalue[1] = $today; 1761 } 1762 else if($type == "last90days" ) 1763 { 1764 1765 $datevalue[0] = $last90days; 1766 $datevalue[1] = $today; 1767 } 1768 elseif($type == "last120days" ) 1769 { 1770 1771 $datevalue[0] = $last120days; 1772 $datevalue[1] = $today; 1773 } 1774 elseif($type == "thisfy" ) 1775 { 1776 1777 $datevalue[0] = $currentFY0; 1778 $datevalue[1] = $currentFY1; 1779 } 1780 elseif($type == "prevfy" ) 1781 { 1782 1783 $datevalue[0] = $lastFY0; 1784 $datevalue[1] = $lastFY1; 1785 } 1786 elseif($type == "nextfy" ) 1787 { 1788 1789 $datevalue[0] = $nextFY0; 1790 $datevalue[1] = $nextFY1; 1791 } 1792 elseif($type == "nextfq" ) 1793 { 1794 1795 $datevalue[0] = $nFq; 1796 $datevalue[1] = $nFq1; 1797 } 1798 elseif($type == "prevfq" ) 1799 { 1800 1801 $datevalue[0] = $pFq; 1802 $datevalue[1] = $pFq1; 1803 } 1804 elseif($type == "thisfq" ) 1805 { 1806 $datevalue[0] = $cFq; 1807 $datevalue[1] = $cFq1; 1808 } 1809 else 1810 { 1811 $datevalue[0] = ""; 1812 $datevalue[1] = ""; 1813 } 1814 return $datevalue; 1815 } 1816 1817 function hasGroupingList() { 1818 global $adb; 1819 $result = $adb->pquery('SELECT 1 FROM vtiger_reportsortcol WHERE reportid=? and columnname <> "none"', array($this->reportid)); 1820 return ($result && $adb->num_rows($result))? true : false; 1821 } 1822 1823 /** Function to get getGroupingList for the given reportid 1824 * @ param $reportid : Type Integer 1825 * returns the $grouplist Array in the following format 1826 * $grouplist = Array($tablename:$columnname:$fieldlabel:fieldname:typeofdata=>$tablename:$columnname $sorder, 1827 * $tablename1:$columnname1:$fieldlabel1:fieldname1:typeofdata1=>$tablename1:$columnname1 $sorder, 1828 * $tablename2:$columnname2:$fieldlabel2:fieldname2:typeofdata2=>$tablename2:$columnname2 $sorder) 1829 * This function also sets the return value in the class variable $this->groupbylist 1830 */ 1831 1832 1833 function getGroupingList($reportid) 1834 { 1835 global $adb; 1836 global $modules; 1837 global $log; 1838 1839 // Have we initialized information already? 1840 if($this->_groupinglist !== false) { 1841 return $this->_groupinglist; 1842 } 1843 1844 $sreportsortsql = " SELECT vtiger_reportsortcol.*, vtiger_reportgroupbycolumn.* FROM vtiger_report"; 1845 $sreportsortsql .= " inner join vtiger_reportsortcol on vtiger_report.reportid = vtiger_reportsortcol.reportid"; 1846 $sreportsortsql .= " LEFT JOIN vtiger_reportgroupbycolumn ON (vtiger_report.reportid = vtiger_reportgroupbycolumn.reportid AND vtiger_reportsortcol.sortcolid = vtiger_reportgroupbycolumn.sortid)"; 1847 $sreportsortsql .= " where vtiger_report.reportid =? AND vtiger_reportsortcol.columnname IN (SELECT columnname from vtiger_selectcolumn WHERE queryid=?) order by vtiger_reportsortcol.sortcolid"; 1848 1849 $result = $adb->pquery($sreportsortsql, array($reportid,$reportid)); 1850 $grouplist = array(); 1851 1852 $inventoryModules = getInventoryModules(); 1853 while($reportsortrow = $adb->fetch_array($result)) 1854 { 1855 $fieldcolname = $reportsortrow["columnname"]; 1856 list($tablename,$colname,$module_field,$fieldname,$single) = split(":",$fieldcolname); 1857 $sortorder = $reportsortrow["sortorder"]; 1858 1859 if($sortorder == "Ascending") 1860 { 1861 $sortorder = "ASC"; 1862 1863 }elseif($sortorder == "Descending") 1864 { 1865 $sortorder = "DESC"; 1866 } 1867 1868 if($fieldcolname != "none") 1869 { 1870 $selectedfields = explode(":",$fieldcolname); 1871 if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) 1872 $selectedfields[0] = "vtiger_crmentity"; 1873 if(stripos($selectedfields[1],'cf_')==0 && stristr($selectedfields[1],'cf_')==true){ 1874 //In sql queries forward slash(/) is treated as query terminator,so to avoid this problem 1875 //the column names are enclosed within ('[]'),which will treat this as part of column name 1876 $sqlvalue = "`".$adb->sql_escape_string(decode_html($selectedfields[2]))."` ".$sortorder; 1877 } else { 1878 $sqlvalue = "`".self::replaceSpecialChar($selectedfields[2])."` ".$sortorder; 1879 } 1880 if($selectedfields[4]=="D" && strtolower($reportsortrow["dategroupbycriteria"])!="none"){ 1881 $groupField = $module_field; 1882 $groupCriteria = $reportsortrow["dategroupbycriteria"]; 1883 if(in_array($groupCriteria,array_keys($this->groupByTimeParent))){ 1884 $parentCriteria = $this->groupByTimeParent[$groupCriteria]; 1885 foreach($parentCriteria as $criteria){ 1886 $groupByCondition[]=$this->GetTimeCriteriaCondition($criteria, $groupField)." ".$sortorder; 1887 } 1888 } 1889 $groupByCondition[] =$this->GetTimeCriteriaCondition($groupCriteria, $groupField)." ".$sortorder; 1890 $sqlvalue = implode(", ",$groupByCondition); 1891 } 1892 $grouplist[$fieldcolname] = $sqlvalue; 1893 $temp = split("_",$selectedfields[2],2); 1894 $module = $temp[0]; 1895 if (in_array($module, $inventoryModules) && $fieldname == 'serviceid') { 1896 $grouplist[$fieldcolname] = $sqlvalue; 1897 } else if(CheckFieldPermission($fieldname,$module) == 'true') { 1898 $grouplist[$fieldcolname] = $sqlvalue; 1899 } else { 1900 $grouplist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]; 1901 } 1902 1903 $this->queryPlanner->addTable($tablename); 1904 } 1905 } 1906 1907 // Save the information 1908 $this->_groupinglist = $grouplist; 1909 1910 $log->info("ReportRun :: Successfully returned getGroupingList".$reportid); 1911 return $grouplist; 1912 } 1913 1914 /** function to replace special characters 1915 * @ param $selectedfield : type string 1916 * this returns the string for grouplist 1917 */ 1918 1919 function replaceSpecialChar($selectedfield){ 1920 $selectedfield = decode_html(decode_html($selectedfield)); 1921 preg_match('/&/', $selectedfield, $matches); 1922 if(!empty($matches)){ 1923 $selectedfield = str_replace('&', 'and',($selectedfield)); 1924 } 1925 return $selectedfield; 1926 } 1927 1928 /** function to get the selectedorderbylist for the given reportid 1929 * @ param $reportid : type integer 1930 * this returns the columns query for the sortorder columns 1931 * this function also sets the return value in the class variable $this->orderbylistsql 1932 */ 1933 1934 1935 function getSelectedOrderbyList($reportid) 1936 { 1937 1938 global $adb; 1939 global $modules; 1940 global $log; 1941 1942 $sreportsortsql = "select vtiger_reportsortcol.* from vtiger_report"; 1943 $sreportsortsql .= " inner join vtiger_reportsortcol on vtiger_report.reportid = vtiger_reportsortcol.reportid"; 1944 $sreportsortsql .= " where vtiger_report.reportid =? order by vtiger_reportsortcol.sortcolid"; 1945 1946 $result = $adb->pquery($sreportsortsql, array($reportid)); 1947 $noofrows = $adb->num_rows($result); 1948 1949 for($i=0; $i<$noofrows; $i++) 1950 { 1951 $fieldcolname = $adb->query_result($result,$i,"columnname"); 1952 $sortorder = $adb->query_result($result,$i,"sortorder"); 1953 1954 if($sortorder == "Ascending") 1955 { 1956 $sortorder = "ASC"; 1957 } 1958 elseif($sortorder == "Descending") 1959 { 1960 $sortorder = "DESC"; 1961 } 1962 1963 if($fieldcolname != "none") 1964 { 1965 $this->orderbylistcolumns[] = $fieldcolname; 1966 $n = $n + 1; 1967 $selectedfields = explode(":",$fieldcolname); 1968 if($n > 1) 1969 { 1970 $sSQL .= ", "; 1971 $this->orderbylistsql .= ", "; 1972 } 1973 if($selectedfields[0] == "vtiger_crmentity".$this->primarymodule) 1974 $selectedfields[0] = "vtiger_crmentity"; 1975 $sSQL .= $selectedfields[0].".".$selectedfields[1]." ".$sortorder; 1976 $this->orderbylistsql .= $selectedfields[0].".".$selectedfields[1]." ".$selectedfields[2]; 1977 } 1978 } 1979 $log->info("ReportRun :: Successfully returned getSelectedOrderbyList".$reportid); 1980 return $sSQL; 1981 } 1982 1983 /** function to get secondary Module for the given Primary module and secondary module 1984 * @ param $module : type String 1985 * @ param $secmodule : type String 1986 * this returns join query for the given secondary module 1987 */ 1988 1989 function getRelatedModulesQuery($module,$secmodule) 1990 { 1991 global $log,$current_user; 1992 $query = ''; 1993 if($secmodule!=''){ 1994 $secondarymodule = explode(":",$secmodule); 1995 foreach($secondarymodule as $key=>$value) { 1996 $foc = CRMEntity::getInstance($value); 1997 1998 // Case handling: Force table requirement ahead of time. 1999 $this->queryPlanner->addTable('vtiger_crmentity'. $value); 2000 2001 $focQuery = $foc->generateReportsSecQuery($module,$value, $this->queryPlanner); 2002 2003 if ($focQuery) { 2004 if(count($secondarymodule) > 1){ 2005 $query .= $focQuery . $this->getReportsNonAdminAccessControlQuery($value,$current_user,$value); 2006 }else{ 2007 $query .= $focQuery . getNonAdminAccessControlQuery($value,$current_user,$value);; 2008 } 2009 } 2010 } 2011 } 2012 $log->info("ReportRun :: Successfully returned getRelatedModulesQuery".$secmodule); 2013 2014 return $query; 2015 2016 } 2017 2018 /** 2019 * Non admin user not able to see the records of report even he has permission 2020 * Fix for Case :- Report with One Primary Module, and Two Secondary modules, let's say for one of the 2021 * secondary module, non-admin user don't have permission, then reports is not showing the record even 2022 * the user has permission for another seconday module. 2023 * @param type $module 2024 * @param type $user 2025 * @param type $scope 2026 * @return $query 2027 */ 2028 function getReportsNonAdminAccessControlQuery($module, $user, $scope = '') { 2029 require('user_privileges/user_privileges_' . $user->id . '.php'); 2030 require('user_privileges/sharing_privileges_' . $user->id . '.php'); 2031 $query = ' '; 2032 $tabId = getTabid($module); 2033 if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] 2034 == 1 && $defaultOrgSharingPermission[$tabId] == 3) { 2035 $sharingRuleInfoVariable = $module . '_share_read_permission'; 2036 $sharingRuleInfo = $$sharingRuleInfoVariable; 2037 $sharedTabId = null; 2038 2039 if($module == "Calendar"){ 2040 $sharedTabId = $tabId; 2041 $tableName = 'vt_tmp_u'.$user->id.'_t'.$tabId; 2042 }else if(!empty($sharingRuleInfo) && (count($sharingRuleInfo['ROLE']) > 0 || 2043 count($sharingRuleInfo['GROUP']) > 0)) { 2044 $sharedTabId = $tabId; 2045 } 2046 2047 if (!empty($sharedTabId)) { 2048 $module = getTabModuleName($sharedTabId); 2049 if($module == "Calendar"){ 2050 // For calendar we have some special case to check like, calendar shared type 2051 $moduleInstance = CRMEntity::getInstance($module); 2052 $query = $moduleInstance->getReportsNonAdminAccessControlQuery($tableName, $tabId, $user, $current_user_parent_role_seq,$current_user_groups); 2053 }else{ 2054 $query = $this->getNonAdminAccessQuery($module, $user, $current_user_parent_role_seq, $current_user_groups); 2055 } 2056 2057 $db = PearDatabase::getInstance(); 2058 $result = $db->pquery($query, array()); 2059 $rows = $db->num_rows($result); 2060 for($i=0; $i<$rows; $i++) { 2061 $ids[] = $db->query_result($result, $i, 'id'); 2062 } 2063 if(!empty($ids)) { 2064 $query = " AND vtiger_crmentity$scope.smownerid IN (".implode(',', $ids).") "; 2065 } 2066 } 2067 } 2068 return $query; 2069 } 2070 2071 2072 /** function to get report query for the given module 2073 * @ param $module : type String 2074 * this returns join query for the given module 2075 */ 2076 2077 function getReportsQuery($module, $type='') 2078 { 2079 global $log, $current_user; 2080 $secondary_module ="'"; 2081 $secondary_module .= str_replace(":","','",$this->secondarymodule); 2082 $secondary_module .="'"; 2083 2084 if($module == "Leads") 2085 { 2086 $query = "from vtiger_leaddetails 2087 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_leaddetails.leadid"; 2088 2089 if ($this->queryPlanner->requireTable('vtiger_leadsubdetails')) { 2090 $query .= " inner join vtiger_leadsubdetails on vtiger_leadsubdetails.leadsubscriptionid=vtiger_leaddetails.leadid"; 2091 } 2092 if ($this->queryPlanner->requireTable('vtiger_leadaddress')) { 2093 $query .= " inner join vtiger_leadaddress on vtiger_leadaddress.leadaddressid=vtiger_leaddetails.leadid"; 2094 } 2095 if ($this->queryPlanner->requireTable('vtiger_leadscf')) { 2096 $query .= " inner join vtiger_leadscf on vtiger_leaddetails.leadid = vtiger_leadscf.leadid"; 2097 } 2098 if ($this->queryPlanner->requireTable('vtiger_groupsLeads')) { 2099 $query .= " left join vtiger_groups as vtiger_groupsLeads on vtiger_groupsLeads.groupid = vtiger_crmentity.smownerid"; 2100 } 2101 if ($this->queryPlanner->requireTable('vtiger_usersLeads')) { 2102 $query .= " left join vtiger_users as vtiger_usersLeads on vtiger_usersLeads.id = vtiger_crmentity.smownerid"; 2103 } 2104 2105 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid 2106 left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2107 2108 if ($this->queryPlanner->requireTable('vtiger_lastModifiedByLeads')) { 2109 $query .= " left join vtiger_users as vtiger_lastModifiedByLeads on vtiger_lastModifiedByLeads.id = vtiger_crmentity.modifiedby"; 2110 } 2111 if($this->queryPlanner->requireTable('vtiger_createdbyLeads')){ 2112 $query .= " left join vtiger_users as vtiger_createdbyLeads on vtiger_createdbyLeads.id = vtiger_crmentity.smcreatorid"; 2113 } 2114 2115 $query .= " " . $this->getRelatedModulesQuery($module,$this->secondarymodule). 2116 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2117 " where vtiger_crmentity.deleted=0 and vtiger_leaddetails.converted=0"; 2118 } 2119 else if($module == "Accounts") 2120 { 2121 $query = "from vtiger_account 2122 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_account.accountid"; 2123 2124 if ($this->queryPlanner->requireTable('vtiger_accountbillads')) { 2125 $query .= " inner join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid"; 2126 } 2127 if ($this->queryPlanner->requireTable('vtiger_accountshipads')) { 2128 $query .= " inner join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid"; 2129 } 2130 if ($this->queryPlanner->requireTable('vtiger_accountscf')) { 2131 $query .= " inner join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid"; 2132 } 2133 if ($this->queryPlanner->requireTable('vtiger_groupsAccounts')) { 2134 $query .= " left join vtiger_groups as vtiger_groupsAccounts on vtiger_groupsAccounts.groupid = vtiger_crmentity.smownerid"; 2135 } 2136 if ($this->queryPlanner->requireTable('vtiger_accountAccounts')) { 2137 $query .= " left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid"; 2138 } 2139 if ($this->queryPlanner->requireTable('vtiger_usersAccounts')) { 2140 $query .= " left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentity.smownerid"; 2141 } 2142 2143 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid 2144 left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2145 2146 if ($this->queryPlanner->requireTable('vtiger_lastModifiedByAccounts')) { 2147 $query.= " left join vtiger_users as vtiger_lastModifiedByAccounts on vtiger_lastModifiedByAccounts.id = vtiger_crmentity.modifiedby"; 2148 } 2149 if($this->queryPlanner->requireTable('vtiger_createdbyAccounts')){ 2150 $query .= " left join vtiger_users as vtiger_createdbyAccounts on vtiger_createdbyAccounts.id = vtiger_crmentity.smcreatorid"; 2151 } 2152 2153 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2154 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2155 " where vtiger_crmentity.deleted=0 "; 2156 } 2157 2158 else if($module == "Contacts") 2159 { 2160 $query = "from vtiger_contactdetails 2161 inner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_contactdetails.contactid"; 2162 2163 if ($this->queryPlanner->requireTable('vtiger_contactaddress')) { 2164 $query .= " inner join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid"; 2165 } 2166 if ($this->queryPlanner->requireTable('vtiger_customerdetails')) { 2167 $query .= " inner join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid"; 2168 } 2169 if ($this->queryPlanner->requireTable('vtiger_contactsubdetails')) { 2170 $query .= " inner join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid"; 2171 } 2172 if ($this->queryPlanner->requireTable('vtiger_contactscf')) { 2173 $query .= " inner join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid"; 2174 } 2175 if ($this->queryPlanner->requireTable('vtiger_groupsContacts')) { 2176 $query .= " left join vtiger_groups vtiger_groupsContacts on vtiger_groupsContacts.groupid = vtiger_crmentity.smownerid"; 2177 } 2178 if ($this->queryPlanner->requireTable('vtiger_contactdetailsContacts')) { 2179 $query .= " left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto"; 2180 } 2181 if ($this->queryPlanner->requireTable('vtiger_accountContacts')) { 2182 $query .= " left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid"; 2183 } 2184 if ($this->queryPlanner->requireTable('vtiger_usersContacts')) { 2185 $query .= " left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentity.smownerid"; 2186 } 2187 2188 $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid 2189 left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2190 2191 if ($this->queryPlanner->requireTable('vtiger_lastModifiedByContacts')) { 2192 $query .= " left join vtiger_users as vtiger_lastModifiedByContacts on vtiger_lastModifiedByContacts.id = vtiger_crmentity.modifiedby"; 2193 } 2194 if($this->queryPlanner->requireTable('vtiger_createdbyContacts')){ 2195 $query .= " left join vtiger_users as vtiger_createdbyContacts on vtiger_createdbyContacts.id = vtiger_crmentity.smcreatorid"; 2196 } 2197 2198 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2199 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2200 " where vtiger_crmentity.deleted=0"; 2201 } 2202 2203 else if($module == "Potentials") 2204 { 2205 $query = "from vtiger_potential 2206 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_potential.potentialid"; 2207 2208 if ($this->queryPlanner->requireTable('vtiger_potentialscf')) { 2209 $query .= " inner join vtiger_potentialscf on vtiger_potentialscf.potentialid = vtiger_potential.potentialid"; 2210 } 2211 if ($this->queryPlanner->requireTable('vtiger_accountPotentials')) { 2212 $query .= " left join vtiger_account as vtiger_accountPotentials on vtiger_potential.related_to = vtiger_accountPotentials.accountid"; 2213 } 2214 if ($this->queryPlanner->requireTable('vtiger_contactdetailsPotentials')) { 2215 $query .= " left join vtiger_contactdetails as vtiger_contactdetailsPotentials on vtiger_potential.contact_id = vtiger_contactdetailsPotentials.contactid"; 2216 } 2217 if ($this->queryPlanner->requireTable('vtiger_campaignPotentials')) { 2218 $query .= " left join vtiger_campaign as vtiger_campaignPotentials on vtiger_potential.campaignid = vtiger_campaignPotentials.campaignid"; 2219 } 2220 if ($this->queryPlanner->requireTable('vtiger_groupsPotentials')) { 2221 $query .= " left join vtiger_groups vtiger_groupsPotentials on vtiger_groupsPotentials.groupid = vtiger_crmentity.smownerid"; 2222 } 2223 if ($this->queryPlanner->requireTable('vtiger_usersPotentials')) { 2224 $query .= " left join vtiger_users as vtiger_usersPotentials on vtiger_usersPotentials.id = vtiger_crmentity.smownerid"; 2225 } 2226 2227 // TODO optimize inclusion of these tables 2228 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2229 $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2230 2231 if ($this->queryPlanner->requireTable('vtiger_lastModifiedByPotentials')) { 2232 $query .= " left join vtiger_users as vtiger_lastModifiedByPotentials on vtiger_lastModifiedByPotentials.id = vtiger_crmentity.modifiedby"; 2233 } 2234 if($this->queryPlanner->requireTable('vtiger_createdbyPotentials')){ 2235 $query .= " left join vtiger_users as vtiger_createdbyPotentials on vtiger_createdbyPotentials.id = vtiger_crmentity.smcreatorid"; 2236 } 2237 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2238 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2239 " where vtiger_crmentity.deleted=0 "; 2240 } 2241 2242 //For this Product - we can related Accounts, Contacts (Also Leads, Potentials) 2243 else if($module == "Products") 2244 { 2245 $query .= " from vtiger_products"; 2246 $query .= " inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_products.productid"; 2247 if ($this->queryPlanner->requireTable("vtiger_productcf")){ 2248 $query .= " left join vtiger_productcf on vtiger_products.productid = vtiger_productcf.productid"; 2249 } 2250 if ($this->queryPlanner->requireTable("vtiger_lastModifiedByProducts")){ 2251 $query .= " left join vtiger_users as vtiger_lastModifiedByProducts on vtiger_lastModifiedByProducts.id = vtiger_crmentity.modifiedby"; 2252 } 2253 if($this->queryPlanner->requireTable('vtiger_createdbyProducts')){ 2254 $query .= " left join vtiger_users as vtiger_createdbyProducts on vtiger_createdbyProducts.id = vtiger_crmentity.smcreatorid"; 2255 } 2256 if ($this->queryPlanner->requireTable("vtiger_usersProducts")){ 2257 $query .= " left join vtiger_users as vtiger_usersProducts on vtiger_usersProducts.id = vtiger_crmentity.smownerid"; 2258 } 2259 if ($this->queryPlanner->requireTable("vtiger_groupsProducts")){ 2260 $query .= " left join vtiger_groups as vtiger_groupsProducts on vtiger_groupsProducts.groupid = vtiger_crmentity.smownerid"; 2261 } 2262 if ($this->queryPlanner->requireTable("vtiger_vendorRelProducts")){ 2263 $query .= " left join vtiger_vendor as vtiger_vendorRelProducts on vtiger_vendorRelProducts.vendorid = vtiger_products.vendor_id"; 2264 } 2265 if ($this->queryPlanner->requireTable("innerProduct")){ 2266 $query .= " LEFT JOIN ( 2267 SELECT vtiger_products.productid, 2268 (CASE WHEN (vtiger_products.currency_id = 1 ) THEN vtiger_products.unit_price 2269 ELSE (vtiger_products.unit_price / vtiger_currency_info.conversion_rate) END 2270 ) AS actual_unit_price 2271 FROM vtiger_products 2272 LEFT JOIN vtiger_currency_info ON vtiger_products.currency_id = vtiger_currency_info.id 2273 LEFT JOIN vtiger_productcurrencyrel ON vtiger_products.productid = vtiger_productcurrencyrel.productid 2274 AND vtiger_productcurrencyrel.currencyid = ". $current_user->currency_id . " 2275 ) AS innerProduct ON innerProduct.productid = vtiger_products.productid"; 2276 } 2277 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2278 getNonAdminAccessControlQuery($this->primarymodule,$current_user)." 2279 where vtiger_crmentity.deleted=0"; 2280 } 2281 2282 else if($module == "HelpDesk") 2283 { 2284 $matrix = $this->queryPlanner->newDependencyMatrix(); 2285 2286 $matrix->setDependency('vtiger_crmentityRelHelpDesk',array('vtiger_accountRelHelpDesk','vtiger_contactdetailsRelHelpDesk')); 2287 2288 $query = "from vtiger_troubletickets inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_troubletickets.ticketid"; 2289 2290 if ($this->queryPlanner->requireTable('vtiger_ticketcf')) { 2291 $query .= " inner join vtiger_ticketcf on vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid"; 2292 } 2293 if ($this->queryPlanner->requireTable('vtiger_crmentityRelHelpDesk', $matrix)) { 2294 $query .= " left join vtiger_crmentity as vtiger_crmentityRelHelpDesk on vtiger_crmentityRelHelpDesk.crmid = vtiger_troubletickets.parent_id"; 2295 } 2296 if ($this->queryPlanner->requireTable('vtiger_accountRelHelpDesk')) { 2297 $query .= " left join vtiger_account as vtiger_accountRelHelpDesk on vtiger_accountRelHelpDesk.accountid=vtiger_crmentityRelHelpDesk.crmid"; 2298 } 2299 if ($this->queryPlanner->requireTable('vtiger_contactdetailsRelHelpDesk')) { 2300 $query .= " left join vtiger_contactdetails as vtiger_contactdetailsRelHelpDesk on vtiger_contactdetailsRelHelpDesk.contactid= vtiger_troubletickets.contact_id"; 2301 } 2302 if ($this->queryPlanner->requireTable('vtiger_productsRel')) { 2303 $query .= " left join vtiger_products as vtiger_productsRel on vtiger_productsRel.productid = vtiger_troubletickets.product_id"; 2304 } 2305 if ($this->queryPlanner->requireTable('vtiger_groupsHelpDesk')) { 2306 $query .= " left join vtiger_groups as vtiger_groupsHelpDesk on vtiger_groupsHelpDesk.groupid = vtiger_crmentity.smownerid"; 2307 } 2308 if ($this->queryPlanner->requireTable('vtiger_usersHelpDesk')) { 2309 $query .= " left join vtiger_users as vtiger_usersHelpDesk on vtiger_crmentity.smownerid=vtiger_usersHelpDesk.id"; 2310 } 2311 2312 // TODO optimize inclusion of these tables 2313 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2314 $query .= " left join vtiger_users on vtiger_crmentity.smownerid=vtiger_users.id"; 2315 2316 if ($this->queryPlanner->requireTable('vtiger_lastModifiedByHelpDesk')) { 2317 $query .= " left join vtiger_users as vtiger_lastModifiedByHelpDesk on vtiger_lastModifiedByHelpDesk.id = vtiger_crmentity.modifiedby"; 2318 } 2319 if($this->queryPlanner->requireTable('vtiger_createdbyHelpDesk')){ 2320 $query .= " left join vtiger_users as vtiger_createdbyHelpDesk on vtiger_createdbyHelpDesk.id = vtiger_crmentity.smcreatorid"; 2321 } 2322 2323 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2324 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2325 " where vtiger_crmentity.deleted=0 "; 2326 } 2327 2328 else if($module == "Calendar") 2329 { 2330 2331 $matrix = $this->queryPlanner->newDependencyMatrix(); 2332 2333 $matrix->setDependency('vtiger_cntactivityrel', array('vtiger_contactdetailsCalendar')); 2334 $matrix->setDependency('vtiger_seactivityrel', array('vtiger_crmentityRelCalendar')); 2335 $matrix->setDependency('vtiger_crmentityRelCalendar', array('vtiger_accountRelCalendar', 2336 'vtiger_leaddetailsRelCalendar','vtiger_potentialRelCalendar','vtiger_quotesRelCalendar', 2337 'vtiger_purchaseorderRelCalendar', 'vtiger_invoiceRelCalendar', 'vtiger_salesorderRelCalendar', 2338 'vtiger_troubleticketsRelCalendar', 'vtiger_campaignRelCalendar' 2339 )); 2340 2341 $query = "from vtiger_activity 2342 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid"; 2343 2344 if ($this->queryPlanner->requireTable('vtiger_activitycf')) { 2345 $query .= " left join vtiger_activitycf on vtiger_activitycf.activityid = vtiger_crmentity.crmid"; 2346 } 2347 if ($this->queryPlanner->requireTable('vtiger_cntactivityrel', $matrix)) { 2348 $query .= " left join vtiger_cntactivityrel on vtiger_cntactivityrel.activityid= vtiger_activity.activityid"; 2349 } 2350 if ($this->queryPlanner->requireTable('vtiger_contactdetailsCalendar')) { 2351 $query .= " left join vtiger_contactdetails as vtiger_contactdetailsCalendar on vtiger_contactdetailsCalendar.contactid= vtiger_cntactivityrel.contactid"; 2352 } 2353 if ($this->queryPlanner->requireTable('vtiger_groupsCalendar')) { 2354 $query .= " left join vtiger_groups as vtiger_groupsCalendar on vtiger_groupsCalendar.groupid = vtiger_crmentity.smownerid"; 2355 } 2356 if ($this->queryPlanner->requireTable('vtiger_usersCalendar')) { 2357 $query .= " left join vtiger_users as vtiger_usersCalendar on vtiger_usersCalendar.id = vtiger_crmentity.smownerid"; 2358 } 2359 2360 // TODO optimize inclusion of these tables 2361 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2362 $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2363 2364 if ($this->queryPlanner->requireTable('vtiger_seactivityrel', $matrix)) { 2365 $query .= " left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_activity.activityid"; 2366 } 2367 if ($this->queryPlanner->requireTable('vtiger_activity_reminder')) { 2368 $query .= " left join vtiger_activity_reminder on vtiger_activity_reminder.activity_id = vtiger_activity.activityid"; 2369 } 2370 if ($this->queryPlanner->requireTable('vtiger_recurringevents')) { 2371 $query .= " left join vtiger_recurringevents on vtiger_recurringevents.activityid = vtiger_activity.activityid"; 2372 } 2373 if ($this->queryPlanner->requireTable('vtiger_crmentityRelCalendar', $matrix)) { 2374 $query .= " left join vtiger_crmentity as vtiger_crmentityRelCalendar on vtiger_crmentityRelCalendar.crmid = vtiger_seactivityrel.crmid"; 2375 } 2376 if ($this->queryPlanner->requireTable('vtiger_accountRelCalendar')) { 2377 $query .= " left join vtiger_account as vtiger_accountRelCalendar on vtiger_accountRelCalendar.accountid=vtiger_crmentityRelCalendar.crmid"; 2378 } 2379 if ($this->queryPlanner->requireTable('vtiger_leaddetailsRelCalendar')) { 2380 $query .= " left join vtiger_leaddetails as vtiger_leaddetailsRelCalendar on vtiger_leaddetailsRelCalendar.leadid = vtiger_crmentityRelCalendar.crmid"; 2381 } 2382 if ($this->queryPlanner->requireTable('vtiger_potentialRelCalendar')) { 2383 $query .= " left join vtiger_potential as vtiger_potentialRelCalendar on vtiger_potentialRelCalendar.potentialid = vtiger_crmentityRelCalendar.crmid"; 2384 } 2385 if ($this->queryPlanner->requireTable('vtiger_quotesRelCalendar')) { 2386 $query .= " left join vtiger_quotes as vtiger_quotesRelCalendar on vtiger_quotesRelCalendar.quoteid = vtiger_crmentityRelCalendar.crmid"; 2387 } 2388 if ($this->queryPlanner->requireTable('vtiger_purchaseorderRelCalendar')) { 2389 $query .= " left join vtiger_purchaseorder as vtiger_purchaseorderRelCalendar on vtiger_purchaseorderRelCalendar.purchaseorderid = vtiger_crmentityRelCalendar.crmid"; 2390 } 2391 if ($this->queryPlanner->requireTable('vtiger_invoiceRelCalendar')) { 2392 $query .= " left join vtiger_invoice as vtiger_invoiceRelCalendar on vtiger_invoiceRelCalendar.invoiceid = vtiger_crmentityRelCalendar.crmid"; 2393 } 2394 if ($this->queryPlanner->requireTable('vtiger_salesorderRelCalendar')) { 2395 $query .= " left join vtiger_salesorder as vtiger_salesorderRelCalendar on vtiger_salesorderRelCalendar.salesorderid = vtiger_crmentityRelCalendar.crmid"; 2396 } 2397 if ($this->queryPlanner->requireTable('vtiger_troubleticketsRelCalendar')) { 2398 $query .= " left join vtiger_troubletickets as vtiger_troubleticketsRelCalendar on vtiger_troubleticketsRelCalendar.ticketid = vtiger_crmentityRelCalendar.crmid"; 2399 } 2400 if ($this->queryPlanner->requireTable('vtiger_campaignRelCalendar')) { 2401 $query .= " left join vtiger_campaign as vtiger_campaignRelCalendar on vtiger_campaignRelCalendar.campaignid = vtiger_crmentityRelCalendar.crmid"; 2402 } 2403 if ($this->queryPlanner->requireTable('vtiger_lastModifiedByCalendar')) { 2404 $query .= " left join vtiger_users as vtiger_lastModifiedByCalendar on vtiger_lastModifiedByCalendar.id = vtiger_crmentity.modifiedby"; 2405 } 2406 if($this->queryPlanner->requireTable('vtiger_createdbyCalendar')){ 2407 $query .= " left join vtiger_users as vtiger_createdbyCalendar on vtiger_createdbyCalendar.id = vtiger_crmentity.smcreatorid"; 2408 } 2409 2410 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2411 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2412 " WHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype != 'Emails')"; 2413 } 2414 2415 else if($module == "Quotes") 2416 { 2417 $matrix = $this->queryPlanner->newDependencyMatrix(); 2418 2419 $matrix->setDependency('vtiger_inventoryproductrelQuotes',array('vtiger_productsQuotes','vtiger_serviceQuotes')); 2420 2421 $query = "from vtiger_quotes 2422 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_quotes.quoteid"; 2423 2424 if ($this->queryPlanner->requireTable('vtiger_quotesbillads')){ 2425 $query .= " inner join vtiger_quotesbillads on vtiger_quotes.quoteid=vtiger_quotesbillads.quotebilladdressid"; 2426 } 2427 if ($this->queryPlanner->requireTable('vtiger_quotesshipads')){ 2428 $query .= " inner join vtiger_quotesshipads on vtiger_quotes.quoteid=vtiger_quotesshipads.quoteshipaddressid"; 2429 } 2430 if ($this->queryPlanner->requireTable("vtiger_currency_info$module")){ 2431 $query .= " left join vtiger_currency_info as vtiger_currency_info$module on vtiger_currency_info$module.id = vtiger_quotes.currency_id"; 2432 } 2433 if($type !== 'COLUMNSTOTOTAL' || $this->lineItemFieldsInCalculation == true) { 2434 if ($this->queryPlanner->requireTable("vtiger_inventoryproductrelQuotes", $matrix)){ 2435 $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelQuotes on vtiger_quotes.quoteid = vtiger_inventoryproductrelQuotes.id"; 2436 } 2437 if ($this->queryPlanner->requireTable("vtiger_productsQuotes")){ 2438 $query .= " left join vtiger_products as vtiger_productsQuotes on vtiger_productsQuotes.productid = vtiger_inventoryproductrelQuotes.productid"; 2439 } 2440 if ($this->queryPlanner->requireTable("vtiger_serviceQuotes")){ 2441 $query .= " left join vtiger_service as vtiger_serviceQuotes on vtiger_serviceQuotes.serviceid = vtiger_inventoryproductrelQuotes.productid"; 2442 } 2443 } 2444 if ($this->queryPlanner->requireTable("vtiger_quotescf")){ 2445 $query .= " left join vtiger_quotescf on vtiger_quotes.quoteid = vtiger_quotescf.quoteid"; 2446 } 2447 if ($this->queryPlanner->requireTable("vtiger_groupsQuotes")){ 2448 $query .= " left join vtiger_groups as vtiger_groupsQuotes on vtiger_groupsQuotes.groupid = vtiger_crmentity.smownerid"; 2449 } 2450 if ($this->queryPlanner->requireTable("vtiger_usersQuotes")){ 2451 $query .= " left join vtiger_users as vtiger_usersQuotes on vtiger_usersQuotes.id = vtiger_crmentity.smownerid"; 2452 } 2453 2454 // TODO optimize inclusion of these tables 2455 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2456 $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2457 2458 if ($this->queryPlanner->requireTable("vtiger_lastModifiedByQuotes")){ 2459 $query .= " left join vtiger_users as vtiger_lastModifiedByQuotes on vtiger_lastModifiedByQuotes.id = vtiger_crmentity.modifiedby"; 2460 } 2461 if($this->queryPlanner->requireTable('vtiger_createdbyQuotes')){ 2462 $query .= " left join vtiger_users as vtiger_createdbyQuotes on vtiger_createdbyQuotes.id = vtiger_crmentity.smcreatorid"; 2463 } 2464 if ($this->queryPlanner->requireTable("vtiger_usersRel1")){ 2465 $query .= " left join vtiger_users as vtiger_usersRel1 on vtiger_usersRel1.id = vtiger_quotes.inventorymanager"; 2466 } 2467 if ($this->queryPlanner->requireTable("vtiger_potentialRelQuotes")){ 2468 $query .= " left join vtiger_potential as vtiger_potentialRelQuotes on vtiger_potentialRelQuotes.potentialid = vtiger_quotes.potentialid"; 2469 } 2470 if ($this->queryPlanner->requireTable("vtiger_contactdetailsQuotes")){ 2471 $query .= " left join vtiger_contactdetails as vtiger_contactdetailsQuotes on vtiger_contactdetailsQuotes.contactid = vtiger_quotes.contactid"; 2472 } 2473 if ($this->queryPlanner->requireTable("vtiger_accountQuotes")){ 2474 $query .= " left join vtiger_account as vtiger_accountQuotes on vtiger_accountQuotes.accountid = vtiger_quotes.accountid"; 2475 } 2476 2477 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2478 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2479 " where vtiger_crmentity.deleted=0"; 2480 } 2481 2482 else if($module == "PurchaseOrder") 2483 { 2484 2485 $matrix = $this->queryPlanner->newDependencyMatrix(); 2486 2487 $matrix->setDependency('vtiger_inventoryproductrelPurchaseOrder',array('vtiger_productsPurchaseOrder','vtiger_servicePurchaseOrder')); 2488 2489 $query = "from vtiger_purchaseorder 2490 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_purchaseorder.purchaseorderid"; 2491 2492 if ($this->queryPlanner->requireTable("vtiger_pobillads")){ 2493 $query .= " inner join vtiger_pobillads on vtiger_purchaseorder.purchaseorderid=vtiger_pobillads.pobilladdressid"; 2494 } 2495 if ($this->queryPlanner->requireTable("vtiger_poshipads")){ 2496 $query .= " inner join vtiger_poshipads on vtiger_purchaseorder.purchaseorderid=vtiger_poshipads.poshipaddressid"; 2497 } 2498 if ($this->queryPlanner->requireTable("vtiger_currency_info$module")){ 2499 $query .= " left join vtiger_currency_info as vtiger_currency_info$module on vtiger_currency_info$module.id = vtiger_purchaseorder.currency_id"; 2500 } 2501 if($type !== 'COLUMNSTOTOTAL' || $this->lineItemFieldsInCalculation == true) { 2502 if ($this->queryPlanner->requireTable("vtiger_inventoryproductrelPurchaseOrder",$matrix)){ 2503 $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelPurchaseOrder on vtiger_purchaseorder.purchaseorderid = vtiger_inventoryproductrelPurchaseOrder.id"; 2504 } 2505 if ($this->queryPlanner->requireTable("vtiger_productsPurchaseOrder")){ 2506 $query .= " left join vtiger_products as vtiger_productsPurchaseOrder on vtiger_productsPurchaseOrder.productid = vtiger_inventoryproductrelPurchaseOrder.productid"; 2507 } 2508 if ($this->queryPlanner->requireTable("vtiger_servicePurchaseOrder")){ 2509 $query .= " left join vtiger_service as vtiger_servicePurchaseOrder on vtiger_servicePurchaseOrder.serviceid = vtiger_inventoryproductrelPurchaseOrder.productid"; 2510 } 2511 } 2512 if ($this->queryPlanner->requireTable("vtiger_purchaseordercf")){ 2513 $query .= " left join vtiger_purchaseordercf on vtiger_purchaseorder.purchaseorderid = vtiger_purchaseordercf.purchaseorderid"; 2514 } 2515 if ($this->queryPlanner->requireTable("vtiger_groupsPurchaseOrder")){ 2516 $query .= " left join vtiger_groups as vtiger_groupsPurchaseOrder on vtiger_groupsPurchaseOrder.groupid = vtiger_crmentity.smownerid"; 2517 } 2518 if ($this->queryPlanner->requireTable("vtiger_usersPurchaseOrder")){ 2519 $query .= " left join vtiger_users as vtiger_usersPurchaseOrder on vtiger_usersPurchaseOrder.id = vtiger_crmentity.smownerid"; 2520 } 2521 2522 // TODO optimize inclusion of these tables 2523 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2524 $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2525 2526 if ($this->queryPlanner->requireTable("vtiger_lastModifiedByPurchaseOrder")){ 2527 $query .= " left join vtiger_users as vtiger_lastModifiedByPurchaseOrder on vtiger_lastModifiedByPurchaseOrder.id = vtiger_crmentity.modifiedby"; 2528 } 2529 if($this->queryPlanner->requireTable('vtiger_createdbyPurchaseOrder')){ 2530 $query .= " left join vtiger_users as vtiger_createdbyPurchaseOrder on vtiger_createdbyPurchaseOrder.id = vtiger_crmentity.smcreatorid"; 2531 } 2532 if ($this->queryPlanner->requireTable("vtiger_vendorRelPurchaseOrder")){ 2533 $query .= " left join vtiger_vendor as vtiger_vendorRelPurchaseOrder on vtiger_vendorRelPurchaseOrder.vendorid = vtiger_purchaseorder.vendorid"; 2534 } 2535 if ($this->queryPlanner->requireTable("vtiger_contactdetailsPurchaseOrder")){ 2536 $query .= " left join vtiger_contactdetails as vtiger_contactdetailsPurchaseOrder on vtiger_contactdetailsPurchaseOrder.contactid = vtiger_purchaseorder.contactid"; 2537 } 2538 2539 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2540 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2541 " where vtiger_crmentity.deleted=0"; 2542 } 2543 2544 else if($module == "Invoice") 2545 { 2546 $matrix = $this->queryPlanner->newDependencyMatrix(); 2547 2548 $matrix->setDependency('vtiger_inventoryproductrelInvoice',array('vtiger_productsInvoice','vtiger_serviceInvoice')); 2549 2550 $query = "from vtiger_invoice 2551 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_invoice.invoiceid"; 2552 2553 if ($this->queryPlanner->requireTable("vtiger_invoicebillads")){ 2554 $query .=" inner join vtiger_invoicebillads on vtiger_invoice.invoiceid=vtiger_invoicebillads.invoicebilladdressid"; 2555 } 2556 if ($this->queryPlanner->requireTable("vtiger_invoiceshipads")){ 2557 $query .=" inner join vtiger_invoiceshipads on vtiger_invoice.invoiceid=vtiger_invoiceshipads.invoiceshipaddressid"; 2558 } 2559 if ($this->queryPlanner->requireTable("vtiger_currency_info$module")){ 2560 $query .=" left join vtiger_currency_info as vtiger_currency_info$module on vtiger_currency_info$module.id = vtiger_invoice.currency_id"; 2561 } 2562 // lineItemFieldsInCalculation - is used to when line item fields are used in calculations 2563 if($type !== 'COLUMNSTOTOTAL' || $this->lineItemFieldsInCalculation == true) { 2564 // should be present on when line item fields are selected for calculation 2565 if ($this->queryPlanner->requireTable("vtiger_inventoryproductrelInvoice",$matrix)){ 2566 $query .=" left join vtiger_inventoryproductrel as vtiger_inventoryproductrelInvoice on vtiger_invoice.invoiceid = vtiger_inventoryproductrelInvoice.id"; 2567 } 2568 if ($this->queryPlanner->requireTable("vtiger_productsInvoice")){ 2569 $query .=" left join vtiger_products as vtiger_productsInvoice on vtiger_productsInvoice.productid = vtiger_inventoryproductrelInvoice.productid"; 2570 } 2571 if ($this->queryPlanner->requireTable("vtiger_serviceInvoice")){ 2572 $query .=" left join vtiger_service as vtiger_serviceInvoice on vtiger_serviceInvoice.serviceid = vtiger_inventoryproductrelInvoice.productid"; 2573 } 2574 } 2575 if ($this->queryPlanner->requireTable("vtiger_salesorderInvoice")){ 2576 $query .= " left join vtiger_salesorder as vtiger_salesorderInvoice on vtiger_salesorderInvoice.salesorderid=vtiger_invoice.salesorderid"; 2577 } 2578 if ($this->queryPlanner->requireTable("vtiger_invoicecf")){ 2579 $query .= " left join vtiger_invoicecf on vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid"; 2580 } 2581 if ($this->queryPlanner->requireTable("vtiger_groupsInvoice")){ 2582 $query .= " left join vtiger_groups as vtiger_groupsInvoice on vtiger_groupsInvoice.groupid = vtiger_crmentity.smownerid"; 2583 } 2584 if ($this->queryPlanner->requireTable("vtiger_usersInvoice")){ 2585 $query .= " left join vtiger_users as vtiger_usersInvoice on vtiger_usersInvoice.id = vtiger_crmentity.smownerid"; 2586 } 2587 2588 // TODO optimize inclusion of these tables 2589 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2590 $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2591 2592 if ($this->queryPlanner->requireTable("vtiger_lastModifiedByInvoice")){ 2593 $query .= " left join vtiger_users as vtiger_lastModifiedByInvoice on vtiger_lastModifiedByInvoice.id = vtiger_crmentity.modifiedby"; 2594 } 2595 if($this->queryPlanner->requireTable('vtiger_createdbyInvoice')){ 2596 $query .= " left join vtiger_users as vtiger_createdbyInvoice on vtiger_createdbyInvoice.id = vtiger_crmentity.smcreatorid"; 2597 } 2598 if ($this->queryPlanner->requireTable("vtiger_accountInvoice")){ 2599 $query .= " left join vtiger_account as vtiger_accountInvoice on vtiger_accountInvoice.accountid = vtiger_invoice.accountid"; 2600 } 2601 if ($this->queryPlanner->requireTable("vtiger_contactdetailsInvoice")){ 2602 $query .= " left join vtiger_contactdetails as vtiger_contactdetailsInvoice on vtiger_contactdetailsInvoice.contactid = vtiger_invoice.contactid"; 2603 } 2604 2605 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2606 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2607 " where vtiger_crmentity.deleted=0"; 2608 } 2609 else if($module == "SalesOrder") 2610 { 2611 $matrix = $this->queryPlanner->newDependencyMatrix(); 2612 2613 $matrix->setDependency('vtiger_inventoryproductrelSalesOrder',array('vtiger_productsSalesOrder','vtiger_serviceSalesOrder')); 2614 2615 $query = "from vtiger_salesorder 2616 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid"; 2617 2618 if ($this->queryPlanner->requireTable("vtiger_sobillads")){ 2619 $query .= " inner join vtiger_sobillads on vtiger_salesorder.salesorderid=vtiger_sobillads.sobilladdressid"; 2620 } 2621 if ($this->queryPlanner->requireTable("vtiger_soshipads")){ 2622 $query .= " inner join vtiger_soshipads on vtiger_salesorder.salesorderid=vtiger_soshipads.soshipaddressid"; 2623 } 2624 if ($this->queryPlanner->requireTable("vtiger_currency_info$module")){ 2625 $query .= " left join vtiger_currency_info as vtiger_currency_info$module on vtiger_currency_info$module.id = vtiger_salesorder.currency_id"; 2626 } 2627 if($type !== 'COLUMNSTOTOTAL' || $this->lineItemFieldsInCalculation == true) { 2628 if ($this->queryPlanner->requireTable("vtiger_inventoryproductrelSalesOrder",$matrix)){ 2629 $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelSalesOrder on vtiger_salesorder.salesorderid = vtiger_inventoryproductrelSalesOrder.id"; 2630 } 2631 if ($this->queryPlanner->requireTable("vtiger_productsSalesOrder")){ 2632 $query .= " left join vtiger_products as vtiger_productsSalesOrder on vtiger_productsSalesOrder.productid = vtiger_inventoryproductrelSalesOrder.productid"; 2633 } 2634 if ($this->queryPlanner->requireTable("vtiger_serviceSalesOrder")){ 2635 $query .= " left join vtiger_service as vtiger_serviceSalesOrder on vtiger_serviceSalesOrder.serviceid = vtiger_inventoryproductrelSalesOrder.productid"; 2636 } 2637 } 2638 if ($this->queryPlanner->requireTable("vtiger_salesordercf")){ 2639 $query .=" left join vtiger_salesordercf on vtiger_salesorder.salesorderid = vtiger_salesordercf.salesorderid"; 2640 } 2641 if ($this->queryPlanner->requireTable("vtiger_contactdetailsSalesOrder")){ 2642 $query .= " left join vtiger_contactdetails as vtiger_contactdetailsSalesOrder on vtiger_contactdetailsSalesOrder.contactid = vtiger_salesorder.contactid"; 2643 } 2644 if ($this->queryPlanner->requireTable("vtiger_quotesSalesOrder")){ 2645 $query .= " left join vtiger_quotes as vtiger_quotesSalesOrder on vtiger_quotesSalesOrder.quoteid = vtiger_salesorder.quoteid"; 2646 } 2647 if ($this->queryPlanner->requireTable("vtiger_accountSalesOrder")){ 2648 $query .= " left join vtiger_account as vtiger_accountSalesOrder on vtiger_accountSalesOrder.accountid = vtiger_salesorder.accountid"; 2649 } 2650 if ($this->queryPlanner->requireTable("vtiger_potentialRelSalesOrder")){ 2651 $query .= " left join vtiger_potential as vtiger_potentialRelSalesOrder on vtiger_potentialRelSalesOrder.potentialid = vtiger_salesorder.potentialid"; 2652 } 2653 if ($this->queryPlanner->requireTable("vtiger_invoice_recurring_info")){ 2654 $query .= " left join vtiger_invoice_recurring_info on vtiger_invoice_recurring_info.salesorderid = vtiger_salesorder.salesorderid"; 2655 } 2656 if ($this->queryPlanner->requireTable("vtiger_groupsSalesOrder")){ 2657 $query .= " left join vtiger_groups as vtiger_groupsSalesOrder on vtiger_groupsSalesOrder.groupid = vtiger_crmentity.smownerid"; 2658 } 2659 if ($this->queryPlanner->requireTable("vtiger_usersSalesOrder")){ 2660 $query .= " left join vtiger_users as vtiger_usersSalesOrder on vtiger_usersSalesOrder.id = vtiger_crmentity.smownerid"; 2661 } 2662 2663 // TODO optimize inclusion of these tables 2664 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2665 $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2666 2667 if ($this->queryPlanner->requireTable("vtiger_lastModifiedBySalesOrder")){ 2668 $query .= " left join vtiger_users as vtiger_lastModifiedBySalesOrder on vtiger_lastModifiedBySalesOrder.id = vtiger_crmentity.modifiedby"; 2669 } 2670 if($this->queryPlanner->requireTable('vtiger_createdbySalesOrder')){ 2671 $query .= " left join vtiger_users as vtiger_createdbySalesOrder on vtiger_createdbySalesOrder.id = vtiger_crmentity.smcreatorid"; 2672 } 2673 2674 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2675 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2676 " where vtiger_crmentity.deleted=0"; 2677 } 2678 else if($module == "Campaigns") 2679 { 2680 $query = "from vtiger_campaign 2681 inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_campaign.campaignid"; 2682 if ($this->queryPlanner->requireTable("vtiger_campaignscf")){ 2683 $query .= " inner join vtiger_campaignscf as vtiger_campaignscf on vtiger_campaignscf.campaignid=vtiger_campaign.campaignid"; 2684 } 2685 if ($this->queryPlanner->requireTable("vtiger_productsCampaigns")){ 2686 $query .= " left join vtiger_products as vtiger_productsCampaigns on vtiger_productsCampaigns.productid = vtiger_campaign.product_id"; 2687 } 2688 if ($this->queryPlanner->requireTable("vtiger_groupsCampaigns")){ 2689 $query .= " left join vtiger_groups as vtiger_groupsCampaigns on vtiger_groupsCampaigns.groupid = vtiger_crmentity.smownerid"; 2690 } 2691 if ($this->queryPlanner->requireTable("vtiger_usersCampaigns")){ 2692 $query .= " left join vtiger_users as vtiger_usersCampaigns on vtiger_usersCampaigns.id = vtiger_crmentity.smownerid"; 2693 } 2694 2695 // TODO optimize inclusion of these tables 2696 $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2697 $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; 2698 2699 if ($this->queryPlanner->requireTable("vtiger_lastModifiedBy$module")){ 2700 $query .= " left join vtiger_users as vtiger_lastModifiedBy".$module." on vtiger_lastModifiedBy".$module.".id = vtiger_crmentity.modifiedby"; 2701 } 2702 if($this->queryPlanner->requireTable("vtiger_createdby$module")){ 2703 $query .= " left join vtiger_users as vtiger_createdby$module on vtiger_createdby$module.id = vtiger_crmentity.smcreatorid"; 2704 } 2705 2706 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2707 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2708 " where vtiger_crmentity.deleted=0"; 2709 } 2710 else if($module == "Emails") { 2711 $query = "from vtiger_activity 2712 INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid AND vtiger_activity.activitytype = 'Emails'"; 2713 2714 if ($this->queryPlanner->requireTable("vtiger_email_track")){ 2715 $query .= " LEFT JOIN vtiger_email_track ON vtiger_email_track.mailid = vtiger_activity.activityid"; 2716 } 2717 if ($this->queryPlanner->requireTable("vtiger_groupsEmails")){ 2718 $query .= " LEFT JOIN vtiger_groups AS vtiger_groupsEmails ON vtiger_groupsEmails.groupid = vtiger_crmentity.smownerid"; 2719 } 2720 if ($this->queryPlanner->requireTable("vtiger_usersEmails")){ 2721 $query .= " LEFT JOIN vtiger_users AS vtiger_usersEmails ON vtiger_usersEmails.id = vtiger_crmentity.smownerid"; 2722 } 2723 2724 // TODO optimize inclusion of these tables 2725 $query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; 2726 $query .= " LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; 2727 2728 if ($this->queryPlanner->requireTable("vtiger_lastModifiedBy$module")){ 2729 $query .= " LEFT JOIN vtiger_users AS vtiger_lastModifiedBy".$module." ON vtiger_lastModifiedBy".$module.".id = vtiger_crmentity.modifiedby"; 2730 } 2731 if($this->queryPlanner->requireTable("vtiger_createdby$module")){ 2732 $query .= " left join vtiger_users as vtiger_createdby$module on vtiger_createdby$module.id = vtiger_crmentity.smcreatorid"; 2733 } 2734 2735 $query .= " ".$this->getRelatedModulesQuery($module,$this->secondarymodule). 2736 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2737 " WHERE vtiger_crmentity.deleted = 0"; 2738 2739 } 2740 else { 2741 if($module!=''){ 2742 $focus = CRMEntity::getInstance($module); 2743 $query = $focus->generateReportsQuery($module, $this->queryPlanner). 2744 $this->getRelatedModulesQuery($module,$this->secondarymodule). 2745 getNonAdminAccessControlQuery($this->primarymodule,$current_user). 2746 " WHERE vtiger_crmentity.deleted=0"; 2747 } 2748 } 2749 $log->info("ReportRun :: Successfully returned getReportsQuery".$module); 2750 2751 return $query; 2752 } 2753 2754 2755 /** function to get query for the given reportid,filterlist,type 2756 * @ param $reportid : Type integer 2757 * @ param $filtersql : Type Array 2758 * @ param $module : Type String 2759 * this returns join query for the report 2760 */ 2761 2762 function sGetSQLforReport($reportid,$filtersql,$type='',$chartReport=false,$startLimit=false,$endLimit=false) 2763 { 2764 global $log; 2765 2766 $columnlist = $this->getQueryColumnsList($reportid,$type); 2767 $groupslist = $this->getGroupingList($reportid); 2768 $groupTimeList = $this->getGroupByTimeList($reportid); 2769 $stdfilterlist = $this->getStdFilterList($reportid); 2770 $columnstotallist = $this->getColumnsTotal($reportid); 2771 $advfiltersql = $this->getAdvFilterSql($reportid); 2772 2773 $this->totallist = $columnstotallist; 2774 global $current_user; 2775 //Fix for ticket #4915. 2776 $selectlist = $columnlist; 2777 //columns list 2778 if(isset($selectlist)) 2779 { 2780 $selectedcolumns = implode(", ",$selectlist); 2781 if($chartReport == true){ 2782 $selectedcolumns .= ", count(*) AS 'groupby_count'"; 2783 } 2784 } 2785 //groups list 2786 if(isset($groupslist)) 2787 { 2788 $groupsquery = implode(", ",$groupslist); 2789 } 2790 if(isset($groupTimeList)){ 2791 $groupTimeQuery = implode(", ",$groupTimeList); 2792 } 2793 2794 //standard list 2795 if(isset($stdfilterlist)) 2796 { 2797 $stdfiltersql = implode(", ",$stdfilterlist); 2798 } 2799 //columns to total list 2800 if(isset($columnstotallist)) 2801 { 2802 $columnstotalsql = implode(", ",$columnstotallist); 2803 } 2804 if($stdfiltersql != "") 2805 { 2806 $wheresql = " and ".$stdfiltersql; 2807 } 2808 2809 if(isset($filtersql) && $filtersql !== false && $filtersql != '') { 2810 $advfiltersql = $filtersql; 2811 } 2812 if($advfiltersql != "") { 2813 $wheresql .= " and ".$advfiltersql; 2814 } 2815 2816 $reportquery = $this->getReportsQuery($this->primarymodule, $type); 2817 2818 // If we don't have access to any columns, let us select one column and limit result to shown we have not results 2819 // Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad 2820 $allColumnsRestricted = false; 2821 2822 if($type == 'COLUMNSTOTOTAL') 2823 { 2824 if($columnstotalsql != '') 2825 { 2826 $reportquery = "select ".$columnstotalsql." ".$reportquery." ".$wheresql; 2827 } 2828 }else 2829 { 2830 if($selectedcolumns == '') { 2831 // Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad 2832 2833 $selectedcolumns = "''"; // "''" to get blank column name 2834 $allColumnsRestricted = true; 2835 } 2836 $reportquery = "select DISTINCT ".$selectedcolumns." ".$reportquery." ".$wheresql; 2837 } 2838 $reportquery = listQueryNonAdminChange($reportquery, $this->primarymodule); 2839 2840 if(trim($groupsquery) != "" && $type !== 'COLUMNSTOTOTAL') 2841 { 2842 if($chartReport == true){ 2843 $reportquery .= "group by ".$this->GetFirstSortByField($reportid); 2844 }else{ 2845 $reportquery .= " order by ".$groupsquery; 2846 } 2847 } 2848 2849 // Prasad: No columns selected so limit the number of rows directly. 2850 if($allColumnsRestricted) { 2851 $reportquery .= " limit 0"; 2852 } else if($startLimit !== false && $endLimit !== false) { 2853 $reportquery .= " LIMIT $startLimit, $endLimit"; 2854 } 2855 2856 preg_match('/&/', $reportquery, $matches); 2857 if(!empty($matches)){ 2858 $report=str_replace('&', '&', $reportquery); 2859 $reportquery = $this->replaceSpecialChar($report); 2860 } 2861 $log->info("ReportRun :: Successfully returned sGetSQLforReport".$reportid); 2862 2863 $this->queryPlanner->initializeTempTables(); 2864 2865 return $reportquery; 2866 2867 } 2868 2869 /** function to get the report output in HTML,PDF,TOTAL,PRINT,PRINTTOTAL formats depends on the argument $outputformat 2870 * @ param $outputformat : Type String (valid parameters HTML,PDF,TOTAL,PRINT,PRINT_TOTAL) 2871 * @ param $filtersql : Type String 2872 * This returns HTML Report if $outputformat is HTML 2873 * Array for PDF if $outputformat is PDF 2874 * HTML strings for TOTAL if $outputformat is TOTAL 2875 * Array for PRINT if $outputformat is PRINT 2876 * HTML strings for TOTAL fields if $outputformat is PRINTTOTAL 2877 * HTML strings for 2878 */ 2879 2880 // Performance Optimization: Added parameter directOutput to avoid building big-string! 2881 function GenerateReport($outputformat,$filtersql, $directOutput=false, $startLimit=false, $endLimit=false) 2882 { 2883 global $adb,$current_user,$php_max_execution_time; 2884 global $modules,$app_strings; 2885 global $mod_strings,$current_language; 2886 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 2887 $modules_selected = array(); 2888 $modules_selected[] = $this->primarymodule; 2889 if(!empty($this->secondarymodule)){ 2890 $sec_modules = split(":",$this->secondarymodule); 2891 for($i=0;$i<count($sec_modules);$i++){ 2892 $modules_selected[] = $sec_modules[$i]; 2893 } 2894 } 2895 2896 // Update Reference fields list list 2897 $referencefieldres = $adb->pquery("SELECT tabid, fieldlabel, uitype from vtiger_field WHERE uitype in (10,101)", array()); 2898 if($referencefieldres) { 2899 foreach($referencefieldres as $referencefieldrow) { 2900 $uiType = $referencefieldrow['uitype']; 2901 $modprefixedlabel = getTabModuleName($referencefieldrow['tabid']).' '.$referencefieldrow['fieldlabel']; 2902 $modprefixedlabel = str_replace(' ','_',$modprefixedlabel); 2903 2904 if($uiType == 10 && !in_array($modprefixedlabel, $this->ui10_fields)) { 2905 $this->ui10_fields[] = $modprefixedlabel; 2906 } elseif($uiType == 101 && !in_array($modprefixedlabel, $this->ui101_fields)) { 2907 $this->ui101_fields[] = $modprefixedlabel; 2908 } 2909 } 2910 } 2911 2912 if($outputformat == "HTML") 2913 { 2914 $sSQL = $this->sGetSQLforReport($this->reportid,$filtersql,$outputformat,false,$startLimit,$endLimit); 2915 $sSQL .= " LIMIT 0, " . (self::$HTMLVIEW_MAX_ROWS+1); // Pull a record more than limit 2916 2917 $result = $adb->query($sSQL); 2918 $error_msg = $adb->database->ErrorMsg(); 2919 if(!$result && $error_msg!=''){ 2920 // Performance Optimization: If direct output is requried 2921 if($directOutput) { 2922 echo getTranslatedString('LBL_REPORT_GENERATION_FAILED', $currentModule) . "<br>" . $error_msg; 2923 $error_msg = false; 2924 } 2925 // END 2926 return $error_msg; 2927 } 2928 2929 // Performance Optimization: If direct output is required 2930 if($directOutput) { 2931 echo '<table cellpadding="5" cellspacing="0" align="center" class="rptTable"><tr>'; 2932 } 2933 // END 2934 2935 if($is_admin==false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) 2936 $picklistarray = $this->getAccessPickListValues(); 2937 if($result) 2938 { 2939 $y=$adb->num_fields($result); 2940 $arrayHeaders = Array(); 2941 for ($x=0; $x<$y; $x++) 2942 { 2943 $fld = $adb->field_name($result, $x); 2944 if(in_array($this->getLstringforReportHeaders($fld->name), $arrayHeaders)) 2945 { 2946 $headerLabel = str_replace("_"," ",$fld->name); 2947 $arrayHeaders[] = $headerLabel; 2948 } 2949 else 2950 { 2951 $headerLabel = str_replace($modules," ",$this->getLstringforReportHeaders($fld->name)); 2952 $headerLabel = str_replace("_"," ",$this->getLstringforReportHeaders($fld->name)); 2953 $arrayHeaders[] = $headerLabel; 2954 } 2955 /*STRING TRANSLATION starts */ 2956 $mod_name = split(' ',$headerLabel,2); 2957 $moduleLabel =''; 2958 if(in_array($mod_name[0],$modules_selected)){ 2959 $moduleLabel = getTranslatedString($mod_name[0],$mod_name[0]); 2960 } 2961 2962 if(!empty($this->secondarymodule)){ 2963 if($moduleLabel!=''){ 2964 $headerLabel_tmp = $moduleLabel." ".getTranslatedString($mod_name[1],$mod_name[0]); 2965 } else { 2966 $headerLabel_tmp = getTranslatedString($mod_name[0]." ".$mod_name[1]); 2967 } 2968 } else { 2969 if($moduleLabel!=''){ 2970 $headerLabel_tmp = getTranslatedString($mod_name[1],$mod_name[0]); 2971 } else { 2972 $headerLabel_tmp = getTranslatedString($mod_name[0]." ".$mod_name[1]); 2973 } 2974 } 2975 if($headerLabel == $headerLabel_tmp) $headerLabel = getTranslatedString($headerLabel_tmp); 2976 else $headerLabel = $headerLabel_tmp; 2977 /*STRING TRANSLATION ends */ 2978 $header .= "<td class='rptCellLabel'>".$headerLabel."</td>"; 2979 2980 // Performance Optimization: If direct output is required 2981 if($directOutput) { 2982 echo $header; 2983 $header = ''; 2984 } 2985 // END 2986 } 2987 2988 // Performance Optimization: If direct output is required 2989 if($directOutput) { 2990 echo '</tr><tr>'; 2991 } 2992 // END 2993 2994 $noofrows = $adb->num_rows($result); 2995 $custom_field_values = $adb->fetch_array($result); 2996 $groupslist = $this->getGroupingList($this->reportid); 2997 2998 $column_definitions = $adb->getFieldsDefinition($result); 2999 3000 do 3001 { 3002 $arraylists = Array(); 3003 if(count($groupslist) == 1) 3004 { 3005 $newvalue = $custom_field_values[0]; 3006 }elseif(count($groupslist) == 2) 3007 { 3008 $newvalue = $custom_field_values[0]; 3009 $snewvalue = $custom_field_values[1]; 3010 }elseif(count($groupslist) == 3) 3011 { 3012 $newvalue = $custom_field_values[0]; 3013 $snewvalue = $custom_field_values[1]; 3014 $tnewvalue = $custom_field_values[2]; 3015 } 3016 if($newvalue == "") $newvalue = "-"; 3017 3018 if($snewvalue == "") $snewvalue = "-"; 3019 3020 if($tnewvalue == "") $tnewvalue = "-"; 3021 3022 $valtemplate .= "<tr>"; 3023 3024 // Performance Optimization 3025 if($directOutput) { 3026 echo $valtemplate; 3027 $valtemplate = ''; 3028 } 3029 // END 3030 3031 for ($i=0; $i<$y; $i++) 3032 { 3033 $fld = $adb->field_name($result, $i); 3034 $fld_type = $column_definitions[$i]->type; 3035 $fieldvalue = getReportFieldValue($this, $picklistarray, $fld, 3036 $custom_field_values, $i); 3037 3038 //check for Roll based pick list 3039 $temp_val= $fld->name; 3040 3041 if($fieldvalue == "" ) 3042 { 3043 $fieldvalue = "-"; 3044 } 3045 else if($fld->name == $this->primarymodule.'_LBL_ACTION' && $fieldvalue != '-') 3046 { 3047 $fieldvalue = "<a href='index.php?module={$this->primarymodule}&action=DetailView&record={$fieldvalue}' target='_blank'>".getTranslatedString('LBL_VIEW_DETAILS', 'Reports')."</a>"; 3048 } 3049 3050 if(($lastvalue == $fieldvalue) && $this->reporttype == "summary") 3051 { 3052 if($this->reporttype == "summary") 3053 { 3054 $valtemplate .= "<td class='rptEmptyGrp'> </td>"; 3055 }else 3056 { 3057 $valtemplate .= "<td class='rptData'>".$fieldvalue."</td>"; 3058 } 3059 }else if(($secondvalue === $fieldvalue) && $this->reporttype == "summary") 3060 { 3061 if($lastvalue === $newvalue) 3062 { 3063 $valtemplate .= "<td class='rptEmptyGrp'> </td>"; 3064 }else 3065 { 3066 $valtemplate .= "<td class='rptGrpHead'>".$fieldvalue."</td>"; 3067 } 3068 } 3069 else if(($thirdvalue === $fieldvalue) && $this->reporttype == "summary") 3070 { 3071 if($secondvalue === $snewvalue) 3072 { 3073 $valtemplate .= "<td class='rptEmptyGrp'> </td>"; 3074 }else 3075 { 3076 $valtemplate .= "<td class='rptGrpHead'>".$fieldvalue."</td>"; 3077 } 3078 } 3079 else 3080 { 3081 if($this->reporttype == "tabular") 3082 { 3083 $valtemplate .= "<td class='rptData'>".$fieldvalue."</td>"; 3084 }else 3085 { 3086 $valtemplate .= "<td class='rptGrpHead'>".$fieldvalue."</td>"; 3087 } 3088 } 3089 3090 // Performance Optimization: If direct output is required 3091 if($directOutput) { 3092 echo $valtemplate; 3093 $valtemplate = ''; 3094 } 3095 // END 3096 } 3097 3098 $valtemplate .= "</tr>"; 3099 3100 // Performance Optimization: If direct output is required 3101 if($directOutput) { 3102 echo $valtemplate; 3103 $valtemplate = ''; 3104 } 3105 // END 3106 3107 $lastvalue = $newvalue; 3108 $secondvalue = $snewvalue; 3109 $thirdvalue = $tnewvalue; 3110 $arr_val[] = $arraylists; 3111 set_time_limit($php_max_execution_time); 3112 }while($custom_field_values = $adb->fetch_array($result)); 3113 3114 // Performance Optimization: Provide feedback on export option if required 3115 // NOTE: We should make sure to pull at-least 1 row more than max-limit for this to work. 3116 if ($noofrows > self::$HTMLVIEW_MAX_ROWS) { 3117 // Performance Optimization: Output directly 3118 if ($directOutput) { 3119 echo '</tr></table><br><table width="100%" cellpading="0" cellspacing="0"><tr>'; 3120 echo sprintf('<td colspan="%s" align="right"><span class="genHeaderGray">%s</span></td>', 3121 $y, getTranslatedString('Only')." ".self::$HTMLVIEW_MAX_ROWS . 3122 "+ " . getTranslatedString('records found') . ". " . getTranslatedString('Export to') . " <a href=\"javascript:;\" onclick=\"goToURL(CrearEnlace('ReportsAjax&file=CreateCSV',{$this->reportid}));\"><img style='vertical-align:text-top' src='themes/images/csv-file.png'></a> /" . 3123 " <a href=\"javascript:;\" onclick=\"goToURL(CrearEnlace('CreateXL',{$this->reportid}));\"><img style='vertical-align:text-top' src='themes/images/xls-file.jpg'></a>" 3124 ); 3125 3126 } else { 3127 $valtemplate .= '</tr></table><br><table width="100%" cellpading="0" cellspacing="0"><tr>'; 3128 $valtemplate .= sprintf('<td colspan="%s" align="right"><span class="genHeaderGray">%s</span></td>', 3129 $y, getTranslatedString('Only')." ".self::$HTMLVIEW_MAX_ROWS . 3130 " " . getTranslatedString('records found') . ". " . getTranslatedString('Export to') . " <a href=\"javascript:;\" onclick=\"goToURL(CrearEnlace('ReportsAjax&file=CreateCSV',{$this->reportid}));\"><img style='vertical-align:text-top' src='themes/images/csv-file.png'></a> /" . 3131 " <a href=\"javascript:;\" onclick=\"goToURL(CrearEnlace('CreateXL',{$this->reportid}));\"><img style='vertical-align:text-top' src='themes/images/xls-file.jpg'></a>" 3132 ); 3133 } 3134 } 3135 3136 3137 // Performance Optimization 3138 if($directOutput) { 3139 3140 $totalDisplayString = $noofrows; 3141 if ($noofrows > self::$HTMLVIEW_MAX_ROWS) { 3142 $totalDisplayString = self::$HTMLVIEW_MAX_ROWS . "+"; 3143 } 3144 3145 echo "</tr></table>"; 3146 echo "<script type='text/javascript' id='__reportrun_directoutput_recordcount_script'> 3147 if($('_reportrun_total')) $('_reportrun_total').innerHTML='$totalDisplayString';</script>"; 3148 } else { 3149 3150 $sHTML ='<table cellpadding="5" cellspacing="0" align="center" class="rptTable"> 3151 <tr>'. 3152 $header 3153 .'<!-- BEGIN values --> 3154 <tr>'. 3155 $valtemplate 3156 .'</tr> 3157 </table>'; 3158 } 3159 //<<<<<<<<construct HTML>>>>>>>>>>>> 3160 $return_data[] = $sHTML; 3161 $return_data[] = $noofrows; 3162 $return_data[] = $sSQL; 3163 return $return_data; 3164 } 3165 }elseif($outputformat == "PDF") 3166 { 3167 3168 $sSQL = $this->sGetSQLforReport($this->reportid,$filtersql,$outputformat,false,$startLimit,$endLimit); 3169 $result = $adb->pquery($sSQL,array()); 3170 if($is_admin==false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) 3171 $picklistarray = $this->getAccessPickListValues(); 3172 3173 if($result) 3174 { 3175 $y=$adb->num_fields($result); 3176 $noofrows = $adb->num_rows($result); 3177 $custom_field_values = $adb->fetch_array($result); 3178 $column_definitions = $adb->getFieldsDefinition($result); 3179 3180 do 3181 { 3182 $arraylists = Array(); 3183 for ($i=0; $i<$y; $i++) 3184 { 3185 $fld = $adb->field_name($result, $i); 3186 $fld_type = $column_definitions[$i]->type; 3187 list($module, $fieldLabel) = explode('_', $fld->name, 2); 3188 $fieldInfo = getFieldByReportLabel($module, $fieldLabel); 3189 $fieldType = null; 3190 if(!empty($fieldInfo)) { 3191 $field = WebserviceField::fromArray($adb, $fieldInfo); 3192 $fieldType = $field->getFieldDataType(); 3193 } 3194 if(!empty($fieldInfo)) { 3195 $translatedLabel = getTranslatedString($field->getFieldLabelKey(), 3196 $module); 3197 } else { 3198 $translatedLabel = getTranslatedString($fieldLabel, $module); 3199 $translatedLabel = str_replace("_", " ", $translatedLabel); 3200 } 3201 /*STRING TRANSLATION starts */ 3202 $moduleLabel =''; 3203 if(in_array($module,$modules_selected)) 3204 $moduleLabel = getTranslatedString($module,$module); 3205 3206 if(empty($translatedLabel)) { 3207 $translatedLabel = getTranslatedString(str_replace('_', " ", 3208 $fld->name), $module); 3209 } 3210 $headerLabel = $translatedLabel; 3211 if(!empty($this->secondarymodule)) { 3212 if($moduleLabel != '') { 3213 $headerLabel = $moduleLabel." ". $translatedLabel; 3214 } 3215 } 3216 // Check for role based pick list 3217 $temp_val= $fld->name; 3218 $fieldvalue = getReportFieldValue($this, $picklistarray, $fld, 3219 $custom_field_values, $i); 3220 3221 if($fld->name == $this->primarymodule.'_LBL_ACTION' && $fieldvalue != '-') { 3222 $fieldvalue = "<a href='index.php?module={$this->primarymodule}&view=Detail&record={$fieldvalue}' target='_blank'>".getTranslatedString('LBL_VIEW_DETAILS', 'Reports')."</a>"; 3223 } 3224 3225 $arraylists[$headerLabel] = $fieldvalue; 3226 } 3227 $arr_val[] = $arraylists; 3228 set_time_limit($php_max_execution_time); 3229 }while($custom_field_values = $adb->fetch_array($result)); 3230 3231 $data['data'] = $arr_val; 3232 $data['count'] = $noofrows; 3233 return $data; 3234 } 3235 }elseif($outputformat == "TOTALXLS") 3236 { 3237 $escapedchars = Array('_SUM','_AVG','_MIN','_MAX'); 3238 $totalpdf=array(); 3239 $sSQL = $this->sGetSQLforReport($this->reportid,$filtersql,"COLUMNSTOTOTAL"); 3240 if(isset($this->totallist)) 3241 { 3242 if($sSQL != "") 3243 { 3244 $result = $adb->query($sSQL); 3245 $y=$adb->num_fields($result); 3246 $custom_field_values = $adb->fetch_array($result); 3247 3248 foreach($this->totallist as $key=>$value) 3249 { 3250 $fieldlist = explode(":",$key); 3251 $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid, uitype as uitype from vtiger_field where tablename = ? and columnname=?",array($fieldlist[1],$fieldlist[2])); 3252 if($adb->num_rows($mod_query)>0){ 3253 $module_name = getTabModuleName($adb->query_result($mod_query,0,'tabid')); 3254 $fieldlabel = trim(str_replace($escapedchars," ",$fieldlist[3])); 3255 $fieldlabel = str_replace("_", " ", $fieldlabel); 3256 if($module_name){ 3257 $field = getTranslatedString($module_name,$module_name)." ".getTranslatedString($fieldlabel,$module_name); 3258 } else { 3259 $field = getTranslatedString($fieldlabel); 3260 } 3261 } 3262 // Since there are duplicate entries for this table 3263 if($fieldlist[1] == 'vtiger_inventoryproductrel') { 3264 $module_name = $this->primarymodule; 3265 } 3266 $uitype_arr[str_replace($escapedchars," ",$module_name."_".$fieldlist[3])] = $adb->query_result($mod_query,0,"uitype"); 3267 $totclmnflds[str_replace($escapedchars," ",$module_name."_".$fieldlist[3])] = $field; 3268 } 3269 for($i =0;$i<$y;$i++) 3270 { 3271 $fld = $adb->field_name($result, $i); 3272 $keyhdr[$fld->name] = $custom_field_values[$i]; 3273 } 3274 3275 $rowcount=0; 3276 foreach($totclmnflds as $key=>$value) 3277 { 3278 $col_header = trim(str_replace($modules," ",$value)); 3279 $fld_name_1 = $this->primarymodule . "_" . trim($value); 3280 $fld_name_2 = $this->secondarymodule . "_" . trim($value); 3281 if($uitype_arr[$key] == 71 || $uitype_arr[$key] == 72 || 3282 in_array($fld_name_1,$this->append_currency_symbol_to_value) || in_array($fld_name_2,$this->append_currency_symbol_to_value)) { 3283 $col_header .= " (".$app_strings['LBL_IN']." ".$current_user->currency_symbol.")"; 3284 $convert_price = true; 3285 } else{ 3286 $convert_price = false; 3287 } 3288 $value = trim($key); 3289 $arraykey = $value.'_SUM'; 3290 if(isset($keyhdr[$arraykey])) 3291 { 3292 if($convert_price) 3293 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3294 else 3295 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3296 $totalpdf[$rowcount][$arraykey] = $conv_value; 3297 }else 3298 { 3299 $totalpdf[$rowcount][$arraykey] = ''; 3300 } 3301 3302 $arraykey = $value.'_AVG'; 3303 if(isset($keyhdr[$arraykey])) 3304 { 3305 if($convert_price) 3306 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3307 else 3308 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3309 $totalpdf[$rowcount][$arraykey] = $conv_value; 3310 }else 3311 { 3312 $totalpdf[$rowcount][$arraykey] = ''; 3313 } 3314 3315 $arraykey = $value.'_MIN'; 3316 if(isset($keyhdr[$arraykey])) 3317 { 3318 if($convert_price) 3319 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3320 else 3321 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3322 $totalpdf[$rowcount][$arraykey] = $conv_value; 3323 }else 3324 { 3325 $totalpdf[$rowcount][$arraykey] = ''; 3326 } 3327 3328 $arraykey = $value.'_MAX'; 3329 if(isset($keyhdr[$arraykey])) 3330 { 3331 if($convert_price) 3332 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3333 else 3334 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3335 $totalpdf[$rowcount][$arraykey] = $conv_value; 3336 }else 3337 { 3338 $totalpdf[$rowcount][$arraykey] = ''; 3339 } 3340 $rowcount++; 3341 } 3342 } 3343 } 3344 return $totalpdf; 3345 }elseif($outputformat == "TOTALHTML") 3346 { 3347 $escapedchars = Array('_SUM','_AVG','_MIN','_MAX'); 3348 $sSQL = $this->sGetSQLforReport($this->reportid,$filtersql,"COLUMNSTOTOTAL"); 3349 3350 static $modulename_cache = array(); 3351 3352 if(isset($this->totallist)) 3353 { 3354 if($sSQL != "") 3355 { 3356 $result = $adb->query($sSQL); 3357 $y=$adb->num_fields($result); 3358 $custom_field_values = $adb->fetch_array($result); 3359 $coltotalhtml .= "<table align='center' width='60%' cellpadding='3' cellspacing='0' border='0' class='rptTable'><tr><td class='rptCellLabel'>".$mod_strings[Totals]."</td><td class='rptCellLabel'>".$mod_strings[SUM]."</td><td class='rptCellLabel'>".$mod_strings[AVG]."</td><td class='rptCellLabel'>".$mod_strings[MIN]."</td><td class='rptCellLabel'>".$mod_strings[MAX]."</td></tr>"; 3360 3361 // Performation Optimization: If Direct output is desired 3362 if($directOutput) { 3363 echo $coltotalhtml; 3364 $coltotalhtml = ''; 3365 } 3366 // END 3367 3368 foreach($this->totallist as $key=>$value) 3369 { 3370 $fieldlist = explode(":",$key); 3371 3372 $module_name = NULL; 3373 $cachekey = $fieldlist[1] . ":" . $fieldlist[2]; 3374 if (!isset($modulename_cache[$cachekey])) { 3375 $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid, uitype as uitype from vtiger_field where tablename = ? and columnname=?",array($fieldlist[1],$fieldlist[2])); 3376 if($adb->num_rows($mod_query)>0){ 3377 $module_name = getTabModuleName($adb->query_result($mod_query,0,'tabid')); 3378 $modulename_cache[$cachekey] = $module_name; 3379 } 3380 } else { 3381 $module_name = $modulename_cache[$cachekey]; 3382 } 3383 if ($module_name) { 3384 $fieldlabel = trim(str_replace($escapedchars," ",$fieldlist[3])); 3385 $fieldlabel = str_replace("_", " ", $fieldlabel); 3386 $field = getTranslatedString($module_name, $module_name)." ".getTranslatedString($fieldlabel,$module_name); 3387 } else { 3388 $field = getTranslatedString($fieldlabel); 3389 } 3390 3391 $uitype_arr[str_replace($escapedchars," ",$module_name."_".$fieldlist[3])] = $adb->query_result($mod_query,0,"uitype"); 3392 $totclmnflds[str_replace($escapedchars," ",$module_name."_".$fieldlist[3])] = $field; 3393 } 3394 for($i =0;$i<$y;$i++) 3395 { 3396 $fld = $adb->field_name($result, $i); 3397 $keyhdr[$fld->name] = $custom_field_values[$i]; 3398 } 3399 3400 foreach($totclmnflds as $key=>$value) 3401 { 3402 $coltotalhtml .= '<tr class="rptGrpHead" valign=top>'; 3403 $col_header = trim(str_replace($modules," ",$value)); 3404 $fld_name_1 = $this->primarymodule . "_" . trim($value); 3405 $fld_name_2 = $this->secondarymodule . "_" . trim($value); 3406 if($uitype_arr[$key]==71 || $uitype_arr[$key] == 72 || 3407 in_array($fld_name_1,$this->append_currency_symbol_to_value) || in_array($fld_name_2,$this->append_currency_symbol_to_value)) { 3408 $col_header .= " (".$app_strings['LBL_IN']." ".$current_user->currency_symbol.")"; 3409 $convert_price = true; 3410 } else{ 3411 $convert_price = false; 3412 } 3413 $coltotalhtml .= '<td class="rptData">'. $col_header .'</td>'; 3414 $value = trim($key); 3415 $arraykey = $value.'_SUM'; 3416 if(isset($keyhdr[$arraykey])) 3417 { 3418 if($convert_price) 3419 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3420 else 3421 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3422 $coltotalhtml .= '<td class="rptTotal">'.$conv_value.'</td>'; 3423 }else 3424 { 3425 $coltotalhtml .= '<td class="rptTotal"> </td>'; 3426 } 3427 3428 $arraykey = $value.'_AVG'; 3429 if(isset($keyhdr[$arraykey])) 3430 { 3431 if($convert_price) 3432 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3433 else 3434 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3435 $coltotalhtml .= '<td class="rptTotal">'.$conv_value.'</td>'; 3436 }else 3437 { 3438 $coltotalhtml .= '<td class="rptTotal"> </td>'; 3439 } 3440 3441 $arraykey = $value.'_MIN'; 3442 if(isset($keyhdr[$arraykey])) 3443 { 3444 if($convert_price) 3445 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3446 else 3447 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3448 $coltotalhtml .= '<td class="rptTotal">'.$conv_value.'</td>'; 3449 }else 3450 { 3451 $coltotalhtml .= '<td class="rptTotal"> </td>'; 3452 } 3453 3454 $arraykey = $value.'_MAX'; 3455 if(isset($keyhdr[$arraykey])) 3456 { 3457 if($convert_price) 3458 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3459 else 3460 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3461 $coltotalhtml .= '<td class="rptTotal">'.$conv_value.'</td>'; 3462 }else 3463 { 3464 $coltotalhtml .= '<td class="rptTotal"> </td>'; 3465 } 3466 3467 $coltotalhtml .= '<tr>'; 3468 3469 // Performation Optimization: If Direct output is desired 3470 if($directOutput) { 3471 echo $coltotalhtml; 3472 $coltotalhtml = ''; 3473 } 3474 // END 3475 } 3476 3477 $coltotalhtml .= "</table>"; 3478 3479 // Performation Optimization: If Direct output is desired 3480 if($directOutput) { 3481 echo $coltotalhtml; 3482 $coltotalhtml = ''; 3483 } 3484 // END 3485 } 3486 } 3487 return $coltotalhtml; 3488 }elseif($outputformat == "PRINT") 3489 { 3490 $sSQL = $this->sGetSQLforReport($this->reportid,$filtersql, $outputformat); 3491 $result = $adb->query($sSQL); 3492 if($is_admin==false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) 3493 $picklistarray = $this->getAccessPickListValues(); 3494 3495 if($result) 3496 { 3497 $y=$adb->num_fields($result); 3498 $arrayHeaders = Array(); 3499 for ($x=0; $x<$y-1; $x++) 3500 { 3501 $fld = $adb->field_name($result, $x); 3502 if(in_array($this->getLstringforReportHeaders($fld->name), $arrayHeaders)) 3503 { 3504 $headerLabel = str_replace("_"," ",$fld->name); 3505 $arrayHeaders[] = $headerLabel; 3506 } 3507 else 3508 { 3509 $headerLabel = str_replace($modules," ",$this->getLstringforReportHeaders($fld->name)); 3510 $headerLabel = str_replace("_"," ",$this->getLstringforReportHeaders($fld->name)); 3511 $arrayHeaders[] = $headerLabel; 3512 } 3513 /*STRING TRANSLATION starts */ 3514 $mod_name = split(' ',$headerLabel,2); 3515 $moduleLabel =''; 3516 if(in_array($mod_name[0],$modules_selected)){ 3517 $moduleLabel = getTranslatedString($mod_name[0],$mod_name[0]); 3518 } 3519 3520 if(!empty($this->secondarymodule)){ 3521 if($moduleLabel!=''){ 3522 $headerLabel_tmp = $moduleLabel." ".getTranslatedString($mod_name[1],$mod_name[0]); 3523 } else { 3524 $headerLabel_tmp = getTranslatedString($mod_name[0]." ".$mod_name[1]); 3525 } 3526 } else { 3527 if($moduleLabel!=''){ 3528 $headerLabel_tmp = getTranslatedString($mod_name[1],$mod_name[0]); 3529 } else { 3530 $headerLabel_tmp = getTranslatedString($mod_name[0]." ".$mod_name[1]); 3531 } 3532 } 3533 if($headerLabel == $headerLabel_tmp) $headerLabel = getTranslatedString($headerLabel_tmp); 3534 else $headerLabel = $headerLabel_tmp; 3535 /*STRING TRANSLATION ends */ 3536 $header .= "<th>".$headerLabel."</th>"; 3537 } 3538 $noofrows = $adb->num_rows($result); 3539 $custom_field_values = $adb->fetch_array($result); 3540 $groupslist = $this->getGroupingList($this->reportid); 3541 3542 $column_definitions = $adb->getFieldsDefinition($result); 3543 3544 do 3545 { 3546 $arraylists = Array(); 3547 if(count($groupslist) == 1) 3548 { 3549 $newvalue = $custom_field_values[0]; 3550 }elseif(count($groupslist) == 2) 3551 { 3552 $newvalue = $custom_field_values[0]; 3553 $snewvalue = $custom_field_values[1]; 3554 }elseif(count($groupslist) == 3) 3555 { 3556 $newvalue = $custom_field_values[0]; 3557 $snewvalue = $custom_field_values[1]; 3558 $tnewvalue = $custom_field_values[2]; 3559 } 3560 3561 if($newvalue == "") $newvalue = "-"; 3562 3563 if($snewvalue == "") $snewvalue = "-"; 3564 3565 if($tnewvalue == "") $tnewvalue = "-"; 3566 3567 $valtemplate .= "<tr>"; 3568 3569 for ($i=0; $i<$y-1; $i++) 3570 { 3571 $fld = $adb->field_name($result, $i); 3572 $fld_type = $column_definitions[$i]->type; 3573 $fieldvalue = getReportFieldValue($this, $picklistarray, $fld, 3574 $custom_field_values, $i); 3575 if(($lastvalue == $fieldvalue) && $this->reporttype == "summary") 3576 { 3577 if($this->reporttype == "summary") 3578 { 3579 $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; 3580 }else 3581 { 3582 $valtemplate .= "<td>".$fieldvalue."</td>"; 3583 } 3584 }else if(($secondvalue == $fieldvalue) && $this->reporttype == "summary") 3585 { 3586 if($lastvalue == $newvalue) 3587 { 3588 $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; 3589 }else 3590 { 3591 $valtemplate .= "<td>".$fieldvalue."</td>"; 3592 } 3593 } 3594 else if(($thirdvalue == $fieldvalue) && $this->reporttype == "summary") 3595 { 3596 if($secondvalue == $snewvalue) 3597 { 3598 $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; 3599 }else 3600 { 3601 $valtemplate .= "<td>".$fieldvalue."</td>"; 3602 } 3603 } 3604 else 3605 { 3606 if($this->reporttype == "tabular") 3607 { 3608 $valtemplate .= "<td>".$fieldvalue."</td>"; 3609 }else 3610 { 3611 $valtemplate .= "<td>".$fieldvalue."</td>"; 3612 } 3613 } 3614 } 3615 $valtemplate .= "</tr>"; 3616 $lastvalue = $newvalue; 3617 $secondvalue = $snewvalue; 3618 $thirdvalue = $tnewvalue; 3619 $arr_val[] = $arraylists; 3620 set_time_limit($php_max_execution_time); 3621 }while($custom_field_values = $adb->fetch_array($result)); 3622 3623 $sHTML = '<tr>'.$header.'</tr>'.$valtemplate; 3624 $return_data[] = $sHTML; 3625 $return_data[] = $noofrows; 3626 return $return_data; 3627 } 3628 }elseif($outputformat == "PRINT_TOTAL") 3629 { 3630 $escapedchars = Array('_SUM','_AVG','_MIN','_MAX'); 3631 $sSQL = $this->sGetSQLforReport($this->reportid,$filtersql,"COLUMNSTOTOTAL"); 3632 if(isset($this->totallist)) 3633 { 3634 if($sSQL != "") 3635 { 3636 $result = $adb->query($sSQL); 3637 $y=$adb->num_fields($result); 3638 $custom_field_values = $adb->fetch_array($result); 3639 3640 $coltotalhtml .= "<br /><table align='center' width='60%' cellpadding='3' cellspacing='0' border='1' class='printReport'><tr><td class='rptCellLabel'>".$mod_strings['Totals']."</td><td><b>".$mod_strings['SUM']."</b></td><td><b>".$mod_strings['AVG']."</b></td><td><b>".$mod_strings['MIN']."</b></td><td><b>".$mod_strings['MAX']."</b></td></tr>"; 3641 3642 // Performation Optimization: If Direct output is desired 3643 if($directOutput) { 3644 echo $coltotalhtml; 3645 $coltotalhtml = ''; 3646 } 3647 // END 3648 3649 foreach($this->totallist as $key=>$value) 3650 { 3651 $fieldlist = explode(":",$key); 3652 $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid, uitype as uitype from vtiger_field where tablename = ? and columnname=?",array($fieldlist[1],$fieldlist[2])); 3653 if($adb->num_rows($mod_query)>0){ 3654 $module_name = getTabModuleName($adb->query_result($mod_query,0,'tabid')); 3655 $fieldlabel = trim(str_replace($escapedchars," ",$fieldlist[3])); 3656 $fieldlabel = str_replace("_", " ", $fieldlabel); 3657 if($module_name){ 3658 $field = getTranslatedString($module_name, $module_name)." ".getTranslatedString($fieldlabel,$module_name); 3659 } else { 3660 $field = getTranslatedString($fieldlabel); 3661 } 3662 } 3663 $uitype_arr[str_replace($escapedchars," ",$module_name."_".$fieldlist[3])] = $adb->query_result($mod_query,0,"uitype"); 3664 $totclmnflds[str_replace($escapedchars," ",$module_name."_".$fieldlist[3])] = $field; 3665 } 3666 3667 for($i =0;$i<$y;$i++) 3668 { 3669 $fld = $adb->field_name($result, $i); 3670 $keyhdr[$fld->name] = $custom_field_values[$i]; 3671 3672 } 3673 foreach($totclmnflds as $key=>$value) 3674 { 3675 $coltotalhtml .= '<tr class="rptGrpHead">'; 3676 $col_header = getTranslatedString(trim(str_replace($modules," ",$value))); 3677 $fld_name_1 = $this->primarymodule . "_" . trim($value); 3678 $fld_name_2 = $this->secondarymodule . "_" . trim($value); 3679 if($uitype_arr[$key]==71 || $uitype_arr[$key] == 72 || 3680 in_array($fld_name_1,$this->append_currency_symbol_to_value) || in_array($fld_name_2,$this->append_currency_symbol_to_value)) { 3681 $col_header .= " (".$app_strings['LBL_IN']." ".$current_user->currency_symbol.")"; 3682 $convert_price = true; 3683 } else{ 3684 $convert_price = false; 3685 } 3686 $coltotalhtml .= '<td class="rptData">'. $col_header .'</td>'; 3687 $value = trim($key); 3688 $arraykey = $value.'_SUM'; 3689 if(isset($keyhdr[$arraykey])) 3690 { 3691 if($convert_price) 3692 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3693 else 3694 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3695 $coltotalhtml .= "<td class='rptTotal'>".$conv_value.'</td>'; 3696 }else 3697 { 3698 $coltotalhtml .= "<td class='rptTotal'> </td>"; 3699 } 3700 3701 $arraykey = $value.'_AVG'; 3702 if(isset($keyhdr[$arraykey])) 3703 { 3704 if($convert_price) 3705 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3706 else 3707 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3708 $coltotalhtml .= "<td class='rptTotal'>".$conv_value.'</td>'; 3709 }else 3710 { 3711 $coltotalhtml .= "<td class='rptTotal'> </td>"; 3712 } 3713 3714 $arraykey = $value.'_MIN'; 3715 if(isset($keyhdr[$arraykey])) 3716 { 3717 if($convert_price) 3718 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3719 else 3720 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3721 $coltotalhtml .= "<td class='rptTotal'>".$conv_value.'</td>'; 3722 }else 3723 { 3724 $coltotalhtml .= "<td class='rptTotal'> </td>"; 3725 } 3726 3727 $arraykey = $value.'_MAX'; 3728 if(isset($keyhdr[$arraykey])) 3729 { 3730 if($convert_price) 3731 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey]); 3732 else 3733 $conv_value = CurrencyField::convertToUserFormat ($keyhdr[$arraykey], null, true); 3734 $coltotalhtml .= "<td class='rptTotal'>".$conv_value.'</td>'; 3735 }else 3736 { 3737 $coltotalhtml .= "<td class='rptTotal'> </td>"; 3738 } 3739 3740 $coltotalhtml .= '</tr>'; 3741 3742 // Performation Optimization: If Direct output is desired 3743 if($directOutput) { 3744 echo $coltotalhtml; 3745 $coltotalhtml = ''; 3746 } 3747 // END 3748 } 3749 3750 $coltotalhtml .= "</table>"; 3751 // Performation Optimization: If Direct output is desired 3752 if($directOutput) { 3753 echo $coltotalhtml; 3754 $coltotalhtml = ''; 3755 } 3756 // END 3757 } 3758 } 3759 return $coltotalhtml; 3760 } 3761 } 3762 3763 //<<<<<<<new>>>>>>>>>> 3764 function getColumnsTotal($reportid) 3765 { 3766 // Have we initialized it already? 3767 if($this->_columnstotallist !== false) { 3768 return $this->_columnstotallist; 3769 } 3770 3771 global $adb; 3772 global $modules; 3773 global $log, $current_user; 3774 3775 static $modulename_cache = array(); 3776 3777 $query = "select * from vtiger_reportmodules where reportmodulesid =?"; 3778 $res = $adb->pquery($query , array($reportid)); 3779 $modrow = $adb->fetch_array($res); 3780 $premod = $modrow["primarymodule"]; 3781 $secmod = $modrow["secondarymodules"]; 3782 $coltotalsql = "select vtiger_reportsummary.* from vtiger_report"; 3783 $coltotalsql .= " inner join vtiger_reportsummary on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid"; 3784 $coltotalsql .= " where vtiger_report.reportid =?"; 3785 3786 $result = $adb->pquery($coltotalsql, array($reportid)); 3787 3788 while($coltotalrow = $adb->fetch_array($result)) 3789 { 3790 $fieldcolname = $coltotalrow["columnname"]; 3791 if($fieldcolname != "none") 3792 { 3793 $fieldlist = explode(":",$fieldcolname); 3794 $field_tablename = $fieldlist[1]; 3795 $field_columnname = $fieldlist[2]; 3796 3797 $cachekey = $field_tablename . ":" . $field_columnname; 3798 if (!isset($modulename_cache[$cachekey])) { 3799 $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid from vtiger_field where tablename = ? and columnname=?",array($fieldlist[1],$fieldlist[2])); 3800 if($adb->num_rows($mod_query)>0){ 3801 $module_name = getTabModuleName($adb->query_result($mod_query,0,'tabid')); 3802 $modulename_cache[$cachekey] = $module_name; 3803 } 3804 } else { 3805 $module_name = $modulename_cache[$cachekey]; 3806 } 3807 3808 $fieldlabel = trim($fieldlist[3]); 3809 if($field_tablename == 'vtiger_inventoryproductrel') { 3810 $field_columnalias = $premod."_".$fieldlist[3]; 3811 } else { 3812 if($module_name){ 3813 $field_columnalias = $module_name."_".$fieldlist[3]; 3814 } else { 3815 $field_columnalias = $module_name."_".$fieldlist[3]; 3816 } 3817 } 3818 3819 //$field_columnalias = $fieldlist[3]; 3820 $field_permitted = false; 3821 if(CheckColumnPermission($field_tablename,$field_columnname,$premod) != "false"){ 3822 $field_permitted = true; 3823 } else { 3824 $mod = split(":",$secmod); 3825 foreach($mod as $key){ 3826 if(CheckColumnPermission($field_tablename,$field_columnname,$key) != "false"){ 3827 $field_permitted=true; 3828 } 3829 } 3830 } 3831 3832 //Calculation fields of "Events" module should show in Calendar related report 3833 $secondaryModules = split(":", $secmod); 3834 if ($field_permitted === false && ($premod === 'Calendar' || in_array('Calendar', $secondaryModules)) && CheckColumnPermission($field_tablename, $field_columnname, "Events") != "false") { 3835 $field_permitted = true; 3836 } 3837 3838 if($field_permitted == true) 3839 { 3840 $field = $this->getColumnsTotalSQL($fieldlist, $premod); 3841 3842 if($fieldlist[4] == 2) 3843 { 3844 $stdfilterlist[$fieldcolname] = "sum($field) '".$field_columnalias."'"; 3845 } 3846 if($fieldlist[4] == 3) 3847 { 3848 //Fixed average calculation issue due to NULL values ie., when we use avg() function, NULL values will be ignored.to avoid this we use (sum/count) to find average. 3849 //$stdfilterlist[$fieldcolname] = "avg(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; 3850 $stdfilterlist[$fieldcolname] = "(sum($field)/count(*)) '".$field_columnalias."'"; 3851 } 3852 if($fieldlist[4] == 4) 3853 { 3854 $stdfilterlist[$fieldcolname] = "min($field) '".$field_columnalias."'"; 3855 } 3856 if($fieldlist[4] == 5) 3857 { 3858 $stdfilterlist[$fieldcolname] = "max($field) '".$field_columnalias."'"; 3859 } 3860 3861 $this->queryPlanner->addTable($field_tablename); 3862 } 3863 } 3864 } 3865 // Save the information 3866 $this->_columnstotallist = $stdfilterlist; 3867 3868 $log->info("ReportRun :: Successfully returned getColumnsTotal".$reportid); 3869 return $stdfilterlist; 3870 } 3871 //<<<<<<new>>>>>>>>> 3872 3873 3874 function getColumnsTotalSQL($fieldlist, $premod) { 3875 // Added condition to support detail report calculations 3876 if($fieldlist[0] == 'cb') { 3877 $field_tablename = $fieldlist[1]; 3878 $field_columnname = $fieldlist[2]; 3879 } else { 3880 $field_tablename = $fieldlist[0]; 3881 $field_columnname = $fieldlist[1]; 3882 } 3883 3884 $field = $field_tablename.".".$field_columnname; 3885 if($field_tablename == 'vtiger_products' && $field_columnname == 'unit_price') { 3886 // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] 3887 $field = " innerProduct.actual_unit_price"; 3888 $this->queryPlanner->addTable("innerProduct"); 3889 } 3890 if($field_tablename == 'vtiger_service' && $field_columnname == 'unit_price') { 3891 // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] 3892 $field = " innerService.actual_unit_price"; 3893 $this->queryPlanner->addTable("innerService"); 3894 3895 } 3896 if(($field_tablename == 'vtiger_invoice' || $field_tablename == 'vtiger_quotes' || $field_tablename == 'vtiger_purchaseorder' || $field_tablename == 'vtiger_salesorder') 3897 && ($field_columnname == 'total' || $field_columnname == 'subtotal' || $field_columnname == 'discount_amount' || $field_columnname == 's_h_amount' 3898 || $field_columnname == 'paid' || $field_columnname == 'balance' || $field_columnname == 'received')) { 3899 $field = " $field_tablename.$field_columnname/$field_tablename.conversion_rate "; 3900 } 3901 3902 if($field_tablename == 'vtiger_inventoryproductrel') { 3903 // Check added so that query planner can prepare query properly for inventory modules 3904 $this->lineItemFieldsInCalculation = true; 3905 $field = $field_tablename.$premod.'.'.$field_columnname; 3906 $itemTableName = 'vtiger_inventoryproductrel'.$premod; 3907 $this->queryPlanner->addTable($itemTableName); 3908 $primaryModuleInstance = CRMEntity::getInstance($premod); 3909 if($field_columnname == 'listprice') { 3910 $field = $field.'/'.$primaryModuleInstance->table_name.'.conversion_rate'; 3911 } else if($field_columnname == 'discount_amount') { 3912 $field = ' CASE WHEN '.$itemTableName.'.discount_amount is not null THEN '.$itemTableName.'.discount_amount/'.$primaryModuleInstance->table_name.'.conversion_rate '. 3913 'WHEN '.$itemTableName.'.discount_percent IS NOT NULL THEN ('.$itemTableName.'.listprice*'.$itemTableName.'.quantity*'.$itemTableName.'.discount_percent/100/'.$primaryModuleInstance->table_name.'.conversion_rate) ELSE 0 END '; 3914 } 3915 } 3916 return $field; 3917 } 3918 3919 /** function to get query for the columns to total for the given reportid 3920 * @ param $reportid : Type integer 3921 * This returns columnstoTotal query for the reportid 3922 */ 3923 3924 function getColumnsToTotalColumns($reportid) 3925 { 3926 global $adb; 3927 global $modules; 3928 global $log; 3929 3930 $sreportstdfiltersql = "select vtiger_reportsummary.* from vtiger_report"; 3931 $sreportstdfiltersql .= " inner join vtiger_reportsummary on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid"; 3932 $sreportstdfiltersql .= " where vtiger_report.reportid =?"; 3933 3934 $result = $adb->pquery($sreportstdfiltersql, array($reportid)); 3935 $noofrows = $adb->num_rows($result); 3936 3937 for($i=0; $i<$noofrows; $i++) 3938 { 3939 $fieldcolname = $adb->query_result($result,$i,"columnname"); 3940 3941 if($fieldcolname != "none") 3942 { 3943 $fieldlist = explode(":",$fieldcolname); 3944 if($fieldlist[4] == 2) 3945 { 3946 $sSQLList[] = "sum(".$fieldlist[1].".".$fieldlist[2].") ".$fieldlist[3]; 3947 } 3948 if($fieldlist[4] == 3) 3949 { 3950 $sSQLList[] = "avg(".$fieldlist[1].".".$fieldlist[2].") ".$fieldlist[3]; 3951 } 3952 if($fieldlist[4] == 4) 3953 { 3954 $sSQLList[] = "min(".$fieldlist[1].".".$fieldlist[2].") ".$fieldlist[3]; 3955 } 3956 if($fieldlist[4] == 5) 3957 { 3958 $sSQLList[] = "max(".$fieldlist[1].".".$fieldlist[2].") ".$fieldlist[3]; 3959 } 3960 } 3961 } 3962 if(isset($sSQLList)) 3963 { 3964 $sSQL = implode(",",$sSQLList); 3965 } 3966 $log->info("ReportRun :: Successfully returned getColumnsToTotalColumns".$reportid); 3967 return $sSQL; 3968 } 3969 /** Function to convert the Report Header Names into i18n 3970 * @param $fldname: Type Varchar 3971 * Returns Language Converted Header Strings 3972 **/ 3973 function getLstringforReportHeaders($fldname) 3974 { 3975 global $modules,$current_language,$current_user,$app_strings; 3976 $rep_header = ltrim($fldname); 3977 $rep_header = decode_html($rep_header); 3978 $labelInfo = explode('_', $rep_header); 3979 $rep_module = $labelInfo[0]; 3980 if(is_array($this->labelMapping) && !empty($this->labelMapping[$rep_header])) { 3981 $rep_header = $this->labelMapping[$rep_header]; 3982 } else { 3983 if($rep_module == 'LBL') { 3984 $rep_module = ''; 3985 } 3986 array_shift($labelInfo); 3987 $fieldLabel = decode_html(implode("_",$labelInfo)); 3988 $rep_header_temp = preg_replace("/\s+/","_",$fieldLabel); 3989 $rep_header = "$rep_module $fieldLabel"; 3990 } 3991 $curr_symb = ""; 3992 $fieldLabel = ltrim(str_replace($rep_module, '', $rep_header), '_'); 3993 $fieldInfo = getFieldByReportLabel($rep_module, $fieldLabel); 3994 if($fieldInfo['uitype'] == '71') { 3995 $curr_symb = " (".$app_strings['LBL_IN']." ".$current_user->currency_symbol.")"; 3996 } 3997 $rep_header .=$curr_symb; 3998 3999 return $rep_header; 4000 } 4001 4002 /** Function to get picklist value array based on profile 4003 * * returns permitted fields in array format 4004 **/ 4005 4006 4007 function getAccessPickListValues() 4008 { 4009 global $adb; 4010 global $current_user; 4011 $id = array(getTabid($this->primarymodule)); 4012 if($this->secondarymodule != '') 4013 array_push($id, getTabid($this->secondarymodule)); 4014 4015 $query = 'select fieldname,columnname,fieldid,fieldlabel,tabid,uitype from vtiger_field where tabid in('. generateQuestionMarks($id) .') and uitype in (15,33,55)'; //and columnname in (?)'; 4016 $result = $adb->pquery($query, $id);//,$select_column)); 4017 $roleid=$current_user->roleid; 4018 $subrole = getRoleSubordinates($roleid); 4019 if(count($subrole)> 0) 4020 { 4021 $roleids = $subrole; 4022 array_push($roleids, $roleid); 4023 } 4024 else 4025 { 4026 $roleids = $roleid; 4027 } 4028 4029 $temp_status = Array(); 4030 for($i=0;$i < $adb->num_rows($result);$i++) 4031 { 4032 $fieldname = $adb->query_result($result,$i,"fieldname"); 4033 $fieldlabel = $adb->query_result($result,$i,"fieldlabel"); 4034 $tabid = $adb->query_result($result,$i,"tabid"); 4035 $uitype = $adb->query_result($result,$i,"uitype"); 4036 4037 $fieldlabel1 = str_replace(" ","_",$fieldlabel); 4038 $keyvalue = getTabModuleName($tabid)."_".$fieldlabel1; 4039 $fieldvalues = Array(); 4040 if (count($roleids) > 1) { 4041 $mulsel="select distinct $fieldname from vtiger_$fieldname inner join vtiger_role2picklist on vtiger_role2picklist.picklistvalueid = vtiger_$fieldname.picklist_valueid where roleid in (\"". implode($roleids,"\",\"") ."\") and picklistid in (select picklistid from vtiger_$fieldname)"; // order by sortid asc - not requried 4042 } else { 4043 $mulsel="select distinct $fieldname from vtiger_$fieldname inner join vtiger_role2picklist on vtiger_role2picklist.picklistvalueid = vtiger_$fieldname.picklist_valueid where roleid ='".$roleid."' and picklistid in (select picklistid from vtiger_$fieldname)"; // order by sortid asc - not requried 4044 } 4045 if($fieldname != 'firstname') 4046 $mulselresult = $adb->query($mulsel); 4047 for($j=0;$j < $adb->num_rows($mulselresult);$j++) 4048 { 4049 $fldvalue = $adb->query_result($mulselresult,$j,$fieldname); 4050 if(in_array($fldvalue,$fieldvalues)) continue; 4051 $fieldvalues[] = $fldvalue; 4052 } 4053 $field_count = count($fieldvalues); 4054 if( $uitype == 15 && $field_count > 0 && ($fieldname == 'taskstatus' || $fieldname == 'eventstatus')) 4055 { 4056 $temp_count =count($temp_status[$keyvalue]); 4057 if($temp_count > 0) 4058 { 4059 for($t=0;$t < $field_count;$t++) 4060 { 4061 $temp_status[$keyvalue][($temp_count+$t)] = $fieldvalues[$t]; 4062 } 4063 $fieldvalues = $temp_status[$keyvalue]; 4064 } 4065 else 4066 $temp_status[$keyvalue] = $fieldvalues; 4067 } 4068 4069 if($uitype == 33) 4070 $fieldlists[1][$keyvalue] = $fieldvalues; 4071 else if($uitype == 55 && $fieldname == 'salutationtype') 4072 $fieldlists[$keyvalue] = $fieldvalues; 4073 else if($uitype == 15) 4074 $fieldlists[$keyvalue] = $fieldvalues; 4075 } 4076 return $fieldlists; 4077 } 4078 4079 function getReportPDF($filterlist=false) { 4080 require_once 'libraries/tcpdf/tcpdf.php'; 4081 4082 $reportData = $this->GenerateReport("PDF",$filterlist); 4083 $arr_val = $reportData['data']; 4084 4085 if(isset($arr_val)) { 4086 foreach($arr_val as $wkey=>$warray_value) { 4087 foreach($warray_value as $whd=>$wvalue) { 4088 if(strlen($wvalue) < strlen($whd)) { 4089 $w_inner_array[] = strlen($whd); 4090 } else { 4091 $w_inner_array[] = strlen($wvalue); 4092 } 4093 } 4094 $warr_val[] = $w_inner_array; 4095 unset($w_inner_array); 4096 } 4097 4098 foreach($warr_val[0] as $fkey=>$fvalue) { 4099 foreach($warr_val as $wkey=>$wvalue) { 4100 $f_inner_array[] = $warr_val[$wkey][$fkey]; 4101 } 4102 sort($f_inner_array,1); 4103 $farr_val[] = $f_inner_array; 4104 unset($f_inner_array); 4105 } 4106 4107 foreach($farr_val as $skkey=>$skvalue) { 4108 if($skvalue[count($arr_val)-1] == 1) { 4109 $col_width[] = ($skvalue[count($arr_val)-1] * 50); 4110 } else { 4111 $col_width[] = ($skvalue[count($arr_val)-1] * 10) + 10 ; 4112 } 4113 } 4114 $count = 0; 4115 foreach($arr_val[0] as $key=>$value) { 4116 $headerHTML .= '<td width="'.$col_width[$count].'" bgcolor="#DDDDDD"><b>'.$this->getLstringforReportHeaders($key).'</b></td>'; 4117 $count = $count + 1; 4118 } 4119 4120 foreach($arr_val as $key=>$array_value) { 4121 $valueHTML = ""; 4122 $count = 0; 4123 foreach($array_value as $hd=>$value) { 4124 $valueHTML .= '<td width="'.$col_width[$count].'">'.$value.'</td>'; 4125 $count = $count + 1; 4126 } 4127 $dataHTML .= '<tr>'.$valueHTML.'</tr>'; 4128 } 4129 4130 } 4131 4132 $totalpdf = $this->GenerateReport("PRINT_TOTAL",$filterlist); 4133 $html = '<table border="0.5"><tr>'.$headerHTML.'</tr>'.$dataHTML.'<tr><td>'.$totalpdf.'</td></tr>'.'</table>'; 4134 $columnlength = array_sum($col_width); 4135 if($columnlength > 14400) { 4136 die("<br><br><center>".$app_strings['LBL_PDF']." <a href='javascript:window.history.back()'>".$app_strings['LBL_GO_BACK'].".</a></center>"); 4137 } 4138 if($columnlength <= 420 ) { 4139 $pdf = new TCPDF('P','mm','A5',true); 4140 4141 } elseif($columnlength >= 421 && $columnlength <= 1120) { 4142 $pdf = new TCPDF('L','mm','A3',true); 4143 4144 }elseif($columnlength >=1121 && $columnlength <= 1600) { 4145 $pdf = new TCPDF('L','mm','A2',true); 4146 4147 }elseif($columnlength >=1601 && $columnlength <= 2200) { 4148 $pdf = new TCPDF('L','mm','A1',true); 4149 } 4150 elseif($columnlength >=2201 && $columnlength <= 3370) { 4151 $pdf = new TCPDF('L','mm','A0',true); 4152 } 4153 elseif($columnlength >=3371 && $columnlength <= 4690) { 4154 $pdf = new TCPDF('L','mm','2A0',true); 4155 } 4156 elseif($columnlength >=4691 && $columnlength <= 6490) { 4157 $pdf = new TCPDF('L','mm','4A0',true); 4158 } 4159 else { 4160 $columnhight = count($arr_val)*15; 4161 $format = array($columnhight,$columnlength); 4162 $pdf = new TCPDF('L','mm',$format,true); 4163 } 4164 $pdf->SetMargins(10, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT); 4165 $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM); 4166 $pdf->SetHeaderMargin(PDF_MARGIN_HEADER); 4167 $pdf->SetFooterMargin(PDF_MARGIN_FOOTER); 4168 $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN)); 4169 $pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA)); 4170 $pdf->setLanguageArray($l); 4171 $pdf->AddPage(); 4172 4173 $pdf->SetFillColor(224,235,255); 4174 $pdf->SetTextColor(0); 4175 $pdf->SetFont('FreeSerif','B',14); 4176 $pdf->Cell(($pdf->columnlength*50),10,getTranslatedString($oReport->reportname),0,0,'C',0); 4177 //$pdf->writeHTML($oReport->reportname); 4178 $pdf->Ln(); 4179 4180 $pdf->SetFont('FreeSerif','',10); 4181 4182 $pdf->writeHTML($html); 4183 4184 return $pdf; 4185 } 4186 4187 function writeReportToExcelFile($fileName, $filterlist='') { 4188 4189 global $currentModule, $current_language; 4190 $mod_strings = return_module_language($current_language, $currentModule); 4191 4192 require_once ("libraries/PHPExcel/PHPExcel.php"); 4193 4194 $workbook = new PHPExcel(); 4195 $worksheet = $workbook->setActiveSheetIndex(0); 4196 4197 $reportData = $this->GenerateReport("PDF",$filterlist); 4198 $arr_val = $reportData['data']; 4199 $totalxls = $this->GenerateReport("TOTALXLS",$filterlist); 4200 4201 $header_styles = array( 4202 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb'=>'E1E0F7') ), 4203 //'font' => array( 'bold' => true ) 4204 ); 4205 4206 if(isset($arr_val)) { 4207 $count = 0; 4208 $rowcount = 1; 4209 //copy the first value details 4210 $arrayFirstRowValues = $arr_val[0]; 4211 array_pop($arrayFirstRowValues); // removed action link in details 4212 foreach($arrayFirstRowValues as $key=>$value) { 4213 $worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $key, true); 4214 $worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles); 4215 4216 // NOTE Performance overhead: http://stackoverflow.com/questions/9965476/phpexcel-column-size-issues 4217 //$worksheet->getColumnDimensionByColumn($count)->setAutoSize(true); 4218 4219 $count = $count + 1; 4220 } 4221 4222 $rowcount++; 4223 foreach($arr_val as $key=>$array_value) { 4224 $count = 0; 4225 array_pop($array_value); // removed action link in details 4226 foreach($array_value as $hdr=>$value) { 4227 if($hdr == 'ACTION') continue; 4228 $value = decode_html($value); 4229 // TODO Determine data-type based on field-type. 4230 // String type helps having numbers prefixed with 0 intact. 4231 $worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $value, PHPExcel_Cell_DataType::TYPE_STRING); 4232 $count = $count + 1; 4233 } 4234 $rowcount++; 4235 } 4236 4237 // Summary Total 4238 $rowcount++; 4239 $count=0; 4240 if(is_array($totalxls[0])) { 4241 foreach($totalxls[0] as $key=>$value) { 4242 $chdr=substr($key,-3,3); 4243 $translated_str = in_array($chdr ,array_keys($mod_strings))?$mod_strings[$chdr]:$key; 4244 $worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $translated_str); 4245 4246 $worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles); 4247 4248 $count = $count + 1; 4249 } 4250 } 4251 4252 $rowcount++; 4253 foreach($totalxls as $key=>$array_value) { 4254 $count = 0; 4255 foreach($array_value as $hdr=>$value) { 4256 $value = decode_html($value); 4257 $worksheet->setCellValueExplicitByColumnAndRow($count, $key+$rowcount, $value); 4258 $count = $count + 1; 4259 } 4260 } 4261 } 4262 4263 $workbookWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel5'); 4264 $workbookWriter->save($fileName); 4265 } 4266 4267 function writeReportToCSVFile($fileName, $filterlist='') { 4268 4269 global $currentModule, $current_language; 4270 $mod_strings = return_module_language($current_language, $currentModule); 4271 4272 $reportData = $this->GenerateReport("PDF",$filterlist); 4273 $arr_val = $reportData['data']; 4274 4275 $fp = fopen($fileName, 'w+'); 4276 4277 if(isset($arr_val)) { 4278 $csv_values = array(); 4279 // Header 4280 $csv_values = array_keys($arr_val[0]); 4281 array_pop($csv_values); //removed header in csv file 4282 fputcsv($fp, $csv_values); 4283 foreach($arr_val as $key=>$array_value) { 4284 array_pop($array_value); //removed action link 4285 $csv_values = array_map('decode_html', array_values($array_value)); 4286 fputcsv($fp, $csv_values); 4287 } 4288 } 4289 fclose($fp); 4290 } 4291 4292 function getGroupByTimeList($reportId){ 4293 global $adb; 4294 $groupByTimeQuery = "SELECT * FROM vtiger_reportgroupbycolumn WHERE reportid=?"; 4295 $groupByTimeRes = $adb->pquery($groupByTimeQuery,array($reportId)); 4296 $num_rows = $adb->num_rows($groupByTimeRes); 4297 for($i=0;$i<$num_rows;$i++){ 4298 $sortColName = $adb->query_result($groupByTimeRes, $i,'sortcolname'); 4299 list($tablename,$colname,$module_field,$fieldname,$single) = split(':',$sortColName); 4300 $groupField = $module_field; 4301 $groupCriteria = $adb->query_result($groupByTimeRes, $i,'dategroupbycriteria'); 4302 if(in_array($groupCriteria,array_keys($this->groupByTimeParent))){ 4303 $parentCriteria = $this->groupByTimeParent[$groupCriteria]; 4304 foreach($parentCriteria as $criteria){ 4305 $groupByCondition[]=$this->GetTimeCriteriaCondition($criteria, $groupField); 4306 } 4307 } 4308 $groupByCondition[] = $this->GetTimeCriteriaCondition($groupCriteria, $groupField); 4309 $this->queryPlanner->addTable($tablename); 4310 } 4311 return $groupByCondition; 4312 } 4313 4314 function GetTimeCriteriaCondition($criteria,$dateField){ 4315 $condition = ""; 4316 if(strtolower($criteria)=='year'){ 4317 $condition = "DATE_FORMAT($dateField, '%Y' )"; 4318 } 4319 else if (strtolower($criteria)=='month'){ 4320 $condition = "CEIL(DATE_FORMAT($dateField,'%m')%13)"; 4321 } 4322 else if(strtolower($criteria)=='quarter'){ 4323 $condition = "CEIL(DATE_FORMAT($dateField,'%m')/3)"; 4324 } 4325 return $condition; 4326 } 4327 4328 function GetFirstSortByField($reportid) 4329 { 4330 global $adb; 4331 $groupByField =""; 4332 $sortFieldQuery = "SELECT * FROM vtiger_reportsortcol 4333 LEFT JOIN vtiger_reportgroupbycolumn ON (vtiger_reportsortcol.sortcolid = vtiger_reportgroupbycolumn.sortid and vtiger_reportsortcol.reportid = vtiger_reportgroupbycolumn.reportid) 4334 WHERE columnname!='none' and vtiger_reportsortcol.reportid=? ORDER By sortcolid"; 4335 $sortFieldResult= $adb->pquery($sortFieldQuery,array($reportid)); 4336 $inventoryModules = getInventoryModules(); 4337 if($adb->num_rows($sortFieldResult)>0){ 4338 $fieldcolname = $adb->query_result($sortFieldResult,0,'columnname'); 4339 list($tablename,$colname,$module_field,$fieldname,$typeOfData) = explode(":",$fieldcolname); 4340 list($modulename,$fieldlabel) = explode('_', $module_field, 2); 4341 $groupByField = $module_field; 4342 if($typeOfData == "D"){ 4343 $groupCriteria = $adb->query_result($sortFieldResult,0,'dategroupbycriteria'); 4344 if(strtolower($groupCriteria)!='none'){ 4345 if(in_array($groupCriteria,array_keys($this->groupByTimeParent))){ 4346 $parentCriteria = $this->groupByTimeParent[$groupCriteria]; 4347 foreach($parentCriteria as $criteria){ 4348 $groupByCondition[]=$this->GetTimeCriteriaCondition($criteria, $groupByField); 4349 } 4350 } 4351 $groupByCondition[] = $this->GetTimeCriteriaCondition($groupCriteria, $groupByField); 4352 $groupByField = implode(", ",$groupByCondition); 4353 } 4354 4355 } elseif(CheckFieldPermission($fieldname,$modulename) != 'true') { 4356 if (!(in_array($modulename, $inventoryModules) && $fieldname == 'serviceid')) { 4357 $groupByField = $tablename.".".$colname; 4358 } 4359 } 4360 } 4361 return $groupByField; 4362 } 4363 4364 function getReferenceFieldColumnList($moduleName, $fieldInfo) { 4365 $adb = PearDatabase::getInstance(); 4366 4367 $columnsSqlList = array(); 4368 4369 $fieldInstance = WebserviceField::fromArray($adb, $fieldInfo); 4370 $referenceModuleList = $fieldInstance->getReferenceList(); 4371 $reportSecondaryModules = explode(':', $this->secondarymodule); 4372 4373 if($moduleName != $this->primarymodule && in_array($this->primarymodule, $referenceModuleList)) { 4374 $entityTableFieldNames = getEntityFieldNames($this->primarymodule); 4375 $entityTableName = $entityTableFieldNames['tablename']; 4376 $entityFieldNames = $entityTableFieldNames['fieldname']; 4377 4378 $columnList = array(); 4379 if(is_array($entityFieldNames)) { 4380 foreach ($entityFieldNames as $entityColumnName) { 4381 $columnList["$entityColumnName"] = "$entityTableName.$entityColumnName"; 4382 } 4383 } else { 4384 $columnList[] = "$entityTableName.$entityFieldNames"; 4385 } 4386 if(count($columnList) > 1) { 4387 $columnSql = getSqlForNameInDisplayFormat($columnList, $this->primarymodule); 4388 } else { 4389 $columnSql = implode('', $columnList); 4390 } 4391 $columnsSqlList[] = $columnSql; 4392 4393 } else { 4394 foreach($referenceModuleList as $referenceModule) { 4395 $entityTableFieldNames = getEntityFieldNames($referenceModule); 4396 $entityTableName = $entityTableFieldNames['tablename']; 4397 $entityFieldNames = $entityTableFieldNames['fieldname']; 4398 4399 $referenceTableName = ''; 4400 $dependentTableName = ''; 4401 4402 if($moduleName == 'HelpDesk' && $referenceModule == 'Accounts') { 4403 $referenceTableName = 'vtiger_accountRelHelpDesk'; 4404 } elseif ($moduleName == 'HelpDesk' && $referenceModule == 'Contacts') { 4405 $referenceTableName = 'vtiger_contactdetailsRelHelpDesk'; 4406 } elseif ($moduleName == 'HelpDesk' && $referenceModule == 'Products') { 4407 $referenceTableName = 'vtiger_productsRel'; 4408 } elseif ($moduleName == 'Calendar' && $referenceModule == 'Accounts') { 4409 $referenceTableName = 'vtiger_accountRelCalendar'; 4410 } elseif ($moduleName == 'Calendar' && $referenceModule == 'Contacts') { 4411 $referenceTableName = 'vtiger_contactdetailsCalendar'; 4412 } elseif ($moduleName == 'Calendar' && $referenceModule == 'Leads') { 4413 $referenceTableName = 'vtiger_leaddetailsRelCalendar'; 4414 } elseif ($moduleName == 'Calendar' && $referenceModule == 'Potentials') { 4415 $referenceTableName = 'vtiger_potentialRelCalendar'; 4416 } elseif ($moduleName == 'Calendar' && $referenceModule == 'Invoice') { 4417 $referenceTableName = 'vtiger_invoiceRelCalendar'; 4418 } elseif ($moduleName == 'Calendar' && $referenceModule == 'Quotes') { 4419 $referenceTableName = 'vtiger_quotesRelCalendar'; 4420 } elseif ($moduleName == 'Calendar' && $referenceModule == 'PurchaseOrder') { 4421 $referenceTableName = 'vtiger_purchaseorderRelCalendar'; 4422 } elseif ($moduleName == 'Calendar' && $referenceModule == 'SalesOrder') { 4423 $referenceTableName = 'vtiger_salesorderRelCalendar'; 4424 } elseif ($moduleName == 'Calendar' && $referenceModule == 'HelpDesk') { 4425 $referenceTableName = 'vtiger_troubleticketsRelCalendar'; 4426 } elseif ($moduleName == 'Calendar' && $referenceModule == 'Campaigns') { 4427 $referenceTableName = 'vtiger_campaignRelCalendar'; 4428 } elseif ($moduleName == 'Contacts' && $referenceModule == 'Accounts') { 4429 $referenceTableName = 'vtiger_accountContacts'; 4430 } elseif ($moduleName == 'Contacts' && $referenceModule == 'Contacts') { 4431 $referenceTableName = 'vtiger_contactdetailsContacts'; 4432 } elseif ($moduleName == 'Accounts' && $referenceModule == 'Accounts') { 4433 $referenceTableName = 'vtiger_accountAccounts'; 4434 } elseif ($moduleName == 'Campaigns' && $referenceModule == 'Products') { 4435 $referenceTableName = 'vtiger_productsCampaigns'; 4436 } elseif ($moduleName == 'Faq' && $referenceModule == 'Products') { 4437 $referenceTableName = 'vtiger_productsFaq'; 4438 } elseif ($moduleName == 'Invoice' && $referenceModule == 'SalesOrder') { 4439 $referenceTableName = 'vtiger_salesorderInvoice'; 4440 } elseif ($moduleName == 'Invoice' && $referenceModule == 'Contacts') { 4441 $referenceTableName = 'vtiger_contactdetailsInvoice'; 4442 } elseif ($moduleName == 'Invoice' && $referenceModule == 'Accounts') { 4443 $referenceTableName = 'vtiger_accountInvoice'; 4444 } elseif ($moduleName == 'Potentials' && $referenceModule == 'Campaigns') { 4445 $referenceTableName = 'vtiger_campaignPotentials'; 4446 } elseif ($moduleName == 'Products' && $referenceModule == 'Vendors') { 4447 $referenceTableName = 'vtiger_vendorRelProducts'; 4448 } elseif ($moduleName == 'PurchaseOrder' && $referenceModule == 'Contacts') { 4449 $referenceTableName = 'vtiger_contactdetailsPurchaseOrder'; 4450 } elseif ($moduleName == 'PurchaseOrder' && $referenceModule == 'Vendors') { 4451 $referenceTableName = 'vtiger_vendorRelPurchaseOrder'; 4452 } elseif ($moduleName == 'Quotes' && $referenceModule == 'Potentials') { 4453 $referenceTableName = 'vtiger_potentialRelQuotes'; 4454 } elseif ($moduleName == 'Quotes' && $referenceModule == 'Accounts') { 4455 $referenceTableName = 'vtiger_accountQuotes'; 4456 } elseif ($moduleName == 'Quotes' && $referenceModule == 'Contacts') { 4457 $referenceTableName = 'vtiger_contactdetailsQuotes'; 4458 } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Potentials') { 4459 $referenceTableName = 'vtiger_potentialRelSalesOrder'; 4460 } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Accounts') { 4461 $referenceTableName = 'vtiger_accountSalesOrder'; 4462 } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Contacts') { 4463 $referenceTableName = 'vtiger_contactdetailsSalesOrder'; 4464 } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Quotes') { 4465 $referenceTableName = 'vtiger_quotesSalesOrder'; 4466 } elseif ($moduleName == 'Potentials' && $referenceModule == 'Contacts') { 4467 $referenceTableName = 'vtiger_contactdetailsPotentials'; 4468 } elseif ($moduleName == 'Potentials' && $referenceModule == 'Accounts') { 4469 $referenceTableName = 'vtiger_accountPotentials'; 4470 } elseif ($moduleName == 'ModComments' && $referenceModule == 'Users') { 4471 $referenceTableName = 'vtiger_usersModComments'; 4472 } elseif (in_array($referenceModule, $reportSecondaryModules)) { 4473 $referenceTableName = "{$entityTableName}Rel$referenceModule"; 4474 $dependentTableName = "vtiger_crmentityRel{$referenceModule}{$fieldInstance->getFieldId()}"; 4475 } elseif (in_array($moduleName, $reportSecondaryModules)) { 4476 $referenceTableName = "{$entityTableName}Rel$moduleName"; 4477 $dependentTableName = "vtiger_crmentityRel{$moduleName}{$fieldInstance->getFieldId()}"; 4478 } else { 4479 $referenceTableName = "{$entityTableName}Rel{$moduleName}{$fieldInstance->getFieldId()}"; 4480 $dependentTableName = "vtiger_crmentityRel{$moduleName}{$fieldInstance->getFieldId()}"; 4481 } 4482 4483 $this->queryPlanner->addTable($referenceTableName); 4484 4485 if(isset($dependentTableName)){ 4486 $this->queryPlanner->addTable($dependentTableName); 4487 } 4488 $columnList = array(); 4489 if(is_array($entityFieldNames)) { 4490 foreach ($entityFieldNames as $entityColumnName) { 4491 $columnList["$entityColumnName"] = "$referenceTableName.$entityColumnName"; 4492 } 4493 } else { 4494 $columnList[] = "$referenceTableName.$entityFieldNames"; 4495 } 4496 if(count($columnList) > 1) { 4497 $columnSql = getSqlForNameInDisplayFormat($columnList, $referenceModule); 4498 } else { 4499 $columnSql = implode('', $columnList); 4500 } 4501 if ($referenceModule == 'DocumentFolders' && $fieldInstance->getFieldName() == 'folderid') { 4502 $columnSql = 'vtiger_attachmentsfolder.foldername'; 4503 $this->queryPlanner->addTable("vtiger_attachmentsfolder"); 4504 } 4505 if ($referenceModule == 'Currency' && $fieldInstance->getFieldName() == 'currency_id') { 4506 $columnSql = "vtiger_currency_info$moduleName.currency_name"; 4507 $this->queryPlanner->addTable("vtiger_currency_info$moduleName"); 4508 } 4509 $columnsSqlList[] = "trim($columnSql)"; 4510 } 4511 } 4512 return $columnsSqlList; 4513 } 4514 } 4515 ?>
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 |