com.dpci.constituentsonly/CRM/ConstituentsOnly/BAO/ConstituentsOnly.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');
  }

}