Fix for Node To Word Stats module, dev snapshot

View
Fix

August 17, 2010 - 12:48pm — Anonymous

<?php
// $Id$
 
/*
 * Created by Greg Harvey on 26 Jul 2010
 *
 * http://www.drupaler.co.uk
 */
 
/**
 * Helper function for returning node ID for given document
 * download path.
 * 
 * @param $path
 *  (string) The path from the accesslog table.
 * 
 * @param $type
 *  (string) The type of download, either 'word' or 'txt'.
 * 
 * @return
 *  Valid Drupal node ID or NULL if no node ID found in the path.
 */
function node_to_word_stats_nid_from_path($path, $type) {
  $nid = NULL;
  $pattern = '/' . $type . '\/([0-9]*)/';
  // Find the nid in the specified path.
  preg_match($pattern, $path, $matches);
  // Make sure we have an ID.
  if (is_numeric($matches[1])) {
    $nid = $matches[1];
  }
  return $nid;
}
 
/**
 * Implementation of hook_perm().
 */
function node_to_word_stats_perm() {
  return array('access node to word statistics', 'administer node to word statistics settings');
}
 
/**
 * Implementation of hook_menu().
 */
function node_to_word_stats_menu() {
  $items = array();
  
  $items['admin/reports/ntw-downloads'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array('all'),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array('all'),
    'access arguments' => array('access node to word statistics'),
  );
  
  $items['admin/reports/ntw-downloads/all'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array('all'),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array('all'),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_DEFAULT_LOCAL_TASK,
  );
  
  $items['admin/reports/ntw-downloads/field'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array(3, 4),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array(3, 4),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_CALLBACK,
  );
  // Load articles by user profile field value.
  $items['admin/reports/ntw-downloads/field'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array(3, 4),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array(3, 4),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_CALLBACK,
  );
  // Load articles by taxonomy term.
  $items['admin/reports/ntw-downloads/category'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array(3, 4),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array(3, 4),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_CALLBACK,
  );
  
  $field = variable_get('node_to_word_stats_grouping_profile_fid', 3);
  $field_name = db_result(db_query("SELECT title FROM {profile_fields} WHERE fid = %d", $field));
  $items['admin/reports/ntw-downloads/by-profile'] = array(
    'title' => t('Downloads by !field', array('!field' => $field_name)),
    'description' => t('Page for showing content grouped by !field', array('!field' => $field_name)),
    'page callback' => 'node_to_word_stats_by_profile_field',
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_LOCAL_TASK,
  );
  
  $items['admin/reports/ntw-downloads/%node'] = array(
    'title callback' => 'node_to_word_stats_user_list_title',
    'title arguments' => array(3),
    'description' => t('Page for viewing user lists of users who downloaded Word and text versions of a specific node.'),
    'page callback' => 'node_to_word_stats_user_list',
    'page arguments' => array(3, 4),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_CALLBACK,
  );
  
  return $items;
}
 
/**
 * Callback for setting the article list page title depending on context.
 * See the page callback function below for parameter information.
 */
function node_to_word_stats_article_list_title($filter = 'all', $data = NULL) {
  if ($filter == 'all') {
    return t('All downloads');
  }
  if ($filter == 'field' && $data) {
    drupal_set_title(t('Downloads by !value', array('!value' => $data)));
  }
  if ($filter == 'category' && $data) {
    $term = taxonomy_get_term($data);
    drupal_set_title(t('Downloads in %value', array('%value' => $term->name)));
  }
}
 
/**
 * Callback for setting the user list page title depending on context.
 */
function node_to_word_stats_user_list_title($node) {
  drupal_set_title(t('Users who downloaded %title', array('%title' => $node->title)));
}
 
/**
 * Function for handling user list pages.
 * 
 * @param $node
 *  (object) Fully formed Drupal node object.
 * 
 * @return
 *  (string) HTML page of results.
 */
