<?php

namespace App\Services;

use Illuminate\Support\Facades\Mail;
use App\User;
use DB;
use App\Models\Service;
use App\Models\Profession;
use App\Models\Pathology;
use App\Models\MedicalCenterSpecialization;
use App\Models\ProfessionSpecialization;
use App\Models\MedicalCenter;

class SearchService
{
    /*     * ************* SET BEGIN TEMPLATE SELECTED 2 ***************************** */

    /* Cerco un Condizione */

    public function getPathologies($search)
    {

        $query = Pathology::
        select(DB::raw('id,'
            . '"pathologies" as selected,'
            . 'name as text,"Condizione" as model, "<i class=\"icofont-stethoscope-alt\"></i>" as image'))
            ->where('name', 'like', '%' . $search . '%')
            ->get();

        return $query;
    }

    /* Cerca un Servizio */

    public function getServices($search)
    {
        $query = Service::
        select(DB::raw('id,name as text,"Servizi" as model, "<i class=\"icofont-stretcher\"></i>" as image,'
            . '"services" as selected'))
            ->where('name', 'like', '%' . $search . '%')
            ->get();
        return $query;
    }

    /* Cerco una  Professione */

    public function getProfession($search)
    {
        $query = Profession::
        select(DB::raw('id,name as text,"Professione" as model, "<i class=\"icofont-bag-alt\"></i>" as image,'
            . '"professions" as selected'))
            ->where('name', 'like', '%' . $search . '%')
            ->get();
        return $query;
    }

    /* Cerca un professionista che abbia una specializzazione  */

    public function getSpecializationForMedicalCenter($search)
    {
        $query = MedicalCenterSpecialization::
        select(DB::raw('id,name as text,"Centro Specializzato" as model, "<i class=\"icofont-ambulance-crescent\"></i>" as image,'
            . '"medical_center_specializations" as selected'))
            ->where('name', 'like', '%' . $search . '%')
            ->get();
        return $query;
    }

    /* Cerca un professionista che abbia una specializzazione  */

    public function getSpecializationForProfession($search)
    {
        $query = ProfessionSpecialization::select(DB::raw('id,name as text,"Professionista Specializzato" as model, "<i class=\"icofont-doctor-alt\"></i>" as image,'
            . '"profession_specializations" as selected'))
            ->where('name', 'like', '%' . $search . '%')
            ->get();
        return $query;
    }

    /* Cerca Professionista per testo */

    public function getProfessionistText($search)
    {
        $query = User::select(DB::raw('users.id,users.user_type,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Specialista/Centro" as model,'
            . '"users" as selected,'
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image'
        ))
            ->leftjoin('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->where(function ($where) use ($search) {
                $where->orwhere('name', 'like', '%' . $search . '%')
                    ->orwhere('surname', 'like', '%' . $search . '%')
                    ->orwhere('business_name', 'like', '%' . $search . '%');
            })
            ->where('users.user_type', '!=', 'admin')
            ->where('users.user_type', '!=', 'care_giver')
            ->where('active', 1)
            ->get();

