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

// no direct access
defined('_JEXEC') or die('Restricted access');
jimport('joomla.application.component.model');

require_once (JPATH_BASE.'/components/com_jsjobs/JSApplication.php');

class SJJsJobFilterModelItemlist extends JModelLegacy
{
    function getData($ordering = NULL)
    {
        $user 		= JFactory::getUser();
        $aid 		= $user->get('aid');
        $db 		= JFactory::getDBO();
        $limitstart = JRequest::getInt('limitstart');
        $limit 		= JRequest::getInt('limit');
        $task 		= JRequest::getCmd('task');
        $tags_id    = JRequest::getVar('tags_id');
		
		$jnow = JFactory::getDate();
        $now = $jnow->toSql();
        $nullDate = $db->getNullDate();

        //Get pagination
        // $pagination = $this->getjobsPagination();
        // $limit = $pagination['limit'];
        // $pagenum = $pagination['pagenum'];

        $config = JSModel::getJSModel('configurations')->getConfig('');

        foreach ($config as $conf) {
            if ($conf->configname == 'noofgoldjobsinlisting')
                $noofgoldjobs = $conf->configvalue;
            if ($conf->configname == 'nooffeaturedjobsinlisting')
                $nooffeaturedjobs = $conf->configvalue;
            if ($conf->configname == 'showgoldjobsinnewestjobs')
                $showgoldjobs = $conf->configvalue;
            if ($conf->configname == 'showfeaturedjobsinnewestjobs')
                $showfeaturedjobs = $conf->configvalue;
            if ($conf->configname == 'currency_align')
                $currency_align = $conf->configvalue;
        }

        $inquery = '';
        $join = '';

        if (JRequest::getVar('jobtitle')) {
            $jobtitle = JRequest::getVar('jobtitle');
            $inquery .= " AND job.title LIKE '%".$jobtitle."%'";
        }

        if (JRequest::getVar('date_posted')) {
            $date_posted = JRequest::getVar('date_posted');
            if ($date_posted == 'hours') {
                $inquery .= " 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') {
                $inquery .= " 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') {
                $inquery .= " 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') {
                $inquery .= " 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)."'";
            }
        }

        if (JRequest::getVar('jobcompany')) {
            $jobcompany = JRequest::getVar('jobcompany');
            if (is_array($jobcompany)) {
                $inquery .= " AND job.companyid IN (".implode(',', $jobcompany).")";
            }
            else {
                $inquery .= " AND job.companyid = ".$jobcompany;
            }
        }

        if (JRequest::getVar('job_cat')) {
            $category = JRequest::getVar('job_cat');
            if (is_array($category)) {
                $catMode = JRequest::getInt('isc',0);
                $inquery .= " AND job.jobcategory IN (".implode(',', $category).")";
            }
            else {
                $inquery .= " AND job.jobcategory = ".$category;
            }
        }

        if (JRequest::getVar('job_type')) {
            $job_type = JRequest::getVar('job_type');
            if (is_array($job_type)) {
                $inquery .= " AND job.jobtype IN (".implode(',', $job_type).")";
            }else {
                if ($job_type != 'all') {
                    $inquery .= " AND job.jobtype = ".$job_type;
                }
            }
        }

        if (JRequest::getVar('jobstatus')) {
            $jobstatus = JRequest::getVar('jobstatus');
            if (is_array($jobstatus)) {
                $inquery .= " AND job.jobstatus IN (".implode(',', $jobstatus).")";
            }
            else {
                $inquery .= " AND job.jobstatus = ".$jobstatus;
            }
        }

        if (JRequest::getVar('careerlevel')) {
            $careerlevel = JRequest::getVar('careerlevel');
            if (is_array($careerlevel)) {
                $inquery .= " AND job.careerlevel IN (".implode(',', $careerlevel).")";
            }
            else {
                $inquery .= " AND job.careerlevel = ".$careerlevel;
            }
        }

        if (JRequest::getVar('salaryrange')) {
            $salaryrange = JRequest::getVar('salaryrange');
            $salaryrange = explode('-', $salaryrange);
            $inquery .= " AND rstart.rangestart >= ".$salaryrange[0];
            $inquery .= " AND rstart.rangeend <= ".$salaryrange[1];
        }

        //Data
        $query = "SELECT job.id AS jobid,job.params,job.title,job.created,CONCAT(job.alias,'-',job.id) AS jobaliasid,job.noofjobs,job.isgoldjob,job.isfeaturedjob,job.jobapplylink,job.joblink,
                 cat.cat_title,company.id AS companyid,company.name AS companyname,company.logofilename, CONCAT(company.alias,'-',company.id) AS companyaliasid,
                 rstart.rangestart,rend.rangeend,srtype.title AS rangetype, jobtype.title AS jobtypetitle,currency.symbol
                 FROM `#__js_job_jobs` AS job
                 JOIN `#__js_job_companies` AS company ON company.id = job.companyid
                 LEFT JOIN `#__js_job_categories` AS cat ON cat.id = job.jobcategory
                 LEFT JOIN `#__js_job_salaryrange` AS rstart ON rstart.id = job.salaryrangefrom
                 LEFT JOIN `#__js_job_salaryrange` AS rend ON rend.id = job.salaryrangeto
                 LEFT JOIN `#__js_job_salaryrangetypes` AS srtype ON srtype.id = job.salaryrangetype
                 LEFT JOIN `#__js_job_jobtypes` AS jobtype ON jobtype.id = job.jobtype
                 LEFT JOIN `#__js_job_currencies` AS currency ON currency.id = job.currencyid
                 WHERE DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()
                 AND job.status = 1";

        $query.=$inquery;

        $query.= " ORDER BY job.created DESC ";
        // echo str_replace('#__', 'egtvr_', $query);die();

        $db->setQuery($query , $limitstart , $limit);
        $results = $db->loadObjectList();
        foreach($results AS $d){
            $jobid = $this->_getJobCities($d->jobid);
            $d->location = JSModel::getJSModel('cities')->getLocationDataForView($jobid);
            $d->salary = JSModel::getJSModel('common')->getSalaryRangeView($d->symbol,$d->rangestart,$d->rangeend,$d->rangetype , $currency_align);
        }
        return $results;
    }

