[civicrm-core.git] / CRM / Report / Form / Contribute / Sybunt.php

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.6                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2014                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2014
 * $Id$
 *
 */
class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form {

  protected $_charts = array(
    '' => 'Tabular',
    'barChart' => 'Bar Chart',
    'pieChart' => 'Pie Chart',
  );
  public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');

  /**
   *
   */
  public function __construct() {
    $yearsInPast = 10;
    $yearsInFuture = 1;
    $date = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, $yearsInFuture);
    $count = $date['maxYear'];
    while ($date['minYear'] <= $count) {
      $optionYear[$date['minYear']] = $date['minYear'];
      $date['minYear']++;
    }

    // Check if CiviCampaign is a) enabled and b) has active campaigns
    $config = CRM_Core_Config::singleton();
    $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
    if ($campaignEnabled) {
      $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
      $this->activeCampaigns = $getCampaigns['campaigns'];
      asort($this->activeCampaigns);
    }

    $this->_columns = array(
        'civicrm_contact' => array(
          'dao' => 'CRM_Contact_DAO_Contact',
          'grouping' => 'contact-field',
          'fields' => array(
            'sort_name' => array(
              'title' => ts('Donor Name'),
              'required' => TRUE,
            ),
            'first_name' => array(
              'title' => ts('First Name'),
            ),
            'last_name' => array(
              'title' => ts('Last Name'),
            ),
            'contact_type' => array(
              'title' => ts('Contact Type'),
            ),
            'contact_sub_type' => array(
              'title' => ts('Contact Subtype'),
            ),
          ),
          'filters' => array(
            'sort_name' => array(
              'title' => ts('Donor Name'),
              'operator' => 'like',
            ),
          ),
        ),
        'civicrm_email' => array(
          'dao' => 'CRM_Core_DAO_Email',
          'grouping' => 'contact-field',
          'fields' => array(
            'email' => array(
              'title' => ts('Email'),
              'default' => TRUE,
            ),
          ),
        ),
        'civicrm_phone' => array(
          'dao' => 'CRM_Core_DAO_Phone',
          'grouping' => 'contact-field',
          'fields' => array(
            'phone' => array(
              'title' => ts('Phone'),
              'default' => TRUE,
            ),
          ),
        ),
      )
    + $this->addAddressFields()
    + array(
        'civicrm_contribution' => array(
          'dao' => 'CRM_Contribute_DAO_Contribution',
          'fields' => array(
            'contact_id' => array(
              'title' => ts('contactId'),
              'no_display' => TRUE,
              'required' => TRUE,
              'no_repeat' => TRUE,
            ),
            'total_amount' => array(
              'title' => ts('Total Amount'),
              'no_display' => TRUE,
              'required' => TRUE,
              'no_repeat' => TRUE,
            ),
            'receive_date' => array(
              'title' => ts('Year'),
              'no_display' => TRUE,
              'required' => TRUE,
              'no_repeat' => TRUE,
            ),
          ),
          'filters' => array(
            'yid' => array(
              'name' => 'receive_date',
              'title' => ts('This Year'),
              'operatorType' => CRM_Report_Form::OP_SELECT,
              'options' => $optionYear,
              'default' => date('Y'),
            ),
            'financial_type_id' => array(
              'title' => ts('Financial Type'),
              'operatorType' => CRM_Report_Form::OP_MULTISELECT,
              'options' => CRM_Contribute_PseudoConstant::financialType(),
            ),
            'contribution_status_id' => array(
              'title' => ts('Contribution Status'),
              'operatorType' => CRM_Report_Form::OP_MULTISELECT,
              'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
              'default' => array('1'),
            ),
          ),
        ),
      );

