<?php
// $Id: pages.inc,v 1.4 2010-04-19 07:07:25 dww Exp $

/**
 * @file
 * Code necessary for the menu callbacks on pages that display usage data.
 */

/**
 * Helper function to display project usage help.
 *
 * @param $key
 *   URL argument that either identifies a project or a release node (either a
 *   node ID or a project short name), or NULL for the site-wide usage page.
 */
function _project_usage_help($key) {
  if (empty($key)) {
    return '<p>'. t('This page summarizes the usage of all projects on @site_name. For each week beginning on the given date the figures show the number of sites that reported they are using (any version of) the project. Detailed usage information for each release of a project is available by clicking the project name.', array('@site_name' => variable_get('site_name', t('this site')))) .'</p>';
  }
  elseif (is_numeric($key)) {
    $node = node_load($key);
  }
  else {
    $node = project_project_retrieve($key);
  }
  switch ($node->type) {
    case 'project_project':
      return '<p>'. t('This page provides information about the usage of the %project_title project, including summaries across all versions and details for each release. For each week beginning on the given date the figures show the number of sites that reported they are using a given version of the project. ', array('%project_title' => $node->title)) .'</p>';

    case 'project_release':
      return '<p>'. t('For each week beginning on a given date, the figures show the number of sites that reported they are using the %release_title release. ', array('%release_title' => $node->title)) .'</p>';

  }
}

/**
 * Menu handler for project URLs.
 *
 * @param $key
 *   Node id or project uri. project nids and uris get the project usage page,
 *   release nids get the release usage page, and everything else gets a not
 *   found. In addition, if a user does not have permission to view the project
 *   or release node they've requested, they get an access denied page.
 */
function project_usage_dispatch($key) {
  // Load the node the user has requested.  We want to only use
  // project_project_retrieve() if the $key parameter is not numeric because
  // project_project_retrieve() will only return a project_project node, and
  // we want to allow $node to also be a project_release node.
  if (is_numeric($key)) {
    $node = node_load($key);
  }
  else {
    $node = project_project_retrieve($key);
  }

  if (!empty($node->nid)) {
    // Make sure that the user has the permission to view this project/
    // project_release node.
    if (node_access('view', $node)) {
      if ($node->type == 'project_project') {
        return project_usage_project_page($node);
      }
      if ($node->type == 'project_release') {
        return project_usage_release_page($node);
      }
    }
    else {
      return drupal_access_denied();
    }
  }
  return drupal_not_found();
}

/**
 * Display an overview of usage for all modules.
 */
