<?php
  
  namespace App\Http\Controllers;

  use App\Models\Category;
  use App\Models\Employer;
  use App\Models\Job_title;
  use App\Models\News;
  use App\Models\reclame;

  use Illuminate\Http\Request;
  
  class MainController extends Controller
  {

      // Главная страница публичной части

      public function index() {

          $news = News::query()->orderBy('id')->limit(6)->get();
  
          $categories = Category::query()->selectRaw('count(ad_employers.id) as cnt, categories.*')
              ->join('ad_employers', 'ad_employers.category_id', '=', 'categories.id')
              ->OrderByDesc('created_at')
              ->GroupBy('categories.id')
              ->get();
  
          $employers = Employer::query()->orderBy('id')->limit(20)->get();
  
          return view('index', compact('news', 'categories', 'employers'));
      }
  
      public function vacancies(Request $request) {
          //должности
          $Job_title = Job_title::query()->orderBy('name')->get();
  
          $categories = Category::query()->selectRaw('count(ad_employers.id) as cnt, categories.*')
                      ->selectRaw('min(ad_employers.salary) as min_salary, max(ad_employers.salary) as max_salary')
                      ->join('ad_employers', 'ad_employers.category_id', '=', 'categories.id')
                      ->join('ad_jobs', 'ad_jobs.ad_employer_id', '=', 'ad_employers.id');
  
          //категории и вакансии
          if ($request->ajax()) {
                $categories = $categories->Where('job_title_id', '=', $request->get('job'))
                                          ->OrderByDesc('created_at')
                                          ->GroupBy('categories.id')
                                          ->get();
          } else {
                  $categories = $categories->OrderByDesc('created_at')
                                             ->GroupBy('categories.id')
                                             ->get();
          }
  
          if ($request->ajax()) {
              return view('ajax.vacancies', compact('categories'));
          } else {
              return view('vacancies', compact('Job_title', 'categories'));
          }
      }
  
      //Вакансии категория детальная
      public function list_vacancies(Request $request) {
         $data = $request->all();
  
         if ($data['categories'] == 'all') {
             $Categories_id = 0;
         } else {
              $Categories_id = $data['categories'];
         }
  
         if ($data['job_titles'] == 'all') {
             $job_titles_id = 0;
         } else {
             $job_titles_id = $data['job_titles'];
         }
  
         $Query = Employer::with('jobs')->with('cat')->select('employers.*');
         if ($Categories_id > 0) {
             $Query = $Query->where('category_id', '=', $Categories_id);
             $Name_categori = Category::query()->where('id', '=', $Categories_id)->get();
  
         }
  
         if ($job_titles_id > 0) {
              $Query = $Query->join('ad_jobs', 'job_title_id', '=', $job_titles_id);
         }
  
         // Данные
         $Job_title = Job_title::query()->get();
         $Query = $Query->OrderBy('updated_at')->paginate(15);
         $Reclama = reclame::query()->limit(3)->get();
  
         return view('list_vacancies', compact('Query', 'Reclama', 'Name_categori', 'Job_title'));

      }
  }