IDCOL/api/application/models/Videomonitor_model.php

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

        protected $table = 'video_monitor';

        public function __construct()
        {
            parent::__construct();
        }
        public function get_project_company($condition,$field){
            $this->db->select($field);
            if (!empty($condition)) {
                foreach ($condition as $key => $value) {
                    if (is_array($value)) {
                        if (!empty($value)) {
                            $this->db->where_in($key,$value);
                        }else{
                            $this->db->where_in($key,array(0));
                        }
                    }else{
                        $this->db->where($key,$value);
                    }
                    
                }
            }else{
                return array();
            }

            $this->db->join('project as P','P.id = V.projectid','left');

            return $this->db->get($this->table.' as V')->row_array();
        }

        public function get_data_by_fiter($fiter=array(),$field="*"){
            $this->db->select($field);
            foreach ($fiter as $key => $value) {
                if (is_array($value)) {
                    $this->db->where_in($key,$value);
                }else{
                    $this->db->where($key,$value);
                }
            }
            return $this->db->get($this->table)->row_array();
        }

        // 地图页获取监控列表
        public function get_data_by_location($role, $companyid, $projectid = 0, $userid = 0,$fields='*',$filter=array()){
            $this->db->select($fields, false);
            $projectIds = array();
            if ($projectid > 0) {
                $projectIds[] = $projectid;
                $this->db->where_in('projectid',$projectIds);
            } elseif ($role != SYSTEM_ADMIN) {
                $idStr = $this->get_projectid_by_role($role,$userid,$companyid);
                if (!empty($idStr)) {
                    $projectIds = explode(',', $idStr);
                }else{
                    $projectIds = array(0);
                }
                $this->db->where_in('projectid',$projectIds);
            }

            // 经纬度筛选
            if (isset($filter['lng_low']) && is_numeric($filter['lng_low']) && isset($filter['lng_high']) && is_numeric($filter['lng_high']) && $filter['lng_low'] > $filter['lng_high']) {

                $this->db->group_start();
                $this->db->group_start();
                $this->db->where('longitude >=', $filter['lng_low']);
                $this->db->where('longitude <=', 180);
                $this->db->group_end();
                $this->db->or_group_start();
                $this->db->where('longitude <=', $filter['lng_high']);
                $this->db->where('longitude >=', -180);
                $this->db->group_end();
                $this->db->group_end();
                unset($filter['lng_high']);
                unset($filter['lng_low']);
            }else{
                if(isset($filter['lng_low']) && is_numeric($filter['lng_low'])){
                    $this->db->where('longitude >=', $filter['lng_low']);
                    unset($filter['lng_low']);
                }
                if(isset($filter['lng_high']) && is_numeric($filter['lng_high'])){
                    $this->db->where('longitude <=', $filter['lng_high']);
                    unset($filter['lng_high']);
                }
            }
            if(isset($filter['lat_low']) && is_numeric($filter['lat_low'])){
                $this->db->where('latitude >=', $filter['lat_low']);
                unset($filter['lat_low']);
            }
            if(isset($filter['lat_high']) && is_numeric($filter['lat_high'])){
                $this->db->where('latitude <=', $filter['lat_high']);
                unset($filter['lat_high']);
            }

            if(isset($filter['keyword']) && !empty($filter['keyword'])){
                $this->db->like('name', $filter['keyword']);
            }

            // if(empty($type) && !empty($filter['page']) && !empty($filter['count'])){
            //     $this->db->limit($filter['count'],($filter['page']-1)*$filter['count']);
            //     unset($filter['page']);
            //     unset($filter['count']);
            // }

            // 根据路灯状态来筛选
            // foreach ($filter as $key => $value) {
            //     $this->db->where($key,$value);
            // }
            $this->db->from($this->table);
            // $this->db->join('project as P', 'L.projectid = P.id', 'left');
            // $this->db->join('network as N', 'L.networkid = N.id', 'left');
            // $this->db->join('(select t1.* from alarm_info_log t1, (select lampid, max(updatetime) as maxtime from alarm_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as AI', 'L.id = AI.lampid', 'left');
            
            // $this->db->group_by('L.id');
            // $this->db->order_by("L.number ASC,N.networkname ASC");
            $query = $this->db->get();
            // if (empty($type)) {
                
            // }else{
            //     return $query->num_rows();
            // }
            return $query->result_array();
        }

        // 获取视屏详情
        public function get_video_by_id($id,$field='*'){
            $sql = "select {$field} from {$this->table} as V 
                    where V.id = {$id}";

            return $this->db->query($sql)->row_array();
        }

        public function get_one_by_role($role, $companyid, $projectid = 0, $userid = 0,$fields='*',$keyword){
            $this->db->select($fields, false);
            $projectIds = array();
            if ($projectid > 0) {
                $projectIds[] = $projectid;
                $this->db->where_in('projectid',$projectIds);
            } elseif ($role != SYSTEM_ADMIN) {
                $idStr = $this->get_projectid_by_role($role,$userid,$companyid);
                if (!empty($idStr)) {
                    $projectIds = explode(',', $idStr);
                }else{
                    $projectIds = array(0);
                }
                $this->db->where_in('projectid',$projectIds);
            }

            if (!empty($keyword)) {
                $this->db->like('name',$keyword);
            }

            $this->db->from($this->table);
            $query = $this->db->get();
            return $query->row_array();
        }

        // (首页)获取首页视屏监控列表
        public function getList($role, $companyid, $projectid, $userid,$page=null,$limit = null){

            if ($projectid > 0) {   // 有项目id
                
                $condition = "projectid = {$projectid}";
            } elseif ($role == SYSTEM_ADMIN) {  // 系统管理员，所有项目
                $condition = "1=1";
            } else {

                $projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
                if (empty($projectIds)) {
                    $condition = "projectid in (0)";
                }else{
                    $condition = "projectid in ({$projectIds})";
                }
                
            }

            $sql = "select address,id,devid,password,type,channel,image from video_monitor WHERE {$condition} order by createtime desc";
            if ($page != null && $limit != null){
                $sql .= " LIMIT ".($page-1)*$limit.",".$limit;
            }
            $res = $this->db->query($sql);
            return $res->result_array();
        }

        // 根据条件获取监控数
        public function getTotal($filter=array(),$role,$user_id,$companyid,$project_id=0){

            $sql = "SELECT count(*) as total from {$this->table} ";

            if (isset($filter['keyword'])) {
                $implode[] = "name like '%{$filter['keyword']}%'";
                unset($filter['keyword']);
            }

            foreach ($filter as $key => $value) {
                $implode[] = "$key=$value";
            }

            if ($project_id > 0) {
                $projectIds = $project_id;
            }else{
                $projectIds = $this->get_projectid_by_role($role,$user_id,$companyid);
            }

            if(empty($projectIds)){
                $implode[] = "projectid in (0)";
            }else{
                $implode[] = "projectid in ({$projectIds})";
            }
            $where = $implode ? " WHERE " . implode(" AND ", $implode) : '';

            $sql .= $where;
            $res = $this->db->query($sql);
            $data = $res->row_array();
            return $data["total"];
        }

        // 根据用户权限获取视屏监控列表
        public function get_list_by_role($role,$userid,$companyid,$field="*",$page=null,$limit = null,$projectid=0,$fiter=array()){
            if ($projectid > 0) {
                $idStr = $projectid;
            }else{
                $idStr = $this->get_projectid_by_role($role,$userid,$companyid);
                $idStr = empty($idStr) ? '0' : $idStr;
            }

            $sql = "select {$field} 
                    from video_monitor as V 
                    where V.projectid in ({$idStr})";
            if (isset($fiter['keyword'])) {
                $sql .= " AND V.name like '%{$fiter['keyword']}%'";
                unset($fiter['keyword']);
            }
            if ($page != null && $limit != null){
                $sql .= " LIMIT ".($page-1)*$limit.",".$limit;
            }
            $res = $this->db->query($sql);
            $data = $res->result_array();
            return $data;
        }

        // 根据刷选条件获取视屏监控列表
        public function get_list_by_filter($filter,$field='*'){

            $temp = [];
            $sql = "select {$field} 
                    from video_monitor as V ";

            foreach ($filter as $key => $value) {
                if (is_array($value)) {
                    if (!empty($value)) {
                        $v = implode(',', $value);
                        $temp[] = "V.{$key} in ($v)";
                    }else{
                        $temp[] = "V.{$key} in (0)";
                    }
                }else{
                    $temp[] = "V.{$key} = '{$value}'";
                }
            }
            if (!empty($temp)) {
                $where = implode(' AND ', $temp);
                $sql .= "where ".$where;
            }

            $data = $this->db->query($sql)->result_array();
            return $data;
        }

        // 通过筛选条件删除监控
        public function delData($condition){
            if (!empty($condition)) {
                foreach ($condition as $key => $value) {
                    if (is_array($value)) {
                        if (!empty($value)) {
                            $this->db->where_in($key,$value);
                        }else{
                            $this->db->where_in($key,array(0));
                        }
                    }else{
                        $this->db->where($key,$value);
                    }
                    
                }
            }else{
                return false;
            }

            $this->db->delete($this->table);
            if ($this->db->affected_rows()) {
                return true;
            } else {
                return false;
            }
        }

        // 通过筛选条件删除监控日志
        public function del_video_info_log($fiter=array()){
            if (empty($fiter)) {
                return array();
            }
            $implode = array();
            foreach ($fiter as $key => $value) {
                if (is_array($value)) {
                    if (!empty($value)) {
                        $v = implode(',', $value);
                        $implode[] = "$key in ($v)";
                    }
                }else{
                    $implode[] = "$key = $value";
                }
            }

            $where = " where ".implode(' AND ', $implode);

            // 删除视屏文件
            $sql1 = "select video_path as path from video_info_log {$where}";
            $data = $this->db->query($sql1)->result_array();
            foreach ($data as $v) {
                @unlink($v['path']);
            }

            // 删除数据记录
            $sql2 = "delete from video_info_log {$where}";
            $this->db->query($sql2);
            if ($this->db->affected_rows()) {
                return true;
            }else{
                return false;
            }
        }

        // 添加NVR设备
        public function insertNVR($data){
            $this->db->insert('nvrdevice', $data);
            return $this->db->insert_id();
        }

        // 获取NVR设备id
        public function getNVRCount($condition){
            $this->db->select('id');
            if (!empty($condition)){
                foreach ($condition as $k => $v) {
                    $this->db->where($k,$v);
                }
            }
            $query = $this->db->get('nvrdevice');
            $data = $query->row_array();        
            if (empty($data)) {
                return 0;
            } else { 
                return $data['id'];
            }
        }
        
        public function getDataCount($condition, $id = 0) {
            if (!empty($condition)){
                foreach ($condition as $k => $v) {
                    $this->db->where($k,$v);
                }
            }
            if (!empty($id)) {
                $this->db->where('id !=',$id); 
            }  
            $query = $this->db->get($this->table);
            $data = $query->row_array();        
            if (empty($data)) {
                return 0;
            } else { 
                return $id == $data['id'] ? 0 : 1;
            }
        }
    }
?>