IDCOL/api/application/models/Dim_model.php

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

    protected $table = 'dim_cmd';

    public function __construct() {
        parent::__construct();
    }
    
    public function queryData($start,$offset){
        $this->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'];

    }
}

?>