CRM/Report/Utils/Report.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  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 arguement 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     header('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     header('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   public static function makeCsv(&$form, &$rows) {
 240     $config = CRM_Core_Config::singleton();
 241     $csv = '';
 242
 243     // Add headers if this is the first row.
 244     $columnHeaders = array_keys($form->_columnHeaders);
 245
 246     // Replace internal header names with friendly ones, where available.
 247     foreach ($columnHeaders as $header) {
 248       if (isset($form->_columnHeaders[$header])) {
 249         $headers[] = '"' . html_entity_decode(strip_tags($form->_columnHeaders[$header]['title'])) . '"';
 250       }
 251     }
 252     // Add the headers.
 253     $csv .= implode($config->fieldSeparator,
 254         $headers
 255       ) . "\r\n";
 256
 257     $displayRows = array();
 258     $value = NULL;
 259     foreach ($rows as $row) {
 260       foreach ($columnHeaders as $k => $v) {
 261         $value = CRM_Utils_Array::value($v, $row);
 262         if (isset($value)) {
 263           // Remove HTML, unencode entities, and escape quotation marks.
 264           $value = str_replace('"', '""', html_entity_decode(strip_tags($value)));
 265
 266           if (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) & 4) {
 267             if (CRM_Utils_Array::value('group_by', $form->_columnHeaders[$v]) == 'MONTH' ||
 268               CRM_Utils_Array::value('group_by', $form->_columnHeaders[$v]) == 'QUARTER'
 269             ) {
 270               $value = CRM_Utils_Date::customFormat($value, $config->dateformatPartial);
 271             }
 272             elseif (CRM_Utils_Array::value('group_by', $form->_columnHeaders[$v]) == 'YEAR') {
 273               $value = CRM_Utils_Date::customFormat($value, $config->dateformatYear);
 274             }
 275             elseif ($form->_columnHeaders[$v]['type'] == 12) {
 276               // This is a datetime format
 277               $value = CRM_Utils_Date::customFormat($value, '%Y-%m-%d %H:%i');
 278             }
 279             else {
 280               $value = CRM_Utils_Date::customFormat($value, '%Y-%m-%d');
 281             }
 282           }
 283           elseif (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) == 1024) {
 284             $value = CRM_Utils_Money::format($value, $row['civicrm_contribution_currency']);
 285           }
 286           $displayRows[$v] = '"' . $value . '"';
 287         }
 288         else {
 289           $displayRows[$v] = " ";
 290         }
 291       }
 292       // Add the data row.
 293       $csv .= implode($config->fieldSeparator,
 294           $displayRows
 295         ) . "\r\n";
 296     }
 297
 298     return $csv;
 299   }
 300
 301   /**
 302    * @return mixed
 303    */
 304   public static function getInstanceID() {
 305
 306     $config = CRM_Core_Config::singleton();
 307     $arg = explode('/', $_GET[$config->userFrameworkURLVar]);
 308
 309     if ($arg[1] == 'report' &&
 310       CRM_Utils_Array::value(2, $arg) == 'instance'
 311     ) {
 312       if (CRM_Utils_Rule::positiveInteger($arg[3])) {
 313         return $arg[3];
 314       }
 315     }
 316   }
 317
 318   /**
 319    * @return string
 320    */
 321   public static function getInstancePath() {
 322     $config = CRM_Core_Config::singleton();
 323     $arg = explode('/', $_GET[$config->userFrameworkURLVar]);
 324
 325     if ($arg[1] == 'report' &&
 326       CRM_Utils_Array::value(2, $arg) == 'instance'
 327     ) {
 328       unset($arg[0], $arg[1], $arg[2]);
 329       $path = trim(CRM_Utils_Type::escape(implode('/', $arg), 'String'), '/');
 330       return $path;
 331     }
 332   }
 333
 334   /**
 335    * @param int $instanceId
 336    *
 337    * @return bool
 338    */
 339   public static function isInstancePermissioned($instanceId) {
 340     if (!$instanceId) {
 341       return TRUE;
 342     }
 343
 344     $instanceValues = array();
 345     $params = array('id' => $instanceId);
 346     CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 347       $params,
 348       $instanceValues
 349     );
 350
 351     if (!empty($instanceValues['permission']) &&
 352       (!(CRM_Core_Permission::check($instanceValues['permission']) ||
 353         CRM_Core_Permission::check('administer Reports')
 354       ))
 355     ) {
 356       return FALSE;
 357     }
 358
 359     return TRUE;
 360   }
 361
 362   /**
 363    * Check if the user can view a report instance based on their role(s)
 364    *
 365    * @instanceId string $str the report instance to check
 366    *
 367    * @param int $instanceId
 368    *
 369    * @return bool
 370    *   true if yes, else false
 371    */
 372   public static function isInstanceGroupRoleAllowed($instanceId) {
 373     if (!$instanceId) {
 374       return TRUE;
 375     }
 376
 377     $instanceValues = array();
 378     $params = array('id' => $instanceId);
 379     CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 380       $params,
 381       $instanceValues
 382     );
 383     //transform grouprole to array
 384     if (!empty($instanceValues['grouprole'])) {
 385       $grouprole_array = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 386         $instanceValues['grouprole']
 387       );
 388       if (!CRM_Core_Permission::checkGroupRole($grouprole_array) &&
 389         !CRM_Core_Permission::check('administer Reports')
 390       ) {
 391         return FALSE;
 392       }
 393     }
 394     return TRUE;
 395   }
 396
 397   /**
 398    * @param array $params
 399    *
 400    * @return array
 401    */
 402   public static function processReport($params) {
 403     $instanceId = CRM_Utils_Array::value('instanceId', $params);
 404
 405     // hack for now, CRM-8358
 406     $_REQUEST['instanceId'] = $instanceId;
 407     $_REQUEST['sendmail'] = CRM_Utils_Array::value('sendmail', $params, 1);
 408
 409     // if cron is run from terminal --output is reserved, and therefore we would provide another name 'format'
 410     $_REQUEST['output'] = CRM_Utils_Array::value('format', $params, CRM_Utils_Array::value('output', $params, 'pdf'));
 411     $_REQUEST['reset'] = CRM_Utils_Array::value('reset', $params, 1);
 412
 413     $optionVal = self::getValueFromUrl($instanceId);
 414     $messages = array("Report Mail Triggered...");
 415
 416     $templateInfo = CRM_Core_OptionGroup::getRowValues('report_template', $optionVal, 'value');
 417     $obj = new CRM_Report_Page_Instance();
 418     $is_error = 0;
 419     if (strstr(CRM_Utils_Array::value('name', $templateInfo), '_Form')) {
 420       $instanceInfo = array();
 421       CRM_Report_BAO_ReportInstance::retrieve(array('id' => $instanceId), $instanceInfo);
 422
 423       if (!empty($instanceInfo['title'])) {
 424         $obj->assign('reportTitle', $instanceInfo['title']);
 425       }
 426       else {
 427         $obj->assign('reportTitle', $templateInfo['label']);
 428       }
 429
 430       $wrapper = new CRM_Utils_Wrapper();
 431       $arguments = array(
 432         'urlToSession' => array(
 433           array(
 434             'urlVar' => 'instanceId',
 435             'type' => 'Positive',
 436             'sessionVar' => 'instanceId',
 437             'default' => 'null',
 438           ),
 439         ),
 440         'ignoreKey' => TRUE,
 441       );
 442       $messages[] = $wrapper->run($templateInfo['name'], NULL, $arguments);
 443     }
 444     else {
 445       $is_error = 1;
 446       if (!$instanceId) {
 447         $messages[] = 'Required parameter missing: instanceId';
 448       }
 449       else {
 450         $messages[] = 'Did not find valid instance to execute';
 451       }
 452     }
 453
 454     $result = array(
 455       'is_error' => $is_error,
 456       'messages' => implode("\n", $messages),
 457     );
 458     return $result;
 459   }
 460
 461   /**
 462    * Build a URL query string containing all report filter criteria that are
 463    * stipulated in $_GET or in a report Preview, but which haven't yet been
 464    * saved in the report instance.
 465    *
 466    * @param array $defaults
 467    *   The report criteria that aren't coming in as submitted form values, as in CRM_Report_Form::_defaults.
 468    * @param array $params
 469    *   All effective report criteria, as in CRM_Report_Form::_params.
 470    *
 471    * @return string
 472    *   URL query string
 473    */
 474   public static function getPreviewCriteriaQueryParams($defaults = array(), $params = array()) {
 475     static $query_string;
 476     if (!isset($query_string)) {
 477       if (!empty($params)) {
 478         $url_params = $op_values = $string_values = $process_params = array();
 479
 480         // We'll only use $params that are different from what's in $default.
 481         foreach ($params as $field_name => $field_value) {
 482           if (!array_key_exists($field_name, $defaults) || $defaults[$field_name] != $field_value) {
 483             $process_params[$field_name] = $field_value;
 484           }
 485         }
 486         // Criteria stipulated in $_GET will be in $defaults even if they're not
 487         // saved, so we can't easily tell if they're saved or not. So just include them.
 488         $process_params += $_GET;
 489
 490         // All $process_params should be passed on if they have an effective value
 491         // (in other words, there's no point in propagating blank filters).
 492         foreach ($process_params as $field_name => $field_value) {
 493           $suffix_position = strrpos($field_name, '_');
 494           $suffix = substr($field_name, $suffix_position);
 495           $basename = substr($field_name, 0, $suffix_position);
 496           if ($suffix == '_min' || $suffix == '_max' ||
 497             $suffix == '_from' || $suffix == '_to' ||
 498             $suffix == '_relative'
 499           ) {
 500             // For these types, we only keep them if they have a value.
 501             if (!empty($field_value)) {
 502               $url_params[$field_name] = $field_value;
 503             }
 504           }
 505           elseif ($suffix == '_value') {
 506             // These filters can have an effect even without a value
 507             // (e.g., values for 'nll' and 'nnll' ops are blank),
 508             // so store them temporarily and examine below.
 509             $string_values[$basename] = $field_value;
 510             $op_values[$basename] = CRM_Utils_Array::value("{$basename}_op", $params);
 511           }
 512           elseif ($suffix == '_op') {
 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             $op_values[$basename] = $field_value;
 517             $string_values[$basename] = $params["{$basename}_value"];
 518           }
 519         }
 520
 521         // Check the *_value and *_op criteria and include them if
 522         // they'll have an effective value.
 523         foreach ($op_values as $basename => $field_value) {
 524           if ($field_value == 'nll' || $field_value == 'nnll') {
 525             // 'nll' and 'nnll' filters should be included even with empty values.
 526             $url_params["{$basename}_op"] = $field_value;
 527           }
 528           elseif ($string_values[$basename]) {
 529             // Other filters are only included if they have a value.
 530             $url_params["{$basename}_op"] = $field_value;
 531             $url_params["{$basename}_value"] = (is_array($string_values[$basename]) ? implode(',', $string_values[$basename]) : $string_values[$basename]);
 532           }
 533         }
 534         $query_string = http_build_query($url_params);
 535       }
 536       else {
 537         $query_string = '';
 538       }
 539     }
 540     return $query_string;
 541   }
 542
 543   /**
 544    * @param $reportUrl
 545    *
 546    * @return mixed
 547    */
 548   public static function getInstanceList($reportUrl) {
 549     static $instanceDetails = array();
 550
 551     if (!array_key_exists($reportUrl, $instanceDetails)) {
 552       $instanceDetails[$reportUrl] = array();
 553
 554       $sql = "
 555 SELECT id, title FROM civicrm_report_instance
 556 WHERE  report_id = %1";
 557       $params = array(1 => array($reportUrl, 'String'));
 558       $result = CRM_Core_DAO::executeQuery($sql, $params);
 559       while ($result->fetch()) {
 560         $instanceDetails[$reportUrl][$result->id] = $result->title . " (ID: {$result->id})";
 561       }
 562     }
 563     return $instanceDetails[$reportUrl];
 564   }
 565
 566 }