Cross-Site Article Author Search & Report module

A Drupal 7 custom module designed to query multiple Drupal databases for all of an author's content and organize the results into an aggregated, filterable list.

Completion Date
Report Preview thumbnail
Platform(s)/Language(s)
Code Snippet
/**
 * @file
 * Adds ability to create cross-site reports of entity data.
 */
 
/**
 * Implements hook_permission().
 */
function mm_reports_permission() {
  return array(
    'access report content' => array(
      'title' => t('Access report content'),
      'description' => t('Access the content of reports'),
    ),
    'administer reports' => array(
      'title' => t('Administer Reports'),
      'description' => t('Administer the reports'),
    ),
  );
}
 
/**
 * Pager submit handler/function to the form.
 *
 * This will increment/decrement the results table pager if called.
 */
function mm_reports_pager_submit($form, &$form_state) {
  $operation = preg_replace("/[^A-Za-z0-9]/", '', $form_state['values']['op']);
  // Pager rendering logic.
  switch ($operation) {
    case t('next'):
      // Increment current_page by 1.
      $form_state['values']['current_page']
        = (isset($form_state['values']['current_page']))
        ? ($form_state['values']['current_page'] + 1) : 1;
      break;
 
    case t('previous'):
      // Decrement current_page by 1.
      $form_state['values']['current_page']
        = (isset($form_state['values']['current_page']))
        ? ($form_state['values']['current_page'] - 1) : 0;
      break;
 
    case t('first'):
      $form_state['values']['current_page'] = 0;
      break;
 
    case t('last'):
      $form_state['values']['current_page']
        = $form_state['values']['total_pages'] - 1;
      break;
 
    default:
      // Check to see if the page button passed is a numerical one.
      if (is_numeric($form_state['values']['op'])) {
        // Set the current_page to the numerical value.
        $form_state['values']['current_page'] = $form_state['values']['op'] - 1;
      }
  }
 
  $form_state['rebuild'] = TRUE;
}
/**
 * @file
 * Adds ability to create cross-site reports of author data.
 */
 
/**
 * Implements hook_menu().
 */
function mm_reports_author_menu() {
  $items = [];
 
  $items['admin/reports/mm-reports/author'] = [
    'title' => 'MM Author Search',
    'description' => 'Search db fields for references to an author',
    'page callback' => 'drupal_get_form',
    'page arguments' => ['mm_reports_search_form'],
    'access arguments' => ['access report content'],
    'type' => MENU_NORMAL_ITEM
  ];
 
  return $items;
}
 
/**
 * Implements hook_form_FORM_ID_alter().
 */
function mm_reports_author_form_mm_reports_search_form_alter(&$form, &$form_state, $form_id) {
  if (arg(3) == 'author') {
    // Generate list of author titles for filtering.
    $search = new MmReportsAuthor();
    $search->connect();
    $search->queryAuthors();
    $authors = $search->getAuthors('title');
 
    $form['sstring']['#type'] = 'hidden';
    $form['sstring']['#value'] = '';
    $form['stype']['#type'] = 'hidden';
    $form['stype']['#value'] = 'text';
 
    $form['filter'] = [
      'author' => [
        '#title' => t('Author'),
        '#description' => t('Optionally filter by a specific Author.'),
        '#type' => 'select',
        '#empty_option' => t('- all authors -'),
        '#options' => $authors,
        '#required' => FALSE,
      ],
      'author_fieldset' => [
        '#type' => 'fieldset',
        '#title' => t('Single-Author Search Options'),
        '#states' => [
          'visible' => [
            ':input[name="author"]' => ['!value' => ''],
          ],
        ],
      ],
    ] + $form['filter'];
 
    $form['filter']['author_fieldset']['deeper'] = [
      '#title' => t('Deeper Search'),
      '#description' => t("Search for all references to author's title & id"),
      '#type' => 'checkbox',
    ];
 
    // Set some default filter settings.
    $form['filter']['etfilter']['#default_value'] = 'node';
    $default_bundles = $form['filter']['bfilter']['#options'];
    unset($default_bundles['author']);
    $form['filter']['bfilter']['#default_value'] = $default_bundles;
 
    $form['#validate'] = [];
    $form['#submit'] = ['mm_reports_author_form_submit'];
  }
}
 
/**
 * Add a submit handler/function to the form.
 *
 * This will add a completion message to the screen when the form successfully
 * processes.
 */
function mm_reports_author_form_submit($form, &$form_state) {
  // Initialize cross-site query object.
  $search = new MmReportsAuthor();
  $search->connect($form_state['values']['sitefilter']);
  $search->searchInit();
  $author_title = empty($form_state['values']['author']) ? '' : $form['filter']['author']['#options'][$form_state['values']['author']];
 
  // Generate the data table based on the selected form field values.
  $search->executeAuthorReport(
    $author_title,
    $form_state['values']['etfilter'],
    $form_state['values']['bfilter'],
    $form_state['values']['stfilter'],
    $form_state['values']['refelem'],
    $form_state['values']['deeper']
  );
  // Format the results for either previewing on-screen or for csv download.
  $search->compileResultsTable($form_state['values']['action']);
  // Sort the data.
  $search->sortResults([
    'AuthorName' => 'ASC',
    'Sitename' => 'ASC',
    'NQID' => 'ASC',
    'NQPos' => 'ASC',
    'EntityType' => 'ASC',
    'Bundle' => 'ASC',
    'EntityTitle' => 'ASC',
  ]);
  $table = $search->getResults();
  $header = $search->getHeader();
 
  $form_state['values']['sstring'] = ($form_state['values']['author'] ?? t('all authors'));
 
  switch ($form_state['values']['action']) {
    case 'preview':
      // Generate Preview table.
      // Set current_page to 0.
      $form_state['values']['current_page'] = 0;
      // Send the results table back to the form page for rendering.
      $form_state['results'] = $table;
      $form_state['header'] = $header;
      $form_state['rebuild'] = TRUE;
      break;
 
    case 'download':
      // Download CSV file.
      // Name the file name based on the sitename, the csv format, and the date.
      $filename = '';
 
      if (count($form_state['values']['sitefilter']) == 1) {
        $key = array_values($form_state['values']['sitefilter'])[0];
        $filename .= $form['filter']['sitefilter']['#options'][$key] . '_';
      }
      if (empty($form_state['values']['author'])) {
        $filename .= 'authors_';
      }
      else {
        $filename .= str_replace([" ", ".", "'"], "",
          $form_state['values']['author']) . '_';
      }
      $filename .= date("Y-m-d") . '.csv';
 
      $search->downloadCsv($filename);
      break;
  }
}
/**
 * @file
 * MM Reports classes.
 */
 
/**
 * MM Reports Authors class.
 */
class MmCrossSite {
 
  /**
   * Associated array of drupal db keys and their connection statuses.
   *
   * - Format: [(string) $sitename => (bool) $connection_status] .
   *
   * @var bool[]
   */
  protected $drupalDbs;
 
  /**
   * Array containing queried report data for processing and presenting.
   *
   * @var mixed
   */
  protected $data;
 
  /**
   * Constructor for new MM Cross-Site reporting object.
   *
   * Parse connection info from `access-{sitename}.php` php files.
   *  - Database connection arrays should be defined identically to the
   *    `$databases` array in `settings.php`.
   *
   * @param string $directory
   *   Directory in which to recursively search for access-{sitename}.php files.
   *   - Default: '/var/www/access' .
   */
  public function __construct(string $directory = '/var/www/access') {
    $this->drupalDbs = array();
 
    // Generate associated array of Drupal sitenames & connection values.
    // Set each site's connection value to FALSE by default.
    $this->drupalDbs['default'] = FALSE;
 
    // Get list of Drupal database definition files.
    // Skip over forum and utility access files, and ignore the current site's
    // access file (because it is already loaded).
    $mask = '/access-(?!(forum|sandbox|adminer|' .
      ($_ENV['SITECONFIG']['sitename'] ?? ' ') . ')).*\.php/';
    $files = file_scan_directory($directory, $mask);
 
    foreach ($files as $absolute => $file) {
      // All of our access file names take the form 'access-{sitename}.php'.
      // Isolate the access file's sitename by cropping off 'access-'.
      if (is_readable($absolute)) {
        // Read the access-{sitename}.php file, check for a database
        // connection info array, and parse it.
        require $absolute;
        $sitename = substr($file->name, 7);
        $key = 'access_' . $sitename . '_databases';
        if (isset(${$key}['default']['default'])) {
          Database::addConnectionInfo($sitename, 'default', ${$key}['default']['default']);
          $this->drupalDbs[$sitename] = FALSE;
        }
      }
    }
  }
 
  /**
   * Get a list of available drupal dbs and their connection status.
   *
   * @return array
   *   [(string) $sitename => (bool) $connection_status] .
   */
  public function getDrupalDbs() {
    return $this->drupalDbs;
  }
 
  /**
   * Get report data array.
   *
   * @return array
   *   [(int) $index => (array) $result] .
   */
  public function getData() {
    return $this->data;
  }
 
  /**
   * Get a list of all bundles on one or more drupal sites.
   *
   * @return array
   *   [(string) $bundle] .
   */
  public function getAllBundles() {
    $results = array();
 
    // Loop thru each db_key that is flagged for inclusion.
    foreach ($this->drupalDbs as $db_key => $connect) {
      if ($connect) {
        db_set_active($db_key);
 
        // First, query 'node_type' table for node type bundles.
        $table1 = db_select('node_type', 'nt');
        // $table1->addExpression("CONCAT('node_', nt.type)", 'thekey');
        $table1->addField('nt', 'type', 'bundle');
        // Then, query 'taxonomy_vocabulary' table for vocabulary bundles.
        $table2 = db_select('taxonomy_vocabulary', 'tv');
        // $table2->addExpression("CONCAT('taxonomy_', tv.machine_name)", 'thekey');
        $table2->addField('tv', 'machine_name', 'bundle');
        // Union the term and node bundles.
        $query = $table1->union($table2);
 
        if (db_field_exists('file_managed', 'type')) {
          // Then, query 'file_managed' table for type bundles, if present.
          $table3 = db_select('file_managed', 'fm');
          // $table3->addExpression("CONCAT('file_', fm.type)", 'thekey');
          $table3->addField('fm,', 'type', 'bundle');
          // Union the file bundles with the node+term bundles.
          $query->union($table3);
        }
 
        if (db_table_exists('field_collection_item')) {
          // Finally, query 'field_collection_item' for field collection names.
          $table4 = db_select('field_collection_item', 'fci');
          // $table4->addExpression("CONCAT('fieldcollection_', fci.field_name)", 'thekey');
          $table4->addField('fci', 'field_name', 'bundle');
          // Union the file bundles with the node+term bundles.
          $query->union($table4);
        }
 
        $query->distinct();
 
        $results[$db_key] = $query->execute()->fetchAll();
      }
    }
    // Set the active db back to the default site db.
    db_set_active();
 
    $bundles = array();
 
    // Loop thru the results and compile a master array of the query results.
    foreach ($results as $db_key => $site_result) {
      foreach ($site_result as $row) {
        $bundles[$row->bundle] = $row->bundle;
      }
    }
    // Add a couple extra "bundles" that aren't technically bundles but are used
    // for filtering purposes.
    // 'custom' is used to categorize custom blocks.
    $bundles['custom'] = 'custom';
    // 'webform_submission' is used to categorize webform submission nodes.
    $bundles['webform_submission'] = 'webform_submission';
    // Include a blank option.
    $bundles['[blank]'] = '[blank]';
 
    // Alphabetize the array.
    asort($bundles);
 
    return $bundles;
  }
 
