function track_da_files_table_build in Track da files 7

Build variables which can be used to create reports table.

2 calls to track_da_files_table_build()

track_da_files_table in includes/track_da_files.admin.inc

Builds a table which shows number of displays by file.

track_da_files_table_export in includes/track_da_files.admin.inc

Exports the report table in csv.

File

includes/track_da_files.admin.inc, line 226

Administrative page callbacks for Track da files module.

Code

function track_da_files_table_build() {

  // We check avalaible optional datas.
  $track_da_files_report_datas = variable_get('track_da_files_report_datas', array(
    'total_ips',
    'average_by_ip',
    'last_display',
  ));
  $track_da_files_report_files_datas = variable_get('track_da_files_report_files_datas', array(
    'timestamp',
    'filesize',
    'filemime',
  ));

  // We prepare main report table header.
  $header = array(
    array(
      'data' => t('Filename'),
    ),
    array(
      'data' => t('Counter'),
      'field' => 'counter',
      'sort' => 'DESC',
    ),
  );

  // We put displays datas in main report table header.
  foreach ($track_da_files_report_datas as $data => $value) {
    if (!empty($value)) {
      if ($value == 'total_ips') {
        $header[] = array(
          'data' => t('Total ips'),
          'field' => $value,
          'sort' => 'DESC',
        );
      }
      elseif ($value == 'average_by_ip') {
        $header[] = array(
          'data' => t('Average by ip'),
          'field' => $value,
          'sort' => 'DESC',
        );
      }
      elseif ($value == 'last_display') {
        $header[] = array(
          'data' => t('Last display'),
          'field' => $value,
          'sort' => 'DESC',
        );
      }
    }
  }

  // We put files datas in main report table header.
  foreach ($track_da_files_report_files_datas as $data => $value) {
    if (!empty($value)) {
      if ($value == 'timestamp') {
        $header[] = array(
          'data' => t('Created'),
          'field' => $value,
          'sort' => 'DESC',
        );
      }
      elseif ($value == 'filesize') {
        $header[] = array(
          'data' => t('File size'),
          'field' => $value,
          'sort' => 'DESC',
        );
      }
      elseif ($value == 'filemime') {
        $header[] = array(
          'data' => t('File mime'),
          'field' => $value,
          'sort' => 'DESC',
        );
      }
    }
  }
  $header[] = array(
    'data' => t('File datas'),
  );

  // We prepare fields.
  $fields = array(
    'pid',
  );
  foreach ($track_da_files_report_datas as $key => $value) {
    if ($value == 'average_by_ip') {
      $average_by_ip = $value;
    }
    elseif ($value == 'total_ips') {
      $total_ips = $value;
    }
    elseif ($value == 'last_display') {
      $last_display = $value;
    }
    else {
      $fields[] = $value;
    }
  }
  $fields2 = array(
    'filename',
  );
  foreach ($track_da_files_report_files_datas as $key => $value) {
    if ($value) {
      $fields2[] = $value;
    }
  }

  // We select the datas in database.
  $query = db_select('track_da_files', 't');
  $query
    ->groupBy('p.fid');
  $query
    ->groupBy('t.pid');
  $query
    ->groupBy('f.filename');
  $query
    ->groupBy('p.path');
  $query
    ->groupBy('f.timestamp');
  $query
    ->groupBy('f.filesize');
  $query
    ->groupBy('f.filemime');
  $query
    ->addExpression('COUNT(recid)', 'counter');
  $query
    ->addExpression('COUNT(DISTINCT(ip))', 'total_ips');
  $query
    ->addExpression('MAX(time)', 'last_display');
  $query
    ->addExpression('ROUND(COUNT(recid) / COUNT(DISTINCT(ip)))', 'average_by_ip');
  $query
    ->join('track_da_files_paths', 'p', 't.pid = p.pid');
  $query
    ->join('file_managed', 'f', 'p.fid = f.fid');
  $query
    ->extend('PagerDefault')
    ->limit(20)
    ->extend('TableSort')
    ->orderByHeader($header)
    ->fields('f', $fields2)
    ->fields('t', $fields)
    ->fields('p', array(
    'fid',
    'path',
  ));
  $results = $query
    ->execute();
  $rows = array();

  // We loop into the results to build table rows for the main report table.
  foreach ($results as $key => $record) {
    foreach ($record as $key2 => $row) {
      if ($key2 == 'filename') {
        $rows[$key][$key2] = l($row, file_create_url($record->path));
      }
      elseif ($key2 == 'pid' || $key2 == 'path') {
        unset($rows[$key][$key2]);
      }
      elseif ($key2 == 'last_display' && !empty($last_display) || $key2 == 'timestamp') {
        $rows[$key][$key2] = format_date($row, 'short');
      }
      elseif ($key2 == 'filesize') {
        $rows[$key][$key2] = format_size($row);
      }
      elseif ($key2 == 'filemime' || $key2 == 'counter' || !empty($total_ips) && $key2 == 'total_ips') {
        $rows[$key][$key2] = $row;
      }
      elseif ($key2 == 'fid') {
        $rows[$key][$key2] = l(t('View more'), 'admin/reports/track_da_files/file_report/' . $record->fid . '/' . $record->pid);
      }
      elseif (!empty($average_by_ip) && $key2 == 'average_by_ip') {
        $rows[$key][$key2] = $record->average_by_ip;
      }
    }
  }

  // We reorder rows to match table headers order.
  $rows_indexes_ordered = array(
    'filename',
    'counter',
    'total_ips',
    'average_by_ip',
    'last_display',
    'timestamp',
    'filesize',
    'filemime',
    'fid',
  );
  $rows_ordered = array();
  foreach ($rows as $row => $value) {
    foreach ($value as $key => $value2) {
      $keys[] = $key;
    }
  }
  $rows_final = array();
  $variables = array();
  foreach ($rows as $row) {
    foreach ($rows_indexes_ordered as $index) {
      if (in_array($index, $keys)) {
        $rows_ordered[$index] = $row[$index];
      }
    }
    $rows_final[] = $rows_ordered;
  }
  $variables = array(
    'header' => $header,
    'rows' => $rows_final,
  );
  return $variables;
}