X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FContact%2FBAO%2FQuery.php;h=990cca71e3c89291931b43d559fe7b5027d5291a;hb=b8416fbb0b2292d8b1390b970816415251d6a256;hp=edffa26e80108aca4adf8b9eade39323a9f48255;hpb=e4bfc4508108044c3ee98467f925233ade17e958;p=civicrm-core.git

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index edffa26e80..990cca71e3 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -1770,6 +1770,8 @@ class CRM_Contact_BAO_Query {
         CRM_Activity_BAO_Query::whereClauseSingle($values, $this);
         return;
 
+      case 'age_low':
+      case 'age_high':
       case 'birth_date_low':
       case 'birth_date_high':
       case 'deceased_date_low':
@@ -1777,6 +1779,10 @@ class CRM_Contact_BAO_Query {
         $this->demographics($values);
         return;
 
+      case 'age_asof_date':
+        // handled by demographics
+        return;
+
       case 'log_date_low':
       case 'log_date_high':
         $this->modifiedDates($values);
@@ -2918,6 +2924,9 @@ class CRM_Contact_BAO_Query {
     elseif (strpos($op, 'IN') !== FALSE) {
       $groups = array($op => $groups);
     }
+    elseif (is_array($groups) && count($groups)) {
+      $groups = array('IN' => $groups);
+    }
 
     // Find all the groups that are part of a saved search.
     $smartGroupClause = self::buildClause("id", $op, $groups, 'Int');
@@ -3853,7 +3862,12 @@ WHERE  $smartGroupClause
   public function demographics(&$values) {
     list($name, $op, $value, $grouping, $wildcard) = $values;
 
-    if (($name == 'birth_date_low') || ($name == 'birth_date_high')) {
+    if (($name == 'age_low') || ($name == 'age_high')) {
+      $this->ageRangeQueryBuilder($values,
+        'contact_a', 'age', 'birth_date', ts('Age')
+      );
+    }
+    elseif (($name == 'birth_date_low') || ($name == 'birth_date_high')) {
 
       $this->dateQueryBuilder($values,
         'contact_a', 'birth_date', 'birth_date', ts('Birth Date')
@@ -4716,6 +4730,7 @@ civicrm_relationship.is_permission_a_b = 0
 SELECT COUNT( conts.total_amount ) as total_count,
        SUM(   conts.total_amount ) as total_amount,
        AVG(   conts.total_amount ) as total_avg,
+       conts.total_amount          as amount,
        conts.currency              as currency";
     if ($this->_permissionWhereClause) {
       $where .= " AND " . $this->_permissionWhereClause;
@@ -4730,10 +4745,11 @@ SELECT COUNT( conts.total_amount ) as total_count,
     $summary = array();
     $summary['total'] = array();
     $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
+    $innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
+      civicrm_contribution.currency $from $completedWhere";
 
     $query = "$select FROM (
-      SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $completedWhere
-      GROUP BY civicrm_contribution.id
+      $innerQuery GROUP BY civicrm_contribution.id
     ) as conts
     GROUP BY currency";
 
@@ -4746,12 +4762,27 @@ SELECT COUNT( conts.total_amount ) as total_count,
       $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
       $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
     }
+
+    $orderBy = 'ORDER BY civicrm_contribution_total_amount_count DESC';
+    $groupBy = 'GROUP BY currency, civicrm_contribution.total_amount';
+    $modeSQL = "$select, conts.civicrm_contribution_total_amount_count as civicrm_contribution_total_amount_count FROM ($innerQuery
+    $groupBy $orderBy) as conts
+    GROUP BY currency";
+
+    $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
+
+    $medianSQL = "{$from} {$completedWhere}";
+    $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution');
+    $summary['total']['currencyCount'] = count($summary['total']['median']);
+
     if (!empty($summary['total']['amount'])) {
       $summary['total']['amount'] = implode(', ', $summary['total']['amount']);
       $summary['total']['avg'] = implode(', ', $summary['total']['avg']);
+      $summary['total']['mode'] = implode(', ', $summary['total']['mode']);
+      $summary['total']['median'] = implode(', ', $summary['total']['median']);
     }
     else {
-      $summary['total']['amount'] = $summary['total']['avg'] = 0;
+      $summary['total']['amount'] = $summary['total']['avg'] = $summary['total']['median'] = 0;
     }
 
     // soft credit summary
@@ -5138,6 +5169,104 @@ SELECT COUNT( conts.total_amount ) as cancel_count,
     }
   }
 
+
+  /**
+   * @param $values
+   * @param string $tableName
+   * @param string $fieldName
+   * @param string $dbFieldName
+   * @param $fieldTitle
+   * @param null $options
+   */
+  public function ageRangeQueryBuilder(
+    &$values,
+    $tableName, $fieldName,
+    $dbFieldName, $fieldTitle,
+    $options = NULL
+  ) {
+    list($name, $op, $value, $grouping, $wildcard) = $values;
+
+    $asofDateValues = $this->getWhereValues("{$fieldName}_asof_date", $grouping);
+    $asofDate = NULL;  // will be treated as current day
+    if ($asofDateValues) {
+      $asofDate = CRM_Utils_Date::processDate($asofDateValues[2]);
+      $asofDateFormat = CRM_Utils_Date::customFormat(substr($asofDate, 0, 8));
+      $fieldTitle .= ' ' . ts('as of') . ' ' . $asofDateFormat;
+    }
+
+    if ($name == "{$fieldName}_low" ||
+      $name == "{$fieldName}_high"
+    ) {
+      if (isset($this->_rangeCache[$fieldName])) {
+        return;
+      }
+      $this->_rangeCache[$fieldName] = 1;
+
+      $secondOP = $secondPhrase = $secondValue = NULL;
+
+      if ($name == "{$fieldName}_low") {
+        $firstPhrase = ts('greater than or equal to');
+        // NB: age > X means date of birth < Y
+        $firstOP = '<=';
+        $firstDate = self::calcDateFromAge($asofDate, $value, 'min');
+
+        $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
+        if (!empty($secondValues)) {
+          $secondOP = '>=';
+          $secondPhrase = ts('less than or equal to');
+          $secondValue = $secondValues[2];
+          $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'max');
+        }
+      }
+      else {
+        $firstOP = '>=';
+        $firstPhrase = ts('less than or equal to');
+        $firstDate = self::calcDateFromAge($asofDate, $value, 'max');
+
+        $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
+        if (!empty($secondValues)) {
+          $secondOP = '<=';
+          $secondPhrase = ts('greater than or equal to');
+          $secondValue = $secondValues[2];
+          $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'min');
+        }
+      }
+
+      if ($secondOP) {
+        $this->_where[$grouping][] = "
+( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
+( {$tableName}.{$dbFieldName} $secondOP '$secondDate' )
+";
+        $displayValue = $options ? $options[$value] : $value;
+        $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
+
+        $this->_qill[$grouping][]
+          = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
+      }
+      else {
+        $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
+        $displayValue = $options ? $options[$value] : $value;
+        $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
+      }
+      $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
+      return;
+    }
+  }
+
+  public static function calcDateFromAge($asofDate, $age, $type) {
+    $date = new DateTime($asofDate);
+    if ($type == "min") {
+      // minimum age is $age: dob <= date - age "235959"
+      $date->sub(new DateInterval("P" . $age . "Y"));
+      return $date->format('Ymd') . "235959";
+    }
+    else {
+      // max age is $age: dob >= date - (age + 1y) + 1d "000000"
+      $date->sub(new DateInterval("P" . ($age + 1) . "Y"))->add(new DateInterval("P1D"));
+      return $date->format('Ymd') . "000000";
+    }
+  }
+
   /**
    * Given the field name, operator, value & its data type
    * builds the where Clause for the query
@@ -5172,6 +5301,12 @@ SELECT COUNT( conts.total_amount ) as cancel_count,
         $clause = " (NULLIF($field, '') IS NOT NULL) ";
         return $clause;
 
+      case 'IN':
+      case 'NOT IN':
+        if (!empty($value) && is_array($value) && !array_key_exists($op, $value)) {
+          $value = array($op => $value);
+        }
+
       default:
         if (empty($dataType)) {
           $dataType = 'String';
@@ -5181,9 +5316,28 @@ SELECT COUNT( conts.total_amount ) as cancel_count,
           // widely used and consistent across the codebase
           // adding this here won't accept the search functions which don't submit an array
           if (($queryString = CRM_Core_DAO::createSqlFilter($field, $value, $dataType)) != FALSE) {
+
             return $queryString;
           }
+
+          // This is the here-be-dragons zone. We have no other hopes left for an array so lets assume it 'should' be array('IN' => array(2,5))
+          // but we got only array(2,5) from the form.
+          // We could get away with keeping this in 4.6 if we make it such that it throws an enotice in 4.7 so
+          // people have to de-slopify it.
+          if (!empty($value[0])) {
+            $dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReport;
+            if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => $value), $dataType)) != FALSE) {
+              return $queryString;
+            }
+          }
+          else {
+            $dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReportUsingOldFormat;
+            if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => array_keys($value)), $dataType)) != FALSE) {
+              return $queryString;
+            }
+          }
         }
+
         $value = CRM_Utils_Type::escape($value, $dataType);
         // if we don't have a dataType we should assume
         if ($dataType == 'String' || $dataType == 'Text') {
@@ -5537,7 +5691,7 @@ AND   displayRelType.is_active = 1
         // handles pseudoconstant fixes for all component
         elseif (in_array($value['pseudoField'], array('participant_status', 'participant_role'))) {
           $pseudoOptions = $viewValues = array();
-          $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', $value['pseudoField'], array('flip' => 1));
+          $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', $value['idCol']);
           foreach (explode(CRM_Core_DAO::VALUE_SEPARATOR, $val) as $k => $v) {
             $viewValues[] = $pseudoOptions[$v];
           }
@@ -5644,6 +5798,9 @@ AND   displayRelType.is_active = 1
     elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
       $pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
     }
+    elseif ($fieldName == 'contribution_product_id') {
+      $pseduoOptions = CRM_Contribute_PseudoConstant::products();
+    }
     elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') {
       $pseduoOptions = CRM_Core_PseudoConstant::group();
     }