<?php
/**
 * @package SJ Filter for JS Jobs
 * @version 1.0.0
 * @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
 * @copyright (c) 2014 YouTech Company. All Rights Reserved.
 * @author YouTech Company http://www.smartaddons.com
 *
 */

defined('_JEXEC') or die;
use Joomla\CMS\Factory;
include_once dirname(__FILE__) . '/helper_base.php';

abstract class JobFilterHelper extends JobFilterBaseHelper
{
	public static function getJobCategories($params) {
        $db = JFactory::getDBO();
        $query = "SELECT id,cat_title, (SELECT COUNT(job.id) from `#__js_job_jobs` AS job WHERE cat.id = job.jobcategory AND job.status = 1 AND DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()) AS countjob FROM `#__js_job_categories` AS cat WHERE isactive = 1";
        $query.=" ORDER BY ordering ";
		try {
			$db->setQuery($query);
			$rows = $db->loadObjectList();
		}
		catch (Exception $e){
			echo $e->getMessage();
		}
        $jobcategories = array();
        foreach ($rows as $row) {
            $jobcategories[] = array(
                'value' => $row->id,
                'text' => JText::_($row->cat_title),
                'count' => $params->get('display_counter') ? '('.$row->countjob.')' : false
            );
        }
        return $jobcategories;
    }

    public static function getJobSubCategories($params) {
        $db = JFactory::getDBO();
        $query = "SELECT id,title, (SELECT COUNT(job.id) from `#__js_job_jobs` AS job WHERE subcat.id = job.subcategoryid AND job.status = 1 AND DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()) AS countjob FROM `#__js_job_subcategories` AS subcat WHERE status = 1";
        $query.=" ORDER BY ordering ";
        $db->setQuery($query);
        $rows = $db->loadObjectList();
        if ($db->getErrorNum()) {
            echo $db->stderr();
            return false;
        }
        $jobsubcategories = array();
        foreach ($rows as $row) {
            $jobsubcategories[] = array(
                'value' => $row->id,
                'text' => JText::_($row->title),
                'count' => $params->get('display_counter') ? '('.$row->countjob.')' : false
            );
        }
        return $jobsubcategories;
    }

    public static function getJobTypes($params) {
        $db = JFactory::getDBO();
        $query = "SELECT id, title, (SELECT COUNT(job.id) from `#__js_job_jobs` AS job WHERE jobtype.id = job.jobtype AND job.status = 1 AND DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()) AS countjob FROM `#__js_job_jobtypes` AS jobtype WHERE isactive = 1";
        $query.=" ORDER BY ordering ASC ";
		try {
			$db->setQuery($query);
			$rows = $db->loadObjectList();
		}
		catch (Exception $e){
			echo $e->getMessage();
		}
        $jobtype = array();
        foreach ($rows as $row) {
            $jobtype[] = array(
            	'value' => $row->id,
                'text' => JText::_($row->title),
                'count' => $params->get('display_counter') ? '('.$row->countjob.')' : false
			);
        }

        return $jobtype;
    }

    public static function getJobCompanies($params) {
        $db = JFactory::getDBO();
        $query = "SELECT id, name, (SELECT COUNT(job.id) from `#__js_job_jobs` AS job WHERE jobcompany.id = job.companyid AND job.status = 1 AND DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()) AS countjob FROM `#__js_job_companies` AS jobcompany ORDER BY name ASC ";
		try {
			$db->setQuery($query);
			$rows = $db->loadObjectList();
		}
		catch (Exception $e){
			echo $e->getMessage();
		}
        $companies = array();
        foreach ($rows as $row) {
            $companies[] = array(
                'value' => $row->id,
                'text' => $row->name,
                'count' => $params->get('display_counter') ? '('.$row->countjob.')' : false
            );
        }
        return $companies;
    }

    public static function getCareerLevel($params) {
        $db = JFactory::getDBO();
        $query = "SELECT id, title, (SELECT COUNT(job.id) from `#__js_job_jobs` AS job WHERE career.id = job.careerlevel AND job.status = 1 AND DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()) AS countjob FROM `#__js_job_careerlevels` AS career WHERE status = 1";
        $query.=" ORDER BY ordering ASC ";

		try {
			$db->setQuery($query);
			$rows = $db->loadObjectList();
		}
		catch (Exception $e){
			echo $e->getMessage();
		}
            
        $careerlevel = array();
        foreach ($rows as $row) {
            $careerlevel[] = array(
                'value' => $row->id,
                'text' => JText::_($row->title),
                'count' => $params->get('display_counter') ? '('.$row->countjob.')' : false
            );
        }
        return $careerlevel;
    }