function project_usage_overview() {
  drupal_add_css(drupal_get_path('module', 'project_usage') .'/project_usage.css');
  // Load the API functions we need for manipulating dates and timestamps.
  module_load_include('inc', 'project_usage', 'includes/date_api');

  // Grab an array of active week timestamps.
  $weeks = project_usage_get_active_weeks();

  // In order to get the project usage data into a sortable table, we've gotta
  // write a pretty evil query:
  //
  // - We need to create a separate column for each week to allow sorting by
  //   usage in any week (the tablesort_sql() requires that anything you can
  //   sort on has a distinct field in the underlying query). However, some
  //   weeks may not have any usage data, forcing us to use a LEFT JOIN,
  //   rather than the more efficient INNER JOIN.
  // - The LEFT JOINs mean we have to limit the entries in {node} so that
  //   we're not including things like forum posts, hence the WHERE IN below.
  // - Each project may have multiple records in {project_usage_week_project}
  //   to track usage for API version. We need to SUM() them to get a total
  //   count forcing us to GROUP BY. Sadly, I can't explain why we need
  //   SUM(DISTINCT)... it just works(TM).
  $sql_elements = project_usage_empty_query();
  // Ignore the order_bys generated by project_usage_empty_query(), and use
  // the tablesort instead.
  unset($sql_elements['order_bys']);

  $where_args = array();
  $sql_elements['fields']['pieces'] = array('n.nid', 'n.title', 'pp.uri');
  $sql_elements['from']['pieces'][] = '{node} n ';
  $sql_elements['joins']['pieces'][] = "INNER JOIN {project_projects} pp ON n.nid = pp.nid";
  $sql_elements['wheres']['pieces'] = array('n.nid IN (SELECT nid FROM {project_usage_week_project}) AND n.status = %d');
  $where_args[] = 1;  // n.status = 1
  $sql_elements['group_bys']['pieces'] = array('n.nid', 'n.title');
  $headers = array(
    t('#'),
    array('field' => 'n.title', 'data' => t('Project')),
  );

  $joins_args = array();
  foreach ($weeks as $i => $week) {
    // Note that "{$i}" in these query snippets are used to add a week integer
    // to the table and field aliases so we can uniquely identify each column
    // for sorting purposes.  These are not literals in the query, we need
    // these aliases to be unique via this PHP string operation before we even
    // build the query.
    $sql_elements['fields']['pieces'][] = "SUM(DISTINCT p{$i}.count) AS week{$i}";
    $sql_elements['joins']['pieces'][] = "LEFT JOIN {project_usage_week_project} p{$i} ON n.nid = p{$i}.nid AND p{$i}.timestamp = %d";
    $joins_args[] = $week;

    $header = array(
      'field' => "week{$i}",
      'data' => format_date($week, 'custom', variable_get('project_usage_date_short', PROJECT_USAGE_DATE_SHORT), 0),
      'class' => 'project-usage-numbers'
    );
    if ($i == 0) {
      $header['sort'] = 'desc';
    }
    $headers[] = $header;
  }

  // Check for a cached page. The cache id needs to take into account the sort
  // column and order.
  $sort = tablesort_init($headers);
  $cid = 'overview:'. $sort['sql'] .':'. $sort['sort'];
  if (project_can_cache() && $cached = cache_get($cid, 'cache_project_usage')) {
    return $cached->data;
  }

  $args = array_merge($joins_args, $where_args);
  $result = db_query(project_usage_build_query($sql_elements) . tablesort_sql($headers), $args);

  $number = 1;
  while ($line = db_fetch_array($result)) {
    $row = array(
      array('data' => $number++),
      array('data' => l($line['title'], 'project/usage/'. $line['uri'])),
    );
    foreach ($weeks as $i => $week) {
      $row[] = array('data' => number_format($line["week{$i}"]), 'class' => 'project-usage-numbers');
    }
    $rows[] = $row;
  }
  $output = theme('table', $headers, $rows, array('id' => 'project-usage-all-projects'));

  // Cache the completed page.
  if (project_can_cache()) {
    cache_set($cid, $output, 'cache_project_usage', project_usage_cache_time());
  }

  return $output;
}

/**
 * Display the usage history of a project node.
 */
