IDCOL/api/application/models/Syslog_model.php

<?php
if (!defined('BASEPATH'))exit('No direct script access allowed');
include_once(FCPATH . 'application/models/Base_model.php');
class Syslog_model extends Base_model {

    protected $table = 'syslog';

    public function __construct() {
        parent::__construct();
    }

    public function set_read($id){
    	return $this->db->where('id',$id)->update($this->table,array('is_read'=>1));
    }
    
    // 获取日志列表
    public function getList($filter,$page = null,$limit = null,$field="S.*",$type = 0){
    	$where_sql = "";
		
    	if (isset($filter['id']) || isset($filter['realname'])  || isset($filter['username'])){
    		$arrUserIds = $this->getUserIds($filter);
    		if (empty($arrUserIds)){
    			$arrUserIds[] = 0;
    		}
    		$where_sql  .= " AND S.userid IN(".implode(',', $arrUserIds).") ";
    	}
    
    	if(isset($filter['starttime']) && isset($filter['endtime'])){
    		$starttime = date('Y-m-d 00:00:00',strtotime($filter['starttime']));
    		$endtime   = date('Y-m-d 23:59:59',strtotime($filter['endtime']));
    		$where_sql  .= " AND S.time >= '".$starttime."' AND S.time <='".$endtime."' ";
    	}
    	
    	if (isset($filter['optype']) && !empty($filter['optype'])){
    		
    		if($filter['optype'] == 'login'){
    			$where_sql  .= " AND S.optype IN('login','logout') ";
    		}else{
    			$where_sql  .= " AND S.optype='".$filter['optype']."'";
    		}
    		
    	}
    	// 获取未读日志
    	// if (isset($filter['is_read'])){
    	// 	$filter['is_read'] = empty($filter['is_read']) ? 0 : $filter['is_read'];
    		
    	// 	$where_sql .= " AND S.is_read = ".$filter['is_read'];
    	// }

    	if (isset($filter['keyword']) && !empty($filter['keyword'])){
    		
    		$where_sql .= " AND (S.time LIKE binary \"%{$filter['keyword']}%\"";
    		$where_sql .= " OR U.username LIKE binary \"%{$filter['keyword']}%\"";
            $where_sql .= " OR U.realname LIKE binary \"%{$filter['keyword']}%\"";
    		$where_sql .= " OR S.content LIKE binary \"%{$filter['keyword']}%\")";
    	}

        if (!empty($filter['startDate']) && !empty($filter['endDate'])) {
            $where_sql .= " AND S.time >= '{$filter['startDate']}'";
            $where_sql .= " AND S.time <= '{$filter['endDate']}'";
        }
    	
        if (!empty($filter['typeArr']) && is_array($filter['typeArr'])) {
            $typeStr = implode(',', $filter['typeArr']);
            $where_sql .= " AND S.optype in({$typeStr})";
        }

        if (isset($filter['language'])) {
            $where_sql .= " AND S.language = {$filter['language']}";
        }

        if (!empty($filter['companyid'])) {
            $where_sql .= " AND S.companyid = {$filter['companyid']}";
        }

        if (!empty($filter['userid'])) {
            $where_sql .= " AND S.userid = {$filter['userid']}";
        }

    	$sql = "SELECT {$field},
    				U.username AS username,
    				U.realname AS realname 
    			FROM syslog AS S JOIN user AS U ON S.userid=U.id
    			WHERE 1=1 ";
    	$limit_sql = "";
        if (empty($type) && $limit < 1000) {
            if ($page != null && $limit != null){
                $limit_sql = " LIMIT ".($page-1)*$limit.",".$limit;
            }
        }else{
            if (isset($page) && isset($limit)){
                $limit_sql = " LIMIT ".$page.",".$limit;
            }
        }
    	
    	 
    	$query  = $sql.$where_sql."  ORDER BY time DESC ".$limit_sql;
    	$query  = $this->db->query($query);
    	return  $query->result_array();
    }
    