    public static function getJobStatus($params) {
        $db = JFactory::getDBO();
        $query = "SELECT id, title, (SELECT COUNT(job.id) from `#__js_job_jobs` AS job WHERE jobstatus.id = job.jobstatus AND job.status = 1 AND DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()) AS countjob FROM `#__js_job_jobstatus` AS jobstatus WHERE isactive = 1";
        $query.=" ORDER BY ordering ASC ";
		try {
			$db->setQuery($query);
			$rows = $db->loadObjectList();
		}
		catch (Exception $e){
			echo $e->getMessage();
		}
        $jobstatus = array();
        foreach ($rows as $row) {
            $jobstatus[] = array(
                'value' => $row->id,
                'text' => JText::_($row->title),
                'count' => $params->get('display_counter') ? '('.$row->countjob.')' : false
            );
        }
        return $jobstatus;
    }

    public static function getJobSalary($params) {
        $db = JFactory::getDBO();
        $query = "SELECT id, rangestart, rangeend, (SELECT COUNT(job.id) from `#__js_job_jobs` AS job WHERE salary.id = job.salaryrangefrom AND job.status = 1 AND DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()) AS countjob FROM `#__js_job_salaryrange` AS salary WHERE status = 1";
        $query.=" ORDER BY ordering ASC ";
		try {
			$db->setQuery($query);
			$rows = $db->loadObjectList();
		}
		catch (Exception $e){
			echo $e->getMessage();
		}
        $salary = array();
        foreach ($rows as $row) {
            $salary[] = array(
                'value' => $row->rangestart.'-'.$row->rangeend,
                'text' => JText::_($row->rangestart).' - '.JText::_($row->rangeend),
                'count' => $params->get('display_counter') ? '('.$row->countjob.')' : false
            );
        }
        return $salary;
    }

    public static function getCurrency() {
        $db = JFactory::getDBO();
        $query = "SELECT * FROM `#__js_job_currencies` WHERE status = 1";
        $query.=" ORDER BY ordering ASC ";
		try {
			$db->setQuery($query);
			$rows = $db->loadObjectList();
		}
		catch (Exception $e){
			echo $e->getMessage();
		}
        
        return $rows;
    }

    public static function getCountJobByDate($date_posted, $params) {
        $db = JFactory::getDBO();
        $query = "SELECT COUNT(job.id) FROM `#__js_job_jobs` AS job WHERE status = 1";
        if ($date_posted == 'hours') {
            $query .= " AND DATE_FORMAT(job.created, '%Y-%m-%d') <= '".date('Y-m-d', time()). "' AND DATE_FORMAT(job.created, '%Y-%m-%d') >= '".date('Y-m-d', time()-24*60*60)."'";
        }elseif($date_posted == 'days') {
            $query .= " AND DATE_FORMAT(job.created, '%Y-%m-%d') <= '".date('Y-m-d', time()). "' AND DATE_FORMAT(job.created, '%Y-%m-%d') >= '".date('Y-m-d', time()-3*24*60*60)."'";
        }elseif($date_posted == 'weeks') {
            $query .= " AND DATE_FORMAT(job.created, '%Y-%m-%d') <= '".date('Y-m-d', time()). "' AND DATE_FORMAT(job.created, '%Y-%m-%d') >= '".date('Y-m-d', time()-7*24*60*60)."'";
        }elseif($date_posted == 'months') {
            $query .= " AND DATE_FORMAT(job.created, '%Y-%m-%d') <= '".date('Y-m-d', time()). "' AND DATE_FORMAT(job.created, '%Y-%m-%d') >= '".date('Y-m-d', time()-30*24*60*60)."'";
        }

        $db->setQuery($query);
        $rows = $db->loadResult();
        return $params->get('display_counter') ? '('.$rows.')' : false;
    }

    public static function getAjaxCount($params) {
		$app  = Factory::getApplication();
        $jobtitle = $app->input->getVar('jobtitle', '');
        $date_posted = $app->input->getVar('date_posted', '');
        $jobcompany = $app->input->getVar('jobcompany', '');
        $job_cat = $app->input->getVar('job_cat', '');
        $job_type = $app->input->getVar('job_type', '');
        $jobstatus = $app->input->getVar('jobstatus', '');
        $salaryrange = $app->input->getVar('salaryrange', '');
        $careerlevel = $app->input->getVar('careerlevel', '');

        $db = JFactory::getDBO();
        $query = "SELECT COUNT(job.id) FROM `#__js_job_jobs` AS job WHERE job.status = 1 AND DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()";
        if ($salaryrange) {
            $query .= " LEFT JOIN #__js_job_salaryrange AS salary ON salary.id = job.salaryrangefrom";
        }
        $db->setQuery($query);
        $rows = $db->loadResult();

        return $params->get('display_counter') ? '('.$rows.')' : false;
    }
}
