<?php namespace App;

use Illuminate\Database\Eloquent\Model;
use DB;
use Config;
use Auth;
use App\Categories;
use App\Industry;
use Session;
class Profile extends Model {

	/**
	 * The database table used by the model.
	 *
	 * @var string
	 */
	protected $table = 'profiles';

	/** 
	 * The attributes that are mass assignable.
	 *
	 * @var array 
	 */
	protected $fillable = ['user_id','name','city','url','about_us','contact_info','profile_type','profile_image','skills','influence','photo','video','resumes_uploading','emp_contact','site_use','funding','private_permission','is_deleted'];

	protected function search($input,$limit,$offset,$profile_type,$return_count = false)
	{
		$skill_array = array();
		$auth_id = Auth::user()->id;
//		$where="where u.id<>{$auth_id} AND profile_type='{$profile_type}'";
		$where="where u.id<>{$auth_id}"; // @1706/2015
		$in = $in_or = array();
		$where_in = $where_or = $where_in_or ='';

		$parent_data = Categories::getListofCategories(Session::get('auth_selected_profile_type'));
		if(!empty($input['assets_currency_units']))$fb_cur_row = CurrencyUnits::find($input['assets_currency_units']);
		$child_data = Categories::getListofCategoriesLikeArray(true);
               // echo Session::get('auth_selected_profile');exit;
                $result = DB::select("SELECT status_business FROM profiles WHERE id = '" . Session::get('auth_selected_profile') . "'");
               // echo $result[0]->status_business;exit;
                if(!empty($result[0]->status_business))$status_business=$result[0]->status_business;else $status_business="";
		$industry_data = Industry::getList();
		$roles_data = Roles::getList();
		//echo Session::get('auth_selected_profile_type');exit;

		/* @17062015 Selected Profile type wise search*/
			/*if(Session::get('auth_selected_profile_type')=='T')
				$where .=" AND profile_type IN ('T')";
			else if(Session::get('auth_selected_profile_type')=='O')
				$where .=" AND profile_type IN ('O')";
			else 
			        $where .=" AND profile_type IN ('T','O') AND status_business NOT LIKE '%$status_business%'";*/
		/* End */


		/* Input Search with where  and in*/
		if($input['country']):
			$in[]="SELECT DISTINCT profile_id from location where country like '%{$input['country']}%'";
		endif;

		if($input['city']):
			$in[]="SELECT DISTINCT profile_id from location where city like '%{$input['city']}%'";
		endif;
                
                if(isset($input['assets_currency']) && !empty($input['assets_currency'])):
			$in[]="SELECT DISTINCT profile_id from funding_business where assets_currency like '%{$input['assets_currency']}%'";
		endif;
                
             /*   if(isset($input['assets_currency_units']) && !empty($input['assets_currency_units'])):
			$in[]="SELECT DISTINCT profile_id from funding_business where min_investment_currency_units like '%{$input['assets_currency_units']}%'";
		endif;*/
               if($result[0]->status_business=="Business" && empty($input['revenue']) && !empty($input['assets_currency_units'])):
               
               	$in[]="SELECT DISTINCT profile_id FROM funding_business WHERE max_investment_fin_amt>='{$fb_cur_row['amount']}'";
               endif;

             if(isset($input['revenue']) && !empty($input['revenue']) && !empty($input['assets_currency_units'])):
             	$revenue=$input['revenue']*$fb_cur_row['amount'];
               $in[]="SELECT DISTINCT profile_id FROM funding_business WHERE min_investment_fin_amt <= '{$revenue}' AND max_investment_fin_amt>='{$revenue}'";
              endif;
               if(isset($input['revenue']) && !empty($input['revenue']) && empty($input['assets_currency_units'])):
               	$in[]="SELECT DISTINCT profile_id FROM funding_business WHERE min_investment_amt <= '{$input['revenue']}' AND max_investment_amt>='{$input['revenue']}'";
                  /*  $revenue = DB::select("SELECT id FROM currency_units WHERE units >= '{$input['revenue']}' AND '{$input['revenue']}' <= units");
                   
                 if(count($revenue)>0): 
                    $rev="";
                    $cnt=count($revenue)-1;
             
                         for($i=0;$i<count($revenue);$i++){
                             if($i<count($revenue)){$or=' OR ';}else{$or='';}
                             $rev.='max_investment_currency_units= '.$revenue[$i]->id.$or;
                             if($i<$cnt){$or=' OR ';}else{$or='';}
                             $rev.='min_investment_currency_units= '.$revenue[$i]->id.$or;
                             }
                // $ids = join(',',$rev);     
                 
                 $in[]="SELECT DISTINCT profile_id from funding_business where ".$rev;
                 endif;*/
		      endif;

		      if($result[0]->status_business=="Finance" && empty($input['revenue_from']) && empty($input['revenue_to']) && !empty($input['assets_currency_units'])):
               
               	$in[]="SELECT DISTINCT profile_id FROM funding_business WHERE max_investment_usd_val>='{$fb_cur_row['amount']}'";
               endif;

                 if(isset($input['revenue_from']) && !empty($input['revenue_from']) && isset($input['revenue_to']) && !empty($input['revenue_to']) && !empty($input['assets_currency_units'])):
                 	$revenue_from=$input['revenue_from']*$fb_cur_row['amount'];$revenue_to=$input['revenue_to']*$fb_cur_row['amount'];
	$in[]="SELECT DISTINCT profile_id from funding_business where 	max_investment_usd_val>= '{$revenue_from}' AND  max_investment_usd_val<='{$revenue_to}'";
    endif;
	 if(isset($input['revenue_from']) && !empty($input['revenue_from']) && isset($input['revenue_to']) && !empty($input['revenue_to']) && empty($input['assets_currency_units'])):
	 	$in[]="SELECT DISTINCT profile_id from funding_business where 	investment_amt>= '{$input['revenue_from']}' AND  investment_amt<='{$input['revenue_to']}'";

	/*$revenue = DB::select("SELECT id FROM currency_units WHERE units >= '{$input['revenue_from']}' AND '{$input['revenue_to']}' >= units");
               
                 if(count($revenue)>0): 
                    $rev="";
                    $cnt=count($revenue)-1;
             
                         for($i=0;$i<count($revenue);$i++){
                             if($i<$cnt){$or=' OR ';}else{$or='';}
                             $rev.='min_investment_currency_units= '.$revenue[$i]->id.$or;
                             }
                // $ids = join(',',$rev);     
                 
                 $in[]="SELECT DISTINCT profile_id from funding_business where ".$rev;
                 endif;*/
                     //$in[]="SELECT DISTINCT profile_id from funding_business where min_investment_currency_units like '%{$input['revenue_from']}%'";
		endif;
                //echo $input['revenue_to']; exit;
              //print_r($in);exit;
                
		if($input['state']):
			$in[]="SELECT DISTINCT profile_id from location where state like '%{$input['state']}%'";
		endif;

		if($input['zip']):
			$in[]="SELECT DISTINCT profile_id from location where zip like '%{$input['zip']}%'";
		endif;

		if($input['industry']):
			$in[]="SELECT DISTINCT CI.profile_id from category_industry as CI LEFT JOIN industry as I ON I.id=CI.industry_id where I.name='{$input['industry']}'";
		endif;

		if($input['skill']):
			$in[]="SELECT DISTINCT PC.profile_id from profile_categories as PC LEFT JOIN categories as C ON C.id=PC.category_id where C.name='{$input['skill']}'";
		endif;

		if($input['company']):
			$cmp_to_date='';
			if(isset($input['sea_cmpny_type'])):
				if($input['sea_cmpny_type']=='c')
					$cmp_to_date = "AND to_date='0000-00-00'";	
				else
					$cmp_to_date =  "AND to_date<>'0000-00-00'";	
			endif;	
			$in[]="SELECT DISTINCT profile_id from experience where company_name like '%{$input['company']}%' {$cmp_to_date}";
		endif;

		if($input['school']):
			$in[]="SELECT DISTINCT profile_id from education where company_name like '%{$input['school']}%'";
		endif;

		if($input['exp']):
			$in[]="SELECT DISTINCT profile_id from experience where FLOOR(DATEDIFF(to_date,from_date)/365)={$input['exp']}";
		endif;
			
		if(count($in)):
			$inc = 0;  
			foreach($in as $n):
				if($inc)
					$where_in = $where_in." AND p.id IN (".$n.")";	
				else 
					$where_in = $where_in." p.id IN (".$n.")";	
				$inc++;
			endforeach;
			$where_in = "AND (".$where_in.")";
		endif;	
		/* END Input Search with where  and in*/

		/* Key word search where in or  AND where orCondition*/
		if($input['keyword']):
			$in_or[] = "SELECT DISTINCT cp.profile_id FROM categories as c LEFT JOIN profile_categories as cp ON cp.category_id=c.id where name like '%{$input['keyword']}%'";
			$in_or[] = "SELECT DISTINCT ci.profile_id FROM industry as i LEFT JOIN category_industry as ci ON ci.industry_id=i.id where name like '%{$input['keyword']}%'";
			$in_or[] = "SELECT DISTINCT cr.profile_id FROM roles as r LEFT JOIN category_roles as cr ON cr.role_id=r.id where name like '%{$input['keyword']}%'";
			$in_or[] = "SELECT DISTINCT profile_id from location where country like '%{$input['keyword']}%' OR city like '%{$input['keyword']}%' OR state like '%{$input['keyword']}%' OR zip like '%{$input['keyword']}%'";
			$in_or[] = "SELECT DISTINCT profile_id from experience where company_name like '%{$input['keyword']}%'";
			$in_or[] = "SELECT DISTINCT profile_id from education where company_name like '%{$input['keyword']}%'";
			$or[] = " OR p.name like '%{$input['keyword']}%'";
			$or[] = " OR u.name like '%{$input['keyword']}%'";
			$or[] = " OR u.name like '%{$input['keyword']}%'";
		endif;	

		if(count($in_or)):
			$inc = 0; 
			foreach($in_or as $n):
				if($inc)
					$where_in_or = $where_in_or." OR p.id IN (".$n.")";	
				else 
					$where_in_or = $where_in_or." p.id IN (".$n.")";	
				$inc++;
			endforeach;


			foreach($or as $n):
					$where_or = $where_or.$n;	
			endforeach;
			
			//$where_or = "AND (".$where_or.")";
			$where_in_or = "AND (".$where_in_or.$where_or.")";
		endif;
//echo '6';		
		/* END Key word search where in or Condition*/

		if($input['profile_title'])	:
			$where .=" AND p.name like '%{$input['profile_title']}%'";
		endif;

		if($input['first_name']):	
			$where .=" AND u.name like '%{$input['first_name']}%'";
		endif;

		if($input['last_name']):	
			$where .=" AND u.name like '%{$input['last_name']}%'";
		endif;

		if($return_count):
			$result = DB::select("select count(*) as total_records from profiles as p LEFT JOIN users as u ON u.id = p.user_id $where $where_in $where_in_or");
			//print_r("select count(*) as total_records from profiles as p LEFT JOIN users as u ON u.id = p.user_id $where $where_in $where_in_or");exit;
                return $result[0]->total_records;
		else:			
			
			$result = DB::select("select p.id,p.profile_image,p.name from profiles as p LEFT JOIN users as u ON u.id = p.user_id $where $where_in $where_in_or limit $offset,$limit");
		//echo "select p.id,p.profile_image,p.name from profiles as p LEFT JOIN users as u ON u.id = p.user_id $where $where_in $where_in_or limit $offset,$limit"; exit;
//print_r("select p.id,p.profile_image,p.name from profiles as p LEFT JOIN users as u ON u.id = p.user_id $where $where_in $where_in_or limit $offset,$limit");exit;
			return $result;
		endif;
		
	}	

