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]); }