CRM/Report/Utils/Report.php

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