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

<?php

namespace Modules\Reports\Http\Controllers;

use Carbon\Carbon;
use DateTime;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Streamline\Models\AgeGroup;
use Streamline\Models\Clinic;
use Streamline\Models\Diagnosis;
use Streamline\Models\Drug;
use Streamline\Models\HospitalInformation;
use Streamline\Models\InpatientInfo;
use Streamline\Models\InsuranceExpenditure;
use Streamline\Models\InvestigationDeposit;
use Streamline\Models\Consultation;
use Streamline\Models\OrderedInvestigation;
use Streamline\Models\Patient;
use Streamline\Models\ProcedureDeposit;
use Streamline\Models\ServiceDeposit;
use Streamline\Models\TreatmentDeposits;
use Streamline\Models\Triage;
use Streamline\Models\Ward;
use Streamline\Models\HmisWard;
use Streamline\Models\HmisCategory;
use Streamline\Models\HmisCategoryOptions;
use Illuminate\Support\Facades\Validator;
use Barryvdh\Snappy\Facades\SnappyPdf;
use Streamline\Models\WardProcedure;

class HmisController extends Controller
{

    /**
     * Health Unit weekly Epidemiological surveillance form 033b
     * fetch hospital information from the database and pass it to the view
     */
    public function weekly_epidemiological_surveillance_surveillance_form(Request $request)
    {
        $hospital_information = HospitalInformation::find(1); $search_button ='';

        if (isset($request->date_from) && isset($request->date_to)) {
            $attendance_from = Carbon::createFromFormat('d/m/Y', $request->date_from)->startOfDay()->toDateString();
            $attendance_to   = Carbon::createFromFormat('d/m/Y', $request->date_to)->endOfDay()->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");
        }

        $flu_id   = get_name("Influenza", "name", "id", "diagnoses");
        $ebola_id = get_name("Ebola Fever", "name", "id", "diagnoses");
        //$death_pox_id = 0;
        $case_pox          = 0;
        $case_flu          = $cma = DB::table('consultations')->where('primary_diagnosis', $flu_id)->whereBetween('created_at', [$attendance_from, $attendance_to])->count();
        $case_ebola        = DB::table('consultations')->where('primary_diagnosis', $ebola_id)->whereBetween('created_at', [$attendance_from, $attendance_to])->count();
        $death_pox         = 0;
        //outcome_id = 5; to get death cases
        $death_flu          = DB::table('consultations')->where('primary_diagnosis', $flu_id)->where('outcome_id', 5)->whereBetween('created_at', [$attendance_from, $attendance_to])->count();
        $death_ebola        = DB::table('consultations')->where('primary_diagnosis', $ebola_id)->where('outcome_id', 5)->whereBetween('created_at', [$attendance_from, $attendance_to])->count();
        /* used from form 5 to 8 */
        $malaria_id = get_name("6A", "hmis_no_inpatient", "id", "diagnoses");
        $mat_one    = DB::table('consultations')->where('primary_diagnosis', $malaria_id)->whereBetween('created_at', [$attendance_from, $attendance_to])->count();
        // DB::enableQueryLog();
        $mat_two    = DB::table('consultations')->whereNotNull('rdt')->whereBetween('created_at', [$attendance_from, $attendance_to])->count();
        $mat_three  = DB::table('consultations')->where('rdt', 1)->whereBetween('created_at', [$attendance_from, $attendance_to])->count();
        $mat_four   = DB::table('investigations')->where('name', 'Stool Microscopy')->count();
        $mat_five   = 0;
        $mat_six    = 0;
        $mat_seven  = DB::table('treatments')->leftJoin('consultations', 'treatments.episode_id', '=', 'consultations.episode_id')
            ->where('consultations.rdt', 0) //0-negative
            ->whereBetween('consultations.created_at', [$attendance_from, $attendance_to])
            ->count();
        $mat_eight  = DB::table('treatments')->leftJoin('consultations', 'treatments.episode_id', '=', 'consultations.episode_id')
            ->where('consultations.rdt', 1) //1-positive
            ->whereBetween('consultations.created_at', [$attendance_from, $attendance_to])
            ->count();
        $mat_nine      = 0;
        $mat_ten       = 0;
        $tra_one       = 0;
        $tra_two       = is_numeric(get_name("ORS Sachets", "name", "store_stock", "drugs")) ? get_name("ORS Sachets", "name", "store_stock", "drugs") + get_name("ORS Sachets", "name", "pharmacy_stock", "drugs") : 0;
        $tra_three     = 0;
        $tra_four      = is_numeric(get_name("Amoxicilin 250mg cap", "name", "store_stock", "drugs")) ? get_name("Amoxicilin 250mg cap", "name", "store_stock", "drugs") + get_name("Amoxicilin 250mg cap", "name", "pharmacy_stock", "drugs") + get_name("Amoxycilin 125mg/5mL 100ml susp", "name", "store_stock", "drugs") + get_name("Amoxycilin 125mg/5mL 100ml susp", "name", "pharmacy_stock", "drugs") : 0;
        $tra_five      = 0;
        $tra_six       = 0;
        $tra_seven     = is_numeric(get_name("Fansidar 525mg  tab", "name", "store_stock", "drugs")) ? get_name("Fansidar 525mg  tab", "name", "store_stock", "drugs") + get_name("Fansidar 525mg  tab", "name", "pharmacy_stock", "drugs") : 0;
        $tra_eight     = 0;
        $arv_one       = 0;
        $arv_two       = 0;
        $arv_three     = 0;

        return view('reports::hmis_reports.weekly_epidemiological_surveillance_form', compact('hospital_information', 'search_button', 'attendance_from', 'attendance_to', 'case_pox', 'case_flu', 'case_ebola', 'death_flu', 'death_ebola', 'death_pox', 'mat_one', 'mat_two', 'mat_three', 'mat_four', 'mat_five', 'mat_six', 'mat_seven', 'mat_eight', 'mat_nine', 'mat_ten', 'tra_one', 'tra_two', 'tra_three', 'tra_four', 'tra_five', 'tra_six', 'tra_seven', 'tra_eight', 'arv_one', 'arv_two', 'arv_three'));
    }

    /**
     * * Get details of a weekly epidemiliological cases report for specific period of time
     */
    public function weekly_epidemiological_surveillance_cases_details($diagnosis_id)
    {
        $attendance_from              = session()->get('attendance_from');
        $attendance_to                = session()->get('attendance_to');
        $weekly_cases_details_sql     = "SELECT * FROM consultations WHERE primary_diagnosis = {$diagnosis_id} AND created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}'";
        $weekly_cases_details_results = DB::select($weekly_cases_details_sql);
        return view('reports::hmis_reports.weekly_epidemiological_surveillance_cases_details', compact('attendance_from', 'attendance_to', 'weekly_cases_details_results'));
    }

    /**
     * * Get details of a weekly epidemiliological deaths report for specific period of time
     */
    public function weekly_epidemiological_surveillance_deaths_details($diagnosis_id)
    {
        //outcome_id = 5 stands for died
        $attendance_from              = session()->get('attendance_from');
        $attendance_to                = session()->get('attendance_to');
        $weekly_cases_details_sql     = "SELECT * FROM consultations WHERE primary_diagnosis = {$diagnosis_id} AND outcome_id=5 AND created_at BETWEEN '{$attendance_from}' AND '{$attendance_to}'";
        $weekly_cases_details_results = DB::select($weekly_cases_details_sql);
        return view('reports::hmis_reports.weekly_epidemiological_surveillance_cases_details', compact('attendance_from', 'attendance_to', 'weekly_cases_details_results'));
    }

    /* Generate the essential medicines and health supplies report
     * */

    public function essential_medicines_report(Request $request)
    {
        $hospital_information = HospitalInformation::find(1);

        if (isset($request->date_from) && isset($request->date_to)) {
            $from_date = Carbon::createFromFormat('d/m/Y', $request->date_from)->toDateString();
            $to_date   = Carbon::createFromFormat('d/m/Y', $request->date_to)->toDateString();
        } else {
            $date = date_create(date("d-m-Y"));
            date_sub($date, date_interval_create_from_date_string("29 days"));
            $startDate = date_format($date, "d-m-Y");
            $from_date = date_format($date, "Y-m-d");
            $to_date   = date("Y-m-d");
        }
        $issued_drugs_sql           = "SELECT * FROM requisitions WHERE quotation_type_id = 1 AND issue_status = 1 AND issued_on BETWEEN '{$from_date}' AND '{$to_date}'";
        $issued_drugs_results       = DB::select($issued_drugs_sql);
        $hssip_indicator_sql        = "SELECT * FROM drugs WHERE deleted_at IS NUll AND hssip !='' AND hssip < 'S07' ORDER BY hssip ASC";
        $hssip_indicator_results    = DB::select($hssip_indicator_sql);
        $other_programmatic_sql     = "SELECT * FROM drugs WHERE deleted_at IS NULL AND hssip > 'S06' AND hssip < 'S42' ORDER BY hssip ASC ";
        $other_programmatic_results = DB::select($other_programmatic_sql);

        $other_drugs_sql     = "SELECT i.id, i.name, s.out_of_stock_date FROM drugs i, inventory_stock_dates s WHERE i.id = s.item_id AND s.out_of_stock_date BETWEEN '{$from_date}' AND '{$to_date}' AND i.deleted_at IS NULL AND i.hssip = '' ORDER BY i.name ASC";
        $other_drugs_results = DB::select($other_drugs_sql);
        $drug_row_array      = [];
        $drug_count          = 0;
        $half_count          = 0;
        if (count($other_drugs_results) > 0) {
            $drug_count = count($other_drugs_results);
            $half_count = (int) ($drug_count / 2);
            foreach ($other_drugs_results as $other_drug) {
                $drug_row_array[] = array("item_id" => $other_drug->id, "item_name" => $other_drug->name);
            }
        }

        return view('reports::hmis_reports.essential_medicines_report', compact('hospital_information', 'issued_drugs_results', 'hssip_indicator_results', 'other_programmatic_results', 'from_date', 'to_date', 'drug_count', 'half_count', 'drug_row_array'));
    }

    /**
     * Generate out patient register report (HMIS 031) - page 1
     * */
    public function out_patient_register_report(Request $request)
    {
        $hospital_information = HospitalInformation::find(1);
        $filtering_date       = isset($request->filter_date) ? Carbon::createFromFormat('d/m/Y', $request->filter_date)->toDateString() : date('Y-m-d');
        $episodes_query       = "SELECT e.id, e.triage_id, e.patient_id, d.first_name, d.last_name, d.gender, d.date_of_birth, ds.name as district_name, p.name as parish_name, v.name as village_name, s.name as subcounty_name, d.county_id, d.next_of_kin, t.observations, t.new_attendance, t.re_attendance FROM patient_episodes e, patients d, villages v, parishes p, subcounties s, districts ds, triage t  WHERE e.created_at LIKE '{$filtering_date}%' AND e.patient_id = d.id AND d.village_id = v.id AND d.parish_id = p.id AND d.subcounty_id = s.id AND d.district_id = ds.id AND e.id = t.episode_id";
        $episodes_results     = DB::select($episodes_query);

        return view('reports::hmis_reports.002.out_patient_register_report', compact('hospital_information', 'filtering_date', 'episodes_results'));
    }

    public function out_patient_register_report_2(Request $request)
    {
        $hospital_information = HospitalInformation::find(1);
        $filtering_date       = isset($request->filter_date) ? Carbon::createFromFormat('d/m/Y', $request->filter_date)->toDateString() : date('Y-m-d');

        $episodes_query   = "SELECT e.id, e.triage_id, e.patient_id, d.first_name, d.last_name, d.gender, d.date_of_birth, ds.name as district_name, p.name as parish_name, v.name as village_name, s.name as subcounty_name, d.county_id, d.next_of_kin, t.observations, t.new_attendance, t.re_attendance FROM patient_episodes e, patients d, villages v, parishes p, subcounties s, districts ds, triage t  WHERE e.created_at LIKE '{$filtering_date}%' AND e.patient_id = d.id AND d.village_id = v.id AND d.parish_id = p.id AND d.subcounty_id = s.id AND d.district_id = ds.id AND e.id = t.episode_id";
        $episodes_results = DB::select($episodes_query);

        return view('reports::hmis_reports.002.out_patient_register_report_2', compact('hospital_information', 'filtering_date', 'episodes_results'));
    }