function node_to_word_stats_user_list($node, $data = NULL) {
  // Set the table headers.
  $header = array(
    array(
      'data' => 'Username',
      'field' => 'u.name',
    ),
    array(
      'data' => 'Last access',
      'field' => 'a.timestamp',
      'sort' => 'desc',
    ),
    array(
      'data' => 'First name',
    ),
    array(
      'data' => 'Last name',
    ),
    array(
      'data' => 'Organisation',
    ),
    array(
      'data' => 'Telephone',
    ),
  );
  // TODO: make this an admin setting.
  $limit = variable_get('node_to_word_stats_results_per_page', 50);
  // Check to see if we need to restrict results by user profile field.
  if ($data) {
    //print $data;
    // Query the database for list of downloading users, filtered by profile field.
    $query = "SELECT DISTINCT(a.uid) as uid, u.name, a.timestamp
              FROM {accesslog} a
              INNER JOIN {users} u
              ON a.uid = u.uid
              INNER JOIN {profile_values} p
              ON p.uid = a.uid
              WHERE
              (path = CONCAT('txt/',%d) OR path = CONCAT('word/',%d))
              AND p.value = '%s' 
              GROUP BY uid"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.uid)
              FROM {accesslog} a
              INNER JOIN {users} u
              ON a.uid = u.uid
              INNER JOIN {profile_values} p
              ON p.uid = a.uid
              WHERE
              (path = CONCAT('txt/',%d) OR path = CONCAT('word/',%d))
              AND p.value = '%s'";
    $result = pager_query($query, $limit, 0, $count_query, $node->nid, $node->nid, $data);
  }
  else {
    // Query the database for list of downloading users.
    $query = "SELECT DISTINCT(a.uid) as uid, u.name, a.timestamp
              FROM {accesslog} a
              INNER JOIN {users} u
              ON a.uid = u.uid
              WHERE
              (path = CONCAT('txt/',%d) OR path = CONCAT('word/',%d)) 
              GROUP BY uid"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.uid)
              FROM {accesslog} a
              INNER JOIN {users} u
              ON a.uid = u.uid
              WHERE
              (path = CONCAT('txt/',%d) OR path = CONCAT('word/',%d))";
    $result = pager_query($query, $limit, 0, $count_query, $node->nid, $node->nid);
  }
 
  while ($row = db_fetch_array($result)) {
    // Fetch the profile data.
    $profile_query = "SELECT p.value, f.name
              FROM {profile_values} p
              INNER JOIN
              {profile_fields} as f
              ON p.fid = f.fid
              WHERE p.uid = %d";
    $profile_result = db_query($profile_query, $row['uid']);
    while ($profile_field = db_fetch_array($profile_result)) {
      $row[$profile_field['name']] = $profile_field['value'];
    }
    // Set link to company website, if available.
    if ($row['profile_website']) {
      $row['profile_org_name'] = l($row['profile_org_name'], $row['profile_website']);
    }
    // Format the date.
    $row['timestamp'] = format_date($row['timestamp'], 'small');
    // Format the user profile link.
    $row['name'] = l($row['name'], 'user/' . $row['uid']);
    // Unset the data we don't want.
    unset($row['uid']);
    unset($row['profile_org_phone']);
    unset($row['profile_mobile']);
    unset($row['profile_website']);
    $rows[] = $row;
  }
  // Back link.
  $back = '<div><a href="javascript:history.go(-1);" />Back</a></div>';
  // Return a themed table.
  return theme('table', $header, $rows) . $back . theme('pager');
}
 
/**
 * Function for handling article list pages.
 * 
 * @param $filter
 *  (string) Either all, field or category.
 *    all = full article list sorted by downloads descending.
 *    field = article list filtered by specified user profile field value.
 *      (Profile module required, field to interrogate an admin setting.)
 *    category = article list filtered by category.
 * 
 * @param $data
 *  (mixed) Either the value to pass to a profile field ($filter = 'field')
 *  or a valid Drupal term ID ($filter = 'category').
 * 
 * @return
 *  (string) HTML page of results.
 */