  /**
   * Set up connection to one or more Drupal databases to query for data.
   *
   * @param string[] $sitenames
   *   Array of sitename db keys to include for querying.
   *   - Note: The drupal db key used for the current site is 'default'.
   *   - Defaullt: [] (include all available drupal databases)
   */
  public function connect(array $sitenames = []) {
    // Loop through each $drupalDbs array key and set connected flag to TRUE
    // unless otherwise defined by $sitenames array.
    foreach (array_keys($this->drupalDbs) as $db_key) {
      if (empty($sitenames) || in_array($db_key, $sitenames)) {
        $this->drupalDbs[$db_key] = TRUE;
      }
      else {
        $this->drupalDbs[$db_key] = FALSE;
      }
    }
  }
 
  /**
   * Get list of connected DBs.
   *
   * @return string[]
   *   Array of db_keys to which $this is currently connected.
   */
  public function getConnected() {
    $list = array();
    foreach ($this->drupalDbs as $db_key => $connected) {
      if ($connected) {
        $list[] = $db_key;
      }
    }
    return $list;
  }
 
  /**
   * Build array of queries for one or more site databases.
   *
   * @param string $query
   *   Query string to execute against each health site.
   * @param array $args
   *   Associated array of arguments for the query string.
   *   - Default: [] .
   *
   * @return array
   *   [(string) $db_key => (object) $query] .
   *   - When executed, $query object will query from db with the $sitename key.
   */
  public function sqlQuerySites(string $query, array $args = []) {
    // Create array for holding query objects.
    $queries = array();
 
    foreach ($this->drupalDbs as $db_key => $connect) {
      if ($connect) {
        db_set_active($db_key);
        $queries[$db_key] = db_query($query, $args);
      }
    }
    // Set the active db back to the default site db.
    db_set_active();
 
    return $queries;
  }
 
  /**
   * Build array of entity field query results for one or more site databases.
   *
   * @param object $efquery
   *   Object of type EntityFieldQuery.
   *
   * @return array
   *   [(string) $sitename => (array) $result] .
   *   - $result is array of executed EntityFieldQuery results.
   */
  public function efQuerySites($efquery) {
    // Create array for holding entity field query objects.
    $efqueries = array();
    $results = array();
 
    foreach ($this->drupalDbs as $sitename => $connect) {
      if ($connect) {
        db_set_active($sitename);
      }
 
      $results[$sitename] = $efquery->execute();
    }
    // Set the active db back to the default site db.
    db_set_active();
 
    return $results;
  }
 
  /**
   * Convert a Drupal entity path to an entity edit link opening in a new tab.
   *
   * - If Drupal is running on environment 'master', then the link should point
   *   to Production.
   * - If the link does not contain a direct path to an editable entity, then do
   *   not convert the path into an edit link.
   *
   * @param string $entity
   *   Drupal internal entity path, or entity id.
   * @param string $db_key
   *   Sitename key for building entity path. For current site, use 'default'.
   *   - Default: 'default' .
   * @param string $type
   *   Type of string being passed. Acceptable values:
   *   - 'path' (default): internal Drupal entity path.
   *   - 'nid', tid', 'rid', 'bid': entity id type.
   * @param string $text
   *   (Optional) a custom label text for the link being created.
   * @param bool $edit
   *   TRUE: Convert the link to an "edit" link. (default)
   *   FALSE: Create a direct link to the entity.
   *
   * @return string
   *   If $entity is valid, return an html link to the entity's edit page.
   *   Else, return the un-linkified $entity value.
   */
  public static function linkify(
    string $entity,
    string $db_key = 'default',
    string $type = 'path',
    string $text = '',
    bool $edit = TRUE
  ) {
    // Initialize $link to be the plain-text path.
    $link = $entity;
 
    $text = ($type != 'path' && empty($text)) ? $entity : $text;
 
    $subdomain = $_ENV['SITECONFIG']['subdomain'] ?? '';
 
    $sitename = $db_key;
    if ($db_key == 'default') {
      $sitename = $_ENV['SITECONFIG']['sitename'] ?? '';
    }
 
    // If the db_key points to a different db, then make the link absolute.
    $absolute = ($db_key != 'default');
 
    if ($subdomain == 'master') {
      // If we are on Master environment, then make the link point to Prod.
      $absolute = TRUE;
      $subdomain = 'www';
    }
 
    if ($absolute) {
      // If the absolute flag is set, then build an absolute link path.
      $domain = $sitename . ($sitename == 'veritas' ? '' : '-') . 'health.com';
      $base_path = 'https://' . $subdomain . '.' . $domain . '/';
    }
    else {
      $base_path = '';
    }
 
    switch ($type) {
      case 'nid':
        if ($db_key != Database::getConnection()->getKey() ||
          MmCrossSite::getEntityCount('node', $entity)) {
          // If $db_key is different than the current db, then skip entity
          // validation and assume the link works.
          // Create node edit link.
 
          // The l() function fails when other databases are active, so avoid
          // using it.
          $link = '<a href="' . $base_path . 'node/' . $entity .
            ($edit ? '/edit' : '') . '" target="_blank">' .
            (empty($text) ? $entity : $text) . '</a>';
        }
        break;
 
      case 'qid':
        if ($db_key != Database::getConnection()->getKey() ||
          MmCrossSite::getEntityCount('nodequeue', $entity)) {
          // If $db_key is different than the current db, then skip entity
          // validation and assume the link works.
          // Create node edit link.
 
          // The l() function fails when other databases are active, so avoid
          // using it.
          $link = '<a href="' . $base_path . 'admin/structure/nodequeue/' . $entity .
            ($edit ? '/edit' : '') . '" target="_blank">' .
            (empty($text) ? $entity : $text) . '</a>';
        }
        break;
 
      case 'tid':
        if ($db_key != Database::getConnection()->getKey() ||
          MmCrossSite::getEntityCount('taxonomy_term', $entity)) {
          // If $db_key is different than the current db, then skip entity
          // validation and assume the link works.
          // Create term edit link.
 
          // The l() function fails when other databases are active, so avoid
          // using it.
          $link = '<a href="' . $base_path . 'taxonomy/term/' . $entity .
            ($edit ? '/edit' : '') . '" target="_blank">' .
            (empty($text) ? $entity : $text) . '</a>';
        }
        break;
 
      case 'rid':
        if ($db_key != Database::getConnection()->getKey() ||
          MmCrossSite::getEntityCount('redirect', $entity)) {
          // If $db_key is different than the current db, then skip entity
          // validation and assume the link works.
          // Create redirect edit link.
 
          // The l() function fails when other databases are active, so avoid
          // using it.
          $link = '<a href="' . $base_path .
            'admin/config/search/redirect/edit/' . $entity .
            '" target="_blank">' . (empty($text) ? $entity : $text) . '</a>';
        }
        break;
 
      case 'fid':
        if ($db_key != Database::getConnection()->getKey() ||
          MmCrossSite::getEntityCount('file', $entity)) {
          // If $db_key is different than the current db, then skip entity
          // validation and assume the link works.
          // Create file edit link.
 
          // The l() function fails when other databases are active, so avoid
          // using it.
          $link = '<a href="' . $base_path . 'file/' . $entity .
            ($edit ? '/edit' : '') . '" target="_blank">' .
            (empty($text) ? $entity : $text) . '</a>';
        }
        break;
 
      case 'bid':
        if ($db_key != Database::getConnection()->getKey()
          || MmCrossSite::getEntityCount('block', $entity)) {
          // If $db_key is different than the current db, then skip entity
          // validation and assume the link works.
          // Create block edit link.
 
          // The l() function fails when other databases are active, so avoid
          // using it.
          $link = '<a href="' . $base_path . 'admin/structure/block/manage/' .
            $entity . (is_numeric($entity) ? 'block/' : 'views/') . $entity .
            '/configure" target="_blank">' .
            (empty($text) ? $entity : $text) . '</a>';
 
        }
        break;
 
      default:
        // $entity is an internal Drupal entity path.
 
        // If $db_key is set to the currently-connected db, then check for path
        // aliasing to the node-edit page.
        if ($db_key == Database::getConnection()->getKey()) {
          // Created un-aliased path variable $normal_path.
          $normal_path = drupal_get_normal_path($entity);
          $aliased_path = drupal_get_path_alias($entity);
          $text = empty($text) ? $aliased_path : $text;
          // Break apart multi-level normal paths into an array of path components.
          $exploded = explode('/', $normal_path);
 
          // Check if $normal_path points to a valid node entity on the
          // currently-connected db.
          if (
            $exploded[0] == 'node' &&
            isset($exploded[1]) &&
            MmCrossSite::getEntityCount('node', $exploded[1])
          ) {
            $link = l($text, $base_path . $normal_path . ($edit ? '/edit' : ''),
              ['attributes' => ['target' => '_blank']]);
          }
          // Check if $normal_path points to a valid taxonomy term on the
          // currently-connected db.
          elseif (
            $exploded[0] == 'taxonomy' &&
            isset($exploded[2]) &&
            MmCrossSite::getEntityCount('taxonomy_term', $exploded[1])
          ) {
            $link = l($text, $base_path . $normal_path . ($edit ? '/edit' : ''),
              ['attributes' => ['target' => '_blank']]);
          }
        }
        else {
          // If $db_key is different from currently-connected db, then skip
          // entity validation and assume the link works.
          $link = l($text, $base_path . $entity . ($edit ? '/edit' : ''),
            ['attributes' => ['target' => '_blank']]);
        }
    }
 
    return $link;
  }
 
