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

<?php

/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.5                                                |
 +--------------------------------------------------------------------+
 | 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_ActivitySummary extends CRM_Report_Form {

  protected $_emailField = FALSE;
  protected $_phoneField = FALSE;

  /**
   *
   */
  /**
   *
   */
  function __construct() {
    $this->_columns = array(
      'civicrm_contact' => array(
        'dao' => 'CRM_Contact_DAO_Contact',
        'fields' => array(
          'id' => array(
            'required' => TRUE,
            'no_display' => TRUE,
          ),
          'sort_name' => array(
            'title' => ts('Contact Name'),
            'default' => TRUE,
            'no_repeat' => TRUE,
          ),
        ),
        'filters' => array(
          'sort_name' => array(
            'title' => ts('Contact Name'),
          ),
        ),
        'group_bys' => array(
          'sort_name' => array(
            'name' => 'id',
            'title' => ts('Contact'),
            'default' => TRUE,
          ),
        ),
        'order_bys' => array(
          'sort_name' => array(
            'title' => ts('Contact Name')
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_email' => array(
        'dao' => 'CRM_Core_DAO_Email',
        'fields' => array(
          'email' => array(
            'title' => 'Email',
            'default' => TRUE,
          ),
        ),
        'order_bys' => array(
          'email' => array(
            'title' => ts('Email')
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_phone' => array(
        'dao' => 'CRM_Core_DAO_Email',
        'fields' => array(
          'phone' => array(
            'title' => 'Phone'
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_activity' => array(
        'dao' => 'CRM_Activity_DAO_Activity',
        'fields' => array(
          'activity_type_id' => array(
            'title' => ts('Activity Type'),
            'default' => TRUE,
            'type' => CRM_Utils_Type::T_STRING,
          ),
          'duration' => array(
            'title' => 'Duration',
            'statistics' => array(
              'sum' => ts('Total Duration'),
            ),
          ),
          'id' => array(
            'title' => 'Total Activities',
            'required' => TRUE,
            'statistics' => array(
              'count' => ts('Activity Count'),
            ),
          ),
        ),
        'filters' => array(
          'activity_date_time' => array(
            'operatorType' => CRM_Report_Form::OP_DATE
          ),
          'activity_type_id' => array(
            'title' => ts('Activity Type'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
          ),
          'status_id' => array(
            'title' => ts('Activity Status'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Core_PseudoConstant::activityStatus(),
          ),
          'priority_id' => array(
            'title' => ts('Priority'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Core_PseudoConstant::get('CRM_Activity_DAO_Activity', 'priority_id'),
          ),
        ),
        'group_bys' => array(
          'activity_date_time' =>
            array(
              'title' => ts('Activity Date'),
              'frequency' => TRUE,
            ),
          'activity_type_id' => array(
            'title' => ts('Activity Type'),
            'default' => TRUE,
          ),
        ),
        'order_bys' => array(
          'activity_date_time' => array(
            'title' => ts('Activity Date')
          ),
          'activity_type_id' => array(
            'title' => ts('Activity Type')
          ),
        ),
        'grouping' => 'activity-fields',
        'alias' => 'activity',
      ),
    );

    parent::__construct();
  }

  function select() {
    $select = array();
    $this->_columnHeaders = array();
    foreach ($this->_columns as $tableName => $table) {
      if (array_key_exists('group_bys', $table)) {
        foreach ($table['group_bys'] as $fieldName => $field) {
          if (!empty($this->_params['group_bys'][$fieldName])) {
            if (isset($this->_params['group_bys_freq']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
              switch ($this->_params['group_bys_freq'][$fieldName]) {
                case 'YEARWEEK':
                  $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";

                  $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
                  $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
                  $field['title'] = 'Week';
                  break;

                case 'YEAR':
                  $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
                  $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
                  $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
                  $field['title'] = 'Year';
                  break;

                case 'MONTH':
                  $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
                  $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
                  $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
                  $field['title'] = 'Month';
                  break;

                case 'QUARTER':
                  $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
                  $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
                  $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
                  $field['title'] = 'Quarter';
                  break;
              }
              $this->_interval = $field['title'];
              $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
              $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
              $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];

              // just to make sure these values are transfered to rows.
              // since we need that for calculation purpose,
              // e.g making subtotals look nicer or graphs
              $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
              $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
            }
          }
        }
      }
      if (array_key_exists('fields', $table)) {
        foreach ($table['fields'] as $fieldName => $field) {
          if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
            if ($tableName == 'civicrm_email') {
              $this->_emailField = TRUE;
            }
            if ($tableName == 'civicrm_phone') {
              $this->_phoneField = TRUE;
            }
            if (!empty($field['statistics'])) {
              foreach ($field['statistics'] as $stat => $label) {
                switch (strtolower($stat)) {
                  case 'count':
                    $select[] = "COUNT(DISTINCT({$field['dbAlias']})) as {$tableName}_{$fieldName}_{$stat}";
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                    $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
                    break;

                  case 'sum':
                    $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                    $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
                    break;
                }
              }
            }
            elseif ($fieldName == 'activity_type_id') {
              if (empty($this->_params['group_bys']['activity_type_id'])) {
                $select[] = "GROUP_CONCAT(DISTINCT {$field['dbAlias']}  ORDER BY {$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'] = CRM_Utils_Array::value('title', $field);
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
            }
            else {
              $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
            }
          }
        }
      }
    }

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

  function from() {
    $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
    $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
    $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
    $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);

    $this->_from = "
        FROM civicrm_activity {$this->_aliases['civicrm_activity']}

             LEFT JOIN civicrm_activity_contact target_activity
                    ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id AND
                       target_activity.record_type_id = {$targetID}
             LEFT JOIN civicrm_activity_contact assignment_activity
                    ON {$this->_aliases['civicrm_activity']}.id = assignment_activity.activity_id AND
                       assignment_activity.record_type_id = {$assigneeID}
             LEFT JOIN civicrm_activity_contact source_activity
                    ON {$this->_aliases['civicrm_activity']}.id = source_activity.activity_id AND
                       source_activity.record_type_id = {$sourceID}
             LEFT JOIN civicrm_contact contact_civireport
                    ON target_activity.contact_id = contact_civireport.id
             LEFT JOIN civicrm_contact civicrm_contact_assignee
                    ON assignment_activity.contact_id = civicrm_contact_assignee.id
             LEFT JOIN civicrm_contact civicrm_contact_source
                    ON source_activity.contact_id = civicrm_contact_source.id
             {$this->_aclFrom}
             LEFT JOIN civicrm_option_value
                    ON ( {$this->_aliases['civicrm_activity']}.activity_type_id = civicrm_option_value.value )
             LEFT JOIN civicrm_option_group
                    ON civicrm_option_group.id = civicrm_option_value.option_group_id
             LEFT JOIN civicrm_case_activity
                    ON civicrm_case_activity.activity_id = {$this->_aliases['civicrm_activity']}.id
             LEFT JOIN civicrm_case
                    ON civicrm_case_activity.case_id = civicrm_case.id
             LEFT JOIN civicrm_case_contact
                    ON civicrm_case_contact.case_id = civicrm_case.id ";

    if ($this->_emailField) {
      $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->_phoneField) {
      $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 ";
    }
  }

  function where() {
    $this->_where = " WHERE civicrm_option_group.name = 'activity_type' AND
                                {$this->_aliases['civicrm_activity']}.is_test = 0 AND
                                {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
                                {$this->_aliases['civicrm_activity']}.is_current_revision = 1";

    $clauses = array();
    foreach ($this->_columns as $tableName => $table) {
      if (array_key_exists('filters', $table)) {

        foreach ($table['filters'] as $fieldName => $field) {
          $clause = NULL;
          if (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);

            $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 (!empty($clause)) {
            $clauses[] = $clause;
          }
        }
      }
    }

    if (empty($clauses)) {
      $this->_where .= " ";
    }
    else {
      $this->_where .= " AND " . implode(' AND ', $clauses);
    }

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

  function groupBy() {
    $this->_groupBy = array();
    if (is_array($this->_params['group_bys']) &&
      !empty($this->_params['group_bys'])
    ) {
      foreach ($this->_columns as $tableName => $table) {
        if (array_key_exists('group_bys', $table)) {
          foreach ($table['group_bys'] as $fieldName => $field) {
            if (!empty($this->_params['group_bys'][$fieldName])) {
              if (!empty($field['chart'])) {
                $this->assign('chartSupported', TRUE);
              }
              if (!empty($table['group_bys'][$fieldName]['frequency']) &&
                !empty($this->_params['group_bys_freq'][$fieldName])
              ) {

                $append = "YEAR({$field['dbAlias']}),";
                if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
                  array('year')
                )) {
                  $append = '';
                }
                $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
                $append = TRUE;
              }
              else {
                $this->_groupBy[] = $field['dbAlias'];
              }
            }
          }
        }
      }

      $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
    }
    else {
      $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id ";
    }
  }

  /**
   * @param $fields
   * @param $files
   * @param $self
   *
   * @return array
   */
  function formRule($fields, $files, $self) {
    $errors = array();
    $contactFields = array('sort_name', 'email', 'phone');
    if (!empty($fields['group_bys'])) {

      if (!empty($fields['group_bys']['activity_type_id']) && empty($fields['group_bys']['sort_name'])) {
        foreach ($fields['fields'] as $fieldName => $val) {
          if (in_array($fieldName, $contactFields)) {
            $errors['fields'] = ts("Please select GroupBy 'Contact' to display Contact Fields");
            break;
          }
        }
      }

      if (!empty($fields['group_bys']['activity_date_time'])) {
        if (!empty($fields['group_bys']['sort_name'])) {
          $errors['fields'] = ts("Please do not select GroupBy 'Activity Date' with GroupBy 'Contact'");
        }
        else {
          foreach ($fields['fields'] as $fieldName => $val) {
            if (in_array($fieldName, $contactFields)) {
              $errors['fields'] = ts("Please do not select any Contact Fields with GroupBy 'Activity Date'");
              break;
            }
          }
        }
      }
    }
    return $errors;
  }

  function postProcess() {
    // get the acl clauses built before we assemble the query
    $this->buildACLClause($this->_aliases['civicrm_contact']);
    parent::postProcess();
  }

  /**
   * @param $rows
   */
  function alterDisplay(&$rows) {
    // custom code to alter rows

    $entryFound = FALSE;
    $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
    $flagContact = 0;

    $onHover = ts('View Contact Summary for this Contact');
    foreach ($rows as $rowNum => $row) {

      if (array_key_exists('civicrm_contact_sort_name', $row) && $this->_outputMode != 'csv') {
        if ($value = $row['civicrm_contact_id']) {

          if ($rowNum == 0) {
            $priviousContact = $value;
          }
          else {
            if ($priviousContact == $value) {
              $flagContact = 1;
              $priviousContact = $value;
            }
            else {
              $flagContact = 0;
              $priviousContact = $value;
            }
          }

          if ($flagContact == 1) {
            $rows[$rowNum]['civicrm_contact_sort_name'] = "";

            if (array_key_exists('civicrm_email_email', $row)) {
              $rows[$rowNum]['civicrm_email_email'] = "";
            }
            if (array_key_exists('civicrm_phone_phone', $row)) {
              $rows[$rowNum]['civicrm_phone_phone'] = "";
            }
          }
          else {
            $url = CRM_Utils_System::url('civicrm/contact/view',
              'reset=1&cid=' . $value,
              $this->_absoluteUrl
            );

            $rows[$rowNum]['civicrm_contact_sort_name'] = "<a href='$url'>" . $row['civicrm_contact_sort_name'] .
              '</a>';
          }
          $entryFound = TRUE;
        }
      }

      if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
        if ($value = $row['civicrm_activity_activity_type_id']) {

          $value = explode(',', $value);
          foreach ($value as $key => $id) {
            $value[$key] = $activityType[$id];
          }

          $rows[$rowNum]['civicrm_activity_activity_type_id'] = implode(' , ', $value);
          $entryFound = TRUE;
        }
      }

      if (!$entryFound) {
        break;
      }
    }
  }
}
Commit	Line	Data
6a488035	1	<?php
6a488035 TO	2
	3	/*
	4	+--------------------------------------------------------------------+
06b69b18	5	\| CiviCRM version 4.5 \|
6a488035	6	+--------------------------------------------------------------------+
06b69b18	7	\| Copyright CiviCRM LLC (c) 2004-2014 \|
6a488035 TO	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
06b69b18	32	* @copyright CiviCRM LLC (c) 2004-2014
6a488035 TO	33	* $Id$
	34	*
	35	*/
	36	class CRM_Report_Form_ActivitySummary extends CRM_Report_Form {
	37
	38	protected $_emailField = FALSE;
430ae6dd TO	39	protected $_phoneField = FALSE;
430ae6dd TO	40
74cf4551 EM	41	/**
	42	*
	43	*/
	44	/**
	45	*
	46	*/
430ae6dd	47	function __construct() {
6a488035	48	$this->_columns = array(
a8c87952	49	'civicrm_contact' => array(
6a488035	50	'dao' => 'CRM_Contact_DAO_Contact',
a8c87952 EM	51	'fields' => array(
a8c87952 EM	52	'id' => array(
6a488035 TO	53	'required' => TRUE,
	54	'no_display' => TRUE,
	55	),
a8c87952 EM	56	'sort_name' => array(
a8c87952 EM	57	'title' => ts('Contact Name'),
6a488035 TO	58	'default' => TRUE,
	59	'no_repeat' => TRUE,
	60	),
	61	),
a8c87952 EM	62	'filters' => array(
	63	'sort_name' => array(
	64	'title' => ts('Contact Name'),
6a488035 TO	65	),
6a488035 TO	66	),
a8c87952 EM	67	'group_bys' => array(
a8c87952 EM	68	'sort_name' => array(
6a488035 TO	69	'name' => 'id',
	70	'title' => ts('Contact'),
	71	'default' => TRUE,
	72	),
	73	),
a8c87952 EM	74	'order_bys' => array(
	75	'sort_name' => array(
	76	'title' => ts('Contact Name')
	77	),
6a488035 TO	78	),
	79	'grouping' => 'contact-fields',
	80	),
a8c87952	81	'civicrm_email' => array(
6a488035	82	'dao' => 'CRM_Core_DAO_Email',
a8c87952 EM	83	'fields' => array(
a8c87952 EM	84	'email' => array(
6a488035 TO	85	'title' => 'Email',
	86	'default' => TRUE,
	87	),
	88	),
a8c87952 EM	89	'order_bys' => array(
	90	'email' => array(
	91	'title' => ts('Email')
	92	),
6a488035 TO	93	),
	94	'grouping' => 'contact-fields',
	95	),
a8c87952	96	'civicrm_phone' => array(
6a488035	97	'dao' => 'CRM_Core_DAO_Email',
a8c87952 EM	98	'fields' => array(
	99	'phone' => array(
	100	'title' => 'Phone'
	101	),
6a488035 TO	102	),
	103	'grouping' => 'contact-fields',
	104	),
a8c87952	105	'civicrm_activity' => array(
6a488035	106	'dao' => 'CRM_Activity_DAO_Activity',
a8c87952 EM	107	'fields' => array(
	108	'activity_type_id' => array(
	109	'title' => ts('Activity Type'),
6a488035 TO	110	'default' => TRUE,
	111	'type' => CRM_Utils_Type::T_STRING,
	112	),
a8c87952	113	'duration' => array(
6a488035	114	'title' => 'Duration',
a8c87952	115	'statistics' => array(
6a488035 TO	116	'sum' => ts('Total Duration'),
	117	),
	118	),
a8c87952	119	'id' => array(
6a488035 TO	120	'title' => 'Total Activities',
6a488035 TO	121	'required' => TRUE,
a8c87952	122	'statistics' => array(
6a488035 TO	123	'count' => ts('Activity Count'),
	124	),
	125	),
	126	),
a8c87952 EM	127	'filters' => array(
	128	'activity_date_time' => array(
	129	'operatorType' => CRM_Report_Form::OP_DATE
	130	),
	131	'activity_type_id' => array(
	132	'title' => ts('Activity Type'),
6a488035 TO	133	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	134	'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
	135	),
a8c87952 EM	136	'status_id' => array(
a8c87952 EM	137	'title' => ts('Activity Status'),
6a488035 TO	138	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	139	'options' => CRM_Core_PseudoConstant::activityStatus(),
	140	),
a8c87952 EM	141	'priority_id' => array(
a8c87952 EM	142	'title' => ts('Priority'),
6a488035	143	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
cbf48754	144	'options' => CRM_Core_PseudoConstant::get('CRM_Activity_DAO_Activity', 'priority_id'),
6a488035 TO	145	),
6a488035 TO	146	),
a8c87952	147	'group_bys' => array(
6a488035	148	'activity_date_time' =>
a8c87952 EM	149	array(
	150	'title' => ts('Activity Date'),
	151	'frequency' => TRUE,
	152	),
	153	'activity_type_id' => array(
	154	'title' => ts('Activity Type'),
6a488035 TO	155	'default' => TRUE,
	156	),
	157	),
a8c87952 EM	158	'order_bys' => array(
	159	'activity_date_time' => array(
	160	'title' => ts('Activity Date')
	161	),
	162	'activity_type_id' => array(
	163	'title' => ts('Activity Type')
	164	),
6a488035 TO	165	),
	166	'grouping' => 'activity-fields',
	167	'alias' => 'activity',
	168	),
	169	);
	170
	171	parent::__construct();
	172	}
	173
	174	function select() {
	175	$select = array();
	176	$this->_columnHeaders = array();
	177	foreach ($this->_columns as $tableName => $table) {
	178	if (array_key_exists('group_bys', $table)) {
	179	foreach ($table['group_bys'] as $fieldName => $field) {
a7488080	180	if (!empty($this->_params['group_bys'][$fieldName])) {
6221462f	181	if (isset($this->_params['group_bys_freq']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
a8c87952 EM	182	switch ($this->_params['group_bys_freq'][$fieldName]) {
	183	case 'YEARWEEK':
	184	$select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
	185
	186	$select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
	187	$select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
	188	$field['title'] = 'Week';
	189	break;
	190
	191	case 'YEAR':
	192	$select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start";
	193	$select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
	194	$select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
	195	$field['title'] = 'Year';
	196	break;
	197
	198	case 'MONTH':
	199	$select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
	200	$select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
	201	$select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
	202	$field['title'] = 'Month';
	203	break;
	204
	205	case 'QUARTER':
	206	$select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
	207	$select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
	208	$select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
	209	$field['title'] = 'Quarter';
	210	break;
	211	}
6a488035 TO	212	$this->_interval = $field['title'];
	213	$this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
	214	$this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
	215	$this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
	216
	217	// just to make sure these values are transfered to rows.
	218	// since we need that for calculation purpose,
	219	// e.g making subtotals look nicer or graphs
	220	$this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
	221	$this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
	222	}
	223	}
	224	}
	225	}
	226	if (array_key_exists('fields', $table)) {
	227	foreach ($table['fields'] as $fieldName => $field) {
8cc574cf	228	if (!empty($field['required']) \|\| !empty($this->_params['fields'][$fieldName])) {
6a488035 TO	229	if ($tableName == 'civicrm_email') {
	230	$this->_emailField = TRUE;
	231	}
	232	if ($tableName == 'civicrm_phone') {
	233	$this->_phoneField = TRUE;
	234	}
a7488080	235	if (!empty($field['statistics'])) {
6a488035 TO	236	foreach ($field['statistics'] as $stat => $label) {
	237	switch (strtolower($stat)) {
	238	case 'count':
	239	$select[] = "COUNT(DISTINCT({$field['dbAlias']})) as {$tableName}_{$fieldName}_{$stat}";
	240	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
	241	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
	242	$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
	243	break;
	244
	245	case 'sum':
	246	$select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
	247	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
	248	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
	249	$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
	250	break;
	251	}
	252	}
	253	}
	254	elseif ($fieldName == 'activity_type_id') {
a7488080	255	if (empty($this->_params['group_bys']['activity_type_id'])) {
6a488035 TO	256	$select[] = "GROUP_CONCAT(DISTINCT {$field['dbAlias']} ORDER BY {$field['dbAlias']} ) as {$tableName}_{$fieldName}";
	257	}
	258	else {
	259	$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
	260	}
	261	$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
	262	$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
	263	$this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
	264	}
	265	else {
	266	$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
	267	$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
	268	$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
	269	$this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
	270	}
	271	}
	272	}
	273	}
	274	}
	275
	276	$this->_select = "SELECT " . implode(', ', $select) . " ";
	277	}
	278
	279	function from() {
ecaec004 RN	280	$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
	281	$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
	282	$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
	283	$sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
6a488035 TO	284
	285	$this->_from = "
	286	FROM civicrm_activity {$this->_aliases['civicrm_activity']}
2f4c2f5d	287
ecaec004 RN	288	LEFT JOIN civicrm_activity_contact target_activity
	289	ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id AND
	290	target_activity.record_type_id = {$targetID}
	291	LEFT JOIN civicrm_activity_contact assignment_activity
	292	ON {$this->_aliases['civicrm_activity']}.id = assignment_activity.activity_id AND
	293	assignment_activity.record_type_id = {$assigneeID}
	294	LEFT JOIN civicrm_activity_contact source_activity
	295	ON {$this->_aliases['civicrm_activity']}.id = source_activity.activity_id AND
	296	source_activity.record_type_id = {$sourceID}
	297	LEFT JOIN civicrm_contact contact_civireport
	298	ON target_activity.contact_id = contact_civireport.id
	299	LEFT JOIN civicrm_contact civicrm_contact_assignee
	300	ON assignment_activity.contact_id = civicrm_contact_assignee.id
	301	LEFT JOIN civicrm_contact civicrm_contact_source
	302	ON source_activity.contact_id = civicrm_contact_source.id
6a488035	303	{$this->_aclFrom}
2f4c2f5d	304	LEFT JOIN civicrm_option_value
6a488035	305	ON ( {$this->_aliases['civicrm_activity']}.activity_type_id = civicrm_option_value.value )
2f4c2f5d	306	LEFT JOIN civicrm_option_group
6a488035	307	ON civicrm_option_group.id = civicrm_option_value.option_group_id
2f4c2f5d	308	LEFT JOIN civicrm_case_activity
6a488035	309	ON civicrm_case_activity.activity_id = {$this->_aliases['civicrm_activity']}.id
2f4c2f5d	310	LEFT JOIN civicrm_case
6a488035	311	ON civicrm_case_activity.case_id = civicrm_case.id
2f4c2f5d	312	LEFT JOIN civicrm_case_contact
6a488035 TO	313	ON civicrm_case_contact.case_id = civicrm_case.id ";
	314
	315	if ($this->_emailField) {
	316	$this->_from .= "
	317	LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
	318	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
	319	{$this->_aliases['civicrm_email']}.is_primary = 1 ";
	320	}
	321
	322	if ($this->_phoneField) {
	323	$this->_from .= "
	324	LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
	325	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
	326	{$this->_aliases['civicrm_phone']}.is_primary = 1 ";
	327	}
	328	}
	329
	330	function where() {
2f4c2f5d	331	$this->_where = " WHERE civicrm_option_group.name = 'activity_type' AND
6a488035 TO	332	{$this->_aliases['civicrm_activity']}.is_test = 0 AND
	333	{$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
	334	{$this->_aliases['civicrm_activity']}.is_current_revision = 1";
	335
	336	$clauses = array();
	337	foreach ($this->_columns as $tableName => $table) {
	338	if (array_key_exists('filters', $table)) {
	339
	340	foreach ($table['filters'] as $fieldName => $field) {
	341	$clause = NULL;
	342	if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
	343	$relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
a8c87952 EM	344	$from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
a8c87952 EM	345	$to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
6a488035 TO	346
	347	$clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
	348	}
	349	else {
	350	$op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
	351	if ($op) {
	352	$clause = $this->whereClause($field,
	353	$op,
	354	CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
	355	CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
	356	CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
	357	);
	358	}
	359	}
	360
	361	if (!empty($clause)) {
	362	$clauses[] = $clause;
	363	}
	364	}
	365	}
	366	}
	367
	368	if (empty($clauses)) {
	369	$this->_where .= " ";
	370	}
	371	else {
	372	$this->_where .= " AND " . implode(' AND ', $clauses);
	373	}
	374
	375	if ($this->_aclWhere) {
	376	$this->_where .= " AND {$this->_aclWhere} ";
	377	}
	378	}
	379
	380	function groupBy() {
	381	$this->_groupBy = array();
	382	if (is_array($this->_params['group_bys']) &&
	383	!empty($this->_params['group_bys'])
	384	) {
	385	foreach ($this->_columns as $tableName => $table) {
	386	if (array_key_exists('group_bys', $table)) {
	387	foreach ($table['group_bys'] as $fieldName => $field) {
a7488080 CW	388	if (!empty($this->_params['group_bys'][$fieldName])) {
a7488080 CW	389	if (!empty($field['chart'])) {
6a488035 TO	390	$this->assign('chartSupported', TRUE);
6a488035 TO	391	}
a8c87952 EM	392	if (!empty($table['group_bys'][$fieldName]['frequency']) &&
	393	!empty($this->_params['group_bys_freq'][$fieldName])
	394	) {
6a488035 TO	395
	396	$append = "YEAR({$field['dbAlias']}),";
	397	if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
a8c87952 EM	398	array('year')
a8c87952 EM	399	)) {
6a488035 TO	400	$append = '';
	401	}
	402	$this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
	403	$append = TRUE;
	404	}
	405	else {
	406	$this->_groupBy[] = $field['dbAlias'];
	407	}
	408	}
	409	}
	410	}
	411	}
	412
	413	$this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
	414	}
	415	else {
	416	$this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id ";
	417	}
	418	}
	419
74cf4551 EM	420	/**
	421	* @param $fields
	422	* @param $files
	423	* @param $self
	424	*
	425	* @return array
	426	*/
6a488035 TO	427	function formRule($fields, $files, $self) {
	428	$errors = array();
	429	$contactFields = array('sort_name', 'email', 'phone');
a7488080	430	if (!empty($fields['group_bys'])) {
6a488035	431
8cc574cf	432	if (!empty($fields['group_bys']['activity_type_id']) && empty($fields['group_bys']['sort_name'])) {
6a488035 TO	433	foreach ($fields['fields'] as $fieldName => $val) {
	434	if (in_array($fieldName, $contactFields)) {
	435	$errors['fields'] = ts("Please select GroupBy 'Contact' to display Contact Fields");
	436	break;
	437	}
	438	}
	439	}
	440
a7488080 CW	441	if (!empty($fields['group_bys']['activity_date_time'])) {
a7488080 CW	442	if (!empty($fields['group_bys']['sort_name'])) {
6a488035 TO	443	$errors['fields'] = ts("Please do not select GroupBy 'Activity Date' with GroupBy 'Contact'");
	444	}
	445	else {
	446	foreach ($fields['fields'] as $fieldName => $val) {
	447	if (in_array($fieldName, $contactFields)) {
	448	$errors['fields'] = ts("Please do not select any Contact Fields with GroupBy 'Activity Date'");
	449	break;
	450	}
	451	}
	452	}
	453	}
	454	}
	455	return $errors;
	456	}
	457
	458	function postProcess() {
	459	// get the acl clauses built before we assemble the query
	460	$this->buildACLClause($this->_aliases['civicrm_contact']);
	461	parent::postProcess();
	462	}
	463
74cf4551 EM	464	/**
	465	* @param $rows
	466	*/
6a488035 TO	467	function alterDisplay(&$rows) {
	468	// custom code to alter rows
	469
a8c87952	470	$entryFound = FALSE;
6a488035	471	$activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
a8c87952	472	$flagContact = 0;
6a488035 TO	473
	474	$onHover = ts('View Contact Summary for this Contact');
	475	foreach ($rows as $rowNum => $row) {
	476
	477	if (array_key_exists('civicrm_contact_sort_name', $row) && $this->_outputMode != 'csv') {
	478	if ($value = $row['civicrm_contact_id']) {
	479
	480	if ($rowNum == 0) {
	481	$priviousContact = $value;
	482	}
	483	else {
	484	if ($priviousContact == $value) {
	485	$flagContact = 1;
	486	$priviousContact = $value;
	487	}
	488	else {
	489	$flagContact = 0;
	490	$priviousContact = $value;
	491	}
	492	}
	493
	494	if ($flagContact == 1) {
	495	$rows[$rowNum]['civicrm_contact_sort_name'] = "";
	496
	497	if (array_key_exists('civicrm_email_email', $row)) {
	498	$rows[$rowNum]['civicrm_email_email'] = "";
	499	}
	500	if (array_key_exists('civicrm_phone_phone', $row)) {
	501	$rows[$rowNum]['civicrm_phone_phone'] = "";
	502	}
	503	}
	504	else {
	505	$url = CRM_Utils_System::url('civicrm/contact/view',
	506	'reset=1&cid=' . $value,
	507	$this->_absoluteUrl
	508	);
	509
a8c87952 EM	510	$rows[$rowNum]['civicrm_contact_sort_name'] = "<a href='$url'>" . $row['civicrm_contact_sort_name'] .
a8c87952 EM	511	'</a>';
6a488035 TO	512	}
	513	$entryFound = TRUE;
	514	}
	515	}
	516
	517	if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
	518	if ($value = $row['civicrm_activity_activity_type_id']) {
	519
	520	$value = explode(',', $value);
	521	foreach ($value as $key => $id) {
	522	$value[$key] = $activityType[$id];
	523	}
	524
	525	$rows[$rowNum]['civicrm_activity_activity_type_id'] = implode(' , ', $value);
	526	$entryFound = TRUE;
	527	}
	528	}
	529
	530	if (!$entryFound) {
	531	break;
	532	}
	533	}
	534	}
	535	}
	536