CRM/Report/Utils/Report.php

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