    // If we have a campaign, build out the relevant elements
    if ($campaignEnabled && !empty($this->activeCampaigns)) {
      $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
        'title' => ts('Campaign'),
        'default' => 'false',
      );
      $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
        'title' => ts('Campaign'),
        'operatorType' => CRM_Report_Form::OP_MULTISELECT,
        'options' => $this->activeCampaigns,
      );
    }

    $this->_groupFilter = TRUE;
    $this->_tagFilter = TRUE;
    parent::__construct();
  }

  public function preProcess() {
    parent::preProcess();
  }

  public function select() {
    $select = array();
    $this->_columnHeaders = array();
    $current_year = $this->_params['yid_value'];
    $previous_year = $current_year - 1;
    $previous_pyear = $current_year - 2;
    $previous_ppyear = $current_year - 3;
    $upTo_year = $current_year - 4;

    foreach ($this->_columns as $tableName => $table) {
      if (array_key_exists('fields', $table)) {
        foreach ($table['fields'] as $fieldName => $field) {

          if (!empty($field['required']) ||
            !empty($this->_params['fields'][$fieldName])
          ) {
            if ($fieldName == 'total_amount') {
              $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}";

              $this->_columnHeaders["civicrm_upto_{$upTo_year}"]['type'] = $field['type'];
              $this->_columnHeaders["civicrm_upto_{$upTo_year}"]['title'] = "Up To $upTo_year";

              $this->_columnHeaders["{$previous_ppyear}"]['type'] = $field['type'];
              $this->_columnHeaders["{$previous_ppyear}"]['title'] = $previous_ppyear;

              $this->_columnHeaders["{$previous_pyear}"]['type'] = $field['type'];
              $this->_columnHeaders["{$previous_pyear}"]['title'] = $previous_pyear;

              $this->_columnHeaders["{$previous_year}"]['type'] = $field['type'];
              $this->_columnHeaders["{$previous_year}"]['title'] = $previous_year;

              $this->_columnHeaders["civicrm_life_time_total"]['type'] = $field['type'];
              $this->_columnHeaders["civicrm_life_time_total"]['title'] = 'LifeTime';;
            }
            elseif ($fieldName == 'receive_date') {
              $select[] = self::fiscalYearOffset($field['dbAlias']) .
                " as {$tableName}_{$fieldName}";
            }
            else {
              $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
            }
            if (!empty($field['no_display'])) {
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = TRUE;
            }
          }
        }
      }
    }

    $this->_select = "SELECT " . implode(', ', $select) . " ";
  }

  public function from() {

    $this->_from = "
        FROM  civicrm_contribution  {$this->_aliases['civicrm_contribution']}
              INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id
             {$this->_aclFrom}";

    if ($this->isTableSelected('civicrm_email')) {
      $this->_from .= "
              LEFT  JOIN civicrm_email  {$this->_aliases['civicrm_email']}
                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id
                     AND {$this->_aliases['civicrm_email']}.is_primary = 1";
    }
    if ($this->isTableSelected('civicrm_phone')) {
      $this->_from .= "
              LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
                         {$this->_aliases['civicrm_phone']}.is_primary = 1";
    }
    $this->addAddressFromClause();
  }

  public function where() {
    $this->_statusClause = "";
    $clauses = array($this->_aliases['civicrm_contribution'] . '.is_test = 0');
    foreach ($this->_columns as $tableName => $table) {
      if (array_key_exists('filters', $table)) {
        foreach ($table['filters'] as $fieldName => $field) {
          $clause = NULL;
          if ($fieldName == 'yid') {
            $clause = "contribution_civireport.contact_id NOT IN
(SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
 WHERE  cont.id = contri.contact_id AND " .
              self::fiscalYearOffset('contri.receive_date') .
              " = {$this->_params['yid_value']} AND contri.is_test = 0 )";
          }
          elseif (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE
          ) {
            $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
            $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
            $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);

            if ($relative || $from || $to) {
              $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
            }
          }
          else {
            $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
            if ($op) {
              $clause = $this->whereClause($field,
                $op,
                CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
                CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
                CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
              );
              if (($fieldName == 'contribution_status_id' ||
                  $fieldName == 'financial_type_id') && !empty($clause)
              ) {
                $this->_statusClause .= " AND " . $clause;
              }
            }
          }

          if (!empty($clause)) {
            $clauses[] = $clause;
          }
        }
      }
    }

    $this->_where = "WHERE " . implode(' AND ', $clauses);

    if ($this->_aclWhere) {
      $this->_where .= " AND {$this->_aclWhere} ";
    }
  }

  public function groupBy() {
    $this->assign('chartSupported', TRUE);
    $this->_groupBy =
      "Group BY {$this->_aliases['civicrm_contribution']}.contact_id, " .
      self::fiscalYearOffset($this->_aliases['civicrm_contribution'] .
        '.receive_date') . " WITH ROLLUP ";
  }

  /**
   * @param $rows
   *
   * @return array
   */
  public function statistics(&$rows) {
    $statistics = parent::statistics($rows);

    if (!empty($rows)) {
      $select = "
                   SELECT
                        SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount ";

      $sql = "{$select} {$this->_from} {$this->_where}";
      $dao = CRM_Core_DAO::executeQuery($sql);
      if ($dao->fetch()) {
        $statistics['counts']['amount'] = array(
          'value' => $dao->amount,
          'title' => 'Total LifeTime',
          'type' => CRM_Utils_Type::T_MONEY,
        );
      }
    }
    return $statistics;
  }

  public function postProcess() {
    // get ready with post process params
    $this->beginPostProcess();

    $this->buildACLClause($this->_aliases['civicrm_contact']);
    $this->select();
    $this->from();
    $this->where();
    $this->groupBy();

    $rows = $contactIds = array();
    if (empty($this->_params['charts'])) {
      $this->limit();
      $getContacts = "SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_limit}";

      $dao = CRM_Core_DAO::executeQuery($getContacts);

      while ($dao->fetch()) {
        $contactIds[] = $dao->cid;
      }
      $dao->free();
      $this->setPager();
    }

    if (!empty($contactIds) || !empty($this->_params['charts'])) {
      if (!empty($this->_params['charts'])) {
        $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy}";
      }
      else {
        $sql =
          "{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" .
          implode(',', $contactIds) .
          ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause} {$this->_groupBy} ";
      }

      $current_year = $this->_params['yid_value'];
      $previous_year = $current_year - 1;
      $previous_pyear = $current_year - 2;
      $previous_ppyear = $current_year - 3;
      $upTo_year = $current_year - 4;

      $rows = $row = array();
      $dao = CRM_Core_DAO::executeQuery($sql);
      $contributionSum = 0;
      $yearcal = array();
      while ($dao->fetch()) {
        if (!$dao->civicrm_contribution_contact_id) {
          continue;
        }
        $row = array();
        foreach ($this->_columnHeaders as $key => $value) {
          if (property_exists($dao, $key)) {
            $rows[$dao->civicrm_contribution_contact_id][$key] = $dao->$key;
          }
        }
        if ($dao->civicrm_contribution_receive_date) {
          if ($dao->civicrm_contribution_receive_date > $upTo_year) {
            $contributionSum += $dao->civicrm_contribution_total_amount;
            $rows[$dao->civicrm_contribution_contact_id][$dao->civicrm_contribution_receive_date] = $dao->civicrm_contribution_total_amount;
          }
        }
        else {
          $rows[$dao->civicrm_contribution_contact_id]['civicrm_life_time_total'] = $dao->civicrm_contribution_total_amount;
          if (($dao->civicrm_contribution_total_amount - $contributionSum) > 0
          ) {
            $rows[$dao->civicrm_contribution_contact_id]["civicrm_upto_{$upTo_year}"] =
              $dao->civicrm_contribution_total_amount - $contributionSum;
          }
          $contributionSum = 0;
        }
      }
      $dao->free();
    }
    // format result set.
    $this->formatDisplay($rows, FALSE);

    // assign variables to templates
    $this->doTemplateAssignment($rows);

    // do print / pdf / instance stuff if needed
    $this->endPostProcess($rows);
  }

  /**
   * @param $rows
   */
  public function buildChart(&$rows) {
    $graphRows = array();
    $count = 0;
    $current_year = $this->_params['yid_value'];
    $previous_year = $current_year - 1;
    $previous_two_year = $current_year - 2;
    $previous_three_year = $current_year - 3;
    $upto = $current_year - 4;

    $interval[$previous_year] = $previous_year;
    $interval[$previous_two_year] = $previous_two_year;
    $interval[$previous_three_year] = $previous_three_year;
    $interval["upto_{$upto}"] = "Up To {$upto}";

    foreach ($rows as $key => $row) {
      $display["upto_{$upto}"] =
        CRM_Utils_Array::value("upto_{$upto}", $display) +
        CRM_Utils_Array::value("civicrm_upto_{$upto}", $row);
      $display[$previous_year] =
        CRM_Utils_Array::value($previous_year, $display) +
        CRM_Utils_Array::value($previous_year, $row);
      $display[$previous_two_year] =
        CRM_Utils_Array::value($previous_two_year, $display) +
        CRM_Utils_Array::value($previous_two_year, $row);
      $display[$previous_three_year] =
        CRM_Utils_Array::value($previous_three_year, $display) +
        CRM_Utils_Array::value($previous_three_year, $row);
    }

    $graphRows['value'] = $display;
    $config = CRM_Core_Config::Singleton();
    $chartInfo = array(
      'legend' => 'Sybunt Report',
      'xname' => 'Year',
      'yname' => "Amount ({$config->defaultCurrency})",
    );
    if ($this->_params['charts']) {
      // build the chart.
      CRM_Utils_OpenFlashChart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo);
      $this->assign('chartType', $this->_params['charts']);
    }
  }

  /**
   * @param $rows
   */
  public function alterDisplay(&$rows) {
    // custom code to alter rows
    $entryFound = FALSE;

    foreach ($rows as $rowNum => $row) {
      //Convert Display name into link
      if (array_key_exists('civicrm_contact_sort_name', $row) &&
        array_key_exists('civicrm_contribution_contact_id', $row)
      ) {
        $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
          'reset=1&force=1&id_op=eq&id_value=' .
          $row['civicrm_contribution_contact_id'],
          $this->_absoluteUrl, $this->_id, $this->_drilldownReport
        );
        $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
        $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
        $entryFound = TRUE;
      }

      // convert campaign_id to campaign title
      if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
        if ($value = $row['civicrm_contribution_campaign_id']) {
          $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
          $entryFound = TRUE;
        }
      }

      $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s)') ? TRUE : $entryFound;

      // skip looking further in rows, if first row itself doesn't
      // have the column we need
      if (!$entryFound) {
        break;
      }
    }
  }

  // Override "This Year" $op options
  /**
   * @param string $type
   * @param null $fieldName
   *
   * @return array
   */
  public function getOperationPair($type = "string", $fieldName = NULL) {
    if ($fieldName == 'yid') {
      return array(
        'calendar' => ts('Is Calendar Year'),
        'fiscal' => ts('Fiscal Year Starting')
      );
    }
    return parent::getOperationPair($type, $fieldName);
  }
}
Commit	Line	Data
6a488035	1	<?php
6a488035 TO	2	/*
6a488035 TO	3	+--------------------------------------------------------------------+
39de6fd5	4	\| CiviCRM version 4.6 \|
6a488035	5	+--------------------------------------------------------------------+
06b69b18	6	\| Copyright CiviCRM LLC (c) 2004-2014 \|
6a488035 TO	7	+--------------------------------------------------------------------+
	8	\| This file is a part of CiviCRM. \|
	9	\| \|
	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. \|
	13	\| \|
	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. \|
	18	\| \|
	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	+--------------------------------------------------------------------+
	26	*/
	27
	28	/**
	29	*
	30	* @package CRM
06b69b18	31	* @copyright CiviCRM LLC (c) 2004-2014
6a488035 TO	32	* $Id$
	33	*
	34	*/
	35	class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form {
	36
	37	protected $_charts = array(
	38	'' => 'Tabular',
	39	'barChart' => 'Bar Chart',
	40	'pieChart' => 'Pie Chart',
	41	);
	42	public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
	43
74cf4551 EM	44	/**
	45	*
	46	*/
00be9182	47	public function __construct() {
9d72cede	48	$yearsInPast = 10;
6a488035	49	$yearsInFuture = 1;
9d72cede EM	50	$date = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, $yearsInFuture);
9d72cede EM	51	$count = $date['maxYear'];
6a488035 TO	52	while ($date['minYear'] <= $count) {
	53	$optionYear[$date['minYear']] = $date['minYear'];
	54	$date['minYear']++;
	55	}
	56
d62fab33 RN	57	// Check if CiviCampaign is a) enabled and b) has active campaigns
	58	$config = CRM_Core_Config::singleton();
	59	$campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
	60	if ($campaignEnabled) {
	61	$getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
	62	$this->activeCampaigns = $getCampaigns['campaigns'];
	63	asort($this->activeCampaigns);
f813f78e	64	}
d62fab33	65
6a488035	66	$this->_columns = array(
9d72cede EM	67	'civicrm_contact' => array(
	68	'dao' => 'CRM_Contact_DAO_Contact',
	69	'grouping' => 'contact-field',
	70	'fields' => array(
	71	'sort_name' => array(
	72	'title' => ts('Donor Name'),
	73	'required' => TRUE,
	74	),
	75	'first_name' => array(
	76	'title' => ts('First Name'),
	77	),
	78	'last_name' => array(
	79	'title' => ts('Last Name'),
	80	),
	81	'contact_type' => array(
	82	'title' => ts('Contact Type'),
	83	),
	84	'contact_sub_type' => array(
	85	'title' => ts('Contact Subtype'),
	86	),
6a488035	87	),
9d72cede EM	88	'filters' => array(
	89	'sort_name' => array(
	90	'title' => ts('Donor Name'),
	91	'operator' => 'like',
	92	),
6a488035 TO	93	),
6a488035 TO	94	),
9d72cede EM	95	'civicrm_email' => array(
	96	'dao' => 'CRM_Core_DAO_Email',
	97	'grouping' => 'contact-field',
	98	'fields' => array(
	99	'email' => array(
	100	'title' => ts('Email'),
	101	'default' => TRUE,
	102	),
6a488035 TO	103	),
6a488035 TO	104	),
9d72cede EM	105	'civicrm_phone' => array(
	106	'dao' => 'CRM_Core_DAO_Phone',
	107	'grouping' => 'contact-field',
	108	'fields' => array(
	109	'phone' => array(
	110	'title' => ts('Phone'),
	111	'default' => TRUE,
	112	),
6a488035 TO	113	),
6a488035 TO	114	),
9d72cede	115	)
84178120 TO	116	+ $this->addAddressFields()
84178120 TO	117	+ array(
9d72cede EM	118	'civicrm_contribution' => array(
	119	'dao' => 'CRM_Contribute_DAO_Contribution',
	120	'fields' => array(
	121	'contact_id' => array(
	122	'title' => ts('contactId'),
	123	'no_display' => TRUE,
	124	'required' => TRUE,
	125	'no_repeat' => TRUE,
	126	),
	127	'total_amount' => array(
	128	'title' => ts('Total Amount'),
	129	'no_display' => TRUE,
	130	'required' => TRUE,
	131	'no_repeat' => TRUE,
	132	),
	133	'receive_date' => array(
	134	'title' => ts('Year'),
	135	'no_display' => TRUE,
	136	'required' => TRUE,
	137	'no_repeat' => TRUE,
	138	),
6a488035	139	),
9d72cede EM	140	'filters' => array(
	141	'yid' => array(
	142	'name' => 'receive_date',
	143	'title' => ts('This Year'),
	144	'operatorType' => CRM_Report_Form::OP_SELECT,
	145	'options' => $optionYear,
	146	'default' => date('Y'),
	147	),
	148	'financial_type_id' => array(
	149	'title' => ts('Financial Type'),
	150	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	151	'options' => CRM_Contribute_PseudoConstant::financialType(),
	152	),
	153	'contribution_status_id' => array(
	154	'title' => ts('Contribution Status'),
	155	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	156	'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
	157	'default' => array('1'),
	158	),
6a488035 TO	159	),
6a488035 TO	160	),
9d72cede	161	);
6a488035	162
d62fab33 RN	163	// If we have a campaign, build out the relevant elements
	164	if ($campaignEnabled && !empty($this->activeCampaigns)) {
	165	$this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
	166	'title' => ts('Campaign'),
	167	'default' => 'false',
	168	);
9d72cede EM	169	$this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
9d72cede EM	170	'title' => ts('Campaign'),
d62fab33 RN	171	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	172	'options' => $this->activeCampaigns,
	173	);
	174	}
	175
