<?php
/**
 * @package Sj Latest Jobs For Js Jobs
 * @version 1.0.0
 * @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
 * @copyright (c) 2015 YouTech Company. All Rights Reserved.
 * @author YouTech Company http://www.smartaddons.com
 *
 */

defined('_JEXEC') or die;
include_once dirname(__FILE__) . '/helper_base.php';

abstract class JobsNewestHelper extends JobsNewestBaseHelper
{
	public static function getList($params,$typeN = 0)
	{
		
		$checkFuture = 0;
		if($typeN == 0){
			if($params->get('sj_newest_jobs_newest_category_all') == 1){
				$searchjobcategory = '';
			}else{
				$searchjobcategory = $params->get('sj_newest_jobs_newest_category');
			}
			if($params->get('sj_newest_jobs_newest_type_all') == 1){
				$searchjobtype = '';
			}else{
				$searchjobtype = $params->get('sj_newest_jobs_newest_type');
			}
			if($params->get('sj_newest_jobs_newest_company_all') == 1){
				$searchcompany = '';
			}else{
				$searchcompany = $params->get('sj_newest_jobs_newest_company');
			}
			$limit = $params->get('sj_newest_jobs_newest_limit',8);
			$queryF = '';
		}else{
			$checkFuture = 1;
			if($params->get('sj_newest_jobs_future_category_all') == 1){
				$searchjobcategory = '';
			}else{
				$searchjobcategory = $params->get('sj_newest_jobs_future_category');
			}
			if($params->get('sj_newest_jobs_future_type_all') == 1){
				$searchjobtype = '';
			}else{
				$searchjobtype = $params->get('sj_newest_jobs_future_type');
			}
			if($params->get('sj_newest_jobs_future_company_all') == 1){
				$searchcompany = '';
			}else{
				$searchcompany = $params->get('sj_newest_jobs_future_company');
			}
			$limit = $params->get('sj_newest_jobs_future_limit',5);
			$future = $params->get('sj_newest_jobs_future_name_field_future','');
			
			if ($future != '') {
				$idF = self::getIdFiled($future);
			} else {
				$idF = '';
			}
			
			
			if($idF == ''){
				return 0;
				$queryF = '';
			}else{
				$queryF = 'JOIN `#__js_job_userfield_data` AS fdata ON job.id = fdata.referenceid JOIN `#__js_job_userfieldvalues` AS fval ON fval.id = fdata.data';
			}
			
		}
		
		
		$db = JFactory :: getDBO();
		$result = array();
		$time = time();
		$date = date('Y-m-d G:i:s');
		$query = "SELECT job.*, cat.cat_title, jobtype.title AS jobtypetitle, company.name AS companyname, company.logofilename As logo
				FROM `#__js_job_jobs` AS job 
				JOIN `#__js_job_categories` AS cat ON job.jobcategory = cat.id ".$queryF."
				JOIN `#__js_job_jobtypes` AS jobtype ON job.jobtype = jobtype.id
				LEFT JOIN `#__js_job_companies` AS company ON job.companyid = company.id
				WHERE job.status <> 0 
				AND '".$date."' > job.startpublishing 
				AND '".$date."' < job.stoppublishing";

        if ($searchcompany != ''){
			$searchcompany = join(',',$searchcompany); 
            $query .= " AND job.companyid IN (" . $searchcompany .")" ;
		}
			
        if ($searchjobcategory != ''){
			$searchjobcategory = join(',',$searchjobcategory); 
            $query .= " AND job.jobcategory IN (" . $searchjobcategory .")";
		}
			
        if ($searchjobtype != ''){
			$searchjobtype = join(',',$searchjobtype); 
			$query .= " AND job.jobtype IN (" . $searchjobtype .")";
		}
        if($checkFuture == 1){
			if($idF == ''){
				$query .= " AND fdata.field = " . $idF;
			}
			$query .= " AND fval.fieldvalue = 1";
		}
        $query .= " ORDER BY job.startpublishing DESC";		
		//echo $query; die;
        $db->setQuery($query, 0, $limit);
        $row = $db->loadObjectList();	
		if($checkFuture == 1){
			if($idF == ''){
				return 0;
			}
		}
		return $row;
	}
	public static function getNamCity(){
		$db = JFactory :: getDBO();
		$query = "SELECT jobcities.id AS city_id, jobcities.cityName AS cityN
				FROM `#__js_job_jobs` AS job
				JOIN `#__js_job_cities` AS jobcities ON jobcities.id = job.city GROUP BY jobcities.id";

		$db->setQuery($query);
		return $db->loadObjectList();
	}
	public static function getIdFiled($name){
		$db = JFactory :: getDBO();
		$query = "SELECT id From #__js_job_userfields WHERE title = '" .$name. "'";
		$db->setQuery($query);
		$row = $db->loadObject();
		if(count($row) == 0){
			return '';
		}
		$return = array();
		$return['id'] = $row->id;
		$query = "SELECT id,fieldvalue,field From #__js_job_userfieldvalues WHERE field = " . $row->id;
		$db->setQuery($query);
        $rows = $db->loadObjectList();
		foreach($rows as $row){
			$return[$row->id] = $row->fieldvalue;
		}
		
		return $return['id'];
		
	}
}