  /**
   * Query for a count of the number of times an entity is present in the db.
   *
   * Usually the count should be either 0 (entity not present) or 1 (entity is
   * present in one table row).
   *
   * @param string $entity_type
   *   Type of entity being queried.
   *   - Example: 'node', 'taxonomy_term', 'file' .
   * @param string $id
   *   Entity ID to query.
   */
  public static function getEntityCount(
    string $entity_type,
    string $id
  ) {
    $table = $entity_type;
    $col = substr($entity_type, 0, 1) . 'id';
 
    switch ($entity_type) {
      case 'nodequeue':
        $table .= '_queue';
        break;
 
      case 'taxonomy_term':
        $table .= '_data';
        break;
 
      case 'file':
        $table .= '_managed';
        break;
    }
 
    $num = db_select($table)
      ->condition($col, $id)
      ->countQuery()
      ->execute()
      ->fetchField();
 
    return $num;
  }
 
  /**
   * Query a drupal db for a node's title and status based on it's id.
   *
   * @param string $entity_type
   *   Type of entity being queried.
   *   - Examples: 'node', 'taxonomy_term', 'file' .
   * @param string $id
   *   Entity ID value.
   *
   * @return object
   *   Query result object.
   */
  public static function getEntityTitleAndStatus(
    string $entity_type,
    string $id
  ) {
    $entity = new stdClass();
    // Set some default values for the entity object.
    $entity->title = '';
    $entity->status = 'Missing';
 
    switch ($entity_type) {
      case 'node':
        $query = db_select('node', 'n');
        $query->fields('n', ['title', 'status'])
          ->condition('nid', $id)
          // Get the latest revision of the node being loaded.
          ->orderBy('vid', 'DESC')
          ->range(0, 1);
        $result = $query->execute()->fetch();
        if ($result) {
          $entity->title = $result->title;
          $entity->status = ($result->status ? 'Published' : 'Unpublished');
        }
        break;
 
      case 'taxonomy_term':
        $query = db_select('taxonomy_term_data', 't');
        $query->fields('t', ['name'])
          ->condition('tid', $id)
          ->orderBy('tid')
          ->range(0, 1);
        $result = $query->execute()->fetch();
        if ($result) {
          $entity->title = $result->name;
          $entity->status = 'N/A';
        }
        break;
 
      case 'file':
        $query = db_select('file_managed', 'f');
        $query->fields('f', ['status', 'filename'])
          ->condition('fid', $id)
          ->orderBy('fid')
          ->range(0, 1);
        $result = $query->execute()->fetch();
        if ($result) {
          $entity->title = $result->filename;
          $entity->status = ($result->status ? 'Enabled' : 'Disabled');
        }
        break;
    }
    return $entity;
  }
 
  /**
   * Query a drupal db for all field definitions.
   *
   * @return object[]
   *   Array query result objects.
   */
  public static function getFieldDefinitions() {
    $query = db_select('field_config', 'fc');
    $query->fields('fc', ['field_name', 'type', 'data']);
    $results = $query->execute()->fetchAllAssoc('field_name');
    foreach ($results as $field_name => $definition) {
      if ($definition->data) {
        $results[$field_name]->data = unserialize($definition->data);
      }
    }
    return $results;
  }
 
  /**
   * Query a drupal db for field instance data.
   *
   * @param string $entity_type
   *   Type of entity on which the field instance exists.
   *   - Example: 'node' or 'taxonomy_term' .
   * @param string $field_name
   *   Machine name of field being queried.
   * @param string $bundle_name
   *   Bundle machine name associated with the field instance.
   *
   * @return array
   *   Unserialized array of field instance data. FALSE if nothing found.
   */
  public static function getFieldInstanceData(
    string $entity_type,
    string $field_name,
    string $bundle_name
  ) {
    $query = db_select('field_config_instance', 'fci');
    $query->fields('fci', ['data'])
      ->condition('entity_type', $entity_type)
      ->condition('field_name', $field_name)
      ->condition('bundle', $bundle_name);
    $result = $query->execute()->fetch();
    return ($result ? unserialize($result->data) : FALSE);
  }
 
}
/**
 * @file
 * Classes for cross-site searching of text & entity references.
 */
 
/**
 * MM Reports Search class.
 */
class MmReportsSearch extends MmCrossSite {
 
  /**
   * Multi-dimensional array of tabular results formatted for presentation.
   *
   * @var array[]
   */
  protected $results;
 
  /**
   * Associated array of column keys and header titles.
   *
   * - Format: [(string) $col_key => (string) $col_title]
   *
   * @var string[]
   */
  protected $header;
 
  /**
   * Get processed results table as a multi-dimensional array.
   *
   * @return array[]
   *   [(int) $index => (array) $result] .
   */
  public function getResults() {
    return $this->results;
  }
 
  /**
   * Get table header array.
   *
   * @return string[]
   *   [(string) $col_key => (string) $col_title] .
   */
  public function getHeader() {
    return $this->header;
  }
 
  /**
   * Initialize a blank results table and header labels.
   *
   * @param string[] $header
   *   Array of column headers to be used for the results table. Default:
   *     [
   *       'Sitename' => 'Sitename',
   *       'EntityId' => 'Entity ID',
   *       'EntityType' => 'Type',
   *       'Bundle' => 'Bundle',
   *       'Status' => 'Status',
   *       'EntityTitle' => 'Entity Title',
   *       'ReferencingElements' => 'Referencing Elements',
   *     ] .
   */
  public function searchInit(array $header = []) {
    // Initialize empty results table for formatted search result data.
    $this->results = array();
 
    // Set header labels.
    if (count($header) > 0) {
      $this->header = $header;
    }
    else {
      $this->header = array(
        'Sitename' => 'Site',
        'EntityIdFormatted' => 'Entity ID',
        'EntityType' => 'Type',
        'Bundle' => 'Bundle',
        'Status' => 'Status',
        'EntityTitleFormatted' => 'Entity Title',
        'NQIDFormatted' => 'NQ',
        'NQPos' => 'Pg',
        'ReferencingElements' => 'Field(s)',
      );
    }
 
  }
 
  /**
   * Master helper function to search db tables for the given search term.
   *
   * @param string $sstring
   *   Target search string to search for in the database.
   * @param string $stype
   *   Search type. Allowable values:
   *    - 'text': Body field text search.
   *    - 'eid':  Entity ID.
   * @param string $tetype
   *   Entity type of the target id.
   *   - Only applies to eid-based searches.
   *   - Default: 'node' .
   * @param string[] $etfilter
   *   Array of entity type strings to filter by. Allowable values:
   *     'block', 'file', 'node', 'taxonomy_term', NULL (default).
   * @param string[] $bfilter
   *   Array of bundle strings to filter by.
   * @param string $stfilter
   *   Numerical value indicating entity status filtering. Allowable values:
   *     1, 0, -1, NULL (default).
   * @param string $refilter
   *   String for filtering Referencing Element Human Readable Name values.
   *
   * @return int
   *   Number of results produced from the search.
   */
  public function executeSearch(
    string $sstring,
    string $stype,
    string $tetype = 'node',
    array $etfilter = [],
    array $bfilter = [],
    string $stfilter = '',
    string $refilter = ''
  ) {
    $count = 0;
 
    switch ($stype) {
      case 'text':
        /* Commenting out because I don't think we actually want this.
         * if (ctype_digit($sstring)) {
         *   // Search Title field data for the search string.
         *   $count += $this->entityIdSearch($sstring, $etfilter, $bfilter);
         * }
         */
 
        // Search Title field data for the search string.
        $count += $this->entityTitleSearch($sstring, $etfilter, $bfilter);
 
        // Search text fields for the search string.
        $count += $this->textFieldSearch($sstring, $etfilter, $bfilter);
 
        if (count($etfilter) == 0 || in_array('taxonomy_term', $etfilter)) {
          // Search Taxonomy Description field data for the search string.
          $count += $this->termDescriptionSearch($sstring, $bfilter);
        }
 
        if (
          (count($etfilter) == 0 || in_array('node', $etfilter)) &&
          (count($bfilter) == 0  || in_array('webform_submission', $bfilter))
        ) {
 
          // Search Webform submitted data for the string.
          $count += $this->webformDataSearch($sstring);
        }
 
        if (
          (count($etfilter) == 0 || in_array('block', $etfilter)) &&
          (count($bfilter) == 0  || in_array('custom', $bfilter))
        ) {
          // Search Custom Block body fields for the string.
          $count += $this->blockCustomSearch($sstring);
        }
 
        // Search the DAM fields for the string.
        $count += $this->damFieldSearch($sstring, $etfilter, $bfilter);
 
        if (
          (count($etfilter) == 0 || in_array('redirect', $etfilter)) &&
          (count($bfilter) == 0 || in_array('[blank]', $bfilter))
        ) {
          // Search the URL Redirects for records that point to our search string.
          $count += $this->urlRedirectSearch($sstring);
        }
        break;
 
      case 'eid':
        // Search Entity IDs for the search string.
        $count += $this->entityIdSearch($sstring, $etfilter, $bfilter, $tetype);
 
        // Search Entity Reference field data for records that point to our eid.
        $count += $this->entityReferenceFieldSearch($sstring, $tetype, $etfilter, $bfilter);
 
        if (
          (count($etfilter) == 0 || in_array('redirect', $etfilter)) &&
          (count($bfilter) == 0 || in_array('[blank]', $bfilter))
        ) {
          // Search the URL Redirects for records that point to our eid.
          $count += $this->urlRedirectSearch($sstring, $tetype);
        }
        break;
    }
 
    // Prune table based on entity status and/or reference filters, if provided.
    // Ternary operator checks that there are results to filter. If $count is 0,
    // then skip the filter.
    $count -= $count ? $this->filterByStatus($stfilter) : 0;
    $count -= $count ? $this->filterByRefElemName($refilter) : 0;
 
    return $count;
  }
 