16e2e80c	176	$this->_groupFilter = TRUE;
6a488035 TO	177	$this->_tagFilter = TRUE;
	178	parent::__construct();
	179	}
	180
00be9182	181	public function preProcess() {
6a488035 TO	182	parent::preProcess();
	183	}
	184
00be9182	185	public function select() {
6a488035 TO	186	$select = array();
	187	$this->_columnHeaders = array();
	188	$current_year = $this->_params['yid_value'];
	189	$previous_year = $current_year - 1;
	190	$previous_pyear = $current_year - 2;
	191	$previous_ppyear = $current_year - 3;
	192	$upTo_year = $current_year - 4;
	193
	194	foreach ($this->_columns as $tableName => $table) {
	195	if (array_key_exists('fields', $table)) {
	196	foreach ($table['fields'] as $fieldName => $field) {
	197
9d72cede EM	198	if (!empty($field['required']) \|\|
	199	!empty($this->_params['fields'][$fieldName])
	200	) {
6a488035 TO	201	if ($fieldName == 'total_amount') {
	202	$select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}";
	203
	204	$this->_columnHeaders["civicrm_upto_{$upTo_year}"]['type'] = $field['type'];
	205	$this->_columnHeaders["civicrm_upto_{$upTo_year}"]['title'] = "Up To $upTo_year";
	206
	207	$this->_columnHeaders["{$previous_ppyear}"]['type'] = $field['type'];
	208	$this->_columnHeaders["{$previous_ppyear}"]['title'] = $previous_ppyear;
	209
	210	$this->_columnHeaders["{$previous_pyear}"]['type'] = $field['type'];
	211	$this->_columnHeaders["{$previous_pyear}"]['title'] = $previous_pyear;
	212
	213	$this->_columnHeaders["{$previous_year}"]['type'] = $field['type'];
	214	$this->_columnHeaders["{$previous_year}"]['title'] = $previous_year;
	215
	216	$this->_columnHeaders["civicrm_life_time_total"]['type'] = $field['type'];
	217	$this->_columnHeaders["civicrm_life_time_total"]['title'] = 'LifeTime';;
	218	}
	219	elseif ($fieldName == 'receive_date') {
9d72cede EM	220	$select[] = self::fiscalYearOffset($field['dbAlias']) .
9d72cede EM	221	" as {$tableName}_{$fieldName}";
6a488035 TO	222	}
	223	else {
	224	$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
	225	$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
	226	$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
	227	}
a7488080	228	if (!empty($field['no_display'])) {
6a488035 TO	229	$this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = TRUE;
	230	}
	231	}
	232	}
	233	}
	234	}
	235
	236	$this->_select = "SELECT " . implode(', ', $select) . " ";
	237	}
	238
