331 lines
9.8 KiB
PHP
331 lines
9.8 KiB
PHP
<?php
|
|
|
|
class CRM_ConstituentsOnly_BAO_ConstituentsOnly {
|
|
|
|
|
|
/**
|
|
* Get Suported Report Classes.
|
|
*
|
|
* @return array
|
|
*
|
|
*/
|
|
public static function getSuportedReportClasses() {
|
|
return [
|
|
'CRM_Report_Form_Contact_Summary',
|
|
];
|
|
}
|
|
|
|
/**
|
|
* Get sql.
|
|
*
|
|
* @return string
|
|
*
|
|
*/
|
|
public static function getCommonSql() {
|
|
return "
|
|
SELECT MAX(contact_a.id) AS contact_id
|
|
FROM civicrm_contact contact_a
|
|
LEfT JOIN civicrm_relationship cr
|
|
ON (cr.contact_id_a = contact_a.id)
|
|
LEFT JOIN civicrm_contact cc
|
|
ON (cr.contact_id_b = cc.id)
|
|
AND cc.is_deleted = 0
|
|
AND (cc.do_not_trade IS NULL OR cc.do_not_trade = 0)
|
|
AND cr.is_active = 1
|
|
WHERE contact_a.do_not_trade = 1
|
|
AND cc.id IS NOT NULL
|
|
GROUP BY contact_a.id
|
|
|
|
UNION
|
|
SELECT MAX(contact_a.id) AS contact_id
|
|
FROM civicrm_contact contact_a
|
|
LEfT JOIN civicrm_relationship cr
|
|
ON (cr.contact_id_b = contact_a.id)
|
|
LEFT JOIN civicrm_contact cc
|
|
ON (cr.contact_id_a = cc.id)
|
|
AND cc.is_deleted = 0
|
|
AND (cc.do_not_trade IS NULL OR cc.do_not_trade = 0)
|
|
AND cr.is_active = 1
|
|
WHERE contact_a.do_not_trade = 1
|
|
AND cc.id IS NOT NULL
|
|
GROUP BY contact_a.id
|
|
";
|
|
}
|
|
|
|
/**
|
|
* Get Constituent Sql.
|
|
*
|
|
* @param array $contactIds
|
|
*
|
|
* @return string
|
|
*/
|
|
public static function getConstituentSql($contactIds, $returnArray = FALSE) {
|
|
$sql[] = "
|
|
SELECT MAX(contact_a.id) AS contact_id, cc.id AS og_contact_id, MAX(cc.sort_name) AS og_sort_name
|
|
FROM civicrm_contact contact_a
|
|
INNER JOIN civicrm_relationship cr
|
|
ON (cr.contact_id_a = contact_a.id)
|
|
AND contact_a.is_deleted = 0
|
|
AND (contact_a.do_not_trade = 0 OR contact_a.do_not_trade IS NULL)
|
|
AND cr.is_active = 1
|
|
INNER JOIN civicrm_contact cc
|
|
ON (cr.contact_id_b = cc.id)
|
|
AND cc.id IN (" . implode(',', $contactIds) . ")
|
|
AND cc.do_not_trade = 1
|
|
GROUP BY cc.id";
|
|
$sql[] = "
|
|
SELECT MAX(contact_a.id) AS contact_id, cc.id AS og_contact_id, MAX(cc.sort_name) AS og_sort_name
|
|
FROM civicrm_contact contact_a
|
|
INNER JOIN civicrm_relationship cr
|
|
ON (cr.contact_id_b = contact_a.id)
|
|
AND contact_a.is_deleted = 0
|
|
AND (contact_a.do_not_trade = 0 OR contact_a.do_not_trade IS NULL)
|
|
AND cr.is_active = 1
|
|
INNER JOIN civicrm_contact cc
|
|
ON (cr.contact_id_a = cc.id)
|
|
AND cc.id IN (" . implode(',', $contactIds) . ")
|
|
AND cc.do_not_trade = 1
|
|
GROUP BY cc.id";
|
|
if ($returnArray) {
|
|
return $sql;
|
|
}
|
|
return "
|
|
SELECT contact_id, MAX(og_contact_id) AS og_contact_id, MAX(og_sort_name) AS og_sort_name
|
|
FROM (" . implode(' UNION ', $sql) . ") AS temp GROUP BY contact_id
|
|
";
|
|
}
|
|
|
|
/**
|
|
* Alter Quick search query.
|
|
*
|
|
* @param string $query
|
|
*
|
|
* @return string
|
|
*/
|
|
public static function getQuickSearchQuery($query) {
|
|
$joinQuery = str_replace('WHERE ', 'WHERE (cc.do_not_trade = 1) AND ', $query);
|
|
$sqls[] = "CREATE TEMPORARY TABLE quick_temp_table_1
|
|
{$joinQuery}
|
|
";
|
|
$queries = self::getConstituentSql(["SELECT id FROM quick_temp_table_1"], TRUE);
|
|
$sqls[] = "
|
|
CREATE TEMPORARY TABLE quick_temp_table_2
|
|
{$queries[0]}
|
|
";
|
|
$sqls[] = "
|
|
INSERT INTO quick_temp_table_2
|
|
{$queries[1]}
|
|
";
|
|
foreach ($sqls as $sql) {
|
|
CRM_Core_DAO::executeQuery($sql);
|
|
}
|
|
$startPoint = stripos($query, 'from');
|
|
$endPoint = stripos($query, 'union');
|
|
$firstQuery = substr($query, $startPoint, ($endPoint - $startPoint));
|
|
$firstQuery = substr($firstQuery, stripos($firstQuery, 'select'));
|
|
$firstQuery = substr($firstQuery, 0, stripos($firstQuery, 'where'));
|
|
$firstQuery = str_replace(
|
|
'sort_name, ',
|
|
"CONCAT(cc.sort_name, ' (', og_sort_name, ')'), ",
|
|
$firstQuery
|
|
);
|
|
|
|
$strToReplace = "
|
|
UNION
|
|
(
|
|
$firstQuery
|
|
INNER JOIN quick_temp_table_2 temp ON temp.contact_id = cc.id
|
|
)
|
|
) t
|
|
";
|
|
|
|
$replace = 'WHERE (cc.do_not_trade IS NULL OR cc.do_not_trade = 0) AND ';
|
|
$query = str_replace('WHERE ', $replace, $query);
|
|
$query = str_replace(') t', $strToReplace, $query);
|
|
return $query;
|
|
}
|
|
|
|
/**
|
|
* Rebuild search result rows.
|
|
*
|
|
* @param array $rows
|
|
* @param array $headers
|
|
*
|
|
* @return array
|
|
*/
|
|
public static function updateSearchRows($rows, $headers) {
|
|
$contactIds = array_keys($rows);
|
|
$sql = self::getConstituentSql($contactIds);
|
|
$result = CRM_Core_DAO::executeQuery($sql);
|
|
$relContactIds = [];
|
|
|
|
while ($result->fetch()) {
|
|
$relContactIds[$result->og_contact_id] = $result->contact_id;
|
|
}
|
|
|
|
if (!empty($relContactIds)) {
|
|
$formValues = ['contact_id' => $relContactIds];
|
|
|
|
$url = CRM_Utils_Array::value('q', $_GET);
|
|
$returnProperties = NULL;
|
|
$request = $_REQUEST;
|
|
|
|
$qfKey = $_REQUEST['qfKey'];
|
|
$cache = "CRM_Contact_Controller_Search_{$qfKey}";
|
|
if (!empty($_REQUEST['has_js'])) {
|
|
$formValuesFromSession = CRM_Core_Session::singleton()->get($cache);
|
|
if (!empty($formValuesFromSession['formValues'])) {
|
|
$request = $formValuesFromSession['formValues'];
|
|
}
|
|
}
|
|
if (in_array($url, ['civicrm/contact/search/builder']) || !empty($request['uf_group_id'])) {
|
|
$returnProperties = CRM_Core_Session::singleton()->get('return_properties_search');
|
|
if (!empty($returnProperties)) {
|
|
foreach ($headers as $header) {
|
|
if (!empty($header['sort'])) {
|
|
$headerName = str_replace('`', '', $header['sort']);
|
|
$returnProperties[$headerName] = 1;
|
|
}
|
|
}
|
|
$returnProperties['contact_sub_type'] = 1;
|
|
$returnProperties['contact_type'] = 1;
|
|
}
|
|
}
|
|
|
|
$queryParams = CRM_Contact_BAO_Query::convertFormValues($formValues);
|
|
$selector = new CRM_Contact_Selector(
|
|
'',
|
|
$queryParams,
|
|
$queryParams,
|
|
$returnProperties
|
|
);
|
|
list($select, $from, $where, $having) = $selector->getQuery()->query();
|
|
$newRows = $selector->getRows('', '', '', '');
|
|
foreach ($relContactIds as $ogContactId => $cid) {
|
|
if (empty($newRows[$cid])) {
|
|
continue;
|
|
}
|
|
$newRows[$cid]['sort_name'] .= " ({$rows[$ogContactId]['sort_name']})";
|
|
$rows[$ogContactId] = $newRows[$cid];
|
|
$prevCacheKey = "civicrm search {$qfKey}";
|
|
$exists = CRM_Core_DAO::singleValueQuery(
|
|
"SELECT count(id) FROM civicrm_prevnext_cache
|
|
WHERE (
|
|
entity_id1 = %1 OR entity_id2 = %1
|
|
)
|
|
AND cacheKey = %2
|
|
AND entity_table = 'civicrm_contact'
|
|
",
|
|
[
|
|
1 => [$cid, 'Integer'],
|
|
2 => [$prevCacheKey, 'String'],
|
|
]
|
|
);
|
|
|
|
if (!$exists) {
|
|
$prevCache = [
|
|
"('civicrm_contact'",
|
|
"'{$cid}'",
|
|
"'{$cid}'",
|
|
"'{$prevCacheKey}'",
|
|
"'" . $newRows[$cid]['sort_name'] . "')",
|
|
];
|
|
CRM_Core_BAO_PrevNextCache::setItem($prevCache);
|
|
}
|
|
CRM_Core_BAO_PrevNextCache::deleteItem($ogContactId, $prevCacheKey);
|
|
}
|
|
}
|
|
return $rows;
|
|
}
|
|
|
|
/**
|
|
* Rebuild report result rows.
|
|
*
|
|
* @param array $rows
|
|
* @param CRM_Core_Report $object
|
|
*
|
|
* @return array
|
|
*/
|
|
public static function updateReportRows($rows, &$object) {
|
|
if (property_exists($object, '_donotTradeRowBuilding')
|
|
&& $object->_donotTradeRowBuilding
|
|
) {
|
|
return $rows;
|
|
}
|
|
|
|
$contactIds = [];
|
|
foreach ($rows as $id => $row) {
|
|
if (CRM_Utils_Array::value(
|
|
'civicrm_contact_do_not_trade_alter', $row) == 'Yes'
|
|
) {
|
|
$contactIds[$id] = $row['civicrm_contact_id'];
|
|
}
|
|
}
|
|
if (empty($contactIds)) {
|
|
return $rows;
|
|
}
|
|
|
|
$sql = self::getConstituentSql($contactIds);
|
|
$result = CRM_Core_DAO::executeQuery($sql);
|
|
$relContactIds = [];
|
|
while ($result->fetch()) {
|
|
$relContactIds[$result->og_contact_id] = $result->contact_id;
|
|
}
|
|
|
|
if (!empty($relContactIds)) {
|
|
$object->_donotTradeRowBuilding = TRUE;
|
|
|
|
$select = $object->getVar('_select');
|
|
$from = $object->getVar('_from');
|
|
$dbAliases = $object->getVar('_aliases');
|
|
$contactAlias = "`{$dbAliases['civicrm_contact']}`";
|
|
$query = "
|
|
{$select}
|
|
{$from}
|
|
WHERE {$contactAlias}.id IN (" . implode(',', $relContactIds) . ")
|
|
";
|
|
|
|
$newRows = [];
|
|
$tempObj = $object;
|
|
$tempObj->_columnHeaders = $object->_backupHeader;
|
|
$tempObj->buildRows($query, $newRows);
|
|
$tempObj->formatDisplay($newRows);
|
|
foreach ($newRows as $id => $newRow) {
|
|
$contactId = $newRow['civicrm_contact_id'];
|
|
while (in_array($contactId, $relContactIds)) {
|
|
$ogContactId = array_search($contactId, $relContactIds);
|
|
$ogId = array_search($ogContactId, $contactIds);
|
|
$newRow['civicrm_contact_sort_name'] .= " ({$rows[$ogId]['civicrm_contact_sort_name']})";
|
|
$rows[$ogId] = $newRow;
|
|
unset($relContactIds[$ogContactId]);
|
|
}
|
|
}
|
|
}
|
|
return $rows;
|
|
}
|
|
|
|
/**
|
|
* Update sql for report.
|
|
*
|
|
* @param CRM_Core_Report $object
|
|
*
|
|
*/
|
|
public static function updateReportSql(&$object) {
|
|
$where = $object->getVar('_where');
|
|
$sql = self::getCommonSql();
|
|
|
|
$dbAliases = $object->getVar('_aliases');
|
|
$contactAlias = "`{$dbAliases['civicrm_contact']}`";
|
|
$searchFrom = "{$contactAlias}.`do_not_trade` IS NULL OR ({$contactAlias}.`do_not_trade` = 0)";
|
|
$replaceFrom = "IF (
|
|
{$contactAlias}.`do_not_trade` = 1,
|
|
{$contactAlias}.id IN ({$sql}),
|
|
1
|
|
)";
|
|
$where = str_replace($searchFrom, $replaceFrom, $where);
|
|
$object->setVar('_where', $where);
|
|
$object->_backupHeader = $object->getVar('_columnHeaders');
|
|
}
|
|
|
|
}
|