function node_to_word_stats_article_list($filter = 'all', $data = NULL) {
  // Set the table headers.
  $header = array(
    array(
      'data' => 'Article',
    ),
    array(
      'data' => 'Total downloads',
      'field' => 'counter',
      'sort' => 'desc',
    ),
  );
  
  // TODO: make this an admin setting.
  $limit = variable_get('node_to_word_stats_results_per_page', 50);
  
  if ($filter == 'all') {
    // Query the database to get all downloaded articles.
    $query = "SELECT a.path, COUNT(a.aid) as counter, a.title
              FROM {accesslog} a 
              WHERE (path LIKE 'txt/%' 
                OR path LIKE 'word/%')
              GROUP BY a.title"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.title) 
              FROM {accesslog} a
              WHERE (path LIKE 'txt/%' 
                OR path LIKE 'word/%')";
    $result = pager_query($query, $limit);
  }
  elseif ($filter == 'field') {
    // Query the database to get all articles downloaded by users with a
    // specified user profile value.
    $query = "SELECT a.path, COUNT(a.aid) as counter, a.title
              FROM {accesslog} a
              INNER JOIN {profile_values} p
              ON p.uid = a.uid
              AND p.fid = %d
              AND p.value = '%s'
              WHERE path LIKE 'txt/%' 
              OR path LIKE 'word/%'
              GROUP BY a.title"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.title) 
              FROM {accesslog} a
              INNER JOIN {profile_values} p
              ON p.uid = a.uid
              AND p.fid = %d
              WHERE path LIKE 'txt/%' 
              OR path LIKE 'word/%'";
    $result = pager_query($query, $limit, 0, $count_query, variable_get('node_to_word_stats_grouping_profile_fid', 3), $data);
  }
  elseif ($filter == 'category') {
    // Query the database to get all articles downloaded in a category.
    $query = "SELECT a.path, a.title, 
              (SELECT COUNT(aid) FROM accesslog WHERE path = CONCAT('txt/',t.nid) OR path = CONCAT('word/',t.nid)) as counter
              FROM {term_node} t
              INNER JOIN {accesslog} a
              ON 
              (CONCAT('txt/',t.nid) = a.path
              OR CONCAT('word/',t.nid) = a.path)
              WHERE t.tid = %d
              GROUP BY a.title"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.title) 
              FROM {term_node} t
              INNER JOIN {accesslog} a
              ON 
              (CONCAT('txt/',t.nid) = a.path
              OR CONCAT('word/',t.nid) = a.path)
              WHERE t.tid = %d";
    $result = pager_query($query, $limit, 0, $count_query, $data);
  }
  
 
  while ($row = db_fetch_array($result)) {
    // Find the nid.
    $nid = node_to_word_stats_nid_from_path($row['path'], 'txt');
    if (!$nid) {
      $nid = node_to_word_stats_nid_from_path($row['path'], 'word');
    }
    // Change the path to be to the actual node.
    $row['path'] = l($row['title'], 'node/' . $nid);
    // Add the details link.
    if ($data && $filter == 'field') {
      // Make sure we pass on the company name, if we have it.
      $row['details'] = l(t('click for details'), 'admin/reports/ntw-downloads/' . $nid . '/' . $data);
    }
    else {
      $row['details'] = l(t('click for details'), 'admin/reports/ntw-downloads/' . $nid);
    }
    unset($row['title']);
    // Key the $rows array by nid.
    $rows[$nid] = $row;
  }
  // Return a themed table.
  $output = drupal_get_form('node_to_word_stats_filters_form');
  $output .= theme('table', $header, $rows) . theme('pager');
  return $output;
}
 
/**
 * Function for collating and returning themed data to show
 * on the Reports page for Word/txt downloads by a profile
 * field using the core Profile module.
 * 
 * TODO: Separate theming out to a template/function.
 */