    function getTotal()
    {
        $user = JFactory::getUser();
        $aid = $user->get('aid');
        $db = JFactory::getDBO();
        $task = JRequest::getCmd('task');
        
        $jnow = JFactory::getDate();
        $now = $jnow->toSql();
        $nullDate = $db->getNullDate();

        $inquery = '';
        $join = '';

        if (JRequest::getVar('jobtitle')) {
            $jobtitle = JRequest::getVar('jobtitle');
            $inquery .= " AND job.title LIKE '%".$jobtitle."%'";
        }

        if (JRequest::getVar('date_posted')) {
            $date_posted = JRequest::getVar('date_posted');
            if ($date_posted == 'hours') {
                $inquery .= " 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') {
                $inquery .= " 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') {
                $inquery .= " 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') {
                $inquery .= " 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)."'";
            }
        }

        if (JRequest::getVar('jobcompany')) {
            $jobcompany = JRequest::getVar('jobcompany');
            if (is_array($jobcompany)) {
                $inquery .= " AND job.companyid IN (".implode(',', $jobcompany).")";
            }
            else {
                $inquery .= " AND job.companyid = ".$jobcompany;
            }
        }

        if (JRequest::getVar('job_cat')) {
            $category = JRequest::getVar('job_cat');
            if (is_array($category)) {
                $inquery .= " AND job.jobcategory IN (".implode(',', $category).")";
            }
            else {
                $inquery .= " AND job.jobcategory = ".$category;
            }
        }

        if (JRequest::getVar('job_type')) {
            $job_type = JRequest::getVar('job_type');
            if (is_array($job_type)) {
                $inquery .= " AND job.jobtype IN (".implode(',', $job_type).")";
            }else {
                if ($job_type != 'all') {
                    $inquery .= " AND job.jobtype = ".$job_type;
                }
            }
        }

        if (JRequest::getVar('jobstatus')) {
            $jobstatus = JRequest::getVar('jobstatus');
            if (is_array($jobstatus)) {
                $inquery .= " AND job.jobstatus IN (".implode(',', $jobstatus).")";
            }
            else {
                $inquery .= " AND job.jobstatus = ".$jobstatus;
            }
        }

        if (JRequest::getVar('careerlevel')) {
            $careerlevel = JRequest::getVar('careerlevel');
            if (is_array($careerlevel)) {
                $inquery .= " AND job.careerlevel IN (".implode(',', $careerlevel).")";
            }
            else {
                $inquery .= " AND job.careerlevel = ".$careerlevel;
            }
        }

        if (JRequest::getVar('salaryrange')) {
            $salaryrange = JRequest::getVar('salaryrange');
            $salaryrange = explode('-', $salaryrange);
            $inquery .= " AND rstart.rangestart >= ".$salaryrange[0];
            $inquery .= " AND rstart.rangeend <= ".$salaryrange[1];
        }

        $query = "SELECT COUNT(DISTINCT job.id) FROM `#__js_job_jobs` AS job
                JOIN `#__js_job_companies` AS company ON company.id = job.companyid
                LEFT JOIN `#__js_job_categories` AS cat ON cat.id = job.jobcategory
                LEFT JOIN `#__js_job_salaryrange` AS rstart ON rstart.id = job.salaryrangefrom
                LEFT JOIN `#__js_job_salaryrange` AS rend ON rend.id = job.salaryrangeto
                LEFT JOIN `#__js_job_salaryrangetypes` AS srtype ON srtype.id = job.salaryrangetype
                LEFT JOIN `#__js_job_jobtypes` AS jobtype ON jobtype.id = job.jobtype
                LEFT JOIN `#__js_job_currencies` AS currency ON currency.id = job.currencyid
                WHERE DATE(job.startpublishing) <= CURDATE() AND DATE(job.stoppublishing) >= CURDATE()
                AND job.status = 1";

        $query.=$inquery;

        $db->setQuery($query);
        $total = $db->loadResult();
        
        return $total;
    }

    function _getJobCities($jobid){
        if(!is_numeric($jobid)) return false;
        $db = JFactory::getDBO();
        $query = "SELECT cityid FROM `#__js_job_jobcities` WHERE jobid = $jobid";
        $db->setQuery($query);
        $cities = $db->loadObjectList();
        $str = '';
        foreach ($cities as $key) {
            if($str) $str .= ",";
            $str .= $key->cityid;
        }
        return $str;
    }

    function getjobsPagination(){
        $pagination = array();
        $mainframe = JFactory::getApplication();
        $option = 'com_jsjobs';
        $limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        //$limitstart = $mainframe->getUserStateFromRequest($option . '.limitstart', 'limitstart', 0, 'int');
        
        $pagenum = JRequest::getVar('pagenum', 0);
        $pagination['limit'] = $limit;
        $pagination['pagenum'] = $pagenum;

        return $pagination;
    }
}