all_lead_list

public function all_lead_list(Request $request) { // return $request->all(); $data_limit = request('data_limit', 50); $data = [ 'data_limit' => $data_limit, 'sl' => ((request('page', 1) - 1) * $data_limit), 'phone' => request('phone', ''), 'name' => request('name', ''), 'email' => request('email', ''), 'start_date' => request('start_date', ''), 'create_start_date' => request('create_start_date', ''), 'create_end_date' => request('create_end_date', ''), 'end_date' => request('end_date', ''), 'assign_date_from' => request('assign_date_from', ''), 'assign_date_to' => request('assign_date_to', ''), 'source' => request('source', []), 'status' => request('status', ''), 'student_status' => request('student_status', ''), 'is_contacted' => request('is_contacted', ''), 'is_counselled' => request('is_counselled', ''), 'lead_category' => request('lead_category', ''), 'tag' => request('tag', []), 'lead_priority' => request('lead_priority', ''), 'assign_to' => request('assign_to', ''), 'destination' => request('destination', ''), 'counsellor_id' => request('counsellor_id', ''), 'processing_counsellor_id' => request('processing_counsellor_id', ''), 'intake' => request('intake', ''), 'preference' => request('preference', ''), 'is_attend_english_proficiency_test' => request('is_attend_english_proficiency_test', ''), 'counsellors' => [], 'telemarketers' => [], ]; $query = Student::leftJoin('expo_events', 'expo_events.student_id', '=', 'students.id') ->leftJoin(DB::raw('(SELECT * FROM counsellings WHERE id IN (SELECT MAX(id) FROM counsellings GROUP BY student_id)) as latest_counselling_activities'), 'latest_counselling_activities.student_id', '=', 'students.id') ->leftJoin(DB::raw('(SELECT * FROM activities WHERE id IN (SELECT MAX(id) FROM activities GROUP BY student_id)) as latest_activities'), 'latest_activities.student_id', '=', 'students.id') // Subquery for latest activities ->leftJoin('activities as all_activities', 'all_activities.student_id', '=', 'students.id') ->leftJoin(DB::raw('(SELECT student_id, COUNT(id) as visit_count FROM front_desks GROUP BY student_id) as frontdesk_counts'), 'frontdesk_counts.student_id', '=', 'students.id') ->select( 'students.id', 'students.merged_student_id', 'students.lead_id', 'students.first_name', 'students.last_name', 'students.email', 'students.alter_email', 'students.phone', 'students.alter_phone', 'students.dob', 'students.academic_qualifications', 'students.status', 'students.is_attend_english_proficiency_test', 'students.english_proficiency', 'students.assign_to', 'students.counsellor_id as counselling_counsellor_id', 'students.priority', 'expo_events.preference', 'students.created_at', 'students.is_contacted', 'students.known_by', 'students.known_by_details', 'students.my_reference', 'students.my_reference_details', 'students.created_by', 'latest_activities.date as activity_date', 'latest_activities.category', 'latest_counselling_activities.counsellor_id', 'latest_counselling_activities.counselling_category', 'latest_counselling_activities.counselling_next_date', 'latest_activities.is_connected', 'latest_activities.contact_medium', 'latest_activities.user_id as activity_user_id', 'frontdesk_counts.visit_count' ) ->orderBy("latest_activities.date") ->groupBy("students.id") ->orderBy("latest_counselling_activities.counselling_next_date") ->when(!empty($data['preference']), function ($q) use ($data) { $q->where('expo_events.preference', 'LIKE', '%' . $data['preference'] . '%'); }); FilterHelper::allLeadFilters($query, $data); $role = ['counsellor', 'telemarketer', 'head_of_counsellor', 'application_officer']; $employees = User::select('id', 'name')->whereIn('role', $role)->select('id', 'name', 'role')->get(); foreach ($employees as $employee) { if ($employee->role === 'counsellor' || $employee->role === 'head_of_counsellor' || $employee->role === 'application_officer') { $data['counsellors'][] = $employee; } elseif ($employee->role === 'telemarketer') { $data['telemarketers'][] = $employee; } } if ($request->export == 'download') { return DownloadLeadList::download($query->get()); } $data['lead_list'] = $query->paginate($data_limit)->withQueryString(); // return $data['lead_list']; $data['count'] = $data['lead_list']->total(); $data['sources'] = GlobalVariables::instance()->source(); $data['total_tag'] = GlobalVariables::instance()->tag(); $data['intakes'] = GlobalVariables::instance()->intakes(); $data['active_menu'] = 'all_lead_list'; $data['page_title'] = 'All Lead List'; $data['user_by_id'] = GlobalVariables::instance()->userById(); $role = Auth::user()->role; $data['tags'] = GlobalVariables::instance()->tagList(); return view('admin.pages.lead_all_list', compact('data')); }