00be9182	239	public function from() {
6a488035	240
74cf4551	241	$this->_from = "
6a488035	242	FROM civicrm_contribution {$this->_aliases['civicrm_contribution']}
a27c90f2 NG	243	INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
	244	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id
	245	{$this->_aclFrom}";
	246
	247	if ($this->isTableSelected('civicrm_email')) {
	248	$this->_from .= "
	249	LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
	250	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id
	251	AND {$this->_aliases['civicrm_email']}.is_primary = 1";
	252	}
	253	if ($this->isTableSelected('civicrm_phone')) {
	254	$this->_from .= "
	255	LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
	256	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
	257	{$this->_aliases['civicrm_phone']}.is_primary = 1";
	258	}
72a9af52	259	$this->addAddressFromClause();
6a488035 TO	260	}
6a488035 TO	261
00be9182	262	public function where() {
6a488035 TO	263	$this->_statusClause = "";
	264	$clauses = array($this->_aliases['civicrm_contribution'] . '.is_test = 0');
	265	foreach ($this->_columns as $tableName => $table) {
	266	if (array_key_exists('filters', $table)) {
	267	foreach ($table['filters'] as $fieldName => $field) {
	268	$clause = NULL;
	269	if ($fieldName == 'yid') {
	270	$clause = "contribution_civireport.contact_id NOT IN
	271	(SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
9d72cede EM	272	WHERE cont.id = contri.contact_id AND " .
	273	self::fiscalYearOffset('contri.receive_date') .
	274	" = {$this->_params['yid_value']} AND contri.is_test = 0 )";
6a488035	275	}
84178120	276	elseif (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE
9d72cede	277	) {
6a488035	278	$relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
9d72cede EM	279	$from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
9d72cede EM	280	$to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
6a488035 TO	281
	282	if ($relative \|\| $from \|\| $to) {
	283	$clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
	284	}
	285	}
	286	else {
	287	$op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
	288	if ($op) {
	289	$clause = $this->whereClause($field,
	290	$op,
	291	CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
	292	CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
	293	CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
	294	);
9d72cede EM	295	if (($fieldName == 'contribution_status_id' \|\|
	296	$fieldName == 'financial_type_id') && !empty($clause)
	297	) {
6a488035 TO	298	$this->_statusClause .= " AND " . $clause;
	299	}
	300	}
	301	}
	302
	303	if (!empty($clause)) {
	304	$clauses[] = $clause;
	305	}
	306	}
	307	}
	308	}
	309
	310	$this->_where = "WHERE " . implode(' AND ', $clauses);
	311
	312	if ($this->_aclWhere) {
	313	$this->_where .= " AND {$this->_aclWhere} ";
	314	}
	315	}
	316
00be9182	317	public function groupBy() {
6a488035	318	$this->assign('chartSupported', TRUE);
9d72cede EM	319	$this->_groupBy =
	320	"Group BY {$this->_aliases['civicrm_contribution']}.contact_id, " .
	321	self::fiscalYearOffset($this->_aliases['civicrm_contribution'] .
	322	'.receive_date') . " WITH ROLLUP ";
6a488035 TO	323	}
6a488035 TO	324
74cf4551 EM	325	/**
	326	* @param $rows
	327	*
	328	* @return array
	329	*/
00be9182	330	public function statistics(&$rows) {
6a488035 TO	331	$statistics = parent::statistics($rows);
	332
	333	if (!empty($rows)) {
	334	$select = "
39eb89f4	335	SELECT
6a488035 TO	336	SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount ";
	337
	338	$sql = "{$select} {$this->_from} {$this->_where}";
	339	$dao = CRM_Core_DAO::executeQuery($sql);
	340	if ($dao->fetch()) {
	341	$statistics['counts']['amount'] = array(
	342	'value' => $dao->amount,
	343	'title' => 'Total LifeTime',
	344	'type' => CRM_Utils_Type::T_MONEY,
	345	);
	346	}
	347	}
	348	return $statistics;
	349	}
	350
00be9182	351	public function postProcess() {
6a488035 TO	352	// get ready with post process params
	353	$this->beginPostProcess();
	354
	355	$this->buildACLClause($this->_aliases['civicrm_contact']);
	356	$this->select();
	357	$this->from();
	358	$this->where();
	359	$this->groupBy();
	360
	361	$rows = $contactIds = array();
a7488080	362	if (empty($this->_params['charts'])) {
6a488035 TO	363	$this->limit();
	364	$getContacts = "SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_limit}";
	365
	366	$dao = CRM_Core_DAO::executeQuery($getContacts);
	367
	368	while ($dao->fetch()) {
	369	$contactIds[] = $dao->cid;
	370	}
	371	$dao->free();
	372	$this->setPager();
	373	}
	374
8cc574cf	375	if (!empty($contactIds) \|\| !empty($this->_params['charts'])) {
a7488080	376	if (!empty($this->_params['charts'])) {
6a488035 TO	377	$sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy}";
	378	}
	379	else {
9d72cede EM	380	$sql =
	381	"{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" .
	382	implode(',', $contactIds) .
	383	") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause} {$this->_groupBy} ";
6a488035 TO	384	}
6a488035 TO	385
9d72cede EM	386	$current_year = $this->_params['yid_value'];
	387	$previous_year = $current_year - 1;
	388	$previous_pyear = $current_year - 2;
6a488035	389	$previous_ppyear = $current_year - 3;
9d72cede	390	$upTo_year = $current_year - 4;
6a488035	391
9d72cede EM	392	$rows = $row = array();
9d72cede EM	393	$dao = CRM_Core_DAO::executeQuery($sql);
6a488035	394	$contributionSum = 0;
9d72cede	395	$yearcal = array();
6a488035 TO	396	while ($dao->fetch()) {
	397	if (!$dao->civicrm_contribution_contact_id) {
	398	continue;
	399	}
	400	$row = array();
	401	foreach ($this->_columnHeaders as $key => $value) {
	402	if (property_exists($dao, $key)) {
	403	$rows[$dao->civicrm_contribution_contact_id][$key] = $dao->$key;
	404	}
	405	}
	406	if ($dao->civicrm_contribution_receive_date) {
	407	if ($dao->civicrm_contribution_receive_date > $upTo_year) {
	408	$contributionSum += $dao->civicrm_contribution_total_amount;
	409	$rows[$dao->civicrm_contribution_contact_id][$dao->civicrm_contribution_receive_date] = $dao->civicrm_contribution_total_amount;
	410	}
	411	}
	412	else {
	413	$rows[$dao->civicrm_contribution_contact_id]['civicrm_life_time_total'] = $dao->civicrm_contribution_total_amount;
9d72cede EM	414	if (($dao->civicrm_contribution_total_amount - $contributionSum) > 0
	415	) {
	416	$rows[$dao->civicrm_contribution_contact_id]["civicrm_upto_{$upTo_year}"] =
	417	$dao->civicrm_contribution_total_amount - $contributionSum;
6a488035 TO	418	}
	419	$contributionSum = 0;
	420	}
	421	}
	422	$dao->free();
	423	}
	424	// format result set.
	425	$this->formatDisplay($rows, FALSE);
	426
	427	// assign variables to templates
	428	$this->doTemplateAssignment($rows);
	429
	430	// do print / pdf / instance stuff if needed
	431	$this->endPostProcess($rows);
	432	}
	433