function project_usage_project_page($node) {
  drupal_add_css(drupal_get_path('module', 'project_usage') .'/project_usage.css');
  // Load the API functions we need for manipulating dates and timestamps.
  module_load_include('inc', 'project_usage', 'includes/date_api');

  $breadcrumb = array(
    l(t('Usage'), 'project/usage'),
  );
  project_project_set_breadcrumb(NULL, $breadcrumb);
  drupal_set_title(t('Usage statistics for %project', array('%project' => $node->title)));

  // In order to keep the database load down we need to cache these pages.
  // Because the release usage table is sortable, the cache id needs to take
  // into account the sort parameters. The easiest way to ensure we have valid
  // sorting parameters is to build the table headers and let the tablesort
  // functions do it. This means we end up doing most of the work to build the
  // page's second table early on. We might as well finish the job, then build
  // the other table and output them in the correct order.

  // Grab an array of active week timestamps.
  $weeks = project_usage_get_active_weeks();

  $releases = project_release_get_releases($node, FALSE);

  // If there are no releases for this project, we can skip the rest
  // of this function.
  if (empty($releases)) {
    return theme('project_usage_project_page', $node);
  }

  // Build a table showing this week's usage for each release. In order to get
  // the release usage data into a sortable table, we need another evil query:
  // - We need to create a separate column for each week to allow sorting by
  //   usage in any week (the tablesort_sql() requires that anything you can
  //   sort on has a distinct field in the underlying query). However, some
  //   weeks may not have any usage data, forcing us to use a LEFT JOIN,
  //   rather than the more efficient INNER JOIN.
  // - We need to create a column for each week but some weeks may not have any
  //   usage data, forcing us to use a LEFT JOIN, rather than the more
  //   efficient INNER JOIN.
  // - The LEFT JOINs mean we have to limit the entries in {node} so that we're
  //   not including things like forum posts, hence the WHERE IN below.
  $sql_elements = project_usage_empty_query();
  // Ignore the order_bys generated by project_usage_empty_query(), and use
  // the tablesort instead.
  unset($sql_elements['order_bys']);

  $sql_elements['fields']['pieces'] = array('n.nid');
  $sql_elements['from']['pieces'][] = '{node} n ';
  $sql_elements['wheres']['pieces'] = array('n.nid IN ('. implode(', ', array_fill(0, count($releases), '%d')) .') AND n.status = %d');
  $where_args = array_keys($releases);
  $where_args[] = 1;  // n.status = 1

  $release_header = array(array('field' => 'n.title', 'data' => t('Release'), 'sort' => 'desc'));

  $joins_args = array();
  foreach ($weeks as $i => $week) {
    // Note that "{$i}" in these query snippets are used to add a week integer
    // to the table and field aliases so we can uniquely identify each column
    // for sorting purposes.  These are not literals in the query, we need
    // these aliases to be unique via this PHP string operation before we even
    // build the query.
    $sql_elements['fields']['pieces'][] = "p{$i}.count AS week{$i}";
    $sql_elements['joins']['pieces'][] = "LEFT JOIN {project_usage_week_release} p{$i} ON n.nid = p{$i}.nid AND p{$i}.timestamp = %d";
    $joins_args[] = $week;

    $release_header[] = array(
      'field' => "week{$i}",
      'data' => format_date($week, 'custom', variable_get('project_usage_date_short', PROJECT_USAGE_DATE_SHORT), 0),
      'class' => 'project-usage-numbers',
    );
  }

  // Check for a cached page. The cache id needs to take into account the sort
  // column and order.
  $sort = tablesort_init($release_header);
  $cid = 'project:'. $node->nid .':'. $sort['sql'] .':'. $sort['sort'];
  if ($cached = cache_get($cid, 'cache_project_usage')) {
    return $cached->data;
  }

  $args = array_merge($joins_args, $where_args);
  $result = db_query(project_usage_build_query($sql_elements) . tablesort_sql($release_header), $args);

  $release_rows = array();
  while ($line = db_fetch_array($result)) {
    $sum = 0;
    $row = array(array('data' => l($releases[$line['nid']], 'project/usage/'. $line['nid'])));
    foreach ($weeks as $i => $week) {
      $sum += $line["week{$i}"];
      $row[] = array('data' => number_format($line["week{$i}"]), 'class' => 'project-usage-numbers');
    }
    // Skip any release with no usage.
    if ($sum) {
      $release_rows[] = $row;
    }
  }

  // Build a table of the weekly usage data with a column for each API version.
  // Get an array of the weeks going back as far as we have data...
  $oldest = db_result(db_query("SELECT MIN(puwp.timestamp) FROM {project_usage_week_project} puwp WHERE puwp.nid = %d", $node->nid));
  if ($oldest === NULL) {
    $weeks = array();
  }
  else {
    $weeks = project_usage_get_weeks_since($oldest);
    // ...ignore the current week, since we won't have usage data for that and
    // reverse the order so it's newest to oldest.
    array_pop($weeks);
    $weeks = array_reverse($weeks);
  }

  // The number of columns varies depending on how many different API versions
  // are in use. Set up the header and a blank, template row, based on the
  // number of distinct terms in use. This *could* be done with LEFT JOINs,
  // but it ends up being a more expensive query and harder to read.
  $project_header = array(0 => array('data' => t('Week')));
  $blank_row = array(0 => array('data' => ''));
  $result = db_query("SELECT DISTINCT td.tid, td.name FROM {project_usage_week_project} p INNER JOIN {term_data} td ON p.tid = td.tid WHERE p.nid = %d ORDER BY td.weight DESC, td.name", $node->nid);
  while ($row = db_fetch_object($result)) {
    $project_header[$row->tid] = array('data' => check_plain($row->name), 'class' => 'project-usage-numbers');
    $blank_row[$row->tid] = array('data' => 0, 'class' => 'project-usage-numbers');
  }
  $project_header['total'] = array('data' => t('Total'), 'class' => 'project-usage-numbers');
  $blank_row['total'] = array('data' => 0, 'class' => 'project-usage-numbers');

  // Now create a blank table with a row for each week and formatted date in
  // the first column...
  $project_rows = array();
  foreach ($weeks as $week) {
    $project_rows[$week] = $blank_row;
    $project_rows[$week][0]['data'] = format_date($week, 'custom', variable_get('project_usage_date_long', PROJECT_USAGE_DATE_LONG), 0);
  }

  // ...then fill it in with our data.
  $result = db_query("SELECT timestamp, tid, count FROM {project_usage_week_project} WHERE nid = %d", $node->nid);
  while ($row = db_fetch_object($result)) {
    $project_rows[$row->timestamp][$row->tid]['data'] = $row->count;
  }

  // ...and add the weekly totals across all API versions
  foreach ($project_rows as $timestamp => $row) {
    $total = 0;
    foreach ($row as $tid => $data) {
      if (!empty($tid) && !empty($data['data'])) {
        $total += $data['data'];
        $project_rows[$timestamp][$tid]['data'] = number_format($data['data']);
      }
    }
    $project_rows[$timestamp]['total']['data'] = number_format($total);
  }

  $output = theme('project_usage_project_page', $node, $release_header, $release_rows, $project_header, $project_rows);

  // Cache the completed page.
  if (project_can_cache()) {
    cache_set($cid, $output, 'cache_project_usage', project_usage_cache_time());
  }

  return $output;
}

