<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

    			/**
     * CodeIgniter
     * @package         Survey
     * @author          Reshma Rajan
     * @since           Version 1.0
     * Date:            27-09-2017 
     * Description:     User Model
     */ 
    class User_model extends CI_Model 
    {

        function __construct() {
            parent::__construct();
            $this->load->database();
            
		}
	public function get_location_version()
			{
				$this->db->initialize();
				$this->db->select('id, loc_version');
				$this->db->from('location_version_master');
				$this->db->limit(1);
				$query = $this->db->get();
				$result	=	$query->result_array();
				$this->db->close();
				return $result;				
			}
		public function login_data($data)
		{
			$this->db->initialize();
         	$username = $data['username'];
			//$password = md5($data['password']);
			$password = hash('SHA512',$data['password'],false);
			$result = $this->db->query("SELECT id, name, username,email, password,created_at, update_date, role, status,district_id,taluk_id,block_id,muncipality,corporation,cds,ads"
					. " FROM login WHERE  status=".ACTIVE." AND  username = ".$this->db->escape($username)." AND password = ".$this->db->escape($password))->result_array();
			$this->db->close();
			return $result;
		}
		public function login_data_app($data)
		{
			$this->db->initialize();
         //print_r($data);
			$username = $data['username'];
		    $password = hash('SHA512',$data['password'],false);
			/*$result = $this->db->query("SELECT id, name, username, password, role, status,district_id,taluk_id,block_id,muncipality,corporation,cds,place_category"
					. " FROM login WHERE status=".ACTIVE." AND  username = ".$this->db->escape($username)." AND BINARY password = ".$this->db->escape($password))->first_row('array');
			return $result;*/
			$this->db->select('id, name, username, password,role, status,district_id,taluk_id,block_id,muncipality,corporation,cds,ads,place_category,constitution,panchayath_id');
			$this->db->from('login');
			$this->db->where('status',ACTIVE);
			$this->db->where('username',$username);
			$this->db->where('password',$password);
			//$query = $this->db->get();
			//return $query->first_row('array');
			$query = $this->db->get();
			//return $query->result_array();
			$result	=	$query->result_array();
			$this->db->close();
			return $result;

		
		}
		function getAgent($did)
			{
				$this->db->initialize();
				$this->db->select('id, name, status');
				$this->db->from('login');
				$this->db->where('district_id', $did);
				$this->db->where('status',ACTIVE);
				$this->db->order_by("name","asc");
				$query = $this->db->get();
				$result	=	$query->result_array();
				$this->db->close();
				return $result;
			}
		function ration_card_color($rid)
			{
				$this->db->initialize();
				$this->db->select('id, title, status');
				$this->db->from('ration_card_category_master');
				$this->db->where('id', $rid);
				$this->db->where('status',ACTIVE);
				$query = $this->db->get();
				$result	=	$query->result_array();
				$this->db->close();
				return $result;
			}
		public function getLocationByName($location,$type)
			{
				$this->db->initialize();
				$this->db->select('id, name, type');
				$this->db->from('location_settings_master');
				$this->db->where('type',$type);
				//$this->db->where('name',$location);
				$this->db->like('name', $location);
				$query = $this->db->get();
				//return $query->first_row('array');
				$result	=	$query->first_row('array');
				$this->db->close();
				return $result;
			}
		public function getLocationById($lid,$type)
			{
				$this->db->initialize();
				$this->db->select('id, name, type');
				$this->db->from('location_settings_master');
				$this->db->where('type',$type);
				//$this->db->where('name',$location);
				$this->db->where('id', $lid);
				$query = $this->db->get();
				//return $query->result_array();
				$result	=	$query->result_array();
				$this->db->close();
				return $result;
			}
		public function getLocation($type,$id,$action)
			{
				$this->db->initialize();
				$this->db->select('id, name, type');
				$this->db->from('location_settings_master');
				$this->db->where('status',ACTIVE);
				if(!empty($id))
				{
					$this->db->where('parent', $id);
				}
				if($action == 'block')
				{
					$this->db->where('type',$action);
				}
				else
				{
					$this->db->where('type',$type);
				}
				$query = $this->db->get();
				//return $query->result_array();
				$result	=	$query->result_array();
				$this->db->close();
				return $result;
			}		
	function taluk()
    {
		$this->db->initialize();
    	$this->db->select('taluk_all.id,taluk_all.taluk_name,taluk_all.status');
		$this->db->from('taluk_all2');
		$this->db->where('taluk_all.status',1);
		$query = $this->db->get();
		//return $query->result_array();
		$result	=	$query->result_array();
		$this->db->close();
		return $result;
    }	
		
		
	function check_email($data)
    {
		$this->db->initialize();
    	$this->db->select('login.id,login.name,login.username,login.password,login.role,login.status');
		$this->db->from('login');
		$this->db->where('login.username',$data['username']);
		$query = $this->db->get();
		//return $query->result_array();
		$result	=	$query->result_array();
		$this->db->close();
		return $result;
    }	
		 function check_user($data)
    {
		$this->db->initialize();
    	$this->db->select('login.id,login.name,login.username,login.password,login.created_at,login.update_date,login.role,login.status,login.taluk,login.panchayath');
		$this->db->from('login');
		$this->db->where('login.id',$data['id']);
		$query = $this->db->get();
		//return $query->first_row('array');
		$result	=	$query->first_row('array');
		$this->db->close();
		return $result;
    }
    function get_datum($table,$selection,$where)
    {
        $this->db->initialize();    
		$this->db->select($selection);
		$this->db->from($table);
		if ((is_array($where) && count($where) > 0) or ( !is_array($where) && trim($where) != ''))
        $this->db->where($where);
    	$query = $this->db->get();
		$data = $query->first_row('array');
		$this->db->close();
		return $data;
    }

    /**
     * CodeIgniter
     * @package         Survey
     * @author          Reshma Rajan
     * @since           Version 1.0
     * Date:            27-09-2017 
     * Description:     Function to get data from table
     */
    function get_data($table, $fields = '*', $where = array(), $type = TRUE, $order_by = '',$joins = array(),$limit = array(),$like = array(),$or_like = array(),$search_value = NULL,$search_like = NULL,$where_in = NULL,$where_in_data = NULL,$group_by = NULL,$escapespl = NULL) {
        $this->db->initialize();
		if ((is_array($where) && count($where) > 0) or ( !is_array($where) && trim($where) != ''))
            $this->db->where($where);
        if (is_array($joins) && count($joins) > 0)
        {
            foreach($joins as $k => $v)
            {
                $this->db->join($v['table'], $v['condition'], $v['jointype']);
            }
        }
        //like clause
        if (is_array($like) && count($like) > 0)
        {   
            $this->db->group_start();
            foreach($like as $k => $v)
            {
                $this->db->like($v['column'], $v['value']);
            }
            $this->db->group_end();
        }
        // or_like clause
        if (is_array($or_like) && count($or_like) > 0)
        {   
            $this->db->group_start();
            foreach($or_like as $k => $v)
            {
                $this->db->or_like($v['column'], $v['value']);
            }
            $this->db->group_end();
        }
        //search a value in different column
        if($search_value != NULL && $search_value != ''){
            if (is_array($search_like) && count($search_like) > 0)
            {
             $this->db->group_start();
                foreach($search_like as $k )
                {  
                    $this->db->or_like($k,$search_value);                   
                }
              $this->db->group_end(); 
            }
        } 
        //Where in clause
        if($where_in != NULL && (!empty($where_in_data))){
         $this->db->where_in($where_in,$where_in_data);
        }
        if(is_array($limit) && count($limit) > 0){
            $this->db->limit($limit['length'],$limit['start']);
        }
        if ($order_by)
            $this->db->order_by($order_by);
        if ($group_by)
            $this->db->group_by($group_by);
        if ($escapespl == TRUE)
        {
            $this->db->select($fields,FALSE);
        }
        else
        {
            $this->db->select($fields);
        }
        $query = $this->db->get($table);
        if ($query->num_rows() > 0) {
            if ($type) {
                //return $query->result();
				$data = $query->result();
				$this->db->close();
				return $data;
            } else {
                //return $query->result_array();
				$data = $query->result();
				$this->db->close();
				return $data;
            }
        } else {
			$this->db->close();
            return FALSE;
        }
    }
			
					
	
		/*public function get_data($data)
		{
			    $userId = $data['userId'];
				$paginationcount = $data['paginationcount'];
				$page = $data['page'];
				
				$num=$paginationcount;
				$offset=$paginationcount*$page;
				
				$this->db->limit($num,$offset);
			    $this->db->select('*');
				$this->db->from('user_data');
                $this->db->where('userId', $userId );
				
							
				$query = $this->db->get();

				if ( $query->num_rows() > 0 )
				{
					$row = $query->result_array();
					return $row;
				}
		}*/
		
	
	function update_user_status($data,$id)
		{
			$this->db->initialize();
			$this->db->set($data);
			$this->db->where('id',$id);
			$this->db->update('login');
			//return $this->db->affected_rows();
			$data = $this->db->affected_rows();
			$this->db->close();
			return $data;
		}	

	 function update_user_details($data,$id)
    {
		$this->db->initialize();
    	$this->db->set($data);
    	$this->db->where('id',$id);
    	$this->db->update('login');
    	//return $this->db->affected_rows();
		$data = $this->db->affected_rows();
		$this->db->close();
		return $data;
    }

     function update_survey($data,$id)
    {
		$this->db->initialize();
    	$this->db->set($data);
    	$this->db->where('family_id',$id);
    	$this->db->update('survey_family');
    	//return $this->db->affected_rows();
		$data = $this->db->affected_rows();
		$this->db->close();
		return $data;
    }
		
		
		
		
		
		public function common_get_pipeline($table, $columns,$condtion = NULL,$search_value = NULL,$type = NULL,$district = NULL,$searchQuery = NULL,$searchQuery1 = NULL,$search_like = NULL,$joins,$limit,$order_by = NULL,$where_in = NULL,$where_in_data = NULL)
			{
				//$this->db->initialize();
				$this->db->select($columns,false)->from($table);
				if (is_array($joins) && count($joins) > 0)
				{
					foreach($joins as $k => $v)
					{
						$this->db->join($v['table'], $v['condition'], $v['jointype']);
					}
				}
				if($condtion != NULL){
					if (is_array($condtion) && count($condtion) > 0)
					{
						foreach($condtion as $k => $v)
						{  
							 if($v['value'] != '' && $v['value'] != NULL){
								$this->db->where($v['key'], $v['value']);
							 }
						}
					}
				}
				
				$total_count = COUNT($this->db->get()->result_array());
				
				//Getting values for DataTale

				$this->db->select($columns,false)->from($table);
				if (is_array($joins) && count($joins) > 0)
				{
					foreach($joins as $k => $v)
					{
						$this->db->join($v['table'], $v['condition'], $v['jointype']);
					}
				}
				if($condtion != NULL){
					if (is_array($condtion) && count($condtion) > 0)
					{
						foreach($condtion as $k => $v)
						{  
							 if($v['value'] != '' && $v['value'] != NULL){
								$this->db->where($v['key'], $v['value']);
							 }
						}
					}
				}
				if($search_value != NULL && $search_value != ''){
					if (is_array($search_like) && count($search_like) > 0)
					{
					$this->db->group_start();
						foreach($search_like as $k )
						{  
							$this->db->or_like($k,$search_value);                   
						}
					  $this->db->group_end(); 
					}
				} 
				
			
				
			  
				//Where in clause
				if($where_in != NULL && (!empty($where_in_data))){
				 $this->db->where_in($where_in,$where_in_data);
				}
				if(is_array($limit) && count($limit) > 0){
					$this->db->limit($limit['length'],$limit['start']);
				}
				if($order_by != NULL){
				  if(is_array($order_by) && count($order_by) > 0){
					$this->db->order_by($order_by['key'],$order_by['type']);
				  }
				}
				$query = $this->db->get();
				//echo $this->db->last_query();die;
				
				//$filter_count = $this->db->query('SELECT FOUND_ROWS () count;')->row()->count;
				$filter_count = $this->db->query('SELECT count(*) AS count;');
				//$query = $this->db->query('SELECT count FROM '.$table.' where role IN(2)');
				$filter_count =$query->num_rows();
				
				$content = array();
				$count = 1;
				$data['draw'] ='test';
				
				if(count($query->result_array()) > 0){
					foreach ($query->result_array() as $row) {
						$row[] = $count;
						$content[] = array_values($row);
						$count ++;
						//array_push($content[], $count);
					}
					//print_r($query->result_array());
					//$this->db->close();
					 return json_encode(array("draw"=> $data['draw'],"recordsTotal"=> $total_count,"recordsFiltered"=> $filter_count,"data" => $content));
				}else{
					//$this->db->close();
						return json_encode(array("draw"=> $data['draw'],"recordsTotal"=> $total_count,"recordsFiltered"=> $filter_count,"data" => $content));
				}


			}
				
				
				
				
				public function insert_users($data)
		{	
			//$this->db->initialize();		
			$name 		= trim($data['name']);
			$username 	= trim($data['username']);
			$uname		=	strtoupper($username);	
			if($username != "") 
				{
					$user_count = $this->db->query("SELECT COUNT(*) AS count FROM login WHERE status != ".DELETE." AND UPPER(username) = " . $this->db->escape($uname))->row()->count;			 
				} 
			else 
				{ 
					$user_count = 0;
				}
			if($user_count == 0)
				{
					$password = hash('SHA512',$data['password'],false);
					$user_details = array(
									'name' => trim($data['name']),
									'email' => trim($data['email']),
									'username' => $username,
									'password' => $password,
									'role' => $data['role'],
									'district_id' => $data['district_id'],
									'muncipality' => $data['muncipality'],
									'block_id' => $data['block_id'],
									'panchayath_id' => $data['panchayath_id'],
									'corporation' => $data['corporation'],
									'place_category' => $data['place_category'],
									'from_source' => 'form','constitution'=>$data['constitution'],
									'cds'=>$data['cds'],
									'ads'=>$data['ads'],
									'status' => INACTIVE,
									'created_at' => date("Y-m-d"),
									);
					$this->db->set($user_details);
					$this->db->insert('login');
					$insId = $this->db->insert_id();
					//$this->db->close();
					return $insId;
				}
			else
				{
					//$this->db->close();
					return 'EXISTS';
				}
		}
		function get_all_agent($dis_id)
		{
			$this->db->initialize();
			$this->db->select('id');
			$this->db->from('login');
			$this->db->where('login.district_id',$dis_id);			
			$result = $this->db->get();
			//return $result->result_array();
			$data = $result->result_array();
			$this->db->close();
			return $data;
		}					
		 function get_all_count($role)
			{
				$this->db->initialize();
				if($role == ADMIN)
				{
					 $active_agent = $this->db->query("SELECT COUNT(*) AS count FROM login WHERE status =".ACTIVE." AND role =".AGENT."")->row()->count;
					 $inactive_agent = $this->db->query("SELECT COUNT(*) AS count FROM login WHERE status =".INACTIVE." AND role =".AGENT."")->row()->count;
					 $full_agent = $this->db->query("SELECT COUNT(*) AS count FROM login WHERE role =".AGENT." AND status !=".DELETE."")->row()->count;
					 $dist_agent = $this->db->query("SELECT COUNT(*) AS count FROM login WHERE role =".DISTRICT_AGENT." AND status !=".DELETE."")->row()->count;
					 $active_survey = $this->db->query("SELECT count(*) FROM survey_family JOIN login ON login.id=survey_family.agent_id AND login.status =".ACTIVE."")->row()->count;
					 $data=array('active_agent'=>$active_agent,'active_survey'=>$active_survey,'inactive_agent'=>$inactive_agent,'full_agent'=>$full_agent,'dist_agent'=>$dist_agent);
					 $this->db->close();
					 return $data;
				}
				else if($role == DISTRICT_AGENT)
					{
						//echo $this->userid;
						$log_user_district  =   $this->district;
						$get_all_agent_ids	=	$this->get_all_agent($log_user_district);	
						$ids				=	array();
						foreach($get_all_agent_ids as $eachAgt)
							{
								$ids[]=$eachAgt['id'];
							}
						
						 $active_agent = $this->db->query("SELECT COUNT(*) AS count FROM login WHERE status =".ACTIVE." AND role =".AGENT." AND district_id=".$this->district)->row()->count;
						 $inactive_agent = $this->db->query("SELECT COUNT(*) AS count FROM login WHERE status =".INACTIVE." AND role =".AGENT." AND district_id=".$this->district)->row()->count;
						 $full_agent = $this->db->query("SELECT COUNT(*) AS count FROM login WHERE role =".AGENT." AND district_id=".$this->district." AND status !=".DELETE." AND id !=".$this->userid)->row()->count;
						 $active_survey = $this->db->query("SELECT COUNT(*) AS count FROM survey_family WHERE agent_id IN (".implode(',',$ids).")")->row()->count; 
						
						 $data=array('active_agent'=>$active_agent,'active_survey'=>$active_survey,'inactive_agent'=>$inactive_agent,'full_agent'=>$full_agent);
						 $this->db->close();
						 return $data;
					}
				
				else
				{
					 $active_survey = $this->db->query("SELECT COUNT(*) AS count FROM user_data WHERE status =".ACTIVE." AND userId=".$this->userid."")->row()->count;
					 $data=array('active_survey'=>$active_survey);
					 $this->db->close();
				     return $data;
				}
				
			}
		/**
     * CodeIgniter
     * @package         Survey
     * @author          Reshma Rajan
     * @since           Version 1.0
     * Date:            9-05-2017 
     * Description:     Common isertion 
     */
	 function save($table, $data) {
	$this->db->initialize();
        if ((isset($table) && !empty($table)) && (isset($data) && !empty($data))) {

            $add = $this->db->insert($table, $data);
            #echo_lq();
            if ($add) {
                //return $this->db->insert_id();
				$data = $this->db->insert_id();
				$this->db->close();
				return $data;
            } else {
				$this->db->close();
                return FALSE;
            }
        } else {
			$this->db->close();
            return FALSE;
        }
		//$this->db->close();
    }
  
    
    	/**
     * CodeIgniter
     * @package         Survey
     * @author          Reshma Rajan
     * @since           Version 1.0
     * Date:            9-05-2017 
     * Description:     Common isertion 
     */
	 
	 
	 
	 
     function update($table, $data, $where) {
		 $this->db->initialize();
        if ((isset($table) && !empty($table)) && (isset($data) && !empty($data))) {
            $this->db->where($where, "", FALSE);
            $this->db->set($data);
            $update = $this->db->update($table);
            if ($update) {
				$this->db->close();
                return TRUE;
            } else {
				$this->db->close();
                return FALSE;
            }
        }
    }
    function survey_count($id)
    {
		$this->db->initialize();
    	$active_survey = $this->db->query("SELECT COUNT(*) AS count FROM user_data WHERE status =".ACTIVE." AND userId =".$id."")->row()->count;
		$this->db->close();
		return $active_survey;
    }	
function test_count()
    {
		$this->db->initialize();
    	$data = array("profile_pic"=>'test.jpg');
    	$this->db->where('family_id', 1);
            $this->db->set($data);
            $update = $this->db->update('survey_family1');
			$this->db->close();
    }	
    function check_survey_exist($data)
    {
		$this->db->initialize();
    	$this->db->select('id');
		$this->db->from('survey_family');
		//$this->db->where('status',ACTIVE);
		$this->db->where('agent_id',$data['agent_id']);
		$this->db->where('survey_appid',$data['survey_appid']);
		$this->db->where('survey_time_stamp',$data['survey_time_stamp']); 
		$data = $this->db->count_all_results();
		$this->db->close();
		return $data;
    }
	function count_detail($table,$where)
    {
        $this->db->initialize();    
		$this->db->select('id');
		$this->db->from($table);
		if ((is_array($where) && count($where) > 0) or ( !is_array($where) && trim($where) != ''))
            $this->db->where($where);
		$data = $this->db->count_all_results();
		//$this->db->close();
		return $data;
    }	
	function get_location_api()
    {
		$this->db->initialize();
    	$this->db->select('id, name, type,parent,district_id,status');
		$this->db->from('location_settings_master');
		//$this->db->where('status',ACTIVE);		
		//$this->db->where_in('parent', array('2','3','4','5','6','7','8','9','10','11','12','13','14','15','16'));	
		$query = $this->db->get();
		$result= $query->result_array();
		$this->db->close();
		return $result;
    }
	function is_user($res)
		{
			$this->db->initialize();
			$this->db->select('id, name, username,status');
			$this->db->from('login');
			$this->db->where('status',ACTIVE);
			$this->db->where('id',$res['userId']);	
			$this->db->where('password',$res['password']);	
			$query = $this->db->get();
			//return $query->result_array();
			$data = $result->result_array();
			$this->db->close();
			return $data;
		}
	function count_table($table,$where,$field)
    {   
		$this->db->initialize();
		$this->db->select($field);
		$this->db->from($table);
		if ((is_array($where) && count($where) > 0) or ( !is_array($where) && trim($where) != ''))
            $this->db->where($where);
		$data = $this->db->count_all_results();
		$this->db->close();
		return $data;
    }
	function update_surveyData($data,$id)
		{ 	//SURVEY soft DELETE
			//$this->db->initialize();
			$this->db->set($data);
			$this->db->where('family_id',$id);
			$this->db->update('survey_family');
			//return $this->db->affected_rows();
			$data = $this->db->affected_rows();
			//$this->db->close();
			return $data;
		}	
	function deleteSurvey($id)
		{
			$this->db->initialize();
			$where1 		= 'family_id='.$id;
			$fam_details 	= $this->count_table('survey_family',$where1,'family_id');
			if($fam_details >0)
				{
					$this->db->where('family_id', $id);
					$this->db->delete('survey_family');
				}
			$where2 		= 'family_id='.$id;
			$ser_details 	= $this->count_table('service',$where2,'family_id');	
			if($ser_details >0)
				{
					$this->db->where('family_id', $id);
					$this->db->delete('service');
				}
			$where3 		= 'family_id='.$id;
			$fam_members 	= $this->count_table('family_members',$where3,'family_id');	
			if($fam_members >0)
				{
					$this->db->where('family_id', $id);
					$this->db->delete('family_members');
				}
			$where4 		= 'family_id='.$id;
			$fam_issues 	= $this->count_table('family_issues',$where4,'family_id');	
			if($fam_issues >0)
				{
					$this->db->where('family_id', $id);
					$this->db->delete('family_issues');
				}
			$where5 		= 'family_member_id='.$id;
			$fam_diseases 	= $this->count_table('diseases_issues',$where5,'family_id');	
			if($fam_diseases >0)
				{
					$this->db->where('family_member_id', $id);
					$this->db->delete('diseases_issues');
				}	
		}
	}