CRM/Report/Utils/Report.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.5                                                |
   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   static function getValueFromUrl($instanceID = NULL) {
  39     if ($instanceID) {
  40       $optionVal = CRM_Core_DAO::getFieldValue('CRM_Report_DAO_ReportInstance',
  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_ReportInstance', $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_ReportInstance',
 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 (empty($instanceInfo['attachments'])) {
 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_ReportInstance',
 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    * @param $instanceId
 305    *
 306    * @return boolean true if yes, else false
 307    * @static
 308    * @access public
 309    */
 310   static function isInstanceGroupRoleAllowed($instanceId) {
 311     if (!$instanceId) {
 312       return TRUE;
 313     }
 314
 315     $instanceValues = array();
 316     $params = array('id' => $instanceId);
 317     CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 318       $params,
 319       $instanceValues
 320     );
 321     //transform grouprole to array
 322     if (!empty($instanceValues['grouprole'])) {
 323       $grouprole_array = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 324         $instanceValues['grouprole']
 325       );
 326       if (!CRM_Core_Permission::checkGroupRole($grouprole_array) &&
 327         !CRM_Core_Permission::check('administer Reports')
 328       ) {
 329         return FALSE;
 330       }
 331     }
 332     return TRUE;
 333   }
 334
 335   static function processReport($params) {
 336     $instanceId = CRM_Utils_Array::value('instanceId', $params);
 337
 338     // hack for now, CRM-8358
 339     $_REQUEST['instanceId'] = $instanceId;
 340     $_REQUEST['sendmail'] = CRM_Utils_Array::value('sendmail', $params, 1);
 341
 342     // if cron is run from terminal --output is reserved, and therefore we would provide another name 'format'
 343     $_REQUEST['output'] = CRM_Utils_Array::value('format', $params, CRM_Utils_Array::value('output', $params, 'pdf'));
 344     $_REQUEST['reset'] = CRM_Utils_Array::value('reset', $params, 1);
 345
 346     $optionVal = self::getValueFromUrl($instanceId);
 347     $messages = array("Report Mail Triggered...");
 348
 349     $templateInfo = CRM_Core_OptionGroup::getRowValues('report_template', $optionVal, 'value');
 350     $obj          = new CRM_Report_Page_Instance();
 351     $is_error     = 0;
 352     if (strstr(CRM_Utils_Array::value('name', $templateInfo), '_Form')) {
 353       $instanceInfo = array();
 354       CRM_Report_BAO_ReportInstance::retrieve(array('id' => $instanceId), $instanceInfo);
 355
 356       if (!empty($instanceInfo['title'])) {
 357         $obj->assign('reportTitle', $instanceInfo['title']);
 358       }
 359       else {
 360         $obj->assign('reportTitle', $templateInfo['label']);
 361       }
 362
 363       $wrapper = new CRM_Utils_Wrapper();
 364       $arguments = array(
 365         'urlToSession' => array(
 366           array(
 367             'urlVar' => 'instanceId',
 368             'type' => 'Positive',
 369             'sessionVar' => 'instanceId',
 370             'default' => 'null',
 371           ),
 372         ),
 373         'ignoreKey' => TRUE
 374       );
 375       $messages[] = $wrapper->run($templateInfo['name'], NULL, $arguments);
 376     }
 377     else {
 378       $is_error = 1;
 379       if (!$instanceId) {
 380         $messages[] = 'Required parameter missing: instanceId';
 381       }
 382       else {
 383         $messages[] = 'Did not find valid instance to execute';
 384       }
 385     }
 386
 387     $result = array(
 388       'is_error' => $is_error,
 389       'messages' => implode("\n", $messages),
 390     );
 391     return $result;
 392   }
 393
 394   /**
 395    * Build a URL query string containing all report filter criteria that are
 396    * stipulated in $_GET or in a report Preview, but which haven't yet been
 397    * saved in the report instance.
 398    *
 399    * @param array $defaults The report criteria that aren't coming in as submitted form values, as in CRM_Report_Form::_defaults
 400    * @param array $params All effective report criteria, as in CRM_Report_Form::_params
 401    *
 402    * @return string URL query string
 403    */
 404   static function getPreviewCriteriaQueryParams($defaults = array(
 405     ), $params = array()) {
 406     static $query_string;
 407     if (!isset($query_string)) {
 408       if (!empty($params)) {
 409         $url_params = $op_values = $string_values = $process_params = array();
 410
 411         // We'll only use $params that are different from what's in $default.
 412         foreach ($params as $field_name => $field_value) {
 413           if (!array_key_exists($field_name, $defaults) || $defaults[$field_name] != $field_value) {
 414             $process_params[$field_name] = $field_value;
 415           }
 416         }
 417         // Criteria stipulated in $_GET will be in $defaults even if they're not
 418         // saved, so we can't easily tell if they're saved or not. So just include them.
 419         $process_params += $_GET;
 420
 421         // All $process_params should be passed on if they have an effective value
 422         // (in other words, there's no point in propagating blank filters).
 423         foreach ($process_params as $field_name => $field_value) {
 424           $suffix_position = strrpos($field_name, '_');
 425           $suffix          = substr($field_name, $suffix_position);
 426           $basename        = substr($field_name, 0, $suffix_position);
 427           if ($suffix == '_min' || $suffix == '_max' ||
 428             $suffix == '_from' || $suffix == '_to' ||
 429             $suffix == '_relative'
 430           ) {
 431             // For these types, we only keep them if they have a value.
 432             if (!empty($field_value)) {
 433               $url_params[$field_name] = $field_value;
 434             }
 435           }
 436           elseif ($suffix == '_value') {
 437             // These filters can have an effect even without a value
 438             // (e.g., values for 'nll' and 'nnll' ops are blank),
 439             // so store them temporarily and examine below.
 440             $string_values[$basename] = $field_value;
 441             $op_values[$basename] = CRM_Utils_Array::value("{$basename}_op", $params);
 442           }
 443           elseif ($suffix == '_op') {
 444             // These filters can have an effect even without a value
 445             // (e.g., values for 'nll' and 'nnll' ops are blank),
 446             // so store them temporarily and examine below.
 447             $op_values[$basename] = $field_value;
 448             $string_values[$basename] = $params["{$basename}_value"];
 449           }
 450         }
 451
 452         // Check the *_value and *_op criteria and include them if
 453         // they'll have an effective value.
 454         foreach ($op_values as $basename => $field_value) {
 455           if ($field_value == 'nll' || $field_value == 'nnll') {
 456             // 'nll' and 'nnll' filters should be included even with empty values.
 457             $url_params["{$basename}_op"] = $field_value;
 458           }
 459           elseif ($string_values[$basename]) {
 460             // Other filters are only included if they have a value.
 461             $url_params["{$basename}_op"] = $field_value;
 462             $url_params["{$basename}_value"] = (is_array($string_values[$basename]) ? implode(',', $string_values[$basename]) : $string_values[$basename]);
 463           }
 464         }
 465         $query_string = http_build_query($url_params);
 466       }
 467       else {
 468         $query_string = '';
 469       }
 470     }
 471     return $query_string;
 472   }
 473
 474   static function getInstanceList($reportUrl) {
 475     static $instanceDetails = array();
 476
 477     if (!array_key_exists($reportUrl, $instanceDetails )) {
 478       $instanceDetails[$reportUrl] = array();
 479
 480       $sql = "
 481 SELECT id, title FROM civicrm_report_instance
 482 WHERE  report_id = %1";
 483       $params = array(1 => array($reportUrl, 'String'));
 484       $result = CRM_Core_DAO::executeQuery($sql, $params);
 485       while( $result->fetch()) {
 486         $instanceDetails[$reportUrl][$result->id] = $result->title . " (ID: {$result->id})";
 487       }
 488     }
 489     return $instanceDetails[$reportUrl];
 490   }
 491 }