/**
 * Display the usage history of a release node.
 */
function project_usage_release_page($node) {
  drupal_add_css(drupal_get_path('module', 'project_usage') .'/project_usage.css');

  // Load the API functions we need for manipulating dates and timestamps.
  module_load_include('inc', 'project_usage', 'includes/date_api');

  $project = node_load($node->project_release['pid']);
  $breadcrumb = array(
    l(t('Usage'), 'project/usage'),
    l($project->title, 'project/usage/'. $project->nid),
  );
  project_project_set_breadcrumb(NULL, $breadcrumb);
  drupal_set_title(t('Usage statistics for %release', array('%release' => $node->title)));

  // Check for a cached page.
  $cid = "release:{$node->nid}";
  if (project_can_cache() && $cached = cache_get($cid, 'cache_project_usage')) {
    return $cached->data;
  }

  // Table displaying the usage back through time.
  $header = array(
    array('data' => t('Week starting')),
    array('data' => t('Count'), 'class' => 'project-usage-numbers'),
  );
  $rows = array();
  $query = db_query("SELECT timestamp, count FROM {project_usage_week_release} WHERE nid = %d ORDER BY timestamp DESC", $node->nid);
  while ($row = db_fetch_object($query)) {
    $rows[] = array(
      array('data' => format_date($row->timestamp, 'custom', variable_get('project_usage_date_long', PROJECT_USAGE_DATE_LONG), 0)),
      array('data' => number_format($row->count), 'class' => 'project-usage-numbers'),
    );
  }
  $output = theme('project_usage_release_page', $project, $node, $header, $rows);

  // Cache the completed page.
  if (project_can_cache()) {
    cache_set($cid, $output, 'cache_project_usage', project_usage_cache_time());
  }

  return $output;
}

