<?php
/*
+--------------------------------------------------------------------+
- | CiviCRM version 4.6 |
+ | CiviCRM version 4.7 |
+--------------------------------------------------------------------+
| Copyright CiviCRM LLC (c) 2004-2015 |
+--------------------------------------------------------------------+
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':
$this->demographics($values);
return;
+ case 'age_asof_date':
+ // handled by demographics
+ return;
+
case 'log_date_low':
case 'log_date_high':
$this->modifiedDates($values);
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')
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;
$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";
$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
}
}
+
+ /**
+ * @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
// 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];
}
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();
}
projects / civicrm-core.git / blobdiff