    protected function homesearch($input,$limit,$offset,$profile_type,$return_count = false)
	{
        $skill_array = array();
		
		

        if(isset(Auth::user()->id))
        {
          $auth_id = Auth::user()->id;
          $where="where u.id<>{$auth_id}"; 
        }
	    else
	    {$where=""; }

		$in = $in_or = array();
		$where_in = $where_or = $where_in_or ='';

		/*$parent_data = Categories::getListofCategories('T');
		$child_data = Categories::getListofCategoriesLikeArray(true);
		$status_business="";
		$industry_data = Industry::getList();
		$roles_data = Roles::getList();*/
		$where .=" AND profile_type IN ('T')";
		//dd($where);
         $input=$input['srch-term'];
		/*if($input):
			$in_or[] = "SELECT DISTINCT cp.profile_id FROM categories as c LEFT JOIN profile_categories as cp ON cp.category_id=c.id where name like '%{$input}%'";
			$in_or[] = "SELECT DISTINCT ci.profile_id FROM industry as i LEFT JOIN category_industry as ci ON ci.industry_id=i.id where name like '%{$input}%'";
			$in_or[] = "SELECT DISTINCT cr.profile_id FROM roles as r LEFT JOIN category_roles as cr ON cr.role_id=r.id where name like '%{$input}%'";
			$in_or[] = "SELECT DISTINCT profile_id from location where country like '%{$input}%' OR city like '%{$input}%' OR state like '%{$input}%' OR zip like '%{$input}%'";
			$in_or[] = "SELECT DISTINCT profile_id from experience where company_name like '%{$input}%'";
			$in_or[] = "SELECT DISTINCT profile_id from education where company_name like '%{$input}%'";
			$or[] = " OR p.name like '%{$input}%'";
			$or[] = " OR u.name like '%{$input}%'";
			$or[] = " OR u.name like '%{$input}%'";
		endif;	*/

		/*if(count($in_or)):
			$inc = 0; 
			foreach($in_or as $n):
				if($inc)
					$where_in_or = $where_in_or." OR p.id IN (".$n.")";	
				else 
					$where_in_or = $where_in_or." p.id IN (".$n.")";	
				$inc++;
			endforeach;
			$where_in_or = "AND (".$where_in_or.")";
		endif;*/

		if($input)	:
			$where .=" AND p.name like '%{$input}%'";
		endif;

		if($input):	
			$where .=" AND u.name like '%{$input}%'";
		endif;

      // print_r($where_in_or);exit;

		if($return_count):
			$result = DB::select("select count(*) as total_records from profiles as p  JOIN users as u ON u.id = p.user_id $where $where_in_or");
			
                return $result[0]->total_records;
         else:
			$result = DB::select("select p.id,p.profile_image,p.name from profiles as p  JOIN users as u ON p.user_id=u.id $where $where_in_or limit $offset,$limit");
			return $result;
		endif;
                
	}
   
