CRM/Report/Utils/Report.php

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