CRM/Report/Utils/Report.php

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