  /**
   * Search entity id values for a search string.
   *
   * Append results to array $this->data.
   *
   * @param string $sstring
   *   Target entity id to search for in the database.
   * @param string[] $etfilter
   *   Array of strings of entity types to filter by. Allowable values:
   *     'block', 'file', 'node', 'taxonomy_term', NULL (default).
   *   - Example: ['node', 'taxonomy_term'] .
   * @param string[] $bfilter
   *   Array of bundle strings to filter by.
   * @param string $tetype
   *   Entity type of the target id.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function entityIdSearch(
    string $sstring,
    array $etfilter = [],
    array $bfilter = [],
    string $tetype = ''
  ) {
    if (!ctype_digit($sstring)) {
      // Only proceed with the id search if the search string is an integer.
      return 0;
    }
 
    $count = 0;
    $db_key = Database::getConnection()->getKey();
 
    if (
      (count($etfilter) == 0 || in_array('node', $etfilter)) &&
      (empty($tetype) || $tetype == 'node')
    ) {
      // Run the search for Node titles.
      $query = db_select('node', 'n');
      $query->fields('n', [
        'nid',
        'type',
        'title',
        'status',
      ]);
      if (db_table_exists('nodequeue_nodes')) {
        $query->leftJoin('nodequeue_nodes', 'nqn', 'nqn.nid=n.nid');
        $query->fields('nqn', [
          'qid',
          'position',
        ]);
      }
      // Match only full-numerical matches.
      $query->condition('n.nid', $sstring);
      if (count($bfilter)) {
        // Filter by bundles defined in the $bfilter array.
        $query->condition('n.type', $bfilter, 'IN');
      }
      $query->orderBy('nid');
      $result = $query->execute();
 
      foreach ($result as $row) {
        $status = ($row->status ? 'Published' : 'Unpublished');
 
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->nid,
          'EntityType' => 'node',
          'Bundle' => $row->type,
          'Status' => $status,
          'EntityTitle' => $row->title,
          'NQID' => $row->qid ?? '',
          'NQPos' => $row->position ?? '',
          'ReferencingElement' => 'Node ID',
        ];
 
        $count++;
      }
    }
 
    if (
      (db_table_exists('nodequeue_nodes')) &&
      (count($etfilter) == 0 || in_array('nodequeue', $etfilter)) &&
      (count($bfilter) == 0  || in_array('[blank]', $bfilter)) &&
      (empty($tetype) || $tetype == 'nodequeue')
    ) {
      // Re-run the search for Nodequeue titles.
      $result = [];
 
      $query = db_select('nodequeue_queue', 'nqq');
      $query->fields('nqq', [
        'qid',
        'title',
      ]);
      // Match only full-numerical matches.
      $query->condition('nqq.qid', $sstring);
      $query->orderBy('qid');
      $result = $query->execute();
 
      foreach ($result as $row) {
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->qid,
          'EntityType' => 'nodequeue',
          'Bundle' => '',
          'Status' => 'N/A',
          'EntityTitle' => $row->title,
          'NQID' => $row->qid,
          'NQPos' => $row->position ?? '',
          'ReferencingElement' => 'Queue ID',
        ];
 
        $count++;
      }
    }
 
    if (
      (count($etfilter) == 0 || in_array('taxonomy_term', $etfilter)) &&
      (empty($tetype) || $tetype == 'taxonomy_term')
    ) {
      // Re-run the search for Taxonomy Term titles.
      $result = [];
 
      $query = db_select('taxonomy_term_data', 'd');
      $query->leftJoin('taxonomy_vocabulary', 'v', 'd.vid = v.vid');
      $query->fields('d', [
        'tid',
        'name',
      ]);
      $query->fields('v', [
        'machine_name',
      ]);
      // Match only full-numerical matches.
      $query->condition('d.tid', $sstring);
      if (count($bfilter)) {
        // Filter by bundles defined in the $bfilter array.
        $query->condition('v.machine_name', $bfilter, 'IN');
      }
      $query->orderBy('tid');
      $result = $query->execute();
 
      foreach ($result as $row) {
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->tid,
          'EntityType' => 'taxonomy_term',
          'Bundle' => $row->machine_name,
          'Status' => 'N/A',
          'EntityTitle' => $row->name,
          'ReferencingElement' => 'Term ID',
        ];
 
        $count++;
      }
    }
 
    if (
      (count($etfilter) == 0 || in_array('block', $etfilter)) &&
      (count($bfilter) == 0  || in_array('custom', $bfilter)) &&
      (empty($tetype) || $tetype == 'block')
    ) {
      // Re-run the search for Custom Block titles.
      $result = [];
 
      $query = db_select('block', 'b');
      $query->fields('b', [
        'delta',
        'title',
      ]);
      $query->addExpression('MAX(status)', 'status');
      $query->addExpression('MAX(region)', 'region');
      $query->groupBy('b.delta');
      // Match only full-numerical matches.
      $query->condition('b.delta', $sstring);
      $query->orderBy('delta');
      $result = $query->execute();
 
      foreach ($result as $row) {
        $status = ($row->status ? 'Enabled (' . $row->region . ')' : 'Disabled');
 
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->delta,
          'EntityType' => 'block',
          'Bundle' => 'custom',
          'Status' => $status,
          'EntityTitle' => $row->title,
          'ReferencingElement' => 'Block ID',
        ];
 
        $count++;
      }
    }
 
    if (
      (count($etfilter) == 0 || in_array('file', $etfilter)) &&
      (empty($tetype) || $tetype == 'file')
    ) {
      // Re-run the search for File titles.
      $result = [];
 
      $query = db_select('file_managed', 'f');
      $query->fields('f', [
        'fid',
        'filename',
        'status'
      ]);
      if (db_field_exists('file_managed', 'type')) {
        $query->addField('f', 'type');
        if (count($bfilter)) {
          // Filter by bundles defined in the $bfilter array.
          $query->condition('f.type', $bfilter, 'IN');
        }
      }
      // Match only full-numerical matches.
      $query->condition('f.fid', $sstring);
      $query->orderBy('fid');
      $result = $query->execute();
 
      foreach ($result as $row) {
        $status = ($row->status ? 'Enabled' : 'Disabled');
 
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->fid,
          'EntityType' => 'file',
          'Bundle' => $row->type ?? '',
          'Status' => $status,
          'EntityTitle' => $row->filename,
          'ReferencingElement' => 'File ID',
        ];
 
        $count++;
      }
    }
    return $count;
  }
 
  /**
   * Search node title & term name fields for a search string.
   *
   * Append results to array $this->data.
   *
   * @param string $sstring
   *   Target entity id to search for in the database.
   * @param string[] $etfilter
   *   Array of strings of entity types to filter by. Allowable values:
   *     'block', 'file', 'node', 'taxonomy_term', NULL (default).
   *   - Example: ['node', 'taxonomy_term'] .
   * @param string[] $bfilter
   *   Array of bundle strings to filter by.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function entityTitleSearch(
    string $sstring,
    array $etfilter = [],
    array $bfilter = []
  ) {
    $count = 0;
    $db_key = Database::getConnection()->getKey();
 
    if (count($etfilter) == 0 || in_array('node', $etfilter)) {
      // Run the search for Node titles.
      $query = db_select('node', 'n');
      $query->fields('n', [
        'nid',
        'type',
        'title',
        'status',
      ]);
      if (db_table_exists('nodequeue_nodes')) {
        $query->leftJoin('nodequeue_nodes', 'nqn', 'nqn.nid=n.nid');
        $query->fields('nqn', [
          'qid',
          'position',
        ]);
      }
      if (ctype_digit($sstring)) {
        // Search string is an integer. Match only full-numerical matches.
        $query->condition('n.title', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
      }
      else {
        // Search string contains non-numerical characters. Just match all strings
        // regardless of word boundaries.
        $query->condition('n.title', '%' . $sstring . '%', 'LIKE');
      }
      if (count($bfilter)) {
        // Filter by bundles defined in the $bfilter array.
        $query->condition('n.type', $bfilter, 'IN');
      }
      $query->orderBy('nid');
      $result = $query->execute();
 
      foreach ($result as $row) {
        $status = ($row->status ? 'Published' : 'Unpublished');
 
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->nid,
          'EntityType' => 'node',
          'Bundle' => $row->type,
          'Status' => $status,
          'EntityTitle' => $row->title,
          'NQID' => $row->qid ?? '',
          'NQPos' => $row->position ?? '',
          'ReferencingElement' => 'Title',
        ];
 
        $count++;
      }
    }
 
    if (
      (db_table_exists('nodequeue_nodes')) &&
      (count($etfilter) == 0 || in_array('nodequeue', $etfilter)) &&
      (count($bfilter) == 0  || in_array('[blank]', $bfilter))
    ) {
      // Re-run the search for Nodequeue titles.
      $result = [];
 
      $query = db_select('nodequeue_queue', 'nqq');
      $query->fields('nqq', [
        'qid',
        'title',
      ]);
      if (ctype_digit($sstring)) {
        // Search string is an integer. Match only full-numerical matches.
        $query->condition('nqq.title', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
      }
      else {
        // Search string contains non-numerical characters. Just match all strings
        // regardless of word boundaries.
        $query->condition('nqq.title', '%' . $sstring . '%', 'LIKE');
      }
      $query->orderBy('qid');
      $result = $query->execute();
 
      foreach ($result as $row) {
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->qid,
          'EntityType' => 'nodequeue',
          'Bundle' => '',
          'Status' => 'N/A',
          'EntityTitle' => $row->title,
          'NQID' => $row->qid,
          'NQPos' => $row->position ?? '',
          'ReferencingElement' => 'Title',
        ];
 
        $count++;
      }
    }
 
    if (count($etfilter) == 0 || in_array('taxonomy_term', $etfilter)) {
      // Re-run the search for Taxonomy Term titles.
      $result = [];
 
      $query = db_select('taxonomy_term_data', 'd');
      $query->leftJoin('taxonomy_vocabulary', 'v', 'd.vid = v.vid');
      $query->fields('d', [
        'tid',
        'name',
      ]);
      $query->fields('v', [
        'machine_name',
      ]);
      if (ctype_digit($sstring)) {
        // Search string is an integer. Match only full-numerical matches.
        $query->condition('d.name', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
      }
      else {
        // Search string contains non-numerical characters. Just match all strings
        // regardless of word boundaries.
        $query->condition('d.name', '%' . $sstring . '%', 'LIKE');
      }
      if (count($bfilter)) {
        // Filter by bundles defined in the $bfilter array.
        $query->condition('v.machine_name', $bfilter, 'IN');
      }
      $query->orderBy('tid');
      $result = $query->execute();
 
      foreach ($result as $row) {
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->tid,
          'EntityType' => 'taxonomy_term',
          'Bundle' => $row->machine_name,
          'Status' => 'N/A',
          'EntityTitle' => $row->name,
          'ReferencingElement' => 'Name',
        ];
 
        $count++;
      }
    }
 
    if (
      (count($etfilter) == 0 || in_array('block', $etfilter)) &&
      (count($bfilter) == 0  || in_array('custom', $bfilter))
    ) {
      // Re-run the search for Custom Block titles.
      $result = [];
 
      $query = db_select('block', 'b');
      $query->fields('b', [
        'delta',
        'title',
      ]);
      $query->addExpression('MAX(status)', 'status');
      $query->addExpression('MAX(region)', 'region');
      $query->groupBy('b.delta');
      if (ctype_digit($sstring)) {
        // Search string is an integer. Match only full-numerical matches.
        $query->condition('b.title', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
      }
      else {
        // Search string contains non-numerical characters. Just match all strings
        // regardless of word boundaries.
        $query->condition('b.title', '%' . $sstring . '%', 'LIKE');
      }
      $query->orderBy('delta');
      $result = $query->execute();
 
      foreach ($result as $row) {
        $status = ($row->status ? 'Enabled (' . $row->region . ')' : 'Disabled');
 
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->delta,
          'EntityType' => 'block',
          'Bundle' => 'custom',
          'Status' => $status,
          'EntityTitle' => $row->title,
          'ReferencingElement' => 'Block title',
        ];
 
        $count++;
      }
    }
 
    if (count($etfilter) == 0 || in_array('file', $etfilter)) {
      // Re-run the search for File titles.
      $result = [];
 
      $query = db_select('file_managed', 'f');
      $query->fields('f', [
        'fid',
        'filename',
        'status'
      ]);
      if (db_field_exists('file_managed', 'type')) {
        $query->addField('f', 'type');
        if (count($bfilter)) {
          // Filter by bundles defined in the $bfilter array.
          $query->condition('f.type', $bfilter, 'IN');
        }
      }
      if (ctype_digit($sstring)) {
        // Search string is an integer. Match only full-numerical matches.
        $query->condition('f.filename', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
      }
      else {
        // Search string contains non-numerical characters. Just match all strings
        // regardless of word boundaries.
        $query->condition('f.filename', '%' . $sstring . '%', 'LIKE');
      }
      $query->orderBy('fid');
      $result = $query->execute();
 
      foreach ($result as $row) {
        $status = ($row->status ? 'Enabled' : 'Disabled');
 
        // Add the resulting entity information as a new row in the results table.
        $this->data[$db_key][] = [
          'EntityId' => $row->fid,
          'EntityType' => 'file',
          'Bundle' => $row->type ?? '',
          'Status' => $status,
          'EntityTitle' => $row->filename,
          'ReferencingElement' => 'Name',
        ];
 
        $count++;
      }
    }
    return $count;
  }
 
  /**
   * Helper function to search text fields for a string of characters.
   *
   * @param string $sstring
   *   Target entity id to search for in the database.
   * @param string[] $etfilter
   *   Array of strings of entity types to filter by. Allowable values:
   *     'block', 'file', 'node', 'taxonomy_term', NULL (default).
   * @param string[] $bfilter
   *   Array of bundle strings to filter by.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function textFieldSearch(
    string $sstring,
    array $etfilter = [],
    array $bfilter = []
  ) {
    $count = 0;
    $db_key = Database::getConnection()->getKey();
    $result = [];
 
    $fields = $this::getFieldDefinitions();
 
    foreach ($fields as $field_name => $definition) {
      if (in_array($definition->type,
        ['text', 'text_long', 'text_with_summary'])) {
        $query = db_select('field_data_' . $field_name, 'a');
        $query->fields('a', [
          'entity_type',
          'bundle',
          'entity_id',
        ]);
        if (db_table_exists('nodequeue_nodes')) {
          $query->leftJoin('nodequeue_nodes', 'nqn', "a.entity_type='node' AND nqn.nid=a.entity_id");
          $query->fields('nqn', [
            'qid',
            'position',
          ]);
        }
        if (ctype_digit($sstring)) {
          // Search string is an integer. Match only full-numerical matches.
          $query->condition('a.' . $field_name . '_value', '[[:<:]]' . $sstring
            . '[[:>:]]', 'REGEXP');
        }
        else {
          // Search string contains non-numerical characters.
          // Just match all strings regardless of word boundaries.
          $query->condition('a.' . $field_name . '_value', '%' . $sstring . '%',
            'LIKE');
        }
        if (count($etfilter)) {
          $query->condition('a.entity_type', $etfilter, 'IN');
        }
        if (count($bfilter)) {
          // Filter by bundles defined in the $bfilter array.
          $query->condition('a.bundle', $bfilter, 'IN');
        }
        $query->orderBy('entity_id');
        $result = $query->execute();
 
        foreach ($result as $row) {
          $ref_elem = MmCrossSite::getFieldInstanceData($row->entity_type, $field_name, $row->bundle);
          $entity = MmCrossSite::getEntityTitleAndStatus($row->entity_type, $row->entity_id);
 
          // Add the resulting entity info as a new row in the results table.
          $this->data[$db_key][] = [
            'EntityId' => $row->entity_id,
            'EntityType' => $row->entity_type,
            'Bundle' => $row->bundle,
            'Status' => $entity->status,
            'EntityTitle' => $entity->title,
            'NQID' => $row->qid ?? '',
            'NQPos' => $row->position ?? '',
            'ReferencingElement' => $ref_elem['label'],
          ];
 
          $count++;
        }
      }
    }
    return $count;
  }
 
  /**
   * Helper function to search taxonomy term descriptions for a search string.
   *
   * @param string $sstring
   *   Target entity id to search for in the database.
   * @param string[] $bfilter
   *   Array of bundle strings to filter by.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function termDescriptionSearch(
    string $sstring,
    array $bfilter = []
  ) {
    $count = 0;
    $db_key = Database::getConnection()->getKey();
    $result = [];
 
    // Include "url:" in the search string to avoid false-positive results.
    $query = db_select('taxonomy_term_data', 'd');
    $query->leftJoin('taxonomy_vocabulary', 'v', 'd.vid = v.vid');
    $query->fields('d', [
      'tid',
      'name',
    ]);
    $query->fields('v', [
      'machine_name',
    ]);
 
    if (ctype_digit($sstring)) {
      // Search string is an integer. Match only full-numerical matches.
      $query->condition('d.description', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
    }
    else {
      // Search string contains non-numerical characters. Just match all strings
      // regardless of word boundaries.
      $query->condition('d.description', '%' . $sstring . '%', 'LIKE');
    }
    if (count($bfilter)) {
      // Filter by bundles defined in the $bfilter array.
      $query->condition('v.machine_name', $bfilter, 'IN');
    }
 
    $query->orderBy('tid');
    $result = $query->execute();
 
    foreach ($result as $row) {
      $entity_title = $row->name;
      $etype = 'taxonomy_term';
      $bundle = $row->machine_name;
 
      // Add the resulting entity information as a new row in the results table.
      $this->data[$db_key][] = [
        'EntityId' => $row->tid,
        'EntityType' => $etype,
        'Bundle' => $bundle,
        'Status' => 'N/A',
        'EntityTitle' => $entity_title,
        'ReferencingElement' => 'Description',
      ];
 
      $count++;
    }
    return $count;
  }
 
  /**
   * Helper function to search webform data for a search string.
   *
   * @param string $sstring
   *   Target entity id to search for in the database.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function webformDataSearch(
    string $sstring
  ) {
    $count = 0;
    $db_key = Database::getConnection()->getKey();
    $result = [];
 
    // Include "url:" in the search string to avoid false-positive results.
    $query = db_select('webform_submitted_data', 'w');
    $query->leftJoin('webform_component', 'c', 'w.nid=c.nid AND w.cid=c.cid');
    $query->fields('w', [
      'nid',
      'sid',
    ]);
    $query->fields('c', [
      'name',
    ]);
    if (ctype_digit($sstring)) {
      // Search string is an integer. Match only full-numerical matches.
      $query->condition('w.data', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
    }
    else {
      // Search string contains non-numerical characters. Just match all strings
      // regardless of word boundaries.
      $query->condition('w.data', '%' . $sstring . '%', 'LIKE');
    }
    $query->orderBy('nid')->orderBy('sid');
    $result = $query->execute();
 
    foreach ($result as $row) {
      $entity_title = $this::getEntityTitleAndStatus('node', $row->nid)->title;
      $etype = 'node';
 
      // Add the resulting entity information as a new row in the results table.
      $this->data[$db_key][] = [
        'EntityId' => $row->nid . '/submission/' . $row->sid,
        'EntityType' => $etype,
        'Bundle' => 'webform_submission',
        'Status' => 'N/A',
        'EntityTitle' => $entity_title,
        'ReferencingElement' => $row->name,
      ];
 
      $count++;
    }
    return $count;
  }
 
  /**
   * Helper function to search custom block fields for a search string.
   *
   * @param string $sstring
   *   Target entity id to search for in the database.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function blockCustomSearch(
    string $sstring
  ) {
    $count = 0;
    $db_key = Database::getConnection()->getKey();
    $result = [];
 
    // Include "url:" in the search string to avoid false-positive results.
    $query = db_select('block_custom', 'bc');
    $query->leftJoin('block', 'b', 'bc.bid=b.delta');
    $query->fields('bc', [
      'bid',
      'info',
    ]);
    $query->fields('b', [
      'title',
    ]);
    $query->addExpression('MAX(b.status)', 'status');
    $query->addExpression('MAX(b.region)', 'region');
    $query->groupBy('b.delta');
    if (ctype_digit($sstring)) {
      // Search string is an integer. Match only full-numerical matches.
      $query->condition('bc.body', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
    }
    else {
      // Search string contains non-numerical characters. Just match all strings
      // regardless of word boundaries.
      $query->condition('bc.body', '%' . $sstring . '%', 'LIKE');
    }
    $query->orderBy('bid');
    $result = $query->execute();
 
    foreach ($result as $row) {
      $status = ($row->status ? 'Enabled (' . $row->region . ')' : 'Disabled');
 
      // Add the resulting entity information as a new row in the results table.
      $this->data[$db_key][] = [
        'EntityId' => $row->bid,
        'EntityType' => 'block',
        'Bundle' => 'custom',
        'Status' => $status,
        'EntityTitle' => $row->title,
        'ReferencingElement' => 'Block body',
      ];
 
      $count++;
    }
 
    // Re-run the search for the Custom Block description field.
    $result = [];
 
    $query = db_select('block_custom', 'bc');
    $query->leftJoin('block', 'b', 'bc.bid=b.delta');
    $query->fields('bc', [
      'bid',
      'info',
    ]);
    $query->fields('b', [
      'title',
    ]);
    $query->addExpression('MAX(b.status)', 'status');
    $query->addExpression('MAX(b.region)', 'region');
    $query->groupBy('b.delta');
    if (ctype_digit($sstring)) {
      // Search string is an integer. Match only full-numerical matches.
      $query->condition('bc.info', '[[:<:]]' . $sstring . '[[:>:]]', 'REGEXP');
    }
    else {
      // Search string contains non-numerical characters. Just match all strings
      // regardless of word boundaries.
      $query->condition('bc.info', '%' . $sstring . '%', 'LIKE');
    }
    $query->orderBy('bid');
    $result = $query->execute();
 
    foreach ($result as $row) {
      $status = ($row->status ? 'Enabled (' . $row->region . ')' : 'Disabled');
 
      // Add the resulting entity information as a new row in the results table.
      $this->data[$db_key][] = [
        'EntityId' => $row->bid,
        'EntityType' => 'block',
        'Bundle' => 'custom',
        'Status' => $status,
        'EntityTitle' => $row->title,
        'ReferencingElement' => 'Block description',
      ];
 
      $count++;
    }
    return $count;
  }
 
  /**
   * Helper function to search DAM fields for a string of characters.
   *
   * @param string $sstring
   *   Target entity id to search for in the database.
   * @param string[] $etfilter
   *   Array of strings of entity types to filter by. Allowable values:
   *     'block', 'file', 'node', 'taxonomy_term', NULL (default).
   * @param string[] $bfilter
   *   Array of bundle strings to filter by.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function damFieldSearch(
    string $sstring,
    array $etfilter = [],
    array $bfilter = []
  ) {
    $count = 0;
    $db_key = Database::getConnection()->getKey();
    $result = [];
 
    $fields = $this::getFieldDefinitions();
    foreach ($fields as $field_name => $definition) {
      if (
        $definition->type == 'mm_dam_dam_resource' &&
        db_table_exists('field_data_' . $field_name)
      ) {
        $query = db_select('field_data_' . $field_name, 'a');
        $query->fields('a', [
          'entity_type',
          'bundle',
          'entity_id',
        ]);
        if (db_table_exists('nodequeue_nodes')) {
          $query->leftJoin('nodequeue_nodes', 'nqn', "a.entity_type='node' AND nqn.nid=a.entity_id");
          $query->fields('nqn', [
            'qid',
            'position',
          ]);
        }
        if (ctype_digit($sstring)) {
          // Search string is an integer. Match only full-numerical matches.
          $query->condition(db_or()
            ->condition('a.' . $field_name . '_dam_id', '[[:<:]]' . $sstring .
              '[[:>:]]', 'REGEXP')
            ->condition('a.' . $field_name . '_filename', '[[:<:]]' . $sstring .
              '[[:>:]]', 'REGEXP')
            ->condition('a.' . $field_name . '_alt_tag', '[[:<:]]' . $sstring .
              '[[:>:]]', 'REGEXP')
          );
        }
        else {
          // Search string contains non-numerical characters.
          // Just match all strings regardless of word boundaries.
          $query->condition(db_or()
            ->condition('a.' . $field_name . '_dam_id', '%' . $sstring . '%',
              'LIKE')
            ->condition('a.' . $field_name . '_filename', '%' . $sstring . '%',
              'LIKE')
            ->condition('a.' . $field_name . '_alt_tag', '%' . $sstring . '%',
              'LIKE')
          );
        }
        if (count($etfilter)) {
          $query->condition('a.entity_type', $etfilter, 'IN');
        }
        if (count($bfilter)) {
          // Filter by bundles defined in the $bfilter array.
          $query->condition('a.bundle', $bfilter, 'IN');
        }
        $query->orderBy('entity_id');
        $result = $query->execute();
 
        foreach ($result as $row) {
          $ref_elem = MmCrossSite::getFieldInstanceData($row->entity_type, $field_name, $row->bundle)['label'];
          $entity = MmCrossSite::getEntityTitleAndStatus($row->entity_type, $row->entity_id);
 
          // Add the resulting entity info as a new row in the results table.
          $this->data[$db_key][] = [
            'EntityId' => $row->entity_id,
            'EntityType' => $row->entity_type,
            'Bundle' => $row->bundle,
            'Status' => $entity->status,
            'EntityTitle' => $entity->title,
            'NQID' => $row->qid ?? '',
            'NQPos' => $row->position ?? '',
            'ReferencingElement' => $ref_elem,
          ];
 
          $count++;
        }
      }
    }
    return $count;
  }
 
  /**
   * Helper function to search an entityreference field for the given target id.
   *
   * @param string $teid
   *   Target entity id to search for in the database.
   * @param string $tetype
   *   Entity type of the target id.
   * @param string[] $etfilter
   *   Array of strings of entity types to filter by. Allowable values:
   *     'block', 'file', 'node', 'taxonomy_term', NULL (default).
   * @param string[] $bfilter
   *   Array of bundle strings to filter by.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function entityReferenceFieldSearch(
    string $teid,
    string $tetype,
    array $etfilter = [],
    array $bfilter = []
  ) {
    $count = 0;
    $db_key = Database::getConnection()->getKey();
    $result = [];
 
    // Generate list of entityreference fields that target the appropriate
    // entity type.
    $fields = $this::getFieldDefinitions();
    foreach ($fields as $field_name => $definition) {
      if ((
          $definition->type == 'entityreference' &&
          $definition->data['settings']['target_type'] == $tetype
        ) || (
          $definition->type == 'taxonomy_term_reference' &&
          'taxonomy_term' == $tetype
      )) {
        // Search the entityreference field date table for records that point to
        // our eid.
 
        $target_field_suffix = (
          $definition->type == 'taxonomy_term_reference' ? '_tid' : '_target_id'
        );
 
        // SQL query to search [data field] references.
        $query = db_select('field_data_' . $field_name, 'a');
        $query->fields('a', [
          'entity_id',
          'entity_type',
          'bundle',
          $field_name . $target_field_suffix,
        ]);
        if (db_table_exists('nodequeue_nodes')) {
          $query->leftJoin('nodequeue_nodes', 'nqn', "a.entity_type='node' AND nqn.nid=a.entity_id");
          $query->fields('nqn', [
            'qid',
            'position',
          ]);
        }
        $query->condition('a.' . $field_name . $target_field_suffix, $teid);
        if (count($etfilter)) {
          $query->condition('a.entity_type',
            array_merge($etfilter, ['field_collection_item']), 'IN');
        }
        if (count($bfilter)) {
          $query->condition('a.bundle', $bfilter, 'IN');
        }
        $query->orderBy('entity_id');
        $result = $query->execute();
 
        foreach ($result as $row) {
          if ($row->entity_type == 'field_collection_item') {
            // For field collection entities, identify and and return the entity
            // which is using the field collection in question.
            $query2 = db_select('field_data_' . $row->bundle, 'b');
            $query2->fields('b', [
              'entity_id',
              'entity_type',
              'bundle',
            ]);
            if (db_table_exists('nodequeue_nodes')) {
              $query2->leftJoin('nodequeue_nodes', 'nqn', "b.entity_type='node' AND nqn.nid=b.entity_id");
              $query2->fields('nqn', [
                'qid',
                'position',
              ]);
            }
            $query2->condition('b.' . $row->bundle . '_value', $row->entity_id);
            if (count($etfilter)) {
              $query2->condition('b.entity_type', $etfilter, 'IN');
            }
            if (count($bfilter)) {
              $query2->condition('b.bundle', $bfilter, 'IN');
            }
            $query2->orderBy('entity_id');
            $query2->range(0, 1);
            $result2 = $query2->execute()->fetchObject();
            if (!empty($result2)) {
              $entity_id = $result2->entity_id;
              $etype = $result2->entity_type;
              $bundle = $result2->bundle;
            }
          }
          else {
            $entity_id = $row->entity_id;
            $etype = $row->entity_type;
            $bundle = $row->bundle;
          }
 
          $ref_elem = MmCrossSite::getFieldInstanceData($row->entity_type, $field_name, $row->bundle)['label'];
          $entity = MmCrossSite::getEntityTitleAndStatus($etype, $entity_id);
 
          // Add the resulting entity information as a new row in the results table.
          $this->data[$db_key][] = [
            'EntityId' => $entity_id,
            'EntityType' => $etype,
            'Bundle' => $bundle,
            'Status' => $entity->status,
            'EntityTitle' => $entity->title,
            'NQID' => $row->qid ?? '',
            'NQPos' => $row->position ?? '',
            'ReferencingElement' => $ref_elem,
          ];
          $count++;
        }
      }
    }
    return $count;
  }
 
  /**
   * Helper function to search db redirect fields for the given search term.
   *
   * @param string $sstring
   *   Target entity id to search for in the database.
   * @param string $tetype
   *   Entity type of the target id.
   *
   * @return int
   *   Number of results produced from the search.
   */
  protected function urlRedirectSearch(
    string $sstring,
    string $tetype = ''
  ) {
    $count = 0;
    $db_key = Database::getConnection()->getKey();
    $result = [];
    $alias = '';
    $prefix = '';
 
    switch ($tetype) {
      case 'node':
        $alias = drupal_get_path_alias('node/' . $sstring);
        $prefix = 'node/';
        break;
 
      case 'taxonomy_term':
        $alias = drupal_get_path_alias('taxonomy/term/' . $sstring);
        $prefix = 'taxonomy/term/';
        break;
 
      case 'file':
        $alias = drupal_get_path_alias('file/' . $sstring);
        $prefix = 'file/';
        break;
 
      default:
        $alias = drupal_get_path_alias($sstring);
        $sstring = drupal_get_normal_path($sstring);
        $prefix = '';
        break;
    }
 
    // SQL query to search redirects to the given search term.
    $query = db_select('redirect', 'r');
    $query->fields('r', [
      'rid',
      'source',
      'status',
    ]);
    $query->condition(db_or()
      ->condition('r.redirect', $prefix . $sstring)
      ->condition('r.redirect', $alias));
    $result = $query->execute();
 
    foreach ($result as $row) {
      $status = ($row->status == 1 ? 'Enabled' : 'Disabled');
 
      // Add the resulting entity information as a new row in the results table.
      $this->data[$db_key][] = [
        'EntityId' => $row->rid,
        'EntityType' => 'redirect',
        'Bundle' => NULL,
        'Status' => $status,
        'EntityTitle' => $row->source,
        'ReferencingElement' => 'URL Redirect'
      ];
 
      $count++;
    }
    return $count;
  }
 
