IDCOL/api/application/models/Project_model.php

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

	protected $table = 'project';

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

	// 通过项目来获取时区
	public function get_timezone_by_projectid($id){
		$sql = "select T.value FROM {$this->table} as P left join timezone as T on P.timezone = T.id where P.id = {$id}";
		return $this->db->query($sql)->row_array();
	}

	// 通过项目名称判断项目是否存在
	public function project_exist_by_name($role,$userid,$companyid,$project_name,$company_name){
		$projectids = $this->get_projectid_by_role($role,$userid,$companyid);
		if (empty($projectids)) {
			return 0;
		}else{
			$idArr = explode(',', $projectids);
			$this->db->select('P.id');
			$this->db->where_in('P.id',$idArr);
			$this->db->where('P.projectname',$project_name);
			$this->db->where('C.name',$company_name);
			$this->db->join('company as C', 'C.id = P.company');
			$res = $this->db->get($this->table.' as P')->row_array();
			if (empty($res)) {
				return 0;
			}else{
				return $res['id'];
			}
		}
	}

	// 获取时区信息
	public function get_timezone_info($fiter=array(),$field='*'){
		$this->db->select($field);
		if (!empty($fiter)) {
			foreach ($fiter as $key => $value) {
				$this->db->where($key,$value);
			}
		}
		return $this->db->get('timezone')->row_array();
	}

	// 获取时区列表
	public function timezone_list($fields = '*'){
		$this->db->select($fields);
		return $this->db->get('timezone')->result_array();
	}

	// 项目用电量，年月日
	private function project_data($projectid){
		$res = array();

		// 获取总的用电量，当天总用电量
		$today = date('Y-m-d 00:00:00');
		$sql = "SELECT sum(elog.totalconsumption) as totalconsumption,sum(elog.totalgeneration) as totalgeneration FROM project as P 
				 join lampinfo as L on L.projectid = P.id 
				 join (select t1.* FROM electric_info_log as t1 join (select lampid,max(updatetime) as time FROM electric_info_log group by lampid) as t2 on t1.updatetime = t2.time and t1.lampid = t2.lampid) as elog on elog.lampid = L.id 
				where P.id = {$projectid} 
				group by P.id ";
		$total = $this->db->query($sql)->row_array();
		$total['totalconsumption'] = empty($total['totalconsumption']) ? 0 : $total['totalconsumption'];
		$total['totalgeneration'] = empty($total['totalgeneration']) ? 0 : $total['totalgeneration'];

		$sql = "SELECT sum(elog.dayconsumption) as dayconsumption,sum(elog.daygeneration) as daygeneration FROM project as P 
				 join lampinfo as L on L.projectid = P.id 
				 join (select t1.* FROM electric_info_log as t1 join (select lampid,max(updatetime) as time FROM electric_info_log where updatetime >= '{$today}' group by lampid) as t2 on t1.updatetime = t2.time and t1.lampid = t2.lampid) as elog on elog.lampid = L.id 
				where P.id = {$projectid} 
				group by P.id ";
		$today = $this->db->query($sql)->row_array();
		$res['dayconsumption'] = empty($today['dayconsumption']) ? 0 : $today['dayconsumption'];
		$res['daygeneration'] = empty($today['daygeneration']) ? 0 : $today['daygeneration'];

		//当月第一天和当年第一天
		$beginDateMonth = date('Y-m-01 00:00:00', time());
		$beginDateYear  = date('Y-01-01 00:00:00', time());

		// 当月总发/用电量
		$sql = "SELECT sum(elog.totalconsumption) as totalconsumption,sum(elog.totalgeneration) as totalgeneration FROM project as P 
				 join lampinfo as L on L.projectid = P.id 
				 join (select t1.* FROM electric_info_log as t1 join (select lampid,max(updatetime) as time FROM electric_info_log where updatetime <= '{$beginDateMonth}' group by lampid) as t2 on t1.updatetime = t2.time and t1.lampid = t2.lampid) as elog on elog.lampid = L.id 
				where P.id = {$projectid} 
				group by P.id ";
		$month = $this->db->query($sql)->row_array();
		$month['totalconsumption'] = empty($month['totalconsumption']) ? 0 : $month['totalconsumption'];
		$month['totalgeneration'] = empty($month['totalgeneration']) ? 0 : $month['totalgeneration'];
		$res['monthconsumption'] = $total['totalconsumption'] - $month['totalconsumption'];
		$res['monthgeneration'] = $total['totalgeneration'] - $month['totalgeneration'];

		// 当年总用电量
		$sql = "SELECT sum(elog.totalconsumption) as totalconsumption,sum(elog.totalgeneration) as totalgeneration FROM project as P 
				 join lampinfo as L on L.projectid = P.id 
				 join (select t1.* FROM electric_info_log as t1 join (select lampid,max(updatetime) as time FROM electric_info_log where updatetime <= '{$beginDateYear}' group by lampid) as t2 on t1.updatetime = t2.time and t1.lampid = t2.lampid) as elog on elog.lampid = L.id 
				where P.id = {$projectid} 
				group by P.id ";
		$year = $this->db->query($sql)->row_array();
		$year['totalconsumption'] = empty($year['totalconsumption']) ? 0 : $year['totalconsumption'];
		$year['totalgeneration'] = empty($year['totalgeneration']) ? 0 : $year['totalgeneration'];
		$res['yearconsumption'] = $total['totalconsumption'] - $year['totalconsumption'];
		$res['yeargeneration'] = $total['totalgeneration'] - $year['totalgeneration'];
		return $res;
	}

	// 通过路灯id获取项目信息
	public function get_data_by_field($field,$value){
		$fields = "P.projectname as project_name,
				  P.projectid as project_no,
				  P.createtime as create_time,
				  z1.name as zone,
				  z2.name as province,
				  C.name as company,
				  C.id as companyid,
				  P.id,
				  P.lampcount as install_num";
		$sql = "SELECT {$fields} FROM project as P 
				left join zone as z1 on z1.id = P.zone 
				left join zone as z2 on z2.id = P.province 
				left join company as C on C.id = P.company 
				left join lampinfo as L on L.projectid = P.id 
				where {$field} = '{$value}'";

		$res1 = $this->db->query($sql)->row_array();

		// 获取项目的日用电量，月用电量，年用电量
		$res2 = $this->project_data($res1['id']);
		$data = array_merge($res1,$res2);
		return $data;
	}
	
	public function queryData($companyid = 0){
		if ($this->session->userdata('role') == COMPANY_ADMIN) {
			$companyid = $this->session->userdata('company_id');
			$this->db->where('company', $companyid);
		} else 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));
			}
		} else if ($companyid > 0) {
			$this->db->where('company', $companyid);
		}
		
		$this->db->order_by("projectid","asc");
		$query = $this->db->get($this->table);
		return $query->result_array();
	}

    public function get_list_in($where_in_field, $where_in, $fields='*', $filter=array()) {
        $this->db->select($fields);
        if(!empty($where_in_field) && !empty($where_in)){
            $this->db->where_in($where_in_field, $where_in);
        }
        
		if (isset($filter['keyword']) && !empty($filter['keyword'])){
			$networkNames = explode(",",$filter['keyword']);
			foreach ($networkNames as $name){
				$this->db->or_like('projectname', $name);
			}
		}
    
        $query = $this->db->get($this->table);
        return $query->result_array();
    }

	public function getIdsLikeName($name){			
		$this->db->select('id');
		$this->db->like('projectname', $name); 
		$query = $this->db->get($this->table);
		$arrProject =  $query->result_array();
		$ids = array();

		foreach ($arrProject as $k=> $project) {
			$ids[] = $project['id'];
		}

		return $ids;
	}
	
	public function getIdByName($name){
		$this->db->select('id');
		$this->db->where('projectname', $name);
		$query  = $this->db->get($this->table);
		$result =  $query->row_array();

		if (isset($result['id'])) {
			return $result['id'];
		}
		return false;
	}

	public function get_project_ids($filter=array()){
		$temp = array();
		if (!empty($filter)){
			foreach ($filter as $k => $v) {
				$temp[] = "{$k} = {$v}";
			}
		}
		$where = '';
		if (!empty($temp)) {
			$where = ' where '.implode(' and ', $temp);
		}
		
		$sql = "select id from {$this->table} ".$where;
		$query = $this->db->query($sql);
		$arrProject =  $query->result_array();
		$ids = array_column($arrProject, 'id');

		return $ids;
	}

	public function get_projectid_list(){
		$this->db->select('id');
		$this->db->select('projectid');

		$query = $this->db->get($this->table);
		$list =  $query->result_array();
		return $list;
	}

	public function get_projectid($role,$userid,$companyid,$page,$count,$keyword){
		$sql = '';
        if ($role == SYSTEM_ADMIN) {  // 系统管理员
            $sql = "select id as projectid from project where 1=1";
        }
        // elseif ($role == COMPANY_ADMIN) {  // 公司管理员
        //     if(!empty($companyid)){
        //         $sql = "select id as projectid from project where company={$companyid}";
        //     }
        // }
        else{  // 公司客户
            $sql = "select zone as projectid from user where id={$userid}";
            $zone = $this->db->query($sql)->row_array();
            $zone['zone'] = empty($zone['zone']) ? '0' : $zone['zone'];
            $sql = "select id as projectid from project where id in ({$zone['zone']})";
        }
        if (!empty($keyword)) {
        	$sql .= " and projectname like '%{$keyword}%'";
        }
        if (!empty($sql)) {
            $data = $this->db->query($sql)->result_array();
            $idArr = array();
            foreach ($data as $v) {
                $idArr[] = $v['projectid'];
            }
            $idStr = implode(',', $idArr);
            $ids = explode(',', $idStr);
            $k = 0;
            $i = 0;
            foreach ($ids as $v) {  // 分页处理
            	$res[$k][] = $v;
            	$i ++;
            	if ($i >= $count) {
            		$i = 0;
            		$k ++;
            	}
            }
            return array('idArr'=>$res[$page-1],'total'=>count($ids));
        }else{
            return '';
        }
	}
	
	public function getList($filter,$page = null,$limit = null){
		if (!empty($filter)){   
			if (isset($filter['keyword']) && !empty($filter['keyword'])){
				$projectNames = explode(",",$filter['keyword']);
				foreach ($projectNames as $name){
					$this->db->or_like('projectname', $name);
				}
				unset($filter['keyword']);
			}
			
			if (isset($filter['keyword'])){
				unset($filter['keyword']);
			}
			
			if (isset($filter['ids']) && !empty($filter['ids']) && is_array($filter['ids'])){
				$this->db->where_in('id',$filter['ids']);
				unset($filter['ids']);
			}

			foreach ($filter as $k => $v) {
				if ($k == 'id' && empty($v)){
					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->order_by("projectid","asc");
		if(is_numeric($page) && is_numeric($limit)){
			$this->db->limit($limit,($page-1)*$limit);
		}
		
		
		$query = $this->db->get($this->table);
		$arrProject =  $query->result_array();

		$zoneids = array(0);
		foreach ($arrProject as $k=> $project) {
			$zoneids[] = $project['zone'];
			$zoneids[] = $project['province'];
		}
		$zoneids = array_unique($zoneids);

		$this->db->select('id');
		$this->db->select('name');
		$this->db->where_in('id', $zoneids);
		$query = $this->db->get('zone');
		$zoneMap  = $query->result_array();
		
		foreach ($arrProject as $pk=> $project) {
			$arrProject[$pk]['zone_name'] = '';
			$arrProject[$pk]['province_name'] = '';
			foreach ($zoneMap as $zk => $zone) {
				if ($project['zone'] == $zone['id']) {
					$arrProject[$pk]['zone_name'] = $zone['name'];
				} elseif ($project['province'] == $zone['id']) {
					$arrProject[$pk]['province_name'] = $zone['name'];
				}
			}
		}
		
		return $arrProject;
	}
	
	public function getTotal($filter, $role, $companyid, $userid = 0){
		$this->db->select('count(id) as total');
		if (!empty($filter)){
			if (isset($filter['keyword']) && !empty($filter['keyword'])){
				$projectNames = explode(",",$filter['keyword']);
				foreach ($projectNames as $name){
					$this->db->or_like('projectname', $name);
				}

				unset($filter['keyword']);
			}
			
			if (isset($filter['keyword'])){
				unset($filter['keyword']);
			}
			
			if (isset($filter['ids']) && !empty($filter['ids']) && is_array($filter['ids'])){
				$this->db->where_in('id',$filter['ids']);
				unset($filter['ids']);
			}

			foreach ($filter as $k => $v) {
				$this->db->where($k,$v);
			}
		}

		// if ($role == COMPANY_ADMIN) {
		// 	$this->db->where('company', $companyid);
		// }
		
		if ($role == COMPANY_CUSTOMER || $role == COMPANY_ADMIN) {
            $this->load->model('User_model');
            $user_project = $this->User_model->get_user_zone($userid);
			if (!empty($user_project)) {
				$projects = explode(',', $user_project);
				$this->db->where_in('id',$projects);
			} else {
				$this->db->where_in('id', array(0));
			}
		}
		 
		$query = $this->db->get($this->table);
		$arr   =  $query->row_array();
		return $arr['total'];
	}
	
	public function get_company_by_id($id){
		$this->db->select('company');
		$this->db->where('id',$id);
		$data = $this->db->get($this->table)->row_array();
		return $data['company'];
	}
	
	public function delBatch($ids){
		$this->db->where_in('id',$ids);
		$this->db->delete($this->table);
		if ($this->db->affected_rows()) {
			return true;
		} else {
			return false;
		}
	}
	
	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;
		}
	}

	public function getData($condition, $fields = '*') {
        $this->db->select($fields);
		if (!empty($condition)){
			foreach ($condition as $k => $v) {
				$this->db->where($k,$v);
			}
		}
		$query = $this->db->get($this->table);
		$data = $query->row_array();      
		return $data;
	}

	public function get_list_by_company($field, $role, $companyid, $projectid){
		$this->db->select($field);

		if ($role == SYSTEM_ADMIN) {
			$this->db->where('company',$companyid);
		}else{
			$this->db->where('company',$companyid);
			if (!empty($projectid)) {
				$idArr = explode(',', $projectid);
			}else{
				$idArr = array(0);
			}
			$this->db->where_in('id',$idArr);
		}

		return $this->db->get($this->table)->result_array();
	}

	public function getMultiData($filter, $fields = '*', $role = 0, $companyid = 0, $userid = 0) {
        $this->db->select($fields);
		if (!empty($filter)){
			if (isset($filter['keyword']) && !empty($filter['keyword'])){
				$projectNames = explode(",",$filter['keyword']);
				foreach ($projectNames as $name){
					$this->db->or_like('P.projectname', $name);
				}
				unset($filter['keyword']);
			}
			
			if (isset($filter['keyword'])){
				unset($filter['keyword']);
			}

			if(!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 $k => $v) {
				$this->db->where($k,$v);
			}
		}

		// if ($role == COMPANY_ADMIN) {
		// 	$this->db->where('company', $companyid);
		// } else
		if ($role == COMPANY_CUSTOMER || $role == COMPANY_ADMIN) {
            $this->load->model('User_model');
            $user_project = $this->User_model->get_user_zone($userid);
			if (!empty($user_project)) {
				$projects = explode(',', $user_project);
				$this->db->where_in('P.id',$projects);
			} else {
				$this->db->where_in('P.id', array(0));
			}
		}
		$this->db->join('zone as Z','on Z.id = P.zone','left');
		$this->db->join('zone as S','on S.id = P.province','left');

		$query = $this->db->get($this->table.' as P');
		
		$data = $query->result_array();      
		return $data;
	}

	// 添加路灯数
	public function add_lamp_count($data){
		$sql = "update `project` set `lampcount` = `lampcount` + 1, `faultcount` = `faultcount` + {$data['faultcount']}  WHERE `id`={$data['projectid']}";
		$this->db->query($sql);
		if ($this->db->affected_rows() == 1) {
			return true;
		} else {
			return false;
		}
	}

	// 减少路灯数
	public function minus_lamp_count($data){
		$sql = "update `project` set `lampcount` = `lampcount` - 1, `faultcount` = `faultcount` - {$data['faultcount']}  WHERE `id`={$data['projectid']} and `lampcount` >= 1 and `faultcount` >= {$data['faultcount']}";
		$this->db->query($sql);
		if ($this->db->affected_rows() == 1) {
			return true;
		} else {
			return false;
		}
	}

	// 添加网络数
	public function add_network_count($data){
		$sql = "update `project` set `networkcount` = `networkcount` + 1, `lampcount` = `lampcount` + {$data['lampcount']}, `faultcount` = `faultcount` + {$data['faultcount']}  WHERE `id`={$data['projectid']}";
		$this->db->query($sql);
		if ($this->db->affected_rows() == 1) {
			return true;
		} else {
			return false;
		}
	}

	// 减少网络数
	public function minus_network_count($data){
		$sql = "update `project` set `networkcount` = `networkcount` - 1, `lampcount` = `lampcount` - {$data['lampcount']}, `faultcount` = `faultcount` - {$data['faultcount']}  WHERE `id`={$data['projectid']} and `networkcount` >= 1 and `lampcount` >= {$data['lampcount']} and `faultcount` >= {$data['faultcount']}";
		$this->db->query($sql);
		if ($this->db->affected_rows() == 1) {
			return true;
		} else {
			return false;
		}
	}
	
	public function getIdByLikeName($name){
		$sql   = "SELECT id FROM project WHERE projectname LIKE '%".$name."%'";
		$query = $this->db->query($sql);
		$arr   =  $query->row_array();
		if (!empty($arr)){
			return $arr['id'];
		}
		return false;		
	}

	// 添加监控数
	public function add_monitor_count($data){
		$sql = "update `project` set `monitorcount` = `monitorcount` + {$data['count']} WHERE `id`={$data['projectid']}";
		$this->db->query($sql);
		if ($this->db->affected_rows() == 1) {
			return true;
		} else {
			return false;
		}
	}

	// 减少监控数
	public function minus_monitor_count($data){
		$sql = "update `project` set `monitorcount` = `monitorcount` - {$data['count']} WHERE `id`={$data['projectid']} and `monitorcount` >= {$data['count']}";
		$this->db->query($sql);
		if ($this->db->affected_rows() == 1) {
			return true;
		} else {
			return false;
		}
	}

    public function getNameById($id, $field = 'projectname'){
        $this->db->where('id',$id);
        $query = $this->db->get($this->table);
        $arr = $query->row_array();
        return !empty($arr) ? $arr[$field] : 0;
    }

    // 减少路灯故障数
	public function minus_fault_count($id){
		$sql = "update `project` set `faultcount` = `faultcount` - 1  WHERE `id`= {$id} and `faultcount` >= 1";
		$this->db->query($sql);
		if ($this->db->affected_rows() == 1) {
			return true;
		} else {
			return false;
		}
	}

	public function delData($condition) {
		if (!empty($condition)){
			foreach ($condition as $k => $v) {
				if (is_array($v)) {
					if (!empty($v)) {
						$this->db->where_in($k,$v);
					}else{
						$this->db->where_in($k,array(0));
					}
				}else{
					$this->db->where($k,$v);
				}
			}
		}else{
			return false;
		}
		$this->db->delete($this->table);
		if ($this->db->affected_rows() > 0) {
			return true;
		} else {
			return false;
		}
	}

}

?>