stagfe2/app/Http/Controllers/AdminController.php

<?php

namespace App\Http\Controllers;

use App\Models\Appointment;
use App\Models\BDTDevice;
use App\Models\CareMonth;
use App\Models\Client;
use App\Models\ClientBDTDevice;
use App\Models\ClientInfoLine;
use App\Models\Erx;
use App\Models\Facility;
use App\Models\Handout;
use App\Models\IncomingReport;
use App\Models\MBClaim;
use App\Models\MBPayer;
use App\Models\Note;
use App\Models\NoteTemplate;
use App\Models\Pro;
use App\Models\Product;
use App\Models\ProProAccess;
use App\Models\SectionTemplate;
use App\Models\Shipment;
use App\Models\SupplyOrder;
use App\Models\Ticket;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use App\Models\Bill;
use App\Models\ClientSMS;

use Illuminate\Support\Facades\Http;
use PDF;

class AdminController extends Controller
{

    public function patients(Request $request)
    {
        $filters = $request->all();
        $patients = Client::whereNull('shadow_pro_id');

       
        if ($request->input('name')) {
            $name = trim($request->input('name'));
            if ($name) {
                $patients = $patients->where(function ($q) use ($name) {
                    $q->where('name_first', 'ILIKE', '%' . $name . '%')
                        ->orWhere('name_last', 'ILIKE', '%' . $name . '%');
                });
            }
        }

        if ($request->input('mcp')) {
            if($request->input('mcp') == 'NO_MCP'){
                $patients = $patients->whereNull('mcp_pro_id');
            }else{
                $mcp = Pro::where('uid', trim($request->input('mcp')))->first();
                if ($mcp) {
                    $patients = $patients->where('mcp_pro_id', $mcp->id);
                }
            }
        }

        if ($request->input('na')) {
            if($request->input('na') == 'NO_NA'){
                $patients = $patients->whereNull('default_na_pro_id');
            }else{
                $na = Pro::where('uid', trim($request->input('na')))->first();
                if ($na) {
                    $patients = $patients->where('default_na_pro_id', $na->id);
                }
            }
           
        }

        if ($request->input('ob')) {
            if ($request->input('ob') == 'yes') {
                $patients = $patients->where('has_mcp_done_onboarding_visit', 'YES');
            } else {
                $patients = $patients->where('has_mcp_done_onboarding_visit', '!=', 'YES');
            }
        }

        if ($request->input('next_appointment_category')) {
            if($request->input('next_appointment_category') == 'NONE'){
                $patients = $patients->whereNull('next_mcp_appointment_id');
            }else{
                $self = $this;
                $patients = $patients->whereHas('nextMcpAppointment', function($pQry) use ($request, $self){
                    return $self->filterMultiQuery($request, $pQry, 'raw_date', 'next_appointment_category', 'next_appointment_value_1', 'next_appointment_value_2');
                });
            }
        }

        if ($request->input('chart_number')) {
            $patients = $patients->where('chart_number', 'ILIKE' , '%'.$request->input('chart_number').'%');
        }

        if ($request->input('home_address_state')) {

            if($request->input('home_address_state') == 'NONE'){
                $patients = $patients->whereRaw("mailing_address_state IS NULL OR TRIM(BOTH FROM mailing_address_state = ''");
            }else if($request->input('home_address_state') == 'NOT_MD'){
                $patients = $patients->whereRaw("(TRIM(BOTH FROM mailing_address_state) NOT ILIKE 'MD' AND TRIM(BOTH FROM mailing_address_state) NOT ILIKE 'MARYLAND')");
            }else{
                $patients = $patients->whereRaw("TRIM(BOTH FROM mailing_address_state) = '" . $request->input('home_address_state') . "'");
            }
        }

        $this->filterMultiQuery($request, $patients, 'age_in_years', 'age_category', 'age_value_1', 'age_value_2', false);
        $this->filterSimpleQuery($request, $patients, 'sex', 'sex');
        $this->filterMultiQuery($request, $patients, 'usual_bmi_max', 'bmi_category', 'bmi_value_1', 'bmi_value_2', false);
        $this->filterMultiQuery($request, $patients, 'most_recent_weight_at', 'last_weighed_in_category', 'last_weighed_in_value_1', 'last_weighed_in_value_2');
        $this->filterMultiQuery($request, $patients, 'most_recent_bp_at', 'last_bp_category', 'last_bp_value_1', 'last_bp_value_2');
        $this->filterMultiQuery($request, $patients, 'created_at', 'created_at', 'created_at_value_1', 'created_at_value_2');
        $this->filterMultiQuery($request, $patients, 'most_recent_completed_mcp_note_date', 'last_visit_category', 'last_visit_value_1', 'last_visit_value_2');

        $hasEmail = $request->input('has_email');
        if($hasEmail) {
            if($hasEmail === 'YES') {
                $patients = $patients->whereRaw("(email_address IS NOT NULL AND TRIM(email_address) != '')");
            }
            else {
                $patients = $patients->whereRaw("(email_address IS NULL OR TRIM(email_address) = '')");
            }
        }

        $hasAccount = $request->input('has_account');
        if($hasAccount) {
            if($hasAccount === 'YES') {
                $patients = $patients->whereRaw("((SELECT COUNT(ac.id) FROM account_client ac WHERE ac.client_id = client.id) > 0)");
            }
            else {
                $patients = $patients->whereRaw("((SELECT COUNT(ac.id) FROM account_client ac WHERE ac.client_id = client.id) = 0)");
            }
        }

        if($request->input('number_of_measurements')){
            $keyName = $request->input('number_of_measurements');
            $measurementCountQuery = '(SELECT COUNT(*) FROM measurement WHERE measurement.client_id = client.id AND is_active IS TRUE AND is_cellular IS TRUE AND is_cellular_zero IS NOT TRUE)';
            switch($keyName) {
                case 'EXACTLY':
                    if($request->input('number_of_measurements_value_1')) {
                        $patients->whereRaw($measurementCountQuery . '='.$request->input('number_of_measurements_value_1'));
                    }
                    break;
                case 'LESS_THAN':
                    if($request->input('number_of_measurements_value_1')) {
                        $patients->whereRaw($measurementCountQuery . '<='.$request->input('number_of_measurements_value_1'));
                    }
                    break;
                case 'GREATER_THAN':
                    if($request->input('number_of_measurements_value_1')) {
                        $patients->whereRaw($measurementCountQuery . '>='.$request->input('number_of_measurements_value_1'));
                    }
                    break;
                case 'BETWEEN':
                    if($request->input('number_of_measurements_value_1') && $request->input('number_of_measurements_value_2')) { 
                        $patients->whereRaw($measurementCountQuery.'>='.$request->input('number_of_measurements_value_1') .' AND '. $measurementCountQuery . '<='.$request->input('number_of_measurements_value_2'));
                    }
                    break;
                case 'NOT_BETWEEN':
                    if($request->input('number_of_measurements_value_1') && $request->input('number_of_measurements_value_2')) {
                        $patients->where(function ($q) use ($request, $measurementCountQuery) {
                                $q->whereRaw($measurementCountQuery . '<'.$request->input('number_of_measurements_value_1') .' OR '. $measurementCountQuery . '>'.$request->input('number_of_measurements_value_2'));                               
                        });
                    }
                    break;
            }
        }

        $status = $request->input('status');
        if($status){
            if($status === 'ACTIVE'){
                $patients->where('is_active', true)->where(function($q) use ($status){
                    return $q->where('client_engagement_status_category', $status)
                    ->orWhereNull('client_engagement_status_category');
                });    
            }elseif($status === 'NONE'){
                $patients->whereNull('client_engagement_status_category');
            }else {
                $patients->where('client_engagement_status_category', $status);
            }

        }

        $initiative = $request->input('initiative');
        if($initiative){
            $wildCardedInitiative = '%'.$initiative.'%';
            $patients->where('initiative', 'ilike', $wildCardedInitiative);
        }

        $include_test_records = $request->input('include_test_records');
        if(!$include_test_records && $status != 'DUMMY'){
            $patients = $patients->where(function ($q) {
                $q->whereNull('client_engagement_status_category')
                    ->orWhere('client_engagement_status_category', '<>', 'DUMMY');
            });
        }

        $zero_deductible = $request->input('zero_deductible');
        if($zero_deductible){
            $patients = $patients->where(function ($q) {
                $q->where('mpb_remaining', 0);
            });
        }

        $insurance = $request->get('insurance');
        if($insurance){
            if($insurance === 'MEDICARE'){
                $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($cpcQuery) {
                    return $cpcQuery->where('is_partbprimary', '=', 'YES');
                });
            }else{
                $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($cpcQuery) use ($insurance){
                    return $cpcQuery->where('commercial_payer_id', '=', $insurance);
                });
            }
        }

        $sortBy = $request->input('sort_by') ?: 'name_first';
        $sortDir = $request->input('sort_dir') ?: 'ASC';

        $sortBySQL = "$sortBy $sortDir NULLS LAST";

        if($sortBy !== 'client_engagement_status_category' && $request->input('status')) {
            $sortBySQL = "client_engagement_status_category DESC NULLS LAST";
        }

        $patients = $patients->orderByRaw($sortBySQL)->paginate(25);

        $insurances = DB::select('SELECT DISTINCT commercial_payer_name, commercial_payer_id FROM client_primary_coverage WHERE commercial_payer_name IS NOT NULL ORDER BY commercial_payer_name ASC');

        return view('app.admin.patients', compact('patients', 'filters', 'insurances'));
    }

    public function partBPatients(Request $request){
        
        $filters = $request->all();
        $patients = Client::whereNull('shadow_pro_id');

      
        if ($request->input('name')) {
            $name = trim($request->input('name'));
            if ($name) {
                $patients = $patients->where(function ($q) use ($name) {
                    $q->where('name_first', 'ILIKE', '%' . $name . '%')
                        ->orWhere('name_last', 'ILIKE', '%' . $name . '%');
                });
            }
        }

        if ($request->input('mcp')) {
            if($request->input('mcp') == 'NO_MCP'){
                $patients = $patients->whereNull('mcp_pro_id');
            }else{
                $mcp = Pro::where('uid', trim($request->input('mcp')))->first();
                if ($mcp) {
                    $patients = $patients->where('mcp_pro_id', $mcp->id);
                }
            }
        }

        if ($request->input('na')) {
            if($request->input('na') == 'NO_NA'){
                $patients = $patients->whereNull('default_na_pro_id');
            }else{
                $na = Pro::where('uid', trim($request->input('na')))->first();
                if ($na) {
                    $patients = $patients->where('default_na_pro_id', $na->id);
                }
            }
           
        }

        if ($request->input('next_appointment_category')) {
            if($request->input('next_appointment_category') == 'NONE'){
                $patients = $patients->whereNull('next_mcp_appointment_id');
            }
        }

        if ($request->input('chart_number')) {
            $patients = $patients->where('chart_number', 'ILIKE' , '%'.$request->input('chart_number').'%');
        }

        if ($request->input('home_address_state')) {

            if($request->input('home_address_state') == 'NONE'){
                $patients = $patients->whereNull('mailing_address_state');
            }else if($request->input('home_address_state') == 'NOT_MD'){
                $patients = $patients->where('mailing_address_state', '<>' , 'MD');
            }else{
                $patients = $patients->where('mailing_address_state', '=' , $request->input('home_address_state'));
            }
        }

        $this->filterMultiQuery($request, $patients, 'age_in_years', 'age_category', 'age_value_1', 'age_value_2', false);
        $this->filterSimpleQuery($request, $patients, 'sex', 'sex');
        $this->filterMultiQuery($request, $patients, 'usual_bmi_max', 'bmi_category', 'bmi_value_1', 'bmi_value_2', false);
        $this->filterMultiQuery($request, $patients, 'most_recent_weight_at', 'last_weighed_in_category', 'last_weighed_in_value_1', 'last_weighed_in_value_2');
        $this->filterMultiQuery($request, $patients, 'most_recent_bp_at', 'last_bp_category', 'last_bp_value_1', 'last_bp_value_2');

        if($request->input('deductible')){
            $keyName = $request->input('deductible');

            switch($keyName) {
                case 'EXACTLY':
                        $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($q) use ($request){
                            return $q->where('auto_medicare_mpb_deductible', '=', $request->input('deductible_value_1'));
                        });
                    break;
                case 'LESS_THAN':
                    if($request->input('deductible_value_1')) {
                        $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($q) use ($request){
                            return $q->where('auto_medicare_mpb_deductible', '<=', $request->input('deductible_value_1'));
                        });
                    }
                    break;
                case 'GREATER_THAN':
                    if($request->input('deductible_value_1')) {
                        $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($q) use ($request){
                            return $q->where('auto_medicare_mpb_deductible', '>=', $request->input('deductible_value_1'));
                        });
                    }
                    break;
                case 'BETWEEN':
                    $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($q) use ($request){
                        return $q->where('auto_medicare_mpb_deductible', '>=', $request->input('deductible_value_1'))
                        ->where('auto_medicare_mpb_deductible', '<=', $request->input('deductible_value_2'));
                    });
                    break;
                case 'NOT_BETWEEN':
                    if($request->input('deductible_value_1') && $request->input('deductible_value_2')) {
                        $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($q) use ($request){

                            return $q->where(function($qq) use ($request){
                                return $qq->where('auto_medicare_mpb_deductible', '<', $request->input('deductible_value_1'))
                                    ->orWhere('auto_medicare_mpb_deductible', '>', $request->input('deductible_value_2'));
                            });
                        });
                    }
                    break;
            }
        }

        switch($request->input('status')) {
            case 'ACTIVE':
                $patients->where('is_active', true)->where('has_mcp_done_onboarding_visit', true);
                break;
            case 'AWAITING_VISIT':
                $patients->where('is_active', true)->where('has_mcp_done_onboarding_visit', false);
                break;
            case 'INACTIVE':
                $patients->where('is_active', '<>', true);
                break;
        }

        $initiative = $request->input('initiative');
        if($initiative){
            $wildCardedInitiative = '%'.$initiative.'%';
            $patients->where('initiative', 'ilike', $wildCardedInitiative);
        }

        $include_test_records = $request->input('include_test_records');
        if(!$include_test_records){
            $patients = $patients->where(function ($q) {
                return $q->whereNull('client_engagement_status_category')
                    ->orWhere('client_engagement_status_category', '<>', 'DUMMY');
            });
        }

        
        $with_claim_not_closed = $request->input('with_claim_not_closed');
        if($with_claim_not_closed){
            $patients = $patients->whereHas('notes', function ($q) {
                return $q->where('is_claim_closed', false)
                ->where('is_signed_by_hcp', true)
                ->where('is_cancelled', false);
            });
        }

        $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($cpcQuery){
            return $cpcQuery->where('is_partbprimary', '=', 'YES');
        });

        $patients = $patients->orderBy('created_at', 'DESC')->paginate(25);
        return view('app.admin.part_b_patients', compact('patients', 'filters'));
    }

    public function notes(Request $request)
    {
        $notes = Note::paginate(5);

        // SELECT * FROM note WHERE client_id IN (SELECT id FROM client WHERE mcp_pro_id = :me.id);

        return view('app.mcp.notes', compact('notes'));
    }

    public function notes_pending_summary_suggestion(Request $request){
        $pro = $this->performer->pro;
        $data = [
            'records' => $pro->get_notes_pending_summary_suggestion_as_admin()
        ];
        return view('app.admin.notes_pending_summary_suggestion', $data);
    }

    public function notes_rejected_summary_suggestion(Request $request){
        $pro = $this->performer->pro;
        $data = [
            'records' => $pro->get_notes_rejected_summary_suggestion_as_admin()
        ];
        return view('app.admin.notes_rejected_summary_suggestion', $data);
    }


    public function appointments(Request $request)
    {
        $appointments = Appointment::paginate(5);
        return view('app.mcp.appointments', compact('appointments'));
    }

    public function bills(Request $request)
    {
        $bills = Bill::paginate(5);
        return view('app.mcp.bills', compact('bills'));
    }

    public function erx_and_orders(Request $request)
    {
        $erxAndOrders = Erx::paginate(5);
        return view('app.mcp.erx_and_orders', compact('erxAndOrders'));
    }

    public function reports(Request $request)
    {
        $data = [];
        return view('app.mcp.reports', $data);
    }

    public function supply_orders(Request $request)
    {
        $supplyOrders = SupplyOrder::paginate(5);
        return view('app.mcp.supply_orders', compact('supplyOrders'));
    }

    public function getCreateNewPatientScriptTemplate(Request $request){
        $template = $request->get('template');
        if(!$template) return $this->fail('No script template');

        $path = resource_path() . '/views/app/patient/create-patient/scripts/' . $template . '.blade.php';
        
        if(!File::exists($path)) return $this->fail('Invalid script template');

        $templateContent = file_get_contents($path);
        return $this->pass($templateContent);
    }
    
    public function bdtDevices(Request $request)
    {
        $filters = $request->all();
        $bdtDevices = BDTDevice::query();

        $imei = $request->input('imei');
        if($imei){
            $bdtDevices = $bdtDevices->where('imei', '=', $imei);
        }
        $client = $request->input('client');

        if($client){
            $client = '%'.$client.'%';
            $bdtDevices = $bdtDevices->whereHas('clientBDTDevice', function($cbdtdQuery) use ($client) {
                return $cbdtdQuery->whereHas('client', function($clientQuery) use ($client){
                    return $clientQuery->where(function($q) use ($client){
                        return $q->where('name_first', 'ilike', $client)
                        ->orWhere('name_last', 'ilike', $client)
                        ->orWhere('cell_number', 'ilike', $client)
                        ->orWhereRaw("name_first||' '||name_last ILIKE "."'".$client."'"); 
                    });
                });
            });
        }

        $is_issued = $request->input('is_issued');
        if($is_issued){
            if($is_issued == 'YES'){
                $bdtDevices = $bdtDevices->whereHas('clientBDTDevice');
            }

            if($is_issued == 'NO'){
                $bdtDevices = $bdtDevices->whereDoesntHave('clientBDTDevice');
            }
        }

        $is_issued = $request->input('is_issued');
        if($is_issued){
            if($is_issued == 'YES'){
                $bdtDevices = $bdtDevices->whereHas('clientBDTDevice');
            }

            if($is_issued == 'NO'){
                $bdtDevices = $bdtDevices->whereDoesntHave('clientBDTDevice');
            }
        }

        $mcp = $request->input('mcp');
        if($mcp){
            $bdtDevices = $bdtDevices->whereHas('clientBDTDevice', function($cbdtdQuery) use ($mcp) {
                return $cbdtdQuery->whereHas('client', function($clientQuery) use ($mcp){
                    $mcpPro = Pro::where('uid', $mcp)->first();
                    return $clientQuery->where('mcp_pro_id', $mcpPro->id);
                });
            });
        }

        $bdtDevices = $bdtDevices->paginate(20);
        
       
        return view('app.admin.bdt_devices', compact('bdtDevices', 'filters'));
    }

    public function patientsMissingDefasultSettings(Request $request){
        $filters = $request->all();
        $patients = Client::whereNull('shadow_pro_id');

        $patients = $patients->where(function($qry){
            return $qry->orWhereNull('mcp_pro_id')->orWhereNull('default_mcp_company_pro_id')->orWhereNull('default_mcp_company_pro_payer_id')->orWhereNull('default_mcp_company_location_id');
        });

        if ($request->input('name')) {
            $name = trim($request->input('name'));
            if ($name) {
                $patients = $patients->where(function ($q) use ($name) {
                    $q->where('name_first', 'ILIKE', '%' . $name . '%')
                        ->orWhere('name_last', 'ILIKE', '%' . $name . '%');
                });
            }
        }

        if ($request->input('mcp')) {
            if($request->input('mcp') == 'NO_MCP'){
                $patients = $patients->whereNull('mcp_pro_id');
            }else{
                $mcp = Pro::where('uid', trim($request->input('mcp')))->first();
                if ($mcp) {
                    $patients = $patients->where('mcp_pro_id', $mcp->id);
                }
            }
        }

        if ($request->input('chart_number')) {
            $patients = $patients->where('chart_number', 'ILIKE' , '%'.$request->input('chart_number').'%');
        }

        $status = $request->input('status');
        if($status){
            if($status == 'ACTIVE'){
                $patients->where('is_active', true)->where(function($q) use ($status){
                    return $q->where('client_engagement_status_category', $status)
                    ->orWhereNull('client_engagement_status_category');
                });    
            }else {
                $patients->where('client_engagement_status_category', $status);
            }

        }
        
        $insurance = $request->get('insurance');
        if($insurance){
            if($insurance === 'MEDICARE'){
                $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($cpcQuery){
                    return $cpcQuery->where('is_partbprimary', '=', 'YES');
                });
            }elseif($insurance === 'MEDICARE_PENDING'){
                $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($cpcQuery){
                    return $cpcQuery->where('plan_type', 'MEDICARE')->where('is_covered', '!=', 'YES');
                });
            }elseif($insurance === 'NOT_COVERED'){
                $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($cpcQuery){
                    return $cpcQuery->where('is_covered', '!=', 'YES');
                });
            }elseif($insurance === 'PENDING'){
                $patients = $patients->whereHas('effectiveClientPrimaryCoverage', function($cpcQuery){
                    return $cpcQuery->where('is_covered', '=', 'UNKNOWN');
                });
            }
            else{
                $patients = $patients->whereDoesntHave('effectiveClientPrimaryCoverage', function($cpcQuery){
                    return $cpcQuery->where('is_partbprimary', '=', 'YES');
                });
            }
        }
        
        $missing_default_settings = $request->get('missing_default_settings');
        if($missing_default_settings){
            if($missing_default_settings === 'NO_MCP') $patients = $patients->whereNull('mcp_pro_id');
            if($missing_default_settings === 'NO_MCP_COMPANY_PRO') $patients = $patients->whereNull('default_mcp_company_pro_id');
            if($missing_default_settings === 'NO_MCP_COMPANY_PRO_PAYER') $patients = $patients->whereNull('default_mcp_company_pro_payer_id');
            if($missing_default_settings === 'NO_MCP_COMPANY_LOCATION') $patients = $patients->whereNull('default_mcp_company_location_id');
        }

        $care_plan = $request->get('care_plan');
        if($care_plan){
            if($care_plan === 'UNSIGNED_CARE_PLANS'){
              $patients = $patients->whereHas('notes', function($noteQuery){ 
                return $noteQuery->where('cm_setup_manager_signature_status', '!=', 'SIGNED');
                });  
            }
            if($care_plan === 'UNCLEARED_CARE_PLANS'){
                $patients = $patients->where('has_care_plan_flag', true)->where('is_flag_cleared', false);
            }
            
        }

        $patients = $patients->orderBy('created_at', 'DESC')->paginate(50);
        return view('app.admin.patients_missing_default_settings', compact('patients', 'filters'));
    }

}