        return $query;
    }

    /* Cerca un Centro Medico  */

    public function getMedicalCenter($search)
    {
        $query = MedicalCenter::select(DB::raw('id,name as text,"Centro Medico" as model, "<i class=\"icofont-hospital\"></i>" as image,'
            . '"medical_centers" as selected'))
            ->where('name', 'like', '%' . $search . '%')
            ->limit(10)
            ->get();
        return $query;
    }

    /*     * ************** SET END TEMPLATE SELECTED 2 ***************************** */

    public function getProfessionist($id)
    {
        $query = User::select(DB::raw('p.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Professione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,p.name as profession_name"
            . ",users.slug as slug"
        ))
            ->leftjoin('user_profession as pu', function ($join) {
                $join->on('pu.user_id', 'users.id');
            })
            ->leftjoin('professions as p', function ($join) {
                $join->on('pu.profession_id', 'p.id');
            })
            ->leftjoin('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->where('users.user_type', '!=', 'admin')
            ->where('users.user_type', '!=', 'care_giver')
            ->where('users.id', $id);

        return $query;
    }


    private function setAdditionalFilters($query, $additionalFilters) {

        if (isset($additionalFilters["days"])) {
            $query = $query->whereHas('user_details', function ($query) use ($additionalFilters) {
                $query->whereHas('days', function($query) use ($additionalFilters) {
                    $query->whereIn('days.id', $additionalFilters["days"]);
                })->orWhereDoesntHave('days');
            });
        }

        if (isset($additionalFilters["turni"])) {
            $query = $query->whereHas('user_details', function ($query) use ($additionalFilters)  {
                $query->whereHas('turni', function($query) use ($additionalFilters) {
                    $query->whereIn('turni.id', $additionalFilters["turni"]);
                })->orWhereDoesntHave('turni');
            });

        }

        if (isset($additionalFilters['sesso']) && $additionalFilters['sesso'] !== "") {
            $query = $query->where(function ($query) use ($additionalFilters) {
                $query->whereNull('gender')->orWhere('gender', $additionalFilters['sesso']);
            });
        }

        /*if (isset($additionalFilters['esperienza'])) {
            $query = $query->whereHas('user_details', function ($query) use ($additionalFilters) {
                $query->whereNull('working_experience')->orWhere('working_experience', '>=', $additionalFilters['esperienza']);
            });
        }*/

        if (isset($additionalFilters["referenze"])) {
            $query = $query->whereHas('referenze');
        }

        if (isset($additionalFilters["rating"]) && $additionalFilters["rating"]) {
            /*$query = $query->whereHas('user_details', function ($query) use ($additionalFilters) {
                $query->whereNull('rating_avg')->orWhere('rating_avg', '>=', $additionalFilters['rating']);
            })->orderBy('ud.rating_avg', 'DESC');*/

            $query = $query->orderBy('ud.rating_avg', 'DESC');
        }

        if (isset($additionalFilters["prezzo_min"])) {
            $query = $query->whereHas('user_details', function ($query) use ($additionalFilters) {
                $query->whereNull('prezzo_min')->orWhere('prezzo_min', '>=', $additionalFilters['prezzo_min']);
            });
        }

        if (isset($additionalFilters["prezzo_max"])) {
            $query = $query->whereHas('user_details', function ($query) use ($additionalFilters) {
                $query->whereNull('prezzo_min')->orWhere('prezzo_max', '<=', $additionalFilters['prezzo_max']);
            });
        }

        if (isset($additionalFilters["automunito"])) {
            $query = $query->where(function ($query) {
                $query->whereHas('user_details', function ($query)  {
                    $query->whereNull('car_owner')->orWhere('car_owner', 1);
                })->orWhereIn('users.user_type', ['other_istitution', 'medical_center']);
            });
        }

        if (isset($additionalFilters["pathologies"])) {
            $query = $query->whereHas('pathologies', function ($query) use ($additionalFilters) {
                $query->whereIn('id', $additionalFilters["pathologies"]);
            });
        }

        if (isset($additionalFilters["certifications"])) {
            $query = $query->whereHas('certifications', function ($query) use ($additionalFilters) {
                $query->whereIn('id', $additionalFilters["certifications"]);
            });
        }

        if (isset($additionalFilters["fasce_eta"])) {

            $query = $query->whereHas('user_details', function ($query) use ($additionalFilters)  {
                $query->whereHas('fasce_eta', function($query) use ($additionalFilters) {
                    $query->whereIn('fasce_eta.id', $additionalFilters["fasce_eta"]);
                })->orWhereDoesntHave('fasce_eta');
            });
        }





/*
        'days' => request()->days,
                'turni' => request()->turni,
                'rating' => request()->rating,
                'prezzo_min' => request()->prezzo_min,
                'prezzo_max' => request()->prezzo_max,
                'referenze' => request()->referenze,
                 'sesso' => request()->sesso,
                'automunito' => request()->automunito,
                 //'esperienza' => request()->esperienza,
                'pathologies' => request()->pathologies,
                'certifications' => request()->certifications,
                'fasce_eta' => request()->fasce_eta,
*/

        return $query;
    }

    /* Cerca Professionista per professione */

    public function getProfessionistForProfession($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('p.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Professione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,p.name as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_profession as pu', function ($join) {
                $join->on('pu.user_id', 'users.id');
            });

        $query = $query->join('professions as p', function ($join) {
            $join->on('pu.profession_id', 'p.id');
        });

        $query = $query->leftjoin('user_details as ud', function ($join) {
            $join->on('ud.user_id', 'users.id');
        })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });

        if ($selected) {
            $query = $query->where('p.id', $selected);
        }
        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }

        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);

        $query = $query->get();

        return $query;
    }

    /* Cerca Professionista per Professione selezionata del Professionista */

    public function getProfessionistForPathologyProfessionUser($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('users.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Condizione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,pr.name as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_pathology as pu', function ($join) use ($selected) {
                $join->on('pu.user_id', 'users.id');
                if ($selected) {
                    $join->where('pu.pathology_id', $selected);
                }
            })
            ->leftjoin('pathologies', function ($join) {
                $join->on('pathologies.id', 'pu.pathology_id');
            })
            ->leftjoin('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->leftjoin('user_profession as pru', function ($join) {
                $join->on('pru.user_id', 'users.id');
            })
            ->leftjoin('professions as pr', function ($join) {
                $join->on('pru.profession_id', 'pr.id');
            })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });

        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }
        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $query->get();

        return $query;
    }

    /* Cerca Professionista per Professione selezionata nel backend  */

    public function getProfessionistForPathologyProfession($selected = null, $state = null, $city = null, $additionalFilters = [])
    {


        $query = User::select(DB::raw('users.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Condizione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,p.name as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_profession as pu', function ($join) {
                $join->on('pu.user_id', 'users.id');
            })
            ->join('professions as p', function ($join) {
                $join->on('pu.profession_id', 'p.id');
            })
            ->join('pathology_profession as pp', function ($join) {
                $join->on('pp.profession_id', 'p.id');
            })
            ->join('pathologies', function ($join) {
                $join->on('pathologies.id', 'pp.pathology_id');
            })
            ->leftjoin('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });
        if ($selected) {
            $query = $query->where('pathologies.id', $selected);
        }
        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }
        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);

        $query = $query->get();
        return $query;
    }

    /* Cerca Professionista per Servizio scelto dell'utente */

    public function getProfessionistForServiceUser($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('users.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'CONCAT(users.name," ",users.surname) as text,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Prestazione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,"
            . "IF(pr.name IS NOT NULL,pr.name,s.name) as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_service as su', function ($join) {
                $join->on('su.user_id', 'users.id');
            })
            ->join('services as s', function ($join) {
                $join->on('s.id', 'su.service_id');
            })
            ->leftjoin('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->leftjoin('user_profession as pru', function ($join) {
                $join->on('pru.user_id', 'users.id');
            })
            ->leftjoin('professions as pr', function ($join) {
                $join->on('pru.profession_id', 'pr.id');
            })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });

        if ($selected) {
            $query = $query->where('s.id', $selected);
        }
        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }
        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);


        $query = $query->get();
        return $query;
    }

    /* Cerca Professionista per Servizio scelto dell'admin */

    public function getProfessionistForServiceBackend($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('users.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'CONCAT(users.name," ",users.surname) as text,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Prestazione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,"
            . "pr.name as profession_name,ps.service_id"
            . ",users.slug as slug"
        ))
            ->join('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->join('user_service as ps', function ($join) use ($selected) {
                $join->on('ps.user_id', 'users.id');

            })
            ->join('user_profession as pru', function ($join) {
                $join->on('pru.user_id', 'users.id');
            })
            ->join('professions as pr', function ($join) {
                $join->on('pru.profession_id', 'pr.id');
            })

            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });
        if ($selected) {
            $query = $query->where('ps.service_id', $selected);
        }
        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }
        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);

        $query = $query->get();

        return $query;
    }

    /* Cerca Professionista per Medical Center */

    public function getProfessionistForMedicalCenter($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('users.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'CONCAT(users.name," ",users.surname) as text,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Prestazione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,"
            . "mc.name as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->join('medical_centers as mc', function ($join) use ($selected) {
                $join->on('mc.id', 'ud.medical_center_id');
            })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });
        if ($selected) {
            $query = $query->where('ud.medical_center_id', $selected);
        }
        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }
        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);


        $query = $query->get();

        return $query;
    }

    /* Cerca Professionista per Specializzaizone Professione */

    public function getProfessionistForProfessionSpecialization($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('p.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Professione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,"
            . "p.name as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->join('prof_profspec as asp', function ($join) use ($selected) {
                $join->where('asp.specialization_id', $selected);
            })
            ->join('professions as p', function ($join) {
                $join->on('asp.profession_id', 'p.id');
            })
            ->join('user_profession as pu', function ($join) {
                $join->on('pu.user_id', 'users.id');
                $join->on('asp.profession_id', 'pu.profession_id');
            })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });


        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }

        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);

        $query = $query->get();
        return $query;
    }

    /* Cerca Professionista per Specializzaizone Professione scelta dall'user */

    public function getProfessionistForProfessionSpecializationUser($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('p.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Professione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,"
            . "p.name as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->join('user_professionspecialization as asp', function ($join) use ($selected) {
                $join->where('asp.prof_speci_id', $selected);
                $join->on('users.id', 'asp.user_id');
            })
            ->join('profession_specializations as p', function ($join) {
                $join->on('asp.prof_speci_id', 'p.id');
            })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });

        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }

        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);

        $query = $query->get();
        return $query;
    }

    /* Cerca Professionista per Centro Medico Specializzato */

    public function getProfessionistForMedicaCenterSpecialization($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('users.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'CONCAT(users.name," ",users.surname) as text,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Prestazione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,"
            . "mc.name as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->join('medcent_medcentspec as asm', function ($join) use ($selected) {
                $join->where('asm.specialization_id', $selected);
            })
            ->join('medical_centers as mc', function ($join) use ($selected) {
                $join->on('mc.id', 'asm.medical_center_id');
                $join->on('mc.id', 'ud.medical_center_id');
            })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });
        if ($selected) {
            $query = $query->where('ud.medical_center_id', $selected);
        }
        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }

        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);


        $query = $query->get();

        return $query;
    }

    /* Cerca Professionista per Centro Medico Specializzato dall'utente */

    public function getProfessionistForMedicaCenterSpecializationUser($selected = null, $state = null, $city = null, $additionalFilters = [])
    {

        $query = User::select(DB::raw('users.id,users.id as id_user,users.user_type,'
            . '"users" as selected,'
            . 'CONCAT(users.name," ",users.surname) as text,'
            . 'IF(ud.business_name IS NOT NULL, ud.business_name , CONCAT(users.name," ",users.surname)) as text,'
            . '"Prestazione" as model, '
            . 'IF(path_url IS NOT NULL,CONCAT("<img src=\'/upload/", path_url,"\' width=\'30px\' style=\'position: relative;left: 0;\'/>"),"<i class=\"icofont-doctor-alt\"></i>") as image,'
            . "users.path_url as path_url,"
            . "mc.name as profession_name"
            . ",users.slug as slug"
        ))
            ->join('user_details as ud', function ($join) {
                $join->on('ud.user_id', 'users.id');
            })
            ->join('user_med_spec as ums', function ($join) use ($selected) {
                $join->where('ums.medical_speci_id', $selected);
                $join->on('users.id', 'ums.user_id');
            })
            ->join('medical_center_specializations as mc', function ($join) use ($selected) {
                $join->on('mc.id', 'ums.medical_speci_id');
            })
            ->where('active', 1)
            ->where(function ($join) {
                $join->Orwhere('user_type', 'medical_center')
                    ->Orwhere('user_type', 'professionist')
                    ->Orwhere('user_type', 'other_istitution');
            });


        if ($state) {
            $query = $query->where('ud.state_id', $state);
        }
        if ($city) {
           $query = $query->orderByRaw("FIELD(city_id, \"$city\") DESC");
        }

        $query = $this->setAdditionalFilters($query, $additionalFilters);

        $query = $query->get();

        return $query;
    }
}