db->order_by("id","DESC"); $query = $this->db->get($this->table); return $query->result_array(); } public function getList($filter,$page = null,$limit = null){ if (isset($filter['network'])){ $sql_in = ""; if (isset($filter['keyword']) && !empty($filter['keyword'])){ $lampIds = explode(",",$filter['keyword']); $arrIds = array(); foreach ($lampIds as $id){ if(is_numeric($id) && !empty($id)){ $arrIds[] = trim($id); } } if(!empty($arrIds)){ $sql_in_ids = implode(',',$arrIds); $sql_in =" AND L.number IN (".$sql_in_ids.")"; } } if(isset($filter['status']) && $filter['status'] == 1){ $sql_in .= " AND isfaulted = 1 "; } $sql_limit = ""; if(is_numeric($page) && is_numeric($limit)){ $sql_limit = " LIMIT ".($page-1)*$limit.",".$limit; } $sql = "SELECT L.*, cmd.*, L.`id` as lid, P.`projectname` as projectname, N.`networkname` as networkname FROM lampinfo AS L LEFT JOIN project AS P ON P.id = L.projectid LEFT JOIN network AS N ON N.id = L.networkid LEFT JOIN (SELECT * FROM ".$this->table." ORDER BY updatetime DESC) AS cmd ON L.id = cmd.id WHERE L.networkid=".intval($filter['network'])." {$sql_in} GROUP BY lid order by L.number asc, lid asc {$sql_limit}"; $query = $this->db->query($sql); return $query->result_array(); } if (!empty($filter)){ foreach ($filter as $k => $v) { if($k == 'status' || $k == 'keyword' || $k == 'type'){ continue; } $this->db->where($k,$v); } } if ($this->session->userdata('role') == COMPANY_ADMIN) { $companyid = $this->session->userdata('company_id'); $this->db->where('company', $companyid); } if ($this->session->userdata('role') == COMPANY_CUSTOMER) { $this->load->model('User_model'); $user_project = $this->User_model->get_user_zone(); if ($user_project) { $projects = explode(',', $user_project); $this->db->where_in('id',$projects); } else { $this->db->where_in('id', array(0)); } } $this->db->select('*'); $query = $this->db->get('project'); $project = $query->result_array(); if (empty($project)){ return array(); } $Ids = array(); $projectIds = array(); foreach ($project as $v){ $Ids[] = $v['id']; $projectIds[$v['id']] = $v['projectid']; } $offset = ($page-1)*$limit; $condition = "L.projectid in (".implode(',', $Ids).")"; $sql_in = ""; if (isset($filter['keyword']) && !empty($filter['keyword'])){ $lampIds = explode(",",$filter['keyword']); $arrIds = array(0); foreach ($lampIds as $id){ if(is_numeric($id) && !empty($id)){ $arrIds[] = trim($id); } } if(!empty($arrIds)){ $sql_in_ids = implode(',',$arrIds); $fieldMap = array(0=>'projectid', 1=>'networkid', 2=>'number'); $sql_in .=" AND L.".$fieldMap[$filter['type']]." IN (".$sql_in_ids.")"; } } if(isset($filter['status']) && $filter['status'] == 1){ $sql_in .= " AND isfaulted = 1 "; } $sql = "SELECT L.*, cmd.*, L.`id` as lid, P.`projectname` as projectname, N.`networkname` as networkname FROM lampinfo AS L LEFT JOIN project AS P ON P.id = L.projectid LEFT JOIN network AS N ON N.id = L.networkid LEFT JOIN (SELECT * FROM ".$this->table." ORDER BY updatetime DESC) AS cmd ON L.id = cmd.id WHERE {$condition} {$sql_in} GROUP BY lid order by L.number asc, lid asc limit {$offset}, {$limit}"; $query = $this->db->query($sql); $arrRet = $query->result_array(); return $arrRet; } public function getTotal($filter){ if(isset($filter['network'])){ $this->db->select('count(id) as total'); $this->db->where_in('networkid',array(intval($filter['network']))); if (isset($filter['keyword']) && !empty($filter['keyword'])){ $lampIds = explode(",",$filter['keyword']); $arrIds = array(); foreach ($lampIds as $id){ if(is_numeric($id)){ $arrIds[] = intval(trim($id)); } } if (!empty($arrIds)){ $this->db->where_in('number', $arrIds); } } if(isset($filter['status']) && $filter['status'] == 1){ $this->db->where('isfaulted',1); } $query = $this->db->get('lampinfo'); $arr = $query->row_array(); return $arr['total']; } if (!empty($filter)){ foreach ($filter as $k => $v) { if($k == 'status' || $k == 'keyword' || $k == 'type'){ continue; } $this->db->where($k,$v); } } if ($this->session->userdata('role') == COMPANY_ADMIN) { $companyid = $this->session->userdata('company_id'); $this->db->where('company', $companyid); } if ($this->session->userdata('role') == COMPANY_CUSTOMER) { $this->load->model('User_model'); $user_project = $this->User_model->get_user_zone(); if ($user_project) { $projects = explode(',', $user_project); $this->db->where_in('id',$projects); } else { $this->db->where_in('id', array(0)); } } $this->db->select('id'); $this->db->order_by("id","DESC"); $query = $this->db->get('project'); $project = $query->result_array(); if (empty($project)){ return 0; } foreach ($project as $v){ $Ids[] = $v['id']; } $this->db->select('count(id) as total'); $this->db->where_in('projectid',$Ids); if (isset($filter['keyword']) && !empty($filter['keyword'])){ $lampIds = explode(",",$filter['keyword']); $arrIds = array(0); foreach ($lampIds as $id){ if(is_numeric($id)){ $arrIds[] = intval(trim($id)); } } if (!empty($arrIds)){ $fieldMap = array(0=>'projectid', 1=>'networkid', 2=>'number'); $this->db->where_in($fieldMap[$filter['type']], $arrIds); } } if(isset($filter['status']) && $filter['status'] == 1){ $this->db->where('isfaulted',1); } $query = $this->db->get('lampinfo'); $arr = $query->row_array(); return $arr['total']; } } ?>