  /**
   * Compile rows of results table based on processed header and search data.
   *
   * @param string $format
   *   How to format the data table. Allowable values:
   *   - 'preview' : Format with html tags for rendering on screen. (Default)
   *   - 'download': Format for human-readable csv file.
   */
  public function compileResultsTable($format = 'preview') {
    if (count($this->data) > 0) {
      foreach ($this->data as $db_key => $data) {
        foreach ($data as $index => $row) {
          $key = $db_key . '_' . $row['EntityType'] . '_' . $row['EntityId'] . (
              empty($row['NQID']) ? '' : '_' . $row['NQID']
            );
 
          // Set default value for $eid.
          $eid = $row['EntityId'];
          $this->results[$key]['EntityId'] = $eid;
          // Set default value for $etitle.
          $etitle = $row['EntityTitle'];
          $this->results[$key]['EntityTitle'] = $etitle;
          // Set default values for nodequeue id & position.
          $nq_id = $row['NQID'] ?? '';
          $this->results[$key]['NQID'] = $nq_id;
          $nq_pos = $row['NQPos'] ?? '';
 
          if ($format == 'preview') {
            if (!empty($row['NQID'])) {
              $nq_id = MmCrossSite::linkify($row['NQID'], $db_key, 'qid');
            }
 
            if ($row['Status'] != 'Missing') {
              switch ($row['EntityType']) {
                case 'node':
                  if ($row['Bundle'] == 'webform_submission') {
                    $exploded = explode('/', $row['EntityId']);
                    if (isset($exploded[2]) && $exploded[1] == 'submission') {
                      $eid = MmCrossSite::linkify(
                        'node/' . $row['EntityId'],
                        $db_key,
                        'path',
                        $exploded[2]
                      );
                    }
                  }
                  else {
                    $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'nid');
                    $etitle = MmCrossSite::linkify($row['EntityId'], $db_key, 'nid', $row['EntityTitle'], FALSE);
                  }
                  break;
 
                case 'nodequeue':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'qid');
                  $etitle = MmCrossSite::linkify($row['EntityId'], $db_key, 'qid', $row['EntityTitle'], FALSE);
                  break;
 
                case 'taxonomy_term':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'tid');
                  $etitle = MmCrossSite::linkify($row['EntityId'], $db_key, 'tid', $row['EntityTitle'], FALSE);
                  break;
 
                case 'redirect':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'rid');
                  break;
 
                case 'block':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'bid');
                  break;
 
                case 'file':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'fid');
                  break;
              }
            }
          }
 
          // If there is already a referencing element for the entity in question,
          // then append the new referencing element to the existing entry.
          $ref_elem = (isset($this->results[$key]['ReferencingElements'])
            ? $this->results[$key]['ReferencingElements'] . ', ' : '')
            . $row['ReferencingElement'];
 
          // Loop thru the table header keys and assign corresponding data values
          // as necessary.
          foreach ($this->header as $header_key => $header_label) {
            switch ($header_key) {
              case 'Sitename':
                $this->results[$key][$header_key] = $db_key;
                break;
 
              case 'EntityIdFormatted':
                $this->results[$key][$header_key] = $eid;
                break;
 
              case 'EntityTitleFormatted':
                $this->results[$key][$header_key] = $etitle;
                break;
 
              case 'ReferencingElements':
                $this->results[$key][$header_key] = $ref_elem;
                break;
 
              case 'NQIDFormatted':
                $this->results[$key][$header_key] = $nq_id;
                break;
 
              case 'NQPos':
                $this->results[$key][$header_key] = $nq_pos;
                break;
 
              default:
                $this->results[$key][$header_key] = $row[$header_key] ?? '';
                break;
            }
          }
        }
      }
    }
  }
 
  /**
   * Sort $this->results based on array of prioritized column keys.
   *
   * - Default: Sort data by Sitename, then by EntityType, then by Bundle, then
   *   by EntityId.
   *
   * @param string[] $sort_cols
   *   Associative array of column names & sort order, beginning with
   *   primary sorting column.
   *   - Allowable sort order values:
   *     - 'ASC' : arrange values in ascending order.
   *     - 'DESC': arrange values in descending order.
   *   - Default:
   *     [
   *       'Sitename' => 'ASC',
   *       'EntityType' => 'ASC',
   *       'Bundle' => 'ASC',
   *       'EntityId' => 'ASC',
   *     ] .
   */
  public function sortResults(
    array $sort_cols = [
      'Sitename' => 'ASC',
      'NQID' => 'ASC',
      'NQPos' => 'ASC',
      'EntityType' => 'ASC',
      'Bundle' => 'ASC',
      'EntityId' => 'ASC',
    ]
  ) {
    // Create php variable string to pass to the array_multisort() method.
    $params = "";
    foreach ($sort_cols as $col => $dir) {
      $sort_dir = ($dir == "DESC" ? "SORT_DESC" : "SORT_ASC");
      if (in_array($col, ['EntityId', 'AuthorId', 'NQID', 'NQPos'])) {
        $sort_type = "SORT_NUMERIC";
      }
      else {
        $sort_type = "SORT_REGULAR";
      }
      $params .= "array_column(\$this->results, '" . $col . "'), " . $sort_dir . ", " . $sort_type . ", ";
    }
 
    // Sort by $sort_cols array from highest priority column key to lowest.
    $multisort_command = "array_multisort(" . $params . "\$this->results);";
    eval($multisort_command);
  }
 
  /**
   * Prune $this->data based on entity status.
   *
   * @param string $stfilter
   *   Numerical value indicating entity status filtering. Allowable values:
   *     1, 0, -1, NULL.
   *
   * @return int
   *   Number of results filtered from the results.
   */
  protected function filterByStatus(string $stfilter = '') {
    $count = 0;
    // Status filtering.
    if ($stfilter != '') {
      $db_key = Database::getConnection()->getKey();
      // Loop through each row of the table.
      foreach ($this->data[$db_key] as $index => $row) {
        // Sometimes the status string contains extra words. Take only the first
        // word of the string.
        $status = str_word_count($row['Status'], 1)[0];
        // If the entity status fails the status filter, then it should be removed
        // from the table.
        switch (TRUE) {
          case $stfilter == 1 && !in_array($status, ['Published', 'Enabled']):
          case $stfilter == 0 && !in_array($status, ['Unpublished', 'Disabled']):
          case $stfilter == -1 && !in_array($status, ['Missing', 'N']):
            unset($this->data[$db_key][$index]);
            $count++;
            break;
        }
      }
    }
    return $count;
  }
 
  /**
   * Prune $this->data based on Referencing Elemnent Human Readable name values.
   *
   * @param string $refilter
   *   String for filtering Referencing Element Human Readable Name values.
   *
   * @return int
   *   Number of results filtered from the results.
   */
  protected function filterByRefElemName(string $refilter = '') {
    $count = 0;
    // Referencing Element filtering.
    if (!empty($refilter)) {
      $db_key = Database::getConnection()->getKey();
      // Loop through each row of the table.
      foreach ($this->data[$db_key] as $index => $row) {
        if (stripos($row['ReferencingElement'], $refilter) === FALSE) {
          // If the referencing element value fails the filter, then the row
          // should be removed from the table.
          unset($this->data[$db_key][$index]);
          $count++;
        }
      }
    }
    return $count;
  }
 
  /**
   * Download the results table as a csv file.
   *
   * @param string $filename
   *   (Optional) File name to suggest when user downloads file as csv.
   */
  public function downloadCsv($filename = '') {
    // If no filename is given, then set it to the sitename and the date.
    if (empty($filename)) {
      $filename = ($_ENV['SITECONFIG']['sitename'] ?? 'download') . '_' .
        date("Y-m-d") . '.csv';
    }
 
    // Add necessary headers for browsers.
    drupal_add_http_header('Content-Type', 'text/csv; utf-8');
    drupal_add_http_header('Content-Disposition', 'attachment; filename=' .
      $filename);
 
    // Clean out blank lines at beginning of csv file.
    ob_clean();
 
    $handle = fopen('php://output', 'w');
    // Print header labels as the first row.
    fputcsv($handle, $this->header);
    // Add our data to the csv file.
    foreach ($this->results as $result) {
      $row = array();
      foreach (array_keys($this->header) as $col) {
        $row[$col] = $result[$col];
      }
      fputcsv($handle, $row);
    }
 
    // Close the file.
    fclose($handle);
 
    drupal_exit();
  }
 
}
/**
 * @file
 * Classes for cross-site searching of text & entity references.
 */
 
