-
Notifications
You must be signed in to change notification settings - Fork 79
Expand file tree
/
Copy pathXDStatistics.php
More file actions
100 lines (91 loc) · 3.06 KB
/
Copy pathXDStatistics.php
File metadata and controls
100 lines (91 loc) · 3.06 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
<?php
use CCR\DB;
class XDStatistics
{
/**
* Retrieve the number of user visits for the provided $user_types, by default all of them,
* formatted for the given $aggregation_type.
*
* @param string $aggregation_type by default 'month', also accepts 'year'.
* @param array $user_types an array of user type id's
* @return mixed returns an array per row found w/ the following key/values:
* - last_name: Users.last_name
* - first_name: Users.first_name
* - email_address: Users.email_address
* - username: Users.username
* - role_list: comma concatenated list of Acls.display per acl assigned to this user
* - user_type: UserTypes.type that corresponds to Users.user_type
* - timeframe: based on $aggregation_type, 'YYYY-MM' for 'month' and 'YYYY' for 'year'
* - visit_frequency The number of records found in SessionManager for this user in the
* timeframe defined by aggregation_type
* @throws Exception if there is a problem retrieving data from the db.
*/
public static function getUserVisitStats($aggregation_type = 'month', $user_types = array())
{
$db = DB::factory('database');
$aggregationFormat = '%Y-%m';
if ($aggregation_type === 'year') {
$aggregationFormat = '%Y';
}
// Default to a no-op if no use_types are provided.
$whereClause = '1 = 1';
if (!empty($user_types)) {
$inValues = array_map(
function ($value) use ($db) {
return $db->quote($value);
},
$user_types
);
$whereClause = 'ud.user_type IN (' . implode(',', $inValues) . ')';
}
$query = <<<SQL
SELECT ud.last_name,
ud.first_name,
ud.email_address,
ud.username,
CONCAT('"', ud.role_list, '"') as role_list,
ud.type as user_type,
DATE_FORMAT(FROM_UNIXTIME(sm.init_time), '$aggregationFormat') timeframe,
COUNT(ud.id) as visit_frequency
FROM SessionManager sm
/* We split out the user-data from the session manager data
* so that we can isolate the group bys.
*/
JOIN (
SELECT u.id,
u.last_name,
u.first_name,
u.email_address,
u.username,
u.user_type,
ut.type,
GROUP_CONCAT(a.display ORDER BY a.display) as role_list
FROM Users u
JOIN user_acls ua ON ua.user_id = u.id
JOIN acls a ON a.acl_id = ua.acl_id
JOIN UserTypes ut ON ut.id = u.user_type
GROUP BY
u.id,
u.last_name,
u.first_name,
u.email_address,
u.username,
u.user_type,
ut.type
) as ud
ON ud.id = sm.user_id
WHERE $whereClause
GROUP BY
ud.id,
ud.email_address,
ud.type,
ud.role_list,
timeframe
ORDER BY
timeframe DESC,
visit_frequency DESC,
ud.role_list DESC;
SQL;
return $db->query($query);
} //getUserVisitStats
} //XDStatistics