/**
 * Theme the output of project/usage/<project> page.
 *
 * @param $project
 *   A fully loaded $node object for a project.
 * @param $release_header
 *   A table header for the release usage table.
 * @param $release_rows
 *   Table rows for the release usage table.
 * @param $project_header
 *   A table header for the weekly project usage table.
 * @param $project_rows
 *   Table rows for the weekly project usage table.
 */
function theme_project_usage_project_page($project, $release_header = NULL, $release_rows = NULL, $project_header = NULL, $project_rows = NULL) {
  $output = theme('project_usage_header_links', $project);

  if (empty($release_rows)) {
    // There are no published releases for a project that the user has access
    // to view.
    $output .= '<p>'. t('There is no usage information for any release of this project.') .'</p>';
    return $output;
  }

  $output .= '<h3>'. t('Weekly project usage') .'</h3>';
  $output .= theme('project_usage_chart_by_release', t('Weekly @project usage by API version', array('@project' => $project->title)), $project_header, $project_rows);
  $output .= theme('table', $project_header, $project_rows, array('id' => 'project-usage-project-api'));
  $output .= '<h3>'. t('Recent release usage') .'</h3>';
  $output .= theme('table', $release_header, $release_rows, array('id' => 'project-usage-project-releases'));
  return $output;
}

/**
 * Theme the output of the project/usage/<release nid> page.
 *
 * @param $project
 *   A fully loaded $node object for a project.
 * @param $release
 *   A fully loaded $node object for a release.
 * @param $header
 *   A table header for the release usage table.
 * @param $rows
 *   Table rows for the release usage table.
 */
function theme_project_usage_release_page($project, $release, $header, $rows) {
  $output = theme('project_usage_header_links', $project, $release);

  // If there is no usage information for a release, don't just
  // display an empty usage table.
  if (empty($rows)) {
    $output .= '<p>' . t('There is no usage information for this release.') . '</p>';
    return $output;
  }

  $output .= theme('project_usage_chart_by_release', t('Weekly @release usage', array('@release' => $release->title)), $header, $rows);
  $output .= theme('table', $header, $rows, array('id' => 'project-usage-release'));
  return $output;
}

/**
 * Create list of links at the top of a usage statistics page.
 *
 * @param $project
 *   A fully loaded $node object for a project.
 * @param $release
 *   If the current statistics page is for a release, the fully loaded $node
 *   object for that release.
 *
 * @return
 *   Themed HTML of a list of links for the top of a statistics page.
 */
function theme_project_usage_header_links($project, $release = NULL) {
  $links = array();
  $links[] = l(t('%project_name project page', array('%project_name' => $project->title)), 'node/'. $project->nid, array('html' => TRUE));
  if (!empty($release)) {
    $links[] = l(t('%release_name release page', array('%release_name' => $release->title)), 'node/' . $release->nid, array('html' => TRUE));
    $links[] = l(t('All %project_name usage statistics', array('%project_name' => $project->title)), 'project/usage/' . $project->project['uri'], array('html' => TRUE));
  }
  $links[] = l(t('Usage statistics for all projects'), 'project/usage');
  return theme('item_list', $links);
}

/**
 * Convert the usage table data into a Google Chart image.
 *
 * First column should be the weeks, each subsequent column should be a data
 * series.
 *
 * @param $header
 *   A table header for the weekly usage table.
 * @param $rows
 *   Table rows for the weekly usage table.
 * @return
 *   An HTML IMG element, or empty string if there is an error such as
 *   insufficent data.
 */
