original vacfil

<?php

namespace App\Repositories\Filters\Vacancies;

use Illuminate\Http\Request;
use DB;
use App\Models\Access\User\User;
use App\Models\Certificate;
use App\Models\FleetLists;
use Illuminate\Pagination\LengthAwarePaginator as Paginator;


class VacanciesFilter
{

    private $request;

    private $sql;

    private $where;

    private $from;

    private $group;

    private $having;

    private $limit;

    private $order;

    private  $perPage;

    private $page;

    private $total;

    private $user;

    public function __construct($user)
    {
        $this->request = request();
        $this->page = $this->request->has('page') ?
               $this->request->page - 1: null;
        $this->perPage = 10;
        $this->user = $user;
    }



    public function vacancies(){
          $this->initSql();
          $this->applyFilters();
          $sql = $this->sql." "
              ."\n" .$this->from ." "
              ."\n". $this->where ." "
              ."\n". $this->group ." "
              ."\n". $this->having ." "
            ;

           $fullSql = $sql." ".$this->order." ".$this->limit;


//        dd($fullSql);

        $this->total = DB::select($sql);
        $res = DB::select($fullSql);
        // dd($res);
        return $this->preparePaginate($res);

    }

    private function initSql()
    {
        $this->sql = "
            SELECT  u.id, u.first_name, u.updated_at,
              IF(ISNULL(u.last_name), '', SUBSTRING(u.last_name, 1, 1)) as last_name,
              fl.name as vname, fl.id as vid,
              pi.date_of_readiness as dreadiness,
              pi.contact_phone as phone,

              (SELECT short_name FROM countries WHERE countries.id=pi.citizenship) as flag,
              (SELECT name FROM countries WHERE countries.id=pi.citizenship) as flag_full,
              YEAR(NOW()) - YEAR(pi.date_of_birth) as date_of_birth,
              (SELECT name from rank_lists
                 where rank_lists.id=pi.rank_list_id) as rname,
              ( SELECT COALESCE( SUM(DATEDIFF(service.sign_off_date, service.sign_on_date))/365, '')
                   FROM service
                   WHERE service.rank_list_id=pi.rank_list_id
                   AND service.user_id=u.id
               ) as yrank,

            (SELECT COUNT(cv_reviews.id)  FROM cv_reviews
              WHERE  cv_reviews.seaman_id=u.id
                   AND cv_reviews.crewing_id=". $this->user->id. "
            ) as review,
            (SELECT expiration FROM certificates WHERE organisation='CoC Validation Certificate' AND certificates.user_id=u.id) as validation,

            (SELECT (SUM(percents.general)+SUM(percents.biometric)+SUM(percents.medical)+SUM(percents.personal)+SUM(percents.training)+SUM(percents.summary)+SUM(percents.service)) as total  FROM percents
              WHERE  percents.user_id = u.id
            ) as strength,

           (SELECT service.sign_off_date FROM service
               where service.user_id=u.id
             ORDER BY service.sign_on_date DESC LIMIT 1
           ) as lsod,


           (SELECT service.rank_list_id FROM service
               where service.user_id=u.id
             ORDER BY service.sign_on_date DESC LIMIT 1
           ) as lrankId,

           (SELECT name FROM rank_lists
               where id=lrankId
           ) as lrank,


           ( SELECT  COALESCE( SUM(DATEDIFF(service.sign_off_date, service.sign_on_date))/30, '')
                FROM service WHERE  service.user_id = u.id AND service.rank_list_id = pi.rank_list_id
           ) as sdog,
           (SELECT name from files where (fileable_id=u.id and fileable_type = 'App\\\Models\\\Access\\\User\\\User')) as logo

            ";

        $this->from = "
            FROM users as u LEFT JOIN assigned_roles as ar ON u.id=ar.user_id
            LEFT JOIN  roles as r ON ar.role_id =r.id
            LEFT JOIN personal_info as pi ON u.id=pi.user_id
            LEFT JOIN fleet_lists as fl ON pi.fleet_list_id = fl.id
        ";

        $this->where = " WHERE r.name='Seaman' AND u.confirmed=1 AND u.status=1 AND deleted_at IS NULL ";
        $this->group = "";
        $this->having = "HAVING 1=1 ";
        $this->order = " ORDER BY updated_at DESC ";


        $this->limit =  " LIMIT "
            .($this->page  ? $this->page*$this->perPage . ", " .$this->perPage : $this->perPage);
    }


    private function preparePaginate($res)
    {
        return new Paginator($res, count($this->total),
            $this->perPage, $this->page + 1,
            [
                'path'  => $this->request->url(),
                'query' => $this->request->query()
            ]
        );
    }

    private function vessel($ids)
    {
        if(is_array($ids)){
            $ids = implode(',', array_map('intval', $ids));
            $this->where .= ' AND pi.fleet_list_id IN('.$ids.')';
        }
        else{
            $this->where .= ' AND pi.fleet_list_id='.$ids;
        }
    }


    private function rank($ids)
    {
        if(is_array($ids)){
            $ids = implode(',',$ids);
            $this->where .= ' AND pi.rank_list_id IN('.$ids.')';
        }
        else{
            $this->where .= ' AND pi.rank_list_id='.$ids;
        }
    }




