public function select()
{
$this->_columnHeaders = array();
parent::select();
//total_amount was affected by sum as it is considered as one of the stat field
//so it is been replaced with correct alias, CRM-13833
$this->_select = str_replace("sum({$this->_aliases['civicrm_contribution']}.total_amount)", "{$this->_aliases['civicrm_contribution']}.total_amount", $this->_select);
}
function select($recordType = NULL)
{
if (!array_key_exists("contact_{$recordType}", $this->_params['fields']) && $recordType != 'final') {
$this->_nonDisplayFields[] = "civicrm_contact_contact_{$recordType}";
$this->_params['fields']["contact_{$recordType}"] = 1;
}
parent::select();
if ($recordType == 'final' && !empty($this->_nonDisplayFields)) {
foreach ($this->_nonDisplayFields as $fieldName) {
unset($this->_columnHeaders[$fieldName]);
}
}
if (empty($this->_selectAliasesTotal)) {
$this->_selectAliasesTotal = $this->_selectAliases;
}
$removeKeys = array();
if ($recordType == 'target') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_assignee.') || strstr($clause, 'civicrm_contact_source.') || strstr($clause, 'civicrm_email_assignee.') || strstr($clause, 'civicrm_email_source.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} else {
if ($recordType == 'assignee') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_target.') || strstr($clause, 'civicrm_contact_source.') || strstr($clause, 'civicrm_email_target.') || strstr($clause, 'civicrm_email_source.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} else {
if ($recordType == 'source') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_target.') || strstr($clause, 'civicrm_contact_assignee.') || strstr($clause, 'civicrm_email_target.') || strstr($clause, 'civicrm_email_assignee.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} else {
if ($recordType == 'final') {
$this->_selectClauses = $this->_selectAliasesTotal;
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_contact_target') || strstr($clause, 'civicrm_contact_contact_assignee') || strstr($clause, 'civicrm_contact_contact_source')) {
$this->_selectClauses[$key] = "GROUP_CONCAT({$clause} SEPARATOR ';') as {$clause}";
}
}
}
}
}
}
if ($recordType) {
foreach ($removeKeys as $key) {
unset($this->_selectAliases[$key]);
}
$this->_select = "SELECT " . implode(', ', $this->_selectClauses) . " ";
}
}
public function select()
{
$select = $columns = array();
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 (in_array($fieldName, array('unsubscribe_id', 'optout_id', 'forward_id', 'reply_id'))) {
$select[] = "IF({$field['dbAlias']} IS NULL, 'No', 'Yes') as {$tableName}_{$fieldName}";
$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
$this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
unset($this->_columns[$tableName]['fields'][$fieldName]);
$columns[$tableName][$fieldName] = $field;
} elseif ($fieldName == 'delivery_id') {
$select[] = "IF(mailing_event_bounce_civireport.id IS NOT NULL, 'Bounced', IF(mailing_event_delivered_civireport.id IS NOT NULL, 'Successful', 'Unknown')) as {$tableName}_{$fieldName}";
$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
$this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
unset($this->_columns[$tableName]['fields'][$fieldName]);
$columns[$tableName][$fieldName] = $field;
}
}
}
}
}
parent::select();
if (!empty($select)) {
$this->_select .= ', ' . implode(', ', $select) . " ";
}
// put the fields that were unset, back in place
foreach ($columns as $tableName => $table) {
foreach ($table as $fieldName => $fields) {
$this->_columns[$tableName]['fields'][$fieldName] = $fields;
}
}
// simple sort
ksort($this->_columnHeaders);
}
function select()
{
parent::select();
}
/**
* @param null $recordType
*/
public function select($recordType = NULL)
{
if (!array_key_exists("contact_{$recordType}", $this->_params['fields']) && $recordType != 'final') {
$this->_nonDisplayFields[] = "civicrm_contact_contact_{$recordType}";
}
parent::select();
if ($recordType == 'final' && !empty($this->_nonDisplayFields)) {
foreach ($this->_nonDisplayFields as $fieldName) {
unset($this->_columnHeaders[$fieldName]);
}
}
if (empty($this->_selectAliasesTotal)) {
$this->_selectAliasesTotal = $this->_selectAliases;
}
$removeKeys = array();
if ($recordType == 'target') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_assignee.') || strstr($clause, 'civicrm_contact_source.') || strstr($clause, 'civicrm_email_assignee.') || strstr($clause, 'civicrm_email_source.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} elseif ($recordType == 'assignee') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_target.') || strstr($clause, 'civicrm_contact_source.') || strstr($clause, 'civicrm_email_target.') || strstr($clause, 'civicrm_email_source.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} elseif ($recordType == 'source') {
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_target.') || strstr($clause, 'civicrm_contact_assignee.') || strstr($clause, 'civicrm_email_target.') || strstr($clause, 'civicrm_email_assignee.')) {
$removeKeys[] = $key;
unset($this->_selectClauses[$key]);
}
}
} elseif ($recordType == 'final') {
$this->_selectClauses = $this->_selectAliasesTotal;
foreach ($this->_selectClauses as $key => $clause) {
if (strstr($clause, 'civicrm_contact_contact_target') || strstr($clause, 'civicrm_contact_contact_assignee') || strstr($clause, 'civicrm_contact_contact_source')) {
$this->_selectClauses[$key] = "GROUP_CONCAT({$clause} SEPARATOR ';') as {$clause}";
}
}
}
if ($recordType) {
foreach ($removeKeys as $key) {
unset($this->_selectAliases[$key]);
}
if ($recordType != 'final') {
foreach ($this->_columns['civicrm_address']['order_bys'] as $fieldName => $field) {
$orderByFld = $this->_columns['civicrm_address']['order_bys'][$fieldName];
$fldInfo = $this->_columns['civicrm_address']['fields'][$fieldName];
$this->_selectAliases[] = $orderByFld['dbAlias'];
$this->_selectClauses[] = "{$fldInfo['dbAlias']} as {$orderByFld['dbAlias']}";
}
$this->_selectAliases[] = $this->_aliases['civicrm_option_value'];
$this->_selectClauses[] = "{$this->_aliases['civicrm_option_value']}.label as {$this->_aliases['civicrm_option_value']}";
$this->_selectAliases = array_unique($this->_selectAliases);
$this->_selectClauses = array_unique($this->_selectClauses);
}
$this->_select = "SELECT " . implode(', ', $this->_selectClauses) . " ";
}
}
/**
* Select function.
*/
function select() {
if ($this->_preConstrain && !$this->_preConstrained) {
$this->_select = " SELECT DISTINCT {$this->_aliases[$this->_baseTable]}.id";
return;
}
if ($this->_customGroupAggregates) {
return;
}
$this->storeGroupByArray();
$this->unsetBaseTableStatsFieldsWhereNoGroupBy();
foreach ($this->_params['fields'] as $fieldName => $field) {
if (substr($fieldName, 0, 7) == 'custom_') {
foreach ($this->_columns as $table => $specs) {
if (CRM_Utils_Array::value($fieldName, $specs['fields'])) {
if ($specs['fields'][$fieldName]['dataType'] == 'ContactReference') {
$this->_columns[$table]['fields'][$fieldName . '_id'] = $specs['fields'][$fieldName];
$this->_columns[$table]['fields'][$fieldName . '_id']['name'] = 'id';
$this->_columns[$table]['fields'][$fieldName . '_id']['title'] .= ' Id';
$this->_columns[$table]['fields'][$fieldName . '_id']['dbAlias'] = $this->_columns[$table]['fields'][$fieldName]['alias'] . '.id';
$this->_columns[$table]['fields'][$fieldName . '_id']['dataType'] = 'Text';
$this->_columns[$table]['fields'][$fieldName . '_id']['hidden'] = 'TRUE';
$this->_params['fields'][$fieldName . '_id'] = 1;
}
}
}
}
}
parent::select();
if (empty($this->_select) || strtolower(trim($this->_select)) == 'select') {
$this->_select = " SELECT 1 ";
}
}
function select()
{
$this->_columnHeaders = array();
foreach ($this->_columns as $tableName => $table) {
if (array_key_exists('fields', $table)) {
foreach ($table['fields'] as $fieldName => $field) {
if (CRM_Utils_Array::value('required', $field) || CRM_Utils_Array::value($fieldName, $this->_params['fields'])) {
if ($tableName == 'civicrm_email_honor') {
$this->_emailFieldHonor = TRUE;
}
if ($tableName == 'civicrm_contact_honor') {
$this->_nameFieldHonor = TRUE;
}
}
}
}
}
parent::select();
}
/**
* Build the select for the query.
*/
public function select()
{
parent::select();
if (!empty($this->_params['fields']['event_id']) && !empty($this->_params['fields']['event_id2'])) {
// $this->_select .= ", (100*((COUNT(DISTINCT {$this->_aliases['civicrm_participant']}.event_id) - COUNT(DISTINCT {$this->_aliases['civicrm_participant2']}.event_id)) / COUNT(DISTINCT {$this->_aliases['civicrm_participant2']}.event_id))) as event_id_pctchange";
$this->_columnHeaders['event_id_pctchange']['title'] = ts('Events Attended Percent Change');
$this->_columnHeaders['event_id_pctchange']['type'] = CRM_Utils_Type::T_FLOAT;
// $this->_statFields[ts('Events Attended Percent Change')] = 'event_id_pctchange';
// $this->_selectAliases[] = 'event_id_pctchange';
}
if (!empty($this->_params['fields']['unique_staff']) && !empty($this->_params['fields']['unique_staff2'])) {
// $this->_select .= ", (100*((COUNT(DISTINCT {$this->_aliases['civicrm_participant']}.contact_id) - COUNT(DISTINCT {$this->_aliases['civicrm_participant2']}.contact_id)) / COUNT(DISTINCT {$this->_aliases['civicrm_participant2']}.contact_id))) as unique_staff_pctchange";
$this->_columnHeaders['unique_staff_pctchange']['title'] = ts('Unique Staff Percent Change');
$this->_columnHeaders['unique_staff_pctchange']['type'] = CRM_Utils_Type::T_FLOAT;
// $this->_statFields[ts('Unique Staff Percent Change')] = 'unique_staff_pctchange';
// $this->_selectAliases[] = 'unique_staff_pctchange';
}
}