    /**
     * View expired drugs in the system
     *
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function expired_drugs_report()
    {
        $drugs = Drug::where('expiry_date', '<', date('Y-m-d'))
            ->where('pharmacy_stock', '>', '0')
            ->orderBy('name', 'asc')
            ->get();

        return view('reports::hmis_reports.expired_drugs_report', compact('drugs'));
    }

    /**
     * View drug stock levels in the system
     *
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function drug_stock_level()
    {
        $drugs = Drug::orderBy('name', 'asc')->get();

        return view('reports::hmis_reports.drug_stock_level', compact('drugs'));
    }

    /**
     * View drug consumption in the system with financial metrics
     *
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function drug_consumption_report(Request $request)
    {
        // check for date search
        if (!is_null($request->start_date) && !is_null($request->end_date)) {
            $start_date = $request->start_date;
            $end_date   = $request->end_date;
        } else {
            $start_date = date('Y-m-d');
            $end_date   = date('Y-m-d');
        }

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

        // prep the dates for search queries
        $start_date_clean = Carbon::parse($start_date)->startOfDay()->toDateTimeString();
        $end_date_clean   = Carbon::parse($end_date)->endOfDay()->toDateTimeString();

        // get drugs dispensed to patients in opd
        $opd_drugs = DB::table('patient_dispensings')->where([['created_at', '>', $start_date_clean], ['created_at', '<', $end_date_clean]])->select('drugs', 'quantity_dispensed')->get();

        foreach ($opd_drugs as $drug) {
            $drugs     = explode(",", $drug->drugs);
            $dispensed = explode(",", $drug->quantity_dispensed);

            // loop through the drugs
            for ($i = 0; $i < count($drugs); $i++) {
                // check if a drug id already exists in the drugs array
                if (in_array($drugs[$i], $drugs_array)) {
                    //get key of that drug which exists
                    $key = array_search($drugs[$i], $drugs_array);

                    //increment the dispensed value of said drug
                    $clean_dispensed = (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                    if (is_numeric($dispensed_array[$key])) {
                        $dispensed_array[$key] += $clean_dispensed;
                    } else {
                        $dispensed_array[$key] = $clean_dispensed;
                    }
                    $dispensed_array[$key] += $clean_dispensed;
                } else {
                    //drugs does not exist, create new entry
                    array_push($drugs_array, $drugs[$i]);
                    array_push($dispensed_array, $dispensed[$i]);
                }
            }
        }

        $ward_drugs = DB::table('ward_dispensings')->where([['created_at', '>', $start_date_clean], ['created_at', '<', $end_date_clean]])->select('drug_id', 'quantity_dispensed')->get();

        foreach ($ward_drugs as $drug) {
            $drugs     = explode(",", $drug->drug_id);
            $dispensed = explode(",", $drug->quantity_dispensed);

            // loop through the drugs
            for ($i = 0; $i < count($drugs); $i++) {
                // check if a drug id already exists in the drugs array
                if (in_array($drugs[$i], $drugs_array)) {
                    //get key of that drug which exists
                    $key = array_search($drugs[$i], $drugs_array);

                    //increment the dispensed value of said drug
                    $clean_dispensed = (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                    if (is_numeric($dispensed_array[$key])) {
                        $dispensed_array[$key] += $clean_dispensed;
                    } else {
                        $dispensed_array[$key] = $clean_dispensed;
                    }
                } else {
                    //drugs does not exist, create new entry
                    array_push($drugs_array, $drugs[$i]);
                    array_push($dispensed_array, $dispensed[$i]);
                }
            }
        }

        return view('reports::hmis_reports.drug_consumption_report', compact('start_date', 'end_date', 'drugs_array', 'dispensed_array'));
    }

    /**
     * View drug consumption in the last year
     */
    public function drug_consumption_analysis()
    {
        // get all drugs in the system
        $drugs = Drug::select('id')->get();

        //drugs ids
        $drugs_array = [];

        foreach ($drugs as $drug) {
            // insert drug id into array
            $drugs_array[] = $drug->id;
        }

        // get total of all drugs in the array
        $drugs_array_total = count($drugs_array);

        //initialize month arrays with count of drugs total
        $january   = array_fill(0, $drugs_array_total, 0);
        $february  = array_fill(0, $drugs_array_total, 0);
        $march     = array_fill(0, $drugs_array_total, 0);
        $april     = array_fill(0, $drugs_array_total, 0);
        $may       = array_fill(0, $drugs_array_total, 0);
        $june      = array_fill(0, $drugs_array_total, 0);
        $july      = array_fill(0, $drugs_array_total, 0);
        $august    = array_fill(0, $drugs_array_total, 0);
        $september = array_fill(0, $drugs_array_total, 0);
        $october   = array_fill(0, $drugs_array_total, 0);
        $november  = array_fill(0, $drugs_array_total, 0);
        $december  = array_fill(0, $drugs_array_total, 0);

        // lets start a fairy tale
        $one_year_ago_today = Carbon::now()->subYear();

        // get drugs dispensed to wards
        $ward_drugs = DB::table('ward_dispensings')->where([['created_at', '>', $one_year_ago_today]])->select('drug_id', 'quantity_dispensed', 'created_at')->get();

        foreach ($ward_drugs as $drug) {
            // get month in which drug was dispensed
            $month = (int) Carbon::parse($drug->created_at)->format('m');

            $drugs     = explode(",", $drug->drug_id);
            $dispensed = explode(",", $drug->quantity_dispensed);

            // loop through the drugs
            for ($i = 0; $i < count($drugs); $i++) {
                if (in_array($drugs[$i], $drugs_array)) {
                    //get key of that drug which exists
                    $key = array_search($drugs[$i], $drugs_array);

                    //update month quantity according to date returned
                    switch ($month) {
                        case 1:
                            $january[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 2:
                            $february[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 3:
                            $march[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 4:
                            $april[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 5:
                            $may[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 6:
                            $june[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 7:
                            $july[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 8:
                            $august[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 9:
                            $september[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 10:
                            $october[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 11:
                            $november[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 12:
                            $december[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        default:
                            # code...
                            break;
                    }
                }
            }
        }

        // get drugs dispensed to patients in opd
        $opd_drugs = DB::table('patient_dispensings')->where([['created_at', '>', $one_year_ago_today]])->select('drugs', 'quantity_dispensed', 'created_at')->get();

        foreach ($opd_drugs as $drug) {
            // get month in which drug was dispensed
            $month = (int) Carbon::parse($drug->created_at)->format('m');

            $drugs     = explode(",", $drug->drugs);
            $dispensed = explode(",", $drug->quantity_dispensed);

            // loop through the drugs
            for ($i = 0; $i < count($drugs); $i++) {
                if (in_array($drugs[$i], $drugs_array)) {
                    //get key of that drug which exists
                    $key = array_search($drugs[$i], $drugs_array);

                    //update month quantity according to date returned
                    switch ($month) {
                        case 1:
                            $january[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 2:
                            $february[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 3:
                            $march[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 4:
                            $april[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 5:
                            $may[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 6:
                            $june[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 7:
                            $july[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 8:
                            $august[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 9:
                            $september[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 10:
                            $october[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 11:
                            $november[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        case 12:
                            $december[$key] += (isset($dispensed[$i]) && is_numeric($dispensed[$i])) ? $dispensed[$i] : 0;
                            break;
                        default:
                            # code...
                            break;
                    }
                }
            }
        }
        $data = compact('drugs_array', 'january', 'february', 'march', 'april',
            'may', 'june', 'july', 'august', 'september', 'october', 'november', 'december');

        return view('reports::hmis_reports.drug_consumption_analysis', $data);
    }

    public function streamline_outpatient_report(Request $request)
    {
        set_time_limit(0);
        $hospital_information           = HospitalInformation::find(1);
        $clinics                        = Clinic::orderBy('name')->pluck('name', 'id')->prepend('OPD', 0)->prepend('-- Select --', '');
        $age                            = AgeGroup::pluck('name', 'id')->toArray();
        $age = ['' => '- select -'] + $age;
        $patient_array                  = [];
        $patient_array_adults           = [];
        $patient_array_males            = [];
        $patient_array_females          = [];
        $patient_array_admitted         = [];
        $patient_array_children         = [];
        $patient_array_children_males   = [];
        $patient_array_children_females = [];
        $patient_array_age              = [];
        $criteria                       = '';
        $filters                        = [];

        if (isset($request->date_from)) {
            $attendance_from           = Carbon::parse($request->date_from)->startOfDay()->toDateTimeString();
            $attendance_to             = Carbon::parse($request->date_to)->endOfDay()->toDateTimeString();
            $inpatient_attendance_from = Carbon::parse($request->date_from)->toDateString();
            $inpatient_attendance_to   = Carbon::parse($request->date_to)->toDateString();
            $clinic_id                 = $request->clinic_id;
            $patient_type              = $request->patient_type;
        } else {
            $attendance_from           = Carbon::now()->startOfDay()->toDateTimeString();
            $attendance_to             = Carbon::now()->endOfDay()->toDateTimeString();
            $inpatient_attendance_from = Carbon::now()->toDateString();
            $inpatient_attendance_to   = Carbon::now()->toDateString();
            $clinic_id                 = 0;
            $patient_type              = 1;
        }

        if ($clinic_id == 0) {
            $triage = Triage::whereNotIn('patient_id', findTestOrDemoPatients())
                ->whereBetween('created_at', [$attendance_from, $attendance_to])
                ->select('patient_id', 'created_at')
                ->get();
        } else {
            $triage = Triage::where('clinic_allocation', $clinic_id)
                ->whereNotIn('patient_id', findTestOrDemoPatients())
                ->whereBetween('created_at', [$attendance_from, $attendance_to])
                ->select('patient_id', 'created_at')
                ->get();
        }

        if ($patient_type == 2) {
            // record for insured patients
            foreach ($triage as $record) {
                $patient             = Patient::where('id', $record->patient_id)->first();
                $patient_was_insured = patient_was_insured($patient->id, $record->created_at);

                if ($patient_was_insured) {
                    $age_at_visit = days_months_years(get_name($patient->id, 'id', 'date_of_birth', 'patients'), $record->created_at)[2];

                    // Children
                    if ($age_at_visit <= 12) {
                        if ($patient->gender == 1) {
                            array_push($filters, ['patients.gender', '=', $patient->gender]);
                            $patient_array_children_males[] = $patient->id;
                            $patient_array[]                = $patient->id;
                        } elseif ($patient->gender == 2) {
                            array_push($filters, ['patients.gender', '=', $patient->gender]);
                            $patient_array_children_females[] = $patient->id;
                            $patient_array[]                  = $patient->id;
                        } else {
                            $patient_array_children[] = $patient->id;
                            $patient_array[]          = $patient->id;
                        }
                        // $patient_array_children[] = $patient->id;
                        // $patient_array[] = $patient->id;
                    }

                    // Adults
                    if ($age_at_visit > 12) {
                        if (!is_null($request->age) && is_numeric($request->age)) {
                            array_push($filters, ['age_groups.name', '=', $request->age]);
                            $patient_array_age[] = $patient->id;
                        }
                        if (($request->gender == 1) && is_numeric($request->gender)) {
                            array_push($filters, ['patients.gender', '=', $request->gender]);
                            $patient_array_males[] = $patient->id;
                        } elseif (($request->gender == 2) && is_numeric($request->gender)) {
                            array_push($filters, ['patients.gender', '=', $request->gender]);
                            $patient_array_females[] = $patient->id;
                        } else {
                            $patient_array_adults[] = $patient->id;
                            $patient_array[]        = $patient->id;
                        }
                    }

                    // find out if patient was admitted during this time period
                    $inpatient_info = InpatientInfo::where('patient_id', $patient->id)
                        ->whereBetween('admitted_on', [$inpatient_attendance_from, $inpatient_attendance_to])
                        ->first();

                    if ($inpatient_info) {
                        $patient_array_admitted[] = $patient->id;
                    }
                }
            }
        } else {
            // record for all patients
            foreach ($triage as $record) {
                $patient      = Patient::withTrashed()->find($record->patient_id);
                if ($patient) {
                    $age_at_visit = days_months_years(get_name($patient->id, 'id', 'date_of_birth', 'patients'), $record->created_at)[2];
                    if ($age_at_visit <= 12) {
                        if ($patient->gender == 1) {
                            array_push($filters, ['patients.gender', '=', $patient->gender]);
                            $patient_array_children_males[] = $patient->id;
                            $patient_array[]                = $patient->id;
                        } elseif ($patient->gender == 2) {
                            array_push($filters, ['patients.gender', '=', $patient->gender]);
                            $patient_array_children_females[] = $patient->id;
                            $patient_array[]                  = $patient->id;
                        } else {
                            $patient_array_children[] = $patient->id;
                            $patient_array[]          = $patient->id;
                        }
                        $patient_array_children[] = $patient->id;
                        $patient_array[]          = $patient->id;
                    }

                    // Adults
                    if ($age_at_visit > 12) {
                        if (!is_null($request->age)) {
                            array_push($filters, ['age_groups.name', '=', $request->age]);
                            $patient_array_age[] = $patient->id;
                        }
                        if (($patient->gender == 1)) {
                            array_push($filters, ['patients.gender', '=', $patient->gender]);
                            $patient_array_males[] = $patient->id;
                        } elseif (($patient->gender == 2)) {
                            array_push($filters, ['patients.gender', '=', $patient->gender]);
                            $patient_array_females[] = $patient->id;
                        } else {
                            array_push($filters, ['patients.gender', '=', $patient->gender]);
                            $patient_array_adults[] = $patient->id;
                            $patient_array[]        = $patient->id;
                        }
                        $patient_array_adults[] = $patient->id;
                        $patient_array[]        = $patient->id;
                    }

                    // find out if patient was admitted during this time period
                    $inpatient_info = InpatientInfo::where('patient_id', $patient->id)
                        ->whereBetween('admitted_on', [$inpatient_attendance_from, $inpatient_attendance_to])
                        ->first();

                    if ($inpatient_info) {
                        $patient_array_admitted[] = $patient->id;
                    }
                }
            }
        }

        // now collect the total amounts spent for these individual patients
        $insurance_drugs_total          = 0;
        $insurance_investigations_total = 0;
        $insurance_procedures_total     = 0;
        $insurance_others_total         = 0;
        $insurance_sundries_total       = 0;
        $insurance_consultations_total  = 0;

        $drugs_insurance = DB::table('insurance_expenditure')
            ->whereIn('patient_id', $patient_array)
            ->where('tag_id', 3)
            ->whereBetween('created_at', [$attendance_from, $attendance_to])
            ->select('insurance_amount')
            ->get();

        $sundries_insurance = DB::table('insurance_expenditure')
            ->whereIn('patient_id', $patient_array)
            ->where('tag_id', 5)
            ->whereBetween('created_at', [$attendance_from, $attendance_to])
            ->select('insurance_amount')
            ->get();

        $investigations_insurance = DB::table('insurance_expenditure')
            ->whereIn('patient_id', $patient_array)
            ->where('tag_id', 2)
            ->whereBetween('created_at', [$attendance_from, $attendance_to])
            ->select('insurance_amount')
            ->get();

        $deposits_insurance = DB::table('insurance_expenditure')
            ->whereIn('patient_id', $patient_array)
            ->whereIn('tag_id', [6, 7, 8])
            ->whereBetween('created_at', [$attendance_from, $attendance_to])
            ->select('insurance_amount', 'item_id')
            ->get();

        $procedures_insurance = DB::table('insurance_expenditure')
            ->whereIn('patient_id', $patient_array)
            ->where('tag_id', 4)
            ->whereBetween('created_at', [$attendance_from, $attendance_to])
            ->select('insurance_amount')
            ->get();

        foreach ($drugs_insurance as $drug) {
            $insurance_drugs_total += $drug->insurance_amount;
        }

        foreach ($investigations_insurance as $investigation) {
            $insurance_investigations_total += $investigation->insurance_amount;
        }

        foreach ($procedures_insurance as $procedure) {
            $insurance_procedures_total += $procedure->insurance_amount;
        }

        foreach ($sundries_insurance as $sundry) {
            $insurance_sundries_total += $sundry->insurance_amount;
        }

        foreach ($deposits_insurance as $deposit) {
            $service_type = DB::table('services')->where('id', $deposit->item_id)->value('item_type');

            if ($service_type == 'Consultation') {
                $insurance_consultations_total += $deposit->insurance_amount;
            }

            if ($service_type == 'Service' || $service_type == 'Other_service') {
                $insurance_others_total += $deposit->insurance_amount;
            }
        }

        $drugs_total          = 0;
        $investigations_total = 0;
        $procedures_total     = 0;
        $others_total         = 0;
        $sundries_total       = 0;
        $consultations_total  = 0;

        // if patient is insured, skip this section
        if ($patient_type != 2) {
            $drugs = DB::table('treatment_deposits')
                ->whereIn('patient_id', $patient_array)
                ->whereBetween('created_at', [$attendance_from, $attendance_to])
                ->select('patient_amount_paid', 'refund_amount')
                ->get();

            $sundries = DB::table('sundries_deposits')
                ->whereIn('patient_id', $patient_array)
                ->whereBetween('created_at', [$attendance_from, $attendance_to])
                ->select('patient_amount_paid', 'refund_amount')
                ->get();

            $investigations = DB::table('investigation_deposits')
                ->whereIn('patient_id', $patient_array)
                ->whereBetween('created_at', [$attendance_from, $attendance_to])
                ->select('patient_amount_paid', 'refund_amount')
                ->get();

            $deposits = DB::table('service_deposits')
                ->whereIn('patient_id', $patient_array)
                ->whereBetween('created_at', [$attendance_from, $attendance_to])
                ->select('patient_amount_paid', 'refund_amount', 'service_type')
                ->get();

            $procedures = DB::table('procedure_deposits')
                ->whereIn('patient_id', $patient_array)
                ->whereBetween('created_at', [$attendance_from, $attendance_to])
                ->select('patient_amount_paid', 'refund_amount')
                ->get();

            foreach ($drugs as $drug) {
                $drugs_total += ($drug->patient_amount_paid - $drug->refund_amount);
            }

            foreach ($investigations as $investigation) {
                $investigations_total += ($investigation->patient_amount_paid - $investigation->refund_amount);
            }

            foreach ($procedures as $procedure) {
                $procedures_total += ($procedure->patient_amount_paid - $procedure->refund_amount);
            }

            foreach ($sundries as $sundry) {
                $sundries_total += ($sundry->patient_amount_paid - $sundry->refund_amount);
            }

            foreach ($deposits as $deposit) {
                if ($deposit->service_type == 'Consultation') {
                    $consultations_total += ($deposit->patient_amount_paid - $deposit->refund_amount);
                }

                if ($deposit->service_type == 'Services' || $deposit->service_type == 'Service' || $deposit->service_type == 'Other_service') {
                    $others_total += ($deposit->patient_amount_paid - $deposit->refund_amount);
                }
            }
        }

        $total_costs_consultation   = $consultations_total + $insurance_consultations_total;
        $total_costs_services       = $others_total + $insurance_others_total;
        $total_costs_investigations = $investigations_total + $insurance_investigations_total;
        $total_costs_drug           = $drugs_total + $insurance_drugs_total;
        $total_costs_procedures     = $procedures_total + $insurance_procedures_total;
        $total_costs_sundries       = $sundries_total + $insurance_sundries_total;
        $total_costs                = $total_costs_consultation + $total_costs_services + $total_costs_investigations + $total_costs_drug + $total_costs_procedures + $total_costs_sundries;

        return view('reports::hmis_reports.streamline_outpatient_report', compact('age', 'hospital_information', 'clinics', 'attendance_from', 'attendance_to', 'total_costs', 'total_costs_consultation', 'total_costs_services', 'total_costs_drug', 'total_costs_investigations', 'total_costs_procedures', 'total_costs_sundries', 'patient_array', 'clinic_id', 'patient_type', 'patient_array', 'patient_array_admitted', 'patient_array_adults', 'patient_array_children', 'patient_array_age', 'patient_array_males', 'patient_array_females', 'patient_array_children_males', 'patient_array_children_females'));
    }

    /*
     * out patient streamline report income drill down
     */

    public function streamline_outpatient_income_details(Request $request)
    {
        //'Investigations=2','Treatments=3','Procedures=4','Sundries=5','Consultation=6','Co-Payments=7','Services=8'
        $date_filter_string           = $request->dates;
        $date_filter_array            = explode('/', $date_filter_string);
        $date_from                    = $date_filter_array[0];
        $date_to                      = $date_filter_array[0];
        $deposits                     = [];
        $insurance_expenditure        = [];
        $tag_id                       = $request->tag_id;
        $patients_json_encoded_string = $request->patient_filter;
        $department                   = $request->department;
        $insured_or_all_patients      = $request->insured_or_all;
        $patient_id_decode = [];
        if (isset($request->patient_filter)) {
            $patient_ids        = $request->patient_filter;
            $patient_id_decode  = json_decode($patient_ids);
            $patient_ids_string = !is_null($patient_id_decode) ? implode(",", $patient_id_decode) : "";

            if (!empty($patient_ids_string)) {
                $patient_filter_sales   = " select_client IN IN (" . $patient_ids_string . ") AND ";
                $patient_filter_general = " patient_id IN (" . $patient_ids_string . ") AND ";
            }
        }
        //Consultation=6
        if ($tag_id == 6) {
            if ($insured_or_all_patients == 1) {
                $deposits              = ServiceDeposit::where('service_type', 'Consultation')->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                $insurance_expenditure = InsuranceExpenditure::whereIn('item_id', $patient_id_decode)->where('tag_id', 6)->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
            } else {
                $insurance_expenditure = InsuranceExpenditure::whereIn('item_id', $patient_id_decode)->where('tag_id', 6)->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
            } //Investigations=2
        } else if ($tag_id == 2) {
            if ($insured_or_all_patients == 1) {
                $deposits              = InvestigationDeposit::whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                $insurance_expenditure = InsuranceExpenditure::whereIn('item_id', $patient_id_decode)->where('tag_id', 2)->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                return view('reports::hmis_reports.streamline_outpatient_income_detailed_report', compact('department', 'tag_id', 'date_from', 'date_to', 'deposits', 'insurance_expenditure'));
            } else {
                $insurance_expenditure = InsuranceExpenditure::whereIn('item_id', $patient_id_decode)->where('tag_id', 2)->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                return view('reports::hmis_reports.streamline_outpatient_income_detailed_report', compact('department', 'tag_id', 'date_from', 'date_to', 'deposits', 'insurance_expenditure'));
            } //Treatments=3
        } else if ($tag_id == 3) {
            if ($insured_or_all_patients == 1) {
                $deposits              = TreatmentDeposits::whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                $insurance_expenditure = InsuranceExpenditure::whereIn('item_id', $patient_id_decode)->where('tag_id', 3)->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                return view('reports::hmis_reports.streamline_outpatient_income_detailed_report', compact('department', 'tag_id', 'date_from', 'date_to', 'deposits', 'insurance_expenditure'));
            } else {
                $insurance_expenditure = InsuranceExpenditure::whereIn('item_id', $patient_id_decode)->where('tag_id', 3)->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                return view('reports::hmis_reports.streamline_outpatient_income_detailed_report', compact('department', 'tag_id', 'date_from', 'date_to', 'deposits', 'insurance_expenditure'));
            } //Procedures = 4
        } else if ($tag_id == 4) {
            if ($insured_or_all_patients == 1) {
                $deposits              = ProcedureDeposit::whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                $insurance_expenditure = InsuranceExpenditure::whereIn('item_id', $patient_id_decode)->where('tag_id', 4)->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                return view('reports::hmis_reports.streamline_outpatient_income_detailed_report', compact('department', 'tag_id', 'date_from', 'date_to', 'deposits', 'insurance_expenditure'));
            } else {
                $insurance_expenditure = InsuranceExpenditure::whereIn('item_id', $patient_id_decode)->where('tag_id', 4)->whereDate('created_at', '>=', $date_from)->whereDate('created_at', '<=', $date_to)->get();
                return view('reports::hmis_reports.streamline_outpatient_income_detailed_report', compact('department', 'tag_id', 'date_from', 'date_to', 'deposits', 'insurance_expenditure'));
            }
        }
        return view('reports::hmis_reports.streamline_outpatient_income_detailed_report', compact('department', 'tag_id', 'date_from', 'date_to', 'deposits', 'insurance_expenditure'));
    }

    public function streamline_outpatient_details(Request $request)
    {
        $patients_lists                 = [];
        $patient_array_adults           = json_decode($request->patient_array_adults);
        $patient_array_males            = json_decode($request->patient_array_males);
        $patient_array_females          = json_decode($request->patient_array_females);
        $patient_array_children         = json_decode($request->patient_array_children);
        $patient_array_children_males   = json_decode($request->patient_array_children_males);
        $patient_array_children_females = json_decode($request->patient_array_children_females);
        $patient_array                  = json_decode($request->patient_array);
        $patient_array_admitted         = json_decode($request->patient_array_admitted);
        $patients                       = Patient::get();
        $patient_category = "Adults";
        $patient_data = [];

        if ($patient_array_adults) {
            foreach ($patient_array_adults as $patient_array_adult) {
                $patient        = Patient::where('id', $patient_array_adult)->first();
                $patient_data[] = $patient;
                $patient_category = "Adults";
            }
            return view('reports::hmis_reports.streamline_outpatient_list_report', compact('patient_category','patient_data', 'patients', 'patient_array_adults'));
        }
        if ($patient_array_males) {
            foreach (collect($patient_array_males) as $patient_array_male) {
                $patient        = Patient::where('id', $patient_array_male)->first();
                $patient_data[] = $patient;
                $patient_category = "Male Adults";
            }
            return view('reports::hmis_reports.streamline_outpatient_list_report', compact('patient_category','patient_data', 'patients', 'patient_array_males'));
        }
        if ($patient_array_females) {
            foreach ($patient_array_females as $patient_array_female) {
                $patient        = Patient::where('id', $patient_array_female)->first();
                $patient_data[] = $patient;
                $patient_category = "Female Adults";
            }
            return view('reports::hmis_reports.streamline_outpatient_list_report', compact('patient_category','patient_data', 'patients', 'patient_array_females'));
        }
        if ($patient_array_children) {
            foreach ($patient_array_children as $patient_array_child) {
                $patient        = Patient::where('id', $patient_array_child)->first();
                $patient_data[] = $patient;
                $patient_category = "Children";
            }
            return view('reports::hmis_reports.streamline_outpatient_list_report', compact('patient_category','patient_data', 'patient_array_children'));
        }
        if ($patient_array_children_males) {
            foreach ($patient_array_children_males as $patient_array_children_male) {
                $patient        = Patient::where('id', $patient_array_children_male)->first();
                $patient_data[] = $patient;
                $patient_category = "Male Children";
            }
            return view('reports::hmis_reports.streamline_outpatient_list_report', compact('patient_category','patient_data', 'patient_array_children_males'));
        }
        if ($patient_array_children_females) {
            foreach ($patient_array_children_females as $patient_array_children_female) {
                $patient        = Patient::where('id', $patient_array_children_female)->first();
                $patient_data[] = $patient;
                $patient_category = "Female Children";
            }
            return view('reports::hmis_reports.streamline_outpatient_list_report', compact('patient_category', 'patient_data', 'patient_array_children_females'));
        }
        if ($patient_array) {
            foreach ($patient_array as $patient_arr) {
                $patient        = Patient::where('id', $patient_arr)->first();
                $patient_data[] = $patient;
                $patient_category = "All Patients";
            }
            return view('reports::hmis_reports.streamline_outpatient_list_report', compact('patient_category','patient_data', 'patient_array'));
        }
        if ($patient_array_admitted) {
            foreach ($patient_array_admitted as $patient_array_admit) {
                $patient        = Patient::where('id', $patient_array_admit)->first();
                $patient_data[] = $patient;
                $patient_category = "Admitted Patients";
            }
            return view('reports::hmis_reports.streamline_outpatient_list_report', compact('patient_category','patient_data', 'patient_array_admitted'));
        }
    }

    /*
     * out patient streamline report attendance drill down
     */

    public function streamline_outpatient_attendance_details(Request $request)
    {
        $date_from     = $request->from;
        $date_to       = $request->to;
        $patient_array = json_decode($request->patient_array);
        $department_id = $request->department;
        return view('reports::hmis_reports.streamline_outpatient_attendance_detailed_report', compact('date_from', 'date_to', 'patient_array'));
    }

    /**
     * Reprint Investigations Receipt coing from streamline outpatient report
     *
     * @param $id
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function reprint_investigation_receipt($id)
    {

        // get payment details for the services
        $investigation_payment = InvestigationDeposit::where(['receipt_number' => $id])->first();

        $patient_to_pay = $investigation_payment->patient_amount_paid;

        $investigation_prices_array = explode(",", $investigation_payment->investigation_amounts);
        $investigation_ids_array    = explode(",", $investigation_payment->investigation_items);

        $total_amount_pay = 0;

        foreach ($investigation_prices_array as $price) {
            $total_amount_pay += $price;
        }

        $receipt_date = $investigation_payment->created_at;

        // receipt number
        $receipt_number = $investigation_payment->receipt_number;

        // details to be used for receipt
        $hospital_information = HospitalInformation::first();

        $discounts_applied = $total_amount_pay - $patient_to_pay;

        $patient = Patient::find($investigation_payment->patient_id);

        $compact_values = compact('hospital_information', 'receipt_date', 'receipt_number', 'discounts_applied',
            'patient', 'investigation_ids_array', 'investigation_prices_array', 'total_amount_pay', 'patient_to_pay');

        return view('patient_finance::patient_finance.receipts.investigations_reprint', $compact_values);
    }

    public function disease_prevalence_charts(Request $request)
    {

        $month_array   = [];
        $data_array    = [];
        $array_tracker = [];

        // check for date search
        if (!is_null($request->start_date) && !is_null($request->end_date)) {
            $start_date = $request->start_date;
            $end_date   = $request->end_date;
        } else {
            $start_date = date('Y-m-d');
            $end_date   = date('Y-m-d');
        }

        // check for selected diagnosis
        if (!is_null($request->diagnosis_id)) {
            $diagnosis_id   = $request->diagnosis_id;
            $diagnosis_name = get_name($diagnosis_id, 'id', 'name', 'diagnoses');
        } else {
            // get a random diagnosis
            $diagnosis_id   = 11;
            $diagnosis_name = get_name($diagnosis_id, 'id', 'name', 'diagnoses');
        }

        // prep the dates for search queries
        $start_date_clean = Carbon::parse($start_date)->startOfDay()->toDateTimeString();
        $end_date_clean   = Carbon::parse($end_date)->endOfDay()->toDateTimeString();

        // get drugs dispensed
        $diagnoses = DB::table('consultations')->where([['created_at', '>', $start_date_clean], ['created_at', '<', $end_date_clean], ['primary_diagnosis', '=', $diagnosis_id]])->select('primary_diagnosis', 'created_at')->get();

        $label = 'Disease Prevalence For ' . $diagnosis_name;

        foreach ($diagnoses as $diagnosis) {
            $month          = (int) Carbon::parse($diagnosis->created_at)->format('m');
            $year           = (int) Carbon::parse($diagnosis->created_at)->format('Y');
            $string_tracker = $year . $month;

            if (in_array($string_tracker, $array_tracker)) {
                $key = array_search($string_tracker, $array_tracker);

                $current_count    = $data_array[$key];
                $data_array[$key] = $current_count + 1;
            } else {
                array_push($data_array, 1);
                array_push($array_tracker, $string_tracker);

                $month_name = "";

                switch ($month) {
                    case 1:
                        $month_name = "January";
                        break;
                    case 2:
                        $month_name = "February";
                        break;
                    case 3:
                        $month_name = "March";
                        break;
                    case 4:
                        $month_name = "April";
                        break;
                    case 5:
                        $month_name = "May";
                        break;
                    case 6:
                        $month_name = "June";
                        break;
                    case 7:
                        $month_name = "July";
                        break;
                    case 8:
                        $month_name = "August";
                        break;
                    case 9:
                        $month_name = "September";
                        break;
                    case 10:
                        $month_name = "October";
                        break;
                    case 11:
                        $month_name = "November";
                        break;
                    case 12:
                        $month_name = "December";
                        break;
                    default:
                        # code...
                        break;
                }

                $result = $month_name . " " . $year;

                // add the new date
                array_push($month_array, $result);
            }
        }

        $diagnoses = Diagnosis::pluck('name', 'id')->toArray();
        $diagnoses = ['' => '- select -'] + $diagnoses;

        return view('reports::hmis_reports.disease_prevalence_charts', compact('month_array', 'data_array', 'label', 'diagnoses'));
    }

    /**
     * * opd attendance referrals diagnosis totals steamline report
     * */
    public function opd_attendance_referrals_diagnosis_totals(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("0 days"));
            $startDate       = date_format($date, "d-m-Y");
            $attendance_from = date_format($date, "Y-m-d");
            $attendance_to   = date("Y-m-d");
        }
        return view('reports::hmis_reports.opd_attendance_referrals_diagnosis_totals', compact('hospital_information', 'attendance_from', 'attendance_to'));
    }

//    Inpatient monthly report 108
    public function hmis_108(Request $request)
    {

        $valid = Validator::make($request->all(), [
            'date_from' => 'nullable|date|required_with:date_to',
            'date_to' => 'bail|nullable|required_with:date_from|date|after:date_from',
        ]);
        if($valid->fails()){
            $string = "";
            foreach ($valid->errors()->getMessages() as $item) {
                $string .= "{$item[0]}<br>";
            }
            flash($string)->error();
            return back()->withErrors($valid)->withInput();
        }
        $start = !empty($request->date_from)? $request->date_from : Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d');
        $end   = !empty($request->date_to)? $request->date_to : date("Y-m-t", strtotime($start));
        $citizenship = isset($request->type)? $request->type :1;
        $page = 'census';
        $dates = $ward_data = $referral_data = []; $counter = $referred_from = $self_referred = $referred_to = $ran_away=0;
        if(empty($request->date_from)){
            $dates['last_month'] = date('F', strtotime($start));
            $dates['last_year'] = date('Y', strtotime($start));
            $dates['days'] = date('t', strtotime($start));
            $dates['citizenship'] = $citizenship;
        }else {
            $dates['start'] = date('d-M-Y', strtotime($start));
            $dates['end'] = date('d-M-Y', strtotime($end));
            $dates['days'] = intval((new DateTime($start))->diff(new DateTime($end))->format("%a"));
            $dates['citizenship'] = $citizenship;
        }
        $ward_types = HmisWard::all('name', 'id');
        foreach ($ward_types as $ward_type)
        {
            $ward_info = Ward::select('id', 'name', 'beds')->where('hmis_ward_id', $ward_type['id'])->get();
            if (!empty($ward_info)) {

                $ward_ids = $patient_ids = $dead_ids = $ward_beds = [];$ward_days_count = $ward_deaths = $ward_admissions =0;
                foreach ($ward_info as $ward){
                    $ward_ids[] = $ward->id;
                    $ward_beds[] = $ward->beds;
                }
                $admissions = DB::table('inpatient_info as i')->leftjoin('triage AS t', 't.episode_id', '=', 'i.episode_id')
                    ->leftjoin('patients AS p', 'p.id', '=', 'i.patient_id')
                    ->whereIn('i.ward_id', $ward_ids)->where('p.citizenship', $citizenship)
                    ->whereBetween('i.admitted_on', [$start, $end])
                    ->select('i.id','i.outcome_id','i.patient_id','i.admitted_on','i.discharged_on', 't.referral')
                    ->get();
                $ward_admissions = !empty($admissions)? count($admissions):0;
                foreach ($admissions as $admission) {
                    $admitted_on = new DateTime($admission->admitted_on);
                    if($admission->outcome_id == '8'){
                        $ran_away +=1;
                        $referral_data['ran_away_ids'][] = $admission->patient_id;
                    }
                    elseif($admission->outcome_id == '4') {
                        $referred_from +=1;
                        $referral_data['referred_from_ids'][] = $admission->patient_id;
                    }
                    elseif($admission->outcome_id == '5') {
                        $ward_deaths +=1;
                        $dead_ids[] =  $admission->patient_id;
                    }
                    if($admission->referral == '1') {
                        $self_referred +=1;
                        $referral_data['self_referred_ids'][] = $admission->patient_id;
                    }
                    else {
                        $referred_to +=1;
                        $referral_data['referred_to_ids'][] = $admission->patient_id;
                    }
                    if(!empty($admission->discharged_on) && ($admission->discharged_on !== "0000-00-00") ){
                        if(intval($admitted_on->diff(new DateTime($admission->discharged_on))->format("%a")) > 30) $test[] =['id' =>$admission->id,'count'=>intval($admitted_on->diff(new DateTime($admission->discharged_on))->format("%a"))];
                        $ward_days_count += intval($admitted_on->diff(new DateTime($admission->discharged_on))->format("%a"));
                    }else {
                        $ward_days_count += intval($admitted_on->diff(new DateTime($end))->format("%a"));
                    }
                    $patient_ids[] = $admission->patient_id;
                }

                $ward_data[$counter]['admissions'] = $ward_admissions;
                $ward_data[$counter]['deaths'] = $ward_deaths;
                $ward_data[$counter]['days_count'] = $ward_days_count;
                $ward_data[$counter]['ward_beds'] = array_sum($ward_beds);
                $ward_data[$counter]['ward_name'] = $ward_type['name'];
                $ward_data[$counter]['id'] = $ward_type['id'];
                $ward_data[$counter]['patient_ids'] = $patient_ids;
                $ward_data[$counter]['dead_ids'] = $dead_ids;
                $counter++;
                $referral_data['referred_to'] = $referred_to;
                $referral_data['referred_to_ids'] = isset($referral_data['referred_to_ids'])? $referral_data['referred_to_ids']:[];
                $referral_data['self_referred'] = $self_referred;
                $referral_data['self_referred_ids'] = isset($referral_data['self_referred_ids'])? $referral_data['self_referred_ids']:[];
                $referral_data['referred_from'] = $referred_from;
                $referral_data['referred_from_ids'] = isset($referral_data['referred_from_ids'])? $referral_data['referred_from_ids']:[];
                $referral_data['ran_away'] = $ran_away;
                $referral_data['ran_away_ids'] = isset($referral_data['ran_away_ids'])? $referral_data['ran_away_ids']:[];

            }else {
                $ward_data[$counter]['admissions'] = 0;
                $ward_data[$counter]['deaths'] = 0;
                $ward_data[$counter]['days_count'] = 0;
                $ward_data[$counter]['ward_beds'] = 0;
                $ward_data[$counter]['ward_name'] = $ward_type['name'];
                $ward_data[$counter]['id'] = $ward_type['id'];
                $ward_data[$counter]['patient_ids'] = [];
                $ward_data[$counter]['dead_ids'] =[];
                $counter++;
            }
        }
        return view('reports::hmis_reports.108.index', compact('ward_data','referral_data', 'dates','page'));
    }


    public function hmis_108_drill(Request $request) {

        $patientIDs = unserialize($request->patients);
        $page = $request->page;
        $diagnoses = unserialize($request->diagnoses);

        $patients = DB::table('patients')->whereIn('id', array_unique($patientIDs))->orderBy('last_name','asc')->get();
        $categories = DB::table('patient_categories')->pluck('name', 'id');

        return view('reports::hmis_reports.108.drill-down', compact('patients', 'categories','page', 'diagnoses'));
    }

    function print_Hmis_108_information(Request $request) {
        $ward_data = !empty($request->ward_data)? unserialize($request->ward_data):[];
        $referral_data = !empty($request->referral_data)? unserialize($request->referral_data):[];
        $dates = !empty($request->dates)? unserialize($request->dates):[];
        $counted_procedures =!empty($request->counted_procedures)? unserialize($request->counted_procedures):[];
        $counted_investigations =!empty($request->counted_investigations)? unserialize($request->counted_investigations):[];
        $diagnoses_data =!empty($request->diagnoses_data)? unserialize($request->diagnoses_data):[];
        $table_options = !empty($request->table_options)? unserialize($request->table_options):[];
        $page = $request->page;
        $pdf = SnappyPDF::loadView('reports::hmis_reports.108.print-census-information', compact('ward_data','referral_data', 'dates','page','counted_investigations','table_options','counted_procedures','diagnoses_data'))
            ->setOrientation('portrait')
            ->setOption('margin-bottom', 7)
            ->setOption('margin-top', 5)
            ->setOption('footer-html', '<i>'. config('app.name') .' - Printed On ' . date('Y-m-d') . ' By ' . auth()->user()->first_name . " " . auth()->user()->last_name . '</i>');
        return $pdf->inline(__('hmis_reports.hmis_108_report_census') . date(" d-m-y h:ia") . '.pdf');
    }

    public function hmis_108_procedures(Request $request) {
        request()->validate([
            'date_from' => 'nullable|date|required_with:date_to',
            'date_to' => 'bail|nullable|required_with:date_from|date|after:date_from',
        ]);
        $start = !empty($request->date_from)? $request->date_from : Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d');
        $end   = !empty($request->date_to)? $request->date_to : date("Y-m-t", strtotime($start));
        $citizenship = isset($request->type)? $request->type :1;
        $page = 'procedures';$table_options =[];
        $dates = $new_procedure = $counted_procedures = $table_options = $actual_procedures =[];
        if(empty($request->date_from)){
            $dates['last_month'] = date('F', strtotime($start));
            $dates['last_year'] = date('Y', strtotime($start));
            $dates['days'] = date('t', strtotime($start));
            $dates['citizenship'] = $citizenship;
        }else {
            $dates['start'] = date('d-M-Y', strtotime($start));
            $dates['end'] = date('d-M-Y', strtotime($end));
            $dates['days'] = intval((new DateTime($start))->diff(new DateTime($end))->format("%a"));
            $dates['citizenship'] = $citizenship;
        }

        $procedures = WardProcedure::where([['procedure_id','<>', ''], ['citizenship', $citizenship]])->whereBetween('ward_procedures.created_at', [$start, $end])->select('ward_procedures.id','patient_id','episode_id','procedure_date as admitted_on','procedure_id as procedures','performed_by as procedures_done_by')->leftJoin('patients', 'patients.id','ward_procedures.patient_id')->get()->toArray();
        foreach($procedures as $procedure) {
            $new_procedure[] =['id'=>$procedure['id'],'patient_id'=>$procedure['patient_id'],'episode_id'=>$procedure['episode_id'],'admitted_on'=>$procedure['admitted_on'], 'procedures_done_by'=>$procedure['procedures_done_by'], 'procedure_ids'=> $procedure['procedures']];
            $actual_procedures[] = $procedure['procedures'];
        }
        $procedures_count = !empty($actual_procedures)? array_unique($actual_procedures) : [];
        foreach($procedures_count as $value){
            $procedure_info = DB::table('procedures AS p')->leftJoin('hmis_category_options AS a','a.id', 'p.hmis_no_inpatient')
                ->leftJoin('hmis_categories AS h','h.id', 'p.hmis_category_inpatient')->where([['p.id', $value],['p.hmis_inpatient_type', 1]])
                ->select('h.id as category_id','h.number as category_number', 'h.title as category_name','a.id as option_id','a.name as option_name','a.number as option_number','p.id','p.name', 'p.hmis_no_inpatient')->first();
            if(!empty($procedure_info)) {
                $patient_ids = [];
                foreach($new_procedure as $pro) if($value == $pro['procedure_ids']) $patient_ids[] = $pro['patient_id'];
                $counted_procedures[] =['id'=>$value, 'count'=>count($patient_ids), 'name'=>$procedure_info->name, 'chosen_option'=>$procedure_info->hmis_no_inpatient ?? 'None',  'category_id'=>$procedure_info->category_id ?? '58','category_number'=>$procedure_info->category_number ?? '3.13','category_name'=>$procedure_info->category_name ?? 'Other Un-classified Surgical Procedures','option_id'=>$procedure_info->option_id ?? '98','option_name'=>$procedure_info->option_name ?? 'Others','option_number'=>$procedure_info->option_number ?? 'OT04', 'patient_ids' => $patient_ids];
            }
        }
        $inpatient_categories = HmisCategory::where('section_number', '3')->select('id','number', 'title')->get()->toArray();
        foreach($inpatient_categories as $inpatient_category) {
            $category_options = HmisCategoryOptions::where('hmis_category_id', $inpatient_category['id'])->select('id','number', 'name')->get()->toArray();
            $table_options[] = ['category_id'=>$inpatient_category['id'],'category_name'=>$inpatient_category['title'],'category_number'=>$inpatient_category['number'],'category_options'=>$category_options];
        }
        return view('reports::hmis_reports.108.index', compact('dates','counted_procedures','table_options','page'));
    }

    public function hmis_108_radiology_and_imaging(Request $request) {
        request()->validate([
            'date_from' => 'nullable|date|required_with:date_to',
            'date_to' => 'bail|nullable|required_with:date_from|date|after:date_from',
        ]);
        $start = !empty($request->date_from)? $request->date_from : Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d');
        $end   = !empty($request->date_to)? $request->date_to : date("Y-m-t", strtotime($start));
        $citizenship = isset($request->type)? $request->type :1;
        $page = 'radiology and Imaging';$table_options =[];
        $dates = $investigation_ids = $ids_count = $counted_investigations =[];
        if(empty($request->date_from)){
            $dates['last_month'] = date('F', strtotime($start));
            $dates['last_year'] = date('Y', strtotime($start));
            $dates['days'] = date('t', strtotime($start));
            $dates['citizenship'] = $citizenship;
        }else {
            $dates['start'] = date('d-M-Y', strtotime($start));
            $dates['end'] = date('d-M-Y', strtotime($end));
            $dates['days'] = intval((new DateTime($start))->diff(new DateTime($end))->format("%a"));
            $dates['citizenship'] = $citizenship;
        }

        $investigations = OrderedInvestigation::whereBetween('created_at', [$start, $end])->where('inpatient', 1)->get(['id', 'patient_id','episode_id','investigation_id']);

        foreach($investigations as $investigation) {
            $ordered_investigations = explode(',',  $investigation->investigation_id);
            foreach ($ordered_investigations as $ordered_investigation) if(!empty($ordered_investigation)) {
                $investigation_ids[] =$ordered_investigation;
                $ids_count[] = ['id' => $investigation->id, 'patient_id' => $investigation->patient_id, 'episode_id' => $investigation->episode_id, 'investigation_id' => $ordered_investigation];
            }
        }
        $investigations_count = (!empty($investigation_ids))? array_unique($investigation_ids):[];
        foreach($investigations_count as $value){
            $investigation_info = DB::table('investigations AS p')->leftJoin('hmis_category_options AS a','a.id', 'p.hmis_no_inpatient')
                ->leftJoin('hmis_categories AS h','h.id', 'p.hmis_category_inpatient')->where('p.id', $value)
                ->select('h.id as category_id','h.number as category_number', 'h.title as category_name','a.id as option_id','a.name as option_name','a.number as option_number','p.id','p.name', 'p.hmis_no_inpatient')->first();
            if(!empty($investigation_info)) {
                $male_under_four = $male_over_five = $female_under_four = $female_over_five = 0;
                $patientIds = $male_under_four_ids = $male_over_five_ids = $female_under_four_ids = $female_over_five_ids = $patients = [];
                $patient_ids = array_filter($ids_count, function ($item) use ($value) {
                    return $item['investigation_id'] == $value;
                });
                foreach ($patient_ids as $options)  $patientIds[]=$options['patient_id'];
                $patients = Patient::whereIn('id', array_unique($patientIds))->where('citizenship', $citizenship)->select('id','date_of_birth', 'gender')->get();
                foreach ($patients as $patient){
                    $age = Carbon::parse($patient->dob)->age;
                    if($age < 4) {
                        if($patient->gender == '1'){
                            $male_over_five++;
                            $male_over_five_ids[] = $patient->id;
                        }
                        elseif($patient->gender == '2'){
                            $female_over_five++;
                            $female_over_five_ids[] = $patient->id;
                        }
                    } else{
                        if($patient->gender == '1'){
                            $male_under_four++;
                            $male_under_four_ids[] = $patient->id;
                        }
                        elseif($patient->gender == '2'){
                            $female_under_four++;
                            $female_under_four_ids[] = $patient->id;
                        }

                    }

                }
                $counted_investigations[] =[
                    'id'=>$value, 'count'=>count(array_unique($patientIds)), 'name'=>$investigation_info->name, 'chosen_option'=>$investigation_info->hmis_no_inpatient ?? 'None',
                    'category_id'=>$investigation_info->category_id ?? '58','category_number'=>$investigation_info->category_number ?? '3.13',
                    'category_name'=>$investigation_info->category_name ?? 'Other Un-classified Surgical Procedures','option_id'=>$investigation_info->option_id ?? '98',
                    'option_name'=>$investigation_info->option_name ?? 'Others', 'female_under_four'=>$female_under_four, 'male_under_four'=>$male_under_four,
                    'option_number'=>$investigation_info->option_number ?? 'OT04', 'patient_ids' => $patient_ids, 'male_over_five'=>$male_over_five,
                    'female_under_four_ids' =>$female_under_four_ids, 'male_under_four_ids'=>$male_under_four_ids, 'female_over_five'=>$female_over_five,
                    'female_over_five_ids'=>$female_over_five_ids, 'male_over_five_ids'=>$male_over_five_ids
                ];
            }
        }
        $inpatient_categories = HmisCategory::where('section_number', '5')->select('id','number', 'title')->get()->toArray();
        foreach($inpatient_categories as $inpatient_category) {
            $category_options = HmisCategoryOptions::where('hmis_category_id', $inpatient_category['id'])->select('id','number', 'name')->get()->toArray();
            $table_options[] = ['category_id'=>$inpatient_category['id'],'category_name'=>$inpatient_category['title'],'category_number'=>$inpatient_category['number'],'category_options'=>$category_options];
        }
        return view('reports::hmis_reports.108.index', compact('dates','counted_investigations','table_options','page'));
    }

    public function hmis_108_admissions_and_deaths (Request $request) {
        request()->validate([
            'date_from' => 'nullable|date|required_with:date_to',
            'date_to' => 'bail|nullable|required_with:date_from|date|after:date_from',
        ]);
        $start = !empty($request->date_from)? $request->date_from : Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d');
        $end   = !empty($request->date_to)? $request->date_to : date("Y-m-t", strtotime($start));
        $citizenship = isset($request->type)? $request->type :1;
        $page = 'Admissions and Deaths by Diagnosis';
        $dates = $primary_diagnosis = $other_diagnoses = $patients = $table_options = $dependent_options = $diagnoses_data =[];
        if(empty($request->date_from)){
            $dates['last_month'] = date('F', strtotime($start));
            $dates['last_year'] = date('Y', strtotime($start));
            $dates['days'] = date('t', strtotime($start));
            $dates['citizenship'] = $citizenship;
        }else {
            $dates['start'] = date('d-M-Y', strtotime($start));
            $dates['end'] = date('d-M-Y', strtotime($end));
            $dates['days'] = intval((new DateTime($start))->diff(new DateTime($end))->format("%a"));
            $dates['citizenship'] = $citizenship;
        }
        $parent_categories = array_unique(HmisCategoryOptions::where('parent_option', '<>','')->pluck('parent_option')->toArray());
        $episodes =  DB::table('inpatient_info as i')->leftJoin('patients as p', 'i.patient_id','p.id')
            ->select('i.id','i.patient_id', 'gender', 'date_of_birth as dob','admitted_on', 'outcome_id', 'primary_diagnosis', 'other_diagnoses')
            ->whereBetween('admitted_on', [$start, $end])
            ->where([['gender','<>',''], ['primary_diagnosis','<>',''],['citizenship', $citizenship]])
            ->get();
        foreach ($episodes as $episode) {
            $primary_diagnosis[] = $episode->primary_diagnosis;
            $others = (!empty($episode->other_diagnoses))? unserialize($episode->other_diagnoses):[];
            if(!empty($others)) foreach($others as $other) if(!empty($other)) $other_diagnoses[] = $other;
            $patients[] = ['id'=> $episode->id,'patient_id'=> $episode->patient_id, 'gender'=> $episode->gender, 'age'=> Carbon::parse($episode->dob)->age,'admitted_on'=> $episode->admitted_on, 'outcome_id'=> $episode->outcome_id, 'primary_diagnosis'=> $episode->primary_diagnosis, 'other_diagnoses'=> $others];
        }
        $all_diagnoses = array_unique(array_merge($primary_diagnosis,$other_diagnoses));
        $diag = Diagnosis::where([['hmis_category','<>',''],['hmis_no_inpatient','<>','']])->whereIn('id', $all_diagnoses)->select('id','name', 'hmis_no_inpatient','hmis_category','dependent_option')->get();
        foreach($diag as $dia) {
            $diagnosis = $dia->id;$other = [];
            $primary = array_filter($patients, function ($item) use ($diagnosis) {
                return $item['primary_diagnosis'] == $diagnosis;
            });
            foreach ($patients as $patient) if(!empty($patient['other_diagnoses']) && in_array($diagnosis, $patient['other_diagnoses'])) $other[] =$patient;
            $diagnoses_data[]=['id'=>$diagnosis,'name'=>$dia->name, 'hmis_category'=>$dia->hmis_category, 'hmis_category_option'=>$dia->hmis_no_inpatient, 'dependent_option'=>$dia->dependent_option, 'primary'=>$primary, 'other'=>$other];
        }

        $diagnosis_categories = HmisCategory::where('section_number', '6')->select('id','number', 'title')->get()->toArray();
        foreach($diagnosis_categories as $diagnosis_category) {
            $options=[];
            $category_options = HmisCategoryOptions::where('hmis_category_id', $diagnosis_category['id'])->select('id','number', 'name')->get()->toArray();
            foreach($category_options as $category_option) {
                $dependent_options = [];
                if(in_array($category_option['id'], $parent_categories))  $dependent_options = HmisCategoryOptions::where('parent_option', $category_option['id'])->select('id','number', 'name')->get()->toArray();
                $options[]=['id'=>$category_option['id'], 'name'=>$category_option['name'], 'number'=>$category_option['number'], 'dependent_options'=>$dependent_options];
            }
            $table_options[] = ['category_id'=>$diagnosis_category['id'],'category_name'=>$diagnosis_category['title'],'category_number'=>$diagnosis_category['number'],'category_options'=>$options, 'dependent_options'=>$dependent_options];
        }
        return view('reports::hmis_reports.108.index', compact('dates','diagnoses_data','table_options','page'));
    }

    public function hmis_108_mental_health (Request $request) {
        request()->validate([
            'date_from' => 'nullable|date|required_with:date_to',
            'date_to' => 'bail|nullable|required_with:date_from|date|after:date_from',
        ]);
        $start = !empty($request->date_from)? $request->date_from : Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d');
        $end   = !empty($request->date_to)? $request->date_to : date("Y-m-t", strtotime($start));
        $citizenship = isset($request->type)? $request->type :1;
        $page = 'Mental Health';
        $dates = $investigation_ids = $patients = $primary_diagnosis = $table_options = $other_diagnoses = $diagnoses_data =[];
        if(empty($request->date_from)){
            $dates['last_month'] = date('F', strtotime($start));
            $dates['last_year'] = date('Y', strtotime($start));
            $dates['days'] = date('t', strtotime($start));
            $dates['citizenship'] = $citizenship;
        }else {
            $dates['start'] = date('d-M-Y', strtotime($start));
            $dates['end'] = date('d-M-Y', strtotime($end));
            $dates['days'] = intval((new DateTime($start))->diff(new DateTime($end))->format("%a"));
            $dates['citizenship'] = $citizenship;
        }
        $episodes =  DB::table('inpatient_info as i')->leftJoin('patients as p', 'i.patient_id','p.id')
            ->select('i.id','i.patient_id', 'gender', 'date_of_birth as dob','admitted_on', 'outcome_id', 'primary_diagnosis', 'other_diagnoses')
            ->whereBetween('admitted_on', [$start, $end])
            ->where([['gender','<>',''], ['primary_diagnosis','<>',''], ['citizenship', $citizenship]])
            ->get();
        foreach ($episodes as $episode) {
            $primary_diagnosis[] = $episode->primary_diagnosis;
            $others = (!empty($episode->other_diagnoses))? unserialize($episode->other_diagnoses):[];
            if(!empty($others)) foreach($others as $other) if(!empty($other)) $other_diagnoses[] = $other;
            $patients[] = ['id'=> $episode->id,'patient_id'=> $episode->patient_id, 'gender'=> $episode->gender, 'age'=> Carbon::parse($episode->dob)->age,'admitted_on'=> $episode->admitted_on, 'outcome_id'=> $episode->outcome_id, 'primary_diagnosis'=> $episode->primary_diagnosis, 'other_diagnoses'=> $others];
        }
        $all_diagnoses = array_unique(array_merge($primary_diagnosis,$other_diagnoses));
        $diag = Diagnosis::where([['hmis_category','<>',''],['hmis_no_inpatient','<>','']])->whereIn('id', $all_diagnoses)->select('id','name', 'hmis_no_inpatient','hmis_category','dependent_option')->get();
        foreach($diag as $dia) {
            $diagnosis = $dia->id;$other = [];
            $primary = array_filter($patients, function ($item) use ($diagnosis) {
                return $item['primary_diagnosis'] == $diagnosis;
            });
            foreach ($patients as $patient) if(!empty($patient['other_diagnoses']) && in_array($diagnosis, $patient['other_diagnoses'])) $other[] =$patient;
            $diagnoses_data[]=['id'=>$diagnosis,'name'=>$dia->name, 'hmis_category'=>$dia->hmis_category, 'hmis_category_option'=>$dia->hmis_no_inpatient, 'dependent_option'=>$dia->dependent_option, 'primary'=>$primary, 'other'=>$other];
        }

        $diagnosis_categories = HmisCategory::where('section_number', '7')->select('id','number', 'title')->get()->toArray();
        foreach($diagnosis_categories as $diagnosis_category) {
            $options=[];
            $category_options = HmisCategoryOptions::where('hmis_category_id', $diagnosis_category['id'])->select('id','number', 'name')->get()->toArray();
            foreach($category_options as $category_option) $options[]=['id'=>$category_option['id'], 'name'=>$category_option['name'], 'number'=>$category_option['number']];

            $table_options[] = ['category_id'=>$diagnosis_category['id'],'category_name'=>$diagnosis_category['title'],'category_number'=>$diagnosis_category['number'],'category_options'=>$options];
        }
        return view('reports::hmis_reports.108.index', compact('dates','diagnoses_data','table_options','page'));
    }

    public function hmis_108_special_services (Request $request) {
        request()->validate([
            'date_from' => 'nullable|date|required_with:date_to',
            'date_to' => 'bail|nullable|required_with:date_from|date|after:date_from',
        ]);
        $start = !empty($request->date_from)? $request->date_from : Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d');
        $end   = !empty($request->date_to)? $request->date_to : date("Y-m-t", strtotime($start));
        $citizenship = isset($request->type)? $request->type :1;
        $page = 'Utilization of Special Services';$table_options =[];
        $dates = $investigation_ids = $counted_investigations =[];
        if(empty($request->date_from)){
            $dates['last_month'] = date('F', strtotime($start));
            $dates['last_year'] = date('Y', strtotime($start));
            $dates['days'] = date('t', strtotime($start));
            $dates['citizenship'] = $citizenship;
        }else {
            $dates['start'] = date('d-M-Y', strtotime($start));
            $dates['end'] = date('d-M-Y', strtotime($end));
            $dates['days'] = intval((new DateTime($start))->diff(new DateTime($end))->format("%a"));
            $dates['citizenship'] = $citizenship;
        }
        return view('reports::hmis_reports.108.index', compact('dates','counted_investigations','table_options','page'));
    }

    public function hmis_108_others (Request $request) {
        request()->validate([
            'date_from' => 'nullable|date|required_with:date_to',
            'date_to' => 'bail|nullable|required_with:date_from|date|after:date_from',
        ]);
        $start = !empty($request->date_from)? $request->date_from : Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d');
        $end   = !empty($request->date_to)? $request->date_to : date("Y-m-t", strtotime($start));
        $citizenship = isset($request->type)? $request->type :1;
        $page = 'others'; $dates = $table_options =[];$counter = 0;
        $alc_male_10_to_19 = $alc_male_over_25 = $alc_male_over_25 = $tob_male_10_to_19=$tob_male_20_to_24=$tob_male_over_25=$alc_female_10_to_19=$alc_female_20_to_24=$alc_female_over_25=$tob_female_10_to_19=$tob_female_20_to_24=$tob_female_over_25 = [];
        if(empty($request->date_from)){
            $dates['last_month'] = date('F', strtotime($start));
            $dates['last_year'] = date('Y', strtotime($start));
            $dates['days'] = date('t', strtotime($start));
            $dates['citizenship'] = $citizenship;
        }else {
            $dates['start'] = date('d-M-Y', strtotime($start));
            $dates['end'] = date('d-M-Y', strtotime($end));
            $dates['days'] = intval((new DateTime($start))->diff(new DateTime($end))->format("%a"));
            $dates['citizenship'] = $citizenship;
        }
        $risky_behaviours = DB::table('inpatient_info as i')->leftJoin('patient_episodes as e', 'i.episode_id','e.id')
            ->leftJoin('triage as t', 't.id','e.triage_id')->leftJoin('patients as p', 'i.patient_id','p.id')
            ->select('i.id','i.patient_id', 't.id as triage_id', 't.observations','p.gender', 'p.date_of_birth as dob','admitted_on')
            ->where([['observations','<>',''],['gender','<>',''],['citizenship', $citizenship]])->whereBetween('i.admitted_on', [$start, $end])
            ->orWhere([ ['observations', 'LIKE', '%Alcohol use=High%'],
                ['observations', 'LIKE', '%Alcohol use=Moderate%'],
                ['observations', 'LIKE', '%Alcohol use=Low%'],
                ['observations', 'LIKE', '%Tobacco use=High%'],
                ['observations', 'LIKE', '%Tobacco use=Moderate%'],
                ['observations', 'LIKE', '%Tobacco use=Low%']
            ])
            ->orderBy('patient_id', 'desc')
            ->get();
        foreach($risky_behaviours as $risky_behaviour){
            $age = Carbon::parse($risky_behaviour->dob)->age;
            $observations = explode(',', $risky_behaviour->observations);
            if($risky_behaviour->gender == '1'){
                if($age >9 && $age < 20 && (in_array('Alcohol use=High', $observations) || in_array('Alcohol use=Moderate', $observations) || in_array('Alcohol use=Low', $observations))) $alc_male_10_to_19[] = $risky_behaviour->patient_id;
                elseif ($age >19 && $age < 25 && (in_array('Alcohol use=High', $observations) || in_array('Alcohol use=Moderate', $observations) || in_array('Alcohol use=Low', $observations))) $alc_male_20_to_24[] = $risky_behaviour->patient_id;
                elseif ($age > 24 && (in_array('Alcohol use=High', $observations) || in_array('Alcohol use=Moderate', $observations) || in_array('Alcohol use=Low', $observations))) $alc_male_over_25[] = $risky_behaviour->patient_id;
                elseif($age >9 && $age < 20 && (in_array('Tobacco use=High', $observations) || in_array('Tobacco use=Moderate', $observations) || in_array('Tobacco use=Low', $observations))) $tob_male_10_to_19[] = $risky_behaviour->patient_id;
                elseif ($age >19 && $age < 25 && (in_array('Tobacco use=High', $observations) || in_array('Tobacco use=Moderate', $observations) || in_array('Tobacco use=Low', $observations))) $tob_male_20_to_24[] = $risky_behaviour->patient_id;
                elseif ($age > 24 && (in_array('Tobacco use=High', $observations) || in_array('Tobacco use=Moderate', $observations) || in_array('Tobacco use=Low', $observations))) $tob_male_over_25[] = $risky_behaviour->patient_id;
            }elseif ($risky_behaviour->gender == '2') {
                if($age >9 && $age < 20 && (in_array('Alcohol use=High', $observations) || in_array('Alcohol use=Moderate', $observations) || in_array('Alcohol use=Low', $observations))) $alc_female_10_to_19[] = $risky_behaviour->patient_id;
                elseif ($age >19 && $age < 25 && (in_array('Alcohol use=High', $observations) || in_array('Alcohol use=Moderate', $observations) || in_array('Alcohol use=Low', $observations))) $alc_female_20_to_24[] = $risky_behaviour->patient_id;
                elseif ($age > 24 && (in_array('Alcohol use=High', $observations) || in_array('Alcohol use=Moderate', $observations) || in_array('Alcohol use=Low', $observations))) $alc_female_over_25[] = $risky_behaviour->patient_id;
                elseif($age >9 && $age < 20 && (in_array('Tobacco use=High', $observations) || in_array('Tobacco use=Moderate', $observations) || in_array('Tobacco use=Low', $observations))) $tob_female_10_to_19[] = $risky_behaviour->patient_id;
                elseif ($age >19 && $age < 25 && (in_array('Tobacco use=High', $observations) || in_array('Tobacco use=Moderate', $observations) || in_array('Tobacco use=Low', $observations))) $tob_female_20_to_24[] = $risky_behaviour->patient_id;
                elseif ($age > 24 && (in_array('Tobacco use=High', $observations) || in_array('Tobacco use=Moderate', $observations) || in_array('Tobacco use=Low', $observations))) $tob_female_over_25[] = $risky_behaviour->patient_id;
            }
        }
        $table_options[$counter] = ['alc_male_10_to_19'=>$alc_male_10_to_19,'alc_male_20_to_24'=>$alc_male_over_25,'alc_male_over_25'=>$alc_male_over_25, 'tob_male_10_to_19'=>$tob_male_10_to_19,'tob_male_20_to_24'=>$tob_male_20_to_24,'tob_male_over_25'=>$tob_male_over_25,
            'alc_female_10_to_19'=>$alc_female_10_to_19,'alc_female_20_to_24'=>$alc_female_20_to_24,'alc_female_over_25'=>$alc_female_over_25,'tob_female_10_to_19'=>$tob_female_10_to_19,'tob_female_20_to_24'=>$tob_female_20_to_24,'tob_female_over_25'=>$tob_female_over_25];
        $counter++; $totals= $diagnosed = $presumptive = $screened = [];

        $tuberculosis_screenings = DB::table('inpatient_info as i')->leftJoin('patient_episodes as e', 'i.episode_id','e.id')
            ->leftJoin('triage as t', 't.id','e.triage_id')->leftJoin('patients as p', 'i.patient_id','p.id')
            ->select('i.id','i.patient_id', 't.id as triage_id', 't.observations','p.gender', 'any_tb_sysmptoms','p.date_of_birth as dob','admitted_on', 'tb_status_assessment')
            ->where([['any_tb_sysmptoms','<>',''],['gender','<>',''],['tb_status_assessment','<>','']])->whereBetween('i.admitted_on', [$start, $end])
            ->orderBy('patient_id', 'desc')
            ->get()->toArray();
        foreach ($tuberculosis_screenings as $key => $tuberculosis_screening) {
            $age = Carbon::parse($tuberculosis_screening->dob)->age;
            $totals['total_tb_screened'][] = $tuberculosis_screening->patient_id;
            if ($tuberculosis_screening->tb_status_assessment == '1') $totals['total_tb_diagnosed'][] = $tuberculosis_screening->patient_id;
            if ($tuberculosis_screening->any_tb_sysmptoms == '1') $totals['total_tb_presumptive'][] = $tuberculosis_screening->patient_id;
            if ($age < 10) {
                if ($tuberculosis_screening->gender == '1') {
                    $screened['male_screened_0_9'][] = $tuberculosis_screening->patient_id;
                    if($tuberculosis_screening->tb_status_assessment == '1') $diagnosed['male_diagnosed_0_9'][] = $tuberculosis_screening->patient_id;
                    if ($tuberculosis_screening->any_tb_sysmptoms == '1') $presumptive['male_presumptive_0_9'][] = $tuberculosis_screening->patient_id;
                } elseif ($tuberculosis_screening->gender == '2') {
                    $screened['female_screened_0_9'][] = $tuberculosis_screening->patient_id;
                    if($tuberculosis_screening->tb_status_assessment == '1') $diagnosed['female_diagnosed_0_9'][] = $tuberculosis_screening->patient_id;
                    if ($tuberculosis_screening->any_tb_sysmptoms == '1') $presumptive['female_presumptive_0_9'][] = $tuberculosis_screening->patient_id;
                }
            } elseif ($age >9 && $age < 15) {
                if ($tuberculosis_screening->gender == '1') {
                    $screened['male_screened_10_14'][] = $tuberculosis_screening->patient_id;
                    if($tuberculosis_screening->tb_status_assessment == '1') $diagnosed['male_diagnosed_10_14'][] = $tuberculosis_screening->patient_id;
                    if ($tuberculosis_screening->any_tb_sysmptoms == '1') $presumptive['male_presumptive_10_14'][] = $tuberculosis_screening->patient_id;
                } elseif ($tuberculosis_screening->gender == '2') {
                    $screened['female_screened_10_14'][] = $tuberculosis_screening->patient_id;
                    if($tuberculosis_screening->tb_status_assessment == '1') $diagnosed['female_diagnosed_10_14'][] = $tuberculosis_screening->patient_id;
                    if ($tuberculosis_screening->any_tb_sysmptoms == '1') $presumptive['female_presumptive_10_14'][] = $tuberculosis_screening->patient_id;
                }
            }elseif ($age >14 && $age < 20) {
                if ($tuberculosis_screening->gender == '1') {
                    $screened['male_screened_15_19'][] = $tuberculosis_screening->patient_id;
                    if($tuberculosis_screening->tb_status_assessment == '1') $diagnosed['male_diagnosed_15_19'][] = $tuberculosis_screening->patient_id;
                    if ($tuberculosis_screening->any_tb_sysmptoms == '1') $presumptive['male_presumptive_15_19'][] = $tuberculosis_screening->patient_id;
                } elseif ($tuberculosis_screening->gender == '2') {
                    $screened['female_screened_15_19'][] = $tuberculosis_screening->patient_id;
                    if($tuberculosis_screening->tb_status_assessment == '1') $diagnosed['female_diagnosed_15_19'][] = $tuberculosis_screening->patient_id;
                    if ($tuberculosis_screening->any_tb_sysmptoms == '1') $presumptive['female_presumptive_15_19'][] = $tuberculosis_screening->patient_id;
                }
            } else {
                if ($tuberculosis_screening->gender == '1') {
                    $screened['male_screened_over_20'][] = $tuberculosis_screening->patient_id;
                    if($tuberculosis_screening->tb_status_assessment == '1') $diagnosed['male_diagnosed_over_20'][] = $tuberculosis_screening->patient_id;
                    if ($tuberculosis_screening->any_tb_sysmptoms == '1') $presumptive['male_presumptive_over_20'][] = $tuberculosis_screening->patient_id;
                } elseif ($tuberculosis_screening->gender == '2') {
                    $screened['female_screened_over_20'][] = $tuberculosis_screening->patient_id;
                    if($tuberculosis_screening->tb_status_assessment == '1') $diagnosed['female_diagnosed_over_20'][] = $tuberculosis_screening->patient_id;
                    if ($tuberculosis_screening->any_tb_sysmptoms == '1') $presumptive['female_presumptive_over_20'][] = $tuberculosis_screening->patient_id;
                }
            }


        }
        $table_options[$counter] =['totals'=>$totals, 'diagnosed'=>$diagnosed, 'presumptive'=>$presumptive,'screened'=>$screened];
        $counter++;

        return view('reports::hmis_reports.108.index', compact('dates','table_options','page'));
    }

    //HMIS 033b: HEALTH UNIT WEEKLY EPIDEMIOLOGICAL SURVEILLANCE REPORT
    public function weekly_cases_and_deaths (Request $request){
        request()->validate([
            'date_from' => 'nullable|date|required_with:date_to',
            'date_to' => 'bail|nullable|required_with:date_from|date|after:date_from',
        ]);
        $start = !empty($request->date_from)? Carbon::parse($request->date_from)->startOfDay()->toDateString() : Carbon::now()->subWeek()->startOfWeek()->toDateString();
        $end   = !empty($request->date_to)? Carbon::parse($request->date_to)->endOfDay()->toDateString() : Carbon::parse($start)->endOfWeek()->toDateString();
        $week_number = empty($request->date_to)? idate('W', strtotime($start)):'';
        $hospital_information = HospitalInformation::find(1);
        $diagnoses_data = $other_diagnoses_data = [];
        $diagnoses = [
            ['name'=>'Malaria (diagnosed)', 'code'=>'MA', 'icd10'=>['B54', 'B531','B538'],'group'=>'1'],
            ['name'=>'Dysentery', 'code'=>'DY', 'icd10'=>['A03.9'],'group'=>'1'],
            ['name'=>'Severe Acute Respiratory Infection (SARI)', 'code'=>'SA', 'icd10'=>['U04'],'group'=>'1'],
            ['name'=>'Acute Flaccid Paralysis', 'code'=>'AF', 'icd10'=>['A80'],'group'=>'1'],
            ['name'=>'Adverse Events Following Immunization (AEFI)', 'code'=>'AE', 'icd10'=>['T80'],'group'=>'1'],
            ['name'=>'Animal Bites (suspected rabies)', 'code'=>'AB', 'icd10'=>['A82'],'group'=>'1'],
            ['name'=>'Bacterial Meningitis', 'code'=>'MG', 'icd10'=>['KH987'],'group'=>'1'],
            ['name'=>'Cholera', 'code'=>'CH', 'icd10'=>['A00','A001', 'A009'],'group'=>'1'],
            ['name'=>'Guinea Worm', 'code'=>'GW', 'icd10'=>['B72'],'group'=>'1'],
            ['name'=>'Measles', 'code'=>'ME', 'icd10'=>['B05','B050','B051','B052','B053','B054','B0581','B0589', 'B059'],'group'=>'1'],
            ['name'=>'Neonatal tetanus', 'code'=>'NT', 'icd10'=>['A33', 'A34', 'A35'],'group'=>'1'],
            ['name'=>'Plague', 'code'=>'PL', 'icd10'=>['A20'],'group'=>'1'],
            ['name'=>'Typhoid Fever', 'code'=>'TF', 'icd10'=>['A01.0'],'group'=>'1'],
            ['name'=>'Hepatitis B', 'code'=>'HB', 'icd10'=>['B180', 'B181', 'B191', 'B182', 'B192'],'group'=>'1'],
            ['name'=>'Rifampicin resistant TB cases', 'code'=>'DR', 'icd10'=>['U50.2'],'group'=>'1'],
            ['name'=>'Yellow Fever', 'code'=>'YF', 'icd10'=>['A95'],'group'=>'1'],
            ['name'=>'Other Viral Hemorrhagic Fevers (EVD, MVD, RVF, CCHF)', 'code'=>'VF', 'icd10'=>[],'group'=>'1'],
            ['name'=>'Leprosy', 'code'=>'LP', 'icd10'=>['A30'],'group'=>'1'],
            ['name'=>'Anthrax', 'code'=>'AX', 'icd10'=>['A22'],'group'=>'1'],
            ['name'=>'Maternal death', 'code'=>'MD', 'icd10'=>[],'group'=>'1'],
            ['name'=>'Macerated Still births', 'code'=>'MB', 'icd10'=>[],'group'=>'1'],
            ['name'=>'Fresh Still Birth', 'code'=>'FB', 'icd10'=>[],'group'=>'2'],
            ['name'=>'Early Neonatal deaths 0-7 days', 'code'=>'ND', 'icd10'=>[],'group'=>'1'],

            ['name'=>'Chikungunya', 'code'=>'CG', 'icd10'=>['A920'],'group'=>'2'],
            ['name'=>'Dengue', 'code'=>'DG', 'icd10'=>['A90', 'A91'],'group'=>'2'],
            ['name'=>'Influenza-like Illness', 'code'=>'IL', 'icd10'=>[],'group'=>'2'],
            ['name'=>'Acute viral hepatitis', 'code'=>'HP', 'icd10'=>['B179'],'group'=>'2'],
            ['name'=>'Dracunculiasis', 'code'=>'DC', 'icd10'=>['B72'],'group'=>'2'],
            ['name'=>'Onchocerciasis', 'code'=>'OC', 'icd10'=>['B7300','B7301','B7302','B7309','B731'],'group'=>'2'],
            ['name'=>'Buruli ulcer', 'code'=>'BU', 'icd10'=>[],'group'=>'2'],
            ['name'=>'Lymphatic Filariasis', 'code'=>'LF', 'icd10'=>['B74.0','B74.9'],'group'=>'2'],
            ['name'=>'Noma', 'code'=>'NO', 'icd10'=>[],'group'=>'2'],
            ['name'=>'Human Influenza due to a new subtype', 'code'=>'HN', 'icd10'=>[],'group'=>'2'],
            ['name'=>'Severe Acute Respiratory Syndrome (SARS)', 'code'=>'SS', 'icd10'=>['U04.9'],'group'=>'2'],
            ['name'=>'Smallpox', 'code'=>'SP', 'icd10'=>['B03'],'group'=>'2'],
            ['name'=>'Diarrhoea with dehydration <5', 'code'=>'DD', 'icd10'=>['A09.0'],'group'=>'2'],
            ['name'=>'Severe pneumonia <5', 'code'=>'PN', 'icd10'=>[],'group'=>'2'],
            ['name'=>'Human African Trypanosomiasis', 'code'=>'TX', 'icd10'=>['B569'],'group'=>'2'],
            ['name'=>'Trachoma', 'code'=>'TR', 'icd10'=>['B940'],'group'=>'2'],
            ['name'=>'Schistosomiasis', 'code'=>'SC', 'icd10'=>['B650','B651','B652','B658','B659'],'group'=>'2'],
            ['name'=>'Diphtheria', 'code'=>'DP', 'icd10'=>['A360','A361','A362','A363', 'A369'],'group'=>'2'],
            ['name'=>'Pertussis (Whooping cough)', 'code'=>'WC', 'icd10'=>['A3700','A3701'],'group'=>'2'],
            ['name'=>'Brucellosis', 'code'=>'BC', 'icd10'=>['A230','A231','A232','A233','A238','A239','A23.9'],'group'=>'2'],
            ['name'=>'Kala azar', 'code'=>'KA', 'icd10'=>['B55','B550','B551','B552','B559'],'group'=>'2'],
            ['name'=>'Nodding Syndrome', 'code'=>'NS', 'icd10'=>[],'group'=>'2'],
            ['name'=>'Adverse Drug Reactions (ADR)', 'code'=>'AR', 'icd10'=>[],'group'=>'2'],
        ];
        foreach ($diagnoses as $diagnosis) {
            $diagnosis_ids = !empty($diagnosis['icd10'])? Diagnosis::whereIn('icd10_code', $diagnosis['icd10'])->pluck('id')->toArray():[];
            $case_ids = $death_ids =[];
            foreach($diagnosis_ids as $id){
                $inpatient_episodes = InpatientInfo::whereBetween('admitted_on', [$start, $end])->where(function ($query) use($id) { $query->where('primary_diagnosis', $id)->orWhere('other_diagnoses', 'LIKE', '%'.$id.'%'); })->select('id', 'patient_id', 'primary_diagnosis', 'other_diagnoses', 'outcome_id');
                $outpatient_episodes = Consultation::whereBetween('created_at', [$start, $end])->where(function ($query) use($id) { $query->where('primary_diagnosis', $id)->orWhere('other_diagnoses', 'LIKE', '%'.$id.'%'); })->select('id', 'patient_id', 'primary_diagnosis', 'other_diagnoses', 'outcome_id')->union($inpatient_episodes)->get();
                foreach ($outpatient_episodes as $episodes){
                    if(in_array($episodes->primary_diagnosis, $diagnosis_ids)){
                        if($episodes->outcome_id == '5') $death_ids[] = $episodes->patient_id;
                        $case_ids[] = $episodes->patient_id;
                    } else {
                        $other_ids = unserialize($episodes->other_diagnoses);
                        if(!empty($other_ids[0])) if(in_array($id, $other_ids)) {
                            if($episodes->outcome_id == '5') $death_ids[] = $episodes->patient_id;
                            $case_ids[] = $episodes->patient_id;
                        }
                    }
                }
            }
            if($diagnosis['group'] == '2' && (!empty($case_ids) || !empty($death_ids))) $other_diagnoses_data[] = ['name'=>$diagnosis['name'], 'code'=>$diagnosis['code'], 'deaths'=>array_unique($death_ids), 'cases'=>array_unique($case_ids)];
            elseif($diagnosis['group'] == '1') $diagnoses_data[]=['name'=>$diagnosis['name'], 'code'=>$diagnosis['code'], 'deaths'=>array_unique($death_ids), 'cases'=>array_unique($case_ids)];
        }
        $new_attendance = $all_attendances = $rdt_data = $tb_episodes = $tb_data = [];
        $attendances = Triage::whereBetween('created_at', [$start, $end])->select('patient_id', 're_attendance', 'new_attendance','any_tb_sysmptoms', 'episode_id')->get();
        $rdt_tests = Consultation::whereBetween('consultations.created_at', [$start, $end])->whereIn('rdt', [0,1])->leftJoin('treatments', 'treatments.episode_id','consultations.episode_id')->select('consultations.patient_id', 'dispense_status', 'rdt')->get();
        foreach ($attendances as $attendance) {
            if($attendance->new_attendance == '1') $new_attendance[] = $attendance->patient_id;
            $all_attendances[] = $attendance->patient_id;
            if(!is_null($attendance->any_tb_sysmptoms)) {
                if($attendance->any_tb_sysmptoms == '1') $tb_presumptive[] = $attendance->patient_id;
                $tb_cases[] = $attendance->patient_id;
                $tb_episodes[] = $attendance->episode_id;
            }
        }
        foreach ($rdt_tests as $rdt_test) {
            $rdt_cases[] = $rdt_test->patient_id;
            if(!empty($rdt_test->rdt)) {
                $positive_rdt[] = $rdt_test->patient_id;
                if($rdt_test->dispense_status == '1') $positive_rdt_treated[] = $rdt_test->patient_id;
            }
            elseif ($rdt_test->rdt =='0' && $rdt_test->dispense_status == '1') $negative_rdt_treated[] = $rdt_test->patient_id;
        }
        if (!empty($tb_episodes)) { $episodes=[];
            $episodes[] =Consultation::whereIn('consultations.episode_id', $tb_episodes)->leftJoin('treatments', 'treatments.episode_id','consultations.episode_id')->select('consultations.patient_id', 'dispense_status', 'tb_status_assessment')->get()->toArray();
            $episodes[] =InpatientInfo::whereIn('inpatient_info.episode_id', $tb_episodes)->leftJoin('treatments', 'treatments.episode_id','inpatient_info.episode_id')->select('inpatient_info.patient_id', 'dispense_status', 'tb_status_assessment')->get()->toArray();
            foreach ($episodes as $episode) {
                foreach ($episode as $value) {
                    if ($value['tb_status_assessment'] == '1') {
                        $def_tb[] = $value['patient_id'];
                        if ($value['dispense_status'] == '1') $tb_treated[] = $value['patient_id'];
                    }
                }
            }
        }
        $rdt_data['rdt_cases'] = $rdt_cases ?? [];
        $rdt_data['positive_rdt'] = $positive_rdt ?? [];
        $rdt_data['positive_rdt_treated'] = $positive_rdt_treated ?? [];
        $rdt_data['negative_rdt_treated'] = $negative_rdt_treated ?? [];
        $tb_data['tb_cases'] = $tb_cases ?? [];
        $tb_data['tb_presumptive'] = $tb_presumptive ?? [];
        $tb_data['def_tb'] = $def_tb ?? [];
        $tb_data['tb_treated'] = $tb_treated ?? [];

        return view('reports::hmis_reports.033.cases_and_deaths', compact('start','end', 'week_number','hospital_information', 'diagnoses_data', 'other_diagnoses_data', 'new_attendance', 'all_attendances','rdt_data','tb_data'));

    }

    public function print_hmis_033b (Request $request){
        $diagnoses_data = !empty($request->diagnoses_data)? unserialize($request->diagnoses_data):[];
        $other_diagnoses_data = !empty($request->other_diagnoses_data)? unserialize($request->other_diagnoses_data):[];
        $start = $request->start;
        $end = $request->end;
        $week_number = !empty($request->week_number)? $request->week_number:'';
        $pdf = SnappyPDF::loadView('reports::hmis_reports.033.print_033b', compact('start','end', 'week_number','diagnoses_data','other_diagnoses_data'))
            ->setOrientation('portrait')
            ->setOption('margin-bottom', 7)
            ->setOption('margin-top', 5)
            ->setOption('footer-html', '<i>'. config('app.name') .' - Printed On ' . date('Y-m-d') . ' By ' . auth()->user()->first_name . " " . auth()->user()->last_name . '</i>');
        return $pdf->inline(__('hmis_reports.hmis_108_report_census') . date(" d-m-y h:ia") . '.pdf');
    }
}