streamline-emr/docker/streamline-src/Modules/Insurance/Http/Controllers/InsuranceMembersController.php

<?php

namespace Modules\Insurance\Http\Controllers;

use Carbon\Carbon;
use Illuminate\Database\QueryException;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Streamline\Models\District;
use Streamline\Models\FamilyRelationship;
use Streamline\Models\HeadOfFamily;
use Streamline\Models\HospitalInformation;
use Streamline\Models\InsuranceGroup;
use Streamline\Models\InsuranceMember;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Auth;
use Streamline\Models\MaritalStatus;
use Streamline\Models\Occupation;
use Streamline\Models\Patient;
use Streamline\Models\PatientCategory;
use Streamline\Models\Religion;
use Barryvdh\Snappy\Facades\SnappyPdf;
use Streamline\Models\CommunityHealthInsurancePlan;
use Streamline\Models\TrackReceipt;

class InsuranceMembersController extends Controller {

    public function __construct() {
        $this->middleware('permission:insurance-member-list');
        $this->middleware('permission:insurance-member-create', ['only' => ['create', 'store']]);
        $this->middleware('permission:insurance-member-edit', ['only' => ['edit', 'update']]);
        $this->middleware('permission:insurance-member-delete', ['only' => ['destroy']]);
    }

    /**
     * Display a listing of the resource.
     *
     */
    public function index() {
        $insurance_family = HeadOfFamily::pluck('name', 'id');

        $insurance_members = InsuranceMember::join('patients', 'insurance_members.patient_id', '=', 'patients.id')
            ->select('insurance_members.*', 'patients.number', 'patients.category_id')
            ->paginate(200);

        $total_registered = DB::table('insurance_members')->whereNull('deleted_at')->get()->count();
        $total_active_insurance = DB::table('insurance_members')->whereNull('deleted_at')
            ->whereRaw("(`current_insurance_end_date` > '" . date('Y-m-d') . "')")
            ->get()->count();

        $total_inactive_insurance = DB::table('insurance_members')->whereNull('deleted_at')
            ->whereRaw("(`current_insurance_end_date` is null or `current_insurance_end_date` <= '" . date('Y-m-d') . "')")
            ->get()->count();

        $insurance_groups_select = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();
        $insurance_groups_select = ['' => '- select -'] + $insurance_groups_select;

        $patient_categories = PatientCategory::pluck('name', 'id');

        $insurance_groups = InsuranceGroup::pluck('name', 'id');

        $relations = FamilyRelationship::pluck('name', 'id');
        $plans = CommunityHealthInsurancePlan::pluck('name', 'id');

        $full_names = DB::table('patients')->whereNull('deleted_at')
            ->select(DB::raw('CONCAT(first_name, " ", last_name, " - ", number, " - (", phone, ")") AS full_name'))
            ->pluck("full_name");

        return view('insurance::insurance_members.index', compact('insurance_members', 'patient_categories', 'insurance_groups', 'insurance_family', 'total_registered', 'relations', 'insurance_groups_select',
            'plans', 'total_active_insurance', 'total_inactive_insurance', 'full_names'));
    }

    public function insurance_card_print_index() {
        $insurance_family = DB::table('insurance_heads_of_family')->whereNull('deleted_at')
            ->orderBy('name', 'asc')->pluck('name', 'id')->toArray();

        $insurance_members = [];

        $insurance_groups = DB::table('insurance_groups')->whereNull('deleted_at')
            ->orderBy('name', 'asc')->pluck('name', 'id')->toArray();
        $insurance_groups_select = ['' => '- select -'] + $insurance_groups;
        $insurance_family_select = ['' => '- select -'] + $insurance_family;

        $patient_categories = PatientCategory::pluck('name', 'id');

        $relations = FamilyRelationship::pluck('name', 'id');
        $plans = CommunityHealthInsurancePlan::pluck('name', 'id');

        return view ('insurance::insurance_members.insurance_card_print_index', compact('insurance_members', 'patient_categories', 'insurance_groups', 'insurance_family', 'relations', 'insurance_groups_select','insurance_family_select', 'plans'));
    }

