CRM/Report/Utils/Report.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2018
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Utils_Report {
  36
  37   /**
  38    * @param int $instanceID
  39    *
  40    * @return null|string
  41    */
  42   public static function getValueFromUrl($instanceID = NULL) {
  43     if ($instanceID) {
  44       $optionVal = CRM_Core_DAO::getFieldValue('CRM_Report_DAO_ReportInstance',
  45         $instanceID,
  46         'report_id'
  47       );
  48     }
  49     else {
  50       $config = CRM_Core_Config::singleton();
  51       $args = explode('/', $_GET[$config->userFrameworkURLVar]);
  52
  53       // remove 'civicrm/report' from args
  54       array_shift($args);
  55       array_shift($args);
  56
  57       // put rest of argument back in the form of url, which is how value
  58       // is stored in option value table
  59       $optionVal = implode('/', $args);
  60     }
  61     return $optionVal;
  62   }
  63
  64   /**
  65    * @param int $instanceID
  66    *
  67    * @return array|bool
  68    */
  69   public static function getValueIDFromUrl($instanceID = NULL) {
  70     $optionVal = self::getValueFromUrl($instanceID);
  71
  72     if ($optionVal) {
  73       $templateInfo = CRM_Core_OptionGroup::getRowValues('report_template', "{$optionVal}", 'value');
  74       return array(CRM_Utils_Array::value('id', $templateInfo), $optionVal);
  75     }
  76
  77     return FALSE;
  78   }
  79
  80   /**
  81    * @param $optionVal
  82    *
  83    * @return mixed
  84    */
  85   public static function getInstanceIDForValue($optionVal) {
  86     static $valId = array();
  87
  88     if (!array_key_exists($optionVal, $valId)) {
  89       $sql = "
  90 SELECT MIN(id) FROM civicrm_report_instance
  91 WHERE  report_id = %1";
  92
  93       $params = array(1 => array($optionVal, 'String'));
  94       $valId[$optionVal] = CRM_Core_DAO::singleValueQuery($sql, $params);
  95     }
  96     return $valId[$optionVal];
  97   }
  98
  99   /**
 100    * @param null $path
 101    *
 102    * @return mixed
 103    */
 104   public static function getInstanceIDForPath($path = NULL) {
 105     static $valId = array();
 106
 107     // if $path is null, try to get it from url
 108     $path = self::getInstancePath();
 109
 110     if ($path && !array_key_exists($path, $valId)) {
 111       $sql = "
 112 SELECT MIN(id) FROM civicrm_report_instance
 113 WHERE  TRIM(BOTH '/' FROM CONCAT(report_id, '/', name)) = %1";
 114
 115       $params = array(1 => array($path, 'String'));
 116       $valId[$path] = CRM_Core_DAO::singleValueQuery($sql, $params);
 117     }
 118     return CRM_Utils_Array::value($path, $valId);
 119   }
 120
 121   /**
 122    * @param $urlValue
 123    * @param string $query
 124    * @param bool $absolute
 125    * @param int $instanceID
 126    * @param array $drilldownReport
 127    *
 128    * @return bool|string
 129    */
 130   public static function getNextUrl($urlValue, $query = 'reset=1', $absolute = FALSE, $instanceID = NULL, $drilldownReport = array()) {
 131     if ($instanceID) {
 132       $drilldownInstanceID = FALSE;
 133       if (array_key_exists($urlValue, $drilldownReport)) {
 134         $drilldownInstanceID = CRM_Core_DAO::getFieldValue('CRM_Report_DAO_ReportInstance', $instanceID, 'drilldown_id', 'id');
 135       }
 136
 137       if (!$drilldownInstanceID) {
 138         $drilldownInstanceID = self::getInstanceIDForValue($urlValue);
 139       }
 140
 141       if ($drilldownInstanceID) {
 142         return CRM_Utils_System::url("civicrm/report/instance/{$drilldownInstanceID}",
 143           "{$query}", $absolute
 144         );
 145       }
 146       else {
 147         return FALSE;
 148       }
 149     }
 150     else {
 151       return CRM_Utils_System::url("civicrm/report/" . trim($urlValue, '/'),
 152         $query, $absolute
 153       );
 154     }
 155   }
 156
 157   /**
 158    * get instance count for a template.
 159    * @param $optionVal
 160    *
 161    * @return int|null|string
 162    */
 163   public static function getInstanceCount($optionVal) {
 164     if (empty($optionVal)) {
 165       return 0;
 166     }
 167
 168     $sql = "
 169 SELECT count(inst.id)
 170 FROM   civicrm_report_instance inst
 171 WHERE  inst.report_id = %1";
 172
 173     $params = array(1 => array($optionVal, 'String'));
 174     $count = CRM_Core_DAO::singleValueQuery($sql, $params);
 175     return $count;
 176   }
 177
 178   /**
 179    * @param $fileContent
 180    * @param int $instanceID
 181    * @param string $outputMode
 182    * @param array $attachments
 183    *
 184    * @return bool
 185    */
 186   public static function mailReport($fileContent, $instanceID = NULL, $outputMode = 'html', $attachments = array()) {
 187     if (!$instanceID) {
 188       return FALSE;
 189     }
 190
 191     list($domainEmailName,
 192       $domainEmailAddress
 193       ) = CRM_Core_BAO_Domain::getNameAndEmail();
 194
 195     $params = array('id' => $instanceID);
 196     $instanceInfo = array();
 197     CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 198       $params,
 199       $instanceInfo
 200     );
 201
 202     $params = array();
 203     $params['groupName'] = 'Report Email Sender';
 204     $params['from'] = '"' . $domainEmailName . '" <' . $domainEmailAddress . '>';
 205     //$domainEmailName;
 206     $params['toName'] = "";
 207     $params['toEmail'] = CRM_Utils_Array::value('email_to', $instanceInfo);
 208     $params['cc'] = CRM_Utils_Array::value('email_cc', $instanceInfo);
 209     $params['subject'] = CRM_Utils_Array::value('email_subject', $instanceInfo);
 210     if (empty($instanceInfo['attachments'])) {
 211       $instanceInfo['attachments'] = array();
 212     }
 213     $params['attachments'] = array_merge(CRM_Utils_Array::value('attachments', $instanceInfo), $attachments);
 214     $params['text'] = '';
 215     $params['html'] = $fileContent;
 216
 217     return CRM_Utils_Mail::send($params);
 218   }
 219
 220   /**
 221    * @param CRM_Core_Form $form
 222    * @param $rows
 223    */
 224   public static function export2csv(&$form, &$rows) {
 225     //Mark as a CSV file.
 226     CRM_Utils_System::setHttpHeader('Content-Type', 'text/csv');
 227
 228     //Force a download and name the file using the current timestamp.
 229     $datetime = date('Ymd-Gi', $_SERVER['REQUEST_TIME']);
 230     CRM_Utils_System::setHttpHeader('Content-Disposition', 'attachment; filename=Report_' . $datetime . '.csv');
 231     echo self::makeCsv($form, $rows);
 232     CRM_Utils_System::civiExit();
 233   }
 234
 235   /**
 236    * Utility function for export2csv and CRM_Report_Form::endPostProcess
 237    * - make CSV file content and return as string.
 238    *
 239    * @param CRM_Core_Form $form
 240    * @param array $rows
 241    *
 242    * @return string
 243    */
 244   public static function makeCsv(&$form, &$rows) {
 245     $config = CRM_Core_Config::singleton();
 246     $csv = '';
 247
 248     // Add headers if this is the first row.
 249     $columnHeaders = array_keys($form->_columnHeaders);
 250
 251     // Replace internal header names with friendly ones, where available.
 252     foreach ($columnHeaders as $header) {
 253       if (isset($form->_columnHeaders[$header])) {
 254         $headers[] = '"' . html_entity_decode(strip_tags($form->_columnHeaders[$header]['title'])) . '"';
 255       }
 256     }
 257     // Add the headers.
 258     $csv .= implode($config->fieldSeparator,
 259         $headers
 260       ) . "\r\n";
 261
 262     $displayRows = array();
 263     $value = NULL;
 264     foreach ($rows as $row) {
 265       foreach ($columnHeaders as $k => $v) {
 266         $value = CRM_Utils_Array::value($v, $row);
 267         if (isset($value)) {
 268           // Remove HTML, unencode entities, and escape quotation marks.
 269           $value = str_replace('"', '""', html_entity_decode(strip_tags($value)));
 270
 271           if (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) & 4) {
 272             if (CRM_Utils_Array::value('group_by', $form->_columnHeaders[$v]) == 'MONTH' ||
 273               CRM_Utils_Array::value('group_by', $form->_columnHeaders[$v]) == 'QUARTER'
 274             ) {
 275               $value = CRM_Utils_Date::customFormat($value, $config->dateformatPartial);
 276             }
 277             elseif (CRM_Utils_Array::value('group_by', $form->_columnHeaders[$v]) == 'YEAR') {
 278               $value = CRM_Utils_Date::customFormat($value, $config->dateformatYear);
 279             }
 280             elseif ($form->_columnHeaders[$v]['type'] == 12) {
 281               // This is a datetime format
 282               $value = CRM_Utils_Date::customFormat($value, '%Y-%m-%d %H:%i');
 283             }
 284             else {
 285               $value = CRM_Utils_Date::customFormat($value, '%Y-%m-%d');
 286             }
 287           }
 288           // Note the reference to a specific field does not belong in this generic class & does not work on all reports.
 289           // @todo - fix this properly rather than just supressing the en-otice. Repeat transaction report is a good example.
 290           elseif (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) == 1024 && !empty($row['civicrm_contribution_currency'])) {
 291             $value = CRM_Utils_Money::format($value, $row['civicrm_contribution_currency']);
 292           }
 293           $displayRows[$v] = '"' . $value . '"';
 294         }
 295         else {
 296           $displayRows[$v] = "";
 297         }
 298       }
 299       // Add the data row.
 300       $csv .= implode($config->fieldSeparator,
 301           $displayRows
 302         ) . "\r\n";
 303     }
 304
 305     return $csv;
 306   }
 307
 308   /**
 309    * @return mixed
 310    */
 311   public static function getInstanceID() {
 312
 313     $config = CRM_Core_Config::singleton();
 314     $arg = explode('/', $_GET[$config->userFrameworkURLVar]);
 315
 316     if ($arg[1] == 'report' &&
 317       CRM_Utils_Array::value(2, $arg) == 'instance'
 318     ) {
 319       if (CRM_Utils_Rule::positiveInteger($arg[3])) {
 320         return $arg[3];
 321       }
 322     }
 323   }
 324
 325   /**
 326    * @return string
 327    */
 328   public static function getInstancePath() {
 329     $config = CRM_Core_Config::singleton();
 330     $arg = explode('/', $_GET[$config->userFrameworkURLVar]);
 331
 332     if ($arg[1] == 'report' &&
 333       CRM_Utils_Array::value(2, $arg) == 'instance'
 334     ) {
 335       unset($arg[0], $arg[1], $arg[2]);
 336       $path = trim(CRM_Utils_Type::escape(implode('/', $arg), 'String'), '/');
 337       return $path;
 338     }
 339   }
 340
 341   /**
 342    * @param int $instanceId
 343    *
 344    * @return bool
 345    */
 346   public static function isInstancePermissioned($instanceId) {
 347     if (!$instanceId) {
 348       return TRUE;
 349     }
 350
 351     $instanceValues = array();
 352     $params = array('id' => $instanceId);
 353     CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 354       $params,
 355       $instanceValues
 356     );
 357
 358     if (!empty($instanceValues['permission']) &&
 359       (!(CRM_Core_Permission::check($instanceValues['permission']) ||
 360         CRM_Core_Permission::check('administer Reports')
 361       ))
 362     ) {
 363       return FALSE;
 364     }
 365
 366     return TRUE;
 367   }
 368
 369   /**
 370    * Check if the user can view a report instance based on their role(s)
 371    *
 372    * @instanceId string $str the report instance to check
 373    *
 374    * @param int $instanceId
 375    *
 376    * @return bool
 377    *   true if yes, else false
 378    */
 379   public static function isInstanceGroupRoleAllowed($instanceId) {
 380     if (!$instanceId) {
 381       return TRUE;
 382     }
 383
 384     $instanceValues = array();
 385     $params = array('id' => $instanceId);
 386     CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 387       $params,
 388       $instanceValues
 389     );
 390     //transform grouprole to array
 391     if (!empty($instanceValues['grouprole'])) {
 392       $grouprole_array = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 393         $instanceValues['grouprole']
 394       );
 395       if (!CRM_Core_Permission::checkGroupRole($grouprole_array) &&
 396         !CRM_Core_Permission::check('administer Reports')
 397       ) {
 398         return FALSE;
 399       }
 400     }
 401     return TRUE;
 402   }
 403
 404   /**
 405    * @param array $params
 406    *
 407    * @return array
 408    */
 409   public static function processReport($params) {
 410     $instanceId = CRM_Utils_Array::value('instanceId', $params);
 411
 412     // hack for now, CRM-8358
 413     $_REQUEST['instanceId'] = $instanceId;
 414     $_REQUEST['sendmail'] = CRM_Utils_Array::value('sendmail', $params, 1);
 415
 416     // if cron is run from terminal --output is reserved, and therefore we would provide another name 'format'
 417     $_REQUEST['output'] = CRM_Utils_Array::value('format', $params, CRM_Utils_Array::value('output', $params, 'pdf'));
 418     $_REQUEST['reset'] = CRM_Utils_Array::value('reset', $params, 1);
 419
 420     $optionVal = self::getValueFromUrl($instanceId);
 421     $messages = array("Report Mail Triggered...");
 422
 423     $templateInfo = CRM_Core_OptionGroup::getRowValues('report_template', $optionVal, 'value');
 424     $obj = new CRM_Report_Page_Instance();
 425     $is_error = 0;
 426     if (strstr(CRM_Utils_Array::value('name', $templateInfo), '_Form')) {
 427       $instanceInfo = array();
 428       CRM_Report_BAO_ReportInstance::retrieve(array('id' => $instanceId), $instanceInfo);
 429
 430       if (!empty($instanceInfo['title'])) {
 431         $obj->assign('reportTitle', $instanceInfo['title']);
 432       }
 433       else {
 434         $obj->assign('reportTitle', $templateInfo['label']);
 435       }
 436
 437       $wrapper = new CRM_Utils_Wrapper();
 438       $arguments = array(
 439         'urlToSession' => array(
 440           array(
 441             'urlVar' => 'instanceId',
 442             'type' => 'Positive',
 443             'sessionVar' => 'instanceId',
 444             'default' => 'null',
 445           ),
 446         ),
 447         'ignoreKey' => TRUE,
 448       );
 449       $messages[] = $wrapper->run($templateInfo['name'], NULL, $arguments);
 450     }
 451     else {
 452       $is_error = 1;
 453       if (!$instanceId) {
 454         $messages[] = 'Required parameter missing: instanceId';
 455       }
 456       else {
 457         $messages[] = 'Did not find valid instance to execute';
 458       }
 459     }
 460
 461     $result = array(
 462       'is_error' => $is_error,
 463       'messages' => implode("\n", $messages),
 464     );
 465     return $result;
 466   }
 467
 468   /**
 469    * Build a URL query string containing all report filter criteria that are
 470    * stipulated in $_GET or in a report Preview, but which haven't yet been
 471    * saved in the report instance.
 472    *
 473    * @param array $defaults
 474    *   The report criteria that aren't coming in as submitted form values, as in CRM_Report_Form::_defaults.
 475    * @param array $params
 476    *   All effective report criteria, as in CRM_Report_Form::_params.
 477    *
 478    * @return string
 479    *   URL query string
 480    */
 481   public static function getPreviewCriteriaQueryParams($defaults = array(), $params = array()) {
 482     static $query_string;
 483     if (!isset($query_string)) {
 484       if (!empty($params)) {
 485         $url_params = $op_values = $string_values = $process_params = array();
 486
 487         // We'll only use $params that are different from what's in $default.
 488         foreach ($params as $field_name => $field_value) {
 489           if (!array_key_exists($field_name, $defaults) || $defaults[$field_name] != $field_value) {
 490             $process_params[$field_name] = $field_value;
 491           }
 492         }
 493         // Criteria stipulated in $_GET will be in $defaults even if they're not
 494         // saved, so we can't easily tell if they're saved or not. So just include them.
 495         $process_params += $_GET;
 496
 497         // All $process_params should be passed on if they have an effective value
 498         // (in other words, there's no point in propagating blank filters).
 499         foreach ($process_params as $field_name => $field_value) {
 500           $suffix_position = strrpos($field_name, '_');
 501           $suffix = substr($field_name, $suffix_position);
 502           $basename = substr($field_name, 0, $suffix_position);
 503           if ($suffix == '_min' || $suffix == '_max' ||
 504             $suffix == '_from' || $suffix == '_to' ||
 505             $suffix == '_relative'
 506           ) {
 507             // For these types, we only keep them if they have a value.
 508             if (!empty($field_value)) {
 509               $url_params[$field_name] = $field_value;
 510             }
 511           }
 512           elseif ($suffix == '_value') {
 513             // These filters can have an effect even without a value
 514             // (e.g., values for 'nll' and 'nnll' ops are blank),
 515             // so store them temporarily and examine below.
 516             $string_values[$basename] = $field_value;
 517             $op_values[$basename] = CRM_Utils_Array::value("{$basename}_op", $params);
 518           }
 519           elseif ($suffix == '_op') {
 520             // These filters can have an effect even without a value
 521             // (e.g., values for 'nll' and 'nnll' ops are blank),
 522             // so store them temporarily and examine below.
 523             $op_values[$basename] = $field_value;
 524             $string_values[$basename] = $params["{$basename}_value"];
 525           }
 526         }
 527
 528         // Check the *_value and *_op criteria and include them if
 529         // they'll have an effective value.
 530         foreach ($op_values as $basename => $field_value) {
 531           if ($field_value == 'nll' || $field_value == 'nnll') {
 532             // 'nll' and 'nnll' filters should be included even with empty values.
 533             $url_params["{$basename}_op"] = $field_value;
 534           }
 535           elseif ($string_values[$basename]) {
 536             // Other filters are only included if they have a value.
 537             $url_params["{$basename}_op"] = $field_value;
 538             $url_params["{$basename}_value"] = (is_array($string_values[$basename]) ? implode(',', $string_values[$basename]) : $string_values[$basename]);
 539           }
 540         }
 541         $query_string = http_build_query($url_params);
 542       }
 543       else {
 544         $query_string = '';
 545       }
 546     }
 547     return $query_string;
 548   }
 549
 550   /**
 551    * @param $reportUrl
 552    *
 553    * @return mixed
 554    */
 555   public static function getInstanceList($reportUrl) {
 556     static $instanceDetails = array();
 557
 558     if (!array_key_exists($reportUrl, $instanceDetails)) {
 559       $instanceDetails[$reportUrl] = array();
 560
 561       $sql = "
 562 SELECT id, title FROM civicrm_report_instance
 563 WHERE  report_id = %1";
 564       $params = array(1 => array($reportUrl, 'String'));
 565       $result = CRM_Core_DAO::executeQuery($sql, $params);
 566       while ($result->fetch()) {
 567         $instanceDetails[$reportUrl][$result->id] = $result->title . " (ID: {$result->id})";
 568       }
 569     }
 570     return $instanceDetails[$reportUrl];
 571   }
 572
 573 }