function theme_project_usage_chart_by_release($title, $header, $rows) {
  // Make sure we have enough data to make a useful chart.
  if (count($rows) < 2 || count($header) < 2) {
    return '';
  }

  // Reverse the order of the rows so it's oldest to newest.
  $rows = array_reverse($rows);

  // Pull the API versions from the table header for use as a legend. Since the
  // table is keyed strangely, make note of which tid is in which order so we
  // can efficiently iterate over the columns.
  $legend = array();
  $mapping = array();
  foreach ($header as $tid => $cell) {
    $legend[] = $cell['data'];
    $mapping[] = $tid;
  }
  // Drop the date column from the legend and mapping since it's the other axis.
  unset($legend[0]);
  unset($mapping[0]);

  // Rotate the table so each series is in a row in the array and grab the
  // dates for use as axis labels.
  $series = array();
  $date_axis = array();
  foreach (array_values($rows) as $i => $row) {
    $date_axis[$i] = $row[0]['data'];
    foreach ($mapping as $j => $tid) {
      // FIXME: The table values have commas in them from number_format(). We
      // need to remove them because we'll use commas to separate the values
      // in the URL string. It might be better to pass in clean number values
      // and format them here rather than have to uncook them.
      $series[$j][$i] = (int) str_replace(',', '', $row[$tid]['data']);
    }
  }

  // Determine the range of the data.
  $min = $max = 0;
  foreach ($series as $values) {
    $max = max($max, max($values));
  }

  // Round the max up to the next decimal place (3->10, 19->20, 8703->9000) so
  // that the labels have round numbers and the entire range is visible. We're
  // forced to build the number as a string because PHP's round() function
  // can round down (3->0, not 3->10) and it returns floats that loose
  // precision (causing 90,000 to display as 89,999.99). We pull off the first
  // digit, add 1 to that, and then pad the rest with zeros.
  $zeros = strlen($max) - 1;
  $max = ($zeros > 0) ? (substr($max, 0, 1) + 1 . str_repeat('0', $zeros)) : 10;
  $value_axis = range($min, $max, '1'. str_repeat('0', $zeros));

  $data = array();

  // Use Google's extended encoding to keep the URLs under 2048 characters.
  $encoding_map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-.';
  $encoding_length = strlen($encoding_map);
  $encoding_max = $encoding_length * $encoding_length;
  foreach ($series as $i => $values) {
    $data[$i] = '';
    foreach ($values as $value) {
      $scaled = floor($encoding_max * $value / $max);
      if ($scaled > $encoding_max - 1) {
        $data[$i] .= '..';
      }
      else if ($scaled < 0) {
        $data[$i] .= '__';
      }
      else {
        // Calculate first and second digits and add them to the output.
        $quotient = floor($scaled / $encoding_length);
        $remainder = $scaled - ($encoding_length * $quotient);
        $data[$i] .= $encoding_map[$quotient] . $encoding_map[$remainder];
      }
    }
  }

  // Might need more colors than this.
  $colors = array('EDAA00', '0062A0', 'A17300', 'ED8200', '38B4BA', '215D6E');

  // The key values in this array are dictated by the Google Charts API:
  // http://code.google.com/apis/chart/
  $args = array(
    // Chart title.
    'chtt' => check_plain($title),
    // Dimensions as width x height in pixels.
    'chs' => '600x200',
    // Chart type
    'cht' => 'lc',
    // Pick some colors.
    'chco' => implode(',', array_slice($colors, 0, count($series))),
    'chd' => 'e:' . implode(',', $data),
    // Set the range of the chart
    'chds' => implode(',', array_fill(0, count($series), $min .','. $max)),
     // Legend is the header titles after excluding the date.
    'chdl' => implode('|', $legend),
  );
  project_usage_chart_axis_labels($args, array(
    'x' => project_usage_chart_label_subset($date_axis, 5),
    'y' => array_map('number_format', project_usage_chart_label_subset($value_axis, 5)),
  ));

  return theme('project_usage_chart', $args);
}