74cf4551 EM	434	/**
	435	* @param $rows
	436	*/
00be9182	437	public function buildChart(&$rows) {
9d72cede EM	438	$graphRows = array();
	439	$count = 0;
	440	$current_year = $this->_params['yid_value'];
	441	$previous_year = $current_year - 1;
	442	$previous_two_year = $current_year - 2;
6a488035	443	$previous_three_year = $current_year - 3;
9d72cede	444	$upto = $current_year - 4;
6a488035 TO	445
	446	$interval[$previous_year] = $previous_year;
	447	$interval[$previous_two_year] = $previous_two_year;
	448	$interval[$previous_three_year] = $previous_three_year;
	449	$interval["upto_{$upto}"] = "Up To {$upto}";
	450
	451	foreach ($rows as $key => $row) {
9d72cede EM	452	$display["upto_{$upto}"] =
	453	CRM_Utils_Array::value("upto_{$upto}", $display) +
	454	CRM_Utils_Array::value("civicrm_upto_{$upto}", $row);
	455	$display[$previous_year] =
	456	CRM_Utils_Array::value($previous_year, $display) +
	457	CRM_Utils_Array::value($previous_year, $row);
	458	$display[$previous_two_year] =
	459	CRM_Utils_Array::value($previous_two_year, $display) +
	460	CRM_Utils_Array::value($previous_two_year, $row);
	461	$display[$previous_three_year] =
	462	CRM_Utils_Array::value($previous_three_year, $display) +
	463	CRM_Utils_Array::value($previous_three_year, $row);
6a488035 TO	464	}
	465
	466	$graphRows['value'] = $display;
9d72cede EM	467	$config = CRM_Core_Config::Singleton();
9d72cede EM	468	$chartInfo = array(
6a488035 TO	469	'legend' => 'Sybunt Report',
	470	'xname' => 'Year',
	471	'yname' => "Amount ({$config->defaultCurrency})",
	472	);
	473	if ($this->_params['charts']) {
	474	// build the chart.
	475	CRM_Utils_OpenFlashChart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo);
	476	$this->assign('chartType', $this->_params['charts']);
	477	}
	478	}
	479