/**
 * MM Reports Author class.
 */
class MmReportsAuthor extends MmReportsSearch {
 
  /**
   * Array containing queried author data for processing and presenting.
   *
   * @var mixed
   */
  protected $authors;
 
  /**
   * Get array of all authors across all connected sites.
   *
   * - By default, return multi-dimensional array of authors split out on a
   *   per-site basis.
   *
   * @param string $property
   *   Array format to return. Options:
   *   - '' (default) - Multidimensional array of objects keyed by sitename.
   *   - 'flat' - Flat array of author objects across connected sites.
   *   - '[property]' - Flat array of object property values.
   *     - Examples: 'title', 'last_name'
   *   If TRUE, return a flattened associated array of all authors as objects,
   *   keyed as lastname_firstname.
   *
   * @return array[]
   *   [(string) $author_key => (object) $author_data] .
   */
  public function getAuthors($property = '') {
    if (empty($property)) {
      // Return default by-site array.
      return $this->authors;
    }
    elseif ($property == 'flat') {
      // Flatten array of authors based on their name keys.
      // Add a key-value pair to each array entry for listing which sites have
      // each author and at what node id each site has the author.
      $list = array();
      foreach ($this->authors as $db_key => $authors) {
        foreach ($authors as $index => $author) {
          foreach ($author as $key => $value) {
            if ($key == 'nid') {
              $list[$index]->nids[$db_key] = $value;
            }
            elseif ($key == 'status') {
              $list[$index]->statuses[$db_key] = $value;
            }
            else {
              $list[$index]->$key = $value;
            }
          }
        }
      }
      // Sort list by author key.
      ksort($list);
      return $list;
    }
    else {
      // Return a flat array of author property values as defined by $property.
      $list = array();
      foreach ($this->authors as $db_key => $authors) {
        foreach ($authors as $index => $author) {
          $list[$index] = $author->$property;
        }
      }
      // Sort list by author key.
      asort($list);
      return $list;
    }
  }
 