function node_to_word_stats_by_profile_field() {
  // Set the table headers.
  $header = array(
    array(
      'data' => 'Title',
    ),
    array(
      'data' => 'Total downloads',
      'field' => 'p.value',
      'sort' => 'asc',
    ),
  );
  // Query the database to get all organisations by download count.
  $query = "SELECT p.value as field, COUNT(a.aid) as counter
            FROM {accesslog} as a
            INNER JOIN {profile_values} as p
            ON p.uid = a.uid
            AND p.fid = %d
            WHERE path LIKE 'txt/%' 
            OR path LIKE 'word/%'
            GROUP BY field"
            . tablesort_sql($header);
  $count_query = "SELECT COUNT(DISTINCT p.value) 
            FROM {accesslog} as a
            INNER JOIN {profile_values} as p
            ON p.uid = a.uid
            AND p.fid = %d
            WHERE path LIKE 'txt/%' 
            OR path LIKE 'word/%'";
  // TODO: make this an admin setting.
  $limit = variable_get('node_to_word_stats_results_per_page', 50);
  $result = pager_query($query, $limit, 0, $count_query, variable_get('node_to_word_stats_grouping_profile_fid', 3));
  while ($row = db_fetch_array($result)) {
    $row['field'] = l($row['field'], 'admin/reports/ntw-downloads/field/' . $row['field']);
    $rows[] = $row;
  }
  // Return a themed table and pager.
  return theme('table', $header, $rows) . theme('pager');
}
 
/**
 * Function describing the filters form on the article page.
 */
function node_to_word_stats_filters_form() {
  $categories = array();
  $categories[0] = t('All');
  
  $query = "SELECT * FROM {term_data}";
  $result = db_query($query);
  while ($row = db_fetch_array($result)) {
    $categories[$row['tid']] = $row['name'];
  }
  
  $form = array();
  
  $form['categories'] = array(
    '#type' => 'select',
    '#title' => t('Filter by category'),
    '#default_value' => 0,
    '#options' => $categories,
  );
  
  return $form;
}

Submit Fix

Summary:

Tags:

Any tags you'd like to associate with your code, delimitered by commas (example: Views, CCK, Module, etc).

Show summary in full view

<?php
// $Id$

/*
 * Created by Greg Harvey on 26 Jul 2010
 *
 * http://www.drupaler.co.uk
 */

/**
 * Helper function for returning node ID for given document
 * download path.
 * 
 * @param $path
 *  (string) The path from the accesslog table.
 * 
 * @param $type
 *  (string) The type of download, either 'word' or 'txt'.
 * 
 * @return
 *  Valid Drupal node ID or NULL if no node ID found in the path.
 */
function node_to_word_stats_nid_from_path($path, $type) {
  $nid = NULL;
  $pattern = '/' . $type . '\/([0-9]*)/';
  // Find the nid in the specified path.
  preg_match($pattern, $path, $matches);
  // Make sure we have an ID.
  if (is_numeric($matches[1])) {
    $nid = $matches[1];
  }
  return $nid;
}

/**
 * Implementation of hook_perm().
 */
function node_to_word_stats_perm() {
  return array('access node to word statistics', 'administer node to word statistics settings');
}

/**
 * Implementation of hook_menu().
 */
function node_to_word_stats_menu() {
  $items = array();
  
  $items['admin/reports/ntw-downloads'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array('all'),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array('all'),
    'access arguments' => array('access node to word statistics'),
  );
  
  $items['admin/reports/ntw-downloads/all'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array('all'),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array('all'),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_DEFAULT_LOCAL_TASK,
  );
  
  $items['admin/reports/ntw-downloads/field'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array(3, 4),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array(3, 4),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_CALLBACK,
  );
  // Load articles by user profile field value.
  $items['admin/reports/ntw-downloads/field'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array(3, 4),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array(3, 4),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_CALLBACK,
  );
  // Load articles by taxonomy term.
  $items['admin/reports/ntw-downloads/category'] = array(
    'title callback' => 'node_to_word_stats_article_list_title',
    'title arguments' => array(3, 4),
    'description' => t('Pages for viewing access log data for Node To Word file downloads using data provided by the core Statistics module in a more useful way.'),
    'page callback' => 'node_to_word_stats_article_list',
    'page arguments' => array(3, 4),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_CALLBACK,
  );
  
  $field = variable_get('node_to_word_stats_grouping_profile_fid', 3);
  $field_name = db_result(db_query("SELECT title FROM {profile_fields} WHERE fid = %d", $field));
  $items['admin/reports/ntw-downloads/by-profile'] = array(
    'title' => t('Downloads by !field', array('!field' => $field_name)),
    'description' => t('Page for showing content grouped by !field', array('!field' => $field_name)),
    'page callback' => 'node_to_word_stats_by_profile_field',
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_LOCAL_TASK,
  );
  
  $items['admin/reports/ntw-downloads/%node'] = array(
    'title callback' => 'node_to_word_stats_user_list_title',
    'title arguments' => array(3),
    'description' => t('Page for viewing user lists of users who downloaded Word and text versions of a specific node.'),
    'page callback' => 'node_to_word_stats_user_list',
    'page arguments' => array(3, 4),
    'access arguments' => array('access node to word statistics'),
    'type' => MENU_CALLBACK,
  );
  
  return $items;
}

