projects / civicrm-core.git / blobdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | inline | side by side
Merge pull request #13632 from colemanw/CRM_Report_Form_Event
[civicrm-core.git] / CRM / Contact / BAO / Query.php
diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index 04e635399dc60721127b0c8e9403f8cb8cc34c72..5247434ab824ea3793a0b7e5ac44c2168b814b4d 100644 (file)
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -5103,99 +5103,13 @@ civicrm_relationship.start_date > {$today}
     $this->addModeToStats($summary, $from, $where);
     $this->addMedianToStats($summary, $where, $from);
 
-    $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'] = $summary['total']['median'] = 0;
-    }
-
-    // soft credit summary
     if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
-      $softCreditWhere = "$where AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution_soft.id IS NOT NULL";
-      $query = "
-        SELECT COUNT( conts.total_amount ) as total_count,
-          SUM(   conts.total_amount ) as total_amount,
-          AVG(   conts.total_amount ) as total_avg,
-          conts.currency              as currency
-        FROM (
-          SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere
-          GROUP BY civicrm_contribution_soft.id
-        ) as conts
-        GROUP BY currency";
-      $dao = CRM_Core_DAO::executeQuery($query);
-      $summary['soft_credit']['count'] = 0;
-      $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = array();
-      while ($dao->fetch()) {
-        $summary['soft_credit']['count'] += $dao->total_count;
-        $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
-        $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
-      }
-      if (!empty($summary['soft_credit']['amount'])) {
-        $summary['soft_credit']['amount'] = implode(', ', $summary['soft_credit']['amount']);
-        $summary['soft_credit']['avg'] = implode(', ', $summary['soft_credit']['avg']);
-      }
-      else {
-        $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
-      }
-    }
-
-    // hack $select
-    //@todo  - this could be one query using the IF in mysql - eg
-    //  SELECT sum(total_completed), sum(count_completed), sum(count_cancelled), sum(total_cancelled) FROM (
-    //   SELECT civicrm_contribution.total_amount, civicrm_contribution.currency  ,
-    //  IF(civicrm_contribution.contribution_status_id = 1, 1, 0 ) as count_completed,
-    //  IF(civicrm_contribution.contribution_status_id = 1, total_amount, 0 ) as total_completed,
-    //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, 1, 0 ) as count_cancelled,
-    //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, total_amount, 0 ) as total_cancelled
-    // FROM civicrm_contact contact_a
-    //  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
-    // WHERE  ( ... where clause....
-    // AND (civicrm_contribution.cancel_date IS NOT NULL OR civicrm_contribution.contribution_status_id = 1)
-    //  ) as conts
-
-    $select = "
-SELECT COUNT( conts.total_amount ) as cancel_count,
-       SUM(   conts.total_amount ) as cancel_amount,
-       AVG(   conts.total_amount ) as cancel_avg,
-       conts.currency              as currency";
-
-    $where .= " AND civicrm_contribution.cancel_date IS NOT NULL ";
-    if ($context == 'search') {
-      $where .= " AND contact_a.is_deleted = 0 ";
+      $this->addBasicSoftCreditStatsToStats($summary, $where, $from);
     }
 
-    $query = "$select FROM (
-      SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where
-      GROUP BY civicrm_contribution.id
-    ) as conts
-    GROUP BY currency";
+    $summary['total']['currencyCount'] = count($summary['total']['median']);
 
-    $dao = CRM_Core_DAO::executeQuery($query);
-
-    if ($dao->N <= 1) {
-      if ($dao->fetch()) {
-        $summary['cancel']['count'] = $dao->cancel_count;
-        $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
-        $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
-      }
-    }
-    else {
-      $summary['cancel']['count'] = 0;
-      $summary['cancel']['amount'] = $summary['cancel']['avg'] = array();
-      while ($dao->fetch()) {
-        $summary['cancel']['count'] += $dao->cancel_count;
-        $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
-        $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
-      }
-      $summary['cancel']['amount'] = implode(',&nbsp;', $summary['cancel']['amount']);
-      $summary['cancel']['avg'] = implode(',&nbsp;', $summary['cancel']['avg']);
-    }
+    $this->addBasicCancelStatsToSummary($summary, $where, $from);
 
     return $summary;
   }
