Untitled

public function datatables(Request $request)
    {

        try {
            if (Gate::denies('lead_access')) {
                return Response::json([
                    "draw" => intval($request->input('draw')),
                    "recordsTotal" => 0,
                    "recordsFiltered" => 0,
                    "data" => [],
                ]);
            }

            // datatable columns
            $columns = $request->input('columns');

            $recordsTotal = $recordsFiltered = Lead::whereNot('is_prospect', 1)->count();
            $leadQuery = Lead::select([
                'leads.id',
                'leads.name',
                'leads.priority',
                'leads.uuid',
                'leads.owner_id',
                'leads.is_prospect',
                'leads.status',
                'leads.year',
                'leads.is_client',
                'owners.name as owner_name',
                'addresses.street as addr_address',
                'addresses.state as addr_state',
                'addresses.city as addr_city',
                DB::raw("CONCAT(first_name, ' ', last_name) as contact_name"),
                DB::raw('(SELECT COUNT(DISTINCT county_id) FROM properties WHERE lead_id = leads.id) as county_count'),
                DB::raw('(SELECT SUM(interest) FROM properties WHERE lead_id = leads.id) as total_interest'),
                DB::raw('(SELECT AVG(interest) FROM properties WHERE lead_id = leads.id) as average_interest'),
                DB::raw('(SELECT SUM(preliminary_appraised_value) FROM properties WHERE lead_id = leads.id) as total_preliminary_value'),
            ])
                ->leftJoin('owners', 'leads.owner_id', '=', 'owners.id')
                ->leftJoin('addresses', function ($join) {
                    $join->where('addresses.id', '=', function ($query) {
                        $query->select('id')
                            ->from('addresses')
                            ->whereColumn('model_id', '=', 'leads.id')
                            ->where('model_type', '=', 'Lead')
                            ->where('is_imported', '=', 1)
                            ->latest()
                            ->limit(1);
                    });
                })
                ->whereNot('leads.is_prospect', 1);

            if ($request->filled('leadYear')) {
                $leadQuery->where('leads.year', $request->leadYear);
            }

            // Search
            if ($request->filled('search.value')) {
                $searchTerm = $request->input('search.value');
                $searchTerms = explode(' ', $searchTerm);
                $leadQuery->where(function ($query) use ($searchTerm, $searchTerms) {
                    $preliminaryVal = str_replace(',', '', $searchTerm);
                    $query->where('leads.name', 'LIKE', "%$searchTerm%")
                        ->orWhere('leads.priority', 'LIKE', "%$searchTerm%")
                        ->orWhere('leads.status', 'LIKE', "%$searchTerm%")
                        ->orWhere('leads.year', 'LIKE', "%$searchTerm%")
                        ->orWhere('owners.name', 'LIKE', "%$searchTerm%")
                        ->orWhere('addresses.street', 'LIKE', "%$searchTerm%")
                        ->orWhere('addresses.state', 'LIKE', "%$searchTerm%")
                        ->orWhere('addresses.city', 'LIKE', "%$searchTerm%")
                        ->orWhere(DB::raw('(SELECT COUNT(DISTINCT county_id) FROM properties WHERE lead_id = leads.id)'), 'LIKE', "%$searchTerm%")
                        ->orWhere(DB::raw('(SELECT SUM(interest) FROM properties WHERE lead_id = leads.id) '), 'LIKE', "%$searchTerm%")
                        ->orWhere( DB::raw('(SELECT AVG(interest) FROM properties WHERE lead_id = leads.id)'), 'LIKE', "%$searchTerm%")
                        ->orWhere( DB::raw('(SELECT SUM(preliminary_appraised_value) FROM properties WHERE lead_id = leads.id)'), 'LIKE', "%$preliminaryVal%");
                       

                    $query->orWhere('leads.first_name', 'LIKE', "%$searchTerms[0]%");
                    if (isset($searchTerms[1])) {
                        $query->orWhere('leads.last_name', 'LIKE', "%$searchTerms[1]%");
                    }
                });

                // get filtered record count
                $recordsFiltered = $leadQuery->count();
            }

            // Sorting
            if ($request->filled('order.0.column') && $request->filled('order.0.dir')) {
                $orderByColumnIndex = $request->input('order.0.column');
                $orderBy = $columns[$orderByColumnIndex]['data'];
                $orderDirection = $request->input('order.0.dir');
                $leadQuery->orderBy(
                    DB::raw('(CASE WHEN status = "New" 
                    THEN 0 ELSE 1 END)')
                )->orderBy($orderBy, $orderDirection);
            }
            // Pagination
            $start = $request->input('start', 0);
            $length = $request->input('length');
            $length = $length > 1 ? $length : 20;
            $leadQuery->skip($start)->take($length);

            $permissions = [
                'markPropspect' => Gate::allows('lead_edit'),
                //'view' => Gate::allows('lead_access'),
                'edit' => Gate::allows('lead_edit'),
            ];

            $leads = $leadQuery->get();
            $leadsData = $leads->map(function ($lead) use ($permissions) {

                $actionData = [];
                // if ($permissions['view']) {
                //     $actionData["view"] = [
                //         "route" => route('leads.show', $lead->uuid),
                //     ];
                // }
                if ($permissions['edit']) {
                    $actionData["edit"] = [
                        "route" => route('leads.edit', $lead->uuid),
                    ];
                }

                return [
                    "id" => $lead->id,
                    "uuid" => $lead->uuid,
                    "name" => $lead->name,
                    "status" => $lead->status,
                    "year" => $lead->year,
                    "addr_address" => $lead->addr_address,
                    "addr_state" => $lead->addr_state,
                    "addr_city" => $lead->addr_city,
                    "county_count" => $lead->county_count,
                    "total_interest" => round($lead->total_interest, 2),
                    "average_interest" => round($lead->average_interest, 2),
                    "total_preliminary_value" => numberFormatWithDollarSign($lead->total_preliminary_value ),
                    "priority" => $lead->priority ?? '',
                    "owner_name" => $lead?->owner_name,
                    "contact_name" => $lead?->contact_name,
                    "detail_route" => route($lead->is_prospect == 1 ? 'prospects.show' : 'leads.show', ['lead' => $lead->uuid]),
                    'showCheckBox' => !$lead->owner_id,
                    'is_client' => !empty($lead->is_client) ? 'Yes' : 'No',
                    "action" => view('common.dataTableAction', $actionData)->render(),

                ];
            });

            return Response::json([
                "draw" => intval($request->input('draw')),
                "recordsTotal" => $recordsTotal,
                "recordsFiltered" => $recordsFiltered,
                "data" => $leadsData,
                "next_start" => $start + $length,
            ]);

        } catch (\Exception $ex) {

            Log::error("LeadController->datatables()", [
                'error' => $ex->getMessage(),
                'errorLine' => $ex->getLine(),
            ]);
            return Response::json([
                "draw" => intval($request->input('draw')),
                "recordsTotal" => 0,
                "recordsFiltered" => 0,
                "data" => [],
            ]);
        }
    }