| 1 | <?php |
| 2 | |
| 3 | /* |
| 4 | +--------------------------------------------------------------------+ |
| 5 | | CiviCRM version 4.5 | |
| 6 | +--------------------------------------------------------------------+ |
| 7 | | Copyright CiviCRM LLC (c) 2004-2014 | |
| 8 | +--------------------------------------------------------------------+ |
| 9 | | This file is a part of CiviCRM. | |
| 10 | | | |
| 11 | | CiviCRM is free software; you can copy, modify, and distribute it | |
| 12 | | under the terms of the GNU Affero General Public License | |
| 13 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | |
| 14 | | | |
| 15 | | CiviCRM is distributed in the hope that it will be useful, but | |
| 16 | | WITHOUT ANY WARRANTY; without even the implied warranty of | |
| 17 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | |
| 18 | | See the GNU Affero General Public License for more details. | |
| 19 | | | |
| 20 | | You should have received a copy of the GNU Affero General Public | |
| 21 | | License and the CiviCRM Licensing Exception along | |
| 22 | | with this program; if not, contact CiviCRM LLC | |
| 23 | | at info[AT]civicrm[DOT]org. If you have questions about the | |
| 24 | | GNU Affero General Public License or the licensing of CiviCRM, | |
| 25 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | |
| 26 | +--------------------------------------------------------------------+ |
| 27 | */ |
| 28 | |
| 29 | /** |
| 30 | * |
| 31 | * @package CRM |
| 32 | * @copyright CiviCRM LLC (c) 2004-2014 |
| 33 | * $Id$ |
| 34 | * |
| 35 | */ |
| 36 | class CRM_Report_Form_Contribute_Repeat extends CRM_Report_Form { |
| 37 | protected $_amountClauseWithAND = NULL; |
| 38 | |
| 39 | public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report'); |
| 40 | |
| 41 | /** |
| 42 | * |
| 43 | */ |
| 44 | /** |
| 45 | * |
| 46 | */ |
| 47 | function __construct() { |
| 48 | $this->_columns = array( |
| 49 | 'civicrm_contact' => |
| 50 | array( |
| 51 | 'dao' => 'CRM_Contact_DAO_Contact', |
| 52 | 'grouping' => 'contact-fields', |
| 53 | 'fields' => |
| 54 | array( |
| 55 | 'sort_name' => |
| 56 | array( |
| 57 | 'title' => ts('Contact Name'), |
| 58 | 'no_repeat' => TRUE, |
| 59 | 'default' => TRUE, |
| 60 | ), |
| 61 | 'display_name' => |
| 62 | array( |
| 63 | 'title' => ts('Display Name'), |
| 64 | 'no_repeat' => TRUE, |
| 65 | ), |
| 66 | 'addressee_display' => |
| 67 | array( |
| 68 | 'title' => ts('Addressee Name'), |
| 69 | 'no_repeat' => TRUE, |
| 70 | ), |
| 71 | 'id' => |
| 72 | array( |
| 73 | 'no_display' => TRUE, |
| 74 | 'required' => TRUE, |
| 75 | ), |
| 76 | 'contact_type' => |
| 77 | array( |
| 78 | 'title' => ts('Contact Type'), |
| 79 | 'no_repeat' => TRUE, |
| 80 | ), |
| 81 | 'contact_sub_type' => |
| 82 | array( |
| 83 | 'title' => ts('Contact Subtype'), |
| 84 | 'no_repeat' => TRUE, |
| 85 | ), |
| 86 | ), |
| 87 | 'filters' => |
| 88 | array( |
| 89 | 'percentage_change' => |
| 90 | array( |
| 91 | 'title' => ts('Percentage Change'), |
| 92 | 'type' => CRM_Utils_Type::T_INT, |
| 93 | 'operatorType' => CRM_Report_Form::OP_INT, |
| 94 | 'name' => 'percentage_change', |
| 95 | 'dbAlias' => '( ( contribution_civireport2.total_amount_sum - contribution_civireport1.total_amount_sum ) * 100 / contribution_civireport1.total_amount_sum )', |
| 96 | ), |
| 97 | ), |
| 98 | 'group_bys' => |
| 99 | array( |
| 100 | 'id' => |
| 101 | array( |
| 102 | 'title' => ts('Contact'), |
| 103 | 'default' => TRUE, |
| 104 | ), |
| 105 | ), |
| 106 | ), |
| 107 | 'civicrm_email' => |
| 108 | array( |
| 109 | 'dao' => 'CRM_Core_DAO_Email', |
| 110 | 'fields' => |
| 111 | array( |
| 112 | 'email' => |
| 113 | array( |
| 114 | 'title' => ts('Email'), |
| 115 | 'no_repeat' => TRUE, |
| 116 | ), |
| 117 | ), |
| 118 | 'grouping' => 'contact-fields', |
| 119 | ), |
| 120 | 'civicrm_phone' => |
| 121 | array( |
| 122 | 'dao' => 'CRM_Core_DAO_Phone', |
| 123 | 'fields' => |
| 124 | array( |
| 125 | 'phone' => |
| 126 | array( |
| 127 | 'title' => ts('Phone'), |
| 128 | 'no_repeat' => TRUE, |
| 129 | ), |
| 130 | ), |
| 131 | 'grouping' => 'contact-fields', |
| 132 | ), |
| 133 | 'civicrm_address' => |
| 134 | array( |
| 135 | 'dao' => 'CRM_Core_DAO_Address', |
| 136 | 'grouping' => 'contact-fields', |
| 137 | 'fields' => |
| 138 | array( |
| 139 | 'street_address' => array('title' => ts('Street Address')), |
| 140 | 'supplemental_address_1' => array('title' => ts('Supplemental Address 1')), |
| 141 | 'city' => array('title' => ts('City')), |
| 142 | 'country_id' => array('title' => ts('Country')), |
| 143 | 'state_province_id' => array('title' => ts('State/Province')), |
| 144 | 'postal_code' => array('title' => ts('Postal Code')), |
| 145 | ), |
| 146 | 'group_bys' => |
| 147 | array( |
| 148 | 'country_id' => array('title' => ts('Country')), |
| 149 | 'state_province_id' => array('title' => ts('State/Province'), |
| 150 | ), |
| 151 | ), |
| 152 | ), |
| 153 | 'civicrm_financial_type' => |
| 154 | array( |
| 155 | 'dao' => 'CRM_Financial_DAO_FinancialType', |
| 156 | 'fields' => array('financial_type' => array('title' => ts('Financial Type'))), |
| 157 | 'grouping' => 'contri-fields', |
| 158 | 'group_bys' => |
| 159 | array('financial_type' => |
| 160 | array( |
| 161 | 'name' => 'id', |
| 162 | 'title' => ts('Financial Type'), |
| 163 | ), |
| 164 | ), |
| 165 | ), |
| 166 | 'civicrm_contribution' => |
| 167 | array( |
| 168 | 'dao' => 'CRM_Contribute_DAO_Contribution', |
| 169 | 'fields' => |
| 170 | array( |
| 171 | 'contribution_source' => NULL, |
| 172 | 'total_amount1' => |
| 173 | array( |
| 174 | 'name' => 'total_amount', |
| 175 | 'alias' => 'contribution1', |
| 176 | 'title' => ts('Range One Stat'), |
| 177 | 'type' => CRM_Utils_Type::T_MONEY, |
| 178 | 'default' => TRUE, |
| 179 | 'required' => TRUE, |
| 180 | 'clause' => ' |
| 181 | contribution_civireport1.total_amount_count as contribution1_total_amount_count, |
| 182 | contribution_civireport1.total_amount_sum as contribution1_total_amount_sum', |
| 183 | ), |
| 184 | 'total_amount2' => |
| 185 | array( |
| 186 | 'name' => 'total_amount', |
| 187 | 'alias' => 'contribution2', |
| 188 | 'title' => ts('Range Two Stat'), |
| 189 | 'type' => CRM_Utils_Type::T_MONEY, |
| 190 | 'default' => TRUE, |
| 191 | 'required' => TRUE, |
| 192 | 'clause' => ' |
| 193 | contribution_civireport2.total_amount_count as contribution2_total_amount_count, |
| 194 | contribution_civireport2.total_amount_sum as contribution2_total_amount_sum', |
| 195 | ), |
| 196 | ), |
| 197 | 'grouping' => 'contri-fields', |
| 198 | 'filters' => |
| 199 | array( |
| 200 | 'receive_date1' => |
| 201 | array( |
| 202 | 'title' => ts('Initial Date Range'), |
| 203 | 'default' => 'previous.year', |
| 204 | 'type' => CRM_Utils_Type::T_DATE, |
| 205 | 'operatorType' => CRM_Report_Form::OP_DATE, |
| 206 | 'name' => 'receive_date', |
| 207 | ), |
| 208 | 'receive_date2' => |
| 209 | array( |
| 210 | 'title' => ts('Second Date Range'), |
| 211 | 'default' => 'this.year', |
| 212 | 'type' => CRM_Utils_Type::T_DATE, |
| 213 | 'operatorType' => CRM_Report_Form::OP_DATE, |
| 214 | 'name' => 'receive_date', |
| 215 | ), |
| 216 | 'total_amount1' => |
| 217 | array( |
| 218 | 'title' => ts('Range One Amount'), |
| 219 | 'type' => CRM_Utils_Type::T_INT, |
| 220 | 'operatorType' => CRM_Report_Form::OP_INT, |
| 221 | 'name' => 'total_amount', |
| 222 | ), |
| 223 | 'total_amount2' => |
| 224 | array( |
| 225 | 'title' => ts('Range Two Amount'), |
| 226 | 'type' => CRM_Utils_Type::T_INT, |
| 227 | 'operatorType' => CRM_Report_Form::OP_INT, |
| 228 | 'name' => 'total_amount', |
| 229 | ), |
| 230 | 'financial_type_id' => |
| 231 | array( |
| 232 | 'title' => ts('Financial Type'), |
| 233 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
| 234 | 'options' => CRM_Contribute_PseudoConstant::financialType(), |
| 235 | ), |
| 236 | 'contribution_status_id' => |
| 237 | array( |
| 238 | 'title' => ts('Contribution Status'), |
| 239 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
| 240 | 'options' => CRM_Contribute_PseudoConstant::contributionStatus(), |
| 241 | 'default' => array('1'), |
| 242 | ), |
| 243 | ), |
| 244 | 'group_bys' => array('contribution_source' => NULL), |
| 245 | ), |
| 246 | ); |
| 247 | |
| 248 | $this->_groupFilter = TRUE; |
| 249 | $this->_tagFilter = TRUE; |
| 250 | |
| 251 | parent::__construct(); |
| 252 | } |
| 253 | |
| 254 | function preProcess() { |
| 255 | parent::preProcess(); |
| 256 | } |
| 257 | |
| 258 | /** |
| 259 | * @param bool $freeze |
| 260 | * |
| 261 | * @return array |
| 262 | */ |
| 263 | function setDefaultValues($freeze = TRUE) { |
| 264 | return parent::setDefaultValues($freeze); |
| 265 | } |
| 266 | |
| 267 | function select() { |
| 268 | $select = array(); |
| 269 | $append = NULL; |
| 270 | // since contact fields not related to financial type |
| 271 | if (array_key_exists('financial_type', $this->_params['group_bys']) || |
| 272 | array_key_exists('contribution_source', $this->_params['group_bys']) |
| 273 | ) { |
| 274 | unset($this->_columns['civicrm_contact']['fields']['id']); |
| 275 | } |
| 276 | |
| 277 | foreach ($this->_columns as $tableName => $table) { |
| 278 | if (array_key_exists('fields', $table)) { |
| 279 | foreach ($table['fields'] as $fieldName => $field) { |
| 280 | if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) { |
| 281 | if (isset($field['clause'])) { |
| 282 | $select[] = $field['clause']; |
| 283 | |
| 284 | // FIXME: dirty hack for setting columnHeaders |
| 285 | $this->_columnHeaders["{$field['alias']}_{$field['name']}_sum"]['type'] = CRM_Utils_Array::value('type', $field); |
| 286 | $this->_columnHeaders["{$field['alias']}_{$field['name']}_sum"]['title'] = $field['title']; |
| 287 | $this->_columnHeaders["{$field['alias']}_{$field['name']}_count"]['type'] = CRM_Utils_Array::value('type', $field); |
| 288 | $this->_columnHeaders["{$field['alias']}_{$field['name']}_count"]['title'] = $field['title']; |
| 289 | continue; |
| 290 | } |
| 291 | |
| 292 | // only include statistics columns if set |
| 293 | $select[] = "{$field['dbAlias']} as {$field['alias']}_{$field['name']}"; |
| 294 | $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['type'] = CRM_Utils_Array::value('type', $field); |
| 295 | $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['title'] = CRM_Utils_Array::value('title', $field); |
| 296 | if (!empty($field['no_display'])) { |
| 297 | $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['no_display'] = TRUE; |
| 298 | } |
| 299 | } |
| 300 | } |
| 301 | } |
| 302 | } |
| 303 | |
| 304 | $this->_select = "SELECT " . implode(', ', $select) . " "; |
| 305 | } |
| 306 | |
| 307 | /** |
| 308 | * @param bool $tableCol |
| 309 | */ |
| 310 | function groupBy($tableCol = FALSE) { |
| 311 | $this->_groupBy = ""; |
| 312 | if (!empty($this->_params['group_bys']) && is_array($this->_params['group_bys'])) { |
| 313 | foreach ($this->_columns as $tableName => $table) { |
| 314 | if (array_key_exists('group_bys', $table)) { |
| 315 | foreach ($table['group_bys'] as $fieldName => $field) { |
| 316 | if (!empty($this->_params['group_bys'][$fieldName])) { |
| 317 | if ($tableCol) { |
| 318 | return array($tableName, $field['alias'], $field['name']); |
| 319 | } |
| 320 | else { |
| 321 | $this->_groupBy[] = "{$field['dbAlias']}"; |
| 322 | } |
| 323 | } |
| 324 | } |
| 325 | } |
| 326 | } |
| 327 | |
| 328 | $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy); |
| 329 | } |
| 330 | } |
| 331 | |
| 332 | function from() { |
| 333 | list($fromTable, $fromAlias, $fromCol) = $this->groupBy(TRUE); |
| 334 | $from = "$fromTable $fromAlias"; |
| 335 | |
| 336 | if ($fromTable == 'civicrm_contact') { |
| 337 | $contriCol = "contact_id"; |
| 338 | $from .= " |
| 339 | LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id |
| 340 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} |
| 341 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1 |
| 342 | LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} |
| 343 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1"; |
| 344 | |
| 345 | } |
| 346 | else if ($fromTable == 'civicrm_financial_type') { |
| 347 | $contriCol = "financial_type_id"; |
| 348 | } |
| 349 | elseif ($fromTable == 'civicrm_contribution') { |
| 350 | $contriCol = $fromCol; |
| 351 | } |
| 352 | elseif ($fromTable == 'civicrm_address') { |
| 353 | $from .= " |
| 354 | INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON {$this->_aliases['civicrm_address']}.contact_id = {$this->_aliases['civicrm_contact']}.id"; |
| 355 | $fromAlias = $this->_aliases['civicrm_contact']; |
| 356 | $fromCol = "id"; |
| 357 | $contriCol = "contact_id"; |
| 358 | } |
| 359 | |
| 360 | $this->_from = " |
| 361 | FROM $from |
| 362 | LEFT JOIN civicrm_temp_civireport_repeat1 {$this->_aliases['civicrm_contribution']}1 |
| 363 | ON $fromAlias.$fromCol = {$this->_aliases['civicrm_contribution']}1.$contriCol |
| 364 | LEFT JOIN civicrm_temp_civireport_repeat2 {$this->_aliases['civicrm_contribution']}2 |
| 365 | ON $fromAlias.$fromCol = {$this->_aliases['civicrm_contribution']}2.$contriCol"; |
| 366 | } |
| 367 | |
| 368 | /** |
| 369 | * @param string $replaceAliasWith |
| 370 | * |
| 371 | * @return mixed|string |
| 372 | */ |
| 373 | function whereContribution($replaceAliasWith = 'contribution1') { |
| 374 | $clauses = array("is_test" => "{$this->_aliases['civicrm_contribution']}.is_test = 0"); |
| 375 | |
| 376 | foreach ($this->_columns['civicrm_contribution']['filters'] as $fieldName => $field) { |
| 377 | $clause = NULL; |
| 378 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { |
| 379 | $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params); |
| 380 | $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params); |
| 381 | $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params); |
| 382 | |
| 383 | $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']); |
| 384 | } |
| 385 | else { |
| 386 | $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); |
| 387 | if ($op) { |
| 388 | $clause = $this->whereClause($field, |
| 389 | $op, |
| 390 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), |
| 391 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), |
| 392 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) |
| 393 | ); |
| 394 | } |
| 395 | } |
| 396 | if (!empty($clause)) { |
| 397 | $clauses[$fieldName] = $clause; |
| 398 | } |
| 399 | } |
| 400 | |
| 401 | if (!$this->_amountClauseWithAND) { |
| 402 | $amountClauseWithAND = array(); |
| 403 | if (!empty($clauses['total_amount1'])) { |
| 404 | $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount", |
| 405 | "{$this->_aliases['civicrm_contribution']}1.total_amount_sum", $clauses['total_amount1']); |
| 406 | } |
| 407 | if (!empty($clauses['total_amount2'])) { |
| 408 | $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount", |
| 409 | "{$this->_aliases['civicrm_contribution']}2.total_amount_sum", $clauses['total_amount2']); |
| 410 | } |
| 411 | $this->_amountClauseWithAND = !empty($amountClauseWithAND) ? implode(' AND ', $amountClauseWithAND) : NULL; |
| 412 | } |
| 413 | |
| 414 | if ($replaceAliasWith == 'contribution1') { |
| 415 | unset($clauses['receive_date2'], $clauses['total_amount2']); |
| 416 | } |
| 417 | else { |
| 418 | unset($clauses['receive_date1'], $clauses['total_amount1']); |
| 419 | } |
| 420 | |
| 421 | $whereClause = !empty($clauses) ? "WHERE " . implode(' AND ', $clauses) : ''; |
| 422 | |
| 423 | if ($replaceAliasWith) { |
| 424 | $whereClause = str_replace($this->_aliases['civicrm_contribution'], $replaceAliasWith, $whereClause); |
| 425 | } |
| 426 | |
| 427 | return $whereClause; |
| 428 | } |
| 429 | |
| 430 | function where() { |
| 431 | if (!$this->_amountClauseWithAND) { |
| 432 | $this->_amountClauseWithAND = |
| 433 | "!({$this->_aliases['civicrm_contribution']}1.total_amount_count IS NULL AND {$this->_aliases['civicrm_contribution']}2.total_amount_count IS NULL)"; |
| 434 | } |
| 435 | $clauses = array("atleast_one_amount" => $this->_amountClauseWithAND); |
| 436 | |
| 437 | foreach ($this->_columns as $tableName => $table) { |
| 438 | if (array_key_exists('filters', $table) && $tableName != 'civicrm_contribution') { |
| 439 | foreach ($table['filters'] as $fieldName => $field) { |
| 440 | $clause = NULL; |
| 441 | $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); |
| 442 | if ($op) { |
| 443 | $clause = $this->whereClause($field, |
| 444 | $op, |
| 445 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), |
| 446 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), |
| 447 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) |
| 448 | ); |
| 449 | } |
| 450 | if (!empty($clause)) { |
| 451 | $clauses[$fieldName] = $clause; |
| 452 | } |
| 453 | } |
| 454 | } |
| 455 | } |
| 456 | |
| 457 | $this->_where = !empty($clauses) ? "WHERE " . implode(' AND ', $clauses) : ''; |
| 458 | } |
| 459 | |
| 460 | /** |
| 461 | * @param $fields |
| 462 | * @param $files |
| 463 | * @param $self |
| 464 | * |
| 465 | * @return array |
| 466 | */ |
| 467 | static function formRule($fields, $files, $self) { |
| 468 | |
| 469 | $errors = $checkDate = $errorCount = array(); |
| 470 | |
| 471 | $rules = array( |
| 472 | 'id' => array( |
| 473 | 'sort_name', |
| 474 | 'display_name', |
| 475 | 'addressee_display', |
| 476 | 'contact_type', |
| 477 | 'contact_sub_type', |
| 478 | 'email', |
| 479 | 'phone', |
| 480 | 'state_province_id', |
| 481 | 'country_id', |
| 482 | 'city', |
| 483 | 'street_address', |
| 484 | 'supplemental_address_1', |
| 485 | 'postal_code', |
| 486 | ), |
| 487 | 'country_id' => array('country_id'), |
| 488 | 'state_province_id' => array('country_id', 'state_province_id'), |
| 489 | 'contribution_source' => array('contribution_source'), |
| 490 | 'financial_type' => array('financial_type'), |
| 491 | ); |
| 492 | |
| 493 | $idMapping = array( |
| 494 | 'id' => ts('Contact'), |
| 495 | 'exposed_id' => ts('Contact'), |
| 496 | 'country_id' => ts('Country'), |
| 497 | 'state_province_id' => ts('State/Province'), |
| 498 | 'contribution_source' => ts('Contribution Source'), |
| 499 | 'financial_type' => ts('Financial Type'), |
| 500 | 'sort_name' => ts('Contact Name'), |
| 501 | 'email' => ts('Email'), |
| 502 | 'phone' => ts('Phone'), |
| 503 | ); |
| 504 | |
| 505 | if (empty($fields['group_bys'])) { |
| 506 | $errors['fields'] = ts('Please select at least one Group by field.'); |
| 507 | } |
| 508 | elseif ((array_key_exists('contribution_source', $fields['group_bys']) || |
| 509 | array_key_exists('contribution_type', $fields['group_bys']) |
| 510 | ) && |
| 511 | (count($fields['group_bys']) > 1) |
| 512 | ) { |
| 513 | $errors['fields'] = ts('You can not use other Group by with Financial type or Contribution source.'); |
| 514 | } |
| 515 | else { |
| 516 | foreach ($fields['fields'] as $fld_id => $value) { |
| 517 | if (!($fld_id == 'total_amount1') && !($fld_id == 'total_amount2')) { |
| 518 | $found = FALSE; |
| 519 | $invlidGroups = array(); |
| 520 | foreach ($fields['group_bys'] as $grp_id => $val) { |
| 521 | $validFields = $rules[$grp_id]; |
| 522 | if (in_array($fld_id, $validFields)) { |
| 523 | $found = TRUE; |
| 524 | } |
| 525 | else { |
| 526 | $invlidGroups[] = $idMapping[$grp_id]; |
| 527 | } |
| 528 | } |
| 529 | if (!$found) { |
| 530 | $erorrGrps = implode(',', $invlidGroups); |
| 531 | $tempErrors[] = ts("Do not select field %1 with Group by %2.", array(1 => $idMapping[$fld_id], 2 => $erorrGrps)); |
| 532 | } |
| 533 | } |
| 534 | } |
| 535 | if (!empty($tempErrors)) { |
| 536 | $errors['fields'] = implode("<br>", $tempErrors); |
| 537 | } |
| 538 | } |
| 539 | |
| 540 | if (!empty($fields['gid_value']) && !empty($fields['group_bys'])) { |
| 541 | if (!array_key_exists('id', $fields['group_bys'])) { |
| 542 | $errors['gid_value'] = ts("Filter with Group only allow with group by Contact"); |
| 543 | } |
| 544 | } |
| 545 | |
| 546 | if ($fields['receive_date1_relative'] == '0') { |
| 547 | $checkDate['receive_date1']['receive_date1_from'] = $fields['receive_date1_from']; |
| 548 | $checkDate['receive_date1']['receive_date1_to'] = $fields['receive_date1_to']; |
| 549 | } |
| 550 | |
| 551 | if ($fields['receive_date2_relative'] == '0') { |
| 552 | $checkDate['receive_date2']['receive_date2_from'] = $fields['receive_date2_from']; |
| 553 | $checkDate['receive_date2']['receive_date2_to'] = $fields['receive_date2_to']; |
| 554 | } |
| 555 | |
| 556 | foreach ($checkDate as $date_range => $range_data) { |
| 557 | foreach ($range_data as $key => $value) { |
| 558 | if (CRM_Utils_Date::isDate($value)) { |
| 559 | $errorCount[$date_range][$key]['valid'] = 'true'; |
| 560 | $errorCount[$date_range][$key]['is_empty'] = 'false'; |
| 561 | } |
| 562 | else { |
| 563 | $errorCount[$date_range][$key]['valid'] = 'false'; |
| 564 | $errorCount[$date_range][$key]['is_empty'] = 'true'; |
| 565 | if (is_array($value)) { |
| 566 | foreach ($value as $v) { |
| 567 | if ($v) { |
| 568 | $errorCount[$date_range][$key]['is_empty'] = 'false'; |
| 569 | } |
| 570 | } |
| 571 | } |
| 572 | elseif (!isset($value)) { |
| 573 | $errorCount[$date_range][$key]['is_empty'] = 'false'; |
| 574 | } |
| 575 | } |
| 576 | } |
| 577 | } |
| 578 | |
| 579 | $errorText = ts("Select valid date range"); |
| 580 | foreach ($errorCount as $date_range => $error_data) { |
| 581 | |
| 582 | if (($error_data[$date_range . '_from']['valid'] == 'false') && |
| 583 | ($error_data[$date_range . '_to']['valid'] == 'false') |
| 584 | ) { |
| 585 | |
| 586 | if (($error_data[$date_range . '_from']['is_empty'] == 'true') && |
| 587 | ($error_data[$date_range . '_to']['is_empty'] == 'true') |
| 588 | ) { |
| 589 | $errors[$date_range . '_relative'] = $errorText; |
| 590 | } |
| 591 | |
| 592 | if ($error_data[$date_range . '_from']['is_empty'] == 'false') { |
| 593 | $errors[$date_range . '_from'] = $errorText; |
| 594 | } |
| 595 | |
| 596 | if ($error_data[$date_range . '_to']['is_empty'] == 'false') { |
| 597 | $errors[$date_range . '_to'] = $errorText; |
| 598 | } |
| 599 | } |
| 600 | elseif (($error_data[$date_range . '_from']['valid'] == 'true') && |
| 601 | ($error_data[$date_range . '_to']['valid'] == 'false') |
| 602 | ) { |
| 603 | if ($error_data[$date_range . '_to']['is_empty'] == 'false') { |
| 604 | $errors[$date_range . '_to'] = $errorText; |
| 605 | } |
| 606 | } |
| 607 | elseif (($error_data[$date_range . '_from']['valid'] == 'false') && |
| 608 | ($error_data[$date_range . '_to']['valid'] == 'true') |
| 609 | ) { |
| 610 | if ($error_data[$date_range . '_from']['is_empty'] == 'false') { |
| 611 | $errors[$date_range . '_from'] = $errorText; |
| 612 | } |
| 613 | } |
| 614 | } |
| 615 | |
| 616 | return $errors; |
| 617 | } |
| 618 | |
| 619 | /** |
| 620 | * @param $rows |
| 621 | * |
| 622 | * @return array |
| 623 | */ |
| 624 | function statistics(&$rows) { |
| 625 | $statistics = parent::statistics($rows); |
| 626 | |
| 627 | //fetch contributions for both date ranges from pre-existing temp tables |
| 628 | $sql = " |
| 629 | CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat3 |
| 630 | SELECT contact_id FROM civicrm_temp_civireport_repeat1 UNION SELECT contact_id FROM civicrm_temp_civireport_repeat2;"; |
| 631 | $dao = CRM_Core_DAO::executeQuery($sql); |
| 632 | |
| 633 | $sql = " |
| 634 | SELECT civicrm_temp_civireport_repeat3.contact_id, |
| 635 | civicrm_temp_civireport_repeat1.total_amount_sum as contribution1_total_amount_sum, |
| 636 | civicrm_temp_civireport_repeat2.total_amount_sum as contribution2_total_amount_sum |
| 637 | FROM civicrm_temp_civireport_repeat3 |
| 638 | LEFT JOIN civicrm_temp_civireport_repeat1 |
| 639 | ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat1.contact_id |
| 640 | LEFT JOIN civicrm_temp_civireport_repeat2 |
| 641 | ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat2.contact_id"; |
| 642 | $dao = CRM_Core_DAO::executeQuery($sql); |
| 643 | |
| 644 | //store contributions in array 'contact_sums' for comparison |
| 645 | $contact_sums = array(); |
| 646 | while ($dao->fetch()) { |
| 647 | $contact_sums[$dao->contact_id] = |
| 648 | array( |
| 649 | 'contribution1_total_amount_sum' => $dao->contribution1_total_amount_sum, |
| 650 | 'contribution2_total_amount_sum' => $dao->contribution2_total_amount_sum |
| 651 | ); |
| 652 | } |
| 653 | |
| 654 | $total_distinct_contacts = count($contact_sums); |
| 655 | $number_maintained = 0; |
| 656 | $number_upgraded = 0; |
| 657 | $number_downgraded = 0; |
| 658 | $number_new = 0; |
| 659 | $number_lapsed = 0; |
| 660 | |
| 661 | foreach ($contact_sums as $uid => $row) { |
| 662 | if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) { |
| 663 | $change = ($row['contribution1_total_amount_sum'] - $row['contribution2_total_amount_sum']); |
| 664 | if($change == 0) |
| 665 | $number_maintained += 1; |
| 666 | elseif($change > 0) |
| 667 | $number_upgraded += 1; |
| 668 | elseif($change < 0) |
| 669 | $number_downgraded += 1; |
| 670 | } |
| 671 | elseif ($row['contribution1_total_amount_sum']) { |
| 672 | $number_new +=1; |
| 673 | } |
| 674 | elseif ($row['contribution2_total_amount_sum']) { |
| 675 | $number_lapsed +=1; |
| 676 | } |
| 677 | } |
| 678 | |
| 679 | //calculate percentages from numbers |
| 680 | $percent_maintained = ($number_maintained / $total_distinct_contacts) * 100; |
| 681 | $percent_upgraded = ($number_upgraded / $total_distinct_contacts) * 100; |
| 682 | $percent_downgraded = ($number_downgraded / $total_distinct_contacts) * 100; |
| 683 | $percent_new = ($number_new / $total_distinct_contacts) * 100; |
| 684 | $percent_lapsed = ($number_lapsed / $total_distinct_contacts) * 100; |
| 685 | |
| 686 | //display percentages for new, lapsed, upgraded, downgraded, and maintained contributors |
| 687 | $statistics['counts']['count_new'] = array( |
| 688 | 'value' => $percent_new, |
| 689 | 'title' => '% New Donors', |
| 690 | ); |
| 691 | $statistics['counts']['count_lapsed'] = array( |
| 692 | 'value' => $percent_lapsed, |
| 693 | 'title' => '% Lapsed Donors', |
| 694 | ); |
| 695 | $statistics['counts']['count_upgraded'] = array( |
| 696 | 'value' => $percent_upgraded, |
| 697 | 'title' => '% Upgraded Donors', |
| 698 | ); |
| 699 | $statistics['counts']['count_downgraded'] = array( |
| 700 | 'value' => $percent_downgraded, |
| 701 | 'title' => '% Downgraded Donors', |
| 702 | ); |
| 703 | $statistics['counts']['count_maintained'] = array( |
| 704 | 'value' => $percent_maintained, |
| 705 | 'title' => '% Maintained Donors', |
| 706 | ); |
| 707 | |
| 708 | $select = " |
| 709 | SELECT COUNT({$this->_aliases['civicrm_contribution']}1.total_amount_count ) as count, |
| 710 | SUM({$this->_aliases['civicrm_contribution']}1.total_amount_sum ) as amount, |
| 711 | ROUND(AVG({$this->_aliases['civicrm_contribution']}1.total_amount_sum), 2) as avg, |
| 712 | COUNT({$this->_aliases['civicrm_contribution']}2.total_amount_count ) as count2, |
| 713 | SUM({$this->_aliases['civicrm_contribution']}2.total_amount_sum ) as amount2, |
| 714 | ROUND(AVG({$this->_aliases['civicrm_contribution']}2.total_amount_sum), 2) as avg2, |
| 715 | currency"; |
| 716 | $sql = "{$select} {$this->_from} {$this->_where} |
| 717 | GROUP BY currency |
| 718 | "; |
| 719 | $dao = CRM_Core_DAO::executeQuery($sql); |
| 720 | |
| 721 | $amount = $average = $amount2 = $average2 = array(); |
| 722 | $count = $count2 = 0; |
| 723 | while ($dao->fetch()) { |
| 724 | if ($dao->amount) { |
| 725 | $amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency)."(".$dao->count.")"; |
| 726 | $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency); |
| 727 | } |
| 728 | |
| 729 | $count += $dao->count; |
| 730 | if ($dao->amount2) { |
| 731 | $amount2[] = CRM_Utils_Money::format($dao->amount2, $dao->currency)."(".$dao->count.")"; |
| 732 | $average2[] = CRM_Utils_Money::format($dao->avg2, $dao->currency); |
| 733 | } |
| 734 | $count2 += $dao->count2; |
| 735 | } |
| 736 | |
| 737 | $statistics['counts']['range_one_title'] = array('title' => 'Initial Date Range:'); |
| 738 | $statistics['counts']['amount'] = array( |
| 739 | 'value' => implode(', ', $amount), |
| 740 | 'title' => 'Total Amount', |
| 741 | 'type' => CRM_Utils_Type::T_STRING, |
| 742 | ); |
| 743 | $statistics['counts']['count'] = array( |
| 744 | 'value' => $count, |
| 745 | 'title' => 'Total Donations', |
| 746 | ); |
| 747 | $statistics['counts']['avg'] = array( |
| 748 | 'value' => implode(', ', $average), |
| 749 | 'title' => 'Average', |
| 750 | 'type' => CRM_Utils_Type::T_STRING, |
| 751 | ); |
| 752 | $statistics['counts']['range_two_title'] = array( |
| 753 | 'title' => 'Second Date Range:', |
| 754 | ); |
| 755 | $statistics['counts']['amount2'] = array( |
| 756 | 'value' => implode(', ', $amount2), |
| 757 | 'title' => 'Total Amount', |
| 758 | 'type' => CRM_Utils_Type::T_STRING, |
| 759 | ); |
| 760 | $statistics['counts']['count2'] = array( |
| 761 | 'value' => $count2, |
| 762 | 'title' => 'Total Donations', |
| 763 | ); |
| 764 | $statistics['counts']['avg2'] = array( |
| 765 | 'value' => implode(', ', $average2), |
| 766 | 'title' => 'Average', |
| 767 | 'type' => CRM_Utils_Type::T_STRING, |
| 768 | ); |
| 769 | |
| 770 | return $statistics; |
| 771 | } |
| 772 | |
| 773 | function postProcess() { |
| 774 | $this->beginPostProcess(); |
| 775 | $create = $subSelect1 = $subSelect2 = NULL; |
| 776 | list($fromTable, $fromAlias, $fromCol) = $this->groupBy(TRUE); |
| 777 | if ($fromTable == 'civicrm_contact') { |
| 778 | $contriCol = "contact_id"; |
| 779 | } |
| 780 | elseif ($fromTable == 'civicrm_contribution_type') { |
| 781 | $contriCol = "contribution_type_id"; |
| 782 | } |
| 783 | elseif ($fromTable == 'civicrm_contribution') { |
| 784 | $contriCol = $fromCol; |
| 785 | } |
| 786 | elseif ($fromTable == 'civicrm_address') { |
| 787 | $contriCol = "contact_id"; |
| 788 | } |
| 789 | elseif ($fromTable == 'civicrm_financial_type') { |
| 790 | $contriCol = 'financial_type_id'; |
| 791 | $subSelect1 = 'contribution1.contact_id,'; |
| 792 | $subSelect2 = 'contribution2.contact_id,'; |
| 793 | $create = 'contact_id int unsigned,'; |
| 794 | } |
| 795 | |
| 796 | $subWhere = $this->whereContribution(); |
| 797 | $subContributionQuery1 = " |
| 798 | SELECT {$subSelect1} contribution1.{$contriCol}, |
| 799 | sum( contribution1.total_amount ) AS total_amount_sum, |
| 800 | count( * ) AS total_amount_count |
| 801 | FROM civicrm_contribution contribution1 |
| 802 | {$subWhere} |
| 803 | GROUP BY contribution1.{$contriCol}"; |
| 804 | |
| 805 | $subWhere = $this->whereContribution('contribution2'); |
| 806 | $subContributionQuery2 = " |
| 807 | SELECT {$subSelect2} contribution2.{$contriCol}, |
| 808 | sum( contribution2.total_amount ) AS total_amount_sum, |
| 809 | count( * ) AS total_amount_count, |
| 810 | currency |
| 811 | FROM civicrm_contribution contribution2 |
| 812 | {$subWhere} |
| 813 | GROUP BY contribution2.{$contriCol}"; |
| 814 | |
| 815 | $sql = " |
| 816 | CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat1 ( |
| 817 | {$create} |
| 818 | {$contriCol} int unsigned, |
| 819 | total_amount_sum int, |
| 820 | total_amount_count int |
| 821 | ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"; |
| 822 | CRM_Core_DAO::executeQuery($sql); |
| 823 | $sql = "INSERT INTO civicrm_temp_civireport_repeat1 {$subContributionQuery1}"; |
| 824 | CRM_Core_DAO::executeQuery($sql); |
| 825 | |
| 826 | $sql = " |
| 827 | CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat2 ( |
| 828 | {$create} |
| 829 | {$contriCol} int unsigned, |
| 830 | total_amount_sum int, |
| 831 | total_amount_count int, |
| 832 | currency varchar(3) |
| 833 | ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"; |
| 834 | CRM_Core_DAO::executeQuery($sql); |
| 835 | $sql = "INSERT INTO civicrm_temp_civireport_repeat2 {$subContributionQuery2}"; |
| 836 | CRM_Core_DAO::executeQuery($sql); |
| 837 | |
| 838 | $this->select(); |
| 839 | $this->from(); |
| 840 | $this->where(); |
| 841 | $this->groupBy(); |
| 842 | $this->limit(); |
| 843 | |
| 844 | $count = 0; |
| 845 | $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_limit}"; |
| 846 | $dao = CRM_Core_DAO::executeQuery($sql); |
| 847 | $rows = array(); |
| 848 | while ($dao->fetch()) { |
| 849 | foreach ($this->_columnHeaders as $key => $value) { |
| 850 | $rows[$count][$key] = $dao->$key; |
| 851 | } |
| 852 | $count++; |
| 853 | } |
| 854 | |
| 855 | // FIXME: calculate % using query |
| 856 | foreach ($rows as $uid => $row) { |
| 857 | if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) { |
| 858 | $rows[$uid]['change'] = number_format((($row['contribution2_total_amount_sum'] - |
| 859 | $row['contribution1_total_amount_sum'] |
| 860 | ) * 100) / |
| 861 | ($row['contribution1_total_amount_sum']), 2 |
| 862 | ); |
| 863 | } |
| 864 | elseif ($row['contribution1_total_amount_sum']) { |
| 865 | $rows[$uid]['change'] = ts('Skipped Donation'); |
| 866 | } |
| 867 | elseif ($row['contribution2_total_amount_sum']) { |
| 868 | $rows[$uid]['change'] = ts('New Donor'); |
| 869 | } |
| 870 | if ($row['contribution1_total_amount_count']) { |
| 871 | $rows[$uid]['contribution1_total_amount_sum'] = $row['contribution1_total_amount_sum'] . " ({$row['contribution1_total_amount_count']})"; |
| 872 | } |
| 873 | if ($row['contribution2_total_amount_count']) { |
| 874 | $rows[$uid]['contribution2_total_amount_sum'] = $row['contribution2_total_amount_sum'] . " ({$row['contribution2_total_amount_count']})"; |
| 875 | } |
| 876 | } |
| 877 | $this->_columnHeaders['change'] = array( |
| 878 | 'title' => '% Change', |
| 879 | 'type' => CRM_Utils_Type::T_INT, |
| 880 | ); |
| 881 | |
| 882 | // hack to fix title |
| 883 | list($from1, $to1) = $this->getFromTo(CRM_Utils_Array::value("receive_date1_relative", $this->_params), |
| 884 | CRM_Utils_Array::value("receive_date1_from", $this->_params), |
| 885 | CRM_Utils_Array::value("receive_date1_to", $this->_params) |
| 886 | ); |
| 887 | $from1 = CRM_Utils_Date::customFormat($from1, NULL, array('d')); |
| 888 | $to1 = CRM_Utils_Date::customFormat($to1, NULL, array('d')); |
| 889 | |
| 890 | list($from2, $to2) = $this->getFromTo(CRM_Utils_Array::value("receive_date2_relative", $this->_params), |
| 891 | CRM_Utils_Array::value("receive_date2_from", $this->_params), |
| 892 | CRM_Utils_Array::value("receive_date2_to", $this->_params) |
| 893 | ); |
| 894 | $from2 = CRM_Utils_Date::customFormat($from2, NULL, array('d')); |
| 895 | $to2 = CRM_Utils_Date::customFormat($to2, NULL, array('d')); |
| 896 | |
| 897 | $this->_columnHeaders['contribution1_total_amount_sum']['title'] = "$from1 -<br/> $to1"; |
| 898 | $this->_columnHeaders['contribution2_total_amount_sum']['title'] = "$from2 -<br/> $to2"; |
| 899 | unset($this->_columnHeaders['contribution1_total_amount_count'], |
| 900 | $this->_columnHeaders['contribution2_total_amount_count'] |
| 901 | ); |
| 902 | |
| 903 | $this->formatDisplay($rows); |
| 904 | |
| 905 | // assign variables to templates |
| 906 | $this->doTemplateAssignment($rows); |
| 907 | |
| 908 | $this->endPostProcess($rows); |
| 909 | } |
| 910 | |
| 911 | /** |
| 912 | * @param $rows |
| 913 | */ |
| 914 | function alterDisplay(&$rows) { |
| 915 | // custom code to alter rows |
| 916 | list($from1, $to1) = $this->getFromTo(CRM_Utils_Array::value("receive_date1_relative", $this->_params), |
| 917 | CRM_Utils_Array::value("receive_date1_from", $this->_params), |
| 918 | CRM_Utils_Array::value("receive_date1_to", $this->_params) |
| 919 | ); |
| 920 | list($from2, $to2) = $this->getFromTo(CRM_Utils_Array::value("receive_date2_relative", $this->_params), |
| 921 | CRM_Utils_Array::value("receive_date2_from", $this->_params), |
| 922 | CRM_Utils_Array::value("receive_date2_to", $this->_params) |
| 923 | ); |
| 924 | |
| 925 | $dateUrl = ""; |
| 926 | if ($from1) { |
| 927 | $dateUrl .= "receive_date1_from={$from1}&"; |
| 928 | } |
| 929 | if ($to1) { |
| 930 | $dateUrl .= "receive_date1_to={$to1}&"; |
| 931 | } |
| 932 | if ($from2) { |
| 933 | $dateUrl .= "receive_date2_from={$from2}&"; |
| 934 | } |
| 935 | if ($to2) { |
| 936 | $dateUrl .= "receive_date2_to={$to2}&"; |
| 937 | } |
| 938 | |
| 939 | foreach ($rows as $rowNum => $row) { |
| 940 | // handle country |
| 941 | if (array_key_exists('address_civireport_country_id', $row)) { |
| 942 | if ($value = $row['address_civireport_country_id']) { |
| 943 | $rows[$rowNum]['address_civireport_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE); |
| 944 | |
| 945 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', |
| 946 | "reset=1&force=1&" . |
| 947 | "country_id_op=in&country_id_value={$value}&" . |
| 948 | "$dateUrl", |
| 949 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport |
| 950 | ); |
| 951 | |
| 952 | $rows[$rowNum]['address_civireport_country_id_link'] = $url; |
| 953 | $rows[$rowNum]['address_civireport_country_id_hover'] = ts("View contributions for this Country."); |
| 954 | } |
| 955 | } |
| 956 | |
| 957 | // handle state province |
| 958 | if (array_key_exists('address_civireport_state_province_id', $row)) { |
| 959 | if ($value = $row['address_civireport_state_province_id']) { |
| 960 | $rows[$rowNum]['address_civireport_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE); |
| 961 | |
| 962 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', |
| 963 | "reset=1&force=1&" . |
| 964 | "state_province_id_op=in&state_province_id_value={$value}&" . |
| 965 | "$dateUrl", |
| 966 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport |
| 967 | ); |
| 968 | |
| 969 | $rows[$rowNum]['address_civireport_state_province_id_link'] = $url; |
| 970 | $rows[$rowNum]['address_civireport_state_province_id_hover'] = ts("View repeatDetails for this state."); |
| 971 | } |
| 972 | } |
| 973 | |
| 974 | // convert display name to links |
| 975 | if (array_key_exists('contact_civireport_sort_name', $row) && |
| 976 | array_key_exists('contact_civireport_id', $row) |
| 977 | ) { |
| 978 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', |
| 979 | 'reset=1&force=1&id_op=eq&id_value=' . $row['contact_civireport_id'], |
| 980 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport |
| 981 | ); |
| 982 | |
| 983 | $rows[$rowNum]['contact_civireport_sort_name_link'] = $url; |
| 984 | $rows[$rowNum]['contact_civireport_sort_name_hover'] = ts("View Contribution details for this contact"); |
| 985 | } |
| 986 | } |
| 987 | // foreach ends |
| 988 | } |
| 989 | } |
| 990 | |