function opigno_statistics_app_query_top_10_groups in Opigno Statistics App 7

Retrieve top 10 groups (cached for 1 day)

Ouput example: array( array( 'nid' => 1, 'title' => 'Course 1', 'number_of_visits' => 123, 'number_of_users' => 45, 'number_passed' => 23, ), array( 'nid' => 2, 'title' => 'Course 2', 'number_of_visits' => 300, 'number_of_users' => 56, 'number_passed' => 30, ), array( 'nid' => 1, 'title' => 'Course 3', 'number_of_visits' => 140, 'number_of_users' => 34, 'number_passed' => 20, ) )

Parameters

string $group_type:

int $month_year:

boolean $filter_month:

int $category_id:

Return value

array

2 calls to opigno_statistics_app_query_top_10_groups()

opigno_statistics_app_present_top_10_classes in includes/dashboard/presenters.inc

Present top 10 classes

opigno_statistics_app_present_top_10_courses in includes/dashboard/presenters.inc

Present top 10 courses

File

includes/dashboard/queries.inc, line 38

Code

function opigno_statistics_app_query_top_10_groups($group_type, $month_year, $filter_month, $category_id) {
  $cache_key = __FUNCTION__ . ':' . $group_type . ':' . $month_year . ':' . $filter_month . ':' . $category_id;
  $cached_object = cache_get($cache_key);
  if ($cached_object) {
    $top_10_groups = $cached_object->data;
  }
  else {
    $top_10_groups = array();
    if ($filter_month) {
      if ($category_id == '') {
        $sql_filter_statistics_group = "WHERE group_type = :group_type AND month_year = :month_year";
        $sql_filter_values = array(
          ':month_year' => $month_year,
          ':group_type' => $group_type,
        );
      }
      else {
        $sql_filter_statistics_group = "WHERE group_type = :group_type AND month_year = :month_year AND category_taxonomy_term_id = :category_id";
        $sql_filter_values = array(
          ':month_year' => $month_year,
          ':group_type' => $group_type,
          ':category_id' => $category_id,
        );
      }
    }
    else {
      if ($category_id == '') {
        $sql_filter_statistics_group = "WHERE group_type = :group_type AND month_year >= :month_year";
        $sql_filter_values = array(
          ':month_year' => $month_year,
          ':group_type' => $group_type,
        );
      }
      else {
        $sql_filter_statistics_group = "WHERE group_type = :group_type AND month_year >= :month_year AND category_taxonomy_term_id = :category_id";
        $sql_filter_values = array(
          ':month_year' => $month_year,
          ':group_type' => $group_type,
          ':category_id' => $category_id,
        );
      }
    }
    $result = db_query("\n        SELECT g2.group_title, g2.page_views, g2.number_passed, COUNT(DISTINCT ug.uid) as nb_members, g2.group_nid\n        FROM (\n               SELECT group_title, SUM(page_views) as page_views, SUM(number_passed) as number_passed, group_nid\n               FROM (\n                      SELECT group_title, page_views, number_passed, group_nid\n                      FROM {opigno_statistics_group}\n                      " . $sql_filter_statistics_group . "\n                      ORDER BY month_year DESC\n                    ) g1\n               GROUP BY group_nid\n             ) g2\n        INNER JOIN opigno_statistics_user_group ug ON g2.group_nid = ug.group_nid\n        GROUP BY g2.group_nid\n        ORDER BY g2.page_views DESC, g2.group_title\n        LIMIT 10\n      ", $sql_filter_values);
    while ($record = $result
      ->fetchAssoc()) {
      if (db_select('node', 't')
        ->fields('t')
        ->condition('nid', $record['group_nid'])
        ->execute()
        ->rowCount()) {
        $stats_link = 'node/' . $record['group_nid'] . '/opigno-statistics';
      }
      else {
        $stats_link = 'opigno-statistics/' . $record['group_nid'] . '/group';
      }
      $record['stats_link'] = $stats_link;
      $top_10_groups[] = $record;
    }
    cache_set($cache_key, $top_10_groups, 'cache', time() + 7200);

    // 7200s = 2h cache
  }
  return $top_10_groups;
}