[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  vimport('~~/modules/Reports/Reports.php');
  11  vimport('~~/modules/Reports/ReportRun.php');
  12  require_once ('modules/Reports/ReportUtils.php');
  13  require_once ('Report.php');
  14  
  15  class Reports_Record_Model extends Vtiger_Record_Model {
  16      /**
  17       * Function to get the id of the Report
  18       * @return <Number> - Report Id
  19       */
  20  	public function getId() {
  21          return $this->get('reportid');
  22      }
  23  
  24      /**
  25       * Function to set the id of the Report
  26       * @param <type> $value - id value
  27       * @return <Object> - current instance
  28       */
  29  	public function setId($value) {
  30          return $this->set('reportid', $value);
  31      }
  32  
  33      /**
  34       * Fuction to get the Name of the Report
  35       * @return <String>
  36       */
  37  	function getName() {
  38          return $this->get('reportname');
  39      }
  40  
  41      /**
  42       * Function deletes the Report
  43       * @return Boolean
  44       */
  45  	function delete() {
  46          return $this->getModule()->deleteRecord($this);
  47      }
  48  
  49      /**
  50       * Function to get the detail view url
  51       * @return <String>
  52       */
  53  	function getDetailViewUrl() {
  54          $module = $this->getModule();
  55          $reporttype = $this->get('reporttype');
  56          if($reporttype == 'chart'){
  57              $view = 'ChartDetail';
  58          } else {
  59              $view = $module->getDetailViewName();
  60          }
  61          return 'index.php?module='.$this->getModuleName().'&view='.$view.'&record='.$this->getId();
  62      }
  63  
  64      /**
  65       * Function to get the edit view url
  66       * @return <String>
  67       */
  68  	function getEditViewUrl() {
  69          $module = $this->getModule();
  70          $reporttype = $this->get('reporttype');
  71          if($reporttype == 'chart'){
  72              $view = 'ChartEdit';
  73          } else {
  74              $view = $module->getEditViewName();
  75          }
  76          return 'index.php?module='.$this->getModuleName().'&view='.$view.'&record='.$this->getId();
  77      }
  78  
  79      /**
  80       * Funtion to get Duplicate Record Url
  81       * @return <String>
  82       */
  83  	public function getDuplicateRecordUrl() {
  84          $module = $this->getModule();
  85          $reporttype = $this->get('reporttype');
  86          if($reporttype == 'chart'){
  87              $view = 'ChartEdit';
  88          } else {
  89              $view = $module->getEditViewName();
  90          }
  91          return 'index.php?module='.$this->getModuleName().'&view='.$view.'&record='.$this->getId().'&isDuplicate=true';
  92      }
  93  
  94      /**
  95       * Function returns the url that generates Report in Excel format
  96       * @return <String>
  97       */
  98  	function getReportExcelURL() {
  99          return 'index.php?module='.$this->getModuleName().'&view=ExportReport&mode=GetXLS&record='. $this->getId();
 100      }
 101  
 102      /**
 103       * Function returns the url that generates Report in CSV format
 104       * @return <String>
 105       */
 106  	function getReportCSVURL() {
 107          return 'index.php?module='.$this->getModuleName().'&view=ExportReport&mode=GetCSV&record='. $this->getId();
 108      }
 109  
 110      /**
 111       * Function returns the url that generates Report in printable format
 112       * @return <String>
 113       */
 114  	function getReportPrintURL() {
 115          return 'index.php?module='.$this->getModuleName().'&view=ExportReport&mode=GetPrintReport&record='. $this->getId();
 116      }
 117  
 118      /**
 119       * Function returns the Reports Model instance
 120       * @param <Number> $recordId
 121       * @param <String> $module
 122       * @return <Reports_Record_Model>
 123       */
 124  	public static function getInstanceById($recordId) {
 125          $db = PearDatabase::getInstance();
 126  
 127          $self = new self();
 128          $reportResult = $db->pquery('SELECT * FROM vtiger_report WHERE reportid = ?', array($recordId));
 129          if($db->num_rows($reportResult)) {
 130              $values = $db->query_result_rowdata($reportResult, 0);
 131              $module = Vtiger_Module_Model::getInstance('Reports');
 132              $self->setData($values)->setId($values['reportid'])->setModuleFromInstance($module);
 133              $self->initialize();
 134          }
 135          return $self;
 136      }
 137  
 138      /**
 139       * Function creates Reports_Record_Model
 140       * @param <Number> $recordId
 141       * @return <Reports_Record_Model>
 142       */
 143  	public static function getCleanInstance($recordId = null) {
 144          if(empty($recordId)) {
 145              $self = new Reports_Record_Model();
 146          } else {
 147              $self = self::getInstanceById($recordId);
 148          }
 149          $self->initialize();
 150          $module = Vtiger_Module_Model::getInstance('Reports');
 151          $self->setModuleFromInstance($module);
 152          return $self;
 153      }
 154  
 155      /**
 156       * Function initializes Report
 157       */
 158  	function initialize() {
 159          $reportId = $this->getId();
 160          $this->report = Vtiger_Report_Model::getInstance($reportId);
 161      }
 162  
 163  
 164      /**
 165       * Function returns Primary Module of the Report
 166       * @return <String>
 167       */
 168  	function getPrimaryModule() {
 169          return $this->report->primodule;
 170      }
 171  
 172      /**
 173       * Function returns Secondary Module of the Report
 174       * @return <String>
 175       */
 176  	function getSecondaryModules() {
 177          return $this->report->secmodule;
 178      }
 179  
 180      /**
 181       * Function sets the Primary Module of the Report
 182       * @param <String> $module
 183       */
 184  	function setPrimaryModule($module) {
 185          $this->report->primodule = $module;
 186      }
 187  
 188      /**
 189       * Function sets the Secondary Modules for the Report
 190       * @param <String> $modules, modules separated with colon(:)
 191       */
 192  	function setSecondaryModule($modules) {
 193          $this->report->secmodule = $modules;
 194      }
 195  
 196      /**
 197       * Function returns Report Type(Summary/Tabular)
 198       * @return <String>
 199       */
 200  	function getReportType() {
 201          $reportType = $this->get('reporttype');
 202          if(!empty($reportType)) {
 203              return $reportType;
 204          }
 205          return $this->report->reporttype;
 206      }
 207  
 208      /**
 209       * Returns the Reports Owner
 210       * @return <Number>
 211       */
 212  	function getOwner() {
 213          return $this->get('owner');
 214      }
 215  
 216      /**
 217       * Function checks if the Report is editable
 218       * @return boolean
 219       */
 220  	function isEditable() {
 221          return ($this->report->isEditable());
 222      }
 223  
 224      /**
 225       * Function returns Report enabled Modules
 226       * @return type
 227       */
 228  	function getReportRelatedModules() {
 229          $report = $this->report;
 230          return $report->related_modules;
 231      }
 232  
 233      function getModulesList() {
 234          return $this->report->getModulesList();
 235      }
 236      /**
 237       * Function returns Primary Module Fields
 238       * @return <Array>
 239       */
 240  	function getPrimaryModuleFields() {
 241          $report = $this->report;
 242          $primaryModule = $this->getPrimaryModule();
 243          $report->getPriModuleColumnsList($primaryModule);
 244          //need to add this vtiger_crmentity:crmid:".$module."_ID:crmid:I
 245          return $report->pri_module_columnslist;
 246      }
 247  
 248      /**
 249       * Function returns Secondary Module fields
 250       * @return <Array>
 251       */
 252  	function getSecondaryModuleFields() {
 253          $report = $this->report;
 254          $secondaryModule = $this->getSecondaryModules();
 255          $report->getSecModuleColumnsList($secondaryModule);
 256          return $report->sec_module_columnslist;
 257      }
 258  
 259      /**
 260       * Function returns Report Selected Fields
 261       * @return <Array>
 262       */
 263  	function getSelectedFields() {
 264          $db = PearDatabase::getInstance();
 265  
 266          $result = $db->pquery("SELECT vtiger_selectcolumn.columnname FROM vtiger_report
 267                      INNER JOIN vtiger_selectquery ON vtiger_selectquery.queryid = vtiger_report.queryid
 268                      INNER JOIN vtiger_selectcolumn ON vtiger_selectcolumn.queryid = vtiger_selectquery.queryid
 269                      WHERE vtiger_report.reportid = ? ORDER BY vtiger_selectcolumn.columnindex", array($this->getId()));
 270  
 271          $selectedColumns = array();
 272          for($i=0; $i<$db->num_rows($result); $i++) {
 273              $column = $db->query_result($result, $i, 'columnname');
 274              list($tableName, $columnName, $moduleFieldLabel, $fieldName, $type) = split(':', $column);
 275              $fieldLabel  = explode('_', $moduleFieldLabel);
 276              $module = $fieldLabel[0];
 277              $dbFieldLabel = trim(str_replace(array($module, '_'), " ", $moduleFieldLabel));
 278              $translatedFieldLabel = vtranslate($dbFieldLabel, $module);
 279              if(CheckFieldPermission($fieldName, $module) == 'true' && $columnName != 'crmid') {
 280                  $selectedColumns[$translatedFieldLabel] = $column;
 281              }
 282          }
 283          return $selectedColumns;
 284      }
 285  
 286      /**
 287       * Function returns Report Calculation Fields
 288       * @return type
 289       */
 290  	function getSelectedCalculationFields() {
 291          $db = PearDatabase::getInstance();
 292  
 293          $result = $db->pquery('SELECT vtiger_reportsummary.columnname FROM vtiger_reportsummary
 294                      INNER JOIN vtiger_report ON vtiger_report.reportid = vtiger_reportsummary.reportsummaryid
 295                      WHERE vtiger_report.reportid=?', array($this->getId()));
 296  
 297          $columns = array();
 298          for($i=0; $i<$db->num_rows($result); $i++) {
 299              $columns[] = $db->query_result($result, $i, 'columnname');
 300          }
 301          return $columns;
 302      }
 303  
 304      /**
 305       * Function returns Report Sort Fields
 306       * @return type
 307       */
 308  	function getSelectedSortFields() {
 309          $db = PearDatabase::getInstance();
 310  
 311          //TODO : handle date fields with group criteria
 312          $result = $db->pquery('SELECT vtiger_reportsortcol.* FROM vtiger_report
 313                      INNER JOIN vtiger_reportsortcol ON vtiger_report.reportid = vtiger_reportsortcol.reportid
 314                      WHERE vtiger_report.reportid = ? ORDER BY vtiger_reportsortcol.sortcolid',array($this->getId()));
 315  
 316          $sortColumns = array();
 317          for($i=0; $i<$db->num_rows($result); $i++) {
 318              $column = $db->query_result($result, $i, 'columnname');
 319              $order = $db->query_result($result, $i, 'sortorder');
 320              $sortColumns[decode_html($column)] = $order;
 321          }
 322          return $sortColumns;
 323      }
 324  
 325      /**
 326       * Function returns Reports Standard Filters
 327       * @return type
 328       */
 329  	function getSelectedStandardFilter() {
 330          $db = PearDatabase::getInstance();
 331  
 332          $result = $db->pquery('SELECT * FROM vtiger_reportdatefilter WHERE datefilterid = ? AND startdate != ? AND enddate != ?',
 333                                                                          array($this->getId(), '0000-00-00', '0000-00-00'));
 334          $standardFieldInfo = array();
 335          if($db->num_rows($result)) {
 336              $standardFieldInfo['columnname'] = $db->query_result($result, 0, 'datecolumnname');
 337              $standardFieldInfo['type'] = $db->query_result($result, 0, 'datefilter');
 338              $standardFieldInfo['startdate'] = $db->query_result($result, 0, 'startdate');
 339              $standardFieldInfo['enddate'] = $db->query_result($result, 0, 'enddate');
 340  
 341              if ($standardFieldInfo['type'] == "custom" || $standardFieldInfo['type'] == "") {
 342                  if ($standardFieldInfo["startdate"] != "0000-00-00" && $standardFieldInfo["startdate"] != "") {
 343                      $startDateTime = new DateTimeField($standardFieldInfo["startdate"] . ' ' . date('H:i:s'));
 344                      $standardFieldInfo["startdate"] = $startDateTime->getDisplayDate();
 345                  }
 346                  if ($standardFieldInfo["enddate"] != "0000-00-00" && $standardFieldInfo["enddate"] != "") {
 347                      $endDateTime = new DateTimeField($standardFieldInfo["enddate"] . ' ' . date('H:i:s'));
 348                      $standardFieldInfo["enddate"] = $endDateTime->getDisplayDate();
 349                  }
 350              } else {
 351                  $startDateTime = new DateTimeField($standardFieldInfo["startdate"] . ' ' . date('H:i:s'));
 352                  $standardFieldInfo["startdate"] = $startDateTime->getDisplayDate();
 353                  $endDateTime = new DateTimeField($standardFieldInfo["enddate"] . ' ' . date('H:i:s'));
 354                  $standardFieldInfo["enddate"] = $endDateTime->getDisplayDate();
 355              }
 356          }
 357  
 358          return $standardFieldInfo;
 359      }
 360  
 361      /**
 362       * Function returns Reports Advanced Filters
 363       * @return type
 364       */
 365  	function getSelectedAdvancedFilter() {
 366          $report = $this->report;
 367          $report->getAdvancedFilterList($this->getId());
 368          return $report->advft_criteria;
 369      }
 370  
 371      /**
 372       * Function saves a Report
 373       */
 374  	function save() {
 375          $db = PearDatabase::getInstance();
 376          $currentUser = Users_Record_Model::getCurrentUserModel();
 377  
 378          $reportId = $this->getId();
 379          if(empty($reportId)) {
 380              $reportId = $db->getUniqueID("vtiger_selectquery");
 381              $this->setId($reportId);
 382  
 383              $db->pquery('INSERT INTO vtiger_selectquery(queryid, startindex, numofobjects) VALUES(?,?,?)',
 384                      array($reportId, 0, 0));
 385  
 386              $reportParams = array($reportId, $this->get('folderid'), $this->get('reportname'), $this->get('description'),
 387                      $this->get('reporttype', 'tabular'), $reportId, 'CUSTOM', $currentUser->id, 'Public');
 388              $db->pquery('INSERT INTO vtiger_report(reportid, folderid, reportname, description,
 389                                  reporttype, queryid, state, owner, sharingtype) VALUES(?,?,?,?,?,?,?,?,?)', $reportParams);
 390  
 391  
 392              $secondaryModule = $this->getSecondaryModules();
 393              $db->pquery('INSERT INTO vtiger_reportmodules(reportmodulesid, primarymodule, secondarymodules) VALUES(?,?,?)',
 394                      array($reportId, $this->getPrimaryModule(), $secondaryModule));
 395  
 396              $this->saveSelectedFields();
 397  
 398              $this->saveSortFields();
 399  
 400              $this->saveCalculationFields();
 401  
 402              $this->saveStandardFilter();
 403  
 404              $this->saveAdvancedFilters();
 405  
 406              $this->saveReportType();
 407  
 408              $this->saveSharingInformation();
 409          } else {
 410  
 411              $reportId = $this->getId();
 412              $db->pquery('DELETE FROM vtiger_selectcolumn WHERE queryid = ?', array($reportId));
 413              $this->saveSelectedFields();
 414  
 415              $db->pquery("DELETE FROM vtiger_reportsharing WHERE reportid = ?", array($reportId));
 416              $this->saveSharingInformation();
 417  
 418  
 419              $db->pquery('UPDATE vtiger_reportmodules SET primarymodule = ?,secondarymodules = ? WHERE reportmodulesid = ?',
 420                      array($this->getPrimaryModule(), $this->getSecondaryModules(), $reportId));
 421  
 422              $db->pquery('UPDATE vtiger_report SET reportname = ?, description = ?, reporttype = ?, folderid = ? WHERE
 423                  reportid = ?', array($this->get('reportname'), $this->get('description'), $this->get('reporttype'), $this->get('folderid'), $reportId));
 424  
 425  
 426              $db->pquery('DELETE FROM vtiger_reportsortcol WHERE reportid = ?', array($reportId));
 427              $db->pquery('DELETE FROM vtiger_reportgroupbycolumn WHERE reportid = ?',array($reportId));
 428              $this->saveSortFields();
 429  
 430              $db->pquery('DELETE FROM vtiger_reportsummary WHERE reportsummaryid = ?', array($reportId));
 431              $this->saveCalculationFields();
 432  
 433              $db->pquery('DELETE FROM vtiger_reportdatefilter WHERE datefilterid = ?', array($reportId));
 434              $this->saveStandardFilter();
 435  
 436              $this->saveReportType();
 437  
 438              $this->saveAdvancedFilters();
 439          }
 440      }
 441  
 442      /**
 443       * Function saves Reports Sorting Fields
 444       */
 445  	function saveSortFields() {
 446          $db = PearDatabase::getInstance();
 447  
 448          $sortFields = $this->get('sortFields');
 449  
 450          if(!empty($sortFields)){
 451              $i = 0;
 452              foreach($sortFields as $fieldInfo) {
 453                  $db->pquery('INSERT INTO vtiger_reportsortcol(sortcolid, reportid, columnname, sortorder) VALUES (?,?,?,?)',
 454                          array($i, $this->getId(), $fieldInfo[0], $fieldInfo[1]));
 455                  if(IsDateField($fieldInfo[0])) {
 456                      if(empty($fieldInfo[2])){
 457                          $fieldInfo[2] = 'None';
 458                      }
 459                      $db->pquery("INSERT INTO vtiger_reportgroupbycolumn(reportid, sortid, sortcolname, dategroupbycriteria)
 460                          VALUES(?,?,?,?)", array($this->getId(), $i, $fieldInfo[0], $fieldInfo[2]));
 461                  }
 462                  $i++;
 463              }
 464          }
 465      }
 466  
 467      /**
 468       * Function saves Reports Calculation Fields information
 469       */
 470  	function saveCalculationFields() {
 471          $db = PearDatabase::getInstance();
 472  
 473          $calculationFields = $this->get('calculationFields');
 474          for ($i=0; $i<count($calculationFields); $i++) {
 475              $db->pquery('INSERT INTO vtiger_reportsummary (reportsummaryid, summarytype, columnname) VALUES (?,?,?)',
 476                      array($this->getId(), $i, $calculationFields[$i]));
 477          }
 478      }
 479  
 480      /**
 481       * Function saves Reports Standard Filter information
 482       */
 483  	function saveStandardFilter() {
 484          $db = PearDatabase::getInstance();
 485  
 486          $standardFilter = $this->get('standardFilter');
 487          if(!empty($standardFilter)) {
 488              $db->pquery('INSERT INTO vtiger_reportdatefilter (datefilterid, datecolumnname, datefilter, startdate, enddate)
 489                              VALUES (?,?,?,?,?)', array($this->getId(), $standardFilter['field'], $standardFilter['type'],
 490                      $standardFilter['start'], $standardFilter['end']));
 491          }
 492      }
 493  
 494      /**
 495       * Function saves Reports Sharing information
 496       */
 497  	function saveSharingInformation() {
 498          $db = PearDatabase::getInstance();
 499  
 500          $sharingInfo = $this->get('sharingInfo');
 501          for($i=0; $i<count($sharingInfo); $i++) {
 502              $db->pquery('INSERT INTO vtiger_reportsharing(reportid, shareid, setype) VALUES (?,?,?)',
 503                      array($this->getId(), $sharingInfo[$i]['id'], $sharingInfo[$i]['type']));
 504          }
 505      }
 506  
 507      /**
 508       * Functions saves Reports selected fields
 509       */
 510  	function saveSelectedFields() {
 511          $db = PearDatabase::getInstance();
 512  
 513          $selectedFields = $this->get('selectedFields');
 514  
 515          if(!empty($selectedFields)){
 516             for($i=0 ;$i<count($selectedFields);$i++) {
 517                  if(!empty($selectedFields[$i])) {
 518                      $db->pquery("INSERT INTO vtiger_selectcolumn(queryid, columnindex, columnname) VALUES (?,?,?)",
 519                              array($this->getId(), $i, decode_html($selectedFields[$i])));
 520                  }
 521              }
 522          }
 523      }
 524  
 525      /**
 526       * Function saves Reports Filter information
 527       */
 528  	function saveAdvancedFilters() {
 529          $db = PearDatabase::getInstance();
 530  
 531          $reportId = $this->getId();
 532          $advancedFilter = $this->get('advancedFilter');
 533          if(!empty($advancedFilter)) {
 534  
 535              $db->pquery('DELETE FROM vtiger_relcriteria WHERE queryid = ?', array($reportId));
 536              $db->pquery('DELETE FROM vtiger_relcriteria_grouping WHERE queryid = ?', array($reportId));
 537  
 538              foreach($advancedFilter as $groupIndex => $groupInfo) {
 539                  if(empty($groupInfo)) continue;
 540  
 541                  $groupColumns = $groupInfo['columns'];
 542                  $groupCondition = $groupInfo['condition'];
 543  
 544                  foreach($groupColumns as $columnIndex => $columnCondition) {
 545                      if(empty($columnCondition)) continue;
 546  
 547                      $advFilterColumn = $columnCondition["columnname"];
 548                      $advFilterComparator = $columnCondition["comparator"];
 549                      $advFilterValue = $columnCondition["value"];
 550                      $advFilterColumnCondition = $columnCondition["column_condition"];
 551  
 552                      $columnInfo = explode(":",$advFilterColumn);
 553                      $moduleFieldLabel = $columnInfo[2];
 554  
 555                      list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
 556                      $fieldInfo = getFieldByReportLabel($module, $fieldLabel);
 557                      $fieldType = null;
 558                      if(!empty($fieldInfo)) {
 559                          $field = WebserviceField::fromArray($db, $fieldInfo);
 560                          $fieldType = $field->getFieldDataType();
 561                      }
 562  
 563                      if($fieldType == 'currency') {
 564                          if($field->getUIType() == '72') {
 565                              // Some of the currency fields like Unit Price, Totoal , Sub-total - doesn't need currency conversion during save
 566                              $advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue, null, true);
 567                          } else {
 568                              $advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue);
 569                          }
 570                      }
 571  
 572                      $tempVal = explode(",",$advFilterValue);
 573                      if(($columnInfo[4] == 'D' || ($columnInfo[4] == 'T' && $columnInfo[1] != 'time_start' && $columnInfo[1] != 'time_end') ||
 574                                      ($columnInfo[4] == 'DT')) && ($columnInfo[4] != '' && $advFilterValue != '' )) {
 575                          $val = Array();
 576                          for($i=0; $i<count($tempVal); $i++) {
 577                              if(trim($tempVal[$i]) != '') {
 578                                  $date = new DateTimeField(trim($tempVal[$i]));
 579                                  if($columnInfo[4] == 'D') {
 580                                      $val[$i] = DateTimeField::convertToDBFormat(trim($tempVal[$i]));
 581                                  } elseif($columnInfo[4] == 'DT') {
 582                                      /**
 583                                       * While generating query to retrieve report, for date time fields we are only taking
 584                                       * date field and appending '00:00:00' for correct results depending on time zone.
 585                                       * If you save the time also here by converting to db format, while showing in edit
 586                                       * view it was changing the date selected.
 587                                       */
 588                                      $values = explode(' ', $tempVal[$i]);
 589                                      $date = new DateTimeField($values[0]);
 590                                      $val[$i] = $date->getDBInsertDateValue();
 591                                  } else {
 592                                      $val[$i] = $date->getDBInsertTimeValue();
 593                                  }
 594                              }
 595                          }
 596                          $advFilterValue = implode(",", $val);
 597                      }
 598  
 599                      $db->pquery('INSERT INTO vtiger_relcriteria (queryid, columnindex, columnname, comparator, value,
 600                          groupid, column_condition) VALUES (?,?,?,?,?,?,?)', array($reportId, $columnIndex, $advFilterColumn,
 601                              $advFilterComparator, $advFilterValue, $groupIndex, $advFilterColumnCondition));
 602  
 603                      // Update the condition expression for the group to which the condition column belongs
 604                      $groupConditionExpression = '';
 605                      if(!empty($advancedFilter[$groupIndex]["conditionexpression"])) {
 606                          $groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"];
 607                      }
 608                      $groupConditionExpression = $groupConditionExpression .' '. $columnIndex .' '. $advFilterColumnCondition;
 609                      $advancedFilter[$groupIndex]["conditionexpression"] = $groupConditionExpression;
 610                  }
 611  
 612                  $groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"];
 613                  if(empty($groupConditionExpression)) continue; // Case when the group doesn't have any column criteria
 614  
 615                  $db->pquery("INSERT INTO vtiger_relcriteria_grouping(groupid, queryid, group_condition, condition_expression) VALUES (?,?,?,?)",
 616                          array($groupIndex, $reportId, $groupCondition, $groupConditionExpression));
 617              }
 618          }
 619      }
 620  
 621      /**
 622       * Function saves Reports Scheduling information
 623       */
 624  	function saveScheduleInformation() {
 625          $db = PearDatabase::getInstance();
 626  
 627          $selectedRecipients = $this->get('selectedRecipients');
 628          $scheduledInterval = $this->get('scheduledInterval');
 629          $scheduledFormat = $this->get('scheduledFormat');
 630  
 631          $db->pquery('INSERT INTO vtiger_scheduled_reports(reportid, recipients, schedule, format, next_trigger_time) VALUES
 632              (?,?,?,?,?)', array($this->getId(), $selectedRecipients, $scheduledInterval, $scheduledFormat, date("Y-m-d H:i:s")));
 633      }
 634  
 635      /**
 636       * Function deletes report scheduling information
 637       */
 638  	function deleteScheduling() {
 639          $db = PearDatabase::getInstance();
 640          $db->pquery('DELETE FROM vtiger_scheduled_reports WHERE reportid = ?', array($this->getId()));
 641      }
 642  
 643      /**
 644       * Function returns sql for the report
 645       * @param <String> $advancedFilterSQL
 646       * @param <String> $format
 647       * @return <String>
 648       */
 649  	function getReportSQL($advancedFilterSQL=false, $format=false) {
 650          $reportRun = ReportRun::getInstance($this->getId());
 651          $sql = $reportRun->sGetSQLforReport($this->getId(), $advancedFilterSQL, $format);
 652          return $sql;
 653      }
 654  
 655      /**
 656       * Function returns sql for count query which don't need any fields
 657       * @param <String> $query (with all columns)
 658       * @return <String> $query (by removing all columns)
 659       */
 660      function generateCountQuery($query){
 661          $from = explode(' from ' , $query);
 662          //If we select the same field in select and grouping/soring then it will include order by and query failure will happen
 663          $fromAndWhereQuery = explode(' order by ', $from[1]);
 664          $sql = "SELECT count(*) AS count FROM ".$fromAndWhereQuery[0];
 665          return $sql;
 666      }
 667      /**
 668       * Function returns report's data
 669       * @param <Vtiger_Paging_Model> $pagingModel
 670       * @param <String> $filterQuery
 671       * @return <Array>
 672       */
 673  	function getReportData($pagingModel = false, $filterQuery = false) {
 674          $reportRun = ReportRun::getInstance($this->getId());
 675          $data = $reportRun->GenerateReport('PDF', $filterQuery, true, $pagingModel->getStartIndex(), $pagingModel->getPageLimit());
 676          return $data;
 677      }
 678  
 679      function getReportsCount($query = null){
 680          if($query == null)
 681              $query = $this->get('recordCountQuery');
 682          global $adb;
 683          $count = 0;
 684          $result = $adb->query($query, array());
 685          if($adb->num_rows($result) > 0 ){
 686              $count = $adb->query_result($result, 0, 'count');
 687          }
 688          return $count;
 689      }
 690  
 691  	function getReportCalulationData($filterQuery = false) {
 692          $reportRun = ReportRun::getInstance($this->getId());
 693          $data = $reportRun->GenerateReport('TOTALXLS', $filterQuery, true);
 694          return $data;
 695      }
 696      /**
 697       * Function exports reports data into a Excel file
 698       */
 699  	function getReportXLS() {
 700          $reportRun = ReportRun::getInstance($this->getId());
 701          $advanceFilterSql = $this->getAdvancedFilterSQL();
 702          $rootDirectory = vglobal('root_directory');
 703          $tmpDir = vglobal('tmp_dir');
 704  
 705          $tempFileName = tempnam($rootDirectory.$tmpDir, 'xls');
 706          $fileName = decode_html($this->getName()).'.xls';
 707          $reportRun->writeReportToExcelFile($tempFileName, $advanceFilterSql);
 708  
 709          if(isset($_SERVER['HTTP_USER_AGENT']) && strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')) {
 710              header('Pragma: public');
 711              header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 712          }
 713  
 714          header('Content-Type: application/x-msexcel');
 715          header('Content-Length: '.@filesize($tempFileName));
 716          header('Content-disposition: attachment; filename="'.$fileName.'"');
 717  
 718          $fp = fopen($tempFileName, 'rb');
 719          fpassthru($fp);
 720          //unlink($tempFileName);
 721      }
 722  
 723      /**
 724       * Function exports reports data into a csv file
 725       */
 726  	function getReportCSV() {
 727          $reportRun = ReportRun::getInstance($this->getId());
 728          $advanceFilterSql = $this->getAdvancedFilterSQL();
 729          $rootDirectory = vglobal('root_directory');
 730          $tmpDir = vglobal('tmp_dir');
 731  
 732          $tempFileName = tempnam($rootDirectory.$tmpDir, 'csv');
 733          $reportRun->writeReportToCSVFile($tempFileName, $advanceFilterSql);
 734          $fileName = decode_html($this->getName()).'.csv';
 735  
 736          if(isset($_SERVER['HTTP_USER_AGENT']) && strpos($_SERVER['HTTP_USER_AGENT'],'MSIE')) {
 737              header('Pragma: public');
 738              header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 739          }
 740  
 741          header('Content-Type: application/csv');
 742          header('Content-Length: '.@filesize($tempFileName));
 743          header('Content-disposition: attachment; filename="'.$fileName.'"');
 744  
 745          $fp = fopen($tempFileName, 'rb');
 746          fpassthru($fp);
 747      }
 748  
 749      /**
 750       * Function returns data in printable format
 751       * @return <Array>
 752       */
 753  	function getReportPrint() {
 754          $reportRun = ReportRun::getInstance($this->getId());
 755          $advanceFilterSql = $this->getAdvancedFilterSQL();
 756          $data = array();
 757          $data['data'] = $reportRun->GenerateReport('PRINT', $advanceFilterSql);
 758          $data['total'] = $reportRun->GenerateReport('PRINT_TOTAL', $advanceFilterSql);
 759          return $data;
 760      }
 761  
 762      /**
 763       * Function returns reports is default or not
 764       * @return <boolean>
 765       */
 766  	function isDefault() {
 767          if ($this->get('state') == 'SAVED') {
 768              return true;
 769          }
 770          return false;
 771      }
 772  
 773      /**
 774       * Function move report to another specified folder
 775       * @param folderid
 776       */
 777  	function move($folderId) {
 778          $db = PearDatabase::getInstance();
 779  
 780          $db->pquery("UPDATE vtiger_report SET folderid = ? WHERE reportid = ?", array($folderId, $this->getId()));
 781      }
 782  
 783      /**
 784       * Function to get Calculation fields for Primary module
 785       * @return <Array> Primary module calculation fields
 786       */
 787  	function getPrimaryModuleCalculationFields() {
 788          $primaryModule = $this->getPrimaryModule();
 789          $primaryModuleFields = $this->getPrimaryModuleFields();
 790          $calculationFields = array();
 791          foreach ($primaryModuleFields[$primaryModule] as $blocks) {
 792              if (!empty ($blocks)) {
 793                  foreach ($blocks as $fieldType => $fieldName) {
 794                      $fieldDetails = explode(':', $fieldType);
 795                      if($fieldName == 'Send Reminder' && $primaryModule == 'Calendar') continue;
 796                      if ($fieldDetails[4] === "I" || $fieldDetails[4] === "N" || $fieldDetails[4] === "NN") {
 797                          $calculationFields[$fieldType] = $fieldName;
 798                      }
 799                  }
 800              }
 801          }
 802          $primaryModuleCalculationFields[$primaryModule] = $calculationFields;
 803          return $primaryModuleCalculationFields;
 804      }
 805  
 806      /**
 807       * Function to get Calculation fields for Secondary modules
 808       * @return <Array> Secondary modules calculation fields
 809       */
 810  	function getSecondaryModuleCalculationFields() {
 811          $secondaryModuleCalculationFields = array();
 812          $secondaryModules = $this->getSecondaryModules();
 813          if (!empty ($secondaryModules)) {
 814              $secondaryModulesList = explode(':', $secondaryModules);
 815              $count = count($secondaryModulesList);
 816  
 817              $secondaryModuleFields = $this->getSecondaryModuleFields();
 818  
 819              for ($i=0; $i<$count; $i++) {
 820                  $calculationFields = array();
 821                  $secondaryModule = $secondaryModulesList[$i];
 822                  foreach ($secondaryModuleFields[$secondaryModule] as $blocks) {
 823                      if (!empty ($blocks)) {
 824                          foreach ($blocks as $fieldType => $fieldName) {
 825                              $fieldDetails = explode(':', $fieldType);
 826                              if($fieldName == 'Send Reminder' && $secondaryModule == 'Calendar') continue;
 827                              if ($fieldDetails[4] === "I" || $fieldDetails[4] === "N" || $fieldDetails[4] === "NN") {
 828                                  $calculationFields[$fieldType] = $fieldName;
 829                              }
 830                          }
 831                      }
 832                  }
 833                  $secondaryModuleCalculationFields[$secondaryModule] = $calculationFields;
 834              }
 835          }
 836          return $secondaryModuleCalculationFields;
 837      }
 838  
 839      /**
 840       * Function to get Calculation fields for entire Report
 841       * @return <Array> report calculation fields
 842       */
 843  	function getCalculationFields() {
 844          $primaryModuleCalculationFields = $this->getPrimaryModuleCalculationFields();
 845          $secondaryModuleCalculationFields = $this->getSecondaryModuleCalculationFields();
 846  
 847          return array_merge($primaryModuleCalculationFields, $secondaryModuleCalculationFields);
 848      }
 849  
 850      /**
 851       * Function used to transform the older filter condition to suit newer filters.
 852       * The newer filters have only two groups one with ALL(AND) condition between each
 853       * filter and other with ANY(OR) condition, this functions tranforms the older
 854       * filter with 'AND' condition between filters of a group and will be placed under
 855       * match ALL conditions group and the rest of it will be placed under match Any group.
 856       * @return <Array>
 857       */
 858  	function transformToNewAdvancedFilter() {
 859          $standardFilter = $this->transformStandardFilter();
 860          $advancedFilter = $this->getSelectedAdvancedFilter();
 861          $allGroupColumns = $anyGroupColumns = array();
 862          foreach($advancedFilter as $index=>$group) {
 863              $columns = $group['columns'];
 864              $and = $or = 0;
 865              $block = $group['condition'];
 866              if(count($columns) != 1) {
 867                  foreach($columns as $column) {
 868                      if($column['column_condition'] == 'and') {
 869                          ++$and;
 870                      } else {
 871                          ++$or;
 872                      }
 873                  }
 874                  if($and == count($columns)-1 && count($columns) != 1) {
 875                      $allGroupColumns = array_merge($allGroupColumns, $group['columns']);
 876                  } else {
 877                      $anyGroupColumns = array_merge($anyGroupColumns, $group['columns']);
 878                  }
 879              } else if($block == 'and' || $index == 1) {
 880                  $allGroupColumns = array_merge($allGroupColumns, $group['columns']);
 881              } else {
 882                  $anyGroupColumns = array_merge($anyGroupColumns, $group['columns']);
 883              }
 884          }
 885          if($standardFilter) {
 886              $allGroupColumns = array_merge($allGroupColumns,$standardFilter);
 887          }
 888          $transformedAdvancedCondition = array();
 889          $transformedAdvancedCondition[1] = array('columns' => $allGroupColumns, 'condition' => 'and');
 890          $transformedAdvancedCondition[2] = array('columns' => $anyGroupColumns, 'condition' => '');
 891  
 892          return $transformedAdvancedCondition;
 893      }
 894  
 895      /*
 896       *  Function used to tranform the standard filter as like as advanced filter format
 897       *    @returns array of tranformed standard filter
 898       */
 899  	public function transformStandardFilter(){
 900          $standardFilter = $this->getSelectedStandardFilter();
 901          if(!empty($standardFilter)){
 902              $tranformedStandardFilter = array();
 903              $tranformedStandardFilter['comparator'] = 'bw';
 904  
 905              $fields = explode(':',$standardFilter['columnname']);
 906  
 907              if($fields[1] == 'createdtime' || $fields[1] == 'modifiedtime' ||($fields[0] == 'vtiger_activity' && $fields[1] == 'date_start')){
 908                  $tranformedStandardFilter['columnname'] = "$fields[0]:$fields[1]:$fields[3]:$fields[2]:DT";
 909                  $date[] = $standardFilter['startdate'].' 00:00:00';
 910                  $date[] = $standardFilter['enddate'].' 00:00:00';
 911                  $tranformedStandardFilter['value'] =  implode(',',$date);
 912              } else{
 913                  $tranformedStandardFilter['columnname'] = "$fields[0]:$fields[1]:$fields[3]:$fields[2]:D";
 914                  $tranformedStandardFilter['value'] = $standardFilter['startdate'].','.$standardFilter['enddate'];
 915              }
 916              return array($tranformedStandardFilter);
 917          } else{
 918              return false;
 919          }
 920      }
 921  
 922      /**
 923       * Function returns the Advanced filter SQL
 924       * @return <String>
 925       */
 926  	function getAdvancedFilterSQL() {
 927          $advancedFilter = $this->get('advancedFilter');
 928  
 929          $advancedFilterCriteria = array();
 930          $advancedFilterCriteriaGroup = array();
 931          if(is_array($advancedFilter)) {
 932              foreach($advancedFilter as $groupIndex => $groupInfo) {
 933                  $groupColumns = $groupInfo['columns'];
 934                  $groupCondition = $groupInfo['condition'];
 935  
 936                  if (empty ($groupColumns)) {
 937                      unset($advancedFilter[1]['condition']);
 938                  } else {
 939                      if(!empty($groupCondition)){
 940                          $advancedFilterCriteriaGroup[$groupIndex] = array('groupcondition'=>$groupCondition);
 941                      }
 942                  }
 943  
 944                  foreach($groupColumns as $groupColumn){
 945                      $groupColumn['groupid'] = $groupIndex;
 946                      $groupColumn['columncondition'] = $groupColumn['column_condition'];
 947                      unset($groupColumn['column_condition']);
 948                      $advancedFilterCriteria[] = $groupColumn;
 949                  }
 950              }
 951          }
 952  
 953          $this->reportRun = ReportRun::getInstance($this->getId());
 954          $filterQuery = $this->reportRun->RunTimeAdvFilter($advancedFilterCriteria,$advancedFilterCriteriaGroup);
 955          return $filterQuery;
 956      }
 957  
 958      /**
 959       * Function to generate data for advanced filter conditions
 960       * @param Vtiger_Paging_Model $pagingModel
 961       * @return <Array>
 962       */
 963  	public function generateData($pagingModel = false) {
 964          $filterQuery = $this->getAdvancedFilterSQL();
 965          return $this->getReportData($pagingModel, $filterQuery);
 966      }
 967  
 968      /**
 969       * Function to generate data for advanced filter conditions
 970       * @param Vtiger_Paging_Model $pagingModel
 971       * @return <Array>
 972       */
 973  	public function generateCalculationData() {
 974          $filterQuery = $this->getAdvancedFilterSQL();
 975          return $this->getReportCalulationData($filterQuery);
 976      }
 977      /**
 978       * Function to check duplicate exists or not
 979       * @return <boolean>
 980       */
 981  	public function checkDuplicate() {
 982          $db = PearDatabase::getInstance();
 983  
 984          $query = "SELECT 1 FROM vtiger_report WHERE reportname = ?";
 985          $params = array($this->getName());
 986  
 987          $record = $this->getId();
 988          if ($record && !$this->get('isDuplicate')) {
 989              $query .= " AND reportid != ?";
 990              array_push($params, $record);
 991          }
 992  
 993          $result = $db->pquery($query, $params);
 994          if ($db->num_rows($result)) {
 995              return true;
 996          }
 997          return false;
 998      }
 999  
1000          /**
1001       * Function is used for Inventory reports, filters should show line items fields only if they are selected in
1002       * calculation otherwise it should not be shown
1003       * @return boolean
1004       */
1005  	function showLineItemFieldsInFilter($calculationFields=false) {
1006          if($calculationFields == false) $calculationFields = $this->getSelectedCalculationFields();
1007  
1008          $primaryModule = $this->getPrimaryModule();
1009          $inventoryModules = array('Invoice', 'Quotes', 'SalesOrder', 'PurchaseOrder');
1010          if(!in_array($primaryModule, $inventoryModules)) return false;
1011          if(!empty($calculationFields)) {
1012              foreach($calculationFields as $field) {
1013                  if(stripos($field, 'cb:vtiger_inventoryproductrel') !== false) {
1014                      return true;
1015                  }
1016              }
1017              return false;
1018          }
1019          return true;
1020      }
1021  
1022  	public function getScheduledReport(){
1023          return Reports_ScheduleReports_Model::getInstanceById($this->getId());
1024      }
1025  
1026      public function getRecordsListFromRequest(Vtiger_Request $request) {
1027          $folderId = $request->get('viewname');
1028          $module = $request->get('module');
1029          $selectedIds = $request->get('selected_ids');
1030          $excludedIds = $request->get('excluded_ids');
1031  
1032          if(!empty($selectedIds) && $selectedIds != 'all') {
1033              if(!empty($selectedIds) && count($selectedIds) > 0) {
1034                  return $selectedIds;
1035              }
1036          }
1037  
1038          $reportFolderModel = Reports_Folder_Model::getInstance();
1039          $reportFolderModel->set('folderid', $folderId);
1040          if($reportFolderModel) {
1041              return $reportFolderModel->getRecordIds($excludedIds,$module);
1042          }
1043      }
1044  
1045      function getModuleCalculationFieldsForReport(){
1046          $aggregateFunctions = $this->getAggregateFunctions();
1047          $moduleFields = array();
1048          $primaryModuleFields = $this->getPrimaryModuleCalculationFields();
1049          $secondaryModuleFields = $this->getSecondaryModuleCalculationFields();
1050          $moduleFields = array_merge($primaryModuleFields, $secondaryModuleFields);
1051          foreach ($moduleFields as $moduleName => $fieldList) {
1052              $fields = array();
1053              if(!empty($fieldList)){
1054                  foreach ($fieldList as $column => $label) {
1055                      foreach ($aggregateFunctions as $function) {
1056                          $fLabel = vtranslate($label, $moduleName).' ('.vtranslate('LBL_'.$function, 'Reports').')';
1057                          $fColumn = $column.':'.$function;
1058                          $fields[$fColumn] = $fLabel;
1059                      }
1060                  }
1061              }
1062              $moduleFields[$moduleName] = $fields;
1063          }
1064          return $moduleFields;
1065      }
1066  
1067      function getAggregateFunctions(){
1068          $functions = array('SUM','AVG','MIN','MAX');
1069          return $functions;
1070      }
1071  
1072      /**
1073       * Function to save reprot tyep data
1074       */
1075      function saveReportType(){
1076          $db = PearDatabase::getInstance();
1077          $data = $this->get('reporttypedata');
1078          if(!empty($data)){
1079              $db->pquery('DELETE FROM vtiger_reporttype WHERE reportid = ?', array($this->getId()));
1080              $db->pquery("INSERT INTO vtiger_reporttype(reportid, data) VALUES (?,?)",
1081              array($this->getId(), $data));
1082          }
1083      }
1084  
1085      function getReportTypeInfo() {
1086          $db = PearDatabase::getInstance();
1087  
1088          $result = $db->pquery("SELECT data FROM vtiger_reporttype WHERE reportid = ?", array($this->getId()));
1089  
1090          $dataFields = '';
1091          if($db->num_rows($result) > 0) {
1092              $dataFields = $db->query_result($result, 0, 'data');
1093          }
1094          return $dataFields;
1095      }
1096  
1097      /**
1098       * Function is used in Charts to remove fields like email, phone, descriptions etc
1099       * as these fields are not generally used for grouping records
1100       * @return $fields - array of report field columns
1101       */
1102  	function getPrimaryModuleFieldsForAdvancedReporting() {
1103          $fields = $this->getPrimaryModuleFields();
1104          $primaryModule = $this->getPrimaryModule();
1105          $primaryModuleModel = Vtiger_Module_Model::getInstance($primaryModule);
1106          $primaryModuleFieldInstances = $primaryModuleModel->getFields();
1107  
1108          if(is_array($fields)) foreach($fields as $module => $blocks) {
1109              if(is_array($blocks)) foreach($blocks as $blockLabel => $blockFields) {
1110                  if(is_array($blockFields)) foreach($blockFields as $reportFieldInfo => $fieldLabel) {
1111                      $fieldInfo = explode(':',$reportFieldInfo);
1112  
1113                      $fieldInstance = $primaryModuleFieldInstances[$fieldInfo[3]];
1114                      if(empty($fieldInstance) || $fieldInfo[0] == 'vtiger_inventoryproductrel' || $fieldInstance->getFieldDataType() == 'email'
1115                              || $fieldInstance->getFieldDataType() == 'phone' || $fieldInstance->getFieldDataType() == 'image'
1116                              || $fieldInstance->get('uitype') == '4') {
1117                          unset($fields[$module][$blockLabel][$reportFieldInfo]);
1118                      }
1119                  }
1120              }
1121          }
1122          return $fields;
1123      }
1124  
1125      /**
1126       * Function is used in Charts to remove fields like email, phone, descriptions etc
1127       * as these fields are not generally used for grouping records
1128       * @return $fields - array of report field columns
1129       */
1130  	function getSecondaryModuleFieldsForAdvancedReporting() {
1131          $fields = $this->getSecondaryModuleFields();
1132          $secondaryModules = $this->getSecondaryModules();
1133  
1134          $secondaryModules = @explode(':', $secondaryModules);
1135          if(is_array($secondaryModules)) {
1136              $secondaryModuleFieldInstances = array();
1137              foreach($secondaryModules as $secondaryModule) {
1138                  if(!empty($secondaryModule)) {
1139                      $secondaryModuleModel = Vtiger_Module_Model::getInstance($secondaryModule);
1140                      $secondaryModuleFieldInstances[$secondaryModule] = $secondaryModuleModel->getFields();
1141                  }
1142              }
1143          }
1144          if(is_array($fields)) foreach($fields as $module => $blocks) {
1145              if(is_array($blocks)) foreach($blocks as $blockLabel => $blockFields) {
1146                  if(is_array($blockFields)) foreach($blockFields as $reportFieldInfo => $fieldLabel) {
1147                      $fieldInfo = explode(':',$reportFieldInfo);
1148                      $fieldInstance = $secondaryModuleFieldInstances[$module][$fieldInfo[3]];
1149                      if(empty($fieldInstance) || $fieldInfo[0] == 'vtiger_inventoryproductrel'
1150                              || $fieldInstance->getFieldDataType() == 'email' || $fieldInstance->getFieldDataType() == 'phone'
1151                                  || $fieldInstance->getFieldDataType() == 'image' || $fieldInstance->get('uitype') == '4') {
1152                          unset($fields[$module][$blockLabel][$reportFieldInfo]);
1153                      }
1154                  }
1155              }
1156          }
1157  
1158          return $fields;
1159      }
1160  }