	protected function getProfileIDs($condition_array)
	{
		echo "<pre>";
		print_r($condition_array);
		exit;
	}

	protected function typeaheadName($query)
	{
		$results = $this->select(['profiles.name as value','profiles.profile_image','users.username','profiles.id'])
						->join('users','users.id','=','profiles.user_id')
						->where('profiles.name','like',"%{$query}%")
						->get();
		return $results;				
	}
	protected function getprofileinfo($inputid)
        {  
            $sender_db = $this->select('users.email','users.name')                            
                            ->join('users', 'profiles.user_id', '=', 'users.id')
                            ->where('profiles.id', '=', $inputid)->get()->toArray();
           return $sender_db;
        }
        
         protected function getsenderinfo($inputid)
        {  
            $sender_db = $this->select('users.email','users.name')                            
                            ->join('users', 'profiles.user_id', '=', 'users.id')
                            ->where('profiles.id', '=', $inputid)->get()->toArray();
           return $sender_db;
        }

    protected function getTotalByType($type = '')
    {
    	if(!$type)
    		return false;

    	$result = DB::select("SELECT * FROM profiles WHERE profile_type = '" . $type . "'");
    	return count($result);
    }

    protected function getMonthwiseTotal($type = '')
    {
    	$where = '';
    	if($type)
    		$where = "WHERE profile_type = '" . $type . "' ";

    	$result = DB::select("SELECT YEAR(created_at) AS y, MONTH(created_at) AS m, MONTHNAME(created_at) AS m_name, COUNT(DISTINCT id) total_profiles FROM `profiles` ".$where."GROUP BY y, m");
    	return $result;
    }
    protected function funding_business()
    {
    	$sender_db = $this->select('profiles.name','profiles.id','profiles.is_approve','profiles.status_business')                            
                            ->join('funding_business', 'funding_business.profile_id', '=', 'profiles.id')
                            ->where('status_business','=','Finance')
                            ->where('profiles.profile_type', '=','O')
                ->orderBy('profiles.id','desc')->get();
           
        return $sender_db;
    }
       protected function getStatus($type='')
    {
         $where = '';
    	if($type)
    		$where = "WHERE profile_type = 'O' AND status_business='".$type."'";

    	$result = DB::select("SELECT * FROM `profiles` ".$where);
    	return count($result);
    }
}
