public function getdata_user_report($search_arr, $per_page, $page_no, $sort_name, $sort_type)
{
if ($sort_name == 'isactive') {
$sort_name = "u.isactive";
}
if ($sort_name == 'createddate') {
$sort_name = "u.createddate";
}
$db = Zend_Db_Table::getDefaultAdapter();
$search_str = "";
$user_search = " where u.id != 1 ";
foreach ($search_arr as $key => $value) {
if ($value != '') {
if ($key != 'logindatetime') {
if ($key == 'createddate') {
$search_str .= " date(u." . $key . ") = '" . sapp_Global::change_date($value, 'database') . "' and";
} else {
if ($key == 'u.isactive') {
if ($value == 0) {
$search_str .= " u.isactive != 1";
} else {
$search_str .= " u.isactive = 1";
}
} else {
$search_str .= " " . $key . " = '" . $value . "' and";
}
}
}
}
}
if ($search_str != '') {
$search_str = trim($search_str, "and");
$search_str = " and " . $search_str;
}
$offset = $per_page * $page_no - $per_page;
$limit_str = " limit " . $per_page . " offset " . $offset;
$tot_query = "select date(max(logindatetime)) lastlog from main_users u\n left join main_userloginlog l on u.id = l.userid and l.userid != 1 and u.id != 1\n inner join main_roles r on r.id = u.emprole " . $user_search . " " . $search_str . "\n group by u.id ";
if (isset($search_arr['logindatetime']) && $search_arr['logindatetime'] != '') {
$tot_query = "select * from (" . $tot_query . ") res where date(lastlog) = '" . sapp_Global::change_date(sapp_Global::getGMTformatdate($search_arr['logindatetime']), 'database') . "' ";
}
$tot_result = $db->query($tot_query);
$count = $tot_result->rowCount();
$page_cnt = ceil($count / $per_page);
if ($sort_name != 'u.isactive') {
$query = "select u.userfullname,r.rolename,u.emailaddress,u.employeeId,date(max(logindatetime)) lastlog,\n if(u.isactive = 1,1,0) isactive,date(u.createddate) createdate from main_users u \n left join main_userloginlog l on u.id = l.userid and l.userid != 1 and u.id != 1\n inner join main_roles r on r.id = u.emprole " . $user_search . " " . $search_str . "\n group by u.id order by " . $sort_name . " " . $sort_type . " ";
} else {
if ($sort_type == 'DESC') {
$str = "when 0 then 0 when 1 then 1 when 2 then 0 when 3 then 0 when 4 then 0 when 5 then 0 else 99 end";
} else {
if ($sort_type == 'ASC') {
$str = "when 0 then 1 when 1 then 0 when 2 then 1 when 3 then 1 when 4 then 1 when 5 then 1 else 99 end";
}
}
$query = "select u.userfullname,r.rolename,u.emailaddress,u.employeeId,date(max(logindatetime)) lastlog,\n if(u.isactive = 1,1,0) isactive,date(u.createddate) createdate from main_users u \n left join main_userloginlog l on u.id = l.userid and l.userid != 1 and u.id != 1\n inner join main_roles r on r.id = u.emprole " . $user_search . " " . $search_str . "\n group by u.id order by case " . $sort_name . " " . $str . " ";
}
if (isset($search_arr['logindatetime']) && $search_arr['logindatetime'] != '') {
$query = "select * from (" . $query . ") res where date(lastlog) = '" . sapp_Global::change_date(sapp_Global::getGMTformatdate($search_arr['logindatetime']), 'database') . "' ";
}
$query .= " " . $limit_str;
$result = $db->query($query);
$data = $result->fetchAll();
return array('rows' => $data, 'page_cnt' => $page_cnt);
}
public function activitylogreportAction()
{
try {
$activitylog_model = new Default_Model_Activitylog();
$logmanager_model = new Default_Model_Logmanager();
$dashboardcall = $this->_getParam('dashboardcall');
if ($dashboardcall == 'Yes') {
$perPage = DASHBOARD_PERPAGE;
} else {
$perPage = PERPAGE;
}
$selectFields = array('menuname' => 'Menu', 'username' => 'User Name', 'empId' => 'Employee ID', 'useraction' => 'Action', 'modifieddate' => 'Modified Date');
$selectColumns = array_keys($selectFields);
$action = array('1' => 'Add', '2' => 'Edit', '3' => 'Delete', '5' => 'Cancel');
$activityLogData = array();
$splitArray = array();
$pageNo = 1;
$order = $funorder = 'Desc';
$by = $sortby = 'last_modifieddate';
$searchData = '';
$lastpage = 0;
if ($this->getRequest()->getPost()) {
$this->_helper->layout->disableLayout();
}
if ($this->_request->getParam('pageNo') != '') {
$pageNo = intval($this->_request->getParam('pageNo'));
}
if ($this->_request->getParam('per_page') != '') {
$perPage = intval($this->_request->getParam('per_page'));
}
if ($this->_request->getParam('fields') != '') {
$selectColumns = explode(',', $this->_request->getParam('fields'));
}
$finalArray = array();
//POST with empty search fields
if ($this->_request->getParam('hiddenusername') == '' && $this->_request->getParam('menu') == '' && $this->_request->getParam('useraction') == '' && $this->_request->getParam('modifieddate') == '') {
if ($this->_request->getParam('sortby') != '') {
$by = $sortby = $this->_request->getParam('sortby');
$order = $funorder = $this->_request->getParam('order');
if ($sortby == 'userfullname') {
$by = '';
$usernameorder = $logmanager_model->getUsernameOrderForLog($order);
if (!empty($usernameorder)) {
$funorder = 'FIND_IN_SET(last_modifiedby,"' . $usernameorder . '")';
}
}
if ($sortby == 'employeeId') {
$by = '';
$empidorder = $logmanager_model->getEmpidOrderForLog($order);
if (!empty($empidorder)) {
$funorder = 'FIND_IN_SET(last_modifiedby,"' . $empidorder . '")';
}
}
if ($sortby == 'menuname') {
$by = '';
$menunameorder = $logmanager_model->getMenuOrderForLog($order);
if (!empty($menunameorder)) {
$funorder = 'FIND_IN_SET(menuId,"' . $menunameorder . '")';
}
}
}
$activityLogData = $logmanager_model->getLogManagerDataReport($by, $funorder, $pageNo, $perPage, $searchData, array('*'));
$activityLogCount = $activitylog_model->getLogManagerCount();
$menuArray = array();
$userArray = array();
if (count($activityLogData) > 0) {
$lastpage = ceil($activityLogCount / $perPage);
foreach ($activityLogData as $activitylog) {
if (isset($activitylog['menuId'])) {
if (!in_array($activitylog['menuId'], $menuArray)) {
array_push($menuArray, $activitylog['menuId']);
}
}
if (isset($activitylog['last_modifiedby'])) {
if (!in_array($activitylog['last_modifiedby'], $userArray)) {
array_push($userArray, $activitylog['last_modifiedby']);
}
}
}
}
$menuNameArray = $activitylog_model->getMenuNamesByIds($menuArray);
$userNameArray = $activitylog_model->getuserNamesByIds($userArray);
if (count($activityLogData) > 0) {
foreach ($activityLogData as $key => $activitylog) {
if (in_array('menuname', $selectColumns)) {
$finalArray[$key]['menuname'] = isset($menuNameArray[$activitylog['menuId']]) ? $menuNameArray[$activitylog['menuId']]['name'] : '';
}
if (in_array('username', $selectColumns)) {
$finalArray[$key]['username'] = isset($userNameArray[$activitylog['last_modifiedby']]) ? $userNameArray[$activitylog['last_modifiedby']]['userfullname'] : '';
}
if (in_array('empId', $selectColumns)) {
$finalArray[$key]['empId'] = isset($userNameArray[$activitylog['last_modifiedby']]) ? $userNameArray[$activitylog['last_modifiedby']]['employeeId'] : '';
}
if (in_array('useraction', $selectColumns)) {
$finalArray[$key]['useraction'] = $action[$activitylog['user_action']];
}
if (in_array('modifieddate', $selectColumns)) {
$finalArray[$key]['modifieddate'] = sapp_Global::getDisplayDate($activitylog['last_modifieddate']);
}
}
}
} else {
// with search values from form
$searchQuery = '';
if ($this->_request->getParam('menu') != '') {
$searchQuery .= ' menuId =' . intval($this->_request->getParam('menu')) . ' AND';
}
if ($this->_request->getParam('useraction') != '') {
$searchQuery .= ' user_action =' . intval($this->_request->getParam('useraction')) . ' AND';
}
if ($this->_request->getParam('hiddenusername') != '') {
$username = $this->_request->getParam('hiddenusername');
//userid in request
$searchQuery .= ' log_details Like \'%"userid":"' . $username . '"%\' AND';
}
if ($this->_request->getParam('modifieddate') != '') {
$date = sapp_Global::change_date($this->_request->getParam('modifieddate'), 'database');
$date1 = str_replace('-', '/', $date);
$onedayafter = date('Y-m-d', strtotime($date1 . "+1 days"));
$onedaybefore = date('Y-m-d', strtotime($date1 . "-1 days"));
$searchQuery .= '(log_details Like "%' . $onedaybefore . '%" or log_details Like "%' . $date . '%" or log_details Like "%' . $onedayafter . '%")';
}
if ($searchQuery != '') {
$searchQuery = rtrim($searchQuery, " AND");
}
$activityLogData = $logmanager_model->getLogManagerDataReport('last_modifieddate', 'Desc', '', '', $searchQuery, array('*'));
/*looping jsonlogs */
if (count($activityLogData) > 0) {
$userArray = array();
$menuArray = array();
$logJsonArray = array();
$jsonCount = 0;
$index = 0;
foreach ($activityLogData as $activitylog) {
$logdetails = '{"testjson":[' . $activitylog['log_details'] . ']}';
$logarr = @get_object_vars(json_decode($logdetails));
if (!empty($logarr)) {
$logarr['testjson'] = array_reverse($logarr['testjson']);
$jsonCount = count($logarr['testjson']);
}
if ($jsonCount > 0 && isset($logarr['testjson']) && !empty($logarr['testjson'])) {
foreach ($logarr['testjson'] as $key => $curr) {
$currArray = @get_object_vars($curr);
/*userid and date check with form and json values*/
$flag = false;
if ($this->_request->getParam('modifieddate') != '') {
$currArrayTemp = sapp_Global::getGMTformatdate($currArray['date']);
$pos = strpos($currArrayTemp, $date);
$flag = $pos !== false ? false : true;
}
if ($this->_request->getParam('hiddenusername') != '' && $username != $currArray['userid'] || $flag) {
continue;
}
/*end userid and date check*/
$logJsonArray[$index]['userid'] = $currArray['userid'] != '' ? $currArray['userid'] : 1;
$logJsonArray[$index]['date'] = $currArray['date'];
$datesort[$index] = $currArray['date'];
// to sort by date
if (!in_array($currArray['userid'], $userArray)) {
array_push($userArray, $currArray['userid']);
}
if (isset($activitylog['menuId'])) {
if (!in_array($activitylog['menuId'], $menuArray)) {
array_push($menuArray, $activitylog['menuId']);
}
}
$logJsonArray[$index]['menu'] = $activitylog['menuId'];
$logJsonArray[$index]['action'] = $action[(string) $activitylog['user_action']];
$actionsort[$index] = $action[(string) $activitylog['user_action']];
$index++;
}
}
}
$menuNameArray = $activitylog_model->getMenuNamesByIds($menuArray);
$userNameArray = $activitylog_model->getuserNamesByIds($userArray);
$lastpage = ceil(count($logJsonArray) / $perPage);
$endIndex = intval($perPage);
if ($pageNo != 1) {
$startIndex = (intval($pageNo) - 1) * intval($perPage);
} else {
$startIndex = 0;
}
if (count($logJsonArray) > 0) {
if ($this->_request->getParam('sortby') != '') {
$sortby = $this->_request->getParam('sortby');
$order = $this->_request->getParam('order');
$orderby = $order == 'asc' ? SORT_ASC : SORT_DESC;
if ($sortby == 'user_action' || $sortby == 'last_modifieddate') {
// can sort by logJsonArray
if ($sortby == 'user_action') {
array_multisort($actionsort, $orderby, $logJsonArray);
}
if ($sortby == 'last_modifieddate') {
array_multisort($datesort, $orderby, $logJsonArray);
}
/* only perpage no of rows */
$splitArray = array_slice($logJsonArray, $startIndex, $endIndex);
foreach ($splitArray as $key => $logjson) {
if (in_array('menuname', $selectColumns)) {
$finalArray[$key]['menuname'] = isset($menuNameArray[(string) $logjson['menu']]) ? $menuNameArray[(string) $logjson['menu']]['name'] : '';
}
if (in_array('username', $selectColumns)) {
$finalArray[$key]['username'] = isset($userNameArray[(string) $logjson['userid']]) ? $userNameArray[(string) $logjson['userid']]['userfullname'] : '';
}
if (in_array('empId', $selectColumns)) {
$finalArray[$key]['empId'] = isset($userNameArray[(string) $logjson['userid']]) ? $userNameArray[(string) $logjson['userid']]['employeeId'] : '';
}
if (in_array('useraction', $selectColumns)) {
$finalArray[$key]['useraction'] = $logjson['action'];
}
if (in_array('modifieddate', $selectColumns)) {
$finalArray[$key]['modifieddate'] = sapp_Global::getDisplayDate($logjson['date']);
}
}
} else {
$totalArray = array();
foreach ($logJsonArray as $key => $logjson) {
if (in_array('menuname', $selectColumns)) {
$totalArray[$key]['menuname'] = isset($menuNameArray[(string) $logjson['menu']]) ? $menuNameArray[(string) $logjson['menu']]['name'] : '';
}
$menunameSort[$key] = $totalArray[$key]['menuname'];
if (in_array('username', $selectColumns)) {
$totalArray[$key]['username'] = isset($userNameArray[(string) $logjson['userid']]) ? $userNameArray[(string) $logjson['userid']]['userfullname'] : '';
}
$usernameSort[$key] = $totalArray[$key]['username'];
if (in_array('empId', $selectColumns)) {
$totalArray[$key]['empId'] = isset($userNameArray[(string) $logjson['userid']]) ? $userNameArray[(string) $logjson['userid']]['employeeId'] : '';
}
$empIdSort[$key] = $totalArray[$key]['empId'];
if (in_array('useraction', $selectColumns)) {
$totalArray[$key]['useraction'] = $logjson['action'];
}
if (in_array('modifieddate', $selectColumns)) {
$totalArray[$key]['modifieddate'] = sapp_Global::getDisplayDate($logjson['date']);
}
}
if ($sortby == 'userfullname') {
array_multisort($usernameSort, $orderby, $totalArray);
}
if ($sortby == 'employeeId') {
array_multisort($empIdSort, $orderby, $totalArray);
}
if ($sortby == 'menuname') {
array_multisort($menunameSort, $orderby, $totalArray);
}
/* only perpage no of rows */
if (count($totalArray) >= intval($perPage)) {
$finalArray = array_slice($totalArray, $startIndex, $endIndex);
} else {
$finalArray = $totalArray;
}
}
}
}
}
}
if ($this->getRequest()->getPost()) {
// To generate PDF START
if ($this->_request->getParam('generatereport') == 'pdf') {
$this->generateActivityLogPdf($finalArray, $selectColumns);
}
}
if ($this->_request->getParam('generatereport') == 'xcel') {
//xcel generation
foreach ($selectFields as $key => $val) {
foreach ($selectColumns as $column) {
if ($column == $key) {
$selectColumnLabels[$key] = $val;
}
}
}
sapp_Global::export_to_excel($finalArray, $selectColumnLabels, 'Activitylog Report.xlsx');
exit;
}
$activitylogreport_form = new Default_Form_activitylogreport();
$this->view->form = $activitylogreport_form;
$this->view->totalselectfields = $selectFields;
$this->view->tabkeys = implode(',', $selectColumns);
$this->view->activitylogData = $finalArray;
$this->view->pageNo = $pageNo;
$this->view->perPage = $perPage;
$this->view->sortBy = $sortby;
$this->view->order = $order;
$this->view->lastPageNo = $lastpage;
} catch (Exception $e) {
echo $e->getMessage();
}
}
/**
* This function gives all content for grid view.
* @parameters
* @param $sort = ascending or descending
* @param $by = name of field which to be sort
* @param $pageNo = page number
* @param $perPage = no.of records per page
* @param $searchData = search string
* @param $call = type of call like ajax.
* @return Array;
*/
public function getGrid($sort, $by, $perPage, $pageNo, $searchData, $call, $p1, $p2, $p3, $p4, $p5)
{
$menu_model = new Default_Model_Menu();
$user_model = new Default_Model_Users();
$searchQuery = '';
$searchArray = array();
$data = array();
if ($searchData != '' && $searchData != 'undefined') {
$searchValues = json_decode($searchData);
if (count($searchValues) > 0) {
foreach ($searchValues as $key => $val) {
if ($key == 'last_modifieddate') {
$searchQuery .= " " . $key . " like '%" . sapp_Global::getGMTformatdate(urldecode($val)) . "%' AND ";
} else {
$searchQuery .= " " . $key . " like '%" . $val . "%' AND ";
}
$searchArray[$key] = $val;
}
$searchQuery = rtrim($searchQuery, " AND");
}
}
$objName = 'logmanager';
$tableFields = array('action' => 'Action', 'menuName' => 'Menu Name', 'id' => 'ID', 'userfullname' => 'Last Modified By', 'profileimg' => 'Profile', 'employeeId' => 'Employee ID', 'menuUrl' => 'Url', 'user_action' => 'Action', 'key_flag' => 'Last Modified Record', 'last_modifieddate' => 'Last Modified Date');
$tablecontent = $this->getActivitylogData($sort, $by, $pageNo, $perPage, $searchQuery);
$menu_data = $menu_model->getMenusListForActivitylog();
$menu_arr = array();
foreach ($menu_data as $gkey => $gdata) {
$menu_arr[$gdata['menuname']] = $gdata['menuname'];
}
$user_data = $user_model->getUserListForActivitylog();
$user_arr = array();
foreach ($user_data as $gkey => $gdata) {
$user_arr[$gdata['userfullname']] = $gdata['userfullname'];
}
$useractionArray = array('1' => 'Add', '5' => 'Cancel', '3' => 'Delete', '2' => 'Edit');
$dataTmp = array('sort' => $sort, 'by' => $by, 'pageNo' => $pageNo, 'perPage' => $perPage, 'tablecontent' => $tablecontent, 'objectname' => $objName, 'menuName' => 'Activity log', 'extra' => array(), 'tableheader' => $tableFields, 'jsGridFnName' => 'getAjaxgridData', 'jsFillFnName' => '', 'searchArray' => $searchArray, 'call' => $call, 'search_filters' => array('menuname' => array('type' => 'select', 'filter_data' => array('' => 'All') + $menu_arr), 'userfullname' => array('type' => 'select', 'filter_data' => array('' => 'All') + $user_arr), 'last_modifieddate' => array('type' => 'datepicker'), 'user_action' => array('type' => 'select', 'filter_data' => array('' => 'All') + $useractionArray)));
return $dataTmp;
}
