streamline-emr/docker/streamline-src/Modules/Reports/Http/Controllers/StreamlineReportsController.php

<?php

namespace Modules\Reports\Http\Controllers;

use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Streamline\Models\AgeGroup;
use Streamline\Models\Consultation;
use Streamline\Models\Diagnosis;
use Streamline\Models\InpatientInfo;
use Streamline\Models\InvestigationResults;
use Streamline\Models\MaternityDeliveryRecord;
use Streamline\Models\PatientAppointment;
use Streamline\Models\HospitalInformation;
use Streamline\Models\Anaesthesia;
use Streamline\Models\PatientCategory;
use Streamline\Models\PatientRegistrationField;
use Streamline\Models\Surgery;
use Streamline\Models\Triage;
use Streamline\Models\User;
use Streamline\Models\Ward;
use Streamline\Models\WardProcedure;
use Streamline\Models\OrderedProcedure;
use Barryvdh\Snappy\Facades\SnappyPdf;
use Streamline\Models\OrderedService;
use Streamline\Models\StaffPerformedService;
use Streamline\Models\WardConsultationsAndService;

class StreamlineReportsController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
        //$this->middleware('permission:streamline-reports-list');
    }

    /**
     * Display a listing of the resource.
     *
     */
    public function index() {
        $diagnoses = DB::table('diagnoses')->where('available', 1)->whereNull('deleted_at')->pluck('name', 'id')->toArray();
        $diagnoses = ['0' => 'All'] + $diagnoses;

        $age = DB::table('age_groups')->whereNull('deleted_at')->pluck('name', 'id')->toArray();
        $age = [-1 => 'Custom Age Group'] + $age;
        $age = ['' => 'All'] + $age;

        $treatments = DB::table('drugs')->whereNull('deleted_at')->pluck('name', 'id')->toArray();
        $treatments = [0 => 'All'] + $treatments;

        $investigations = DB::table('investigations')->whereNull('deleted_at')->pluck('name', 'id')->toArray();
        $investigations = [0 => 'All'] + $investigations;

        $symptoms = DB::table('symptoms')->whereNull('deleted_at')->pluck('name', 'id')->toArray();
        $symptoms = [0 => 'All'] + $symptoms;
        $patient_categories = PatientCategory::orderBy('name')->pluck('name', 'id')->prepend('All', '0');
        $custom_reg_fields = PatientRegistrationField::orderBy('name', 'asc')->pluck('name', 'id')->prepend('All', '0');

        return view('reports::hmis_reports.streamline_reports', compact('diagnoses', 'symptoms', 'age', 'treatments', 'patient_categories','investigations', 'custom_reg_fields'));
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
    }

    /**
     * access the different reports
     */
    public function listReports()
    {
        return view('reports::hmis_reports.list_memorised_reports');
    }

    public function health_unit_quarterly_report()
    {
        return view('reports::hmis_reports.health_unit_quarterly_report');
    }

    public function charge_book()
    {
        $procedures = DB::table('procedures')->whereNull('deleted_at')->orderBy('name')->get()->toArray();
        $services = DB::table('services')->whereNull('deleted_at')->orderBy('name')->get()->toArray();
        $sundries = DB::table('sundries')->whereNull('deleted_at')->orderBy('name')->get()->toArray();
        $investigations = DB::table('investigations')->whereNull('deleted_at')->orderBy('name')->get()->toArray();
        $drugs = DB::table('drugs')->whereNull('deleted_at')->orderBy('name')->get()->toArray();
        $bed_categories = DB::table('inpatient_bed_categories')->whereNull('deleted_at')->get()->toArray();

        return view('reports::reports.charge-book.index', compact(
            'procedures',
            'services',
            'sundries',
            'investigations',
            'drugs',
            'bed_categories'
        ));
    }

    public function charge_book_print()
    {
        $procedures = DB::table('procedures')->orderBy('name')->get()->toArray();
        $services = DB::table('services')->orderBy('name')->get()->toArray();
        $sundries = DB::table('sundries')->orderBy('name')->get()->toArray();
        $investigations = DB::table('investigations')->orderBy('name')->get()->toArray();
        $drugs = DB::table('drugs')->orderBy('name')->get()->toArray();
        $bed_categories = DB::table('inpatient_bed_categories')->get()->toArray();

        $hospital_information = HospitalInformation::find(1);

        $data = [
            "procedures" => $procedures,
            "services" => $services,
            "sundries" => $sundries,
            "investigations" => $investigations,
            "drugs" => $drugs,
            "bed_categories" => $bed_categories,
            'hospitalInfo' => $hospital_information,
        ];

        $pdf = SnappyPDF::loadView('reports::reports/charge-book/print', $data)
            ->setOrientation('landscape')
            ->setPaper('a4')
            ->setOption('margin-bottom', 10)
            ->setOption('footer-html', '<i>Stre@mline</i>');


        return $pdf->inline('Streamline Charge Book' . date(" d-m-y h:ia") . '.pdf');
    }

    /**
     * return cases this week
     */
    public function cases()
    {
        return view('reports::hmis_reports.weekly_epidem_form');
    }

    public function streamline_reports_details(Request $request) {
        $criteria = '';
        $filters = $eye_filters = [];

        // check if gender is selected
        if (!is_null($request->gender)) {
            if ($request->gender == 0) {
                $criteria .= 'Gender (All) ';
            } elseif ($request->gender == 1) {
                $filters[] = $eye_filters[] = ['patients.gender', '=', 1];
                $criteria .= 'Gender (Male) ';
            } elseif ($request->gender == 2) {
                $filters[] = $eye_filters[] = ['patients.gender', '=', 2];
                $criteria .= 'Gender (Female) ';
            }
        }

        //Patient Registration Fields
        if(!empty($request->custom_reg_fields)) {
            if (!empty($custom_reg_field_options)) {
                $filters[] = $eye_filters[] = ['patients.registration_fields', 'LIKE', '%'.$request->custom_reg_field_options.'%'];
                $criteria .= 'Patient Registation Fields (' . get_name($request->custom_reg_fields, 'id', 'name', 'patient_registration_fields') . ') ';
                $criteria .= 'Patient Registation Field option (' . $request->custom_reg_field_options . ') ';
            } else {
                $filters[] = $eye_filters[] = ['patients.registration_fields', 'LIKE', '%reg_field_'.$request->custom_reg_fields.'%'];
                $criteria .= 'Patient Registation Fields (' . get_name($request->custom_reg_fields, 'id', 'name', 'patient_registration_fields') . ') ';
            }
            $custom_field = get_name($request->custom_reg_fields, 'id', 'name', 'patient_registration_fields');
            if(!empty($request->custom_reg_field_options)) $custom_field .= ' ('.$request->custom_reg_field_options. ')';
            session()->put('custom_field', $custom_field);
        }

        // check if age group is selected
        if (!is_null($request->age)) {
            if ($request->age == -1) {
                // for the custom age
                $start_date = Carbon::now()->subYears($request->first_age);
                $end_date = Carbon::now()->subYears($request->second_age);

                $criteria .= 'Age Group:(' . $request->first_age . ' - ' . $request->second_age . ' Years) ';
            } else {
                $age_group = DB::table('age_groups')->where('id', $request->age)->first();

                if ($age_group->age_type == 1) {
                    $start_date = Carbon::now()->subYears($age_group->from_age);
                    $end_date = Carbon::now()->subYears($age_group->to_age);
                } elseif ($age_group->age_type == 2) {
                    $start_date = Carbon::now()->subMonths($age_group->from_age);
                    $end_date = Carbon::now()->subMonths($age_group->to_age);
                } elseif ($age_group->age_type == 3) {
                    $start_date = Carbon::now()->subDays($age_group->from_age);
                    $end_date = Carbon::now()->subDays($age_group->to_age);
                }

                $criteria .= 'Age Group:' . $age_group->name . ' ';
            }

            array_push($filters, ['patients.date_of_birth', '<', $start_date->toDateString()], ['patients.date_of_birth', '>', $end_date->toDateString()]);
            array_push($eye_filters, ['patients.date_of_birth', '<', $start_date->toDateString()], ['patients.date_of_birth', '>', $end_date->toDateString()]);
        }

        if (!is_null($request->district) && is_numeric($request->district)) {
            $filters[] = $eye_filters[] = ['patients.district_id', '=', $request->district];
            $criteria .= 'District (' . get_name($request->district, 'id', 'name', 'districts') . ') ';
        }

        if (!is_null($request->county) && is_numeric($request->county)) {
            $filters[] = $eye_filters[] = ['patients.county_id', '=', $request->county];
            $criteria .= 'County (' . get_name($request->county, 'id', 'name', 'counties') . ') ';
        }

        if (!is_null($request->sub_county) && is_numeric($request->sub_county)) {
            $filters[] = $eye_filters[] = ['patients.subcounty_id', '=', $request->sub_county];
            $criteria .= 'Sub County (' . get_name($request->sub_county, 'id', 'name', 'subcounties') . ') ';
        }

        if (!is_null($request->parish) && is_numeric($request->parish)) {
            $filters[] = $eye_filters[] = ['patients.parish_id', '=', $request->parish];
            $criteria .= 'Parish (' . get_name($request->parish, 'id', 'name', 'parishes') . ') ';
        }

        if (!is_null($request->village) && is_numeric($request->village)) {
            $filters[] = $eye_filters[] = ['patients.village_id', '=', $request->village];
            $criteria .= 'Village (' . get_name($request->village, 'id', 'name', 'villages') . ') ';
        }

        $patient_categories_names = DB::table('patient_categories')->pluck('name', 'id')->toArray();

        if ($request->report_category == 1) {
            if (!Auth::user()->can('streamline-reports-diagnosis')) {
                flash('You do not have access to this report.')->error();
                return redirect('/streamline_reports');
            }

            // specific search queries for consultations
            // check if inpatient/outpatient is selected
            if (!is_null($request->in_out_patient)) {
                if ($request->in_out_patient == 0) {
                    $criteria .= 'Inpatient and Outpatient ';
                } elseif ($request->in_out_patient == 1) {
                    $filters[] = ['consultations.ward_id', '!=', NULL];
                    $eye_filters[] = ['e.ward_id', '!=', NULL];
                    $criteria .= 'Inpatient ';
                } elseif ($request->in_out_patient == 2) {
                    $filters[] = ['consultations.ward_id', '=', NULL];
                    $eye_filters[] = ['e.ward_id', '=', NULL];
                    $criteria .= 'Outpatient ';
                }
            }

            // check if dates are selected
            if (!is_null($request->start_date) && !is_null($request->end_date)) {
                $start_date = Carbon::parse($request->start_date)->startOfDay()->toDateTimeString();
                $end_date = Carbon::parse($request->end_date)->endOfDay()->toDateTimeString();

                $filters[] = ['consultations.created_at', '>', $start_date];
                $filters[] = ['consultations.created_at', '<', $end_date];
                $eye_filters[] = ['e.created_at', '>', $start_date];
                $eye_filters[] = ['e.created_at', '<', $end_date];
                $criteria .= 'Between (' . $request->start_date . ') And (' . $request->end_date . ')';
            }

            if(!empty($request->patient_category)) {
                $filters[] = ['patients.category_id', $request->patient_category];
                $criteria .= ' Patient Category(' . ($patient_categories_names[$request->patient_category] ?? 'N/A') . ') ';
            } else {
                $criteria .= ' Patient Category(All) ';
            }

            // search for diagnosis
            if (is_null($request->diagnosis)) {
                flash("Please select a diagnosis")->error();
                return redirect("streamline_reports");
            } else {
                if ($request->diagnosis != 0) {
                    // get specified diagnosis
                    $filters[] = ['consultations.primary_diagnosis', '=', $request->diagnosis];
                    $eye_diagnoses = DB::table('eye_clinic_main_exam as e')->leftjoin('patients', 'e.patient_id', '=', 'patients.id')
                        ->where($eye_filters)
                        ->whereRaw("FIND_IN_SET(".$request->diagnosis. ",e.right_eye_diagnosis) OR FIND_IN_SET(".$request->diagnosis. ",e.left_eye_diagnosis)")
                        ->get(['patients.id', 'patients.gender', 'e.right_eye_diagnosis', 'e.left_eye_diagnosis']);
                } else {
                    $eye_diagnoses = DB::table('eye_clinic_main_exam as e')->leftjoin('patients', 'e.patient_id', '=', 'patients.id')
                        ->where($eye_filters)
                        ->get(['patients.id', 'patients.gender', 'e.right_eye_diagnosis', 'e.left_eye_diagnosis']);
                }

                // collect patient information and along with consultations
                $diagnosis = Consultation::where($filters)
                    ->join('patients', 'consultations.patient_id', '=', 'patients.id')
                    ->select('patients.id', 'patients.gender', 'consultations.primary_diagnosis')
                    ->get();

                if (count($diagnosis) > 0 || count($eye_diagnoses) > 0) {

                    // store the search in the session variable
                    session()->put('search_query_filters', $filters);
                    session()->put('eye_search_query_filters', $eye_filters);

                    // ready the ammunition
                    $male_totals_array = [];
                    $female_totals_array = [];
                    $diagnosis_array = [];
                    $male_totals = 0;
                    $female_totals = 0;

                    // loop through the results, make sure there no repeated diagnosis, or is that diagnoses?, ids and total for the genders
                    foreach ($diagnosis as $record) {

                        // check if we have already recorded the diagnosis
                        if (in_array($record->primary_diagnosis, $diagnosis_array)) {
                            // We've struck gold! Now find the key
                            $key = array_search($record->primary_diagnosis, $diagnosis_array);
                            // increment the appropriate genders
                            if ($record->gender == 1) {
                                // use the key to find count dracula himself
                                $current_count = $male_totals_array[$key] ?? 0;
                                $male_totals_array[$key] = $current_count + 1;

                                $male_totals++;
                            } elseif ($record->gender == 2) {
                                // or is it a her...self
                                $current_count = $female_totals_array[$key] ?? 0;
                                $female_totals_array[$key] = $current_count + 1;

                                $female_totals++;
                            }
                        } else {
                            // diagnosis is not recorded so insert it
                            $diagnosis_array[] = $record->primary_diagnosis;

                            // bring up the initiates
                            if ($record->gender == 1) {
                                // the males
                                $male_totals_array[] = 1;
                                $female_totals_array[] = 0;

                                $male_totals++;
                            } elseif ($record->gender == 2) {
                                // the females
                                $female_totals_array[] = 1;
                                $male_totals_array[] = 0;

                                $female_totals++;
                            }
                        }
                    }

                    foreach ($eye_diagnoses as $record) {
                        $actual_diagnoses = [];
                        $right_eye_diagnosis = explode(',',$record->right_eye_diagnosis);
                        $left_eye_diagnosis = explode(',',$record->left_eye_diagnosis);
                        foreach ($right_eye_diagnosis as $value) $actual_diagnoses[] = $value;
                        foreach ($left_eye_diagnosis as $value) $actual_diagnoses[] = $value;
                        $unique_diagnoses = array_unique($actual_diagnoses);
                        foreach ($unique_diagnoses as $eye_diagnosis) {
                            if ($request->diagnosis != 0 && $eye_diagnosis != $request->diagnosis) {
                                continue;
                            }

                            // check if we have already recorded the diagnosis
                            if (in_array($eye_diagnosis, $diagnosis_array)) {
                                // We've struck gold! Now find the key
                                $key = array_search($eye_diagnosis, $diagnosis_array);
                                // increment the appropriate genders
                                if ($record->gender == 1) {
                                    // use the key to find count dracula himself
                                    $current_count = $male_totals_array[$key] ?? 0;
                                    $male_totals_array[$key] = $current_count + 1;

                                    $male_totals++;
                                } elseif ($record->gender == 2) {
                                    // or is it a her...self
                                    $current_count = $female_totals_array[$key] ?? 0;
                                    $female_totals_array[$key] = $current_count + 1;

                                    $female_totals++;
                                }
                            } else {
                                // diagnosis is not recorded so insert it
                                $diagnosis_array[] = $eye_diagnosis;

                                // bring up the initiates
                                if ($record->gender == 1) {
                                    // the males
                                    $male_totals_array[] = 1;
                                    $female_totals_array[] = 0;

                                    $male_totals++;
                                } elseif ($record->gender == 2) {
                                    // the females
                                    $female_totals_array[] = 1;
                                    $male_totals_array[] = 0;

                                    $female_totals++;
                                }
                            }
                        }
                    }
                    // send the data with some kisses
                    return view('reports::hmis_reports.diagnosis_report', compact('criteria', 'diagnosis_array', 'male_totals_array', 'female_totals_array', 'male_totals', 'female_totals'));
                } else {
                    flash("No results found for the diagnosis search")->error();
                    return redirect("streamline_reports");
                }
            }
        } elseif ($request->report_category == 2) {
            if (!Auth::user()->can('streamline-reports-treatment')) {
                flash('You do not have access to this report.')->error();
                return redirect('/streamline_reports');
            }

            // search for treatment

            if (is_null($request->treatment)) {
                flash("Please select a drug")->error();
                return redirect("streamline_reports");
            } else {

                // check if dates are selected
                if (!is_null($request->start_date) && !is_null($request->end_date)) {
                    $start_date = Carbon::parse($request->start_date)->startOfDay()->toDateTimeString();
                    $end_date = Carbon::parse($request->end_date)->endOfDay()->toDateTimeString();

                    $filters[] = ['patient_dispensings.created_at', '>', $start_date];
                    $filters[] = ['patient_dispensings.created_at', '<', $end_date];
                    $criteria .= 'Between (' . $request->start_date . ') And (' . $request->end_date . ')';
                }

                if(!empty($request->patient_category)) {
                    $filters[] = ['patients.category_id', $request->patient_category];
                    $criteria .= ' Patient Category(' . ($patient_categories_names[$request->patient_category] ?? 'N/A') . ') ';
                } else {
                    $criteria .= ' Patient Category(All) ';
                }

                // collect patient information and along with consultations
                $drugs = DB::table('patient_dispensings')
                    ->where($filters)
                    ->join('patients', 'patient_dispensings.patient_id', '=', 'patients.id')
                    ->select('patient_dispensings.drugs', 'patient_dispensings.quantity_dispensed')
                    ->get();

                if ($drugs->count() > 0) {

                    // store the search in the session variable
                    session()->put('search_query_filters', $filters);

                    // check if a specific treatment has been searched for and go straight to details
                    if ($request->treatment != 0) {
                        return redirect('/streamline_reports/treatments_drill_down/' . $request->treatment);
                    }

                    $drugs_array = [];
                    $dispensed_array = [];

                    foreach ($drugs as $drug) {
                        // split into an array for drugs
                        $temp_drugs_array = explode(",", $drug->drugs);
                        $temp_dispensed_array = explode(",", $drug->quantity_dispensed);

                        // loop through the temporary arrays
                        for ($i = 0; $i < count($temp_dispensed_array); $i++) {
                            // check if we have already recorded the diagnosis
                            if (in_array($temp_drugs_array[$i], $drugs_array)) {
                                // We've struck gold! Now find the key
                                $key = array_search($temp_drugs_array[$i], $drugs_array);
                                $dispensed_key = isset($dispensed_array[$key]) ? (int)$dispensed_array[$key] : 0;
                                $temp_dispensed_key = isset($temp_dispensed_array[$i]) ? (int)$temp_dispensed_array[$i] : 0;

                                $dispensed_array[$key] = $dispensed_key + $temp_dispensed_key;
                            } else {
                                // diagnosis is not recorded so insert it
                                $drugs_array[] = $temp_drugs_array[$i];
                                $dispensed_array[] = $temp_dispensed_array[$i];
                            }
                        }
                    }

                    // check if drugs have returned results
                    if (count($drugs_array) < 1) {
                        flash("No results found for the treatment search")->error();
                        return redirect("streamline_reports");
                    }

                    return view('reports::hmis_reports.treatment_report', compact('criteria', 'drugs_array', 'dispensed_array'));
                } else {
                    flash("No results found for the treatment search")->error();
                    return redirect("streamline_reports");
                }
            }
        } elseif ($request->report_category == 3) {
            if (!Auth::user()->can('streamline-reports-investigations')) {
                flash('You do not have access to this report.')->error();
                return redirect('/streamline_reports');
            }

            // search for lab tests

            if (is_null($request->lab_test)) {
                flash("Please select a laboratory test")->error();
                return redirect("streamline_reports");
            } else {
                // check if dates are selected
                if (!is_null($request->start_date) && !is_null($request->end_date)) {
                    $start_date = Carbon::parse($request->start_date)->startOfDay()->toDateTimeString();
                    $end_date = Carbon::parse($request->end_date)->endOfDay()->toDateTimeString();

                    $filters[] = ['investigation_results.created_at', '>', $start_date];
                    $filters[] = ['investigation_results.created_at', '<', $end_date];
                    $criteria .= 'Between (' . $request->start_date . ') And (' . $request->end_date . ')';
                }

                // check if inpatient/outpatient is selected
                if (!is_null($request->in_out_patient)) {
                    if ($request->in_out_patient == 0) {
                        $criteria .= 'Inpatient and Outpatient ';
                    } elseif ($request->in_out_patient == 1) {
                        $filters[] = ['investigation_results.inpatient', '=', 1];
                        $criteria .= 'Inpatient ';
                    } elseif ($request->in_out_patient == 2) {
                        $filters[] = ['investigation_results.inpatient', '=', 0];
                        $criteria .= 'Outpatient ';
                    }
                }

                if(!empty($request->patient_category)) {
                    $filters[] = ['patients.category_id', $request->patient_category];
                    $criteria .= ' Patient Category(' . ($patient_categories_names[$request->patient_category] ?? 'N/A') . ') ';
                } else {
                    $criteria .= ' Patient Category(All) ';
                }

                // collect patient information and along with consultations
                $investigations_results = DB::table('investigation_results')
                    ->where($filters)
                    ->join('patients', 'investigation_results.patient_id', '=', 'patients.id')
                    ->select('investigation_results.investigation_id', 'investigation_results.value', 'investigation_results.id')
                    ->get();

                if ($investigations_results->count() > 0) {

                    // store the search in the session variable
                    session()->put('search_query_filters', $filters);

                    // check if a specific lab test has been searched for and go straight to details
                    if ($request->lab_test != 0) {
                        return redirect('/streamline_reports/investigations_drill_down/' . $request->lab_test);
                    }

                    $super_categories_names = DB::table('investigation_super_categories')->pluck('name', 'id')->toArray();
                    $categories_names = DB::table('investigation_categories')->pluck('name', 'id')->toArray();
                    $investigation_names = DB::table('investigations')->pluck('name', 'id')->toArray();

                    $investigations = [];
                    $investigation_categories = [];
                    $investigation_super_categories = [];
                    $inv_category_counts = [];

                    foreach ($investigations_results as $value) {
                        // split into an array for labs
                        $temp_investigations_array = explode(",", $value->investigation_id);

                        // loop through the temporary arrays
                        for ($i = 0; $i < count($temp_investigations_array); $i++) {
                            $inv_id = $temp_investigations_array[$i];
                            $investigation_category = get_name($inv_id, 'id', 'category', 'investigations');

                            if (!is_integer($investigation_category)) {
                                continue;
                            }

                            // check if we have already recorded the diagnosis
                            if (isset($investigations[$investigation_category][$inv_id])) {
                                $investigations[$investigation_category][$inv_id] = $investigations[$investigation_category][$inv_id] + 1;
                            } else {
                                // investigation is not recorded so insert it
                                $investigations[$investigation_category][$inv_id] = 1;
                                $inv_category_counts[$investigation_category] = 1 + ($inv_category_counts[$investigation_category] ?? 0);
                            }

                            // add the investigation category here
                            if (isset($investigation_categories[$investigation_category])) {
                                $investigation_categories[$investigation_category] = $investigation_categories[$investigation_category] + 1;
                            } else {
                                $investigation_categories[$investigation_category] = 1;
                            }
                        }
                    }

                    // go through the categories and elevate to the super categories
                    foreach ($investigation_categories as $key => $category_count) {
                        $super_id = get_name($key, 'id', 'super_category_id', 'investigation_categories');

                        if (!is_integer($super_id)) {
                            continue;
                        }

                        if (isset($investigation_super_categories[$super_id])) {
                            $investigation_super_categories[$super_id]["count"] = $investigation_super_categories[$super_id]["count"] + $category_count;
                            $investigation_super_categories[$super_id]["inv_count"] = $investigation_super_categories[$super_id]["inv_count"] + $inv_category_counts[$key];
                        } else {
                            $investigation_super_categories[$super_id]["count"] = $category_count;
                            $investigation_super_categories[$super_id]["inv_count"] = $inv_category_counts[$key];
                        }

                        $investigation_super_categories[$super_id]["sub_categories"][$key]["count"] = $category_count;
                    }

                    // check if labs have returned results
                    if (count($investigations) < 1) {
                        flash("No results found for the labs search")->error();
                        return redirect("streamline_reports");
                    }

                    return view('reports::hmis_reports.investigation_report', compact('criteria', 'investigations', 'investigation_super_categories', 'super_categories_names', 'categories_names', 'investigation_names'));
                } else {
                    flash("No results found for the labs search")->error();
                    return redirect("streamline_reports");
                }
            }
        } elseif ($request->report_category == 4) {
            if (!Auth::user()->can('streamline-reports-patient-visits')) {
                flash('You do not have access to this report.')->error();
                return redirect('/streamline_reports');
            }

            // check if inpatient/outpatient is selected
            $is_outpatient_queried = true;
            $is_inpatient_queried = true;

            if (!is_null($request->in_out_patient) && $request->in_out_patient != 0) {
                if ($request->in_out_patient == 1) {
                    $criteria .= 'Inpatient ';
                    $is_outpatient_queried = false;
                } elseif ($request->in_out_patient == 2) {
                    $criteria .= 'Outpatient ';
                    $is_inpatient_queried = false;
                }
            } else {
                $criteria .= 'Inpatient and Outpatient ';
            }

            $age_groups = AgeGroup::all();
            $age_group_data = [];

            // check if dates are selected
            if (!is_null($request->start_date) && !is_null($request->end_date)) {
                $start_date = Carbon::parse($request->start_date)->startOfDay()->toDateTimeString();
                $end_date = Carbon::parse($request->end_date)->endOfDay()->toDateTimeString();
                $number_of_days = Carbon::parse($request->start_date)->startOfDay()->diffInDays(Carbon::parse($request->end_date)->endOfDay());
                $number_of_days = $number_of_days == 0 ? 1 : $number_of_days;
            } else {
                $start_date = Carbon::today()->startOfDay()->toDateTimeString();
                $end_date = Carbon::today()->endOfDay()->toDateTimeString();
                $number_of_days = 1;
            }

            $filters[] = ['patient_episodes.created_at', '>', $start_date];
            $filters[] = ['patient_episodes.created_at', '<', $end_date];

            $new_patients = 0;
            $returning_patients = 0;
            $total_admissions = 0;
            $total_discharges = 0;
            $total_ward_deaths = 0;
            $total_opd_deaths = 0;
            $total_opd_deaths_arr = [];
            $total_re_attendance = 0;
            $unique_patients = [];
            $total_patients_in_clinics = 0;
            $number_of_episodes = 0;
            $triaged_patients = 0;
            $wards = DB::table('wards')->pluck('name', 'id')->toArray();

            $patient_clinics[0] = [
                "name" => "No Clinic Assigned",
                "total_patients" => 0,
                "male" => 0,
                "new" => 0,
                "patient_ids" => [],
                "total_episodes" => 0,
            ];

            if(!empty($request->patient_category)) {
                $filters[] = ['patients.category_id', $request->patient_category];
                $criteria .= ' Patient Category(' . ($patient_categories_names[$request->patient_category] ?? 'N/A') . ') ';
            } else {
                $criteria .= ' Patient Category(All) ';
            }

            $patient_wards = [];
            $patient_categories = [];

            // get ordered procedures
            $ipd_procedures = DB::table('ward_procedures')->whereNull('deleted_at')->whereBetween('created_at', [$start_date, $end_date])->count();
            $opd_procedures = DB::table('ordered_procedures')->whereNull('deleted_at')->whereBetween('created_at', [$start_date, $end_date])->count();

            // collect patient information and along with consultations
            $episodes = DB::table('patient_episodes')
                ->where($filters)
                ->whereNull('patient_episodes.deleted_at')
                ->join('patients', 'patient_episodes.patient_id', '=', 'patients.id')
                ->select('patient_episodes.patient_id', 'patient_episodes.id', 'patient_episodes.consultation_id', 'patient_episodes.clinic_id', 'patient_episodes.triage_id')
                ->get();

            if (count($episodes) > 0) {
                $total_patients = $total_female_patients = $total_male_patients = 0;
                $total_patients_arr = $total_female_patients_arr = $total_male_patients_arr = [];

                foreach ($episodes as $episode) {
                    $number_of_episodes++;
                    $is_patient_admitted = false;
                    $patient = DB::table('patients')->find($episode->patient_id);

                    // if a patient does not have DOB or gender, exclude them
                    if (!$patient || is_null($patient->date_of_birth) || ($patient->gender != 1 && $patient->gender != 2)) {
                        continue;
                    }

                    // check if patient is triaged
                    if (!is_null($episode->triage_id)) {
                        $triaged_patients++;
                    }

                    if (!is_null($request->in_out_patient)) {
                        $inpatient_record = DB::table('inpatient_info')->where('episode_id', $episode->id)->first();

                        if (($request->in_out_patient == 1 && !$inpatient_record) || ($request->in_out_patient == 2 && $inpatient_record)) {
                            // (episode has no inpatient record and we want inpatients) or (episode has an inpatient record and we want outpatients)
                            continue;
                        }

                        if ($inpatient_record) {
                            $is_patient_admitted = true;

                            if (isset($patient_wards[$inpatient_record->ward_id])) {
                                $patient_wards[$inpatient_record->ward_id]["total_episodes"] = $patient_wards[$inpatient_record->ward_id]["total_episodes"] + 1;
                                $patient_wards[$inpatient_record->ward_id]["discharged"] = $inpatient_record->discharged == 1 ? $patient_wards[$inpatient_record->ward_id]["discharged"] + 1 : $patient_wards[$inpatient_record->ward_id]["discharged"];
                                $patient_wards[$inpatient_record->ward_id]["deaths"] = $inpatient_record->outcome_id == 5 ? $patient_wards[$inpatient_record->ward_id]["deaths"] + 1 : $patient_wards[$inpatient_record->ward_id]["deaths"];

                                if (!isset($patient_wards[$inpatient_record->ward_id]["patient_ids"][$patient->id])) {
                                    $total_admissions++;
                                    $patient_wards[$inpatient_record->ward_id]["total_patients"] = $patient_wards[$inpatient_record->ward_id]["total_patients"] + 1;
                                    $patient_wards[$inpatient_record->ward_id]["male"] = $patient->gender == 1 ? $patient_wards[$inpatient_record->ward_id]["male"] + 1 : $patient_wards[$inpatient_record->ward_id]["male"];
                                    $patient_wards[$inpatient_record->ward_id]["female"] = $patient->gender == 2 ? $patient_wards[$inpatient_record->ward_id]["female"] + 1 : $patient_wards[$inpatient_record->ward_id]["female"];
                                    $patient_wards[$inpatient_record->ward_id]["new"] = is_patient_new_in_ward($episode->patient_id, $inpatient_record->ward_id, $episode->id) ? $patient_wards[$inpatient_record->ward_id]["new"] + 1 : $patient_wards[$inpatient_record->ward_id]["new"];
                                    $patient_wards[$inpatient_record->ward_id]["patient_ids"][$patient->id] = $patient->id;

                                    if ($patient->gender == 1) {
                                        $patient_wards[$inpatient_record->ward_id]["male_arr"][] = $patient->id;
                                    } else {
                                        $patient_wards[$inpatient_record->ward_id]["female_arr"][] = $patient->id;
                                    }

                                    if ($inpatient_record->discharged == 1) {
                                        $patient_wards[$inpatient_record->ward_id]["discharged_arr"][] = $patient->id;
                                        $total_discharges++;
                                    }

                                    if ($inpatient_record->outcome_id == 5) {
                                        $patient_wards[$inpatient_record->ward_id]["deaths_arr"][] = $patient->id;
                                        $total_ward_deaths++;
                                    }
                                }
                            } else {
                                $total_admissions++;
                                $patient_wards[$inpatient_record->ward_id]["name"] = $wards[$inpatient_record->ward_id] ?? 'N/A';
                                $patient_wards[$inpatient_record->ward_id]["total_patients"] = 1;
                                $patient_wards[$inpatient_record->ward_id]["total_episodes"] = 1;
                                $patient_wards[$inpatient_record->ward_id]["male_arr"] = [];
                                $patient_wards[$inpatient_record->ward_id]["female_arr"] = [];
                                $patient_wards[$inpatient_record->ward_id]["discharged_arr"] = [];
                                $patient_wards[$inpatient_record->ward_id]["deaths_arr"] = [];

                                if ($patient->gender == 1) {
                                    $patient_wards[$inpatient_record->ward_id]["male_arr"][] = $patient->id;
                                } else {
                                    $patient_wards[$inpatient_record->ward_id]["female_arr"][] = $patient->id;
                                }

                                if ($inpatient_record->discharged == 1) {
                                    $patient_wards[$inpatient_record->ward_id]["discharged_arr"][] = $patient->id;
                                    $total_discharges++;
                                }

                                if ($inpatient_record->outcome_id == 5) {
                                    $patient_wards[$inpatient_record->ward_id]["deaths_arr"][] = $patient->id;
                                    $total_ward_deaths++;
                                }

                                $patient_wards[$inpatient_record->ward_id]["male"] = $patient->gender == 1 ? 1 : 0;
                                $patient_wards[$inpatient_record->ward_id]["female"] = $patient->gender == 2 ? 1 : 0;
                                $patient_wards[$inpatient_record->ward_id]["new"] = is_patient_new_in_ward($episode->patient_id, $inpatient_record->ward_id, $episode->id) ? 1 : 0;
                                $patient_wards[$inpatient_record->ward_id]["discharged"] = $inpatient_record->discharged == 1 ? 1 : 0;
                                $patient_wards[$inpatient_record->ward_id]["deaths"] = $inpatient_record->outcome_id == 5 ? 1 : 0;
                                $patient_wards[$inpatient_record->ward_id]["patient_ids"] = [$patient->id];
                                $patient_wards[$inpatient_record->ward_id]["admitted_as_of"] = 0;
                                $patient_wards[$inpatient_record->ward_id]["admitted_as_of_arr"] = [];
                            }
                        } elseif (get_name($episode->consultation_id, 'id', 'outcome_id', 'consultations') == 5) {
                            $total_opd_deaths++;
                            $total_opd_deaths_arr[] = $episode->patient_id;
                        }
                    }

                    // create data for the clinics part of the report
                    if ($episode->clinic_id) {
                        if (isset($patient_clinics[$episode->clinic_id])) {
                            if (!in_array($patient->id, $patient_clinics[$episode->clinic_id]["patient_ids"])) {
                                $patient_clinics[$episode->clinic_id]["total_patients"] = $patient_clinics[$episode->clinic_id]["total_patients"] + 1;
                                $patient_clinics[$episode->clinic_id]["male"] = $patient->gender == 1 ? $patient_clinics[$episode->clinic_id]["male"] + 1 : $patient_clinics[$episode->clinic_id]["male"];
                                $patient_clinics[$episode->clinic_id]["new"] = is_patient_new_in_clinic($episode->patient_id, $episode->clinic_id, $episode->id) ? $patient_clinics[$episode->clinic_id]["new"] + 1 : $patient_clinics[$episode->clinic_id]["new"];
                                $patient_clinics[$episode->clinic_id]["patient_ids"][] = $patient->id;
                                $total_patients_in_clinics++;
                            }

                            $patient_clinics[$episode->clinic_id]["total_episodes"] = $patient_clinics[$episode->clinic_id]["total_episodes"] + 1;
                        } else {
                            $patient_clinics[$episode->clinic_id] = [
                                "name" => get_name($episode->clinic_id, 'id', 'name', 'clinics'),
                                "total_patients" => 1,
                                "male" => $patient->gender == 1 ? 1 : 0,
                                "new" => is_patient_new_in_clinic($episode->patient_id, $episode->clinic_id, $episode->id) ? 1 : 0,
                                "patient_ids" => [$patient->id],
                                "total_episodes" => 1,
                            ];
                            $total_patients_in_clinics++;
                        }
                    } else {
                        if (!isset($patient_clinics[0]["patient_ids"][$patient->id])) {
                            $patient_clinics[0]["total_patients"] = $patient_clinics[0]["total_patients"] + 1;
                            $patient_clinics[0]["male"] = $patient->gender == 1 ? $patient_clinics[0]["male"] + 1 : $patient_clinics[0]["male"];
                            $patient_clinics[0]["new"] = $patient_clinics[0]["new"] + 1;
                            $patient_clinics[0]["patient_ids"][$patient->id] = $patient->id;
                            $total_patients_in_clinics++;
                        }

                        $patient_clinics[0]["total_episodes"] = $patient_clinics[0]["total_episodes"] + 1;
                    }

                    // prepare report by patient category
                    if (isset($patient_categories[$patient->category_id])) {
                        if (!isset($patient_categories[$patient->category_id]["patient_ids"][$patient->id])) {
                            $patient_categories[$patient->category_id]["total_patients"] = $patient_categories[$patient->category_id]["total_patients"] + 1;
                            $patient_categories[$patient->category_id]["male"] = $patient->gender == 1 ? $patient_categories[$patient->category_id]["male"] + 1 : $patient_categories[$patient->category_id]["male"];
                            $patient_categories[$patient->category_id]["new"] = is_patient_new($episode->patient_id, $episode->id) ? $patient_categories[$patient->category_id]["new"] + 1 : $patient_categories[$patient->category_id]["new"];
                            $patient_categories[$patient->category_id]["patient_ids"][$patient->id] = $patient->id;
                        }

                        $patient_categories[$patient->category_id]["admissions"] = $is_patient_admitted ? $patient_categories[$patient->category_id]["admissions"] + 1 : $patient_categories[$patient->category_id]["admissions"];
                        $patient_categories[$patient->category_id]["total_episodes"] = $patient_categories[$patient->category_id]["total_episodes"] + 1;
                    } else {
                        $patient_categories[$patient->category_id] = [
                            "name" => $patient_categories_names[$patient->category_id] ?? 'N/A',
                            "total_patients" => 1,
                            "male" => $patient->gender == 1 ? 1 : 0,
                            "new" => is_patient_new($episode->patient_id, $episode->id) ? 1 : 0,
                            "patient_ids" => [$patient->id],
                            "total_episodes" => 1,
                            "admissions" => $is_patient_admitted ? 1 : 0,
                        ];
                    }

                    if (!isset($unique_patients[$episode->patient_id])) {
                        // check if this is a new attendance
                        $current_diagnosis = get_name($episode->consultation_id, 'id', 'primary_diagnosis', 'consultations');
                        if ($current_diagnosis != 'N/A') {
                            $current_episode_date = get_name($episode->consultation_id, 'id', 'created_at', 'consultations');
                            $start_of_day = Carbon::parse($current_episode_date)->startOfDay();
                            $end_of_day = Carbon::parse($current_episode_date)->endOfDay();

                            $past_diagnoses_count = DB::table('consultations')
                                ->where('patient_id', $episode->patient_id)
                                ->where('primary_diagnosis', $current_diagnosis)
                                ->whereNotBetween('created_at', [$start_of_day, $end_of_day])
                                ->count();

                            if ($past_diagnoses_count > 1) {
                                $total_re_attendance++;
                            }
                        }

                        // check if this is a new patient or returning by number of episodes
                        if (is_patient_new($episode->patient_id, $episode->id)) {
                            $new_patients++;
                        } else {
                            $returning_patients++;
                        }

                        if ($patient->date_of_birth != "") {
                            $dob = Carbon::parse($patient->date_of_birth);
                            $age_diff_days = $dob->diffInDays(Carbon::now());
                        } else {
                            $age_diff_days = 0;
                        }

                        $unique_patients[$episode->patient_id] = $episode->patient_id;

                        foreach ($age_groups as $age_group_record) {
                            // turn into days
                            if ($age_group_record->age_type == 3) {
                                // days
                                $first_day = $age_group_record->from_age;
                                $last_day = $age_group_record->to_age;
                            } elseif ($age_group_record->age_type == 2) {
                                // months
                                $first_day = $age_group_record->from_age * 30;
                                $last_day = $age_group_record->to_age * 30;
                            } else {
                                // years
                                $first_day = $age_group_record->from_age * 365;
                                $last_day = $age_group_record->to_age * 365;
                            }

                            if (between($age_diff_days, $first_day, $last_day)) {
                                $age_group_data[$age_group_record->id]['name'] = $age_group_record->name;

                                // patient fits this age group
                                if ($patient->gender == 1) {
                                    $age_group_data[$age_group_record->id]['male'][] = $episode->patient_id;
                                    $total_male_patients++;
                                    $total_male_patients_arr[] = $episode->patient_id;
                                } else {
                                    $age_group_data[$age_group_record->id]['female'][] = $episode->patient_id;
                                    $total_female_patients++;
                                    $total_female_patients_arr[] = $episode->patient_id;
                                }

                                $total_patients++;
                                $total_patients_arr[] = $episode->patient_id;
                                break;
                            }
                        }
                    }
                }

                // sort the clinics according to those with the most patients
                usort($patient_clinics, function ($a, $b) {
                    return $b['total_patients'] <=> $a['total_patients'];
                });

                // sort the wards according to those with the most patients
                usort($patient_wards, function ($a, $b) {
                    return $b['total_patients'] <=> $a['total_patients'];
                });

                // sort the wards according to those with the most patients
                usort($patient_categories, function ($a, $b) {
                    return $b['total_patients'] <=> $a['total_patients'];
                });

                $all_discharges = DB::table('inpatient_info')
                    ->whereBetween('discharged_on', [$start_date,$end_date])
                    ->count();

                $still_admitted = 0;
                $still_admitted_arr = [];

                $still_admitted_patients = DB::table('inpatient_info')
                    ->where('discharged_on', '>', $end_date)
                    ->OrWhereNull('discharged_on')
                    ->where('admitted_on', '<', $end_date)
                    ->get(['ward_id', 'patient_id']);

                foreach ($still_admitted_patients as $still_admitted_patient) {
                    $still_admitted++;
                    $still_admitted_arr[] = $still_admitted_patient->patient_id;
                    if (isset($patient_wards[$still_admitted_patient->ward_id])) {
                        $patient_wards[$still_admitted_patient->ward_id]['admitted_as_of'] += 1;
                        $patient_wards[$still_admitted_patient->ward_id]['admitted_as_of_arr'][] = $still_admitted_patient->patient_id;
                    }
                }

                return view('reports::hmis_reports.patient_visits_report', compact('criteria', 'total_male_patients', 'new_patients', 'returning_patients', 'total_admissions', 'total_discharges', 'all_discharges', 'still_admitted', 'total_re_attendance', 'is_outpatient_queried', 'number_of_episodes',
                    'start_date', 'end_date', 'total_patients', 'total_female_patients', 'is_inpatient_queried', 'total_ward_deaths', 'total_opd_deaths', 'patient_clinics', 'patient_wards', 'total_patients_in_clinics', 'patient_categories', 'number_of_days', 'triaged_patients', 'ipd_procedures', 'opd_procedures',
                    'total_male_patients_arr', 'total_female_patients_arr', 'total_patients_arr', 'still_admitted_arr', 'total_opd_deaths_arr', 'age_group_data'));
            } else {
                flash("No results found for the patients search")->error();
                return redirect("streamline_reports");
            }
        } elseif ($request->report_category == 5) {
            if (!Auth::user()->can('streamline-reports-symptoms')) {
                flash('You do not have access to this report.')->error();
                return redirect('/streamline_reports');
            }

            // search for symptoms
            if (is_null($request->symptoms)) {
                flash("Please select a symptom")->error();
                return redirect("streamline_reports");
            } else {
                // check if dates are selected
                if (!is_null($request->start_date) && !is_null($request->end_date)) {
                    $start_date = Carbon::parse($request->start_date)->startOfDay()->toDateTimeString();
                    $end_date = Carbon::parse($request->end_date)->endOfDay()->toDateTimeString();

                    array_push($filters, ['triage.created_at', '>', $start_date]);
                    array_push($filters, ['triage.created_at', '<', $end_date]);
                    $criteria .= 'Between (' . $request->start_date . ') And (' . $request->end_date . ')';
                }

                // check if inpatient/outpatient is selected
                if (!is_null($request->in_out_patient)) {
                    if ($request->in_out_patient == 0) {
                        $criteria .= 'Inpatient and Outpatient ';
                    } elseif ($request->in_out_patient == 1) {
                        array_push($filters, ['consultations.ward_id', '<>', '']);
                        $criteria .= 'Inpatient ';
                    } elseif ($request->in_out_patient == 2) {
                        array_push($filters, ['consultations.ward_id', '=', '']);
                        $criteria .= 'Outpatient ';
                    }
                }

                if(!empty($request->patient_category)) {
                    $filters[] = ['patients.category_id', $request->patient_category];
                    $criteria .= ' Patient Category(' . ($patient_categories_names[$request->patient_category] ?? 'N/A') . ') ';
                } else {
                    $criteria .= ' Patient Category(All) ';
                }

                // collect patient information and along with consultations
                $symptoms = DB::table('triage')
                    ->leftjoin('patients', 'triage.patient_id', '=', 'patients.id')
                    ->leftjoin('consultations', 'triage.episode_id', '=', 'consultations.episode_id')
                    ->where($filters)
                    ->select('triage.id', 'triage.patient_id','triage.episode_id', 'triage.symptoms','consultations.symptoms as symptoms2','triage.created_at')
                    ->get();
                if (!empty($symptoms)) {

                    // store the search in the session variable
                    session()->put('search_query_filters', $filters);

                    // check if a specific lab test has been searched for and go straight to details
                    if ($request->symptoms != 0) {
                        return redirect('/streamline_reports/symptoms_drill_down/' . $request->symptoms);
                    }

                    $symptoms_id_array = [];
                    $values_array = [];

                    foreach ($symptoms as $value) {
                        $symptoms1 = !(is_null($value->symptoms) || $value->symptoms == '') ? explode(",", $value->symptoms) : [];
                        $symptoms2 = !(is_null($value->symptoms2) || $value->symptoms2 == '') ? explode(",", $value->symptoms2) : [];
                        $temp_symptoms_array = array_merge($symptoms1, $symptoms2);

                        // loop through the temporary arrays
                        for ($i = 0; $i < count($temp_symptoms_array); $i++) {
                            // check if we have already recorded the diagnosis
                            if (in_array($temp_symptoms_array[$i], $symptoms_id_array)) {
                                // We've struck gold! Now find the key
                                $key = array_search($temp_symptoms_array[$i], $symptoms_id_array);

                                $values_array[$key] = $values_array[$key] + 1;
                            } else {
                                // diagnosis is not recorded so insert it
                                array_push($symptoms_id_array, $temp_symptoms_array[$i]);
                                array_push($values_array, 1);
                            }
                        }
                    }

                    // check if symptoms have returned results
                    if (count($symptoms_id_array) < 1) {
                        flash("No results found for the symptoms search")->error();
                        return redirect("streamline_reports");
                    }

                    return view('reports::hmis_reports.symptoms_report', compact('criteria', 'symptoms_id_array', 'values_array'));
                } else {
                    flash("No results found for the symptoms search")->error();
                    return redirect("streamline_reports");
                }
            }
        } elseif ($request->report_category == 6) {
            if (!Auth::user()->can('streamline-reports-doctors')) {
                flash('You do not have access to this report.')->error();
                return redirect('/streamline_reports');
            }

            if (!is_null($request->start_date) && !is_null($request->end_date)) {
                $start_date = Carbon::parse($request->start_date)->startOfDay()->toDateTimeString();
                $end_date = Carbon::parse($request->end_date)->endOfDay()->toDateTimeString();
            } else {
                $start_date = Carbon::today()->startOfDay()->toDateTimeString();
                $end_date = Carbon::today()->endOfDay()->toDateTimeString();
            }

            $criteria .= 'Between (' . $request->start_date . ') And (' . $request->end_date . ')';

            $consultations = DB::table('consultations')->whereNull('deleted_at')
                ->whereBetween('created_at', [$start_date, $end_date])
                ->where('completed', 1)
                ->get(['consultation_done_by', 'created_by', 'updated_by', 'ward_id', 'episode_id']);

            $eye_consultations = DB::table('eye_clinic_main_exam')->whereNull('deleted_at')
                ->whereBetween('created_at', [$start_date, $end_date])
                ->where('completed', 1)
                ->get(['consultation_done_by', 'created_by', 'updated_by', 'ward_id', 'episode_id']);

            $doctors = [];

            foreach ($consultations as $consultation) {
                if (!is_null($consultation->consultation_done_by)) {
                    $doctor_id = $consultation->consultation_done_by;
                } else {
                    $doctor_id = $consultation->created_by;
                }

                $treatment_numbers = DB::table('treatments')->where('episode_id', $consultation->episode_id)
                    ->where('created_by', $doctor_id)
                    ->whereNull('deleted_at')
                    ->count();

                $investigation_numbers = DB::table('ordered_investigations')->where('episode_id', $consultation->episode_id)
                    ->where('created_by', $doctor_id)
                    ->whereNull('deleted_at')
                    ->count();

                if (isset($doctors[$doctor_id])) {
                    $doctors[$doctor_id]["patients_seen"] = $doctors[$doctor_id]["patients_seen"] + 1;
                    $doctors[$doctor_id]["investigations"] = $doctors[$doctor_id]["investigations"] + $investigation_numbers;
                    $doctors[$doctor_id]["prescriptions"] = $doctors[$doctor_id]["prescriptions"] + $treatment_numbers;
                    $doctors[$doctor_id]["admissions"] = $doctors[$doctor_id]["admissions"] + (!is_null($consultation->ward_id) ? 1 : 0);
                } else {
                    $doctors[$doctor_id] = [
                        "id" => $doctor_id,
                        "name" => get_full_name($doctor_id, 'id', 'first_name', 'last_name', 'users'),
                        "patients_seen" => 1,
                        "investigations" => $investigation_numbers,
                        "prescriptions" => $treatment_numbers,
                        "admissions" => !is_null($consultation->ward_id) ? 1 : 0
                    ];
                }
            }

            foreach ($eye_consultations as $eye_consultation) {
                if (!is_null($eye_consultation->consultation_done_by)) {
                    $doctor_id = $eye_consultation->consultation_done_by;
                } else {
                    $doctor_id = $eye_consultation->created_by;
                }

                $treatment_numbers = DB::table('treatments')->where('episode_id', $eye_consultation->episode_id)
                    ->where('created_by', $doctor_id)
                    ->whereNull('deleted_at')
                    ->count();

                $investigation_numbers = DB::table('ordered_investigations')->where('episode_id', $eye_consultation->episode_id)
                    ->where('created_by', $doctor_id)
                    ->whereNull('deleted_at')
                    ->count();

                if (isset($doctors[$doctor_id])) {
                    $doctors[$doctor_id]["patients_seen"] = $doctors[$doctor_id]["patients_seen"] + 1;
                    $doctors[$doctor_id]["investigations"] = $doctors[$doctor_id]["investigations"] + $investigation_numbers;
                    $doctors[$doctor_id]["prescriptions"] = $doctors[$doctor_id]["prescriptions"] + $treatment_numbers;
                    $doctors[$doctor_id]["admissions"] = $doctors[$doctor_id]["admissions"] + (!is_null($eye_consultation->ward_id) ? 1 : 0);
                } else {
                    $doctors[$doctor_id] = [
                        "id" => $doctor_id,
                        "name" => get_full_name($doctor_id, 'id', 'first_name', 'last_name', 'users'),
                        "patients_seen" => 1,
                        "investigations" => $investigation_numbers,
                        "prescriptions" => $treatment_numbers,
                        "admissions" => !is_null($eye_consultation->ward_id) ? 1 : 0
                    ];
                }
            }

            if (count($doctors) > 0) {
                return view('reports::hmis_reports.doctors_performance_report', compact('criteria', 'doctors', 'start_date', 'end_date'));
            } else {
                flash("No results found for the doctors search")->error();
                return redirect("streamline_reports");
            }
        } else {
            flash("No results found for the search")->error();
            return redirect("streamline_reports");
        }
    }

    public function patient_visits_breakdown($title, $patients)
    {
        $patients = DB::table('patients')->whereIn('id', explode(",", $patients))->get();

        return view('reports::hmis_reports.patient_visits_breakdown', compact('patients', 'title'));
    }

    public function diagnosis_drill_down($diagnosis_id, $gender) {
        // get the filter session
        $filters = session()->get('search_query_filters');
        $eye_filters = session()->get('eye_search_query_filters');
        $custom_field = session()->get('custom_field');

        // clear the filter session
        session()->forget('search_query_filters');
        session()->forget('eye_search_query_filters');
        session()->forget('custom_field');

        // check whether it is null as some people will try to reload this page
        if (is_null($filters)) {
            // redirect them back to the main reports page
            return redirect("streamline_reports");
        }

        // add gender to filters
        if ($gender != 3) {
            $filters[] = ['patients.gender', '=', $gender];
            $eye_filters[] = ['patients.gender', '=', $gender];
        }

        // add diagnosis id to filters
        $filters[] = ['consultations.primary_diagnosis', '=', $diagnosis_id];

        $eye_diagnoses = DB::table('eye_clinic_main_exam as e')->join('patients', 'e.patient_id', '=', 'patients.id')
            ->where($eye_filters)
            ->whereRaw("FIND_IN_SET(". $diagnosis_id . ",right_eye_diagnosis) OR FIND_IN_SET(". $diagnosis_id . ",left_eye_diagnosis)")
            ->get(['patients.id', 'patients.gender', 'patients.number', 'patients.date_of_birth', 'right_eye_diagnosis', 'left_eye_diagnosis', 'e.created_at', 'next_of_kin', 'phone_of_next_of_kin', 'phone']);

        // collect patient information and along with consultations
        $diagnosis = DB::table('consultations')
            ->where($filters)
            ->join('patients', 'consultations.patient_id', '=', 'patients.id')
            ->select('patients.id', 'patients.number', 'patients.gender', 'patients.date_of_birth', 'consultations.primary_diagnosis', 'consultations.created_at', 'next_of_kin', 'phone_of_next_of_kin', 'phone')
            ->get();

        $patients = [];

        foreach ($diagnosis as $record) {
            $patients[] = [
                "patient_number" => $record->number, "patient_id" => $record->id,
                "patient_name" => insurance_flag($record->id), "gender" => ($record->gender == 1 ? __('hmis_reports.male') : __('hmis_reports.female')),
                "date_of_birth" => $record->date_of_birth, "created_at" => $record->created_at, 'next_of_kin'=>  $record->next_of_kin,
                'phone_of_next_of_kin'=> $record->phone_of_next_of_kin, 'phone'=> $record->phone
            ];
        }

        foreach ($eye_diagnoses as $record) {
            $patients[] = [
                "patient_number" => $record->number, "patient_id" => $record->id,
                "patient_name" => insurance_flag($record->id), "gender" => ($record->gender == 1 ? __('hmis_reports.male') : __('hmis_reports.female')),
                "date_of_birth" => get_patients_age($record->date_of_birth),
                "created_at" => streamline_date_time($record->created_at),
                'next_of_kin'=>  $record->next_of_kin, 'phone_of_next_of_kin'=> $record->phone_of_next_of_kin, 'phone'=> $record->phone
            ];
        }

        return view('reports::hmis_reports.diagnosis_drill_down', compact('patients', 'diagnosis_id', 'custom_field'));
    }

    public function treatments_drill_down($drug_id) {
        // get the filter session
        $filters = session()->get('search_query_filters');
        $custom_field = session()->get('custom_field');
        $drugs = DB::table('patient_dispensings')
            ->where($filters)
            ->whereRaw('FIND_IN_SET(' . $drug_id . ',patient_dispensings.drugs)')
            ->join('patients', 'patient_dispensings.patient_id', '=', 'patients.id')
            ->select('patients.id', 'patients.number', 'patient_dispensings.drugs', 'patient_dispensings.quantity_dispensed', 'patient_dispensings.created_at')
            ->get();

        $dispensed_array = [];
        $patient_number_array = [];
        $patient_id_array = [];
        $date_array = [];
        session()->forget('custom_field');
        foreach ($drugs as $drug) {
            // split into an array for drugs
            $temp_drugs_array = explode(",", $drug->drugs);
            $temp_dispensed_array = explode(",", $drug->quantity_dispensed);

            // loop through the temporary arrays
            for ($i = 0; $i < count($temp_dispensed_array); $i++) {

                // check if specific drug has been requested
                if ($drug_id == $temp_drugs_array[$i]) {
                    // diagnosis is not recorded so insert it
                    $dispensed_array[] = $temp_dispensed_array[$i];
                    $patient_number_array[] = $drug->number;
                    $patient_id_array[] = $drug->id;
                    $date_array[] = $drug->created_at;
                }
            }
        }

        return view('reports::hmis_reports.treatments_drill_down', compact('dispensed_array', 'patient_id_array', 'date_array', 'patient_number_array', 'drug_id', 'custom_field'));
    }

    public function investigations_drill_down($investigation_id) {
        // get the filter session
        $filters = session()->get('search_query_filters');
        $custom_field = session()->get('custom_field');
        $investigations = InvestigationResults::where($filters)
            ->whereRaw('FIND_IN_SET(' . $investigation_id . ',investigation_results.investigation_id)')
            ->leftjoin('patients', 'investigation_results.patient_id', '=', 'patients.id')
            ->get(['investigation_results.investigation_id', 'investigation_results.value', 'investigation_results.created_at', 'patients.number', 'patients.id']);

        $result_templates_array = $inv_temp = [];
        $patient_records = $transformed_inv_temp = [];
        session()->forget('custom_field');
        $result_templates = DB::table('investigation_result_templates')->whereNull('deleted_at')->where('investigation_id', $investigation_id)->get();

        foreach ($result_templates as $template) {
            $result_templates_array[$template->template]['counter'] = 0;
            $inv_temp[] = $template->template;
            $transformed_inv_temp[] = strtolower(str_replace(' ', '',  $template->template));
        }

        if(!empty($result_templates_array)) $result_templates_array['Other Results']['counter'] = 0;

        foreach ($investigations as $investigation) {
            // split into an array for investigations
            $temp_investigations_array = explode(",", $investigation->investigation_id);
            $temp_value_array = explode(",", $investigation->value);
            $id = array_search($investigation_id, $temp_investigations_array);
            $inv_result = $temp_value_array[$id];
            $temp_key = array_search(strtolower(str_replace(' ', '', $inv_result)), $transformed_inv_temp);
            if($temp_key !== false) $template_key = $inv_temp[$temp_key];

            $patient_records[] = [
                "patient_number" => $investigation->number,
                "patient_id" => $investigation->id,
                "created_at" => $investigation->created_at,
                "value" => $inv_result
            ];

            // check if the result template is set
            if ($temp_key !== false) {
                $result_templates_array[$template_key]['counter'] = $result_templates_array[$template_key]['counter'] + 1;
                $result_templates_array[$template_key]['patients'][] = [
                    "patient_number" => $investigation->number,
                    "patient_id" => $investigation->id,
                    "created_at" => $investigation->created_at,
                    "value" => $inv_result
                ];
            } else {
                if (isset($result_templates_array['Other Results']['counter'])) {
                    $result_templates_array['Other Results']['counter'] = $result_templates_array['Other Results']['counter'] + 1;
                    $result_templates_array['Other Results']['patients'][] = [
                        "patient_number" => $investigation->number,
                        "patient_id" => $investigation->id,
                        "created_at" => $investigation->created_at,
                        "value" => $inv_result
                    ];
                }
            }
        }

        return view('reports::hmis_reports.investigations_drill_down', compact('patient_records', 'investigation_id', 'result_templates_array', 'custom_field'));
    }

    public function symptoms_drill_down($symptom_id)
    {
        // get the filter session
        $filters = session()->get('search_query_filters');
        $custom_field = session()->get('custom_field');
        // clear the filter session
        session()->forget('search_query_filters');
        session()->forget('custom_field');
        if (is_null($filters)) {
            // redirect them back to the main reports page
            return redirect("streamline_reports");
        }

        $symptoms = DB::table('triage')
            ->leftjoin('patients', 'triage.patient_id', '=', 'patients.id')
            ->leftjoin('consultations', 'triage.episode_id', '=', 'consultations.episode_id')
            ->where(function($query) use ($symptom_id) {
                $query->whereRaw('FIND_IN_SET(' . $symptom_id . ', triage.symptoms)')
                    ->orWhereRaw('FIND_IN_SET(' . $symptom_id . ', consultations.symptoms)');
            })
            ->where($filters)
            ->get(['patients.gender', 'patients.number', 'patients.date_of_birth','triage.patient_id', 'triage.created_at','next_of_kin', 'phone_of_next_of_kin', 'phone']);

        return view('reports::hmis_reports.symptoms_drill_down', compact('symptoms', 'symptom_id','custom_field'));
    }

    /**
     * Body mass index report
     * */
    public function bmi_report(Request $request)
    {
        $hospital_information = HospitalInformation::find(1);
        if (isset($request->date_from) && isset($request->date_to)) {
            $attendance_from = Carbon::createFromFormat('d/m/Y', $request->date_from)->toDateString();
            $attendance_to = Carbon::createFromFormat('d/m/Y', $request->date_to)->toDateString();
            $search_button = $request->filterBtn;
        } else {
            $date = date_create(date("d-m-Y"));
            date_sub($date, date_interval_create_from_date_string("31 days"));
            $startDate = date_format($date, "d-m-Y");
            $attendance_from = date_format($date, "Y-m-d");
            $attendance_to = date("Y-m-d");
        }
        // $sql_query = "SELECT t.patient_id as patient_id, t.id, t.observations, d.date_of_birth, d.gender FROM triage t, patients d  WHERE (t.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}') AND t.patient_id = d.id AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) > 5 AND t.observations LIKE '%BMI=%' ORDER BY t.patient_id DESC";
        $triage_results = DB::table('triage')
            ->leftJoin('patients', 'triage.patient_id', '=', 'patients.id')
            ->select('patient_id', 'date_of_birth','gender','patient_id','observations','triage.id')
            ->whereBetween('triage.created_at', [$attendance_from, $attendance_to])
            ->where('observations', 'LIKE', '%BMI=%')->whereRaw('(YEAR(CURDATE()) - YEAR(date_of_birth)) >= 5')
            ->orderBy('patient_id', 'desc')
            ->get();
        // $triage_results1 = DB::select($sql_query);
        //                        5 - 10 years MALES ARRAY
        $severely_underweight_5_10yrs_male_array = array();
        $underweight_5_10yrs_male_array = array();
        $normal_5_10yrs_male_array = array();
        $overweight_5_10yrs_male_array = array();
        $obese_5_10yrs_male_array = array();
        //                        11 - 18 years MALES ARRAY
        $severely_underweight_11_18yrs_male_array = array();
        $underweight_11_18yrs_male_array = array();
        $normal_11_18yrs_male_array = array();
        $overweight_11_18yrs_male_array = array();
        $obese_11_18yrs_male_array = array();
        //                        > 18 years MALES ARRAY
        $severely_underweight_18yrs_male_array = array();
        $underweight_18yrs_male_array = array();
        $normal_18yrs_male_array = array();
        $overweight_18yrs_male_array = array();
        $obese_18yrs_male_array = array();

        //                        5 - 10 years FEMALES ARRAY
        $severely_underweight_5_10yrs_female_array = array();
        $underweight_5_10yrs_female_array = array();
        $normal_5_10yrs_female_array = array();
        $overweight_5_10yrs_female_array = array();
        $obese_5_10yrs_female_array = array();
        //                        11 - 18 years FEMALES ARRAY
        $severely_underweight_11_18yrs_female_array = array();
        $underweight_11_18yrs_female_array = array();
        $normal_11_18yrs_female_array = array();
        $overweight_11_18yrs_female_array = array();
        $obese_11_18yrs_female_array = array();
        //                        > 18 years FEMALES ARRAY
        $severely_underweight_18yrs_female_array = array();
        $underweight_18yrs_female_array = array();
        $normal_18yrs_female_array = array();
        $overweight_18yrs_female_array = array();
        $obese_18yrs_female_array = array();

        if (count($triage_results) > 0):
            foreach ($triage_results as $result) {
                $observation_explode = explode(",", $result->observations);
                $bmi_string = custom_search_array_like("BMI", $observation_explode); // returns 'BMI=27.1'
                $bmi = explode("=", $bmi_string)[1]; // returns the real BMI 27.1
                if (empty($bmi) || $bmi == "" || $bmi == " "):
                    continue;
                endif;
                $patient_id = $result->patient_id;
                $date_of_birth = new Carbon($result->date_of_birth);
                $age_years = $date_of_birth->diffInYears(Carbon::now());
                $age_years = (int)$age_years;
                $sex = $result->gender;

                if (between($age_years, 5, 10)):
                    if ($sex == 1 && $bmi < 16):
                        $severely_underweight_5_10yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 16, 18.5)):
                        $underweight_5_10yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 18.5, 25)):
                        $normal_5_10yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 25, 30)):
                        $overweight_5_10yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && $bmi > 30):
                        $obese_5_10yrs_male_array[] = $patient_id;
                    endif;

                    if ($sex == 2 && $bmi < 16):
                        $severely_underweight_5_10yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 16, 18.5)):
                        $underweight_5_10yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 18.5, 25)):
                        $normal_5_10yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 25, 30)):
                        $overweight_5_10yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && $bmi > 30):
                        $obese_5_10yrs_female_array[] = $patient_id;
                    endif;
                endif;

                if (between($age_years, 11, 18)):
                    if ($sex == 1 && $bmi < 16):
                        $severely_underweight_11_18yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 16, 18.5)):
                        $underweight_11_18yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 18.5, 25)):
                        $normal_11_18yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 25, 30)):
                        $overweight_11_18yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && $bmi > 30):
                        $obese_11_18yrs_male_array[] = $patient_id;
                    endif;

                    if ($sex == 2 && $bmi < 16):
                        $severely_underweight_11_18yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 16, 18.5)):
                        $underweight_11_18yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 18.5, 25)):
                        $normal_11_18yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 25, 30)):
                        $overweight_11_18yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && $bmi > 30):
                        $obese_11_18yrs_female_array[] = $patient_id;
                    endif;
                endif;


                if ($age_years >= 18):
                    if ($sex == 1 && $bmi < 16):
                        $severely_underweight_18yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 16, 18.5)):
                        $underweight_18yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 18.5, 25)):
                        $normal_18yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && between($bmi, 25, 30)):
                        $overweight_18yrs_male_array[] = $patient_id;
                    endif;
                    if ($sex == 1 && $bmi > 30):
                        $obese_18yrs_male_array[] = $patient_id;
                    endif;

                    if ($sex == 2 && $bmi < 16):
                        $severely_underweight_18yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 16, 18.5)):
                        $underweight_18yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 18.5, 25)):
                        $normal_18yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && between($bmi, 25, 30)):
                        $overweight_18yrs_female_array[] = $patient_id;
                    endif;
                    if ($sex == 2 && $bmi > 30):
                        $obese_18yrs_female_array[] = $patient_id;
                    endif;
                endif;
            }
        endif;

        $severely_underweight_5_10yrs_male_array = array();
        $underweight_5_10yrs_male_array = array();
        return view('reports::hmis_reports.bmi_report', compact('hospital_information', 'attendance_from', 'attendance_to', 'triage_results', 'obese_18yrs_female_array', 'overweight_18yrs_female_array', 'normal_18yrs_female_array', 'underweight_18yrs_female_array', 'severely_underweight_18yrs_female_array', 'obese_11_18yrs_female_array', 'overweight_11_18yrs_female_array', 'normal_11_18yrs_female_array', 'underweight_11_18yrs_female_array', 'severely_underweight_11_18yrs_female_array', 'obese_5_10yrs_female_array', 'overweight_5_10yrs_female_array', 'normal_5_10yrs_female_array', 'underweight_5_10yrs_female_array', 'severely_underweight_5_10yrs_female_array', 'obese_18yrs_male_array', 'overweight_18yrs_male_array', 'normal_18yrs_male_array', 'underweight_18yrs_male_array', 'severely_underweight_18yrs_male_array', 'obese_11_18yrs_male_array', 'overweight_11_18yrs_male_array', 'normal_11_18yrs_male_array', 'underweight_11_18yrs_male_array', 'severely_underweight_11_18yrs_male_array', 'obese_5_10yrs_male_array', 'overweight_5_10yrs_male_array', 'normal_5_10yrs_male_array', 'underweight_5_10yrs_male_array', 'severely_underweight_5_10yrs_male_array'));
    }

    /*
        * bmi report drill down
        */

    public function bmi_report_details(Request $request)
    {
        $from = $request->from;
        $to = $request->to;
        $age_range = $request->age_range;
        $search_criteria = $request->search_criteria;
        $patient_id_array = unserialize($request->patient_ids);
        $patient_query = "SELECT * FROM patients WHERE id IN (" . implode(',', $patient_id_array) . ")";
        $patients_results = DB::select($patient_query);
        return view('reports::hmis_reports.bmi_report_details', compact('from', 'to', 'age_range', 'search_criteria', 'patients_results'));
    }

    /*
        * Family plannig report
        */

    public function family_planning_report(Request $request)
    {
        $hospital_information = HospitalInformation::find(1);
        if (isset($request->date_from) && isset($request->date_to)) {
            $attendance_from = Carbon::createFromFormat('d/m/Y', $request->date_from)->toDateString();
            $attendance_to = Carbon::createFromFormat('d/m/Y', $request->date_to)->toDateString();
            $search_button = $request->filterBtn;
        } else {
            $date = date_create(date("d-m-Y"));
            date_sub($date, date_interval_create_from_date_string("30 days"));
            $startDate = date_format($date, "d-m-Y");
            $attendance_from = date_format($date, "Y-m-d");
            $attendance_to = date("Y-m-d");
        }
        $sql_query = "SELECT t.patient_id as patient_id, t.id as triage_id, t.fp_action as fp_action, d.gender FROM triage t, patients d WHERE (t.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}') AND (patient_id = d.id) AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) > 17";
        $triage_results = DB::select($sql_query);

        $counseling_male = [];
        $counseling_female = [];
        $fp_clinic_male = [];
        $fp_clinic_female = [];

        if (count($triage_results) > 0):
            foreach ($triage_results as $result) {
                $sex = $result->gender;
                $fp_action = $result->fp_action;

                if ($sex == 1 && $fp_action == 'Counseling') {
                    array_push($counseling_male, $result->patient_id);
                }
                if ($sex == 1 && $fp_action == 'Referral to FP clinic') {
                    array_push($fp_clinic_male, $result->patient_id);
                }
                if ($sex == 2 && $fp_action == 'Counseling') {
                    array_push($counseling_female, $result->patient_id);
                }
                if ($sex == 2 && $fp_action == 'Referral to FP clinic') {
                    array_push($fp_clinic_female, $result->patient_id);
                }
            }
        endif;

        return view('reports::hmis_reports.family_planning_report', compact('hospital_information', 'attendance_from', 'attendance_to', 'triage_results', 'counseling_male', 'counseling_female', 'fp_clinic_male', 'fp_clinic_female'));
    }

    /*
        * family planning report details
        */

    public function family_planning_report_details(Request $request)
    {
        $from = $request->from;
        $to = $request->to;
        $gender = $request->sex;
        $patient_id_array = unserialize($request->patient_ids);
        $patient_query = "SELECT * FROM patients WHERE id IN (" . implode(',', $patient_id_array) . ")";
        $patients_results = DB::select($patient_query);
        return view('reports::hmis_reports.family_planning_report_details', compact('from', 'to', 'patients_results', 'gender'));
    }

    /*
        * RDT report
        */

    public function rdt_report(Request $request)
    {
        $hospital_information = HospitalInformation::find(1);
        if (isset($request->date_from) && isset($request->date_to)) {
            $attendance_from = Carbon::createFromFormat('d/m/Y', $request->date_from)->toDateString();
            $attendance_to = Carbon::createFromFormat('d/m/Y', $request->date_to)->toDateString();
            $search_button = $request->filterBtn;
        } else {
            $date = date_create(date("d-m-Y"));
            date_sub($date, date_interval_create_from_date_string("6 days"));
            $startDate = date_format($date, "d-m-Y");
            $attendance_from = date_format($date, "Y-m-d");
            $attendance_to = date("Y-m-d");
        }
        /* rdt positive=1 negative=0 */
        $pos_male_child_sql = "SELECT c.patient_id, c.rdt, c.created_at, d.id, d.gender, d.date_of_birth from consultations c,patients d where c.patient_id = d.id AND c.rdt = 1 AND d.gender = 1 AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) <= 12 AND c.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}' order by c.created_at DESC";
        $pos_male_child = count(DB::select($pos_male_child_sql));

        $pos_male_adult_sql = "SELECT c.patient_id, c.rdt, c.created_at, d.id, d.gender, d.date_of_birth from consultations c,patients d where c.patient_id = d.id AND c.rdt = 1 AND d.gender = 1 AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) > 12 AND c.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}' order by c.created_at DESC";
        $pos_male_adult = count(DB::select($pos_male_adult_sql));

        $pos_female_child_sql = "SELECT c.patient_id, c.rdt, c.created_at, d.id, d.gender, d.date_of_birth from consultations c,patients d where c.patient_id = d.id AND c.rdt = 1 AND d.gender = 2 AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) <= 12 AND c.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}' order by c.created_at DESC";
        $pos_female_child = count(DB::select($pos_female_child_sql));

        $pos_female_adult_sql = "SELECT c.patient_id, c.rdt, c.created_at, d.id, d.gender, d.date_of_birth from consultations c,patients d where c.patient_id = d.id AND c.rdt = 1 AND d.gender = 2 AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) > 12 AND c.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}' order by c.created_at DESC";
        $pos_female_adult = count(DB::select($pos_female_adult_sql));

        $neg_male_child_sql = "SELECT c.patient_id, c.rdt, c.created_at, d.id, d.gender, d.date_of_birth from consultations c,patients d where c.patient_id = d.id AND c.rdt = 0 AND d.gender = 1 AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) <= 12 AND c.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}' order by c.created_at DESC";
        $neg_male_child = count(DB::select($neg_male_child_sql));

        $neg_male_adult_sql = "SELECT c.patient_id, c.rdt, c.created_at, d.id, d.gender, d.date_of_birth from consultations c,patients d where c.patient_id = d.id AND c.rdt = 0 AND d.gender = 1 AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) > 12 AND c.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}' order by c.created_at DESC";
        $neg_male_adult = count(DB::select($neg_male_adult_sql));

        $neg_female_child_sql = "SELECT c.patient_id, c.rdt, c.created_at, d.id, d.gender, d.date_of_birth from consultations c,patients d where c.patient_id = d.id AND c.rdt = 0 AND d.gender = 2 AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) <= 12 AND c.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}' order by c.created_at DESC";
        $neg_female_child = count(DB::select($neg_female_child_sql));

        $neg_female_adult_sql = "SELECT c.patient_id, c.rdt, c.created_at, d.id, d.gender, d.date_of_birth from consultations c,patients d where c.patient_id = d.id AND c.rdt = 0 AND d.gender = 2 AND (YEAR(CURDATE()) - YEAR(d.date_of_birth)) > 12 AND c.created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}' order by c.created_at DESC";
        $neg_female_adult = count(DB::select($neg_female_adult_sql));

        return view('reports::hmis_reports.rdt_report', compact('hospital_information', 'attendance_from', 'attendance_to', 'pos_male_child', 'pos_male_adult', 'pos_female_child', 'pos_female_adult', 'neg_male_child', 'neg_male_adult', 'neg_female_child', 'neg_female_adult'));
    }

    /* report to show carried out anaesthesias */

    public function anaesthesia_report(Request $request)
    {
        $procedures = DB::table('procedures')->orderBy("name", "asc")->pluck("name", "id")->prepend('- select -', '');
        $diagnoses = Diagnosis::where('available', 1)->orderBy("name", "asc")->pluck("name", "id")->prepend('- select -', '');
        $anaesthesia_types = DB::table('procedures')->where('category_id', 3)->orderBy("name", "asc")->pluck("name", "id")->prepend('- select -', '');
        $users = DB::table('users')->orderBy("first_name", "asc")->select("id")->get()->toArray();
        $doctors = [];
        $all_users = [];
        foreach ($users as $value) {
            $user = User::withTrashed()->find($value->id);
            if (!is_null($user)) {
                $all_users[$value->id] = get_full_name($value->id, 'id', 'first_name', 'last_name', 'users');
            }
        }
        $all_users = ['' => '- select -'] + $all_users;

        $search_by = $request->search_by;
        $reg_date = $request->reg_date;
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $filters = [];

        if (is_null($search_by)) {
            $anaesthesia_records = Anaesthesia::orderBy('created_at', 'desc')->paginate(500);
        } else {
            if ($search_by == 0) {
                // last 24 hours
                $last_day = Carbon::now()->subDay();
                array_push($filters, ['anaesthesias.created_at', '>', $last_day]);
            } elseif ($search_by == 1) {
                // custom date
                //$selected_date_filter = Carbon::createFromFormat('d/m/Y', $request->reg_date)->toDateString();
                //array_push($filters, ['anaesthesias.created_at', $selected_date_filter]);
                $start_date_search = Carbon::parse($reg_date)->startOfDay()->toDateTimeString();
                $end_date_search = Carbon::parse($reg_date)->endOfDay()->toDateTimeString();
                array_push($filters, ['anaesthesias.created_at', '>', $start_date_search]);
                array_push($filters, ['anaesthesias.created_at', '<', $end_date_search]);
            } elseif ($search_by == 2) {
                // custom date range
                $start_date_search = Carbon::createFromFormat('d/m/Y', $start_date)->startOfDay()->toDateTimeString();
                $end_date_search = Carbon::createFromFormat('d/m/Y', $end_date)->endOfDay()->toDateTimeString();
                array_push($filters, ['anaesthesias.created_at', '>', $start_date_search]);
                array_push($filters, ['anaesthesias.created_at', '<', $end_date_search]);
            }

            if ($request->has('surgical_operation') && !is_null($request->surgical_operation)) {
                array_push($filters, ['anaesthesias.procedure_id', $request->surgical_operation]);
            }

            if ($request->has('anaesthetist') && !is_null($request->anaesthetist)) {
                array_push($filters, ['anaesthesias.anaesthetist', $request->anaesthetist]);
            }

            if ($request->has('anaesthesia_type') && !is_null($request->anaesthesia_type)) {
                array_push($filters, ['anaesthesias.anaesthesia_type', $request->anaesthesia_type]);
            }

            if ($request->has('diagnosis') && !is_null($request->diagnosis)) {
                array_push($filters, ['consultations.primary_diagnosis', $request->diagnosis]);
                $anaesthesia_records = DB::table('anaesthesias')->join('consultations', 'consultations.episode_id', '=', 'anaesthesias.episode_id')->where($filters)->get();
            } else {
                $anaesthesia_records = Anaesthesia::where($filters)->orderBy('created_at', 'desc')->get();
            }
        }

        return view('reports::hmis_reports.anaesthesia_report', compact('anaesthesia_records', 'procedures', 'diagnoses', 'anaesthesia_types', 'all_users'));
    }

    /* report to show carried out surgeries */

    public function surgery_report(Request $request) {
        $procedures = DB::table('procedures')->orderBy("name", "asc")->pluck("name", "id")->prepend('- select -', '');
        $theatre_locations = DB::table('theatre_locations')->orderBy("name", "asc")->pluck("name", "id")->prepend('- select -', '');
        $diagnoses = DB::table('diagnoses')->where('available', 1)->whereNull('deleted_at')->orderBy('name')->pluck("name", "id")->prepend('- select -', '');
        $users = DB::table('users')->orderBy("first_name", "asc")->select("id", 'first_name', 'last_name')->get()->toArray();

        $all_users = [];
        foreach ($users as $value) {
            $all_users[$value->id] = $value->first_name . ' ' .  $value->last_name;
        }
        $all_users = ['' => '- select -'] + $all_users;

        $search_by = $request->search_by;
        $filters = [];

        if ($search_by == 1) {
            // custom date
            $selected_date_filter = Carbon::createFromFormat('d/m/Y', $request->reg_date)->toDateString();
            $filters[] = ['surgeries.date_surgery_completed', $selected_date_filter];
        } elseif ($search_by == 2) {
            // custom date range
            $start_date_search = Carbon::createFromFormat('d/m/Y', $request->start_date)->startOfDay()->toDateTimeString();
            $end_date_search = Carbon::createFromFormat('d/m/Y', $request->end_date)->endOfDay()->toDateTimeString();

            $filters[] = ['surgeries.date_surgery_completed', '>', $start_date_search];
            $filters[] = ['surgeries.date_surgery_completed', '<', $end_date_search];
        } else {
            $filters[] = ['surgeries.date_surgery_completed', today()->toDateString()];
        }

        if ($request->has('surgical_operation') && !is_null($request->surgical_operation)) {
            $filters[] = ['surgeries.procedure_id', $request->surgical_operation];
        }

        if ($request->has('surgeon') && !is_null($request->surgeon)) {
            $filters[] = ['surgeries.surgeon', $request->surgeon];
        }

        if ($request->has('assistant_surgeon') && !is_null($request->assistant_surgeon)) {
            $filters[] = ['surgeries.assistant_surgeon', $request->assistant_surgeon];
        }

        if ($request->has('diagnosis') && !is_null($request->diagnosis)) {
            $filters[] = ['consultations.primary_diagnosis', $request->diagnosis];
        }

        $surgery_records = Surgery::join('consultations', 'consultations.episode_id', '=', 'surgeries.episode_id')
            ->join('patients', 'patients.id', '=', 'surgeries.patient_id')
            ->where($filters)->get(['surgeries.*', 'consultations.primary_diagnosis', 'patients.number', 'patients.first_name', 'patients.last_name']);

        return view('reports::hmis_reports.surgery_report', compact('surgery_records', 'procedures', 'theatre_locations', 'diagnoses', 'all_users'));
    }

    public function ward_re_admittance_report(Request $request)
    {
        $wards = Ward::pluck('name', 'id')->toArray();
        $wards = [0 => '- select -'] + $wards;
        $missing_search_parameters = false;

        if (isset($request->start_date) && isset($request->start_date)) {
            $start_date = Carbon::parse($request->start_date)->startOfDay()->toDateTimeString();
            $end_date = Carbon::parse($request->end_date)->endOfDay()->toDateTimeString();
        } else {
            $missing_search_parameters = true;
        }

        if ($missing_search_parameters || $request->ward_id == 0) {
            flash("Please select ward and date range to generate report")->error();
            return view('reports::hmis_reports.ward_re_admittance_report', compact('wards'));
        }

        $ward = $request->ward_id;

        // get all patients discharged
        $discharged_patients = InpatientInfo::where('discharged', 1)->where('ward_id', $ward)->whereBetween('created_at', [$start_date, $end_date])->orderBy('created_at', 'asc')->get();

        // arrays to track the recurring patient ids
        $patient_ids_array = [];
        $discharge_date_array = [];

        // array to track the re-admittance information
        $readmittances = [];
        $count = 0;

        foreach ($discharged_patients as $record) {
            if (in_array($record->patient_id, $patient_ids_array)) {
                $readmittances[$count]['number'] = get_name($record->patient_id, 'id', 'number', 'patients');
                $readmittances[$count]['name'] = get_full_name($record->patient_id, 'id', 'first_name', 'last_name', 'patients');
                $dob = new Carbon(get_name($record->patient_id, 'id', 'date_of_birth', 'patients'));
                $readmittances[$count]['age'] = $dob->diffInYears(Carbon::now()) . " year(s)";
                $readmittances[$count]['gender'] = get_name($record->patient_id, 'id', 'gender', 'patients') == 1 ? 'Male' : 'Female';
                $readmittances[$count]['primary_diagnosis'] = get_name(get_name($record->episode_id, 'episode_id', 'primary_diagnosis', 'consultations'), 'id', 'name', 'diagnoses');
                $key = array_search($record->patient_id, $patient_ids_array);
                $readmittances[$count]['discharge_date'] = streamline_date($discharge_date_array[$key]);
                $readmittances[$count]['readmission_date'] = streamline_date($record->created_at);
                $readmittances[$count]['comments'] = ($record->comments) ? $record->comments : "N/A";
                $readmittances[$count]['patient_id'] = $record->patient_id;

                $count++;
            } else {
                $patient_ids_array[] = $record->patient_id;
                $discharge_date_array[] = $record->discharged_on;
            }
        }

        return view('reports::hmis_reports.ward_re_admittance_report', compact('start_date', 'end_date', 'wards', 'ward', 'readmittances'));
    }

    public function patients_lost_to_follow_up(Request $request)
    {
        if (isset($request->start_date)) {
            $start_date = Carbon::createFromFormat('d/m/Y', $request->start_date)->toDateString();
            $end_date = Carbon::createFromFormat('d/m/Y', $request->end_date)->toDateString();
        } else {
            $start_date = Carbon::now()->startOfDay()->toDateTimeString();
            $end_date = Carbon::now()->endOfDay()->toDateTimeString();
        }

        $lost_follow_ups = PatientAppointment::where('appointment_fulfilled', 0)
            ->whereBetween('appointment_date', [$start_date, $end_date])
            ->get();

        $total_follow_ups = PatientAppointment::whereBetween('appointment_date', [$start_date, $end_date])->count();
        $number_of_patients_lost = count($lost_follow_ups);
        $number_of_patients_followed_up = $total_follow_ups - $number_of_patients_lost;
        $drop_out_rate = $total_follow_ups > 0 ? ($number_of_patients_lost / $total_follow_ups) * 100 : 0;

        return view('reports::hmis_reports.patients_lost_to_follow_up', compact('lost_follow_ups', 'start_date', 'end_date', 'total_follow_ups', 'number_of_patients_followed_up', 'number_of_patients_lost', 'drop_out_rate'));
    }

    public function days_drugs_out_of_stock(Request $request) {
        if (isset($request->start_date)) {
            $start_date = Carbon::createFromFormat('d/m/Y', $request->start_date);
            $end_date = Carbon::createFromFormat('d/m/Y', $request->end_date);
        } else {
            $start_date = Carbon::now();
            $end_date = Carbon::now();
        }

        $selected_drugs = [];

        $stock_watcher_records = DB::table('stock_watcher')
            ->where('item_type', 1)
            ->get();

        foreach ($stock_watcher_records as $record) {
            $start_date_search = clone $start_date; // or you can use $start_date->copy()
            $end_date_search = clone $end_date;

            $details_arr = json_decode($record->details, true);

            // keep incrementing the start date till it reaches the next day after the end date and stop
            while (!$start_date_search->isAfter($end_date_search)) {
                if (isset($details_arr[$start_date_search->toDateString()]) && $details_arr[$start_date_search->toDateString()]["pharmacy_stock"] < 1 && $details_arr[$start_date_search->toDateString()]["store_stock"] < 1) {
                    $selected_drugs[$record->item_id] = 1 + ($selected_drugs[$record->item_id] ?? 0);
                }

                $start_date_search->addDay();
            }
        }

        return view('reports::hmis_reports.days_drugs_out_of_stock', compact('selected_drugs', 'start_date', 'end_date'));
    }

    public function list_hmis_reports()
    {
        return view('reports::hmis_reports.list-hmis-reports');
    }

    public function print_patient_visits_report(Request $request)
    {
        $hospitalInfo = DB::table('hospital_information')->find(1);

        $data = [
            'hospitalInfo' => $hospitalInfo,
            'criteria' => $request->criteria,
            'returning_patients' => $request->returning_patients,
            'new_patients' => $request->new_patients,
            'total_male_patients' => $request->total_male_patients,
            'total_re_attendance' => $request->total_re_attendance,
            'total_discharges' => $request->total_discharges,
            'all_discharges' => $request->all_discharges,
            'still_admitted' => $request->still_admitted,
            'total_admissions' => $request->total_admissions,
            'is_outpatient_queried' => $request->is_outpatient_queried,
            'end_date' => $request->end_date,
            'total_patients' => $request->total_patients,
            'total_female_patients' => $request->total_female_patients,
            'patient_wards' => unserialize($request->patient_wards),
            'age_group_data' => unserialize($request->age_group_data),
            'start_date' => $request->start_date,
            'total_ward_deaths' => $request->total_ward_deaths,
            'total_opd_deaths' => $request->total_opd_deaths,
            'patient_clinics' => unserialize($request->patient_clinics),
            'is_inpatient_queried' => $request->is_inpatient_queried,
            'number_of_episodes' => $request->number_of_episodes,
            'total_patients_in_clinics' => $request->total_patients_in_clinics,
            'patient_categories' => unserialize($request->patient_categories),
            'number_of_days' => $request->number_of_days,
        ];

        $pdf = SnappyPDF::loadView('reports::hmis_reports/patient_visits_report_print', $data)
            ->setOrientation('portrait')
            ->setOption('margin-bottom', 7)
            ->setOption('margin-top', 5)
            ->setOption('footer-html', '<i>Stre@mline</i>');

        return $pdf->inline('Patient Visits Report' . date(" d-m-y h:ia") . '.pdf');
    }

    public function tb_screening_report(Request $request)
    {
        $triage_filters = $consultation_filter = $inpatient_filter = [];

        $search_by = $request->search_by;
        $custom_date = $request->custom_date;
        $start_date = $request->start_date;
        $end_date = $request->end_date;

        if ($search_by == 0) {
            // last 24 hours
            $last_day = Carbon::now()->subDay();

            array_push($triage_filters, ['triage.created_at', '>', $last_day]);
            array_push($consultation_filter, ['consultations.created_at', '>', $last_day]);
            array_push($inpatient_filter, ['inpatient_info.created_at', '>', $last_day]);
            $date_search = "Last 24 hours";
        } elseif ($search_by == 1) {
            // custom date
            $start_date_search = Carbon::parse($custom_date)->startOfDay()->toDateTimeString();
            ;
            $end_date_search = Carbon::parse($custom_date)->endOfDay()->toDateTimeString();
            ;

            array_push($triage_filters, ['triage.created_at', '>', $start_date_search]);
            array_push($triage_filters, ['triage.created_at', '<', $end_date_search]);
            array_push($consultation_filter, ['consultations.created_at', '>', $start_date_search]);
            array_push($consultation_filter, ['consultations.created_at', '<', $end_date_search]);
            array_push($inpatient_filter, ['inpatient_info.created_at', '>', $start_date_search]);
            array_push($inpatient_filter, ['inpatient_info.created_at', '<', $end_date_search]);
            $date_search = streamline_date($start_date_search);
        } elseif ($search_by == 2) {
            // custom date range
            $start_date_search = Carbon::parse($start_date)->startOfDay()->toDateTimeString();
            ;
            $end_date_search = Carbon::parse($end_date)->endOfDay()->toDateTimeString();
            ;

            array_push($triage_filters, ['triage.created_at', '>', $start_date_search]);
            array_push($triage_filters, ['triage.created_at', '<', $end_date_search]);
            array_push($consultation_filter, ['consultations.created_at', '>', $start_date_search]);
            array_push($consultation_filter, ['consultations.created_at', '<', $end_date_search]);
            array_push($inpatient_filter, ['inpatient_info.created_at', '>', $start_date_search]);
            array_push($inpatient_filter, ['inpatient_info.created_at', '<', $end_date_search]);
            $date_search = streamline_date($start_date_search) . " to " . streamline_date($end_date_search);
        } elseif ($search_by == 4) {
            // Today
            $today = Carbon::today()->toDateTimeString();

            array_push($triage_filters, ['triage.created_at', '>=', $today]);
            array_push($consultation_filter, ['consultations.created_at', '>=', $today]);
            array_push($inpatient_filter, ['inpatient_info.created_at', '>=', $today]);
            $date_search = "Today";
        } elseif ($search_by == 3) {
            // Today
            $yesterday = Carbon::yesterday()->toDateTimeString();

            array_push($triage_filters, ['triage.created_at', '>=', $yesterday]);
            array_push($consultation_filter, ['consultations.created_at', '>=', $yesterday]);
            array_push($inpatient_filter, ['inpatient_info.created_at', '>=', $yesterday]);
            $date_search = "Yesterday";
        }

        //load from triage (any_tb_sysmptoms i.e 1-patient has TB sign, 0-no TB signs)
        //tb triage symptoms with 1- yes and 0 - no (cough_for_2_weeks,fever_for_2_weeks,tb_weight_loss,tb_excessive_night_sweats,tb_poor_weight_gain,tb_contact_with_tb_person)
        //load from consultations (tb_status_assessment i.e 1-definite TB, 2-possible TB, 3-definitely not TB)

        // <5 years, 5- 9years, 10-14years, 15-19years, 20+ years
        $below_5_males_screened_array = $btn_5_and_9_males_screened_array = $btn_10_and_14_males_screened_array = $btn_15_and_19_males_screened_array = $above_19_males_screened_array = [];

        $below_5_females_screened_array = $btn_5_and_9_females_screened_array = $btn_10_and_14_females_screened_array = $btn_15_and_19_females_screened_array = $above_19_females_screened_array = [];

        $below_5_males_presumptives_identified_array = $btn_5_and_9_males_presumptives_identified_array = $btn_10_and_14_males_presumptives_identified_array = $btn_15_and_19_males_presumptives_identified_array = $above_19_males_presumptives_identified_array = [];

        $below_5_females_presumptives_identified_array = $btn_5_and_9_females_presumptives_identified_array = $btn_10_and_14_females_presumptives_identified_array = $btn_15_and_19_females_presumptives_identified_array = $above_19_females_presumptives_identified_array = [];

        $patients_screened_at_triage = Triage::where($triage_filters)->whereNotNull('any_tb_sysmptoms')->leftJoin('patients', 'triage.patient_id', '=', 'patients.id')->select('triage.id as triage_id', 'triage.any_tb_sysmptoms', 'patients.id as patient_id', 'patients.gender', 'patients.date_of_birth')->get();
        $consultations_assesments = Consultation::where($consultation_filter)->whereNotNull('tb_status_assessment')->leftJoin('patients', 'consultations.patient_id', '=', 'patients.id')->select('consultations.id as consultation_id', 'consultations.tb_status_assessment', 'patients.id as patient_id', 'patients.gender', 'patients.date_of_birth')->get();

        foreach ($patients_screened_at_triage as $triage_record) {
            $ca = Carbon::parse($triage_record->date_of_birth);
            $age = $ca->diffInYears();

            if ($age < 5) {
                if ($triage_record->gender == 1) {
                    $below_5_males_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $below_5_males_presumptives_identified_array[] = $triage_record->triage_id : "";
                } else {
                    $below_5_females_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $below_5_females_presumptives_identified_array[] = $triage_record->triage_id : "";
                }
            }

            if (($age >= 5) && ($age < 10)) {
                if ($triage_record->gender == 1) {
                    $btn_5_and_9_males_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $btn_5_and_9_males_screened_array[] = $triage_record->triage_id : "";
                } else {
                    $btn_5_and_9_females_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $btn_5_and_9_females_screened_array[] = $triage_record->triage_id : "";
                }
            }

            if (($age >= 10) && ($age < 15)) {
                if ($triage_record->gender == 1) {
                    $btn_10_and_14_males_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $btn_10_and_14_males_presumptives_identified_array[] = $triage_record->triage_id : "";
                } else {
                    $btn_10_and_14_females_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $btn_10_and_14_females_presumptives_identified_array[] = $triage_record->triage_id : "";
                }
            }

            if (($age >= 15) && ($age < 20)) {
                if ($triage_record->gender == 1) {
                    $btn_15_and_19_males_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $btn_15_and_19_males_presumptives_identified_array[] = $triage_record->triage_id : "";
                } else {
                    $btn_15_and_19_females_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $btn_15_and_19_females_presumptives_identified_array[] = $triage_record->triage_id : "";
                }
            }

            if ($age >= 20) {
                if ($triage_record->gender == 1) {
                    $above_19_males_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $above_19_males_presumptives_identified_array[] = $triage_record->triage_id : "";
                } else {
                    $above_19_females_screened_array[] = $triage_record->triage_id;
                    $triage_record->any_tb_sysmptoms == 1 ? $above_19_females_presumptives_identified_array[] = $triage_record->triage_id : "";
                }
            }
        }

        $below_5_males_presumptives_array = $btn_5_and_9_males_presumptives_array = $btn_10_and_14_males_presumptives_array = $btn_15_and_19_males_presumptives_array = $above_19_males_presumptives_array = [];

        $below_5_females_presumptives_array = $btn_5_and_9_females_presumptives_array = $btn_10_and_14_females_presumptives_array = $btn_15_and_19_females_presumptives_array = $above_19_females_presumptives_array = [];

        foreach ($consultations_assesments as $consultation_record) {
            $ca = Carbon::parse($consultation_record->date_of_birth);
            $age = $ca->diffInYears();

            if (($age < 5) && (in_array($consultation_record->tb_status_assessment, [1]))) {
                if ($consultation_record->gender == 1) {
                    $below_5_males_presumptives_array[] = $consultation_record->consultation_id;
                } else {
                    $below_5_females_presumptives_array[] = $consultation_record->consultation_id;
                }
            }

            if (($age >= 5) && ($age < 10) && (in_array($consultation_record->tb_status_assessment, [1]))) {
                if ($consultation_record->gender == 1) {
                    $btn_5_and_9_males_presumptives_array[] = $consultation_record->consultation_id;
                } else {
                    $btn_5_and_9_females_presumptives_array[] = $consultation_record->consultation_id;
                }
            }

            if (($age >= 10) && ($age < 15) && (in_array($consultation_record->tb_status_assessment, [1]))) {
                if ($consultation_record->gender == 1) {
                    $btn_10_and_14_males_presumptives_array[] = $consultation_record->consultation_id;
                } else {
                    $btn_10_and_14_females_presumptives_array[] = $consultation_record->consultation_id;
                }
            }

            if (($age >= 15) && ($age < 20) && (in_array($consultation_record->tb_status_assessment, [1]))) {
                if ($consultation_record->gender == 1) {
                    $btn_15_and_19_males_presumptives_array[] = $consultation_record->consultation_id;
                } else {
                    $btn_15_and_19_females_presumptives_array[] = $consultation_record->consultation_id;
                }
            }

            if ($age >= 20 && (in_array($consultation_record->tb_status_assessment, [1]))) {
                if ($consultation_record->gender == 1) {
                    $above_19_males_presumptives_array[] = $consultation_record->consultation_id;
                } else {
                    $above_19_females_presumptives_array[] = $consultation_record->consultation_id;
                }
            }
        }

        $below_5_males_possible_TB_at_OPD_array = $btn_5_and_9_males_possible_TB_at_OPD_array = $btn_10_and_14_males_possible_TB_at_OPD_array = $btn_15_and_19_males_possible_TB_at_OPD_array = $above_19_males_possible_TB_at_OPD_array = [];

        $below_5_females_possible_TB_at_OPD_array = $btn_5_and_9_females_possible_TB_at_OPD_array = $btn_10_and_14_females_possible_TB_at_OPD_array = $btn_15_and_19_females_possible_TB_at_OPD_array = $above_19_females_possible_TB_at_OPD_array = [];

        foreach ($consultations_assesments as $consultation_record) {
            $ca = Carbon::parse($consultation_record->date_of_birth);
            $age = $ca->diffInYears();

            if (($age < 5) && (in_array($consultation_record->tb_status_assessment, [2]))) {
                if ($consultation_record->gender == 1) {
                    $below_5_males_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                } else{
                    $below_5_females_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                }
            }

            if (($age >= 5) && ($age < 10) && (in_array($consultation_record->tb_status_assessment, [2]))) {
                if ($consultation_record->gender == 1) {
                    $btn_5_and_9_males_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                } else{
                    $btn_5_and_9_females_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                }
            }

            if (($age >= 10) && ($age < 15) && (in_array($consultation_record->tb_status_assessment, [2]))) {
                if ($consultation_record->gender == 1) {
                    $btn_10_and_14_males_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                } else{
                    $btn_10_and_14_females_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                }
            }

            if (($age >= 15) && ($age < 20) && (in_array($consultation_record->tb_status_assessment, [2]))) {
                if ($consultation_record->gender == 1) {
                    $btn_15_and_19_males_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                } else{
                    $btn_15_and_19_females_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                }
            }

            if ($age >= 20 && (in_array($consultation_record->tb_status_assessment, [2]))) {
                if ($consultation_record->gender == 1) {
                    $above_19_males_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                } else{
                    $above_19_females_possible_TB_at_OPD_array[] = $consultation_record->consultation_id;
                }
            }
        }

        $inpatient_assesments = InpatientInfo::where($inpatient_filter)->whereNotNull('tb_status_assessment')->leftJoin('patients', 'inpatient_info.patient_id', '=', 'patients.id')->select('inpatient_info.id as inpatient_info_id', 'inpatient_info.tb_status_assessment', 'patients.id as patient_id', 'patients.gender', 'patients.date_of_birth')->get();
        $below_5_males_inpatient_presumptives_array = $btn_5_and_9_males_inpatient_presumptives_array = $btn_10_and_14_males_inpatient_presumptives_array = $btn_15_and_19_males_inpatient_presumptives_array = $above_19_males_inpatient_presumptives_array = [];

        $below_5_females_inpatient_presumptives_array = $btn_5_and_9_females_inpatient_presumptives_array = $btn_10_and_14_females_inpatient_presumptives_array = $btn_15_and_19_females_inpatient_presumptives_array = $above_19_females_inpatient_presumptives_array = [];

        foreach ($inpatient_assesments as $inpatient_info_record) {
            $ca = Carbon::parse($inpatient_info_record->date_of_birth);
            $age = $ca->diffInYears();

            if (($age < 5) && (in_array($inpatient_info_record->tb_status_assessment, [1]))) {
                if ($inpatient_info_record->gender == 1) {
                    $below_5_males_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                } else {
                    $below_5_females_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }

            if (($age >= 5) && ($age < 10) && (in_array($inpatient_info_record->tb_status_assessment, [1]))) {
                if ($inpatient_info_record->gender == 1) {
                    $btn_5_and_9_males_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                } else {
                    $btn_5_and_9_females_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }

            if (($age >= 10) && ($age < 15) && (in_array($inpatient_info_record->tb_status_assessment, [1]))) {
                if ($inpatient_info_record->gender == 1) {
                    $btn_10_and_14_males_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                } else {
                    $btn_10_and_14_females_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }

            if (($age >= 15) && ($age < 20) && (in_array($inpatient_info_record->tb_status_assessment, [1]))) {
                if ($inpatient_info_record->gender == 1) {
                    $btn_15_and_19_males_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                } else {
                    $btn_15_and_19_females_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }

            if ($age >= 20 && (in_array($inpatient_info_record->tb_status_assessment, [1]))) {
                if ($inpatient_info_record->gender == 1) {
                    $above_19_males_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                } else {
                    $above_19_females_inpatient_presumptives_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }
        }

        $below_5_males_possible_TB_at_IPD_array = $btn_5_and_9_males_possible_TB_at_IPD_array = $btn_10_and_14_males_possible_TB_at_IPD_array = $btn_15_and_19_males_possible_TB_at_IPD_array = $above_19_males_possible_TB_at_IPD_array = [];

        $below_5_females_possible_TB_at_IPD_array = $btn_5_and_9_females_possible_TB_at_IPD_array = $btn_10_and_14_females_possible_TB_at_IPD_array = $btn_15_and_19_females_possible_TB_at_IPD_array = $above_19_females_possible_TB_at_IPD_array = [];

        foreach ($inpatient_assesments as $inpatient_info_record) {
            $ca = Carbon::parse($inpatient_info_record->date_of_birth);
            $age = $ca->diffInYears();

            if (($age < 5) && (in_array($inpatient_info_record->tb_status_assessment, [2]))) {
                if ($inpatient_info_record->gender == 1) {
                    $below_5_males_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                } else{
                    $below_5_females_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }

            if (($age >= 5) && ($age < 10) && (in_array($inpatient_info_record->tb_status_assessment, [2]))) {
                if ($inpatient_info_record->gender == 1) {
                    $btn_5_and_9_males_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                } else{
                    $btn_5_and_9_females_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }

            if (($age >= 10) && ($age < 15) && (in_array($inpatient_info_record->tb_status_assessment, [2]))) {
                if ($inpatient_info_record->gender == 1) {
                    $btn_10_and_14_males_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                } else{
                    $btn_10_and_14_females_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }

            if (($age >= 15) && ($age < 20) && (in_array($inpatient_info_record->tb_status_assessment, [2]))) {
                if ($inpatient_info_record->gender == 1) {
                    $btn_15_and_19_males_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                } else{
                    $btn_15_and_19_females_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }

            if ($age >= 20 && (in_array($inpatient_info_record->tb_status_assessment, [2]))) {
                if ($inpatient_info_record->gender == 1) {
                    $above_19_males_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                } else{
                    $above_19_females_possible_TB_at_IPD_array[] = $inpatient_info_record->inpatient_info_id;
                }
            }
        }

        return view('reports::hmis_reports.tb_screening_report', compact('start_date', 'end_date', 'search_by', 'custom_date', 'above_19_females_screened_array', 'above_19_males_screened_array', 'btn_15_and_19_females_screened_array', 'btn_15_and_19_males_screened_array', 'btn_10_and_14_females_screened_array', 'btn_10_and_14_males_screened_array', 'btn_5_and_9_females_screened_array', 'btn_5_and_9_males_screened_array', 'below_5_females_screened_array', 'below_5_males_screened_array', 'above_19_females_presumptives_array', 'above_19_males_presumptives_array', 'btn_15_and_19_females_presumptives_array', 'btn_15_and_19_males_presumptives_array', 'btn_10_and_14_females_presumptives_array', 'btn_10_and_14_males_presumptives_array', 'btn_5_and_9_females_presumptives_array', 'btn_5_and_9_males_presumptives_array', 'below_5_females_presumptives_array', 'below_5_males_presumptives_array','above_19_females_inpatient_presumptives_array', 'above_19_males_inpatient_presumptives_array', 'btn_15_and_19_females_inpatient_presumptives_array', 'btn_15_and_19_males_inpatient_presumptives_array', 'btn_10_and_14_females_inpatient_presumptives_array', 'btn_10_and_14_males_inpatient_presumptives_array', 'btn_5_and_9_females_inpatient_presumptives_array', 'btn_5_and_9_males_inpatient_presumptives_array', 'below_5_females_inpatient_presumptives_array', 'below_5_males_inpatient_presumptives_array', 'below_5_males_presumptives_identified_array', 'btn_5_and_9_males_presumptives_identified_array', 'btn_10_and_14_males_presumptives_identified_array', 'btn_15_and_19_males_presumptives_identified_array', 'above_19_males_presumptives_identified_array', 'below_5_females_presumptives_identified_array', 'btn_5_and_9_females_presumptives_identified_array', 'btn_10_and_14_females_presumptives_identified_array', 'btn_15_and_19_females_presumptives_identified_array', 'above_19_females_presumptives_identified_array', 'above_19_females_possible_TB_at_OPD_array', 'above_19_males_possible_TB_at_OPD_array', 'btn_15_and_19_females_possible_TB_at_OPD_array', 'btn_15_and_19_males_possible_TB_at_OPD_array', 'btn_10_and_14_females_possible_TB_at_OPD_array', 'btn_10_and_14_males_possible_TB_at_OPD_array', 'btn_5_and_9_females_possible_TB_at_OPD_array', 'btn_5_and_9_males_possible_TB_at_OPD_array', 'below_5_females_possible_TB_at_OPD_array', 'below_5_males_possible_TB_at_OPD_array', 'above_19_females_possible_TB_at_IPD_array', 'above_19_males_possible_TB_at_IPD_array', 'btn_15_and_19_females_possible_TB_at_IPD_array', 'btn_15_and_19_males_possible_TB_at_IPD_array', 'btn_10_and_14_females_possible_TB_at_IPD_array', 'btn_10_and_14_males_possible_TB_at_IPD_array', 'btn_5_and_9_females_possible_TB_at_IPD_array', 'btn_5_and_9_males_possible_TB_at_IPD_array', 'below_5_females_possible_TB_at_IPD_array', 'below_5_males_possible_TB_at_IPD_array'));
    }

    public function tb_screening_report_presumptive(Request $request)
    {
        $section = $request->section;
        $record_ids = explode(",", $request->record_ids);

        return view('reports::hmis_reports.tb_screening_report_details', compact('record_ids', 'section'));
    }

    public function tb_screening_report_triage_details(Request $request)
    {
        $section = $request->section;
        $record_ids = explode(",", $request->record_ids);

        return view('reports::hmis_reports.tb_screening_report_triage_details', compact('record_ids', 'section'));
    }

    public function tb_screening_triage_presumptives(Request $request)
    {
        $section = $request->section;
        $record_ids = explode(",", $request->record_ids);

        return view('reports::hmis_reports.tb_screening_triage_presumptives', compact('record_ids', 'section'));
    }

    public function procedures_report(Request $request)
    {
        $procedures = DB::table('procedures')->orderBy("name", "asc")->pluck("name", "id")->prepend('- select -', '');

        $users = DB::table('users')->orderBy("first_name", "asc")->select("id")->get()->toArray();
        $doctors = [];
        $all_users = [];
        foreach ($users as $value) {
            $user = User::withTrashed()->find($value->id);
            if (!is_null($user)) {
                $all_users[$value->id] = get_full_name($value->id, 'id', 'first_name', 'last_name', 'users');
            }
        }
        $all_users = ['' => '- select -'] + $all_users;
        $search_string = null;

        $search_by = $request->search_by;
        $reg_date = $request->reg_date;
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $filters = [];

        if ($search_by == 0) {
            // last 24 hours
            $last_day = Carbon::now()->subDay();
            array_push($filters, ['ward_procedures.created_at', '>', $last_day]);
            $search_string = "<h4 class='label label-info'>Showing results of ".streamline_date($last_day)."</h4>";
        } elseif ($search_by == 1) {
            // custom date
            if (is_null($request->reg_date)) {
                flash('Please select a date')->error();
                return redirect()->back();
            }
            $selected_date_filter = Carbon::createFromFormat('d/m/Y', $request->reg_date)->toDateString();
            array_push($filters, ['ward_procedures.created_at', $selected_date_filter]);
            $search_string = "<h4 class='label label-info'>Showing results of ".streamline_date($selected_date_filter)."</h4>";
        } elseif ($search_by == 2) {
            // custom date range
            if (is_null($start_date) || is_null($end_date)) {
                flash('Please select a date')->error();
                return redirect()->back();
            }
            $start_date_search = Carbon::createFromFormat('d/m/Y', $start_date)->startOfDay()->toDateTimeString();
            $end_date_search = Carbon::createFromFormat('d/m/Y', $end_date)->endOfDay()->toDateTimeString();

            array_push($filters, ['ward_procedures.created_at', '>', $start_date_search]);
            array_push($filters, ['ward_procedures.created_at', '<', $end_date_search]);
            $search_string = "<h4 class='label label-info'>Showing between ".streamline_date($start_date_search). " and ".streamline_date($end_date_search)."</h4>";
        }

        if ($request->has('procedure_ordered') && !is_null($request->procedure_ordered)) {
            array_push($filters, ['ward_procedures.procedure_id', $request->procedure_ordered]);
        }

        if ($request->has('created_by') && !is_null($request->created_by)) {
            array_push($filters, ['ward_procedures.created_by', $request->created_by]);
        }

        $procedure_ipd = WardProcedure::where($filters)->orderBy('created_at', 'desc')->paginate(500);

        $filters = [];

        if ($search_by == 0) {
            // last 24 hours
            $last_day = Carbon::now()->subDay();
            array_push($filters, ['ordered_procedures.created_at', '>', $last_day]);
        } elseif ($search_by == 1) {
            // custom date
            $selected_date_filter = Carbon::createFromFormat('d/m/Y', $request->reg_date)->toDateString();
            array_push($filters, ['ordered_procedures.created_at', $selected_date_filter]);
        } elseif ($search_by == 2) {
            // custom date range
            $start_date_search = Carbon::createFromFormat('d/m/Y', $start_date)->startOfDay()->toDateTimeString();
            $end_date_search = Carbon::createFromFormat('d/m/Y', $end_date)->endOfDay()->toDateTimeString();

            array_push($filters, ['ordered_procedures.created_at', '>', $start_date_search]);
            array_push($filters, ['ordered_procedures.created_at', '<', $end_date_search]);
        }

        if ($request->has('procedure_ordered') && !is_null($request->procedure_ordered)) {
            array_push($filters, ['ordered_procedures.procedure_id', $request->procedure_ordered]);
        }

        if ($request->has('created_by') && !is_null($request->created_by)) {
            array_push($filters, ['ordered_procedures.created_by', $request->created_by]);
        }

        $procedure_opd = OrderedProcedure::where($filters)->orderBy('created_at', 'desc')->paginate(500);

        return view('reports::hmis_reports.procedures_report', compact('procedure_ipd','procedure_opd', 'procedures', 'all_users', 'search_string'));
    }

    public function performed_services_report(Request $request)
    {
        $services = DB::table('services')->orderBy("name", "asc")->pluck("name", "id")->prepend('- All services -', '');

        $users = DB::table('users')->orderBy("first_name", "asc")->select("id")->get()->toArray();
        $doctors = [];
        $all_users = [];
        $search_string = null;
        foreach ($users as $value) {
            $user = User::withTrashed()->find($value->id);
            if (!is_null($user)) {
                $all_users[$value->id] = get_full_name($value->id, 'id', 'first_name', 'last_name', 'users');
            }
        }
        $all_users = ['' => '- select -'] + $all_users;

        $search_by = $request->search_by;
        $reg_date = $request->reg_date;
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $filters = [];

        if ($search_by == 0) {
            // last 24 hours
            $last_day = Carbon::now()->subDay();
            array_push($filters, ['ward_consultations_and_services.created_at', '>', $last_day]);
            $search_string = "<h4 class='label label-info'>Showing results of ".streamline_date($last_day)."</h4>";
        } elseif ($search_by == 1) {
            // custom date
            if (is_null($request->reg_date)) {
                flash('Please select a date')->error();
                return redirect()->back();
            }
            $selected_date_filter = Carbon::createFromFormat('d/m/Y', $request->reg_date)->toDateString();
            array_push($filters, ['ward_consultations_and_services.created_at', $selected_date_filter]);
            $search_string = "<h4 class='label label-info'>Showing results of ".streamline_date($selected_date_filter)."</h4>";
        } elseif ($search_by == 2) {
            // custom date range
            if (is_null($start_date) || is_null($end_date)) {
                flash('Please select a date')->error();
                return redirect()->back();
            }
            $start_date_search = Carbon::createFromFormat('d/m/Y', $start_date)->startOfDay()->toDateTimeString();
            $end_date_search = Carbon::createFromFormat('d/m/Y', $end_date)->endOfDay()->toDateTimeString();

            array_push($filters, ['ward_consultations_and_services.created_at', '>', $start_date_search]);
            array_push($filters, ['ward_consultations_and_services.created_at', '<', $end_date_search]);
            $search_string = "<h4 class='label label-info'>Showing between ".streamline_date($start_date_search). " and ".streamline_date($end_date_search)."</h4>";
        }

        if ($request->has('procedure_ordered') && !is_null($request->procedure_ordered)) {
            array_push($filters, ['ward_consultations_and_services.procedure_id', $request->procedure_ordered]);
        }

        if ($request->has('created_by') && !is_null($request->created_by)) {
            array_push($filters, ['ward_consultations_and_services.created_by', $request->created_by]);
        }

        $services_ipd = WardConsultationsAndService::where($filters)->orderBy('created_at', 'desc')->paginate(500);

        $filters = [];

        if ($search_by == 0) {
            // last 24 hours
            $last_day = Carbon::now()->subDay();
            array_push($filters, ['staff_performed_services.created_at', '>', $last_day]);
        } elseif ($search_by == 1) {
            // custom date
            $selected_date_filter = Carbon::createFromFormat('d/m/Y', $request->reg_date)->toDateString();
            array_push($filters, ['staff_performed_services.created_at', $selected_date_filter]);
        } elseif ($search_by == 2) {
            // custom date range
            $start_date_search = Carbon::createFromFormat('d/m/Y', $start_date)->startOfDay()->toDateTimeString();
            $end_date_search = Carbon::createFromFormat('d/m/Y', $end_date)->endOfDay()->toDateTimeString();

            array_push($filters, ['staff_performed_services.created_at', '>', $start_date_search]);
            array_push($filters, ['staff_performed_services.created_at', '<', $end_date_search]);
        }

        if ($request->has('procedure_ordered') && !is_null($request->procedure_ordered)) {
            array_push($filters, ['staff_performed_services.procedure_id', $request->procedure_ordered]);
        }

        if ($request->has('created_by') && !is_null($request->created_by)) {
            array_push($filters, ['ordered_services.created_by', $request->created_by]);
        }

        $services_opd = StaffPerformedService::where('item_category', 3)->where($filters)->orderBy('created_at', 'desc')->paginate(500);

        return view('reports::hmis_reports.services_report', compact('services_ipd','services_opd', 'services', 'all_users', 'search_string'));
    }

    public function maternity_report(Request $request)
    {
        $modes_of_delivery = DB::table('mode_of_delivery')->orderBy("name", "asc")->pluck("name", "id")->prepend('- All modes of delivery -', '');

        $search_by = $request->search_by;
        $reg_date = $request->reg_date;
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $filters = [];
        $filters_string = "";

        if ($search_by == 0) {
            // last 24 hours
            $last_day = Carbon::now()->subDay();
            array_push($filters, ['created_at', '>', $last_day]);
        } elseif ($search_by == 1) {
            // custom date
            if (is_null($request->reg_date)) {
                flash('Please select a date')->error();
                return redirect()->back();
            }
            $selected_date_filter = Carbon::createFromFormat('d/m/Y', $request->reg_date)->toDateString();
            array_push($filters, ['created_at', $selected_date_filter]);
        } elseif ($search_by == 2) {
            // custom date range
            if (is_null($start_date) || is_null($end_date)) {
                flash('Please select a date')->error();
                return redirect()->back();
            }
            $start_date_search = Carbon::createFromFormat('d/m/Y', $start_date)->startOfDay()->toDateTimeString();
            $end_date_search = Carbon::createFromFormat('d/m/Y', $end_date)->endOfDay()->toDateTimeString();

            array_push($filters, ['created_at', '>', $start_date_search]);
            array_push($filters, ['created_at', '<', $end_date_search]);
        }

        $maternity_records = MaternityDeliveryRecord::where($filters)->orderBy('created_at', 'desc')->paginate(500);
        $delivery_records_numbers_array = [];

        if (count($maternity_records)) {
            foreach ($maternity_records as $record) {
                $foetal_number = $record->foetal_number;
                $modes_of_delivery_array = explode(",", $record->mode_of_delivery);

                for ($i=0; $i < count($modes_of_delivery_array) ; $i++) {
                    if (!is_null($request->mode_of_delivery)) {
                        if (($i < $foetal_number) && ($request->mode_of_delivery == $modes_of_delivery_array[$i]) && ($modes_of_delivery_array[$i] != "")) {
                            $delivery_records_numbers_array[$modes_of_delivery_array[$i]] = $delivery_records_numbers_array[$modes_of_delivery_array[$i]] + 1;
                        }
                    } else{
                        if (($i < $foetal_number) && ($modes_of_delivery_array[$i] != "")) {
                            if (array_key_exists($modes_of_delivery_array[$i], $delivery_records_numbers_array)) {
                                $delivery_records_numbers_array[$modes_of_delivery_array[$i]] = $delivery_records_numbers_array[$modes_of_delivery_array[$i]] + 1;
                            } else{
                                $delivery_records_numbers_array[$modes_of_delivery_array[$i]] = 1;
                            }
                        }
                    }
                }
            }
        }

        return view('reports::hmis_reports.maternity_report', compact('delivery_records_numbers_array', 'modes_of_delivery', 'search_by', 'reg_date', 'start_date', 'end_date'));
    }

    public function maternity_report_details(Request $request)
    {
        $modes_of_delivery = DB::table('mode_of_delivery')->orderBy("name", "asc")->pluck("name", "id")->prepend('- All modes of delivery -', '');

        $search_by = $request->search_by;
        $reg_date = $request->reg_date;
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $filters = [];
        $filters_string = "";

        if ($search_by == 0) {
            // last 24 hours
            $last_day = Carbon::now()->subDay();
            array_push($filters, ['created_at', '>', $last_day]);
        } elseif ($search_by == 1) {
            // custom date
            if (is_null($request->reg_date)) {
                flash('Please select a date')->error();
                return redirect()->back();
            }
            $selected_date_filter = Carbon::createFromFormat('d/m/Y', $request->reg_date)->toDateString();
            array_push($filters, ['created_at', $selected_date_filter]);
        } elseif ($search_by == 2) {
            // custom date range
            if (is_null($start_date) || is_null($end_date)) {
                flash('Please select a date')->error();
                return redirect()->back();
            }
            $start_date_search = Carbon::createFromFormat('d/m/Y', $start_date)->startOfDay()->toDateTimeString();
            $end_date_search = Carbon::createFromFormat('d/m/Y', $end_date)->endOfDay()->toDateTimeString();

            array_push($filters, ['created_at', '>', $start_date_search]);
            array_push($filters, ['created_at', '<', $end_date_search]);
        }

        $maternity_records = MaternityDeliveryRecord::where($filters)->orderBy('created_at', 'desc')->paginate(500);

        $selected_modes_of_delivery = $request->mode_of_delivery;

        return view('reports::hmis_reports.maternity_report_details', compact('maternity_records', 'modes_of_delivery', 'search_by', 'reg_date', 'start_date', 'end_date', 'selected_modes_of_delivery'));
    }

    public function doctors_performance_report_details(Request $request) {
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $doctor_id = $request->doctor_id;
        $report_type = $request->report_type;
        $patients = [];
        $admitted_patients = [];
        $prescriptions = [];
        $investigation_orders = [];

        $diagnoses = Diagnosis::where('available', 1)->pluck('name', 'id');
        $wards = DB::table('wards')->pluck('name', 'id');
        $drugs = DB::table('drugs')->pluck('name', 'id');
        $patient_categories = DB::table('patient_categories')->pluck('name', 'id');
        $clinics = DB::table('clinics')->pluck('name', 'id');
        $investigations = DB::table('investigations')->pluck('name', 'id');

        $criteria = 'Between (' . streamline_date($start_date) . ') And (' . streamline_date($end_date) . ')';

        $consultations = DB::table('consultations')->whereNull('consultations.deleted_at')
            ->whereBetween('consultations.created_at', [$start_date, $end_date])
            ->where('consultations.completed', 1)
            ->where(function($query) use ($doctor_id){
                $query->where('consultations.consultation_done_by', $doctor_id)
                    ->orWhere('consultations.created_by', $doctor_id);
                //->orWhere('consultations.updated_by', $doctor_id);
            })
            ->join('patients', 'consultations.patient_id', '=', 'patients.id')
            ->join('patient_episodes', 'consultations.episode_id', '=', 'patient_episodes.id')
            ->get(['patients.number', 'patients.first_name', 'patients.last_name', 'consultations.patient_id', 'consultations.admitted_on', 'patient_episodes.clinic_id',
                'consultations.primary_diagnosis', 'consultations.ward_id', 'consultations.episode_id', 'consultations.created_at', 'patients.category_id']);

        foreach ($consultations as $consultation) {

            if ($report_type == 1) {
                $patients[] = [
                    "id" => $consultation->patient_id,
                    "name" => $consultation->first_name . ' ' . $consultation->last_name,
                    "number" => $consultation->number,
                    "patient_category" => $patient_categories[$consultation->category_id] ?? 'N/A',
                    "clinic" => $clinics[$consultation->clinic_id] ?? 'N/A',
                    "diagnosis" => $diagnoses[$consultation->primary_diagnosis] ?? 'N/A',
                    "date" => streamline_date_time($consultation->created_at),
                ];
            } elseif ($report_type == 2) {
                $ordered_investigations = DB::table('ordered_investigations')->where('episode_id', $consultation->episode_id)
                    ->where('created_by', $doctor_id)
                    ->whereNull('deleted_at')
                    ->get(['investigation_id', 'created_at', 'investigation_status']);

                if (count($ordered_investigations) > 0) {
                    $investigation_orders[] = [
                        "id" => $consultation->patient_id,
                        "name" => $consultation->first_name . ' ' . $consultation->last_name,
                        "number" => $consultation->number,
                        "patient_category" => $patient_categories[$consultation->category_id] ?? 'N/A',
                        "clinic" => $clinics[$consultation->clinic_id] ?? 'N/A',
                        "diagnosis" => $diagnoses[$consultation->primary_diagnosis] ?? 'N/A',
                        "investigations" => $ordered_investigations,
                        "date" => streamline_date_time($consultation->created_at),
                    ];
                }
            } elseif ($report_type == 3) {
                $treatments = DB::table('treatments')->where('episode_id', $consultation->episode_id)
                    ->where('created_by', $doctor_id)
                    ->whereNull('deleted_at')
                    ->get(['drugs', 'created_at']);

                if (count($treatments) > 0) {
                    $prescriptions[] = [
                        "id" => $consultation->patient_id,
                        "name" => $consultation->first_name . ' ' . $consultation->last_name,
                        "number" => $consultation->number,
                        "patient_category" => $patient_categories[$consultation->category_id] ?? 'N/A',
                        "clinic" => $clinics[$consultation->clinic_id] ?? 'N/A',
                        "diagnosis" => $diagnoses[$consultation->primary_diagnosis] ?? 'N/A',
                        "treatments" => $treatments,
                        "date" => streamline_date_time($consultation->created_at),
                    ];
                }
            } elseif ($report_type == 4) {
                if (!is_null($consultation->ward_id)) {
                    $admitted_patients[] = [
                        "id" => $consultation->patient_id,
                        "name" => $consultation->first_name . ' ' . $consultation->last_name,
                        "number" => $consultation->number,
                        "patient_category" => $patient_categories[$consultation->category_id] ?? 'N/A',
                        "clinic" => $clinics[$consultation->clinic_id] ?? 'N/A',
                        "diagnosis" => $diagnoses[$consultation->primary_diagnosis] ?? 'N/A',
                        "ward" => $wards[$consultation->ward_id] ?? 'N/A',
                        "date" => streamline_date_time($consultation->created_at),
                    ];
                }
            }
        }

        return view('reports::hmis_reports.doctors_performance_report_details', compact('criteria', 'patients', 'report_type', 'start_date', 'end_date', 'doctor_id', 'report_type',
            'admitted_patients', 'prescriptions', 'investigation_orders', 'drugs', 'investigations'));
    }

    public function view_investigation_specimen_report(Request $request) {
        $search_text = "";

        switch ($request->search_date_by){
            case 'yesterday':
                $end_date = Carbon::yesterday()->endOfDay();
                $start_date = Carbon::yesterday()->startOfDay();
                $search_text .= "Yesterday ";
                break;
            case 'custom_date':
                $end_date = Carbon::parse($request->start_date)->endOfDay();
                $start_date = Carbon::parse($request->start_date)->startOfDay();
                $search_text .= "From: " . streamline_date($start_date) . " ";
                break;
            case 'custom_date_range':
                $end_date = Carbon::parse($request->end_date)->endOfDay();
                $start_date = Carbon::parse($request->start_date)->startOfDay();
                $search_text .= "From: " . streamline_date($start_date) . " to " . streamline_date($end_date) . " ";
                break;
            case 'today':
            default:
                $end_date = Carbon::today()->endOfDay();
                $start_date = Carbon::today()->startOfDay();
                $search_text .= "Today ";
                break;
        }

        $filter = [];

        if (isset($request->specimen_status) && $request->specimen_status != 0) {
            $filter[] = ['ordered_investigations.specimen_status', 'like', '%' . $request->specimen_status . '%'];
        }

        $records = DB::table('ordered_investigations')
            ->join('patients', 'patients.id', '=', 'ordered_investigations.patient_id')
            ->where('ordered_investigations.request_received', 1)
            ->where($filter)
            ->whereNull('ordered_investigations.deleted_at')
            ->whereBetween('ordered_investigations.created_at', [$start_date, $end_date])
            ->where('ordered_investigations.order_type', 'Lab')
            ->get(['ordered_investigations.patient_id', 'ordered_investigations.investigation_id', 'ordered_investigations.specimen', 'ordered_investigations.specimen_status',
                'ordered_investigations.specimen_reason', 'ordered_investigations.request_received_date', 'ordered_investigations.request_received_by', 'patients.first_name', 'patients.last_name', 'patients.number']);

        $investigations = DB::table('investigations')->pluck('name', 'id')->toArray();
        $laboratory_specimens = DB::table('laboratory_specimens')->pluck('name', 'id')->toArray();

        return view('reports::hmis_reports.view_investigation_specimen_report', compact('search_text', 'records', 'investigations', 'laboratory_specimens'));
    }

    public function get_registration_field_options (Request $request) {
        $option = PatientRegistrationField::where('id', $request->id)->whereNotNull('options')->first();
        if (!empty($option)) {
            $data ='';
            $options = explode(',', $option->options);
            foreach($options as $option){
                $data .= '<option value="'.$option.'">'.$option.'</option>';
            }
        } else $data ='false';
        return $data;
    }

    public function view_eye_clinic_report(Request $request) {
        if(isset($request->start_date) && isset($request->end_date)){
            $start_date = Carbon::parse($request->start_date)->startOfDay()->toDateTimeString();
            $end_date = Carbon::parse($request->end_date)->endOfDay()->toDateTimeString();
        } else {
            $start_date = Carbon::today()->startOfDay()->toDateTimeString();
            $end_date = Carbon::today()->endOfDay()->toDateTimeString();
        }

        $diagnoses = \Illuminate\Support\Facades\DB::table('diagnoses')->pluck('name', 'id');
        $districts = \Illuminate\Support\Facades\DB::table('districts')->pluck('name', 'id');
        $procedures = \Illuminate\Support\Facades\DB::table('procedures')->pluck('name', 'id');

        $main_exams = \Illuminate\Support\Facades\DB::table('eye_clinic_main_exam as me')
            ->join('eye_clinic_base_exam_refraction as bf', 'bf.episode_id', '=', 'me.episode_id')
            ->join('patients as pt', 'pt.id', '=', 'me.patient_id')
            ->join('ordered_procedures as pr', 'pr.episode_id', '=', 'me.episode_id')
            ->whereBetween('me.created_at', [$start_date, $end_date])
            ->whereNull('me.deleted_at')->whereNull('pt.deleted_at')->whereNull('pr.deleted_at')
            ->get(['me.patient_id', 'me.episode_id', 'right_eye_diagnosis', 'left_eye_diagnosis', 'cdr_left', 'cdr_right', 'iop_left', 'iop_right',
                'visual_acuity_distance_sc_right', 'visual_acuity_distance_sc_left', 'visual_acuity_distance_cc_right', 'visual_acuity_distance_cc_left',
                'visual_acuity_distance_ph_right', 'visual_acuity_distance_ph_left', 'visual_acuity_near_sc_right', 'visual_acuity_near_sc_left',
                'visual_acuity_near_cc_right', 'visual_acuity_near_cc_left', 'pt.first_name', 'pt.last_name', 'pt.date_of_birth', 'pt.district_id', 'pt.gender',
                'pr.procedure_id', 'pr.eye']);

        return view('eye_clinic::eye_clinic.view_eye_clinic_report', compact('diagnoses', 'districts', 'procedures', 'start_date', 'end_date', 'main_exams'));
    }

    public function print_eye_clinic_report($start_date, $end_date) {
        $diagnoses = \Illuminate\Support\Facades\DB::table('diagnoses')->pluck('name', 'id');
        $districts = \Illuminate\Support\Facades\DB::table('districts')->pluck('name', 'id');
        $procedures = \Illuminate\Support\Facades\DB::table('procedures')->pluck('name', 'id');

        $main_exams = \Illuminate\Support\Facades\DB::table('eye_clinic_main_exam as me')
            ->join('eye_clinic_base_exam_refraction as bf', 'bf.episode_id', '=', 'me.episode_id')
            ->join('patients as pt', 'pt.id', '=', 'me.patient_id')
            ->join('ordered_procedures as pr', 'pr.episode_id', '=', 'me.episode_id')
            ->whereBetween('me.created_at', [$start_date, $end_date])
            ->whereNull('me.deleted_at')->whereNull('pt.deleted_at')->whereNull('pr.deleted_at')
            ->get(['me.patient_id', 'me.episode_id', 'right_eye_diagnosis', 'left_eye_diagnosis', 'cdr_left', 'cdr_right', 'iop_left', 'iop_right',
                'visual_acuity_distance_sc_right', 'visual_acuity_distance_sc_left', 'visual_acuity_distance_cc_right', 'visual_acuity_distance_cc_left',
                'visual_acuity_distance_ph_right', 'visual_acuity_distance_ph_left', 'visual_acuity_near_sc_right', 'visual_acuity_near_sc_left',
                'visual_acuity_near_cc_right', 'visual_acuity_near_cc_left', 'pt.first_name', 'pt.last_name', 'pt.date_of_birth', 'pt.district_id', 'pt.gender',
                'pr.procedure_id', 'pr.eye']);

        $hospital_information = HospitalInformation::find(1);

        $data = [
            'main_exams' => $main_exams,
            'hospitalInfo' => $hospital_information,
            'diagnoses' => $diagnoses,
            'districts' => $districts,
            'procedures' => $procedures
        ];

        $pdf = SnappyPDF::loadView('eye_clinic::eye_clinic/print_eye_clinic_report', $data)
            ->setOrientation('landscape')
            ->setOption('margin-bottom', 7)
            ->setOption('margin-top', 5)
            ->setOption('footer-html', '<i>Printed On  ' . date('d-M-Y  h:ia') . '</i>');

        return $pdf->inline('Eye Clinic Report' . date(" d-m-y h:ia") . '.pdf');
    }
}