| [ Index ] |
|
Code source de vtiger CRM 5.0.2 |
1 <?php 2 /********************************************************************************* 3 ** The contents of this file are subject to the vtiger CRM Public License Version 1.0 4 * ("License"); You may not use this file except in compliance with the License 5 * The Original Code is: vtiger CRM Open Source 6 * The Initial Developer of the Original Code is vtiger. 7 * Portions created by vtiger are Copyright (C) vtiger. 8 * All Rights Reserved. 9 * 10 ********************************************************************************/ 11 global $calpath; 12 global $app_strings,$mod_strings; 13 global $theme; 14 global $log; 15 16 $theme_path="themes/".$theme."/"; 17 $image_path=$theme_path."images/"; 18 require_once ('include/database/PearDatabase.php'); 19 require_once ($theme_path."layout_utils.php"); 20 require_once ('data/CRMEntity.php'); 21 require_once ("modules/Reports/Reports.php"); 22 23 class ReportRun extends CRMEntity 24 { 25 26 var $primarymodule; 27 var $secondarymodule; 28 var $orderbylistsql; 29 var $orderbylistcolumns; 30 31 var $selectcolumns; 32 var $groupbylist; 33 var $reporttype; 34 var $reportname; 35 var $totallist; 36 37 /** Function to set reportid,primarymodule,secondarymodule,reporttype,reportname, for given reportid 38 * This function accepts the $reportid as argument 39 * It sets reportid,primarymodule,secondarymodule,reporttype,reportname for the given reportid 40 */ 41 function ReportRun($reportid) 42 { 43 $oReport = new Reports($reportid); 44 $this->reportid = $reportid; 45 $this->primarymodule = $oReport->primodule; 46 $this->secondarymodule = $oReport->secmodule; 47 $this->reporttype = $oReport->reporttype; 48 $this->reportname = $oReport->reportname; 49 } 50 51 /** Function to get the columns for the reportid 52 * This function accepts the $reportid 53 * This function returns $columnslist Array($tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname As Header value, 54 * $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 As Header value, 55 * | 56 * $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen As Header value 57 * ) 58 * 59 */ 60 function getQueryColumnsList($reportid) 61 { 62 global $adb; 63 global $modules; 64 global $log,$current_user; 65 66 $ssql = "select vtiger_selectcolumn.* from vtiger_report inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid"; 67 $ssql .= " left join vtiger_selectcolumn on vtiger_selectcolumn.queryid = vtiger_selectquery.queryid"; 68 $ssql .= " where vtiger_report.reportid =".$reportid; 69 $ssql .= " order by vtiger_selectcolumn.columnindex"; 70 $result = $adb->query($ssql); 71 72 $permitted_fields = Array(); 73 74 while($columnslistrow = $adb->fetch_array($result)) 75 { 76 $fieldname =""; 77 $fieldcolname = $columnslistrow["columnname"]; 78 list($tablename,$fieldname,$module_field,$colname,$single) = split(":",$fieldcolname); 79 require('user_privileges/user_privileges_'.$current_user->id.'.php'); 80 if(sizeof($permitted_fields) == 0 && $is_admin != true && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) 81 { 82 list($module,$field) = split("_",$module_field); 83 $permitted_fields = $this->getaccesfield($module); 84 } 85 $selectedfields = explode(":",$fieldcolname); 86 87 $querycolumns = $this->getEscapedColumns($selectedfields); 88 89 if(sizeof($permitted_fields) != 0 && !in_array($fieldname,$permitted_fields)) 90 { 91 continue; 92 } 93 else 94 { 95 if($querycolumns == "") 96 { 97 $columnslist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1].' AS "'.$selectedfields[2].'"'; 98 } 99 else 100 { 101 $columnslist[$fieldcolname] = $querycolumns; 102 } 103 } 104 } 105 $log->info("ReportRun :: Successfully returned getQueryColumnsList".$reportid); 106 return $columnslist; 107 } 108 109 /** Function to get field columns based on profile 110 * @ param $module : Type string 111 * returns permitted fields in array format 112 */ 113 function getaccesfield($module) 114 { 115 global $current_user; 116 global $adb; 117 $access_fields = Array(); 118 119 $profileList = getCurrentUserProfileList(); 120 $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 vtiger_field.tabid=(select tabid from vtiger_tab where vtiger_tab.name='".$module."') and vtiger_field.displaytype in (1,2,4) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_profile2field.profileid in ".$profileList." group by vtiger_field.fieldid order by block,sequence"; 121 122 $result = $adb->query($query); 123 124 while($collistrow = $adb->fetch_array($result)) 125 { 126 $access_fields[] = $collistrow["fieldname"]; 127 } 128 return $access_fields; 129 } 130 131 /** Function to get Escapedcolumns for the field in case of multiple parents 132 * @ param $selectedfields : Type Array 133 * returns the case query for the escaped columns 134 */ 135 function getEscapedColumns($selectedfields) 136 { 137 $fieldname = $selectedfields[3]; 138 if($fieldname == "parent_id") 139 { 140 if($this->primarymodule == "HelpDesk" && $selectedfields[0] == "vtiger_crmentityRelHelpDesk") 141 { 142 $querycolumn = "case vtiger_crmentityRelHelpDesk.setype when 'Accounts' then vtiger_accountRelHelpDesk.accountname when 'Contacts' then vtiger_contactdetailsRelHelpDesk.lastname End"." '".$selectedfields[2]."', vtiger_crmentityRelHelpDesk.setype 'Entity_type'"; 143 return $querycolumn; 144 } 145 if($this->primarymodule == "Products" || $this->secondarymodule == "Products") 146 { 147 $querycolumn = "case vtiger_crmentityRelProducts.setype when 'Accounts' then vtiger_accountRelProducts.accountname when 'Leads' then vtiger_leaddetailsRelProducts.lastname when 'Potentials' then vtiger_potentialRelProducts.potentialname End"." '".$selectedfields[2]."', vtiger_crmentityRelProducts.setype 'Entity_type'"; 148 } 149 if($this->primarymodule == "Calendar" || $this->secondarymodule == "Calendar") 150 { 151 $querycolumn = "case vtiger_crmentityRelCalendar.setype when 'Accounts' then vtiger_accountRelCalendar.accountname when 'Leads' then vtiger_leaddetailsRelCalendar.lastname when 'Potentials' then vtiger_potentialRelCalendar.potentialname when 'Quotes' then vtiger_quotesRelCalendar.subject when 'PurchaseOrder' then vtiger_purchaseorderRelCalendar.subject when 'Invoice' then vtiger_invoiceRelCalendar.subject End"." '".$selectedfields[2]."', vtiger_crmentityRelCalendar.setype 'Entity_type'"; 152 } 153 } 154 return $querycolumn; 155 } 156 157 /** Function to get selectedcolumns for the given reportid 158 * @ param $reportid : Type Integer 159 * returns the query of columnlist for the selected columns 160 */ 161 function getSelectedColumnsList($reportid) 162 { 163 164 global $adb; 165 global $modules; 166 global $log; 167 168 $ssql = "select vtiger_selectcolumn.* from vtiger_report inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid"; 169 $ssql .= " left join vtiger_selectcolumn on vtiger_selectcolumn.queryid = vtiger_selectquery.queryid where vtiger_report.reportid =".$reportid; 170 $ssql .= " order by vtiger_selectcolumn.columnindex"; 171 172 $result = $adb->query($ssql); 173 $noofrows = $adb->num_rows($result); 174 175 if ($this->orderbylistsql != "") 176 { 177 $sSQL .= $this->orderbylistsql.", "; 178 } 179 180 for($i=0; $i<$noofrows; $i++) 181 { 182 $fieldcolname = $adb->query_result($result,$i,"columnname"); 183 $ordercolumnsequal = true; 184 if($fieldcolname != "") 185 { 186 for($j=0;$j<count($this->orderbylistcolumns);$j++) 187 { 188 if($this->orderbylistcolumns[$j] == $fieldcolname) 189 { 190 $ordercolumnsequal = false; 191 break; 192 }else 193 { 194 $ordercolumnsequal = true; 195 } 196 } 197 if($ordercolumnsequal) 198 { 199 $selectedfields = explode(":",$fieldcolname); 200 $sSQLList[] = $selectedfields[0].".".$selectedfields[1]." '".$selectedfields[2]."'"; 201 } 202 } 203 } 204 $sSQL .= implode(",",$sSQLList); 205 206 $log->info("ReportRun :: Successfully returned getSelectedColumnsList".$reportid); 207 return $sSQL; 208 } 209 210 /** Function to get advanced comparator in query form for the given Comparator and value 211 * @ param $comparator : Type String 212 * @ param $value : Type String 213 * returns the check query for the comparator 214 */ 215 function getAdvComparator($comparator,$value,$datatype="") 216 { 217 218 global $log,$adb; 219 220 if($comparator == "e") 221 { 222 if(trim($value) == "NULL") 223 { 224 $rtvalue = " is NULL"; 225 }elseif(trim($value) != "") 226 { 227 $rtvalue = " = ".$adb->quote($value); 228 }elseif(trim($value) == "" && $datatype == "V") 229 { 230 $rtvalue = " = ".$adb->quote($value); 231 }else 232 { 233 $rtvalue = " is NULL"; 234 } 235 } 236 if($comparator == "n") 237 { 238 if(trim($value) == "NULL") 239 { 240 $rtvalue = " is NOT NULL"; 241 }elseif(trim($value) != "") 242 { 243 $rtvalue = " <> ".$adb->quote($value); 244 }elseif(trim($value) == "" && $datatype == "V") 245 { 246 $rtvalue = " <> ".$adb->quote($value); 247 }else 248 { 249 $rtvalue = " is NOT NULL"; 250 } 251 } 252 if($comparator == "s") 253 { 254 $rtvalue = " like ".$adb->quote($value."%"); 255 } 256 if($comparator == "c") 257 { 258 $rtvalue = " like ".$adb->quote("%".$value."%"); 259 } 260 if($comparator == "k") 261 { 262 $rtvalue = " not like ".$adb->quote("%".$value."%"); 263 } 264 if($comparator == "l") 265 { 266 $rtvalue = " < ".$adb->quote($value); 267 } 268 if($comparator == "g") 269 { 270 $rtvalue = " > ".$adb->quote($value); 271 } 272 if($comparator == "m") 273 { 274 $rtvalue = " <= ".$adb->quote($value); 275 } 276 if($comparator == "h") 277 { 278 $rtvalue = " >= ".$adb->quote($value); 279 } 280 281 $log->info("ReportRun :: Successfully returned getAdvComparator"); 282 return $rtvalue; 283 } 284 285 /** Function to get the advanced filter columns for the reportid 286 * This function accepts the $reportid 287 * This function returns $columnslist Array($columnname => $tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname filtercriteria, 288 * $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria, 289 * | 290 * $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen filtercriteria 291 * ) 292 * 293 */ 294 295 296 function getAdvFilterList($reportid) 297 { 298 global $adb; 299 global $modules; 300 global $log; 301 302 $advfiltersql = "select vtiger_relcriteria.* from vtiger_report"; 303 $advfiltersql .= " inner join vtiger_selectquery on vtiger_selectquery.queryid = vtiger_report.queryid"; 304 $advfiltersql .= " left join vtiger_relcriteria on vtiger_relcriteria.queryid = vtiger_selectquery.queryid"; 305 $advfiltersql .= " where vtiger_report.reportid =".$reportid; 306 $advfiltersql .= " order by vtiger_relcriteria.columnindex"; 307 308 $result = $adb->query($advfiltersql); 309 while($advfilterrow = $adb->fetch_array($result)) 310 { 311 $fieldcolname = $advfilterrow["columnname"]; 312 $comparator = $advfilterrow["comparator"]; 313 $value = $advfilterrow["value"]; 314 315 if($fieldcolname != "" && $comparator != "") 316 { 317 $selectedfields = explode(":",$fieldcolname); 318 $valuearray = explode(",",trim($value)); 319 $datatype = (isset($selectedfields[4])) ? $selectedfields[4] : ""; 320 if(isset($valuearray) && count($valuearray) > 1) 321 { 322 $advorsql = ""; 323 for($n=0;$n<count($valuearray);$n++) 324 { 325 $advorsql[] = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($valuearray[$n]),$datatype); 326 } 327 $advorsqls = implode(" or ",$advorsql); 328 $fieldvalue = " (".$advorsqls.") "; 329 }else 330 { 331 $fieldvalue = $selectedfields[0].".".$selectedfields[1].$this->getAdvComparator($comparator,trim($value),$datatype); 332 } 333 $advfilterlist[$fieldcolname] = $fieldvalue; 334 } 335 336 } 337 $log->info("ReportRun :: Successfully returned getAdvFilterList".$reportid); 338 return $advfilterlist; 339 } 340 341 /** Function to get the Standard filter columns for the reportid 342 * This function accepts the $reportid datatype Integer 343 * This function returns $stdfilterlist Array($columnname => $tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname filtercriteria, 344 * $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria, 345 * ) 346 * 347 */ 348 function getStdFilterList($reportid) 349 { 350 global $adb; 351 global $modules; 352 global $log; 353 354 $stdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report"; 355 $stdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid"; 356 $stdfiltersql .= " where vtiger_report.reportid = ".$reportid; 357 358 $result = $adb->query($stdfiltersql); 359 $stdfilterrow = $adb->fetch_array($result); 360 if(isset($stdfilterrow)) 361 { 362 $fieldcolname = $stdfilterrow["datecolumnname"]; 363 $datefilter = $stdfilterrow["datefilter"]; 364 $startdate = $stdfilterrow["startdate"]; 365 $enddate = $stdfilterrow["enddate"]; 366 367 if($fieldcolname != "none") 368 { 369 if($datefilter == "custom") 370 { 371 if($startdate != "0000-00-00" && $enddate != "0000-00-00") 372 { 373 $selectedfields = explode(":",$fieldcolname); 374 $stdfilterlist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]." between '".$startdate."' and '".$enddate."'"; 375 } 376 }else 377 { 378 $selectedfields = explode(":",$fieldcolname); 379 $startenddate = $this->getStandarFiltersStartAndEndDate($datefilter); 380 if($startenddate[0] != "" && $startenddate[1] != "") 381 { 382 $stdfilterlist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]." between '".$startenddate[0]." 00:00:00' and '".$startenddate[1]." 23:59:00'"; 383 } 384 } 385 386 } 387 } 388 $log->info("ReportRun :: Successfully returned getStdFilterList".$reportid); 389 return $stdfilterlist; 390 } 391 392 /** Function to get the RunTime filter columns for the given $filtercolumn,$filter,$startdate,$enddate 393 * @ param $filtercolumn : Type String 394 * @ param $filter : Type String 395 * @ param $startdate: Type String 396 * @ param $enddate : Type String 397 * This function returns $stdfilterlist Array($columnname => $tablename:$columnname:$fieldlabel=>$tablename.$columnname 'between' $startdate 'and' $enddate) 398 * 399 */ 400 function RunTimeFilter($filtercolumn,$filter,$startdate,$enddate) 401 { 402 if($filtercolumn != "none") 403 { 404 if($filter == "custom") 405 { 406 if($startdate != "" && $enddate != "") 407 { 408 $selectedfields = explode(":",$filtercolumn); 409 $stdfilterlist[$filtercolumn] = $selectedfields[0].".".$selectedfields[1]." between '".$startdate."' and '".$enddate."'"; 410 } 411 }else 412 { 413 if($startdate != "" && $enddate != "") 414 { 415 $selectedfields = explode(":",$filtercolumn); 416 $startenddate = $this->getStandarFiltersStartAndEndDate($filter); 417 if($startenddate[0] != "" && $startenddate[1] != "") 418 { 419 $stdfilterlist[$filtercolumn] = $selectedfields[0].".".$selectedfields[1]." between '".$startenddate[0]." 00:00:00' and '".$startenddate[1]." 23:59:00'"; 420 } 421 } 422 } 423 424 } 425 return $stdfilterlist; 426 427 } 428 429 /** Function to get standardfilter for the given reportid 430 * @ param $reportid : Type Integer 431 * returns the query of columnlist for the selected columns 432 */ 433 434 function getStandardCriterialSql($reportid) 435 { 436 global $adb; 437 global $modules; 438 global $log; 439 440 $sreportstdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report"; 441 $sreportstdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid"; 442 $sreportstdfiltersql .= " where vtiger_report.reportid =".$reportid; 443 444 $result = $adb->query($sreportstdfiltersql); 445 $noofrows = $adb->num_rows($result); 446 447 for($i=0; $i<$noofrows; $i++) 448 { 449 $fieldcolname = $adb->query_result($result,$i,"datecolumnname"); 450 $datefilter = $adb->query_result($result,$i,"datefilter"); 451 $startdate = $adb->query_result($result,$i,"startdate"); 452 $enddate = $adb->query_result($result,$i,"enddate"); 453 454 if($fieldcolname != "none") 455 { 456 if($datefilter == "custom") 457 { 458 if($startdate != "0000-00-00" && $enddate != "0000-00-00") 459 { 460 $selectedfields = explode(":",$fieldcolname); 461 $sSQL .= $selectedfields[0].".".$selectedfields[1]." between '".$startdate."' and '".$enddate."'"; 462 } 463 }else 464 { 465 $selectedfields = explode(":",$fieldcolname); 466 $startenddate = $this->getStandarFiltersStartAndEndDate($datefilter); 467 if($startenddate[0] != "" && $startenddate[1] != "") 468 { 469 $sSQL .= $selectedfields[0].".".$selectedfields[1]." between '".$startenddate[0]."' and '".$startenddate[1]."'"; 470 } 471 } 472 } 473 } 474 $log->info("ReportRun :: Successfully returned getStandardCriterialSql".$reportid); 475 return $sSQL; 476 } 477 478 /** Function to get standardfilter startdate and enddate for the given type 479 * @ param $type : Type String 480 * returns the $datevalue Array in the given format 481 * $datevalue = Array(0=>$startdate,1=>$enddate) 482 */ 483 484 485 function getStandarFiltersStartAndEndDate($type) 486 { 487 $today = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d"), date("Y"))); 488 $tomorrow = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+1, date("Y"))); 489 $yesterday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-1, date("Y"))); 490 491 $currentmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m"), "01", date("Y"))); 492 $currentmonth1 = date("Y-m-t"); 493 $lastmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")-1, "01", date("Y"))); 494 $lastmonth1 = date("Y-m-t", strtotime("-1 Month")); 495 $nextmonth0 = date("Y-m-d",mktime(0, 0, 0, date("m")+1, "01", date("Y"))); 496 $nextmonth1 = date("Y-m-t", strtotime("+1 Month")); 497 498 $lastweek0 = date("Y-m-d",strtotime("-2 week Sunday")); 499 $lastweek1 = date("Y-m-d",strtotime("-1 week Saturday")); 500 501 $thisweek0 = date("Y-m-d",strtotime("-1 week Sunday")); 502 $thisweek1 = date("Y-m-d",strtotime("this Saturday")); 503 504 $nextweek0 = date("Y-m-d",strtotime("this Sunday")); 505 $nextweek1 = date("Y-m-d",strtotime("+1 week Saturday")); 506 507 $next7days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+6, date("Y"))); 508 $next30days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+29, date("Y"))); 509 $next60days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+59, date("Y"))); 510 $next90days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+89, date("Y"))); 511 $next120days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+119, date("Y"))); 512 513 $last7days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-6, date("Y"))); 514 $last30days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-29, date("Y"))); 515 $last60days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-59, date("Y"))); 516 $last90days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-89, date("Y"))); 517 $last120days = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-119, date("Y"))); 518 519 $currentFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y"))); 520 $currentFY1 = date("Y-m-t",mktime(0, 0, 0, "12", date("d"), date("Y"))); 521 $lastFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y")-1)); 522 $lastFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")-1)); 523 $nextFY0 = date("Y-m-d",mktime(0, 0, 0, "01", "01", date("Y")+1)); 524 $nextFY1 = date("Y-m-t", mktime(0, 0, 0, "12", date("d"), date("Y")+1)); 525 526 if($type == "today" ) 527 { 528 529 $datevalue[0] = $today; 530 $datevalue[1] = $today; 531 } 532 elseif($type == "yesterday" ) 533 { 534 535 $datevalue[0] = $yesterday; 536 $datevalue[1] = $yesterday; 537 } 538 elseif($type == "tomorrow" ) 539 { 540 541 $datevalue[0] = $tomorrow; 542 $datevalue[1] = $tomorrow; 543 } 544 elseif($type == "thisweek" ) 545 { 546 547 $datevalue[0] = $thisweek0; 548 $datevalue[1] = $thisweek1; 549 } 550 elseif($type == "lastweek" ) 551 { 552 553 $datevalue[0] = $lastweek0; 554 $datevalue[1] = $lastweek1; 555 } 556 elseif($type == "nextweek" ) 557 { 558 559 $datevalue[0] = $nextweek0; 560 $datevalue[1] = $nextweek1; 561 } 562 elseif($type == "thismonth" ) 563 { 564 565 $datevalue[0] =$currentmonth0; 566 $datevalue[1] = $currentmonth1; 567 } 568 569 elseif($type == "lastmonth" ) 570 { 571 572 $datevalue[0] = $lastmonth0; 573 $datevalue[1] = $lastmonth1; 574 } 575 elseif($type == "nextmonth" ) 576 { 577 578 $datevalue[0] = $nextmonth0; 579 $datevalue[1] = $nextmonth1; 580 } 581 elseif($type == "next7days" ) 582 { 583 584 $datevalue[0] = $today; 585 $datevalue[1] = $next7days; 586 } 587 elseif($type == "next30days" ) 588 { 589 590 $datevalue[0] =$today; 591 $datevalue[1] =$next30days; 592 } 593 elseif($type == "next60days" ) 594 { 595 596 $datevalue[0] = $today; 597 $datevalue[1] = $next60days; 598 } 599 elseif($type == "next90days" ) 600 { 601 602 $datevalue[0] = $today; 603 $datevalue[1] = $next90days; 604 } 605 elseif($type == "next120days" ) 606 { 607 608 $datevalue[0] = $today; 609 $datevalue[1] = $next120days; 610 } 611 elseif($type == "last7days" ) 612 { 613 614 $datevalue[0] = $last7days; 615 $datevalue[1] = $today; 616 } 617 elseif($type == "last30days" ) 618 { 619 620 $datevalue[0] = $last30days; 621 $datevalue[1] = $today; 622 } 623 elseif($type == "last60days" ) 624 { 625 626 $datevalue[0] = $last60days; 627 $datevalue[1] = $today; 628 } 629 else if($type == "last90days" ) 630 { 631 632 $datevalue[0] = $last90days; 633 $datevalue[1] = $today; 634 } 635 elseif($type == "last120days" ) 636 { 637 638 $datevalue[0] = $last120days; 639 $datevalue[1] = $today; 640 } 641 elseif($type == "thisfy" ) 642 { 643 644 $datevalue[0] = $currentFY0; 645 $datevalue[1] = $currentFY1; 646 } 647 elseif($type == "prevfy" ) 648 { 649 650 $datevalue[0] = $lastFY0; 651 $datevalue[1] = $lastFY1; 652 } 653 elseif($type == "nextfy" ) 654 { 655 656 $datevalue[0] = $nextFY0; 657 $datevalue[1] = $nextFY1; 658 } 659 elseif($type == "nextfq" ) 660 { 661 662 $datevalue[0] = "2005-07-01"; 663 $datevalue[1] = "2005-09-30"; 664 } 665 elseif($type == "prevfq" ) 666 { 667 668 $datevalue[0] = "2005-01-01"; 669 $datevalue[1] = "2005-03-31"; 670 } 671 elseif($type == "thisfq" ) 672 { 673 $datevalue[0] = "2005-04-01"; 674 $datevalue[1] = "2005-06-30"; 675 } 676 else 677 { 678 $datevalue[0] = ""; 679 $datevalue[1] = ""; 680 } 681 682 return $datevalue; 683 } 684 685 /** Function to get getGroupingList for the given reportid 686 * @ param $reportid : Type Integer 687 * returns the $grouplist Array in the following format 688 * $grouplist = Array($tablename:$columnname:$fieldlabel:fieldname:typeofdata=>$tablename:$columnname $sorder, 689 * $tablename1:$columnname1:$fieldlabel1:fieldname1:typeofdata1=>$tablename1:$columnname1 $sorder, 690 * $tablename2:$columnname2:$fieldlabel2:fieldname2:typeofdata2=>$tablename2:$columnname2 $sorder) 691 * This function also sets the return value in the class variable $this->groupbylist 692 */ 693 694 695 function getGroupingList($reportid) 696 { 697 global $adb; 698 global $modules; 699 global $log; 700 701 $sreportsortsql = "select vtiger_reportsortcol.* from vtiger_report"; 702 $sreportsortsql .= " inner join vtiger_reportsortcol on vtiger_report.reportid = vtiger_reportsortcol.reportid"; 703 $sreportsortsql .= " where vtiger_report.reportid =".$reportid." order by vtiger_reportsortcol.sortcolid"; 704 705 $result = $adb->query($sreportsortsql); 706 707 while($reportsortrow = $adb->fetch_array($result)) 708 { 709 $fieldcolname = $reportsortrow["columnname"]; 710 $sortorder = $reportsortrow["sortorder"]; 711 712 if($sortorder == "Ascending") 713 { 714 $sortorder = "ASC"; 715 716 }elseif($sortorder == "Descending") 717 { 718 $sortorder = "DESC"; 719 } 720 721 if($fieldcolname != "none") 722 { 723 $selectedfields = explode(":",$fieldcolname); 724 $sqlvalue = $selectedfields[0].".".$selectedfields[1]." ".$sortorder; 725 $grouplist[$fieldcolname] = $sqlvalue; 726 $this->groupbylist[$fieldcolname] = $selectedfields[0].".".$selectedfields[1]." ".$selectedfields[2]; 727 } 728 } 729 $log->info("ReportRun :: Successfully returned getGroupingList".$reportid); 730 return $grouplist; 731 } 732 733 /** function to get the selectedorderbylist for the given reportid 734 * @ param $reportid : type integer 735 * this returns the columns query for the sortorder columns 736 * this function also sets the return value in the class variable $this->orderbylistsql 737 */ 738 739 740 function getSelectedOrderbyList($reportid) 741 { 742 743 global $adb; 744 global $modules; 745 global $log; 746 747 $sreportsortsql = "select vtiger_reportsortcol.* from vtiger_report"; 748 $sreportsortsql .= " inner join vtiger_reportsortcol on vtiger_report.reportid = vtiger_reportsortcol.reportid"; 749 $sreportsortsql .= " where vtiger_report.reportid =".$reportid." order by vtiger_reportsortcol.sortcolid"; 750 751 $result = $adb->query($sreportsortsql); 752 $noofrows = $adb->num_rows($result); 753 754 for($i=0; $i<$noofrows; $i++) 755 { 756 $fieldcolname = $adb->query_result($result,$i,"columnname"); 757 $sortorder = $adb->query_result($result,$i,"sortorder"); 758 759 if($sortorder == "Ascending") 760 { 761 $sortorder = "ASC"; 762 } 763 elseif($sortorder == "Descending") 764 { 765 $sortorder = "DESC"; 766 } 767 768 if($fieldcolname != "none") 769 { 770 $this->orderbylistcolumns[] = $fieldcolname; 771 $n = $n + 1; 772 $selectedfields = explode(":",$fieldcolname); 773 if($n > 1) 774 { 775 $sSQL .= ", "; 776 $this->orderbylistsql .= ", "; 777 } 778 $sSQL .= $selectedfields[0].".".$selectedfields[1]." ".$sortorder; 779 $this->orderbylistsql .= $selectedfields[0].".".$selectedfields[1]." ".$selectedfields[2]; 780 } 781 } 782 $log->info("ReportRun :: Successfully returned getSelectedOrderbyList".$reportid); 783 return $sSQL; 784 } 785 786 /** function to get secondary Module for the given Primary module and secondary module 787 * @ param $module : type String 788 * @ param $secmodule : type String 789 * this returns join query for the given secondary module 790 */ 791 792 function getRelatedModulesQuery($module,$secmodule) 793 { 794 global $log; 795 796 if($module == "Contacts") 797 { 798 if($secmodule == "Accounts") 799 { 800 $query = "left join vtiger_account on vtiger_account.accountid = vtiger_contactdetails.accountid 801 left join vtiger_crmentity as vtiger_crmentityAccounts on vtiger_crmentityAccounts.crmid=vtiger_account.accountid 802 left join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid 803 left join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid 804 left join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid 805 left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid 806 left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentityAccounts.smownerid "; 807 } 808 if($secmodule == "Potentials") 809 { 810 $query = "left join vtiger_potential on vtiger_potential.accountid = vtiger_contactdetails.accountid 811 left join vtiger_crmentity as vtiger_crmentityPotentials on vtiger_crmentityPotentials.crmid=vtiger_potential.potentialid 812 left join vtiger_account as vtiger_accountPotentials on vtiger_potential.accountid = vtiger_accountPotentials.accountid 813 left join vtiger_potentialscf on vtiger_potentialscf.potentialid = vtiger_potential.potentialid 814 left join vtiger_users as vtiger_usersPotentials on vtiger_usersPotentials.id = vtiger_crmentityPotentials.smownerid "; 815 } 816 if($secmodule == "Quotes") 817 { 818 $query = "left join vtiger_quotes on vtiger_quotes.contactid = vtiger_contactdetails.contactid 819 left join vtiger_quotescf on vtiger_quotes.quoteid = vtiger_quotescf.quoteid 820 left join vtiger_crmentity as vtiger_crmentityQuotes on vtiger_crmentityQuotes.crmid=vtiger_quotes.quoteid 821 left join vtiger_quotesbillads on vtiger_quotes.quoteid=vtiger_quotesbillads.quotebilladdressid 822 left join vtiger_quotesshipads on vtiger_quotes.quoteid=vtiger_quotesshipads.quoteshipaddressid 823 left join vtiger_users as vtiger_usersQuotes on vtiger_usersQuotes.id = vtiger_crmentityQuotes.smownerid 824 left join vtiger_users as vtiger_usersRel1 on vtiger_usersRel1.id = vtiger_quotes.inventorymanager 825 left join vtiger_potential as vtiger_potentialRel on vtiger_potentialRel.potentialid = vtiger_quotes.potentialid 826 left join vtiger_contactdetails as vtiger_contactdetailsQuotes on vtiger_contactdetailsQuotes.contactid = vtiger_quotes.contactid 827 left join vtiger_account as vtiger_accountQuotes on vtiger_accountQuotes.accountid = vtiger_quotes.accountid "; 828 } 829 if($secmodule == "PurchaseOrder") 830 { 831 $query = "left join vtiger_purchaseorder on vtiger_purchaseorder.contactid = vtiger_contactdetails.contactid 832 left join vtiger_purchaseordercf on vtiger_purchaseorder.purchaseorderid = vtiger_purchaseordercf.purchaseorderid 833 left join vtiger_crmentity as vtiger_crmentityPurchaseOrder on vtiger_crmentityPurchaseOrder.crmid=vtiger_purchaseorder.purchaseorderid 834 left join vtiger_pobillads on vtiger_purchaseorder.purchaseorderid=vtiger_pobillads.pobilladdressid 835 left join vtiger_poshipads on vtiger_purchaseorder.purchaseorderid=vtiger_poshipads.poshipaddressid 836 left join vtiger_users as vtiger_usersPurchaseOrder on vtiger_usersPurchaseOrder.id = vtiger_crmentityPurchaseOrder.smownerid 837 left join vtiger_vendor as vtiger_vendorRel on vtiger_vendorRel.vendorid = vtiger_purchaseorder.vendorid 838 left join vtiger_contactdetails as vtiger_contactdetailsPurchaseOrder on vtiger_contactdetailsPurchaseOrder.contactid = vtiger_purchaseorder.contactid "; 839 } 840 841 } 842 843 if($module == "Accounts") 844 { 845 if($secmodule == "Potentials") 846 { 847 $query = "left join vtiger_potential on vtiger_potential.accountid = vtiger_account.accountid 848 left join vtiger_crmentity as vtiger_crmentityPotentials on vtiger_crmentityPotentials.crmid=vtiger_potential.potentialid 849 left join vtiger_potentialscf on vtiger_potentialscf.potentialid = vtiger_potential.potentialid 850 left join vtiger_users as vtiger_usersPotentials on vtiger_usersPotentials.id = vtiger_crmentityPotentials.smownerid "; 851 852 } 853 if($secmodule == "Contacts") 854 { 855 $query = "left join vtiger_contactdetails on vtiger_contactdetails.accountid = vtiger_account.accountid 856 left join vtiger_crmentity as vtiger_crmentityContacts on vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid 857 left join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid 858 left join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid 859 left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto 860 left join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid 861 left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid 862 left join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid 863 left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentityContacts.smownerid "; 864 } 865 if($secmodule == "Quotes") 866 { 867 $query = "left join vtiger_quotes on vtiger_quotes.accountid = vtiger_account.accountid 868 left join vtiger_quotescf on vtiger_quotes.quoteid = vtiger_quotescf.quoteid 869 left join vtiger_crmentity as vtiger_crmentityQuotes on vtiger_crmentityQuotes.crmid=vtiger_quotes.quoteid 870 left join vtiger_quotesbillads on vtiger_quotes.quoteid=vtiger_quotesbillads.quotebilladdressid 871 left join vtiger_quotesshipads on vtiger_quotes.quoteid=vtiger_quotesshipads.quoteshipaddressid 872 left join vtiger_users as vtiger_usersQuotes on vtiger_usersQuotes.id = vtiger_crmentityQuotes.smownerid 873 left join vtiger_users as vtiger_usersRel1 on vtiger_usersRel1.id = vtiger_quotes.inventorymanager 874 left join vtiger_potential as vtiger_potentialRel on vtiger_potentialRel.potentialid = vtiger_quotes.potentialid 875 left join vtiger_contactdetails as vtiger_contactdetailsQuotes on vtiger_contactdetailsQuotes.contactid = vtiger_quotes.contactid 876 left join vtiger_account as vtiger_accountQuotes on vtiger_accountQuotes.accountid = vtiger_quotes.accountid "; 877 } 878 if($secmodule == "PurchaseOrder") 879 { 880 $query = "left join vtiger_purchaseorder on vtiger_purchaseorder.accountid = vtiger_account.accountid 881 left join vtiger_purchaseordercf on vtiger_purchaseorder.purchaseorderid = vtiger_purchaseordercf.purchaseorderid 882 left join vtiger_crmentity as vtiger_crmentityPurchaseOrder on vtiger_crmentityPurchaseOrder.crmid=vtiger_purchaseorder.purchaseorderid 883 left join vtiger_pobillads on vtiger_purchaseorder.purchaseorderid=vtiger_pobillads.pobilladdressid 884 left join vtiger_poshipads on vtiger_purchaseorder.purchaseorderid=vtiger_poshipads.poshipaddressid 885 left join vtiger_users as vtiger_usersPurchaseOrder on vtiger_usersPurchaseOrder.id = vtiger_crmentityPurchaseOrder.smownerid 886 left join vtiger_vendor as vtiger_vendorRel on vtiger_vendorRel.vendorid = vtiger_purchaseorder.vendorid 887 left join vtiger_contactdetails as vtiger_contactdetailsPurchaseOrder on vtiger_contactdetailsPurchaseOrder.contactid = vtiger_purchaseorder.contactid "; 888 } 889 if($secmodule == "Invoice") 890 { 891 $query = "left join vtiger_invoice on vtiger_invoice.accountid = vtiger_account.accountid 892 left join vtiger_invoicecf on vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid 893 left join vtiger_crmentity as vtiger_crmentityInvoice on vtiger_crmentityInvoice.crmid=vtiger_invoice.invoiceid 894 left join vtiger_invoicebillads on vtiger_invoice.invoiceid=vtiger_invoicebillads.invoicebilladdressid 895 left join vtiger_invoiceshipads on vtiger_invoice.invoiceid=vtiger_invoiceshipads.invoiceshipaddressid 896 left join vtiger_users as vtiger_usersInvoice on vtiger_usersInvoice.id = vtiger_crmentityInvoice.smownerid 897 left join vtiger_account as vtiger_accountInvoice on vtiger_accountInvoice.accountid = vtiger_invoice.accountid "; 898 } 899 if($secmodule == "Products") 900 { 901 $query = "left join vtiger_seproductsrel on vtiger_seproductsrel.crmid = vtiger_account.accountid 902 left join vtiger_products on vtiger_products.productid = vtiger_seproductsrel.productid 903 left join vtiger_crmentity as vtiger_crmentityProducts on vtiger_crmentityProducts.crmid=vtiger_products.productid 904 left join vtiger_productcf on vtiger_products.productid = vtiger_productcf.productid 905 left join vtiger_users as vtiger_usersProducts on vtiger_usersProducts.id = vtiger_crmentityProducts.smownerid 906 left join vtiger_contactdetails as vtiger_contactdetailsProducts on vtiger_contactdetailsProducts.contactid = vtiger_products.contactid 907 left join vtiger_vendor as vtiger_vendorRel on vtiger_vendorRel.vendorid = vtiger_products.vendor_id 908 left join vtiger_crmentity as vtiger_crmentityRel on vtiger_crmentityRel.crmid = vtiger_seproductsrel.crmid 909 left join vtiger_account as vtiger_accountRel on vtiger_accountRel.accountid=vtiger_crmentityRel.crmid 910 left join vtiger_leaddetails as vtiger_leaddetailsRel on vtiger_leaddetailsRel.leadid = vtiger_crmentityRel.crmid 911 left join vtiger_potential as vtiger_potentialRel on vtiger_potentialRel.potentialid = vtiger_crmentityRel.crmid "; 912 } 913 } 914 if($module == "Quotes") 915 { 916 if($secmodule == "Accounts") 917 { 918 $query = "left join vtiger_account on vtiger_account.accountid = vtiger_quotes.accountid 919 left join vtiger_crmentity as vtiger_crmentityAccounts on vtiger_crmentityAccounts.crmid=vtiger_account.accountid 920 left join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid 921 left join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid 922 left join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid 923 left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid 924 left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentityAccounts.smownerid "; 925 } 926 if($secmodule == "Potentials") 927 { 928 $query = "left join vtiger_potential on vtiger_potential.potentialid = vtiger_quotes.potentialid 929 left join vtiger_crmentity as vtiger_crmentityPotentials on vtiger_crmentityPotentials.crmid=vtiger_potential.potentialid 930 left join vtiger_potentialscf on vtiger_potentialscf.potentialid = vtiger_potential.potentialid 931 left join vtiger_users as vtiger_usersPotentials on vtiger_usersPotentials.id = vtiger_crmentityPotentials.smownerid "; 932 933 } 934 if($secmodule == "Contacts") 935 { 936 $query = "left join vtiger_contactdetails on vtiger_contactdetails.contactid = vtiger_quotes.contactid 937 left join vtiger_crmentity as vtiger_crmentityContacts on vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid 938 left join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid 939 left join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid 940 left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto 941 left join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid 942 left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid 943 944 left join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid 945 left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentityContacts.smownerid "; 946 } 947 948 } 949 if($module == "PurchaseOrder") 950 { 951 if($secmodule == "Accounts") 952 { 953 $query = "left join vtiger_account on vtiger_account.accountid = vtiger_purchaseorder.accountid 954 left join vtiger_crmentity as vtiger_crmentityAccounts on vtiger_crmentityAccounts.crmid=vtiger_account.accountid 955 left join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid 956 left join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid 957 left join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid 958 left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid 959 left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentityAccounts.smownerid "; 960 } 961 if($secmodule == "Contacts") 962 { 963 $query = "left join vtiger_contactdetails on vtiger_contactdetails.contactid = vtiger_purchaseorder.contactid 964 left join vtiger_crmentity as vtiger_crmentityContacts on vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid 965 left join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid 966 left join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid 967 left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto 968 left join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid 969 left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid 970 971 left join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid 972 left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentityContacts.smownerid "; 973 } 974 } 975 if($module == "Invoice") 976 { 977 if($secmodule == "Accounts") 978 { 979 $query = "left join vtiger_account on vtiger_account.accountid = vtiger_invoice.accountid 980 left join vtiger_contactdetails as vtiger_contactdetailsInvoice on vtiger_contactdetailsInvoice.contactid = vtiger_invoice.contactid 981 left join vtiger_crmentity as vtiger_crmentityAccounts on vtiger_crmentityAccounts.crmid=vtiger_account.accountid 982 left join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid 983 left join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid 984 left join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid 985 left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid 986 left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentityAccounts.smownerid "; 987 } 988 } 989 if($module == "Products") 990 { 991 if($secmodule == "Accounts") 992 { 993 $query = "left join vtiger_account on vtiger_account.accountid = vtiger_crmentityRelProducts.crmid 994 left join vtiger_crmentity as vtiger_crmentityAccounts on vtiger_crmentityAccounts.crmid=vtiger_account.accountid 995 left join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid 996 left join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid 997 left join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid 998 left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid 999 left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentityAccounts.smownerid "; 1000 } 1001 if($secmodule == "Contacts") 1002 { 1003 $query = "left join vtiger_contactdetails on vtiger_contactdetails.contactid = vtiger_products.contactid 1004 left join vtiger_crmentity as vtiger_crmentityContacts on vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid 1005 left join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid 1006 left join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid 1007 left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto 1008 left join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid 1009 left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid 1010 left join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid 1011 left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentityContacts.smownerid "; 1012 1013 } 1014 1015 } 1016 if($module == "Potentials") 1017 { 1018 if($secmodule == "Accounts") 1019 { 1020 $query = "left join vtiger_account on vtiger_account.accountid = vtiger_potential.accountid 1021 left join vtiger_crmentity as vtiger_crmentityAccounts on vtiger_crmentityAccounts.crmid=vtiger_account.accountid 1022 left join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid 1023 left join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid 1024 left join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid 1025 left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid 1026 left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentityAccounts.smownerid "; 1027 } 1028 if($secmodule == "Contacts") 1029 { 1030 $query = "left join vtiger_contactdetails on vtiger_contactdetails.accountid = vtiger_potential.accountid 1031 left join vtiger_crmentity as vtiger_crmentityContacts on vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid 1032 left join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid 1033 left join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid 1034 left join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid 1035 left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto 1036 left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid 1037 left join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid 1038 left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentityContacts.smownerid "; 1039 1040 } 1041 if($secmodule == "Quotes") 1042 { 1043 $query = "left join vtiger_quotes on vtiger_quotes.potentialid = vtiger_potential.potentialid 1044 left join vtiger_crmentity as vtiger_crmentityQuotes on vtiger_crmentityQuotes.crmid=vtiger_quotes.quoteid 1045 left join vtiger_quotesbillads on vtiger_quotes.quoteid=vtiger_quotesbillads.quotebilladdressid 1046 left join vtiger_quotesshipads on vtiger_quotes.quoteid=vtiger_quotesshipads.quoteshipaddressid 1047 left join vtiger_users as vtiger_usersQuotes on vtiger_usersQuotes.id = vtiger_crmentityQuotes.smownerid 1048 left join vtiger_users as vtiger_usersRel1 on vtiger_usersRel1.id = vtiger_quotes.inventorymanager 1049 left join vtiger_potential as vtiger_potentialRel on vtiger_potentialRel.potentialid = vtiger_quotes.potentialid 1050 left join vtiger_contactdetails as vtiger_contactdetailsQuotes on vtiger_contactdetailsQuotes.contactid = vtiger_quotes.contactid 1051 left join vtiger_account as vtiger_accountQuotes on vtiger_accountQuotes.accountid = vtiger_quotes.accountid "; 1052 } 1053 } 1054 if($module == "HelpDesk") 1055 { 1056 if($secmodule == "Products") 1057 { 1058 $query = "left join vtiger_products on vtiger_products.productid = vtiger_troubletickets.product_id 1059 left join vtiger_crmentity as vtiger_crmentityProducts on vtiger_crmentityProducts.crmid=vtiger_products.productid 1060 left join vtiger_productcf on vtiger_products.productid = vtiger_productcf.productid 1061 left join vtiger_users as vtiger_usersProducts on vtiger_usersProducts.id = vtiger_crmentityProducts.smownerid 1062 left join vtiger_contactdetails as vtiger_contactdetailsProducts on vtiger_contactdetailsProducts.contactid = vtiger_products.contactid 1063 left join vtiger_vendor as vtiger_vendorRel on vtiger_vendorRel.vendorid = vtiger_products.vendor_id 1064 left join vtiger_seproductsrel on vtiger_seproductsrel.productid = vtiger_products.productid 1065 left join vtiger_crmentity as vtiger_crmentityRelProducts on vtiger_crmentityRelProducts.crmid = vtiger_seproductsrel.crmid 1066 left join vtiger_account as vtiger_accountRelProducts on vtiger_accountRelProducts.accountid=vtiger_seproductsrel.crmid 1067 left join vtiger_leaddetails as vtiger_leaddetailsRelProducts on vtiger_leaddetailsRelProducts.leadid = vtiger_seproductsrel.crmid 1068 left join vtiger_potential as vtiger_potentialRelProducts on vtiger_potentialRelProducts.potentialid = vtiger_seproductsrel.crmid "; 1069 } 1070 } 1071 if($module == "Calendar") 1072 { 1073 if($secmodule == "Contacts") 1074 { 1075 $query = "left join vtiger_contactdetails on vtiger_contactdetails.contactid = vtiger_cntactivityrel.contactid 1076 left join vtiger_crmentity as vtiger_crmentityContacts on vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid 1077 left join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid 1078 left join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid 1079 left join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid 1080 left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto 1081 left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid 1082 left join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid 1083 left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentityContacts.smownerid "; 1084 } 1085 } 1086 if($module == 'Campaigns') 1087 { 1088 if($secmodule == 'Products') 1089 { 1090 $query = "left join vtiger_products on vtiger_products.productid = vtiger_campaign.product_id 1091 left join vtiger_crmentity as vtiger_crmentityProducts on vtiger_crmentityProducts.crmid=vtiger_products.productid 1092 left join vtiger_productcf on vtiger_products.productid = vtiger_productcf.productid 1093 left join vtiger_users as vtiger_usersProducts on vtiger_usersProducts.id = vtiger_crmentityProducts.smownerid 1094 left join vtiger_contactdetails as vtiger_contactdetailsProducts on vtiger_contactdetailsProducts.contactid = vtiger_products.contactid 1095 left join vtiger_vendor as vtiger_vendorRel on vtiger_vendorRel.vendorid = vtiger_products.vendor_id 1096 left join vtiger_seproductsrel on vtiger_seproductsrel.productid = vtiger_products.productid 1097 left join vtiger_crmentity as vtiger_crmentityRelProducts on vtiger_crmentityRelProducts.crmid = vtiger_seproductsrel.crmid 1098 left join vtiger_account as vtiger_accountRelProducts on vtiger_accountRelProducts.accountid=vtiger_seproductsrel.crmid 1099 left join vtiger_leaddetails as vtiger_leaddetailsRelProducts on vtiger_leaddetailsRelProducts.leadid = vtiger_seproductsrel.crmid 1100 left join vtiger_potential as vtiger_potentialRelProducts on vtiger_potentialRelProducts.potentialid = vtiger_seproductsrel.crmid "; 1101 } 1102 } 1103 $log->info("ReportRun :: Successfully returned getRelatedModulesQuery".$secmodule); 1104 return $query; 1105 } 1106 /** function to get report query for the given module 1107 * @ param $module : type String 1108 * this returns join query for the given module 1109 */ 1110 1111 function getReportsQuery($module) 1112 { 1113 global $log; 1114 if($module == "Leads") 1115 { 1116 $query = "from vtiger_leaddetails 1117 inner join vtiger_crmentity as vtiger_crmentityLeads on vtiger_crmentityLeads.crmid=vtiger_leaddetails.leadid 1118 inner join vtiger_leadsubdetails on vtiger_leadsubdetails.leadsubscriptionid=vtiger_leaddetails.leadid 1119 inner join vtiger_leadaddress on vtiger_leadaddress.leadaddressid=vtiger_leadsubdetails.leadsubscriptionid 1120 inner join vtiger_leadscf on vtiger_leaddetails.leadid = vtiger_leadscf.leadid 1121 left join vtiger_users as vtiger_usersLeads on vtiger_usersLeads.id = vtiger_crmentityLeads.smownerid 1122 where vtiger_crmentityLeads.deleted=0 and vtiger_leaddetails.converted=0"; 1123 } 1124 if($module == "Accounts") 1125 { 1126 $query = "from vtiger_account 1127 inner join vtiger_crmentity as vtiger_crmentityAccounts on vtiger_crmentityAccounts.crmid=vtiger_account.accountid 1128 inner join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid 1129 inner join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid 1130 inner join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid 1131 left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid 1132 left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentityAccounts.smownerid 1133 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1134 where vtiger_crmentityAccounts.deleted=0 "; 1135 } 1136 1137 if($module == "Contacts") 1138 { 1139 $query = "from vtiger_contactdetails 1140 inner join vtiger_crmentity as vtiger_crmentityContacts on vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid 1141 inner join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid 1142 inner join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid 1143 inner join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid 1144 inner join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid 1145 left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto 1146 left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid 1147 left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentityContacts.smownerid 1148 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1149 where vtiger_crmentityContacts.deleted=0"; 1150 } 1151 1152 if($module == "Potentials") 1153 { 1154 $query = "from vtiger_potential 1155 inner join vtiger_crmentity as vtiger_crmentityPotentials on vtiger_crmentityPotentials.crmid=vtiger_potential.potentialid 1156 inner join vtiger_account as vtiger_accountPotentials on vtiger_potential.accountid = vtiger_accountPotentials.accountid 1157 inner join vtiger_potentialscf on vtiger_potentialscf.potentialid = vtiger_potential.potentialid 1158 left join vtiger_users as vtiger_usersPotentials on vtiger_usersPotentials.id = vtiger_crmentityPotentials.smownerid 1159 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1160 where vtiger_crmentityPotentials.deleted=0 "; 1161 } 1162 1163 if($module == "Products") 1164 { 1165 $query = "from vtiger_products 1166 inner join vtiger_crmentity as vtiger_crmentityProducts on vtiger_crmentityProducts.crmid=vtiger_products.productid 1167 left join vtiger_productcf on vtiger_products.productid = vtiger_productcf.productid 1168 left join vtiger_users as vtiger_usersProducts on vtiger_usersProducts.id = vtiger_crmentityProducts.smownerid 1169 left join vtiger_contactdetails as vtiger_contactdetailsProducts on vtiger_contactdetailsProducts.contactid = vtiger_products.contactid 1170 left join vtiger_vendor as vtiger_vendorRel on vtiger_vendorRel.vendorid = vtiger_products.vendor_id 1171 left join vtiger_seproductsrel on vtiger_seproductsrel.productid = vtiger_products.productid 1172 left join vtiger_crmentity as vtiger_crmentityRelProducts on vtiger_crmentityRelProducts.crmid = vtiger_seproductsrel.crmid 1173 left join vtiger_account as vtiger_accountRelProducts on vtiger_accountRelProducts.accountid=vtiger_crmentityRelProducts.crmid 1174 left join vtiger_leaddetails as vtiger_leaddetailsRelProducts on vtiger_leaddetailsRelProducts.leadid = vtiger_crmentityRelProducts.crmid 1175 left join vtiger_potential as vtiger_potentialRelProducts on vtiger_potentialRelProducts.potentialid = vtiger_crmentityRelProducts.crmid 1176 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1177 where vtiger_crmentityProducts.deleted=0 "; 1178 } 1179 1180 if($module == "HelpDesk") 1181 { 1182 $query = "from vtiger_troubletickets 1183 inner join vtiger_crmentity as vtiger_crmentityHelpDesk 1184 on vtiger_crmentityHelpDesk.crmid=vtiger_troubletickets.ticketid 1185 inner join vtiger_ticketcf on vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid 1186 left join vtiger_crmentity as vtiger_crmentityRelHelpDesk on vtiger_crmentityRelHelpDesk.crmid = vtiger_troubletickets.parent_id 1187 left join vtiger_account as vtiger_accountRelHelpDesk on vtiger_accountRelHelpDesk.accountid=vtiger_crmentityRelHelpDesk.crmid 1188 left join vtiger_contactdetails as vtiger_contactdetailsRelHelpDesk on vtiger_contactdetailsRelHelpDesk.contactid= vtiger_crmentityRelHelpDesk.crmid 1189 left join vtiger_products as vtiger_productsRel on vtiger_productsRel.productid = vtiger_troubletickets.product_id 1190 left join vtiger_users as vtiger_usersHelpDesk on vtiger_crmentityHelpDesk.smownerid=vtiger_usersHelpDesk.id 1191 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1192 where vtiger_crmentityHelpDesk.deleted=0 "; 1193 } 1194 1195 if($module == "Calendar") 1196 { 1197 $query = "from vtiger_activity 1198 inner join vtiger_crmentity as vtiger_crmentityCalendar on vtiger_crmentityCalendar.crmid=vtiger_activity.activityid 1199 left join vtiger_cntactivityrel on vtiger_cntactivityrel.activityid= vtiger_activity.activityid 1200 left join vtiger_contactdetails as vtiger_contactdetailsCalendar on vtiger_contactdetailsCalendar.contactid= vtiger_cntactivityrel.contactid 1201 left join vtiger_users as vtiger_usersCalendar on vtiger_usersCalendar.id = vtiger_crmentityCalendar.smownerid 1202 left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_activity.activityid 1203 left join vtiger_crmentity as vtiger_crmentityRelCalendar on vtiger_crmentityRelCalendar.crmid = vtiger_seactivityrel.crmid 1204 left join vtiger_account as vtiger_accountRelCalendar on vtiger_accountRelCalendar.accountid=vtiger_crmentityRelCalendar.crmid 1205 left join vtiger_leaddetails as vtiger_leaddetailsRelCalendar on vtiger_leaddetailsRelCalendar.leadid = vtiger_crmentityRelCalendar.crmid 1206 left join vtiger_potential as vtiger_potentialRelCalendar on vtiger_potentialRelCalendar.potentialid = vtiger_crmentityRelCalendar.crmid 1207 left join vtiger_quotes as vtiger_quotesRelCalendar on vtiger_quotesRelCalendar.quoteid = vtiger_crmentityRelCalendar.crmid 1208 left join vtiger_purchaseorder as vtiger_purchaseorderRelCalendar on vtiger_purchaseorderRelCalendar.purchaseorderid = vtiger_crmentityRelCalendar.crmid 1209 left join vtiger_invoice as vtiger_invoiceRelCalendar on vtiger_invoiceRelCalendar.invoiceid = vtiger_crmentityRelCalendar.crmid 1210 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1211 WHERE vtiger_crmentityCalendar.deleted=0 and (vtiger_activity.activitytype = 'Meeting' or vtiger_activity.activitytype='Call' or vtiger_activity.activitytype='Task')"; 1212 } 1213 1214 if($module == "Quotes") 1215 { 1216 $query = "from vtiger_quotes 1217 inner join vtiger_crmentity as vtiger_crmentityQuotes on vtiger_crmentityQuotes.crmid=vtiger_quotes.quoteid 1218 inner join vtiger_quotesbillads on vtiger_quotes.quoteid=vtiger_quotesbillads.quotebilladdressid 1219 inner join vtiger_quotesshipads on vtiger_quotes.quoteid=vtiger_quotesshipads.quoteshipaddressid 1220 left join vtiger_quotescf on vtiger_quotes.quoteid = vtiger_quotescf.quoteid 1221 left join vtiger_users as vtiger_usersQuotes on vtiger_usersQuotes.id = vtiger_crmentityQuotes.smownerid 1222 left join vtiger_users as vtiger_usersRel1 on vtiger_usersRel1.id = vtiger_quotes.inventorymanager 1223 left join vtiger_potential as vtiger_potentialRel on vtiger_potentialRel.potentialid = vtiger_quotes.potentialid 1224 left join vtiger_contactdetails as vtiger_contactdetailsQuotes on vtiger_contactdetailsQuotes.contactid = vtiger_quotes.contactid 1225 left join vtiger_account as vtiger_accountQuotes on vtiger_accountQuotes.accountid = vtiger_quotes.accountid 1226 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1227 where vtiger_crmentityQuotes.deleted=0"; 1228 } 1229 1230 if($module == "PurchaseOrder") 1231 { 1232 $query = "from vtiger_purchaseorder 1233 inner join vtiger_crmentity as vtiger_crmentityPurchaseOrder on vtiger_crmentityPurchaseOrder.crmid=vtiger_purchaseorder.purchaseorderid 1234 inner join vtiger_pobillads on vtiger_purchaseorder.purchaseorderid=vtiger_pobillads.pobilladdressid 1235 inner join vtiger_poshipads on vtiger_purchaseorder.purchaseorderid=vtiger_poshipads.poshipaddressid 1236 left join vtiger_purchaseordercf on vtiger_purchaseorder.purchaseorderid = vtiger_purchaseordercf.purchaseorderid 1237 left join vtiger_users as vtiger_usersPurchaseOrder on vtiger_usersPurchaseOrder.id = vtiger_crmentityPurchaseOrder.smownerid 1238 left join vtiger_vendor as vtiger_vendorRel on vtiger_vendorRel.vendorid = vtiger_purchaseorder.vendorid 1239 left join vtiger_contactdetails as vtiger_contactdetailsPurchaseOrder on vtiger_contactdetailsPurchaseOrder.contactid = vtiger_purchaseorder.contactid 1240 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1241 where vtiger_crmentityPurchaseOrder.deleted=0"; 1242 } 1243 1244 if($module == "Invoice") 1245 { 1246 $query = "from vtiger_invoice 1247 inner join vtiger_crmentity as vtiger_crmentityInvoice on vtiger_crmentityInvoice.crmid=vtiger_invoice.invoiceid 1248 inner join vtiger_invoicebillads on vtiger_invoice.invoiceid=vtiger_invoicebillads.invoicebilladdressid 1249 inner join vtiger_invoiceshipads on vtiger_invoice.invoiceid=vtiger_invoiceshipads.invoiceshipaddressid 1250 left join vtiger_invoicecf on vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid 1251 left join vtiger_users as vtiger_usersInvoice on vtiger_usersInvoice.id = vtiger_crmentityInvoice.smownerid 1252 left join vtiger_account as vtiger_accountInvoice on vtiger_accountInvoice.accountid = vtiger_invoice.accountid 1253 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1254 where vtiger_crmentityInvoice.deleted=0"; 1255 } 1256 if($module == "SalesOrder") 1257 { 1258 $query = "from vtiger_salesorder 1259 inner join vtiger_crmentity as vtiger_crmentitySalesOrder on vtiger_crmentitySalesOrder.crmid=vtiger_salesorder.salesorderid 1260 inner join vtiger_sobillads on vtiger_salesorder.salesorderid=vtiger_sobillads.sobilladdressid 1261 inner join vtiger_soshipads on vtiger_salesorder.salesorderid=vtiger_soshipads.soshipaddressid 1262 left join vtiger_salesordercf on vtiger_salesorder.salesorderid = vtiger_salesordercf.salesorderid 1263 left join vtiger_contactdetails as vtiger_contactdetailsSalesOrder on vtiger_contactdetailsSalesOrder.contactid = vtiger_salesorder.contactid 1264 left join vtiger_quotes as vtiger_quotesSalesOrder on vtiger_quotesSalesOrder.quoteid = vtiger_salesorder.quoteid 1265 left join vtiger_account as vtiger_accountSalesOrder on vtiger_accountSalesOrder.accountid = vtiger_salesorder.accountid 1266 left join vtiger_potential as vtiger_potentialRel on vtiger_potentialRel.potentialid = vtiger_salesorder.potentialid 1267 left join vtiger_users as vtiger_usersSalesOrder on vtiger_usersSalesOrder.id = vtiger_crmentitySalesOrder.smownerid 1268 where vtiger_crmentitySalesOrder.deleted=0"; 1269 1270 1271 } 1272 if($module == "Campaigns") 1273 { 1274 $query = "from vtiger_campaign 1275 inner join vtiger_campaignscf as vtiger_campaignscf on vtiger_campaignscf.campaignid=vtiger_campaign.campaignid 1276 inner join vtiger_crmentity as vtiger_crmentityCampaigns on vtiger_crmentityCampaigns.crmid=vtiger_campaign.campaignid 1277 1278 left join vtiger_users as vtiger_usersCampaigns on vtiger_usersCampaigns.id = vtiger_crmentityCampaigns.smownerid 1279 ".$this->getRelatedModulesQuery($module,$this->secondarymodule)." 1280 where vtiger_crmentityCampaigns.deleted=0"; 1281 } 1282 $log->info("ReportRun :: Successfully returned getReportsQuery".$module); 1283 return $query; 1284 } 1285 1286 1287 /** function to get query for the given reportid,filterlist,type 1288 * @ param $reportid : Type integer 1289 * @ param $filterlist : Type Array 1290 * @ param $module : Type String 1291 * this returns join query for the report 1292 */ 1293 1294 function sGetSQLforReport($reportid,$filterlist,$type='') 1295 { 1296 global $log; 1297 1298 $columnlist = $this->getQueryColumnsList($reportid); 1299 $groupslist = $this->getGroupingList($reportid); 1300 $stdfilterlist = $this->getStdFilterList($reportid); 1301 $columnstotallist = $this->getColumnsTotal($reportid); 1302 $advfilterlist = $this->getAdvFilterList($reportid); 1303 $this->totallist = $columnstotallist; 1304 if($this->reporttype == "summary") 1305 { 1306 if(isset($this->groupbylist)) 1307 { 1308 $newcolumnlist = array_diff($columnlist, $this->groupbylist); 1309 $selectlist = array_merge($this->groupbylist,$newcolumnlist); 1310 }else 1311 { 1312 $selectlist = $columnlist; 1313 } 1314 }else 1315 { 1316 $selectlist = $columnlist; 1317 } 1318 1319 //columns list 1320 if(isset($selectlist)) 1321 { 1322 $selectedcolumns = implode(", ",$selectlist); 1323 } 1324 //groups list 1325 if(isset($groupslist)) 1326 { 1327 $groupsquery = implode(", ",$groupslist); 1328 } 1329 1330 //standard list 1331 if(isset($stdfilterlist)) 1332 { 1333 $stdfiltersql = implode(", ",$stdfilterlist); 1334 } 1335 if(isset($filterlist)) 1336 { 1337 $stdfiltersql = implode(", ",$filterlist); 1338 } 1339 //columns to total list 1340 if(isset($columnstotallist)) 1341 { 1342 $columnstotalsql = implode(", ",$columnstotallist); 1343 } 1344 //advanced filterlist 1345 if(isset($advfilterlist)) 1346 { 1347 $advfiltersql = implode(" and ",$advfilterlist); 1348 } 1349 if($stdfiltersql != "") 1350 { 1351 $wheresql = " and ".$stdfiltersql; 1352 } 1353 if($advfiltersql != "") 1354 { 1355 $wheresql .= " and ".$advfiltersql; 1356 } 1357 1358 $reportquery = $this->getReportsQuery($this->primarymodule); 1359 1360 if($type == 'COLUMNSTOTOTAL') 1361 { 1362 if(trim($groupsquery) != "") 1363 { 1364 if($columnstotalsql != '') 1365 { 1366 $reportquery = "select ".$columnstotalsql." ".$reportquery." ".$wheresql. " order by ".$groupsquery; 1367 } 1368 }else 1369 { 1370 if($columnstotalsql != '') 1371 { 1372 $reportquery = "select ".$columnstotalsql." ".$reportquery." ".$wheresql; 1373 } 1374 } 1375 }else 1376 { 1377 if(trim($groupsquery) != "") 1378 { 1379 $reportquery = "select ".$selectedcolumns." ".$reportquery." ".$wheresql. " order by ".$groupsquery; 1380 }else 1381 { 1382 $reportquery = "select ".$selectedcolumns." ".$reportquery." ".$wheresql; 1383 } 1384 } 1385 $log->info("ReportRun :: Successfully returned sGetSQLforReport".$reportid); 1386 return $reportquery; 1387 1388 } 1389 1390 /** function to get the report output in HTML,PDF,TOTAL,PRINT,PRINTTOTAL formats depends on the argument $outputformat 1391 * @ param $outputformat : Type String (valid parameters HTML,PDF,TOTAL,PRINT,PRINT_TOTAL) 1392 * @ param $filterlist : Type Array 1393 * This returns HTML Report if $outputformat is HTML 1394 * Array for PDF if $outputformat is PDF 1395 * HTML strings for TOTAL if $outputformat is TOTAL 1396 * Array for PRINT if $outputformat is PRINT 1397 * HTML strings for TOTAL fields if $outputformat is PRINTTOTAL 1398 * HTML strings for 1399 */ 1400 1401 function GenerateReport($outputformat,$filterlist) 1402 { 1403 global $adb,$current_user; 1404 global $modules; 1405 global $mod_strings,$current_language; 1406 1407 if($outputformat == "HTML") 1408 { 1409 $sSQL = $this->sGetSQLforReport($this->reportid,$filterlist); 1410 $result = $adb->query($sSQL); 1411 $y=$adb->num_fields($result); 1412 1413 if($result) 1414 { 1415 for ($x=0; $x<$y; $x++) 1416 { 1417 $fld = $adb->field_name($result, $x); 1418 $header .= "<td class='rptCellLabel'>".$this->getLstringforReportHeaders($fld->name)."</td>"; 1419 } 1420 1421 $noofrows = $adb->num_rows($result); 1422 $custom_field_values = $adb->fetch_array($result); 1423 $groupslist = $this->getGroupingList($this->reportid); 1424 do 1425 { 1426 $arraylists = Array(); 1427 if(count($groupslist) == 1) 1428 { 1429 $newvalue = $custom_field_values[0]; 1430 }elseif(count($groupslist) == 2) 1431 { 1432 $newvalue = $custom_field_values[0]; 1433 $snewvalue = $custom_field_values[1]; 1434 }elseif(count($groupslist) == 3) 1435 { 1436 $newvalue = $custom_field_values[0]; 1437 $snewvalue = $custom_field_values[1]; 1438 $tnewvalue = $custom_field_values[2]; 1439 } 1440 1441 if($newvalue == "") $newvalue = "-"; 1442 1443 if($snewvalue == "") $snewvalue = "-"; 1444 1445 if($tnewvalue == "") $tnewvalue = "-"; 1446 1447 $valtemplate .= "<tr>"; 1448 1449 for ($i=0; $i<$y; $i++) 1450 { 1451 $fld = $adb->field_name($result, $i); 1452 if ($fld->name == "Potentials_Amount") 1453 $fieldvalue = convertFromMasterCurrency($custom_field_values[$i],$current_user->conv_rate); 1454 else 1455 $fieldvalue = $custom_field_values[$i]; 1456 if($fieldvalue == "" ) 1457 { 1458 $fieldvalue = "-"; 1459 } 1460 if(($lastvalue == $fieldvalue) && $this->reporttype == "summary") 1461 { 1462 if($this->reporttype == "summary") 1463 { 1464 $valtemplate .= "<td class='rptEmptyGrp'> </td>"; 1465 }else 1466 { 1467 $valtemplate .= "<td class='rptData'>".$fieldvalue."</td>"; 1468 } 1469 }else if(($secondvalue == $fieldvalue) && $this->reporttype == "summary") 1470 { 1471 if($lastvalue == $newvalue) 1472 { 1473 $valtemplate .= "<td class='rptEmptyGrp'> </td>"; 1474 }else 1475 { 1476 $valtemplate .= "<td class='rptGrpHead'>".$fieldvalue."</td>"; 1477 } 1478 } 1479 else if(($thirdvalue == $fieldvalue) && $this->reporttype == "summary") 1480 { 1481 if($secondvalue == $snewvalue) 1482 { 1483 $valtemplate .= "<td class='rptEmptyGrp'> </td>"; 1484 }else 1485 { 1486 $valtemplate .= "<td class='rptGrpHead'>".$fieldvalue."</td>"; 1487 } 1488 } 1489 else 1490 { 1491 if($this->reporttype == "tabular") 1492 { 1493 $valtemplate .= "<td class='rptData'>".$fieldvalue."</td>"; 1494 }else 1495 { 1496 $valtemplate .= "<td class='rptGrpHead'>".$fieldvalue."</td>"; 1497 } 1498 } 1499 } 1500 $valtemplate .= "</tr>"; 1501 $lastvalue = $newvalue; 1502 $secondvalue = $snewvalue; 1503 $thirdvalue = $tnewvalue; 1504 $arr_val[] = $arraylists; 1505 }while($custom_field_values = $adb->fetch_array($result)); 1506 1507 $sHTML ='<table cellpadding="5" cellspacing="0" align="center" class="rptTable"> 1508 <tr>'. 1509 $header 1510 .'<!-- BEGIN values --> 1511 <tr>'. 1512 $valtemplate 1513 .'</tr> 1514 </table>'; 1515 //<<<<<<<<construct HTML>>>>>>>>>>>> 1516 $return_data[] = $sHTML; 1517 $return_data[] = $noofrows; 1518 $return_data[] = $sSQL; 1519 return $return_data; 1520 } 1521 }elseif($outputformat == "PDF") 1522 { 1523 1524 $sSQL = $this->sGetSQLforReport($this->reportid,$filterlist); 1525 $result = $adb->query($sSQL); 1526 $y=$adb->num_fields($result); 1527 1528 if($result) 1529 { 1530 $noofrows = $adb->num_rows($result); 1531 $custom_field_values = $adb->fetch_array($result); 1532 1533 do 1534 { 1535 $arraylists = Array(); 1536 for ($i=0; $i<$y; $i++) 1537 { 1538 $fld = $adb->field_name($result, $i); 1539 if ($fld->name == "Potentials_Amount") 1540 $fieldvalue = convertFromMasterCurrency($custom_field_values[$i],$current_user->conv_rate); 1541 else 1542 $fieldvalue = $custom_field_values[$i]; 1543 1544 if($fieldvalue == "" ) 1545 { 1546 $fieldvalue = "-"; 1547 } 1548 $arraylists[str_replace($modules," ",$this->getLstringforReportHeaders($fld->name))] = $fieldvalue; 1549 } 1550 $arr_val[] = $arraylists; 1551 }while($custom_field_values = $adb->fetch_array($result)); 1552 1553 return $arr_val; 1554 } 1555 }elseif($outputformat == "TOTALHTML") 1556 { 1557 $escapedchars = Array('_SUM','_AVG','_MIN','_MAX'); 1558 $sSQL = $this->sGetSQLforReport($this->reportid,$filterlist,"COLUMNSTOTOTAL"); 1559 if(isset($this->totallist)) 1560 { 1561 if($sSQL != "") 1562 { 1563 $result = $adb->query($sSQL); 1564 $y=$adb->num_fields($result); 1565 $custom_field_values = $adb->fetch_array($result); 1566 1567 $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>"; 1568 1569 foreach($this->totallist as $key=>$value) 1570 { 1571 $fieldlist = explode(":",$key); 1572 $totclmnflds[str_replace($escapedchars," ",$fieldlist[3])] = str_replace($escapedchars," ",$fieldlist[3]); 1573 } 1574 1575 for($i =0;$i<$y;$i++) 1576 { 1577 $fld = $adb->field_name($result, $i); 1578 $keyhdr[$fld->name] = $custom_field_values[$i]; 1579 1580 } 1581 foreach($totclmnflds as $key=>$value) 1582 { 1583 1584 $coltotalhtml .= '<tr class="rptGrpHead" valign=top><td class="rptData">'.str_replace($modules," ",$value).'</td>'; 1585 $arraykey = trim($value).'_SUM'; 1586 if(isset($keyhdr[$arraykey])) 1587 { 1588 $coltotalhtml .= '<td class="rptTotal">'.convertFromMasterCurrency($keyhdr[$arraykey],$current_user->conv_rate).'</td>'; 1589 }else 1590 { 1591 $coltotalhtml .= '<td class="rptTotal"> </td>'; 1592 } 1593 1594 $arraykey = trim($value).'_AVG'; 1595 if(isset($keyhdr[$arraykey])) 1596 { 1597 $coltotalhtml .= '<td class="rptTotal">'.convertFromMasterCurrency($keyhdr[$arraykey],$current_user->conv_rate).'</td>'; 1598 }else 1599 { 1600 $coltotalhtml .= '<td class="rptTotal"> </td>'; 1601 } 1602 1603 $arraykey = trim($value).'_MIN'; 1604 if(isset($keyhdr[$arraykey])) 1605 { 1606 $coltotalhtml .= '<td class="rptTotal">'.convertFromMasterCurrency($keyhdr[$arraykey],$current_user->conv_rate).'</td>'; 1607 }else 1608 { 1609 $coltotalhtml .= '<td class="rptTotal"> </td>'; 1610 } 1611 1612 $arraykey = trim($value).'_MAX'; 1613 if(isset($keyhdr[$arraykey])) 1614 { 1615 $coltotalhtml .= '<td class="rptTotal">'.convertFromMasterCurrency($keyhdr[$arraykey],$current_user->conv_rate).'</td>'; 1616 }else 1617 { 1618 $coltotalhtml .= '<td class="rptTotal"> </td>'; 1619 } 1620 1621 $coltotalhtml .= '<tr>'; 1622 } 1623 1624 $coltotalhtml .= "</table>"; 1625 } 1626 } 1627 return $coltotalhtml; 1628 }elseif($outputformat == "PRINT") 1629 { 1630 $sSQL = $this->sGetSQLforReport($this->reportid,$filterlist); 1631 $result = $adb->query($sSQL); 1632 $y=$adb->num_fields($result); 1633 1634 if($result) 1635 { 1636 for ($x=0; $x<$y; $x++) 1637 { 1638 $fld = $adb->field_name($result, $x); 1639 $header .= "<th>".$this->getLstringforReportHeaders($fld->name)."</th>"; 1640 } 1641 1642 $noofrows = $adb->num_rows($result); 1643 $custom_field_values = $adb->fetch_array($result); 1644 $groupslist = $this->getGroupingList($this->reportid); 1645 1646 do 1647 { 1648 $arraylists = Array(); 1649 if(count($groupslist) == 1) 1650 { 1651 $newvalue = $custom_field_values[0]; 1652 }elseif(count($groupslist) == 2) 1653 { 1654 $newvalue = $custom_field_values[0]; 1655 $snewvalue = $custom_field_values[1]; 1656 }elseif(count($groupslist) == 3) 1657 { 1658 $newvalue = $custom_field_values[0]; 1659 $snewvalue = $custom_field_values[1]; 1660 $tnewvalue = $custom_field_values[2]; 1661 } 1662 1663 if($newvalue == "") $newvalue = "-"; 1664 1665 if($snewvalue == "") $snewvalue = "-"; 1666 1667 if($tnewvalue == "") $tnewvalue = "-"; 1668 1669 $valtemplate .= "<tr>"; 1670 1671 for ($i=0; $i<$y; $i++) 1672 { 1673 $fld = $adb->field_name($result, $i); 1674 if ($fld->name == "Potentials_Amount") 1675 $fieldvalue = convertFromMasterCurrency($custom_field_values[$i],$current_user->conv_rate); 1676 else 1677 $fieldvalue = $custom_field_values[$i]; 1678 1679 if($fieldvalue == "" ) 1680 { 1681 $fieldvalue = "-"; 1682 } 1683 if(($lastvalue == $fieldvalue) && $this->reporttype == "summary") 1684 { 1685 if($this->reporttype == "summary") 1686 { 1687 $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; 1688 }else 1689 { 1690 $valtemplate .= "<td>".$fieldvalue."</td>"; 1691 } 1692 }else if(($secondvalue == $fieldvalue) && $this->reporttype == "summary") 1693 { 1694 if($lastvalue == $newvalue) 1695 { 1696 $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; 1697 }else 1698 { 1699 $valtemplate .= "<td>".$fieldvalue."</td>"; 1700 } 1701 } 1702 else if(($thirdvalue == $fieldvalue) && $this->reporttype == "summary") 1703 { 1704 if($secondvalue == $snewvalue) 1705 { 1706 $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; 1707 }else 1708 { 1709 $valtemplate .= "<td>".$fieldvalue."</td>"; 1710 } 1711 } 1712 else 1713 { 1714 if($this->reporttype == "tabular") 1715 { 1716 $valtemplate .= "<td>".$fieldvalue."</td>"; 1717 }else 1718 { 1719 $valtemplate .= "<td>".$fieldvalue."</td>"; 1720 } 1721 } 1722 } 1723 $valtemplate .= "</tr>"; 1724 $lastvalue = $newvalue; 1725 $secondvalue = $snewvalue; 1726 $thirdvalue = $tnewvalue; 1727 $arr_val[] = $arraylists; 1728 }while($custom_field_values = $adb->fetch_array($result)); 1729 1730 $sHTML = '<tr>'.$header.'</tr>'.$valtemplate; 1731 $return_data[] = $sHTML; 1732 $return_data[] = $noofrows; 1733 return $return_data; 1734 } 1735 }elseif($outputformat == "PRINT_TOTAL") 1736 { 1737 $escapedchars = Array('_SUM','_AVG','_MIN','_MAX'); 1738 $sSQL = $this->sGetSQLforReport($this->reportid,$filterlist,"COLUMNSTOTOTAL"); 1739 if(isset($this->totallist)) 1740 { 1741 if($sSQL != "") 1742 { 1743 $result = $adb->query($sSQL); 1744 $y=$adb->num_fields($result); 1745 $custom_field_values = $adb->fetch_array($result); 1746 1747 $coltotalhtml .= '<table width="100%" border="0" cellpadding="5" cellspacing="0" align="center" class="printReport" ><tr><th>'.$mod_strings[Totals].'</th><th>'.$mod_strings[SUM].'</th><th>'.$mod_strings[AVG].'</th><th>'.$mod_strings[MIN].'</th><th>'.$mod_strings[MAX].'</th></tr>'; 1748 1749 foreach($this->totallist as $key=>$value) 1750 { 1751 $fieldlist = explode(":",$key); 1752 $totclmnflds[str_replace($escapedchars," ",$fieldlist[3])] = str_replace($escapedchars," ",$fieldlist[3]); 1753 } 1754 1755 for($i =0;$i<$y;$i++) 1756 { 1757 $fld = $adb->field_name($result, $i); 1758 $keyhdr[$fld->name] = $custom_field_values[$i]; 1759 1760 } 1761 foreach($totclmnflds as $key=>$value) 1762 { 1763 1764 $coltotalhtml .= '<tr valign=top><td>'.str_replace($modules," ",$value).'</td>'; 1765 $arraykey = trim($value).'_SUM'; 1766 if(isset($keyhdr[$arraykey])) 1767 { 1768 $coltotalhtml .= '<td>'.$keyhdr[$arraykey].'</td>'; 1769 }else 1770 { 1771 $coltotalhtml .= '<td> </td>'; 1772 } 1773 1774 $arraykey = trim($value).'_AVG'; 1775 if(isset($keyhdr[$arraykey])) 1776 { 1777 $coltotalhtml .= '<td>'.$keyhdr[$arraykey].'</td>'; 1778 }else 1779 { 1780 $coltotalhtml .= '<td> </td>'; 1781 } 1782 1783 $arraykey = trim($value).'_MIN'; 1784 if(isset($keyhdr[$arraykey])) 1785 { 1786 $coltotalhtml .= '<td>'.$keyhdr[$arraykey].'</td>'; 1787 }else 1788 { 1789 $coltotalhtml .= '<td> </td>'; 1790 } 1791 1792 $arraykey = trim($value).'_MAX'; 1793 if(isset($keyhdr[$arraykey])) 1794 { 1795 $coltotalhtml .= '<td>'.$keyhdr[$arraykey].'</td>'; 1796 }else 1797 { 1798 $coltotalhtml .= '<td> </td>'; 1799 } 1800 1801 $coltotalhtml .= '<tr>'; 1802 } 1803 1804 $coltotalhtml .= "</table>"; 1805 } 1806 } 1807 return $coltotalhtml; 1808 } 1809 } 1810 1811 //<<<<<<<new>>>>>>>>>> 1812 function getColumnsTotal($reportid) 1813 { 1814 global $adb; 1815 global $modules; 1816 global $log; 1817 1818 $coltotalsql = "select vtiger_reportsummary.* from vtiger_report"; 1819 $coltotalsql .= " inner join vtiger_reportsummary on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid"; 1820 $coltotalsql .= " where vtiger_report.reportid =".$reportid; 1821 1822 $result = $adb->query($coltotalsql); 1823 1824 while($coltotalrow = $adb->fetch_array($result)) 1825 { 1826 $fieldcolname = $coltotalrow["columnname"]; 1827 1828 if($fieldcolname != "none") 1829 { 1830 $fieldlist = explode(":",$fieldcolname); 1831 if($fieldlist[4] == 2) 1832 { 1833 $stdfilterlist[$fieldcolname] = "sum(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; 1834 } 1835 if($fieldlist[4] == 3) 1836 { 1837 $stdfilterlist[$fieldcolname] = "avg(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; 1838 } 1839 if($fieldlist[4] == 4) 1840 { 1841 $stdfilterlist[$fieldcolname] = "min(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; 1842 } 1843 if($fieldlist[4] == 5) 1844 { 1845 $stdfilterlist[$fieldcolname] = "max(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; 1846 } 1847 } 1848 } 1849 $log->info("ReportRun :: Successfully returned getColumnsTotal".$reportid); 1850 return $stdfilterlist; 1851 } 1852 //<<<<<<new>>>>>>>>> 1853 1854 1855 /** function to get query for the columns to total for the given reportid 1856 * @ param $reportid : Type integer 1857 * This returns columnstoTotal query for the reportid 1858 */ 1859 1860 function getColumnsToTotalColumns($reportid) 1861 { 1862 global $adb; 1863 global $modules; 1864 global $log; 1865 1866 $sreportstdfiltersql = "select vtiger_reportsummary.* from vtiger_report"; 1867 $sreportstdfiltersql .= " inner join vtiger_reportsummary on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid"; 1868 $sreportstdfiltersql .= " where vtiger_report.reportid =".$reportid; 1869 1870 $result = $adb->query($sreportstdfiltersql); 1871 $noofrows = $adb->num_rows($result); 1872 1873 for($i=0; $i<$noofrows; $i++) 1874 { 1875 $fieldcolname = $adb->query_result($result,$i,"columnname"); 1876 1877 if($fieldcolname != "none") 1878 { 1879 $fieldlist = explode(":",$fieldcolname); 1880 if($fieldlist[4] == 2) 1881 { 1882 $sSQLList[] = "sum(".$fieldlist[1].".".$fieldlist[2].") ".$fieldlist[3]; 1883 } 1884 if($fieldlist[4] == 3) 1885 { 1886 $sSQLList[] = "avg(".$fieldlist[1].".".$fieldlist[2].") ".$fieldlist[3]; 1887 } 1888 if($fieldlist[4] == 4) 1889 { 1890 $sSQLList[] = "min(".$fieldlist[1].".".$fieldlist[2].") ".$fieldlist[3]; 1891 } 1892 if($fieldlist[4] == 5) 1893 { 1894 $sSQLList[] = "max(".$fieldlist[1].".".$fieldlist[2].") ".$fieldlist[3]; 1895 } 1896 } 1897 } 1898 if(isset($sSQLList)) 1899 { 1900 $sSQL = implode(",",$sSQLList); 1901 } 1902 $log->info("ReportRun :: Successfully returned getColumnsToTotalColumns".$reportid); 1903 return $sSQL; 1904 } 1905 /** Function to convert the Report Header Names into i18n 1906 * @param $fldname: Type Varchar 1907 * Returns Language Converted Header Strings 1908 **/ 1909 function getLstringforReportHeaders($fldname) 1910 { 1911 global $modules,$current_language,$current_user; 1912 $rep_header = ltrim(str_replace($modules," ",$fldname)); 1913 $rep_header_temp = ereg_replace(" ","_",$rep_header); 1914 $rep_module = ereg_replace('_'.$rep_header_temp,"",$fldname); 1915 $temp_mod_strings = return_module_language($current_language,$rep_module); 1916 $curr_symb = ""; 1917 if($rep_header == 'Amount') 1918 $curr_symb = "(in ".$current_user->currency_symbol.")"; 1919 if($temp_mod_strings[$rep_header] != '') 1920 { 1921 $rep_header = $temp_mod_strings[$rep_header]; 1922 $rep_header .=$curr_symb; 1923 } 1924 return $rep_header; 1925 } 1926 1927 } 1928 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 10:22:19 2007 | par Balluche grâce à PHPXref 0.7 |