stagfe2/app/Http/Controllers/AdminController.php

<?php

namespace App\Http\Controllers;

use App\Models\AccountingItem;
use App\Models\AccountingItemTemplate;
use App\Models\AppSetting;
use App\Models\Claim;
use App\Models\Lead;
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\InternalMessage;
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 App\Models\Point;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Str;
use PDF;
use Illuminate\Support\Facades\Schema;
use App\Models\AdminPatient;
use App\Models\SupplyOrderView;
use Illuminate\Pagination\LengthAwarePaginator;
use Ramsey\Uuid\Uuid;

class AdminController extends Controller
{

    public function patients(Request $request)
    {

        // DB::enableQueryLog();

        $filters = $request->all();
        $patients = AdminPatient::whereNull('shadow_pro_id');


        if ($request->input('name')) {
            $name = trim($request->input('name'));
            if ($name) {
                $patients = $patients->where(function ($q) use ($name) {
                    $q->where('display_name', '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');

        $fVal = $request->input('has_email');
        if($fVal) {
            if($fVal === '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) = '')");
            }
        }

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

        $fVal = $request->input('has_default_mcp_company_pro');
        if($fVal) {
            if($fVal === 'YES') {
                $patients = $patients->whereRaw("(default_mcp_company_pro_id IS NOT NULL)");
            }
            else {
                $patients = $patients->whereRaw("(default_mcp_company_pro_id IS NULL)");
            }
        }

        $fVal = $request->input('has_default_mcp_company_pro_payer');
        if($fVal) {
            if($fVal === 'YES') {
                $patients = $patients->whereRaw("(default_mcp_company_pro_payer_id IS NOT NULL)");
            }
            else {
                $patients = $patients->whereRaw("(default_mcp_company_pro_payer_id IS NULL)");
            }
        }

        $fVal = $request->input('has_default_mcp_company_location');
        if($fVal) {
            if($fVal === 'YES') {
                $patients = $patients->whereRaw("(default_mcp_company_location_id IS NOT NULL)");
            }
            else {
                $patients = $patients->whereRaw("(default_mcp_company_location_id IS NULL)");
            }
        }

        $fVal = $request->input('has_bp_device');
        if($fVal) {
            if($fVal === 'YES') {
                $patients = $patients->whereRaw("((SELECT count(sh.id) " .
                    "FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id " .
                    "WHERE so.product_id = 1 AND sh.status IN ('DELIVERED', 'DISPATCHED') AND so.client_id = admin_patient_list.id) > 0)");
            }
            else {
                $patients = $patients->whereRaw("((SELECT count(sh.id) " .
                    "FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id " .
                    "WHERE so.product_id = 1 AND sh.status IN ('DELIVERED', 'DISPATCHED') AND so.client_id = admin_patient_list.id) = 0)");
            }
        }

        $fVal = $request->input('has_weight_scale');
        if($fVal) {
            if($fVal === 'YES') {
                $patients = $patients->whereRaw("((SELECT count(sh.id) " .
                    "FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id " .
                    "WHERE so.product_id = 2 AND sh.status IN ('DELIVERED', 'DISPATCHED') AND so.client_id = admin_patient_list.id) > 0)");
            }
            else {
                $patients = $patients->whereRaw("((SELECT count(sh.id) " .
                    "FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id " .
                    "WHERE so.product_id = 2 AND sh.status IN ('DELIVERED', 'DISPATCHED') AND so.client_id = admin_patient_list.id) = 0)");
            }
        }

        $fVal = $request->input('has_pulse_ox');
        if($fVal) {
            if($fVal === 'YES') {
                $patients = $patients->whereRaw("((SELECT count(sh.id) " .
                    "FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id " .
                    "WHERE so.product_id = 3 AND sh.status IN ('DELIVERED', 'DISPATCHED') AND so.client_id = admin_patient_list.id) > 0)");
            }
            else {
                $patients = $patients->whereRaw("((SELECT count(sh.id) " .
                    "FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id " .
                    "WHERE so.product_id = 3 AND sh.status IN ('DELIVERED', 'DISPATCHED') AND so.client_id = admin_patient_list.id) = 0)");
            }
        }

        $fVal = $request->input('has_temp_fun');
        if($fVal) {
            if($fVal === 'YES') {
                $patients = $patients->whereRaw("((SELECT count(sh.id) " .
                    "FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id " .
                    "WHERE so.product_id = 4 AND sh.status IN ('DELIVERED', 'DISPATCHED') AND so.client_id = admin_patient_list.id) > 0)");
            }
            else {
                $patients = $patients->whereRaw("((SELECT count(sh.id) " .
                    "FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id " .
                    "WHERE so.product_id = 4 AND sh.status IN ('DELIVERED', 'DISPATCHED') AND so.client_id = admin_patient_list.id) = 0)");
            }
        }

        $fVal = $request->input('imei');
        if($fVal) {
            $patients = $patients->whereRaw("((SELECT count(cbd.id) FROM client_bdt_device cbd
            WHERE cbd.client_id = admin_patient_list.id AND cbd.device_id IN (SELECT bd.id FROM bdt_device bd WHERE bd.imei LIKE '%$fVal%' AND bd.is_active IS TRUE)) > 0)");
        }

        if($request->input('number_of_measurements')){
            $keyName = $request->input('number_of_measurements');
            $measurementCountQuery = '(SELECT COUNT(*) FROM measurement WHERE measurement.client_id = admin_patient_list.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(strpos($insurance, '_new_|') === 0){
                $trimmed = trim(str_replace('_new_|', '', $insurance));
                $condSql = "EXISTS(SELECT 1 FROM insurance_card WHERE client_id = admin_patient_list.id AND is_active = TRUE AND (carrier_category ILIKE '%{$trimmed}%' OR carrier_name ILIKE '%{$trimmed}%'))";
                $patients = $patients->whereRaw($condSql);
            }
            else {
                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);
                    });
                }
            }
        }

        $companyID = $request->get('company');
        if($companyID){
            if($companyID == 'NONE'){
                $patients = $patients->doesntHave('companyClients');
            }else{
                $patients = $patients->whereHas('companyClients', function($qry)use($companyID){
                if($companyID != 'ANY'){
                    return $qry->where('company_id', $companyID);
                }
            });
            }

        }

        // search by tag
        if ($request->input('tags')) {
            $tags = trim($request->input('tags'));
            if ($tags) {
                try {
                    $condSql = "((SELECT COUNT(ct.id) FROM client_tag ct WHERE ct.client_id = admin_patient_list.id AND UPPER(ct.tag) LIKE '%" . strtoupper($tags) . "%') > 0)";
                    $patients = $patients->whereRaw($condSql);
                }
                catch (\Exception $e) {
                    dd($e->getMessage());
                }
            }
        }

        $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";
        }

        if(@$filters['mapView'] == 1){
            $patients = $patients->orderByRaw($sortBySQL)->paginate(100);
        }else{
          $patients = $patients->orderByRaw($sortBySQL)->paginate(25);
        }

        $oldInsurances = 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');

        $newInsurances = DB::select("select distinct COALESCE(ic.carrier_name, ic.carrier_category) as payer_name from insurance_card ic");

        // $qLog = DB::getQueryLog();

        // dd($qLog);

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

    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 leads(Request $request){

        $leads = Lead::whereNotNull('created_at');

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

    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 = SupplyOrderView::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'));
    }

    public function points(Request $request)
    {
        $filters = $request->all();
        $points = Point::query();
        $points = $points->where('is_removed', '!=', true)
            ->where('category', '!=', 'REVIEW')
            ->where('category', '!=', 'PLAN');

        $intentions = DB::select("SELECT intention, COUNT(intention) as count FROM point WHERE intention is not null and is_removed is not true  and category != 'REVIEW' and category != 'PLAN' GROUP BY intention ORDER BY intention ASC");
        $categories = [];
        $names = [];

        if ($request->input('mcp')) {
            $mcp = Pro::where('uid', trim($request->input('mcp')))->first();
            if ($mcp) {
              //  $points = $points->where('created_by_pro_id', $mcp->id);
            }
        }

        $implodedIntentions = null;
        if ($request->input('intentions')) {
            $points = $points->whereIn('intention', $request->input('intentions'));
            $implodedIntentions = join("','", $request->input('intentions'));
            $categories = DB::select("SELECT category, COUNT(id) as count FROM point WHERE intention in ('" . $implodedIntentions . "') and is_removed is not true  and category != 'REVIEW' and category != 'PLAN' GROUP BY category ORDER BY category ASC");
        }

        if ($request->input('categories')) {
            $points = $points->whereIn('category', $request->input('categories'));
            $implodedCategories = join("','", $request->input('categories'));
            $names = DB::select("select (data::json)->>'name' as name, count(id) as count from point where category in ('".$implodedCategories."') and is_removed is not true  and intention in ('".$implodedIntentions."')  and category != 'REVIEW' and category != 'PLAN' group by 1 order by 1 asc");
        }

        if ($request->input('names')){
            $implodedNames = join("','", $request->input('names'));
            $points = $points->whereRaw("(data::json)->>'name' in ('".$implodedNames."')");
        }

        $points = $points->paginate(30);

        $_point = new Point;
        $tableName = $_point->getTable();
        $columns = Schema::getColumnListing($tableName);


        return view('app.admin.points.index', compact('points', 'filters', 'columns', 'intentions', 'categories', 'names'));
    }

    public function pointDetails(Request  $request, $uid){
        $point = Point::where('uid', $uid)->first();
        $tableName = $point->getTable();
        $columns = Schema::getColumnListing($tableName);
        return view('app.admin.points.record-details', compact('point', 'columns'));
    }

    public function messages(Request $request)
    {
        $messages = InternalMessage::orderBy('created_at', 'desc')->paginate(50);
        return view('app.admin.messages', compact('messages'));
    }

    public function patientsNotesPointsFilter(Request $request){
        $filters = $request->all();

        $searchArrayStrings = null;
        $searchString = $request->get('string');
        if($searchString){
            $searchStrings = explode(',', $searchString);
            $searchArray = [];
            foreach($searchStrings as $string){
                $s = "'" . '%'.strtolower(trim($string)).'%'. "'";
                array_push($searchArray, $s);
            }
            $searchArrayStrings = implode(',', $searchArray);
        }


        //Query distinct clients who have points that contain specific substrings in the point.data

        $qry = "
        SELECT
        DISTINCT ON (p.client_id)
        p.client_id,
        p.id,
        p.uid,
        c.uid as client_uid,
        c.chart_number,
        c.cell_number,
        c.phone_home,
        c.phone_mobile,
        c.phone_work,
        c.email_address,
        c.dob,
        (c.name_first ||' '||c.name_last) as patient_name,
        (mcp.name_first ||' '||mcp.name_last) as mcp_name,
        c.most_recent_completed_mcp_note_date as last_visit_date,
        (cover.plan_type) as cover

        FROM
        point p
        LEFT JOIN client c on c.id = p.client_id
        LEFT JOIN pro mcp on mcp.id = c.mcp_pro_id
        LEFT JOIN client_primary_coverage cover on cover.client_id = c.id
        ";
        

        if($searchArrayStrings){
            $qry = $qry . " WHERE lower(p.data) ILIKE any (array[".$searchArrayStrings."])";
        }
        $qry = $qry . " ORDER BY p.client_id DESC, p.created_at DESC";

        $records = null;

        if($searchArrayStrings){
            $records = DB::select($qry);
            $page = $request->get('page', 1);
            $size = 20;
            $collect = collect($records);
            $records = new LengthAwarePaginator(
                $collect->forPage($page, $size),
                $collect->count(),
                $size,
                $page
            );
            $records->setPath(route('admin.patients-notes-points-filter'));
        }

        

        return view('app.admin.patients-notes-points-filter', compact('records', 'filters'));
    }

    public function manageAccountingItemsForBill(Request $request, Bill $bill) {
        return view('app.admin.accounting-items-for-bill', ['bill' => $bill]);
    }

    public function putClaimProperty(Request $request)
    {
        $claim = Claim::where('uid', $request->get('uid'))->first();
        if($claim){
            $json = $claim->detail_json;
            if($json) {
                $parsed = json_decode($json, true);
                if(!$parsed) $parsed = [];
            }
            else $parsed = [];
            $parsed[$request->get('name')] = $request->get('value');
            $json = json_encode($parsed);

            DB::select("UPDATE claim SET detail_json = '$json' WHERE uid = '$claim->uid'");

            return json_encode([
                "success" => true,
                "data" => $claim->uid
            ]);
        }
        return json_encode([
            "success" => false,
            "error" => "Claim does not exist."
        ]);
    }

    public function putAppSetting(Request $request)
    {
        $key = $request->get('key');
        $value = $request->get('value');
        $setting = AppSetting::where('key', $key)->first();
        if($setting){
            DB::select("UPDATE app_setting SET value = :value WHERE key = :key", [
                'value' => $value,
                'key' => $key
            ]);
        }
        else {
            $uuid = Str::uuid();
            DB::select("INSERT INTO app_setting 
                (id, uid, created_at, last_updated_at, created_by_pro_id, created_by_session_id, type, detail_json, key, value, data_type)
                VALUES 
                (nextval('app_setting_id_seq'), :uuid, NOW(), NOW(),
                NULL, NULL, 'AppSetting', NULL,
                :key, :value, NULL)", [
                'uuid' => $uuid,
                'value' => $value,
                'key' => $key
            ]);
        }
        return json_encode([
            "success" => true,
            "data" => null
        ]);
    }

    public function accountingItems(Request $request) {

        if($request->input('_ql')) DB::enableQueryLog();

        $columns = [
            'ai.id',
            'ai.uid',
            'ai.entity_type',
            'ai.entity_id',
            'ai.entity_uid',
            'ai.pro_id',
            'ai.client_id',
            'ai.note_id',
            'ai.care_month_id',
            'ai.bill_id',
            'ai.supply_order_id',
            'ai.positive_or_negative',
            'ai.memo',
            'ai.expected_value',
            'ai.received_value',
            'ai.created_at',
            'ai.last_updated_at',
            'ai.is_open',
            'ai.is_active',
            'cl.uid as client_uid',
            'cl.name_first as client_name_first',
            'cl.name_last as client_name_last',
            'p.uid as pro_uid',
            'p.name_first as pro_name_first',
            'p.name_last as pro_name_last',
            'n.uid as note_uid',
            'cm.uid as care_month_uid',
            'o.uid as supply_order_uid',
            'b.uid as bill_uid',
        ];

        $tables = [
            'accounting_item ai',
            'LEFT JOIN client cl ON cl.id = ai.client_id',
            'LEFT JOIN pro p ON p.id = ai.pro_id',
            'LEFT JOIN note n ON n.id = ai.note_id',
            'LEFT JOIN supply_order o ON o.id = ai.supply_order_id',
            'LEFT JOIN care_month cm ON cm.id = ai.care_month_id',
            'LEFT JOIN bill b ON b.id = ai.bill_id',
        ];

        $conditions = ["ai.id > 0"];
        $params = [];

        if($request->input('y')) {
            $conditions[] = 'EXTRACT(YEAR from ai.created_at) = :y';
            $params['y'] = intval($request->input('y'));
        }

        if($request->input('y') && $request->input('m')) {
            $conditions[] = 'EXTRACT(MONTH from ai.created_at) = :m';
            $params['m'] = intval($request->input('m'));
        }

        if($request->input('type') == 'revenue')
            $conditions[] = 'ai.positive_or_negative = 0';
        elseif($request->input('type') == 'cost')
            $conditions[] = 'ai.positive_or_negative = 1';

        if($request->input('context')) {
            $conditions[] = 'ai.entity_type = :context';
            $params['context'] = $request->input('context');
        }

        if($request->input('open') == 'open')
            $conditions[] = 'ai.is_open IS TRUE';
        elseif($request->input('open') == 'closed')
            $conditions[] = 'ai.is_open IS FALSE';

        if($request->input('active') == 'active')
            $conditions[] = 'ai.is_active IS TRUE';
        elseif($request->input('active') == 'inactive')
            $conditions[] = 'ai.is_active IS FALSE';

        if($request->input('pro')) {
            $conditions[] = 'p.uid = :proUid';
            $params['proUid'] = $request->input('pro');
        }

        if($request->input('client')) {
            $conditions[] = 'cl.uid = :clientUid';
            $params['clientUid'] = $request->input('client');
        }

        // execute
        $columns = implode(",\n", $columns);
        $tables = implode("\n", $tables);
        $conditions = implode(" AND\n", $conditions);

        $pagination = "";
        if($request->input('paginate')) {
            $page = $request->get('page', 1);
            $size = $request->get('size', 25);
            $pagination = "OFFSET ".(($page - 1) * $size) . " LIMIT $size";
        }

        $countSql = "SELECT count(ai.id) as count FROM $tables WHERE $conditions";
        $countResult = DB::select($countSql, $params);

        $dataSql = "SELECT 
                $columns 
            FROM 
                $tables 
            WHERE 
                $conditions 
            ORDER BY 
                ai.created_at DESC
            $pagination";
        $dataResult = DB::select($dataSql, $params);

        $revenueTotalSql = "SELECT 
                SUM(COALESCE(ai.expected_value, 0)) as total_expected_value, SUM(COALESCE(ai.received_value, 0)) as total_received_value
            FROM
                $tables
            WHERE
                $conditions AND ai.positive_or_negative = 0";
        $revenueTotalResult = DB::select($revenueTotalSql, $params);

        $costTotalSql = "SELECT 
                SUM(COALESCE(ai.expected_value, 0)) as total_expected_value, SUM(COALESCE(ai.received_value, 0)) as total_received_value
            FROM
                $tables
            WHERE
                $conditions AND ai.positive_or_negative = 1";
        $costTotalResult = DB::select($costTotalSql, $params);

        $expectedTotal = $revenueTotalResult[0]->total_expected_value - $costTotalResult[0]->total_expected_value;
        $receivedTotal = $revenueTotalResult[0]->total_received_value - $costTotalResult[0]->total_received_value;

        $paginator = null;
        if($request->input('paginate')) {
            $paginator = new LengthAwarePaginator($dataResult, $countResult[0]->count, $size, $page);
            $paginator->setPath(route('accounting-items'));
        }

        // all clients
        $allClientsSQL = "SELECT DISTINCT(ai.client_id), cl.uid as client_uid, cl.name_first, cl.name_last
        FROM 
            $tables
        WHERE
            $conditions";
        $allClientsResult = DB::select($allClientsSQL, $params);

        // all pros
        $allProsSQL = "SELECT DISTINCT(ai.pro_id), p.uid as pro_uid, p.name_first, p.name_last
        FROM 
            $tables
        WHERE
            $conditions";
        $allProsResult = DB::select($allProsSQL, $params);

        $html = view('app.admin.accounting-items', [
            'total' => $countResult[0]->count,
            'records' => $dataResult,
            'paginator' => $paginator,
            'expected_total' => $expectedTotal,
            'received_total' => $receivedTotal,
            'all_clients' => $allClientsResult,
            'all_pros' => $allProsResult,
        ]);

        if($request->input('_ql')) printQueryLog(DB::getQueryLog());

        return $html;
    }

    public function accountingItemTemplates(Request $request) {
        $records = AccountingItemTemplate::orderBy('entity_type')->get();
        return view('app.admin.accounting-item-templates', compact('records'));
    }

    public function createAccountingItemTemplate(Request $request) {

        $id = DB::select("select (coalesce(max(id), 1) + 1) as nextid from accounting_item_template");

        $template = new  AccountingItemTemplate();
        $template->id =  $id[0]->nextid;
        $template->uid = Uuid::uuid4();
        $template->is_active = true;
        $template->created_at =  date('Y-m-d H:i:s');
        $template->last_updated_at =  date('Y-m-d H:i:s');

        $template->entity_type = $request->input('entityType');
        $template->memo = $request->input('memo');
        $template->expected_value = $request->input('expectedValue');
        $template->received_value = $request->input('receivedValue');
        $template->is_open = $request->input('isOpen');
        $template->positive_or_negative = $request->input('positiveOrNegative');

        $template->save();
        return $this->pass($template->uid);
    }

    public function updateAccountingItemTemplate(Request $request) {

        $template = AccountingItemTemplate::where('uid',  $request->input('uid'))->first();

        $template->last_updated_at =  date('Y-m-d H:i:s');

        $template->entity_type = $request->input('entityType');
        $template->memo = $request->input('memo');
        $template->expected_value = $request->input('expectedValue');
        $template->received_value = $request->input('receivedValue');
        $template->is_open = $request->input('isOpen');
        $template->positive_or_negative = $request->input('positiveOrNegative');

        $template->save();
        return $this->pass($template->uid);
    }
}