  /**
   * Initialize a blank results table and header labels.
   *
   * @param string[] $header
   *   Array of column headers to be used for the results table. Default:
   *     [
   *       'AuthorNameFormatted' => 'Author Name',
   *       'AuthorId' => 'Author ID',
   *       'Sitename' => 'Sitename',
   *       'EntityType' => 'Type',
   *       'Bundle' => 'Bundle',
   *       'EntityId' => 'Entity ID',
   *       'EntityTitleFormatted' => 'Entity Title',
   *       'Status' => 'Status',
   *       'Date' => 'Date Field',
   *       'ReferencingElements' => 'Referencing Elements',
   *     ] .
   */
  public function searchInit(array $header = []) {
    // Initialize empty results table for formatted search result data.
    $this->results = array();
 
    // Set header labels.
    if (count($header) > 0) {
      $this->header = $header;
    }
    else {
      $this->header = array(
        'AuthorIdFormatted' => 'NID',
        'AuthorNameFormatted' => 'Author Name',
        'AuthorStatus' => 'Status',
        'Sitename' => 'Site',
        'EntityType' => 'Type',
        'Bundle' => 'Bundle',
        'EntityIdFormatted' => 'ID',
        'EntityTitleFormatted' => 'Content Title',
        'NQIDFormatted' => 'NQ',
        'NQPos' => 'Pg',
        'NQIDs' => 'NQs',
        'Status' => 'Status',
        'Date' => 'Date',
        'ReferencingElements' => 'Field(s)',
      );
    }
  }
 
