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>