    // 获取日志总数
	public function getTotal($filter){
    	$where_sql = "";
        
        if (isset($filter['id']) || isset($filter['realname'])  || isset($filter['username'])){
            $arrUserIds = $this->getUserIds($filter);
            if (empty($arrUserIds)){
                $arrUserIds[] = 0;
            }
            $where_sql  .= " AND S.userid IN(".implode(',', $arrUserIds).") ";
        }
    
        if(isset($filter['starttime']) && isset($filter['endtime'])){
            $starttime = date('Y-m-d 00:00:00',strtotime($filter['starttime']));
            $endtime   = date('Y-m-d 23:59:59',strtotime($filter['endtime']));
            $where_sql  .= " AND S.time >= '".$starttime."' AND S.time <='".$endtime."' ";
        }
        
        if (isset($filter['optype']) && !empty($filter['optype'])){
            
            if($filter['optype'] == 'login'){
                $where_sql  .= " AND S.optype IN('login','logout') ";
            }else{
                $where_sql  .= " AND S.optype='".$filter['optype']."'";
            }
            
        }
        // 获取未读日志
        // if (isset($filter['is_read'])){
        //  $filter['is_read'] = empty($filter['is_read']) ? 0 : $filter['is_read'];
            
        //  $where_sql .= " AND S.is_read = ".$filter['is_read'];
        // }

        if (isset($filter['keyword']) && !empty($filter['keyword'])){
            
            $where_sql .= " AND (S.time LIKE binary \"%{$filter['keyword']}%\"";
            $where_sql .= " OR U.username LIKE binary \"%{$filter['keyword']}%\"";
            $where_sql .= " OR U.realname LIKE binary \"%{$filter['keyword']}%\"";
            $where_sql .= " OR S.content LIKE binary \"%{$filter['keyword']}%\")";
        }

        if (!empty($filter['startDate']) && !empty($filter['endDate'])) {
            $where_sql .= " AND S.time >= '{$filter['startDate']}'";
            $where_sql .= " AND S.time <= '{$filter['endDate']}'";
        }
        
        if (!empty($filter['typeArr']) && is_array($filter['typeArr'])) {
            $typeStr = implode(',', $filter['typeArr']);
            $where_sql .= " AND S.optype in({$typeStr})";
        }

        if (isset($filter['language'])) {
            $where_sql .= " AND S.language = {$filter['language']}";
        }

        if (!empty($filter['companyid'])) {
            $where_sql .= " AND S.companyid = {$filter['companyid']}";
        }

        if (!empty($filter['userid'])) {
            $where_sql .= " AND S.userid = {$filter['userid']}";
        }

        $sql = "SELECT count(*) as total 
                FROM syslog AS S JOIN user AS U ON S.userid=U.id
                WHERE 1=1 ";
    	// $limit_sql = "";
    	
    	$query  = $sql.$where_sql;
    	$query  = $this->db->query($query);
    	$row =   $query->row_array();
    	return   $row['total'];
    }
    
	public function getUserIds($filter){
		
		$arrIds = array();
		if (isset($filter['realname']) && !empty($filter['realname'])){
			$sql        = "SELECT id FROM user WHERE realname LIKE '%".$filter['realname']."%' ";
			$query      = $this->db->query($sql);
			$userList   = $query->result_array();

			if (empty($userList)){
				return $arrIds;
			}
			
			foreach ($userList as $v){
				if (!in_array($v['id'], $arrIds)){
					$arrIds[]  = $v['id'];
				}
			}
			return $arrIds;
		}
		
		if (isset($filter['username']) && !empty($filter['username'])){
			$sql        = "SELECT id FROM user WHERE username LIKE '%".$filter['username']."%' ";
			$query  = $this->db->query($sql);
			$userList   = $query->result_array();
			if (empty($userList)){
				return $arrIds;
			}
			
			foreach ($userList as $v){
				if (!in_array($v['id'], $arrIds)){
					$arrIds[]  = $v['id'];
				}
			}
			return $arrIds;
		}
		
		if (isset($filter['id']) && !empty($filter['id'])){
			$sql    = "SELECT id,role FROM user WHERE id={$filter['id']}";
			$query  = $this->db->query($sql);
			$user   = $query->row_array();
			if (empty($user)){
				return $arrIds;
			}
			$arrIds[] = $filter['id'];
			
			// 超级管理员
			if($user['role'] == 1){
				$sql        = "SELECT id FROM user WHERE parentid={$filter['id']}";
				$query      = $this->db->query($sql);
				$userList   = $query->result_array();
				if (empty($userList)){
					return $arrIds;
				}
				
				$arrIds2 = array();
				foreach ($userList as $v){
					$arrIds2[] = $v['id'];
					if (!in_array($v['id'], $arrIds)){
						$arrIds[]  = $v['id'];
					}
				}
				
				$sql        = "SELECT id FROM user WHERE parentid IN (".implode(',', $arrIds2).")";
				$query      = $this->db->query($sql);
				$userList   = $query->result_array();
				if (empty($userList)){
					return $arrIds;
				}
				
				foreach ($userList as $v){
					if (!in_array($v['id'], $arrIds)){
						$arrIds[]  = $v['id'];
					}
				}
				return $arrIds;
			}elseif ($user['role'] == 2){
				$sql        = "SELECT id FROM user WHERE parentid={$filter['id']}";
				$query      = $this->db->query($sql);
				$userList   = $query->result_array();
				if (empty($userList)){
					return $arrIds;
				}
				
				foreach ($userList as $v){
					if (!in_array($v['id'], $arrIds)){
						$arrIds[]  = $v['id'];
					}
					
				}
				$arrIds[]  = $v['id'];
			}else {
				return $arrIds;
			}
		}

	}

}