*
* @var array
*/
- protected $_options = array();
+ public $_options = array();
/**
* By default most reports hide contact id.
*/
protected $_groupFilter = FALSE;
+ /**
+ * Has the report been optimised for group filtering.
+ *
+ * The functionality for group filtering has been improved but not
+ * all reports have been adjusted to take care of it.
+ *
+ * This property exists to highlight the reports which are still using the
+ * slow method & allow group filtering to still work for them until they
+ * can be migrated.
+ *
+ * In order to protect extensions we have to default to TRUE - but I have
+ * separately marked every class with a groupFilter in the hope that will trigger
+ * people to fix them as they touch them.
+ *
+ * CRM-19170
+ *
+ * @var bool
+ */
+ protected $groupFilterNotOptimised = TRUE;
+
/**
* Navigation fields
*
protected $_selectAliases = array();
protected $_rollup = NULL;
+ /**
+ * Table containing list of contact IDs within the group filter.
+ *
+ * @var string
+ */
+ protected $groupTempTable = '';
+
/**
* @var array
*/
*/
protected $rollupRow = array();
+ /**
+ * @var string Database attributes - character set and collation
+ */
+ protected $_databaseAttributes = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci';
+
/**
* SQL being run in this report.
*
* @throws \Exception
*/
public function preProcessCommon() {
- $this->_force
- = CRM_Utils_Request::retrieve(
- 'force',
- 'Boolean',
- CRM_Core_DAO::$_nullObject
- );
+ $this->_force = CRM_Utils_Request::retrieve('force', 'Boolean');
- $this->_dashBoardRowCount
- = CRM_Utils_Request::retrieve(
- 'rowCount',
- 'Integer',
- CRM_Core_DAO::$_nullObject
- );
+ $this->_dashBoardRowCount = CRM_Utils_Request::retrieve('rowCount', 'Integer');
- $this->_section = CRM_Utils_Request::retrieve('section', 'Integer', CRM_Core_DAO::$_nullObject);
+ $this->_section = CRM_Utils_Request::retrieve('section', 'Integer');
$this->assign('section', $this->_section);
CRM_Core_Region::instance('page-header')->add(array(
$this->assignTabs();
$this->sqlArray[] = $sql;
- foreach (array('LEFT JOIN') as $term) {
- $sql = str_replace($term, '<br />  ' . $term, $sql);
- }
- foreach (array('FROM', 'WHERE', 'GROUP BY', 'ORDER BY', 'LIMIT', ';') as $term) {
- $sql = str_replace($term, '<br /><br />' . $term, $sql);
+ foreach ($this->sqlArray as $sql) {
+ foreach (array('LEFT JOIN') as $term) {
+ $sql = str_replace($term, '<br>  ' . $term, $sql);
+ }
+ foreach (array('FROM', 'WHERE', 'GROUP BY', 'ORDER BY', 'LIMIT', ';') as $term) {
+ $sql = str_replace($term, '<br><br>' . $term, $sql);
+ }
+ $this->sqlFormattedArray[] = $sql;
+ $this->assign('sql', implode(';<br><br><br><br>', $this->sqlFormattedArray));
}
- $this->sql .= $sql . "<br />";
-
- $this->assign('sql', $this->sql);
}
/**
protected function getActions($instanceId) {
$actions = CRM_Report_BAO_ReportInstance::getActionMetadata();
if (empty($instanceId)) {
- $actions['report_instance.save']['title'] = ts('Create Report');
+ $actions['report_instance.save'] = array(
+ 'title' => ts('Create Report'),
+ 'data' => array(
+ 'is_confirm' => TRUE,
+ 'confirm_title' => ts('Create Report'),
+ 'confirm_refresh_fields' => json_encode(array(
+ 'title' => array('selector' => '.crm-report-instanceForm-form-block-title', 'prepend' => ''),
+ 'description' => array('selector' => '.crm-report-instanceForm-form-block-description', 'prepend' => ''),
+ )),
+ ),
+ );
}
if (!$this->_csvSupported) {
case 'in':
case 'notin':
- if (is_string($value) && strlen($value)) {
+ if ((is_string($value) || is_numeric($value)) && strlen($value)) {
$value = explode(',', $value);
}
if ($value !== NULL && is_array($value) && count($value) > 0) {
return;
}
+ $customFields = array();
$customFieldIds = array();
foreach ($this->_params['fields'] as $fieldAlias => $value) {
if ($fieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
* @return string
*/
public function buildQuery($applyLimit = TRUE) {
+ $this->buildGroupTempTable();
$this->select();
$this->from();
$this->customDataFrom();
if ($this->addPaging) {
$this->_select = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
- $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
+ $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer');
// @todo all http vars should be extracted in the preProcess
// - not randomly in the class
}
/**
- * Build where clause for groups.
+ * Build a group filter with contempt for large data sets.
+ *
+ * This function has been retained as it takes time to migrate the reports over
+ * to the new method which will not crash on large datasets.
+ *
+ * @deprecated
*
* @param string $field
* @param mixed $value
*
* @return string
*/
- public function whereGroupClause($field, $value, $op) {
-
+ public function legacySlowGroupFilterClause($field, $value, $op) {
$smartGroupQuery = "";
$group = new CRM_Contact_DAO_Group();
{$smartGroupQuery} ) ";
}
+ /**
+ * Build where clause for groups.
+ *
+ * @param string $field
+ * @param mixed $value
+ * @param string $op
+ *
+ * @return string
+ */
+ public function whereGroupClause($field, $value, $op) {
+ if ($this->groupFilterNotOptimised) {
+ return $this->legacySlowGroupFilterClause($field, $value, $op);
+ }
+ if ($op === 'notin') {
+ return " group_temp_table.id IS NULL ";
+ }
+ // We will have used an inner join instead.
+ return "1";
+ }
+
+
+ /**
+ * Create a table of the contact ids included by the group filter.
+ *
+ * This function is called by both the api (tests) and the UI.
+ */
+ public function buildGroupTempTable() {
+ if (!empty($this->groupTempTable) || empty ($this->_params['gid_value']) || $this->groupFilterNotOptimised) {
+ return;
+ }
+ $filteredGroups = (array) $this->_params['gid_value'];
+
+ $groups = civicrm_api3('Group', 'get', array(
+ 'is_active' => 1,
+ 'id' => array('IN' => $filteredGroups),
+ 'saved_search_id' => array('>' => 0),
+ 'return' => 'id',
+ ));
+ $smartGroups = array_keys($groups['values']);
+
+ $query = "
+ SELECT group_contact.contact_id as id
+ FROM civicrm_group_contact group_contact
+ WHERE group_contact.group_id IN (" . implode(', ', $filteredGroups) . ")
+ AND group_contact.status = 'Added' ";
+
+ if (!empty($smartGroups)) {
+ CRM_Contact_BAO_GroupContactCache::check($smartGroups);
+ $smartGroups = implode(',', $smartGroups);
+ $query .= "
+ UNION DISTINCT
+ SELECT smartgroup_contact.contact_id as id
+ FROM civicrm_group_contact_cache smartgroup_contact
+ WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
+ }
+
+ $this->groupTempTable = 'civicrm_report_temp_group_' . date('Ymd_') . uniqid();
+ $this->executeReportQuery("
+ CREATE TEMPORARY TABLE $this->groupTempTable
+ $query
+ ");
+ CRM_Core_DAO::executeQuery("ALTER TABLE $this->groupTempTable ADD INDEX i_id(id)");
+ }
+
+ /**
+ * Execute query and add it to the developer tab.
+ *
+ * @param string $query
+ * @param array $params
+ *
+ * @return \CRM_Core_DAO|object
+ */
+ protected function executeReportQuery($query, $params = array()) {
+ $this->addToDeveloperTab($query);
+ return CRM_Core_DAO::executeQuery($query, $params);
+ }
+
/**
* Build where clause for tags.
*
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
}
+ // CRM-19401 fix
+ if ($customDAO->html_type == 'Select' && !array_key_exists('options', $curFilters[$fieldName])) {
+ $options = CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search');
+ if ($options !== FALSE) {
+ $curFilters[$fieldName]['operatorType'] = CRM_Core_BAO_CustomField::isSerialized($customDAO) ? CRM_Report_Form::OP_MULTISELECT_SEPARATOR : CRM_Report_Form::OP_MULTISELECT;
+ $curFilters[$fieldName]['options'] = $options;
+ }
+ }
+
if (!array_key_exists('type', $curFields[$fieldName])) {
$curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
}
* address fields for construct clause
*/
public function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array('country_id' => TRUE)) {
+ $defaultAddressFields = array(
+ 'street_address' => ts('Street Address'),
+ 'supplemental_address_1' => ts('Supplementary Address Field 1'),
+ 'supplemental_address_2' => ts('Supplementary Address Field 2'),
+ 'street_number' => ts('Street Number'),
+ 'street_name' => ts('Street Name'),
+ 'street_unit' => ts('Street Unit'),
+ 'city' => ts('City'),
+ 'postal_code' => ts('Postal Code'),
+ 'postal_code_suffix' => ts('Postal Code Suffix'),
+ 'country_id' => ts('Country'),
+ 'state_province_id' => ts('State/Province'),
+ 'county_id' => ts('County'),
+ );
$addressFields = array(
'civicrm_address' => array(
'dao' => 'CRM_Core_DAO_Address',
'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
'name' => 'name',
),
- 'street_address' => array(
- 'title' => ts('Street Address'),
- 'default' => CRM_Utils_Array::value('street_address', $defaults, FALSE),
- ),
- 'supplemental_address_1' => array(
- 'title' => ts('Supplementary Address Field 1'),
- 'default' => CRM_Utils_Array::value('supplemental_address_1', $defaults, FALSE),
- ),
- 'supplemental_address_2' => array(
- 'title' => ts('Supplementary Address Field 2'),
- 'default' => CRM_Utils_Array::value('supplemental_address_2', $defaults, FALSE),
- ),
- 'street_number' => array(
- 'name' => 'street_number',
- 'title' => ts('Street Number'),
- 'type' => 1,
- 'default' => CRM_Utils_Array::value('street_number', $defaults, FALSE),
- ),
- 'street_name' => array(
- 'name' => 'street_name',
- 'title' => ts('Street Name'),
- 'type' => 1,
- 'default' => CRM_Utils_Array::value('street_name', $defaults, FALSE),
- ),
- 'street_unit' => array(
- 'name' => 'street_unit',
- 'title' => ts('Street Unit'),
- 'type' => 1,
- 'default' => CRM_Utils_Array::value('street_unit', $defaults, FALSE),
- ),
- 'city' => array(
- 'title' => ts('City'),
- 'default' => CRM_Utils_Array::value('city', $defaults, FALSE),
- ),
- 'postal_code' => array(
- 'title' => ts('Postal Code'),
- 'default' => CRM_Utils_Array::value('postal_code', $defaults, FALSE),
- ),
- 'postal_code_suffix' => array(
- 'title' => ts('Postal Code Suffix'),
- 'default' => CRM_Utils_Array::value('postal_code_suffix', $defaults, FALSE),
- ),
- 'country_id' => array(
- 'title' => ts('Country'),
- 'default' => CRM_Utils_Array::value('country_id', $defaults, FALSE),
- ),
- 'state_province_id' => array(
- 'title' => ts('State/Province'),
- 'default' => CRM_Utils_Array::value('state_province_id', $defaults, FALSE),
- ),
- 'county_id' => array(
- 'title' => ts('County'),
- 'default' => CRM_Utils_Array::value('county_id', $defaults, FALSE),
- ),
),
'grouping' => 'location-fields',
),
);
+ foreach ($defaultAddressFields as $fieldName => $fieldLabel) {
+ $addressFields['civicrm_address']['fields'][$fieldName] = array(
+ 'title' => $fieldLabel,
+ 'default' => CRM_Utils_Array::value($fieldName, $defaults, FALSE),
+ );
+ }
+ $street_address_filters = $general_address_filters = array();
if ($filters) {
// Address filter depends on whether street address parsing is enabled.
// (CRM-18696)
$street_address_filters = array(
'street_number' => array(
'title' => ts('Street Number'),
- 'type' => 1,
+ 'type' => CRM_Utils_Type::T_INT,
'name' => 'street_number',
),
'street_name' => array(
'title' => ts('Street Name'),
'name' => 'street_name',
- 'operator' => 'like',
+ 'type' => CRM_Utils_Type::T_STRING,
),
);
}
$street_address_filters = array(
'street_address' => array(
'title' => ts('Street Address'),
- 'operator' => 'like',
+ 'type' => CRM_Utils_Type::T_STRING,
'name' => 'street_address',
),
);
$general_address_filters = array(
'postal_code' => array(
'title' => ts('Postal Code'),
- 'type' => 1,
+ 'type' => CRM_Utils_Type::T_STRING,
'name' => 'postal_code',
),
'city' => array(
'title' => ts('City'),
- 'operator' => 'like',
+ 'type' => CRM_Utils_Type::T_STRING,
'name' => 'city',
),
'country_id' => array(
// There is no reason not to add links for all fields but it seems a bit odd to be able to click on
// 'Mrs'. Also, we don't have metadata about the title. So, add selectively to addLinks.
$addLinks = array('gender_id' => 'Gender');
- foreach (array('prefix_id', 'suffix_id', 'gender_id') as $fieldName) {
+ foreach (array('prefix_id', 'suffix_id', 'gender_id', 'contact_sub_type', 'preferred_language') as $fieldName) {
if (array_key_exists('civicrm_contact_' . $fieldName, $row)) {
if (($value = $row['civicrm_contact_' . $fieldName]) != FALSE) {
- $rows[$rowNum]['civicrm_contact_' . $fieldName] = CRM_Core_Pseudoconstant::getLabel('CRM_Contact_BAO_Contact', $fieldName, $value);
+ $rowValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
+ $rowLabels = array();
+ foreach ($rowValues as $rowValue) {
+ if ($rowValue) {
+ $rowLabels[] = CRM_Core_Pseudoconstant::getLabel('CRM_Contact_BAO_Contact', $fieldName, $rowValue);
+ }
+ }
+ $rows[$rowNum]['civicrm_contact_' . $fieldName] = implode(', ', $rowLabels);
if ($baseUrl && ($title = CRM_Utils_Array::value($fieldName, $addLinks)) != FALSE) {
$this->addLinkToRow($rows[$rowNum], $baseUrl, $linkText, $value, $fieldName, 'civicrm_contact', $title);
}
'do_not_sms' => array(),
'is_opt_out' => array(),
'is_deceased' => array(),
+ 'preferred_language' => array(),
);
}
'type' => CRM_Utils_Type::T_BOOLEAN,
'default' => 0,
),
+ 'do_not_email' => array(
+ 'title' => ts('Do not email'),
+ 'type' => CRM_Utils_Type::T_BOOLEAN,
+ ),
+ 'do_not_phone' => array(
+ 'title' => ts('Do not phone'),
+ 'type' => CRM_Utils_Type::T_BOOLEAN,
+ ),
+ 'do_not_mail' => array(
+ 'title' => ts('Do not mail'),
+ 'type' => CRM_Utils_Type::T_BOOLEAN,
+ ),
+ 'do_not_sms' => array(
+ 'title' => ts('Do not SMS'),
+ 'type' => CRM_Utils_Type::T_BOOLEAN,
+ ),
+ 'is_opt_out' => array(
+ 'title' => ts('Do not bulk email'),
+ 'type' => CRM_Utils_Type::T_BOOLEAN,
+ ),
+ 'preferred_language' => array(
+ 'title' => ts('Preferred Language'),
+ ),
);
}
// upload strictly for '.png' images
$name = trim(basename(CRM_Utils_Request::retrieve('name', 'String', CRM_Core_DAO::$_nullObject, FALSE, NULL, 'GET')));
if (preg_match('/\.png$/', $name)) {
- //
- // POST data is usually string data, but we are passing a RAW .png
- // so PHP is a bit confused and $_POST is empty. But it has saved
- // the raw bits into $HTTP_RAW_POST_DATA
- //
- $httpRawPostData = $GLOBALS['HTTP_RAW_POST_DATA'];
+
+ // Get the RAW .png from the input.
+ $httpRawPostData = file_get_contents("php://input");
// prepare the directory
$config = CRM_Core_Config::singleton();
}
}
+ /**
+ * Set the base table for the FROM clause.
+ *
+ * Sets up the from clause, allowing for the possibility it might be a
+ * temp table pre-filtered by groups if a group filter is in use.
+ *
+ * @param string $baseTable
+ * @param string $field
+ * @param null $tableAlias
+ */
+ public function setFromBase($baseTable, $field = 'id', $tableAlias = NULL) {
+ if (!$tableAlias) {
+ $tableAlias = $this->_aliases[$baseTable];
+ }
+ $this->_from = $this->_from = " FROM $baseTable $tableAlias ";
+ $this->joinGroupTempTable($baseTable, $field, $tableAlias);
+ $this->_from .= " {$this->_aclFrom} ";
+ }
+
+ /**
+ * Join the temp table contacting contacts who are members of the filtered groups.
+ *
+ * If we are using an IN filter we use an inner join, otherwise a left join.
+ *
+ * @param string $baseTable
+ * @param string $field
+ * @param string $tableAlias
+ */
+ public function joinGroupTempTable($baseTable, $field, $tableAlias) {
+ if ($this->groupTempTable) {
+ if ($this->_params['gid_op'] == 'in') {
+ $this->_from = " FROM $this->groupTempTable group_temp_table INNER JOIN $baseTable $tableAlias
+ ON group_temp_table.id = $tableAlias.{$field} ";
+ }
+ else {
+ $this->_from .= "
+ LEFT JOIN $this->groupTempTable group_temp_table
+ ON $tableAlias.{$field} = group_temp_table.id ";
+ }
+ }
+ }
+
}