/**
 * Callback for setting the article list page title depending on context.
 * See the page callback function below for parameter information.
 */
function node_to_word_stats_article_list_title($filter = 'all', $data = NULL) {
  if ($filter == 'all') {
    return t('All downloads');
  }
  if ($filter == 'field' && $data) {
    drupal_set_title(t('Downloads by !value', array('!value' => $data)));
  }
  if ($filter == 'category' && $data) {
    $term = taxonomy_get_term($data);
    drupal_set_title(t('Downloads in %value', array('%value' => $term->name)));
  }
}

/**
 * Callback for setting the user list page title depending on context.
 */
function node_to_word_stats_user_list_title($node) {
  drupal_set_title(t('Users who downloaded %title', array('%title' => $node->title)));
}

/**
 * Function for handling user list pages.
 * 
 * @param $node
 *  (object) Fully formed Drupal node object.
 * 
 * @return
 *  (string) HTML page of results.
 */
function node_to_word_stats_user_list($node, $data = NULL) {
  // Set the table headers.
  $header = array(
    array(
      'data' => 'Username',
      'field' => 'u.name',
    ),
    array(
      'data' => 'Last access',
      'field' => 'a.timestamp',
      'sort' => 'desc',
    ),
    array(
      'data' => 'First name',
    ),
    array(
      'data' => 'Last name',
    ),
    array(
      'data' => 'Organisation',
    ),
    array(
      'data' => 'Telephone',
    ),
  );
  // TODO: make this an admin setting.
  $limit = variable_get('node_to_word_stats_results_per_page', 50);
  // Check to see if we need to restrict results by user profile field.
  if ($data) {
    //print $data;
    // Query the database for list of downloading users, filtered by profile field.
    $query = "SELECT DISTINCT(a.uid) as uid, u.name, a.timestamp
              FROM {accesslog} a
              INNER JOIN {users} u
              ON a.uid = u.uid
              INNER JOIN {profile_values} p
              ON p.uid = a.uid
              WHERE
              (path = CONCAT('txt/',%d) OR path = CONCAT('word/',%d))
              AND p.value = '%s' 
              GROUP BY uid"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.uid)
              FROM {accesslog} a
              INNER JOIN {users} u
              ON a.uid = u.uid
              INNER JOIN {profile_values} p
              ON p.uid = a.uid
              WHERE
              (path = CONCAT('txt/',%d) OR path = CONCAT('word/',%d))
              AND p.value = '%s'";
    $result = pager_query($query, $limit, 0, $count_query, $node->nid, $node->nid, $data);
  }
  else {
    // Query the database for list of downloading users.
    $query = "SELECT DISTINCT(a.uid) as uid, u.name, a.timestamp
              FROM {accesslog} a
              INNER JOIN {users} u
              ON a.uid = u.uid
              WHERE
              (path = CONCAT('txt/',%d) OR path = CONCAT('word/',%d)) 
              GROUP BY uid"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.uid)
              FROM {accesslog} a
              INNER JOIN {users} u
              ON a.uid = u.uid
              WHERE
              (path = CONCAT('txt/',%d) OR path = CONCAT('word/',%d))";
    $result = pager_query($query, $limit, 0, $count_query, $node->nid, $node->nid);
  }

while ($row = db_fetch_array($result)) {
    // Fetch the profile data.
    $profile_query = "SELECT p.value, f.name
              FROM {profile_values} p
              INNER JOIN
              {profile_fields} as f
              ON p.fid = f.fid
              WHERE p.uid = %d";
    $profile_result = db_query($profile_query, $row['uid']);
    while ($profile_field = db_fetch_array($profile_result)) {
      $row[$profile_field['name']] = $profile_field['value'];
    }
    // Set link to company website, if available.
    if ($row['profile_website']) {
      $row['profile_org_name'] = l($row['profile_org_name'], $row['profile_website']);
    }
    // Format the date.
    $row['timestamp'] = format_date($row['timestamp'], 'small');
    // Format the user profile link.
    $row['name'] = l($row['name'], 'user/' . $row['uid']);
    // Unset the data we don't want.
    unset($row['uid']);
    unset($row['profile_org_phone']);
    unset($row['profile_mobile']);
    unset($row['profile_website']);
    $rows[] = $row;
  }
  // Back link.
  $back = '<div><a href="javascript:history.go(-1);" />Back</a></div>';
  // Return a themed table.
  return theme('table', $header, $rows) . $back . theme('pager');
}

