Report Help

Hello, This is a function for inquiry report in my software.

public function inquiryReports(Request $request) { $templateData['inquiries'] = []; $helpTab = new HelpTabApi(); $templateData['help_tab_content'] = $helpTab->getHelpTabContent('report_inq_tab'); $templateData['params'] = $request->except(['_token', '_method', 'submit']); $templateData['status'] = $request->status; $templateData['start_date'] = $request->start_date; $templateData['end_date'] = $request->end_date; $templateData['global'] = $request->global = 'true' ? $request->global : 'false';

    if ($request->status && $request->start_date && $request->end_date) {
        $sortBy = $request->sortBy ? $request->sortBy : 'name';
        $sortOrder = $request->order ? $request->order : 'asc';

        $inquiry = ShInquiry::query();

        // When full search is set
        $inquiry->when(!empty($request->full_search), function ($query) {
            $query->where(function ($query) {
                $query->whereHas('stakeholder', function ($query) {
                    $query->where('stakeholders.full_name', 'like', '%' . request('full_search') . '%')
                        ->orWhereRaw('CONCAT(stakeholders.first_name, " ", stakeholders.last_name) like ?', ['%' . request('full_search') . '%']);
                });

            });
        });

        $inquiry->when($sortBy === 'name', function ($query) use ($sortOrder) {
            return $query->join('stakeholders as sh', 'sh.id', '=', 'sh_inquiry.sh_id')
                ->select('sh.id as stakeholderId', 'sh.full_name')
                ->orderBy(\DB::raw('CONCAT(sh.first_name, " ", sh.last_name)'), $sortOrder);
        });

        $inquiry->when($sortBy === 'method_of_inquiry', function ($query) use ($sortOrder) {
            return $query->join('type_of_contact as toc', 'toc.id', '=', 'sh_inquiry.sh_inquiry_method')
                ->select('toc.id as tocId', 'toc.type_of_contact_name')
                ->orderBy('toc.type_of_contact_name', $sortOrder);
        });

        $inquiry->when($sortBy != 'name' && $sortBy != 'method_of_inquiry', function ($query) use ($sortBy, $sortOrder) {
            return $query->orderBy($sortBy, $sortOrder);
        });



        if ($request->status != 3) {
            if (request('status') == 1) {
                $inquiry->where('sh_inquiry_fu', 1);
            } else {
                $inquiry->where('sh_inquiry_fu', 0);
            }
        }



        // When StartDate is set
        $inquiry->when(!empty($request->start_date), function ($query) {
            $query->whereDate('sh_inquiry_date', '>=', Carbon::parse(request('start_date'))->toDateString());
        });

        // When EndDate is set
        $inquiry->when(!empty($request->start_date), function ($query) {
            $query->whereDate('sh_inquiry_date', '<=', Carbon::parse(request('end_date')));
        });



        if ($request->global == 'true') {
            $templateData['global'] = 'true';
        } else {
            $inquiry->where('sh_inquiry.project_id', session()->get('project_id'));
            $templateData['global'] = 'false';
        }

        if ($request->pdf_export) {
            $templateData['inquiries'] = $inquiry->select('sh_inquiry.*')->get();
            $pdf = PDF::loadView('reports.pdf.inquiry_export', $templateData);
            return $pdf->download('stakeholders.pdf');
        }

        if ($request->excel_export) {
            $inquiries = $inquiry->select('sh_inquiry.*')->get();
            return Excel::download(new InquiryReportsExport($inquiries), 'stakeholders' . Carbon::now()->format('d-m-Y') . '.xlsx');
        }

        if ($request->csv_export) {
            $inquiries = $inquiry->select('sh_inquiry.*')->get();
            return Excel::download(new InquiryReportsExport($inquiries), 'stakeholders' . Carbon::now()->format('d-m-Y') . '.csv');
        }

        if ($request->print) {
            $templateData['inquiries'] = $inquiry->select('sh_inquiry.*')->get();
            $loadView = \View::make('reports.inquiry_print', $templateData);
            $content = (string) $loadView;
            $content = $loadView->render();

            return response()->json(['content' => $content]);
        }

        $templateData['inquiries'] = $inquiry->select('sh_inquiry.*')->with('followups')->paginate(10);


        if ($request->ajax()) {
            $pagination = $templateData['inquiries'];
            $pageData = View('reports.inquiry_report_rows', $templateData)->render();
            $paginationData = [
                'total' => $pagination->total(),
                'currentPage' => $pagination->currentPage(),
                'lastPage' => $pagination->lastPage(),
            ];

            return response()->json([
                'content' => $pageData,
                'pagination' => $paginationData
            ], 200);
        }
    }
    return View('reports.inquiry_reports', $templateData);
}

This works as expected. It shows all inquires and associated followups within the start_date and end_date parameters. I now need it to show any quiry and associated followups if a followup happened to an inquiry that is outside the start_date and end_date parameters.

I added the following code:

// get inquiries if a followup happened out the date range

        $inquiry->whereHas('followups', function ($query) use ($start_date) {
        $query->whereDate('followup_date', '>=', $start_date);
    });

Just adding this code resulted in $start_date as being undefined.

So I added $start_date = $request->start_date;

I know start_date and end_date are being defined in the request but I could not figure out how to use it from there so I added this line.

Now I get no results. Can someone help point me in the right direction. I'm a laravel newbie so take it easy on me.

Thanks,

Steve