View source
<?php
namespace Drupal\opigno_statistics\Form;
use Drupal\Component\Datetime\TimeInterface;
use Drupal\Core\Database\Connection;
use Drupal\Core\Datetime\DateFormatterInterface;
use Drupal\Core\Datetime\DrupalDateTime;
use Drupal\Core\Form\FormBase;
use Drupal\Core\Form\FormStateInterface;
use Drupal\Core\Link;
use Drupal\Core\Render\Markup;
use Drupal\opigno_learning_path\LearningPathAccess;
use Drupal\opigno_statistics\StatisticsPageTrait;
use Drupal\Core\Access\AccessResult;
use Drupal\Core\Access\AccessResultAllowed;
use Drupal\Core\Session\AccountInterface;
use Drupal\Core\Database\Database;
use Symfony\Component\DependencyInjection\ContainerInterface;
class DashboardForm extends FormBase {
use StatisticsPageTrait;
protected $database;
protected $time;
protected $date_formatter;
public function __construct(Connection $database, TimeInterface $time, DateFormatterInterface $date_formatter) {
$this->database = $database;
$this->time = $time;
$this->date_formatter = $date_formatter;
}
public static function create(ContainerInterface $container) {
return new static($container
->get('database'), $container
->get('datetime.time'), $container
->get('date.formatter'));
}
public function getFormId() {
return 'opigno_statistics_dashboard_form';
}
protected function buildUsersPerDay(DrupalDateTime $datetime, $lp_ids = NULL) {
$max_time = $datetime
->format(DrupalDateTime::FORMAT);
$min_datetime = $datetime
->sub(new \DateInterval('P1M'));
$min_time = $min_datetime
->format(DrupalDateTime::FORMAT);
$query = $this->database
->select('opigno_statistics_user_login', 'u');
$query
->addExpression('DAY(u.date)', 'hour');
$query
->addExpression('COUNT(DISTINCT u.uid)', 'count');
if (is_array($lp_ids)) {
$query
->leftJoin('group_content_field_data', 'g_c_f_d', 'u.uid = g_c_f_d.entity_id');
$query
->condition('g_c_f_d.gid', $lp_ids, 'IN');
$query
->condition('g_c_f_d.type', 'learning_path-group_membership');
}
$query
->condition('u.uid', 0, '<>');
$data = $query
->condition('u.date', [
$min_time,
$max_time,
], 'BETWEEN')
->groupBy('hour')
->execute()
->fetchAllAssoc('hour');
for ($i = 1; $i <= 31; ++$i) {
if (isset($data[$i])) {
$data[$i] = $data[$i]->count;
}
else {
$data[$i] = 0;
}
}
return [
'#theme' => 'opigno_statistics_chart__user_per_day',
'#max_count' => max(max($data), 5),
'#data' => $data,
];
}
protected function buildTrainingsProgress(DrupalDateTime $datetime, $lp_ids = NULL) {
$progress = 0;
$completion = 0;
$time_str = $datetime
->format(DrupalDateTime::FORMAT);
$query = $this->database
->select('opigno_learning_path_achievements', 'a');
$query
->addExpression('SUM(a.progress) / COUNT(a.progress) / 100', 'progress');
$query
->addExpression('COUNT(a.completed) / COUNT(a.registered)', 'completion');
$query
->fields('a', [
'name',
])
->groupBy('a.name')
->orderBy('a.name')
->condition('a.registered', $time_str, '<');
if (is_array($lp_ids)) {
$query
->condition('a.gid', $lp_ids, 'IN');
$query
->leftJoin('group_content_field_data', 'g_c_f_d', 'a.uid = g_c_f_d.entity_id AND g_c_f_d.gid = a.gid');
$query
->condition('g_c_f_d.type', 'learning_path-group_membership');
}
$query
->condition('a.uid', 0, '<>');
$or_group = $query
->orConditionGroup();
$or_group
->condition('a.completed', $time_str, '<');
$or_group
->isNull('a.completed');
$data = $query
->execute()
->fetchAll();
$count = count($data);
if ($count > 0) {
foreach ($data as $row) {
$progress += $row->progress;
$completion += $row->completion;
}
$progress /= $count;
$completion /= $count;
}
return [
'#type' => 'container',
'#attributes' => [
'class' => [
'trainings-progress',
],
],
'progress' => $this
->buildValueWithIndicator($this
->t('Training Progress'), $progress, NULL, t('Training progress is calculated as the sum of training progress for all published trainings divided by the total number of published trainings.
The training progress for a training is the sum of progress for all the users registered to the training divided by the number of users registered to the training.')),
'completion' => $this
->buildValueWithIndicator($this
->t('Training Completion'), $completion, NULL, t('Training completion is calculated as the sum of training completion rate for all published trainings divided by the total number of published trainings.
The training completion for a training is the total number of users being successful at the training divided by the number of users registered to the training.')),
'users' => $this
->buildUsersPerDay($datetime, $lp_ids),
];
}
protected function buildUserMetric($label, $value, $help_text = NULL) {
return [
'#theme' => 'opigno_statistics_user_metric',
'#label' => $label,
'#value' => $value,
'#help_text' => $help_text,
];
}
protected function buildUserMetrics($lp_ids = NULL) {
$connection = Database::getConnection();
$query = $connection
->select('users', 'u');
if (is_array($lp_ids)) {
$query
->leftJoin('group_content_field_data', 'g_c_f_d', 'u.uid = g_c_f_d.entity_id');
$query
->condition('g_c_f_d.type', 'learning_path-group_membership');
$query
->condition('g_c_f_d.gid', $lp_ids, 'IN');
}
$query
->condition('u.uid', 0, '<>');
$query
->groupBy('u.uid');
$users = $query
->countQuery()
->execute()
->fetchField();
$now = $this->time
->getRequestTime();
$period = 60 * 60 * 24 * 7;
$query = $connection
->select('users_field_data', 'u');
if (is_array($lp_ids)) {
$query
->leftJoin('group_content_field_data', 'g_c_f_d', 'u.uid = g_c_f_d.entity_id');
$query
->condition('g_c_f_d.type', 'learning_path-group_membership');
$query
->condition('g_c_f_d.gid', $lp_ids, 'IN');
}
$query
->condition('u.uid', 0, '<>');
$query
->condition('u.created', $now - $period, '>');
$query
->groupBy('u.uid');
$new_users = $query
->countQuery()
->execute()
->fetchField();
$query = $connection
->select('users_field_data', 'u');
if (is_array($lp_ids)) {
$query
->leftJoin('group_content_field_data', 'g_c_f_d', 'u.uid = g_c_f_d.entity_id');
$query
->condition('g_c_f_d.type', 'learning_path-group_membership');
$query
->condition('g_c_f_d.gid', $lp_ids, 'IN');
}
$query
->condition('u.uid', 0, '<>');
$query
->condition('u.access', $now - $period, '>');
$query
->groupBy('u.uid');
$active_users = $query
->countQuery()
->execute()
->fetchField();
return [
'#theme' => 'opigno_statistics_user_metrics',
'#help_text' => t('The data below is related to your global Opigno platform (for all trainings).'),
'users' => $this
->buildUserMetric($this
->t('Users'), $users, t('This is the total number of users on your Opigno instance')),
'new_users' => $this
->buildUserMetric($this
->t('New users'), $new_users, t('This is the number of new users who registered to your Opigno instance during the last 7 days.')),
'active_users' => $this
->buildUserMetric($this
->t('Recently active users'), $active_users, t('This is the number of users who logged in to your Opigno instance during the last 7 days.')),
];
}
protected function buildTrainingsList($lp_ids) {
$query = $this->database
->select('opigno_learning_path_achievements', 'a');
$query
->addExpression('COUNT(a.completed)', 'users_completed');
$query
->addExpression('AVG(a.time)', 'time');
$query
->fields('a', [
'gid',
'name',
]);
if (is_array($lp_ids)) {
$query
->condition('a.gid', $lp_ids, 'IN');
}
$data = $query
->groupBy('a.gid')
->groupBy('a.name')
->orderBy('a.name')
->distinct()
->execute()
->fetchAll();
$query = $this->database
->select('opigno_learning_path_group_user_status', 's');
$query
->addField('s', 'gid');
$query
->condition('s.uid', 0, '<>');
$query
->addExpression('COUNT(*)', 'count');
$query
->groupBy('s.gid');
$groups = $query
->execute()
->fetchAllAssoc('gid');
$table = [
'#type' => 'table',
'#attributes' => [
'class' => [
'statistics-table',
'trainings-list',
'table-striped',
],
],
'#header' => [
$this
->t('Training'),
$this
->t('Nb of users'),
$this
->t('Nb completed'),
$this
->t('Avg time spent'),
$this
->t('Details'),
],
'#rows' => [],
];
$gids = $this->database
->select('groups', 'g')
->fields('g', [
'id',
])
->execute()
->fetchCol();
foreach ($data as $row) {
$time = max(0, round($row->time));
$time_str = $time > 0 ? $this->date_formatter
->formatInterval($time) : '-';
if (in_array($row->gid, $gids)) {
$details_link = Link::createFromRoute(Markup::create('<span class="sr-only">' . t('Details @name', [
'@name' => $row->name,
]) . '</span>'), 'opigno_statistics.training', [
'group' => $row->gid,
])
->toRenderable();
$details_link['#attributes']['class'][] = 'details';
$details_link = [
'data' => $details_link,
];
}
else {
$details_link = [];
}
$table['#rows'][] = [
$row->name,
isset($groups[$row->gid]) ? $groups[$row->gid]->count : '',
$row->users_completed,
$time_str,
$details_link,
];
}
return $table;
}
public function buildForm(array $form, FormStateInterface $form_state) {
$moduleHandler = \Drupal::service('module_handler');
$query = $this->database
->select('opigno_learning_path_achievements', 'a');
$query
->addExpression('YEAR(a.registered)', 'year');
$data = $query
->groupBy('year')
->orderBy('year', 'DESC')
->execute()
->fetchAll();
$years = [
'none' => $this
->t('- None -'),
];
foreach ($data as $row) {
$year = $row->year;
if (!isset($years[$year])) {
$years[$year] = $year;
}
}
$max_year = !empty($years) ? max(array_keys($years)) : NULL;
$year_select = [
'#type' => 'select',
'#title' => $this
->t('Year'),
'#title_display' => 'invisible',
'#options' => $years,
'#default_value' => 'none',
'#ajax' => [
'event' => 'change',
'callback' => '::submitFormAjax',
'wrapper' => 'statistics-trainings-progress',
],
];
$year_current = $form_state
->getValue('year');
if ($year_current == NULL || $year_current == 'none') {
if ($max_year == 'none') {
$max_year = date('Y');
}
$year = $max_year;
}
else {
$year = $year_current;
}
$query = $this->database
->select('opigno_learning_path_achievements', 'a');
$query
->addExpression('MONTH(a.registered)', 'month');
$query
->addExpression('YEAR(a.registered)', 'year');
$data = $query
->groupBy('month')
->groupBy('year')
->orderBy('month')
->execute()
->fetchAll();
$months = [
'none' => $this
->t('- None -'),
];
foreach ($data as $row) {
$month = $row->month;
if (!isset($months[$month]) && $row->year == $year) {
$timestamp = mktime(0, 0, 0, $month, 1);
$months[$month] = $this->date_formatter
->format($timestamp, 'custom', 'F');
}
}
$max_month = !empty($months) ? max(array_keys($months)) : NULL;
$month_select = [
'#type' => 'select',
'#title' => $this
->t('Month'),
'#title_display' => 'invisible',
'#options' => $months,
'#default_value' => 'none',
'#ajax' => [
'event' => 'change',
'callback' => '::submitFormAjax',
'wrapper' => 'statistics-trainings-progress',
],
];
$month = $form_state
->getValue('month', $max_month);
if ($month == 'none' || $year_current == NULL || $year_current == 'none') {
if ($max_month == 'none') {
$max_month = date('n');
}
$month = $max_month;
}
$timestamp = mktime(0, 0, 0, $month, 1, $year);
$datetime = DrupalDateTime::createFromTimestamp($timestamp);
$datetime
->add(new \DateInterval('P1M'));
$account = \Drupal::currentUser();
$lp_ids = NULL;
if (!($account
->hasPermission('view global statistics') || $account
->hasPermission('view any user statistics') || $account
->id() == 1)) {
$lp_ids = $this
->checkLimitPermissions($account);
}
$form['trainings_progress'] = [
'#type' => 'container',
'#attributes' => [
'id' => 'statistics-trainings-progress',
],
[
'#type' => 'html_tag',
'#tag' => 'h2',
'#value' => $this
->t('Dashboard of statistics'),
'#attributes' => [
'class' => [
'sr-only',
],
],
],
'year' => $year_select,
];
if ($year_current != NULL && $year_current != 'none') {
$form['trainings_progress']['month'] = $month_select;
}
$form['trainings_progress']['trainings_progress'] = $this
->buildTrainingsProgress($datetime, $lp_ids);
$form['content_statistics'] = [
'#type' => 'container',
'users' => $this
->buildUserMetrics($lp_ids),
];
if ($moduleHandler
->moduleExists('opigno_skills_system')) {
$form['content_statistics']['skills_list'] = $this
->buildSkillsTable();
}
$form['content_statistics']['trainings_list'] = $this
->buildTrainingsList($lp_ids);
$form['#attached']['library'][] = 'opigno_statistics/dashboard';
return $form;
}
protected function buildSkillsTable() {
$query = $this->database
->select('opigno_skills_statistic', 'a')
->fields('a', [
'tid',
]);
$query
->addExpression('AVG(a.score)', 'score');
$query
->addExpression('AVG(a.progress)', 'progress');
$query
->groupBy('tid');
$rows = $query
->execute()
->fetchAllAssoc('tid');
$rows = array_map(function ($row) {
$score = [
'data' => $this
->buildScore(round($row->score)),
];
$progress = [
'data' => $this
->buildScore(round($row->progress)),
];
$term = \Drupal::entityTypeManager()
->getStorage('taxonomy_term')
->load($row->tid);
if (!empty($term)) {
$skill_name = $term
->getName();
return [
'class' => 'training',
'data-training' => $row->tid,
'data' => [
$skill_name,
$score,
$progress,
],
];
}
}, $rows);
$rows = array_filter($rows);
if (empty($rows)) {
return [];
}
return [
'#type' => 'container',
'#attributes' => [
'class' => [
'skills-list',
],
],
'table' => [
'#type' => 'table',
'#attributes' => [
'class' => [
'statistics-table',
'skills-list',
'table-striped',
],
],
'#header' => [
$this
->t('Skill'),
$this
->t('Score'),
$this
->t('Progress'),
],
'#rows' => $rows,
],
];
}
public function checkLimitPermissions(AccountInterface $account) {
$connection = Database::getConnection();
$query = $connection
->select('group_content_field_data', 'g_c_f_d')
->fields('g_c_f_d', [
'gid',
]);
$query
->leftJoin('group_content__group_roles', 'g_c_g_r', 'g_c_f_d.id = g_c_g_r.entity_id');
$query
->condition('g_c_g_r.group_roles_target_id', 'learning_path-user_manager');
$query
->condition('g_c_f_d.entity_id', $account
->id());
$query
->condition('g_c_f_d.type', 'learning_path-group_membership');
$result = $query
->execute()
->fetchAllAssoc('gid');
$lp_ids = [];
foreach ($result as $row) {
$lp_ids[] = $row->gid;
}
return $lp_ids;
}
public function access(AccountInterface $account) {
$uid = $account
->id();
if ($account
->hasPermission('view global statistics') || $account
->hasPermission('view any user statistics') || $uid == 1) {
return AccessResult::allowed();
}
else {
$is_user_manager = LearningPathAccess::memberHasRole('user_manager', $account);
if ($is_user_manager) {
return AccessResultAllowed::allowed()
->mergeCacheMaxAge(0);
}
else {
return AccessResultAllowed::forbidden()
->mergeCacheMaxAge(0);
}
}
}
public function submitFormAjax(array &$form, FormStateInterface &$form_state) {
$trigger = $form_state
->getTriggeringElement();
if (isset($trigger['#name']) && $trigger['#name'] == 'year') {
$form['trainings_progress']['month']['#value'] = 'none';
}
return $form['trainings_progress'];
}
public function getCacheMaxAge() {
return 0;
}
public function submitForm(array &$form, FormStateInterface $form_state) {
}
}