3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2016
35 class CRM_Report_Form_Contribute_PCP
extends CRM_Report_Form
{
40 public function __construct() {
41 $this->_columns
= array(
42 'civicrm_contact' => array(
43 'dao' => 'CRM_Contact_DAO_Contact',
46 'title' => ts('Supporter'),
54 'contact_type' => array(
55 'title' => ts('Supporter Contact Type'),
57 'contact_sub_type' => array(
58 'title' => ts('Supporter Contact Subtype'),
63 'title' => ts('Supporter Name'),
64 'type' => CRM_Utils_Type
::T_STRING
,
68 'title' => ts('Contact ID'),
72 'grouping' => 'pcp-fields',
74 'civicrm_contribution_page' => array(
75 'dao' => 'CRM_Contribute_DAO_ContributionPage',
77 'page_title' => array(
78 'title' => ts('Contribution Page Title'),
84 'page_title' => array(
85 'title' => ts('Contribution Page Title'),
87 'type' => CRM_Utils_Type
::T_STRING
,
90 'grouping' => 'pcp-fields',
92 'civicrm_pcp' => array(
93 'dao' => 'CRM_PCP_DAO_PCP',
96 'title' => ts('Personal Campaign Title'),
99 'goal_amount' => array(
100 'title' => ts('Goal Amount'),
101 'type' => CRM_Utils_Type
::T_MONEY
,
107 'title' => ts('Personal Campaign Title'),
108 'type' => CRM_Utils_Type
::T_STRING
,
111 'grouping' => 'pcp-fields',
113 'civicrm_contribution_soft' => array(
114 'dao' => 'CRM_Contribute_DAO_ContributionSoft',
117 'title' => ts('Committed Amount'),
119 'type' => CRM_Utils_Type
::T_MONEY
,
121 'statistics' => array(
122 'sum' => ts('Committed Amount'),
126 'title' => ts('Amount Received'),
128 'type' => CRM_Utils_Type
::T_MONEY
,
130 // nice trick with dbAlias
131 'dbAlias' => 'SUM(IF( contribution_civireport.contribution_status_id > 1, 0, contribution_soft_civireport.amount))',
134 'title' => ts('Number of Donors'),
137 'statistics' => array(
138 'count' => ts('Number of Donors'),
144 'title' => ts('Amount Received'),
145 'type' => CRM_Utils_Type
::T_MONEY
,
146 'dbAlias' => 'SUM(IF( contribution_civireport.contribution_status_id > 1, 0, contribution_soft_civireport.amount))',
149 'grouping' => 'pcp-fields',
151 'civicrm_contribution' => array(
152 'dao' => 'CRM_Contribute_DAO_Contribution',
154 'contribution_id' => array(
156 'no_display' => TRUE,
159 'receive_date' => array(
160 'title' => ts('Most Recent Contribution'),
162 'statistics' => array(
163 'max' => ts('Most Recent Contribution'),
167 'grouping' => 'pcp-fields',
171 parent
::__construct();
174 public function from() {
176 FROM civicrm_pcp {$this->_aliases['civicrm_pcp']}
178 LEFT JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
179 ON {$this->_aliases['civicrm_pcp']}.id =
180 {$this->_aliases['civicrm_contribution_soft']}.pcp_id
182 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
183 ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
184 {$this->_aliases['civicrm_contribution']}.id
186 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
187 ON {$this->_aliases['civicrm_pcp']}.contact_id =
188 {$this->_aliases['civicrm_contact']}.id
190 LEFT JOIN civicrm_contribution_page {$this->_aliases['civicrm_contribution_page']}
191 ON {$this->_aliases['civicrm_pcp']}.page_id =
192 {$this->_aliases['civicrm_contribution_page']}.id";
195 public function groupBy() {
196 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_pcp']}.id");
199 public function orderBy() {
200 $this->_orderBy
= " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name ";
203 public function where() {
204 $whereClauses = $havingClauses = array();
206 foreach ($this->_columns
as $tableName => $table) {
207 if (array_key_exists('filters', $table)) {
208 foreach ($table['filters'] as $fieldName => $field) {
211 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
212 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
213 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
214 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
215 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
218 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
221 $clause = $this->whereClause($field,
223 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
224 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
225 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
230 if (!empty($clause)) {
231 if ($tableName == 'civicrm_contribution_soft' &&
232 $fieldName == 'amount_2'
234 $havingClauses[] = $clause;
237 $whereClauses[] = $clause;
243 if (empty($whereClauses)) {
244 $this->_where
= "WHERE ( 1 ) ";
247 $this->_where
= "WHERE " . implode(' AND ', $whereClauses);
249 if ($this->_aclWhere
) {
250 $this->_where
.= " AND {$this->_aclWhere} ";
253 if (!empty($havingClauses)) {
254 // use this clause to construct group by clause.
255 $this->_having
= "HAVING " . implode(' AND ', $havingClauses);
264 public function statistics(&$rows) {
265 $statistics = parent
::statistics($rows);
267 // Calculate totals from the civicrm_contribution_soft table.
268 $select = "SELECT SUM({$this->_aliases['civicrm_contribution_soft']}.amount) "
269 . "as committed_total, COUNT({$this->_aliases['civicrm_contribution_soft']}.id) "
270 . "as donors_total, SUM(IF( contribution_civireport.contribution_status_id > 1, 0, "
271 . "contribution_soft_civireport.amount)) AS received_total ";
272 $sql = "{$select} {$this->_from} {$this->_where}";
273 $dao = CRM_Core_DAO
::executeQuery($sql);
275 $committed_total = $dao->committed_total
;
276 $received_total = $dao->received_total
;
277 $donors_total = $dao->donors_total
;
279 // Calculate goal total goal from the PCP table (we only want one result per
280 // PCP page - the query above produces one result per contribution made).
281 $sql = "SELECT SUM(goal_amount) as goal_total FROM civicrm_pcp WHERE "
282 . "goal_amount IS NOT NULL AND id IN ("
283 . "SELECT DISTINCT {$this->_aliases['civicrm_pcp']}.id {$this->_from} "
286 $dao = CRM_Core_DAO
::executeQuery($sql);
288 $goal_total = $dao->goal_total
;
290 $statistics['counts']['goal_total'] = array(
291 'title' => ts('Goal Total'),
292 'value' => $goal_total,
293 'type' => CRM_Utils_Type
::T_MONEY
,
295 $statistics['counts']['committed_total'] = array(
296 'title' => ts('Total Committed'),
297 'value' => $committed_total,
298 'type' => CRM_Utils_Type
::T_MONEY
,
300 $statistics['counts']['received_total'] = array(
301 'title' => ts('Total Received'),
302 'value' => $received_total,
303 'type' => CRM_Utils_Type
::T_MONEY
,
305 $statistics['counts']['donors_total'] = array(
306 'title' => ts('Total Donors'),
307 'value' => $donors_total,
308 'type' => CRM_Utils_Type
::T_INT
,
314 * Alter display of rows.
316 * Iterate through the rows retrieved via SQL and make changes for display purposes,
317 * such as rendering contacts as links.
320 * Rows generated by SQL, with an array for each row.
322 public function alterDisplay(&$rows) {
324 $checkList = array();
325 foreach ($rows as $rowNum => $row) {
326 if (!empty($this->_noRepeats
) && $this->_outputMode
!= 'csv') {
327 // not repeat contact sort names if it matches with the one
329 $repeatFound = FALSE;
331 foreach ($row as $colName => $colVal) {
332 if (!empty($checkList[$colName]) &&
333 is_array($checkList[$colName]) &&
334 in_array($colVal, $checkList[$colName])
336 $rows[$rowNum][$colName] = "";
339 if (in_array($colName, $this->_noRepeats
)) {
340 $checkList[$colName][] = $colVal;
345 if (array_key_exists('civicrm_contact_sort_name', $row) &&
346 $rows[$rowNum]['civicrm_contact_sort_name'] &&
347 array_key_exists('civicrm_contact_id', $row)
349 $url = CRM_Utils_System
::url("civicrm/contact/view",
350 'reset=1&cid=' . $row['civicrm_contact_id'],
353 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
354 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");