  /**
   * Master helper function to search db tables for the given search term.
   *
   * @param string $author_key
   *   Key associated with $this->authors keys for filtering by specific author.
   * @param string[] $etfilter
   *   Array of entity type strings to filter by. Allowable values:
   *     'block', 'file', 'node', 'taxonomy_term', NULL (default).
   * @param string[] $bfilter
   *   Array of bundle strings to filter by.
   * @param string $stfilter
   *   Numerical value indicating entity status filtering. Allowable values:
   *     1, 0, -1, NULL (default).
   * @param string $refilter
   *   String for filtering Referencing Element Human Readable Name values.
   * @param bool $deeper
   *   Search database for all text matches to author title and id.
   */
  public function executeAuthorReport(
    string $author_key = '',
    array $etfilter = [],
    array $bfilter = [],
    string $stfilter = '',
    string $refilter = '',
    bool $deeper = FALSE
  ) {
    // Get a list of all author IDs & names on the site.
    $this->queryAuthors($author_key);
    // If no bundle filter is defined, generate a filter array of all bundles so
    // we can unset specific bundles from the array.
    if (count($bfilter) == 0) {
      $bfilter = $this->getAllBundles();
    }
    // Skip webform submissions search.
    if (in_array('webform_submission', $bfilter)) {
      unset($bfilter['webform_submission']);
    }
 
    // The nid from site to site may vary for an author.
    // So, only run an author search-by-id on one site at a time.
    // Loop thru each site, and search each matching author for all referencing
    // elements.
    foreach ($this->authors as $db_key => $authors) {
      db_set_active($db_key);
 
      foreach ($authors as $author) {
        // Use $count to keep count of number of results for the current author.
        $count = 0;
 
        // Search for entity references to the author's nid.
        $count += $this->executeSearch(
          $author->nid,
          'eid',
          'node',
          $etfilter,
          $bfilter,
          $stfilter,
          $refilter
        );
 
        // Additionally, if a deeper search is desired, then run the text field
        // searches.
        // I had to limit the expanded searching to one author because searching
        // all authors for all things takes too many resources to complete.
        if ($deeper) {
          $count += $this->executeSearch(
            $author->title,
            'text',
            '',
            $etfilter,
            $bfilter,
            $stfilter,
            $refilter
          );
 
          // Search for taxonomy term references to the author.
          $count += $this->executeSearch(
            $author->nid,
            'text',
            '',
            $etfilter,
            $bfilter,
            $stfilter,
            $refilter
          );
        }
        elseif (count($etfilter) == 0 || in_array('taxonomy_term', $etfilter)) {
          $count += $this->executeSearch(
            $author->title,
            'text',
            '',
            ['taxonomy_term'],
            $bfilter,
            $stfilter,
            $refilter
          );
 
          // Search for taxonomy term references to the author.
          $count += $this->executeSearch(
            $author->nid,
            'text',
            '',
            ['taxonomy_term'],
            $bfilter,
            $stfilter,
            $refilter
          );
        }
 
        if ($count) {
          // Loop thru each new search result and add author & date values.
          $new_result_indexes = array_slice(array_keys($this->data[$db_key]), -1 * $count);
          foreach ($new_result_indexes as $index) {
            // Add author name, id, & status to each new search result.
            $this->data[$db_key][$index]['AuthorId'] = $author->nid;
            $this->data[$db_key][$index]['AuthorName'] = $author->title;
            $this->data[$db_key][$index]['AuthorStatus'] = $author->status;
            // Updated/End Date Field.
            $query = db_select('field_data_field_article_dates', 'd')
              ->fields('d', ['field_article_dates_value', 'field_article_dates_value2'])
              ->condition('entity_type', $this->data[$db_key][$index]['EntityType'])
              ->condition('entity_id', $this->data[$db_key][$index]['EntityId']);
            $result = $query->execute()->fetch();
            if (isset($result->field_article_dates_value)) {
              $date1 = explode("T", $result->field_article_dates_value)[0];
              $date2 = explode("T", $result->field_article_dates_value2)[0];
              $date = max($date1, $date2);
              $this->data[$db_key][$index]['Date'] = $date;
            }
            else {
              $this->data[$db_key][$index]['Date'] = '';
            }
          }
        }
      }
    }
    // Connect back to the default site db.
    db_set_active();
  }
 
  /**
   * Query each flagged db for all authors.
   *
   * Save results to array $this->authors.
   *
   * @param string $author
   *   Target author id or title to search for in the database. (optional)
   */
  public function queryAuthors(string $author = '') {
    // Run the search for Author names & IDs.
    $query = db_select('node', 'n');
    $query->fields('n', [
      'nid',
      'title',
      'status',
    ]);
    $query->condition('n.type', 'author');
    if (ctype_digit($author)) {
      // Search string is an integer. Match only full-numerical matches.
      $query->condition('n.nid', $author);
    }
    elseif (!empty($author)) {
      // Search string contains non-numerical characters. Just match all strings
      // regardless of word boundaries.
      $query->condition('n.title', '%' . $author . '%', 'LIKE');
    }
    $query->orderBy('title');
 
    $queries = $this->sqlQuerySites((string) $query, $query->arguments());
 
    // Collect the results in an associated array, key'd by db_key and then by
    // LastnameFirstname.
    $results = array();
    foreach ($queries as $key => $query) {
      $results[$key] = $query->fetchAll();
    }
    $this->authors = array();
    foreach ($results as $db_key => $authors) {
      foreach ($authors as $author) {
        $author_key = str_replace([" ", ".", "'"], "", $author->title);
        $author->status = ($author->status ? 'Published' : 'Unpublished');
        $this->authors[$db_key][$author_key] = $author;
      }
    }
  }
 
  /**
   * Compile rows of results table based on processed header and search data.
   *
   * @param string $format
   *   How to format the data table. Allowable values:
   *   - 'preview' : Format with html tags for rendering on screen. (Default)
   *   - 'download': Format for human-readable csv file.
   */
  public function compileResultsTable($format = 'preview') {
    if (count($this->data) > 0) {
      foreach ($this->data as $db_key => $data) {
        foreach ($data as $index => $row) {
          $key = $db_key . '_' . $row['AuthorId'] . '_' . $row['EntityType'] .
            '_' . $row['EntityId'] . (
              empty($row['NQID']) ? '' : '_' . $row['NQID']
            );
 
          // Set default value for $author.
          $author_name = $row['AuthorName'];
          $this->results[$key]['AuthorName'] = $author_name;
          $author_id = $row['AuthorId'];
          $this->results[$key]['AuthorId'] = $author_id;
          // Set default value for $eid.
          $eid = $row['EntityId'];
          $this->results[$key]['EntityId'] = $eid;
          // Set default value for $etitle.
          $etitle = $row['EntityTitle'];
          $this->results[$key]['EntityTitle'] = $etitle;
          // Set default values for nodequeue id & position.
          $nq_ids = $nq_id = $row['NQID'] ?? '';
          $this->results[$key]['NQID'] = $nq_id;
          $nq_pos = $row['NQPos'] ?? '';
 
          if ($format == 'preview') {
            $author_name = MmCrossSite::linkify($row['AuthorId'], $db_key, 'nid', $row['AuthorName'], FALSE);
            $author_id = MmCrossSite::linkify($row['AuthorId'], $db_key, 'nid', $row['AuthorId']);
            if (!empty($row['NQID'])) {
              $nq_id = MmCrossSite::linkify($row['NQID'], $db_key, 'qid', $row['NQID'], FALSE);
            }
 
            if ($row['Status'] != 'Missing') {
              switch ($row['EntityType']) {
                case 'node':
                  if ($row['Bundle'] == 'webform_submission') {
                    $exploded = explode('/', $row['EntityId']);
                    if (isset($exploded[2]) && $exploded[1] == 'submission') {
                      $eid = MmCrossSite::linkify(
                        'node/' . $row['EntityId'],
                        $db_key,
                        'path',
                        $exploded[2]
                      );
                    }
                  }
                  else {
                    $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'nid');
                    $etitle = MmCrossSite::linkify($row['EntityId'], $db_key, 'nid', $row['EntityTitle'], FALSE);
                  }
                  break;
 
                case 'taxonomy_term':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'tid');
                  $etitle = MmCrossSite::linkify($row['EntityId'], $db_key, 'tid', $row['EntityTitle'], FALSE);
                  break;
 
                case 'redirect':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'rid');
                  break;
 
                case 'block':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'bid');
                  break;
 
                case 'file':
                  $eid = MmCrossSite::linkify($row['EntityId'], $db_key, 'fid');
                  break;
              }
            }
          }
 
          // If there is already a referencing element for the entity in question,
          // then append the new referencing element to the existing entry.
          $ref_elem = (isset($this->results[$key]['ReferencingElements'])
            ? $this->results[$key]['ReferencingElements'] . ', ' : '')
            . $row['ReferencingElement'];
 
          // If there is already a nodequeue id for the entity in question,
          // then query see if there are any other ids associated with the same
          // node and compile a comma-separated list.
          if (!empty($row['NQID']) && $row['EntityType'] == 'node') {
            db_set_active($db_key);
            $query = db_select('nodequeue_nodes', 'nqn')
              ->fields('nqn', ['qid'])
              ->condition('nid', $row['EntityId'])
              ->orderBy('qid', 'ASC');
            $result = $query->execute()->fetchCol();
            $nq_ids = implode(', ', $result);
            db_set_active();
          }
 
 
          // Loop thru the table header keys and assign corresponding data values
          // as necessary.
          foreach ($this->header as $header_key => $header_label) {
            switch ($header_key) {
              case 'AuthorNameFormatted':
                $this->results[$key][$header_key] = $author_name;
                break;
 
              case 'AuthorIdFormatted':
                $this->results[$key][$header_key] = $author_id;
                break;
 
              case 'Sitename':
                $this->results[$key][$header_key] = $db_key;
                break;
 
              case 'EntityIdFormatted':
                $this->results[$key][$header_key] = $eid;
                break;
 
              case 'EntityTitleFormatted':
                $this->results[$key][$header_key] = $etitle;
                break;
 
              case 'ReferencingElements':
                $this->results[$key][$header_key] = $ref_elem;
                break;
 
              case 'NQIDFormatted':
                $this->results[$key][$header_key] = $nq_id;
                break;
 
              case 'NQPos':
                $this->results[$key][$header_key] = $nq_pos;
                break;
 
              case 'NQIDs':
                $this->results[$key][$header_key] = $nq_ids;
                break;
 
              default:
                $this->results[$key][$header_key] = $row[$header_key] ?? '';
                break;
            }
          }
        }
      }
    }
  }
 
}