    private function yearrank($amount)
    {

        if (!$this->request->has('vessel') ||
            !$this->request->has('rank') ||
            !$this->request->has('yearrank') ) {
            return;
        }


        $this->sql .= ", (SELECT SUM(DATEDIFF(service.sign_off_date, service.sign_on_date))/365
                FROM service
                 WHERE service.rank_list_id in(".implode(',', (array)$this->request->rank).")
                 AND service.user_id=u.id
            ) as selectedrank ";


//        $this->where = " WHERE r.name='Seaman' ";
        $this->having .= " AND selectedrank >=". $this->request->yearrank;
        $this->having .= " AND yrank > 0";
    }


    public function tankeryearrank()
    {
        if (!$this->request->has('vessel') ||
            !$this->request->has('rank') ||
            !$this->request->has('tankeryearrank') ) {
            return;
        }

        $this->sql .= ", (SELECT SUM(DATEDIFF(service.sign_off_date, service.sign_on_date))/365
                FROM service
                 WHERE  service.rank_list_id in(".implode(',',(array)$this->request->rank).")
                        AND service.user_id=u.id
                   AND  service.type IN (".implode(',', (array)($this->request->vessel)).")
             ) as tankerselectedrank ";

//        $this->where = " WHERE r.name='Seaman' ";
        $this->having .= " AND tankerselectedrank >=". $this->request->tankeryearrank;
    }


    /**
     * @param $SurnameId
     */
    private function searchSeaman($SurnameId)
    {
    	if(is_numeric($SurnameId)){
            $this->where .= " AND u.id LIKE '%".$SurnameId."%'";
    	}
        else {

        	$this->where .= " AND (IF((SELECT COUNT(cv_reviews.id)  FROM cv_reviews
              WHERE  cv_reviews.seaman_id=u.id
                   AND cv_reviews.crewing_id=". $this->user->id. "
            ), u.last_name LIKE '".$SurnameId."%', '' )";
        	$this->where .= " OR  u.first_name LIKE '" .$SurnameId."%'";
        	$this->where .= " ) ";
        }
    }



    private function englishlevel($id)
    {
        $this->where .= " AND  pi.english_proficiency>=".$id;
    }

    private function citizenship($id)
    {
       // $this->where .= " AND  pi.citizenship=".$id;
       $this->where .= " AND  (pi.citizenship=".$id;
        $this->where .= " OR pi.citizenship_1=".$id;
        $this->where .= " OR pi.citizenship_2=".$id;
        $this->where .= " ) ";

    }

    private function salary($amount)
    {
        //$this->where .= " AND  pi.salary_expected>".$amount;
        if(!$this->request->has('sto')) {
            return;
        }
        $sfrom = $this->request->sfrom ?? 0;
        $this->where .= " AND pi.salary_expected BETWEEN "
            .$sfrom." AND ".$this->request->sto." ";


    }

    private function duration($amount)
    {
        if(!$this->request->has('dfrom') ||  !$this->request->has('dto')) {
            return;
        }

        $this->where .= " AND  pi.salary_expected>".$amount;
    }

    private function yold()
    {
        if(!$this->request->has('yoldfrom') || !$this->request->has('yoldto')) {
            return;
        }
        $this->having .= " AND date_of_birth BETWEEN "
              .$this->request->yoldfrom." AND ".$this->request->yoldto." ";

    }

   private function getseadog($amount)
    {
       $request = $this->request;
       $rookie = $request->has('rookie') ? 1 : 0 ;
       $skilled = $request->has('skilled') ? 3 : 0 ;
       $seadog = $request->has('seadog') ? 7 : 0 ;
       $res =  $rookie + $skilled + $seadog;

       switch($res){
           case 1:
               $this->having .= " AND sdog<=12";
               break;
           case 3:
               $this->having .= " AND sdog>12 AND sdog<=60";
               break;
           case 4:
               $this->having .= " AND sdog<=60";
               break;
           case 7:
               $this->having .= " AND sdog>60";
               break;
           case 8:
               $this->having .= " AND sdog<=12 OR sdog>60";
               break;
           case 10:
               $this->having .= " AND sdog>12";
               break;
       }
    }



    private function sort($s)
    {
        //$sortBy = " sdog ";
        $sortBy = " ";

        switch($s) {
            case 'newest':
                $sortBy = " u.updated_at ";
                $this->order = " ORDER BY ".$sortBy." DESC";
                break;
            case 'expirienced':
                $sortBy = " yrank ";
                $this->order = " ORDER BY ".$sortBy." DESC";
                break;
            case 'readiness':
                $sortBy = " dreadiness ";
                $this->order = " ORDER BY ".$sortBy." ASC";
                break;
            case 'strength':
                $sortBy = " strength ";
                $this->order = " ORDER BY ".$sortBy." DESC";
                break;
       }


    }

    private function filters()
    {
        return $this->request->all();
    }

    private function applyFilters()
    {
        foreach ($this->filters() as $name => $value) {
            if(method_exists($this, $name)){
                call_user_func_array([$this, $name], array_filter([$value]));
            }
        }

    }

} // end class