@@ -6620,7 +6534,8 @@ AND   displayRelType.is_active = 1
    * @return array
    */
   protected function addBasicStatsToSummary(&$summary, $where, $from) {
-    $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
+    $summary['total']['count'] = 0;
+    $summary['total']['amount'] = $summary['total']['avg'] = [];
 
     $query = "
       SELECT COUNT( conts.total_amount ) as total_count,
@@ -6638,13 +6553,18 @@ AND   displayRelType.is_active = 1
 
     $dao = CRM_Core_DAO::executeQuery($query);
 
-    $summary['total']['count'] = 0;
-    $summary['total']['amount'] = $summary['total']['avg'] = [];
     while ($dao->fetch()) {
       $summary['total']['count'] += $dao->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);
     }
+    if (!empty($summary['total']['amount'])) {
+      $summary['total']['amount'] = implode(',&nbsp;', $summary['total']['amount']);
+      $summary['total']['avg'] = implode(',&nbsp;', $summary['total']['avg']);
+    }
+    else {
+      $summary['total']['amount'] = $summary['total']['avg'] = $summary['total']['median'] = 0;
+    }
     return $summary;
   }
 
@@ -6679,7 +6599,8 @@ AND   displayRelType.is_active = 1
       ) as conts
       GROUP BY currency";
 
-    $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
+    $mode = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
+    $summary['total']['mode'] = implode(',&nbsp;', (array) $mode);
   }
 
   /**
@@ -6697,7 +6618,89 @@ AND   displayRelType.is_active = 1
    */
   protected function addMedianToStats(&$summary, $where, $from) {
     $medianSQL = "{$from} {$where} AND civicrm_contribution.contribution_status_id = 1 ";
-    $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution');
+    $median = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution');
+    $summary['total']['median'] = implode(',&nbsp;', (array) $median);
+  }
+
+  /**
+   * Add basic soft credit statistics to summary array.
+   *
+   * @param array $summary
+   * @param string $where
+   * @param string $from
+   */
+  protected function addBasicSoftCreditStatsToStats(&$summary, $where, $from) {
+    $query = "
+      SELECT COUNT( conts.total_amount ) as total_count,
+      SUM(   conts.total_amount ) as total_amount,
+      AVG(   conts.total_amount ) as total_avg,
+      conts.currency as currency
+      FROM (
+        SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency
+        $from
+        $where AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution_soft.id IS NOT NULL
+        GROUP BY civicrm_contribution_soft.id
+      ) as conts
+      GROUP BY currency";
+
+    $dao = CRM_Core_DAO::executeQuery($query);
+    $summary['soft_credit']['count'] = 0;
+    $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = [];
+    while ($dao->fetch()) {
+      $summary['soft_credit']['count'] += $dao->total_count;
+      $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
+      $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
+    }
+    if (!empty($summary['soft_credit']['amount'])) {
+      $summary['soft_credit']['amount'] = implode(',&nbsp;', $summary['soft_credit']['amount']);
+      $summary['soft_credit']['avg'] = implode(',&nbsp;', $summary['soft_credit']['avg']);
+    }
+    else {
+      $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
+    }
+  }
+
+  /**
+   * Add basic stats about cancelled contributions to the summary.
+   *
+   * @param array $summary
+   * @param string $where
+   * @param string $from
+   */
+  protected function addBasicCancelStatsToSummary(&$summary, $where, $from) {
+    $query = "
+      SELECT COUNT( conts.total_amount ) as cancel_count,
+       SUM(   conts.total_amount ) as cancel_amount,
+       AVG(   conts.total_amount ) as cancel_avg,
+       conts.currency              as currency
+        FROM (
+      SELECT civicrm_contribution.total_amount, civicrm_contribution.currency
+      $from
+      $where  AND civicrm_contribution.cancel_date IS NOT NULL 
+      GROUP BY civicrm_contribution.id
+    ) as conts
+    GROUP BY currency";
+
+    $dao = CRM_Core_DAO::executeQuery($query);
+
+    if ($dao->N <= 1) {
+      if ($dao->fetch()) {
+        $summary['cancel']['count'] = $dao->cancel_count;
+        $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
+        $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
+      }
+    }
+    else {
+      $summary['cancel']['count'] = 0;
+      $summary['cancel']['amount'] = $summary['cancel']['avg'] = [];
+      while ($dao->fetch()) {
+        $summary['cancel']['count'] += $dao->cancel_count;
+        $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
+        $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
+      }
+      $summary['cancel']['amount'] = implode(',&nbsp;', $summary['cancel']['amount']);
+      $summary['cancel']['avg'] = implode(',&nbsp;', $summary['cancel']['avg']);
+    }
   }
 
 }