74cf4551 EM	480	/**
	481	* @param $rows
	482	*/
00be9182	483	public function alterDisplay(&$rows) {
72a9af52 NG	484	// custom code to alter rows
72a9af52 NG	485	$entryFound = FALSE;
6a488035 TO	486
	487	foreach ($rows as $rowNum => $row) {
	488	//Convert Display name into link
	489	if (array_key_exists('civicrm_contact_sort_name', $row) &&
	490	array_key_exists('civicrm_contribution_contact_id', $row)
	491	) {
	492	$url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
9d72cede EM	493	'reset=1&force=1&id_op=eq&id_value=' .
9d72cede EM	494	$row['civicrm_contribution_contact_id'],
6a488035 TO	495	$this->_absoluteUrl, $this->_id, $this->_drilldownReport
	496	);
	497	$rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
	498	$rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
72a9af52	499	$entryFound = TRUE;
6a488035	500	}
f813f78e	501
d62fab33 RN	502	// convert campaign_id to campaign title
	503	if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
	504	if ($value = $row['civicrm_contribution_campaign_id']) {
	505	$rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
	506	$entryFound = TRUE;
	507	}
	508	}
a27c90f2	509
72a9af52 NG	510	$entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s)') ? TRUE : $entryFound;
	511
	512	// skip looking further in rows, if first row itself doesn't
	513	// have the column we need
	514	if (!$entryFound) {
	515	break;
a27c90f2	516	}
6a488035 TO	517	}
	518	}
	519
	520	// Override "This Year" $op options
74cf4551 EM	521	/**
	522	* @param string $type
	523	* @param null $fieldName
	524	*
	525	* @return array
	526	*/
00be9182	527	public function getOperationPair($type = "string", $fieldName = NULL) {
6a488035	528	if ($fieldName == 'yid') {
9d72cede EM	529	return array(
	530	'calendar' => ts('Is Calendar Year'),
	531	'fiscal' => ts('Fiscal Year Starting')
	532	);
6a488035 TO	533	}
	534	return parent::getOperationPair($type, $fieldName);
	535	}
	536	}