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