    public function search(Request $request){
        $insurance_family = HeadOfFamily::orderBy('name', 'asc')->pluck('name', 'id')->toArray();

        $total_registered = DB::table('insurance_members')->whereNull('deleted_at')->get()->count();
        $total_active_insurance = DB::table('insurance_members')->whereNull('deleted_at')
            ->whereRaw("(`current_insurance_end_date` > '" . date('Y-m-d') . "')")
            ->get()->count();

        $total_inactive_insurance = DB::table('insurance_members')->whereNull('deleted_at')
            ->whereRaw("(`current_insurance_end_date` is null or `current_insurance_end_date` <= '" . date('Y-m-d') . "')")
            ->get()->count();

        $patient_categories = PatientCategory::pluck('name', 'id');

        $insurance_groups = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();
        $insurance_groups_select = ['' => '- select -'] + $insurance_groups;
        $insurance_family_select = ['' => '- select -'] + $insurance_family;

        $relations = FamilyRelationship::pluck('name', 'id');
        $plans = CommunityHealthInsurancePlan::pluck('name', 'id');

        $criteria = '';
        $filters = [];
        $full_name_search = $request->full_name;

        if ($full_name_search) {
            $split_full_name_array = explode(' ', $full_name_search);

            if (is_array($split_full_name_array)) {
                $first_name_from_split = $split_full_name_array[0] ?? "";
                $last_name_from_split = $split_full_name_array[1] ?? "";
                $patient_number_from_split = count($split_full_name_array) > 2 ? array_slice($split_full_name_array, -3)[0] : '';

                //just only use the patient number from the full name submitted
                if ($patient_number_from_split != "") {
                    $filters[] = ['number', 'LIKE', '%' . $patient_number_from_split . '%'];
                    $criteria .= 'Number (' . $patient_number_from_split . ') ';
                } else {
                    $filters[] = ['first_name', 'LIKE', '%' . $first_name_from_split . '%'];
                    $filters[] = ['last_name', 'LIKE', '%' . $last_name_from_split . '%'];
                    $criteria .= 'Name (' . $full_name_search . ') ';
                }
            } else {
                //just only use the patient number from the full name submitted
                $filters[] = ['first_name', 'LIKE', '%' . $full_name_search . '%'];
                $criteria .= 'Name (' . $full_name_search . ') ';
            }
        }

        if ($request->group_id) {
            $filters[] = ['insurance_members.group_id', '=', $request->group_id];
            $criteria .= 'Insurance Group ('. ($insurance_groups[$request->group_id] ?? 'N/A') .') ';
        }
        if ($request->insurance_family) {
            $filters[] = ['insurance_members.family_id', '=', $request->insurance_family];
            $criteria .= 'Family ('. ($insurance_family[$request->insurance_family] ?? 'N/A') .') ';
        }

        $insurance_members_query = InsuranceMember::query()
            ->join('patients', 'insurance_members.patient_id', '=', 'patients.id')
            ->select('insurance_members.*', 'patients.number', 'patients.category_id')
            ->where($filters)->orderBy('id', 'desc');

        if ($request->member_type == 1){
            $insurance_members_query->where('insurance_members.current_insurance_end_date', '>', date('Y-m-d'));
            $criteria .= __('insurance_members.members_with_active_insurance') . ' ';
        } elseif ($request->member_type == 2) {
            $insurance_members_query->whereRaw("(`insurance_members`.`current_insurance_end_date` is null or `insurance_members`.`current_insurance_end_date` <= '" . date('Y-m-d') . "')");
            $criteria .= __('insurance_members.members_with_expired_insurance') . ' ';
        }

        $insurance_members = $insurance_members_query->paginate(200);

        $full_names = DB::table('patients')->whereNull('deleted_at')
            ->select(DB::raw('CONCAT(first_name, " ", last_name, " - ", number, " - (", phone, ")") AS full_name'))
            ->pluck("full_name");

        if(isset($request->is_from_card_print)) {
            return view('insurance::insurance_members.insurance_card_print_index', compact('insurance_members', 'patient_categories', 'insurance_groups', 'insurance_family', 'total_registered', 'relations', 'criteria', 'insurance_groups_select', 'plans', 'insurance_family_select', 'total_active_insurance', 'total_inactive_insurance'));
        } else {
            return view('insurance::insurance_members.index', compact('insurance_members', 'patient_categories', 'insurance_groups', 'insurance_family', 'total_registered', 'relations', 'criteria', 'insurance_groups_select', 'insurance_family_select', 'plans', 'total_active_insurance', 'total_inactive_insurance', 'full_names'));
        }
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return Response
     */
    public function create() {
        $insurance_groups = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();

        $insurance_groups = ['' => '- select -'] + $insurance_groups;

        $families = HeadOfFamily::orderBy('name', 'asc')->pluck('id')->toArray();

        $relations = FamilyRelationship::orderBy('id', 'asc')->pluck('name', 'id')->toArray();

        $relations = ['' => '- select -'] + $relations;

        $occupations = Occupation::orderBy('name')->pluck('name', 'id')->toArray();
        $patient_categories = PatientCategory::where('available', 1)->orderby('name')->pluck('name', 'id')->toArray();
        $districts = District::orderBy('name')->pluck('name', 'id')->toArray();
        $marital_statuses = MaritalStatus::pluck('name', 'id')->toArray();
        $religions = Religion::orderBy('name')->pluck('name', 'id')->toArray();
        $relationships = FamilyRelationship::orderBy('name')->pluck('name', 'id')->toArray();

        $occupations = ['' => '- select -'] + $occupations;
        $districts = ['' => '- select -'] + $districts;
        $religions = ['' => '- select -'] + $religions;
        $relationships = ['' => '- select -'] + $relationships;
        $countries = DB::table('countries')->whereNull('deleted_at')->pluck('name','id')->prepend('- Select country of origin - ', '');
        $hospital_information = HospitalInformation::first();
        $dynamic_counties = [];//DB::table('counties')->pluck('name','id')->prepend('- Select County - ', '');
        $dynamic_sub_counties = [];//DB::table('subcounties')->pluck('name','id')->prepend('- Select Sub County - ', '');
        $dynamic_parishes = []; //DB::table('parishes')->pluck('name','id')->prepend('- Select Parish - ', '');
        $person_titles = DB::table('person_titles')->whereNull('deleted_at')->pluck('name','id')->prepend('- Select Title - ', '');
        $chi_plans = DB::table('community_health_insurance_plans')->whereNull('deleted_at')->pluck('name','id')->prepend('- Select Plan - ', '');
        $chi_risks = DB::table('community_health_insurance_risks')->whereNull('deleted_at')->pluck('name','id')->prepend('None', '0');

        return view('insurance::insurance_members.create', compact('insurance_groups', 'families', 'relations', 'dynamic_counties', 'dynamic_parishes', 'dynamic_sub_counties', 'relationships', 'religions', 'occupations', 'districts', 'marital_statuses', 'patient_categories', 'hospital_information', 'countries', 'person_titles', 'chi_plans', 'chi_risks'));
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return Response
     */
    public function store(Request $request) {
        request()->validate([
            'group_id' => 'required',
            'first_name' => 'required',
            'last_name' => 'required',
            'date_of_birth' => 'required_without:age_in_years',
            'age_in_years' => 'required_without:date_of_birth',
            'marital_status' => 'required',
            'occupation' => 'required',
            'religion' => 'required',
            'patient_category' => 'required',
            'duplicate_research_id' => 'nullable|required_with:research_id|same:research_id|unique:insurance_members,research_id',
            'research_id' => 'nullable|required_with:duplicate_research_id|same:duplicate_research_id|unique:insurance_members'
        ], [
            'duplicate_research_id.required_with' => 'Research ID field must be filled.',
            'duplicate_research_id.same' => 'Research ID values must be similar or identical.',
            'research_id.required_with' => 'Research ID field must be filled.',
            'research_id.same' => 'Research ID values must be similar or identical.',
            'research_id.unique' => 'Research ID already exists, please enter another one.',
            'duplicate_research_id.unique' => 'Research ID already exists, please enter another one.',
        ]);

        // save the patient
        $patient = new Patient;
        $patient->first_name = $request->first_name;
        $patient->last_name = $request->last_name;
        $calculated_date_of_birth = null;
        if (is_null($request->date_of_birth)) {
            $age_in_years = $request->age_in_years;
            $calculated_dob = Carbon::now()->subYears($age_in_years);
            $calculated_date_of_birth = $calculated_dob->toDateString();
            $patient->date_of_birth = $calculated_date_of_birth;
        } else {
            $patient->date_of_birth = Carbon::createFromFormat('d/m/Y', $request->date_of_birth)->toDateString();
        }

        $patient->marital_status = $request->marital_status;
        $patient->occupation_id = $request->occupation;
        $patient->religion_id = $request->religion;
        $patient->category_id = $request->patient_category;
        $patient->insurance_status = 1;
        $patient->citizenship = 1;
        $patient->country_id = 6; //uganda

        // split the residence values
        $residence_array = explode(",", $request->residence);
        $patient->district_id = $residence_array[4] ?? 0;
        $patient->county_id = $residence_array[3] ?? 0;
        $patient->subcounty_id = $residence_array[2] ?? 0;
        $patient->parish_id = $residence_array[1] ?? 0;
        $patient->village_id = $residence_array[0] ?? 0;

        $patient->gender = $request->gender;
        $patient->next_of_kin = $request->next_of_kin;
        $patient->next_of_kin_relationship = $request->next_of_kin_relationship;
        $patient->phone_of_next_of_kin = removeSpaces($request->next_of_kin_phone);
        $patient->phone = removeSpaces($request->phone);
        $patient->national_id = $request->national_id;
        $patient->phone_owner = $request->phone_owner; // problem
        $patient->hospital_contact = $request->hospital_contact;
        $patient->language = $request->language;
        $patient->lc_one = $request->lc_one;
        $patient->created_by = Auth::id();

        if ($patient->save()){
            $prefix = DB::table('hospital_information')->where('id', 1)->value('patient_number_abbr');
            $new_id = quadLimit($patient->id);
            $patient_number = $prefix . "-" . $new_id;

            DB::table('patients')->where('id', $new_id)->update(['number' => $patient_number]); // Updating the patient number

            flash('Patient with patient number ' . $patient_number . ' has been successfully registered')->success();

            // add patient to insurance
            $insurance_member = new InsuranceMember;

            if ($request->hasFile('photo')){
                $photo = $request->file('photo');
                $photo_name = $patient->id . "." . $photo->getClientOriginalExtension();
                $destinationPath = public_path('/uploads/images/insurance_members');
                $photoPath = $destinationPath . "/" . $photo_name;
                $photo->move($destinationPath, $photoPath);
                $insurance_member->photo = $photo_name;
            } else {
                $insurance_member->photo = 'placeholder.png';
            }

            $logged_in_user_id = Auth::id();

            if ($request->is_family_head == 1){
                $head_of_family = new HeadOfFamily;
                $head_of_family->name = $request->first_name . " " . $request->last_name;
                $head_of_family->group_id = $request->group_id;
                $head_of_family->patient_id = $patient->id;
                $head_of_family->risk_ids = is_null($request->family_risk_id) ? null : implode(",", $request->family_risk_id);
                $head_of_family->created_by = $logged_in_user_id;
                $head_of_family->updated_by = $logged_in_user_id;
                $head_of_family->save();

                $insurance_member->family_id = $head_of_family->id;
            } else {
                $insurance_member->family_id = $request->family_id;
            }

            $insurance_member->group_id = $request->group_id;
            $insurance_member->relationship_to_head = $request->relationship_to_head;
            $insurance_member->patient_id = $patient->id;
            $insurance_member->is_family_head = $request->is_family_head;
            $insurance_member->title = $request->title;
            $insurance_member->premium = $request->member_premium;
            $insurance_member->chi_plan = $request->chi_plan;
            $insurance_member->other_plan = $request->other_plan;
            $insurance_member->risk_ids = is_null($request->risk_id) ? null : implode(",", $request->risk_id);
            $insurance_member->membership_start_date = is_null($request->membership_start_date) ? null : Carbon::createFromFormat('d/m/Y', $request->membership_start_date)->toDateString();
            $insurance_member->membership_registration_date = is_null($request->membership_registration_date) ? null : Carbon::createFromFormat('d/m/Y', $request->membership_registration_date)->toDateString();
            $insurance_member->social_security_number = $request->social_security_number;
            $insurance_member->research_id = $request->research_id;
            $insurance_member->next_anniversary_date = is_null($request->next_anniversary_date) ? null : Carbon::createFromFormat('d/m/Y', $request->next_anniversary_date)->toDateString();
            $insurance_member->created_by = $logged_in_user_id;

            try {

                $insurance_member->save();

                if ($request->has('does_member_have_existing_premium') && $request->does_member_have_existing_premium == 1 && !is_null($request->existing_premium_start_date)) {

                    $existing_premium_start_date = is_null($request->existing_premium_start_date) ? null : Carbon::createFromFormat('d/m/Y', $request->existing_premium_start_date)->toDateString();
                    $existing_premium_end_date = is_null($request->existing_premium_end_date) ? null : Carbon::createFromFormat('d/m/Y', $request->existing_premium_end_date)->toDateString();

                    $insurance_member->existing_premium_start_date = $existing_premium_start_date;
                    $insurance_member->existing_premium_end_date = $existing_premium_end_date;

                    $track_receipts = new TrackReceipt;
                    $track_receipts->created_by = Auth::id();
                    $track_receipts->reason = 'Premiums';
                    $track_receipts->save();
                    $receipt_number = sprintf("%04u", $track_receipts->id);

                    add_member_to_insurance_subscription($insurance_member->group_id, $insurance_member->id, $insurance_member->premium,
                        $insurance_member->family_id, $existing_premium_start_date, $existing_premium_end_date, $receipt_number);

                    $insurance_member->current_insurance_end_date = $existing_premium_end_date;
                    $insurance_member->current_insurance_amount_paid = $insurance_member->premium;
                }

                $insurance_member_account = allocate_insurance_member_account_number($insurance_member->id, $insurance_member->group_id);
                $insurance_member->member_account_number = $insurance_member_account;
                $insurance_member->update();

                //update the insurance_head_of_family table with the new member_id and their respective premium
                update_insurance_heads_of_family_record($insurance_member->family_id, $insurance_member->id, $insurance_member->premium);

                flash("Insurance Member has been saved")->success();
                return redirect("/insurance_members/");
            } catch (QueryException $e) {
                flash("Failed to register patient. Please try again")->error();
                return back()->withInput();
            }
        } else {
            flash("Failed to register patient. Please try again")->error();
            return back()->withInput();
        }
    }

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

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return Response
     */
    public function edit($id) {
        $insurance_groups = InsuranceGroup::orderBy('name', 'asc')
            ->pluck('name', 'id');

        $families = HeadOfFamily::orderBy('name', 'asc')
            ->pluck('name', 'id');

        $relations = FamilyRelationship::orderBy('name', 'asc')
            ->pluck('name', 'id');

        $insurance_member = InsuranceMember::where(['id' => $id])->first();

        $uploaded_photo = asset('uploads/images/insurance_members') . '/' . $insurance_member->photo;

        $person_titles = DB::table('person_titles')->whereNull('deleted_at')->pluck('name','id')->prepend('- Select Title - ', '');
        $chi_plans = DB::table('community_health_insurance_plans')->whereNull('deleted_at')->pluck('name','id')->prepend('- Select Plan - ', '');
        $chi_risks = DB::table('community_health_insurance_risks')->whereNull('deleted_at')->pluck('name','id')->prepend('None', '0');

        if (!$insurance_member) {
            flash()->error("There is no such Insurance Member");
            return redirect('/insurance_members/');
        } else {
            return view('insurance::insurance_members.edit', compact('insurance_groups', 'insurance_member', 'families', 'relations', 'uploaded_photo', 'person_titles', 'chi_plans', 'chi_risks'));
        }
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, $id) {
        $insurance_member = InsuranceMember::find($id);

        $logged_in_user_id = Auth::id();
        request()->validate([
            'duplicate_research_id' => 'nullable|required_with:research_id|same:research_id|unique:insurance_members,research_id,'.$id,
            'research_id' => 'nullable|required_with:research_id|same:research_id|unique:insurance_members,research_id,'.$id,
        ],[
            'duplicate_research_id.required_with' => 'Research ID field must be filled.',
            'duplicate_research_id.same' => 'Research ID values must be similar or identical.',
            'research_id.required_with' => 'Research ID field must be filled.',
            'research_id.same' => 'Research ID values must be similar or identical.',
            'research_id.unique' => 'Research ID already exists, please enter another one.',
            'duplicate_research_id.unique' => 'Research ID already exists, please enter another one.',
        ]);

        if ($request->hasFile('photo') && $request->file('photo') !== null) {
            $photo = $request->file('photo');
            $photo_name = $insurance_member->patient_id . "." . $photo->getClientOriginalExtension();
            $destinationPath = public_path('/uploads/images/insurance_members');
            $photoPath = $destinationPath . "/" . $photo_name;
            $photo->move($destinationPath, $photoPath);
            $insurance_member->photo = $photo_name;
        }

        if ($request->is_family_head == 1){
            $head_of_family = HeadOfFamily::find($request->family_id);

            if ($head_of_family) {
                $head_of_family->group_id = $request->group_id;
                $head_of_family->save();

                // change group for the rest of the family members + themselves
                $related_members = InsuranceMember::where('family_id', $request->family_id)->update([
                    "group_id" => $request->group_id
                ]);
            }
        }

        $insurance_member->family_id = $request->family_id;
        $insurance_member->relationship_to_head = $request->relationship_to_head;
        $insurance_member->title = $request->title;
        $insurance_member->premium = $request->member_premium;
        $insurance_member->chi_plan = $request->chi_plan;
        $insurance_member->other_plan = $request->other_plan;
        $insurance_member->risk_ids = is_null($request->risk_id) ? null : implode(",", $request->risk_id);
        $insurance_member->membership_start_date = $request->membership_start_date;
        $insurance_member->membership_registration_date = $request->membership_registration_date;
        $insurance_member->social_security_number = $request->social_security_number;
        $insurance_member->research_id = $request->research_id;
        $insurance_member->next_anniversary_date = $request->next_anniversary_date;
        $insurance_member->updated_by = $logged_in_user_id;

        try {
            $insurance_member->save();

            if ($request->has('does_member_have_existing_premium') && $request->does_member_have_existing_premium == 1 && !is_null($request->existing_premium_start_date)) {

                $existing_premium_start_date = $request->existing_premium_start_date;
                $existing_premium_end_date = $request->existing_premium_end_date;

                $insurance_member->existing_premium_start_date = $existing_premium_start_date;
                $insurance_member->existing_premium_end_date = $existing_premium_end_date;
                $insurance_member->save();

                $track_receipts = new TrackReceipt;
                $track_receipts->created_by = Auth::id();
                $track_receipts->reason = 'Premiums';
                $track_receipts->save();
                $receipt_number = sprintf("%04u", $track_receipts->id);

                add_member_to_insurance_subscription($insurance_member->group_id, $insurance_member->id, $insurance_member->premium,
                    $insurance_member->family_id, $existing_premium_start_date, $existing_premium_end_date, $receipt_number);

                $insurance_member->current_insurance_end_date = $existing_premium_end_date;
                $insurance_member->current_insurance_amount_paid = $insurance_member->premium;
                $insurance_member->update();
            }

            update_insurance_heads_of_family_record($insurance_member->family_id, $insurance_member->id, $insurance_member->premium);

            flash("Insurance Member has been updated")->success();
            return redirect("/insurance_members/");
        } catch (QueryException $e) {
            flash("An error occurred. Please try again later")->error();
            return back()->withInput();
        }
    }

    /**
     * Remove the specified resource from storage.
     *
     */
    public function destroy($id) {
        $insurance_member = InsuranceMember::find($id);
        /*
        1. double check to make sure that it is not a family head
        2. turn the patient record to insurance status = 0;
        3. remove them from the insurance_heads_of_family ie family_members_ids,family_member_premiums csv columns
        4. soft delete them from insurance_members table
        */
        if ($insurance_member) {
            if ($insurance_member->is_family_head == 0) {
                $patient_id = $insurance_member->patient_id;
                $patient = Patient::find($patient_id);
                $patient->insurance_status = 0;
                $patient->save();

                $family_details = HeadOfFamily::withTrashed()->find($insurance_member->family_id);
                if ($family_details) {
                    $family_members_ids_array = is_null($family_details->family_members_ids) ? [] : explode(",", $family_details->family_members_ids);
                    $family_member_premiums_array = is_null($family_details->family_member_premiums) ? [] : explode(",", $family_details->family_member_premiums);

                    if(array_search($insurance_member->id, $family_members_ids_array) !== false){
                        $key = array_search($insurance_member->id, $family_members_ids_array);
                        unset($family_members_ids_array[$key]);
                        unset($family_member_premiums_array[$key]);
                        //update family details after removing this member
                        $family_details->family_members_ids = is_null($family_members_ids_array) ? "" : implode(",", $family_members_ids_array);
                        $family_details->family_member_premiums = is_null($family_member_premiums_array) ? "" : implode(",", $family_member_premiums_array);
                        $family_details->update();
                    }
                }

                if ($insurance_member->delete()):
                    flash("Insurance Member ".get_full_name($patient_id, "id", "first_name", "last_name", "patients")." has been deleted from Community Health Insurance.")->success();
                    return redirect('/insurance_members/');
                endif;
            }

            flash("Insurance Member is a family head. Please make sure that the member is no longer the family head.")->info();
            return redirect('/insurance_members/');
        }

        flash("Insurance Member could not be found.")->warning();
        return redirect('/insurance_members/');
    }

    public function inactive() {
        $insurance_family = HeadOfFamily::pluck('name', 'id');

        $insurance_members = InsuranceMember::onlyTrashed()
            ->orderBy('id', 'desc')
            ->join('patients', 'insurance_members.patient_id', '=', 'patients.id')
            ->select('insurance_members.*', 'patients.number', 'patients.category_id')
            ->paginate(200);

        $insurance_groups_select = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();
        $insurance_groups_select = ['' => '- select -'] + $insurance_groups_select;

        $patient_categories = PatientCategory::pluck('name', 'id');

        $insurance_groups = InsuranceGroup::pluck('name', 'id');

        $relations = FamilyRelationship::orderBy('id', 'asc')->pluck('name', 'id');

        $full_names = DB::table('patients')->whereNull('deleted_at')
            ->select(DB::raw('CONCAT(first_name, " ", last_name, " - ", number, " - (", phone, ")") AS full_name'))
            ->pluck("full_name");

        return view('insurance::insurance_members.inactive', compact('insurance_members', 'patient_categories', 'full_names', 'insurance_groups', 'insurance_family', 'relations', 'insurance_groups_select'));
    }

    public function searchInactive(Request $request){
        $insurance_family = HeadOfFamily::orderBy('name', 'asc')->pluck('name', 'id')->toArray();

        $patient_categories = PatientCategory::pluck('name', 'id');

        $insurance_groups = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();
        $insurance_groups_select = ['' => '- select -'] + $insurance_groups;

        $relations = FamilyRelationship::pluck('name', 'id');

        $criteria = '';
        $filters = [];
        $full_name_search = $request->full_name;

        if ($full_name_search) {
            $split_full_name_array = explode(' ', $full_name_search);

            if (is_array($split_full_name_array)) {
                $first_name_from_split = $split_full_name_array[0] ?? "";
                $last_name_from_split = $split_full_name_array[1] ?? "";
                $patient_number_from_split = count($split_full_name_array) > 2 ? array_slice($split_full_name_array, -3)[0] : '';

                //just only use the patient number from the full name submitted
                if ($patient_number_from_split != "") {
                    $filters[] = ['number', 'LIKE', '%' . $patient_number_from_split . '%'];
                    $criteria .= 'Number (' . $patient_number_from_split . ') ';
                } else {
                    $filters[] = ['first_name', 'LIKE', '%' . $first_name_from_split . '%'];
                    $filters[] = ['last_name', 'LIKE', '%' . $last_name_from_split . '%'];
                    $criteria .= 'Name (' . $full_name_search . ') ';
                }
            } else {
                //just only use the patient number from the full name submitted
                $filters[] = ['first_name', 'LIKE', '%' . $full_name_search . '%'];
                $criteria .= 'Name (' . $full_name_search . ') ';
            }
        }

        if ($request->patient_id != 0){
            $filters[] = ['insurance_members.patient_id', '=', $request->patient_id];
            $criteria .= 'Search by patient ';
        }

        if ($request->group_id) {
            $filters[] = ['insurance_members.group_id', '=', $request->group_id];
            $criteria .= 'Insurance Group ('. ($insurance_groups[$request->group_id] ?? 'N/A') .') ';
        }

        $insurance_members = InsuranceMember::onlyTrashed()
            ->join('patients', 'insurance_members.patient_id', '=', 'patients.id')
            ->select('insurance_members.*', 'patients.number', 'patients.category_id')
            ->where($filters)
            ->paginate(200);

        $full_names = DB::table('patients')->whereNull('deleted_at')
            ->select(DB::raw('CONCAT(first_name, " ", last_name, " - ", number, " - (", phone, ")") AS full_name'))
            ->pluck("full_name");

        return view('insurance::insurance_members.inactive', compact('insurance_members', 'patient_categories', 'insurance_groups', 'insurance_family', 'relations', 'insurance_groups_select', 'criteria', 'full_names'));
    }

    /**
     * Activate the specified resource in storage.
     *
     * @param  int  $id
     * @return Response
     */
    public function activate($id) {
        $insurance_member = InsuranceMember::withTrashed()->find($id);

        $patient_id = $insurance_member->patient_id;
        $patient = Patient::find($patient_id);
        $patient->insurance_status = 1;
        $patient->save();

        if ($insurance_member->restore()):
            flash("Insurance Member has been activated.")->success();
            return redirect('/insurance_members/');
        endif;
    }

    public function update_patient_info($id) {
        $patient = Patient::where('patients.id', $id)
            ->leftJoin('insurance_members', 'insurance_members.patient_id', '=', 'patients.id')
            ->first(['patients.id', 'patients.first_name', 'patients.last_name', 'patients.phone', 'patients.next_of_kin',
                'patients.phone_of_next_of_kin', 'patients.category_id', 'patients.village_id', 'patients.gender', 'patients.date_of_birth', 'patients.number',
                'insurance_members.id as insured_id', 'insurance_members.group_id', 'insurance_members.family_id']);

        if ($patient) {
            $first_name = title_case($patient->first_name);

            $last_name = title_case($patient->last_name);

            if (is_null($patient->insured_id)) {
                $code = '<p><b>Patient Names:</b> ' . $first_name . ' ' . $last_name . '</p>';

                $code .= '<p><b>Patient Number:</b> ' . $patient->number . '</p>';

                $code .= '<p><b>Gender:</b> ' . ($patient->gender == 1 ? "Male" : "Female"). ' &nbsp;&nbsp;&nbsp;<b>Date of Birth:</b> ' . streamline_date($patient->date_of_birth) . '</p>';

                $code .= '<p><b>Phone Number:</b> ' . $patient->phone . ' &nbsp;&nbsp;&nbsp;<b>Next of Kin:</b> ' . $patient->next_of_kin . ' ('.$patient->phone_of_next_of_kin. ')</p>';

                $code .= '<p><b>Patient Category:</b> ' . get_name($patient->category_id, 'id', 'name', 'patient_categories') . '</p>';

                $code .= '<p><b>Village:</b> ' . get_name($patient->village_id, 'id', 'name', 'villages') . '</p>';
            } else {
                $code = '<h3 style="color: red"><b>' . $first_name . ' ' . $last_name . '</b> is already a C.H.I Member belonging to the family of <b>' . get_name($patient->family_id,"id", "name", "insurance_heads_of_family"). '</b> of group <b>'. get_name($patient->group_id,"id", "name", "insurance_groups").'</b></h3>';
            }
        } else {
            $code = '<h3 style="color: red"><b>Patient Not Found</b></h3>';
        }

        return $code;
    }

    public function details($id) {

        $result = InsuranceMember::where('id', $id)->first();

        $patient_relationship = get_name($result->relationship_to_head, 'id', 'name', 'family_relations');

        $family_id = $result->family_id;
        $research_id = $result->research_id;

        $head_of_family_details = HeadOfFamily::find($family_id);
        $head_of_family = $head_of_family_details->name;

        $group_id = $result->group_id;

        $group_name = InsuranceGroup::where('id', $group_id)->value('name');

        $start_date = DB::table('insurance_subscriptions')->where('group_id', $group_id)->value('start_date');

        $end_date = DB::table('insurance_subscriptions')->where('group_id', $group_id)->value('end_date');

        $member_photo = asset('uploads/images/insurance_members') . '/' . $result->photo;
        $head_photo = asset('uploads/images/insurance_members') . '/' . DB::table('insurance_members')->where('patient_id', $head_of_family_details->patient_id)->value('photo');

        $patient_id = $result->patient_id;

        $result2 = DB::table('patients')->where('id', $patient_id)->first();

        $patient_number = $result2->number;

        return view('insurance::insurance_members.details', compact('patient_number', 'patient_id', 'patient_relationship', 'research_id','head_of_family', 'group_name', 'start_date', 'end_date', 'member_photo', 'head_photo', 'group_id', 'family_id', 'id'));
    }

    public function add_existing_patient_to_insurance(){
        $insurance_groups = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();

        $insurance_groups = ['' => '- select -'] + $insurance_groups;

        $families = HeadOfFamily::orderBy('name', 'asc')->pluck('id')->toArray();

        $relations = FamilyRelationship::orderBy('id', 'asc')->pluck('name', 'id')->toArray();

        $relations = ['' => '- select -'] + $relations;

        $person_titles = DB::table('person_titles')->whereNull('deleted_at')->pluck('name','id')->prepend('- Select Title - ', '');
        $chi_plans = DB::table('community_health_insurance_plans')->whereNull('deleted_at')->pluck('name','id')->prepend('- Select Plan - ', '');
        $chi_risks = DB::table('community_health_insurance_risks')->whereNull('deleted_at')->pluck('name','id')->prepend('None', '0');

        return view('insurance::insurance_members.add_existing_patient_to_insurance', compact('insurance_groups', 'families', 'relations', 'person_titles', 'chi_plans', 'chi_risks'));
    }

    public function create_insurance_patient(Request $request){
        request()->validate([
            'group_id' => 'required',
            'patient_id' => 'required|unique:insurance_members,patient_id',
            'duplicate_research_id' => 'nullable|required_with:research_id|same:research_id|unique:insurance_members,research_id',
            'research_id' => 'nullable|required_with:duplicate_research_id|same:duplicate_research_id|unique:insurance_members'
        ], [
            'duplicate_research_id.required_with' => 'Research ID field must be filled.',
            'duplicate_research_id.same' => 'Research ID values must be similar or identical.',
            'research_id.required_with' => 'Research ID field must be filled.',
            'research_id.same' => 'Research ID values must be similar or identical.',
            'research_id.unique' => 'Research ID already exists, please enter another one.',
            'patient_id.unique' => 'Patient is already insured, please add another one.',
            'duplicate_research_id.unique' => 'Research ID already exists, please enter another one.',
        ]);

        // save the patient
        $patient = Patient::find($request->patient_id);
        $patient->insurance_status = 1;
        $patient->updated_by = Auth::id();
        $patient->save();

        // check if member is already registered
        $member = DB::table('insurance_members')->where('patient_id', $patient->id)
            ->first();

        if ($member) {
            flash("Insurance Member already exists!")->error();
            return back()->withInput();
        }

        // add patient to insurance
        $insurance_member = new InsuranceMember;

        if ($request->hasFile('photo')){
            $photo = $request->file('photo');
            $photo_name = $request->patient_id . "." . $photo->getClientOriginalExtension();
            $destinationPath = public_path('/uploads/images/insurance_members');
            $photoPath = $destinationPath . "/" . $photo_name;
            $photo->move($destinationPath, $photoPath);
            $insurance_member->photo = $photo_name;
        } else {
            $insurance_member->photo = 'placeholder.png';
        }

        $logged_in_user_id = Auth::id();

        if ($request->is_family_head == 1){
            $head_of_family = new HeadOfFamily;
            $head_of_family->name = $patient->first_name . " " . $patient->last_name;
            $head_of_family->group_id = $request->group_id;
            $head_of_family->patient_id = $patient->id;
            $head_of_family->risk_ids = is_null($request->family_risk_id) ? null : implode(",", $request->family_risk_id);
            $head_of_family->created_by = $logged_in_user_id;
            $head_of_family->updated_by = $logged_in_user_id;
            $head_of_family->save();

            $insurance_member->family_id = $head_of_family->id;
        } else {
            $insurance_member->family_id = $request->family_id;
        }

        $insurance_member->group_id = $request->group_id;
        $insurance_member->relationship_to_head = $request->relationship_to_head;
        $insurance_member->patient_id = $patient->id;
        $insurance_member->is_family_head = $request->is_family_head;
        $insurance_member->title = $request->title;
        $insurance_member->premium = $request->member_premium;
        $insurance_member->chi_plan = $request->chi_plan;
        $insurance_member->other_plan = $request->other_plan;
        $insurance_member->risk_ids = is_null($request->risk_id) ? null : implode(",", $request->risk_id);
        $insurance_member->membership_start_date = is_null($request->membership_start_date) ? null : Carbon::createFromFormat('d/m/Y', $request->membership_start_date)->toDateString();
        $insurance_member->membership_registration_date = is_null($request->membership_registration_date) ? null : Carbon::createFromFormat('d/m/Y', $request->membership_registration_date)->toDateString();
        $insurance_member->social_security_number = $request->social_security_number;
        $insurance_member->research_id = $request->research_id;
        $insurance_member->next_anniversary_date = is_null($request->next_anniversary_date) ? null : Carbon::createFromFormat('d/m/Y', $request->next_anniversary_date)->toDateString();
        $insurance_member->created_by = $logged_in_user_id;
        $insurance_member->updated_by = $logged_in_user_id;

        try {

            $insurance_member->save();

            if ($request->has('does_member_have_existing_premium') && $request->does_member_have_existing_premium == 1 && !is_null($request->existing_premium_start_date)) {

                $existing_premium_start_date = is_null($request->existing_premium_start_date) ? null : Carbon::createFromFormat('d/m/Y', $request->existing_premium_start_date)->toDateString();
                $existing_premium_end_date = is_null($request->existing_premium_end_date) ? null : Carbon::createFromFormat('d/m/Y', $request->existing_premium_end_date)->toDateString();

                $insurance_member->existing_premium_start_date = $existing_premium_start_date;
                $insurance_member->existing_premium_end_date = $existing_premium_end_date;

                $track_receipts = new TrackReceipt;
                $track_receipts->created_by = Auth::id();
                $track_receipts->reason = 'Premiums';
                $track_receipts->save();
                $receipt_number = sprintf("%04u", $track_receipts->id);

                add_member_to_insurance_subscription($insurance_member->group_id, $insurance_member->id, $insurance_member->premium,
                    $insurance_member->family_id, $existing_premium_start_date, $existing_premium_end_date, $receipt_number);

                $insurance_member->current_insurance_end_date = $existing_premium_end_date;
                $insurance_member->current_insurance_amount_paid = $insurance_member->premium;
            }

            $insurance_member_account = allocate_insurance_member_account_number($insurance_member->id, $insurance_member->group_id);
            $insurance_member->member_account_number = $insurance_member_account;
            $insurance_member->update();

            //update the insurance_head_of_family table with the new member_id and their respective premium
            update_insurance_heads_of_family_record($insurance_member->family_id, $insurance_member->id, $insurance_member->premium);

            flash("Insurance Member has been saved")->success();
            return redirect("/insurance_members/");
        } catch (QueryException $e) {
            flash("An error occurred")->error();
            return back()->withInput();
        }
    }

    public function pre_existing_premiums_report(Request $request)
    {
        $search_text = "";
        if($request->start_date){
            $start = Carbon::createFromFormat('d/m/Y', $request->start_date)->toDateTimeString();
            $end = Carbon::createFromFormat('d/m/Y', $request->end_date)->toDateTimeString();
        } else {
            $start = Carbon::now()->startOfDay()->toDateTimeString();
            $end = Carbon::now()->endOfDay()->toDateTimeString();
        }

        $search_text = "Searching between " . streamline_date($start) . " and " . streamline_date($end);

        $pre_insurance_members = InsuranceMember::whereNotNull('existing_premium_start_date')->whereBetween('created_at', [$start, $end])->get();

        return view('insurance::insurance_members.pre_existing_premiums_report', compact('pre_insurance_members', 'search_text'));
    }

    public function update_insurance_heads_of_family_table_with_family_details()
    {
        $group_families = HeadOfFamily::all();
        if(count($group_families) > 0){
            foreach($group_families as $family){
                $family_insurance_members = InsuranceMember::where('family_id', $family->id)->get();
                $each_member_ids_array = $each_member_premiums_array = [];
                if (count($family_insurance_members) > 0) {
                    foreach ($family_insurance_members as $member) {
                        $each_member_ids_array[] = $member->id;
                        $each_member_premiums_array[] = $member->premium;
                    }
                }

                //update the heads of family records
                $head_of_family_record = HeadOfFamily::withTrashed()->find($family->id);
                if ($head_of_family_record) {
                    $head_of_family_record->family_members_ids = implode(",", $each_member_ids_array);
                    $head_of_family_record->family_member_premiums = implode(",", $each_member_premiums_array);
                    $head_of_family_record->update();
                }
            }
        }

        return "insurance heads of family table has been updated with family members and premiums details";
    }

    public function get_chi_plan_amount($chi_plan_id)
    {
        $chi_plan = CommunityHealthInsurancePlan::find($chi_plan_id);
        if ($chi_plan) {
            return $chi_plan->plan_fee;
        }
        return 0;
    }

    public function get_family_group_id($family_id)
    {
        $insurance_heads_of_family = HeadOfFamily::find($family_id);
        if ($insurance_heads_of_family) {
            return $insurance_heads_of_family->group_id;
        }
        return null;
    }

    public function print_insurance_cards(Request $request){
        $ids = $request->checked_members ?? [];

        $insurance_members = InsuranceMember::join('patients', 'patients.id', '=', 'insurance_members.patient_id')->leftJoin('patient_categories','patient_categories.id','=','patients.category_id')
            ->whereIn('insurance_members.id', $ids)->select(['insurance_members.photo', 'insurance_members.group_id', 'patients.gender', 'patients.date_of_birth', 'patients.first_name',
                'patients.number', 'patients.national_id', 'patients.village_id', 'patients.district_id', 'patients.last_name', 'insurance_members.patient_id','patient_categories.name as category_name'])->get();

        $hospital_details = HospitalInformation::first();

        $data = [
            'hospitalInfo' => $hospital_details,
            'insurance_members' => $insurance_members,
        ];

        $pdf = SnappyPDF::loadView('insurance::insurance_members.print_membership_cards', $data)
            ->setOrientation('portrait')
            ->setOption('margin-bottom', 5)
            ->setOption('margin-top', 5)
            ->setOption('margin-left', 5)
            ->setOption('margin-right', 5);
        return $pdf->inline('CHI Cards' . date(" d-m-y h:ia") . '.pdf');
    }

    public function search_insured_patient_by_name_or_number(Request $request) {
        $data = [];

        if ($request->has('q')) {
            $search = $request->q;
            $data = InsuranceMember::leftjoin('patients', 'patients.id', '=', 'insurance_members.patient_id')->select("patients.id", "first_name", "last_name", "number", "phone")
                ->where('last_name', 'LIKE', "%$search%")
                ->orWhere('first_name', 'LIKE', "%$search%")
                ->orWhere('number', 'LIKE', "%$search%")
                ->get();
        }

        return response()->json($data);
    }
}