Returns the sum of the specified column values.
|
public sum ( string $q, |
||
| $q | string | the column name or expression. Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
| $db | the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used. | |
| return | mixed | the sum of the specified column values. |
/**
* Dashboard
*
* @return mixed
*/
public function actionIndex()
{
// Unique users today
$usersQuery = new Query();
$usersQuery->select(['domain_userid'])->from('{{%event}}')->where('DATE(FROM_UNIXTIME(collector_tstamp)) = CURRENT_DATE')->distinct();
// Unique users total
$totalUsersQuery = new Query();
$totalUsersQuery->select(['users'])->from('{{%stats_performance}}')->where('DATE(day) != CURRENT_DATE');
// Submissions today
$submissionsQuery = new Query();
$submissionsQuery->select(['id'])->from('{{%form_submission}}')->andWhere('DATE(FROM_UNIXTIME(created_at)) = CURRENT_DATE');
// Submissions total
$totalSubmissionsQuery = new Query();
$totalSubmissionsQuery->select(['id'])->from('{{%form_submission}}');
// If not admin
if (!empty(Yii::$app->user) && !Yii::$app->user->can("admin")) {
// Add user filter to queries
$formIds = Yii::$app->user->getAssignedFormIds();
$formIds = count($formIds) > 0 ? $formIds : 0;
// Important restriction
$usersQuery->andFilterWhere(['app_id' => $formIds]);
$totalUsersQuery->andFilterWhere(['app_id' => $formIds]);
$submissionsQuery->andFilterWhere(['form_id' => $formIds]);
$totalSubmissionsQuery->andFilterWhere(['form_id' => $formIds]);
}
// Execute queries
$users = $usersQuery->count();
$totalUsers = $totalUsersQuery->sum('users');
$submissions = $submissionsQuery->count();
$totalSubmissions = $totalSubmissionsQuery->count();
// Add today data to total
$totalUsers = $totalUsers + $users;
// Users / submissions = Conversion rate
$submissionRate = 0;
if ($users > 0 && $submissions > 0) {
$submissionRate = round($submissions / $users * 100);
}
$totalSubmissionRate = 0;
if ($totalUsers > 0 && $totalSubmissions > 0) {
$totalSubmissionRate = round($totalSubmissions / $totalUsers * 100);
}
// Most viewed forms list by unique users
$formsByUsersQuery = (new Query())->select(['f.id', 'f.name', 'COUNT(DISTINCT(e.domain_userid)) AS users'])->from('{{%event}} AS e')->innerJoin('{{%form}} AS f', 'e.app_id = f.id')->where(['event' => 'pv'])->andWhere('DATE(FROM_UNIXTIME(collector_tstamp)) = CURRENT_DATE')->groupBy(['id'])->orderBy('users DESC')->limit(Yii::$app->params['ListGroup.listSize']);
// Forms list by submissions
$formsBySubmissionsQuery = (new Query())->select(['f.id', 'f.name', 'COUNT(fs.id) AS submissions'])->from('{{%form_submission}} AS fs')->innerJoin('{{%form}} as f', 'fs.form_id = f.id')->where('DATE(FROM_UNIXTIME(fs.created_at)) = CURRENT_DATE')->groupBy(['f.id'])->orderBy('submissions DESC')->limit(Yii::$app->params['ListGroup.listSize']);
// Last updated forms list
$lastUpdatedFormsQuery = (new Query())->select(['id', 'name', 'updated_at'])->from('{{%form}} AS f')->where('DATE(FROM_UNIXTIME(updated_at)) = CURRENT_DATE')->orderBy('updated_at DESC')->limit(Yii::$app->params['ListGroup.listSize']);
// If not admin
if (!empty(Yii::$app->user) && !Yii::$app->user->can("admin")) {
// Add user filter to queries
$formIds = Yii::$app->user->getAssignedFormIds();
$formIds = count($formIds) > 0 ? $formIds : 0;
// Important restriction
$formsByUsersQuery->andFilterWhere(['f.id' => $formIds]);
$formsBySubmissionsQuery->andFilterWhere(['fs.form_id' => $formIds]);
$lastUpdatedFormsQuery->andFilterWhere(['f.id' => $formIds]);
}
$formsByUsers = $formsByUsersQuery->all();
$formsBySubmissions = $formsBySubmissionsQuery->all();
$lastUpdatedForms = $lastUpdatedFormsQuery->all();
return $this->render('index', ['users' => $users, 'submissions' => $submissions, 'submissionRate' => $submissionRate, 'totalUsers' => $totalUsers, 'totalSubmissions' => $totalSubmissions, 'totalSubmissionRate' => $totalSubmissionRate, 'formsByUsers' => $formsByUsers, 'formsBySubmissions' => $formsBySubmissions, 'lastUpdatedForms' => $lastUpdatedForms]);
}