/**
 * Function for handling article list pages.
 * 
 * @param $filter
 *  (string) Either all, field or category.
 *    all = full article list sorted by downloads descending.
 *    field = article list filtered by specified user profile field value.
 *      (Profile module required, field to interrogate an admin setting.)
 *    category = article list filtered by category.
 * 
 * @param $data
 *  (mixed) Either the value to pass to a profile field ($filter = 'field')
 *  or a valid Drupal term ID ($filter = 'category').
 * 
 * @return
 *  (string) HTML page of results.
 */
function node_to_word_stats_article_list($filter = 'all', $data = NULL) {
  // Set the table headers.
  $header = array(
    array(
      'data' => 'Article',
    ),
    array(
      'data' => 'Total downloads',
      'field' => 'counter',
      'sort' => 'desc',
    ),
  );
  
  // TODO: make this an admin setting.
  $limit = variable_get('node_to_word_stats_results_per_page', 50);
  
  if ($filter == 'all') {
    // Query the database to get all downloaded articles.
    $query = "SELECT a.path, COUNT(a.aid) as counter, a.title
              FROM {accesslog} a 
              WHERE (path LIKE 'txt/%' 
                OR path LIKE 'word/%')
              GROUP BY a.title"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.title) 
              FROM {accesslog} a
              WHERE (path LIKE 'txt/%' 
                OR path LIKE 'word/%')";
    $result = pager_query($query, $limit);
  }
  elseif ($filter == 'field') {
    // Query the database to get all articles downloaded by users with a
    // specified user profile value.
    $query = "SELECT a.path, COUNT(a.aid) as counter, a.title
              FROM {accesslog} a
              INNER JOIN {profile_values} p
              ON p.uid = a.uid
              AND p.fid = %d
              AND p.value = '%s'
              WHERE path LIKE 'txt/%' 
              OR path LIKE 'word/%'
              GROUP BY a.title"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.title) 
              FROM {accesslog} a
              INNER JOIN {profile_values} p
              ON p.uid = a.uid
              AND p.fid = %d
              WHERE path LIKE 'txt/%' 
              OR path LIKE 'word/%'";
    $result = pager_query($query, $limit, 0, $count_query, variable_get('node_to_word_stats_grouping_profile_fid', 3), $data);
  }
  elseif ($filter == 'category') {
    // Query the database to get all articles downloaded in a category.
    $query = "SELECT a.path, a.title, 
              (SELECT COUNT(aid) FROM accesslog WHERE path = CONCAT('txt/',t.nid) OR path = CONCAT('word/',t.nid)) as counter
              FROM {term_node} t
              INNER JOIN {accesslog} a
              ON 
              (CONCAT('txt/',t.nid) = a.path
              OR CONCAT('word/',t.nid) = a.path)
              WHERE t.tid = %d
              GROUP BY a.title"
              . tablesort_sql($header);
    $count_query = "SELECT COUNT(DISTINCT a.title) 
              FROM {term_node} t
              INNER JOIN {accesslog} a
              ON 
              (CONCAT('txt/',t.nid) = a.path
              OR CONCAT('word/',t.nid) = a.path)
              WHERE t.tid = %d";
    $result = pager_query($query, $limit, 0, $count_query, $data);
  }

while ($row = db_fetch_array($result)) {
    // Find the nid.
    $nid = node_to_word_stats_nid_from_path($row['path'], 'txt');
    if (!$nid) {
      $nid = node_to_word_stats_nid_from_path($row['path'], 'word');
    }
    // Change the path to be to the actual node.
    $row['path'] = l($row['title'], 'node/' . $nid);
    // Add the details link.
    if ($data && $filter == 'field') {
      // Make sure we pass on the company name, if we have it.
      $row['details'] = l(t('click for details'), 'admin/reports/ntw-downloads/' . $nid . '/' . $data);
    }
    else {
      $row['details'] = l(t('click for details'), 'admin/reports/ntw-downloads/' . $nid);
    }
    unset($row['title']);
    // Key the $rows array by nid.
    $rows[$nid] = $row;
  }
  // Return a themed table.
  $output = drupal_get_form('node_to_word_stats_filters_form');
  $output .= theme('table', $header, $rows) . theme('pager');
  return $output;
}