/**
 * Extract a subset of labels at regular intervals from a set.
 *
 * @param $labels
 *   Array of values to choose from.
 * @param $n
 *   Number of segments in the set. This number should divide $labels with no
 *   remander.
 */
function project_usage_chart_label_subset($labels, $n) {
  $subset = array();
  $count = count($labels) - 1;
  // We can't give them back more labels that we're given.
  $n = min($count, $n - 1);
  for ($i = 0; $i <= $n; $i++) {
    $subset[] = $labels[(int) ($count * ($i / $n))];
  }
  return $subset;
}

/**
 * Add axis labels to the chart arguments.
 *
 * @param $args
 *   The array where the chart is being built.
 * @param $labels
 *   Array keyed by axis (x, t, y, r) the value is an array of labels for that
 *   axis.
 * @see http://code.google.com/apis/chart/labels.html#multiple_axes_labels
 */
function project_usage_chart_axis_labels(&$args, $labels) {
  $keys = array_keys($labels);
  $args['chxt'] = implode(',', $keys);

  $l = array();
  foreach ($keys as $i => $key) {
    $l[$i] = $i .':|' . implode('|', $labels[$key]);
  }
  $args['chxl'] = implode('|', $l);
}

/**
 * Convert the array of Google Chart paramters into an image URL.
 *
 * @param $args
 *   Array of key, value pairs to turn into a Google Charts image.
 * @return
 *   HTML image element.
 */
function theme_project_usage_chart($args) {
  $params = array();
  foreach ($args as $key => $value) {
    $params[] = $key .'='. $value;
  }
  // If the chart has a title use that for the image's alt and title values.
  $title = empty($args['chtt']) ? '' : $args['chtt'];
  return theme('image', 'http://chart.apis.google.com/chart?'. implode('&', $params), $title, $title, NULL, FALSE);
}

/**
 * Begin crap old project code.
 *
 * This is old project module code that project_usage is still leveraging.
 * At some point we need to rip this out and do something better, perhaps
 * when core gets its own query builder?
 */

/**
 * Construct an empty query for project_usage_build_query().
 *
 * Set the default elements that will be used to construct the SQL statement.
 */
function project_usage_empty_query() {
  return array(
    'fields' => array(
      'prefix' => 'SELECT ',
      'glue' => ', ',
      'pieces' => array(),
    ),
    'from' => array(
      'prefix' => ' FROM ',
      'glue' => NULL,
      'pieces' => array(''),
    ),
    'joins' => array(
      'prefix' => '',
      'glue' => ' ',
      'pieces' => array(),
    ),
    'wheres' => array(
      'prefix' => ' WHERE ',
      'glue' => ' AND ',
      'pieces' => array(),
    ),
    'group_bys' => array(
      'prefix' => ' GROUP BY ',
      'glue' => ', ',
      'pieces' => array(),
    ),
    'order_bys' => array(
      'prefix' => ' ORDER BY ',
      'glue' => ', ',
      'pieces' => array(),
    ),
    'parameters' => array(
      'prefix' => NULL,
      'glue' => NULL,
      'pieces' => array(),
    )
  );
}

/**
 * Build a SQL statment from a structured array.
 *
 * @param $sql_elements
 *   An array with the following keys:
 *     'fields', 'from', 'joins', 'wheres', 'group_bys', 'order_bys',
 *     'parameters'
 *   Each value is an array with the following keys:
 *     'prefix', 'glue', 'pieces'
 * @return
 *   SQL string.
 */
function project_usage_build_query($sql_elements) {
  $sql = '';
  foreach ($sql_elements as $key => $sql_element) {
    if ($key != 'parameters' && count($sql_element['pieces'])) {
      $sql .= $sql_element['prefix'] . implode($sql_element['glue'], $sql_element['pieces']);
    }
  }
  return db_rewrite_sql($sql);
}

/**
 * End of old project crap ccode.
 */