CRM-21616 ensure sql metadata is available from api

[civicrm-core.git] / CRM / Report / Form.php

diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php
index 8c6cfd6340a1a56ee69a030fc3d9d3d9f7bb3b25..6b83d4ff0df929deba5542523fc0159e0be94859 100644 (file)
--- a/CRM/Report/Form.php
+++ b/CRM/Report/Form.php
@@ -814,6 +814,9 @@ class CRM_Report_Form extends CRM_Core_Form {
                 }
               }
             }
+            if (!isset($this->_columns[$tableName]['metadata'][$fieldName])) {
+              $this->_columns[$tableName]['metadata'][$fieldName] = $this->_columns[$tableName][$fieldGrp][$fieldName];
+            }
           }
         }
       }
@@ -1310,7 +1313,7 @@ class CRM_Report_Form extends CRM_Core_Form {
    *
    * @param string $sql
    */
-  protected function addToDeveloperTab($sql) {
+  public function addToDeveloperTab($sql) {
     if (!CRM_Core_Permission::check('view report sql')) {
       return;
     }
@@ -2281,6 +2284,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
    */
   public function select() {
     $select = $this->_selectAliases = array();
+    $this->storeGroupByArray();
 
     foreach ($this->_columns as $tableName => $table) {
       if (array_key_exists('fields', $table)) {
@@ -2311,7 +2315,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
             }
 
             // include statistics columns only if set
-            if (!empty($field['statistics'])) {
+            if (!empty($field['statistics']) && !empty($this->_groupByArray)) {
               $select = $this->addStatisticsToSelect($field, $tableName, $fieldName, $select);
             }
             else {
@@ -2645,19 +2649,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
    * Build group by clause.
    */
   public function groupBy() {
-    if (!empty($this->_params['group_bys']) &&
-      is_array($this->_params['group_bys'])
-    ) {
-      foreach ($this->_columns as $tableName => $table) {
-        if (array_key_exists('group_bys', $table)) {
-          foreach ($table['group_bys'] as $fieldName => $field) {
-            if (!empty($this->_params['group_bys'][$fieldName])) {
-              $this->_groupByArray[] = $field['dbAlias'];
-            }
-          }
-        }
-      }
-    }
+    $this->storeGroupByArray();
 
     if (!empty($this->_groupByArray)) {
       $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $this->_groupByArray);
@@ -3370,7 +3362,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
     $group->find();
     $smartGroups = array();
     while ($group->fetch()) {
-      if (in_array($group->id, $this->_params['gid_value']) &&
+      if (in_array($group->id, (array) $this->_params['gid_value']) &&
         $group->saved_search_id
       ) {
         $smartGroups[] = $group->id;
@@ -4379,7 +4371,7 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a
         'dao' => 'CRM_Core_DAO_Phone',
         'fields' => array(
           $options['prefix'] . 'phone' => array(
-            'title' => ts($options['prefix_label'] . 'Phone'),
+            'title' => $options['prefix_label'] . ts('Phone'),
             'name' => 'phone',
           ),
         ),
@@ -5000,19 +4992,19 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a
     $spec = array(
       $options['prefix'] . 'display_name' => array(
         'name' => 'display_name',
-        'title' => ts($options['prefix_label'] . 'Contact Name'),
+        'title' => $options['prefix_label'] . ts('Contact Name'),
         'is_fields' => TRUE,
       ),
       $options['prefix'] . 'sort_name' => array(
         'name' => 'sort_name',
-        'title' => ts($options['prefix_label'] . 'Contact Name (in sort format)'),
+        'title' => $options['prefix_label'] . ts('Contact Name (in sort format)'),
         'is_fields' => TRUE,
         'is_filters' => TRUE,
         'is_order_bys' => TRUE,
       ),
       $options['prefix'] . 'id' => array(
         'name' => 'id',
-        'title' => ts($options['prefix_label'] . 'Contact ID'),
+        'title' => $options['prefix_label'] . ts('Contact ID'),
         'alter_display' => 'alterContactID',
         'type' => CRM_Utils_Type::T_INT,
         'is_order_bys' => TRUE,
@@ -5021,12 +5013,12 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a
       ),
       $options['prefix'] . 'external_identifier' => array(
         'name' => 'external_identifier',
-        'title' => ts($options['prefix_label'] . 'External ID'),
+        'title' => $options['prefix_label'] . ts('External ID'),
         'type' => CRM_Utils_Type::T_INT,
         'is_fields' => TRUE,
       ),
       $options['prefix'] . 'contact_type' => array(
-        'title' => ts($options['prefix_label'] . 'Contact Type'),
+        'title' => $options['prefix_label'] . ts('Contact Type'),
         'name' => 'contact_type',
         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
         'options' => CRM_Contact_BAO_Contact::buildOptions('contact_type'),
@@ -5035,7 +5027,7 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a
         'is_group_bys' => TRUE,
       ),
       $options['prefix'] . 'contact_sub_type' => array(
-        'title' => ts($options['prefix_label'] . 'Contact Sub Type'),
+        'title' => $options['prefix_label'] . ts('Contact Sub Type'),
         'name' => 'contact_sub_type',
         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
         'options' => CRM_Contact_BAO_Contact::buildOptions('contact_sub_type'),
@@ -5044,7 +5036,7 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a
         'is_group_bys' => TRUE,
       ),
       $options['prefix'] . 'is_deleted' => array(
-        'title' => ts($options['prefix_label'] . 'Is deleted'),
+        'title' => $options['prefix_label'] . ts('Is deleted'),
         'name' => 'is_deleted',
         'type' => CRM_Utils_Type::T_BOOLEAN,
         'is_fields' => FALSE,
@@ -5055,30 +5047,30 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a
     $individualFields = array(
       $options['prefix'] . 'first_name' => array(
         'name' => 'first_name',
-        'title' => ts($options['prefix_label'] . 'First Name'),
+        'title' => $options['prefix_label'] . ts('First Name'),
         'is_fields' => TRUE,
         'is_filters' => TRUE,
         'is_order_bys' => TRUE,
       ),
       $options['prefix'] . 'middle_name' => array(
         'name' => 'middle_name',
-        'title' => ts($options['prefix_label'] . 'Middle Name'),
+        'title' => $options['prefix_label'] . ts('Middle Name'),
         'is_fields' => TRUE,
       ),
       $options['prefix'] . 'last_name' => array(
         'name' => 'last_name',
-        'title' => ts($options['prefix_label'] . 'Last Name'),
+        'title' => $options['prefix_label'] . ts('Last Name'),
         'default_order' => 'ASC',
         'is_fields' => TRUE,
       ),
       $options['prefix'] . 'nick_name' => array(
         'name' => 'nick_name',
-        'title' => ts($options['prefix_label'] . 'Nick Name'),
+        'title' => $options['prefix_label'] . ts('Nick Name'),
         'is_fields' => TRUE,
       ),
       $options['prefix'] . 'gender_id' => array(
         'name' => 'gender_id',
-        'title' => ts($options['prefix_label'] . 'Gender'),
+        'title' => $options['prefix_label'] . ts('Gender'),
         'options' => CRM_Contact_BAO_Contact::buildOptions('gender_id'),
         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
         'alter_display' => 'alterGenderID',
@@ -5086,20 +5078,20 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a
         'is_filters' => TRUE,
       ),
       'birth_date' => array(
-        'title' => ts($options['prefix_label'] . 'Birth Date'),
+        'title' => $options['prefix_label'] . ts('Birth Date'),
         'operatorType' => CRM_Report_Form::OP_DATE,
         'type' => CRM_Utils_Type::T_DATE,
         'is_fields' => TRUE,
         'is_filters' => TRUE,
       ),
       'age' => array(
-        'title' => ts($options['prefix_label'] . 'Age'),
+        'title' => $options['prefix_label'] . ts('Age'),
         'dbAlias' => 'TIMESTAMPDIFF(YEAR, ' . $tableAlias . '.birth_date, CURDATE())',
         'type' => CRM_Utils_Type::T_INT,
         'is_fields' => TRUE,
       ),
       $options['prefix'] . 'is_deceased' => array(
-        'title' => ts($options['prefix_label'] . 'Is deceased'),
+        'title' => $options['prefix_label'] . ts('Is deceased'),
         'name' => 'is_deceased',
         'type' => CRM_Utils_Type::T_BOOLEAN,
         'is_fields' => FALSE,
@@ -5194,6 +5186,54 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a
     return $columns;
   }
 
+  /**
+   * Store group bys into array - so we can check elsewhere what is grouped.
+   */
+  protected function storeGroupByArray() {
+
+    if (CRM_Utils_Array::value('group_bys', $this->_params) &&
+      is_array($this->_params['group_bys']) &&
+      !empty($this->_params['group_bys'])
+    ) {
+      foreach ($this->_columns as $tableName => $table) {
+        $table = $this->_columns[$tableName];
+        if (array_key_exists('group_bys', $table)) {
+          foreach ($table['group_bys'] as $fieldName => $fieldData) {
+            $field = $this->_columns[$tableName]['metadata'][$fieldName];
+            if (!empty($this->_params['group_bys'][$fieldName])) {
+              if (!empty($field['chart'])) {
+                $this->assign('chartSupported', TRUE);
+              }
+
+              if (!empty($table['group_bys'][$fieldName]['frequency']) &&
+                !empty($this->_params['group_bys_freq'][$fieldName])
+              ) {
+
+                switch ($this->_params['group_bys_freq'][$fieldName]) {
+                  case 'FISCALYEAR':
+                    $this->_groupByArray[$tableName . '_' . $fieldName . '_start'] = self::fiscalYearOffset($field['dbAlias']);
+
+                  case 'YEAR':
+                    $this->_groupByArray[$tableName . '_' . $fieldName . '_start'] = " {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
+
+                  default:
+                    $this->_groupByArray[$tableName . '_' . $fieldName . '_start'] = "EXTRACT(YEAR_{$this->_params['group_bys_freq'][$fieldName]} FROM {$field['dbAlias']})";
+
+                }
+              }
+              else {
+                if (!in_array($field['dbAlias'], $this->_groupByArray)) {
+                  $this->_groupByArray[$tableName . '_' . $fieldName] = $field['dbAlias'];
+                }
+              }
+            }
+          }
+
+        }
+      }
+    }
+  }
+
   /**
    * @param $options
    *