/**
 * Function for collating and returning themed data to show
 * on the Reports page for Word/txt downloads by a profile
 * field using the core Profile module.
 * 
 * TODO: Separate theming out to a template/function.
 */
function node_to_word_stats_by_profile_field() {
  // Set the table headers.
  $header = array(
    array(
      'data' => 'Title',
    ),
    array(
      'data' => 'Total downloads',
      'field' => 'p.value',
      'sort' => 'asc',
    ),
  );
  // Query the database to get all organisations by download count.
  $query = "SELECT p.value as field, COUNT(a.aid) as counter
            FROM {accesslog} as a
            INNER JOIN {profile_values} as p
            ON p.uid = a.uid
            AND p.fid = %d
            WHERE path LIKE 'txt/%' 
            OR path LIKE 'word/%'
            GROUP BY field"
            . tablesort_sql($header);
  $count_query = "SELECT COUNT(DISTINCT p.value) 
            FROM {accesslog} as a
            INNER JOIN {profile_values} as p
            ON p.uid = a.uid
            AND p.fid = %d
            WHERE path LIKE 'txt/%' 
            OR path LIKE 'word/%'";
  // TODO: make this an admin setting.
  $limit = variable_get('node_to_word_stats_results_per_page', 50);
  $result = pager_query($query, $limit, 0, $count_query, variable_get('node_to_word_stats_grouping_profile_fid', 3));
  while ($row = db_fetch_array($result)) {
    $row['field'] = l($row['field'], 'admin/reports/ntw-downloads/field/' . $row['field']);
    $rows[] = $row;
  }
  // Return a themed table and pager.
  return theme('table', $header, $rows) . theme('pager');
}

/**
 * Function describing the filters form on the article page.
 */
function node_to_word_stats_filters_form() {
  $categories = array();
  $categories[0] = t('All');
  
  $query = "SELECT * FROM {term_data}";
  $result = db_query($query);
  while ($row = db_fetch_array($result)) {
    $categories[$row['tid']] = $row['name'];
  }
  
  $form = array();
  
  $form['categories'] = array(
    '#type' => 'select',
    '#title' => t('Filter by category'),
    '#default_value' => 0,
    '#options' => $categories,
  );
  
  return $form;
}

Syntax highlighting mode:

Select the syntax highlighting mode to use.

See Also:		Order
	Title: URL:
	Title: URL:

Any links you'd like to have associated with the post (Drupal.org issue, Wikipedia article, etc).

File attachments

Changes made to the attachments are not permanent until you save this post. The first "listed" file will be included in RSS feeds.

Attach new file:

The maximum upload size is 1 MB. Only files with the following extensions may be uploaded: jpg jpeg gif png txt doc xls pdf ppt pps odt ods odp phps.

DrupalBin

Recent Posts

User login

Tags

Fix for Node To Word Stats module, dev snapshot

Submit Fix