function sc_user_activity()
{
if (qw_hook_exist(__FUNCTION__)) {
$args = func_get_args();
array_unshift($args, $this);
return qw_event_hook(__FUNCTION__, $args, NULL);
}
if (defined('QA_WORDPRESS_INTEGRATE_PATH')) {
require_once QA_INCLUDE_DIR . 'qa-app-posts.php';
$handle = qa_post_userid_to_handle($this->content['raw']['userid']);
} else {
$handle = qa_post_userid_to_handle($this->content['raw']['userid']);
}
if (isset($handle)) {
$content = get_user_activity($handle);
$this->output($content);
}
}
//function to get all user activity on a given case.
function get_user_activity($username, $case_id, $dbh)
{
//find the user's total time on the case
$case_time_query = $dbh->prepare("SELECT case_id, username, SUM( TIME ) as totaltime FROM `cm_case_notes` WHERE `case_id` LIKE :case_id and `username` LIKE :username GROUP BY username");
$case_time_query->bindParam(':case_id', $case_id);
$case_time_query->bindParam(':username', $username);
$case_time_query->execute();
$case_time_data = $case_time_query->fetch(PDO::FETCH_ASSOC);
//Get the user's last activity on the case
$last_activity_query = $dbh->prepare("SELECT cn.* FROM cm_case_notes AS cn JOIN ( SELECT case_id, username ,MAX( `date` ) AS recent_date FROM cm_case_notes WHERE case_id = :case_id GROUP BY username) AS q ON ( q.username, q.recent_date ) = ( cn.username, cn.`date`) WHERE cn.case_id = :case_id and cn.username = :username ");
$last_activity_query->bindParam(':case_id', $case_id);
$last_activity_query->bindParam(':username', $username);
$last_activity_query->execute();
$last_activity_data = $last_activity_query->fetch(PDO::FETCH_ASSOC);
//Get the users data
$user_data_query = $dbh->prepare("SELECT * from cm_users where username = :username");
$user_data_query->bindParam(':username', $username);
$user_data_query->execute();
$user_data = $user_data_query->fetch(PDO::FETCH_ASSOC);
//Get the assignment id of the user to this case.
$assignment_query = $dbh->prepare("SELECT id as assign_id,username,case_id, status as case_status,date_assigned from cm_case_assignees WHERE username = :username and case_id = :case_id LIMIT 1");
$assignment_query->bindParam(':case_id', $case_id);
$assignment_query->bindParam(':username', $username);
$assignment_query->execute();
$assignment = $assignment_query->fetch(PDO::FETCH_ASSOC);
$user_activity = array_merge((array) $user_data, (array) $last_activity_data, (array) $case_time_data, (array) $assignment);
return $user_activity;
}
$data = get_user_activity($username, $case_id, $dbh);
include '../../../html/templates/interior/cases_detail_user_activity.php';
<th width="13"></th>
<th>Usuario</th>
<th>Date</th>
<th>Tipo</th>
<th>IP</th>
<th width="110" class="ac">Content Control</th>
</tr>
<?php
$query = $this->db->query("SELECT * FROM ci_sessions ORDER BY last_activity DESC");
foreach ($query->result() as $item) {
$custom_data = unserialize($item->user_data);
?>
<tr>
<td></td>
<td><?php
echo get_user_activity($custom_data);
?>
</td>
<td><?php
echo mdate('%d/%m/%Y-%H:%i', $item->last_activity);
?>
</td>
<td><?php
echo $item->user_agent;
?>
</td>
<td><?php
echo $item->ip_address;
?>
</td>
<td><a href="#" class="ico del">Delete</a></td>
